REMS Data Dictionary - Sponsor Code Extract Files

Extract Files Overview

This data dictionary defines all fields included in the nightly REMS sponsor code extract files available to each UC campus or location. Two extract files are currently available: REMS.SPT.DLY.FULL.xml, which contains all fields, and the truncated legacy REMS.SPT.DLY.xml, which contains a subset of fields. The field definitions below are presented in the order in which the fields appear in the full extract file. Refer to our Sponsor Codes pages for additional information on many of the attributes, categories, and other characteristics described below.

Data Fields

SPONSOR_CODE
4 characters, alphanumeric. Indicates the unique sponsor code ID of the entity.

GUIDANCE_INDICATOR
"1" or blank. A "1" value indicates that Sponsor Guidance is present for that sponsor code in REMS.

SPONSOR_NAME_KEY
70 characters, alphanumeric. Field internal for REMS use. Identical to SPONSOR_NAME field.

SPONSOR_NAME
70 characters, alphanumeric. Indicates the primary, official, or legal name of the entity.

SPONSOR_CATEGORY_CODE
1-2 digits, numeric. Represents the primary sponsor category (federal, state, for-profit, non-profit, etc.). See the Sponsor Category matrix for all sponsor category, agency, and sub-agency codes.

SPONSOR_CFS_ONLY_INDICATOR
"Y" or blank. A "Y" value indicates the code is a "miscellaneous sponsors" code intended for use in campus and Corporate Financial Systems only and is not valid for SPX.

SPONSOR_AGENCY_CODE
2 digits, numeric, or blank. Represents subcategory identifier (e.g., government type or federal agency-level). See the Sponsor Category matrix for all sponsor category, agency, and sub-agency codes.

SPONSOR_SUB_AGENCY_CODE
2 digits, numeric, or blank. Represents further sub-agency identifier of certain federal agencies. See the Sponsor Category matrix for all sponsor category, agency, and sub-agency codes.

SPONSOR_FOREIGN_IND
"Y" or blank. A "Y" value indicates the entity is located outside the U.S.

SPONSOR_FOREIGN_COUNTRY
Legacy field with name of country outside the U.S. This field is no longer in use and should not be relied on. Use the SPONSOR_COUNTRY_ISO_CODE field to identify the correct country.

SPONSOR_NAME_ALTERNATE
70 characters, alphanumeric. Indicates alternate, dba, or former names when applicable.

PARENT_SPONSOR_CODE
4 characters, alphanumeric, or blank. When present, indicates the sponsor code of the immediate owner/parent of the entity.

ADD_SOURCE
Alphanumeric. Indicates the User ID of the individual who created the code. May be blank for some legacy codes.

ADD_DATE
Format YYYY-MM-DD HH:MM:SS.000000. Indicates the date the code was created.

CHANGE_SOURCE
Alphanumeric. Indicates the User ID of the individual who last modified the code. Will be blank if never modified.

CHANGE_DATE
Format YYYY-MM-DD HH:MM:SS.000000. Indicates the date the code was last modified. If never modified, this value will be the same as the ADD_DATE or will show as "1900-01-01 00:00:00.000000".

REQUESTING_LOCATION
2 digits, numeric. Represents the UC campus or location which originally requested the code.
1 = Berkeley, 2 = San Francisco, 3 = Davis, 04 = Los Angeles, 05 = Riverside, 06 = San Diego, 07 = Santa Cruz, 08 = Santa Barbara, 09 = Irvine, 20 = Office of the President, 21 = Lawrence Berkeley Lab, 22 = Lawrence Livermore Lab, 23 = Los Alamos Lab, 52 = Agriculture & Natural Resources

COMMENT
4000 characters, alphanumeric. Includes any relevant additional information regarding the sponsor, including programs managed by the entity, additional alternate names, restricted party screening details, etc. Note: Non-letter/number/space characters that may interfere with use of the XML file are removed from the comments during export. Some comments may therefore be missing some punctuation, and certain "nonsense" letters may appear as REMS now allows some HTML formatting within the comments section.

REQUEST_DATE
Unused legacy field. All values will show as "1900-01-01 00:00:00.000000".

REPLACEMENT_SPONSOR_CODE
4 characters, alphanumeric. For Deactivated codes (SPONSOR_STATUS = "DE"), indicates the code that should be used instead. Note that this is not added for ALL Deactivated codes. In the future, will also be available when a sponsor code is marked "Do Not Use" (DONOTUSE_FLAG = "y").

SPONSOR_STATUS
"AP", "DE", "SA", or "SU". Indicates the approval status or otherwise of the sponsor code.
"AP" = Approved, "DE" = Deactivated, "SA" = Saved, "SU" = Submitted. Only Approved codes are valid for SPX submission.

PROJECT_TYPE
Unused field. Legacy data will show as "1" or blank.

UCOP_REVIEW_DATE
Unused legacy field. All values will show as "1900-01-01 00:00:00.000000".

UCOP_REVIEW_STATUS
Unused legacy field. All values will be blank.

SPONSOR_DUNS
10 characters, alphanumeric, or blank. May include the numeric identifier managed by Dun & Bradstreet that identifies individual business entities globally, previously used in SAM.gov and for other federal funding purposes but now replaced by the Unique Entity Identifier.

ADDRESS1
50 characters, alphanumeric. Indicates the first-level street address of the entity. Required for new codes, but may be blank for older codes.

ADDRESS2
50 characters, alphanumeric. Indicates the second-level street address of the entity. Optional field which may be blank.

ADDRESS3
50 characters, alphanumeric. Indicates the third-level street address of the entity. Optional field which may be blank.

ADDRESS4
50 characters, alphanumeric. Indicates the fourth-level street address of the entity. Optional field which may be blank.

CITY
30 characters, alphanumeric. Indicates the city/locality in which the entity is located. Required for new codes, but may be blank for older codes.

STATE
40 characters, alphanumeric.
For entities located in U.S. states and territories, the two-character standard abbreviation (e.g., CA, DE, NY, PR) will be used. This is a mandatory field for U.S.-based entities.
For entities located outside the U.S., this is an optional field for subnational political divisions (state, province, county, prefecture, etc.) but will be populated into new and existing codes over time. Generally, the name of the state/province will be spelled out in full (e.g., Manitoba, Baja California, Queensland).

POSTAL_CODE
10 characters, alphanumeric.
For U.S.-based entities, new codes must follow the ZIP+4 format (99999-9999). Older codes may only show the 5-digit ZIP code (99999) or, in some cases, may be blank.
For entities located outside the U.S., this is an optional field. Formats will vary among countries or may be blank.

URL
150 characters, alphanumeric. Indicates the entity's main website, when available. May be blank for older codes or if the entity has no website.

APPROVER_ID
Indicates the User ID of the individual who approved/activated the code.

APPROVAL_DATE
Format YYYY-MM-DD HH:MM:SS.000000. Indicates the date the code was approved/activated. Will be blank for codes in the Saved (SPONSOR_STATUS = "SA") and Submitted (SPONSOR_STATUS = "SU") states. May be blank for some older codes in Approved (SPONSOR_STATUS = "AP") and Deactivated (SPONSOR_STATUS = "DE") states.

SPONSOR_ACRONYM
10 characters, alphanumeric. Indicates an entity's commonly known acronym. May be blank.

DEACTIVATED_SOURCE
Indicates the User ID of the individual who deactivated the code when SPONSOR_STATUS = "DE".

DEACTIVATED_DATE
Format YYYY-MM-DD HH:MM:SS.000000. Indicates the date of deactivation when SPONSOR_STATUS = "DE". In all other states, the value will show as "1900-01-01 00:00:00.000000".

ADD_SOURCE_NAME
70 characters, alphanumeric. Indicates the name of the individual who created the code. Will be blank for older codes.

ADD_SOURCE_EMAIL
50 characters, alphanumeric. Indicates the email address of the individual who created the code. Will be blank for older codes.

ADD_SOURCE_PHONE
13 characters, alphanumeric. Indicates the phone number of the individual who created the code. Will be blank for older codes.

CRO_FLAG
"y" or blank. A "y" value indicates the entity provides support to UC or other outside organizations in the form of research services outsourced on a contract basis.

DONOTUSE_FLAG
"y" or blank. A "y" value indicates that the sponsor code should not be used and will be deactivated once any ongoing/active awards have terminated. Details are provided in the DONOTUSEADDL_INSTRUCTIONS field.

DONOTUSEADDL_INSTRUCTIONS
2000 characters, alphanumeric. When DONOTUSE_FLAG = "y", additional information including replacement codes or other instructions are provided here.

COI_FLAG (Entity of Concern)
"y" or blank. A "y" value indicates that the "Entity of Concern" indicator is marked in REMS and that staff should contact their local Export Control office for additional information/direction. The "COI_FLAG" field was repurposed when the "Entity of Concern" identifier was created and has been left as such in order to avoid causing issues in campus systems.

SPONSOR_EIN
10 digits, numeric. Represents the Employer Identification Number of the entity in the format 99-9999999. May be blank.

SPONSOR_COUNTRY_ID
1-3 digits, numeric. Represents the internal country identifier used in REMS. Will correspond to the SPONSOR_COUNTRY_ISO_CODE field, which should be used to identify the country.

AGRICULTURAL_ENTITY_FLAG
"Y", "N", or blank. A "Y" value indicates that the entity is subject to UC's practice regarding collection of indirect costs from agricultural commodity groups. A blank value will equal a "N" value.

DOE_NATIONAL_LABORATORIES_FLAG
"Y", "N", or blank. A "Y" value indicates that the entity operates/manages a Department of Energy (DOE) National Laboratory. A blank value will equal a "N" value.

DISREGARDED_ENTITY_FLAG
"Y", "N", or blank. A "Y" value Single-Member Limited Liability Companies (LLC) which is wholly owned by a single, tax-exempt parent entity, and which thus may be treated as non-profit entity despite lacking its own tax-exempt status. A blank value will equal a "N" value.

SPONSOR_UEI
12 characters, alphanumeric. Indicates the entity's Unique Entity Identifier assigned through SAM.gov. May be blank.

SPONSOR_COUNTRY_ISO_CODE
3 characters, alphabetical. Indicates the 3-digit ISO-3166 code of the country in which the entity is registered or located. This is the primary source for identifying the country.

FOREIGN_OWNED_FLAG
"Y" or "N". A "Y" value indicates that the entity is U.S.-based but is known to be owned or controlled by an entity outside the U.S.

COUNTRY_OF_CONCERN_FLAG
"Y" or "N". A "Y" value indicates that the entity is located in, or is known to be owned or controlled by an entity located in, a foreign country of concern as defined under the CHIPS and Science Act of 2022.