Settlements - Payment Reconciliation – Tinx E-Commerce Connector

Introduction

To download settlements from Amazon a private app needs to be created by the merchant.

What is settlement?

The purpose of the settlement report is to adjust the Amazon Fees that Amazon deducted per order under different heads, so we can better calculate the COGS in Business Central.

Find more information on the Amazon settlements here:

https://developer-docs.amazon.com/sp-api/docs/report-type-values-settlement

Settlement reports cannot be requested or scheduled. They are automatically scheduled by Amazon.

There are 2 types of Settlement Reports, one is tab-separate (Flat File) and the other one is in XML (XML). The Tinx app is using the Flat File version: GET_V2_SETTLEMENT_REPORT_DATA_FLAT_FILE_V2

Transactions for an order

This is an example of an order where the Item Price is $5 and shipping is $5. We created the order in Business Central with the order amount equal to $10.
But on this order Amazon deducted $1.82, so the order amount should have to be $8.18 instead of $10.

Below you see the details of an order:

We cannot create or schedule this report, Amazon periodically generates this report. We can download it once it has been generated.

We can fetch the Settlement Report through API, the XML one will have the data like this

Order details can be fetched and the charges under ItemFee section. Then we need to adjust these changes against the respective customer requirements.

We can ask clients requirement on the business central side like which document they want to create to adjust these balances but Ideally, we will be creating a General Journal record, That particular expense account will be debited against the customer account.

Flows in Business Central

1. Download the settlement data by request a report

First step is to download the settlement data (generated by Amazon), you can find this process 'Get settlement data' in this dashboard.

To request a report, specify the report type (is set with the correct value) and marketplaces (change this parameter) that you are requesting, and any optional parameters.

This step will populate the table 11205938 Tinx Amazon Report with new records.

2. Retrieve report - Schedule report 11205839 in Job Queue

More information on retrieving reports is here: https://developer-docs.amazon.com/sp-api/docs/reports-api-v2021-06-30-retrieve-a-report

Add report 11205839 "TINX AMZ Reports Processing" in the Job Queue, this will complete the AMZ report with data. It will set the status from DONE to COMPLETE and will fill the URL.

3. Process data from report - Schedule report 11205847 in Job Queue

Add report 11205847 "TINX AMZ Settlement" in the Job Queue. This report will process data from step 2.

Filters are set on these fields:

SetRange("Is Processed", false);

SetRange("Report Status", "Report Status"::COMPLETE);

SetRange("Report Type", 'GET_V2_SETTLEMENT_REPORT_DATA_FLAT_FILE_V2');