CAC PO Price vs. Costing Method Comparison – Oracle EBS SQL Report

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

Overview

Report to compare the open purchase order lines and unit prices with the costing method item cost in Oracle (Average, Standard, FIFO or LIFO). Used by the buyers and cost accounting to check the accuracy of the recently created purchase orders and run using a range of purchase order line creation dates. Foreign currency purchase orders convert to the inventory organization’s currency by either using the original purchase order exchange rate, if the Invoice Match Option is “Purchase Order” or by using the latest exchange rate date if the Invoice Match Option is “Receipt”.

Parameters:

Creation Date From: purchase order starting creation date (mandatory). Creation Date To: purchase order ending creation date (mandatory). Cost Type: enter the cost type you wish to report (mandatory). Defaults to your Costing Method. Minimum Value Difference: the absolute smallest difference you want to report (mandatory). Minimum Cost Difference: the absolute smallest difference you want to report (mandatory). Currency Conversion Type: enter the currency conversion type for translating PO unit prices, used if the Invoice Match Option is “Receipt”, defaults to Corporate (mandatory). Currency Conversion Date: enter the currency conversion date for translating PO unit prices, used if the Invoice Match Option is “Receipt” (mandatory). Category Set 1: the first item category set to report, typically the Cost or Product Line Category Set (optional). Category Set 2: the second item category set to report, typically the Inventory Category Set (optional). Item Number: enter the item numbers 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-2024 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 – | – | Version Modified on Modified by Description – | ======= =========== ============== ========================================= – | 1.0 02 APR 2006 Douglas Volz Initial Coding – | 1.17 13 Jun 2017 Douglas Volz Added OSP Resource Code – | 1.18 19 Aug 2019 Douglas Volz Removed non-generic item categories – | 1.19 27 Jan 2020 Douglas Volz Changed to multi-language views for the item – | master, inventory orgs and operating units. – | Added project number to report. – | 1.20 19 Dec 2020 Douglas Volz Add these columns: PO Need By Date, PO Promise Date, – | PO Expected Receipt Date, Target Price (PO List Price), – | Customer Name (description for category set 1).
– | And added Minimum Cost Difference parameter. – | 1.21 22 Dec 2020 Douglas Volz Changed the item cost “union all” to just “union”, – | which eliminated a full table scan on cst_item_costs. – | 1.22 02 Nov 2023 Douglas Volz Add Cost Type as a parameter, remove tabs and – | add org access controls. – | 1.23 18 Jul 2024 Douglas Volz Fix for Percent Difference calculation. – +=============================================================================+*/

Report Parameters

Creation Date From, Creation Date To, Cost Type, Minimum Value Difference, Minimum Cost Difference, Currency Conversion Type, Currency Conversion Date, Category Set 1, Category Set 2, Category Set 3, Item Number, Organization Code, Operating Unit, Ledger

Oracle EBS Tables Used

mtl_supply, po_headers_all, po_lines_all, po_line_locations_all, po_distributions_all, po_releases_all, po_vendors, pa_projects_all, mtl_system_items_vl, mtl_uom_conversions_view, mtl_units_of_measure_vl, mtl_item_status_vl, mfg_lookups, fnd_common_lookups, cst_resource_costs, cst_cost_types, bom_resources, mtl_parameters, org_access_view, cst_item_costs, hr_employees, hr_locations, hr_organization_information, hr_all_organization_units_vl, gl_ledgers, gl_daily_rates, gl_daily_conversion_types

Report Categories

Enginatics

CAC Calculate ICP PII Item Costs by Where Used, CAC Manufacturing Variance, CAC WIP Pending Cost Adjustment, INV Items, CAC ICP PII WIP Pending Cost Adjustment, INV Item Upload, CAC WIP Material Usage Variance, CAC ICP PII WIP Material Usage Variance, BOM Bill of Materials Upload

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 Price vs. Costing Method Comparison 07-Jul-2022 173319.xlsx
Blitz Report™ XML Import CAC_PO_Price_vs_Costing_Method_Comparison.xml
Full SQL on Enginatics www.enginatics.com/reports/cac-po-price-vs-costing-method-comparison/

Case Study & Technical Analysis: CAC PO Price vs. Costing Method Comparison

Executive Summary

The CAC PO Price vs. Costing Method Comparison report is a proactive audit tool. It compares the Unit Price on Open Purchase Orders against the current Item Cost in the system. This allows organizations to catch significant pricing errors or forecast large variances before the goods are received.

Business Challenge

Solution

This report highlights the discrepancies.

Technical Architecture

Parameters

Performance

FAQ

Q: Does this affect the GL? A: No, this is a reporting tool for Open POs. No accounting has happened yet. It is for forecasting and data cleansing.

Q: What if the item has no cost? A: It will show a 100% variance, highlighting that a New Item Cost needs to be set up before receipt.

© 2026 Enginatics