Dynamics GP - SQL script to revert multiple bins into single bins
So you want to turn off multiple binning in Dynamics GP and need to record the multiple bin where the stock is held back into the basic single bin field against each location?
This script I just used to do that, it will pick the multiple bin with the most stock as the one to use for the single bin field. I would take a snapshot of the multiple bins table into excel, just for the record if performing this change (IV00112) and for future reference.
WITH CTE AS ( SELECT ITEMNMBR ,LOCNCODE ,BIN ,ROW_NUMBER() OVER ( PARTITION BY ITEMNMBR ,LOCNCODE ORDER BY QUANTITY DESC ) RowSort FROM iv00112 WHERE qtytype = 1 ) UPDATE IV00102 SET BINNMBR=BIN --SELECT CTE.ITEMNMBR -- ,CTE.LOCNCODE -- ,BIN -- ,BINNMBR FROM CTE JOIN IV00102 ON CTE.ITEMNMBR=IV00102.ITEMNMBR AND iv00102.LOCNCODE=CTE.LOCNCODE WHERE RowSort = 1 AND BIN !=BINNMBR
Then, turn off multiple bins in the inventory setup window, and reconcile the inventory after doing so.