Fuel Card Integrations: BP
BP, Shell, Caltex, Mobil, and other fuel card interfaces
BP
FORMAT16
Note: This file should be imported using .csv as the format
BPNZ
Field mapping from the BPNZ CSV file to Catch-e table as follows. Note files do not contain a header row.
Shell
The Business rules for importing the SHELL file are:
Note:
Only rows with '3C' in column A are considered to be transactions, all other rows are skipped.
A Shell Card number is sixteen digits long, whereas the .ec file truncates the number to 8 digits. When creating new card entries delete the last digit from the card number and then count back eight digits the result will be the card number for entry in the fm_cards table.
Example:
card number 7071340051163224 would become 05116322
FLEETCOR
The Fleetcor interface is based on a .csv data file provided by Fleetcor.
Business Rules
Business rules used by the interface are:
- The file must be in .csv. format.
File Validation
The Validation rules on import are:
The file must consist of 22 columns.
The brackets surrounding the Card Number in the import file are ignored on import.
Account Name
- Card No — Translated as SUBSTRING({Card No.}, 2, 17) on import, ignoring the brackets.
Transaction Number
- Transaction Date — CONCATENATE {Transaction Date} & {Transaction Time} INTO odometertimestampstart
- Transaction Time — CONCATENATE {Transaction Date} & {Transaction Time} INTO odometertimestampstart
Docket Voucher
- Product Description — Use this description to add products to your Card Services / Mappings records for FLEETCOR. See also the Product Codes section on this page for suggested entries.
Quantity
- Total +
- productnet {Total} - productgst
- product_gst {Total} - ROUND({Total} / (1+, 2)
File View
An image of a Fleetcor Import file
Product Codes
When adding records to Card Services / Mappings, use the "Product Description" supplied in your files as the "Product Code".
I.e. "Product Code" and "Description" will be the same in the mapping.
Below is a list of "Product Codes" used in FLEETCOR interfaces.
AdBlue
Auto Renewal Fee
Car Wash
Diesel
Diesel Exhaust Fluid
Ethanol Blend
Lubricant
Merchants Surcharge
Miscellaneous
Oil
Periodic Fee/Stamp Duty
Premium Diesel
Premium unleaded
Premium Unleaded 98
Repairs/maintenance
Replacement
Replacement Card
Routine Service
Super
Unleaded
FLEETCORD
The Fleetcord interface is based on a .dat data file provided by Fleetcard.
Business Rules
Business rules used by the interface are:
- The file must be in .dat format.
- The file format is DAT delimited by pipes "|".
File Validation
The Validation rules on import are:
The file must consist of 39 columns.
The brackets surrounding the Card Number in the import file are ignored on import.
File columns and mappings to Catch-e fields
File View
An image of a Fleetcord Import file
Product Codes
Below is a list of known "Product Codes" used in the FLEETCORD interface.
CLINK
The Linkt file is a record of all travel by drivers using a e-tag device supplied by Linkt.
This includes travel in multiple states under the one account, these being VIC, NSW & QLD.
The import file must be in .csv format.
E-tags are linked to the associated contract using the same process as fuel cards.
Visit the supplier's home page here: https://www.linkt.com.au
The interface code is CLINK owing to the supplier's previous name, City Link.
Total trip charges
Data is combined from the Total trip charges section and the State Trip details section of the file to enable each trip to be imported to the linked contract.
Trips without an e-Tag device
Data from the Trips without an e-Tag device section is used to create an itemised record for all the trips that a vehicle has made with no e-Tag.
These transactions will import successfully if you create a card record to match each vehicle. Without a matching card record, these records will fail the validation checks in Card Services / Transactions as 'Failed Card' .
To create a matching card record:
- The vehicle registration is 'ABC123'.
- Create a card with a "Card Number" of 'No Tag ABC123'.
Fees charges and adjustments
The only fees, charges and adjustments that can be imported from this section are those that can be linked to a contract via a registration number.
Where the registration number is provided in the 'Source Reference' column of the import file and the 'Details' description is supported, the import will accept it.
The list of accepted 'Details' descriptions is shown below. These descriptions will also be imported if registration numbers or other details follow e.g., 'No Tag in Vehicle Fee - ABC123 VIC'
- Late Toll Notice Admin Fee
- Video Matching Fee
- No Tag in Vehicle Fee
- CityLink Tulla Pass
- Admin Fee
No other fees, charges and adjustments will be imported via card interface upload as they are applied at Account/Client level. The interface allocates at contract level. Should any other charges appear on the client’s invoice, they will need to be added manually via an approval.
File Columns and Mappings
The Linkt import file consists of various sections that are detailed below.
Header
This section contains header records in columns A and B that summarise the invoice details.
- 1 — Displays Linkt Client Account Number Account number
- 2 — Displays the name in which the Account is under Account name
- 3 — Displays invoice number charges to be paid under Invoice number
- 4 — Displays the period for which the charges apply Invoice period
- 5 — Displays invoice issue date Date issued
- 6 — Displays amount owing from previous periods Opening balance
- 7 — Displays last received payment made Payments received
- 8 — Displays amount owing for trips taken on Linkt tolled roads. This includes trips made without an e-tag. Total charged for trips
- 9 — Displays amount owing for trips taken on Linkt tolled roads with day passes Total charged for day passes
- 10 — Displays fees accrued due to trips taken on Linkt tolled roads without an e-tag. Any adjustments, such as credits, will also be included in this amount Total fees charges and adjustments
11
- 12 — Displays amount due for payment Amount due
- 13 — Date payment is required Due Date
14
- 15 — Displays GST amount incurred for shown invoice period Total GST from invoice period
Total trip charges
This section contains a summary of charges by Registration / eTag. The table below details how this section is presented in the data file.
Trips without an e-Tag device
This section contains a summary of charges by Registration / State. I.e. the vehicle has travelled on City Link toll roads without an e-Tag device.
The information provided in this section is the same as that shown above, except for the e-Tag Device column.
eTag Total Amount
This row shows the total Net Amount of the totals detailed in the Total trip charges and Trips without an e-Tag device sections above. It matches the value in the header row Total charged for trips.
Fees charges and adjustments
Total Amount
This row shows the total Net Amount for the transactions detailed in the Fees charges and adjustments section above. It matches the values in the header rows Total charged for day passes + Total fees charges and adjustments.
State Trip details
Each state (VIC, NSW, QLD), is shown in a separate section, but the details are presented in the same manner and are itemised by vehicle.
- A — Displays City Link allocated code Fleet identifier
- B — Displays the vehicle registration number and the state of travel. E.g. 'ABC123 VIC' Licence plate
- C — Displays the e-tag number for this registration number. If the vehicle has travelled without an e-Tag device, this field will be blank. E.g. 'eTag 123456789012' or ' ' e-Tag device
- D — Shows the location of the eTag reader. Travel details
- E — Start date of the trip. Travel date & time Start
- F — Start time of the trip. This is added to the description stored in Catch-e so you can see when the trip was made.
- G — Finish date of the trip. Finish
- H — Finish time of the trip.
- I — GST amount applied to trips made in the invoice period GST
- J — Net amount applied to trips made in the invoice period Net Amount
- K Account ID
State Trip details (Sub Total)
Each vehicle listed in a detail section also has a sub total row. There is no total row.
- D — Row description. The description you see depends on whether the vehicle had an eTag or not. E.g. 'Total Trips for eTag 123456789012' or 'Total Trips for ABC123 VIC No Tag-CAR' Travel details
- H — Net amount of all trips for the vehicle above. E.g. $27.33 Net Amount
MPASS
The WEX Motorpass (WEX) card can be used for purchasing fuel as well as a range for vehicle services such as maintenance, tyres, scheduled services etc. The file format must be .txt, i.e. Text (Tab Delimited). Column headings are expected in the file but not account opening and closing balances.
The MPASS interface ignores the 1st 2 rows of the file if it contains a heading like: ACCGROUP.EXTRACT05 followed by a blank row.
The real data has a column heading of "Account No" in the first column. You can leave the 1st 2 rows of the file in, or delete them prior to uploading if you prefer.
Product Code 89 is a WEX Fuel Rebate. These rebates are negative and reduce the fuel costs and (if applicable), the recharge amount of fuel.
The transactions import with a quantity value. The fuel data will change this to a zero quantity value when loaded into Catch-e. Doing this maintains continuity on fuel reports and billings where appropriate.