-- ============================================================================ -- 1.0.562 · 中立标准层：4 张新表 + 既有表补列 + 库存事务码迁移 + 宽表改名 -- 幂等。AutoVersionUpdate 按 ; 拆句、无事务。语句内不得再嵌套分号。 -- 口径：approved_flag 默认 1（源系统不提供审核标记时视为已审核）。 -- ============================================================================ CREATE TABLE IF NOT EXISTS mdp_std_work_order_line ( id BIGINT AUTO_INCREMENT PRIMARY KEY, tenant_id BIGINT NOT NULL DEFAULT 0, factory_id BIGINT NULL DEFAULT 1, source_system VARCHAR(50) NOT NULL DEFAULT 'AIDOP', domain VARCHAR(80) NULL, doc_type VARCHAR(32) NOT NULL, src_doc_type_raw VARCHAR(64) NULL, order_no VARCHAR(120) NOT NULL, line_no VARCHAR(50) NULL, task_no VARCHAR(80) NULL, item_code VARCHAR(100) NULL, qty_planned DECIMAL(18,6) NULL DEFAULT 0, qty_completed DECIMAL(18,6) NULL DEFAULT 0, plan_finish_date DATETIME NULL, release_time DATETIME NULL, closed_flag TINYINT NOT NULL DEFAULT 0, closed_time DATETIME NULL, approved_flag TINYINT NOT NULL DEFAULT 1, void_flag TINYINT NOT NULL DEFAULT 0, source_row_id VARCHAR(100) NOT NULL, source_biz_key VARCHAR(200) NOT NULL, sync_batch_id VARCHAR(100) NOT NULL, sync_time DATETIME NOT NULL, create_time DATETIME DEFAULT CURRENT_TIMESTAMP, update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uk_std_wo_line (tenant_id, source_system, source_biz_key), KEY idx_std_wo_line_order (tenant_id, doc_type, order_no, line_no), KEY idx_std_wo_line_task (tenant_id, task_no), KEY idx_std_wo_line_plan (tenant_id, plan_finish_date), KEY idx_std_wo_line_closed (tenant_id, closed_flag, closed_time) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='中立任务/订单行'; CREATE TABLE IF NOT EXISTS mdp_std_work_order_bom ( id BIGINT AUTO_INCREMENT PRIMARY KEY, tenant_id BIGINT NOT NULL DEFAULT 0, factory_id BIGINT NULL DEFAULT 1, source_system VARCHAR(50) NOT NULL DEFAULT 'AIDOP', domain VARCHAR(80) NULL, order_no VARCHAR(120) NOT NULL, order_line_no VARCHAR(50) NULL, task_no VARCHAR(80) NULL, item_code VARCHAR(100) NULL, qty_required DECIMAL(18,6) NULL DEFAULT 0, unit VARCHAR(50) NULL, source_row_id VARCHAR(100) NOT NULL, source_biz_key VARCHAR(200) NOT NULL, sync_batch_id VARCHAR(100) NOT NULL, sync_time DATETIME NOT NULL, update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uk_std_wo_bom (tenant_id, source_system, source_biz_key), KEY idx_std_wo_bom_order (tenant_id, order_no), KEY idx_std_wo_bom_item (tenant_id, item_code) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='中立任务用料清单'; CREATE TABLE IF NOT EXISTS mdp_std_employee ( id BIGINT AUTO_INCREMENT PRIMARY KEY, tenant_id BIGINT NOT NULL DEFAULT 0, factory_id BIGINT NULL DEFAULT 1, source_system VARCHAR(50) NOT NULL DEFAULT 'AIDOP', domain VARCHAR(80) NOT NULL, employee_no VARCHAR(100) NOT NULL, employee_name VARCHAR(100) NULL, department_code VARCHAR(50) NULL, department_name VARCHAR(200) NULL, position_code VARCHAR(32) NOT NULL DEFAULT 'OTHER', src_position_raw VARCHAR(100) NULL, employment_status VARCHAR(32) NOT NULL DEFAULT 'ACTIVE', src_employment_status_raw VARCHAR(100) NULL, source_row_id VARCHAR(100) NOT NULL, source_biz_key VARCHAR(200) NOT NULL, sync_batch_id VARCHAR(100) NOT NULL, sync_time DATETIME NOT NULL, update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uk_std_employee (tenant_id, source_system, domain, employee_no), KEY idx_std_employee_pos (tenant_id, position_code, employment_status) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='中立人员与岗位'; CREATE TABLE IF NOT EXISTS mdp_std_inventory_balance_monthly ( id BIGINT AUTO_INCREMENT PRIMARY KEY, tenant_id BIGINT NOT NULL DEFAULT 0, factory_id BIGINT NULL DEFAULT 1, source_system VARCHAR(50) NOT NULL DEFAULT 'AIDOP', domain VARCHAR(80) NULL, period_ym VARCHAR(6) NOT NULL, category_code VARCHAR(50) NOT NULL DEFAULT '', category_name VARCHAR(200) NULL, warehouse_code VARCHAR(50) NOT NULL DEFAULT '', warehouse_name VARCHAR(200) NULL, item_code VARCHAR(100) NOT NULL DEFAULT '', avg_balance_amount DECIMAL(18,6) NOT NULL DEFAULT 0, issue_cost_amount DECIMAL(18,6) NOT NULL DEFAULT 0, source_biz_key VARCHAR(200) NOT NULL, sync_batch_id VARCHAR(100) NOT NULL, sync_time DATETIME NOT NULL, update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uk_std_inv_bal_m (tenant_id, source_system, period_ym, category_code, warehouse_code, item_code), KEY idx_std_inv_bal_m_period (tenant_id, period_ym) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='月度库存金额（成本口径）'; -- mdp_std_inv_trans 补列 SET @sql := IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='mdp_std_inv_trans' AND COLUMN_NAME='biz_doc_type')=0, 'ALTER TABLE `mdp_std_inv_trans` ADD COLUMN `biz_doc_type` VARCHAR(32) NOT NULL DEFAULT ''OTHER'' COMMENT ''中立业务单据类型''', 'DO 0'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @sql := IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='mdp_std_inv_trans' AND COLUMN_NAME='src_biz_doc_type_raw')=0, 'ALTER TABLE `mdp_std_inv_trans` ADD COLUMN `src_biz_doc_type_raw` VARCHAR(100) NULL', 'DO 0'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @sql := IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='mdp_std_inv_trans' AND COLUMN_NAME='src_trans_type_raw')=0, 'ALTER TABLE `mdp_std_inv_trans` ADD COLUMN `src_trans_type_raw` VARCHAR(64) NULL', 'DO 0'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @sql := IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='mdp_std_inv_trans' AND COLUMN_NAME='approved_time')=0, 'ALTER TABLE `mdp_std_inv_trans` ADD COLUMN `approved_time` DATETIME NULL', 'DO 0'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @sql := IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='mdp_std_inv_trans' AND COLUMN_NAME='approved_flag')=0, 'ALTER TABLE `mdp_std_inv_trans` ADD COLUMN `approved_flag` TINYINT NOT NULL DEFAULT 1', 'DO 0'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @sql := IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='mdp_std_inv_trans' AND COLUMN_NAME='void_flag')=0, 'ALTER TABLE `mdp_std_inv_trans` ADD COLUMN `void_flag` TINYINT NOT NULL DEFAULT 0', 'DO 0'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @sql := IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='mdp_std_inv_trans' AND COLUMN_NAME='summary_flag')=0, 'ALTER TABLE `mdp_std_inv_trans` ADD COLUMN `summary_flag` TINYINT NOT NULL DEFAULT 0', 'DO 0'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @sql := IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='mdp_std_inv_trans' AND COLUMN_NAME='ref_task_no')=0, 'ALTER TABLE `mdp_std_inv_trans` ADD COLUMN `ref_task_no` VARCHAR(80) NULL', 'DO 0'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @sql := IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='mdp_std_inv_trans' AND COLUMN_NAME='doc_qty')=0, 'ALTER TABLE `mdp_std_inv_trans` ADD COLUMN `doc_qty` DECIMAL(18,6) NULL COMMENT ''单据数量，供完工判定与计划量比较''', 'DO 0'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @sql := IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='mdp_std_inv_trans' AND COLUMN_NAME='line_closed_flag')=0, 'ALTER TABLE `mdp_std_inv_trans` ADD COLUMN `line_closed_flag` TINYINT NOT NULL DEFAULT 0', 'DO 0'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @sql := IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='mdp_std_inv_trans' AND COLUMN_NAME='line_closed_time')=0, 'ALTER TABLE `mdp_std_inv_trans` ADD COLUMN `line_closed_time` DATETIME NULL', 'DO 0'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; -- 原始事务码迁到 src_trans_type_raw，trans_type 只留中立阶段码 UPDATE mdp_std_inv_trans SET src_trans_type_raw = trans_type WHERE src_trans_type_raw IS NULL AND trans_type IS NOT NULL AND trans_type NOT IN ('MAT_RECEIPT','MAT_IQC_RELEASE','MAT_PUTAWAY','MAT_PICK','MAT_ISSUE','MAT_LINE','FG_RECEIPT','FG_PUTAWAY','FG_PICK','FG_SHIP','FG_PROD_RECEIPT','FG_FQC_RELEASE'); UPDATE mdp_std_inv_trans SET trans_type = CASE src_trans_type_raw WHEN 'iss-wo' THEN 'MAT_ISSUE' WHEN 'rct-po' THEN 'MAT_RECEIPT' WHEN 'rct-unp' THEN 'MAT_RECEIPT' WHEN 'MAT_RECEIPT' THEN 'MAT_RECEIPT' WHEN 'MAT_IQC_RELEASE' THEN 'MAT_IQC_RELEASE' WHEN 'MAT_PUTAWAY' THEN 'MAT_PUTAWAY' WHEN 'MAT_PICK' THEN 'MAT_PICK' WHEN 'MAT_ISSUE' THEN 'MAT_ISSUE' WHEN 'MAT_LINE' THEN 'MAT_LINE' WHEN 'FG_RECEIPT' THEN 'FG_RECEIPT' WHEN 'FG_PUTAWAY' THEN 'FG_PUTAWAY' WHEN 'FG_PICK' THEN 'FG_PICK' WHEN 'FG_SHIP' THEN 'FG_SHIP' WHEN 'FG_PROD_RECEIPT' THEN 'FG_PROD_RECEIPT' WHEN 'FG_FQC_RELEASE' THEN 'FG_FQC_RELEASE' ELSE NULL END, biz_doc_type = CASE src_trans_type_raw WHEN 'iss-wo' THEN 'PROD_ISSUE' WHEN 'rct-wo' THEN 'PROD_RECEIPT' WHEN 'rct-po' THEN 'PUR_RECEIPT' WHEN 'rct-unp' THEN 'PUR_RECEIPT' WHEN 'iss-so' THEN 'SALES_SHIP' ELSE biz_doc_type END WHERE src_trans_type_raw IS NOT NULL AND (trans_type IS NULL OR trans_type NOT IN ('MAT_RECEIPT','MAT_IQC_RELEASE','MAT_PUTAWAY','MAT_PICK','MAT_ISSUE','MAT_LINE','FG_RECEIPT','FG_PUTAWAY','FG_PICK','FG_SHIP','FG_PROD_RECEIPT','FG_FQC_RELEASE')); -- 工单排程补列，并把唯一键扩成（租户、单据类型、单号），避免生产任务与销售订单重号覆盖 SET @sql := IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='mdp_std_work_order_schedule' AND COLUMN_NAME='doc_type')=0, 'ALTER TABLE `mdp_std_work_order_schedule` ADD COLUMN `doc_type` VARCHAR(32) NOT NULL DEFAULT ''PROD_TASK''', 'DO 0'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @sql := IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='mdp_std_work_order_schedule' AND COLUMN_NAME='approved_flag')=0, 'ALTER TABLE `mdp_std_work_order_schedule` ADD COLUMN `approved_flag` TINYINT NOT NULL DEFAULT 1', 'DO 0'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @sql := IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='mdp_std_work_order_schedule' AND COLUMN_NAME='void_flag')=0, 'ALTER TABLE `mdp_std_work_order_schedule` ADD COLUMN `void_flag` TINYINT NOT NULL DEFAULT 0', 'DO 0'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @sql := IF((SELECT COUNT(*) FROM information_schema.STATISTICS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='mdp_std_work_order_schedule' AND INDEX_NAME='uk_std_work_order_schedule')>0 AND (SELECT COUNT(*) FROM information_schema.STATISTICS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='mdp_std_work_order_schedule' AND INDEX_NAME='uk_std_wo_sched_type')=0, 'ALTER TABLE `mdp_std_work_order_schedule` DROP INDEX `uk_std_work_order_schedule`, ADD UNIQUE KEY `uk_std_wo_sched_type` (`tenant_id`,`doc_type`,`work_order`)', 'DO 0'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @sql := IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='mdp_std_production_receipt' AND COLUMN_NAME='closed_flag')=0, 'ALTER TABLE `mdp_std_production_receipt` ADD COLUMN `closed_flag` TINYINT NOT NULL DEFAULT 0', 'DO 0'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @sql := IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='mdp_std_production_receipt' AND COLUMN_NAME='closed_time')=0, 'ALTER TABLE `mdp_std_production_receipt` ADD COLUMN `closed_time` DATETIME NULL', 'DO 0'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @sql := IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='mdp_std_production_receipt' AND COLUMN_NAME='task_no')=0, 'ALTER TABLE `mdp_std_production_receipt` ADD COLUMN `task_no` VARCHAR(80) NULL', 'DO 0'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @sql := IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='mdp_std_purchase_receipt' AND COLUMN_NAME='approved_flag')=0, 'ALTER TABLE `mdp_std_purchase_receipt` ADD COLUMN `approved_flag` TINYINT NOT NULL DEFAULT 1', 'DO 0'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @sql := IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='mdp_std_purchase_receipt' AND COLUMN_NAME='approved_time')=0, 'ALTER TABLE `mdp_std_purchase_receipt` ADD COLUMN `approved_time` DATETIME NULL', 'DO 0'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @sql := IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='mdp_std_s6_report' AND COLUMN_NAME='start_work_date')=0, 'ALTER TABLE `mdp_std_s6_report` ADD COLUMN `start_work_date` DATETIME NULL', 'DO 0'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @sql := IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='mdp_std_s6_report' AND COLUMN_NAME='task_no')=0, 'ALTER TABLE `mdp_std_s6_report` ADD COLUMN `task_no` VARCHAR(80) NULL', 'DO 0'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @sql := IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='mdp_std_s6_report' AND COLUMN_NAME='domain')=0, 'ALTER TABLE `mdp_std_s6_report` ADD COLUMN `domain` VARCHAR(80) NULL', 'DO 0'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @sql := IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='mdp_std_fqc_task' AND COLUMN_NAME='sales_order_no')=0, 'ALTER TABLE `mdp_std_fqc_task` ADD COLUMN `sales_order_no` VARCHAR(100) NULL', 'DO 0'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @sql := IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='mdp_std_fqc_task' AND COLUMN_NAME='sales_order_line')=0, 'ALTER TABLE `mdp_std_fqc_task` ADD COLUMN `sales_order_line` VARCHAR(50) NULL', 'DO 0'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @sql := IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='mdp_std_fqc_task' AND COLUMN_NAME='apply_flag')=0, 'ALTER TABLE `mdp_std_fqc_task` ADD COLUMN `apply_flag` TINYINT NOT NULL DEFAULT 0', 'DO 0'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @sql := IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='mdp_std_fqc_task' AND COLUMN_NAME='domain')=0, 'ALTER TABLE `mdp_std_fqc_task` ADD COLUMN `domain` VARCHAR(80) NULL', 'DO 0'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @sql := IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='mdp_std_so' AND COLUMN_NAME='line_closed_flag')=0, 'ALTER TABLE `mdp_std_so` ADD COLUMN `line_closed_flag` TINYINT NOT NULL DEFAULT 0', 'DO 0'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @sql := IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='mdp_std_so' AND COLUMN_NAME='line_closed_time')=0, 'ALTER TABLE `mdp_std_so` ADD COLUMN `line_closed_time` DATETIME NULL', 'DO 0'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; -- 九张宽表去掉 t8 前缀，旧名保留为视图 SET @sql := IF((SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='dwd_t8_material_online_cycle' AND TABLE_TYPE='BASE TABLE')=1, 'RENAME TABLE `dwd_t8_material_online_cycle` TO `dwd_material_online_cycle`', 'DO 0'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @sql := IF((SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='dwd_material_online_cycle' AND TABLE_TYPE='BASE TABLE')=1 AND (SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='dwd_t8_material_online_cycle')=0, 'CREATE VIEW `dwd_t8_material_online_cycle` AS SELECT * FROM `dwd_material_online_cycle`', 'DO 0'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @sql := IF((SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='dwd_t8_material_online_fulfillment' AND TABLE_TYPE='BASE TABLE')=1, 'RENAME TABLE `dwd_t8_material_online_fulfillment` TO `dwd_material_online_fulfillment`', 'DO 0'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @sql := IF((SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='dwd_material_online_fulfillment' AND TABLE_TYPE='BASE TABLE')=1 AND (SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='dwd_t8_material_online_fulfillment')=0, 'CREATE VIEW `dwd_t8_material_online_fulfillment` AS SELECT * FROM `dwd_material_online_fulfillment`', 'DO 0'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @sql := IF((SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='dwd_t8_material_warehouse_efficiency' AND TABLE_TYPE='BASE TABLE')=1, 'RENAME TABLE `dwd_t8_material_warehouse_efficiency` TO `dwd_material_warehouse_efficiency`', 'DO 0'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @sql := IF((SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='dwd_material_warehouse_efficiency' AND TABLE_TYPE='BASE TABLE')=1 AND (SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='dwd_t8_material_warehouse_efficiency')=0, 'CREATE VIEW `dwd_t8_material_warehouse_efficiency` AS SELECT * FROM `dwd_material_warehouse_efficiency`', 'DO 0'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @sql := IF((SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='dwd_t8_material_inventory_turnover' AND TABLE_TYPE='BASE TABLE')=1, 'RENAME TABLE `dwd_t8_material_inventory_turnover` TO `dwd_material_inventory_turnover`', 'DO 0'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @sql := IF((SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='dwd_material_inventory_turnover' AND TABLE_TYPE='BASE TABLE')=1 AND (SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='dwd_t8_material_inventory_turnover')=0, 'CREATE VIEW `dwd_t8_material_inventory_turnover` AS SELECT * FROM `dwd_material_inventory_turnover`', 'DO 0'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @sql := IF((SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='dwd_t8_work_order_mfg_fulfillment' AND TABLE_TYPE='BASE TABLE')=1, 'RENAME TABLE `dwd_t8_work_order_mfg_fulfillment` TO `dwd_work_order_mfg_fulfillment`', 'DO 0'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @sql := IF((SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='dwd_work_order_mfg_fulfillment' AND TABLE_TYPE='BASE TABLE')=1 AND (SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='dwd_t8_work_order_mfg_fulfillment')=0, 'CREATE VIEW `dwd_t8_work_order_mfg_fulfillment` AS SELECT * FROM `dwd_work_order_mfg_fulfillment`', 'DO 0'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @sql := IF((SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='dwd_t8_work_order_mfg_efficiency' AND TABLE_TYPE='BASE TABLE')=1, 'RENAME TABLE `dwd_t8_work_order_mfg_efficiency` TO `dwd_work_order_mfg_efficiency`', 'DO 0'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @sql := IF((SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='dwd_work_order_mfg_efficiency' AND TABLE_TYPE='BASE TABLE')=1 AND (SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='dwd_t8_work_order_mfg_efficiency')=0, 'CREATE VIEW `dwd_t8_work_order_mfg_efficiency` AS SELECT * FROM `dwd_work_order_mfg_efficiency`', 'DO 0'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @sql := IF((SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='dwd_t8_order_shipment_cycle' AND TABLE_TYPE='BASE TABLE')=1, 'RENAME TABLE `dwd_t8_order_shipment_cycle` TO `dwd_order_shipment_cycle`', 'DO 0'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @sql := IF((SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='dwd_order_shipment_cycle' AND TABLE_TYPE='BASE TABLE')=1 AND (SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='dwd_t8_order_shipment_cycle')=0, 'CREATE VIEW `dwd_t8_order_shipment_cycle` AS SELECT * FROM `dwd_order_shipment_cycle`', 'DO 0'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @sql := IF((SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='dwd_t8_order_shipment_fulfillment' AND TABLE_TYPE='BASE TABLE')=1, 'RENAME TABLE `dwd_t8_order_shipment_fulfillment` TO `dwd_order_shipment_fulfillment`', 'DO 0'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @sql := IF((SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='dwd_order_shipment_fulfillment' AND TABLE_TYPE='BASE TABLE')=1 AND (SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='dwd_t8_order_shipment_fulfillment')=0, 'CREATE VIEW `dwd_t8_order_shipment_fulfillment` AS SELECT * FROM `dwd_order_shipment_fulfillment`', 'DO 0'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @sql := IF((SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='dwd_t8_finished_warehouse_efficiency' AND TABLE_TYPE='BASE TABLE')=1, 'RENAME TABLE `dwd_t8_finished_warehouse_efficiency` TO `dwd_finished_warehouse_efficiency`', 'DO 0'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @sql := IF((SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='dwd_finished_warehouse_efficiency' AND TABLE_TYPE='BASE TABLE')=1 AND (SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='dwd_t8_finished_warehouse_efficiency')=0, 'CREATE VIEW `dwd_t8_finished_warehouse_efficiency` AS SELECT * FROM `dwd_finished_warehouse_efficiency`', 'DO 0'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; UPDATE ado_smart_ops_kpi_dimension_config SET SqlScript = REPLACE(SqlScript, 'dwd_t8_', 'dwd_'), BusinessSqlSource = REPLACE(BusinessSqlSource, 'dwd_t8_', 'dwd_') WHERE SqlScript LIKE '%dwd_t8_%' OR BusinessSqlSource LIKE '%dwd_t8_%'; UPDATE ado_smart_ops_kpi_calc_config SET SqlScript = REPLACE(SqlScript, 'dwd_t8_', 'dwd_') WHERE SqlScript LIKE '%dwd_t8_%';