CAC Material Account Summary No SLA

Description
Categories: Cost Accounting - Assessment, Cost Accounting - Transactions, Enginatics, Toolkit - Cost Accounting
Repository: Github
Report to get the material accounting entries for each item, organization, subinventory with amounts. Including Ship From and Ship To information for inter-org transfers. This report version does not use the Release 12 Subledger Accounting (Create Accounting) tables. And includes all material transactions but to keep the report smaller it does not display WIP job information. Use the Show Subi ...  Report to get the material accounting entries for each item, organization, subinventory with amounts. Including Ship From and Ship To information for inter-org transfers. This report version does not use the Release 12 Subledger Accounting (Create Accounting) tables. And includes all material transactions but to keep the report smaller it does not display WIP job information. Use the Show Subinventory parameter to further reduce the size of this report, as needed. If you choose Yes you get the Subinventory Code, if you choose No you only get the Accounting Line Type for inventory, (Inventory valuation) thus greatly reducing the size of this report. Also note this report version shows the latest item status and planning make buy codes, even if you run the report for prior accounting periods.

/* +=============================================================================+
-- | Copyright 2009-22 Douglas Volz Consulting, Inc. |
-- | 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_mtl_dist_xla_sum_rept.sql
-- |
-- | Parameters:
-- | p_trx_date_from -- starting transaction date for PII related transactions, mandatory
-- | p_trx_date_to -- ending transaction date for PII related transactions, mandatory
-- | 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)
-- | p_show_subinv -- display the subinventory code or don't display the subinventory code.
-- | Enter a 'Y' or 'N' value. Mandatory. Use to limit the report size.
-- |
-- | Description:
-- | Report to get the material accounting entries for each item, organization and subinventory
-- | with amounts. Including Ship From and Ship To information for inter-org
-- | transfers.
-- |
-- | Version Modified on Modified by Description
-- | ======= =========== ============== =========================================
-- | 1.0 06 Nov 2009 Douglas Volz Initial Coding
-- | 1.21 14 May 2020 Douglas Volz Use multi-language table for UOM_Code, mtl_units_of_measure_vl
-- | 1.22 17 May 2020 Douglas Volz Removed group by for inner queries, not needed.
-- | 1.23 11 Mar 2021 Douglas Volz Removed redundant joins and tables to improve
-- | performance. Added Make Buy Code.
-- | 1.24 28 Feb 2022 Douglas Volz Move category columns to first select statement.
-- +=============================================================================+*/

   more
Run CAC Material Account Summary No SLA and other Oracle EBS reports with Blitz Report™ on our demo environment
select	nvl(gl.short_name, gl.name) Ledger,
	haou2.name Operating_Unit,
	mtl_acct.organization_code Org_Code,
	haou2_from.name From_OU,
	haou2_to.name To_OU,
	nvl((select	ml2.meaning
	     from	mfg_lookups ml2
	     where	ml2.lookup_type  = 'MTL_FOB_POINT'
	     and	ml2.lookup_code  = mtl_acct.fob_point),'') FOB_Point,
	mtl_acct.ship_from_org Ship_From_Org,
	mtl_acct.ship_to_org Ship_To_Org,
	-- No SLA Version changes
	-- ah.period_name Period_Name,
	oap.period_name,
	&segment_columns
	mtl_acct.item_number Item_Number,
	mtl_acct.item_description Item_Description,
	mtl_acct.item_type Item_Type,
	-- Revision for version 1.22
	mtl_acct.inventory_item_status_code Status_Code,
	-- Revision for version 1.23
	mtl_acct.make_buy_code Make_Buy_Code,
	-- Revision for version 1.12 and 1.24
&category_columns
	mtl_acct.acct_line_type Accounting_Line_Type,
	mtl_acct.transaction_type_name Transaction_Type,
	mtl_acct.transaction_source Transaction_Source,	
	mtl_acct.subinventory_code Subinventory,
        mtl_acct.primary_uom_code UOM_Code,
	sum(mtl_acct.primary_quantity) Quantity,
	gl.currency_code Curr_Code,
	sum(mtl_acct.Matl_Amount) Material_Amount,
	sum(mtl_acct.Matl_Ovhd_Amount) Material_Overhead_Amount,
 	sum(mtl_acct.Resource_Amount) Resource_Amount,
	sum(mtl_acct.OSP_Amount) Outside_Processing_Amount,
	sum(mtl_acct.Overhead_Amount) Overhead_Amount,
	sum(mtl_acct.mta_amount) Amount
from	gl_code_combinations gcc,
	hr_organization_information hoi,
	hr_all_organization_units_vl haou, -- inv_organization_id
	hr_all_organization_units_vl haou2, -- operating unit
	hr_organization_information hoi_from,
	hr_all_organization_units_vl haou_from, -- inv_organization_id
	hr_all_organization_units_vl haou2_from, -- operating unit
	hr_organization_information hoi_to,
	hr_all_organization_units_vl haou_to, -- inv_organization_id
	hr_all_organization_units_vl haou2_to, -- operating unit
	gl_ledgers gl,
	-- No SLA Version changes
	org_acct_periods oap,
	-- xla.xla_transaction_entities ent,  -- apps synonym not working
	-- xla_events xe,
	-- xla_distribution_links xdl,
	-- xla_ae_headers ah,
	-- xla_ae_lines al,
	-- End of No SLA Version changes
	-- ==========================================================================
	-- Use this inline table to fetch the WIP and non-WIP material transactions
	-- ==========================================================================
	(
	 select	mp.organization_code organization_code,
		-- Fix for version 1.6
		-- Revision for version 1.5
		decode(mmt.transaction_action_id,
			 3, mp_mmt_org.organization_code,  -- Direct Org Transfer, txn_id 3
			 9, mp_mmt_org.organization_code,  -- Logical Intercompany Sales Issue, txn_id 11
			10, mp_xfer_org.organization_code, -- Logical Intercompany Shipment Receipt, txn_id 10
			12, mp_xfer_org.organization_code, -- Receive from intransit, Int Req Intr Rcpt, txn_id 12,61
			13, mp_mmt_org.organization_code,  -- Logical Intercompany Receipt Return, txn_id 13
			15, mp_xfer_org.organization_code, -- Logical Intransit Receipt, txn_id 76
			-- Revision for version 1.11
			17, mp_xfer_org.organization_code, -- Logical Expense Requisition Receipt, txn_id 27
			21, mp_mmt_org.organization_code,  -- Intransit Shipment, Int Order Intr Ship,  txn_id 21,62
			22, mp_mmt_org.organization_code,  -- Logical Intransit Shipment, tnx_id 60, 65
		    '') ship_from_org,
                decode(mmt.transaction_action_id,
			 3, mp_xfer_org.organization_code, -- Direct Org Transfer, txn_id 3
			 9, mp_xfer_org.organization_code, -- Logical Intercompany Sales Issue, txn_id 11
			10, mp_mmt_org.organization_code,  -- Logical Intercompany Shipment Receipt, txn_id 10
			12, mp_mmt_org.organization_code,  -- Receive from intransit, Int Req Intr Rcpt, txn_id 12,61
			13, mp_xfer_org.organization_code, -- Logical Intercompany Receipt Return, txn_id 13
			15, mp_mmt_org.organization_code,  -- Logical Intransit Receipt, txn_id 76
			-- Revision for version 1.11
			17, mp_mmt_org.organization_code,  -- Logical Expense Requisition Receipt, txn_id 27
			21, mp_xfer_org.organization_code, -- Intransit Shipment, Int Order Intr Ship,  txn_id 21,62
			22, mp_xfer_org.organization_code, -- Logical Intransit Shipment, tnx_id 60, 65
		    '') ship_to_org,
		-- End revision for version 1.5
		-- End revision for version 1.6 
		mp.organization_id organization_id,
		-- Revision for version 1.8
                decode(mmt.transaction_action_id,
			 3, mp_mmt_org.organization_id,  -- Direct Org Transfer, txn_id 3
			 9, mp_mmt_org.organization_id,  -- Logical Intercompany Sales Issue, txn_id 11
			10, mp_xfer_org.organization_id, -- Logical Intercompany Shipment Receipt, txn_id 10
			12, mp_xfer_org.organization_id, -- Receive from intransit, Int Req Intr Rcpt, txn_id 12,61
			13, mp_mmt_org.organization_id,  -- Logical Intercompany Receipt Return, txn_id 13
			15, mp_xfer_org.organization_id, -- Logical Intransit Receipt, txn_id 76
			-- Revision for version 1.11
			17, mp_xfer_org.organization_id, -- Logical Expense Requisition Receipt, txn_id 27	
			21, mp_mmt_org.organization_id,  -- Intransit Shipment, Int Order Intr Ship,  txn_id 21,62
			22, mp_mmt_org.organization_id,  -- Logical Intransit Shipment, tnx_id 60, 65
			mta.organization_id) ship_from_org_id,
		decode(mmt.transaction_action_id,
			 3, mp_xfer_org.organization_id, -- Direct Org Transfer, txn_id 3
			 9, mp_xfer_org.organization_id, -- Logical Intercompany Sales Issue, txn_id 11
			10, mp_mmt_org.organization_id,  -- Logical Intercompany Shipment Receipt, txn_id 10
			12, mp_mmt_org.organization_id,  -- Receive from intransit, Int Req Intr Rcpt, txn_id 12,61
			13, mp_xfer_org.organization_id, -- Logical Intercompany Receipt Return, txn_id 13
			15, mp_mmt_org.organization_id,  -- Logical Intransit Receipt, txn_id 76
			-- Revision for version 1.11
			17, mp_mmt_org.organization_id,  -- Logical Expense Requisition Receipt, txn_id 27
			21, mp_xfer_org.organization_id, -- Intransit Shipment, Int Order Intr Ship,  txn_id 21,62
			22, mp_xfer_org.organization_id, -- Logical Intransit Shipment, tnx_id 60, 65
			mta.organization_id) ship_to_org_id,
		mmt.fob_point fob_point,
		-- End revision for version 1.8
		mmt.acct_period_id acct_period_id,
		mta.reference_account reference_account,
		-- Revision for version 1.7, comment this out, for Release 12
		mta.inv_sub_ledger_id inv_sub_ledger_id,
		mta.inventory_item_id,
		msiv.concatenated_segments item_number,
		msiv.description item_description,
		-- Revision for version 1.6 and 1.19, add in item type
		fcl.meaning item_type,
		-- Revision for version 1.22
		misv.inventory_item_status_code,
		-- Revision for version 1.23
		ml2.meaning Make_Buy_Code,
		ml1.meaning acct_line_type,
		mtt.transaction_type_name transaction_type_name,
		-- Revision for version 1.13
		mtst.transaction_source_type_name transaction_source,
		-- Revision for version 1.15
		decode(:p_show_subinv,                                                                    -- p_show_subinv
			'N',  decode(mta.accounting_line_type, 
				 7, ml1.meaning, -- WIP
				14, ml1.meaning, -- Intransit
				 1, ml1.meaning, -- Subinventory / Inventory valuation
				null),
			'Y',
			decode(mta.accounting_line_type, 7, ml1.meaning, 14, ml1.meaning, 1,
			 decode(mmt.transaction_action_id,
				 2, decode(sign (mta.primary_quantity),
					-1, mmt.subinventory_code,
					 1, mmt.transfer_subinventory,
					mmt.subinventory_code
	                        	  ),
				 3, decode(mmt.organization_id,
					mta.organization_id, mmt.subinventory_code,
					mmt.transfer_subinventory
					  ),
				21, decode(sign (mta.primary_quantity),
					-1, mmt.subinventory_code,
					 1, mmt.transfer_subinventory,
					mmt.subinventory_code
					  ),
				22, decode(sign (mta.primary_quantity),
					-1, mmt.subinventory_code,
					 1, mmt.transfer_subinventory,
					mmt.subinventory_code
					  ),
				28, decode(sign (mta.primary_quantity),
					-1, mmt.subinventory_code,
					 1, mmt.transfer_subinventory,
					mmt.subinventory_code
					  ),
				mmt.subinventory_code
				-- Fix for version 1.6
				-- )
		                ) 
			      ),
			decode(mta.accounting_line_type, 
				 7, ml1.meaning, -- WIP
				14, ml1.meaning, -- Intransit
				 1, ml1.meaning, -- Subinventory / Inventory valuation
				null)		
		      ) subinventory_code,
		-- End revision for version 1.15
		-- Revision for version 1.21
		muomv.uom_code primary_uom_code,
		-- Revision for version 1.20, logic fix for the transaction quantity
		decode(mmt.transaction_action_id, 
			24, mmt.quantity_adjusted,
			mta.primary_quantity/
				(select count(*)
					from	mtl_transaction_accounts mta2
					where	mta2.transaction_id       = mta.transaction_id
					and	mta2.reference_account    = mta.reference_account
					and	mta2.accounting_line_type = mta.accounting_line_type)
		) primary_quantity,
		-- Revision for version 1.13
		decode(mta.cost_element_id,
			1, mta.base_transaction_value,
			0) Matl_Amount,
		decode(mta.cost_element_id,
			2, mta.base_transaction_value,
			0) Matl_Ovhd_Amount,
	 	decode(mta.cost_element_id,
			3, mta.base_transaction_value,
			0) Resource_Amount,
		decode(mta.cost_element_id,
			4, mta.base_transaction_value,
			0) OSP_Amount,
		decode(mta.cost_element_id,
			5, mta.base_transaction_value,
			0) Overhead_Amount,
		-- End revision for version 1.13
		mta.base_transaction_value mta_amount
	 from	mtl_transaction_accounts mta,
		mtl_material_transactions mmt,
		mtl_transaction_types mtt,
		mtl_system_items_vl msiv,
		-- Revision for version 1.21
		mtl_units_of_measure_vl muomv,
		-- Revision for version 1.22
		mtl_item_status_vl misv,
		-- Revision for version 1.13
		mtl_txn_source_types mtst,
		mtl_parameters mp,
		-- Fix for version 1.6
		-- Revision for version 1.5
		-- mtl_parameters mp_owning_org, -- Owning Org
		-- End fix for version 1.6
		mtl_parameters mp_xfer_org,   -- Transfer Org
		mtl_parameters mp_mmt_org,    -- MMT Org
		-- End revision for version 1.5
		-- Revision for version 1.19
		fnd_common_lookups fcl,
		mfg_lookups ml1, -- Accounting Line Type
		-- Revision for version 1.23
		mfg_lookups ml2  -- Planning Make Buy Code
	 -- ========================================================
	 -- Material Transaction, org and item joins
	 -- ========================================================
	 where	mta.transaction_id              = mmt.transaction_id
	 and	mmt.transaction_type_id         = mtt.transaction_type_id
	 and	msiv.organization_id            = mta.organization_id
	 and	msiv.inventory_item_id          = mta.inventory_item_id
	 -- Revision for version 1.21
	 and	msiv.primary_uom_code           = muomv.uom_code
	 -- Revision for version 1.22
	 and	msiv.inventory_item_status_code = misv.inventory_item_status_code
	 and	mp.organization_id              = mta.organization_id
	 -- Fix for version 1.6
	 -- Revision for version 1.5
	 -- and	mp_owning_org.organization_id   = nvl(mmt.owning_organization_id, mta.organization_id)
	 -- and	mp_xfer_org.organization_id     = nvl(mmt.transfer_organization_id, mta.organization_id)
	 -- and	mp_mmt_org.organization_id      = mmt.organization_id
	 -- End revision for version 1.5
	 and	mp_xfer_org.organization_id     = nvl(mmt.transfer_organization_id, mmt.organization_id)
	 and	mp_mmt_org.organization_id      = mmt.organization_id
	 -- End fix for version 1.6
	 -- ========================================================
	 -- Material Transaction date and accounting code joins
	 -- ========================================================
	 and	4=4
	 -- Revision for version 1.14
	 -- Join to mta instead of mmt for faster performance
	 -- Revision for version 1.13
	 -- and	mmt.transaction_source_type_id  = mtst.transaction_source_type_id
	 and	mta.transaction_source_type_id  = mtst.transaction_source_type_id
	 -- End of revision for version 1.14
	 and	fcl.lookup_code (+)             = msiv.item_type
	 and	fcl.lookup_type (+)             = 'ITEM_TYPE'
	 and	ml1.lookup_type                 = 'CST_ACCOUNTING_LINE_TYPE'
	 and	ml1.lookup_code                 = mta.accounting_line_type
	 -- Revision for version 1.23
	 and	ml2.lookup_type                 = 'MTL_PLANNING_MAKE_BUY'
	 and	ml2.lookup_code                 = msiv.planning_make_buy_code
	) mtl_acct
-- ========================================================
-- using the base tables to avoid the performance issues
-- with org_organization_definitions and hr_operating_units
-- ========================================================
where	hoi.org_information_context      = 'Accounting Information'
and	hoi.organization_id              = mtl_acct.organization_id
and	hoi.organization_id              = haou.organization_id   -- this gets the organization name
and	haou2.organization_id            = to_number(hoi.org_information3) -- this gets the operating unit id
and	gl.ledger_id                     = to_number(hoi.org_information1) -- get the ledger_id
and	1=1                              -- p_item_number, p_org_code, p_operating_unit, p_ledger
-- ========================================================
-- Revision for version 1.8
-- Joins for From and To Operating_Units
-- ========================================================
and	hoi_from.org_information_context = 'Accounting Information'
and	hoi_from.organization_id         = mtl_acct.ship_from_org_id
and	hoi_from.organization_id         = haou_from.organization_id   -- this gets the organization name
and	haou2_from.organization_id       = to_number(hoi_from.org_information3) -- this gets the operating unit id
and	hoi_to.org_information_context   = 'Accounting Information'
and	hoi_to.organization_id           = mtl_acct.ship_to_org_id
and	hoi_to.organization_id           = haou_to.organization_id   -- this gets the organization name
and	haou2_to.organization_id         = to_number(hoi_to.org_information3) -- this gets the operating unit id
-- ========================================================
-- SLA table joins to get the exact account numbers
-- ========================================================
-- No SLA Version changes
-- and	ent.entity_code                  = 'MTL_ACCOUNTING_EVENTS'
-- and	ent.application_id               = 707
-- and	xe.application_id                = ent.application_id
-- and	xe.event_id                      = xdl.event_id
-- and	ah.entity_id                     = ent.entity_id
-- and	ah.ledger_id                     = ent.ledger_id
-- and	ah.application_id                = al.application_id
-- and	ah.application_id                = 707
-- and	ah.event_id                      = xe.event_id
-- and	ah.ae_header_id                  = al.ae_header_id
-- and	al.application_id                = ent.application_id
-- and	al.ledger_id                     = ah.ledger_id
-- and	al.ae_header_id                  = xdl.ae_header_id
-- and	al.ae_line_num 	                 = xdl.ae_line_num
-- and	xdl.application_id               = ent.application_id
-- and	xdl.source_distribution_type     = 'MTL_TRANSACTION_ACCOUNTS'
-- and	mtl_acct.inv_sub_ledger_id       = xdl.source_distribution_id_num_1
-- Revision for version 1.17, outer join for CCIDs
-- and	gcc.code_combination_id (+)      = al.code_combination_id
and	gcc.code_combination_id (+)      = mtl_acct.reference_account
and	oap.acct_period_id               = mtl_acct.acct_period_id
-- End of No SLA Version changes
-- ==========================================================
group by 
	nvl(gl.short_name, gl.name),
	haou2.name,
	mtl_acct.organization_code,
	haou2_from.name, -- From_OU
	haou2_to.name, -- To_OU
	mtl_acct.fob_point, -- FOB_Point
	mtl_acct.ship_from_org,
	mtl_acct.ship_to_org,
	-- No SLA Version changes
	-- ah.period_name,
	oap.period_name,
	&segment_columns_grp
	mtl_acct.item_number,
	mtl_acct.item_description,
	mtl_acct.item_type,
	-- Revision for version 1.22
	mtl_acct.inventory_item_status_code,
	-- Revision for version 1.23
	mtl_acct.make_buy_code,
	-- Revision for version 1.12 and 1.24
	mtl_acct.organization_id,
	mtl_acct.inventory_item_id,
	-- End revision for version 1.12 and 1.24
	mtl_acct.subinventory_code,
	mtl_acct.acct_line_type,
	mtl_acct.transaction_type_name,
	mtl_acct.transaction_source,
	mtl_acct.primary_uom_code,
	gl.currency_code
order by
	nvl(gl.short_name, gl.name), -- Ledger
	haou2.name, -- Operating_Unit
	mtl_acct.organization_code, -- Org_Code
	-- No SLA Version changes
	-- ah.period_name,
	oap.period_name, 
	&segment_columns_grp
	mtl_acct.item_number, -- Item_Number
	mtl_acct.acct_line_type,
	mtl_acct.transaction_type_name, -- Transaction_Type	
	mtl_acct.subinventory_code -- Subinventory
Parameter Name SQL text Validation
Transaction Date From
mta.transaction_date >= :p_trx_date_from and
mmt.transaction_date >= :p_trx_date_from
Date
Transaction Date To
mta.transaction_date < :p_trx_date_to + 1 and
mmt.transaction_date < :p_trx_date_to +1
Date
Show Subinventory
 
LOV Oracle
Category Set 1
select x.* from (
select
'(select min(mcb.'||lower(fifsv.application_column_name)||') keep (dense_rank first order by mic.category_id) from mtl_item_categories mic, mtl_categories_b mcb where mic.category_set_id='||mcsv.category_set_id||' and mtl_acct.organization_id=mic.organization_id and mtl_acct.inventory_item_id=mic.inventory_item_id and mic.category_id=mcb.category_id) "'||substr(fifsv.form_left_prompt,1,30)||'",' sql_text
from
mtl_category_sets_vl mcsv,
fnd_id_flex_segments_vl fifsv
where
mcsv.category_set_name=:category_set_name1 and
mcsv.structure_id=fifsv.id_flex_num and
fifsv.application_id=401 and
fifsv.id_flex_code='MCAT'
order by
fifsv.id_flex_num,
fifsv.segment_num
) x
union all
select
'(select min(mct.description) keep (dense_rank first order by mic.category_id) from mtl_item_categories mic, mtl_categories_tl mct where mic.category_set_id='||mcsv.category_set_id||' and mtl_acct.organization_id=mic.organization_id and mtl_acct.inventory_item_id=mic.inventory_item_id and mic.category_id=mct.category_id and mct.language=userenv(''lang'')) "'||substr(mcsv.category_set_name||' Description',1,30)||'",' sql_text
from
mtl_category_sets_vl mcsv
where
mcsv.category_set_name=:category_set_name1
LOV
Category Set 2
select x.* from (
select
'(select min(mcb.'||lower(fifsv.application_column_name)||') keep (dense_rank first order by mic.category_id) from mtl_item_categories mic, mtl_categories_b mcb where mic.category_set_id='||mcsv.category_set_id||' and mtl_acct.organization_id=mic.organization_id and mtl_acct.inventory_item_id=mic.inventory_item_id and mic.category_id=mcb.category_id) "'||substr(fifsv.form_left_prompt,1,30)||'",' sql_text
from
mtl_category_sets_vl mcsv,
fnd_id_flex_segments_vl fifsv
where
mcsv.category_set_name=:category_set_name2 and
mcsv.structure_id=fifsv.id_flex_num and
fifsv.application_id=401 and
fifsv.id_flex_code='MCAT'
order by
fifsv.id_flex_num,
fifsv.segment_num
) x
union all
select
'(select min(mct.description) keep (dense_rank first order by mic.category_id) from mtl_item_categories mic, mtl_categories_tl mct where mic.category_set_id='||mcsv.category_set_id||' and mtl_acct.organization_id=mic.organization_id and mtl_acct.inventory_item_id=mic.inventory_item_id and mic.category_id=mct.category_id and mct.language=userenv(''lang'')) "'||substr(mcsv.category_set_name||' Description',1,30)||'",' sql_text
from
mtl_category_sets_vl mcsv
where
mcsv.category_set_name=:category_set_name2
LOV
Item Number
mtl_acct.item_number = :p_item_number
LOV
Organization Code
mtl_acct.organization_code = :p_org_code
LOV
Operating Unit
haou2.name = :p_operating_unit
LOV
Ledger
gl.name = :p_ledger
LOV