create or replace view vw_ba_cybaxx 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, --出生日期 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) 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, --出院登记时间 --to_char(to_date(D.CYRQ00||D.CYSJ00,'YYYY.MM.DD HH24:MI:SS'),'YYYY-MM-DD HH24:MI:SS') SJCYRQ, nvl2(trim(D.CYRQ00),substr(D.CYRQ00,1,4)||'-'||substr(D.CYRQ00,5,2)||'-'|| substr(D.CYRQ00,7,2)||' '||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, to_char(to_date(A.SJCYRQ,'YYYY.MM.DD'),'YYYY-MM-DD') CYDJRQ, to_char(to_date(D.FSRQ00,'YYYY.MM.DD'),'YYYY-MM-DD') 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, 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)) 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, to_char(to_date(D.QSRQ00||D.QSSJ00,'YYYYMMDD HH24:MI:SS'),'YYYY-MM-DD HH24:MI:SS') QSRQSJ, (select ZWXM00 from BM_YGBM00 where YGBH00=D.QSR000 and rownum=1) QSRXM0, to_char(to_date(D.HTRQ00,'YYYY.MM.DD'),'YYYY-MM-DD') HTRQ00, HTSJ00,HTR000, (select ZWXM00 from BM_YGBM00 where YGBH00=D.HTR000 and D.HTR000<>0 and rownum=1) HTRXM0, to_char(to_date(D.LRRQ00,'YYYY.MM.DD'),'YYYY-MM-DD') 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,--首次审核人 /*nvl2(trim(D.SCSHRQ),substr(D.SCSHRQ,1,4)||'-'||substr(D.SCSHRQ,5,2)||'-'|| substr(D.SCSHRQ,7,2)||' '||D.SCSHSJ,'') SCSHRQ, d.scshr0 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,--按时归档。 ' ' GDGDSJ, ' ' ASGD00, to_char(to_date(D.SCTJRQ,'YYYY.MM.DD'),'YYYY-MM-DD') SCTJRQ, --首次提交日期 to_char(to_date(D.GDTJRQ,'YYYY.MM.DD'),'YYYY-MM-DD') 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 to_char(to_date(DZGRQ0||DZGSJ0,'YYYY.MM.DD HH24:MI:SS'),'YYYY-MM-DD HH24:MI:SS') from BA_ZGSJCF zg where d.LSH000=zg.LSH000) DZGRQ0,--待整改时间 (select to_char(to_date(YZGRQ0||YZGSJ0,'YYYY.MM.DD HH24:MI:SS'),'YYYY-MM-DD HH24:MI:SS') from BA_ZGSJCF zg where d.LSH000=zg.LSH000) YZGRQ0 --已整改时间 from ZY_BRXXB0 A left join BA_BRZYXX D on A.ZYID00=D.LSH000 where 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, --性别 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) 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, --出院登记时间 to_char(to_date(A.SJCYRQ||A.SJCYSJ,'YYYY.MM.DD HH24:MI:SS'),'YYYY-MM-DD HH24:MI:SS') CYRQ00, --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, --首次审核时间 null 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 - 36500, 'YYYYMMDD'))*/ select A.ZYID00, --住院ID A.BRID00, --病人ID A.MRN000, --病案号 A.ZYH000, --住院号 A.XM0000 XM0000, --姓名 A.XB0000 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) 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, --出院登记时间 to_char(to_date(A.SJCYRQ||A.SJCYSJ,'YYYY.MM.DD HH24:MI:SS'),'YYYY-MM-DD HH24:MI:SS') CYRQ00, --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, --首次审核时间 null 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 left join BA_BRZYXX B on a.ZYID00 = B.LSH000 where A.BRZT00 in ('2', '20', '3', '4', '5') and b.CYRQ00 < To_Char(SYSDATE - 36500, 'YYYYMMDD')