Introduction

YayPay allows integration with our platform using CSV files. CSV files should contain the data that YayPay needs to operate and depending on your setup, can be generated by your ERP reporting tools or using your preferred ETL/Integration software.

YayPay would need to have the following entities in the CSV files:

• Customers
• Customers' contact details
• Invoices 
• Invoice Items
• Payments, Credit Memos, and other documents that impact AR ledger.
• Transaction allocation information - what documents are allocated to what Invoices. 
• Sales order (Optional)

All the CSV files have to be enclosed in a ZIP file, and also placed on your FTP Server. For more info, see the Connect YayPay to your FTP Server article. 

This article explains each CSV file and the diagram below displays the relationships between the data objects listed above.

Below you can find a detailed description of each CSV file that would contain this information. 

There are two modes of CSV data upload that you can use:

• Snapshot. In this mode on your every upload of the CSV files to YayPay, you are loading an entire snapshot of the AR data from the ERP. This will take more time to load just because of the file size, but it makes sure that at every upload your data is exactly the same as you expected. 
• Incremental. In this mode, after the initial data load, you are only loading updates to the documents. The only risk here is if for any reason one of the uploads did not go through, and the next one does not mention updated documents - they will stay in an incorrect state in YayPay.

If possible, we recommend using an Incremental approach, but with a floating window of the update documents. For example, every day you can be sending all the updated documents for the last 7 days. This will allow the synching process to solve-correct in case of errors.

CSV files can be loaded manually or automatically through the FTP server, configured on the customer side. 

CSV Integration works the same way as a regular integration, except that CSV files need to be uploaded to Yaypay regularly in order to keep the system up-to-date with the AR situation in the client's ERP (e.g. these regular updates help to facilitate workflow collection & reminders sent to your end-customer by using the latest information. It is recommended for updated CSVs to be uploaded on a daily basis.

CSV Format Requirements

YayPay needs to receive CSVs in a certain format in order to be able to process them properly so that the values correspond to what is expected.

The first row of the CSV files should always contain column names. 

YayPay recommends quoting text, so any data that includes a comma in the system doesn't shift the output.

Data Types used:

1. Double - any number with . as a separator
2. Boolean - String with the value true or false. This value is case sensitive and must be in lower case only 
3. String - any String value
4. Date - Date in format yyyy-MM-dd'T'HH:mm:ss. Example of valid date: 2017-08-13T11:05:19. 

All CSV files should be in the same zip file. The zip file should contain:

• customer.csv
• contact.csv
• invoice.csv
• invoiceLines.csv
• transaction.csv or transactionFull.csv
• transactionAllocations.csv
• salesOrder.csv (not obligatory) 

Customers

This CSV file should include information about all of the customers in the system, including ones that are not currently active.

Download customer.csv template

Filename MUST be: customer.csv   

For Custom Fields:

1. Json Column:

• The column header in the file must be called "customFields"
• Add unlimited custom fields/values for each record/row in the customer.csv file. The format must be as follows:

1. JSON data-interchange format is used
2. The text must be enclosed in double quotes and additional double quotes are required in JSON format
3. Open with a curly bracket, add a double quote before the curly bracket
4. Enclose the first custom field name in two double quotes (JSON and CSV format requirements), separated by a colon, followed by the value in two double quotes
5. Repeat the same manner for the second, third and fourth custom fields (each separated by a comma before beginning the next one)
6. Close with a curly bracket and add the final double quote

An example for customer.csv with 2 custom fields could be:

"{""customer_group"":""Online"",""customer_industry"":""Media""}"

The field/value string must be in the same cell. It is not to be placed in separate columns in the CSV file

2. Custom fields columns: 

•  For each custom field, you can add a column with a name starting with cf_
•  Each column is a separate custom field
• You are unable to configure both solutions. If "customFields" column exists the Json Column solution will be used.

Contacts

This CSV should include contact details of the customer and/or invoice that will be part of the recipients used in the collection workflows. 

Download contact.csv template

Filename MUST be: contact.csv

Transactions

This CSV should include all the transactions that influence customer's AR balance, such as:

• Payment
• CreditMemo
• JournalEntry
• Adjustment

Download transaction.csv template

Filename MUST be: transaction.csv

Download transactionFull.csv template

This file supports invoices along with credit memos, payments, journal entries, and adjustments.

Note, that you can use ONE out of the two files: transaction.csv or transactionFull.csv, and NOT both of them.

Filename MUST be: transactionFull.csv

Transaction Allocations

This document would represent the relations between applied transactions and invoices.

Download transactionAllocations.csv template

Note, if you send an invoice allocation information, and invoice Open amount or invoice status changed - this invoice must be listed in the invoice.csv file. It is not enough to just list it in transaction allocations. 

Filename MUST be: transactionAllocations.csv 

Invoices

This document should have a list of open and closed invoices.

Download invoice.csv template

The filename MUST be: invoice.csv

*Example:

In YayPay, the invoices can be unpaid, partially paid, and paid.

On the Statement page, paid invoices are found in the CLOSED INVOICES tab, and unpaid and partially paid invoices are found in the OPEN INVOICES tab.

Consider the following rules regarding the “paid” field:

• IF "paid" = 0 THEN the invoice is unpaid
• IF "paid" > 0 AND paid < "amount" THEN the invoice is partially paid
• IF "paid" >= "amount" THEN the invoice is paid
   

For Custom Fields:

1. Json Column:

• The column header in the file must be called ‘customFields’
• Add unlimited custom fields/values for each record/row in the invoice.csv file. The format must be as follows:

1. JSON data-interchange format is used
2. The text must be enclosed in double quotes and additional double quotes are required in JSON format
3. Open with a curly bracket, add a double quote before the curly bracket
4. Enclose the first custom field name in two double quotes (JSON and CSV format requirements), separated by a colon, followed by the value in two double quotes
5. Repeat the same manner for the second, third and fourth custom fields (each separated by a comma before beginning the next one)
6. Close with a curly bracket and add the final double quote

An example of invoice.csv with 3 custom fields could be:

"{""sales_terms"":""Net 30"","ship_method"":""TNT Express"",""term_name"":""5% Discount""}"

The field/value string must be in the same cell. It is not to be placed in separate columns in the CSV file

2. Custom fields columns: 

• For each custom field, you can add a column with a name started with cf_
• Each column is a separate custom field
• You are unable to configure both solutions. If 'customFields' column exists the Json Column solution will be used. 

Invoice Items

Download invoiceLines.csv template

Filename MUST be: invoiceLines.csv

Sales Order 

Download salesOrder.csv template

Filename MUST be: salesOrder.csv 

Delivery of files to YayPay

  You should upload zip file with content to YayPay. Zip archive structure is as follows:

  /customer.csv

  /contact.csv

  /invoice.csv

  /invoiceLines.csv

  /transaction.csv or /transactionFull.csv

  /transactionAllocations.csv

  /salesOrder.csv (not obligatory)

Transaction File Mode with Invoices

The CSV Connector provides the ability to upload the transaction file that includes ALL the open documents that impact AR balances, including invoices. 

The new CSV file has the name transactionFull.csv. Besides, the CSV Connector supports processing the ‘old’ transaction.csv file. 

How the CSV Connector works if your ZIP archive includes a transactionFull.csv file: 

1. Regardless of the fact that you can upload a new file transactionFull.csv with open entities, the CSV Connector proceeds to support the sync process for the ‘old’ transaction.csv file. The CSV Connector can switch the sync process in a new way to sync the ZIP archive with the transactionFull.csv file:
 

a) If the ZIP archive includes the transactionFull.csv file, the Connector handles the ZIP archive using new sync. 

b) If the ZIP archive includes the transaction.csv file, the Connector handles the ZIP archive using old sync. 

c) If the ZIP archive includes both files (transactionFull.csv and transaction.csv), the Connector fails the sync process with the error message.

2. The peculiarity of the new transactionFull.csv file is to support the new document type - Invoice. Thus, the new transactionFull.csv file combines the following AR documents:

• CreditMemo
• Payment
• JournalEntry
• Adjustment
• Invoice → NEW 

3. Due to the new logic of processing the transactionFull.csv file, to sync Invoices to YayPay, you can fill in the required fields for Invoices in the transactionFull.csv file. Additional fields for Invoices can be filled in the invoice.csv file. The transactionFull.csv file structure requirements are given above.

By default, the "dueDate" field is optional for Credit Memo, Payments, JournalEntry, and Adjustments because these types of documents don’t use Due Date. The "dueDate" is required only for Invoices. Thus, during the validation process, the CSV Connector checks if the "txType" is equal to Invoices. If txType = Invoices, the "dueDate" field must be filled in.

So, the invoice.csv file is an optional file during the sync process.

4. The CSV Connector uses the invoice.csv file as a complementary file with additional data for Invoices that the CSV Connector will sync from the transactionFull.csv file.

The transactionFull.csv file prevails over the invoice.csv file. If during the sync process the CSV Connector doesn’t detect a connection for Invoices between the transactionFull.csv file and invoice.csv file, the CSV Connector doesn’t proceed with the sync process to sync Invoices from the transactionFull.csv file.

5. Connection of the transactionFull.csv file with the invoiceLines.csv file.

The "invoiceId" field from the invoiceLines.csv file should exactly match the "txId" field of the associated invoice from the transactionFull.csv file.

6. Connection of the transactionFull.csv file with the transactionAllocations.csv file.
The "invoiceId" field from the transactionFull.csv file should exactly match the "invoiceId" field of the associated Invoice from the transactionAllocations.csv file.

7. Determining a Positive or Negative Amount in the transactionFull.csv file.

In the transactionFull.csv, the "amount" field defines if this is a Credit or a Debit document.

Therefore, in the transactionFull.csv field "amount" is UNIFIED. This means that the sign of the "amount" field should define if this is a Credit or a Debit document.

 

Example:

Invoice: $ 800
Payment: -$300
CreditMemo: -$100
Adjustment 1: -$50
Adjustment 2: $150

Total Balance: $500

Thus, if you fill in the Payment amount -$300 or $300, and Credit Memo amount -$200 or $200 in the CSV file, the CSV Connector will use the opposite value.

Example:

-$300 → invert to $300
$300 → invert to -$300

The Connector applies the amount inversion for the following fields in the transactionFull.csv file:

- "amount"

- "amountApplied"