CAC Open Purchase Orders – 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 show open purchase orders and related information. This report will convert any foreign currency purchases into the currency of the general ledger (defaulted from the inventory organization for this session). Use the To and From Transaction Date parameters to create an average receipt cost and use the Comparison Cost Type parameter to show a comparison amounts from another cost type.

Parameters:

Comparison Cost Type: enter the cost type for a comparison against the purchase order prices (optional). Defaulted from your Costing Method. Transaction Date From: starting transaction date for averaging the purchase order receipts (mandatory). Use these averages for comparing to the PO unit prices. Transaction Date To: ending transaction date for averaging the purchase order receipts (mandatory). Use these averages for comparing to the PO unit prices. Currency Conversion Date: enter the currency conversion date to use for converting foreign currency purchases into the currency of the general ledger (mandatory). Currency Conversion Type: enter the currency conversion type to use for converting foreign currency purchases into the currency of hhe general ledger (mandatory). Supplier Name: specific vendor or supplier you wish to report (optional). 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 2020 - 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. – +=============================================================================+ – |
– | Original Author: Douglas Volz (doug@volzconsulting.com) – | – | Program Name: open_po_rept.sql – | – | Version Modified on Modified by Description – | ======= =========== ============== ========================================= – | 1.0 09 Sep 2020 Douglas Volz Initial Coding based on Purchase Price – | variance report, xxx_ppv_lot_rept.sql – | 1.1 10 Sep 2020 Douglas Volz Added inspection flag. – | 1.2 01 Dec 2020 Douglas Volz Added variance and charge accounts – | 1.3 20 Dec 2020 Douglas Volz Added promise date, Need By Date, project, – | Expected Receipt Date, Target Price (PO List Price), – | Customer Name and difference columns. – | And added Minimum Cost Difference parameter. – | 1.4 03 Feb 2021 Douglas Volz Merged OSP with stock purchase orders. – | 1.5 05 Feb 2021 Douglas Volz Added PO averages and item cost information. – | 1.6 07 Jul 2022 Douglas Volz Add multi-language item status. – | 1.7 28 Nov 2023 Andy Haack Remove tabs, add org access controls, fix for G/L Daily Rates, outer joins. – | 1.8 15 Dec 2023 Douglas Volz Setting the comparison cost type as mandatory, to – | prevent a multiple rows error for the comparison cost type.
– +=============================================================================+*/

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

Comparison Cost Type, Transaction Date From, Transaction Date To, Currency Conversion Date, Currency Conversion Type, Supplier Name, Category Set 1, Category Set 2, Category Set 3, Show SLA, Item Number, Organization Code, Operating Unit, Ledger

Oracle EBS Tables Used

mtl_supply, cst_cost_types, cst_item_costs, po_lines_all, po_line_locations_all, po_headers_all, org_organization_definitions, 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, po_distributions_all, po_releases_all, mtl_parameters, ap_suppliers, mtl_system_items_vl, mtl_item_status_vl, mtl_uom_conversions_view, mtl_units_of_measure_vl, wip_entities, pa_projects_all, hr_employees, gl_daily_rates, gl_code_combinations, bom_resources, cst_resource_costs, mtl_material_transactions

Report Categories

Enginatics

CAC Item List Price vs. Item Cost, CAC Calculate Average Item Costs, CAC Receiving Value (Period-End), CAC PO Receipt History for Item Costing, CAC Inventory to G/L Reconciliation (Restricted by Org Access), CAC PO Receipt History for Actual Costing, CAC Recost Cost of Goods Sold, CAC WIP Resource Efficiency, WIP Account Distribution

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 Open Purchase Orders 07-Jul-2022 170534.xlsx
Blitz Report™ XML Import CAC_Open_Purchase_Orders.xml
SQL Source CAC Open Purchase Orders.sql
Full Report on Enginatics www.enginatics.com/reports/cac-open-purchase-orders/

Case Study & Technical Analysis: CAC Open Purchase Orders

Executive Summary

The CAC Open Purchase Orders report is a forward-looking financial control tool. While most variance reports analyze what has happened (historical PPV), this report analyzes what is about to happen. It lists all open Purchase Orders (POs) and compares the negotiated PO price against the current Standard Cost of the item. This allows Finance and Procurement teams to:

  1. Forecast PPV: Predict the Purchase Price Variance that will hit the P&L when these goods are received.
  2. Audit Procurement Performance: Identify significant deviations from standard cost before the liability is incurred.
  3. Manage Currency Risk: Simulate the impact of exchange rate fluctuations on open foreign currency orders.

Business Challenge

In a standard costing environment, the difference between the PO Price and the Standard Cost is recorded as Purchase Price Variance (PPV) at the moment of receipt.

The Solution

This report acts as a “PPV Radar,” scanning the horizon of open orders.

Technical Architecture (High Level)

The query is built on the Purchasing (PO) schema but heavily enriched with Costing (CST) and Inventory (MTL) data.

Parameters & Filtering

Performance & Optimization

FAQ

Q: Why is the “Converted PO Unit Price” different from the price on the PO? A: If you entered a Currency Conversion Date for today, the report re-calculates the value of foreign POs using today’s exchange rate. This shows you the cost if you received it today, which may differ from the rate when the PO was created.

Q: Does this report show received items? A: No, it focuses on Open orders (where Closed Code is not ‘CLOSED’ or ‘FINALLY CLOSED’). For received items, use the “Purchase Price Variance” report.

Q: How does it handle OSP (Outside Processing)? A: For OSP items, the report looks at the “Resource” linked to the item. It compares the PO Price (service charge) against the Standard Rate of that Resource.

© Enginatics GmbH