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

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. – | 1.2 23 Oct 2026 Andy Haack Rebuilt to the Blitz Report SQL standards. – +=============================================================================+*/

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

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

Oracle EBS Tables Used

mtl_parameters, hr_organization_information, hr_all_organization_units, hr_all_organization_units_vl, gl_ledgers, org_acct_periods, cst_period_close_summary, mtl_system_items_vl, mtl_item_status_vl, mtl_secondary_inventories, xxen_report_org_access, cst_item_costs, cst_cost_types

Report Categories

Enginatics

CAC Inventory and Intransit Value (Period-End) - Discrete/OPM, CAC ICP PII vs. Item Costs, CAC Inventory Pending Cost Adjustment - No Currencies, CAC Inventory Pending Cost Adjustment, CAC ICP PII Inventory Pending Cost Adjustment, CAC Inventory and Intransit Value (Period-End), CAC Inventory Out-of-Balance, CAC ICP PII Inventory and Intransit Value (Period-End), CAC Inventory Lot and Locator OPM Value (Period-End)

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 Example
Blitz Report™ XML Import CAC_Cost_Type_Costs_Not_in_Period_Close_Inventory_Snapshot.xml
SQL Source CAC Cost Type Costs Not in Period Close Inventory Snapshot.sql
Full Report 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.

© Enginatics GmbH