View brand-wise totals in settlement reports

In the Amazon settlement reports, you can view the receipt and payment details, and further segregate them based on brand names in NetSuite. To view the details based on brand names, you need to create or assign a custom field in NetSuite. The custom field that needs to be created or assigned must be an Item Field in NetSuite and the field type should be selected as Free-form text.

Categorize journal entries by brand name

To categorize the journal entry lines by brand, enter the NetSuite internal ID in the NetSuite item brand field internal ID setting (Settings > Settlement > Journal Entry).

The value entered in the NetSuite item brand field internal ID setting is displayed on the customer payment and customer refund records in NetSuite. Based on the value selected in the NetSuite item brand field internal ID setting, a lookup will be created in the Amazon Settlement Report to NetSuite Custom Settlement Records flow.

The value that needs to be entered in the NetSuite item brand field internal ID setting needs to match the Field ID of the assigned field in NetSuite. If this is not the case, you will not be able to see brand-wise totals in the settlement reports when the Amazon Settlement Report to NetSuite Custom Settlement Records flow is run.

You can view the Field ID of the assigned field in NetSuite as described below:

1. In NetSuite, access any item which contains the assigned field.
2. In the screen that appears, access the tab which contains the field. For example, if you have assigned a custom field named Brand for this purpose, access the Custom tab and navigate to the Brand field.
3. Click the field name to view the Field ID, as shown in the following image:

Edit mapping for records with no exact matches

When exact matches are not found for the records in NetSuite, the default action is to ignore such records and pass the remaining records to NetSuite. In order to avoid this, you need to select the action that needs to be performed if such an event occurs in the Action to take if unique match not found section. If you do not want the record to be ignored and want it to be added for the other flows, 

1. From the integration app, go to Flows > Settlement.
2. Next to the Amazon Settlement Report to NetSuite Custom Settlement Record flow, click Mappings().

3. Click Settings () next to the Celigo Amazon brand (or the custom NetSuite field) mapping in the Edit mapping: Post custom settlement records to NetSuite pane. The settings for the mapping field will appear:

4. In the Settings pane, choose Use Empty String as Default Value under the Action to take if unique match not found setting. This is the recommended option so that records which have empty values are passed to the next flow and not ignored. If this option is selected, records with empty values (no brand name has been entered or selected) will be imported without any error message being displayed.
5. Click Save & close in the Settings pane.
6. Click Save & close in the Edit mapping: Post custom settlement records to NetSuite pane.

Edit mapping for Item Aliases

If you are using Item Aliases, you need to modify the mapping for the Celigo Amazon brand field, as described below. To know more about item aliases, refer Item Aliases

1. From the integration app, go to Flows > Settlement.
2. Next to the Amazon Settlement Report to NetSuite Custom Settlement Record flow, click Mappings().
3. Click Settings() next to the Celigo Amazon brand (or the custom NetSuite field) mapping in the  Edit mapping: Post custom settlement records to NetSuite pane. The settings for the mapping field will appear:

4. On the Settings pane, click Add Filter to add the lookup filter for item aliases under the Refresh search filters setting.
   

   Note: Select the OR operator to add the lookup filter for viewing brand-wise totals of items when item aliases are used, in addition to the lookup filter used for viewing brand-wise totals of items.

5. Select the values <Itemaliascustomfield>, contains, sku in the drop-down lists for the item aliases.
   

   Important: If you have enabled the Request Amazon Settlement Report List flow, you need to click Settings() in the newly added lookup filter and change the operand type to Expression to enter the value as {{{sku}}}.

   Notes:

   • The values selected in the Refresh search filters section will populate the Lookup filter expression which is used to fetch the records into NetSuite.
   • The NetSuite internal ID entered in the NetSuite item brand field internal ID setting will be updated in the Value field drop-down list.
6. If an exact match is not found during the lookup, the record will be failed by default. If you do not want the record to be failed and want it to be added for the other flows, choose Use Empty String as Default Value.
   This is the recommended option so that records which have empty values are passed to the next flow and not ignored. If this option is selected, records with empty values (no brand name has been entered or selected) will be imported without any error message being displayed.
7. Click Save & close in the Settings pane.
8. Click Save & close in the Edit mapping: Post custom settlement records to NetSuite pane.

Run the flow

1. Run the Amazon Settlement Report to NetSuite Custom Settlement Records flow. The records matching the lookup value created will be imported to the Celigo Amazon Settlement Transaction Record record in NetSuite. 
2. Enable the NetSuite Settlement Transaction to NetSuite Payment Add and NetSuite Settlement Transaction to NetSuite Refund Add flows to run automatically after the Amazon Settlement Report to NetSuite Custom Settlement Record flow runs successfully. The value in the Celigo Amazon Settlement Transaction Record record is added based on the transaction,
   • If it is a payment, the value is added to the Customer Payment record.
   • If it is a refund, the value is added to the Customer Refund record.
3. Run the NetSuite Settlement Summary Record to NetSuite Journal Entry flow. The brand-wise totals are displayed in the NetSuite Journal Entry record. If there are records for which the brand name is empty or is not mapped during the settlement flows, the values for all those records will be added up and displayed with the brand name as “Other brands”.

Related topics:

• Sync your Amazon settlement report (manually or automatically)
• NetSuite Settlement Transaction to NetSuite Payment Add
• NetSuite Settlement Transaction to NetSuite Refund Add
• Manage journal entries in NetSuite