| Data element |
Definition |
| 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.
|
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
|
|
CURRENTLY_EMPLOYED
Indexed - no
Format - varchar2 (1)
May be null? yes
|
Indicates whether the individual is currently employed by
the University. Records for currently employed individuals will show a value
of 'Y' in this column, as indicated by a values of A, L, or P in the Employment_Status
column. 'N' indicates that the employee is no longer employed by the University,
as indicated by a status of T in the Employment_Status column. To ensure
that individuals who are Currently_Employed are truly active (there may
be instances in which the last known employment status in the Payroll system
is 'A', but the employee is no longer active), compare the Primary_Appt_Begin_Date
and the Primary_Appt_End_Date to the system date, or examine the Distribution_Start_Date
and Distribution_Stop_Date in EMPLOYEE_DISTRIBUTION where History_Status
= 'C'.
Values:
Y Employee is currently employed by the University
N Employee is no longer employed by the University
|
CURRENT_DIST_INDICATOR
Indexed - no
Format - varchar2 (1)
May be null? yes |
Indicates (with Y/N) whether there are any current 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 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
|
| 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
|
| 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.
|
| 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.
Note: In EMPLOYEE_GENERAL, Full_Part_Time codes for disability (L), retirement (R) and workers compensation (W) will all be displayed as 'O' (Other). Refer to the EMPLOYEE table to see actual codes for individuals in this group.
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_GENERAL 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). Updates made via the batch cycle will not be reflected in this column.
Values:
Refer to the DWPAY.PAYROLL_ACTION_CODES table for values.
|
| MIDDLE_NAME
Indexed - yes
Format - varchar2 (30)
May be null? yes
|
The employee's legal middle name, in uppercase.
|
| MINIMUM_RECORD
Indexed - no
Format - varchar2(2)
May be null? yes
|
An indicator that indicates that the employee record is complete.
Note: older records for employees who have been purged from Payroll may have null values in this column.
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 - no
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
|
| 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_general.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_general.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_general.t.html.
Values:
Refer to the DWADMIN.JOB_CLASS or DWADMIN.JOB_CLASS_GENERAL
table for values.
|
| 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_general.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_general.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_general.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_general.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_general.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 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_general.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_general.t.html.
Values:
Refer to the DWADMIN.JOB_CLASS or DWADMIN.JOB_CLASS_GENERAL
table for values.
|
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_general.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_general.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_general.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_general.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
Primary_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_general.t.html.
|
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.)
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.)
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.)
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.)
Examples: ESQ, PHD, MD
|
| 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
|
| 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.
|
| 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
in the EMPLOYEE table.)
|
