-- ============================================================ -- 保税维修关务对接 - 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);