CREATE OR REPLACE PROCEDURE SP_SF_BRXXB0_BRTH00 ( Pghh000 in varchar2 , --挂号号 Pczy000 in number , --操作员 Pczyks0 in number , --操作员科室 Pcommit in varchar2 default 'N' , --是否提交 Pzxcgbz out number , --执行成功标志 1成功 0失败 Ptsxx00 out varchar2 , --执行结果提示信息，成功的情况下也可能有警告信息 Pxxxx00 out varchar2 --执行结果详细信息，成功的情况下也可能有警告信息，一般用来显示参数等指标 ) -- MODIFICATION HISTORY -- Person Date Comments ---lintj 2012.08.03 create ---yangy 2012.08.21 冲销数量错误,退号退费过程错误 ---yangy 2012.09.17 存在已退费确认的项目，和已作废的处方，也要允许退号、、 ---yangy 2012.12.08 挂号之外的费用算法修改； -- jlg 2019.05.31 重新整理过程 MZSF9-20190530-003 -- qks 2020.06.04 判断费用是否已结算,条件从JZDH00+0<>0改为JZDH00+0>1; for YJ9-20200603-001 -- linzetao 2021.07.23 修正无法退号问题 MZSF9-20210722-001 -- qks 2021.08.26 bug:当参数YJ_XMCXZJTF=Y,门诊医生以及门诊收费系统退号,系统都会出现“挂号费退号失败！”,实际已退号成功; for MZSF9-20210826-001 -- jlg 2021.11.24 修改非挂号费用判断的错误的bug，原来按BM_ZLZD00.LBBH00 not in (20,21)判断，现在改为YJ_YW0000.FJDJBZ not in ('3','4')判断 MZSF9-20211124-001 -- chenHeyi 2022.11.04 参数MZHS_THSFJXSY=N时，号源表YS_HBHZXH只更改ZY0000，不复用此号源； MZSF9-20221026-002 -- ruanbh 2022.12.09 退号时限制必需先退二次挂号 MZDZBLS-20221209-003 -- chenHeyi 2023.06.16 扩展MZHS_THSFJXSY，此过程取第1段 MZHS9-20230609-001 -- jiangjh 2024.10.11 YS_CFXXB0.CFZT00 增加判断 not in('0','2') MZSF13-20241008-002 -- luowf 2024.01.14 当病人处方是已记账，已发药的状态，不允许病人退号成功。 MZSF13-20250107-002 -- jiangjh 2025.03.06 YF_MZCF00过滤已被退费的数据 MZSF13-20250304-002 ---------------------------------------------------------------------------------------- as Vczyxm0 varchar2(20); Vghksbh SF_YSGHHB.GHKSBH%type; --旧挂号对应的挂号科室编号 Vghysbh SF_YSGHHB.GHYSBH%type; --旧挂号对应的挂号医生编号 Vghlb00 SF_YSGHHB.GHLBBH%type; --旧挂号对应的挂号类别编号 Vghrq00 SF_YSGHHB.GHMZSJ%type; --旧挂号对应的挂号日期 Vghhbid SF_YSGHHB.GHHBID%type; --挂号号表ID Vid0000 SF_HZBR00.ID0000%type; --候诊类别ID,SF_HZBR00.ID0000 Vhzlb00 SF_BRXXB0.HZLB00%type; Vsfdtyy SF_HZBR00.SFDTYY%type; --是否当天预约：'1'当天预约 Vcxdjh0 YJ_YW0000.YJDJH0%type; --冲销单据号 Vczrq00 char(8); Vczsj00 char(8); Vcounter number(14,4); --计数器变量 Ecustom exception; aYjdjh0List varchar2(200); i number(3); j number(3); sYjdjh0 varchar2(20); sSl0000 varchar2(10); Vhjje00 SF_BRFY00.HJJE00%type; --合计金额 VTHSFJXSY XT_XTCS00.VALUE0%type; --患者取某个现场号源后,护士做退预挂号操作后,是否允许该号源继续使用 VBRID00 SF_BRXXB0.BRID00%type; --病人id YS_BRECGHLB XT_XTCS00.VALUE0%type; --二次挂号类别 cursor CUR_YJYW00 is select YJDJH0,SL0000 from YJ_YW0000 a where ZYHGHH = trim(Pghh000) and MZZYBZ = '0' and nvl(CXDJH0,0)=0 and a.zje000>0 and a.xmzt00<>'6' and not exists(select 1 from YJ_YW0000 where CXDJH0=a.YJDJH0 and zyghid=a.zyghid and mzzybz=a.mzzybz and xmzt00 in ('2','3')); begin Pzxcgbz := 0; Vczrq00 := to_char(sysdate,'yyyymmdd'); Vczsj00 := to_char(sysdate,'hh24:mi:ss'); Pxxxx00 :=nvl(to_char(trim(Pghh000)),'null')||','||nvl(to_char(Pczy000),'null')||','||nvl(to_char(Pczyks0),'null')||','||nvl(to_char(Pcommit),'null'); SP_TransLog(sysdate,'SP_SF_BRXXB0_BRTH00',Pczy000,Pczyks0,Pxxxx00); select nvl(max(substrb(trim(VALUE0),1,1)),'Y') into VTHSFJXSY from XT_XTCS00 where NAME00='MZHS_THSFJXSY'; if VTHSFJXSY<>'Y' and VTHSFJXSY<>'N' then VTHSFJXSY:='Y'; end if; --处方未退药，不能退号 --SF_WJZCFSFYXTH --含有未记帐的医技单，是否作废该医技单 --含有未记帐的处方单，是否作废该处方单 --作废未记账的医技单，处方单 --SP_SF_BRGHXX_YBEDIT 门诊医保病人挂号冲销补充存储过程 合并到这里处理吧 select count(*) into Vcounter from SF_FYMX00 where JZDH00+0>1 and JFLBID=1 and MZH000 = trim(Pghh000); if Vcounter > 0 then Ptsxx00 :='预交金病人该挂号有费用结算过，不能退号!'; raise Ecustom; end if; select sum(HJJE00) into Vhjje00 from SF_FYMX00 a where a.MZH000 = trim(Pghh000) and a.CXBZ00='Z' and exists(select 1 from YJ_YW0000 d where d.ZYHGHH=a.MZH000 and d.MZZYBZ='0' and d.SFDJH0=a.DJH000 and d.FJDJBZ not in ('3','4')); --3挂号费4诊查费 if Vhjje00 > 0 then Ptsxx00 :='该挂号还有除挂号费用外的其它费用，不能退号!'; raise Ecustom; end if; --select count(*) into Vcounter from YS_CFXXB0 a,SF_BRXXB0 c where a.GHID00=c.GHID00 and c.GHH000 = trim(Pghh000) and c.THBZ00='2' and a.CFZT00 not in('0','2'); ---原有语句会导致门诊电子病历在已经对所有药品退费后仍然无法退号 /*select count(*) into Vcounter from YF_MZCF00 a,SF_BRXXB0 c where a.GHID00=c.GHID00 and c.GHH000 = trim(Pghh000) and c.THBZ00='2' and a.CFZT00 not in ('0','3','6') and not exists(select 1 from YF_MZCF00 where GHID00=a.GHID00 and BCCFH0=a.CFLSH0 and abs(CFZJE0)=a.CFZJE0);*/ ---故修改语句为此语句 select count(*) into Vcounter from YF_MZCF00 a,SF_BRXXB0 c where a.GHID00=c.GHID00 and c.GHH000 = trim(Pghh000) and c.THBZ00='2' and a.CFZT00 not in ('0','3','6')and not exists ( select 1 from YF_MZCF00 where BCCFH0 is not null and cfzt00='6' and BCCFH0=a.cflsh0); if Vcounter <> 0 then Ptsxx00 :='有处方存在，须先经医生申请才可进行退号!'; raise Ecustom; end if; select count(*) into Vcounter from YJ_YW0000 a,bm_zlzd00 b, SF_BRXXB0 c where a.ZYGHID=c.GHID00 and a.ZLXMID=b.ZLXMID and A.MZZYBZ='0' and c.GHH000 = trim(Pghh000) and c.THBZ00='2' and a.XMZT00<>'6' and a.ZJE000>0 and b.lbbh00 not in (20,21) and not exists(select 1 from YJ_YW0000 d where d.ZYGHID=a.ZYGHID and d.MZZYBZ='0' and d.CXDJH0=a.YJDJH0 and d.XMZT00 in ('2','3')); if Vcounter <> 0 then Ptsxx00 :='存在有效的非药品项目，不能进行退号!'; raise Ecustom; end if; select ZWXM00 into Vczyxm0 from BM_YGBM00 where YGBH00 = Pczy000; select GHKS00,JZYS00,GHLB00,GHRQ00,BRID00 into Vghksbh,Vghysbh,Vghlb00,Vghrq00,VBRID00 from SF_BRXXB0 where GHH000 = trim(Pghh000); select nvl(max(VALUE0),'N') into YS_BRECGHLB from XT_XTCS00 where NAME00='YS_BRECGHLB'; if instr(','||YS_BRECGHLB||',',','||Vghlb00||',')=0 then select count(*) into Vcounter from SF_BRXXB0 A where A.GHRQ00=Vghrq00 and A.JZYS00=Vghysbh and A.BRID00=VBRID00 and instr(','||YS_BRECGHLB||',',','||A.GHLB00||',')>0 AND A.THBZ00<>'0'; if Vcounter <> 0 then Ptsxx00 :='存在二次挂号，不能进行退号，请先退二次挂号!'; raise Ecustom; end if; end if; select nvl(max(ID0000),0),nvl(max(YSPBID),0),nvl(max(HZLB00),0),nvl(max(SFDTYY),'0') into Vid0000,Vghhbid,Vhzlb00,Vsfdtyy from SF_HZBR00 where GHH000 = trim(Pghh000) and HZZT00<>'Z'; if VTHSFJXSY='N' then --该号源不允许使用时，只更改ZY0000；BRID00不动 update YS_HBHZXH set ZY0000='挂号:'||Pghh000||',BRID00:'||to_char(BRID00)||'使用过' where GHHBID=Vghhbid and GHH000=Pghh000; else if Vhzlb00=3 then--退预约号 update SF_YSGHHB set GHYGHS = nvl(GHYGHS,0)-1, GHYYHS = nvl(GHYYHS,0)-1, YYXHS0 = nvl(YYXHS0,0)-decode(Vsfdtyy,'1',1,0), TQYYS0 = nvl(TQYYS0,0)-decode(Vsfdtyy,'1',1,0) where GHKSBH = Vghksbh and GHYSBH = Vghysbh and GHLBBH = Vghlb00 and GHMZSJ = Vghrq00 and (GHHBID = Vghhbid or Vghhbid = 0) and GHYGHS >= 1 and rownum = 1; else update SF_YSGHHB set GHYGHS = nvl(GHYGHS,0)-1 where GHKSBH = Vghksbh and GHYSBH = Vghysbh and GHLBBH = Vghlb00 and GHMZSJ = Vghrq00 and (GHHBID = Vghhbid or Vghhbid = 0) and GHYGHS >= 1 and rownum = 1; end if; update YS_HBHZXH set ZY0000='挂号:'||Pghh000||',BRID00:'||to_char(BRID00)||'使用过',BRID00=null,BRXM00=null,GHH000=null,YYBZ00='1' where GHHBID=Vghhbid and GHH000=Pghh000; end if; update SF_BRXXB0 set THRQ00 = Vczrq00, THSJ00 = Vczsj00, THY000 = Pczy000, THYXM0 = Vczyxm0, THBZ00 = '0' --该挂号信息已退号 where GHH000 = trim(Pghh000); --普通病人保留旧记录，其他病人删除候诊记录 delete from SF_HZBR00 where ID0000 = Vid0000 and HZLB00<>'1'; --将病人候诊表中的信息删除 update SF_HZBR00 set DLXH00=-100,HZZT00='Z' where ID0000=Vid0000 and HZLB00='1'; --update YS_HBHZXH set ZY0000='挂号:'||Pghh000||',BRID00:'||to_char(BRID00)||'使用过',BRID00=null,BRXM00=null,GHH000=null,YYBZ00='1' where GHHBID=Vghhbid and GHH000=Pghh000; i:=0; aYjdjh0List := ''; for reg in CUR_YJYW00 loop aYjdjh0List := reg.yjdjh0||','||reg.sl0000||';'||aYjdjh0List; end loop; while (aYjdjh0List is not null or length(aYjdjh0List)=0) loop i := instrb(aYjdjh0List,','); sYjdjh0 := substr(aYjdjh0List,1,i-1); j := instrb(aYjdjh0List,';'); sSl0000 := substr(aYjdjh0List,i+1,j-i-1); aYjdjh0List := substr(aYjdjh0List,j+1,length(aYjdjh0List)-j); if (sYjdjh0 is not null) and (to_number(sSl0000)>0) then begin SP_YJ_YWCX( sYjdjh0, --医技单据号 Pczy000, --员工编号 Pczyks0, --科室编号 'N', --病区申请的项目是否直接退费 'N', --是否重新申请 to_number(sSl0000) --冲消数量 ); exception when others then Ptsxx00 := '挂号费退费失败！'; Pxxxx00 := '执行医技业务冲销过程SP_YJ_YWCX失败'||substrb(sqlerrm,1,150)||'！*'; raise Ecustom; end; select nvl(max(YJDJH0),0) into Vcxdjh0 from YJ_YW0000 where ZYHGHH=Pghh000 and CXDJH0=sYjdjh0 and nvl(sfdjh0,0)=0; if Vcxdjh0 > 0 then begin SP_SF_YJGZ00_YJD000( Vcxdjh0, --医技单据号 Pczy000, --操作员 Vczyxm0, --操作员姓名 Pczyks0, --操作员科室 Pczy000, --审核员 '', --审核原因 Ptsxx00, --存储过程提示的错误信息 Pxxxx00, --系统提示的错误信息 '0', --是否收费员登记, 0否1是 'N' --是否提交 ); if trim(Ptsxx00) is not null then raise Ecustom; end if; exception when others then Ptsxx00 := '挂号费退费确认失败！'; Pxxxx00 := '执行费用更正过程SP_SF_YJGZ00_YJD000失败'||substrb(sqlerrm,1,150)||'！*'; raise Ecustom; end; end if; end if; end loop; Pzxcgbz:=1; if pcommit='Y' then commit; end if; exception when no_data_found then raise_application_error(-20001,substrb('没有找到数据！*'||sqlerrm,1,240)); when Ecustom then --Pxxxx00 := '执行费用更正过程SP_SF_YJGZ00_YJD000失败！*'||nvl(sqlerrm,'原因不明出错！'); Pxxxx00 := Pxxxx00; --raise_application_error(-20010,substrb(Ptsxx00||'！*',1,240)); when others then raise_application_error(-20002,substrb(nvl(sqlerrm, '原因不明出错')||'！*',1,240)); end;