CAC Recost Cost of Goods Sold

Description

Categories
Enginatics, R12 only
Repository
GitHub
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 ([email protected])
-- |
-- | 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.
-- +=============================================================================+*/

SQL 184 lines

Parameters 12

ParameterTypeList of values SQL
Transaction Date FromDate
mta.transaction_date>=:p_trx_date_from and mmt.transaction_date>=:p_trx_date_from
Transaction Date ToDate
mta.transaction_date<:p_trx_date_to+1 and mmt.transaction_date<:p_trx_date_to+1
COGS Transaction TypesLOV
mtt.transaction_type_name=:p_transaction_type
Recost Cost TypeLOV
cct.cost_type=:p_cost_type
Minimum Value DifferenceNumber
Category Set 1LOV
select xxen_util.item_category_columns(p_category_set_name=>'<parameter_value>') sql_text from dual
Category Set 2LOV
select xxen_util.item_category_columns(p_category_set_name=>'<parameter_value>') sql_text from dual
Category Set 3LOV
select xxen_util.item_category_columns(p_category_set_name=>'<parameter_value>') sql_text from dual
Item NumberLOV
mmt.inventory_item_id=(select msiv.inventory_item_id from mtl_system_items_vl msiv where mta.organization_id=msiv.organization_id and msiv.concatenated_segments=:p_item_number)
Organization CodeLOV
mp.organization_code=:p_org_code
Operating UnitLOV
haouv_ou.name=:p_operating_unit
LedgerLOV
gl.name=:p_ledger