Data Import & Export: Importdata Service Profiles

Importdata Service Profiles

This feature will only be useful where either Glass's or Redbook data is being imported into Catch-e. It is a separate Import and will need to be requested as an additional service. If you are interested, please read the specification document and contact Catch-e.

The import of SMR Data (Service, Maintenance, Repairs) will create Service Profiles for each variant included in the file, or will update existing data. When in use, rows for the required parts and labour will be populated into an Approvals / Approval record when the parent VMRS Code is entered.

Two methods of importing the SMR Data is available;

1. A scheduled job can be run to retrieve the file from an ftp site, subject to exiting features within Catch-e.
2. Via the Fleet / Import Data / Service Profiles menu

Validation Process

Before any data is imported, the file and some of the content is subjected to a validation process to ensure that correct data is being imported, minimising the possibility of data corruption.

If the validation fails, the process is aborted and no data is imported into Catch-e. The reasons why will need to be investigated and a possible re-issue will need to be requested from the data supplier.

The Validation Errors are as follows;

Incorrect File Name

The file name for Glass's related data is identified as ‘Glass’s - SMR Data yyyy-mm-dd.zip’. If imported under another name or format, it will fail.

The file name for Redbook related data is identified as ‘Catch-e - SMR Data.zip' If imported under another name or format, it will fail.

• Solution : Rename your supplied file if possible and re-import. If your file is imported via a scheduled job, you may need to refer the problem back to the data supplier.

Missing file(s) in import zip file

Each zip file must contain three .txt formatted files.

Glass's has the following;

1. UIDURNtoNVIC.txt
2. URN Service Profile Mapping.txt
3. Service Schedule Details.txt

Redbook has the following;

1. UIDURNtoRedbook.txt
2. URN Service Profile Mapping.txt
3. Service Schedule Details.txt

Without all three files in the .zip file, the mapping process into the interface is not possible and the process will be aborted.

• Solution : You will need to contact the data supplier to send through an updated .zip file for any future import to be successful.

File does not contain correct number of fields

Both Glass's and Redbook have the same titled file in their respective .zip files - Service Schedule Details.txt.

Each file has 11 columns in the header and they each have the same sequence.

If this file does not have 11 header columns and the second column does not have the header 'Service Profile Id', the validation will fail and the process is aborted.

• Solution : You will need to contact the data supplier to send through an updated .zip file for any future import to be successful.

Duplicate File

An attempt to upload a previously imported file will fail. When the following criteria are exactly the same, the file being imported will be deemed a 'duplicate';

1. File Name
2. File Size, and
3. Timestamp of when file was created

• Solution : Alter any of the above criteria or rely on next scheduled import to update the data.

Import Process

Once all validation points have passed, the data is stored in the table and the status_flag is set to 'pending'. This indicates that validation has passed, but NO data has been imported into any other Catch-e tables. The import process is not yet complete.

Once the data stored in this table has completed the 'Import' process the status_flag will be updated to either 'active' or 'invalid'.

• 'active' implies that the data has been imported into the Catch-e tables and has been mapped correctly.
• 'invalid' implies that certain criteria is missing which would allow the Service Interval information to be created automatically. An exception report will need to be set up to run from the scheduler which will extract this data for you to resolve. Reasons data will be marked as 'invalid' is as follows;

Invalid or missing NVIC/Redbook Code

Glasses NVIC Codes must be 6 characters in length, anything greater will fail.

Redbook Codes must be 15 characters in length, anything greater will fail.

• Solution : You will need to contact the data supplier to send through an updated .zip file for any future import to be successful for the code causing the error.

This error may also appear if the Variant Code in the SMR data file does not match to a variant code in the table which stores the respective data source's vehicle data.

• Solution : Re-import the Glass's or Redbook vehicle data to update the latest vehicle information into Catch-e. Re-import the SMR Data file. If the error persists, you may need to request the latest vehicle data file from your data supplier.

Invalid Service KM. No matching External Code found in fmvmrscodes

This error will appear when the Service Interval included in the SMR Data does not exist as a VMRS Code and it therefore cannot establish a link back to it's respective variant code.

• Solution : Create the VMRS Code entry that relates to the unidentified service Interval. The update will be made on the next import cycle, or if the update is required earlier, please contact Catch-e. To allow the re-import of the SMR Data file, update to 'deleted' of the related . This will remove the previously imported file (not data) and by-pass the 'Duplicate File' error

Invalid Part Code. No matching Product Code found in fminterfacevmrs_mappings.

As part of the set up, a 'SMR' interface is required. On import, the 'Part No' supplied in the SMR data is mapped to the 'Product Code' in the SMR Interface. This error will appear where a new 'Product Code' has been identified and needs to be mapped to a 'VMRS' entry. An entry is required in the 'VMRS' field to then go on and be added to , effectively creatine the collective 'parts' seen on screen under the respective Service Interval.

• Solution : A vmrs code will need to be updated into each 'Product Code' that has a missing 'VMRS' entry. Once this is complete, a schedule job titled 'Update service profile group items' will need to be run to update the Interface Mappings and follow up with populating the respective .

Nothing to Report

Import will only proceed where there is at least one record where = 'pending'.

Reporting Process

Scheduled jobs that have set to run at a specific time will supply a report where errors have been identified in the import process.

These errors will need to be resolved based on the information above.

Importdata Service Profiles Set Up

The Service Profiles Import feature automates the upload of service interval data which is compatible for use where clients use either Glass's or Redbook vehicle data.

When a new approval is raised for a service for a particular variant, it can create individual rows for labour and each part used in the service and each part's respective quantity and unit price.

Follow the steps below to setup the required settings to enable these uploads.

SMR Interface

Go to the Card Services / Interface tab and create a new interface record. The "Interface" code for this must be 'SMR'.

The 'Mappings' tab of the Interface will need to be set up with Product Codes and their related VMRS Codes. If the interface is created, you can proceed with the import to update the 'Product Code' however each 'VMRS' entry will need to be allocated to every line item. This relationship must be established here first before the Service Interval data can be seen in the approval.

Once the 'Mappings' updates are complete, a second scheduled report MUST run to push the updated data into the table to complete the process.

VMRS Codes

Go to the Maintenance / VMRS Codes screen and check that record with a "VMRS Code" of 'LAB' exists. If it is missing, create it. It is required to enable the auto creation of the Labour row in approvals using SMR data.

Scheduler Reports

Two reports are required to manage the SMR Data creation process into Catch-e. Please contact Catch-e to create these jobs.

• Check Imported Service Profiles

1. Module = "gb"
2. Class = "queries"
3. Method = "executeQuery"
4. Attachment Format = "Text". Due to the potential volume of data in this report, it cannot be any other format extension.
5. Column Headings = Check ON

Please consider which recipients will be nominated to receive this exception report and rectify identified errors.

• Update service profile group items

1. Module = "gb"
2. Class = "queries"
3. Method = "executeScript"

Glass's SMR Data

For clients using Glass's Data, it is recommended that they arrange to have the SMR data files 'dropped' into the ftp site along with their Vehicle data files. This will allow the process to be completely automated and a scheduled job can be created to upload the data files when they become available.

Please contact Catch-e for this scheduled report to be created as the following "Job Type";

• Module = "fm"
• Class = "service_profile"
• Method = "ImportServiceProfiles"

Please consider which recipients will be nominated to receive the related exception report to resolve any validation issues, as this is required to complete the scheduled job set up.

Redbook SMR Data

Currently, Redbook does not provide an automated upload facility, therefore the SMR data files will need to be uploaded into Catch-e via the available menu option,

Fleet / Import Data / Service Profiles

The process requires the user to work through the screen prompts for the file to be imported. Any validation issues will be prompted on screen for the user to action accordingly. No exception report will be issued using this import method.

Importdata contributions Data

Warning: This feature is not currently active. Contact your Account Manager if you think this feature would be of use to you.

The contribution option is used to import payroll contributions into catch-e from a payroll export (or similar). These are periodically transferred to card systems to update the account balance allowing the employee to spend the contributed funds using their meal card.

Contribution imports follow the same model as Redbook and Sequoya imports and are used in the same fashion. A CSV is exported from the payroll system and placed in a location on the local computer. Using the import menu option, you can browse to the CSV which is uploaded into temporary file storage on the catch-e server.

The second stage of the import process is validation. The CSV file is read by catch-e and imported into a temporary table named tmpcontributions.

Note: import files should have no header record.

Here is a sample contributions import file

Import File fields

Import Contributions table structure and field purpose are listed below:

Employee/Transactions table

spemployeetransactions table structure and field purpose are listed below:

Import Validations

If validation is successful, the data is transferred from the temporary table into spemployeetransactions. The content placed into individual spemployeetransaction fields is listed below:

If the validation process fails, an error is displayed and no data is imported. Validation can fail if the amount is invalid, the date is invalid, one or more of the listed cardaccountnumber(s) are not assigned to a catch-e employee or if the CSV file contains the wrong number of fields. The order of fields in the CSV must match the table structure or imported data will be corrupted and possibly fail import.

All errors occur during the validation phase, and the error messages are just the standard, same as other imports:

• Wrong number of fields: "File does not contain the correct number of fields!"
• Invalid amount: "Error validating data!"
• Invalid date: "Error validating data!"
• cardaccountnumber(s) not assigned to drivers: "Error validating data!"

Importdata redbook Data

The import of RedBook data generally creates new records and updates existing records where required.

Go to the the Variants / Detail page to review the data that is imported for a variant.

Note: Import Redbook data uses an FTP connection.
This function is marked for upgrade to SFTP, but at this stage FTP must be used.

Scheduler

RedBook data is usually processed by using scheduled jobs to import data from their FTP site.

Filename

The importVehicleData files are stored on the FTP site in a zip file with a static name that is nominated in the scheduler job.

This zip file contains both the data files updated here, and also the photo files that are updated in the Redbook Photos screen.

Mandatory Files

• Family.txt — Lists families (models) for each manufacturer.
• Make.txt — Lists all the available manufacturers.
• Vehicle.txt — The main vehicle file. All Catch-e and RedBook mutual clients share an identical column structure however, all clients do not receive data in all columns. The latest file layout contains 87 columns in total. Download Catch-eStandardLayout.xlsx to see the field details of this file.
• Year.txt — Shows the available year groups for each family.

Optional Files

• AMOptions.txt — Shows the after market options available to each vehicle, linked by VehicleKey
• Options.txt — Shows the optional equipment available to each vehicle, linked by VehicleKey
• OptionPacks.txt — Shows the available equipment within an option pack (if an option is flagged as an option pack). Linked by OptionKey
• PriceAhead.txt — Shows the Red Book predicted residuals for new vehicles only, by months and km. Linked via VehicleKey
• Photos.txt — Contains the Red Book Photo library and description. This file is processed using the Redbook Photos menu item.
• Standards.txt — Shows the standard equipment that is attached to each vehicle, linked by VehicleKey
• Vehicleincprice.txt — An alternative (additional) file to the main vehicle file. It contains some additional fields.
• VehiclePhotos.txt — Used to link vehicles to photos. This file is processed using the Redbook Photos menu item. VEPaint.txt RedBook can supply an additional file containing manufacturer paint (colour) details. This data can then be used to select Quotes / Vehicle Colour Preferences. Only the columns named VehicleKey, PaintDescription, PrimaryGenericColour & PrimaryPaintType are used in the system. Download Catch-eStandardLayout.xlsx to see the field details of this file.

Quotes / Vehicle "List Price" data treatment

RedBook provide a 'Recommended Retail Price' alongside values for amountnet and amountgst'.
'Recommended Retail Price' includes LCT, when it is applicable at this point to show the RRP as being inclusive of taxes like LCT and GST.

When imported, the following steps are performed

1. The RRP is stored as (this is not shown in system screens).
2. List Price Exc GST:
3. List Price GST:
4. List Price Total: + .
   Thus explaining why, sometimes, the database fields amountnet, amountgst and amount_gross do not match.

Protected Fields

Certain fields are not over written by the interface if the interface file is attempting to import a zero or blank data item.
These fields are:

• weight
• payload
• towing_capacity
• kerb_weight
• fuel_metro
• fuel_country
• fuel_combined
• warranty_years
• warranty_kms
• service_kms
• service_mths

Protected Client Fields

The system can be coded to nominate certain fields per client database to NOT over ride with imported data unless the target data is zero or blank. This can be used when you may want to keep specific data and ignore the values imported by RedBook. Contact Catch-e if you wish to nominate any fields for use with this business rule.

Validation

The import process will validate the 'RedBook' code for each row in each file. RedBook codes must be 15 characters. If any of the RedBook code fields are not 15 characters long the file will fail. No vehicles will be imported in this instance. Catch-e support will be notified.

Audit File

The import records created and changed in the gbauditimport file. Reports and queries can be run over this file using the timestamp of each import to identify new records created and existing records updated. Contact Catch-e for the sample queries named:

• Variants created and updated
• Variant residuals updated
• Other tables updated

The RedBook supplied table for Dealer Fit options can be turned off within the system if not desired.