CREATE OR REPLACE PROCEDURE SP_PY_MZPFYCZ -- MODIFICATION HISTORY -- Person Date Comments -- JXQ 2012.08.18 Create -- qks 2012.09.03 复核人(待配->待发) FHR001 -- qks 2014.04.02 增加字段 YF_CFDL00.DKFYBZ -- qks 2014.08.06 Vphrq00 number-->char(8) -- qks 2014.09.18 发药确认:修改YF_MZCF00.FYR000=PFHRBH0 取消掉 --配药确认:修改YF_MZCF00.FYR000=PFHRBH0,FHR000非空时值不要修改 -- qks 2015.06.03 西成药配药确认:跟中草药一样要修改YF_MZCF00.FYR000,FHR001=PFHRBH0 -- qks 2018.08.01 POPFLAG=3配药反确认,YF_MZCF00.FYR000不修改。for YF9-20180801-002 -- liwm 2019.03.14 增加三码融合发送消息 YF9-20190314-002 -- liwm 2019.04.26 三码融合消息发送添加参数控制 MZPY9-20190426-001 -- liwm 2019.06.25 打印增加人民发药人处理 MZPY9-20190621-001 -- liwm 2019.06.27 代配->已配处理 MZPY9-20190627-001 -- liwm 2019.11.01 解决莆田市仙游县第二医院没有操作员的问题 MZPY9-20191101-001 -- qks 2021.01.05 光泽县医院：当POPFLAG = 1,初步判断sql%notfound值可能会出现问题,改用select。for MZPY9-20210107-002 -- qks 2021.02.23 新增参数XT_UPDATESQL控制之前MZPY9-20210107-002出现的问题; for MZPY9-20210223-001 -- qks 2021.03.25 注释：2021.01.05 初步判断sql%notfound值可能会出现问题,改用select。同时增加过程调用日志。for MZPY9-20210325-001 -- chenxz 2021.05.21 新增入参，开启参数MZPY_PYRGHQR，入参值写入配药人栏位 for MZPY9-20210509-001 ( POPFLAG in number, --操作标志，1:打印 2:配药确认 3:配药反确认 4:发药确认 5:发药确认->待发 6:待配直接发药 PCFLSH0 in varchar2, --处方流水号 PFHRBH0 in number default null, --审核人、复核人编码 PCZY000 in number, --操作员 PCZYKS0 in number, --操作员科室 PCOMMIT in varchar2 default 'N', --是否提交 PZXCGBZ out number, --执行成功标志 1成功 0失败 PTSXX00 out varchar2, --执行结果提示信息，成功的情况下也可能有警告信息 PXXXX00 out varchar2, --执行结果详细信息，成功的情况下也可能有警告信息，一般用来显示参数等指标 PDKFYBZ in varchar2 default '0', --读卡发药标志 1读卡 0非读卡 PPFR000 in number default null --配药人编码 ) as VCOUNT0 number; VBRID00 number; VCFDL VW_PY_FYCFDL%rowtype; VPYCKBH number; Vfyckbh number; Vfydlxh number; Vphrq00 char(8); Vjhxh00 number; VPFR000 YF_MZCF00.PFR000%type; ECUSTOM exception; --错误变量 Verrts varchar2(10); V_MZPY_SFFSSMRHXX varchar2(10); --是否发送三码融合消息参数 Vfyr000 BM_CKBM00.FYR000%type; --发药人配置 Vupdate char(1); --是否通过sql%notfound判断update执行成功:1是,0否 Vpfrqr0 char(1); --开启参数MZPY_PYRGHQR是否将审核人工号(PFHRBH0)写入配药人(PFR000)字段:1是，0否 -- VS_PROCESS XT_XTRZ00.JCID00%type; -- VS_YGBH00 XT_XTRZ00.YGBH00%type; -- VS_CZNR00 XT_XTCZRZ.CZNR00%type; begin PZXCGBZ := 0; --查找是否有默认发药人 select nvl(max(FYR000), 0) into Vfyr000 from BM_CKBM00 where CKBH00 = Vfyckbh; --如果没有配置用当前登录人员 if Vfyr000 is null or Vfyr000 = 0 then Vfyr000 := PCZY000; end if; Pxxxx00 := nvl(to_char(PCFLSH0),'null')||','||nvl(to_char(POPFLAG),'null')||','||nvl(to_char(Pczy000),'null')||','|| nvl(to_char(Pczyks0),'null')||','||nvl(to_char(Pcommit),'null'); SP_TransLog(sysdate,'SP_PY_MZPFYCZ',Pczy000,Pczyks0,Pxxxx00); Pxxxx00 := ''; if POPFLAG = 1 then --打印 --是否通过sql%notfound判断update执行成功:1是,0否 select nvl(substrb(max(trim(value0)),1,1),'1') into Vupdate from XT_XTCS00 where trim(NAME00)='XT_UPDATESQL'; --2021.01.05 初步判断sql%notfound值可能会出现问题,改用select。 --select count(1) into VCOUNT0 from YF_CFDL00 where CFLSH0 = PCFLSH0 and ZT0000 = '0'; --if VCOUNT0 = 0 then -- PTSXX00:='队列状态发生改变，请刷新后再重试'; -- raise Ecustom; --end if; update YF_CFDL00 set DYRQ00= to_char(sysdate, 'YYYYMMDD'), DYSJ00 = to_char(sysdate,'HH24:MI:SS'), ZT0000= '1' where CFLSH0 = PCFLSH0 and ZT0000 = '0'; --2021.01.05 实际上有update成功,但是出现sql%notfound为true情况,该问题出现的频率还比较高。 if sql%notfound and Vupdate='1' then PTSXX00:='队列状态发生改变，请刷新后再重试'; raise Ecustom; end if; select PYCKBH,BRID00,FYDLXH,FYCKBH into VPYCKBH,VBRID00,VFYDLXH,Vfyckbh from YF_CFDL00 where CFLSH0 = PCFLSH0; update YF_MZCF00 set FHR000 = PFHRBH0,--decode(FHR000,null,PFHRBH0,FHR000), FHR001 = PFHRBH0,--decode(FHR001,null,PFHRBH0,FHR001), FYR000 = Vfyr000 --decode(FYR000,null,PCZY000,FYR000) where CFLSH0 = PCFLSH0 and YPDLBH<>'2'; select nvl(max(a.PFR000),0) into VPFR000 from YF_MZCF00 a,YF_CFDL00 b where b.BRID00=VBRID00 and b.PHRQ00=to_char(sysdate,'YYYYMMDD') and a.CFLSH0=b.CFLSH0 and a.CFLSH0<>PCFLSH0 and b.PYCKBH=VPYCKBH and FYDLXH=VFYDLXH; if VPFR000=0 then select SF_PY_JSPYRY(VPYCKBH) into VPFR000 from dual; end if; if nvl(VPFR000,0)=0 then PTSXX00 := '无法指定配药人，请检查药房配药人员设置是否正确'; raise ECUSTOM; end if; if PPFR000 is not null then update YF_MZCF00 set PFR000 = PPFR000 where CFLSH0 = PCFLSH0; else update YF_MZCF00 set PFR000 = VPFR000 where CFLSH0 = PCFLSH0; end if; update YF_PYCKRY set ZHPYSJ = to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') where YGBH00=VPFR000; --update YF_MZCF00 set PFR000 = decode(PFR000, null, VPFR000, PFR000) where CFLSH0 = PCFLSH0; commit; end if; if POPFLAG <> 1 then select * into VCFDL from VW_PY_FYCFDL where CFLSH0 = PCFLSH0; end if; if POPFLAG = 2 then --配药确认 --if VCFDL.ZT0000 <> '1' then -- PTSXX00 := '非[待配药]状态，请刷新，操作终止'; -- raise ECUSTOM; --end if; update YF_CFDL00 A set DYRQ00 = decode(DYRQ00, null, to_char(sysdate, 'YYYYMMDD'), DYRQ00), DYSJ00 = decode(DYSJ00, null, to_char(sysdate,'HH24:MI:SS'), DYSJ00), PYRQ00 = to_char(sysdate, 'YYYYMMDD'), PYSJ00 = to_char(sysdate, 'HH24:MI:SS'), ZT0000 = '2', PYQRR0 = PCZY000 where CFLSH0 = PCFLSH0 and ZT0000 in ('1'); if sql%notfound then PTSXX00:='队列状态发生改变，请刷新后再重试'; raise Ecustom; end if; select BRID00,FYDLXH,PHRQ00,FYCKBH into Vbrid00,Vfydlxh,Vphrq00,Vfyckbh from YF_CFDL00 where CFLSH0 = PCFLSH0; select nvl(max(JHXH00),0) into Vjhxh00 from YF_CFDL00 where BRID00=Vbrid00 and FYCKBH=Vfyckbh and PHRQ00=Vphrq00 and FYDLXH=Vfydlxh; if Vjhxh00=0 then select nvl(max(JHXH00),0)+1 into Vjhxh00 from YF_CFDL00 where PHRQ00=Vphrq00; end if; update YF_CFDL00 set JHXH00 = Vjhxh00 where CFLSH0=PCFLSH0 ; update YF_MZCF00 set FYR000 = nvl(PFHRBH0,FYR000), --PCZY000,--decode(FYR000, null, PCZY000, FYR000), FHR000 = nvl(FHR000,PFHRBH0), --PFHRBH0,--decode(FHR000, null, PFHRBH0, FHR000), FHR001 = PFHRBH0 --decode(FHR001, null, PFHRBH0, FHR001) where CFLSH0 = PCFLSH0; --and YPDLBH = '2'; if Vfyr000 is not null and Vfyr000 > 0 then update YF_MZCF00 set FYR000 = Vfyr000 where CFLSH0 = PCFLSH0; end if; end if; if POPFLAG = 3 then --配药反确认 if VCFDL.ZT0000 <> '2' then PTSXX00 := '非[已配药]状态，请刷新，操作终止'; raise ECUSTOM; end if; --select count(*) into VCOUNT0 from YF_MZCF00 where CFLSH0 = PCFLSH0 and PFR000 = PCZY000; --if VCOUNT0 = 0 then -- PTSXX00 := '非您本人配药确认，操作终止'; -- raise ECUSTOM; --end if; update YF_CFDL00 set PYRQ00 = null, PYSJ00 = null, ZT0000 = '1', JHXH00=null,JHCS00=0,PYQRR0 =null where CFLSH0 = PCFLSH0 and ZT0000 = '2'; if sql%notfound then PTSXX00:='队列状态发生改变，请刷新后再重试'; raise Ecustom; end if; --update YF_MZCF00 set PFR000 = null where CFLSH0 = PCFLSH0; update YF_MZCF00 set --FYR000 = null, FHR000 = null, FHR001 = null where CFLSH0 = PCFLSH0 and YPDLBH = '2'; end if; if POPFLAG = 4 then --发药确认 if (VCFDL.ZT0000 = '0') or (VCFDL.ZT0000 = '4') then select count(*) into VCOUNT0 from VW_YS_YXGH00 where GHH000 = VCFDL.GHH000; if VCOUNT0 = 0 then PTSXX00 := '该处方挂号号: ['||VCFDL.GHH000||']已失效，不能在此发药'; raise ECUSTOM; end if; end if; if VCFDL.ZT0000 <> '2' then select count(1) into VCOUNT0 from YF_CFDL00 where CFLSH0 = PCFLSH0 and nvl(DKFYBZ,'0')='1'; if VCOUNT0>0 then PTSXX00:='该处方已[读卡发药]，请刷新后再重试'; else PTSXX00 := '非[已配药]状态，请刷新，操作终止'; end if; raise ECUSTOM; end if; SP_YF_MZCFFY(PCFLSH0, PFHRBH0,null,'1', PCZY000, PCZYKS0, PCOMMIT, PZXCGBZ, PTSXX00, PXXXX00); update YF_CFDL00 set FYRQ00 = to_char(sysdate, 'YYYYMMDD'), FYSJ00 = to_char(sysdate, 'HH24:MI:SS'), ZT0000 = '3', DKFYBZ = PDKFYBZ where CFLSH0 = PCFLSH0 and ZT0000 = '2'; if sql%notfound then PTSXX00:='队列状态发生改变，请刷新后再重试'; raise Ecustom; end if; update YF_MZCF00 set LYBZ00 = '1',LYRQSJ = to_char(sysdate,'YYYYMMDDHH24:MI:SS') where CFLSH0 = PCFLSH0; --三码融合发药消息推送 select nvl(max(VALUE0), 'N') into V_MZPY_SFFSSMRHXX from XT_XTCS00 where NAME00 = 'MZPY_SFFSSMRHXX'; if V_MZPY_SFFSSMRHXX = 'Y' then begin select PYCKBH,BRID00,FYDLXH into VPYCKBH,VBRID00,VFYDLXH from YF_CFDL00 where CFLSH0 = PCFLSH0; execute immediate 'begin SP_FZBMPT_PYDLXX('||VBRID00||','||PCFLSH0||',''7''); end;'; exception when others then Verrts := substrb(nvl(sqlerrm, '执行消息推送失败'), 1, 240); end; end if; if Vfyr000 is not null and Vfyr000 > 0 then update YF_MZCF00 set FYR000 = Vfyr000 where CFLSH0 = PCFLSH0; end if; end if; if POPFLAG = 5 then --发药确认->待发 if (VCFDL.ZT0000 <> '3') then return; end if; select count(1) into VCOUNT0 from YF_CFDL00 where CFLSH0 = PCFLSH0 and ZT0000 = '3' and nvl(DKFYBZ,'0')='1'; if VCOUNT0>0 then PTSXX00:='该处方已[读卡发药]!'; raise Ecustom; end if; update YF_CFDL00 set ZT0000 = '2', FYRQ00 = null where CFLSH0 = PCFLSH0 and ZT0000 = '3'; if sql%notfound then PTSXX00:='队列状态发生改变，请刷新后再重试'; raise Ecustom; end if; update YF_MZCF00 set LYBZ00 = '0',LYRQSJ = null where CFLSH0 = PCFLSH0; end if; if POPFLAG = 6 then --待配直接发药 if (VCFDL.ZT0000 <> '1') then return; end if; SP_YF_MZCFFY(PCFLSH0, PFHRBH0,null,'1', PCZY000, PCZYKS0, PCOMMIT, PZXCGBZ, PTSXX00, PXXXX00); update YF_CFDL00 set DYRQ00 = decode(DYRQ00, null, to_char(sysdate, 'YYYYMMDD'), DYRQ00), DYSJ00 = decode(DYSJ00, null, to_char(sysdate,'HH24:MI:SS'), DYSJ00), PYRQ00 = to_char(sysdate, 'YYYYMMDD'), PYSJ00 = to_char(sysdate, 'HH24:MI:SS'), FYRQ00 = to_char(sysdate, 'YYYYMMDD'), FYSJ00 = to_char(sysdate, 'HH24:MI:SS'), ZT0000 = '3', PYQRR0 = PCZY000 where CFLSH0 = PCFLSH0 and ZT0000 = '1'; if sql%notfound then PTSXX00:='队列状态发生改变，请刷新后再重试'; raise Ecustom; end if; --update YF_MZCF00 set FHR001 = decode(PFHRBH0, null, PCZY000, PFHRBH0), PFR000 = PCZY000 -- where CFLSH0 = PCFLSH0; update YF_MZCF00 set FYR000 = nvl(PFHRBH0,FYR000),--PCZY000,--decode(FYR000, null, PCZY000, FYR000), FHR000 = nvl(FHR000,PFHRBH0),--PFHRBH0,--decode(FHR000, null, PFHRBH0, FHR000), FHR001 = PFHRBH0 --decode(FHR001, null, PFHRBH0, FHR001) where CFLSH0 = PCFLSH0 and YPDLBH = '2'; update YF_MZCF00 set LYBZ00 = '1',LYRQSJ = to_char(sysdate,'YYYYMMDDHH24:MI:SS') where CFLSH0 = PCFLSH0; if Vfyr000 is not null and Vfyr000 > 0 then update YF_MZCF00 set FYR000 = Vfyr000 where CFLSH0 = PCFLSH0; end if; end if; PZXCGBZ := 1; if PCOMMIT = 'Y' then commit; end if; exception when No_Data_Found then raise_application_error(-20001, substrb('没有找到数据！*'||sqlerrm||PTSXX00, 1, 240)); when ECUSTOM then raise_application_error(-20010, substrb(PTSXX00||'！*', 1, 240)); when others then raise_application_error(-20002, substrb(nvl(sqlerrm, '原因不明出错')||'！*', 1, 240)); end;