CAC Calculate Average Item Costs

➜ Back to Library
Description
Categories: Enginatics
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 ...  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 Cost Type. And if planning to use roll up these item costs, to avoid doubling up rolled up material costs on assemblies, choose Yes for the parameter Exclude Rolled Up Items, to avoid having manually defined material costs and rolled up material costs.

Parameters:
===========
Transaction Date From: enter the starting transaction date for PO Receipt History (mandatory).
Transaction Date To: enter the ending transaction date for PO Receipt History (mandatory).
Comparison Cost Type: enter the cost type to compare against the calculated average item costs (mandatory). If the costs are not found in the Comparison Cost Type get them from the Costing Method Cost Type.
Use Default Material Overheads: set to Yes to report the material overheads based on the Default Material Overhead setups. Set to No to use the material overheads from the specified Comparison Cost type (mandatory).
Only Active Rates: set to Yes to only get active, enabled default material overhead rates (mandatory).
Exclude Rolled Up Items: to avoid doubling up rolled up material costs, choose Yes for this parameter, to avoid having manually defined material costs and rolled up material costs (mandatory).
Currency Conversion Date: the exchange rate conversion date that was used to set the standard costs (mandatory).
Currency Conversion Type: the exchange rate conversion type that was used to set the standard costs (mandatory).
Show Last AP Invoice: Enter Yes to show the last AP invoice date, number and price (mandatory).
Category Set 1: any item category you wish, typically the Cost or Product Line category set (optional).
Category Set 2: any item category you wish, typically the Inventory category set (optional).
Supplier Type to Exclude: enter the specific supplier(s) you wish to exclude from the average cost calculations (optional).
Item Status to Exclude: enter the item number status you want to exclude. Defaulted to 'Inactive' (optional).
Item Number: enter the specific item number(s) you wish to report (optional).
Organization Code: enter the specific inventory organization(s) you wish to report (optional).
Operating Unit: enter the specific operating unit(s) you wish to report (optional).
Ledger: enter the specific ledger(s) you wish to report (optional).

/*
-- | Copyright 2006 - 2023 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.
-- |
-- | Version Modified on Modified by Description
-- | ======= =========== ============== =========================================
-- | 1.0 05 Jan 2019 Douglas Volz Initial Coding based on item_cost_history.sql
-- | 1.14 07 Dec 2022 Douglas Volz Performance improvements. Added Item Status parameter.
-- | 1.15 10 Jan 2023 Douglas Volz Fix for Average Cost calculations, UOM issue.
-- | 1.16 21 Aug 2023 Douglas Volz Remove tabs and restrict to only orgs you have access to,
-- | Add last payables invoice and purchase order price information.
-- | 1.17 25 Sep 2023 Douglas Volz Fix for the Last PO and Last A/P information
-- | 1.18 13 Oct 2023 Andy Haack Fix for G/L Daily Rates, outer joins
-- | 1.19 24 Nov 2023 Douglas Volz Fixed item number parameter for last PO information
-- | and fix Last A/P Invoice info for G/L Daily Rates
-- +=============================================================================+*/   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