-- Start of DDL Script for View SD_HOSPITAL.VW_YS_CFXX00_YBKF
-- Generated 2021-01-12 17:37:58 from SD_HOSPITAL@30

CREATE OR REPLACE VIEW vw_ys_cfxx00_ybkf (
   cflsh0,
   cfid00,
   ybbm00,
   yyxmmc,
   sl0000,
   jjdw00,
   lsdj00,
   je0000,
   gg0000,
   yypc00,
   mcsl00,
   yyts00,
   ghh000,
   xmlx00,
   tymc00,
   ybxmmc,
   yyxmbh,
   yzmxid,
   ybzxlb,
   cfzt00,
   zlxmid,
   tsbzbh,
   tsbzmc )
AS
select
  a.CFLSH0,
  d.CFID00,
  (select n.YBXMBH from BM_YBSFDY n where n.YBZXLB=b.YBZXLB and n.SFYP00='Y' and d.YPNM00=n.SFXMID),
  d.YPMC00,
  d.YPZSL0,
  d.CFDW00,
  d.LSDJ00,
  d.HJJE00,
  d.YPGG00,
  (select YBPCBM from BM_PC0000 p where p.PCID00 = g.PCID00),
  decode(substr(to_char(d.CSL000),1,1),'.','0','')||to_char(d.CSL000)||d.JLDW00,
  to_char(d.YYSJ00),
  a.GHH000,
  '1',
  (select YPTYM0 from BM_YD0000 e where d.YPNM00=e.YPNM00),
  (select o.XMMC00 from BM_YBSFDY n,BM_YBSFXM o where n.YBZXLB=b.YBZXLB and n.SFYP00='Y' and d.YPNM00=n.SFXMID AND n.YBXMBH=o.XMBH00 and n.YBZXLB=o.YBZXLB),
  d.YPNM00,
  d.CFID00,
  b.YBZXLB,
 decode(a.cfzt00,'0','0','1') cfzt00,
  d.YPNM00,
  A.TSBZBH,
  (select X.MC0000 from BM_TSBZB0 X where X.BH0000=A.TSBZBH and X.YBZXLB=B.YBZXLB and rownum=1) TSBZMC
from  yf_mzcf00 a,sf_brxxb0 b,yf_mzcfmx d, YS_CFMX00 g
where a.ghh000=b.ghh000 and a.cflsh0=d.cflsh0  and d.ypzsl0>0 and a.cflsh0 = g.cflsh0 and D.ZFFSBZ='N' and d.ypnm00=g.ypnm00
union all
select
  to_char(a.YJDJH0),
  d.ID0000,
  (select n.YBXMBH from BM_YBSFDY n where n.YBZXLB=b.YBZXLB and n.SFYP00='N' and d.SFXMID=n.SFXMID),
  e.XMMC00,
  d.SFCS00*d.SL0000,
  e.DW0000,
  d.JJJE00,
  d.ZFJE00,
  e.GG0000,
  '',
  '',
  '',
  b.GHH000,
   decode(JSXM00,'2','3','2'),
  a.zlxmjc,
  (select o.XMMC00 from BM_YBSFDY n,BM_YBSFXM o where n.YBZXLB=b.YBZXLB and n.SFYP00='N' and d.SFXMID=n.SFXMID AND n.YBXMBH=o.XMBH00 and n.YBZXLB=o.YBZXLB),
  d.SFXMID,
  d.YJDJH0,
  b.YBZXLB, 
   decode(a.xmzt00,'0','0','1','0','2') cfzt00,
  a.ZLXMID,
  A.BH0000,
  (select X.MC0000 from BM_TSBZB0 X where X.BH0000=A.BH0000 and X.YBZXLB=B.YBZXLB and rownum=1) TSBZMC
from  YJ_YW0000 a,SF_BRXXB0 b,YJ_YWJJ00 d,BM_YYSFXM e 
where a.ZYHGHH=b.GHH000 and a.YJDJH0=d.YJDJH0  and d.SL0000>0 and d.SFXMID=e.SFXMID
/


-- End of DDL Script for View SD_HOSPITAL.VW_YS_CFXX00_YBKF