rckrdmrd
5043a640e4
- Replace old DDL structure with new numbered files (01-24) - Update migrations and seeds for new schema - Clean up deprecated files Co-Authored-By: Claude Opus 4.5 <noreply@anthropic.com>
244 lines
9.2 KiB
SQL
244 lines
9.2 KiB
SQL
-- =============================================================
|
|
-- ARCHIVO: 23-purchases.sql
|
|
-- DESCRIPCION: Ordenes de compra
|
|
-- VERSION: 1.0.0
|
|
-- PROYECTO: ERP-Core V2
|
|
-- FECHA: 2026-01-13
|
|
-- DEPENDE DE: 16-partners.sql, 17-products.sql, 18-warehouses.sql
|
|
-- =============================================================
|
|
|
|
-- =====================
|
|
-- SCHEMA: purchases
|
|
-- =====================
|
|
CREATE SCHEMA IF NOT EXISTS purchases;
|
|
|
|
-- =====================
|
|
-- TABLA: purchase_orders
|
|
-- Ordenes de compra a proveedores
|
|
-- =====================
|
|
CREATE TABLE IF NOT EXISTS purchases.purchase_orders (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
|
|
|
|
-- Identificacion
|
|
order_number VARCHAR(30) NOT NULL,
|
|
|
|
-- Proveedor
|
|
supplier_id UUID NOT NULL REFERENCES partners.partners(id) ON DELETE RESTRICT,
|
|
supplier_name VARCHAR(200),
|
|
supplier_email VARCHAR(255),
|
|
|
|
-- Direcciones
|
|
shipping_address JSONB, -- Direccion de recepcion
|
|
|
|
-- Fechas
|
|
order_date DATE NOT NULL DEFAULT CURRENT_DATE,
|
|
expected_date DATE, -- Fecha esperada de recepcion
|
|
received_date DATE,
|
|
|
|
-- Comprador
|
|
buyer_id UUID REFERENCES auth.users(id),
|
|
|
|
-- Almacen destino
|
|
warehouse_id UUID REFERENCES inventory.warehouses(id),
|
|
|
|
-- Totales
|
|
currency VARCHAR(3) DEFAULT 'MXN',
|
|
subtotal DECIMAL(15, 2) NOT NULL DEFAULT 0,
|
|
tax_amount DECIMAL(15, 2) NOT NULL DEFAULT 0,
|
|
discount_amount DECIMAL(15, 2) NOT NULL DEFAULT 0,
|
|
shipping_amount DECIMAL(15, 2) DEFAULT 0,
|
|
total DECIMAL(15, 2) NOT NULL DEFAULT 0,
|
|
|
|
-- Terminos
|
|
payment_term_days INTEGER DEFAULT 0,
|
|
payment_method VARCHAR(50),
|
|
incoterm VARCHAR(10), -- FOB, CIF, EXW, etc.
|
|
|
|
-- Estado
|
|
status VARCHAR(20) NOT NULL DEFAULT 'draft', -- draft, sent, confirmed, partial, received, cancelled
|
|
|
|
-- Referencia del proveedor
|
|
supplier_reference VARCHAR(100),
|
|
|
|
-- Notas
|
|
notes TEXT,
|
|
internal_notes TEXT,
|
|
|
|
-- Metadata
|
|
created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
|
|
created_by UUID REFERENCES auth.users(id),
|
|
updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
|
|
updated_by UUID REFERENCES auth.users(id),
|
|
deleted_at TIMESTAMPTZ,
|
|
|
|
UNIQUE(tenant_id, order_number)
|
|
);
|
|
|
|
-- Indices para purchase_orders
|
|
CREATE INDEX IF NOT EXISTS idx_purchase_orders_tenant ON purchases.purchase_orders(tenant_id);
|
|
CREATE INDEX IF NOT EXISTS idx_purchase_orders_number ON purchases.purchase_orders(order_number);
|
|
CREATE INDEX IF NOT EXISTS idx_purchase_orders_supplier ON purchases.purchase_orders(supplier_id);
|
|
CREATE INDEX IF NOT EXISTS idx_purchase_orders_status ON purchases.purchase_orders(status);
|
|
CREATE INDEX IF NOT EXISTS idx_purchase_orders_date ON purchases.purchase_orders(order_date);
|
|
CREATE INDEX IF NOT EXISTS idx_purchase_orders_expected ON purchases.purchase_orders(expected_date);
|
|
CREATE INDEX IF NOT EXISTS idx_purchase_orders_warehouse ON purchases.purchase_orders(warehouse_id);
|
|
CREATE INDEX IF NOT EXISTS idx_purchase_orders_buyer ON purchases.purchase_orders(buyer_id);
|
|
|
|
-- =====================
|
|
-- TABLA: purchase_order_items
|
|
-- Lineas de orden de compra
|
|
-- =====================
|
|
CREATE TABLE IF NOT EXISTS purchases.purchase_order_items (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
order_id UUID NOT NULL REFERENCES purchases.purchase_orders(id) ON DELETE CASCADE,
|
|
product_id UUID REFERENCES products.products(id) ON DELETE SET NULL,
|
|
|
|
-- Linea
|
|
line_number INTEGER NOT NULL DEFAULT 1,
|
|
|
|
-- Producto
|
|
product_sku VARCHAR(50),
|
|
product_name VARCHAR(200) NOT NULL,
|
|
supplier_sku VARCHAR(50), -- SKU del proveedor
|
|
description TEXT,
|
|
|
|
-- Cantidad
|
|
quantity DECIMAL(15, 4) NOT NULL DEFAULT 1,
|
|
quantity_received DECIMAL(15, 4) DEFAULT 0,
|
|
quantity_returned DECIMAL(15, 4) DEFAULT 0,
|
|
uom VARCHAR(20) DEFAULT 'PZA',
|
|
|
|
-- Precio
|
|
unit_price DECIMAL(15, 4) NOT NULL DEFAULT 0,
|
|
|
|
-- Descuentos
|
|
discount_percent DECIMAL(5, 2) DEFAULT 0,
|
|
discount_amount DECIMAL(15, 2) DEFAULT 0,
|
|
|
|
-- Impuestos
|
|
tax_rate DECIMAL(5, 2) DEFAULT 16.00,
|
|
tax_amount DECIMAL(15, 2) DEFAULT 0,
|
|
|
|
-- Totales
|
|
subtotal DECIMAL(15, 2) NOT NULL DEFAULT 0,
|
|
total DECIMAL(15, 2) NOT NULL DEFAULT 0,
|
|
|
|
-- Lote/Serie
|
|
lot_number VARCHAR(50),
|
|
expiry_date DATE,
|
|
|
|
-- Estado
|
|
status VARCHAR(20) DEFAULT 'pending', -- pending, partial, received, cancelled
|
|
|
|
-- Metadata
|
|
created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- Indices para purchase_order_items
|
|
CREATE INDEX IF NOT EXISTS idx_purchase_order_items_order ON purchases.purchase_order_items(order_id);
|
|
CREATE INDEX IF NOT EXISTS idx_purchase_order_items_product ON purchases.purchase_order_items(product_id);
|
|
CREATE INDEX IF NOT EXISTS idx_purchase_order_items_line ON purchases.purchase_order_items(order_id, line_number);
|
|
CREATE INDEX IF NOT EXISTS idx_purchase_order_items_status ON purchases.purchase_order_items(status);
|
|
|
|
-- =====================
|
|
-- TABLA: purchase_receipts
|
|
-- Recepciones de mercancia
|
|
-- =====================
|
|
CREATE TABLE IF NOT EXISTS purchases.purchase_receipts (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
|
|
order_id UUID NOT NULL REFERENCES purchases.purchase_orders(id) ON DELETE RESTRICT,
|
|
|
|
-- Identificacion
|
|
receipt_number VARCHAR(30) NOT NULL,
|
|
|
|
-- Recepcion
|
|
receipt_date DATE NOT NULL DEFAULT CURRENT_DATE,
|
|
received_by UUID REFERENCES auth.users(id),
|
|
|
|
-- Almacen
|
|
warehouse_id UUID REFERENCES inventory.warehouses(id),
|
|
location_id UUID REFERENCES inventory.warehouse_locations(id),
|
|
|
|
-- Documentos del proveedor
|
|
supplier_delivery_note VARCHAR(100),
|
|
supplier_invoice_number VARCHAR(100),
|
|
|
|
-- Estado
|
|
status VARCHAR(20) NOT NULL DEFAULT 'draft', -- draft, confirmed, cancelled
|
|
|
|
-- Notas
|
|
notes TEXT,
|
|
|
|
-- Metadata
|
|
created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
|
|
created_by UUID REFERENCES auth.users(id),
|
|
updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
UNIQUE(tenant_id, receipt_number)
|
|
);
|
|
|
|
-- Indices para purchase_receipts
|
|
CREATE INDEX IF NOT EXISTS idx_purchase_receipts_tenant ON purchases.purchase_receipts(tenant_id);
|
|
CREATE INDEX IF NOT EXISTS idx_purchase_receipts_order ON purchases.purchase_receipts(order_id);
|
|
CREATE INDEX IF NOT EXISTS idx_purchase_receipts_number ON purchases.purchase_receipts(receipt_number);
|
|
CREATE INDEX IF NOT EXISTS idx_purchase_receipts_date ON purchases.purchase_receipts(receipt_date);
|
|
CREATE INDEX IF NOT EXISTS idx_purchase_receipts_status ON purchases.purchase_receipts(status);
|
|
|
|
-- =====================
|
|
-- TABLA: purchase_receipt_items
|
|
-- Lineas de recepcion
|
|
-- =====================
|
|
CREATE TABLE IF NOT EXISTS purchases.purchase_receipt_items (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
receipt_id UUID NOT NULL REFERENCES purchases.purchase_receipts(id) ON DELETE CASCADE,
|
|
order_item_id UUID REFERENCES purchases.purchase_order_items(id),
|
|
product_id UUID REFERENCES products.products(id) ON DELETE SET NULL,
|
|
|
|
-- Cantidad
|
|
quantity_expected DECIMAL(15, 4),
|
|
quantity_received DECIMAL(15, 4) NOT NULL,
|
|
quantity_rejected DECIMAL(15, 4) DEFAULT 0,
|
|
|
|
-- Lote/Serie
|
|
lot_number VARCHAR(50),
|
|
serial_number VARCHAR(50),
|
|
expiry_date DATE,
|
|
|
|
-- Ubicacion de almacenamiento
|
|
location_id UUID REFERENCES inventory.warehouse_locations(id),
|
|
|
|
-- Control de calidad
|
|
quality_status VARCHAR(20) DEFAULT 'pending', -- pending, approved, rejected, quarantine
|
|
quality_notes TEXT,
|
|
|
|
-- Metadata
|
|
created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- Indices para purchase_receipt_items
|
|
CREATE INDEX IF NOT EXISTS idx_purchase_receipt_items_receipt ON purchases.purchase_receipt_items(receipt_id);
|
|
CREATE INDEX IF NOT EXISTS idx_purchase_receipt_items_order_item ON purchases.purchase_receipt_items(order_item_id);
|
|
CREATE INDEX IF NOT EXISTS idx_purchase_receipt_items_product ON purchases.purchase_receipt_items(product_id);
|
|
CREATE INDEX IF NOT EXISTS idx_purchase_receipt_items_lot ON purchases.purchase_receipt_items(lot_number);
|
|
|
|
-- =====================
|
|
-- COMENTARIOS
|
|
-- =====================
|
|
COMMENT ON TABLE purchases.purchase_orders IS 'Ordenes de compra a proveedores';
|
|
COMMENT ON COLUMN purchases.purchase_orders.status IS 'Estado: draft, sent, confirmed, partial (parcialmente recibido), received, cancelled';
|
|
COMMENT ON COLUMN purchases.purchase_orders.incoterm IS 'Termino de comercio internacional: FOB, CIF, EXW, etc.';
|
|
|
|
COMMENT ON TABLE purchases.purchase_order_items IS 'Lineas de detalle de ordenes de compra';
|
|
COMMENT ON COLUMN purchases.purchase_order_items.supplier_sku IS 'Codigo del producto segun el proveedor';
|
|
COMMENT ON COLUMN purchases.purchase_order_items.quantity_received IS 'Cantidad ya recibida de esta linea';
|
|
|
|
COMMENT ON TABLE purchases.purchase_receipts IS 'Documentos de recepcion de mercancia';
|
|
COMMENT ON COLUMN purchases.purchase_receipts.status IS 'Estado: draft, confirmed, cancelled';
|
|
|
|
COMMENT ON TABLE purchases.purchase_receipt_items IS 'Lineas de detalle de recepciones';
|
|
COMMENT ON COLUMN purchases.purchase_receipt_items.quality_status IS 'Estado QC: pending, approved, rejected, quarantine';
|