>From the newsgroups: Changing item currency decimal places

February 3, 2011

>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 871973How 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!

Mariano Gomez, MVP
IntellPartners, LLC


From the newsgroups: Tracking COBRA in Microsoft Dynamics GP Payroll (US only)

December 20, 2010

This week’s answer comes courtesy of Microsoft’s Aaron Richards over at the Partner Online Technical Community, but first the question — no names given to protect the innocent 🙂

We are using Dynamics GP2010. How do we track COBRA in Payroll. We are not using HR.

Specifically, we are trying to track what the government reimbureses us. For example, we’re trying to track 70% that we pay to the insurer on the 941 that we send to the government which eventually reimburses back from the government that we had paid.

Please advise or point me to a reference for utilizing this.

As stated by the partner, the customer is not using the HR module which has full COBRA tracking capabilities. Here is what Aaron had to say:

Thank you for using Microsoft Online Communities. My name is Aaron and I will be assisting you with your questions today. This information was released when we started tracking COBRA in the hotfix pdf.

It stated the following:

Consolidated Omnibus Reconciliation Act (COBRA) changes

The recently-passed economic stimulus legislation (American Recovery and Reinvestment Act of 2009) establishes an employer-provided 65% COBRA premium subsidy for certain workers who lost their jobs between Sept. 1, 2008, and Dec. 31, 2009. The employer is reimbursed for the subsidy by claiming a credit on quarterly federal tax returns (Form 941). For details of the Act’s provisions relating to COBRA, see http://www.irs.gov/newsroom/article/0,,id=204505,00.html at the IRS site, and http://www.dol.gov/ebsa/COBRA.html at the Department of Labor site. The March 2009 Round 4 U.S. Payroll Tax Update contains the following Payroll and Human Resources changes to support the COBRA premium subsidy.

The Payroll Setup window contains new fields that allow you to select and display a COBRA subsidy benefit code.

The Cobra Premiums and Payments window in Human Resources contains a new field that allows you to indicate whether a payment is an employer-paid COBRA Premium Subsidy. If it is, you can enter or select a Batch ID for the payment (the batch lookup will display only manual check batches). These fields will be accessible only if you have assigned a COBRA subsidy benefit code in the Payroll Setup window.

The Quarterly 941 Preparation report and the Form 941 report have been changed to display COBRA subsidy information. Line 12a shows the total of the posted COBRA subsidy benefits for all employees during the associated quarter. Line 12b shows the number of employees receiving COBRA subsidy benefits during the associated quarter. The changes are included on both single-company and cross-company quarterly 941 reports.

Additionally, lines Line 7d through 7g are removed from Form 941, and Line 7h (Total Adjustments) becomes Line 7d. The form layout is changed to reflect the new field positions.

The Act specifies that employers must notify certain current and former participants and beneficiaries about the premium reduction. The U.S. Department of Labor has posted links to four model notices at http://www.dol.gov/ebsa/COBRAmodelnotice.html. You will need to decide which notification to use. A link to the Department of Labor Web site has been added to the Human Resources COBRA Recipients Lists window. There are no changes to the COBRA notifications generated by Human Resources.

March 2009 Round 4 U.S. Payroll Tax Update – 4

Setting up the COBRA subsidy
1. In the Benefit Setup window, create a new benefit code to track the employer COBRA subsidy.

2. In the Payroll Posting Accounts Setup window, assign General Ledger account codes for the employer subsidy payment (Benefit Expense and Benefit Payable).

If you do not want to post the amount to General Ledger, you can use the same account for both codes.

3. In the Payroll Setup window, assign the new benefit code as the COBRA subsidy benefit.

4. For any employees electing to receive COBRA benefits and eligible for the 65% subsidy, assign the new benefit code to the employee, using the Employee Benefit Maintenance window. Mark the Transaction Required option.

5. Inactivate the employee pay records, as well as all other deduction, benefit, and tax records for the employee.

6. Keep the employee record as well as the new COBRA benefit active.

Processing a COBRA subsidy if you are using only Payroll

1. In the Payroll Manual Check-Adjustment Entry window, enter an adjustment transaction for the employee.

2. In the Payroll Manual Check Transaction Entry window, specify the COBRA benefit code and the amount of the subsidy.

3. Post the adjustment transaction, which updates the benefit code.
4. At quarter end, print the Quarter End 941 report, which includes the COBRA subsidy amount.

I hope this in-depth review helps with your COBRA strikes.

Until next post!

Mariano Gomez, MVP
Maximum Global Business, LLC

SmartList Builder "Display as a negative value based on field" option not working as expected

December 14, 2010

Just recently on a newsgroup forum, a partner brought up an issue affecting SmartList Builder. The partner had just recently upgraded the client from version 9 and was testing the Smartlists the customer had built prior to the upgrade to ensure they were still working as expected. In the partner’s own words:

“It was working when we were on GP9, but now we’re on GP2010 SP1 none of my fields set up with Negative Values are showing up with Negative Values. When I run the smartlist from the old GP9 server, amounts are showing negative amounts for Return documents. But when we run the smartlist on GP2010, the amounts are now positive amounts, and when I check the Set Field Options window, they are still set up to “Display as negative based on field” when SOP Type = Return.”

In order to verify this I launched GP 2010 and recreated the SmartList mentioned above by the partner, setting the field option for the Document Amount to display a negative value based on a SOP Type of Return:

Set Field Options window

After saving the SmartList Builder option, I launched SmartList and had it build the newly created option. Once I checked the entry I had just created, effectively Returns were no displaying as negative.

BUG: Returns document amounts not displayed as negative

This bug seems to be confined to Microsoft Dynamics GP 2010 RTM (Build 11.00.1247) and Microsoft Dynamics GP 2010 SP1 (Build 11.00.1524). I could not replicate this issue for version 10.0 SP5 (10.00.1579) or earlier.

As a workaround, you can create a SmartList Builder calculated field to change the sign of the amount being displayed based on the SOP Type, as follows:

Add Calculated Field

NOTE: You can perform a similar CASE statement for any other SmartList you have created based on the field(s) criteria you are using to render the value a negative value.

This problem has been reported to Microsoft and a bug report is being written up to address the issue as of the publishing date of this article. For more information, contact Microsoft Dynamics GP Support.

Until next post!

Mariano Gomez, MVP
Maximum Global Business, LLC

From the Newsgroups: "The input tax for this range hasn’t been entered. Do you want to continue calculating?" error when running VAT Return on Microsoft Dynamics GP v10

October 5, 2010

While attempting to generate a VAT Return report for his customer, a partner ran into the following issue.

Hi folks , weird one here. Have ran fine in the past,however we are now getting this error: ‘The input tax for ths range hasn’t been entered. Do you want to continue calculating?

The only change is that we have now set up all the reverse charge functionality for EU VAT. So the question is, where is the VAT return looking for the input tax?

Fortunately, the partner dog enough into this issue and found a solution which he did not mind sharing with the rest of the partner community.

Ok fixed it! Extracted all the transactions in SmartLists for the tax reporting period, and pivot tabled them on the tax detail id, compared the id’s to those setup in GP only to discover that a tax detail id used 6 times does not physicly exist, [I then] changed the tax detail id in SQL to rectify to the correct id. ID must have been deleted some how.

So if you experience the same problem, now you know how to fix it.

Until next post!

Mariano Gomez, MVP
Maximum Global Business, LLC

From the Newsgroups: What are those GL entries with reference SALESASMxxxx?

July 30, 2010

Welcome to another edition of From the Newsgroups.

The Microsoft Dynamics GP Online Partner Technical Community forum is one of those virtual places where you get to see and experience it all. One gets to become familiar with real life issues experienced by us partners keen enough to share our implementation and support issues. The following is a thread from the Online Partner Technical Community:

Good afternoon. Our customer keeps getting these “mystery” GL postings [SALESASMxxxx] and wants to know why they are being generated but I cannot find any article or search that shows this document prefix for GP. Does anyone know what type of doc this is and why it may be generated automatically into GL?

The answer comes courtesy of Tristan Thor Clores, a Microsoft Support Engineer.

For transactions that have the SALESASM prefix in their description: These may mean that a sales invoice with a shortage override of a top level BOM (also known as finished good) was posted at one cost. Then when the BOM that fulfilled the shortage was posted its unit cost was different from what the invoice held.

Let’s pretend that we have a $0.00 balance in the Inventory-Finished Goods account. An invoice was posted with a shortage override for 10 units of finished good item HARD DISK. Its unit cost is $10.00 and its unit price is $20. Its journal entry will look like the following:

Debit: Accounts Receivables $200.00
Debit: Cost of Sales $100.00

Credit: Sales $200.00
Credit: Inventory-Finished Goods $100.00

However, when a BOM assembly was posted for 10 units of HARD DISK, its unit cost was actually at $12.00 per base unit. Let’s pretend that it has the following account distributions:

Debit: Inventory-Finished Goods $120.00

Credit: Inventory-Assembly Component #1 $50.00
Credit: Inventory-Assembly Component #2 $70.00

This journal entry will result in you having 0 units On Hand for the item but with a debit balance of $20.00 in the General Ledger. So now, Microsoft Dynamics GP will create the following cost variance journal entry to remove this balance and help your GL recognize the true cost of the sale. This change though will never hit Sales Order Processing history and so the SOP Document Analysis report will not print this additional cost and will then print an incorrect profit margin afterwards:

Debit: Cost of Sales $20.00
Credit: Inventory $20.00

I would also like to share the following list with you. It is a list of the transaction reference prefixes that Microsoft Dynamics GP support engineers have encountered with cost variance journal entries so far:

1. BOM – assembly transaction
2. INV – invoice from the Invoicing module
3. IVT – inventory transfer
4. IVA – inventory adjustment
5. IVV – inventory variance
6. SALES – sales invoice from Sales Order Processing
7. PRTN – purchase return
8. MCTE – for a transaction that originated in Manufacturing Component Transaction Entry
9. MRCT – manufacturing receipt
10. MCLS – manufacturing close, including regular close and Quick MO
11. STCK – stock count variance
12. FSSC – field service Service Call
13. FSRMA – field service RMA
14. FSRTV – field service RTV
15. FSWO – field service Work Order
16. PA – project accounting
17. POP – close a PO line in Edit PO Status
18. RECON – created by Inventory Reconcile
19. CONV – created by an upgrade conversion
20. MCTERCT – If the items were issued in the Component Transaction Entry window and then the invoice is completed the adjustment will have a prefix of MCTERCT
21. MRCTRCV – If the items were issued in Mo Receipt Entry (they are “backflushed”) and then the invoice is complete the adjustment will have a prefix of MRCTRCV.
22. MCLSRCV – If the items were issued in the MO Close process (the MO was partially received and more “backflushed” items were issued during the close) and then the invoice is completed the adjustment will have a prefix of MCLSRCV.

I recommend going through KB Article 869470 – “Cost Variance for Inventory,” “Sales Order Processing,” and “Purchase Order Processing” for mor details regarding cost variance in the system.

Let me know how this goes.

I hope you find this article very interesting.

Related Articles

What do those strage reference codes in GL mean?

Until next post!

Mariano Gomez, MVP
Maximum Global Business, LLC

From the Newsgroups: What happened to Integration Manager "Save As" function?

July 8, 2010

In previous versions of Integration Manager you had the ability to save an integration with another name, but v10 onwards this feature was removed. Here is what Beth Gardner from the Microsoft Dynamics GP Online Partner Technical Community had to say:

“We removed the Save As functionality in one of the IM 10.0 service packs. The reason why, was the Save As feature had a bug with it for many years. It would copy the integration source file instead of creating a new integration source file in the new integration. This meant that if you were to change the source file on the second integration to browse to a different file, the original integration source file would also change.

This caused a large amount of confusion and problems for customers. Due to this, we removed it from the menu. What you will need to do instead is use the File Import Integrations process. This will allow you to browse out the the IM.mdb file you currently have open and select integrations. You will then be prompted to change the integration name and integration source names. This is a true copy of the original integration but has no ties to it and everything is unique.

This is the process you will need to follow going forward.”

I hope you found this information useful.

Until next post!

Mariano Gomez, MVP
Maximum Global Business, LLC

From the Newsgroups: Upgrading 150+ company databases

June 10, 2010

This week the partner newsgroup takes on the topic of upgrades and you will be surprise to know the different approaches available to do upgrades for large amounts of company databases, especially when down time is not much of an option.

Q: Have got a situation with a client with 150+ companies (databases) on version 9. Client is extememly downtime sensitive. Is there a supported upgrade technique or method to minimized downtime?

The answer comes courtesy of Microsoft’s Randal Mayer, Partner Technical Online Community moderator.

A: The supported approach is to prioritize the databases by urgency and importance of being completed. It is the high priority databases which will require being upgraded first, validated, backed up and then released to production. For the remaining databases of less importance and less urgency, run Utilities to convert them afterwards ideally after hours.

NOTE: All company databases are not required to be upgraded. In other words, once the system database DYNAMICS and a company database are upgraded, users having the new GP client can access GP to view the upgraded company data.

Dynamics GP Utilities by design does not allow an in-place upgrade of a database to run while users are in that database. In order to upgrade a database, a lock is placed on the database (duLCK table) to ensure users cannot access it from within Dynamics GP.

As you know, once the system DYNAMICS database is upgraded, no user will be able to access Dynamics GP unless using a GP install at the new version.

Not found in a manual but known to be occurring, you can also run Dynamics GP Utilities from multiple GP installs. The risk is of an interruption or resource issue at the SQL Server machine. If SQL Server machine has the resources and ideally designated strictly for SQL Server, multiple clients running GP Utilities can be converting company databases. I find this batch type approach where high priority companies are upgraded via GP Utilities at the SQL Server and lower priority companies from a client or two machine.

There are other approaches like upgrading databases amongst multiple SQL Server instances then restoring plus others. To stay within a supported approach as you request, I will stop here with the above information.

It is becoming not all that uncommon to find 100+ database GP installations. In summary, prioritize the databases during your upgrade planning.

I hope this information is useful and that it will help you when planning your next upgrade.

Until next post!

Mariano Gomez, MVP
Maximum Global Business, LLC