CREATE OR REPLACE VIEW SD_HOSPITAL.VW_YS_YZDY00_MZDZBL
 (
 CFBZ00, GHID00, BRID00, XM0000, BMMC00, SRRQ00, SRSJ00, CFLSH0, YZDY01, 
YZDY02, KZYS00, LYLXX0, MONTH0, DAY000, TIME00, YYQC00, YPNM00, CFID00, 
YPDLBH, YPMC00, YPZSL0, CFDW00, ZYTS00, YPYFMC, YPCZMC, YSGZH0

 )
 AS 
select
  '1'CFBZ00,A.GHID00,A.BRID00,A.BRXM00 XM0000,B.BMMC00,A.SRRQ00,A.SRSJ00,A.CFLSH0,
  decode(nvl((select 1  from yf_mzcf00 h,yf_mzcfmx j where h.cflsh0=j.cflsh0 and j.ypnm00=m.ypnm00 and h.bccfh0 = a.cflsh0 and h.ghh000 = a.ghh000 and rownum=1),0),1,'(退)')
  ||M.YPMC00||' '||M.CSL000||M.JLDW00||' '||M.YPYFMC||' '||M.PCMC00||'  x'||M.YYSJ00||M.YYSJDW||' (共'||M.YPZSL0||trim(M.CFDW00)||')'  YZDY01,
  M.YPYFMC||' '||M.PCMC00 YZDY02,A.YSXM00 KZYS00,'' LYLXX0,
  substr(A.SRRQ00,5,2) MONTH0,   --月
  substr(A.SRRQ00,7,2) DAY000,   --日
  substr(A.SRSJ00,1,5) TIME00,    --时刻
  (select YYQC00 from XT_YYXX00 where YYID00=B.YYID00) YYQC00,
  to_char(m.ypnm00) ypnm00,M.Cfid00 cfid00,M.YPDLBH,M.YPMC00,M.YPZSL0,M.CFDW00,A.ZYTS00,M.YPYFMC,decode(D.YPCZFL,'0','草药','全成分') YPCZMC,A.YSGZH0
from  YF_MZCF00 A,BM_BMBM00 B,BM_BMBM00 C,YF_MZCFMX M,BM_YD0000 D
where A.YFBMBH=C.BMBH00 and A.GHKS00=B.BMBH00  and  A.CFLSH0=M.CFLSH0 and M.YPNM00=D.YPNM00 and M.YPZSL0>0
union all
select
  '2'CFBZ00,A.ZYGHID,A.BRID00,A.BRXM00 XM0000,D.BMMC00,A.KDRQ00,A.KDSJ00,to_char(A.YJDJH0) CFLSH0,
  decode(nvl((select 1 from yj_yw0000 d where d.cxdjh0 = a.yjdjh0 and d.ZYHGHH = a.ZYHGHH and rownum=1),0),1,'(退)')
  ||A.ZLXMJC YZDY01,
  '' YZDY02,KDYSXM KZYS00,'' LYLXX0,
  substr(A.KDRQ00,5,2) MONTH0,   --月
  substr(A.KDRQ00,7,2) DAY000,   --日
  substr(A.KDSJ00,1,5) TIME00,   --时刻
  (select YYQC00 from XT_YYXX00 where YYID00=D.YYID00) YYQC00,
  ''  ypnm00, a.yjdjh0 cfid00,to_char(B.LBBH00),A.ZLXMJC,A.SL0000,A.DW0000,0,'','',A.KDYS00
from  YJ_YW0000 A,BM_ZLZD00 B,BM_ZLXMLB C,BM_BMBM00 D,BM_BMBM00 E
where A.ZLXMID=B.ZLXMID and B.LBBH00=C.LBBH00 and
      A.KDKSBH=D.BMBH00 and A.YJKSBH=E.BMBH00 and A.MZZYBZ='0' and A.FJDJBZ='0' and A.SL0000>0
/