CAC Inventory and Intransit Value (Period-End)

âžœ Back to Library
Description
Categories: Enginatics, Toolkit - Cost Accounting
Repository: Github Columns: Ledger, Operating Unit, Org Code, Period Name, Company, Account, Cost Centre, Product, Item Number, Item Description ...
Tracks the amount of inventory at the end of the month. If you enter a cost type this report uses the item costs from the cost type; if you leave the cost type blank it uses the item costs from the month-end snapshot. In either case this report uses the month-end quantities, based on the entered period name.

Note: if you enter a cost type this report uses the item costs from the cost typ ...  Tracks the amount of inventory at the end of the month. If you enter a cost type this report uses the item costs from the cost type; if you leave the cost type blank it uses the item costs from the month-end snapshot. In either case this report uses the month-end quantities, based on the entered period name.

Note: if you enter a cost type this report uses the item costs from the cost type; if you leave the cost type blank it uses the item costs from the month-end snapshot.

/* +=============================================================================+
-- | Copyright 2009 - 2020 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_inventory_val_rept.sql
-- |
-- | Parameters:
-- | p_period_name -- Accounting period you wish to report for
-- | 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_cost_type -- Enter a Cost Type to value the quantities
-- | using the Cost Type Item Costs; or, if
-- | Cost Type is blank or null the report will
-- | use the stored month-end snapshot values
-- | 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
-- |
-- | ===================================================================
-- | Note: if you enter a cost type this script uses the item costs
-- | from the cost type; if you leave the cost type
-- | blank it uses the item costs from the month-end snapshot.
-- | ===================================================================
-- | Version Modified on Modified by Description
-- | ======= =========== ============== =========================================
-- | 1.0 27 Sep 2009 Douglas Volz Initial Coding
-- | 1.1 28 Sep 2009 Douglas Volz Added a sum for the ICP costs from cicd
-- | 1.16 23 Apr 2020 Douglas Volz Changed to multi-language views for the item
-- | master, item categories and operating units.
-- | Used mfg_lookups for "Intransit".
-- +=============================================================================+*/   more 
select	nvl(gl.short_name, gl.name) Ledger,
-- =======================================================================
-- The first select gets the period-end quantities from the subinventories
-- =======================================================================
	haou2.name Operating_Unit,
	mp.organization_code Org_Code,
	oap.period_name Period_Name,
	&segment_columns
	msiv.concatenated_segments  Item_Number,
	msiv.description Item_Description,
	-- Revision for version 1.13
	fcl.meaning Item_Type,
	-- Revision for version 1.14 and 1.16
	misv.inventory_item_status_code_tl Item_Status,
	-- Revision for version 1.11
	nvl((select	max(mc.segment1)
	     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       = msiv.inventory_item_id
	     and	mic.organization_id         = msiv.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')
	),'') "&p_category_set1",						-- p_category_set1
	nvl((select	max(mc.segment1)
	     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       = msiv.inventory_item_id
	     and	mic.organization_id         = msiv.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')
	),'') "&p_category_set2",						-- p_category_set2
	-- End revision for version 1.11
	gl.currency_code Curr_Code,
	round((nvl(cpcs.rollback_value,0)) /
	decode(nvl(cpcs.rollback_quantity,0), 0, 1,
	nvl(cpcs.rollback_quantity,0)),5) Item_Cost,
	cpcs.subinventory_code Subinventory_or_Intransit,
	-- Revision for version 1.16
	muomv.uom_code UOM_Code,
	round(sum(nvl(cpcs.rollback_quantity,0)),3) Onhand_Quantity,
	round(sum(nvl(cpcs.rollback_value,0)),2) Onhand_Value
from	cst_period_close_summary cpcs,
	org_acct_periods oap,
	mtl_parameters mp,
	-- Revision for version 1.16
	mtl_system_items_vl msiv,
	mtl_units_of_measure_vl muomv,
	mtl_item_status_vl misv,
	-- End Revision for version 1.16
	mtl_secondary_inventories msub,
	gl_code_combinations gcc,  -- subinventory accounts
	hr_organization_information hoi,
	hr_all_organization_units_vl haou,
	hr_all_organization_units_vl haou2,
	gl_ledgers gl,
	fnd_common_lookups fcl
	-- ===========================================
	-- Inventory accounting period joins
	-- ===========================================
where	oap.acct_period_id              = cpcs.acct_period_id
and	oap.organization_id             = mp.organization_id 
-- ========================================================================
-- Subinventory, mtl parameter, item master and period close snapshot joins
-- ========================================================================
and	msub.secondary_inventory_name   = cpcs.subinventory_code
and	msub.organization_id            = cpcs.organization_id
and	mp.organization_id              = cpcs.organization_id
-- Revision for version 1.16
and	mp.organization_id              = msiv.organization_id
and	msiv.organization_id            = cpcs.organization_id
and	msiv.inventory_item_id          = cpcs.inventory_item_id
and	msiv.primary_uom_code           = muomv.uom_code
and	msiv.inventory_item_status_code = misv.inventory_item_status_code
-- End for revision for version 1.16
-- ===========================================
-- Accounting code combination joins
-- ===========================================
and	msub.material_account         = gcc.code_combination_id (+)
-- ===========================================
-- Organization joins to the HR org model
-- ===========================================
and	hoi.org_information_context   = 'Accounting Information'
and	hoi.organization_id           = mp.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_period_name, p_item_number, p_org_code, p_operating_unit, p_ledger
-- ===========================================
-- For Item_Type
-- ===========================================
and	fcl.lookup_code (+)           = msiv.item_type
and	fcl.lookup_type (+)           = 'ITEM_TYPE'
-- End revision for version 1.13
-- ===========================================
-- limit the rows returned-don't get zero rows
-- ===========================================
and	nvl(cpcs.rollback_quantity,0) <> 0
-- Don't report expense subinventories or expense items
and	msub.asset_inventory          <> 2
and	msiv.inventory_asset_flag     = 'Y'
-- ===========================================
-- Revision for version 1.12
-- Run this query if the Cost Type parameter 
-- is null, to get the snapshot inventory value.
-- ===========================================
and	decode('&p_cost_type',							-- p_cost_type
		null, 'use snapshot values', 
		'do not use snapshot values') =  'use snapshot values'
group by
	nvl(gl.short_name, gl.name),
	haou2.name,
	mp.organization_code,
	oap.period_name,
	&segment_columns_grp
	msiv.concatenated_segments,
	msiv.description,
	-- Revision for version 1.13
	fcl.meaning,
	-- Revision for version 1.14 and 1.16
	misv.inventory_item_status_code_tl,
	gl.currency_code,
	round((nvl(cpcs.rollback_value,0)) /
		decode(nvl(cpcs.rollback_quantity,0), 0, 1,
		nvl(cpcs.rollback_quantity,0)),5),
	msiv.inventory_item_id,
	msiv.organization_id,
	cpcs.subinventory_code,
	-- Revision for version 1.16
	muomv.uom_code
union all
-- =======================================================================
-- The second select gets the period-end quantities from intransit
-- =======================================================================
select	nvl(gl.short_name, gl.name) Ledger,
	haou2.name Operating_Unit,
	mp.organization_code Org_Code,
	oap.period_name Period_Name,
	&segment_columns
	msiv.concatenated_segments  Item_Number,
	msiv.description Item_Description,
	-- Revision for version 1.13
	fcl.meaning Item_Type,
	-- Revision for version 1.14 and 1.16
	misv.inventory_item_status_code_tl Item_Status,
	-- Revision for version 1.11
	nvl((select	max(mc.segment1)
	     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       = msiv.inventory_item_id
	     and	mic.organization_id         = msiv.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')
	),'') "&p_category_set1",
	nvl((select	max(mc.segment1)
	     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       = msiv.inventory_item_id
	     and	mic.organization_id         = msiv.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')
	),'') "&p_category_set2",
	-- End revision for version 1.11
	gl.currency_code Curr_Code,
	round((nvl(cpcs.rollback_intransit_value,0)) /
		decode(nvl(cpcs.rollback_quantity,0), 0, 1,
		nvl(cpcs.rollback_quantity,0)),5) Item_Cost,
	ml.meaning Subinventory_or_Intransit,
	muomv.uom_code UOM_Code,
	round(sum(nvl(cpcs.rollback_quantity,0)),3) Quantity,
	round(sum(nvl(cpcs.rollback_intransit_value,0)),2) Onhand_Value                                                               -- p_pii_cost_type
from	cst_period_close_summary cpcs,
	org_acct_periods oap,
	mtl_parameters mp,
	-- Revision for version 1.16
	mtl_system_items_vl msiv,
	mtl_units_of_measure_vl muomv,
	mtl_item_status_vl misv,
	mfg_lookups ml,
	-- End Revision for version 1.16
	gl_code_combinations gcc,  -- subinventory accounts
	hr_organization_information hoi,
	hr_all_organization_units_vl haou,
	hr_all_organization_units_vl haou2,
	gl_ledgers gl,
	fnd_common_lookups fcl,
	-- Revision for version 1.14
	-- Revision for version 1.10
	-- If the Inventory Parameters is missing the Intransit Account
	-- Need to get the Intransit Account from the Shipping Network
	(select	ic.code_combination_id,
		ic.organization_id
	 from	(select	gcc.code_combination_id,
			mip.to_organization_id organization_id
		 from	mtl_interorg_parameters mip,
			mtl_parameters mp,
			gl_code_combinations gcc
		 where	mip.fob_point               = 1 -- shipment
		 and	mp.organization_id          = mip.to_organization_id
		 and	mp.intransit_inv_account is null
		 and	gcc.code_combination_id (+) = mip.intransit_inv_account
		 union all
		 select	gcc.code_combination_id,
			mip.from_organization_id organization_id
		 from	mtl_interorg_parameters mip,
			mtl_parameters mp,
			gl_code_combinations gcc
		 where	mip.fob_point               = 2 -- receipt
		 and	mp.organization_id          = mip.from_organization_id
		 and	mp.intransit_inv_account is null
		 and	gcc.code_combination_id (+) = mip.intransit_inv_account
		 union all
		 select	gcc.code_combination_id,
			mp.organization_id organization_id
		 from	mtl_parameters mp,
			gl_code_combinations gcc
		 where	mp.intransit_inv_account is not null
		 and	gcc.code_combination_id (+) = mp.intransit_inv_account
		) ic
	 group by
		ic.code_combination_id,
		ic.organization_id
	) interco
	-- End revision for version 1.0
-- ===========================================
-- Inventory accounting period joins
-- ===========================================
where	oap.acct_period_id              = cpcs.acct_period_id
and	oap.organization_id             = mp.organization_id 
-- ========================================================================
-- Subinventory, mtl parameter, item master and period close snapshot joins
-- ========================================================================
and	cpcs.subinventory_code is null
and	mp.organization_id              = cpcs.organization_id
-- Revision for version 1.16
and	mp.organization_id              = msiv.organization_id
and	msiv.organization_id            = cpcs.organization_id
and	msiv.inventory_item_id          = cpcs.inventory_item_id
and	msiv.primary_uom_code           = muomv.uom_code
and	msiv.inventory_item_status_code = misv.inventory_item_status_code
-- End for revision for version 1.16
-- ===========================================
-- Accounting code combination joins
-- ===========================================
-- Revision for version 1.9
-- and  mp.intransit_inv_account        = gcc.code_combination_id
-- Revision for version 1.14
-- -- Use the material account as a default for segments 1,3,4,5,6
-- -- and	mp.material_account     = gcc.code_combination_id
-- Revision for version 1.14
and	interco.code_combination_id     = gcc.code_combination_id (+)
and	interco.organization_id         = mp.organization_id
-- ===========================================
-- Organization joins to the HR org model
-- ===========================================
and	hoi.org_information_context     = 'Accounting Information'
and	hoi.organization_id             = mp.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_period_name, p_item_number, p_org_code, p_operating_unit, p_ledger
-- ===========================================
-- For Item_Type
-- Revision for version 1.13
-- ===========================================
and	fcl.lookup_code (+)             = msiv.item_type
and	fcl.lookup_type (+)             = 'ITEM_TYPE'
-- Revision for version 1.16
and	ml.lookup_code                  = 3
and	ml.lookup_type                  = 'MSC_CALENDAR_TYPE'
-- ===========================================
-- limit the rows returned-don't get zero rows
-- ===========================================
and	nvl(cpcs.rollback_quantity,0) <> 0
-- ===========================================
-- Revision for version 1.12
-- Run this query if the Cost Type parameter 
-- is null, to get the snapshot inventory value.
-- ===========================================
and	decode('&p_cost_type',							-- p_cost_type
		null, 'use snapshot values', 
		'do not use snapshot values') =  'use snapshot values'
group by
	nvl(gl.short_name, gl.name),
	haou2.name,
	mp.organization_code,
	oap.period_name,
	&segment_columns_grp
	msiv.concatenated_segments,
	msiv.description,
	-- Revision for version 1.13
	fcl.meaning,
	-- Revision for version 1.14 and 1.16
	misv.inventory_item_status_code_tl,
	gl.currency_code,
	round(nvl(cpcs.rollback_intransit_value,0) /
		decode(nvl(cpcs.rollback_quantity,0), 0, 1,
		nvl(cpcs.rollback_quantity,0)),5),
	msiv.inventory_item_id,
	msiv.organization_id,
	-- Revision for version 1.16
	ml.meaning,
	muomv.uom_code
union all
-- =======================================================================
-- Revision for version 1.12
-- Get the inventory values from the entered Cost Type
-- but use the quantities from the month-end snapshot
-- =======================================================================
-- =======================================================================
-- The first select gets the period-end quantities from the subinventories
-- =======================================================================
select	nvl(gl.short_name, gl.name) Ledger,
	haou2.name Operating_Unit,
	mp.organization_code Org_Code,
	oap.period_name Period_Name,
	&segment_columns
	msiv.concatenated_segments  Item_Number,
	msiv.description Item_Description,
	fcl.meaning Item_Type,
	-- Revision for version 1.14 and 1.16
	misv.inventory_item_status_code_tl Item_Status,
	-- Revision for version 1.11
	nvl((select	max(mc.segment1)
	     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       = msiv.inventory_item_id
	     and	mic.organization_id         = msiv.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')
	),'') "&p_category_set1",
	nvl((select	max(mc.segment1)
	     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       = msiv.inventory_item_id
	     and	mic.organization_id         = msiv.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')
	),'') "&p_category_set2",
	-- End revision for version 1.11
	gl.currency_code Curr_Code,
	round(nvl(cic.item_cost,0),5) Item_Cost,
	cpcs.subinventory_code Subinventory_or_Intransit,
	-- Revision for version 1.16
	muomv.uom_code UOM_Code,
	round(sum(nvl(cpcs.rollback_quantity,0)),3) Onhand_Quantity,
	-- Use the Cost Type Costs instead of the rollback_value
	round(sum(nvl(cpcs.rollback_quantity,0) * nvl(cic.item_cost,0)),2) Onhand_Value
from	cst_period_close_summary cpcs,
	org_acct_periods oap,
	mtl_parameters mp,
	-- Revision for version 1.16
	mtl_system_items_vl msiv,
	mtl_units_of_measure_vl muomv,
	mtl_item_status_vl misv,
	-- End Revision for version 1.16
	mtl_secondary_inventories msub,
	-- Revision for version 1.12
	cst_cost_types cct,
	cst_item_costs cic,
	-- End revision for version 1.12
	gl_code_combinations gcc,  -- subinventory accounts
	hr_organization_information hoi,
	hr_all_organization_units_vl haou,
	hr_all_organization_units_vl haou2,
	gl_ledgers gl,
	-- Revision for version 1.13
	fnd_common_lookups fcl
-- ===========================================
-- Inventory accounting period joins
-- ===========================================
where	oap.acct_period_id            = cpcs.acct_period_id
and	oap.organization_id           = mp.organization_id 
-- ========================================================================
-- Subinventory, mtl parameter, item master and period close snapshot joins
-- ========================================================================
and	msub.secondary_inventory_name = cpcs.subinventory_code
and	msub.organization_id          = cpcs.organization_id
and	mp.organization_id            = cpcs.organization_id
-- Revision for version 1.16
and	mp.organization_id              = msiv.organization_id
and	msiv.organization_id            = cpcs.organization_id
and	msiv.inventory_item_id          = cpcs.inventory_item_id
and	msiv.primary_uom_code           = muomv.uom_code
and	msiv.inventory_item_status_code = misv.inventory_item_status_code
-- End for revision for version 1.16
-- ===========================================
-- Accounting code combination joins
-- ===========================================
and	msub.material_account         = gcc.code_combination_id (+)
-- ===========================================
-- Cost Type Joins
-- Revision for version 1.12
-- ===========================================
and	6=6										-- p_cost_type
and	cct.cost_type_id              = cic.cost_type_id
and	cic.organization_id           = msiv.organization_id
and	cic.inventory_item_id         = msiv.inventory_item_id
-- ===========================================
-- Organization joins to the HR org model
-- ===========================================
and	hoi.org_information_context   = 'Accounting Information'
and	hoi.organization_id           = mp.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_period_name, p_item_number, p_org_code, p_operating_unit, p_ledger
-- ===========================================
-- For Item_Type
-- ===========================================
and	fcl.lookup_code (+)           = msiv.item_type
and	fcl.lookup_type (+)           = 'ITEM_TYPE'
-- End revision for version 1.13
-- ===========================================
-- limit the rows returned-don't get zero rows
-- ===========================================
and	nvl(cpcs.rollback_quantity,0) <> 0
-- Don't report expense subinventories or expense items
and	msub.asset_inventory          <> 2
and	msiv.inventory_asset_flag     = 'Y'
-- ===========================================
-- Revision for version 1.12
-- Run this query if the Cost Type parameter 
-- is not null, use the Cost Type Costs
-- to get the reported inventory value.
-- ===========================================
and	decode('&p_cost_type', 
		null, 'do not use snapshot values', 
		'use cost type values') =  'use cost type values'
group by
	nvl(gl.short_name, gl.name),
	haou2.name,
	mp.organization_code,
	oap.period_name,
	&segment_columns_grp
	msiv.concatenated_segments,
	msiv.description,
	fcl.meaning,
	-- Revision for version 1.14 and 1.16
	misv.inventory_item_status_code_tl,
	gl.currency_code,
	round((nvl(cpcs.rollback_value,0)