PO Approval Hierarchy Query -R12

 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