CREATE OR REPLACE PROCEDURE SP_YJ_JCYYDJ(PYYID00 in char default '0', --预约ID 可空 PYJDJH0 in char, --医技单据号 不能空 PBZ0000 in char, --注意事项 PYYRQ00 in char default '', --预约日期格式YYYYMMDD PYYSJ00 in char default '', --预约时间 格式 09:00 PYYXH00 in char default '0', --预约序号 PCZRXM0 in char, --操作人姓名 PCZBZ00 in char, --0:预约确认,1病区读取确认 PJCSQDH in char, --PACS检查申请单号 PRESULT out number , --错误代码 0成功 1错误 PERROR0 out char, --错误描述 PTZBH00 in char, --同时登记编号 PYYSD00 in char, --预约时段 PXSYYXH in char, --显示预约序号 PPBHBID in number default 0,--号表ID PJZBZ00 in char default 'N', --急诊标志 PCZZT00 in char default '0', --操作状态 0增加 1修改 2取消 PBRDH00 in char default '' , --病人电话 pSFBLJC in char default 'N',--是否病理检查 pSFMZJC in char default 'N'--是否麻醉检查 ) as LS_RQ0000 date; LS_ERROR0 varchar2(500); LS_COU number(10); Ecustom exception ;--错误变量 YJROW YJ_YW0000%rowtype; YYBRBROW YJ_YYBRB0%rowtype; ls_YJKSMC BM_BMBM00.BMMC00%type; ls_KDKSMC BM_BMBM00.BMMC00%type; ls_YYID00 YJ_YYBRB0.YYID00%type; LS_YJDJH0 varchar2(50); LS_YJDJH1 YJ_YYBRB0.YJDJH0%type; LS_POS000 number(5); LS_CZYXM0 BM_YGBM00.ZWXM00%type; LS_YGBH00 BM_YGBM00.YGBH00%type; LS_CZMC00 varchar2(100); LS_MZMC00 varchar2(100); VS_CZNR00 XT_XTCZRZ.CZNR00%type; icount number(10); LS_JCYSBH YJ_KSPBB0.JCYSBH%type; -- MODIFICATION HISTORY -- Person Date Comments -- dsm 2013.08.14 create for YJ-20130819-001 -- dsm 2013.09.03 ZLXMJC写入 for YJ-20130902-001 -- dsm 2013.10.21 把PYYID00改成char for YJ-20131014-001 -- dsm 2013.11.05 add pBRDH00 for YJ-20131105-001 -- dsm 2013.12.03 取消时 YYRQ00不变 for YJ-20131115-002 -- dsm 2013.12.04 YJ_JCHBXH写入YJDJH0 for YJ-20131115-001 -- dsm 2013.12.04 修改取消加日志 for YJ-20131115-001 -- dsm 2013.12.09 取消时，合并单据号要清空 for YJ-20131205-001 -- dsm 2013.12.13 修改时JZBZ00也要修改 for YJ-20131206-001 -- dsm 2014.04.21 增加调用自动占号 for YJ-20140328-001 -- dsm 2014.04.21 把PBRDH00写入BM_BRXXB0.BRDH00 for YJ-20140404-001 -- dsm 2015.04.23 YJ_YYBRB0.YYZT00='1'时,不要解锁 for YJ-20150309-001 -- dsm 2015.12.31 YJ_YYBRB0.JCYSBH写入for YJ-20151223-001 -- dsm 2016.01.08 登记和修改时才取YJ_YYBRB0.JCYSBH写入for YJ-20160107-001 -- dsm 2016.01.25 YJ_YW0000.SFBLJC,SFMZJC数据写入 for YJ-20160122-001 -- qiulf 2021.07.05 修正PPBHBID为0时找不到数据错误 by YJ-20210706-001 begin PRESULT:=0; PERROR0:=''; begin select YGBH00,ZWXM00 into LS_YGBH00,LS_CZYXM0 from BM_YGBM00 where ZWXM00=PCZRXM0 and CZBZ00<>'2' and rownum=1; exception when others then LS_YGBH00:=0; LS_CZYXM0:=PCZRXM0; LS_ERROR0:='获取员工编号错误！'; raise Ecustom; end; if PCZBZ00='0' then begin LS_RQ0000:=to_date(PYYRQ00,'YYYYMMDD'); exception when others then LS_ERROR0:='预约日期格式错误！'; raise Ecustom; end; if length(trim(PYJDJH0))=0 then LS_ERROR0:='请提供HIS单据号！'; raise Ecustom; end if; LS_YJDJH0:=PYJDJH0; LS_YJDJH1:=0; LS_POS000:=instr(LS_YJDJH0,','); if LS_POS000=0 then begin LS_YJDJH1:=to_number(LS_YJDJH0); exception when others then LS_ERROR0:='医技单据号不对！LS_YJDJH0='||LS_YJDJH0||' '||SQLERRM; raise Ecustom; end; LS_YJDJH0:=''; else begin LS_YJDJH1:=to_number(substr(LS_YJDJH0,1,LS_POS000-1)); exception when others then LS_ERROR0:='医技单据号不对！LS_YJDJH1='||LS_YJDJH1||'LS_POS000='||LS_POS000||SQLERRM; raise Ecustom; end; LS_YJDJH0:=substr(LS_YJDJH0,LS_POS000+1,length(LS_YJDJH0)-LS_POS000); end if; icount:=0; while LS_YJDJH1>0 loop --select count(*) into ls_COU from YJ_YYBRB0 where YJDJH0=LS_YJDJH1 and LS_YJDJH1>0 and YYZT00<>'2'; if ls_COU=0 or PYYID00='0' then --增加预约登记 select * into YJROW from YJ_YW0000 where YJDJH0=LS_YJDJH1; select SQ_YJ_YYBRB0_YYID00.nextval into LS_YYID00 from dual; select BMMC00 into ls_YJKSMC from BM_BMBM00 where BMBH00=YJROW.YJKSBH; select BMMC00 into ls_KDKSMC from BM_BMBM00 where BMBH00=YJROW.KDKSBH; begin select nvl(JCYSBH,0) into LS_JCYSBH from YJ_KSPBHB where PBHBID=PPBHBID; exception when others then LS_JCYSBH :=0; end; insert into YJ_YYBRB0(YYID00 ,PBHBID ,MZZYBZ ,ZYGHID ,YJDJH0 ,YYH000 ,BRID00 ,BRXM00 ,BRDH00 ,KMH000 ,ICKH00 ,YJKSBH ,YJKSMC ,YYLB00 ,YYRQ00 ,YYSJ00 ,YYZT00 ,YYYGBH ,YYYGXM ,SQKSBH ,SQKSMC ,DJRQ00 ,DJSJ00 ,BZ0000 ,JKYYH0,JCSQDH,YYSD00,JZBZ00,ZLXMJC,ZLXMID,JCYSBH) values(LS_YYID00,PPBHBID,YJROW.MZZYBZ ,YJROW.ZYGHID ,nvl(LS_YJDJH1,0),PYYXH00 ,YJROW.BRID00 ,YJROW.BRXM00 ,PBRDH00,'' ,'' ,YJROW.YJKSBH ,ls_YJKSMC ,'0' ,PYYRQ00 ,PYYSJ00 ,'0' ,LS_YGBH00 ,PCZRXM0 ,YJROW.KDKSBH ,ls_KDKSMC ,to_char(sysdate,'YYYYMMDD') ,to_char(sysdate,'HH24:MI:SS') ,'' ,PXSYYXH,substr(PJCSQDH,1,20),PYYSD00,PJZBZ00,YJROW.ZLXMJC,YJROW.ZLXMID,LS_JCYSBH); if PPBHBID>0 then Update YJ_JCHBXH set YYID00=LS_YYID00 ,BRID00=YJROW.BRID00 ,BRXM00=YJROW.BRXM00 ,YJDJH0=YJROW.YJDJH0 ,WBDLXH=PXSYYXH where PBHBID=PPBHBID and XH0000=PYYXH00 ; end if; if (nvl(PBRDH00,' ')<>' ') and (not PBRDH00 is null) then update BM_BRXXB0 set BRDH00=PBRDH00 where BRID00=YJROW.BRID00; end if; Insert into YJ_JCYYFK(YYID00,YJDJH0) values (LS_YYID00,LS_YJDJH1); update YJ_JCYYFK set YYAPRQ=PYYRQ00, YYAPSJ=PYYSJ00, BZ0000=PBZ0000, PACSZT='1', PSCZR0=PCZRXM0, PSCZRQ=to_char(sysdate,'YYYYMMDD'), PSCZSJ=to_char(sysdate,'HH24:MI:SS'), JCSQDH=substr(PJCSQDH,1,20), TZBH00=PTZBH00, YYSD00=PYYSD00, YYXH00=PXSYYXH where (YJDJH0=LS_YJDJH1 and LS_YJDJH1>0); update YJ_YW0000 set YYID00= LS_YYID00,HBXHID=0,SFMZJC=pSFMZJC,SFBLJC=pSFBLJC where YJDJH0=LS_YJDJH1; else --修改预约登记 select * into YJROW from YJ_YW0000 where YJDJH0=LS_YJDJH1 ; select YYID00 into ls_YYID00 from YJ_YYBRB0 where YJDJH0=LS_YJDJH1 and instr(PYYID00,','||YYID00||',')>0; select * into YYBRBROW from YJ_YYBRB0 where YYID00=ls_YYID00; --解原锁 if icount=0 and YYBRBROW.YYZT00='0' then update YJ_JCHBXH set BRXM00='',YYID00=0,BRID00=0,YJDJH0=0,LBMC00='' where instr(PYYID00,','||YYID00||',')>0; end if; if PCZZT00='0' or PCZZT00='1' then begin select nvl(JCYSBH,0) into LS_JCYSBH from YJ_KSPBHB where PBHBID=PPBHBID; exception when others then LS_JCYSBH :=0; end; end if; --设置新的预约记录 if (PCZZT00='0' or PCZZT00='1') and PPBHBID >0 then Update YJ_JCHBXH set YYID00=ls_YYID00 ,BRID00=YJROW.BRID00 ,BRXM00=YJROW.BRXM00 ,YJDJH0=YJROW.YJDJH0 ,WBDLXH=PXSYYXH where PBHBID=PPBHBID and XH0000=PYYXH00; end if; if (nvl(PBRDH00,' ')<>' ') and (not PBRDH00 is null) then update BM_BRXXB0 set BRDH00=PBRDH00 where BRID00=YJROW.BRID00; end if; update YJ_YYBRB0 set YYRQ00= decode(PCZZT00,'2',YYRQ00,PYYRQ00), YYSJ00=decode(PCZZT00,'2',YYSJ00,PYYSJ00), JKYYH0=decode(PCZZT00,'2',JKYYH0,PXSYYXH), YYSD00=decode(PCZZT00,'2',YYSD00,PYYSD00), YYH000=decode(PCZZT00,'2',YYH000,PYYXH00), YYZT00=decode(PCZZT00,'2','2','0'), JCSQDH=substr(PJCSQDH,1,20), BRDH00=Pbrdh00, PBHBID=decode(PCZZT00,'2',PBHBID,PPBHBID), JZBZ00=decode(PCZZT00,'2',JZBZ00,PJZBZ00), JCYSBH=decode(PCZZT00,'2',JCYSBH,LS_JCYSBH) where (YJDJH0=LS_YJDJH1 and LS_YJDJH1>0) and instr(PYYID00,','||YYID00||',')>0; update YJ_JCYYFK set YYAPRQ=PYYRQ00, YYAPSJ=decode(PCZZT00,'2',YYAPSJ,PYYSJ00), BZ0000=PBZ0000, PACSZT='1', PSCZR0=PCZRXM0, PSCZRQ=to_char(sysdate,'YYYYMMDD'), PSCZSJ=to_char(sysdate,'HH24:MI:SS'), JCSQDH=substr(PJCSQDH,1,20), TZBH00=PTZBH00, YYSD00=decode(PCZZT00,'2',YYSD00,PYYSD00), YYXH00=PXSYYXH where (YJDJH0=LS_YJDJH1 and LS_YJDJH1>0 and instr(PYYID00,','||YYID00||',')>0); update YJ_YW0000 set HBXHID=0,SFMZJC=pSFMZJC,SFBLJC=pSFBLJC where YJDJH0=LS_YJDJH1; if PCZZT00='2' then --for YJ-20131205-001 update YJ_YW0000 set HBDJH0=null where YJDJH0=LS_YJDJH1; end if; if PCZZT00='2' then ls_mzmc00:='预约取消:'; ls_czmc00:=' 预约日期:'||YYBRBROW.YYRQ00||'日'||YYBRBROW.JKYYH0||'号'; else ls_mzmc00:='预约修改:'; ls_czmc00:=' 预约日期:'||YYBRBROW.YYRQ00||'日'||YYBRBROW.JKYYH0||'号-->'||PYYRQ00||'日'||PXSYYXH||'号'; end if; VS_CZNR00:=ls_mzmc00||'住院(门诊)号:'||trim(YJROW.ZYHGHH)||' 诊疗单号：'||YJROW.YJDJH0||' 检查项目:'||YJROW.ZLXMJC||ls_czmc00; Insert Into XT_XTCZRZ(CZID00,CZTable,CZLX00,CZRYBM,CZNR00,DATAID,CZRQ00,CZSJ00) Values(SQ_XT_XTCZRZ_ID0000.NEXTVAL,'YJ_YYBRB0',1,LS_YGBH00,VS_CZNR00||' 操作IP:'||sys_context('userenv', 'ip_address'),YJROW.YJDJH0,to_char(sysdate,'YYYYMMDD'),to_char(sysdate,'HH24:MI:SS')); end if; -- if PCZZT00 in ('0','1') or (PYYID00='0') then --强制占号 -- SP_YJ_HBDJQZZH00(LS_YJDJH1,YJROW.ZYGHID,PPBHBID,PYYXH00); -- end if; LS_POS000:=instr(LS_YJDJH0,','); LS_YJDJH1:=0; if LS_POS000=0 and nvl(LS_YJDJH0,' ')<>' ' then begin LS_YJDJH1:=to_number(LS_YJDJH0); LS_YJDJH0:=''; exception when others then LS_ERROR0:='医技单据号不对！LS_YJDJH0='||LS_YJDJH0||' '||SQLERRM; raise Ecustom; end; elsif nvl(LS_YJDJH0,' ')<>' ' then begin LS_YJDJH1:=to_number(substr(LS_YJDJH0,1,LS_POS000-1)); exception when others then LS_ERROR0:='医技单据号不对！LS_YJDJH1='||LS_YJDJH1||'LS_POS000='||LS_POS000||SQLERRM; raise Ecustom; end; LS_YJDJH0:=substr(LS_YJDJH0,LS_POS000+1,length(LS_YJDJH0)-LS_POS000); end if; icount:=icount+1; end loop; if YJROW.HBDJH0>0 and (PCZZT00 in ('0','1') or (PYYID00='0') ) and PPBHBID > 0 then SP_YJ_HBDJQZZH00(YJROW.HBDJH0,YJROW.ZYGHID,PPBHBID,PYYXH00); end if; else --目前不会走这插 update YJ_JCYYFK set HISZT0='1', HISQRR=PCZRXM0, HSCZRQ=to_char(sysdate,'YYYYMMDD'), HSCZSJ=to_char(sysdate,'HH24:MI:SS') where instr(PYYID00,','||YYID00||',')>0 and YYXH00=PXSYYXH; end if; exception when Ecustom then PRESULT:=1; PERROR0:=LS_ERROR0||SQLERRM; RAISE_APPLICATION_ERROR(-20020, NVL(SQLERRM, '原因不明出错！*')); rollback; when others then PRESULT:=1; PERROR0:=SQLERRM; RAISE_APPLICATION_ERROR(-20020, NVL(SQLERRM, '原因不明出错！*')); rollback; end;