CAC Intercompany SO Price List vs. Item Cost Comparison
Description
Categories: Enginatics
Repository: Github
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. ... more
/* +=============================================================================+
-- | Copyright 2010 - 2022 Douglas Volz Consulting, Inc. ... 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_information hoi, hr_all_organization_units_vl haou, hr_all_organization_units_vl haou2, gl_ledgers gl, mfg_lookups ml1, fnd_common_lookups fcl -- ======================================================= -- Organization, cost type, item cost and COGS acct joins -- ======================================================= where cct.cost_type_id = cic.cost_type_id and 6=6 -- p_cost_type, p_category_set and cic.organization_id = mp.organization_id and msiv.organization_id = mp.organization_id and cic.inventory_item_id = msiv.inventory_item_id -- Revision for version 1.15 -- and msiv.cost_of_sales_account = gcc.code_combination_id -- Revision for version 1.20 -- Revision for version 1.16 and msiv.inventory_item_status_code <> 'Inactive' -- End revision for version 1.20 -- ======================================================= -- Sourcing rule joins -- ======================================================= and msiv.organization_id = srcg_rules.src_organization_id and msiv.inventory_item_id = srcg_rules.inventory_item_id -- ================================================= -- Revision for version 1.15 -- Joins for category product line values -- ================================================= and mcs.category_set_id = mcs_tl.category_set_id and mcs_tl.language = userenv('lang') and mic.inventory_item_id = msiv.inventory_item_id (+) and mic.organization_id = msiv.organization_id (+) and mic.category_id = mc.category_id and mic.category_set_id = mcs.category_set_id -- End revision for version 1.15 -- =========================================== -- Organization joins to the HR org model -- =========================================== and hoi.org_information_context = 'Accounting Information' -- for inventory orgs and hoi.organization_id = mp.organization_id -- joins for the inventory org 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 -- avoid selecting disabled inventory organizations and sysdate < nvl(haou.date_to, sysdate +1) -- Revision for version 1.11 -- Eliminate orgs not in use and mp.organization_id <> mp.master_organization_id -- ======================================================= -- joins for the Lookups Codes -- ======================================================= and ml1.lookup_type = 'MTL_PLANNING_MAKE_BUY' and ml1.lookup_code = msiv.PLANNING_MAKE_BUY_CODE -- Lookup codes for item types and fcl.lookup_code (+) = msiv.item_type and fcl.lookup_type (+) = 'ITEM_TYPE' -- Revision for version 1.11 -- and mp.process_enabled_flag = 'N'-- Added to restrict only discrete orgs 11/3/14 ) Src_Org, -- =========================================================================== -- Tables to get the To_Org, items and costs based on the sourcing rules -- =========================================================================== (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, srcg_rules.to_organization_id to_organization_id, srcg_rules.src_organization_id src_organization_id, srcg_rules.assignment_set_name assignment_set, srcg_rules.sourcing_rule_name sourcing_rule, 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, ml2.meaning based_on_rollup, -- Revision for version 1.15 -- gcc.segment5 prod_grp, mc.category_concat_segs prod_grp, -- End revision for version 1.15 nvl(cic.tl_material_overhead,0) tl_material_overhead_cost, |