Tuesday, August 20, 2013

Importing Payroll Posting Accounts

Payroll posting accounts can be fairly straightforward, but for some users the combination of code/department/position can create a seemingly neverending matrix of possibilities- particularly when factoring in the use of ALL for some of the components. Although the payroll posting account setup window is not terribly difficult to use, it is a little unwieldy when trying to enter and proof a long list of variations. Table import can help with this, by allowing users to enter and review the information in Excel.

Now, I am not going to go in to detail about using table import, Tools-Import-Table Import, except to say that you should always test in a test database first. In the case of importing payroll posting accounts, I would go so far as to build (which should flush out any true errors), calculate, print, and post a payroll (which will flush out anything entered incorrectly).

So, to import payroll posting accounts, you will be importing to the Payroll Accounts Setup table (UPR40500). And you will need the following fields in your upload file (which can be in Excel, and then saved as a text tab-delimited or CSV file).

1. Department (Must match department code exactly in GP)
2. Job Title (Must match position code exactly in GP)

And then we have the three more complicated ones...

3. Payroll Code In a lot of cases, this would be the Pay Code, Deduction Code, or Benefit Code (based on the UPR transaction type which is explained next).

But in some cases, this would also be...
Tax Code (State)- When used with the transaction type, State Tax Withholding, SUTA Payable or FUTA Payable.
EFIC/M, EFIC/S, FED, FICA/M, FICA/S - When used with Federal Tax Withholding (the codes that start with E are the employer side)
FIC/ME, FIC/SE, FUTA, SUTA - When used with Employer Tax Expense.

So that brings us to..
4. UPR Transaction Type This has different values that correspond to the dropdown list in the Payroll Posting Accounts Setup window. You will use the number noted in your file.

Gross Pay (DR)- 1
Federal Tax Withholding (CR)- 2
State Tax Withholding (CR)- 3
Local Tax Withholding (CR)- 4
Deduction Withholding (CR)- 5
Employer's Tax Expense (DR)- 6
Benefits Expense (DR)- 7
Benefits Payable (CR)- 8
Taxable Benefits Expense (DR)- 9
Taxable Benefits Payable (CR)- 10
SUTA Payable (CR)- 11
FUTA Payable (DR)- 12
Workers Comp Tax Expense (DR)- 13
Workers Comp Tax Payable (CR)- 14

The last field in the file would be...

5. Account Index. This is easily retrieved from the GL00100 or GL00105 table, or you can just add Account Index to the Accounts Smartlist.

It is important to note this is NOT the account number, but the index that is a incremental unique number assigned to each account in the database. When in doubt, go set a few up manually and then do a select * from UPR40500 to check out the values and how they populate. This can go a long way to making your table import successful.

One additional side note, make sure you include all of the required payroll posting account combinations in your upload (refer to help from the Payroll Posting Accounts Setup window for a list of them).

Christina Phillips is a Microsoft Certified Trainer and Dynamics GP Certified Professional. She is a supervising consultant 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.

No comments: