PeopleSoft Learning

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.