Penn Computing

University of Pennsylvania
Penn Computing << go backback
EMPLOYEE_JOB Table   Tables and Data Elements   Salary Management Home   Data Warehouse Home

EMPLOYEE_JOB Table - Data Element Index

Select a data element to view its definition and its indexed, format, and null values.



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.

 


EMPLOYEE_JOB Table   Tables and Data Elements   Salary Management Home   Data Warehouse Home

Questions about this page? Email us at da-staff@isc.upenn.edu

Information Systems and Computing
University of Pennsylvania
Information Systems and Computing, University of Pennsylvania