PENNERA_REQUEST Table
DWADMIN Schema
Explanation
Records data about each request for funding for a given budget period within
a proposal (funding cycle) for a sponsored project. Includes
information such as the
school,
organization,
and investigator making the request; the duration of the requested budget
period; and various requested dollar amounts. There is one PENNERA_REQUEST
record per
proposal (funding cycle),
per
request. For example, a proposal for a one-year project submitted
to the Small Business Administration including a request for funding for
the
year,
as
well as a
request for
supplemental funding, would have two PENNERA_REQUEST records.
Common Uses
- Checking the status for a request. ("My project is about to go into
year two, and I need to make sure my non-competing continuation has been submitted.
Have the requests for period 2 changed from 'Future'
status to 'Future Pending' status yet?"")
- Reporting on funding requests at a detailed level.
- Listing data about a particular request based on the school's log number
for the request. ("What
information is there about the request with log number '02-1701'?")
- Reporting on requests by the date when they were submitted.
Primary Key |
Indexed Data Elements |
Related Tables |
PROP_NO
PERIOD_NUMBER
REQUEST_NUMBER
|
INSTITUTION_NO
PAR_PI_PRIACADAPPT_ORG_CODE
PAR_PI_PRIACADAPPT_SCH_CODE
PARENT_PI_ERA_PRI_ORG_CODE
PARENT_PI_ERA_PRI_SCHOOL_CODE
PARENT_PI_HOME_ORG_CODE
PARENT_PI_HOME_SCH_CODE
PARENT_PI_INV_ORG_CODE
PARENT_PI_INV_SCH_CODE
PARENT_PI_PRI_APPT_ORG_CODE
PARENT_PI_PRI_APPT_SCH_CODE
PARENT_PROP_RESP_ORG_CODE
PARENT_PROP_RESP_SCH_CODE
PERIOD_NUMBER
PI_ERA_PRIMARY_ORG_CODE
PI_ERA_PRIMARY_SCHOOL_CODE
PI_HOME_ORG_CODE
PI_HOME_SCHOOL_CODE
PI_INVESTIGATOR_ORG_CODE
PI_INVESTIGATOR_SCHOOL_CODE
PI_PRI_ACAD_APPT_ORG_CODE
PI_PRI_ACAD_APPT_SCHOOL_CODE
PI_PRI_APPT_ORG_CODE
PI_PRI_APPT_SCHOOL_CODE
PROP_NO
PROPOSAL_RESP_ORG_CODE
PROPOSAL_RESP_SCHOOL_CODE
REQRESPINV_ERA_PRI_ORG_CODE
REQRESPINV_ERA_PRI_SCH_CODE
REQRESPINV_HOME_ORG_CODE
REQRESPINV_HOME_SCH_CODE
REQRESPINV_INV_ORG_CODE
REQRESPINV_INV_SCH_CODE
REQRESPINV_PRI_APPT_ORG_CODE
REQRESPINV_PRI_APPT_SCH_CODE
REQRESPINV_PRIACADAP_ORG_CODE
REQRESPINV_PRIACADAP_SCH_CODE
REQUEST_RESP_ORG_CODE
REQUEST_RESP_SCHOOL_CODE
REQUEST_STATUS
SCHOOL_LOG_NUMBER
SPONSOR_CODE
|
PENNERA_PEOPLE
PENNERA_PERIOD
PENNERA_PERIOD_STAT_HIST
PENNERA_PROP_REGULATORY_APPR
PENNERA_PROP_STAT_HIST
PENNERA_PROPOSAL
PENNERA_PROPOSAL_ACTIVITY
PENNERA_PROPOSAL_INVESTIGATOR
PENNERA_PROPOSAL_PARENT
PENNERA_SPONSOR
|
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 does 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 is one PENNERA_REQUEST record per proposal (INSTITUTION_NO or PROP_NO)
per REQUEST_NUMBER. Though PERIOD_NUMBER is part of the physical primary
key for the PENNERA_REQUEST table, it is not part of the logical primary
key for the table.
- A PENNERA_REQUEST record includes the current status for the request ('Pending',
'Awarded', etc.).
- Note that REQUEST_STATUS will be null if the request belongs to a proposal
whose PennERA record was created in Proposal Development
(PD) and the proposal has not yet been submitted to its sponsor. (If
the record was created in PD, PENNERA_PROPOSAL.PD_FLAG = 'Y'. If the
proposal has been submitted to the sponsor, its proposal status history
includes a record for the 'Pending' status.)
- For the effective date of the current status of the request, or for
more details on how the request's status has changed over
time, use the PENNERA_REQ_STAT_HIST table. Note that, while the status
date defaults to the system date when the status was entered, it may
be changed by the person entering
the data
to
more correctly reflect the effective date of the status.
- For the current status for
the proposal (funding cycle), see the PENNERA_PROPOSAL table. For more
details
on how the proposal's status has changed over time, use the PENNERA_PROP_STAT_HIST
table. Note that, while the status date defaults to the system date
when the status was entered, it may be changed by the person entering
the data
to
more correctly reflect the effective date of the status.
- The names of some data elements begin with 'REQUESTED'. Those
names can be confusing. The values in those data elements reflect what was
requested, not what was awarded. This is true regardless of the
current status of the request ('Pending', 'Awarded', etc.).
- An Organization is a subdivision of the University created for management
purposes. When reporting on Organizations ("orgs."), be sure to
select the data element that meets your needs. (Note that for every organization
element,
the PENNERA_REQUEST
table also stores the associated school/center element.)
- Organizations for the proposal (funding cycle): The PROPOSAL_RESP_ORG_CODE
identifies the org. responsible
for the proposal
at the time
of
the proposal. There are several organizations for the PI (the Principal
Investigator for the proposal). The PI_HOME_ORG_CODE identifies the org.
that owns the PI's employee record and is responsible
for its maintenance. The PI_PRI_APPT_ORG_CODE identifies the org. where
the PI has his or her primary job appointment. The PI_PRI_ACAD_APPT_ORG_CODE
identifies the org. where the PI has his or her primary academic job
appointment--the
highest-ranking faculty job appointment. (For information on how
the primary appointment and the primary academic appointment are determined,
see the explanation in the Cautions section of the documentation
for the EMPLOYEE_GENERAL table.)
The PI_ERA_PRIMARY_ORG_CODE identifies the org. flagged as
the
primary department
in the PI’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.). PI_INVESTIGATOR_ORG_CODE is either the same as the PROPOSAL_RESP_ORG_CODE,
or (roughly speaking) the org. with which the PI 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.)
- Organizations 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. If the increment's 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'. If applicable, the PENNERA_REQUEST record will have information
on the same kinds of orgs. for the parent proposal as for the proposal:
PARENT_PROP_RESP_ORG_CODE (the org. responsible
for the parent proposal
at the time
of
the parent proposal), PARENT_PI_HOME_ORG_CODE (the org.
that owns the employee record for the PI of the parent proposal),
PARENT_PI_PRI_APPT_ORG_CODE, PAR_PI_PRIACADAPPT_ORG_CODE, PARENT_PI_ERA_PRI_ORG_CODE,
and PARENT_PI_INV_ORG_CODE.
- Organizations for the request: The REQUEST_RESP_ORG_CODE
identifies
the org. responsible for the
request at the time
of the request. The PENNERA_REQUEST record also includes information
on the same kinds of orgs. for the investigator responsible for the request
as it does for the Principal Investigator for the proposal: REQRESPINV_HOME_ORG_CODE
(the org. that owns the employee record
for
the investigator who is responsible for the request),
REQRESPINV_PRI_APPT_ORG_CODE, REQRESPINV_PRIACADAP_ORG_CODE, REQRESPINV_ERA_PRI_ORG_CODE, and REQRESPINV_INV_ORG_CODE.
- Note that the value of REQUEST_RESP_ORG_CODE may be inaccurate
for some records. For request records created on or after Oct. 10,
2006, in PennERA's Proposal Development (PD) module, the REQUEST_RESP_ORG_CODE
reflects the ERA primary org. of the person who created the request
record. That person's ERA primary org. might or might not be the
org. that is responsible for the request. (If the record was created
in PD, PENNERA_PROPOSAL.PD_FLAG = 'Y'. For more information on the
ERA primary org., see the documentation on PENNERA_PEOPLE.ERA_PRIMARY_ORG.)
- Users with University-wide access may see all the data elements for every
PENNERA_REQUEST record. Users with Organization-based access may see secured
PENNERA_REQUEST data elements only for records which they are authorized
to access
based on one or more of the orgs. in the PENNERA_REQUEST record. Users
with Organization-based access
may see
all other PENNERA_REQUEST data elements (including REQUESTED_TOT_SPON_COSTS)
for
every
record.
- The orgs. that apply to Organization-based access are the PROPOSAL_RESP_ORG_CODE,
PI_HOME_ORG_CODE, PI_PRI_APPT_ORG_CODE, PI_PRI_ACAD_APPT_ORG_CODE,
PI_ERA_PRIMARY_ORG_CODE, PI_INVESTIGATOR_ORG_CODE, REQUEST_RESP_ORG_CODE,
REQRESPINV_HOME_ORG_CODE,
REQRESPINV_PRI_APPT_ORG_CODE, REQRESPINV_PRIACADAP_ORG_CODE, REQRESPINV_ERA_PRI_ORG_CODE,
REQRESPINV_INV_ORG_CODE, PARENT_PROP_RESP_ORG_CODE,
PARENT_PI_HOME_ORG_CODE, PARENT_PI_PRI_APPT_ORG_CODE, PAR_PI_PRIACADAPPT_ORG_CODE,
PARENT_PI_ERA_PRI_ORG_CODE, and PARENT_PI_INV_ORG_CODE.
- To facilitate Organization-based security, the orgs. listed above have
their values set to '.' if they would otherwise be null.
- The secured PENNERA_REQUEST data elements are the REQUESTED_DIRECT_COSTS,
REQUESTED_MTDC, REQUESTED_INDIRECT_COSTS,
REQUESTED_COST_SHR_IN_KIND, REQUESTED_COST_SHR_CASH, REQUESTED_COST_SHR_OTHER,
REQUESTED_COST_SHR_TOTAL, REQUESTED_NONSPON_COSTS, and REQUESTED_TOTAL_COSTS.
- If an org. is '.', the corresponding school/center code is also '.'. (For
example, if the Principal Investigator has no employee information about
a faculty job appointment dating back to the time of the funding cycle, both
PI_PRI_ACAD_APPT_ORG_CODE and PI_PRI_ACAD_APPT_SCHOOL_CODE have the value
'.'.) The school/center data elements that store '.' (rather than a null
value) are the PROPOSAL_RESP_SCHOOL_CODE, PI_ERA_PRIMARY_SCHOOL_CODE, PI_HOME_SCHOOL_CODE,
PI_INVESTIGATOR_SCHOOL_CODE, PI_PRI_ACAD_APPT_SCHOOL_CODE, PI_PRI_APPT_SCHOOL_CODE,
REQUEST_RESP_SCHOOL_CODE, REQRESPINV_ERA_PRI_SCH_CODE, REQRESPINV_HOME_SCH_CODE,
REQRESPINV_INV_SCH_CODE, REQRESPINV_PRI_APPT_SCH_CODE, and REQRESPINV_PRIACADAP_SCH_CODE.
- Note: the org. and school/center data elements that refer
to the parent proposal should not be used in queries. 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.
- Note that a request is linked to a budget period--not to an increment.
To compare what was requested to what was awarded, use the PENNERA_PERIOD
table or the PENNERA_PROPOSAL
table.
- When reporting on investigators, be sure to select the data element that
meets your needs.
- Each proposal ought to have exactly one PENNERA_PROPOSAL_INVESTIGATOR
record with information on the Principal Investigator (PI_FLAG='Y').
(For Business Objects users, the Universe includes the PI's name in the
folder for the PENNERA_REQUEST table.)
- One way to get PENNERA_PROPOSAL_INVESTIGATOR information on the
Principal Investigator (PI) for the proposal is to specify that the
INSTITUTION_NO in the PENNERA_REQUEST
table must match the INSTITUTION_NO in the PENNERA_PROPOSAL_INVESTIGATOR
table, where PI_FLAG='Y'.
- Another way to get PENNERA_PROPOSAL_INVESTIGATOR information on
the PI for the proposal is to specify that the INSTITUTION_NO and
PI_PENN_ID in the PENNERA_REQUEST
table 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).
- Each request has one investigator responsible for it (the one whose
PennID is stored in REQUEST_RESP_INVESTIGATOR).
- The request responsible investigator may
or may not be the same as the Principal Investigator for the proposal.
- A given investigator may be responsible for more than one
request.
- Information on the REQUEST_RESP_INVESTIGATOR is stored in the PENNERA_PROPOSAL_INVESTIGATOR
table. (For Business Objects users, the Universe includes the
request responsible investigator's name in the folder for the PENNERA_REQUEST
table.) The proposal may have more than one PENNERA_PROPOSAL_INVESTIGATOR
record for the REQUEST_RESP_INVESTIGATOR if
that investigator is associated with the proposal more than once
(with different
investigator
orgs. and/or with different PI_FLAGs). To see PENNERA_PROPOSAL_INVESTIGATOR
information about the request responsible investigator just once
per request,
use SELECT
DISTINCT to retrieve information where the INSTITUTION_NO
and REQUEST_RESP_INVESTIGATOR in the
PENNERA_REQUEST table match the INSTITUTION_NO and PENN_ID in
the PENNERA_PROPOSAL_INVESTIGATOR table, and do not retrieve 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.)
- A proposal may have any number of PENNERA_PROPOSAL_INVESTIGATOR records
(including zero records) with information on the co-investigator(s),
if any (PI_FLAG='N'). 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). When including investigators' names in reports,
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
only for the PI, or only for the request responsible investigator.
- The PENNERA_PROPOSAL_INVESTIGATOR table has the investigator's information
as it was at the time of the proposal (funding cycle). For the current
information about the Principal Investigator or the request responsible
investigator, see the PENNERA_PEOPLE table where the Penn ID of the desired
investigator
in the PENNERA_REQUEST table matches the PENN_ID in the PENNERA_PEOPLE
table.
- SOMERA is the School of Medicine’s Electronic Research Administration
system. When a proposal record is created in PennERA via the SOMERA Interface,
the proposal’s PENNERA_REQUEST record for REQUEST_NUMBER 1 is created
with the SCHOOL_LOG_NUMBER for the SOMERA record that was fed to PennERA
via the Interface. (In turn, PennERA feeds SOMERA the Institution Number
for the PennERA proposal that includes the SCHOOL_LOG_NUMBER.) REQUEST_NUMBER
1 has the REQUEST_SUBMITTED_DATE set to the same value as the PENNERA_PROPOSAL.SUBMITTED_DATE
(the Status Date in SOMERA). The SOMERA Interface does not populate the SCHOOL_LOG_NUMBER
or the REQUEST_SUBMITTED_DATE for a request in PennERA unless the REQUEST_NUMBER
is 1. For every other REQUEST_NUMBER, the values of the SCHOOL_LOG_NUMBER
and REQUEST_SUBMITTED_DATE remain null until the School of Medicine provides
the values and the Office of Research Services enters them into PennERA.
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
|