create or replace view vw_yf_xcjzsm_jszsmx as
select '0' as MZZYBZ,      --门诊住院标志
       '门诊' as MZZYMC,   --门诊住院名称
       b.YZLB00,
       b.CFLSH0,
       b.YPQLPC,
       b.YPNM00,
       b.ZSM000,
       b.SCZT00,
       decode(b.SCZT00,'1','已上传','0','未上传','未定义') as SCZTMC,
       b.SCRQ00,
       b.SCSJ00,
       c.JZDH00,
       b.ZYID00,
       b.FIXMEDINS_BCHNO,
       nvl(b.YPZSL0,0) as YPZSL0,
       nvl(b.TYZSL0,0) as TYZSL0
  from SF_BRFY00 a,YF_CFZSMX b,SF_JZB000 c,YF_MZCF00 d,YF_MZCFMX e
    where a.JZDH00=c.JZDH00
      and a.DJH000=d.DJH000
      and a.BRID00=d.BRID00
      and d.CFLSH0=e.CFLSH0
      and b.CFLSH0=e.CFLSH0
      and b.YZLB00='0'
      and b.YPQLPC=e.CFID00
      and b.YPNM00=e.YPNM00
      and a.JZDH00 > 0
      and a.HJJE00 > 0
      and c.HJJE00 > 0
      and not exists(select 1 from SF_JZB000 where BRID00=c.BRID00 and CXJZDH=c.JZDH00)
      and b.MZZYBZ = '0'
      and b.FYTYBZ = '1'
      and nvl(b.YPZSL0,0) - nvl(b.TYZSL0,0) > 0
union all
select '0' as MZZYBZ,      --门诊住院标志
       '门诊' as MZZYMC,   --门诊住院名称
       b.YZLB00,
       b.CFLSH0,
       b.YPQLPC,
       b.YPNM00,
       b.ZSM000,
       b.SCZT00,
       decode(b.SCZT00,'1','已上传','0','未上传','未定义') as SCZTMC,
       b.SCRQ00,
       b.SCSJ00,
       c.JZDH00,
       b.ZYID00,
       b.FIXMEDINS_BCHNO,
       nvl(b.YPZSL0,0) as YPZSL0,
       nvl(b.TYZSL0,0) as TYZSL0
  from SF_BRFY00 a,YF_CFZSMX b,SF_JZB000 c,YJ_YW0000 d,YJ_YWJJ00 e
    where a.JZDH00=c.JZDH00
      and a.DJH000=d.SFDJH0
      and a.BRID00=d.BRID00
      and d.YJDJH0=e.YJDJH0
      and b.YZLB00='3'
      and b.YPQLPC=e.ID0000
      and b.YPNM00=e.SFXMID
      and a.JZDH00 > 0
      and a.HJJE00 > 0
      and c.HJJE00 > 0
      and not exists(select 1 from SF_JZB000 where BRID00=c.BRID00 and CXJZDH=c.JZDH00)
      and b.MZZYBZ = '0'
      and b.FYTYBZ = '1'
      and nvl(b.YPZSL0,0) - nvl(b.TYZSL0,0) > 0
union all
select '1' as MZZYBZ,
      '住院' as MZZYMC,
       b.YZLB00,
       b.CFLSH0,
       b.YPQLPC,
       b.YPNM00,
       b.ZSM000,
       b.SCZT00,
       decode(b.SCZT00,'1','已上传','0','未上传','未定义') as SCZTMC,
       b.SCRQ00,
       b.SCSJ00,
       c.JZDH00,
       b.ZYID00,
       b.FIXMEDINS_BCHNO,
       nvl(b.YPZSL0,0) as YPZSL0,
       nvl(b.TYZSL0,0) as TYZSL0
   from ZY_BRFY00 a,YF_CFZSMX b,ZY_JZB000 c,YF_ZYCF00 d,YF_ZYCFMX e
     where a.JZDH00 = (select JZDH00 from ZY_JZB000 a1 start with a1.JZDH00=c.JZDH00 connect by prior a1.CXJZDH=a1.JZDH00)
       and a.DJH000 = d.DJH000
       and a.ZYID00 = d.ZYID00
       and d.CFLSH0 = e.CFLSH0
       and b.YZLB00 = '2'
       and b.YPQLPC = e.CFID00
       and a.JZDH00 > 0
       and a.HJJE00 > 0
       and c.HJJE00 > 0
       and not exists(select 1 from ZY_JZB000 where ZYID00=c.ZYID00 and CXJZDH=c.JZDH00)
       and b.FYTYBZ = '1'
       and nvl(b.YPZSL0,0) - nvl(b.TYZSL0,0) > 0
union all
select '1' as MZZYBZ,
      '住院' as MZZYMC,
       b.YZLB00,
       b.CFLSH0,
       b.YPQLPC,
       b.YPNM00,
       b.ZSM000,
       b.SCZT00,
       decode(b.SCZT00,'1','已上传','0','未上传','未定义') as SCZTMC,
       b.SCRQ00,
       b.SCSJ00,
       c.JZDH00,
       b.ZYID00,
       b.FIXMEDINS_BCHNO,
       nvl(b.YPZSL0,0) as YPZSL0,
       nvl(b.TYZSL0,0) as TYZSL0
   from ZY_BRFY00 a,YF_CFZSMX b,ZY_JZB000 c,YF_YZYPSQ d
     where a.JZDH00 = (select JZDH00 from ZY_JZB000 a1 start with a1.JZDH00=c.JZDH00 connect by prior a1.CXJZDH=a1.JZDH00)
       and a.DJH000 = d.DJH000
       and a.ZYID00 = d.ZYID00
       and b.YZLB00 = '1'
       and b.YPQLPC = d.YPQLPC
       and a.JZDH00 > 0
       and a.HJJE00 > 0
       and c.HJJE00 > 0
       and not exists(select 1 from ZY_JZB000 where ZYID00=c.ZYID00 and CXJZDH=c.JZDH00)
       and b.FYTYBZ = '1'
       and nvl(b.YPZSL0,0) - nvl(b.TYZSL0,0) > 0
union all
select '1' as MZZYBZ,
      '住院' as MZZYMC,
       b.YZLB00,
       b.CFLSH0,
       b.YPQLPC,
       b.YPNM00,
       b.ZSM000,
       b.SCZT00,
       decode(b.SCZT00,'1','已上传','0','未上传','未定义') as SCZTMC,
       b.SCRQ00,
       b.SCSJ00,
       c.JZDH00,
       b.ZYID00,
       b.FIXMEDINS_BCHNO,
       nvl(b.YPZSL0,0) as YPZSL0,
       nvl(b.TYZSL0,0) as TYZSL0
   from ZY_BRFY00 a,YF_CFZSMX b,ZY_JZB000 c,YJ_YW0000 d,YJ_YWJJ00 e
     where a.JZDH00 = (select JZDH00 from ZY_JZB000 a1 start with a1.JZDH00=c.JZDH00 connect by prior a1.CXJZDH=a1.JZDH00)
       and a.DJH000 = d.SFDJH0
       and a.ZYID00 = d.ZYGHID
       and b.YZLB00 = '3'
       and b.YPQLPC = e.ID0000
       and a.JZDH00 > 0
       and a.HJJE00 > 0
       and c.HJJE00 > 0
       and d.MZZYBZ = '1'
       and not exists(select 1 from ZY_JZB000 where ZYID00=c.ZYID00 and CXJZDH=c.JZDH00)
       and b.FYTYBZ = '1'
       and nvl(b.YPZSL0,0) - nvl(b.TYZSL0,0) > 0;