--%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
--prompt 01、住院预约管理通知单中间表-待发送  ZY_DFSTZD
create table SD_ZY.ZY_DFSTZD
(
  ID0000 number(12) not null,
  ZYSQID varchar2(6) not null,
  FSBZ00 varchar2(1) not null,
  CZBZ00 varchar2(1) not null,
  BRID00 number(10) not null,
  ZXDJBZ varchar2(1) not null,
  SQYS00 number(5),
  SQKS00 number(5),
  SQRQ00 char(8),
  SQSJ00 char(8),
  KSH000 number(5),
  BQH000 number(5),
  ZYYS00 number(5),
  BZ0000 varchar2(200),
  CZRQ00 varchar2(8) default to_char(sysdate,'yyyymmdd') not null,
  CZSJ00 varchar2(8) default to_char(sysdate,'hh24:mi:ss') not null,
  QMSG00 clob,
  RMSG00 clob,
  MSGID0 varchar2(100),
  CID000 varchar2(100)
);
-- Add comments to the columns 
comment on column SD_ZY.ZY_DFSTZD.ID0000 is '消息ID';
comment on column SD_ZY.ZY_DFSTZD.ZYSQID is '住院申请ID';
comment on column SD_ZY.ZY_DFSTZD.FSBZ00 is '发送标志,0未发送,1已发送,2发送失败';
comment on column SD_ZY.ZY_DFSTZD.CZBZ00 is '操作标志,0插入,1修改,3删除';
comment on column SD_ZY.ZY_DFSTZD.BRID00 is '病人ID';
comment on column SD_ZY.ZY_DFSTZD.ZXDJBZ is '中心登记标志:1检查,2回家3住院通知';
comment on column SD_ZY.ZY_DFSTZD.SQYS00 is '申请医生';
comment on column SD_ZY.ZY_DFSTZD.SQKS00 is '申请科室';
comment on column SD_ZY.ZY_DFSTZD.SQRQ00 is '申请日期';
comment on column SD_ZY.ZY_DFSTZD.SQSJ00 is '申请时间';
comment on column SD_ZY.ZY_DFSTZD.KSH000 is '住院科室';
comment on column SD_ZY.ZY_DFSTZD.BQH000 is '住院病区';
comment on column SD_ZY.ZY_DFSTZD.ZYYS00 is '住院医生';
comment on column SD_ZY.ZY_DFSTZD.BZ0000 is '备注';
comment on column SD_ZY.ZY_DFSTZD.CZRQ00 is '操作日期';
comment on column SD_ZY.ZY_DFSTZD.CZSJ00 is '操作时间';
comment on column SD_ZY.ZY_DFSTZD.QMSG00 is '发送消息串';
comment on column SD_ZY.ZY_DFSTZD.RMSG00 is '返回消息串';
comment on column SD_ZY.ZY_DFSTZD.MSGID0 is '返回讯息ID';
comment on column SD_ZY.ZY_DFSTZD.CID000 is '连接ID';
-- Create/Recreate indexes 
create index INDEX_ZY_DFSTZD_CZRQ00 on SD_ZY.ZY_DFSTZD (CZRQ00);
create index INDEX_ZY_DFSTZD_ID0000 on SD_ZY.ZY_DFSTZD (ID0000);
create index INDEX_ZY_DFSTZD_ZYSQID on SD_ZY.ZY_DFSTZD (ZYSQID);
create index INDEX_ZY_DFSTZD_BRID00 on SD_ZY.ZY_DFSTZD (BRID00);
--创建同义词  
create or replace synonym ZY_DFSTZD for  SD_ZY.ZY_DFSTZD ;  

--%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
--prompt 02、住院预约管理通知单中间表-已发送  ZY_YFSTZD
create table SD_ZY.ZY_YFSTZD
(
  ID0000 number(12) not null,
  ZYSQID varchar2(6) not null,
  FSBZ00 varchar2(1) not null,
  CZBZ00 varchar2(1) not null,
  BRID00 number(10) not null,
  ZXDJBZ varchar2(1) not null,
  SQYS00 number(5),
  SQKS00 number(5),
  SQRQ00 char(8),
  SQSJ00 char(8),
  KSH000 number(5),
  BQH000 number(5),
  ZYYS00 number(5),
  BZ0000 varchar2(200),
  CZRQ00 varchar2(8) default to_char(sysdate,'yyyymmdd') not null,
  CZSJ00 varchar2(8) default to_char(sysdate,'hh24:mi:ss') not null,
  QMSG00 clob,
  RMSG00 clob,
  MSGID0 varchar2(100),
  CID000 varchar2(100),
  FSRQ00 varchar2(8),
  FSSJ00 varchar2(8)
);
-- Add comments to the columns 
comment on column SD_ZY.ZY_YFSTZD.ID0000 is '消息ID';
comment on column SD_ZY.ZY_YFSTZD.ZYSQID is '住院申请ID';
comment on column SD_ZY.ZY_YFSTZD.FSBZ00 is '发送标志,0未发送,1已发送,2发送失败';
comment on column SD_ZY.ZY_YFSTZD.CZBZ00 is '操作标志,0插入,1修改,3删除';
comment on column SD_ZY.ZY_YFSTZD.BRID00 is '病人ID';
comment on column SD_ZY.ZY_YFSTZD.ZXDJBZ is '中心登记标志:1检查,2回家3住院通知';
comment on column SD_ZY.ZY_YFSTZD.SQYS00 is '申请医生';
comment on column SD_ZY.ZY_YFSTZD.SQKS00 is '申请科室';
comment on column SD_ZY.ZY_YFSTZD.SQRQ00 is '申请日期';
comment on column SD_ZY.ZY_YFSTZD.SQSJ00 is '申请时间';
comment on column SD_ZY.ZY_YFSTZD.KSH000 is '住院科室';
comment on column SD_ZY.ZY_YFSTZD.BQH000 is '住院病区';
comment on column SD_ZY.ZY_YFSTZD.ZYYS00 is '住院医生';
comment on column SD_ZY.ZY_YFSTZD.BZ0000 is '备注';
comment on column SD_ZY.ZY_YFSTZD.CZRQ00 is '操作日期';
comment on column SD_ZY.ZY_YFSTZD.CZSJ00 is '操作时间';
comment on column SD_ZY.ZY_YFSTZD.QMSG00 is '发送消息串';
comment on column SD_ZY.ZY_YFSTZD.RMSG00 is '返回消息串';
comment on column SD_ZY.ZY_YFSTZD.MSGID0 is '返回讯息ID';
comment on column SD_ZY.ZY_YFSTZD.CID000 is '连接ID';  
comment on column SD_ZY.ZY_YFSTZD.FSRQ00 is '发送日期';
comment on column SD_ZY.ZY_YFSTZD.FSSJ00 is '发送时间';
-- Create/Recreate indexes 
create index INDEX_ZY_YFSTZD_CZRQ00 on SD_ZY.ZY_YFSTZD (CZRQ00);
create index INDEX_ZY_YFSTZD_ID0000 on SD_ZY.ZY_YFSTZD (ID0000);
create index INDEX_ZY_YFSTZD_ZYSQID on SD_ZY.ZY_YFSTZD (ZYSQID);
create index INDEX_ZY_YFSTZD_BRID00 on SD_ZY.ZY_YFSTZD (BRID00);
--创建同义词  
create or replace synonym ZY_YFSTZD for  SD_ZY.ZY_YFSTZD ;