Penn Computing

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

EMPLOYEE Table - Data Element Index

Select a data element to view its definition and its indexed, format, and null values.



<
Data element Definition
ACADEMIC_BASE_SALARY

Indexed - no
Format - number (9,2)
May be null? yes
The total amount that the University guarantees to pay a faculty member for basic services for the Pay_Term of the primary appointment where the primary appointment is a faculty job.

In general an Academic_Base_Salary is appropriate only for those individuals holding an academic primary appointment. In the PennWorks system, the academic base salary and the salary distributions are recorded on separate screens; the academic base salary is not linked to a particular job. When querying someone's Academic_Base_Salary, it is a good idea to confirm that the primary appointment job class for that person is a faculty job.

Academic_Base_Salary and other Faculty Compensation components are entered into PennWorks and fed nightly from there to the DWCOMP.COMP_PERSON_SALARY table, and then on to the EMPLOYEE table.


Values:

0-9,999,999.99

AFFILIATION_CODE

Indexed - no
Format - char (3)
May be null? yes

For employees of the School of Medicine, the 3-character code that identifies the primary work location of the employee. Affiliation_Code is invalid for other employees.

Example: A scientist is an adjunct professor at the University of Pennsylvania but works primarily at Merck, Sharp and Dohme. Therefore, M12 is the Affiliation_Code.

Values:
Refer to the COMP_AFFILIATION_LIST table for values.
BARG_UNIT_ELIG

Indexed - no
Format - varchar2 (1)
May be null? yes
Indicates whether employee’s position is represented by a bargaining unit (union).
Values:
U         Union
null      Non-union
BENEFIT_BASE

Indexed - no
Format - number (7)
May be null? yes

Benefits Base Salary is the amount used to calculate an employee’s Basic & Supplemental Life Insurance. Benefits Base includes the salary paid to the employee for the primary appointment at the University, including pre-tax retirement, health care and spending account contributions but excluding any bonus or overtime payments. If the employee is a faculty member with a full-time administrative appointment, Benefits Base means the annual salary paid for the primary appointment, including pre-tax contributions plus the salary for any administrative appointments. Benefits Base is determined prior to open enrollment (typically in March) and remains in effect for the entire upcoming plan year. It does not take into account pay received for services performed for CPUP (the Clinical Practices of the University of Pennsylvania).

BIRTH_DATE

Indexed - no
Format - date (7)
May be null? yes
The birth date of the employee.
CAMPUS_MAIL_CODE

Indexed - yes
Format - char (4)
May be null? yes

The 4-digit intramural mail code associated with the location where the employee receives intramural mail. This is the University mail code. Note: This is not necessarily the same as the U.S. Postal Service zip-plus-four extension.

Values:

Refer to the DWADMIN.MAIL_CODES table for values.

COUNTRY_OF_CITIZENSHIP

Indexed - yes
Format - char (2)
May be null? yes

The 2-character IRS tax rate grouping indicating the employee's country of citizenship. The value will be null for U.S. citizens. Sometimes, two countries with a political affiliation share one country code. Refer to the COUNTRY_CODES table for the full names of countries associated with codes.

Examples: JA (Japan), AC (Antigua and Barbuda)


Values:

Refer to the DWADMIN.COUNTRY_CODES table for values.

CPUP_BENEFITS_INDICATOR

Indexed - no
Format - varchar2 (1)
May be null? yes
Indicates (with Y) whether employee is part of the Clinical Practices of the University of Pennsylvania (CPUP). Note that this indicator is manually set by CPUP benefits office, is not cross-validated with job class information, and data entry may lag for new employees.

Values:

Y     Employee is part of CPUP
N     Employee is not part of CPUP

CPUP_JOB_INDICATOR

Indexed - no
Format - varchar2 (1)
May be null? yes
Indicates (with Y/N) whether an employee has an current CPUP (grade CPU) job class, based on History Status = 'C' EMPLOYEE_JOB; the Appt_Begin_Date and Appt_End_Date are not considered in this logic.


Values:

Y     Employee has a current CPUP job
N     Employee does not have a current CPUP job

CURRENT_DIST_INDICATOR

Indexed - no
Format - varchar2 (1)
May be null? yes
Indicates (with Y/N) whether there are any current Payroll distributions for the employee. The indicator is recalculated nightly, and inspects each distribution start and end date relative to the date on which the nightly jobstream began. The logic inspects each distribution with a History Status of 'C', for a job with History Status of 'C'.

Values:

Y     Employee has a current distribution
N     Employee does not have a current
      distribution

CURRENT_JOB_INDICATOR

Indexed - no
Format - varchar2 (1)
May be null? yes
Indicates (with Y/N) whether there are any current Payroll jobs for the employee. The indicator is recalculated nightly, and appointment begin and end date relative to the date on which the nightly jobstream began. The logic inspects each job with a History Status of 'C'.

Values:

Y     Employee has a current job
N     Employee does not have a current job

DISABILITY_STATUS

Indexed - no
Format - varchar2 (1)
May be null? yes

Indicates the disability status, if any, for the employee. Values in this column will only be displayed to individuals with access to disability information; all other users will retrieve nulls in this column.

Values:
A        Ambulatory
C        Coordination
H        Hearing
L        Learning
M        Mental/Psychological
P        Speech
S        Sight
null        No disability status

EDUCATION_LEVEL

Indexed - no
Format - char (2)
May be null? yes
A 2-character code indicating the highest level of education completed by the employee. See also EMPLOYEE / Education_Level_Year.

Examples: C (High School diploma or equivalent), G (Bachelor's Degree).


Values:

B     No academic credentials

C     High School diploma or equivalent

D     Trade certificate

E     Some college

F     Associate Degree

G     Bachelor's Degree

H     Master's Degree

I     Medical Doctorate (M.D., D.D.S., 

      D.V.M., V.M.D.)

J     Other doctorate (Dr of Educ.,

      Dr of Sct., LL.D.)

K     Doctor of Philosophy (Ph.D.)

L     Other

EDUCATION_LEVEL_YEAR

Indexed - no
Format - char (4)
May be null? yes

The year in which the employee obtained the Education_Level. See also EMPLOYEE / Education_Level.

Example: 1982.

EMAIL_ADDRESS

Indexed - no
Format - char (60)
May be null? yes

For employees with electronic mail accounts, the address to which Email may be sent. This column is updated nightly from the Online Directory, which contains data from various school and center source systems as well as individual user updates. The address that an individual has noted as their preferred one is loaded to the Warehouse. If no address has been noted as preferred in the Online Directory, the oldest address available for the individual is loaded to the Warehouse.

Examples: DOE@ISC.UPENN.EDU; doe@sas.upenn.edu

 

EMERGENCY_CONTACT

Indexed - no
Format - char (30)
May be null? yes

The name of the individual to be contacted if there is an emergency involving the employee.

Examples: ANN SMITH, DR & MRS JOHN DOE

EMERGENCY_CONTACT_PHONE

Indexed - no
Format - char (15)
May be null? yes
The telephone number at which the employee's emergency contact can be reached.

Example: 2156661111

EMERGENCY_CONTACT_RELATIONSHIP

Indexed - no
Format - char (8)
May be null? yes

The relationship of the emergency contact to the employee.

Example: HUSBAND, WIFE, MOTHER

EMPLOYMENT_DATE

Indexed - no
Format - date (7)
May be null? yes

The employee's most recent date of hire by the home department. Example: Jane Doe is hired on January 1, 1989; therefore the Employment_Date is initially entered as 01/01/89. Jane Doe is terminated from the University of Pennsylvania on January 2, 1996, but is rehired one year later on January 2, 1997. The Employment_Date for Jane Doe is now January 2, 1997. In the case that an employee holds more than one job, the employment date is still controlled by the home department.

 

EMPLOYMENT_STATUS

Indexed - no
Format - char (1)
May be null? yes

A 1-character code identifying the employee's current employment relationship with the University of Pennsylvania. To ensure that individuals with a status of 'A' are truly active (there may be instances in which the last known value in the Payroll system is 'A', but the employee is no longer active), compare the Primary_Appt_Begin_Date and Primary_Appt_End_Date to the system date, or examine Distribution_Start_Date and Distribution_Stop_Date in EMPLOYEE_DISTRIBUTION where the distribution History_Status = 'C'. A value of 'X' for Employment_Status denotes records for employees whose SSN has been changed; records with an Employment_Status of X will contain incorrect values for SSN.

Values:

A     Active.  Eligible for benefits 

      within certain employee categories.

L     Leave of Absence Without Pay.  

      Benefits are suspended.  Employee 

      must arrange with the Benefits Office

      to pay for or waive benefits.

P     Leave of Absence with Pay.  Eligible

      for benefits.

T     Separated or Terminated.  No longer 

      eligible for University-paid benefits.

X     Indicates a cross-reference record.  

      This is automatically entered on the

      record with the old Social Security Number

      when HRIM/Records completes a Social

      Security Number change.

EMPLOYMENT_STATUS_DATE

Indexed - no
Format - date (7)
May be null? yes

The date the employee's employment status went into effect.
EXEMPT_NONEXEMPT

Indexed - no
Format - varchar2 (1)
May be null? yes

Indicates whether the individual is an exempt or non-exempt employee.
Values:
E     Exempt  
N     Non-exempt
FIRST_NAME

Indexed - yes
Format - varchar2 (30)
May be null? yes

The employee's legal first name, in uppercase.

FULL_PART_TIME

Indexed - no
Format - char (1)
May be null? yes

A 1-character code indicating the salary status of the employee. Refer also to Percent_Full_Time in the EMPLOYEE_JOB table, as temporary workers (Full_Part_Time value of 'T') may be 100% full time according to their job record.
Values:
Refer to the DWCOMP.COMP_SALARY_TYPE_LOOKUP table for values.
HOME_DEPARTMENT

Indexed - yes
Format - char (5)
May be null? yes

The 5-digit Payroll code identifying the department that owns the employee's record and is responsible for its maintenance. Department numbers in the Personnel/Payroll and related systems were converted to four-digit organization codes on July 16, 1999. All records after that date will have null values for Home_Dept; Home_Dept_Org should be used in its place.

Example: 17101 (Data Administration)


Values:

Refer to the DWADMIN.ORG_OLD_TO_NEW table for values.

HOME_DEPT_ORG

Indexed - yes
Format - char (4)
May be null? yes

The 4-digit number that represents the organization that owns the employee's record and is responsible for its maintenance. An organization is a subdivision of the University created for management purposes. An Organization belongs to only one School or Responsibility Center, and its organization code is unique. (The code set is from the University's General Ledger, and has been in use since July 1, 1996.)

Examples: 0104 (CHEMISTRY); 0001 (GEN UNIVERSITY)


Values:

Refer to the DWADMIN.ORG_CODES table for values.

HOME_SCHOOL

Indexed - yes
Format - char (2)
May be null? yes

The 2-digit Payroll code used to identify the school or center that owns the employee's record and is responsible for its maintenance. Home_School codes in the Personnel/Payroll and related systems were converted to Home_School_Ctr codes on July 16, 1999. All records after that date will have null values for Home_School; Home_School_Ctr should be used in its place.

Example: 54 (School of Medicine)


Values:

Refer to the DWADMIN.ORG_OLD_TO_NEW table for values.

HOME_SCHOOL_CTR

Indexed - yes
Format - char (2)
May be null? yes

A 2-digit number used to identify the school or center that owns the employee's record and is responsible for its maintenance. The code set is from the University's General Ledger, and has been in use since July 1, 1996.

Example: 40 (School of Medicine)


Values:

Refer to the DWADMIN.ORG_CODES table, for values where 

Home_School_Ctr = Center_Code.

LAST_ACTION_DATE

Indexed - no
Format - date (7)
May be null? yes

The date of the last personnel action performed in the online Payroll system for the employee. Updates made via the batch cycle will not be reflected in this column.
LAST_EXTRACT_DATE

Indexed - no
Format - date (7)
May be null? yes

The date the EMPLOYEE record was extracted from the Payroll system and loaded into the Warehouse.
LAST_NAME

Indexed - yes
Format - varchar2 (30)
May be null? yes

The employee's legal last name, in uppercase.

LAST_PAY_PROCESSED_DATE

Indexed - no
Format - date (7)
May be null? yes

The date the employee last received pay or payroll processing dealing with benefits.
LAST_PERSONNEL_ACTION

Indexed - no
Format - char (2)
May be null? yes

Identifies the type of the last update made via the online Payroll system to an employee record (e.g., 01 INITIAL EMPLOYMNT).

Values:
Refer to the DWPAY.PAYROLL_ACTION_CODES table for values.
LEAVE_REASON

Indexed - no
Format - char (2)
May be null? yes

For employees on leave of absence from work, the 2-character code indicating the reason for the leave. For employees not on leave, the value will be null.

Example: 01 (Disability - Worker's Compensation only)

Values:
Refer to the DWCOMP.COMP_LEAVE_REASON_LIST table for values.
LEAVE_RETURN_DATE

Indexed - no
Format - date (7)
May be null? yes

For an employee on leave of absence from work, the date of the first of the month when the employee expects to return to work.
MARITAL_STATUS

Indexed - no
Format - char (1)
May be null? yes

The 1-character code that indicates the employee's marital status. This is used to determine benefits enrollment. (This may differ from the marital status the employee declares on the W-4 form for computation of withholding tax for which widowed, divorced, and legally separated persons are considered single.)

Values:

S     Single

M     Married

D     Divorced

W     Widow or widower

MIDDLE_NAME

Indexed - yes
Format - varchar2 (30)
May be null? yes

The employee's legal middle name, in uppercase.

 
MILITARY_STATUS

Indexed - no
Format - varchar2 (1)
May be null? yes

Indicates the military (Veteran) status, if any, for the employee. Values in this column will only be displayed to individuals with access to military status information; all other users will retrieve nulls in this column.

Values:
Refer to the DWCOMP.COMP_MILITARY_LIST table for values.
MINIMUM_RECORD

Indexed - no
Format - varchar2(2)
May be null? yes

An indicator that indicates that the employee record is complete.
Values:
NO   Indicates that insufficient data has been 
     entered by the user. The employee will not 
     be paid.
TX   Necessary forms have not been received by 
     HRIM/Records office. The employee will not 
     be paid.
OK   Minimum record is satisfied, all necessary 
     forms were submitted and approved, and the 
     employee can be paid.
NAME

Indexed - yes
Format - char (30)
May be null? yes

The employee's full legal name, in uppercase, in the format: LASTNAME,FIRSTNAME MIDDLEINITIAL (Note that only a comma separates the last name from the first name. Blank space is not used in the separator.) This is the name used for Federal W-2 forms, for example. (See also Name_Flipped and Professional names.)

Examples: SMITH,JOHN A; SHERWOOD-FOREST,CORKIE S

 

NAME_FLIPPED

Indexed - no
Format - char (30)
May be null? yes

The employee's full legal name, in uppercase, in the format: FIRSTNAME MIDDLEINITIAL LASTNAME. This is the name used for Federal W-2 forms, for example. (See also Professional_Name_Flipped, Name, Name_Prefix, and Name_Suffix.)

Examples: JOHN A SMITH; CORKIE S SHERWOOD-FOREST

 

NAME_PREFIX

Indexed - no
Format - char (4)
May be null? yes

The optional title, entered without punctuation and in uppercase letters, used before the employee's name. (See also Name_Flipped and Name_Suffix.)

Examples: MISS, MRS, MS, MR, DR

NAME_SUFFIX

Indexed - no
Format - char (4)
May be null? yes

The modifier (if any) at the end of the employee's name. Name_Suffix is entered without any punctuation and in uppercase letters. (See also Name_Flipped and Name_Prefix.)

Examples: JR, SR, III, ESQ, PHD

 

NEXT_SALARY_REVIEW_DATE

Indexed - no
Format - date (7)
May be null? yes

The date of the employee's next performance review.
ORIGINAL_HIRE_DATE

Indexed - no
Format - date (7)
May be null? yes

The employee's first date of hire with the University of Pennsylvania. This date can be the same as, or earlier than, the Employment_Date. Example: Jane Doe became a student worker on February 1, 1996. On January 1, 1997, she became a full-time Business Administrator; therefore, Jane Doe's Original_Hire_Date is February 1, 1996 and her Employment_Date is January 1, 1997. Once the Original_Hire_Date is entered, only the Benefits Office can change it (For example, it might be changed in order to bridge service. Bridging is the process by which periods of University service are joined).

 

PAY_STATUS

Indexed - no
Format - varchar2 (1)
May be null? yes

Indicates the pay status (hourly or salaried) for the employee.
Values:
H     Hourly  
S     Salaried
PENN_ID

Indexed - no
Format - char (8)
May be null? yes

The 8-digit identification number assigned to an individual by Penn Community system. For example: 77777777. No two persons have the same Penn ID. Note that Penn IDs created in and assigned by Penn Community begin at 10000000. Within the Data Warehouse, the numeric range from 00000000-00999999 is used for individuals who do not yet have a Penn ID in Penn Community, to allow for reliable joins between tables using the PENN_ID column. When a Penn ID appears for the individual appear in Penn Community, it will replace the assigned value throughout the Data Warehouse.

 

PENNKEY

Indexed - no
Format - varchar2 (24)
May be null? yes

The employee's PennKey, the username required to authenticate an individual's identity to many of Penn's networked systems. For more information on PennKeys, refer to http://www.upenn.edu/computing/pennkey.
PREVIOUS_SSN

Indexed - yes
Format - char (9)
May be null? yes

The 9-character previous Social Security Number of the employee. If an employee record has been entered in the Payroll system with a temporary or incorrect Social Security Number, the HRIM/Records office can change/correct it by entering a new record for the employee. The new record will contain the correct Social Security Number as the value for SSN and the temporary or incorrect Social Security Number as the value for Previous_SSN. The old record (which will have the Employment_Status set to "X") will have the temporary or incorrect Social Security Number as the value for SSN, and the correct Social Security Number as the value for Previous_SSN. The SSN correction is made to all tables containing the employee's SSN; the temporary or incorrect value will appear only in this Previous_SSN column. (See also SSN.)

Values in this column will only be displayed to individuals with access to employee SSN data; all other users will retrieve nulls in this column.

 

PRI_ACAD_APPT_BEGIN_DATE

Indexed - no
Format - date (7)
May be null? yes

The date of the beginning of the employee's latest appointment to the position listed as the primary academic appointment.

The logic used to determine an employee's primary academic appointment is documented in the cautions for this table, at http://www.upenn.edu/computing/da/dw/salmgt/employee.t.html.

PRI_ACAD_APPT_END_DATE

Indexed - no
Format - date (7)
May be null? yes

The date of the end of the employee's appointment to the position listed as the primary academic appointment. Note: If there is no definite end date, Pri_Acad_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).

The logic used to determine an employee's primary academic appointment is documented in the cautions for this table, at http://www.upenn.edu/computing/da/dw/salmgt/employee.t.html.

PRI_ACAD_APPT_JOB_CLASS

Indexed - yes
Format - char (6)
May be null? yes
The 6-digit job class code indicating the employee's primary academic appointment. Key to the job classification table where the job attributes are listed. To find the Job_Title associated with the Primary_Appt_Job_Class, join to the JOB_CLASS_GENERAL table using the Job_Class column. (The results of this join may be questionable for employee records when the Termination_Date falls before April 8, 1998.) 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. For jobs that were reclassified, records before this date will contain the old class, and records after this date will reflect the new scheme. Some classes were reused, however, and JOB_CLASS_GENERAL contains only job titles now associated with those classes. Faculty classes were unaffected by this conversion. (In general, faculty job classes begin with '2'.)

Example: 226010 (Associate Professor C-E)

The logic used to determine an employee's primary academic appointment is documented in the cautions for this table, at http://www.upenn.edu/computing/da/dw/salmgt/employee.t.html.

Values:
Refer to the DWADMIN.JOB_CLASS or DWADMIN.JOB_CLASS_GENERAL
 table for values.

PRI_ACAD_APPT_SALARY

Indexed - no
Format - number (9,2)
May be null? yes

The annual salary for the primary academic job appointment. This field reflects the annual salary for the job in the Payroll mainframe application.

The logic used to determine an employee's primary academic appointment is documented in the cautions for this table, at http://www.upenn.edu/computing/da/dw/salmgt/employee.t.html.


Values:

0 - 9,999,999.99

PRI_ACAD_DEPT_ORG

Indexed - yes
Format - char (4)
May be null? yes

The 4-digit number that represents the Organization responsible for the employee's primary academic job appointment. Pri_Acad_Dept_Org may differ from the Home_Dept_Org. An organization is a subdivision of the University created for management purposes. An Organization belongs to only one School or Responsibility Center, and its organization code is unique. (The code set is from the University's General Ledger, and has been in use since July 1, 1996.)

Example: 1306 (Mechanical Engineering and Applied Mechanics)

The logic used to determine an employee's primary academic appointment is documented in the cautions for this table, at http://www.upenn.edu/computing/da/dw/salmgt/employee.t.html.


Values:

Refer to the DWADMIN.ORG_CODES table for values.

PRI_ACAD_JCLS_ENT_DATE

Indexed - no
Format - date (7)
May be null? yes

The date the employee started working in the position listed as the primary academic appointment. (This may differ from the Pri_Acad_Appt_Begin_Date because that date reflects the start of the latest appointment to the primary job.)

The logic used to determine an employee's primary academic appointment is documented in the cautions for this table, at http://www.upenn.edu/computing/da/dw/salmgt/employee.t.html.

PRI_ACAD_JOB_LINK_ID

Indexed - yes
Format - number (9,0)
May be null? yes

The unique sequence number that identifies the employee's primary academic appointment in the EMPLOYEE_JOB table.

The logic used to determine an employee's primary academic appointment is documented in the cautions for this table, at http://www.upenn.edu/computing/da/dw/salmgt/employee.t.html.

PRI_ACAD_UNIV_POS_NO

Indexed - yes
Format - number (7,0)
May be null? yes
The 7-digit number that identifies the employee's primary academic position at the University of Pennsylvania. A position is an assignment of a person to perform a specific job in a specific organization that is supported by specific funding source(s). Over time, the person, job classification, organization, and funding sources associated with a position may change. As of this writing (November, 2001), faculty, temporary workers, and student workers do not have a number. In instances where no number is available, the element Primary_Univ_Position_Number will be zero.

The logic used to determine an employee's primary academic appointment is documented in the cautions for this table, at http://www.upenn.edu/computing/da/dw/salmgt/employee.t.html.

 

PRIMARY_APPT_BEGIN_DATE

Indexed - no
Format - date (7)
May be null? yes

The date of the beginning of the employee's latest appointment to the position listed as the primary appointment.

The logic used to determine an employee's primary appointment is documented in the cautions for this table, at http://www.upenn.edu/computing/da/dw/salmgt/employee.t.html.

PRIMARY_APPT_END_DATE

Indexed - no
Format - date (7)
May be null? yes

The date of the end of the employee's appointment to the position listed as the primary appointment. Note: If there is no definite end date, Primary_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).

The logic used to determine an employee's primary appointment is documented in the cautions for this table, at http://www.upenn.edu/computing/da/dw/salmgt/employee.t.html.

PRIMARY_APPT_JOB_CLASS

Indexed - yes
Format - char (6)
May be null? yes
The 6-digit job class code indicating the employee's primary appointment. Key to the job classification table where the job attributes are listed. This value matches the "University Census Report" primary appointment. To find the Job_Title associated with the Primary_Appt_Job_Class, join to the JOB_CLASS_GENERAL table using the Job_Class column. (The results of this join may be questionable for employee records when the Termination_Date falls before April 8, 1998.) 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. For jobs that were reclassified, records before this date will contain the old class, and records after this date will reflect the new scheme. Some classes were reused, however, and JOB_CLASS_GENERAL contains only job titles now associated with those classes. Faculty classes were unaffected by this conversion. (In general, faculty job classes begin with '2'.)

Example: 226010 (Associate Professor C-E)

The logic used to determine an employee's primary appointment is documented in the cautions for this table, at http://www.upenn.edu/computing/da/dw/salmgt/employee.t.html.

Values:
Refer to the DWADMIN.JOB_CLASS or DWADMIN.JOB_CLASS_GENERAL
 table for values.

PRIMARY_APPT_SALARY

Indexed - no
Format - number (9,2)
May be null? yes

The estimated annual amount that the employee will be paid for the primary job appointment. The value be 0 or null for Positive Paid employees. (For Positive Paid employees, the time worked during the pay period must be input before the employee is paid.) If the employee is monthly paid (pay cycle = M1 or M2) or salaried weekly (pay cycle = W1 and appointment employee type = 2), the Primary_Appt_Salary is the annual salary (entered on Payroll Screen 004). This figure is approximately correct, but may vary from the actual encumbered amount as Primary_Appt_Salary is not calculated to the level of distributions for the fiscal year. If the employee is hourly paid (pay cycle = W1 and appointment employee type = 3 or 5), the Primary_Appt_Salary is one of the following:

If hours = 0 and % full-time = 0, Then Primary_Appt_Salary = 0 (No data to form an opinion)

If hours = 0 and % full-time > 0, Then Primary_Appt_Salary = hourly rate * 35 * % full-time * 52

(When no hours are available, but % full-time is available, assume 35 hours a week, 52 weeks a year.)

If hours > 0 and hours < 35, Then Primary_Appt_Salary = hourly rate * hours * 52

(Ignore % full-time, base calculation on hours, and assume 52 weeks a year.)

If hours >= 35 and % full-time > 0, Then Primary_Appt_Salary = hourly rate * hours & % full-time * 52

(Use all available fields in calculation and assume 52 weeks a year.)

If hours >= 35 and % full-time = 0, Then Primary_Appt_Salary = hourly rate * hours * 52.

(When % full-time is not available, use hours and assume 52 weeks a year.)

For employees who do not meet any of the above criteria, the last method is used to calculate their Primary_Appt_Salary.

This method of calculation does not consider the week-by-week changes that might be made in actual payments; it is simply a rough estimate using the best data available.

The employee's primary job appointment is determined before the Primary_Appt_Salary is calculated. The calculation logic involves some data scrubbing, in part because of the various ways of implementing part-time status. For example, someone who is working half-time could have the number of hours set to 20 and have % full-time set to 50. If a workaround such as the above were not devised (for example, giving precedence to the number of hours), an employee would show a 25% salary rather than a 50% salary.

There may still be glitches in the data. For example, some employees may have % full-time expressed as a decimal value (say, 50% entered on Payroll System Screen 004 as 00.50, which is 0.50%). There is no way to challenge data entered at this level. Any absurd values for Primary_Appt_Salary are probably due to glitches in the data.

The logic used to determine an employee's primary appointment is documented in the cautions for this table, at http://www.upenn.edu/computing/da/dw/salmgt/employee.t.html.


Values:

0 - 9,999,999.99
PRIMARY_DEPARTMENT

Indexed - yes
Format - char (5)
May be null? yes
The 5-digit Payroll code that indicates the department responsible for the employee's primary job appointment. (This may differ from the employee's home department.) Department numbers in the Personnel/Payroll and related systems were converted to four-digit organization codes on July 16, 1999. All records after that date will have null values for Primary_Dept; Primary_Dept_Org should be used in its place.

Example: 17101 (Data Administration)

The logic used to determine an employee's primary appointment is documented in the cautions for this table, at http://www.upenn.edu/computing/da/dw/salmgt/employee.t.html..


Values:

Refer to the DWADMIN.ORG_OLD_TO_NEW table for values.

PRIMARY_DEPT_ORG

Indexed - yes
Format - char (4)
May be null? yes

The 4-digit number that represents the Organization responsible for the employee's primary job appointment. Primary_Dept_Org may differ from the Home_Dept_Org. An organization is a subdivision of the University created for management purposes. An Organization belongs to only one School or Responsibility Center, and its organization code is unique. (The code set is from the University's General Ledger, and has been in use since July 1, 1996.)

Example: 9120 (Data Administration)

The logic used to determine an employee's primary appointment is documented in the cautions for this table, at http://www.upenn.edu/computing/da/dw/salmgt/employee.t.html.


Values:

Refer to the DWADMIN.ORG_CODES table for values.

PRIMARY_JOB_CLASS_ENTRY_DATE

Indexed - no
Format - date (7)
May be null? yes

The date the employee started working in the position listed as the primary appointment. (This may differ from the Primary_Appt_Begin_Date because that date reflects the start of the latest appointment to the primary job.)

The logic used to determine an employee's primary appointment is documented in the cautions for this table, at http://www.upenn.edu/computing/da/dw/salmgt/employee.t.html.

 

PRIMARY_JOB_LINK_ID

Indexed - yes
Format - number (9,0)
May be null? yes

The unique sequence number that identifies the employee's primary appointment in the EMPLOYEE_JOB table.

The logic used to determine an employee's primary appointment is documented in the cautions for this table, at http://www.upenn.edu/computing/da/dw/salmgt/employee.t.html.

PRIMARY_SCHOOL_CTR

Indexed - yes
Format - char (2)
May be null? yes

A 2-digit number used to identify the school or center associated with Organization (Primary_Dept_Org) responsible for the employee's primary job appointment. The code set is from the University's General Ledger, and has been in use since July 1, 1996.

Example: 40 (School of Medicine)

Values:
Refer to the DWADMIN.ORG_CODES table, for values where 
Home_School_Ctr = Center_Code.
PRIMARY_UNIV_POSITION_NUMBER

Indexed - yes
Format - number (7,0)
May be null? yes
The 7-digit number that identifies the employee's primary position at the University of Pennsylvania. A position is an assignment of a person to perform a specific job in a specific organization that is supported by specific funding source(s). Over time, the person, job classification, organization, and funding sources associated with a position may change. As of this writing (August, 1999), faculty, temporary workers, and student workers do not have a number. In instances where no number is available, the element Primary_Univ_Position_Number will be zero.

The logic used to determine an employee's primary appointment is documented in the cautions for this table, at http://www.upenn.edu/computing/da/dw/salmgt/employee.t.html.

PRIOR_PAY_DATE

Indexed - no
Format - date (7)
May be null? yes

The next to last date the employee received pay or payroll processing dealing with benefits.
PROFESSIONAL_NAME

Indexed - no
Format - char (30)
May be null? yes

The name used by the employee for professional purposes (for example, in directories). The value will default to that of the Name column, unless another value has been designated. The name is stored in uppercase in the format: LASTNAME,FIRSTNAME MIDDLEINITIAL (Note that only a comma separates the last name from the first name; blank space is not used in the separator.) (See also Name and Professional_Name_Flipped.)

Professional name fields are entered into PennWorks and fed nightly from there to the DWCOMP.COMP_PERSON table, and then on to the EMPLOYEE table.

Examples: SMITH,JOHN A; SHERWOOD-FOREST,CORKIE S

PROFESSIONAL_NAME_FLIPPED

Indexed - no
Format - char (30)
May be null? yes

The name used by the employee for professional purposes (for example, in directories). The value will default to that of the Name_Flipped column, unless another value has been designated. The name is stored in uppercase, in the format: FIRSTNAME MIDDLEINITIAL LASTNAME. (See also Professional_Name, Professional_Name_Prefix, Professional_Name_Suffix and Name_Flipped.)

Professional name fields are entered into PennWorks and fed nightly from there to the DWCOMP.COMP_PERSON table, and then on to the EMPLOYEE table.

Examples: JOHN A SMITH, CORKIE S SHERWOOD-FOREST

PROFESSIONAL_NAME_PREFIX

Indexed - no
Format - char (4)
May be null? yes

Optional title used before the employee's professional name. Professional_Name_Prefix is entered without any punctuation and in uppercase letters. (See also Professional_Name_Flipped and Professional_Name_Suffix.)

Professional name fields are entered into PennWorks and fed nightly from there to the DWCOMP.COMP_PERSON table, and then on to the EMPLOYEE table.

Examples: DR, REV, HON

PROFESSIONAL_NAME_SUFFIX

Indexed - no
Format - char (4)
May be null? yes

The modifier (if any) at the end of the employee's professional name. Professional_Name_Suffix is entered without any punctuation and in uppercase letters. (See also Professional_Name_Flipped and Professional_Name_Prefix.)

Professional name fields are entered into PennWorks and fed nightly from there to the DWCOMP.COMP_PERSON table, and then on to the EMPLOYEE table.

Examples: ESQ, PHD, MD

PROVOST_STAFF_MINUTES_DATE

Indexed - no
Format - date (7)
May be null? yes

For faculty members, the date on which the Provost Staff Conference met to discuss their faculty appointment (this value will be null for non-faculty). This field is not currently in use.
PUBLICATIONS_MAIL_CODE

Indexed - no
Format - char (3)
May be null? yes

For faculty of the School of Medicine, the code indicating the location where internal mail for the employee should be delivered. Publications_Mail_Code is invalid for other employees. Although the documentation for the online Payroll system lists other values, this field is, in practice, used only by the School of Medicine.

Example: K (Frankford)


Values:
Refer to the DWCOMP.COMP_PUB_MAIL_CODE_LIST table for values.
      

 

REG_OR_TEMP

Indexed - no
Format - char (1)
May be null? yes

The 1-character code that indicates whether an employee is employed on a regular or temporary basis. Regular employees include full-time, part-time, and limited service workers. Temporary employees include temporary, occasional, and student workers. These workers work 1,000 hours or less in a fiscal year. The fiscal year at the University of Pennsylvania is the 12-month accounting period from July 1 of one year through June 30 of the next year.

Values:

R     Regular

T     Temporary

REHIRE_DATE

Indexed - no
Format - date
May be null? yes

The employee's date of rehire to the University of Pennsylvania. This date can be the same as, or later than, the Original_Hire_Date and Employment_Date.
SENIORITY_DATE

Indexed - no
Format - date (7)
May be null? yes

The date an employee enters a University position that is represented by a bargaining unit (union). This date corresponds to the Bargaining Unit indicator.
SEPARATION_REASON

Indexed - no
Format - char (2)
May be null? yes

For terminated employees, the 2-digit code that indicates the reason for one's termination of employment at Penn.

Example: 01 (Position discontinued), 31 (Early retirement)


Values:

Refer to the DWCOMP.COMP_SEPARATION_REASON_LIST 
table for values.
      
SEX

Indexed - no
Format - char (1)
May be null? yes

The 1-character code that indicates the gender of the employee. This data is used in compliance with Federal reporting regulations.

Example: F (Female), M (Male)


Values:
Refer to the DWCOMP.COMP_GENDER_LIST table for values.
 

    
SSN

Indexed - yes
Format - char (9)
May be null? no

The 9-digit Social Security Number of the employee. A Social Security Number is a unique number assigned to an individual by the federal Social Security Administration.

Values in this column will only be displayed to individuals with access to employee SSN data; all other users will retrieve nulls in this column.

SSN4

Indexed - yes
Format - char (4)
May be null? no

The last four digits of the 9-digit Social Security Number of the individual. A Social Security Number is a unique number assigned to an individual by the federal Social Security Administration.

Values in this column will only be displayed to individuals with access to employee SSN data; all other users will retrieve nulls in this column.

TENURE_PROBATION_DATE

Indexed - no
Format - date (7)
May be null? yes

For faculty members, the date of the first of the month in which their tenure review period begins.

Tenure related fields are maintained in the Faculty Information System (FIS) and fed nightly from there to the DWFAC.FIS_FACULTY table, and then on to the EMPLOYEE table.

TENURE_STATUS

Indexed - no
Format - char (1)
May be null? yes

For faculty members, the 1-character code that indicates their tenure status, as determined by the Provost Staff Conference.

Tenure related fields are maintained in the Faculty Information System (FIS) and fed nightly from there to the DWFAC.FIS_FACULTY table, and then on to the EMPLOYEE table.



Values:

1     Tenure has been granted.

2     Restricted tenure has been granted.  Tenure

      is limited to a specific title or to the 

      duration of specific funding.

3     Under consideration for tenure.  The 

      individual is in the final stage of the tenure

      track, during which the final decision to 

      grant or deny tenure is made.

4     Tenure has been considered and denied.

5     In tenure probationary status.  Accruing 

      tenure status.

6     Probationary status in either the research 

      faculty or the Clinician-Educator track.  

      Subject to mandatory review.

7     Not in tenure probationary status.

8     Senior Clinician-Educator track faculty member

      no longer subject to mandatory review.

9     Senior research faculty member no longer 

      subject to mandatory review.

TENURE_STATUS_DATE

Indexed - no
Format - date (7)
May be null? yes

For faculty members, the date of the first of the month in which the Tenure_Status is expected or was attained. For Clinician-Educators, this information is used for tenure tracking.

Tenure related fields are maintained in the Faculty Information System (FIS) and fed nightly from there to the DWFAC.FIS_FACULTY table, and then on to the EMPLOYEE table.

TERMINATION_DATE

Indexed - no
Format - date (7)
May be null? yes

The date on which the employee's employment relationship with Penn ends. This can be a date in the future. (See also Separation_Reason.)
VISA

Indexed - no
Format - char (2)
May be null? yes

The 2-character code that indicates the type of visa held by an employee who is not a U.S. citizen.

Values:
Refer to the DWCOMP.COMP_VISA_LIST table for values.
VISA_EXPIRATION_DATE

Indexed - no
Format - date (7)
May be null? yes

For employees who are not U.S. citizens, the date when the visa expires.
WORK_PHONE1

Indexed - no
Format - char (15)
May be null? yes

The 15-digit primary telephone number at which the employee can be reached during working hours. The area code is included.

Example: 2158987200

WORK_PHONE2

Indexed - no
Format - char (15)
May be null? yes

A second 15-digit telephone number at which the employee can be reached during working hours. The area code is included.

Example: 2158987171


EMPLOYEE Table   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