0 Replies Latest reply: Jul 23, 2014 6:06 AM by Ravi91349 RSS

    Absence Management OAF Form Personalization

    Ravi91349

      Good day,

       

      I am trying to run a Absence Management page from Manager Self Service responsibility in JDeveloper but getting "java.sql.SQLException: Missing IN or OUT parameter at index:: 3" error, I have run SQL statement separately on TOAD it's executed successfully with record(s). Could you please help me?

       

      Your help highly appreciated.

       

      Here is the ERROR:

      Exception Details.

      oracle.apps.fnd.framework.OAException: oracle.jbo.SQLStmtException: JBO-27122: SQL error during statement preparation.  Statement: SELECT * FROM (select ppf.list_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 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(Unknown Source)

                      at oracle.apps.fnd.framework.webui.OAPageErrorHandler.prepareException(Unknown Source)

                      at oracle.apps.fnd.framework.webui.OAPageBean.renderDocument(Unknown Source)

                      at oracle.apps.fnd.framework.webui.OAPageBean.renderDocument(Unknown Source)

                      at _OA._jspService(_OA.java:87)

                      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:298)

                      at java.lang.Thread.run(Thread.java:595)

      ## Detail 0 ##

      1. java.sql.SQLException: Missing IN or OUT parameter at index:: 3

      ...............

      ...............

       

      Thanks,

      Ravi