Migrating from old Travel invoice page

If you were recording travel invoices in the old form (shown below) before 15.04.2020 (version 2.0.0) and exporting that data to external systems, then you will need to update your settings to ensure the travel invoice data gets exported correctly after the 15.4.2020.

What has changed

The old travel invoice page had a finite number of fixed fields that couldn't be customised so we've moved the travel invoice fields and data into the Supplement (Työlisät) system so customers can freely define which expenses, reimbursements and allowances are provided to users when recording travel invoices.

This means if you are exporting travel invoice data via integration rulesets the integration rules and salary codes will need updating, or if you are exporting travel invoice data using the old integration settings, these will need updating to, see "What needs manual migration" below.

What is being automatically migrated

For customers using the old travel invoice form, the reimbursement, allowance and expense fields (along with the saved values and salary codes) will be copied over to the new system providing the field was in use before. For example, if the "Motorbike" field was never previously used, it will not be copied over but if "Daily allowance" was then that would be copied over.

Important note 1: the supplement settings will be added after supplement number 150 in the settings so they do not interfere with existing supplements:

Important Note 2: On multi-day trips, we cannot know on which travel days any free meals and allowances were recorded so we have allocated them all to the first travel day, this may lead to a warning about incorrect calculations that can be ignored.

Customers who were not previously recording travel invoices but wish to use the new travel invoice form should contact customer service

What needs manual migration

Migrating integration rulesets 

Rulesets queries that use the travel invoice columns in UserEventData table will need migrating to use the rows of CompensationType='WorkIncrement' in the UserSalaryData table instead. An example of this would be:

Before:

SELECT *, SUM(amount) AS OverrideAmount 
FROM UserEventData 
WHERE kilometerallowance1passenger <> 0 
GROUP BY activityid

After (non-optimal solution):

SELECT *, SUM(amount) AS OverrideAmount 
FROM UserSalaryData 
WHERE CompensationType='WorkIncrement' 
AND WorkIncrementNumber = XXXXXX 
GROUP BY activityid

^ ..where XXXXXX is equal to the supplement number representing the travel invoice field to export. This would export just a specific travel invoice field as before BUT is not the easiest way to do it as you would require a single query for every single travel invoice field. Below is an easier solution to set up and maintain...

After (optimal solution):

A better way to do it would be to first configure the supplement code for each travel invoice field (and any other supplement values that need exporting) e.g.

..and then either this information will be exported automatically if there's an existing query to export supplements or you can add one along the lines of:

SELECT *, SUM(amount) AS OverrideAmount, WorkIncrementCode AS OverrideSalaryCode
FROM UserSalaryData
WHERE CompensationType = 'WorkIncrement'
AND WorkIncrementCode != ''
GROUP BY SalaryRenderingDate, WorkIncrementNumber

This will export any supplement values (with a salary code defined in the supplement settings) and group and sum by the supplement number and salary date.

Note 1: If you need to exclude the new travel invoice supplements from an existing supplement query you can add a condition WHERE WorkIncrementType NOT IN('expense', 'allowance', 'tripReimbursement')

Note 2: because you are grouping and summing, this might lose information like comments (WorkIncrementComment) and allowance dates (WorkIncrementDate). For example, daily allowances are now exported with a date (representing the date of the trip the allowance was earned), which might be useful information to export for some customers.

More information on new fields and queries can be found here