oracle.apps.fnd.framework.OAException

oracle.jbo.SQLStmtException: JBO-27122: SQL error during statement preparation. Statement: SELECT * FROM (SELECT SEHA_CW_PERSONAL_INFO_EO.CW_PERSON_ID, SEHA_CW_PERSONAL_INFO_EO.CW_NUMBER, SEHA_CW_PERSONAL_INFO_EO.CREATOR_BG_ID, SEHA_CW_PERSONAL_INFO_EO.TITLE, SEHA_CW_PERSONAL_INFO_EO.FIRST_NAME, SEHA_CW_PERSONAL_INFO_EO.MIDDLE_NAME, SEHA_CW_PERSONAL_INFO_EO.FAMILY_NAME, SEHA_CW_PERSONAL_INFO_EO.FULL_NAME, SEHA_CW_PERSONAL_INFO_EO.GENDER, SEHA_CW_PERSONAL_INFO_EO.DATE_OF_BIRTH, SEHA_CW_PERSONAL_INFO_EO.APPROVAL_STATUS, SEHA_CW_PERSONAL_INFO_EO.WORK_EMAIL_ADDRESS, SEHA_CW_PERSONAL_INFO_EO.WORK_PHONE_NUMBER, SEHA_CW_PERSONAL_INFO_EO.WORK_MOBILE_NUMBER, SEHA_CW_PERSONAL_INFO_EO.SEHA_EMAIL_ADDRESS, SEHA_CW_PERSONAL_INFO_EO.SEHA_WORK_PHONE_NUMBER, SEHA_CW_PERSONAL_INFO_EO.SEHA_MOBILE_NUMBER, SEHA_CW_PERSONAL_INFO_EO.CW_PICTURE, SEHA_CW_PERSONAL_INFO_EO.FULL_NAME AS Custom_Full_Name, (SELECT meaning Meaning FROM fnd_lookup_values l WHERE lookup_type = 'SEHA_HRMS_APPROVAL' AND l.language = 'US' AND l.enabled_flag = 'Y' AND l.lookup_code = SEHA_CW_PERSONAL_INFO_EO.APPROVAL_STATUS) Meaning_Status, DECODE(SEHA_CW_PERSONAL_INFO_EO.APPROVAL_STATUS, 'INCOMPLETE', 'Update_Enabled', 'REJECTED', 'Update_Enabled', 'APPROVED', 'Update_Enabled', 'Update_Disabled') Update_Swither, SEHA_CW_PERSONAL_INFO_EO.Created_By, SEHA_CW_PERSONAL_INFO_EO.BATCH_NO, SEHA_CW_PERSONAL_INFO_EO.BIO_METRIC, SEHA_CW_PERSONAL_INFO_EO.EMPLOYEE_BADGE_NO, SEHA_CW_PERSONAL_INFO_EO.KRONOS_BADGE_NO, /*(SELECT 1 SECTION FROM seha_global_emp_new_mv jj WHERE jj.section_id = (SELECT case when j.divsion_id is not null and j.depart_id is null and j.section_id is null then j.divsion_id when j.divsion_id is null and j.depart_id is not null and j.section_id is null then j.depart_id when j.divsion_id is null and j.depart_id is null and j.section_id is not null then j.section_id when j.divsion_id is not null and j.depart_id is not null and j.section_id is null then j.depart_id when j.divsion_id is not null and j.depart_id is not null and j.section_id is not null then j.section_id else -0089 end sec_dep_div_id FROM seha_global_emp_new_mv j, FND_USER U WHERE U.EMPLOYEE_ID = j.PERSON_ID AND J.BUSINESS_GROUP_ID = 81 AND U.USER_ID = SEHA_CW_PERSONAL_INFO_EO.CREATED_BY) union SELECT 2 DEPART FROM seha_global_emp_new_mv jj WHERE jj.depart_id = (SELECT case when j.divsion_id is not null and j.depart_id is null and j.section_id is null then j.divsion_id when j.divsion_id is null and j.depart_id is not null and j.section_id is null then j.depart_id when j.divsion_id is null and j.depart_id is null and j.section_id is not null then j.section_id when j.divsion_id is not null and j.depart_id is not null and j.section_id is null then j.depart_id when j.divsion_id is not null and j.depart_id is not null and j.section_id is not null then j.section_id else -0089 end sec_dep_div_id FROM seha_global_emp_new_mv j, FND_USER U WHERE U.EMPLOYEE_ID = j.PERSON_ID AND J.BUSINESS_GROUP_ID = 81 AND U.USER_ID = SEHA_CW_PERSONAL_INFO_EO.CREATED_BY) UNION SELECT 3 DIVISION FROM seha_global_emp_new_mv jj WHERE jj.divsion_id = (SELECT case when j.divsion_id is not null and j.depart_id is null and j.section_id is null then j.divsion_id when j.divsion_id is null and j.depart_id is not null and j.section_id is null then j.depart_id when j.divsion_id is null and j.depart_id is null and j.section_id is not null then j.section_id when j.divsion_id is not null and j.depart_id is not null and j.section_id is null then j.depart_id when j.divsion_id is not null and j.depart_id is not null and j.section_id is not null then j.section_id else -0089 end sec_dep_div_id FROM seha_global_emp_new_mv j, FND_USER U WHERE U.EMPLOYEE_ID = j.PERSON_ID AND J.BUSINESS_GROUP_ID = 81 AND U.USER_ID = SEHA_CW_PERSONAL_INFO_EO.CREATED_BY )) SEC_DEP_DIV,*/ SEHA_CW_PERSONAL_INFO_EO.ACTIVE_INACTIVE_YN, decode(SEHA_CW_PERSONAL_INFO_EO.ACTIVE_INACTIVE_YN, 'A', 'Active', 'Inactive') Meaning_ActiveStatus, SEHA_CW_PERSONAL_INFO_EO.ARABIC_NAME AS ARABIC_NAME, SEHA_CW_PERSONAL_INFO_EO.ALTERNATE_EMAIL AS ALTERNATE_EMAIL, SEHA_CW_PERSONAL_INFO_EO.CONTIGENT_WORKER_TYPE AS Contigent_worker_type, (SELECT MEANING FROM apps.hr_lookups WHERE lookup_type = 'SEHA CONTIGENT WORKER TYPE' AND LOOKUP_CODE = SEHA_CW_PERSONAL_INFO_EO.CONTIGENT_WORKER_TYPE) ContigentWorkertypeMeaning, trunc(SEHA_CW_PERSONAL_INFO_EO.creation_date) as CreationDate, SEHA_CW_PERSONAL_INFO_EO.organization_id as OrganizationId FROM SEHA_CW_PERSONAL_INFO SEHA_CW_PERSONAL_INFO_EO WHERE SEHA_CW_PERSONAL_INFO_EO.CREATOR_BG_ID = 81 AND SEHA_CW_PERSONAL_INFO_EO.Organization_Id in (SELECT org.organization_id FROM hr_all_organization_units org, per_org_structure_elements ose WHERE 1 = 1 AND org.organization_id = ose.organization_id_child START WITH ose.organization_id_child IN (SELECT paaf.organization_id FROM fnd_user fu, per_all_people_f papf, per_all_assignments_f paaf WHERE papf.person_id = fu.employee_id AND TRUNC(sysdate) BETWEEN papf.effective_start_date AND papf.effective_end_date AND papf.person_id = paaf.person_id AND TRUNC(sysdate) BETWEEN paaf.effective_start_date AND paaf.effective_end_date AND fu.user_id = :1) CONNECT BY PRIOR ose.organization_id_child = ose.organization_id_parent)) QRSLT ORDER BY 2 asc

Samebug tips0

There are no available Samebug tips for this exception. If you know how to solve this issue, help other users by writing a short tip.

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

Solutions on the web1680

  • via Oracle Community by Shahzad.H.Magsi, 11 months ago
    j.divsion_id is not null and j.depart_id is not null and j.section_id is not null then j.section_id else -0089 end sec_dep_div_id FROM seha_global_emp_new_mv j, FND_USER U WHERE U.EMPLOYEE_ID = j.PERSON_ID AND J.BUSINESS_GROUP_ID = 81 AND U.USER_ID
  • = (SELECT paaf.vendor_id FROM per_all_assignments_f paaf WHERE paaf.person_id = :2 AND paaf.primary_flag = 'Y' AND trunc(fnd_date.canonical_to_date((:3))) BETWEEN trunc(paaf.effective_start_date ) AND trunc(paaf.effective_end_date ) AND paaf.vendor_id IS NOT NULL )) QRSLT ORDER BY description
  • via Oracle Community by Perseo4022, 10 months ago
    = (SELECT paaf.vendor_id FROM per_all_assignments_f paaf WHERE paaf.person_id = :2 AND paaf.primary_flag = 'Y' AND trunc(fnd_date.canonical_to_date((:3))) BETWEEN trunc(paaf.effective_start_date ) AND trunc(paaf.effective_end_date ) AND paaf.vendor_id IS NOT NULL )) QRSLT ORDER BY description
  • Stack trace

    • oracle.apps.fnd.framework.OAException: oracle.jbo.SQLStmtException: JBO-27122: SQL error during statement preparation. Statement: SELECT * FROM (SELECT SEHA_CW_PERSONAL_INFO_EO.CW_PERSON_ID, SEHA_CW_PERSONAL_INFO_EO.CW_NUMBER, SEHA_CW_PERSONAL_INFO_EO.CREATOR_BG_ID, SEHA_CW_PERSONAL_INFO_EO.TITLE, SEHA_CW_PERSONAL_INFO_EO.FIRST_NAME, SEHA_CW_PERSONAL_INFO_EO.MIDDLE_NAME, SEHA_CW_PERSONAL_INFO_EO.FAMILY_NAME, SEHA_CW_PERSONAL_INFO_EO.FULL_NAME, SEHA_CW_PERSONAL_INFO_EO.GENDER, SEHA_CW_PERSONAL_INFO_EO.DATE_OF_BIRTH, SEHA_CW_PERSONAL_INFO_EO.APPROVAL_STATUS, SEHA_CW_PERSONAL_INFO_EO.WORK_EMAIL_ADDRESS, SEHA_CW_PERSONAL_INFO_EO.WORK_PHONE_NUMBER, SEHA_CW_PERSONAL_INFO_EO.WORK_MOBILE_NUMBER, SEHA_CW_PERSONAL_INFO_EO.SEHA_EMAIL_ADDRESS, SEHA_CW_PERSONAL_INFO_EO.SEHA_WORK_PHONE_NUMBER, SEHA_CW_PERSONAL_INFO_EO.SEHA_MOBILE_NUMBER, SEHA_CW_PERSONAL_INFO_EO.CW_PICTURE, SEHA_CW_PERSONAL_INFO_EO.FULL_NAME AS Custom_Full_Name, (SELECT meaning Meaning FROM fnd_lookup_values l WHERE lookup_type = 'SEHA_HRMS_APPROVAL' AND l.language = 'US' AND l.enabled_flag = 'Y' AND l.lookup_code = SEHA_CW_PERSONAL_INFO_EO.APPROVAL_STATUS) Meaning_Status, DECODE(SEHA_CW_PERSONAL_INFO_EO.APPROVAL_STATUS, 'INCOMPLETE', 'Update_Enabled', 'REJECTED', 'Update_Enabled', 'APPROVED', 'Update_Enabled', 'Update_Disabled') Update_Swither, SEHA_CW_PERSONAL_INFO_EO.Created_By, SEHA_CW_PERSONAL_INFO_EO.BATCH_NO, SEHA_CW_PERSONAL_INFO_EO.BIO_METRIC, SEHA_CW_PERSONAL_INFO_EO.EMPLOYEE_BADGE_NO, SEHA_CW_PERSONAL_INFO_EO.KRONOS_BADGE_NO, /*(SELECT 1 SECTION FROM seha_global_emp_new_mv jj WHERE jj.section_id = (SELECT case when j.divsion_id is not null and j.depart_id is null and j.section_id is null then j.divsion_id when j.divsion_id is null and j.depart_id is not null and j.section_id is null then j.depart_id when j.divsion_id is null and j.depart_id is null and j.section_id is not null then j.section_id when j.divsion_id is not null and j.depart_id is not null and j.section_id is null then j.depart_id when j.divsion_id is not null and j.depart_id is not null and j.section_id is not null then j.section_id else -0089 end sec_dep_div_id FROM seha_global_emp_new_mv j, FND_USER U WHERE U.EMPLOYEE_ID = j.PERSON_ID AND J.BUSINESS_GROUP_ID = 81 AND U.USER_ID = SEHA_CW_PERSONAL_INFO_EO.CREATED_BY) union SELECT 2 DEPART FROM seha_global_emp_new_mv jj WHERE jj.depart_id = (SELECT case when j.divsion_id is not null and j.depart_id is null and j.section_id is null then j.divsion_id when j.divsion_id is null and j.depart_id is not null and j.section_id is null then j.depart_id when j.divsion_id is null and j.depart_id is null and j.section_id is not null then j.section_id when j.divsion_id is not null and j.depart_id is not null and j.section_id is null then j.depart_id when j.divsion_id is not null and j.depart_id is not null and j.section_id is not null then j.section_id else -0089 end sec_dep_div_id FROM seha_global_emp_new_mv j, FND_USER U WHERE U.EMPLOYEE_ID = j.PERSON_ID AND J.BUSINESS_GROUP_ID = 81 AND U.USER_ID = SEHA_CW_PERSONAL_INFO_EO.CREATED_BY) UNION SELECT 3 DIVISION FROM seha_global_emp_new_mv jj WHERE jj.divsion_id = (SELECT case when j.divsion_id is not null and j.depart_id is null and j.section_id is null then j.divsion_id when j.divsion_id is null and j.depart_id is not null and j.section_id is null then j.depart_id when j.divsion_id is null and j.depart_id is null and j.section_id is not null then j.section_id when j.divsion_id is not null and j.depart_id is not null and j.section_id is null then j.depart_id when j.divsion_id is not null and j.depart_id is not null and j.section_id is not null then j.section_id else -0089 end sec_dep_div_id FROM seha_global_emp_new_mv j, FND_USER U WHERE U.EMPLOYEE_ID = j.PERSON_ID AND J.BUSINESS_GROUP_ID = 81 AND U.USER_ID = SEHA_CW_PERSONAL_INFO_EO.CREATED_BY )) SEC_DEP_DIV,*/ SEHA_CW_PERSONAL_INFO_EO.ACTIVE_INACTIVE_YN, decode(SEHA_CW_PERSONAL_INFO_EO.ACTIVE_INACTIVE_YN, 'A', 'Active', 'Inactive') Meaning_ActiveStatus, SEHA_CW_PERSONAL_INFO_EO.ARABIC_NAME AS ARABIC_NAME, SEHA_CW_PERSONAL_INFO_EO.ALTERNATE_EMAIL AS ALTERNATE_EMAIL, SEHA_CW_PERSONAL_INFO_EO.CONTIGENT_WORKER_TYPE AS Contigent_worker_type, (SELECT MEANING FROM apps.hr_lookups WHERE lookup_type = 'SEHA CONTIGENT WORKER TYPE' AND LOOKUP_CODE = SEHA_CW_PERSONAL_INFO_EO.CONTIGENT_WORKER_TYPE) ContigentWorkertypeMeaning, trunc(SEHA_CW_PERSONAL_INFO_EO.creation_date) as CreationDate, SEHA_CW_PERSONAL_INFO_EO.organization_id as OrganizationId FROM SEHA_CW_PERSONAL_INFO SEHA_CW_PERSONAL_INFO_EO WHERE SEHA_CW_PERSONAL_INFO_EO.CREATOR_BG_ID = 81 AND SEHA_CW_PERSONAL_INFO_EO.Organization_Id in (SELECT org.organization_id FROM hr_all_organization_units org, per_org_structure_elements ose WHERE 1 = 1 AND org.organization_id = ose.organization_id_child START WITH ose.organization_id_child IN (SELECT paaf.organization_id FROM fnd_user fu, per_all_people_f papf, per_all_assignments_f paaf WHERE papf.person_id = fu.employee_id AND TRUNC(sysdate) BETWEEN papf.effective_start_date AND papf.effective_end_date AND papf.person_id = paaf.person_id AND TRUNC(sysdate) BETWEEN paaf.effective_start_date AND paaf.effective_end_date AND fu.user_id = :1) CONNECT BY PRIOR ose.organization_id_child = ose.organization_id_parent)) QRSLT ORDER BY 2 asc 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.OAPageErrorHandler.processErrors(Unknown Source) at oracle.apps.fnd.framework.webui.OAPageBean.processFormRequest(Unknown Source) at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(Unknown Source) at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(Unknown Source) at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(Unknown Source) at _OA._jspService(_OA.java:71) 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 com.evermind.util.ReleasableResourcePooledExecutor$MyWorker.run(ReleasableResourcePooledExecutor.java:303) at java.lang.Thread.run(Thread.java:595)

    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 user
    Once, 1 year ago
    Unknown user
    Once, 1 year ago
    Unknown user
    Once, 1 year ago
    Unknown user
    Once, 10 months ago
    Unknown user
    Once, 1 year ago
    14 more bugmates