Now that we have a function to determine the payroll posting account given the type of transaction, department, position, and payroll code, it’s time to put the function to good use (see the previous post for the actual SQL function).
One of the most significant ways this can be used is to determine what accounts will be hit when a payroll is run.
For example, you might look at a batch in payroll to see where the gross pay transactions will post to like this:
SELECT COMPTRNM,BACHNUMB,EMPLOYID,DEPRTMNT,JOBTITLE,UPRTRXCD,dbo.sockeye_PayrollAccount(1,DEPRTMNT,JOBTITLE,UPRTRXCD) AS Account FROM UPR10302 WHERE COMPTRTP = 1
Which would have results like this:
You could view the FICA Medicare expense account by using this:
SELECT COMPTRNM,BACHNUMB,EMPLOYID,DEPRTMNT,JOBTITLE,’FIC/ME’ AS UPRTRXCD,dbo.sockeye_PayrollAccount(6,DEPRTMNT,JOBTITLE,’FIC/ME’) AS Account FROM UPR10302 WHERE COMPTRTP = 1
Which would have results like this:
You could view possible combinations, although depending upon the number of combinations, this might not be advisable. Run this script first to see how many results you would get prior to running actual function.
SELECT
(SELECT COUNT(*) FROM UPR40600 WHERE INACTIVE = 0)
*(SELECT COUNT(*) FROM UPR40300)
*(SELECT COUNT(*) FROM UPR40301)
If that number is not too large, here’s the script that would provide all possible combinations for departments, positions, and pay codes:
SELECT department.DEPRTMNT,POSITION.JOBTITLE,paycode.PAYRCORD,dbo.sockeye_PayrollAccount(1,department.DEPRTMNT,POSITION.JOBTITLE,paycode.PAYRCORD) AS Account
FROM UPR40600 AS paycode CROSS JOIN UPR40300 AS department CROSS JOIN UPR40301 AS POSITION WHERE paycode.INACTIVE = 0
If an employee doesn’t change departments or positions within a pay period, you could view all the pay codes for all active employees by running this:
SELECT e.EMPLOYID,e.DEPRTMNT,e.JOBTITLE,pc.PAYRCORD,dbo.sockeye_PayrollAccount(1,e.DEPRTMNT,e.JOBTITLE,pc.PAYRCORD) AS Account
FROM UPR00100 AS e INNER JOIN UPR00400 AS pc ON e.EMPLOYID = pc.EMPLOYID WHERE pc.INACTIVE = 0
There are myriad uses for this function. Let us know how you use it.