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

Query to validate if there were any POs (including Blanket POs) that were modified by the “approver”?  Where creator and modifier or approver is different

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 ;

Leave a Reply

Your email address will not be published.