Settlement & Reconciliation

Payment Provider Settlement - Customer Payment

How customer payments from payment providers are settled and reconciled.

The "Payment Provider Settlement" is used for the settlement-files from payment-providers who´s settlement-files can be transformed into a CSV-file (e.g. eNett, Amex (vPayment), Amadeus (B2B Wallet) etc.) as well as the PayPal native settlement format.

This handout covers the settlement of customer payments.

Setup Steps

Some program setup has to be done by our Helpdesk Team.. Please get in contact with them for the initial setup.
1. Once that is done, you are provided with a new menu item: Reconciliation → Payment-Provider-Settlement
Order Master Data → Payment-Provider:
Note: It is possible to work with inheritance here. If the settings are to apply to several organisational units, these settings must be maintained in the highest organisational unit in the hierarchy.

fig 1: Order Master Data - Payment-Provider

Field explanation:
1. Payment Provider
  Here you type in the name of the payment provider (free text input), it is used for you to recognise this payment provider throughout the application.
2. Format
  Here you choose the format of the settlement file, which the payment provider provides.
  Initially (on first import of that file format of this provider) the given information in the file has to be mapped first to the corresponding fields in
  Midoco before processing the import (for a detailed explanation see Import a payment provider file below). For each following upload for that provider, Midoco will remember those initial settings.
3. Allocation Account
  This field is supposed to contain the account ID which is to be used when allocating the account entries for this payment provider.
  Note: Until release 12.8 the account list in Midoco allowed only one account of the type 'allocation account'. This account only considered credit accounts.
  Now there is a second account type created to cover the allocation of customer payments: 'allocation account (debit)'.
  With this account it is now possible, to have a cost centre in the journal entries.
  The previous 'allocation account' was renamed to 'allocation account (credit)', but works as before.
4. Debtor (debit account)
  This represents the debit account of the payment provider.
5. Account Transaction Tee VAT
  Transaction fees with VAT will be booked on this account.
6. Cost Centre Transaction Fee VAT (optional)
  In case your revenue/expense accounts require cost centre information, you can set the cost centre in here for a transaction fees with VAT.
7. Account Transaction Tee VAT Free
  Transaction fees with VAT will be booked on this account.
8. Cost Centre Transaction Fee VAT Free (optional)
  In case your revenue/expense accounts require cost centre information, you can set the cost centre in here for a transaction fees without VAT.
9. Ignore Transaction Fees
  If this check box is checked, Midoco will ignore all transaction fees.

General Note

The transaction fees will not be booked directly in an order, but on the open entry number (external invoice number) of the settlement. You can find it in the journal entries list and in the open items list.

Midoco Mode

FIBU (accounting / booking)

VERK (only control, no booking)

Application range

Accounting - reconciliation payment provider

Import a payment provider file

Open the programm:

fig 2: Open the program Payment Provider Settlement

You will be presented with the following screen:

Tab Import / Search Settlement

fig 3: Import / Search Settlement

Details:

Search Criteria
1. Creation Date From/To
  Date, on which the settlement was uploaded by a user.
2. Settlement Date From/To
  Date, which is settled within the file. Transaction Date of the settlement.
3. Settl. Amount From/To
  Total amount of the settlement.
4. Checked
  Here you can select to search for checked / not checked settlements ('both' is going to check for both status) .
  Note: This always refers to the entire settlement, e.g. "checked = yes" means, that the entire settlement was checked (if only single data records were checked,
  then this settlement does not appear with this flag, but with "both" and "no").
5. Booked
  Note: This always refers to the entire settlement, e.g. "booked = yes" means, that the entry settlement was booked (if only single data records were booked,
  then this settlement does not appear with this selection, but with "both" and "no").
6. Payment Provider
  Name of the Payment Provider
7. External Invoice No.
  Invoice number of the settlement (given by the payment provider).
Results Table
1. Payment Provider
2. File Name
  File name of the imported file.
3. Invoice Number
  External invoice number of the payment provider within this settlement file.
4. Total Amount
  Settlement amount of the imported file.
5. Invoice Date
  Date, which is settled within the file.
6. Creation Date
  Date, on which the settlement was uploaded by a user.
7. Checked
  Number of checked data records in this settlement.
8. Booked
  Number of booked data records in this settlement.
9. Show
  Opens the selected settlement
10. Check
  Checks all data records of the selected settlement.
11. Delete
  Deletes the selected settlement.
  Note: This is only possible, if no data record was booked already.
Buttons
1. Search
  The system uses the selected criteria to search for the corresponding imported settlements.
  Note: Minimum information required is one date field, else this button is deactivated.
2. Import
  Opens the import screen.
3. Book
  Books all data records of the settlement.
4. Clear
  Clears all criteria fields.
5. Export
  Exports the result table view.
6. Print
  Prints the result table view.

File Upload

Note: When first uploading a payment provider file you are gonna teach the application how to read that file. The application will remember that on any further upload for that provider.

Note: After the successful upload an entry appears in the table '"Results" in the tab "Import/Search Settlement".

Import a settlement

If you want to import a settlement, it must be in CSV format.
Note: After the successful import an entry appears in the table '"Results" in the tab "Import / Search Settlement".

After clicking on the button "Import" the following window opens:

Upload Steps

After clicking on the 'Import' button, the following screen pops up:

fig 4: Upload Dialogue

Details:

Payment Provider
Select the payment provider.
File Name
Please use the Browse button to select the file you want to upload. The file name will then be displayed in the field File Name.
Delimiter
Select the separator that separates the fields in the CSV file. Usually, and therefore by default, this is the semicolon (' ; ').
Date Format
Choose the date format which is used in the uploaded file.
Note: If an unknown date format is used in the file it cannot be imported. In that case, please change the date format in the file to one that is known to Midoco.
Decimal Delimiter
Select the character which is used to display decimals in the file. Usually, and therefore by default, this is the comma (' , ').
Text in quotes?
If any text in the file is in quotation marks, please select this check box.

First you choose the Payment-Provider.

After clicking "Browse" a new window opens:

fig 5: Open the file from your local drive

Here you select the settlement file.

Once a file has been selected, all the other fields in the dialogue are enabled and the content of the file is displayed in the table:

fig 6: Upload dialogue after file selection.

The example file contains:
Note: To be able to create the following screen, the .csv file was opened in an editor.

information delimiter = 'semicolon' (yellow frame)
date format = 'dd.MM.yyyy' (green)
decimal delimiter = 'comma' (red)
Text in quotes? → no (blue)

Now (once per system / payment provider in Midoco format) the columns require mapping to the corresponding Midoco fields/information.

Right-click on the column numbering. The context menu opens, where you are presented with Midoco fields/information to use for the mapping:

fig 7: Midoco Mapping Fields - Settlement Information

fig 8: Midoco Mapping Fields - Booking References

The entries labelled with are mandatory fields and require mapping. Unless all of the mandatory fields are mapped to a column in the uploaded file, the 'OK' button is deactivated (cannot be clicked).

Details:

Settlement Information
1. Invoice No.
  Invoice number provided by the payment provider.
2. Invoice Date
  Date of the invoice(s) contained in that file.
3. Total Amount
Booking References
1. Amount
  Amount of the data record / row.
2. Trans. Code
  Transaction code of the data record / row.
3. Trans. Date
  Transaction date of the data record / row.
4. Comment
  Comment of the data record / row.
5. Cust. Field
  Reference 1 of a data record / row. Usually this is the transaction ID issued by the provider, which now is required for matching the settlement. Hence this is a mandatory field.
6. Cust. Field 2
  Reference 2 of a data record / row.
7. Cust. Field 3
  Reference 3 of a data record / row.
8. Cust. Field 4
  Reference 4 of a data record / row.
9. Cust. Field 5
  Reference 5 of a data record / row.
10. Fee NoVAT
  Transaction fee which comes without applicable VAT for a data record / row.
11. Fee VAT
  Transaction fee which comes incl. applicable VAT for a data record / row.
12. Ignore Fees
  Flag to indicate that a transaction fee for this data record / row shall be ignored.
Delete
The mapping to the selected column is removed. You can start with the mapping from scratch.
Note: The column is not deleted, but the mapping of a Midoco field to that file information is reset.

After the mapping has been completed, the example looks like that:

fig 9: Upload dialogue after information mapping.

Note: If the file contains a headline row, this row should be deleted before importing the file. Anyhow, you are also able to remove it after the import, but that will require you to do that over and over again on each file import for that provider.
After upload you may delete the header row by selecting that line it with your left mouse button. .
Right-click to open a context menu with the entry "Delete line". Confirming this entry deletes the line.

Please make sure that you only remove lines that are irrelevant for accounting / processing. Otherwise, the original file may not match.

Add missing data

In case the upload file does not contain the following general settlement information:

Invoice No.
Invoice Date
Total Amount

you will be presented with the following screen to provide that information for this file:

fig 10: Manually set settlement information for the uploaded settlement.

These information are vital for the further process, so they are mandatory. Once all information is provided, the OK button is enabled.

Display and check a payment provider settlement

After the import, the settlement is available in the table of the 'Import/Search Settlement' tab.

fig 11: Display of uploaded files (depending on search criteria).

The following actions are available:

Show
Opens the chosen settlement in the tab "Bookings", the data records will be displayed.
Check
A click on the icon will check the complete settlement, for each reference number Midoco looks for a corresponding open entry.
The following popup appears:

fig 12: Message on check action.

Notes:
- Depending on the amount of data in the selected settlement, the check may take several minutes.
- It is possible to check each record individually; this is useful for individual changes within the detail view of the settlement. We recommend to have each settlement checked initially in total using the check functionality in this overview.

Delete
Deletes the chosen settlement.
The following popup appears:

fig 13: Message on delete attempt.

Notes:
- You can only delete a settlement if nothing has been booked yet.
- The deletion cannot be undone. In case the deletion happened by mistake, the settlement needs to be imported again. (see File Upload)

Tab "Bookings"

Double click on an uploaded settlement in the overview tab (Import/Search Settlement) to open the details (Bookings) view of that settlement.

In this tab, the items of the imported settlement are displayed.

It is divided into four parts:

fig 14: Bookings detailed view

Header Fields
1. Payment Provider
  Name of the payment provider.
2. Invoice No.
  External invoice number as imported / typed in.
  Note: The content of this field can be changed. Changes apply once you hit 'Save'.
3. Total Amount
  Total amount of the settlement.
4. Invoice Date
  Date of the settlement.
  Note: You can choose today as a date, but not a date in the future.
5. Creation Date
  The import date of the settlement.
6. Save (button)
  Saves changes for the invoice header.
Criteria Fields
Allow you to narrow down / filter the items shown in the data table of that screen.
1. Checked
2. Difference
3. Booked
Data Table
1. Pos.
  Position number of this row.
2. Unit
  Shows in which organisation unit the corresponding booking was found.
3. Trans. Code
  Transaction code of this row.
4. Trans. Date
  Transaction date of this row.
5. Exp. Amount (expected amount)
  Amount that the program expects for settlement; the column "Difference" relates to this (calculated) information.
6. Amount
  Amount of the file for this transaction.
7. Difference
  Difference of 'Exp. Amount' vs. 'Amount' (so calculated value and settlement value for this transaction are compared to form this value).
8. Fee NoVAT
  The VAT free total amount of fees for this transaction given in the file.
9. Fee VAT
  The total amount of fees incl. VAT for this transaction given in the file.
10. Ignore Fees
11. Cust. Field
  Provider reference 1.
12. Cust. Field 2
  Provider reference 21.
13. Cust. Field 3
  Provider reference 3.
14. Ignored
15. Manually Checked
16. Status
  Status for the row / data record. The following status are possible (meaning in brackets):
  1. (unchecked item)
  2. (booked successfully)
  3. (checked successfully - booking is yet required)
  4. (checked, no action required, no booking required → very uncommon, usually those are transactions listed in the statement but transaction value = 0.00)
  5. (checked, transaction found, amount to high compared to expected value)
  6. (checked, transaction found, amount to low compared to expected value)
  7. (checked, user checked this transaction as manually checked)
  8. (checked, no matching item found in Midoco)
  9. (error → this status is assigned to a transaction when an error occurs while booking the settlement → a specific message is shown to indicate the error reason)
    Tip: Hovering on the status symbol in the application will show you its meaning as a tool tip.
17. Order
  A click opens the corresponding order.
18. Check
  With a click the selected row (only) will be checked (that means, that Midoco searches a corresponding payment / order)
  Icon changes from to.
19. Book
  With a click the selected row will be booked - journal entry is written.
  Icon changes from to.
20. Comment
Buttons
1. Refresh
2. Export
  Exports the displayed table in Excel.

Popup "Edit Details"

Double-clicking on a data record / a row will open the pop up:

fig 15: Edit/view details for a specific transaction

Transaction fees
1. Account Fee NoVAT
  Account for the fee without VAT
2. Account Fee VAT
  Account for the fee with VAT
3. Cost Center Fee No VAT
  Cost centre for the fee without VAT.
4. Cost Center Fee VAT
  Cost centre for the fee with VAT.
5. Fee No VAT
  Amount of the fee without VAT.
6. Fee VAT
  Amount of the fee with VAT.
7. Ignore Fees
  The fees for this data record will be ignored (not booked).
Group 'Differences'
1. Sum Settlements
  The total sum of all settlements with this transaction code / open entry number.
2. Midoco Difference
  Difference of all imported settlements (for this transaction code / open entry number) and the expected amount in Midoco.
Table 'Bookings Overview'
This table lists all imported settlements referring to this transaction code / open entry number.
1. Invoice Date
2. Invoice No.
  External invoice number.
3. Trans. Code
  Transaction code / open entry number.
4. Amount
  Settled amount.
5. Fee No VAT
  Transaction fee without VAT.
6. Fee VAT
  Transaction fee with VAT.
7. Show
  Clicking the icon guides you to the settlement, which contains this line.
8. Book
  Icon gray = this line is not booked, but can be booked by clicking it (a popup appears).
  Icon green = this line is booked already.
Remaining Fields
1. Comment
2. Cust. Field 4
3. Cust. Field 5
4. Trans. Code
  Transaction code / open entry number.
5. Amount
  Amount of the imported settlement for this data record.
6. Ignored
  The data record will be ignored (not booked).
7. Manually Checked
  The data record was checked manually (as a signal to other users, someone checked that item already).

Evaluation / Searching differences

Tab 'Bookings Overview'

In the tab 'Bookings Overview' you have the possibility to search all booked and not booked entries.

Depending on your settings is it possible to do this across settlements.

fig 15: Bookings Overview

Criteria Fields
1. Booking date
  Restrict booking date.
2. Transaction date
  Restrict transaction date.
3. Expected Settl. Amount
  The amount, which Midoco calculated/expects due to previous communication with the payment provider.
4. Settl. Amount
  The amount, which was settled in the imported file(s).
5. Entry Account From / To
  Open entry account.
6. With Settlement
  1. Both
  2. Yes
  3. No
    there is no settlement
7. With Settlement Differences
  1. Both
  2. Yes
    There is a settlement already, with differences.
  3. No
    There is a settlement already, without differences.
8. Entry ID
  Open entry number.
9. External Invoice No.
Data Table
1. Payment Provider
2. Account No.
  Allocation account for this payment provider.
3. Entry ID
  Open entry number of the booking.
4. Creation Date
5. Due Date
  Due date of the open entry.
6. Paid Amount
  Expected amount.
7. Settl-Amount
  Amount of the settlement.
8. Difference
  Difference between expected and settled amount.
9. Booked
  Shows, if the row was already booked.
10. Invoice No.
  External invoice number.
11. Cust. Field
12. Show
  Icon present when that transaction has been booked already → redirects you to the corresponding settlement.
Buttons
1. Search
  Triggers the search according to your criteria.
2. Export
  Exports the results of the search.
3. Print
  Prints the results of the search.

Error handling

Content by label

There is no content with the specified labels

Payment Provider Settlement - Customer Payment

Setup Steps

General Note

Midoco Mode

Application range

Import a payment provider file

Tab Import / Search Settlement

File Upload

Import a settlement

Upload Steps

Add missing data

Display and check a payment provider settlement

Tab "Bookings"

Popup "Edit Details"

Evaluation / Searching differences

Tab 'Bookings Overview'

Error handling

Related articles