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™. Oracle EBS module: Cost Accounting · all Cost Accounting reports

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”.

/* +=============================================================================+ – | 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. – | 1.24 23 Oct 2026 Andy Haack Rebuilt to the Blitz Report SQL standards. – | 1.25 24 Oct 2026 Andy Haack Price per primary unit of measure divided by the conversion rate. – +=============================================================================+*/

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

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

hr_employees, mtl_supply, gl_daily_rates, gl_daily_conversion_types, mtl_parameters, hr_organization_information, hr_all_organization_units_vl, gl_ledgers, po_line_locations_all, po_lines_all, po_headers_all, po_distributions_all, po_releases_all, ap_suppliers, pa_projects_all, mtl_system_items_vl, mtl_uom_conversions_view, mtl_units_of_measure_vl, cst_resource_costs, cst_cost_types, bom_resources, mtl_system_items_b, cst_item_costs, xxen_report_org_access

Report Categories

Enginatics

CAC Open Purchase Orders, CAC Purchase Price Variance, CAC Item List Price vs. Item Cost, CAC Calculate Average Item Costs, AP Invoices and Lines, CAC Internal Order Shipment Margin, GL Account Distribution Analysis, CAC Receiving Activity Summary, AP Invoice Upload

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 CAC PO Price vs. Costing Method Comparison 07-Jul-2022 173319.xlsx
Blitz Report™ XML Import CAC_PO_Price_vs_Costing_Method_Comparison.xml
SQL Source CAC PO Price vs- Costing Method Comparison.sql
Full Report 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.

© Enginatics GmbH