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

Query to get Oracle Scheduled Concurrent Program Information

select * from
(
SELECT DISTINCT
fcr.concurrent_program_id
,fcpl.user_concurrent_program_name Concurrent_Program
,frl.responsibility_name
,DECODE(fcrc.class_type,'P','Periodic : '
       ||SUBSTR(fcrc.class_info,1,INSTR(fcrc.class_info,':',1,1)-1)
||' : '||DECODE(SUBSTR(fcrc.class_info,-3,1)
        ,'M','Month(s)','W','Week(s)','D','Day(s)'
        ,'H','Hour(s)','N','Minute(s)','')
||' : '||DECODE(SUBSTR(fcrc.class_info,-1),'C'
        ,'From Completion of Prior Run','S'
        ,'From Start of Prior Run',''),'')
||DECODE(fcrc.class_type,'S','Specific : '
||DECODE(SUBSTR(fcrc.class_info,1,1),'0','',':1:')
||DECODE(SUBSTR(fcrc.class_info,2,1),'0','',':2:')
||DECODE(SUBSTR(fcrc.class_info,3,1),'0','',':3:')
||DECODE(SUBSTR(fcrc.class_info,4,1),'0','',':4:')
||DECODE(SUBSTR(fcrc.class_info,5,1),'0','',':5:')
||DECODE(SUBSTR(fcrc.class_info,6,1),'0','',':6:')
||DECODE(SUBSTR(fcrc.class_info,7,1),'0','',':7:')
||DECODE(SUBSTR(fcrc.class_info,8,1),'0','',':8:')
||DECODE(SUBSTR(fcrc.class_info,9,1),'0','',':9:')
||DECODE(SUBSTR(fcrc.class_info,10,1),'0','',':10:')
||DECODE(SUBSTR(fcrc.class_info,11,1),'0','',':11:')
||DECODE(SUBSTR(fcrc.class_info,12,1),'0','',':12:')
||DECODE(SUBSTR(fcrc.class_info,13,1),'0','',':13:')
||DECODE(SUBSTR(fcrc.class_info,14,1),'0','',':14:')
||DECODE(SUBSTR(fcrc.class_info,15,1),'0','',':15:')
||DECODE(SUBSTR(fcrc.class_info,16,1),'0','',':16:')
||DECODE(SUBSTR(fcrc.class_info,17,1),'0','',':17:')
||DECODE(SUBSTR(fcrc.class_info,18,1),'0','',':18:')
||DECODE(SUBSTR(fcrc.class_info,19,1),'0','',':19:')
||DECODE(SUBSTR(fcrc.class_info,20,1),'0','',':20:')
||DECODE(SUBSTR(fcrc.class_info,21,1),'0','',':21:')
||DECODE(SUBSTR(fcrc.class_info,22,1),'0','',':22:')
||DECODE(SUBSTR(fcrc.class_info,23,1),'0','',':23:')
||DECODE(SUBSTR(fcrc.class_info,24,1),'0','',':24:')
||DECODE(SUBSTR(fcrc.class_info,25,1),'0','',':25:')
||DECODE(SUBSTR(fcrc.class_info,26,1),'0','',':26:')
||DECODE(SUBSTR(fcrc.class_info,27,1),'0','',':27:')
||DECODE(SUBSTR(fcrc.class_info,28,1),'0','',':28:')
||DECODE(SUBSTR(fcrc.class_info,29,1),'0','',':29:')
||DECODE(SUBSTR(fcrc.class_info,30,1),'0','',':30:')
||DECODE(SUBSTR(fcrc.class_info,31,1),'0','',':31:')
||DECODE(SUBSTR(fcrc.class_info,32,1),'0','',':Last Day:')
||DECODE(SUBSTR(fcrc.class_info,33,1),'0','',':Sunday:')
||DECODE(SUBSTR(fcrc.class_info,34,1),'0','',':Monday:')
||DECODE(SUBSTR(fcrc.class_info,35,1),'0','',':Tuesday:')
||DECODE(SUBSTR(fcrc.class_info,36,1),'0','',':Wednesday:')
||DECODE(SUBSTR(fcrc.class_info,37,1),'0','',':Thursday:')
||DECODE(SUBSTR(fcrc.class_info,38,1),'0','',':Friday:')
||DECODE(SUBSTR(fcrc.class_info,39,1),'0','',':Saturday:')
, '') scheduling_information
FROM apps.fnd_executables fe
, apps.fnd_concurrent_programs fcp
, apps.fnd_concurrent_programs_tl fcpl
, apps.fnd_concurrent_requests fcr
, apps.fnd_lookups fl
, apps.fnd_responsibility_tl frl
, apps.fnd_user fu
, apps.fnd_conc_req_summary_v fcrs
, apps.fnd_conc_release_classes fcrc
WHERE     fe.executable_id = fcp.executable_id
AND fcr.responsibility_application_id = frl.application_id
AND fcp.concurrent_program_id = fcr.concurrent_program_id
AND fcpl.concurrent_program_id = fcp.concurrent_program_id
AND frl.responsibility_id = fcr.responsibility_id
AND fu.user_id = fcr.requested_by
AND fcrc.release_class_id(+) = fcr.release_class_id
AND fl.lookup_code = fe.execution_method_code
AND fcrs.concurrent_program_id = fcr.concurrent_program_id
AND fcrs.request_id = fcr.request_id
AND fl.lookup_type = 'CP_EXECUTION_METHOD_CODE'
AND frl.language = 'US'
AND fcpl.language = 'US'
AND fcr.phase_code IN ('P','R')
AND nvl(fcr.hold_flag,'N') <> 'Y')
Where scheduling_information is not null;