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.

Add comment

Loading