CAC Recost Cost of Goods Sold – 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 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. – | 1.7 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

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

oe_order_lines_all, oe_order_headers_all, mtl_material_transactions, po_headers_all, wip_entities, mtl_generic_dispositions, po_requisition_headers_all, mtl_cycle_count_headers, mtl_physical_inventories, cst_cost_updates, oe_transaction_types_tl, mtl_parameters, mtl_transaction_accounts, mtl_transaction_types, xxen_report_org_access, xla_distribution_links, xla_ae_lines, xla_ae_headers, cst_item_costs, cst_cost_types, hr_organization_information, hr_all_organization_units, hr_all_organization_units_vl, gl_ledgers, mtl_system_items_vl, mtl_item_status_vl, mtl_txn_source_types, gl_code_combinations

Report Categories

Enginatics, R12 only

GL Account Analysis, INV Material Account Distribution Detail, GL Account Analysis (Drilldown) (with inventory and WIP), GL Account Distribution Analysis, INV Transaction Register, INV Lot Transaction Register, INV Material Transactions

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_Recost_Cost_of_Goods_Sold.xml
SQL Source CAC Recost Cost of Goods Sold.sql
Full Report 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.

© Enginatics GmbH