Penn Computing

University of Pennsylvania
Penn Computing << go backback
PENNERA_PROPOSAL_INVESTIGATOR Table   Tables and Data Elements   PennERA Proposals Home   Data Warehouse Home

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

 

PENNERA_PROPOSAL_INVESTIGATOR Table   Tables and Data Elements  PennERA Proposals 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