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

PENNERA_PERIOD Table

DWADMIN Schema

Explanation
Records data about each budget period within a proposal (funding cycle) for a sponsored project. Includes information such as the status for the period, the requested and awarded start and end dates for the period, the number of requests and increments for the period, and various requested and awarded dollar amounts for the period. Award information is stored not only for award payments received and for advance accounts, but also for future periods ("out years") as specified in the Notice of Award. There is one PENNERA_PERIOD record per proposal (funding cycle), per budget period. For example, a proposal submitted to the NIH for a five-year project would have five PENNERA_PERIOD records.

Common Uses

• Comparing what was requested for a period to what was awarded for a period.
• Using information on future periods to develop budgets. ("It's time to develop the budget for Biochemistry. How much money do we expect to get in awards to Biochemistry next year?")
  

PROP_NO
PERIOD_NUMBER

FIRST_INSTITUTION_NO
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
PREV_INSTITUTION_NO
PROP_NO
PROPOSAL_RESP_ORG_CODE
PROPOSAL_RESP_SCHOOL_CODE
SPONSOR_CODE

PENNERA_INCREMENT
PENNERA_PEOPLE
PENNERA_PERIOD_STAT_HIST
PENNERA_PROP_REGULATORY_APPR
PENNERA_PROP_STAT_HIST
PENNERA_PROPOSAL
PENNERA_PROPOSAL_ACTIVITY
PENNERA_PROPOSAL_INVESTIGATOR
PENNERA_PROPOSAL_PARENT
PENNERA_REQUEST
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 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.
• 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_PERIOD 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 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_PERIOD has 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.
• Users with University-wide access may see all the data elements for every PENNERA_PERIOD record. Users with Organization-based access may see secured PENNERA_PERIOD data elements only for records which they are authorized to access based on one or more of the orgs. in the PENNERA_PERIOD record. Users with Organization-based access may see all other PENNERA_PERIOD data elements (including REQUESTED_TOT_SPON_COSTS and AWARDED_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, 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, PARENT_PI_INV_ORG_CODE, and PERIOD_RESP_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_PERIOD 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, REQUESTED_TOTAL_COSTS, AWARDED_DIRECT_COSTS, AWARDED_MTDC, AWARDED_INDIRECT_COSTS, AWARDED_COST_SHR_IN_KIND, AWARDED_COST_SHR_CASH, AWARDED_COST_SHR_OTHER, AWARDED_COST_SHR_TOTAL,
    AWARDED_NONSPON_COSTS, and AWARDED_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 PI_ERA_PRIMARY_SCHOOL_CODE, PI_HOME_SCHOOL_CODE, PI_INVESTIGATOR_SCHOOL_CODE, PI_PRI_ACAD_APPT_SCHOOL_CODE, PI_PRI_APPT_SCHOOL_CODE, and PROPOSAL_RESP_SCHOOL_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 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_PERIOD table.
  • 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_PERIOD table.)
    • One way to get information on the Principal Investigator (PI) for the proposal is to specify that the INSTITUTION_NO in the PENNERA_PERIOD table 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 in the PENNERA_PERIOD 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).
  • 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 (PI_FLAG='Y').
  • 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). For the current information about the Principal Investigator, see the PENNERA_PEOPLE table where the PI_PENN_ID in the PENNERA_PERIOD table matches the PENN_ID in the PENNERA_PEOPLE table.
• Usually, a proposal ought to have no requested periods that overlap with each other, and no awarded periods that overlap with each other. For example, if the REQUESTED_PERIOD_END_DATE for period 1 is 12/31/2004, the REQUESTED_PERIOD_START_DATE for period 2 ought to be 1/1/2005 or later. However, in some cases, it is legitimate for the proposal to have overlapping budget periods. If you find a proposal with overlapping budget periods, and you are uncertain whether they are legitimate, contact your pre-award administrator in the Office of Research Services (ORS).
• A given requested amount in a PENNERA_PERIOD record for a given proposal and period should be the same as the sum of that given requested amount for all the PENNERA_REQUEST records for that proposal and period, regardless of the value of PENNERA_REQUEST.REQUEST_STATUS.
  • For example, PENNERA_PERIOD.REQUESTED_TOT_SPON_COSTS should equal the sum of the PENNERA_REQUEST.REQUESTED_TOT_SPON_COSTS where PENNERA_PERIOD.INSTITUTION_NO = PENNERA_REQUEST.INSTITUTION_NO and PENNERA_PERIOD.PERIOD_NUMBER = PENNERA_REQUEST.PERIOD_NUMBER.
  • In this example, PENNERA_PERIOD.REQUESTED_TOT_SPON_COSTS reflects the amount requested for the period, not what was awarded. This is true regardless of the current status of the request records for the period (even if one or more has the status 'Awarded').
• A given awarded amount in a PENNERA_PERIOD record for a given proposal and period should be the same as the sum of that given awarded amount for all the PENNERA_INCREMENT records for that proposal and period, regardless of the value of PENNERA_INCREMENT.INCREMENT_STATUS.
  • For example, PENNERA_PERIOD.AWARDED_TOT_SPON_COSTS should equal the sum of the PENNERA_INCREMENT.AWARDED_TOT_SPON_COSTS where PENNERA_PERIOD.INSTITUTION_NO = PENNERA_INCREMENT.INSTITUTION_NO and PENNERA_PERIOD.PERIOD_NUMBER = PENNERA_INCREMENT.PERIOD_NUMBER.
  • In this example, PENNERA_PERIOD.AWARDED_TOT_SPON_COSTS includes not only awards that have been received for the period, but also awards for the period that are expected to be paid in the future (per a Notice of Award) and amounts paid by a department in expectation of an award for the period.
• 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.
  

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_PERIOD Table - Data Element Index   Tables and Data Elements   PennERA Proposals Home   Data Warehouse Home