CAC Cost vs. Planning Item Controls

➜ Back to Library
Description
Categories: Cost Accounting - Assessment, Cost Accounting - Cost, Enginatics, Toolkit - Cost Accounting
Repository: Github
Compare item make/buy controls vs. costing based on rollup controls. There are eleven included reports, see below description for more information.

/* +=============================================================================+
-- | Copyright 2008-2021 Douglas Volz Consulting, Inc. |
-- | All rights reserved.  ...  Compare item make/buy controls vs. costing based on rollup controls. There are eleven included reports, see below description for more information.

/* +=============================================================================+
-- | Copyright 2008-2021 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. |
-- +=============================================================================+
-- |
-- | Description:
-- | Use the below SQL scripts to compare the item costing rollup flags
-- | with the item's make / buy flag. For the any Cost_Type.
-- | Report to show cost rollup flags which may be incorrect:
-- | 1. Based on Rollup Yes - No BOMS
-- | Find make items where the item is set to be rolled up
-- | but there are no BOMs. May roll up to a zero cost.
-- | 2. Based on Rollup Yes - No Routing
-- | Find make items costs are based on the cost rollup, but there are no routings.
-- | 3. Based on Rollup Yes - No Rollup
-- | Find make items where it is set to be rolled up but there are
-- | no rolled up costs
-- | 4. Based on Rollup Yes - Buy Items
-- | Find buy items where the item is set to rolled up
-- | 5. Based on Rollup No - With BOMS
-- | Find make items where the item is not set to be rolled up
-- | but BOMS or routings exist.
-- | 6. Based on Rollup No - With Sourcing Rules
-- | Find buy items where costs are not based on the cost rollup, but
-- | sourcing rules exist.
-- | 7. Based on Rollup No - Make Items
-- | Find make items where the item is not set to rolled up, whether
-- | or not BOMs or routings exist.
-- | 8. Lot-Based Resources With Lot Size One
-- | Find make items where there are charges based on Lot but the lot
-- | size is one. Duplicates the setup charges for each item you make.
-- | 9. BOMs With No Components
-- | Find make items with BOMS that have no components.
-- | 10. Item Costing vs. Item Asset Controls
-- | Find items where the item master costed flag (costed enabled) and
-- | the item asset flag do not match.
-- | 11. Item Asset vs. Costing Asset Controls
-- | Find items where the item master asset and the costing asset flags do not match.
-- |
-- | Version Modified on Modified by Description
-- | ======= =========== ============== =========================================
-- | 1.0 15 Oct 2008 Douglas Volz Initial Coding
-- | 1.33 05 May 2021 Douglas Volz Remove the 'Item vs BOM vs Cost Controls' and replace with
-- | 'Item_Costed vs Item Asset' and Item Asset vs. Costing Asset'
-- | report types. The check for the item master item default include
-- | in rollup, and BOM component include in rollup do not match has
-- | been removed to a separate report, as this is a lengthy query.
-- | 1.34 07 May 2021 Douglas Volz Using with statement, summarized report type queries for efficiency.
+=============================================================================+*/   more 
with rept as
	(select mp.organization_code,
		cct.cost_type,
		cct.cost_type_id,
		msiv.concatenated_segments,
		msiv.description,
		muomv.uom_code,
		msiv.item_type,
		misv.inventory_item_status_code_tl,
		msiv.planning_make_buy_code,
		msiv.inventory_item_id,
		msiv.organization_id,
		-- check to see if a bom exists
		nvl((select	distinct 'Y'
		     from	bom_structures_b bom
		     where	bom.organization_id     = mp.organization_id
		     and	bom.assembly_item_id    = msiv.inventory_item_id
		     and	bom.alternate_bom_designator is null),
		'N') BOM,
		-- check to see if a routing exists
		nvl((select	distinct 'Y'
		     from	bom_operational_routings bor
		     where	bor.organization_id     = mp.organization_id
		     and	bor.assembly_item_id    = msiv.inventory_item_id
		     and	bor.alternate_routing_designator is null),
		'N') Routing,
		 -- check to see if a sourcing rule exists for the receipt org
		nvl((select	distinct 'Y'
		     from	mrp_sr_receipt_org msro,
				mrp_sr_source_org msso,
				mrp_sourcing_rules msr,
				mrp_sr_assignments msa,
				mrp_assignment_sets mas
		     where	msr.sourcing_rule_id    = msro.sourcing_rule_id
		     -- fix for version 1.4, check to see if the sourcing rule is
		     -- for an inventory org, not a vendor
		     and	msso.sr_receipt_id      = msro.sr_receipt_id
		     and	msso.source_organization_id is not null
		     and	msa.sourcing_rule_id    = msr.sourcing_rule_id
		     and	msa.assignment_set_id   = mas.assignment_set_id
		     and	msiv.organization_id    = msa.organization_id
		     and	msiv.inventory_item_id  = msa.inventory_item_id
		     and	3=3                     -- p_assignment_set
		     and	mp.organization_id      = msa.organization_id),'N') Sourcing_Rule,
		cic.based_on_rollup_flag,
		msiv.costing_enabled_flag,
		msiv.inventory_asset_flag,
		to_char(cic.inventory_asset_flag) cost_asset_flag,
		msiv.std_lot_size,
		cic.lot_size cost_lot_size,
		cic.item_cost,
		msiv.creation_date
	 from	mtl_system_items_vl msiv,
		mtl_units_of_measure_vl muomv,
		mtl_item_status_vl misv, 
		cst_item_costs cic,
		cst_cost_types cct,
		mtl_parameters mp
	 -- ===================================================================
	 -- Cost type, organization, item master and report specific controls
	 -- ===================================================================
	 where	cic.cost_type_id                = cct.cost_type_id
	 and	mp.organization_id              = cic.organization_id
	 and	msiv.organization_id            = cic.organization_id
	 and	msiv.inventory_item_id          = cic.inventory_item_id
	 and	msiv.primary_uom_code           = muomv.uom_code
	 and	misv.inventory_item_status_code = msiv.inventory_item_status_code
	 and	msiv.inventory_item_status_code <> 'Inactive'
	 -- Avoid unused inventory organizations
	 and	mp.organization_id             <> mp.master_organization_id -- the item master org usually does not have costs
	 and	2=2                             -- p_cost_type, p_item_number, p_org_code
	)
select	rept_all.report_type,
	nvl(gl.short_name, gl.name) Ledger,
	haou2.name Operating_Unit,
	rept_all.organization_code Org_Code,
	rept_all.cost_type Cost_Type,
	rept_all.concatenated_segments Item_Number,
	rept_all.description Item_Description,
	rept_all.uom_code UOM_Code,
	fcl.meaning Item_Type,
	rept_all.inventory_item_status_code_tl Item_Status,
	ml1.meaning Make_Buy_Code,
&category_columns
	fl1.meaning BOM,
	fl2.meaning Routing,
	fl3.meaning Sourcing_Rule,							-- p_assignment_set
	ml2.meaning Based_on_Rollup,
	fl4.meaning Costing_Enabled,
	fl5.meaning Item_Inventory_Asset,
	ml3.meaning Cost_Inventory_Asset,
	rept_all.std_lot_size Item_Std_Lot_Size,
	rept_all.cost_lot_size Cost_Lot_Size,
	gl.currency_code Currency_Code,
	rept_all.Item_Cost,
	rept_all.creation_date Item_Creation_Date
from	mfg_lookups ml1, -- Make/buy code, MTL_PLANNING_MAKE_BUY
	mfg_lookups ml2, -- based on rollup, CST_BONROLLUP_VAL
	mfg_lookups ml3, -- Cost inventory_asset_flag, SYS_YES_NO
	fnd_lookups fl1, -- BOM, YES_NO
	fnd_lookups fl2, -- Routing, YES_NO
	fnd_lookups fl3, -- Sourcing_Rule, YES_NO
	fnd_lookups fl4, -- Item costing enabled, YES_NO
	fnd_lookups fl5, -- Item inventory asset, YES_NO
	fnd_common_lookups fcl, -- Item Type
	hr_organization_information hoi,
	hr_all_organization_units_vl haou,  -- inv_organization_id
	hr_all_organization_units_vl haou2, -- operating unit
	gl_ledgers gl,
	(
	 -- ===============================================
	 -- Report 1 - 'Based on Rollup Yes - No BOMs'
	 -- ===============================================
	 select	'Based on Rollup Yes - No BOMs' report_type, rept.* from rept where rept.costing_enabled_flag = 'Y' and rept.planning_make_buy_code = 1 and rept.based_on_rollup_flag = 1 and rept.bom = 'N' 
	 union all
	 -- ===============================================
	 -- Report 2 - 'Based on Rollup Yes - No Routing'
	 -- ===============================================
	 select 'Based on Rollup Yes - No Routing' report_type, rept.* from rept where rept.costing_enabled_flag = 'Y' and rept.planning_make_buy_code = 1 and rept.based_on_rollup_flag = 1 and rept.routing = 'N'
	 union all
	 -- ===============================================
	 -- Report 3 - 'Based on Rollup Yes - No Rollup'
	 -- ===============================================
	 select 'Based on Rollup Yes - No Rollup' report_type, rept.* from rept where rept.costing_enabled_flag = 'Y' and (rept.planning_make_buy_code = 1 or (rept.planning_make_buy_code = 2 and rept.sourcing_rule = 'Y')) and rept.based_on_rollup_flag = 1
		and not exists
			(select	'x'
			 from	cst_item_cost_details cicd
			 where	cicd.organization_id    = rept.organization_id
			 and	cicd.inventory_item_id  = rept.inventory_item_id
			 and	cicd.cost_type_id       = rept.cost_type_id
			 and	cicd.rollup_source_type = 3 -- rolled up
			)
	 union all
	 -- ===============================================
	 -- Report 4 - 'Based on Rollup Yes - Buy Items'
	 -- ===============================================
	 select 'Based on Rollup Yes - Buy Items' report_type, rept.* from rept where rept.costing_enabled_flag = 'Y' and rept.planning_make_buy_code = 2 and rept.based_on_rollup_flag = 1 and rept.sourcing_rule = 'N'
	 union all
	 -- ===============================================
	 -- Report 5 - 'Based on Rollup No - With BOMs'
	 -- ===============================================
	 select 'Based on Rollup No - With BOMs' report_type, rept.* from rept where rept.costing_enabled_flag = 'Y' and rept.planning_make_buy_code = 1 and rept.based_on_rollup_flag = 2 and (rept.bom = 'Y' or rept.routing = 'Y')
	 union all 
	 -- ===============================================
	 -- Report 6 - 'Based on Rollup No - With Sourcing Rules'
	 -- ===============================================
	 select 'Based on Rollup No - With Sourcing Rules' report_type, rept.* from rept where rept.costing_enabled_flag = 'Y' and rept.based_on_rollup_flag = 2 and rept.sourcing_rule = 'Y'
	 union all
	 -- ===============================================
	 -- Report 7 - 'Based on Rollup No - Make Items'
	 -- ===============================================
	 select 'Based on Rollup No - Make Items' report_type, rept.* from rept where rept.costing_enabled_flag = 'Y' and rept.planning_make_buy_code = 1 and rept.based_on_rollup_flag = 2
	 union all
	 -- ===============================================
	 -- Report 8 - 'Lot-Based Resources With Lot Size 1'
	 -- ===============================================
	 select 'Lot-Based Resources With Lot Size 1' report_type, rept.* from rept where rept.costing_enabled_flag = 'Y' and nvl(rept.cost_lot_size,1) = 1 and rept.planning_make_buy_code = 1
		and exists
			-- check to see if there are material or resource charges based on Lot
			(select	'x'
			 from	cst_item_cost_details cicd
			 where	cicd.organization_id    = rept.organization_id
			 and	cicd.inventory_item_id  = rept.inventory_item_id
			 and	cicd.cost_type_id       = rept.cost_type_id
			 and	cicd.basis_type         = 2 -- Lot
			)
	 union all
	 -- ===============================================
	 -- Report 9 - BOMs With No Components
	 -- ===============================================
	 select 'BOMs With No Components' report_type, rept.* from rept where rept.costing_enabled_flag = 'Y' and rept.based_on_rollup_flag = 1 and rept.bom = 'Y'
		and not exists
			-- check to see if a BOM exists with components
			(select 'x'
			 from	bom_structures_b bom,
				bom_components_b comp
			 where	bom.organization_id     = rept.organization_id
			 and	bom.assembly_item_id    = rept.inventory_item_id
			 and	bom.bill_sequence_id    = comp.bill_sequence_id
			 and	comp.effectivity_date  <= sysdate
			 and	nvl(comp.disable_date, sysdate+1) >  sysdate	
			)
	 union all
	 -- ==========================================
	 -- Report 10 - Item Costing vs. Item Asset Controls
	 -- ==========================================
	 -- Costing_Enabled <> Item_Inventory_Asset
	 select 'Item Costing vs. Item Asset Controls' report_type, rept.* from rept where rept.costing_enabled_flag <> rept.inventory_asset_flag
	 union all
	 -- ===============================================
	 -- Report 11 - Item Asset vs. Costing Asset Controls
	 -- ===============================================
	 -- Item_Inventory_Asset <> Cost Inventory_Asset
	 select 'Item Asset vs. Costing Asset Controls' report_type, rept.* from rept where rept.inventory_asset_flag <> decode(rept.cost_asset_flag, 1, 'Y', 'N')
	) rept_all
-- ===================================================================
-- Joins for the lookup codes
-- ===================================================================
where	ml1.lookup_type             = 'MTL_PLANNING_MAKE_BUY'
and	ml1.lookup_code             = rept_all.planning_make_buy_code
and	ml2.lookup_type             = 'CST_BONROLLUP_VAL'
and	ml2.lookup_code             = rept_all.based_on_rollup_flag
and	ml3.lookup_type             = 'SYS_YES_NO'
and	ml3.lookup_code             = rept_all.cost_asset_flag
and	fl1.lookup_type             = 'YES_NO'
and	fl1.lookup_code             = rept_all.bom
and	fl2.lookup_type             = 'YES_NO'
and	fl2.lookup_code             = rept_all.routing
and	fl3.lookup_type             = 'YES_NO'
and	fl3.lookup_code             = rept_all.sourcing_rule
and	fl4.lookup_type             = 'YES_NO'
and	fl4.lookup_code             = rept_all.costing_enabled_flag
and	fl5.lookup_type             = 'YES_NO'
and	fl5.lookup_code             = rept_all.inventory_asset_flag
and	fcl.lookup_type (+)         = 'ITEM_TYPE'
and	fcl.lookup_code (+)         = rept_all.item_type
-- ===================================================================
-- using the base tables to avoid hr views
-- ===================================================================
and	hoi.org_information_context = 'Accounting Information'
and	hoi.organization_id         = rept_all.organization_id
and	hoi.organization_id         = haou.organization_id -- this gets the organization name
-- avoid selecting disabled inventory organizations
and	sysdate < nvl(haou.date_to, sysdate + 1)
and	haou2.organization_id       = to_number(hoi.org_information3) -- this gets the operating unit id
and	hoi.org_information1        = gl.ledger_id      -- this gets the ledger id
and	1=1                         -- p_operating_unit, p_ledger
-- Order by Report Type, Ledger, Operating_Unit, Org_Code, Cost_Type, Item_Number
order by
	rept_all.report_type,
	nvl(gl.short_name, gl.name),
	haou2.name,
	rept_all.organization_code,
	rept_all.cost_type,
	rept_all.concatenated_segments
Parameter Name SQL text Validation
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 rept_all.organization_id=mic.organization_id and rept_all.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 rept_all.organization_id=mic.organization_id and rept_all.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 rept_all.organization_id=mic.organization_id and rept_all.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 rept_all.organization_id=mic.organization_id and rept_all.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
Cost Type 
cct.cost_type = :p_cost_type
 LOV
Assignment Set 
mas.assignment_set_name = :p_assignment_set
 LOV
Operating Unit 
haou2.name = :p_operating_unit
 LOV
Organization Code 
mp.organization_code = :p_org_code
 LOV
Download
Related Blitz Reports
CAC User-Defined and Rolled Up Costs MRP Pegging INV Inventory Items INV Item Templates GW Items WIP Required Components MSC Pegging FND Attached Documents CRP Resource Plan
Blitz Report In Action
Blitz Report™

Blitz Report™ provides multiple benefits: