Rose Business Solutions Dynamics GP

Archive for January, 2011

We are happy to announce that the always working Microsoft Dynamics Team has released the GP 9.0 Round 2 for U.S. Payroll Hotfix.

Please take note that the issues found on the reports after the release of the GP 10 US Payroll hotfix were fixed in this version and for GP 2010 which will be released hopefully in a few hours from this post.

 

Also Microsoft team has informed that there will be another release for GP 10 that will address a fix for the reports found on the earlier release, also will include the release of the Canadian Payroll (round 2) and some changes for the new T4A functionality as well some additional CSST functionality/change.

 

The round 2 2011 tax update (table) is already on the automatic server for all versions.  This is the last tax update supported for 9.0 and 9.0 SBF.

The Microsoft team has announced that they hope to release the 941 form change in March once IRS has the final form published.

 

I have to tell you that there is a slight change on the download process for this hotfix here are the Steps to Download Microsoft Dynamics GP 9.0 US Payroll Round 2 Tax Update

1. Click on the link below:

Microsoft Dynamics GP 9.0 US Payroll Round 2 Tax Update KB 2477913 – ** Once you install your Dynamics version is 9.0.388**

http://support.microsoft.com/hotfix/KBHotfix.aspx?kbnum=2477913&kbln=en-us

2. Under Step #1, click to select the checkbox under the "Select hotfix" area.

3. Under Step #2, enter a valid e-mail address and confirm the e-mail address in the "Provide us with your e-mail" area. An e-mail will be sent to

this e-mail address with the link to download. Type the characters visible in the Picture box.

4. Click Request Hotfix.

5. The following message will appear:  "Hotfix Confirmation. We will send these hotfixes to the following e-mail address:"

6. Go to your e-mail and find the e-mail from hotfix@microsoft.com with the subject "Hotfix download link you requested".  Towards the bottom of the e-mail, find the section titled "Package". Click the link from the e-mail to download the file. Save the file to a location on your machine.

7. Once downloaded, double-click the .EXE file and click Continue. It’s a 102MB file.

8. Enter a location to a folder to extract the contents of the file. Click OK.

9. Enter the password from the e-mail you received.  Click OK.

10. Double-click the .MSP file to install the US Payroll Round 2 Tax Update

 

Soon it will be available on the following link, but at the time I was writing this post it was not there: https://mbs.microsoft.com/customersource/downloads/taxupdates/usgptu9.htm?printpage=false

Issues list from 10 site- all fixed on 9 and GP 2010
Check Register
When you have Direct Deposit employees the Direct Deposit Check Register will still show the incorrect amount for Employer FICA Owed.  You will need to print the Reprint Check Register (Reports | Payroll | Reprint Journals) in order to show the correct Employer FICA Owed amount.

941 changes
941 Preparation Report will be correct through 6e.  The rest will be understated.
941 Schedule B Preparation Report will be understated.  Report is taking Employee total times 2 instead of taking Employer amount.
Form 941 Schedule B will be understated.  Report is taking Employee total times 2 instead of taking Employer amount.
Federal Tax Liabilities List 941 amount will be understated. 
Form 941 change – we will release update when form changes are finalized by the IRS.

 

Thank you Theley

I often get asked about which version of SQL server to use on a particular Dynamics GP  installation, sometimes I have seen odd configurations work with GP but the fact is that it’s not supported by Microsoft.

So this post goal is to show you the compatibility between SQL server versions and Dynamics GP that are supported.

 

Sources:

System Requirements for Dynamics GP 2010: GP 2010 Requirements

System Requirements for Dynamics GP 10: GP 10 Requirements

 

See original post in spanish by Bertrand Desmarest: http://blogs.msdn.com/b/dynamics_latam/archive/2010/10/22/sql-server-dynamics-gp-compatibilidad-entre-versiones.aspx

Hello, yesterday I was working a bit late when I received the call from my coworker Doug P. and he says “Francisco I have an error while upgrading GP, do you want to take a look?”.

Immediately I replied “Of Course!!” as I like to investigate things down, after connecting to his computer he was receiving errors due to VBA code attached to reports my assessment was to delete all .vba files of 4kb, take a backup of the reports.dic dictionary (since he before the update, exported into a .package all customizations) and we moved out of GP code folder the .vba files remaining.

 

He was able now to proceed with the installation but before we could celebrate there was an error preventing us from login to gp:

“An available update may be required for your computer, but the update process couldn’t verify information”

Once we got the error message I asked “Do you know if Automatic Client Updates were used?”

Even after receiving the answer “No” I queried on the Dynamics..SYUPDATE table and there was a record in there, so I deleted the record and problem solved!!!.

A customer called and said “My FRx is giving me the error: Out of Memory” , the first thing that came to my mind was “*.g32 and *.ldb files issue” but this time the answer was pretty easy to fix.

I took notes on the location of FRx “C:\FRx Software\FRx67\” and I asked the customer to try and replicate the issue, he went to GP > Reports > Financial > FRx and the error appeared.

I went to GP code folder and opened FRXDYN.ini file, when the contents were displayed he said “Ohhhh its redirected to the Old drive” so what I did was to delete the file, then into GP, I went the same path and GP asked me where the FRx exe file was located, now this time I selected the correct installation on his C drive and Application worked!!

 

So next time you receive this error try to replicate how this problem occurs if the error happens when the FRx is launched from its Icon then there are other steps to consider like re-registering dll’s etc.

 

Have a great day!!

Another day at the office managing support calls was a very good day until my colleague Doug Pitcher tells me that there was a customer issue and if I wanted to look at the problem.

What happened was that in most companies for this customer whenever they tried to Schedule a Stock Count, they started filling all the details, but when they clicked on the “Mass Add” button, the form appeared they actually selected the from and to fields and after they clicked ok to accept the parameters nothing appeared to work, they were returned to the main Stock Count Schedule window with no records to count.

Before they worked on this even they created a support case, the assessment from the engineer at Microsoft was right there was a bug in GP 10 SP4 and was fixed in SP5 so the answer was “Update to SP5 and the issue will be resolved”.

We did what support plan was and after coordinating a downtime with the customer we proceeded to the upgrade, but to our surprise it was having the same behavior after the service pack.

My task was to try and find a solution to this, I started monitoring with DEXSQL.Log and

everything appeared to be normal but no records were returned, so I went and review the tables involved and discover that 2 of them had invalid records. The invalid records had the column Itemnmbr left blank, so I wrote a script to be run for that company and fix those invalid records:

Delete IV10301 where ITEMNMBR =’’

Delete IV00101 where ITEMNMBR =’’

Delete IV00102 where ITEMNMBR =’’

After this script  I noticed that a few records were deleted from the DB (I did a select first to evaluate records, before I jumped into deleting data) we went to the Stock Count Schedule window, we did the steps and voilá!!!! now everything was working as expected.

Hope it helps!!

Hello, recently I was working with a customer who upgraded from GP 9 to GP 2010, all the process went without any errors, utilities ran smoothly, but after installing Service Pack 1 the customer was presented with the following error:

Error while upgrading Dynamics GP Utilities, company failed upgrade

Error on Upgrade CREATE VIEW GL10000CurrencyTranslationView AS select [GL10000Final].[OPENYEAR], [GL10000Final].[ACTINDX], [GL10000Final].[CRDTAMNT], [GL10000Final].[DEBITAMT], [GL10000Final].[ORCRDAMT], [GL10000Final].[ORDBTAMT], [GL10000Final].[TRXDATE], [GL10000Final].[DSCRIPTN], [GL10000Final].[REFRENCE], [GL10000Final].[CURNCYID], [GL10000Final].[Original_Exchange_Rate], [GL10000Final].[JRNENTRY], [GL10000Final].[TRXSORCE], [GL10000Final].[SOURCDOC], [GL10000Final].[ORDOCNUM], [GL10000Final].[ORTRXSRC], [GL10000Final].[ORMSTRID], [GL10000Final].[ORMSTRNM], [GL10000Final].[ORTRXTYP], [GL10000Final].[SERIES], [GL10000Final].[VOIDED], [GL10000Final].[Ledger_ID], [GL10000Final].[TranslationCurrencyID], [GL10000Final].[CurrencyTranslationType], [GL10000Final].[PERDENDT], [GL10000Final].[TranslationExchangeRate], case GL10000Final.CRDTAMNT when 0.0 then 0.0 else dbo.mcFuncCalculateAmountExtended([GL10000Final].[RTCLCMTD], 3, [GL10000Final].[TranslationExchangeRate], [GL10000Final].[DENXRATE], [GL10000Final].[MCTRXSTT], [GL10000Final].[DECPLCUR], [GL10000Final].[CRDTAMNT]) end as TranslationCreditAmount, case GL10000Final.DEBITAMT when 0.0 then 0.0 else dbo.mcFuncCalculateAmountExtended([GL10000Final].[RTCLCMTD], 3, [GL10000Final].[TranslationExchangeRate], [GL10000Final].[DENXRATE], [GL10000Final].[MCTRXSTT], [GL10000Final].[DECPLCUR], [GL10000Final].[DEBITAMT]) end as TranslationDebitAmount, [GL10000Final].[SequenceNumber], [GL10000Final].[PERIODID], [GL10000Final].[CURRNIDX], [GL10000Final].[DECPLCUR], [GL10000Final].[RATETPID], [GL10000Final].[EXGTBLID], [GL10000Final].[EXCHDATE], [GL10000Final].[TIME1], [GL10000Final].[RTCLCMTD], [GL10000Final].[DENXRATE], [GL10000Final].[MCTRXSTT], [GL10000Final].[Adjustment_Transaction] from (select distinct [GL_TRX_WORK].[OPENYEAR], [GL_TRX_WORK].[ACTINDX], [GL_TRX_WORK].[CRDTAMNT], [GL_TRX_WORK].[DEBITAMT], [GL_TRX_WORK].[ORCRDAMT], [GL_TRX_WORK].[ORDBTAMT], [GL_TRX_WORK].[TRXDATE], [GL_TRX_WORK].[DSCRIPTN], [GL_TRX_WORK].[REFRENCE], [GL_TRX_WORK].[CURNCYID], [GL_TRX_WORK].[XCHGRATE] as Original_Exchange_Rate, [GL_TRX_WORK].[JRNENTRY], [GL_TRX_WORK].[TRXSORCE], [GL_TRX_WORK].[SOURCDOC], [GL_TRX_WORK].[ORDOCNUM], [GL_TRX_WORK].[ORTRXSRC], [GL_TRX_WORK].[ORMSTRID], [GL_TRX_WORK].[ORMSTRNM], [GL_TRX_WORK].[ORTRXTYP], [GL_TRX_WORK].[SERIES], [GL_TRX_WORK].[VOIDED], [GL_TRX_WORK].[Ledger_ID], [GL_TRX_WORK].[TranslationCurrencyID], [GL_TRX_WORK].[CurrencyTranslationType], [GL_TRX_WORK].[PERDENDT], F.XCHGRATE as TranslationExchangeRate, [GL_TRX_WORK].[SQNCLINE] as SequenceNumber, [GL_TRX_WORK].[PERIODID], E.[CURRNIDX], (E.[DECPLCUR]-1) as DECPLCUR, [GL_TRX_WORK].[RATETPID], [GL_TRX_WORK].[EXGTBLID], F.[EXCHDATE], F.[TIME1], D.[RTCLCMTD], dbo.mcFuncGetDenExchRate(GL_TRX_WORK.TranslationCurrencyID,D.RTCLCMTD) as DENXRATE, [GL_TRX_WORK].[MCTRXSTT], [GL_TRX_WORK].[Adjustment_Transaction] from DYNAMICS..MC40200 E, DYNAMICS..MC40300 D cross apply (select e.YEAR1 as OPENYEAR,a.JRNENTRY,a.SOURCDOC,a.REFRENCE,f.DSCRIPTN,a.TRXDATE, a.TRXSORCE,f.ACTINDX,a.SERIES,f.ORTRXTYP,f.ORMSTRID,f.ORMSTRNM, f.ORDOCNUM,a.ORTRXSRC,a.SQNCLINE,a.CURNCYID,b.CURNCYID as TranslationCurrencyID, a.CURRNIDX,a.RATETPID,b.ExchangeTableID as EXGTBLID,a.XCHGRATE, a.EXCHDATE,a.TIME1,a.RTCLCMTD,dbo.glFuncGetPeriodID(a.TRXDATE,a.OPENYEAR,2) as PERIODID,f.CRDTAMNT,f.DEBITAMT,f.ORCRDAMT,f.ORDBTAMT, e.PERDENDT, dbo.mcFuncGetMCTrxState(b.CURNCYID) as MCTRXSTT,b.CurrencyTranslationType, a.VOIDED,a.Ledger_ID, a.Adjustment_Transaction, case b.CurrencyTranslationType when 1 then e.PERDENDT when 3 then a.TRXDATE end as ExchangeRateDate from GL10000 a, GL10001 f, (select c.ACTINDX,b.CURNCYID, ExchangeTableID= case CurrencyTranslationType when 1 then b.AverageExchangeTableID when 3 then b.HistoricalExchgTableID end, c.CurrencyTranslationType from MC00200 c,MC40600 b where c.CURNCYID=” and c.CurrencyTranslationType<>2) b, (select distinct b.PERIODID, a.YEAR1,a.FSTFSCDY,a.LSTFSCDY, b.PERIODDT,b.PERDENDT from SY40101 a, SY40100 b where a.YEAR1=b.YEAR1 and b.SERIES=2) e where a.JRNENTRY=f.JRNENTRY and f.ACTINDX=b.ACTINDX and a.PERIODID=e.PERIODID and e.YEAR1=e.YEAR1 and a.TRXDATE >=e.FSTFSCDY and a.TRXDATE <=e.LSTFSCDY) GL_TRX_WORK cross apply dbo.mcFuncGetExchangeRateTable(GL_TRX_WORK.ExchangeRateDate

ERROR [Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid object name ‘MC40600′

We started our research on the default locations such as PartnerSource, Knowledgebase, communities, blogs but we were unable to find any information on it. So our task was to tackle this problem and our internal research gave us the following results:

We verified that the company database contained the referred tables MC40600, GL20001, GL30001 and MC50500, as well the table MC00200 was missing a column (CurrencyTranslationType).

In order for us to fix this, we extracted the SQL resources using a setting in DEX.ini then we searched for one of the tables structure within all the extracted files and found them on tables.cmp under the product ID 5567 HITB, after recreating the tables using SQL Server management studio we were able to continue our upgrade, not to mention that we unlocked the company in Dynamics..duLCK.

I hope this experience helps the community.

 

See this post in spanish on: http://blogs.msdn.com/b/dynamics_latam/archive/2011/01/19/error-en-actualizaci-243-n-de-gp-2010.aspx

In today’s post I want to share a SQL script that I considered very important, lately I worked in  cases  were  I  have  been  witness  on  how  other  partners/customers  deal in SQL to remove stranded users. 

A few do this task from inside GP, others run the following code: 

Delete from Activity where userid = 'XXXX"

But  lets  analyze,  they  are  not  really  removing  a  stranded  user  and  all  of  the  activity associated  with  the  user, what  they are really doing is just removing the record from the activity table so that Dynamics GP does not see the user, but what happens if they remove via  SQL  or GP the wrong user id? if the user is logged into 2 or more different companies at the same time?. 

Messages like “The selected document is being edited by another user” sounds familiar? 

Let  me  explain  to  you how this SQL script works and then I’ll provide it to you remember with this script you don’t need the users to log off from Dynamics GP. 

SQL server maintains a record of active user sessions in the table SYSPROCESSES from the  MASTER  database, inside sysprocesses there is a column named “login name” and we  base  our  script  on  sysprocesses  to  clear  the  ACTIVITY  table from the DYNAMICS database. 

Once ACTIVITY table has  been  cleaned out we are ready to clean 2 tables from the TEMP database  first  we  clean  DEX_SESSION and then DEX_LOCK in order to eliminate locks and processes in temp tables. 

The next step is to clean batch activity (SY00800) and resource activity (SY00801) in order to have a valid session clean up. 

Here is the code: 

delete from DYNAMICS..ACTIVITY

  where USERID not in (select loginame from master..sysprocesses)

delete from tempdb..DEX_SESSION

  where session_id not in (select SQLSESID from DYNAMICS..ACTIVITY)

delete from tempdb..DEX_LOCK

  where session_id not in (select SQLSESID from DYNAMICS..ACTIVITY)

delete from DYNAMICS..SY00800

  where USERID not in (select USERID from DYNAMICS..ACTIVITY)

delete from DYNAMICS..SY00801

  where USERID not in (select USERID from DYNAMICS..ACTIVITY)

Note: Make  sure  you  have  a  backup  of  your  databases  when  running  scripts  that can modify your data. 

Also  I am including the KB’s published that show how to remove records from DEX_LOCK and DEX_SESSION that working together make the script I just provided. 

A batch is held in the Posting, Receiving, Busy, Marked, Locked, or Edited status in Microsoft Dynamics GP (KB 850289)

How to remove all the inactive sessions from the DEX_LOCK table in the TempDB database when you use Microsoft Dynamics GP together with Microsoft SQL Server (KB 864411)  

How to remove all the inactive sessions from the DEX_SESSION table in the TempDB database when you use Microsoft Dynamics GP together with Microsoft SQL Server (KB 864413)

Here’s an easy way to track actual costs for a specific item on a sales order in Dynamics GP. If you have a standard service or item you sell, and the actual cost to deliver the service or item can fluctuate from sale, to sale, you can record the actual cost as a component of a kit item.

I recorded this video to show this in action.

Here’s a screen shot of the kit item I used:

Here’s a standard sales report that shows the results of using this procedure for several invoices:

If you have a situation in which you need to capture actual costs for specific sales, try this procedure out.

You should be aware by now that employees will have FICA Social Security withheld at a 4.2% rate in 2011 rather than the 6.2% rate used in 2010.  However, the employer still has to pay FICA Social Security at the 6.2% rate.  Under current Great Plains tax code calculations, this will result in incorrect amounts indicated on the Check Register for Employer Owed FICA ( see screen shots below) until the Service Pack/Tax Code update is released by Microsoft (currently scheduled for mid January 2011).   Any payroll run in Great Plains with a 2011 payroll check/direct deposit date run prior to the mid January Service Pack/Tax Code update must make corrections for the federal taxes submitted to the IRS and also make corrections to the general ledger posting journals from payroll.  You should also assume that the quarterly 941 and the year end employer tax reconciliation may also require adjustments for this issue. 

The screen shots below are taken from Fabrikam for one employee.  The tax schedule used is dated 12/22/2010 (Round 1 2011)  which includes the 4.2% withholding for Employee FICA Social Security.  The amount withheld from Pilar Ackerman is $52.96 ($39.37 + $13.59).   The Employer Owed FICA on the Check Register is also listed as $52.96.  The correct Employer Owed FICA is actually $71.72 ($58.13 + $13.59).  Using the Employer Owed FICA from the Check Register will result in under payment of FICA taxes due by the employer of $18.76 ($71.72 – $52.96).    The general ledger posting journals for payroll will also be incorrect and musts be adjusted as well. 

Do not install the Round 1 2011 tax table update in your environment until all payrolls have been prepared with payroll check dates in 2010 and your Year End Wage File has been created.  I can install the Round 1 tax table update in my environment as I am not preparing any actual payrolls.  When the Service Pack/Tax Code update is released in mid January, Microsoft will undoubtedly have additional updates and/or information available at that time.

It is critically important that the individuals responsible for transmitting taxes due to the IRS understand and are familiar with this issue.  Please share this information with those individuals in your organization that are responsible for payroll.   Microsoft will be updating their website with pertinent information, and I would recommend that all interested persons check with Customer Source at the Microsoft website.

CRM 2011 is loaded with new functionality, and there is a connector to Dynamics GP with basic touch points to connect the two systems out-of-the-box.  If you’re even the least bit interested in CRM, you should check out the launch event: