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

SPONSOR_OLD_TO_NEW Table

Explanation
A static copy of the sponsor mapping table used to convert each 4-character sponsor code to its 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 legacy 4-character sponsor codes (used before August 25, 2003) includes those used in 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). In addition to the 4- and 5-character sponsor codes, the data includes the legacy sponsor name, the legacy and PennERA sponsor types, the code for the PennERA sponsor type, a flag indicating the post-conversion status of the legacy sponsor code, and the single 4-character code (the 'valid dup') now associated with the 5-character code. There is one record per 4-character sponsor code.

Common Uses
The job that refreshes the LEGACY_FUND_SPONSORS table in the Data Warehouse retrieves data from SPONSOR_OLD_TO_NEW. Most Warehouse users will find what they need in LEGACY_FUND_SPONSORS rather than in SPONSOR_OLD_TO_NEW. However, Warehouse users may query SPONSOR_OLD_TO_NEW when they are:

• Checking how the 4-character code is treated post-conversion (since August 25, 2003). ("Which 4-character codes were not used for any proposals, awards, or protocols? Which ones are now uniquely associated with a 5-character sponsor code? Which ones are 'duplicate' codes for a sponsor that has more than one 4-character code?")
• Finding the 5-character sponsor code now used instead of a legacy 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), 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 (This
   is the logical primary

   key.  The table does not

   have a physical primary key.)

FUND_SPONSOR_CODE 
FUND_SPONSOR_TYPE
INFOED_SPONSOR_TYPE_CODE
LEGACY_SPONSOR_CODE
LEGACY_SPONSOR_TYPE

AGENCY
AWARD
FUND_CODES
FUND_SPONSORS
LEGACY_FUND_SPONSORS
PROPOSAL

• The SPONSOR_OLD_TO_NEW table is a static copy of of the sponsor mapping table used to convert each 4-character sponsor code to its equivalent 5-character sponsor code. The contents of SPONSOR_OLD_TO_NEW will never change.

• The General Ledger began using the 5-character sponsor code on August 24, 2003. However, BRIM (the Billing and Receivables Information Management system used by the Office of Research Services) continues to use the 4-character sponsor code. The General Ledger stores not just the 5-character sponsor code, but also the 4-character sponsor code, in order to feed it to BRIM. Sponsors added after August 25, 2003 will have a 4-character code beginning with '7'. Records for those sponsors are not included in SPONSOR_OLD_TO_NEW. For a complete, up-to-date list of all 4-character sponsor codes (past and present) and their 5-character equivalents, see LEGACY_FUND_SPONSORS.

• The SPONSOR_OLD_TO_NEW 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 FUND_SPONSOR_DESC in SPONSOR_OLD_TO_NEW is the name associated with the 4-character sponsor code. It may differ from the name 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. For the name associated with the 5-character sponsor code, see FUND_SPONSORS.FUND_SPONSOR_DESC.

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

• The SPONSOR_OLD_TO_NEW 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 a LEGACY_SPON_CODE_STATUS of 'R'. Although some 'R' records have values for some of their other data elements, if LEGACY_SPON_CODE_STATUS is 'R', all the data elements other than LEGACY_SPONSOR_CODE and LEGACY_SPON_CODE_STATUS are likely to have null values.

• In addition to 'R' and null, LEGACY_SPON_CODE_STATUS may have a value of 'D'. This indicates that more than one 4-character code exists for the sponsor, and that, beginning August 25, 2003, only the 4-character code in LEGACY_SPON_CODE_VALID_DUP will be associated with the 5-character sponsor code. For sponsors with more than one 4-character code, the 'root' record is the one whose LEGACY_SPONSOR_CODE stores the 4-character code now associated with the 5-character code. All root records have a null value for LEGACY_SPON_CODE_STATUS. (Sponsors with only one 4-character code have just a root record, with a null value for LEGACY_SPON_CODE_STATUS.)
  • 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.
  • To determine whether a 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.

• 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 included in SPONSOR_OLD_TO_NEW were used before August 25, 2003 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. 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
The sponsor mapping table, UP_FND_TMP_SPONSOR. (This is the table that was used to convert sponsor data in BEN Financials' General Ledger on August 24, 2003.) The mapping table was based on UP_GL_SPONSOR_CODE_DFK in BEN Financials (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). The Office of Research Services also provided data for the mapping table by entering the values for the legacy sponsor code status, the valid dup., and the 5-character sponsor code.

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