CAC Calculate ICP PII Item Costs by Where Used
Description
Categories: Enginatics
Repository: Github
Repository: Github
Use this report to find where components from one source organization are being used in another manufacturing organization, as a single-level BOM report. Based on sourcing rules and bills of materials.
/* +=============================================================================+
-- | Copyright 2018 - 2020 Douglas Volz Consulting, Inc. |
-- | All rights r ... more
/* +=============================================================================+
-- | Copyright 2018 - 2020 Douglas Volz Consulting, Inc. |
-- | All rights r ... more
Run
CAC Calculate ICP PII Item Costs by Where Used and other Oracle EBS reports with Blitz Report™ on our demo environment
select gp.period_name Period_Name, -- Revision for version 1.4 nvl(inv_src_org.gl_short_name, inv_src_org.gl_name) Src_Ledger, nvl(inv_to_org.gl_short_name, inv_to_org.gl_name) To_Ledger, inv_src_org.operating_unit Src_Operating_Unit, inv_to_org.operating_unit To_Operating_Unit, -- End revision for version 1.4 item_sourcing.to_org_code Assy_To_Org, item_sourcing.src_org_code Comp_Src_Org, item_sourcing.assignment_set Assignment_Set, item_sourcing.sourcing_rule Sourcing_Rule, msiv.concatenated_segments Assembly, msiv.description Assembly_Description, -- Revision for version 1.4 fcl_bom.meaning Assembly_Item_Type, misv_bom.inventory_item_status_code_tl Assembly_Status_Code, ml_bom.meaning Assembly_Make_Buy_Code, muomv_bom.uom_code UOM_Code, ml_bom2.meaning BOM_Type, -- End revision for version 1.4 bom.implementation_date Date_Implemented, mir.revision_code Item_Revision, &category_columns comp.item_num Item_Seq, comp.operation_seq_num Op_Seq, item_sourcing.comp_item_number Component, item_sourcing.comp_description Component_Description, item_sourcing.comp_uom_code Component_UOM, fcl_comp.meaning Component_Item_Type, item_sourcing.item_status_code Component_Status_Code, -- Revision for version 1.3 item_sourcing.to_org_mb_code Component_Make_Buy_Code, comp.component_quantity Quantity_per_Assembly, max(comp.effectivity_date) Effective_From, comp.disable_date Effective_To, nvl(comp.planning_factor,0) Planning_Percent, comp.component_yield_factor Yield, -- Revision for version 1.4 ml_comp2.meaning Include_in_Cost_Rollup, ml_comp2.meaning WIP_Supply_Type, -- End revision for version 1.4 nvl((select sum(mohd.transaction_quantity) from mtl_onhand_quantities_detail mohd where mohd.inventory_item_id = msiv.inventory_item_id and mohd.organization_id = msiv.organization_id),0) Assembly_Onhand_Quantity, nvl((select sum(mohd.transaction_quantity) from mtl_onhand_quantities_detail mohd where mohd.inventory_item_id = item_sourcing.comp_item_id and mohd.organization_id = msiv.organization_id),0) Src_Component_Onhand_Quantity, gdr.from_currency Src_Curr_Code, nvl(src_org_costs.item_cost,0) Src_Component_Item_Cost, nvl((select sum(mohd.transaction_quantity) from mtl_onhand_quantities_detail mohd where mohd.inventory_item_id = item_sourcing.comp_item_id and mohd.organization_id = msiv.organization_id),0) * nvl(src_org_costs.item_cost,0) Src_Component_Onhand_Value, -- Revision for version 1.1 -- Add in item costs and PII costs, -- Revision for version 1.3 -- Use the Planning Make/Buy Code, not the MFG Org_Code -- round(decode(item_sourcing.src_org_code, round(decode(msiv.planning_make_buy_code, -- End revision for version 1.3 -- ====================================================================== -- Revision for version 1.1, 1.3 -- 1 = Make, 2 = Buy -- If Make or buy item, take away Source Org PII for comparison purposes -- This assumes the transfer price is marked up at each hop, including any TL costs -- Note that PII is a negative value so we add the two together -- ====================================================================== 1, nvl(src_org_costs.item_cost,0) + nvl(src_org_costs.pii_cost,0), 2, nvl(src_org_costs.item_cost,0) + nvl(src_org_costs.pii_cost,0), nvl(src_org_costs.item_cost,0) + nvl(src_org_costs.pii_cost,0) -- End revision for version 1.1, 1.3 ) ,5) Source_Item_Cost, gdr.conversion_date Curr_Conv_Date, gdr.conversion_rate Curr_Conv_Rate, gdr.to_currency To_Org_Curr_Code, round(gdr.conversion_rate * -- Revision for version 1.3 -- Use the Planning Make/Buy Code, not the MFG Org_Code -- decode(item_sourcing.src_org_code, decode(msiv.planning_make_buy_code, -- End revision for version 1.3 -- ====================================================================== -- Revision for version 1.1, 1.3 -- 1 = Make, 2 = Buy -- If Make or buy item, take away Source Org PII for comparison purposes -- This assumes the transfer price is marked up at each hop, including any TL costs -- Note that PII is a negative value so we add the two together -- ====================================================================== 1, nvl(src_org_costs.item_cost,0) + nvl(src_org_costs.pii_cost,0), 2, nvl(src_org_costs.item_cost,0) + nvl(src_org_costs.pii_cost,0), nvl(src_org_costs.item_cost,0) + nvl(src_org_costs.pii_cost,0) -- End revision for version 1.1, 1.3 ) ,5) Converted_Source_Item_Cost, item_sourcing.to_org_code To_Org, -- Revision for version 1.3 -- Use the Planning Make/Buy Code, not the MFG Org_Code -- round(decode(item_sourcing.to_org_code, round(decode(item_sourcing.to_org_mb_code, -- End revision for version 1.3 -- ====================================================================== -- Revision for version 1.1, 1.3 -- 1 = Make, 2 = Buy -- If Make or buy item, take away To_Org This Level Costs for comparison purposes -- This assumes the transfer price is marked up at each hop, including any TL costs -- Note that PII is a negative value so we add the two together -- ====================================================================== 1, nvl(to_org_costs.net_cost,0), 2, nvl(to_org_costs.net_cost,0), nvl(to_org_costs.net_cost,0) -- End revision for version 1.1, 1.3 ) ,5) To_Org_Item_Cost, -- Conv. Src_Item_Cost minus the To_Org_Item_Cost = Calculated_To_Org_PII -- Conv. Src_Item_Cost round(gdr.conversion_rate * -- Revision for version 1.3 -- Use the Planning Make/Buy Code, not the MFG Org_Code -- decode(item_sourcing.src_org_code, decode(msiv.planning_make_buy_code, -- End revision for version 1.3 -- ====================================================================== -- Revision for version 1.1, 1.3 -- 1 = Make, 2 = Buy -- If Make or buy item, take away Source Org PII for comparison purposes -- This assumes the transfer price is marked up at each hop, including any TL costs -- Note that PII is a negative value so we add the two together -- ====================================================================== 1, nvl(src_org_costs.item_cost,0) + nvl(src_org_costs.pii_cost,0), 2, nvl(src_org_costs.item_cost,0) + nvl(src_org_costs.pii_cost,0), nvl(src_org_costs.item_cost,0) + nvl(src_org_costs.pii_cost,0) -- End revision for version 1.1, 1.3 ) - -- minus the To_Org_Item_Cost -- Revision for version 1.3 -- Use the Planning Make/Buy Code, not the MFG Org_Code -- round(decode(item_sourcing.to_org_code, round(decode(item_sourcing.to_org_mb_code, -- End revision for version 1.3 -- ====================================================================== -- Revision for version 1.1, 1.3 -- 1 = Make, 2 = Buy -- If Make or buy item, take away To_Org This Level Costs for comparison purposes -- This assumes the transfer price is marked up at each hop, including any TL costs -- Note that PII is a negative value so we add the two together -- ====================================================================== 1, nvl(to_org_costs.net_cost,0), 2, nvl(to_org_costs.net_cost,0), nvl(to_org_costs.net_cost,0) -- End revision for version 1.1, 1.3 ) ) ,5) Calculated_To_Org_PII, -- Calculated_To_Org_PII / To_Org_Item_Cost = PII_Percent round( -- Calculated_To_Org_PII (gdr.conversion_rate * -- Revision for version 1.3 -- Use the Planning Make/Buy Code, not the MFG Org_Code -- (decode(item_sourcing.src_org_code, (decode(msiv.planning_make_buy_code, -- End revision for version 1.3 -- ====================================================================== -- Revision for version 1.1, 1.3 -- 1 = Make, 2 = Buy -- If Make or buy item, take away Source Org PII for comparison purposes -- This assumes the transfer price is marked up at each hop, including any TL costs -- Note that PII is a negative value so we add the two together -- ====================================================================== 1, nvl(src_org_costs.item_cost,0) + nvl(src_org_costs.pii_cost,0), 2, nvl(src_org_costs.item_cost,0) + nvl(src_org_costs.pii_cost,0), nvl(src_org_costs.item_cost,0) + nvl(src_org_costs.pii_cost,0) -- End revision for version 1.1, 1.3 ) - -- Revision for version 1.3 -- Use the Planning Make/Buy Code, not the MFG Org_Code -- decode(item_sourcing.to_org_code, decode(item_sourcing.to_org_mb_code, -- End revision for version 1.3 -- ====================================================================== -- Revision for version 1.1, 1.3 -- 1 = Make, 2 = Buy -- If Make or buy item, take away To_Org This Level Costs for comparison purposes -- This assumes the transfer price is marked up at each hop, including any TL costs -- Note that PII is a negative value so we add the two together -- ====================================================================== 1, nvl(to_org_costs.net_cost,0), 2, nvl(to_org_costs.net_cost,0), nvl(to_org_costs.net_cost,0) -- End revision for version 1.1, 1.3 ) ) ) -- Revision for version 1.3 -- Use the Planning Make/Buy Code, not the MFG Org_Code -- / decode(decode(item_sourcing.to_org_code, / decode(decode(item_sourcing.to_org_mb_code, -- End revision for version 1.3 -- ====================================================================== -- Revision for version 1.1, 1.3 -- 1 = Make, 2 = Buy -- If Make or buy item, take away To_Org This Level Costs for comparison purposes -- This assumes the transfer price is marked up at each hop, including any TL costs -- Note that PII is a negative value so we add the two together -- ====================================================================== 1, nvl(to_org_costs.net_cost,0), 2, nvl(to_org_costs.net_cost,0), nvl(to_org_costs.net_cost,0) -- End revision for version 1.1, 1.3 ), 0, 1, -- Revision for version 1.3 -- Use the Planning Make/Buy Code, not the MFG Org_Code -- decode(item_sourcing.to_org_code, decode(item_sourcing.to_org_mb_code, -- End revision for version 1.3 -- ====================================================================== -- Revision for version 1.1, 1.3 -- 1 = Make, 2 = Buy -- If Make or buy item, take away To_Org This Level Costs for comparison purposes -- This assumes the transfer price is marked up at each hop, including any TL costs -- Note that PII is a negative value so we add the two together -- ====================================================================== 1, nvl(to_org_costs.net_cost,0), 2, nvl(to_org_costs.net_cost,0), nvl(to_org_costs.net_cost,0) -- End revision for version 1.1, 1.3 ) ) ,5) PII_Percent, to_org_costs.pii_cost PII_Item_Cost, -- Calculated_To_Org_PII minus PII_Item_Cost = PII_Difference -- Conv. Src_Item_Cost minus the To_Org_Item_Cost = Calculated_To_Org_PII -- Conv. Src_Item_Cost round(gdr.conversion_rate * -- Revision for version 1.3 -- decode(item_sourcing.src_org_code, decode(item_sourcing.to_org_mb_code, -- End revision for version 1.3 -- ====================================================================== -- Revision for version 1.1, 1.3 -- 1 = Make, 2 = Buy -- If Make or buy item, take away Source Org PII for comparison purposes -- This assumes the transfer price is marked up at each hop, including any TL costs -- Note that PII is a negative value so we add the two together -- ====================================================================== 1, nvl(src_org_costs.item_cost,0) + nvl(src_org_costs.pii_cost,0), 2, nvl(src_org_costs.item_cost,0) + nvl(src_org_costs.pii_cost,0), nvl(src_org_costs.item_cost,0) + nvl(src_org_costs.pii_cost,0) -- End revision for version 1.1, 1.3 ) - -- To_Org_Item_Cost -- Revision for version 1.3 -- decode(item_sourcing.to_org_code, decode(item_sourcing.to_org_mb_code, -- End revision for version 1.3 -- ====================================================================== -- Revision for version 1.1, 1.3 -- 1 = Make, 2 = Buy -- If Make or buy item, take away To_Org This Level Costs for comparison purposes -- This assumes the transfer price is marked up at each hop, including any TL costs -- Note that PII is a negative value so we add the two together -- ====================================================================== 1, nvl(to_org_costs.net_cost,0), 2, nvl(to_org_costs.net_cost,0), nvl(to_org_costs.net_cost,0) -- End revision for version 1.1, 1.3 ) - to_org_costs.pii_cost,5) PII_Cost_Difference from bom_components_b comp, bom_structures_b bom, mtl_system_items_vl msiv, -- Revision for version 1.4 mtl_units_of_measure_vl muomv_bom, mtl_item_status_vl misv_bom, mfg_lookups ml_bom, mfg_lookups ml_bom2, mfg_lookups ml_comp, mfg_lookups ml_comp2, mfg_lookups ml_comp3, fnd_common_lookups fcl_bom, fnd_common_lookups fcl_comp, -- End revision for version 1.4 gl_periods gp, -- Get the Item_Revisions (select max(mir.revision) revision_code, mir.inventory_item_id inventory_item_id, mir.organization_id organization_id from mtl_item_revisions_b mir where mir.effectivity_date <= sysdate group by mir.inventory_item_id, mir.organization_id ) mir, -- Source of Components (select mp_to_org.organization_code to_org_code, mp_src_org.organization_code src_org_code, mp_to_org.organization_id to_org_id, mp_src_org.organization_id src_org_id, mas.assignment_set_name assignment_set, msr.sourcing_rule_name sourcing_rule, msiv.segment1 comp_item_number, msiv.inventory_item_id comp_item_id, msiv.primary_uom_code comp_uom_code, msiv.description comp_description, msiv.inventory_item_status_code item_status_code, msiv.item_type comp_item_type, -- Revision for version 1.3 msiv.planning_make_buy_code to_org_mb_code from mrp_sr_source_org msso, mrp_sr_receipt_org msro, mrp_sourcing_rules msr, mrp_sr_assignments msa, mrp_assignment_sets mas, mtl_system_items_vl msiv, mtl_parameters mp_to_org, mtl_parameters mp_src_org -- ==================================== -- Sourcing_Rule Joins -- ==================================== 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 and msa.assignment_set_id = mas.assignment_set_id -- Want the source organization item information and msiv.organization_id = msso.source_organization_id and msiv.inventory_item_id = msa.inventory_item_id -- Only choose organization sourcing rules and msso.source_organization_id is not null -- Client only has one Assignment_Set and 1=1 -- p_assignment_set and 2=2 -- p_src_org, p_to_org -- Don't report obsolete or inactive items and msiv.inventory_item_status_code <> 'Inactive' -- ==================================== -- Material Parameter joins -- ==================================== and mp_to_org.organization_id = msa.organization_id and mp_src_org.organization_id = msso.source_organization_id union all select mp_to_org.organization_code to_org_code, mp_src_org.organization_code src_org_code, mp_to_org.organization_id to_org_id, msiv.source_organization_id src_org_id, '' assignment_set, '' sourcing_rule, msiv.segment1 comp_item_number, msiv.inventory_item_id comp_item_id, msiv.primary_uom_code comp_uom_code, msiv.description comp_description, msiv.inventory_item_status_code comp_item_status, msiv.item_type comp_item_type, -- Revision for version 1.3 msiv.planning_make_buy_code to_org_mb_code from mtl_system_items_vl msiv, mtl_parameters mp_to_org, mtl_parameters mp_src_org -- ==================================== -- Sourcing_Rule Joins -- ==================================== where msiv.organization_id = mp_to_org.organization_id and msiv.source_organization_id is not null -- Don't report obsolete or inactive items and msiv.inventory_item_status_code <> 'Inactive' -- ==================================== -- Material Parameter joins for to_org -- ==================================== and msiv.organization_id = mp_to_org.organization_id and msiv.source_organization_id = mp_src_org.organization_id and msiv.organization_id <> mp_to_org.master_organization_id and msiv.source_organization_id <> mp_src_org.master_organization_id and 2=2 -- p_src_org, p_to_org and not exists ( select 'x' 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 1=1 -- 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 -- ==================================== -- Material Parameter joins for to_org -- ==================================== and msa.organization_id = mp_to_org.organization_id and msso.source_organization_id = mp_src_org.organization_id ) ) item_sourcing, -- ================================================= -- Revision for version 1.1, add PII amounts -- Get To_Org Cost information -- ================================================= (select cic.organization_id, cic.inventory_item_id, cic.cost_type_id, cic.item_cost, cic.material_cost, cic.tl_material_overhead, cic.tl_resource, cic.tl_outside_processing, cic.tl_overhead, cic.item_cost - cic.tl_material_overhead - cic.tl_resource - cic.tl_outside_processing - cic.tl_overhead net_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 3=3 -- p_pii_cost_type, p_pii_resource_code and cicd.inventory_item_id = cic.inventory_item_id and cicd.organization_id = cic.organization_id and cicd.resource_id = br.resource_id ),0) pii_cost from cst_item_costs cic, cst_cost_types cct -- ==================================== -- Item_Cost Joins for the To_Org -- ==================================== where cic.cost_type_id = cct.cost_type_id and 4=4 -- p_cost_type union all select cic.organization_id, cic.inventory_item_id, cic.cost_type_id, cic.item_cost, cic.material_cost, cic.tl_material_overhead, cic.tl_resource, cic.tl_outside_processing, cic.tl_overhead, cic.item_cost - cic.tl_material_overhead - cic.tl_resource - cic.tl_outside_processing - cic.tl_overhead net_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 3=3 -- p_pii_cost_type, p_pii_resource_code and cicd.inventory_item_id = cic.inventory_item_id and cicd.organization_id = cic.organization_id and cicd.resource_id = br.resource_id ),0) pii_cost from cst_item_costs cic, cst_cost_types cct, mtl_parameters mp -- ==================================== -- Item_Cost Joins for the To_Org -- ==================================== where cic.organization_id = mp.organization_id and cic.cost_type_id = mp.primary_cost_method -- this gets the Frozen Costs and cct.cost_type_id <> mp.primary_cost_method -- this avoids getting the Frozen costs twice and 4=4 -- p_cost_type -- ==================================== -- Find all the Frozen costs not in the -- Pending or unimplemented cost type -- ==================================== and not exists ( select 'x' from cst_item_costs cic2 where cic2.organization_id = cic.organization_id and cic2.inventory_item_id = cic.inventory_item_id and cic2.cost_type_id = cct.cost_type_id) ) to_org_costs, -- ================================================= -- Get Source Org Cost information -- ================================================= (select cic.organization_id, cic.inventory_item_id, cic.cost_type_id, cic.item_cost, cic.material_cost, cic.tl_material_overhead, cic.tl_resource, cic.tl_outside_processing, cic.tl_overhead, cic.item_cost - cic.tl_material_overhead - cic.tl_resource - cic.tl_outside_processing - cic.tl_overhead net_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 3=3 -- p_pii_cost_type, p_pii_resource_code and cicd.inventory_item_id = cic.inventory_item_id and cicd.organization_id = cic.organization_id and cicd.resource_id = br.resource_id ),0) pii_cost from cst_item_costs cic, cst_cost_types cct -- ==================================== -- Item_Cost Joins for the Source Org -- ==================================== where cic.cost_type_id = cct.cost_type_id and 4=4 -- p_cost_type union all select cic.organization_id, cic.inventory_item_id, cic.cost_type_id, cic.item_cost, cic.material_cost, cic.tl_material_overhead, cic.tl_resource, cic.tl_outside_processing, cic.tl_overhead, cic.item_cost - cic.tl_material_overhead - cic.tl_resource - cic.tl_outside_processing - cic.tl_overhead net_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 3=3 -- p_pii_cost_type, p_pii_resource_code and cicd.inventory_item_id = cic.inventory_item_id and cicd.organization_id = cic.organization_id and cicd.resource_id = br.resource_id ),0) pii_cost from cst_item_costs cic, cst_cost_types cct, mtl_parameters mp -- ==================================== -- Item_Cost Joins for the Source Org -- ==================================== where cic.organization_id = mp.organization_id and cic.cost_type_id = mp.primary_cost_method -- this gets the Frozen Costs and cct.cost_type_id <> mp.primary_cost_method -- this avoids getting the Frozen costs twice and 4=4 -- p_cost_type -- ==================================== -- Find all the Frozen costs not in the -- Pending or unimplemented cost type -- ==================================== and not exists ( select 'x' from cst_item_costs cic2 where cic2.organization_id = cic.organization_id and cic2.inventory_item_id = cic.inventory_item_id and cic2.cost_type_id = cct.cost_type_id) ) src_org_costs, -- ================================================= -- Get To_Org Currency information -- ================================================= (select mp.organization_id, gl.ledger_id, gl.name gl_name, gl.currency_code, haou2.organization_id operating_unit_id, haou2.name operating_unit, -- Revision for version 1.4 gl. |