Comparing Dynamics GP Inventory to Accellos WMS Inventory and Sorting by Myriad Procurement Ranking
Comparing Dynamics GP Inventory to Accellos WMS Inventory and Sorting by Myriad Procurement Ranking
— Developed to give real time insight into SKU’s that do not sync quantity wise between WMS and GP
— Converted to a stored procedure to produce a user friendly Excel report from 3rd party GP Reports viewer that is the reasoning behind the barcode piece. The Excel converts it to a scanable — barcode. The EZ number is a UPC reference
— Compares WMS available quantity to GP available quantity
— Uses 3rd party GP table from Myraid Procurement to produce SKU ranking
— This report assumes a few things:
— #1 You know how to link different DB servers together as seen in the sub-queries
— #2 It is pulling GP inventory quantities from multiple sites that are related back to the WMS site locations
— #3 The GP quantity calculations are based off the inventory buckets that we use
select A.itemnmbr [Item #], B.uscatvls_1 [EZ Number], ‘*’ + rtrim(B.uscatvls_1) + ‘*’ [Item Barcode], rtrim(B.itemdesc) [Item Description],
ISNULL(CASE
WHEN A.tcsFLDL_itemrank = ‘1’ then ‘A’
WHEN A.tcsFLDL_itemrank = ‘2’ then ‘B’
WHEN A.tcsFLDL_itemrank = ‘3’ then ‘C’
WHEN A.tcsFLDL_itemrank = ‘4’ then ‘D’
WHEN A.tcsFLDL_itemrank = ‘4’ then ‘X’
END,’?’) [Rank], rtrim(C.locncode) [Site], C.qtyonhnd [GP QTY on Hand], C.atyalloc [GP QTY Allocated], (C.QTYINUSE + c.QTYDMGED + c.QTYINSVC) [GP Other Inv Buckets], (C.qtyonhnd – C.atyalloc) [GP QTY Available for Sale],
rtrim((C.qtyonhnd – C.atyalloc + C.qtyinuse + C.qtydmged + C.qtyinsvc)) [GP QTY CNT],
rtrim(isnull((select sum(x.quantity) from [XXX].[XXX].[dbo].[binlocat] x where x.product = a.itemnmbr and C.locncode =’HT-DAMAGE’ and x.binlabel like ‘HTDMGD%’),0)) [WMS DMG QTY],
rtrim(isnull((select sum(x.quantity) from [XXX].[XXX].[dbo].[binlocat] x where x.product = a.itemnmbr and C.locncode =’MAIN’ and x.binlabel not like ‘HTDMGD%’),0)) [WMS AVAIL QTY]
from tcsINVTB00091_IR_ISD A
left outer join iv00101 B
on A.itemnmbr = b.ITEMNMBR
left outer join iv00102 C
on A.itemnmbr = C.ITEMNMBR
and C.locncode <> ‘ ‘