Accounting Exports: JDE

Exporting to Xero, MYOB, JDE, Accpac, and other systems

JDE

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 / jde / createExportBatch

Then automate the creation of export files:

gl / jde / createExportFile

Summarisation of Data

Data is summarised by default as follows: Transactions are grouped by Company number and usually with a detail row per transaction.

Purchases

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

Contracts / Payments when they are posted on the Payments / Periodic screen
Approvals / Approval when they are posted
Contracts / Claim when they are posted or auto-posted from the scheduler
Card Services / Transactions when they are posted. Card Services transactions may be summarised to any contract field (e.g. contractid, companyid)

Once posted, these transactions form part of the next export file that is generated.

If there are commas present in the description field, they are removed during file creation, as they can interfere with the processing of a CSV file, which relies on the commas to define each column.

The last column in the Purchases export file is GLDate. For Approvals / Approval records, this is shown as the Posted Date.

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 Bookings.

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

The last column in the sl export is for a GLDate which is determined by the following rules:

The GLDate will equal the invoice to date where the invoice type is either initial, periodic, or combined
The GLDate will equal a calculated field where the invoice type is either recharge, recharge-fuel, or recharge-reg. The basis of the calculation will be GLDate = the 1st of the month following the invoice date

Examples:

Recharge invoice with invoice date 10/11/2019: GLDate = 01/12/2019
Recharge invoice with invoice date 30/11/2019: GLDate = 01/12/2019
The GLDate will equal the date when an invoice is posted where the invoice type is periodic-fmfee, booking, or sale

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

Journals

Corresponding journal exports can be exported to go with the sales exports.

Zero value records are excluded from the journal file export.

Output Definitions

Tax Types used in the interface are:

GST on Expenses (used where GST is greater than $0.00)
GST Free Expenses
GST on Income (used where GST is greater than $0.00)
GST Free Income

AccPac

This option is used to export a sales, purchases, or journal file to the AccPac external accounts system.

The standard AccPac export files do not require a general journal export because the Sales Ledger export is split by cost code. Discuss with Catch-e if you require alterations to this method.

Note: 20/06/2011 the export files were modified so that PL exports are summarised by supplier invoice and gl code, i.e. contract level was removed. The SL exports were left at exporting at the contract level and may need to be modified if requested by future client users. As at 20/06/2011 the SL and GL AccPac exports are not used by any clients of Catch-e.

Workflow

Click on the 'New' tab
Select Purchases or Sales from the select box
Review Control Items and Control Total
Click 'Create'
Click 'Export'
Save the txt file generated and import into the external accounts system
Select the Control Type again, Export and Save if both Debits and Credits exist
Click 'Edit'
Update the status from 'pending' to 'exported ok'
Add any notes if required

Note: Credit transactions (i.e. the opposite to normal) are generated as separate files to allow exporting to AccPac separately. There is a single Export ID containing both the debits and credits. The system will display 'Control Debits' and 'Control Credits' and an overall control total. You can select the Debit or Credit file individually using the "Select Control Type" drop down list per batch.

Note: The 'create' action flags the relevant transactions as exported and will not be duplicated in any subsequent 'create' actions. Copies of exported files can be re-created anytime by using the search icon and selecting the relevant file.

Interface File Specification

Purchase Ledger

Header Row:

Field	Description
Record Type	H to denote header row
Vendor Code	Refer Global Control gl.accpacplexportsuppliercode nominating field to use
Invoice No	22 characters, Cap Alphanumeric
Invoice Date	dd/mm/yyyy
Invoice Total Amount including GST
PO number	22 characters, Cap Alphanumeric, if any
Order number	22 characters, Cap Alphanumeric, if any
Invoice transaction description	60 characters, if any

Detail Row:

Field	Description
Record Type	D to denote detail row
Expense class code	gl code from contract types
Ex Tax amount
Tax amount
Purchase Item Tax Class
Expense transaction description	Including the Supplier Ref from the Contracts / Payments screen (up to 60 characters)

Purchase Tax Classes are as follows:

Tax Class	Description
1	Taxable Purchasers
2	Input Taxed Purchasers
3	GST Free Purchasers
4	Capital Taxable Purchasers
5	Capital Non Taxable Purchasers

Note: Detail rows containing GST are forced to have an exact GST amount based on the current GST rate (e.g. 10%). If a supplier invoice has a non 10% GST amount the detail row is split into 2 rows.

Sales Ledger Interface

Header Row:

Field	Description
Contract ID	6 numerics
Invoice No	22 characters, Cap Alphanumeric
Invoice Date	dd/mm/yyyy
Invoice Total Amount

Detail Row:

Field	Description
Item Category	See list below
Ex Tax amount
Tax amount
Sales Item Tax Class	See list below
Quantity
Unit billing rate	ex GST

Sales Tax Classes are as follows:

Tax Class	Description
1	Taxable Items
2	GST Free Items
3	Input Taxed Items
4	Capital Taxable Items
5	Capital Non Taxable Items

AccPac Details

Click the following link to access the AccPac wiki page: General Ledger Export To AccPac

BECS Disbursements ABA

Generate an aba file here to pay claim reimbursements into each driver's bank account. Each un-paid claim that has been posted and marked as a reimbursement will be added to this file.

The ABA file page details how the file is compiled.

ABA files can also be generated for Payments / Periodic and Receivables / Receipts.

Visit the Australian Payments Clearing Association website: http://www.apca.com.au/ for more information.

The following programmer blog is also helpful about the aba file layout: http://ddkonline.blogspot.com.au/2009/01/aba-bank-payment-file-format-australian.html

Record Selection

Claims are selected for reimbursement and added to the next batch here if:

Contracts / Contract "Company" is the same as that selected or shown
Contracts / Claims "BSB" and "Account Number" are recorded correctly
Contracts / Claims "Reimbursement?" is checked
Contracts / Claims "Stop BECS" is un-checked
Contracts / Claims "Status" is 'Posted'
The claim is not already linked to in an existing batch

Workflow

Visit the follow process pages:

Create and post a claim
Claims may also be entered by drivers in the Driver Portal
Post a claim created by a driver or employee or use in combination with the Auto Post on Claims function
Use Stop BECS to prevent posted reimbursement claims from being paid
Generate an ABA banking file to reimburse claims

Filters

Filter	Type	Description
BECS Id	Lookup	Lookup or enter the batch number of an existing batch
New	Button	Click this when you want to create a new batch

Field Descriptions

BECS ID (Display) - This is blank if you are creating a new batch. If you are reviewing an existing batch, the "BECS ID" is displayed
ABA Type (List Box) - Two options are available: 'Client' and 'Company'. Where there are no client bank accounts, the list box will not be required, and 'Company' will be defaulted in. It is not editable
Bank Account (List Box) - This field will be enabled when "Bank Account Type" equals 'Company'. Where "Bank Account Type" equals 'Client', field is disabled, and no editing options made available
Client Code (Lookup) - The lookup will only list the Clients that have the flag 'Client Bank Account' checked ON (i.e. = 'yes')
Control Invoices (Num) - Calculates or displays the number of invoices that will be paid. i.e. payments and / or approvals
Control Items (Num) - Calculates or displays the number of rows that are in the selection for posted approvals and payments
Control Total (Num) - Calculates or displays the total amount of the batch

Action Buttons

New - Click this when you want to create a new batch
Print - The content of the BECS export file can now be printed before being created. Where there is no data to report, the button will be inactive
Export - Will be active when data is available to export. Select to create export file. Where multiple bank accounts in use, a bank account must be selected to activate the button
Cancel - Select to return to BECS export page to allow process to begin again with no action being executed

Output Options

reimbursementspreview (Preview) - Click on the Print button to generate a report that lists the reimbursements that will be made if you create this batch. The records are shown by item, so the number of records you see will match the "Control Items" number shown for the batch on the screen. This report can be output as a Office(xls) file or a Text(CSV) file
reimbursementsbecsid_Report - Select a stored "BECS Id" and click Print. This report lists the reimbursements contained in this batch. The records are shown by item, so the number of records you see will match the "Control Items" number shown for the batch on the screen. Payments are made by Approval ID, so these rows will be grouped up for payment. This report can be output as a Office(xls) file or a Text(CSV) file
reimbursementsaba - Click Export for the displayed "BECS Id". Upload this aba file into your banking software to make the reimbursement payments to drivers

Validations and Alerts

Alert	Context	Comments
Banking details require set up!	On navigation to the Accounts / Export / BECS / Reimbursements (ABA) screen	There are no bank account records in your system. Contact your Account Manager to help you set up your banking details and configure this feature
Nothing to reimburse!	New	There are no outstanding claims to reimburse. Click on OK to be returned to the screen
Warning! Reimbursements cannot be exported for the following drivers:	Create	The listed drivers have missing bank details. This error can occur if a user has removed the Driver's banking details after a claim has been raised and posted, but has not yet been paid. To correct these errors, go to the Drivers / Finances tab and add or correct the details in the "BSB Number" and "Bank Account Number" fields for the listed drivers. Up to eight (8) driver records can be displayed on the alert message pop-up. If more than eight drivers have missing bank details, they will show progressively as displayed records are corrected and the invalid list reduces. Note that bank account numbers can only be 9 characters long. If there are driver records that you cannot update at this time, you can still create a file for the valid records by clicking Create. Claims that have missing bank details will be skipped. The invalid records will be displayed again the next time you create a reimbursements file
Warning! There are reimbursements due for export but none can be exported due to insufficient data! Drivers: {driverid}	Create	This alert occurs where the listed driver or drivers have missing bank details (like the alert above) and there are no valid records that can be selected to create a reimbursement file. i.e. all the claims that should be reimbursed are linked to drivers with missing bank details
Print failed, check client banking details! Client: {given_name} {surname} Account: {bank_account}	Export	Go to the Drivers / Finances tab and add or correct the details in the "BSB Number" and "Bank Account Number" fields for the listed drivers
File cannot be generated. Contact Support	Export	Since this batch was originally created, the stored aba file has been corrupted and can not be re-created. This scenario could happen if multiple bank accounts have been set up since the batch was created, which means due to the data constraints, the file can no longer be reconstructed automatically

BPAY

System can generate BPay file based on CommBizz file specification version 6 (April 2011). The file can be downloaded here.

Warning: This function has not been used for some years and will not work with current setups. Contact your Account Manager to discuss using this function.

Records will only be selected for file creation if Reg or CTP Renewals batches:

are Posted
have BPAY checkbox ON
have not been exported previously

Create Export Files

Click on the 'New' tab
Click 'Create'
Click 'Export'
Save the csv file for import into your Banking software
Click 'Edit'
Update the status from 'pending' to 'exported ok'
Add any notes if required

The 'create' action flags the relevant transactions as exported and will not be duplicated in any subsequent 'create' actions.

Copies of exported files can be re-created anytime by using the search icon and selecting the relevant file.

BPAY File Header Record

Field #	Type	Field Name	Length	Format	Mandatory	Description
1	N	Record Type	2		Yes	"01"
2	N	File Creation Date	8	YYYYMMDD	Yes	The date the file was created
3	N	File Creation Time	6	HHMMSS	Yes	The time the file was created
4	N	File Number	3		Yes	Sequential number of file. Could commence at '001' each day
5	N	Payment Account	20		Yes	The account number of the funding account
6	N	Payment Date	8	YYYYMMDD	Yes	The date on which the payments are to be made. Can be up to 15 months into the future from the lodgement date. If left blank, will default to the date the file is submitted
7	N	Number of Payment Records	6		Yes	The number of payments in the file
8	N	Total Amount of Payments	12		Yes	The total amount of payments in the file, in cents

BPAY File Payment Details Records

Field #	Field Name	Length	Type	Mandatory	Description
1	Record Type	2	N	Yes	"50"
2	Payment Instruction Type	2	N		empty
3	BPAY Transaction Type	1	N		empty
4	Payer Institution Code	3	A		empty
5	Payment Account Detail	20	AN		empty
6	Country of Payment	3	A		empty
7	State of Payment	3	A		empty
8	Currency Code of Payment	3	A		empty
9	Biller Code	10	N	Yes	The BPay Biller Code of the beneficiary of the payment
10	Service Code	7	N		empty
11	Customer Reference Number	20	N	Yes	The account number, reference number, etc of the account that is being paid
12	Number Method	3	N		empty
13	Entry Method	3	N		empty
14	Amount	12	N	Yes	The amount of the payment, in cents (+gst_cost)
15	Transaction Reference	21	AN		empty
16	Number Reference	21	AN		empty
17	BPAY Settlement Date	8	N		empty
18	Date Payment Accepted	8	N		empty
19	Time Payment Accepted	6	N		empty
20	Payer Name	40	A		empty
21	Additional Reference	20	A		empty
22	Code Correction Reason	3	N		empty
23	Discount Method	3	A		empty
24	Discount Reference	20	A		empty
25	Discretionary Data	50	A		empty

Click here for pdf version.

Useful Queries

select c.contract_id, pm.posting_map_group, c.company_id, m.maintenance_id, m.batch_no, m.item_no, bi.bpay_flag, bi.bpay_biller_code, t.bpay_id from fm_maintenance as m inner join fm_contracts as c on c.contract_id = m.contract_id inner join gl_posting_maps as pm on pm.posting_map_id = c.posting_map_id inner join gl_batch_items as bi on bi.record_id = m.maintenance_id inner join gl_transactions as t on t.batch_no = m.batch_no and t.item_no = m.item_no group by m.maintenance_id order by maintenance_id desc limit 4;