rckrdmrd
4c4e27d9ba
🤖 Generated with [Claude Code](https://claude.com/claude-code) Co-Authored-By: Claude Opus 4.5 <noreply@anthropic.com>
1386 lines
52 KiB
PL/PgSQL
1386 lines
52 KiB
PL/PgSQL
-- =====================================================
|
|
-- SCHEMA: financial
|
|
-- PROPÓSITO: Contabilidad, facturas, pagos, finanzas
|
|
-- MÓDULOS: MGN-004 (Financiero Básico)
|
|
-- FECHA: 2025-11-24
|
|
-- =====================================================
|
|
|
|
-- Crear schema
|
|
CREATE SCHEMA IF NOT EXISTS financial;
|
|
|
|
-- =====================================================
|
|
-- TYPES (ENUMs)
|
|
-- =====================================================
|
|
|
|
CREATE TYPE financial.account_type AS ENUM (
|
|
'asset',
|
|
'liability',
|
|
'equity',
|
|
'revenue',
|
|
'expense'
|
|
);
|
|
|
|
CREATE TYPE financial.journal_type AS ENUM (
|
|
'sale',
|
|
'purchase',
|
|
'bank',
|
|
'cash',
|
|
'general'
|
|
);
|
|
|
|
CREATE TYPE financial.entry_status AS ENUM (
|
|
'draft',
|
|
'posted',
|
|
'cancelled'
|
|
);
|
|
|
|
CREATE TYPE financial.invoice_type AS ENUM (
|
|
'customer',
|
|
'supplier'
|
|
);
|
|
|
|
CREATE TYPE financial.invoice_status AS ENUM (
|
|
'draft',
|
|
'open',
|
|
'paid',
|
|
'cancelled'
|
|
);
|
|
|
|
CREATE TYPE financial.payment_type AS ENUM (
|
|
'inbound',
|
|
'outbound'
|
|
);
|
|
|
|
CREATE TYPE financial.payment_method AS ENUM (
|
|
'cash',
|
|
'bank_transfer',
|
|
'check',
|
|
'card',
|
|
'other'
|
|
);
|
|
|
|
CREATE TYPE financial.payment_status AS ENUM (
|
|
'draft',
|
|
'posted',
|
|
'reconciled',
|
|
'cancelled'
|
|
);
|
|
|
|
CREATE TYPE financial.tax_type AS ENUM (
|
|
'sales',
|
|
'purchase',
|
|
'all'
|
|
);
|
|
|
|
CREATE TYPE financial.fiscal_period_status AS ENUM (
|
|
'open',
|
|
'closed'
|
|
);
|
|
|
|
-- COR-004: Estado de pago separado del estado contable (Odoo alignment)
|
|
CREATE TYPE financial.payment_state AS ENUM (
|
|
'not_paid',
|
|
'in_payment',
|
|
'paid',
|
|
'partial',
|
|
'reversed'
|
|
);
|
|
|
|
-- =====================================================
|
|
-- TABLES
|
|
-- =====================================================
|
|
|
|
-- Tabla: account_types (Tipos de cuenta contable)
|
|
CREATE TABLE financial.account_types (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
code VARCHAR(20) NOT NULL UNIQUE,
|
|
name VARCHAR(100) NOT NULL,
|
|
account_type financial.account_type NOT NULL,
|
|
description TEXT,
|
|
|
|
-- Sin tenant_id: catálogo global
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- Tabla: accounts (Plan de cuentas)
|
|
CREATE TABLE financial.accounts (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
|
|
company_id UUID NOT NULL REFERENCES auth.companies(id) ON DELETE CASCADE,
|
|
|
|
code VARCHAR(50) NOT NULL,
|
|
name VARCHAR(255) NOT NULL,
|
|
account_type_id UUID NOT NULL REFERENCES financial.account_types(id),
|
|
parent_id UUID REFERENCES financial.accounts(id),
|
|
|
|
-- Configuración
|
|
currency_id UUID REFERENCES core.currencies(id),
|
|
is_reconcilable BOOLEAN DEFAULT FALSE, -- ¿Permite conciliación?
|
|
is_deprecated BOOLEAN DEFAULT FALSE,
|
|
|
|
-- Notas
|
|
notes TEXT,
|
|
|
|
-- Auditoría
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
created_by UUID REFERENCES auth.users(id),
|
|
updated_at TIMESTAMP,
|
|
updated_by UUID REFERENCES auth.users(id),
|
|
deleted_at TIMESTAMP,
|
|
deleted_by UUID REFERENCES auth.users(id),
|
|
|
|
CONSTRAINT uq_accounts_code_company UNIQUE (company_id, code),
|
|
CONSTRAINT chk_accounts_no_self_parent CHECK (id != parent_id)
|
|
);
|
|
|
|
-- Tabla: journals (Diarios contables)
|
|
CREATE TABLE financial.journals (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
|
|
company_id UUID NOT NULL REFERENCES auth.companies(id) ON DELETE CASCADE,
|
|
|
|
name VARCHAR(255) NOT NULL,
|
|
code VARCHAR(20) NOT NULL,
|
|
journal_type financial.journal_type NOT NULL,
|
|
|
|
-- Configuración
|
|
default_account_id UUID REFERENCES financial.accounts(id),
|
|
sequence_id UUID REFERENCES core.sequences(id),
|
|
currency_id UUID REFERENCES core.currencies(id),
|
|
|
|
-- Control
|
|
active BOOLEAN NOT NULL DEFAULT TRUE,
|
|
|
|
-- Auditoría
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
created_by UUID REFERENCES auth.users(id),
|
|
updated_at TIMESTAMP,
|
|
updated_by UUID REFERENCES auth.users(id),
|
|
deleted_at TIMESTAMP,
|
|
deleted_by UUID REFERENCES auth.users(id),
|
|
|
|
CONSTRAINT uq_journals_code_company UNIQUE (company_id, code)
|
|
);
|
|
|
|
-- Tabla: fiscal_years (Años fiscales)
|
|
CREATE TABLE financial.fiscal_years (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
|
|
company_id UUID NOT NULL REFERENCES auth.companies(id) ON DELETE CASCADE,
|
|
|
|
name VARCHAR(100) NOT NULL,
|
|
code VARCHAR(20) NOT NULL,
|
|
start_date DATE NOT NULL,
|
|
end_date DATE NOT NULL,
|
|
status financial.fiscal_period_status NOT NULL DEFAULT 'open',
|
|
|
|
-- Auditoría
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
created_by UUID REFERENCES auth.users(id),
|
|
updated_at TIMESTAMP,
|
|
updated_by UUID REFERENCES auth.users(id),
|
|
|
|
CONSTRAINT uq_fiscal_years_code_company UNIQUE (company_id, code),
|
|
CONSTRAINT chk_fiscal_years_dates CHECK (end_date > start_date)
|
|
);
|
|
|
|
-- Tabla: fiscal_periods (Períodos fiscales - meses)
|
|
CREATE TABLE financial.fiscal_periods (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
|
|
fiscal_year_id UUID NOT NULL REFERENCES financial.fiscal_years(id) ON DELETE CASCADE,
|
|
|
|
name VARCHAR(100) NOT NULL,
|
|
code VARCHAR(20) NOT NULL,
|
|
start_date DATE NOT NULL,
|
|
end_date DATE NOT NULL,
|
|
status financial.fiscal_period_status NOT NULL DEFAULT 'open',
|
|
|
|
-- Auditoría
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
created_by UUID REFERENCES auth.users(id),
|
|
updated_at TIMESTAMP,
|
|
updated_by UUID REFERENCES auth.users(id),
|
|
|
|
CONSTRAINT uq_fiscal_periods_code_year UNIQUE (fiscal_year_id, code),
|
|
CONSTRAINT chk_fiscal_periods_dates CHECK (end_date > start_date)
|
|
);
|
|
|
|
-- Tabla: journal_entries (Asientos contables)
|
|
CREATE TABLE financial.journal_entries (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
|
|
company_id UUID NOT NULL REFERENCES auth.companies(id) ON DELETE CASCADE,
|
|
|
|
journal_id UUID NOT NULL REFERENCES financial.journals(id),
|
|
name VARCHAR(100) NOT NULL, -- Número de asiento
|
|
ref VARCHAR(255), -- Referencia externa
|
|
date DATE NOT NULL,
|
|
status financial.entry_status NOT NULL DEFAULT 'draft',
|
|
|
|
-- Metadatos
|
|
notes TEXT,
|
|
|
|
-- Auditoría
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
created_by UUID REFERENCES auth.users(id),
|
|
updated_at TIMESTAMP,
|
|
updated_by UUID REFERENCES auth.users(id),
|
|
posted_at TIMESTAMP,
|
|
posted_by UUID REFERENCES auth.users(id),
|
|
cancelled_at TIMESTAMP,
|
|
cancelled_by UUID REFERENCES auth.users(id),
|
|
|
|
CONSTRAINT uq_journal_entries_name_journal UNIQUE (journal_id, name)
|
|
);
|
|
|
|
-- Tabla: journal_entry_lines (Líneas de asiento contable)
|
|
CREATE TABLE financial.journal_entry_lines (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
|
|
entry_id UUID NOT NULL REFERENCES financial.journal_entries(id) ON DELETE CASCADE,
|
|
|
|
account_id UUID NOT NULL REFERENCES financial.accounts(id),
|
|
partner_id UUID REFERENCES core.partners(id),
|
|
|
|
-- Montos
|
|
debit DECIMAL(15, 2) NOT NULL DEFAULT 0,
|
|
credit DECIMAL(15, 2) NOT NULL DEFAULT 0,
|
|
|
|
-- Analítica
|
|
analytic_account_id UUID, -- FK a analytics.analytic_accounts (se crea después)
|
|
|
|
-- Descripción
|
|
description TEXT,
|
|
ref VARCHAR(255),
|
|
|
|
-- Multi-moneda
|
|
currency_id UUID REFERENCES core.currencies(id),
|
|
amount_currency DECIMAL(15, 2),
|
|
|
|
-- Auditoría
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT chk_journal_lines_debit_positive CHECK (debit >= 0),
|
|
CONSTRAINT chk_journal_lines_credit_positive CHECK (credit >= 0),
|
|
CONSTRAINT chk_journal_lines_not_both CHECK (
|
|
(debit > 0 AND credit = 0) OR (credit > 0 AND debit = 0)
|
|
)
|
|
);
|
|
|
|
-- Índices para journal_entry_lines
|
|
CREATE INDEX idx_journal_entry_lines_tenant_id ON financial.journal_entry_lines(tenant_id);
|
|
CREATE INDEX idx_journal_entry_lines_entry_id ON financial.journal_entry_lines(entry_id);
|
|
CREATE INDEX idx_journal_entry_lines_account_id ON financial.journal_entry_lines(account_id);
|
|
|
|
-- RLS para journal_entry_lines
|
|
ALTER TABLE financial.journal_entry_lines ENABLE ROW LEVEL SECURITY;
|
|
CREATE POLICY tenant_isolation_journal_entry_lines ON financial.journal_entry_lines
|
|
USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);
|
|
|
|
-- =====================================================
|
|
-- COR-005: Tabla tax_groups (Grupos de impuestos)
|
|
-- Equivalente a account.tax.group en Odoo
|
|
-- =====================================================
|
|
CREATE TABLE financial.tax_groups (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
|
|
|
|
name VARCHAR(100) NOT NULL,
|
|
sequence INTEGER DEFAULT 10,
|
|
country_id UUID, -- Futuro: countries table
|
|
|
|
-- Auditoría
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
created_by UUID REFERENCES auth.users(id),
|
|
|
|
CONSTRAINT uq_tax_groups_name_tenant UNIQUE (tenant_id, name)
|
|
);
|
|
|
|
COMMENT ON TABLE financial.tax_groups IS
|
|
'COR-005: Grupos de impuestos para clasificación y reporte (equivalente a account.tax.group Odoo)';
|
|
|
|
-- Tabla: taxes (Impuestos)
|
|
CREATE TABLE financial.taxes (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
|
|
company_id UUID NOT NULL REFERENCES auth.companies(id) ON DELETE CASCADE,
|
|
|
|
name VARCHAR(100) NOT NULL,
|
|
code VARCHAR(20) NOT NULL,
|
|
rate DECIMAL(5, 4) NOT NULL, -- 0.1600 para 16%
|
|
tax_type financial.tax_type NOT NULL,
|
|
|
|
-- COR-005: Grupo de impuestos
|
|
tax_group_id UUID REFERENCES financial.tax_groups(id),
|
|
|
|
-- COR-005: Tipo de cálculo
|
|
amount_type VARCHAR(20) DEFAULT 'percent', -- percent, fixed, group, division
|
|
include_base_amount BOOLEAN DEFAULT FALSE,
|
|
price_include BOOLEAN DEFAULT FALSE,
|
|
children_tax_ids UUID[] DEFAULT '{}', -- Para impuestos compuestos
|
|
|
|
-- Configuración contable
|
|
account_id UUID REFERENCES financial.accounts(id),
|
|
refund_account_id UUID REFERENCES financial.accounts(id), -- COR-005: Cuenta para devoluciones
|
|
|
|
-- Control
|
|
active BOOLEAN NOT NULL DEFAULT TRUE,
|
|
|
|
-- Auditoría
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
created_by UUID REFERENCES auth.users(id),
|
|
updated_at TIMESTAMP,
|
|
updated_by UUID REFERENCES auth.users(id),
|
|
|
|
CONSTRAINT uq_taxes_code_company UNIQUE (company_id, code),
|
|
CONSTRAINT chk_taxes_rate CHECK (rate >= 0 AND rate <= 1),
|
|
CONSTRAINT chk_taxes_amount_type CHECK (amount_type IN ('percent', 'fixed', 'group', 'division'))
|
|
);
|
|
|
|
-- Tabla: payment_terms (Términos de pago)
|
|
CREATE TABLE financial.payment_terms (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
|
|
company_id UUID NOT NULL REFERENCES auth.companies(id) ON DELETE CASCADE,
|
|
|
|
name VARCHAR(100) NOT NULL,
|
|
code VARCHAR(20) NOT NULL,
|
|
|
|
-- Configuración de términos (JSON)
|
|
-- Ejemplo: [{"days": 0, "percent": 100}] = Pago inmediato
|
|
-- Ejemplo: [{"days": 30, "percent": 100}] = 30 días
|
|
-- Ejemplo: [{"days": 15, "percent": 50}, {"days": 30, "percent": 50}] = 50% a 15 días, 50% a 30 días
|
|
terms JSONB NOT NULL DEFAULT '[]',
|
|
|
|
-- Control
|
|
active BOOLEAN NOT NULL DEFAULT TRUE,
|
|
|
|
-- Auditoría
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
created_by UUID REFERENCES auth.users(id),
|
|
updated_at TIMESTAMP,
|
|
updated_by UUID REFERENCES auth.users(id),
|
|
|
|
CONSTRAINT uq_payment_terms_code_company UNIQUE (company_id, code)
|
|
);
|
|
|
|
-- Tabla: invoices (Facturas)
|
|
CREATE TABLE financial.invoices (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
|
|
company_id UUID NOT NULL REFERENCES auth.companies(id) ON DELETE CASCADE,
|
|
|
|
partner_id UUID NOT NULL REFERENCES core.partners(id),
|
|
invoice_type financial.invoice_type NOT NULL,
|
|
|
|
-- Numeración
|
|
number VARCHAR(100), -- Número de factura (generado al validar)
|
|
ref VARCHAR(100), -- Referencia del partner
|
|
|
|
-- Fechas
|
|
invoice_date DATE NOT NULL,
|
|
due_date DATE,
|
|
|
|
-- Montos
|
|
currency_id UUID NOT NULL REFERENCES core.currencies(id),
|
|
amount_untaxed DECIMAL(15, 2) NOT NULL DEFAULT 0,
|
|
amount_tax DECIMAL(15, 2) NOT NULL DEFAULT 0,
|
|
amount_total DECIMAL(15, 2) NOT NULL DEFAULT 0,
|
|
amount_paid DECIMAL(15, 2) NOT NULL DEFAULT 0,
|
|
amount_residual DECIMAL(15, 2) NOT NULL DEFAULT 0,
|
|
|
|
-- Estado
|
|
status financial.invoice_status NOT NULL DEFAULT 'draft',
|
|
|
|
-- COR-004: Estado de pago separado (Odoo alignment)
|
|
payment_state financial.payment_state DEFAULT 'not_paid',
|
|
|
|
-- Configuración
|
|
payment_term_id UUID REFERENCES financial.payment_terms(id),
|
|
journal_id UUID REFERENCES financial.journals(id),
|
|
|
|
-- Asiento contable (generado al validar)
|
|
journal_entry_id UUID REFERENCES financial.journal_entries(id),
|
|
|
|
-- Notas
|
|
notes TEXT,
|
|
|
|
-- Auditoría
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
created_by UUID REFERENCES auth.users(id),
|
|
updated_at TIMESTAMP,
|
|
updated_by UUID REFERENCES auth.users(id),
|
|
validated_at TIMESTAMP,
|
|
validated_by UUID REFERENCES auth.users(id),
|
|
cancelled_at TIMESTAMP,
|
|
cancelled_by UUID REFERENCES auth.users(id),
|
|
|
|
CONSTRAINT uq_invoices_number_company UNIQUE (company_id, number),
|
|
CONSTRAINT chk_invoices_amounts CHECK (
|
|
amount_total = amount_untaxed + amount_tax
|
|
),
|
|
CONSTRAINT chk_invoices_residual CHECK (
|
|
amount_residual = amount_total - amount_paid
|
|
)
|
|
);
|
|
|
|
-- Tabla: invoice_lines (Líneas de factura)
|
|
CREATE TABLE financial.invoice_lines (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
|
|
invoice_id UUID NOT NULL REFERENCES financial.invoices(id) ON DELETE CASCADE,
|
|
|
|
product_id UUID, -- FK a inventory.products (se crea después)
|
|
description TEXT NOT NULL,
|
|
|
|
-- Cantidades y precios
|
|
quantity DECIMAL(12, 4) NOT NULL DEFAULT 1,
|
|
uom_id UUID REFERENCES core.uom(id),
|
|
price_unit DECIMAL(15, 4) NOT NULL,
|
|
|
|
-- Impuestos (array de tax_ids)
|
|
tax_ids UUID[] DEFAULT '{}',
|
|
|
|
-- Montos calculados
|
|
amount_untaxed DECIMAL(15, 2) NOT NULL,
|
|
amount_tax DECIMAL(15, 2) NOT NULL,
|
|
amount_total DECIMAL(15, 2) NOT NULL,
|
|
|
|
-- Contabilidad
|
|
account_id UUID REFERENCES financial.accounts(id),
|
|
analytic_account_id UUID, -- FK a analytics.analytic_accounts
|
|
|
|
-- Auditoría
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP,
|
|
|
|
CONSTRAINT chk_invoice_lines_quantity CHECK (quantity > 0),
|
|
CONSTRAINT chk_invoice_lines_amounts CHECK (
|
|
amount_total = amount_untaxed + amount_tax
|
|
)
|
|
);
|
|
|
|
-- Índices para invoice_lines
|
|
CREATE INDEX idx_invoice_lines_tenant_id ON financial.invoice_lines(tenant_id);
|
|
CREATE INDEX idx_invoice_lines_invoice_id ON financial.invoice_lines(invoice_id);
|
|
CREATE INDEX idx_invoice_lines_product_id ON financial.invoice_lines(product_id);
|
|
|
|
-- RLS para invoice_lines
|
|
ALTER TABLE financial.invoice_lines ENABLE ROW LEVEL SECURITY;
|
|
CREATE POLICY tenant_isolation_invoice_lines ON financial.invoice_lines
|
|
USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);
|
|
|
|
-- Tabla: payments (Pagos)
|
|
CREATE TABLE financial.payments (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
|
|
company_id UUID NOT NULL REFERENCES auth.companies(id) ON DELETE CASCADE,
|
|
|
|
partner_id UUID NOT NULL REFERENCES core.partners(id),
|
|
payment_type financial.payment_type NOT NULL,
|
|
payment_method financial.payment_method NOT NULL,
|
|
|
|
-- Monto
|
|
amount DECIMAL(15, 2) NOT NULL,
|
|
currency_id UUID NOT NULL REFERENCES core.currencies(id),
|
|
|
|
-- Fecha y referencia
|
|
payment_date DATE NOT NULL,
|
|
ref VARCHAR(255),
|
|
|
|
-- Estado
|
|
status financial.payment_status NOT NULL DEFAULT 'draft',
|
|
|
|
-- Configuración
|
|
journal_id UUID NOT NULL REFERENCES financial.journals(id),
|
|
|
|
-- Asiento contable (generado al validar)
|
|
journal_entry_id UUID REFERENCES financial.journal_entries(id),
|
|
|
|
-- Notas
|
|
notes TEXT,
|
|
|
|
-- Auditoría
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
created_by UUID REFERENCES auth.users(id),
|
|
updated_at TIMESTAMP,
|
|
updated_by UUID REFERENCES auth.users(id),
|
|
posted_at TIMESTAMP,
|
|
posted_by UUID REFERENCES auth.users(id),
|
|
|
|
CONSTRAINT chk_payments_amount CHECK (amount > 0)
|
|
);
|
|
|
|
-- Tabla: payment_invoice (Conciliación pagos-facturas)
|
|
CREATE TABLE financial.payment_invoice (
|
|
payment_id UUID NOT NULL REFERENCES financial.payments(id) ON DELETE CASCADE,
|
|
invoice_id UUID NOT NULL REFERENCES financial.invoices(id) ON DELETE CASCADE,
|
|
amount DECIMAL(15, 2) NOT NULL,
|
|
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
PRIMARY KEY (payment_id, invoice_id),
|
|
CONSTRAINT chk_payment_invoice_amount CHECK (amount > 0)
|
|
);
|
|
|
|
-- Tabla: bank_accounts (Cuentas bancarias)
|
|
CREATE TABLE financial.bank_accounts (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
|
|
company_id UUID REFERENCES auth.companies(id),
|
|
|
|
partner_id UUID REFERENCES core.partners(id), -- Puede ser de la empresa o de un partner
|
|
|
|
bank_name VARCHAR(255) NOT NULL,
|
|
account_number VARCHAR(50) NOT NULL,
|
|
account_holder VARCHAR(255),
|
|
|
|
-- Configuración
|
|
currency_id UUID REFERENCES core.currencies(id),
|
|
journal_id UUID REFERENCES financial.journals(id), -- Diario asociado (si es cuenta de la empresa)
|
|
|
|
-- Control
|
|
active BOOLEAN NOT NULL DEFAULT TRUE,
|
|
|
|
-- Auditoría
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
created_by UUID REFERENCES auth.users(id),
|
|
updated_at TIMESTAMP,
|
|
updated_by UUID REFERENCES auth.users(id)
|
|
);
|
|
|
|
-- Tabla: reconciliations (Conciliaciones bancarias)
|
|
CREATE TABLE financial.reconciliations (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
|
|
company_id UUID NOT NULL REFERENCES auth.companies(id) ON DELETE CASCADE,
|
|
|
|
bank_account_id UUID NOT NULL REFERENCES financial.bank_accounts(id),
|
|
|
|
-- Período de conciliación
|
|
start_date DATE NOT NULL,
|
|
end_date DATE NOT NULL,
|
|
|
|
-- Saldos
|
|
balance_start DECIMAL(15, 2) NOT NULL,
|
|
balance_end_real DECIMAL(15, 2) NOT NULL, -- Saldo real del banco
|
|
balance_end_computed DECIMAL(15, 2) NOT NULL, -- Saldo calculado
|
|
|
|
-- Líneas conciliadas (array de journal_entry_line_ids)
|
|
reconciled_line_ids UUID[] DEFAULT '{}',
|
|
|
|
-- Estado
|
|
status financial.entry_status NOT NULL DEFAULT 'draft',
|
|
|
|
-- Auditoría
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
created_by UUID REFERENCES auth.users(id),
|
|
updated_at TIMESTAMP,
|
|
updated_by UUID REFERENCES auth.users(id),
|
|
validated_at TIMESTAMP,
|
|
validated_by UUID REFERENCES auth.users(id),
|
|
|
|
CONSTRAINT chk_reconciliations_dates CHECK (end_date >= start_date)
|
|
);
|
|
|
|
-- =====================================================
|
|
-- COR-013: Tablas de Conciliación (Reconciliation Engine)
|
|
-- Equivalente a account.partial.reconcile y account.full.reconcile en Odoo
|
|
-- =====================================================
|
|
|
|
-- Tabla: account_full_reconcile (Conciliación completa)
|
|
CREATE TABLE financial.account_full_reconcile (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
|
|
|
|
name VARCHAR(100) NOT NULL,
|
|
exchange_move_id UUID REFERENCES financial.journal_entries(id),
|
|
|
|
-- Auditoría
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
created_by UUID REFERENCES auth.users(id)
|
|
);
|
|
|
|
-- Tabla: account_partial_reconcile (Conciliación parcial)
|
|
CREATE TABLE financial.account_partial_reconcile (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
|
|
|
|
-- Líneas a conciliar
|
|
debit_move_id UUID NOT NULL REFERENCES financial.journal_entry_lines(id),
|
|
credit_move_id UUID NOT NULL REFERENCES financial.journal_entry_lines(id),
|
|
|
|
-- Montos
|
|
amount DECIMAL(15, 2) NOT NULL,
|
|
debit_amount_currency DECIMAL(15, 2),
|
|
credit_amount_currency DECIMAL(15, 2),
|
|
|
|
-- Moneda
|
|
company_currency_id UUID REFERENCES core.currencies(id),
|
|
debit_currency_id UUID REFERENCES core.currencies(id),
|
|
credit_currency_id UUID REFERENCES core.currencies(id),
|
|
|
|
-- Conciliación completa
|
|
full_reconcile_id UUID REFERENCES financial.account_full_reconcile(id),
|
|
|
|
-- Fecha máxima
|
|
max_date DATE,
|
|
|
|
-- Auditoría
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
created_by UUID REFERENCES auth.users(id),
|
|
|
|
CONSTRAINT chk_partial_reconcile_amount CHECK (amount > 0),
|
|
CONSTRAINT chk_partial_reconcile_different_lines CHECK (debit_move_id != credit_move_id)
|
|
);
|
|
|
|
COMMENT ON TABLE financial.account_full_reconcile IS
|
|
'COR-013: Conciliación completa - agrupa partial reconciles cuando las líneas están 100% conciliadas';
|
|
COMMENT ON TABLE financial.account_partial_reconcile IS
|
|
'COR-013: Conciliación parcial - vincula líneas de débito y crédito con el monto conciliado';
|
|
|
|
-- Agregar campos de reconciliación a journal_entry_lines
|
|
-- (Nota: En producción, esto sería ALTER TABLE)
|
|
|
|
-- =====================================================
|
|
-- INDICES
|
|
-- =====================================================
|
|
|
|
-- Account Types
|
|
CREATE INDEX idx_account_types_code ON financial.account_types(code);
|
|
|
|
-- Accounts
|
|
CREATE INDEX idx_accounts_tenant_id ON financial.accounts(tenant_id);
|
|
CREATE INDEX idx_accounts_company_id ON financial.accounts(company_id);
|
|
CREATE INDEX idx_accounts_code ON financial.accounts(code);
|
|
CREATE INDEX idx_accounts_parent_id ON financial.accounts(parent_id);
|
|
CREATE INDEX idx_accounts_type_id ON financial.accounts(account_type_id);
|
|
|
|
-- Journals
|
|
CREATE INDEX idx_journals_tenant_id ON financial.journals(tenant_id);
|
|
CREATE INDEX idx_journals_company_id ON financial.journals(company_id);
|
|
CREATE INDEX idx_journals_code ON financial.journals(code);
|
|
CREATE INDEX idx_journals_type ON financial.journals(journal_type);
|
|
|
|
-- Fiscal Years
|
|
CREATE INDEX idx_fiscal_years_tenant_id ON financial.fiscal_years(tenant_id);
|
|
CREATE INDEX idx_fiscal_years_company_id ON financial.fiscal_years(company_id);
|
|
CREATE INDEX idx_fiscal_years_dates ON financial.fiscal_years(start_date, end_date);
|
|
|
|
-- Fiscal Periods
|
|
CREATE INDEX idx_fiscal_periods_tenant_id ON financial.fiscal_periods(tenant_id);
|
|
CREATE INDEX idx_fiscal_periods_year_id ON financial.fiscal_periods(fiscal_year_id);
|
|
CREATE INDEX idx_fiscal_periods_dates ON financial.fiscal_periods(start_date, end_date);
|
|
|
|
-- Journal Entries
|
|
CREATE INDEX idx_journal_entries_tenant_id ON financial.journal_entries(tenant_id);
|
|
CREATE INDEX idx_journal_entries_company_id ON financial.journal_entries(company_id);
|
|
CREATE INDEX idx_journal_entries_journal_id ON financial.journal_entries(journal_id);
|
|
CREATE INDEX idx_journal_entries_date ON financial.journal_entries(date);
|
|
CREATE INDEX idx_journal_entries_status ON financial.journal_entries(status);
|
|
|
|
-- Journal Entry Lines
|
|
CREATE INDEX idx_journal_entry_lines_entry_id ON financial.journal_entry_lines(entry_id);
|
|
CREATE INDEX idx_journal_entry_lines_account_id ON financial.journal_entry_lines(account_id);
|
|
CREATE INDEX idx_journal_entry_lines_partner_id ON financial.journal_entry_lines(partner_id);
|
|
CREATE INDEX idx_journal_entry_lines_analytic ON financial.journal_entry_lines(analytic_account_id);
|
|
|
|
-- Taxes
|
|
CREATE INDEX idx_taxes_tenant_id ON financial.taxes(tenant_id);
|
|
CREATE INDEX idx_taxes_company_id ON financial.taxes(company_id);
|
|
CREATE INDEX idx_taxes_code ON financial.taxes(code);
|
|
CREATE INDEX idx_taxes_type ON financial.taxes(tax_type);
|
|
CREATE INDEX idx_taxes_active ON financial.taxes(active) WHERE active = TRUE;
|
|
|
|
-- Payment Terms
|
|
CREATE INDEX idx_payment_terms_tenant_id ON financial.payment_terms(tenant_id);
|
|
CREATE INDEX idx_payment_terms_company_id ON financial.payment_terms(company_id);
|
|
|
|
-- Invoices
|
|
CREATE INDEX idx_invoices_tenant_id ON financial.invoices(tenant_id);
|
|
CREATE INDEX idx_invoices_company_id ON financial.invoices(company_id);
|
|
CREATE INDEX idx_invoices_partner_id ON financial.invoices(partner_id);
|
|
CREATE INDEX idx_invoices_type ON financial.invoices(invoice_type);
|
|
CREATE INDEX idx_invoices_status ON financial.invoices(status);
|
|
CREATE INDEX idx_invoices_number ON financial.invoices(number);
|
|
CREATE INDEX idx_invoices_date ON financial.invoices(invoice_date);
|
|
CREATE INDEX idx_invoices_due_date ON financial.invoices(due_date);
|
|
|
|
-- Invoice Lines
|
|
CREATE INDEX idx_invoice_lines_invoice_id ON financial.invoice_lines(invoice_id);
|
|
CREATE INDEX idx_invoice_lines_product_id ON financial.invoice_lines(product_id);
|
|
CREATE INDEX idx_invoice_lines_account_id ON financial.invoice_lines(account_id);
|
|
|
|
-- Payments
|
|
CREATE INDEX idx_payments_tenant_id ON financial.payments(tenant_id);
|
|
CREATE INDEX idx_payments_company_id ON financial.payments(company_id);
|
|
CREATE INDEX idx_payments_partner_id ON financial.payments(partner_id);
|
|
CREATE INDEX idx_payments_type ON financial.payments(payment_type);
|
|
CREATE INDEX idx_payments_status ON financial.payments(status);
|
|
CREATE INDEX idx_payments_date ON financial.payments(payment_date);
|
|
|
|
-- Payment Invoice
|
|
CREATE INDEX idx_payment_invoice_payment_id ON financial.payment_invoice(payment_id);
|
|
CREATE INDEX idx_payment_invoice_invoice_id ON financial.payment_invoice(invoice_id);
|
|
|
|
-- Bank Accounts
|
|
CREATE INDEX idx_bank_accounts_tenant_id ON financial.bank_accounts(tenant_id);
|
|
CREATE INDEX idx_bank_accounts_company_id ON financial.bank_accounts(company_id);
|
|
CREATE INDEX idx_bank_accounts_partner_id ON financial.bank_accounts(partner_id);
|
|
|
|
-- Reconciliations
|
|
CREATE INDEX idx_reconciliations_tenant_id ON financial.reconciliations(tenant_id);
|
|
CREATE INDEX idx_reconciliations_company_id ON financial.reconciliations(company_id);
|
|
CREATE INDEX idx_reconciliations_bank_account_id ON financial.reconciliations(bank_account_id);
|
|
CREATE INDEX idx_reconciliations_dates ON financial.reconciliations(start_date, end_date);
|
|
|
|
-- =====================================================
|
|
-- FUNCTIONS
|
|
-- =====================================================
|
|
|
|
-- Función: validate_entry_balance
|
|
-- Valida que un asiento esté balanceado (debit = credit)
|
|
CREATE OR REPLACE FUNCTION financial.validate_entry_balance(p_entry_id UUID)
|
|
RETURNS BOOLEAN AS $$
|
|
DECLARE
|
|
v_total_debit DECIMAL;
|
|
v_total_credit DECIMAL;
|
|
BEGIN
|
|
SELECT
|
|
COALESCE(SUM(debit), 0),
|
|
COALESCE(SUM(credit), 0)
|
|
INTO v_total_debit, v_total_credit
|
|
FROM financial.journal_entry_lines
|
|
WHERE entry_id = p_entry_id;
|
|
|
|
IF v_total_debit != v_total_credit THEN
|
|
RAISE EXCEPTION 'Journal entry % is not balanced: debit=% credit=%',
|
|
p_entry_id, v_total_debit, v_total_credit;
|
|
END IF;
|
|
|
|
RETURN TRUE;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
COMMENT ON FUNCTION financial.validate_entry_balance IS 'Valida que un asiento contable esté balanceado (debit = credit)';
|
|
|
|
-- Función: post_journal_entry
|
|
-- Contabiliza un asiento (cambiar estado a posted)
|
|
CREATE OR REPLACE FUNCTION financial.post_journal_entry(p_entry_id UUID)
|
|
RETURNS VOID AS $$
|
|
BEGIN
|
|
-- Validar balance
|
|
PERFORM financial.validate_entry_balance(p_entry_id);
|
|
|
|
-- Actualizar estado
|
|
UPDATE financial.journal_entries
|
|
SET status = 'posted',
|
|
posted_at = CURRENT_TIMESTAMP,
|
|
posted_by = get_current_user_id()
|
|
WHERE id = p_entry_id
|
|
AND status = 'draft';
|
|
|
|
IF NOT FOUND THEN
|
|
RAISE EXCEPTION 'Journal entry % not found or already posted', p_entry_id;
|
|
END IF;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
COMMENT ON FUNCTION financial.post_journal_entry IS 'Contabiliza un asiento contable después de validar su balance';
|
|
|
|
-- Función: calculate_invoice_totals
|
|
-- Calcula los totales de una factura a partir de sus líneas
|
|
CREATE OR REPLACE FUNCTION financial.calculate_invoice_totals(p_invoice_id UUID)
|
|
RETURNS VOID AS $$
|
|
DECLARE
|
|
v_amount_untaxed DECIMAL;
|
|
v_amount_tax DECIMAL;
|
|
v_amount_total DECIMAL;
|
|
BEGIN
|
|
SELECT
|
|
COALESCE(SUM(amount_untaxed), 0),
|
|
COALESCE(SUM(amount_tax), 0),
|
|
COALESCE(SUM(amount_total), 0)
|
|
INTO v_amount_untaxed, v_amount_tax, v_amount_total
|
|
FROM financial.invoice_lines
|
|
WHERE invoice_id = p_invoice_id;
|
|
|
|
UPDATE financial.invoices
|
|
SET amount_untaxed = v_amount_untaxed,
|
|
amount_tax = v_amount_tax,
|
|
amount_total = v_amount_total,
|
|
amount_residual = v_amount_total - amount_paid,
|
|
updated_at = CURRENT_TIMESTAMP,
|
|
updated_by = get_current_user_id()
|
|
WHERE id = p_invoice_id;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
COMMENT ON FUNCTION financial.calculate_invoice_totals IS 'Calcula los totales de una factura a partir de sus líneas';
|
|
|
|
-- Función: update_invoice_paid_amount
|
|
-- Actualiza el monto pagado de una factura
|
|
CREATE OR REPLACE FUNCTION financial.update_invoice_paid_amount(p_invoice_id UUID)
|
|
RETURNS VOID AS $$
|
|
DECLARE
|
|
v_amount_paid DECIMAL;
|
|
BEGIN
|
|
SELECT COALESCE(SUM(amount), 0)
|
|
INTO v_amount_paid
|
|
FROM financial.payment_invoice
|
|
WHERE invoice_id = p_invoice_id;
|
|
|
|
UPDATE financial.invoices
|
|
SET amount_paid = v_amount_paid,
|
|
amount_residual = amount_total - v_amount_paid,
|
|
status = CASE
|
|
WHEN v_amount_paid >= amount_total THEN 'paid'::financial.invoice_status
|
|
WHEN v_amount_paid > 0 THEN 'open'::financial.invoice_status
|
|
ELSE status
|
|
END
|
|
WHERE id = p_invoice_id;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
COMMENT ON FUNCTION financial.update_invoice_paid_amount IS 'Actualiza el monto pagado y estado de una factura';
|
|
|
|
-- =====================================================
|
|
-- TRIGGERS
|
|
-- =====================================================
|
|
|
|
-- Trigger: Actualizar updated_at
|
|
CREATE TRIGGER trg_accounts_updated_at
|
|
BEFORE UPDATE ON financial.accounts
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION auth.update_updated_at_column();
|
|
|
|
CREATE TRIGGER trg_journals_updated_at
|
|
BEFORE UPDATE ON financial.journals
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION auth.update_updated_at_column();
|
|
|
|
CREATE TRIGGER trg_fiscal_years_updated_at
|
|
BEFORE UPDATE ON financial.fiscal_years
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION auth.update_updated_at_column();
|
|
|
|
CREATE TRIGGER trg_fiscal_periods_updated_at
|
|
BEFORE UPDATE ON financial.fiscal_periods
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION auth.update_updated_at_column();
|
|
|
|
CREATE TRIGGER trg_journal_entries_updated_at
|
|
BEFORE UPDATE ON financial.journal_entries
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION auth.update_updated_at_column();
|
|
|
|
CREATE TRIGGER trg_taxes_updated_at
|
|
BEFORE UPDATE ON financial.taxes
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION auth.update_updated_at_column();
|
|
|
|
CREATE TRIGGER trg_payment_terms_updated_at
|
|
BEFORE UPDATE ON financial.payment_terms
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION auth.update_updated_at_column();
|
|
|
|
CREATE TRIGGER trg_invoices_updated_at
|
|
BEFORE UPDATE ON financial.invoices
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION auth.update_updated_at_column();
|
|
|
|
CREATE TRIGGER trg_payments_updated_at
|
|
BEFORE UPDATE ON financial.payments
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION auth.update_updated_at_column();
|
|
|
|
CREATE TRIGGER trg_bank_accounts_updated_at
|
|
BEFORE UPDATE ON financial.bank_accounts
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION auth.update_updated_at_column();
|
|
|
|
CREATE TRIGGER trg_reconciliations_updated_at
|
|
BEFORE UPDATE ON financial.reconciliations
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION auth.update_updated_at_column();
|
|
|
|
-- Trigger: Validar balance antes de contabilizar
|
|
CREATE OR REPLACE FUNCTION financial.trg_validate_entry_before_post()
|
|
RETURNS TRIGGER AS $$
|
|
BEGIN
|
|
IF NEW.status = 'posted' AND OLD.status = 'draft' THEN
|
|
PERFORM financial.validate_entry_balance(NEW.id);
|
|
END IF;
|
|
RETURN NEW;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
CREATE TRIGGER trg_journal_entries_validate_balance
|
|
BEFORE UPDATE OF status ON financial.journal_entries
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION financial.trg_validate_entry_before_post();
|
|
|
|
-- Trigger: Actualizar totales de factura al cambiar líneas
|
|
CREATE OR REPLACE FUNCTION financial.trg_update_invoice_totals()
|
|
RETURNS TRIGGER AS $$
|
|
BEGIN
|
|
IF TG_OP = 'DELETE' THEN
|
|
PERFORM financial.calculate_invoice_totals(OLD.invoice_id);
|
|
ELSE
|
|
PERFORM financial.calculate_invoice_totals(NEW.invoice_id);
|
|
END IF;
|
|
RETURN NULL;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
CREATE TRIGGER trg_invoice_lines_update_totals
|
|
AFTER INSERT OR UPDATE OR DELETE ON financial.invoice_lines
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION financial.trg_update_invoice_totals();
|
|
|
|
-- Trigger: Actualizar monto pagado al conciliar
|
|
CREATE OR REPLACE FUNCTION financial.trg_update_invoice_paid()
|
|
RETURNS TRIGGER AS $$
|
|
BEGIN
|
|
IF TG_OP = 'DELETE' THEN
|
|
PERFORM financial.update_invoice_paid_amount(OLD.invoice_id);
|
|
ELSE
|
|
PERFORM financial.update_invoice_paid_amount(NEW.invoice_id);
|
|
END IF;
|
|
RETURN NULL;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
CREATE TRIGGER trg_payment_invoice_update_paid
|
|
AFTER INSERT OR UPDATE OR DELETE ON financial.payment_invoice
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION financial.trg_update_invoice_paid();
|
|
|
|
-- =====================================================
|
|
-- TRACKING AUTOMÁTICO (mail.thread pattern)
|
|
-- =====================================================
|
|
|
|
-- Trigger: Tracking automático para facturas
|
|
CREATE TRIGGER track_invoice_changes
|
|
AFTER INSERT OR UPDATE OR DELETE ON financial.invoices
|
|
FOR EACH ROW EXECUTE FUNCTION system.track_field_changes();
|
|
|
|
COMMENT ON TRIGGER track_invoice_changes ON financial.invoices IS
|
|
'Registra automáticamente cambios en facturas (estado, monto, cliente, fechas)';
|
|
|
|
-- Trigger: Tracking automático para asientos contables
|
|
CREATE TRIGGER track_journal_entry_changes
|
|
AFTER INSERT OR UPDATE OR DELETE ON financial.journal_entries
|
|
FOR EACH ROW EXECUTE FUNCTION system.track_field_changes();
|
|
|
|
COMMENT ON TRIGGER track_journal_entry_changes ON financial.journal_entries IS
|
|
'Registra automáticamente cambios en asientos contables (estado, fecha, diario)';
|
|
|
|
-- =====================================================
|
|
-- ROW LEVEL SECURITY (RLS)
|
|
-- =====================================================
|
|
|
|
ALTER TABLE financial.accounts ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE financial.journals ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE financial.fiscal_years ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE financial.fiscal_periods ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE financial.journal_entries ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE financial.taxes ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE financial.payment_terms ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE financial.invoices ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE financial.payments ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE financial.bank_accounts ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE financial.reconciliations ENABLE ROW LEVEL SECURITY;
|
|
|
|
CREATE POLICY tenant_isolation_accounts ON financial.accounts
|
|
USING (tenant_id = get_current_tenant_id());
|
|
|
|
CREATE POLICY tenant_isolation_journals ON financial.journals
|
|
USING (tenant_id = get_current_tenant_id());
|
|
|
|
CREATE POLICY tenant_isolation_fiscal_years ON financial.fiscal_years
|
|
USING (tenant_id = get_current_tenant_id());
|
|
|
|
CREATE POLICY tenant_isolation_fiscal_periods ON financial.fiscal_periods
|
|
USING (tenant_id = get_current_tenant_id());
|
|
|
|
CREATE POLICY tenant_isolation_journal_entries ON financial.journal_entries
|
|
USING (tenant_id = get_current_tenant_id());
|
|
|
|
CREATE POLICY tenant_isolation_taxes ON financial.taxes
|
|
USING (tenant_id = get_current_tenant_id());
|
|
|
|
CREATE POLICY tenant_isolation_payment_terms ON financial.payment_terms
|
|
USING (tenant_id = get_current_tenant_id());
|
|
|
|
CREATE POLICY tenant_isolation_invoices ON financial.invoices
|
|
USING (tenant_id = get_current_tenant_id());
|
|
|
|
CREATE POLICY tenant_isolation_payments ON financial.payments
|
|
USING (tenant_id = get_current_tenant_id());
|
|
|
|
CREATE POLICY tenant_isolation_bank_accounts ON financial.bank_accounts
|
|
USING (tenant_id = get_current_tenant_id());
|
|
|
|
CREATE POLICY tenant_isolation_reconciliations ON financial.reconciliations
|
|
USING (tenant_id = get_current_tenant_id());
|
|
|
|
-- =====================================================
|
|
-- SEED DATA
|
|
-- =====================================================
|
|
|
|
-- Tipos de cuenta estándar
|
|
INSERT INTO financial.account_types (code, name, account_type, description) VALUES
|
|
('ASSET_CASH', 'Cash and Cash Equivalents', 'asset', 'Efectivo y equivalentes'),
|
|
('ASSET_RECEIVABLE', 'Accounts Receivable', 'asset', 'Cuentas por cobrar'),
|
|
('ASSET_CURRENT', 'Current Assets', 'asset', 'Activos circulantes'),
|
|
('ASSET_FIXED', 'Fixed Assets', 'asset', 'Activos fijos'),
|
|
('LIABILITY_PAYABLE', 'Accounts Payable', 'liability', 'Cuentas por pagar'),
|
|
('LIABILITY_CURRENT', 'Current Liabilities', 'liability', 'Pasivos circulantes'),
|
|
('LIABILITY_LONG', 'Long-term Liabilities', 'liability', 'Pasivos a largo plazo'),
|
|
('EQUITY_CAPITAL', 'Capital', 'equity', 'Capital social'),
|
|
('EQUITY_RETAINED', 'Retained Earnings', 'equity', 'Utilidades retenidas'),
|
|
('REVENUE_SALES', 'Sales Revenue', 'revenue', 'Ingresos por ventas'),
|
|
('REVENUE_OTHER', 'Other Revenue', 'revenue', 'Otros ingresos'),
|
|
('EXPENSE_COGS', 'Cost of Goods Sold', 'expense', 'Costo de ventas'),
|
|
('EXPENSE_OPERATING', 'Operating Expenses', 'expense', 'Gastos operativos'),
|
|
('EXPENSE_ADMIN', 'Administrative Expenses', 'expense', 'Gastos administrativos')
|
|
ON CONFLICT (code) DO NOTHING;
|
|
|
|
-- =====================================================
|
|
-- COMENTARIOS
|
|
-- =====================================================
|
|
|
|
COMMENT ON SCHEMA financial IS 'Schema de contabilidad, facturas, pagos y finanzas';
|
|
COMMENT ON TABLE financial.account_types IS 'Tipos de cuentas contables (asset, liability, equity, revenue, expense)';
|
|
COMMENT ON TABLE financial.accounts IS 'Plan de cuentas contables';
|
|
COMMENT ON TABLE financial.journals IS 'Diarios contables (ventas, compras, bancos, etc.)';
|
|
COMMENT ON TABLE financial.fiscal_years IS 'Años fiscales';
|
|
COMMENT ON TABLE financial.fiscal_periods IS 'Períodos fiscales (meses)';
|
|
COMMENT ON TABLE financial.journal_entries IS 'Asientos contables';
|
|
COMMENT ON TABLE financial.journal_entry_lines IS 'Líneas de asientos contables (partida doble)';
|
|
COMMENT ON TABLE financial.taxes IS 'Impuestos (IVA, retenciones, etc.)';
|
|
COMMENT ON TABLE financial.payment_terms IS 'Términos de pago (inmediato, 30 días, etc.)';
|
|
COMMENT ON TABLE financial.invoices IS 'Facturas de cliente y proveedor';
|
|
COMMENT ON TABLE financial.invoice_lines IS 'Líneas de factura';
|
|
COMMENT ON TABLE financial.payments IS 'Pagos y cobros';
|
|
COMMENT ON TABLE financial.payment_invoice IS 'Conciliación de pagos con facturas';
|
|
COMMENT ON TABLE financial.bank_accounts IS 'Cuentas bancarias de la empresa y partners';
|
|
COMMENT ON TABLE financial.reconciliations IS 'Conciliaciones bancarias';
|
|
|
|
-- =====================================================
|
|
-- COR-024: Tax Repartition Lines
|
|
-- Equivalente a account.tax.repartition.line de Odoo
|
|
-- =====================================================
|
|
|
|
CREATE TYPE financial.repartition_type AS ENUM ('invoice', 'refund');
|
|
|
|
CREATE TABLE financial.tax_repartition_lines (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
|
|
tax_id UUID NOT NULL REFERENCES financial.taxes(id) ON DELETE CASCADE,
|
|
repartition_type financial.repartition_type NOT NULL,
|
|
sequence INTEGER DEFAULT 1,
|
|
factor_percent DECIMAL(10,4) DEFAULT 100,
|
|
account_id UUID REFERENCES financial.accounts(id),
|
|
tag_ids UUID[],
|
|
use_in_tax_closing BOOLEAN DEFAULT TRUE,
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE INDEX idx_tax_repartition_tax ON financial.tax_repartition_lines(tax_id);
|
|
CREATE INDEX idx_tax_repartition_type ON financial.tax_repartition_lines(repartition_type);
|
|
|
|
COMMENT ON TABLE financial.tax_repartition_lines IS 'COR-024: Tax repartition lines - Equivalent to account.tax.repartition.line';
|
|
|
|
-- =====================================================
|
|
-- COR-023: Bank Statements
|
|
-- Equivalente a account.bank.statement de Odoo
|
|
-- =====================================================
|
|
|
|
CREATE TYPE financial.statement_status AS ENUM ('draft', 'open', 'confirm', 'cancelled');
|
|
|
|
CREATE TABLE financial.bank_statements (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
|
|
journal_id UUID NOT NULL REFERENCES financial.journals(id),
|
|
name VARCHAR(100),
|
|
reference VARCHAR(255),
|
|
date DATE NOT NULL,
|
|
date_done DATE,
|
|
balance_start DECIMAL(20,6) DEFAULT 0,
|
|
balance_end_real DECIMAL(20,6) DEFAULT 0,
|
|
total_entry_encoding DECIMAL(20,6) DEFAULT 0,
|
|
status financial.statement_status DEFAULT 'draft',
|
|
currency_id UUID REFERENCES core.currencies(id),
|
|
is_complete BOOLEAN DEFAULT FALSE,
|
|
created_by UUID REFERENCES auth.users(id),
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE TABLE financial.bank_statement_lines (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
|
|
statement_id UUID NOT NULL REFERENCES financial.bank_statements(id) ON DELETE CASCADE,
|
|
sequence INTEGER DEFAULT 10,
|
|
date DATE NOT NULL,
|
|
payment_ref VARCHAR(255),
|
|
ref VARCHAR(255),
|
|
partner_id UUID REFERENCES core.partners(id),
|
|
amount DECIMAL(20,6) NOT NULL,
|
|
amount_currency DECIMAL(20,6),
|
|
foreign_currency_id UUID REFERENCES core.currencies(id),
|
|
transaction_type VARCHAR(50),
|
|
narration TEXT,
|
|
is_reconciled BOOLEAN DEFAULT FALSE,
|
|
partner_bank_id UUID REFERENCES core.partner_banks(id),
|
|
account_number VARCHAR(64),
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE INDEX idx_bank_statements_tenant ON financial.bank_statements(tenant_id);
|
|
CREATE INDEX idx_bank_statements_journal ON financial.bank_statements(journal_id);
|
|
CREATE INDEX idx_bank_statements_date ON financial.bank_statements(date);
|
|
CREATE INDEX idx_bank_statements_status ON financial.bank_statements(status);
|
|
CREATE INDEX idx_bank_statement_lines_statement ON financial.bank_statement_lines(statement_id);
|
|
CREATE INDEX idx_bank_statement_lines_partner ON financial.bank_statement_lines(partner_id);
|
|
|
|
-- RLS
|
|
ALTER TABLE financial.bank_statements ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE financial.bank_statement_lines ENABLE ROW LEVEL SECURITY;
|
|
CREATE POLICY tenant_isolation_bank_statements ON financial.bank_statements
|
|
USING (tenant_id = get_current_tenant_id());
|
|
CREATE POLICY tenant_isolation_bank_statement_lines ON financial.bank_statement_lines
|
|
USING (tenant_id = get_current_tenant_id());
|
|
|
|
COMMENT ON TABLE financial.bank_statements IS 'COR-023: Bank statements - Equivalent to account.bank.statement';
|
|
COMMENT ON TABLE financial.bank_statement_lines IS 'COR-023: Bank statement lines - Equivalent to account.bank.statement.line';
|
|
|
|
-- =====================================================
|
|
-- COR-028: Fiscal Positions
|
|
-- Equivalente a account.fiscal.position de Odoo
|
|
-- =====================================================
|
|
|
|
CREATE TABLE financial.fiscal_positions (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
|
|
name VARCHAR(255) NOT NULL,
|
|
sequence INTEGER DEFAULT 10,
|
|
is_active BOOLEAN DEFAULT TRUE,
|
|
company_id UUID REFERENCES core.companies(id),
|
|
country_id UUID REFERENCES core.countries(id),
|
|
state_ids UUID[], -- Array of core.states IDs
|
|
zip_from VARCHAR(20),
|
|
zip_to VARCHAR(20),
|
|
auto_apply BOOLEAN DEFAULT FALSE,
|
|
vat_required BOOLEAN DEFAULT FALSE,
|
|
note TEXT,
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE TABLE financial.fiscal_position_taxes (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
fiscal_position_id UUID NOT NULL REFERENCES financial.fiscal_positions(id) ON DELETE CASCADE,
|
|
tax_src_id UUID NOT NULL REFERENCES financial.taxes(id),
|
|
tax_dest_id UUID REFERENCES financial.taxes(id)
|
|
);
|
|
|
|
CREATE TABLE financial.fiscal_position_accounts (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
fiscal_position_id UUID NOT NULL REFERENCES financial.fiscal_positions(id) ON DELETE CASCADE,
|
|
account_src_id UUID NOT NULL REFERENCES financial.accounts(id),
|
|
account_dest_id UUID NOT NULL REFERENCES financial.accounts(id)
|
|
);
|
|
|
|
CREATE INDEX idx_fiscal_positions_tenant ON financial.fiscal_positions(tenant_id);
|
|
CREATE INDEX idx_fiscal_positions_country ON financial.fiscal_positions(country_id);
|
|
CREATE INDEX idx_fiscal_position_taxes_fp ON financial.fiscal_position_taxes(fiscal_position_id);
|
|
CREATE INDEX idx_fiscal_position_accounts_fp ON financial.fiscal_position_accounts(fiscal_position_id);
|
|
|
|
-- RLS
|
|
ALTER TABLE financial.fiscal_positions ENABLE ROW LEVEL SECURITY;
|
|
CREATE POLICY tenant_isolation_fiscal_positions ON financial.fiscal_positions
|
|
USING (tenant_id = get_current_tenant_id());
|
|
|
|
COMMENT ON TABLE financial.fiscal_positions IS 'COR-028: Fiscal positions - Equivalent to account.fiscal.position';
|
|
COMMENT ON TABLE financial.fiscal_position_taxes IS 'COR-028: Tax mappings for fiscal positions';
|
|
COMMENT ON TABLE financial.fiscal_position_accounts IS 'COR-028: Account mappings for fiscal positions';
|
|
|
|
-- =====================================================
|
|
-- COR-035: Payment Term Lines (Detalle de terminos de pago)
|
|
-- Equivalente a account.payment.term.line de Odoo
|
|
-- =====================================================
|
|
|
|
CREATE TYPE financial.payment_term_value AS ENUM ('balance', 'percent', 'fixed');
|
|
|
|
CREATE TABLE financial.payment_term_lines (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
payment_term_id UUID NOT NULL REFERENCES financial.payment_terms(id) ON DELETE CASCADE,
|
|
value financial.payment_term_value NOT NULL DEFAULT 'balance',
|
|
value_amount DECIMAL(20,6) DEFAULT 0,
|
|
nb_days INTEGER DEFAULT 0,
|
|
delay_type VARCHAR(20) DEFAULT 'days_after', -- days_after, days_after_end_of_month, days_after_end_of_next_month
|
|
day_of_the_month INTEGER,
|
|
sequence INTEGER DEFAULT 10,
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE INDEX idx_payment_term_lines_term ON financial.payment_term_lines(payment_term_id);
|
|
COMMENT ON TABLE financial.payment_term_lines IS 'COR-035: Payment term lines - Equivalent to account.payment.term.line';
|
|
|
|
-- =====================================================
|
|
-- COR-036: Incoterms (Terminos de comercio internacional)
|
|
-- Equivalente a account.incoterms de Odoo
|
|
-- =====================================================
|
|
|
|
CREATE TABLE financial.incoterms (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
name VARCHAR(255) NOT NULL,
|
|
code VARCHAR(10) NOT NULL UNIQUE,
|
|
is_active BOOLEAN DEFAULT TRUE,
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- Seed data para incoterms comunes
|
|
INSERT INTO financial.incoterms (name, code) VALUES
|
|
('Ex Works', 'EXW'),
|
|
('Free Carrier', 'FCA'),
|
|
('Carriage Paid To', 'CPT'),
|
|
('Carriage and Insurance Paid To', 'CIP'),
|
|
('Delivered at Place', 'DAP'),
|
|
('Delivered at Place Unloaded', 'DPU'),
|
|
('Delivered Duty Paid', 'DDP'),
|
|
('Free Alongside Ship', 'FAS'),
|
|
('Free on Board', 'FOB'),
|
|
('Cost and Freight', 'CFR'),
|
|
('Cost Insurance and Freight', 'CIF');
|
|
|
|
COMMENT ON TABLE financial.incoterms IS 'COR-036: Incoterms - Equivalent to account.incoterms';
|
|
|
|
-- =====================================================
|
|
-- COR-037: Payment Methods (Metodos de pago)
|
|
-- Equivalente a account.payment.method de Odoo
|
|
-- =====================================================
|
|
|
|
CREATE TABLE financial.payment_methods (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
name VARCHAR(255) NOT NULL,
|
|
code VARCHAR(50) NOT NULL,
|
|
payment_type VARCHAR(20) NOT NULL, -- inbound, outbound
|
|
is_active BOOLEAN DEFAULT TRUE,
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
UNIQUE(code, payment_type)
|
|
);
|
|
|
|
-- Seed data para metodos de pago comunes
|
|
INSERT INTO financial.payment_methods (name, code, payment_type) VALUES
|
|
('Manual', 'manual', 'inbound'),
|
|
('Manual', 'manual', 'outbound'),
|
|
('Bank Transfer', 'bank_transfer', 'inbound'),
|
|
('Bank Transfer', 'bank_transfer', 'outbound'),
|
|
('Check', 'check', 'inbound'),
|
|
('Check', 'check', 'outbound'),
|
|
('Credit Card', 'credit_card', 'inbound'),
|
|
('Direct Debit', 'direct_debit', 'inbound');
|
|
|
|
-- Agregar payment_method_id a payments
|
|
ALTER TABLE financial.payments ADD COLUMN IF NOT EXISTS payment_method_id UUID REFERENCES financial.payment_methods(id);
|
|
|
|
COMMENT ON TABLE financial.payment_methods IS 'COR-037: Payment methods - Equivalent to account.payment.method';
|
|
|
|
-- =====================================================
|
|
-- COR-038: Reconcile Models (Modelos de conciliacion)
|
|
-- Equivalente a account.reconcile.model de Odoo
|
|
-- =====================================================
|
|
|
|
CREATE TYPE financial.reconcile_model_type AS ENUM (
|
|
'writeoff_button',
|
|
'writeoff_suggestion',
|
|
'invoice_matching'
|
|
);
|
|
|
|
CREATE TABLE financial.reconcile_models (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
|
|
name VARCHAR(255) NOT NULL,
|
|
sequence INTEGER DEFAULT 10,
|
|
rule_type financial.reconcile_model_type DEFAULT 'writeoff_button',
|
|
auto_reconcile BOOLEAN DEFAULT FALSE,
|
|
match_nature VARCHAR(20) DEFAULT 'both', -- amount_received, amount_paid, both
|
|
match_amount VARCHAR(20) DEFAULT 'any', -- lower, greater, between, any
|
|
match_amount_min DECIMAL(20,6),
|
|
match_amount_max DECIMAL(20,6),
|
|
match_label VARCHAR(50),
|
|
match_label_param VARCHAR(255),
|
|
match_partner BOOLEAN DEFAULT FALSE,
|
|
match_partner_ids UUID[],
|
|
is_active BOOLEAN DEFAULT TRUE,
|
|
company_id UUID REFERENCES core.companies(id),
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE TABLE financial.reconcile_model_lines (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
model_id UUID NOT NULL REFERENCES financial.reconcile_models(id) ON DELETE CASCADE,
|
|
sequence INTEGER DEFAULT 10,
|
|
account_id UUID NOT NULL REFERENCES financial.accounts(id),
|
|
journal_id UUID REFERENCES financial.journals(id),
|
|
label VARCHAR(255),
|
|
amount_type VARCHAR(20) DEFAULT 'percentage', -- percentage, fixed, regex
|
|
amount_value DECIMAL(20,6) DEFAULT 100,
|
|
tax_ids UUID[],
|
|
analytic_account_id UUID REFERENCES analytics.analytic_accounts(id),
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE INDEX idx_reconcile_models_tenant ON financial.reconcile_models(tenant_id);
|
|
CREATE INDEX idx_reconcile_model_lines_model ON financial.reconcile_model_lines(model_id);
|
|
|
|
ALTER TABLE financial.reconcile_models ENABLE ROW LEVEL SECURITY;
|
|
CREATE POLICY tenant_isolation_reconcile_models ON financial.reconcile_models
|
|
USING (tenant_id = get_current_tenant_id());
|
|
|
|
COMMENT ON TABLE financial.reconcile_models IS 'COR-038: Reconcile models - Equivalent to account.reconcile.model';
|
|
COMMENT ON TABLE financial.reconcile_model_lines IS 'COR-038: Reconcile model lines';
|
|
|
|
-- =====================================================
|
|
-- COR-039: Campos adicionales en tablas existentes
|
|
-- =====================================================
|
|
|
|
-- Campos en journal_entries (account.move)
|
|
ALTER TABLE financial.journal_entries ADD COLUMN IF NOT EXISTS invoice_origin VARCHAR(255);
|
|
ALTER TABLE financial.journal_entries ADD COLUMN IF NOT EXISTS payment_reference VARCHAR(255);
|
|
ALTER TABLE financial.journal_entries ADD COLUMN IF NOT EXISTS invoice_date_due DATE;
|
|
ALTER TABLE financial.journal_entries ADD COLUMN IF NOT EXISTS incoterm_id UUID REFERENCES financial.incoterms(id);
|
|
ALTER TABLE financial.journal_entries ADD COLUMN IF NOT EXISTS incoterm_location VARCHAR(255);
|
|
ALTER TABLE financial.journal_entries ADD COLUMN IF NOT EXISTS qr_code_method VARCHAR(50);
|
|
ALTER TABLE financial.journal_entries ADD COLUMN IF NOT EXISTS invoice_source_email VARCHAR(255);
|
|
ALTER TABLE financial.journal_entries ADD COLUMN IF NOT EXISTS reversed_entry_id UUID REFERENCES financial.journal_entries(id);
|
|
ALTER TABLE financial.journal_entries ADD COLUMN IF NOT EXISTS auto_post VARCHAR(20) DEFAULT 'no'; -- no, at_date, monthly, quarterly, yearly
|
|
ALTER TABLE financial.journal_entries ADD COLUMN IF NOT EXISTS auto_post_until DATE;
|
|
|
|
-- Campos en journal_entry_lines (account.move.line)
|
|
ALTER TABLE financial.journal_entry_lines ADD COLUMN IF NOT EXISTS discount DECIMAL(10,4) DEFAULT 0;
|
|
ALTER TABLE financial.journal_entry_lines ADD COLUMN IF NOT EXISTS display_type VARCHAR(20); -- product, line_section, line_note
|
|
ALTER TABLE financial.journal_entry_lines ADD COLUMN IF NOT EXISTS is_rounding_line BOOLEAN DEFAULT FALSE;
|
|
ALTER TABLE financial.journal_entry_lines ADD COLUMN IF NOT EXISTS exclude_from_invoice_tab BOOLEAN DEFAULT FALSE;
|
|
|
|
-- Campos en taxes
|
|
ALTER TABLE financial.taxes ADD COLUMN IF NOT EXISTS tax_scope VARCHAR(20); -- service, consu
|
|
ALTER TABLE financial.taxes ADD COLUMN IF NOT EXISTS is_base_affected BOOLEAN DEFAULT FALSE;
|
|
ALTER TABLE financial.taxes ADD COLUMN IF NOT EXISTS hide_tax_exigibility BOOLEAN DEFAULT FALSE;
|
|
ALTER TABLE financial.taxes ADD COLUMN IF NOT EXISTS tax_exigibility VARCHAR(20) DEFAULT 'on_invoice'; -- on_invoice, on_payment
|
|
|
|
COMMENT ON COLUMN financial.journal_entries.invoice_origin IS 'COR-039: Source document reference';
|
|
COMMENT ON COLUMN financial.journal_entries.qr_code_method IS 'COR-039: QR code payment method';
|
|
|
|
-- =====================================================
|
|
-- FIN DEL SCHEMA FINANCIAL
|
|
-- =====================================================
|