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 ...
Report showing 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 ...  Report showing 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,
&category_columns
	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,
&category_columns
	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,
&category_columns
	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)) /
		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,
	-- Revision for version 1.12
	-- Added for valuing based on the item costs
	cic.item_cost
union all
-- =======================================================================
-- The second select gets the period-end quantities from intransit
-- and the item costs from the entered cost type.
-- =======================================================================
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,
&category_columns
	gl.currency_code Curr_Code,
	round(nvl(cic.item_cost,0),5) Item_Cost,
	-- Revision for version 1.16
	ml.meaning   Subinventory_or_Intransit,
	muomv.uom_code UOM_Code,
	round(sum(nvl(cpcs.rollback_quantity,0)),3) Quantity,
	-- Use the item costs from the Cost Type
	-- to value the Intransit inventory
	-- sum(nvl(cpcs.rollback_intransit_value,0)) Onhand_Value,
	round(sum(nvl(cic.item_cost,0) * nvl(cpcs.rollback_quantity,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,
	mfg_lookups ml,
	-- End Revision for version 1.16
	-- 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,
	fnd_common_lookups fcl,
	-- Revision for version 1.10
	-- Need to get the Intransit Account from the Shipping Network
	-- as the inventory parameters Intransit Account is not always populated
	(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	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	gcc.code_combination_id (+) = mip.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
-- ===========================================
-- 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'
-- 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 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_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,
	-- Revision for version 1.12
	-- Added for valuing based on the item costs
	cic.item_cost
-- Order by Ledger, Operating_Unit, Org_Code, Accounts, Item_Number, Subinventory
order by 1,2,3,5,6,7,8,9,10,11,12,15
Parameter NameSQL textValidation
Chart of Accounts
select x.* from
(
select
'gcc.segment'||regexp_substr(fifs.application_column_name,'.+(\d+)',1,1,null,1)||' "'||fifst.form_left_prompt||'",' text
from
fnd_id_flex_segments fifs,
fnd_id_flex_segments_tl fifst
where
fifs.application_id=101 and
fifs.id_flex_code='GL#' and
fifs.id_flex_num=(select fifsv.id_flex_num from fnd_id_flex_structures_vl fifsv where fifsv.application_id=101 and fifsv.id_flex_code='GL#' and fifsv.id_flex_structure_name=:chart_of_accounts) and
fifs.application_id=fifst.application_id and
fifs.id_flex_code=fifst.id_flex_code and
fifs.id_flex_num=fifst.id_flex_num and
fifs.application_column_name=fifst.application_column_name and
fifst.language=userenv('lang')
order by
fifs.segment_num
) x
LOV
Ledger
gl.name = :p_ledger
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 msiv.organization_id=mic.organization_id and msiv.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 msiv.organization_id=mic.organization_id and msiv.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 msiv.organization_id=mic.organization_id and msiv.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 msiv.organization_id=mic.organization_id and msiv.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
Cost Type
cct.cost_type = :p_cost_type
LOV
Item Number
msiv.concatenated_segments = :p_item_number
LOV
Period Name (Closed)
oap.period_name = :p_period_name
LOV
Operating Unit
haou2.name = :p_operating_unit
LOV
Organization Code
mp.organization_code = :p_org_code
LOV
Chart of Accounts
with x as (
select
fifs.application_column_name,
fifst.form_left_prompt
from
fnd_id_flex_segments fifs,
fnd_id_flex_segments_tl fifst
where
fifs.application_id=101 and
fifs.id_flex_code='GL#' and
fifs.id_flex_num=(select fifsv.id_flex_num from fnd_id_flex_structures_vl fifsv where fifsv.application_id=101 and fifsv.id_flex_code='GL#' and fifsv.id_flex_structure_name=:chart_of_accounts) and
fifs.application_id=fifst.application_id and
fifs.id_flex_code=fifst.id_flex_code and
fifs.id_flex_num=fifst.id_flex_num and
fifs.application_column_name=fifst.application_column_name and
fifst.language=userenv('lang')
order by
fifs.segment_num
)
select 'gcc.'||lower(x.application_column_name)||',' text from x
Cost Type
<parameter_value>
Download
 
Blitz Report In Action
Blitz Report™