Purchases Report

[Former Member]

Hi There,

My Client requested a report to show by date, what the price for items were on the purchase order and then what it was when it was received and again when invoiced. Is there an easy way of getting this information?

Thanks,

Wynand

[Former Member · Accepted solution]

This is not easy by SQL query because you may only get those prices from your marketing documents. You may not find any matches for certain days.

Check this one to see what I mean:

Select T0.ItemCode, T0.ItemName, T1.DocDate, T1.Price as 'Cost', 'On PO' as 'Document'
From dbo.OITM T0
Inner Join  dbo.POR1 T1 ON T1.ItemCode = T0.ItemCode
WHERE T0.ItemCode = '[%0]'
UNION ALL
Select T0.ItemCode, T0.ItemName, T1.DocDate, T1.Price as 'Cost', 'On GRPO' 
From dbo.OITM T0
Inner Join  dbo.PDN1 T1 ON T1.ItemCode = T0.ItemCode
WHERE T0.ItemCode = '[%0]'
UNION ALL
Select T0.ItemCode, T0.ItemName, T1.DocDate, T1.Price as 'Cost', 'On AP Invoice' 
From dbo.OITM T0
Inner Join  dbo.PCH1 T1 ON T1.ItemCode = T0.ItemCode
WHERE T0.ItemCode = '[%0]'
ORDER BY T1.DocDate

Thanks,

Gordon

[Former Member · Accepted solution]

Hi Gordon,

Thanks, we managed to get it working.

One can use this query when a PO is copied to GRPO and then copied to Invoice. This is to show when an item was purchased with a different amount then invoiced.

Thanks for the help.

SELECT PL.ITEMCODE,PL.DSCRIPTION,PL.PRICE,PL.LINENUM,PH.DOCNUM

INTO #TMPPOR

FROM POR1 PL

JOIN OPOR PH ON PL.DOCENTRY = PH.DOCENTRY

SELECT GL.ITEMCODE,GL.DSCRIPTION,GL.PRICE,GL.LINENUM,GH.DOCNUM,GL.BASEREF

INTO #TMPPDN

FROM PDN1 GL

JOIN OPDN GH ON GL.DOCENTRY = GH.DOCENTRY

SELECT IL.ITEMCODE,IL.DSCRIPTION,IL.PRICE,IL.LINENUM,IH.DOCNUM,IL.BASEREF

INTO #TMPPCH

FROM PCH1 IL

JOIN OPCH IH ON IL.DOCENTRY = IH.DOCENTRY

SELECT A.ITEMCODE AS [ITEM CODE],A.DSCRIPTION AS [ITEM NAME],A.DOCNUM AS [PO DOC NUM],A.PRICE AS [PO PRICE],B.DOCNUM AS [GRPO DOC NUM],B.PRICE AS [GRPO PRICE],C.DOCNUM AS [INV DOC NUM],C.PRICE AS [INV PRICE]

FROM #TMPPOR A

LEFT OUTER JOIN #TMPPDN B ON A.ITEMCODE = B.ITEMCODE AND A.DSCRIPTION = B.DSCRIPTION AND A.LINENUM = B.LINENUM AND A.DOCNUM = B.BASEREF

LEFT OUTER JOIN #TMPPCH C ON A.ITEMCODE = C.ITEMCODE AND A.DSCRIPTION = C.DSCRIPTION AND A.LINENUM = C.LINENUM AND B.DOCNUM = C.BASEREF

WHERE (A.PRICE - B.PRICE) + C.PRICE <> A.PRICE

DROP TABLE #TMPPOR

DROP TABLE #TMPPDN

DROP TABLE #TMPPCH