PennERA Proposals - Summary
of New Features
- What was called a ‘proposal’ in the Sponsored
Projects data collection is called a ‘request’ in the
PennERA Proposals data collection.
A ‘proposal’ in the PennERA Proposals data collection refers
to a project period (funding cycle), not a budget period or supplement.
- The identification number for a proposal is not a ‘smart’ number.
In the Sponsored Projects data collection, a Proposal ID consisted
of a 5-digit project ID, followed by a 2-digit funding segment ID,
by two zeroes. (The Award ID was the same as the Proposal ID, but used
the last two digits as the award account sequence number.) In the PennERA
Proposals data collection, a proposal (for a project period) is identified
by an Institution Number. The budget period within a proposal is identified
by the Period Number. (A proposal with five periods will have Period
Numbers 1 through 5.) A request belonging to a proposal is identified
by a Request Number, and is assigned to a period. (A proposal with
six requests will have Request Numbers 1 through 6.) An award increment
belonging to a proposal is identified by an Increment Number, and is
assigned to a period. (An award increment is a portion of an award
payment assigned to a General Ledger account. If the sponsor has sent
three award checks for a proposal, and each check is divided between
two General Ledger accounts, the proposal has six award increments,
with Increment Numbers 1 through 6.)
- Summarized data is available for the project
Say a Principal Investigator asks a sponsor to fund a project that
will run for five years (five budget periods). The PENNERA_PROPOSAL
has information on what was requested and what was awarded for the
five-year project period as a whole (all five budget periods combined).
For example, it stores the requested and awarded project period start
and end dates, the requested number of budget periods,
and the requested and awarded dollar amount from the sponsor (‘sponsor
- Summarized data is available for the budget
Say a Principal Investigator asks a sponsor for funding for a budget
period, and later asks for supplemental funding for the same budget
period. The PENNERA_PERIOD table has information on what was requested
for the budget period as a whole (the budget period request and the
supplement request combined). Say the award check for the budget
period was divided among three General Ledger accounts, and the award
for the supplement was assigned to one General Ledger account. The
PENNERA_PERIOD table has information on what was awarded for the
budget period as whole (all four award increments combined). The
table also stores the requested and awarded start and end dates for
the budget period.
- Data is available for ‘out years.’
Say a Principal Investigator asks a sponsor to fund a project that
will run for five years (five budget periods), and that will need
just one 22-digit account (CNAC-Org.-Budget Check-Fund-Program-CREF)
to track its income and expenses.
- In PennERA, a proposal record
is created for the five-year project, a requested budget period
record is created for each of the five years, and a request (with
request type 'Budget Period') is created for each of the five
years. When the proposal is sent
to the sponsor, the status for each request is ‘Pending’.
In the PENNERA_PERIOD and PENNERA_REQUEST tables, the records for each
of the five budget periods will have a value for REQUESTED_TOT_SPON_COSTS.
- Say an advance account is authorized. The Office of Research
Services (ORS) creates an increment for period 1 with the status
Account'. In the PENNERA_PERIOD and PENNERA_INCREMENT tables, the
records for period 1 will have a value
- Say the sponsor
decides to fund the project and sends the first Notice of Award,
including information on the funding granted for the first year,
and the funding
planned for each subsequent year. ORS
changes the status of the 'Budget Period' request for period 1
to ‘Awarded’ and
the status of the 'Budget Period' requests for periods 2 through
5 to ‘Future.’ ORS
changes the status of the increment for period 1 to ‘Awarded’,
adjusts the AWARDED_TOT_SPON_COSTS
for that increment as needed, and creates
an increment with the status 'Future' for each of the other four
periods. In the PENNERA_PERIOD and PENNERA_INCREMENT tables, the
records for each
will have a value
- When the Principal Investigator submits
the non-competing continuation for the second year of the project,
the status of the 'Budget Period' request for period 2 is changed
to ‘Future Pending.’
sponsor grants funding for the second year and sends the next Notice
of Award. ORS changes the status of
the of the 'Budget Period' request for period 2 to ‘Awarded’,
changes the status of the increment for period 2 to ‘Awarded’,
the AWARDED_TOT_SPON_COSTS for that increment as needed.
- In the Sponsored Projects data collection,
to compare what was requested to what was awarded, we compared what
was in the Proposal table
to what was in the Award table. In the PennERA Proposals data collection,
the requested and awarded amounts are available in one table.
- The PENNERA_PROPOSAL table has information on what was requested
and what was awarded for the entire project period.
- The PENNERA_PERIOD table has information on what was requested
and what was awarded for a particular budget period.
- A given PENNERA_REQUEST record cannot be tied to a PENNERA_INCREMENT
record. For example, a period could have two requests and
four increments. The first request might have been awarded,
award check was
split among four accounts; the second request might still
be pending. Or, the
award for the first request might have been assigned to
one account, but the award for the second request was split
among three accounts.
These are just two of the possible explanations for how
came to be four increments for the period. Do not attempt
to link a request
- Because a given PENNERA_REQUEST record cannot
be tied to a PENNERA_INCREMENT record:
- SCHOOL_LOG_NUMBER is available only in the PENNERA_REQUEST table.
- SPONSOR_AWARD_ID is available only in the PENNERA_INCREMENT
- NIH_GRANT_ACTIVITY_CODE, NIH_GRANT_ADMIN_ORG, NIH_GRANT_APPLICATION_TYPE,
NIH_GRANT_SERIAL_NUMBER, NIH_GRANT_SUPPLEMENT, and NIH_GRANT_YEAR
are available only in the PENNERA_INCREMENT table, and only
for awards that
are grants from the National Institutes of Health.
- Submitted date is available
for both proposals and requests.
In the PENNERA_PROPOSAL table, SUBMITTED_DATE is the date when the proposal
for the project period (funding cycle) was submitted to the sponsor.
In the PENNERA_REQUEST table, REQUEST_SUBMITTED_DATE is the date the
request (specific to the budget period) was submitted to the sponsor.
- ‘Type’ is available for proposals,
requests, and increments.
In the PENNERA_PROPOSAL table, the PROPOSAL_TYPE indicates the reason
for requesting funding for the funding cycle, as noted in the 'Type
Proposal' field on the
Office of Research Services Proposal Transmittal and Approval Form that
was submitted with the first request for funding for
this funding cycle. (To give just two examples,
the proposal may have been submitted as a ‘New Project’,
or it may
have been submitted due to a 'Change of Grantee Inst'--a change of
grantee institution.) In the PENNERA_REQUEST table, the REQUEST_TYPE
indicates whether the record stores the original request for funding
for the budget period ('Budget Period') or a request for supplemental
funding for the budget period ('Supplement'). In the PENNERA_INCREMENT
table, AWARD_TYPE indicates what the increment was created to track
Project’, ‘Cost Sharing’, 'Supplement', etc.).
- Status history data is available.
In the Sponsored Projects data collection, only the latest value was
stored for the Proposal record’s status. In the PennERA Proposals
data collection, the current status and the status history is stored
the proposal (project period), for each request, and for each increment.
(History is complete only for records created
the status history for a proposal might show when its status
was set to ‘Pending’, then to ‘Advance Account’,
then to ‘Awarded’.
- It is possible to store more data on program
projects and their subprojects.
the Sponsored Projects data collection, there was one Proposal record
for a program project,
and multiple associated Award records (for the
subprojects and their various award accounts). In PennERA,
it is possible to store data for a program project in more
than one PENNERA_PROPOSAL record.
Note: currently, data for program projects is stored in
PennERA in one PENNERA_PROPOSAL record per program project; subproject
data is stored at the increment level. The PennERA
data collection is designed to accommodate program project data
stored in multiple PENNERA_PROPOSAL records per program project if
and when it is entered in that form. It is possible to
create one PENNERA_PROPOSAL record for the program project as a whole,
and one for each sub-project.
The PENNERA_PROPOSAL record for the program
project as a whole will
have a null value for PARENT_INSTITUTION_NO. Its dollar amounts will
be the total of the sub-project amounts plus anything specific to the
program project as a whole. The PENNERA_PROPOSAL records for the subprojects
will have the Institution Number for the program project proposal as
their PARENT_INSTITUTION_NO. (The PENNERA_PROPOSAL record for the program
project will have no value for PARENT_INSTITUTION_NO, but will have
a non-zero value for SUBPROJECT_COUNT.) The Warehouse includes the
PENNERA_PROPOSAL_PARENT table to make it easy to retrieve the data
for the program project and all its subprojects, via the program project’s
- More data is available on cost sharing.
Cost sharing is funding provided for the sponsored project that does
not come from the sponsor. For example, the University receives a grant
for a project estimated to have a total cost of $100,000. The sponsor
agrees to pay 75% ($75,000) and the University agrees to pay 25% ($25,000).
The $25,000 is the cost-sharing component.
- The PENNERA_PROPOSAL table includes both a cost sharing flag and
information on the cost sharing type:
- Mandatory—the sponsor
requires that cost sharing be included in the proposal
and Voluntary Committed—the proposal includes
both cost sharing required by the sponsor and cost sharing
that the University
has voluntarily committed to provide
- None—the proposal involves
no cost sharing
- Undetermined—data for the proposal was
converted from the Research Services system (the system used
before October 14, 2003
to track proposals
and awards.) It involves cost sharing, but the type of commitment
has not been entered into PennERA
- Voluntary Committed—the
proposal includes cost sharing that the University has voluntarily
committed to provide
- The cost sharing amount is called ‘nonsponsor costs.’ ‘Total
costs’ is the combination of nonsponsor costs and sponsor costs.
- Awards involving cost sharing have one or more award increments
for the cost sharing, using a 5- fund created to track the cost
sharing. Increments for cost sharing have a non-null value for FUND_COST_SHR_PARENT
in the PENNERA_INCREMENT table.
- Other information in the PENNERA_PROPOSAL
table that was not featured in the Sponsored Projects data collection:
- CONFLICT_OF_INT_FLAG (conflict of interest)
- CURRENT_PERIOD (current awarded period)
- CURRENT_PRIME_FUND and PREVIOUS_PRIME_FUND (the fund used to
track most of the funding for the proposal)
- DEADLINE_DATE (the date by which the proposal must be received
by the sponsor)
- FDP_FLAG (Federal Demonstration Partnership)
- ORIGINATING_SPONSOR_CODE and ORIG_SPON_FUNDING_SOURCE
(populated if the sponsor is covering the sponsor
costs of the proposal
from another source rather than its own money)
- PREV_INSTITUTION_NO and FIRST_INSTITUTION_NO (allows
for tracking of projects that span funding cycles)
- PROCESSED_DATE (the date when the Office of Research
Services entered the record for the proposal
into the PennERA Proposal
- SNAP_FLAG (Streamlined Non-Competing Award
- SPONSOR_PROGRAM_ID and PROGRAM_TYPE (this
might be an RFP, an RFA, an Announcement,
program offered by the
- Some of the above data is available
only for records created or updated
- Other information in the PENNERA_PERIOD table
that was not featured in the Sponsored Projects data collection:
- Other information in the PENNERA_INCREMENT
table that was not featured in the Sponsored Projects data collection:
- AWARD_OBJECT (Object code in the General Ledger account; set
- FED_FY_AWD_APPROPRIATION (if applicable, the U.S. federal fiscal
year whose budget includes the appropriation of funding for the
- FOREIGN_CURRENCY_FLAG, FOREIGN_CURRENCY_AMT, and FOREIGN_CURRENCY_UNIT
(for awards paid in foreign currency)
- FUND_COST_SHR_PARENT (used if the fund for the award increment
is a cost sharing fund)
- FUND_INT_BEAR_PARENT (used if the fund for the award increment
is for an interest-bearing account)
- FUND_OVERHEAD_PARENT (used if the award increment includes indirect
- FUND_REVENUE_PARENT (used if the fund tracks money provided
by the sponsor)
- An investigator is identified by his or her PennID.
Social Security numbers are not stored in PennERA. If the investigator
is taking a job at the University, but is not here yet, he or she
must be added to Penn Community so a PennID is assigned. No data will be
entered into PennERA for anyone who does not have a PennID. For records
dating from before time that the PennID has been in use, if the investigator’s
PennID could not be determined, a PennID beginning with a ‘P’ was
assigned to the investigator.
- Records created or updated in PennERA include information on
The Sponsored Projects data collection could store information only
for the principal investigator and one co-investigator.
- Records created or updated in PennERA may include information
on co-investigators who are not University employees.
All investigators, whether or not they are University employees,
will have PennIDs. Information on all investigators is available in
table (information as it was at the time of the proposal) and in the
PENNERA_PEOPLE table (current information).
- Investigators have an ERA primary organization.
For members of the research community who are otherwise not affiliated
with Penn, the ERA primary org. is ‘8760' ('Research Services').
The ERA primary org. is the org. used as the person’s default
affiliation (investigator org.) in PennERA. For staff, the ERA primary
org. is the
primary (job) appointment org. For faculty, it is the primary academic
(job) appointment org., which might not be the same as primary appointment
org. for those faculty holding administrative positions. For University
employees whose job appointments are all on the executive payroll, ERA
primary org. is ‘8000’. For students, the ERA primary org.
is the organizational equivalent of their home Division (for example, ‘0200’,
School of Arts and Sciences.) For investigators from the University of
Pennsylvania Health System (UPHS), the ERA primary org. is ‘2100’.
- Investigators with one or more academic appointments have a
primary academic organization.
The primary academic org. is the one where the investigator has his
or her highest-ranking academic appointment.
- Investigators have an investigator organization.
org. is the org. with which the investigator is affiliated for the purposes
of the proposal. 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. For
a given investigator associated with a proposal, the investigator org. is the
org. specified (on the Office of Research Services Proposal Transmittal and Approval Form)
as the 'Dept. Administering Project' (the PROPOSAL_RESP_ORG_CODE). An exception
to this rule is that, if the PROPOSAL_RESP_ORG_CODE is neither the investigator's
ERA primary org. nor an org. where the investigator has a job appointment, when
the investigator's data is entered for the proposal in PennERA, the investigator
org. is chosen from among those orgs., based on its being the
one that is most closely related to the PROPOSAL_RESP_ORG_CODE. In other words:
- if the investigator has only one org, then that org. is chosen. Otherwise,
- if the investigator has an org. that is the same as the PROPOSAL_RESP_ORG_CODE, then that org. is chosen. Otherwise,
- the investigator's
orgs. are examined in ascending order, and the first org. whose org.
code is greater than or equal to the PROPOSAL_RESP_ORG_CODE is chosen. Otherwise,
- if the investigator has no org. whose org. code
is greater than or equal to the PROPOSAL_RESP_ORG_CODE,
the investigator's ERA primary org. is chosen.
- Information about changes to records is stored at the
The Sponsored Projects data collection stored the CREATE_DATE (the
date the budget period-specific request record was created in the
Services System) and the MODIFY_DATE (the date of the latest change
to the budget period-specific request record in the Research Services
It did not store information on who created or modified the record.
PennERA stores only the MODIFY_DATE, and only at the proposal level
period or funding cycle). MODIFY_DATE in the PENNERA_PROPOSAL table
reflects the date of the latest change to any information related
to the proposal—including
investigators, budget periods, requests, increments, etc. PENNERA_PROPOSAL
also stores MODIFIED_BY, which identifies the PennERA user who last
modified the data.