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.
Primary Key |
Indexed Data Elements |
Related Tables |
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.
Questions about this page? Email us at da-staff@isc.upenn.edu
|