Reconcile Amazon Pay settlement transactions

Gogula R Bhavana

Updated March 18, 2024 07:54

Understand how the flow works
Understand an Amazon Pay settlement
Set up a connection with Amazon Pay
Before you set up the flow
Configure Amazon Pay settings and run the flow
Understand the payout records in NetSuite

The "Payout to Reconciliation Automation for NetSuite" integration app automates the cash reconciliation process. If you're manually reconciling your transactions by creating deposit records in NetSuite and then moving funds around, the "Payout to Reconciliation Automation for NetSuite" integration app automates the process for you. This also helps you to track and accurately match the balance in the accounting systems with the balance in Amazon Pay. The app reports any unapplied transactions so you can raise a dispute with Amazon Pay

With the "Payout to Reconciliation Automation for NetSuite", you can:

Automatically request for the Amazon Pay settlements.
Process multiple settlement reports in one run.
Apply the undeposited transactions and move them to the bank account.
Report balance transactions that are causing discrepancies and unsettled amounts.
Delay the reconciling process for a certain window with the Lag to bring payout related records setting
Track fees, refund adjustment fees, and disputes against unique GL accounts.

Understand how the flows work

The app comprises two flows:

Request Amazon Pay settlement report list and its transactions
Amazon Pay settlement transactions to NetSuite deposit

Step 1: Request the Amazon Pay settlement report list and its transactions

The Request Amazon Pay settlement report list and its transactions flow get the settlement reports with the status Paid, Successful, and Settled from Amazon Pay. The is a two-step scheduled flow. In the first step, the flow gets the settlement report list from Amazon Pay, and in the second step, it gets the respective transactions from the settlement report.

Note: (Optional) Before you run the flow, select a custom date which is the start date and the current date will be the end date to get the historical settlements. From the next flow run, the flow syncs the data in the “delta flow” format.

Step 2: Create deposits and variance transactions in NetSuite

The Amazon Pay settlement transactions to NetSuite deposit flow syncs the settlement transactions and creates the NetSuite deposit records. The flow creates the “Celigo Payout” custom record per settlement in NetSuite. This is a sequenced flow to the above flow. The flow then creates bank deposits in NetSuite based on the mapped transaction types and the corresponding bank accounts you select in the settings. The flow aggregates gateway processing fees and records against an expense account. The net amount of Amazon Pay settlement should match the NetSuite deposited amount.

For each Amazon Pay settlement transaction,

If the transaction type is “capture,” the flow looks for a cash sale, payment, or customer deposit that matches with Amazon transaction ID.
If the transaction type is “refund,” the flow looks for a cash refund or customer refund that matches with Amazon transaction ID or seller order ID mentioned in the flow mappings.
While processing the transactions, If duplicate transaction records are available with matching criteria, the first available match is considered.
In the above scenarios,
- You can select an account to post adjustments and fees in the NetSuite GL account to track fees and application fee refund setting. The Amazon Pay fee (payment processing fee and refund processing fee) is also tracked as part of this setting. For the transaction type “capture” and “refund,” the fee amount is tracked as separate adjustment lines on the NetSuite deposit record. These adjustment lines are tracked against the same GL account. If you add a handlebar mapping in the flow’s import Post-transaction(s) to NetSuite as Make deposits, you can track these adjustment lines separately.

Important:

The integration app does not sync manual Amazon Pay settlement reports.
The Amazon Pay settlement transactions to NetSuite deposit flow ignores sync transactions with type Transfer, Authorization, Carry Over, and Reserve.
Whenever a settlement report is ignored by the flow, the success count is shown as 1 on the Integration App Dashboard.

NetSuite limitation: A deposit is created in NetSuite with a maximum of 2000 Amazon Pay transactions. If the number of transactions in the Amazon Pay settlement record is more than 2000, multiple deposits are created in NetSuite. Example: If an Amazon Pay settlement has 10500 transactions, six deposits are created in NetSuite, five deposits with 2000 transactions, and a sixth deposit with 500 transactions.

A custom record “Celigo Payout Variance Transaction” is created in NetSuite for any variance amounts reported for each transaction and is linked to the NetSuite deposit. You can select an account to track your variances separately in the NetSuite GL account to track variance setting. A variance transaction is created in the following scenarios:

If the NetSuite transaction amount value does not match the Amazon Pay transaction value, a variance transaction with the type “Amount Mismatch” is created in NetSuite.
If no transaction is found, a variance transaction with the type “Missing Transaction” is created in NetSuite.

For more information, see Understand the Amazon Pay payout variance transactions.

Understand the flow schedule

The scheduled flow fetches the settlements from Amazon Pay and creates Payout Custom records and Deposits in NetSuite as follows:

Understand an Amazon Pay settlement

An Amazon Pay settlement record provides a detailed breakdown of account activity for a given settlement period. The settlement is generated at the end of each day. The consolidated amount for the particular settlement is shown below the Closing Balance. Any settlement report generated in Amazon Pay is available for 90 days.

For more information, refer to Amazon Pay settlement reports and Amazon Pay Payments.

Set up a connection with Amazon Pay

For more information on how to understand and install the payment gateway, see:

Before you set up the flow

You can add a mapping in the "Post payout(s) to NetSuite as custom record" import to enter a prefix or suffix that is displayed in the memo. The default value is AmazonPay_$SettlementID.

For the transaction type “capture” and “refund,” the fee amount is tracked as separate adjustment lines on the NetSuite deposit record. These adjustment lines are tracked against the same GL account. If you add the below handlebar mapping in the flow’s import Post-transaction(s) to NetSuite as Make deposits, you can track these adjustment lines separately.

Export field (Amazon Pay)	Import field(NetSuite)
{{#compare cashbacks[].memo '===' "refund fees"}} #ns account id here# {{else}} {{cashbacks[].account}} {{/compare}}	Cashback : Account(internal id)

Configure Amazon Pay settings and run the flow

In your integrator.io account, click Payout to Reconciliation.

Note: Make sure to select the appropriate Amazon Pay account in the Payout to Reconciliation drop-down list at the top-right.
Go to Settings.
- In the NetSuite bank account setting, choose a NetSuite bank account to deposit funds received from Amazon Pay.
- In the NetSuite GL account to track variance setting, choose a NetSuite GL account to track your variances. A variance is reported if there is an amount mismatch or a matching balance transaction is not found in NetSuite.
  - If the NetSuite transaction amount value does not match with the Amazon Pay transaction value, a variance transaction with the type “Amount Mismatch” is created in NetSuite.
  - If no transaction is found, a variance transaction with the type “Missing Transaction” is created.
- In the NetSuite GL account to track transaction and refund fees setting, choose a NetSuite expense GL account to track transaction fees (TransactionFixedFee and TransactionPercentageFee) and refund fees. On the NetSuite deposit record, you can find the positive fee amounts in the “other deposits” tab and the negative fee amounts in the “cashback” tab. By default, the transaction fees and refund fees are tracked against the same GL account in NetSuite. You can add an additional mapping in the “Amazon Pay settlement transactions to NetSuite deposit” flow to track refund fees to a separate account.
- In the Map Amazon Pay transaction types against NetSuite GL accounts setting, enter the transaction types to map your Amazon Pay non-sales transaction types to corresponding NetSuite GL accounts. For more information, see Amazon Pay transactions.
  - While processing, if any transaction matches the transaction type that you specify in this setting, that particular transaction amount is automatically posted to the NetSuite account you provide in this setting.
  - A separate line is added for a unique unmapped non-transactional type.
  - You can find the respective transaction type name in the memo.
- In the Default NetSuite GL account setting, choose a NetSuite GL account to deposit the Amazon Pay settlement transactions whose transaction types are not mapped.
  Information: Any non-transaction that is not mapped in the Map Amazon Pay transaction types against NetSuite GL accounts setting is posted to the Default NetSuite GL account setting. Transactions are grouped by transaction types and a separate line is added per transaction type with a memo representing the transaction type name.
- In the Duration to reconcile transactions setting, enter the number of days prior to the current date to reconcile transactions in NetSuite.
  Example: If the current date is January 30th and you want to reconcile transactions since January 1st, enter 30 in the setting.
  If you leave it empty, all available transactions are retrieved for reconciliation.
  
  Limitation: This setting only accepts natural numbers. If you enter values such as “3.5”, “-10”, or “ten”, you will receive an invalid error.
- In the Lag to reconcile payouts setting, enter the value in days. The number of days you enter and payouts for those many days are ignored from the previous flow run date and the present date. This setting helps you to reduce the missing transactions if there is a lag b/w the time of payment capture and the creation of payment record in NetSuite(for instance, if you are billing an order to cash sale in NetSuite only after fulfillment)
  
  Example: If you provide lag as 4, and your previous flow run date is Nov 5th and present date is Nov 10th, the flow considers the payouts from Nov 1st to Nov 6th.
  You can leverage this setting to hours by providing an appropriate input value.
  Example: For one hour, you can enter the value as 0.041666.
Click Save.
Go to Flows.
Enable and run the Request Amazon Pay settlement report list and its transactions flow. The Amazon Pay Payout Transactions to NetSuite Deposit is an auto-triggered flow.

Understand the payout records in NetSuite

After you run the Request Amazon Pay settlement report list and its transactions and Amazon Pay Payout Transactions to NetSuite Deposit flows,

For each Amazon Pay settlement, the “Request Amazon Pay settlement report list and its transactions” flow gets the Amazon Pay settlement report with Paid, Successful, and Settled status. The flow then creates the “Celigo Payout” custom record per payout in NetSuite. The flow then creates bank deposits in NetSuite based on the mapped transaction types and the corresponding bank accounts you select in the settings. Before a NetSuite deposit is created, each underlying transaction (charges, refunds, and disputes) goes through a validation process.
The deposit is 'linked' to every charge or refund contained within that payout. This moves cash from the "Undeposited Funds" account to the bank account specified on the deposit. The status of the transaction is changed from “undeposited” to “deposited.”
Amazon Pay transaction fees and refund fees are recorded as "Cash Back" or "Other Deposit" line items on the NetSuite deposit based on the accumulated amount.
Any dispute (chargeback) fees or dispute reversals are represented as "Cash Back" line items.
On the Deposit record, in the “Cash Back” tab, two adjustment lines are added separately for transaction types “capture” and “refund.”

Limitation: From NetSuite, a maximum of 10000 lines can be linked to a deposit. The edit operation on the deposit record does not work if there are more than 10000+ unsettled transactions.

Celigo source account details

For every Amazon Pay account that you configure in the integration app, a list is added on the “Celigo source account details” page. Each record is added as a separate line with the connection name and store ID. The integration app ID is treated as the store ID for now.

Celigo Payout custom record

In this record, you’ll find:

The summary record for each payout report.
Name
Payout ID
Amount
Payout Date
Currency
Memo
In the Deposits tab, the appropriate deposit record is attached.

Deposit

You can find the following:

Account
Amount details
The “Memo” field shows the payout ID. You can configure the memo field as per your business needs.
Deposit number.

Under tabs

You can find the following sub-tabs:

In the Payments sub-tab, you can find all the corresponding transactions such as cash sales, cash refunds, customer deposits, and customer refunds.
In the Other Deposits and Cash Back sub-tabs, you can find the refund adjustments, variances, and fees. The amounts are grouped by the transaction type and are displayed as individual lines.
- Any amount that has to be added to the Deposit is tracked in the “Other Deposits” tab.
- Any amount that is deducted from the Deposit is tracked in the “Cash Back” tab.

Payout tab

In this tab, you can find the Source Account Name and Source Payout that is listed on the “Celigo source account details” page. You can also find the complete list of variance transactions for the particular deposit record.

Celigo payout variance transaction

A variance transaction can occur when there's a mismatch between the order totals in Amazon Pay and NetSuite. It could also be because the transactions were not found in NetSuite. In NetSuite, all the order and transaction details that cause variance are reported in the “Payout” tab.

In this record, you can find the following:

Type: You can find the transaction type that will help to identify which transaction type there was a variance transaction created.
Variance Type: “Missing Transaction” or “Amount Mismatch”
NetSuite Deposit: You can find the deposit record linked.
Variance Amount
- In the “Original Amounts” tab, you can find the below field values only if there is an “amount mismatch” scenario:
  - Transaction Amount
  - Transaction Net
  - Transaction Fee
- In the “IDs” tab, you can find the “Transaction ID.” This is the original Amazon Pay transaction ID.