PayPack logo
PayPack logo
All articles

Payout ReconciliationUpdated 14 days ago

Business scenario necessitates seamless Stripe payouts integration with NetSuite to offer comprehensive payment overview, encompassing:

  • Payments: Deposits seamlessly link to existing NetSuite records, enabling exact reconciliation and granular income source tracking.
  • Other Deposits: Handles unassociated transactions efficiently, regardless of existing records.
  • Cashback: This category serves to address negative amounts, primarily for transaction types such as Stripe fees or refunds.

Configuration

To enable Payout Reconciliation, navigate to novaApps → PayPack - Integrated Payment Processing Solution for Stripe → Configuration.

  • Check the Update Configuration checkbox
  • Click on the Payout Reconciliation Subtab
  • Check the Enable Payout Reconciliation checkbox
  • Select NetSuite Bank Deposit Account
  • Select NetSuite Account to Track Stripe Fee
  • Select NetSuite Account to Track Unreconciled Deposits
  • Enter Stripe Charge Field ID (optional for custom field)
  • Enter Stripe Refund Field ID (optional for custom field)
  • Enter Stripe Dispute Field ID (optional for custom field)
  • Enter JSON data for Deposit Field Mapping
  • Select the Filter Payouts by Status (optional)
  • Select the Filter Payouts by Date Parameter

Filter Payouts by Status

In the PayPack configuration → Payout Reconciliation, users will find a dropdown Filter Payouts by Status. This is an optional configuration and will grab all statuses by default when field value is not set.

  • Blank (default) - Returns all payout statuses including failed and canceled.
  • Pending - The payout is waiting to be sent to the bank.
  • Paid - The transaction was successful and the funds have been transferred to the merchant's bank account.
  • Failed - The bank was unable to receive the funds and they were sent back to Stripe.
  • Canceled - The payout was canceled, usually by the seller or Stripe.

Filter Payouts by Date Parameter

For the flexibility and accuracy of payout management, this feature allows businesses to retrieve payouts from Stripe by either the created or arrival date.

In the PayPack configuration → Payout Reconciliation, users will find a dropdown Filter Payouts by Date Parameter. This dropdown allows users to select either "Created Date" or "Arrival Date" for filtering payouts.

Once a selection is made and saved in the configuration, the payout retrieval script will use the chosen parameter to fetch payouts accordingly. If no selection is made, the system will default to using the created date for fetching payouts from Stripe.

  • Created (default) - Only return payouts that were created during the given date interval.
  • Arrival Date - Only return payouts that are expected to arrive during the given date interval.


Sample JSON Data

General Mappings

1{
2  "mapping": {
3    "fields": [
4      {
5        "generate": "memo",
6        "extract": "id"
7      },
8      {
9        "generate": "trandate",
10        "extract": "created"
11      },
12      {
13        "generate": "location",
14        "hardCodedValue": 2,
15        "fieldtype": "",
16        "discardIfEmpty": false
17      }
18    ],
19    "sublist": {
20      "other": [
21        {
22          "generate": "account",
23          "hardCodedValue": "1"
24        },
25        {
26          "generate": "class",
27          "hardCodedValue": "2"
28        },
29        {
30          "generate": "department",
31          "hardCodedValue": "3"
32        },
33        {
34          "generate": "location",
35          "hardCodedValue": 4,
36        }
37      ]
38    }
39  }
40}

Set Deposit Transaction Date

The JSON example below shows how to change the default configuration to map the Stripe Payout's created date to the NetSuite Bank Deposit Date. In the Payout deposit field mapping configuration, you can update the "trandate" from "created" to "arrival_date".

1{
2  "mapping": {
3    "fields": [
4      {
5        "generate": "trandate",
6        "extract": "arrival_date"
7      }
8    ]
9  }
10}

Set Deposit Line Level Field

The JSON example below shows how to set the class and department line level fields using sublist mapping and Stripe transaction type lookups.

In this particular example, the Deposit will only set Stripe Fee line level class and department values under Cash Back tab.

  • Classlookup "stripe_fee": "1" is looking at the NetSuite Class Internal ID of 1. Default is set to blank.
  • Departmentlookup "stripe_fee": "2" is looking at the NetSuite Department Internal ID of 2. Default is set to blank.
1{
2  "lookups": {
3    "accountlookup": {
4      "map": {
5        "stripe_fee": "1",
6        "charge": "15",
7        "refund": "10",
8        "variance": "24"
9      },
10      "default": ""
11    }, 
12   "classlookup": {
13      "map": {
14        "stripe_fee": "1"
15      },
16      "default": ""
17    },
18    "departmentlookup": {
19      "map": {
20        "stripe_fee": "2"
21      },
22      "default": ""
23    },
24    "locationlookup": {
25      "map": {
26        "stripe_fee": "2"
27      },
28      "default": ""
29    }
30  },
31  "mapping": {
32    "fields": [
33      {
34        "generate": "memo",
35        "extract": "id"
36      },
37      {
38        "generate": "trandate",
39        "extract": "created"
40      }
41    ],
42    "sublist": {
43      "other": [
44        {
45          "generate": "account",
46          "extract": "type",
47          "lookupName": "accountlookup",
48          "discardIfEmpty": true
49        },
50        {
51          "generate": "class",
52          "extract": "type",
53          "lookupName": "classlookup"
54        },
55        {
56          "generate": "department",
57          "extract": "type",
58          "lookupName": "departmentlookup"
59        },
60        {
61          "generate": "location",
62          "extract": "type",
63          "lookupName": "locationlookup"
64        }
65      ]
66    }
67  }
68}

Available Stripe transaction types:

  • stripe_fee
  • charge
  • payment
  • refund
  • network_cost
  • adjustment
  • variance

Scripts

Two custom scripts have been developed, addressing distinct workflow needs to improve efficiency and streamline operations.

  1. Scheduled Script
    Script integrates multiple NetSuite records and schedules automated execution at specified intervals, removing manual work.
  1. Regular / OnDemand Script
    Script allows selective execution of missing or specific payouts, eliminating unnecessary processing. Enables concurrent processing of roughly 20 payouts via comma-separated list.

Script Deployment

Navigate to Customization → Scripting → Script Deployments 

  1. Select  “nm PayPack Payout Process Script” for Scheduled Script
  2. Select  “nm PayPack Payout Process ScriptOnDemand” for  Regular/ OnDemand Script

Scheduled Script

Settings/Configuration for the first run:

  1. Click on edit of this script “nm PayPack Payout Process Script”
  2. Navigate to the subtab parameters
  3. Enter the payout start date in epoch format. Use an epoch converter to change the date to epoch format (The day from which one would like to get the payout records from Stripe)
  4. Hover on save button and click on “Save & Execute”

Epoch Converter: https://www.epochconverter.com/

For multiple PayPack configurations, insert the corresponding Stripe Account Id under the Parameters tab for each script deployment.

 

Setting/Configuration for the next runs:

  1. Click on edit of this script “customdeploy_nm_pp_payout_proc_ns_scrp”
  2. Navigate to the subtab “Schedule” and select the type of event
    • Single Event
    • Daily Event
    • Monthly Event
    • Weekly event
    • Yearly Event
  3. Enter the Start Date for the event
  4. Select how often the event should be scheduled
  5. Enter End Date or Check No End Date checkbox
  6. Click on Save

OnDemand Script

This script is deployed to selectively run missing or specific payouts without the necessity to process all payouts simultaneously. It facilitates the simultaneous processing of approximately 20 payouts by specifying them as comma-separated values.

Setting/Configuration to run the script:

  1. Click on edit of this script “nm PayPack Payout Process ScriptOnDemand”
  2. Navigate to “Parameters” subtab
  3. Enter a single Stripe payout Id or multiple Stripe payout Ids
  4. Click on “Save & Execute”

Custom Record

After executing the script in NetSuite, a custom record named “PayPack Stripe Payout Reconciliation” is automatically generated, serving as a detailed summary of the payout record. This custom record captures key financial components including total amount, payout id, payout date, charges, refunds, adjustments, Stripe fees, network costs, reserved funds and transfers. This functionality ensures that our financial management processes are both accurate and efficient, providing a comprehensive view of our financial transactions. This is also linked to the Deposit record created in NetSuite.

To have a look at the Custom Record go to page PayPack Stripe Payout Reconciliation and click view on any payout transaction.

To navigate to the Deposit page from the custom record go to Deposits subtab → Click on the document number. It leads to the deposit page of that payout record.

Deposits

To view the list of deposits go to Transactions → Bank → Make Deposits → List.

The deposit record in NetSuite includes three critical subtabs: Payments, Other Deposits, and Cashback. Here's how these subtabs function within the NetSuite environment.

  • Payments: This subtab links deposits seamlessly with existing records in NetSuite, facilitating precise reconciliation and detailed tracking of income sources.
  • Other Deposits: Designed for transactions that may not yet have corresponding entries in our NetSuite records, this subtab efficiently manages these records, ensuring complete and accurate financial data.
  • Cashback: In the NetSuite ecosystem, the Cashback subtab is dedicated to addressing negative amounts, particularly for items like Stripe fees or refunds. It allows for effective segregation and management of these transactions to maintain precise financial records, even when dealing with reimbursements or fee adjustments.

According to the requirement, navigate to

  • Items → Payments
  • Items → Other Deposits
  • Items → Cashback

To navigate to a Custom Record from the deposit record. Go to novamodule subtab → PayPack → click on STRIPE PAYOUT RECON [PAYPACK] id.

Custom Field Configuration

Payout Reconciliation handles the categorization of payout overviews into Payments, Other Deposits, and Cashback. This categorization relies on default settings, which are:

  • Charge IDs: Populated in the STRIPE CHARGE ID (PAYPACK) field within Customer Payments.
  • Refund IDs: Populated in the STRIPE REFUND ID (PAYPACK) field within Customer Refunds.
  • Dispute IDs: Populated in the STRIPE DISPUTE ID (PAYPACK) field within Customer Refunds, Customer Payments and Customer Deposits.

For visual references of these default locations, please refer to the images below.


If charge IDs, refund IDs and Dispute IDs are stored in fields other than the default ones, Payout Reconciliation can still be supported. Here's how: 

  • Identify Internal Field IDs: Locate the internal IDs of the specific fields where charge IDs are stored within Payment Records, where refund IDs reside within Refund Records, and dispute IDs reside within Payment or Refund Records.

  • Override Default Locations: Enter the identified internal IDs into the fields labeled STRIPE CHARGE FIELD ID, STRIPE REFUND FIELD ID and STRIPE DISPUTE FIELD ID on the Payout Reconciliation tab.

This action instructs Payout Reconciliation to fetch IDs from these designated fields, overriding the default locations.

Understand the behavior of override fields:

  • Optional Text Boxes: These fields are not mandatory. If left empty, Payout Reconciliation will default to the original field locations for charge,  refund and dispute IDs.

  • Independent Customization: You can modify one field while leaving the other untouched. The updated field will override the original location, while the unchanged field will maintain its default behavior.

  • Unified Functionality: If both fields are assigned the same field ID, Payout Reconciliation will encompass all three categories: Payments, Other Deposits, and Cashback within that single field.

Variances

Support for payout variances improve the accuracy, efficiency, and transparency of the Payout system, ultimately supporting better financial management and reporting within the organization. The most common payout variances occur from the use of multiple currencies where NetSuite and Stripe currency conversions are not calculated the same.

NetSuite Deposit Record includes a subtab, PayPack Payout Variance.

  • Transaction Variance: This refers to the differences in expected vs. actual transaction amounts, which can be either positive (+ve) or negative (-ve).
  • Custom Record Creation: Each transaction variance is recorded as a custom record named "PayPack Payout Variance." This helps track and manage discrepancies efficiently.

If the variance is positive it is added under the other deposits tab, indicating increased funds.

If the variance is negative variance is added under the cashback tab, reflecting reductions in funds.

If different account mapping is required for these variances, you can set them up as specified in this documentation.

The type for variance mapping should be designated as “variance” to ensure proper categorization and mapping.

Was this article helpful?
Yes
No
© 2024 · PayPack. All Rights Reserved.