PO Approval Hierarchy Query -R12

 

SELECT DISTINCT
pah.object_id,
pha.segment1 AS PO_NUMBER,
pah.Action_Code,
pah.Action_Date,
papf.full_name AS performed_by,
pah.Note,
pha.amount_limit,
pha.currency_code,
pha.rate,
pha.blanket_total_amount,
(pha.rate * pha.blanket_total_amount) AS BLANKET_TOTAL_AMOUNT_CAD,
abc.second_sign,
pah.object_revision_num AS Revision_Number
FROM po_action_history pah,
per_all_people_f papf,
po_headers_all pha,
( SELECT object_id,
Action_Code,
object_revision_num,
CASE WHEN COUNT (Action_Code) <= 1 THEN 'N' ELSE 'Y' END
AS SECOND_SIGN
FROM po_action_history
WHERE 1 = 1
AND Action_Code = 'FORWARD'
AND object_sub_type_code = 'BLANKET'
GROUP BY object_id, Action_Code, object_revision_num
HAVING COUNT (Action_Code) > 0) abc
WHERE pah.action_code = 'APPROVE'
AND pah.employee_id = papf.person_id
AND pah.object_id = pha.po_header_id
AND pah.object_id = abc.object_id
AND pah.object_revision_num = abc.object_revision_num
AND pha.segment1 = 'XX_PO_NUMBER' -- PO Number
AND pah.object_sub_type_code = 'BLANKET'
ORDER BY pha.segment1 ASC, pah.object_revision_num