Pick List Query - Sales Order

[bbranco]

Hi

I am trying to create a query showing any pick lists that are open that are not picked and include the sales order item.

I am getting some strange results, including items from other orders and I am not sure why.

I am joining OPKL to PLK1 based on the AbsEntry

Then joining ORDR to PLK1 where PLK1.AbsEntry = ORDR.DocEntry

Then I think my problems is joining to RDR1, I need the ORDR.DocEntry + PLK1.LineNum

Can someone help with this? I would appreciate it.

This is on Hana

SELECT

T0."AbsEntry" AS "Pick",

T2."CardCode" AS "Customer",

T0."Status", T0."PickDate",

T1."PickQtty" AS "Pick Qty",

T1."OrderLine" AS "Line",

T3."ItemCode",

T2."DocNum" AS "SO",

T2."DocDate" AS "SO Date"FROM OPKL T0

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

INNER JOIN ORDR T2 ON T1."OrderEntry" = T2."DocEntry"

INNER JOIN RDR1 T3 ON T2."DocEntry" + T1."OrderLine" = T3."DocEntry" + T3."LineNum"

WHERE (T2."CardCode" = 'AMAZON' OR T2."CardCode" = 'NSLWEB' OR T2."CardCode" = 'WALMART') AND T1."PickQtty" = 0 AND T0."Status" <> 'C'

ORDER BY T0."AbsEntry", T1."OrderLine"

[SonTran · Accepted solution]

Hi,

Try this

SELECT

T0."AbsEntry" AS "Pick",

T2."CardCode" AS "Customer",

T0."Status", T0."PickDate",

T1."PickQtty" AS "Pick Qty",

T1."OrderLine" AS "Line",

T3."ItemCode",

T2."DocNum" AS "SO",

T2."DocDate" AS "SO Date" FROM OPKL T0

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

LEFT JOIN RDR1 T3 ON T1."OrderEntry"=T3."DocEntry" and T1."OrderLine"=T3."LineNum"

LEFT JOIN ORDR T2 ON T2."DocEntry" = T3."DocEntry"

WHERE (T2."CardCode" = 'AMAZON' OR T2."CardCode" = 'NSLWEB' OR T2."CardCode" = 'WALMART') AND 
T1."PickQtty" = 0 AND T0."Status" <> 'C'

ORDER BY T0."AbsEntry", T1."OrderLine";

Hope this helps,

Son Tran

[bbranco · Accepted solution]

Thanks for your help, I apprecate it.