how to link the inventory transfer with picklist and transfer Request

[alyallam88]

Sir,
wanna create query to get warehouses transfer detail but i cannot link the picklist with inventory transfer to know the inventory transfer DocNum

SELECT Distinct T0."AbsEntry" as "Pick List No.", T0."Status", T0."Name", T0."PickDate", T0."U_NoOfCartons", T2."DocNum" as "Stock Transfer Request No.", T3."ItemCode", T3."Dscription", T3."CodeBars", T1."PickQtty", T1."RelQtty", T2."Filler" as "From Warehouse", T2."ToWhsCode", T2."DocDate" as "Request date", T2."DocStatus", T4."U_Season"

FROM OPKL T0

INNER JOIN PKL1 T1 ON T0."AbsEntry" = T1."AbsEntry"

INNER JOIN WTQ1 T3 ON T1."OrderEntry" = T3."DocEntry" AND T1."OrderLine" = T3."LineNum"

INNER JOIN OWTQ T2 ON T2."DocEntry" = T3."DocEntry"

INNER JOIN OITM T4 ON T3."ItemCode" = T4."ItemCode"

[SonTran · Accepted solution]

Hi,

Try this

SELECT  T0."AbsEntry" as "Pick List No.", T0."Status", T0."Name", T0."PickDate"
, T0."U_NoOfCartons"
, T2."DocNum" as "Stock Transfer Request No.", T3."ItemCode", T3."Dscription", T3."CodeBars", T1."PickQtty"
, T1."RelQtty", T3."FromWhsCod" as "From Warehouse", T3."WhsCode", T2."DocDate" as "Request date", T2."DocStatus"
, T4."U_Season"
, T7."DocNum" as "Stock Transfer No."
FROM OPKL T0 INNER JOIN PKL1 T1 ON T0."AbsEntry" = T1."AbsEntry"
LEFT JOIN WTQ1 T3 ON T1."OrderEntry" = T3."DocEntry" AND T1."OrderLine" = T3."LineNum" and T1."BaseObject"='1250000001'
LEFT JOIN OWTQ T2 ON T3."DocEntry" = T3."DocEntry"
LEFT JOIN OITM T4 ON T3."ItemCode" = T4."ItemCode"
LEFT JOIN WTR1 T6 ON T6."BaseType"='1250000001' and T6."BaseEntry"=T3."DocEntry" and T6."BaseLine"=T3."LineNum"
LEFT JOIN OWTR T7 ON T6."DocEntry"=T7."DocEntry"

Hope this helps,

Son Tran

[alyallam88 · Accepted solution]

Dear Son, I am deeply grateful for your invaluable assistance and support. Thank you sincerely.