Depositing Direct Deposit Information Directly

How's that for a tongue twister? When working on small-scale ( less then 250 employees) payroll implementations, manual entry is not necessarily a deal-breaker. And in some cases, it can be a good thing in terms of cleaning up and reviewing data. However, when working on larger implementations including one I am finishing up with over 1500 employees between three companies, manually entry is not possibly in many cases.

So, okay, fine. We use integration manager to import a lot of data including employee maintenance info, deductions, benefits, and pay codes. But what about direct deposit information? This is critical and subject to entry mistakes, so even if keying is possible...I don't know that I want to risk an incorrect account number or routing number. So, I use table import.

DISCLAIMER :) Always thoroughly test table imports in a test environment first, and also always make a backup prior to performing a table import.

Generally, I am not a fan of table imports because they can be prone to issues if you aren't completely confident of the table and field requirements. But this is one I have done repeatedly and with great success. Thousands of records can be loaded in seconds, and you can avoid carpal tunnel from the manually entry :)

Table import is located under Microsoft Dynamics GP>>Tools>>Integrate>>Table Import. And it should be noted that the import definitions are stored locally in the SY50o00.dat, SY50000.idx, SY50100.dat, and SY50100.idx files stored in \Program Files\Microsoft Dynamics\GP\Data. So if you plan to use the table import from more than one machine, you will need to copy these files from the machine used to create the import. Also, make sure you back up these files after creating the import in case they are accidentally overwritten during a new installation or are lost when a workstation is replaced.

To import to Cards>>Payroll>>Direct Deposit, you must create two table imports. One for the header and one for the detail record.

The destination table for the header record is Direct Deposit Employee Master. And the upload file should contain one record per employee with the Employee ID. The file can then be mapped to the header fields as follows:

• Employee ID= Employee ID (from file)
• Inactive= 0 (Constant)
• DD Remainder=1 (Constant, always will use first account for remainder of DD. Can be supplied in file instead if different settings are needed.)
• Index=0 (Constant)

Now for the detail records, the destination table is the Direct Deposit Employee Accounts Master. The upload file should contain the employee ID, index (incremental per employee, so if the employee has two accounts then the first one would be labeled 1 for the index and the second one would be 2 and so on), percentage (make sure this is formatted as a number like 10 for 10%), amount (only one of these fields should be completed for each line- percentage or amount), account number, routing number, type (23 for checking, 33 for savings). The file can then be mapped as follows for the import:

• Employee ID=Employee ID (from file)
• Index=Index (from file)
• Inactive= 0 (Constant)
• Deduction Method= 0 (Constant)
• Deduction= -none-/blank (Constant)
• DD Prenote= 1 (Constant)
• DD Prenote Count= 1 (Constant)
• DD Percent= Percentage (from file)
• DD AmountDir=Amount (from file)
• DD Account Number= Account Number (from file)
• DD Transit Number= Routing Number (from file)
• DD Transaction Code= Type (from file)

After importng, I run check links (Microsoft Dynamics GP>>Maintenance>>Check Links>>select Payroll) on the Payroll Direct Deposit Master. The records will have a status of Prenote (Cards>>Payroll>>Employee) and when you are ready to make all records active you can run the following script against the company database in SQL Server Management Studio.

UPDATE DD00200 SET DDPRE=0

This will update all direct deposit records to an Active status.

Hope some of you find this helpful, and as always, please share your comments, questions, and experiences.

Ouch, Modifying Direct Deposit Files

I have definitely had my fair share of ouch moments over the generation of ACH files in Dynamics GP. If a payroll is processed with incorrect information like a mistyped routing number or account number, or if a direct deposit is voided after the payroll is posted, the ACH file that is generated contains incorrect information.

The party line is to void the entire payroll and then re-run with corrected information. Although this is fine for small companies, it doesn't work all that well for large companies with large payrolls.

So, we used to edit the ACH file manually. This worked great, although it was always a little hit and miss to make sure you caught all of the fields that needed to be updated. However, in recent years this is no longer an option due the encoding created at the time the ACH file is created in GP (referred to as "hash" entries). These hash entries will vary based on the information in the file, so there is no reasonable way to update the file manually and still have it accepted by the bank.

So what to do? Well, if the client has an understanding bank, or the bank has a full-featured website, often you can upload the file to the bank and either make the changes in their website or the bank will make the changes for you. But what to do if the bank's website does not have that capability, or the bank itself is not helpful?

Well, in those cases, I have found you can use the following script to update the Direct Deposit records in GP and then regenerate the file (which will recreate the correct hash entries as well). Now, I must say, I have anecdotal experience that this works. But it may not work in all instances, so you need to test it with the bank and also make sure you have a functional backup before doing this!

These scripts assume that the payroll build is still available in the Generate ACH window (Transactions>>Payroll>>Generate ACH). The script should be run against the company database.

UPDATE DD10500 SET DDAMTDLR=’1500.00000’ WHERE DEX_ROW_ID=’insert dex row ID of record you need to change’

The field in the script above (DDAMTDLR) will change the dollar amount, DDTRANUM would update the routing number, and DDACTNUM will update the account number. In all cases, once you make the change, you can regenerate the file and the footer information (totals, hash info, etc) will be correct. To view the contents in the table, use SELECT * FROM DD10500 and you can find the correct dex_row_ID of the record you want to update. You will notice that the records in the table are identified with the build number from the Generate ACH window (field INDEX1 in the table).

Since I sort of stumbled on this through trial and error under pressure, please share any insights you have or other fixes or pitfalls you have found.

Table Import is not always the ugly stepsister

I shy away from table import whenever possible. I prefer integration manager, or using SQL directly, or eConnect if I can. But sometimes I am reminded of how simple things can be done simply.

For the past few weeks, I have been working on an implementation that goes live Monday 12/28. We have had a fairly tight timeframe, so I have been trying to avoid manual data entry whenever possible. However, I have run up against more than a few things that I needed to import but could not do so with Integration Manager. Table import to the rescue with imports in to some simple tables like...

• Direct Deposit Header and Lines for Payroll
• Shipping Methods
• Customer Items

All of these table structures are fairly friendly to table import, and result in simple file uploads. Shipping methods even came up late in the game, and it took less than 10 minutes to build and load the file.

So why am I writing about this? Well, I know I could use the reminder that older tools that I regularly dismiss can actually come in quite handy :)

Please share any of your table import success stories, and I will be happy to update the post to include.

A few other uses courtesy of Steve Chapman and Frank Harnelly:

• High volume imports even if the destinations are available in Integration Manager, like Customers (1,000,000+ records in minutes) or Chart of Accounts
• Fixed Assets General Info