oracle.apps.fnd.framework.OAException

oracle.jbo.SQLStmtException: JBO-27122: SQL error during statement preparation. Statement: SELECT ppd.segment2 position_number, ppd.segment3 position_title, papf.full_name employee_name, haou1.NAME budget_centre, haou1.organization_id budget_centre_id, hapf.position_id position_id, haou.organization_id position_org_id, papf.person_id person_id, haou.NAME position_org_name, haou.TYPE pos_org_type FROM hr_all_organization_units haou, hr_all_organization_units haou1, hr_all_positions_f hapf, per_position_definitions ppd, per_all_people_f papf, per_all_assignments_f paaf, per_person_types ppt, per_person_type_usages_f pptuf WHERE haou.business_group_id = :0 AND hapf.business_group_id = :1 AND SYSDATE BETWEEN haou.date_from AND NVL (haou.date_to, TO_DATE ('31-DEC-4712', 'DD-MON-RRRR')) AND SYSDATE BETWEEN haou1.date_from AND NVL (haou1.date_to, TO_DATE ('31-DEC-4712', 'DD-MON-RRRR')) AND hapf.organization_id = haou.organization_id AND papf.person_id = paaf.person_id AND hapf.position_id = paaf.position_id AND papf.person_id = pptuf.person_id AND ppt.system_person_type = ('EMP') AND paaf.assignment_status_type_id = 1 AND ppt.person_type_id = pptuf.person_type_id AND SYSDATE BETWEEN hapf.effective_start_date AND hapf.effective_end_date AND SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date AND SYSDATE BETWEEN paaf.effective_start_date AND paaf.effective_end_date AND SYSDATE BETWEEN pptuf.effective_start_date AND pptuf.effective_end_date AND ppd.position_definition_id = hapf.position_definition_id AND haou1.organization_id = xxhr_utils_pkg.xxhr_get_parent_org_f ( haou.organization_id, 'BC', 'WHO Organization Hierarchy', SYSDATE) AND haou.organization_id IN ( SELECT organization_id_child FROM per_org_structure_elements WHERE org_structure_version_id = (SELECT MAX (posv1.org_structure_version_id) FROM per_org_structure_versions posv1, per_organization_structures pos WHERE posv1.organization_structure_id = pos.organization_structure_id AND pos.primary_structure_flag = 'Y') CONNECT BY organization_id_parent = PRIOR organization_id_child START WITH organization_id_parent = :2) UNION ALL SELECT ppd.segment2 position_number, ppd.segment3 position_title, per_emp.employee_name employee_name, haou1.NAME budget_centre, haou1.organization_id budget_centre_id, hapf.position_id position_id, haou.organization_id position_org_id, per_emp.person_id person_id, haou.NAME position_org_name, haou.TYPE pos_org_type FROM hr_all_organization_units haou, hr_all_organization_units haou1, hr_all_positions_f hapf, per_position_definitions ppd, (SELECT papf.full_name employee_name, papf.person_id person_id, paaf.position_id position_id FROM per_person_types ppt, per_person_type_usages_f pptuf, per_all_people_f papf, per_all_assignments_f paaf WHERE 1 = 1 AND SYSDATE BETWEEN pptuf.effective_start_date AND pptuf.effective_end_date AND ppt.system_person_type = ('EMP') AND ppt.person_type_id = pptuf.person_type_id AND papf.person_id = pptuf.person_id AND SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date AND papf.person_id = paaf.person_id AND SYSDATE BETWEEN paaf.effective_start_date AND paaf.effective_end_date AND paaf.assignment_status_type_id = 1 AND paaf.organization_id = :3) per_emp WHERE hapf.organization_id = :4 AND haou.business_group_id = :5 AND hapf.business_group_id = :6 AND SYSDATE BETWEEN haou.date_from AND NVL (haou.date_to, TO_DATE ('31-DEC-4712', 'DD-MON-RRRR')) AND SYSDATE BETWEEN haou1.date_from AND NVL (haou1.date_to, TO_DATE ('31-DEC-4712', 'DD-MON-RRRR')) AND hapf.organization_id = haou.organization_id AND hapf.position_id = per_emp.position_id(+) AND SYSDATE BETWEEN hapf.effective_start_date AND hapf.effective_end_date AND ppd.position_definition_id = hapf.position_definition_id AND haou1.organization_id = xxhr_utils_pkg.xxhr_get_parent_org_f ( haou.organization_id, 'BC', 'WHO Organization Hierarchy', SYSDATE) UNION ALL SELECT ppd.segment2 position_number, ppd.segment3 position_title, NULL employee_name, haou1.NAME budget_centre, haou1.organization_id budget_centre_id, hapf.position_id position_id, haou.organization_id position_org_id, NULL person_id, haou.NAME position_org_name, haou.TYPE pos_org_type FROM hr_all_organization_units haou, hr_all_organization_units haou1, hr_all_positions_f hapf, per_position_definitions ppd WHERE haou.business_group_id = :7 AND hapf.business_group_id = :8 AND SYSDATE BETWEEN haou.date_from AND NVL (haou.date_to, TO_DATE ('31-DEC-4712', 'DD-MON-RRRR')) AND SYSDATE BETWEEN haou1.date_from AND NVL (haou1.date_to, TO_DATE ('31-DEC-4712', 'DD-MON-RRRR')) AND hapf.organization_id = haou.organization_id AND SYSDATE BETWEEN hapf.effective_start_date AND hapf.effective_end_date AND ppd.position_definition_id = hapf.position_definition_id AND haou1.organization_id = xxhr_utils_pkg.xxhr_get_parent_org_f ( haou.organization_id, 'BC', 'WHO Organization Hierarchy', SYSDATE) AND NOT EXISTS (SELECT 'c' FROM per_person_types ppt, per_person_type_usages_f pptuf, per_all_people_f papf, per_all_assignments_f paaf WHERE 1 = 1 AND SYSDATE BETWEEN pptuf.effective_start_date AND pptuf.effective_end_date AND ppt.system_person_type = ('EMP') AND ppt.person_type_id = pptuf.person_type_id AND papf.person_id = pptuf.person_id AND SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date AND papf.person_id = paaf.person_id AND SYSDATE BETWEEN paaf.effective_start_date AND paaf.effective_end_date AND paaf.assignment_status_type_id = 1 AND hapf.position_id = paaf.position_id) AND haou.organization_id IN ( SELECT organization_id_child FROM per_org_structure_elements WHERE org_structure_version_id = (SELECT MAX (posv1.org_structure_version_id) FROM per_org_structure_versions posv1, per_organization_structures pos WHERE posv1.organization_structure_id = pos.organization_structure_id AND pos.primary_structure_flag = 'Y') CONNECT BY organization_id_parent = PRIOR organization_id_child START WITH organization_id_parent = :9)

Samebug tips0

We couldn't find tips for this exception.

Don't give up yet. Paste your full stack trace to get a solution.

Solutions on the web1658

  • via Unknown by user2626293,
  • via Unknown by 567494,
  • via Unknown by 568541,
  • Stack trace

    • oracle.apps.fnd.framework.OAException: oracle.jbo.SQLStmtException: JBO-27122: SQL error during statement preparation. Statement: SELECT ppd.segment2 position_number, ppd.segment3 position_title, papf.full_name employee_name, haou1.NAME budget_centre, haou1.organization_id budget_centre_id, hapf.position_id position_id, haou.organization_id position_org_id, papf.person_id person_id, haou.NAME position_org_name, haou.TYPE pos_org_type FROM hr_all_organization_units haou, hr_all_organization_units haou1, hr_all_positions_f hapf, per_position_definitions ppd, per_all_people_f papf, per_all_assignments_f paaf, per_person_types ppt, per_person_type_usages_f pptuf WHERE haou.business_group_id = :0 AND hapf.business_group_id = :1 AND SYSDATE BETWEEN haou.date_from AND NVL (haou.date_to, TO_DATE ('31-DEC-4712', 'DD-MON-RRRR')) AND SYSDATE BETWEEN haou1.date_from AND NVL (haou1.date_to, TO_DATE ('31-DEC-4712', 'DD-MON-RRRR')) AND hapf.organization_id = haou.organization_id AND papf.person_id = paaf.person_id AND hapf.position_id = paaf.position_id AND papf.person_id = pptuf.person_id AND ppt.system_person_type = ('EMP') AND paaf.assignment_status_type_id = 1 AND ppt.person_type_id = pptuf.person_type_id AND SYSDATE BETWEEN hapf.effective_start_date AND hapf.effective_end_date AND SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date AND SYSDATE BETWEEN paaf.effective_start_date AND paaf.effective_end_date AND SYSDATE BETWEEN pptuf.effective_start_date AND pptuf.effective_end_date AND ppd.position_definition_id = hapf.position_definition_id AND haou1.organization_id = xxhr_utils_pkg.xxhr_get_parent_org_f ( haou.organization_id, 'BC', 'WHO Organization Hierarchy', SYSDATE) AND haou.organization_id IN ( SELECT organization_id_child FROM per_org_structure_elements WHERE org_structure_version_id = (SELECT MAX (posv1.org_structure_version_id) FROM per_org_structure_versions posv1, per_organization_structures pos WHERE posv1.organization_structure_id = pos.organization_structure_id AND pos.primary_structure_flag = 'Y') CONNECT BY organization_id_parent = PRIOR organization_id_child START WITH organization_id_parent = :2) UNION ALL SELECT ppd.segment2 position_number, ppd.segment3 position_title, per_emp.employee_name employee_name, haou1.NAME budget_centre, haou1.organization_id budget_centre_id, hapf.position_id position_id, haou.organization_id position_org_id, per_emp.person_id person_id, haou.NAME position_org_name, haou.TYPE pos_org_type FROM hr_all_organization_units haou, hr_all_organization_units haou1, hr_all_positions_f hapf, per_position_definitions ppd, (SELECT papf.full_name employee_name, papf.person_id person_id, paaf.position_id position_id FROM per_person_types ppt, per_person_type_usages_f pptuf, per_all_people_f papf, per_all_assignments_f paaf WHERE 1 = 1 AND SYSDATE BETWEEN pptuf.effective_start_date AND pptuf.effective_end_date AND ppt.system_person_type = ('EMP') AND ppt.person_type_id = pptuf.person_type_id AND papf.person_id = pptuf.person_id AND SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date AND papf.person_id = paaf.person_id AND SYSDATE BETWEEN paaf.effective_start_date AND paaf.effective_end_date AND paaf.assignment_status_type_id = 1 AND paaf.organization_id = :3) per_emp WHERE hapf.organization_id = :4 AND haou.business_group_id = :5 AND hapf.business_group_id = :6 AND SYSDATE BETWEEN haou.date_from AND NVL (haou.date_to, TO_DATE ('31-DEC-4712', 'DD-MON-RRRR')) AND SYSDATE BETWEEN haou1.date_from AND NVL (haou1.date_to, TO_DATE ('31-DEC-4712', 'DD-MON-RRRR')) AND hapf.organization_id = haou.organization_id AND hapf.position_id = per_emp.position_id(+) AND SYSDATE BETWEEN hapf.effective_start_date AND hapf.effective_end_date AND ppd.position_definition_id = hapf.position_definition_id AND haou1.organization_id = xxhr_utils_pkg.xxhr_get_parent_org_f ( haou.organization_id, 'BC', 'WHO Organization Hierarchy', SYSDATE) UNION ALL SELECT ppd.segment2 position_number, ppd.segment3 position_title, NULL employee_name, haou1.NAME budget_centre, haou1.organization_id budget_centre_id, hapf.position_id position_id, haou.organization_id position_org_id, NULL person_id, haou.NAME position_org_name, haou.TYPE pos_org_type FROM hr_all_organization_units haou, hr_all_organization_units haou1, hr_all_positions_f hapf, per_position_definitions ppd WHERE haou.business_group_id = :7 AND hapf.business_group_id = :8 AND SYSDATE BETWEEN haou.date_from AND NVL (haou.date_to, TO_DATE ('31-DEC-4712', 'DD-MON-RRRR')) AND SYSDATE BETWEEN haou1.date_from AND NVL (haou1.date_to, TO_DATE ('31-DEC-4712', 'DD-MON-RRRR')) AND hapf.organization_id = haou.organization_id AND SYSDATE BETWEEN hapf.effective_start_date AND hapf.effective_end_date AND ppd.position_definition_id = hapf.position_definition_id AND haou1.organization_id = xxhr_utils_pkg.xxhr_get_parent_org_f ( haou.organization_id, 'BC', 'WHO Organization Hierarchy', SYSDATE) AND NOT EXISTS (SELECT 'c' FROM per_person_types ppt, per_person_type_usages_f pptuf, per_all_people_f papf, per_all_assignments_f paaf WHERE 1 = 1 AND SYSDATE BETWEEN pptuf.effective_start_date AND pptuf.effective_end_date AND ppt.system_person_type = ('EMP') AND ppt.person_type_id = pptuf.person_type_id AND papf.person_id = pptuf.person_id AND SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date AND papf.person_id = paaf.person_id AND SYSDATE BETWEEN paaf.effective_start_date AND paaf.effective_end_date AND paaf.assignment_status_type_id = 1 AND hapf.position_id = paaf.position_id) AND haou.organization_id IN ( SELECT organization_id_child FROM per_org_structure_elements WHERE org_structure_version_id = (SELECT MAX (posv1.org_structure_version_id) FROM per_org_structure_versions posv1, per_organization_structures pos WHERE posv1.organization_structure_id = pos.organization_structure_id AND pos.primary_structure_flag = 'Y') CONNECT BY organization_id_parent = PRIOR organization_id_child START WITH organization_id_parent = :9) 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.OAListOfValuesHelper.processRequest(OAListOfValuesHelper.java:439) at oracle.apps.fnd.framework.webui.beans.layout.OAListOfValuesBean.processRequest(OAListOfValuesBean.java:413) 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:2373) 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_html._OA._jspService(_OA.java:84) at oracle.jsp.runtime.HttpJsp.service(HttpJsp.java:119) at oracle.jsp.app.JspApplication.dispatchRequest(JspApplication.java:417) at oracle.jsp.JspServlet.doDispatch(JspServlet.java:267) at oracle.jsp.JspServlet.internalService(JspServlet.java:186) at oracle.jsp.JspServlet.service(JspServlet.java:156) at javax.servlet.http.HttpServlet.service(HttpServlet.java:588) at org.apache.jserv.JServConnection.processRequest(JServConnection.java:456) at org.apache.jserv.JServConnection.run(JServConnection.java:294) at java.lang.Thread.run(Thread.java:619)

    Write tip

    You have a different solution? A short tip here would help you and many other users who saw this issue last week.

    Users with the same issue

    Unknown visitor
    Unknown visitorOnce,
    Unknown visitor
    Unknown visitorOnce,
    Unknown visitor
    Unknown visitorOnce,
    Unknown visitor
    Unknown visitorOnce,
    Unknown visitor
    Unknown visitorOnce,
    14 more bugmates