Bug in Dynamics GP 2013 eConnect Employee Deduction import: taCreateEmployeeDeduction

By Steve Endow

Several years ago I developed a Payroll integration for a customer that transitioned from Dynamics GP HR to Kronos HR.  The client wanted to use Kronos for HR, but keep GP for Payroll.  This resulted in a rather complex integration that required Kronos to send over any type of employee, benefit, and deduction change so that GP payroll could have accurate information.

The integration has been in place for years and running fine.  Recently, the client had to make a change to some of their deductions to modify the Tax Shelter settings.

They had to check the box to shelter the deductions from FICA Medicare.  This FICA Medicare tax shelter option was added in GP 2013, and if you query the UPR40900 and UPR00500 tables, you'll see the new SHFRFICAMED field tacked on near the end of the tables.

After my customer modified their deductions to check the FICA Medicare sheltered check box, they noticed that imported employee deductions did not have the box checked.

If they manually entered the employee deduction in GP, the FICA Medical sheltered option would be checked, as expected.

So this seemed to be a bug in eConnect, and I had to start digging to find it.  I have found a few other eConnect bugs while developing this very complex payroll integration, so it didn't surprise me at all that there might be one more.

The eConnect stored procedure that handles the insert and update of employee deductions is taCreateEmployeeDeduction, so that was the likely culprit.

As far as eConnect procedures go, taCreateEmployeeDeduction is quite simple.  It has validation for the input parameters, and then it performs an insert or update on the UPR00500 table.

I reviewed the stored procedure to see how it handled the SHFRFICAMED field.  There were so few references to the field that I didn't see anything that looked incorrect.  All of the SQL for the SHFRFICAMED seemed to match the other tax sheltered options.  I couldn't see any invalid logic or problems with the script that would lead to the FICA Medicare option to not be set properly in UPR00500.

I tried to debug the stored procedure directly, but it would be a hassle to get it setup for debugging, so I took the low tech approach: debug logging.

I created a logging table called cstbDeductionTrace to record the values of several of the stored procedure parameters.  I then added this line at several points in the stored procedure.  This let me capture the procedure parameter values at various points in the proc to see when the SHFRFICAMED parameter got its incorrect value.

INSERT INTO cstbDeductionTrace SELECT '1 Before Pre', @I_vEMPLOYID, @I_vDEDUCTON, @I_vSFRFEDTX, @I_vSHFRFICA, @I_vSHFRSTTX, @I_vSFRLCLTX, @I_vSHFRFICAMED;

I then ran my import and saw this in the trace table.  And I was puzzled.

Notice that the first four tax sheltered parameter values start out as NULL.  Only after the procedure pulls default values do they get set to 1, which makes sense.

But the SHFRFICAMED parameter starts out with a value of 0 rather than NULL.  And because the stored procedure is designed to only load default values for NULL parameters, the value never gets set properly--it stays at 0.

Huh.

So...why was the parameter being passed in with value of zero rather than NULL?

I wondered whether the eConnect serialization might be sending an invalid value.  I checked the XML I was sending to eConnect, and it didn't have a SHFRFICAMED node.

I then checked the eConnect documentation...and guess what.  The documentation doesn't even list the SHFRFICAMED field as a parameter.  And the eConnect object doesn't have a FICA Medicare property available in Visual Studio.  It looks like Microsoft forgot to add it to eConnect 2013 entirely.  So serialization didn't seem to be the culprit.

I then stared at the stored procedure SQL again.  And then it dawned on me.

If serialization wasn't adding the values for the tax sheltered fields in the XML, then it also wasn't passing values to the stored procedure.  Which means that the parameter values in the stored procedure were coming from...the parameter default values!

This seems a little obvious in hindsight, but when you are tracing and reverse engineering someone else's code, many things are far from obvious.

So I jumped to the top of the stored procedure to check the parameter default values...and sure enough, there was the culprit.

SHFRFICAMED was being defaulted to 0.  But what were the other tax sheltered parameters being defaulted to?

As I guessed, and as my trace data indicated, the other parameters were being defaulted to NULL.  Someone had typed the wrong default value for the new FICA Medicare parameter.  Bad Laama!

So why does a NULL vs. 0 value matter?  Well, the stored procedure is designed to only pull default values from the master deduction if the parameter is NULL.

@I_vSHFRFICAMED = CASE WHEN @I_vSHFRFICAMED IS NULL

                        THEN SHFRFICAMED

                        ELSE @I_vSHFRFICAMED

Since a 0 was being defaulted, the proc code thought that the value was being passed in, and didn't change it or override it.

Changing the parameter default value from 0 to NULL fixed the issue and resolved the issues with my deduction import.

I looked at the taCreateEmployeeDeduction on GP 2015 RTM and it has the same issue--an incorrect default value for the @I_vSHFRFICAMED parameter.  So the bug still exists there.

I checked the help file for GP 2015 eConnect, and guess what--the title page still says "eConnect 2013 R2", and also doesn't list the SHFRFICAMED field, so it seems that eConnect for GP 2015 has not added the FICA Medicare tax sheltered field.  Fun times ahead.

I'll be submitting this to Microsoft to see if they are aware of the issue, but I don't expect a fix for GP 2013, and I'm not holding my breath for a fix for GP 2015.  Fortunately it's fairly easy to correct the stored procedure, but it's a hassle to have to remember to update the proc after service packs and upgrades, as those can replace eConnect procedures.

Steve Endow is a Microsoft MVP for Dynamics GP and a Dynamics GP Certified IT Professional in Los Angeles.  He is the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.

You can also find him on Google+ and Twitter

http://www.precipioservices.com