Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

Number of times an Item of Stock has been Received (Goods Receipt PO)?

Hi Everyone,

I have been asked to create a report to show how often a stock item has been received amongst other details; such as the last Goods Receipt Purchase Order #, and date!

Here is the query that I have created; it is doing everything except returning the number of times that a given Item Code has been received (which is the most important part!)

SELECT

T0.ItemCode AS 'Item Code'

, T0.Dscription AS 'Description'

, MAX(T1.DocNum) AS 'Last Goods Receipt PO #'

, MAX(T1.DocDate) AS 'Last Receipt Date'

, SUM(T0.Quantity) AS 'Received since Go Live!'

, T2.OnHand AS 'Currently On Hand'

, SUM(T1.DocNum) AS '# of Times Received'    --Number of Goods Receipt Purchase Orders for the given ItemCode

FROM AU.dbo.PDN1 T0

INNER JOIN AU.dbo.OPDN T1 ON T1.DocEntry = T0.DocEntry

INNER JOIN AU.dbo.OITM T2 ON T2.ItemCode = T0.ItemCode

GROUP BY T0.ItemCode, T0.Dscription, T2.OnHand

ORDER BY T0.ItemCode


The line SUM(T1.DocNum) AS '# of Times Received' is actually returning the SUM of all Document Numbers associated with a given Item Code, whereas I really just want to know how many times a given Item has been received! For example if there are 12 Goods Receipt Purchase Orders for Item 'X' then I simply want to show 12, rather than some obscenely big (meaningless) number.

Any help here will be greatly appreciated.

Kind Regards,

David

Former Member
Former Member replied

hi,

use below query

SELECT 
T0.ItemCode AS 'Item Code' 
, T0.Dscription AS 'Description' 
, MAX(T1.DocNum) AS 'Last Goods Receipt PO #' 
, MAX(T1.DocDate) AS 'Last Receipt Date' 
, SUM(T0.Quantity) AS 'Received since Go Live!' 
, T2.OnHand AS 'Currently On Hand' 
, count (distinct T1.DocNum) AS '# of Times Received'   
FROM PDN1 T0 
INNER JOIN OPDN T1 ON T1.DocEntry = T0.DocEntry 
INNER JOIN OITM T2 ON T2.ItemCode = T0.ItemCode 
GROUP BY T0.ItemCode, T0.Dscription, T2.OnHand 
ORDER BY T0.ItemCode

regards,

Raviraj

0 View this answer in context

Helpful Answer

by
Not what you were looking for? View more on this topic or Ask a question