PROMPT .正在创建函数 SF_XSPXS_JY... create or replace function SF_XSPXS_JY( p_XSPBH0 IN NUMBER --显示屏编号 )RETURN VARCHAR2 IS --返回该显示屏的显示内容 用'@','%','#'隔开 --如'0@123% 张三 普01请进#' --表示向地址123的八字显示屏发送" 张三 普01请进" --'A@BBB%CCCCCCCCC#'其中A为屏类型 --'0'八字(首屏闪烁)'1'八字'2'单行13字'3'双行13字'Z'不用发送 --'4'药房液晶显示器 '5'分诊显示器,'J'检验显示屏 --BBB为显示屏地址，CCCCCCCCC为发送的内容 v_return BM_XSPDM0.BZ0000%TYPE; v_FLAG00 BM_XSPDZ0.XSPLB0%TYPE; v_XSPDZ0 BM_XSPDZ0.XSPDZ0%TYPE; v_CKBH00 BM_CKBM00.CKBH00%TYPE; v_XSFSLB BM_XSPDZ0.XSFSLB%TYPE; v_BRXM00 VARCHAR2(20); v_CFDLSJ NUMBER(5); v_XSPYSL NUMBER(2); i NUMBER(3); v_GHYSXM VARCHAR2(20); v_BMBH00 BM_BMBM00.BMBH00%TYPE; v_JYXMMC varchar2(20); v_TYBZ00 Char(1); v_FYPYBZ BM_XSPDM0.BZ0000%TYPE; v_count number(5); v_qsjl number(5); tsql varchar2(200); v_XYDLS number(3); v_TYDLS number(3); v_ZDLS number(3); --Person Date Comments --chenyw 2011.11.16 创建显示屏函数 -- chenyw 2011.12.05 限制大显示屏幕只能显示先'UT'和'BC'两台仪器的项目 by LIS-20111130-001 -- chenyw 2012.01.06 把南平市第一医院和长乐市医院的过程整和下 by LIS-20111111-001 -- chenyw 2012.07.06 南平市第一医院要求左边的屏幕要填满来 -- chenyw 2012.08.20 长乐检验项目情况显示要求显示今天出报告的信息 by LIS-20120820-002 -- jinfl 2013.07.18 福州六院医院显示屏 归档 JYPDJH-20130306-001 -- chenyw 2015.12.01 显示屏幕的名字第二位是否改成* for JYPDJH-20151201-001 -- 说明:BM_XSPDZ0 XSFSLB：0 ：代表长乐的窗口显示屏显示 -- 1 ：南平大屏幕采集屏幕显示采集信息 -- 2 : 南平大屏幕准备屏幕显示准备信息 -- 3 : 南平大屏幕病人检验信息显示 -- 4 : 长乐大屏幕1检验病人信息显示 -- 5 ： 长乐大屏幕2检验病人信息显示 -- 6： 代表长乐的窗口显示屏显示 -- 7: 费科医院显示屏幕 begin select XSPLB0,XSPDZ0,XSFSLB into v_FLAG00,v_XSPDZ0,v_XSFSLB from BM_XSPDZ0 where XSPBH0=p_XSPBH0 and XSPXH0='0' and rownum=1; if v_FLAG00='J' then if v_XSFSLB='0' then --长乐呼叫检验显示屏 v_return :=''; begin select CKBH00 into v_CKBH00 from BM_CKBM00 where DQXSP0=p_XSPBH0 and FLAG00='J' and ZT0000='0' and DQXSP0=p_XSPBH0 and rownum=1; EXCEPTION WHEN NO_DATA_FOUND THEN v_CKBH00:=-1; v_return:=v_return||'长乐市第一医院祝您健康'; return v_return; end; declare cursor c_getCKBH is select CKBH00 into v_CKBH00 from BM_CKBM00 where DQXSP0=p_XSPBH0 and FLAG00='J' and ZT0000='0' and DQXSP0=p_XSPBH0 order by CKBH00; begin for v_getCKBH in c_getCKBH loop declare cursor c_jybrdl is select a.DLH000,a.BRID00,b.ckmc00,decode(JHBZ00,'1','采集','3','准备','7','采集','8,','准备') ZT0000 from JY_PDJHB0 a,BM_CKBM00 b where b.ckbh00=a.cyckbh and jhbz00 in ('1','3','7','8') and phrq00=to_char(sysdate,'YYYYMMDD') and CYCKBH = v_getCKBH.CKBH00 order by decode(jhbz00,'7','1',JHBZ00),djsj00; begin for v_jybrdl in c_jybrdl loop begin select trim(substrb(BRXM00,1,8)) into v_BRXM00 from BM_BRXXB0 where BRID00=v_jybrdl.BRID00; exception when NO_DATA_FOUND then v_BRXM00:=''; end; v_BRXM00:=nvl(v_BRXM00,''); select SF_JY_GETPDJHBRXM(v_BRXM00,'0') into v_BRXM00 from dual; v_BRXM00:=rpad(lpad(v_BRXM00,(8+lengthb(trim(v_BRXM00)))/2,' '),8,' '); v_return:=v_return||'请'||v_jybrdl.DLH000||'号'||v_BRXM00||'到'||substrb(nvl(v_jybrdl.ckmc00,''),1,8)||chr(13); end loop; end; end loop; end; v_return:=v_return||'%'; elsif v_XSFSLB='6' then ----南平呼叫检验显示屏 v_return :=''; begin select CKBH00 into v_CKBH00 from BM_CKBM00 where DQXSP0=p_XSPBH0 and FLAG00='J' and ZT0000='0' and DQXSP0=p_XSPBH0 and rownum=1; EXCEPTION WHEN NO_DATA_FOUND THEN v_CKBH00:=-1; v_return:=v_return||'南平市一医院祝您健康'; return v_return; end; declare cursor c_getCKBH is select CKBH00 into v_CKBH00 from BM_CKBM00 where DQXSP0=p_XSPBH0 and FLAG00='J' and ZT0000='0' and DQXSP0=p_XSPBH0 order by CKBH00; begin for v_getCKBH in c_getCKBH loop declare cursor c_jybrdl is select a.DLH000,a.BRID00,b.ckmc00,decode(JHBZ00,'1','采集','3','采集','8,','准备') ZT0000 from JY_PDJHB0 a,BM_CKBM00 b where b.ckbh00=a.cyckbh and jhbz00 in ('1','3','8') and phrq00=to_char(sysdate,'YYYYMMDD') and CYCKBH = v_getCKBH.CKBH00 order by decode(jhbz00,'3','1',JHBZ00); begin for v_jybrdl in c_jybrdl loop begin select trim(substrb(BRXM00,1,8)) into v_BRXM00 from BM_BRXXB0 where BRID00=v_jybrdl.BRID00; exception when NO_DATA_FOUND then v_BRXM00:=''; end; v_BRXM00:=nvl(v_BRXM00,''); select SF_JY_GETPDJHBRXM(v_BRXM00,'0') into v_BRXM00 from dual; v_BRXM00:=rpad(lpad(v_BRXM00,(8+lengthb(trim(v_BRXM00)))/2,' '),8,' '); v_return:=v_return||'请'||v_jybrdl.DLH000||'号'||v_BRXM00||'到'||substrb(nvl(v_jybrdl.ckmc00,''),1,8)||trim(v_jybrdl.zt0000)||chr(13); end loop; end; end loop; end; v_return:=v_return||'%'; elsif v_XSFSLB='1' then --南平检验窗口显示屏 v_return :=''; declare cursor c_getCKBH is select CKBH00 into v_CKBH00 from BM_CKBM00 where FLAG00='J' and ZT0000='0' order by CKBH00; begin for v_getCKBH in c_getCKBH loop declare cursor c_jybrdl is select a.DLH000,a.BRID00,b.ckmc00 from JY_PDJHB0 a,BM_CKBM00 b where b.ckbh00=a.cyckbh and jhbz00 in ('1','3') and phrq00=to_char(sysdate,'YYYYMMDD') and CYCKBH = v_getCKBH.CKBH00 ; begin for v_jybrdl in c_jybrdl loop begin select trim(substrb(BRXM00,1,8)) into v_BRXM00 from BM_BRXXB0 where BRID00=v_jybrdl.BRID00; exception when NO_DATA_FOUND then v_BRXM00:=''; end; v_BRXM00:=nvl(v_BRXM00,''); select SF_JY_GETPDJHBRXM(v_BRXM00,'0') into v_BRXM00 from dual; v_BRXM00:=rpad(lpad(v_BRXM00,(8+lengthb(trim(v_BRXM00)))/2,' '),8,' '); v_return:=v_return||'请'||v_jybrdl.DLH000||'号'||v_BRXM00||'到'||substrb(nvl(v_jybrdl.ckmc00,''),1,8)||'采集'||chr(13); end loop; end; end loop; end; v_return:=v_return||'%'; elsif v_XSFSLB='2' then v_return :=''; v_ZDLS:=12; begin select count(*) into v_XYDLS from BM_CKBM00 a, JY_DLLX00 b where a.cklxid=b.dllxid and FLAG00='J' and ZT0000='0' and a.cklxid=1; exception when others then v_XYDLS:=0; end; begin select count(*) into v_TYDLS from BM_CKBM00 a, JY_DLLX00 b where a.cklxid=b.dllxid and FLAG00='J' and ZT0000='0' and a.cklxid=2; exception when others then v_TYDLS:=0; end; v_return:=SF_JY_XSPDDXX(v_XYDLS,v_TYDLS,v_ZDLS); /*i:=0; declare cursor c_CKDLXX is select a.cklxid,count(*) SL0000 from BM_CKBM00 a, JY_DLLX00 b where a.cklxid=b.dllxid and FLAG00='J' and ZT0000='0' group by CKLXID; begin for v_CKDLXX in c_CKDLXX loop i:=i+v_CKDLXX.SL0000; declare cursor c_JHZBDL is select * from (select DLH000,BRID00,brxm00,decode(DLHMYX,'1','(急诊)','') JZBZ00 from JY_PDJHB0 where jhbz00 in ('0','4') and dllxid=v_CKDLXX.cklxid and phrq00=to_char(sysdate,'YYYYMMDD') order by DLHMYX desc,to_number(DLH000)) where rownum<=v_CKDLXX.SL0000; begin for v_JHZBDL in c_JHZBDL loop v_BRXM00:=nvl(v_JHZBDL.BRXM00,''); v_BRXM00:=rpad(lpad(v_BRXM00,(8+lengthb(trim(v_BRXM00)))/2,' '),8,' '); v_return:=v_return||'请'||v_JHZBDL.DLH000||'号'||v_BRXM00||'准备'||v_JHZBDL.JZBZ00||''||chr(13); end loop; end; end loop; end; v_return:=v_return||'%';*/ elsif v_XSFSLB='3' then --南平显示病人信息 v_return :=''; i:=0; select seq_jy_jyxsxx_jlid00.nextval into v_qsjl from dual; select count(*) into v_count from (select XL,brxm00,XB0000,NL0000,ZLXMMC,JYZT00,ZYZBID,nvl(ZBXMMC,'未知项目') ZBXMMC from (select rownum XL ,a.brxm00,decode(a.xb0000,'M','男','F','女','其他') XB0000, a.NL0000||decode(a.NLDW00,'Y','岁','M','月','D','天') NL0000, b.zlxmjc ZLXMMC,decode(a.jgzt00,'6','可打印','检验中') JYZT00 ,B.ZLXMID,B.ZYZBID, (select substr(zyzbmc,1,6)||'项目' from BM_JYZYZB where zyzbid=b.zyzbid) ZBXMMC from jy_yw0000 a ,BM_ZLZD00 b where a.zlxmid=b.zlxmid and a.jgzt00 in ('1','2','4','5','6') and a.mzzybz='0' and a.sftj00='N' and a.sqrq00>=to_char(sysdate-360,'YYYYMMDD') ) ); if (v_qsjl)*12>=v_count+12 then return '重置'; end if; declare cursor c_JYYWXX is select * from ( select XL, brxm00 ,XB0000,NL0000,ZLXMMC,JYZT00,ZYZBID,nvl(ZBXMMC,'未知项目') ZBXMMC from (select rownum XL ,a.brxm00,decode(a.xb0000,'M','男','F','女','其他') XB0000, a.NL0000||decode(a.NLDW00,'Y','岁','M','月','D','天') NL0000, b.zlxmjc ZLXMMC,decode(a.jgzt00,'6','可打印','检验中') JYZT00 ,B.ZLXMID,B.ZYZBID, (select substr(zyzbmc,1,6)||'项目' from BM_JYZYZB where zyzbid=b.zyzbid) ZBXMMC from jy_yw0000 a ,BM_ZLZD00 b where a.zlxmid=b.zlxmid and a.jgzt00 in ('1','2','4','5','6') and a.mzzybz='0' and a.sftj00='N' and a.sqrq00>=to_char(sysdate-360,'YYYYMMDD') ) ) where XL between (v_qsjl-1)*12+1 and v_qsjl*12 order by XL; begin for v_JYYWXX in c_JYYWXX loop v_BRXM00:=nvl(trim(v_JYYWXX.brxm00),''); select SF_JY_GETPDJHBRXM(v_BRXM00,'0') into v_BRXM00 from dual; v_BRXM00:=rpad(lpad(v_BRXM00,(8+lengthb(trim(v_BRXM00)))/2,' '),8,' '); v_return:=v_return||v_BRXM00||' '||trim(v_JYYWXX.ZBXMMC)||' '||trim(v_JYYWXX.JYZT00)||' '||chr(13); end loop; end; v_return:=v_return||'%'; elsif v_XSFSLB='4' then --长乐市医院屏幕1 v_return :=''; i:= 0; declare cursor c_JYYWXX is select XL,brxm00,XB0000,NL0000,ZLXMMC,JYZT00,ZYZBID,nvl(ZBXMMC,'未知项目') ZBXMMC from (select rownum XL,brxm00,XB0000,NL0000,ZLXMMC,JYZT00,ZYZBID,nvl(ZBXMMC,'未知项目') ZBXMMC from (select a.brxm00,decode(a.xb0000,'M','男','F','女','其他') XB0000, a.NL0000||decode(a.NLDW00,'Y','岁','M','月','D','天') NL0000, b.zlxmjc ZLXMMC,decode(a.jgzt00,'8','可打印','9','可打印') JYZT00 ,B.ZLXMID,B.ZYZBID, (select zlxmjc from bm_zlzd00 where zlxmid=a.zlxmid) ZBXMMC from jy_yw0000 a ,BM_ZLZD00 b where a.zlxmid=b.zlxmid and a.yqdh00 in ('UT','BC') and a.jgzt00 in ('8','9') and a.mzzybz='0' and a.sftj00='N' and a.CBGRQ0>=to_char(sysdate,'YYYYMMDD') order by a.cbgrq0||' '||a.cbgsj0 desc ) )where XL<=20; begin for v_JYYWXX in c_JYYWXX loop i:=i+1; v_BRXM00:=nvl(trim(v_JYYWXX.brxm00),''); select SF_JY_GETPDJHBRXM(v_BRXM00,'0') into v_BRXM00 from dual; v_BRXM00:=rpad(lpad(v_BRXM00,(8+lengthb(trim(v_BRXM00)))/2,' '),8,' '); v_JYXMMC:=trim(substrb(v_JYYWXX.ZBXMMC,1,20)); v_JYXMMC:=rpad(lpad(v_JYXMMC,(20+lengthb(trim(v_JYXMMC)))/2,' '),20,' '); if mod(i,4)=0 then v_return:=v_return||v_BRXM00||' '||v_JYXMMC||' '||trim(v_JYYWXX.JYZT00)||' '||chr(13)||'%'; else v_return:=v_return||v_BRXM00||' '||v_JYXMMC||' '||trim(v_JYYWXX.JYZT00)||' '||chr(13); end if; end loop; end; if mod(i,4)>0 then v_return:=v_return||'%'; end if; elsif v_XSFSLB='5' then --长乐市医院屏幕2 v_return :=''; i:= 0; declare cursor c_JYYWXX is select XL,brxm00,XB0000,NL0000,ZLXMMC,JYZT00,ZYZBID,nvl(ZBXMMC,'未知项目') ZBXMMC from (select rownum XL,brxm00,XB0000,NL0000,ZLXMMC,JYZT00,ZYZBID,nvl(ZBXMMC,'未知项目') ZBXMMC from (select a.brxm00,decode(a.xb0000,'M','男','F','女','其他') XB0000, a.NL0000||decode(a.NLDW00,'Y','岁','M','月','D','天') NL0000, b.zlxmjc ZLXMMC,decode(a.jgzt00,'8','可打印','9','可打印') JYZT00 ,B.ZLXMID,B.ZYZBID, (select zlxmjc from bm_zlzd00 where zlxmid=a.zlxmid) ZBXMMC from jy_yw0000 a ,BM_ZLZD00 b where a.zlxmid=b.zlxmid and yqdh00 in ('UT','BC') and a.jgzt00 in ('8','9') and a.mzzybz='0' and a.sftj00='N' and a.CBGRQ0>=to_char(sysdate,'YYYYMMDD') order by a.cbgrq0||' '||a.cbgsj0 desc ) )where XL>=21 and XL<=40; begin for v_JYYWXX in c_JYYWXX loop i:=i+1; v_BRXM00:=nvl(trim(v_JYYWXX.brxm00),''); select SF_JY_GETPDJHBRXM(v_BRXM00,'0') into v_BRXM00 from dual; v_BRXM00:=rpad(lpad(v_BRXM00,(8+lengthb(trim(v_BRXM00)))/2,' '),8,' '); v_JYXMMC:=trim(substrb(v_JYYWXX.ZBXMMC,1,20)); v_JYXMMC:=rpad(lpad(v_JYXMMC,(20+lengthb(trim(v_JYXMMC)))/2,' '),20,' '); if mod(i,4)=0 then v_return:=v_return||v_BRXM00||' '||v_JYXMMC||' '||trim(v_JYYWXX.JYZT00)||' '||chr(13)||'%'; else v_return:=v_return||v_BRXM00||' '||v_JYXMMC||' '||trim(v_JYYWXX.JYZT00)||' '||chr(13); end if; end loop; end; if mod(i,4)>0 then v_return:=v_return||'%'; end if; elsif v_XSFSLB='7' then --费科医院显示屏幕显示 v_return :=''; begin select CKBH00 into v_CKBH00 from BM_CKBM00 where DQXSP0=p_XSPBH0 and FLAG00='J' and ZT0000='0' and rownum=1; EXCEPTION WHEN NO_DATA_FOUND THEN v_CKBH00:=-1; v_return:=v_return||'肺科医院祝您健康'; return v_return; end; declare cursor c_getCKBH is select CKBH00 into v_CKBH00 from BM_CKBM00 where DQXSP0=p_XSPBH0 and FLAG00='J' and ZT0000='0' order by CKBH00; begin i:=0; for v_getCKBH in c_getCKBH loop declare cursor c_jybrdl is select DLH000,BRID00,ckmc00,jhbz00 from (select a.DLH000,a.BRID00,b.ckmc00,jhbz00 from JY_PDJHB0 a,BM_CKBM00 b where b.ckbh00=a.cyckbh and jhbz00 in ('1','2') and phrq00=to_char(sysdate,'YYYYMMDD') and CYCKBH = v_getCKBH.CKBH00 order by JHBZ00 ,to_number(dlh000) desc ) where rownum<=3 order by JHBZ00 desc,to_number(dlh000) ; begin for v_jybrdl in c_jybrdl loop begin select trim(substrb(BRXM00,1,8)) into v_BRXM00 from BM_BRXXB0 where BRID00=v_jybrdl.BRID00; exception when NO_DATA_FOUND then v_BRXM00:=''; end; i:=i+1; v_BRXM00:=nvl(v_BRXM00,''); select SF_JY_GETPDJHBRXM(v_BRXM00,'0') into v_BRXM00 from dual; v_BRXM00:=rpad(lpad(v_BRXM00,(8+lengthb(trim(v_BRXM00)))/2,' '),8,' '); v_return:=v_return||'请'||v_jybrdl.DLH000||'号到'||substrb(nvl(v_jybrdl.ckmc00,''),1,8)||'采集'||chr(13); end loop; if (3-i=1) then v_return:=v_return||chr(13); elsif (3-i=2) then v_return:=v_return||chr(13); v_return:=v_return||chr(13); elsif (3-i=1) then v_return:=v_return||chr(13); v_return:=v_return||chr(13); end if; end; end loop; end; v_return:=v_return||'%'; elsif v_XSFSLB='8' then v_return :=''; begin select CKBH00 into v_CKBH00 from BM_CKBM00 where DQXSP0=p_XSPBH0 and FLAG00='J' and ZT0000='0' and rownum=1; EXCEPTION WHEN NO_DATA_FOUND THEN v_CKBH00:=-1; v_return:=v_return||'福州市第六医院祝您健康'; return v_return; end; declare cursor c_getCKBH is select CKBH00 into v_CKBH00 from BM_CKBM00 where DQXSP0=p_XSPBH0 and FLAG00='J' and ZT0000='0' order by CKBH00; begin i:=0; for v_getCKBH in c_getCKBH loop declare cursor c_jybrdl is select DLH000,BRID00,ckmc00,jhbz00 from (select a.DLH000,a.BRID00,b.ckmc00,jhbz00 from JY_PDJHB0 a,BM_CKBM00 b where b.ckbh00=a.cyckbh and jhbz00 in ('1','2') and phrq00=to_char(sysdate,'YYYYMMDD') and CYCKBH = v_getCKBH.CKBH00 order by JHBZ00 ,to_number(dlh000) desc ) where rownum<=3 order by JHBZ00 desc,to_number(dlh000) ; begin for v_jybrdl in c_jybrdl loop begin select trim(substrb(BRXM00,1,8)) into v_BRXM00 from BM_BRXXB0 where BRID00=v_jybrdl.BRID00; exception when NO_DATA_FOUND then v_BRXM00:=''; end; i:=i+1; v_BRXM00:=nvl(v_BRXM00,''); select SF_JY_GETPDJHBRXM(v_BRXM00,'0') into v_BRXM00 from dual; v_BRXM00:=rpad(lpad(v_BRXM00,(8+lengthb(trim(v_BRXM00)))/2,' '),8,' '); v_return:=v_return||'请'||v_jybrdl.DLH000||'号'||v_BRXM00||'到'||substrb(nvl(v_jybrdl.ckmc00,''),1,8)||'采集'||chr(13); end loop; if (3-i=1) then v_return:=v_return||chr(13); elsif (3-i=2) then v_return:=v_return||chr(13); v_return:=v_return||chr(13); elsif (3-i=1) then v_return:=v_return||chr(13); v_return:=v_return||chr(13); end if; end; end loop; end; v_return:=v_return||'%'; end if; end if; return v_return; end; / --%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%