CSV File Format Definition

Introduction

The aim of this document is to provide you with a process overview and inform you of the standards and requirements for the CSV files to be sent to Corporate LinX (CLX) by our customers. This is typically as a scheduled over-night task.

It is of the utmost importance that the unique references are specified. In your accounting/ERP system, the standard practice may be to reset the transaction references within each instance each year. This means that a unique identifier for a transaction typically consists of 3 sections. For example, “5100001320FR012020”, the 3 sections are:

Number (the number for the transaction), in the example above, this would be “5100001320”,
Instance (the instance name/code), in the example above, this would be “”FR01”,
Fiscal Year (the accounting year for the transaction), in the example above, this would be “2020”.

In this situation, please compute and send us the unique references in the daily feed, if this is something you wish for us to do for you, please let us know.

Terminology

The following terms are used all over our portal, this table helps define this terminology.

Terms	Description
Masterdata	This is all active debtor and creditor company’s data in the customer’s system. E.g., global tax reference.
Transactions	This is a collective reference to the invoices, credit notes, debit notes, payments, offers, etc..
Source System	Customer’s IT system(s) from where the required transactions and master data will be extracted.
VAT Reference	The VAT or Code TVA reference for the buyer or the supplier company. This is used as a primary company identifier in the portal, and it’s required for transactions that are being funded.
Debtor	This is the buyer company.
Creditor	This is the supplier company.

Process Overview

At a scheduled time (to be agreed upon), each day the client will create and send us CSV files, in most cases, a master data file and a transactions data file. This is uploaded to the CLX hosted Document Management System (DMS) within your portal site.

Upon specific request, we can provide a SFTP drop folder if this is preferred. However, our DMS ensures that you can put the new data directly in to your client portal and is easy to work with, so this tends to be our preference.

It is recommended that the following file naming conventions are used:

MasterData_YYYYMMDD-hhmmss.csv:
- MasterData – this is the file type,
- YYMMDD – this is the date of the file’s creation,
  - YYYY – Year, e.g., 2021,
  - MM – Month, e.g., 01 for January,
  - DD – Date, e.g., 07 for the 7th.
- hhmmss – this is the time of the file’s creation,
  - hh – Hour (24-hour format), e.g., 23 for 11pm,
  - mm – Minutes,
  - ss – Seconds.
- For Example: MasterData_20211203-132800.csv

It is important to note here that, if you have multiple Source Systems, then the preferred naming method would be: SourceSystem_MasterData_YYYYMMDD-hhmmss.csv.

Transactions_YYYYMMDD-hhmmss.csv
Transactions – this is the file type,
YYMMSDD – this is the date of the file’s creation,
- YYYY – Year, e.g., 2021,
- MM – Month, e.g., 01 for January,
- DD – Date, e.g., 07 for the 7th.
hhmmss – this is the time of the file’s creation.
- hh – Hour (24-hour format), e.g., 23 for 11pm,
- mm – Minutes,
- ss – Seconds.
For example: Transactions_YYYYMMDD-hhmmss.csv

Customers may also integrate companies and transactions from other IT systems, at which time, another pair of files as detailed above will be required to be sent to CLX for data consolidation purposes in the portal.

Something that may make it a little easier for a customer, is to create multiple files (for example, an Open Items/OI file and a Closed Items/CI file for items that are being paid). The system can handle this and will process files in the order that they are named.

CLX will monitor the agreed pickup directory and will process files placed there, once processed, they are moved to the Processed subdirectory

CLX may further be working with funders to offer Reverse Factoring solutions to our customer’s suppliers. What this means is:

The customer will amend the payee for suppliers that sign up with funders, and this information will be included as the FunderRef element in transactions.

Process Notes

Key Assumptions

Please note: these assumptions are related to the first phase of the project, where they may change in later phases.

Basic Assumptions:
- The customer has one system that will be the source of master data and transactions data (e.g., a SAP instance),
- All transactions will be sent each day at an agreed time,
- Payments will not be changed after being sent, e.g., adding/removing invoices to a payment reference that already exists.
Funding Assumptions:
- Approved Invoices in the funding process will not be changed,
  - Approved state cannot be changed to blocked or rejected,
  - Credit notes cannot be attached to them,
  - Key details must not change o Will be paid according to the agreed payment calendar rules.

Although, we don’t enforce this, as our duty is to serve up to your partners what you give us, the issue is that breaking this rule it creates funding calculations.

Company Synchronisation

The customer will send a master data file which should contain all active master data. It should contain all suppliers, buyers and funders from their system that are in the related transactions files that will be sent daily.

The system will extract any new companies from this file and add them to the portal and use the references given to identify companies on each transaction.

For a company to participate in the funding process, at least one VAT reference must be provided for them. This VAT reference will be used as the primary identifier for a company in the UI when and when sending the data to funders.

A customer will also be required to send their own system identifier for the company, it is important to note that:

A single company may have ore than one VAT reference,
A single company may have more than one internal identifier.

Transactions

The transactions file will need to contain all changes that you wish to apply to your data in the portal. For example, if you wish to update the state of your transaction from “Approved” to “Paid” it would need to be sent with this field updated. It is recommended that all unpaid transactions plus all paid transactions where the payment info is being added today are included within the transactions file.

The system will reflect any changes or updates to the transaction unless a prior agreed business rule is broken. Following our standards when producing your transaction files will ensure that the portal is correct. It means that we can take data as is without any customisation/rules on top of that.

CSV Row Definitions

This next section covers our expected CSV data format. It will cover which elements are required and which ones are optional. If required fields are not populated, then the data will not go into our system correctly.

Masterdata

Master data is needed for ALL companies. This means we need the following data for all Buyers, Suppliers and Funders that are referenced by the same day’s Transactions file at least once, but we usually recommend matching the data in the master data file to the transactions sent that same day.

Example

The data below shows an example of what we expect the data you send us to look like in your master data CSV file. Here, all the fields are populated, despite some fields being marked as optional.

Perspective,Name,Reference,GlobalTax,Address.Line1,Address.Line2,Address.City,Address.Region,Address.Country,Address.PostCode,FundingType,FunderRef 
Buyer,Coporate Linx,x1234,GB1234,Unit A,Rennie Gate,Andover,Hampshire,GB,SP10 3TU,Systematic,FR1234 
Supplier,Coporate Linx,xyz12,GB2345,Unit A,Rennie Gate,Andover,Hampshire,GB,SP10 3TU,ALaCarte,FR1234

Data Overiew

Element Name	Type	Description	Required?
Perspective	String	Must be one of: Buyer, Supplier, Funder.	Yes
Name	String	The name of the company.	Yes
Reference	String	Your system reference.	Yes
GlobalTax	String	The Tax Reference of the company.	Yes
Address.Line1	String	The first line of the company's address.	No
Address.Line2	String	The second line of the company's address.	Yes
Address.City	String	The City or Town where the company is based.	Yes
Address.Region	String	The Region/County where the company is based.	Yes
Address.Country	String	The 2 Char ISO Country Code where the company is based.	Yes
Address.Postcode	String	The Postcode of where the company is based.	Yes
FundingType	String	Must be either: Systematic ALaCarte This tells us how to configure this company for funding scenarios, if this is not provided, the transactions linked to this company will not be provided for funding.	No
FunderRef	String	This is the reference for the funder that will be used when funding this company’s transactions.	No

Transactions

Example

The data below shows an example of what we expect the data you send us to look like in your Transactions CSV files. The following data would generate a 2-line Invoice and a Credit Note with a single line.

Type,State,Reference,CreditorTransactionRef,DebtorRef,CreditorRef,VATRef,Payref,Currency,DocDate,DueDate,PaymentDate,GrossValue,TaxValue,PayRef,IsFundable,IsRaLine,AlsoPayOffer 
Invoice,Approved,CLX123,x1234,CLX456,CLX153,GB1234,,GBP,2021-01-01,2021-03-01,2021-02-20,1000.00,200.00,,false,, 
Invoice,Approved,CLX123,x1234,CLX456,CLX153,GB1234,,GBP,2021-01-01,2021-03-01,2021-02-20,1200.00,240.00,,false,, 
Credit,Approved,CLX124,y1234,CLX456,CLX153,GB1234,,GBP,2021-01-01,2021-03-01,2021-02-20,1000.00,200.00,,false,,

Data Overview

The following table details what fields are required and what fields are optional:

Element Name	Type	Description	Required
Type	String	Must be one of: Purchase Order Invoice Credit	Yes
State	String	This is flexible per process, but we would recommend one of: Approved, ForApproval, Held, Paid, Proposed, RejectedCanceled.	Yes
Reference	String	This is your reference for the transaction, this needs to be globally unique as it matches future versions of the transaction using this.	Yes
CreditorTransactionRef	String	This is the supplier’s invoice reference, or the “VAT reference” for the transaction.	Yes
DebtorRef	String	This is your reference for the buyer.	Yes
CreditorRef	String	This is your reference for the supplier.	Yes
VATRef	String	The VAT reference for the supplier.	Yes
Currency	String	This is the currency used for the transaction amounts, this needs to be the standardised currency code, e.g., GBP, EUR, USD.	Yes
DocDate	String	The “tax point” or the issue date of the transaction.	Yes
DueDate	String	The date which the transaction is due to be paid.	Yes
PaymentDate	String	The expected payment date (or the date of the pay run).	Yes
RelatedTransactionRef	String	This property is to be set on Credits ONLY and should be populated with the reference of the Invoice that you wish to link the Credit to.	No
PayRef	String	This property is the payment reference ant is ONLY required when generating a line on a Remittance Advice. Please note that when generating payments, the state on the transaction should be set to “Paid”.	Yes – When the state is set to paid
Value	Number	This is the total value, excluding VAT.	Yes
GrossValue	Number	This is the total value, including VAT.	Yes
Tax Value	Number	This is the amount of tax applied to the transaction line.	Yes
IsFundable	Boolran	This tells the system whether this line can be funded or not.	No
IsRALine	Boolean	This tells the system that the line is to be treated as Remittance Advice info when it’s set to true.	Yes – For Remittance Advice
AlspPayOffer	Boolean	This clears any attached accepted offers on a transaction.	Yes – For Remittance Advice

There are a few, very important things to note here when producing your transaction files:

Dates should be in the format “yyyy-mm-dd”, this applies to ALL date columns (DocDate, DueDate, PaymentDate),
Monetary values/amounts should be formatted using UK number formatting, e.g., “1000.00”,
Transactions are grouped by Reference to determine what line items on each transaction, so each row of data with the same reference will appear as lines on a single transaction in the UI.
For Remittance Advice rows they are grouped by PayRef, so rows with the same PayRef will appear as lines on a single Remittance Advice in the UI.

Transaciton State Transitions

State transition files can be used to denote a “state only” change to a transaction without it affecting anything else other than the transaction listed. It is important to note here that the state information is held on the header in our database, so it is not possible to have a RA that is part cancelled or part paid. Giving us a single line for the RA that denotes that the RA is “RejectedCancelled” will cancel the entire RA so the invoices paid by it will no longer be paid.

Example

Type,State,Reference,Description
Invoice,Approved,CLX123,
Invoice,Held,CLX123,Checking ...
Credit,Approved,CLX124,
Remittance,RejectedCancelled,CLX012,Internal Product Question Raised

The following table details the required fields to update an existing Transaction’s state:

Element Name	Type	Description	Required
Type	String	Must be one of: Purchase Order Invoice Credit	Yes
State	String	This is flexible per process, but we would recommend one of: Approved, ForApproval, Held, Paid, Proposed, RejectedCanceled.	Yes
Reference	String	This is your reference for the transaction, this needs to be globally unique as it matches future versions of the transaction using this.	Yes
Description	String	Comment or reson behind the change	Yes