Greetings Sage Citizens.
It would appear no one wanted to talk about the walking dead season finale cliffhanger or any other series so we will go right into the technicals. The focus of today’s rant is going to be back end level and will be a discussion about how to easily pull totals for audits on the SQL side. The first thing we will need to understand is the schema of some of the more meaningful tables in HRMS and how they inter relate.
UPEMPL:. This the equivalent of the HRPERSNL table in HRMS. This is where you can find the employee name, social, hire date and other bits of information about the employee. The good news is that so much of the info that is in HRPERSNL is ported over to this table there really shouldn't be a need to also try to bring in HRPERSNL. (in the Canadian version the table name is CPEMPL).
EMPLOYEE- EMPLOYEE NUMBER
FIRSTNAME- EMPLOYEE FIRST NAME
LASTNAME- EMPLOYEE LAST NAME
FULLNAME- LAST, FIRST MIDDLE.
UPCHKH: This is the check header file. This is where you can find things like the check date, the check total, the bank account info and a couple other items of interest that may be relevant to the aggregates.
EMPLOYEE- employee number
PEREND- Period end date
ENTRYSEQ- This most closely ties to the idea of payroll batch number. Every time you run calc payroll and and move to print post, it uses a new entry seq for each group of payments processed.
TRANSDATE-This is the actual check date. the values is stored as YYYYMMDD
TRANSAMT-This is the total amount of net cash pay to employee.
(notes: EMPLOYEE, PEREND,ENTRYSEQ are all keys that need to be used to link UPCHKH and UPCHKD. Also once you added in UPCHKD to any query , creating any kind of aggregate against UPCHK info will likely be fruitless.).
UPCHKD. These are where the check details are stored. This gives you the complete detail of every single posti9ng, where it was charged on the GL the taxable amount, whether it was an earning a deduction or a tax and tons of other useful facts.
EMPLOYEE- same as above
PEREND-same as above
ENTRYSEQ-same as above
EARNDED-The code associated to the earning, deduction, tax, accrual or benefit.
HOURS- The hours attributed against the code in question (typically accrual or earning)
EEXETEND-The amount of the earning, deduction or tax.
REXTEND- The employer match for items that have a match calculated in the system.
TAXEARNS-The taxable wages for items that don't have a ceiling.
TXEARNCEIL-the taxable wages for items that do have a ceiling.
PCATEGORY-flag to determin if what your working with is an earning, deduction, tax, benefit, accrual. (E=1,D=2,T=3,A=4,B=5).
EXMAPLES OF USAGE.
Let us say that you are looking to create a summary of all earnings, deductions, taxes, accruals, benefits, for a date range of 01/01/2015 through 12/31/2015. you need to include the employee name. here is how the syntax would be structured.
select
fullname,
upempl.employee,
earnded,
pcategory,
Sum(eextend) as AMOUNT,
Sum(hours) as hours,
sum(taxearns) as Wages,
sum(txearnceil) as Cwages,
sum(rextend) as match
from
upempl,upchkh,upchkd
where
upempl.employee=upchkh.employee and
upchkh.employee=upchkd.employee and
upchkh.entryseq=upchkd.entryseq and
upchkd.perend=upchkd.perend and
transdate>=20150101 and
transdate<=20151231
group by
fullname,upempl.employee,earnded,pcategory
order by
upemple.employee,pcategory,earnded
with the above query you should get the following results. (note: these are NOT real people).
Here is the entire expression again but explanations of each of the logical color groupings.
select
fullname,upempl.employee,earnded,pcategory,
These are direct pull fields. if any of these fields exists in two or more tables you must qualify the field with a table name like I had to do with employee. also any field you pull that is NOT an aggregate expression must be used in the group by clause at the end.
Sum(eextend) as AMOUNT,Sum(hours) as hours,sum(taxearns) as Wages,sum(txearnceil) as Cwages,sum(rextend) as match
These are all aggregates. When you use them you must specify the aggregate type. The most common ones being sum, min, max, avg, count and count(distinct [field]). if you want your aggregate to assume a name other then expr_x you must add to the end of is AS [desired description]. fields being aggragate should not be in the group by clause.
from
upempl,upchkh,upchkd
any database used needs to be listed here and separated by commas. The order is not important.
where
upempl.employee=upchkh.employee and upchkh.employee=upchkd.employee and upchkh.entryseq=upchkd.entryseq and upchkd.perend=upchkd.perend and
the first half of the where/selection clause is to establish how these database linked to eachother. as each of the fields being used to establish the linkage exists in multiple tables, all them need to be qualified by the table name.
transdate>=20150101 and transdate<=20151231
this section of the select statement limits the records to ones associated to items with a check date in year 2015. additional and statements could now be used to limit it down even tighter to just earnings (pcategory=1) or just federal income tax (pcategory=3 and earnded='USFIT')
group by
fullname,upempl.employee,earnded,pcategory
as mentioned above, any field used in the select statement that is not being used as an aggregate must be specified here. once again, if the field exists in multiple tables you must also qualify the field with the table name like i have done here with upempl.employee.
order by
upemple.employee,pcategory,earnded
the order by clause can help turn this from being an employee audit to a code audit. if I changed the order by clause to pcategory,earnded,upempl.employee the results would look like this instead.
Keep in mind, this is a bit on the advanced side and is intended mostly for channel partners and advanced sql users as the ability to pull this data in this form directly out of sql can aid in auditing. There is no report built into the system that allows you to pull this out in this format so this is a handy thing to know how to do. One could even create a SQL view so this information could be pulled directly from a crystal report linked to this information.
if anyone has any questions on this let me know. Let’s start a discussion.
thanks for reading Sage Citizens.
