CAC Purchase Price Variance
Description
Categories: Enginatics
Repository: Github
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 ...
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 |