Friday, September 23, 2016

Year End Is Coming! I know, I know...

So this week has been a whirlwind, I started the week in Fargo at Reimagine and then headed over the Indianapolis for the inaugural BKD Women's Empowerment Conference.  I have to say that it was one of the most inspiring and energizing weeks I have had in a long time.  Starting out at Reimagine, it is always fun to reconnect with old friends and share in all of the great things we are doing collectively to move clients forward with their software.  And then at #BKDWomenLead, I got to spend 2 days with the women of our firm learning how to build momentum towards our own goals.  All in all a great week, and I am reminded how fun my job is!


And then I get the email from Terry Heley, reminding me that year end is indeed coming.  I always feel like September rounds the corner in to the downward slide that is year end.  So here is to being prepared!  So here are a few "heads up" tips as you look ahead:


  • Remember! Only supported versions for year end updates (1099s, W-2s, Tax Changes, etc) are GP 2013, GP2015, and GP2016.  That's it.  If you are on GP 2010 or earlier, you need to get upgraded now.
  • As we learned at Reimagine earlier this week, GP 2016 R2 will drop in the 4th quarter.  So the year end update for clients already on GP 2016 will move them to GP 2016 R2 (so the update and year end handled in one pass)

One of the most well-received pieces of news out of Reimagine earlier this week was the move back to an annual release cadence (instead of the more recent 6  month cadence).  So that means after GP 2016 R2 we won't see GP2017 until late in the new year.

Start your engines, the fun times are a'coming!


Christina Phillips is a Microsoft Certified Trainer and Dynamics GP Certified Professional. She is a director with BKD Technologies, providing training, support, and project management services to new and existing Microsoft Dynamics customers. This blog represents her views only, not those of her employer.



Wednesday, September 21, 2016

Dynamics GP GPConnNet.dll is not compatible with SQL Server high availability setup or DNS aliases

By Steve Endow

A client recently upgraded to Dynamics GP 2016.  As part of their GP upgrade, the client setup new SQL Servers in a dual-server high availability (HA) cluster.  The ODBC DSN is setup to use a DNS name that points to the SQL Server cluster.  The ODBC DSN looks something like this:


The name "sqlcluster.company.lan" is not a physical machine.  It's a DNS entry that points to an IP address for the SQL cluster.  The SQL cluster decides which of two SQL instances / machines should receive the requests.

Using these DSN settings, you can setup GP just fine.  When you create users based on this DSN and fully qualified server name, GP works great and users can login.

But this configuration can cause problems with applications or utilities that are not designed to work with the SQL Server HA setup or DNS aliases.  One such incompatible utility is the GPConnNet.dll library.

GPConnNet is used by .NET developers who create Dynamics GP AddIns that require a connection to the GP SQL Server.  It allows the developer to request a connection to SQL without knowing the server, username or password.  You simply ask GPConnNet to provide you with an active connection to the SQL Server.

Well, in an environment using SQL Server HA, GPConnNet is unable to connect to the SQL Server instance.  Rather than looking at the ODBC DSN server setting for the GP client, GPConnNet uses some other technique to determine the SQL Server instance name.  As a result, it gets the physical SQL instance name, which in a SQL HA environment, is different than the fully qualified cluster name.


This dialog shows that GPConnNet returned an error number of 131074, which indicates that the connection was successful, but that the login failed.  You can see that the Data Source value ends in "DB01", which is the name of one of the physical SQL instances, and not the cluster DNS name.

As a result, when GPConnNet tries to connect to the physical SQL instance, the login fails.  Because the Data Source name is used, the user password is encrypted differently, and an incorrect password is then sent to the SQL Server.

GPConnNet could likely be modified to fix this issue, but I'm pretty sure that isn't going to happen.

There might be a way to configure SQL Server HA to work around this issue with GPConnNet, but I'm skeptical about that, and even if there was, it would require this customer to completely re-implement their HA setup and GP configuration.

I offered the customer the option of customizing my software to use a configuration file to store SQL credentials to work around this issue.  But the client informed me that they have encountered several other issues with the HA setup, and as a result of this additional issue, they are abandoning SQL HA for their GP environment.  They will be switching over to a standard single SQL Server machine and will be eliminating the use of a DSN entry to point to the SQL cluster.

So while SQL HA for Dynamics GP might be a sensible goal, be aware that other software may not work properly in such an environment.


You can also find him on Google+ and Twitter








Tuesday, September 20, 2016

Bug in eConnect taPMManualCheck for Credit Card and Cash Payables Manual Payments

By Steve Endow

I'm working on an eConnect integration that imports Payables Manual Payments.  The integration itself is working fine, but after adding support for the Credit Card payment method, I noticed an oddity.

If I manually enter a Payables Manual Payment for a Credit Card Payment Method, it looks like this.


Notice the Amount fields in the lower right corner.  The payment amount is for $111.11, and that amount is displayed in both the Unapplied and Total fields on the left.

However, this is what an eConnect imported Credit Card payment looks like.


While testing, I noticed that the Total field is zero for the imported Credit Card payment.

If I query the PM10400 records for both of the Credit Card payment transactions, I see that the CHEKAMNT field is zero for the eConnect imported payment, and has a value for the payment entered manually in GP.


The eConnect taPMManualCheck node only has one amount field, which is DOCAMNT, so I don't think I'm missing an amount field value in my submission to eConnect.

After reviewing the SQL for the taPMManualCheck stored procedure, I think I found the bug. To verify my suspicion, I imported a new payment with Payment Method of Cash.  Sure enough, the imported Cash payment has the same issue: the Total field is zero.


And the record in PM10400 has the same issue--the CHEKAMNT field is zero.


Looking at the Insert statement that is being called by the taPMManualCheck stored procedure, you can see that it is inserting the value using a parameter called @CHEKAMNT.


The fact that the field is called CHEKAMNT and the parameter is called @CHEKAMNT should be a clue as to the genesis of the problem.

It would seem that the Payables Manual Payment Entry was originally designed to only record payables "checks", and was subsequently modified to support Credit Card, Cash, and more recently EFT.

eConnect was updated to support Credit Card and Cash (it does not support EFT), but when updating the taPMManualCheck procedure for those two new payment methods, this "Total" amount / CHEKAMNT bug was introduced.

For some reason, two new parameters were added to the stored procedure to differentiate the Cash Amount and Credit Card Amount values.


But although these parameters are set based on the payment type, only the @CHEKAMNT value is used for the insert to PM10400.  So it looks like the developer forgot to make sure that the appropriate amount parameter was used for the actual Insert.

So how do we work around this issue?

One easy approach is to use the taPMManualCheckPost procedure to fix the problem.  This script in the Post proc appears to work well based on a quick test.



However, if you have ever used the eConnect Pre or Post procedures, you likely know that the big downside is that they typically get wiped out when a GP service pack is installed or GP is upgraded.  Nearly everyone (including myself) forgets that they have the custom Pre / Post scripts, and it takes a few weeks after an update or upgrade before they realize they have bad data in GP due to the missing Pre / Post proc.  So for that reason, I am not a huge fan of the Pre and Post scripts, despite their convenience.

So for my application, instead of using the Post script, I'm going to add the SQL update to my .NET code, so that my application performs the update of the CHEKAMNT field after the Manual Payment is successfully imported.  That way the customer doesn't need to install a custom stored procedure and remember to update it after any GP upgrades.

And with that long winded explanation of another eConnect bug, may all of your future integrations bring happiness and joy to your Dynamics GP users.



You can also find him on Google+ and Twitter









Limited Users? Light Users? Self Service Users? SMH?


I had a bit of a whirlwind trip up to Fargo this week for Reimagine 2016.  It was a quick trip, mostly due to client commitments and our inaugural BKD Women’s Empowerment Summit later this week in Indy.  But while I was up at the mothership, I had the privilege of presenting a session on delineating the functionality  of Limited Users and Self Services in the Perpetual Licensing Model.  There definitely seemed to be a fair amount of hesitation in the room, in terms of everyone feeling confident about the licensing types (Beyond the full license) and accompanying functionality. 

I realize that this seems like a sales topic, but in my completely anecdotal experience—the majority of opportunities to sell Limited and Self Service licenses come up during or after implementation (not during the original sales cycle).  So consultants should be aware of these differences.  And clients should understand too, so they can avoid over-buying full licenses (which is very common) when a Limited or Self Service user would be more appropriate (and cost less).

So here are a few high points…

First, let’s throw out old terminology.  Light users, Business Portal Users, Employee Users, HRM Self Service Users—all old terms from Business Ready and Module Based Licensing. The three terms we have today in Perpetual Licensing – Full, Limited, Self Service.  And you want to think of them in that order…

Full is what we are used to, full is input and output and access to all of the functionality in GP.   This is concurrent licensing in a traditional (non-subscription) licensing environment.

Limited is a step down, focused primarily on output only.  Inquiries, SmartLists, and Reports.  This license type can also do limited input via the self service functionality (see self service user below, the limited user license includes this functionality).  This is also a concurrent user license (a point of confusion for many) in a traditional licensing environment.

Self Service is the most limited of the types, with access only granted to the input/output functions of the self service areas: Payroll/HR (Benefits, Direct Deposit, Skills and Training, Employee Profile, W4 Withholding, Benefit Self Service), Project Time and Expense, and Purchase Requisitions.   These licenses are NAMED licenses.

Limited and Self Service users are set up in the same windows as a full user in Dynamics GP.  And Dynamics GP comes with predefined security roles for each (Limited roles and ESS roles for Employee Self Service).  For more info on the security roles, check out this KB article and blog post from Pam M.


http://community.dynamics.com/gp/b/gpteamblog/archive/2014/12/17/what-are-the-self-serve-and-limited-users

Pricing flows accordingly, with full users being the most and self service being the least.

For transitions from prior licensing models, things flow pretty obviously with a few exceptions.  Most BP licensing (employee  users, HRM self service, etc) will flow to self service  users.  And the previously named Light user flows to Limited user (with the exception of customers who bought Light User- Limited Upgrade and Light User- Starter Pack licenses under prior licensing models).


Christina Phillips is a Microsoft Certified Trainer and Dynamics GP Certified Professional. She is a director with BKD Technologies, providing training, support, and project management services to new and existing Microsoft Dynamics customers. This blog represents her views only, not those of her employer.

Friday, September 9, 2016

Another One To Share: Reconciling Payroll Summary

A quick share to end the day, if you can successfully reconcile your 941 manually by taking gross wages and subtracting tax sheltered deductions and adding taxable benefits, but your payroll summary does not match...this script is for you!


This will display the wages as calculated for the payroll summary, and then also display (based on the transaction history) the tax sheltered deductions (divided in to two categories, sheltered from all taxes or just fed and state) and taxable benefits (also divided in to two categories, taxable by all taxes or just fed and state).  This will allow you to manually calculate (by employee) what you would expect the federal and fica wages to be.  If the manual calculation differs from the amounts display for the payroll summary, then you know you need to make an adjustment to correct.  Most often these discrepancies can be traced to tax flags being changed after transactions were processed.


Happy hunting!


--Summarizes deductions by employee for a period of time that are sheltered from all taxes
With CTE_DedTax as (Select employid, SUM(UPRTRXAM) as TotalDedTaxSheltered from UPR30300 where CHEKDate<='6/30/2016' and chekdate>='4/1/2016'
and payrolcd in ('POPULATE WITH LIST OF DEDUCTIONS SHELTERED FROM ALL TAXES')
and PYRLRTYP=2 group by EMPLOYID),


--Summarizes deductions by employee for a period of time that are sheltered just from federal and state
CTE_DedFS as (Select employid, SUM(UPRTRXAM) as TotalDedFedStateSheltered from UPR30300 where CHEKDate<='6/30/2016' and chekdate>='4/1/2016'
and payrolcd in ('POPULATE WITH LIST OF DEDUCTIONS SHELTERED FROM JUST FEDERAL AND STATE')
and PYRLRTYP=2 group by EMPLOYID),


--Summarizes benefits by employee for a period of time that are taxable by all taxes
CTE_BenTax as (Select employid, SUM(UPRTRXAM) as TotalBenTaxSheltered from UPR30300 where CHEKDate<='6/30/2016' and chekdate>='4/1/2016'
and payrolcd in ('POPULATE WITH LIST OF BENEFITS TAXABLE BY ALL TAXES')
and PYRLRTYP=3 group by EMPLOYID),


--Summarizes benefits by employee for a period of time that are taxable by just federal and state
CTE_BenFS as (Select employid, SUM(UPRTRXAM) as TotalBenFedStateSheltered from UPR30300 where CHEKDate<='6/30/2016' and chekdate>='4/1/2016'
and payrolcd in ('POPULATE WITH LIST OF BENEFITS TAXABLE BY JUST FEDERAL AND STATE')
and PYRLRTYP=3 group by EMPLOYID),


--Summarizes wage information by employee as reported on the payroll summary report
CTE_PRSummary as (Select sum(fdwgpyrn) as FedWages,
sum(ficasswp) as FICASSWages, sum(ficamwgp) as FICAMWages, sum(GRWGPRN) as GrossWages, Employid as EmployID
from UPR30100 where year1='2016' and CHEKDate<='6/30/2016' and chekdate>='4/1/2016' group by EMPLOYID)


--Pulls all information together for comparison
select CTE_PRSummary.Employid, CTE_PRSummary.FedWages, CTE_PRSummary.FICASSWages, CTE_PRSummary.FICAMWages,
CTE_PRSummary.GrossWages, isnull(CTE_DedTax.TotalDedTaxSheltered,0) as TotalDedTax, isnull(CTE_DedFS.TotalDedFedStateSheltered,0) as TotalDedFS,
isnull(CTE_BenTax.TotalBenTaxSheltered,0) as TotalBenTax, isnull(CTE_BenFS.TotalBenFedStateSheltered,0) as TotalBenFS from CTE_PRSummary
left outer join CTE_DedTax
on CTE_PRSummary.EmployID=CTE_DedTax.EmployID
left outer join CTE_DedFS on CTE_PRSummary.EmployID=CTE_DedFs.EmployID left outer join
CTE_BenTax on CTE_PRSummary.EmployID=CTE_BenTAX.EmployID left outer join CTE_BenFS on CTE_PRSummary.EmployID=CTE_Benfs.EMPLOYID


Christina Phillips is a Microsoft Certified Trainer and Dynamics GP Certified Professional. She is a director with BKD Technologies, providing training, support, and project management services to new and existing Microsoft Dynamics customers. This blog represents her views only, not those of her employer.

Some Things Never Change: Reconciling Payroll

Building on my post from a few weeks ago.  A few more scripts. For 15 years now, I have been doing this.  Doing the GP thing.  And for 15 years, I have been reconciling payroll taxes.  My job and career has changed tremendously in 15 years, but just when I start to think I might never have to sit down with a 941, Payroll Summary, and a bunch of Check Registers again....


So I thought I would share a few scripts I use in the course of reconciling.  These are my quick check Captain Obvious sorts of things.  These are usually most beneficial during the first year of a go live, when setup and settings might have been corrected or adjusted in the early days of the go live.


Hope these are helpful!




-Compare deduction tax flags by employee (Cards-Payroll-Deduction) to the deduction tax flags in setup (Setup-Payroll-Deduction)
-Display list of exceptions when these settings differ
-This may identify potential reconciliation issues by highlighting items that may individually impact wages differently than expected



select UPR00500.EMPLOYID as EmployeeID,

UPR00500.DEDUCTON as Deduction, 

UPR00500.SFRFEDTX as ShelteredFed,

UPR00500.SHFRFICA as ShelteredFICA,

UPR00500.SHFRSTTX as ShelteredState

from UPR00500 inner join UPR40900 on UPR00500.DEDUCTON=UPR40900.DEDUCTON

WHERE (UPR00500.SFRFEDTX<>UPR40900.SFRFEDTX)

or (UPR00500.SHFRFICA<>UPR40900.SHFRFICA)

or (UPR00500.SHFRSTTX<>UPR40900.SHFRSTTX)


-Same logic as the deduction script above, only for benefits

select UPR00600.EMPLOYID as EmployeeID,
 UPR00600.BENEFIT as Benefit, 
 UPR00600.SBJTFDTX as SubjectFed,
 UPR00600.SBJTFDTX as SubjectFed, 
 UPR00600.SBJTSSEC as SubjectFICA, 
 UPR00600.SBJTMCAR as SubjectMed,
 UPR00600.SBJTSTTX as SubjectState 
 from UPR00600 inner join UPR40800 on UPR00600.BENEFIT=UPR40800.BENEFIT
 WHERE (UPR00600.SBJTFDTX<>UPR40800.SBJTFDTX)
 or (UPR00600.SBJTSSEC<>UPR40800.SBJTSSEC)
 or (UPR00600.SBJTMCAR<>UPR40800.SBJTMCAR)
 or (UPR00600.SBJTSTTX<>UPR40800.SBJTSTTX)




Christina Phillips is a Microsoft Certified Trainer and Dynamics GP Certified Professional. She is a director with BKD Technologies, providing training, support, and project management services to new and existing Microsoft Dynamics customers. This blog represents her views only, not those of her employer.


Thursday, September 8, 2016

Updating a .NET development environment for a new version of Dynamics GP

By Steve Endow

I have developed many .NET AddIns for Dynamics GP.  They are great because they are relatively simple to develop, very simple to deploy, and they are also easy to update for new versions of Dynamics GP.

However, the big catch is preparing my development environment for a new version of GP.

I've been working on updating one of my development virtual machines to GP 2016 for the last TWO DAYS.  It hasn't been two full days of work, but it has been 15 minutes of starting an install, then waiting 30+ minutes for installs to complete.  I'll go work on something else, then come back whenever I remember and work on the next item.

In this particular case, I have a virtual machine dedicated to Project Accounting development.  PA is a unique beast, so I keep a separate VM just for PA related projects.  The dedicated VM makes it easier for me to deal with PA related config and development on this one machine, but it also means that I have to maintain and update this VM separately.

So to upgrade my PA customization to GP 2016 on this machine, I have to:

1. Install a new instance of SQL Server
2. Install GP 2016
3. Create new GP 2016 test company
4. Get PA configured in GP 2016 the way I need it
5. Install Visual Studio 2015 Update 1 from an ISO (since I only had VS 2013), which takes forever
6. Install Visual Studio 2015 Update 3 separately (6GB file!?!?!), which also takes forever
7. Create a new Git branch for the GP 2016 version of my customization
8. Finally work on updating my customization to GP 2016
9. Test the new version on GP 2016
10. Prepare a new release for GP 2016


I started this process on Tuesday.  I'm on Step 6, and it's now Thursday afternoon.

Actually updating the Visual Studio solution to work with a new version of GP is usually simple and relatively quick, but all of the other tasks to prepare for that code upgrade can be very, very time consuming.

May the Patience Be With You.



You can also find him on Google+ and Twitter