CAC Calculate Average Item Costs
Description
Categories: Enginatics
Repository: Github
Repository: Github
Using purchase order receipts, calculate average item costs over a specified transaction date range and compare against the Comparison Cost Type. In addition, with the Use Default Material Overheads parameter you can choose which material overheads to use on this report. Select Yes to use the Default Material Overhead setups. Set to No to use the material overheads from the specified Comparison ...
more
Run
CAC Calculate Average Item Costs and other Oracle EBS reports with Blitz Report™ on our demo environment
select nvl(gl.short_name, gl.name) Ledger, haou2.name Operating_Unit, cicd_moh.organization_code Org_Code, cicd_moh.concatenated_segments Item_Number, cicd_moh.description Item_Description, -- Revision for version 1.2 fcl.meaning Item_Type, -- Revision for version 1.10 ml1.meaning Make_Buy_Code, -- Revision for version 1.11 ml2.meaning Based_on_Rollup, -- Revision for version 1.6 misv.inventory_item_status_code_tl Item_Status, cic.lot_size Lot_Size, -- Revision for version 1.3 &category_columns -- Revision for version 1.6 muomv.uom_code UOM_Code, receipts.sum_primary_quantity Sum_Primary_Quantity, -- Revision for version 1.4 gl.currency_code Currency_Code, receipts.sum_po_receipts Sum_PO_Receipts, receipts.average_material_cost Average_Material_Cost, -- Revision for version 1.12 cic.cost_type Comparison_Cost_Type, -- Revision for version 1.7 round(nvl(cic.unburdened_cost,0),5) Comparison_Material_Cost, -- Difference = Avg Item Cost - Item Cost receipts.average_material_cost - round(nvl(cic.unburdened_cost,0),5) Material_Cost_Difference, -- Revision for version 1.10 -- case -- when difference = 0 then 0 -- when comparison item cost = 0 then 100% -- when average PO unit price = 0 then -100% -- else (average PO unit price - comparison item cost) / comparison item cost round(case when receipts.average_material_cost - nvl(cic.unburdened_cost,0) = 0 then 0 when nvl(cic.unburdened_cost,0) = 0 then 100 when receipts.average_material_cost = 0 then -100 else (receipts.average_material_cost - nvl(cic.unburdened_cost,0)) / nvl(cic.unburdened_cost,0) * 100 end,3) Percent_Difference, -- End of revision for version 1.7 and 1.10 -- Revision for version 1.10 -- Add in material overheads -- sum((so much per item) + (lot/lot size) + ((item cost - TL moh4) X rate)) decode(:p_use_default_matl_ovhds, 'Y', decode(cicd_moh.planning_make_buy_code, 1, nvl((round(default_moh.make_item_rate,5) + round(default_moh.make_lot_rate/decode(cic.lot_size,0, 1, null, 1, cic.lot_size),5) + round(receipts.average_material_cost * default_moh.make_total_value_rate,5) + round(default_moh.make_activity_rate,5) + round(default_moh.all_item_rate,5) + round(default_moh.all_lot_rate/decode(cic.lot_size,0, 1, null, 1, cic.lot_size),5) + round(receipts.average_material_cost * default_moh.all_total_value_rate,5) + round(default_moh.all_activity_rate,5)),0), 2, nvl((round(default_moh.buy_item_rate,5) + round(default_moh.buy_lot_rate/decode(cic.lot_size,0, 1, null, 1, cic.lot_size),5) + round(receipts.average_material_cost * default_moh.buy_total_value_rate,5) + round(default_moh.buy_activity_rate,5) + round(default_moh.all_item_rate,5) + round(default_moh.all_lot_rate/decode(cic.lot_size,0, 1, null, 1, cic.lot_size),5) + round(receipts.average_material_cost * default_moh.all_total_value_rate,5) + round(default_moh.all_activity_rate,5)),0)), 'N', nvl((round(cicd_moh.item_rate,5) + round(cicd_moh.lot_rate/decode(cic.lot_size,0, 1, null, 1, cic.lot_size),5) + round(receipts.average_material_cost * cicd_moh.total_value_rate,5) + round(cicd_moh.activity_rate,5)),0) ,0) Material_Overhead_Cost, receipts.average_material_cost + decode(:p_use_default_matl_ovhds, 'Y', decode(cicd_moh.planning_make_buy_code, 1, nvl((round(default_moh.make_item_rate,5) + round(default_moh.make_lot_rate/decode(cic.lot_size,0, 1, null, 1, cic.lot_size),5) + round(receipts.average_material_cost * default_moh.make_total_value_rate,5) + round(default_moh.make_activity_rate,5) + round(default_moh.all_item_rate,5) + round(default_moh.all_lot_rate/decode(cic.lot_size,0, 1, null, 1, cic.lot_size),5) + round(receipts.average_material_cost * default_moh.all_total_value_rate,5) + round(default_moh.all_activity_rate,5)),0), 2, nvl((round(default_moh.buy_item_rate,5) + round(default_moh.buy_lot_rate/decode(cic.lot_size,0, 1, null, 1, cic.lot_size),5) + round(receipts.average_material_cost * default_moh.buy_total_value_rate,5) + round(default_moh.buy_activity_rate,5) + round(default_moh.all_item_rate,5) + round(default_moh.all_lot_rate/decode(cic.lot_size,0, 1, null, 1, cic.lot_size),5) + round(receipts.average_material_cost * default_moh.all_total_value_rate,5) + round(default_moh.all_activity_rate,5)),0)), 'N', nvl((round(cicd_moh.item_rate,5) + round(cicd_moh.lot_rate/decode(cic.lot_size,0, 1, null, 1, cic.lot_size),5) + round(receipts.average_material_cost * cicd_moh.total_value_rate,5) + round(cicd_moh.activity_rate,5)),0) ,0) Total_Average_Item_Cost, nvl(cic.item_cost,0) Total_Comparison_Item_Cost, receipts.average_material_cost + decode(:p_use_default_matl_ovhds, 'Y', decode(cicd_moh.planning_make_buy_code, 1, nvl((round(default_moh.make_item_rate,5) + round(default_moh.make_lot_rate/decode(cic.lot_size,0, 1, null, 1, cic.lot_size),5) + round(receipts.average_material_cost * default_moh.make_total_value_rate,5) + round(default_moh.make_activity_rate,5) + round(default_moh.all_item_rate,5) + round(default_moh.all_lot_rate/decode(cic.lot_size,0, 1, null, 1, cic.lot_size),5) + round(receipts.average_material_cost * default_moh.all_total_value_rate,5) + round(default_moh.all_activity_rate,5)),0), 2, nvl((round(default_moh.buy_item_rate,5) + round(default_moh.buy_lot_rate/decode(cic.lot_size,0, 1, null, 1, cic.lot_size),5) + round(receipts.average_material_cost * default_moh.buy_total_value_rate,5) + round(default_moh.buy_activity_rate,5) + round(default_moh.all_item_rate,5) + round(default_moh.all_lot_rate/decode(cic.lot_size,0, 1, null, 1, cic.lot_size),5) + round(receipts.average_material_cost * default_moh.all_total_value_rate,5) + round(default_moh.all_activity_rate,5)),0)), 'N', nvl((round(cicd_moh.item_rate,5) + round(cicd_moh.lot_rate/decode(cic.lot_size,0, 1, null, 1, cic.lot_size),5) + round(receipts.average_material_cost * cicd_moh.total_value_rate,5) + round(cicd_moh.activity_rate,5)),0) ,0) - nvl(cic.item_cost,0) Total_Cost_Difference, -- Revision for version 1.16 po.Last_Purchase_Order, po.Last_PO_Line, po.Last_PO_Date, po.Last_PO_Currency_Code, po.Last_PO_Price, po.Converted_Last_PO_Price, &last_ap_invoice_columns receipts.list_price Item_Master_List_Price -- End revision for version 1.16 from -- Revision for version 1.6 mtl_units_of_measure_vl muomv, mtl_item_status_vl misv, -- End revision for version 1.6 -- Revision for version 1.2 fnd_common_lookups fcl, -- Revision for version 1.10 mfg_lookups ml1, -- planning make/buy code, MTL_PLANNING_MAKE_BUY mfg_lookups ml2, -- based on rollup, CST_BONROLLUP_VAL hr_organization_information hoi, hr_all_organization_units_vl haou, -- inv_organization_id hr_all_organization_units_vl haou2, -- operating unit gl_ledgers gl, -- Revision for version 1.10 -- Calculate the Average PO Receipt Costs -- Add Hint from Oracle's 12.2.4 package cstpmecs, procedure cstpapor (select /*+ NO_UNNEST */ mmt.organization_id, mmt.inventory_item_id, sum(mmt.primary_quantity) sum_primary_quantity, -- Revision for version 1.15 -- PO Unit Price * Conversion to Primary UOM * Primary Quantity -- round(sum(nvl(mmt.transaction_cost,0) * mmt.transaction_quantity/mmt.primary_quantity * mmt.primary_quantity),2) sum_po_receipts, -- round(sum(nvl(mmt.transaction_cost,0) * mmt.transaction_quantity/mmt.primary_quantity * mmt.primary_quantity) / -- decode(sum(mmt.transaction_quantity * mmt.transaction_quantity/mmt.primary_quantity), -- 0,1, -- sum(mmt.transaction_quantity * mmt.transaction_quantity/mmt.primary_quantity) -- ),5) average_material_cost -- PO Unit Price in Primary UOM * Primary Quantity round(sum(nvl(mmt.transaction_cost,0) * mmt.primary_quantity),2) sum_po_receipts, round(sum(nvl(mmt.transaction_cost,0) * mmt.primary_quantity) / decode(sum(mmt.primary_quantity), 0,1, sum(mmt.primary_quantity) ),5) average_material_cost, -- End revision for version 1.15 -- Revision for version 1.16 msiv.list_price_per_unit list_price from mtl_material_transactions mmt, rcv_transactions rt, mtl_system_items_vl msiv, mtl_parameters mp, po_vendors pv, fnd_lookup_values_vl flvv -- Revision for version 1.10, from Oracle's 12.2.4 Cost Mass Edit for PO Receipts, package cstpmecs, procedure cstpapor -- mmt.transaction_source_type_id = 1 -- purchase orders where ( ( mmt.transaction_source_type_id = 1 and mmt.transaction_action_id = 27 and mmt.transaction_type_id = 18 -- PO Receipts ) or ( mmt.transaction_source_type_id = 1 and mmt.transaction_action_id = 29 and mmt.transaction_type_id = 71 -- PO Rcpt Adjust ) or ( mmt.transaction_source_type_id = 1 and mmt.transaction_action_id = 1 and mmt.transaction_type_id = 36 -- Return to Vendor ) or ( mmt.transaction_source_type_id = 1 and mmt.transaction_action_id = 6 and mmt.transaction_type_id = 74 -- Transfer for Regular ) or ( mmt.transaction_source_type_id = 13 and mmt.transaction_action_id = 6 and mmt.transaction_type_id = 75 -- Transfer to Consigned ) ) and mmt.rcv_transaction_id = rt.transaction_id and mmt.inventory_item_id = msiv.inventory_item_id and mmt.organization_id = msiv.organization_id and msiv.inventory_asset_flag = 'Y' -- Revision for version 1.14 -- and msiv.inventory_item_status_code <> 'Inactive' and 9=9 -- p_item_status_to_exclude -- End revision for version 1.14 and mmt.organization_id = mp.organization_id and nvl(mmt.transaction_cost,0) <> 0 -- Revision for version 1.9 and pv.vendor_id (+) = rt.vendor_id and flvv.lookup_code (+) = pv.vendor_type_lookup_code -- Revision for version 1.13 and flvv.lookup_type = 'VENDOR TYPE' -- Revision for version 1.16 and mp.organization_code in (select oav.organization_code from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id) and 2=2 -- p_org_code and 3=3 -- p_trx_date_from, p_trx_date_to, p_exclude_supplier_type and 5=5 -- p_item_number group by mmt.organization_id, mmt.inventory_item_id, -- Revision for version 1.16 msiv.list_price_per_unit -- Revision for version 1.11 -- Avoid negative quantities due to returns having sum(mmt.primary_quantity) > 0 ) receipts, -- Revision for version 1.12 -- Get the comparison item costs by cost type and item (select cic.cost_type_id, cct.cost_type, -- Revision for version 1.14 -- msiv.organization_id, -- msiv.inventory_item_id, -- msiv.inventory_asset_flag, cic.organization_id, cic.inventory_item_id, -- End revision for version 1.14 cic.lot_size, cic.unburdened_cost, cic.item_cost, nvl(cic.based_on_rollup_flag,2) based_on_rollup_flag from cst_cost_types cct, cst_item_costs cic, -- Revision for version 1.14 -- mtl_system_items_vl msiv, mtl_parameters mp -- Revision for version 1.14 -- where mp.organization_id = msiv.organization_id -- and msiv.organization_id = cic.organization_id -- and msiv.inventory_item_id = cic.inventory_item_id -- and msiv.inventory_asset_flag = 'Y' -- and msiv.inventory_item_status_code <> 'Inactive' where mp.organization_id = cic.organization_id -- End revision for version 1.14 -- Revision for version 1.16 and mp.organization_code in (select oav.organization_code from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id) and cct.cost_type_id = cic.cost_type_id and 2=2 -- p_org_code and 4=4 -- p_cost_type and 7=7 -- p_item_number and 6=6 -- p_exclude_rolled_up_items union all select cic.cost_type_id, cct_primary.cost_type, -- Revision for version 1.14 -- msiv.organization_id, -- msiv.inventory_item_id, -- msiv.inventory_asset_flag, cic.organization_id, cic.inventory_item_id, -- End revision for version 1.14 cic.lot_size, cic.unburdened_cost, cic.item_cost, nvl(cic.based_on_rollup_flag,2) based_on_rollup_flag from cst_item_costs cic, cst_cost_types cct, cst_cost_types cct_primary, -- Revision for version 1.14 -- mtl_system_items_vl msiv, mtl_parameters mp -- Revision for version 1.14 -- where mp.organization_id = msiv.organization_id -- and msiv.organization_id = cic.organization_id -- and msiv.inventory_item_id = cic.inventory_item_id -- and msiv.inventory_asset_flag = 'Y' -- and msiv.inventory_item_status_code <> 'Inactive' where mp.organization_id = cic.organization_id -- End revision for version 1.14 -- Revision for version 1.16 and mp.organization_code in (select oav.organization_code from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_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 costs twice and cct_primary.cost_type_id = mp.primary_cost_method and 2=2 -- p_org_code and 4=4 -- p_cost_type and |