CAC Open Purchase Orders – Oracle EBS SQL Report

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

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.
– +=============================================================================+*/

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, &sla_tables, 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, select, inventory_item_id, sum

Report Categories

Enginatics, Nidec changes

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 Open Purchase Orders 07-Jul-2022 170534.xlsx
Blitz Report™ XML Import CAC_Open_Purchase_Orders.xml
Full SQL 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.

© 2026 Enginatics