Stock Balance Documentation

Overview

The Stock Balance module (stock.balance) maintains real-time inventory quantity tracking across all products, locations, lots, and containers. It aggregates stock movements to provide current and virtual (projected) stock levels for inventory management and planning.

---

Model Information

Model Name: stock.balance
Display Name: Stock Balance
Key Fields: None (composite unique constraint)

Features

• ❌ Audit logging disabled
• ❌ Multi-company support (not enforced)
• ❌ Auto-sync disabled (manual update triggers)
• ✅ Unique constraint on product/location/lot/container combination

---

Key Fields Reference

Core Fields

Field	Type	Required	Description
product_id	Many2One	✅	Product being tracked
location_id	Many2One	✅	Storage location
lot_id	Many2One	❌	Lot/serial number (if tracked)
container_id	Many2One	❌	Primary container
container2_id	Many2One	❌	Secondary container
uom_id	Many2One	✅	Unit of measurement

Quantity Fields

Field	Type	Description
qty_phys	Decimal	Physical quantity on hand (completed moves)
qty_virt	Decimal	Virtual quantity (physical + planned moves)
qty_out	Decimal	Planned outgoing quantity
qty_in	Decimal	Planned incoming quantity
qty2	Decimal	Secondary UoM quantity

Valuation Fields

Field	Type	Description
amount	Decimal	Inventory value (cost amount)

Stock Control Fields

Field	Type	Description
min_qty	Decimal	Minimum stock level (from orderpoint)
below_min	Boolean	Flag: quantity below minimum
max_qty	Decimal	Maximum stock level (from orderpoint)
above_max	Boolean	Flag: quantity above maximum
last_change	DateTime	Last balance update timestamp

Aggregation Fields

Field	Type	Description
agg_qty_phys	Decimal	Sum of physical quantities
agg_qty_virt	Decimal	Sum of virtual quantities
agg_amount	Decimal	Sum of cost amounts

Computed Search Fields

Field	Type	Description
supplier_id	Many2One	Product's supplier (function search)
lot_type	Selection	with_lot/without_lot (function search)
expiry_date	Date	Lot expiry date (related field)
master_product_id	Many2One	Master product (function search)

---

Understanding Stock Balance Updates

How Balances Are Calculated

Stock balances are computed from stock moves, not stored independently:

# Physical quantity = completed moves
qty_phys = SUM(qty where state='done' and location_to_id=X) 
         - SUM(qty where state='done' and location_from_id=X)

# Virtual quantity = physical + planned
qty_virt = qty_phys 
         + SUM(qty where state IN ('pending','approved') and location_to_id=X)
         - SUM(qty where state IN ('pending','approved') and location_from_id=X)

Update Trigger Mechanism

Balance updates are triggered by the stock.balance.update table:

# When stock move is created/updated
INSERT INTO stock_balance_update (product_id, lot_id) VALUES (100, 50);

# Periodic job processes updates
get_model("stock.balance").do_update_balances()

---

API Methods

1. Update Balances

Method: do_update_balances(context)

Recalculates stock balances from stock movements.

Context Options:

context = {
    "job_id": "task_123",           # For progress tracking
    "update_all": True,             # Recalculate all balances
    "update_loc_ids": [10, 11],     # Update specific locations
    "no_limit": True                # Bypass 15,000 record limit
}

Behavior: - Locks stock_balance table (exclusive) - Deletes old balance records - Queries stock moves by state (pending/approved/done) - Aggregates quantities by product/location/lot/container - Creates new balance records - Updates min/max threshold flags

Example:

# Typically called by scheduled job
get_model("stock.balance").do_update_balances()

# Force full recalculation
get_model("stock.balance").do_update_balances(
    context={"update_all": True}
)

# Update specific locations only
get_model("stock.balance").do_update_balances(
    context={"update_loc_ids": [warehouse_loc_id]}
)

---

2. Get Totals (Static Method)

Method: get_totals(product_ids, lot_ids, location_ids, date_from, date_to, states, contact_id)

Retrieves aggregated stock totals with flexible filtering.

Parameters: - product_ids (list): Filter by products - lot_ids (list): Filter by lots - location_ids (list): Filter by locations - date_from (str): Start date filter - date_to (str): End date filter - states (list): Move states to include - contact_id (int): Filter by contact

Returns: Dictionary keyed by (product_id, lot_id, location_id)

Example:

# Get balances for specific products at location
totals = get_model("stock.balance").get_totals(
    product_ids=[100, 101, 102],
    location_ids=[warehouse_id],
    date_to="2025-10-27"
)

# totals structure:
# {
#     (100, None, 10): {"qty_phys": 150, "qty_virt": 180, "amount": 7500},
#     (101, 50, 10): {"qty_phys": 75, "qty_virt": 75, "amount": 3750},
#     ...
# }

---

3. Search and Read

Method: search(...) and read(...)

Automatically triggers balance update before querying.

Behavior: - Calls update_balances() before search/read - Ensures data is current - May have performance impact on large datasets

Example:

# Search triggers update automatically
low_stock = get_model("stock.balance").search([
    ["below_min", "=", True],
    ["location_id.type", "=", "internal"]
])

# Read also triggers update
balances = get_model("stock.balance").browse(balance_ids)

---

Search Functions

Search by Product

# Find all balances for product
balances = get_model("stock.balance").search([
    ["product_id", "=", product_id]
])

Search by Location

# Find all balances at location
balances = get_model("stock.balance").search([
    ["location_id", "=", location_id],
    ["qty_phys", ">", 0]  # Only non-zero balances
])

Search by Low Stock

# Find products below minimum
low_stock = get_model("stock.balance").search([
    ["below_min", "=", True],
    ["location_id.type", "=", "internal"]
])

Search by Expiry Date

# Find expiring stock
expiring = get_model("stock.balance").search([
    ["expiry_date", "<=", "2025-12-31"],
    ["expiry_date", "!=", None],
    ["qty_phys", ">", 0]
])

Search by Lot Type

# Find balances with lots
with_lots = get_model("stock.balance").search([
    ["lot_type", "=", "with_lot"]
])

# Find balances without lots
without_lots = get_model("stock.balance").search([
    ["lot_type", "=", "without_lot"]
])

---

Common Use Cases

Use Case 1: Check Product Availability

# Get available quantity for product at location
balances = get_model("stock.balance").search_browse([
    ["product_id", "=", product_id],
    ["location_id", "=", warehouse_id]
])

total_available = sum(b.qty_phys for b in balances)
total_committed = sum(b.qty_out for b in balances)
available_to_promise = sum(b.qty_virt for b in balances)

print(f"On hand: {total_available}")
print(f"Committed: {total_committed}")
print(f"Available: {available_to_promise}")

---

Use Case 2: Low Stock Report

# Find all products below minimum stock
low_stock = get_model("stock.balance").search_browse([
    ["below_min", "=", True],
    ["location_id.type", "=", "internal"]
])

for balance in low_stock:
    deficit = balance.min_qty - balance.qty_phys
    print(f"{balance.product_id.name} @ {balance.location_id.name}")
    print(f"  Current: {balance.qty_phys}, Min: {balance.min_qty}")
    print(f"  Need to order: {deficit}")

---

Use Case 3: Inventory Valuation

# Calculate total inventory value
balances = get_model("stock.balance").search_browse([
    ["location_id.type", "=", "internal"],
    ["qty_phys", ">", 0]
])

total_value = sum(b.amount for b in balances)
total_qty = sum(b.qty_phys for b in balances)

print(f"Total inventory: {total_qty} units")
print(f"Total value: ${total_value:,.2f}")

# By product category
from collections import defaultdict
by_category = defaultdict(lambda: {"qty": 0, "value": 0})

for balance in balances:
    categ = balance.product_id.categ_id.name if balance.product_id.categ_id else "Uncategorized"
    by_category[categ]["qty"] += balance.qty_phys
    by_category[categ]["value"] += balance.amount

for categ, data in by_category.items():
    print(f"{categ}: {data['qty']} units, ${data['value']:,.2f}")

---

Use Case 4: Lot-Specific Inventory

# Get all lots for product at location
balances = get_model("stock.balance").search_browse([
    ["product_id", "=", product_id],
    ["location_id", "=", location_id],
    ["lot_id", "!=", None],
    ["qty_phys", ">", 0]
])

for balance in balances:
    lot = balance.lot_id
    print(f"Lot: {lot.number}")
    print(f"  Qty: {balance.qty_phys}")
    if lot.expiry_date:
        print(f"  Expires: {lot.expiry_date}")
        print(f"  Life remaining: {lot.life_remain_percent}%")

---

Use Case 5: Container Inventory

# Get contents of container
container_id = 123

balances = get_model("stock.balance").search_browse([
    ["container_id", "=", container_id],
    ["qty_phys", ">", 0]
])

print(f"Container contents:")
for balance in balances:
    print(f"  {balance.product_id.name}: {balance.qty_phys} {balance.uom_id.name}")
    if balance.lot_id:
        print(f"    Lot: {balance.lot_id.number}")

---

Best Practices

1. Always Filter by Positive Quantities

# Good: Filter out zero balances
balances = get_model("stock.balance").search([
    ["location_id", "=", loc_id],
    ["qty_phys", ">", 0]  # Important!
])

# Avoid: Including zero balances
all_balances = get_model("stock.balance").search([
    ["location_id", "=", loc_id]
])  # May include many zero records

2. Use Virtual Quantity for Planning

# Physical quantity = what you have now
physical = balance.qty_phys

# Virtual quantity = physical + incoming - outgoing
virtual = balance.qty_virt

# For order promising, use virtual
if virtual >= order_qty:
    print("Can fulfill order (including pending receipts)")
elif physical >= order_qty:
    print("Can fulfill now, but committed elsewhere")
else:
    print("Insufficient stock")

3. Set Stock Reorder Points

# Configure in stock.orderpoint
orderpoint_id = get_model("stock.orderpoint").create({
    "product_id": product_id,
    "location_id": warehouse_id,
    "min_qty": 50,   # Reorder when below this
    "max_qty": 200,  # Order up to this
    "uom_id": uom_id
})

# Balance automatically updates below_min/above_max flags
balance = get_model("stock.balance").search_browse([
    ["product_id", "=", product_id],
    ["location_id", "=", warehouse_id]
])[0]

if balance.below_min:
    qty_to_order = balance.max_qty - balance.qty_virt
    print(f"Reorder {qty_to_order} units")

---

Performance Tips

1. Avoid Frequent Full Updates

# Bad: Updating all balances frequently
get_model("stock.balance").do_update_balances(
    context={"update_all": True}
)  # Very expensive!

# Good: Let stock moves trigger selective updates
# Updates happen automatically via stock.balance.update table

# Only force update for specific locations when needed
get_model("stock.balance").do_update_balances(
    context={"update_loc_ids": [specific_loc_id]}
)

2. Use Aggregation Fields

# Use built-in aggregation instead of manual summing
result = get_model("stock.balance").search_read(
    domain=[["location_id", "=", loc_id]],
    fields=["agg_qty_phys", "agg_amount"]
)

total_qty = result[0]["agg_qty_phys"]
total_value = result[0]["agg_amount"]

3. Filter Early and Specifically

# Good: Specific filters reduce dataset
balances = get_model("stock.balance").search([
    ["product_id", "in", product_ids],  # Specific products
    ["location_id", "=", location_id],  # Specific location
    ["qty_phys", ">", 0]                # Non-zero only
])

# Avoid: Broad queries
all_balances = get_model("stock.balance").search([])  # Huge dataset!

---

Database Constraints

Unique Constraint

UNIQUE (product_id, location_id, lot_id, container_id, container2_id)

Only one balance record per unique combination of product/location/lot/containers.

---

Related Models

Model	Relationship	Description
product	Many2One	Product being tracked
stock.location	Many2One	Storage location
stock.lot	Many2One	Lot/serial number
stock.container	Many2One	Primary container
stock.move	Indirect	Source data for balances
stock.orderpoint	Indirect	Min/max qty settings
uom	Many2One	Unit of measurement

---

Troubleshooting

"Balance not updating"

Cause: Update job not running or stock.balance.update not triggered
Solution: Check scheduled jobs, manually call do_update_balances()

"Negative stock showing"

Cause: Outgoing moves exceed incoming moves
Solution: Check stock move history, verify no data corruption

"Performance slow on balance queries"

Cause: Auto-update triggers on every search
Solution: Use specific filters, index on commonly searched fields

"Balance mismatch with actual stock"

Cause: Update not run after recent moves
Solution: Trigger manual update for affected locations

---

Configuration Settings

Required Settings

Setting	Location	Description
Update Job	Scheduled Tasks	Regular balance update job
Stock Orderpoints	stock.orderpoint	Min/max levels per product/location

---

Integration Points

Internal Modules

• Stock Move: Source data for all balance calculations
• Stock Location: Defines where balances are tracked
• Stock Lot: Enables lot-level balance tracking
• Stock Container: Enables container-level balance tracking
• Stock Orderpoint: Provides min/max thresholds
• Product: Defines what is being tracked

---

This documentation is generated for developer onboarding and reference purposes.