rckrdmrd
49c64e74a8
DDL (20-core-catalogs.sql): - Countries, States tables with ISO codes - Currencies with ISO 4217 - Currency rates with historical tracking - UoM categories and units with conversion factors - Product categories hierarchy - Sequences, Payment terms, Discount rules Seed data (04-seed-catalogs.sql): - 33 countries (Americas, Europe, Asia) - 32 Mexican states with timezones - 16 currencies (MXN, USD, EUR, etc.) - Initial exchange rates - 6 UoM categories with 30+ units Co-Authored-By: Claude Opus 4.5 <noreply@anthropic.com>
260 lines
11 KiB
SQL
260 lines
11 KiB
SQL
-- =============================================================
|
|
-- ARCHIVO: 04-seed-catalogs.sql
|
|
-- DESCRIPCION: Datos semilla para catalogos maestros
|
|
-- VERSION: 1.0.0
|
|
-- PROYECTO: ERP-Core V2
|
|
-- FECHA: 2026-01-18
|
|
-- =============================================================
|
|
|
|
-- =====================
|
|
-- PAISES (ISO 3166-1)
|
|
-- =====================
|
|
|
|
INSERT INTO core.countries (code, code_alpha3, name, phone_code, currency_code) VALUES
|
|
-- America del Norte
|
|
('MX', 'MEX', 'México', '+52', 'MXN'),
|
|
('US', 'USA', 'Estados Unidos', '+1', 'USD'),
|
|
('CA', 'CAN', 'Canadá', '+1', 'CAD'),
|
|
-- America Central
|
|
('GT', 'GTM', 'Guatemala', '+502', 'GTQ'),
|
|
('BZ', 'BLZ', 'Belice', '+501', 'BZD'),
|
|
('SV', 'SLV', 'El Salvador', '+503', 'USD'),
|
|
('HN', 'HND', 'Honduras', '+504', 'HNL'),
|
|
('NI', 'NIC', 'Nicaragua', '+505', 'NIO'),
|
|
('CR', 'CRI', 'Costa Rica', '+506', 'CRC'),
|
|
('PA', 'PAN', 'Panamá', '+507', 'PAB'),
|
|
-- America del Sur
|
|
('CO', 'COL', 'Colombia', '+57', 'COP'),
|
|
('VE', 'VEN', 'Venezuela', '+58', 'VES'),
|
|
('EC', 'ECU', 'Ecuador', '+593', 'USD'),
|
|
('PE', 'PER', 'Perú', '+51', 'PEN'),
|
|
('BO', 'BOL', 'Bolivia', '+591', 'BOB'),
|
|
('CL', 'CHL', 'Chile', '+56', 'CLP'),
|
|
('AR', 'ARG', 'Argentina', '+54', 'ARS'),
|
|
('UY', 'URY', 'Uruguay', '+598', 'UYU'),
|
|
('PY', 'PRY', 'Paraguay', '+595', 'PYG'),
|
|
('BR', 'BRA', 'Brasil', '+55', 'BRL'),
|
|
-- Europa
|
|
('ES', 'ESP', 'España', '+34', 'EUR'),
|
|
('DE', 'DEU', 'Alemania', '+49', 'EUR'),
|
|
('FR', 'FRA', 'Francia', '+33', 'EUR'),
|
|
('IT', 'ITA', 'Italia', '+39', 'EUR'),
|
|
('GB', 'GBR', 'Reino Unido', '+44', 'GBP'),
|
|
('PT', 'PRT', 'Portugal', '+351', 'EUR'),
|
|
('NL', 'NLD', 'Países Bajos', '+31', 'EUR'),
|
|
('BE', 'BEL', 'Bélgica', '+32', 'EUR'),
|
|
('CH', 'CHE', 'Suiza', '+41', 'CHF'),
|
|
-- Asia
|
|
('CN', 'CHN', 'China', '+86', 'CNY'),
|
|
('JP', 'JPN', 'Japón', '+81', 'JPY'),
|
|
('KR', 'KOR', 'Corea del Sur', '+82', 'KRW'),
|
|
('IN', 'IND', 'India', '+91', 'INR')
|
|
ON CONFLICT (code) DO NOTHING;
|
|
|
|
-- =====================
|
|
-- ESTADOS DE MEXICO
|
|
-- =====================
|
|
|
|
-- Obtener ID de México
|
|
DO $$
|
|
DECLARE
|
|
mexico_id UUID;
|
|
BEGIN
|
|
SELECT id INTO mexico_id FROM core.countries WHERE code = 'MX';
|
|
|
|
IF mexico_id IS NOT NULL THEN
|
|
INSERT INTO core.states (country_id, code, name, timezone, is_active) VALUES
|
|
(mexico_id, 'AGU', 'Aguascalientes', 'America/Mexico_City', true),
|
|
(mexico_id, 'BCN', 'Baja California', 'America/Tijuana', true),
|
|
(mexico_id, 'BCS', 'Baja California Sur', 'America/Mazatlan', true),
|
|
(mexico_id, 'CAM', 'Campeche', 'America/Merida', true),
|
|
(mexico_id, 'CHP', 'Chiapas', 'America/Mexico_City', true),
|
|
(mexico_id, 'CHH', 'Chihuahua', 'America/Chihuahua', true),
|
|
(mexico_id, 'CMX', 'Ciudad de México', 'America/Mexico_City', true),
|
|
(mexico_id, 'COA', 'Coahuila', 'America/Monterrey', true),
|
|
(mexico_id, 'COL', 'Colima', 'America/Mexico_City', true),
|
|
(mexico_id, 'DUR', 'Durango', 'America/Monterrey', true),
|
|
(mexico_id, 'GUA', 'Guanajuato', 'America/Mexico_City', true),
|
|
(mexico_id, 'GRO', 'Guerrero', 'America/Mexico_City', true),
|
|
(mexico_id, 'HID', 'Hidalgo', 'America/Mexico_City', true),
|
|
(mexico_id, 'JAL', 'Jalisco', 'America/Mexico_City', true),
|
|
(mexico_id, 'MEX', 'Estado de México', 'America/Mexico_City', true),
|
|
(mexico_id, 'MIC', 'Michoacán', 'America/Mexico_City', true),
|
|
(mexico_id, 'MOR', 'Morelos', 'America/Mexico_City', true),
|
|
(mexico_id, 'NAY', 'Nayarit', 'America/Mazatlan', true),
|
|
(mexico_id, 'NLE', 'Nuevo León', 'America/Monterrey', true),
|
|
(mexico_id, 'OAX', 'Oaxaca', 'America/Mexico_City', true),
|
|
(mexico_id, 'PUE', 'Puebla', 'America/Mexico_City', true),
|
|
(mexico_id, 'QUE', 'Querétaro', 'America/Mexico_City', true),
|
|
(mexico_id, 'ROO', 'Quintana Roo', 'America/Cancun', true),
|
|
(mexico_id, 'SLP', 'San Luis Potosí', 'America/Mexico_City', true),
|
|
(mexico_id, 'SIN', 'Sinaloa', 'America/Mazatlan', true),
|
|
(mexico_id, 'SON', 'Sonora', 'America/Hermosillo', true),
|
|
(mexico_id, 'TAB', 'Tabasco', 'America/Mexico_City', true),
|
|
(mexico_id, 'TAM', 'Tamaulipas', 'America/Monterrey', true),
|
|
(mexico_id, 'TLA', 'Tlaxcala', 'America/Mexico_City', true),
|
|
(mexico_id, 'VER', 'Veracruz', 'America/Mexico_City', true),
|
|
(mexico_id, 'YUC', 'Yucatán', 'America/Merida', true),
|
|
(mexico_id, 'ZAC', 'Zacatecas', 'America/Mexico_City', true)
|
|
ON CONFLICT (country_id, code) DO NOTHING;
|
|
END IF;
|
|
END $$;
|
|
|
|
-- =====================
|
|
-- MONEDAS (ISO 4217)
|
|
-- =====================
|
|
|
|
INSERT INTO core.currencies (code, name, symbol, decimals, rounding, active) VALUES
|
|
-- Americas
|
|
('MXN', 'Peso Mexicano', '$', 2, 0.01, true),
|
|
('USD', 'Dólar Estadounidense', '$', 2, 0.01, true),
|
|
('CAD', 'Dólar Canadiense', '$', 2, 0.01, true),
|
|
('BRL', 'Real Brasileño', 'R$', 2, 0.01, true),
|
|
('ARS', 'Peso Argentino', '$', 2, 0.01, true),
|
|
('CLP', 'Peso Chileno', '$', 0, 1, true),
|
|
('COP', 'Peso Colombiano', '$', 0, 1, true),
|
|
('PEN', 'Sol Peruano', 'S/', 2, 0.01, true),
|
|
('GTQ', 'Quetzal', 'Q', 2, 0.01, true),
|
|
-- Europa
|
|
('EUR', 'Euro', '€', 2, 0.01, true),
|
|
('GBP', 'Libra Esterlina', '£', 2, 0.01, true),
|
|
('CHF', 'Franco Suizo', 'Fr', 2, 0.01, true),
|
|
-- Asia
|
|
('JPY', 'Yen Japonés', '¥', 0, 1, true),
|
|
('CNY', 'Yuan Chino', '¥', 2, 0.01, true),
|
|
('KRW', 'Won Surcoreano', '₩', 0, 1, true),
|
|
('INR', 'Rupia India', '₹', 2, 0.01, true)
|
|
ON CONFLICT (code) DO NOTHING;
|
|
|
|
-- =====================
|
|
-- TIPOS DE CAMBIO INICIALES (MXN base)
|
|
-- =====================
|
|
|
|
DO $$
|
|
DECLARE
|
|
mxn_id UUID;
|
|
usd_id UUID;
|
|
eur_id UUID;
|
|
cad_id UUID;
|
|
BEGIN
|
|
SELECT id INTO mxn_id FROM core.currencies WHERE code = 'MXN';
|
|
SELECT id INTO usd_id FROM core.currencies WHERE code = 'USD';
|
|
SELECT id INTO eur_id FROM core.currencies WHERE code = 'EUR';
|
|
SELECT id INTO cad_id FROM core.currencies WHERE code = 'CAD';
|
|
|
|
IF mxn_id IS NOT NULL AND usd_id IS NOT NULL THEN
|
|
INSERT INTO core.currency_rates (tenant_id, from_currency_id, to_currency_id, rate, rate_date, source)
|
|
VALUES
|
|
(NULL, usd_id, mxn_id, 17.50, CURRENT_DATE, 'manual'),
|
|
(NULL, eur_id, mxn_id, 19.20, CURRENT_DATE, 'manual'),
|
|
(NULL, cad_id, mxn_id, 13.10, CURRENT_DATE, 'manual')
|
|
ON CONFLICT DO NOTHING;
|
|
END IF;
|
|
END $$;
|
|
|
|
-- =====================
|
|
-- CATEGORIAS DE UOM (Globales)
|
|
-- =====================
|
|
|
|
INSERT INTO core.uom_categories (tenant_id, name, description) VALUES
|
|
(NULL, 'Unidad', 'Unidades contables'),
|
|
(NULL, 'Peso', 'Unidades de peso/masa'),
|
|
(NULL, 'Longitud', 'Unidades de longitud'),
|
|
(NULL, 'Volumen', 'Unidades de volumen'),
|
|
(NULL, 'Área', 'Unidades de área'),
|
|
(NULL, 'Tiempo', 'Unidades de tiempo')
|
|
ON CONFLICT (tenant_id, name) DO NOTHING;
|
|
|
|
-- =====================
|
|
-- UOM (Globales)
|
|
-- =====================
|
|
|
|
DO $$
|
|
DECLARE
|
|
cat_unit UUID;
|
|
cat_peso UUID;
|
|
cat_longitud UUID;
|
|
cat_volumen UUID;
|
|
cat_area UUID;
|
|
cat_tiempo UUID;
|
|
BEGIN
|
|
SELECT id INTO cat_unit FROM core.uom_categories WHERE name = 'Unidad' AND tenant_id IS NULL;
|
|
SELECT id INTO cat_peso FROM core.uom_categories WHERE name = 'Peso' AND tenant_id IS NULL;
|
|
SELECT id INTO cat_longitud FROM core.uom_categories WHERE name = 'Longitud' AND tenant_id IS NULL;
|
|
SELECT id INTO cat_volumen FROM core.uom_categories WHERE name = 'Volumen' AND tenant_id IS NULL;
|
|
SELECT id INTO cat_area FROM core.uom_categories WHERE name = 'Área' AND tenant_id IS NULL;
|
|
SELECT id INTO cat_tiempo FROM core.uom_categories WHERE name = 'Tiempo' AND tenant_id IS NULL;
|
|
|
|
-- Unidad
|
|
IF cat_unit IS NOT NULL THEN
|
|
INSERT INTO core.uom (tenant_id, category_id, name, symbol, uom_type, factor, is_active) VALUES
|
|
(NULL, cat_unit, 'Pieza', 'pz', 'reference', 1, true),
|
|
(NULL, cat_unit, 'Docena', 'doc', 'bigger', 12, true),
|
|
(NULL, cat_unit, 'Ciento', 'cto', 'bigger', 100, true),
|
|
(NULL, cat_unit, 'Millar', 'mil', 'bigger', 1000, true),
|
|
(NULL, cat_unit, 'Par', 'par', 'bigger', 2, true)
|
|
ON CONFLICT (tenant_id, category_id, name) DO NOTHING;
|
|
END IF;
|
|
|
|
-- Peso
|
|
IF cat_peso IS NOT NULL THEN
|
|
INSERT INTO core.uom (tenant_id, category_id, name, symbol, uom_type, factor, is_active) VALUES
|
|
(NULL, cat_peso, 'Kilogramo', 'kg', 'reference', 1, true),
|
|
(NULL, cat_peso, 'Gramo', 'g', 'smaller', 0.001, true),
|
|
(NULL, cat_peso, 'Miligramo', 'mg', 'smaller', 0.000001, true),
|
|
(NULL, cat_peso, 'Tonelada', 't', 'bigger', 1000, true),
|
|
(NULL, cat_peso, 'Libra', 'lb', 'smaller', 0.453592, true),
|
|
(NULL, cat_peso, 'Onza', 'oz', 'smaller', 0.0283495, true)
|
|
ON CONFLICT (tenant_id, category_id, name) DO NOTHING;
|
|
END IF;
|
|
|
|
-- Longitud
|
|
IF cat_longitud IS NOT NULL THEN
|
|
INSERT INTO core.uom (tenant_id, category_id, name, symbol, uom_type, factor, is_active) VALUES
|
|
(NULL, cat_longitud, 'Metro', 'm', 'reference', 1, true),
|
|
(NULL, cat_longitud, 'Centímetro', 'cm', 'smaller', 0.01, true),
|
|
(NULL, cat_longitud, 'Milímetro', 'mm', 'smaller', 0.001, true),
|
|
(NULL, cat_longitud, 'Kilómetro', 'km', 'bigger', 1000, true),
|
|
(NULL, cat_longitud, 'Pulgada', 'in', 'smaller', 0.0254, true),
|
|
(NULL, cat_longitud, 'Pie', 'ft', 'smaller', 0.3048, true),
|
|
(NULL, cat_longitud, 'Yarda', 'yd', 'smaller', 0.9144, true)
|
|
ON CONFLICT (tenant_id, category_id, name) DO NOTHING;
|
|
END IF;
|
|
|
|
-- Volumen
|
|
IF cat_volumen IS NOT NULL THEN
|
|
INSERT INTO core.uom (tenant_id, category_id, name, symbol, uom_type, factor, is_active) VALUES
|
|
(NULL, cat_volumen, 'Litro', 'L', 'reference', 1, true),
|
|
(NULL, cat_volumen, 'Mililitro', 'mL', 'smaller', 0.001, true),
|
|
(NULL, cat_volumen, 'Metro cúbico', 'm³', 'bigger', 1000, true),
|
|
(NULL, cat_volumen, 'Galón US', 'gal', 'bigger', 3.78541, true),
|
|
(NULL, cat_volumen, 'Onza líquida', 'fl oz', 'smaller', 0.0295735, true)
|
|
ON CONFLICT (tenant_id, category_id, name) DO NOTHING;
|
|
END IF;
|
|
|
|
-- Área
|
|
IF cat_area IS NOT NULL THEN
|
|
INSERT INTO core.uom (tenant_id, category_id, name, symbol, uom_type, factor, is_active) VALUES
|
|
(NULL, cat_area, 'Metro cuadrado', 'm²', 'reference', 1, true),
|
|
(NULL, cat_area, 'Centímetro cuadrado', 'cm²', 'smaller', 0.0001, true),
|
|
(NULL, cat_area, 'Kilómetro cuadrado', 'km²', 'bigger', 1000000, true),
|
|
(NULL, cat_area, 'Hectárea', 'ha', 'bigger', 10000, true),
|
|
(NULL, cat_area, 'Pie cuadrado', 'ft²', 'smaller', 0.092903, true)
|
|
ON CONFLICT (tenant_id, category_id, name) DO NOTHING;
|
|
END IF;
|
|
|
|
-- Tiempo
|
|
IF cat_tiempo IS NOT NULL THEN
|
|
INSERT INTO core.uom (tenant_id, category_id, name, symbol, uom_type, factor, is_active) VALUES
|
|
(NULL, cat_tiempo, 'Hora', 'h', 'reference', 1, true),
|
|
(NULL, cat_tiempo, 'Minuto', 'min', 'smaller', 0.0166667, true),
|
|
(NULL, cat_tiempo, 'Día', 'd', 'bigger', 24, true),
|
|
(NULL, cat_tiempo, 'Semana', 'sem', 'bigger', 168, true)
|
|
ON CONFLICT (tenant_id, category_id, name) DO NOTHING;
|
|
END IF;
|
|
END $$;
|
|
|
|
-- =====================
|
|
-- FIN DEL ARCHIVO
|
|
-- =====================
|