-- ============================================================ -- quoERP — Modulo de Produccion / MRP -- Migracion: 032_production_mrp.sql -- ============================================================ -- BOM (Lista de Materiales), Ordenes de Produccion, -- Centros de Trabajo, y calculo MRP. -- Integra con: products, inventory_stock, inventory_movements, -- journal_entries (costeo). -- ============================================================ SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ───────────────────────────────────────────────────────────── -- CENTROS DE TRABAJO — maquinas o areas de produccion -- ───────────────────────────────────────────────────────────── CREATE TABLE IF NOT EXISTS work_centers ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, company_id INT UNSIGNED NOT NULL, code VARCHAR(20) NOT NULL, name VARCHAR(150) NOT NULL, type ENUM('machine','assembly','quality','storage','other') NOT NULL DEFAULT 'assembly', capacity_per_day DECIMAL(10,2) DEFAULT NULL COMMENT 'Capacidad de produccion por dia (unidades)', cost_per_hour DECIMAL(12,4) NOT NULL DEFAULT 0 COMMENT 'Costo operativo por hora (maquina + operario)', is_active TINYINT(1) NOT NULL DEFAULT 1, description TEXT DEFAULT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uk_code (company_id, code), INDEX idx_company (company_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ───────────────────────────────────────────────────────────── -- BOM HEADERS — encabezado de la lista de materiales -- Un producto terminado puede tener una BOM activa -- ───────────────────────────────────────────────────────────── CREATE TABLE IF NOT EXISTS bom_headers ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, company_id INT UNSIGNED NOT NULL, product_id INT UNSIGNED NOT NULL COMMENT 'Producto terminado que se fabrica', version VARCHAR(20) NOT NULL DEFAULT '1.0' COMMENT 'Version de la BOM (1.0, 1.1, 2.0...)', name VARCHAR(150) DEFAULT NULL COMMENT 'Nombre descriptivo de la BOM', qty_base DECIMAL(12,4) NOT NULL DEFAULT 1.0000 COMMENT 'Cantidad base de produccion (normalmente 1)', unit VARCHAR(20) DEFAULT NULL COMMENT 'Unidad del producto terminado', status ENUM('draft','active','obsolete') NOT NULL DEFAULT 'draft', std_time_hours DECIMAL(8,2) DEFAULT NULL COMMENT 'Tiempo estandar de produccion en horas', notes TEXT DEFAULT NULL, created_by INT UNSIGNED DEFAULT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), INDEX idx_company (company_id), INDEX idx_product (product_id), INDEX idx_status (status) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ───────────────────────────────────────────────────────────── -- BOM LINES — componentes / materiales de la BOM -- ───────────────────────────────────────────────────────────── CREATE TABLE IF NOT EXISTS bom_lines ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, bom_id INT UNSIGNED NOT NULL, component_id INT UNSIGNED NOT NULL COMMENT 'Producto/materia prima componente', quantity DECIMAL(12,4) NOT NULL DEFAULT 1.0000 COMMENT 'Cantidad requerida por qty_base del producto terminado', unit VARCHAR(20) DEFAULT NULL, waste_pct DECIMAL(5,2) NOT NULL DEFAULT 0.00 COMMENT 'Porcentaje de merma/desperdicio esperado', work_center_id INT UNSIGNED DEFAULT NULL COMMENT 'Centro de trabajo donde se usa este componente', operation_time DECIMAL(8,2) DEFAULT NULL COMMENT 'Tiempo de operacion en minutos para este componente', notes VARCHAR(300) DEFAULT NULL, sort_order TINYINT UNSIGNED NOT NULL DEFAULT 0, PRIMARY KEY (id), INDEX idx_bom (bom_id), INDEX idx_component (component_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ───────────────────────────────────────────────────────────── -- ORDENES DE PRODUCCION -- ───────────────────────────────────────────────────────────── CREATE TABLE IF NOT EXISTS production_orders ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, company_id INT UNSIGNED NOT NULL, number VARCHAR(30) NOT NULL COMMENT 'Numero interno: OP-YYYY-NNNNN', product_id INT UNSIGNED NOT NULL COMMENT 'Producto terminado a fabricar', product_name VARCHAR(200) NOT NULL COMMENT 'Snapshot', bom_id INT UNSIGNED NOT NULL, qty_planned DECIMAL(12,4) NOT NULL DEFAULT 1.0000 COMMENT 'Cantidad planificada a producir', qty_produced DECIMAL(12,4) NOT NULL DEFAULT 0.0000 COMMENT 'Cantidad realmente producida', qty_wasted DECIMAL(12,4) NOT NULL DEFAULT 0.0000 COMMENT 'Cantidad con defectos / desechada', unit VARCHAR(20) DEFAULT NULL, warehouse_id INT UNSIGNED DEFAULT NULL COMMENT 'Bodega origen de materiales', output_warehouse_id INT UNSIGNED DEFAULT NULL COMMENT 'Bodega destino del producto terminado', planned_start DATE DEFAULT NULL, planned_end DATE DEFAULT NULL, actual_start DATETIME DEFAULT NULL, actual_end DATETIME DEFAULT NULL, status ENUM('draft','planned','in_progress','completed','cancelled') NOT NULL DEFAULT 'draft', -- Costos (calculados al completar) cost_materials DECIMAL(14,4) NOT NULL DEFAULT 0 COMMENT 'Costo real de materiales consumidos', cost_labor DECIMAL(14,4) NOT NULL DEFAULT 0 COMMENT 'Costo de mano de obra', cost_overhead DECIMAL(14,4) NOT NULL DEFAULT 0 COMMENT 'Costos indirectos', cost_total DECIMAL(14,4) NOT NULL DEFAULT 0, cost_per_unit DECIMAL(14,4) NOT NULL DEFAULT 0, -- Contabilidad journal_entry_id INT UNSIGNED DEFAULT NULL, -- Trazabilidad sale_order_id INT UNSIGNED DEFAULT NULL COMMENT 'OV que origino esta OP (opcional)', notes TEXT DEFAULT NULL, created_by INT UNSIGNED DEFAULT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uk_number (company_id, number), INDEX idx_company (company_id), INDEX idx_product (product_id), INDEX idx_status (status), INDEX idx_planned (planned_start) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ───────────────────────────────────────────────────────────── -- LINEAS DE CONSUMO DE LA ORDEN DE PRODUCCION -- Materiales planificados y realmente consumidos -- ───────────────────────────────────────────────────────────── CREATE TABLE IF NOT EXISTS production_order_lines ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, order_id INT UNSIGNED NOT NULL, component_id INT UNSIGNED NOT NULL, component_name VARCHAR(200) NOT NULL COMMENT 'Snapshot', unit VARCHAR(20) DEFAULT NULL, qty_planned DECIMAL(12,4) NOT NULL DEFAULT 0 COMMENT 'Cantidad planificada (de la BOM x qty_planned)', qty_consumed DECIMAL(12,4) NOT NULL DEFAULT 0 COMMENT 'Cantidad realmente consumida', qty_returned DECIMAL(12,4) NOT NULL DEFAULT 0 COMMENT 'Sobrante devuelto al stock', unit_cost DECIMAL(14,4) NOT NULL DEFAULT 0 COMMENT 'Costo unitario al momento de consumo', total_cost DECIMAL(14,4) NOT NULL DEFAULT 0, work_center_id INT UNSIGNED DEFAULT NULL, sort_order TINYINT UNSIGNED NOT NULL DEFAULT 0, PRIMARY KEY (id), INDEX idx_order (order_id), INDEX idx_component (component_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ───────────────────────────────────────────────────────────── -- Columna para marcar productos como productos terminados (con BOM) -- ───────────────────────────────────────────────────────────── ALTER TABLE products ADD COLUMN IF NOT EXISTS is_manufactured TINYINT(1) NOT NULL DEFAULT 0 COMMENT '1 = producto fabricado en planta (tiene BOM)', ADD COLUMN IF NOT EXISTS active_bom_id INT UNSIGNED DEFAULT NULL COMMENT 'BOM activa para este producto'; -- ───────────────────────────────────────────────────────────── -- Cuentas contables para produccion en company_account_map -- ───────────────────────────────────────────────────────────── ALTER TABLE company_account_map ADD COLUMN IF NOT EXISTS acc_wip INT UNSIGNED DEFAULT NULL COMMENT 'Productos en proceso (Work in Progress)', ADD COLUMN IF NOT EXISTS acc_finished_goods INT UNSIGNED DEFAULT NULL COMMENT 'Productos terminados', ADD COLUMN IF NOT EXISTS acc_production_variance INT UNSIGNED DEFAULT NULL COMMENT 'Variacion de costos de produccion'; SET FOREIGN_KEY_CHECKS = 1; -- ───────────────────────────────────────────────────────────── -- FIN DE MIGRACION -- Tablas nuevas: work_centers, bom_headers, bom_lines, -- production_orders, production_order_lines -- ALTER: products (is_manufactured, active_bom_id) -- ALTER: company_account_map (acc_wip, acc_finished_goods) -- ─────────────────────────────────────────────────────────────