create or replace view vw_xm_zkyykz as
select
  A.YPNM00,
  A.SFQY00,
  B.YPBM00,
  B.YPMLBZ,
  B.YPTYM0,
  B.YPMC00,
  B.YPGG00,
  (select SCCJZW from  BM_YPSCCJ where SCCJBH=B.SCCJBH) SCCJZW,
  B.LBBH00,
  B.YLBH00,
  B.JXBH00,
  (select LBMC00 from BM_YPLB00 where LBBH00=B.LBBH00 ) LBMC00,
  (select YLMC00 from BM_YPYL00 where LBBH00=B.LBBH00 and YLBH00=B.YLBH00 ) YLMC00,
  (select JXMC00 from BM_YPJX00 where LBBH00=B.LBBH00 and JXBH00=B.JXBH00 ) JXMC00,
  A.QYRQ00||' '||A.QYSJ00  QYRQSJ,
  A.MZZYBZ, 
  decode(A.MZZYBZ,'0','门诊','1','住院','2','所有') MZZYSY,
  A.SFXM00,
  nvl(A.ZYYID0,'0') YYID00,
  nvl((select YYJC00 from XT_YYXX00 where YYID00=a.ZYYID0),'全院') YYMC00
from BM_ZKYYKZ A,BM_YD0000 B where A.YPNM00=B.YPNM00 and A.SFXM00='0'
union all
select
  A.YPNM00,--药品内码
  A.SFQY00,--是否启用
  B.ZLXMBH,--药品编码
  ' ' YPMLBZ,--药品目录备注
  b.ZLXMJC YPTYM0,--药品通用名
  B.ZLXMJC YPMC00,--药品名称（商品名）
  ' ' YPGG00,--药品规格
  ' ' SCCJZW,--生产厂家
  to_char(B.LBBH00),--类别编号
  ' ' YLBH00,--药理编号
  ' ' JXBH00,--剂型编号
  ' ' LBMC00,--类别名称
  ' ' YLMC00,--药理名称
  ' ' JXMC00,--剂型名称
  A.QYRQ00||' '||A.QYSJ00  QYRQSJ,--启用日期时间
  A.MZZYBZ, --门诊住院标志 (0:门诊;1:住院;2:公用)
  decode(A.MZZYBZ,'0','门诊','1','住院','2','所有') MZZYSY,
  A.SFXM00,
  nvl(A.ZYYID0,'0') YYID00,
  nvl((select YYJC00 from XT_YYXX00 where YYID00=a.ZYYID0),'全院') YYMC00
from BM_ZKYYKZ A,BM_ZLZD00 B where A.YPNM00=B.ZLXMID and A.SFXM00='1';