Purchase price variance report

[ghanbary_shawn]

Hi,

Where can I run purchase price variance report from.

We are using standard cost method and we would like to get a purchase price variance report from GRPO.

Tkx

Shawn

[Former Member · Accepted solution]

Hi,

You need to create a query in this.

What is your requirements? Purchase price variance per item?

monthly? Please specify.

Thanks.

Clint

[ghanbary_shawn · Accepted solution]

Hi Clint,

I want to run a report to compare Goods receipts price (PO Price) VS standard cost at the time of goods receipts so I can see the variances at end of the month or anytime after goods receipts. There is a field in goods receipts table called StockPrice which does not get updated all the time. The suppose to be the cost on oitw table at the time of goods receipts.

Tkx

Shawn

[Former Member · Accepted solution]

Hi.

Try this query:

From GRPO and Master Item Data Module

SELECT  T0.DocDate,T0.DocEntry,T1.[ItemCode],
        T1.[Dscription],T1.[Price] as 'GRPO Price',
		T2.AvgPrice  
FROM OPDN T0 INNER JOIN PDN1 T1 ON T0.DocEntry = T1.DocEntry 
INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode 
WHERE T0.DocDate BETWEEN '[%0]' and '[%1]'

Thanks.

Clint

[ghanbary_shawn · Accepted solution]

Clint,

Thank you for the query but the problem which I see here would be that if the standard cost changes after po goods receipts then the report will not be accurate since you are comparing to current standard cost not the standard cost of when the goods receipts took place. Maybe if we can capture the item cost at goods receipts time will solve the issue. Then the issue is if formatted search will work or not since there are no trigger point unless you know a way to do it.Tkx

Shawn

[Former Member · Accepted solution]

hi,

from the above query

the T2.AvgPrice is from Master Item Data.

This is the standard price.

SELECT  T0.DocDate,T0.DocEntry,T1.[ItemCode],
        T1.[Dscription],T1.[Price] as 'GRPO Price',
		T2.AvgPrice   as 'Standard Price'
FROM OPDN T0 INNER JOIN PDN1 T1 ON T0.DocEntry = T1.DocEntry 
INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode 
WHERE T0.DocDate BETWEEN '[%0]' and '[%1]'

From the query you can compare Standard Price and GRPO Price.

Thanks.

Clint

[ghanbary_shawn · Accepted solution]

Clint,

That is correct but lets say if change my standard cost today and I want to run the report for last month GRPO the cost variance will be based on the new cost not the cost which we had at GRPO time. We need to capture the standard cost at the time of GRPO in order to have a correct report.

Shawn

[Former Member · Accepted solution]

Sir,

It would be better for u to maintain Price Lists whenever your Standard Price is changed. Based on Price List U can create Query report and i think that will help u.

Regards,

Ravi

[Former Member · Accepted solution]

Your requirement is way more complicated than you can imagine. It is because B1 only keep the current data. The historical data in log table is not easy to check against the time frame you are giving.

[ghanbary_shawn · Accepted solution]

Gordon,

Couldn't you create a UDF with formated search to store the standard cost at the time of Goods receipts?

[Former Member · Accepted solution]

Hi Ghanbary,

I don't think you need such hassle to achieve this. You could query to OIVL (I assume you're using version 8.81?), and to achieve what you want here, all information has been provided in this table.

Let say for an example, if you have item A001 which costing you set as standard and the value of standard cost given is $100, while you receive this item in GR-PO with unit price of $120 and the quantity of purchase is 5, in OIVL will be stored like this:

ItemCode = A001 (item code)

InQty = 5 (GR-PO quantity)

Price = 120 (purchase price in GR-PO)

VarVal = 100 (even the price variance stored here, variance here is total variance for all quantity of this item)

SumStock = 500 (quantity * standard cost during this GR-PO)

Therefore, you could retrieve the standard cost for each GR-PO even you change your standard cost regularly by dividing the SumStock with InQty (500 / 5 = 100). Is this enough to assist you?

You could make your own query, adding parameter here and there, joining with table OPDN and PDN1 to make it nicer and show you more detail information, but the most important piece of puzzle stored in OIVL. If you want a complete query, you could inform what column and data you want to retrieve.

Best Regards,

Hendry Wijaya

[Former Member]

Hi Shawn,

In standard report, there is only a Purchase Analysis report. If this report does not meet your need, you have to create your own report.

Thanks,

Gordon