CAC Deferred COGS Out-of-Balance
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 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.
– +=============================================================================+*/
Parameters
Transaction Date From, Transaction Date To, Category Set 1, Category Set 2, Category Set 3, Organization Code, Operating Unit, Ledger
Used tables
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
Categories
Related reports
CAC Inventory and Intransit Value (Period-End) - Discrete/OPM
Dependencies
If you would like to try one of these Oracle EBS SQLs without having Blitz Report installed, note that some of the reports require functions from utility package xxen_util.
Example Report
CAC Deferred COGS Out-of-Balance 23-Jun-2022 135858.xlsx
Report SQL
www.enginatics.com/reports/cac-deferred-cogs-out-of-balance/
Blitz Report™ import options
CAC_Deferred_COGS_Out_of_Balance.xml
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:
- 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 Solution
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.
Technical Architecture (High Level)
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.
Parameters & Filtering
- 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.
Performance & Optimization
- 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.
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.
Oracle E-Business Suite Reporting Library
We provide an open source Oracle EBS SQLs as a part of operational and project implementation support toolkits for rapid Excel reports generation.
Blitz Report™ is based on Oracle EBS forms technology, and hence requires minimal training. There are no data or performance limitations since the output files are created directly from the database without going through intermediate file formats such as XML.
Blitz Report can be used as BI Publisher and Oracle Discoverer replacement tool. Standard Oracle BI Publisher and Discoverer reports can also be imported into Blitz Report for immediate output to Excel. Typically, reports can be created and version tracked within hours instead of days. The concurrent request output automatically opens upon completion without the need for re-formatting.
The Filters, Columns, Rows and Values fields are used to create and deliver the data in pivot table format with full drill down to details.
The Excel template upload functionality in Blitz Report allows users to create their own layouts by uploading an Excel template with additional sheets and charts, automatically refreshed when the report runs again. This allows to create custom dashboards and more advanced visualizations of report data.
You can download and use Blitz Report free of charge for your first 30 reports.
The installation and implementation process usually takes less than 1 hour; you can refer to our installation and user guides for specific details.
If you would like to optimize your Oracle EBS implementation and or operational reporting you can visit www.enginatics.com to review great ideas and example usage in blog. Or why not try for yourself in our demo environment.
Useful Links
Blitz Report™ – World’s fastest data upload and reporting for Oracle EBS
Oracle Discoverer replacement – importing worksheets into Blitz Report™
Blitz Report™ Questions & Answers
Supply Chain Hub by Blitz Report™
© 2025 Enginatics