CAC PO Price vs. Costing Method Comparison

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
select
 pll.Ledger,
 pll.Operating_Unit,
 pll.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, pll.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)), trunc(nvl(gdr1.conversion_date, sysdate))) Currency_Rate_Date,
 decode(pll.match_option, 
  'P', nvl(pod.rate,1), nvl(gdr1.conversion_rate,1)) PO_Exchange_Rate,
 pll.gl_currency_code,
 decode(pll.match_option, 
  'P', nvl(pod.rate,1), nvl(gdr1.conversion_rate,1)) * 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), nvl(gdr1.conversion_rate,1)) * 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), nvl(gdr1.conversion_rate,1)) * 
  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), nvl(gdr1.conversion_rate,1)) * 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), nvl(gdr1.conversion_rate,1)
       ) * 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), nvl(gdr1.conversion_rate,1)
       ) * 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), nvl(gdr1.conversion_rate,1)) * 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,
(
select
pha.currency_code currency_code,
gl.currency_code gl_currency_code,
nvl(gl.short_name, gl.name) Ledger,
haouv.name operating_unit,
ood.organization_code,
pll.*
from
po_line_locations_all pll,
po_headers_all pha,
org_organization_definitions ood,
gl_ledgers gl,
hr_all_organization_units_vl haouv
where
gl.ledger_id in (select nvl(glsnav.ledger_id,gasna.ledger_id) from gl_access_set_norm_assign gasna, gl_ledger_set_norm_assign_v glsnav where gasna.access_set_id=fnd_profile.value('GL_ACCESS_SET_ID') and gasna.ledger_id=glsnav.ledger_set_id(+))
and haouv.organization_id in (select mgoat.organization_id from mo_glob_org_access_tmp mgoat union select fnd_global.org_id from dual where fnd_release.major_version=11)
and 2=2 and
pll.po_header_id=pha.po_header_id and
pll.ship_to_organization_id=ood.organization_id and
ood.set_of_books_id=gl.ledger_id and
ood.operating_unit=haouv.organization_id
 ) 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,
 hr_locations                 hl,
 -- ===========================================================================
 -- Select current Currency Rates based on the concurrency conversion date
 -- ===========================================================================
(select gdr.* from gl_daily_rates gdr, gl_daily_conversion_types gdct where gdr.conversion_date=:conversion_date and gdct.user_conversion_type=:user_conversion_type and gdct.conversion_type=gdr.conversion_type) gdr1 -- Current Currency Rates
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           = pl.item_id
and cic.organization_id             = pll.ship_to_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 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
-- ===================================================================
-- Joins for the currency exchange rates
-- ===================================================================
-- Current FX rate
and pll.currency_code                = gdr1.from_currency(+)
and pll.gl_currency_code                = gdr1.to_currency(+)
order by
 pll.ledger, -- Ledger
 pll.operating_unit, -- Operating_Unit
 pll.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