Query to Extract Purchase Order (PO) and related Purchase Requisition (PR) in Oracle EBS

SELECT poh.org_id                  circle
     , poh.segment1                po_number
     , prha.segment1               req_number
FROM PO_HEADERS_ALL POH, 
       PO_DISTRIBUTIONS_ALL PDA ,
       PO_REQ_DISTRIBUTIONS_ALL PRDA ,
       PO_REQUISITION_LINES_ALL PRLA ,
       PO_REQUISITION_HEADERS_ALL PRHA
WHERE POH.PO_HEADER_ID = PDA. PO_HEADER_ID 
  AND PDA.REQ_DISTRIBUTION_ID = PRDA.DISTRIBUTION_ID
  AND PRDA.REQUISITION_LINE_ID = PRLA. REQUISITION_LINE_ID
  AND PRLA.REQUISITION_HEADER_ID = PRHA. REQUISITION_HEADER_ID
  AND prha.authorization_status   = 'APPROVED'
  AND poh.authorization_status    = 'APPROVED'
  AND POH.creation_date BETWEEN TO_DATE( '15-OCT-2018', 'DD-MON-YYYY' )AND TO_DATE( '21-OCT-2018', 'DD-MON-YYYY' );

Query to extract PO-PR-Which are Timed-Out

select poh.object_id, prha.segment1 REQUISITION_NUMBER,
(Select name from apps.hr_operating_units where ORGANIZATION_ID = prha.org_id ) Circle_name,
(select  max(wfn.notification_id)
from apps.wf_item_activity_statuses wias,
apps.wf_notifications wfn
where wias.notification_id is not null
and wias.notification_id = wfn.group_id
and wfn.status = 'OPEN'
and wias.item_type = 'REQAPPRV'
and wias.item_key in (select max(wi.item_key)
                       from apps.wf_items wi
                      where wi.parent_item_key = prha.wf_item_key
                        and end_date is null
                    )) Notification_id ,
poh.object_type_code,
poh.object_sub_type_code,
poh.sequence_num,
poh.action_code,
poh.action_date,
poh.employee_id,
papf.full_name
from apps.po_action_history poh, apps.per_all_people_f papf,apps.po_requisition_headers_all prha
 where poh.object_type_code='REQUISITION'
   and poh.employee_id = papf.person_id
   and prha.requisition_header_id=poh.object_id
   and prha.wf_item_key is not null
   and sysdate between papf.effective_start_date and papf.effective_end_date
   and poh.sequence_num = (select max(pah1.sequence_num) from apps.po_action_history pah1 where pah1.object_id = poh.object_id)
   and exists (select '1' from apps.po_action_history where object_id = poh.object_id and action_code ='NO ACTION')
   and poh.object_id in (select requisition_header_id from apps.po_requisition_headers_all where authorization_status ='IN PROCESS' and creation_date between '01-Mar-2019' and '14-Jun-2019')
union all
select poh.object_id, prha.segment1 po_NUMBER,
(Select name from apps.hr_operating_units where ORGANIZATION_ID = prha.org_id ) Circle_name,
(select  max(wfn.notification_id)
from apps.wf_item_activity_statuses wias,
apps.wf_notifications wfn
where wias.notification_id is not null
and wias.notification_id = wfn.group_id
and wfn.status = 'OPEN'
and wias.item_type = 'POAPPRV'
and wias.item_key = (select max(wi.item_key)
                       from apps.wf_items wi
                      where wi.item_key = prha.wf_item_key ) 
                    ) Notfication_id ,
poh.object_type_code,
poh.object_sub_type_code,
poh.sequence_num,
poh.action_code,
poh.action_date,
poh.employee_id,
papf.full_name
from apps.po_action_history poh, apps.per_all_people_f papf,apps.po_headers_all prha
 where poh.object_type_code='PO'
   and poh.employee_id = papf.person_id
   and prha.po_header_id=poh.object_id
   and prha.wf_item_key is not null
   and sysdate between papf.effective_start_date and papf.effective_end_date
   and poh.sequence_num = (select max(pah1.sequence_num) from apps.po_action_history pah1 where pah1.object_id = poh.object_id)
   and exists (select '1' from apps.po_action_history where object_id = poh.object_id and action_code ='NO ACTION')
   and poh.object_id in (select po_header_id from apps.po_headers_all where authorization_status ='IN PROCESS' and creation_date between '01-Mar-2019' and '14-Jun-2019')
   order by 5,3,2,1;

Query - AP Invoice - PO Information

SELECT distinct
   AIA.INVOICE_NUM Invoice_number,
   -- AIDA.INVOICE_LINE_NUMBER,AIDA.DISTRIBUTION_LINE_NUMBER,
   AIA.INVOICE_DATE, 
   AIA.CREATION_DATE INV_CREATION_DATE,
   AIA.SOURCE,
   AIA.payment_status_flag,
   AIDA.ACCOUNTING_DATE INV_ACCOUNTING_DATE,  
   ATL.NAME INV_PAYMENT_TERM_NAME, 
   ATL1.NAME PO_PAYMENT_TERM_NAME,    
   pha.SEGMENT1 PO_NUMBER, 
   pha.CREATION_DATE PO_CREATION_DATE, 
   pha.APPROVED_DATE PO_APPROVED_DATE,
   AIA.invoice_id
FROM    
   APPS.PO_HEADERS_ALL pha, 
   APPS.PO_LINES_ALL pl, 
   APPS.PO_LINE_LOCATIONS_ALL POL, 
   APPS.PO_DISTRIBUTIONS_ALL PDA, 
   APPS.AP_INVOICE_DISTRIBUTIONS_ALL AIDA, 
   APPS.AP_INVOICES_ALL AIA, 
   APPS.AP_TERMS_TL ATL, 
   APPS.AP_TERMS_TL ATL1 
WHERE 
       PL.PO_LINE_ID = POL.PO_LINE_ID  
  AND  pha.PO_HEADER_ID = PL.PO_HEADER_ID  
  AND  POL.LINE_LOCATION_ID = PDA.LINE_LOCATION_ID 
  AND  AIDA.INVOICE_ID = AIA.INVOICE_ID 
  AND  PDA.PO_DISTRIBUTION_ID = AIDA.PO_DISTRIBUTION_ID  
  AND  AIA.TERMS_ID = ATL.TERM_ID 
  AND  pha.TERMS_ID = ATL1.TERM_ID 
  AND  AIA.invoice_id=12312312;

Query - Supplier - Site- Active Bank Information - Oracle EBS

SELECT
   pv.segment1 vendor_number
 , pv.vendor_name
 , pvs.org_id
 , pvs.vendor_site_code
 , ibv.home_country || '-' || ibv.bank_name bank_name
 , abb.home_country || '-' || abb.bank_branch_name bank_branch_name
 , aba.bank_account_num
 , hepa.default_payment_method_code pay_method
 , hepa.inactive_date assignment_inactive_date
 , ipiua.order_of_preference uses_preference
 , ipiua.start_date uses_start_date
 , ipiua.end_date uses_end_date
 , pv.vendor_id
 , pvs.vendor_site_id
 , ibv.bank_party_id
 , abb.branch_party_id
 , aba.ext_bank_account_id
 , hepa.ext_payee_id
 , hepa.payee_party_id
 , ipiua.instrument_payment_use_id
FROM
   ap.ap_suppliers pv
 , ap.ap_supplier_sites_all pvs
 , iby_ext_banks_v ibv
 , iby_ext_bank_branches_v abb
 , iby_ext_bank_accounts aba
 , iby_external_payees_all hepa
 , iby_pmt_instr_uses_all ipiua
WHERE
      pv.vendor_id          = 2684821
  AND pv.vendor_id          = pvs.vendor_id
  AND pvs.vendor_site_id    = hepa.supplier_site_id
  AND hepa.org_id           = pvs.org_id
  AND nvl( pvs.inactive_date, SYSDATE ) >= SYSDATE
  AND nvl( hepa.inactive_date, SYSDATE ) >= SYSDATE
  AND hepa.ext_payee_id     = ipiua.ext_pmt_party_id (+)
  AND ipiua.instrument_id   = aba.ext_bank_account_id (+)
  AND nvl( ipiua.end_date, SYSDATE ) >= SYSDATE
  AND aba.branch_id         = abb.branch_party_id (+)
  AND ibv.bank_party_id (+) = abb.bank_party_id;          

Query to Extract PR => PO => Invoices Approval Sequence - By and Time

Query Columns:
-----------------

Column Name             Type          
------------------- ---------------------- 
PR_Number                    VARCHAR2(20)  
PR_CREATION_DATE             DATE          
PR_Approver_1                VARCHAR2(240) 
PR_Approver1_DATE            DATE          
PR_Approver_2                VARCHAR2(240) 
PR_Approver2_DATE            DATE          
PR_Approver_3                VARCHAR2(240) 
PR_Approver3_DATE            DATE          
PR_Approver_4                VARCHAR2(240) 
PR_Approver4_DATE            DATE          
PR_Approver_5                VARCHAR2(240) 
PR_Approver5_DATE            DATE          
PR_Approver_6                VARCHAR2(240) 
PR_Approver6_DATE            DATE          
PR_Approver_7                VARCHAR2(240) 
PR_Approver7_DATE            DATE          
PR_Approver_8                VARCHAR2(240) 
PR_Approver8_DATE            DATE          
PR_Approver_9                VARCHAR2(240) 
PR_Approver9_DATE            DATE          
PR_Approver_10               VARCHAR2(240) 
PR_Approver10_DATE           DATE          
PO_NUMBER                    VARCHAR2(20)  
PO_CREATION_DATE             DATE          
PO_Approver_1                VARCHAR2(240) 
PO_Approver1_DATE            DATE          
PO_Approver_2                VARCHAR2(240) 
PO_Approver2_DATE            DATE          
PO_Approver_3                VARCHAR2(240) 
PO_Approver3_DATE            DATE          
PO_Approver_4                VARCHAR2(240) 
PO_Approver4_DATE            DATE          
PO_Approver_5                VARCHAR2(240) 
PO_Approver5_DATE            DATE          
PO_Approver_6                VARCHAR2(240) 
PO_Approver6_DATE            DATE          
PO_Approver_7                VARCHAR2(240) 
PO_Approver7_DATE            DATE          
PO_Approver_8                VARCHAR2(240) 
PO_Approver8_DATE            DATE          
PO_Approver_9                VARCHAR2(240) 
PO_Approver9_DATE            DATE          
PO_Approver_10               VARCHAR2(240) 
PO_Approver10_DATE           DATE          
INVOICE_NUMBER               VARCHAR2(50)  
Invoice Date                 DATE          
INV_Approver1                VARCHAR2(150) 
INV_Approver1_DATE           DATE          
INV_Approver2                VARCHAR2(150) 
INV_Approver2_DATE           DATE          
INV_Approver3                VARCHAR2(150) 
INV_Approver3_DATE           DATE          
INV_Approver4                VARCHAR2(150) 
INV_Approver4_DATE           DATE          
INV_Approver5                VARCHAR2(150) 
INV_Approver5_DATE           DATE          
INV_Approver6                VARCHAR2(150) 
INV_Approver6_DATE           DATE          
INV_Approver7                VARCHAR2(150) 
INV_Approver7_DATE           DATE          
INV_Approver8                VARCHAR2(150) 
INV_Approver8_DATE           DATE          
INV_Approver9                VARCHAR2(150) 
INV_Approver9_DATE           DATE          
INV_Approver10               VARCHAR2(150) 
INV_Approver10_DATE          DATE          

Query
------
SELECT
  prha.segment1 "PR Number"
 , prha.creation_date pr_creation_date
 , nvl(
    (
      SELECT
        papf.full_name
      FROM
        apps.po_action_history pah
      , apps.fnd_user fu
      WHERE
          object_id              = prha.requisition_header_id
        AND pah.employee_id        = fu.employee_id
        AND fu.employee_id         = papf.person_id
        AND SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date
        AND pah.object_type_code   = 'REQUISITION'
        AND pah.action_code        = 'APPROVE'
        AND pah.sequence_num       = 0
    )
  , 'NO_APPROVER'
  ) "PR Approver 1"
 , nvl(
    (
      SELECT
        pah.last_update_date
      FROM
        apps.po_action_history pah
      , apps.fnd_user fu
      WHERE
          object_id              = prha.requisition_header_id
        AND pah.employee_id        = fu.employee_id
        AND fu.employee_id         = papf.person_id
        AND SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date
        AND pah.object_type_code   = 'REQUISITION'
        AND pah.action_code        = 'APPROVE'
        AND pah.sequence_num       = 0
    )
  , NULL
  ) "PR Approver1_DATE"
 , nvl(
    (
      SELECT
        papf.full_name
      FROM
        apps.po_action_history pah
      , apps.fnd_user fu
      WHERE
          object_id              = prha.requisition_header_id
        AND pah.employee_id        = fu.employee_id
        AND fu.employee_id         = papf.person_id
        AND SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date
        AND pah.object_type_code   = 'REQUISITION'
        AND pah.action_code        = 'APPROVE'
        AND pah.sequence_num       = 1
    )
  , 'NO_APPROVER'
  ) "PR Approver 2"
 , nvl(
    (
      SELECT
        pah.last_update_date
      FROM
        apps.po_action_history pah
      , apps.fnd_user fu
      WHERE
          object_id              = prha.requisition_header_id
        AND pah.employee_id        = fu.employee_id
        AND fu.employee_id         = papf.person_id
        AND SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date
        AND pah.object_type_code   = 'REQUISITION'
        AND pah.action_code        = 'APPROVE'
        AND pah.sequence_num       = 1
    )
  , NULL
  ) "PR Approver2_DATE"
 , nvl(
    (
      SELECT
        papf.full_name
      FROM
        apps.po_action_history pah
      , apps.fnd_user fu
      WHERE
          object_id              = prha.requisition_header_id
        AND pah.employee_id        = fu.employee_id
        AND fu.employee_id         = papf.person_id
        AND SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date
        AND pah.object_type_code   = 'REQUISITION'
        AND pah.action_code        = 'APPROVE'
        AND pah.sequence_num       = 2
    )
  , 'NO_APPROVER'
  ) "PR Approver 3"
 , nvl(
    (
      SELECT
        pah.last_update_date
      FROM
        apps.po_action_history pah
      , apps.fnd_user fu
      WHERE
          object_id              = prha.requisition_header_id
        AND pah.employee_id        = fu.employee_id
        AND fu.employee_id         = papf.person_id
        AND SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date
        AND pah.object_type_code   = 'REQUISITION'
        AND pah.action_code        = 'APPROVE'
        AND pah.sequence_num       = 2
    )
  , NULL
  ) "PR Approver3_DATE"
 , nvl(
    (
      SELECT
        papf.full_name
      FROM
        apps.po_action_history pah
      , apps.fnd_user fu
      WHERE
          object_id              = prha.requisition_header_id
        AND pah.employee_id        = fu.employee_id
        AND fu.employee_id         = papf.person_id
        AND SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date
        AND pah.object_type_code   = 'REQUISITION'
        AND pah.action_code        = 'APPROVE'
        AND pah.sequence_num       = 3
    )
  , 'NO_APPROVER'
  ) "PR Approver 4"
 , nvl(
    (
      SELECT
        pah.last_update_date
      FROM
        apps.po_action_history pah
      , apps.fnd_user fu
      WHERE
          object_id              = prha.requisition_header_id
        AND pah.employee_id        = fu.employee_id
        AND fu.employee_id         = papf.person_id
        AND SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date
        AND pah.object_type_code   = 'REQUISITION'
        AND pah.action_code        = 'APPROVE'
        AND pah.sequence_num       = 3
    )
  , NULL
  ) "PR Approver4_DATE"
 , nvl(
    (
      SELECT
        papf.full_name
      FROM
        apps.po_action_history pah
      , apps.fnd_user fu
      WHERE
          object_id              = prha.requisition_header_id
        AND pah.employee_id        = fu.employee_id
        AND fu.employee_id         = papf.person_id
        AND SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date
        AND pah.object_type_code   = 'REQUISITION'
        AND pah.action_code        = 'APPROVE'
        AND pah.sequence_num       = 4
    )
  , 'NO_APPROVER'
  ) "PR Approver 5"
 , nvl(
    (
      SELECT
        pah.last_update_date
      FROM
        apps.po_action_history pah
      , apps.fnd_user fu
      WHERE
          object_id              = prha.requisition_header_id
        AND pah.employee_id        = fu.employee_id
        AND fu.employee_id         = papf.person_id
        AND SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date
        AND pah.object_type_code   = 'REQUISITION'
        AND pah.action_code        = 'APPROVE'
        AND pah.sequence_num       = 4
    )
  , NULL
  ) "PR Approver5_DATE"
 , nvl(
    (
      SELECT
        papf.full_name
      FROM
        apps.po_action_history pah
      , apps.fnd_user fu
      WHERE
          object_id              = prha.requisition_header_id
        AND pah.employee_id        = fu.employee_id
        AND fu.employee_id         = papf.person_id
        AND SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date
        AND pah.object_type_code   = 'REQUISITION'
        AND pah.action_code        = 'APPROVE'
        AND pah.sequence_num       = 5
    )
  , 'NO_APPROVER'
  ) "PR Approver 6"
 , nvl(
    (
      SELECT
        pah.last_update_date
      FROM
        apps.po_action_history pah
      , apps.fnd_user fu
      WHERE
          object_id              = prha.requisition_header_id
        AND pah.employee_id        = fu.employee_id
        AND fu.employee_id         = papf.person_id
        AND SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date
        AND pah.object_type_code   = 'REQUISITION'
        AND pah.action_code        = 'APPROVE'
        AND pah.sequence_num       = 5
    )
  , NULL
  ) "PR Approver6_DATE"
 , nvl(
    (
      SELECT
        papf.full_name
      FROM
        apps.po_action_history pah
      , apps.fnd_user fu
      WHERE
          object_id              = prha.requisition_header_id
        AND pah.employee_id        = fu.employee_id
        AND fu.employee_id         = papf.person_id
        AND SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date
        AND pah.object_type_code   = 'REQUISITION'
        AND pah.action_code        = 'APPROVE'
        AND pah.sequence_num       = 6
    )
  , 'NO_APPROVER'
  ) "PR Approver 7"
 , nvl(
    (
      SELECT
        pah.last_update_date
      FROM
        apps.po_action_history pah
      , apps.fnd_user fu
      WHERE
          object_id              = prha.requisition_header_id
        AND pah.employee_id        = fu.employee_id
        AND fu.employee_id         = papf.person_id
        AND SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date
        AND pah.object_type_code   = 'REQUISITION'
        AND pah.action_code        = 'APPROVE'
        AND pah.sequence_num       = 6
    )
  , NULL
  ) "PR Approver7_DATE"
 , nvl(
    (
      SELECT
        papf.full_name
      FROM
        apps.po_action_history pah
      , apps.fnd_user fu
      WHERE
          object_id              = prha.requisition_header_id
        AND pah.employee_id        = fu.employee_id
        AND fu.employee_id         = papf.person_id
        AND SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date
        AND pah.object_type_code   = 'REQUISITION'
        AND pah.action_code        = 'APPROVE'
        AND pah.sequence_num       = 7
    )
  , 'NO_APPROVER'
  ) "PR Approver 8"
 , nvl(
    (
      SELECT
        pah.last_update_date
      FROM
        apps.po_action_history pah
      , apps.fnd_user fu
      WHERE
          object_id              = prha.requisition_header_id
        AND pah.employee_id        = fu.employee_id
        AND fu.employee_id         = papf.person_id
        AND SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date
        AND pah.object_type_code   = 'REQUISITION'
        AND pah.action_code        = 'APPROVE'
        AND pah.sequence_num       = 7
    )
  , NULL
  ) "PR Approver8_DATE"
 , nvl(
    (
      SELECT
        papf.full_name
      FROM
        apps.po_action_history pah
      , apps.fnd_user fu
      WHERE
          object_id              = prha.requisition_header_id
        AND pah.employee_id        = fu.employee_id
        AND fu.employee_id         = papf.person_id
        AND fu.employee_id         = papf.person_id
        AND SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date
        AND pah.object_type_code   = 'REQUISITION'
        AND pah.action_code        = 'APPROVE'
        AND pah.sequence_num       = 8
    )
  , 'NO_APPROVER'
  ) "PR Approver 8"
 , nvl(
    (
      SELECT
        pah.last_update_date
      FROM
        apps.po_action_history pah
      , apps.fnd_user fu
      WHERE
          object_id              = prha.requisition_header_id
        AND pah.employee_id        = fu.employee_id
        AND fu.employee_id         = papf.person_id
        AND SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date
        AND pah.object_type_code   = 'REQUISITION'
        AND pah.action_code        = 'APPROVE'
        AND pah.sequence_num       = 8
    )
  , NULL
  ) "PR Approver9_DATE"
 , nvl(
    (
      SELECT
        papf.full_name
      FROM
        apps.po_action_history pah
      , apps.fnd_user fu
      WHERE
          object_id              = prha.requisition_header_id
        AND pah.employee_id        = fu.employee_id
        AND fu.employee_id         = papf.person_id
        AND SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date
        AND pah.object_type_code   = 'REQUISITION'
        AND pah.action_code        = 'APPROVE'
        AND pah.sequence_num       = 9
    )
  , 'NO_APPROVER'
  ) "PR Approver 10"
 , nvl(
    (
      SELECT
        pah.last_update_date
      FROM
        apps.po_action_history pah
      , apps.fnd_user fu
      WHERE
          object_id              = prha.requisition_header_id
        AND pah.employee_id        = fu.employee_id
        AND fu.employee_id         = papf.person_id
        AND SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date
        AND pah.object_type_code   = 'REQUISITION'
        AND pah.action_code        = 'APPROVE'
        AND pah.sequence_num       = 9
    )
  , NULL
  ) "PR Approver10_DATE"
 , pha.segment1 po_number
 , pha.creation_date po_creation_date
 , nvl(
    (
      SELECT
        papf.full_name
      FROM
        po.po_action_history pah
      , apps.fnd_user fu
      WHERE
          object_id              = pha.po_header_id
        AND pah.employee_id        = fu.employee_id
        AND fu.employee_id         = papf.person_id
        AND SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date
        AND pah.object_type_code   = 'PO'
        AND pah.action_code        = 'APPROVE'
        AND pah.sequence_num       = 0
    )
  , 'NO_APPROVER'
  ) "PO Approver 1"
 , nvl(
    (
      SELECT
        pah.last_update_date
      FROM
        apps.po_action_history pah
      , apps.fnd_user fu
      WHERE
          object_id              = prha.requisition_header_id
        AND pah.employee_id        = fu.employee_id
        AND fu.employee_id         = papf.person_id
        AND SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date
        AND pah.object_type_code   = 'PO'
        AND pah.action_code        = 'APPROVE'
        AND pah.sequence_num       = 0
    )
  , NULL
  ) "PO Approver1_DATE"
 , nvl(
    (
      SELECT
        papf.full_name
      FROM
        po.po_action_history pah
      , apps.fnd_user fu
      WHERE
          object_id              = pha.po_header_id
        AND pah.employee_id        = fu.employee_id
        AND fu.employee_id         = papf.person_id
        AND SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date
        AND pah.object_type_code   = 'PO'
        AND pah.action_code        = 'APPROVE'
        AND pah.sequence_num       = 1
    )
  , 'NO_APPROVER'
  ) "PO Approver 2"
 , nvl(
    (
      SELECT
        pah.last_update_date
      FROM
        apps.po_action_history pah
      , apps.fnd_user fu
      WHERE
          object_id              = prha.requisition_header_id
        AND pah.employee_id        = fu.employee_id
        AND fu.employee_id         = papf.person_id
        AND SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date
        AND pah.object_type_code   = 'PO'
        AND pah.action_code        = 'APPROVE'
        AND pah.sequence_num       = 1
    )
  , NULL
  ) "PO Approver2_DATE"
 , nvl(
    (
      SELECT
        papf.full_name
      FROM
        po.po_action_history pah
      , apps.fnd_user fu
      WHERE
          object_id              = pha.po_header_id
        AND pah.employee_id        = fu.employee_id
        AND fu.employee_id         = papf.person_id
        AND SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date
        AND pah.object_type_code   = 'PO'
        AND pah.action_code        = 'APPROVE'
        AND pah.sequence_num       = 2
    )
  , 'NO_APPROVER'
  ) "PO Approver 3"
 , nvl(
    (
      SELECT
        pah.last_update_date
      FROM
        apps.po_action_history pah
      , apps.fnd_user fu
      WHERE
          object_id              = prha.requisition_header_id
        AND pah.employee_id        = fu.employee_id
        AND fu.employee_id         = papf.person_id
        AND SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date
        AND pah.object_type_code   = 'PO'
        AND pah.action_code        = 'APPROVE'
        AND pah.sequence_num       = 2
    )
  , NULL
  ) "PO Approver3_DATE"
 , nvl(
    (
      SELECT
        papf.full_name
      FROM
        po.po_action_history pah
      , apps.fnd_user fu
      WHERE
          object_id              = pha.po_header_id
        AND pah.employee_id        = fu.employee_id
        AND fu.employee_id         = papf.person_id
        AND SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date
        AND pah.object_type_code   = 'PO'
        AND pah.action_code        = 'APPROVE'
        AND pah.sequence_num       = 3
    )
  , 'NO_APPROVER'
  ) "PO Approver 4"
 , nvl(
    (
      SELECT
        pah.last_update_date
      FROM
        apps.po_action_history pah
      , apps.fnd_user fu
      WHERE
          object_id              = prha.requisition_header_id
        AND pah.employee_id        = fu.employee_id
        AND fu.employee_id         = papf.person_id
        AND SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date
        AND pah.object_type_code   = 'PO'
        AND pah.action_code        = 'APPROVE'
        AND pah.sequence_num       = 3
    )
  , NULL
  ) "PO Approver4_DATE"
 , nvl(
    (
      SELECT
        papf.full_name
      FROM
        po.po_action_history pah
      , apps.fnd_user fu
      WHERE
          object_id              = pha.po_header_id
        AND pah.employee_id        = fu.employee_id
        AND fu.employee_id         = papf.person_id
        AND SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date
        AND pah.object_type_code   = 'PO'
        AND pah.action_code        = 'APPROVE'
        AND pah.sequence_num       = 4
    )
  , 'NO_APPROVER'
  ) "PO Approver 5"
 , nvl(
    (
      SELECT
        pah.last_update_date
      FROM
        apps.po_action_history pah
      , apps.fnd_user fu
      WHERE
          object_id              = prha.requisition_header_id
        AND pah.employee_id        = fu.employee_id
        AND fu.employee_id         = papf.person_id
        AND SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date
        AND pah.object_type_code   = 'PO'
        AND pah.action_code        = 'APPROVE'
        AND pah.sequence_num       = 4
    )
  , NULL
  ) "PO Approver5_DATE"
 , nvl(
    (
      SELECT
        papf.full_name
      FROM
        po.po_action_history pah
      , apps.fnd_user fu
      WHERE
          object_id              = pha.po_header_id
        AND pah.employee_id        = fu.employee_id
        AND fu.employee_id         = papf.person_id
        AND SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date
        AND pah.object_type_code   = 'PO'
        AND pah.action_code        = 'APPROVE'
        AND pah.sequence_num       = 5
    )
  , 'NO_APPROVER'
  ) "PO Approver 6"
 , nvl(
    (
      SELECT
        pah.last_update_date
      FROM
        apps.po_action_history pah
      , apps.fnd_user fu
      WHERE
          object_id              = prha.requisition_header_id
        AND pah.employee_id        = fu.employee_id
        AND fu.employee_id         = papf.person_id
        AND SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date
        AND pah.object_type_code   = 'PO'
        AND pah.action_code        = 'APPROVE'
        AND pah.sequence_num       = 5
    )
  , NULL
  ) "PO Approver6_DATE"
 , nvl(
    (
      SELECT
        papf.full_name
      FROM
        po.po_action_history pah
      , apps.fnd_user fu
      WHERE
          object_id              = pha.po_header_id
        AND pah.employee_id        = fu.employee_id
        AND fu.employee_id         = papf.person_id
        AND SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date
        AND pah.object_type_code   = 'PO'
        AND pah.action_code        = 'APPROVE'
        AND pah.sequence_num       = 6
    )
  , 'NO_APPROVER'
  ) "PO Approver 7"
 , nvl(
    (
      SELECT
        pah.last_update_date
      FROM
        apps.po_action_history pah
      , apps.fnd_user fu
      WHERE
          object_id              = prha.requisition_header_id
        AND pah.employee_id        = fu.employee_id
        AND fu.employee_id         = papf.person_id
        AND SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date
        AND pah.object_type_code   = 'PO'
        AND pah.action_code        = 'APPROVE'
        AND pah.sequence_num       = 6
    )
  , NULL
  ) "PO Approver7_DATE"
 , nvl(
    (
      SELECT
        papf.full_name
      FROM
        po.po_action_history pah
      , apps.fnd_user fu
      WHERE
          object_id              = pha.po_header_id
        AND pah.employee_id        = fu.employee_id
        AND fu.employee_id         = papf.person_id
        AND SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date
        AND pah.object_type_code   = 'PO'
        AND pah.action_code        = 'APPROVE'
        AND pah.sequence_num       = 7
    )
  , 'NO_APPROVER'
  ) "PO Approver 8"
 , nvl(
    (
      SELECT
        pah.last_update_date
      FROM
        apps.po_action_history pah
      , apps.fnd_user fu
      WHERE
          object_id              = prha.requisition_header_id
        AND pah.employee_id        = fu.employee_id
        AND fu.employee_id         = papf.person_id
        AND SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date
        AND pah.object_type_code   = 'PO'
        AND pah.action_code        = 'APPROVE'
        AND pah.sequence_num       = 7
    )
  , NULL
  ) "PO Approver8_DATE"
 , nvl(
    (
      SELECT
        papf.full_name
      FROM
        po.po_action_history pah
      , apps.fnd_user fu
      WHERE
          object_id              = pha.po_header_id
        AND pah.employee_id        = fu.employee_id
        AND fu.employee_id         = papf.person_id
        AND SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date
        AND pah.object_type_code   = 'PO'
        AND pah.action_code        = 'APPROVE'
        AND pah.sequence_num       = 8
    )
  , 'NO_APPROVER'
  ) "PO Approver 9"
 , nvl(
    (
      SELECT
        pah.last_update_date
      FROM
        apps.po_action_history pah
      , apps.fnd_user fu
      WHERE
          object_id              = prha.requisition_header_id
        AND pah.employee_id        = fu.employee_id
        AND fu.employee_id         = papf.person_id
        AND SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date
        AND pah.object_type_code   = 'PO'
        AND pah.action_code        = 'APPROVE'
        AND pah.sequence_num       = 8
    )
  , NULL
  ) "PO Approver9_DATE"
 , nvl(
    (
      SELECT
        papf.full_name
      FROM
        po.po_action_history pah
      , apps.fnd_user fu
      WHERE
          object_id              = pha.po_header_id
        AND pah.employee_id        = fu.employee_id
        AND fu.employee_id         = papf.person_id
        AND SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date
        AND pah.object_type_code   = 'PO'
        AND pah.action_code        = 'APPROVE'
        AND pah.sequence_num       = 9
    )
  , 'NO_APPROVER'
  ) "PO Approver 10"
 , nvl(
    (
      SELECT
        pah.last_update_date
      FROM
        apps.po_action_history pah
      , apps.fnd_user fu
      WHERE
          object_id              = prha.requisition_header_id
        AND pah.employee_id        = fu.employee_id
        AND fu.employee_id         = papf.person_id
        AND SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date
        AND pah.object_type_code   = 'PO'
        AND pah.action_code        = 'APPROVE'
        AND pah.sequence_num       = 9
    )
  , NULL
  ) "PO Approver10_DATE"
 , aia.invoice_num invoice_number
 , aia.invoice_date "Invoice Date"
 , nvl(
    (
      SELECT
        approver_name
      FROM
        (
          SELECT
            approver_name
          , ROWNUM r
          FROM
            ap_inv_aprvl_hist_all aiah
          , ap_invoices_all aiaa
          WHERE
              aiah.invoice_id   = aiaa.invoice_id
            AND aiah.org_id       = aiaa.org_id
        )
      WHERE
        r   = 1
    )
  , 'NO_APPROVER'
  ) "INV Approver1"
 , nvl(
    (
      SELECT
        last_update_date
      FROM
        (
          SELECT
            aiah.last_update_date
          , ROWNUM r
          FROM
            ap_inv_aprvl_hist_all aiah
          , ap_invoices_all aiaa
          WHERE
              aiah.invoice_id   = aiaa.invoice_id
            AND aiah.org_id       = aiaa.org_id
        )
      WHERE
        r   = 1
    )
  , NULL
  ) "INV Approver1_DATE"
 , nvl(
    (
      SELECT
        approver_name
      FROM
        (
          SELECT
            approver_name
          , ROWNUM r
          FROM
            ap_inv_aprvl_hist_all aiah
          , ap_invoices_all aiaa
          WHERE
              aiah.invoice_id   = aiaa.invoice_id
            AND aiah.org_id       = aiaa.org_id
        )
      WHERE
        r   = 2
    )
  , 'NO_APPROVER'
  ) "INV Approver2"
 , nvl(
    (
      SELECT
        last_update_date
      FROM
        (
          SELECT
            aiah.last_update_date
          , ROWNUM r
          FROM
            ap_inv_aprvl_hist_all aiah
          , ap_invoices_all aiaa
          WHERE
              aiah.invoice_id   = aiaa.invoice_id
            AND aiah.org_id       = aiaa.org_id
        )
      WHERE
        r   = 2
    )
  , NULL
  ) "INV Approver2_DATE"
 , nvl(
    (
      SELECT
        approver_name
      FROM
        (
          SELECT
            approver_name
          , ROWNUM r
          FROM
            ap_inv_aprvl_hist_all aiah
          , ap_invoices_all aiaa
          WHERE
              aiah.invoice_id   = aiaa.invoice_id
            AND aiah.org_id       = aiaa.org_id
        )
      WHERE
        r   = 3
    )
  , 'NO_APPROVER'
  ) "INV Approver3"
 , nvl(
    (
      SELECT
        last_update_date
      FROM
        (
          SELECT
            aiah.last_update_date
          , ROWNUM r
          FROM
            ap_inv_aprvl_hist_all aiah
          , ap_invoices_all aiaa
          WHERE
              aiah.invoice_id   = aiaa.invoice_id
            AND aiah.org_id       = aiaa.org_id
        )
      WHERE
        r   = 3
    )
  , NULL
  ) "INV Approver3_DATE"
 , nvl(
    (
      SELECT
        approver_name
      FROM
        (
          SELECT
            approver_name
          , ROWNUM r
          FROM
            ap_inv_aprvl_hist_all aiah
          , ap_invoices_all aiaa
          WHERE
              aiah.invoice_id   = aiaa.invoice_id
            AND aiah.org_id       = aiaa.org_id
        )
      WHERE
        r   = 4
    )
  , 'NO_APPROVER'
  ) "INV Approver4"
 , nvl(
    (
      SELECT
        last_update_date
      FROM
        (
          SELECT
            aiah.last_update_date
          , ROWNUM r
          FROM
            ap_inv_aprvl_hist_all aiah
          , ap_invoices_all aiaa
          WHERE
              aiah.invoice_id   = aiaa.invoice_id
            AND aiah.org_id       = aiaa.org_id
        )
      WHERE
        r   = 4
    )
  , NULL
  ) "INV Approver4_DATE"
 , nvl(
    (
      SELECT
        approver_name
      FROM
        (
          SELECT
            approver_name
          , ROWNUM r
          FROM
            ap_inv_aprvl_hist_all aiah
          , ap_invoices_all aiaa
          WHERE
              aiah.invoice_id   = aiaa.invoice_id
            AND aiah.org_id       = aiaa.org_id
        )
      WHERE
        r   = 5
    )
  , 'NO_APPROVER'
  ) "INV Approver5"
 , nvl(
    (
      SELECT
        last_update_date
      FROM
        (
          SELECT
            aiah.last_update_date
          , ROWNUM r
          FROM
            ap_inv_aprvl_hist_all aiah
          , ap_invoices_all aiaa
          WHERE
              aiah.invoice_id   = aiaa.invoice_id
            AND aiah.org_id       = aiaa.org_id
        )
      WHERE
        r   = 5
    )
  , NULL
  ) "INV Approver5_DATE"
 , nvl(
    (
      SELECT
        approver_name
      FROM
        (
          SELECT
            approver_name
          , ROWNUM r
          FROM
            ap_inv_aprvl_hist_all aiah
          , ap_invoices_all aiaa
          WHERE
              aiah.invoice_id   = aiaa.invoice_id
            AND aiah.org_id       = aiaa.org_id
        )
      WHERE
        r   = 6
    )
  , 'NO_APPROVER'
  ) "INV Approver6"
 , nvl(
    (
      SELECT
        last_update_date
      FROM
        (
          SELECT
            aiah.last_update_date
          , ROWNUM r
          FROM
            ap_inv_aprvl_hist_all aiah
          , ap_invoices_all aiaa
          WHERE
              aiah.invoice_id   = aiaa.invoice_id
            AND aiah.org_id       = aiaa.org_id
        )
      WHERE
        r   = 6
    )
  , NULL
  ) "INV Approver6_DATE"
 , nvl(
    (
      SELECT
        approver_name
      FROM
        (
          SELECT
            approver_name
          , ROWNUM r
          FROM
            ap_inv_aprvl_hist_all aiah
          , ap_invoices_all aiaa
          WHERE
              aiah.invoice_id   = aiaa.invoice_id
            AND aiah.org_id       = aiaa.org_id
        )
      WHERE
        r   = 7
    )
  , 'NO_APPROVER'
  ) "INV Approver7"
 , nvl(
    (
      SELECT
        last_update_date
      FROM
        (
          SELECT
            aiah.last_update_date
          , ROWNUM r
          FROM
            ap_inv_aprvl_hist_all aiah
          , ap_invoices_all aiaa
          WHERE
              aiah.invoice_id   = aiaa.invoice_id
            AND aiah.org_id       = aiaa.org_id
        )
      WHERE
        r   = 7
    )
  , NULL
  ) "INV Approver7_DATE"
 , nvl(
    (
      SELECT
        approver_name
      FROM
        (
          SELECT
            approver_name
          , ROWNUM r
          FROM
            ap_inv_aprvl_hist_all aiah
          , ap_invoices_all aiaa
          WHERE
              aiah.invoice_id   = aiaa.invoice_id
            AND aiah.org_id       = aiaa.org_id
        )
      WHERE
        r   = 8
    )
  , 'NO_APPROVER'
  ) "INV Approver8"
 , nvl(
    (
      SELECT
        last_update_date
      FROM
        (
          SELECT
            aiah.last_update_date
          , ROWNUM r
          FROM
            ap_inv_aprvl_hist_all aiah
          , ap_invoices_all aiaa
          WHERE
              aiah.invoice_id   = aiaa.invoice_id
            AND aiah.org_id       = aiaa.org_id
        )
      WHERE
        r   = 8
    )
  , NULL
  ) "INV Approver8_DATE"
 , nvl(
    (
      SELECT
        approver_name
      FROM
        (
          SELECT
            approver_name
          , ROWNUM r
          FROM
            ap_inv_aprvl_hist_all aiah
          , ap_invoices_all aiaa
          WHERE
              aiah.invoice_id   = aiaa.invoice_id
            AND aiah.org_id       = aiaa.org_id
        )
      WHERE
        r   = 9
    )
  , 'NO_APPROVER'
  ) "INV Approver9"
 , nvl(
    (
      SELECT
        last_update_date
      FROM
        (
          SELECT
            aiah.last_update_date
          , ROWNUM r
          FROM
            ap_inv_aprvl_hist_all aiah
          , ap_invoices_all aiaa
          WHERE
              aiah.invoice_id   = aiaa.invoice_id
            AND aiah.org_id       = aiaa.org_id
        )
      WHERE
        r   = 9
    )
  , NULL
  ) "INV Approver9_DATE"
 , nvl(
    (
      SELECT
        approver_name
      FROM
        (
          SELECT
            approver_name
          , ROWNUM r
          FROM
            ap_inv_aprvl_hist_all aiah
          , ap_invoices_all aiaa
          WHERE
              aiah.invoice_id   = aiaa.invoice_id
            AND aiah.org_id       = aiaa.org_id
        )
      WHERE
        r   = 10
    )
  , 'NO_APPROVER'
  ) "INV Approver10"
 , nvl(
    (
      SELECT
        last_update_date
      FROM
        (
          SELECT
            aiah.last_update_date
          , ROWNUM r
          FROM
            ap_inv_aprvl_hist_all aiah
          , ap_invoices_all aiaa
          WHERE
              aiah.invoice_id   = aiaa.invoice_id
            AND aiah.org_id       = aiaa.org_id
        )
      WHERE
        r   = 10
    )
  , NULL
  ) "INV Approver10_DATE"
FROM
  apps.po_requisition_headers_all prha
 , apps.per_all_people_f papf
 , apps.po_requisition_lines_all prla
 , apps.po_req_distributions_all prda
 , apps.po_line_locations_all plla
 , apps.po_lines_all pla
 , apps.po_headers_all pha
 , apps.po_distributions_all pda
 , apps.ap_invoices_all aia
 , apps.ap_invoice_distributions_all aida
WHERE
    prha.preparer_id             = papf.person_id
  AND prha.requisition_header_id   = prla.requisition_header_id
  AND prla.requisition_line_id     = prda.requisition_line_id
  AND prla.line_location_id        = plla.line_location_id
  AND prda.distribution_id         = pda.req_distribution_id
  AND pda.po_line_id               = pla.po_line_id
  AND pda.po_header_id             = pha.po_header_id
  AND plla.po_line_id              = pla.po_line_id
  AND pla.po_header_id             = pha.po_header_id
  AND prha.authorization_status    = 'APPROVED'
  AND aia.invoice_id               = aida.invoice_id
  AND aida.po_distribution_id      = pda.po_distribution_id (+)
  AND trunc( prha.creation_date ) BETWEEN '01-JUN-2018' AND '01-JUN-2018';