rckrdmrd/erp-construccion-database-v2
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
main
erp-construccion-database-v2/seeds/dev/09-finanzas.sql
Adrian Flores Cortes 8df74f73d2 [TASK-2026-02-03-SEEDS] feat: Add complete development seeds for demo 
Seeds include:
- 01-core-catalogs.sql: Units, currencies, taxes, banks, states (~100 records)
- 02-users-profiles.sql: 12 roles, 50+ permissions, 12 demo users
- 03-empresa-tenant.sql: Demo company with 2 branches, 4 warehouses
- 04-proyectos-obras.sql: 3 projects, stages, lots, apartments
- 05-presupuestos.sql: CMIC-based concepts, 5 budgets
- 06-estimaciones.sql: Contracts, estimates, advances
- 07-avances-calidad.sql: Progress, logbook, quality checklists
- 08-hse.sql: PPE catalog, training, incidents
- 09-finanzas.sql: Chart of accounts, CxP, CxC, balanced entries
- 10-activos-documentos.sql: Assets, maintenance, documents

Total: ~940 records for complete demo coverage

Co-Authored-By: Claude Opus 4.5 <noreply@anthropic.com>
2026-02-03 11:08:26 -06:00

380 lines
22 KiB
PL/PgSQL
Raw Permalink Blame History

-- ═══════════════════════════════════════════════════════════════════════════════
-- SEED 09: FINANZAS Y CONTABILIDAD
-- ═══════════════════════════════════════════════════════════════════════════════
-- Proyecto: ERP Construcción
-- Autor: Claude Opus 4.5
-- Fecha: 2026-02-03
-- Descripción: Catálogo de cuentas, cuentas bancarias, CxP, CxC, pólizas
-- Dependencias: 03-empresa-tenant.sql, 06-estimaciones.sql
-- ═══════════════════════════════════════════════════════════════════════════════
-- UUIDs: 00000000-0000-0000-0009-XXXXXXXXXXXX
BEGIN;
-- ─────────────────────────────────────────────────────────────────────────────────
-- 1. CATÁLOGO DE CUENTAS CONTABLES (Simplificado SAT)
-- ─────────────────────────────────────────────────────────────────────────────────
INSERT INTO finance.chart_of_accounts (
id, tenant_id, code, name, account_type, nature, status, level, parent_id,
allows_direct_posting, created_at
) VALUES
-- ACTIVO (1)
('00000000-0000-0000-0009-000000000001', '00000000-0000-0000-0003-000000000001',
'1000', 'ACTIVO', 'asset', 'debit', 'active', 1, NULL, false, NOW()),
('00000000-0000-0000-0009-000000000002', '00000000-0000-0000-0003-000000000001',
'1100', 'Activo Circulante', 'asset', 'debit', 'active', 2, '00000000-0000-0000-0009-000000000001', false, NOW()),
('00000000-0000-0000-0009-000000000003', '00000000-0000-0000-0003-000000000001',
'1101', 'Caja', 'asset', 'debit', 'active', 3, '00000000-0000-0000-0009-000000000002', true, NOW()),
('00000000-0000-0000-0009-000000000004', '00000000-0000-0000-0003-000000000001',
'1102', 'Bancos', 'asset', 'debit', 'active', 3, '00000000-0000-0000-0009-000000000002', true, NOW()),
('00000000-0000-0000-0009-000000000005', '00000000-0000-0000-0003-000000000001',
'1103', 'Clientes', 'asset', 'debit', 'active', 3, '00000000-0000-0000-0009-000000000002', true, NOW()),
('00000000-0000-0000-0009-000000000006', '00000000-0000-0000-0003-000000000001',
'1104', 'Deudores Diversos', 'asset', 'debit', 'active', 3, '00000000-0000-0000-0009-000000000002', true, NOW()),
('00000000-0000-0000-0009-000000000007', '00000000-0000-0000-0003-000000000001',
'1105', 'Inventarios', 'asset', 'debit', 'active', 3, '00000000-0000-0000-0009-000000000002', true, NOW()),
('00000000-0000-0000-0009-000000000008', '00000000-0000-0000-0003-000000000001',
'1106', 'Anticipos a Proveedores', 'asset', 'debit', 'active', 3, '00000000-0000-0000-0009-000000000002', true, NOW()),
('00000000-0000-0000-0009-000000000009', '00000000-0000-0000-0003-000000000001',
'1107', 'IVA Acreditable', 'asset', 'debit', 'active', 3, '00000000-0000-0000-0009-000000000002', true, NOW()),
('00000000-0000-0000-0009-000000000010', '00000000-0000-0000-0003-000000000001',
'1200', 'Activo Fijo', 'asset', 'debit', 'active', 2, '00000000-0000-0000-0009-000000000001', false, NOW()),
('00000000-0000-0000-0009-000000000011', '00000000-0000-0000-0003-000000000001',
'1201', 'Terrenos', 'asset', 'debit', 'active', 3, '00000000-0000-0000-0009-000000000010', true, NOW()),
('00000000-0000-0000-0009-000000000012', '00000000-0000-0000-0003-000000000001',
'1202', 'Edificios', 'asset', 'debit', 'active', 3, '00000000-0000-0000-0009-000000000010', true, NOW()),
('00000000-0000-0000-0009-000000000013', '00000000-0000-0000-0003-000000000001',
'1203', 'Maquinaria y Equipo', 'asset', 'debit', 'active', 3, '00000000-0000-0000-0009-000000000010', true, NOW()),
('00000000-0000-0000-0009-000000000014', '00000000-0000-0000-0003-000000000001',
'1204', 'Equipo de Transporte', 'asset', 'debit', 'active', 3, '00000000-0000-0000-0009-000000000010', true, NOW()),
('00000000-0000-0000-0009-000000000015', '00000000-0000-0000-0003-000000000001',
'1205', 'Depreciación Acumulada', 'asset', 'credit', 'active', 3, '00000000-0000-0000-0009-000000000010', true, NOW()),
-- PASIVO (2)
('00000000-0000-0000-0009-000000000020', '00000000-0000-0000-0003-000000000001',
'2000', 'PASIVO', 'liability', 'credit', 'active', 1, NULL, false, NOW()),
('00000000-0000-0000-0009-000000000021', '00000000-0000-0000-0003-000000000001',
'2100', 'Pasivo Circulante', 'liability', 'credit', 'active', 2, '00000000-0000-0000-0009-000000000020', false, NOW()),
('00000000-0000-0000-0009-000000000022', '00000000-0000-0000-0003-000000000001',
'2101', 'Proveedores', 'liability', 'credit', 'active', 3, '00000000-0000-0000-0009-000000000021', true, NOW()),
('00000000-0000-0000-0009-000000000023', '00000000-0000-0000-0003-000000000001',
'2102', 'Acreedores Diversos', 'liability', 'credit', 'active', 3, '00000000-0000-0000-0009-000000000021', true, NOW()),
('00000000-0000-0000-0009-000000000024', '00000000-0000-0000-0003-000000000001',
'2103', 'IVA por Pagar', 'liability', 'credit', 'active', 3, '00000000-0000-0000-0009-000000000021', true, NOW()),
('00000000-0000-0000-0009-000000000025', '00000000-0000-0000-0003-000000000001',
'2104', 'ISR por Pagar', 'liability', 'credit', 'active', 3, '00000000-0000-0000-0009-000000000021', true, NOW()),
('00000000-0000-0000-0009-000000000026', '00000000-0000-0000-0003-000000000001',
'2105', 'Retenciones por Pagar', 'liability', 'credit', 'active', 3, '00000000-0000-0000-0009-000000000021', true, NOW()),
('00000000-0000-0000-0009-000000000027', '00000000-0000-0000-0003-000000000001',
'2106', 'Anticipos de Clientes', 'liability', 'credit', 'active', 3, '00000000-0000-0000-0009-000000000021', true, NOW()),
-- CAPITAL (3)
('00000000-0000-0000-0009-000000000030', '00000000-0000-0000-0003-000000000001',
'3000', 'CAPITAL CONTABLE', 'equity', 'credit', 'active', 1, NULL, false, NOW()),
('00000000-0000-0000-0009-000000000031', '00000000-0000-0000-0003-000000000001',
'3100', 'Capital Social', 'equity', 'credit', 'active', 2, '00000000-0000-0000-0009-000000000030', true, NOW()),
('00000000-0000-0000-0009-000000000032', '00000000-0000-0000-0003-000000000001',
'3200', 'Utilidades Acumuladas', 'equity', 'credit', 'active', 2, '00000000-0000-0000-0009-000000000030', true, NOW()),
('00000000-0000-0000-0009-000000000033', '00000000-0000-0000-0003-000000000001',
'3300', 'Utilidad del Ejercicio', 'equity', 'credit', 'active', 2, '00000000-0000-0000-0009-000000000030', true, NOW()),
-- INGRESOS (4)
('00000000-0000-0000-0009-000000000040', '00000000-0000-0000-0003-000000000001',
'4000', 'INGRESOS', 'income', 'credit', 'active', 1, NULL, false, NOW()),
('00000000-0000-0000-0009-000000000041', '00000000-0000-0000-0003-000000000001',
'4100', 'Ingresos por Obra', 'income', 'credit', 'active', 2, '00000000-0000-0000-0009-000000000040', true, NOW()),
('00000000-0000-0000-0009-000000000042', '00000000-0000-0000-0003-000000000001',
'4200', 'Ingresos por Ventas', 'income', 'credit', 'active', 2, '00000000-0000-0000-0009-000000000040', true, NOW()),
('00000000-0000-0000-0009-000000000043', '00000000-0000-0000-0003-000000000001',
'4300', 'Otros Ingresos', 'income', 'credit', 'active', 2, '00000000-0000-0000-0009-000000000040', true, NOW()),
-- GASTOS (5)
('00000000-0000-0000-0009-000000000050', '00000000-0000-0000-0003-000000000001',
'5000', 'COSTOS Y GASTOS', 'expense', 'debit', 'active', 1, NULL, false, NOW()),
('00000000-0000-0000-0009-000000000051', '00000000-0000-0000-0003-000000000001',
'5100', 'Costo de Obra', 'expense', 'debit', 'active', 2, '00000000-0000-0000-0009-000000000050', true, NOW()),
('00000000-0000-0000-0009-000000000052', '00000000-0000-0000-0003-000000000001',
'5200', 'Gastos de Administración', 'expense', 'debit', 'active', 2, '00000000-0000-0000-0009-000000000050', true, NOW()),
('00000000-0000-0000-0009-000000000053', '00000000-0000-0000-0003-000000000001',
'5300', 'Gastos Financieros', 'expense', 'debit', 'active', 2, '00000000-0000-0000-0009-000000000050', true, NOW())
ON CONFLICT (id) DO NOTHING;
-- ─────────────────────────────────────────────────────────────────────────────────
-- 2. CUENTAS BANCARIAS
-- ─────────────────────────────────────────────────────────────────────────────────
INSERT INTO finance.bank_accounts (
id, tenant_id, name, account_number, clabe, account_type, status,
bank_name, bank_code, currency,
initial_balance, current_balance, available_balance,
ledger_account_id, is_default, allows_payments, allows_collections,
created_at, created_by
) VALUES
('00000000-0000-0000-0009-000000000101', '00000000-0000-0000-0003-000000000001',
'Cuenta Principal BBVA', '0123456789', '012345678901234567', 'checking', 'active',
'BBVA MEXICO', '012', 'MXN',
5000000.00, 4250000.00, 4200000.00,
'00000000-0000-0000-0009-000000000004', true, true, true,
NOW(), '00000000-0000-0000-0002-000000000107'),
('00000000-0000-0000-0009-000000000102', '00000000-0000-0000-0003-000000000001',
'Cuenta Nómina Banorte', '9876543210', '072987654321098765', 'checking', 'active',
'BANORTE', '072', 'MXN',
500000.00, 320000.00, 320000.00,
'00000000-0000-0000-0009-000000000004', false, true, false,
NOW(), '00000000-0000-0000-0002-000000000107'),
('00000000-0000-0000-0009-000000000103', '00000000-0000-0000-0003-000000000001',
'Cuenta de Inversión Santander', '5555666677', '014555566667700001', 'investment', 'active',
'SANTANDER', '014', 'MXN',
2000000.00, 2050000.00, 0.00,
'00000000-0000-0000-0009-000000000004', false, false, false,
NOW(), '00000000-0000-0000-0002-000000000107')
ON CONFLICT (id) DO NOTHING;
-- ─────────────────────────────────────────────────────────────────────────────────
-- 3. CUENTAS POR PAGAR
-- ─────────────────────────────────────────────────────────────────────────────────
INSERT INTO finance.accounts_payable (
id, tenant_id, document_number, document_type,
supplier_id, supplier_name, supplier_rfc,
status, invoice_date, due_date,
subtotal, tax_amount, retention_amount, total_amount, paid_amount, balance,
currency, payment_terms, payment_days,
expense_account_id, project_id,
created_at, created_by
) VALUES
('00000000-0000-0000-0009-000000000201', '00000000-0000-0000-0003-000000000001',
'FAC-PROV-001', 'invoice',
'00000000-0000-0000-0004-000000000801', 'Cimientos del Norte S.A. de C.V.', 'CNO010101AAA',
'paid', '2024-03-20', '2024-04-20',
283500.00, 45360.00, 0.00, 328860.00, 328860.00, 0.00,
'MXN', 'Neto 30 días', 30,
'00000000-0000-0000-0009-000000000051', '00000000-0000-0000-0004-000000000101',
NOW(), '00000000-0000-0000-0002-000000000107'),
('00000000-0000-0000-0009-000000000202', '00000000-0000-0000-0003-000000000001',
'FAC-PROV-002', 'invoice',
'00000000-0000-0000-0004-000000000802', 'Electrificaciones AGS S.A. de C.V.', 'EAG010101BBB',
'pending', '2024-05-20', '2024-06-20',
254800.00, 40768.00, 0.00, 295568.00, 0.00, 295568.00,
'MXN', 'Neto 30 días', 30,
'00000000-0000-0000-0009-000000000051', '00000000-0000-0000-0004-000000000101',
NOW(), '00000000-0000-0000-0002-000000000107'),
('00000000-0000-0000-0009-000000000203', '00000000-0000-0000-0003-000000000001',
'FAC-PROV-003', 'invoice',
'00000000-0000-0000-0004-000000000803', 'Plomería Integral de Aguascalientes S.A. de C.V.', 'PIA010101CCC',
'pending', '2024-05-25', '2024-06-25',
180000.00, 28800.00, 0.00, 208800.00, 0.00, 208800.00,
'MXN', 'Neto 30 días', 30,
'00000000-0000-0000-0009-000000000051', '00000000-0000-0000-0004-000000000101',
NOW(), '00000000-0000-0000-0002-000000000107')
ON CONFLICT (id) DO NOTHING;
-- ─────────────────────────────────────────────────────────────────────────────────
-- 4. CUENTAS POR COBRAR
-- ─────────────────────────────────────────────────────────────────────────────────
INSERT INTO finance.accounts_receivable (
id, tenant_id, document_number, document_type,
customer_id, customer_name, customer_rfc,
status, invoice_date, due_date,
subtotal, tax_amount, retention_amount, total_amount, collected_amount, balance,
currency, payment_terms, payment_days,
income_account_id, project_id,
created_at, created_by
) VALUES
('00000000-0000-0000-0009-000000000301', '00000000-0000-0000-0003-000000000001',
'FAC-CLT-001', 'estimation',
'00000000-0000-0000-0002-000000000112', 'Cliente Demo', 'XAXX010101000',
'collected', '2024-04-01', '2024-04-30',
1900000.00, 304000.00, 0.00, 2204000.00, 2204000.00, 0.00,
'MXN', 'Neto 30 días', 30,
'00000000-0000-0000-0009-000000000042', '00000000-0000-0000-0004-000000000101',
NOW(), '00000000-0000-0000-0002-000000000107'),
('00000000-0000-0000-0009-000000000302', '00000000-0000-0000-0003-000000000001',
'FAC-CLT-002', 'estimation',
'00000000-0000-0000-0002-000000000112', 'Cliente Demo', 'XAXX010101000',
'pending', '2024-05-15', '2024-06-15',
1880000.00, 300800.00, 0.00, 2180800.00, 0.00, 2180800.00,
'MXN', 'Neto 30 días', 30,
'00000000-0000-0000-0009-000000000042', '00000000-0000-0000-0004-000000000101',
NOW(), '00000000-0000-0000-0002-000000000107')
ON CONFLICT (id) DO NOTHING;
-- ─────────────────────────────────────────────────────────────────────────────────
-- 5. PÓLIZAS CONTABLES (Ejemplo balanceado)
-- ─────────────────────────────────────────────────────────────────────────────────
INSERT INTO finance.accounting_entries (
id, tenant_id, entry_number, entry_type, entry_date, status,
description, fiscal_year, fiscal_period,
total_debit, total_credit, is_balanced,
currency, exchange_rate, posted_at, posted_by_id,
created_at, created_by
) VALUES
('00000000-0000-0000-0009-000000000401', '00000000-0000-0000-0003-000000000001',
'POL-2024-001', 'purchase', '2024-03-20', 'posted',
'Registro factura proveedor cimentaciones', 2024, 3,
328860.00, 328860.00, true,
'MXN', 1.0, '2024-03-20', '00000000-0000-0000-0002-000000000107',
NOW(), '00000000-0000-0000-0002-000000000107'),
('00000000-0000-0000-0009-000000000402', '00000000-0000-0000-0003-000000000001',
'POL-2024-002', 'payment', '2024-04-15', 'posted',
'Pago a proveedor cimentaciones', 2024, 4,
328860.00, 328860.00, true,
'MXN', 1.0, '2024-04-15', '00000000-0000-0000-0002-000000000107',
NOW(), '00000000-0000-0000-0002-000000000107'),
('00000000-0000-0000-0009-000000000403', '00000000-0000-0000-0003-000000000001',
'POL-2024-003', 'sale', '2024-04-01', 'posted',
'Facturación venta lote A-01', 2024, 4,
2204000.00, 2204000.00, true,
'MXN', 1.0, '2024-04-01', '00000000-0000-0000-0002-000000000107',
NOW(), '00000000-0000-0000-0002-000000000107'),
('00000000-0000-0000-0009-000000000404', '00000000-0000-0000-0003-000000000001',
'POL-2024-004', 'collection', '2024-04-25', 'posted',
'Cobro cliente lote A-01', 2024, 4,
2204000.00, 2204000.00, true,
'MXN', 1.0, '2024-04-25', '00000000-0000-0000-0002-000000000107',
NOW(), '00000000-0000-0000-0002-000000000107')
ON CONFLICT (id) DO NOTHING;
-- ─────────────────────────────────────────────────────────────────────────────────
-- 6. LÍNEAS DE PÓLIZA
-- ─────────────────────────────────────────────────────────────────────────────────
INSERT INTO finance.accounting_entry_lines (
id, tenant_id, entry_id, line_number,
account_id, account_code, description,
debit, credit,
created_at
) VALUES
-- POL-001: Compra
('00000000-0000-0000-0009-000000000501', '00000000-0000-0000-0003-000000000001',
'00000000-0000-0000-0009-000000000401', 1,
'00000000-0000-0000-0009-000000000051', '5100', 'Costo de obra - Cimentación',
283500.00, 0.00, NOW()),
('00000000-0000-0000-0009-000000000502', '00000000-0000-0000-0003-000000000001',
'00000000-0000-0000-0009-000000000401', 2,
'00000000-0000-0000-0009-000000000009', '1107', 'IVA Acreditable',
45360.00, 0.00, NOW()),
('00000000-0000-0000-0009-000000000503', '00000000-0000-0000-0003-000000000001',
'00000000-0000-0000-0009-000000000401', 3,
'00000000-0000-0000-0009-000000000022', '2101', 'Proveedores',
0.00, 328860.00, NOW()),
-- POL-002: Pago
('00000000-0000-0000-0009-000000000504', '00000000-0000-0000-0003-000000000001',
'00000000-0000-0000-0009-000000000402', 1,
'00000000-0000-0000-0009-000000000022', '2101', 'Proveedores',
328860.00, 0.00, NOW()),
('00000000-0000-0000-0009-000000000505', '00000000-0000-0000-0003-000000000001',
'00000000-0000-0000-0009-000000000402', 2,
'00000000-0000-0000-0009-000000000004', '1102', 'Bancos',
0.00, 328860.00, NOW()),
-- POL-003: Venta
('00000000-0000-0000-0009-000000000506', '00000000-0000-0000-0003-000000000001',
'00000000-0000-0000-0009-000000000403', 1,
'00000000-0000-0000-0009-000000000005', '1103', 'Clientes',
2204000.00, 0.00, NOW()),
('00000000-0000-0000-0009-000000000507', '00000000-0000-0000-0003-000000000001',
'00000000-0000-0000-0009-000000000403', 2,
'00000000-0000-0000-0009-000000000042', '4200', 'Ingresos por Ventas',
0.00, 1900000.00, NOW()),
('00000000-0000-0000-0009-000000000508', '00000000-0000-0000-0003-000000000001',
'00000000-0000-0000-0009-000000000403', 3,
'00000000-0000-0000-0009-000000000024', '2103', 'IVA por Pagar',
0.00, 304000.00, NOW()),
-- POL-004: Cobro
('00000000-0000-0000-0009-000000000509', '00000000-0000-0000-0003-000000000001',
'00000000-0000-0000-0009-000000000404', 1,
'00000000-0000-0000-0009-000000000004', '1102', 'Bancos',
2204000.00, 0.00, NOW()),
('00000000-0000-0000-0009-000000000510', '00000000-0000-0000-0003-000000000001',
'00000000-0000-0000-0009-000000000404', 2,
'00000000-0000-0000-0009-000000000005', '1103', 'Clientes',
0.00, 2204000.00, NOW())
ON CONFLICT (id) DO NOTHING;
-- ─────────────────────────────────────────────────────────────────────────────────
-- VERIFICACIÓN
-- ─────────────────────────────────────────────────────────────────────────────────
DO $$
DECLARE
v_cuentas INTEGER;
v_bancos INTEGER;
v_cxp INTEGER;
v_cxc INTEGER;
v_polizas INTEGER;
v_balance_check INTEGER;
BEGIN
SELECT COUNT(*) INTO v_cuentas FROM finance.chart_of_accounts;
SELECT COUNT(*) INTO v_bancos FROM finance.bank_accounts;
SELECT COUNT(*) INTO v_cxp FROM finance.accounts_payable;
SELECT COUNT(*) INTO v_cxc FROM finance.accounts_receivable;
SELECT COUNT(*) INTO v_polizas FROM finance.accounting_entries;
-- Verificar balance de pólizas
SELECT COUNT(*) INTO v_balance_check
FROM finance.accounting_entries e
WHERE ABS(total_debit - total_credit) > 0.01;
RAISE NOTICE '══════════════════════════════════════════════════════════════';
RAISE NOTICE 'SEED 09 - FINANZAS - COMPLETADO';
RAISE NOTICE '══════════════════════════════════════════════════════════════';
RAISE NOTICE 'Cuentas contables: %', v_cuentas;
RAISE NOTICE 'Cuentas bancarias: %', v_bancos;
RAISE NOTICE 'Cuentas por pagar: %', v_cxp;
RAISE NOTICE 'Cuentas por cobrar: %', v_cxc;
RAISE NOTICE 'Pólizas contables: %', v_polizas;
RAISE NOTICE 'Pólizas desbalanceadas: % (debe ser 0)', v_balance_check;
RAISE NOTICE '══════════════════════════════════════════════════════════════';
END $$;
COMMIT;
-- ═══════════════════════════════════════════════════════════════════════════════
-- FIN SEED 09
-- ═══════════════════════════════════════════════════════════════════════════════
Reference in New Issue View Git Blame Copy Permalink