create or replace view vw_zy_basyxx as
select 
  a.ZYID00 ,
  a.ZYLSH0 ,
  a.CARDNO ,
  a.YYBAH0 ,
  decode(nvl(a.RYKSBH,0),0,a.RYKB00,((select BMMC00 from BM_BMBM00 where BMBH00 = a.RYKSBH and rownum=1))) as RYKB00 ,
  decode(nvl(a.RYBQBH,0),0,a.RYBS00,((select BMMC00 from BM_BMBM00 where BMBH00 = a.RYBQBH and rownum=1))) as RYBS00 ,
  a.ZKKB00 ,
  decode(nvl(a.CYKSBH,0),0,a.CYKB00,((select BMMC00 from BM_BMBM00 where BMBH00 = a.CYKSBH and rownum=1))) as CYKB00 ,
  decode(nvl(a.CYBQBH,0),0,a.CYBS00,((select BMMC00 from BM_BMBM00 where BMBH00 = a.CYBQBH and rownum=1))) as CYBS00 ,
  a.RYQK00 ,            --入院情况(1、危中 2、急诊 3、一般)
  decode(a.RYQK00,'1','危中','2','急诊','3','一般') as RYQKMC,
  a.RYZD00 ,
  a.RYZDQK ,             
  a.RYTJ00 ,             --入院途径（新增）1.急诊2.门诊3.其他医疗机构转入9.其他
  decode(a.RYTJ00,'1','急诊','2','门诊','3','其他医疗机构转入','9','其他') as RYTJMC,  
  a.BAZL00 ,             --病案质量（1.甲2.乙3.丙）
  decode(a.BAZL00,'1','甲','2','乙','3','丙') as BAZLMC, 
  a.LYFS00 ,             --离院方式（1.医嘱离院，2.医嘱转院，3.医嘱转社区卫生服务机构/乡镇卫生院，4.非医嘱离院5.死亡9.其他）
  decode(a.LYFS00,'1','医嘱离院','2','医嘱转院','3','医嘱转社区卫生服务机构/乡镇卫生院','4','非医嘱离院','5','死亡','9','其他') as LYFSMC,    
  a.CYZD00 ,             
  a.ZDMC00 ,             
  a.CYQK00 ,            --出院主要情况
  (select MC0000 from XT_ZLXG00 where DM0000=a.CYQK00 and rownum=1) as CYQKMC,
  a.CYZD01 ,
  a.ZDMC01 ,
  a.CYQK01 ,
  a.CYZD02 ,
  a.ZDMC02 ,
  a.CYQK02 ,
  a.CYZD03 ,
  a.ZDMC03 ,
  a.CYQK03 ,
  a.CYZD04 ,
  a.ZDMC04 ,
  a.CYQK04 ,
  a.CYZD05 ,
  a.ZDMC05 ,
  a.CYQK05 ,
  a.CYZD06 ,             --出院诊断编码06
  a.ZDMC06 ,             --出院诊断名称06
  a.CYQK06 ,           --出院情况06
  a.CYZD07 ,             --出院诊断编码07
  a.ZDMC07 ,             --出院诊断名称07
  a.CYQK07 ,          --出院情况07
  a.CYZD08 ,             --出院诊断编码08
  a.ZDMC08 ,             --出院诊断名称08
  a.CYQK08 ,          --出院情况08
  a.CYZD09 ,             --出院诊断编码09
  a.ZDMC09 ,             --出院诊断名称09
  a.CYQK09 ,         --出院情况09
  a.CYZD10 ,             --出院诊断编码10
  a.ZDMC10 ,             --出院诊断名称10
  a.CYQK10 ,           --出院情况10  
  a.BLZD00 ,
  b.SXYSXM ZZYSXM,
  a.SSBH01 ,             --手术编码01
  a.SSMC01 ,             --手术名称01
  a.SSRQ01 ,             --手术日期01
  a.MZFS01 ,             --麻醉方式01
  a.SSJB01 ,             --手术级别01
  a.CZYS01 ,             --手术医生01(术者，Ⅰ助，Ⅱ助)
  --(select ZWXM00 from BM_YGBM00 where YGBH00=a.CZYS01 and rownum=1 ) as YSXM01,  --医生姓名01
  a.DYZS01 ,             --第一助手01
  (select ZWXM00 from BM_YGBM00 where YGBH00=a.DYZS01 and rownum=1 ) as YZXM01,  --Ⅰ助姓名01
  a.DEZS01 ,             --第二助手01
  (select ZWXM00 from BM_YGBM00 where YGBH00=a.DEZS01 and rownum=1 ) as EZXM01,  --Ⅱ助姓名01
  a.YHDJ01 ,             --切口愈合等级01
  a.MZYS01 ,             --麻醉医师01
  (select ZWXM00 from BM_YGBM00 where YGBH00=a.MZYS01 and rownum=1 ) as MZXM01,   --麻醉医师姓名01
  a.SSBH02 ,
  a.SSMC02 ,
  a.SSRQ02 ,
  a.MZFS02 ,
  a.SSJB02 ,
  a.CZYS02 ,             
 -- (select ZWXM00 from BM_YGBM00 where YGBH00=a.CZYS02 and rownum=1 ) as YSXM02,  
  a.DYZS02 ,            
  (select ZWXM00 from BM_YGBM00 where YGBH00=a.DYZS02 and rownum=1 ) as YZXM02,  
  a.DEZS02 ,            
  (select ZWXM00 from BM_YGBM00 where YGBH00=a.DEZS02 and rownum=1 ) as EZXM02,  
  a.YHDJ02 ,             
  a.MZYS02 ,             
  (select ZWXM00 from BM_YGBM00 where YGBH00=a.MZYS02 and rownum=1 ) as MZXM02,   
  a.SSBH03 ,
  a.SSMC03 ,
  a.SSRQ03 ,
  a.MZFS03 ,
  a.SSJB03 ,
  a.CZYS03 ,             
 -- (select ZWXM00 from BM_YGBM00 where YGBH00=a.CZYS03 and rownum=1 ) as YSXM03,  
  a.DYZS03 ,            
  (select ZWXM00 from BM_YGBM00 where YGBH00=a.DYZS03 and rownum=1 ) as YZXM03,  
  a.DEZS03 ,            
  (select ZWXM00 from BM_YGBM00 where YGBH00=a.DEZS03 and rownum=1 ) as EZXM03,  
  a.YHDJ03 ,             
  a.MZYS03 ,             
  (select ZWXM00 from BM_YGBM00 where YGBH00=a.MZYS03 and rownum=1 ) as MZXM03,   
  a.SSBH04 ,
  a.SSMC04 ,
  a.SSRQ04 ,
  a.MZFS04 ,
  a.SSJB04 ,
  a.CZYS04 ,             
  --(select ZWXM00 from BM_YGBM00 where YGBH00=a.CZYS04 and rownum=1 ) as YSXM04,  
  a.DYZS04 ,            
  (select ZWXM00 from BM_YGBM00 where YGBH00=a.DYZS04 and rownum=1 ) as YZXM04,  
  a.DEZS04 ,            
  (select ZWXM00 from BM_YGBM00 where YGBH00=a.DEZS04 and rownum=1 ) as EZXM04,  
  a.YHDJ04 ,             
  a.MZYS04 ,             
  (select ZWXM00 from BM_YGBM00 where YGBH00=a.MZYS04 and rownum=1 ) as MZXM04,   
  a.SSBH05 ,
  a.SSMC05 ,
  a.SSRQ05 ,
  a.MZFS05 ,
  a.SSJB05 ,
  a.CZYS05 ,             
  --(select ZWXM00 from BM_YGBM00 where YGBH00=a.CZYS05 and rownum=1 ) as YSXM05,  
  a.DYZS05 ,            
  (select ZWXM00 from BM_YGBM00 where YGBH00=a.DYZS05 and rownum=1 ) as YZXM05,  
  a.DEZS05 ,            
  (select ZWXM00 from BM_YGBM00 where YGBH00=a.DEZS05 and rownum=1 ) as EZXM05,  
  a.YHDJ05 ,             
  a.MZYS05 ,             
  (select ZWXM00 from BM_YGBM00 where YGBH00=a.MZYS05 and rownum=1 ) as MZXM05,   
  a.SSBH06 ,
  a.SSMC06 ,
  a.SSRQ06 ,
  a.MZFS06 ,
  a.SSJB06 ,
  a.CZYS06 ,             
  --(select ZWXM00 from BM_YGBM00 where YGBH00=a.CZYS06 and rownum=1 ) as YSXM06,  
  a.DYZS06 ,            
  (select ZWXM00 from BM_YGBM00 where YGBH00=a.DYZS06 and rownum=1 ) as YZXM06,  
  a.DEZS06 ,            
  (select ZWXM00 from BM_YGBM00 where YGBH00=a.DEZS06 and rownum=1 ) as EZXM06,  
  a.YHDJ06 ,             
  a.MZYS06 ,             
  (select ZWXM00 from BM_YGBM00 where YGBH00=a.MZYS06 and rownum=1 ) as MZXM06,   
  a.SSBH07 ,
  a.SSMC07 ,
  a.SSRQ07 ,
  a.MZFS07 ,
  a.SSJB07 ,
  a.CZYS07 ,             
 -- (select ZWXM00 from BM_YGBM00 where YGBH00=a.CZYS07 and rownum=1 ) as YSXM07,  
  a.DYZS07 ,            
  (select ZWXM00 from BM_YGBM00 where YGBH00=a.DYZS07 and rownum=1 ) as YZXM07,  
  a.DEZS07 ,            
  (select ZWXM00 from BM_YGBM00 where YGBH00=a.DEZS07 and rownum=1 ) as EZXM07,  
  a.YHDJ07 ,            
  a.MZYS07 ,             
  (select ZWXM00 from BM_YGBM00 where YGBH00=a.MZYS07 and rownum=1 ) as MZXM07,   
  a.SSBH08 ,
  a.SSMC08 ,
  a.SSRQ08 ,
  a.MZFS08 ,
  a.SSJB08 ,
  a.CZYS08 ,             
 -- (select ZWXM00 from BM_YGBM00 where YGBH00=a.CZYS08 and rownum=1 ) as YSXM08,  
  a.DYZS08 ,            
  (select ZWXM00 from BM_YGBM00 where YGBH00=a.DYZS08 and rownum=1 ) as YZXM08,  
  a.DEZS08 ,            
  (select ZWXM00 from BM_YGBM00 where YGBH00=a.DEZS08 and rownum=1 ) as EZXM08,  
  a.YHDJ08 ,             
  a.MZYS08 ,             
  (select ZWXM00 from BM_YGBM00 where YGBH00=a.MZYS08 and rownum=1 ) as MZXM08,   
  a.CZRQ00 ,
  a.CZSJ00 ,
  a.FSCGBZ ,          --未发送 1已发送
  a.FSCGBZ as FSFLAG , --为了兼容另外一个关于病案首页发送的视图      
  decode(a.FSCGBZ,'0','未发送','1','已发送') as FSBSMC,    
  a.ERROR0 ,
  '0' as FSR000,
  ' ' as FSRXM0,
  b.CYRQ00 ,
  b.CYSJ00 ,
  c.BRZJBH as SFZHAO ,                                     --证件号码
  b.XM0000 as XMING0 ,                                     --姓名
  decode(b.XB0000,'男','1','女','2') as XBIE00 ,           --性别(1男2女)
  b.XB0000 as XBIEMC,
  b.CSRQ00 ,                                               --出生日期
  substr(SF_XT_CSRQTONL(b.CSRQ00),1,10) as BRNL00,         --年龄 
  b.ZYH000 ,
  b.BRRYCS as ZYCS00,
  b.FBBH00 ,                                               --费别编号
  (select FBMC00 from BM_BRFBB0 where FBBH00 = b.FBBH00) as FBMC00,  --费别名称
  b.DQBQ00 ,
(select YYID00 from BM_BMBM00 where BMBH00 = b.DQBQ00 and rownum=1) as BQYYID, --病人病区所属医院ID
  a.CYXJ00,
  a.MZZD00,
  a.MZZDMC,
  a.SHR000,
  a.SHRQ00,
  a.RYKSBH,  --入院科室编号 
  (select BMMC00 from BM_BMBM00 where BMBH00 = a.RYKSBH and rownum=1) as RYKSMC,
  a.RYBQBH,  --入院病区编号
  (select BMMC00 from BM_BMBM00 where BMBH00 = a.RYBQBH and rownum=1) as RYBQMC,
  a.CYKSBH,  --出院科室编号
  (select BMMC00 from BM_BMBM00 where BMBH00 = a.CYKSBH and rownum=1) as CYKSMC,
  a.CYBQBH,
  (select BMMC00 from BM_BMBM00 where BMBH00 = a.CYBQBH and rownum=1) as CYBQMC,
  a.CYRQ00 SJCYRQ,
  a.BLZDBM,
  a.SCCS00   --上传次数    
from ZY_BASYXX a,ZY_BRXXB0 b,BM_BRXXB0 c
  where a.ZYID00 = b.ZYID00
    and b.BRID00 = c.BRID00
;