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

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.