We have 2 systems, the Warehouse management system (WMS) and SAP.
I have a query I can run in SAP which will generate me a TRUE free stock report for all items based on information in the WMS (A1WMS).
The query generate 2 columns, 'WH Free Stock' and PRODUCT
Each PRODUCT is also in our SAP database. I want to pull the product group assigned to these products into the report as a third column. How do I achieve this?
SELECT CAST(TB2.QTY_UNALLOCATED as nvarchar) [WH Free Stock], TB2.PRODUCT FROM (SELECT T40.PRODUCT , SUM(T40.QUANTITY * T40.PACKSIZE) - SUM(ISNULL(T41.QTY_PICKLOC, 0)) - SUM(ISNULL(T42.QTY_REPLENIS, 0)) QTY_UNALLOCATED FROM A1WMS.DBO.BINLOCAT T40 JOIN A1WMS.DBO.BINMSTR T47 ON T40.BINLABEL = T47.BINLABEL LEFT JOIN (SELECT T41.EXTENDED, T41.BINLABEL, T41.PACKSIZE, SUM((T41.QTY_TOPICK - T41.QTY_PICKED) / T41.PACKSIZE) QTY_PICKLOC FROM A1WMS.DBO.PICKLOC T41 WHERE T41.PCKLOC_ST = '' AND T41.ALLOCATED = '1' GROUP BY T41.EXTENDED, T41.BINLABEL, T41.PACKSIZE) T41 ON T40.EXTENDED = T41.EXTENDED AND T40.BINLABEL = T41.BINLABEL AND T40.PACKSIZE = T41.PACKSIZE LEFT JOIN (SELECT T42.EXTENDED, T42.BULKSLOT, T42.BULKPACK, SUM((T42.QTY_REQD * T42.PACKSIZE) / T42.BULKPACK) QTY_REPLENIS FROM A1WMS.DBO.REPLENIS T42 GROUP BY T42.EXTENDED, T42.BULKSLOT, T42.BULKPACK) T42 ON T40.EXTENDED = T42.EXTENDED AND T40.BINLABEL = T42.BULKSLOT AND T40.PACKSIZE = T42.BULKPACK WHERE (T40.QUANTITY > 0 OR T40.BINTYPE <> 'S') AND T40.RESERVED NOT IN ('B', 'I') AND T40.BINLABEL NOT LIKE '#%' AND T47.RESERVED <> 'B' AND T40.BINLABEL NOT LIKE 'RECV%' AND T40.BINLABEL NOT LIKE 'QUAR%' AND T40.BINLABEL NOT LIKE 'REP%' AND T40.BINLABEL NOT LIKE 'RET%' AND T40.ZONE NOT IN ('Q','S','L','C','R') GROUP BY T40.PRODUCT) TB2