Grant Master Data Conversion Guide
August 2009
Grant Master Conversion Overview
This document will explain Master Data Elements for Grants. This will be the basis for creating the files for the data that will be loaded into SAP. The fields described are the fields which will be on the upload file templates.
Two file templates have been developed that will be passed to the agencies. The first file contains header information (grant type, CFDA, Cost Sharing rule, etc.) for the grant. In this file, there will only be one line per grant. The second file will contain repetitive data, such as Fund, Sponsored Class, and Cost Sharing rates. There could be 3 Funds, 2 Sponsored Programs, and 14 Sponsored Classes, for example, which would result in 14 line items in the file for the grant. The number of lines in this file will depend on the grant.
Both file templates will be filled in by the agencies, saved at tab-delimited text files, and uploaded by the SCEIS conversion team.
Data Cleansing
Data cleansing will be required in order to eliminate duplicates and inactive obsolete data. Therefore data cleansing and data analysis by the agencies will be required prior to loading the grant master data.
Legacy Data Extraction and Mapping
The field definitions below are to be used in identifying what legacy data corresponds to the SCEIS data and should aid with the mapping process. This list includes the attributes of the SCEIS fields (length and type) and whether the data is mandatory or optional.
Grant Master Field Definitions
File Format #1:
Description / Reqd/Opt / File Field Length / File-specific notes
Grant / R / 12 / Pos 1-4: Bus Area
Pos 5: grant type (1, 2, 3, 4,5)
Pos 6-10: agency choice
Pos 11-12: FY
Grant Type / R / 2 / "G1" - Federal
"G2" - State
"G3" - Non-Government
"G4" - Recd from Non-SC Agencies
"G5" - Sub-Grant
External Reference / O / 24
Internal Reference / R / 24 / Project/Phase (include leading zeroes)
Grant Sponsor / R / 7
Grant validity from date / R / 8 / YYYYMMDD
Grant Validity To Date / R / 8 / YYYYMMDD
Rule Used as a Basis for Billing / R / 1 / "6": manual billing
Award Type / R / 3 / "OTH" - Other
"RND" - R&D
Grant Value / R / 13 / XXXXXXXXXXX.XX
Number in Catalog of Federal Domestic Assistance / O / 6
Fiscal year variant of grant / R / 2 / "SC"
Rule for Determining Indirect Costs / O / 1 / "1" - Based on External Funds only
“2” – Based on Internal and External Funds
“Blank”
Rule for Determining Cost Sharing Requirements / O / 1 / "1" - based on direct cost
"2" - based on direct and indirect cost
Cost Sharing Rate / O / 6 / XX.XXX
Origin of Grant Funding / O / 30
Letter of Credit / O / 20
Name / R / 20
Description / R / 80
Start Date / R / 8 / YYYYMMDD
End Date / R / 8 / YYYYMMDD
Report on SFFA? / O / 1 / "X" or blank
R&D Award / O / 1 / "Y" or "N"
Fund Source Code / O / 1 / "A", "B", "C", or blank
Program Contact1 / O / 25
Program Contact2 / O / 25
Originating Sponsor / O / 7
Sub-recipient Agency / O / 4
Ref1 / O / 50
Ref2 / O / 50
Ref3 / O / 50
Ref4 / O / 50
File Format #2
Description / Reqd/Opt / File Field Length / File-specific notes
Grant / R / 12 / Used to link the files
Fund / O / 8 / Must be at least one Fund
Indirect Cost Recover Fund / O / 1 / "X" or blank
Cost sharing fund / O / 1 / "X" or blank
GM Program Income Fund / O / 1 / "X" or blank
Sponsored Program / O / 20 / Must be at least one Spon Pgm
Sponsored Class / O / 20 / Must be at least one Spon Class
Contributes to Indirect Cost Calculations / O / 1 / "X" or blank: relative to Sponsored Class on same line
Cost Sharing Fund / O / 8
Cost Sharing Program / O / 20
Cost Sharing Percentage / O / 6 / "XX.XXX": relative to associated Cost Sharing Fund and Sponsored Program
IDC Sponsored Program / O / 20
IDC Indirect Overhead Rate / O / 6 / "XX.XXX": relative to associated IDC Sponsored Program
Formatted Grant Master Data Template
This spreadsheet is to be used for staging the Legacy data. There are two worksheets: Grant Header and Grant Detail.
<Grant_Master-Data-Template.xls>
Special Instructions
This data lists workbook is to assist you in building your grant master data upload files.
<Grant_GM-Datalists-for-Grant-Master-Data-Uploads.xls>
In-depth documentation on the process is included in this document.
Grant_Master-Data-Elements.doc>
August 10, 2009 Page 2 of 6