| Data element |
Definition |
ANNUAL_SALARY
Indexed - no
Format - number (10,3)
May be null? yes |
The annual salary for the job appointment. A value is only present for applicable job appointments.
|
APPT_EMPLOYEE_TYPE
Indexed - no
Format - varchar2 (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_PAY_RATE
Indexed - no
Format - number (9,3)
May be null? yes
|
The pay rate for the employee, for the role. Depending upon the employee type, the pay rate will be a pay period amount or hourly rate.
|
COMP_PERSON_ROLE_ID
Indexed - yes
Format - number (12)
May be null? no
|
Uniquely identifies a record in the COMP_PERSON_ROLE table.
|
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 (7)
May be null? yes
|
The date on which the record was created in the PennWorks application.
|
EXTRACT_DATE
Indexed - no
Format - date (7)
May be null? yes
|
The date on which the COMP_PERSON_ROLE record was loaded to the Data Warehouse.
|
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.
|
HOURLY_RATE
Indexed - no
Format - number (7,3)
May be null? yes
|
The hourly rate for the job appointment. A value is only present for applicable job appointments.
|
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
|
INTERIM_ROLE
Indexed - no
Format - varchar2 (1)
May be null? yes
|
Indicates (Y/N) whether the person holds the role on an interim basis. |
JOB_CLASS
Indexed - yes
Format - varchar (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.
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 role with this Job_Class. |
LAST_ACTION_ID
Indexed - no
Format - number (12)
May be null? yes
|
The unique identifier of the last update action performed in the online PennWorks system for the employee/role record.
|
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? no
|
The date on which the record was last updated in the PennWorks application.
|
LAST_UPDATE_TIMESTAMP
Indexed - no
Format - timestamp (6)
May be null? no
|
The timestamp on which the record was last updated in the PennWorks application.
|
ORIG_START_DATE
Indexed - no
Format - date (7)
May be null? yes
|
The date of the original appointment to the role.
|
PAY_CYCLE
Indexed - no
Format - varchar2 (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 - 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.
|
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%)
|
POSITION_NUMBER
Indexed - no
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, 2009), faculty, temporary workers, and student workers do not have a number. In instances where no number is available, the element Position_Number will be zero.
|
ROLE_CENTER
Indexed - yes
Format - varchar2 (2)
May be null? yes
|
The 2-digit number that represents the school or center in which the person performs the role. 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
Role_Center = Center_Code.
|
ROLE_ORG
Indexed - yes
Format - varchar2 (4)
May be null? yes |
The 4-digit number that represents the organization in which the person performs the role. 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.
|
ROLE_STATE
Indexed - yes
Format - varchar2 (1)
May be null? yes |
Selected by the user to indicate the record is ready to be processed by the system if/when appropriate. Records with a state value of ‘P’ (projected) will remain in the PennWorks work area only. Users may select to delete records from PennWorks; these rows are retained in in the Data Warehouse for historical purposes with a state of 'X'.
Values:
Refer to the DWADMIN.ROLE_DIST_STATE_LOOKUP
table for values.
|
ROLE_TITLE
Indexed - no
Format - varchar2 (30)
May be null? yes |
The title corresponding to the role. The title need not correspond to a job class in title in Payroll. The title field is provided for the convenience of the role organization, and may be entered as free-form text or left blank. |
ROLE_TYPE
Indexed - yes
Format - number (12)
May be null? yes |
Identifies the type of role. |
TERM_END_DATE
Indexed - yes
Format - date (7)
May be null? yes |
The end date of the term for which the person holds the role. |
TERM_END_FISCAL_YEAR
Indexed - yes
Format - varchar2 (4)
May be null? yes |
The fiscal year within which the Term_End_Date falls. |
TERM_END_YEAR
Indexed - yes
Format - varchar2 (4)
May be null? yes |
The calendar year within which the Term_End_Date falls. |
TERM_START_DATE
Indexed - yes
Format - date (7)
May be null? yes |
The start date of the term for which the person holds the role. |
TERM_START_FISCAL_YEAR
Indexed - yes
Format - varchar2 (4)
May be null? yes |
The fiscal year within which the Term_Start_Date falls. |
TERM_START_YEAR
Indexed - yes
Format - varchar2 (4)
May be null? yes |
The calendar year within which the Term_Start_Date falls. |
TRF_SORT_CODE
Indexed - no
Format - varchar2 (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 role.
Values:
P Positive (Hourly employees, Appt_Employee_Types
3 and 5)
E Exception (Salaried employees, Appt_Employee_Types
1, 2, 4, 8, and 9)
|
