CAC Purchase Price Variance

➜ Back to Library
Description
Categories: Enginatics
Repository: Github
Report for Purchase Price Variance accounting entries for external inventory purchases, external outside processing purchases, (internal) intransit shipments, (internal) direct organization transfers and transfer to regular (consignment) transactions. The FOB point indicates when title passes to the receiving organization and it also determines which internal transfer transaction gets the PPV. W ...  Report for Purchase Price Variance accounting entries for external inventory purchases, external outside processing purchases, (internal) intransit shipments, (internal) direct organization transfers and transfer to regular (consignment) transactions. The FOB point indicates when title passes to the receiving organization and it also determines which internal transfer transaction gets the PPV. With FOB Shipment, PPV happens on the Intransit Shipment transaction. With FOB Receipt, PPV happens on the Intransit Receipt transaction. And if you enter PO receipts by lot numbers this report splits out the PPV variances by lot number.

/* +=============================================================================+
-- | Copyright 2010-23 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])
-- |
-- | Parameters:
-- | p_trx_date_from -- starting transaction date for ppv related transactions
-- | p_trx_date_to -- ending transaction date for ppv related transactions
-- | 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
-- | p_item_number -- Enter the specific item number you wish to report (optional)
-- | p_org_code -- Specific inventory organization you wish to report (optional)
-- | p_operating_unit -- Operating_Unit you wish to report, leave blank for all
-- | operating units (optional)
-- | p_ledger -- general ledger you wish to report, leave blank for all
-- | ledgers (optional)
-- |
-- | Version Modified on Modified by Description
-- | ======= =========== ============== =========================================
-- | 1.0 26 Jan 2010 Douglas Volz Initial Coding
-- | 1.16 30 Dec 2020 Douglas Volz Revise calculations for Percent Difference, to:
-- | when difference = 0 then 0
-- | when standard = 0 then 100%
-- | when PO unit price = 0 then -100%
-- | else PO - std / std
-- | Performance improvements for Sections 3 and 4.
-- | 1.17 01 Jan 2021 Douglas Volz Added Section 5 PPV for Transfer to Regular transactions.
-- | 1.18 08 Jan 2021 Douglas Volz Removed redundant joins and tables to improve performance.
-- | 1.19 14 Dec 2021 Douglas Volz Bug fix, Section I and V were both picking up
-- | Transfer to Regular PPV transactions
-- | 1.20 21 Jun 2022 Douglas Volz Add PO Line and PO Shipment Line Creation Date.
-- | 1.21 04 Apr 2022 Andy Haack Added organization security restriction by org_access_view oav.
-- | 1.22 10 May 2023 Douglas Volz Fix PPV calculations for RTV and Receipt Adjustment transactions..
+=============================================================================+*/   more
Run CAC Purchase Price Variance and other Oracle EBS reports with Blitz Report™ on our demo environment
select nvl(gl.short_name, gl.name) Ledger,
-- =============================================================
-- Section I
-- Get the Deliveries from Receiving Inspection to Stores
-- inventory for purchase order receipt transactions
-- Revision for version 1.11 and 1.15, correct PPV calculation 
-- for Material Overhead, need the standard item cost to be net
-- of this level material overhead.
-- =============================================================
 haou2.name Operating_Unit,
 mp.organization_code Ship_To_Org,
 '' Ship_From_Org,
 oap.period_name Period_Name,
 &segment_columns
 pov.vendor_name Supplier,
 he.full_name Buyer,
 msiv.concatenated_segments Item_Number,
 msiv.description Item_Description,
 -- Revision for version 1.14
 fcl.meaning Item_Type,
 misv.inventory_item_status_code_tl Item_Status,
 ml2.meaning Make_Buy_Code,
 -- End Revision for version 1.14
 -- Revision for version 1.12
&category_columns
 -- End revision for version 1.12
 -- Fix for version 1.9
 '' WIP_Job,
 '' OSP_Resource,
 -- End fix for version 1.9
 ph.segment1 PR_or_PO_Number,
 to_char(pl.line_num)  Line_Number,
 -- Revision for version 1.20
 pl.creation_date Line_Creation_Date,
 to_char(pr.release_num) PO_Release,
 rsh.receipt_num Receipt_Number,
 rsh.shipment_num Shipment_Number,
 -- Revision for version 1.20
 pll.creation_date Shipment_Creation_Date,
 ml.meaning Accounting_Line_Type,
 mtt.transaction_type_name Transaction_Type,
 mmt.transaction_id Transaction_Id,
 trunc(mmt.transaction_date) Transaction_Date,
 mtln.lot_number Lot_Number,
 -- Revision for version 1.14
 muomv.uom_code UOM_Code,
 decode(mtln.lot_number, null, mmt.primary_quantity, mtln.primary_quantity) Received_Quantity,
 nvl(ph.currency_code, gl.currency_code) PO_Currency_Code,
 round(nvl(mmt.transaction_cost,0) / decode(nvl(mta.currency_conversion_rate,1),0,1,nvl(mta.currency_conversion_rate,1)),5) PO_Unit_Price,
 nvl(mta.currency_conversion_rate,1) PO_Exchange_Rate,
 gl.currency_code GL_Currency_Code,
 round(nvl(mmt.transaction_cost,0),5) Converted_PO_Unit_Price,
 -- Revision for version 1.22
 -- mmt.transaction_cost is the purchase order cost
 -- mta.rate_of_amount is the PPV amount per unit
 -- mmt.variance_amount is the total PPV amont
 round(case
    when mmt.primary_quantity < 0 and mta.rate_or_amount > 0 and mmt.variance_amount > 0 then mmt.transaction_cost + mta.rate_or_amount
    when mmt.primary_quantity < 0 and mta.rate_or_amount > 0 and mmt.variance_amount < 0 then mmt.transaction_cost + (mta.rate_or_amount * -1)
    when mmt.primary_quantity < 0 and mta.rate_or_amount < 0 and mmt.variance_amount > 0 then mmt.transaction_cost + (mta.rate_or_amount * -1)
    when mmt.primary_quantity < 0 and mta.rate_or_amount < 0 and mmt.variance_amount < 0 then mmt.transaction_cost + mta.rate_or_amount
    when mmt.primary_quantity > 0 and mta.rate_or_amount > 0 and mmt.variance_amount > 0 then mmt.transaction_cost + (mta.rate_or_amount * -1)
    when mmt.primary_quantity > 0 and mta.rate_or_amount > 0 and mmt.variance_amount < 0 then mmt.transaction_cost + mta.rate_or_amount
    when mmt.primary_quantity > 0 and mta.rate_or_amount < 0 and mmt.variance_amount > 0 then mmt.transaction_cost + mta.rate_or_amount
    when mmt.primary_quantity > 0 and mta.rate_or_amount < 0 and mmt.variance_amount < 0 then mmt.transaction_cost + (mta.rate_or_amount * -1)
    else 0
 end,5) Standard_Unit_Cost,
 -- Unit cost difference = PO price - Std Unit Cost
 round(mmt.transaction_cost,5) -
 round(case
    when mmt.primary_quantity < 0 and mta.rate_or_amount > 0 and mmt.variance_amount > 0 then mmt.transaction_cost + mta.rate_or_amount
    when mmt.primary_quantity < 0 and mta.rate_or_amount > 0 and mmt.variance_amount < 0 then mmt.transaction_cost + (mta.rate_or_amount * -1)
    when mmt.primary_quantity < 0 and mta.rate_or_amount < 0 and mmt.variance_amount > 0 then mmt.transaction_cost + (mta.rate_or_amount * -1)
    when mmt.primary_quantity < 0 and mta.rate_or_amount < 0 and mmt.variance_amount < 0 then mmt.transaction_cost + mta.rate_or_amount
    when mmt.primary_quantity > 0 and mta.rate_or_amount > 0 and mmt.variance_amount > 0 then mmt.transaction_cost + (mta.rate_or_amount * -1)
    when mmt.primary_quantity > 0 and mta.rate_or_amount > 0 and mmt.variance_amount < 0 then mmt.transaction_cost + mta.rate_or_amount
    when mmt.primary_quantity > 0 and mta.rate_or_amount < 0 and mmt.variance_amount > 0 then mmt.transaction_cost + mta.rate_or_amount
    when mmt.primary_quantity > 0 and mta.rate_or_amount < 0 and mmt.variance_amount < 0 then mmt.transaction_cost + (mta.rate_or_amount * -1)
    else 0
 end,5) Unit_Cost_Difference,
 -- End revision for version 1.22
 round(nvl(mmt.transaction_cost,0) * decode(mtln.lot_number, null, mmt.primary_quantity, mtln.primary_quantity),2) Total_Purchase_Amount,
 -- Revision for version 1.22, Std Unit Cost X Qty
 round(round(case
    when mmt.primary_quantity < 0 and mta.rate_or_amount > 0 and mmt.variance_amount > 0 then mmt.transaction_cost + mta.rate_or_amount
    when mmt.primary_quantity < 0 and mta.rate_or_amount > 0 and mmt.variance_amount < 0 then mmt.transaction_cost + (mta.rate_or_amount * -1)
    when mmt.primary_quantity < 0 and mta.rate_or_amount < 0 and mmt.variance_amount > 0 then mmt.transaction_cost + (mta.rate_or_amount * -1)
    when mmt.primary_quantity < 0 and mta.rate_or_amount < 0 and mmt.variance_amount < 0 then mmt.transaction_cost + mta.rate_or_amount
    when mmt.primary_quantity > 0 and mta.rate_or_amount > 0 and mmt.variance_amount > 0 then mmt.transaction_cost + (mta.rate_or_amount * -1)
    when mmt.primary_quantity > 0 and mta.rate_or_amount > 0 and mmt.variance_amount < 0 then mmt.transaction_cost + mta.rate_or_amount
    when mmt.primary_quantity > 0 and mta.rate_or_amount < 0 and mmt.variance_amount > 0 then mmt.transaction_cost + mta.rate_or_amount
    when mmt.primary_quantity > 0 and mta.rate_or_amount < 0 and mmt.variance_amount < 0 then mmt.transaction_cost + (mta.rate_or_amount * -1)
    else 0
 end,5) * decode(mtln.lot_number, null, mmt.primary_quantity, mtln.primary_quantity),2) Total_Standard_Amount,
 -- PPV_Amount = PO Amount - Std Amount
 round(nvl(mmt.transaction_cost,0) * decode(mtln.lot_number, null, mmt.primary_quantity, mtln.primary_quantity),2)  -
 round(round(case
    when mmt.primary_quantity < 0 and mta.rate_or_amount > 0 and mmt.variance_amount > 0 then mmt.transaction_cost + mta.rate_or_amount
    when mmt.primary_quantity < 0 and mta.rate_or_amount > 0 and mmt.variance_amount < 0 then mmt.transaction_cost + (mta.rate_or_amount * -1)
    when mmt.primary_quantity < 0 and mta.rate_or_amount < 0 and mmt.variance_amount > 0 then mmt.transaction_cost + (mta.rate_or_amount * -1)
    when mmt.primary_quantity < 0 and mta.rate_or_amount < 0 and mmt.variance_amount < 0 then mmt.transaction_cost + mta.rate_or_amount
    when mmt.primary_quantity > 0 and mta.rate_or_amount > 0 and mmt.variance_amount > 0 then mmt.transaction_cost + (mta.rate_or_amount * -1)
    when mmt.primary_quantity > 0 and mta.rate_or_amount > 0 and mmt.variance_amount < 0 then mmt.transaction_cost + mta.rate_or_amount
    when mmt.primary_quantity > 0 and mta.rate_or_amount < 0 and mmt.variance_amount > 0 then mmt.transaction_cost + mta.rate_or_amount
    when mmt.primary_quantity > 0 and mta.rate_or_amount < 0 and mmt.variance_amount < 0 then mmt.transaction_cost + (mta.rate_or_amount * -1)
    else 0
 end,5) * decode(mtln.lot_number, null, mmt.primary_quantity, mtln.primary_quantity),2) PPV_Amount,
 -- Revision for version 1.22, calculate the percentage
 -- case
 --   when difference = 0 then 0
 --   when standard = 0 then 100%
 --   when PO unit price = 0 then -100%
 --   else PO - std / std
 case
    when round(nvl(mta.rate_or_amount,0),5) = 0 then 0
    when round(nvl(mmt.transaction_cost,0) + nvl(mta.rate_or_amount,0),5) = 0 then 100
    when round(nvl(mmt.transaction_cost,0),5) = 0 then -100
    else
  round(mmt.transaction_cost,5) -
  round(case
     when mmt.primary_quantity < 0 and mta.rate_or_amount > 0 and mmt.variance_amount > 0 then mmt.transaction_cost + mta.rate_or_amount
     when mmt.primary_quantity < 0 and mta.rate_or_amount > 0 and mmt.variance_amount < 0 then mmt.transaction_cost + (mta.rate_or_amount * -1)
     when mmt.primary_quantity < 0 and mta.rate_or_amount < 0 and mmt.variance_amount > 0 then mmt.transaction_cost + (mta.rate_or_amount * -1)
     when mmt.primary_quantity < 0 and mta.rate_or_amount < 0 and mmt.variance_amount < 0 then mmt.transaction_cost + mta.rate_or_amount
     when mmt.primary_quantity > 0 and mta.rate_or_amount > 0 and mmt.variance_amount > 0 then mmt.transaction_cost + (mta.rate_or_amount * -1)
     when mmt.primary_quantity > 0 and mta.rate_or_amount > 0 and mmt.variance_amount < 0 then mmt.transaction_cost + mta.rate_or_amount
     when mmt.primary_quantity > 0 and mta.rate_or_amount < 0 and mmt.variance_amount > 0 then mmt.transaction_cost + mta.rate_or_amount
     when mmt.primary_quantity > 0 and mta.rate_or_amount < 0 and mmt.variance_amount < 0 then mmt.transaction_cost + (mta.rate_or_amount * -1)
     else 0
  end,5) /
  decode(
  round(case
      when mmt.primary_quantity < 0 and mta.rate_or_amount > 0 and mmt.variance_amount > 0 then mmt.transaction_cost + mta.rate_or_amount
      when mmt.primary_quantity < 0 and mta.rate_or_amount > 0 and mmt.variance_amount < 0 then mmt.transaction_cost + (mta.rate_or_amount * -1)
      when mmt.primary_quantity < 0 and mta.rate_or_amount < 0 and mmt.variance_amount > 0 then mmt.transaction_cost + (mta.rate_or_amount * -1)
      when mmt.primary_quantity < 0 and mta.rate_or_amount < 0 and mmt.variance_amount < 0 then mmt.transaction_cost + mta.rate_or_amount
      when mmt.primary_quantity > 0 and mta.rate_or_amount > 0 and mmt.variance_amount > 0 then mmt.transaction_cost + (mta.rate_or_amount * -1)
      when mmt.primary_quantity > 0 and mta.rate_or_amount > 0 and mmt.variance_amount < 0 then mmt.transaction_cost + mta.rate_or_amount
      when mmt.primary_quantity > 0 and mta.rate_or_amount < 0 and mmt.variance_amount > 0 then mmt.transaction_cost + mta.rate_or_amount
      when mmt.primary_quantity > 0 and mta.rate_or_amount < 0 and mmt.variance_amount < 0 then mmt.transaction_cost + (mta.rate_or_amount * -1)
      else 0
   end,5),0,1,
  round(case
      when mmt.primary_quantity < 0 and mta.rate_or_amount > 0 and mmt.variance_amount > 0 then mmt.transaction_cost + mta.rate_or_amount
      when mmt.primary_quantity < 0 and mta.rate_or_amount > 0 and mmt.variance_amount < 0 then mmt.transaction_cost + (mta.rate_or_amount * -1)
      when mmt.primary_quantity < 0 and mta.rate_or_amount < 0 and mmt.variance_amount > 0 then mmt.transaction_cost + (mta.rate_or_amount * -1)
      when mmt.primary_quantity < 0 and mta.rate_or_amount < 0 and mmt.variance_amount < 0 then mmt.transaction_cost + mta.rate_or_amount
      when mmt.primary_quantity > 0 and mta.rate_or_amount > 0 and mmt.variance_amount > 0 then mmt.transaction_cost + (mta.rate_or_amount * -1)
      when mmt.primary_quantity > 0 and mta.rate_or_amount > 0 and mmt.variance_amount < 0 then mmt.transaction_cost + mta.rate_or_amount
      when mmt.primary_quantity > 0 and mta.rate_or_amount < 0 and mmt.variance_amount > 0 then mmt.transaction_cost + mta.rate_or_amount
      when mmt.primary_quantity > 0 and mta.rate_or_amount < 0 and mmt.variance_amount < 0 then mmt.transaction_cost + (mta.rate_or_amount * -1)
      else 0
  end,5)) * 100
 end Percent_Difference
 -- End revision for version 1.22
from mtl_transaction_accounts mta,
 mtl_material_transactions mmt,
 mtl_transaction_lot_numbers mtln,
 mtl_transaction_types mtt,
 rcv_transactions rt,
 rcv_shipment_headers rsh,
 po_vendors pov,
 po_headers_all ph,
 po_lines_all pl,
 po_releases_all pr,
 po_line_locations_all pll,
 po_distributions_all pod,
 mtl_system_items_vl msiv,
 -- Revision for version 1.8
 mtl_units_of_measure_vl muomv,
 mtl_item_status_vl misv,
 -- End revision for version 1.8
 org_acct_periods oap,
 gl_code_combinations_kfv gcc,
 mtl_parameters mp, 
 hr_employees he,
 fnd_common_lookups fcl, -- item type
 mfg_lookups ml, -- accounting line type
 mfg_lookups ml2, -- planning make/buy code
 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.18, remove tables to increase performance
 -- xla.xla_transaction_entities ent,  -- apps synonym not working
 -- xla_events xe,
 -- End revision for version 1.18
 xla_distribution_links xdl,
 xla_ae_headers ah,
 xla_ae_lines al
-- ========================================================
-- Material transaction, receiving and item joins
-- ========================================================
where mta.transaction_id              = mmt.transaction_id
and mmt.transaction_id              = mtln.transaction_id (+)
and mmt.transaction_type_id    = mtt.transaction_type_id
and rt.transaction_id               = mmt.rcv_transaction_id
and mta.organization_id             = msiv.organization_id
and mta.inventory_item_id           = msiv.inventory_item_id
and mp.organization_id              = msiv.organization_id
-- Revision for version 1.14
and msiv.primary_uom_code           = muomv.uom_code
and misv.inventory_item_status_code = msiv.inventory_item_status_code
-- End revision for version 1.14
and mta.accounting_line_type        = 6
and mmt.transaction_source_type_id  = 1
-- Revision for version 1.18, performance improvements
and mta.transaction_source_type_id  = 1
-- Revision for version 1.19
and mmt.transaction_type_id        <> 74
-- ========================================================
-- Purchase Order Joins
-- ========================================================
-- Revision for version 1.18, performance improvements
-- and ph.po_header_id                 = pl.po_header_id
and pod.destination_type_code       = 'INVENTORY'
and pod.po_distribution_id          = rt.po_distribution_id
and rsh.shipment_header_id          = rt.shipment_header_id
and pod.line_location_id            = pll.line_location_id
and pl.po_line_id                   = pll.po_line_id
and pll.po_release_id               = pr.po_release_id (+)
-- Revision for version 1.18, performance improvements
-- and pl.item_id                      = msiv.inventory_item_id
-- and rt.po_header_id                 = ph.po_header_id
-- End revision for version 1.14
and mmt.transaction_source_id