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"