CAC Cost Type Costs Not in Period Close Inventory Snapshot – Oracle EBS SQL Report

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

Overview

Report comparing the month-end items, balances and costs against any entered Cost Type, showing which item numbers in your month-end inventory which are not in your Cost Type. You automatically save off your month-end quantities and values when you close the inventory accounting period.

Parameters:

Period Name (Closed): the closed inventory accounting period you wish to report (mandatory). Cost Type: enter a Cost Type to compare against the stored month-end items, quantities and values (mandatory). Item Number: enter the specific item number(s) you wish to report (optional). Organization Code: any inventory organization, defaults to your session’s inventory organization (optional). Operating Unit: specific operating unit (optional) Ledger: specific ledger (optional)

/* +=============================================================================+ – | Copyright 2024 - 2025 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 15 Jan 2024 Douglas Volz Initial Coding – | 1.1 04 Mar 2025 Douglas Volz Removed tabs, add ledger and operating unit – | columns and security access profiles. – +=============================================================================+*/

Report Parameters

Period Name (Closed), Cost Type, Item Number, Organization Code, Operating Unit, Ledger

Oracle EBS Tables Used

mtl_parameters, mtl_units_of_measure_vl, mtl_item_status_vl, fnd_common_lookups, mfg_lookups, mtl_secondary_inventories, hr_organization_information, hr_all_organization_units_vl, gl_ledgers, select, inventory_item_id, concatenated_segments, regexp_replace, primary_uom_code, inventory_item_status_code, item_type, inventory_asset_flag, period_name, acct_period_id, nvl, sum, cst_period_close_summary, org_acct_periods, subinventory_code, cst_item_costs, cst_cost_types, gl_access_set_norm_assign, gl_ledger_set_norm_assign_v, mo_glob_org_access_tmp, dual

Report Categories

Enginatics

CAC Missing Material Accounting Transactions, CAC Inventory Pending Cost Adjustment, CAC Onhand Lot Value (Real-Time), CAC ICP PII Inventory Pending Cost Adjustment, CAC Inventory Lot and Locator OPM Value (Period-End), CAC Inventory and Intransit Value (Period-End), CAC Inventory Out-of-Balance, CAC ICP PII Inventory and Intransit Value (Period-End), CAC Inventory and Intransit Value (Period-End) - Discrete/OPM

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 None
Blitz Report™ XML Import CAC_Cost_Type_Costs_Not_in_Period_Close_Inventory_Snapshot.xml
Full SQL on Enginatics www.enginatics.com/reports/cac-cost-type-costs-not-in-period-close-inventory-snapshot/

Case Study & Technical Analysis: CAC Cost Type Costs Not in Period Close Inventory Snapshot

Executive Summary

The CAC Cost Type Costs Not in Period Close Inventory Snapshot report is a reconciliation and audit tool designed to identify discrepancies between the official period-end inventory records and a target Cost Type (such as “Frozen” or “Pending”). Specifically, it finds items that held inventory balances at the time of period close but are completely missing a cost definition in the specified Cost Type. This is critical for ensuring that all inventory is properly valued and that there are no “uncosted” items lurking in the system which could lead to zero-value transactions or margin errors.

Business Challenge

In Oracle EBS, inventory value at period end is captured in a snapshot table (CST_PERIOD_CLOSE_SUMMARY). However, organizations often maintain multiple Cost Types (e.g., Frozen for standard costing, Pending for future updates, or simulation types). A common issue arises when:

The Solution

This report solves these problems by:

Technical Architecture (High Level)

The report employs a “Set Difference” logic using a NOT EXISTS clause to identify the gaps.

Parameters & Filtering

Performance & Optimization

FAQ

Q: Why does this report require a closed period? A: The table CST_PERIOD_CLOSE_SUMMARY is only populated by the “Period Close” process. If the period is open, this table may not contain up-to-date data for that period.

Q: What does “Rollback Value” mean? A: In the context of the period close summary, “Rollback” refers to the quantity and value calculated back to the period end date. However, since this table is the snapshot, it represents the static value at that point in time.

Q: Does this report show items with Zero Cost? A: No, it shows items with No Cost Record. An item with a cost record of $0.00 is technically “costed” (at zero). This report finds items that are completely missing from the CST_ITEM_COSTS table for the selected Cost Type.

Q: Can I use this for Average Costing? A: Yes, but in Average Costing, the “Cost Type” concept is less fluid than in Standard Costing. You would typically compare against the “Average” cost type to ensure integrity, though the system usually enforces cost creation automatically in Average Costing. This is most useful for Standard Costing environments.

© 2026 Enginatics