(ASC) Settlement Reports

Overview

Settlement Reports are to settle the Fees/Charges lines of the Settlement Report (Credit/Debit values) that are not listed on transactions, in order to settle those Lines on the Settlement Report from Amazon to the Seller.

Recommended/Supported Process

As of Amazon DirectConnect Version 1.8.9 and later
In this Process, Settlement Report can be settled without any dependencies of Transactions listing Fees, and no Fees are listed on the Transactions.
This reduces additional Processing in many areas of the Amazon Integration, Less user Involvement and mitigates possible Order Line Limits with NetSuite.

When Settlement Reports are Pulled, they create a Settlement Report Record.
Under Setup Tab > In8 Amazon Seller Central > Settlement Reports

The Settlement Report Status is set as “Pending” Status as the Fees/Charges lines (Credit/Debit) are added to the Report. Once all Settlement Fees/Charges Lines are added the Settlement Report Moves to “For Posting/Processing” Status. From the “For Posting/Processing” Status the Settlement Report can be Processed to create the Journal entries for the Total Adjustment and the Proceeds Bank Transfer.

Fee/Charge Mapping Errors

If the GL Accounts are Mot Mapped to the “Marketplace Fees/Charge Mappings” and results in the Settlement Error saying:

Please use the “Process Fee/Name to Marketplace” Button on the Settlement Report to have it list the Fee Type and Fee Name to the “Marketplace Fees/Charge Mappings” for GL updating.

All the Fees/Charges lines Amount Type and Name are Required to be Mapped in the Correct Marketplace for them to be mapped to the Correct Revenue/Expense Item and GL account for the Settlement to Complete on the Journal Entries.

Clicking on the “Process Fee/Name to Marketplace” Button will load all missing Fee/Charge Type and Name to the Corresponding Marketplace. Which should load a Banner confirming those are being processes and a Link to the Direct Fees/Charges

• 

Mapping List to allow easily listing the Item and GL accounts needed. When Clicking the “Here” Link, please wait and Refresh the Fees/Charges Mapping Page to allow the script to finish loading the Missing ones.

• 

Or Head to the “Marketplace Fees/Charge Mappings” Menu and Refresh the page until you see the new lines apear

• 

The New Settlement Reports created after version 1.8.9:

These ones will come in correctly and be able to automated to be Settled.

Notes to Processes and Fields Explained:

Preferred View for the Transactions Fee/Charge Lines “In8 Amazon Settlement Report Fees”

Processes:

When Existing Reports are Re-Processes, per above, or New Settlement Reports are created, Fee lines are Automatically “Completed Status” and either “No Match” or “Match”

• “Completed” & “Match” Status lines are Transaction Type “Refund & Order” + Amount Type “ItemPrice” + Fee Name “Principal”

  • From the Transaction(Fee/Charge) lines Column “FEE TRANTYPE|AMOUNTNAME|FEENAME” will be seen in Concatenated format as “Order|ItemPrice|Principal” & “Refund|ItemPrice|Principal”

  • “Order|ItemPrice|Principal” & “Refund|ItemPrice|Principal” Lines are Items that would be listed on the Sales and Returns Transactions already in NetSuite and are excluded from Settlement By Default. As their Revenue is already accounted for on the NetSuite Transactions and not seen in the Settlement Report to be settled.

• “Completed” & “No Match” Status lines are the lines not on transactions of the Settlement Report that ARE to be Settled for the Report.

Fields Definitions Explained:

Due lack of Clarity from Amazon about which specific Fee Lines would be included with the Expenses Calculation & lack of Clarify about how the Categorize the lines to Expenses, there are small Variances making the Total Fee/Expenses field Lower in Some instances. This Does not Effect the Lines being Settled Though and is there for Reference only to Amazon Summary.

Most Fields are Self Explanatory, but the Main Settlement Reports' “Totals” section fields explained here:

• Currency:

  • The Currency the Settlement Report is Paid out in and that is settled in.

• Deposit Amount:

  • The Total Net Proceeds from the Settlement Report to Be Paid to the Seller on the Bank Transfer Journal Entry | “Net Proceeds”

• Previous Reserve Amount:

  • Amount Carried over to the Settlement Report by Amazon | “Beginning Balance”

• Total Refunds:

  • Calculation of total refund amount of Settlement report | “Refunds”

• Total Fees/Expenses:

  • Attempted Calculation of the Total Expenses | “Expenses”

• Current Reserve Amount:

  • Amount still held by Amazon to be carried over | “Account Level Reserve”

• Total Credit Adjustment:

  • Total of Positive Lines to be settled (Credit) to the GL Accounts

• Total Debit Adjustment:

  • Total of Negative Lines to be settled (Debit) to the GL Accounts

Settlement Searches

Found under Setup Tab > Amazon Seller Central > Settlement Report Searches

The searches listed are Summary searches Grouped by Fees “Transaction Type”+”Amount Type”+”Fee Name” and Fee Totals Summed for Reference.

Additionally has two Filters:

• Settlement Report Name

• Settlement Report ID

Re-Processing Existing Settlement Reports created before Version 1.8.9:

As of Change to SP-API since September 2022, re-processing old Settlement Reports are no longer re-processable with new API

All the Fees/Charges lines Amount Type and Name are Required to be Mapped in the Correct Marketplace for them to be mapped to the Correct Revenue/Expense Item and GL account for the Settlement to Complete on the Journal Entries.

Clicking on the “Process Fee/Name to Marketplace” Button will load all missing Fee/Charge Type and Name to the Corresponding Marketplace. Which should load a Banner confirming those are being processes and a Link to the Direct Fees/Charges

• 

Mapping List to allow easily listing the Item and GL accounts needed. When Clicking the “Here” Link, please wait and Refresh the Fees/Charges Mapping Page to allow the script to finish loading the Missing ones.

• 

Once the Existing Settlement Reports created Prior to Version 1.8.9 that are needed Re-Pulled are Re-Processes, Fees cleared from Transactions and Fees/Charge Mappings updated they can be Settled; either By Leaving it in the Status “For Posting/Processing” and the Schedule Process will Perform the Settlement and Bank Transfer Journal Entries OR by Manually Clicking the Buttons:

Unsupported/Not Recommended Process

This Process below is Deprecated and unsupported from In8Sync, including any updates to the functions of the Settlement Report dependencies with Transactions listing Fees.
Requires In8Sync Support to Enable Processing Fees Transactions that also enables other Matching Functions to take place and increased Client User involvement, Increases chances of hitting NetSuite Order Line Limits and increased overall Amazon Integration processing times.

This process has Dependencies for the Transaction that are Part of the Settlement Report to be completed and Fees listed on the Transactions.
Requires Matching Script processing to check for Fees listed on the Transactions that are Part of the Settlement Report before Settling the Report.

When Settlement Report Matching is ran, it will check NetSuite for the Orders and Fees already listed to determine the No Match on the Settlement Report that still need to be handled that are not accounted for through Cash Sales from the Orders Processed & Credit Memos from the Returns Processed.
No Matches confirmed are then Listed on a Journal Entry for their Settlement in NetSuite.

When Settlement Reports are Pulled, they create a Settlement Report Record.
Under Setup Tab > In8 Amazon Seller Central > Settlement Reports

The Settlement Report Status is set as “Pending” Status for the Initial Matching of the Settlement Report Transaction Fee lines to be Matched or Not Matched with Marketplace Setup and Cash Sales to Determine any other fees needing settled in the Account.
Status and Matching Statuses:

When the Initial Matching Script scheduled starts to process the Settlement Report Record, the status of the Settlement Report will move to “Processing Match”. Once the Initial Matching is completed, the Settlement Report will move to Status “Match Verification Needed”, if there are any No Match Fee Transaction Lines.

REQUIRING:

• NetSuite Seller to review the “Not Matched” to verify anything needing done or adjusted before the Settlement Report is settled and placed in Status “For Processing/Posting” that would then allow the other Processing Script to create the Journal Entries for Bank Transfer and Fees Settlement. (see section “Possible Matching Corrections” to get other fees mapped in the Marketplace settings for Future Settlements to Match and have fees on Transactions. Current Settlement will stay No Match on those as their fees on transaction were already completed based on the Fees Mappings already)

• Initiating Match Process Again, by pressing the “Run Matching” Button, for anything fixed to make sure that what should have matched is matched.

  

  View to use to review No Match “In8 Amazon Settlement Report Fees”

  

Possible Matching corrections:

• Fee Lines not listed on Cash Sales that were added from Amazon

  • Checking can be done by Verification of searching in NetSuite Global Search the Market Place Order ID, Confirming the Fees Not Matched are in fact not on the Cash Sales

    

  • For Future Settlements(As Fees were already processed on the current Transaction, & to make sure you can settle the Fees in “No Match”), Make sure the Missing Fees from Settlement are Mapped in the Marketplace Fee/Other Charges SubList Mappings.

    

    On the Settlement Report there is a Button you can press that will confirm to list the “Fee Type” and “Fee Name” into the Marketplace “Fee/Other Charges” List for you.

    

    Allowing the Seller to Only need to List the Item and GL Account Columns to complete the “Fee/Other Charges” List for settlement.

    

    If Trying to Settle Fees in “No Match” that are not mapped completely in the “Fees/Other Charges” List, the following error happens when Clicking on the Settle Fees Per the Settlement Report Page:

    

    “Error loading page:

    Error Settling Fees.
    Missing Item / GL Account value in the marketplace "Fees/Other Charges" mappings.
    Please head over to the <marketplace name> marketplace to List the Item and GL Accounts, after pressing the Process Fee/Name to Marketplace button, to complete the "Fees/Other Charges" Mapping for settlement.”

• Order Not found (Status = Error | Match = No Match)

  • Transaction Type = Order

    • Cash Sales is not Generated for the order, Get Generated to complete Matching

      • “Shipment event” may need to still run after Cash Sale Generated to process the Fees needed on Cash Sale for Matching

  • Transaction Type = Refund

    • Credit Memo is not Generated for the Return Authorization, Get Generated to complete Matching

      • “Refund Event” should create all Records for RMA (See section, Reprocessing Refund and Shipment events > Fee Processing Script > Refund event; Below)

  • Then when matching verification is complete, the NetSuite Seller would need to hit the “Move to Processing/Posting” button to allow the Settlement Report to be processed in Next Schedule Processing Script run.

Reprocessing Refund and Shipment Events

For the Transaction Fee lines in the Settlement Report, that Are Status Error & Match status “No Match”:

The Fees Queue Event Payloads should be confirmed Primary Status is in Pending for re-processing. Then the Fees Processing Script to be ran again to process and complete the Fees update to the Transaction needed; based on the Marketplace Settings “Fees/Other Charge” Mappings.

Fees Queue

Pulls the payloads of all the “Refund Events” & “Shipment Events” From Amazon that will be Processed to List Fees on the Transactions that are Mapped in the Marketplace settings:

Under Setup Tab > In8 Amazon Seller Central > Fees Queue

• Shipment Event

  • “Shipment Event” Records should stay in Processing until Cash Sales are created. Then complete when Fees are listed on them.

• Refund Event

  • Since the “Refund Event” Queue Payload when processed would create the Full RMA Return, in the cases where the NetSuite Seller created a RMA for the Item Manually the Following error will be reported on the Queue Request error:
    Manually Created RMA found with; No Event Date & No Fee Queue ID
    Please delete the RMA and reprocess Queue record. Integration will create the full RMA and list fees when Record is processed

    • Returns should be allow to be created Solly by the Amazon Integration for all Amazon Orders, to allow also all fees to be listed that are mapped in the Marketplace Settings under the “Fees/Other Charge” subtab.

Fees Processes Script

Runs on the “Refund Events” & “Shipment Events” payload From Amazon to list the Fees on the Transactions that are Mapped in the Marketplace settings:

Under Setup Tab > In8 Amazon Seller Central > Fees Processing

• Shipment Event

  • “Shipment Event” Queue record to process and list fees on both Sales Order and Cash Sales, requires the Cash sale to be Generated against the Sales order.

    • FBA- Should Fulfill and Bill the order initially

    • FBM- After the Seller Fulfills the NetSuite Order, the Order should be Billed

      • There is a Setting in the Marketplace Settings > Under the Orders SubTab > Under FBM Settings for “Enable FBM Auto Bill On Fulfillment”. To bill the order once the Sales Order is Pending Billing and fully Fulfilled. This will allow the Incoming Shipment Event from Amazon, listed by the Seller, to be able to update the Fees on Sales Order & Cash Sale; that are mapped in the Marketplace Settings under the “Fees/Other Charge” subtab.

• Refund Event

  • “Refund Event” Queue Record When Processed, Creates the RMA in Full & lists all Fees on the Return Authorization & Credit Memo Transactions, that are mapped in the Marketplace Settings under the “Fees/Other Charge” subtab.

    • Return Authorization

      • Lists Fees that are mapped in the Marketplace Settings under the “Fees/Other Charge” subtab.

      • Listing the Fee Queue Record Internal ID into the Return Authorization Field “In8 Amazon Fee Queue“

      • Listing the Refund Event Posting Date into the Return Authorization Field “In8 Amazon Event Date“

    • Item Receipt

      • When FBM Return- the Warehouse Location set will be the “FBM Return Warehouse” If that is not set it will use the “FBM Warehouse”, configured in Marketplace Settings under the Orders Subtab.

    • Credit Memo

      • Lists Fees that are mapped in the Marketplace Settings under the “Fees/Other Charge” subtab.

    • Customer Refund

Process Settlement Reports

When the Settlement Report is Processed for Posting, there will be possibly Two Journal Entries created

1. The Main “Bank Transfer” Journal Entry to Credit the Clearing Account for the Amount on the Settlement being Paid to the Seller, Debiting the Bank Account for the Seller.

2. The “Settlement Transaction” Journal Entry to Credit/Debit as needed from/to the Clearing account and GL Accounts for the Fees not found on the Existing Transaction (“No Match” Fee lines from the Report)

   1. For future Settlement Reports to find Matches for the Fee Types that we No Match on current Settlement Reports, they would need Mapped Currently in the Marketplace Settings under the “Fees/Other Charge” subtab.

If the Settlement Report is Match Verified and Moved from Status “Match Verification Need” to “For Processing/Posting”, A button “Settle Fees” will be shown. Allowing for the Settlement of Fees to be processed. There is a Script that can be scheduled to process Settlement Reports when in status “For Processing/Posting” named “In8 Amazon Settlement Transact MR”

Again, If the Fees to be Settled are NOT in the Associated Marketplace to be able to List on the Settlement Transaction Journal Entry you will see the following message:

“Error loading page:

Error Settling Fees.
Missing Item / GL Account value in the marketplace "Fees/Other Charges" mappings.
Please head over to the <marketplace name> marketplace to List the Item and GL Accounts, after pressing the Process Fee/Name to Marketplace button, to complete the "Fees/Other Charges" Mapping for settlement.”

Click on the Button “Process Fee/Name to Marketplace” for listing all the Fees missing in the “Fees/Other Charges” Sublist, then list the Item and GL Account needed for All Fees listed. Then the Settlement Report should allow to Settle Fees.

There is a Button for “Bank Transfer” to Process the Bank Transfer Journal Entry at any time.