Query - Concurrent Program Summary - Completed-Error-Warning-running-Terminated

SELECT fcpt.USER_CONCURRENT_PROGRAM_NAME PROGRAM_NAME ,trunc(fcr.ACTUAL_START_DATE) ACTUAL_START_DATE,fcr.phase_code
     ,sum(decode(fcr.status_code,'C',1,0))completed_program_cnt
     ,sum(decode(fcr.status_code,'E',1,0))error_program_cnt
     ,sum(decode(fcr.status_code,'W',1,0))warning_program_cnt
     ,sum(decode(fcr.status_code,'R',1,0))running_program_cnt
     ,sum(decode(fcr.status_code,'D',1,0))Terminated_program_cnt
     ,count(1) Total_program_count
FROM FND_CONCURRENT_REQUESTS fcr, FND_CONCURRENT_PROGRAMS_TL fcpt
WHERE fcr.CONCURRENT_PROGRAM_ID = fcpt.CONCURRENT_PROGRAM_ID
AND fcpt.USER_CONCURRENT_PROGRAM_NAME LIKE 'Accounting Program'
Group by trunc(fcr.ACTUAL_START_DATE),fcpt.USER_CONCURRENT_PROGRAM_NAME,fcr.phase_code
order by 1,2,3 ;

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';