Microsoft GP

Determining the Payroll Posting Account for GP Part I: How to determine the posting account

By Eric Ward

Years ago, I was first asked to build a report that would display the GL account that will be used when posting a given transaction in payroll.  For those that have been consulting on GP Payroll for some time, the question is a common one, and the answer is easy to find for a single record if you know how GP looks for the given account.  Creating a report with the account number given a department, position and code can be a more daunting task.  I’ll break this down in a three-part blog.  Part I will cover the logic on how the account is selected, Part II will discuss the SQL query to accomplish the reporting aspect, and Part III will provide examples of this in action.

My client wanted a report that would allow them to pass in the employees’ departments, positions and pay codes and would return all the account that would be hit (it is helpful to identify the pesky ALL-ALL-ALL transactions before they happen).

This is a good example where “easy to explain” did not necessarily translate to “easy to script in SQL”

First, let me explain the logic. Take the following setup as an example:

Microsoft GP Payroll Posting Accounts Set Up

The payroll posting accounts work from left to right, narrowing down the selection until there is only one option left.  In the event that there are on rows to match, the logic is to go back a column and replace the code with “ALL”.

Examples

For example, an employee that has the following transaction:

Department: ACCT  |  Position: ATC  |  Code: HOLI

Start with the leftmost column (Department) and narrow down the options:

Microsoft GP Accounts Filtering

Then look at the position to narrow it further.

Microsoft GP Accounts Filtered

Since there is only one option and it fits, (the Code is either a match or is “ALL”) that account will be selected.

Let’s look at a few more examples.

Department: ACCT  |  Position: APA  |  Code: HOUR

First Filter the Department.

Microsoft GP Filter by Department

Then the Position.

Microsoft GP position Filtering

Then the Code.

Microsoft GP Code Filtering

As long as we have records after our filter is applied, this is pretty easy to follow. Let’s look at an example where that is not the case. 

Department: INST  |  Position: ATC  |  Code: HOUR

After applying our department filter we are left with

Microsoft GP Accounts Filtering

The one row we have doesn’t apply to our transaction details so we need to go back a step and use the “ALL” value for our department.

Microsoft GP Accounts Filtering Position

After applying the position filter we would be left with a single record that would meet the criteria of the transaction.

Microsoft GP Accounts Filtering code hour

Stay tuned for Part II that will discuss how to do this in SQL!

Leave a Reply

Your email address will not be published. Required fields are marked *