CREATE OR REPLACE VIEW SD_HOSPITAL.VW_WLPT_HRJGXX
 (
 ID0000, EVENT_NO, MZZYBZ, REPORT_FORM_NO, TYPE, CODE, IS_ACCEPT, REASON, 
REASON_CONTENT, DEPT_CODE, DEPT_NAME, DOC_CODE, DOC_NAME, ID_NO, ID_TYPE_CODE, 
REPORT_TIME, ORG_CODE, XMID00, DEPT_STD_CODE, DEPT_STD_NAME, YYID00

 )
 AS 
select id0000,decode(mzzybz,'1',a.zyghid, SF_WLPT_GETGHH(a.ZYGHID)) event_no,mzzybz,bgdh00 report_form_no,decode(lx0000,'0','1','2') type,
       bzxmbh code,decode(sfcn00,'0','2','1') is_accept,bcnyy0 reason,bcnyy1 reason_content,
        nvl((select b.KDKSBH from yj_yw0000 b where b.mzzybz='0' and instr(A.XMID00,b.YJDJH0)>0 and (a.ZYGHID=b.ZYGHID or rpad(a.ZYGHID,12,' ')=b.ZYHGHH) and rownum=1),a.jzks00) dept_code,
        nvl((select c.bmmc00 from yj_yw0000 b,bm_bmbm00 c where b.kdksbh=c.bmbh00 and b.mzzybz='0' and instr(A.XMID00,b.YJDJH0)>0 and (a.ZYGHID=b.ZYGHID or rpad(a.ZYGHID,12,' ')=b.ZYHGHH) and rownum=1),(select bmmc00 from bm_bmbm00 where bmbh00=a.jzks00 and rownum=1)) dept_name,
        decode((select count(1) from XT_YYXX00 where YYID00 in('226793')),1,decode(lx0000,'0',(select XKH000 from BM_YGBM00 where a.JZYS00=YGBH00),to_char(jzys00)),to_char(jzys00)) doc_code,
        (select zwxm00 from bm_ygbm00 where ygbh00=a.jzys00 and rownum=1) doc_name,
        sfzjh0 id_no,sfzjlx id_type_code, to_char(to_date(qrrq00||' '||qrsj00,'yyyyMMdd hh24:mi:ss'),'yyyy-MM-dd hh24:mi:ss') report_time,jgbh00 ORG_CODE,xmid00,
        --(select BZKSH0 from wlpt_hrksdz where a.JZKS00=BYKSH0 and SFYX00='1' and rownum=1) dept_std_code,
        --(select BZKSMC from wlpt_hrksdz where a.JZKS00=BYKSH0 and SFYX00='1' and rownum=1) dept_std_name,
        nvl((select c.BZKSH0 from yj_yw0000 b,wlpt_hrksdz c where b.mzzybz='0' and instr(A.XMID00,b.YJDJH0)>0 and (a.ZYGHID=b.ZYGHID or rpad(a.ZYGHID,12,' ')=b.ZYHGHH) and b.KDKSBH=c.BYKSH0 and c.SFYX00='1' and rownum=1),(select BZKSH0 from wlpt_hrksdz where a.JZKS00=BYKSH0 and SFYX00='1' and rownum=1)) dept_std_code,
        nvl((select c.BZKSMC from yj_yw0000 b,wlpt_hrksdz c where b.mzzybz='0' and instr(A.XMID00,b.YJDJH0)>0 and (a.ZYGHID=b.ZYGHID or rpad(a.ZYGHID,12,' ')=b.ZYHGHH) and b.KDKSBH=c.BYKSH0 and c.SFYX00='1' and rownum=1),(select BZKSMC from wlpt_hrksdz where a.JZKS00=BYKSH0 and SFYX00='1' and rownum=1)) dept_std_name,
        (select YYID00 from BM_BMBM00 where a.JZKS00=BMBH00) YYID00
 from WLPT_HRJG00 a
where a.SFSB00<>'1'
  and a.MZZYBZ='0'
  and ( A.SFCN00='1'
     or (A.SFCN00='0' and  (exists(select 1 from YJ_YW0000 where XMZT00 in ('3','4')
     and instr(A.XMID00,YJDJH0)>0 and (a.ZYGHID=ZYGHID or rpad(a.ZYGHID,12,' ')=ZYHGHH))))
     )
  and nvl(a.SBSBCS,0)<=5
union all
select id0000,decode(mzzybz,'1',a.zyghid, SF_WLPT_GETGHH(a.ZYGHID)) event_no,mzzybz,bgdh00 report_form_no,decode(lx0000,'0','1','2') type,bzxmbh code,decode(sfcn00,'0','2','1') is_accept,bcnyy0 reason,bcnyy1 reason_content,
        jzks00 dept_code,(select bmmc00 from bm_bmbm00 where bmbh00=a.jzks00 and rownum=1 ) dept_name,
        decode((select count(1) from XT_YYXX00 where YYID00 in('226793')),1,decode(lx0000,'0',(select XKH000 from BM_YGBM00 where a.JZYS00=YGBH00),to_char(jzys00)),to_char(jzys00)) doc_code,(select zwxm00 from bm_ygbm00 where ygbh00=a.jzys00 and rownum=1) doc_name,
        sfzjh0 id_no,sfzjlx id_type_code,to_char(to_date(qrrq00||' '||qrsj00,'yyyyMMdd hh24:mi:ss'),'yyyy-MM-dd hh24:mi:ss') report_time,jgbh00 ORG_CODE,xmid00,
        (select BZKSH0 from wlpt_hrksdz where a.JZKS00=BYKSH0 and SFYX00='1' and rownum=1) dept_std_code,
        (select BZKSMC from wlpt_hrksdz where a.JZKS00=BYKSH0 and SFYX00='1' and rownum=1) dept_std_name,
        (select YYID00 from BM_BMBM00 where a.JZKS00=BMBH00) YYID00
 from WLPT_HRJG00 a
where a.SFSB00<>'1'
  and a.MZZYBZ='1'
  and ( A.SFCN00='1'
     or (A.SFCN00='0' and  (exists(select 1 from YJ_YW0000 where XMZT00 in('3','4') and instr( A.XMID00,trim(YZCFID))>0 and (a.ZYGHID=ZYGHID or rpad(a.ZYGHID,12,' ')=ZYHGHH))))
     )
  and nvl(a.SBSBCS,0)<=5
/