12 Replies Latest reply on Mar 29, 2011 1:18 PM by MeherIrk

    Reg:Got an exception while getting multiple records for updation

    835862
      Hi All,
      i want to update the records in Advanced table after clicking Search Btn.
      and i check the check boxes to update the more than one record(Multiple Records selection in advanced table for updation).
      i am writing the code in ProcessRequest Method to get the multiple records for updation after clicking Update Button in the Search Page.
      but when i am clicking Update Button, I got the Below Exception.

      My Code is:

      XxSupppacklistAMImpl am=(XxSupppacklistAMImpl)pageContext.getApplicationModule(webBean);
      System.out.println("Hello i am in before init trrquery multiupdation of PR:");
      am.initTreeQuery();

      System.out.println("Hello i am in multiupdation of after inittreequeryPR:");

      OAViewObjectImpl upDtVO=(OAViewObjectImpl)am.findViewObject("PackingListSeaVO");
      System.out.println("Hello i am in inside view object:");

      Row[] rows=upDtVO.getFilteredRows("SingleSelection","Y");// (Here i Got the Below Exception)

      System.out.println("Hello i am in inside Row:");

      if((rows!=null) && rows.length<0)
      {
      System.out.println("Hello i am in inside For Loop:");
      Row mRow=rows[0];
      upDtVO.setCurrentRow(mRow);
      String vIteNumber=(String)mRow.getAttribute("ItemNumber");
      System.out.println("The Selected Record for Updation,"+vIteNumber);
      }

      Exception Details.
      oracle.apps.fnd.framework.OAException: oracle.jbo.SQLStmtException: JBO-27122: SQL error during statement preparation. Statement: SELECT
      PACKING_UNIT_NUM
      ,PACKING_UNIT_STATUS
      ,PO_NUM
      ,PO_LINE
      ,PROJECT_NUM
      ,VENDOR_NAME
      ,CUSTOMER_PLANT
      ,ITEM_NUMBER
      ,PARENT_ITEM
      ,SUPPLIER_REFERENCE
      ,TERMS_OF_DELIVERY
      ,DELIVERY_STATUS
      ,READY_FOR_DISPATCH_ON
      ,TOTAL_GROSS_WEIGHT
      ,TOTAL_NET_WEIGHT
      ,COLLECTION_ADDRESS
      ,ZIP_CODE_CITY
      ,COUNTRY
      ,CONTACT_PERSON
      ,COUNTRY_OF_ORIGIN
      ,CARGO_SPEC_NUM
      ,SHIPMENT_NUM
      ,LOGISTIC_USER
      ,DEMAND_SOURCE
      ,EXPEDITOR_NAME
      ,BUYER_NAME
      ,SUPPLY_STATUS
      ,PO_QTY
      ,RECEIVED_QTY
      FROM XXFLS_SWB_CPL FFPU
      WHERE
      UPPER(FFPU.PO_NUM)=(NVL(:1,FFPU.PO_NUM))
      AND UPPER(FFPU.PACKING_UNIT_NUM)=(nvl(:2,FFPU.PACKING_UNIT_NUM))
      AND UPPER(FFPU.VENDOR_NAME) LIKE UPPER(NVL(:3,FFPU.VENDOR_NAME))
           at oracle.apps.fnd.framework.OAException.wrapperException(Unknown Source)
           at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequest(Unknown Source)
           at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processRequest(Unknown Source)
           at oracle.apps.fnd.framework.webui.OAPageLayoutHelper.processRequest(Unknown Source)
           at oracle.apps.fnd.framework.webui.beans.layout.OAPageLayoutBean.processRequest(Unknown Source)
           at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequestChildren(Unknown Source)
           at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequestChildren(Unknown Source)
           at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequest(Unknown Source)
           at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processRequest(Unknown Source)
           at oracle.apps.fnd.framework.webui.beans.form.OAFormBean.processRequest(Unknown Source)
           at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequestChildren(Unknown Source)
           at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequestChildren(Unknown Source)
           at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequest(Unknown Source)
           at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processRequest(Unknown Source)
           at oracle.apps.fnd.framework.webui.beans.OABodyBean.processRequest(Unknown Source)
           at oracle.apps.fnd.framework.webui.OAPageBean.processRequest(Unknown Source)
           at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(Unknown Source)
           at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(Unknown Source)
           at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(Unknown Source)
           at OA.jspService(_OA.java:71)
           at com.orionserver.http.OrionHttpJspPage.service(OrionHttpJspPage.java:59)
           at oracle.jsp.runtimev2.JspPageTable.service(JspPageTable.java:462)
           at oracle.jsp.runtimev2.JspServlet.internalService(JspServlet.java:594)
           at oracle.jsp.runtimev2.JspServlet.service(JspServlet.java:518)
           at javax.servlet.http.HttpServlet.service(HttpServlet.java:856)
           at com.evermind.server.http.ServletRequestDispatcher.invoke(ServletRequestDispatcher.java:713)
           at com.evermind.server.http.ServletRequestDispatcher.forwardInternal(ServletRequestDispatcher.java:370)
           at com.evermind.server.http.ServletRequestDispatcher.unprivileged_forward(ServletRequestDispatcher.java:259)
           at com.evermind.server.http.ServletRequestDispatcher.access$100(ServletRequestDispatcher.java:51)
           at com.evermind.server.http.ServletRequestDispatcher$2.oc4jRun(ServletRequestDispatcher.java:193)
           at oracle.oc4j.security.OC4JSecurity.doPrivileged(OC4JSecurity.java:283)
           at com.evermind.server.http.ServletRequestDispatcher.forward(ServletRequestDispatcher.java:198)
           at com.evermind.server.http.EvermindPageContext.forward(EvermindPageContext.java:392)
           at OA.jspService(_OA.java:80)
           at com.orionserver.http.OrionHttpJspPage.service(OrionHttpJspPage.java:59)
           at oracle.jsp.runtimev2.JspPageTable.service(JspPageTable.java:462)
           at oracle.jsp.runtimev2.JspServlet.internalService(JspServlet.java:594)
           at oracle.jsp.runtimev2.JspServlet.service(JspServlet.java:518)
           at javax.servlet.http.HttpServlet.service(HttpServlet.java:856)
           at com.evermind.server.http.ServletRequestDispatcher.invoke(ServletRequestDispatcher.java:713)
           at com.evermind.server.http.ServletRequestDispatcher.forwardInternal(ServletRequestDispatcher.java:370)
           at com.evermind.server.http.HttpRequestHandler.doProcessRequest(HttpRequestHandler.java:871)
           at com.evermind.server.http.HttpRequestHandler.processRequest(HttpRequestHandler.java:453)
           at com.evermind.server.http.HttpRequestHandler.serveOneRequest(HttpRequestHandler.java:221)
           at com.evermind.server.http.HttpRequestHandler.run(HttpRequestHandler.java:122)
           at com.evermind.server.http.HttpRequestHandler.run(HttpRequestHandler.java:111)
           at oracle.oc4j.network.ServerSocketReadHandler$SafeRunnable.run(ServerSocketReadHandler.java:260)
           at oracle.oc4j.network.ServerSocketAcceptHandler.procClientSocket(ServerSocketAcceptHandler.java:239)
           at oracle.oc4j.network.ServerSocketAcceptHandler.access$700(ServerSocketAcceptHandler.java:34)
           at oracle.oc4j.network.ServerSocketAcceptHandler$AcceptHandlerHorse.run(ServerSocketAcceptHandler.java:880)
           at com.evermind.util.ReleasableResourcePooledExecutor$MyWorker.run(ReleasableResourcePooledExecutor.java:303)
           at java.lang.Thread.run(Thread.java:595)
      ## Detail 0 ##
      java.sql.SQLException: Missing IN or OUT parameter at index:: 1
           at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:138)
           at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:175)
           at oracle.jdbc.driver.OraclePreparedStatement.processCompletedBindRow(OraclePreparedStatement.java:1587)
           at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:2996)
           at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3043)
           at oracle.jbo.server.QueryCollection.buildResultSet(QueryCollection.java:857)
           at oracle.jbo.server.QueryCollection.executeQuery(QueryCollection.java:666)
           at oracle.jbo.server.ViewObjectImpl.executeQueryForCollection(ViewObjectImpl.java:3655)
           at oracle.jbo.server.OAJboViewObjectImpl.executeQueryForCollection(Unknown Source)
           at oracle.apps.fnd.framework.server.OAViewObjectImpl.executeQueryForCollection(Unknown Source)
           at oracle.jbo.server.ViewRowSetImpl.execute(ViewRowSetImpl.java:742)
           at oracle.jbo.server.ViewRowSetImpl.execute(ViewRowSetImpl.java:687)
           at oracle.jbo.server.ViewRowSetIteratorImpl.ensureRefreshed(ViewRowSetIteratorImpl.java:2657)
           at oracle.jbo.server.ViewRowSetIteratorImpl.ensureRefreshed(ViewRowSetIteratorImpl.java:2634)
           at oracle.jbo.server.ViewRowSetIteratorImpl.setRangeStartWithRefresh(ViewRowSetIteratorImpl.java:2607)
           at oracle.jbo.server.ViewRowSetIteratorImpl.setRangeStart(ViewRowSetIteratorImpl.java:2598)
           at oracle.apps.fnd.framework.server.OAViewObjectImpl.getFilteredRowsInFetchedRange(Unknown Source)
           at oracle.apps.fnd.framework.server.OAViewObjectImpl.getFilteredRowsInFetchedRange(Unknown Source)
           at oracle.apps.fnd.framework.server.OAViewObjectImpl.getFilteredRows(Unknown Source)
           at xxfls.oracle.apps.po.packlist.webui.hGridCO.processRequest(hGridCO.java:70)
           at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequest(Unknown Source)
           at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processRequest(Unknown Source)
           at oracle.apps.fnd.framework.webui.OAPageLayoutHelper.processRequest(Unknown Source)
           at oracle.apps.fnd.framework.webui.beans.layout.OAPageLayoutBean.processRequest(Unknown Source)
           at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequestChildren(Unknown Source)
           at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequestChildren(Unknown Source)
           at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequest(Unknown Source)


      Can you please give me the proper solution For this.

      Regards,
        • 1. Re: Reg:Got an exception while getting multiple records for updation
          Gyan Darpan
          Hi,

          Check with the am.initTreeQuery(); Method. Your are trying to execute a VO without passing the expected input parameter.

          Post the code for the same.

          Regards,
          Gyan
          • 2. Re: Reg:Got an exception while getting multiple records for updation
            835862
            Hi Gyan,

            i write the sop and i will test the initQuery() method get executed successfully.
            Row[] rows=upDtVO.getFilteredRows("SingleSelection","Y"); (Here only i got the exception)


            Regards,
            • 3. Re: Reg:Got an exception while getting multiple records for updation
              Gyan Darpan
              Hi,

              Post the upDtVO Query.

              Hope you have initialized the VO (upDtVO). Check the same.

              Regards,
              Gyan
              • 4. Re: Reg:Got an exception while getting multiple records for updation
                835862
                hi Gyan,

                i am writing the code below can you please check once.
                i have initialize the VO.

                XxSupppacklistAMImpl am=(XxSupppacklistAMImpl)pageContext.getApplicationModule(webBean);
                am.initTreeQuery();
                OAViewObjectImpl upDtVO=(OAViewObjectImpl)am.findViewObject("PackingListSeaVO");
                System.out.println("Hello i am in inside view object:");


                Object swq=upDtVO.getTransientValue("SingleSelection");
                System.out.println("Hello i am before to the single selection VO:,"+swq);

                upDtVO.executeQuery();
                Row[] rows=upDtVO.getFilteredRows("SingleSelection","Y");

                System.out.println("Hello i am in inside Row:");

                if((rows!=null) && rows.length<0)
                {
                System.out.println("Hello i am in inside For Loop:");
                Row mRow=rows[0];
                upDtVO.setCurrentRow(mRow);
                String vIteNumber=(String)mRow.getAttribute("ItemNumber");
                System.out.println("The Selected Record for Updation,"+vIteNumber);
                }else

                {
                throw new OAException("The records are not selected for updation:");
                }
                }


                Regards,
                • 5. Re: Reg:Got an exception while getting multiple records for updation
                  Gyan Darpan
                  Hi,

                  Replace the below line to
                  //OAViewObjectImpl upDtVO=(OAViewObjectImpl)am.findViewObject("PackingListSeaVO");
                  
                  OAViewObjectImpl upDtVO=(OAViewObjectImpl)am.findViewObject("PackingListSeaVO1");//You should init the VO Instance not the VO
                  Regards.
                  Gyan
                  • 6. Re: Reg:Got an exception while getting multiple records for updation
                    835862
                    hi Gyan,
                    i have instantiated VO Instnce only,which is attached to AM.
                    i have deleted the 1 after attaching the VO to the AM.


                    Regards,
                    • 7. Re: Reg:Got an exception while getting multiple records for updation
                      Gyan Darpan
                      Hi,
                      i have deleted the 1 after attaching the VO to the AM.
                      I didn't understand what does you mean by above line. Specify the same.

                      Regards,
                      Gyan
                      • 8. Re: Reg:Got an exception while getting multiple records for updation
                        835862
                        hi Gyan,


                        //OAViewObjectImpl upDtVO=(OAViewObjectImpl)am.findViewObject("PackingListSeaVO");

                        OAViewObjectImpl upDtVO=(OAViewObjectImpl)am.findViewObject("PackingListSeaVO1");//You should init the VO Instance not the VO
                        the above you are saying,we need to attach VO Instance PackingListSeaVO1 to the AM,
                        My View Instance is this only PackingListSeaVO

                        OAViewObjectImpl upDtVO=(OAViewObjectImpl)am.findViewObject("PackingListSeaVO");// it is my View Instacne which is attached to AM.


                        Regards,
                        • 9. Re: Reg:Got an exception while getting multiple records for updation
                          Gyan Darpan
                          Hi,

                          Share your AM.xml file.

                          Regards,
                          Gyan
                          • 10. Re: Reg:Got an exception while getting multiple records for updation
                            835862
                            hi Gyan,

                            Could you please find the Below AM.

                            public class XxSupppacklistAMImpl extends OAApplicationModuleImpl {
                            /**This is the default constructor (do not remove)
                            */
                            public XxSupppacklistAMImpl(){
                            }
                            /**Sample main for debugging Business Components code using the tester.
                            */
                            public static void main(String[] args) {
                            launchTester("xxfls.oracle.apps.po.packlist.server", /* package name */
                            "XxSupppacklistAMLocal" /* Configuration Name */);
                            }
                            //create blank row for insertinf the data into the custom tab

                            public void creaPackListPkg(){

                            SuppCreaPackListEOVVOImpl poVO=getSuppCreaPackListEOVVO();
                            if (!poVO.isPreparedForExecution())
                            {
                            poVO.executeQuery();
                            poVO.setMaxFetchSize(0);
                            poVO.clearCache();
                            }
                            Row row=poVO.createRow();
                            poVO.insertRow(row);
                            row.setNewRowState(Row.STATUS_INITIALIZED);
                            }

                            public void saveTransaction(){
                            getOADBTransaction().commit();
                            }

                            public void saveRollback()
                            {
                            getOADBTransaction().rollback();
                            OADBTransaction tr=getOADBTransaction();
                            tr.isDirty();
                            System.out.println("Hello i am in isDirty Method");
                            }

                            public void initTreeQuery()
                            {
                            ItemDetailsVOImpl vo=getItemDetailsVO();
                            vo.clearCache();
                            System.out.println("Hello i am in initTreeQuery method");
                            vo.initQuery();
                            System.out.println("Hello Ram How are u Man");
                            }

                            public void setSearchParams(OAPageContext pageContext, OAWebBean webBean)
                            {
                            PackingListSeaVOImpl seVO=getPackingListSeaVO();
                            String pPonum="", pPUnitNO="",pVname="";
                            int f=0;


                            if (pageContext.getParameter("PoNo")!=null && !pageContext.getParameter("PoNo").equals("") )
                            {
                            pPonum=pageContext.getParameter("PoNo");
                            seVO.setWhereClauseParam(0,pPonum);
                            f=1;
                            }
                            else
                            {
                            seVO.setWhereClauseParam(0,null);
                            }
                            if (pageContext.getParameter("PackingUnitNumber")!=null && !pageContext.getParameter("PackingUnitNumber").equals(""))
                            {
                            pPUnitNO=pageContext.getParameter("PackingUnitNumber");
                            seVO.setWhereClauseParam(1,pPUnitNO);
                            f=1;
                            }
                            else
                            {
                            seVO.setWhereClauseParam(1,null);
                            }
                            if (pageContext.getParameter("VendorName")!=null && !pageContext.getParameter("VendorName").equals(""))
                            {
                            pVname=pageContext.getParameter("VendorName");
                            seVO.setWhereClauseParam(2,pVname);
                            f=1;
                            }
                            else
                            {
                            seVO.setWhereClauseParam(2,null);
                            }
                            if (f==1)
                            {
                            seVO.executeQuery();
                            }
                            else
                            {
                            throw new OAException("Please Select al least one Search Criteria",OAException.WARNING);
                            }
                            }


                            /**Container's getter for ItemDetailsVO1
                            */
                            public ItemDetailsVOImpl getItemDetailsVO(){
                            return (ItemDetailsVOImpl)findViewObject("ItemDetailsVO1");
                            }

                            /**Container's getter for ItemDetailsVO2
                            */
                            public ItemDetailsVOImpl getItemDetailsVO2() {
                            return (ItemDetailsVOImpl)findViewObject("ItemDetailsVO2");
                            }

                            /**Container's getter for ItemDetailsVL1
                            */
                            public ViewLinkImpl getItemDetailsVL1(){
                            return (ViewLinkImpl)findViewLink("ItemDetailsVL1");
                            }

                            /**Container's getter for PackingListSeaVO // this is the VO which i have used for Searching
                            */
                            public PackingListSeaVOImpl getPackingListSeaVO() {
                            return (PackingListSeaVOImpl)findViewObject("PackingListSeaVO");
                            }


                            /**Container's getter for ItemDetailsVO1
                            */
                            public ItemDetailsVOImpl getItemDetailsVO1() {
                            return (ItemDetailsVOImpl)findViewObject("ItemDetailsVO1");
                            }


                            /**Container's getter for ItemWhereClauseVO
                            */
                            public ItemWhereClauseVOImpl getItemWhereClauseVO() {
                            return (ItemWhereClauseVOImpl)findViewObject("ItemWhereClauseVO");
                            }

                            /**Container's getter for FlsPONumVO
                            */
                            public FlsPONumVOImpl getFlsPONumVO() {
                            return (FlsPONumVOImpl)findViewObject("FlsPONumVO");
                            }

                            /**Container's getter for PackUnitNumVO
                            */
                            public PackUnitNumVOImpl getPackUnitNumVO() {
                            return (PackUnitNumVOImpl)findViewObject("PackUnitNumVO");
                            }

                            /**Container's getter for VendorNameVO
                            */
                            public VendorNameVOImpl getVendorNameVO() {
                            return (VendorNameVOImpl)findViewObject("VendorNameVO");
                            }

                            /**Container's getter for SuppCreaPackListEOVVO
                            */
                            public SuppCreaPackListEOVVOImpl getSuppCreaPackListEOVVO() {
                            return (SuppCreaPackListEOVVOImpl)findViewObject("SuppCreaPackListEOVVO");
                            }

                            /**Container's getter for SuppCreaPackListEOVVO1
                            */
                            public SuppCreaPackListEOVVOImpl getSuppCreaPackListEOVVO1() {
                            return (SuppCreaPackListEOVVOImpl)findViewObject("SuppCreaPackListEOVVO1");
                            }
                            }

                            Regards,
                            • 11. Re: Reg:Got an exception while getting multiple records for updation
                              Gyan Darpan
                              Hi,
                              OAViewObjectImpl upDtVO=(OAViewObjectImpl)am.findViewObject("PackingListSeaVO");
                              System.out.println("Hello i am in inside view object:"); 
                              Object swq=upDtVO.getTransientValue("SingleSelection");
                              System.out.println("Hello i am before to the single selection VO:,"+swq); 
                              //Your are missing the parameter
                              upDtVO.executeQuery();//Actual error lies here... you are trying to execute the Query without passing the parameter. 
                              SELECT PACKING_UNIT_NUM,PACKING_UNIT_STATUS,PO_NUM,PO_LINE
                              ,PROJECT_NUM,VENDOR_NAME,CUSTOMER_PLANT,ITEM_NUMBER,PARENT_ITEM
                              ,SUPPLIER_REFERENCE,TERMS_OF_DELIVERY,DELIVERY_STATUS
                              ,READY_FOR_DISPATCH_ON,TOTAL_GROSS_WEIGHT
                              ,TOTAL_NET_WEIGHT,COLLECTION_ADDRESS,ZIP_CODE_CITY
                              ,COUNTRY,CONTACT_PERSON,COUNTRY_OF_ORIGIN
                              ,CARGO_SPEC_NUM,SHIPMENT_NUM,LOGISTIC_USER
                              ,DEMAND_SOURCE,EXPEDITOR_NAME,BUYER_NAME
                              ,SUPPLY_STATUS,PO_QTY,RECEIVED_QTY
                              FROM XXFLS_SWB_CPL FFPU
                              WHERE 
                              UPPER(FFPU.PO_NUM)=(NVL(:1,FFPU.PO_NUM)) 
                              AND UPPER(FFPU.PACKING_UNIT_NUM)=(nvl(:2,FFPU.PACKING_UNIT_NUM))
                              AND UPPER(FFPU.VENDOR_NAME) LIKE UPPER(NVL(:3,FFPU.VENDOR_NAME))
                              Regards,
                              Gyan
                              1 person found this helpful
                              • 12. Re: Reg:Got an exception while getting multiple records for updation
                                MeherIrk
                                Hi,
                                Iam writing the code in ProcessRequest Method to get the multiple records for updation after clicking UpdateButton
                                in the Search Page.
                                ---write the method in processFormRequest not in processReq.
                                ---In the method by using iterator read the row by row.
                                ---as i knw u have code for this..

                                Regards
                                Meher Irk