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