create or replace view vw_bq_kszyfatj as
select a.ZYID00,a.HLDLB0,
       a.BQH000,
       a.ZYH000,
       a.BRXM00,
       a.BRXB00,
       a.BRNL00,
       (select nvl(d.BRLXDH, d.BRDH00) from BM_BRXXB0 d where d.BRID00 = b.BRID00) BRDH00,
       a.KSMC00,
       b.SJRYRQ RYRQ00,
       b.SJCYRQ,
       a.JLRQ00,
       decode(b.SJCYRQ, '20991231', '', b.SJCYRQ) CYRQ00,
       (to_char(greatest(decode(nvl(b.SJCYRQ, '20991231'),'20991231',to_date(to_char(sysdate, 'yyyyMMdd'), 'yyyyMMdd'),to_date(b.sjcyrq, 'yyyymmdd')) - to_date(b.sjryrq, 'yyyymmdd'), 1)) || '天') ZYTS00,
       (select c.MC0000 from BM_TYZD00 c, BM_YGBM00 d where c.ZDMC00 like '%员工信息_专业技术职称%' and c.NBBH00 = d.ZYJSZC and d.YGBH00 = b.ZGHS00) ZHZC00,
       a.HLDMC0,
       a.HLPJID,
       (select decode(BLZT00, '3', '归档', '未归档') from ZS_BLXX01 where ZYID00 = b.ZYID00 AND ROWNUM = 1) BLZTMC,
       a.ZYZHMC,
       (select sf_bq_rep_repeat_str(to_char(wmsys.wm_concat(substr(HLLBMC,3))),',')
          from BQ_ZYHLLB x
          join BQ_ZYHLZY y            
            on x.HLLBID = y.HLLBID
          join BQ_BRHLXX z
            on y.HLLBID=z.HLLBID
         where x.LBMC02='中医护理技术'
           and y.KJID00=2
           and z.ZYID00=a.zyid00
           and z.HLPJID=a.hlpjid
           and x.HLDLB0=a.HLDLB0
           and z.TZNR00='1') ZYHLJS--已勾选中医护理技术

  from BQ_BRHLPJ a, ZY_BRXXB0 b
 where a.ZYID00 = b.ZYID00
   and a.YXBZ00 = '1'