Fuel Card and E-Tag Interfaces

Fuel Card and E-Tag Interfaces

Documentation covering fuel card and e-tag interface file formats, validation rules, field mappings, and import business rules for Catch-e fleet management system.

BP

File format: CSV

Field mapping from BP CSV file to Catch-e table. Files do not contain header row.

Field from BP

File Sample Data

Catch-e Field

Data Treatment

Record Id

5

Cost Centre

ignored

Vehicle

ABC123

reg_no

Card No

787878 7777777 888

card_number

Transaction Date

160405

ignored

Site address township/suburb

EPSOM AUCKLAND

site_location

Proper Case eg. Epsom Auckland

Transaction Time

1419

odometer_timestamp_start

Concatenate and Format 'Transaction Date' & 'Transaction Time' eg. 2016-04-05 14:19:00

Transaction Summary Number

91000

invoice_no

Transaction Capture Source

POS

ignored

Transaction Capture method

P

ignored

Receipt No

59466

docket_number

Product Code

8

product_code

Odometer

7689

odometer

CPL Price

17690

unit_cost

Litres

5796

quantity

Amount $ Incl. GST

10253

product_net

ROUND(('Amount $ Incl. GST'/100) - 'Product GST' - 'Transaction Fee Incl. GST',2) eg. 89.16

Transaction Fee Incl. GST

0

product_net

0.00

Order Number

ignored

Reference Number

ignored

Stamp Duty Incl. GST

0

ignored

Site Number

168

ignored

Pricing Method

P

ignored

Fleet Id

ignored

Driver Id

ignored

Vehicle Id

ignored

Cost Centre

ignored

Attention Flag

0

ignored

Product GST

1337

product_gst

Decimal eg. 13.37

Rebate $ Incl. GST

0

#N/A

Card Account Number

110048396

#N/A

Site Name

BP 2GO EPSOM

ignored

Non-Financial Transaction Flag

N

#N/A

ISP Account Reference

50424849

ignored

Transaction Date

20160405

odometer_timestamp_start

Pump Price

17690

#N/A

Calculated Fields

Field

Calculation

Transaction Fee GST

fee_gst: ROUND( 'Transaction Fee Incl. GST' - ( 'Transaction Fee Incl. GST' / (1 + {gst_rate}) )),2)

Shell

File format: .ec (Excel compatible)

Business Rules

Field

Column

Calculated / Input

Card Number

U

Reg No

N

Invoice Number

L

Invoice Date

User input

Product Code

E

Description

Blank

Quantity

ROUND(H / 1000, 2)

Unit Cost

ROUND(G / 100, 2)

Product Net

AA / 100

Product Gst

Z / 100

Odometer

Q

Odometer Date

B

Odometer Time

C

Site Location

K

Converted to proper text

Docket Number

D

Validation Notes

  • Only rows with '3C' in column A considered transactions; all other rows skipped

  • Shell Card number 16 digits long; .ec file truncates to 8 digits

  • When creating new card entries: delete last digit from card number, count back 8 digits for fm_cards entry

  • Example: card number 7071340051163224 becomes 05116322

FLEETCOR

File format: CSV

File Validation

  • File must consist of 22 columns

  • Brackets surrounding Card Number ignored on import

Field Mappings

Field Mapping

Notes

Card No

Translated as SUBSTRING({Card No.}, 2, 17) on import, ignoring 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 description to add products to Card Services / Mappings records. Product Code and Description same in mapping

Quantity

Total

+

product_net

{Total} - product_gst

product_gst

{Total} - ROUND({Total} / (1+), 2)

Product Codes

When adding Card Services / Mappings records, use Product Description as Product Code.

Standard FLEETCOR Product Codes:

  • 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

File format: DAT (pipe delimited |)

File Validation

  • File must consist of 39 columns

  • Brackets surrounding Card Number ignored on import

File Columns and Mappings

Column Name

Notes

COMPANY_CD

PROCESSOR

ACCOUNT_NO

ACCOUNT_NAME

COST_CENTRE

Card No

Translated as SUBSTRING({Card No.}, 2, 17) on import, ignoring brackets

SITE_ID

SITE_NAME

SITE_ADDR

SITE_CITY

SITE_STATE

SITE_ZIP

SITETAXNO

SITE_BRAND

TXN_NO

TXN_DATE

CONCATENATE {Transaction Date} & {Transaction Time} INTO odometertimestampstart

TXN_TIME

CONCATENATE {Transaction Date} & {Transaction Time} INTO odometertimestampstart

POST_DATE

VOUCHER_NUMBER

PROD_TYPE

PROD_CODE

PROD_DESC

QTY

UNITOFMEASURE

RETAIL_PRICE/AMT

DISCOUNT

INVOICE_AMT

= {Total} - product_gst

Total

+

product_net

{Total} - product_gst

product_gst

{Total} - ROUND({Total} / (1+), 2)

RETAILUNITPRICE

DISCOUNTEDUNITPRICE

DISCOUNT_APPLIED

ODOMETER

PLASTIC_ID

PLASTIC_DESC

VEHREGNO

VEH_DESC

DRIVER_NAME

CARDHOLDER_NAME

CARDREFEXT_ID

CURRENCY

Product Codes

Product Code

Description

6

Diesel

30

Premium Diesel

3

Unleaded

CLINK (Linkt E-Tag)

File format: CSV

E-tag device records travel by drivers using e-tag device. Includes travel in multiple states: VIC, NSW, QLD.

E-tags linked to associated contract using same process as fuel cards.

Visit: https://www.linkt.com.au

Interface code: CLINK (previous supplier name: City Link)

Total Trip Charges

Data combined from Total trip charges section and State Trip details section to enable each trip imported to linked contract.

Trips Without E-Tag Device

Data from Trips without e-Tag device section creates itemised record for all trips vehicle made without e-Tag. Import succeeds if card record created to match each vehicle. Without matching card, records fail validation as 'Failed Card'.

To create matching card record for vehicle ABC123:

  • Create card with "Card Number" = 'No Tag ABC123'

Fees, Charges and Adjustments

Only fees, charges and adjustments linked to contract via registration number imported. Registration number in 'Source Reference' column and 'Details' description supported.

Accepted Details Descriptions

  • Late Toll Notice Admin Fee

  • Video Matching Fee

  • No Tag in Vehicle Fee

  • CityLink Tulla Pass

  • Admin Fee

Other fees, charges and adjustments applied at Account/Client level, not contract level.

File Columns and Mappings

Header Section

Row

Description

1

Linkt Client Account Number

2

Account name

3

Invoice number

4

Invoice period

5

Invoice issue date

6

Opening balance (previous periods)

7

Payments received

8

Total charged for trips

9

Total charged for day passes

10

Total fees charges and adjustments

11

12

Amount due for payment

13

Due date

14

15

Total GST from invoice period

Total Trip Charges Section

Column

Name

Comments

A

Current fleet id

Linkt allocated code

B

Current LPN

Vehicle registration number and state. E.g. ABC123 VIC

C

Vehicle class

E.g. CAR

D

e-Tag device

e-tag number. E.g. eTag 123456789012

E

Tag class

E.g. CAR

F

Trips

Number of trips for invoice period. E.g. 25

G

GST

GST amount all trips. E.g. $10.45

H

Net Amount

Net amount all trips. E.g. $114.83

Trips Without E-Tag Device Section

Same as Total Trip Charges except:

Column

Name

Comments

D

e-Tag device

Registration Number and State. E.g. 'eTag ABC123 VIC'

eTag Total Amount Row

Column

Name

Comments

A

eTag Total Amount

Row name

H

Net Amount

Net amount all trips for all vehicles. E.g. $7,214.94

Fees Charges and Adjustments Section

Column

Name

Comments

A

Date

Date of travel

B

Source Reference

Vehicle registration and state. Only registration used in validation

C

D

Details

Reason for fee charge

E

F

Additional Information

Date and time trip recorded

G

H

I

GST

GST amount applied

J

Net Amount

NET amount applied. E.g. $1,052.07

K

Account ID

Total Amount Row

Column

Name

Comments

A

Total Amount

Row name

H

Net Amount

Net amount all fees and charges. E.g. $1,052.07

State Trip Details Section

Separate sections for VIC, NSW, QLD presented same manner, itemised by vehicle.

Column

Name

Comments

A

Fleet identifier

City Link allocated code

B

Licence plate

Vehicle registration and state. E.g. 'ABC123 VIC'

C

e-Tag device

e-tag number or blank. E.g. 'eTag 123456789012' or ' '

D

Travel details

Location of eTag reader

E

Start

Start date of trip

F

Start time. Added to description in Catch-e

G

Finish

Finish date of trip

H

Finish time

I

GST

GST amount applied

J

Net Amount

Net amount applied

K

Account ID

State Trip Details Sub Total

Column

Name

Comments

D

Travel details

E.g. 'Total Trips for eTag 123456789012' or 'Total Trips for ABC123 VIC No Tag-CAR'

H

Net Amount

Net amount all trips for vehicle. E.g. $27.33

MPASS

File format: TXT (Tab Delimited)

WEX Motorpass card used for fuel and vehicle services (maintenance, tyres, scheduled services).

Column headings expected. Account opening/closing balances not required.

MPASS interface ignores first 2 rows if containing heading like ACCGROUP.EXTRACT05 followed by blank row.

Real data has "Account No" column heading in first column.

Note: Product Code 89 is WEX Fuel Rebate (negative). Rebates reduce fuel costs and recharge amount. Transactions import with quantity value; fuel data changes to zero quantity when loaded into Catch-e for report/billing continuity.

MPASS07

File format: TXT (Tab Delimited)

Revised file format introduced by Motorpass in 2015 (E07 format). Before 01/07/2021, WEX changed filename to E17 without format change.

Maximum 100 rows ignored until row with "Account No" heading reached. If not found within first 100 rows, file invalidated.

Format Differences from MPASS05

  • Column 18 'Merchant No' removed

  • Columns 7-17 shifted right 1 column

  • Column 7 filled by new field 'Transaction Time'

Validation and Business Rules

  • IF columns NOT = 30 (empty rows) OR column 14 (Voucher) NOT = 'Voucher' THEN validation fails: "The format of the Import File does not match the selected Card Interface! Please check your selections and try again!"

  • Any row with empty column 1 or empty column 2 ignored

  • IF column 13 (product code) = 89 THEN fm_fuel.quantity = 0 ELSE fm_fuel.quantity = column 8 (Litres)

  • IF column 17 (Brand Description) contains 'NON FUEL' AND column 21 (Merchant Name) populated THEN fm_fuel.site_location = column 21 (Merchant Name) + column 23 (Merchant Suburb) ELSE fm_fuel.site_location = column 16 (Brand Description) + column 23 (Merchant Suburb)

Note: Product Code 89 is WEX Fuel Rebate (negative). Rebates reduce fuel costs and recharge amount. Transactions import with quantity value; fuel data changes to zero quantity when loaded into Catch-e for report/billing continuity.

TROPIC

File format: CSV (Tab Delimited)

Tropic card used for fuel and vehicle services (maintenance, tyres, scheduled services).

File Validation

  • Header row: Column headings expected

  • Column count: Must be 17 columns

File Columns and Mappings

Column No

Name

Notes

0

cus

Not used by Catch-e

1

trx_date

Not used by Catch-e

2

docket

3

card1

First 8 digits of card number; combined with card2 into card_number

4

card2

Last 8 digits of card number; combined with card1 into card_number

5

trace_no

Not used by Catch-e

6

in_date

Date of transaction

7

in_time

Time of transaction

8

storeDes

Store name where transaction took place

9

state

Not used by Catch-e

10

rego

Registration number of vehicle assigned to card

11

odo

Odometer entered at time of sale

12

productAPN

Description of item sold

13

qty

Quantity of goods purchased

14

amount

Total Ex GST of discounted sale applied to customer account

15

gst

Total GST for amount of sale

PUMA

File format: TXT (Tab Delimited)

Puma card used for fuel and vehicle services (maintenance, tyres, scheduled services).

File Validation

  • Header Row 1: First 21 characters must be "PUMATRANSEXTRACT_05" or "Account No" if deleted first three rows

  • Column count: Must be 37 columns

  • Column 17: Must be "Voucher" (column 0 = first)

File Columns and Mappings

Column No

Name

Notes

0

Account No

1

Trading Name

2

Customer Group Code

3

Card No

Expected 7 or 8 characters; greater/less ignored

4

Record Type

5

Vehicle Rego

6

Odometer

7

Transaction Date

8

Transaction Time

9

Litres

10

Global Discount

11

Golden Network Discount

12

Amount

13

GST Amount

14

Net Amount

15

Cost Centre

16

Product Code

17

Voucher

18

Reference

19

Billed Date

20

Brand Description

21

Driver Name

22

Merchant No

23

ABN Number

24

BRN Number

25

Merchant Name

26

Merchant Street Line 1

27

Merchant Street Line 2

28

Merchant Suburb

29

Merchant State

30

Merchant Postcode

31

Curr Merchant Source Code

32

Card Description

33

Product Name

34

Pump Price

35

Estimated Quantity

36

List Price

United

File format: CSV (Tab Delimited)

United card used for fuel and vehicle services (maintenance, tyres, scheduled services).

File Validation

  • Header row: Column headings expected

  • Column count: Must be 17 columns

File Columns and Mappings

Column No

Name

Notes

0

SITE-CODE

Not used by Catch-e

1

SITE-NAME

2

CUSTOMER-CODE

Not used by Catch-e

3

GROUP

Not used by Catch-e

4

NAME-ON-CARD

Not used by Catch-e

5

CARD-NUMBER

6

DATE

Combined with TIME into odometer_timestamp_start

7

TIME

Combined with DATE into odometer_timestamp_start

8

REFERENCE

9

PRODUCT-CODE

10

PRODUCT-DESC

11

ODOMETER

12

SIGN

Not used by Catch-e

13

UNIT-PRICE

14

VOLUME

15

VALUE-INCGST

ROUND(({VALUE-INCGST} – {DISC-INCGST}) / (1 + ), 2)

16

DISC-INCGST

ROUND({VALUE-INCGST} / (1 + ), 2)

Summit

File format: XLS (save as TXT Tab Delimited prior to importing)

Import Business Rules

Field Source

Column

Mapping

invoice_no

A

Start identified by column heading "Invoice Num"

invoice_date

B

odometer_timestamp

C

reg_no

F

card_number

G

product_code

M

E.g. "Monthly Rental"

description

M

Use Description Import Type = Replace

product_net

O

product_gst

P

7ELEVEN

File format: TXT (Tab Delimited)

7-Eleven card used for fuel and vehicle services (maintenance, tyres, scheduled services).

File Validation

  • Header Row 1: First 21 characters must be "E07REPORTEXCLMERNO" or "Account No" if deleted first three rows

  • Column count: Must be 30 columns

  • Column 14: Must be "Voucher" (column 1 = first)

File Columns and Mappings

Column No

Name

Notes

1

Account No

2

Card No

Expected 7 or 8 characters; greater/less ignored

3

Record Type

4

Vehicle Rego

5

Odometer

6

Transaction Date

7

Transaction Time

8

Litres

9

Amount

10

GST Amount

11

Net Amount

12

Cost Centre

13

Product Code

14

Voucher

15

Reference

16

Billed Date

17

Brand Description

18

Driver Name

19

ABN Number

20

BRN Number

21

Merchant Name

22

Merchant Street Line 1

23

Merchant Street Line 2

24

Merchant Suburb

25

Merchant State

26

Merchant Postcode

27

Card Description

28

Product Name

29

Unit Price

30

Estimated Quantity

Notes

  • Each interface has specific file format requirements; verify format before import

  • Field mappings vary by provider; refer to specific interface section

  • Validation rules must pass for successful import; check column count and required fields

  • Date/time fields often combined using CONCATENATE

  • GST calculations using formula pattern: ROUND({Total} / (1 + {gst_rate}), 2)

  • Card numbers may require truncation or substring operations

  • E-tag providers have special handling for trips without devices

  • Some interfaces require saving XLS as TXT before importing

  • Product codes vary by provider; standard codes must match Card Services / Mappings setup

  • Rebates (negative amounts) handled specially to maintain report and billing continuity