rckrdmrd
4b6240311d
Financial Schema (50-57): - 50-financial-schema.sql: Schema + 10 ENUMs - 51-financial-accounts.sql: account_types, accounts, account_mappings - 52-financial-journals.sql: fiscal_years, fiscal_periods, journals - 53-financial-entries.sql: journal_entries, journal_entry_lines - 54-financial-invoices.sql: invoices, invoice_lines - 55-financial-payments.sql: payments, payment_invoice_allocations - 56-financial-taxes.sql: taxes, tax_groups - 57-financial-bank-reconciliation.sql: bank_statements, bank_statement_lines, rules Purchases Matching (46): - 46-purchases-matching.sql: purchase_order_matching, purchase_matching_lines, matching_exceptions Co-Authored-By: Claude Opus 4.5 <noreply@anthropic.com>
141 lines
6.8 KiB
SQL
141 lines
6.8 KiB
SQL
-- =============================================================
|
|
-- ARCHIVO: 46-purchases-matching.sql
|
|
-- DESCRIPCION: 3-Way Matching (PO-Receipt-Invoice)
|
|
-- VERSION: 1.0.0
|
|
-- PROYECTO: ERP-Core V2
|
|
-- FECHA: 2026-01-20
|
|
-- DEPENDE DE: 23-purchases.sql
|
|
-- =============================================================
|
|
|
|
-- =====================
|
|
-- TABLA: purchase_order_matching
|
|
-- Registro de matching por orden de compra
|
|
-- =====================
|
|
CREATE TABLE IF NOT EXISTS purchases.purchase_order_matching (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
|
|
purchase_order_id UUID NOT NULL REFERENCES purchases.purchase_orders(id) ON DELETE RESTRICT,
|
|
|
|
-- Estado del matching
|
|
status VARCHAR(20) NOT NULL DEFAULT 'pending', -- pending, partial_receipt, received, partial_invoice, matched, mismatch
|
|
|
|
-- Totales
|
|
total_ordered DECIMAL(15, 2) NOT NULL,
|
|
total_received DECIMAL(15, 2) DEFAULT 0,
|
|
total_invoiced DECIMAL(15, 2) DEFAULT 0,
|
|
|
|
-- Varianzas calculadas
|
|
receipt_variance DECIMAL(15, 2) GENERATED ALWAYS AS (total_ordered - total_received) STORED,
|
|
invoice_variance DECIMAL(15, 2) GENERATED ALWAYS AS (total_received - total_invoiced) STORED,
|
|
|
|
-- Referencias al ultimo documento
|
|
last_receipt_id UUID REFERENCES purchases.purchase_receipts(id),
|
|
last_invoice_id UUID,
|
|
|
|
-- Matching completado
|
|
matched_at TIMESTAMPTZ,
|
|
matched_by UUID REFERENCES auth.users(id),
|
|
|
|
-- Metadata
|
|
created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
UNIQUE(tenant_id, purchase_order_id)
|
|
);
|
|
|
|
-- Indices para purchase_order_matching
|
|
CREATE INDEX IF NOT EXISTS idx_purchase_order_matching_tenant ON purchases.purchase_order_matching(tenant_id);
|
|
CREATE INDEX IF NOT EXISTS idx_purchase_order_matching_po ON purchases.purchase_order_matching(purchase_order_id);
|
|
CREATE INDEX IF NOT EXISTS idx_purchase_order_matching_status ON purchases.purchase_order_matching(status);
|
|
CREATE INDEX IF NOT EXISTS idx_purchase_order_matching_receipt ON purchases.purchase_order_matching(last_receipt_id);
|
|
|
|
-- =====================
|
|
-- TABLA: purchase_matching_lines
|
|
-- Matching por linea de orden de compra
|
|
-- =====================
|
|
CREATE TABLE IF NOT EXISTS purchases.purchase_matching_lines (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
matching_id UUID NOT NULL REFERENCES purchases.purchase_order_matching(id) ON DELETE CASCADE,
|
|
order_item_id UUID NOT NULL REFERENCES purchases.purchase_order_items(id) ON DELETE RESTRICT,
|
|
tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
|
|
|
|
-- Cantidades
|
|
qty_ordered DECIMAL(15, 4) NOT NULL,
|
|
qty_received DECIMAL(15, 4) DEFAULT 0,
|
|
qty_invoiced DECIMAL(15, 4) DEFAULT 0,
|
|
|
|
-- Precios
|
|
price_ordered DECIMAL(15, 2) NOT NULL,
|
|
price_invoiced DECIMAL(15, 2) DEFAULT 0,
|
|
|
|
-- Varianzas calculadas
|
|
qty_variance DECIMAL(15, 4) GENERATED ALWAYS AS (qty_ordered - qty_received) STORED,
|
|
invoice_qty_variance DECIMAL(15, 4) GENERATED ALWAYS AS (qty_received - qty_invoiced) STORED,
|
|
price_variance DECIMAL(15, 2) GENERATED ALWAYS AS (price_ordered - price_invoiced) STORED,
|
|
|
|
-- Estado
|
|
status VARCHAR(20) DEFAULT 'pending', -- pending, partial, matched, mismatch
|
|
|
|
-- Metadata
|
|
created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- Indices para purchase_matching_lines
|
|
CREATE INDEX IF NOT EXISTS idx_purchase_matching_lines_matching ON purchases.purchase_matching_lines(matching_id);
|
|
CREATE INDEX IF NOT EXISTS idx_purchase_matching_lines_order_item ON purchases.purchase_matching_lines(order_item_id);
|
|
CREATE INDEX IF NOT EXISTS idx_purchase_matching_lines_tenant ON purchases.purchase_matching_lines(tenant_id);
|
|
CREATE INDEX IF NOT EXISTS idx_purchase_matching_lines_status ON purchases.purchase_matching_lines(status);
|
|
|
|
-- =====================
|
|
-- TABLA: matching_exceptions
|
|
-- Excepciones de matching
|
|
-- =====================
|
|
CREATE TABLE IF NOT EXISTS purchases.matching_exceptions (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
|
|
matching_id UUID REFERENCES purchases.purchase_order_matching(id) ON DELETE CASCADE,
|
|
matching_line_id UUID REFERENCES purchases.purchase_matching_lines(id) ON DELETE CASCADE,
|
|
|
|
-- Tipo de excepcion
|
|
exception_type VARCHAR(50) NOT NULL, -- over_receipt, short_receipt, over_invoice, short_invoice, price_variance
|
|
|
|
-- Valores
|
|
expected_value DECIMAL(15, 4),
|
|
actual_value DECIMAL(15, 4),
|
|
variance_value DECIMAL(15, 4),
|
|
variance_percent DECIMAL(5, 2),
|
|
|
|
-- Resolucion
|
|
status VARCHAR(20) DEFAULT 'pending', -- pending, approved, rejected
|
|
resolved_at TIMESTAMPTZ,
|
|
resolved_by UUID REFERENCES auth.users(id),
|
|
resolution_notes TEXT,
|
|
|
|
-- Metadata
|
|
created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- Indices para matching_exceptions
|
|
CREATE INDEX IF NOT EXISTS idx_matching_exceptions_tenant ON purchases.matching_exceptions(tenant_id);
|
|
CREATE INDEX IF NOT EXISTS idx_matching_exceptions_matching ON purchases.matching_exceptions(matching_id);
|
|
CREATE INDEX IF NOT EXISTS idx_matching_exceptions_line ON purchases.matching_exceptions(matching_line_id);
|
|
CREATE INDEX IF NOT EXISTS idx_matching_exceptions_type ON purchases.matching_exceptions(exception_type);
|
|
CREATE INDEX IF NOT EXISTS idx_matching_exceptions_status ON purchases.matching_exceptions(status);
|
|
|
|
-- =====================
|
|
-- COMENTARIOS
|
|
-- =====================
|
|
COMMENT ON TABLE purchases.purchase_order_matching IS 'Registro de 3-way matching por orden de compra (PO-Receipt-Invoice)';
|
|
COMMENT ON COLUMN purchases.purchase_order_matching.status IS 'Estado: pending, partial_receipt, received, partial_invoice, matched, mismatch';
|
|
COMMENT ON COLUMN purchases.purchase_order_matching.receipt_variance IS 'Varianza = total_ordered - total_received (columna generada)';
|
|
COMMENT ON COLUMN purchases.purchase_order_matching.invoice_variance IS 'Varianza = total_received - total_invoiced (columna generada)';
|
|
|
|
COMMENT ON TABLE purchases.purchase_matching_lines IS 'Matching por linea de detalle de orden de compra';
|
|
COMMENT ON COLUMN purchases.purchase_matching_lines.qty_variance IS 'Varianza de cantidad = qty_ordered - qty_received (columna generada)';
|
|
COMMENT ON COLUMN purchases.purchase_matching_lines.invoice_qty_variance IS 'Varianza de factura = qty_received - qty_invoiced (columna generada)';
|
|
COMMENT ON COLUMN purchases.purchase_matching_lines.price_variance IS 'Varianza de precio = price_ordered - price_invoiced (columna generada)';
|
|
|
|
COMMENT ON TABLE purchases.matching_exceptions IS 'Excepciones detectadas durante el proceso de matching';
|
|
COMMENT ON COLUMN purchases.matching_exceptions.exception_type IS 'Tipo: over_receipt, short_receipt, over_invoice, short_invoice, price_variance';
|
|
COMMENT ON COLUMN purchases.matching_exceptions.status IS 'Estado: pending, approved, rejected';
|