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