Skip to Content

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

Query to get the goods receipt status & post date of a PO

Hi all

I am a beginner at queries & haven't used SQL for a while.

I am trying to write a query which beings me up the following

  • PO number
  • PO status
  • Goods receipt number (if closed)
  • Goods receipt date
  • for a specific period e.g. previous month

I have drafted & tweaked this query but can't get it to work.

(I have attached my draft query as an attachement as for some reason I don't seem to be able to paste in into this message)

Can anyone help please?

thanks

Rodney

PO query.PNG (8373 B)
replied

Hi

Welcome to Forum...

Try this

SELECT

T2.DocNum AS 'Purch Ord',
T2.DocDate AS ' Ord Date',
T2.CardCode AS 'Vend Num',
T1.ItemCode AS 'Product',
T1.LineStatus AS 'Row Stat',
T2.Canceled AS 'Cancelled?',
T1.LineNum+1 AS 'Line',
T1.Quantity AS 'Quantity',
T1.OpenQty AS 'Open Qty',
T4.DocNum AS 'Goods Rcpt',
T4.DocDueDate AS 'Rcpt Date',
T3.LineStatus AS 'Row Stat',
T4.Canceled AS 'Cancelled?',
T3.LineNum + 1 AS 'Line',
T3.Quantity AS 'Quantity',
T6.DocNum AS 'Return',
T5.LineNum +1 AS 'Line',
T5.LineStatus AS 'Row Stat',
T6.Canceled AS 'Cancelled?',
T5.Quantity AS 'Quantity',
T8.DocNum AS 'AP Invoice',
T7.LineNum + 1 AS 'Line',
T7.Quantity AS 'Quantity',
T10.DocNum AS 'AP Cred Memo',
T9.LineNum + 1 AS 'Line',
T9.Quantity AS 'Quantity'

FROM POR1 T1

LEFT OUTER JOIN OPOR T2
ON T1.DocEntry = T2.DocEntry

LEFT OUTER JOIN PDN1 T3
ON T2.DocEntry = T3.BaseEntry
AND T1.LineNum = T3.BaseLine
AND T1.ItemCode = T3.ItemCode

LEFT OUTER JOIN OPDN T4
ON T3.DocEntry = T4.DocEntry

LEFT OUTER JOIN RPD1 T5
ON T4.DocEntry = T5.BaseEntry
AND T3.LineNum = T5.BaseLine
AND T3.ItemCode = T5.ItemCode

LEFT OUTER JOIN ORPD T6
ON T5.DocEntry = T6.DocEntry

LEFT OUTER JOIN PCH1 T7
ON T4.DocEntry = T7.BaseEntry
AND T3.LineNum = T7.BaseLine
AND T3.ItemCode = T7.ItemCode

LEFT OUTER JOIN OPCH T8
ON T7.DocEntry = T8.DocEntry

LEFT OUTER JOIN RPC1 T9
ON T8.DocEntry = T9.BaseEntry
AND T7.LineNum = T9.BaseLine
AND T7.ItemCode = T9.ItemCode

LEFT OUTER JOIN ORPC T10
ON T9.DocEntry = T10.DocEntry

ORDER BY

T2.DocNum,
T1.LineNum,
T4.DocNum,
T3.LineNum,
T6.DocNum,
T5.LineNum,
T8.DocNum,
T7.LineNum,
T10.DocNum,
T9.LineNum

Regards

Kennedy

0 View this answer in context
Not what you were looking for? View more on this topic or Ask a question