Dependent Records

Use a UUID placeholder to import dependent records without needing to populate the parent key field into the import file for the child records.

This feature is available in the following places:

• Setup / Import Table Data

• APIs > Import / Export > Request a table data import

• APIs > Import / Export > Insert or update a record with auditing

Summary Without This Function

Below is a summary of the steps required without this function, using approvals as the example. The same principle can be used for any table relationship.

1. Import records

2. The fields are populated during the import

3. Export the detail of the record you have just imported, or identify the some other way

4. Update the records you want to import to contain the right value to link the dependent records to their parents

5. Import the updated records

Summary With This Function

Below is a summary of the same example as above, but using this function.

1. Populate each field with a unique UUID value. Populate each field with the same UUID value as its parent

2. Import the records. During the import, the fields are populated and the original UUID value are stored in dependent record mapping table. Import the records. During the import, the fields are populated with their parents value. The details stored in dependent record mapping enables this

Detailed Example of Using a Placeholder Value to Import Dependent Records Without Reference

You want to create a batch of three approval records. Each of them has at least one corresponding maintenance row.

To do this, you need a universally unique identifier (UUID) for each approval record you are creating.

A UUID can be generated in a number of ways. One way is by generating them using a generator tool such as https://www.uuidgenerator.net/ (use Version 4 for best results).

The format of the UUID must be: xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx. The UUID is not case sensitive.

Once you've generated the required UUID values, put the UUID into the place of the table's Primary Key (the Unique ID of the table).

The syntax in the field is 'ref(UUID)'. Example: ref(xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx)

Import File fm_approvals.csv

When the approvals were imported, each UUID was stored in dependent record mapping along with a field value and table name.

Dependent Record Mapping Table

Updated Approvals After Import

Import File fm_maintenance.csv

Any time the UUID ref(95328c94-d4c1-468b-b30f-b64116ab91f5) is used, the system will look it up in dependent record mapping and populate the stored field_value (in this case the approval_id of 111111).

Import Data 1link Approvals

The 1Link Approvals data import option allows the user to import approval records generated by 1Link into the catch-e database if so configured. These records can then be viewed as approvals and maintenance records. If the record is correct, the Approval status is set to "Posted" and the GL table is updated so that supplier invoices are ready for payment and recharges to clients are ready for billing. The 'External Order' field that displays on the Approvals screen is the 1Link reference number.

This process can be performed manually, but is best set up to run nightly as part of an automatic web service.

Validation Rules Summary

• Match the 1Link supplier and contract to Catch-e records

• Create the appropriate non-recharge, recharge and partial recharge lines required

• Adjust the 1Link GST data to suit Catch-e's data structure

• Validate the Odometer readings and untick 'Valid Odometer Flag' if reading is invalid

• If the validation process fails for an approval, the approval is not loaded and is set aside for review

Transaction Outcomes

There are three outcomes that can result for an uploaded 1Link approval:

• Posted - the approval record is in Approvals/Maintenance and has been posted to the General Ledger file

• Active - the approval record is in Approvals/Maintenance, but could not be posted (typically because of missing information or inconsistent record profiling between 1Link and Catch-e)

• Invalid - the approval record is not copied into Catch-e

Import Process

The import process uses the following steps:

1. Select Table - Drop down list of available tables

2. Upload File - Select the file using the browse feature

3. Validate File - The system will check the number of fields in the file and accept or reject

4. Import File - The system will import the file

5. Repeat - Another file may be selected for importing

Import Data 1link Approvals Setup

SFTP Connection

Setup / Reference Data > gb_controls

Scheduler Jobs

Set up three scheduler jobs using the methods listed below.

1Link Setup

17.1 Create fmsuppliers and fmsupplierfranchisevehicles Mapping

Create fmsuppliers and fmsupplierfranchisevehicles mapping from scheme operators list supplied by 1Link.

17.1.1 Create fmsuppliers Record

Create fmsuppliers record for each Account Name from scheme operators list. Map 1Link Account Number to fmsuppliers.externalcode.

17.1.2 Create Supplier Code

Create supplier code from Account Name. Add 1, 2, 3 etc at the end to make them unique if required.

17.1.3 Create fmsupplierfranchisevehicles Record

Create one fmsupplierfranchisevehicles record for each scheme operator. Map 1Link Account Number to fmsupplierfranchisevehicles.3rdpartyid and 1Link Site ID to fmsupplierfranchisevehicles.3rdpartysite_id.

17.2 Setup NVIC or Variant Code to 1Link URN Mapping

Setup NVIC (Glasses) or Variant (Redbook) Code to 1Link URN mapping in fmvariantcode_mappings.

17.3 Setup VMRS Mappings

Setup fminterfacevmrsmappings (fminterfacevmrsmappings.productcode => X6.1linkjobcode + '-' + X6.reasoncode). Create mappings for Tyre Codes and 5 reason codes below as not all reason codes are relevant for Tyres.

X6 is Item Line Details row in the 1Link Invoice file.

• A02 Below Legal Limit

• A04 Client Request

• A07 Damage

• A18 Routine

• A21 Worn

17.4 Setup gbqueries and gbcontrols

17.4.1 Create Summary Report Query

Create the following query in gb_queries to generate Summary Report for 1Link Invoice Import Process.

SELECT t1.import_id, t3.created, t1.external_order_number, t1.approval_id, t4.contract_id, t7.posting_map_code AS type, t6.supplier_code, t6.name, t1.invoice_date, t1.invoice_no, SUM(IF(t2.recharge_flag = 'Y', total_cost, 0)) AS recharged_cost, SUM(t2.total_cost) AS total_cost, t1.status_flag, t1.onelink_approval_id AS 'import Approval Id' FROM fm_onelink_approvals AS t1 INNER JOIN fm_onelink_maintenance AS t2 ON t2.onelink_approval_id = t1.onelink_approval_id INNER JOIN gb_imports AS t3 ON t1.import_id = t3.import_id LEFT JOIN fm_contracts AS t4 ON t4.contract_id = t1.contract_id LEFT JOIN fm_suppliers AS t6 ON t6.supplier_id = t1.supplier_id LEFT JOIN gl_posting_maps AS t7 ON t4.posting_map_id = t7.posting_map_id WHERE t3.interface = 'onelink' AND t1.import_id = (SELECT MAX(import_id) FROM gb_imports) GROUP BY t1.onelink_approval_id ORDER BY t1.status_flag, t1.approval_id; 

17.4.2 Create Validation Report Query

Create the following query in gb_queries to generate Validation Report for 1Link Invoice Import Process.

/* Generate an exception report with following details: * Invalid records with reasons * Exception records with reasons */ SELECT t1.import_id, t1.onelink_approval_id AS 'Onelink Approval Id', t2.onelink_maintenance_id AS 'Onelink Maintenance Id', t1.contract_id AS 'Onelink Contract Id', IFNULL(t4.contract_id, 'Invalid Contract Id') AS 'Catch-E Contract Id', t1.approval_id, t1.external_order_number, t1.supplier_id AS 'Onelink Supplier Id', IFNULL(t6.supplier_id, 'Invalid Supplier Id') AS 'Catch-E Supplier Id', t1.invoice_no AS 'Invoice No', IFNULL(t2.product_code, 'Missing Onelink Job/Reason Code') AS 'Product Code', t1.status_flag AS 'Status', IFNULL(t5.exception_flag, 'yes') AS 'VMRS Mapping Exception', IF(t2.recharge_flag = 'N' && t2.onelink_recharge_flag = 'Y', 'Yes', 'No') AS 'Recharge Exception', IF(t2.posting_class_id = 0, 'No matching Posting Class', t2.posting_class_id) AS 'Posting Class Id', IF(t2.vmrs_code_id = 0, 'No matching VMRS Code', t2.vmrs_code_id) AS 'VMRS Code Id', t2.description AS Description FROM fm_onelink_approvals AS t1, fm_onelink_maintenance AS t2, fm_interfaces AS t3 LEFT JOIN fm_contracts AS t4 ON t4.contract_id = t1.contract_id LEFT JOIN fm_interface_vmrs_mappings AS t5 ON t5.product_code = t2.product_code AND t5.interface_id = t3.interface_id LEFT JOIN fm_suppliers AS t6 ON t6.supplier_id = t1.supplier_id WHERE t2.onelink_approval_id = t1.onelink_approval_id AND t2.line_type != 'part' AND t3.interface_code = '1LINK' AND t1.status_flag IN('invalid', 'exception') AND t1.import_id = (SELECT MAX(import_id) FROM gb_imports) GROUP BY t2.import_id, t2.row_no_x6 ORDER BY t2.import_id, t2.row_no_x6 

17.4.3 Global Control Configuration

See Global Control onelink_sftp_connection details.

17.4.4 Setup Summary Report Control

$onelink_summary_report['query_id'] = '100016'; $onelink_summary_report['recipients'][0]['name'] = 'John Smith'; $onelink_summary_report['recipients'][0]['address'] = 'JohnSmith@abc.com.au'; $onelink_summary_report['recipients'][1]['name'] = 'Denis Pearl'; $onelink_summary_report['recipients'][1]['address'] = 'denisp@abc.com.au'; $onelink_summary_report = serialize($onelink_summary_report); 

17.4.5 Setup Validation Report Control

$onelink_validation_report['query_id'] = '100017'; $onelink_summary_report['recipients'][0]['name'] = 'John Smith'; $onelink_summary_report['recipients'][0]['address'] = 'JohnSmith@abc.com.au'; $onelink_summary_report['recipients'][1]['name'] = 'Denis Pearl'; $onelink_summary_report['recipients'][1]['address'] = 'denisp@abc.com.au'; $onelink_validation_report = serialize($onelink_validation_report); REPLACE INTO gb_controls VALUES ('fm', 'onelink_summary_report', '0000-00-00', '$onelink_summary_report'), ('fm', 'onelink_validation_report', '0000-00-00', '$onelink_validation_report'); 

17.5 Schedule Following Jobs in the Scheduler

17.5.1 Catch-e to 1Link Export

17.5.1.1 exportVehicle

fm>onelink>exportVehicle: This exports all contracts (fve.txt - vehicles) and Clients (fce.txt - Customers) to 1Link.

17.5.1.2 exportMaintHistory

fm>onelink>exportMaintHistory: This exports all maintenance history (vmYMD.txt) to 1Link.

17.5.2 1Link Invoice Import into Catch-e

Schedule this job very early in the morning say around 6:00am. The invoice file to be imported is only available on the FTP Server very late at night maybe around 5:00am or so AEST time.

17.5.2.1 importInvoices

fm>onelink>importInvoices. Invoices can also be imported manually using menu option Import Data / 1Link Approvals. This menu option is excluded by default and needs to be included in Menu by modifying client specific hiermenu.

Import Data Insurance Data

Insurance data may be sent by an Insurance company that contains details of policy premiums, policy numbers, cover dates etc.

Using this menu, you can import this data into a table using a standard import format. Once imported Scheduler job(s) may be set up to use the stored data to update Contract fields.

Client Specific development is required to turn on the import functionality. Please contact your Account Manager for further details.