Last Purchase price

[Former Member]

Hi,

I need to populate the last purchase price for items which happen only through A/P Invoice.

Table OITM has last purchase price but its not equal to the actual price from A/P Invoice which can be checked from Inventory-Inventory Reports-Last Price Reports.

Item Master (OITM) price and this above report price are both different.

How to get the last purchase price based on A/P in a query?

Thanks

[Former Member]

Try this one...

SELECT	ROW_NUMBER()
		OVER (PARTITION BY PCH1.ItemCode ORDER BY OPCH.DocDate DESC) AS RawNo,
		OPCH.DocNum, OPCH.DocDate, PCH1.ItemCode, PCH1.Price, OPCH.DocCur, OPCH.DocRate, PCH1.Price * OPCH.DocRate AS 'Price(LC)'

INTO #PurchasingItems

FROM OPCH

INNER JOIN PCH1 ON OPCH.DocEntry = PCH1.DocEntry

WHERE

OPCH.DocType = 'I'

ORDER BY

PCH1.ItemCode, OPCH.DocDate DESC

SELECT *

FROM #PurchasingItems

WHERE

#PurchasingItems.RawNo = 1

[tatehil]

SELECT
  T1."ItemCode",
  T1."Price"
FROM OPCH T0
INNER JOIN PCH1 T1
  ON T0."DocEntry" = T1."DocEntry"
INNER JOIN (SELECT
  T1."ItemCode",
  MAX(T0."DocDate") "LastDocDate",
  MAX(T0."DocNum") "LastDocNum"
FROM OPCH T0
INNER JOIN PCH1 T1
  ON T0."DocEntry" = T1."DocEntry"
GROUP BY T1."ItemCode") T2
  ON (T1."ItemCode" = T2."ItemCode"
  AND T0."DocDate" = T2."LastDocDate"
  AND T0."DocNum" = T2."LastDocNum")

[siewpingtan]

SELECT T0.ItemCode, MAX(T0.DocDate) 'Latest',

(SELECT top 1 Price FROM PCH1 WHERE ItemCode=T0.ItemCode order by DocDate desc) 'Last Purchase Price'

FROM PCH1 T0

Group By T0.ItemCode

[BattleshipCobra]

Hello,

You don't have to do this via query.  Simply create a PO, add a new row with an item, click in the price for that item and push CTRL + TAB.  Then you can uncheck all the boxes except for AP Invoice.

I wrote a short article with an example here: http://www.battleshipcobra.com/2013/last-price-report-in-sales-documents/

Thanks,

Mike

[former_member768149]

Hello Mike,

Thank you for this. Do you have any idea on how can I include on my query report the last evaluated price and last purchase price?

Thank you.

L.Abuel

[BattleshipCobra]

Hi L.Abuel!

Those two reports are system generated and already included in the OITM table:

OITM.[LstEvlPric]

OITM.[LastPurPrc]

So just add those columns and you have the two price lists very easily,

Mike

[Former Member]

Hi........

Please try this.......

SELECT Top 1 T0.[DocNum] 'PO No.', T0.[DocDate] 'PO Date', T0.CardName 'Supplier', T1.[Dscription] 'Product Name', T1.[Quantity], T1.[Price], T2.[PymntGroup] 'Payment Terms' FROM OPCH T0  INNER JOIN PCH1 T1 ON T0.DocEntry = T1.DocEntry Inner Join OCTG T2 On T0.GroupNum=T2.GroupNum
WHERE T1.[Dscription] ='[%0]'
Order By T0.[DocDate] DESC

Regards,

Rahul

[Former Member]

Hi,

Try:

SELECT T0.ItemCode,MAX(T0.DocDate) 'Latest', (SELECT Price FROM PCH1 WHERE ItemCode=T0.ItemCode AND DocDate= MAX(T0.DocDate)) 'Last Price'

FROM PCH1 T0

Group By T0.ItemCode

Thanks,

Gordon

[Former Member]

Hi Gordon,

I am getting below error when i run your query

02/11/2011 16:16:24: 1). [Microsoft][SQL Native Client][SQL Server]Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

'Activities' (OCLG)

Further i have a query which shows list of price from all the A/P invoices for an item from pch1 table. How do i select the latest A/P date and show only the latest single price for any item at any given report run date and not show all history.

Thanks,

[Former Member]

That is because you have the same day changed the price twice or more which it is not happened in most of the businesses.

You may add an item selection to try.

WHERE T0.ItemCode = '[%0\]'

[Former Member]

Hi Gordon,

This is what i've as a query:

Select t1.itemcode,t1.itemname,t2.itmsgrpnam, (SELECT t3.Price FROM PCH1 WHERE ItemCode=T3.ItemCode AND DocDate= MAX(T3.DocDate))'Last Price', MAX(t3.docdate)'Latest Date'

From oitm t1

INNER JOIN oitb t2 on t1.itmsgrpcod = t2.itmsgrpcod and t2.itmsgrpnam IN ('Disposables','Others-Shop supplies','Raw Material')

INNER JOIN pch1 t3 on t1.itemcode = t3.itemcode

WHERE T3.ItemCode = '[%0]'

Group By T3.ItemCode

I'm getting errors running it after i select an item.

Thanks,

[Former Member]

Hi.......

Try this......

Select t1.itemcode,t1.itemname,t2.itmsgrpnam, (SELECT t3.Price FROM PCH1 WHERE ItemCode=T3.ItemCode AND DocDate= MAX(T3.DocDate))'Last Price', MAX(t3.docdate)'Latest Date'
From oitm t1
INNER JOIN oitb t2 on t1.itmsgrpcod = t2.itmsgrpcod and t2.itmsgrpnam IN ('Disposables','Others-Shop supplies','Raw Material')
INNER JOIN pch1 t3 on t1.itemcode = t3.itemcode
WHERE T3.ItemCode = '[%0]'
Group By T3.ItemCode, t1.itemname,t2.itmsgrpnam, T3.ItemCode

Regards,

Rahul

[Former Member]

Hi Rahul ,

I'm getting this error:

03/11/2011 11:39:50: 1). [Microsoft][SQL Native Client][SQL Server]Column 'oitm.ItemCode' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

2). [Microsoft][SQL Native Client][SQL Server]Sta

Thanks,

[Former Member]

Ok......

Try this......

Select t1.itemcode,t1.itemname,t2.itmsgrpnam, (SELECT t3.Price FROM PCH1 WHERE ItemCode=T3.ItemCode AND DocDate= MAX(T3.DocDate))'Last Price', MAX(t3.docdate)'Latest Date'
From oitm t1
INNER JOIN oitb t2 on t1.itmsgrpcod = t2.itmsgrpcod and t2.itmsgrpnam IN ('Disposables','Others-Shop supplies','Raw Material')
INNER JOIN pch1 t3 on t1.itemcode = t3.itemcode
WHERE T3.ItemCode = '[%0]'
Group By T3.ItemCode, t1.itemname,t2.itmsgrpnam, T1.ItemCode

Regards,

Rahul

[Former Member]

Hi Rahul,

Now getting this error message :

03/11/2011 11:45:03: 1). [Microsoft][SQL Native Client][SQL Server]Column 'pch1.Price' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

2). [Microsoft][SQL Native Client][SQL Server]Statem

Thanks

[Former Member]

Hmmmm......

Ok try this.....

Select t1.itemcode,t1.itemname,t2.itmsgrpnam, (SELECT t3.Price FROM PCH1 WHERE ItemCode=T3.ItemCode AND DocDate= MAX(T3.DocDate))'Last Price', MAX(t3.docdate)'Latest Date'
From oitm t1
INNER JOIN oitb t2 on t1.itmsgrpcod = t2.itmsgrpcod and t2.itmsgrpnam IN ('Disposables','Others-Shop supplies','Raw Material')
INNER JOIN pch1 t3 on t1.itemcode = t3.itemcode
WHERE T3.ItemCode = '[%0]'
Group By T3.ItemCode, t1.itemname,t2.itmsgrpnam, T1.ItemCode, T3.Price

Regards,

Rahul

[Former Member]

Now this :

1). [Microsoft][SQL Native Client][SQL Server]Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. 'User-Defined Values' (CSHS)

I do not need to input any single item so i removed WHERE T3.ItemCode = '[%0]'. Even keeping this and giving an input shows the same error.

Thanks

[Former Member]

Hi,

Any idea how i can do this ?

[Former Member]

Check this:

SELECT T0.ItemCode,MAX(T0.DocDate) 'Latest', (SELECT MAX(Price) FROM PCH1 WHERE ItemCode=T0.ItemCode AND DocDate= MAX(T0.DocDate)) 'Last Price'

FROM PCH1 T0

Group By T0.ItemCode

[Former Member]

Hi Gordon,

Thank you for the help. I got it !!

Thanks,

[Former Member]

Hi Gordon,

Can you add currency in?

thanks