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

[Former Member]

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

[KennedyT21 · Accepted solution]

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

[Former Member · Accepted solution]

Hi Kennedy,

This query is working very well for me except that I get all data since 2006!

Can you suggest some code to select a specific date range?

thanks

Rodney

[former_member212181]

Hi Rodney,

Please try below query which will give PO > GRPO > AP Invoice tracking.

Select POHead.Docnum[PO Num],POHead.DocDate[PO Date], POHead.DocStatus [Po Status]

  ,POHead.CardCode [BP COde]

  ,GRHead.DocNum[GR Num],GRHead.DocDate[GR Date]

  ,APHead.DocNum[AP Num],APHead.DocNum[AP Date]

  , POLine.ItemCode, ItmMstr.ItemName

  , POLine.Quantity[PO Qty], POLine.Price[POPrice]

  , GRLine.Quantity[GR Qty],GRLine.Price[GR Price]

  , APLine.Quantity[AP Qty], APLine.Price[AP price]

from OPOR POHead

  Inner Join POR1 POLine on POHead.Docentry = POLine.DocEntry

  Left Outer Join OITM ItmMstr on POLine.ItemCode = ItmMstr.ItemCode

  Inner Join OCRD BPMstr on POHead.CardCode = BPMstr.CardCode

  Left Outer Join PDN1 GRLine on GRLine.BaseType = 22 and GRLine.BaseEntry = POLine.DocEntry and GRLine.BaseLine = POLine.LineNum

  Left Outer Join OPDN GRHead on GRHead.Docentry = GRLine.DocEntry

  Left Outer Join PCH1 APLine on APLine.BaseType = 20 and APLine.BaseEntry = GRLine.DocEntry and APLine.BaseLine = GRLine.LineNum

  Left Outer Join OPCH APHead on APHead.Docentry = APLine.DocEntry

Where POHEad.DocDate>=[%0] and POHead.DocDate<=[%1]

group By POHead.Docnum,POHead.DocDate,POHead.CardCode

  ,GRHead.DocNum,GRHead.DocDate

  ,APHead.DocNum,APHead.DocDate

  , POLine.ItemCode, ItmMstr.ItemName

  , POLine.Quantity, POLine.Price

  , GRLine.Quantity,GRLine.Price

  , APLine.Quantity, APLine.Price

Thanks

Unnikrishnan

[Former Member]

Hi Rodney,

You may try this one.

SELECT     OPOR.DocEntry, OPOR.DocNum, OPOR.U_PO_type as SeriesName, OPOR.DocDate, OPOR.CardCode, OPOR.CardName, POR1.LineNum, POR1.ItemCode, POR1.Dscription,

                      POR1.Quantity,

                      POR1.WhsCode as Whs,

                       POR1.Price, POR1.unitMsr, POR1.ShipDate, OPDN.DocEntry AS GRPO_Entry, OPDN.DocNum AS GRPO_No, OPDN.DocDate AS GRPO_Dt,

                      PDN1.ItemCode AS GRPO_ItemCode, PDN1.Dscription AS GRPO_ItemName,

(

PDN1.Quantity

-

isnull((Select sum(RPD1.Quantity) From RPD1 Where RPD1.BaseEntry = PDN1.DocEntry and RPD1.BaseType = 20 and RPD1.BaseLine = PDN1.LineNum ),0)

)

AS GRPO_Qty ,

(

PDN1.Price

-

isnull((Select sum(RPD1.Price) From RPD1 Where RPD1.BaseEntry = PDN1.DocEntry and RPD1.BaseType = 20 and RPD1.BaseLine = PDN1.LineNum ),0)

)                    

AS GRPO_Price,

                      OPDN.Numatcard as U_CNO ,OPDN.Taxdate as U_Cdate

FROM         NNM1 RIGHT OUTER JOIN

                      OPOR INNER JOIN

                      POR1 ON OPOR.DocEntry = POR1.DocEntry ON NNM1.Series = OPOR.Series LEFT OUTER JOIN

                      OPDN INNER JOIN

                      PDN1 ON OPDN.DocEntry = PDN1.DocEntry ON POR1.ItemCode = PDN1.ItemCode AND POR1.DocEntry = PDN1.BaseEntry

WHERE     OPOR.DocDate >= {?@Fdate} and OPOR.DocDate <= {?@Tdate}

--and OPOR.CardCode Like (CASE When '{?Cardcode@ Select 'All' as Cardcode,'All' as Cardname from OCRD union Select Cardcode,Cardname from OCRD where CardType = 'S' Order By Cardname}' = 'All' then '%' else '{?Cardcode@ Select 'All' as Cardcode,'All' as Cardname from OCRD union Select Cardcode,Cardname from OCRD where CardType = 'S' Order By Cardname}' end )

--and POR1.ItemCode Like (Case When '{?Item@ Select 'All' as ItemCode,'All' as Itemname from OITM Union Select ItemCode,Itemname from OITM Order by Itemname}' = 'All' then '%' else '{?Item@ Select 'All' as ItemCode,'All' as Itemname from OITM Union Select ItemCode,Itemname from OITM Order by Itemname}' end )

Thanks,

Harshal

[Former Member]

Thanks Harshall,

unfortunately it didn't seem to work when I copied it into my query & ran it

cheers

Rodney