oracle.apps.fnd.framework.OAException

oracle.jbo.SQLStmtException: JBO-27122: SQL error during statement preparation. Statement: SELECT NVL(paa.date_start,paa.date_projected_start) start_date ,NVL(paa.date_end,paa.date_projected_end) end_date ,paattl.name absence_type ,paat.absence_attendance_type_id absence_attendance_type_id ,fcl.meaning absence_category ,paat.absence_category absence_category_code ,paa.absence_hours ,(SELECT meaning from hr_lookups where 'A' = lookup_code(+) and 'LEAVE_STATUS' = lookup_type(+)) approval_status , 'A' approval_status_code ,decode(paa.date_start,null, (SELECT meaning from fnd_lookup_values where lookup_type ='ABSENCE_STATUS' and lookup_code ='PLANNED' and language = userenv('LANG')), (SELECT meaning from fnd_lookup_values where lookup_type ='ABSENCE_STATUS' and lookup_code ='CONFIRMED' and language = userenv('LANG'))) absence_status , decode(paa.date_start,null,'PLANNED','CONFIRMED') absence_status_code ,(nvl((SELECT 'Y' from fnd_attached_documents where entity_name='PER_ABSENCE_ATTENDANCES' and pk1_value = to_char(paa.absence_attendance_id) and rownum = 1),'N')) supporting_documents ,decode ( paa.date_start,null, decode(paa.date_projected_start, null, 'HrCancelDisabled', 'HrCancelEnabled'), decode( sign(trunc(paa.date_start)-trunc(sysdate)),1,'HrCancelEnabled', decode(paa.date_end,null,'HrCancelEnabled','HrCancelDisabled') ) ) cancel_icon ,decode(paa.date_end,NULL, decode(paa.date_start,Null, 'HrConfirmEnabled','HrConfirmDisabled'), 'HrConfirmDisabled' ) confirm_icon ,decode ( paa.date_start,null, 'HrUpdateEnabled', decode(sign(nvl(paa.date_end,sysdate+1)-sysdate),1,'HrUpdateEnabled','HrUpdateDisabled')) update_icon ,NULL details_icon ,paa.absence_attendance_id ,null transaction_id ,to_char(paa.absence_attendance_id) supportingDocKey ,paa.absence_days ,decode(fnd_profile.value('HR_LOCAL_OR_GLOBAL_NAME_FORMAT'),'G',p.global_name,p.local_name) full_name FROM per_absence_attendances paa ,per_absence_attendance_types paat ,per_abs_attendance_types_tl paattl ,hr_lookups fcl , per_all_people_f p WHERE paa.person_id = :1 and (trunc(sysdate) between p.effective_start_date and p.effective_end_date) and paa.person_id = p.person_id and paa.business_group_id+0 = :2 and paa.absence_attendance_type_id = paat.absence_attendance_type_id and paat.absence_attendance_type_id = paattl.absence_attendance_type_id and paattl.language = userenv('LANG') and fcl.lookup_type(+) = 'ABSENCE_CATEGORY' and paat.absence_category = fcl.lookup_code(+) and ((hr_api.return_legislation_code(paat.business_group_id) = 'GB' and nvl(paat.absence_category,'#') not in ('M','GB_PAT_ADO','GB_PAT_BIRTH','GB_ADO')) or (hr_api.return_legislation_code(paat.business_group_id) <> 'GB' and nvl(paat.absence_category,'#') not in ('GB_PAT_ADO','GB_PAT_BIRTH','GB_ADO'))) and not exists ( select 'e' from hr_api_transactions t WHERE t.selected_person_id = paa.person_id -- and t.CREATOR_PERSON_ID = nvl(:3,t.CREATOR_PERSON_ID) and t.transaction_ref_table='PER_ABSENCE_ATTENDANCES' and t.transaction_ref_id = paa.absence_attendance_id and not(hr_absutil_ss.getabsencetype(t.transaction_id, NULL) IS NULL and t.status = 'W') and t.status not in ('D','E','AC') ) union all select hr_absutil_ss.getStartDate(hat.transaction_id,null) start_date ,hr_absutil_ss.getEndDate(hat.transaction_id,null) end_date ,hr_absutil_ss.getAbsenceType(hat.transaction_id,null) absence_type , to_number(hats.Information5) absence_attendance_type_id ,hr_absutil_ss.getAbsenceCategory(hat.transaction_id,null) absence_category , hats.Information6 absence_category_code ,hr_absutil_ss.getAbsenceHoursDuration(hat.transaction_id,null) absence_hours ,hr_absutil_ss.getApprovalStatus(hat.transaction_id,null) approval_status ,hr_absutil_ss.getApprovalStatusCode(hat.transaction_id,null) approval_status_code ,hr_absutil_ss.getAbsenceStatus(hat.transaction_id,null) absence_status , hats.Information9 absence_status_code ,hr_absutil_ss.hasSupportingDocuments(hat.transaction_id,hat.TRANSACTION_REF_ID) supporting_documents ,hr_absutil_ss.isCancelAllowed(hat.transaction_id,null,hat.status) cancel_icon ,hr_absutil_ss.isConfirmAllowed(hat.transaction_id,null) confirm_icon ,hr_absutil_ss.isUpdateAllowed(hat.transaction_id,null,hat.status) update_icon ,null details_icon ,hat.TRANSACTION_REF_ID absence_attendance_id ,hat.transaction_id transaction_id ,to_char(hat.TRANSACTION_REF_ID||'_'||hat.transaction_id) supportingDocKey ,hr_absutil_ss.getAbsenceDaysDuration(hat.transaction_id,null) absence_days ,decode(fnd_profile.value('HR_LOCAL_OR_GLOBAL_NAME_FORMAT'),'G',p.global_name,p.local_name) full_name from hr_api_transactions hat ,hr_api_transaction_steps hats ,per_all_people_f p where hat.TRANSACTION_REF_TABLE='PER_ABSENCE_ATTENDANCES' and hat.TRANSACTION_GROUP='ABSENCE_MGMT' and hat.TRANSACTION_IDENTIFIER='ABSENCES' and hat.TRANSACTION_REF_ID is not null and hat.SELECTED_PERSON_ID =:3 --and hat.CREATOR_PERSON_ID = nvl(:5,hat.CREATOR_PERSON_ID) and (nvl(start_date,sysdate) between p.effective_start_date and p.effective_end_date) and hat.SELECTED_PERSON_ID = p.person_id and hat.transaction_id=hats.transaction_id(+) and hat.status not in ('D','E','AC') and not (hr_absutil_ss.getabsencetype(hat.transaction_id,null) is null and hat.status='W')

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 web1357

  • via Unknown by Srini-999,
  • Stack trace

    • oracle.apps.fnd.framework.OAException: oracle.jbo.SQLStmtException: JBO-27122: SQL error during statement preparation. Statement: SELECT NVL(paa.date_start,paa.date_projected_start) start_date ,NVL(paa.date_end,paa.date_projected_end) end_date ,paattl.name absence_type ,paat.absence_attendance_type_id absence_attendance_type_id ,fcl.meaning absence_category ,paat.absence_category absence_category_code ,paa.absence_hours ,(SELECT meaning from hr_lookups where 'A' = lookup_code(+) and 'LEAVE_STATUS' = lookup_type(+)) approval_status , 'A' approval_status_code ,decode(paa.date_start,null, (SELECT meaning from fnd_lookup_values where lookup_type ='ABSENCE_STATUS' and lookup_code ='PLANNED' and language = userenv('LANG')), (SELECT meaning from fnd_lookup_values where lookup_type ='ABSENCE_STATUS' and lookup_code ='CONFIRMED' and language = userenv('LANG'))) absence_status , decode(paa.date_start,null,'PLANNED','CONFIRMED') absence_status_code ,(nvl((SELECT 'Y' from fnd_attached_documents where entity_name='PER_ABSENCE_ATTENDANCES' and pk1_value = to_char(paa.absence_attendance_id) and rownum = 1),'N')) supporting_documents ,decode ( paa.date_start,null, decode(paa.date_projected_start, null, 'HrCancelDisabled', 'HrCancelEnabled'), decode( sign(trunc(paa.date_start)-trunc(sysdate)),1,'HrCancelEnabled', decode(paa.date_end,null,'HrCancelEnabled','HrCancelDisabled') ) ) cancel_icon ,decode(paa.date_end,NULL, decode(paa.date_start,Null, 'HrConfirmEnabled','HrConfirmDisabled'), 'HrConfirmDisabled' ) confirm_icon ,decode ( paa.date_start,null, 'HrUpdateEnabled', decode(sign(nvl(paa.date_end,sysdate+1)-sysdate),1,'HrUpdateEnabled','HrUpdateDisabled')) update_icon ,NULL details_icon ,paa.absence_attendance_id ,null transaction_id ,to_char(paa.absence_attendance_id) supportingDocKey ,paa.absence_days ,decode(fnd_profile.value('HR_LOCAL_OR_GLOBAL_NAME_FORMAT'),'G',p.global_name,p.local_name) full_name FROM per_absence_attendances paa ,per_absence_attendance_types paat ,per_abs_attendance_types_tl paattl ,hr_lookups fcl , per_all_people_f p WHERE paa.person_id = :1 and (trunc(sysdate) between p.effective_start_date and p.effective_end_date) and paa.person_id = p.person_id and paa.business_group_id+0 = :2 and paa.absence_attendance_type_id = paat.absence_attendance_type_id and paat.absence_attendance_type_id = paattl.absence_attendance_type_id and paattl.language = userenv('LANG') and fcl.lookup_type(+) = 'ABSENCE_CATEGORY' and paat.absence_category = fcl.lookup_code(+) and ((hr_api.return_legislation_code(paat.business_group_id) = 'GB' and nvl(paat.absence_category,'#') not in ('M','GB_PAT_ADO','GB_PAT_BIRTH','GB_ADO')) or (hr_api.return_legislation_code(paat.business_group_id) <> 'GB' and nvl(paat.absence_category,'#') not in ('GB_PAT_ADO','GB_PAT_BIRTH','GB_ADO'))) and not exists ( select 'e' from hr_api_transactions t WHERE t.selected_person_id = paa.person_id -- and t.CREATOR_PERSON_ID = nvl(:3,t.CREATOR_PERSON_ID) and t.transaction_ref_table='PER_ABSENCE_ATTENDANCES' and t.transaction_ref_id = paa.absence_attendance_id and not(hr_absutil_ss.getabsencetype(t.transaction_id, NULL) IS NULL and t.status = 'W') and t.status not in ('D','E','AC') ) union all select hr_absutil_ss.getStartDate(hat.transaction_id,null) start_date ,hr_absutil_ss.getEndDate(hat.transaction_id,null) end_date ,hr_absutil_ss.getAbsenceType(hat.transaction_id,null) absence_type , to_number(hats.Information5) absence_attendance_type_id ,hr_absutil_ss.getAbsenceCategory(hat.transaction_id,null) absence_category , hats.Information6 absence_category_code ,hr_absutil_ss.getAbsenceHoursDuration(hat.transaction_id,null) absence_hours ,hr_absutil_ss.getApprovalStatus(hat.transaction_id,null) approval_status ,hr_absutil_ss.getApprovalStatusCode(hat.transaction_id,null) approval_status_code ,hr_absutil_ss.getAbsenceStatus(hat.transaction_id,null) absence_status , hats.Information9 absence_status_code ,hr_absutil_ss.hasSupportingDocuments(hat.transaction_id,hat.TRANSACTION_REF_ID) supporting_documents ,hr_absutil_ss.isCancelAllowed(hat.transaction_id,null,hat.status) cancel_icon ,hr_absutil_ss.isConfirmAllowed(hat.transaction_id,null) confirm_icon ,hr_absutil_ss.isUpdateAllowed(hat.transaction_id,null,hat.status) update_icon ,null details_icon ,hat.TRANSACTION_REF_ID absence_attendance_id ,hat.transaction_id transaction_id ,to_char(hat.TRANSACTION_REF_ID||'_'||hat.transaction_id) supportingDocKey ,hr_absutil_ss.getAbsenceDaysDuration(hat.transaction_id,null) absence_days ,decode(fnd_profile.value('HR_LOCAL_OR_GLOBAL_NAME_FORMAT'),'G',p.global_name,p.local_name) full_name from hr_api_transactions hat ,hr_api_transaction_steps hats ,per_all_people_f p where hat.TRANSACTION_REF_TABLE='PER_ABSENCE_ATTENDANCES' and hat.TRANSACTION_GROUP='ABSENCE_MGMT' and hat.TRANSACTION_IDENTIFIER='ABSENCES' and hat.TRANSACTION_REF_ID is not null and hat.SELECTED_PERSON_ID =:3 --and hat.CREATOR_PERSON_ID = nvl(:5,hat.CREATOR_PERSON_ID) and (nvl(start_date,sysdate) between p.effective_start_date and p.effective_end_date) and hat.SELECTED_PERSON_ID = p.person_id and hat.transaction_id=hats.transaction_id(+) and hat.status not in ('D','E','AC') and not (hr_absutil_ss.getabsencetype(hat.transaction_id,null) is null and hat.status='W') at oracle.apps.fnd.framework.OAException.wrapperException(OAException.java:912) at oracle.apps.fnd.framework.OAException.wrapperException(OAException.java:886) at oracle.apps.fnd.framework.OAException.wrapperInvocationTargetException(OAException.java:1009) at oracle.apps.fnd.framework.server.OAUtility.invokeMethod(OAUtility.java:211) at oracle.apps.fnd.framework.server.OAUtility.invokeMethod(OAUtility.java:133) at oracle.apps.fnd.framework.server.OAApplicationModuleImpl.invokeMethod(OAApplicationModuleImpl.java:797) at oracle.apps.per.selfservice.absence.webui.AbsenceSubTabCO.processRequest(AbsenceSubTabCO.java:47) at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequest(OAWebBeanHelper.java:600) at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processRequest(OAWebBeanContainerHelper.java:252) at oracle.apps.fnd.framework.webui.beans.layout.OAStackLayoutBean.processRequest(OAStackLayoutBean.java:350) at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequestChildren(OAWebBeanHelper.java:968) at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequestChildren(OAWebBeanHelper.java:935) at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequest(OAWebBeanHelper.java:659) at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processRequest(OAWebBeanContainerHelper.java:252) at oracle.apps.fnd.framework.webui.OASubTabLayoutHelper.processRequest(OASubTabLayoutHelper.java:434) at oracle.apps.fnd.framework.webui.beans.layout.OASubTabLayoutBean.processRequest(OASubTabLayoutBean.java:465) at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequestChildren(OAWebBeanHelper.java:968) at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequestChildren(OAWebBeanHelper.java:935) at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequest(OAWebBeanHelper.java:659) at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processRequest(OAWebBeanContainerHelper.java:252) at oracle.apps.fnd.framework.webui.beans.layout.OAStackLayoutBean.processRequest(OAStackLayoutBean.java:350) at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequestChildren(OAWebBeanHelper.java:968) at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequestChildren(OAWebBeanHelper.java:935) at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequest(OAWebBeanHelper.java:659) at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processRequest(OAWebBeanContainerHelper.java:252) at oracle.apps.fnd.framework.webui.beans.layout.OAHeaderBean.processRequest(OAHeaderBean.java:391) at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequestChildren(OAWebBeanHelper.java:968) at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequestChildren(OAWebBeanHelper.java:935) at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequest(OAWebBeanHelper.java:659) at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processRequest(OAWebBeanContainerHelper.java:252) at oracle.apps.fnd.framework.webui.beans.layout.OAStackLayoutBean.processRequest(OAStackLayoutBean.java:350) at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequestChildren(OAWebBeanHelper.java:968) at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequestChildren(OAWebBeanHelper.java:935) at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequest(OAWebBeanHelper.java:659) at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processRequest(OAWebBeanContainerHelper.java:252) at oracle.apps.fnd.framework.webui.OAPageLayoutHelper.processRequest(OAPageLayoutHelper.java:1183) at oracle.apps.fnd.framework.webui.beans.layout.OAPageLayoutBean.processRequest(OAPageLayoutBean.java:1569) at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequestChildren(OAWebBeanHelper.java:968) at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequestChildren(OAWebBeanHelper.java:935) at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequest(OAWebBeanHelper.java:659) at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processRequest(OAWebBeanContainerHelper.java:252) at oracle.apps.fnd.framework.webui.beans.form.OAFormBean.processRequest(OAFormBean.java:385) at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequestChildren(OAWebBeanHelper.java:968) at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequestChildren(OAWebBeanHelper.java:935) at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequest(OAWebBeanHelper.java:659) at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processRequest(OAWebBeanContainerHelper.java:252) at oracle.apps.fnd.framework.webui.beans.OABodyBean.processRequest(OABodyBean.java:353) at oracle.apps.fnd.framework.webui.OAPageBean.processRequest(OAPageBean.java:2620) at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:1940) at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:543) at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:431)

    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,
    10 more bugmates