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';
No comments:
Post a Comment