Good afternoon all,
Does anyone know or have any instances of creating a PPV BAQ? Ideally pulling from the Item part cost to the invoiced cost?
I’m weary that it will be derived from the PO screen and thus am trying to ascertain if the variance should be captured from PO/booking in to invoice or from the standardized cost on an item card.
Any help is greatly appreciated.
Regards
ryan
PPV (Pay Per view?)… but I’m pretty sure that’s not what you are asking. What’s PPV?
Purchase Price Variance
Now that I know what PPV is …
You want to get a list of parts whose supplier invoice’s unit cost differs from that on file?
If you use AVG costing, it will never match - except for the rare occasions wher the prior QOH was zero, or this part has never changed cost.
Doesn’t AP entry already balk at invoices that don’t match the PO?
Hello Ryan,
This is the one we use. We are on Standard Cost and the query compares the purchased price to the Standard. It has a bunch of info you may not want but it works for us. Good luck
[PODetail].[PONUM] as [PODetail_PONUM],
[PODetail].[POLine] as [PODetail_POLine],
[Vendor].[Name] as [Vendor_Name],
[PODetail].[PartNum] as [PODetail_PartNum],
[PODetail].[LineDesc] as [PODetail_LineDesc],
[PODetail].[DocUnitCost] as [PODetail_DocUnitCost],
[PODetail].[OrderQty] as [PODetail_OrderQty],
[PORel].[ShippedQty] as [PORel_ShippedQty],
(PODetail.OrderQty - PORel.ShippedQty) as [Calculated_Units],
((PODetail.DocUnitCost * (PODetail.OrderQty - PORel.ShippedQty))) as [Calculated_ExtPrice],
[PORel].[DueDate] as [PORel_DueDate],
[PORel].[PromiseDt] as [PORel_PromiseDt],
[PartCost].[StdBurdenCost] as [PartCost_StdBurdenCost],
[PartCost].[StdLaborCost] as [PartCost_StdLaborCost],
[PartCost].[StdMaterialCost] as [PartCost_StdMaterialCost],
[PartCost].[StdMtlBurCost] as [PartCost_StdMtlBurCost],
[PartCost].[StdSubContCost] as [PartCost_StdSubContCost],
(PartCost.StdBurdenCost + PartCost.StdlaborCost + PartCost.StdMaterialCost + PartCost.StdMtlBurCost + PartCost.StdSubContCost) as [Calculated_PartStdCost],
(Units * PartStdCost) as [Calculated_LineStdCost],
[POHeader].[BuyerID] as [POHeader_BuyerID],
[PurAgent].[Name] as [PurAgent_Name],
[Part].[ClassID] as [Part_ClassID],
[POHeader].[OrderDate] as [POHeader_OrderDate],
[Part].[PurchasingFactor] as [Part_PurchasingFactor],
((PODetail.OrderQty - PORel.ShippedQty) * Part.PurchasingFactor) as [Calculated_OurQty],
(((PODetail.DocUnitCost * (PODetail.OrderQty - PORel.ShippedQty))) / NULLIF(((PODetail.OrderQty - PORel.ShippedQty) * Part.PurchasingFactor), 0)) as [Calculated_UnitPOCost],
[PORel].[ReceivedQty] as [PORel_ReceivedQty],
[RcvDtl].[ReceiptDate] as [RcvDtl_ReceiptDate],
[PORel].[OrderRelNum] as [PORel_OrderRelNum],
[PORel].[PORelNum] as [PORel_PORelNum],
[PORel].[OrderLine] as [PORel_OrderLine],
[Part].[TypeCode] as [Part_TypeCode],
(substring(PODetail.linedesc,1,20)) as [Calculated_ShortDesc],
[PODetail].[UnitCost] as [PODetail_UnitCost]
from Erp.POHeader as POHeader
inner join Erp.PODetail as PODetail on
POHeader.Company = PODetail.Company
and POHeader.PONum = PODetail.PONUM
inner join Erp.PORel as PORel on
PODetail.Company = PORel.Company
and PODetail.PONUM = PORel.PONum
and PODetail.POLine = PORel.POLine
inner join Erp.RcvDtl as RcvDtl on
PORel.Company = RcvDtl.company
and PORel.PONum = RcvDtl.ponum
and PORel.POLine = RcvDtl.poline
and PORel.PORelNum = RcvDtl.PORelNum
inner join Erp.Part as Part on
PODetail.Company = Part.Company
and PODetail.PartNum = Part.PartNum
and ( Part.ClassID <> 'DCTL' and Part.QtyBearing = 1 )
inner join Erp.PartCost as PartCost on
Part.Company = PartCost.Company
and Part.PartNum = PartCost.PartNum
inner join Erp.PartPlant as PartPlant on
Part.Company = PartPlant.Company
and Part.PartNum = PartPlant.PartNum
and ( PartPlant.SourceType = 'P' )
inner join Erp.Vendor as Vendor on
POHeader.Company = Vendor.Company
and POHeader.VendorNum = Vendor.VendorNum
inner join Erp.PurAgent as PurAgent on
POHeader.Company = PurAgent.Company
and POHeader.BuyerID = PurAgent.BuyerID
Rick Stannard
Rick - place four of the backwards apostrophes (on the key with the tilde) on a line by itself before your “code”, and four more (also by themself) on a line after
from Erp.POHeader as POHeader
inner join Erp.PODetail as PODetail on
POHeader.Company = PODetail.Company
and POHeader.PONum = PODetail.PONUM
inner join Erp.PORel as PORel on
PODetail.Company = PORel.Company
and PODetail.PONUM = PORel.PONum
and PODetail.POLine = PORel.POLine
becomes
from Erp.POHeader as POHeader
inner join Erp.PODetail as PODetail on
POHeader.Company = PODetail.Company
and POHeader.PONum = PODetail.PONUM
inner join Erp.PORel as PORel on
PODetail.Company = PORel.Company
and PODetail.PONUM = PORel.PONum
and PODetail.POLine = PORel.POLine
Attached is a query i use for month end PPV.PPV.baq (30.6 KB)
When i run it, it asks for the date range. I usually run it each month to analyze PPV. I copy it to excel and remove duplicate entries by using “Data > Remove Duplicate” function in excel 2010. I am only removing duplicates based on “TranNum” which should be unique.
I updated the Attached BAQ. I still copy it to excel and then remove the duplicate based on “tran” number (1st column) to give me unqiue transactions. I then filter for anything >= to +/-$250 total PPV for month end analysis.PPV.baq (37.5 KB)
Hi guys,
thank you for this its much appreciated.
Next question is almost PPV related. How can i tell if an amendment has been made to a PO and what the amendment was (price change etc) - more importantly how can i make this into a BAQ?
regards
ryan