CREATE OR REPLACE VIEW SD_HOSPITAL.VW_YS_BSCFZL
 (
 CFBZ00, CFLSH0, CFLB00, CFLBMC, CFMC00, SL0000, CFZJE0, KDYSXM, KDKSMC, 
ZXKSMC, CFZT00, CXCFH0, GHH000, GHID00, BRID00, KDRQSJ, KDRQ00, DJH000, 
BZ0000, KDYS00, ZT0000, YJFZH0, RLRY00, ICKH00, KDJGTS, TSBZMC, BWMC00, 
DJFSMC

 )
 AS 
select
  '1'cfbz00, a.cflsh0,a.ypdlbh,decode(a.ypdlbh,'0','西药','1','成药','2','中药')cflbmc,
  (select ypmc00 from yf_mzcfmx m where a.cflsh0=m.cflsh0 and rownum=1)cfmc00,
  decode(a.ypdlbh,'2',A.zyts00||'剂',''),a.cfzje0,a.ysxm00,b.bmmc00 kdksmc,c.bmmc00,
  (select decode(nvl(max(h.cfzt00),'-1'),'-1',  decode(a.cfzt00,'0','未收费','1','已收费','2','已发药','3','已作废'),'0','退费申请','5','已退药','6','已退费') from yf_mzcf00 h where h.bccfh0 = a.cflsh0  and h.ghh000 = a.ghh000),
  a.bccfh0,a.ghh000,a.ghid00,a.brid00,A.srrq00||A.srsj00,to_date(A.srrq00,'yyyymmdd'),decode(a.djh000,0,null,a.djh000),bz0000,A.ysgzh0,A.cfzt00,0,
  (select f.zwxm00 from bm_ygbm00 f where f.ygbh00=a.czy000) rlry00,
  (select ICKH00 FROM IC_YHXX00 WHERE BRID00=a.brid00 and zt0000='1' and rownum=1) ickh00,
  trunc(sysdate-to_date(A.srrq00||A.srsj00,'YYYYMMDDHH24:MI:SS')) KDJGTS,
  (select X.MC0000 from BM_TSBZB0 X where X.BH0000=A.TSBZBH and rownum=1) TSBZMC,
  '' BWMC00,
  D.DJFSMC
from  yf_mzcf00 a,bm_bmbm00 b,bm_bmbm00 c,YS_CFXXB0 D
where a.yfbmbh=c.bmbh00 and a.ghks00=b.bmbh00 and A.CFLSH0=D.CFLSH0(+)
union all
select
  '2'cfbz00,to_char(yjdjh0),to_char(b.lbbh00),c.lbmc00,SF_YS_GETYJXMJC(a.yjdjh0),
  to_char(a.sl0000),zje000,a.kdysxm,d.bmmc00,e.bmmc00,
  decode(a.xmzt00,'0','新开','1','已定价','2',decode(sign(a.zje000),-1,'已退费','已收费'),'3',decode(sign(a.zje000),-1,'已退费','已执行'),'4','已报告','5','疑退','6','作废'),
  to_char(a.cxdjh0),a.zyhghh,a.zyghid,a.brid00,kdrq00||kdsj00,to_date(kdrq00,'yyyymmdd'),decode(a.sfdjh0,0,null,a.sfdjh0),a.bz0000,kdys00,xmzt00,yjfzh0,
  a.LRXM00,(select ICKH00 FROM IC_YHXX00 WHERE BRID00=a.brid00 and zt0000='1' and rownum=1) ickh00,
  trunc(sysdate-to_date(kdrq00||kdsj00,'YYYYMMDDHH24:MI:SS')) KDJGTS,
  (select X.MC0000 from BM_TSBZB0 X where X.BH0000=A.BH0000 and rownum=1) TSBZMC,
  BWMC00,
  null
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'
/