Contains most current available biographic/demographic data for each employee paid through the Penn Payroll system.
The table includes employees who had Payroll records as of July 1, 1996 or thereafter
(some of whom have been purged from Payroll). There is one row per employee.
Retrieving secured biographic or demographic data on employees (for example,
primary appointment salary, birth date, campus mail code, education level, emergency
contact information, sex, marital status).
||Indexed Data Elements
- The EMPLOYEE table contains the latest available biographic/demographic 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, matching biographic/demographic information appears in both DWCOMP.COMP_PERSON and DWADMIN.EMPLOYEE. (PennWorks data is fed to the mainframe Payroll system for Payroll processing. After that processing, the biographic/demographic data is fed from the Payroll system to the EMPLOYEE table.) Note: the EMPLOYEE table includes employees who had Payroll records on or after July 1, 1996. For employees with Payroll records prior to October 2009, biographic/demographic data is available in the EMPLOYEE table only.
- The presence of a record in the EMPLOYEE table does not imply the employee
is active. Selection of "active employees" should be based on other criteria
(for more information, see the documentation for the Employment_Status data
element). When employee records are purged from the Payroll system, they remain
the Warehouse in their most recent state.
- Keep in mind that this is the latest information about an employee - the
EMPLOYEE table does not keep history about an employee. Historical snapshots
of employee information are available in the EMPLOYEE_SNAP
- You can access an EMPLOYEE record only if you are authorized to access data for the employee's home school or organization. To access basic biographic/demographic data for all employees, use the unsecured EMPLOYEE_GENERAL table. Access to SSN is further restricted to specifically authorized individuals.
- The EMPLOYEE and EMPLOYEE_GENERAL tables store information about both an
employee's primary appointment and primary academic appointment
(if one exists). The primary appointment represents the University
census logic primary appointment for an individual, while the primary academic
appointment columns serve to highlight an individual's primary academic
job, and pay no attention to an individual's administrative appointments.
Both primary and primary academic information is updated nightly, although the primary academic update lags by a few hours. Primary academic information should not be considered up-to-date until the completion of the Payroll/HR Email update. The logic used to determine these appointments is as follows:
Primary Appointment: An employee's primary appointment is determined
according to the following logic. If:
1. The employee has only one job, that is the primary job. Else,
2. If the employee is a standing faculty member (Job_Class 216000-216010,
216020, 226000, 226010, 226020 and Tenure_Status NOT = 7), that is the
primary job. Else,
3. Review the employee's personnel class. If the class is SS (Support
Staff), US (Unionized Staff) or ST (Student), that is the primary job.
4. Determine if the employee is academic or administrative. Add up
the salary of all the employee's administrative jobs (Job_Classes that
begin with a number other than 2). Add up the salary of all the employee's
academic jobs (indicated by a Job_Class beginning with 2). If administrative
salaries are greater than academic salaries, the individual is administrative,
otherwise the individual is academic. Then,
5. If the person is academic, review the salaries of all the employee's
academic jobs. The job with the highest salary is the primary job. Or,
6. If the person is administrative, review the salaries of all the
employee's administrative jobs. The job with the highest salary is the
Note: For employees having a CPUP appointment (job class
315071 or 315072) or Dental Network Clinician appointment (job class 315078), that job will only appear as the employee's primary
appointment if it is the individual's only appointment. If the person
having a CPUP job has other non-CPUP appointments as well, one of those
will appear as the primary, as determined by the logic above. Similary, if the person
having a Dental Network Clinician job has one or more other appointments (of a different job class) as well, one of those
will appear as the primary, as determined by the logic above.
Finally, only appointments whose begin dates have already passed (where the appointment begin date is less than the system date at the time of the data load) are considered for the primary appointment. Future-dated appointments will not appear as the primary appointment, although they are still stored in the EMPLOYEE_JOB table. Appointments whose end date has passed can still appear as primary, however, in order to show what the last known primary appointment was for an individual.
Primary Academic Appointment: An employee's primary academic appointment
is determined according to the following logic. If:
1. The employee has an Emeritus Faculty job or is on Faculty Early Retirement (Faculty Class is 'EMF' or 'FER'),
take that job. Else,
2. If the employee has a Tenure Track job (Faculty Class in the Job
Class table is 'TEN'), take that job. Else,
3. If the employee has a Pre-Tenure Track job (Faculty Class is 'PRE'),
take that job. Else,
4. If the employee has a job as a Clinician Educator (Faculty Class
is 'CLE'), take that job. Else,
5. If the employee is is on the Research Faculty (Faculty Class is
'RES'), take that job. Else,
6. If the employee is a Practice Professor (Faculty Class is 'PRF),
take that job. Else,
7. If the employee is on the Clinical Faculty, Visiting Faculty, or
Wistar Faculty, or is a Visiting Executive Professor (Faculty Class
is 'ACN', 'CLN', 'VIS', 'WIS', 'VEP'), take that job. Else,
8. If the employee is on the Adjunct Faculty (Faculty Class is 'ADJ'),
take that job. Else,
9. If the employee is on the Support Faculty (Faculty Class is 'SUP'),
take that job. Else,
10. If the employee is a Post-Doc (Faculty Class is 'POS'), take that
11. If the employee is a graduate or professional student (Faculty
Class is 'GRP'), take that job (if more than one, take job class 217002
if possible; if the employee doesn't have that job, take 217004; if
the employee doesn't have that job, take the one that pays the most).
12. If the employee has an unclassified faculty job (Faculty Class
is 'UNC'), take that job (if more than one, take the one that pays the
13. If the employee has no academic jobs (i.e, no job classes with
an associated Faculty_Class), the primary academic appointment columns
will contain null values.
Note: At any step, if the employee has more than one job that meets
the criteria for the logic in that step, the primary academic appointment
will be the one with the highest salary (with the exception of the
of classes 217002 and 217004 in step 11, for GRP). Then, if there is
still more than one job with the same salary, the primary academic
will be the job with the lowest Job_Sequence_Number. Finally, only
with a current appointment are considered for in the primary academic
appointment logic -- those are jobs where the appointment begin date
precedes the extract date, and the appointment end date has not passed
at the time the extract is taken. Note that for terminated employees,
the appointment end date may be later than the termination date.
- 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 table can be found in the DWADMIN schema, as DWADMIN.EMPLOYEE_V.
Questions about this page? Email us at email@example.com