Financial Extraction for an ITC

The checklist below may be used for ITCs whose data is included with their fiscal agent’s data and must extract their 025 fund's cash, expenditure, receipt and ITC's specific OPU data for the ITC's financial reporting. (May also be used for ITCs who are their own COG who have other funds but need to extract just 025 fund). The checklist will cover:

How to import the provided JSON files into USAS-R in order to extract the ITC’s 025 Cash, Expenditure, Revenue and OPU data from their respective grids into spreadsheets
Using provided EMISFFE spreadsheets to copy the required ODE columns and insert them into the newly created spreadsheets.
Adding/editing any columns in order to prepare the spreadsheets for EMISFFE import
Importing into EMISFFE and extract the QC data into a sequential file.

5. Generate and format ITC Expenditure Extract

In USAS-R, generate the ITC Expenditure Extract spreadsheet for the 025 funds

Open the ‘EMISFFE Expenditure First 6 Columns’ spreadsheet and copy the first 6 columns (A-F) and insert them into the first 6 columns (A-F) of the ITC Expenditure Extract spreadsheet (copying down the data to match the number of expenditure rows)

Add the IRN in Column B (ensure the column remains in text format in order to include leading zeroes)

Add the General Fund Debt-Bond Retire fund to Column S (column after encumbrances). Enter a blank space into the cells in the column by pressing spacebar (and copy down all records)

Before saving the file in CSV format, remove Line 1 (header row). It cannot be included when importing into FFE. (example below)

6. Generate and format ITC Receipt Extract

In USAS-R, generate the ITC Receipt Extract spreadsheet for the 025 funds

Open the EMISFFE Revenue First 6 Columns spreadsheet and copy the first 6 columns (A-F) and insert them into the first 6 columns (A-F) on the ITC Receipt Extract spreadsheet (copying down the data to match the number of revenue rows)

Add the IRN in Column B (ensure the column remains in text format in order to include leading zeroes)

Add the Fiscal Year Receivables column to Column N. Enter a blank space into the cells in the column by pressing spacebar (and copy down all records).

Add the General Fund Debt-Bond Retire column to Column O. Enter a blank space into the cells in the column by pressing spacebar (and copy down all records).

Before saving the file in CSV, remove Line 1 (header row). It cannot be included when importing into FFE. (example below)

7. Generate and format ITC OPU Extract

In USAS-R, generate the OPU Extract for all OPUs associated with the 025 funds

Open the EMISFFE OPU First 6 Columns spreadsheet and copy the first 6 columns (A-F) and insert them into the first 6 columns (A-F) on the ITC OPU Extract spreadsheet (copying down the data to match the number of OPU rows)

Add the IRN in Column B (ensure the column remains in text format in order to include leading zeroes)

Edit the Line Number (Column E). The line number cannot be duplicated so please increment this number

If the Central Office (Column J) contains a ‘true’, the value of ‘true’ must be changed to ‘C’. If the entity doesn’t have a central office designated, you must enter blank space into the cells in the column by pressing spacebar (and copy down all records).

Before saving the file in CSV format, remove Line 1 (header row). It cannot be included when importing into FFE. (example below)

8. Import CSV files into EMISFFE

Log into ITCs EMISFFE account

Cash Record

Click on Cash (QC) and ensure existing Cash record is empty. If it isn't, use the 'Delete All Cash' to remove the existing records

Click on Home, Import Data and import the ITC Cash Extract CSV file

Ensure no errors were generated and review the Cash Record (example below)

Expenditure Record
Click on Expenditure (QC) and ensure existing Expenditure record is empty. If it isn't, use the 'Delete All Expenditure' to remove the existing records
Click on Home, Import Data and import the ITC Expenditure Extract CSV file
Ensure no errors were generated and review the Expenditure Record (example below)

Receipt Record
Click on Receipt (QC) and ensure existing Receipt record is empty. If it isn't, use the 'Delete All Receipt' to remove the existing records
Click on Home, Import Data and import the ITC Receipt Extract CSV file
Ensure no errors were generated and review the Receipt Record (example below)

OPU Record
Click on Operational Unit (QC) and ensure existing OPU record is empty. If it isn't, use the 'Delete All Operational Unit' to remove the existing records
Click on Home, Import Data and import the ITC OPU Extract CSV file
Ensure no errors were generated and review the OPU Record (example below)

9. The QC data in EMISFFE includes the following. If the ITC has Cash Reconciliation, Civil Proceedings and Federal Assistance Summary and Detail information to enter for the reporting period, they may enter it in manually in EMISFFE

10. Once the QC data have been imported into EMISFFE, use the ‘Export data’ option to extract the reporting year's “H - July/Financial” data set and select only the ‘QC’ record to export and click on 'Export File'. It will create an IRN#_FFE-SEQ file that will need to be loaded into the Financial Data Source in EMIS-R. We strongly recommend running a test collection of the flat file data in the financial folder to ensure everything formatted correctly

EMISFFE Import Troubleshooting

CSV Invalid format error, this error will occur when the CSV layout does not match the Chapter 5 layout minus the filler, fiscal year and reporting period fields.
Negative numbers, must be formatted with a minus ( - ) sign. Negative values that are represented using parenthesis rather than a minus sign will cause an error during the import.
Please ensure leading zeroes are included in the account code dimensions before importing into EMISFFE (i.e. 025 fund code contains the leading ‘0’)

USAS Documentation

Financial Extraction for an ITC

EMISFFE Import Troubleshooting

Related content