CAC Deferred COGS Out-of-Balance – Oracle EBS SQL Report

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

Overview

Report to find the out-of-balance deferred COGS entries by organization, item and sales order number. You do not need to run Create Accounting as this report uses the pre-Create Accounting material accounting entries.

/* +=============================================================================+ – | Copyright 2019 - 2020 Douglas Volz Consulting, Inc. | – | All rights reserved. | – | Permission to use this code is granted provided the original author is | – | acknowledged | – +=============================================================================+ – | – | Original Author: Douglas Volz (doug@volzconsulting.com) – | – | Program Name: xxx_mtl_dist_xla_oob_dcogs_rept.sql – | – | Parameters: – | p_trx_date_from – Starting accounting date for the transaction lines – | p_trx_date_to – Ending accounting date for the transaction lines – | p_category_set1 – The first item category set to report, typically the – | Cost or Product Line Category Set – | p_category_set2 – The second item category set to report, typically the – | Inventory Category Set – | p_org_code – Specific inventory organization you wish to report (optional) – | p_operating_unit – Operating Unit you wish to report, leave blank for all – | operating units (optional) – | p_ledger – general ledger you wish to report, leave blank for all – | ledgers (optional) – | – | Description: – | Report to find the out-of-balance deferred COGS entries.
– | – | Version Modified on Modified by Description – | ======= =========== ============== ========================================= – | 1.0 07 Jun 2019 Douglas Volz Initial Coding, based on version 1.25 for – | the xxx_mtl_dist_xla_detail_rept.sql. – | 1.1 06 Feb 2020 Douglas Volz Adding Operating Unit and Org Code parameters. – | 1.2 20 Apr 2020 Douglas Volz Changed to multi-lang views for the item – | master, item category sets and operating units.
– | 1.3 26 Jul 2020 Douglas Volz Removed Ledger, Operating Unit, subinventory, – | Item Type, Subledger Accounting tables and joins. – | Removed Create Accounting from this report; – | get the quantities from mmt, when the item – | cost is zero the DCOGS entries are not – | recorded on the COGS Recognition Txn Type. – | 1.4 29 Jun 2022 Douglas Volz Added back Ledger, Operating Unit, item type, plus – | added language tables for item status and UOM.
– +=============================================================================+*/

Report Parameters

Transaction Date From, Transaction Date To, Category Set 1, Category Set 2, Category Set 3, Organization Code, Operating Unit, Ledger

Oracle EBS Tables Used

gl_code_combinations, mfg_lookups, fnd_common_lookups, hr_organization_information, hr_all_organization_units_vl, gl_ledgers, org_acct_periods, select, organization_id, acct_period_id, reference_account, inv_sub_ledger_id, concatenated_segments, description, inventory_item_status_code, planning_make_buy_code, item_type, inventory_item_id, accounting_line_type, transaction_type_name, transaction_source_type_name, Decode, nvl, transaction_id, parent_transaction_id, decode, uom_code, -, base_transaction_value, mtl_material_transactions, mtl_transaction_types, mtl_system_items_vl, mtl_item_status_vl, mtl_units_of_measure_vl, mtl_txn_source_types, mtl_parameters, gl_access_set_norm_assign, gl_ledger_set_norm_assign_v, mo_glob_org_access_tmp, dual

Report Categories

Enginatics, R12 only

CAC Inventory and Intransit Value (Period-End) - Discrete/OPM, CAC ICP PII WIP Material Usage Variance, CAC Manufacturing Variance, CAC Inventory Lot and Locator OPM Value (Period-End), CAC WIP Account Summary, CAC Material Account Summary, CAC Inventory and Intransit Value (Period-End), CAC Inventory Out-of-Balance, CAC ICP PII Inventory and Intransit Value (Period-End), CAC Material Account Alias with Lot Numbers

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 Deferred COGS Out-of-Balance 23-Jun-2022 135858.xlsx
Blitz Report™ XML Import CAC_Deferred_COGS_Out_of_Balance.xml
Full SQL on Enginatics www.enginatics.com/reports/cac-deferred-cogs-out-of-balance/

Case Study & Technical Analysis: CAC Deferred COGS Out-of-Balance

Executive Summary

The CAC Deferred COGS Out-of-Balance report is a reconciliation tool used to analyze the “Deferred Cost of Goods Sold” (DCOGS) account. In Oracle EBS, when a Sales Order is shipped, the cost is typically debited to a DCOGS account rather than the final COGS account. The balance is only moved to COGS when the associated revenue is recognized (matching principle). This report identifies Sales Orders and Items where the DCOGS account has a non-zero balance, effectively highlighting shipments for which revenue has not yet been fully recognized (or where the accounting flow is incomplete).

Business Challenge

Managing the DCOGS account is complex due to the timing differences between shipment and revenue recognition. Common challenges include:

The Solution

The report provides a granular view of the DCOGS account by:

Technical Architecture (High Level)

The report aggregates accounting lines from the inventory subledger to calculate the net position of the DCOGS account.

Parameters & Filtering

Performance & Optimization

FAQ

Q: Why is this report called “Out-of-Balance”? A: In this context, “Out-of-Balance” refers to any Sales Order line that has a remaining balance in the DCOGS account. While a balance is normal for recently shipped items (waiting for revenue), old balances often indicate errors or “stuck” transactions that need investigation.

Q: Does this report match the General Ledger? A: Ideally, yes. The sum of the “Net Deferred COGS Amount” column for a given period should tie to the ending balance of the DCOGS GL account (assuming all journals have been posted).

Q: Why do I see negative balances? A: A negative balance might occur if an RMA (Return) was processed and credited to DCOGS, but the original shipment happened in a prior period or was already fully recognized. It could also indicate a COGS Recognition transaction occurred without a corresponding Shipment (rare, but possible in data corruption scenarios).

Q: Do I need to run “Create Accounting” first? A: No. This report looks at MTL_TRANSACTION_ACCOUNTS, which is populated by the Cost Processor. It reflects the inventory subledger reality immediately after the Cost Manager runs, regardless of whether the GL transfer has happened.

© 2026 Enginatics