CAC PO Receipt History for Actual 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 non-Standard Cost inventory organizations, by PO receipt date range. You may also use this report for Standard Cost organizations but the CAC PO Receipt History for Item Costing report may be a better choice, as the following columns will have zero or empty values for Standard Cost organizations: prior costed quantity, prior cost and new onhand quantity.

/* +=============================================================================+ – | Copyright 2006 - 2022 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. | – +=============================================================================+ – | – | Original Author: Douglas Volz (doug@volzconsulting.com) – | – | Program Name: xxx_item_cost_history.sql – | – | Parameters: – | p_org_code – Specific inventory organization you wish to report (optional) – | p_trx_date_from – Starting transaction date for the PO Receipt History – | p_trx_date_to – Ending transaction date for the PO Receipt History – | p_item_number – Enter a specific item number to review. If you – | enter a blank or null value you get all items. – | p_category_set1 – The first item category set to report, typically the – | Cost or Product Line Category Set – | p_category_set2 – The second item category set to report, typically the – | Inventory Category Set – | p_operating_unit – Operating Unit you wish to report, leave blank for all – | operating units (optional) – | p_ledger – general ledger you wish to report, leave blank for all – | ledgers (optional) – | – | Description: – | Report to show PO Receipt History for non-standard cost inventory organizations, – | by PO receipt date range. – | – | 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 Non-Standard Costing. – +=============================================================================+*/

Report Parameters

Transaction Date From, Transaction Date To, Category Set 1, Category Set 2, Category Set 3, Organization Code, Item Number, Ledger

Oracle EBS Tables Used

mtl_material_transactions, mtl_cst_actual_cost_details, rcv_transactions, 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, org_access_view, gl_access_set_norm_assign, gl_ledger_set_norm_assign_v

Report Categories

Enginatics

CAC PO Receipt History for Item Costing, CAC Manufacturing Variance, CAC Calculate Average Item Costs, CAC Interface Error Summary, CAC Receiving Value (Period-End), CAC Purchase Price Variance, CAC WIP Resource Efficiency, CAC Invoice Price Variance, PO Purchase Price Variance

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 Actual Costing 13-Dec-2022 011635.xlsx
Blitz Report™ XML Import CAC_PO_Receipt_History_for_Actual_Costing.xml
Full SQL on Enginatics www.enginatics.com/reports/cac-po-receipt-history-for-actual-costing/

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

Executive Summary

The CAC PO Receipt History for Actual Costing report is essential for organizations using Average, FIFO, or LIFO costing. In these methods, the “Item Cost” is dynamic—it updates with every receipt. This report provides the audit trail explaining why the cost changed, by showing the receipt quantity and price that drove the update.

Business Challenge

Solution

This report reconstructs the cost update logic.

Technical Architecture

Parameters

Performance

FAQ

Q: Can I use this for Standard Costing? A: You can, but the “Prior Cost” and “New Cost” columns might not be relevant as Standard Cost doesn’t change on receipt. Use the “Item Costing” version instead.

Q: Does it include freight? A: If “Landed Cost Management” (LCM) is used, the receipt price will include estimated landed costs.

© 2026 Enginatics