The next question is how do we create a SQL Script that will return the GL account that will be used given a payroll transaction. I will first answer this while specifically using the gross wages in payroll. The Payroll Posting accounts can be found in the UPR40500 table in the SQL database (join the GL00100 or GL00105 to get the actual account number). Keep in mind that what the front end of GP calls position is actually Job Title in the database.
The script below will return the payroll posting account for the gross wages.
SELECT ppa.DEPRTMNT,ppa.JOBTITLE,ppa.PAYROLCD,ppa.UPRACTYP,ai.ACTNUMST,a.ACTDESCR
FROM UPR40500 AS ppa INNER JOIN GL00100 AS a ON ppa.ACTINDX = a.ACTINDX INNER JOIN GL00105 AS ai ON a.ACTINDX = ai.ACTINDX
WHERE UPRACTYP = 1
As usual, there are many ways to design a script that will return the account number given a department, job title, and payroll code. In instances where there are multiple ways to accomplish a task, I agree with a quote largely attributed to Albert Einstein “Everything should be made as simple as possible, but not simpler.” I have come at this specific script from many different angles over the years. The current iteration of my function is vastly simpler than anything else I have come up with.
This SQL script will work for any of the payroll posting account (not just gross wages) and takes the type, department, position and payroll code as parameters and returns the account string. The logic of the script is to pull in all the possible accounts that could get used (including the “ALL” selections), sort the results by the best match, and return the top result using powers of 2 to score the matches of department, position, and payroll code. If a match is not found, the function will return NULL
The script is below:
CREATE FUNCTION sockeye_PayrollAccount( @TYPE AS INT,
@Department AS VARCHAR(6),
@POSITION AS VARCHAR(6),
@Code AS VARCHAR(6))
/********************************************************************************/
/* Created by: Eric Ward of Sockeye Business Solutions */
/* Purpose: To return the Payroll posting account that will be used */
/* */
/* */
/* */
/* */
/********************************************************************************/
RETURNS VARCHAR(66)
AS
BEGIN
/*
variables used for testing
set @type = 1
set @Department = ‘ADMIN’
set @Position = ‘CNST’
set @Code = ‘HR’
*/
DECLARE @Account AS VARCHAR(66)
SET @Account =
(SELECT top 1 ACTNUMST
FROM UPR40500 AS p INNER JOIN GL00105 AS a ON
p.ACTINDX = a.ACTINDX
WHERE UPRACTYP = @TYPE AND
(DEPRTMNT = @Department OR DEPRTMNT = ‘ALL’) AND
(JOBTITLE = @POSITION OR JOBTITLE = ‘ALL’) AND
(PAYROLCD = @Code OR PAYROLCD = ‘ALL’)
ORDER BY
CASE DEPRTMNT WHEN @Department THEN 4 ELSE 0 END +
CASE JOBTITLE WHEN @POSITION THEN 2 ELSE 0 END +
CASE PAYROLCD WHEN @Code THEN 1 ELSE 0 END DESC)
RETURN(@Account)
Stay tuned for Part III that will discuss how to use this function!