select (select brid00 from bm_brxxb0 br where  br.mrn000=k.bah000 and rownum=1) BRID00,A.ZYID00,decode((select count(1) from LJ_ZRZC00 where nvl(jsfs00,' ')=' ' and zyid00=a.zyid00),0,'0','1') sfljbr,nvl((select max(decode(JSFS00,'',LJID00,-1)) from LJ_ZRZC00 where ZYID00=A.ZYID00 and (nvl(JSRQ00,' ')=' ' or JSFS00 in('完成','强制退出'))),0) as DQLJID,A.BAH000,k.ZYH000,k.brxm00 XM0000,k.brxb00 XB0000,k.rybf00 RYCWH0,k.brcsrq,EXTRACT(year FROM sysdate) - EXTRACT(year FROM to_date(k.brcsrq,'yyyyMMdd')) NL0000,'4' BRZT00,K.RYRQ00,K.RYSJ00,A.RYRQ00 BLRYRQ,A.RYSJ00 BLRYSJ,K.cyrq00 CYRQ00,K.cySJ00 CYSJ00,K.zyysmc SXYSXM,K.ZZYSMC ZZYSXM,k.kzrmc0 ZRYSXM,a.CJYSBH,a.CJYSXM,a.CJRQ00,a.JXBZ00,a.KFBZ00,A.YSZBH0,A.YSZMC0,k.cykb00 as BMBH00,C.BMMC00,A.BLZT00,k.zkysmc as ZKYSXM,k.zkrq00 as ZKRQ00,'' as ZKSJ00,null BQMC00,(select ZDMC00 from BQ_BRZDXX where ZDLB00='3' and ZYID00=a.ZYID00 and Rownum=1) ZDMC00,(select ZDMC00 from BQ_BRZDXX where ZYID00=A.ZYID00 and ZDLB00='a' and rownum<=1) ZYZDMC ,(select f.ZT0000  from ZS_ZDJKBL F where a.ZYID00=f.ZYID00 ) JKZT00,(select f.CZYXM0  from ZS_ZDJKBL F where a.ZYID00=f.ZYID00 ) JKRXM0,(select f.KSRQ00  from ZS_ZDJKBL F where a.ZYID00=f.ZYID00 ) JKKSRQ,(select f.KSSJ00  from ZS_ZDJKBL F where a.ZYID00=f.ZYID00 ) JKKSSJ,GDRQ00,XGGDRQ,trunc(to_date(decode(k.cyrq00,'20991231',to_char(sysdate,'yyyyMMdd'),k.cyrq00),'yyyyMMdd')-to_date(k.RYRQ00,'yyyyMMdd')) as ZYTS00,
null RYLB00,(select ZDMC00 from (select ZYID00,ZDMC00 from BQ_BRZDXX t where ZDFS00=1 AND ZDLB00 = '2' order by plxh00) G where G.ZYID00=A.ZYID00 and ROWNUM <= 1)  RYZDMC
,null DBZBM0,null DBZMC0,k.zycs00,null ZRZYBZ
,decode(nvl(K.SHBZ00,'9'),'9','未创建','0','未提交','1','待签收','2','已审核','3','退回','4',
          '已修订','5','已签收','未提交') ||
    decode(A.SDBASY,'1','(已锁)','') BAZT00,--病案状态
    decode(K.ZZZT00,null,decode(nvl(K.SHBZ00,'9'),'9','未创建','0','未提交','1','待签收'),'2','待整改','3','已入库',
     '4','已借阅','6','已归还','5','已签收','未提交') ZZZT00,--纸质病案状态
     (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,--首次审核人
    K.SHRXM0,--最终审核人
    to_char(to_date(K.SHRQ00||K.SHSJ00,'YYYYMMDD HH24:MI:SS'),'YYYY.MM.DD HH24:MI:SS') SHSJ00,--最终审核时间
    (select to_char(to_date(B.CZRQ00||B.CZSJ00,'YYYYMMDD HH24:MI:SS'),'YYYY.MM.DD HH24:MI:SS') RKRQ00
       from BA_ZZBAGL B where A.ZYID00=B.LSH000 and B.ZZZT00='3' and rownum=1) RKRQ00,--纸质病案入库时间
    to_char(to_date(K.HTRQ00||K.HTSJ00,'YYYYMMDD HH24:MI:SS'),'YYYY.MM.DD HH24:MI:SS') HTRQ00--回退时间
 from ba_brzyxx k,BM_BMBM00 C,zs_blxx01 a where k.cykb00=C.BMBH00(+) and k.lsh000=a.zyid00(+)
 and not exists(select 1 from zy_brxxb0 y where k.lsh000=y.zyid00)