-- ============================================
-- DMS 模块所有序列定义
-- 生成时间: 2026-01-23
-- 说明: 包含 DMS 模块所有 Mapper 中使用的序列
-- ============================================

-- ----------------------------
-- 1. SEQ_DMS_INSPECT_PROJ (点检项目)
-- 表: DMS_BASE_INSPECT_PROJECT
-- ----------------------------
BEGIN
   EXECUTE IMMEDIATE 'DROP SEQUENCE "HAIWEI"."SEQ_DMS_INSPECT_PROJ"';
EXCEPTION
   WHEN OTHERS THEN NULL;
END;
/
CREATE SEQUENCE "HAIWEI"."SEQ_DMS_INSPECT_PROJ"
  MINVALUE 1
  MAXVALUE 9999999999999999999999999999
  START WITH 1
  INCREMENT BY 1
  CACHE 20;

-- ----------------------------
-- 2. SEQ_DMS_INSPECT_ROUTE (点检路线)
-- 表: DMS_BASE_INSPECT_ROUTE
-- ----------------------------
BEGIN
   EXECUTE IMMEDIATE 'DROP SEQUENCE "HAIWEI"."SEQ_DMS_INSPECT_ROUTE"';
EXCEPTION
   WHEN OTHERS THEN NULL;
END;
/
CREATE SEQUENCE "HAIWEI"."SEQ_DMS_INSPECT_ROUTE"
  MINVALUE 1
  MAXVALUE 9999999999999999999999999999
  START WITH 1
  INCREMENT BY 1
  CACHE 20;

-- ----------------------------
-- 3. SEQ_DMS_INSPECT_STD (点检标准)
-- 表: DMS_BASE_INSPECT_STANDARD
-- ----------------------------
BEGIN
   EXECUTE IMMEDIATE 'DROP SEQUENCE "HAIWEI"."SEQ_DMS_INSPECT_STD"';
EXCEPTION
   WHEN OTHERS THEN NULL;
END;
/
CREATE SEQUENCE "HAIWEI"."SEQ_DMS_INSPECT_STD"
  MINVALUE 1
  MAXVALUE 9999999999999999999999999999
  START WITH 1
  INCREMENT BY 1
  CACHE 20;

-- ----------------------------
-- 4. SEQ_DMS_INSPECT_RTE_DTL (点检路线明细)
-- 表: DMS_INSPECT_ROUTE_DETAIL
-- ----------------------------
BEGIN
   EXECUTE IMMEDIATE 'DROP SEQUENCE "HAIWEI"."SEQ_DMS_INSPECT_RTE_DTL"';
EXCEPTION
   WHEN OTHERS THEN NULL;
END;
/
CREATE SEQUENCE "HAIWEI"."SEQ_DMS_INSPECT_RTE_DTL"
  MINVALUE 1
  MAXVALUE 9999999999999999999999999999
  START WITH 1
  INCREMENT BY 1
  CACHE 20;

-- ----------------------------
-- 5. SEQ_DMS_LUBE_STD (润滑标准)
-- 表: DMS_BASE_LUBE_STANDARD
-- ----------------------------
BEGIN
   EXECUTE IMMEDIATE 'DROP SEQUENCE "HAIWEI"."SEQ_DMS_LUBE_STD"';
EXCEPTION
   WHEN OTHERS THEN NULL;
END;
/
CREATE SEQUENCE "HAIWEI"."SEQ_DMS_LUBE_STD"
  MINVALUE 1
  MAXVALUE 9999999999999999999999999999
  START WITH 1
  INCREMENT BY 1
  CACHE 20;

-- ----------------------------
-- 6. SEQ_DMS_LUBE_STATION (润滑部位)
-- 表: DMS_BASE_LUBE_STATION
-- ----------------------------
BEGIN
   EXECUTE IMMEDIATE 'DROP SEQUENCE "HAIWEI"."SEQ_DMS_LUBE_STATION"';
EXCEPTION
   WHEN OTHERS THEN NULL;
END;
/
CREATE SEQUENCE "HAIWEI"."SEQ_DMS_LUBE_STATION"
  MINVALUE 1
  MAXVALUE 9999999999999999999999999999
  START WITH 1
  INCREMENT BY 1
  CACHE 20;

-- ----------------------------
-- 7. SEQ_DMS_MAINT_PROJ (保养项目)
-- 表: DMS_BASE_MAINT_PROJECT
-- ----------------------------
BEGIN
   EXECUTE IMMEDIATE 'DROP SEQUENCE "HAIWEI"."SEQ_DMS_MAINT_PROJ"';
EXCEPTION
   WHEN OTHERS THEN NULL;
END;
/
CREATE SEQUENCE "HAIWEI"."SEQ_DMS_MAINT_PROJ"
  MINVALUE 1
  MAXVALUE 9999999999999999999999999999
  START WITH 1
  INCREMENT BY 1
  CACHE 20;

-- ----------------------------
-- 8. SEQ_DMS_MAINT_STD (保养标准)
-- 表: DMS_BASE_MAINT_STANDARD
-- ----------------------------
BEGIN
   EXECUTE IMMEDIATE 'DROP SEQUENCE "HAIWEI"."SEQ_DMS_MAINT_STD"';
EXCEPTION
   WHEN OTHERS THEN NULL;
END;
/
CREATE SEQUENCE "HAIWEI"."SEQ_DMS_MAINT_STD"
  MINVALUE 1
  MAXVALUE 9999999999999999999999999999
  START WITH 1
  INCREMENT BY 1
  CACHE 20;

-- ----------------------------
-- 9. SEQ_DMS_MAINT_STATION (保养部位)
-- 表: DMS_BASE_MAINT_STATION
-- ----------------------------
BEGIN
   EXECUTE IMMEDIATE 'DROP SEQUENCE "HAIWEI"."SEQ_DMS_MAINT_STATION"';
EXCEPTION
   WHEN OTHERS THEN NULL;
END;
/
CREATE SEQUENCE "HAIWEI"."SEQ_DMS_MAINT_STATION"
  MINVALUE 1
  MAXVALUE 9999999999999999999999999999
  START WITH 1
  INCREMENT BY 1
  CACHE 20;

-- ----------------------------
-- 10. SEQ_DMS_SHUT_REASON (停机原因)
-- 表: DMS_BASE_SHUT_REASON
-- ----------------------------
BEGIN
   EXECUTE IMMEDIATE 'DROP SEQUENCE "HAIWEI"."SEQ_DMS_SHUT_REASON"';
EXCEPTION
   WHEN OTHERS THEN NULL;
END;
/
CREATE SEQUENCE "HAIWEI"."SEQ_DMS_SHUT_REASON"
  MINVALUE 1
  MAXVALUE 9999999999999999999999999999
  START WITH 1
  INCREMENT BY 1
  CACHE 20;

-- ----------------------------
-- 11. SEQ_DMS_SHUT_TYPE (停机类型)
-- 表: DMS_BASE_SHUT_TYPE
-- ----------------------------
BEGIN
   EXECUTE IMMEDIATE 'DROP SEQUENCE "HAIWEI"."SEQ_DMS_SHUT_TYPE"';
EXCEPTION
   WHEN OTHERS THEN NULL;
END;
/
CREATE SEQUENCE "HAIWEI"."SEQ_DMS_SHUT_TYPE"
  MINVALUE 1
  MAXVALUE 9999999999999999999999999999
  START WITH 1
  INCREMENT BY 1
  CACHE 20;

-- ----------------------------
-- 12. SEQ_DMS_BILLS_FAULT_INSTANCE (故障工单)
-- 表: DMS_BILLS_FAULT_INSTANCE
-- ----------------------------
BEGIN
   EXECUTE IMMEDIATE 'DROP SEQUENCE "HAIWEI"."SEQ_DMS_BILLS_FAULT_INSTANCE"';
EXCEPTION
   WHEN OTHERS THEN NULL;
END;
/
CREATE SEQUENCE "HAIWEI"."SEQ_DMS_BILLS_FAULT_INSTANCE"
  MINVALUE 1
  MAXVALUE 9999999999999999999999999999
  START WITH 1
  INCREMENT BY 1
  CACHE 20;

-- ----------------------------
-- 13. SEQ_FAULT_INSTANCE_ACTIVITY (故障工单活动)
-- 表: DMS_FAULT_INSTANCE_ACTIVITY
-- ----------------------------
BEGIN
   EXECUTE IMMEDIATE 'DROP SEQUENCE "HAIWEI"."SEQ_FAULT_INSTANCE_ACTIVITY"';
EXCEPTION
   WHEN OTHERS THEN NULL;
END;
/
CREATE SEQUENCE "HAIWEI"."SEQ_FAULT_INSTANCE_ACTIVITY"
  MINVALUE 1
  MAXVALUE 9999999999999999999999999999
  START WITH 1
  INCREMENT BY 1
  CACHE 20;

-- ----------------------------
-- 14. SEQ_DMS_MAINT_INST (保养工单)
-- 表: DMS_BILLS_MAINT_INSTANCE
-- ----------------------------
BEGIN
   EXECUTE IMMEDIATE 'DROP SEQUENCE "HAIWEI"."SEQ_DMS_MAINT_INST"';
EXCEPTION
   WHEN OTHERS THEN NULL;
END;
/
CREATE SEQUENCE "HAIWEI"."SEQ_DMS_MAINT_INST"
  MINVALUE 1
  MAXVALUE 9999999999999999999999999999
  START WITH 1
  INCREMENT BY 1
  CACHE 20;

-- ----------------------------
-- 15. SEQ_DMS_MAINT_DETAIL (保养工单明细)
-- 表: DMS_BILLS_MAINT_DETAIL
-- ----------------------------
BEGIN
   EXECUTE IMMEDIATE 'DROP SEQUENCE "HAIWEI"."SEQ_DMS_MAINT_DETAIL"';
EXCEPTION
   WHEN OTHERS THEN NULL;
END;
/
CREATE SEQUENCE "HAIWEI"."SEQ_DMS_MAINT_DETAIL"
  MINVALUE 1
  MAXVALUE 9999999999999999999999999999
  START WITH 1
  INCREMENT BY 1
  CACHE 20;

-- ----------------------------
-- 16. SEQ_DMS_MAINT_DTL_PROJ (保养明细项目)
-- 表: DMS_BILLS_MAINT_DETAIL_PROJECT
-- ----------------------------
BEGIN
   EXECUTE IMMEDIATE 'DROP SEQUENCE "HAIWEI"."SEQ_DMS_MAINT_DTL_PROJ"';
EXCEPTION
   WHEN OTHERS THEN NULL;
END;
/
CREATE SEQUENCE "HAIWEI"."SEQ_DMS_MAINT_DTL_PROJ"
  MINVALUE 1
  MAXVALUE 9999999999999999999999999999
  START WITH 1
  INCREMENT BY 1
  CACHE 20;

-- ----------------------------
-- 17. SEQ_DMS_DISPATCH_PLAN (派工计划)
-- 表: DMS_DISPATCH_PLAN
-- ----------------------------
BEGIN
   EXECUTE IMMEDIATE 'DROP SEQUENCE "HAIWEI"."SEQ_DMS_DISPATCH_PLAN"';
EXCEPTION
   WHEN OTHERS THEN NULL;
END;
/
CREATE SEQUENCE "HAIWEI"."SEQ_DMS_DISPATCH_PLAN"
  MINVALUE 1
  MAXVALUE 9999999999999999999999999999
  START WITH 1
  INCREMENT BY 1
  CACHE 20;

-- ----------------------------
-- 18. SEQ_DMS_DISPATCH_MEMBER (派工成员)
-- 表: DMS_DISPATCH_MEMBER
-- ----------------------------
BEGIN
   EXECUTE IMMEDIATE 'DROP SEQUENCE "HAIWEI"."SEQ_DMS_DISPATCH_MEMBER"';
EXCEPTION
   WHEN OTHERS THEN NULL;
END;
/
CREATE SEQUENCE "HAIWEI"."SEQ_DMS_DISPATCH_MEMBER"
  MINVALUE 1
  MAXVALUE 9999999999999999999999999999
  START WITH 1
  INCREMENT BY 1
  CACHE 20;

-- ----------------------------
-- 19. SEQ_DMS_KNOWLEDGE_REPAIR (维修知识库)
-- 表: DMS_KNOWLEDGE_REPAIR
-- ----------------------------
BEGIN
   EXECUTE IMMEDIATE 'DROP SEQUENCE "HAIWEI"."SEQ_DMS_KNOWLEDGE_REPAIR"';
EXCEPTION
   WHEN OTHERS THEN NULL;
END;
/
CREATE SEQUENCE "HAIWEI"."SEQ_DMS_KNOWLEDGE_REPAIR"
  MINVALUE 1
  MAXVALUE 9999999999999999999999999999
  START WITH 1
  INCREMENT BY 1
  CACHE 20;

-- ----------------------------
-- 20. SEQ_DMS_PLAN_INSPECT (点检计划)
-- 表: DMS_PLAN_INSPECT
-- ----------------------------
BEGIN
   EXECUTE IMMEDIATE 'DROP SEQUENCE "HAIWEI"."SEQ_DMS_PLAN_INSPECT"';
EXCEPTION
   WHEN OTHERS THEN NULL;
END;
/
CREATE SEQUENCE "HAIWEI"."SEQ_DMS_PLAN_INSPECT"
  MINVALUE 1
  MAXVALUE 9999999999999999999999999999
  START WITH 1
  INCREMENT BY 1
  CACHE 20;

-- ----------------------------
-- 21. SEQ_DMS_PLAN_MAINT (保养计划)
-- 表: DMS_PLAN_MAINT
-- ----------------------------
BEGIN
   EXECUTE IMMEDIATE 'DROP SEQUENCE "HAIWEI"."SEQ_DMS_PLAN_MAINT"';
EXCEPTION
   WHEN OTHERS THEN NULL;
END;
/
CREATE SEQUENCE "HAIWEI"."SEQ_DMS_PLAN_MAINT"
  MINVALUE 1
  MAXVALUE 9999999999999999999999999999
  START WITH 1
  INCREMENT BY 1
  CACHE 20;

-- ----------------------------
-- 22. SEQ_DMS_PLAN_MAINT_DETAIL (保养计划明细)
-- 表: DMS_PLAN_MAINT_DETAIL
-- ----------------------------
BEGIN
   EXECUTE IMMEDIATE 'DROP SEQUENCE "HAIWEI"."SEQ_DMS_PLAN_MAINT_DETAIL"';
EXCEPTION
   WHEN OTHERS THEN NULL;
END;
/
CREATE SEQUENCE "HAIWEI"."SEQ_DMS_PLAN_MAINT_DETAIL"
  MINVALUE 1
  MAXVALUE 9999999999999999999999999999
  START WITH 1
  INCREMENT BY 1
  CACHE 20;

-- ----------------------------
-- 23. SEQ_DMS_PLAN_REPAIR (检修计划)
-- 表: DMS_PLAN_REPAIR
-- ----------------------------
BEGIN
   EXECUTE IMMEDIATE 'DROP SEQUENCE "HAIWEI"."SEQ_DMS_PLAN_REPAIR"';
EXCEPTION
   WHEN OTHERS THEN NULL;
END;
/
CREATE SEQUENCE "HAIWEI"."SEQ_DMS_PLAN_REPAIR"
  MINVALUE 1
  MAXVALUE 9999999999999999999999999999
  START WITH 1
  INCREMENT BY 1
  CACHE 20;

-- ----------------------------
-- 24. SEQ_DMS_REPAIR_MATERIAL (维修物料)
-- 表: DMS_REPAIR_MATERIAL
-- ----------------------------
BEGIN
   EXECUTE IMMEDIATE 'DROP SEQUENCE "HAIWEI"."SEQ_DMS_REPAIR_MATERIAL"';
EXCEPTION
   WHEN OTHERS THEN NULL;
END;
/
CREATE SEQUENCE "HAIWEI"."SEQ_DMS_REPAIR_MATERIAL"
  MINVALUE 1
  MAXVALUE 9999999999999999999999999999
  START WITH 1
  INCREMENT BY 1
  CACHE 20;

-- ----------------------------
-- 25. SEQ_DMS_REPAIR_PROJECT (维修项目)
-- 表: DMS_REPAIR_PROJECT
-- ----------------------------
BEGIN
   EXECUTE IMMEDIATE 'DROP SEQUENCE "HAIWEI"."SEQ_DMS_REPAIR_PROJECT"';
EXCEPTION
   WHEN OTHERS THEN NULL;
END;
/
CREATE SEQUENCE "HAIWEI"."SEQ_DMS_REPAIR_PROJECT"
  MINVALUE 1
  MAXVALUE 9999999999999999999999999999
  START WITH 1
  INCREMENT BY 1
  CACHE 20;

-- ----------------------------
-- 26. SEQ_DMS_REPAIR_RECORD (维修记录)
-- 表: DMS_REPAIR_RECORD
-- ----------------------------
BEGIN
   EXECUTE IMMEDIATE 'DROP SEQUENCE "HAIWEI"."SEQ_DMS_REPAIR_RECORD"';
EXCEPTION
   WHEN OTHERS THEN NULL;
END;
/
CREATE SEQUENCE "HAIWEI"."SEQ_DMS_REPAIR_RECORD"
  MINVALUE 1
  MAXVALUE 9999999999999999999999999999
  START WITH 1
  INCREMENT BY 1
  CACHE 20;

-- ----------------------------
-- 27. SEQ_DMS_REPAIR_WORK_ORDER (维修工单)
-- 表: DMS_REPAIR_WORK_ORDER
-- ----------------------------
BEGIN
   EXECUTE IMMEDIATE 'DROP SEQUENCE "HAIWEI"."SEQ_DMS_REPAIR_WORK_ORDER"';
EXCEPTION
   WHEN OTHERS THEN NULL;
END;
/
CREATE SEQUENCE "HAIWEI"."SEQ_DMS_REPAIR_WORK_ORDER"
  MINVALUE 1
  MAXVALUE 9999999999999999999999999999
  START WITH 1
  INCREMENT BY 1
  CACHE 20;

-- 序列对齐：按表当前行数 + 1 设置 NEXTVAL（Oracle 11g 兼容方案）
-- 实现思路：
-- 1) 读取表行数，目标值 = 行数 + 1
-- 2) 读取当前序列 last_number
-- 3) 若目标值 > last_number，则临时修改 INCREMENT BY (差值)，取一次 NEXTVAL，最后再改回 1
-- 4) 若目标值 <= last_number，则保持不变，仅输出提示
DECLARE
  PROCEDURE sync_seq(p_seq_name VARCHAR2, p_table_name VARCHAR2) IS
    v_cnt       NUMBER := 0;
    v_last      NUMBER := 0;
    v_target    NUMBER := 0;
    v_diff      NUMBER := 0;
    v_seq_short VARCHAR2(128);
    v_sql       VARCHAR2(4000);
    v_dummy     NUMBER;
  BEGIN
    -- 表行数
    v_sql := 'SELECT NVL(COUNT(*),0) FROM ' || p_table_name;
    EXECUTE IMMEDIATE v_sql INTO v_cnt;

    -- 取 sequence 名（去 schema 部分）用于 USER_SEQUENCES 查询
    v_seq_short := UPPER(REGEXP_SUBSTR(p_seq_name, '[^\.]+$'));
    SELECT last_number INTO v_last FROM user_sequences WHERE sequence_name = v_seq_short;

    v_target := v_cnt + 1;

    IF v_target > v_last THEN
      v_diff := v_target - v_last;
      EXECUTE IMMEDIATE 'ALTER SEQUENCE ' || p_seq_name || ' INCREMENT BY ' || v_diff;
      EXECUTE IMMEDIATE 'SELECT ' || p_seq_name || '.NEXTVAL FROM DUAL' INTO v_dummy;
      EXECUTE IMMEDIATE 'ALTER SEQUENCE ' || p_seq_name || ' INCREMENT BY 1';
      DBMS_OUTPUT.PUT_LINE('SYNC ' || p_seq_name || ' TO ' || v_target || ' (TABLE ROWS=' || v_cnt || ')');
    ELSE
      DBMS_OUTPUT.PUT_LINE('SKIP ' || p_seq_name || ' (TARGET ' || v_target || ' <= LAST ' || v_last || ')');
    END IF;
  EXCEPTION
    WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('SYNC ' || p_seq_name || ' FAILED: ' || SQLERRM);
  END;
BEGIN
  sync_seq('HAIWEI.SEQ_DMS_INSPECT_PROJ', 'HAIWEI.DMS_BASE_INSPECT_PROJECT');
  sync_seq('HAIWEI.SEQ_DMS_INSPECT_ROUTE', 'HAIWEI.DMS_BASE_INSPECT_ROUTE');
  sync_seq('HAIWEI.SEQ_DMS_INSPECT_STD', 'HAIWEI.DMS_BASE_INSPECT_STANDARD');
  sync_seq('HAIWEI.SEQ_DMS_INSPECT_RTE_DTL', 'HAIWEI.DMS_INSPECT_ROUTE_DETAIL');
  sync_seq('HAIWEI.SEQ_DMS_LUBE_STD', 'HAIWEI.DMS_BASE_LUBE_STANDARD');
  sync_seq('HAIWEI.SEQ_DMS_LUBE_STATION', 'HAIWEI.DMS_BASE_LUBE_STATION');
  sync_seq('HAIWEI.SEQ_DMS_MAINT_PROJ', 'HAIWEI.DMS_BASE_MAINT_PROJECT');
  sync_seq('HAIWEI.SEQ_DMS_MAINT_STD', 'HAIWEI.DMS_BASE_MAINT_STANDARD');
  sync_seq('HAIWEI.SEQ_DMS_MAINT_STATION', 'HAIWEI.DMS_BASE_MAINT_STATION');
  sync_seq('HAIWEI.SEQ_DMS_SHUT_REASON', 'HAIWEI.DMS_BASE_SHUT_REASON');
  sync_seq('HAIWEI.SEQ_DMS_SHUT_TYPE', 'HAIWEI.DMS_BASE_SHUT_TYPE');
  sync_seq('HAIWEI.SEQ_DMS_BILLS_FAULT_INSTANCE', 'HAIWEI.DMS_BILLS_FAULT_INSTANCE');
  sync_seq('HAIWEI.SEQ_FAULT_INSTANCE_ACTIVITY', 'HAIWEI.DMS_FAULT_INSTANCE_ACTIVITY');
  sync_seq('HAIWEI.SEQ_DMS_MAINT_INST', 'HAIWEI.DMS_BILLS_MAINT_INSTANCE');
  sync_seq('HAIWEI.SEQ_DMS_MAINT_DETAIL', 'HAIWEI.DMS_BILLS_MAINT_DETAIL');
  sync_seq('HAIWEI.SEQ_DMS_MAINT_DTL_PROJ', 'HAIWEI.DMS_BILLS_MAINT_DETAIL_PROJECT');
  sync_seq('HAIWEI.SEQ_DMS_DISPATCH_PLAN', 'HAIWEI.DMS_DISPATCH_PLAN');
  sync_seq('HAIWEI.SEQ_DMS_DISPATCH_MEMBER', 'HAIWEI.DMS_DISPATCH_MEMBER');
  sync_seq('HAIWEI.SEQ_DMS_KNOWLEDGE_REPAIR', 'HAIWEI.DMS_KNOWLEDGE_REPAIR');
  sync_seq('HAIWEI.SEQ_DMS_PLAN_INSPECT', 'HAIWEI.DMS_PLAN_INSPECT');
  sync_seq('HAIWEI.SEQ_DMS_PLAN_MAINT', 'HAIWEI.DMS_PLAN_MAINT');
  sync_seq('HAIWEI.SEQ_DMS_PLAN_MAINT_DETAIL', 'HAIWEI.DMS_PLAN_MAINT_DETAIL');
  sync_seq('HAIWEI.SEQ_DMS_PLAN_REPAIR', 'HAIWEI.DMS_PLAN_REPAIR');
  sync_seq('HAIWEI.SEQ_DMS_REPAIR_MATERIAL', 'HAIWEI.DMS_REPAIR_MATERIAL');
  sync_seq('HAIWEI.SEQ_DMS_REPAIR_PROJECT', 'HAIWEI.DMS_REPAIR_PROJECT');
  sync_seq('HAIWEI.SEQ_DMS_REPAIR_RECORD', 'HAIWEI.DMS_REPAIR_RECORD');
  sync_seq('HAIWEI.SEQ_DMS_REPAIR_WORK_ORDER', 'HAIWEI.DMS_REPAIR_WORK_ORDER');
END;
/

-- ============================================
-- 执行完成提示
-- ============================================
-- 共创建 27 个序列
-- 如需验证，可执行:
-- SELECT SEQUENCE_NAME FROM USER_SEQUENCES WHERE SEQUENCE_NAME LIKE 'SEQ_DMS%' OR SEQUENCE_NAME LIKE 'SEQ_FAULT%';