HCM/HRMS Query reporting
The following are the main tables used to query HR related data
Human Resources Information/ Personal Data:
PERSONAL_DATA, PERS_NID
Employment Data:
EMPLOYMENT- general employment data, such as various service dates, etc.
Department Inforamation
DEPT_TBL --This is one of foundation tables in HR. You will rarely query against it directly; more
likely you will join to it to get the description or short description. Department codes in PeopleSoft are
different from what was used in HRS/Legacy, and department data is effective dated.
Job code information
JOBCODE_TBL --This is also a foundation table. This table holds job codes, descriptions and related
data such as FLSA classification, pay plan, grade and structure information.
Job Data:
JOB-- Job (roughly equates to assignment records in legacy). Contains job records along with other
data. Effective dated someone's job history will be stored in this table. Remember, Empl Records
refer to an employee's job record(s). The existence of multiple Empl Records means that they have
had or are currently holding multiple jobs Empl Records start with 0, and their benefits are tied to
Empl Record #0.
Benefits Data :
BEN_PROG_PARTIC – Stores the Benefit Program to which the employee is assigned.
LEAVE_PLAN – Stores enrollment in leave plans.
HEALTH_BENEFIT – Stores e nrollment in health insurance plans
SAVINGS_PLAN -- Stores enrollment in TDA, ORP and Deferred Compensation
LIFE_ADD_BEN -- Stores enrollment in life insurance plans.
RTRMNT_PLAN – Stores TRS enrollment.
DISABILITY_BEN – Stores enrollment in disability plans.
Tax Data:
FED_TAX_DATA – Contains federal tax data, including nonresident alien data
STATE_TAX_DATA – Although there is no state withholding in Texas, this record is necessary to
maintain state of residence and unemployment jurisdiction.
Payroll Data:
Payroll processing check data (records used by the payroll process to store check history)
PAY_CALENDAR
PAY_CHECK
PAY_LINE
PAY_CHECK
PAY_EARNINGS
PAY_OTH_EARNS
PAY_DEDUCTION
PAY_TAX
Payroll funding distribution data (records that tie funding to payroll actuals):
PAY_ERN_DIST
PAY_DED_DIST
PAY_TAX_DIST
Other employee level payroll data:
GENL_DEDUCTION - Genearl deduction.
GENL_DED_CD - General deduction code.
ADDL_PAY_EFFDT - additional pay effdt.
ADDL_PAY_DATA - additional pay data.
ADDL_PAY_ERNCD - additional pay earn code.
DIRECT_DEPOSIT - Direct deposit.
DIR_DEP_DISTRIB - Direct deposit distribution.
Position Data:
POSITION_DATA—identifies position data for each business unit.
Tenure Data:
EG_TENURE Tenure Data.
Hints:-
Use the Employees table whenever possible. Queries using this table will generally have better
performance for several reasons. This tables has name, Empl ID, empl records, job title, job code,
dept title, dept code, location code, comp rate, comp frequency, position number, standard hours,
etc., data for active employees. Two fields that it does not have are Empl Class and FTE. This table
is not a transactional table, meaning that records are not actively written to it throughout the day.
Rather, it is refreshed nightly on process scheduler. There may be a one-day lag time for new data to
hit the table, but for most reporting this is acceptable
The following are the main tables used to query HR related data
Human Resources Information/ Personal Data:
PERSONAL_DATA, PERS_NID
Employment Data:
EMPLOYMENT- general employment data, such as various service dates, etc.
Department Inforamation
DEPT_TBL --This is one of foundation tables in HR. You will rarely query against it directly; more
likely you will join to it to get the description or short description. Department codes in PeopleSoft are
different from what was used in HRS/Legacy, and department data is effective dated.
Job code information
JOBCODE_TBL --This is also a foundation table. This table holds job codes, descriptions and related
data such as FLSA classification, pay plan, grade and structure information.
Job Data:
JOB-- Job (roughly equates to assignment records in legacy). Contains job records along with other
data. Effective dated someone's job history will be stored in this table. Remember, Empl Records
refer to an employee's job record(s). The existence of multiple Empl Records means that they have
had or are currently holding multiple jobs Empl Records start with 0, and their benefits are tied to
Empl Record #0.
Benefits Data :
BEN_PROG_PARTIC – Stores the Benefit Program to which the employee is assigned.
LEAVE_PLAN – Stores enrollment in leave plans.
HEALTH_BENEFIT – Stores e nrollment in health insurance plans
SAVINGS_PLAN -- Stores enrollment in TDA, ORP and Deferred Compensation
LIFE_ADD_BEN -- Stores enrollment in life insurance plans.
RTRMNT_PLAN – Stores TRS enrollment.
DISABILITY_BEN – Stores enrollment in disability plans.
Tax Data:
FED_TAX_DATA – Contains federal tax data, including nonresident alien data
STATE_TAX_DATA – Although there is no state withholding in Texas, this record is necessary to
maintain state of residence and unemployment jurisdiction.
Payroll Data:
Payroll processing check data (records used by the payroll process to store check history)
PAY_CALENDAR
PAY_CHECK
PAY_LINE
PAY_CHECK
PAY_EARNINGS
PAY_OTH_EARNS
PAY_DEDUCTION
PAY_TAX
Payroll funding distribution data (records that tie funding to payroll actuals):
PAY_ERN_DIST
PAY_DED_DIST
PAY_TAX_DIST
Other employee level payroll data:
GENL_DEDUCTION - Genearl deduction.
GENL_DED_CD - General deduction code.
ADDL_PAY_EFFDT - additional pay effdt.
ADDL_PAY_DATA - additional pay data.
ADDL_PAY_ERNCD - additional pay earn code.
DIRECT_DEPOSIT - Direct deposit.
DIR_DEP_DISTRIB - Direct deposit distribution.
Position Data:
POSITION_DATA—identifies position data for each business unit.
Tenure Data:
EG_TENURE Tenure Data.
Hints:-
Use the Employees table whenever possible. Queries using this table will generally have better
performance for several reasons. This tables has name, Empl ID, empl records, job title, job code,
dept title, dept code, location code, comp rate, comp frequency, position number, standard hours,
etc., data for active employees. Two fields that it does not have are Empl Class and FTE. This table
is not a transactional table, meaning that records are not actively written to it throughout the day.
Rather, it is refreshed nightly on process scheduler. There may be a one-day lag time for new data to
hit the table, but for most reporting this is acceptable
