Query to Validate Modifications Made by ‘Approver’ to Purchase Orders, Including Blanket POs

The query to validate the existence of any POs (inclusive of Blanket POs) that were altered by the ‘approver,’ where the creator differs from the modifier or approver.

SELECT DISTINCT poh.org_id “ORG ID”,

poh.segment1 “PO NUM”,

poh.type_lookup_code “TYPE”,

(SELECT agent_name FROM po_agents_v WHERE agent_id=Poh.agent_id

)Buyer,

poh.creation_date,

(SELECT USER_NAME FROM apps.FND_USER WHERE USER_ID = poh.created_by) “PO_CREATED_BY”,

(SELECT USER_NAME FROM apps.FND_USER WHERE USER_ID = poah.created_by) “PO_APPROVED_BY”,

(SELECT USER_NAME FROM apps.FND_USER WHERE USER_ID = poh.last_updated_by) “PO_UPDATED_BY”,

(SELECT USER_NAME FROM apps.FND_USER WHERE USER_ID = pol.last_updated_by) “PO_LINE_UPDATED_BY”,

(SELECT USER_NAME FROM apps.FND_USER WHERE USER_ID = poll.last_updated_by) “PO_SHIPMENT_UPDATED_BY”,

(SELECT USER_NAME FROM apps.FND_USER WHERE USER_ID = pod.last_updated_by) “PO_DIST_UPDATED_BY”,

(SELECT full_name

FROM apps.per_all_people_f

WHERE person_id = poah.employee_id

AND effective_end_date > poah.creation_date

AND rownum =1) approver_name,

po_core_s.get_total(‘H’, poh.po_header_id) amount,

poh.currency_code,

poh.authorization_status “STATUS”,

poh.note_to_authorizer,

poh.approved_flag,

poh.approved_date,

poh.approval_required_flag,

poh.cancel_flag,

poh.document_creation_method,

poh.submit_date,

poh.closed_date,

poh.closed_code

FROM apps.po_headers_all poh,

apps.po_lines_all pol,

apps.po_line_locations_all poll,

apps.po_distributions_all pod,

apps.po_action_history poah

WHERE poh.po_header_id = pol.po_header_id

AND poll.po_line_id = pol.po_Line_id

AND poll.line_location_id = pod.line_location_id

AND poh.authorization_status = ‘APPROVED’

AND poh.Po_header_id = poah.object_id

AND poah.object_type_code = ‘PO’

AND poah.ACTION_CODE = ‘APPROVE’

AND poh.creation_date > TO_DATE(’01-JAN-2016′, ‘DD-MON-YYYY’)

AND (poh.last_updated_by = poah.created_by

OR pol.last_updated_by = poah.created_by

OR poll.last_updated_by = poah.created_by

OR pod.last_updated_by = poah.created_by)

AND poh.created_by <> poah.created_by ;