create or replace view vw_ba_cybaxx_0528 as select A.ZYID00, --住院ID A.BRID00, --病人ID A.MRN000, --病案号 A.ZYH000, --住院号 nvl(D.BRXM00,A.XM0000) XM0000, --姓名 nvl(D.BRXB00,A.XB0000) XB0000, --性别 --to_char(to_date(A.CSRQ00,'YYYY.MM.DD'),'YYYY.MM.DD') CSRQ00, --出生日期 A.CSRQ00 CSRQ00, --出生日期 --to_char(to_date(A.RYRQ00||A.RYSJ00,'YYYY.MM.DD HH24:MI:SS'),'YYYY.MM.DD HH24:MI:SS') RYRQSJ, --入院时间 A.RYRQ00||' '||A.RYSJ00 RYRQSJ, --入院时间 A.RYZD00, --入院诊断 (select ZDMC00 from BQ_BRZDXX where ZDLB00='3' and ZYID00=A.ZYID00 and rownum=1) CYZD00, A.RYSQK0, --入院时情况(危、急、一般) A.RYBQ00, --入院病区 A.DQBQ00, --当前病区 (select B.BMMC00 from BM_BMBM00 B where A.DQBQ00=B.BMBH00 and rownum=1) 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, --出院登记时间 A.SJCYRQ||' '||A.SJCYSJ CYDJSJ, --出院登记时间 --to_char(to_date(D.CYRQ00||D.CYSJ00,'YYYY.MM.DD HH24:MI:SS'),'YYYY.MM.DD HH24:MI:SS') SJCYRQ, decode(D.CYRQ00||' '||D.CYSJ00,' ',A.CYRQ00||' '||A.CYSJ00,D.CYRQ00||' '||D.CYSJ00) sjcyrq, nvl(D.ZZYSMC,A.ZZYSXM) ZZYSXM, --主治医生姓名 nvl(D.FZRMC0,A.ZRYSXM) ZRYSXM, --主任医生姓名 nvl(D.ZYYSMC,A.SXYSXM) SXYSXM,--实习医生员姓名 A.BRRYCS BRRYCS, --入院次数 decode(substrb(A.BRZT00,1,1),'1','入院登记','2','在院','3','登记出院','4','结算出院', '5','呆账出院','9','取消住院') BRZT00,--病人状态 nvl(D.CYKBMC,(select BM.BMMC00 from bm_bmbm00 BM where BM.BMBH00=A.DQKS00 and rownum=1)) 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 and rownum=1) YBMC00, A.YBLB00 YBLB00, A.SJCYRQ CYDJRQ, --A.SJCYRQ D.FSRQ00 FSRQ00, A.FBBH00 FBBH00, A.GSDJH0 GSDJH0, A.BRRYCS ZYCS00, lpad(trim(A.MRN000)||lpad(to_char(A.BRRYCS),2,'0'),10,'0') BAHCS0, A.DQKS00 DQKS00, D.SHRXM0 SHRXM0, D.SHRQ00||' '||D.SHSJ00 SHSJ00, decode(trim(A.YBKH00),'0','',trim(A.YBKH00)) YBKH00, (select E.YBID00 from BM_BRXXB0 E where E.BRID00=A.BRID00 and rownum=1) YBID00, A.YBZYH0 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 RYLB00, D.QSRQ00||' '||QSSJ00 QSRQSJ, (select ZWXM00 from BM_YGBM00 where YGBH00=D.QSR000 and rownum=1) QSRXM0, HTRQ00,HTSJ00,HTR000, (select ZWXM00 from BM_YGBM00 where YGBH00=D.HTR000 and D.HTR000<>0 and rownum=1) HTRXM0,LRRQ00, D.FSBAJK FSBAJK,D.BAWCBZ BAWCBZ,D.FSBARQ FSBARQ,decode(A.SJCYRQ,'20991231','1','0') BAGS00 ,D.LXRDH0 LXRDH0, D.ZJE000 HJJE00, (select CZRQ00||' '||CZSJ00 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 DBZMC0,A.DBZBM0 DBZBM0, decode(D.ZZZT00,null,decode(nvl(D.SHBZ00,'9'),'9','未创建','0','未提交','1','待签收'),'2','待整改','3','已入库', '4','已借阅','6','已归还','5','已签收','7','已整改','未提交') ZZZT00, ZZZT00 ZZZTBH, D.GDGDRQ||' '||D.GDGDSJ GDGDSJ,--规定归档时间 decode(sign(D.GDGDRQ-D.QSRQ00),'0','是','1','是','否') ASGD00,--按时归档。 D.SCTJRQ SCTJRQ, --首次提交日期 D.GDTJRQ GDTJRQ, --规定提交日期 D.SFZH00 SFZH00, (select decode(WT.WTZT00,'*','*','') from BA_WTBAGL WT where D.LSH000=WT.LSH000 and rownum=1) WTZT00, D.CYRQ00, (select BACFBM from BA_BAQTXX q where d.lsh000=q.lsh000 and rownum=1) BACFBM, (select (DZGRQ0||' '||DZGSJ0) from BA_ZGSJCF zg where d.lsh000=zg.lsh000) DZGRQ0,--待整改时间 (select (YZGRQ0||' '||YZGSJ0) from BA_ZGSJCF zg where d.lsh000=zg.lsh000) YZGRQ0 --已整改时间 from ZY_BRXXB0 A,BA_BRZYXX D where A.ZYID00=D.LSH000 and A.BRZT00 in ('2','20','3','4','5') union all select A.ZYID00, --住院ID A.BRID00, --病人ID A.MRN000, --病案号 A.ZYH000, --住院号 A.XM0000 XM0000, --姓名 A.XB0000 XB0000, --性别 A.CSRQ00 CSRQ00, --出生日期 A.RYRQ00 || ' ' || A.RYSJ00 RYRQSJ, --入院时间 A.RYZD00, --入院诊断 (select ZDMC00 from BQ_BRZDXX where ZDLB00 = '3' and ZYID00 = A.ZYID00 and rownum = 1) CYZD00, A.RYSQK0, --入院时情况(危、急、一般) A.RYBQ00, --入院病区 A.DQBQ00, --当前病区 (select B.BMMC00 from BM_BMBM00 B where A.DQBQ00 = B.BMBH00 and rownum = 1) DQBQMC, --当前病区名称 A.RZKS00, --入院科室 A.RYCWH0, --入院床位号 A.SJCYRQ || ' ' || A.SJCYSJ CYDJSJ, --出院登记时间 A.Sjcyrq CYRQ00, A.ZZYSXM ZZYSXM, --主治医生姓名 A.ZRYSXM ZRYSXM, --主任医生姓名 A.SXYSXM SXYSXM, --实习医生员姓名 A.BRRYCS BRRYCS, --入院次数 decode(substrb(A.BRZT00, 1, 1), '1', '入院登记', '2', '在院', '3', '登记出院', '4', '结算出院', '5', '呆账出院', '9', '取消住院') BRZT00, --病人状态 (select BM.BMMC00 from bm_bmbm00 BM where BM.BMBH00 = A.DQKS00 and rownum = 1) DQKSMC, --病历所在当前科室 '9' SHBZ00, '未创建' || 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 and rownum = 1) YBMC00, A.YBLB00 YBLB00, SJCYRQ CYDJRQ, --A.SJCYRQ '' FSRQ00, A.FBBH00 FBBH00, A.GSDJH0 GSDJH0, A.BRRYCS ZYCS00, lpad(trim(A.MRN000) || lpad(to_char(A.BRRYCS), 2, '0'), 10, '0') BAHCS0, A.DQKS00 DQKS00, '' SHRXM0, '' SHSJ00, decode(trim(A.YBKH00), '0', '', trim(A.YBKH00)) YBKH00, (select E.YBID00 from BM_BRXXB0 E where E.BRID00 = A.BRID00 and rownum = 1) YBID00, A.YBZYH0 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 RYLB00, null QSRQSJ, null QSRXM0, null HTRQ00, null HTSJ00, null HTR000, null HTRXM0, null LRRQ00, null FSBAJK, null BAWCBZ, '' FSBARQ, decode(A.SJCYRQ, '20991231', '1', '0') BAGS00, '' LXRDH0, 0 HJJE00, '' SCSHRQ, --首次审核时间 '' SCSHR0, --首次审核人 A.DBZMC0 DBZMC0, A.DBZBM0 DBZBM0, '未创建' ZZZT00, '' ZZZTBH, '' GDGDSJ, --规定归档时间 '' ASGD00, --按时归档。 '' SCTJRQ, --首次提交日期 null GDTJRQ, --规定提交日期 null SFZH00, null WTZT00, a.sjcyrq CYRQ00, null BACFBM, null DZGRQ0, --待整改时间 null YZGRQ0 --已整改时间 from ZY_BRXXB0 A where A.BRZT00 in ('2', '20', '3', '4', '5') and not exists (select 1 from BA_BRZYXX B where a.zyid00 = B.lsh000 and b.cyrq00 > To_Char(SYSDATE - 365, 'YYYYMMDD'))