CAC ICP PII Inventory and Intransit Value (Period-End)

➜ Back to Library
Description
Categories: Cost Accounting - Inventory Value, Enginatics, Toolkit - Cost Accounting
Repository: Github
Report showing amount of profit in 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. And as these quantities come from the month-end snapshot (created when yo ...  Report showing amount of profit in 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. And as these quantities come from the month-end snapshot (created when you close the inventory accounting period) and this snapshot is only by inventory organization, subinventory and item and not split out by cost element, this report only shows the Material Account, based upon your Costing Method.

Notes:
1) Profit in inventory is abbreviated as PII or sometimes as ICP - InterCompany Profit.
2) There is a hidden parameter, Numeric Sign for PII, which allows you to set the sign of the profit in inventory amounts. You can specify positive or negative values based on how you enter PII amounts into your PII Cost Type. Defaulted as positive (+1).

/* +=============================================================================+
-- | Copyright 2009 - 2022 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])
-- |
-- | Hidden Parameters:
-- | p_sign_pii -- Hidden parameter to set the sign of the profit in
-- | inventory amounts. This parameter determines if PII
-- | is normally entered as a positive or negative amount.
-- | Displayed Parameters:
-- | 4 p_period_name -- Accounting period you wish to report for
-- | 5 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
-- | 6 p_pii_cost_type -- The PII Cost Type you wish to report (mandatory)
-- | 6 p_pii_sub_element -- The sub-element or resource for profit in inventory,
-- | such as PII or ICP (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
-- | 4 p_item_number -- Enter the specific item number you wish to report (optional)
-- | 3 p_subinventory -- Enter the specific subinventory you wish to report (optional)
-- | 2 p_org_code -- Specific inventory organization you wish to report (optional)
-- | 1 p_operating_unit -- Operating Unit you wish to report, leave blank for all
-- | operating units (optional)
-- | 1 p_ledger -- general ledger you wish to report, leave blank for all
-- | ledgers (optional)
-- | ===================================================================
-- | Version Modified on Modified by Description
-- | ======= =========== ============== =========================================
-- | 1.0 27 Sep 2009 Douglas Volz Initial Coding
-- | 1.19 20 Mar 2022 Douglas Volz Fix for category accounts (valuation accounts) and
-- | added subinventory description.
-- +=============================================================================+*/   more
Run CAC ICP PII Inventory and Intransit Value (Period-End) and other Oracle EBS reports with Blitz Report™ on our demo environment
with inv_organizations as
-- Revision for version 1.17
-- Get the list of organizations
	(select	nvl(gl.short_name, gl.name) ledger,
		gl.ledger_id,
		haou2.name operating_unit,
		haou2.organization_id operating_unit_id,
		mp.organization_code,
		mp.organization_id,
		mca.organization_id category_organization_id,
		-- Revision for version 1.18
		mca.category_set_id, 
		mp.material_account,
		mp.cost_group_accounting,
		mp.primary_cost_method,
		mp.default_cost_group_id,
		haou.date_to disable_date,
		gl.currency_code
	 from	mtl_category_accounts mca,
		mtl_parameters mp,
		hr_organization_information hoi,
		hr_all_organization_units_vl haou, -- inv_organization_id
		hr_all_organization_units_vl haou2, -- operating unit
		gl_ledgers gl
	 where	mp.organization_id              = mca.organization_id (+)
	 -- Avoid the item master organization
	 and	mp.organization_id             <> mp.master_organization_id
	 -- Avoid disabled inventory organizations
	 and	sysdate                        <  nvl(haou.date_to, sysdate +1)
	 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_operating_unit, p_ledger
	 and	2=2                             -- p_org_code
	 group by
		nvl(gl.short_name, gl.name),
		gl.ledger_id,
		haou2.name, -- operating_unit
		haou2.organization_id, -- operating_unit_id
		mp.organization_code,
		mp.organization_id,
		mca.organization_id, -- category_organization_id
		-- Revision for version 1.18
		mca.category_set_id,
		mp.material_account,
		mp.cost_group_accounting,
		mp.primary_cost_method,
		mp.default_cost_group_id,
		haou.date_to,
		gl.currency_code
	),
-- Get the inventory valuation accounts by organization, subinventory and category
valuation_accounts as
	(-- Standard Costing, no Cost Group Accounting
	 select 'Std Cost No Cost Group Accounting' valuation_type,
		msub.organization_id,
		msub.secondary_inventory_name,
		null category_id,
		null category_set_id,
		msub.material_account,
		msub.asset_inventory,
		msub.quantity_tracked,
		msub.default_cost_group_id cost_group_id
	 from	mtl_secondary_inventories msub,
		inv_organizations mp
	 where	msub.organization_id = mp.organization_id
	 and	nvl(mp.cost_group_accounting,2) = 2 -- No
	 -- Avoid organizations with category accounts
	 and	mp.category_organization_id is null
	 and	3=3                             -- p_subinventory
	 union all
	 -- Not Standard Costing, no Cost Group Accounting
	 select	'Not Std Cost No Cost Group Accounting' valuation_type,
		msub.organization_id,
		msub.secondary_inventory_name,
		null category_id,
		null category_set_id,
		mp.material_account,
		msub.asset_inventory,
		msub.quantity_tracked,
		msub.default_cost_group_id cost_group_id
	 from	mtl_secondary_inventories msub,
		inv_organizations mp
	 where	msub.organization_id = mp.organization_id
	 and	nvl(mp.cost_group_accounting,2) = 2 -- No
	 and	mp.primary_cost_method         <> 1 -- not Standard Costing
	 -- Avoid organizations with category accounts
	 and	mp.category_organization_id is null
	 and	3=3                             -- p_subinventory
	 union all
	 -- With Cost Group Accounting
	 select	'Cost Group Accounting' valuation_type,
		msub.organization_id,
		msub.secondary_inventory_name,
		null category_id,
		null category_set_id,
		ccga.material_account,
		msub.asset_inventory,
		msub.quantity_tracked,
		msub.default_cost_group_id cost_group_id
	 from	mtl_secondary_inventories msub,
		cst_cost_group_accounts ccga,
		cst_cost_groups ccg,
		inv_organizations mp
	 where	msub.organization_id            = mp.organization_id
	 and	mp.cost_group_accounting        = 1 -- Yes
	 and	ccga.cost_group_id              = nvl(msub.default_cost_group_id, mp.default_cost_group_id)
	 and	ccga.cost_group_id              = ccg.cost_group_id
	 and	ccga.organization_id            = mp.organization_id
	 -- Avoid organizations with category accounts
	 and	mp.category_organization_id is null
	 and	3=3                             -- p_subinventory
	 union all
	 -- Category Accounting
	 -- Revision for version 1.19
	 select	'Category Accounting' valuation_type,
		mp.organization_id,
		cat_subinv.subinventory_code secondary_inventory_name,
		mc.category_id,
		mp.category_set_id,
		cat_subinv.material_account,
		cat_subinv.asset_inventory,
		cat_subinv.quantity_tracked,
		cat_subinv.cost_group_id
	 from	inv_organizations mp,
		mtl_categories_b mc,
		mtl_category_sets_b mcs,
		mtl_item_categories mic,
		(select	msub.organization_id,
			nvl(mca.subinventory_code, msub.secondary_inventory_name) subinventory_code,
			mca.category_id,
			mp.category_set_id,
			mca.material_account,
			msub.asset_inventory,
			msub.quantity_tracked,
			msub.default_cost_group_id cost_group_id
		 from	mtl_secondary_inventories msub,
			mtl_category_accounts mca,
			inv_organizations mp
		 where	msub.organization_id            = mp.organization_id
		 and	msub.organization_id            = mca.organization_id (+)
		 -- Revision for version 1.19
		 -- and	msub.secondary_inventory_name   = mca.subinventory_code (+)
		 and	msub.secondary_inventory_name   = nvl(mca.subinventory_code, msub.secondary_inventory_name)
		 -- Only get organizations with category accounts
		 and	mp.category_organization_id is not null
		 and	3=3                             -- p_subinventory
		 -- For a given category_id, if a subinventory-specific category account exists
		 -- exclude the category account with a null subinventory, to avoid double-counting  
		 and not exists
				(select	'x'
				 from	mtl_category_accounts mca2
				 where	mca.subinventory_code is null
				 and	mca2.subinventory_code is not null
				 and	mca2.organization_id = mca.organization_id
				 and	mca2.category_id     = mca.category_id
				)
		 group by
			msub.organization_id,
			nvl(mca.subinventory_code, msub.secondary_inventory_name),
			mca.category_id,
			mp.category_set_id,
			mca.material_account,
			msub.asset_inventory,
			msub.quantity_tracked,
			msub.default_cost_group_id
		) cat_subinv
	 where	mp.organization_id              = mic.organization_id
	 and	mp.category_set_id              = mic.category_set_id
	 and	mic.category_id                 = mc.category_id
	 and	mic.category_set_id             = mcs.category_set_id
	 and	mc.category_id                  = mic.category_id
	 and	mic.organization_id             = cat_subinv.organization_id (+)
	 and	mic.category_id                 = cat_subinv.category_id (+)
	 group by
		'Category Accounting',
		mp.organization_id,
		cat_subinv.subinventory_code,
		mc.category_id,
		mp.category_set_id,
		cat_subinv.material_account,
		cat_subinv.asset_inventory,
		cat_subinv.quantity_tracked,
		cat_subinv.cost_group_id
	 -- End revision for version 1.19
	 union all
	 select	'Intransit Accounting' valuation_type,
		interco.organization_id,
		'Intransit' secondary_inventory_name,
		null category_id,
		null category_set_id,
		interco.intransit_inv_account material_account,
		1 asset_inventory,
		1 quantity_tracked,
		mp.default_cost_group_id cost_group_id
	 from	inv_organizations mp,
		(select	ic.intransit_inv_account,
			ic.organization_id
		 from	(select	mip.intransit_inv_account,
				mip.to_organization_id organization_id
			 from	mtl_interorg_parameters mip,
				inv_organizations mp
			 where	mip.fob_point               = 1 -- shipment
			 and	mp.organization_id          = mip.to_organization_id
			 union all
			 select	mip.intransit_inv_account,
				mip.from_organization_id organization_id
			 from	mtl_interorg_parameters mip,
				inv_organizations mp
			 where	mip.fob_point               = 2 -- receipt
			 and	mp.organization_id          = mip.from_organization_id
			) ic
		 group by
			ic.intransit_inv_account,
			ic.organization_id
		) interco
	 where	mp.organization_id = interco.organization_id
	),
-- End revision for version 1.17
-- Revision 1.17 for PII
pii as
	(select	sum(nvl(cicd.item_cost, 0)) pii_item_cost,
		cicd.inventory_item_id,
		cicd.organization_id,
		cct.cost_type pii_cost_type
	 from	cst_item_cost_details cicd,
		bom_resources br,
		cst_cost_types cct,
		mtl_parameters mp
	 where	cicd.resource_id       = br.resource_id
	 and	cicd.cost_type_id      = cct.cost_type_id
	 and	mp.organization_id     = cicd.organization_id
	 and	2=2                    -- p_org_code
	 and	6=6                    -- p_pii_cost_type, p_pii_sub_element
	 group by
		cicd.inventory_item_id,
		cicd.organization_id,
		cct.cost_type
	)
-- End revision 1.17 for PII
 
----------------main query starts here--------------
 
-- =======================================================================
-- Section I.  For non-category accounting, get period-end quantities and
--             values based solely on the month-end inventory snapshot.
-- =======================================================================
select	mp.ledger Ledger,
	mp.operating_unit Operating_Unit,
	mp.organization_code Org_Code,
	onhand.period_name Period_Name,
	&segment_columns
	onhand.concatenated_segments Item_Number,
	onhand.description Item_Description,
	-- Revision for version 1.13
	-- flv.meaning Item_Type,
	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
&category_columns
	mp.currency_code Currency_Code,
	decode(onhand.subinventory_code,
			null, round(nvl(onhand.rollback_intransit_value,0) /
				decode(nvl(onhand.rollback_quantity,0), 0, 1,
				nvl(onhand.rollback_quantity,0)),5),
			round((nvl(onhand.rollback_value,0)) /
				decode(nvl(onhand.rollback_quantity,0), 0, 1,
				nvl(onhand.rollback_quantity,0)),5)
	      ) Gross_Item_Cost,
	-- Revision for version 1.17 PII
	nvl(pii.pii_item_cost,0) PII_Item_Cost,
	decode(onhand.subinventory_code,
			null, round(nvl(onhand.rollback_intransit_value,0) /
				decode(nvl(onhand.rollback_quantity,0), 0, 1,
				nvl(onhand.rollback_quantity,0)),5),
			round((nvl(onhand.rollback_value,0)) /
				decode(nvl(onhand.rollback_quantity,0), 0, 1,
				nvl(onhand.rollback_quantity,0)),5)
	      ) - nvl(pii.pii_item_cost,0) * decode(sign(:p_sign_pii),1,1,-1,-1,1) Net_Item_Cost,
	-- End revision for version 1.17 PII
	nvl(onhand.subinventory_code, ml1.meaning) Subinventory_or_Intransit,
	-- Revision for version 1.19
	nvl(regexp_replace(msub.description,'[^[:alnum:]'' '']', null), ml1.meaning) Description,
	-- Revision for version 1.18
	ml2.meaning Asset,
	-- Revision for version 1.16
	muomv.uom_code UOM_Code,
	round(nvl(onhand.rollback_quantity,0),3) Onhand_Quantity,
	decode(onhand.subinventory_code,
		null, round(nvl(onhand.rollback_intransit_value,0),2),
		round(nvl(onhand.rollback_value,0),2)
	      ) Onhand_Value,
	-- Revision for version 1.17 PII
	round(nvl(onhand.rollback_quantity,0) * nvl(pii.pii_item_cost,0),2) PII_Onhand_Value,
	round(decode(onhand.subinventory_code,
			null, nvl(onhand.rollback_intransit_value,0),
			nvl(onhand.rollback_value,0)
		    ) - round(nvl(onhand.rollback_quantity,0) * nvl(pii.pii_item_cost,0),2) *
			decode(sign(:p_sign_pii),1,1,-1,-1,1),2) Net_Onhand_Value
	-- End revision for version 1.17 PII
from	inv_organizations mp,
	valuation_accounts va,
	-- Revision for version 1.16
	mtl_units_of_measure_vl muomv,
	mtl_item_status_vl misv,
	-- End revision for version 1.16
	-- Revision for version 1.17 PII
	pii,
	gl_code_combinations gcc,
	fnd_common_lookups fcl, -- Item Type
	mfg_lookups ml1, -- Intransit
	-- Revision for version 1.18
	mfg_lookups ml2, -- Inventory Asset
	-- Revision for version 1.19
	mtl_secondary_inventories msub,
	-- Revision for version 1.18
	-- Inner query for onhand quantities and values
	(-- For non-category accounting
	 select	mp.organization_id,
		msiv.inventory_item_id,
		msiv.concatenated_segments,
		-- Revision for version 1.19
		regexp_replace(msiv.description,'[^[:alnum:]'' '']', null) description,
		msiv.primary_uom_code,
		msiv.inventory_item_status_code,
		msiv.item_type,
		msiv.inventory_asset_flag,
		oap.period_name,
		cpcs.acct_period_id,
		nvl(cpcs.subinventory_code, 'Intransit') subinventory_code,
		sum(cpcs.rollback_quantity) rollback_quantity,
		sum(cpcs.rollback_value) rollback_value,
		sum(cpcs.rollback_intransit_value) rollback_intransit_value		
	 from	mtl_system_items_vl msiv,
		cst_period_close_summary cpcs,
		org_acct_periods oap,
		inv_organizations mp
	 where	mp.organization_id              = msiv.organization_id
	 and	oap.acct_period_id              = cpcs.acct_period_id
	 and	oap.organization_id             = mp.organization_id
	 and	msiv.organization_id            = cpcs.organization_id
	 and	msiv.inventory_item_id          = cpcs.inventory_item_id
	 and	mp.category_organization_id is null
	 -- Don't get zero quantities
	 and	nvl(cpcs.rollback_quantity,0)  <> 0
	 -- Don't report expense items
	 and	msiv.inventory_asset_flag       = 'Y'
	 and	4=4                             -- p_period_name, p_item_number
	 -- Need to group by due to possibility for having multiple cost groups by subinventory
	 group by
		mp.organization_id,
		msiv.inventory_item_id,
		msiv.concatenated_segments,
		regexp_replace(msiv.description,'[^[:alnum:]'' '']', null),
		msiv.primary_uom_code,
		msiv.inventory_item_status_code,
		msiv.item_type,
		msiv.inventory_asset_flag,
		oap.period_name,
		cpcs.acct_period_id,
		cpcs.subinventory_code
	) onhand
	-- End revision for version 1.18
-- ========================================================================
-- Subinventory, mtl parameter, item master and period close snapshot joins
-- ========================================================================
where	mp.organization_id              = onhand.organization_id
and	muomv.uom_code                  = onhand.primary_uom_code
and	misv.inventory_item_status_code = onhand.inventory_item_status_code
and	mp.category_organization_id is null
-- Revision for version 1.17 PII
and	pii.organization_id (+)         = onhand.organization_id
and	pii.inventory_item_id (+)       = onhand.inventory_item_id
-- Revision for version 1.19
and	onhand.subinventory_code        = msub.secondary_inventory_name (+)
and	onhand.organization_id          = msub.organization_id (+)
-- End revision for version 1.19
-- ===========================================
-- Accounting code combination joins
-- ===========================================
-- Revision for version 1.18
-- and	msub.material_account           = gcc.code_combination_id (+)
and	va.material_account             = gcc.code_combination_id (+)
and	va.secondary_inventory_name (+) = onhand.subinventory_code
and	va.organization_id (+)          = onhand.organization_id
and	va.valuation_type              <> 'Category Accounting'
-- End revision for version 1.18
-- ===========================================
-- Lookup Codes
-- ===========================================
-- Revision for version 1.13
and	fcl.lookup_code (+)             = onhand.item_type
and	fcl.lookup_type (+)             = 'ITEM_TYPE'
-- Revision for version 1.16
and	ml1.lookup_code                 = 3 -- Intransit
and	ml1.lookup_type                 = 'MSC_CALENDAR_TYPE'
-- Revision for version 1.19
and	ml2.lookup_code                 = nvl(msub.asset_inventory,1)
and	ml2.lookup_type                 = 'SYS_YES_NO'
-- ===========================================
-- 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'
union all
-- =======================================================================
-- Section II. For non-category accounting, get period-end quantities
--             based on the month-end inventory snapshot but get item
--             costs and values from the entered cost type.
-- =======================================================================
select	mp.ledger Ledger,
	mp.operating_unit Operating_Unit,
	mp.organization_code Org_Code,
	onhand.period_name Period_Name,
	&segment_columns
	onhand.concatenated_segments Item_Number,
	onhand.description Item_Description,
	-- Revision for version 1.13
	-- flv.meaning Item_Type,
	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
&category_columns
	mp.currency_code Currency_Code,
	round(nvl(cic.item_cost,0),5) Gross_Item_Cost,
	-- Revision for version 1.17 PII
	nvl(pii.pii_item_cost,0) PII_Item_Cost,
	round(nvl(cic.item_cost,0),5) - nvl(pii.pii_item_cost,0) * decode(sign(:p_sign_pii),1,1,-1,-1,1) Net_Item_Cost,
	-- End revision for version 1.17 PII
	nvl(onhand.subinventory_code, ml1.meaning) Subinventory_or_Intransit,
	-- Revision for version 1.19
	nvl(regexp_replace(msub.description,'[^[:alnum:]'' '']', null), ml1.meaning) Description,
	-- Revision for version 1.18
	ml2.meaning Asset,
	-- Revision for version 1.16
	muomv.uom_code UOM_Code,
	round(nvl(onhand.rollback_quantity,0),3) Onhand_Quantity,
	-- Use the Cost Type Costs instead of the rollback_value
	round(nvl(onhand.rollback_quantity,0) * nvl(cic.item_cost,0),2) Onhand_Value,
	-- Revision for version 1.17 PII
	round(nvl(onhand.rollback_quantity,0) * nvl(pii.pii_item_cost,0),2) PII_Onhand_Value,
	round(nvl(onhand.rollback_quantity,0) * nvl(cic.item_cost,0),2) -
		round(nvl(onhand.rollback_quantity,0) * nvl(pii.pii_item_cost,0) * decode(sign(:p_sign_pii),1,1,-1,-1,1),2) Net_Onhand_Value
	-- End revision for version 1.17 PII
from	inv_organizations mp,
	valuation_accounts va,
	-- Revision for version 1.16
	mtl_units_of_measure_vl muomv,
	mtl_item_status_vl misv,
	-- End revision for version 1.16
	-- Revision for version 1.17 PII
	pii,
	-- Revision for version 1.12
	cst_cost_types cct,
	cst_item_costs cic,
	-- End revision for version 1.12
	gl_code_combinations gcc,
	-- Revision for version 1.13
	fnd_common_lookups fcl, -- Item Type
	-- Revision for version 1.17
	mfg_lookups ml1, -- Intransit
	-- Revision for version 1.18
	mfg_lookups ml2, -- Inventory Asset Flag
	-- Revision for version 1.19
	mtl_secondary_inventories msub,
	-- Inner query for onhand quantities and values
	(-- For non-category accounting
	 select	mp.organization_id,
		msiv.inventory_item_id,
		msiv.concatenated_segments,
		regexp_replace(msiv.description,'[^[:alnum:]'' '']', null) description,
		msiv.primary_uom_code,
		msiv.inventory_item_status_code,
		msiv.item_type,
		msiv.inventory_asset_flag,
		oap.period_name,
		cpcs.acct_period_id,
		nvl(cpcs.subinventory_code, 'Intransit') subinventory_code,
		sum(cpcs.rollback_quantity) rollback_quantity,
		sum(cpcs.rollback_value) rollback_value,
		sum(cpcs.rollback_intransit_value) rollback_intransit_value		
	 from	mtl_system_items_vl msiv,
		cst_period_close_summary cpcs,
		org_acct_periods oap,
		inv_organizations mp
	 where	mp.organization_id              = msiv.organization_id
	 and	oap.acct_period_id              = cpcs.acct_period_id
	 and	oap.organization_id             = mp.organization_id
	 and	msiv.organization_id            = cpcs.organization_id
	 and	msiv.inventory_item_id          = cpcs.inventory_item_id
	 and	mp.category_organization_id is null
	 -- Don't report zero quantities
	 and	nvl(cpcs.rollback_quantity,0)  <> 0
	 -- Don't report expense items
	 and	msiv.inventory_asset_flag       = 'Y'
	 and	4=4                             -- p_period_name, p_item_number
	 -- Need to group by due to possibility for having multiple cost groups by subinventory
	 group by
		mp.organization_id,
		msiv.inventory_item_id,
		msiv.concatenated_segments,
		regexp_replace(msiv.description,'[^[:alnum:]'' '']', null),
		msiv.primary_uom_code,
		msiv.inventory_item_status_code,
		msiv.item_type,
		msiv.inventory_asset_flag,
		oap.period_name,
		cpcs.acct_period_id,
		cpcs.subinventory_code
	) onhand
	-- End revision for version 1.19
-- ========================================================================
-- Subinventory, mtl parameter, item master and period close snapshot joins
-- ========================================================================
where	mp.organization_id              = onhand.organization_id
and	muomv.uom_code                  = onhand.primary_uom_code
and	misv.inventory_item_status_code = onhand.inventory_item_status_code
and	mp.category_organization_id is null
-- Revision for version 1.17 PII
and	pii.organization_id (+)         = onhand.organization_id
and	pii.inventory_item_id (+)       = onhand.inventory_item_id
-- Revision for version 1.19
and	onhand.subinventory_code        = msub.secondary_inventory_name (+)
and	onhand.organization_id          = msub.organization_id (+)
-- ===========================================
-- Accounting code combination joins
-- ===========================================
-- Revision for version 1.18
-- and	msub.material_account           = gcc.code_combination_id (+)
and	va.material_account             = gcc.code_combination_id (+)
and	va.secondary_inventory_name (+) = onhand.subinventory_code
and	va.organization_id (+)          = onhand.organization_id
and	va.valuation_type              <> 'Category Accounting'
-- End revision for version 1.18
-- ===========================================
-- Cost Type Joins
-- Revision for version 1.12
-- ===========================================
and	5=5                             -- p_cost_type
and	cct.cost_type_id                = cic.cost_type_id
-- Revision for version 1.16
and	cic.organization_id             = onhand.organization_id
and	cic.inventory_item_id           = onhand.inventory_item_id
-- End for revision for version 1.16
-- ===========================================
-- Lookup Codes
-- ===========================================
-- Revision for version 1.13
and	fcl.lookup_code (+)             = onhand.item_type
and	fcl.lookup_type (+)             = 'ITEM_TYPE'
-- Revision for version 1.16
and	ml1.lookup_code                 = 3 -- Intransit
and	ml1.lookup_type                 = 'MSC_CALENDAR_TYPE'
-- Revision for version 1.19
and	ml2.lookup_code                 = nvl(msub.asset_inventory,1)
and	ml2.lookup_type                 = 'SYS_YES_NO'
-- ===========================================
-- 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,            -- p_cost_type
		null, 'do not use snapshot values', 
		'use cost type values') =  'use cost type values'
union all
-- =======================================================================
-- Section III.  For category accounting, get period-end quantities and
--               values based solely on the month-end inventory snapshot.
-- =======================================================================
select	mp.ledger Ledger,
	mp.operating_unit Operating_Unit,
	mp.organization_code Org_Code,
	onhand.period_name Period_Name,
	&segment_columns
	onhand.concatenated_segments Item_Number,
	onhand.description Item_Description,
	-- Revision for version 1.13
	-- flv.meaning Item_Type,
	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
&category_columns
	mp.currency_code Currency_Code,
	decode(onhand.subinventory_code,
			null, round(nvl(onhand.rollback_intransit_value,0) /
				decode(nvl(onhand.rollback_quantity,0), 0, 1,
				nvl(onhand.rollback_quantity,0)),5),
			round((nvl(onhand.rollback_value,0)) /
				decode(nvl(onhand.rollback_quantity,0), 0, 1,
				nvl(onhand.rollback_quantity,0)),5)
	      ) Gross_Item_Cost,
	-- Revision for version 1.17 PII
	nvl(pii.pii_item_cost,0) PII_Item_Cost,
	decode(onhand.subinventory_code,
			null, round(nvl(onhand.rollback_intransit_value,0) /
				decode(nvl(onhand.rollback_quantity,0), 0, 1,
				nvl(onhand.rollback_quantity,0)),5),
			round((nvl(onhand.rollback_value,0)) /
				decode(nvl(onhand.rollback_quantity,0), 0, 1,
				nvl(onhand.rollback_quantity,0)),5)
	      ) - nvl(pii.pii_item_cost,0) * decode(sign(:p_sign_pii),1,1,-1,-1,1) Net_Item_Cost,
	-- End revision for version 1.17 PII
	nvl(onhand.subinventory_code, ml1.meaning) Subinventory_or_Intransit,
	-- Revision for version 1.19
	nvl(regexp_replace(msub.description,'[^[:alnum:]'' '']', null), ml1.meaning) Description,
	-- Revision for version 1.18
	ml2.meaning Asset,
	-- Revision for version 1.16
	muomv.uom_code UOM_Code,
	round(nvl(onhand.rollback_quantity,0),3) Onhand_Quantity,
	decode(onhand.subinventory_code,
		null, round(nvl(onhand.rollback_intransit_value,0),2),
		round(nvl(onhand.rollback_value,0),2)
	      ) Onhand_Value,
	-- Revision for version 1.17 PII
	round(nvl(onhand.rollback_quantity,0) * nvl(pii.pii_item_cost,0),2) PII_Onhand_Value,
	round(decode(onhand.subinventory_code,
			null, nvl(onhand.rollback_intransit_value,0),
			nvl(onhand.rollback_value,0)
		    ) - round(nvl(onhand.rollback_quantity,0) * nvl(pii.pii_item_cost,0),2) *
			decode(sign(:p_sign_pii),1,1,-1,-1,1),2) Net_Onhand_Value
	-- End revision for version 1.17 PII
from	inv_organizations mp,
	valuation_accounts va,
	-- Revision for version 1.16
	mtl_units_of_measure_vl muomv,
	mtl_item_status_vl misv,
	-- End revision for version 1.16
	-- Revision for version 1.17 PII
	pii,
	gl_code_combinations gcc,
	fnd_common_lookups fcl, -- Item Type
	mfg_lookups ml1, -- Intransit
	-- Revision for version 1.18
	mfg_lookups ml2, -- Inventory Asset
	-- Revision for version 1.19
	mtl_secondary_inventories msub,
	(-- This onhand inner query is for category accounting
	 select	onhand2.organization_id,
		onhand2.category_organization_id,
		onhand2.category_set_id,
		mic.category_id,
		onhand2.inventory_item_id,
		onhand2.concatenated_segments,
		onhand2.description,
		onhand2.primary_uom_code,
		onhand2.inventory_item_status_code,
		onhand2.item_type,
		onhand2.period_name,
		onhand2.subinventory_code,
		sum(onhand2.rollback_quantity) rollback_quantity,
		sum(onhand2.rollback_value) rollback_value,
		sum(onhand2.rollback_intransit_value) rollback_intransit_value		
	 from	mtl_item_categories mic,
		-- Inner select to have consistent outer joins with categories
		(select	mp.organization_id,
			mp.category_set_id,
			mp.category_organization_id,
			msiv.inventory_item_id,
			msiv.concatenated_segments,
			regexp_replace(msiv.description,'[^[:alnum:]'' '']', null) description,
			msiv.primary_uom_code,
 			msiv.inventory_item_status_code,
			msiv.item_type,
			oap.period_name,
			nvl(cpcs.subinventory_code, 'Intransit') subinventory_code,
			sum(cpcs.rollback_quantity) rollback_quantity,
			sum(cpcs.rollback_value) rollback_value,
			sum(cpcs.rollback_intransit_value) rollback_intransit_value
		 from	mtl_system_items_vl msiv,
			cst_period_close_summary cpcs,
			org_acct_periods oap,
			inv_organizations mp
		 where	mp.organization_id              = msiv.organization_id
		 and	mp.category_organization_id is not null
		 and	oap.organization_id             = mp.organization_id
		 and	oap.acct_period_id              = cpcs.acct_period_id
		 and	cpcs.organization_id            = msiv.organization_id
		 and	cpcs.inventory_item_id          = msiv.inventory_item_id
		 -- Don't get zero quantities
		 and	nvl(cpcs.rollback_quantity,0)  <> 0
		 and	4=4                             -- p_period_name, p_item_number
		 group by
			mp.organization_id,
			mp.category_set_id,
			mp.category_organization_id,
			msiv.inventory_item_id,
			msiv.concatenated_segments,
			regexp_replace(msiv.description,'[^[:alnum:]'' '']', null),
			msiv.primary_uom_code,
 			msiv.inventory_item_status_code,
			msiv.item_type,
			oap.period_name,
			nvl(cpcs.subinventory_code, 'Intransit') -- subinventory_code
		) onhand2
	 where	mic.inventory_item_id (+)       = onhand2.inventory_item_id
	 and	mic.organization_id (+)         = onhand2.organization_id
	 and	mic.category_set_id (+)         = onhand2.category_set_id
	 -- Need to group by due to possibility for having multiple cost groups by subinventory
	 group by
		onhand2.organization_id,
		onhand2.category_organization_id,
		onhand2.category_set_id,
		mic.category_id,
		onhand2.inventory_item_id,
		onhand2.concatenated_segments,
		onhand2.description,
		onhand2.primary_uom_code,
		onhand2.inventory_item_status_code,
		onhand2.item_type,
		onhand2.period_name,
		onhand2.subinventory_code
	) onhand
	-- End revision for version 1.19
-- ========================================================================
-- Subinventory, mtl parameter, item master and period close snapshot joins
-- ========================================================================
where	mp.organization_id              = onhand.organization_id
and	muomv.uom_code                  = onhand.primary_uom_code
and	misv.inventory_item_status_code = onhand.inventory_item_status_code
and	mp.category_organization_id is not null
-- Revision for version 1.17 PII
and	pii.organization_id (+)         = onhand.organization_id
and	pii.inventory_item_id (+)       = onhand.inventory_item_id
-- Revision for version 1.19
and	onhand.subinventory_code        = msub.secondary_inventory_name (+)
and	onhand.organization_id          = msub.organization_id (+)
-- End revision for version 1.19
-- ===========================================
-- Accounting code combination joins
-- ===========================================
-- Revision for version 1.19
and	va.material_account             = gcc.code_combination_id (+)
and	va.secondary_inventory_name (+) = onhand.subinventory_code 
and	va.organization_id (+)          = onhand.organization_id
and	va.category_set_id (+)          = onhand.category_set_id
and	va.category_id (+)              = onhand.category_id
and	va.valuation_type (+)           = 'Category Accounting'
-- End revision for version 1.19
-- ===========================================
-- Lookup Codes
-- ===========================================
-- Revision for version 1.13
and	fcl.lookup_code (+)             = onhand.item_type
and	fcl.lookup_type (+)             = 'ITEM_TYPE'
-- Revision for version 1.16
and	ml1.lookup_code                 = 3 -- Intransit
and	ml1.lookup_type                 = 'MSC_CALENDAR_TYPE'
-- Revision for version 1.19
and	ml2.lookup_code                 = nvl(msub.asset_inventory,1)
and	ml2.lookup_type                 = 'SYS_YES_NO'
-- ===========================================
-- 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'
union all
-- =======================================================================
-- Section IV. For category accounting, get period-end quantities from the
--             month-end snapshot but get item costs from entered cost type.
-- =======================================================================
select	mp.ledger Ledger,
	mp.operating_unit Operating_Unit,
	mp.organization_code Org_Code,
	onhand.period_name Period_Name,
	&segment_columns
	onhand.concatenated_segments Item_Number,
	onhand.description Item_Description,
	-- Revision for version 1.13
	-- flv.meaning Item_Type,
	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
&category_columns
	mp.currency_code Currency_Code,
	round(nvl(cic.item_cost,0),5) Gross_Item_Cost,
	-- Revision for version 1.17 PII
	nvl(pii.pii_item_cost,0) PII_Item_Cost,
	round(nvl(cic.item_cost,0),5) - nvl(pii.pii_item_cost,0) * decode(sign(:p_sign_pii),1,1,-1,-1,1) Net_Item_Cost,
	-- End revision for version 1.17 PII
	nvl(onhand.subinventory_code, ml1.meaning) Subinventory_or_Intransit,
	-- Revision for version 1.19
	nvl(regexp_replace(msub.description,'[^[:alnum:]'' '']', null), ml1.meaning) Description,
	-- Revision for version 1.18
	ml2.meaning Asset,
	-- Revision for version 1.16
	muomv.uom_code UOM_Code,
	round(nvl(onhand.rollback_quantity,0),3) Onhand_Quantity,
	-- Use the Cost Type Costs instead of the rollback_value
	round(nvl(onhand.rollback_quantity,0) * nvl(cic.item_cost,0),2) Onhand_Value,
	-- Revision for version 1.17 PII
	round(nvl(onhand.rollback_quantity,0) * nvl(pii.pii_item_cost,0),2) PII_Onhand_Value,
	round(nvl(onhand.rollback_quantity,0) * nvl(cic.item_cost,0),2) -
Parameter Name SQL text Validation
Period Name (Closed) 
oap.period_name = :p_period_name
 LOV
Cost Type 
cct.cost_type = :p_cost_type
 LOV
PII Cost Type 
cct.cost_type = :p_pii_cost_type
 LOV
PII Sub-Element 
br.resource_code = :p_pii_sub_element
 LOV
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 onhand.organization_id=mic.organization_id and onhand.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 onhand.organization_id=mic.organization_id and onhand.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 onhand.organization_id=mic.organization_id and onhand.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 onhand.organization_id=mic.organization_id and onhand.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 
msiv.concatenated_segments = :p_item_number
 LOV
Subinventory 
msub.secondary_inventory_name =:p_subinventory
 LOV
Organization Code 
mp.organization_code = :p_org_code
 LOV
Operating Unit 
haou2.name = :p_operating_unit
 LOV
Ledger 
gl.name =