Penn Computing

University of Pennsylvania
Penn Computing << go backback

Back to Clickable Table Diagram

AFFILIATION Table - Data Element Index

The Affiliation table provides information on all the Affiliations for a Member of Penn Community. There will be as many rows as there are Affiliations for an individual. The values which are on any Affiliation row depend on the source of the Affiliation - for instance, if an Affiliation is supplied by Penn's payroll system, the SRS And ASSOC fields will be null.

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


 
Data element Definition
ACTIVE_CODE

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

The activity status for this particular affiliation - the status is either A (Active) or I (Inactive). Note: the Active_Code on the Member table is a rollup of all the persistent affiliation Active_Codes for an individual.

top

AFFILIATION_CODE

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

Code used to identify an affiliation of a member of Penn Community.  Affiliations are a form of association to the university.  Certain affiliations are assigned only by specific sources; for instance, a student affiliation is only added by a load process from the Student Record System (SRS).

top

ASSOC_PAY_DEPARTMENT_NAME

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

The department name corresponding to the Assoc_Pay_Employee_Dept number.


top

ASSOC_PAY_EMPLOYEE_DEPT

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

The number that represents the organization that owns the employee's record and is responsible for its maintenance, as supplied by the Associate organization (an example of an Associate organization is the Hospital of the University of Pennsylvania).

top

ASSOC_PAY_EMPLOYEE_ID

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

An identifier supplied by an Associate Organization, such as the University of Pennsylvania Health System, for an employee. 

Note: due to the possibility of various Associate Organizations, it is feasible that this identifer will not be unique in Penn Community.

top

ASSOC_PAY_EMPLOYEE_STATUS

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

Employment status as supplied by an Associate Organization, such as the University of Pennsylvania Health System.  The employment status definitions may vary according to the associate organization supplying the information.

top

ASSOC_PAY_EMPLOYEE_STATUS_DATE

Indexed - no
Format - date
May be null? yes

The latest date associated with an employment status, as supplied by an Associate organization, such as the University of Pennsylvania Health System. 

top

ASSOC_PAY_JOB_CODE

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

The job code, as supplied by the Payroll system of an Associate organization.  An example of an Associate organization is the University of Pennsylvania Health System. 

top

ASSOC_PAY_JOB_CODE_DESCR

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

The job title for an individual, as found on the payroll record, for an Associate organization (such as UPHS).

top

ASSOC_PAY_JOBCODE_EFFECT_DATE

Indexed - no
Format - date
May be null? yes

The date of beginning of employment, as supplied by the Associate Organization (An example of an Associate Organization is the University of Pennsylvania Health System).

top

ASSOC_PAY_NAME

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

The name of a member of Penn Community, as supplied by the payroll system of an Associate organization.  An example of an Associate organization is the University of Pennsylvania Health System.

top

ASSOC_PAY_PAYORG

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

A truncated version of the UPHS payorg. Not used in Penn Community, but in the extract.

top

ASSOC_PAY_PROCESS_LEVEL

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

A division of an Associate organization. An example of an Associate organization is the University of Pennsylvania Health System.

top

ASSOC_PAY_PROCESS_LEVEL_NAME

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

The name of the division corresponding to the Assoc_Pay_Process_Level.

 

top

ASSOC_PAY_STATUS_DESCR

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

The Associate organization's payroll status description, which is used to determine whether the Active_Code for the affiliation is set to Active or Inactive.

top

ATLAS_DEGREED

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

The indicator showing if the individual completed a degree at Penn. Values can be Y or N for affiliation ALUM.

top

ATLAS_DIV_CLASS

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

The division and preferred class year in the ATLAS (Development and Alumni Relations) system for affiliation ALUM. Examples include: WH90 (Wharton, Class of 1990), CGS 75 (College of General Studies, Class of 1975).

top

ATLAS_ID

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

The unique identifier in the ATLAS (Development and Alumni Relations) system for affiliation ALUM.

top

COLLEGE_HOUSE_CODE

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

A student's College House code, as displayed on screen 119 in the Student Records System. This information is available for opt-in display in the Online Directory.

top

COMMENTS

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

Comments field, for use during online addition of a member of the Penn Community.  This field is designed to aid in understanding the history of the association of an individual person or process to Penn Community.

top

DATE_INACTIVATED

Indexed - no
Format - date
May be null? yes

Date of inactivation for a specific affiliation record, not all affiliation records for the individual.

top

DISP_COLLEGE_HOUSE_PENN_FLAG

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

Indicates whether the College House information displays in the PennKey-authenticated view of the Online Directory. The values can be D - Default, Y - Yes or N - No.

top

DISP_COLLEGE_HOUSE_PUB_FLAG

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

Indicates whether the College House information displays in the Public view of the Online Directory. The values can be D - Default, Y - Yes or N - No.

top

EXPIRATION_DATE

Indexed - no
Format - date
May be null? yes

The date upon which a particular affiliation will be inactivated.  Expiration dates apply to specific affiliations, not to all affiliations for an individual.  For instance, a student affiliation will not necessarily expire, while a staff affiliation for the same individual may reach its expiration date and be inactivated.

top

FIRST_NAME

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

The first 'given' name of an individual, as supplied with this particular affiliation.

top

FULL_NAME

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

The name of an individual in the following order,
FIRST_NAME MIDDLE_NAME LAST_NAME as supplied with this particular affiliation.

top

GENDER

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

The gender of the individual, as supplied with this affiliation.

 

top

GUEST_ORG

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

An optional field which can hold the information on an outside originating organization with which a guest affiliation (PGUE, CTSY, etc.) is associated. For instance, if the guest is affiliated with Harvard University, the field can be populated 'Harvard'.

GUEST_TITLE

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

An optional field which can hold the title associated with an outside originating organization with which a guest account (PGUE, CTSY, etc) is associated. For instance, if a guest record is created for a faculty member from Princeton, the GUEST_TITLE can be populated 'Faculty'.

HOST_AFFILIATION_CODE

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

Some affiliations are dependent upon the status of another individual's association with the University, for instance, a Spouse or Child affiliation.  This code is for the 'hosting' affiliation - for example, the affiliation row for the spouse of a faculty would have the faculty member's affiliation code in this field.

top

HOST_PENN_ID

Indexed - no
Format - number (8)
May be null? yes

Some affiliations are dependent upon the status of another individual's association with the University, for instance, a Spouse or Child affiliation.  This field contains the Penn ID of the sponsoring member of the Penn Community. 

top

LAST_NAME

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

The surname of an individual, as supplied with this particular affiliation.

top

MIDDLE_NAME

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

The second given name of an individual, as supplied with for this affiliation.

top

NAME_PREFIX

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

The optional title, from the University Payroll system, entered without punctuation and in uppercase letters, used before an employee's name.

top

PENN_ASSIGNED_SSN

Indexed - yes
Format - varchar2 (9)
May be null? yes

A University-assigned, or faux Social Security Number, used in the case of an individual not having a SSN (such as foreign students), or when the individual has a choice in not providing a true SSN, or when a process doesn't use true SSNs: the Penncard office assigns non-load process Penncards SSNs starting with '888' (examples are Spouse or Alumni cards) .

top

PENN_ID

Indexed - yes 
Format - number (8)
May be null? no

A unique 8-digit number, sequentially generated in the Penn Community database whenever a new member of the Penn Community is identified.  PENN encourages the use of the Penn ID instead of SSN whenever possible.

top

PENN_PAY_EMPLOYMENT_STATUS

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

A 1-character code identifying the University of Pennsylvania Payroll System employee's current employment relationship with the University of Pennsylvania. There may be instances in which the last known value in the Payroll system is 'A', but the employee is no longer active.  Values are:

A Active
L Leave of Absence without Pay
P Leave of Absence with Pay
T Separated or Terminated

top

PENN_PAY_FT_PT_CODE

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

The salary status of a University of Pennsylvania Payroll System employee. Values are:

F Full time regular employment
P Part time regular employment
U Unsalaried.
S Status for individuals who are not regular Full-time but are eligible for special benefits.
L Long-term disability.
T Temporary employment. 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.

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.

W This field indicates that the employee is receiving workers compensation.

top

PENN_PAY_JOB_CLASS_CODE

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

The job class of the primary job for an individual (determined by Census logic).

top

PENN_PAY_JOB_CLASS_TITLE

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

The Job Title currently associated with the Job_Class_Code in the University of Pennsylvania Payroll System. The title associated with a given Job Class may vary over time.

top

PENN_PAY_NAME

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

The University of Pennsylvania Payroll System employee's full legal name, in uppercase, in the format: LASTNAME,FIRSTNAME MIDDLEINITIAL (Note that only a comma separates the last name from the first name. Blank space is not used in the separator.) This is the name used for Federal W-2 forms. 

top

PENN_PAY_ORG

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

The University of Pennsylvania Payroll System employee's home organization, ie., the organization that owns the employee record and is responsible for its maintenance.

top

PENN_PAY_PAID_FLAG

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

Indicates if a person is currently paid (ie., has an active distribution at the time of the payroll rundate) by the University of Pennsylvania Payroll System.

top

PREV_HOST_PENN_ID

Indexed - no
Format - number (8)
May be null? yes

This field contains the Penn ID of the member of the Penn Community who previously sponsored this affiliation (see HOST_PENN_ID). 

top

ROW_CREATED_BY_ID

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

The Penn ID that created this particular Affiliation row.

top

ROW_CREATED_BY_PGM

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

The name of the software object responsible for creation of this particular Affiliation row.

top

ROW_CREATED_DATE_TIME

Indexed - no
Format - date
May be null? yes

Date of creation of the row in the Affiliation table.

top

ROW_LAST_UPD_BY_ID

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

The Penn ID performing the last Affiliation row update for this particular affiliation.

 

top

ROW_LAST_UPD_BY_PGM

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

The software object responsible for performing the last Affiliation row update for this particular affiliation.

top

ROW_LAST_UPD_DATE_TIME

Indexed - no
Format - date
May be null? yes

Date/time of last update on this particular Affiliation table row.

 

top

SORT_NAME

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

The individuals's full legal name for this affiliation, in uppercase, in the format: LAST_NAME, FIRST_NAME MIDDLE_NAME

Examples: SMITH, JOHN ATWOOD; SHERWOOD-FOREST, CORKIE SUTTER

top

SOURCE

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

The software object responsible for the creation of a particular Affiliation. 

 

top

SPONSOR_ORG

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

An optional field which can hold information on a sponsoring organization for a guest affiliation (PGUE, CTSY, etc). For instance, if the Dental School is sponsoring a conference and creates a guest record, the sponsor_org could be populated 'Dental School'.

top

SRS_ACTIVE_CODE

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

Whether a student is considered 'active' in Penn Community ( has an open or a future Academic Program Summary).  This code is specific to a student Affiliation row only.

top

SRS_DIVISION

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

The division associated with the earliest open Academic Program Summary degree pursual for a particular student. A division is an administrative subunit of a school.  Schools at Penn can be composed of one or more divisions. A division oversees students as they pursue degrees within that division. 

Examples of Division code include COL (College of Arts and Sciences), NUR (Nursing Undergraduate), GFP (Fine Arts Graduate) and GEX (Engineering and Applied Science Executive). 

top

SRS_ENTRY_ACTION

Indexed - yes
Format - varchar2 (2)
May be null? yes

Indicates the type of action that caused this Degree Pursual to be opened for this student affiliation.

Values

AD Regular Admit
AS Submatric Admit
AT Transfer Admit
DD Admit to Dual Degree
RA ReAdmit
RI Reinstate
TR Internal Transfer
DT Degree Transfer within Division
SRS_ENTRY_TERM

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

The Entry term for the earliest open or the last closed Academic Program Summary (also known as an 'APS', and described here) associated with a student. The entry term is the term the student first enrolled in classes for this division and degree. This may not be the student's first term at Penn. 

Penn divides the academic year into 3 terms or semsesters: Fall, Spring, and Summer. Terms are designated by the 4-digit calendar year, followed by a term code. The Spring term code is A, the Summer term code is B, and the Fall term code is C. 

For example: 1995C means Fall 1995; 1996A means Spring 1996; 1996B means Summer 1996.

top

SRS_EXIT_ACTION_CODE

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

The action that concludes the earliest open-Academic Program Summary, or (if all APS's are closed), the latest academic activity related to a particular student. This field is set to 'EG' or 'EP' until the degree pursual is completed.
     Note: Leaves do not end Degree_Pursuals.

Values

AW Withdrawn by Division
CA Cancelled by Admitting office
DF Deferred admissions
DR Dropped by Division
EG Expected to Graduate
EP End of program
GF No longer has a record in Student Record System
JE Judicial expulsion (on transcript)
JS Judicial suspension (on transcript)
JX Judicial suspension (not on transcript)
PD Dropped from program
PW Withdrew from program
TR Internal transfer
WD Withdrew
WT Withdrew to transfer
GD Graduated

top

SRS_EXIT_REASON_CODE

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

The action that concludes this Degree Pursual for this student.  This field is held only for downstream systems - this field will not display in Penn Community.

top

SRS_EXIT_TERM

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

The last term the student pursued this degree in this division for this Affiliation row. The value will be EG or EP if the student has not exited the degree program. 

top

SRS_EXPECTED_DEGREE_DATE

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

The term in which the division or department responsible for this student expects this student to receive the his or her degree. If the student is no longer expected to receive the degree, this value may no longer be accurate. 

top

SRS_FT_PT_CODE

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

Indicates the time status of the student, based on the number of courses for which a student is registered, and the rules established for the student's Division.  This field's population is dependent upon the TIME_STATUS_OVERRIDE_FLAG in SRS.

top

SRS_MAJOR_CODE

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

The code for a major. A major is the academic area which makes up a major component of a student's work. For graduate programs, this is the graduate group. For example: a student may have a major in ENGL (English).

top

SRS_MATRIC_DATE

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

The date of the student's entry term in MMYY format.  This date is specific to the student's primary school Academic Program Segment. 

top

SRS_NAME

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

The full name of this student. The name is in uppercase letters. 
The name is in the following format. 
There is no space between the comma and the first name. 

LAST_NAME (space SUFFIX if exists) comma FIRST_NAME space MIDDLE_NAME 
(The LAST_NAME may contain blank spaces).

Example: VAN BURAN JR,MARTIN JOSEPH 

top

SRS_RESTRICT_BIRTH_DATE

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

Indicates whether a student has notified the University to restrict his or her date of birth from release to persons outside of the University. 

top

SRS_RESTRICT_CLASS

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

Indicates whether a student has notified the University to restrict his or her classification (ie., freshman, junior, graduate, professional) from release to persons outside of the University.

top

SRS_RESTRICT_EMAIL_DIR

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

A flag that indicates whether or not a student has restricted his or her email from inclusion on email lists. 

top

SRS_RESTRICT_GENDER

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

A flag that indicates whether or not a student has restricted his or her gender from release to persons outside of the University.

top

SRS_RESTRICT_HARDCOPY_DIR

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

Indicates whether a student has notified the University to restrict printing his/her data to hardcopy directories.

top

SRS_RESTRICT_LOCAL_ADDR

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

Indicates whether the student has notified the University to restrict the local address information from release to persons outside of the University. 

top

SRS_RESTRICT_LOCAL_PHONE

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

Indicates whether a student has notified the University to restrict his or her local phone number from release to persons outside of the University. 

top

SRS_RESTRICT_MAJOR

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

Indicates whether a student has notified the University to restrict information about his or her major field of study from release to persons outside of the University. 

top

SRS_RESTRICT_NAME

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

Indicates whether a student has notified the University to restrict his or her name from release to persons outside of the University. 

top

SRS_RESTRICT_PERM_ADDR

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

Indicates whether the student has notified the University to restrict the permanent address information from release to persons outside of the University. 

top

SRS_RESTRICT_PHOTO

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

Indicates whether a student has notified the University to restrict his or her photo from release to persons outside of the University.

top

SRS_STUDENT_CLASS

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

The student's classification in this program. For undergraduates, classification is system-maintained, based on accumulation of earned credit; for graduate and professional students, it is maintained by the student's division. Example:
     FR means Freshman. 

Values

PBA Post-Bacclaureate (CGS)
PRB Pre-Baccalaureate (CGS
FR Freshman
SO Sophomore
JR Junior
SR Senior
ND Undergraduate, non-degree
G Graduate
GM Graduate, master's candidate
GC Graduate, phd candidate
P Professional
P1 Professional, first level
P2 Professional, second level
P3 Professional, third level
P4 Professional, fourth level
P5 Professional, fifth level
P6 Professional, sixth level
L1 Law, first year
L1A Law, first year (SECTION A)
L1B Law, first year (SECTION B)
L2 Law, second year
L3 Law, third year
SA Senior Associate

top

WHY_INACTIVATED_CODE

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

Indicates whether this affiliation is inactive due to an exceeded expiration date ('E'), or a persistent load's incoming data is not active at the time of the load ('P').

The conversion program for bringing in initial Penn Community data from Penncard will populate affiliations inactive at that point in time with a 'C'.

top

Back to Clickable Table Diagram


Documentation Contact: penncommunity@isc.upenn.edu

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