How to resolve the Stock Sync issue with the help of a Discrepancy Report & Product Report from OMS – Anchanto

In a situation where we see that stock quantity on marketplace is not matching with the one in OMS even after couple of stock syncs performed, in that case Seller can check for the discrepancies between Marketplace and OMS and action them accordingly to remove any discrepancies to get the stock synced properly.

To generate the Discrepancy Report and investigate, follow the below steps:

Login into your OMS account.
Go to Reports -> Generated Reports -> Create New.
On the pop-up, select Stock Discrepancy Report and the marketplace for which you wish to generate the report.
Same way generate the Product Report as well.
Once the reports are generated, click on the download link to download both these reports.

Once you’ve downloaded the report, open the Discrepancy Report and it should look like below (Note: The columns may vary for different marketplaces like WooCommerce, Zalora).

Add 3 columns after the column G (Marketplace Product Type), and you may want to rename them as Parent ID Mismatch, Variation ID Mismatch, and Product Type Mismatch.

Apply Conditional Formatting for Duplicate Values to the column A (SKU).
Select Column A. Click Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.

Now need to apply few formulae to find out Parent ID Mismatch, Variation ID Mismatch, and Product Type Mismatch.
1. Parent ID Mismatch = Parent Item ID - SelluSeller Item ID.
  Apply this to the whole column H.
2. Variation ID Mismatch = Variation ID - SelluSeller variation ID.
  Apply this to the whole column I.
3. Product Type Mismatch = SelluSeller Product Type = Marketplace Product Type.
  Apply this to the whole column J.

Select Row 1 and apply the filter to the complete Row. Click Home > Sort & Filter > Filter.

Now create a Blank Sheet with column headers as Duplicate SKUs, SKUs with Different Parent ID, SKUs with Different Variation ID, and SKUs with Different Product Type.

Now, on column A (SKU) of the Discrepancy report, apply the filter for Filter by Colour > Filter by Cell Colour.
Copy the resulting the SKUs and paste it on the Column Duplicate SKUs of the New Sheet.
Remove the applied filter of column A of the Discrepancy report.

Now, apply the filter on Column H (Parent ID Mismatch) of the Discrepancy Report for the all the values except 0 and #VALUE!
Copy the resulting the SKUs from Column A (SKU) and paste it under the Column SKUs with Different Parent ID of the New Sheet.
Remove the applied filter of column H of the Discrepancy report.

Now, apply the filter on Column I (Variation ID Mismatch) of the Discrepancy Report for the all the values except 0 and #VALUE!
Copy the resulting the SKUs from Column A (SKU) and paste it under the Column SKUs with Different Variation ID of the New Sheet.
Remove the applied filter of column I of the Discrepancy report.
Now, apply the filter on Column J (Product Type Mismatch) of the Discrepancy Report for the value FALSE.
Copy the resulting the SKUs from Column A (SKU) and paste it under the Column SKUs with Different Product Type of the New Sheet.
Remove the applied filter of column J of the Discrepancy report.

You’ve found the SKUs with discrepancy between Marketplace and OMS.

Save this file with proper name.
Now, open the Product Report you've generated.

NOTE: Each report will consists of details for around 50K SKUs. In case the number of SKUs listed/created on OMS are more than 50K+, multiple Product Reports will be generated.
You can download all the generated Product Reports and collate the data from Sheet1 into one single file.
The Product report will look like below:
Note that if the SKU is listed on multiple marketplaces, it'll have multiple entries equal to the number of marketplaces it is listed for on OMS (no matter what status of that SKU is on OMS).
Now, there could be a possibility that same SKU listed on two different marketplaces, may have similar marketplace code (these codes are assigned by the respective marketplace). Hence, to make sure that we are investigating the actual duplicate SKUs on OMS, we need to create another column on this sheet and perform the concatenation operation:
1. Go to the column next to Marketplace Code (last column in the report), put the header for the column as Unique Marketplace Code.
2. Perform concatenation operation for the columns Marketplace Nickname (Read Only) and Marketplace Code only.
Example:

Result:

3. Apply this to the whole column.
Now, filter out the SKUs having blank marketplace code in column Marketplace Code.
These are the SKUs created on OMS, but not listed on the marketplace.
Now, apply the conditional formatting to find out the duplicates on the column we created i.e. Unique Marketplace Code.
Copy these SKUs and paste the same onto the sheet that we created above when checked for the discrepancies through Discrepancy Report.
Save the file.
Now, below are the actions that need to perform on these SKUs found with discrepancy:

Duplicate SKU: Maintain unique SKU at Marketplace. Delete the duplicate ones from the respective marketplace.
Variation ID mismatch: SKU code changed at Marketplace. Confirm if the update is made correctly at Marketplace. Possible Duplicate SKUs on Marketplace.
Parent ID Mismatch: SKU code changed at Marketplace. Confirm if the update is made correctly at Marketplace. Possible Duplicate SKUs on Marketplace.
Product Type mismatch: Check the product type on Marketplace and maintain the unique product type at all platforms.
Once you've taken care of the duplicate SKUs, remove the marketplace codes from the SKUs under Parent ID Mismatch and Variation ID mismatch by going to Catalogue -> Bulk Actions -> Bulk Products ID/Code Unmap. Download the template file, update the SKUs in that file. While uploading the file, select the marketplace which you want to Unmap the codes for.

Once this is done, request you to perform the Product Sync followed by Stock Sync and check.

Related articles