Skip to main content

​
Overview

The purchase system handles inventory procurement from suppliers. Purchases automatically update product stock levels and maintain detailed records of costs and quantities for inventory management.

​
Purchase Entity

The Compra entity represents a complete purchase transaction from a supplier.

​
Entity Fields

​
Norden
string
required
Unique purchase order identifier (primary key)
​
OrdenN
int
required
Sequential order number for display purposes
​
Codprov
string
required
Supplier code (foreign key to proveedores table)
​
Codusu
int
required
User ID who created the purchase (foreign key to users table)
​
Fecha
DateTime
Purchase date and time (defaults to current datetime)
​
Subtotal
decimal
required
Sum of all line items before tax
​
Iva
decimal
required
Tax amount (IVA - Value Added Tax)
​
Total
decimal
required
Final purchase total (Subtotal + IVA)
​
Estado
int
default:"1"
Purchase status (1 = active, other values for different states)

​
Relationships

  • ComprasDetalles: Collection of line items for this purchase
  • CodprovNavigation: Supplier information
  • CodusuNavigation: User who created the purchase

​
Purchase Details

Each purchase contains one or more line items stored in the ComprasDetalle entity.

​
Line Item Fields

​
Id
int
required
Auto-generated line item identifier
​
Norden
string
required
Purchase order reference (foreign key to compras)
​
Codprod
string
required
Product code (foreign key to productos)
​
Cantidad
int
required
Quantity of product purchased
​
Precio
decimal
required
Unit purchase price for this product
​
Subtotal
decimal
required
Line total (Cantidad × Precio)

​
Relationships

  • NordenNavigation: Parent purchase record
  • CodprodNavigation: Product information

​
Creating a Purchase

1

Select Supplier

Choose the supplier (Codprov) from the proveedores table. Ensure the supplier is active (estado = 1).
2

Create Purchase Header

Create a new Compra record with:
  • Unique Norden (purchase order ID)
  • Sequential OrdenN for display
  • Supplier code (Codprov)
  • User creating the purchase (Codusu)
  • Current date/time (Fecha)
3

Add Purchase Line Items

Create ComprasDetalle records for each product being purchased:
  • Product code (Codprod)
  • Quantity (Cantidad)
  • Unit price (Precio)
  • Calculate line subtotal: Cantidad × Precio
The system will automatically increase inventory stock when purchase details are saved.
4

Calculate Totals

Sum all line item subtotals to get the purchase Subtotal, calculate Iva (tax), and compute the final Total:
Subtotal = SUM(ComprasDetalle.Subtotal)
Iva = Subtotal × IVA_RATE
Total = Subtotal + Iva
5

Update Product Costs

Optionally update the precioCompra field in the productos table to reflect the latest purchase price for cost tracking and margin analysis.

​
Inventory Impact

When purchase details are saved, the system should increase product stock levels: 
UPDATE productos
SET stock = stock + cantidad
WHERE codprod = @ProductCode
This ensures inventory counts reflect received merchandise.

​
Cascade Deletion

Purchase details are configured with ON DELETE CASCADE, meaning:
  • Deleting a purchase (Compra) automatically deletes all associated line items (ComprasDetalle)
  • This maintains referential integrity
  • Ensure stock adjustments are handled before deletion if necessary

​
Database Schema

CREATE TABLE compras(
    norden VARCHAR(50) PRIMARY KEY,
    ordenN INT NOT NULL,
    codprov VARCHAR(50) NOT NULL,
    codusu INT NOT NULL,
    fecha DATETIME DEFAULT GETDATE(),
    subtotal DECIMAL(18,2) NOT NULL,
    iva DECIMAL(18,2) NOT NULL,
    total DECIMAL(18,2) NOT NULL,
    estado INT DEFAULT 1,
    FOREIGN KEY (codprov) REFERENCES proveedores(codprovee),
    FOREIGN KEY (codusu) REFERENCES users(id)
)

CREATE TABLE comprasDetalle(
    id INT IDENTITY(1,1) PRIMARY KEY,
    norden VARCHAR(50) NOT NULL,
    codprod VARCHAR(50) NOT NULL,
    cantidad INT NOT NULL,
    precio DECIMAL(18,2) NOT NULL,
    subtotal DECIMAL(18,2) NOT NULL,
    FOREIGN KEY (norden) REFERENCES compras(norden) ON DELETE CASCADE,
    FOREIGN KEY (codprod) REFERENCES productos(codprod)
)

​
Key Differences from Sales

AspectPurchasesSales
EntityCompraVenta
PartnerSupplier (proveedores)Customer (clientes)
Stock ImpactIncreases inventoryDecreases inventory
Payment PlansNot supportedSupports CONTADO/CREDITO
InterestN/AAvailable for credit sales
VoidingUse estado fieldUse nula field

​
Stock Management

Purchases work together with the inventory system:
  • Stock Level: Updated automatically when purchase details are saved
  • Minimum Stock: Check stockMinimo to identify reorder points
  • Purchase Price: Update precioCompra for margin calculations
  • Low Stock Alerts: Index on stock, stockMinimo enables efficient queries
-- Find products below minimum stock
SELECT codprod, descripcion, stock, stockMinimo
FROM productos
WHERE stock < stockMinimo
AND estado = 1

​
Best Practices

Before creating a purchase, verify current stock levels to avoid over-ordering:
var product = await context.Productos
    .FirstOrDefaultAsync(p => p.Codprod == productCode);

if (product.Stock > product.StockMinimo * 3) {
    // Consider reducing order quantity
}
Use purchase history to identify reliable suppliers and track price trends:
SELECT p.nombre, AVG(cd.precio) as avgPrice, COUNT(*) as orderCount
FROM compras c
INNER JOIN proveedores p ON c.codprov = p.codprovee
INNER JOIN comprasDetalle cd ON c.norden = cd.norden
WHERE cd.codprod = @ProductCode
GROUP BY p.nombre
Record accurate purchase dates for:
  • Cost of goods sold (COGS) calculations
  • Supplier performance analysis
  • Seasonal demand planning
  • Sales - Managing customer sales transactions
  • Products - Product and inventory management
  • Suppliers - Supplier information management

Build docs developers (and LLMs) love

Get started for freeTalk to us