57 KiB
57 KiB
SPEC-CONSOLIDACION-FINANCIERA: Consolidación Financiera Multi-Empresa
Metadata
- Código: SPEC-CONSOLIDACION-FINANCIERA
- Versión: 1.0.0
- Fecha: 2025-01-15
- Gap Relacionado: GAP-MGN-002-001
- Módulo: MGN-002 (Empresas y Organizaciones)
- Prioridad: P1
- Story Points: 13
- Odoo Referencia: account (multi-company), account_consolidation
1. Resumen Ejecutivo
1.1 Descripción del Gap
El sistema actual soporta multi-empresa básico pero carece de funcionalidad para consolidación financiera, que permite generar estados financieros combinados de un grupo corporativo, eliminando transacciones intercompany y convirtiendo monedas de subsidiarias extranjeras.
1.2 Impacto en el Negocio
| Aspecto | Sin Consolidación | Con Consolidación |
|---|---|---|
| Reportes grupo | Manual en Excel | Automático en sistema |
| Eliminaciones intercompany | Manual propenso a errores | Automático con reglas |
| Conversión monedas | Cálculo externo | Integrado con CTA |
| Cumplimiento IFRS | Difícil de auditar | Trazabilidad completa |
| Tiempo cierre | Días/semanas | Horas |
1.3 Objetivos de la Especificación
- Implementar jerarquía de empresas (parent_store)
- Diseñar proceso de consolidación por períodos
- Soportar métodos: integral, proporcional, participación
- Automatizar eliminación de transacciones intercompany
- Integrar conversión de moneda con CTA
- Generar estados financieros consolidados
2. Arquitectura de Datos
2.1 Diagrama del Sistema
┌─────────────────────────────────────────────────────────────────────────────┐
│ SISTEMA DE CONSOLIDACIÓN FINANCIERA │
├─────────────────────────────────────────────────────────────────────────────┤
│ │
│ ┌─────────────────────────────────────────────────────────────────────┐ │
│ │ JERARQUÍA CORPORATIVA │ │
│ │ │ │
│ │ Holding Corp (USD) │ │
│ │ │ │ │
│ │ ┌───────────┼───────────┐ │ │
│ │ ▼ ▼ ▼ │ │
│ │ Sub MX Sub EU Sub BR │ │
│ │ (MXN) (EUR) (BRL) │ │
│ │ 100% 100% 80% │ │
│ │ │ │
│ └─────────────────────────────────────────────────────────────────────┘ │
│ │ │
│ ▼ │
│ ┌─────────────────────────────────────────────────────────────────────┐ │
│ │ PROCESO DE CONSOLIDACIÓN │ │
│ │ │ │
│ │ 1. Recopilar 2. Convertir 3. Eliminar 4. Ajustar │ │
│ │ Balances Monedas IC CTA │ │
│ │ │ │
│ │ ┌─────────┐ ┌─────────┐ ┌─────────┐ ┌─────────┐ │ │
│ │ │ Holding │ │ Tasa │ │ Ventas │ │ Diff. │ │ │
│ │ │ Sub MX │ ──▶ │ Actual │ ──▶│ IC │ ──▶│ Cambio │ │ │
│ │ │ Sub EU │ │ Promedio│ │ Présta-│ │ (CTA) │ │ │
│ │ │ Sub BR │ │ Históric│ │ mos │ │ │ │ │
│ │ └─────────┘ └─────────┘ └─────────┘ └─────────┘ │ │
│ └─────────────────────────────────────────────────────────────────────┘ │
│ │ │
│ ▼ │
│ ┌─────────────────────────────────────────────────────────────────────┐ │
│ │ ESTADOS CONSOLIDADOS │ │
│ │ │ │
│ │ Balance General Estado de Resultados Flujo de Caja │ │
│ │ Consolidado Consolidado Consolidado │ │
│ │ │ │
│ └─────────────────────────────────────────────────────────────────────┘ │
│ │
└─────────────────────────────────────────────────────────────────────────────┘
2.2 Definición de Tablas
-- =============================================================================
-- SCHEMA: consolidation
-- =============================================================================
-- -----------------------------------------------------------------------------
-- Extensión de tenants para jerarquía
-- -----------------------------------------------------------------------------
ALTER TABLE tenants ADD COLUMN IF NOT EXISTS parent_id UUID REFERENCES tenants(id);
ALTER TABLE tenants ADD COLUMN IF NOT EXISTS parent_path VARCHAR(255);
ALTER TABLE tenants ADD COLUMN IF NOT EXISTS consolidation_currency_id UUID REFERENCES currencies(id);
ALTER TABLE tenants ADD COLUMN IF NOT EXISTS ownership_percentage DECIMAL(5,2) DEFAULT 100.00;
CREATE INDEX idx_tenants_parent ON tenants(parent_id);
CREATE INDEX idx_tenants_parent_path ON tenants USING GIST (parent_path gist_trgm_ops);
-- -----------------------------------------------------------------------------
-- Tabla: consolidation_groups
-- Descripción: Grupos de consolidación definidos
-- -----------------------------------------------------------------------------
CREATE TABLE consolidation_groups (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
-- Empresa holding
holding_tenant_id UUID NOT NULL REFERENCES tenants(id),
-- Información
name VARCHAR(200) NOT NULL,
description TEXT,
-- Moneda de consolidación
currency_id UUID NOT NULL REFERENCES currencies(id),
-- Configuración
consolidation_method consolidation_method_type DEFAULT 'full',
eliminate_intercompany BOOLEAN DEFAULT TRUE,
use_cta_rates BOOLEAN DEFAULT TRUE, -- Cumulative Translation Adjustment
-- Estado
active BOOLEAN DEFAULT TRUE,
-- Auditoría
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW(),
created_by UUID REFERENCES users(id),
updated_by UUID REFERENCES users(id)
);
CREATE TYPE consolidation_method_type AS ENUM (
'full', -- Consolidación integral (100%)
'proportional', -- Consolidación proporcional (% participación)
'equity' -- Método de participación (una línea)
);
-- -----------------------------------------------------------------------------
-- Tabla: consolidation_group_members
-- Descripción: Empresas miembro del grupo de consolidación
-- -----------------------------------------------------------------------------
CREATE TABLE consolidation_group_members (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
consolidation_group_id UUID NOT NULL REFERENCES consolidation_groups(id) ON DELETE CASCADE,
tenant_id UUID NOT NULL REFERENCES tenants(id),
-- Participación
ownership_percentage DECIMAL(5,2) NOT NULL DEFAULT 100.00,
consolidation_method consolidation_method_type, -- Override del grupo
-- Mapeo de cuentas (si difiere del holding)
use_account_mapping BOOLEAN DEFAULT FALSE,
-- Fecha de adquisición (para goodwill)
acquisition_date DATE,
acquisition_cost DECIMAL(20,4),
-- Estado
active BOOLEAN DEFAULT TRUE,
CONSTRAINT uq_consolidation_member UNIQUE(consolidation_group_id, tenant_id),
CONSTRAINT chk_ownership CHECK (ownership_percentage > 0 AND ownership_percentage <= 100)
);
CREATE INDEX idx_cg_members_group ON consolidation_group_members(consolidation_group_id);
CREATE INDEX idx_cg_members_tenant ON consolidation_group_members(tenant_id);
-- -----------------------------------------------------------------------------
-- Tabla: consolidation_account_mappings
-- Descripción: Mapeo de cuentas entre subsidiaria y holding
-- -----------------------------------------------------------------------------
CREATE TABLE consolidation_account_mappings (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
consolidation_group_id UUID NOT NULL REFERENCES consolidation_groups(id) ON DELETE CASCADE,
-- Cuenta subsidiaria
source_tenant_id UUID NOT NULL REFERENCES tenants(id),
source_account_code VARCHAR(64) NOT NULL,
-- Cuenta consolidada (holding)
target_account_code VARCHAR(64) NOT NULL,
-- Factor de ajuste (normalmente 1.0)
adjustment_factor DECIMAL(10,6) DEFAULT 1.0,
CONSTRAINT uq_account_mapping UNIQUE(consolidation_group_id, source_tenant_id, source_account_code)
);
-- -----------------------------------------------------------------------------
-- Tabla: consolidation_periods
-- Descripción: Períodos de consolidación ejecutados
-- -----------------------------------------------------------------------------
CREATE TABLE consolidation_periods (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
consolidation_group_id UUID NOT NULL REFERENCES consolidation_groups(id),
-- Período
name VARCHAR(50) NOT NULL, -- '2024-Q1', '2024-12', etc.
date_from DATE NOT NULL,
date_to DATE NOT NULL,
-- Estado
state consolidation_state NOT NULL DEFAULT 'draft',
-- Tasas usadas
exchange_rate_date DATE, -- Fecha para tasa de cierre
-- Resultados
total_assets DECIMAL(20,4),
total_liabilities DECIMAL(20,4),
total_equity DECIMAL(20,4),
net_income DECIMAL(20,4),
cta_adjustment DECIMAL(20,4), -- Cumulative Translation Adjustment
-- Auditoría
executed_at TIMESTAMPTZ,
executed_by UUID REFERENCES users(id),
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW(),
CONSTRAINT uq_consolidation_period UNIQUE(consolidation_group_id, date_from, date_to)
);
CREATE TYPE consolidation_state AS ENUM ('draft', 'in_progress', 'completed', 'cancelled');
CREATE INDEX idx_consolidation_periods_group ON consolidation_periods(consolidation_group_id);
CREATE INDEX idx_consolidation_periods_dates ON consolidation_periods(date_from, date_to);
-- -----------------------------------------------------------------------------
-- Tabla: consolidation_lines
-- Descripción: Líneas del balance consolidado
-- -----------------------------------------------------------------------------
CREATE TABLE consolidation_lines (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
consolidation_period_id UUID NOT NULL REFERENCES consolidation_periods(id) ON DELETE CASCADE,
-- Cuenta consolidada
account_code VARCHAR(64) NOT NULL,
account_name VARCHAR(200) NOT NULL,
account_type account_type NOT NULL,
-- Montos por empresa (antes de conversión)
tenant_balances JSONB NOT NULL DEFAULT '{}',
-- Formato: {"tenant_id": {"original": 1000, "converted": 920, "rate": 0.92}}
-- Totales consolidados (en moneda de consolidación)
debit_consolidated DECIMAL(20,4) DEFAULT 0,
credit_consolidated DECIMAL(20,4) DEFAULT 0,
balance_consolidated DECIMAL(20,4) DEFAULT 0,
-- Ajustes
intercompany_elimination DECIMAL(20,4) DEFAULT 0,
cta_adjustment DECIMAL(20,4) DEFAULT 0,
minority_interest DECIMAL(20,4) DEFAULT 0,
-- Balance final
balance_final DECIMAL(20,4) DEFAULT 0
);
CREATE INDEX idx_consolidation_lines_period ON consolidation_lines(consolidation_period_id);
CREATE INDEX idx_consolidation_lines_account ON consolidation_lines(account_code);
-- -----------------------------------------------------------------------------
-- Tabla: intercompany_transactions
-- Descripción: Transacciones intercompany identificadas
-- -----------------------------------------------------------------------------
CREATE TABLE intercompany_transactions (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
consolidation_period_id UUID NOT NULL REFERENCES consolidation_periods(id) ON DELETE CASCADE,
-- Empresa A (vendedor/acreedor)
tenant_a_id UUID NOT NULL REFERENCES tenants(id),
account_a_code VARCHAR(64) NOT NULL,
amount_a DECIMAL(20,4) NOT NULL,
-- Empresa B (comprador/deudor)
tenant_b_id UUID NOT NULL REFERENCES tenants(id),
account_b_code VARCHAR(64) NOT NULL,
amount_b DECIMAL(20,4) NOT NULL,
-- Tipo de transacción
transaction_type intercompany_type NOT NULL,
-- Estado de eliminación
eliminated BOOLEAN DEFAULT FALSE,
elimination_amount DECIMAL(20,4),
-- Referencia a movimientos originales
move_line_a_id UUID,
move_line_b_id UUID
);
CREATE TYPE intercompany_type AS ENUM (
'sale_purchase', -- Venta A → Compra B
'loan', -- Préstamo intercompany
'dividend', -- Dividendos
'service_fee', -- Cargos por servicios
'royalty', -- Regalías
'other'
);
CREATE INDEX idx_ic_transactions_period ON intercompany_transactions(consolidation_period_id);
CREATE INDEX idx_ic_transactions_tenants ON intercompany_transactions(tenant_a_id, tenant_b_id);
-- -----------------------------------------------------------------------------
-- Tabla: currency_rate_table (temporal para consolidación)
-- Descripción: Tasas de cambio para el período de consolidación
-- -----------------------------------------------------------------------------
CREATE TABLE consolidation_currency_rates (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
consolidation_period_id UUID NOT NULL REFERENCES consolidation_periods(id) ON DELETE CASCADE,
-- Empresa
tenant_id UUID NOT NULL REFERENCES tenants(id),
source_currency_id UUID NOT NULL REFERENCES currencies(id),
-- Tasas
rate_closing DECIMAL(20,10) NOT NULL, -- Tasa al cierre (activos/pasivos)
rate_average DECIMAL(20,10) NOT NULL, -- Tasa promedio (P&L)
rate_historical DECIMAL(20,10), -- Tasa histórica (patrimonio)
CONSTRAINT uq_consolidation_rate UNIQUE(consolidation_period_id, tenant_id)
);
3. Lógica de Negocio
3.1 Motor de Consolidación
# services/consolidation_engine.py
from decimal import Decimal
from typing import Dict, List, Optional
from dataclasses import dataclass
from datetime import date
from enum import Enum
class ConsolidationMethod(Enum):
FULL = 'full'
PROPORTIONAL = 'proportional'
EQUITY = 'equity'
@dataclass
class ConsolidationResult:
period_id: str
total_assets: Decimal
total_liabilities: Decimal
total_equity: Decimal
net_income: Decimal
cta_adjustment: Decimal
lines: List[Dict]
eliminations: List[Dict]
class ConsolidationEngine:
"""Motor de consolidación financiera multi-empresa."""
def __init__(self, db_session):
self.db = db_session
async def execute_consolidation(
self,
group_id: str,
date_from: date,
date_to: date,
rate_date: Optional[date] = None
) -> ConsolidationResult:
"""
Ejecuta el proceso de consolidación para un período.
Pasos:
1. Obtener configuración del grupo
2. Recopilar balances de todas las empresas
3. Crear tabla de tasas de cambio
4. Convertir monedas
5. Identificar transacciones intercompany
6. Aplicar eliminaciones
7. Calcular CTA
8. Generar balance consolidado
"""
# 1. Configuración
group = await self.db.get_consolidation_group(group_id)
members = await self.db.get_group_members(group_id)
rate_date = rate_date or date_to
# 2. Crear período
period = await self.db.create_consolidation_period(
group_id=group_id,
date_from=date_from,
date_to=date_to,
state='in_progress'
)
try:
# 3. Recopilar balances
tenant_balances = await self._collect_balances(
members=members,
date_from=date_from,
date_to=date_to
)
# 4. Crear tasas de cambio
rates = await self._create_rate_table(
period_id=period['id'],
members=members,
target_currency_id=group['currency_id'],
date_from=date_from,
date_to=date_to,
rate_date=rate_date
)
# 5. Convertir y consolidar
consolidated_lines = await self._convert_and_consolidate(
tenant_balances=tenant_balances,
rates=rates,
members=members,
method=group['consolidation_method']
)
# 6. Identificar transacciones intercompany
if group['eliminate_intercompany']:
ic_transactions = await self._identify_intercompany(
period_id=period['id'],
members=members,
date_from=date_from,
date_to=date_to
)
# 7. Aplicar eliminaciones
consolidated_lines = await self._apply_eliminations(
lines=consolidated_lines,
ic_transactions=ic_transactions
)
# 8. Calcular CTA
if group['use_cta_rates']:
cta = await self._calculate_cta(
period_id=period['id'],
lines=consolidated_lines,
rates=rates
)
else:
cta = Decimal('0')
# 9. Guardar resultados
await self._save_consolidation_lines(period['id'], consolidated_lines)
# 10. Calcular totales
totals = self._calculate_totals(consolidated_lines)
# 11. Actualizar período
await self.db.update_consolidation_period(
period_id=period['id'],
state='completed',
total_assets=totals['assets'],
total_liabilities=totals['liabilities'],
total_equity=totals['equity'],
net_income=totals['net_income'],
cta_adjustment=cta
)
return ConsolidationResult(
period_id=period['id'],
total_assets=totals['assets'],
total_liabilities=totals['liabilities'],
total_equity=totals['equity'],
net_income=totals['net_income'],
cta_adjustment=cta,
lines=consolidated_lines,
eliminations=ic_transactions if group['eliminate_intercompany'] else []
)
except Exception as e:
await self.db.update_consolidation_period(
period_id=period['id'],
state='cancelled'
)
raise
async def _collect_balances(
self,
members: List[Dict],
date_from: date,
date_to: date
) -> Dict[str, List[Dict]]:
"""Recopila balances de todas las empresas miembro."""
balances = {}
for member in members:
tenant_id = member['tenant_id']
# Obtener trial balance de la empresa
trial_balance = await self.db.get_trial_balance(
tenant_id=tenant_id,
date_from=date_from,
date_to=date_to
)
balances[tenant_id] = trial_balance
return balances
async def _create_rate_table(
self,
period_id: str,
members: List[Dict],
target_currency_id: str,
date_from: date,
date_to: date,
rate_date: date
) -> Dict[str, Dict]:
"""Crea tabla de tasas de cambio para el período."""
rates = {}
target_currency = await self.db.get_currency(target_currency_id)
for member in members:
tenant = await self.db.get_tenant(member['tenant_id'])
source_currency_id = tenant['currency_id']
if source_currency_id == target_currency_id:
# Misma moneda, tasa = 1
rates[member['tenant_id']] = {
'closing': Decimal('1.0'),
'average': Decimal('1.0'),
'historical': Decimal('1.0')
}
else:
# Obtener tasas
rate_closing = await self.db.get_exchange_rate(
from_currency=source_currency_id,
to_currency=target_currency_id,
date=rate_date
)
rate_average = await self.db.get_average_exchange_rate(
from_currency=source_currency_id,
to_currency=target_currency_id,
date_from=date_from,
date_to=date_to
)
# Tasa histórica (patrimonio inicial)
rate_historical = await self.db.get_exchange_rate(
from_currency=source_currency_id,
to_currency=target_currency_id,
date=member.get('acquisition_date') or date_from
)
rates[member['tenant_id']] = {
'closing': rate_closing,
'average': rate_average,
'historical': rate_historical
}
# Guardar en BD
await self.db.create_consolidation_rate(
period_id=period_id,
tenant_id=member['tenant_id'],
source_currency_id=source_currency_id,
rate_closing=rate_closing,
rate_average=rate_average,
rate_historical=rate_historical
)
return rates
async def _convert_and_consolidate(
self,
tenant_balances: Dict[str, List[Dict]],
rates: Dict[str, Dict],
members: List[Dict],
method: str
) -> List[Dict]:
"""Convierte monedas y consolida balances."""
# Diccionario para acumular por cuenta
consolidated = {}
for member in members:
tenant_id = member['tenant_id']
balances = tenant_balances.get(tenant_id, [])
tenant_rates = rates.get(tenant_id, {})
ownership = Decimal(str(member['ownership_percentage'])) / 100
member_method = member.get('consolidation_method') or method
for line in balances:
account_code = line['account_code']
account_type = line['account_type']
# Determinar tasa según tipo de cuenta
if account_type.startswith('asset') or account_type.startswith('liability'):
rate = tenant_rates.get('closing', Decimal('1'))
elif account_type.startswith('equity'):
rate = tenant_rates.get('historical', Decimal('1'))
else: # income, expense
rate = tenant_rates.get('average', Decimal('1'))
# Convertir monto
original_balance = Decimal(str(line['balance']))
converted_balance = original_balance * rate
# Aplicar porcentaje según método
if member_method == 'proportional':
final_balance = converted_balance * ownership
elif member_method == 'equity':
# Solo para cuentas de inversión, se maneja aparte
continue
else: # full
final_balance = converted_balance
# Acumular
if account_code not in consolidated:
consolidated[account_code] = {
'account_code': account_code,
'account_name': line['account_name'],
'account_type': account_type,
'tenant_balances': {},
'debit_consolidated': Decimal('0'),
'credit_consolidated': Decimal('0'),
'balance_consolidated': Decimal('0'),
'intercompany_elimination': Decimal('0'),
'cta_adjustment': Decimal('0'),
'minority_interest': Decimal('0'),
'balance_final': Decimal('0')
}
consolidated[account_code]['tenant_balances'][tenant_id] = {
'original': float(original_balance),
'converted': float(converted_balance),
'rate': float(rate),
'ownership': float(ownership)
}
consolidated[account_code]['balance_consolidated'] += final_balance
# Calcular interés minoritario si < 100%
if ownership < 1 and member_method == 'full':
minority = converted_balance * (1 - ownership)
consolidated[account_code]['minority_interest'] += minority
# Calcular balance final
for line in consolidated.values():
line['balance_final'] = (
line['balance_consolidated'] -
line['intercompany_elimination'] +
line['cta_adjustment'] -
line['minority_interest']
)
return list(consolidated.values())
async def _identify_intercompany(
self,
period_id: str,
members: List[Dict],
date_from: date,
date_to: date
) -> List[Dict]:
"""Identifica transacciones intercompany para eliminar."""
ic_transactions = []
tenant_ids = [m['tenant_id'] for m in members]
# Buscar ventas intercompany
for member_a in members:
for member_b in members:
if member_a['tenant_id'] == member_b['tenant_id']:
continue
# Buscar facturas de A a B
invoices = await self.db.get_intercompany_invoices(
seller_tenant_id=member_a['tenant_id'],
buyer_tenant_id=member_b['tenant_id'],
date_from=date_from,
date_to=date_to
)
for invoice in invoices:
ic_transactions.append({
'consolidation_period_id': period_id,
'tenant_a_id': member_a['tenant_id'],
'account_a_code': invoice['revenue_account'],
'amount_a': invoice['amount'],
'tenant_b_id': member_b['tenant_id'],
'account_b_code': invoice['expense_account'],
'amount_b': invoice['amount'],
'transaction_type': 'sale_purchase',
'eliminated': False
})
# Guardar transacciones identificadas
for ic in ic_transactions:
await self.db.create_intercompany_transaction(ic)
return ic_transactions
async def _apply_eliminations(
self,
lines: List[Dict],
ic_transactions: List[Dict]
) -> List[Dict]:
"""Aplica eliminaciones de transacciones intercompany."""
# Crear índice por cuenta
lines_by_account = {l['account_code']: l for l in lines}
for ic in ic_transactions:
# Eliminar en cuenta A (ingreso)
if ic['account_a_code'] in lines_by_account:
lines_by_account[ic['account_a_code']]['intercompany_elimination'] += Decimal(str(ic['amount_a']))
# Eliminar en cuenta B (gasto)
if ic['account_b_code'] in lines_by_account:
lines_by_account[ic['account_b_code']]['intercompany_elimination'] -= Decimal(str(ic['amount_b']))
ic['eliminated'] = True
ic['elimination_amount'] = ic['amount_a']
# Recalcular balance final
for line in lines:
line['balance_final'] = (
line['balance_consolidated'] -
line['intercompany_elimination'] +
line['cta_adjustment'] -
line['minority_interest']
)
return lines
async def _calculate_cta(
self,
period_id: str,
lines: List[Dict],
rates: Dict[str, Dict]
) -> Decimal:
"""
Calcula el Cumulative Translation Adjustment (CTA).
CTA = Diferencia entre:
- Activos/Pasivos convertidos a tasa de cierre
- P&L convertido a tasa promedio
- Patrimonio convertido a tasa histórica
"""
total_cta = Decimal('0')
for line in lines:
account_type = line['account_type']
if account_type.startswith('equity'):
# Diferencia entre tasa histórica y cierre
for tenant_id, tenant_data in line['tenant_balances'].items():
rate_hist = rates[tenant_id]['historical']
rate_close = rates[tenant_id]['closing']
if rate_hist != rate_close:
original = Decimal(str(tenant_data['original']))
diff = original * (rate_close - rate_hist)
line['cta_adjustment'] += diff
total_cta += diff
return total_cta
def _calculate_totals(self, lines: List[Dict]) -> Dict[str, Decimal]:
"""Calcula totales del balance consolidado."""
totals = {
'assets': Decimal('0'),
'liabilities': Decimal('0'),
'equity': Decimal('0'),
'revenue': Decimal('0'),
'expenses': Decimal('0'),
'net_income': Decimal('0')
}
for line in lines:
account_type = line['account_type']
balance = line['balance_final']
if account_type.startswith('asset'):
totals['assets'] += balance
elif account_type.startswith('liability'):
totals['liabilities'] += balance
elif account_type.startswith('equity'):
totals['equity'] += balance
elif account_type.startswith('income'):
totals['revenue'] += balance
elif account_type.startswith('expense'):
totals['expenses'] += balance
totals['net_income'] = totals['revenue'] - totals['expenses']
return totals
3.2 Generador de Reportes Consolidados
# services/consolidated_report_generator.py
from typing import Dict, List
from decimal import Decimal
class ConsolidatedReportGenerator:
"""Genera estados financieros consolidados."""
def __init__(self, db_session):
self.db = db_session
async def generate_balance_sheet(
self,
period_id: str,
format: str = 'standard'
) -> Dict:
"""Genera Balance General Consolidado."""
lines = await self.db.get_consolidation_lines(period_id)
period = await self.db.get_consolidation_period(period_id)
# Agrupar por tipo
assets = []
liabilities = []
equity = []
for line in lines:
account_type = line['account_type']
item = {
'code': line['account_code'],
'name': line['account_name'],
'balance': line['balance_final'],
'details': {
'consolidated': line['balance_consolidated'],
'eliminations': line['intercompany_elimination'],
'cta': line['cta_adjustment'],
'minority': line['minority_interest']
}
}
if account_type.startswith('asset'):
assets.append(item)
elif account_type.startswith('liability'):
liabilities.append(item)
elif account_type.startswith('equity'):
equity.append(item)
total_assets = sum(a['balance'] for a in assets)
total_liabilities = sum(l['balance'] for l in liabilities)
total_equity = sum(e['balance'] for e in equity)
return {
'report_type': 'balance_sheet',
'period': {
'name': period['name'],
'date_from': str(period['date_from']),
'date_to': str(period['date_to'])
},
'assets': {
'items': assets,
'total': float(total_assets)
},
'liabilities': {
'items': liabilities,
'total': float(total_liabilities)
},
'equity': {
'items': equity,
'total': float(total_equity),
'cta_adjustment': float(period['cta_adjustment'])
},
'total_liabilities_equity': float(total_liabilities + total_equity),
'is_balanced': abs(total_assets - (total_liabilities + total_equity)) < 0.01
}
async def generate_income_statement(
self,
period_id: str
) -> Dict:
"""Genera Estado de Resultados Consolidado."""
lines = await self.db.get_consolidation_lines(period_id)
period = await self.db.get_consolidation_period(period_id)
revenue = []
cost_of_sales = []
operating_expenses = []
other_income = []
other_expenses = []
for line in lines:
account_type = line['account_type']
item = {
'code': line['account_code'],
'name': line['account_name'],
'amount': line['balance_final'],
'eliminations': line['intercompany_elimination']
}
if account_type == 'income':
revenue.append(item)
elif account_type == 'expense_direct_cost':
cost_of_sales.append(item)
elif account_type == 'expense':
operating_expenses.append(item)
elif account_type == 'income_other':
other_income.append(item)
elif account_type in ('expense_depreciation',):
other_expenses.append(item)
total_revenue = sum(r['amount'] for r in revenue)
total_cos = sum(c['amount'] for c in cost_of_sales)
gross_profit = total_revenue - total_cos
total_opex = sum(o['amount'] for o in operating_expenses)
operating_income = gross_profit - total_opex
total_other_income = sum(o['amount'] for o in other_income)
total_other_expense = sum(o['amount'] for o in other_expenses)
net_income = operating_income + total_other_income - total_other_expense
return {
'report_type': 'income_statement',
'period': {
'name': period['name'],
'date_from': str(period['date_from']),
'date_to': str(period['date_to'])
},
'revenue': {
'items': revenue,
'total': float(total_revenue)
},
'cost_of_sales': {
'items': cost_of_sales,
'total': float(total_cos)
},
'gross_profit': float(gross_profit),
'operating_expenses': {
'items': operating_expenses,
'total': float(total_opex)
},
'operating_income': float(operating_income),
'other_income': {
'items': other_income,
'total': float(total_other_income)
},
'other_expenses': {
'items': other_expenses,
'total': float(total_other_expense)
},
'net_income': float(net_income)
}
4. API REST
4.1 Endpoints
# Grupos de Consolidación
POST /api/v1/consolidation-groups:
summary: Crear grupo de consolidación
body:
name: string (required)
holding_tenant_id: uuid (required)
currency_id: uuid (required)
consolidation_method: enum[full, proportional, equity]
eliminate_intercompany: boolean
use_cta_rates: boolean
response: ConsolidationGroup
GET /api/v1/consolidation-groups:
summary: Listar grupos de consolidación
response: ConsolidationGroup[]
GET /api/v1/consolidation-groups/{id}:
summary: Obtener grupo con miembros
response: ConsolidationGroupDetail
# Miembros
POST /api/v1/consolidation-groups/{id}/members:
summary: Agregar empresa al grupo
body:
tenant_id: uuid (required)
ownership_percentage: decimal (required)
consolidation_method: enum (optional override)
acquisition_date: date
acquisition_cost: decimal
response: ConsolidationGroupMember
DELETE /api/v1/consolidation-groups/{group_id}/members/{tenant_id}:
summary: Remover empresa del grupo
response: { success: boolean }
# Mapeo de Cuentas
POST /api/v1/consolidation-groups/{id}/account-mappings:
summary: Crear mapeo de cuenta
body:
source_tenant_id: uuid
source_account_code: string
target_account_code: string
response: AccountMapping
GET /api/v1/consolidation-groups/{id}/account-mappings:
summary: Listar mapeos
response: AccountMapping[]
# Períodos de Consolidación
POST /api/v1/consolidation-groups/{id}/consolidate:
summary: Ejecutar consolidación para período
body:
date_from: date (required)
date_to: date (required)
exchange_rate_date: date
response:
period_id: uuid
status: string
total_assets: decimal
total_liabilities: decimal
total_equity: decimal
net_income: decimal
cta_adjustment: decimal
GET /api/v1/consolidation-periods:
summary: Listar períodos de consolidación
params:
group_id: uuid
state: enum
response: ConsolidationPeriod[]
GET /api/v1/consolidation-periods/{id}:
summary: Obtener detalle del período
response: ConsolidationPeriodDetail
GET /api/v1/consolidation-periods/{id}/lines:
summary: Obtener líneas consolidadas
response: ConsolidationLine[]
GET /api/v1/consolidation-periods/{id}/intercompany:
summary: Obtener transacciones intercompany
response: IntercompanyTransaction[]
# Reportes
GET /api/v1/consolidation-periods/{id}/balance-sheet:
summary: Generar Balance General Consolidado
params:
format: enum[standard, detailed]
response: BalanceSheetReport
GET /api/v1/consolidation-periods/{id}/income-statement:
summary: Generar Estado de Resultados Consolidado
response: IncomeStatementReport
GET /api/v1/consolidation-periods/{id}/export:
summary: Exportar consolidación
params:
format: enum[xlsx, pdf]
response: binary
5. Interfaz de Usuario
5.1 Configuración de Grupo
┌─────────────────────────────────────────────────────────────────────────────┐
│ Contabilidad > Consolidación > Grupos de Consolidación │
├─────────────────────────────────────────────────────────────────────────────┤
│ │
│ Grupo: Corporativo ACME [Guardar] [✕] │
│ │
│ ┌──────────────────────────────────────────────────────────────────────┐ │
│ │ Configuración General │ │
│ ├──────────────────────────────────────────────────────────────────────┤ │
│ │ │ │
│ │ Nombre: [Corporativo ACME ] │ │
│ │ │ │
│ │ Empresa Holding: [ACME Corp (USA) ▼] │ │
│ │ │ │
│ │ Moneda Consolidación: [USD - Dólar Estadounidense ▼] │ │
│ │ │ │
│ │ Método: (●) Consolidación Integral │ │
│ │ ( ) Consolidación Proporcional │ │
│ │ ( ) Método de Participación │ │
│ │ │ │
│ │ [✓] Eliminar transacciones intercompany │ │
│ │ [✓] Usar CTA (Cumulative Translation Adjustment) │ │
│ │ │ │
│ └──────────────────────────────────────────────────────────────────────┘ │
│ │
│ ┌──────────────────────────────────────────────────────────────────────┐ │
│ │ Empresas del Grupo [+ Agregar] │ │
│ ├──────────────────────────────────────────────────────────────────────┤ │
│ │ │ │
│ │ ┌────────────────────┬──────────┬───────────┬────────────┬────────┐ │ │
│ │ │ Empresa │ Moneda │ % Partic. │ Método │ Acción │ │ │
│ │ ├────────────────────┼──────────┼───────────┼────────────┼────────┤ │ │
│ │ │ ACME Corp (USA) │ USD │ 100% │ Holding │ │ │ │
│ │ │ ACME México │ MXN │ 100% │ Integral │ [🗑] │ │ │
│ │ │ ACME Europa │ EUR │ 100% │ Integral │ [🗑] │ │ │
│ │ │ ACME Brasil │ BRL │ 80% │ Integral │ [🗑] │ │ │
│ │ │ Partner Joint V. │ USD │ 50% │ Proporcio. │ [🗑] │ │ │
│ │ └────────────────────┴──────────┴───────────┴────────────┴────────┘ │ │
│ │ │ │
│ └──────────────────────────────────────────────────────────────────────┘ │
│ │
└─────────────────────────────────────────────────────────────────────────────┘
5.2 Ejecutar Consolidación
┌─────────────────────────────────────────────────────────────────────────────┐
│ Ejecutar Consolidación │
├─────────────────────────────────────────────────────────────────────────────┤
│ │
│ Grupo: Corporativo ACME │
│ │
│ ┌──────────────────────────────────────────────────────────────────────┐ │
│ │ Período │ │
│ ├──────────────────────────────────────────────────────────────────────┤ │
│ │ │ │
│ │ Fecha Desde: [2024-01-01 📅] │ │
│ │ │ │
│ │ Fecha Hasta: [2024-12-31 📅] │ │
│ │ │ │
│ │ Fecha Tasa Cierre: [2024-12-31 📅] │ │
│ │ (Para conversión de activos/pasivos) │ │
│ │ │ │
│ └──────────────────────────────────────────────────────────────────────┘ │
│ │
│ ┌──────────────────────────────────────────────────────────────────────┐ │
│ │ Tasas de Cambio a Utilizar │ │
│ ├──────────────────────────────────────────────────────────────────────┤ │
│ │ │ │
│ │ ┌────────────────┬──────────┬──────────┬──────────┐ │ │
│ │ │ Empresa │ Cierre │ Promedio │ Histórica│ │ │
│ │ ├────────────────┼──────────┼──────────┼──────────┤ │ │
│ │ │ ACME México │ 17.0500 │ 17.1234 │ 16.8000 │ │ │
│ │ │ ACME Europa │ 1.0850 │ 1.0912 │ 1.0700 │ │ │
│ │ │ ACME Brasil │ 4.9200 │ 4.9850 │ 5.1000 │ │ │
│ │ └────────────────┴──────────┴──────────┴──────────┘ │ │
│ │ │ │
│ └──────────────────────────────────────────────────────────────────────┘ │
│ │
│ [Cancelar] [Ejecutar Consolidación] │
│ │
└─────────────────────────────────────────────────────────────────────────────┘
5.3 Balance Consolidado
┌─────────────────────────────────────────────────────────────────────────────┐
│ Balance General Consolidado - Corporativo ACME │
│ Período: 2024-01-01 a 2024-12-31 [Excel] [PDF] [🖨] │
├─────────────────────────────────────────────────────────────────────────────┤
│ │
│ ACTIVOS │
│ ─────────────────────────────────────────────────────────────────────── │
│ │ Cuenta │ Consolidado │ Eliminaciones │ CTA │ Final │ │
│ ├─────────────────────┼─────────────┼───────────────┼─────────┼─────────┤ │
│ │ Efectivo y Bancos │ 5,234,000 │ 0 │ 12,500 │5,246,500│ │
│ │ Cuentas por Cobrar │ 8,125,000 │ (450,000) │ 8,200 │7,683,200│ │
│ │ Inventarios │ 3,890,000 │ 0 │ 15,300 │3,905,300│ │
│ │ Activo Fijo Neto │ 12,500,000 │ 0 │ 45,000 │12,545,00│ │
│ ├─────────────────────┼─────────────┼───────────────┼─────────┼─────────┤ │
│ │ TOTAL ACTIVOS │ 29,749,000 │ (450,000) │ 81,000 │29,380,00│ │
│ └─────────────────────┴─────────────┴───────────────┴─────────┴─────────┘ │
│ │
│ PASIVOS Y PATRIMONIO │
│ ─────────────────────────────────────────────────────────────────────── │
│ │ Cuenta │ Consolidado │ Eliminaciones │ CTA │ Final │ │
│ ├─────────────────────┼─────────────┼───────────────┼─────────┼─────────┤ │
│ │ Cuentas por Pagar │ 4,250,000 │ (450,000) │ 5,200 │3,805,200│ │
│ │ Deuda Financiera │ 8,000,000 │ 0 │ 22,000 │8,022,000│ │
│ │ Capital Social │ 10,000,000 │ 0 │ 0 │10,000,00│ │
│ │ Utilidades Acumulad │ 6,500,000 │ 0 │ 53,800 │6,553,800│ │
│ │ Interés Minoritario │ 999,000 │ 0 │ 0 │ 999,000│ │
│ ├─────────────────────┼─────────────┼───────────────┼─────────┼─────────┤ │
│ │ TOTAL PAS + PAT │ 29,749,000 │ (450,000) │ 81,000 │29,380,00│ │
│ └─────────────────────┴─────────────┴───────────────┴─────────┴─────────┘ │
│ │
│ ✓ Balance Cuadrado │
│ │
└─────────────────────────────────────────────────────────────────────────────┘
6. Casos de Prueba
6.1 Consolidación Integral
async def test_full_consolidation():
"""Prueba consolidación integral (100%)."""
engine = ConsolidationEngine(db)
# Crear grupo con 2 empresas
group = await db.create_consolidation_group({
'name': 'Test Group',
'holding_tenant_id': holding_id,
'currency_id': usd_id,
'consolidation_method': 'full'
})
await db.add_group_member(group['id'], {
'tenant_id': subsidiary_mx_id,
'ownership_percentage': 100
})
# Ejecutar consolidación
result = await engine.execute_consolidation(
group_id=group['id'],
date_from=date(2024, 1, 1),
date_to=date(2024, 12, 31)
)
assert result.total_assets > 0
assert abs(result.total_assets - (result.total_liabilities + result.total_equity)) < 0.01
6.2 Eliminación Intercompany
async def test_intercompany_elimination():
"""Prueba eliminación de transacciones intercompany."""
# Crear venta de A a B por $100,000
await create_intercompany_invoice(
seller_tenant=holding_id,
buyer_tenant=subsidiary_id,
amount=100000
)
result = await engine.execute_consolidation(
group_id=group_id,
date_from=date(2024, 1, 1),
date_to=date(2024, 12, 31)
)
# Verificar que la venta fue eliminada
revenue_line = next(l for l in result.lines if l['account_type'] == 'income')
assert revenue_line['intercompany_elimination'] == 100000
7. Plan de Implementación
Fase 1: Jerarquía de Empresas (3 SP)
- Extender modelo de tenants con parent_id
- Implementar parent_path
- Control de acceso jerárquico
Fase 2: Motor de Consolidación (5 SP)
- Recopilación de balances
- Conversión de monedas
- Cálculo de totales
Fase 3: Eliminaciones y CTA (3 SP)
- Identificación intercompany
- Aplicación de eliminaciones
- Cálculo de CTA
Fase 4: Reportes (2 SP)
- Balance consolidado
- Estado de resultados
- Exportación
8. Referencias
- Odoo 18.0:
account/models/company.py,account_report.py - IFRS 10: Consolidated Financial Statements
- IAS 21: Effects of Changes in Foreign Exchange Rates
- IAS 28: Investments in Associates and Joint Ventures