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

In Oracle ERP systems, maintaining accountability and transparency in the Purchase Order (PO) approval process is a critical compliance measure.
While some organizations enforce strict Segregation of Duties (SoD), there are legitimate cases where a PO creator and approver may be the same individual, often referred to as the ‘Owner’.

This SQL query is designed to validate and confirm Purchase Orders, including Blanket Purchase Orders (BPOs), that were created and approved by the same user.
It ensures that system administrators, auditors, and procurement managers can identify self-approved transactions efficiently.

Scenario

This query focuses on identifying instances where the creator and approver of a PO are identical.
Such cases are often flagged for audit review or internal validation to ensure compliance with corporate procurement policies and control frameworks.

This is particularly relevant for:

• Procurement audits
• Governance reviews
• SoD exception reporting
• Self-approval policy checks

SQL Query: Confirm Approval of Purchase Orders by Owner

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

How the Query Works

This SQL script examines Purchase Orders (POs) and Blanket Purchase Orders (BPOs) in Oracle E-Business Suite to determine whether the same user performed both the creation and approval actions.

Key Components Explained

• PO_HEADERS_ALL – Stores main PO information such as creation details, status, and buyer data.
• PO_ACTION_HISTORY – Records all workflow actions including approvals.
• PO_LINES_ALL, PO_LINE_LOCATIONS_ALL, and PO_DISTRIBUTIONS_ALL – Validate line, shipment, and distribution-level linkages.
• FND_USER – Translates user IDs to usernames for readability.
• PER_ALL_PEOPLE_F – Provides the approver’s full name and employee details.
  

By joining these tables, the query cross-verifies that the created_by and approved_by users are identical, confirming self-approval events.

Use Cases

• Audit Review: Identify Purchase Orders approved by their creators.
• Compliance Validation: Check adherence to SoD policies and corporate procurement controls.
• System Monitoring: Detect potential process loopholes where ownership and approval overlap.
• Governance Reporting: Support internal and external audits with clear, data-driven insights.

Business Value

Executing this validation query helps organizations:

• Strengthen internal audit readiness by identifying self-approved POs.
• Improve compliance transparency across procurement processes.
• Enable data-driven governance by highlighting potential control gaps.
• Maintain accountability through detailed tracking of ownership and approval actions.

Best Practices

• Regularly execute this query as part of monthly audit checks.
• Combine it with the “Approver Modified PO” query to ensure complete visibility.
• Integrate results with Oracle Audit Frameworks or Business Intelligence dashboards for automated monitoring.
• Use it to define SoD violation thresholds for process improvement.

Key Takeaways

• Confirms Purchase Orders approved by their creators (‘Owners’).
• Applies to both Standard and Blanket POs.
• Enhances procurement compliance and audit traceability.
• Strengthens data governance and accountability frameworks.

Conclusion

This query plays a vital role in strengthening the procurement governance landscape within Oracle ERP systems.
By confirming self-approved Purchase Orders, organizations can proactively identify process exceptions, uphold Segregation of Duties (SoD) integrity, and reinforce procurement transparency.At SQLGuru.ai, we bring you practical Oracle SQL insights, real-time compliance solutions, and governance tools designed to help organizations maintain control and confidence in their ERP environments.