CAC Material Account Summary

➜ Back to Library
Description
Categories: Enginatics, Toolkit - Cost Accounting
Repository: Github Columns: Ledger, Operating Unit, Org Code, From OU, To OU, Fob Point, Ship From Org, Ship To Org, Period Name, Company ...
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 includes all material transactions but to keep the report smaller it does not displays WIP job information, such as WIP Accounting Class, Class Description, Assembly Number, Assembly Description or Job Order Number. ...  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 includes all material transactions but to keep the report smaller it does not displays WIP job information, such as WIP Accounting Class, Class Description, Assembly Number, Assembly Description or Job Order Number.
Use the Show Subinventory parameter to 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.

/* +=============================================================================+
-- | Copyright 2009-20 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.19 22 Apr 2020 Douglas Volz Put Item Type lookup code into inner queries, to avoid
-- | creating outer joins errors to multiple tables (12.1.3).
-- | Put item master back into inner queries for the item type
-- | lookup and changed FOB point into a lookup code for languages.
-- | 1.20 03 May 2020 Douglas Volz Can have multiple mta rows with the same CCID, quantity
-- | and accounting line type. To avoid summing incorrectly,
-- | need to count the number of rows and then divide into the
-- | total quantity sum. However, for Standard Cost Updates,
-- | use the quantity adjusted.
-- | 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 Remove inner query group by, not needed.
-- +=============================================================================+*/

   more 
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,
	ah.period_name Period_Name,
	&segment_columns
	mtl_acct.item_number Item_Number,
	mtl_acct.item_description Item_Description,
	mtl_acct.item_type Item_Type,
	mtl_acct.category1 "&p_category_set1",
	mtl_acct.category2 "&p_category_set2",
	-- End revision for version 1.12
	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,
	xla.xla_transaction_entities ent,  -- apps synonym not working
	xla_events xe,
	xla_distribution_links xdl,
	xla_ae_headers ah,
	xla_ae_lines al,
	-- ==========================================================================
	-- 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.12
		nvl((select	max(mc.category_concat_segs)
		     from	mtl_categories_v mc,
				mtl_item_categories mic,
				mtl_category_sets_b mcs,
				mtl_category_sets_tl mcs_tl
		     where	mic.category_set_id         = mcs.category_set_id
		     and	2=2
		     and	mic.inventory_item_id       = mta.inventory_item_id
		     and	mic.organization_id         = mta.organization_id
		     and	mc.category_id              = mic.category_id
		     and	mcs.category_set_id         = mcs_tl.category_set_id
		     and	mcs_tl.language             = userenv('lang')
		   ),'') category1,
		nvl((select	max(mc.category_concat_segs)
		     from	mtl_categories_v mc,
				mtl_item_categories mic,
				mtl_category_sets_b mcs,
				mtl_category_sets_tl mcs_tl
		     where	mic.category_set_id         = mcs.category_set_id
		     and	3=3 
		     and	mic.inventory_item_id       = mta.inventory_item_id
		     and	mic.organization_id         = mta.organization_id
		     and	mc.category_id              = mic.category_id
		     and	mcs.category_set_id         = mcs_tl.category_set_id
		     and	mcs_tl.language             = userenv('lang')
		   ),'') category2,
		-- End revision for version 1.12
		ml.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, ml.meaning, -- WIP
				14, ml.meaning, -- Intransit
				 1, ml.meaning, -- Subinventory / Inventory valuation
				null),
			'Y',
			decode(mta.accounting_line_type, 7, ml.meaning, 14, ml.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