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

PENNERA_INCREMENT Table

DWADMIN Schema

Explanation
Records data about each increment of a sponsored project award or advance account, including information such as the financial account; the school, organization, and investigator administering the award; the duration of the awarded budget period; and various awarded dollar amounts. When an amount is received, or an amount is expected to be paid in the future (per a Notice of Award), or an amount is paid by a department in expectation of an amount to be received, an increment is the portion of that amount that is assigned to a General Ledger account.

For example, say Dr. Doe and Dr. Smith are working on a project, and the University receives an award check for $10,000 for that project. Dr. Doe is assigned $6,000, and Dr. Smith is assigned $4,000. The $6,000 is added to a General Ledger account for Dr. Doe's part of the project, and the $4,000 is added to a separate General Ledger account for Dr. Smith's part of the project. In this scenario, we have two increment records.

There is one PENNERA_INCREMENT record per proposal (funding cycle), per increment. For example, an award from the NIH for a five-year project that received one award check each year, split between two General Ledger accounts each year, would have ten increments.

Common Uses

• Checking the status for an increment. ("My project is about to go into year two. Have the increments for period 2 changed from 'Future' status to 'Awarded' status yet?")
• Reporting on award payments at a detailed level
• Listing data about a particular award based on the sponsor's award ID. ("What information is there about NIH award 2-P30-DK19525-26?")
• Reporting on awards by the federal fiscal year of the award appropriation.
• Reporting on awards by the date the award was made.
• Reporting on awards paid in foreign currency.
  

PROP_NO
PERIOD_NUMBER
INCREMENT_NUMBER

ACCTRESPINV_ERA_PRI_ORG_CODE
ACCTRESPINV_ERA_PRI_SCH_CODE
ACCTRESPINV_HOME_ORG_CODE
ACCTRESPINV_HOME_SCH_CODE
ACCTRESPINV_INV_ORG_CODE
ACCTRESPINV_INV_SCH_CODE
ACCTRESPINV_PRI_APPT_ORG_CODE
ACCTRESPINV_PRI_APPT_SCH_CODE
ACCTRESPINV_PRIACADAP_ORG_CODE
ACCTRESPINV_PRIACADAP_SCH_CODE
AWARD_CNAC
AWARD_CREF
AWARD_FUND
AWARD_GL_ACCOUNT
AWARD_LEGACY_ACCOUNT
AWARD_ORG
AWARD_PROGRAM
FUND_RESPONSIBLE_ORG
INCREMENT_NUMBER
INCREMENT_RESP_ORG_CODE
INCREMENT_RESP_SCHOOL_CODE
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
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 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 is one PENNERA_INCREMENT record per proposal (INSTITUTION_NO or PROP_NO) per INCREMENT_NUMBER. Though PERIOD_NUMBER is part of the physical primary key for the PENNERA_INCREMENT table, it is not part of the logical primary key for the table.
• A PENNERA_INCREMENT record includes the current status for the increment: 'Advance Account', 'Awarded', or 'Future'. For the effective date of that status, or for more details on how the increment's status has changed over time, use the PENNERA_INCR_STAT_HIST table. 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 a 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 'AWARD' or 'AWARDED'. Those names can be deceiving. To see whether the value for the data element applies to an advance account, an award, or an award expected in the future, see INCREMENT_STATUS.
• The data elements NIH_GRANT_APPLICATION_TYPE, NIH_GRANT_ACTIVITY_CODE, NIH_GRANT_ADMIN_ORG, NIH_GRANT_SERIAL_NUMBER, NIH_GRANT_YEAR, and NIH_GRANT_SUPPLEMENT are populated only if two criteria are met:
  1. the proposal's sponsor or sponsor parent is NIH (SPONSOR_CODE = '09470' or the PENNERA_SPONSOR record for the proposal's sponsor has SPONSOR_PARENT= '09470'), and
  2. the increment is funded under a 'Grant' or 'Cooperative Agreement' (per the PENNERA_PROPOSAL.INSTRUMENT_TYPE).
  For other records, the NIH_GRANT_ data elements are null.
• AWARD_LEGACY_ACCOUNT stores the legacy account number in the 6-digit format used in SUBLED (the subledger system used at Penn before July 1, 1996). (Legacy accounts in the 6-digit format are still used for grants and contracts in the Bursar system.) AWARD_GL_ACCOUNT stores AWARD_LEGACY_ACCOUNT's counterpart, the 26-position (seven-segment) Accounting Flexfield used in the present General Ledger accounting structure (in use at Penn since July 1, 1996). AWARD_GL_ACCOUNT is the account combination assigned to the increment at the time of the award. (Its Object segment is set to '3000'.) The combination of AWARD_CNAC, AWARD_ORG, AWARD_BC, AWARD_FUND, AWARD_PROGRAM, and AWARD_CREF ought to be used for posting transactions to BEN Financials, but transactions may be posted to any combination that includes the AWARD_FUND. Only the AWARD_FUND segment is reliable for finding postings in BEN Financials. (For awards that were not active July 1, 1996 and thereafter, AWARD_CNAC and AWARD_ORG are populated, but the other segments are null.)
• When counting 'awards', be careful to specify what you are counting. To count active, awarded proposals (funding cycles), count PENNERA_PROPOSAL.INSTITUTION_NO where PENNERA_PROPOSAL.PROPOSAL_STATUS is 'Awarded'. To count awarded increments (counting each portion of an award payment that has been assigned to a General Ledger account), count the combination of the PENNERA_INCREMENT elements INSTITUTION_NO, PERIOD_NUMBER, and INCREMENT_NUMBER where INCREMENT_STATUS is 'Awarded'. To ensure you are counting each 'award' only once, use SELECT DISTINCT (in Business Objects, using the Edit Data Provider screen, select Options / No Duplicate Rows).
• 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_INCREMENT 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_INCREMENT 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 increment: The INCREMENT_RESP_ORG_CODE identifies the org. responsible for managing the science and the money for the increment at the time of the increment. The FUND_RESPONSIBLE_ORG identifies the org. responsible for managing the General Ledger fund (the increment's AWARD_FUND). The AWARD_ORG is the org. segment of the increment's AWARD_GL_ACCOUNT, the 26-position (seven-segment) Accounting Flexfield used in the present General Ledger accounting structure (in use at Penn since July 1, 1996). The PENNERA_INCREMENT record also includes information on the orgs. for the ACCOUNT_RESP_INVESTIGATOR (the investigator responsible for managing the funding tracked by the AWARD_GL_ACCOUNT, at the time of the increment). These are the ACCTRESPINV_HOME_ORG_CODE (the org. that owns the employee record for the ACCOUNT_RESP_INVESTIGATOR), ACCTRESPINV_PRI_APPT_ORG_CODE, ACCTRESPINV_PRIACADAP_ORG_CODE, ACCTRESPINV_ERA_PRI_ORG_CODE, and ACCTRESPINV_INV_ORG_CODE.
• Users with University-wide access may see all the data elements for every PENNERA_INCREMENT record. Users with Organization-based access may see secured PENNERA_INCREMENT data elements only for records which they are authorized to access based on one or more of the orgs. in the PENNERA_INCREMENT record. Users with Organization-based access may see all other PENNERA_INCREMENT data elements (including 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, INCREMENT_RESP_ORG_CODE, FUND_RESPONSIBLE_ORG, AWARD_ORG, ACCTRESPINV_HOME_ORG_CODE, ACCTRESPINV_PRI_APPT_ORG_CODE, ACCTRESPINV_PRIACADAP_ORG_CODE, ACCTRESPINV_ERA_PRI_ORG_CODE, ACCTRESPINV_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_INCREMENT data elements are the INCREMENT_F_AND_A_RATE, 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 ACCTRESPINV_ERA_PRI_SCH_CODE, ACCTRESPINV_HOME_SCH_CODE, ACCTRESPINV_INV_SCH_CODE, ACCTRESPINV_PRI_APPT_SCH_CODE, ACCTRESPINV_PRIACADAP_SCH_CODE, INCREMENT_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, 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.
• Within a budget period for an awarded proposal, there may be more than one value for SPONSOR_AWARD_ID (the identification number that is assigned to the award by the sponsor). For example, if the period includes an increment for the new project as well as an increment for a supplement, those two increments might have different values for SPONSOR_AWARD_ID.
• Note that an increment is linked to a budget period--not to a request. 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_INCREMENT 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_INCREMENT 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_INCREMENT 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 increment is assigned to an account that includes an AWARD_FUND. The AWARD_FUND has one investigator responsible for it (the one whose PennID is stored in FUND_RESPONSIBLE_INVESTIGATOR).
    • The fund 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 fund.
    • Information on the FUND_RESPONSIBLE_INVESTIGATOR is stored in the PENNERA_PROPOSAL_INVESTIGATOR table. The proposal may have more than one PENNERA_PROPOSAL_INVESTIGATOR record for the FUND_RESPONSIBLE_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 fund responsible investigator just once per increment, use SELECT DISTINCT to retrieve information where the INSTITUTION_NO and FUND_RESPONSIBLE_INVESTIGATOR in the PENNERA_INCREMENT 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.)
  • Each increment has one investigator responsible for the increment (the one whose PennID is stored in ACCOUNT_RESP_INVESTIGATOR).
    • The account 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 increment.
    • Information on the ACCOUNT_RESP_INVESTIGATOR is stored in the PENNERA_PROPOSAL_INVESTIGATOR table. (For Business Objects users, the Universe includes the account responsible investigator's name in the folder for the PENNERA_INCREMENT table.) The proposal may have more than one PENNERA_PROPOSAL_INVESTIGATOR record for the ACCOUNT_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 account responsible investigator just once per increment, use SELECT DISTINCT to retrieve information where the INSTITUTION_NO and ACCOUNT_RESP_INVESTIGATOR in the PENNERA_INCREMENT 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 account 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, the fund responsible investigator, or the account responsible investigator, see the PENNERA_PEOPLE table where the Penn ID of the desired investigator in the PENNERA_INCREMENT table matches the PENN_ID in the PENNERA_PEOPLE table.
• If a proposal has awarded ARRA funding, that does not necessarily mean that all of its funded project sponsor costs are ARRA dollars.
  • A proposal with awarded ARRA funding might or might not also have other (non-ARRA) awarded funding. The ARRA_FUNDED_INCREMENT_FLAG stores a 'Y' or an 'N' to indicate whether a given increment’s AWARDED_TOT_SPON_COSTS were or were not awarded under ARRA (the American Recovery and Reinvestment Act of 2009, also known as the Recovery Act or the federal economic stimulus program). See also the ARRA_AWARD_FLAG in the PENNERA_PROPOSAL table.
    • For increments awarded under ARRA, the FUND_REVENUE_PARENT is 'GCB009', 'LOCNIS', or 'LOCNSS'. (In the PARENT_FUND_CODES table, their PARENT_FUND is 'ARRALC'.) Because advance accounts are funded by the University (not by the sponsor), an increment in 'Advance Account' status should not have 'GCB009', 'LOCNIS', or 'LOCNSS' as its FUND_REVENUE_PARENT, but it might. Rather than using the FUND_REVENUE_PARENT to identify ARRA increments, use the ARRA_FUNDED_INCREMENT_FLAG.
  • Some increments whose ARRA_FUNDED_INCREMENT_FLAG is 'Y' might have a status other than 'Awarded'. Depending on the requirements of a particular report on ARRA increments, you might or might not want to specify a condition on INCREMENT_STATUS.

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