Amazon Inventory Sync

Amazon Inventory Sync

Quick View: Amazon Sync Fix & FBM Alias Standard

We've stabilized the Amazon inventory flow by updating the data standard. Here’s a brief summary of the fix.

🛑 The Problem (Duplication & Sync Errors)

  • Initial Issue: Inventory was either **not syncing or duplicating listings**.

  • Technical Block: The Saved Search was forced to handle highly conditional, multi-line output (up to 4+ FBM aliases) while excluding the Parent SKU. NetSuite could not process this complex filtering reliably, leading to the **Parent SKU being incorrectly duplicated** alongside valid aliases.

  • ​

✅ The Solution (Data Standardization)

  • Action Taken: **Devon cleaned up all listings** to enforce a **single FBM alias maximum** per Inventory Item.

  • Impact: This standardization eliminated the need for complex, error-prone filtering.

  • ​

🌟 Final Logic (How the Search Works Now)

  • Goal Reminder: The key objective is to ensure the Saved Search output provides a **perfect 1:1 match** for the SKU Amazon expects.

  • Logic: **If FBM alias exists, return the Alias Name. If not (only FBA or no aliases), return the Parent SKU.**

  • Result: Only one correct record is passed to the Celigo flow, resolving the duplication issues.

Where to Verify

​

Resource

Name

Celigo Flow

NetSuite Inventory to Amazon Inventory Add/Update [AMZ-US-1]

Saved Search

Celigo Amazon Inventory Export Search [AMZ-US-1] - 3

⚙️ Technical Deep Dive: The Final Formula

This formula implements the priority logic while ensuring the Parent SKU is always returned in a clean format (without matrix prefixes) for Amazon.

Formula:

CASE 

    WHEN {custrecord_celigo_etail_alias_par_item.name} IS NULL 

    THEN CASE NVL2(REGEXP_REPLACE(REGEXP_SUBSTR({name},': [^:]+*$'),': ',''),1,2)

        WHEN 2 THEN {name} 

        ELSE REGEXP_REPLACE(REGEXP_SUBSTR({name},': [^:]+*$'),': ','') 

    END 

    WHEN {custrecord_celigo_etail_alias_par_item.custrecord1} = 'F' 

    THEN {custrecord_celigo_etail_alias_par_item.name} 

    ELSE CASE NVL2(REGEXP_REPLACE(REGEXP_SUBSTR({name},': [^:]+*$'),': ',''),1,2)

        WHEN 2 THEN {name} 

        ELSE REGEXP_REPLACE(REGEXP_SUBSTR({name},': [^:]+*$'),': ','') 

    END 

END

       

Explanation:

Section

Role

Function

**FBM Priority**

`WHEN {custrecord_celigo_etail_alias_par_item.custrecord1} = 'F' THEN {custrecord_celigo_etail_alias_par_item.name}`

If an FBM alias is found (due to the single-FBM rule), this clause is met first, returning the clean alias name.

**SKU Fallback Logic**

The sections using `CASE NVL2(...)` and `ELSE CASE NVL2(...)`

These handle the fallback when no alias is joined (`IS NULL`) or when only an FBA alias is present (`ELSE`). **This ensures the Parent SKU is always returned.**

**Matrix Cleanup**

`REGEXP_REPLACE(REGEXP_SUBSTR({name},': [^:]+*$'),': ','')`

This is the crucial cleanup. This regular expression **handles and removes the matrix prefix** (e.g., `Parent SKU :`) by extracting only the final SKU segment. This ensures Amazon receives a clean SKU/Item ID.


    • Related Articles

    • Purchasing - Creating Inventory Adjustments | 2022

      Overview This manual will guide you through the steps of creating an inventory adjustment in NetSuite. Requirements NetSuite account Appropriate permissions to create inventory adjustments General SOPs for Creating Inventory Adjustments All ...

    • Purchasing - Inventory Control Best Practices | 2022

      Investigating Inventory Variances - Best Practices Investigating and determining the cause(s) of a discrepancy can be a tedious & time consuming process. You must consider an item’s overall value, amount of variance, current inventory levels, & item ...

    • DT171 Not on INV Report

      Great work. Here is a search that should give you what you need next time you need to audit a Temp OOS Inactivated item. It sounds like this also results in them dropping off the INV Report. We will keep this in mind. OOS Temp Inactivate Audit: ...

    • ShipHawk General Troubleshooting and Common Errors | 2022

      This tutorial will instruct on the most common types of errors and troubleshooting steps. Overview This manual will go over some of the more common errors and troubleshooting steps to take when trying to resolve. This tutorial will focus on three ...

    • ShipHawk General Information and User Interface | 2022

      This tutorial will go over general information and navigating ShipHawks user interface. Overview This manual will go over general ShipHawk information along with user interface navigation.This is a quick high level tutorial. For more specific ...