11 Replies Latest reply on Oct 8, 2013 1:23 PM by user632699

    Error:  oracle.apps.fnd.framework.OAException: oracle.jbo.SQLStmtException: JBO-27122: SQL error during statement preparation

    user632699

      Hi all,

       

      I am trying to extend the supplier address book site creation controller: ByrCrtStCO and loop inside the processFormRequest method through all the operating unit related sites on that page.

       

      I have the following code:

       

      package XXXX.oracle.apps.pos.supplier.webui;

      import java.sql.SQLException;
      import java.sql.Types;
      import oracle.apps.fnd.framework.OAApplicationModule;
      import oracle.apps.fnd.framework.OAException;
      import oracle.apps.fnd.framework.OARow;
      import oracle.apps.fnd.framework.OAViewObject;
      import oracle.apps.fnd.framework.webui.OAPageContext;
      import oracle.apps.fnd.framework.webui.beans.OAWebBean;
      import oracle.apps.pos.supplier.components.server.ByrAddrAMImpl;
      import oracle.apps.pos.supplier.webui.ByrCrtStCO;
      import oracle.apps.pos.supplier.webui.SupplierUtil;
      import oracle.jdbc.OracleCallableStatement;

      import oracle.jbo.RowSetIterator;
      import oracle.jbo.Row;


      /**
      * @author AO *
      *  *
      */
      public class xxxpoByrCrtStCO  extends ByrCrtStCO
      {
         //constructor
          public xxxpoByrCrtStCO()
          {
          }


          public void processFormRequest(OAPageContext oapagecontext, OAWebBean oawebbean)
          {
              //call super to continue the rest of the processing
              super.processFormRequest(oapagecontext, oawebbean);
              //update the supplier site on all records with the vat code
              updateVendorSites(oapagecontext, oawebbean);
            

          }

          private void updateVendorSites(OAPageContext oapagecontext, OAWebBean oawebbean) {
              OAApplicationModule applicationModule = oapagecontext.getApplicationModule(oawebbean);
              if(oapagecontext.getParameter("applyBtn") != null)
              {
                  OAViewObject oaviewobject =  (OAViewObject) applicationModule.findViewObject("CrtSitesVO");           
                    if (oaviewobject != null) {
                                        int count = oaviewobject.getRowCount();
                                        if(oapagecontext.isLoggingEnabled(2))
                                                               oapagecontext.writeDiagnostics(this, (new StringBuilder()).append(" Count: ").append(count).toString(), 2);
                                       
                          }
                          else {
                              if(oapagecontext.isLoggingEnabled(2))
                                                     oapagecontext.writeDiagnostics(this, (new StringBuilder()).append(" Count: ").append("null").toString(), 2);
                             
                          }

         }

       

      I get the below error:  What could be problem?

       

       

      Error Page

      Exception Details.
      oracle.apps.fnd.framework.OAException: oracle.jbo.SQLStmtException: JBO-27122: SQL error during statement preparation. Statement: SELECT * FROM (select 'N' as select1, substr(:1,1,15) as Site_Name, organization_id, name,:2 as Site_Name_Display from hr_operating_units WHERE mo_global.check_access(organization_id) = 'Y') QRSLT ORDER BY name at oracle.apps.fnd.framework.OAException.wrapperException(OAException.java:912) at oracle.apps.fnd.framework.webui.OAPageErrorHandler.prepareException(OAPageErrorHandler.java:1169) at oracle.apps.fnd.framework.webui.OAPageErrorHandler.processErrors(OAPageErrorHandler.java:1435) at oracle.apps.fnd.framework.webui.OAPageBean.processFormRequest(OAPageBean.java:3001) at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:1885) at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:550) at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:438) at _OA._jspService(_OA.java:212) at com.orionserver.http.OrionHttpJspPage.service(OrionHttpJspPage.java:59) at oracle.jsp.runtimev2.JspPageTable.service(JspPageTable.java:390) 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.ResourceFilterChain.doFilter(ResourceFilterChain.java:64) at oracle.apps.jtf.base.session.ReleaseResFilter.doFilter(ReleaseResFilter.java:26) at com.evermind.server.http.EvermindFilterChain.doFilter(EvermindFilterChain.java:15) at oracle.apps.fnd.security.AppsServletFilter.doFilter(AppsServletFilter.java:318) at com.evermind.server.http.ServletRequestDispatcher.invoke(ServletRequestDispatcher.java:642) at com.evermind.server.http.ServletRequestDispatcher.forwardInternal(ServletRequestDispatcher.java:391) at com.evermind.server.http.HttpRequestHandler.doProcessRequest(HttpRequestHandler.java:911) at com.evermind.server.http.HttpRequestHandler.processRequest(HttpRequestHandler.java:458) at com.evermind.server.http.AJPRequestHandler.run(AJPRequestHandler.java:313) at com.evermind.server.http.AJPRequestHandler.run(AJPRequestHandler.java:199) at oracle.oc4j.network.ServerSocketReadHandler$SafeRunnable.run(ServerSocketReadHandler.java:260) at com.evermind.util.ReleasableResourcePooledExecutor$MyWorker.run(ReleasableResourcePooledExecutor.java:303) at java.lang.Thread.run(Thread.java:662) ## Detail 0 ## java.sql.SQLException: Missing IN or OUT parameter at index:: 1 at oracle.jdbc.driver.OraclePreparedStatement.processCompletedBindRow(OraclePreparedStatement.java:1752) at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3444) at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3493) at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1491) at oracle.jbo.server.QueryCollection.buildResultSet(QueryCollection.java:860) at oracle.jbo.server.QueryCollection.executeQuery(QueryCollection.java:669) at oracle.jbo.server.ViewObjectImpl.executeQueryForCollection(ViewObjectImpl.java:3723) at oracle.jbo.server.OAJboViewObjectImpl.executeQueryForCollection(Unknown Source) at oracle.apps.fnd.framework.server.OAViewObjectImpl.executeQueryForCollection(OAViewObjectImpl.java:4560) at oracle.jbo.server.ViewRowSetImpl.execute(ViewRowSetImpl.java:743) at oracle.jbo.server.ViewRowSetImpl.execute(ViewRowSetImpl.java:688) at oracle.jbo.server.ViewRowSetImpl.getRowCount(ViewRowSetImpl.java:1932) at oracle.jbo.server.ViewObjectImpl.getRowCount(ViewObjectImpl.java:6011) at xxx.oracle.apps.pos.supplier.webui.xxxpoByrCrtStCO.updateVendorSites(xxxpoByrCrtStCO14.java:85) at xxx.oracle.apps.pos.supplier.webui.xxxpoByrCrtStCO.processFormRequest(xxxpoByrCrtStCO.java:46) at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequest(OAWebBeanHelper.java:827) at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processFormRequest(OAWebBeanContainerHelper.java:385) at oracle.apps.fnd.framework.webui.OAPageLayoutHelper.processFormRequest(OAPageLayoutHelper.java:1205) at oracle.apps.fnd.framework.webui.beans.layout.OAPageLayoutBean.processFormRequest(OAPageLayoutBean.java:1579) at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequestChildren(OAWebBeanHelper.java:1039) at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequestChildren(OAWebBeanHelper.java:1005) at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequest(OAWebBeanHelper.java:860) at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processFormRequest(OAWebBeanContainerHelper.java:385) at oracle.apps.fnd.framework.webui.beans.form.OAFormBean.processFormRequest(OAFormBean.java:395) at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequestChildren(OAWebBeanHelper.java:1039) at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequestChildren(OAWebBeanHelper.java:1005) at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequest(OAWebBeanHelper.java:860) at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processFormRequest(OAWebBeanContainerHelper.java:385) at oracle.apps.fnd.framework.webui.beans.OABodyBean.processFormRequest(OABodyBean.java:363) at oracle.apps.fnd.framework.webui.OAPageBean.processFormRequest(OAPageBean.java:2997) at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:1885) at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:550) at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:438) at _OA._jspService(_OA.java:212) at com.orionserver.http.OrionHttpJspPage.service(OrionHttpJspPage.java:59) at oracle.jsp.runtimev2.JspPageTable.service(JspPageTable.java:390) 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.ResourceFilterChain.doFilter(ResourceFilterChain.java:64) at oracle.apps.jtf.base.session.ReleaseResFilter.doFilter(ReleaseResFilter.java:26) at com.evermind.server.http.EvermindFilterChain.doFilter(EvermindFilterChain.java:15) at oracle.apps.fnd.security.AppsServletFilter.doFilter(AppsServletFilter.java:318) at com.evermind.server.http.ServletRequestDispatcher.invoke(ServletRequestDispatcher.java:642) at com.evermind.server.http.ServletRequestDispatcher.forwardInternal(ServletRequestDispatcher.java:391) at com.evermind.server.http.HttpRequestHandler.doProcessRequest(HttpRequestHandler.java:911) at com.evermind.server.http.HttpRequestHandler.processRequest(HttpRequestHandler.java:458) at com.evermind.server.http.AJPRequestHandler.run(AJPRequestHandler.java:313) at com.evermind.server.http.AJPRequestHandler.run(AJPRequestHandler.java:199) at oracle.oc4j.network.ServerSocketReadHandler$SafeRunnable.run(ServerSocketReadHandler.java:260) at com.evermind.util.ReleasableResourcePooledExecutor$MyWorker.run(ReleasableResourcePooledExecutor.java:303) at java.lang.Thread.run(Thread.java:662) java.sql.SQLException: Missing IN or OUT parameter at index:: 1 at oracle.jdbc.driver.OraclePreparedStatement.processCompletedBindRow(OraclePreparedStatement.java:1752) at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3444) at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3493) at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1491) at oracle.jbo.server.QueryCollection.buildResultSet(QueryCollection.java:860) at oracle.jbo.server.QueryCollection.executeQuery(QueryCollection.java:669) at oracle.jbo.server.ViewObjectImpl.executeQueryForCollection(ViewObjectImpl.java:3723) at oracle.jbo.server.OAJboViewObjectImpl.executeQueryForCollection(Unknown Source) at oracle.apps.fnd.framework.server.OAViewObjectImpl.executeQueryForCollection(OAViewObjectImpl.java:4560) at oracle.jbo.server.ViewRowSetImpl.execute(ViewRowSetImpl.java:743) at oracle.jbo.server.ViewRowSetImpl.execute(ViewRowSetImpl.java:688) at oracle.jbo.server.ViewRowSetImpl.getRowCount(ViewRowSetImpl.java:1932) at oracle.jbo.server.ViewObjectImpl.getRowCount(ViewObjectImpl.java:6011) at xxx.oracle.apps.pos.supplier.webui.xxxpoByrCrtStCO.updateVendorSites(xxxpoByrCrtStCO14.java:85) at xxx.oracle.apps.pos.supplier.webui.xxxpoByrCrtStCO14.processFormRequest(xxxpoByrCrtStCO.java:46) at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequest(OAWebBeanHelper.java:827) at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processFormRequest(OAWebBeanContainerHelper.java:385) at oracle.apps.fnd.framework.webui.OAPageLayoutHelper.processFormRequest(OAPageLayoutHelper.java:1205) at oracle.apps.fnd.framework.webui.beans.layout.OAPageLayoutBean.processFormRequest(OAPageLayoutBean.java:1579) at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequestChildren(OAWebBeanHelper.java:1039) at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequestChildren(OAWebBeanHelper.java:1005) at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequest(OAWebBeanHelper.java:860) at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processFormRequest(OAWebBeanContainerHelper.java:385) at oracle.apps.fnd.framework.webui.beans.form.OAFormBean.processFormRequest(OAFormBean.java:395) at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequestChildren(OAWebBeanHelper.java:1039) at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequestChildren(OAWebBeanHelper.java:1005) at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequest(OAWebBeanHelper.java:860) at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processFormRequest(OAWebBeanContainerHelper.java:385) at oracle.apps.fnd.framework.webui.beans.OABodyBean.processFormRequest(OABodyBean.java:363) at oracle.apps.fnd.framework.webui.OAPageBean.processFormRequest(OAPageBean.java:2997) at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:1885) at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:550) at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:438) at _OA._jspService(_OA.java:212) at com.orionserver.http.OrionHttpJspPage.service(OrionHttpJspPage.java:59) at oracle.jsp.runtimev2.JspPageTable.service(JspPageTable.java:390) 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.ResourceFilterChain.doFilter(ResourceFilterChain.java:64) at oracle.apps.jtf.base.session.ReleaseResFilter.doFilter(ReleaseResFilter.java:26) at com.evermind.server.http.EvermindFilterChain.doFilter(EvermindFilterChain.java:15) at oracle.apps.fnd.security.AppsServletFilter.doFilter(AppsServletFilter.java:318) at com.evermind.server.http.ServletRequestDispatcher.invoke(ServletRequestDispatcher.java:642) at com.evermind.server.http.ServletRequestDispatcher.forwardInternal(ServletRequestDispatcher.java:391) at com.evermind.server.http.HttpRequestHandler.doProcessRequest(HttpRequestHandler.java:911) at com.evermind.server.http.HttpRequestHandler.processRequest(HttpRequestHandler.java:458) at com.evermind.server.http.AJPRequestHandler.run(AJPRequestHandler.java:313) at com.evermind.server.http.AJPRequestHandler.run(AJPRequestHandler.java:199) at oracle.oc4j.network.ServerSocketReadHandler$SafeRunnable.run(ServerSocketReadHandler.java:260) at com.evermind.util.ReleasableResourcePooledExecutor$MyWorker.run(ReleasableResourcePooledExecutor.java:303) at java.lang.Thread.run(Thread.java:662) 
      Logout
        • 1. Re: Error:  oracle.apps.fnd.framework.OAException: oracle.jbo.SQLStmtException: JBO-27122: SQL error during statement preparation
          Mahesh112

          hi,

           

          At what time this error is coming, when u r clicking on applyBtn button?

          Put some diagnostic message and check till what point the controller is going and another thing check the VO is attached to the proper AM?

           

          Regards

          Mahesh

          • 2. Re: Error:  oracle.apps.fnd.framework.OAException: oracle.jbo.SQLStmtException: JBO-27122: SQL error during statement preparation
            user632699

            Hi

             

            You're right!  It is when I click on "Apply" button.  The code reaches the part where it says  int count = oaviewobject.getRowCount() and bombs there.

            I included my code above.  I tried to change this code to say  OARow oarow = (OARow)oaviewobject.first(); instead, but it gives same error.  So my conclusion is that any manipulation on that view object is not possible with the calls to find the object that I make.  It does however find that object because it is not null as you can see in my code.  I will check for different AM for that VO.  I read about nested AMs in Anil's book.  Let me know if there is some code that can help me in that investigation.

             

            My objective of this code is to loop through the user's entered values on the create sites by operating unit page and update the VAT code on the newly created supplier site(s).  There can be many in that page if the user's responsibility has access to more than one org.

             

             

            Another question:

            In meantime, even I get this working, I have another related question on this page.  When creating a new supplier site using the above controller, the tax classification code (vat_code in ap_supplier_sites_all) table is not populated.  We checked and there is no public API to update that value so we can not extend controller to do super and then call update API on that supplier site.  Oracle does not support it.  So if we wanted to update it through OA Framework, the only way is to simulate what is happening in the page ByrTaxRptPG where the user can actually enter a tax classification code at the supplier site level and hit save.  This is a different page from the controller that is on the create supplier page, but they share same root AM (atleast so it appears when I click on about page).  Oracle then saves the vat_code on ap_supplier_sites_all.  This page can be navigated to functionally by clicking on Tax and Reporting link and then update button.  The controller for that one is ByrTaxRptStCO.  We put some diagnostics and found that in that controller, no API is called to update the vat_code column on ap_supplier_sites_all table.  Instead, they use a view object set methods and then they commit to the database.  So it happening through the EO directly on that table just like a base table form.

            What is the best way to update this column in OA Framework.  If you can help, please put some sample code, that would be great.

             

            Thank you for looking into these two questions.

             

             


            • 3. Re: Error:  oracle.apps.fnd.framework.OAException: oracle.jbo.SQLStmtException: JBO-27122: SQL error during statement preparation
              Mahesh112

              hi,

               

              Please check the below links , may be it will help you:

               

              https://forums.oracle.com/thread/664574

              https://forums.oracle.com/thread/627603

              https://forums.oracle.com/message/9653465

               

              and please check:

              OAViewObject oaviewobject =  (OAViewObject) applicationModule.findViewObject("CrtSitesVO");   

              check here are you getting the right VO and VO query

                     

              if (oaviewobject != null) {

              put one sop here and check

                        int count = oaviewobject.getRowCount();


              Somewhere the issue with the VO only. if i will get some more option i will share here....


              Regards

              Mahesh

              • 4. Re: Error:  oracle.apps.fnd.framework.OAException: oracle.jbo.SQLStmtException: JBO-27122: SQL error during statement preparation
                user632699

                Hi Mahesh,

                I got the first issue resolved.  You are correct in your thinking.  I was basically finding that view in the root AM and not the child AM.  Once I used the right AM and right class/methods to find it, it worked.  I can now loop through the create sites view.  Here is my new code

                 

                       ApplicationModule applicationModule = oapagecontext.getRootApplicationModule().findApplicationModule("ByrAddrAM");

                           

                        if(oapagecontext.getParameter("applyBtn") != null)

                        {

                       

                            OAViewObject oaviewobject =  (OAViewObject) applicationModule.findViewObject("CrtSitesVO");

                           

                              if (oaviewobject != null) {

                                        OARow oarow = (OARow)oaviewobject.first();

                                       oracle.jbo.domain.Number  orgId2 = (oracle.jbo.domain.Number ) oarow.getAttribute("OrganizationId");

                                                  if(oapagecontext.isLoggingEnabled(2))

                                                                         oapagecontext.writeDiagnostics(this, (new StringBuilder()).append(" Count: ").append(orgId2).toString(), 2);

                                                 

                                    }

                                    else {

                                        if(oapagecontext.isLoggingEnabled(2))

                                                               oapagecontext.writeDiagnostics(this, (new StringBuilder()).append(" Count: ").append("null").toString(), 2);

                                       

                                    }

                 

                 

                Code returns a diagnostic message of Count: 3 where 3 is the org id.

                 

                Now that this hurdle is gone, I have one remaining issue, which is the more difficult one.  How can you influence behaviour from controller or any other object extension to assign the vat_code at supplier site creation or maybe update it later.

                 

                I repeat my question from yesterday.

                When creating a new supplier site using the above controller, the tax classification code (vat_code in ap_supplier_sites_all) table is not populated.  We checked and there is no public API to update that value so we can not extend controller to do super and then call update API on that supplier site.  Oracle does not support it.  So if we wanted to update it through OA Framework, the only way is to simulate what is happening in the page ByrTaxRptPG where the user can actually enter a tax classification code at the supplier site level and hit save.  This is a different page from the controller that is on the create supplier page, but they share same root AM (atleast so it appears when I click on about page).  Oracle then saves the vat_code on ap_supplier_sites_all.  This page can be navigated to functionally by clicking on Tax and Reporting link and then update button.  The controller for that one is ByrTaxRptStCO.  We put some diagnostics and found that in that controller, no API is called to update the vat_code column on ap_supplier_sites_all table.  Instead, they use a view object set methods and then they commit to the database.  So it is happening through the EO directly on that table just like a base table form on Oracle forms. 

                What is the best way to update this column in OA Framework.  If you can help, please put some sample code, that would be great.

                • 5. Re: Error:  oracle.apps.fnd.framework.OAException: oracle.jbo.SQLStmtException: JBO-27122: SQL error during statement preparation
                  Mahesh112

                  hi,

                   

                  I'm not fully sure but you can try below:

                   

                  1. extend the CO for ByrTaxRptPG

                  2. in the extended CO, take the control of the EO based VO, as same  as done in ByrTaxRptStCO and call the same am method that they have used in thsi CO and AM.

                   

                  as you have the same am for the both the pages so you can take the control of the VO in your extended CO and do the need full.

                   

                  Please correct me if i'm wrong...

                   

                  Regards

                  Mahesh

                  • 6. Re: Error:  oracle.apps.fnd.framework.OAException: oracle.jbo.SQLStmtException: JBO-27122: SQL error during statement preparation
                    user632699

                    Hi Mahesh.

                     

                    Thanks for your input, but I think I did not explain my requirements fully.  It is not useful to extend the CO for ByrTaxRptPG because the apply button that triggers this whole thing is on a different page.  I will try to explain it from a different angle.

                    There are two different and separate pages and two controllers under the same AM.

                    One is to create supplier sites when the user hits the apply button (ByrAddrPG), and one is to update the tax code on an existing supplier site when the user hits the "save" button (ByrTaxRptPG).  The two pages are functionally separate and users can navigate to one without the other but they are in same AM.

                     

                    My action needs to trigger when the user hits the apply button (processFormRequest in poByrCrtStCO on the ByrAddrPG).  The original seeded action when the user presses that button is to create a brand new supplier site at that moment without the tax classification code.  My requirement to create the site with a default tax classification code.  So using the controller on the second page is not useful for me, but we can look at that code to get some hints because it is the only code in the whole application that actually updates the tax classification code on an existing supplier site.  As I said, there is no public API otherwise, this solution would be easy.  It would just be creating the site using the (poByrCrtStCO) extended controller by using the Super method and then calling the public API to update vat_code.

                    We have to get creative so one of the ideas but I am not sure how to implement yet is to extend the only possible controller which is poByrCrtStCO, but then find the view in the other page (which I think is SearchSuppAcctSitesVO in ByrTaxRptPG).

                    Once I find it, it will ofcourse be NULL because the user is not in that page and it has not been queried.  Can I populate some parameters on that page to force it to execute its native query on a specific supplier site (the one that was just created when the user hit the apply button) and then call the set methods of that view object to set the vat_code and then simply issue a commit to the database.  I hope you now understand my requirement and if you can think of a solution, please let me know.  Code is always appreciated.

                     

                     

                     

                     

                     

                     

                     

                     

                     

                     


                    • 7. Re: Error:  oracle.apps.fnd.framework.OAException: oracle.jbo.SQLStmtException: JBO-27122: SQL error during statement preparation
                      user632699

                      Hi Mahesh

                       

                      Thanks for your help.  I managed to get this working by using some of the seeded code that is on the second page.

                      • 8. Re: Error:  oracle.apps.fnd.framework.OAException: oracle.jbo.SQLStmtException: JBO-27122: SQL error during statement preparation
                        Mahesh112

                        hi,

                         

                        Congrats.....Please share , how you got resolved, it will help others.

                         

                        Regards

                        Mahesh

                        • 9. Re: Error:  oracle.apps.fnd.framework.OAException: oracle.jbo.SQLStmtException: JBO-27122: SQL error during statement preparation
                          user632699

                          Hi all

                          This is the method I used to update the vendor sites.  It takes a list of vendor site IDs retrieved in previous sections and updates one by one.  Note the Super method is invoked before this method and another method not mentioned here is used to retrieve the vendor site IDs from the operating unit the user checked.

                               private void updateVendorSites(OAPageContext oapagecontext, OAWebBean oawebbean, HashMap<oracle.jbo.domain.Number, String> updateList ) {
                                  if(oapagecontext.isLoggingEnabled(2))
                                      oapagecontext.writeDiagnostics(this, "xxXXXXpoByrCrtStCO - updateVendorSites(OAPageContext, OAWebBean).BEGIN", 2);
                                      System.out.println("xxXXXXpoByrCrtStCO - updateVendorSites(OAPageContext, OAWebBean).BEGIN" );           
                                      try {
                                      // Initalize variables      
                                      oracle.jbo.domain.Number vendorSiteId = null;
                                      oracle.jbo.domain.Number orgId = null;
                                      String vatCode = null;
                                      // THis is a nested AM, so the view for operating units/sites is not in the root AM.  It is in the ByrAddrAM.
                                         // In order to update the tax code on the newly created supplier site, we need to get the AM and VO for the Tax and Reporting Update Tax code page
                                      ApplicationModule maintainpartytaxprofilesam = oapagecontext.getRootApplicationModule().findApplicationModule("MaintainPartyTaxProfilesAM");
                                      SearchSuppAcctSitesVOImpl searchsuppacctsitesvoimpl =  (SearchSuppAcctSitesVOImpl) maintainpartytaxprofilesam.findViewObject("SearchSuppAcctSitesVO");             
                                      Set set = updateList.entrySet();
                                      Iterator i = set.iterator();           
                                      while (i.hasNext()) {
                                            System.out.println("xxXXXXpoByrCrtStCO - updateVendorSites Inside while" );

                                            Map.Entry<oracle.jbo.domain.Number, String> updateCode = (Map.Entry<oracle.jbo.domain.Number, String>) i.next();
                                           // Initialize variables used in the loop to ensure no exchange of data between rows.  This is just a precaution.
                                           vendorSiteId = null;
                                           orgId = null;
                                           vatCode = null;
                                            System.out.println("xxXXXXpoByrCrtStCO - updateVendorSites before cast org id" );

                                           orgId =  updateCode.getKey();
                                            System.out.println("xxXXXXpoByrCrtStCO - updateVendorSites before cast vatCode" );

                                           vatCode = updateCode.getValue();
                                           if(oapagecontext.isLoggingEnabled(2))
                                             oapagecontext.writeDiagnostics(this, (new StringBuilder()).append(" OrgId: ").append(orgId).toString(), 2);
                                            System.out.println(" OrgId: " + orgId.toString());
                                            System.out.println(" vatCode: " + vatCode);

                                            // Get the vendor site ID from separate method
                                            vendorSiteId = getVendorSiteId(oapagecontext, oawebbean, orgId);
                                            // Find the row in the ap_supplier_sites_all table for that vendor Site ID.
                                            searchsuppacctsitesvoimpl.executeVOQuery (vendorSiteId);
                                            SearchSuppAcctSitesVORowImpl searchsuppacctsitesvorowimpl = (SearchSuppAcctSitesVORowImpl)searchsuppacctsitesvoimpl.first();
                                            if(searchsuppacctsitesvorowimpl != null)
                                            {
                                                System.out.println("xxXXXXpoByrCrtStCO - updateVendorSites before set vat code" );

                                                  searchsuppacctsitesvorowimpl.setVatCode(vatCode);
                                            }
                                        }         
                                          oapagecontext.getApplicationModule(oawebbean).getTransaction().commit();
                                      }
                                    catch (OAException oaEx) {          
                                      throw new OAException(oaEx.getMessage(),  (byte)0);
                                    }         
                                }

                          • 10. Re: Error:  oracle.apps.fnd.framework.OAException: oracle.jbo.SQLStmtException: JBO-27122: SQL error during statement preparation
                            DilipG

                            Hi,

                            Please Make it Correct Answer so it will helpful to others.

                             

                            Thanks,

                            Dilip