EMPLOYEE_JOB Table - Data Element Index   Tables and Data Elements   Salary Management Home   Data Warehouse Home

EMPLOYEE_JOB Table

Note: This table is no longer updated, following the retirement of the mainframe Personnel/Payroll system and the implementation Workday@Penn in July 2019.

Explanation
Contains data about employees and their jobs as maintained in the Penn Payroll System. There is one record per employee/job. Multiple versions of job records are included and are organized by job sequence number and extract date.

Common Uses
To determine what jobs an employee holds (or has held), the salary for each job, when the job appointment begins and ends, when the employee first began working in the job, the pay cycle, the percentage of full-time hours spent in the job, and the organization responsible for the job appointment.

HISTORY_STATUS

JOB_SEQUENCE_NUMBER

LAST_EXTRACT_DATE

SSN

APPT_BEGIN_FISCAL_YEAR

APPT_BEGIN_YEAR

APPT_END_FISCAL_YEAR

APPT_END_YEAR

HISTORY_STATUS

HOME_DEPT

HOME_DEPT_ORG

HOME_SCHOOL

HOME_SCHOOL_CTR

JOB_CLASS

JOB_DEPT

JOB_DEPT_ORG

JOB_LINK_ID

JOB_SCHOOL

JOB_SCHOOL_CTR

JOB_SEQUENCE_NUMBER

LAST_EXTRACT_DATE

PENN_ID

SSN

UNIV_POSITION_NUMBER

COMP_PERSON (DWCOMP)
COMP_PERSON_ROLE (DWCOMP)
EMPLOYEE (DWADMIN)
EMPLOYEE_ADDRESS (DWADMIN)
EMPLOYEE_DISTRIBUTION (DWADMIN)
EMPLOYEE_ENCUMBRANCE (DWADMIN)
EMPLOYEE_ENCUMBRANCE_CURRENT (DWADMIN)
EMPLOYEE_GENERAL (DWADMIN)
EMPLOYEE_PAYMENT (DWADMIN)
EMPLOYEE_PAYMENT_SUMMARY (DWADMIN)
JOB_CLASS (DWADMIN)
JOB_CLASS_GENERAL (DWADMIN)
ORG_CODES (DWADMIN)
PAR_MAIN (DWADMIN)
PAR_EARNINGS (DWADMIN)

Cautions

• The EMPLOYEE_JOB table contains job data for the employees paid via the Penn Payroll system. For individuals with Payroll records as of November 1, 2009, when PennWorks came into use, job-related information appears in both DWCOMP.COMP_PERSON_ROLE and DWADMIN.EMPLOYEE_JOB. (Roles from PennWorks are mapped to a maximum of four job records in the mainframe Payroll system, where they are used for Payroll processing. After that processing, the job data is fed from the Payroll system to the EMPLOYEE_JOB table.) Note: the EMPLOYEE_JOB table includes job history for employees who had Payroll records on or after July 1, 1996. For employees with Payroll records prior to October 2009, job data is available in the EMPLOYEE_JOB table only.

• An employee may hold up to four jobs at the same time. About two percent of the employees hold more than one job with the same Job_Class and the same Job_Dept_Org. Job_Sequence_Number is used to distinguish one job from another for the employee.

• At any point in time, the rows with a History_Status of "C" reproduce the jobs as they currently exist in Payroll (or as they existed most recently before the employee was purged from Payroll). The rows with a History_Status of "H" represent earlier versions of the same data. Additions and subtractions to the list of an employee's appointments can change the order of jobs. If investigating job history, take into account the History_Status_Date, the Last_Extract_Date, the Job_Dept_Org and Job_Class, as well as the Job_Sequence_Number. In most cases, look for rows with a History_Status of "C". Only when you need to trace jobs over time would you need to probe through the "history" rows. "Current" does not mean "currently active." You cannot query by this status column and get an accurate list of currently active jobs. For most queries, you will need to limit rows by Appt_Begin_Date and Appt_End_Date as well as History_Status. Note: If there is no definite end date for the employee's appointment to the job, Appt_End_Date will default to the latest available date in the Warehouse, which is 12/31/9999 (while the default for appointments with no end dates is actually stored in Payroll as 9999/99/99). Historical snapshots of job information are available in the EMPLOYEE_JOB_SNAP table.

• You can access an EMPLOYEE_JOB record only if you are authorized to access data for the employee's home school or organization, or the job organization. Access to SSN is further restricted to specifically authorized individuals.

• As a result of the University's Classification Redesign Project, a new job classification system for staff positions was developed in 1998. Job classes in the online Payroll system and the Data Warehouse were converted to this new system over April 8 - 9, 1998. Some classes were reused, however; only the attributes now used for those classes are available. Faculty classes were unaffected by this conversion.

• Use of 5-character Payroll department codes and 2-character Payroll school codes was phased out at the end of June 1998, when the Payroll system became fully compliant with the present General Ledger accounting structure. These columns should be null for all records from fiscal year 1999 onward; in these records, refer to Dept_Org and School_Ctr columns instead.

• The EMPLOYEE_JOB table can be found in the DWADMIN schema, as DWADMIN.EMPLOYEE_JOB_V

EMPLOYEE_JOB Table - Data Element Index   Tables and Data Elements   Salary Management Home   Data Warehouse Home