CREATE OR REPLACE VIEW SD_HOSPITAL.VW_BQ_JCYYSQ_NEW ( BRID00, ZYGHID, YJKSBH, YJDJH0, ZYHGHH, BRXM00, YEXM00, BRXB00, CSRQ00, BRNL00, ZLXMID, ZLXMJC, ZLXMJC1, KDYSXM, BMMC00, KDKSBH, KDRQ00, KDSJ00, BWMC00, JZBZ00, XMZT00, SFDJH0, CXDJH0, KDBQ00, SQID00, YYBZ00, YYRQ00, YYSD00, XSYYSD, JCSQDH, JKYYH0, HBDJH0, JCZID0, JCZMC0, YYZT00, DYZT00, JCZYSX, BDBZ00, YJKSMC, PSZTMC, CH0000, MZZYBZ, TX0000, SCDYXM, SCDYSJ, DJRQSJ, DDRS00, SM0000, TSYY00, YYSJ00, YJDJDY, DYTS00, YYJCDZ, SFFR00, DCNYJ0 ) AS select Y.BRID00, --病人id Y.ZYGHID, Y.YJKSBH, --医技科室编号 Y.YJDJH0, --医技单据号 Y.ZYHGHH, --住院挂号号 Y.BRXM00, --病人姓名 Y.YEXM00, --婴儿姓名 Y.XB0000, --性别 Y.CSRQ00, --出生日期 SF_BQ_CSRQTONL(Y.CSRQ00) BRNL00, --病人年龄 Y.ZLXMID, --诊疗项目id substr(SF_YJ_HBDJHMC(Y.YJDJH0),1,250) ZLXMJC , --诊疗项目简称 Y.ZLXMJC ZLXMJC1, Y.KDYSXM, --开单医生姓名 B.BMMC00, --开单科室名称 Y.KDKSBH, --开单科室编号 Y.KDRQ00, --开单日期 Y.KDSJ00, Y.BWMC00, --部位名称 DECODE(Y.JZBZ00, NULL, '普通', 'Y', '急诊', 'N', '普通', '普通') JZBZ00, --DECODE(Y.XMZT00,'0','已申请','1','已计价','2','已收费','3','已执行','4','已报告','5','疑退','6','已作废','7','已退费') XMZT00, Y.XMZT00, Y.SFDJH0, Y.CXDJH0, Y.KDBQ00, nvl(Y.SQID00,0) SQID00, decode(nvl(Y.YYID00,0),0,'','已预约') YYBZ00 , --预约标志 C.YYRQ00, --(select e.yysj00 from YJ_YYBRB0 e where c.yyid00 =e.yyid00 and e.yjksbh in ('502','499')) YYSD00, (select e.yysd00 from YJ_YYBRB0 e where c.yyid00 =e.yyid00 and (e.yjksbh ='502'or e.yjksbh='505' or (e.yjksbh='499' and exists(select 1 from BM_ZLZD00 F WHERE F.ZLXMID=Y.ZLXMID and F.JCLBID=3))) ) YYSD00, DECODE((SELECT F.JCZID0 FROM BM_ZLZD00 F WHERE F.ZLXMID=Y.ZLXMID),'20',yysd00||'来等候', '21',C.YYSJ00||'提早30分钟到', '22',substr(yysd00,1,5)||'来等候','23',C.YYSJ00||'提早30分钟到','24',C.YYSJ00||'来等候', '25',C.YYSJ00||'来等候', decode((select a.KSPBSJ from YJ_KSPBB0 a,yj_kspbhb b where a.kspbid=b.kspbid and c.pbhbid=b.pbhbid and rownum=1),'上午', least(to_char(substr(yysd00,1,5)),to_char('10:00')),substr(yysd00,1,5))||'来等候') XSYYSD, --显示时段 C.JCSQDH, C.JKYYH0, nvl(Y.HBDJH0,Y.YJDJH0) HBDJH0, (select JCZID0 from BM_ZLZD00 where ZLXMID=Y.ZLXMID) JCZID0, (select F.YYXMMC from BM_ZLZD00 E,YJ_YYZLZD F where E.ZLXMID=Y.ZLXMID and E.JCZID0=F.XMWHID and XMWHID>0) JCZMC0, nvl(c.YYZT00,'-1') YYZT00, decode(nvl(C.DYCS00,0),0,'未打印','已打印') DYZT00, --打印状态 SF_YJ_GetJCZYSX(Y.YJDJH0) JCZYSX, decode(nvl(C.DYCS00,0),0,'','补打') BDBZ00, (select BMMC00 from BM_BMBM00 where BMBH00=Y.YJKSBH) YJKSMC, decode(BBZT00,'1','收到影像','2','初步报告','3','确认报告','0','申请','') PSZTMC, Y.CH0000, DECODE(Y.MZZYBZ,0,'门诊',1,'住院',Y.MZZYBZ) mzzybz, decode(y.yjksbh,'531','延时30分钟请重新预约,不便之处,请予谅解!',null), (select ZWXM00 from BM_YGBM00 D,YJ_YYDYJL E where E.YJDJH0=Y.YJDJH0 and YGBH00=E.DYRBH0 and E.DYCS00=1 and E.YYID00=C.YYID00) DYRXM0, (select to_char(to_date(DYRQ00||DYSJ00,'YYYYMMDDHH24:MI:SS'),'YYYY.MM.DD HH24:MI:SS') from YJ_YYDYJL where YJDJH0=Y.YJDJH0 and DYCS00=1 and YYID00=C.YYID00) DYRQSJ, to_char(to_date(C.DJRQ00||C.DJSJ00,'YYYYMMDDHH24:MI:SS'),'YYYY.MM.DD HH24:MI:SS') DJRQSJ, (select count(*) from YJ_YYBRB0 where YYRQ00=C.YYRQ00 and PBHBID=C.PBHBID and YYH0000 AND Y.MZZYBZ='1' and Y.BRID00=C.BRID00 and Y.KDKSBH=B.BMBH00(+) and Y.YJDJH0=C.YJDJH0 and Y.YYID00=C.YYID00 and (nvl(HBDJH0,0)=0 or HBDJH0=Y.YJDJH0) and ( not exists (select 1 from YJ_YYBRB0 e where c.yyid00 =e.yyid00 and e.yjksbh ='502' and yysj00 is not null and e.JKYYH0 is not null) ) and ( not exists (select 1 from YJ_YYBRB0 e where c.yyid00 =e.yyid00 and e.yjksbh ='499' and yysj00 is not null and e.JKYYH0 is not null) ) and ( not exists (select 1 from YJ_YYBRB0 e where c.yyid00 =e.yyid00 and e.yjksbh ='505' and yysj00 is not null and e.JKYYH0 is not null) ) --20200722胃镜预约后不显示，郑力需求。 and nvl(Y.SQID00,0) > 0 and Y.CXSL00=0 and not exists (select 1 from YJ_YW0000 where ZYGHID=Y.ZYGHID and CXDJH0=Y.CXDJH0) and Y.YJKSBH<>Y.KDKSBH --and not (Y.YJKSBH=504 and Y.KDKSBH=488) and Y.YJLRBZ<>'Y' union all select Y.BRID00, --病人id Y.ZYGHID, Y.YJKSBH, --医技科室编号 Y.YJDJH0, --医技单据号 Y.ZYHGHH, --住院挂号号 Y.BRXM00, --病人姓名 Y.YEXM00, --婴儿姓名 Y.XB0000, --性别 Y.CSRQ00, --出生日期 SF_BQ_CSRQTONL(Y.CSRQ00) BRNL00, --病人年龄 Y.ZLXMID, --诊疗项目id substr(SF_YJ_HBDJHMC(Y.YJDJH0),1,250) ZLXMJC , --诊疗项目简称 Y.ZLXMJC ZLXMJC1, Y.KDYSXM, --开单医生姓名 (select BMMC00 from BM_BMBM00 where BMBH00=Y.KDKSBH) BMMC00, --开单科室名称 Y.KDKSBH, --开单科室编号 Y.KDRQ00, --开单日期 Y.KDSJ00, Y.BWMC00, --部位名称 DECODE(Y.JZBZ00, NULL, '普通', 'Y', '急诊', 'N', '普通', '普通') JZBZ00, --DECODE(Y.XMZT00,'0','已申请','1','已计价','2','已收费','3','已执行','4','已报告','5','疑退','6','已作废','7','已退费') XMZT00, Y.XMZT00, Y.SFDJH0, Y.CXDJH0, Y.KDBQ00, nvl(Y.SQID00,0) SQID00, decode(nvl(Y.YYID00,0),0,'','已预约') YYBZ00 , --预约标志 '' YYRQ00, '' YYSD00, '' XSYYSD, '' JCSQDH, '' JKYYH0, nvl(Y.HBDJH0,Y.YJDJH0) HBDJH0, (select JCZID0 from BM_ZLZD00 where ZLXMID=Y.ZLXMID) JCZID0, (select F.YYXMMC from BM_ZLZD00 E,YJ_YYZLZD F where E.ZLXMID=Y.ZLXMID and E.JCZID0=F.XMWHID and XMWHID>0) JCZMC0, decode(nvl(Y.SM0000,' '),' ','-1','3') YYZT00, decode(nvl(Y.YJDJDY,0),0,'未打印','已打印') DYZT00, --打印状态 SF_YJ_GetJCZYSX(Y.YJDJH0) JCZYSX, ' ' BDBZ00, (select BMMC00 from BM_BMBM00 where BMBH00=Y.YJKSBH) YJKSMC, decode(BBZT00,'1','收到影像','2','初步报告','3','确认报告','0','申请','') PSZTMC, Y.CH0000, DECODE(Y.MZZYBZ,0,'门诊',1,'住院',Y.MZZYBZ) mzzybz, null tx0000, (select ZWXM00 from BM_YGBM00 D,YJ_YYDYJL E where E.YJDJH0=Y.YJDJH0 and YGBH00=E.DYRBH0 and E.DYCS00=1 and rownum=1) DYRXM0, (select to_char(to_date(DYRQ00||DYSJ00,'YYYYMMDDHH24:MI:SS'),'YYYY.MM.DD HH24:MI:SS') from YJ_YYDYJL where YJDJH0=Y.YJDJH0 and DYCS00=1 and rownum=1) DYRQSJ, ' ' DJRQSJ, 0 DDRS00, Y.SM0000, (case when yjksbh ='503' then '请到肺功能室预约!' when yjksbh ='505' then '请持本条码至内镜室预约!'else '请到检查科室预约!' end) TSYY00, '' YYSJ00, YJDJDY, (case when yjksbh ='502' then '病人'|| Y.BRXM00||'，'||'B超科室项目'||Y.ZLXMJC||'未预约，' when yjksbh ='499' and exists(select 1 from BM_ZLZD00 F WHERE F.ZLXMID=Y.ZLXMID and F.JCLBID=3) then '病人'|| Y.BRXM00||'，'||'放射科磁共振项目'||Y.ZLXMJC||'未预约，' end) DYTS00, b.yybmdz yyjcdz, decode(Y.sffr00,'1','发热','') sffr00, decode(Y.DCNYJ0,'1','多耐','') DCNYJ0 from YJ_YW0000 Y,bm_bmbm00 b where Y.YJKSBH<>0 AND Y.MZZYBZ='1' and nvl(Y.YYID00,0)=0 and exists(select 1 from BM_ZLZD00 where ZLXMID=Y.ZLXMID and nvl(JCZID0,0)>0) and not exists (select 1 from YJ_YW0000 where ZYGHID=Y.ZYGHID and CXDJH0=Y.CXDJH0) and y.CXSL00=0 and Y.YJKSBH<>Y.KDKSBH and y.yjksbh=b.bmbh00 and not (Y.YJKSBH=504 and Y.KDKSBH=488) and Y.YJLRBZ<>'Y' /