Write a Query to validate if there were any POs (including Blanket POs) that were approve by the “owner”?

Query to validate if there were any Purchase Orders(POs) (including Blanket POs) that were approve by the “owner”? Where creator and approver of the Purchase Order(PO)  is same.

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 ;

Leave a Reply

Your email address will not be published. Required fields are marked *