ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN E6016B11E2B24D03BDF8565FB55ADED1Y select x.operating_unit, x.document_type, x.document_number, x.release_number, x.revision, x.document_description, x.supplier, x.document_status, x.sequence_num, x.action, x.performer, x.action_date, x.previous_action_date, x.days_since_previous_action, x.submitted_date, x.approval_path, x.note, x.buyer_or_preparer, x.document_created_by, x.document_creation_date from ( select haouv.name operating_unit, (select pdtav.type_name from po_document_types_all_vl pdtav where pah.object_type_code=pdtav.document_type_code and pah.object_sub_type_code=pdtav.document_subtype and coalesce(pha.org_id,pra.org_id,prha.org_id)=pdtav.org_id) document_type, coalesce(pha.segment1,pha_rel.segment1,prha.segment1) document_number, pra.release_num release_number, pah.object_revision_num revision, coalesce(pha.comments,prha.description) document_description, (select aps.vendor_name from ap_suppliers aps where coalesce(pha.vendor_id,pha_rel.vendor_id)=aps.vendor_id) supplier, xxen_util.meaning(coalesce(pha.authorization_status,pra.authorization_status,prha.authorization_status),'AUTHORIZATION STATUS',201) document_status, pah.sequence_num, nvl(coalesce(xxen_util.meaning(pah.action_code,'APPR_HIST_ACTIONS',201),xxen_util.meaning(pah.action_code,'APPROVER ACTIONS',201),xxen_util.meaning(pah.action_code,'CONTROL ACTIONS',201),pah.action_code),xxen_util.meaning('PENDING','APPR_HIST_ACTIONS',201)) action, pah.action_code, (select max(papf.full_name) keep (dense_rank last order by papf.effective_start_date) from per_all_people_f papf where pah.employee_id=papf.person_id) performer, case when pah.action_code is not null then pah.action_date end action_date, lag(pah.action_date) over (partition by pah.object_type_code, pah.object_id order by pah.sequence_num) previous_action_date, trunc(case when pah.action_code is not null then pah.action_date else sysdate end-lag(pah.action_date) over (partition by pah.object_type_code, pah.object_id order by pah.sequence_num),1) days_since_previous_action, max(case when pah.action_code='SUBMIT' then pah.action_date end) over (partition by pah.object_type_code, pah.object_id order by pah.sequence_num rows unbounded preceding) submitted_date, (select pps.name from per_position_structures pps where pah.approval_path_id=pps.position_structure_id) approval_path, pah.note, (select max(papf.full_name) keep (dense_rank last order by papf.effective_start_date) from per_all_people_f papf where coalesce(pha.agent_id,pra.agent_id,prha.preparer_id)=papf.person_id) buyer_or_preparer, xxen_util.user_name(coalesce(pha.created_by,pra.created_by,prha.created_by)) document_created_by, xxen_util.client_time(coalesce(pha.creation_date,pra.creation_date,prha.creation_date)) document_creation_date from po_action_history pah, po_headers_all pha, po_releases_all pra, po_headers_all pha_rel, po_requisition_headers_all prha, hr_all_organization_units_vl haouv where 1=1 and pah.object_type_code in ('PO','PA','RELEASE','REQUISITION') and decode(pah.object_type_code,'PO',pah.object_id,'PA',pah.object_id)=pha.po_header_id(+) and decode(pah.object_type_code,'RELEASE',pah.object_id)=pra.po_release_id(+) and pra.po_header_id=pha_rel.po_header_id(+) and decode(pah.object_type_code,'REQUISITION',pah.object_id)=prha.requisition_header_id(+) and coalesce(pha.org_id,pra.org_id,prha.org_id) in (select mgoat.organization_id from mo_glob_org_access_tmp mgoat union select fnd_global.org_id from dual where fnd_release.major_version=11) and coalesce(pha.org_id,pra.org_id,prha.org_id)=haouv.organization_id ) x where 2=2 order by x.operating_unit, x.document_type, x.document_number, x.release_number, x.sequence_num New report. :Action_Date_From is not null or :Pending_Only is not null or :Document_Number is not null US PO Approval History Approval and action history of purchase orders, releases, agreements and requisitions: one row per entry of the document's action history (submit, forward, approve, reject, return, reserve, cancel, close, hold), in sequence. The pending step of a document awaiting approval is shown with action Pending and the current approver as performer. Days Since Previous Action is measured from the previous history entry, for a pending step until today. Submitted Date is the latest submission up to that step. For a release, Document Number is the number of its blanket agreement. Please specify an Action Date From or Document Number, or set Pending Only. Enginatics 1=1 2=2 1 10 1=1 haouv.name=:operating_unit LOV HR Operating Unit 8E2FF36EDEB979D2E0530100007F1FF2 Y select hou.name value, null description from hr_operating_units hou where sysdate between hou.date_from and nvl(hou.date_to,sysdate) and (:$flex$.ledger is null or hou.set_of_books_id in (select gl.ledger_id from gl_ledgers gl where xxen_util.contains(:$flex$.ledger,gl.name)='Y')) and hou.organization_id in (select xroa.id from xxen_report_org_access xroa where xroa.access_type='OU') order by hou.name US Operating Unit 2 20 2=2 x.document_type=:document_type LOV custom Y select distinct pdtav.type_name value, null description from po_document_types_all_vl pdtav where pdtav.document_type_code in ('PO','PA','RELEASE','REQUISITION') and pdtav.org_id in (select mgoat.organization_id from mo_glob_org_access_tmp mgoat union select fnd_global.org_id from dual where fnd_release.major_version=11) order by pdtav.type_name US Document Type 3 30 1=1 coalesce(pha.segment1,pha_rel.segment1,prha.segment1)=:document_number Char US Document Number Purchase order, agreement or requisition number. For a release, the number of its blanket agreement. 4 40 1=1 coalesce(pha.vendor_id,pha_rel.vendor_id) in (select aps.vendor_id from ap_suppliers aps where aps.vendor_name=:supplier) LOV AP Supplier B9847D20A0E4742FE0538931640A6379 Y Y select aps.vendor_name value, aps.segment1 description from ap_suppliers aps where (:$flex$.operating_unit is null or aps.vendor_id in (select assa.vendor_id from hr_all_organization_units_vl haouv, ap_supplier_sites_all assa where xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y' and haouv.organization_id=assa.org_id)) and (:$flex$.organization_code is null or aps.vendor_id in (select assa.vendor_id from org_organization_definitions ood, ap_supplier_sites_all assa where xxen_util.contains(:$flex$.organization_code,ood.organization_code)='Y' and ood.operating_unit=assa.org_id)) order by aps.vendor_name, aps.vendor_id US Supplier 5 50 2=2 x.performer=:performer LOV PER Person Full Name 8E2FF36EDEEE79D2E0530100007F1FF2 Y select ppx.full_name value, xxen_util.meaning(ppx.sex,'SEX',3)||' '||pptv.user_person_type description from per_people_x ppx, per_person_types_v pptv where ppx.person_type_id=pptv.person_type_id order by ppx.full_name US Performer Employee who took the action, or the current approver of a pending document. 6 60 2=2 x.action_code=xxen_util.lookup_code(:action,'APPR_HIST_ACTIONS',201) LOV custom Y select flvv.meaning value, flvv.description from fnd_lookup_values_vl flvv where flvv.lookup_type='APPR_HIST_ACTIONS' and flvv.view_application_id=201 and flvv.security_group_id=0 and flvv.enabled_flag='Y' order by flvv.meaning US Action 7 1=1 (pah.object_type_code,pah.object_id) in (select pah2.object_type_code, pah2.object_id from po_action_history pah2 where pah2.action_code=xxen_util.lookup_code(:action,'APPR_HIST_ACTIONS',201)) US Action 8 70 2=2 x.action_date>=:action_date_from Date US Action Date From 9 1=1 (pah.object_type_code,pah.object_id) in (select pah2.object_type_code, pah2.object_id from po_action_history pah2 where pah2.action_date>=:action_date_from) US Action Date From 10 80 2=2 x.action_date<:action_date_to+1 Date last_day(:$flex$.action_date_from) US Action Date To 11 1=1 (pah.object_type_code,pah.object_id) in (select pah2.object_type_code, pah2.object_id from po_action_history pah2 where pah2.action_date<:action_date_to+1) US Action Date To 12 90 2=2 x.action_code is null LOV Yes 8E2FF36EDEA679D2E0530100007F1FF2 Y select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual US Pending Only Show only the pending approval step of documents awaiting approval, with the current approver. 13 1=1 (pah.object_type_code,pah.object_id) in (select pah2.object_type_code, pah2.object_id from po_action_history pah2 where pah2.action_code is null) US Pending Only 584FCD729F884CC0B4D2590FDEC22A95 Pending PO Approvals Purchase orders, releases, agreements and requisitions awaiting approval, with the current approver and the number of days the document has been waiting, longest first. SYSADMIN 13 APPROVAL_PATH 12 BUYER_OR_PREPARER 11 DAYS_SINCE_PREVIOUS_ACTION -1 6 DOCUMENT_DESCRIPTION 3 DOCUMENT_NUMBER 2 DOCUMENT_TYPE 1 OPERATING_UNIT 8 PERFORMER 10 PREVIOUS_ACTION_DATE 4 RELEASE_NUMBER 5 REVISION 9 SUBMITTED_DATE 7 SUPPLIER Pending Only Y S Site 1BBCDD0AC8584F8ABF8FC73AB4ADD576 Rejected and Returned Documents Purchasing documents and requisitions rejected or returned by an approver, with the approver, the date and the note. SYSADMIN 10 ACTION 12 ACTION_DATE -1 16 APPROVAL_PATH 18 BUYER_OR_PREPARER 14 DAYS_SINCE_PREVIOUS_ACTION 19 DOCUMENT_CREATED_BY 20 DOCUMENT_CREATION_DATE 6 DOCUMENT_DESCRIPTION 3 DOCUMENT_NUMBER 8 DOCUMENT_STATUS 2 DOCUMENT_TYPE 17 NOTE 1 OPERATING_UNIT 11 PERFORMER 13 PREVIOUS_ACTION_DATE 4 RELEASE_NUMBER 5 REVISION 9 SEQUENCE_NUM 15 SUBMITTED_DATE 7 SUPPLIER Action <multiple_values>Rejected;Returned S Site