Odoo Lot/Serial Number Duplicate — 'Serial Number Already Exists' Fix

Duplicate Lot/Serial Number Errors

When receiving or manufacturing products with tracking, you encounter:

ValidationError: The serial number 'SN-2026-001' is already used 
for product 'Laptop Model X'.

IntegrityError: duplicate key value violates unique constraint 
"stock_lot_product_company_uniq"

UserError: A serial number can only be linked to a single unit.
Serial 'SN-2026-001' is already assigned to another unit.

Understanding Lot vs Serial Tracking

Fix 1: Serial Number Already Used

# A serial number must be unique per product per company

# Find where the serial is used:
sudo -u postgres psql -d mydb -c "
  SELECT sl.name, sl.product_id, pt.name as product_name,
    sl.company_id, sq.location_id, loc.complete_name, sq.quantity
  FROM stock_lot sl
  JOIN product_product pp ON sl.product_id = pp.id
  JOIN product_template pt ON pp.product_tmpl_id = pt.id
  LEFT JOIN stock_quant sq ON sq.lot_id = sl.id
  LEFT JOIN stock_location loc ON sq.location_id = loc.id
  WHERE sl.name = 'SN-2026-001';
"

# Options:
# 1. Use a different serial number for the new unit
# 2. If the old serial was scrapped/returned, the serial should be freed
# 3. If the old serial is in a virtual location (customer/scrap),
#    the serial technically still exists in the system

Fix 2: Lot Number Reuse

# Unlike serials, lot numbers CAN hold multiple units
# But they must be for the SAME product

# Error: trying to use Lot "BATCH-001" for Product A
# when it already belongs to Product B

# This is by design — each lot is tied to one product
# Solution: Use a different lot name for the different product
# Or create a naming convention: PRODUCT-BATCH-001

Fix 3: Serial Numbers from Returns

# When a customer returns a serialized product:
# The serial number comes back into stock
# If you try to receive a NEW unit with the same serial: error

# Check if the serial exists from a return:
# Inventory > Products > Lot/Serial Numbers
# Search for the serial
# Check its current location

# If the returned unit is back in stock:
# You cannot create another unit with the same serial
# Either:
# - Use the existing serial (it's the same physical unit)
# - Use a new serial for a genuinely different unit

Fix 4: Multi-Company Serial Conflicts

# Serials are unique per product PER COMPANY
# The same serial can exist in different companies

# But if inter-company transfers share serials:
# The receiving company gets a copy of the lot record

# Check company on the lot:
sudo -u postgres psql -d mydb -c "
  SELECT name, product_id, company_id
  FROM stock_lot
  WHERE name = 'SN-2026-001';
"

# If the lot is in the wrong company:
# Create it in the correct company context
# Or update the company_id if it was misassigned

Fix 5: Import Duplicate Handling

# When importing products with serial numbers via CSV:
# Each serial must be unique per product

# CSV format:
product_id,lot_id,quantity
Product A,SN-001,1
Product A,SN-002,1
Product A,SN-001,1  # ERROR: duplicate!

# Fix: remove duplicate rows from CSV before importing
# Use spreadsheet formulas to detect duplicates:
# =COUNTIF(B:B, B2) > 1  (flags duplicates in column B)

# For mass import of serials:
# Use unique naming convention: PRODUCT-YYYYMMDD-SEQ
# Example: LAPTOP-20260324-001, LAPTOP-20260324-002

Fix 6: Cleaning Up Stale Serial Records

# If serial numbers exist in the system but no physical product:
# The lot record persists even after the product is consumed/scrapped

# Find orphaned lots (no quant with quantity):
sudo -u postgres psql -d mydb -c "
  SELECT sl.id, sl.name, sl.product_id, pt.name
  FROM stock_lot sl
  JOIN product_product pp ON sl.product_id = pp.id
  JOIN product_template pt ON pp.product_tmpl_id = pt.id
  LEFT JOIN stock_quant sq ON sq.lot_id = sl.id AND sq.quantity > 0
  WHERE sq.id IS NULL
  AND sl.product_id = PRODUCT_ID;
"

# These lots can be archived but not easily deleted
# (they may be referenced by stock moves for traceability)

Prevention

DeployMonkey's AI agent validates serial/lot uniqueness during data imports and receiving operations. Automatic serial generation with unique naming conventions prevents duplicate conflicts.