PENNERA_PROPOSAL_INVESTIGATOR Table - Data Element Index   Tables and Data Elements   PennERA Proposals Home   Data Warehouse Home

PENNERA_PROPOSAL_INVESTIGATOR Table

DWADMIN Schema

Explanation
Identifies each investigator for each proposal (funding cycle) for a sponsored project, and records the investigator's status as it was at the time of the funding cycle. If the proposal has been awarded, once the awarded project end date for the proposal has passed, the associated PENNERA_PROPOSAL_INVESTIGATOR records are no longer updated. For proposals that were not awarded, once the requested project end date for the proposal has passed, the associated PENNERA_PROPOSAL_INVESTIGATOR records are no longer updated. (For current information on an investigator, see the PENNERA_PEOPLE table. If the investigator is an employee of the University, current information is also available in the EMPLOYEE_GENERAL table.)

There is one PENNERA_PROPOSAL_INVESTIGATOR record per proposal (funding cycle), per person, per investigator organization ("org."), per role (Principal Investigator or non-Principal Investigator). For example:

• If a proposal lists four different people (one as the Principal Investigator, and three as co-investigators), that proposal will have four PENNERA_PROPOSAL_INVESTIGATOR records.
• Over the course of a proposal (project period), a person may be associated with the proposal more than once, with different investigator orgs. In such a case, the proposal's PENNERA_PROPOSAL_INVESTIGATOR records for that person will include one for each of that person's investigator orgs. For instance, a proposal might have two PENNERA_PROPOSAL_INVESTIGATOR records for Dr. Smith -- one for Otorhinolaryncology and one for Neuroscience -- because, although Dr. Smith had just one job during the project period, at the beginning, his job was in Otorhinolaryncology, but in the middle of the project period, he transferred to Neuroscience.
• When a person is the Principal Investigator (PI) for a proposal, and is also associated with the proposal in a different role, that person will have one PENNERA_PROPOSAL_INVESTIGATOR record as the PI for that proposal and one as a non-PI for that proposal. For instance, a proposal might have two PENNERA_PROPOSAL_INVESTIGATOR records for Dr. Jones -- one where PI_FLAG = 'Y' and one where PI_FLAG = 'N' -- because Dr. Jones is both the PI and the statistician for the proposal.

Common Uses

• Sorting information related to a proposal by the name of the Principal Investigator.
• Finding the Principal Investigator and the co-investigators who worked on a particular project.
• Reporting what an investigator's name, job appointment, or department (organization) was at the time of the grant or contract.
  

PROP_NO
UNIQUE_ID
INVESTIGATOR_ORG_CODE
PI_FLAG

HOME_ORG_CODE
INSTITUTION_NO
INVESTIGATOR_ORG_CODE
PENN_ID
PI_FLAG
PRIMARY_ACADEMIC_FACULTY_CLASS
PRIMARY_ACADEMIC_JOB_CLASS
PRIMARY_ACADEMIC_JOB_TITLE
PRIMARY_ACADEMIC_ORG_CODE
PRIMARY_APPT_JOB_CLASS
PRIMARY_APPT_JOB_TITLE
PRIMARY_APPT_ORG_CODE
PROP_NO

PENNERA_INCREMENT
PENNERA_PEOPLE
PENNERA_PERIOD
PENNERA_PERIOD_STAT_HIST
PENNERA_PROP_REGULATORY_APPR
PENNERA_PROP_STAT_HIST
PENNERA_PROPOSAL
PENNERA_PROPOSAL_ACTIVITY
PENNERA_PROPOSAL_PARENT
PENNERA_REQUEST

Cautions

• There are two different unique identifiers for a proposal (funding cycle): INSTITUTION_NO and PROP_NO. INSTITUTION_NO is the identification number assigned by the University. PROP_NO is the identification number 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.
• 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 an investigator: PENN_ID and UNIQUE_ID. PENN_ID is the identification number assigned by the University. UNIQUE_ID is the identification code 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.
• Note that a proposal may have one or more associated PENNERA_PROPOSAL_INVESTIGATOR records. That is, there may be one or more PENNERA_PROPOSAL_INVESTIGATOR records whose INSTITUTION_NO matches the INSTITUTION_NO in the PENNERA_PROPOSAL table. (For Business Objects users, this specification is included in the Universe.)
  • Each proposal ought to have exactly one PENNERA_PROPOSAL_INVESTIGATOR record with information on the Principal Investigator (PI_FLAG='Y').
    • One way to get information on the Principal Investigator (PI) for the proposal is to specify that the INSTITUTION_NO (for the proposal, period, request, or increment) must match the INSTITUTION_NO in the PENNERA_PROPOSAL_INVESTIGATOR table, where PI_FLAG='Y'.
    • Another way to get information on the PI for the proposal is to specify that the INSTITUTION_NO and PI_PENN_ID (for the proposal, period, request, or increment) must match the INSTITUTION_NO and PENN_ID in the PENNERA_PROPOSAL_INVESTIGATOR table, where PI_FLAG='Y'.
    • Be sure to specify PI_FLAG='Y', in case the person who is the PI is associated with the proposal more than once (with different investigator orgs. and/or with different roles).
  • A proposal may have any number of records (including zero records) with information on the co-investigator(s), if any (PI_FLAG='N').
    • If the proposal record was created in PennERA via the SOMERA Interface, the proposal initially has one co-investigator at most. (SOMERA is the School of Medicine’s Electronic Research Administration system.) Information on other co-investigators for the proposal may be entered later by the Office of Research Services.
    • Note that a proposal may have more than one PENNERA_PROPOSAL_INVESTIGATOR record for a given investigator if that investigator is associated with the proposal more than once (with different investigator orgs. and/or with different PI_FLAGs). 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.)
    • 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 avoid having multiple records returned, ask for information for only one investigator, such as the PI (PI_FLAG='Y').
• If a proposal is for a sub-project--for example, if it is for a project that is part of a program project--the proposal for the program project is termed the 'parent proposal'. In addition to its Principal Investigator, the sub-project proposal will also have exactly one PENNERA_PROPOSAL_INVESTIGATOR record with information on the Principal Investigator for the parent proposal. Note: data for parent proposals is not currently entered in PennERA. The PennERA Proposals Data Collection has been designed to accommodate that data when it becomes available. To get information on the Principal Investigator for the parent proposal, specify that the INSTITUTION_NO and PARENT_PI_PENN_ID (for the proposal, period, request, or increment) must match the INSTITUTION_NO and PENN_ID in the PENNERA_PROPOSAL_INVESTIGATOR table, respectively, where PI_FLAG='Y'.
• An investigator is linked to a proposal, not to a project. If a project spans two funding cycles, it will have two PENNERA_PROPOSAL records, and each of those records will have its own associated PENNERA_PROPOSAL_INVESTIGATOR record(s).
• An Organization ("org.") is a subdivision of the University created for management purposes.
  • To facilitate Organization-based security in other tables, the PENNERA_PROPOSAL_INVESTIGATOR data elements that store information on orgs. have their values set to '.' if they would otherwise be null.
    • If a data element for an org. is '.', the corresponding school/center data element is also '.'.
  • When reporting on orgs., be sure to select the data element that meets your needs. (Note that for every organization element, the PENNERA_PROPOSAL_INVESTIGATOR table also stores the associated school/center element.)
    • The INVESTIGATOR_ORG_CODE is determined when the investigator's data is entered for the proposal in PennERA. It is either the same as the PROPOSAL_RESP_ORG_CODE (for the proposal, period, request, or increment), or (roughly speaking) the org. with which the investigator is affiliated that is closest to the PROPOSAL_RESP_ORG_CODE. (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.)
    • The following data elements store the org. information for the investigator as it was at the time of the proposal (funding cycle). Once the funding cycle has ended, this org. information 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 HOME_ORG_CODE identifies the org. that owns the investigator's University employee record and is responsible for its maintenance. The value of this data element is '.' if the investigator has no University employee information dating back to the time of the funding cycle.
      • The PRIMARY_APPT_ORG_CODE identifies the org. where the investigator has his or her primary job appointment at the University. (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 is '.' if the investigator has no University employee information dating back to the time of the funding cycle.
      • The PRIMARY_ACADEMIC_ORG_CODE identifies the org. where the investigator has his or her primary academic job appointment at the University. (For information on how the primary academic appointment is determined if the person has more than one academic job appointment at the University, see the explanation in the Cautions section of the documentation for the EMPLOYEE_GENERAL table.) 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.
      • The ERA_PRIMARY_ORG_CODE identifies the org. flagged as the primary department in the investigator'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.)
• Most investigators are University employees. Some investigators have more than one job appointment at the University. While the PENNERA_PROPOSAL_INVESTIGATOR table does not store information on every University job appointment held by the investigator, it does store the job class, job title, org., and school/center for the investigator's primary appointment. If the investigator has at least one academic appointment at the University, the table also stores the job class, job title, faculty class, org., and school/center for the investigator's primary academic appointment. If the investigator has an academic appointment at the University, and has only one job, the information about the primary appointment and the primary academic appointment is the same. For details on how a person's primary and primary academic appointments are determined if the person has more than one job appointment at the University, go to the documentation for the EMPLOYEE_GENERAL table, and scroll down to the relevant bullet point under Cautions.
• PENNERA_PROPOSAL_INVESTIGATOR stores the investigator's name, job appointment, and department (org.) data as it was at the time of the proposal (funding cycle). PENNERA_PEOPLE, on the other hand, stores the most recent data for the investigator. If the end date for the proposal (funding cycle) has not passed, these two are the same.
  • To see the most recent information for an investigator, see the PENNERA_PEOPLE table where the PENN_ID in the PENNERA_PROPOSAL_INVESTIGATOR table matches the PENN_ID in the PENNERA_PEOPLE table, and the INSTITUTION_NO in the PENNERA_PROPOSAL_INVESTIGATOR table is the identification number of the desired proposal. Reminder: if the investigator is associated with the proposal more than once, this query will return one row per proposal, per investigator, per investigator org., 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.)
  • If the investigator is a University employee, the most recent data for the investigator is also available in EMPLOYEE_GENERAL. (EMPLOYEE_GENERAL is a view of the EMPLOYEE table, which is part of the Salary Management Data Collection in the Data Warehouse. It includes information, such as the CURRENTLY_EMPLOYED flag, that is not included in the PENNERA_PEOPLE table.) You might wish to query EMPLOYEE_GENERAL where the PENN_ID in the PENNERA_PROPOSAL_INVESTIGATOR table matches the PENN_ID in EMPLOYEE_GENERAL, and the INSTITUTION_NO in the PENNERA_PROPOSAL_INVESTIGATOR table is the identification number of the desired proposal. Reminder: if the investigator is associated with the proposal more than once, this query will return one row per proposal, per investigator, per investigator org., 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.)

Source
The Proposal Tracking module of PennERA, used at Penn since October 14, 2003 to track information on proposed and awarded grants and contracts.

PennERA is the University's electronic research administration system, providing support to faculty and research administrators for all sponsored project activity at the University of Pennsylvania.

PENNERA_PROPOSAL_INVESTIGATOR Table - Data Element Index   Tables and Data Elements   PennERA Proposals Home   Data Warehouse Home