Former Member

# Last Purchase price

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

10|10000 characters needed characters exceeded

### Related questions

• Former Member
Posted on Nov 02, 2011 at 07:39 PM

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

10|10000 characters needed characters exceeded
• Former Member
Posted on Nov 03, 2011 at 04:25 AM

Hi........

```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

10|10000 characters needed characters exceeded
• Former Member
Posted on Oct 09, 2013 at 07:41 AM

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)'

FROM OPCH

INNER JOIN PCH1 ON OPCH.DocEntry = PCH1.DocEntry

WHERE

OPCH.DocType = 'I'

ORDER BY

PCH1.ItemCode, OPCH.DocDate DESC

SELECT *

WHERE

10|10000 characters needed characters exceeded
• Posted on Nov 17, 2013 at 10:32 PM

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

10|10000 characters needed characters exceeded
• Posted on Oct 12, 2015 at 07:16 AM

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

10|10000 characters needed characters exceeded
• Posted on Nov 08, 2019 at 07:47 AM
```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")```