Dynamics GP: Using SQL to Change Inventory Currency Decimal Places

More by

A customer recently had an issue with the number of decimal places for the price of an item on an open Sales Order.  The item was incorrectly setup with 2 decimal places when it needed to be four.  Note your Item Number and your Order Number. 

Is there a way to change the Currency Decimals on an Item after it has been saved?

YES – there are two ways to accomplish this:

  • Use the Dynamics GP Utility – Change Decimal Places.  Here’s a post on how to do that, including the extra steps if you use Manufacturing.
  • Or for all you SQL gurus, here’s how with code.  This way will change Currency Decimal Places with having to remove items from Work transactions:

Note:  This is for increasing decimal places only.  Do not use this for reducing decimal places.  Reducing decimal places will round down and can have undesired results.

Here’s How:

  • Backup your GP Databases
  • Run the following Script in SQL, which will search for tables that have DECPLCUR as a column.
SELECT * FROM sysobjects o, syscolumns c
WHERE o.id = c.id and o.type = 'U' AND c.name = 'DECPLCUR'
ORDER BY o.name
  • If you are only concerned about distribution modules SOP, POP, and INV, use the following script.
Select DISTINCT C.TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS C WHERE
COLUMN_NAME =
       'DECPLCUR'AND EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE
COLUMN_NAME
       = 'ITEMNMBR' AND C.TABLE_NAME = TABLE_NAME) AND (C.TABLE_NAME LIKE
'SOP%' OR
       C.TABLE_NAME LIKE 'POP%' OR C.TABLE_NAME LIKE 'IV%' OR C.TABLE_NAME
LIKE 'SEE%')
       AND C.TABLE_NAME NOT LIKE '%capture%'
  • This script will search the tales for tables that have DECPLCUR as a column.
  • Check all the tables for ITEMNMBR.
  • If a table has Your Item Number, run the script below.
Update TABLENAME set DECPLCUR = 'y' where ITEMNMBR = 'xxx'

​Note 1: Replace xxx with the Item Number of the Item you need to change Decimal Places for.

Note 2: Replace y with the number of decimal places you are going to change to.  The number set for DECPLCUR is always one more than the number you want.  For example, use the number 5 to set to 4 decimal places.      

  • Lastly, you will want to check your work.  For example, you can open up a Sales Order.  Add new lines for your Item, mirroring the ones on the order.  You should now have four decimal places for the new line items. (For example if you had two items and 2 lines.)  Then, delete the original two lines with the bad decimal places.

If you have questions or need some assistance, visit our support page for more help.

Leave a Reply