Reconcile PayPal payout transactions

Contents

• Understand how the flow works
• Understand PayPal records
  • PayPal payout
  • PayPal payout report
• Before you set up the flow
• Configure PayPal settings and run the flow
• Understand NetSuite records

The "PayPal – NetSuite Payout to Reconciliation" app automates the cash reconciliation process. The app downloads the settlement transactions from PayPal Transaction API to match against outstanding payments and refunds. Instead of manually reconciling your transactions by creating deposit records in NetSuite and then moving funds around, the integration app automates the process for you. It also helps you to track and accurately match the balance in the accounting systems with the balance in the PayPal statement. The app reports any unapplied transactions so you can raise a dispute with PayPal for unprocessed amounts. It also provides you the ability to delay the reconciling process for a certain window using the app settings. 

Note: You can sync the transactions to NetSuite even if your PayPal account is on the PayPal Express Checkout edition. The Payflow edition is not required. 

Understand how the flow works

The “PayPal payout transactions to NetSuite deposits” flow gets the payout transaction records from PayPal Transaction API with the transaction status ”S” (Successfully Completed). The flow then creates bank deposits in NetSuite based on the mapped transaction event codes and the corresponding bank accounts you select in the settings. The flow aggregates gateway processing fees and records against an expense account. This is a scheduled flow. 

Note: If you run the flow for the first time, select a custom date which is the start date and the current date will be the end date. From the next flow run, the flow syncs the data in the “delta flow” format.

For each PayPal transaction whose,

• Transaction code = Express Checkout Payment (OR) eBay Auction Payment (OR) Mass pay payment, the flow looks for an associated cash sale or customer deposit with PayPalTransaction ID = transaction ID (value of the field specified in the “transactionRecordidentifiers” mapping).
• Transaction code = Payment refund, the flow looks for an associated cash refund or customer refund with PayPalTransaction ID = transaction ID (value of the field specified in the “transactionRecordidentifiers” mapping).

A custom record “Celigo Payout Variance Transaction” is created in NetSuite for all the variance transactions 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 PayPal 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 PayPal payout variance transactions.

Understand PayPal records

PayPal payout

Whenever a financial transaction happens on any third-party application and is integrated with the PayPal payment gateway, the transactions are listed in PayPal as below shown in the image. All these transactions, either payment or refund, are reflected in the PayPal Balance.

Note: Any transaction with the type “General Withdrawal” is ignored by the “PayPal payout transactions to NetSuite deposits” flow. 

In PayPal, to find the transactions:

1. Go to Reports > Financial Summary. 
2. Select the date range and click Create Report. 
3. You can either download the report in the respective format or click on the total balance to view the list of transactions.

PayPal payout report 

PayPal generates a payout report on-demand basis depending on the input parameters. There is no unique identifier or fixed schedule for these payout reports. In the PayPal payout report, you can find different types of transaction event codes in the “Type” column. You can find the commonly used transaction event types in this report. There might be more transactions and event codes that are not available in this report and you can find them here.

By default, the following four transactions are treated as sales transactions:

• Express Checkout Payment (T0006)
• eBay Auction Payment (T0004)
• MassPay Payment (T0001)
• Payment Refund (T1107)

The “Transaction ID” in the PayPal payout record must match the value on the invoice or cash sale record.

Other than the sales transactions, there are other transaction types that are considered “non-sales transactions:”

• Reversal of Points Usage
• Hold on Balance for Dispute Invoice
• General Credit Card Deposit
• Website Payment - PayPal service fee

Before you run the flow

• The Etail Transaction Id(s) field in NetSuite consists of the PayPal transaction IDs. (This can be modified based on the custom mapping given for the field “transactionRecordIdentifiers”)
• Connect a PayPal account that has Payout transactions.

Understand the custom mappings

In the “Post deposits to NetSuite” import, the following mapping is added already:

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

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.

Configure PayPal settings and run the flow

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

   Note: Make sure to select the appropriate PayPal account in the Payout to Reconciliation drop-down list at the top-right.

2. Go to Settings.
   • Check the Exclude marketplace tax amount while matching payments checkbox if you want to ignore the marketplace tax amount while matching the NetSuite payments. It is recommended to check this checkbox if you don’t record tax in NetSuite for the marketplace tax-facilitated order.
     • If you check the checkbox, deduct tax from the gross amount before you look for the matching transaction. 
     • If you uncheck the checkbox, you can add a mapping to track the “Tax collected by Partner” into a GL account. The transaction amount is added as part of the non-sales transaction.
       
       Note: This setting is applicable only for eBay merchants.
   • In the Ignore PayPal transaction types setting, enter the transaction event codes in a comma-separated format. The transaction with transaction event codes specified in this setting is ignored and no amount corresponding to these transactions is synced to NetSuite.
     This includes not only the amount considered as a transaction amount but also the amount considered as a fee. For example, if a transaction has a transaction amount of $45.00 and an amount of $5.00 towards fees, the entire amount of $50.00 will be ignored when the records are synced in NetSuite.
     The default transaction event codes specified in this setting are T0400 (General withdrawal from PayPal account)  and T0401 (AutoSweep). According to your unique business requirements, you can enter other transaction event codes.
     
   • In the Match PayPal transaction types with NetSuite sales transactions setting, enter the transaction event codes in a comma-separated format. The codes entered in this setting are considered payments or refunds and mapped with existing NetSuite sales transactions. The transaction types not entered in this setting are considered non-sales transactions. 
     The default transaction event codes specified in this setting are T0001 (MassPay payment), T0004 (eBay auction payment), T0006 (PayPal Checkout APIs), and T1107 (Payment refund, initiated by the merchant) which are used to match the auto-populated sales. According to your business requirements, you can enter other transaction event codes.
     • For each transaction,
       • Transaction code = Express Checkout Payment (OR) eBay Auction Payment (OR) Mass pay payment, the flow looks for an associated cash sale or customer deposit with PayPalTransaction ID = transaction ID (value of the field specified in the “transactionRecordidentifiers” mapping).
       • Transaction code = Payment refund, the flow looks for an associated cash refund or customer refund with PayPalTransaction ID = transaction ID (value of the field specified in the “transactionRecordidentifiers” mapping).
       • In the above scenarios, 
         • You can select an account to track your adjustments and fees in the NetSuite GL account to track transaction fees setting.
         • While processing the transactions, If duplicate transaction records are available, an error is displayed on the integration app dashboard.
   • In the NetSuite bank account setting, select a NetSuite bank account to which the PayPal funds must be deposited.
   • In the NetSuite GL account to track variance setting, choose a NetSuite GL account to track your variances and unreconciled amounts. A variance is reported if there is an amount mismatch or a missing transaction scenario. 
     • If the NetSuite transaction amount value does not match with the PayPal 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 fees setting, choose a NetSuite expense GL account to track transactions.
   • In the Map PayPal non-transactional types against NetSuite GL accounts setting, enter the account IDs (NetSuite internalid of the GL account) and transaction event codes to map your Paypal non-sales transaction event code(s) to corresponding NetSuite GL accounts. 
     • While processing if any transaction event code matches with the event code that you specify in this setting, that particular transaction is automatically mapped to the NetSuite account you provide in this setting.  
   • In the Default NetSuite GL account setting, choose a Netsuite GL account to deposit the transactions whose transaction event codes are not mapped. Any transaction event code that is not mapped in the Map PayPal non-transactional types against NetSuite GL accounts setting, and also not part of the payment transactions, such transactions can be mapped to the Default NetSuite GL account setting.
   • 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.

     
   • There is a lag between the payment made in the third-party application and when it is being reflected in PayPal. In the Lag to bring payout related records 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.
     
     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.
   • In the Start and end time to get PayPal transactions setting, select the start and end time in the UTC timezone (for a given day) to get the payout transactions from PayPal. The time should match the bank transfer time in your PayPal account.
     
     Example: If you enter 10 AM in the setting, the flow picks the deposit transactions only between the previous flow run date +10 AM UTC and the current flow run date +10 AM UTC. This generates a NetSuite deposit with the same amount matching as Paypal general withdrawal or auto-sweep.
     • The Start and end time to get PayPal transactions setting respects any lag specified in the Lag to bring payout related records setting.
       
       Example: If the current flow run date is 01/05/2022 and the previous flow run date is 01/04/2022, and the time selected is +10 AM UTC, and the lag specified in the Lag to bring payout related records setting is 1, the PayPal payout transactions to NetSuite deposits flow picks the transactions from 01/03/2022 +10 AM UTC to 01/04/2022 +10 AM UTC.
       Note: If you've configured different time zones in your integrator.io and PayPal accounts, you might find additional or missing deposit transactions. In this scenario, you should set the same time zones in both your accounts. And before you run the flow, consider the time difference between both time zones. For example, if the time set in integrator.io is 01/03/2022 +10 AM EST and in PayPal it is 01/03/2022 +2 PM UTC, when you run the PayPal payout transactions to NetSuite deposits flow, during the four-hour time difference, you might find additional or missing deposit transactions in NetSuite.
     • If you run the PayPal payout transactions to NetSuite deposits flow multiple times a day, it is recommended not to use the Start and end time to get the PayPal transactions setting. 
     • If the time provided in the setting is a future time compared to when the flow is actually triggered, then the flow will error out as API doesn't support future time for start_date. Ensure to set the correct time. 
     • If the time zone in the PayPal report is ahead of the UTC timezone, it is recommended to run the PayPal payout transactions to NetSuite deposits flow with a previous date for the first time and adjust the lag in the Lag to bring payout related records setting accordingly.
       
       Example: PayPal Report Timezone: IST and auto-sweep time is 3 AM IST daily. While converting to UTC: 21st Oct: 3 AM would be 20th Oct 9:30 PM. Since we're only specifying the time, ensure the date is selected as the previous date while running the flow manually for the first time.
3. Click Save.
4. Go to Flows.
5. Enable and run the “PayPal payout transactions to NetSuite deposit” flow.

Understand NetSuite PayPal records

After you run the “PayPal payout transactions to NetSuite deposits” flow, the following records are created in NetSuite:

• Deposit
• If any variance, Celigo payout variance transaction.

Celigo source account details

For every PayPal connection that you configure in the integration app, a record 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.

Deposit

In the Deposit record, you can find:

• Account
• Amount details
• The “Memo” field shows the timestamp with the start date and end date. This will help you to understand during which time period these transactions are synced. 
• Deposit number

Items tab

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. 

Note: When you run the PayPal payout transactions to NetSuite deposits flow, with the custom mappings from the Other Deposits sublist, ensure to add the Other Deposits: Account and Other Deposits: Amount mappings. For more information, see this community post.

Payout tab

In this tab, you can find the Source Account Name 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 PayPal 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:

• 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:
    
    • Transaction Amount
    • Transaction Net
    • Transaction Fee
  • In the “IDs” tab, you can find the “Transaction ID.” This is the original PayPal transaction ID.

For more information, see Understand PayPal payout variance transactions.