create or replace function SF_JY_HQQFXX ( PBRID00 in number , --病人ID PTXMQZ0 in varchar2 , --条形码,可以传多个 PYJDJH0 in varchar2 , --医技单,可以传多个 PCFLSH0 in varchar2 , --处方号,可以传多个 PCXFSL0 in number , --抽血费数量 PJYF000 in number , --煎药费　 PJYFCS0 in number --煎药费数量　 ) 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_YBZHQF char(1); 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); V_SQL000 varchar2(2000); V_TXMQZ0 varchar2(1000); V_RESULT varchar2(100); --返回总金额、自付金额｜病人余额｜欠费 ls_YDYBSFYYKT char(1); --异地医保是否使用一卡通 v_YLJE number(3); -- MODifICATION HisTorY -- Person date Comments -- chenyw 2011.09.16 检验取号项目是否欠费（都预留10元，用来扣采血费） -- chenyw 2011.12.13 长乐要求不预留10元 by Lis-20111130-001 -- wusd 2020.07.31 增加V_SQL000长度 by JYPDJH-20200730-001 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 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'; select substrb(nvl(max(trim(VALUE0)),'N'),1,1) into ls_YDYBSFYYKT from XT_XTCS00 where name00='SF_YDYBSFYYKT'; --异地卡是否使用一卡通 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(PTXMQZ0)) is not null 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; --煎药费 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; select nvl(SUM(ZYE000),0),nvl(SUM(YXQFJE),0),nvl(SUM(TYJE00),0),nvl(SUM(YBZHYE),0) into ls_ZYE000,ls_YXQFJE,ls_TYJE00,ls_YBZHYE 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' or (V_YBZXLB=2 and ls_YDYBSFYYKT='Y') then --南平医保异地卡不能透支医保余额 ls_YBZHYE:=0; end if; else ls_YBZHYE:=0; end if; --V_BRYE00:=ls_ZYE000+ls_YXQFJE-ls_TYJE00+ls_YBZHYE; ---取消“允许欠费金额”计算 V_BRYE00:=ls_ZYE000-ls_TYJE00+ls_YBZHYE; if (V_FBBH00='3') and (V_YBZXLB='Z') then V_YLJE:=0; else V_YLJE:=0; end if; if (V_BRYE00 - V_LJZFJE-V_YLJE + V_LSJZJE) < 0 then -- zhr 2009.06.05 余额不够时，提示补交的金额不能包含允许欠费金额; V_RESULT := trim(to_char(V_LJZJE0,'9999999990.99'))||' 自付:'||trim(to_char(V_LJZFJE,'9999999990.99'))||'|'|| trim(to_char(V_BRYE00-ls_YXQFJE,'9999999990.99'))||'|'||trim(to_char((V_LJZFJE - V_BRYE00 - V_LSJZJE+V_YLJE+ls_YXQFJE),'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'; end if; return V_RESULT; end;