uas-eis/sql/01_ddl_customs_tables.sql

-- ============================================================
-- 保税维修关务对接 - Oracle 表结构 DDL
-- 数据库：Oracle 11g/12c+
-- 字符集：AL32UTF8
-- 命名规范：表名 GW_ 前缀，主键 PK_，索引 IDX_
-- ============================================================

-- ============================================================
-- 一、基础配置表
-- ============================================================

-- 1.1 企业/账册基础信息配置表
CREATE TABLE GW_EMS_CONFIG (
                               CONFIG_ID    NUMBER(19)      NOT NULL,
                               EMS_NO          VARCHAR2(64)    NOT NULL,           -- 账册编号
                               ETPS_PRE_ENT_NO VARCHAR2(64)    NOT NULL,           -- 企业内部编号(预录入编号)
                               EMS_TYPECD      VARCHAR2(2),                         -- 账册类型 1:E账册 2:H账册 4:工单账册
                               MASTER_CUSCD    VARCHAR2(4),                         -- 主管关区代码
                               DCL_ETPS_TYPECD VARCHAR2(10),                        -- 申报单位代码(海关十位)
                               DCL_ETPS_SCCD   VARCHAR2(18),                        -- 申报单位社会信用代码
                               DCL_ETPS_NM     VARCHAR2(512),                       -- 申报单位名称
                               SENDER_ID       VARCHAR2(16),                        -- 发送方编号(海关十位)
                               RECEIVER_ID     VARCHAR2(16) DEFAULT 'NC-CUSTOMS',   -- 接收方编号
                               FINISH_VALID_DATE VARCHAR2(10),                      -- 结束有效期
                               VCLR_PRID_VAL   VARCHAR2(19),                        -- 核销周期
                               USAGE_TYPECD    VARCHAR2(4),                         -- 账册用途 3:保税维修
                               NETWK_ETPS_ARCRP_NO VARCHAR2(18),                    -- 联网企业档案库编号
                               APCRET_NO       VARCHAR2(80),                        -- 批准证编号
                               MAX_TOVR_AMT    NUMBER(19,5),                        -- 最大周转金额
                               MAX_APPR_IMP_AMT NUMBER(19,5),                       -- 最大进口金额
                               STATUS          VARCHAR2(2) DEFAULT '1',             -- 状态 1:启用 0:停用
                               REMARK          VARCHAR2(1000),
                               CREATE_TIME     TIMESTAMP DEFAULT SYSTIMESTAMP,
                               UPDATE_TIME     TIMESTAMP DEFAULT SYSTIMESTAMP,
                               CONSTRAINT PK_GW_EMS_CONFIG PRIMARY KEY (CONFIG_ID),
                               CONSTRAINT UK_GW_EMS_CONFIG_NO UNIQUE (EMS_NO)
);
COMMENT ON TABLE GW_EMS_CONFIG IS '关务账册基础配置表';
COMMENT ON COLUMN GW_EMS_CONFIG.EMS_NO IS '账册编号';
COMMENT ON COLUMN GW_EMS_CONFIG.ETPS_PRE_ENT_NO IS '企业内部编号(预录入编号)';

CREATE SEQUENCE GW_EMS_CONFIG_SEQ START WITH 1 INCREMENT BY 1 NOCACHE;

-- ============================================================
-- 二、WO101 账册备案
-- ============================================================

-- 2.1 账册备案表头
CREATE TABLE GW_EMS_HEAD (
                             EMS_HEAD_ID    NUMBER(19)      NOT NULL,
                             MESSAGE_ID          VARCHAR2(64)    NOT NULL,       -- 报文唯一ID
                             SEQ_NO              VARCHAR2(18),                    -- 统一编号(系统生成)
                             ETPS_PRE_ENT_NO     VARCHAR2(64)    NOT NULL,       -- 企业内部编号
                             CHG_TMS_CNT         VARCHAR2(19),                    -- 变更次数
                             EMS_NO              VARCHAR2(64)    NOT NULL,       -- 账册编号
                             EMS_TYPECD          VARCHAR2(2),                     -- 账册类型
                             FINISH_VALID_DATE   VARCHAR2(10),                    -- 结束有效期
                             ACTL_IMP_TOTAL_AMT  NUMBER(19,5),                    -- 实际进口总金额
                             ACTL_EXP_TOTAL_AMT  NUMBER(19,5),                    -- 实际出口总金额
                             APCRET_NO           VARCHAR2(80),                    -- 批准证编号
                             NETWK_ETPS_ARCRP_NO VARCHAR2(18),                    -- 联网企业档案库编号
                             MAX_TOVR_AMT        NUMBER(19,5),                    -- 最大周转金额
                             MAX_APPR_IMP_AMT    NUMBER(19,5),                    -- 最大进口金额
                             RCNT_VCLR_TIME      VARCHAR2(10),                    -- 最近核销日期
                             VCLR_PRID_VAL       VARCHAR2(19),                    -- 核销周期
                             USAGE_TYPECD        VARCHAR2(4),                     -- 账册用途
                             DCL_TYPECD          VARCHAR2(2),                     -- 申报类型 1:新增
                             DCL_TIME            VARCHAR2(19),                    -- 申报时间
                             EMAPV_STUCD         VARCHAR2(2),                     -- 审批状态 1:通过 2:退单
                             PUTREC_APPR_TIME    VARCHAR2(19),                    -- 备案批准时间
                             VCLR_TYPECD         VARCHAR2(2),                     -- 核销类型
                             RMK                 VARCHAR2(1000),                  -- 备注
                             SEND_STATUS         VARCHAR2(2) DEFAULT '0',         -- 发送状态 0:待发送 1:已发送 2:发送失败
                             RESP_CODE           VARCHAR2(2),                     -- 回执响应码
                             RESP_TEXT           VARCHAR2(1000),                  -- 回执信息
                             CREATE_TIME         TIMESTAMP DEFAULT SYSTIMESTAMP,
                             SEND_TIME           TIMESTAMP,
                             CONSTRAINT PK_GW_EMS_HEAD PRIMARY KEY (EMS_HEAD_ID),
                             CONSTRAINT UK_GW_EMS_HEAD_MSGID UNIQUE (MESSAGE_ID)
);
COMMENT ON TABLE GW_EMS_HEAD IS 'WO101账册备案表头';

CREATE SEQUENCE GW_EMS_HEAD_SEQ START WITH 1 INCREMENT BY 1 NOCACHE;
CREATE INDEX IDX_GW_EMS_HEAD_EMS ON GW_EMS_HEAD(EMS_NO);
CREATE INDEX IDX_GW_EMS_HEAD_STATUS ON GW_EMS_HEAD(SEND_STATUS);

-- 2.2 账册备案明细表
CREATE TABLE GW_EMS_LIST (
                             EMS_LIST_ID    NUMBER(19)      NOT NULL,
                             HEAD_ID             NUMBER(19)      NOT NULL,       -- 关联表头ID
                             SEQ_NO              VARCHAR2(18),
                             EMS_NO              VARCHAR2(64)    NOT NULL,
                             GDS_SEQ_NO          VARCHAR2(19),                    -- 料件序号
                             CHG_TMS_CNT         VARCHAR2(19),                    -- 变更次数
                             GDS_MTNO            VARCHAR2(32)    NOT NULL,       -- 商品料号
                             GDS_TYPE            VARCHAR2(2)     NOT NULL,       -- 料件类型 I/E/1/2/3/4
                             GDECD               VARCHAR2(10),                    -- 商品编码
                             GDS_NM              VARCHAR2(512),                   -- 商品名称
                             GDS_SPCF_MODEL_DESC VARCHAR2(512),                   -- 商品规格型号
                             DCL_UNITCD          VARCHAR2(3),                     -- 申报计量单位
                             DCL_QTY             NUMBER(19,5),                    -- 申报数量
                             DCL_UPRC_AMT        NUMBER(19,5),                    -- 申报单价
                             DCL_CURRCD          VARCHAR2(3),                     -- 申报币制
                             LAWF_UNITCD         VARCHAR2(3),                     -- 法定计量单位
                             LAWF_QTY            NUMBER(19,5),                    -- 法定数量
                             SECD_LAWF_UNITCD    VARCHAR2(3),                     -- 第二法定计量单位
                             SECD_LAWF_QTY       NUMBER(19,5),                    -- 第二法定数量
                             VCLR_PRID_INIT_QTY  NUMBER(19,5),                    -- 期初数量
                             ADJMTR_MARKCD       VARCHAR2(2),                     -- 主料标志 1:主料 2:辅料
                             MODF_MARKCD         VARCHAR2(2),                     -- 修改标志 0:未改 1:修改 2:删除 3:增加
                             QTY_CNTR_MARKCD     VARCHAR2(2),                     -- 数量控制标志
                             LVYRLF_MODECD       VARCHAR2(3),                     -- 征免方式
                             APPR_MAX_SURP_QTY   NUMBER(19,5),                    -- 批准最大余量
                             RMK                 VARCHAR2(2000),
                             GDS_SOURCE          VARCHAR2(2),                     -- 商品来源
                             ERP_ITEM_CODE       VARCHAR2(64),                    -- ERP物料编码(关联)
                             ERP_ITEM_NAME       VARCHAR2(255),                   -- ERP物料名称
                             CONSTRAINT PK_GW_EMS_LIST PRIMARY KEY (EMS_LIST_ID),
                             CONSTRAINT FK_GW_EMS_LIST_HEAD FOREIGN KEY (HEAD_ID) REFERENCES GW_EMS_HEAD(ID)
);
COMMENT ON TABLE GW_EMS_LIST IS 'WO101账册备案明细';

CREATE SEQUENCE GW_EMS_LIST_SEQ START WITH 1 INCREMENT BY 1 NOCACHE;
CREATE INDEX IDX_GW_EMS_LIST_HEAD ON GW_EMS_LIST(HEAD_ID);
CREATE INDEX IDX_GW_EMS_LIST_MTNO ON GW_EMS_LIST(GDS_MTNO);

-- ============================================================
-- 三、WO102 出入库确认
-- ============================================================

-- 3.1 出入库确认表头
CREATE TABLE GW_CONFIRM_HEAD (
                                 CONFIRM_HEAD_ID    NUMBER(19)      NOT NULL,
                                 MESSAGE_ID          VARCHAR2(64)    NOT NULL,
                                 SEQ_NO              VARCHAR2(18),
                                 ETPS_PRE_ENT_NO     VARCHAR2(64)    NOT NULL,
                                 EMS_NO              VARCHAR2(64)    NOT NULL,
                                 IOCONFIRM_TYPECD    VARCHAR2(10)    NOT NULL,       -- I:进 E:出
                                 MASTER_CUSCD        VARCHAR2(4),
                                 DCL_ETPS_TYPECD     VARCHAR2(10),
                                 DCL_ETPS_SCCD       VARCHAR2(18),
                                 DCL_ETPS_NM         VARCHAR2(512),
                                 DCL_TYPECD          VARCHAR2(2),                     -- 1:新增
                                 TAKE_TIME           VARCHAR2(19),                    -- 出入库日期
                                 RMK                 VARCHAR2(1000),
                                 SEND_STATUS         VARCHAR2(2) DEFAULT '0',
                                 RESP_CODE           VARCHAR2(2),
                                 RESP_TEXT           VARCHAR2(1000),
                                 CREATE_TIME         TIMESTAMP DEFAULT SYSTIMESTAMP,
                                 SEND_TIME           TIMESTAMP,
                                 CONSTRAINT PK_GW_CONFIRM_HEAD PRIMARY KEY (CONFIRM_HEAD_ID),
                                 CONSTRAINT UK_GW_CONFIRM_HEAD_MSGID UNIQUE (MESSAGE_ID)
);
COMMENT ON TABLE GW_CONFIRM_HEAD IS 'WO102出入库确认表头';

CREATE SEQUENCE GW_CONFIRM_HEAD_SEQ START WITH 1 INCREMENT BY 1 NOCACHE;
CREATE INDEX IDX_GW_CONFIRM_HEAD_EMS ON GW_CONFIRM_HEAD(EMS_NO);

-- 3.2 出入库确认明细表
CREATE TABLE GW_CONFIRM_LIST (
                                 CONFIRM_LIST_ID    NUMBER(19)      NOT NULL,
                                 HEAD_ID         NUMBER(19)      NOT NULL,
                                 SEQ_NO          VARCHAR2(18),
                                 BOND_INVT_NO    VARCHAR2(64)    NOT NULL,           -- 核注清单编号
                                 IE_FLAG         VARCHAR2(2)     NOT NULL,           -- I:进 E:出
                                 RMK             VARCHAR2(1000),
                                 ERP_INVT_NO     VARCHAR2(64),                        -- ERP入库单号
                                 CONSTRAINT PK_GW_CONFIRM_LIST PRIMARY KEY (CONFIRM_LIST_ID),
                                 CONSTRAINT FK_GW_CONFIRM_LIST_HEAD FOREIGN KEY (HEAD_ID) REFERENCES GW_CONFIRM_HEAD(ID)
);
COMMENT ON TABLE GW_CONFIRM_LIST IS 'WO102出入库确认明细';

CREATE SEQUENCE GW_CONFIRM_LIST_SEQ START WITH 1 INCREMENT BY 1 NOCACHE;
CREATE INDEX IDX_GW_CONFIRM_LIST_HEAD ON GW_CONFIRM_LIST(HEAD_ID);
CREATE INDEX IDX_GW_CONFIRM_LIST_INVT ON GW_CONFIRM_LIST(BOND_INVT_NO);

-- ============================================================
-- 四、WO103 工单
-- ============================================================

-- 4.1 工单表头
CREATE TABLE GW_WO_HEAD (
                            WO_HEAD_ID    NUMBER(19)      NOT NULL,
                            MESSAGE_ID          VARCHAR2(64)    NOT NULL,
                            SEQ_NO              VARCHAR2(18),
                            ETPS_PRE_ENT_NO     VARCHAR2(64)    NOT NULL,
                            MASTER_CUSCD        VARCHAR2(4),
                            DCL_ETPS_TYPECD     VARCHAR2(10),
                            DCL_ETPS_SCCD       VARCHAR2(18),
                            DCL_ETPS_NM         VARCHAR2(512),
                            DCL_TYPECD          VARCHAR2(2),                     -- 1:新增
                            EMS_NO              VARCHAR2(64)    NOT NULL,
                            WO_NO               VARCHAR2(12)    NOT NULL,       -- 工单编号(12位数字)
                            WO_TIME             VARCHAR2(19),                    -- 工单日期
                            BEGIN_TIME          VARCHAR2(19),                    -- 开工日期
                            END_TIME            VARCHAR2(19),                    -- 完工日期
                            WO_TYPE             VARCHAR2(2)     NOT NULL,       -- 1:加工 2:拆机 3:调整 4:重工
                            WO_ITEM_TYPE        VARCHAR2(2),                     -- 1:拆机 2:生产半成品 3:生产成品
                            WO_CLOSE_STATUS     VARCHAR2(2)     NOT NULL,       -- 1:关闭 2:未关闭
                            WO_OVER_STATUS      VARCHAR2(2),                     -- 核销状态
                            RMK                 VARCHAR2(1000),
                            ERP_WO_NO           VARCHAR2(64),                    -- ERP工单号
                            SEND_STATUS         VARCHAR2(2) DEFAULT '0',
                            RESP_CODE           VARCHAR2(2),
                            RESP_TEXT           VARCHAR2(1000),
                            CREATE_TIME         TIMESTAMP DEFAULT SYSTIMESTAMP,
                            SEND_TIME           TIMESTAMP,
                            CONSTRAINT PK_GW_WO_HEAD PRIMARY KEY (WO_HEAD_ID),
                            CONSTRAINT UK_GW_WO_HEAD_MSGID UNIQUE (MESSAGE_ID),
                            CONSTRAINT UK_GW_WO_HEAD_WONO UNIQUE (WO_NO)
);
COMMENT ON TABLE GW_WO_HEAD IS 'WO103工单表头';

CREATE SEQUENCE GW_WO_HEAD_SEQ START WITH 1 INCREMENT BY 1 NOCACHE;
CREATE INDEX IDX_GW_WO_HEAD_EMS ON GW_WO_HEAD(EMS_NO);
CREATE INDEX IDX_GW_WO_HEAD_ERPWO ON GW_WO_HEAD(ERP_WO_NO);

-- 4.2 工单生产入库明细 (WoExgList)
CREATE TABLE GW_WO_EXG_LIST (
                                WO_EXG_ID    NUMBER(19)      NOT NULL,
                                HEAD_ID             NUMBER(19)      NOT NULL,
                                SEQ_NO              VARCHAR2(18),
                                GDS_MTNO            VARCHAR2(32)    NOT NULL,
                                ITEM_TYPE           VARCHAR2(32),                    -- I/E/1/2/3/4
                                PROCESS_NO          VARCHAR2(64),                    -- 扣账编号
                                DCL_QTY             NUMBER(19,5)    NOT NULL,       -- 正数入库
                                DCL_UNITCD          VARCHAR2(3),
                                PUT_TIME            VARCHAR2(19),                    -- 入库日期
                                DISPOSAL            VARCHAR2(3),                     -- 废坏件处置 1:本地销毁 2:退运
                                GDECD               VARCHAR2(10),
                                GDS_NM              VARCHAR2(512),
                                GDS_SPCF_MODEL_DESC VARCHAR2(512),
                                DCL_UPRC_AMT        VARCHAR2(25),
                                DCL_CURRCD          VARCHAR2(3),
                                LAWF_UNITCD         VARCHAR2(3),
                                LAWF_QTY            VARCHAR2(19),
                                SECD_LAWF_UNITCD    VARCHAR2(3),
                                SECD_LAWF_QTY       VARCHAR2(19),
                                RMK                 VARCHAR2(2000),
                                ERP_ITEM_CODE       VARCHAR2(64),
                                CONSTRAINT PK_GW_WO_EXG_LIST PRIMARY KEY (WO_EXG_ID),
                                CONSTRAINT FK_GW_WO_EXG_HEAD FOREIGN KEY (HEAD_ID) REFERENCES GW_WO_HEAD(ID)
);
COMMENT ON TABLE GW_WO_EXG_LIST IS 'WO103工单生产入库明细';

CREATE SEQUENCE GW_WO_EXG_LIST_SEQ START WITH 1 INCREMENT BY 1 NOCACHE;
CREATE INDEX IDX_GW_WO_EXG_HEAD ON GW_WO_EXG_LIST(HEAD_ID);

-- 4.3 工单领料退料明细 (WoImgList)
CREATE TABLE GW_WO_IMG_LIST (
                                WO_IMG_ID    NUMBER(19)      NOT NULL,
                                HEAD_ID             NUMBER(19)      NOT NULL,
                                SEQ_NO              VARCHAR2(18),
                                GDS_MTNO            VARCHAR2(32)    NOT NULL,
                                ITEM_TYPE           VARCHAR2(32),
                                PROCESS_NO          VARCHAR2(64),
                                DCL_QTY             NUMBER(19,5)    NOT NULL,       -- 负数领料,正数退料
                                DCL_UNITCD          VARCHAR2(3),
                                TAKE_TIME           VARCHAR2(19),                    -- 领料日期
                                DCL_TYPECD          VARCHAR2(2),
                                DISPOSAL            VARCHAR2(3),
                                GDECD               VARCHAR2(10),
                                GDS_NM              VARCHAR2(512),
                                GDS_SPCF_MODEL_DESC VARCHAR2(512),
                                DCL_UPRC_AMT        VARCHAR2(25),
                                DCL_CURRCD          VARCHAR2(3),
                                LAWF_UNITCD         VARCHAR2(3),
                                LAWF_QTY            VARCHAR2(19),
                                SECD_LAWF_UNITCD    VARCHAR2(3),
                                SECD_LAWF_QTY       VARCHAR2(19),
                                RMK                 VARCHAR2(2000),
                                ERP_ITEM_CODE       VARCHAR2(64),
                                CONSTRAINT PK_GW_WO_IMG_LIST PRIMARY KEY (WO_IMG_ID),
                                CONSTRAINT FK_GW_WO_IMG_HEAD FOREIGN KEY (HEAD_ID) REFERENCES GW_WO_HEAD(ID)
);
COMMENT ON TABLE GW_WO_IMG_LIST IS 'WO103工单领料退料明细';

CREATE SEQUENCE GW_WO_IMG_LIST_SEQ START WITH 1 INCREMENT BY 1 NOCACHE;
CREATE INDEX IDX_GW_WO_IMG_HEAD ON GW_WO_IMG_LIST(HEAD_ID);

-- ============================================================
-- 五、WO105 实盘库存
-- ============================================================

-- 5.1 实盘库存表头
CREATE TABLE GW_WH_HEAD (
                            WH_HEAD_ID    NUMBER(19)      NOT NULL,
                            MESSAGE_ID          VARCHAR2(64)    NOT NULL,
                            SEQ_NO              VARCHAR2(18),
                            ETPS_PRE_ENT_NO     VARCHAR2(64)    NOT NULL,
                            MASTER_CUSCD        VARCHAR2(4),
                            DCL_ETPS_TYPECD     VARCHAR2(10),
                            DCL_ETPS_SCCD       VARCHAR2(18),
                            DCL_ETPS_NM         VARCHAR2(512),
                            DCL_TYPECD          VARCHAR2(2),
                            EMS_NO              VARCHAR2(64)    NOT NULL,
                            WH_TYPECD           VARCHAR2(2)     NOT NULL,       -- 1:期初 2:期中 3:实盘
                            WH_TIME             VARCHAR2(19)    NOT NULL,       -- 库存日期
                            DCL_QTY             NUMBER(10)      NOT NULL,       -- 明细条数
                            RMK                 VARCHAR2(2000),
                            SEND_STATUS         VARCHAR2(2) DEFAULT '0',
                            RESP_CODE           VARCHAR2(2),
                            RESP_TEXT           VARCHAR2(1000),
                            CREATE_TIME         TIMESTAMP DEFAULT SYSTIMESTAMP,
                            SEND_TIME           TIMESTAMP,
                            CONSTRAINT PK_GW_WH_HEAD PRIMARY KEY (WH_HEAD_ID),
                            CONSTRAINT UK_GW_WH_HEAD_MSGID UNIQUE (MESSAGE_ID)
);
COMMENT ON TABLE GW_WH_HEAD IS 'WO105实盘库存表头';

CREATE SEQUENCE GW_WH_HEAD_SEQ START WITH 1 INCREMENT BY 1 NOCACHE;
CREATE INDEX IDX_GW_WH_HEAD_EMS ON GW_WH_HEAD(EMS_NO);

-- 5.2 实盘库存明细表
CREATE TABLE GW_WH_LIST (
                            WH_LIST_ID    NUMBER(19)      NOT NULL,
                            HEAD_ID             NUMBER(19)      NOT NULL,
                            SEQ_NO              VARCHAR2(18),
                            EMS_NO              VARCHAR2(64)    NOT NULL,
                            WH_NO               VARCHAR2(64)    NOT NULL,       -- 仓库编号
                            WH_LOC              VARCHAR2(64),                    -- 库位
                            GDS_SEQ_NO          VARCHAR2(19),
                            GDS_MTNO            VARCHAR2(32)    NOT NULL,
                            GDS_TYPE            VARCHAR2(32)    NOT NULL,
                            DCL_UNITCD          VARCHAR2(3),
                            DCL_QTY             NUMBER(19,5)    NOT NULL,
                            GDECD               VARCHAR2(10),
                            GDS_NM              VARCHAR2(512),
                            GDS_SPCF_MODEL_DESC VARCHAR2(512),
                            DCL_UPRC_AMT        VARCHAR2(25),
                            DCL_CURRCD          VARCHAR2(3),
                            LAWF_UNITCD         VARCHAR2(3),
                            LAWF_QTY            VARCHAR2(19),
                            SECD_LAWF_UNITCD    VARCHAR2(3),
                            SECD_LAWF_QTY       VARCHAR2(19),
                            RMK                 VARCHAR2(2000),
                            ERP_WH_CODE         VARCHAR2(64),                    -- ERP仓库编码
                            ERP_ITEM_CODE       VARCHAR2(64),
                            CONSTRAINT PK_GW_WH_LIST PRIMARY KEY (WH_LIST_ID),
                            CONSTRAINT FK_GW_WH_LIST_HEAD FOREIGN KEY (HEAD_ID) REFERENCES GW_WH_HEAD(ID)
);
COMMENT ON TABLE GW_WH_LIST IS 'WO105实盘库存明细';

CREATE SEQUENCE GW_WH_LIST_SEQ START WITH 1 INCREMENT BY 1 NOCACHE;
CREATE INDEX IDX_GW_WH_LIST_HEAD ON GW_WH_LIST(HEAD_ID);
CREATE INDEX IDX_GW_WH_LIST_MTNO ON GW_WH_LIST(GDS_MTNO);

-- ============================================================
-- 六、WO106 核销结案
-- ============================================================

-- 6.1 核销结案表头
CREATE TABLE GW_DCR_HEAD (
                             DCR_HEAD_ID    NUMBER(19)      NOT NULL,
                             MESSAGE_ID          VARCHAR2(64)    NOT NULL,
                             SEQ_NO              VARCHAR2(18),
                             ETPS_PRE_ENT_NO     VARCHAR2(64)    NOT NULL,
                             MASTER_CUSCD        VARCHAR2(4),
                             DCL_ETPS_TYPECD     VARCHAR2(10),
                             DCL_ETPS_NM         VARCHAR2(512),
                             DCL_ETPS_SCCD       VARCHAR2(18),
                             DCL_TYPECD          VARCHAR2(2),
                             EMS_NO              VARCHAR2(64)    NOT NULL,
                             CHGOFF_TMS_CNT      VARCHAR2(19),                    -- 核销次数
                             CHGOFF_BEGIN_TIME   VARCHAR2(19)    NOT NULL,       -- 报核开始
                             CHGOFF_DUE_TIME     VARCHAR2(19)    NOT NULL,       -- 报核截止
                             CHGOFF_DCL_TIME     VARCHAR2(19)    NOT NULL,       -- 核销申请时间
                             RMK                 VARCHAR2(2000),
                             SEND_STATUS         VARCHAR2(2) DEFAULT '0',
                             RESP_CODE           VARCHAR2(2),
                             RESP_TEXT           VARCHAR2(1000),
                             CREATE_TIME         TIMESTAMP DEFAULT SYSTIMESTAMP,
                             SEND_TIME           TIMESTAMP,
                             CONSTRAINT PK_GW_DCR_HEAD PRIMARY KEY (DCR_HEAD_ID),
                             CONSTRAINT UK_GW_DCR_HEAD_MSGID UNIQUE (MESSAGE_ID)
);
COMMENT ON TABLE GW_DCR_HEAD IS 'WO106核销结案表头';

CREATE SEQUENCE GW_DCR_HEAD_SEQ START WITH 1 INCREMENT BY 1 NOCACHE;
CREATE INDEX IDX_GW_DCR_HEAD_EMS ON GW_DCR_HEAD(EMS_NO);

-- 6.2 核销结案清单表体
CREATE TABLE GW_DCR_INVT_LIST (
                                  DCR_INVT_ID    NUMBER(19)      NOT NULL,
                                  HEAD_ID         NUMBER(19)      NOT NULL,
                                  SEQ_NO          VARCHAR2(18),
                                  G_NO            VARCHAR2(19),                    -- 序号
                                  BOND_INVT_NO    VARCHAR2(64)    NOT NULL,
                                  IE_FLAG         VARCHAR2(2)     NOT NULL,
                                  NOTE            VARCHAR2(255),
                                  CONSTRAINT PK_GW_DCR_INVT PRIMARY KEY (DCR_INVT_ID),
                                  CONSTRAINT FK_GW_DCR_INVT_HEAD FOREIGN KEY (HEAD_ID) REFERENCES GW_DCR_HEAD(ID)
);
COMMENT ON TABLE GW_DCR_INVT_LIST IS 'WO106核销结案清单';

CREATE SEQUENCE GW_DCR_INVT_LIST_SEQ START WITH 1 INCREMENT BY 1 NOCACHE;
CREATE INDEX IDX_GW_DCR_INVT_HEAD ON GW_DCR_INVT_LIST(HEAD_ID);

-- 6.3 核销结案工单表体
CREATE TABLE GW_DCR_WO_LIST (
                                DCR_WO_ID    NUMBER(19)      NOT NULL,
                                HEAD_ID         NUMBER(19)      NOT NULL,
                                SEQ_NO          VARCHAR2(18),
                                G_NO            VARCHAR2(19),
                                WO_NO           VARCHAR2(64)    NOT NULL,
                                NOTE            VARCHAR2(255),
                                CONSTRAINT PK_GW_DCR_WO PRIMARY KEY (DCR_WO_ID),
                                CONSTRAINT FK_GW_DCR_WO_HEAD FOREIGN KEY (HEAD_ID) REFERENCES GW_DCR_HEAD(ID)
);
COMMENT ON TABLE GW_DCR_WO_LIST IS 'WO106核销结案工单';

CREATE SEQUENCE GW_DCR_WO_LIST_SEQ START WITH 1 INCREMENT BY 1 NOCACHE;
CREATE INDEX IDX_GW_DCR_WO_HEAD ON GW_DCR_WO_LIST(HEAD_ID);

-- 6.4 核销结案实盘库存表体
CREATE TABLE GW_DCR_STOCK_LIST (
                                   DCR_STOCK_ID    NUMBER(19)      NOT NULL,
                                   HEAD_ID             NUMBER(19)      NOT NULL,
                                   SEQ_NO              VARCHAR2(18),
                                   EMS_NO              VARCHAR2(64)    NOT NULL,
                                   WH_NO               VARCHAR2(64),
                                   WH_LOC              VARCHAR2(64),
                                   GDS_SEQ_NO          VARCHAR2(19),
                                   GDS_MTNO            VARCHAR2(32)    NOT NULL,
                                   GDS_TYPE            VARCHAR2(32)    NOT NULL,
                                   GDECD               VARCHAR2(10),
                                   GDS_NM              VARCHAR2(512),
                                   GDS_SPCF_MODEL_DESC VARCHAR2(512),
                                   DCL_UNITCD          VARCHAR2(3),
                                   DCL_QTY             NUMBER(19,5)    NOT NULL,
                                   DCL_UPRC_AMT        VARCHAR2(25),
                                   DCL_CURRCD          VARCHAR2(3),
                                   LAWF_UNITCD         VARCHAR2(3),
                                   LAWF_QTY            VARCHAR2(19),
                                   SECD_LAWF_UNITCD    VARCHAR2(3),
                                   SECD_LAWF_QTY       VARCHAR2(19),
                                   RMK                 VARCHAR2(2000),
                                   CONSTRAINT PK_GW_DCR_STOCK PRIMARY KEY (DCR_STOCK_ID),
                                   CONSTRAINT FK_GW_DCR_STOCK_HEAD FOREIGN KEY (HEAD_ID) REFERENCES GW_DCR_HEAD(ID)
);
COMMENT ON TABLE GW_DCR_STOCK_LIST IS 'WO106核销结案实盘库存';

CREATE SEQUENCE GW_DCR_STOCK_LIST_SEQ START WITH 1 INCREMENT BY 1 NOCACHE;
CREATE INDEX IDX_GW_DCR_STOCK_HEAD ON GW_DCR_STOCK_LIST(HEAD_ID);

-- ============================================================
-- 七、WO200 通用回执
-- ============================================================

CREATE TABLE GW_RESPONSE (
                             RESP_ID    NUMBER(19)      NOT NULL,
                             MESSAGE_ID      VARCHAR2(64)    NOT NULL,           -- 原申报报文MessageId
                             RESP_CODE       VARCHAR2(2)     NOT NULL,           -- 0:成功 1:失败
                             RESP_TEXT       VARCHAR2(2000),                     -- 响应信息(BASE64解密后)
                             RESP_TEXT_RAW   VARCHAR2(2000),                     -- 原始BASE64
                             PRE_ENT_NO      VARCHAR2(64),                       -- 企业内部编号
                             SEQ_NO          VARCHAR2(18),                       -- 统一编号
                             MSG_TYPE        VARCHAR2(16),                       -- 原报文类型 WO101/WO102...
                             RECEIVE_TIME    TIMESTAMP DEFAULT SYSTIMESTAMP,
                             CONSTRAINT PK_GW_RESPONSE PRIMARY KEY (RESP_ID)
);
COMMENT ON TABLE GW_RESPONSE IS 'WO200海关通用回执';

CREATE SEQUENCE GW_RESPONSE_SEQ START WITH 1 INCREMENT BY 1 NOCACHE;
CREATE INDEX IDX_GW_RESPONSE_MSGID ON GW_RESPONSE(MESSAGE_ID);

-- ============================================================
-- 八、MQ发送日志表
-- ============================================================

CREATE TABLE GW_MQ_LOG (
                           LOG_ID    NUMBER(19)      NOT NULL,
                           MSG_TYPE        VARCHAR2(16)    NOT NULL,           -- WO101/WO102/WO103/WO105/WO106
                           MESSAGE_ID      VARCHAR2(64)    NOT NULL,
                           FILE_NAME       VARCHAR2(255),
                           DIRECTION       VARCHAR2(4)     NOT NULL,           -- SEND/RECV
                           STATUS          VARCHAR2(2) DEFAULT '0',            -- 0:待处理 1:成功 2:失败
                           XML_CONTENT     CLOB,                               -- 报文XML内容
                           ZIP_CONTENT     BLOB,                               -- 压缩后内容(发送用)
                           ERROR_MSG       VARCHAR2(2000),
                           RETRY_COUNT     NUMBER(5) DEFAULT 0,
                           CREATE_TIME     TIMESTAMP DEFAULT SYSTIMESTAMP,
                           UPDATE_TIME     TIMESTAMP DEFAULT SYSTIMESTAMP,
                           CONSTRAINT PK_GW_MQ_LOG PRIMARY KEY (LOG_ID)
);
COMMENT ON TABLE GW_MQ_LOG IS 'MQ报文收发日志';

CREATE SEQUENCE GW_MQ_LOG_SEQ START WITH 1 INCREMENT BY 1 NOCACHE;
CREATE INDEX IDX_GW_MQ_LOG_MSGID ON GW_MQ_LOG(MESSAGE_ID);
CREATE INDEX IDX_GW_MQ_LOG_STATUS ON GW_MQ_LOG(STATUS, DIRECTION);

-- ============================================================
-- 九、ERP业务单据关联表（可选，用于追溯）
-- ============================================================

CREATE TABLE GW_ERP_RELATION (
                                 RELATION_ID    NUMBER(19)      NOT NULL,
                                 MSG_TYPE        VARCHAR2(16)    NOT NULL,
                                 MESSAGE_ID      VARCHAR2(64)    NOT NULL,
                                 ERP_BILL_TYPE   VARCHAR2(32),                        -- ERP单据类型
                                 ERP_BILL_NO     VARCHAR2(64),                        -- ERP单据号
                                 ERP_BILL_LINE   NUMBER(10),                          -- ERP单据行号
                                 RELATION_DESC   VARCHAR2(500),
                                 CREATE_TIME     TIMESTAMP DEFAULT SYSTIMESTAMP,
                                 CONSTRAINT PK_GW_ERP_RELATION PRIMARY KEY (RELATION_ID)
);
COMMENT ON TABLE GW_ERP_RELATION IS '关务报文与ERP单据关联追溯表';

CREATE SEQUENCE GW_ERP_RELATION_SEQ START WITH 1 INCREMENT BY 1 NOCACHE;
CREATE INDEX IDX_GW_ERP_REL_BILL ON GW_ERP_RELATION(ERP_BILL_TYPE, ERP_BILL_NO);