| Data element |
Definition |
|
AFFILIATION_CODE
Indexed - no
Format - varchar2 (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 in this column will only be displayed to individuals with access to the employee's Home or Partner ORG; all other users will retrieve nulls in this column.
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 in this column will only be displayed to individuals with access to the employee's Home or Partner ORG; all other users will retrieve nulls in this column.
Values:
U Union
null Non-union |
BENEFIT_BASE
Indexed - no
Format - number (9)
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)..
Values in this column will only be displayed to individuals with access to the employee's Home or Partner ORG; all other users will retrieve nulls in this column.
|
BIRTH_DATE
Indexed - no
Format - date (7)
May be null? yes
|
The birth date of the employee.
Values in this column will only be displayed to individuals with access to the employee's Home or Partner ORG; all other users will retrieve nulls in this column. |
| CAMPUS_MAIL_CODE
Indexed - yes
Format - varchar2 (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.
|
| CITIZENSHIP
Indexed - no
Format - varchar2 (1)
May be null? yes
|
Indicates whether the employee is a US Citizen, a Permanent Resident/Green Card Holder, or a Foreign National.
Values:
Refer to the DWCOMP.COMP_CITIZENSHIP_LOOKUP table for values. |
| COMP_PERSON_ID
Indexed - yes
Format - number (12)
May be null? no |
The unique ID for an employee in the PennWorks application. This number is not used in other data collections, and should not be used for reporting. |
|
COUNTRY_OF_CITIZENSHIP
Indexed - no
Format - varchar2 (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.
Values in this column will only be displayed to individuals with access to the employee's Home or Partner ORG; all other users will retrieve nulls in this column.
Examples: JA (Japan), AC (Antigua and Barbuda)
Values:
Refer to the DWADMIN.COUNTRY_CODES table for values.
|
CREATED_BY
Indexed - no
Format - varchar2 (12)
May be null? yes |
The Penn ID of the PennWorks user who created the record in the PennWorks application. |
CREATION_DATE
Indexed - no
Format - date
May be null? yes |
The date on which the record was created in the PennWorks application. |
EDUCATION_FIELD_OF_DEGREE
Indexed - no
Format - varchar2 (300)
May be null? yes |
The field of study in which the highest educational degree was awarded. For Faculty, this data may be fed from FIS (the Faculty Information System).
Values in this column will only be displayed to individuals with access to the employee's Home or Partner ORG; all other users will retrieve nulls in this column. |
EDUCATION_LEVEL
Indexed - no
Format - varchar (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.
Values in this column will only be displayed to individuals with access to the employee's Home or Partner ORG; all other users will retrieve nulls in this column.
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 - varchar2 (4)
May be null? yes |
The year in which the employee obtained the Education_Level. See also
EMPLOYEE / Education_Level.
Values in this column will only be displayed to individuals with access to the employee's Home or Partner ORG; all other users will retrieve nulls in this column.
Example: 1982.
|
|
EMERGENCY_CONTACT
Indexed - no
Format - varchar2 (30)
May be null? yes
|
The name of the individual to be contacted if there is an
emergency involving the employee.
Values in this column will only be displayed to individuals with access to the employee's Home or Partner ORG; all other users will retrieve nulls in this column.
Examples: ANN SMITH, DR & MRS JOHN DOE
|
EMERGENCY_CONTACT_PHONE
Indexed - no
Format - varchar2 (15)
May be null? yes
|
The telephone number at which the employee's emergency contact
can be reached.
Values in this column will only be displayed to individuals with access to the employee's Home or Partner ORG; all other users will retrieve nulls in this column.
Example: 2156661111
|
| EMERGENCY_CONTACT_RELATIONSHIP
Indexed - no
Format - varchar2 (8)
May be null? yes
|
The relationship of the emergency contact to the employee.
Values in this column will only be displayed to individuals with access to the employee's Home or Partner ORG; all other users will retrieve nulls in this column.
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.
In the
case that an employee holds more than one job, the employment date is still
controlled by the home department. The Employment_Date should be the same as or earlier than the Employment_Status_Date, and the same as or later than the Original_Hire_Date.
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. |
| EMPLOYMENT_STATUS
Indexed - no
Format - varchar2 (1)
May be null? yes
|
A 1-character code identifying the employee's current employment
relationship with the University of Pennsylvania.
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.
|
| EMPLOYMENT_STATUS_DATE
Indexed - no
Format - date (7)
May be null? yes
|
The date the employee's employment status went into effect. The Employment_Status_Date should be the same as or after the Employment_Date and Original_Hire_Date. |
| EXEMPT_NONEXEMPT
Indexed - no
Format - varchar2 (1)
May be null? yes |
Indicates whether the individual is an exempt or non-exempt employee.
Under Federal law, employees are either exempt or nonexempt from the Fair Labor Standards Act (FLSA) wage and hour regulations. Monthly-paid staff are exempt, and weekly-paid staff are non-exempt. Non-exempt employees are paid overtime for all hours worked over 40 in a given week.
Values in this column will only be displayed to individuals with access to the employee's Home or Partner ORG; all other users will retrieve nulls in this column.
Values:
E Exempt
N Non-exempt |
| EXTRACT_DATE
Indexed - no
Format - date
May be null? yes |
The date on which the COMP_PERSON record was loaded to the Data Warehouse. |
| FIRST_NAME
Indexed - yes
Format - varchar2 (30)
May be null? yes |
The employee's legal first name, in uppercase. (See also Professional_First_Name.)
|
| FREEZE_ROLE
Indexed - no
Format - varchar2 (1)
May be null? yes |
Indicates (Y/N) whether PennWorks users from organizations other than the employee's Home ORG may add roles for the individual. |
| FULL_PART_TIME
Indexed - no
Format - varchar2 (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 in this column will only be displayed to individuals with access to the employee's Home or Partner ORG; all other users will retrieve nulls in this column.
Values:
Refer to the DWCOMP.COMP_SALARY_TYPE_LOOKUP table for values.
|
| HOME_CENTER
Indexed - yes
Format - varchar2 (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_Center = Center_Code.
|
| HOME_ORG
Indexed - yes
Format - varchar2 (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.
|
| LAST_ACTION_ID
Indexed - no
Format - number (12)
May be null? yes |
The unique identifier of the last personnel action performed in the online PennWorks system for the employee. |
| LAST_NAME
Indexed - yes
Format - varchar2 (30)
May be null? yes
|
The employee's legal last name, in uppercase. (See also Professional_Last_Name.)
|
| 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_UPDATED_BY Indexed - no
Format - varchar2 (12)
May be null? yes |
The Penn ID of the PennWorks user who last updated the record in the PennWorks application. |
| LAST_UPDATE_DATE
Indexed - no
Format - date (7)
May be null? yes |
The date on which the record was last updated in the PennWorks application. |
| LAST_UPDATE_TIMESTAMP
Indexed - no
Format - timestamp (6)
May be null? yes |
The timestamp on which the record was last updated in the PennWorks application. |
| LEAVE_REASON
Indexed - no
Format - varchar2 (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.
Values in this column will only be displayed to individuals with access to the employee's Home or Partner ORG; all other users will retrieve nulls in this column.
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.
Values in this column will only be displayed to individuals with access to the employee's Home or Partner ORG; all other users will retrieve nulls in this column. |
| MARITAL_STATUS
Indexed - no
Format - varchar2 (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 in this column will only be displayed to individuals with access to the employee's Home or Partner ORG; all other users will retrieve nulls in this column.
Values:
Refer to the DWCOMP.COMP_MARITAL_STATUS_LIST table for values.
|
| MIDDLE_NAME
Indexed - no
Format - varchar2 (30)
May be null? yes
|
The employee's legal middle name, in uppercase. (See also Professional_Middle_Name.)
|
| MINIMUM_RECORD
Indexed - no
Format - varchar2(1)
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_PREFIX
Indexed - no
Format - varchar2 (4)
May be null? yes
|
The optional title, entered without punctuation and in uppercase
letters, used before the employee's name. (See also Professional_Name_Prefix and Name_Suffix.)
Examples: MISS, MRS, MS, MR, DR
|
| NAME_SUFFIX
Indexed - no
Format - varchar2 (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 Professional_Name_Suffix
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.
Values in this column will only be displayed to individuals with access to the employee's Home or Partner ORG; all other users will retrieve nulls in this column. |
| 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).
Values in this column will only be displayed to individuals with access to the employee's Home or Partner ORG; all other users will retrieve nulls in this column. |
| PAYROLL_NAME
Indexed - yes
Format - varchar2 (30)
May be null? yes |
The person’s full legal name, in uppercase, as stored in the mainframe Payroll/Personnel System, 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. |
| PAY_STATUS
Indexed - no
Format - varchar2 (1)
May be null? yes
|
Indicates the pay status (hourly or salaried) for the employee.
Values in this column will only be displayed to individuals with access to the employee's Home or Partner ORG; all other users will retrieve nulls in this column.
Values:
H Hourly
S Salaried
|
| PENN_ID
Indexed - yes
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.
|
| PERFORMANCE_INCENTIVE_ELIGIBLE
Indexed - no
Format - varchar2 (1)
May be null? yes
|
A manually-set flag indicating whether the indivdiual is eligible for a performance incentive.
For faculty:
A performance incenstive is a pay plan designed to reward attainment of specific goals. Targets are communicated to faculty members at the beginning of the performance cycle to incent productivity. Payments may be made on an annual, semi-annual, quarterly or monthly basis.
For staff: A formal documented plan developed in consultation with the Compensation Office and approved by the Director of Compensation and the Vice President of Human Resources. Incentive payments are based on performance measured against the written goals and objectives of the plan. All plans must be supported the President, Provost, EVP, or CPUP Administration. Plans must be on file in Human Resources/Compensation and reviewed annually.
Values in this column will only be displayed to individuals with access to the employee's Home or Partner ORG; all other users will retrieve nulls in this column. |
| PERSON_STATE
Indexed - no
Format - varchar2 (1)
May be null? yes
|
Indicates the state of the record in the PennWorks system.
Values:
Refer to the DWADMIN.ROLE_DIST_STATE_LOOKUP
table for values.
|
| 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.
Values in this column will only be displayed to individuals with access to the employee's Home or Partner ORG; all other users will retrieve nulls in this column.
|
PROFESSIONAL_FIRST_NAME
Indexed - no
Format - varchar2 (30)
May be null? yes |
The first name used by the employee for professional purposes (for
example, in directories). The value will default to that of the First_Name column,
unless another value has been designated. For faculty, the source of the name may be Faculty Information System actions. (See also First_Name)
Examples: SMITH,JOHN A; SHERWOOD-FOREST,CORKIE S
|
| PROFESSIONAL_LAST_NAME
Indexed - no
Format - varchar2 (30)
May be null? yes
|
The last name used by the employee for professional purposes (for example, in directories). The value will default to that of the Last_Name column, unless another value has been designated. For faculty, the source of the name may be Faculty Information System actions. (See also Last_Name)
Examples: JOHN A SMITH, CORKIE S SHERWOOD-FOREST
|
| PROFESSIONAL_MIDDLE_NAME
Indexed - no
Format - varchar2 (30)
May be null? yes |
The middle name used by the employee for professional purposes (for example, in directories). The value will default to that of the Last_Name column, unless another value has been designated. For faculty, the source of the name may be Faculty Information System actions. (See also Middle_Name)
Examples: JOHN A SMITH, CORKIE S SHERWOOD-FOREST
|
|
PROFESSIONAL_NAME_PREFIX
Indexed - no
Format - varchar2 (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.)
Examples: DR, REV, HON
|
PROFESSIONAL_NAME_SUFFIX
Indexed - no
Format - varchar2 (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.)
Examples: ESQ, PHD, MD
|
| PUBLICATIONS_MAIL_CODE
Indexed - no
Format - varchar2 (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.
Values in this column will only be displayed to individuals with access to the employee's Home or Partner ORG; all other users will retrieve nulls in this column.
Example: K (Frankford)
Values:
Refer to the DWCOMP.COMP_PUB_MAIL_CODE_LIST table for values.
|
| REG_OR_TEMP
Indexed - no
Format - varchar2 (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.
Values in this column will only be displayed to individuals with access to the employee's Home or Partner ORG; all other users will retrieve nulls in this column.
|
| SEPARATION_REASON
Indexed - no
Format - varchar2 (2)
May be null? yes
|
For terminated employees, the 2-digit code that indicates
the reason for one's termination of employment at Penn.
Values in this column will only be displayed to individuals with access to the employee's Home or Partner ORG; all other users will retrieve nulls in this column.
Example: 01 (Position discontinued), 31 (Early retirement)
Values:
Refer to the DWCOMP.COMP_SEPARATION_REASON_LIST
table for values.
|
| SEX
Indexed - no
Format - varchar2 (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.
Values in this column will only be displayed to individuals with access to the employee's Home or Partner ORG; all other users will retrieve nulls in this column.
Example: F (Female), M (Male)
Values:
Refer to the DWCOMP.COMP_GENDER_LIST table for values.
|
| VISA
Indexed - no
Format - varchar2 (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 in this column will only be displayed to individuals with access to the employee's Home or Partner ORG; all other users will retrieve nulls in this column.
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.
Values in this column will only be displayed to individuals with access to the employee's Home or Partner ORG; all other users will retrieve nulls in this column. |
| WORK_PHONE1
Indexed - no
Format - varchar2 (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.
Values in this column will only be displayed to individuals with access to the employee's Home or Partner ORG; all other users will retrieve nulls in this column.
Example: 2158987200
|
| WORK_PHONE2
Indexed - no
Format - varchar2 (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.
Values in this column will only be displayed to individuals with access to the employee's Home or Partner ORG; all other users will retrieve nulls in this column.
Example: 2158987171
|
