| Data element |
Definition |
ACCOUNTING_PERIOD
Indexed - yes
Format - char (6)
May be null? yes |
The Accounting Period stated in MON-YY format. MON is the calendar month
(stored in upper case), and YY is the calendar year. Note that the beginning
and ending dates of an ACCOUNTING_PERIOD are not the same as the first
and last day of the calendar month.
Example: JUL-97 (July 1997, which occurs in fiscal year 1998).
|
ANTICIPATED_FUNDS
Indexed - no
Format - number(11,2)
May be null? yes |
The sum of payments and remaining encumbrances for the accounting period
from fund 000001. Derived from the Salary Management system.
|
APPT_BEGIN_DATE
Indexed - yes
Format - date
May be null? yes |
The date of the beginning of the employee's appointment to the job. Appt_Begin_Date
is stored in the Warehouse in date format. In Business Objects, it is
displayed as specified in the desktop operating system settings. It is
recommended that users specify a format including 4 digits for the year.
(To specify the date format in Windows, use the Control Panel's Regional
Settings, selecting the Date tab and specifying the Short date style.
On the Macintosh, use the Date & Time Control Panel.)
|
APPT_END_DATE
Indexed - yes
Format - date
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_Date
is stored in the Warehouse in date format. In Business Objects, it is
displayed as specified in the desktop operating system settings. It is
recommended that users specify a format including 4 digits for the year.
(To specify the date format in Windows, use the Control Panel's Regional
Settings, selecting the Date tab and specifying the Short date style.
On the Macintosh, use the Date & Time Control Panel.)
|
DESIGNATED_FUNDS
Indexed - no
Format - number(11,2)
May be null - yes
|
The sum of payments and remaining encumbrances for the accounting period
from fund numbers that begin with 4 (endowment funds), 6 (gift funds),
and to the extent that they exist, fund numbers that begin with 1, 2,
3, 7, 8, or 9. Derived from the Salary Management system.
|
EMPLOYEE_SALARY
Indexed - no
Format - number(11,2)
May be null - yes
|
Salary is computed for Filled positions as follows:
For Monthly Paid and Weekly (type 2, as designated in Payroll) employees: Salary is derived from the Annual Salary field in the Employee Job
record in the Payroll/Personnell system.
For other Weekly Paid employees
(type 3), salary is computed using the rate and hours in the Employee
Job record. Assumptions: Weekly Paid are assumed to work a 35-hour week,
52 weeks a year (salary will be determined by the first condition an
employee meets)
- If hours and rate are both = 0, then salary = 0
- If hours = 0, but percent full time is greater than 0, then salary
= rate * 35 * percent full-time * 52
- If hours are greater than zero but less than 35, then salary = rate
* hours * 52
Salary is computed for Non-filled Positions as follows:
If the position was previously filled, the previous incumbent's salary
is used, as computed above. If the position has never been filled, the
average of active, primary appointments with the same job class is computed
and used. If a reasonable average is unavailable, the annualized salary
using the midpoint for the job class (maximum minus minimum, divided
by 2, with adjustments for Weekly Paid employees) is computed and used.
If a reasonable midpoint is unavailable, the average of active, primary
appointments with the same Personnel Class (Administrative/Professional,
Support Staff of Unionized Staff) is computed and used.
|
EMPLOYMENT_STATUS
Indexed - yes
Format - char(1)
May be null - yes
|
A 1-character code identifying the employee's current employment
relationship with the University of Pennsylvania. 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.
|
EXTERNAL_FUNDS
Indexed - no
Format - number(11,2)
May be null - yes
|
The sum of payments and remaining encumbrances for the accounting period
from non-university commitments (ie. anything with an object code of 1251,
1252, 1253, 1254, 1521, 1522, 1524; or anything from a "9-fund" with an
object code between 5000 and 5199). Derived from the Salary Management
system.
|
FISCAL_YEAR
Indexed - yes
Format - char (4)
May be null - yes
|
The financial year. Penn's fiscal year begins July 1 of one calendar
year and ends June 30 of the next calendar year.
Example: 1998 (fiscal year that began July 1, 1997, and ended June 30,
1998)
|
FULL_PART_TIME
Indexed - yes
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:
F Full-time regular employment.
Eligible for benefits.
P Part-time regular employment.
Eligible for certain benefits.
U Unsalaried. No benefits, except for
Faculty appointments approved by the Provost.
S Status for individuals who are not regular
Full-time but are eligible for special
benefits. Usually for Post-Doctoral
Fellows. Non Post-Docs must be approved by
the Provost's Office.
L Long-term disability. Assigned by the
Benefits Office only.
T Temporary. No benefits. This designation
includes all employees with "Temp" in their
title and all students except Post-Doctoral
fellows. For non-students, this designation
may not be used if an individual accumulates
more than 1,000 hours in the Fiscal Year.
The Fiscal Year at Penn begins July 1 of one
calendar year and ends June 30 of the next
calendar year.
R Retired. Once this code is assigned, it
cannot be changed, even if the employee
returns to work on a part-time, temporary,
or occasional basis. Retired employees must
meet age and service requirements for retiree
benefits.
W Assigned by the Workers Compensation Office
only. This field indicates that the employee
is receiving workers compensation.
|
FUNDS_SALARY_DIFF
Indexed - no
Format - number(11,2)
May be null - yes
|
The difference between Total Funds and Total Salary Commitment. (Total
Salary is subtracted from Total Funds.)
|
GENERAL_OPERATING_FUNDS
Indexed - no
Format - number(11,2)
May be null - yes
|
The sum of payments and remaining encumbrances for the accounting period
from General Operating Funds -- those fund numbers that begin with 0,
except funds 000001 and 000011. (e.g. general university funds). Derived
from the Salary Management system.
|
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 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 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 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 CNAC_CODES table, for values where
Home_School_Ctr||'0' = CNAC_Code.
|
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 JOB_CLASS table for values.
|
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 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 ORG_CODES table for values.
|
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 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 CNAC_CODES table, for values where
Home_School_Ctr||'0' = CNAC_Code.
|
PENN_ID
Indexed - yes
Format - char (8)
May be null - yes
|
The 8-digit identification number assigned to the employee by the PENNcard
system. For example: 77777777. No two persons have the same Penn_ID. Only
persons affiliated with the University will be assigned a Penn_ID. Note:
As of this writing (August, 1999), Penn_ID is populated in the Salary
Management data collection tables only for employees who have records
in the PENNcard system. Individuals who were not in Payroll after December,
1998 may have null values for Penn_ID. This column should be populated
for employees in Payroll from January, 1999 and after. Since it is not
completely populated throughout the collection, Penn_ID should be used
with caution in joining tables.
|
PERCENT_FULL_TIME
Indexed - yes
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%)
|
PERSONNEL_CLASS
Indexed - yes
Format - char(2)
May be null - yes
|
The
class to which a position is assigned as a function of employee
type and or job grade. Includes only those classes subject to the
HR-1 process (the form used to request a position posting from
Human Resources). Executive, Faculty (unless they hold an
Administrative posting as well), Non-Employees, and Student Workers
are excluded. Payments-to-date and projected encumbrances are
reported for all Personnel Classes with the exception of Temporary
staff. For Temporary staff, only payments are included. [Note:
"Type" of employee, below, is derived from the EMP TYPE in the
Personnel/Payroll system.]
Administrative/Professional: Type 1 employees with
Job Grade not equal to FAC (Faculty)
Support Staff: Meeting any of the following criteria:
-Type 2 employees, first position having
Job Grade between 021 and 032 or any Job Grade A-H
-Type 2 or 3 employees with a Job Grade equal to
LIM (would otherwise be categorized as Temporary;
includes Limited Service Clerk, Limited Service
Positive, Limited Service Exception and Theatre
Tech)
-Job Class = 515550 - Regular Part-time
(would otherwise be classified as Temporary)
-Job Class = 515515 - Regular Part-time
(would otherwise be classified as Temporary)
Unionized Staff: Job Grade = U
Temporary Staff: Only payments-to-date reported, no
projected encumbrances
-Job Grade = TEM
-Employee Type = 3, Job Grade not = U
-Job Class = 855150 - Occasional workers
|
POSITION_BEGIN_DATE
Indexed - yes
Format - date
May be null - yes
|
The date of the beginning of the position; independent of appointment begin and end dates. Position_Begin_Date
is stored in the Warehouse in date format. In Business Objects, it is
displayed as specified in the desktop operating system settings. It is
recommended that users specify a format including 4 digits for the year.
(To specify the date format in Windows, use the Control Panel's Regional
Settings, selecting the Date tab and specifying the Short date style.
On the Macintosh, use the Date & Time Control Panel.)
|
POSITION_END_DATE
Indexed - yes
Format - date
May be null - yes
|
The ending date of the position; independent of appointment begin and end dates. Position_Begin_Date
is stored in the Warehouse in date format. In Business Objects, it is
displayed as specified in the desktop operating system settings. It is
recommended that users specify a format including 4 digits for the year.
(To specify the date format in Windows, use the Control Panel's Regional
Settings, selecting the Date tab and specifying the Short date style.
On the Macintosh, use the Date & Time Control Panel.)
|
POSITION_NUMBER
Indexed - yes
Format - number(7)
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 Position_Number
will be zero
|
POSITION_PAYMENT_SUMMARY_PK
Indexed - no
Format - number(9)
May be null - yes
|
|
POSITION_STATUS
Indexed - yes
Format - char(1)
May be null - yes
|
A position is a post of employment offered by an organization
within the University for a given job classification. A position can have
a status of Approved, Discontinued, Filled, or Vacated. Be aware that Position
Status is distinct from and unrelated to job classification, and that reclassification
of a job will not affect position status. Promotion or transfer of a current
employee to another position, however, would affect the status of both new
and old positions. Position Status is derived from the Payroll/Personnel
system.
Approved: A position has been created online and has
been approved to be filled. Only persons with the
proper authority can approve a position.
For Schools, Position Status automatically returns
to Approved rather than Vacated when the
employee leaves the position.
Discontinued: The position cannot be refilled.
Only persons with the appropriate authority
can discontinue a position.
Filled: A School or Center has assigned an employee
to a position on the Job screen in the
Payroll/Personnel system. Includes employees on
leave of absence.
Vacated: A position exists but is currently unfilled.
This is the default for Administrative Centers. For
Schools, Position Status automatically
returns to Approved rather than Vacated when
the employee leaves the position.
|
POSITION_STATUS_DATE
Indexed - no
Format - date
May be null - yes
|
The date the position status went into effect. The Position_Status_Date
is stored in the Warehouse in date format. In Business Objects, it is
displayed as specified in the desktop operating system settings. It is
recommended that users specify a format including 4 digits for the year.
(To specify the date format in Windows, use the Control Panel's Regional
Settings, selecting the Date tab and specifying the Short date style.
On the Macintosh, use the Date & Time Control Panel.)
|
POSITION_SUMMARY_PK
Indexed - no
Format - number(9)
May be null - no
|
The unique identifier for a row in the POSITION_SUMMARY table.
|
POSITION_TITLE
Indexed - no
Format - varchar2(50)
May be null - yes
|
The title currently associated with the position. The Position_Title defaults to the Job_Title for the Job_Class associated
with the position. If the Position_Title is changed from the default, it affects only the position -- the Job_Title is not updated. The title associated with a given Job_Class may vary
over time, as the Data Warehouse does not store historical data on Job_Classes.
A report listing titles of past jobs for an employee may therefore not exactly
reflect the title associated with the Job_Class at the time the person held
the job. 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. Faculty classes were unaffected
by this conversion. Some classes were resued, however (and only the attributes
now used for those classes are available).
|
SALARY_COMMITMENT
Indexed - no
Format - number(11,2)
May be null - yes
|
The portion of the annualized salary that is payable this
fiscal year. Salary Commitment compensates for instances when a person leaves
before the end of the fiscal year, or begins after the start of the fiscal
year, or when a position is filled by more than one person at different
times during the course of the year. If a position is filled more than once
in the course of the year, all distinct instances of "filled" status will
be taken into account when summing the salary commitment. Dollar amounts
represent a combination of payments made to date plus remaining encumbrances
for the fiscal year. Historical data is based on actual payments to employees
rather than projected Payroll distributions and takes start and end date
into account.
The computation process for Salary Commitment is as follows:
1. All positions for the fiscal year are scanned,
and the following information is extracted for each
filled entry:
- Annualized salary, as computed in Employee_Salary.
- Fiscal year begin date, or position assignment
start date, whichever comes last.
- Fiscal year end date, or position assignment end
date, whichever comes first.
Position Assignment End Date is adjusted to avoid
overlap. If someone leaves a position, the Position
Assignment End Date may still contain the original
value; if so, for the purposes of calculation, it is
changed to reflect the date when the position was
vacated. If someone is replaced in a position, the
original incumbent's end date must be less than or
equal to the new incumbent's start date. The
calculation does not account for position sharing.
2. After the salary is annualized, it is divided by
365 and multiplied by the number of position
assignment days to obtain Salary Commitment.
|
SERVICE_CENTER_FUNDS
Indexed - no
Format - number(11,2)
May be null - yes
|
The sum of payments and remaining encumbrances for the accounting period
from fund 000011. Derived from the Salary Management system.
|
SPONSORED_PROGRAM_FUNDS
Indexed - no
Format - number(11,2)
May be null - yes
|
The sum of payments and remaining encumbrances for the accounting period
from Sponsored Program Funds, where the fund number begins with 5. Usually,
these are grant or contract funds that are awarded for a specific purpose
and that are often government related. Examples are Wharton SBDC and Administrative/Professional
staff who are paid from this fund number. Often calendar year based, so
two funds may be applied for a fiscal year. Encumbrances are only calculated
for the duration of the grant or contract. Derived from the Salary Management
system.
|
SSN
Indexed - yes
Format - char(9)
May be null - yes
|
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.
|
TENURE_STATUS
Indexed - yes
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. This value
can only be entered or updated by the Office of the Provost.
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.
|
TOTAL_FUNDS
Indexed - no
Format - number(11,2)
May be null - yes
|
The sum of Total Operating, Designated, Sponsored Program, and External
Funds.
|
TOTAL_OPERATING_FUNDS
Indexed - no
Format - number(11,2)
May be null - yes
|
Sum of General Operating, Anticipated and Service Center Funds Designated
Funds.
|
