PennERA and Sponsor Conversion: effect on the Data Warehouse
August 25, 2003
PennERA (Electronic Research Administration) includes several modules. Some
of those modules replace RSS (the Research Services System, used by the Office
of Research Services) and PPM (the Penn Protocol Manager, used by the Office
of Regulatory Affairs). These modules are integrated with another module called
SPIN (the Sponsored Programs Information Network), which provides information
on funding opportunities.
SPIN and the other PennERA modules use a 5-character code to identify a sponsor.
Each sponsor has only one 5-character code. That code indicates nothing other
than the identity of the sponsor. Information on the sponsor type and federal
flow through dollars is stored in other data elements.
RSS, PPM, and the General Ledger used a 4-character code to identify a sponsor
(or agency). Besides identifying the sponsor, the 4-character code also indicated
the sponsor type (a foundation, the federal government, etc.), and whether
the sponsor was providing federal flow through dollars. (When a sponsor provides
financial resources for a fund, the source of that money might be not the sponsor
itself, but the U.S. federal government. Such a fund is said to contain federal
flow through dollars.) Because the 4-character code did more than simply identify
the sponsor, a given sponsor could have more than one 4-character code. Some
sponsors have more than one 4-character code for other reasons. (For example,
National Institutes of Health has multiple 4-character codes, because some
codes were assigned to institutes within NIH.) For the purposes of conversion,
each sponsor had one 4-character code identified as its ‘root’ code
(or ‘valid duplicate’), and any other codes for the sponsor were
Effective August 25, 2003, the General Ledger was converted to use the 5-character
sponsor code. However, BRIM (the Billing and Receivables Information Management
system used by the Office of Research Services) continued to use the 4-character
sponsor code. Therefore, a sponsored project fund created in the General Ledger
after sponsor conversion has a 5-character sponsor code as an attribute, and
that code is linked to the 4-character code that is the valid duplicate for
the sponsor. Sponsors added after August 25, 2003 have a 4-character code beginning
Accordingly, the following changes were made in the Data Warehouse:
These changes affect any query that uses one or both of these tables
to access information on sponsors or on sponsored project funds (‘5-funds’).
- Any queries that set conditions on the 4-character fund sponsor code
must be changed. For example, if the query selected records for funds with FUND_SPONSOR_CODE
1517 (Children’s Hospital of Philadelphia, federal flow through), change
the query to select FUND_SPONSOR_CODE 61399, and also specify funds where FEDERAL_FLAG
is Y (to return only funds that contain federal flow through dollars). Another
approach might be to change the query to select records with LEGACY_FUND_SPONSOR_CODE 1517, but this is not recommended.
Funds created for a sponsor on or after August 25, 2003 are linked to the
valid duplicate code—and the valid
duplicate for Children’s Hospital of Philadelphia is not 1517, but
4151. Asking for1517 might omit records that you want to include in your
Any queries that sort records based on the 4-character fund sponsor
code may need to be changed. For example, instead of Children’s Hospital of
Philadelphia funds showing up in two different places, they will show up
in one place. This
may or may not be a problem for the user of the query results.
Any queries that involve the old sponsor type (derived from the
4-character sponsor code) must be changed. Use the new sponsor type (FUND_SPONSORS.FUND_SPONSOR_TYPE).
Also, note that the values for FUND_SPONSOR_TYPE are different from the values
for the sponsor type that was based on the 4-character code. You
could also derive the old sponsor type from LEGACY_FUND_SPONSOR_CODE,
but this is not recommended. A fund created for a sponsor on or after August 25, 2003
will be linked to the valid duplicate code. If the sponsor had more
than one 4-character
code, its new fund, which might have been counted under one sponsor
type in the past, will be counted under the valid duplicate’s
sponsor type instead.
- Any queries that identify federal flow through funds must be changed. Use the new FEDERAL_FLAG.
Any queries that set conditions on sponsor name may need to be changed. In
some cases, the name associated with the 5-character sponsor code is different
from the name associated with the 4-character code. For example, funds that
had sponsor 1537—CASE WESTERN RESERVE UNIVERSITY will be changed to sponsor
52007—CASE WESTERN UNIVERSITY.
- Any reports that display the sponsor code, name, or type may need to
be changed to allow for the increase in the size of the column.
For information on the sponsor mapping table used to convert RSS, PPM, and
General Ledger data, see SPONSOR_OLD_TO_NEW.
(Business Objects users can query this table using the FINQUERY Universe.)
For a complete list of all past and current 4-character sponsor codes, names,
and types, along with their PennERA equivalents, see LEGACY_FUND_SPONSORS. (Business
Objects users can query this table using the FINQUERY Universe.)
The Sponsored Projects Universes
In all Universes describing Sponsored Projects (SponProj) data, the Agency
folder now includes a sub-folder called Agency Old to New, that maps the Agency
codes in SponProj to the 5-character SPIN sponsor codes. (The sub-folder accesses
the LEGACY_FUND_SPONSORS table.) Existing SponProj queries that use the Agency
code need not be changed, but users who wish can use the Agency Old to New
folder to get the 5-character fund sponsor code, the sponsor name, and the