From the newsgroups: Changing item currency decimal places

Every once in a while as consultants, we run into these requests that leave us scrambling for an answer. It is not at all strange to find companies that want to modify the currency decimal places supported only at the product level (item master), while wanting to maintain the currency decimals set from an accounting perpective (general ledger and financial reporting). For example, for product transaction purposes, some companies may require 3 or 4 decimal places, but for financial reporting and general ledger would still want to maintain 2 decimals. Here is what the consultant requested:

Is there a way to change "Currency Decimals" on the Item Maintenance window after an item is saved? If the field is not accessible which field in which SQL table has to be changed?

The answer comes courtesy of Microsoft's Tirumal Boppana from the Dynamics GP Online Partner Technical community forum. Tirumal outlines two methods that allow you to change your currency decimals and explains the values stored in the tables representing the on-screen decimal places.

There are actually two ways in changing the currency decimal places of the item.

Note: Secure a current restorable backup of the company database. You also have the option to restore your backup to a test company database so you can go through these steps in the test environment. I have attached KB Article 871973 - How to set up a test company that has a copy of live company data by using SQL Server 7.0, SQL Server 2000, or SQL Server 2005.

Option 1: Change the currency decimal place using the Change Decimal Places Utility. (Recommended)

1. Delete all unposted transactions against the item number from all module such as IV, SOP, POP, Manufacturing, Bill of Materials and Field Service. These transactions could be allocation of the item, stock count and anything that updates Inventory tables.
2. Use the utility to change the decimal place.

a. Go to Change Decimal Places window (Microsoft Dynamics GP menu - Utilities - Inventory - Change Decimal Places).
b. Mark Change Currency Decimal Place option and then click Yes when you get the prompt saying that 'Changing the currency decimals will round the amounts in the price list for each item included in the range. Do you want to continue?'
c. Select the decimal place you wish to change it to.
d. Select the appropriate Currency ID.
e. Click on Process.

Option 2: How to change currency decimals without having to remove items from Work transactions and without having to change price lists.

Here are the lists the steps that we can take to change currency decimals for items with a high turnover rate and cannot be removed from Work transactions. If the users would like to keep the price list of the item then this set of steps will also help.

Note: Do not use these steps if you are reducing decimal places and if the decimals you would like to lose have already been used. Doing this may cause data integrity issues for you on the costing of your items.

For example: Let's say that some of my cost layers for my A item have a cost tag that takes all its currency decimal places (like $1.12345 for 5 decimals, $1.1234 for 4 decimals and $1.123 for 3 decimals). If I force a change of the currency decimals to 2 for my item, then these cost values will be truncated (to show $1.12) and may cause issues with the cost values being posted to the General Ledger. This goes with the item prices that you have setup as well.

1. Make a complete backup of the company database. You can restore this backup to a test database if you would like to test these steps in a test environment first.

2. In SQL Server Management Studio, run the script below:

-- This script is provided "AS IS"
SELECT * FROM sysobjects o, syscolumns c
WHERE o.id = c.id AND o.type = 'U' AND c.name = 'DECPLCUR'
ORDER BY o.name

** This script will search the tables that have the DECPLCUR as a column name.


3. Run the script below only if the tables that were listed by the script in step 1 has an ITEMNMBR field.

-- This script is provided "AS IS"
Update TABLENAME set DECPLCUR = '0' where ITEMNMBR = 'xxx'

Note #1 : Replace xxx with the item number of the item we need to change decimals for.


Note #2 : Replace Y with your preferred currency decimal place field. Below are the possible integer values of the Currency Decimal Place (DECPLCUR) field in SQL Query Analyzer:


1 - 0 decimal place
2 - 1 decimal place
3 - 2 decimal places
4 - 3 decimal places
5 - 4 decimal places
6 - 5 decimal places


Example: Since I wanted to have 5 Decimal Places, the Update statement is: Update IV10001 set DECPLCUR = '6' where ITEMNMBR = 'CURRENCY'


Note: This option would not affect posted transactions. This change would affect the transactions that are to be posted.

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.intellpartners.com/