9 Replies Latest reply: Aug 29, 2013 11:47 AM by Pavan_paa RSS

    JBO-27122: SQL error during statement preparation IN OAF(java.sql.SQLException: ORA-01008: not all variables bound)

    Pavan_paa

      Hi Friends,

       

      I have have extended CO where i have added dynamic where condition to VO,it's throwing error.

       

      Code added in controller :

      public class Custom_HomePageCO extends HomePageCO

      {

        public Custom_HomePageCO()

        {

        }

      public void processRequest(OAPageContext pageContext,OAWebBean webBean)

      {

        super.processRequest(pageContext,webBean);

        System.out.println("NewClase");

        OAApplicationModule am=pageContext.getApplicationModule(webBean);

        System.out.println(am);

        OAApplicationModule am1=(OAApplicationModule)am.findApplicationModule("TrackExpenseReportsAM");

      System.out.println(am1);

        OAViewObject vo=(OAViewObject)am1.findViewObject("TrackExpenseReportsVO");

        System.out.println(vo);

        vo.setWhereClause("REPORT_SUBMITTED_DATE is not null");

          vo.executeQuery();

      }

      public void processFormRequest(OAPageContext pageContext,OAWebBean webBean)

      {

        super.processFormRequest(pageContext,webBean);

         }

      }

       

       

      Error message:

      Exception Details.

      Error Details
             Logout
           


             
            Error Page

             
            Exception Details.
             
      oracle.apps.fnd.framework.OAException: oracle.jbo.SQLStmtException: JBO-27122: SQL error during statement preparation.  Statement: SELECT * FROM (SELECT
      AI.DESCRIPTION PURPOSE,
      AI.INVOICE_CURRENCY_CODE CURRENCY_CODE,
      AI.INVOICE_DATE REPORT_DATE,
      AERH.REPORT_SUBMITTED_DATE REPORT_SUBMITTED_DATE,
      AI.INVOICE_NUM REPORT_NUMBER,
      TO_CHAR(decode(nvl(AI.AMT_DUE_CCARD_COMPANY, AERH.AMT_DUE_CCARD_COMPANY) + nvl(AI.AMT_DUE_EMPLOYEE, AERH.AMT_DUE_EMPLOYEE) + nvl(AERH.MAXIMUM_AMOUNT_TO_APPLY,0),
                       0, decode(AI.CANCELLED_DATE,
                                   null, APS.GROSS_AMOUNT,
                                   AERH.TOTAL),
                     nvl(AI.AMT_DUE_CCARD_COMPANY, AERH.AMT_DUE_CCARD_COMPANY) + nvl(AI.AMT_DUE_EMPLOYEE, AERH.AMT_DUE_EMPLOYEE) + nvl(AERH.MAXIMUM_AMOUNT_TO_APPLY,0)) ,  
              FND_CURRENCY_CACHE.GET_FORMAT_MASK
              (AI.INVOICE_CURRENCY_CODE, 30)) ||' '|| AI.INVOICE_CURRENCY_CODE REPORT_TOTAL_CURRENCY,
      TO_CHAR(decode(nvl(AI.AMT_DUE_CCARD_COMPANY, AERH.AMT_DUE_CCARD_COMPANY) + nvl(AI.AMT_DUE_EMPLOYEE, AERH.AMT_DUE_EMPLOYEE) + nvl(AERH.MAXIMUM_AMOUNT_TO_APPLY,0),
                       0, decode(AI.CANCELLED_DATE,
                                   null, APS.GROSS_AMOUNT,
                                   AERH.TOTAL),
                     nvl(AI.AMT_DUE_CCARD_COMPANY, AERH.AMT_DUE_CCARD_COMPANY) + nvl(AI.AMT_DUE_EMPLOYEE, AERH.AMT_DUE_EMPLOYEE) + nvl(AERH.MAXIMUM_AMOUNT_TO_APPLY,0)) ,  
              FND_CURRENCY_CACHE.GET_FORMAT_MASK
              (AI.INVOICE_CURRENCY_CODE, 30))
      REPORT_TOTAL,
      P.PERSON_ID EMPLOYEE_ID,
      AERH.REPORT_HEADER_ID REPORT_HEADER_ID,
      P.FULL_NAME FULL_NAME ,
      DECODE(AI.CANCELLED_DATE,null,
                                nvl(aerh.expense_status_code, DECODE(APS.GROSS_AMOUNT ,0,'PAID',
                                      decode(AI.Payment_status_flag,'Y','PAID',
                                                              'N','INVOICED',
                                                              'P','PARPAID',NULL))),
                                              'CANCELLED') STATUS_CODE,
      AERH.source SOURCE,
      NULL CURRENT_APPROVER,
      ROUND(sysdate - AI.LAST_UPDATE_DATE) DAYS_SINCE_ACTIVITY,
      AERH.RECEIPTS_STATUS RECEIPTS_STATUS_CODE,
      AERH.HOLDING_REPORT_HEADER_ID,
      AI.VENDOR_ID VENDOR_ID,
      AERH.AMT_DUE_CCARD_COMPANY AMT_DUE_CCARD_COMPANY,
      AERH.AMT_DUE_EMPLOYEE AMT_DUE_EMPLOYEE,         
      'CurrentApproverName' CURRENT_APPROVER_SWITCHER,
      to_char(AERH.LAST_UPDATE_DATE, 'DD-MON-RRRR HH:MI:SS'),
      AI.INVOICE_ID INVOICE_ID
      FROM
             AK_WEB_USER_SEC_ATTR_VALUES A,
             PO_VENDORS PV,
             AP_INVOICES AI,
             AP_EXPENSE_REPORT_HEADERS AERH,
             PER_PEOPLE_X P,
             AP_PAYMENT_SCHEDULES APS
      WHERE  AI.INVOICE_ID= APS.INVOICE_ID
      AND    AI.INVOICE_ID = AERH.VOUCHNO(+)
      AND AI.INVOICE_TYPE_LOOKUP_CODE||'' = 'EXPENSE REPORT'
      AND A.ATTRIBUTE_CODE = 'ICX_HR_PERSON_ID'
      AND PV.EMPLOYEE_ID = A.NUMBER_VALUE
      AND A.WEB_USER_ID = :1
      AND P.PERSON_ID = PV.EMPLOYEE_ID
      AND PV.VENDOR_ID = AI.VENDOR_ID
      AND DECODE (AI.PAYMENT_STATUS_FLAG,
               'Y', sysdate - AI.LAST_UPDATE_DATE,
              decode(APS.GROSS_AMOUNT , 0 ,sysdate - AI.LAST_UPDATE_DATE,0)
                )  <= 30
      AND (AERH.SOURCE <> 'Both Pay' OR AERH.REPORT_HEADER_ID IS NULL)       
      UNION
      SELECT
      AI.DESCRIPTION PURPOSE,
      AI.INVOICE_CURRENCY_CODE CURRENCY_CODE,
      AI.INVOICE_DATE REPORT_DATE,
      AERH.REPORT_SUBMITTED_DATE REPORT_SUBMITTED_DATE,
      AI.INVOICE_NUM REPORT_NUMBER,
      TO_CHAR(decode(nvl(AI.AMT_DUE_CCARD_COMPANY, AERH.AMT_DUE_CCARD_COMPANY) + nvl(AI.AMT_DUE_EMPLOYEE, AERH.AMT_DUE_EMPLOYEE) + nvl(AERH.MAXIMUM_AMOUNT_TO_APPLY,0),
                       0, decode(AI.CANCELLED_DATE,
                                   null, APS.GROSS_AMOUNT,
                                   AERH.TOTAL),
                     nvl(AI.AMT_DUE_CCARD_COMPANY, AERH.AMT_DUE_CCARD_COMPANY) + nvl(AI.AMT_DUE_EMPLOYEE, AERH.AMT_DUE_EMPLOYEE) + nvl(AERH.MAXIMUM_AMOUNT_TO_APPLY,0)) ,  
              FND_CURRENCY_CACHE.GET_FORMAT_MASK
              (AI.INVOICE_CURRENCY_CODE, 30)) ||' '|| AI.INVOICE_CURRENCY_CODE REPORT_TOTAL_CURRENCY,
      TO_CHAR(decode(nvl(AI.AMT_DUE_CCARD_COMPANY, AERH.AMT_DUE_CCARD_COMPANY) + nvl(AI.AMT_DUE_EMPLOYEE, AERH.AMT_DUE_EMPLOYEE) + nvl(AERH.MAXIMUM_AMOUNT_TO_APPLY,0),
                       0, decode(AI.CANCELLED_DATE,
                                   null, APS.GROSS_AMOUNT,
                                   AERH.TOTAL),
                     nvl(AI.AMT_DUE_CCARD_COMPANY, AERH.AMT_DUE_CCARD_COMPANY) + nvl(AI.AMT_DUE_EMPLOYEE, AERH.AMT_DUE_EMPLOYEE) + nvl(AERH.MAXIMUM_AMOUNT_TO_APPLY,0)),  
              FND_CURRENCY_CACHE.GET_FORMAT_MASK
              (AI.INVOICE_CURRENCY_CODE, 30))
      REPORT_TOTAL,
      P.PERSON_ID EMPLOYEE_ID,
      AERH.REPORT_HEADER_ID REPORT_HEADER_ID,
      P.FULL_NAME FULL_NAME ,
      DECODE(AI.CANCELLED_DATE,null,
                                nvl(aerh.expense_status_code, DECODE(APS.GROSS_AMOUNT ,0,'PAID',
                                      decode(AI.Payment_status_flag,'Y','PAID',
                                                              'N','INVOICED',
                                                              'P','PARPAID',NULL))),
                                              'CANCELLED') STATUS_CODE,
      AERH.source SOURCE,
      NULL CURRENT_APPROVER,
      ROUND(sysdate - AI.LAST_UPDATE_DATE) DAYS_SINCE_ACTIVITY,
      AERH.RECEIPTS_STATUS RECEIPTS_STATUS_CODE,
      AERH.HOLDING_REPORT_HEADER_ID,
      AI.VENDOR_ID VENDOR_ID,
      AERH.AMT_DUE_CCARD_COMPANY AMT_DUE_CCARD_COMPANY,
      AERH.AMT_DUE_EMPLOYEE AMT_DUE_EMPLOYEE,         
      'CurrentApproverName' CURRENT_APPROVER_SWITCHER,
      to_char(AERH.LAST_UPDATE_DATE, 'DD-MON-RRRR HH:MI:SS'),
      AI.INVOICE_ID INVOICE_ID
      FROM
             AK_WEB_USER_SEC_ATTR_VALUES A,
             PO_VENDORS PV,
             AP_INVOICES AI,
             AP_EXPENSE_REPORT_HEADERS AERH,
             PER_PEOPLE_X P,
             AP_PAYMENT_SCHEDULES APS
      WHERE  AI.INVOICE_ID= APS.INVOICE_ID
      AND    AI.INVOICE_ID = AERH.VOUCHNO(+)
      AND AI.INVOICE_TYPE_LOOKUP_CODE||'' in ('STANDARD','MIXED')
      AND A.ATTRIBUTE_CODE = 'ICX_HR_PERSON_ID'
      AND AI.PAID_ON_BEHALF_EMPLOYEE_ID = A.NUMBER_VALUE
      AND A.WEB_USER_ID = :2
      AND P.PERSON_ID = AI.PAID_ON_BEHALF_EMPLOYEE_ID
      AND PV.VENDOR_ID = AI.VENDOR_ID
      AND DECODE (AI.PAYMENT_STATUS_FLAG,
               'Y', sysdate - AI.LAST_UPDATE_DATE,
              decode(APS.GROSS_AMOUNT , 0 ,sysdate - AI.LAST_UPDATE_DATE,0)
              ) <= 30
      AND (AERH.SOURCE <> 'Both Pay' OR AERH.REPORT_HEADER_ID IS NULL)            
      UNION ALL
      SELECT
      AERH.DESCRIPTION PURPOSE,
      AERH.DEFAULT_CURRENCY_CODE CURRENCY_CODE,
      AERH.WEEK_END_DATE REPORT_DATE,
      AERH.REPORT_SUBMITTED_DATE REPORT_SUBMITTED_DATE,
      AERH.INVOICE_NUM REPORT_NUMBER,
      TO_CHAR(nvl(AERH.AMT_DUE_CCARD_COMPANY+AERH.AMT_DUE_EMPLOYEE+nvl(AERH.MAXIMUM_AMOUNT_TO_APPLY,0),AERH.TOTAL),FND_CURRENCY_CACHE.GET_FORMAT_MASK
        (AERH.DEFAULT_CURRENCY_CODE,30)) ||' '|| AERH.DEFAULT_CURRENCY_CODE REPORT_TOTAL_CURRENCY,
      TO_CHAR(nvl(AERH.AMT_DUE_CCARD_COMPANY+AERH.AMT_DUE_EMPLOYEE+nvl(AERH.MAXIMUM_AMOUNT_TO_APPLY,0),AERH.TOTAL),FND_CURRENCY_CACHE.GET_FORMAT_MASK
        (AERH.DEFAULT_CURRENCY_CODE,30)) REPORT_TOTAL,
      PER_EMPLOYEE.PERSON_ID EMPLOYEE_ID,
      AERH.REPORT_HEADER_ID REPORT_HEADER_ID,
      PER_EMPLOYEE.FULL_NAME FULL_NAME,
      NVL(AERH.expense_status_code,
        AP_WEB_OA_ACTIVE_PKG.GetReportStatusCode(AERH.Source, AERH.Workflow_approved_flag,
        AERH.report_header_id, 'Y', 'N')) STATUS_CODE,
      AERH.source SOURCE,
      NVL (PER_APPROVER.full_name, AP_WEB_OA_ACTIVE_PKG.GetCurrentApprover(AERH.Source,
              AERH.Workflow_approved_flag, AERH.report_header_id, AERH.expense_status_code)) CURRENT_APPROVER,
      ROUND(NVL(sysdate - AERH.EXPENSE_LAST_STATUS_DATE,
                sysdate - AERH.LAST_UPDATE_DATE)) DAYS_SINCE_ACTIVITY,
      AERH.RECEIPTS_STATUS RECEIPTS_STATUS_CODE,
      AERH.HOLDING_REPORT_HEADER_ID,
      0 VENDOR_ID,
      AERH.AMT_DUE_CCARD_COMPANY AMT_DUE_CCARD_COMPANY,
      AERH.AMT_DUE_EMPLOYEE AMT_DUE_EMPLOYEE,  
      DECODE(AERH.expense_current_approver_id,
      -99999, 'AMEMultipleApprovers',
      decode(PER_APPROVER.full_name,
        null,'CurrentApproverName','AMESingleApprover')) CURRENT_APPROVER_SWITCHER,
      to_char(AERH.LAST_UPDATE_DATE, 'DD-MON-RRRR HH:MI:SS'),
      -1 INVOICE_ID
      FROM
             AK_WEB_USER_SEC_ATTR_VALUES A,
             AP_EXPENSE_REPORT_HEADERS AERH,
             PER_PEOPLE_X PER_EMPLOYEE,
             PER_PEOPLE_X PER_APPROVER
      WHERE  AERH.VOUCHNO +0 =0
      AND A.ATTRIBUTE_CODE = 'ICX_HR_PERSON_ID'
      AND AERH.EMPLOYEE_ID = A.NUMBER_VALUE
      AND A.WEB_USER_ID = :3
      AND PER_EMPLOYEE.PERSON_ID = AERH.EMPLOYEE_ID
      AND (AERH.Source <> 'NonValidatedWebExpense'
           OR AERH.Workflow_approved_flag IS NULL)
      AND AERH.expense_current_approver_id = PER_APPROVER.person_id
      AND decode(AERH.total,0,ROUND(NVL(sysdate - AERH.EXPENSE_LAST_STATUS_DATE,sysdate - AERH.LAST_UPDATE_DATE)),30) <= 30
      AND AERH.SOURCE <> 'Both Pay'
      UNION
      SELECT
      AERH.DESCRIPTION PURPOSE,
      AERH.DEFAULT_CURRENCY_CODE CURRENCY_CODE,
      AERH.WEEK_END_DATE REPORT_DATE,
      AERH.REPORT_SUBMITTED_DATE REPORT_SUBMITTED_DATE,
      AERH.INVOICE_NUM REPORT_NUMBER,
      TO_CHAR(nvl(AERH.AMT_DUE_CCARD_COMPANY+AERH.AMT_DUE_EMPLOYEE+nvl(AERH.MAXIMUM_AMOUNT_TO_APPLY,0),AERH.TOTAL),FND_CURRENCY_CACHE.GET_FORMAT_MASK
        (AERH.DEFAULT_CURRENCY_CODE,30)) ||' '|| AERH.DEFAULT_CURRENCY_CODE REPORT_TOTAL_CURRENCY,
      TO_CHAR(nvl(AERH.AMT_DUE_CCARD_COMPANY+AERH.AMT_DUE_EMPLOYEE+nvl(AERH.MAXIMUM_AMOUNT_TO_APPLY,0),AERH.TOTAL),FND_CURRENCY_CACHE.GET_FORMAT_MASK
        (AERH.DEFAULT_CURRENCY_CODE,30)) REPORT_TOTAL,
      PER_EMPLOYEE.PERSON_ID EMPLOYEE_ID,
      AERH.REPORT_HEADER_ID REPORT_HEADER_ID,
      PER_EMPLOYEE.FULL_NAME FULL_NAME,
      NVL(AERH.expense_status_code,
        AP_WEB_OA_ACTIVE_PKG.GetReportStatusCode(AERH.Source, AERH.Workflow_approved_flag,
        AERH.report_header_id,'Y','N')) STATUS_CODE,
      AERH.source SOURCE,
      NVL (PER_APPROVER.full_name, AP_WEB_OA_ACTIVE_PKG.GetCurrentApprover(AERH.Source,
              AERH.Workflow_approved_flag, AERH.report_header_id, AERH.expense_status_code)) CURRENT_APPROVER,
      ROUND(NVL(sysdate - AERH.EXPENSE_LAST_STATUS_DATE,
                sysdate - AERH.LAST_UPDATE_DATE)) DAYS_SINCE_ACTIVITY,
      AERH.RECEIPTS_STATUS RECEIPTS_STATUS_CODE,
      AERH.HOLDING_REPORT_HEADER_ID,
      0 VENDOR_ID,
      AERH.AMT_DUE_CCARD_COMPANY AMT_DUE_CCARD_COMPANY,
      AERH.AMT_DUE_EMPLOYEE AMT_DUE_EMPLOYEE,  
      DECODE(AERH.expense_current_approver_id,
      -99999, 'AMEMultipleApprovers',
      decode(PER_APPROVER.full_name,
        null,'CurrentApproverName','AMESingleApprover')) CURRENT_APPROVER_SWITCHER,
      to_char(AERH.LAST_UPDATE_DATE, 'DD-MON-RRRR HH:MI:SS'),
      -1 INVOICE_ID
      FROM
             AK_WEB_USER_SEC_ATTR_VALUES A,
             AP_EXPENSE_REPORT_HEADERS AERH,
             PER_PEOPLE_X PER_EMPLOYEE,
             PER_PEOPLE_X PER_APPROVER
      WHERE  AERH.VOUCHNO +0=0
      AND A.ATTRIBUTE_CODE = 'ICX_HR_PERSON_ID'
      AND AERH.PAID_ON_BEHALF_EMPLOYEE_ID = A.NUMBER_VALUE
      AND A.WEB_USER_ID = :4
      AND PER_EMPLOYEE.PERSON_ID = PAID_ON_BEHALF_EMPLOYEE_ID
      AND AERH.EMPLOYEE_ID IS NULL
      AND (AERH.Source <> 'NonValidatedWebExpense'
           OR AERH.Workflow_approved_flag IS NULL)
      AND AERH.expense_current_approver_id = PER_APPROVER.person_id
      AND decode(AERH.total,0,ROUND(NVL(sysdate - AERH.EXPENSE_LAST_STATUS_DATE,sysdate - AERH.LAST_UPDATE_DATE)),30) <= 30
      AND AERH.SOURCE <> 'Both Pay'
      UNION ALL
      /* This select is for invoice imported reports by contingent workers */
      SELECT
      AI.DESCRIPTION PURPOSE,
      AI.INVOICE_CURRENCY_CODE CURRENCY_CODE,
      AI.INVOICE_DATE REPORT_DATE,
      AERH.REPORT_SUBMITTED_DATE REPORT_SUBMITTED_DATE,
      AI.INVOICE_NUM REPORT_NUMBER,
      TO_CHAR(decode(nvl(AI.AMT_DUE_CCARD_COMPANY, AERH.AMT_DUE_CCARD_COMPANY) + nvl(AI.AMT_DUE_EMPLOYEE, AERH.AMT_DUE_EMPLOYEE) + nvl(AERH.MAXIMUM_AMOUNT_TO_APPLY,0),
                       0, decode(AI.CANCELLED_DATE,
                                   null, APS.GROSS_AMOUNT,
                                   AERH.TOTAL),
                     nvl(AI.AMT_DUE_CCARD_COMPANY, AERH.AMT_DUE_CCARD_COMPANY) + nvl(AI.AMT_DUE_EMPLOYEE, AERH.AMT_DUE_EMPLOYEE) + nvl(AERH.MAXIMUM_AMOUNT_TO_APPLY,0)) ,  
              FND_CURRENCY_CACHE.GET_FORMAT_MASK
              (AI.INVOICE_CURRENCY_CODE, 30)) ||' '|| AI.INVOICE_CURRENCY_CODE REPORT_TOTAL_CURRENCY,
      TO_CHAR(decode(nvl(AI.AMT_DUE_CCARD_COMPANY, AERH.AMT_DUE_CCARD_COMPANY) + nvl(AI.AMT_DUE_EMPLOYEE, AERH.AMT_DUE_EMPLOYEE) + nvl(AERH.MAXIMUM_AMOUNT_TO_APPLY,0),
                       0, decode(AI.CANCELLED_DATE,
                                   null, APS.GROSS_AMOUNT,
                                   AERH.TOTAL),
                     nvl(AI.AMT_DUE_CCARD_COMPANY, AERH.AMT_DUE_CCARD_COMPANY) + nvl(AI.AMT_DUE_EMPLOYEE, AERH.AMT_DUE_EMPLOYEE) + nvl(AERH.MAXIMUM_AMOUNT_TO_APPLY,0)) ,  
              FND_CURRENCY_CACHE.GET_FORMAT_MASK
              (AI.INVOICE_CURRENCY_CODE, 30))
      REPORT_TOTAL,
      P.PERSON_ID EMPLOYEE_ID,
      AERH.REPORT_HEADER_ID REPORT_HEADER_ID,
      P.FULL_NAME FULL_NAME ,
      DECODE(AI.CANCELLED_DATE,null,
                                nvl(aerh.expense_status_code, DECODE(APS.GROSS_AMOUNT ,0,'PAID',
                                      decode(AI.Payment_status_flag,'Y','PAID',
                                                              'N','INVOICED',
                                                              'P','PARPAID',NULL))),
                                              'CANCELLED') STATUS_CODE,
      AERH.source SOURCE,
      NULL CURRENT_APPROVER,
      ROUND(sysdate - AI.LAST_UPDATE_DATE) DAYS_SINCE_ACTIVITY,
      AERH.RECEIPTS_STATUS RECEIPTS_STATUS_CODE,
      AERH.HOLDING_REPORT_HEADER_ID,
      AI.VENDOR_ID VENDOR_ID,
      AERH.AMT_DUE_CCARD_COMPANY AMT_DUE_CCARD_COMPANY,
      AERH.AMT_DUE_EMPLOYEE AMT_DUE_EMPLOYEE,          
      'CurrentApproverName' CURRENT_APPROVER_SWITCHER,
      to_char(AERH.LAST_UPDATE_DATE, 'DD-MON-RRRR HH:MI:SS'),
      AI.INVOICE_ID INVOICE_ID
      FROM
             AK_WEB_USER_SEC_ATTR_VALUES A,
             AP_INVOICES AI,
             AP_EXPENSE_REPORT_HEADERS AERH,
             PER_PEOPLE_X P,
             AP_PAYMENT_SCHEDULES APS
      WHERE  AI.INVOICE_ID= APS.INVOICE_ID
      AND    AI.INVOICE_ID = AERH.VOUCHNO(+)
      AND AI.INVOICE_TYPE_LOOKUP_CODE||'' = 'EXPENSE REPORT'
      AND A.ATTRIBUTE_CODE = 'ICX_HR_PERSON_ID'
      AND AI.PAID_ON_BEHALF_EMPLOYEE_ID = A.NUMBER_VALUE
      AND A.WEB_USER_ID = :5
      AND P.PERSON_ID = AI.PAID_ON_BEHALF_EMPLOYEE_ID
      AND AP_WEB_DB_HR_INT_PKG.IsPersonCwk(AI.PAID_ON_BEHALF_EMPLOYEE_ID)='Y'
      AND DECODE (AI.PAYMENT_STATUS_FLAG,
               'Y', sysdate - AI.LAST_UPDATE_DATE,
              decode(APS.GROSS_AMOUNT , 0 ,sysdate - AI.LAST_UPDATE_DATE,0)
                )  <= 30
      AND (AERH.SOURCE <> 'Both Pay' OR AERH.REPORT_HEADER_ID IS NULL)) QRSLT  WHERE (REPORT_SUBMITTED_DATE is not null)
      at oracle.apps.fnd.framework.OAException.wrapperException(OAException.java:891)
      at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequest(OAWebBeanHelper.java:603)
      at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processRequest(OAWebBeanContainerHelper.java:247)
      at oracle.apps.fnd.framework.webui.OAPageLayoutHelper.processRequest(OAPageLayoutHelper.java:1136)
      at oracle.apps.fnd.framework.webui.beans.layout.OAPageLayoutBean.processRequest(OAPageLayoutBean.java:1569)
      at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequestChildren(OAWebBeanHelper.java:959)
      at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequestChildren(OAWebBeanHelper.java:926)
      at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequest(OAWebBeanHelper.java:646)
      at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processRequest(OAWebBeanContainerHelper.java:247)
      at oracle.apps.fnd.framework.webui.beans.form.OAFormBean.processRequest(OAFormBean.java:385)
      at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequestChildren(OAWebBeanHelper.java:959)
      at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequestChildren(OAWebBeanHelper.java:926)
      at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequest(OAWebBeanHelper.java:646)
      at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processRequest(OAWebBeanContainerHelper.java:247)
      at oracle.apps.fnd.framework.webui.beans.OABodyBean.processRequest(OABodyBean.java:353)
      at oracle.apps.fnd.framework.webui.OAPageBean.processRequest(OAPageBean.java:2360)
      at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:1759)
      at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:511)
      at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:432)
      at _OA._jspService(OA.jsp:33)
      at com.orionserver.http.OrionHttpJspPage.service(OrionHttpJspPage.java:56)
      at oracle.jsp.runtimev2.JspPageTable.service(JspPageTable.java:317)
      at oracle.jsp.runtimev2.JspServlet.internalService(JspServlet.java:465)
      at oracle.jsp.runtimev2.JspServlet.service(JspServlet.java:379)
      at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
      at com.evermind.server.http.ServletRequestDispatcher.invoke(ServletRequestDispatcher.java:727)
      at com.evermind.server.http.ServletRequestDispatcher.forwardInternal(ServletRequestDispatcher.java:306)
      at com.evermind.server.http.HttpRequestHandler.processRequest(HttpRequestHandler.java:767)
      at com.evermind.server.http.HttpRequestHandler.run(HttpRequestHandler.java:259)
      at com.evermind.server.http.HttpRequestHandler.run(HttpRequestHandler.java:106)
      at EDU.oswego.cs.dl.util.concurrent.PooledExecutor$Worker.run(PooledExecutor.java:803)
      at java.lang.Thread.run(Thread.java:534)
      ## Detail 0 ##
      java.sql.SQLException: ORA-01008: not all variables bound

      at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
      at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)
      at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:583)
      at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1986)
      at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java:1144)
      at oracle.jdbc.driver.OracleStatement.doExecuteQuery(OracleStatement.java:2548)
      at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2933)
      at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:650)
      at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:578)
      at oracle.jbo.server.QueryCollection.buildResultSet(QueryCollection.java:631)
      at oracle.jbo.server.QueryCollection.executeQuery(QueryCollection.java:518)
      at oracle.jbo.server.ViewObjectImpl.executeQueryForCollection(ViewObjectImpl.java:3375)
      at oracle.jbo.server.OAJboViewObjectImpl.executeQueryForCollection(OAJboViewObjectImpl.java:828)
      at oracle.apps.fnd.framework.server.OAViewObjectImpl.executeQueryForCollection(OAViewObjectImpl.java:4525)
      at oracle.jbo.server.ViewRowSetImpl.execute(ViewRowSetImpl.java:574)
      at oracle.jbo.server.ViewRowSetImpl.execute(ViewRowSetImpl.java:544)
      at oracle.jbo.server.ViewRowSetImpl.executeDetailQuery(ViewRowSetImpl.java:619)
      at oracle.jbo.server.ViewObjectImpl.executeDetailQuery(ViewObjectImpl.java:3339)
      at oracle.jbo.server.ViewObjectImpl.executeQuery(ViewObjectImpl.java:3326)
      at oracle.apps.fnd.framework.server.OAViewObjectImpl.executeQuery(OAViewObjectImpl.java:441)
      at oracle.apps.ap.oie.webui.Custom_HomePageCO.processRequest(Custom_HomePageCO.java:26)
      at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequest(OAWebBeanHelper.java:587)
      at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processRequest(OAWebBeanContainerHelper.java:247)
      at oracle.apps.fnd.framework.webui.OAPageLayoutHelper.processRequest(OAPageLayoutHelper.java:1136)
      at oracle.apps.fnd.framework.webui.beans.layout.OAPageLayoutBean.processRequest(OAPageLayoutBean.java:1569)
      at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequestChildren(OAWebBeanHelper.java:959)
      at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequestChildren(OAWebBeanHelper.java:926)
      at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequest(OAWebBeanHelper.java:646)
      at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processRequest(OAWebBeanContainerHelper.java:247)
      at oracle.apps.fnd.framework.webui.beans.form.OAFormBean.processRequest(OAFormBean.java:385)
      at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequestChildren(OAWebBeanHelper.java:959)
      at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequestChildren(OAWebBeanHelper.java:926)
      at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequest(OAWebBeanHelper.java:646)
      at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processRequest(OAWebBeanContainerHelper.java:247)
      at oracle.apps.fnd.framework.webui.beans.OABodyBean.processRequest(OABodyBean.java:353)
      at oracle.apps.fnd.framework.webui.OAPageBean.processRequest(OAPageBean.java:2360)
      at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:1759)
      at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:511)
      at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:432)
      at _OA._jspService(OA.jsp:33)
      at com.orionserver.http.OrionHttpJspPage.service(OrionHttpJspPage.java:56)
      at oracle.jsp.runtimev2.JspPageTable.service(JspPageTable.java:317)
      at oracle.jsp.runtimev2.JspServlet.internalService(JspServlet.java:465)
      at oracle.jsp.runtimev2.JspServlet.service(JspServlet.java:379)
      at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
      at com.evermind.server.http.ServletRequestDispatcher.invoke(ServletRequestDispatcher.java:727)
      at com.evermind.server.http.ServletRequestDispatcher.forwardInternal(ServletRequestDispatcher.java:306)
      at com.evermind.server.http.HttpRequestHandler.processRequest(HttpRequestHandler.java:767)
      at com.evermind.server.http.HttpRequestHandler.run(HttpRequestHandler.java:259)
      at com.evermind.server.http.HttpRequestHandler.run(HttpRequestHandler.java:106)
      at EDU.oswego.cs.dl.util.concurrent.PooledExecutor$Worker.run(PooledExecutor.java:803)
      at java.lang.Thread.run(Thread.java:534)
      java.sql.SQLException: ORA-01008: not all variables bound

      at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
      at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)
      at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:583)
      at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1986)
      at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java:1144)
      at oracle.jdbc.driver.OracleStatement.doExecuteQuery(OracleStatement.java:2548)
      at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2933)
      at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:650)
      at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:578)
      at oracle.jbo.server.QueryCollection.buildResultSet(QueryCollection.java:631)
      at oracle.jbo.server.QueryCollection.executeQuery(QueryCollection.java:518)
      at oracle.jbo.server.ViewObjectImpl.executeQueryForCollection(ViewObjectImpl.java:3375)
      at oracle.jbo.server.OAJboViewObjectImpl.executeQueryForCollection(OAJboViewObjectImpl.java:828)
      at oracle.apps.fnd.framework.server.OAViewObjectImpl.executeQueryForCollection(OAViewObjectImpl.java:4525)
      at oracle.jbo.server.ViewRowSetImpl.execute(ViewRowSetImpl.java:574)
      at oracle.jbo.server.ViewRowSetImpl.execute(ViewRowSetImpl.java:544)
      at oracle.jbo.server.ViewRowSetImpl.executeDetailQuery(ViewRowSetImpl.java:619)
      at oracle.jbo.server.ViewObjectImpl.executeDetailQuery(ViewObjectImpl.java:3339)
      at oracle.jbo.server.ViewObjectImpl.executeQuery(ViewObjectImpl.java:3326)
      at oracle.apps.fnd.framework.server.OAViewObjectImpl.executeQuery(OAViewObjectImpl.java:441)
      at oracle.apps.ap.oie.webui.Custom_HomePageCO.processRequest(Custom_HomePageCO.java:26)
      at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequest(OAWebBeanHelper.java:587)
      at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processRequest(OAWebBeanContainerHelper.java:247)
      at oracle.apps.fnd.framework.webui.OAPageLayoutHelper.processRequest(OAPageLayoutHelper.java:1136)
      at oracle.apps.fnd.framework.webui.beans.layout.OAPageLayoutBean.processRequest(OAPageLayoutBean.java:1569)
      at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequestChildren(OAWebBeanHelper.java:959)
      at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequestChildren(OAWebBeanHelper.java:926)
      at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequest(OAWebBeanHelper.java:646)
      at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processRequest(OAWebBeanContainerHelper.java:247)
      at oracle.apps.fnd.framework.webui.beans.form.OAFormBean.processRequest(OAFormBean.java:385)
      at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequestChildren(OAWebBeanHelper.java:959)
      at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequestChildren(OAWebBeanHelper.java:926)
      at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequest(OAWebBeanHelper.java:646)
      at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processRequest(OAWebBeanContainerHelper.java:247)
      at oracle.apps.fnd.framework.webui.beans.OABodyBean.processRequest(OABodyBean.java:353)
      at oracle.apps.fnd.framework.webui.OAPageBean.processRequest(OAPageBean.java:2360)
      at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:1759)
      at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:511)
      at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:432)
      at _OA._jspService(OA.jsp:33)
      at com.orionserver.http.OrionHttpJspPage.service(OrionHttpJspPage.java:56)
      at oracle.jsp.runtimev2.JspPageTable.service(JspPageTable.java:317)
      at oracle.jsp.runtimev2.JspServlet.internalService(JspServlet.java:465)
      at oracle.jsp.runtimev2.JspServlet.service(JspServlet.java:379)
      at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
      at com.evermind.server.http.ServletRequestDispatcher.invoke(ServletRequestDispatcher.java:727)
      at com.evermind.server.http.ServletRequestDispatcher.forwardInternal(ServletRequestDispatcher.java:306)
      at com.evermind.server.http.HttpRequestHandler.processRequest(HttpRequestHandler.java:767)
      at com.evermind.server.http.HttpRequestHandler.run(HttpRequestHandler.java:259)
      at com.evermind.server.http.HttpRequestHandler.run(HttpRequestHandler.java:106)
      at EDU.oswego.cs.dl.util.concurrent.PooledExecutor$Worker.run(PooledExecutor.java:803)
      at java.lang.Thread.run(Thread.java:534)

       

       

      I have took the sql from error message and i tried executing the same in toad... i was able execute it smoothly... i dot know why its working while running the page

       

       

      Please help me guys...

       

      Thanks,

      Pavan