CREATE OR REPLACE VIEW SD_HOSPITAL.VW_ZY_BRXXZYTZ
 (
 BRID00, ZYID00, ZYH000, RYZDMC, ZZYSXM, ZRYSXM, SXYSXM, BRXM00, BRXB00, 
BRCSRQ, BRHYZK, BRZJBH, BRSZSF, BRSZS0, BRSZDS, BRLXDZ, BRJTDZ, BRLXDH, 
YJJE00, XTN000, XTY000, XTR000, SQRQN0, SQRQY0, SQRQR0, RZKS00, NL0000, 
RYBQJ0, RYBQW0, RYBQYB, FBXXZF, FBXXYB, FBMC00, TYDWMC, BRLXXM, BRLXGX, 
SZYSXM, DQBQMC, DQKSMC, KSFB00, RYFS00

 )
 AS 
select 
  a.BRID00,                                                         --病人ID
  a.ZYID00,                                                         --住院ID
  a.ZYH000,                                                         --住院号    
  a.RYZDMC,                                                         --入院诊断名称
  a.ZZYSXM,                                                         --主治医生姓名
  a.ZRYSXM,                                                         --主任医生姓名
  a.SXYSXM,                                                         --主管医生员姓名
  b.BRXM00,                                                         --病人姓名
  b.BRXB00,                                                         --病人性别
  b.BRCSRQ,                                                         --出生日期
  b.BRHYZK,                                                         --婚姻状况
  b.BRZJBH,                                                         --证件编号
  b.BRSZSF,                                                         --病人所在省份
  b.BRSZS0,                                                         --病人所在市(县、区)
  b.BRSZDS,                                                         --病人所在地级市
  b.BRLXDZ,                                                         --联系人地址
  b.BRJTDZ,                                                         --家庭地址/通信地址
  b.BRLXDH,                                                         --联系人电话
  nvl((select sum(JFJE00) from ZY_BRJFB0 where JFLBID='2' and BRID00=a.BRID00 and ZFFS00 in(1,2,8,24,25)),0) as YJJE00,    --预交金额
  to_char(sysdate, 'yyyy' ) as XTN000,                              --系统年
  to_char(sysdate, 'MM' ) as XTY000,                                --系统月
  to_char(sysdate, 'DD' ) as XTR000,                                --系统日
  substr(a.RYRQ00,1,4) as SQRQN0,                                   --入院日期年
  substr(a.RYRQ00,5,2) as SQRQY0,                                   --入院日期月
  substr(a.RYRQ00,7,2) as SQRQR0,                                   --入院日期日
  (select BMMC00 from BM_BMBM00 where BMBH00=a.RZKS00) as RZKS00,   --入住科室
  substr(SF_XT_CSRQTONL(b.BRCSRQ,''),1,20) as NL0000,               --病人年龄
  decode(a.RYSQK0,'急','√','') as RYBQJ0,                           --入院病情急
  decode(a.RYSQK0,'危','√','') as RYBQW0,                           --入院病情危
  decode(a.RYSQK0,'一般','√','') as RYBQYB,                         --入院病情一般 
  decode(b.FBBH00,'1','√','') as FBXXZF,                            --费别信息自费
  decode(b.FBBH00,'3','√','') as FBXXYB,                            --费别信息医保
   (select FBMC00 from BM_BRFBB0 where FBBH00 = b.FBBH00) as FBMC00 ,
  b.TYDWMC,                                                         --单位名称
  b.BRLXXM,                                                         --联系人姓名
  b.BRLXGX,                                                         --联系人关系
  (select ZWXM00 from BM_YGBM00 where YGBH00 = a.SZYS00) as SZYSXM, --收治医生姓名
  (select BMMC00 from BM_BMBM00 where BMBH00 = a.DQBQ00) as DQBQMC, --所在病区
  (select BMMC00 from BM_BMBM00 where BMBH00 = a.DQKS00) as DQKSMC, --所在科室
   nvl((select KSWZ00 from BM_KSFB00 where KSBH00 = a.DQBQ00),(select yybmdz from bm_bmbm00 where bmbh00 = a.DQks00)) as KSFB00,   --科室分布
   a.ryfs00   --入院方式
from ZY_BRXXB0 a,BM_BRXXB0 b 
where a.BRID00 = b.BRID00
/