SKU Stock Count Dump from GP to Import into Accellos WMS

SKU Stock Count Dump from GP to Import into Accellos WMS

A few things to note about this query:
– it was developed to dump inventory from a certain site location into an import file to populate the Accellos WMS to avoid doing a full stock count when implementing the WMS.
– the column [+ / -] is used to signify a positive or negative import value to the WMS. I suggest creating a positive import and a negative import to avoid data issues.
– the column [Packsize] is defaulted to the GP packsize for the inventory. In this case it is 1.
– remove all commas from the data that you are exporting from GP before importing it into the WMS. Commas in the data with cause the import to fail.

Variables in the query:
– FIFO Date. Replace the XXXX-XX-XX with the date you want the import to reference
– a.qtyonhnd. Replace the X with the quantity on hand you want to focus on. In the case of this query the X was set to 0 to bring in any SKU’s with stock-able quantities.
-a.locncode. Replace the XXX with the site location from which you are exporting data.

select ‘CA’ [CA], ‘MA’ [Adjustment Subcode], a.itemnmbr [Product Code], b.itemdesc [Description], b.UOMSCHDL [Unit of Measure], b.ITMCLSCD [Product Class], ” [UPC], a.qtyonhnd [Quantity], ‘1’ [Packsize], ‘+’ [+ / -],

a.binnmbr [BINNMBR],

” [Reserved Stock Flag], ” [PO Number], ” [Comment], ” [Attribute 1], ” [Attribute 2], ” [Attribute 3], ” [Attribute 4], ” [Attribute 5], ” [Attribute 6], ” [Attribute 7], ” [Attribute 8], ” [Attribute 9],

” [Attribute 10], ” [Receiving Attribute Control], ‘XXXX-XX-XX’ [FIFO Date], ” [Client Name], ” [Inner Pack], ” [Minimum Level of Replinishment], ” [Maximum Level of Replinishment], ” [Liscense Plate]

from iv00102 a

inner join iv00101 b on

a.itemnmbr = b.itemnmbr

where a.qtyonhnd = ‘X’

and

a.LOCNCODE = ‘XXX’

and

a.BINNMBR <> ‘ ‘