| layout | default |
|---|---|
| title | CAC Deferred COGS Out-of-Balance | Oracle EBS SQL Report |
| description | 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… |
| keywords | Oracle EBS, Oracle E-Business Suite, SQL report, Blitz Report, Enginatics, R12 only, CAC, Deferred, COGS, Out-of-Balance, gl_code_combinations, mfg_lookups, fnd_common_lookups |
| permalink | /CAC%20Deferred%20COGS%20Out-of-Balance/ |
Oracle E-Business Suite SQL report from the Enginatics Library powered by Blitz Report™.
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 |
| -- +=============================================================================+ |
|---|
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- +=============================================================================+*/ |
Transaction Date From, Transaction Date To, Category Set 1, Category Set 2, Category Set 3, Organization Code, Operating Unit, Ledger
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
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
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.
| 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/ |
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).
Managing the DCOGS account is complex due to the timing differences between shipment and revenue recognition. Common challenges include:
- Revenue Recognition Delays: Shipments made in one period but not invoiced/recognized until later, leaving balances in DCOGS.
- Data Integrity Issues: "Stuck" DCOGS balances where revenue was recognized but the Cost Processor failed to generate the offsetting credit to DCOGS.
- RMA Mismatches: Returns (RMAs) that credit DCOGS but don't have a corresponding original shipment debit in the same period/context.
- Period Close Reconciliation: Finance teams need to substantiate the balance in the DCOGS GL account at month-end; this report provides the detailed sub-ledger breakdown to match the GL balance.
The report provides a granular view of the DCOGS account by:
- Sales Order & Item Level Detail: It doesn't just show a total; it breaks down the balance by specific Sales Order and Item, allowing for precise troubleshooting.
- Netting Logic: It sums all debits (Shipments) and credits (COGS Recognition, RMAs) for the DCOGS line type (36). If the sum is zero, the transaction is considered "closed" and excluded. If non-zero, it appears on the report.
- Pre-Create Accounting: It queries the
MTL_TRANSACTION_ACCOUNTStable directly, meaning it reflects the inventory subledger view before the General Ledger transfer, allowing for faster operational analysis without waiting for the "Create Accounting" process.
The report aggregates accounting lines from the inventory subledger to calculate the net position of the DCOGS account.
- Core Table:
MTL_TRANSACTION_ACCOUNTS(MTA) is the primary source, filtered forACCOUNTING_LINE_TYPE = 36(Deferred COGS). - Transaction Sources: It focuses on
Sales Order(Source Type 2) andRMA(Source Type 12) transactions. - Aggregation: The query groups data by Ledger, Operating Unit, Organization, Period, Item, and Sales Order.
- Filtering: The
HAVINGclauseSUM(AMOUNT) <> 0ensures that only orders with a remaining DCOGS balance are displayed. Fully recognized orders (where Shipment Debit = Recognition Credit) are automatically filtered out.
- Transaction Date From/To: Defines the period of analysis.
- Category Sets: Allows filtering by specific product lines or inventory categories.
- Organization Code: Filter by specific inventory organization.
- Operating Unit/Ledger: Supports multi-org reporting.
- Inline View Strategy: The report uses an inline view (
mtl_acct) to perform the heavy lifting of joiningMTL_MATERIAL_TRANSACTIONSandMTL_TRANSACTION_ACCOUNTSand resolving the polymorphicTRANSACTION_SOURCE_ID(which can point to PO headers, OE headers, etc.) before aggregating. - Materialized View Avoidance: It accesses base tables directly rather than relying on potentially stale or slow views like
ORG_ORGANIZATION_DEFINITIONS. - Indexed Filtering: Filters on
ACCOUNTING_LINE_TYPEandTRANSACTION_SOURCE_TYPE_IDleverage standard Oracle indexes to quickly isolate the relevant DCOGS rows.
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.
- Blitz Report™ – World’s Fastest Oracle EBS Reporting Tool
- Oracle Discoverer Replacement – Import Worksheets into Blitz Report™
- Oracle EBS Reporting Toolkits by Blitz Report™
- Blitz Report™ FAQ & Community Q&A
- Supply Chain Hub by Blitz Report™
- Blitz Report™ Customer Case Studies
- Oracle EBS Reporting Blog
- Oracle EBS Reporting Resource Centre
© 2026 Enginatics