• Home
  • /
  • Blog
  • /
  • Determining the Payroll Posting Account for GP Part I: How to determine the posting account

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:

Payroll Posting Account for GP Setup

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:

Determining the Payroll Posting Account for GP Part

Then look at the position to narrow it further.

Determining the Payroll Posting Account for GP Part

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.

Determining the Payroll Posting Account for GP Part

2. Then the Position.

Determining the Payroll Posting Account for GP Part

3. Then the Code.

Determining the Payroll Posting Account for GP Part

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:

Determining the Payroll Posting Account for GP Part

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.

Determining the Payroll Posting Account for GP Part

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

Determining the Payroll Posting Account for GP Part

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


You might also like