Understanding Payroll Tables

If you are a former Abra Suite user you have probably found that writing Crystal Reports using HR data is not all that much different in Sage HRMS.  The transition is so easy that most reports will simply convert by choosing a new data source.  Payroll, on the other hand, is a completely different story.  Table and file names are completely different.  For example:

Table:  UPEMPL

This is the payroll version of the HRPERSNL table in HRMS.  This is where you find information like the employee name, SSN, hire date and other demographic information.  For the most part, if you are writing a payroll report you will start with this file rather than HRPERSNL.  Here are some of the important fields:

  • EMPLOYEE – Employee Number
  • FIRSTNAME – Employee First Name
  • LASTNAME – Employee Last Name
  • FULLNAME – Employee name in Last, First Middle format

Table:  UPCHKH

This is the check header file.  This is where you find information that relates to the entire check like the check date, the net pay amount, bank account information, etc.  Here are some of the important fields:

  • EMPLOYEE – Employee Number
  • PEREND – Period End Date
  • ENTRYSEQ – This is similar to a payroll batch number.  If you process more than one payroll on the same day they will have different values in the ENTRYSEQ field even though they may have all the same dates.
  • TRANSDATE – This is the check date, stored as either YYYMMDD or MM/DD/YYYY depending on your version of Sage HRMS.
  • TRANSAMT – This is the net cash pay amount

NOTE:  EMPLOYEE, PEREND, and ENTRSEQ are all keys that need to be used to link the UPCHKH (Check Header) and UPCHKD (Check Detail) tables.  Also, once you have three tables rather than two tables in the report you will need to be very careful about trying to create totals using  fields from the Header.  It will be easier to create totals using the Detail fields.

 

Table:  UPCHKD

This is where the check details are stored.  This table gives you the complete detail of every single posting; whether it was an earning, deduction, or tax;  and where it was charged for General Ledger.  For tax records or percentage deductions it will also show the wagebase.  Here are some of the important fields:

  • Employee – Employee Number
  • PEREND – Period End Date
  • ENTRYSEQ – Entry sequence (payroll batch number)
  • EARNDED – The code for the earning, deduction, tax, accrual, or benefit.
  • HOURS – The hours associated with an earning or accrual record.
  • EEXTEND – The dollar amount of the earning, deduction, or tax.
  • REXTEND – The employer match for items such as 401(k) deductions or the employee portion of benefits.
  • TAXEARNS – The taxable wages for items that don’t have a ceiling.
  • TAXEARNCEIL – The taxable wages for items that do have a ceiling.
  • PCATEGORY – Flag to determine if the record is an earning, deduction, tax, benefit, or accrual.
    • 1 = Earning
    • 2 = Deduction
    • 3 = Tax
    • 4 = Accrual
    • 5 = Benefit