Query to Confirm Approval of Purchase Orders, Including Blanket POs, by the ‘Owner’

The query to confirm the approval of Purchase Orders (POs), encompassing Blanket POs, by the ‘owner,’ where the creator and approver of the Purchase Order (PO) are identical.

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 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.created_by = poah.created_by ;