PENNERA_PROPOSAL_INVESTIGATOR Table - Data Element Index
Select a data element to view its definition and its indexed, format, and
null values.
Data element |
Definition |
ERA_PRIMARY_ORG_CODE
Indexed - no
Format - char (4)
May be null? yes |
The 4-character code for the ERA primary organization (‘org.’)
for the investigator. The ERA primary org. is the org. flagged as the
primary department in the person’s PennERA Profile. Although it
is used to secure certain data elements in the Data Warehouse, the ERA
primary org. is not used by the University’s business processes.
For Penn staff, the ERA primary org. is the primary (job) appointment
org. For Penn faculty, it is the primary academic (job) appointment org.,
which will not be the same as primary appointment org. for those faculty
holding administrative positions. For Penn employees whose job appointments
are all on the executive payroll, ERA primary org. is ‘8000’ ('General
University Special '). For Penn students, the ERA primary org. is the
organizational equivalent of their home Division (for example, ‘0200’,
School of Arts and Sciences.) For investigators from the University of
Pennsylvania Health System (UPHS), the ERA primary org. is ‘2100’ ('Health
System '). For members of the research community who are otherwise not
affiliated with Penn, the ERA primary org. is ‘8760' ('Research
Services').
ERA_PRIMARY_ORG_CODE reflects the ERA primary org. of the investigator
as it was at the time of the proposal (funding cycle). Once the funding
cycle has ended, the ERA_PRIMARY_ORG_CODE is no longer updated. The dates
for the funding cycle are the AWARDED_PROJECT_START and AWARDED_PROJECT_END
in the PENNERA_PROPOSAL record for the INSTITUTION_NO. (If the proposal
was not awarded, the dates for the funding cycle are the REQUESTED_PROJECT_START
and REQUESTED_PROJECT_END in the PENNERA_PROPOSAL record for the INSTITUTION_NO.)
To facilitate Organization-based security in other tables, ERA_PRIMARY_ORG_CODE
is set to '.' if it would otherwise be null.
Example: 4261 (DM-Rheumatology)
Values:
Refer to the ORG_CODES table for values.
Source: PENNERA_PEOPLE.ERA_PRIMARY_ORG where PENNERA_PROPOSAL_INVESTIGATOR.PENN_ID = PENNERA_PEOPLE.PENN_ID
|
ERA_PRIMARY_SCHOOL_CODE
Indexed - no
Format - char (2)
May be null? yes |
The 2-character code for the school or center for the ERA_PRIMARY_ORG_CODE.
That identifies the ERA primary organization (‘org.’) for
the investigator. The ERA primary org. is the org. flagged as the primary
department in the person’s PennERA Profile. Although it is used
to secure certain data elements in the Data Warehouse, the ERA primary
org. is not used by the University’s business processes.
ERA_PRIMARY_SCHOOL_CODE reflects the ERA primary school of the investigator
as it was at the time of the proposal (funding cycle). Once the funding
cycle has ended, the ERA_PRIMARY_SCHOOL_CODE is no longer updated. The
dates for the funding cycle are the AWARDED_PROJECT_START and AWARDED_PROJECT_END
in the PENNERA_PROPOSAL record for the INSTITUTION_NO. (If the proposal
was not awarded, the dates for the funding cycle are the REQUESTED_PROJECT_START
and REQUESTED_PROJECT_END in the PENNERA_PROPOSAL record for the INSTITUTION_NO.)
If the ERA_PRIMARY_ORG_CODE is '.', the ERA_PRIMARY_SCHOOL_CODE is also
'.'.
Example: 40 (School of Medicine)
Values:
Refer to the CNAC_CODES Table for values. The code
for a school or center is the same as the first 2
characters of the CNAC.
Source: PENNERA_PEOPLE.ERA_PRIMARY_ORG_SCH_CTR where PENNERA_PROPOSAL_INVESTIGATOR.PENN_ID = PENNERA_PEOPLE.PENN_ID
|
HOME_ORG_CODE
Indexed - yes
Format - char (4)
May be null? yes |
The 4-character code for the home organization (‘org.’)
for the investigator. The home org. is the org. that owns the person's
University employee record and is responsible for its maintenance.
HOME_ORG_CODE reflects the home org. of the investigator as it was at
the time of the proposal (funding cycle). Once the funding cycle has
ended, the HOME_ORG_CODE is no longer updated. The dates for the funding
cycle are the AWARDED_PROJECT_START and AWARDED_PROJECT_END in the PENNERA_PROPOSAL
record for the INSTITUTION_NO. (If the proposal was not awarded, the
dates for the funding cycle are the REQUESTED_PROJECT_START and REQUESTED_PROJECT_END
in the PENNERA_PROPOSAL record for the INSTITUTION_NO.) HOME_ORG_CODE
is '.' if the investigator has no University employee information dating
back to the time of the funding
cycle. (Because the HOME_ORG_CODE is one of the data elements
used to secure the data in other tables, it is set to '.' if it would
otherwise be null.)
Example: 4261 (DM-Rheumatology)
Values:
Refer to the ORG_CODES table for values.
Source: PENNERA_PEOPLE.HOME_ORG where PENNERA_PROPOSAL_INVESTIGATOR.PENN_ID = PENNERA_PEOPLE.PENN_ID
|
HOME_SCHOOL_CODE
Indexed - no
Format - char (2)
May be null? yes |
The 2-character code for the school or center for the HOME_ORG_CODE.
That is the home organization (‘org.’) for the investigator.
The home org. is the org. that owns the person's University employee
record and is responsible for its maintenance.
HOME_SCHOOL_CODE reflects the home school of the investigator as it
was at the time of the proposal (funding cycle). Once the funding cycle
has ended, the PI_HOME_SCHOOL_CODE is no longer updated. The dates for
the funding cycle are the AWARDED_PROJECT_START and AWARDED_PROJECT_END
in the PENNERA_PROPOSAL record for the INSTITUTION_NO. (If the proposal
was not awarded, the dates for the funding cycle are the REQUESTED_PROJECT_START
and REQUESTED_PROJECT_END in the PENNERA_PROPOSAL record for the INSTITUTION_NO.)
HOME_SCHOOL_CODE is '.' if the investigator
has no University employee information dating back to the time of the
funding cycle. (Because the HOME_ORG_CODE is one of the data elements
used to secure the data in other tables, it is set to '.' if it would
otherwise be null. If it is '.', it does not fall under any school or
center, so
HOME_SCHOOL_CODE
is also set to '.'.)
Example: 40 (School of Medicine)
Values:
Refer to the CNAC_CODES Table for values. The code
for a school or center is the same as the first 2
characters of the CNAC.
Source: PENNERA_PEOPLE.HOME_SCH_CTR where PENNERA_PROPOSAL_INVESTIGATOR.PENN_ID = PENNERA_PEOPLE.PENN_ID
|
INSTITUTION_NO
Indexed - yes
Format - char (8)
May be null? yes |
The eight-digit sequence number used by the University to uniquely identify
the proposal with which the investigator is associated. Note that the value
for INSTITUTION_NO includes leading zeroes.
Records with an INSTITUTION_NO less than or equal to 05017400 were converted
from the Research Services System (RSS, the system used to track proposals
and awards before October 14, 2003). Other records are for proposals
created in PennERA.
There are two different unique identifiers for a proposal: INSTITUTION_NO
and PROP_NO. INSTITUTION_NO is used by the University. PROP_NO is used
internally by the PennERA system. For a given record, INSTITUTION_NO
will not have the same value as PROP_NO. While either one may be used
when joining PennERA Proposals tables, INSTITUTION_NO is the one recommended
for display in reports.
There is one PENNERA_PROPOSAL_INVESTIGATOR record per proposal (INSTITUTION_NO
or PROP_NO) per investigator (PENN_ID or UNIQUE_ID) per INVESTIGATOR_ORG_CODE
per PI_FLAG.
When including investigators' names in reports, note that multiple records
will be returned for a given proposal if there are any co-investigators
for the proposal, or if the person who is the Principal Investigator
(PI) is also associated with the proposal in a different role. To list
only one investigator's name for the proposal, ask for information only
for the PI (PI_FLAG='Y').
Example: 00216424
Values:
List of values not available
PennERA source: Proposal Tracking module; any screen for the proposal;
summary box in the upper right corner of the screen; Proposal
|
INVESTIGATOR_ORG_CODE
Indexed - yes
Format - char (4)
May be null? yes |
The 4-character code for the investigator organization (‘org.’)
for the investigator. Although it is used to secure certain data elements
in the Data Warehouse, the investigator org. is not used by the University’s
business processes.
For a given investigator associated with a proposal, the investigator
org. is the org. specified (on the Office of Research Services Proposal
Transmittal and Approval Form) as the 'Dept. Administering Project' (the
PROPOSAL_RESP_ORG_CODE). An exception to this rule is that, if the PROPOSAL_RESP_ORG_CODE
is neither the investigator's ERA primary org. nor an org. where the
investigator has a job appointment, when the investigator's data is entered
for the proposal in PennERA, the investigator org. is chosen from among
those orgs., based on its being the one that is most closely related
to the PROPOSAL_RESP_ORG_CODE. In other words:
- if the investigator has only one org, then that org. is chosen; otherwise,
- if the investigator has an org. that is the same as the PROPOSAL_RESP_ORG_CODE,
then that org. is chosen; otherwise
- the investigator's orgs. are sorted in ascending order, and the first
org. whose org. code is greater than or equal to the PROPOSAL_RESP_ORG_CODE
is chosen; otherwise,
- if the investigator has no org. whose org. code is greater than or
equal to the PROPOSAL_RESP_ORG_CODE, the investigator's ERA primary
org. is chosen
There is one PENNERA_PROPOSAL_INVESTIGATOR record per proposal (INSTITUTION_NO
or PROP_NO) per investigator (PENN_ID or UNIQUE_ID) per INVESTIGATOR_ORG_CODE
per PI_FLAG. Note that a proposal may have more than one PENNERA_PROPOSAL_INVESTIGATOR
record for a given investigator if that investigator had more than
one investigator org. over the course of that proposal (project period).
For example,
say that Dr. Jones is a co-investigator for a proposal, and has had only
one job appointment per budget period. He
worked
for
Otorhinolaryncology
during
the first period of the proposal, at which time the proposal had only
one PENNERA_PROPOSAL_INVESTIGATOR record for him. He worked
for Neuroscience during the
second period of the proposal. As soon as his
information
was entered
for the
second period, the proposal had two PENNERA_PROPOSAL_INVESTIGATOR records
for him.
To see each investigator just once per proposal, use SELECT
DISTINCT, and do not use the INVESTIGATOR_ORG_CODE or the PI_FLAG. (Business
Objects users: to SELECT DISTINCT, when editing the Data Provider, click
the Options button, and select the No Duplicate Rows radio button.)
To facilitate Organization-based security in other tables, INVESTIGATOR_ORG_CODE
is set to '.' if it would otherwise be null.
Example: 4261 (DM-Rheumatology)
Values:
Refer to the ORG_CODES table for values.
PennERA source: Proposal Tracking module; Summary screen for the proposal;
Investigator tab; the code in the Department column for the person flagged
as the PI
|
INVESTIGATOR_SCHOOL_CODE
Indexed - no
Format - char (2)
May be null? yes |
The 2-character code for the school or center for the INVESTIGATOR_ORG_CODE.
That is the investigator
organization (‘org.’) for the investigator. Although it is
used to secure certain data elements in the Data Warehouse, the investigator
org. is not used by the University’s business
processes.
If the INVESTIGATOR_ORG_CODE is '.', the INVESTIGATOR_SCHOOL_CODE
is also '.'.
Example: 40 (School of Medicine)
Values:
Refer to the CNAC_CODES Table for values. The code
for a school or center is the same as the first 2
characters of the CNAC.
Source: ORG_CODES.CENTER_CODE where PENNERA_PROPOSAL_INVESTIGATOR.INVESTIGATOR_ORG_CODE
=ORG_CODES.ORGANIZATION_CODE |
LAST_EXTRACT_DATE
Indexed - no
Format - date
May be null? yes |
The date this proposal investigator record was extracted from the PennERA
Proposal Tracking system and loaded into the Warehouse.
Example: 10/28/2003
Values:
List of values not available |
NAME
Indexed - no
Format - varchar2 (52)
May be null? yes |
The name of the investigator. The name may be up to 52 characters long,
and is stored in uppercase in the format ‘LASTNAME,FIRSTNAME I’ (with
no spaces around the comma, and no period after the middle initial).
NAME reflects the investigator’s name as it was at the time of
the proposal (funding cycle). Once the funding cycle has ended, the NAME
is no longer updated. The dates for the funding cycle are the AWARDED_PROJECT_START
and AWARDED_PROJECT_END in the PENNERA_PROPOSAL record for the INSTITUTION_NO.
(If the proposal was not awarded, the dates for the funding cycle are
the REQUESTED_PROJECT_START and REQUESTED_PROJECT_END in the PENNERA_PROPOSAL
record for the INSTITUTION_NO.)
The value for NAME is usually--but not always--stored in upper case.
Allow for both upper and lower case when setting record selection conditions
on NAME.
Example: ‘GAULTON,GLEN N’
Values:
Refer to the PENNERA_PEOPLE Table for values.
PennERA source: Proposal Tracking module; Summary screen for the proposal;
Investigator tab; the Name column for the row for the person. Note that
the name may be displayed in a format that is different from the one
used in the PENNERA_PROPOSAL_INVESTIGATOR table.
|
PENN_ID
Indexed - yes
Format - char (8)
May be null? no |
The Penn ID of the investigator associated with the proposal.
The PennID is the 8-digit identification number assigned to a person
by the Penn Community system. No two persons have the same PennID. However,
a person may have more than one PennID. For example, say Dr. John Doe
had a sponsored project in 1990, but his Social Security number was not
recorded in the Research Services System (RSS, the system then used to
track proposals and awards). Dr. Doe left the University after project
ended. When the RSS data was converted to PennERA, there was no record
in Penn Community for Dr. Doe. Because his Social Security number was
not available, a PennERA-specific PennID (beginning with a ‘P’)
was created for him. In 2003, Dr. Doe returned to the University, and
was assigned a numeric PennID (for example, 10039706). Dr. Doe would
then have two PennIDs, but one could not tell from looking at
the data that both PennIDs identify the same person.
There are two different unique identifiers for an investigator: PENN_ID
and UNIQUE_ID. PENN_ID is the identification number assigned by the University.
UNIQUE_ID is the identification number used internally by the PennERA
system. For a given record, PENN_ID will not have the same value as UNIQUE_ID.
PENN_ID is the investigator identifier that is recommended for use by
those querying the PENNERA_PROPOSAL_INVESTIGATOR table; UNIQUE_ID is
not recommended.
There is one PENNERA_PROPOSAL_INVESTIGATOR record per proposal (INSTITUTION_NO
or PROP_NO) per investigator (PENN_ID or UNIQUE_ID), per INVESTIGATOR_ORG_CODE
per PI_FLAG. To see each investigator just once per proposal, use SELECT
DISTINCT, and do not use the INVESTIGATOR_ORG_CODE or the PI_FLAG. (Business
Objects users: to SELECT DISTINCT, when editing the Data Provider, click
the Options button, and select the No Duplicate Rows radio button.)
Examples: ‘P1000194’ (Lisa Jay); ‘10006336’ (Herman
Gluck)
Values:
Refer to the PENNERA_PEOPLE Table for values.
PennERA source: Proposal Tracking module; Summary screen for the proposal;
Investigator tab; the PennID for the person flagged as the PI (The PennID
itself is not displayed.) |
PI_FLAG
Indexed - yes
Format - char (1)
May be null? no |
A Yes/No flag indicating whether the investigator is the Principal Investigator
(PI) for the proposal.
There is one PENNERA_PROPOSAL_INVESTIGATOR record per proposal (INSTITUTION_NO
or PROP_NO) per investigator (PENN_ID or UNIQUE_ID) per INVESTIGATOR_ORG_CODE
per PI_FLAG. Note that, if a person is the Principal Investigator
(PI) for a given proposal and is also associated with that proposal in
a different role, that proposal
will have multiple PENNERA_PROPOSAL_INVESTIGATOR records for that person
(one as the PI and one as a non-PI). For instance, a proposal might have
two
PENNERA_PROPOSAL_INVESTIGATOR
records for Dr. Smith -- one where PI_FLAG = 'Y' and one where PI_FLAG
= 'N' -- because Dr. Smith is both the PI and the statistician for the
proposal.
To see each investigator just once per proposal, use SELECT DISTINCT,
and do not use the INVESTIGATOR_ORG_CODE or the PI_FLAG. (Business
Objects users: to SELECT DISTINCT, when editing the Data Provider, click
the Options button, and select the No Duplicate Rows radio button.)
Values:
Y yes, the investigator is the Principal Investigator
for the proposal
N no, the investigator is not the Principal
Investigator for the proposal; the investigator is a
co-investigator
PennERA source: Proposal Tracking module; Summary screen; Investigator
tab; the PI column for the row for the investigator |
PRIMARY_ACADEMIC_FACULTY CLASS
Indexed - yes
Format - char (3)
May be null? yes |
The 3-character code that indicates the faculty classification for
the investigator’s primary academic job appointment at the University.
The primary academic job appointment is determined per the University’s
employee census logic. For information on how the primary academic appointment
is determined if the person has more than one academic appointment at
the University, see the explanation in the Cautions section of the documentation
for the EMPLOYEE_GENERAL Table.
PRIMARY_ACADEMIC_FACULTY_CLASS reflects the faculty classification for
the investigator’s primary academic appointment, as it was at the
time of the proposal (funding cycle). Once the funding cycle has ended,
the PRIMARY_ACADEMIC_FACULTY_CLASS is no longer updated. The dates for
the funding cycle are the AWARDED_PROJECT_START and AWARDED_PROJECT_END
in the PENNERA_PROPOSAL record for the INSTITUTION_NO. (If the proposal
was not awarded, the dates for the funding cycle are the REQUESTED_PROJECT_START
and REQUESTED_PROJECT_END in the PENNERA_PROPOSAL record for the INSTITUTION_NO.)
If the investigator has no employee information about an academic job appointment
dating back to the time of the funding cycle, PRIMARY_ACADEMIC_FACULTY_CLASS
is null.
Examples: TEN (tenure track, including tenure of title and standing
faculty); VIS (visiting faculty)
Values:
TEN Tenure Track (includes tenure of title and
standing faculty): Job Class = 216000-216010,
216020
PRE Pre-Tenure Track: Job Class = 206047
CLE Clinician Educator: Job Class = 226000,
226010, 226020
RES Research Faculty: Job Class = 266000,
266010, 266020
CLN Clinical Faculty: Job Class = 256000,
256010, 256020
ADJ Adjunct Faculty: Job Class = 236000,
236010, 236020
VIS Visiting Faculty: Job Class = 246000,
246010, 246020
VEP Visiting Executive Professor : Job
Class = 246050
PRF Practice Professor: Job Class =
296000
WIS Wistar Faculty: Job Class = 276000,
276010, 276020
SUP Support Faculty: Job Title = LECTURER
or RESEARCH ASSOCIATE; or Job Class =
206040, 216040, 206015, 256030, 206006
POS Post-Docs: Job Title = POST-D
GRP Grad/Prof Students: Job Class = 206050;
or Job Title = TEACHING A, RESEARCH FELLOW,
RESEARCH ASSISTANT, EDUC FLWSHP,
PRE-DOCTORAL
EMF Emeritus Faculty: Job Title = EMERITUS
UNC Unclassified: Everything else
Source: JOB_CLASS. FACULTY_CLASS where PENNERA_PROPOSAL_INVESTIGATOR.PRIMARY_ACADEMIC_JOB_CLASS
= JOB_CLASS.JOB_CLASS
|
PRIMARY_ACADEMIC_JOB_CLASS
Indexed - yes
Format - char (6)
May be null? yes |
The 6-digit code indicating the job classification for the investigator’s
primary academic job appointment at the University. (Refer to PRIMARY_ACADEMIC_JOB_TITLE
to find the title which corresponds to the PRIMARY_ACADEMIC_JOB_CLASS.)
The primary academic job appointment is determined per the University’s
employee census logic. For information on how the primary academic appointment
is determined if the person has more than one academic appointment at
the University, see the explanation in the Cautions section of the documentation
for the EMPLOYEE_GENERAL Table.
PRIMARY_ACADEMIC_JOB_CLASS reflects the investigator’s primary
academic appointment as it was at the time of the proposal (funding cycle).
Once the funding cycle has ended, the PRIMARY_ACADEMIC_JOB_CLASS is no
longer updated. The dates for the funding cycle are the AWARDED_PROJECT_START
and AWARDED_PROJECT_END in the PENNERA_PROPOSAL record for the INSTITUTION_NO.
(If the proposal was not awarded, the dates for the funding cycle are
the REQUESTED_PROJECT_START and REQUESTED_PROJECT_END in the PENNERA_PROPOSAL
record for the INSTITUTION_NO.) If the investigator has no employee information
about an academic job appointment dating back to the time of the funding
cycle, PRIMARY_ACADEMIC_JOB_CLASS is null.
Example: 226010 (Associate Professor C-E)
Values:
Refer to the JOB_CLASS table for values.
Source: EMPLOYEE_GENERAL.PRI_ACAD_APPT_JOB_CLASS where PENNERA_PROPOSAL_INVESTIGATOR.PENN_ID = EMPLOYEE_GENERAL.PENN_ID |
PRIMARY_ACADEMIC_JOB_TITLE
Indexed - yes
Format - varchar2 (25)
May be null? yes |
The investigator’s title, per his or her primary academic job appointment
at the University.
The primary academic job appointment is determined per the University’s
employee census logic. For information on how the primary academic appointment
is determined if the person has more than one academic appointment at
the University, see the explanation in the Cautions section of the documentation
for the EMPLOYEE_GENERAL Table.
PRIMARY_ACADEMIC_JOB_TITLE reflects the investigator’s primary
academic appointment as it was at the time of the proposal (funding cycle).
Once the funding cycle has ended, the PRIMARY_ACADEMIC_JOB_TITLE is no
longer updated. The dates for the funding cycle are the AWARDED_PROJECT_START
and AWARDED_PROJECT_END in the PENNERA_PROPOSAL record for the INSTITUTION_NO.
(If the proposal was not awarded, the dates for the funding cycle are
the REQUESTED_PROJECT_START and REQUESTED_PROJECT_END in the PENNERA_PROPOSAL
record for the INSTITUTION_NO.) If the investigator has no employee information
about an academic job appointment dating back to the time of the funding
cycle, PRIMARY_ACADEMIC_JOB_TITLE is null.
Example: ASSOCIATE PROFESSOR C-E (226010)
Values:
Refer to the JOB_CLASS table for values.
Source: JOB_CLASS. JOB_TITLE where PENNERA_PROPOSAL_INVESTIGATOR.PRIMARY_ACADEMIC_JOB_CLASS
= JOB_CLASS.JOB_CLASS
|
PRIMARY_ACADEMIC_ORG_CODE
Indexed - yes
Format - char (4)
May be null? yes |
The 4-character code for the organization (‘org.’) where
the investigator has his or her primary academic job appointment at the
University. The primary academic job appointment is determined
per the University’s
employee census logic. For information on how the primary academic appointment
is determined if the person has more than one academic appointment at
the University, see the explanation in the Cautions section of the documentation
for the EMPLOYEE_GENERAL Table.
PRIMARY_ACADEMIC_ORG_CODE reflects the primary academic appointment
org. of the PI for the proposal, as it was at the time of the proposal
(funding cycle). Once the funding cycle has ended, the PRIMARY_ACADEMIC_ORG_CODE
is no longer updated. The dates for the funding cycle are the AWARDED_PROJECT_START
and AWARDED_PROJECT_END in the PENNERA_PROPOSAL record for the INSTITUTION_NO.
(If the proposal was not awarded, the dates for the funding cycle are
the REQUESTED_PROJECT_START and REQUESTED_PROJECT_END in the PENNERA_PROPOSAL
record for the INSTITUTION_NO.) PRIMARY_ACADEMIC_ORG_CODE is '.' if the
investigator has no University employee information about an academic
job appointment dating back to the time of
the funding
cycle. (Because the PRIMARY_ACADEMIC_ORG_CODE is one of the data elements
used to secure the data in other tables, it is set to '.' if it would
otherwise be null.)
Example: 4261 (DM-Rheumatology)
Values:
Refer to the ORG_CODES table for values.
Source: PENNERA_PEOPLE.PRI_ACAD_ORG where PENNERA_PROPOSAL_INVESTIGATOR.PENN_ID = PENNERA_PEOPLE.PENN_ID
|
PRIMARY_ACADEMIC_SCHOOL_CODE
Indexed - yes
Format - char (2)
May be null? yes |
The 2-character code for the school or center for the PRIMARY_ACADEMIC_ORG_CODE.
That is the organization (‘org.’) where
the investigator has his or her primary academic job appointment at the
University.
The primary academic job appointment is determined per the University’s
employee census logic. For information on how the primary academic appointment
is determined if the person has more than one academic appointment at
the University, see the explanation in the Cautions section of the documentation
for the EMPLOYEE_GENERAL Table.
PRIMARY_ACADEMIC_SCHOOL_CODE reflects the primary academic appointment
school of the investigator as it was at the time of the proposal (funding
cycle). Once the funding cycle has ended, the PRIMARY_ACADEMIC_SCHOOL_CODE
is no longer updated. The dates for the funding cycle are the AWARDED_PROJECT_START
and AWARDED_PROJECT_END in the PENNERA_PROPOSAL record for the INSTITUTION_NO.
(If the proposal was not awarded, the dates for the funding cycle are
the REQUESTED_PROJECT_START and REQUESTED_PROJECT_END in the PENNERA_PROPOSAL
record for the INSTITUTION_NO.) PRIMARY_ACADEMIC_SCHOOL_CODE is '.'
if the investigator has no University employee information about an
academic job appointment dating back to the time
of the funding cycle. (Because the PRIMARY_ACADEMIC_ORG_CODE is one
of the data elements used to secure the data in other tables, it is
set to '.' if it would otherwise be null. If it is '.', it does not fall
under
any
school or center, so PRIMARY_ACADEMIC_SCHOOL_CODE is also set to '.'.)
Example: 40 (School of Medicine)
Values:
Refer to the CNAC_CODES Table for values. The code
for a school or center is the same as the first 2
characters of the CNAC.
Source: PENNERA_PEOPLE.PRI_ACAD_SCH_CTR where PENNERA_PROPOSAL_INVESTIGATOR.PENN_ID = PENNERA_PEOPLE.PENN_ID
|
PRIMARY_APPT_JOB_CLASS
Indexed - yes
Format - char (6)
May be null? yes |
The 6-digit code indicating the job classification for the investigator’s
primary job appointment at the University. (Refer to PRIMARY_APPT_JOB_TITLE
to find the title which corresponds to the PRIMARY_APPT_JOB_CLASS.)
The primary job appointment is determined per the University’s
employee census logic. For information on how the primary appointment
is determined if the person has more than one job appointment at the
University, see the explanation in the Cautions section of the documentation
for the EMPLOYEE_GENERAL Table.
PRIMARY_APPT_JOB_CLASS reflects the primary appointment org. of the
investigator as it was at the time of the proposal (funding cycle). Once
the funding cycle has ended, the PRIMARY_APPT_JOB_CLASS is no longer
updated. The dates for the funding cycle are the AWARDED_PROJECT_START
and AWARDED_PROJECT_END in the PENNERA_PROPOSAL record for the INSTITUTION_NO.
(If the proposal was not awarded, the dates for the funding cycle are
the REQUESTED_PROJECT_START and REQUESTED_PROJECT_END in the PENNERA_PROPOSAL
record for the INSTITUTION_NO.) If the investigator has no employee information
about a job appointment dating back to the time of the funding cycle,
PRIMARY_APPT_JOB_CLASS is null.
Example: 226010 (Associate Professor C-E)
Values:
Refer to the JOB_CLASS table for values.
Source: EMPLOYEE_GENERAL.PRIMARY_APPT_JOB_CLASS where PENNERA_PROPOSAL_INVESTIGATOR.PENN_ID = EMPLOYEE_GENERAL.PENN_ID
|
PRIMARY_APPT_JOB_TITLE
Indexed - yes
Format - varchar2 (25)
May be null? yes |
The investigator’s title, per his or her primary job appointment
at the University.
The primary job appointment is determined per the University’s
employee census logic. For information on how the primary appointment
is determined if the person has more than one job appointment at the
University, see the explanation in the Cautions section of the documentation
for the EMPLOYEE_GENERAL Table
PRIMARY_APPT_JOB_TITLE reflects the investigator’s primary appointment
as it was at the time of the proposal (funding cycle). Once the funding
cycle has ended, the PRIMARY_APPT_JOB_TITLE is no longer
updated. The dates for the funding cycle are the AWARDED_PROJECT_START
and AWARDED_PROJECT_END in the PENNERA_PROPOSAL record for the INSTITUTION_NO.
(If the proposal was not awarded, the dates for the funding cycle are
the REQUESTED_PROJECT_START and REQUESTED_PROJECT_END in the PENNERA_PROPOSAL
record for the INSTITUTION_NO.) If the investigator has no employee information
about a job appointment dating back to the time of the funding cycle,
PRIMARY_APPT_JOB_TITLE is null.
Example: ASSOCIATE PROFESSOR C-E (226010)
Values:
Refer to the JOB_CLASS table for values.
Source: Source: JOB_CLASS. JOB_TITLE where PENNERA_PROPOSAL_INVESTIGATOR.PRIMARY_APPT_JOB_CLASS
= JOB_CLASS.JOB_CLASS
|
PRIMARY_APPT_ORG_CODE
Indexed - yes
Format - char (4)
May be null? yes |
The 4-character code for the organization (‘org.’) where
the investigator has his or her primary job appointment at the University.
The primary job appointment is determined per the University’s
employee census logic. For information on how the primary appointment
is determined if the person has more than one job appointment at the
University, see the explanation in the Cautions section of the documentation
for the EMPLOYEE_GENERAL Table
PRIMARY_APPT_ORG_CODE reflects the primary appointment org. of the investigator
as it was at the time of the proposal (funding cycle). Once the funding
cycle has ended, the PRIMARY_APPT_ORG_CODE is no longer updated. The
dates for the funding cycle are the AWARDED_PROJECT_START and AWARDED_PROJECT_END
in the PENNERA_PROPOSAL table. (If the proposal was not awarded, the
dates for the funding cycle are the REQUESTED_PROJECT_START and REQUESTED_PROJECT_END
in the PENNERA_PROPOSAL table.) PRIMARY_APPT_ORG_CODE
is '.' if the investigator has no University employee information dating
back to the time of the funding
cycle. (Because the PRIMARY_APPT_ORG_CODE is one of the data elements
used to secure the data in other tables, it is set to '.' if it would
otherwise be null.)
Example: 4261 (DM-Rheumatology) Values:
Refer to the ORG_CODES table for values.
Source: PENNERA_PEOPLE.PRI_APPT_ORG where PENNERA_PROPOSAL_INVESTIGATOR.PENN_ID = PENNERA_PEOPLE.PENN_ID |
PRIMARY_APPT_SCHOOL_CODE
Indexed - yes
Format - char (4)
May be null? yes |
The 2-character code for the school or center for the PRIMARY_APPT_ORG_CODE.
That is the organization (‘org.’) where
the investigator has his or her primary job appointment at the University. The primary job appointment is determined per the University’s
employee census logic. For information on how the primary appointment
is determined if the person has more than one job appointment at the
University, see the explanation in the Cautions section of the documentation
for the EMPLOYEE_GENERAL Table
PRIMARY_APPT_SCHOOL_CODE reflects the primary appointment school of
the investigator as it was at the time of the proposal (funding cycle).
Once the funding cycle has ended, the PRIMARY_APPT_SCHOOL_CODE is no
longer updated. The dates for the funding cycle are the AWARDED_PROJECT_START
and AWARDED_PROJECT_END in the PENNERA_PROPOSAL table. (If the proposal
was not awarded, the dates for the funding cycle are the REQUESTED_PROJECT_START
and REQUESTED_PROJECT_END in the PENNERA_PROPOSAL table.) PRIMARY_APPT_SCHOOL_CODE
is '.' if the investigator has no University employee information dating
back to the time of the funding
cycle. (Because the PRIMARY_APPT_ORG_CODE is one of the data elements
used to secure the data in other tables, it is set to '.' if it would
otherwise be null. If it is '.', it does not fall under any
school or center, so PRIMARY_APPT_SCHOOL_CODE is also set to '.'.)
Example: 40 (School of Medicine)
Values:
Refer to the CNAC_CODES Table for values. The code
for a school or center is the same as the first 2
characters of the CNAC.
Source: PENNERA_PEOPLE.PRI_APPT_SCH_CTR where PENNERA_PROPOSAL_INVESTIGATOR.PENN_ID = PENNERA_PEOPLE.PENN_ID
|
PROP_NO
Indexed - yes
Format - varchar2 (20)
May be null? no |
The 10-digit sequence number used internally by the PennERA system to
uniquely identify the proposal with which the investigator is associated.
Note that the value for PROP_NO includes leading zeroes.
There are two different unique identifiers for a proposal: INSTITUTION_NO
and PROP_NO. INSTITUTION_NO is used by the University. PROP_NO is used
internally by the PennERA system. For a given record, INSTITUTION_NO
will not have the same value as PROP_NO. While either one may be used
when joining PennERA Proposals tables, INSTITUTION_NO is the one recommended
for display in reports.
There is one PENNERA_PROPOSAL_INVESTIGATOR record per proposal (INSTITUTION_NO
or PROP_NO) per investigator (PENN_ID or UNIQUE_ID), per INVESTIGATOR_ORG_CODE
per PI_FLAG.
When including investigators' names in reports, note that
multiple records will be returned for a given proposal if there are
any co-investigators
for the proposal, or if the person who is the Principal Investigator
(PI) is also associated with the proposal in a different role. To list
only one investigator's name for the proposal, ask for information
only for the PI (PI_FLAG='Y').
Example: ‘0000000217’
Values:
List of values not available
PennERA source: Proposal Tracking module; any screen for the proposal;
summary box in the upper right corner of the screen; the prop_no for
the Proposal (Proposal is the institution number; prop_no is not shown)
|
UNIQUE_ID
Indexed - yes
Format - varchar2 (40)
May be null? no |
The 15-character used internally by the PennERA system to uniquely
identify the investigator associated with the proposal.
There are two different unique identifiers for an investigator: PENN_ID
and UNIQUE_ID. PENN_ID is the identification number assigned by the University.
UNIQUE_ID is the identification number assigned by, and used internally
by, the PennERA system. For a given record, PENN_ID does not have the
same value as UNIQUE_ID.
PENN_ID is the investigator identifier that is recommended for use by
those querying the PENNERA_PROPOSAL_INVESTIGATOR table; UNIQUE_ID is
not recommended.
The substring of a person's UNIQUE_ID from character 7 through
14 is usually, but not always, the same as the person's PENN_ID. The
numeric portion of a person's UNIQUE_ID is never the person's Social
Security Number.
There is one PENNERA_PROPOSAL_INVESTIGATOR record per proposal (INSTITUTION_NO
or PROP_NO) per investigator (PENN_ID or UNIQUE_ID), per INVESTIGATOR_ORG_CODE
per PI_FLAG. To see each investigator just once per proposal, use SELECT
DISTINCT, and do not use the INVESTIGATOR_ORG_CODE or the PI_FLAG. (Business
Objects users: to SELECT DISTINCT, when editing the Data Provider, click
the Options button, and select the No Duplicate Rows radio button.)
Example: ‘UPENN-100424239’ (Glen N. Gaulton)
Values:
List of values not available
PennERA source: Proposal Tracking module; Summary screen for the proposal;
Investigator tab; the Unique_ID for the person listed as an investigator
(The Unique_ID itself is not displayed.)
|
Questions about this page? Email us at da-staff@isc.upenn.edu
|