PROMPT 1、医技业务冲销冲销单据 SF_YJ_YWCX_CXDJH PROMPT 2、医技业务冲销过程 SP_YJ_YWCX ------- ---------------------- -------------------------------------------------- PROMPT 1、医技业务冲销冲销单据 SF_YJ_YWCX_CXDJH create or replace function SF_YJ_YWCX_CXDJH ( Pyjdjh0 in number , --医技单据号 Psfcxfy in number , --是否直接退费 0:否,1:是 Perrmsg out varchar2 , --错误信息 Pczy000 in number , --操作员 Pczyks0 in number , --操作员科室 Pcxsl00 in number default null , --冲销数量 Pbz0000 in varchar2 default '' --冲销备注 ) -- MODifICATION HisTORY -- Person Date Comments -- lintj 2013.07.20 写YSZID0 -- lintj 2012.08.08 modify -- yangy 2013.03.30 冲销时负的yj_yw0000表要写入kdbq00 -- zhangwz 2013.08.07 冲销时负的yj_yw0000表要写入DQKS00 -- qks 2013.08.21 增加高值耗材冲销计价 -- qks 2013.09.12 SP_YJ_GZHCJJ->SP_KS_GZHCJJ -- zhangwz 2014.06.10 YJ_YW0000表增加YYID01数据，冲销的时候也要处理这个数据 -- qks 2014.08.25 写YYID00值 -- qks 2017.06.25 YJ_YW0000.XMZK00,TCID00,TCNWBZ三个字段值写入; for YJ9-20170214-001 -- qks 2017.06.28 当参数SF_SFYXWJSFYZKCL=Y,如果YJ_YWJJ00.JMJE00有值,退费时要减去这个金额后再计算出新单价; for MZSF9-20170627-004 -- qks 2017.07.13 处理当Pcxsl00入参值为空值,退费会报错问题; for YJ9-20170713-001 -- qks 2018.06.05 整单冲销时需要考虑子项目已进行部分退费申请; for SSMZ9-20180529-001 -- qks 2018.06.06 当总单退费申请时（实际已分几个子单完成退费申请）,不产生为0的记录; for SSMZ9-20180606-002 -- qks 2018.06.11 修正写入YJ_YWJJ00表数据错误问题,还原处理; for SSMZ9-20180606-002 -- qks 2018.06.28 取最大ZY_BRXXB0.ZYID00值,即MAX(ZYID00),取消入院病人不参与判断; for YJ9-20180628-001 -- jlg 2019.04.30 调用SP_YJ_ZLDCCF_TF0000时增加入参Pczyks0 YJ9-20190429-001 -- liwm 2019.06.23 老年旧医技项目退费异常问题 YJ9-20190623-001 -- qks 2019.08.08 负单也需要写入YJ_YWJJ00.CKMXID值,解决：当参数YJ_XMCXZJTF启用,耗材的收费项目退费后,对应的耗材科室库存没有退还问题。for KCWZ9-20190808-003 -- linzetao 2020.04.22 优化医技单项目收费次数为0的提示信息 YJ9-20200417-001 -- linzetao 2020.06.01 处理医技单分次退费时带出的药品被多次退费的问题，修正为医技单全部退完后才会对带出的药品进行退费 YJ9-20200527-001 -- linzetao 2021.10.10 处理医技单子单部分退费后无法对剩余部分进行整单退费问题 YJ9-20210905-001 -- linzetao 2021.11.05 处理公费病人退费问题 YJ9-20211105-001 -- liyanni 2022.08.31 modify 退费申请时添加退费说明 for BQHSEX-20220826-004 return number --1：成功 ，0：失败 as Yw_Row YJ_YW0000%rowtype; Jj_Row YJ_YWJJ00%rowtype; Mx_Row YJ_YWMX00%rowtype; Ecustom exception; --错误描述 Verrmsg char(255); Vyjdjh0 number; Vywmxid number; Vywjjid number; --收费单据号 Vdjh000 number; --操作员姓名 Ls_Czyxm0 BM_YGBM00.ZWXM00%type; Vcounter number; --冲销标志 Ls_Cxbz00 char; --结帐单号 Vjzdh00 number; Vparams varchar2(500); Ls_Nodata char(255); Ls_Zwxm00 BM_YGBM00.ZWXM00%type; Ld_Fbbh00 bm_brfbb0.FBBH00%type; Ld_Jflbid bm_brjflb.JFLBID%type; Vzldccf YJ_YW0000.ZLDCCF%type; Vcflsh2 YF_MZCF00.CFLSH0%type; --是否向医保发送减免金额 Ls_Fsjmje char(1); Vmxid00 SF_FYMX00.MXID00%type; Vzxcgbz number; Vtsxx00 varchar2(200); Vxxxx00 varchar2(200); --参数SF_SFYXWJSFYZKCL:是否启用折扣处理 Vsfqyzk number(1); Vcbid00 YJ_YWJJ00.ID0000%type; Ldcxsl00 YJ_YWJJ00.CXSL00%type; begin Vparams:='参数:Pyjdjh0='||Pyjdjh0||',Psfcxfy='||Psfcxfy||',Perrmsg OUT,Pczy000='||Pczy000||',Pczyks0='||Pczyks0; select ZWXM00 into Ls_Zwxm00 from BM_YGBM00 where YGBH00=Pczy000; select * into Yw_Row from YJ_YW0000 where YJDJH0=Pyjdjh0; select count(1) into Vsfqyzk from XT_XTCS00 where NAME00='SF_SFYXWJSFYZKCL' and VALUE0='Y'; if nvl(YW_ROW.CXSL00,0)+nvl(Pcxsl00,YW_ROW.SL0000)>YW_ROW.SL0000 then Perrmsg:='本次冲消数量+已冲消数量不能超过原数量,请刷新后再试!'; return 0; end if; if Yw_Row.XMZT00='6' then Perrmsg:='该项目已经作废*'; return 0; elsif Yw_Row.XMZT00='7' then Perrmsg:='该项目已经退费*'; return 0; end if; if Yw_Row.MZZYBZ='1' then select max(ZYID00) into Vcounter from ZY_BRXXB0 where ZYH000=Yw_Row.ZYHGHH and substrb(BRZT00,1,1)<>'9'; if Yw_Row.ZYGHID<>Vcounter then Perrmsg:='不能冲销病人上次住院的诊疗项目*'; return 0; end if; end if; --新的YJDJH0 select SQ_YJ_YW0000_YJDJH0.nextval into Vyjdjh0 from dual; if Yw_Row.ZLDCCF is not null and Yw_Row.CXSL00+Pcxsl00 >= Yw_Row.SL0000 and Yw_Row.FJDJBZ <> '7' then --冲销处方单 Vzldccf :=SF_YJ_ZLDCYP(Yw_Row.ZLXMID,Yw_Row.ZYGHID,Yw_Row.MZZYBZ,Yw_Row.YJKSBH,Yw_Row.KDYS00,Yw_Row.SL0000,Yw_Row.ZLDCCF,4,Yw_Row.SL0000); end if; --冲销YJ_YW0000 insert into YJ_YW0000 (YJDJH0, YJFZH0, YJKSBH, BRID00, ZYGHID, BRXM00, YEXM00, ZLXMJC, KDYS00, KDKSBH, KDRQ00, KDSJ00, LRXM00, JZBZ00, FBZXBZ, BWMC00, APRXM0, APRQ00, APSJ00, ZXR000, ZXRXM0, ZXRQ00, ZXSJ00, JGLR00, ZDMC00, JGBGRQ, JGBGYS, BGYSXM, XMZT00, MZZYBZ, CXDJH0, BZ0000, XB0000, CSRQ00, ZDID00, ZLXMID, SL0000, ZXSL00, DW0000, YZCFID, YPNM00, DCBZ00, ZYHGHH, ZJE000, TQPC00, YJLRBZ, ZLDCCF, BH0000, KDYSXM, YSSZKS, FJDJBZ, KDBQ00, DQKS00, YSZID0, YYID01, YYID00, XMZK00, TCID00, TCNWBZ, SM0000) values (Vyjdjh0, Yw_Row.YJFZH0,Yw_Row.YJKSBH, Yw_Row.BRID00, Yw_Row.ZYGHID , Yw_Row.BRXM00, Yw_Row.YEXM00, Yw_Row.ZLXMJC, Yw_Row.KDYS00, Yw_Row.KDKSBH, to_char(sysdate,'YYYYMMDD'),to_char(sysdate,'HH24:MI:SS'), Ls_Zwxm00, Yw_Row.JZBZ00, Yw_Row.FBZXBZ, Yw_Row.BWMC00, Yw_Row.APRXM0, Yw_Row.APRQ00, Yw_Row.APSJ00, Yw_Row.ZXR000, Yw_Row.ZXRXM0, Yw_Row.ZXRQ00, Yw_Row.ZXSJ00, Yw_Row.JGLR00, Yw_Row.ZDMC00, Yw_Row.JGBGRQ, Yw_Row.JGBGYS, Yw_Row.BGYSXM, '1', Yw_Row.MZZYBZ, Pyjdjh0, Yw_Row.BZ0000,Yw_Row.XB0000, Yw_Row.CSRQ00, Yw_Row.ZDID00, Yw_Row.ZLXMID, -nvl(Pcxsl00, Yw_Row.SL0000), -nvl(Pcxsl00, Yw_Row.ZXSL00), Yw_Row.DW0000, Yw_Row.YZCFID, Yw_Row.YPNM00, Yw_Row.DCBZ00, Yw_Row.ZYHGHH, -nvl(Yw_Row.ZJE000*Pcxsl00/Yw_Row.SL0000, Yw_Row.ZJE000), Yw_Row.TQPC00, Yw_Row.YJLRBZ, Vzldccf, Yw_Row.BH0000, Yw_Row.KDYSXM,Yw_Row.YSSZKS, Yw_Row.FJDJBZ,Yw_Row.KDBQ00,Yw_Row.DQKS00,Yw_Row.YSZID0,Yw_Row.YYID01,Yw_Row.YYID00, Yw_Row.XMZK00, Yw_Row.TCID00, Yw_Row.TCNWBZ,decode(Yw_Row.SM0000,'',Pbz0000,Yw_Row.SM0000||' | '||Pbz0000)); --执行记录增加负记录 if Yw_Row.ZXRQ00<>null or Yw_Row.ZXSJ00<>null then insert into YJ_YWZXJL(YJDJH0,ZXRQ00,ZXSJ00,ZXCS00,ZXR000) values(Vyjdjh0,Yw_Row.ZXRQ00,Yw_Row.ZXSJ00,-nvl(Pcxsl00, Yw_Row.ZXSL00),Yw_Row.ZXR000); end if; --冲销YJ_YWMX00 insert into YJ_YWMX00 (ID0000,YJDJH0,XMMC00,JGDW00,CKZ000,JGMS00,BZ0000,ZXRXM0,ZXRQ00,ZXSJ00,ZLXMID,SL0000,DW0000) select SQ_YJ_YWMX00_ID0000.nextval,Vyjdjh0,XMMC00,JGDW00,CKZ000,JGMS00,BZ0000,ZXRXM0,ZXRQ00,ZXSJ00,ZLXMID, nvl(SL0000*Pcxsl00/Yw_Row.SL0000, SL0000),DW0000 from YJ_YWMX00 where YJDJH0=Pyjdjh0 ; select count(*) into Vcounter from YJ_YWJJ00 where YJDJH0=Pyjdjh0 and SL0000-CXSL00>0 and SFCS00=0; if Vcounter>0 then Perrmsg := '医技单('||Pyjdjh0||')存在收费次数为0的收费项目, 冲销失败'; return 0; end if; --2021.10.10 --insert into YJ_YWJJ00 (ID0000,YJDJH0,YJFZH0,SFXMID,SFXX00,JJJE00,GFJE00,ZFJE00,QZFJE0,SFSX00,YBBZ00, -- JZJE00,CXBZ00,SL0000,SFCS00,GDBZ00,FJFYBZ,ZFBL00,SFLB00,GJBM00,JMJE00,CXID00,CKMXID) -- select SQ_YJ_YWJJ00_ID0000.NEXTVAL,Vyjdjh0,YJFZH0,SFXMID,SFXX00, -- JJJE00-round(Vsfqyzk*nvl(JMJE00,0)*nvl(Pcxsl00/YW_ROW.SL0000,1)/(SL0000*SFCS00),4), -- -GFJE00*nvl(Pcxsl00/YW_ROW.SL0000,1), -- -(ZFJE00-Vsfqyzk*nvl(JMJE00,0))*nvl(Pcxsl00/YW_ROW.SL0000,1), -- -QZFJE0*nvl(Pcxsl00/YW_ROW.SL0000,1), -- SFSX00,YBBZ00, -- -JZJE00*nvl(Pcxsl00/YW_ROW.SL0000,1), -- '-', -- -SL0000*nvl(Pcxsl00/YW_ROW.SL0000,1), -- SFCS00,GDBZ00,FJFYBZ, -- ZFBL00,SFLB00,GJBM00,-JMJE00*nvl(Pcxsl00/YW_ROW.SL0000,1),ID0000,CKMXID -- from YJ_YWJJ00 where YJDJH0=Pyjdjh0 and SL0000-CXSL00>0; --update YJ_YWJJ00 SET CXBZ00='+', CXSL00=nvl(CXSL00,0)+nvl(SL0000*Pcxsl00/YW_ROW.SL0000, SL0000) where YJDJH0=Pyjdjh0; for ywjj in (select * from YJ_YWJJ00 where YJDJH0=Pyjdjh0 and SL0000-CXSL00>0) loop Ldcxsl00 := ywjj.SL0000*nvl(Pcxsl00/Yw_Row.SL0000,1); if Ldcxsl00 > ywjj.SL0000 - ywjj.CXSL00 then Ldcxsl00 := ywjj.SL0000 - ywjj.CXSL00; end if; insert into YJ_YWJJ00(ID0000,YJDJH0,YJFZH0,SFXMID,SFXX00, JJJE00,GFJE00,ZFJE00,QZFJE0, SFSX00,YBBZ00, JZJE00,CXBZ00,SL0000,SFCS00,GDBZ00,FJFYBZ,ZFBL00,SFLB00,GJBM00,JMJE00,CXID00) values(SQ_YJ_YWJJ00_ID0000.nextval,Vyjdjh0,ywjj.YJFZH0,ywjj.SFXMID,ywjj.SFXX00, ywjj.JJJE00-round(Vsfqyzk*nvl(ywjj.JMJE00,0)*nvl(Pcxsl00/Yw_Row.SL0000,1)/(ywjj.SL0000*ywjj.SFCS00),4), -ywjj.GFJE00*((Ldcxsl00)/ywjj.SL0000), -(ywjj.ZFJE00*((Ldcxsl00)/ywjj.SL0000)-Vsfqyzk*nvl(ywjj.JMJE00,0)), -ywjj.QZFJE0*((Ldcxsl00)/ywjj.SL0000), ywjj.SFSX00,ywjj.YBBZ00, -ywjj.JZJE00*((Ldcxsl00)/ywjj.SL0000), '-', -ldCXSL00,ywjj.SFCS00,ywjj.GDBZ00,ywjj.FJFYBZ, ywjj.ZFBL00,ywjj.SFLB00,ywjj.GJBM00, -ywjj.JMJE00*((Ldcxsl00)/ywjj.SL0000), ywjj.ID0000); update YJ_YWJJ00 set CXBZ00='+', CXSL00=nvl(CXSL00,0)+nvl(Ldcxsl00, SL0000) where YJDJH0=Pyjdjh0 and ID0000=ywjj.ID0000; end loop; -- --2018.06.05 整单冲销时需要考虑子项目已进行部分退费申请 -- insert into YJ_YWJJ00 (ID0000,YJDJH0,YJFZH0,SFXMID,SFXX00,JJJE00,GFJE00,ZFJE00,QZFJE0,SFSX00,YBBZ00, -- JZJE00,CXBZ00,SL0000,SFCS00,GDBZ00,FJFYBZ,ZFBL00,SFLB00,GJBM00,JMJE00,CXID00) -- select SQ_YJ_YWJJ00_ID0000.NEXTVAL,Vyjdjh0,YJFZH0,SFXMID,SFXX00,JJJE00-round(Vsfqyzk*nvl(JMJE00,0)*nvl(Pcxsl00/YW_ROW.SL0000,1)/(SL0000*SFCS00),4),-GFJE00*((SL0000-CXSL00)/SL0000)*nvl(Pcxsl00/YW_ROW.SL0000,1), -- -(ZFJE00*((SL0000-CXSL00)/SL0000)-Vsfqyzk*nvl(JMJE00,0))*nvl(Pcxsl00/YW_ROW.SL0000,1),-QZFJE0*((SL0000-CXSL00)/SL0000)*nvl(Pcxsl00/YW_ROW.SL0000,1),SFSX00,YBBZ00, -- -JZJE00*((SL0000-CXSL00)/SL0000)*nvl(Pcxsl00/YW_ROW.SL0000,1),'-',-(SL0000-CXSL00)*nvl(Pcxsl00/YW_ROW.SL0000,1),SFCS00,GDBZ00,FJFYBZ, -- ZFBL00,SFLB00,GJBM00,-JMJE00*((SL0000-CXSL00)/SL0000)*nvl(Pcxsl00/YW_ROW.SL0000,1),ID0000 -- from YJ_YWJJ00 where YJDJH0=Pyjdjh0 and SL0000-CXSL00>0; -- update YJ_YWJJ00 SET CXBZ00='+', CXSL00=nvl(CXSL00,0)+nvl((SL0000-CXSL00)*Pcxsl00/YW_ROW.SL0000, SL0000) where YJDJH0=Pyjdjh0; select nvl(max(ID0000),0) into VCBID00 from YJ_YWJJ00 where YJDJH0=Pyjdjh0 and CXSL00>SL0000; if Vcbid00>0 then select '项目:'||substrb(A.XMMC00,1,100)||',退费合计数量'||to_char(B.CXSL00)||' 超过原数量'||to_char(B.SL0000)||',请选择子项目进行退费申请!' into Perrmsg from BM_YYSFXM A,YJ_YWJJ00 B where A.SFXMID=B.SFXMID and B.ID0000=Vcbid00; return 0; end if; select count(1) into Vcounter from YJ_YWJJ00 where YJDJH0=Vyjdjh0; if Vcounter=0 then Perrmsg := '“'||Yw_Row.ZLXMJC||'”项目已经全部申请退费过,请重新确认！'; return 0; end if; update YJ_YW0000 set CXSL00=nvl(CXSL00,0) + nvl(Pcxsl00, SL0000) where YJDJH0 = Pyjdjh0; update YJ_YW0000 A set ZJE000=(select nvl(round(sum(JJJE00*SL0000*SFCS00),2),0) from YJ_YWJJ00 where YJDJH0=A.YJDJH0) where YJDJH0=Vyjdjh0; --高值耗材 select count(1) into Vcounter from YJ_YWJJ00 A,WZ_GZHCJJ B where A.YJDJH0=Pyjdjh0 and A.ID0000=B.JJID00 and nvl(B.JZRQ00,'20991231')<>'20991231'; if Vcounter>0 then SP_KS_GZHCJJ( 2, --操作内容 1修改 2增加 3删除 2, --收费退费标志: 1收费 2退费 null, --YJ_YWJJ00.ID0000 Vyjdjh0, --医技单据号 null, --数量 null, --药品入库批次(高值耗材预入库) null, --耗材内码(高值耗材,无预入库单) null, --批次条形码(高值耗材,无预入库单) Pczy000, --操作员编码 Pczyks0, --操作员科室 'N', --是否提交 Vzxcgbz, --执行成功标志 1成功 0失败 Vtsxx00, --执行结果提示信息，成功的情况下也可能有警告信息 Vxxxx00 --执行结果详细信息，成功的情况下也可能有警告信息，一般用来显示参数等指标 ); if Vzxcgbz = 0 then return 0; end if; end if; --return 1;--不冲销费用表，全部由收费处退费 --if YW_ROW.MZZYBZ='0' then return 1; end if; --门诊病人费用，不能直接冲掉 if Psfcxfy=0 then return 1; end if; --不直接冲销退费 if Yw_Row.BRID00=0 then return 1; end if; if Yw_Row.SFDJH0 is null then Perrmsg:='没有相关连的收费单据号'; return 0; end if; if Yw_Row.MZZYBZ<>'1' then--门诊 select count(*) into Vcounter from SF_BRFY00 where DJH000=Yw_Row.SFDJH0; else--住院 select count(*) into Vcounter from ZY_BRFY00 where DJH000=Yw_Row.SFDJH0; end if; if Vcounter <1 then Perrmsg:='病人费用表中没有相关连的收费单据号'; return 0; end if; if Yw_Row.MZZYBZ<>'1' then select JZDH00 into VJZDH00 from SF_BRFY00 where DJH000=Yw_Row.SFDJH0 ; else select JZDH00 into VJZDH00 from ZY_BRFY00 where DJH000=Yw_Row.SFDJH0; end if; if VJZDH00>0 then Perrmsg:='已经结帐'; return 1; end if; if Yw_Row.MZZYBZ<>'1' then --门诊 select count(*) into Vcounter from SF_FYMX00 where DJH000=Yw_Row.SFDJH0 and SFYDJ0='1'; else select count(*) into Vcounter from ZY_FYMX00 where DJH000=Yw_Row.SFDJH0 and SFYDJ0='1'; end if; if Vcounter >0 then Perrmsg:='该项目由收费处收费,不能冲销费用表'; return 1; end if; --操作员姓名 select ZWXM00 into Ls_Czyxm0 from BM_YGBM00 where YGBH00=Pczy000; if Yw_Row.MZZYBZ<>'1' then--门诊 select SQ_SF_BRJFB0_DJH000.nextval into Vdjh000 from dual; SP_SF_YJDJ00( Vyjdjh0, --医技单据号(YJDJH1,YJDJH2,YJDJH3...) Vdjh000, --单据号 Yw_Row.YJKSBH, --执行科室 nvl(Yw_Row.ZXR000, pczy000), --执行医生 to_char(sysdate,'YYYYMMDD'), --操作日期 to_char(sysdate,'HH24:MI:SS'), --操作时间 Perrmsg, --存储过程提示的错误信息 Perrmsg, --系统提示的错误信息 '0', --收费员登记 1是 0否 '-' --操作标志 Z记账插入 -冲销插入 ); update YJ_YW0000 set SFDJH0=Vdjh000,XMZT00=Yw_Row.xmzt00 where YJDJH0=Vyjdjh0; update SF_FYMX00 set ZXRQ00=to_char(sysdate,'YYYYMMDD'),ZXSJ00=to_char(sysdate,'HH24:MI:SS'),ZXYS00=nvl(Yw_Row.ZXR000,pczy000) where DJH000=Vdjh000; else--住院 select SQ_ZY_BRJFB0_DJH000.nextval into Vdjh000 from dual; SP_ZY_YJDJ00( Vyjdjh0, --医技单据号(YJDJH1,YJDJH2,YJDJH3...) Vdjh000, --单据号 Yw_Row.YJKSBH, --执行科室 nvl(Yw_Row.ZXR000, Pczy000), --执行医生 to_char(sysdate,'YYYYMMDD'), --操作日期 to_char(sysdate,'HH24:MI:SS'), --操作时间 Perrmsg, --存储过程提示的错误信息 Perrmsg, --系统提示的错误信息 '0', --收费员登记 1是 0否 '-' --操作标志 Z记账插入 -冲销插入 ); update YJ_YW0000 set SFDJH0=Vdjh000,XMZT00=Yw_Row.xmzt00 where YJDJH0=Vyjdjh0; update ZY_FYMX00 set ZXRQ00=to_char(sysdate,'YYYYMMDD'),ZXSJ00=to_char(sysdate,'HH24:MI:SS'),ZXYS00=nvl(Yw_Row.ZXR000, Pczy000) where DJH000=Vdjh000; end if; --自动退诊疗费的同时，也自动退诊疗项目带出的处方 if Yw_Row.ZLDCCF is not null then --累计冲销数量达到项目数量时，才退诊疗项目带出的处方 if Yw_Row.CXSL00 + Pcxsl00 >= Yw_Row.SL0000 then select ZLDCCF into Vzldccf from YJ_YW0000 where YJDJH0=Vyjdjh0; for i in 0..1 loop if (instrb(Vzldccf,',')<>0) then if i=0 then Vcflsh2:=substrb(trim(Vzldccf),1,instrb(trim(Vzldccf),',')-1); end if; if i=1 then Vcflsh2:=substrb(trim(Vzldccf),instrb(trim(Vzldccf),',')+1); end if; else if i=0 then Vcflsh2:=trim(Vzldccf); end if; if i=1 then Vcflsh2:='0'; end if; end if; if nvl(Vcflsh2,'0')<>'0' then SP_YJ_ZLDCCF_TF0000(Vcflsh2,Yw_Row.MZZYBZ,Pczy000,Pczyks0); end if; end loop; end if; end if; return 1; exception when Ecustom then raise; when no_data_found then raise; when others then raise; rollback; end; / --%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% PROMPT 2、医技业务冲销过程 SP_YJ_YWCX create or replace procedure SP_YJ_YWCX( Pyjdjh0 number, --医技单据号 Pczy000 number, --员工编号 Pczyks0 number, --科室编号 Psfzjtf varchar2 default 'N', --病区申请的项目是否直接退费 Psfcxsq varchar2 default 'N', --是否重新申请 Pcxsl00 number default null, --冲消数量 Pcommit in varchar2 default 'N', --是否提交 Ptsyztz in varchar2 default 'N', --是否属于 特殊频次(小时)的医技医嘱停止,Y是,N否 Psfydj0 in char default '0', --收费员登记 1是 0否 Pbz0000 varchar2 default '' --冲消备注 ) -- MODifICATION HisTORY -- Person Date Comments -- lintj 2013.12.18 加 SP_YJ_YJDJGL_SFQR00 -- lintj 2012.08.13 modify -- zhangwz 2012.10.23 解决冲销的时候医技子单先被独立冲销而没有update相关YJ_YJDJGL.SCX000='0'; -- lintj 2013.06.28 标本未采集的允许退费 -- qks 2013.09.09 按小时收费的医嘱,当原费用已结算过打印发票过,停止时自动多还少补，会出医嘱无法无法停止情况 -- -->现改为如果已结算过，只进行退费开单，不进行退费确认 -- qks 2013.09.25 按小时收费的医嘱,当原费用已结算过打印发票过,停止时自动多还少补-->现改为不进行退费开单,直接退出 -- qks 2014.01.24 一个子单对应多个主单情况,退某个主单时，对应的子单都要YJ_YJDJGL.SCX000='1',当所有的主单都退掉后,对应的子单才同时退掉; -- qks 2014.08.19 出院登记状态不允许开单; -- qks 2016.06.30 已执行项目退费申请提示已作废; by YJ9-20160630-001 -- zhengzk 2016.12.16 已经出科的病人要求可以退费确认，退费申请也请放开限制 by YJ9-20161207-001 -- liwm 2018.06.05 统一添加调用日志 for YJ9-20180605-001 -- qks 2018.06.07 最后抛错的异常信息需要截取; for SSMZ9-20180606-002 -- liwm 2018.06.08 错误变量不赋值了，不然其他系统过程调用可能会出错 for YJ9-20180608-001 -- qks 2018.08.22 修改游标YJ_TZDJH0定义：要存在于YJ_YW0000表中。for YJ9-20180821-001 -- cyhong 2019.04.25 住院检验中间表由HIS_REQUISITION 改为YJ_BBZJB0 for BQHS9.0-20190424-002 -- gzj 2019.05.28 住院病人诊疗带出处方流程改造 YJ9-20190518-002 -- liwm 2019.07.03 入参增加是否收费员 MZSF9-20190702-001 -- cyhong 2019.08.23 撤单时要求在医嘱前去掉"(取消)"字样 BQHS9.0-20190823-001 -- linshu 2020.09.01 金域LIS接口,签收过的不能退费(YJ_YW0000.JKZT00='5') by YJ9-20200831-001 -- linzetao 2020.10.19 增加参数控制JKZT00='5'时是否可以冲销 YJ9-20201019-001 -- chenHeyi 2021.08.11 参数YJ_CKBRKYBLDYJXM,出科病人可以补入的医技项目,输入诊疗项目ID,多个用逗号分隔,默认为-1 YJ9-20210721-001 -- jlg 2021.08.24 上面修改的有问题，先退回修改 YJ9-20210824-001 -- chenHeyi 2021.08.26 对原需求YJ9-20210721-001修正 YJ9-20210826-002 -- liyanni 2022.08.31 modify 退费申请时添加退费说明 for BQHSEX-20220826-004 as Vyw0000 YJ_YW0000%rowtype; Ecustom exception; --错误描述 Verrmsg varchar2(238); Vnewdjh number; Vcounter number; Vparams varchar2(200); --非药品医嘱是否收费 Vxmsfsf XT_XTCS00.VALUE0%type; --项目冲消时是否直接退费 Vsfzjtf XT_XTCS00.VALUE0%type; --结算过的项目发票未回收不允许进行项目冲消或退费申请 Vxmcxsq XT_XTCS00.VALUE0%type; --结算单号 Vjzdh00 ZY_BRFY00.JZDH00%type; --是否限制已结算费用门诊医生平台不能直接冲销,得先取消结算才行 YS_SFXZYJSFYBNZJCX XT_XTCS00.VALUE0%type; --病人费别(自费，公费，医保等) VFBBH00 BM_BRFBB0.FBBH00%type; --记账日期 V_JZRQ00 char(8); --医保限制冲销日期(默认为20010101) V_YBCXJZRQ XT_XTCS00.VALUE0%type; --医保卡号 V_YBKH00 BM_BRXXB0.YBKH00%type; --医保ID V_YBID00 BM_BRXXB0.YBID00%Type; Vzxcgbz number; Vtsxx00 varchar2(200); Vxxxx00 varchar2(200); --是否属于 已结算费用特殊频次(小时)的医技医嘱停止,Y是N否 Vjststz char(1); --接口状态已签收是否允许冲销 Vqssfcx XT_XTCS00.VALUE0%type; Vxxxlog varchar2(2000); --参数YJ_CKBRKYBLDYJXM,出科病人可以补入的医技项目,输入诊疗项目ID,多个用逗号分隔,默认为-1 VYJ_CKBRKYBLDYJXM XT_XTCS00.VALUE0%type; --同组的所有项目 cursor YJ_TZDJH0 is select YJDJH0 from YJ_YW0000 where YJDJH0=Pyjdjh0 union select ZYJDJH YJDJH0 from YJ_YJDJGL a where a.FYJDJH=Pyjdjh0 and a.SCX000='0' and not exists(select 1 from YJ_YJDJGL where ZYJDJH=a.ZYJDJH and SCX000='0' and FYJDJH<>a.FYJDJH) and not exists(select 1 from YJ_YW0000 where YJDJH0=a.ZYJDJH and XMZT00 in ('6','7')) and exists (select 1 from YJ_YW0000 where YJDJH0=a.ZYJDJH); begin Vxxxlog := substr('PYJDJH0:'||nvl(to_char(Pyjdjh0), 'null')||','||'PCZY000:'||nvl(to_char(Pczy000), 'null')||','||'PCZYKS0:'||nvl(to_char(Pczyks0), 'null')||','||'PSFZJTF:'||nvl(to_char(Psfzjtf), 'null')||','||'PSFCXSQ:'||nvl(to_char(Psfcxsq), 'null')||','||'PCXSL00:'||nvl(to_char(Pcxsl00), 'null')||','||'PBZ0000:'||nvl(to_char(Pbz0000), 'null')||','||'PCOMMIT:'||nvl(to_char(Pcommit), 'null')||','||'PTSYZTZ:'||nvl(to_char(Ptsyztz), 'null'),1,2000); SP_TransLog(sysdate,'SP_YJ_YWCX',Pczy000,Pczyks0,Vxxxlog); --Vparams:=substr(Vxxxlog, 1, 200); Vparams := ''; select nvl(max(trim(VALUE0)),'0') into Vxmsfsf from XT_XTCS00 where NAME00 = 'BQ_XMSFZJTF'; select nvl(max(trim(VALUE0)),'N') into Vsfzjtf from XT_XTCS00 where NAME00 = 'YJ_XMCXZJTF'; --YJ_XMCXSQ_JS select nvl(max(trim(VALUE0)),'N') into Vxmcxsq from XT_XTCS00 where NAME00 = 'BQ_JSFYSQTFFPHSCZ'; --接口状态已签收是否允许冲销, 0否1是, 默认0 select nvl(max(trim(VALUE0)),'0') into Vqssfcx from XT_XTCS00 where NAME00 = 'YJ_JKZTYQSSFYXCX'; if Pcxsl00 = 0 then Verrmsg:='冲销数量不能为0'; raise Ecustom; end if; for row in YJ_TZDJH0 loop select * into Vyw0000 from YJ_YW0000 where YJDJH0=row.YJDJH0; if Vyw0000.XMZT00='6' then Verrmsg:='该项目已经作废'; raise Ecustom; elsif Vyw0000.XMZT00='7' then Verrmsg:='该项目已经退费'; raise Ecustom; elsif Vyw0000.JKZT00='5' and Vqssfcx <> '1' then Verrmsg:='该项目报告已经签收，不允许退费!'; raise Ecustom; end if; if SF_YJ_JudgeYJDSX(Vyw0000.YJDJH0) = 1 then if Vyw0000.MZZYBZ='1' then select count(*) into Vcounter from YJ_BBZJB0 where YJDJH0=to_char(Vyw0000.YJDJH0); if Vcounter > 0 then select count(*) into Vcounter from YJ_BBZJB0 where YJDJH0=to_char(Vyw0000.YJDJH0) and STATE0 not in ('canceled','deleted','returned'); if Vcounter > 0 then Verrmsg := '该标本状态不允许退费'; raise Ecustom; end if; end if; else select count(*) into Vcounter from HIS_REQUISITION where HIS_ID=to_char(Vyw0000.YJDJH0); if Vcounter > 0 then select count(*) into Vcounter from HIS_REQUISITION where HIS_ID=to_char(Vyw0000.YJDJH0) and REQUISITION_STATE not in ('canceled','deleted','returned'); if Vcounter > 0 then Verrmsg := '该标本状态不允许退费'; raise Ecustom; end if; end if; end if; end if; if SF_YJ_GETTFSH(Vyw0000.YJDJH0,'1',Psfydj0)=0 then Verrmsg := '该标本状态不允许退费'; raise Ecustom; end if; if Vyw0000.MZZYBZ='1' then if(Vyw0000.SFDJH0 <> 0) then select nvl(max(trim(czrq00)),' ') into V_JZRQ00 from ZY_FYMX00 where DJH000 = Vyw0000.SFDJH0; end if; --医保限制冲销日期(默认为20010101) select nvl(max(trim(VALUE0)),'20010101') into V_YBCXJZRQ from XT_XTCS00 where NAME00='ZY_YBCXJZRQ'; --ZY_YBCXJZRQ值根据不同的YBZXLB获取不同的 YBCXJZRQ begin select SF_ZY_GETXTCS(C.YBZXLB,V_YBCXJZRQ) into V_YBCXJZRQ from ZY_BRXXB0 A,BM_BRXXB0 B,IC_YBBRLB C where A.ZYID00=Vyw0000.ZYGHID and A.BRID00=B.BRID00 and B.FBBH00=3 and B.FBBH00=C.FBBH00 and B.YBLB00=C.YBLB00; exception when others then V_YBCXJZRQ:='20010101'; end; --取病人费别 select FBBH00,YBKH00,YBID00 into VFBBH00, V_YBKH00, V_YBID00 from BM_BRXXB0 where BRID00= Vyw0000.BRID00; --除了新农合病人和异地医保病人，其他医保病人都要限制不能冲销某个时间段之前的费用 if (VFBBH00=3) and (V_YBCXJZRQ<>'20010101') and (V_JZRQ00<>'20991231') then if (V_YBCXJZRQ >= V_JZRQ00) and (V_YBKH00 <> V_YBID00) and (instr(V_YBKH00,'@') <= 0) then Verrmsg:='不能冲销'||substr(V_YBCXJZRQ,1,4)||'年'||substr(V_YBCXJZRQ,5,2)||'月'||substr(V_YBCXJZRQ,7,2)||'日之前的费用'; raise Ecustom; end if; end if; --对原需求YJ9-20210721-001修正 YJ9-20210826-002 select nvl(max(trim(VALUE0)),'-1') into VYJ_CKBRKYBLDYJXM from XT_XTCS00 where NAME00='YJ_CKBRKYBLDYJXM'; select count(*)into Vcounter from XT_XTCS00 where NAME00 = 'YJ_CKBRSFYXTF' and VALUE0 = 'Y'; if Vcounter = 0 then if VYJ_CKBRKYBLDYJXM<>'-1' then --如果设置了出科病人可以补入的医技项目 YJ9-20210826-002 select count(*)into Vcounter from ZY_BRXXB0 where ZYID00=Vyw0000.ZYGHID and SUBSTR(BRZT00,1,1) in ('3'); if Vcounter<>0 then --如果是出科病人，判断项目是否在[设置出科病人可以补入的医技项目] if instr(','||VYJ_CKBRKYBLDYJXM||',' , ','||to_char(Vyw0000.ZLXMID)||',')=0 then Verrmsg:=Vyw0000.ZLXMJC||' 不在参数[出科病人可以补入的医技项目]设置中，不能撤单!'; raise Ecustom; end if; else --非出科病人,按原来的 select count(*)into Vcounter from ZY_BRXXB0 where ZYID00=Vyw0000.ZYGHID and substr(BRZT00,1,1) in ('1','2'); if Vcounter=0 then Verrmsg:='非在院病人，不能撤单!'; raise Ecustom; end if; end if; else --没设置出科病人可以补入的医技项目，按原来的 YJ9-20210826-002 --出院登记状态不允许开单; --,'3' select count(*)into Vcounter from ZY_BRXXB0 where ZYID00=Vyw0000.ZYGHID and SUBSTR(BRZT00,1,1) in ('1','2'); if Vcounter=0 then Verrmsg:='非在院病人，不能撤单!'; raise Ecustom; end if; end if; else select count(*)into Vcounter from ZY_BRXXB0 where ZYID00=Vyw0000.ZYGHID and SUBSTR(BRZT00,1,1) in ('1','2','3'); if Vcounter=0 then Verrmsg:='非在院病人，不能撤单!'; raise Ecustom; end if; end if; if (Vxmcxsq='Y') then if Vyw0000.SFDJH0<>0 then select JZDH00 into Vjzdh00 from ZY_FYMX00 where DJH000=Vyw0000.SFDJH0 and rownum=1; if Vjzdh00<>0 then select COUNT(1) into Vcounter from ZY_JZB000 where JZDH00=Vjzdh00 and FPHSBZ='0'; if Vcounter>0 then if nvl(Ptsyztz,'N')<>'Y' then Verrmsg:='系统不允许退费或冲销,该费用已结算,而且发票未回收,请先把发票交还到收费处！*'; raise Ecustom; else --直接退出 return; end if; Vjststz:='Y'; end if; end if; end if; end if; end if; --住院 if nvl(Vyw0000.CXSL00,0)+nvl(Pcxsl00,Vyw0000.SL0000)>Vyw0000.SL0000 then Verrmsg:='本次冲消数量+已冲消数量不能超过原数量,请刷新后再试!'; raise Ecustom; end if; if Vyw0000.MZZYBZ='0' then select nvl(max(trim(VALUE0)),'N') into YS_SFXZYJSFYBNZJCX from XT_XTCS00 where NAME00='YS_SFXZYJSFYBNZJCX'; if YS_SFXZYJSFYBNZJCX='Y' then select count(*) into Vcounter from YJ_YW0000 A,SF_BRFY00 B where A.YJDJH0=row.YJDJH0 and A.MZZYBZ='0' and A.SFDJH0=B.DJH000 and B.JZDH00+0>0; if Vcounter>0 then Verrmsg:='要冲销的费用已经结算，不能直接冲销，请先收费处取消结算!'; raise Ecustom; end if; end if; end if; if Vyw0000.XMZT00='0' then --刚申请的，直接删除 delete from YJ_YW0000 where YJDJH0=Vyw0000.YJDJH0; delete from YJ_YWMX00 where YJDJH0=Vyw0000.YJDJH0; elsif Vyw0000.XMZT00 ='1' then--已经定价，删除YW，MX，JJ delete from YJ_YW0000 where YJDJH0=Vyw0000.YJDJH0; delete from YJ_YWMX00 where YJDJH0=Vyw0000.YJDJH0; delete from YJ_YWJJ00 where YJDJH0=Vyw0000.YJDJH0; elsif Vyw0000.XMZT00 in ('3','4','5') then --3:执行,4:报告5:疑退; if Vyw0000.XMZT00 in ('3','4') and Vyw0000.DCBZ00='2' and Vyw0000.YZCFID>'0' then BEGin--带出的诊疗项目 if Vyw0000.MZZYBZ='0' then select count(*) into Vcounter from YF_MZCF00 where CFLSH0=Vyw0000.YZCFID and SFKSLY='Y' and LYKS00=Vyw0000.YJKSBH; if Vcounter>0 then select count(*) into Vcounter from YF_MZCFMX where CFLSH0=Vyw0000.YZCFID and CXBZ00='Z'; if Vcounter>0 then SP_YJ_JSCFTF(Vyw0000.YZCFID,'0',Vyw0000.YJKSBH,Pczy000); end if; end if; else select count(*) into Vcounter from YF_ZYCF00 where CFLSH0=Vyw0000.YZCFID and SFKSLY='Y' and LYKS00=Vyw0000.YJKSBH; if Vcounter>0 then select count(*) into Vcounter from YF_ZYCFMX where CFLSH0=Vyw0000.YZCFID and CXBZ00='Z'; if Vcounter>0 then SP_YJ_JSCFTF(Vyw0000.YZCFID,'1',Vyw0000.YJKSBH,Pczy000); end if; end if; end if; exception when others then Vcounter:=Vcounter; end; end if; if ((Vxmsfsf='1' and Vsfzjtf='Y') or (Psfzjtf='Y')) and nvl(Vjststz,'N')<>'Y' then Vcounter:=SF_YJ_YWCX_CXDJH(row.YJDJH0,1,Verrmsg,Pczy000,Pczyks0,Pcxsl00,Pbz0000); else Vcounter:=SF_YJ_YWCX_CXDJH(row.YJDJH0,0,Verrmsg,Pczy000,Pczyks0,Pcxsl00,Pbz0000); end if; if Vcounter=0 then raise Ecustom; end if; elsif Vyw0000.XMZT00 ='2' then --2:已经收费 --if Vyw0000.ZXSL00>0 then --已经执行若干次 if Vyw0000.ZXSL00=Vyw0000.SL0000 then--二者相等，就是已经执行完毕 update YJ_YW0000 set XMZT00='3' where YJDJH0=row.YJDJH0; if ((Vxmsfsf='1' and Vsfzjtf='Y') or (Psfzjtf='Y')) and nvl(Vjststz,'N')<>'Y' then Vcounter:=SF_YJ_YWCX_CXDJH(row.YJDJH0,1,Verrmsg,Pczy000,Pczyks0,Pcxsl00,Pbz0000); else Vcounter:=SF_YJ_YWCX_CXDJH(row.YJDJH0,0,Verrmsg,Pczy000,Pczyks0,Pcxsl00,Pbz0000); end if; if Vcounter=0 then raise Ecustom; end if; else--没全部执行完 if ((Vxmsfsf='1' and Vsfzjtf='Y') or (Psfzjtf='Y')) and nvl(Vjststz,'N')<>'Y' then Vcounter:=SF_YJ_YWCX_CXDJH(row.YJDJH0,1,Verrmsg,Pczy000,Pczyks0,Pcxsl00,Pbz0000); else Vcounter:=SF_YJ_YWCX_CXDJH(row.YJDJH0,0,Verrmsg,Pczy000,Pczyks0,Pcxsl00,Pbz0000); end if; if Vcounter=0 then raise Ecustom; end if; --重新申请已经执行的次数 if Vyw0000.ZXSL00>0 then select count(*) into Vcounter from xt_xtcs00 where name00='YJ_YWCX_CXSQYZXXM' and value0='N'; if (Vcounter=0) or (Psfcxsq='Y') then Vcounter:=SF_YJ_YWCX_SQ(row.YJDJH0,0,Verrmsg,Pczy000,Pczyks0,Vnewdjh); if Vcounter=0 then raise Ecustom; end if; --20030918冲销产生的项目不能自动收费 Vcounter:=SF_YJ_YWCX_SF(row.YJDJH0,Vnewdjh,0,Verrmsg,Pczy000,Pczyks0); if Vcounter=0 then raise Ecustom; end if; end if; end if; end if; end if ; --修改冲消数量 拿掉,在嵌套的过程 SF_YJ_YWCX_CXDJH 里面已经处理过了 --update YJ_YW0000 a set CXSL00=nvl(CXSL00,0) + nvl(Pcxsl00,Vyw0000.SL0000) where YJDJH0=Vyw0000.yjdjh0; --撤单时要求在医嘱前增加"(取消)"字样 --if Vyw0000.mzzybz='1' and Vyw0000.dcbz00='0' and Vyw0000.yzcfid is not null and (Vyw0000.fjdjbz = '0') then --update bq_yjyz00 set zlxmjc='(取消)'||trim(zlxmjc) --where yzmxid=to_number(Vyw0000.yzcfid) and clbz00='1' and instr(zlxmjc,'(取消)')=0; --end if; --update YJ_YJDJGL set SCX000='1' where FYJDJH=Pyjdjh0 and ZYJDJH=Vyw0000.YJDJH0; update YJ_YJDJGL set SCX000='1' where FYJDJH=Pyjdjh0 and SCX000='0'; --qks 2014.01.24 update YJ_YJDJGL set SCX000='1' where ZYJDJH=Vyw0000.YJDJH0 and SCX000='0'; --zhangwz 12.10.23 SP_XK_XDCX00(row.YJDJH0); end loop; SP_YJ_YJDJGL_SFQR00(1, Pyjdjh0); if pcommit='Y' then commit; end if; exception when ecustom then raise_application_error(-20011, substrb(Verrmsg||'！*'||Vparams,1,100)); when no_data_found then raise_application_error(-20010, substrb('数据没有找到！*'||Verrmsg,1,100)); when others then raise_application_error(-20020, substrb(nvl(sqlerrm, '原因不明出错！*'||Verrmsg),1,100)); rollback; end SP_YJ_YWCX; / --%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%