In Oracle ERP environments, maintaining data integrity and accountability is crucial, especially when it comes to Purchase Order (PO) approvals and modifications. It’s not uncommon for auditors or administrators to seek visibility into whether any approver has made unauthorized or unexpected changes to Purchase Orders, including Blanket Purchase Orders (BPOs) — after they were initially created by another user.
This SQL query provides a comprehensive validation mechanism to identify POs that were modified by the approver, ensuring that proper segregation of duties (SoD) and compliance standards are upheld.
Scenario
The purpose of this validation is to detect any POs (including Blanket POs) where the creator differs from the approver or modifier, indicating that the approver made post-approval updates.
This scenario is essential for audit trails, SoD reviews, and procurement compliance monitoring.
SQL Query: Validate Purchase Orders Modified by Approver
SELECT DISTINCT
poh.org_id AS “ORG ID”,
poh.segment1 AS “PO NUM”,
poh.type_lookup_code AS “TYPE”,
(SELECT agent_name
FROM po_agents_v
WHERE agent_id = poh.agent_id) AS “Buyer”,
poh.creation_date,
(SELECT USER_NAME FROM apps.FND_USER WHERE USER_ID = poh.created_by) AS “PO_CREATED_BY”,
(SELECT USER_NAME FROM apps.FND_USER WHERE USER_ID = poah.created_by) AS “PO_APPROVED_BY”,
(SELECT USER_NAME FROM apps.FND_USER WHERE USER_ID = poh.last_updated_by) AS “PO_UPDATED_BY”,
(SELECT USER_NAME FROM apps.FND_USER WHERE USER_ID = pol.last_updated_by) AS “PO_LINE_UPDATED_BY”,
(SELECT USER_NAME FROM apps.FND_USER WHERE USER_ID = poll.last_updated_by) AS “PO_SHIPMENT_UPDATED_BY”,
(SELECT USER_NAME FROM apps.FND_USER WHERE USER_ID = pod.last_updated_by) AS “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) AS “APPROVER_NAME”,
po_core_s.get_total(‘H’, poh.po_header_id) AS “AMOUNT”,
poh.currency_code,
poh.authorization_status AS “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;
How This Query Works
This query intelligently tracks data lineage and activity across multiple PO tables within Oracle Apps:
- PO_HEADERS_ALL – Captures header-level details (creation, approval, and update information).
- PO_LINES_ALL – Reflects line-level changes, including updates by approvers.
- PO_LINE_LOCATIONS_ALL – Validates shipment-related modifications.
- PO_DISTRIBUTIONS_ALL – Checks distribution-level updates.
- PO_ACTION_HISTORY – Logs all user actions, especially approvals.
By comparing the created_by and last_updated_by fields across these tables, the query highlights cases where the approver has modified the PO after approval, an important red flag for auditors.
Use Cases
- Audit & Compliance: Detects potential SoD violations where approvers alter purchase details post-approval.
- Internal Controls: Ensures clear separation between creator, approver, and modifier roles.
- Procurement Oversight: Helps procurement teams maintain transparency in PO lifecycle management.
- Data Integrity Review: Validates that only authorized users perform updates.
Business Impact
By executing this query, organizations can:
- Strengthen procurement governance frameworks.
- Minimize risks of unauthorized PO amendments.
- Improve audit readiness with accurate evidence of user activity.
- Align with compliance standards such as SOX, ISO 27001, and internal SoD policies.
Key Takeaways
Identifies Purchase Orders modified by approvers after approval.
Works across Standard and Blanket POs.
Enhances audit transparency within Oracle E-Business Suite.
Strengthens control mechanisms in procurement workflows.
Conclusion
In a robust Oracle ERP environment, query-driven auditing plays a vital role in safeguarding data integrity and enforcing accountability.
This SQL query not only enables real-time validation of approver activity but also empowers audit teams with actionable insights to maintain procurement discipline and compliance confidence.