CAC Inventory Pending Cost Adjustment

➜ Back to Library
Description
Categories: Enginatics
Repository: Github
Report showing the potential standard cost changes for onhand and intransit inventory value which you own. If you enter a period name this report uses the quantities from the month-end snapshot; if you leave the period name blank it uses the real-time quantities. The Cost Type (Old) defaults to your Costing Method Cost Type (Average, Standard, etc.); the Currency Conversion Dates default to the  ...  Report showing the potential standard cost changes for onhand and intransit inventory value which you own. If you enter a period name this report uses the quantities from the month-end snapshot; if you leave the period name blank it uses the real-time quantities. The Cost Type (Old) defaults to your Costing Method Cost Type (Average, Standard, etc.); the Currency Conversion Dates default to the latest open or closed accounting period; and the To Currency Code and the Organization Code default from the organization code set for this session. And if you want to enter a period name to use the quantities from the month-end snapshot, you can only choose closed accounting periods; this is because the month-end snapshot is created when you close the inventory accounting period.

/* +=============================================================================+
-- | Copyright 2008-2022 Douglas Volz Consulting, Inc. |
-- | All rights reserved. |
-- +=============================================================================+
-- |
-- | Original Author: Douglas Volz ([email protected])
-- |
-- | Parameters:
-- | p_cost_type1 -- The new cost type to be reported, mandatory
-- | p_cost_type2 -- The old cost type to be reported, mandatory
-- | p_curr_conv_date1 -- the new currency conversion date, mandatory
-- | p_curr_conv_date2 -- the old currency conversion date, mandatory
-- | p_curr_conv_type1 -- the desired currency conversion type to use for cost type 1, mandatory
-- | p_curr_conv_type2 -- the desired currency conversion type to use for cost type 2, mandatory
-- | p_to_currency_code -- the currency you are converting into
-- | p_period_name -- Enter a Period Name to use the month-end snapshot; if no
-- | period name is entered will use the real-time quantities
-- | p_category_set1 -- The first item category set to report, typically the
-- | Cost or Product Line Category Set
-- | p_category_set2 -- The second item category set to report, typically the
-- | Inventory Category Set
-- | p_include_zero_quantities -- Include items with no onhand or no intransit quantities
-- | p_only_items_in_cost_type -- Only report items in the New Cost Type
-- | p_item_number -- specific item number to report, works with
-- | null or valid item numbers
-- | 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)
-- | Version Modified on Modified by Description
-- | ======= =========== ============== =========================================
-- | 1.0 21 Nov 2010 Douglas Volz Created initial Report for prior client
-- | based on BBCI_INV_VALUE_STD_ADJ_FX_REPT1.7.sql
-- | 1.9 6 Dec 2020 Douglas Volz Fixed logic for Percentage Difference
-- | 1.10 15 Dec 2021 Douglas Volz Added parameter to include zero onhand quantities
-- | 1.11 06 Jun 2022 Douglas Volz Fix for category accounts (valuation accounts) and
-- | added subinventory description.
-- +=============================================================================+*/
   more
Run CAC Inventory Pending Cost Adjustment and other Oracle EBS reports with Blitz Report™ on our demo environment
with inv_organizations as
-- Revision for version 1.11
-- 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,
		haou.name organization_name,
		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,
		-- Revision for version 1.11
		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	9=9                             -- 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,
		haou.name, -- organization_name
		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,
		-- Revision for version 1.11
		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.11
 
----------------main query starts here--------------
 
-- ====================================================
-- Select operating unit and organization information
-- ====================================================
 
-- Revision for version 1.11
select	mp.ledger							Ledger,
	mp.operating_unit						Operating_Unit,
	mp.organization_code						Org_Code,
	mp.organization_name						Organization_Name,
-- End revision for version 1.11
	:p_period_name							Period_Name,
	&segment_columns
	msiv.concatenated_segments					Item_Number,
	msiv.description						Item_Description,
	fcl.meaning							Item_Type,
	-- Revision for version 1.7
	misv.inventory_item_status_code_tl				Item_Status,
	ml1.meaning							Make_Buy_Code,
        -- Revision for version 1.5
&category_columns
	-- Revision for version 1.11
	mp.currency_code						Currency_Code,
-- ==========================================================
-- Select the new and old item costs from Cost Type 1 and 2
-- ==========================================================
	round(nvl(cic1.material_cost,0),5)				New_Material_Cost,
	round(nvl(cic2.material_cost,0),5)				Old_Material_Cost,
	round(nvl(cic1.material_overhead_cost,0),5)			New_Material_Overhead_Cost,
	round(nvl(cic2.material_overhead_cost,0),5)			Old_Material_Overhead_Cost,
	round(nvl(cic1.resource_cost,0),5)				New_Resource_Cost,
	round(nvl(cic2.resource_cost,0),5)				Old_Resource_Cost,
	round(nvl(cic1.outside_processing_cost,0),5)			New_Outside_Processing_Cost,
	round(nvl(cic2.outside_processing_cost,0),5)			Old_Outside_Processing_Cost,
	round(nvl(cic1.overhead_cost,0),5)				New_Overhead_Cost,
	round(nvl(cic2.overhead_cost,0),5)				Old_Overhead_Cost,
	round(nvl(cic1.item_cost,0),5)					New_Item_Cost,
	round(nvl(cic2.item_cost,0),5)					Old_Item_Cost,
-- ========================================================
-- Select the item costs from Cost Type 1 and 2 and compare
-- ========================================================
	round(nvl(cic1.item_cost,0),5) - round(nvl(cic2.item_cost,0),5)	Item_Cost_Difference,
	-- Revision for version 1.9
	-- round((nvl(cic1.item_cost,0) -nvl(cic2.item_cost,0))
	--     /
 	-- (decode(nvl(cic2.item_cost,0),0,1,cic2.item_cost)) * 100,1)	Percent_Difference,
	case
	  when round((nvl(cic1.item_cost,0) - nvl(cic2.item_cost,0)),5) = 0 then 0
	  when round((nvl(cic1.item_cost,0) - nvl(cic2.item_cost,0)),5) = round(nvl(cic1.item_cost,0),5) then  100
	  when round((nvl(cic1.item_cost,0) - nvl(cic2.item_cost,0)),5) = round(nvl(cic2.item_cost,0),5) then -100
	  else round((nvl(cic1.item_cost,0) - nvl(cic2.item_cost,0)) / nvl(cic2.item_cost,0) * 100,1)
	end								Percent_Difference,
	-- End revision for version 1.9
-- ===========================================================
-- Select the onhand and intransit quantities and values
-- ===========================================================
	sumqty.subinventory_code					Subinventory_or_Intransit,
	-- Revision for version 1.11
	sumqty.subinv_description					Subinventory_Description,
	-- Revision for version 1.7
	-- msiv.primary_uom_code						UOM_Code,
	muomv.uom_code							UOM_Code,
	-- End revision for version 1.7
	nvl(sumqty.onhand_quantity,0) + nvl(sumqty.intransit_quantity,0)		Onhand_Quantity,
	round(nvl(cic1.item_cost,0) * 
		(nvl(sumqty.intransit_quantity,0) + nvl(sumqty.onhand_quantity,0)),2)	New_Onhand_Value,
	round(nvl(cic2.item_cost,0) * 
		(nvl(sumqty.intransit_quantity,0) + nvl(sumqty.onhand_quantity,0)),2)	Old_Onhand_Value,
	round((nvl(cic1.item_cost,0) - nvl(cic2.item_cost,0)) * 
		(nvl(sumqty.intransit_quantity,0) + nvl(sumqty.onhand_quantity,0)),2)	Onhand_Value_Difference,
-- ========================================================
-- Select the new and old currency rates
-- ========================================================
	gdr1.conversion_rate						New_FX_Rate,
	gdr2.conversion_rate						Old_FX_Rate,
	gdr1.conversion_rate - gdr2.conversion_rate			Exchange_Rate_Difference,
-- ===========================================================
-- Select To Currency onhand and intransit quantities and values
-- ===========================================================
-- ===========================================================
-- Costs in To Currency by Cost Element, new values at new Fx rate
-- old values at old Fx rate
-- ===========================================================
	round(nvl(cic1.material_cost,0) * gdr1.conversion_rate
	* (nvl(sumqty.onhand_quantity,0) + nvl(sumqty.intransit_quantity,0)),2)	"&p_to_currency_code New Material Value",
	round(nvl(cic2.material_cost,0) * gdr2.conversion_rate
	* (nvl(sumqty.onhand_quantity,0) + nvl(sumqty.intransit_quantity,0)),2)	"&p_to_currency_code Old Material Value",
	round(nvl(cic1.material_overhead_cost,0) * gdr1.conversion_rate	
	* (nvl(sumqty.onhand_quantity,0) + nvl(sumqty.intransit_quantity,0)),2)	"&p_to_currency_code New Material Ovhd Value",
	round(nvl(cic2.material_overhead_cost,0) * gdr2.conversion_rate
	* (nvl(sumqty.onhand_quantity,0) + nvl(sumqty.intransit_quantity,0)),2)	"&p_to_currency_code Old Material Ovhd Value",
	round(nvl(cic1.resource_cost,0) * gdr1.conversion_rate
	* (nvl(sumqty.onhand_quantity,0) + nvl(sumqty.intransit_quantity,0)),2)	"&p_to_currency_code New Resource Value",
	round(nvl(cic2.resource_cost,0) * gdr2.conversion_rate
	* (nvl(sumqty.onhand_quantity,0) + nvl(sumqty.intransit_quantity,0)),2)	"&p_to_currency_code Old Resource Value",
	round(nvl(cic1.outside_processing_cost,0) * gdr1.conversion_rate
	* (nvl(sumqty.onhand_quantity,0) + nvl(sumqty.intransit_quantity,0)),2)	"&p_to_currency_code New OSP Value",
	round(nvl(cic2.outside_processing_cost,0) * gdr2.conversion_rate
	* (