Unable to reconcile the marketplace facilitator tax

Gogula R Bhavana

Updated February 08, 2021 10:48

Issue

When you run the “NetSuite Settlement Transaction to NetSuite Payment” flow, if you are unable to reconcile the marketplace facilitator tax and if you see the “Invoice record is already paid in full' but the Invoice status is Open” error in the Integration App Dashboard.

Solution

Important: This solution is only applicable to the users who are using a certain formula mentioned in the below table.

If you are using the below formula in the “Celigo AmazonIO Settlement Payment Transaction” saved search, you must delete that column and replace it with the new formula.

Action	Field Text	Formula	Custom Label
Delete this column in the saved search	Formula (Currency)	CASE WHEN INSTR({custrecord_celigo_amzio_set_ip_par_trans. custrecord_celigo_amzio_set_ip_mis_am_ty} , 'ItemWithheldTax') > 0 THEN ({custrecord_celigo_amzio_set_total_tax}+{custrecord_celigo_amzio_set_tran_amount}) ELSE {custrecord_celigo_amzio_set_tran_amount} END	Transaction Amount
Add this column to the saved search	Formula (Text)	{custrecord_celigo_amzio_set_tran_amount}-{custrecord_celigo_amzio_set_ip_titwh_tax}	Transaction Amount

Because of the earlier provided formula, multiple transactions for a single payment are created. By updating the saved search you will not get the “Invoice record is already paid in full' but the Invoice status is Open” error and multiple transactions will not be created.

Note: Ensure that the Exclude Tax if Marketplace Tax Collection Applies box is unchecked in the General > Tax tab.

By adding the new formula in the saved search, you can import the Amazon collected taxes into NetSuite Order Transaction and is displayed in the “'ItemWithheldTax'” field in your order transaction.

Update the mapping in the saved search

Login to your NetSuite account.
Search for “Celigo AmazonIO Settlement Payment Transaction” saved search in the global search.
From the results, open the saved search suffixed with your Amazon store.
Click Edit this Search.
Go to Results tab > Columns.
Replace the old column details with the new column details provided in the above table.