Dynamics GP - How to update Item Decimal Places, Currency Decimal Places and Standard Price Lists without wiping out all the prices

This script will update ALL items and ALL UofM schedules. Use a WHERE if you need to update specific ranges to different values.
Do not use this on a live environment with transactions being processed
This is meant for setup only in a new environment or test environment.
Run check links after to ensure the item is setup correctly.



update IV40201 set UMDPQTYS = 3 --Sets all UofM dp's to 2
UPDATE IV00101 SET DECPLQTY=3 --Sets all Inventory DP's to 2
update IV00108 set TOQTY = 999999999999.99, FROMQTY = 0.01 --Sets all pricing ranges to the correct DP's

update sop10200 set decplqty = 3 --fix open sop line items
update POP10110 set decplqty = 3 --fix open pop line items
update iv10001 set decplqty=3 --fix open inventory trx line items

Rebuild any Inventory BOMs
Rebuild any MFG BOMS
Update iv10402 to fix the UofM if it has changed, and the QTYTO to set the correct FROMQTY and TOQTY
Update iv00106 to fix the UofM if it has changed


To change item currency decimals, refer to this article
http://dynamicsgpblogster.blogspot.com/2011/02/from-newsgroups-changing-item-currency.html

For multicurrency transactions, the decplcur values are as follows
Number = decimal places
7 = 0
8 = 1
9 = 2
10 = 3
11 = 4
12 = 5

The total scripts i used to update the currencies dp's to 5dp for all currencies for this item were as follows.
These scripts are repeated for each table identified from

SELECT * FROM sysobjects o, syscolumns c
WHERE o.id = c.id AND o.type = 'U' AND c.name = 'DECPLCUR'
ORDER BY o.name

that has an itemnmbr and a decplcur field
--This is for the functional currency
--select 'IV00101', * from IV00101 where itemnmbr = 'Item001' and decplcur <6
update IV00101 set decplcur = 6 where itemnmbr ='Item001' and decplcur <6

--This is for the multicurrency
--select 'IV00101', * from IV00101 where itemnmbr = 'Item001' and decplcur >6
update IV00101 set decplcur = 12 where itemnmbr ='Item001' and decplcur >6


Therefore a complete example of all the scripts i used for this one item are

update IV00101 set decplcur = 6 where itemnmbr ='Item001' and decplcur <6
update IV00105 set decplcur = 6 where itemnmbr ='Item001' and decplcur <6
update IV10001 set decplcur = 6 where itemnmbr ='Item001' and decplcur <6
update IV30300 set decplcur = 6 where itemnmbr ='Item001' and decplcur <6
update IV30701 set decplcur = 6 where itemnmbr ='Item001' and decplcur <6
update IV50300 set decplcur = 6 where itemnmbr ='Item001' and decplcur <6
update IVC10101 set decplcur = 6 where itemnmbr ='Item001' and decplcur <6
update IVC30102 set decplcur = 6 where itemnmbr ='Item001' and decplcur <6
update POP10110 set decplcur = 6 where itemnmbr ='Item001' and decplcur <6
update POP10310 set decplcur = 6 where itemnmbr ='Item001' and decplcur <6
update POP30110 set decplcur = 6 where itemnmbr ='Item001' and decplcur <6
update POP30310 set decplcur = 6 where itemnmbr ='Item001' and decplcur <6
update PT10000 set decplcur = 6 where itemnmbr ='Item001' and decplcur <6
update SEE30303 set decplcur = 6 where itemnmbr ='Item001' and decplcur <6
update SOP10200 set decplcur = 6 where itemnmbr ='Item001' and decplcur <6
update SOP30300 set decplcur = 6 where itemnmbr ='Item001' and decplcur <6
update IV00101 set decplcur = 12 where itemnmbr ='Item001' and decplcur >6
update IV00105 set decplcur = 12 where itemnmbr ='Item001' and decplcur >6
update IV10001 set decplcur = 12 where itemnmbr ='Item001' and decplcur >6
update IV30300 set decplcur = 12 where itemnmbr ='Item001' and decplcur >6
update IV30701 set decplcur = 12 where itemnmbr ='Item001' and decplcur >6
update IV50300 set decplcur = 12 where itemnmbr ='Item001' and decplcur >6
update IVC10101 set decplcur = 12 where itemnmbr ='Item001' and decplcur >6
update IVC30102 set decplcur = 12 where itemnmbr ='Item001' and decplcur >6
update POP10110 set decplcur = 12 where itemnmbr ='Item001' and decplcur >6
update POP10310 set decplcur = 12 where itemnmbr ='Item001' and decplcur >6
update POP30110 set decplcur = 12 where itemnmbr ='Item001' and decplcur >6
update POP30310 set decplcur = 12 where itemnmbr ='Item001' and decplcur >6
update PT10000 set decplcur = 12 where itemnmbr ='Item001' and decplcur >6
update SEE30303 set decplcur = 12 where itemnmbr ='Item001' and decplcur >6
update SOP10200 set decplcur = 12 where itemnmbr ='Item001' and decplcur >6
update SOP30300 set decplcur = 12 where itemnmbr ='Item001' and decplcur >6