CAC PO Price vs. Costing Method Comparison

➜ Back to Library
Description
Categories: Enginatics
Repository: Github
Report to compare the open purchase order lines and unit prices with the costing method item cost in Oracle (Average, Standard, FIFO or LIFO). Used by the buyers and cost accounting to check the accuracy of the recently created purchase orders and run using a range of purchase order line creation dates. Foreign currency purchase orders convert to the inventory organization's currency by either u ...  Report to compare the open purchase order lines and unit prices with the costing method item cost in Oracle (Average, Standard, FIFO or LIFO). Used by the buyers and cost accounting to check the accuracy of the recently created purchase orders and run using a range of purchase order line creation dates. Foreign currency purchase orders convert to the inventory organization's currency by either using the original purchase order exchange rate, if the Invoice Match Option is "Purchase Order" or by using the latest exchange rate date if the Invoice Match Option is "Receipt".

/* +=============================================================================+
-- | Copyright 2006-2020 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_po_vs_costing_method_rept.sql
-- |
-- | Parameters:
-- | p_creation_date_from -- starting creation date for po line shipment/delivery info.
-- | p_creation_date_to -- ending creation date for po line shipment/delivery info.
-- | p_ledger -- ledger parameter, optional
-- | p_min_value_diff -- minimum value difference between the PO amount or value
-- | and the costing method value (cost X quantity)
-- | p_category_set1 -- The first item category set to report, typically the
-- | Cost or Product Line Category Set
-- | p_category_set2 -- The second item category set to report, typically the
-- | Inventory Category Set
-- |
-- | Description:
-- | Report to compare the open purchase order lines and unit prices with
-- | the current costing method item cost in Oracle (average, standard, FIFO or LIFO).
-- | Used by the buyers and cost accounting to check the accuracy of the open
-- | purchase orders.
-- |
-- |
-- | Version Modified on Modified by Description
-- | ======= =========== ============== =========================================
-- | 1.0 02 APR 2006 Douglas Volz Initial Coding
-- | 1.17 13 Jun 2017 Douglas Volz Added OSP Resource Code
-- | 1.18 19 Aug 2019 Douglas Volz Removed non-generic item categories
-- | 1.19 27 Jan 2020 Douglas Volz Changed to multi-language views for the item
-- | master, inventory orgs and operating units.
-- | Added project number to report.
-- | 1.20 Add these columns: PO Need By Date, PO Promise Date,
-- | PO Expected Receipt Date, Target Price (PO List Price),
-- | Customer Name (description for category set 1).
-- | And added Minimum Cost Difference parameter.
+=============================================================================+*/
   more
Run CAC PO Price vs. Costing Method Comparison and other Oracle EBS reports with Blitz Report™ on our demo environment
select nvl(gl.short_name, gl.name) Ledger,
 haou2.name Operating_Unit,
 mp.organization_code Org_Code,
 pv.vendor_name Supplier,
 emp.full_name Buyer,
 msiv.concatenated_segments Item_Number,
 msiv.description Item_Description,
 fcl.meaning Item_Type,
 -- Revision for version 1.19
 -- msiv.inventory_item_status_code Item_Status,
 misv.inventory_item_status_code Item_Status,
 ml.meaning Make_Buy_Code,
&category_columns
 -- Revision for version 1.17
 cic.resource_code OSP_Resource,
 pl.vendor_product_num Supplier_Item,
 -- Revision for version 1.20
 round(msiv.list_price_per_unit,5) Target_or_List_Price,
 ph.segment1 PO_Number,
 to_char(pl.line_num) PO_Line,
 -- Revision for version 1.19
 pp.segment1 Project_Number,
 pp.name Project_Name,
 -- Revision for version 1.20
 pll.creation_date Creation_Date,
 pll.promised_date Promised_Date,
 pll.need_by_date Need_by_Date,
 to_char(pr.release_num) PO_Release,
 pr.release_date Release_Date,
 (select max(ms.expected_delivery_date)
  from mtl_supply ms
  where ms.supply_type_code in ('PO','RECEIVING','SHIPMENT')
  and ms.item_id                      = msiv.inventory_item_id
  and ms.to_organization_id           = msiv.organization_id
  and ms.destination_type_code        in ('INVENTORY','SHOP FLOOR')
  and ms.po_distribution_id           = pod.po_distribution_id
 ) Expected_Receipt_Date,
 -- End revision for version 1.20
 -- Revision for version 1.19
 -- pll.unit_meas_lookup_code PO_UOM,
 muomv_po.uom_code PO_UOM,
 pll.quantity PO_Quantity,
 pll.quantity_received PO_Quantity_Received,
 nvl(ph.currency_code, gl.currency_code) PO_Currency_Code,
 nvl(pll.price_override, pl.unit_price) PO_Unit_Price,
 -- Revision for version 1.20
 -- pod.rate_date Currency_Rate_Date,
 -- nvl(pod.rate,1) PO_Exchange_Rate,
 decode(pll.match_option, 
  'P', trunc(nvl(pod.rate_date, pll.creation_date)),
  'R', trunc(nvl(gdr1.conversion_date, sysdate)),
  trunc(nvl(gdr1.conversion_date, sysdate))) Currency_Rate_Date,
 decode(pll.match_option, 
  'P', nvl(pod.rate,1),
  'R', gdr1.conversion_rate,
  gdr1.conversion_rate) PO_Exchange_Rate,
 gl.currency_code GL_Currency_Code,
 decode(pll.match_option, 
  'P', nvl(pod.rate,1),
  'R', gdr1.conversion_rate,
  gdr1.conversion_rate) * nvl(pll.price_override, pl.unit_price) Converted_PO_Unit_Price,
 ucr.conversion_rate PO_UOM_Conversion_Rate,
 decode(pll.match_option, 
  'P', nvl(pod.rate,1),
  'R', gdr1.conversion_rate,
  gdr1.conversion_rate) * nvl(pll.price_override, pl.unit_price) * ucr.conversion_rate Converted_PO_at_Primary_UOM,
 -- End revision for version 1.20
 -- Revision for version 1.19
 -- msiv.primary_uom_code UOM_Code,
 muomv_msi.uom_code UOM_Code,
 -- Revision for version 1.21
 cic.cost_type Cost_Type,
 nvl(cic.unburdened_cost,0) Unburdened_Unit_Cost,
 -- Revision for version 1.20
 -- PO Price - Unburdened Cost = Unit Cost Difference
 round((decode(pll.match_option, 
  'P', nvl(pod.rate,1),
  'R', gdr1.conversion_rate,
  gdr1.conversion_rate) * 
  nvl(pll.price_override, pl.unit_price)) - nvl(cic.unburdened_cost,0),5) Unit_Cost_Difference,
 -- PO Price - Unburdened Cost X Quantity = Extended Cost Difference
 round((decode(pll.match_option, 
  'P', nvl(pod.rate,1),
  'R', gdr1.conversion_rate,
  gdr1.conversion_rate) * nvl(pll.price_override, pl.unit_price) -
  nvl(cic.unburdened_cost,0)) * nvl(pll.quantity,0),2) Extended_Cost_Difference,
 -- Revision for version 1.20
 -- round(((nvl(ph.rate,1) * nvl(pll.price_override, pl.unit_price)) - nvl(cic.unburdened_cost,0)) /
 --  decode(nvl(cic.unburdened_cost,0),0,1,nvl(cic.unburdened_cost,0)) * 100,1) Percent,
 -- Calculate the Percent Difference
 -- when PO price - item cost = 0 then 0
 -- when item cost = 0 then 100 * SIGN PO price
 -- when PO price  = 0 then -100 * SIGN item cost
 -- else (PO price - item cost) / item cost
 case
    when round(decode(pll.match_option, 
   'P', nvl(pod.rate,1), 
   'R', gdr1.conversion_rate,
   gdr1.conversion_rate
       ) * nvl(pll.price_override, pl.unit_price) - nvl(cic.unburdened_cost,0),5) = 0
  then 0
    when round(nvl(cic.unburdened_cost,0),5) = 0
  then 100 * SIGN(nvl(pll.price_override, pl.unit_price))
    when round(decode(pll.match_option, 
   'P', nvl(pod.rate,1), 
   'R', gdr1.conversion_rate,
   gdr1.conversion_rate
       ) * nvl(pll.price_override, pl.unit_price),5) = 0
  then -100 * SIGN(nvl(cic.unburdened_cost,0))
    else round(decode(pll.match_option, 
  'P', nvl(pod.rate,1), 
  'R', gdr1.conversion_rate,
  gdr1.conversion_rate) * nvl(pll.price_override, pl.unit_price) - nvl(cic.unburdened_cost,0) /
  nvl(cic.unburdened_cost,0) * 100,2)
 end Percent_Difference,
 -- End revision for version 1.20
 -- Revision for version 1.21
 cic.cost_type               Cost_Type,
 cic.material_cost           Material_Cost,
 cic.material_overhead_cost  Material_Overhead_Cost,
 cic.resource_cost           Resource_Cost,
 cic.outside_processing_cost Outside_Processing_Cost,
 cic.overhead_cost           Overhead_Cost,
 cic.item_cost               Item_Cost
from po_headers_all              ph,
 po_lines_all                pl,
 po_line_locations_all       pll,
 po_distributions_all        pod,
 po_releases_all             pr,
 po_vendors                  pv,
 -- Revision for version 1.19
 pa_projects_all        pp,
 mtl_system_items_vl         msiv,
 mtl_uom_conversions_view    ucr,
 -- Revision for version 1.19
 mtl_units_of_measure_vl     muomv_po,
 mtl_units_of_measure_vl     muomv_msi,
 mtl_item_status_vl          misv,
 -- End revision for version 1.10
 mfg_lookups                 ml,
 fnd_common_lookups          fcl,
 (select cct.cost_type,
  msiv.inventory_item_id inventory_item_id,
  crc.organization_id organization_id,
  -- Revision for version 1.17
  br.resource_code,
  0 material_cost,
  0 material_overhead_cost,
  0 resource_cost,
  nvl(crc.resource_rate,0) outside_processing_cost,
  0 overhead_cost,
  nvl(crc.resource_rate,0) unburdened_cost,
  nvl(crc.resource_rate,0) item_cost
  from cst_resource_costs  crc,
  cst_cost_types      cct,
  bom_resources       br,
  mtl_system_items_vl msiv,
  mtl_parameters      mp
  where  crc.cost_type_id      = cct.cost_type_id
  and    crc.resource_id       = br.resource_id
  and    crc.organization_id   = br.organization_id
  and    crc.organization_id   = mp.organization_id
  and    br.purchase_item_id   = msiv.inventory_item_id
  and    br.organization_id    = msiv.organization_id
  and mp.organization_id    = msiv.organization_id
  and msiv.item_type        = 'OP'
  and cct.cost_type_id      = mp.primary_cost_method
  and mp.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id)
  and 6=6                   -- p_org_code
  -- Revision for version 1.21
  -- union all
  union
  select cct.cost_type,
  cic.inventory_item_id inventory_item_id,
  cic.organization_id organization_id,
  -- Revision for version 1.17
  null resource_code,
  nvl(cic.material_cost,0) material_cost,
  nvl(cic.material_overhead_cost,0) material_overhead_cost,
  nvl(cic.resource_cost,0) resource_cost,
  nvl(cic.outside_processing_cost,0) outside_processing_cost,
  nvl(cic.overhead_cost,0) overhead_cost,
  nvl(cic.unburdened_cost,0) unburdened_cost,
  nvl(cic.item_cost,0) item_cost
  from cst_item_costs      cic,
  cst_cost_types      cct,
  mtl_system_items_vl msiv,
  mtl_parameters      mp
  where cic.organization_id   = msiv.organization_id
  and mp.organization_id    = msiv.organization_id
  and    cic.inventory_item_id = msiv.inventory_item_id
  and msiv.item_type        <> 'OP'
  and    cic.cost_type_id      = cct.cost_type_id
  and cct.cost_type_id      = mp.primary_cost_method
  and mp.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id)
  and 6=6                   -- p_org_code
 ) cic, -- costs per the Cost Method
 hr_employees                 emp,
 mtl_parameters               mp,
 hr_locations                 hl,
 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.20
 -- ===========================================================================
 -- Select current Currency Rates based on the concurrency conversion date
 -- ===========================================================================
 (select gdr1.from_currency,
  gdr1.to_currency,
  gdct1.user_conversion_type,
  gdr1.conversion_date,
  gdr1.conversion_rate
  from gl_daily_rates gdr1,
  gl_daily_conversion_types gdct1
  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 gdr1.to_currency              = gl.currency_code
   -- Do not report the master inventory organization
   and mp.organization_id           <> mp.master_organization_id
      )
  and gdr1.conversion_type       = gdct1.conversion_type
  and 4=4                        -- p_curr_conv_date1
  and 5=5                        -- p_curr_conv_type1
  union all
  select gl.currency_code,              -- from_currency
  gl.currency_code,              -- to_currency
  gdct1.user_conversion_type,    -- user_conversion_type
  :p_curr_conv_date1,            -- conversion_date                                             -- p_curr_conv_date1
  1                              -- conversion_rate
  from gl_ledgers gl,
  gl_daily_conversion_types gdct1
  where 5=5                            -- p_curr_conv_type1
  group by
  gl.currency_code,
  gl.currency_code,
  gdct1.user_conversion_type,                                                                  -- p_curr_conv_date1
  :p_curr_conv_date1,           -- conversion_date                                             -- p_curr_conv_date1
  1
 ) gdr1 -- Current Currency Rates
 -- End for revision 1.20
where ph.po_header_id                 = pl.po_header_id
and pl.po_line_id                   = pll.po_line_id
and pll.line_location_id            = pod.line_location_id
and pr.po_release_id (+)            = pod.po_release_id
and pll.closed_code                 =  'OPEN'
and pv.vendor_id                    = ph.vendor_id
-- Revision for version 1.19
and pp.project_id (+)               = pod.project_id
and muomv_po.unit_of_measure        = pll.unit_meas_lookup_code
and muomv_msi.uom_code              = msiv.primary_uom_code
and misv.inventory_item_status_code = msiv.inventory_item_status_code
-- End revision for version 1.19
and msiv.inventory_item_id          = ucr.inventory_item_id
and msiv.organization_id            = ucr.organization_id
and ucr.unit_of_measure             = pl.unit_meas_lookup_code
and pl.item_id                      = msiv.inventory_item_id
and cic.inventory_item_id           = msiv.inventory_item_id
and cic.organization_id             = msiv.organization_id
and ml.lookup_type                  = 'MTL_PLANNING_MAKE_BUY'
and ml.lookup_code                  = msiv.planning_make_buy_code
and fcl.lookup_type (+)             = 'ITEM_TYPE'
and fcl.lookup_code (+)             = msiv.item_type
and ph.agent_id                     = emp.employee_id
and mp.organization_id              = msiv.organization_id
and pll.ship_to_location_id         = hl.ship_to_location_id
and msiv.organization_id            = pll.ship_to_organization_id
and 1=1 -- p_creation_date_from, p_creation_date_to, p_operating_unit, p_ledger
            -- p_min_value_diff, p_min_cost_diff
and 6=6 -- p_org_code
-- ===================================================================
-- Joins for the currency exchange rates
-- ===================================================================
-- Current FX rate
and ph.currency_code                = gdr1.from_currency
and gl.currency_code                = gdr1.to_currency
-- ===================================================================
-- Using the base tables instead of HR organization views
-- ===================================================================
and 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
-- ===================================================================
order by
 nvl(gl.short_name, gl.name), -- Ledger
 haou2.name, -- Operating_Unit
 mp.organization_code, -- Org_Code
 pv.vendor_name, -- Supplier
 msiv.concatenated_segments, -- Item_Number
 pl.vendor_product_num, -- Supplier_Item
 ph.segment1, -- PO_Number
 to_char(pl.line_num), -- PO_Line
 to_char(pr.release_num) -- PO_Rel
Parameter Name SQL text Validation
Creation Date From 
pll.creation_date >= :p_creation_date_from
 Date
Creation Date To 
pll.creation_date < :p_creation_date_to + 1
 Date
Minimum Value Difference 
abs(round((decode(pll.match_option,'P', nvl(pod.rate,1),'R', gdr1.conversion_rate, gdr1.conversion_rate)
  * nvl(pll.price_override, pl.unit_price) - nvl(cic.unburdened_cost,0)) * nvl(pll.quantity,0),2)) >= :p_min_value_diff
 Number
Minimum Cost Difference 
abs(round((decode(pll.match_option,'P', nvl(pod.rate,1),'R', gdr1.conversion_rate, gdr1.conversion_rate)
  * nvl(pll.price_override, pl.unit_price) - nvl(cic.unburdened_cost,0)),2)) >= :p_min_cost_diff
 Number
Currency Conversion Type 
gdct1.user_conversion_type = :p_curr_conv_type
 LOV
Currency Conversion Date 
gdr1.conversion_date = :p_curr_conv_date1
 Date
Category Set 1 
select x.* from (
select
'(select min(mcb.'||lower(fifsv.application_column_name)||') keep (dense_rank first order by mic.category_id) from mtl_item_categories mic, mtl_categories_b mcb where mic.category_set_id='||mcsv.category_set_id||' and msiv.organization_id=mic.organization_id and msiv.inventory_item_id=mic.inventory_item_id and mic.category_id=mcb.category_id) "'||substrb(fifsv.form_left_prompt_,1,xxen_report.max_column_length)||'",' sql_text
from
mtl_category_sets_vl mcsv,
(select xxen_util.init_cap(fifsv.form_left_prompt) form_left_prompt_, fifsv.* from fnd_id_flex_segments_vl fifsv) fifsv
where
mcsv.category_set_name=:category_set_name1 and
mcsv.structure_id=fifsv.id_flex_num and
fifsv.application_id=401 and
fifsv.id_flex_code='MCAT' and
fifsv.enabled_flag='Y'
order by
fifsv.id_flex_num,
fifsv.segment_num
) x
union all
select
'(select min(mct.description) keep (dense_rank first order by mic.category_id) from mtl_item_categories mic, mtl_categories_tl mct where mic.category_set_id='||mcsv.category_set_id||' and msiv.organization_id=mic.organization_id and msiv.inventory_item_id=mic.inventory_item_id and mic.category_id=mct.category_id and mct.language=userenv(''lang'')) "'||substrb(mcsv.category_set_name||' Description',1,xxen_report.max_column_length)||'",' sql_text
from
mtl_category_sets_vl mcsv
where
mcsv.category_set_name=:category_set_name1
 LOV
Category Set 2 
select x.* from (
select
'(select min(mcb.'||lower(fifsv.application_column_name)||') keep (dense_rank first order by mic.category_id) from mtl_item_categories mic, mtl_categories_b mcb where mic.category_set_id='||mcsv.category_set_id||' and msiv.orga