| 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. |
|
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
|
|
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
|
|
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
|
|
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. |
|
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
|
