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.