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.
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.
||Indexed Data Elements
- 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
- 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
Questions about this page? Email us at email@example.com