Penn Computing

University of Pennsylvania
Penn Computing << go backback
LEGACY_FUND_SPONSORS Table   Tables and Data Elements   Sponsored Projects Home   Data Warehouse Home

LEGACY_FUND_SPONSORS Table - Data Element Index

Select a data element to view its definition and its indexed, format, and null values.

 

Data element Definition
FUND_SPONSOR_CODE

Indexed - yes
Format - char (5)
May be null? yes

The 5-digit code that uniquely identifies the sponsor, per PennERA (the Electronic Research Administration system in use since August 25, 2003). The sponsor may be the agency to which a proposal has been sent, or from which an award has been received. The sponsor may also be the agency that might fund, or has funded, a project involving a protocol (such as a human subjects protocol).

Codes less than or equal to 599999 were assigned to the sponsor by InfoEd (the vendor of the PennERA software). Codes greater than or equal to 60000 were assigned to the sponsor by the Office of Research Services.

Note that the FUND_SPONSOR_CODE may be null if no proposals, awards, protocols, or General Ledger fund records have used the 4-character LEGACY_SPONSOR_CODE.

See also LEGACY_SPONSOR_CODE.

Example: 09470 (NATIONAL INSTITUTES OF HEALTH).

Values:
Refer to the LEGACY_FUND_SPONSORS Table for values.
Source: For 4-character sponsor codes that are in the General Ledger, FUND_SPONSOR_CODE comes from FUND_SPONSORS.FUND_SPONSOR_CODE. (If LEGACY_SPON_CODE_VALID_DUP is null, the source record is the one where LEGACY_FUND_SPONSORS.LEGACY_SPONSOR_CODE = FUND_SPONSORS.LEGACY_FUND_SPONSOR_CODE. If LEGACY_SPON_CODE_VALID_DUP is not null, the source record is the one where LEGACY_FUND_SPONSORS.LEGACY_SPON_CODE_VALID_DUP = FUND_SPONSORS.LEGACY_FUND_SPONSOR_CODE.) For LEGACY_SPONSOR_CODEs not in the General Ledger, the data comes from SPONSOR_OLD_TO_NEW.FUND_SPONSOR_CODE, where LEGACY_FUND_SPONSORS.LEGACY_SPONSOR_CODE = SPONSOR_OLD_TO_NEW.LEGACY_SPONSOR_CODE.
FUND_SPONSOR_DESC

Indexed - no
Format - varchar2 (150)
May be null? yes

The 150-character name of the sponsor. This is the name associated with the FUND_SPONSOR_CODE (the 5-character code), and is the name used in PennERA (the Electronic Research Administration system in use since August 25, 2003). The sponsor may be the agency to which a proposal has been sent, or from which an award has been received. The sponsor may also be the agency that might fund, or has funded, a project involving a protocol (such as a human subjects protocol).

Note that the FUND_SPONSOR_DESC may be null if the 4-character LEGACY_SPONSOR_CODE is not in the General Ledger, or if no proposals, awards, protocols, or General Ledger fund records have used the 4-character LEGACY_SPONSOR_CODE.

See also LEGACY_SPONSOR_DESC.

Example: NATIONAL INSTITUTES OF HEALTH (09470).


Values:
Refer to the LEGACY_FUND_SPONSORS Table for values.
Source: For 4-character sponsor codes that are in the General Ledger, FUND_SPONSOR_DESC comes from FUND_SPONSORS.FUND_SPONSOR_DESC. (If LEGACY_SPON_CODE_VALID_DUP is null, the source record is the one where LEGACY_FUND_SPONSORS.LEGACY_SPONSOR_CODE = FUND_SPONSORS.LEGACY_FUND_SPONSOR_CODE. If LEGACY_SPON_CODE_VALID_DUP is not null, the source record is the one where LEGACY_FUND_SPONSORS.LEGACY_SPON_CODE_VALID_DUP = FUND_SPONSORS.LEGACY_FUND_SPONSOR_CODE.) For LEGACY_SPONSOR_CODEs not in the General Ledger, the value will be null.
FUND_SPONSOR_TYPE

Indexed - no
Format - varchar2 (50)
May be null? yes

The 50-character description of the sponsor type, per PennERA (the Electronic Research Administration system in use since August 25, 2003). This is used to group sponsors according to their business.

Note that the FUND_SPONSOR_TYPE may be null if no proposals, awards, protocols, or General Ledger fund records have used the 4-character LEGACY_SPONSOR_CODE.

See also LEGACY_SPONSOR_TYPE.

Example: UPENN Federal


Values:
UPENN Commercial/Industrial
UPENN Federal
UPENN Foreign Governments
UPENN Foundations
UPENN Hospital/Medical Centers
UPENN Internal
UPENN Other
UPENN Other Non-Federal Governments
UPENN Other Non-Profit Organizations
UPENN Private Associations
UPENN Universities
Source: For 4-character sponsor codes that are in the General Ledger, FUND_SPONSOR_TYPE comes from FUND_SPONSORS.FUND_SPONSOR_TYPE. (If LEGACY_SPON_CODE_VALID_DUP is null, the source record is the one where LEGACY_FUND_SPONSORS.LEGACY_SPONSOR_CODE = FUND_SPONSORS.LEGACY_FUND_SPONSOR_CODE. If LEGACY_SPON_CODE_VALID_DUP is not null, the source record is the one where LEGACY_FUND_SPONSORS.LEGACY_SPON_CODE_VALID_DUP = FUND_SPONSORS.LEGACY_FUND_SPONSOR_CODE.) For LEGACY_SPONSOR_CODEs not in the General Ledger, the data comes from SPONSOR_OLD_TO_NEW.FUND_SPONSOR_TYPE, where LEGACY_FUND_SPONSORS.LEGACY_SPONSOR_CODE = SPONSOR_OLD_TO_NEW.LEGACY_SPONSOR_CODE.
LEGACY_SPON_CODE_VALID_DUP

Indexed - yes
Format - char (4)
May be null? yes

The 4-character sponsor code to be associated with the 5-character FUND_SPONSOR_CODE when records are created in the General Ledger after PennERA is implemented (August 25, 2003). BRIM (the Billing and Receivables Information Management system used by the Office of Research Services) continues to use this code.

Note that LEGACY_SPON_CODE_VALID_DUP will be null if the 4-character code is to be removed from the source system, or if the LEGACY_SPONSOR_CODE itself is the one that is associated with the 5-character FUND_SPONSOR_CODE for records created in the General Ledger after August 25, 2003 (when PennERA was implemented). (The 4-character code was to be removed from the source system if no proposals, awards, protocols, or General Ledger fund records have used the 4-character code.)

To find the 4-character sponsor code (the 'valid dup') associated with a given 5-character code, regardless of whether the sponsor has more than one 4-character code, select LEGACY_SPONSOR_CODE where LEGACY_SPON_CODE_VALID_DUP is null and FUND_SPONSOR_CODE is the desired 5-character code.

Example: 1110 (National Institutes of Health).

Values:
Refer to the LEGACY_FUND_SPONSORS Table for values.
Source: SPONSOR_OLD_TO_NEW.LEGACY_SPON_CODE_VALID_DUP where LEGACY_FUND_SPONSORS.LEGACY_SPONSOR_CODE = SPONSOR_OLD_TO_NEW.LEGACY_SPONSOR_CODE
LEGACY_SPONSOR_CODE

Indexed - yes
Format - char (4)
May be null? no

The 4-character code that identifies the sponsor. The sponsor may be the agency to which a proposal was sent, or from which an award was received. The sponsor may also be the agency that might fund, or did fund, a project involving a protocol (such as a human subjects protocol). BRIM (the Billing and Receivables Information Management system used by the Office of Research Services) continues to use this 4-character code (or the one specified as the LEGACY_SPON_CODE_VALID_DUP).

Each sponsor has only one 5-character FUND_SPONSOR_CODE, but, before PennERA was implemented (August 25, 2003), more than one 4-character LEGACY_SPONSOR_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 LEGACY_FUND_SPONSORS record for 1517 has a LEGACY_SPON_CODE_VALID_DUP of '4151'. (The data element is null for the record for 4151.)

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 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. Sponsors added after PennERA was implemented (August 25, 2003) have will have a LEGACY_SPONSOR_CODE that starts with '7', and will have a null value for LEGACY_SPONSOR_TYPE. The current (PennERA) sponsor type is stored in FUND_SPONSOR_TYPE.

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.

See also FUND_SPONSOR_CODE.

Example: 1493 (NATIONAL CANCER INSTITUTE).


Values:
Refer to the LEGACY_FUND_SPONSORS Table for values.
Source: For 4-character sponsor codes that are in the General Ledger, LEGACY_SPONSOR_CODE comes from UP_GL_SPONSOR_CODE_DFK in BEN Financials (the General Ledger value set for the 4-character sponsor codes). For LEGACY_SPONSOR_CODEs not in the General Ledger, the data comes from SPONSOR_OLD_TO_NEW.LEGACY_SPONSOR_CODE.
LEGACY_SPONSOR_DESC

Indexed - no
Format - char2 (70)
May be null? yes

The 70-character name of the sponsor. This is the name associated with the LEGACY_SPONSOR_CODE (the 4-character code). The sponsor may be the agency to which a proposal has been sent, or from which an award has been received. The sponsor may also be the agency that might fund, or has funded, a project involving a protocol (such as a human subjects protocol).

Note that the LEGACY_SPONSOR_DESC may be null if no proposals, awards, protocols, or General Ledger fund records have used the 4-character LEGACY_SPONSOR_CODE.

See also FUND_SPONSOR_DESC.

Example: NATIONAL CANCER INSTITUTE (1493).


Values:
Refer to the LEGACY_FUND_SPONSORS Table for values.
Source: For 4-character sponsor codes that are in the General Ledger, LEGACY_SPONSOR_DESC comes from UP_GL_SPONSOR_CODE_DFK in BEN Financials (the General Ledger value set for the 4-character sponsor codes). For LEGACY_SPONSOR_CODEs not in the General Ledger, the data comes from SPONSOR_OLD_TO_NEW.FUND_SPONSOR_DESC.
LEGACY_SPONSOR_TYPE

Indexed - no
Format - varchar2 (50)
May be null? yes

The 50-character description of the sponsor type, derived from the 4-character LEGACY_SPONSOR_CODE. This is used to group sponsors according to their business, and to identify federal flow through funds. (A federal flow through fund tracks money received from a sponsor that itself got the money from the U.S. federal government.)

Note that the value of LEGACY_SPONSOR_TYPE will be null for any LEGACY_SPONSOR_CODE that does not fall between 1000 and 5999, inclusive. Sponsors added after PennERA was implemented (August 25, 2003) have will have a LEGACY_SPONSOR_CODE that starts with '7', and will have a null value for LEGACY_SPONSOR_TYPE.

See also FUND_SPONSOR_TYPE.

Example: Federal

Values:
Federal                       LEGACY_SPONSOR_CODE 1000 - 1499,
                              inclusive
Federal Flow Through          LEGACY_SPONSOR_CODE 1500 - 1999,
                              inclusive
Corporations                  LEGACY_SPONSOR_CODE 2000 - 2999,
                              inclusive
Foundations                   LEGACY_SPONSOR_CODE 3000 - 3999,
                              inclusive
Private Associations          LEGACY_SPONSOR_CODE 4000 - 4499,
                              inclusive
Universities                  LEGACY_SPONSOR_CODE 4500 - 4999,
                              inclusive
Other non-Federal Governments LEGACY_SPONSOR_CODE 5000 - 5499,
                              inclusive
Foreign Governments           LEGACY_SPONSOR_CODE 5500 - 5999,
                              inclusive
[null]                        all other values of LEGACY_SPONSOR_CODE

 

LEGACY_FUND_SPONSORS Table   Tables and Data Elements   Sponsored Projects Home   Data Warehouse Home

Questions about this page? Email us at da-staff@isc.upenn.edu

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