KeyTotalTreasury logo
Online Help

Data Import and Export


The Invoice Payment function makes use of E-Bill & Collect’s dynamic file processing capabilities to support the import of account and invoice data, and the export of payment and invoice payment and return data.

The file layouts and selected configuration workbook are embedded below.

File Format and Configuration

The file format an configuration options for each file are as follows:

File Format

The dynamic file format is a delimited file, where every data field is separated by a delimiter.

Fields themselves are enclosed within double quotes. If a field contains double quotes further double quote serve as escape characters

Each data record should end with a Carriage Return character to ensure correct formatting if the file is viewed in Notepad, Wordpad etc. Finally, the file can optionally contain a file header that names the fields present in the file.

File Configuration

The configuration of the dynamic file for a Partner is managed through the Partner Configuration Manager, File Layout Configuration option.

The import file is configured under the appropriate File option, which may already be present or may need to be added as a new file. Selecting the Dynamic Format option in the file layout selector dropdown will display the screen shown on page overleaf.

The following options are then displayed:

  • Partner Key passed as part of file. If the Partner Key is to be passed as part of the file Yes should be selected, otherwise No. This is typically used only in more complex partner set ups.
  • Account Type passed as part of file. If the customer’s account type is to be passed as part of the file Yes should be selected, otherwise No. This is typically used only in partner set ups where business rules are driven by the customer account type.
  • Select Delimiter. There are four options for the delimiter to be used to separate the data fields within the file record. These are:
    • Comma [ , ]
    • At Sign [ @ ]
    • Pipe [ | ]
    • Asterisk [ * ]
  • File Contains Header. Selecting this checkbox will cause the first record in the file to display the field names of the fields in the file rather than customer data.
  • Add/Remove Selected Fields. These list boxes and controls are used to specify the fields that are present in the file and the order they appear in.

    When accessed for the first time the Left Hand box will contain all of the optional data elements and Right Hand box the mandatory data elements.

    (The Partner Key and Payee Account Type fields initially appear as mandatory until they are deselected using the radio buttons shown above.)

    The user can then select Optional fields and move them to and fro to set the fields to be passed in the file.

  • Move Up/Move Down. These buttons allow the user to alter the order of the data fields in the RH box to correspond with the order they will be sent in the file.

    Once the user has configured the file they can Submit the changes. Any files uploaded will then need to correspond to the newly specific file layout.

Account Import File

The Dynamic Account Import file is used to load details about customers and any amounts owed into E-Bill & Collect and is used to verify customer data during enrollment, verify payments that have been entered and trigger any automated payments.

NOTE: Full details of the file can be found in the spreadsheet E-Bill & Collect Dynamic File Specification, in the Account Import worksheet.

File Content

The import file contains only 4 mandatory fields. These are:

  • Customer (Last Name). Customer’s full name can be sent in this field, or it can be combined with the optional Customer First Name and Middle Initial fields to send the name separately.
  • Customer Account Number. The account number or ID that uniquely identifies the customer account within the Partner hierarchy.
  • Payment Due Date. The date that the payment is due to be paid on the customer’s account.
  • Current Statement Balance. The amount that is due to be paid on the customer’s account.

    NOTE: The above 4 records must be supplied to create an account in the system. As the Payment Due Date and Current Statement Balance do not apply to invoice payments these will need to populated with default data ($0.00 and 12/31/2013 for example.

    They are not shown to the customer, so the values sent can be anything.

    Additional optional data fields can be passed in the data import file, and are described in the batch file specification spreadsheet. These fields are not needed to successfully process a payment but are used to provide more payment options, control and verify the payments entered and so on.

    NOTE: If the Web Direct Integration method is being used one of optional SSN, Zip Code or Online Enrollment Code fields will need to be passed in the file or customers will not be able to enroll for payment processing.

File Names

The expected file name of these files is:

ORBI.IMPORT.[PARTNERKEY].MMDDYYYY.HHMMSS.DAT

Where [PARTNERKEY] is the Partner Key for that Partner.

Invoice Data Import File

The Invoice Data Import file is used to load details about a customer’s invoices into E-Bill & Collect and is used where the Invoice Payment option is being made available.

NOTE: Full details of the file can be found in the spreadsheet E-Bill & Collect Dynamic File Specification, in the Invoice Data Import worksheet.

File Content

The import file contains only 7 mandatory fields. These are:

  • Partner Key. This uniquely identifies the Partner within E-Bill & Collect .
  • Customer Account Number. The account number or ID that uniquely identifies the customer account within the Partner hierarchy.
  • Invoice Number. The number that uniquely identifies this invoice.
  • Invoice Description. Description of the invoice to be paid.
  • Invoice Amount. The amount that is due to be paid on this customer invoice.
  • Invoice Status. The status of the invoice.
  • Partial Payment Allowed? A flag that determines whether the customer can make a partial payment against the invoice.

Additional optional data fields can be passed in the file, and are described in the batch file specification spreadsheet. These fields are not needed to successfully process a payment but are used to provide more payment options, control and verify the payments entered and so on.

NOTE: If images of the invoice are to be made available the Invoice Image URL or Filepath parameter must be added to the file layout.

File Names

The expected file name of these files is:

ORBI.INVOICEDATA.[PARTNERKEY].MMDDYYYY.HHMMSS.DAT

Where [PARTNERKEY] is the Partner Key for that Partner.

Payment Posting Files

The Payment Posting files are used to advise the Partner about payments that have been processed and about any returned payments received. Separate files are produced for Payments and Returns, at different times of the day.

NOTE: Full details of the file can be found in the spreadsheet E-Bill & Collect Dynamic File Specification, in the Payment Posting worksheet.

File Content

The import file contains only 6 mandatory fields. These are:

  • Customer (Last Name). Customer’s full name can be sent in this field, or it can be combined with the optional Customer First Name and Middle Initial fields to send the name separately.
  • Customer Account Number. The account number or ID that uniquely identifies the customer account within the Partner hierarchy.
  • Payment Date. The date that the payment was processed.
  • Payment Amount. The amount of the payment that was processed.
  • Payment Method. The funding source used to pay for the payment.
  • Payment Confirmation Number. The unique confirmation number for the payment.

Additional optional data fields can be passed in the data export files, and are described in the batch file specification spreadsheet. These fields are not needed to successfully process a payment but are used to provide more details back to the Partner (for example, any Email Address captured when the customer enrolled to process payments).

File Names

As noted above, two files are sent, one for payments, one for returns. The expected file names of these files is:

ORBI.ALLPAY.EXPORT.[PARTNERKEY].MMDDYYYY.HHMMSS.DAT

ORBI.ALLRET.EXPORT.[PARTNERKEY].MMDDYYYY.HHMMSS.DAT

Where the first is for payments, the latter is for returns and [PARTNERKEY] is the Partner Key for that Partner.

Invoice Posting Files

The Invoice Posting files are used to advise the Partner about invoice payments that have been processed and about any returned payments received. Separate files are produced for Payments and Returns, at different times of the day.

NOTE: Full details of the file can be found in the spreadsheet E-Bill & Collect Dynamic File Specification, in the Invoice Posting worksheet.

File Content

The import file contains only 7 mandatory fields. These are:

  • Customer (Last Name). Customer’s full name can be sent in this field, or it can be combined with the optional Customer First Name and Middle Initial fields to send the name separately.
  • Customer Account Number. The account number or ID that uniquely identifies the customer account within the Partner hierarchy.
  • Invoice Number. The number that uniquely identifies this invoice.
  • Invoice Payment Date. The date that the payment was processed.
  • Invoice Payment Amount. The amount of the payment that was processed.
  • Payment Method. The funding source used to pay for the payment.
  • Payment Confirmation Number. The unique confirmation number for the payment.

Additional optional data fields can be passed in the data export files, and are described in the batch file specification spreadsheet. These fields are not needed to successfully process a payment but are used to provide more details back to the Partner (for example, any Email Address captured when the customer enrolled to process payments).

File Names

As noted above, two files are sent, one for payments, one for returns. The expected file name of these files is:

ORBI.INVPAY.EXPORT.[PARTNERKEY].MMDDYYYY.HHMMSS.DAT

ORBI.INVRET.EXPORT.[PARTNERKEY].MMDDYYYY.HHMMSS.DAT

Where the first is for payments, the latter is for returns and [PARTNERKEY] is the Partner Key for that Partner.