Reconcile your Stripe payouts

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 Stripe. The app reports any unapplied transactions so you can raise a dispute with Stripe. 

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

• Automatically download the Stripe payouts.
• Process multiple payout 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:

1. Stripe payout to NetSuite payout custom record
2. Stripe payout balance transactions to NetSuite deposit

Step 1: Create payout custom records in NetSuite

The Stripe payout to NetSuite payout custom record flow extracts the Stripe payouts with “Paid” status. The flow then creates the “Celigo Payout” custom record for a Stripe payout in NetSuite. This is a scheduled flow.

All the deposits for the payout will be linked to the custom record. For more information related to a custom record, see Understand Stripe payout-related records.

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 payouts. From the next flow run, the flow syncs the data in the “delta flow” format.

Step 2: Create deposits in NetSuite

The Stripe payout balance transactions to NetSuite deposit flow sync the balance transactions of a payout and creates the NetSuite deposit records. The net amount of Payout will always match the NetSuite deposited amount. This is an auto-triggered flow.  

The flow creates bank deposits in NetSuite by processing transactions as below

For each Stripe payout transaction whose,

• If the reporting category is “charge,” the flow looks for a cash sale or customer deposit that matches with Stripe transaction/source ID. 
• If the reporting category is “refund,” the flow looks for a cash refund or customer refund that matches with Stripe transaction/source ID
• 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 Stripe fee (payment processing fee and refund processing fee) is also tracked as part of this setting.

While processing the transactions, If duplicate transaction records are available with matching criteria, the first available match is considered.

Note: The integration app does not sync manual Stripe payouts.

NetSuite limitation: A deposit is created in NetSuite with a maximum of 2000 Stripe transactions. If the number of transactions in the Stripe payout record is more than 2000, multiple deposits are created in NetSuite. Example: If a Stripe payout record 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 with the Stripe 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 Stripe payout variance transactions.

Understand the flow schedule

The scheduled flow fetches the Payouts from Stripe and creates Payout Custom records and Deposits in NetSuite as follows:

Understand a Stripe payout record

A Stripe payout record provides a detailed breakdown of account activity for a given settlement period. The settlement period varies with different countries. 

• Whenever a financial transaction happens directly in Stripe or any third-party application that is integrated with the Stripe payment gateway, all the transactions, either payment or refund, are reflected in Payments.
• The consolidated payouts amount is shown in Balance. 
• You can find the complete list of the financial transaction activity in All transactions.
• In Payouts, you can find the complete list of Stripe payouts.
  • In each Payout > Overview section, you can find Amount, Fee, and, ID.
  • In the Payout > Summary section, you can find a summary of the different transaction types such as charges, adjustments, refunds, Stripe fees, and network costs.
  • In the Payout > Transactions section, you can find the list of transactions that are contributing to the particular Stripe payout record.

For more information, refer to Stripe Payments.

Install Stripe - NetSuite App

To install the "Payout to Reconciliation Automation for NetSuite" integration app and set up a connection with your Stripe account, see Install the "Payout to Reconciliation Automation for NetSuite" integration app.

Before you set up the flow

Prerequisite: If you integrate the Stripe payment gateway with the Recharge plugin, to sync the Shopify order transaction data, you need to contact the Recharge team to configure the required permissions.

The Etail Transaction Id(s) field in NetSuite consists of the Stripe transaction IDs. (This can be modified based on the custom mapping given for the field “transactionRecordIdentifiers”)

Understand the fundamental mappings

For the Stripe payout balance transactions to NetSuite deposit flow in the Post deposits to NetSuite import, the following mapping is added already:

The value of the “Export field (Stripe)” is a particular custom field in NetSuite that is used to identify the matching undeposited transactions. You can modify the value of this mapping as per your business needs. When you run the Stripe payout balance transactions to NetSuite deposit flow to sync transactions, the flow gets the matching transactions by comparing Stripe transaction ID with the value of this particular NetSuite custom field (“custrecord_celigo_etail_trans_id”). 

Note: The “custrecord_celigo_etail_trans_id” mapping is packaged in the eBay, Magento2, and Shopify Integration App bundles.

In the “Post deposits to NetSuite” import, the following mappings are added for the Cash Back sub-tab. You can find the account, amount, and memo details on the NetSuite deposit record.

Note: If you're using the Shopify - NetSuite integration app and integrated with the Stripe payment gateway, configure the below handlebar expression in the below flow mappings:

• Flows > Order: Shopify order to NetSuite order (cash sale or sales order), Shopify order to NetSuite cash sale (add), or Shopify order to NetSuite sales order (add) flow.
• Flows > Billing: Shopify transaction to NetSuite customer deposit (add) flow, only if you're using the customer deposit record for applying the deposit.
• Flows > Refund: Shopify refund to NetSuite refund (add) flow (in both the imports: Post order refunds (invoice or cash sale) to NetSuite and Post customer refunds to NetSuite)

Configure Stripe settings and run the flow

1. In your integrator.io, click Payout to Reconciliation.
   
   Note: Make sure to select the appropriate Stripe account in the Payout to Reconciliation drop-down list at the top-right.
   
2. Go to Settings.
   • In the NetSuite bank account setting, choose a NetSuite bank account to deposit funds received from Stripe.
   • 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 Stripe 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 fees and application fee refund setting, select a NetSuite expense GL account to track processing fees and application fee refund.
   • In the Map Stripe non-sales transaction types against NetSuite GL accounts setting, enter the account IDs and reporting categories to map your Stripe non-sales reporting categories to corresponding NetSuite GL accounts. For more information, see Stripe reporting categories.
     • While processing, if any reporting category matches with the category 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 reporting category.
     • You can find the respective category names in the memo.
   • In the Default NetSuite GL account setting, choose a NetSuite GL account to post the balance transactions whose reporting categories are not mapped.
     Information: Any non-transactional reporting category that is not mapped in the Map Stripe non-sales transaction types against NetSuite GL accounts setting are posted to the Default NetSuite GL account setting. Transactions are grouped by reporting category and a separate line is added per reporting category with a memo representing the category name. 

   • Enter the number of transactions to be processed per page in the Set page size for transactions setting. A single record will be created in NetSuite for the given number of transactions on a page. The maximum number of transactions that can be processed on a single page is 2000. If you do not enter any value in this setting, the default page size will be set to 2000.

     

     Example: If you set the page size as 20 and there are 2000 transaction records, there will be a total of 100 pages with 20 transaction records appearing per page. In NetSuite, 100 deposit records will be created with 20 transaction records in each deposit record.

   • 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 between 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 the 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.

   • In the Transaction amount multiplier setting, enter a value of 0.01 or 1.
     For decimal currencies like USD, enter a value of 0.01. To get the settlement value, the transaction amount in the currency’s smallest unit is multiplied with the entered value.
     For zero-decimal currencies like JPY, enter a value of 1 as zero-decimal currencies have no decimal values. The transaction amount is multiplied by 1 to get the settlement value.
     The default value of 0.01 will be considered if you do not enter the multiplier value.
     
     
     Example:
     Decimal currencies: To get the value of USD 11.50, 1150 cents is multiplied with 0.01.
     Zero-decimal currencies: To get the value of ¥500, the amount value of 500 is multiplied with 1.
3. Click Save.
4. Go to Flows.
5. Enable and run the Stripe Payout to NetSuite Payout Custom Record flow. The Stripe Payout Transactions to NetSuite Deposit is an auto-triggered flow.

Find your payout records in NetSuiteAfter you run the  Stripe Payout to NetSuite Payout Custom Record and Stripe Payout Transactions to NetSuite Deposit flows,

• For each Stripe payout, the Stripe payout to NetSuite payout custom record flow gets the Stripe payout with “Paid” 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 reporting categories and the corresponding bank accounts you select in the settings. Before a NetSuite deposit is created, each underlying transaction (charges, refunds, and disputes) go 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.”
• Stripe fees and fee refunds 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.

Limitations: From the NetSuite UI, 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 detailsFor every Stripe 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 recordIn 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.

NetSuite deposit record at the body level, you can find:

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

NetSuite deposit record sub-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 tabIn 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 transactionA variance transaction can occur when there's a mismatch between the order totals in Stripe 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 event code 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 Stripe transaction ID.

For more information about Stripe variance transactions, see Understand Stripe payout variance transactions.