刘泉佳
62 lines
2.6 KiB
SQL
62 lines
2.6 KiB
SQL
-- 结算调整单接入金蝶（未推送自动推 · 已推送手工冲销后重推新单）
|
||
-- 整理日期：2026-10-16
|
||
|
||
-- 结算调整单：落金蝶单据号（未推送场景回填原单金蝶单号；已推送重推场景回填新单金蝶单号）
|
||
SET @ddl_sql = (
|
||
SELECT IF(COUNT(*) = 0,
|
||
'ALTER TABLE `blade_settlement_adjustment` ADD COLUMN `kingdee_bill_no` varchar(64) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT ''金蝶单据号'' AFTER `kingdee_sync_status`',
|
||
'SELECT 1')
|
||
FROM information_schema.COLUMNS
|
||
WHERE TABLE_SCHEMA = DATABASE()
|
||
AND TABLE_NAME = 'blade_settlement_adjustment'
|
||
AND COLUMN_NAME = 'kingdee_bill_no'
|
||
);
|
||
PREPARE ddl_stmt FROM @ddl_sql;
|
||
EXECUTE ddl_stmt;
|
||
DEALLOCATE PREPARE ddl_stmt;
|
||
|
||
-- 正式结算单：来源原单号（重推生成的新单，接续原单来源）
|
||
SET @ddl_sql = (
|
||
SELECT IF(COUNT(*) = 0,
|
||
'ALTER TABLE `blade_formal_settlement` ADD COLUMN `origin_formal_settlement_no` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT ''来源原正式结算单号（结算调整重推生成）''',
|
||
'SELECT 1')
|
||
FROM information_schema.COLUMNS
|
||
WHERE TABLE_SCHEMA = DATABASE()
|
||
AND TABLE_NAME = 'blade_formal_settlement'
|
||
AND COLUMN_NAME = 'origin_formal_settlement_no'
|
||
);
|
||
PREPARE ddl_stmt FROM @ddl_sql;
|
||
EXECUTE ddl_stmt;
|
||
DEALLOCATE PREPARE ddl_stmt;
|
||
|
||
-- 正式结算单：调整标记（被调整的原单）
|
||
SET @ddl_sql = (
|
||
SELECT IF(COUNT(*) = 0,
|
||
'ALTER TABLE `blade_formal_settlement` ADD COLUMN `adjust_status` varchar(32) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT ''调整状态：adjusted=已被结算调整'' AFTER `origin_formal_settlement_no`',
|
||
'SELECT 1')
|
||
FROM information_schema.COLUMNS
|
||
WHERE TABLE_SCHEMA = DATABASE()
|
||
AND TABLE_NAME = 'blade_formal_settlement'
|
||
AND COLUMN_NAME = 'adjust_status'
|
||
);
|
||
PREPARE ddl_stmt FROM @ddl_sql;
|
||
EXECUTE ddl_stmt;
|
||
DEALLOCATE PREPARE ddl_stmt;
|
||
|
||
-- 正式结算单：来源原单号索引（重推幂等按来源原单号查已克隆新单）
|
||
SET @ddl_sql = (
|
||
SELECT IF(COUNT(*) = 0,
|
||
'ALTER TABLE `blade_formal_settlement` ADD KEY `idx_formal_origin_no` (`origin_formal_settlement_no`)',
|
||
'SELECT 1')
|
||
FROM information_schema.STATISTICS
|
||
WHERE TABLE_SCHEMA = DATABASE()
|
||
AND TABLE_NAME = 'blade_formal_settlement'
|
||
AND INDEX_NAME = 'idx_formal_origin_no'
|
||
);
|
||
PREPARE ddl_stmt FROM @ddl_sql;
|
||
EXECUTE ddl_stmt;
|
||
DEALLOCATE PREPARE ddl_stmt;
|
||
|
||
-- 推送金蝶 / 重新推送已合并为单一按钮（按金蝶状态自动分流），复用既有菜单 settlement_adjustment_repush，
|
||
-- 菜单已在 blade_settlement_adjustment_20260818.sql 初始化，本脚本无需新增菜单。
|