Hermes CI Fix
8631ce718f
ci/woodpecker/push/main Pipeline failed
Kaliber: wecom-finance 2026-10-02 ruling (Boss 11:59 CST "li; but (1) add real ROI"). - kpi_definitions seed scripts/sql/profitability_kpi_defs_20261003.sql: F_ROI renamed to F_ROA (same kpi_id=20, all 8 historical values kept), new F_ROI (real IMA caliber = operating profit / invested capital), F_EVA activated with unit and thresholds aligned to 万元, new F_RI (residual income). - parameters are NOT hardcoded in formula text: they live in kpi_definitions.data_source_config.params (tax_rate / wacc / min_return_rate / include_factory_subsidy / include_channel_subsidy / include_sales_discount / annualize / capital_basis); single reader = app/utils/profitability_params.py. - deriver scripts/kpi_profitability_deriver.py: YTD operating profit from voucher_details (carry_forward=0 net), invested capital from subject_balances level-1 rows only, idempotent upsert into kpi_values (source_type=derived, no data_status downgrade, fail-closed when invested capital is 0). - read-only endpoint GET /reports/value-management reads the derived rows only (single source of truth, no recomputation) and exposes the parameter block. - DupontAnalysis.vue: value management section (ROI / ROA / ROE / EVA / RI). - caliber_units.py: RATIO_CALIBER_TABLE key F_ROI renamed to F_ROA, new F_ROI row. - tests: backend/tests/test_profitability_indicators.py (21 cases).
81 lines
4.8 KiB
SQL
81 lines
4.8 KiB
SQL
-- CMA 盈利能力三指标（真 ROI / EVA / RI）定义收敛 — 2026-10-03
|
||
-- 任务: finance-20261002-profitability-indicators-roi-eva-ri-fullstack（部署目标 cma-backend · cma-frontend）
|
||
-- 口径: 财务Bot 2026-10-02 裁定（任总 11:59 CST DM「立；但①要补真ROI」）
|
||
-- 动作:
|
||
-- ① F_ROI 正名 → F_ROA（**沿用原 kpi_id=20 与全部 8 期历史值，不删行**；名称「总资产报酬率(ROA)」不变）
|
||
-- ② 新增 F_ROI（真口径：经营口径营业利润 ÷ 投入资本）
|
||
-- ③ F_EVA 启用 status='active' + 单位/阈值对齐「万元」（原 unit='元' 而派生值以万元落库 ⇒ 会恒黄/量级失真）
|
||
-- ④ 新增 F_RI（剩余收益）
|
||
-- 参数: 全部落 data_source_config.params（可配置、非硬编码进公式文本）；
|
||
-- 读取入口唯一 = app/utils/profitability_params.py
|
||
-- 幂等: 条件 UPDATE + NOT EXISTS 守卫，可安全重跑
|
||
-- ⚠️ 顺序不可换：①必须先于②（(entity_id,kpi_code) 唯一键 uk_entity_kpi 会撞）
|
||
|
||
-- ① F_ROI 正名 → F_ROA（仅当 F_ROA 尚不存在；历史值随 kpi_id 保留）
|
||
UPDATE kpi_definitions
|
||
SET kpi_code = 'F_ROA', updated_at = NOW()
|
||
WHERE entity_id = 1 AND kpi_code = 'F_ROI'
|
||
AND NOT EXISTS (
|
||
SELECT 1 FROM (SELECT id FROM kpi_definitions
|
||
WHERE entity_id = 1 AND kpi_code = 'F_ROA') AS t
|
||
);
|
||
|
||
-- ② 新增 F_ROI（真口径）
|
||
INSERT INTO kpi_definitions
|
||
(entity_id, kpi_code, kpi_name, dimension, category, formula, formula_desc,
|
||
data_source_type, data_source, data_owner, frequency, unit, target_value,
|
||
target_calc_type, threshold_green, threshold_yellow, threshold_red,
|
||
kpi_level, status, bot_source, epic, created_by, data_source_config)
|
||
SELECT 1, 'F_ROI', '投入资本回报率(ROI)', 'finance', 'profitability',
|
||
'经营口径营业利润 / 投入资本 * 100',
|
||
'真ROI=营业利润÷投入资本; 投入资本=有息负债(2001+2501)+所有者权益(3xxx一级科目); YTD累计(非年化); '
|
||
'厂补530103/渠补571103计入经营(开关 include_factory_subsidy/include_channel_subsidy); '
|
||
'571112商品折扣默认不计入(include_sales_discount); 参数见 data_source_config.params',
|
||
'derived', '盈利能力派生器 kpi_profitability_deriver.py(subject_balances + voucher_details)',
|
||
'财务部', 'monthly', '%', 0.00,
|
||
'ratio', NULL, NULL, NULL,
|
||
'strategic', 'active', 'finance-bot', 'Epic2', 1,
|
||
'{"params": {"include_factory_subsidy": true, "include_channel_subsidy": true, "include_sales_discount": false, "annualize": false, "capital_basis": "ending"}, "param_note": "利润口径开关，三条指标(F_ROI/F_EVA/F_RI)共用；读取入口 app/utils/profitability_params.py"}'
|
||
FROM DUAL
|
||
WHERE NOT EXISTS (
|
||
SELECT 1 FROM kpi_definitions WHERE entity_id = 1 AND kpi_code = 'F_ROI'
|
||
);
|
||
|
||
-- ③ F_EVA 启用 + 万元口径对齐（阈值语义逐字平移：原 绿>=0元 / 黄>=-100000元 / 红<-100000元
|
||
-- ⇔ 万元 绿>=0 / 黄>=-10 / 红<-10；不改判定语义，只消除 元↔万元 跨量级失真）
|
||
UPDATE kpi_definitions
|
||
SET status = 'active',
|
||
unit = '万元',
|
||
target_value = 0.00,
|
||
target_calc_type = 'accumulate',
|
||
formula = 'NOPAT - 投入资本 * WACC',
|
||
formula_desc = 'EVA=NOPAT−投入资本×WACC; NOPAT=经营口径营业利润×(1−所得税率); 单位万元; '
|
||
'YTD累计(非年化); 资本成本参数见 data_source_config.params(tax_rate/wacc)',
|
||
data_source_type = 'derived',
|
||
data_source = '盈利能力派生器 kpi_profitability_deriver.py(subject_balances + voucher_details)',
|
||
data_owner = '财务部',
|
||
threshold_green = '>=0', threshold_yellow = '>=-10', threshold_red = '<-10',
|
||
data_source_config = '{"params": {"tax_rate": 0.25, "wacc": 0.08}}',
|
||
updated_at = NOW()
|
||
WHERE entity_id = 1 AND kpi_code = 'F_EVA';
|
||
|
||
-- ④ 新增 F_RI（剩余收益）
|
||
INSERT INTO kpi_definitions
|
||
(entity_id, kpi_code, kpi_name, dimension, category, formula, formula_desc,
|
||
data_source_type, data_source, data_owner, frequency, unit, target_value,
|
||
target_calc_type, threshold_green, threshold_yellow, threshold_red,
|
||
kpi_level, status, bot_source, epic, created_by, data_source_config)
|
||
SELECT 1, 'F_RI', '剩余收益(RI)', 'finance', 'profitability',
|
||
'经营口径营业利润 - 投入资本 * 最低报酬率',
|
||
'RI=营业利润−投入资本×最低报酬率(同WACC族,EVA为先扣税版本); 单位万元; YTD累计(非年化); '
|
||
'最低报酬率参数见 data_source_config.params.min_return_rate; 部门级 RI 待部门级资产负债后下沉',
|
||
'derived', '盈利能力派生器 kpi_profitability_deriver.py(subject_balances + voucher_details)',
|
||
'财务部', 'monthly', '万元', 0.00,
|
||
'accumulate', NULL, NULL, NULL,
|
||
'strategic', 'active', 'finance-bot', 'Epic2', 1,
|
||
'{"params": {"min_return_rate": 0.08}}'
|
||
FROM DUAL
|
||
WHERE NOT EXISTS (
|
||
SELECT 1 FROM kpi_definitions WHERE entity_id = 1 AND kpi_code = 'F_RI'
|
||
);
|