CAC Recost Cost of Goods Sold – Oracle EBS SQL Report

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

Overview

Report to compare the “as transacted” cost of goods sold (COGS) transactions in Oracle Inventory with the “what if” costs from a simulation cost type. Use this report when you need to make manual journal entry corrections to your COGS entries, due to incorrect item costs. In addition to reporting the normal COGS entries for the COGS Recognition, Sales Order Issue, RMA Receipt and RMA Return transactions, you can also report user-defined material transactions such as Account Issues/Receipts, Miscellaneous Issues/Receipts and Account Alias transactions which could represent COGS entries from other ERP Systems. To report additional user-defined transactions enter these along with the defaulted values for the COGS Transaction Type parameter: COGS Recognition, Sales Order Issue, RMA Receipt and RMA Return.

Note: in order for COGS entries to be reported you must first run Create Accounting.

Parameters:

Transaction Date From: enter the starting transaction date for COGS transactions (mandatory). Transaction Date To: enter the ending transaction date for PO COGS transactions (mandatory). COGS Transaction Types: enter the COGS related transaction types you wish to report (mandatory). Recost Cost Type: enter the cost type you wish to use to recost your COGS transactions (mandatory). Minimum Value Difference: the absolute smallest COGS difference you want to report (mandatory). Category Set 1: any item category you wish, typically the Cost or Product Line category set (optional). Category Set 2: any item category you wish, typically the Inventory category set (optional). Item Number: enter the specific item number(s) you wish to report (optional). Organization Code: enter the specific inventory organization(s) you wish to report (optional). Operating Unit: enter the specific operating unit(s) you wish to report (optional). Ledger: enter the specific ledger(s) you wish to report (optional).

/* +=============================================================================+ – | Copyright 2006- 2023 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) – | – | Program Name: xxx_recost_cogs_detail_rept.sql – | – | Version Modified on Modified by Description – | ======= =========== ============== ========================================= – | 1.0 02 Apr 2006 Douglas Volz Initial Coding – | 1.1 28 Apr 2006 Douglas Volz Final Coding – | 1.2 02 May 2017 Douglas Volz Modified for Client’s use, added inline table – | to get one row for COGS accounting entries, to – | to get the mmt correct qty and avoid cross-joining. – | 1.3 13 Nov 2023 Douglas Volz Modified for organization access, removed tabs and – | added subledger accounting ccids. – | 1.4 16 Nov 2023 Douglas Volz Remove org_acct_periods, use period name from xla_ae_headers. – | 1.5 05 Dec 2023 Douglas Volz Added G/L and Operating Unit security restrictions. – | 1.6 12 Mar 2024 Douglas Volz Add cost elements for COGS per client request. – +=============================================================================+*/

Report Parameters

Transaction Date From, Transaction Date To, COGS Transaction Types, Recost Cost Type, Minimum Value Difference, Category Set 1, Category Set 2, Category Set 3, Item Number, Organization Code, Operating Unit, Ledger

Oracle EBS Tables Used

mtl_system_items_vl, mtl_txn_source_types, cst_item_costs, cst_cost_types, mtl_item_status_vl, mtl_units_of_measure_vl, mfg_lookups, fnd_common_lookups, po_headers_all, oe_order_headers_all, oe_order_lines_all, wip_entities, mtl_generic_dispositions, po_requisition_headers_all, mtl_cycle_count_headers, mtl_physical_inventories, cst_cost_updates, mtl_material_transactions, oe_transaction_types_tl, mtl_transaction_accounts, mtl_transaction_types, mtl_parameters, org_access_view, gl_code_combinations, hr_organization_information, hr_all_organization_units, gl_ledgers, xla_distribution_links, xla_ae_headers, xla_ae_lines, gl_access_set_norm_assign, gl_ledger_set_norm_assign_v, mo_glob_org_access_tmp, dual

Report Categories

Enginatics

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_Recost_Cost_of_Goods_Sold.xml
Full SQL on Enginatics www.enginatics.com/reports/cac-recost-cost-of-goods-sold/

Case Study & Technical Analysis: CAC Recost Cost of Goods Sold

Executive Summary

The CAC Recost Cost of Goods Sold report is a powerful financial simulation tool. It allows Cost Accountants to answer “What If” questions regarding margins. Specifically, it compares the COGS recorded in the General Ledger (based on the active Standard Cost) against a theoretical COGS calculated using a different Cost Type (e.g., “Proposed 2024 Standard”).

Business Challenge

Solution

This report performs a retrospective valuation.

Technical Architecture

Parameters

Performance

FAQ

Q: Does this update the GL? A: No, it is a reporting tool only. Any adjustments must be booked via manual journal entries. Q: Can it handle RMA? A: Yes, Return Material Authorizations (Credits) are included to give a net COGS impact.

© 2026 Enginatics