Importing Project expenditure data from Finance system

Data directory + Schedule

You need to specify a few items for us to configure the scheduled job:

• You need to specify where you want Awards Management to source the import file from - the location where the extract from your Finance system will be saved.  The file must be named RFS_Project_Expenditure_Import.csv. 

  NOTE:  There can only be one file with the specified name in the location.  Meaning, each time a new file is deposited in the folder, the previous version should be removed.  It is STRONGLY recommended that these files are archived in order to help resolve any issues that may arise if there are any problems with an import.

• How often would you like the job to be scheduled - i.e. how often would you like expenditure information from Finance to be updated (e.g. nightly)?  If you are also importing project details and income data, you must ensure that the Project and Income data job runs and has time to complete prior to the Expenditure import running.

When you are ready to define the data directory and the schedule, get in touch with us so we can configure the job's settings as required.

The import file should be in .csv format, and if you plan to include any special characters in any of the fields the file must be UTF-8 encoded.  The file should include the following columns (in any order, but the column headers must be exactly as stated) (and if, for example, you're only providing expenditure at the Category level, you do not need to include the Sub-category columns):

Import notes:

• It is expected that the file will only include new data / updates (so we can presume to overwrite any expenditure value (where permitted) that may already exist for the entity) (this is to ensure we don't encounter significant performance issues)

• Identifier:

  • Either the Awards Management Project GUID or Awards Management Project ID must be provided

  • For each Project, the import process will first look for the GUID and if none provided, will use the ID

  • If any combination of the ID, the GUID, and the Finance Reference are provided and they do not match the same Project, the Project will not be updated

  • If the Finance Reference column is provided, it must be populated with the correct Finance Reference for the Project

• To update an existing expenditure value, replace the existing value in the file with the new value

• To clear an existing expenditure value, replace the existing value with '0'

• If expenditure information is provided at the budget category level, sub-category details are not required (and vice-versa)

• The import will accept one row per Project + Budget category / sub-category

  • If more than one row per these properties (i.e. if duplicate budget categories / sub-categories for a single Project are identified), all duplicate rows will fail import and an error will be generated

  • Note that duplicates are only recognised within the same identifier. So, for example, if for a single Project you provide a row with the budget category GUID and another row for the same budget category, but use a different identifier (e.g. name or finance system ID), this will not be recognised as a duplicate and only the last row of the 'duplicates' will be imported successfully 

• If a Budget item GUID is provided, no other IDs for the budget item need to be provided

• When identifying the appropriate Budget category / sub-category to apply the expenditure value provided, the import job parses identifiers in the following precedence order (and does not look for consistency across them, where multiple are provided):

  • category / sub-category Finance System ID

  • category / sub-category GUID

  • category / sub-category name

• When identifying the appropriate budget category / sub-category to apply the expenditure value provided, if both category and sub-category identifiers are provided, the import job looks only at the sub-category identifiers 

• If an expenditure value is provided for a Budget category / sub-category that does not exist on the specified Project, the row will not import and an error will be generated

• If Project status = Closed, updates can not be made via this scheduled job (the Project would need to be re-opened and data updated if needed)

The import will run as per the configured schedule.

Any errors generated during the import process can be investigated as described here.