cancel
Showing results for 
Search instead for 
Did you mean: 

Query: Last actual purchase cost of item number

Former Member
0 Kudos

I would like a query that can show me the last price we paid for a specific item on an invoice on a per unit basis. So it is basically like the "last price report" but for all item numbers.

A huge bonus would be if I could also get a column of data that shows the HIGHEST purchase price per unit for that item.

Thank you,

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi,

You can try:

SELECT  T0.[ItemCode], T0.[Dscription], T0.Price 'Last Actual Purchase Price ' FROM PCH1 T0

JOIN

(SELECT  T1.[ItemCode], MAX(T1.Docentry) 'DocEntry'

FROM PCH1 T1

Group By T1.[ItemCode]) T ON T.DocEntry=T0.DocEntry AND T.ItemCode=T0.ItemCode

ORDER BY T0.ItemCode

Thanks,

Gordon

Answers (5)

Answers (5)

former_member188586
Active Contributor
0 Kudos

hi

if you got the solution

please close the thread

Former Member
0 Kudos

Hi Nadeem,

Try this query

SELECT distinct T1.[ItemCode],  T1.[Dscription],T1.[unitMsr], T1.[Price]

FROM OPOR T0  INNER JOIN POR1 T1 ON T0.DocEntry = T1.DocEntry

WHERE T0.[CANCELED] = 'N' order by  itemcode,price desc

Former Member
0 Kudos

Hi Nadeem Seedat,

Try the following:

SELECT T0.[ItemCode] AS 'Item No.', T0.[ItemName] AS 'Item Description', T0.[LastPurDat] AS 'Last Purchase Date', T0.[LastPurPrc] AS 'Last Purchase Price'

FROM  [dbo].[OITM] T0

JO

Former Member
0 Kudos

This one is use the value from the price list "Last Purchase Price"

I want the price that would be on the last AP invoice for the item...which is not necessarily how SAP B1 calculates its "last purchase price" value.

I appreciate the help.

isaac_kalii
Active Participant
0 Kudos

Hi,

Please try the below:

SELECT DISTINCT TOP (100) PERCENT dbo.PCH1.ItemCode, dbo.PCH1.Dscription, dbo.PCH1.Price, dbo.OPCH.DocEntry

FROM         dbo.OPCH INNER JOIN

                      dbo.PCH1 ON dbo.OPCH.DocEntry = dbo.PCH1.DocEntry

WHERE     (dbo.OPCH.DocType = 'i')

ORDER BY dbo.OPCH.DocEntry DESC

Regards,

Isaac.

former_member188586
Active Contributor
0 Kudos

HI Nadeem

check bellow Query and update the status

SELECT TOP 1 T0.[DocNum],T0.[DocDate], T0.[CardName],  T1.[ItemCode],  T1.[Dscription], T1.[FreeTxt], T1.[unitMsr],T0.[Project], T1.[Quantity], T1.[Price] FROM OPOR T0  INNER JOIN POR1 T1 ON T0.DocEntry = T1.DocEntry WHERE T1.[ItemCode] =[%0]  AND T0.[CANCELED] != 'Y'  order by  T0.[DocDate] desc

---Ramudu

Former Member
0 Kudos

I want to be able to see all item numbers at once. I don't need a selection criteria.

I appreciate the help.

isaac_kalii
Active Participant
0 Kudos

Hi Nadeem,

Pleae try this for Last Unit Price; also note that this will be in a Purchase Request or Purchase Order in FMS:

SELECT T1.[Price] FROM OPCH T0  INNER JOIN PCH1 T1 ON T0.DocEntry = T1.DocEntry WHERE T1.[ItemCode] = $[$38.1.0] ORDER BY T0.[DocEntry] Desc

And this for Last Supplier:

SELECT T0.[CardName] FROM OPCH T0  INNER JOIN PCH1 T1 ON T0.DocEntry = T1.DocEntry WHERE T1.[ItemCode] = $[$38.1.0] ORDER BY T0.[DocEntry] Desc

Regards,

Isaac K.

Former Member
0 Kudos

I appreciate the help.