Penn Computing

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

SPONSOR_OLD_TO_NEW 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 FUND_SPONSOR_CODE may be null if no proposals, awards, protocols, or General Ledger fund records used the 4-character LEGACY_SPONSOR_CODE (that is, if LEGACY_SPON_CODE_STATUS is 'R').

Example: 09470 (National Institutes of Health).

See also LEGACY_SPONSOR_CODE.



Values:
Refer to the SPONSOR_OLD_TO_NEW Table for values.
FUND_SPONSOR_DESC

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

The 68-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 FUND_SPONSOR_DESC may be null if no proposals, awards, protocols, or General Ledger fund records used the 4-character LEGACY_SPONSOR_CODE (that is, if LEGACY_SPON_CODE_STATUS is 'R').

Example: NATIONAL CANCER INSTITUTE (1493).


Values:
Refer to the SPONSOR_OLD_TO_NEW Table for values.
FUND_SPONSOR_TYPE

Indexed - yes
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.

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
      
INFOED_SPONSOR_TYPE_CODE

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

The 2-character code associated with the sponsor type, per PennERA (the Electronic Research Administration system in use since August 25, 2003). InfoEd is the vendor of the PennERA software. INFOED_SPONSOR_TYPE_CODE is not used in other tables in the Data Warehouse.

See also FUND_SPONSOR_DESC.

Example: 40 (UPENN Federal)


Values:
33  UPENN Commercial/Industrial
40  UPENN Federal
41  UPENN Foundations
42  UPENN Private Associations
43  UPENN Universities
44  UPENN Hospital/Medical Centers
45  UPENN Other Non-Federal Governments
46  UPENN Foreign Governments
47  UPENN Other
48  UPENN Other Non-Profit Organizations
49  UPENN Internal
      
LEGACY_SPON_CODE_STATUS

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

The 1-character code that indicates how the LEGACY_SPONSOR_CODE was treated after sponsor-related data was converted 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) as of August 25, 2003.


Values:


[null]  When new records are created in the General Ledger,
        associate this 4-character LEGACY_SPONSOR_CODE with
        the 5-character FUND_SPONSOR_CODE.


D       More than one 4-character LEGACY_SPONSOR_CODE exists
        for this sponsor.  When records are created in the 
        General Ledger after PennERA is implemented 
        (August 25, 2003), associate the 4-character 
        LEGACY_SPON_CODE_VALID_DUP with the 5-character 
        FUND_SPONSOR_CODE.
        

R       No proposals, awards, protocols, or General Ledger fund 
        records use this 4-character LEGACY_SPONSOR_CODE.  The
        4-character code ought to be removed from the source 
		system (RSS, PPM, or the General Ledger).
LEGACY_SPON_CODE_VALID_DUP

Indexed - no
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.

LEGACY_SPON_CODE_VALID_DUP is populated only if LEGACY_SPON_CODE_STATUS is 'D'. 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 is the one that is associated with the 5-character FUND_SPONSOR_CODE when records are created in the General Ledger after PennERA is implemented.

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

See also LEGACY_SPON_CODE_STATUS and LEGACY_SPONSOR_CODE.

Example: 1110 (National Institutes of Health).

Values:
Refer to the SPONSOR_OLD_TO_NEW Table for values.
LEGACY_SPONSOR_CODE

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

The 4-character code that identified the sponsor, prior to the implementation of PennERA (the Electronic Research Administration system in use since August 25, 2003). 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, in the past, 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 SPONSOR_OLD_TO_NEW record for 1517 has a LEGACY_SPON_CODE_STATUS of 'D' and a LEGACY_SPON_CODE_VALID_DUP of '4151'. (Both data elements are 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. The current sponsor type is stored in FUND_SPONSOR_TYPE.

The legacy 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 legacy 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.

See also FUND_SPONSOR_CODE.

Example: 1493 (NATIONAL CANCER INSTITUTE).


Values:
Refer to the SPONSOR_OLD_TO_NEW Table for values.
LEGACY_SPONSOR_TYPE

Indexed - yes
Format - varchar2 (30)
May be null? yes

The 30-character description of the sponsor type, based on 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.

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
SPONSOR_OLD_TO_NEW 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