CAC Intercompany SO Price List vs. Item Cost Comparison

âžœ Back to Library
Description
Categories: Enginatics
Repository: Github
Report to show the internal SO price lists, source org item costs and compare against the "To Org" item costs and PII (profit in inventory) amounts. This report is used to ensure the profit in inventory (PII) cost model is working correctly.

/* +=============================================================================+
-- | Copyright 2010 - 2022 Douglas Volz Consulting, Inc.  ...  Report to show the internal SO price lists, source org item costs and compare against the "To Org" item costs and PII (profit in inventory) amounts. This report is used to ensure the profit in inventory (PII) cost model is working correctly.

/* +=============================================================================+
-- | Copyright 2010 - 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])
-- |
-- | Program Name: xxx_so_price_cost_pii_rept.sql
-- |
-- | Parameters:
-- | p_price_effective_date -- Date the sales order list prices are effective, mandatory
-- | p_curr_conv_date -- currency conversion date in dd-mon-yyyy format, mandatory
-- | p_curr_conv_type -- the desired currency conversion type to use to convert the
-- | sales price to the same currency as the item cost, mandatory
-- | p_pii_cost_type -- PII cost type to be reported, mandatory
-- | p_pii_sub_element -- The sub-element or resource for profit in inventory,
-- | such as PII or ICP (mandatory)
-- | p_assignment_set -- The assignment set parameter, mandatory
-- | p_cost_type -- Cost type to be reported for the standard costs, mandatory
-- | p_cost_category_set -- The item category set for Cost Accounting, mandatory
-- | p_to_org_code -- Specific "To Org" you wish to report, optional
-- | p_to_org_ledger -- the "To-Org" general ledger you wish to report, optional
-- |
-- | Description:
-- | Report to show the internal SO price lists, source org item costs and compare
-- | against the "To Org" item costs and PII/ICP amounts. This report is used to
-- | ensure the profit in inventory cost model is working correctly.
-- |
-- | Version Modified on Modified by Description
-- | ======= =========== =========================================
-- | 1.0 26 Nov 2010 Douglas Volz Created initial Report
-- | 1.20 21 Aug 2019 Douglas Volz Removed client-specific SQL logic.
-- | 1.21 25 Oct 2019 Douglas Volz Correction to p_price_effective_date parameter
-- | 1.22 17 Jul 2022 Douglas Volz Changes for multi-language lookup values. Changed
-- | back to Oracle QP price packages, to get price list
-- | information based on both category or item-specific
-- | price lists.
-- +=============================================================================+*/



   more
Run CAC Intercompany SO Price List vs. Item Cost Comparison and other Oracle EBS reports with Blitz Reportâ„¢ on our demo environment
select	Src_Org.ledger Source_Ledger,
	Src_Org.operating_unit Source_Operating_Unit,
	Src_Org.organization_code Source_Org,
	-- Revision for version 1.10, only  
	-- show the max assignment set
	-- To_Org.assignment_set Assignment_Set,
	max(To_Org.assignment_set) Assignment_Set,
	To_Org.sourcing_rule Sourcing_Rule,
	List_Price.item_number Item_Number,
	List_Price.item_description Item_Description,
	Src_Org.primary_uom_code UOM_Code,
	Src_Org.item_status Item_Status,
	Src_Org.item_type Item_Type,
--	To_Org.item_type To_Org_Item_Type,
	Src_Org.mb_code Source_Make_Buy_Code,
	To_Org.mb_code To_Org_Make_Buy_Code,
	-- Revision for version 1.22
	Src_Org.category_set_name Category_Set,
	Src_Org.prod_grp Source_Category,
	To_Org.prod_grp To_Org_Category,
	-- End revision for version 1.22
	List_Price.name Source_Price_List_Name,
	List_Price.currency_code Price_Currency_Code,
	List_Price.price_in_primary_uom List_Price_in_Primary_UOM, 
	Price_gdr.conversion_date Currency_Conversion_Date,
	Price_gdr.conversion_rate Currency_Conversion_Rate,
	To_Org.currency_code To_Org_GL_Currency_Code,
	(List_Price.price_in_primary_uom * Price_gdr.conversion_rate) Converted_Source_List_Price,
	Src_Org.currency_code Source_Currency_Code,
	-- Revision for version 1.19
	-- Src_Org.tl_material_overhead_cost Src_Org_TL_Matl_Ohvd_Cost,
	-- Src_Org.tl_resource_cost Src_Org_TL_Resource_Cost,
	-- Src_Org.tl_outside_processing_cost Src_Org_TL_OSP_Cost,
	-- Src_Org.tl_overhead_cost Src_Org_TL_Overhead_Cost,
	-- Src_Org.tl_added_cost Src_Org_Added_Cost,
	-- End revision for version 1.19
	Src_Org.item_cost Source_Item_Cost, 
	To_Org_gdr.conversion_date To_Org_Currency_Conv_Date,
	To_Org_gdr.conversion_rate To_Org_Currency_Conv_Rate,
	To_Org.currency_code To_Org_Currency_Code,
	Src_Org.item_cost * To_Org_gdr.conversion_rate Converted_Src_Item_Cost,
	(List_Price.price_in_primary_uom * Price_gdr.conversion_rate) - 
		       (Src_Org.item_cost * To_Org_gdr.conversion_rate) Converted_Src_InterCo_Margin,
	Src_Org.pii_cost Prod_Source_PII_Cost,
	To_Org.ledger To_Org_Ledger,
	To_Org.operating_unit To_Org_Operating_Unit,
	To_Org.organization_code To_Org,
	-- Fix for version 1.5
	-- InterCo.party_name Internal_Customer,
	-- Revision for version 1.19
	-- To_Org.tl_material_overhead_cost To_Org_TL_Matl_Ohvd_Cost,
	-- To_Org.tl_resource_cost To_Org_TL_Resource_Cost,
	-- To_Org.tl_outside_processing_cost To_Org_TL_OSP_Cost,
	-- To_Org.tl_overhead_cost To_Org_TL_Overhead_Cost,
	-- To_Org.tl_added_cost To_Org_Added_Cost,
	-- End revision for version 1.19
	To_Org.item_cost To_Org_Item_Cost,
	(List_Price.price_in_primary_uom * Price_gdr.conversion_rate) - To_Org.item_cost Source_Price_vs_To_Org_Cost,
	(Src_Org.item_cost * To_Org_gdr.conversion_rate) - To_Org.item_cost Src_Org_Cost_vs_To_Org_Cost,
	To_Org.pii_cost To_Org_Prod_PII_Cost,
	List_Price.start_date Price_Start_Date,
	List_Price.revision_date Price_Revision_Date	
	-- ===========================================================================
	-- Tables to get the price list and selling operating units.  Use a table
	-- select statement to group by price_list_id and sell_organization_id 
	-- ===========================================================================
-- Revision for version 1.14
-- from 	(select	hsu.price_list_id,
from	(select nvl(hsu.price_list_id, hca.price_list_id) price_list_id,
		mip.sell_organization_id,
		-- Revision for version 1.18
		mip.ship_organization_id
		-- Fix for version 1.5
		-- hz.party_name,
		-- pv.vendor_name
	 from	hz_cust_site_uses_all hsu,           -- joins to intercompany relationships
		mtl_intercompany_parameters mip,     -- intercompany ship (from) and sell to relationships
		-- Fix for version 1.14
		hz_cust_acct_sites_all hcs,
		hz_cust_accounts hca
		-- End revision for version 1.14
		-- Fix for version 1.5
		-- ar.hz_parties hz,
		-- po_vendors pv,
	 where	mip.customer_site_id             = hsu.site_use_id            -- internal customer information
	 -- Fix for version 1.5
	 -- and	hca.party_id                     = hz.party_id
	 -- and	mip.vendor_id = pv.vendor_id
	 -- End fix for version 1.5
	 -- Revision for version 1.14
	 and	hca.cust_account_id              = hcs.cust_account_id
	 and	hcs.cust_acct_site_id            = hsu.cust_acct_site_id
	 -- and	hsu.price_list_id is not null
	 -- End revision for version 1.14 
	 -- Fix for version 1.4, the customer type is not set correctly for orgs 1xx
	 -- This condition was preventing the 1xx To Orgs from being selected
	 -- and	hca.customer_type                = 'I'  -- Internal Customers
	 group by
		-- Revision for version 1.14
		-- hsu.price_list_id,
		nvl(hsu.price_list_id, hca.price_list_id),
		mip.sell_organization_id,
		-- Revision for version 1.18
		mip.ship_organization_id
	 	-- Fix for version 1.5
		-- hz.party_name
	) InterCo_OUs,	
	-- ===========================================================================
	-- Tables to get the most current list price based on the Price Effective Date
	-- ===========================================================================
	 -- Revision for version 1.22, revert back
         -- Revision for version 1.16
         -- Hint to make the price list header the driving table
	 -- (select	 /*+ leading(ic_price_list)*/  
	(select	qlh_b.list_header_id list_header_id,
	 -- End revision for version 1.22
		qlh_tl.name name,
		qlh_tl.description description,
		msiv.concatenated_segments Item_number,
		msiv.description item_description,
		-- Revision for version 1.22
		misv.inventory_item_status_code,
		mucv.primary_uom_code,
		-- End revision for version 1.22
		msiv.inventory_item_id inventory_item_id,
		msiv.organization_id validation_org_id,
		qlh_b.currency_code currency_code,
		qpll.operand price,
		qpll.operand * mucv.conversion_rate price_in_primary_uom, 
		max(qpll.start_date_active) start_date,
		trunc(qpll.revision_date) revision_date
	 from	qp_list_headers_b qlh_b,
		qp_list_headers_tl qlh_tl,
		qp_list_lines qpll,
		-- Revision for version 1.22, revert back to Oracle functions, remove qpa and revision 1.16
		-- Revision for version 1.16
		-- qp_pricing_attributes qpa,
		-- Revision for version 1.16
		-- Revision for version 1.13
		-- (select	hsu.price_list_id
		-- from	ar.hz_cust_site_uses_all hsu,                   -- joins to intercompany relationships
		--	inv.mtl_intercompany_parameters mip             -- interco ship (from) and sell to relationships     
		-- where	mip.customer_site_id          = hsu.site_use_id -- internal customer information
		-- and	hsu.price_list_id is not null
		-- group by
		-- 	hsu.price_list_id) ic_price_list,
		(select	distinct nvl(hsu.price_list_id, hca.price_list_id) price_list_id
		 from	mtl_intercompany_parameters mip,
			hz_cust_site_uses_all hsu,
			hz_cust_accounts hca,
			hz_cust_acct_sites_all hcs
		 where  mip.customer_site_id             = hsu.site_use_id            -- internal customer information
		 and	hsu.cust_acct_site_id            = hcs.cust_acct_site_id
		 and	hcs.cust_account_id              = hca.cust_account_id
		 -- Revision for version 1.19
		 and nvl(hsu.price_list_id, hca.price_list_id) is not null
		) ic_price_list,
		-- End revision for version 1.16
		mtl_system_items_vl msiv,                            -- item master per the validation organization
		mtl_uom_conversions_view mucv,                       -- item master UOM conversion view
		-- Revision for version 1.22
		mtl_item_status_vl misv
	 -- =======================================================
	 -- For the price lists, joins to the item master and UOM 
	 -- conversions. The price list may be in a different UOM 
	 -- than the primary UOM code.  The item costs are in the 
	 -- primary UOM code only.  Assume the uom_code is the same 
	 -- across all inventory organizations
	 -- =======================================================
	 where	mucv.inventory_item_id           = msiv.inventory_item_id
	 and	mucv.organization_id             = msiv.organization_id -- join to the validation org
	 -- Revision for version 1.22, revert back to Oracle pricing functions
	 -- Revision for version 1.19, eliminate joins to private functions
	 -- Will get one price qualifier row per inventory_item_id
	 -- and	msiv.inventory_item_id           = to_number(qpa.product_attr_value)
	 -- and	ucr.uom_code                     = qpa.product_uom_code
	 -- and	qpa.product_attr_value <> 'ALL'
	 -- Non-unique index on qpa.list_line_id and qpa.excluder_flag
	 -- and	qpa.list_line_id                 = qpll.list_line_id
	 -- and	qpa.excluder_flag                = 'N'
	 -- and	qpa.product_attribute_context    = 'ITEM'
	 -- Find the item master validation organizations
	 -- and	msiv.organization_id             = (select	max(to_number(ospa.parameter_value))
	 --					    from	oe_sys_parameters_all ospa
	 --					    where	ospa.parameter_code = 'MASTER_ORGANIZATION_ID'
	 --					   )
	 -- End revision for version 1.19
	 -- =======================================================
	 -- Joins to the Price List, based on qp_price_list_lines_v
	 -- =======================================================
	 -- Revision for version 1.16 and 1.22
	 and	msiv.inventory_item_id           = qp_price_list_pvt.get_inventory_item_id(qpll.list_line_id)
	 and	msiv.organization_id             = qp_util.get_item_validation_org
	 and	mucv.uom_code                    = qp_price_list_pvt.get_product_uom_code(qpll.list_line_id) -- uom code
	 and	msiv.inventory_item_status_code  = misv.inventory_item_status_code
	 -- End revision for version 1.22
	 -- Revision for version 1.16 and 1.20, exclude inactive items
	 and	msiv.inventory_item_status_code <> 'Inactive'
	 and	qlh_b.list_header_id             = qlh_tl.list_header_id
	 -- Revision for version 1.11
	 -- and	qlh_tl.language                  = 'US'
	 and	qlh_tl.language                  = userenv('lang')
	 -- Revision for version 1.16
	 -- Non-unique index on qpll.list_line_type_code and qpll.modifier_level_code
	 and	qpll.list_line_type_code         = 'PLL'
	 and	qpll.modifier_level_code         = 'LINE'
	 and	qlh_b.list_header_id             = qpll.list_header_id
	 -- Revision for version 1.13, get price list Ids based on intercompany relationships
	 and	qlh_b.list_header_id             = ic_price_list.price_list_id
	 -- Avoid selecting inactive specific list prices (sales prices)
 	 and	1=1                              -- p_price_effective_date
	 group by
		qlh_b.list_header_id,                   -- list_header_id
		qlh_tl.name,                            -- price_list
		qlh_tl.description,                     -- price_list_description
		msiv.concatenated_segments,             -- item number
		msiv.description,                       -- item description
		-- Revision for version 1.22
		misv.inventory_item_status_code,        -- item status code
		mucv.primary_uom_code,                  -- primary uom code
		-- End revision for version 1.22
		msiv.inventory_item_id,                 -- inventory_item_id
		msiv.organization_id,                   -- validation_org_id
		qlh_b.currency_code,                    -- price_curr_code
		qpll.operand,                           -- price
		qpll.operand * mucv.conversion_rate,     -- price_in_primary_uom 
		QPLL.revision_date                      -- price_revision_date
	) List_Price,
	-- ===========================================================================
	-- Tables to get currency exchange rate information for the Price List prices
	-- Select Currency Rates based on the currency conversion date
	-- ===========================================================================
	(select	gdr.from_currency,
		gdr.to_currency,
		gdct.user_conversion_type,
		gdr.conversion_date,
		gdr.conversion_rate
	 from	gl_daily_rates gdr,
		gl_daily_conversion_types gdct
	 where	exists (
			select 'x'
			from	mtl_parameters mp,
				hr_organization_information hoi,
				hr_all_organization_units_vl haou,
				hr_all_organization_units_vl haou2,
				gl_ledgers gl
			-- =================================================
			-- Get inventory ledger and operating unit information
			-- =================================================
			where	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	gdr.to_currency               = gl.currency_code
			-- =================================================
			-- Revision for version 1.11
			-- Eliminate orgs not in use
			and	mp.organization_id           <> mp.master_organization_id
			   )
	 and	exists (
			select 'x'
			from	mtl_parameters mp,
				hr_organization_information hoi,
				hr_all_organization_units_vl haou,
				hr_all_organization_units_vl haou2,
				gl_ledgers gl
			-- =================================================
			-- Get inventory ledger and operating unit information
			-- =================================================
			where	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	gdr.from_currency             = gl.currency_code
			-- =================================================
			-- Eliminate orgs no longer in use
			and	mp.organization_id           <> mp.master_organization_id
			-- End revision for version 1.11
			   )
	 and	gdr.conversion_type       = gdct.conversion_type
	 and	2=2                       -- p_curr_conv_date
	 and	3=3                       -- p_curr_conv_type
	 union all
	 select	gl.currency_code,              -- from_currency
		gl.currency_code,              -- to_currency
		gdct.user_conversion_type,     -- conversion_type
		:p_curr_conv_date,             -- conversion_date
		1                              -- conversion_rate
	 from	gl_ledgers gl,
		gl_daily_conversion_types gdct
	 where	3=3                       -- p_curr_conv_type
	 group by
		gl.currency_code,
		gl.currency_code,
		gdct.user_conversion_type,
		:p_curr_conv_date,
		1
	) Price_gdr, -- Currency Exchange Rates for the Price Lists
	-- ===========================================================================
	-- Tables to get currency exchange rate information for the To_Org
	-- Select Currency Rates based on the currency conversion date
	-- ===========================================================================
	(select	gdr.from_currency,
		gdr.to_currency,
		gdct.user_conversion_type,
		gdr.conversion_date,
		gdr.conversion_rate
	 from	gl_daily_rates gdr,
		gl_daily_conversion_types gdct
	 where	exists (
			select 'x'
			from	mtl_parameters mp,
				hr_organization_information hoi,
				hr_all_organization_units_vl haou,
				hr_all_organization_units_vl haou2,
				gl_ledgers gl
			-- =================================================
			-- Get inventory ledger and operating unit information
			-- =================================================
			where	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	gdr.to_currency               = gl.currency_code
			-- =================================================
			-- Revision for version 1.11
			-- Eliminate orgs not in use
			and	mp.organization_id           <> mp.master_organization_id
			   )
	 and	exists (
			select 'x'
			from	mtl_parameters mp,
				hr_organization_information hoi,
				hr_all_organization_units_vl haou,
				hr_all_organization_units_vl haou2,
				gl_ledgers gl
			-- =================================================
			-- Get inventory ledger and operating unit information
			-- =================================================
			where	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	gdr.from_currency             = gl.currency_code
			-- =================================================
			-- Revision for version 1.11
			-- Eliminate orgs not in use
			and	mp.organization_id           <> mp.master_organization_id
			   )
	 and	gdr.conversion_type       = gdct.conversion_type
	 and	2=2                       -- p_curr_conv_date
	 and	3=3                       -- p_curr_conv_type
	 union all
	 select	gl.currency_code,              -- from_currency
		gl.currency_code,              -- to_currency
		gdct.user_conversion_type,     -- conversion_type
		:p_curr_conv_date,             -- conversion_date
		1                              -- conversion_rate
	 from	gl_ledgers gl,
		gl_daily_conversion_types gdct
	  where	3=3                        -- p_curr_conv_type
	 group by
		gl.currency_code,
		gl.currency_code,
		gdct.user_conversion_type,
		:p_curr_conv_date,
		1
	) To_Org_gdr, -- Currency Exchange Rates for the to org
	-- =======================================================
	-- Get the source orgs, items and item cost information
	-- =======================================================
	(select	nvl(gl.short_name, gl.name)	ledger,
		gl.ledger_id ledger_id,
	 	gl.currency_code currency_code,
		haou2.name operating_unit,
		haou2.organization_id operating_unit_id,
		mp.organization_code organization_code,
		cic.organization_id src_organization_id,
		-- Revision for version 1.16
		srcg_rules.to_organization_id to_organization_id,
		cic.inventory_item_id inventory_item_id,
		msiv.primary_uom_code,
		msiv.inventory_item_status_code item_status,
		fcl.meaning item_type,
		ml1.meaning mb_code,
		-- Revision for version 1.22
		mcs_tl.category_set_name,
		-- Revision for version 1.15
		-- gcc.segment5 prod_grp,
		mc.category_concat_segs prod_grp,
		-- End revision for version 1.15
		-- End revision for version 1.22
		nvl(cic.tl_material_overhead,0) tl_material_overhead_cost,
		nvl(cic.tl_resource,0) tl_resource_cost,
		nvl(cic.tl_outside_processing,0) tl_outside_processing_cost,
		nvl(cic.tl_overhead,0) tl_overhead_cost,
		nvl(cic.tl_material_overhead,0) + nvl(cic.tl_resource,0) + nvl(cic.tl_outside_processing,0) + nvl(cic.tl_overhead,0) tl_added_cost,
		nvl(cic.item_cost,0) - nvl(cic.tl_material_overhead,0) - nvl(cic.tl_resource,0) - 
			nvl(cic.tl_outside_processing,0) - nvl(cic.tl_overhead,0) net_item_cost,
		nvl(cic.item_cost,0) item_cost,
                nvl((select sum(cicd.item_cost)
	             from   cst_item_cost_details cicd,
		            cst_cost_types cct,
		            bom_resources br
	             where  cicd.cost_type_id      = cct.cost_type_id
	             and    4=4                    -- p_pii_cost_type, p_pii_sub_element
	             and    cicd.inventory_item_id = cic.inventory_item_id  -- get the source org item id
	             and    cicd.organization_id   = cic.organization_id    -- get the source org id
	             and    cicd.resource_id       = br.resource_id
		    ),0)					pii_cost
	 -- =======================================================
	 -- Item, costs, organization and ledger tables
	 -- =======================================================
	 from	cst_item_costs		cic,
		cst_cost_types		cct,
		mtl_parameters		mp,
		mtl_system_items_vl 	msiv,
		-- Revision for version 1.15
		mtl_categories_v           mc,
		mtl_item_categories        mic,
		mtl_category_sets_b        mcs,
		mtl_category_sets_tl       mcs_tl,
		-- End revision for version 1.15
		-- Revision for version 1.17
		-- Add in the item master source organizations
		-- Revision for version 1.16
		-- Limit the source org costs by sourcing rule
		-- mrp_sr_source_org           msso,
		-- mrp_sr_receipt_org          msro,
		-- mrp_sourcing_rules          msr,
		-- mrp_sr_assignments          msa,
		-- mrp_assignment_sets         mas,
		-- End revision for version 1.16
		-- =======================================================
		-- Revision for version 1.17
		-- Get both sourcing rules and item master source org
		-- information.  Missing lots of sourcing rules.
		-- =======================================================
		(select	msa.organization_id to_organization_id,
			msso.source_organization_id src_organization_id,
			msa.inventory_item_id inventory_item_id
		 from	mrp_sr_source_org           msso,
			mrp_sr_receipt_org          msro,
			mrp_sourcing_rules          msr,
			mrp_sr_assignments          msa,
			mrp_assignment_sets         mas
		 where	msso.sr_receipt_id              = msro.sr_receipt_id
		 and	msr.sourcing_rule_id            = msro.sourcing_rule_id
		 and	msa.sourcing_rule_id            = msr.sourcing_rule_id
		 -- Client only has one Assignment Set
		 and	5=5                             -- p_assignment_set
		 and	msa.assignment_set_id           = mas.assignment_set_id
		 -- exclude vendor sourcing rules
		 and	msso.source_organization_id is not null
		 union all
		 select	msiv.organization_id to_organization_id,
			msiv.source_organization_id src_organization_id,
			msiv.inventory_item_id inventory_item_id
		 from	mtl_system_items_vl msiv,
			mtl_parameters inv_to_org,
			mtl_parameters inv_src_org
		 where	msiv.source_organization_id is not null
		 and	msiv.organization_id           = inv_to_org.organization_id
		 and	msiv.source_organization_id    = inv_src_org.organization_id
		 -- Revision for version 1.20
		 and	msiv.inventory_item_status_code <> 'Inactive'
		 and	msiv.organization_id          <> inv_to_org.master_organization_id
		 and	msiv.source_organization_id   <> inv_src_org.master_organization_id
		 and	(msiv.organization_id,msiv.source_organization_id,msiv.inventory_item_id) not in
				 (
				    select	msa.organization_id,msso.source_organization_id,msa.inventory_item_id
				    from	mrp_sr_source_org msso,
						mrp_sr_receipt_org msro,
						mrp_sourcing_rules msr,
						mrp_sr_assignments msa,
						mrp_assignment_sets mas
				    where	msso.sr_receipt_id            = msro.sr_receipt_id
				    and		msr.sourcing_rule_id          = msro.sourcing_rule_id
				    and		msa.sourcing_rule_id          = msr.sourcing_rule_id
				    -- Client only has one Assignment Set
				    and		5=5                           -- p_assignment_set
				    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		msso.source_organization_id is not null
				    -- ====================================
				    -- Material Parameter joins for to_org
				    -- ====================================
				    and		msa.organization_id           = inv_to_org.organization_id
				    and		msso.source_organization_id   = inv_src_org.organization_id
				)
		) srcg_rules,
		-- End for revision 1.17
		-- Revision for version 1.15
		-- gl_code_combinations gcc,
		hr_organization_inf