CREATE OR REPLACE PROCEDURE SD_HOSPITAL.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否 ) -- 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 -- jlg 2023.05.17 增加门诊住院费别分开处理 YJ9-20230516-003 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); --记账日期 V_YBCXJZRQ XT_XTCS00.VALUE0%type; --医保限制冲销日期(默认为20010101) V_YBKH00 BM_BRXXB0.YBKH00%type; --医保卡号 V_YBID00 BM_BRXXB0.YBID00%type; --医保ID Vzxcgbz number; -- Vtsxx00 varchar2(200); -- Vxxxx00 varchar2(200); -- Vjststz char(1); --是否属于 已结算费用特殊频次(小时)的医技医嘱停止,Y是N否 Vqssfcx XT_XTCS00.VALUE0%type; --接口状态已签收是否允许冲销 Vxxxlog varchar2(2000); -- VYJ_CKBRKYBLDYJXM XT_XTCS00.VALUE0%type; --参数YJ_CKBRKYBLDYJXM,出科病人可以补入的医技项目,输入诊疗项目ID,多个用逗号分隔,默认为-1 --同组的所有项目 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')||','||'PCOMMIT:'||nvl(to_char(PCOMMIT), 'null')||','||'PTSYZTZ:'||nvl(to_char(PTSYZTZ), 'null'),1,2000); SP_TransLog(sysdate,'SP_YJ_YWCX',Pczy000,Pczyks0,Vxxxlog); Vparams := ''; --非药品医嘱是否直接退费？0-否,1-是 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'; --已发送医保清单是否允许进行院内退费处理，Y不允许，N允许。默认为N 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 nvl(Vyw0000.CXSL00,0)+nvl(Pcxsl00,Vyw0000.SL0000)>Vyw0000.SL0000 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 ybzxlb from ic_ybbrlb where FBBH00=3 and YBZXLB=YBLB00)控制,参数格式 ic_ybbrlb.ybzxlb1^日期1|ic_ybbrlb.ybzxlb2^日期2|默认日期 具体格式如下:1^20100101|2^20100201|Z^20100301|}^20100401|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,IC_YBBRLB c where a.FBBH00=c.FBBH00 and a.YBLB00=c.YBLB00 and a.ZYID00=Vyw0000.ZYGHID and a.FBBH00=3; exception when others then V_YBCXJZRQ:='20010101'; end; begin select FBBH00,YBKH00,YBID00 into VFBBH00, V_YBKH00, V_YBID00 from ZY_BRXXB0 where ZYID00= Vyw0000.ZYGHID; --优先从ZY_BRXXB0获取费别信息 exception when others then select FBBH00,YBKH00,YBID00 into VFBBH00, V_YBKH00, V_YBID00 from BM_BRXXB0 where BRID00= Vyw0000.BRID00; --上面取值异常，则改从BM_BRXXB0获取病人费别 end; --除了新农合病人和异地医保病人，其他医保病人都要限制不能冲销某个时间段之前的费用 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 出科病人可以补入的医技项目,输入诊疗项目ID,多个用逗号分隔,默认为-1 select nvl(max(trim(VALUE0)),'-1') into VYJ_CKBRKYBLDYJXM from XT_XTCS00 where NAME00='YJ_CKBRKYBLDYJXM'; --项目执行时,出科病人是否允许退费申请和退费确认 Y是 N否,默认为N 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 --出院登记状态不允许开单; 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; 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; --已发送医保清单是否允许进行院内退费处理，Y不允许，N允许。默认为N 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 Vyw0000.MZZYBZ='0' then --是否限制已结算费用门诊医生平台不能直接冲销,得先取消结算才行. Y 是 N 否 默认值为N 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); else Vcounter:=SF_YJ_YWCX_CXDJH(row.YJDJH0,0,Verrmsg,Pczy000,Pczyks0,Pcxsl00); end if; if Vcounter=0 then raise Ecustom; end if; elsif Vyw0000.XMZT00 ='2' then --2:已经收费 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); else Vcounter:=SF_YJ_YWCX_CXDJH(row.YJDJH0,0,Verrmsg,Pczy000,Pczyks0,Pcxsl00); 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); else Vcounter:=SF_YJ_YWCX_CXDJH(row.YJDJH0,0,Verrmsg,Pczy000,Pczyks0,Pcxsl00); end if; if Vcounter=0 then raise Ecustom; end if; --重新申请已经执行的次数 if Vyw0000.ZXSL00>0 then --诊疗单冲销自动重新申请已执行部分M为前台指定冲消数量 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 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; --YJ_YJDJGL表的确认标志更新 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;