Penn Computing

University of Pennsylvania
Penn Computing << go backback
Tables and Data Elements   Faculty Home   Data Warehouse Home

FIS Lookup Tables

Explanation
Throughout the Faculty Information System (FIS), lookup tables are used to display "friendly names" for a variety of elements. These lookup tables are joined to other tables using the lookup table MESSAGE_ID column. The NAME_FRIENDLY column contains the element description as displayed in FIS, in the order designated in the DISPLAY_INDEX column. Lookup tables are structured as follows:

Data element Definition
DISPLAY_INDEX

Indexed - no
Format - number(12)
May be null - yes

Sequence number used to determine the order that the list of values will display in the application drop down list.

EFFECTIVE_DT

Indexed - no
Format - date
May be null? yes

The effective start date of the lookup table value.

EXPIRATION_DATE

Indexed - no
Format - date
May be null? yes

The effective end date of the lookup table value.

MESSAGE_ID

Indexed - yes (primary key)
Format - number(12)
May be null? no

The unique key used to join to the related Faculty Information System (FIS) table.
NAME_FRIENDLY

Indexed - no
Format - varchar2(200)
May be null? yes

The description of the lookup value, as displayed in FIS.
 
NAME_SYSTEM

Indexed - no
Format - varchar2(100)
May be null? yes

The system abbreviation used to identify the lookup value.
UPD_DT

Indexed - no
Format - date
May be null? no

The date the record was last updated.
UPD_ID

Indexed - no
Format - varchar2(12)
May be null? no

The Penn ID of the person who last updated the record.


Individual lookup tables:

r
Table Name Related Table.Element NAME_FRIENDLY usage
FIS_ACTION_STATUS_LOOKUP
FIS_ACTION.
STATUS_CD
The status of an action.
Valid values:
Action approved - post meeting; action was 
      approved by PSC/PSCS)
Action declined - post meeting; action was 
      declined by PSC/PSCS
Action deferred - post meeting; decision on 
      the action has been deferred
Scheduled for meeting - assigned to a 
      specific meeting
Approved for PSC/PSCS submission - action has 
      passed audit and is ready to be scheduled for 
      a meeting
Returned to school for action - returned to 
      school due to missing or inaccurate 
      information
Under audit review - under review by the 
      Provost's Office for accuarcy and 
      completeness
Draft - not yet submitted to the Provost's Office
FIS_ACTION_TYPE_LOOKUP FIS_ACTION.
ACTION_TYPE
The type of action. Part of what determines which type of language key will be used to build the text minutes.

Examples: Appointment, Chair Designation, Leave of Absence

Note: NAME_FRIENDLY_PLURAL contains the plural version of the value in the NAME_FRIENDLY column.

FIS_ADDRESS_TYPE_LOOKUP FIS_ADDRESS.
TYPE_CD
The type of address.
Valid values:
Local
Office
Permanent
FIS_APPT_BENEFIT_LEVEL_LOOKUP FIS_ACTION.
APPT_BENEFIT_LEVEL
The description of the benefits level granted this action as stated in the minute.
Valid values:
Full
Partial
Without
FIS_APPT_CLASSIFICATION_LOOKUP FIS_ACTION.
APPT_CLASSIFICATION
Indicator of whether an appointment is a primary appointment, secondary appointment, joint (lead), or a joint (non-lead) appointment.
Valid values:
Primary
Secondary
Joint (administering)
Joint (non-administering)
PIK (administering)
PIK (non-adminstering)
Not applicable
FIS_APPT_EMPLOYEE_TYPE_LOOKUP FIS_ACTION.
APPT_EMPLOYEE_TYPE

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: Exempt Salaried - Professional, administrative, and faculty (monthly paid); Exempt Salaried Student (monthly paid)

FIS_APPT_SALARY_LEVEL_LOOKUP FIS_ACTION.
APPT_SALARY_LEVEL
The description of the salary level granted this action as stated in the minute.
Valid values:
Full
Partial
Without
FIS_APPT_TRACK_LOOKUP FIS_ACTION.
APPT_TRACK

The appointment track associated with the proposed action, as used in the text minutes.

Valid values:
Standing Faculty
Standing Faculty C-E
Associated Faculty
Academic Support Staff

Emeritus
FIS_CHAIR_TYPE_CODE_LOOKUP FIS_ACTION.
CHAIR_TYPE_CODE
Indicates the type of Chair.
Valid values:
Department
Endowed
Endowed Term
Term
Endowed-PIK
FIS_CHANGE_REASON_LOOKUP

FIS_ACTION.CORRECTION

(Referred to in the Business Objects universe as Correction Reason)

Indicates the reason for a change to the action.
Valid values:
Change in previously approved minute
Change in the effective date
Change in the period of appointment
Correction
FIS_COMMITMENT_TYPE_LOOKUP FIS_COMMITMENTS.
COMMITMENT_TYPE

Indicates the type of commitment made.

Examples: Computer, Equipment, Lab Space

FIS_DISCIPLINE_LIST FIS_ACTION.DISCIPLINE_CD

The name of the discipline associated with an action, used only in conjunction with select departments.

FIS_ED_LEVEL_LIST FIS_DEGREE_TYPE_LIST.
EDUCATION_LEVEL

The description of the education level associated with a degree type.

Examples: No academic credentials, Bachelor's Degree, Doctor of Philosophy (Ph.D.)

FIS_EMAIL_TYPE_LOOKUP FIS_EMAIL.IDIndicates the type of email address.
Valid values:
Primary
Alternate
FIS_ETHNICITY_LOOKUP FIS_FACULTY.
ETHNICITY_CD

The description of the ethnicity associated with a faculty member.

Valid values:
Asian
Black/African American
Declined to Identify
Hispanic/Latino
Native American
Other
Pacific Islander
White/Caucasian
FIS_GENDER_LOOKUP FIS_FACULTY.
GENDER_CD
Indicates the the sex of the faculty member.
Valid values:
Male

Female
Unknown
FIS_JOB_CLASS

FIS_ACTION. JOB_CLASS_TITLE; FIS_ACTION.
PROPOSED_JOB_CODE

Maps the job class for the action to the title as used in the text minutes.

Examples: Professor C-E, Wistar Institute Associate Professor

FIS_HOSPITAL_LOOKUP

FIS_HOSP_AFFIL_ACTION.
HOSPITAL_AFFIL_ID

The full name of a hospital that may be affiliated with an action.

Examples: the Children's Hospital of Philadelphia, the Veteran's Administration Medical Center

FIS_LOA_BENEFIT_LEVEL_LOOKUP FIS_LOA_BENEFIT_FUNDING.
LOA_BENEFIT_LEVEL
Indicates the leave of absence (LOA) benefit level.
Valid values:
Appropriate - Commensurate with 
     LOA salary level %
Full
None
Paid by 3rd Party
Paid by employee
FIS_LOA_EVENT_TYPE_LOOKUP FIS_LOA_CREDIT.
EVENT_TYPE_CD
Indicates the type of LOA credit.
Valid values:
Automatic Accrual
Automatic Accrual - RID
LOA Action Debit
Accrual Eligible
Manual Adjustment
FIS_LOA_SALARY_LEVEL_LOOKUP FIS_LOA_SALARY_FUNDING.
LOA_SALARY_LEVEL
Indicates the LOA salary level.
Valid values:

Full
None
Partial
FIS_LOA_SAL_FUND_TYPE_LOOKUP FIS_LOA_SALARY_FUNDING.
LOA_SALARY_FUNDING_TYPE
Indicates the type of LOA salary funding.
Valid values:
Department
Grants
Leave Budget
Other
FIS_LOA_STATUS_LOOKUP FIS_ACTION.
LOA_STATUS
The description of the of LOA status.
Valid values:
Current
Future
Historic
Pending
FIS_LOA_TYPE_LOOKUP FIS_ACTION.
LOA_TYPE
Indicates the type of leave of absence (LOA).
Valid values:
Medical
Scholarly
Compressed Scholarly
Employment Elsewhere
Administrative
Faculty Master
FIS_MARITAL_STATUS_LOOKUP FIS_FACULTY.
MARITAL_STS_CD
Indicates the individual's marital status, as entered into FIS.
Valid values:
Divorced
Married
Single
Unknown
Widow or widower
FIS_MEETING_STATUS_LOOKUP FIS_MEETING.STATUS Indicates the status of the meeting.
Valid values:
Not Reviewable
Reviewable
Closed to Review
Done
Cancelled
FIS_MEETING_TYPE_LOOKUP FIS_MEETING.TYPE Indicates the type of meeting.
Valid values:
PSC
PSCS
Trustees
FIS_MILITARY_BRANCH_LOOKUP FIS_ACTION.
MILITARY_BRANCH_CD
Indicates the military branch associated with ROTC actions:
Valid values:
US Navy
US Marine Corps
US Marine Corps Reserve
Naval Reserve Officers Training Corps
FIS_PHONE_TYPE_LOOKUP FIS_PHONE.TYPE_CD Indicates the type of phone number.
Valid values:
Office
Cell
Local
Permanent
Fax
FIS_PSC_CONTINGEN_TYPE_LOOKUP FIS_CONTINGENCY.
TYPE_CD
Indicates the type of contingency.
Valid values:
Administrative
Permanent Residency
PhD
Visa
FIS_REDUCTION_LOOKUP FIS_ACTION.
REDUCTION_IN_DUTIES_CD
Indicates the reducation in duties reason.
Valid values:
serious illness or injury
child care
elder care
faculty anticipating a move to 
    emeritus status
FIS_REVIEW_TYPE_LOOKUP FIS_REVIEW.
REVIEW_CD
Indicates the reviewer's disposition toward an action.
Valid values:
Yes
Discuss - have some questions
Maybe - unsure / leaning towards no
FIS_TENURE_EXT_CODE_LOOKUP FIS_ACTION.
TENURE_EXTENSION_CD
Indicates the reason for granting extension of the tenure probationary period.
Valid values:
Childbirth
Caring for family member
Personal illness
Reduction in duties 
Trustees Resolution of 6/17/2005
FIS_TENURE_STATUS_LOOKUP FIS_ACTION.TENURE_STATUS Indicates the tenure status, as determined by PSC.
Valid values:
1 Granted
2 Restricted
3 Under final consideration
4 Denied
5 Accruing
6 Research or clin prob
7 Not in prob status
8 Snr clin - no more prob
9 Snr research - no more prob
FIS_TERMINAT_REASON_CD_LOOKUP FIS_ACTION.
TERMINATION_REASON
Indicates the reason for termination.
Valid values:
Position Discontinued
Lack of Funding
Completion of Contract
End of Appointment
Not Qualified
Excessive Unexcused Absence
       or Tardiness
Unsatisfactory Performance
Misconduct or Violation of Rules
Other
Normal Retirement
Early Retirement
Accepted New Job (Academic)
Accepted New Job (Non-Academic)
Marriage or Domestic Obligations
Dissatisfied with Pay
Personal Reasons
Quit without Notice
Relocation
Returned to School
Dissatisfied with Working
       Conditions
Student Job Ended
Medical Necessity
Death
FIS_VOTING_RIGHTS_CODE_LOOKUP FIS_ACTION.
VOTING_RIGHTS_CODE
Indicates the type of voting rights:
Valid values:
Non-Voting
Partial Voting rights
Full Voting rights

Tables and Data Elements   Faculty 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