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

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.
  

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.

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