CREATE TABLE "BLADE_NG_REASON_INFO"
(
    "ID"          NUMBER(20) NOT NULL,
    "TENANT_ID"   NVARCHAR2(12) DEFAULT 000000,
    "TYPE_ID"     NUMBER(20),
    "REASON_CODE" NVARCHAR2(24),
    "REASON_NAME" NVARCHAR2(64),
    "CREATE_USER" NUMBER(20),
    "CREATE_DEPT" NUMBER(20),
    "CREATE_TIME" DATE,
    "UPDATE_USER" NUMBER(20),
    "UPDATE_TIME" DATE,
    "REMARK"      NVARCHAR2(255),
    "IS_DELETED"  NUMBER(11),
    "STATUS"      NUMBER(11)
) TABLESPACE "USERS"
    LOGGING
    NOCOMPRESS
    PCTFREE 10
    INITRANS 1
    STORAGE
(
    BUFFER_POOL DEFAULT
)
    PARALLEL 1
    NOCACHE
    DISABLE ROW MOVEMENT
;
COMMENT
ON COLUMN "BLADE_NG_REASON_INFO"."ID" IS 'id';
COMMENT
ON COLUMN "BLADE_NG_REASON_INFO"."TENANT_ID" IS '租户ID';
COMMENT
ON COLUMN "BLADE_NG_REASON_INFO"."TYPE_ID" IS '父类型id(暂没用,现使用的是公共组表做关联)';
COMMENT
ON COLUMN "BLADE_NG_REASON_INFO"."REASON_CODE" IS '原因编号';
COMMENT
ON COLUMN "BLADE_NG_REASON_INFO"."REASON_NAME" IS '原因名称';
COMMENT
ON COLUMN "BLADE_NG_REASON_INFO"."CREATE_USER" IS '创建人';
COMMENT
ON COLUMN "BLADE_NG_REASON_INFO"."CREATE_DEPT" IS '创建单位';
COMMENT
ON COLUMN "BLADE_NG_REASON_INFO"."CREATE_TIME" IS '创建时间';
COMMENT
ON COLUMN "BLADE_NG_REASON_INFO"."UPDATE_USER" IS '更新人';
COMMENT
ON COLUMN "BLADE_NG_REASON_INFO"."UPDATE_TIME" IS '更新时间';
COMMENT
ON COLUMN "BLADE_NG_REASON_INFO"."REMARK" IS '备注';
COMMENT
ON COLUMN "BLADE_NG_REASON_INFO"."STATUS" IS '业务状态';
COMMENT
ON TABLE "BLADE_NG_REASON_INFO" IS 'NG原因维护信息表';


-- ----------------------------
-- Primary Key structure for table BLADE_NG_REASON_INFO
-- ----------------------------
ALTER TABLE "BLADE_NG_REASON_INFO"
    ADD CONSTRAINT "1655780238PrimaryId" PRIMARY KEY ("ID");


-- ----------------------------
-- Indexes structure for table BLADE_NG_REASON_INFO
-- ----------------------------
CREATE
    UNIQUE INDEX "1654843172UniqueReason_code"
    ON "BLADE_NG_REASON_INFO" ("IS_DELETED" ASC, "REASON_CODE" ASC) LOGGING
    TABLESPACE "USERS"
    VISIBLE
    PCTFREE 10
    INITRANS 2
    STORAGE (
    BUFFER_POOL DEFAULT
    );



CREATE TABLE "BLADE_TRACE_FLOW_RECORD"
(
    "ID"                    NUMBER(20) NOT NULL,
    "REMARK"                NVARCHAR2(255),
    "CREATE_USER"           NUMBER(20),
    "CREATE_DEPT"           NUMBER(20),
    "CREATE_TIME"           DATE,
    "UPDATE_USER"           NUMBER(20),
    "UPDATE_TIME"           DATE,
    "STATUS"                NUMBER(11),
    "IS_DELETED"            NUMBER(11),
    "TENANT_ID"             NVARCHAR2(10) DEFAULT 000000,
    "PART_NO"               NVARCHAR2(50) NOT NULL,
    "FLOW_CODE"             NVARCHAR2(50) NOT NULL,
    "FLOW_DISPLAY_NAME"     NVARCHAR2(50) NOT NULL,
    "FLOW_STATE"            NUMBER(11),
    "FLOW_TAG"              NUMBER(11),
    "TRACE_FLOW_SETTING_ID" NUMBER(20),
    "WORKSTATION_ID"        NUMBER(20),
    "WORKSTATION_CODE"      NVARCHAR2(50),
    "WORKSTATION_NAME"      NVARCHAR2(50),
    "WORKBENCH_ID"          NUMBER(20),
    "WORKBENCH_NAME"        NVARCHAR2(50),
    "ENTRY_TIME"            DATE,
    "LEFT_TIME"             DATE,
    "EXTENSION_DATA"        NCLOB,
    "ARCHIVE_TABLE"         NVARCHAR2(50),
    "POSITION"              NVARCHAR2(50)
) TABLESPACE "USERS"
    LOGGING
    NOCOMPRESS
    PCTFREE 10
    INITRANS 1
    STORAGE
(
    BUFFER_POOL DEFAULT
)
    PARALLEL 1
    NOCACHE
    DISABLE ROW MOVEMENT
;
COMMENT
ON COLUMN "BLADE_TRACE_FLOW_RECORD"."REMARK" IS '备注';
COMMENT
ON COLUMN "BLADE_TRACE_FLOW_RECORD"."PART_NO" IS '工件编号';
COMMENT
ON COLUMN "BLADE_TRACE_FLOW_RECORD"."FLOW_CODE" IS '流程编号';
COMMENT
ON COLUMN "BLADE_TRACE_FLOW_RECORD"."FLOW_DISPLAY_NAME" IS '流程名称';
COMMENT
ON COLUMN "BLADE_TRACE_FLOW_RECORD"."FLOW_STATE" IS '流程状态:1 完成  2 进行中';
COMMENT
ON COLUMN "BLADE_TRACE_FLOW_RECORD"."FLOW_TAG" IS '流程标识:1 质检合格 2 质检不合格';
COMMENT
ON COLUMN "BLADE_TRACE_FLOW_RECORD"."TRACE_FLOW_SETTING_ID" IS '流程设定ID';
COMMENT
ON COLUMN "BLADE_TRACE_FLOW_RECORD"."WORKSTATION_ID" IS '工位id';
COMMENT
ON COLUMN "BLADE_TRACE_FLOW_RECORD"."WORKSTATION_CODE" IS '工位编号';
COMMENT
ON COLUMN "BLADE_TRACE_FLOW_RECORD"."WORKSTATION_NAME" IS '工位名称';
COMMENT
ON COLUMN "BLADE_TRACE_FLOW_RECORD"."WORKBENCH_ID" IS '工作台id';
COMMENT
ON COLUMN "BLADE_TRACE_FLOW_RECORD"."WORKBENCH_NAME" IS '工作台名称';
COMMENT
ON COLUMN "BLADE_TRACE_FLOW_RECORD"."ENTRY_TIME" IS '进入时间';
COMMENT
ON COLUMN "BLADE_TRACE_FLOW_RECORD"."LEFT_TIME" IS '离开时间';
COMMENT
ON COLUMN "BLADE_TRACE_FLOW_RECORD"."EXTENSION_DATA" IS '数据';
COMMENT
ON COLUMN "BLADE_TRACE_FLOW_RECORD"."POSITION" IS '位置';
COMMENT
ON TABLE "BLADE_TRACE_FLOW_RECORD" IS '工件流程记录';

-- ----------------------------
-- Primary Key structure for table BLADE_TRACE_FLOW_RECORD
-- ----------------------------
ALTER TABLE "BLADE_TRACE_FLOW_RECORD"
    ADD CONSTRAINT "1655780285PrimaryId" PRIMARY KEY ("ID");

DROP TRIGGER "tray_tree_id_tri";

CREATE TABLE "BLADE_TRACE_CATALOG"
(
    "ID"                   NUMBER(20) NOT NULL,
    "REMARK"               NVARCHAR2(255),
    "CREATE_USER"          NUMBER(20),
    "CREATE_DEPT"          NUMBER(20),
    "CREATE_TIME"          DATE,
    "UPDATE_USER"          NUMBER(20),
    "UPDATE_TIME"          DATE,
    "STATUS"               NUMBER(11),
    "IS_DELETED"           NUMBER(11),
    "TENANT_ID"            NVARCHAR2(10) DEFAULT 000000,
    "PART_NO"              NVARCHAR2(50) NOT NULL,
    "BATCH_NUMBER"         NVARCHAR2(50),
    "ONLINE_TIME"          DATE,
    "OFFLINE_TIME"         DATE,
    "QUALIFIED"            NUMBER(4),
    "IS_REWORK_PART"       NUMBER(4),
    "SHIFT_INDEX"          NUMBER(11),
    "SHIFT_INDEX_NAME"     NVARCHAR2(50),
    "PLAN_ID"              NUMBER(11),
    "TRACE_STATUS"         NUMBER(11),
    "WORKSTATION_GROUP_ID" NUMBER(20)
) TABLESPACE "USERS"
    LOGGING
    NOCOMPRESS
    PCTFREE 10
    INITRANS 1
    STORAGE
(
    BUFFER_POOL DEFAULT
)
    PARALLEL 1
    NOCACHE
    DISABLE ROW MOVEMENT
;
COMMENT
ON COLUMN "BLADE_TRACE_CATALOG"."REMARK" IS '备注';
COMMENT
ON COLUMN "BLADE_TRACE_CATALOG"."PART_NO" IS '工件编号';
COMMENT
ON COLUMN "BLADE_TRACE_CATALOG"."BATCH_NUMBER" IS '批次号';
COMMENT
ON COLUMN "BLADE_TRACE_CATALOG"."ONLINE_TIME" IS '上线时间';
COMMENT
ON COLUMN "BLADE_TRACE_CATALOG"."OFFLINE_TIME" IS '下线时间';
COMMENT
ON COLUMN "BLADE_TRACE_CATALOG"."QUALIFIED" IS '是否合格：0 否 1 是';
COMMENT
ON COLUMN "BLADE_TRACE_CATALOG"."IS_REWORK_PART" IS '是否返工：0 否 1 是';
COMMENT
ON COLUMN "BLADE_TRACE_CATALOG"."SHIFT_INDEX" IS '班次下标';
COMMENT
ON COLUMN "BLADE_TRACE_CATALOG"."SHIFT_INDEX_NAME" IS '班次下标别名';
COMMENT
ON COLUMN "BLADE_TRACE_CATALOG"."PLAN_ID" IS '计划id,无计划填0';
COMMENT
ON COLUMN "BLADE_TRACE_CATALOG"."TRACE_STATUS" IS '工件转态：1 已下线 2 未下线 3 未知';
COMMENT
ON COLUMN "BLADE_TRACE_CATALOG"."WORKSTATION_GROUP_ID" IS '工位组id';
COMMENT
ON TABLE "BLADE_TRACE_CATALOG" IS '工件上线记录';

-- ----------------------------
-- Primary Key structure for table BLADE_TRACE_CATALOG
-- ----------------------------
ALTER TABLE "BLADE_TRACE_CATALOG"
    ADD CONSTRAINT "1655780284PrimaryId" PRIMARY KEY ("ID");

DROP TRIGGER "catalog_tree_id_tri";

CREATE TABLE "BLADE_TRACE_FLOW_SETTINGS"
(
    "ID"                     NUMBER(20) NOT NULL,
    "TENANT_ID"              NVARCHAR2(12),
    "CODE"                   NVARCHAR2(40),
    "NAME"                   NVARCHAR2(100),
    "FLOW_SEQ"               NUMBER(11),
    "STATION_TYPE"           NUMBER(11),
    "WORKSTATION_GROUP_ID"   NUMBER(20),
    "PRE_FLOW_ID"            NUMBER(20),
    "NEXT_FLOW_ID"           NUMBER(20),
    "FLOW_TYPE"              NUMBER(11),
    "TRIGGER_END_FLOW_STYLE" NUMBER(11),
    "QUALITY_RESULT_OFFLINE" NVARCHAR2(32),
    "QUALITY_MAKER_FLOW_ID"  NUMBER(20),
    "IS_DELETED"             NUMBER(11),
    "CREATE_TIME"            DATE,
    "CREATE_USER"            NUMBER(20),
    "UPDATE_TIME"            DATE,
    "UPDATE_USER"            NUMBER(20),
    "CREATE_DEPT"            NUMBER(20),
    "STATUS"                 NUMBER(11),
    "EXTENSION_DATA"         NCLOB
) TABLESPACE "USERS"
    LOGGING
    NOCOMPRESS
    PCTFREE 10
    INITRANS 1
    STORAGE
(
    BUFFER_POOL DEFAULT
)
    PARALLEL 1
    NOCACHE
    DISABLE ROW MOVEMENT
;
COMMENT
ON COLUMN "BLADE_TRACE_FLOW_SETTINGS"."TENANT_ID" IS '所属租户';
COMMENT
ON COLUMN "BLADE_TRACE_FLOW_SETTINGS"."CODE" IS '流程编号';
COMMENT
ON COLUMN "BLADE_TRACE_FLOW_SETTINGS"."NAME" IS '流程名称';
COMMENT
ON COLUMN "BLADE_TRACE_FLOW_SETTINGS"."FLOW_SEQ" IS '流程顺序';
COMMENT
ON COLUMN "BLADE_TRACE_FLOW_SETTINGS"."STATION_TYPE" IS '工位类型 0-其他 1-打标 2-测量 3-清洗 4-装卸站 5-加工 6-搬运 7-三坐标';
COMMENT
ON COLUMN "BLADE_TRACE_FLOW_SETTINGS"."WORKSTATION_GROUP_ID" IS '工位组id';
COMMENT
ON COLUMN "BLADE_TRACE_FLOW_SETTINGS"."PRE_FLOW_ID" IS '上一流程id';
COMMENT
ON COLUMN "BLADE_TRACE_FLOW_SETTINGS"."NEXT_FLOW_ID" IS '下一流程id';
COMMENT
ON COLUMN "BLADE_TRACE_FLOW_SETTINGS"."FLOW_TYPE" IS '流程类别 1-上线流程 2-途径流程 3-下线流程 4-可选流程';
COMMENT
ON COLUMN "BLADE_TRACE_FLOW_SETTINGS"."TRIGGER_END_FLOW_STYLE" IS '结束流程方式 1-仅本流程 2-仅上个流程 3-本流程和上个流程';
COMMENT
ON COLUMN "BLADE_TRACE_FLOW_SETTINGS"."QUALITY_RESULT_OFFLINE" IS '按质量结果下线 1-OK下线 2-NG下线';
COMMENT
ON COLUMN "BLADE_TRACE_FLOW_SETTINGS"."QUALITY_MAKER_FLOW_ID" IS '质量责任归属到特定流程';
COMMENT
ON COLUMN "BLADE_TRACE_FLOW_SETTINGS"."CREATE_TIME" IS '创建时间';
COMMENT
ON COLUMN "BLADE_TRACE_FLOW_SETTINGS"."CREATE_USER" IS '创建人';
COMMENT
ON COLUMN "BLADE_TRACE_FLOW_SETTINGS"."UPDATE_TIME" IS '更新时间';
COMMENT
ON COLUMN "BLADE_TRACE_FLOW_SETTINGS"."UPDATE_USER" IS '更新人';
COMMENT
ON COLUMN "BLADE_TRACE_FLOW_SETTINGS"."CREATE_DEPT" IS '创建部门';
COMMENT
ON COLUMN "BLADE_TRACE_FLOW_SETTINGS"."STATUS" IS '业务状态';
COMMENT
ON COLUMN "BLADE_TRACE_FLOW_SETTINGS"."EXTENSION_DATA" IS '数据';
COMMENT
ON TABLE "BLADE_TRACE_FLOW_SETTINGS" IS '流程维护';

-- ----------------------------
-- Primary Key structure for table BLADE_TRACE_FLOW_SETTINGS
-- ----------------------------
ALTER TABLE "BLADE_TRACE_FLOW_SETTINGS"
    ADD CONSTRAINT "1655780286PrimaryId" PRIMARY KEY ("ID");


-- ----------------------------
-- Indexes structure for table BLADE_TRACE_FLOW_SETTINGS
-- ----------------------------
CREATE
    UNIQUE INDEX "1654843195UniqueCode"
    ON "BLADE_TRACE_FLOW_SETTINGS" ("IS_DELETED" ASC, "CODE" ASC) LOGGING
    TABLESPACE "USERS"
    VISIBLE
    PCTFREE 10
    INITRANS 2
    STORAGE (
    BUFFER_POOL DEFAULT
    );


-- 追溯关联工位
CREATE TABLE "TRACE_REF_WORKSTATION"
(
    "ID"                    NUMBER(20) NOT NULL,
    "WORKSTATION_ID"        NUMBER(20) NOT NULL,
    "WORKBENCH_ID"          NUMBER(20) NOT NULL,
    "TRACE_FLOW_SETTING_ID" NUMBER(20) NOT NULL,
    "MACHINING_POSITION"    NVARCHAR2(255)
) TABLESPACE "USERS"
    LOGGING
    NOCOMPRESS
    PCTFREE 10
    INITRANS 1
    STORAGE
(
    BUFFER_POOL DEFAULT
)
    PARALLEL 1
    NOCACHE
    DISABLE ROW MOVEMENT
;
COMMENT
ON COLUMN "TRACE_REF_WORKSTATION"."WORKSTATION_ID" IS '工位id';
COMMENT
ON COLUMN "TRACE_REF_WORKSTATION"."WORKBENCH_ID" IS '工作台id';
COMMENT
ON COLUMN "TRACE_REF_WORKSTATION"."TRACE_FLOW_SETTING_ID" IS '流程设定ID';
COMMENT
ON COLUMN "TRACE_REF_WORKSTATION"."MACHINING_POSITION" IS '加工位置';
COMMENT
ON TABLE "TRACE_REF_WORKSTATION" IS '追溯关联工位';

-- ----------------------------
-- Primary Key structure for table TRACE_REF_WORKSTATION
-- ----------------------------
ALTER TABLE "TRACE_REF_WORKSTATION"
    ADD CONSTRAINT "1655780327PrimaryId" PRIMARY KEY ("ID");


-- 工件追溯NG表
CREATE TABLE "BLADE_TRACE_RECORD_NG_REASON"
(
    "ID"               NUMBER(20) NOT NULL,
    "RECORD_ID"        NUMBER(20),
    "BAD_PART_ID"      NUMBER(20),
    "BAD_PART"         NVARCHAR2(32),
    "REASON_ID"        NUMBER(20),
    "REASON_CODE"      NVARCHAR2(24),
    "REASON_NAME"      NVARCHAR2(64),
    "CREATE_USER_NAME" NVARCHAR2(255),
    "TENANT_ID"        NVARCHAR2(12) DEFAULT 000000,
    "STATUS"           NUMBER(4) DEFAULT 1,
    "IS_DELETED"       NUMBER(20),
    "CREATE_USER"      NUMBER(20),
    "CREATE_DEPT"      NUMBER(20),
    "CREATE_TIME"      DATE,
    "UPDATE_USER"      NUMBER(20),
    "UPDATE_TIME"      DATE
) TABLESPACE "USERS"
    LOGGING
    NOCOMPRESS
    PCTFREE 10
    INITRANS 1
    STORAGE
(
    BUFFER_POOL DEFAULT
)
    PARALLEL 1
    NOCACHE
    DISABLE ROW MOVEMENT
;
COMMENT
ON COLUMN "BLADE_TRACE_RECORD_NG_REASON"."RECORD_ID" IS '工件流程记录id';
COMMENT
ON COLUMN "BLADE_TRACE_RECORD_NG_REASON"."BAD_PART_ID" IS '不良部位id';
COMMENT
ON COLUMN "BLADE_TRACE_RECORD_NG_REASON"."BAD_PART" IS '不良部位';
COMMENT
ON COLUMN "BLADE_TRACE_RECORD_NG_REASON"."REASON_ID" IS '原因id';
COMMENT
ON COLUMN "BLADE_TRACE_RECORD_NG_REASON"."REASON_CODE" IS '原因编号';
COMMENT
ON COLUMN "BLADE_TRACE_RECORD_NG_REASON"."REASON_NAME" IS '原因名称';
COMMENT
ON COLUMN "BLADE_TRACE_RECORD_NG_REASON"."CREATE_USER_NAME" IS '创建用户名称';
COMMENT
ON COLUMN "BLADE_TRACE_RECORD_NG_REASON"."TENANT_ID" IS '租户ID';
COMMENT
ON COLUMN "BLADE_TRACE_RECORD_NG_REASON"."STATUS" IS '业务状态 1激活 0 冻结';
COMMENT
ON COLUMN "BLADE_TRACE_RECORD_NG_REASON"."IS_DELETED" IS '删除标记：0-未删除，1-已删除';
COMMENT
ON COLUMN "BLADE_TRACE_RECORD_NG_REASON"."CREATE_USER" IS '创建人';
COMMENT
ON COLUMN "BLADE_TRACE_RECORD_NG_REASON"."CREATE_DEPT" IS '创建部门';
COMMENT
ON COLUMN "BLADE_TRACE_RECORD_NG_REASON"."CREATE_TIME" IS '创建时间';
COMMENT
ON COLUMN "BLADE_TRACE_RECORD_NG_REASON"."UPDATE_USER" IS '更新人';
COMMENT
ON COLUMN "BLADE_TRACE_RECORD_NG_REASON"."UPDATE_TIME" IS '更新时间';
COMMENT
ON TABLE "BLADE_TRACE_RECORD_NG_REASON" IS '工件流程NG原因表';


-- ----------------------------
-- Primary Key structure for table BLADE_TRACE_RECORD_NG_REASON
-- ----------------------------
ALTER TABLE "BLADE_TRACE_RECORD_NG_REASON"
    ADD CONSTRAINT "1655780287PrimaryId" PRIMARY KEY ("ID");


-- 菜单数据
INSERT INTO "BLADE_MENU"(id, parent_id, code, name, alias, path, source, sort, category, action, is_open, remark,
                         is_deleted, belong_application, keep_alive)
VALUES ('1518863601934606338', '0', 'trace', '工件追溯', 'trace', '/trace', 'iconfont saber-zhushuju', '8', '0', '0',
        '1',
        NULL, '0', 'CPS', 1);
INSERT INTO "BLADE_MENU"(id, parent_id, code, name, alias, path, source, sort, category, action, is_open, remark,
                         is_deleted, belong_application, keep_alive)
VALUES ('1518864188126339074', '1518863601934606338', 'trace_query', '追溯查询', 'trace_query', '/trace/query/index',
        'iconfont iconicon_search', '1', '1', '0', '1', NULL, '0', 'CPS', 1);
INSERT INTO "BLADE_MENU"(id, parent_id, code, name, alias, path, source, sort, category, action, is_open, remark,
                         is_deleted, belong_application, keep_alive)
VALUES ('1519183072176939010', '1518863601934606338', 'processMaintenance', '流程维护', 'processMaintenance',
        '/trace/maintain/index', 'iconfont saber-caijifenxi', '3', '1', '0', '1', NULL, '0', 'CPS', 1);
INSERT INTO "BLADE_MENU"(id, parent_id, code, name, alias, path, source, sort, category, action, is_open, remark,
                         is_deleted, belong_application, keep_alive)
VALUES ('1522377449602215938', '1518863601934606338', 'ng', 'NG原因维护', 'ng', '/trace/ng-info/index',
        'iconfont iconicon_setting', '4', '1', '0', '1', NULL, '0', 'CPS', 1);
INSERT INTO "BLADE_MENU"(id, parent_id, code, name, alias, path, source, sort, category, action, is_open, remark,
                         is_deleted, belong_application, keep_alive)
VALUES ('1523465621521539073', '1518863601934606338', 'ng_trace', 'NG工件追溯', 'ng_trace', '/trace/ng-trace/index',
        'iconfont iconicon_ding', '99', '1', '0', '1', NULL, '0', 'CPS', 1);

-- 追溯表相关索引
CREATE UNIQUE INDEX default_index ON blade_trace_catalog (part_no, batch_number, workstation_group_id, online_time,
                                                          offline_time);

CREATE INDEX time_index ON blade_trace_catalog (online_time, offline_time, part_no, batch_number);

CREATE INDEX record_default_index ON blade_trace_flow_record (part_no, workstation_id, entry_time);

CREATE INDEX record_flag_index ON blade_trace_flow_record (flow_tag, part_no, workstation_id);

CREATE INDEX ng_reason_default_index ON blade_trace_record_ng_reason (record_id);