-- Create table
create table BM_YD0000
(
  YPNM00 NUMBER(10) not null,
  YKBMBH NUMBER(5) not null,
  YPBM00 VARCHAR2(15) not null,
  LBBH00 CHAR(1) not null,
  YPGBM0 VARCHAR2(20),
  YZYYP0 NUMBER(10),
  YPMCLB CHAR(1) default '0' not null,
  YPMC00 VARCHAR2(160) not null,
  HXMC00 VARCHAR2(160),
  YPTYM0 VARCHAR2(50),
  PYSMTY VARCHAR2(30),
  TYMBH0 VARCHAR2(10) not null,
  PYSM00 VARCHAR2(30),
  WBSM00 VARCHAR2(30),
  YPGG00 VARCHAR2(50),
  EYPTYM VARCHAR2(50),
  EYPMC0 VARCHAR2(50),
  EKCDW0 VARCHAR2(10),
  EYPGG0 VARCHAR2(20),
  LSYPMC VARCHAR2(160),
  YLBH00 VARCHAR2(15) not null,
  JXBH00 VARCHAR2(5) not null,
  CYLLSH NUMBER(5),
  TJLXBH VARCHAR2(2),
  QYRQ00 CHAR(8),
  TZRQ00 CHAR(8),
  DRRQ00 CHAR(8),
  YPDRRQ CHAR(8),
  YPSYSM VARCHAR2(2000),
  TPWJMC VARCHAR2(100),
  MRYYZF VARCHAR2(30),
  YPJCL0 NUMBER(14,4) default 0.15 not null,
  JLDW00 VARCHAR2(12) not null,
  KCDW00 VARCHAR2(12) not null,
  ZYFYDW VARCHAR2(12) not null,
  MZFYDW VARCHAR2(12) not null,
  K2J000 NUMBER(13,3),
  Z2J000 NUMBER(13,3),
  M2J000 NUMBER(13,3),
  ZJJLDW VARCHAR2(12) not null,
  K2ZJJL NUMBER(13,3) not null,
  ZJJL2J NUMBER(13,3) not null,
  YYZHL0 NUMBER(12,3),
  YYDW00 VARCHAR2(12),
  MZKFDW VARCHAR2(12),
  F2J000 NUMBER(13,3) default 1 not null,
  ZJMZCF CHAR(1),
  ZJZYCF CHAR(1),
  FZGG01 NUMBER(13,3),
  FZGG02 NUMBER(13,3),
  BZGG00 VARCHAR2(30),
  PFJ000 NUMBER(12,4),
  LSJ000 NUMBER(12,4) not null,
  ZBJ000 NUMBER(12,4),
  GJJ000 NUMBER(12,4),
  GJPJDJ NUMBER(12,4),
  GJZJE0 NUMBER(30,2),
  GJZSL0 NUMBER(31,3),
  KCSL00 NUMBER(31,3) default 0 not null,
  KCJE00 NUMBER(12,2) default 0 not null,
  KCGJJE NUMBER(12,2) default 0 not null,
  KCPFJE NUMBER(12,2),
  ZYJCSL NUMBER(31,3),
  ZYJCJE NUMBER(30,2),
  ZYGJJE NUMBER(30,2),
  SFZBYP CHAR(1) default 'N' not null,
  KCSX00 NUMBER(13,3) default 1000000 not null,
  KCXX00 NUMBER(13,3) default 0 not null,
  PDXH00 VARCHAR2(7) default '1' not null,
  CFWZ00 VARCHAR2(2),
  QZFS00 CHAR(1) default '0' not null,
  MZQZFS CHAR(1) default '0' not null,
  CFJB00 NUMBER(3) default 0 not null,
  GZDJ00 CHAR(1) default '0' not null,
  MZDJ00 CHAR(1) default '0' not null,
  DPDJ00 CHAR(1) default '0' not null,
  SJDJ00 CHAR(1) default '0' not null,
  KSSJB0 NUMBER(2) default 0 not null,
  DXSYBZ CHAR(1) default '0' not null,
  YPCZFL CHAR(1) not null,
  SFZLYP CHAR(1) default 'N' not null,
  SFKYYY CHAR(1) default 'N' not null,
  SFPSYY CHAR(1) default 'N' not null,
  PSLX00 VARCHAR2(2),
  ZJBZ00 CHAR(1) default '0' not null,
  SFXY00 CHAR(1) default '0',
  BYFL00 CHAR(1),
  SFQTXS CHAR(1) default 'Y',
  SFJCYY CHAR(1) default 'N',
  SFOTC0 NUMBER(2) default 0 not null,
  YPXZ00 NUMBER(1) default 1 not null,
  CYJL00 NUMBER(12,4),
  YFID00 VARCHAR2(3),
  PCID00 NUMBER(3),
  ZQS000 NUMBER(3) default 1,
  MAXJL0 NUMBER(13,3),
  MAXTS0 NUMBER(5),
  ZDXL00 NUMBER(12,4),
  CYL000 NUMBER(12,4),
  SCCJBH VARCHAR2(10),
  CDID00 NUMBER(10),
  GHDWNM NUMBER(5),
  ZBXH00 VARCHAR2(20),
  ZBQMC0 VARCHAR2(20),
  PZWH00 VARCHAR2(50),
  BZ0000 VARCHAR2(200),
  CPID00 VARCHAR2(10),
  GHSID0 VARCHAR2(10),
  SFZFDJ CHAR(1),
  ZFZGDJ NUMBER(12,4),
  MZFPID NUMBER(5) not null,
  ZYFPID NUMBER(5) not null,
  BAXMID NUMBER(5) not null,
  HSXMID NUMBER(5) not null,
  SFYBML CHAR(1),
  YBLB00 VARCHAR2(1),
  ZFBL00 NUMBER(3,2),
  YBLB01 VARCHAR2(1),
  ZFBL01 NUMBER(3,2),
  YPJEXZ CHAR(1),
  JKBH00 VARCHAR2(12),
  NSBL00 NUMBER(4,2) default 1 not null,
  NDJCTS VARCHAR2(100),
  DDD000 NUMBER(12,4),
  DDD001 NUMBER(12,4),
  YPMLBZ VARCHAR2(40),
  YPPZBH VARCHAR2(10),
  TZBH00 VARCHAR2(10),
  CJR000 NUMBER(5) not null,
  CJRQSJ CHAR(16) default to_char(sysdate,'yyyymmddhh24:mi:ss') not null,
  XGR000 NUMBER(5),
  XGRQSJ VARCHAR2(16),
  WDWBH0 VARCHAR2(15),
  CCFS00 NUMBER(10),
  SFBA00 VARCHAR2(1),
  FSDJ00 CHAR(1) default '0' not null,
  JBYYML CHAR(1),
  KJXMID NUMBER(5) default 0 not null,
  YPYZSX NUMBER(10),
  ZSDYLB NUMBER(10),
  YBYYXZ VARCHAR2(100),
  SFTDYP CHAR(1) default '0' not null,
  MAXZL0 NUMBER(13,3),
  TSYBLB NUMBER(10),
  PZTBH0 NUMBER(10),
  FYLB00 CHAR(1) default '0',
  SFDZGG CHAR(1),
  TXM000 VARCHAR2(200),
  PXXH00 NUMBER(8),
  MCCD00 NUMBER(3),
  KJFJBZ VARCHAR2(1),
  WZDJ00 CHAR(1) default '0' not null,
  YDYYTS VARCHAR2(100),
  YDYPYF CHAR(1)
)
tablespace TS_YY
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 3
    next 1
    minextents 1
    maxextents unlimited
  );
-- Add comments to the table 
comment on table BM_YD0000
  is '药典表';
-- Add comments to the columns 
comment on column BM_YD0000.YPNM00
  is '药品内码，机器内码  SQ_BM_YD0000_YPNM00';
comment on column BM_YD0000.YKBMBH
  is '药库部门编号，区分药品所属的药库';
comment on column BM_YD0000.YPBM00
  is '药品编码，院内码';
comment on column BM_YD0000.LBBH00
  is '药品类别编号';
comment on column BM_YD0000.YPGBM0
  is '药品国标码';
comment on column BM_YD0000.YZYYP0
  is '医嘱用药编码默认为YPNM00,主要处理草药不同规格药品医嘱输入如果规格不同的草药医嘱看作相同的药品，通过本字段维护';
comment on column BM_YD0000.YPMCLB
  is '药品名称类别 ';
comment on column BM_YD0000.YPMC00
  is '药品名称';
comment on column BM_YD0000.HXMC00
  is '化学名称';
comment on column BM_YD0000.YPTYM0
  is '药品通用码';
comment on column BM_YD0000.TYMBH0
  is '通用名编号 F.K=BM_YPTYM0.TYMBH0';
comment on column BM_YD0000.PYSM00
  is '拼音首码';
comment on column BM_YD0000.WBSM00
  is '五笔首码';
comment on column BM_YD0000.YPGG00
  is '规格';
comment on column BM_YD0000.EYPTYM
  is '英文药品通用码';
comment on column BM_YD0000.EYPMC0
  is '英文药品通用名称';
comment on column BM_YD0000.EKCDW0
  is '英文库存单位';
comment on column BM_YD0000.EYPGG0
  is '英文药品规格';
comment on column BM_YD0000.YLBH00
  is '药理编号';
comment on column BM_YD0000.JXBH00
  is '剂型编号';
comment on column BM_YD0000.CYLLSH
  is '次级药理流水号';
comment on column BM_YD0000.QYRQ00
  is '启用日期';
comment on column BM_YD0000.TZRQ00
  is '停止日期';
comment on column BM_YD0000.DRRQ00
  is '导入日期';
comment on column BM_YD0000.YPSYSM
  is '药品使用说明';
comment on column BM_YD0000.MRYYZF
  is '用药嘱咐';
comment on column BM_YD0000.YPJCL0
  is '药品加成率x (零售价=购进价*(1+x))';
comment on column BM_YD0000.JLDW00
  is '剂量单位(J)';
comment on column BM_YD0000.KCDW00
  is '库存单位(K)';
comment on column BM_YD0000.ZYFYDW
  is '住院发药单位(Z)（住院药房维护）';
comment on column BM_YD0000.MZFYDW
  is '门诊发药单位(M)（门诊药房维护）';
comment on column BM_YD0000.K2J000
  is 'K/J转换率  --单位相同，此值须为1';
comment on column BM_YD0000.Z2J000
  is 'Z/J转换率  --单位相同，此值须为1';
comment on column BM_YD0000.M2J000
  is 'M/J转换率  --单位相同，此值须为1';
comment on column BM_YD0000.ZJJLDW
  is '中间计量单位(J)';
comment on column BM_YD0000.K2ZJJL
  is '库存/中间计量 转换率';
comment on column BM_YD0000.ZJJL2J
  is '中间计量/剂量 转换率';
comment on column BM_YD0000.YYZHL0
  is '用药转化率';
comment on column BM_YD0000.YYDW00
  is '用药单位';
comment on column BM_YD0000.MZKFDW
  is '门诊开方单位';
comment on column BM_YD0000.ZJMZCF
  is '中间门诊拆分 0:不拆分 1:拆分';
comment on column BM_YD0000.ZJZYCF
  is '中间住院拆分 0:不拆分 1:拆分';
comment on column BM_YD0000.FZGG01
  is '分装规格1:(计量单位)';
comment on column BM_YD0000.FZGG02
  is '分装规格2:(计量单位)';
comment on column BM_YD0000.PFJ000
  is '批发价(库存单位)';
comment on column BM_YD0000.LSJ000
  is '零售价(库存单位)';
comment on column BM_YD0000.ZBJ000
  is '中标价(库存单位)';
comment on column BM_YD0000.GJJ000
  is '购进价(库存单位,最近一批)';
comment on column BM_YD0000.GJPJDJ
  is '购进平均价(购进总金额/购进总数量)';
comment on column BM_YD0000.GJZJE0
  is '购进总金额(每次购进自动增长)';
comment on column BM_YD0000.GJZSL0
  is '购进总数量(每次购进自动增长)(剂量单位)';
comment on column BM_YD0000.KCSL00
  is '库存数量(剂量单位)(药库)';
comment on column BM_YD0000.KCJE00
  is '库存金额';
comment on column BM_YD0000.KCPFJE
  is '库存批发金额';
comment on column BM_YD0000.ZYJCSL
  is '结存数量';
comment on column BM_YD0000.ZYJCJE
  is '结存金额';
comment on column BM_YD0000.ZYGJJE
  is '购进金额';
comment on column BM_YD0000.SFZBYP
  is '是否招标药品';
comment on column BM_YD0000.KCSX00
  is '库存上限(库存单位)';
comment on column BM_YD0000.KCXX00
  is '库存下限(库存单位)';
comment on column BM_YD0000.PDXH00
  is '盘点序号(为0的不参与盘点)';
comment on column BM_YD0000.CFWZ00
  is '药品存放位置编码';
comment on column BM_YD0000.QZFS00
  is '取整方式';
comment on column BM_YD0000.MZQZFS
  is '门诊取整方式';
comment on column BM_YD0000.CFJB00
  is '处方级别';
comment on column BM_YD0000.GZDJ00
  is '贵重等级';
comment on column BM_YD0000.MZDJ00
  is '麻醉等级';
comment on column BM_YD0000.DPDJ00
  is '毒品等级';
comment on column BM_YD0000.SJDJ00
  is '神经等级';
comment on column BM_YD0000.KSSJB0
  is '抗生素级别 0非抗生素，1类抗生素 2类抗生素 3类抗生素';
comment on column BM_YD0000.DXSYBZ
  is '大型输液标志 ''0''普通 ''1''大型输液';
comment on column BM_YD0000.YPCZFL
  is '药品材质分类 0:普通中草药 1:颗粒药 2:全成分 3:包药机药品';
comment on column BM_YD0000.SFZLYP
  is '是否专利药品 ''Y''是,''N''否';
comment on column BM_YD0000.SFKYYY
  is '是否科研用药 ''Y'':科研用药,单价数量均为0';
comment on column BM_YD0000.SFPSYY
  is '是否皮试用药''N''-否，''Y''-是';
comment on column BM_YD0000.PSLX00
  is '皮试类型   BM_PS000.PSID00 ';
comment on column BM_YD0000.ZJBZ00
  is '本院制剂标志(BM_TYZD00.ZDMC00=药典维护-制剂标志).BH0000 0 自购药品 1 自制药品';
comment on column BM_YD0000.SFXY00
  is '是否新药';
comment on column BM_YD0000.BYFL00
  is '摆药分类 0,针剂;1,口服 具体值见 (BM_TYZD00.ZDMC00=药典维护-摆药分类).BH0000';
comment on column BM_YD0000.SFJCYY
  is '是否基层用药';
comment on column BM_YD0000.SFOTC0
  is '是否OTC用药 0 处方药 1 OTC用药';
comment on column BM_YD0000.YPXZ00
  is '药品性质 1基本用药 2临时用处方药 3临时用非处方药 4非基本药物';
comment on column BM_YD0000.CYJL00
  is '常用剂量';
comment on column BM_YD0000.YFID00
  is '用法ID';
comment on column BM_YD0000.PCID00
  is '频次ID';
comment on column BM_YD0000.ZQS000
  is '周期数';
comment on column BM_YD0000.MAXJL0
  is '处方单次的最大剂量';
comment on column BM_YD0000.MAXTS0
  is '处方的最大天数';
comment on column BM_YD0000.ZDXL00
  is '处方单次的最大剂量(等同MAXJL0,因为电子病历用这个字段，所以这个值维护两次)';
comment on column BM_YD0000.CYL000
  is '常用量';
comment on column BM_YD0000.SCCJBH
  is '生产厂家编号';
comment on column BM_YD0000.CDID00
  is '产地ID';
comment on column BM_YD0000.GHDWNM
  is '供货单位内码';
comment on column BM_YD0000.ZBXH00
  is '中标序号';
comment on column BM_YD0000.ZBQMC0
  is '中标期';
comment on column BM_YD0000.PZWH00
  is '批准文号';
comment on column BM_YD0000.BZ0000
  is '备注';
comment on column BM_YD0000.SFZFDJ
  is '是否政府定价';
comment on column BM_YD0000.ZFZGDJ
  is '政府最高定价';
comment on column BM_YD0000.MZFPID
  is '门诊发票ID F.K=BM_FPXM00.FPXMID';
comment on column BM_YD0000.ZYFPID
  is '住院发票ID F.K=BM_FPXM00.FPXMID';
comment on column BM_YD0000.BAXMID
  is '病案项目ID F.K=BM_BAXM00.BAXMID';
comment on column BM_YD0000.HSXMID
  is '核算项目ID F.K=BM_YYHSXM.HSXMID';
comment on column BM_YD0000.SFYBML
  is '是否医保目录';
comment on column BM_YD0000.YBLB00
  is '医保类别:4甲类药品 5乙类药品 6民族药品 7非医保药品';
comment on column BM_YD0000.ZFBL00
  is '自付比率(医保)';
comment on column BM_YD0000.YBLB01
  is '农保类别:4甲类药品 5乙类药品 6民族药品 7非医保药品';
comment on column BM_YD0000.ZFBL01
  is '自付比率(农保)';
comment on column BM_YD0000.YPJEXZ
  is '药品金额性质 0普通1贵重2进口';
comment on column BM_YD0000.DDD000
  is '累积每日约定剂量(defined daily doses 简称DDD)';
comment on column BM_YD0000.TZBH00
  is '同组编号:相同才可以开在一张处方中，主要指全成分颗粒，中草药颗粒';
comment on column BM_YD0000.CJR000
  is '创建人';
comment on column BM_YD0000.CJRQSJ
  is '创建日期时间';
comment on column BM_YD0000.XGR000
  is '最后修改人';
comment on column BM_YD0000.XGRQSJ
  is '最后修改日期时间';
comment on column BM_YD0000.CCFS00
  is '储存方式  F.K=BM_TYZD00.NBBH00(zdmc00=''储存方法'')';
comment on column BM_YD0000.JBYYML
  is '国家基本用药目录 Y 是 N 否';
comment on column BM_YD0000.KJXMID
  is '会计项目ID F.K=BM_YYKJXM.KJXMID';
comment on column BM_YD0000.YPYZSX
  is '药品医嘱属性(BM_TYZD00.ZDMC00=药典维护-医嘱属性).NBBH00';
comment on column BM_YD0000.ZSDYLB
  is '注射剂打印类别(BM_TYZD00.ZDMC00=药典维护-注射剂打印类别).NBBH00';
comment on column BM_YD0000.YBYYXZ
  is '医保用药限制';
comment on column BM_YD0000.SFTDYP
  is '是否特定药品 0否 1特定药品 2针剂 3普通片剂 4缓释片(见VW_YK_YDSFTDYP.BH0000)';
comment on column BM_YD0000.MAXZL0
  is '处方的最大总量';
comment on column BM_YD0000.TSYBLB
  is '特殊医保类别(BM_TYZD00.ZDMC00=药典维护-特殊医保类别).NBBH00';
comment on column BM_YD0000.PZTBH0
  is '配制台编号(BM_TYZD00.ZDMC00=药典维护-配制台编号).NBBH00';
comment on column BM_YD0000.FYLB00
  is '发药类别:1药房是不直接发药给病区(寄存,定期手工抵消借药)';
comment on column BM_YD0000.TXM000
  is '条形码';
comment on column BM_YD0000.PXXH00
  is '排序序号';
comment on column BM_YD0000.MCCD00
  is '名称长度';
comment on column BM_YD0000.WZDJ00
  is '危重等级';
comment on column BM_YD0000.YDYYTS
  is '运动员用药提示';
comment on column BM_YD0000.YDYPYF
  is '药品用法 0、口服 1、外用 2、针剂';
-- Create/Recreate primary, unique and foreign key constraints 
alter table BM_YD0000
  add constraint PK_BM_YD0000_YPNM00 primary key (YPNM00)
  using index 
  tablespace TS_YY
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 192K
    next 1M
    minextents 1
    maxextents unlimited
  );
alter table BM_YD0000
  add constraint UQ_BM_YD0000_YPBM00 unique (YPBM00)
  using index 
  tablespace TS_YY
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 256K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate check constraints 
alter table BM_YD0000
  add constraint CK_BM_YD0000
  check (sfkyyy IN ('Y','N')  AND    cfjb00 >= 0 AND qzfs00 IN ('0','1','2','3','4') AND sfpsyy IN ('Y','N')
 AND  sfzbyp IN ('Y','N') AND kcje00 >= 0 AND kcsl00 >= 0 AND kcxx00 >= 0 AND  kcsx00 >= kcxx00 AND NVL(cyjl00,0) >= 0 AND NVL(gjzsl0,0) >= 0 
 AND NVL(gjzje0,0) >= 0 AND NVL(gjpjdj,0) >= 0 AND NVL(gjj000,0) >= 0 AND NVL(zbj000,0) >= 0 AND ((sfkyyy = 'Y' AND lsj000 >= 0) OR (sfkyyy = 'N' AND lsj000 >0)) 
 AND   NVL(pfj000,0) >= 0 AND  ((kcdw00 = jldw00 AND k2j000 = 1) OR (kcdw00 <> jldw00 AND k2j000 > 0)) 
 AND  ((mzfydw = jldw00 AND m2j000 = 1) OR (mzfydw <> jldw00 AND m2j000 > 0)) AND ((zyfydw = jldw00 AND z2j000 = 1) OR (zyfydw <> jldw00 AND z2j000 > 0)) 
 AND ((zyfydw = zjjldw AND z2j000 = ZJJL2J or zyfydw <> zjjldw))
 AND ((mzfydw = zjjldw AND m2j000 = ZJJL2J or mzfydw <> zjjldw))
 AND ((kcdw00 = zjjldw AND k2j000 = ZJJL2J or kcdw00 <> zjjldw))
 AND ((kcdw00 = mzfydw AND k2j000 = m2j000 or kcdw00 <> mzfydw))
 AND (K2ZJJL*ZJJL2J=K2j000)
 and ( (ZJMZCF='0' and MZQZFS in ('0','1','2') and MZFYDW=KCDW00) or (ZJMZCF='1' and MZFYDW=ZJJLDW) ) and ( (ZJZYCF='0' and QZFS00 in ('0','1','2') and ZYFYDW=KCDW00) or (ZJZYCF='1' and ZYFYDW=ZJJLDW) )
 and LSJ000>=GJJ000);
-- Create/Recreate indexes 
create index INDEX_BM_YD0000_PYSM00 on BM_YD0000 (PYSM00)
  tablespace TS_YY
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 320K
    next 1M
    minextents 1
    maxextents unlimited
  );
create index INDEX_BM_YD0000_WBSM00 on BM_YD0000 (WBSM00)
  tablespace TS_YY
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 320K
    next 1M
    minextents 1
    maxextents unlimited
  );
create index INDEX_BM_YD0000_WDWBH0 on BM_YD0000 (WDWBH0)
  tablespace TS_YY
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
create index INDEX_BM_YD0000_YKBMBH on BM_YD0000 (YKBMBH)
  tablespace TS_YY
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 448K
    next 1M
    minextents 1
    maxextents unlimited
  );
create index INDEX_BM_YD0000_YPMC00 on BM_YD0000 (YPMC00)
  tablespace TS_YY
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 384K
    next 1M
    minextents 1
    maxextents unlimited
  );
create index INDEX_BM_YD0000_YPTYM0 on BM_YD0000 (YPTYM0)
  tablespace TS_YY
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Grant/Revoke object privileges 
grant select on BM_YD0000 to SD_WLPT;