Goods Receipt Query Help

[former_member711203]

I have this query made and it shows all items received for the current day (for PO's created by a specific user), and I want it to only show items received (on the current day) that are items on open lines on sales orders. I don't know how to tie in the RDR1 table to this query. Can anyone help?

SELECT T2."DocNum" "PO Number", T2."CardCode" "Vendor Code", T2."CardName", T0."ItemCode", T0."Dscription", T0."Quantity" "Received Qty", T3."DocEntry" "GRPO Key", T3."DocTime", T3."UpdateDate", T3."UpdateTS" FROM PDN1 T0 INNER JOIN POR1 T1 ON T0."BaseType" = T1."ObjType" AND T0."BaseEntry" = T1."DocEntry" AND T0."BaseLine" = T1."LineNum" INNER JOIN OPOR T2 ON T1."DocEntry" = T2."DocEntry" INNER JOIN OPDN T3 ON T0."DocEntry" = T3."DocEntry" WHERE T2."UserSign" IN ('8') AND T3."CreateDate" = CURRENT_DATE

[former_member711203]

I have this query made and it shows all items received for the current day (for PO's created by a specific user), and I want it to only show items received (on the current day) that are items on open lines on sales orders. I don't know how to tie in the RDR1 table to this query. Can anyone help?

SELECT T2."DocNum" "PO Number", T2."CardCode" "Vendor Code", T2."CardName", T0."ItemCode", T0."Dscription", T0."Quantity" "Received Qty", T3."DocEntry" "GRPO Key", T3."DocTime", T3."UpdateDate", T3."UpdateTS" FROM PDN1 T0 INNER JOIN POR1 T1 ON T0."BaseType" = T1."ObjType" AND T0."BaseEntry" = T1."DocEntry" AND T0."BaseLine" = T1."LineNum" INNER JOIN OPOR T2 ON T1."DocEntry" = T2."DocEntry" INNER JOIN OPDN T3 ON T0."DocEntry" = T3."DocEntry" WHERE T2."UserSign" IN ('8') AND T3."CreateDate" = CURRENT_DATE

[neilos]

Hi Marissa,

As far as I can make out, RDR1 and POR1 can be joined by the 'DocEntry' field.
So adding something like in your FROM

INNER JOIN RDR1 T4 ON T1.[DocEntry] = T4.[DocEntry]

[former_member711203]

Thank you Neil! I will give that a try. 🙂