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) until the retirement of PennWorks and the mainframe Payroll/Personnel system in June 2019. Following the implementation of Workday at Penn in July 2019, select columns in the EMPLOYEE table will be updated with Workday data, to support longitudinal and cross collection reporting. Refer to the data element index for details on mapping.

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

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. Following the implementation of Workday at Penn in July 2019, select columns in the EMPLOYEE table will be updated with Workday data, to support longitudinal and cross collection reporting. For individuals with Payroll records as of November 1, 2009, when PennWorks came into use, until the PennWorks retirement in June 2019, 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 EMPLOYEE table also contains records about Academic Affiliates in Workday. Since these individuals are not workers, several columns in their records (including Home Dept ORG, Employment Status, and Primary Appointment information) may be null. Refer to DWHCM.HCM_PERSON_V for additonal information about these individuals.
  
  
• 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). Once a person has been terminated in Workday and is therefore no longer occupying a position, elements related to the primary appointment will likely be null - this includes the Home Dept ORG, which is updated based on the Primary Penn Cost Center in the HCM_PERSON_V view. When the PRI_PENN_COST_CENTER is null, you will no longer be able to see those elements with ORG-based security (e.g., birth date, sex, etc.). You may need to consult EMPLOYEE_SNAP for information about that person when she or he was active.

• 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. With the implementation of Workday, the indication of whether a position is the primary one is sourced in Workday and loaded to the Data Warehouse. In cases where an indvividual holds more than one position, and/or more than one academic appointment, the primary of each is based on the designation in Workday, and not any additonal logic (unlike the legacy systems, where primary appointment were calculated upon load to the Warehouse).
  • For faculty workers, appointment records are generally, but not always, related to a position (available in HCM_POSITION). In cases where the the Primary Academic Appointment is not related to a position, the Primary Position_ID and Primary Job_Profile-related values will be null.
  • The following logic was used prior to the implementation of Workday, and displayed here for documentation purposes:
    • 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. Similarly, 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