PO Receipts/Deliveries Discrepancy – Oracle EBS SQL Report

Oracle E-Business Suite SQL report from the Enginatics Library powered by Blitz Report™. Oracle EBS module: Purchasing · all Purchasing reports

Overview

PO Receipts/Deliveries Discrepancy highlights, at purchase order line and release level, differences between the quantities and amounts received into receiving (transaction type RECEIVE) and delivered to their final destination (DELIVER), including the impact of any CORRECT transactions applied to either side.

Quantity Received and Quantity Delivered show the original transaction quantities, Quantity Corrected Receipt and Quantity Corrected Delivery the net correction impact, and Total Quantity Received and Total Quantity Delivered the corrected totals. Quantity Variance and Cost Variance compare the corrected totals, with amounts valued at the purchase price of each receiving transaction.

Return to Vendor and Return to Receiving quantities (net of their corrections) are shown for reference but are not netted into the variance columns.

Provide a Last Receipt Date range (required): the report shows purchase order lines that had a receipt within that range, and for each it nets ALL of the line’s receiving transactions to date, so the variance reflects the current outstanding received-vs-delivered position, not just activity in the window. Narrow the range (and/or add a PO, Supplier or Item filter) to keep large organizations fast.

By default the report shows discrepancy rows only (quantity or cost variance different from zero). Set parameter Discrepancies Only to No to list all purchase order receiving activity for the selected lines.

Run this report in Excel in seconds: download Blitz Report™ free (your first 30 reports are free) or try it in the online demo.

Report Parameters

Operating Unit, Organization Code, Supplier, PO Number, Item, Last Receipt Date From, Last Receipt Date To, Discrepancies Only

Oracle EBS Tables Used

po_line_locations_all, rcv_transactions, rcv_shipment_headers, po_headers_all, po_releases_all, po_lines_all, ap_suppliers, ap_supplier_sites_all, mtl_system_items_vl, mtl_parameters, hr_operating_units, org_access_view, mo_glob_org_access_tmp, dual

Report Categories

Enginatics, R12 only

PO Headers and Lines, PO Receiving Transactions, GL Account Distribution Analysis, PO Purchase Requisitions with PO Details, AP Invoices and Lines

Running This SQL Without Blitz Report

The SQL source can be run in any SQL client. Some Oracle EBS SQL reports in this library use functions from the utility package xxen_util – install it first. Blitz Report™ adds parameters, Excel output with pivots, scheduling and security on top.

Download & Import Options

Resource Link
Excel Example Output Example
Blitz Report™ XML Import PO_Receipts_Deliveries_Discrepancy.xml
SQL Source PO Receipts-Deliveries Discrepancy.sql
Full Report on Enginatics www.enginatics.com/reports/po-receipts-deliveries-discrepancy/

Case Study & Technical Analysis: PO Receipts/Deliveries Discrepancy

Executive Summary

The PO Receipts/Deliveries Discrepancy report finds purchase order lines and releases where the quantity and cost received into receiving differ from the quantity and cost delivered to the final destination. It is written for receiving managers, inventory accountants and purchasing teams who must clear the in-transit and receiving balances that build up when goods are received but not delivered, or delivered without a matching receipt. ## Business Challenge

The Solution

Technical Architecture (High Level)

Primary Tables

Logical Relationships

  1. A distinct list of purchase order lines and organisations with a RECEIVE transaction forms the driving set, and receiving transactions are joined to it on line and organisation.
  2. Each correction transaction is joined to its parent transaction through PARENT_TRANSACTION_ID, so the correction can be classed as a receipt or delivery correction.
  3. Receiving transactions join to RCV_SHIPMENT_HEADERS for the receipt number, and to PO_HEADERS_ALL, PO_RELEASES_ALL and PO_LINES_ALL for the purchase order.
  4. Transactions join to the supplier through the purchase order header and to the supplier site as an outer join.
  5. Items join to MTL_SYSTEM_ITEMS_VL on the item and organisation, as an outer join.
  6. The result is grouped to one row per operating unit, organisation, purchase order, release and line, and then wrapped in an outer query that returns the final columns.

Parameters & Filtering

Performance & Optimization

FAQ

Q: How do I find purchase order lines where received and delivered quantities do not match in Oracle Purchasing? A: Run PO Receipts/Deliveries Discrepancy with a last receipt date range and read the quantity variance and cost variance columns, which are non-zero for discrepancies.

Q: Why does the variance not include returns to vendor? A: Returns to vendor and returns to receiving are shown for reference, and the description states that they are not netted into the variance columns.

Q: Why are older receipts included when I enter a narrow date range? A: The date range selects the lines that had a receipt within it, but all receiving transactions of those lines are netted to date, so the variance is the current outstanding position.

© Enginatics GmbH