LEGACY_FUND_SPONSORS Table - Data Element Index    Tables and Data Elements   Sponsored Projects Home   Data Warehouse Home

LEGACY_FUND_SPONSORS Table

Explanation
A complete list of 4-character sponsor codes, each mapped to the equivalent 5-character sponsor code. The 5-character sponsor code has been used since August 25, 2003, when PennERA (Electronic Research Administration) was implemented. The list of 4-character sponsor codes includes those used in the General Ledger. It also includes legacy codes that were used in 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). The 4-character sponsor code is still stored in the General Ledger to be fed to BRIM (the Billing and Receivables Information Management system used by the Office of Research Services). In addition to the 4- and 5-character sponsor codes, the data includes the legacy and PennERA sponsor names, the legacy and PennERA sponsor types, and the single legacy code (the 'valid dup') now associated with the 5-character code. There is one record per 4-character sponsor code.

Common Uses

• Finding the 5-character sponsor code associated with a 4-character code. ("What's the 5-character sponsor code for 4686, the Massey Cancer Center?")
• Finding the 4-character code now associated with a 5-character code. ("What's the valid dup. for 09470, the National Institutes of Health?")
• Finding all the 4-character codes that were used for a sponsor. ("What legacy codes were used for the National Institutes of Health (09470)?")
• Retrieving the legacy sponsor type, or the current sponsor type, for a sponsor. ("List all the legacy sponsor codes that had a legacy sponsor type of 'Federal'. List all the legacy sponsor codes now assigned to sponsor type 'UPENN Universities'.")
• Displaying the legacy sponsor name (associated with the 4-character code) or the current sponsor name (associated with the 5-character sponsor code), rather than the code that appears in a related table. ("Don't show the codes in the report. Show the English instead.")

LEGACY_SPONSOR_CODE

FUND_SPONSOR_CODE
LEGACY_SPONSOR_CODE
LEGACY_SPON_CODE_VALID_DUP

AGENCY
AWARD
FUND_CODES
FUND_SPONSORS
PROPOSAL
SPONSOR_OLD_TO_NEW

• The LEGACY_FUND_SPONSORS table has one record per 4-character sponsor code, but it may have more than one record for a particular sponsor. Each sponsor has only one 5-character code, but, in the past, more than one 4-character code could be used for the same sponsor. For example, 4151 was used when the Children’s Hospital of Philadelphia (CHOP) was the source of the financial resources for a fund. But 1517 was used when the funding came from CHOP, but the source of those resources was actually U.S. federal government. In other words, 1517 was used for CHOP funds that had “federal flow through” money. In this example, the SPONSOR_OLD_TO_NEW record for 1517 has a LEGACY_SPON_CODE_VALID_DUP of '4151'. (This data element is null for the record for 4151.)

• The LEGACY_FUND_SPONSORS table includes more than the legacy sponsor codes (those used in the General Ledger, RSS, or PPM as of August, 2003). When PennERA was implemented, BRIM (the Billing and Receivables Information Management system used by the Office of Research Services) continued to use the 4-character sponsor code (the 'valid dup') fed to it from the General Ledger. As new sponsors are added, they are assigned not only a 5-character code, but also a 4-character code that begins with a '7'. The SPONSOR_OLD_TO_NEW table includes these ‘7’ codes.

• When querying sponsor names, be sure to use the data element that meets your needs.The LEGACY_SPONSOR_DESC (associated with the 4-character sponsor code) may differ from the FUND_SPONSOR_DESC (associated with the 5-character sponsor code). For example, the name associated with 1537 is CASE WESTERN RESERVE UNIVERSITY, but the name associated with 52007 is CASE WESTERN UNIVERSITY. Also, LEGACY_SPONSOR_DESC may be up to 79 characters long, but FUND_SPONSOR_DESC may be up to 150 characters long.

• The LEGACY_FUND_SPONSORS table includes all the 4-character sponsor codes that existed as of August 24, 2003 in the General Ledger value set for the 4-character sponsor codes), the table of agency codes in RSS (the Research Services system, used by the Office of Research Services), and the sponsor table in PPM (the Penn Protocol Manager, used by the Office of Regulatory Affairs). However, some of these 4-character codes were never used in records for proposals, awards, protocols, or funds. (Those 4-character codes have SPONSOR_OLD_TO_NEW records with a LEGACY_SPON_CODE_STATUS of 'R', indicating that they should be removed from their source system.). Although some records for 'never used' 4-character codes have values for some of their other data elements, for most 'never used' 4-character codes, all the data elements other than LEGACY_SPONSOR_CODE are likely to have null values.

• To determine whether a sponsor had more than one 4-character code
  • given the 5-character code for the sponsor, select LEGACY_SPONSOR_CODE where FUND_SPONSOR_CODE is the desired 5-character code. If the query returns more than one record, the sponsor had more than one 4-character code.
  • given a 4-character code for the sponsor, select LEGACY_SPON_CODE_VALID_DUP where LEGACY_SPONSOR_CODE is the desired 4-character code. If LEGACY_SPON_CODE_VALID_DUP is not null, the sponsor had more than one 4-character code. If LEGACY_SPON_CODE_VALID_DUP is null, run a new query, selecting LEGACY_SPON_CODE_VALID_DUP where LEGACY_SPON_CODE_VALID_DUP is the desired 4-character code. If there are any records, the sponsor had more than one 4-character code.

• To find the 4-character sponsor code (the 'valid dup') associated with a given 5-character code, select LEGACY_SPONSOR_CODE where LEGACY_SPON_CODE_VALID_DUP is null and FUND_SPONSOR_CODE is the desired 5-character code.

• In the past, a fund's federal flow through status was indicated by a 4-character sponsor code between 1500 and 1999, inclusive. Now that unique 5-character sponsor codes are used, a fund’s federal flow through status is indicated by a new fund attribute (see FUND_CODES.FEDERAL_FLAG).

• In the past, the sponsor type could be deduced from the 4-character sponsor code. To facilitate queries using the legacy sponsor type, the decoded value of the sponsor type based on the 4-character sponsor code is stored in LEGACY_SPONSOR_TYPE. (Note that the value will be null for any legacy code that does not fall between 1000 and 5999, inclusive.) The current sponsor type is stored in FUND_SPONSOR_TYPE. Note that the list of values for LEGACY_SPONSOR_TYPE differs from the list of values for FUND_SPONSOR_TYPE. For example, the LEGACY_SPONSOR_TYPE for Children’s Hospital of Philadelphia (CHOP) records with sponsor code 4151 is 'Private Associations'. The FUND_SPONSOR_TYPE for CHOP (61399) is 'UPENN Hospital/Medical Centers'.

• The 4-character sponsor codes were used in BEN Financials (the General Ledger), 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). However, not every 4-character sponsor code value was used in all three systems. All three systems used numeric codes beginning with a number between '1' and '5', inclusive. Numeric codes beginning with '7' are used for sponsors added to the General Ledger after PennERA was implemented (August 25, 2003). Other numeric codes were used by PPM only. Codes that begin with a letter ('X' or 'Y') were used by RSS, and refer only to agencies that sponsored projects before July 1, 1996, but no longer exist.

Source
UP_GL_SPONSOR_CODE_DFK in BEN Financials (the General Ledger value set for the 4-character sponsor codes), and the Data Warehouse FUND_SPONSORS and SPONSOR_OLD_TO_NEW tables.

LEGACY_FUND_SPONSORS Table - Data Element Index    Tables and Data Elements   Sponsored Projects Home   Data Warehouse Home