3 Way PO Matching SQL Query in Oracle Fusion: Purchase Order, Receipt, and Invoice Reconciliation

-

3 Way Matching SQL Query in Oracle Fusion: Purchase Order, Receipt, and Invoice Reconciliation

In any procurement system, ensuring the accuracy of supplier payments is critical. One of the most robust methods for validating supplier invoices is the 3-way matching process. This blog breaks down the 3-way matching concept in Oracle E-Business Suite (EBS) or Oracle Fusion, explains its benefits, and presents a detailed SQL query that helps extract and reconcile PO, Receipt, and Invoice data.

What is 3-Way Matching ?

3-Way Matching is a process that compares the following three documents before payment is released:

  1. Purchase Order (PO) – What you ordered

  2. Receipt – What you received

  3. Invoice – What the supplier billed

This comparison ensures that you only pay for goods that were ordered and received, avoiding overpayments, duplicates, and fraud.

Matching Criteria

  Element          PO    Receipt           Invoice
   Quantity                 Ordered quantity         Quantity received              Quantity invoiced
   Amount           Unit price × Ordered Qty           Amount received                  Amount billed
Item/Service                Line items on PO         Goods received                 Billed line items
 Distribution            Charge accounts, taxes    Matched distributions           Invoiced distributions

Extract 3-Way Matching Data

Below is a comprehensive SQL query to pull data across PO, Receiving, and Invoices. It is helpful for audit, reconciliation, or payment troubleshooting in Oracle EBS or Fusion.

Query Breakdown:

  • PO details: po_headers_all, po_lines_all, po_line_locations_all, po_distributions_all

  • Receipts: rcv_transactions, rcv_shipment_headers, rcv_shipment_lines

  • Invoices: ap_invoices_all, ap_invoice_lines_all, ap_payment_schedules_all


SQL Code:

SELECT
    po.po_number,
    po.po_order_date,
    po.po_currency_code,
    po.po_line_number,
    po.po_status,
    po.quantity_p,
    po.amount_p,
    po.amount_received_p,
    po.po_line_qty,
    po.polineamount,
    po.quantity_received,
    po.po_promised_date,
    po.po_charge_account,
    po.promise_delivery_date,
    po.po_distribution_num,
    rcv.*,
    inv.invoice_num,
    inv.line_number,
    inv.invoice_line_amount_lc,
    inv.payment_status,
    inv.invoice_currency_code
FROM
    (
        SELECT
            pha.segment1 po_number,
            TO_CHAR(pha.creation_date, 'DD-Mon-YYYY', 'nls_date_language=english') po_order_date,
            pha.currency_code po_currency_code,
            pla.line_num po_line_number,
            pha.document_status po_status,
            pla.line_status po_line_status,
            pla.quantity quantity_p,
            plla.amount amount_p,
            plla.amount_received amount_received_p,
            CASE
                WHEN plla.quantity IS NULL THEN plla.amount
                ELSE pla.unit_price
            END polineamount,
            CASE
                WHEN pla.quantity IS NULL THEN plla.amount
                ELSE pla.quantity
            END po_line_qty,
            CASE
                WHEN pla.quantity IS NULL THEN plla.amount - plla.amount_received
                ELSE pla.quantity - plla.quantity_received
            END qty_open,
            CASE
                WHEN plla.quantity_received IS NULL THEN plla.amount_received
                ELSE plla.quantity_received
            END quantity_received,
            TO_CHAR(plla.promised_date, 'DD-Mon-YYYY', 'nls_date_language=english') po_promised_date,
            pda.distribution_num po_distribution_num,
            gcc1.segment1 || '-' || gcc1.segment2 || '-' || gcc1.segment3 || '-' || gcc1.segment4 || '-' ||
            gcc1.segment5 || '-' || gcc1.segment6 || '-' || gcc1.segment6 || '-' || gcc1.segment7 || '-' ||
            gcc1.segment8 || '-' || gcc1.segment9 po_charge_account,
            pda.req_distribution_id,
            plla.line_location_id,
            plla.promised_date promise_delivery_date,
            pha.po_header_id ppo_header_id,
            pla.po_line_id ppo_line_id
        FROM
            po_distributions_all pda,
            po_line_locations_all plla,
            po_lines_all pla,
            po_headers_all pha,
            gl_code_combinations gcc1
        WHERE
            pda.line_location_id = plla.line_location_id (+)
            AND plla.po_line_id = pla.po_line_id (+)
            AND pla.po_header_id = pha.po_header_id (+)
            AND pda.code_combination_id = gcc1.code_combination_id (+)
    ) po,
    (
        SELECT
            rt.po_header_id,
            rt.po_line_id,
            rt.po_line_location_id,
            rt.po_distribution_id,
            rt.transaction_id,
            rsh.receipt_num po_receipt_no,
            TO_CHAR(rsh.creation_date, 'DD-Mon-YYYY', 'nls_date_language=english') receipt_date,
            rsh.approval_status receipt_status,
            CASE
                WHEN rsl.quantity_received IS NULL THEN rsl.amount_received
                ELSE rsl.quantity_received
            END re_qty,
            rsh.SHIPMENT_NUM
        FROM
            po_headers_all pha1,
            rcv_shipment_headers rsh,
            rcv_shipment_lines rsl,
            rcv_transactions rt
        WHERE
            pha1.po_header_id = rt.po_header_id
            AND rt.shipment_header_id = rsh.shipment_header_id
            AND rsh.shipment_header_id = rsl.shipment_header_id
            AND rt.shipment_line_id = rsl.shipment_line_id
            AND pha1.po_header_id = rsl.po_header_id
            AND rt.transaction_type = 'RECEIVE'
    ) rcv,
    (
        SELECT
            aila.rcv_transaction_id,
            aila.po_line_id,
            aia.invoice_currency_code,
            aila.po_line_location_id,
            aila.po_distribution_id,
            aila.po_header_id,
            aia.invoice_id,
            aia.invoice_num,
            aila.line_number,
            aia.approval_status inv_status,
            DECODE(apsa.payment_status_flag, 'Y', 'Paid', 'P', 'Partially Paid', 'Unpaid') payment_status,
            (aila.amount) * (NVL(aia.exchange_rate, 1)) invoice_line_amount_lc
        FROM
            ap_invoices_all aia,
            ap_invoice_lines_all aila,
            ap_payment_schedules_all apsa
        WHERE
            aia.invoice_id = aila.invoice_id
            AND aia.cancelled_date IS NULL
            AND aia.invoice_id = apsa.invoice_id (+)
            AND aia.org_id = apsa.org_id (+)
    ) inv
WHERE
    po.line_location_id = rcv.po_line_location_id (+)
    AND rcv.transaction_id = inv.rcv_transaction_id (+)
    AND rcv.po_line_id = inv.po_line_id (+)
    AND rcv.po_line_location_id = inv.po_line_location_id (+)
    AND rcv.po_distribution_id = inv.po_distribution_id (+)
    AND rcv.po_header_id = inv.po_header_id (+)
    AND po.ppo_header_id = inv.po_header_id (+)
Location: Hyderabad, Telangana, India

Comments

Post a Comment

Popular posts from this blog

Query to find Responsibility and Request Group from Concurrent Program

-
 In this blog, we’ll break down a query that helps you trace a specific Concurrent Program through its Application , Request Group , and associated Responsibilities in Oracle EBS R12 Objective The goal of this query is to find: The User-Friendly Name of a Concurrent Program The Technical Concurrent Program Name The Application to which it belongs The Request Group under which it is registered The Responsibility that grants access to it This is especially useful for ensuring correct program registration or for debugging access issues. SQL Query:      SELECT DISTINCT      fcpl.user_concurrent_program_name,     fcp.concurrent_program_name,     fapp.application_name,     frg.request_group_name,     fnrtl.responsibility_name FROM      apps.fnd_request_groups         frg,     apps.fnd_application_tl         fapp,   ...
Read more »»»»

How to Extract User Role Details with SQL in Oracle Fusion

-
When working with Oracle HCM / Fusion Applications , administrators and developers often need to fetch details about users, their assigned roles, and their effective dates. This helps in audits, access reviews, and troubleshooting authorization issues. In this post, we’ll walk through a SQL query that retrieves user-role information from Oracle HCM tables. SQL Query:                          SELECT     pus.user_id             "User ID",     pus.username            "Username",     ppnaf.full_name         "Full Name",     prdn.role_common_name   "Role Common Name",     prdtl.role_name         "Role Name",     to_char(purs.start_date, 'DD-MON-YYYY') "Role Start Date",     to_char(purs.end_date, 'DD-MON-YYYY')   "...
Read more »»»»