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
|
Questions about this page? Email us at da-staff@isc.upenn.edu
|