SPON_PROJ_INVESTIGATOR Table - Data Element Index    Tables and Data Elements   Sponsored Projects Home   Data Warehouse Home

SPON_PROJ_INVESTIGATOR Table

Explanation
Identifies the investigator(s) for each proposal and award/account, and records their status as it was at the time of the proposal or award. For awarded accounts and advance accounts, and for proposals with associated records in the AWARD table, once the end date for the AWARD record has passed, the associated SPON_PROJ_INVESTIGATOR records are no longer updated. For proposals that do not have associated records in the AWARD table, once the end date for the proposal has passed, the associated SPON_PROJ_INVESTIGATOR records are no longer updated. (For current information on an investigator, see the EMPLOYEE_GENERAL table.)

Common Uses

• Sorting proposals or awards by the name of the investigator.
• Finding the principal investigators and co-principal investigators who worked on a particular project.
• Tracking the change of principal investigators for a project over time.
• Getting an investigator's name, track, rank, appointment, or department as it was at the time of the grant.

PROPOSAL_ID

SSN

PRIMARY_ORG_CODE

PRIMARY_SCHOOL_CODE

PROJECT_ID 

PROPOSAL_ID

SSN

AWARD

EMPLOYEE_GENERAL

ORG_CODES

PROPOSAL

• In the case of incremental awards (where each increment has its own AWARD_ID), SPON_PROJ_INVESTIGATOR stores one record for each increment.
• In the case of awards with sub-accounts, SPON_PROJ_INVESTIGATOR stores one record for each sub-account. Note that a given investigator may be associated with more than one account for an award.
• When including investigators' names in reports on proposals, note that multiple records will be returned for a given proposal if there is a co-principal investigator for the proposal. To avoid having multiple records returned, specify the condition that RESEARCHER_TYPE = 'P'. This will return the principal investigator's name.
• The investigator associated with an AWARD record may appear in SPON_PROJ_INVESTIGATOR as a principal investigator (RESEARCHER_TYPE = 'P') or co-principal investigator (RESEARCHER_TYPE = 'C'), but that does not mean that the investigator is the principal or co-principal investigator for the entire project. To find the principal or co-principal investigator for the project, see the investigators associated with the proposal for the award. To find the principal investigator for the award, see the investigator associated with the prime account for the award (the AWARD_ACCOUNT_TYPE in the AWARD record has the value P).
• SPON_PROJ_INVESTIGATOR stores the investigator's primary appointment and department data as it was at the time of the at the time of the proposal or award. EMPLOYEE_GENERAL, on the other hand, stores the most recent data on an investigator's primary appointment and department. If the end date for the proposal or award has not passed, these two will be the same.
• The ORA_INVESTIGATOR_NAME for a person may not be the same as that person's NAME in the EMPLOYEE_GENERAL table. This will be the case when the person's name has changed since the end of the proposal or award.

SPON_PROJ_INVESTIGATOR Table - Data Element Index    Tables and Data Elements   Sponsored Projects Home   Data Warehouse Home