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

Download Blitz Report™ – World’s fastest reporting and data upload for Oracle EBS

Contact us to schedule a demo or if you need help with the installation

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 
 
 LOV
Currency Conversion Date 
 
 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.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_name2 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_name2
 LOV
Item Number 
msiv.concatenated_segments = :p_item_number
 LOV
Organization Code 
mp.organization_code = :p_org_code
 LOV
Operating Unit 
haouv.name = :p_operating_unit
 LOV
Ledger 
gl.name = :p_ledger
 LOV
Download
   
Related Blitz Reports
CAC WIP Material Usage with Configuration and Lot Variances CAC Manufacturing Variance Detail CAC WIP Pending Cost Adjustment CAC ICP PII WIP Pending Cost Adjustment CAC Manufacturing Variance Summary INV Item Upload CAC WIP Material Usage Variance CAC ICP PII WIP Material Usage Variance CAC Calculate ICP PII Item Costs by Where Used WIP Discrete Job Pick List
Blitz Report™

Blitz Report™ provides multiple benefits: