6 Replies Latest reply on Aug 1, 2011 6:16 AM by 542656

    customizing the oaf page

    Kumar K
      Hello all,

      I'm trying to run the seeded page in my jdeveloper. When i run the page, i see the page without any errors but i don't see any data in that page. When i tried to debug, the viewobjects bind parameters are getting null values. I'm not sure why its happening.

      Anyone with any suggestions would be really helpful.


      Thank You
      KK
        • 1. Re: customizing the oaf page
          Kumar K
          Below is the error message. Please check.

          Exception Details.

          oracle.apps.fnd.framework.OAException: oracle.jbo.SQLStmtException: JBO-27122: SQL error during statement preparation. Statement: SELECT * FROM (select decode(fnd_profile.value('BEN_DISPLAY_EMPLOYEE_NAME'),'FN',ppf.full_name, ppf.first_name||' '|| ppf.last_name) full_name,
          hr_person_type_usage_info.get_user_person_type(ppf.effective_end_date, ppf.person_id) person_type,
          Decode(paf.assignment_type,
                    'C', hr_general.decode_lookup('CWK_ASG_CATEGORY', paf.employment_category),
          hr_general.decode_lookup('EMP_CAT', paf.employment_category)
          )employment_category,
          ppf.start_date, ppf.original_date_of_hire,
          decode(paf.period_of_service_id, null, pop.date_start, pos.date_start) date_start,
          decode(paf.period_of_service_id, null, pop.actual_termination_date, pos.actual_termination_date) actual_termination_date,
          Decode(paf.assignment_type,
                    'C', hr_general.decode_lookup('HR_CWK_TERMINATION_REASONS', pop.termination_reason),
          hr_general.decode_lookup('LEAV_REAS', pos.leaving_reason)
          ) leaving_reason,
          paf.effective_start_date,
          hr_util_misc_ss.getObjectName('JOB', paf.job_id, ppf.business_group_id, jtl.name) job, paf.job_id,
          orgtl.name organization, orgtl.organization_id,
          bgtl.name business_group, bgtl.organization_id business_group_id,
          lvl.location_code,
          hr_util_misc_ss.getObjectName('GRADE', paf.grade_id, ppf.business_group_id, gtl.name) grade, paf.supervisor_id, ppf.person_id, paf.assignment_id, paf.primary_flag, paf.assignment_number,
          ppf.employee_number,
          ppf.npw_number cwk_number,
          nvl(paf.effective_start_date, ppf.effective_start_date) Asg_start_date,
          decode(nvl(paf.effective_end_date, ppf.effective_end_date), to_date('31/12/4712','DD/MM/RRRR'), null, nvl(paf.effective_end_date, ppf.effective_end_date) ) Asg_end_date,
          hr_general.decode_lookup('YES_NO', paf.primary_flag) Prim_Flag,
          lvl.derived_locale,
          v.vendor_name Supplier,
          paf.VENDOR_ASSIGNMENT_NUMBER Supplier_id_assignment,
          paf.VENDOR_EMPLOYEE_NUMBER Supplier_id_worker,
          Decode(paf.assignment_type,
                    'C', hr_general.decode_lookup('HR_CWK_TERMINATION_REASONS', pop.termination_reason),
          hr_general.decode_lookup('LEAV_REAS', pos.leaving_reason)
          )term_reason,
          decode(ppf.rehire_recommendation, 'N', null, hr_general.decode_lookup('YES_NO', ppf.rehire_recommendation)) rehire,
          ast.user_status assignment_status,
          decode(paf.period_of_service_id, null, pop.date_start, pos.date_start) latest_hire_date,
          hr_util_misc_ss.getObjectName('POSITION', paf.position_id, ppf.business_group_id, ptl.name) position,
          ppf.REHIRE_REASON,
          paf.supervisor_assignment_id,
          null Work_Phone,
          null Position_Id,
          null Disc_Title,
          'Y' Manager_Enabled,
          null Payroll,
          null Normal_Hours,
          'Y1' details_enabled,
          ppf.email_address,
          org_info.org_information9 LegislationCode
          from per_assignments_f2 paf, per_assignment_status_types_tl ast,
          per_people_f ppf,
          hr_all_organization_units_tl orgtl, hr_all_organization_units_tl bgtl,
          per_jobs_tl jtl, hr_all_positions_f_tl ptl, hr_locations_all_vl lvl, po_vendors v,
          per_grades_tl gtl, per_periods_of_service pos, per_periods_of_placement pop, hr_organization_information org_info
          where trunc(sysdate) between paf.effective_start_date and paf.effective_end_date
          and trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date
          and (ppf.current_employee_flag = 'Y' or ppf.current_npw_flag = 'Y')
          and paf.primary_flag = decode(:0 ,'Y', paf.primary_flag, 'Y')
          and paf.assignment_type in ('E','C')
          and paf.person_id = ppf.person_id
          and paf.vendor_id = v.vendor_id(+)
          and paf.assignment_status_type_id = ast.assignment_status_type_id
          and ast.language = userenv('LANG')
          and paf.period_of_service_id = pos.period_of_service_id(+)
          and paf.period_of_placement_date_start = pop.date_start(+)
          and paf.person_id = pop.person_id(+)
          and paf.job_id = jtl.job_id(+)
          and jtl.language(+) = userenv('LANG')
          and paf.position_id = ptl.position_id(+)
          and ptl.language(+) = userenv('LANG')
          and paf.organization_id = orgtl.organization_id
          and orgtl.language = userenv('LANG')
          and ppf.business_group_id = bgtl.organization_id
          and bgtl.language = userenv('LANG')
          and paf.location_id = lvl.location_id(+)
          and paf.grade_id = gtl.grade_id(+)
          and gtl.language(+) = userenv('LANG')
          and org_info.organization_id = ppf.business_group_id
          and org_information_context = 'Business Group Information') QRSLT WHERE (:1 = SUPERVISOR_ID)AND (:2 = SUPERVISOR_ASSIGNMENT_ID or hr_general2.supervisor_assignments_in_use = 'FALSE' ) ORDER BY FULL_NAME
               at oracle.apps.fnd.framework.OAException.wrapperException(OAException.java:888)
               at oracle.apps.fnd.framework.webui.OAPageErrorHandler.prepareException(OAPageErrorHandler.java:1145)
               at oracle.apps.fnd.framework.webui.OAPageBean.renderDocument(OAPageBean.java:2898)
               at oracle.apps.fnd.framework.webui.OAPageBean.renderDocument(OAPageBean.java:2700)
               at OA.jspService(OA.jsp:48)
               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:1983)
               at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java:1141)
               at oracle.jdbc.driver.OracleStatement.doExecuteQuery(OracleStatement.java:2487)
               at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2854)
               at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:622)
               at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:550)
               at oracle.jbo.server.QueryCollection.buildResultSet(QueryCollection.java:627)
               at oracle.jbo.server.QueryCollection.executeQuery(QueryCollection.java:515)
               at oracle.jbo.server.ViewObjectImpl.executeQueryForCollection(ViewObjectImpl.java:3347)
               at oracle.jbo.server.OAJboViewObjectImpl.executeQueryForCollection(OAJboViewObjectImpl.java:825)
               at oracle.apps.fnd.framework.server.OAViewObjectImpl.executeQueryForCollection(OAViewObjectImpl.java:4465)
               at oracle.jbo.server.ViewRowSetImpl.execute(ViewRowSetImpl.java:574)
               at oracle.jbo.server.ViewRowSetImpl.execute(ViewRowSetImpl.java:538)
               at oracle.jbo.server.ViewRowSetIteratorImpl.ensureRefreshed(ViewRowSetIteratorImpl.java:2369)
               at oracle.jbo.server.ViewRowSetIteratorImpl.refresh(ViewRowSetIteratorImpl.java:2586)
               at oracle.jbo.server.ViewRowSetImpl.notifyRefresh(ViewRowSetImpl.java:1674)
               at oracle.jbo.server.ViewRowSetImpl.refreshRowSet(ViewRowSetImpl.java:3703)
               at oracle.jbo.server.ViewRowSetIteratorImpl.notifyDetailRowSets(ViewRowSetIteratorImpl.java:3018)
               at oracle.jbo.server.ViewRowSetIteratorImpl.notifyNavigation(ViewRowSetIteratorImpl.java:3126)
               at oracle.jbo.server.ViewRowSetIteratorImpl.internalSetCurrentRow(ViewRowSetIteratorImpl.java:2910)
               at oracle.jbo.server.ViewRowSetIteratorImpl.first(ViewRowSetIteratorImpl.java:1257)
               at oracle.jbo.server.ViewRowSetImpl.first(ViewRowSetImpl.java:2363)
               at oracle.jbo.server.ViewObjectImpl.first(ViewObjectImpl.java:5231)
               at oracle.apps.fnd.framework.webui.OADataBoundValueHGrid.getValue(OADataBoundValueHGrid.java:85)
               at oracle.apps.fnd.framework.webui.OAWebBeanHelper.getAttributeValueImpl(OAWebBeanHelper.java:1760)
               at oracle.apps.fnd.framework.webui.beans.table.OAHGridBean.getAttributeValueImpl(OAHGridBean.java:509)
               at oracle.cabo.ui.BaseUINode.getAttributeValue(Unknown Source)
               at oracle.cabo.ui.laf.base.desktop.table.HGridRenderingContext.getTableDataFromBean(Unknown Source)
               at oracle.cabo.ui.laf.base.xhtml.TableRenderingContext.<init>(Unknown Source)
               at oracle.cabo.ui.laf.base.desktop.table.HGridRenderingContext.<init>(Unknown Source)
               at oracle.cabo.ui.laf.base.desktop.HGridRenderer.createRenderingContext(Unknown Source)
               at oracle.cabo.ui.laf.base.xhtml.TableRenderer.getTableRenderingContext(Unknown Source)
               at oracle.cabo.ui.laf.base.xhtml.TableRenderer.renderContent(Unknown Source)
               at oracle.cabo.ui.laf.base.desktop.TableRenderer.renderContent(Unknown Source)
               at oracle.cabo.ui.laf.base.desktop.HGridRenderer.renderContent(Unknown Source)
               at oracle.cabo.ui.BaseRenderer.render(Unknown Source)
               at oracle.cabo.ui.laf.base.xhtml.XhtmlLafRenderer.render(Unknown Source)
               at oracle.cabo.ui.BaseUINode.render(Unknown Source)
               at oracle.cabo.ui.BaseUINode.render(Unknown Source)
               at oracle.cabo.ui.BaseRenderer.renderChild(Unknown Source)
               at oracle.cabo.ui.BaseRenderer.renderIndexedChild(Unknown Source)
               at oracle.cabo.ui.BaseRenderer.renderIndexedChild(Unknown Source)
               at oracle.cabo.ui.BaseRenderer.renderContent(Unknown Source)
               at oracle.cabo.ui.BaseRenderer.render(Unknown Source)
               at oracle.cabo.ui.laf.base.xhtml.XhtmlLafRenderer.render(Unknown Source)
               at oracle.cabo.ui.BaseUINode.render(Unknown Source)
               at oracle.cabo.ui.BaseUINode.render(Unknown Source)
               at oracle.cabo.ui.BaseRenderer.renderChild(Unknown Source)
               at oracle.cabo.ui.BaseRenderer.renderIndexedChild(Unknown Source)
               at oracle.cabo.ui.BaseRenderer.renderIndexedChild(Unknown Source)
               at oracle.cabo.ui.BaseRenderer.renderContent(Unknown Source)
               at oracle.cabo.ui.BaseRenderer.render(Unknown Source)
               at oracle.cabo.ui.laf.base.xhtml.XhtmlLafRenderer.render(Unknown Source)
               at oracle.cabo.ui.BaseUINode.render(Unknown Source)
               at oracle.cabo.ui.BaseUINode.render(Unknown Source)
               at oracle.cabo.ui.BaseRenderer.renderChild(Unknown Source)
               at oracle.cabo.ui.BaseRenderer.renderIndexedChild(Unknown Source)
               at oracle.cabo.ui.BaseRenderer.renderIndexedChild(Unknown Source)
               at oracle.cabo.ui.BaseRenderer.renderContent(Unknown Source)
               at oracle.cabo.ui.BaseRenderer.render(Unknown Source)
               at oracle.cabo.ui.laf.base.xhtml.XhtmlLafRenderer.render(Unknown Source)
               at oracle.cabo.ui.BaseUINode.render(Unknown Source)
               at oracle.cabo.ui.BaseUINode.render(Unknown Source)
               at oracle.cabo.ui.composite.ContextPoppingUINode$ContextPoppingRenderer.render(Unknown Source)
               at oracle.cabo.ui.BaseUINode.render(Unknown Source)
               at oracle.cabo.ui.BaseUINode.render(Unknown Source)
               at oracle.cabo.ui.BaseRenderer.renderChild(Unknown Source)
               at oracle.cabo.ui.BaseRenderer.renderIndexedChild(Unknown Source)
               at oracle.cabo.ui.BaseRenderer.renderIndexedChild(Unknown Source)
               at oracle.cabo.ui.BaseRenderer.renderContent(Unknown Source)
               at oracle.cabo.ui.BaseRenderer.render(Unknown Source)
               at oracle.cabo.ui.laf.base.xhtml.XhtmlLafRenderer.render(Unknown Source)
               at oracle.cabo.ui.BaseUINode.render(Unknown Source)
               at oracle.cabo.ui.BaseUINode.render(Unknown Source)
               at oracle.cabo.ui.BaseRenderer.renderChild(Unknown Source)
               at oracle.cabo.ui.BaseRenderer.renderIndexedChild(Unknown Source)
               at oracle.cabo.ui.BaseRenderer.renderIndexedChild(Unknown Source)
               at oracle.cabo.ui.BaseRenderer.renderContent(Unknown Source)
               at oracle.cabo.ui.laf.oracle.desktop.HeaderRenderer.renderContent(Unknown Source)
               at oracle.cabo.ui.BaseRenderer.render(Unknown Source)
               at oracle.cabo.ui.laf.base.xhtml.XhtmlLafRenderer.render(Unknown Source)
               at oracle.cabo.ui.BaseUINode.render(Unknown Source)
               at oracle.cabo.ui.BaseUINode.render(Unknown Source)
               at oracle.cabo.ui.BaseRenderer.renderChild(Unknown Source)
               at oracle.cabo.ui.BaseRenderer.renderIndexedChild(Unknown Source)
               at oracle.cabo.ui.BaseRenderer.renderIndexedChild(Unknown Source)
               at oracle.cabo.ui.BaseRenderer.renderContent(Unknown Source)
               at oracle.cabo.ui.laf.base.xhtml.BorderLayoutRenderer.renderIndexedChildren(Unknown Source)
               at oracle.cabo.ui.laf.base.xhtml.BorderLayoutRenderer.renderContent(Unknown Source)
               at oracle.cabo.ui.BaseRenderer.render(Unknown Source)
               at oracle.cabo.ui.laf.base.xhtml.XhtmlLafRenderer.render(Unknown Source)
               at oracle.cabo.ui.BaseUINode.render(Unknown Source)
               at oracle.cabo.ui.BaseUINode.render(Unknown Source)
               at oracle.cabo.ui.BaseRenderer.renderChild(Unknown Source)
               at oracle.cabo.ui.BaseRenderer.renderIndexedChild(Unknown Source)
               at oracle.cabo.ui.BaseRenderer.renderIndexedChild(Unknown Source)
               at oracle.cabo.ui.BaseRenderer.renderContent(Unknown Source)
               at oracle.cabo.ui.BaseRenderer.render(Unknown Source)
               at oracle.cabo.ui.laf.base.xhtml.XhtmlLafRenderer.render(Unknown Source)
               at oracle.cabo.ui.BaseUINode.render(Unknown Source)
               at oracle.cabo.ui.BaseUINode.render(Unknown Source)
               at oracle.cabo.ui.composite.UINodeRenderer.renderWithNode(Unknown Source)
               at oracle.cabo.ui.composite.UINodeRenderer.render(Unknown Source)
               at oracle.cabo.ui.laf.oracle.desktop.PageLayoutRenderer.render(Unknown Source)
               at oracle.cabo.ui.BaseUINode.render(Unknown Source)
               at oracle.cabo.ui.BaseUINode.render(Unknown Source)
               at oracle.cabo.ui.BaseRenderer.renderChild(Unknown Source)
               at oracle.cabo.ui.BaseRenderer.renderIndexedChild(Unknown Source)
               at oracle.cabo.ui.BaseRenderer.renderIndexedChild(Unknown Source)
               at oracle.cabo.ui.BaseRenderer.renderContent(Unknown Source)
               at oracle.cabo.ui.BaseRenderer.render(Unknown Source)
               at oracle.cabo.ui.laf.base.xhtml.XhtmlLafRenderer.render(Unknown Source)
               at oracle.cabo.ui.BaseUINode.render(Unknown Source)
               at oracle.cabo.ui.BaseUINode.render(Unknown Source)
               at oracle.cabo.ui.BaseRenderer.renderChild(Unknown Source)
               at oracle.cabo.ui.BaseRenderer.renderIndexedChild(Unknown Source)
               at oracle.cabo.ui.BaseRenderer.renderIndexedChild(Unknown Source)
               at oracle.cabo.ui.BaseRenderer.renderContent(Unknown Source)
               at oracle.cabo.ui.laf.base.xhtml.BodyRenderer.renderContent(Unknown Source)
               at oracle.cabo.ui.BaseRenderer.render(Unknown Source)
               at oracle.cabo.ui.BaseUINode.render(Unknown Source)
               at oracle.apps.fnd.framework.webui.beans.OABodyBean.render(OABodyBean.java:398)
               at oracle.cabo.ui.BaseUINode.render(Unknown Source)
               at oracle.cabo.ui.BaseRenderer.renderChild(Unknown Source)
               at oracle.cabo.ui.BaseRenderer.renderIndexedChild(Unknown Source)
               at oracle.cabo.ui.BaseRenderer.renderIndexedChild(Unknown Source)
               at oracle.cabo.ui.BaseRenderer.renderContent(Unknown Source)
               at oracle.cabo.ui.laf.base.xhtml.DocumentRenderer.renderContent(Unknown Source)
               at oracle.cabo.ui.BaseRenderer.render(Unknown Source)
               at oracle.cabo.ui.laf.base.xhtml.DocumentRenderer.render(Unknown Source)
               at oracle.cabo.ui.BaseUINode.render(Unknown Source)
               at oracle.cabo.ui.BaseUINode.render(Unknown Source)
               at oracle.cabo.ui.partial.PartialPageUtils.renderPartialPage(Unknown Source)
               at oracle.apps.fnd.framework.webui.OAPageBean.render(OAPageBean.java:3209)
               at oracle.apps.fnd.framework.webui.OAPageBean.renderDocument(OAPageBean.java:2888)
               at oracle.apps.fnd.framework.webui.OAPageBean.renderDocument(OAPageBean.java:2700)
               at OA.jspService(OA.jsp:48)
               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
          • 2. Re: customizing the oaf page
            D.Ram
            Hi KK,

            This may be security profie issue, you may like to try the following.

            In the project setting under runtime connection section, give the user name, password and responsibility key information exactly same that you use to access the page from application server.



            Regards,
            Ram.
            • 3. Re: customizing the oaf page
              Kumar K
              Hi Ram,

              Thanks for the response. I'm using the same user and password and the responsibility key which i used to login to the page. It doesn't work.

              Below are the details of the page:
              Page path: /oracle/apps/per/selfservice/hierarchy/webui/HierarchyPG

              Responsibility_Key: XXX_MGR_SSHR
              Function: HR_PERSON_TREE_SS.

              This Function HR_PERSON_TREE_SS has the below information in the Functions page:
              HTML Call: OA.jsp?akRegionCode=HR_PERSON_TREE_TOP_SS&akRegionApplicationId=800
              Parameters: pDestination=HR_ACTIONS_SS&pCalledFrom=&addBreadCrumb=Y

              To run the page, I have created a jsp page and in the href property, defined as below:
              <a href="<%=URLMgr.processOutgoingURL(OA.jsp?akRegionCode=HR_PERSON_TREE_TOP_SS&akRegionApplicationId=800&pDestination=HR_ACTIONS_SS&pCalledFrom=&addBreadCrumb=Y&transactionid=" + transactionid, macKey) %">">Mgr Page</a><br>

              Is there anything i need to do to run this page? I have also tried running the first page "/oracle/apps/per/selfservice/hierarchy/webui/HierarchyPG" but it doesn't work.

              Please advice.

              Thank You
              KK</a>
              • 4. Re: customizing the oaf page
                872073
                check for some imput parameters expected by this page.
                • 5. Re: customizing the oaf page
                  Kumar K
                  Where should i check for that?

                  Thanks
                  • 6. Re: customizing the oaf page
                    542656
                    Hi,

                    Please copy the query into sql developer and check whether it is working or not.
                    Hope you have porvided values for all the bind params. If possible please provide the code snippet.
                    also start bind parameter sequence from '1' and check.
                    Regards,
                    Syed