Skip to Content
0
Aug 10, 2023 at 02:44 PM

Pick List Query - Sales Order

90 Views

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"