Penn Computing

University of Pennsylvania
Penn Computing << go backback
EMPLOYEE Table - Data Element Index   Tables and Data Elements   Salary Management Home   Data Warehouse Home

EMPLOYEE Table

Explanation
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.

Common Uses
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).

Primary Key Indexed Data Elements Related Tables
SSN

CAMPUS_MAIL_CODE
COUNTRY_OF_CITIZENSHIP
FIRST_NAME
HOME_DEPARTMENT
HOME_DEPT_ORG
HOME_SCHOOL
HOME_SCHOOL_CTR
LAST_NAME
MIDDLE_NAME
NAME
PENN_ID
PREVIOUS_SSN
PRI_ACAD_APPT_JOB_CLASS
PRI_ACAD_DEPT_ORG
PRI_ACAD_JOB_LINK_ID
PRI_ACAD_UNIV_POS_NO
PRIMARY_APPT_JOB_CLASS
PRIMARY_DEPARTMENT
PRIMARY_DEPT_ORG
PRIMARY_JOB_LINK_ID
PRIMARY_SCHOOL_CTR
PRIMARY_UNIV_POSITION_NUMBER
SSN
BUDGET_BASE (DWADMIN)
BUDGET_DISTRIBUTION (DWADMIN)
COMP_CHANGE_LOG (DWCOMP)
COMP_DISTRIBUTION (DWCOMP)
COMP_DISTRIBUTION_EXT (DWCOMP)
COMP_PERSON (DWCOMP)
COMP_PERSON_ADDRESS (DWCOMP)
COMP_PERSON_ROLE (DWCOMP)
COMP_PERSON_ROLE_JOINT_ORG (DWCOMP)
COMP_PERSON_SALARY (DWCOMP)
COMP_PERSON_SALARY_SUMMARY (DWCOMP)
COMP_VISA_LIST (DWCOMP)
COUNTRY_CODE (DWADMIN)
EMPLOYEE_ADDRESS (DWADMIN)
EMPLOYEE_DISTRIBUTION (DWADMIN)
EMPLOYEE_ENCUMBRANCE (DWADMIN)
EMPLOYEE_ENCUMBRANCE_CURRENT (DWADMIN)
EMPLOYEE_GENERAL (DWADMIN)
EMPLOYEE_JOB (DWADMIN)
EMPLOYEE_PAYMENT (DWADMIN)
EMPLOYEE_PAYMENT_SUMMARY (DWADMIN)
JOB_CLASS (DWADMIN)
JOB_CLASS_GENERAL (DWADMIN)
MAIL_CODE (DWADMIN)
PAR_EARNINGS (DWPAY)
PAR_MAIN (DWPAY)
PAYROLL_ACTION_CODES (DWPAY)
PAYROLL_CHANGE_LOG (DWADMIN)

Cautions

  • 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 table.
  • 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. Else,

    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 primary job.

    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 job. Else,

    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). Else,

    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 most). Else,

    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 precedence 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 appointment will be the job with the lowest Job_Sequence_Number. Finally, only jobs 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.
EMPLOYEE Table - Data Element Index   Tables and Data Elements   Salary Management Home   Data Warehouse Home

Questions about this page? Email us at da-staff@isc.upenn.edu

Information Systems and Computing
University of Pennsylvania
Information Systems and Computing, University of Pennsylvania