Oracle PO Archive extract

Oracle PO Archive extract

SELECT po_number, creation_date po_date, vendor_num, vendor_name,
revision_num, approved_date, closed_date,
change_type type_of_modification
FROM (SELECT pv.segment1 vendor_num, pv.vendor_name, pha.po_header_id,
pha.segment1 po_number, pha.agent_id, pha.creation_date,
pha.vendor_id, phaa.submit_date, pha.revision_num rev_num,
phaa.revision_num, pha.approved_flag, pha.approved_date,
pha.closed_date, phaa.closed_code, pha.cancel_flag,
CASE
WHEN ( NVL (pha.approved_flag, '01-JAN-1951') <>
'01-JAN-1951'
AND NVL (pha.closed_date, '01-JAN-1951') <>
'01-JAN-1951'
AND NVL (phaa.closed_code, 'OPEN') = 'OPEN'
AND pha.approved_flag <> 'F'
AND phaa.revision_num = pha.revision_num
)
THEN 'APPROVED & CLOSED'
WHEN ( NVL (pha.approved_flag, '01-JAN-1951') <>
'01-JAN-1951'
AND NVL (pha.closed_date, '01-JAN-1951') =
'01-JAN-1951'
AND pha.approved_flag = 'Y'
AND phaa.revision_num = pha.revision_num
)
THEN 'APPROVED'
WHEN ( NVL (phaa.closed_code, 'OPEN') = 'CLOSED'
AND pha.cancel_flag = 'Y'
)
THEN 'CANCELLED'
WHEN (pha.approved_flag = 'F')
THEN 'REJECTED'
WHEN phaa.submit_date IS NULL
THEN 'NEW'
WHEN phaa.revision_num < pha.revision_num
THEN 'CHANGE'
END change_type
FROM po_headers_all pha,
po_headers_archive_all phaa,
po_vendors pv
WHERE 1 = 1
AND phaa.po_header_id(+) = pha.po_header_id
AND pv.vendor_id = pha.vendor_id
ORDER BY pha.po_header_id, phaa.revision_num) po_archive