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:

Then look at the position to narrow it further.

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

1. First Filter the Department.

2. Then the Position.

3. Then the Code.

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:

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.

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