create or replace function SF_SF_HQQFXX ( PBRID00 in number, --病人ID PTXMQZ0 in varchar2, --条形码,可以传多个 PYJDJH0 in varchar2, --医技单,可以传多个 PCFLSH0 in varchar2, --处方号,可以传多个 PCXFSL0 in number, --抽血费数量 PJYF000 in number, --煎药费　 PJYFCS0 in number --煎药费数量　 ) -- MODIFICATION HISTORY -- Person Date Comments -- zhr 2009.04.20 created by zhr ; -- Jxq 2009.10.15 修正,要将相同YJFZH0金额全部统计在内 -- zhr 2010.04.22 余额不够时，提示补交的金额不能包含允许欠费金额;增加担保金额 -- daihq 2016.11.09 条码管V_SQL000的判断增加PYJDJH0为空的处理 MZSF9-20161114-001 -- daihq 2017.03.15 增加判断诊疗带诊疗采血费的判断 LIS-20170213-005 -- daihq 2017.04.13 诊疗带出诊疗采血费与试管关系，增加没有数据的处理，sum(zje000)改为nvl(sum(zje000),0) for LIS9-20170413-001 -- jlg 2021.04.18 将 XMZT00 NOT in (''2'',''3'')改为 nvl(XMZT00,''0'') NOT in (''2'',''3'') ，可以提高查询速度；同时重整本脚本 MZSF9-20210418-001 -- wusd 2021.08.17 扩增V_SQL000 for LIS9-20210806-002 -- linzetao 2022.11.18 余额不足时返回信息增加允许欠费和担保金额 MZSF9-20221117-001 return varchar2 is V_COUNT0 number; V_FBBH00 BM_YBBRLB.FBBH00%type; V_YBBRLB BM_YBBRLB.YBBRLB%type; V_YBZXLB BM_YBBRLB.YBZXLB%type; V_ZLXMID varchar2(100); V_CXF000 BM_YYSFXM.SFJE00%type; --抽血费 V_TMJE00 BM_YYSFXM.SFJE00%type; --条码金额 V_ZFJE00 BM_YYSFXM.SFJE00%type; --自付金额 V_ZJE000 SF_BRZHXX.XJYE00%type; --项目总金额 V_LJZFJE BM_YYSFXM.SFJE00%type; --累计自付金额 V_LJZJE0 SF_BRZHXX.XJYE00%type; --累计项目总金额 V_LSJZJE SF_BRZHXX.XJYE00%type; --历史未结算费用记账金额 V_BRYE00 SF_BRZHXX.XJYE00%type; --病人余额 ls_LXQFJZ char(1); ls_YBZHQF char(1); ls_TABLE0 varchar2(300); ls_TABLE1 varchar2(300); ls_DECODE varchar2(60); ls_YBZHYE number(14,4); ls_ZYE000 number(14,4); ls_YXQFJE number(14,4); ls_TYJE00 number(14,4); ls_DBJE00 number(14,4); V_SQL000 varchar2(5000); V_TXMQZ0 varchar2(1000); v_result varchar2(100); --返回总金额、自付金额｜病人余额｜欠费 begin V_CXF000 := 0; V_TMJE00 := 0; V_ZFJE00 := 0; V_ZJE000 := 0; V_BRYE00 := 0; V_LJZFJE := 0; V_LJZJE0 := 0; select to_char(a.FBBH00),trim(B.YBZXLB),trim(A.YBBRLB) into V_FBBH00,V_YBZXLB,V_YBBRLB from BM_BRXXB0 a,IC_YBBRLB b where a.BRID00=PBRID00 and a.FBBH00=b.FBBH00 and trim(a.YBLB00)=trim(b.YBLB00); --医保离休病人非自费部分费用允许欠费 select substrb(nvl(max(trim(VALUE0)),'N'),1,1) into ls_LXQFJZ from XT_XTCS00 where name00='SF_YBLX_FZFFYYXQF'; if (ls_LXQFJZ in ('Y','M') and SF_SF_LXBR00(PBRID00)='1') or (V_FBBH00 NOT in ('1','3')) then ls_TABLE0:=',VW_BM_YBSFDY v where a.SFXMID=v.SFXMID and v.FBBH00='||V_FBBH00||' and v.YBZXLB='''||V_YBZXLB||''' and v.YBBRLB='''||V_YBBRLB||''' and SFYP00=''N'' and'; ls_TABLE1:=',VW_BM_YBSFDY v where a.YPNM00=v.SFXMID and v.FBBH00='||V_FBBH00||' and v.YBZXLB='''||V_YBZXLB||''' and v.YBBRLB='''||V_YBBRLB||''' and SFYP00=''Y'' and'; if (ls_LXQFJZ='Y') or (V_FBBH00 not in ('1','3') and ls_LXQFJZ='N') then ls_DECODE:='v.ZFBL00'; elsif ls_LXQFJZ='M' then ls_DECODE:='decode(v.ZFBL00,1,1,0)'; end if; --医保离休病人病人未结账的历史费用记账金额 if V_FBBH00='3' then select nvl(sum(a.HJJE00*(1-decode(ls_LXQFJZ,'Y',a.ZFBL00,'M',decode(a.ZFBL00,1,1,0)))),0) into V_LSJZJE from SF_FYMX00 a,SF_BRFY00 b where a.DJH000=b.DJH000 and b.BRID00=PBRID00 and b.JZDH00=0; end if; else ls_TABLE0:='where'; ls_TABLE1:='where'; ls_DECODE:='1'; V_LSJZJE :=0; end if; --医技单 if rtrim(ltrim(PYJDJH0)) is not null then V_SQL000 :='select nvl(sum(a.ZFJE00*'||ls_DECODE||'),0) from YJ_YWJJ00 a '||ls_TABLE0||' YJDJH0 in ('||PYJDJH0||') '|| ' and exists(select 1 from YJ_YW0000 where YJDJH0=a.YJDJH0 and XMZT00 not in (''2'',''3''))'; execute immediate V_SQL000 into V_ZFJE00; V_SQL000 :='select nvl(sum(nvl(a.JJJE00,0)*SFCS00*SL0000),0) from YJ_YWJJ00 a where YJDJH0 in ('||PYJDJH0||')'; execute immediate V_SQL000 into V_ZJE000; V_LJZFJE := V_LJZFJE+V_ZFJE00; V_LJZJE0 := V_LJZJE0+V_ZJE000; end if; --处方 if rtrim(ltrim(PCFLSH0)) is not null then V_SQL000 :='select nvl(sum(a.ZFJE00*'||ls_DECODE||'),0) from YF_MZCFMX a '||ls_TABLE1||' CFLSH0 in ('''||PCFLSH0||''') '|| ' and SFZBY0=''N'' and exists (select 1 from YF_MZCF00 where CFLSH0=a.CFLSH0 and CFZT00 in (''0'',''4''))'; execute immediate V_SQL000 into V_ZFJE00; V_SQL000 :='select nvl(sum(a.ZFJE00),0) from YF_MZCFMX a where CFLSH0 in ('''||PCFLSH0||''') and SFZBY0=''N'''|| ' and exists (select 1 from YF_MZCF00 where CFLSH0=a.CFLSH0 and CFZT00 in (''0'',''4''))'; execute immediate V_SQL000 into V_ZJE000; V_LJZFJE := V_LJZFJE+V_ZFJE00; V_LJZJE0 := V_LJZJE0+V_ZJE000; end if; --采血管项目 select nvl(max(trim(VALUE0)),'0') into V_ZLXMID from XT_XTCS00 where NAME00 = 'JY_XGCXXM'; if V_ZLXMID <> '0' and PCXFSL0>0 then V_SQL000 :='select nvl(sum(SFJE00*'||to_char(nvl(PCXFSL0,0))||'*'||ls_DECODE||'),0) from BM_YYSFXM a '||ls_TABLE0|| ' a.SFXMID in (select SFXMID from XT_ZLSFGX where ZLXMID in ('||V_ZLXMID||'))'; execute immediate V_SQL000 into V_ZFJE00; V_SQL000 :='select nvl(sum(SFJE00)*'||to_char(nvl(PCXFSL0,0))||',0) from BM_YYSFXM a '|| 'where a.SFXMID in (select SFXMID from XT_ZLSFGX where ZLXMID in ('||V_ZLXMID||'))'; execute immediate V_SQL000 into V_ZJE000; V_CXF000 := V_ZJE000; V_LJZFJE := V_LJZFJE+V_ZFJE00; V_LJZJE0 := V_LJZJE0+V_ZJE000; end if; --条码管 if rtrim(ltrim(PYJDJH0)) is not null then V_SQL000 := 'select count(*) from JY_YW0000 where YJDJH0 in ('||PYJDJH0||')'; execute immediate V_SQL000 into V_COUNT0; end if; if (rtrim(ltrim(PTXMQZ0)) is not null) and (V_COUNT0 = 0) then select ''''||REPLACE(PTXMQZ0,'|',''',''')||'''' into V_TXMQZ0 from DUAL; V_SQL000 :='select nvl(sum(SFJE00*'||ls_DECODE||'),0) from BM_YYSFXM a '||ls_TABLE0||' a.SFXMID in (select SFXMID from JY_TMSFDY where TXMQZ0 in ('||V_TXMQZ0||'))'; execute immediate V_SQL000 into V_ZFJE00; V_SQL000 :='select nvl(sum(SFJE00),0) from BM_YYSFXM a where a.SFXMID in (select SFXMID from JY_TMSFDY where TXMQZ0 in ('||V_TXMQZ0||'))'; execute immediate V_SQL000 into V_ZJE000; V_TMJE00 := V_ZJE000; V_LJZFJE := V_LJZFJE+V_ZFJE00; V_LJZJE0 := V_LJZJE0+V_ZJE000; end if; --2017.03.15 daihq add for LIS-20170213-005 --查找诊疗带出诊疗采血费与试管关系 if length(trim(PYJDJH0))<>0 then V_SQL000:='select nvl(sum(zje000),0) from yj_yw0000 where yjdjh0 in (select ZYJDJH from yj_yjdjgl where Fyjdjh in ('||PYJDJH0||') and scx000=''0'' ) and xmzt00=''1'' and cxdjh0 is null and fjdjbz in (''1'',''2'') '; execute immediate V_SQL000 into V_ZJE000; V_CXF000 := V_ZJE000; V_LJZFJE := V_LJZFJE+V_ZJE000; V_LJZJE0 := V_LJZJE0+V_ZJE000; end if; --煎药费 if nvl(PJYF000,0)>0 then V_SQL000 :='select nvl(sum(SFJE00),0) from BM_YYSFXM a where a.SFXMID=to_char('||PJYF000||')'; execute immediate V_SQL000 into V_ZFJE00; V_SQL000 :='select nvl(sum(SFJE00)*'||ls_DECODE||',0) from BM_YYSFXM a '||ls_TABLE0||' a.SFXMID=to_char('||PJYF000||')'; execute immediate V_SQL000 into V_ZJE000; V_LJZFJE := V_LJZFJE+V_ZFJE00; V_LJZJE0 := V_LJZJE0+V_ZJE000; end if; --通过Update ZY_BRDB00,使用该表的Trigger,来修改该病人的允许欠费金额 -- Update SF_BRDB00 set SFYX00='N' where BRID00=PBRID00 and JFLBID='1' and SFYX00='Y' -- and nvl(DBJSRQ,to_char(SYSDATE,'YYYYMMDD'))||nvl(DBJSSJ,to_char(sysdate,'HH24:MI:SS'))< -- to_char(SYSDATE,'YYYYMMDD')||to_char(sysdate,'HH24:MI:SS'); --相同YJFZH0的情况 if rtrim(ltrim(PYJDJH0)) is not null then V_SQL000 :='select nvl(sum(ZFJE00),0) from YJ_YWJJ00 A where YJDJH0 in (select YJDJH0 from YJ_YW0000 where 1=1 ' || 'and ZYGHID in (select DISTINCT ZYGHID from YJ_YW0000 where YJDJH0 in ('||PYJDJH0||')) '|| 'and YJFZH0 in (select DISTINCT YJFZH0 from YJ_YW0000 where YJDJH0 in ('||PYJDJH0||')) '|| --'and EXISTS(select 1 from YJ_YW0000 where YJDJH0=A.YJDJH0 and XMZT00 not in (''2'',''3'')) '|| --'and XMZT00 not in (''2'',''3'') ' || --将 XMZT00 not in (''2'',''3'')改为 nvl(XMZT00,''0'') not in (''2'',''3'') 可以提高查询速度 'and nvl(XMZT00,''0'') not in (''2'',''3'') ' || 'and YJDJH0 not in ('||PYJDJH0||'))'; execute immediate V_SQL000 into V_ZFJE00; V_SQL000 :='select nvl(sum(nvl(JJJE00,0)*SFCS00*SL0000),0) from YJ_YWJJ00 A where YJDJH0 in (select YJDJH0 from YJ_YW0000 where 1=1 ' || 'and ZYGHID in (select DISTINCT ZYGHID from YJ_YW0000 where YJDJH0 in ('||PYJDJH0||')) '|| 'and YJFZH0 in (select DISTINCT YJFZH0 from YJ_YW0000 where YJDJH0 in ('||PYJDJH0||')) '|| 'and YJDJH0 not in ('||PYJDJH0||'))'; execute immediate V_SQL000 into V_ZJE000; V_LJZFJE := V_LJZFJE + V_ZFJE00; V_LJZJE0 := V_LJZJE0 + V_ZJE000; end if; select nvl(sum(ZYE000),0),nvl(sum(YXQFJE),0),nvl(sum(TYJE00),0),nvl(sum(YBZHYE),0),nvl(sum(DBJE00),0) into ls_ZYE000,ls_YXQFJE,ls_TYJE00,ls_YBZHYE,ls_DBJE00 from SF_BRZHXX where BRID00=PBRID00 and JFLBID=1; if ls_YBZHYE>0 and V_FBBH00='3' then --医保病人的个人账户额作为允许欠费金额 --医保病人的个人账户余额是否作为允许欠费金额 select substrb(nvl(max(trim(VALUE0)),'N'),1,1) into ls_YBZHQF from XT_XTCS00 where name00='SF_YBGRZHZWYXQFJE'; if ls_YBZHQF='N' then ls_YBZHYE:=0; end if; else ls_YBZHYE:=0; end if; V_BRYE00:=ls_ZYE000+ls_YXQFJE-ls_TYJE00+ls_YBZHYE+ls_DBJE00; if (V_BRYE00 - V_LJZFJE + V_LSJZJE) < 0 then -- zhr 2010.04.22 余额不够时，提示补交的金额不能包含允许欠费金额; v_result := trim(to_char(V_LJZJE0,'9999999990.99'))||' 自付:'||trim(to_char(V_LJZFJE,'9999999990.99')) ||'|'||trim(to_char(V_BRYE00-ls_YXQFJE-ls_DBJE00,'9999999990.99')) ||'|'||trim(to_char((V_LJZFJE - V_BRYE00 - V_LSJZJE+ls_YXQFJE+ls_DBJE00),'9999999990.99')) /*-- 2022.11.18 返回信息增加允许欠费和担保金额 ||'|'||trim(to_char(ls_YXQFJE,'9999999990.99')) ||'|'||trim(to_char(ls_DBJE00,'9999999990.99'))*/ ; else v_result := trim(to_char(V_LJZJE0,'9999999990.99'))||' 自付:'||trim(to_char(V_LJZFJE,'9999999990.99'))||'|'|| trim(to_char(V_BRYE00,'9999999990.99'))||'|0' -- 2022.11.29 返回信息增加 病人实际可用金额（即扣除允许欠费和担保金额） ，同判断 <0 ||'|'||trim(to_char(V_BRYE00-ls_YXQFJE-ls_DBJE00,'9999999990.99')) ||'|'||trim(to_char((V_LJZFJE - V_BRYE00 - V_LSJZJE+ls_YXQFJE+ls_DBJE00),'9999999990.99')) ; end if; return v_result; end;