REMS Data Dictionary - Indirect Cost Exceptions Extract File

Extract File Overview

This data dictionary defines all fields included in the nightly REMS indirect cost exceptions extract file, REMS.IDC.DLY.xml, which is available to each UC campus or location. The field definitions are presented in the order in which the fields appear in the extract file.

Data Fields

INDIRECT_COST_EX_ID
6 digits, numeric. Represents the unique ID for the individual case-by-case exception.

OLD_INDIRECT_COST_EX_ID
6-7 characters, alphanumeric, 6-7 digits. Represents the legacy ID for older codes, when one existed, prior to transition to the current numeric IDC exception format. Otherwise, this field is left blank.

SPONSOR_CODE
4 characters, alphanumeric. Represents the sponsor code of the direct sponsor.
For Verified Sponsor Policies (EX_TYPE = "V"), this field may be left blank when the VSP is applicable to a Sponsor Category or Agency/Sub-Category rather than an individual sponsor code.

SUBAWARD_IND
"Y" or blank. "Y" indicates that UC is a subawardee and a prime sponsor is present. Blank indicates no prime sponsor.

SUBAWARD_SPONSOR_CODE
4 characters, alphanumeric. Represents the sponsor code of the prime sponsor when SUBAWARD_IND is "Y". Blank indicates no prime sponsor code.

LOCATION_CODE
1-2 digits, numeric. Represents the UC campus or location to which the exception applies.
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

PROJECT_TITLE
253 characters, alphanumeric. Indicates the title of the project.
For Verified Sponsor Policies (EX_TYPE = "V"), this field will be blank.

PRIN_INVESTIGATOR_FIRST_NAME
35 characters, alphanumeric. Indicates the first name of the principal investigator of the project.
For Verified Sponsor Policies (EX_TYPE = "V"), this field will be blank.

PRIN_INVESTIGATOR_LAST_NAME
35 characters, alphanumeric. Indicates the last name of the principal investigator of the project.
For Verified Sponsor Policies (EX_TYPE = "V"), this field will be blank.

CAMPUS_ON_OFF_IND
"ON", "OFF", or blank. "ON" indicates the project is conducted on-campus and the on-campus rate would normally apply. "OFF" indicates the project is conducted off-campus and the off-campus rate would normally apply.
For Verified Sponsor Policies (EX_TYPE = "V"), this field will be blank.

EX_TYPE
One character, shown as "C", "I", or "V".
"C" = Historical Class Exception (now deprecated), "I" = Individual/Case-by-Case, "V" = Verified Sponsor Policy

EX_REASON
One character, shown as "A", "C", "G", "S", "T", or blank.
"A" = Vital Interest, "C" = Sponsor Policy, "G" = Agricultural Interest, "S" = Special Approval, "T" = Campus Determination.
For Verified Sponsor Policies (EX_TYPE = "V"), this field will be blank.

ACTIVATED_DATE
Format YYYY-MM-DD HH:MM:SS.000000. Indicates date and time of approval of the exception.

APPROVER_TITLE
70 characters, alphanumeric. Title of approver when basis for approval is Vital Interest, Campus Determination, or Special Approval.

APPROVER_NAME
70 characters, alphanumeric. Name of approver when basis for approval is Vital Interest, Campus Determination, or Special Approval.

SUBMITTER_ID
Deprecated legacy field/no longer used.

SUBMITTER_NAME
Alphanumeric. Name of creator/submitter. May show as "Firstname Lastname" or "Lastname, Firstname" depending on campus/location.

SUBMITTAL_DATE
Deprecated field/no longer used. Values will normally show as "1900-01-01 00:00:00.000000".

UPDATED_DATE
Format YYYY-MM-DD HH:MM:SS.000000. Indicates the date of last modification or status change.

EX_STATUS
"AP", "DE", "SA", or "SU".
"AP" = Approved, "DE" = Deactivated, "SA" = Saved, "SU" = Submitted. Note that Deactivated exceptions are not currently included in the extract.

PROJECT_START_DATE
Format YYYY-MM-DD HH:MM:SS.000000. Indicates the anticipated start date of the project.

PROJECT_END_DATE
Format YYYY-MM-DD HH:MM:SS.000000. Indicates the anticipated end date of the project.

EXPIRATION_DATE
Format YYYY-MM-DD HH:MM:SS.000000. For Verified Sponsor Policies (EX_TYPE = "V"), this indicates the expiration date of the VSP. For Case-by-Case Exceptions, the value will be "1900-01-01 00:00:00.000000".

REQUESTED_EX_RATE
16 characters, numeric, format "0000000000000.00". Indicates the requested IDC rate as a percentage value. A 25.5% IDC rate would show as "0000000000025.50".

REQUESTED_EX_RATE_BASE_AMT
16 characters, numeric, format "0000000000000.00". Indicates the requested direct cost base amount as a dollar value. A base rate of $655,280 would show as "0000000655280.00". Will be blank when Optional Loss Calculator is not completed.

REQUESTED_EX_RATE_BASE_TYPE
Blank when no IDC is permitted and REQUESTED_EX_RATE = "0000000000000.00".

REQUESTED_EX_BASE_TYPE_NOTES
"BNA", "MTDC", "OT", "TC", "TDC", or blank.
"BNA" = Base Not Applicable, "MTDC" = Modified Total Direct Costs, "OT" = Other, "TC" = Total Costs, "TDC" = Total Direct Costs

REQUESTED_FIXED_FEE_AMT
16 characters, numeric, format "0000000000000.00". When a fixed IDC fee is offered in lieu of a rate, indicates the flat fee as a dollar value. A flat fee of $20,851 will show as "0000000020851.00". Otherwise, will be blank.

PROPOSAL_ID
32 characters, alphanumeric. Indicates the Proposal ID of the project.
For Verified Sponsor Policies (EX_TYPE = "V"), this field will be blank.

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

DEACTIVATED_DATE
Format YYYY-MM-DD HH:MM:SS.000000. Indicates the date of deactivation of an exception. Since Deactivated exceptions are not presently included in the extract, the date will show as "1900-01-01 00:00:00.000000" for all records.