| Data element |
Definition |
ANNUAL_SALARY
Indexed - no
Format - number (10,3)
May be null? yes |
For salaried employees (both exempt and non-exempt), the annual
salary for this employee for this job. The value should be 0 for positive
time reporting, hourly paid employees (those whose Appt_Employee_Type is
3 or 5). This field reflects the corresponding field in the Payroll mainframe
application.
Values:
0-9,999,999.999
|
|
APPT_BEGIN_DATE
Indexed - no
Format - date (7)
May be null? yes
|
The date of the beginning of the employee's appointment to
the job.
|
APPT_BEGIN_FISCAL_YEAR
Indexed - yes
Format - char (4)
May be null? yes
|
The fiscal year within which the Appt_Begin_Date falls. Penn's
fiscal year begins July 1 of one year and ends June 30 of the next year.
Example: For an appointment beginning on September 1, 1999, the Appt_Begin_Fiscal_Year
is 2000 (fiscal year that began July 1, 1999, and ended June 30, 2000).
|
|
APPT_BEGIN_YEAR
Indexed - yes
Format - char (4)
May be null? yes
|
The calendar year within which the Appt_Begin_Date falls.
Example: For an appointment beginning on September 1, 1999, the Appt_Begin_Year
is 1999.
|
APPT_EMPLOYEE_TYPE
Indexed - no
Format - char (2)
May be null? yes |
Code indicating the Payroll system employee type for the employee,
for the job. This code can be used to distinguish whether the individual
is a monthy, weekly, full-time, paid professional, support staff or student
employee. Note: An employee with more than one job (for example, a faculty
member who also has an administrative job) may have more than one value
for Appt_Employee_Type.
Examples: 1 (monthly paid salaried; exempt from overtime); 3 (non-exempt
hourly paid)
Values:
Refer to the DWCOMP.COMP_EMPLOYEE_TYPE_LIST table
for values.
|
APPT_END_DATE
Indexed - no
Format - date (7)
May be null? yes |
The date of the end of the employee's job appointment. Note:
If there is no definite end date, 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).
|
APPT_END_FISCAL_YEAR
Indexed - yes
Format - char (4)
May be null? yes |
The fiscal year within which the Appt_End_Date falls. Note:
If there is no definite end date for the employee's appointment to the job,
Appt_End_Fiscal_Year will default to the latest available year in the Warehouse,
which is 9999. Penn's fiscal year begins July 1 of one year and ends June
30 of the next year.
Example: For an appointment ending on December 31, 1999, the Appt_End_Fiscal_Year
is 1999 (fiscal year that began July 1, 1999, and ended June 30, 2000).
|
APPT_END_YEAR
Indexed - yes
Format - char (4)
May be null? yes
|
The calendar year within which the Appt_End_Date falls. Note:
If there is no definite end date for the employee's appointment to the job,
Appt_End_Year will default to the latest available year in the Warehouse,
which is 9999..
Example: For an appointment that ends on December 31, 1999, the Appt_End_Year
is 1999.
|
APPT_PAY_AMOUNT_RATE
Indexed - no
Format - number (9,3)
May be null? yes
|
The pay rate for the employee, for the job. The Appt_Rate_Code
indicates whether the Appt_Pay_Amount_Rate is the hourly rate of pay, or
the amount paid by the employer per pay period.
Examples: 5.25, 423.56
|
APPT_RATE_CODE
Indexed - no
Format - char (1)
May be null? yes
|
The 1-character code that indicates whether the Appt_Pay_Amount_Rate
is the hourly rate of pay for the job or the rate per pay period.
Examples: H (hourly - Appt_Employee_Type 3 or 5); P per pay period -
Appt_Employee_Types 1, 2, 4, 8, or 9)
Values:
H Hourly (Appt_Employee_Type 3 or 5)
P Per Pay Period (Appt_Employee_Type 1, 2,
4, 8, or 9
|
HISTORY_STATUS
Indexed - yes
Format - char (1)
May be null? no
|
The 1-character code that indicates whether the EMPLOYEE_JOB
data is Current (C) or History (H). Records with a History_Status of "C"
reproduce the EMPLOYEE_JOB records as they currently exist in the Payroll
system (or as they existed immediately before the employee was purged from
the Payroll system). Records with a History_Status of "H" represent earlier
versions of the data. Note that "current" does not mean "currently active."
Setting a condition of History_Status equal to "C" will not retrieve an
accurate list of currently active EMPLOYEE_JOB records. Along with this
condition, set conditions on Appt_Begin_Date and Appt_End_Date to select
currently active employees and their jobs. (See also History_Status_Date.)
Values:
C Current (that is, the most recent in
the Payroll system)
H History
|
HISTORY_STATUS_DATE
Indexed - no
Format - date (7)
May be null? yes
|
Date on which the History_Status for this EMPLOYEE_JOB record
changed from C (current) to H (history). The date will be null for current
records. This date will match the Last_Extract_Date for the current record
that supersedes the history record. That is, a history record was in effect
for any data greater than or equal to its Last_Extract_Date and less than
its History_Status_Date. If you are investigating job history, you must
take the Job_Dept_Org and Job_Class into account, as well as the History_Status_Date,
the Last_Extract_Date, and the Job_Sequence_Number.
|
HOME_DEPT
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.
|
HOURLY_RATE
Indexed - no
Format - number (7,3)
May be null? yes
|
For employees who are not exempt from overtime pay, the hourly
pay rate for the employee, for the job. This data element reflects information
for Appt_Employee_Types 2 (Non-exempt Salaried - support staff (weekly paid)),
3 (Non-exempt hourly part-time, temporary, and occasional workers and full-time
members of certain bargaining units) and 5 (Non-exempt hourly students --
weekly paid) only. The system automatically calculates the hourly rate for
Appt_Employee_Type 2 (Non-exempt salaried - support staff, weekly paid).
The Hourly_Rate will be "0" for other Appt_Employee_Types. For Appt_Employee_Types
3 and 5, the value for Hourly_Rate will match the value for Appt_Pay_Amount_Rate.
Example: 5.25 ($5.25 per hour)
|
HOURS
Indexed - no
Format - number (5,2)
May be null? yes
|
The standard number of hours in the work week for all regular
employees with this Job_Class code.
Examples: 35, 37.5, 40
|
JOB_CLASS
Indexed - yes
Format - char (6)
May be null? yes
|
The 6-digit code indicating the job held by the employee.
Key to the job classification table where the job attributes are listed.
Refer to Job_Title in the JOB_CLASS or JOB_CLASS_GENERAL tables to find
the title which currently corresponds to the Job_Class. 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 only the attributes now used
for those classes are available). Faculty classes were unaffected by this
conversion.Example: 226010 (Associate Professor C-E)
Example: 226010 (Associate Professor C-E)
Values:
Refer to the DWADMIN.JOB_CLASS table for values.
|
JOB_CLASS_ENTRY_DATE
Indexed - no
Format - date (7)
May be null? yes
|
The date of the beginning of the employee's first appointment
to any Penn job with this Job_Class. (This may differ from the Appt_Begin_Date
because that date reflects the start of the latest appointment to this job.)
|
JOB_DEPT
Indexed - yes
Format - char (5)
May be null? yes
|
The 5-digit Payroll code identifying the department having
primary responsibility for this job appointment. 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 Job_Dept; Job_Dept_Org should be used in its place.
Example: 17101 (Data Administration)
Values:
Refer to the DWADMIN.ORG_OLD_TO_NEW table for values.
|
JOB_DEPT_ORG
Indexed - yes
Format - char (4)
May be null? yes
|
The 4-digit number identifying the Organization having primary
responsibility for this job appointment. 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.
|
|
JOB_LINK_ID
Indexed - yes
Format - number (9,0)
May be null? no
|
A unique sequence number that identifies a row in the EMPLOYEE_JOB table.
This ID is used to facilitate joins between payment or encumbrance data
and the corresponding job information for an employee.
|
JOB_SCHOOL
Indexed - yes
Format - char (2)
May be null? yes
|
The 2-digit Payroll code used to identify the school or center
having primary responsibility for this job appointment. Job_School codes
in the Personnel/Payroll and related systems were converted to Job_School_Ctr
codes on July 16, 1999. All records after that date will have null values
for Job_School; Job_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.
|
JOB_SCHOOL_CTR
Indexed - yes
Format - char (2)
May be null? yes
|
A 2-digit number used to identify the school or center having
primary responsibility for this 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 Job_School_Ctr = Center_Code.
|
JOB_SEQUENCE_NUMBER
Indexed - yes
Format - char (1)
May be null? no
|
A 1-digit number that distinguishes one appointment record
from another for the employee. An employee may have up to four jobs at a
time. (About 2% of employees hold more than one job with the same Job_Class
and the same Job_Dept_Org.) Additions and subtractions to the list of an
employee's appointments can change the order of jobs.
Values:
1
2
3
4
|
LAST_EXTRACT_DATE
Indexed - yes
Format - date (7)
May be null? no
|
The date the EMPLOYEE_JOB record was extracted from the Payroll
system and loaded into the Warehouse. |
PAY_CYCLE
Indexed - no
Format - char (2)
May be null? yes
|
A 2-character code indicating the payment schedule for the
job. The University of Pennsylvania has two main payroll cycles: M1 (monthly)
and W1 (weekly)
Values:
M1 Monthly Paid (Appt_Employee_Type 1, 4,
8, or 9)
M2 Monthly Paid (Appt_Employee_Type 6) -
Note: records for this employee type are
not retained in the Data Warehouse.
W1 Weekly Paid (Appt_Employee_Type 2, 3, or 5)
|
PAY_TERM
Indexed - no
Format - number (2,0)
May be null? yes
|
The 1-digit code indicating how the employee's work schedule
for the job is related to the pay schedule.
Examples: 0 (12 months worked paid over 12 months); 2 (9 months worked
paid over 12 months)
Values:
Refer to the DWCOMP.COMP_TERM_LOOKUP table
for values.
|
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.
|
PERCENT_FULL_TIME
Indexed - no
Format - number (5,2)
May be null? yes
|
The percentage of full-time work completed by the employee
for the job during a pay period. The value is estimated by the department,
as a percentage of the standard number of hours in the work week for the
Job_Class.
Example: For an employee holding a job with 40 standard work hours per
week, who works 20 hours a week: 50 (50%)
|
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.
|
TIME_REPORTING_CODE
Indexed - no
Format - char (1)
May be null? yes
|
For non-exempt employees, the 1-character code that indicates
the required method for reporting hours worked. (The value will be null
for other employees.) P (positive pay) indicates time input is required
in order for the employee to be paid. E (exception) indicates that no time
input is required -- the employee will be paid based on the payroll distribution
lines for the job. (For the payroll distribution lines, see the EMPLOYEE_DISTRIBUTION
Table.)
Values:
P Positive (Hourly employees, Appt_Employee_Types
3 and 5)
E Exception (Salaried employees, Appt_Employee_Types
1, 2, 4, 8, and 9)
|
UNIV_POSITION_NUMBER
Indexed - yes
Format - number (7,0)
May be null? yes
|
The 7-digit number that identifies a 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 Univ_Position_Number
will be zero.
|
