Xero

Create files to export sales, purchases, or journal records to your accounting system. A separate file is created for each kind of record. The files contain summary data of transactions in the system.

Transactions that have been exported are flagged and then excluded from subsequent export files. Transactions that have been exported cannot be edited. Exported transactions can be un-posted unless you are an 'Admin' user. 'Admin' users are warned of this before the un-posting is executed.

If the multi-company feature is being used, transactions will be exported in separate files for each company. Visit the Company page to read more about this feature.

Scheduler Jobs

You can automate this process with scheduler jobs:

• Set up a job to create the export batch: gl / xero / createExportBatch

• Then set up a job to create the export file: gl / xero / createExportFile

Output Options

Screen Reports - Customised reports can be configured to generate from here. Use this to get a custom export file or a 'Plain English' report of your export detail. The required key for these reports is '#export_id#'.

Record Selections

Purchase transactions are entered into Catch-e in five ways:

• Via scheduled payments. Payment schedules are set up on the payments tab of Fleet / Administration / Contracts. Then each month, within the Fleet / Payments / Periodic menu option, they are printed paid and posted

• Via approvals. Fleet / Administration / Approvals - where they are entered and posted

• Via Claims. Fleet / Administration / Contracts

• Via interfaces. Fleet / Card Services / Transactions

• Via Bookings. Fleet / Administration / Bookings

After posting these transactions, they become available to export and form part of the generated export file (file produced will be in .csv format).

Grouping Options

Standard

Transactions are grouped by Company number and usually contain a detailed row per transaction.

Card Transactions

Card Services transactions may be summarised to any fmcontracts field (e.g. contractid, companyid) using the Global Control cardservicespostinggroup_by.

Summarised

Summarise the data further using the Xero Setup control xeroexportsummarisedata.

When set to 'yes', the data in the export file will be summarised by supplier, becsid, bpay_id, or reference.

In addition this control also splits rows to be 10% GST or zero GST to suit the Xero import process which calculates GST at 10%.

Note: This control goes through the complete process of creating the original non-summarised file. If that completes ok, and all the control checks pass, it replaces that file with the summarised data including a final control check. The control numbers and totals shown on screen and stored in the database use the same logic as before. The summarised counts are not used.

Business rules for output fields:

InvoiceNumber:

CONCAT(fmsuppliers.suppliercode, '-', CASE WHEN becsid != 0 THEN gl_transactions.becsid WHEN gl_transactions.bpayid != 0 THEN gl_transactions.bpayid ELSE gl_transactions.reference END) 

InvoiceDate:

MAX(gl_transactions.date) within the group defined above 

Description:

CASE WHEN gl_transactions.becsid != 0 THEN CONCAT('BECS ID: ', gl_transactions.becsid) WHEN gl_transactions.bpayid != 0 THEN CONCAT('BPAY ID: ', gl_transactions.bpayid) ELSE CONCAT('Reference: ', gl_transactions.reference) END 

Sales

The bulk of invoicing is done within the Fleet / Billing section including recharges, periodic, initial, and combined billing.

In addition billing can be done in Sale Management (vehicle sale invoicing).

Once off billing can also be done in Fleet / Administration / Bookings.

After completing these transactions, they become available to export and form part of the generated export file.

Note: The 'DueDate' created in the gltoxero0sl_xxxxxx.txt is the payment due date determined by the Billing Term used.

Journals

Corresponding journal exports are created to go with the sales exports.

Output Definitions

Tax Types used in the interface are:

• PL - GST on Expenses (used where GST is greater than $0.00)

• PL - GST Free Expenses

• SL - GST on Income (used where GST is greater than $0.00)

• SL - GST Free Income

Open the Xero Output Definitions.xlsx file for more details.

MYOB

Create files to export sales, purchases, or journal records to your accounting system. A separate file is created for each kind of record. The files contain summary data of transactions in the system.

Transactions that have been exported are flagged and then excluded from subsequent export files. Transactions that have been exported cannot be edited. Exported transactions can be un-posted unless you are an 'Admin' user. 'Admin' users are warned of this before the un-posting is executed.

If the multi-company feature is being used, transactions will be exported in separate files for each company. Visit the Company page to read more about this feature.

Scheduler Jobs

Automate this process by creating export batches in the scheduler:

• gl / myob / createExportBatch

Then automate the creation of export files:

• gl / myob / createExportFile

Summarisation of Data

PL Interface

Transactions are grouped by Company number and usually with a detail row per transaction.

Card Services transactions may be summarised to any fmcontracts field (e.g. contractid, companyid) by updating the control. This control became available 30/4/2009 prior to which Card transactions were summarised by Supplier code, invoice number, and glcode.

The sort order is supplier id, invoice number, and gl code. Thus a single fuel supplier may result in a PL record of only one or a few rows. The client code and name is stored in the transaction description to assist with reconciliation within the accounting system. The MYOB field "Comment" contains the Catch-e gl_transactions batch number and item number to also assist with reconciling.

The Order Number used is the Catch-e Approval id OR the External order number if available (i.e. 1link generated authorisations).

The driver's surname can be included in the MYOB Purchase Ledger export file by setting myobexportplsurnameindescriptionflag as 'yes'.

The PL export file 'Date' and 'Shipping Date' fields are populated with the stored invoice_date. If you want to use posted_date to populate 'Date' and 'Shipping Date' instead, change myobexportpltransactiondate to be 'posteddate' instead of 'invoicedate'. This control applies to records that come from both approvals and payments.

Each transaction in the system is exported separately, but it can be grouped by supplier and gl_code. Set myobexportsummarise_data by updating to 'yes'.

Below is an example of how the line content of the export file will appear based on the setting.

SL Interface

Class 'F' (fuel transactions) are summarised by contract id where the Billing menu option Recharge-Fuel or Combined is used. Thus each vehicle will have a single row of summarised data for the batch imported. Recharge-Standard does not summarise the fuel transactions per contract and they are passed across in individual rows.

Sales Invoices can be split by contract using the global control gl.myobexportbreakbyitem_flag. If set to "yes" a new invoice break is performed in the export file to MYOB. This allows payments of sales invoices to be applied per contract if required.

The MYOB "Job" field for SL and PL exports can be populated with a field from the fmcontracts table in Catch-e to allow transactions to be posted to jobs in MYOB. This is controlled by the gbcontrol myobexportjob. Fields which can be used for populating the job field are:

• contract_id

• finance_ref

• supplier_reference

• asset_id

The MYOB "Customer PO" field can be populated with the following data from Catch-e (a typical example is driver surname) to allow for easier searching of invoices within MYOB:

• none (i.e. do not populate)

• surname (from fmdrivers, note the driverid is allocated at the time of billing)

• contractid (from fmcontracts)

• regno (from fmcontracts)

• clientorderno (from fmcontracts)

The Global Control gl.myobslexportcustomerpo contains the field to use. Note that the global control gl.myobexportbreakbyitem_flag must be to 'yes' for SL because in MYOB there can only be one purchase order number per invoice, so the invoice needs to break on contract id etc when exporting.

Sales Invoices can be set to nominate the contract id OR the client code as the Card ID in MYOB. This allows SL invoices to be posted to individual contracts (setup as customers) in MYOB which can facilitate managing cash received per contract. This is controlled by the global control myobslexportcardid (settings can be either "clientcode" or "contractid". "client_code" is the default).

The Global Control gl.myobexportsummarise_data, by default, is set to 'no' to enable the Sales export files to have individual transaction lines. If set to 'yes' the Sales export file will be created with a summary of all transactions in one line.

Jnl Interface

The Grouping rules for creating journal entries in gl_transactions (and thus in the export files) are:

When multicompanyexport_flag = No:

• For Debit entries: Group by Debit code

• For Credit entries: Group by Credit code, Debit code

When multicompanyexport_flag = Yes:

• For Debit: Group by Company ID, Debit code

• For Credit: Group by Company ID, Credit code, Debit code

Procedure to Import into MYOB

This example is for Sales Invoices:

1. Choose MYOB menu option File / Import Data / Sales / Service Sales

2. Import File Format: Comma-separated

3. First Record is: Header Record

4. Duplicate Records: Add Them (otherwise fails due to duplicate order numbers)

5. Identify Customers By: Co. / Last Name (OR Card ID - refer note below)

6. Continue, browse for saved Catch-e file

7. Match All (untick Co. Last name if not matching on name)

8. Import

9. Check the MYOB log file: MYOBPLOG.TXT for errors and warnings

Note: To import a Purchases Journal, choose MYOB menu option File / Import Data / Purchases / Service Purchases. To import a General Journal, choose MYOB menu option File / Import Data / General Journal Entries.

Note: If your Card ID's in MYOB DO NOT match the Catch-e Client code (or contract id if applicable) or Supplier code you need to un-tick the matched field "Card ID" during the import process to prevent the import failing - this is regardless of whether you choose the option to match by Card ID or not. If both company name and card id remain as matched fields then MYOB will require both data fields to match.

The company name can be set to blank for both the SL and PL export files if desired using the global control gl.myobexportcolastname_flag.

Troubleshooting

Below are some common reasons you may get errors in MYOB:

• GL Code not matching

• Customer name or Card ID not matching (also applies to Suppliers). Note the MYOB Card ID uses the client or supplier code in Catch-e

• Tax Account not linked in MYOB. Catch-e supplies GST codes of either "GST" or "N-T". These codes need to be linked in MYOB to the GL Codes used for collecting and paying GST. In MYOB you need to link these Tax Codes to a GL code using the menu option "Lists / Tax Codes". Choose "GST" and select the linked GST account codes. Then do the same for "N-T"

• Error -29: Invalid tax amount when importing journals. Deselect fields not required if you have used the "Match All" feature, e.g. deselect any fields remaining after "Tax Code"

MYOB Details

Click here to access the help page: General Ledger Export To MYOB

MYOB Set Up

Review and update the controls below to get the export file behaviours you want.

Global Controls