--转换申请单状态为ESB状态码 create or replace function SP_JY_ESB_SQDZT0 (PJYYWID in number) return varchar is VJGZT00 JY_YW0000.JGZT00%type; VSQDZT0 varchar2(2); -- MODIFICATION HISTORY -- Person Date Comments -- nixj 2016.06.02 create -- nixj 2016.06.02 LIS与集成平台交互 for LIS-20160608-001 begin select JGZT00 into VJGZT00 from JY_YW0000 where JYYWID=PJYYWID; if (VJGZT00='0') then VSQDZT0:='1'; elsif (VJGZT00='1') then VSQDZT0:='5'; elsif (VJGZT00='2') then VSQDZT0:='6'; elsif (VJGZT00='3') then VSQDZT0:='7'; elsif (VJGZT00='4') then VSQDZT0:='8'; elsif (VJGZT00='5') then VSQDZT0:='8'; elsif (VJGZT00='6') then VSQDZT0:='9'; elsif (VJGZT00='7') then VSQDZT0:='11'; elsif (VJGZT00='8') then VSQDZT0:='12'; end if; return VSQDZT0; end; / --%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% --转换阳性标识结果 create or replace function SP_JY_ESB_YXBS00 (PJGID00 in number) return char is VYXBS00 char(1); VJGLX00 JY_BGXMZD.JGLX00%type; VJYJG00 JY_JYJG00.JYJG00%type; VCKXX00 JY_JYJG00.CKXX00%type; VCKSX00 JY_JYJG00.CKSX00%type; VJGZCBZ JY_JYJG00.JGZCBZ%type; VBGXMID JY_JYJG00.BGXMID%type; -- MODIFICATION HISTORY -- Person Date Comments -- nixj 2016.06.02 create -- nixj 2016.06.02 LIS与集成平台交互 for LIS-20160608-001 begin VYXBS00:='N'; select JGLX00,JYJG00,CKXX00,CKSX00,JGZCBZ,BGXMID into VJGLX00,VJYJG00,VCKXX00,VCKSX00,VJGZCBZ,VBGXMID from VW_JY_JYJG00 where JGID00=PJGID00; if(VBGXMID<999990) then if(VJGZCBZ<>'') then VYXBS00:='A'; end if; if(VJGLX00='N') then if(to_number(VJYJG00)to_number(VCKSX00)) then VYXBS00:='H'; end if; elsif (VJGLX00='C') then if(to_number(VJYJG00)to_number(VCKSX00)) then VYXBS00:='H'; end if; elsif (VJGLX00='L') then if VJGZCBZ='↓' then VYXBS00:='L'; elsif VJGZCBZ='↑' then VYXBS00:='H'; end if; end if; end if; return VYXBS00; exception when others then VYXBS00:='N'; return VYXBS00; end; / --%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% --检验结果发送（2） create or replace function SF_JY_ESB_JYJGXX ( PJYYWID in number, PYBH000 in varchar2, PPYRQ00 in char, PMZZYBZ in char, PZYGHID in number ) return clob is VXXJG00 clob; VYJDJH0 JY_YW0000.YJDJH0%type;--医技单据号 申请单号 VZDID00 JY_YW0000.ZDID00%type; VBGR000 JY_YW0000.BGR000%type; VBGRXM0 BM_YGBM00.ZWXM00%type; VBGJYR0 JY_YW0000.BGJYR0%type; VJYRXM0 BM_YGBM00.ZWXM00%type; VBG0000 char(14); VPY0000 char(14); VZDMC00 varchar2(100); VRYCWH0 varchar(10); VRYBQ00 ZY_BRXXB0.RYBQ00%type; VRZKS00 ZY_BRXXB0.RZKS00%type; VJKBH01 BM_ZLZD00.JKBH01%type; VZLXMJC BM_ZLZD00.ZLXMJC%type; VJKBH00 JY_BGXMZD.JKBH00%type; VZX0000 char(14); VKDYS00 YJ_YW0000.KDYS00%type; VKDYSXM YJ_YW0000.KDYSXM%type; VYYID00 XT_YYXX00.YYID00%type; VYYJC00 XT_YYXX00.YYJC00%type; VXH0000 number(3,0); VYMSL00 number(3,0); cursor CUR_JY_JYJG00 is select BGXMID,BGXMMC,BZBM00,JYJG00,JGDW00,CKXX00,CKSX00,JJZXX0,JJZSX0,JGID00 from VW_JY_JYJG00 where JYYWID=PJYYWID; cursor CUR_JY_XJJG00 is select XJID00,XJMS00,BBMC00,KSSID0,KSZWMC,YMJG00,YMSM00,ZWMC00,YMJGNE,JLS000,SFDCNY from VW_JY_XJJG00 where JYYWID=PJYYWID; -- MODIFICATION HISTORY -- Person Date Comments -- nixj 2016.06.02 create -- nixj 2016.06.02 LIS与集成平台交互 for LIS-20160608-001 begin --OBR部分 select YJDJH0,ZDID00,ZDMC00,CBGRQ0||replace(CBGSJ0,':',''),PYRQ00||replace(PYSJ00,':',''),BGR000,BGRXM0,BGJYR0,JYRXM0 into VYJDJH0,VZDID00,VZDMC00,VBG0000,VPY0000,VBGR000,VBGRXM0,VBGJYR0,VJYRXM0 from VW_JY_YW0000 where JYYWID=PJYYWID; if (PMZZYBZ='1') then select trim(RYCWH0),RYBQ00,RZKS00 into VRYCWH0,VRYBQ00,VRZKS00 from ZY_BRXXB0 where ZYID00=PZYGHID; end if; select JKBH01,ZLXMJC into VJKBH01,VZLXMJC from BM_ZLZD00 where ZLXMID=(select ZLXMID from JY_YW0000 where JYYWID=PJYYWID); VXXJG00:=VXXJG00||'OBR||'||VYJDJH0||'|'||PJYYWID||'|'||VJKBH01||'^'||VZLXMJC||'|||||||||' ||VZDID00||'^'||VZDMC00||'|||||||||||||||||||'||VBGJYR0||'&'||VJYRXM0||'||'||VBGR000||'&'||VBGRXM0||'|||||||||||||||||||||'||VBG0000||'||'||chr(13); --ORC部分 select ZXRQ00||replace(ZXSJ00,':',''),YYID00,KDYS00,KDYSXM into VZX0000,VYYID00,VKDYS00,VKDYSXM from YJ_YW0000 where YJDJH0=VYJDJH0; --select YYJC00 into VYYJC00 from XT_YYXX00 where YYID00=VYYID00; VXXJG00:=VXXJG00||'ORC|NW|'||VYJDJH0||'|||'||SP_JY_ESB_SQDZT0(PJYYWID)||'||||||||^^'||VRYCWH0||'^'||VRYBQ00||'^'||VRZKS00||'||'||VZX0000||'||'||VYYID00||'^'||VYYJC00||'||'||VKDYS00||'^'||VKDYSXM||'||||||||||'||chr(13); --OBX部分 VXH0000:=0; for V_JY_JYJG00 in CUR_JY_JYJG00 loop select JKBH00 into VJKBH00 from JY_BGXMZD where BGXMID=V_JY_JYJG00.BGXMID; VXXJG00:=VXXJG00||'OBX|'||VXH0000||'||'||VJKBH00||'^'||V_JY_JYJG00.BGXMMC ||'^^^'||V_JY_JYJG00.BZBM00||'|'||PYBH000||'|'||V_JY_JYJG00.JYJG00||'|'||V_JY_JYJG00.JGDW00||'|'||V_JY_JYJG00.CKXX00 ||'^'||V_JY_JYJG00.CKSX00||'^'||V_JY_JYJG00.JJZXX0||'^'||V_JY_JYJG00.JJZSX0||'|' ||SP_JY_ESB_YXBS00(V_JY_JYJG00.JGID00)||'|||U||非细菌||'||VPY0000||'|'||VYYID00||'^'||VYYJC00 ||'|'||VBGJYR0||'^'||VJYRXM0||'|||||||||||||||||||||||'||chr(13); VXH0000:=VXH0000+1; end loop; select count(*) into VYMSL00 from VW_JY_XJJG00 where KSSID0 is not null and trim(KSSID0)<>'' and JYYWID=PJYYWID; for V_JY_XJJG00 in CUR_JY_XJJG00 loop VXXJG00:=VXXJG00||'OBX|'||VXH0000||'||'||V_JY_XJJG00.XJID00||'^'||V_JY_XJJG00.XJMS00||'|'||PYBH000||'|||||||U|||细菌||||||||||||||'||V_JY_XJJG00.XJID00 ||'|'||V_JY_XJJG00.XJMS00||'|'||V_JY_XJJG00.BBMC00||'|'||V_JY_XJJG00.KSZWMC ||'|'||V_JY_XJJG00.YMJG00||'|'||VYMSL00||'|'||V_JY_XJJG00.YMSM00||'||' ||V_JY_XJJG00.ZWMC00||'|'||V_JY_XJJG00.YMJGNE||'|'||V_JY_XJJG00.JLS000||'||'||V_JY_XJJG00.SFDCNY||chr(13); VXH0000:=VXH0000+1; end loop; return trim(VXXJG00); end; / --%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% --检验结果发送（1） create or replace function SP_JY_ESB_JYJG00 ( PPYRQ00 in char, PYBH000 in char )return clob is VXXJG00 clob; VBRXM00 BM_BRXXB0.BRXM00%type; VBRID00 JY_YW0000.BRID00%type; VMZZYBZ JY_YW0000.MZZYBZ%type; VZYGHID JY_YW0000.ZYGHID%type; VZYH000 varchar(12); VRYCWH0 varchar(10); VRYBQ00 ZY_BRXXB0.RYBQ00%type; VRZKS00 ZY_BRXXB0.RZKS00%type; cursor CUR_JY_YW0000 is select JYYWID from JY_YW0000 where PYRQ00=PPYRQ00 and YBH000=PYBH000; -- MODIFICATION HISTORY -- Person Date Comments -- nixj 2016.06.02 create -- nixj 2016.06.02 LIS与集成平台交互 for LIS-20160608-001 begin VXXJG00:='MSH|^~\'; VXXJG00:=VXXJG00||'&|LIS|发送模块名称|EMR|接收模块名称|'||to_char(systimestamp, 'yyyymmddhh24miss.ff3')|| '|T|OUL^R24^OUL_R24|Test_Report_Send-'||to_char(systimestamp, 'yyyymmddhh24missff3')||'|P|2.7'||chr(13); select MZZYBZ,ZYGHID into VMZZYBZ,VZYGHID from VW_JY_YW0000 where PYRQ00=PPYRQ00 and YBH000=PYBH000 and rownum=1; --PID及PV1部分 if (VMZZYBZ='0') then select XM0000 into VBRXM00 from SF_BRXXB0 where GHID00=VZYGHID; VXXJG00:=VXXJG00||'PID|||'||VBRID00||'||'||VBRXM00||chr(13)||'PV1|1|1||||||||||'||chr(13); elsif (VMZZYBZ='1') then select XM0000,trim(ZYH000),trim(RYCWH0),RYBQ00,RZKS00 into VBRXM00,VZYH000,VRYCWH0,VRYBQ00,VRZKS00 from ZY_BRXXB0 where ZYID00=VZYGHID; VXXJG00:=VXXJG00||'PID|||'||VBRID00||'||'||VBRXM00||chr(13)||'PV1|1|3^'||VZYGHID||'^'||VZYH000||'|^^'||VRYCWH0||'^'||VRYBQ00||'^'||VRZKS00||'||||||||||'||chr(13); end if; for V_JY_JYYWID in CUR_JY_YW0000 loop VXXJG00:=VXXJG00||SF_JY_ESB_JYJGXX(V_JY_JYYWID.JYYWID,PYBH000,PPYRQ00,VMZZYBZ,VZYGHID); end loop; return VXXJG00; end; / --%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% --发送检验申请单更新 create or replace function SP_JY_ESB_GXSQD1 ( PJYYWID in number) return varchar2 is VXXJG00 varchar2(4000); VJGZT00 JY_YW0000.JGZT00%type; VBRID00 JY_YW0000.BRID00%type; VBRXM00 JY_YW0000.BRXM00%type; VMZZYBZ JY_YW0000.MZZYBZ%type; VZYGHID JY_YW0000.ZYGHID%type; VZYHGHH JY_YW0000.ZYHGHH%type; VYJDJH0 JY_YW0000.YJDJH0%type; VSQRQSJ char(14); VSQYS00 JY_YW0000.SQYS00%type; VCZY000 JY_YW0000.CZY000%type; VSQBQ00 JY_YW0000.SQBQ00%type; VSQKS00 JY_YW0000.SQKS00%type; VYXBZ00 JY_YW0000.YXBZ00%type; VZLXMID JY_YW0000.ZLXMID%type; VZLXMJC BM_ZLZD00.ZLXMJC%type; VJKBH01 BM_ZLZD00.JKBH01%type; VPYRQSJ char(14); VBBCJR0 JY_YW0000.BBCJR0%type; VTXM000 JY_YW0000.TXM000%type; VZWXM00 BM_YGBM00.ZWXM00%type; VBBID00 JY_YW0000.BBID00%type; VBBMC00 BM_BBZD00.BBMC00%type; VJKBH00 BM_BBZD00.JKBH00%type; -- MODIFICATION HISTORY -- Person Date Comments -- nixj 2016.06.02 create -- nixj 2016.06.02 LIS与集成平台交互 for LIS-20160608-001 begin select JGZT00,BRXM00,BRID00,MZZYBZ,ZYGHID,ZYHGHH,YJDJH0,SQRQ00||replace(SQSJ00,':',''),SQYS00,CZY000,SQBQ00,SQKS00,YXBZ00,ZLXMID ,PYRQ00||replace(PYSJ00,':',''),BBCJR0,TXM000,BBID00 into VJGZT00,VBRXM00,VBRID00,VMZZYBZ,VZYGHID,VZYHGHH ,VYJDJH0,VSQRQSJ,VSQYS00,VCZY000,VSQBQ00,VSQKS00,VYXBZ00,VZLXMID,VPYRQSJ,VBBCJR0,VTXM000,VBBID00 from JY_YW0000 where JYYWID=PJYYWID; VXXJG00:=VXXJG00||'MSH|^~\'; VXXJG00:=VXXJG00||'&|LIS|发送模块名称|EMR|接收模块名称|'||to_char(systimestamp, 'yyyymmddhh24miss.ff3')|| '|T|OML^O21^OML_O21|Test_Form_Update-'||to_char(systimestamp, 'yyyymmddhh24missff3')||'|P|2.7'||chr(13); --PID VXXJG00:=VXXJG00||'PID|||'||VBRID00||'||'||VBRXM00||chr(13); --PV1 if(VMZZYBZ='0') then VMZZYBZ:='1'; elsif(VMZZYBZ='1') then VMZZYBZ:='3'; end if; VXXJG00:=VXXJG00||'PV1|1|'||VMZZYBZ||'^'||VZYGHID||'^'||VZYHGHH||chr(13); --ORC VXXJG00:=VXXJG00||'ORC|XO|'||VYJDJH0||'|||'||SP_JY_ESB_SQDZT0(PJYYWID)||'||||'||VSQRQSJ||'|'||VCZY000||'^||'||VSQYS00||'^|^^^'||VSQBQ00||'^'||VSQKS00||'^||||||||||||'||VCZY000||'^||||||||'||chr(13); --TQ1 if(VYXBZ00='Y') then VYXBZ00:='A'; elsif(VYXBZ00='N') then VYXBZ00:='R'; end if; --TQ1 VXXJG00:=VXXJG00||'TQ1||1|||||||'||VYXBZ00||chr(13); --OBR begin select BBMC00,JKBH00 into VBBMC00,VJKBH00 from BM_BBZD00 where BBID00=VBBID00; exception when others then VBBMC00:=''; VJKBH00:=''; end; begin select ZLXMJC,PTJKBH into VZLXMJC,VJKBH01 from BM_ZLZD00 where ZLXMID=VZLXMID; exception when others then VZLXMJC:=''; VJKBH01:=''; end; begin select ZWXM00 into VZWXM00 from BM_YGBM00 where YGBH00=VBBCJR0; exception when others then VZWXM00:='检验科'; VBBCJR0:=2086; update JY_YW0000 set BBCJR0=2086 where JYYWID=PJYYWID; end; VXXJG00:=VXXJG00||'OBR|1|'||VYJDJH0||'||'||VJKBH01||'^'||VZLXMJC||'|||'||VPYRQSJ||'|||||| |||'||VSQYS00||'^||||||||||||||||||'||VBBCJR0||'&'||VZWXM00||'^^^^^^^^|||'||VTXM000||'||||||'||VJKBH00||'^'||VBBMC00; insert into JY_ESB_FSLOG values(VYJDJH0,PJYYWID,'申请单更新',to_char(systimestamp, 'yyyymmdd hh24:mi:ss'),VXXJG00); return VXXJG00; end; / --%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% --接收HIS更新患者信息 create or replace procedure SP_JY_ESB_GXBRXX ( PHL7000 in varchar2) as VHHF000 char(1); VPID000 varchar2(1000); VPV1000 varchar2(1000); VHL7000 varchar2(4000); VSTR000 varchar2(500); VBRID00 JY_YW0000.BRID00%type; VBRXM00 JY_YW0000.BRXM00%type; VCSRQ00 JY_YW0000.CSRQ00%type; VXB0000 JY_YW0000.XB0000%type; VZYGHID JY_YW0000.ZYGHID%type; VZYHGHH JY_YW0000.ZYHGHH%type; VMZZYBZ JY_YW0000.MZZYBZ%type; VNL0000 JY_YW0000.NL0000%type; VNLDW00 varchar2(2); VCH0000 JY_YW0000.CH0000%type; -- MODIFICATION HISTORY -- Person Date Comments -- nixj 2016.06.02 create -- nixj 2016.06.02 LIS与集成平台交互 for LIS-20160608-001 begin VHHF000:='@'; VHL7000:=translate(PHL7000,chr(13)||chr(10),'#@'); VPID000:=substr(VHL7000,instr(VHL7000,VHHF000,1,2)+1,instr(VHL7000,VHHF000,1,3)-instr(VHL7000,VHHF000,1,2)-1); VSTR000:=substr(VPID000,instr(VPID000,'|',1,3)+1,instr(VPID000,'|',1,4)-instr(VPID000,'|',1,3)-1); VBRID00:=substr(VSTR000,0,instr(VSTR000,'^',1,1)-1); VBRXM00:=substr(VPID000,instr(VPID000,'|',1,5)+1,instr(VPID000,'|',1,6)-instr(VPID000,'|',1,5)-1); VCSRQ00:=substr(VPID000,instr(VPID000,'|',1,7)+1,instr(VPID000,'|',1,8)-instr(VPID000,'|',1,7)-1); VNL0000:=substr(SF_JY_CSRQTONL(VCSRQ00),0,length(SF_JY_CSRQTONL(VCSRQ00))-1); VNLDW00:=substr(SF_JY_CSRQTONL(VCSRQ00),-1); if(VNLDW00='岁') then VNLDW00:='Y'; elsif (VNLDW00='月') then VNLDW00:='M'; elsif (VNLDW00='天') then VNLDW00:='D'; end if; VXB0000:=substr(VPID000,instr(VPID000,'|',1,8)+1,instr(VPID000,'|',1,9)-instr(VPID000,'|',1,8)-1); if(VXB0000='1') then VXB0000:='M'; elsif (VXB0000='2') then VXB0000:='F'; else VXB0000:='W'; end if; VPV1000:=substr(VHL7000,instr(VHL7000,VHHF000,1,3)+1,instr(VHL7000,VHHF000,1,4)-instr(VHL7000,VHHF000,1,3)-1); VSTR000:=substr(VPV1000,instr(VPV1000,'|',1,2)+1,instr(VPV1000,'|',1,3)-instr(VPV1000,'|',1,2)-1); if (substr(VSTR000,0,1)='3') then VMZZYBZ:='1'; elsif (substr(VSTR000,0,1)='1') then VMZZYBZ:='0'; end if; VZYGHID:=substr(VSTR000,3,instr(VSTR000,'^',1,2)-3); VZYHGHH:=substr(VSTR000,instr(VSTR000,'^',1,2)+1,instr(VSTR000,'^',1,3)-instr(VSTR000,'^',1,2)-1); VSTR000:=substr(VPV1000,instr(VPV1000,'|',1,3)+1,instr(VPV1000,'|',1,4)-instr(VPV1000,'|',1,3)-1); VCH0000:=substr(VSTR000,instr(VSTR000,'^',1,2)+1,instr(VSTR000,'^',1,3)-instr(VSTR000,'^',1,2)-1); update JY_YW0000 set BRXM00=VBRXM00,XB0000=VXB0000,CSRQ00=VCSRQ00,NL0000=VNL0000,NLDW00=VNLDW00,MZZYBZ=VMZZYBZ,ZYGHID=VZYGHID,ZYHGHH=VZYHGHH,CH0000=VCH0000 where BRID00=VBRID00; exception when others then RAISE_APPLICATION_ERROR(-20020, nvl(sqlerrm, '原因不明出错！*')); end; / --%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% --接收更新检验申请单状态 create or replace procedure SP_JY_ESB_GXSQD0 ( PHL7000 in varchar2) as VHHF000 char(1); VPID000 varchar2(4000); VPV1000 varchar2(4000); VORC000 varchar2(4000); VHL7000 varchar2(4000); VYJDJH0 JY_YW0000.YJDJH0%type; VJGZT00 JY_YW0000.JGZT00%type; VSFBZ00 JY_YW0000.SFBZ00%type; VSQRQ00 JY_YW0000.SQRQ00%type; VSQSJ00 JY_YW0000.SQSJ00%type; VSQDZT0 varchar2(2); VTQ1000 varchar2(4000); VOBR000 varchar2(4000); VDG1000 varchar2(4000); -- MODIFICATION HISTORY -- Person Date Comments -- nixj 2016.06.02 create -- nixj 2016.06.02 LIS与集成平台交互 for LIS-20160608-001 begin VHHF000:='@'; VHL7000:=translate(PHL7000,chr(13)||chr(10),'#@'); VSQRQ00:=substr(VHL7000,instr(VHL7000,'|',1,6)+1,8); VSQSJ00:=to_char(to_date(substr(VHL7000,instr(VHL7000,'|',1,6)+1,14),'YYYYMMDDhh24:mi:ss'),'HH24:mi:ss'); --PID VPID000:=substr(VHL7000,instr(VHL7000,VHHF000,1,1)+1,instr(VHL7000,VHHF000,1,2)-instr(VHL7000,VHHF000,1,1)-1); --PV1 --VPV1000:=substr(VHL7000,instr(VHL7000,VHHF000,1,2)+1,instr(VHL7000,VHHF000,1,3)-instr(VHL7000,VHHF000,1,2)); --PV2 --VPV1000:=substr(VHL7000,instr(VHL7000,VHHF000,1,3)+1,instr(VHL7000,VHHF000,1,4)-instr(VHL7000,VHHF000,1,3)); --ORC VORC000:=substr(VHL7000,instr(VHL7000,VHHF000,1,4)+1,instr(VHL7000,VHHF000,1,5)-instr(VHL7000,VHHF000,1,4)); VYJDJH0:=to_number(substr(VORC000,instr(VORC000,'|',1,2)+1,instr(VORC000,'|',1,3)-instr(VORC000,'|',1,2)-1)); VSQDZT0:=substr(VORC000,instr(VORC000,'|',1,5)+1,instr(VORC000,'|',1,6)-instr(VORC000,'|',1,5)-1); --LIS用以下数据：0=预约1=申请2=收费3=退费4=备管5=采集6＝签收7＝退回8＝排样9＝审核 11＝作废 12＝打印 13=冲销 14=撤销冲销 if VSQDZT0='2' then VSFBZ00:='2'; elsif VSQDZT0='3' then VSFBZ00:='2'; VJGZT00:='7'; elsif VSQDZT0='11' then VSFBZ00:='6'; VJGZT00:='7'; elsif VSQDZT0='13' then VSFBZ00:='3'; VJGZT00:='7'; elsif VSQDZT0='14' then VSFBZ00:='3'; VJGZT00:='0'; end if; if (nvl(VSFBZ00,'-1')<>'-1') then update JY_YW0000 set SFBZ00=VSFBZ00 where YJDJH0=VYJDJH0; end if; if (nvl(VJGZT00,'-1')<>'-1') then update JY_YW0000 set JGZT00=VJGZT00 where YJDJH0=VYJDJH0; end if; exception when others then RAISE_APPLICATION_ERROR(-20020, nvl(sqlerrm, '原因不明出错！*')); end; / --%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% --接收检验申请单 create or replace procedure SP_JY_ESB_JSSQD0 ( PHL7000 in varchar )as VTEST varchar2(200); VSQRQ00 JY_YW0000.SQRQ00%type; VSQSJ00 JY_YW0000.SQSJ00%type; VSTR000 varchar2(1000); VHHF000 varchar2(2); VPID000 varchar2(1000); VJGZT00 JY_YW0000.JGZT00%type; VBRID00 JY_YW0000.BRID00%type; VBRXM00 JY_YW0000.BRXM00%type; VXB0000 JY_YW0000.XB0000%type; VCSRQ00 JY_YW0000.CSRQ00%type; VNL0000 JY_YW0000.NL0000%type; --VNLDW00 JY_YW0000.NLDW00%type; VNLDW00 varchar(2); VHL7000 varchar2(4000); VTXMFS0 XT_XTCS00.VALUE0%type; VKDBQ00 JY_YW0000.SQBQ00%type; VPV1000 varchar2(1000); VMZZYBZ JY_YW0000.MZZYBZ%type; VZYGHID JY_YW0000.ZYGHID%type; VZYHGHH JY_YW0000.ZYHGHH%type; VSQKS00 JY_YW0000.SQKS00%type; VSQBQ00 JY_YW0000.SQBQ00%type; VCH0000 JY_YW0000.CH0000%type; VSQYS00 JY_YW0000.SQYS00%type; VYJDJH0 JY_YW0000.YJDJH0%type; VYJFZH0 JY_YW0000.YJFZH0%type; VZDID00 JY_YW0000.ZDID00%type; VJYYWID JY_YW0000.JYYWID%type; VYXBZ00 JY_YW0000.YXBZ00%type; VRLY000 JY_YW0000.LRY000%type; VSFBZ00 JY_YW0000.SFBZ00%type; VYEXM00 JY_YW0000.YEXM00%type; VJKBH01 BM_ZLZD00.JKBH01%type; VZLXMID BM_ZLZD00.ZLXMID%type; VBBJKBH BM_BBZD00.JKBH00%type; VBBID00 BM_BBZD00.BBID00%type; JY_JYCJSFCYZDYHYTM XT_XTCS00.VALUE0%type; JY_TSSYNXZDY XT_XTCS00.VALUE0%type; VZHXMCF XT_XTCS00.VALUE0%type; VCOUNT0 number; VORC000 varchar2(1000); VTQ1000 varchar2(1000); VOBR000 varchar2(1000); VDG1000 varchar2(1000); VOBX000 varchar2(1000); cursor CUR_ZHXM00 is select distinct ZHXMID,ZLXMID,TDJTTM from XT_ZHMX00 where ZHXMID=VZLXMID; -- MODIFICATION HISTORY -- Person Date Comments -- nixj 2016.06.02 create -- nixj 2016.06.02 LIS与集成平台交互 for LIS-20160608-001 begin --insert into jy_test values('1',PHL7000); --commit; --VHHF000:=chr(13); VHL7000:=translate(PHL7000,chr(13)||chr(10),'#@'); VHHF000:='@'; VSQRQ00:=substr(VHL7000,instr(VHL7000,'|',1,6)+1,8); VSQSJ00:=to_char(to_date(substr(VHL7000,instr(VHL7000,'|',1,6)+1,14),'YYYYMMDDhh24:mi:ss'),'HH24:mi:ss'); --PID VPID000:=substr(VHL7000,instr(VHL7000,VHHF000,1,1)+1,instr(VHL7000,VHHF000,1,2)-instr(VHL7000,VHHF000,1,1)-1); VBRID00:=to_number(substr(VPID000,7,instr(VPID000,'^',1,1)-7)); --VBRID00:=substr(VPID000,7,instr(VPID000,'^',1,1)-7); --insert into jy_test values('1.1',VHL7000); --commit; VSTR000:=substr(VPID000,instr(VPID000,'|',1,5)+1,instr(VPID000,'|',1,6)-instr(VPID000,'|',1,5)-1); VBRXM00:=substr(VSTR000,0,instr(VSTR000,'~',1,1)-1); VYEXM00:=substr(VSTR000,instr(VSTR000,'^',1,1)+1,instr(VSTR000,'^',1,2)-instr(VSTR000,'^',1,1)-1); VXB0000:=substr(VPID000,instr(VPID000,'|',1,8)+1,instr(VPID000,'|',1,9)-instr(VPID000,'|',1,8)-1); --insert into jy_test values('1.2',VXB0000); --commit; if(VXB0000='1') then VXB0000:='M'; elsif (VXB0000='2') then VXB0000:='F'; else VXB0000:='W'; end if; --VCSRQ00:=substr(VPID000,instr(VPID000,'|',1,7)+1,instr(VPID000,'|',1,8)-instr(VPID000,'|',1,7)-1); VCSRQ00:=substr(VPID000,instr(VPID000,'|',1,7)+1,instr(VPID000,'|',1,8)-instr(VPID000,'|',1,7)-1); VNL0000:=substr(SF_JY_CSRQTONL(VCSRQ00),0,length(SF_JY_CSRQTONL(VCSRQ00))-1); VNLDW00:=substr(SF_JY_CSRQTONL(VCSRQ00),-1); if(VNLDW00='岁') then VNLDW00:='Y'; elsif (VNLDW00='月') then VNLDW00:='M'; elsif (VNLDW00='天') then VNLDW00:='D'; end if; --PV1 VPV1000:=substr(VHL7000,instr(VHL7000,VHHF000,1,2)+1,instr(VHL7000,VHHF000,1,3)-instr(VHL7000,VHHF000,1,2)-1); VSTR000:=substr(VPV1000,instr(VPV1000,'|',1,2)+1,instr(VPV1000,'|',1,3)-instr(VPV1000,'|',1,2)-1); if (substr(VSTR000,0,1)='3') then VMZZYBZ:='1'; VJGZT00:='0'; elsif (substr(VSTR000,0,1)='1') then VMZZYBZ:='0'; vjgzt00:='0'; end if; VZYGHID:=substr(VSTR000,3,instr(VSTR000,'^',1,2)-3); VZYHGHH:=substr(VSTR000,instr(VSTR000,'^',1,2)+1,instr(VSTR000,'^',1,3)-instr(VSTR000,'^',1,2)-1); VSTR000:=substr(VPV1000,instr(VPV1000,'|',1,3)+1,instr(VPV1000,'|',1,4)-instr(VPV1000,'|',1,3)-1); VCH0000:=substr(VSTR000,instr(VSTR000,'^',1,2)+1,instr(VSTR000,'^',1,3)-instr(VSTR000,'^',1,2)-1); VKDBQ00:=substr(VSTR000,instr(VSTR000,'^',1,3)+1,instr(VSTR000,'^',1,4)-instr(VSTR000,'^',1,3)-1); --insert into jy_test values('2',VPV1000); --commit; --ORC VORC000:=substr(VHL7000,instr(VHL7000,VHHF000,1,4)+1,instr(VHL7000,VHHF000,1,5)-instr(VHL7000,VHHF000,1,4)-1); --insert into jy_test values('3',VORC000); --commit; VYJDJH0:=to_number(substr(VORC000,instr(VORC000,'|',1,2)+1,instr(VORC000,'|',1,3)-instr(VORC000,'|',1,2)-1)); --insert into jy_test values('3.1',to_char(VYJDJH0)); --commit; VYJFZH0:=substr(VORC000,instr(VORC000,'|',1,4)+1,instr(VORC000,'|',1,5)-instr(VORC000,'|',1,4)-1); --insert into jy_test values('3.2',VYJFZH0); --commit; VSFBZ00:=substr(VORC000,instr(VORC000,'|',1,5)+1,instr(VORC000,'|',1,6)-instr(VORC000,'|',1,5)-1); --insert into jy_test values('3.3',VSFBZ00); --commit; VSTR000:=substr(VORC000,instr(VORC000,'|',1,11)+1,instr(VORC000,'|',1,12)-instr(VORC000,'|',1,11)-1); VRLY000:=substr(VSTR000,0,instr(VSTR000,'^',1,1)-1); VSTR000:=substr(VORC000,instr(VORC000,'|',1,12)+1,instr(VORC000,'|',1,13)-instr(VORC000,'|',1,12)-1); VSQYS00:=substr(VSTR000,0,instr(VSTR000,'^',1,1)-1); VSTR000:=substr(VORC000,instr(VORC000,'|',1,13)+1,instr(VORC000,'|',1,14)-instr(VORC000,'|',1,13)-1); VTEST:=substr(VSTR000,4,instr(VSTR000,'^',1,4)-4); if(length(VTEST)>'0') then VSQBQ00:=to_number(VTEST); end if; VSQKS00:=to_number(substr(VSTR000,instr(VSTR000,'^',1,4)+1,instr(VSTR000,'^',1,5)-instr(VSTR000,'^',1,4)-1)); --insert into jy_test values('4',VSQKS00); --commit; --TQ1 VTQ1000:=substr(VHL7000,instr(VHL7000,VHHF000,1,5)+1,instr(VHL7000,VHHF000,1,6)-instr(VHL7000,VHHF000,1,5)); VYXBZ00:=substr(VTQ1000,instr(VOBR000,'|',1,9)+1,1); if(VYXBZ00='A') then VYXBZ00:='Y'; else VYXBZ00:='N'; end if; --OBR VOBR000:=substr(VHL7000,instr(VHL7000,VHHF000,1,6)+1,instr(VHL7000,VHHF000,1,7)-instr(VHL7000,VHHF000,1,6)); VJKBH01:=substr(VOBR000,instr(VOBR000,'|',1,4)+1,instr(VOBR000,'^',1,1)-instr(VOBR000,'|',1,4)-1); --insert into jy_test values('4.1',VJKBH01); --commit; begin select ZLXMID into VZLXMID from BM_ZLZD00 where trim(ptjkbh)=VJKBH01; exception when others then VZLXMID:=VJKBH01; end; VSTR000:=substr(VOBR000,instr(VOBR000,'|',1,46)+1); VBBJKBH:=substr(VSTR000,0,instr(VSTR000,'^',1,1)-1); begin select BBID00 into VBBID00 from BM_BBZD00 where JKBH00=VBBJKBH; exception when others then VBBID00:=''; end; --DG1 VDG1000:=substr(VHL7000,instr(VHL7000,VHHF000,1,7)+1); VZDID00:=substr(VDG1000,instr(VDG1000,'|',1,3)+1,instr(VDG1000,'^',1,1)-instr(VDG1000,'|',1,3)-1); --insert into jy_test values('5',VDG1000); --commit; --OBX --VOBX000:=substr(VHL7000,instr(VHL7000,VHHF000,1,7)+1,instr(VHL7000,VHHF000,1,8)-instr(VHL7000,VHHF000,1,7)); begin select trim(VALUE0) into VZHXMCF from XT_XTCS00 where NAME00='JY_ZHXMSFCF'; exception when others then VZHXMCF := 'N'; end; --insert into jy_test values('6',VDG1000); --commit; select trim(VALUE0) into VTXMFS0 from XT_XTCS00 where NAME00='JY_TXMFS'; select trim(value0) into JY_JYCJSFCYZDYHYTM from xt_xtcs00 where name00='JY_JYCJSFCYZDYHYTM'; select trim(VALUE0) into JY_TSSYNXZDY from XT_XTCS00 where NAME00='JY_TSSYNXZDY'; select count(*) into VCOUNT0 from JY_YW0000 where YJDJH0=VYJDJH0 and JGZT00<>'7'; if VCOUNT0<>0 then begin delete JY_YW0000 where YJDJH0=VYJDJH0 and JGZT00='0' and MZZYBZ='0' and SFBZ00='1'; exception when others then RAISE_APPLICATION_ERROR(-20019,'申请信息已经存在！'); end; end if; select count(*) into VCOUNT0 from XT_ZHMX00 where ZHXMID=VZLXMID; if (VZHXMCF = 'Y') and (VCOUNT0 > 1) then for V_ZHXM00 in CUR_ZHXM00 loop select SEQ_JY_YW0000_LSID00.nextval into VJYYWID from DUAL; insert into JY_YW0000 (JYYWID,YJDJH0,BRID00,YJFZH0,ZYHGHH,ZLXMID,ZYGHID,MZZYBZ,YXBZ00,LRY000, ZDID00,BBID00,SQRQ00,SQSJ00,SQKS00,SQBQ00,SQYS00,JYDLY0,JGZT00,SFBZ00, BRXM00,YEXM00,XB0000,CSRQ00,CH0000,NL0000,NLDW00) VALUES (VJYYWID,VYJDJH0,VBRID00,nvl(VYJFZH0,0),VZYHGHH,V_ZHXM00.ZLXMID,VZYGHID,VMZZYBZ,VYXBZ00, VRLY000,VZDID00,VBBID00,VSQRQ00,VSQSJ00,VSQKS00, VKDBQ00,VSQYS00,VMZZYBZ,VJGZT00, VSFBZ00,VBRXM00,VYEXM00,VXB0000,VCSRQ00,VCH0000,VNL0000,VNLDW00); update jy_yw0000 set txm000=null where JYYWID=VJYYWID; insert into JY_YWMX00(JYMXID,JYYWID,ZLXMID) select SEQ_JY_YWMX00_JYMXID.nextval,VJYYWID,V_ZHXM00.ZLXMID from dual where not exists(select 1 from JY_YWMX00 where JYYWID=VJYYWID and ZLXMID=V_ZHXM00.ZLXMID); end loop; else select SEQ_JY_YW0000_LSID00.nextval into VJYYWID from DUAL; insert into JY_YW0000 (JYYWID,YJDJH0,BRID00,YJFZH0,ZYHGHH,ZLXMID,ZYGHID,MZZYBZ,YXBZ00,LRY000, ZDID00,BBID00,SQRQ00,SQSJ00,SQKS00,SQBQ00,SQYS00,JYDLY0,JGZT00,SFBZ00, BRXM00,YEXM00,XB0000,CSRQ00,CH0000,NL0000,NLDW00) VALUES (VJYYWID,VYJDJH0,VBRID00,nvl(VYJFZH0,0),VZYHGHH,VZLXMID,VZYGHID,VMZZYBZ,VYXBZ00, VRLY000,VZDID00,VBBID00,VSQRQ00,VSQSJ00,VSQKS00, VKDBQ00,VSQYS00,VMZZYBZ,VJGZT00, VSFBZ00,VBRXM00,VYEXM00,VXB0000,VCSRQ00,VCH0000,VNL0000,VNLDW00); update jy_yw0000 set txm000=null where JYYWID=VJYYWID; insert into JY_YWMX00(JYMXID,JYYWID,ZLXMID) select SEQ_JY_YWMX00_JYMXID.nextval,VJYYWID,VZLXMID from dual where not exists(select 1 from JY_YWMX00 where JYYWID=VJYYWID and ZLXMID=VZLXMID); end if; commit; if JY_JYCJSFCYZDYHYTM='Y' then SP_JY_JYTXM0(VBRID00,VMZZYBZ,'N','','0','Y'); else if VTXMFS0 = '1' then SP_JY_JYTXM0(VBRID00,VMZZYBZ,'N','','0','Y'); elsif (VTXMFS0 = '2') and ( JY_TSSYNXZDY = VMZZYBZ) then SP_JY_JYTXM0(VBRID00,VMZZYBZ,'N','','0','Y'); end if; end if; exception when others then RAISE_APPLICATION_ERROR(-20020, nvl(sqlerrm, '原因不明出错！*')); end; / --%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% --接收危急值确认 create or replace procedure SP_JY_ESB_WJZQR0 ( PHL7000 in varchar2) as VHHF000 char(1); VOBR000 varchar2(1000); VHL7000 varchar2(4000); VYJDJH0 JY_YW0000.YJDJH0%type; VBGCKR0 JY_YW0000.BGCKR0%type; VBGCKSJ JY_YW0000.BGCKSJ%type; VSTR000 varchar2(500); -- MODIFICATION HISTORY -- Person Date Comments -- nixj 2016.06.02 create -- nixj 2016.06.02 LIS与集成平台交互 for LIS-20160608-001 begin VHHF000:='@'; VHL7000:=translate(PHL7000,chr(13)||chr(10),'#@'); --OBR VOBR000:=substr(VHL7000,instr(VHL7000,VHHF000,1,4)+1); VYJDJH0:=to_number(substr(VOBR000,instr(VOBR000,'|',1,2)+1,instr(VOBR000,'|',1,3)-instr(VOBR000,'|',1,2)-1)); VSTR000:=substr(VOBR000,instr(VOBR000,'|',1,12)+1); VBGCKR0:=substr(VSTR000,instr(VSTR000,'^',1,1)+1,instr(VSTR000,'^',1,2)-instr(VSTR000,'^',1,1)-1); VBGCKSJ:=substr(VSTR000,instr(VSTR000,'^',1,2)+1,instr(VSTR000,'^',1,3)-instr(VSTR000,'^',1,2)-1)||substr(VSTR000,instr(VSTR000,'^',1,3)+1,instr(VSTR000,'^',1,4)-instr(VSTR000,'^',1,3)-1); VBGCKSJ:=to_char(to_date(VBGCKSJ,'YYYYMMDD hh24:mi:ss'),'YYYYMMDD hh24:mi:ss'); update JY_YW0000 set BGCKR0=VBGCKR0,BGCKSJ=VBGCKSJ where YJDJH0=VYJDJH0; exception when others then RAISE_APPLICATION_ERROR(-20020, nvl(sqlerrm, '原因不明出错！*')); end; / --%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% --检验危急值发送 -- MODIFICATION HISTORY -- Person Date Comments -- nixj 2016.06.02 create -- nixj 2016.06.02 LIS与集成平台交互 for LIS-20160608-001 create or replace function SP_JY_ESB_JYWJZ0 (PJYYWID in number) return varchar2 is VXXJG00 varchar2(4000); VBRXM00 varchar2(40); VBRID00 number(10); VBG0000 char(14); VYJDJH0 JY_YW0000.YJDJH0%type; VYBH000 JY_YW0000.YBH000%type; VXH0000 number(3); VJKBH01 BM_ZLZD00.JKBH01%type; VZLXMJC BM_ZLZD00.ZLXMJC%type; VJKBH00 JY_BGXMZD.JKBH00%type; VZWMC00 JY_BGXMZD.ZWMC00%type; cursor CUR_JY_WJZJL0 is select ZLXMID,BGXMID,JYJG00,BGRQ00,BGSJ00 from JY_JYJG00 where SFCJJZ='Y' and JYYWID=PJYYWID; -- MODIFICATION HISTORY -- Person Date Comments -- nixj 2016.06.02 create -- nixj 2016.06.02 LIS与集成平台交互 for LIS-20160608-001 begin select BRXM00,BRID00,YJDJH0,YBH000,CBGRQ0||replace(CBGSJ0,':','') into VBRXM00,VBRID00,VYJDJH0,VYBH000,VBG0000 from VW_JY_YW0000 where JYYWID=PJYYWID; VXXJG00:='MSH|^~\'; VXXJG00:=VXXJG00||'&|LIS|发送模块名称|EMR|接收模块名称|'||to_char(systimestamp, 'yyyymmddhh24miss.ff3')||'|T|PPR^PC1^PPR_PC1|Test_Critical_Send-'||to_char(systimestamp, 'yyyymmddhh24missff3')||'|P|2.7'||chr(13); --PID VXXJG00:=VXXJG00||'PID|||'||VBRID00||'||'||VBRXM00||chr(13); --PRB VXXJG00:=VXXJG00||'PRB|AD|'||VBG0000||'|'||VYJDJH0||'|'||VYBH000||''||chr(13); --ORC VXXJG00:=VXXJG00||'ORC|NW|'||VYJDJH0||'|'||PJYYWID||chr(13); --OBR VXH0000:=1; for V_JY_WJZJL0 in CUR_JY_WJZJL0 loop select JKBH00,ZWMC00 into VJKBH00,VZWMC00 from JY_BGXMZD where BGXMID=V_JY_WJZJL0.BGXMID; select JKBH01,ZLXMJC into VJKBH01,VZLXMJC from BM_ZLZD00 where ZLXMID=V_JY_WJZJL0.ZLXMID; VXXJG00:=VXXJG00||'OBR|'||VXH0000||'|'||VYJDJH0||'|'||PJYYWID||'|'||VJKBH01||'^'||VZLXMJC||'^^'||VJKBH00||'^'||VZWMC00||'||||||||'||V_JY_WJZJL0.JYJG00||'^^^^^^'||V_JY_WJZJL0.BGRQ00||'^'||replace(V_JY_WJZJL0.BGSJ00,':','')||'^'||chr(13); VXH0000:=VXH0000+1; end loop; insert into JY_ESB_FSLOG values(VYJDJH0,PJYYWID,'危急值发送',to_char(systimestamp, 'yyyymmdd hh24:mi:ss'),VXXJG00); return trim(VXXJG00); end; / --%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% --医技扣费 create or replace function SP_JY_ESB_YJKF00 ( PYJDJH0 in number, PYGBH00 in number)return varchar2 is VXXJG00 varchar2(4000); VBRID00 YJ_YW0000.BRID00%type; VBRXM00 YJ_YW0000.BRXM00%type; VMZZYBZ YJ_YW0000.MZZYBZ%type; VZYGHID YJ_YW0000.ZYGHID%type; VZYHGHH YJ_YW0000.ZYHGHH%type; VYJKSBH YJ_YW0000.YJKSBH%type; VSL0000 YJ_YW0000.SL0000%type; VZLXMID YJ_YW0000.ZLXMID%type; VZLXMJC YJ_YW0000.ZLXMJC%type; VKDRQSJ char(14); VJYYWID JY_YW0000.JYYWID%type; VZWXM00 BM_YGBM00.ZWXM00%type; VXKH000 BM_YGBM00.XKH000%type; VBMBH00 BM_BMBM00.BMBH00%type; VBMMC00 BM_BMBM00.BMMC00%type; -- MODIFICATION HISTORY -- Person Date Comments -- nixj 2016.06.02 create -- nixj 2016.06.02 LIS与集成平台交互 for LIS-20160608-001 begin VXXJG00:='MSH|^~\'; VXXJG00:=VXXJG00||'&|LIS|发送模块名称|EMR|接收模块名称|'||to_char(systimestamp, 'yyyymmddhh24miss.ff3')||'|T|OML^O21^OML_O21|Fee_Pay_Medical_Technology-'||to_char(systimestamp, 'yyyymmddhh24missff3')||'|P|2.7'||chr(13); begin select BRID00,BRXM00,MZZYBZ,ZYGHID,ZYHGHH,YJKSBH,KDRQ00||replace(KDSJ00,':',''),SL0000,ZLXMID,ZLXMJC into VBRID00,VBRXM00,VMZZYBZ,VZYGHID,VZYHGHH,VYJKSBH,VKDRQSJ,VSL0000,VZLXMID,VZLXMJC from YJ_YW0000 where YJDJH0=PYJDJH0 and XMZT00 in('1','0'); exception when others then RAISE_APPLICATION_ERROR(-20090, nvl(sqlerrm, '找不到未执行医技单据号！*医技单据号'||PYJDJH0)); end; begin select JYYWID into VJYYWID from JY_YW0000 where YJDJH0=PYJDJH0 and SFBZ00='1' and rownum=1; exception when others then RAISE_APPLICATION_ERROR(-20090, nvl(sqlerrm, '找不到未收费检验单据号！*医技单据号'||PYJDJH0)); end; select ZWXM00,XKH000,BMBH00 into VZWXM00,VXKH000,VBMBH00 from BM_YGBM00 where YGBH00=PYGBH00; select BMMC00 into VBMMC00 from BM_BMBM00 where BMBH00=VBMBH00; --EVN --VXXJG00:=VXXJG00||'EVN||'||to_char(systimestamp, 'yyyymmddhh24miss')||'|||'||PYGBH00||'^'||VZWXM00||'^'||VXKH000||'||'||VBMBH00||'^'||VBMMC00||chr(13); --PID VXXJG00:=VXXJG00||'PID|||'||VBRID00||'||'||VBRXM00||chr(13); --PV1 if(VMZZYBZ='0') then VXXJG00:=VXXJG00||'PV1|1|1^'||VZYGHID||chr(13); elsif (VMZZYBZ='1') then VXXJG00:=VXXJG00||'PV1|1|3^'||VZYGHID||'^'||VZYHGHH||chr(13); end if; --ORC VXXJG00:=VXXJG00||'ORC|NW|'||PYJDJH0||'|||'||SP_JY_ESB_SQDZT0(VJYYWID)||'||||'||VKDRQSJ||'|'||PYGBH00||'^'||VZWXM00||'^'||VXKH000||'|||^^^^'||VYJKSBH||chr(13); --OBR VXXJG00:=VXXJG00||'OBR|1|'||PYJDJH0||'||'||VZLXMID||'^'||VZLXMJC||'^^^|||||1||||||||||||||'||chr(13); insert into JY_ESB_FSLOG values(PYJDJH0,VJYYWID,'医技扣费',to_char(systimestamp, 'yyyymmdd hh24:mi:ss'),VXXJG00); return VXXJG00; exception when others then RAISE_APPLICATION_ERROR(-20020, nvl(sqlerrm, '原因不明出错！*医技单据号'||PYJDJH0||'员工编号'||PYGBH00)); end; / --%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% --第三方查询LIS报告汇总 create or replace function SF_JY_ESB_REPORT (PHL7000 in clob) return clob is VJGXX00 clob; VHHF000 char(1); VHL7000 clob; VQPD000 varchar2(4000); VYQDH00 JY_YW0000.YQDH00%type; VJYYWID JY_YW0000.JYYWID%type; VYMSL00 number(3,0); VHS0000 varchar2(5); VJYRXM0 varchar2(30); VPYRQ00 JY_YW0000.PYRQ00%type; cursor CUR_JY_JYJG00 is select JGID00,BGXMID,BGXMMC,JYJG00,JGDW00,CKFW00 from VW_JY_JYJG00 where JYYWID=VJYYWID; cursor CUR_JY_XJJG00 is select XJID00,XJMS00,BBMC00,KSSID0,KSZWMC,YMJG00,YMSM00,ZWMC00,YMJGNE,JLS000,SFDCNY from VW_JY_XJJG00 where JYYWID=VJYYWID; -- MODIFICATION HISTORY -- Person Date Comments -- nixj 2016.06.02 create -- nixj 2016.06.02 LIS与集成平台交互 for LIS-20160608-001 begin VHHF000:='@'; VHL7000:=translate(PHL7000,chr(13)||chr(10),'#@'); VQPD000:=substr(VHL7000,instr(VHL7000,VHHF000,1,1)+1,instr(VHL7000,VHHF000,1,2)-instr(VHL7000,VHHF000,1,1)-1); VJYYWID:=to_number(substr(VQPD000,instr(VQPD000,'|',1,3)+1) ); select JYRXM0,PYRQ00 into VJYRXM0,VPYRQ00 from VW_JY_YW0000 where jyywid=VJYYWID; select YQDH00 into VYQDH00 from JY_YW0000 where jyywid=VJYYWID; if(trim(VYQDH00)<>'BI') then select count(*) into VHS0000 from jy_jyjg00 where jyywid=VJYYWID; VJGXX00:='QAK|QTR001|查询回应情况|K13^QRY_Test_Report_Return^HL7|'||VHS0000||chr(13); VJGXX00:=VJGXX00||'QPD|K13^QRY_Test_Report_Return^HL7|QTR001'||chr(13); VJGXX00:=VJGXX00||'RDF|21|检验项目代码^ST^50~项目名称^ST^100~检验结果^ST^1000~结果单位^ST^50~参考值范围^ST^500~阳性标识名称^ST^100~检测负责人姓名^ST^50~检测时间^DTM^100~是否细菌^ST^10~细菌ID^ST^50~细菌描述^ST^500~培养描述^ST^500~抗生素ID^ST^50~药敏结果^ST^100~药敏数量^NM^20~药敏说明^ST^500~细菌结果^ST^500~药敏NE结果值^ST^100~菌落数^ST^100~异养菌^ST^100~是否多重耐药^ST^50'||chr(13); for V_JYJG in CUR_JY_JYJG00 loop VJGXX00:=VJGXX00||'RDT|'||V_JYJG.BGXMID||'|'||V_JYJG.BGXMMC||'|'||V_JYJG.JYJG00||'|'||V_JYJG.JGDW00||'|'||V_JYJG.CKFW00||'|'||SP_JY_ESB_YXBS00(V_JYJG.JGID00)||'|'||VJYRXM0||'|'||VPYRQ00||'|非细菌||||||||||||'||chr(13); end loop; else select count(*) into VHS0000 from VW_JY_XJJG00 where JYYWID=VJYYWID; VJGXX00:='QAK|QTR001|查询回应情况|K13^QRY_Test_Report_Return^HL7|'||VHS0000||chr(13); VJGXX00:=VJGXX00||'QPD|K13^QRY_Test_Report_Return^HL7|QTR001'||chr(13); VJGXX00:=VJGXX00||'RDF|21|检验项目代码^ST^50~项目名称^ST^100~检验结果^ST^1000~结果单位^ST^50~参考值范围^ST^500~阳性标识名称^ST^100~检测负责人姓名^ST^50~检测时间^DTM^100~是否细菌^ST^10~细菌ID^ST^50~细菌描述^ST^500~培养描述^ST^500~抗生素ID^ST^50~药敏结果^ST^100~药敏数量^NM^20~药敏说明^ST^500~细菌结果^ST^500~药敏NE结果值^ST^100~菌落数^ST^100~异养菌^ST^100~是否多重耐药^ST^50'||chr(13); select count(*) into VYMSL00 from VW_JY_XJJG00 where KSSID0 is not null and trim(KSSID0)<>'' and JYYWID=VJYYWID; for V_XJJG in CUR_JY_XJJG00 loop VJGXX00:=VJGXX00||'RDT|||||||'||VJYRXM0||'|'||VPYRQ00||'|细菌|'||V_XJJG.XJID00||'|'||V_XJJG.XJMS00||'|'||V_XJJG.BBMC00||'|'||V_XJJG.KSZWMC||'|'||V_XJJG.YMJG00||'|'||VYMSL00||'|'||V_XJJG.YMSM00||'|'||V_XJJG.ZWMC00||'|'||V_XJJG.YMJGNE||'|'||V_XJJG.JLS000||'||'||V_XJJG.SFDCNY||chr(13); end loop; end if; return VJGXX00; end; / --%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% --反向查询诊疗项目ID create or replace function SF_JY_ESB_ZLXMID (PZLXMID in number) return varchar2 is VPTJKBH BM_ZLZD00.PTJKBH%type; -- MODIFICATION HISTORY -- Person Date Comments -- nixj 2016.06.02 create -- nixj 2016.06.02 LIS与集成平台交互 for LIS-20160608-001 begin select PTJKBH into VPTJKBH from BM_ZLZD00 where ZLXMID=PZLXMID; return VPTJKBH; end; / --%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% --第三方查询LIS报告明细 create or replace function SF_JY_ESB_REPORTLIST (PHL7000 IN CLOB) return clob is VJGXX00 CLOB; VHL7000 CLOB; VHHF000 char(1); VQPD000 VARCHAR2(4000); VBRID00 NUMBER(10); VHS0000 VARCHAR2(5); VBRXM00 JY_YW0000.BRXM00%TYPE; VKSRQ00 JY_YW0000.SQRQ00%TYPE; VJSRQ00 JY_YW0000.SQRQ00%TYPE; cursor CUR_JY_YW0000 is select BRXM00,JYYWID,replace(SQRQ00||SQSJ00,':','') SQRQ00,ZLXMID,ZLXMJC,replace(CBGRQ0||CBGSJ0,':','') CBGRQ0,BBMC00 from VW_jy_yw0000 where brid00=VBRID00 AND sqrq00>=VKSRQ00 AND SQRQ00<=VJSRQ00 AND JGZT00 IN('6','8'); --AND YQDH00<>trim('BI'); --Person Date comment --Nixj 2018.11.06 增加审核时间节点for LIS9-20181026-001 begin VHHF000:='@'; VHL7000:=translate(PHL7000,chr(13)||chr(10),'#@'); VQPD000:=substr(VHL7000,instr(VHL7000,VHHF000,1,1)+1); VBRID00:=substr(VQPD000,instr(VQPD000,'|',1,3)+1,instr(VQPD000,'|',1,4)-instr(VQPD000,'|',1,3)-1); VKSRQ00:=substr(VQPD000,instr(VQPD000,'|',1,9)+1,instr(VQPD000,'|',1,10)-instr(VQPD000,'|',1,9)-1); VJSRQ00:=substr(VQPD000,instr(VQPD000,'|',1,10)+1,8); --VJGXX00:='MSH|^~\'; --VJGXX00:=VJGXX00||'&|LIS|发送模块名称|第三方|接收模块名称|'||to_char(systimestamp, 'yyyymmddhh24miss.ff3')||'|N|RSP^K13^RSP_K13|QRY_Test_Report_List_Return-'||to_char(systimestamp, 'yyyymmddhh24missff3')||'|P|2.7'||CHR(13); select count(*) into VHS0000 from VW_jy_yw0000 where brid00=VBRID00 and sqrq00>=VKSRQ00 and SQRQ00<=VJSRQ00; VJGXX00:='QAK|QD01|查询回应情况|K13^QRY_Test_Report_List_Return^HL7|'||VHS0000||chr(13); VJGXX00:=VJGXX00||'QPD|K13^QRY_Test_Report_List_Return^HL7|QTRL1'||chr(13); VJGXX00:=VJGXX00||'RDF|8|病人ID^ST^50~病人姓名^ST^50~检查单号^ST^50~申请时间^DTM^50~申请项目代码^ST^50~申请项目名称^ST^100~审核时间^DTM^50~样本类型名称^ST^100'||chr(13); for V_VW_JY_YW0000 in CUR_JY_YW0000 loop VJGXX00:=VJGXX00||'RDT|'||VBRID00||'|'||V_VW_JY_YW0000.BRXM00||'|'||V_VW_JY_YW0000.JYYWID||'|'||V_VW_JY_YW0000.SQRQ00||'|'||SF_JY_ESB_ZLXMID(V_VW_JY_YW0000.ZLXMID)||'|'||V_VW_JY_YW0000.ZLXMJC||'|'||V_VW_JY_YW0000.CBGRQ0||'|'||V_VW_JY_YW0000.BBMC00||chr(13); end loop; return VJGXX00; end; / --%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% --接收体检更新患者信息 create or replace procedure SP_JY_ESB_TJGXBRXX ( PHL7000 in varchar2) as VHHF000 char(1); VPID000 varchar2(1000); VPV1000 varchar2(1000); VHL7000 varchar2(4000); VSTR000 varchar2(500); VTJH000 varchar2(15); VBRID00 JY_YW0000.BRID00%type; VBRXM00 JY_YW0000.BRXM00%type; VCSRQ00 JY_YW0000.CSRQ00%type; VXB0000 JY_YW0000.XB0000%type; VZYGHID JY_YW0000.ZYGHID%type; VZYHGHH JY_YW0000.ZYHGHH%type; VMZZYBZ JY_YW0000.MZZYBZ%type; VNL0000 JY_YW0000.NL0000%type; VNLDW00 varchar2(2); VCH0000 JY_YW0000.CH0000%type; -- MODIFICATION HISTORY -- Person Date Comments -- nixj 2016.06.02 create -- nixj 2016.06.02 LIS与集成平台交互 for LIS-20160608-001 begin VHHF000:='@'; VHL7000:=translate(PHL7000,chr(13)||chr(10),'#@'); VPID000:=substr(VHL7000,instr(VHL7000,VHHF000,1,2)+1,instr(VHL7000,VHHF000,1,3)-instr(VHL7000,VHHF000,1,2)-1); VSTR000:=substr(VPID000,instr(VPID000,'|',1,3)+1,instr(VPID000,'|',1,4)-instr(VPID000,'|',1,3)-1); VTJH000:=substr(VSTR000,0,instr(VSTR000,'^',1,1)-1); VBRXM00:=substr(VPID000,instr(VPID000,'|',1,5)+1,instr(VPID000,'|',1,6)-instr(VPID000,'|',1,5)-1); VCSRQ00:=substr(VPID000,instr(VPID000,'|',1,7)+1,instr(VPID000,'|',1,8)-instr(VPID000,'|',1,7)-1); VNL0000:=substr(SF_JY_CSRQTONL(VCSRQ00),0,length(SF_JY_CSRQTONL(VCSRQ00))-1); VNLDW00:=substr(SF_JY_CSRQTONL(VCSRQ00),-1); if(VNLDW00='岁') then VNLDW00:='Y'; elsif (VNLDW00='月') then VNLDW00:='M'; elsif (VNLDW00='天') then VNLDW00:='D'; end if; VXB0000:=substr(VPID000,instr(VPID000,'|',1,8)+1,instr(VPID000,'|',1,9)-instr(VPID000,'|',1,8)-1); if(VXB0000='1') then VXB0000:='M'; elsif (VXB0000='2') then VXB0000:='F'; else VXB0000:='W'; end if; VPV1000:=substr(VHL7000,instr(VHL7000,VHHF000,1,3)+1,instr(VHL7000,VHHF000,1,4)-instr(VHL7000,VHHF000,1,3)-1); VSTR000:=substr(VPV1000,instr(VPV1000,'|',1,2)+1,instr(VPV1000,'|',1,3)-instr(VPV1000,'|',1,2)-1); VMZZYBZ:='0'; --VZYGHID:=substr(VSTR000,3,instr(VSTR000,'^',1,2)-3); --VZYHGHH:=substr(VSTR000,instr(VSTR000,'^',1,2)+1,instr(VSTR000,'^',1,3)-instr(VSTR000,'^',1,2)-1); VSTR000:=substr(VPV1000,instr(VPV1000,'|',1,3)+1,instr(VPV1000,'|',1,4)-instr(VPV1000,'|',1,3)-1); --VCH0000:=substr(VSTR000,instr(VSTR000,'^',1,2)+1,instr(VSTR000,'^',1,3)-instr(VSTR000,'^',1,2)-1); select BRID00 into VBRID00 from BM_BRXXB0 where BRBLH0=rpad('TJ'||VTJH000,30,' ') and rownum=1; update BM_BRXXB0 set BRXM00=VBRXM00,BRXB00=VXB0000,BRCSRQ=VCSRQ00 where BRID00=VBRID00; update SF_BRXXB0 set XM0000=VBRXM00,XB0000=VXB0000,CSRQ00=VCSRQ00 where BRID00=VBRID00; update YJ_YW0000 set BRXM00=VBRXM00,XB0000=VXB0000,CSRQ00=VCSRQ00 where BRID00=VBRID00; update JY_YW0000 set BRXM00=VBRXM00,XB0000=VXB0000,CSRQ00=VCSRQ00,NL0000=VNL0000,NLDW00=VNLDW00,MZZYBZ=VMZZYBZ where BRID00=VBRID00; exception when others then RAISE_APPLICATION_ERROR(-20020, nvl(sqlerrm, '原因不明出错！*')); end; / --%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% --接收体检更新检验申请单 create or replace procedure SP_JY_ESB_TJGXSQD0 ( PHL7000 in varchar2) as VHHF000 varchar2(2); VPID000 varchar2(4000); VPV1000 varchar2(4000); VORC000 varchar2(4000); VHL7000 varchar2(4000); VHISYJH varchar2(30); VJGZT00 JY_YW0000.JGZT00%type; VSFBZ00 JY_YW0000.SFBZ00%type; VSQRQ00 JY_YW0000.SQRQ00%type; VSQSJ00 JY_YW0000.SQSJ00%type; VSQDZT0 varchar2(2); VTQ1000 varchar2(4000); VOBR000 varchar2(4000); VDG1000 varchar2(4000); -- MODIFICATION HISTORY -- Person Date Comments -- nixj 2016.06.02 create -- nixj 2016.06.02 LIS与集成平台交互 for LIS-20160608-001 begin VHHF000:='@'; VHL7000:=translate(PHL7000,chr(13)||chr(10),'#@'); VSQRQ00:=substr(VHL7000,instr(VHL7000,'|',1,6)+1,8); VSQSJ00:=to_char(to_date(substr(VHL7000,instr(VHL7000,'|',1,6)+1,14),'YYYYMMDDhh24:mi:ss'),'HH24:mi:ss'); --PID --VPID000:=substr(VHL7000,instr(VHL7000,VHHF000,1,1)+1,instr(VHL7000,VHHF000,1,2)-instr(VHL7000,VHHF000,1,1)); --PV1 --VPV1000:=substr(VHL7000,instr(VHL7000,VHHF000,1,2)+1,instr(VHL7000,VHHF000,1,3)-instr(VHL7000,VHHF000,1,2)); --ORC VORC000:=substr(VHL7000,instr(VHL7000,VHHF000,1,3)+1,instr(VHL7000,VHHF000,1,4)-instr(VHL7000,VHHF000,1,3)-1); VHISYJH:=substr(VORC000,instr(VORC000,'|',1,2)+1,instr(VORC000,'|',1,3)-instr(VORC000,'|',1,2)-1); VSQDZT0:=substr(VORC000,instr(VORC000,'|',1,5)+1,instr(VORC000,'|',1,6)-instr(VORC000,'|',1,5)-1); --LIS用以下数据：0=预约1=申请2=收费3=退费4=备管5=采集6＝签收7＝退回8＝排样9＝审核 11＝作废 12＝打印 13=冲销 14=撤销冲销 if VSQDZT0='2' then VSFBZ00:='2'; VJGZT00:='0'; elsif VSQDZT0='3' then VSFBZ00:='2'; VJGZT00:='7'; elsif VSQDZT0='11' then VSFBZ00:='6'; VJGZT00:='7'; elsif VSQDZT0='13' then VSFBZ00:='3'; VJGZT00:='7'; elsif VSQDZT0='14' then VSFBZ00:='3'; VJGZT00:='0'; end if; if (nvl(VSFBZ00,'-1')<>'-1') and (nvl(VJGZT00,'-1')<>'-1') then update JY_YW0000 set SFBZ00=VSFBZ00, JGZT00=VJGZT00 where YJDJH0=(select YJDJH0 from YJ_YW0000 where HISYJH=VHISYJH); end if; exception when others then RAISE_APPLICATION_ERROR(-20020, nvl(sqlerrm, '原因不明出错！*')); end; / --%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% --接收体检检验申请单 create or replace procedure SP_JY_ESB_TJJSSQD ( PHL7000 in varchar )as VTEST varchar2(200); VSQRQ00 JY_YW0000.SQRQ00%type; VSQSJ00 JY_YW0000.SQSJ00%type; VSTR000 varchar2(1000); VHHF000 varchar2(2); VPID000 varchar2(1000); VTJH000 varchar2(15); VFBBH00 BM_BRFBB0.FBBH00%type; VJGZT00 JY_YW0000.JGZT00%type; VBRID00 JY_YW0000.BRID00%type; VBRXM00 JY_YW0000.BRXM00%type; VXB0000 JY_YW0000.XB0000%type; VCSRQ00 JY_YW0000.CSRQ00%type; VNL0000 JY_YW0000.NL0000%type; VTJCZY0 BM_YGBM00.YGBH00%type; --VNLDW00 JY_YW0000.NLDW00%type; VNLDW00 varchar(2); VHL7000 varchar2(4000); VHISYJH varchar2(36); VPV1000 varchar2(1000); V_COUNT0 number; VZJE000 number; VMZZYBZ JY_YW0000.MZZYBZ%type; VZYGHID JY_YW0000.ZYGHID%type; VZYHGHH JY_YW0000.ZYHGHH%type; VSQKS00 JY_YW0000.SQKS00%type; VSQBQ00 JY_YW0000.SQBQ00%type; VCH0000 JY_YW0000.CH0000%type; VYGBH00 JY_YW0000.SQYS00%type; VSQYS00 BM_YGBM00.XKH000%type; VYJDJH0 JY_YW0000.YJDJH0%type; VYJFZH0 JY_YW0000.YJFZH0%type; VZDID00 JY_YW0000.ZDID00%type; VJYYWID JY_YW0000.JYYWID%type; VYXBZ00 JY_YW0000.YXBZ00%type; VRLY000 JY_YW0000.LRY000%type; VSFBZ00 JY_YW0000.SFBZ00%type; VYEXM00 JY_YW0000.YEXM00%type; VTXM000 varchar2(15); VZLXMJC BM_ZLZD00.ZLXMJC%type; VJKBH01 BM_ZLZD00.JKBH01%type; VZLXMID BM_ZLZD00.ZLXMID%type; VBBJKBH BM_BBZD00.JKBH00%type; VBBID00 BM_BBZD00.BBID00%type; VYJKSBH XT_XTCS00.VALUE0%type; VZHXMCF XT_XTCS00.VALUE0%type; VSFQS00 XT_XTCS00.VALUE0%type; VCOUNT0 number; VORC000 varchar2(1000); VTQ1000 varchar2(1000); VOBR000 varchar2(1000); VDG1000 varchar2(1000); VOBX000 varchar2(1000); cs exception; VERR varchar2(100); cursor CUR_ZHXM00 IS select distinct ZHXMID,ZLXMID,TDJTTM from XT_ZHMX00 where ZHXMID=VZLXMID; -- MODIFICATION HISTORY -- Person Date Comments -- nixj 2016.06.02 create -- nixj 2016.06.02 LIS与集成平台交互 for LIS-20160608-001 -- nixj 2016.06.16 体检修改发送人员格式，检验跟着修改 for LIS-20160616-001 -- nixj 2016.09.02 增加当体检开单医生在HIS不存在时返回错误给集成平台及年龄未写入错误 for LIS-20160914-002 begin --insert into jy_test values('1',PHL7000); --commit; --VHHF000:=CHR(13); VHL7000:=translate(PHL7000,chr(13)||chr(10),'#@'); VHHF000:='@'; VSQRQ00:=substr(VHL7000,instr(VHL7000,'|',1,6)+1,8); VSQSJ00:=to_char(to_date(substr(VHL7000,instr(VHL7000,'|',1,6)+1,14),'YYYYMMDDhh24:mi:ss'),'HH24:mi:ss'); --PID VPID000:=substr(VHL7000,instr(VHL7000,VHHF000,1,1)+1,instr(VHL7000,VHHF000,1,2)-instr(VHL7000,VHHF000,1,1)-1); VTJH000:=substr(VPID000,7,instr(VPID000,'^',1,1)-7); --VBRID00:=substr(VPID000,7,instr(VPID000,'^',1,1)-7); --insert into jy_test values('1.1',VHL7000); --commit; VSTR000:=substr(VPID000,instr(VPID000,'|',1,5)+1,instr(VPID000,'|',1,6)-instr(VPID000,'|',1,5)-1); VBRXM00:=VSTR000; --VYEXM00:=substr(VSTR000,instr(VSTR000,'^',1,1)+1,instr(VSTR000,'^',1,2)-instr(VSTR000,'^',1,1)-1); VXB0000:=substr(VPID000,instr(VPID000,'|',1,8)+1,instr(VPID000,'|',1,9)-instr(VPID000,'|',1,8)-1); --insert into jy_test values('1.2',VXB0000); --commit; if(VXB0000='1') then VXB0000:='M'; elsif (VXB0000='2') then VXB0000:='F'; else VXB0000:='W'; end if; --VCSRQ00:=substr(VPID000,instr(VPID000,'|',1,7)+1,instr(VPID000,'|',1,8)-instr(VPID000,'|',1,7)-1); VCSRQ00:=substr(VPID000,instr(VPID000,'|',1,7)+1,instr(VPID000,'|',1,8)-instr(VPID000,'|',1,7)-1); VNL0000:=substr(SF_JY_CSRQTONL(VCSRQ00),0,length(SF_JY_CSRQTONL(VCSRQ00))-1); VNLDW00:=substr(SF_JY_CSRQTONL(VCSRQ00),-1); if(VNLDW00='岁') then VNLDW00:='Y'; elsif (VNLDW00='月') then VNLDW00:='M'; elsif (VNLDW00='天') then VNLDW00:='D'; end if; --查询BM_BRXXB select nvl(max(FBBH00),null) into VFBBH00 from BM_BRFBB0 where FBMC00 = '体检'; if VFBBH00 is null then VERR := '请先维护体检费别'; raise cs; end if; select nvl(VALUE0,null) into VTJCZY0 from xt_xtcs00 where name00='JY_TJBRXXCZY' ; if VTJCZY0 is null then VERR := '请先设置体检病人信息录入默认员工编号'; raise cs; end if; begin select BRID00 into VBRID00 from BM_BRXXB0 where BRBLH0=rpad('TJ'||VTJH000,30,' ') and rownum=1; update BM_BRXXB0 set FBBH00=99,BRXM00=trim(VBRXM00) ,BRXB00=trim(VXB0000),BRCSRQ=trim(VCSRQ00) where BRID00=VBRID00; exception when NO_DATA_FOUND then begin select SQ_BM_BRXXB0_BRID00.nextval into VBRID00 from DUAL; insert into BM_BRXXB0(BRID00,BRBLH0,BRXM00,BRXB00,BRCSRQ,FBBH00,YBLB00,CZY000,CZRQ00,CZSJ00,ZYZT00) values(VBRID00,'TJ'||trim(VTJH000),VBRXM00,VXB0000,VCSRQ00,VFBBH00, 0,VTJCZY0,to_char(sysdate,'YYYYMMDD'),to_char(sysdate,'HH24:MI:SS'), '0'); end; end; select count(*) into V_COUNT0 from SF_BRXXB0 where GHH000=RPAD('TJ'||VTJH000,12,' '); if V_COUNT0 <> 0 then select max(GHID00) into VZYGHID from SF_BRXXB0 where GHH000=RPAD('TJ'||VTJH000,12,' '); update SF_BRXXB0 set FBBH00=VFBBH00,XM0000=trim(VBRXM00),XB0000=trim(VXB0000),CSRQ00=trim(VCSRQ00) where GHID00=VZYGHID; else select SQ_SF_BRXXB0_GHID00.nextval into VZYGHID from DUAL; insert into SF_BRXXB0(GHID00,BRBLH0,GHH000,GHLB00,BRID00,XM0000,XB0000,CSRQ00, FBBH00,LBBH00,GHRQ00,GHSJ00,GHY000,GHYXM0,GHKS00,JZRQ00,JZSJ00,JZKS00,CZRQ00,CZSJ00) values( VZYGHID,'TJ'||VTJH000,'TJ'||VTJH000,'1',VBRID00,VBRXM00,trim(VXB0000),trim(VCSRQ00), VFBBH00,'1',to_char(sysdate,'YYYYMMDD'),to_char(sysdate,'hh24:mi:ss'), VTJCZY0,'体检划拨',1024, to_char(sysdate,'YYYYMMDD'),to_char(sysdate,'hh24:mi:ss'),1024, to_char(sysdate,'YYYYMMDD'),to_char(sysdate,'HH24:MI:SS')); end if; select GHH000,BRID00,XM0000,XB0000,CSRQ00 into VZYHGHH,VBRID00,VBRXM00,VXB0000,VCSRQ00 from SF_BRXXB0 where GHID00=VZYGHID; --PV1 VPV1000:=substr(VHL7000,instr(VHL7000,VHHF000,1,2)+1,instr(VHL7000,VHHF000,1,3)-instr(VHL7000,VHHF000,1,2)-1); VSTR000:=substr(VPV1000,instr(VPV1000,'|',1,2)+1,instr(VPV1000,'|',1,3)-instr(VPV1000,'|',1,2)-1); VMZZYBZ:='0'; --VZYGHID:=substr(VSTR000,3,instr(VSTR000,'^',1,2)-3); --VZYHGHH:=substr(VSTR000,instr(VSTR000,'^',1,2)+1,instr(VSTR000,'^',1,3)-instr(VSTR000,'^',1,2)-1); VSTR000:=substr(VPV1000,instr(VPV1000,'|',1,3)+1,instr(VPV1000,'|',1,4)-instr(VPV1000,'|',1,3)-1); --VCH0000:=substr(VSTR000,instr(VSTR000,'^',1,2)+1,instr(VSTR000,'^',1,3)-instr(VSTR000,'^',1,2)-1); --insert into jy_test values('2',VPV1000); --commit; --ORC VORC000:=substr(VHL7000,instr(VHL7000,VHHF000,1,3)+1,instr(VHL7000,VHHF000,1,4)-instr(VHL7000,VHHF000,1,3)-1); --insert into jy_test values('3',VORC000); --commit; VHISYJH:=substr(VORC000,instr(VORC000,'|',1,2)+1,instr(VORC000,'|',1,3)-instr(VORC000,'|',1,2)-1); --insert into jy_test values('3.1',to_char(VYJDJH0)); --commit; VYJFZH0:=substr(VORC000,instr(VORC000,'|',1,4)+1,instr(VORC000,'|',1,5)-instr(VORC000,'|',1,4)-1); --insert into jy_test values('3.2',VYJFZH0); --commit; VSFBZ00:=substr(VORC000,instr(VORC000,'|',1,5)+1,instr(VORC000,'|',1,6)-instr(VORC000,'|',1,5)-1); --insert into jy_test values('3.3',VSFBZ00); --commit; VSTR000:=substr(VORC000,instr(VORC000,'|',1,11)+1,instr(VORC000,'|',1,12)-instr(VORC000,'|',1,11)-1); VRLY000:=substr(VSTR000,0,instr(VSTR000,'^',1,1)-1); VSTR000:=substr(VORC000,instr(VORC000,'|',1,12)+1,instr(VORC000,'|',1,13)-instr(VORC000,'|',1,12)-1); VSQYS00:=substr(VSTR000,0,instr(VSTR000,'^',1,1)-1); VSTR000:=substr(VORC000,instr(VORC000,'|',1,13)+1,instr(VORC000,'|',1,14)-instr(VORC000,'|',1,13)-1); VTEST:=substr(VSTR000,4,instr(VSTR000,'^',1,4)-4); if(length(VTEST)>'0') then VSQBQ00:=to_number(VTEST); end if; VSQKS00:=1024; --insert into jy_test values('4',VSQKS00); --commit; --TQ1 VTQ1000:=substr(VHL7000,instr(VHL7000,VHHF000,1,4)+1,instr(VHL7000,VHHF000,1,5)-instr(VHL7000,VHHF000,1,4)); VYXBZ00:=substr(VTQ1000,instr(VOBR000,'|',1,9)+1,1); if(VYXBZ00='A') then VYXBZ00:='Y'; else VYXBZ00:='N'; end if; --OBR VOBR000:=substr(VHL7000,instr(VHL7000,VHHF000,1,5)+1,instr(VHL7000,VHHF000,1,6)-instr(VHL7000,VHHF000,1,5)); VJKBH01:=substr(VOBR000,instr(VOBR000,'|',1,4)+1,instr(VOBR000,'^',1,1)-instr(VOBR000,'|',1,4)-1); VTXM000:=substr(VOBR000,instr(VOBR000,'|',1,37)+1,instr(VOBR000,'|',1,38)-instr(VOBR000,'|',1,37)-1); --insert into jy_test values('4.1',VJKBH01); --commit; VSTR000:=substr(VOBR000,instr(VOBR000,'|',1,23)+1,instr(VOBR000,'|',1,24)-instr(VOBR000,'|',1,23)-1); if instr(VSTR000,'^',1,1)>0 then VZJE000:=to_number(substr(VSTR000,1,instr(VSTR000,'^',1,1)-1)); else VZJE000:=to_number(VSTR000); end if ; begin select ZLXMID into VZLXMID from BM_ZLZD00 where trim(zlxmbh)=VJKBH01; select ZLXMJC into VZLXMJC from BM_ZLZD00 where trim(zlxmbh)=VJKBH01; exception when others then VZLXMID:=0; end; VSTR000:=substr(VOBR000,instr(VOBR000,'|',1,46)+1); VBBJKBH:=substr(VSTR000,0,instr(VSTR000,'^',1,1)-1); begin select BBID00 into VBBID00 from BM_BBZD00 where JKBH00=VBBJKBH; exception when others then VBBID00:=''; end; select nvl(VALUE0,null) into VYJKSBH from XT_XTCS00 where NAME00='PE_JYZXKSBH'; if VYJKSBH is null then VERR := '请先设置检验科室编号'; raise cs; end if; begin select YGBH00 into VYGBH00 from BM_YGBM00 where XKH000=VSQYS00; exception when others then VERR := '申请员工不存在'; raise cs; end; --写入yj_yw0000表 select SQ_YJ_YW0000_YJDJH0.nextval into VYJDJH0 from DUAL; insert into YJ_YW0000(YJDJH0,YJLRBZ,HISYJH,BRID00,ZYGHID,ZYHGHH,MZZYBZ,BRXM00,XB0000,CSRQ00,ZLXMID,ZLXMJC, SL0000,KDYS00,KDYSXM,KDKSBH,YSSZKS,KDRQ00,KDSJ00,JZBZ00,ZDID00,SFDJH0,KDBQ00,CH0000,XMZT00,ZJE000,YZCFID,LRXM00,BBID00,yjksbh) select VYJDJH0,'T', VHISYJH , VBRID00, VZYGHID, 'TJ'||trim(VTJH000), '0', trim(VBRXM00), trim(VXB0000), trim(VCSRQ00), VZLXMID, VZLXMJC, 1, YGBH00, ZWXM00, 40,40, VSQRQ00, VSQSJ00, 'N', null, 99999999, 40, null, '3', VZJE000, 1, ZWXM00,null,VYJKSBH from BM_YGBM00 where XKH000=VSQYS00; --DG1 VDG1000:=substr(VHL7000,instr(VHL7000,VHHF000,1,6)+1); VZDID00:=substr(VDG1000,instr(VDG1000,'|',1,3)+1,instr(VDG1000,'^',1,1)-instr(VDG1000,'|',1,3)-1); --insert into jy_test values('5',VDG1000); --commit; --OBX --VOBX000:=substr(VHL7000,instr(VHL7000,VHHF000,1,7)+1,instr(VHL7000,VHHF000,1,8)-instr(VHL7000,VHHF000,1,7)); begin select trim(VALUE0) into VZHXMCF from XT_XTCS00 where NAME00='JY_ZHXMSFCF'; exception when others then VZHXMCF := 'N'; end; --insert into jy_test values('6',VDG1000); --commit; select value0 into VSFQS00 from XT_XTCS00 where NAME00='JY_TJSFQS'; if VSFQS00<>'N' then VJGZT00:='1'; else VJGZT00:='2'; end if; select count(*) into VCOUNT0 from JY_YW0000 where YJDJH0=VYJDJH0; if VCOUNT0<>0 then RAISE_APPLICATION_ERROR(-20019,'申请信息已经存在！'); end if; select count(*) into VCOUNT0 from XT_ZHMX00 where ZHXMID=VZLXMID; if (VZHXMCF = 'Y') and (VCOUNT0 > 1) then for V_ZHXM00 in CUR_ZHXM00 loop select SEQ_JY_YW0000_LSID00.nextval into VJYYWID from DUAL; insert into JY_YW0000 (JYYWID,YJDJH0,ZYHGHH,BRID00,YJFZH0,ZLXMID,ZYGHID,MZZYBZ,YXBZ00,LRY000, ZDID00,BBID00,SQRQ00,SQSJ00,SQKS00,SQBQ00,SQYS00,JYDLY0,JGZT00,SFBZ00, BRXM00,YEXM00,XB0000,CSRQ00,CH0000,SFTJ00,TXM000,NL0000,NLDW00) values (VJYYWID,VYJDJH0,'TJ'||VTJH000,nvl(VYJFZH0,0),VBRID00,V_ZHXM00.ZLXMID,VZYGHID,VMZZYBZ,VYXBZ00, VRLY000,VZDID00,VBBID00,VSQRQ00,VSQSJ00,VSQKS00, VSQBQ00,VYGBH00,VMZZYBZ,VJGZT00, '3',VBRXM00,VYEXM00,VXB0000,VCSRQ00,'','Y',VTXM000,VNL0000,VNLDW00); insert into JY_YWMX00(JYMXID,JYYWID,ZLXMID) select SEQ_JY_YWMX00_JYMXID.nextval,VJYYWID,V_ZHXM00.ZLXMID from dual where not exists(select 1 from JY_YWMX00 where JYYWID=VJYYWID and ZLXMID=V_ZHXM00.ZLXMID); end loop; else select SEQ_JY_YW0000_LSID00.nextval into VJYYWID from DUAL; insert into JY_YW0000 (JYYWID,YJDJH0,ZYHGHH,BRID00,YJFZH0,ZLXMID,ZYGHID,MZZYBZ,YXBZ00,LRY000, ZDID00,BBID00,SQRQ00,SQSJ00,SQKS00,SQBQ00,SQYS00,JYDLY0,JGZT00,SFBZ00, BRXM00,YEXM00,XB0000,CSRQ00,CH0000,SFTJ00,TXM000,NL0000,NLDW00) values (VJYYWID,VYJDJH0,'TJ'||VTJH000,VBRID00,nvl(VYJFZH0,0),VZLXMID,VZYGHID,VMZZYBZ,VYXBZ00, VRLY000,VZDID00,VBBID00,VSQRQ00,VSQSJ00,VSQKS00, VSQBQ00,VYGBH00,VMZZYBZ,VJGZT00, '3',VBRXM00,VYEXM00,VXB0000,VCSRQ00,'','Y',VTXM000,VNL0000,VNLDW00); insert into JY_YWMX00(JYMXID,JYYWID,ZLXMID) select SEQ_JY_YWMX00_JYMXID.nextval,VJYYWID,VZLXMID from dual where not exists(select 1 from JY_YWMX00 where JYYWID=VJYYWID and ZLXMID=VZLXMID); end if; exception when cs then RAISE_APPLICATION_ERROR(-20021, nvl('', 'cs*')||VTJH000||VERR); when others then RAISE_APPLICATION_ERROR(-20020, nvl(sqlerrm, '原因不明出错！*')); end; / --%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% --接收体检危急值确认 create or replace procedure SP_JY_ESB_TJWJZQR ( PHL7000 in varchar2) as VHHF000 char(1); VOBR000 varchar2(1000); VHL7000 varchar2(4000); VHISYJH varchar2(15); VYJDJH0 JY_YW0000.YJDJH0%type; VBGCKR0 JY_YW0000.BGCKR0%type; VBGCKSJ JY_YW0000.BGCKSJ%type; VSTR000 varchar2(500); -- MODIFICATION HISTORY -- Person Date Comments -- nixj 2016.06.02 create -- nixj 2016.06.02 LIS与集成平台交互 for LIS-20160608-001 begin VHHF000:='@'; VHL7000:=translate(PHL7000,chr(13)||chr(10),'#@'); --OBR VOBR000:=substr(VHL7000,instr(VHL7000,VHHF000,1,4)+1); VHISYJH:=substr(VOBR000,instr(VOBR000,'|',1,2)+1,instr(VOBR000,'|',1,3)-instr(VOBR000,'|',1,2)-1); VSTR000:=substr(VOBR000,instr(VOBR000,'|',1,12)+1); VBGCKR0:=substr(VSTR000,instr(VSTR000,'^',1,1)+1,instr(VSTR000,'^',1,2)-instr(VSTR000,'^',1,1)-1); VBGCKSJ:=substr(VSTR000,instr(VSTR000,'^',1,2)+1,instr(VSTR000,'^',1,3)-instr(VSTR000,'^',1,2)-1)||substr(VSTR000,instr(VSTR000,'^',1,3)+1,instr(VSTR000,'^',1,4)-instr(VSTR000,'^',1,3)-1); VBGCKSJ:=to_char(to_date(VBGCKSJ,'YYYYMMDD hh24:mi:ss'),'YYYYMMDD hh24:mi:ss'); select YJDJH0 into VYJDJH0 from YJ_YW0000 where HISYJH=VHISYJH; update JY_YW0000 set BGCKR0=VBGCKR0,BGCKSJ=VBGCKSJ where YJDJH0=VYJDJH0; exception when others then RAISE_APPLICATION_ERROR(-20020, nvl(sqlerrm, '原因不明出错！*')); end; / --%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%55 --体检检验结果发送2 create or replace function SF_JY_ESB_TJJYJGXX ( PJYYWID in number, PYBH000 in varchar2, PPYRQ00 in char, PMZZYBZ in char, PZYGHID in number ) return clob is VXXJG00 clob; VYJDJH0 JY_YW0000.YJDJH0%type;--医技单据号 申请单号 VZDID00 JY_YW0000.ZDID00%type; VBGR000 JY_YW0000.BGR000%type; VBGRXM0 BM_YGBM00.ZWXM00%type; VBGJYR0 JY_YW0000.BGJYR0%type; VJYRXM0 BM_YGBM00.ZWXM00%type; VTXM000 JY_YW0000.TXM000%type; VBG0000 char(14); VPY0000 char(14); VZDMC00 varchar2(100); VRYCWH0 varchar(10); VRYBQ00 ZY_BRXXB0.RYBQ00%type; VRZKS00 ZY_BRXXB0.RZKS00%type; VJKBH01 BM_ZLZD00.JKBH01%type; VZLXMJC BM_ZLZD00.ZLXMJC%type; VJKBH00 JY_BGXMZD.JKBH00%type; VZX0000 char(14); VKDYS00 YJ_YW0000.KDYS00%type; VKDYSXM YJ_YW0000.KDYSXM%type; VYYID00 XT_YYXX00.YYID00%type; VYYJC00 XT_YYXX00.YYJC00%type; VXH0000 number(3,0); VYMSL00 number(3,0); cursor CUR_JY_JYJG00 is select BGXMID,BGXMMC,BZBM00,JYJG00,JGDW00,CKXX00,CKSX00,JJZXX0,JJZSX0,JGID00 from VW_JY_JYJG00 where JYYWID=PJYYWID; cursor CUR_JY_XJJG00 is select XJID00,XJMS00,BBMC00,KSSID0,KSZWMC,YMJG00,YMSM00,ZWMC00,YMJGNE,JLS000,SFDCNY from VW_JY_XJJG00 where JYYWID=PJYYWID; -- MODIFICATION HISTORY -- Person Date Comments -- nixj 2016.06.02 create -- nixj 2016.06.02 LIS与集成平台交互 for LIS-20160608-001 begin --OBR部分 select YJDJH0,TXM000,ZDID00,ZDMC00,CBGRQ0||replace(CBGSJ0,':',''),PYRQ00||replace(PYSJ00,':',''),BGR000,BGRXM0,BGJYR0,JYRXM0 into VYJDJH0,VTXM000,VZDID00,VZDMC00,VBG0000,VPY0000,VBGR000,VBGRXM0,VBGJYR0,VJYRXM0 from VW_JY_YW0000 where JYYWID=PJYYWID; if (PMZZYBZ='1') then select trim(RYCWH0),RYBQ00,RZKS00 into VRYCWH0,VRYBQ00,VRZKS00 from ZY_BRXXB0 where ZYID00=PZYGHID; end if; select PTJKBH,ZLXMJC into VJKBH01,VZLXMJC from BM_ZLZD00 where ZLXMID=(select ZLXMID from JY_YW0000 where JYYWID=PJYYWID); VXXJG00:=VXXJG00||'OBR||'||VYJDJH0||'|'||PJYYWID||'|'||VJKBH01||'^'||VZLXMJC||'|||||||||' ||VZDID00||'^'||VZDMC00||'|||||||||||||||||||'||VBGJYR0||'&'||VJYRXM0||'||'||VBGR000||'&'||VBGRXM0||'|||'||VTXM000||'||||||||||||||||||'||VBG0000||'||'||chr(10); --ORC部分 select ZXRQ00||replace(ZXSJ00,':',''),YYID00,KDYS00,KDYSXM into VZX0000,VYYID00,VKDYS00,VKDYSXM from YJ_YW0000 where YJDJH0=VYJDJH0; --select YYJC00 into VYYJC00 from XT_YYXX00 where YYID00=VYYID00; VXXJG00:=VXXJG00||'ORC|NW|'||VYJDJH0||'|||'||SP_JY_ESB_SQDZT0(PJYYWID)||'||||||||^^'||VRYCWH0||'^'||VRYBQ00||'^'||VRZKS00||'||'||VZX0000||'||'||VYYID00||'^'||VYYJC00||'||'||VKDYS00||'^'||VKDYSXM||'||||||||||'||chr(10); --OBX部分 VXH0000:=0; for V_JY_JYJG00 in CUR_JY_JYJG00 loop select BZBM00 into VJKBH00 from JY_BGXMZD where BGXMID=V_JY_JYJG00.BGXMID; VXXJG00:=VXXJG00||'OBX|'||VXH0000||'||'||V_JY_JYJG00.BZBM00||'^'||V_JY_JYJG00.BGXMMC ||'^|'||PYBH000||'|'||V_JY_JYJG00.JYJG00||'|'||V_JY_JYJG00.JGDW00||'|'||V_JY_JYJG00.CKXX00 ||'^'||V_JY_JYJG00.CKSX00||'^'||V_JY_JYJG00.JJZXX0||'^'||V_JY_JYJG00.JJZSX0||'|' ||SP_JY_ESB_YXBS00(V_JY_JYJG00.JGID00)||'|||U||非细菌|'||VPY0000||'|'||VYYID00||'^'||VYYJC00 ||'|'||VBGJYR0||'^'||VJYRXM0||'|||||||||||||||||||||||'||chr(10); VXH0000:=VXH0000+1; end loop; select count(*) into VYMSL00 from VW_JY_XJJG00 where KSSID0 is not null and trim(KSSID0)<>''; for V_JY_XJJG00 in CUR_JY_XJJG00 loop VXXJG00:=VXXJG00||'OBX|'||VXH0000||'||'||V_JY_XJJG00.XJID00||'^'||V_JY_XJJG00.XJMS00||'|'||PYBH000||'|||||||U|||细菌||||||||||||||'||V_JY_XJJG00.XJID00 ||'|'||V_JY_XJJG00.XJMS00||'|'||V_JY_XJJG00.BBMC00||'|'||V_JY_XJJG00.KSZWMC ||'|'||V_JY_XJJG00.YMJG00||'|'||VYMSL00||'|'||V_JY_XJJG00.YMSM00||'||' ||V_JY_XJJG00.ZWMC00||'|'||V_JY_XJJG00.YMJGNE||'|'||V_JY_XJJG00.JLS000||'||'||V_JY_XJJG00.SFDCNY||chr(10); VXH0000:=VXH0000+1; end loop; return trim(VXXJG00); end; / --%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% --体检检验结果发送1 create or replace function SP_JY_ESB_TJJYJG ( PPYRQ00 in char, PYBH000 in char )return clob is VXXJG00 clob; VBRXM00 BM_BRXXB0.BRXM00%type; VTJH000 varchar2(15); VMZZYBZ JY_YW0000.MZZYBZ%type; VZYGHID JY_YW0000.ZYGHID%type; VZYH000 varchar(12); VRYCWH0 varchar(10); VRYBQ00 ZY_BRXXB0.RYBQ00%type; VRZKS00 ZY_BRXXB0.RZKS00%type; cursor CUR_JY_YW0000 is select JYYWID from JY_YW0000 where PYRQ00=PPYRQ00 and YBH000=PYBH000; -- MODIFICATION HISTORY -- Person Date Comments -- nixj 2016.06.02 create -- nixj 2016.06.02 LIS与集成平台交互 for LIS-20160608-001 begin VXXJG00:='MSH|^~\'; VXXJG00:=VXXJG00||'&|LIS|发送模块名称|PEIS|接收模块名称|'||to_char(systimestamp, 'yyyymmddhh24miss.ff3')|| '|T|OUL^R24^OUL_R24|Test_Report_Send_PEIS-'||to_char(systimestamp, 'yyyymmddhh24missff3')||'|P|2.7'||chr(10); select MZZYBZ,ZYGHID,substr(ZYHGHH,3) into VMZZYBZ,VZYGHID,VTJH000 from VW_JY_YW0000 where PYRQ00=PPYRQ00 and YBH000=PYBH000 and rownum=1; --PID及PV1部分 select XM0000 into VBRXM00 from SF_BRXXB0 where GHID00=VZYGHID; VXXJG00:=VXXJG00||'PID|||'||trim(VTJH000)||'||'||VBRXM00||chr(10)||'PV1|1|4^'||VTJH000||'|||||||||||'||chr(10); for V_JY_JYYWID in CUR_JY_YW0000 loop VXXJG00:=VXXJG00||SF_JY_ESB_TJJYJGXX(V_JY_JYYWID.JYYWID,PYBH000,PPYRQ00,VMZZYBZ,VZYGHID); end loop; return VXXJG00; end; / --%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% --发送体检检验申请单更新 create or replace function SP_JY_ESB_TJGXSQD1 ( PJYYWID in number) return varchar2 is VXXJG00 varchar2(4000); VJGZT00 JY_YW0000.JGZT00%type; vtjh000 varchar2(15); VHISYJH varchar2(30); VBRID00 JY_YW0000.BRID00%type; VBRXM00 JY_YW0000.BRXM00%type; VMZZYBZ JY_YW0000.MZZYBZ%type; VZYGHID JY_YW0000.ZYGHID%type; VZYHGHH JY_YW0000.ZYHGHH%type; VYJDJH0 JY_YW0000.YJDJH0%type; VSQRQSJ char(14); VSQYS00 JY_YW0000.SQYS00%type; VCZY000 JY_YW0000.CZY000%type; VSQBQ00 JY_YW0000.SQBQ00%type; VSQKS00 JY_YW0000.SQKS00%type; VYXBZ00 JY_YW0000.YXBZ00%type; VZLXMID JY_YW0000.ZLXMID%type; VZLXMJC BM_ZLZD00.ZLXMJC%type; VJKBH01 BM_ZLZD00.JKBH01%type; VPYRQSJ char(14); VBBCJR0 JY_YW0000.BBCJR0%type; VTXM000 JY_YW0000.TXM000%type; VBBID00 JY_YW0000.BBID00%type; VBBMC00 BM_BBZD00.BBMC00%type; VJKBH00 BM_BBZD00.JKBH00%type; -- MODIFICATION HISTORY -- Person Date Comments -- nixj 2016.06.02 create -- nixj 2016.06.02 LIS与集成平台交互 for LIS-20160608-001 begin select JGZT00,BRXM00,BRID00,MZZYBZ,ZYGHID,substr(ZYHGHH,3),YJDJH0,SQRQ00||replace(SQSJ00,':',''),SQYS00,CZY000,SQBQ00,SQKS00,YXBZ00,ZLXMID ,PYRQ00||replace(PYSJ00,':',''),BBCJR0,TXM000,BBID00 into VJGZT00,VBRXM00,VBRID00,VMZZYBZ,VZYGHID,VTJH000 ,VYJDJH0,VSQRQSJ,VSQYS00,VCZY000,VSQBQ00,VSQKS00,VYXBZ00,VZLXMID,VPYRQSJ,VBBCJR0,VTXM000,VBBID00 from JY_YW0000 where JYYWID=PJYYWID; select HISYJH into VHISYJH from YJ_YW0000 where YJDJH0=VYJDJH0; VXXJG00:=VXXJG00||'MSH|^~\'; VXXJG00:=VXXJG00||'&|LIS|发送模块名称|PEIS|接收模块名称|'||to_char(systimestamp, 'yyyymmddhh24miss.ff3')|| '|T|OML^O21^OML_O21|Test_Form_Update_PEIS-'||to_char(systimestamp, 'yyyymmddhh24missff3')||'|P|2.7'||chr(13); --PID VXXJG00:=VXXJG00||'PID|||'||VTJH000||'||'||VBRXM00||chr(13); --PV1 VMZZYBZ:='4'; VXXJG00:=VXXJG00||'PV1|1|'||VMZZYBZ||'^'||VTJH000||'|||||||||||||||||||||||||||||||||||||||'||chr(13); --ORC VXXJG00:=VXXJG00||'ORC|XO|'||VHISYJH||'|||'||SP_JY_ESB_SQDZT0(PJYYWID)||'||||'||VSQRQSJ||'|'||VCZY000||'^||'||VSQYS00||'^|^^^'||VSQBQ00||'^'||VSQKS00||'^||||||||||||'||VCZY000||'^||||||||'||chr(13); --TQ1 if(VYXBZ00='Y') then VYXBZ00:='A'; elsif(VYXBZ00='N') then VYXBZ00:='R'; end if; --TQ1 VXXJG00:=VXXJG00||'TQ1||1|||||||'||VYXBZ00||chr(13); --OBR begin select BBMC00,JKBH00 into VBBMC00,VJKBH00 from BM_BBZD00 where BBID00=VBBID00; exception when others then VBBMC00:=''; VJKBH00:=''; end; begin select ZLXMJC,PTJKBH into VZLXMJC,VJKBH01 from BM_ZLZD00 where ZLXMID=VZLXMID; exception when others then VZLXMJC:=''; VJKBH01:=''; end; VXXJG00:=VXXJG00||'OBR|1|'||VHISYJH||'||'||VJKBH01||'^'||VZLXMJC||'|||'||VPYRQSJ||'|||||| |||'||VSQYS00||'^||||||||||||||||||'||VBBCJR0||'&^^^^^^^^|||'||VTXM000||'||||||'||VJKBH00||'^'||VBBMC00; return VXXJG00; end; / --%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% --体检危急值发送 create or replace function SP_JY_ESB_TJJYWJZ (PJYYWID in number) return varchar2 is VXXJG00 varchar2(4000); VBRXM00 varchar2(40); VTJH000 varchar2(15); VBG0000 char(14); VYJDJH0 JY_YW0000.YJDJH0%type; VHISYJH varchar2(30); VYBH000 JY_YW0000.YBH000%type; VXH0000 number(3); VJKBH01 BM_ZLZD00.JKBH01%type; VZLXMJC BM_ZLZD00.ZLXMJC%type; VJKBH00 JY_BGXMZD.JKBH00%type; VZWMC00 JY_BGXMZD.ZWMC00%type; cursor CUR_JY_WJZJL0 is select ZLXMID,BGXMID,JYJG00,BGRQ00,BGSJ00 from JY_JYJG00 where SFCJJZ='Y' and JYYWID=PJYYWID; -- MODIFICATION HISTORY -- Person Date Comments -- nixj 2016.06.02 create -- nixj 2016.06.02 LIS与集成平台交互 for LIS-20160608-001 begin select BRXM00,substr(ZYHGHH,3),YJDJH0,YBH000,CBGRQ0||replace(CBGSJ0,':','') into VBRXM00,VTJH000,VYJDJH0,VYBH000,VBG0000 from VW_JY_YW0000 where JYYWID=PJYYWID; select HISYJH into VHISYJH from YJ_YW0000 where YJDJH0=VYJDJH0; VXXJG00:='MSH|^~\'; VXXJG00:=VXXJG00||'&|LIS|发送模块名称|PEIS|接收模块名称|'||to_char(systimestamp, 'yyyymmddhh24miss.ff3')||'|T|PPR^PC1^PPR_PC1|Test_Critical_Send-'||to_char(systimestamp, 'yyyymmddhh24missff3')||'|P|2.7'||chr(13); --PID VXXJG00:=VXXJG00||'PID|||'||VTJH000||'||'||VBRXM00||chr(13); --PRB VXXJG00:=VXXJG00||'PRB|AD|'||VBG0000||'|'||VHISYJH||'|'||VYBH000||''||chr(13); --ORC VXXJG00:=VXXJG00||'ORC|NW|'||VHISYJH||'|'||PJYYWID||chr(13); --OBR VXH0000:=1; for V_JY_WJZJL0 in CUR_JY_WJZJL0 loop select JKBH00,ZWMC00 into VJKBH00,VZWMC00 from JY_BGXMZD where BGXMID=V_JY_WJZJL0.BGXMID; select JKBH01,ZLXMJC into VJKBH01,VZLXMJC from BM_ZLZD00 where ZLXMID=V_JY_WJZJL0.ZLXMID; VXXJG00:=VXXJG00||'OBR|'||VXH0000||'|'||VHISYJH||'|'||PJYYWID||'|'||VJKBH01||'^'||VZLXMJC||'^^'||VJKBH00||'^'||VZWMC00||'||||||||'||V_JY_WJZJL0.JYJG00||'^^^^^^'||V_JY_WJZJL0.BGRQ00||'^'||replace(V_JY_WJZJL0.BGSJ00,':','')||'^'||chr(13); VXH0000:=VXH0000+1; end loop; return trim(VXXJG00); end; / --%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%