create or replace procedure SP_BQ_YZTYSQ ( Ptqdjh0 in number , --提取ID Pzyid00 in number , --住院ID Pyzid00 in number , --医嘱ID Pczy000 in number , --操作员 Pczyks0 in number , --操作员科室 Pcommit in varchar2 default 'N' , --是否提交 Pzxcgbz out number , --执行成功标志 1成功 0失败 Ptsxx00 out varchar2 , --执行结果提示信息，成功的情况下也可能有警告信息 Pxxxx00 out varchar2 , --执行结果详细信息，成功的情况下也可能有警告信息，一般用来显示参数等指标 Pyyrq00 in varchar2 default '20991231' --用药日期 ) as Vcount0 number(5); --计数器 Vcount1 number(5); --计数器 Vjsrq00 char(8); Vjssj00 char(8); Vjsrqsj char(16); Vjztybz char(1); --已结帐是否允许退药标志 Vjzrq00 char(8); --记账日期 Vckdh00 varchar2(50); --冲销的出库单号 Vcxqlpc number; Vcklsj0 number; --出库零售价 Vckgjj0 number; --出库购进价 Vsffyjl number; --是否有发药记录 Ecustom exception; --错误变量 Vczyxm0 BM_YGBM00.ZWXM00%type; Vfbbh00 BM_BRFBB0.FBBH00%type; --病人费别(自费，公费，医保等) Vyfbmbh BM_BMBM00.BMBH00%type; --药房部门编号 Vbqbmbh BM_BMBM00.BMBH00%type; --病区部门编号 Vybkh00 BM_BRXXB0.YBKH00%type; --医保卡号 Vybid00 BM_BRXXB0.YBID00%type; --医保ID Vzyh000 ZY_BRXXB0.ZYH000%type; --住院号 Vjzdh00 ZY_FYMX00.JZDH00%type; --结算单号 Vsfty00 XT_XTCS00.VALUE0%type; --医嘱退药是否直接进行退药申请确认 Vxmcxsq XT_XTCS00.VALUE0%type; --结算过的项目发票未回收不允许进行项目冲消或退费申请 Vcxjzrq XT_XTCS00.VALUE0%type; --医保限制冲销日期(默认为20010101) Vbmxz00 BM_BMBM00.BMXZ00%type; --开单科室部门性质 Vsfbfty XT_XTCS00.VALUE0%type; --是否允许部分退药（同组内已发药的已退药确认的情况下） BQ_TYHYXZTQ XT_XTCS00.VALUE0%type; --退药后允许再提取 vyyrq00 YF_YZYPSQ.YYRQ00%type; Vclbz00 BQ_YPYZ00.CLBZ00%type; --长临标志 BQ_FZYLCKSSFYXTY XT_XTCS00.VALUE0%type; --非住院临床科室是否允许退药 cursor C_yp is select YPQLPC,YFBMBH,YPNM00,YPZSL0,QLDW00,ZHL000,QLZT00 from YF_YZYPSQ where TQDJH0=Ptqdjh0 and YZID00=Pyzid00 and YPZSL0>0 and QLZT00 in ('0','1') and (vyyrq00='20991231' or yyrq00=vyyrq00); -- MODifICATION HISTORY -- Person Date Comments -- zhanghr 2013.03.22 整改 -- qks 2013.08.08 发现BQ_YZZXJL表数据有重复 -- qks 2013.10.18 允许同组中存在已发药记录，可以对未发药部分进行退药 -- zxz 2014.09.19 非病区科室（麻醉科）申请药品，不允许退药申请 -- zhaoxz 2017.03.09 药房已发药药品，不允许再此过程退药 BQHS9.0-20170308-002 -- zhaoxz 2017.04.26 处理同组内，已发药药品已退费，未发药药品无法申请问题 BQHS9.0-20170410-002 -- ruanbh 2019.07.03 退药后允许再提取，参数控制 BQHS9.0-20190702-005 -- dongxb 2019.08.20 退费后医嘱状态不改变 for BQHS9.0-20190814-005 -- linyx 2020.01.15 修改查询*改为具体字段 for BQHS9.0-20200114-001 -- dongxb 2021.04.20 修复同时提取同个药品两天量，其中一天发药另外一天未发药收费状态，对收费状态的药品进行退费时无法退费问题 for BQHS9.0-20210420-001 -- dongxb 2021.04.21 修复同时提取同个药品两天量，对其中一个退费，另外一个也会自动退费问题 for BQHS9.0-20210421-002 -- dongxb 2021.04.23 修复需求BQHS9.0-20210421-002其他过程调用该过程没有传用药日期时只能退用药日期为当天的问题 for BQHS9.0-20210423-004 -- dongxb 2022.01.10 修复同时提取同个药品多天量，药品状态为申请时，对其中一个退药，另外一个也会自动退药问题 for ZYHS11-20220110-001 -- dongxb 2023.08.11 修复同时提取同个药品多天量，对其中一天退药后生成药单界面该药品同时提取的这几天都恢复为能重新摆药申请问题 for BQHS9.0-20230811-001 -- dongxb 2023.09.07 修复非当日临时医嘱在医嘱管理界面执行临嘱再退药后无法再提取问题 for BQHS9.0-20230907-003 -- lisuzhu 2023.10.24 非住院临床科室允许退药 for BQHS9.0-20231012-002 begin --已经发药的处方，可以退单个药品或者单个药品的部分，未发药的处方(可能已经扣费,必须整组退) Ptsxx00:='OK'; --已结算的申请退药，是否需要先进行发票回收 select upper(trim(nvl(max(VALUE0),'N'))) into Vxmcxsq from XT_XTCS00 where NAME00 = 'BQ_JSFYSQTFFPHSCZ'; --退药申请是否直接进行退药确认 select trim(nvl(max(VALUE0),'N')) into Vsfty00 from XT_XTCS00 where NAME00='BQ_YZYPTYSQQR'; --已结帐药品是否允许退药 select trim(nvl(max(VALUE0),'Y')) into Vjztybz from XT_XTCS00 where NAME00='BQ_YJZYPSFYXTY'; --已发药部分药品已退费确认，是否允许退未发药部分 select trim(nvl(max(VALUE0),'N')) into Vsfbfty from XT_XTCS00 where NAME00='BQ_WFYYPSFYXBFTY'; --退药后允许再提取 select trim(nvl(max(VALUE0),'Y')) into BQ_TYHYXZTQ from XT_XTCS00 where NAME00='BQ_TYHYXZTQ'; --取操作员信息 select ZWXM00 into Vczyxm0 from BM_YGBM00 where YGBH00=Pczy000; select count(*) into Vcount0 from ZY_BRXXB0 where ZYID00=Pzyid00 and Substr(BRZT00,1,1) in ('1','2','3'); if Vcount0=0 then Ptsxx00:='非在院病人，不能提出退药申请！'; raise Ecustom; end if; select trim(nvl(max(VALUE0),'N')) into BQ_FZYLCKSSFYXTY from XT_XTCS00 where NAME00='BQ_FZYLCKSSFYXTY'; --判断开单科室是否为病区科室申请 select BMXZ00 into Vbmxz00 from BM_BMBM00 where BMBH00 in(select DQKS00 from YF_YZYPSQ where YZID00=Pyzid00 and TQDJH0=Ptqdjh0 and rownum=1); if Vbmxz00!='0' and BQ_FZYLCKSSFYXTY<>'Y' then Ptsxx00:='非病区科室申请药品，不能提出退药申请！'; raise Ecustom; end if; select CLBZ00 into Vclbz00 from BQ_YPYZ00 where yzid00=Pyzid00; --医保限制冲销日期(默认为20010101) select nvl(max(trim(Value0)),'20010101') into Vcxjzrq from XT_XTCS00 where Name00='ZY_YBCXJZRQ'; vyyrq00:=Pyyrq00; if(vyyrq00 is null) then vyyrq00:='20991231'; end if; if (Pyzid00 is not null) then --判断是否退过部分药品 select count(*) into Vcount0 from YF_YZYPSQ b where b.ZYID00=Pzyid00 and b.YZID00=Pyzid00 and b.TQDJH0=Ptqdjh0 and (b.YPZSL0<0 or b.ZCXSL0>0 or b.BCCXSL>0) and (vyyrq00='20991231' or yyrq00=vyyrq00); if Vcount0>0 then --如果已发药部分已退费确认，允许退未发药部分,参数为Y时重新进行赋值，防止未发药部分退药,重新赋值后，1代表进行部分退药操作，0非部分退药操作 if Vsfbfty = 'Y' then select count(*) into Vcount0 from YF_YZYPSQ b where b.ZYID00=Pzyid00 and b.YZID00=Pyzid00 and b.TQDJH0=Ptqdjh0 and (b.YPZSL0<0 or b.ZCXSL0>0 or b.BCCXSL>0) and nvl(DJH000,0)=0 and (vyyrq00='20991231' or yyrq00=vyyrq00); if Vcount0>0 then Vsfbfty := '0'; else Vsfbfty := '1'; end if; end if; if Vsfbfty <> '1' then Ptsxx00:='已经申请退药，请确认'; raise Ecustom; end if; end if; -- select count(*) into Vcount0 from YF_YZYPSQ b where b.ZYID00=Pzyid00 and b.YZID00=Pyzid00 and b.TQDJH0=Ptqdjh0 and QLZT00 not in ('0','1') and YPZSL0>0; -- if Vcount0>0 then -- Ptsxx00:='药品已经发药，不能执行本业务'; -- raise Ecustom; -- end if; select count(*) into Vsffyjl from YF_YZYPSQ b where b.ZYID00=Pzyid00 and b.YZID00=Pyzid00 and b.TQDJH0=Ptqdjh0 and QLZT00 not in ('0','1') and YPZSL0>0 and (vyyrq00='20991231' or yyrq00=vyyrq00); --同组医嘱的至少部分药品正在发药 select count(*) into Vcount0 from YF_YZYPSQ b where b.ZYID00=Pzyid00 and b.YZID00=Pyzid00 and b.TQDJH0=Ptqdjh0 and FYBZ00='1' and (vyyrq00='20991231' or yyrq00=vyyrq00); if Vcount0>0 then Ptsxx00:='药房正在发药，病区不能退药'; raise Ecustom; end if; --如果一组药品的状态不相同，必须状态一致后才能退药 select count(1) into Vcount0 from ( select distinct QLZT00,CXBZ00 from YF_YZYPSQ b where b.ZYID00=Pzyid00 and b.YZID00=Pyzid00 and b.TQDJH0=Ptqdjh0 and b.YPZSL0>0 and (vyyrq00='20991231' or yyrq00=vyyrq00) and nvl(zbybz0,'0')='0'); if Vcount0>1 then if Vsffyjl<=0 and Vsfbfty <> '1' then Ptsxx00:='该处方状态不一致，必须一致后才可退药'; raise Ecustom; end if; end if; --处方状态已经发药，不允许再过程退药 select count(*) into Vcount0 from YF_YZYPSQ b where b.ZYID00=Pzyid00 and b.YZID00=Pyzid00 and b.TQDJH0=Ptqdjh0 and QLZT00='2' and (vyyrq00='20991231' or yyrq00=vyyrq00); --解决同时提取同个药品两天量，其中一天发药另外一天未发药收费状态，对收费状态的药品进行退费时无法退费问题 --select count(*) into Vcount1 from YF_YZYPSQ b where b.ZYID00=Pzyid00 and b.YZID00=Pyzid00 and b.TQDJH0=Ptqdjh0 and QLZT00 in('0','1') and (vyyrq00='20991231' or yyrq00=vyyrq00); if Vcount0>0 and Vsfbfty <> '1' then Ptsxx00:='该处方已发药，请刷新界面，在进行退药操作'; raise Ecustom; end if; --取病人信息 select ZYH000,FBBH00,YBKH00,SHBZH0 into Vzyh000,Vfbbh00,Vybkh00,Vybid00 from ZY_BRXXB0 where ZYID00=Pzyid00; select nvl(min(JZDH00),0),nvl(min(JZRQ00),'20991231') into Vjzdh00,Vjzrq00 from YF_YZYPSQ a,ZY_FYMX00 b where a.DJH000=b.DJH000 and b.JZDH00<>0 and a.ZYID00=Pzyid00 and a.TQDJH0=Ptqdjh0 and YZID00=Pyzid00 and (vyyrq00='20991231' or a.YYRQ00=vyyrq00); if Vjzdh00<>0 then --已结帐药品不允许退药申请 if Vjztybz='N' then Ptsxx00:='该处方收费处已结帐,病区不能退药申请！*'; raise Ecustom; end if; if Vxmcxsq='Y' then select count(1) into Vcount0 from ZY_JZB000 where jzdh00=Vjzdh00 and fphsbz='0'; if Vcount0>0 then Ptsxx00:='不能退药,该处方收费处已结算,而且发票未回收,请先把发票交还到收费处！*'; raise Ecustom; end if; end if; end if; if Vfbbh00=3 then --ZY_YBCXJZRQ 根据不同的YBZXLB获取不同的 YBCXJZRQ by liuj BQ-20120113-001 on 20120116 Vcxjzrq:='20010101'; select SF_ZY_GETXTCS(c.YBZXLB,Vcxjzrq) into Vcxjzrq from ZY_BRXXB0 a,IC_YBBRLB c where a.ZYID00=Pzyid00 and a.FBBH00=c.FBBH00 and a.YBLB00=c.YBLB00; --除了新农合病人和异地医保病人，其他医保病人都要限制不能冲销某个时间段之前的费用 if (Vcxjzrq<>'20010101') and (Vjzrq00<>'20991231') then if (Vcxjzrq >= Vjzrq00) and (Vybkh00 <> Vybid00) and (instr(Vybkh00,'@') <= 0) then Ptsxx00:='不能冲销'||substr(Vcxjzrq,1,4)||'年'||substr(Vcxjzrq,5,2)||'月'||substr(Vcxjzrq,7,2)||'日之前的费用'; raise Ecustom; end if; end if; end if; --先更新冲销数量 update YF_YZYPSQ set BCCXSL=YPZSL0 where ZYID00=Pzyid00 and TQDJH0=Ptqdjh0 and YZID00=Pyzid00 and YPZSL0>0 and (vyyrq00='20991231' or yyrq00=vyyrq00); --循环申请退一个药品 for yp in C_YP loop --退预扣库存 if yp.QLZT00 in ('0','1') then SP_YF_KCYKMX(1, --0 预扣 1开单退预扣 2已记账退预扣 yp.YPQLPC, --来源单据号 '1', --门诊住院标志 '0'门诊 '1'住院 yp.YFBMBH, --药房部门编号 yp.YPNM00, --药品内码 yp.YPZSL0, --出库数量(处方单位) yp.QLDW00, --处方单位 yp.ZHL000, --转换率 Pczy000, --操作员 Pczyks0, --操作员科室 'N', --是否提交 Pzxcgbz, --执行成功标志 1成功 0失败 Ptsxx00, --执行结果提示信息，成功的情况下也可能有警告信息 Pxxxx00, --执行结果详细信息，成功的情况下也可能有警告信息，一般用来显示参数等指标 Vcklsj0, --出库零售价 Vckgjj0, --出库购进价 0, --药库入库批次,Pykrkpc>0情况：直接指定药库入库批次进行预扣 '2', --单据类型 0门诊处方 1 住院处方 2住院医嘱 3借药单 '0', --只更新批次 1只更新YF_PCKCMX 0更新YF_PCKCMX和YF_YPKCXX '1', --是否更新价格 1更新 0不更新 '1' --库存不足退出 0报错提示 1不提示直接退出 ); end if; if yp.QLZT00='0' then if Vsfbfty <> '1' then delete YF_YZYPSQ where YPQLPC=yp.YPQLPC and QLZT00='0'; else update YF_YZYPSQ set QLZT00='3' where YPQLPC=yp.YPQLPC and QLZT00='0'; end if; elsif yp.QLZT00='1' then--已经扣费的 --申请退药 SP_YF_YZYPSQ_CXCZ(yp.YPQLPC,Vcxqlpc,Pczy000,Pczyks0,'病区申请','N',Pzxcgbz,Ptsxx00,Pxxxx00,null); --退费 SP_YF_YZYPTF(Vcxqlpc,Pczy000,Pczyks0,'N',Pzxcgbz,Ptsxx00,Pxxxx00); end if; end loop; if Vsfbfty <> '1' then --删除执行记录 delete from BQ_YZZXLS where TQDJH0=Ptqdjh0 and YZID00=Pyzid00 and (vyyrq00='20991231' or ZXRQ00=vyyrq00); if BQ_TYHYXZTQ<>'N' then update BQ_YZCFMX set TQBZ00='0' where TQDJH0=Ptqdjh0 and YZID00=Pyzid00 and (vyyrq00='20991231' or ZXRQ00=vyyrq00); end if; update BQ_FJF000 set CXBZ00='+' where ZYID00=Pzyid00 and TQDJH0=Ptqdjh0 and YZID00=Pyzid00 and (VYYRQ00='20991231' or ZXRQ00 =VYYRQ00); update YJ_YW0000 A set XMZT00='6',ZXRQ00=to_char(sysdate,'yyyymmdd'),ZXSJ00=to_char(sysdate,'hh24:mi:ss'),ZXR000=Pczy000 where ZYGHID=Pzyid00 and MZZYBZ='1' and YZCFID=to_char(Pyzid00) and TQPC00=Ptqdjh0 and XMZT00='1' and FJDJBZ='e' and exists(select 1 from BQ_FJF000 where ZYID00=a.ZYGHID and TQDJH0=A.TQPC00 and ID0000||''=A.CFID00 and YZID00=Pyzid00 and (VYYRQ00='20991231' or ZXRQ00 =VYYRQ00)); end if; --如果是最后一次提取的医嘱，还应进行医嘱还原处理 if Vsffyjl<=0 and Vsfbfty <> '1' then select count(*) into Vcount0 from BQ_YZZXJL where TQDJH0=Ptqdjh0 and YZID00=Pyzid00 and ZT0000 in ('0','1') and TQDJH0=(select max(TQDJH0) from BQ_YZZXJL where YZID00=Pyzid00 and ZT0000<>'5'); if Vcount0>0 then--最后一次提取，医嘱还原 update BQ_YZZXJL SET ZT0000='5' where TQDJH0=Ptqdjh0 and YZID00=Pyzid00 and ZT0000 IN ('0','1') and (Vclbz00='1'or (Vclbz00='0' and (VYYRQ00='20991231' or QSRQ00 =VYYRQ00))); select max(JSRQ00||JSSJ00) into Vjsrqsj from BQ_YZZXJL where ZYID00=Pzyid00 and YZID00=Pyzid00 and ZT0000 IN ('0','1','2'); Vjsrq00:=substrb(VJSRQSJ,1,8); Vjssj00:=substrb(VJSRQSJ,9,8); if(BQ_TYHYXZTQ<>'N') then if Vjsrqsj is null then--第一次的提取 update BQ_YPYZ00 set TQRQ00=null, TQSJ00=null, ZXRQ00=null, YZZT00=decode(YZZT00,'2','1',YZZT00) where YZID00=Pyzid00 and CLBZ00='0'; update BQ_YPYZ00 set TQRQ00=null, TQSJ00=null, ZXRQ00=null, YZZT00='1', TZRQ00=null, TZSJ00=null, TZCZRQ=null, TZCZSJ=null, TZYS00=null, TZRXM0=null where YZID00=Pyzid00 and CLBZ00='1'; update BQ_YPYZMX X set TQRQ00=NULL,TQSJ00=NULL where YZID00=Pyzid00; else update BQ_YPYZ00 set TQRQ00=to_char(to_date(Vjsrq00,'YYYYMMDD')-1,'YYYYMMDD'), TQSJ00=Vjssj00 where YZID00=Pyzid00; update BQ_YPYZMX X set (TQRQ00,TQSJ00)= (select SJJSRQ,SJJSSJ from BQ_YZZXJL J where J.YZID00=Pyzid00 and J.YZMXID=X.YZMXID and JSRQ00=VJSRQ00 and JSSJ00=VJSSJ00 and ZT0000<>'5' and rownum=1) where YZID00=Pyzid00; end if; end if; else--不是最后一次提取,医嘱不回滚 update BQ_YZZXJL set ZT0000='2' where TQDJH0=Ptqdjh0 and YZID00=Pyzid00 and ZT0000 IN ('0','1') and (Vclbz00='1'or (Vclbz00='0' and (VYYRQ00='20991231' or QSRQ00 =VYYRQ00))); delete YF_YZYPSQ where ZYID00=Pzyid00 and TQDJH0=Ptqdjh0 and YZID00=Pyzid00 and CXBZ00='Z' and FYBZ00='0'and QLZT00 IN('0') and (VYYRQ00='20991231' or YYRQ00=VYYRQ00); --update YF_YZYPSQ set QLZT00='3' where ZYID00=Pzyid00 and TQDJH0=Ptqdjh0 and YZID00=Pyzid00 and CXBZ00='Z' and FYBZ00='0'and QLZT00 IN('0'); end if; end if; end if; --已领药品退费申请时,如果是未结算过的，则直接调用过程SP_YF_BQTYSQQR进行退药申请确认 if Vsfty00='Y' then begin select YFBMBH,BQBMBH,ZYH000 into Vyfbmbh,Vbqbmbh,Vzyh000 from YF_YZYPSQ L where ZYID00=Pzyid00 and QLZT00='0' and CXBZ00='-' and ROWNUM=1 and NOT EXISTS (select 1 from YF_YZYPSQ A,ZY_BRFY00 B where A.YPQLPC=L.CXPCID and A.DJH000=B.DJH000 and B.JZDH00<>0); exception when others then Vyfbmbh:=0; end; if Vyfbmbh<>0 then SP_YF_BQTYSQQR(TRIM(Vzyh000),0,0,0,Vyfbmbh,Vbqbmbh,Vckdh00); end if; end if; Pzxcgbz:=1; if Pcommit='Y' then commit; end if; exception when Ecustom then raise_application_error(-20016,Ptsxx00||'！*'); when no_data_found then raise_application_error(-20010, '数据没有找到！*'); when others then raise_application_error(-20020, substrb(NVL(SQLERRM, '原因不明出错')||'！*',1,220)); rollback; end;