Symptom
You get an error when you try to change the Unique Serial Numbers by field (Administration → System Initialization → General Settings → tab/Inventory → sub-tab/Items).
Environment
SAP Business One
Reproducing the Issue
Prerequisites:
You are working with items managed by serial numbers and you have set the Unique Serial Numbers by field to anything except 'None'.
Scenario:
Try to change the settings for Unique Serial Numbers by, for example, from 'Mfr. Serial Number' or 'Serial Number' to 'Lot Number'.
Actual behavior:
If any duplicate serial number exists, the setting cannot be changed. The system displays one of the following messages:
You cannot change this setting, because duplicate serial numbers already exist. To change this setting, first remove duplicate serial numbers.
Duplicate Serial Numbers Already Exist in the Customer Equipment Card. To Change This Setting First Remove Duplicate Serial Numbers.
Duplicate serial numbers already exist in equipment card. To change this setting, first remove duplicate serial numbers.
Resolution
If you want to change the setting for Unique Serial Numbers by, you must make sure that all created serial numbers are unique for the option you are going to select. In addition, each Customer Equipment Card (for the same Item & Business Partner) must have a unique serial number.
To find the Serial Numbers that prevent you from updating the settings, use the following queries below. The queries to be run are those related to the new selection for Unique Serial Numbers by.
/* Manufacturer Serial No. and its Customer Equipment Card */
SELECT COUNT("MnfSerial") AS "CountQty", "MnfSerial", "ItemCode"
FROM OSRN
WHERE "MnfSerial" IS NOT NULL
GROUP BY "MnfSerial", "ItemCode"
HAVING COUNT("MnfSerial")>1;
SELECT "manufSN", "itemCode", "customer", COUNT(*)
FROM OINS
GROUP BY "manufSN", "itemCode", "customer"
HAVING COUNT(*)>1;
/* Serial Number and its Customer Equipment Card */
SELECT COUNT("DistNumber") AS "CountQty", "DistNumber", "ItemCode"
FROM OSRN
WHERE "DistNumber" IS NOT NULL
GROUP BY "DistNumber", "ItemCode"
HAVING COUNT("DistNumber")>1;
SELECT "internalSN", "itemCode", "customer", COUNT(*)
FROM OINS
GROUP BY "internalSN", "itemCode", "customer"
HAVING COUNT(*)>1;
/* Lot Number */
SELECT COUNT("LotNumber") AS "CountQty", "LotNumber", "ItemCode"
FROM OSRN
WHERE "LotNumber" IS NOT NULL
GROUP BY "LotNumber", "ItemCode"
HAVING COUNT("LotNumber")>1;
