Wednesday, July 14, 2010

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.


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.


Dylan West said...

"wow. That was just what I needed, and very detailed. You are a lifesaver!"

Unknown said...

The article is great only one issue.
Type of the account at least for GP 2013
(22 for checking, 32 for saving)

I found the hard way.