create or replace view vw_ba_cybaxx
(zyid00, brid00, mrn000, zyh000, xm0000, xb0000, csrq00, ryrqsj, ryzd00, cyzd00, rysqk0, rybq00, dqbq00, dqbqmc, rzks00, rycwh0, cydjsj, sjcyrq, zzysxm, zrysxm, sxysxm, brrycs, brzt00, dqksmc, shbz00, bazt00, ybmc00, yblb00, cydjrq, fsrq00, fbbh00, gsdjh0, zycs00, bahcs0, dqks00, shrxm0, shsj00, ybkh00, ybid00, ybzyh0, sfxnh0, rylb00, qsrqsj, qsrxm0, htrq00, htsj00, htr000, htrxm0, lrrq00, fsbajk, bawcbz, fsbarq, bags00, lxrdh0, hjje00, scshrq, scshr0, dbzmc0, dbzbm0, zzzt00, zzztbh, gdgdsj, asgd00, sctjrq, gdtjrq, sfzh00, wtzt00, cyrq00)
as
select
    A.ZYID00,                   --住院ID
    A.BRID00,                   --病人ID
    A.MRN000,                   --病案号
    A.ZYH000,                   --住院号
    A.XM0000,                   --姓名
    A.XB0000,                   --性别
    to_char(to_date(A.CSRQ00,'YYYY.MM.DD'),'YYYY.MM.DD') CSRQ00,   --出生日期
    to_char(to_date(A.RYRQ00||A.RYSJ00,'YYYY.MM.DD HH24:MI:SS'),'YYYY.MM.DD HH24:MI:SS') RYRQSJ, --入院时间
    A.RYZD00,                   --入院诊断
    (select ZDMC00 from BQ_BRZDXX where ZDLB00='3' and ZYID00=A.ZYID00 and rownum=1),
    A.RYSQK0,                   --入院时情况(危、急、一般)
    A.RYBQ00,                   --入院病区
    A.DQBQ00,                   --当前病区
    (select  B.BMMC00 from BM_BMBM00 B where A.DQBQ00=B.BMBH00) DQBQMC,            --当前病区名称
    A.RZKS00,                   --入院科室
    A.RYCWH0,                   --入院床位号
    to_char(to_date(A.SJCYRQ||A.SJCYSJ,'YYYY.MM.DD HH24:MI:SS'),'YYYY.MM.DD HH24:MI:SS') CYDJSJ, --出院登记时间
    to_char(to_date(D.CYRQ00||D.CYSJ00,'YYYY.MM.DD HH24:MI:SS'),'YYYY.MM.DD HH24:MI:SS') CYRQ00,
    nvl(D.ZZYSMC,A.ZZYSXM), --主治医生姓名
    nvl(D.FZRMC0,A.ZRYSXM), --主任医生姓名
    nvl(D.ZYYSMC,A.SXYSXM),--实习医生员姓名
    A.BRRYCS,                   --入院次数
    decode(substrb(A.BRZT00,1,1),'1','入院登记','2','在院','3','登记出院','4','结算出院',
           '5','呆账出院','9','取消住院') BRZT00,--病人状态
    D.CYKBMC DQKSMC,--病历所在当前科室
    nvl(D.SHBZ00,'9') SHBZ00,
    decode(nvl(D.SHBZ00,'9'),'9','未创建','0','未提交','1','待签收','2','已审核','3','退回','4',
          '已修订','5','已签收','未提交') ||
    decode((select SDBASY from ZS_BLXX01 Z where Z.ZYID00=A.ZYID00 and  rownum=1),'1','(已锁)','') BAZT00,
    (select YBMC00 from IC_YBBRLB where FBBH00=A.FBBH00 and YBLB00=A.YBLB00) YBMC00,
    A.YBLB00,
    A.SJCYRQ CYDJRQ,
    D.FSRQ00,
    A.FBBH00,
    A.GSDJH0,
    A.BRRYCS,
    lpad(trim(A.MRN000)||lpad(to_char(A.BRRYCS),2,'0'),10,'0'),
    A.DQKS00,
    D.SHRXM0,
    to_char(to_date(D.SHRQ00||D.SHSJ00,'YYYYMMDD HH24:MI:SS'),'YYYY.MM.DD HH24:MI:SS') SHSJ00,
    decode(trim(A.YBKH00),'0','',trim(A.YBKH00)),
    E.YBID00,
    A.YBZYH0,
    nvl((select SFXNH0 from IC_YBBRLB where FBBH00=A.FBBH00 and YBLB00=A.YBLB00 and YBZXLB=A.YBZXLB and
        rownum=1),'0') SFXNH0,
    A.RYLB00,
    to_char(to_date(decode(d.QSRQ00,NULL,'20991231',D.QSRQ00||QSSJ00),'YYYYMMDD HH24:MI:SS'),'YYYY.MM.DD HH24:MI:SS') QSRQSJ,
    (select ZWXM00 from BM_YGBM00 where YGBH00=D.QSR000) QSRXM0,
    HTRQ00,HTSJ00,HTR000,
    (select ZWXM00 from BM_YGBM00 where YGBH00=D.HTR000  and D.HTR000<>0) HTRXM0,LRRQ00,
    D.FSBAJK,D.BAWCBZ,D.FSBARQ,decode(A.SJCYRQ,'20991231','1','0') BAGS00 ,D.LXRDH0,
    D.ZJE000 HJJE00,
    (select to_char(to_date(CZRQ00 || CZSJ00, 'YYYYMMDD HH24:MI:SS'),'YYYY.MM.DD HH24:MI:SS') CZRQ00
             from (select * from XT_XTCZRZ order by CZID00 asc)
            where CZTABLE = 'BA_BRZYXX'
              and CZNR00 = '2'
              and DATAID =  A.ZYID00  and rownum <= 1 ) SCSHRQ,--首次审核时间
    (select (select ZWXM00 from BM_YGBM00 where YGBH00=CZRYBM)
       from (select * from XT_XTCZRZ order by CZID00 asc)
      where CZTABLE = 'BA_BRZYXX' and CZNR00='2'
       and  DATAID =A.ZYID00  and rownum<=1 ) SCSHR0,--首次审核人
     A.DBZMC0,A.DBZBM0,
     decode(D.ZZZT00,null,decode(nvl(D.SHBZ00,'9'),'9','未创建','0','未提交','1','待签收'),'2','待整改','3','已入库',
     '4','已借阅','6','已归还','5','已签收','7','已整改','未提交') ZZZT00,
     ZZZT00 ZZZTBH,
    to_char(to_date(D.GDGDRQ||D.GDGDSJ,'YYYYMMDD HH24:MI:SS'),'YYYY.MM.DD HH24:MI:SS') GDGDSJ,--规定归档时间
    decode(sign(D.GDGDRQ-D.QSRQ00),'0','是','1','是','否') ASGD00,--按时归档。
    D.SCTJRQ, --首次提交日期
    D.GDTJRQ,  --规定提交日期
    D.SFZH00,
    '',
    D.CYRQ00
from ZY_BRXXB0 A,BA_BRZYXX D,BM_BRXXB0 E
where A.ZYID00=D.LSH000(+) and
A.BRZT00 in ('2','20','3','4','5','24')
and A.BRID00=E.BRID00
;