Penn Computing

Penn Computing

Computing Menu Computing A-Z
Computing Home Information Systems & Computing Penn

  
  UPDATES
Current Load Status
Regular Availability
  
  INFORMATION
FAQs & Tips
Password Changer
Support services
Security
About the Data Warehouse
Data Administration
  
  DATA COLLECTIONS
Advancement
Assets
BRIM
Express Mail
Facilities
Faculty
GAR
General Ledger
Infrastructure
ISSS-iOffice
Learning Management
Position Inventory
Research-PennERA Proposals
Salary Management
Student
Travel Expense Management
Tuition Distribution
Cross-Collection

PennERA and Sponsor Conversion: effect on the Data Warehouse

August 25, 2003


Background

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 considered ‘duplicates’.


Sponsor Conversion

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 with ‘7’.

Accordingly, the following changes were made in the Data Warehouse:

FUND_CODES table

FUND_SPONSORS table

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’). That is:

  • 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 query results.


  • 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 sponsor type.


top

Information Systems and Computing
University of Pennsylvania
Comments & Questions


Penn Computing University of Pennsylvania
Information Systems and Computing, University of Pennsylvania