Fix Odoo Sequence Gaps: Missing Invoice Numbers and Broken Numbering

The Problem

Your invoice sequence jumped from INV/2026/0042 to INV/2026/0045 — numbers 43 and 44 are missing. Or your sales order numbers have gaps. In many countries, gapless invoice numbering is a legal requirement for tax compliance. Understanding why gaps happen and how to handle them is essential for Odoo accounting.

Why Sequence Gaps Happen

1. Cancelled or Deleted Invoices

The most common cause. An invoice is created (consuming sequence number 43), then cancelled or deleted. The number is gone but the sequence continues at 44.

2. Draft Invoices That Get Sequence Numbers

In some Odoo configurations, sequence numbers are assigned when the invoice is created in draft, not when it is posted. If the draft is deleted, the number is lost.

3. Database Transaction Rollbacks

PostgreSQL sequences are non-transactional. If a transaction creates an invoice (incrementing the sequence) but then rolls back due to an error, the sequence number is consumed but no invoice exists.

4. Concurrent Users

When multiple users create invoices simultaneously, sequence numbers are allocated in order but invoices may be posted in a different order, creating apparent gaps.

5. Year/Month Reset Issues

When a sequence resets at year or month boundaries, misconfigured reset dates can skip numbers.

When Gaps Matter (Legal Requirements)

• France: Gapless invoice numbering is legally required (Article 242 nonies A)
• Germany: Sequential numbering required, gaps must be explainable
• Spain: Strict sequential numbering for SII reporting
• India: GST requires sequential invoice numbering
• USA/UK: No strict legal requirement for gapless sequences, but auditors prefer it

Fixes and Prevention

Fix 1: Use Odoo's No-Gap Sequence

Odoo supports gapless sequences that guarantee no gaps:

# In the sequence configuration:
# Settings > Technical > Sequences > find the sequence
# Set "Implementation" to "No gap"

# Or in code:
sequence = self.env['ir.sequence'].create({
    'name': 'Invoice Sequence',
    'code': 'account.move',
    'implementation': 'no_gap',  # guarantees no gaps
    'prefix': 'INV/%(year)s/',
    'padding': 4,
})

Trade-off: No-gap sequences use SELECT FOR UPDATE locks, which means only one invoice can be created at a time. This can cause performance issues with many concurrent users.

Fix 2: Assign Numbers at Posting, Not Creation

Configure Odoo to assign sequence numbers only when invoices are posted (confirmed), not when created in draft.

Fix: Go to Accounting > Configuration > Settings > check the option for sequence assignment at posting time. In Odoo 17+, this is the default behavior for new installations.

Fix 3: Credit Note Instead of Delete

Instead of deleting or cancelling invoices, create a credit note. This preserves the original number and creates a proper reversal with its own number.

# Instead of:
invoice.button_cancel()  # loses the number
invoice.unlink()  # permanently deletes

# Do:
# Create a credit note (reversal)
invoice.action_reverse()  # INV/43 stays, RINV/001 created

Fix 4: Fill Gaps Manually (Use With Caution)

For past gaps, you may need to create placeholder entries to explain the missing numbers. This is jurisdiction-specific — consult your accountant.

# Check for gaps in invoice numbers:
WITH numbers AS (
    SELECT 
        CAST(REGEXP_REPLACE(name, '[^0-9]', '', 'g') AS INTEGER) as num
    FROM account_move 
    WHERE name LIKE 'INV/2026/%'
      AND state = 'posted'
)
SELECT num + 1 as missing_start, 
       next_num - 1 as missing_end
FROM (
    SELECT num, LEAD(num) OVER (ORDER BY num) as next_num
    FROM numbers
) sub
WHERE next_num - num > 1;

Fix 5: Separate Sequences for Different Document Types

Use different sequence prefixes for invoices, credit notes, and other documents to minimize confusion:

# Different prefixes:
# INV/2026/0001 - Customer invoices
# RINV/2026/0001 - Credit notes (refunds)
# BILL/2026/0001 - Vendor bills
# Each sequence is independent and gapless within its series

Fix 6: Reset Sequence Counter

If you need to reset the counter (e.g., at the start of a new fiscal year):

# Via UI:
# Settings > Technical > Sequences > find the sequence
# Set "Next Number" to the desired value

# Via SQL (last resort):
UPDATE ir_sequence 
SET number_next = 1 
WHERE code = 'account.move' 
  AND company_id = your_company_id;

# For no-gap sequences, also update:
UPDATE ir_sequence 
SET number_next_actual = 1 
WHERE code = 'account.move'
  AND company_id = your_company_id;

Auditing Sequences

# List all sequences and their current state:
SELECT code, name, implementation, number_next, 
       prefix, suffix, padding
FROM ir_sequence 
WHERE code LIKE 'account%'
ORDER BY code;

# Check for gaps in posted invoices:
SELECT name, state, date, create_date
FROM account_move 
WHERE journal_id = your_journal_id
  AND move_type = 'out_invoice'
ORDER BY name;

Best Practices

• Use "No gap" implementation for invoices in countries requiring gapless numbering
• Assign sequence numbers at posting time, not draft creation
• Never delete posted invoices — use credit notes instead
• Use separate sequences per journal/document type
• Document any unavoidable gaps with explanatory notes for auditors
• Test sequence behavior with concurrent users before going live