CAC PO Receipt History for Item Costing – Oracle EBS SQL Report

Oracle E-Business Suite SQL report from the Enginatics Library powered by Blitz Report™.

Overview

Report to show Purchase Order (PO) Receipt History for inventory organizations, for a selected PO receipt date range. If the Comparison Cost Type is not entered the report uses the Cost Type from the Primary Costing Method, such as Frozen, Average, FIFO or LIFO. And note you may use this report for any discrete costing method but the CAC PO Receipt History for Actual Costing report may be a better choice for Average, FIFO and LIFO Costing, as additional information is available, such as the prior costed quantity, prior cost and new onhand quantity.

Parameters:

Transaction Date From: enter the starting transaction date for PO Receipt History (mandatory). Transaction Date To: enter the ending transaction date for PO Receipt History (mandatory). Comparison Cost Type: enter the cost type to compare against the PO receipts (optional). If the Comparison Cost Type is not entered the report uses Cost Type from the Primary Costing Method. Category Set 1: any item category you wish, typically the Cost or Product Line category set (optional). Category Set 2: any item category you wish, typically the Inventory category set (optional). Item Number: enter the specific item number(s) you wish to report (optional). Organization Code: enter the specific inventory organization(s) you wish to report (optional). Operating Unit: enter the specific operating unit(s) you wish to report (optional). Ledger: enter the specific ledger(s) you wish to report (optional).

/* +=============================================================================+ – | Copyright 2006 - 2023 Douglas Volz Consulting, Inc. – | All rights reserved. – | Permission to use this code is granted provided the original author is – | acknowledged. No warranties, express or otherwise is included in this permission.
– +=============================================================================+ – | – | Version Modified on Modified by Description – | ======= =========== ============== ========================================= – | 1.0 28 May 2006 Douglas Volz Initial Coding based on item_cost_history.sql – | 1.1 04 Jan 2019 Douglas Volz Added transaction date range, inventory – | org and specific item parameters. – | 1.2 30 Aug 2019 Douglas Volz Add Ledger, Operating Unit, Item Type, Status – | and item categories for cost and inventory. – | 1.3 27 Jan 2020 Douglas Volz Added Org_Code and Operating_Unit parameters. – | 1.4 05 Jul 2022 Douglas Volz Modify for multi-language tables, change UOM to – | primary, and changes for Standard Costing. – | 1.5 01 Sep 2022 Douglas Volz Add supplier information to report. – | 1.6 13 Dec 2022 Douglas Volz Fix supplier type and cost type logic. – | 1.7 11 Sep 2023 Douglas Volz Added Transaction Type to report. – +=============================================================================+*/

Report Parameters

Transaction Date From, Transaction Date To, Comparison Cost Type, Category Set 1, Category Set 2, Category Set 3, Item Number, Organization Code, Operating Unit, Ledger

Oracle EBS Tables Used

mtl_material_transactions, mtl_transaction_types, cst_item_costs, cst_cost_types, rcv_transactions, po_vendors, fnd_lookup_values_vl, mtl_system_items_vl, mtl_item_status_vl, mtl_units_of_measure_vl, mfg_lookups, po_headers_all, po_lines_all, po_line_locations_all, po_releases_all, hr_employees, mtl_parameters, fnd_common_lookups, hr_organization_information, hr_all_organization_units_vl, gl_ledgers, gl_access_set_norm_assign, gl_ledger_set_norm_assign_v, mo_glob_org_access_tmp, dual, org_access_view

Report Categories

Enginatics

CAC Calculate Average Item Costs, CAC Purchase Price Variance, CAC Receiving Value (Period-End), PO Headers and Lines 11i, GL Account Analysis, MRP Pegging, GL Account Analysis (Distributions), GL Account Distribution Analysis, PO Headers and Lines

Running This SQL Without Blitz Report

Some Oracle EBS SQL reports in this library require functions from the utility package xxen_util. Install it before running the SQL directly against your Oracle EBS database.

Download & Import Options

Resource Link
Excel Example Output CAC PO Receipt History for Item Costing 13-Dec-2022 104628.xlsx
Blitz Report™ XML Import CAC_PO_Receipt_History_for_Item_Costing.xml
Full SQL on Enginatics www.enginatics.com/reports/cac-po-receipt-history-for-item-costing/

Case Study & Technical Analysis: CAC PO Receipt History for Item Costing

Executive Summary

The CAC PO Receipt History for Item Costing report is a general-purpose purchasing analysis tool. While the “Actual Costing” version focuses on cost updates, this version focuses on the purchasing aspect: Supplier performance, price trends, and comparison against a fixed Standard Cost.

Business Challenge

Solution

This report provides a clean list of receipts.

Technical Architecture

Parameters

Performance

FAQ

Q: What is the difference between this and the “Actual Costing” report? A: This report does not show the “Prior Cost” or “New Onhand” calculation. It is simpler and focused on the receipt transaction itself vs. the cost update impact.

Q: Does it show returns? A: Yes, “Return to Vendor” (RTV) transactions are typically included (often with negative quantities) to show net purchasing.

© 2026 Enginatics