Skip to main content
The inventory module provides real-time visibility into stock levels, automated inventory adjustments, and intelligent reorder point monitoring to help you maintain optimal inventory levels.

​
Inventory tracking

Inventory is tracked at the product level through the Producto model. Every product maintains its current stock quantity and minimum stock threshold.

​
Stock fields

​
Stock
int
Current quantity available in inventory. Updated automatically with sales and purchases. Defaults to 0
​
StockMinimo
int
Reorder point threshold. When stock falls below this level, the product needs replenishment. Defaults to 5

​
Automated inventory adjustments

TechCore Mini ERP automatically manages inventory levels through database triggers, eliminating manual stock adjustments and reducing errors.

​
Stock decrease on sales

When products are sold, inventory decreases automatically:
1

Sale created

A new sales order is created with line items in the ventasDetalle table.
2

Trigger executes

The TR_DisminuirStock trigger fires immediately after the sale detail is inserted.
3

Stock updated

Product stock is decreased by the quantity sold, but only if the sale is valid (not voided).
4

Inventory synchronized

The product’s Stock field now reflects the current available quantity.
Voided sales (where ventas.nula = 1) do not affect inventory. The trigger only processes valid transactions.

​
Database trigger implementation

CREATE TRIGGER TR_DisminuirStock
ON ventasDetalle
AFTER INSERT
AS
BEGIN
    UPDATE p
    SET p.stock = p.stock - i.cantidad
    FROM productos p
    INNER JOIN inserted i ON p.codprod = i.codprod
    INNER JOIN ventas v ON v.norden = i.norden
    WHERE v.nula = 0
END

​
Stock increase on purchases

When products are received from suppliers, inventory levels increase through the purchase order process. Purchase details are recorded in the comprasDetalle table, and stock quantities are updated accordingly.

​
Reorder point monitoring

The system provides intelligent low stock detection to prevent stockouts.

​
How reorder points work

Set minimum levels

Configure StockMinimo for each product based on sales velocity and supplier lead time

Automatic detection

The indexed IDX_productos_stock enables fast queries to identify products below reorder points

Reorder alerts

Generate reports of products where Stock < StockMinimo to trigger purchase orders

Prevent stockouts

Proactive reordering ensures continuous product availability

​
Reorder point query

Identify products requiring replenishment: 
SELECT codprod, descripcion, stock, stockMinimo
FROM productos
WHERE stock < stockMinimo
AND estado = 1
ORDER BY (stockMinimo - stock) DESC
This query returns active products sorted by urgency (largest shortage first).

​
Inventory valuation

Calculate inventory value using purchase and sale prices stored in the product catalog.

​
Valuation methods

Calculate inventory value at purchase cost:
SELECT 
    SUM(stock * precioCompra) AS inventoryAtCost
FROM productos
WHERE estado = 1
This represents the total capital invested in current inventory.

​
Inventory movements

Track how inventory changes over time through transaction details.

​
Sales movements

Query sales history to see inventory outflows: 
SELECT 
    vd.codprod,
    p.descripcion,
    v.fecha,
    vd.cantidad AS quantitySold,
    vd.pventa AS salePrice
FROM ventasDetalle vd
INNER JOIN productos p ON vd.codprod = p.codprod
INNER JOIN ventas v ON vd.norden = v.norden
WHERE v.nula = 0
ORDER BY v.fecha DESC

​
Purchase movements

Query purchase history to see inventory inflows: 
SELECT 
    cd.codprod,
    p.descripcion,
    c.fecha,
    cd.cantidad AS quantityReceived,
    cd.precio AS purchasePrice
FROM comprasDetalle cd
INNER JOIN productos p ON cd.codprod = p.codprod
INNER JOIN compras c ON cd.norden = c.norden
ORDER BY c.fecha DESC

​
User workflows

​
Monitoring stock levels

1

View current inventory

Access the products list to see current Stock levels for all active products.
2

Identify low stock

Filter products where stock is below the minimum threshold to identify reorder needs.
3

Review by category

Group inventory views by product category to analyze stock distribution.
4

Check stock value

Calculate total inventory value to understand capital tied up in stock.

​
Setting reorder points

Set StockMinimo using this formula:Minimum Stock = (Average Daily Sales × Lead Time) + Safety StockWhere:
  • Average Daily Sales = Monthly sales ÷ 30
  • Lead Time = Days from order to delivery
  • Safety Stock = Buffer for demand variability (typically 3-7 days)
1

Analyze sales velocity

Review historical sales data to determine average daily demand for each product.
2

Determine lead time

Identify supplier lead times from order placement to inventory receipt.
3

Calculate safety stock

Add buffer stock to account for demand spikes and delivery delays.
4

Update StockMinimo

Set the minimum stock threshold in the product record.
5

Monitor and adjust

Review reorder points quarterly and adjust based on changing demand patterns.

​
Creating purchase orders from low stock

1

Generate low stock report

Query products where Stock < StockMinimo to identify items needing replenishment.
2

Group by supplier

Organize products by supplier to consolidate purchase orders.
3

Calculate order quantities

Determine how much to order: OrderQuantity = StockMinimo - Stock + SafetyBuffer
4

Create purchase orders

Generate purchase orders in the compras module for each supplier.
5

Receive inventory

When products arrive, stock levels increase automatically through the purchase receipt process.

​
Inventory reconciliation

Periodically verify physical inventory matches system records:
Physical inventory counts should be performed regularly to identify discrepancies caused by damage, theft, or data entry errors.
1

Export current stock levels

Generate a report of all products with current Stock quantities.
2

Perform physical count

Count actual inventory on hand for each product.
3

Identify variances

Compare physical counts to system records and investigate significant differences.
4

Adjust records

Update product stock quantities to match verified physical counts.
5

Document adjustments

Record reasons for adjustments (damage, loss, counting errors, etc.).

​
Best practices

  • Review and adjust StockMinimo quarterly
  • Account for seasonal demand variations
  • Consider supplier reliability when setting safety stock
  • Use higher safety stock for critical, fast-moving products
  • Monitor stockout incidents to refine reorder points
  • Perform cycle counts regularly rather than annual full counts
  • Investigate and resolve discrepancies immediately
  • Train staff on proper receiving procedures
  • Verify sale transactions are not voided incorrectly
  • Maintain secure storage to prevent unauthorized inventory movements
  • Avoid overstocking slow-moving items to reduce carrying costs
  • Maintain higher stock of fast-moving products
  • Use ABC analysis to prioritize inventory management efforts
  • Monitor aged inventory and mark down or liquidate old stock
  • Balance stock levels with storage capacity constraints
  • Generate weekly low stock reports for proactive reordering
  • Track inventory turnover ratio by product and category
  • Monitor stock-to-sales ratios to optimize inventory investment
  • Analyze stockout incidents to improve service levels
  • Review inventory value trends monthly
  • Ensure sales staff understand that stock updates automatically
  • Train receiving staff on purchase order receipt procedures
  • Verify triggers are functioning correctly after database maintenance
  • Monitor for negative stock levels indicating data errors
  • Document any manual stock adjustments with clear justifications

​
Key performance indicators

Monitor these metrics to optimize inventory management:

Stock coverage

Days of supply available: Stock ÷ Average Daily Sales

Inventory turnover

How many times inventory is sold and replaced: Annual Sales ÷ Average Inventory Value

Fill rate

Percentage of orders fulfilled from stock: Orders Filled ÷ Total Orders

Carrying cost

Cost to hold inventory: Average Inventory Value × Annual Carrying Cost %

​
Advanced inventory queries

​
Products by stock status

SELECT 
    codprod,
    descripcion,
    stock,
    stockMinimo,
    CASE 
        WHEN stock = 0 THEN 'Out of Stock'
        WHEN stock < stockMinimo THEN 'Low Stock'
        WHEN stock >= stockMinimo * 2 THEN 'Overstock'
        ELSE 'Normal'
    END AS stockStatus
FROM productos
WHERE estado = 1
ORDER BY stock ASC

​
Inventory value by category

SELECT 
    c.nombre AS category,
    COUNT(p.codprod) AS productCount,
    SUM(p.stock) AS totalUnits,
    SUM(p.stock * p.precioCompra) AS costValue,
    SUM(p.stock * p.precioVenta) AS retailValue
FROM productos p
INNER JOIN categoria c ON p.codCategoria = c.codCategoria
WHERE p.estado = 1
GROUP BY c.nombre
ORDER BY costValue DESC

Build docs developers (and LLMs) love

Get started for freeTalk to us