create or replace procedure SP_ZY_BRFY00_JS0000 ( ad_ZYID00 in number, --病人住院信息表ID ad_JFLBID in number, --病人交费类别ID as_JFDJHS in char, --本次结算所使用的预交金的对应的交费单据号 as_JMYY00 in varchar2, --减免原因 as_CZBZ00 in char, --结算标志：0:中途结算 1:出院结算 2:呆账结算 3:年度结算 as_JSZZRQ in char, --中途结算截止日期 as_TSBZBH in char, --结算的特殊病种编号 ad_YEXM00 in char default '全部', --婴儿姓名 ad_DBZBM0 in char default '', --单病种编码 ad_JZDH00 out number, --病人结账单号 ad_XJYE00 out number, --病人账户上的现金余额(有可能为负数，为负则表示应补交现金金额jlg) ad_ZZYE00 out number, --病人账户上的转账余额(不能为负数jlg) ad_XJDJH0 out NUMBER, --病人现金的退款单据号 ad_ZZDJH0 out NUMBER, --病人转账的退款单据号 ad_XJTKPJ out number, --病人现金退款票据号 ad_ZZTKPJ out number, --病人转账退款票据号 as_XJTKXH out varchar2, --病人现金退款票据序号 as_ZZTKXH out varchar2, --病人转账退款票据序号 Pczy000 in number default -1 , --输入：操作员编码 Pczyks0 in number default -1 , --输入：操作员科室 Pcommit in varchar2 default 'N', --是否提交 Pcslx00 in varchar2 default '1', --农合传送类型 0传明细 1实时结算 默认1 Pzxcgbz out number, --执行成功标志 1成功 0失败 2存在项目总额为负 3余病人额不足4医保支付金额大于病人费用 Ptsxx00 out varchar2, --执行结果提示信息，成功的情况下也可能有警告信息 Pxxxx00 out varchar2 --执行结果详细信息，成功的情况下也可能有警告信息，一般用来显示参数等指标 ) -- MODIFICATION HISTORY -- Person Date Comments -- lintj 2013.02.07 modify 去掉不必要的参数 -- jlg 2013.02.21 增加插入ZY_PJSYQK.HJJE00,ZFJE00,JZJE00,JMJE00,YHJE00 -- jlg 2013.02.21 增加插入ZY_JZB000.FBBH00 -- jlg 2013.03.29 增加同步修改ZY_JZB000.YBZXLB,YBLB00,YBBRLB -- jlg 2013.03.29 条件ls_BRID00 --> ad_ZYID00 -- jlg 2013.04.15 增加插入ZY_PJSYQK.ZYID00 -- jlg 2013.04.15 修改数据写入规则,结算退款的票据号不用结算对应的票据号 -- jlg 2013.04.22 增加处理关于退款返回参数ad_XJTKPJ,ad_ZZTKPJ -- jlg 2013.04.24 医保病人结算退款票据号问题的处理 -- jlg 2013.04.24 结算后退现金转账及收现金转账的处理 -- jlg 2013.04.25 增加处理关于退款返回参数票据序号as_XJTKXH,as_ZZTKXH -- jlg 2013.06.18 增加传入参数 农合结算传送类型Pcslx00 -- jlg 2013.07.02 修改关于中途已结算费用的处理 -- jlg 2013.07.06 重新定义Pzxcgbz返回值的意义 -- qks 2013.09.22 sp_zy_jzmxxm_sjzl00 增加入参ad_JZDH00 -- qks 2013.10.16 省人民:出院结算退"转账"预交金使用"现金"方式退出 -- qks 2013.10.17 公费病人审批,调用过程SP_ZY_BRFY00_EDIT00 -- qks 2013.11.20 省人民:市保健病人发票打印拆分为自费和记账2张 -- jlg 2016.03.16 政和妇幼医院 处理单病种医保返回总金额与分摊金额不一致的问题 (医院支付金额) ZYSF9-20160312-001 -- jlg 2016.08.18 增加ZY_JZB000.YBYL14-->YBYL32字段的写入 ZYSF9-20160816-001 -- jlg 2016.10.09 增加医保病人按自费结算的处理(不发送医保) ZYSF9-20160922-001 -- jlg 2016.10.18 修改单病种病人应补交金额计算有误的问题 ZYSF9-20161014-001 -- qks 2016.10.25 政和县医院：新增参数ZY_DBZJSZFJESF,控制单病种结算自付金额算法; by ZYSF9-20161024-003 -- qks 2016.11.07 政和县医院：收员缴款单上“发票自付”金额,跟按明细费别结算上的“个人自付金额”合计金额不等问题; by ZYSF9-20161107-001 -- qks 2016.11.17 政和县医院：单病种病人结算,欠费情况下也会结算成功并打印发票问题; by ZYSF9-20161121-002 -- qks 2016.12.04 参数ZY_YJJPJ0值为N时,写入ZY_BRJFB0.PLBH00值是错误; for ZYSF9-20161205-002 -- jlg 2017.03.14 增加ZY_JZB000.FFBZBM字段的写入处理,同时YBYL04写入病种标准收费金额 ZYSF9-20170313-002 -- jlg 2017.03.14 增加超标床位费及除外耗材费用 写入YBYL33,YBYL34 ZYSF9-20170314-001 -- jlg 2017.03.16 付费单病种处理：根据医保返回的结果进行结算相关金额的处理 ZYSF9-20170316-001 -- jlg 2017.03.23 付费单病种处理：增加自费病人单病种结算的处理 ZYSF9-20170320-002 -- jlg 2017.03.27 付费单病种处理：结算后BZWFY0字段值清零 ZYSF9-20170313-001 -- jlg 2017.05.25 付费单病种处理: 如果GRZFE0>HJJE00，则多余部分移除到YBYL05字段 ZYSF9-20170524-001 -- jlg 2017.06.12 处理婴儿费用单独结算的问题 ZYSF9-20170301-002 -- jlg 2017.07.28 结算后修改病人结算核对状态JSHDZT为未核对的处理 ZYSF9-20170621-002 -- jlg 2017.08.17 修改自费病人单病种结算医院支付金额有误的问题 ZYSF9-20170815-002 -- jlg 2017.09.13 增加对之前逃跑欠费出院的提示限制处理 ZYSF9-20170912-001 -- jlg 2018.01.04 增加C-DRG结算流程的数据处理 ZYSF9-20180104-001 -- jlg 2018.01.08 增加C-DRG结算时医保返回报销金额与院内费用总额不一致的处理 ZYSF9-20180105-002 -- jlg 2018.01.16 付费单病种处理：对于医保返回病人实际费用的金额时医院支付部分的计算处理 ZYSF9-20180116-002 -- jlg 2018.02.07 增加省属付费单病种医保病人按自费结算的限制(省属单病种医保病人不能按不发送医保来处理) ZYSF9-20180207-002 as ls_XM0000 ZY_BRXXB0.XM0000%type; --病人姓名 ls_XB0000 ZY_BRXXB0.XB0000%type; --病人性别 ls_BRID00 ZY_BRXXB0.BRID00%type; --病人性别 ls_JFDJHS varchar2(2000); ls_TMPDJH varchar2(20); ls_PJH000 ZY_PJSYQK.PJH000%type; --票据流水号 ls_PJH001 ZY_PJSYQK.PJH000%type; --票据流水号 ls_FZPJH0 ZY_PJSYQK.PJH000%type; ls_SFXMID BM_YYSFXM.SFXMID%type; --收费项目ID ls_CYQKJE ZY_BRZHXX.ZYE000%type; --病人账户总余额 ls_XJJFJE ZY_BRJFB0.JFJE00%type; --病人现金交费金额 ls_ZZJFJE ZY_BRJFB0.JFJE00%type; --病人转账交费金额 ls_HJJE00 ZY_BRFY00.HJJE00%type; --合计金额 ls_ZFJE00 ZY_BRFY00.ZFJE00%type; --合计自付金额 ls_JZJE00 ZY_BRFY00.JZJE00%type; --记帐金额 ls_GFJE00 ZY_BRFY00.GFJE00%type; --公费金额 ls_YBZYH0 ZY_BRXXB0.YBZYH0%type; --医保住院号 ls_GRZHZF ZY_JZB000.GRZHZF%type; --个人医疗账户支付(医保返回) ls_TCJJZF ZY_JZB000.TCJJZF%type; --统筹基金支付(医保返回) ls_YBQTZF ZY_YBFS00.QTZFHJ%type; --其他支付合计 ls_HSHJJE ZY_BRFY00.HJJE00%type; --合计结算金额 ls_CFZFJE ZY_FYMX00.ZFJE00%type; --拆分自费金额 ls_CYYTK0 ZY_BRZHXX.ZYE000%type; --出院应退款 ls_JZMX00 ZY_JZMX00%rowtype; ls_ZPH000 ZY_BRJFB0.ZPH000%type; ls_ZPKHYH ZY_BRJFB0.ZPKHYH%type; ls_ZPDWMC ZY_BRJFB0.ZPDWMC%type; ls_PLBH00 BM_PJLXB0.PLBH00%type; ls_COUNT0 number; ls_CZRQ00 char(8); ls_CZSJ00 char(8); ls_SSWUTSCL char(4); ls_SFCFFP char(4); --是否拆分发票 ls_SF_ZY_FFJJ char(4); ls_DQBQ00 ZY_BRXXB0.DQBQ00%type; ls_FBBH00 ZY_BRXXB0.FBBH00%type; ls_FYQSRQ ZY_JZB000.FYQSRQ%type; ls_FYJZRQ ZY_JZB000.FYJZRQ%type; ZY_SFTSDYZSFP varchar2(4); ls_ZYPJMC BM_FPXM00.XMMC00%type; --发票名称 ls_YEXM00 varchar2(40); --婴儿姓名 ls_YBZXLB ZY_JZB000.YBZXLB%type; --医保中心类别 ls_YBLB00 ZY_JZB000.YBLB00%type; --医保类别 ls_YBBRLB ZY_JZB000.YBBRLB%type; --医保病人类别 Ecustom exception; ls_XZQH00 BM_BRXXB0.XZQH00%Type; ls_TYDWMC BM_BRXXB0.TYDWMC%Type; Vczyxm0 BM_YGBM00.ZWXM00%type; ls_YBFSDH ZY_YBFS00.YBFSDH%type; ls_DJH000 ZY_BRJFB0.DJH000%type; ls_GSDJH0 ZY_BRXXB0.GSDJH0%type; ls_CSLX00 ZY_BRXXB0.CSLX00%type; ls_BRZT00 ZY_BRXXB0.BRZT00%type; ls_YYID00 XT_YYXX00.YYID00%type; ls_ZZXJTC XT_XTCS00.VALUE0%type; ls_YBJZSJ varchar2(16); ls_JMJE00 number; ls_YHJE00 number; ZY_JSTKAYJJFS char(1); ls_ZY_FFJJ char(1); ls_SSWR00 number; ls_TMPPJH number; ls_YBZHYE number; ls_CFFPFB XT_XTCS00.VALUE0%type; ls_DBZJK0 varchar2(2); --医保单病种结算接口参数值 ls_YYZFJE number; --医院支付金额 ls_JSZFJE number; --结算自付金额 ls_FPZFJE number; --发票自付金额 ls_YBZFJE number; --医保支付合计金额 ls_GRZFE0 number; --医保返回个人自付额 ls_JFBZ00 ZY_BRJFB0.JFBZ00%type; --交费标志 '0'交预交金,'1'退预交金 '2'作废 ls_DBZSF0 XT_XTCS00.VALUE0%type; --单病种结算自付金额算法: =0默认; =1 医院支付>0,自付=医院支付+医保返回的自付金额,医院支付<=0,自付=医保返回的自付金额 ls_YBYYZF ZY_JZB000.YBYL05%type; --根据参数ZY_DBZJSZFJESF=1计算出来的医院支付金额 ls_FFBZBM ZY_BRXXB0.FFBZBM%type; --付费单病种编码 ls_JSBLLX ZY_YBFS00.YBSM10%type; --C-DRG结算病历类型 ls_FFBZJE number; --付费单病种对应收费金额 ls_CBCWFY number; --付费单病种超标床位费用 ls_CWHCFY number; --付费单病种除外耗材费用 ls_BCBXF0 number; --医保返回的报销费总金额 ls_BZZFJE number; --付费单病种病人自付金额 ls_YBYL43 number; --可报高值耗材费(C-DRG) ls_YBYL44 number; --超标床位费(C-DRG) ls_YBYL45 number; --超标高值耗材费(C-DRG) ls_ID0000 ZY_YBFSMX.ID0000%type; --医保发送明细的ID Vsfyxgz XT_XTCS00.VALUE0%type; --是否允许逃跑挂账的病人重新办理入院 cursor CUR_ZY_JZMX00_ZFBL00 is select * from ZY_JZMX00 where JZDH00 = ad_JZDH00 and nvl(ZFBL00,1)<>0 and SFYLXM='Y' order by ZFBL00; begin Pzxcgbz:=0; ls_CZRQ00 := to_char(sysdate,'YYYYMMDD'); ls_CZSJ00 := to_char(sysdate,'HH24:MI:SS'); ls_GRZHZF := 0; ls_TCJJZF := 0; ls_YBQTZF := 0; ls_CYQKJE := 0; --设置病人的出院欠款金额 ls_CYYTK0 := 0; ad_XJTKPJ := 0; ad_ZZTKPJ := 0; ls_CFZFJE := 0; ls_FFBZJE := 0; ls_CBCWFY := 0; ls_CWHCFY := 0; ls_BZZFJE := 0; ls_YBYL43 := 0; --可报高值耗材费(C-DRG) ls_YBYL44 := 0; --超标床位费(C-DRG) ls_YBYL45 := 0; --超标高值耗材费(C-DRG) select trim(nvl(max(VALUE0),'N')) into LS_SF_ZY_FFJJ from XT_XTCS00 where NAME00='SF_ZY_FFJJ'; --收费员逢分进角 select trim(nvl(max(VALUE0),'N')) into ZY_JSTKAYJJFS from XT_XTCS00 where NAME00='ZY_JSTKAYJJFS'; --住院病人中期结算退预交金是否以预交金方式转入Y是N否是 select trim(nvl(max(VALUE0),'N')) into ls_ZZXJTC from XT_XTCS00 where NAME00='ZY_CYJSZZXJTC'; --出院结算退"转账"预交金是否使用"现金"方式退出,Y是N否,默认N select trim(nvl(max(VALUE0),'N')) into LS_SFCFFP from XT_XTCS00 where NAME00='SF_SFCFFP'; --是否拆分发票 select trim(nvl(max(VALUE0),'N')) into ls_SSWUTSCL from XT_XTCS00 where NAME00='SF_SSWUTSCL'; --四舍五入部分的金额特殊处理,Y:是 select trim(nvl(max(VALUE0),'N')) into ls_CFFPFB from XT_XTCS00 where NAME00='ZY_KZMZFBBRCFFP'; --控制某种费别(离休)病人拆分发票(按是否医保来分)VALUE0=(BM_BRFBB0.FBBH00)默认为-1 select trim(nvl(max(VALUE0),'0')) into ls_DBZSF0 from XT_XTCS00 where NAME00='ZY_DBZJSZFJESF'; --单病种结算自付金额算法 select trim(nvl(max(VALUE0),'N')) into ls_DBZJK0 from XT_XTCS00 where NAME00='ZY_YBDBZJSJK'; --新农合单病种处理 jlg select trim(nvl(max(VALUE0),'0')) into Vsfyxgz from XT_XTCS00 where NAME00='ZY_SFYXTPGZBRCXRYDJ'; --是否允许逃跑挂账病人重新入院登记，1是0否，默认0 （重新入院后，在正常出院结算时要提示结清? select ZWXM00 into Vczyxm0 from BM_YGBM00 where YGBH00=Pczy000; select YYID00 into ls_YYID00 from BM_BMBM00 where BMBH00=Pczyks0; select BRID00,YBZYH0,XM0000,XB0000,DQBQ00,FBBH00,YBZXLB,YBLB00,YBBRLB,GSDJH0,CSLX00,BRZT00,trim(FFBZBM) into ls_BRID00,ls_YBZYH0,ls_XM0000,ls_XB0000,ls_DQBQ00,ls_FBBH00,ls_YBZXLB,ls_YBLB00,ls_YBBRLB,ls_GSDJH0,ls_CSLX00,ls_BRZT00,ls_FFBZBM from ZY_BRXXB0 where ZYID00 = ad_ZYID00; select XZQH00,TYDWMC into ls_XZQH00,ls_TYDWMC from BM_BRXXB0 where BRID00=ls_BRID00; if ls_BRZT00='20' and as_CZBZ00 in ('1','2') then Ptsxx00:='在院病人不能进行出院结算或挂账结算'; raise Ecustom; elsif ls_BRZT00='3' and as_CZBZ00 in ('0') and ad_YEXM00='全部' then Ptsxx00:='出科病人不能进行中途结算'; raise Ecustom; end if; --0.判断病人是否可以进行出院结算 if as_CZBZ00 in ('1','2','3') then select count(*) into ls_COUNT0 from ZY_BRZHXX where ZYID00 = ad_ZYID00 and SFDJ00 <> '2' and JFLBID <> 0 ; if ls_COUNT0 > 1 then Ptsxx00:='病人有多个账户,不能办理出院结算!请将其它账户结清后,再进行本操作！'; Pxxxx00:=SQLERRM; raise Ecustom; end if; end if; if (as_CZBZ00 ='1') and (Vsfyxgz = '1') then --需查看该病人之前是否有逃跑挂账出院的，如果有，则提示，且不能办理正常出院，除非已结清挂账 select count(*) into ls_COUNT0 from ZY_BRXXB0 where BRID00 = ls_BRID00 and BRZT00='5'; if ls_COUNT0 > 0 then Ptsxx00:='该病人之前的住院有发生逃跑挂账结算出院,现不能办理正常出院结算!请将之前住院的账结清后,再进行本操作！'; Pxxxx00:=SQLERRM; raise Ecustom; end if; end if; if trim(ad_YEXM00) in ('全部','母亲') then ls_YEXM00:=''; else ls_YEXM00:=ad_YEXM00; end if; if (ls_FFBZBM is not null) then --病种收费金额 select nvl(max(BZSFJE),0) into ls_FFBZJE from BM_BZSFBZ where FBBH00 = ls_FBBH00 and YBZXLB = ls_YBZXLB and YBBRLB = ls_YBBRLB and FFBZBM = ls_FFBZBM; --超标床位费，除外耗材费 select nvl(max(DBZCWF),0),nvl(max(DBZHCF),0) into ls_CBCWFY,ls_CWHCFY from ZY_BRZHXX where ZYID00 = ad_ZYID00 and JFLBID = ad_JFLBID; ls_BZZFJE := ls_FFBZJE + ls_CBCWFY + ls_CWHCFY; end if; SP_ZY_BRFY00_EDIT00(ad_ZYID00,ad_JFLBID,as_CZBZ00,as_JSZZRQ,ad_YEXM00,Pczy000,Pczyks0,'N',Pzxcgbz,Ptsxx00,Pxxxx00); if Pzxcgbz = 0 then Ptsxx00:='住院病人费用修改出错：'||Ptsxx00; raise Ecustom; end if; select SF_ZY_GETJZDH into ad_JZDH00 from dual; update ZY_FYMX00 a set JZDH00 = ad_JZDH00 where JFLBID = ad_JFLBID and ZYID00 = ad_ZYID00 and JZDH00+0=0 and (decode(nvl(a.YEXM00,'0'),'0','母亲',a.YEXM00)=ad_YEXM00 or ad_YEXM00='全部') and (CZRQ00 <= as_JSZZRQ or as_CZBZ00 in ('1','2','3')); --如果本次结算费用合计为0，直接UPDATE后返回 select sum(HJJE00) into ls_HJJE00 from ZY_FYMX00 a where ZYID00 = ad_ZYID00 and JZDH00=ad_JZDH00; if nvl(ls_HJJE00,0)=0 then update ZY_FYMX00 a set JZDH00=1 WHERE ZYID00 = ad_ZYID00 and JZDH00=ad_JZDH00; return; end if; --判断是否存在单个项目费用总额为负记录 jlg select count(*) into ls_COUNT0 from ( select a.XMBH00,a.XMMC00,sum(a.HJJE00) from ZY_FYMX00 a WHERE a.ZYID00 = ad_ZYID00 and a.JZDH00+0=ad_JZDH00 group by a.XMBH00,a.XMMC00 having sum(a.HJJE00)<0) b ; if ls_COUNT0>0 then Ptsxx00:='病人的本次结算存在单个项目总费用为负数情况,结算终止'; Pxxxx00:=''; Pzxcgbz:='2'; --raise Ecustom; rollback; Return; end if; select SF_ZY_GETYJJPLBH into ls_PLBH00 from dual; select SQ_ZY_PJSYQK_PJH000.nextval into ls_PJH000 from dual; --计算病人本次结算共交了预交金的总额 ls_XJJFJE:=0; ls_ZZJFJE:=0; --1、回收预交金处理 ls_JFDJHS:=as_JFDJHS; --由于中途结算或出院结算时,还应该同时将病人的交费信息做结算处理 if ls_JFDJHS is not null then loop ls_TMPDJH:=substr(ls_JFDJHS,1,instr(ls_JFDJHS,',')-1); if ls_TMPDJH <> '0' then select PJH000 into ls_TMPPJH from ZY_BRJFB0 where DJH000 = ls_TMPDJH; update ZY_PJSYQK set HSCZY0 = Pczy000, HSCZXM = Vczyxm0,HSRQ00 = ls_CZRQ00,HSSJ00 = ls_CZSJ00 where PJH000 = ls_TMPPJH; if sql%notfound then Ptsxx00:='病人预交金票据回收操作不成功,请与系统管理员联系！'; Pxxxx00:='update zy_pjsyqk set hsczy0= where pjh000 ='||to_char(ls_TMPPJH)||SQLERRM; rollback; return; end if; update ZY_BRJFB0 set JZDH00 = ad_JZDH00 where ZYID00 = ad_ZYID00 and JZDH00+0=0 and DJH000 = ls_TMPDJH; if SQL%NOTFOUND then Ptsxx00:='病人交费信息结算不成功,请与系统管理员联系！'; Pxxxx00:='update zy_brjfb0 set jzdh00= where brid00 ='||to_char(ls_brid00)||' and jzdh00=0 and djh000='||ls_TMPDJH||SQLERRM; rollback; return; end if; end if; ls_JFDJHS:=substr(ls_JFDJHS,instr(ls_JFDJHS,',')+1,length(ls_JFDJHS)); exit when instr(ls_JFDJHS,',') = 0; end loop; else --回收预交金单 update ZY_PJSYQK set HSCZY0 = Pczy000, HSCZXM = Vczyxm0,HSRQ00 = ls_CZRQ00,HSSJ00 = ls_CZSJ00 where PJH000 in (select PJH000 from ZY_BRJFB0 where ZYID00 = ad_ZYID00 and JZDH00+0=0); --if SQL%NOTFOUND then -- Ptsxx00:='病人预交金票据回收操作不成功,请与系统管理员联系！'; -- Pxxxx00:=''; -- raise Ecustom; --end if; update ZY_BRJFB0 set JZDH00 = ad_JZDH00 where ZYID00 = ad_ZYID00 and JZDH00+0=0; --if SQL%NOTFOUND then -- Ptsxx00:='病人交费信息结算不成功,请与系统管理员联系！'; -- Pxxxx00:='update zy_brjfb0 set jzdh00= where ZYID00 ='||to_char(AD_ZYID00)||' and jzdh00=0'; -- raise Ecustom; --end if; end if; --医保发送记录置标志 update ZY_YBFS00 set JZDH00=ad_JZDH00 where ZYID00=ad_ZYID00 and JZDH00=0 and CXBZ00='0'; --新增加的表要修改 select nvl(max(ID0000),0) into ls_ID0000 from ZY_YBFSMX where ZYID00=ad_ZYID00 and nvl(JZDH00,0)=0 and CXBZ00='0'; update ZY_YBFSMX set JZDH00=ad_JZDH00 where ID0000 = ls_ID0000; --冗余 update ZY_BRFY00 a set JZDH00 = ad_JZDH00 --结算单号 where ZYID00 = ad_ZYID00 and DJH000 in (select DJH000 from ZY_FYMX00 where JZDH00=ad_JZDH00 and ZYID00 = ad_ZYID00); --医保返回结果处理 增加ls_YYZFJE = YBYL05 (医院支付金额); 增加医保返回总费用的处理 ls_BCBXF0 select nvl(sum(HJJE00),0), nvl(sum(GRZFE0),0),nvl(sum(GRZHHJ),0),nvl(sum(TCJJHJ),0),nvl(sum(QTZFHJ),0),nvl(sum(YBYL05),0), nvl(max(YBFSDH),'0'),max(YBJZRQ||YBJZSJ),nvl(max(YBSM10),'无') into ls_BCBXF0,ls_GRZFE0,ls_GRZHZF,ls_TCJJZF,ls_YBQTZF,ls_YYZFJE, ls_YBFSDH,ls_YBJZSJ,ls_JSBLLX from ZY_YBFS00 where ZYID00=ad_ZYID00 and JZDH00=ad_JZDH00 and CXBZ00='0'; --C-DRG结算类型处理 if ls_JSBLLX = '无' then ls_JSBLLX := null; end if; ls_YBYYZF := 0; if ls_DBZSF0='1' then if ls_YYZFJE > 0 then ls_YBYYZF := ls_YYZFJE; ls_YYZFJE := 0; end if; end if; select nvl(max(GRZHYE),0) into ls_ybzhye from ZY_YBFS00 where ZYID00=ad_ZYID00 and JZDH00=ad_JZDH00 and CXBZ00='0' and rowid=(select max(ROWID) from ZY_YBFS00 where ZYID00=ad_ZYID00 and JZDH00=ad_JZDH00 and CXBZ00='0'); if ls_GRZHZF<>0 then select SQ_ZY_BRJFB0_DJH000.nextval into ls_DJH000 from dual; insert into ZY_BRJFB0(DJH000,BRID00,ZYID00,JFLBID,JFJE00,ZFFS00,ZFFSMC,PJH000, JFRQ00,JFSJ00,JFCZY0,CZYXM0,BRYE00,JFBZ00,JKBID0,JZDH00, PLBH00,DLR000,DQBQ00,CZYKS0,YYID00,JSLX00) values(ls_DJH000,ls_BRID00,ad_ZYID00,2,ls_GRZHZF,3,'医保个人账户',ls_PJH000, ls_CZRQ00,ls_CZSJ00,Pczy000,Vczyxm0,0,'0',0,ad_JZDH00, '3',-1,ls_DQBQ00,Pczyks0,ls_YYID00,'1'); SP_ZY_BRJFB0_INSERT(ls_DJH000, Pczy000, Pczyks0, 'N', Pzxcgbz, Ptsxx00, Pxxxx00); end if; if ls_TCJJZF<>0 then select SQ_ZY_BRJFB0_DJH000.nextval into ls_DJH000 from dual; insert into ZY_BRJFB0(DJH000,BRID00,ZYID00,JFLBID,JFJE00,ZFFS00,ZFFSMC,PJH000, JFRQ00,JFSJ00,JFCZY0,CZYXM0,BRYE00,JFBZ00,JKBID0,JZDH00, PLBH00,DLR000,DQBQ00,CZYKS0,YYID00,JSLX00) values(ls_DJH000,ls_BRID00,ad_ZYID00,2,ls_TCJJZF,4,'医保统筹基金',ls_PJH000, ls_CZRQ00,ls_CZSJ00,Pczy000,Vczyxm0,0,'0',0,ad_JZDH00, '3',-1,ls_DQBQ00,Pczyks0,ls_YYID00,'1'); SP_ZY_BRJFB0_INSERT(ls_DJH000, Pczy000, Pczyks0, 'N', Pzxcgbz, Ptsxx00, Pxxxx00); end if; if ls_YBQTZF<>0 then select SQ_ZY_BRJFB0_DJH000.nextval into ls_DJH000 from dual; insert into ZY_BRJFB0(DJH000,BRID00,ZYID00,JFLBID,JFJE00,ZFFS00,ZFFSMC,PJH000, JFRQ00,JFSJ00,JFCZY0,CZYXM0,BRYE00,JFBZ00,JKBID0,JZDH00, PLBH00,DLR000,DQBQ00,CZYKS0,YYID00,JSLX00) values(ls_DJH000,ls_BRID00,ad_ZYID00,2,ls_YBQTZF,12,'医保其他支付',ls_PJH000, ls_CZRQ00,ls_CZSJ00,Pczy000,Vczyxm0,0,'0',0,ad_JZDH00, '3',-1,ls_DQBQ00,Pczyks0,ls_YYID00,'1'); SP_ZY_BRJFB0_INSERT(ls_DJH000, Pczy000, Pczyks0, 'N', Pzxcgbz, Ptsxx00, Pxxxx00); end if; --单病种,添加医院支付金额 jlg (政和地区模式) if (ls_DBZJK0 = 'Y') and (ls_YYZFJE<>0) then select SQ_ZY_BRJFB0_DJH000.nextval into ls_DJH000 from dual; insert into ZY_BRJFB0(DJH000,BRID00,ZYID00,JFLBID,JFJE00,ZFFS00,ZFFSMC,PJH000, JFRQ00,JFSJ00,JFCZY0,CZYXM0,BRYE00,JFBZ00,JKBID0,JZDH00, PLBH00,DLR000,DQBQ00,CZYKS0,YYID00,JSLX00) values(ls_DJH000,ls_BRID00,ad_ZYID00,2,ls_YYZFJE,20,'医院支付',ls_PJH000, ls_CZRQ00,ls_CZSJ00,Pczy000,Vczyxm0,0,'0',0,ad_JZDH00, '3',-1,ls_DQBQ00,Pczyks0,ls_YYID00,'1'); SP_ZY_BRJFB0_INSERT(ls_DJH000, Pczy000, Pczyks0, 'N', Pzxcgbz, Ptsxx00, Pxxxx00); elsif (ls_FFBZBM is not null) or (ls_JSBLLX is not null) then --付费单病种、C-DRG结算流程,添加医院支付金额 jlg (省属医院单病种模式) if ls_FBBH00 = 1 then --自费单病种病人 ls_YYZFJE := ls_HJJE00 - ls_BZZFJE; --医院支付金额 = 院内结算的总金额 - 病种自付金额 ls_FFBZJE -->ls_BZZFJE else --医保病人 if ls_HJJE00 - ls_BCBXF0 <> 0 then ls_YYZFJE := ls_HJJE00 - ls_BCBXF0; --医院支付金额 = 院内结算的总金额 - 医保返回的总金额 end if; --如果ls_HJJE00 - ls_BCBXF0 =0 则ls_YYZFJE还是取原来的值 end if; if ls_YYZFJE <> 0 then select SQ_ZY_BRJFB0_DJH000.nextval into ls_DJH000 from dual; insert into ZY_BRJFB0(DJH000,BRID00,ZYID00,JFLBID,JFJE00,ZFFS00,ZFFSMC,PJH000, JFRQ00,JFSJ00,JFCZY0,CZYXM0,BRYE00,JFBZ00,JKBID0,JZDH00, PLBH00,DLR000,DQBQ00,CZYKS0,YYID00,JSLX00) values(ls_DJH000,ls_BRID00,ad_ZYID00,2,ls_YYZFJE,20,'医院支付',ls_PJH000, ls_CZRQ00,ls_CZSJ00,Pczy000,Vczyxm0,0,'0',0,ad_JZDH00, '3',-1,ls_DQBQ00,Pczyks0,ls_YYID00,'1'); SP_ZY_BRJFB0_INSERT(ls_DJH000, Pczy000, Pczyks0, 'N', Pzxcgbz, Ptsxx00, Pxxxx00); end if; end if; --累计的现金交费金额 select nvl(sum(JFJE00),0) into ls_XJJFJE from ZY_BRJFB0 where ZYID00 = ad_ZYID00 and JZDH00 = ad_JZDH00 and JFLBID = ad_JFLBID and ZFFS00 in (1,8) and JFBZ00<>'2'; --现金 --累计的转帐交费金额 select nvl(sum(JFJE00),0) into ls_ZZJFJE from ZY_BRJFB0 where ZYID00 = ad_ZYID00 and JZDH00 = ad_JZDH00 and JFLBID = ad_JFLBID and ZFFS00 = 2 and JFBZ00<>'2'; --转帐 --计算病人本次结算的费用总额 select nvl(sum(HJJE00),0),nvl(sum(ZFJE00),0),nvl(sum(JZJE00),0),min(Trim(CZRQ00)),max(trim(CZRQ00)), nvl(sum(decode(JMBZ00,1,GFJE00,0)),0),nvl(sum(decode(JMBZ00,1,0,GFJE00)),0) into ls_HJJE00,ls_ZFJE00,ls_JZJE00,ls_FYQSRQ,ls_FYJZRQ,ls_JMJE00,ls_YHJE00 from ZY_FYMX00 where JZDH00= ad_JZDH00 and ZYID00 = ad_ZYID00; if ((ls_DBZJK0 = 'Y') or (ls_FFBZBM is not null) or (ls_JSBLLX is not null)) and (ls_YYZFJE <> 0) then ls_CYQKJE := ls_ZFJE00 - ls_XJJFJE - ls_ZZJFJE - ls_GRZHZF - ls_TCJJZF - ls_YBQTZF - ls_YYZFJE; --计算病人的欠费的金额，出院欠款金额 else ls_CYQKJE := ls_ZFJE00 - ls_XJJFJE - ls_ZZJFJE - ls_GRZHZF - ls_TCJJZF - ls_YBQTZF; --计算病人的欠费的金额，出院欠款金额 end if; if as_CZBZ00='2' then if (ls_CYQKJE<0) then ls_CYYTK0:=-1*ls_CYQKJE; ls_CYQKJE:=0; end if; elsif ls_CYQKJE>0 and as_CZBZ00<>'2' then Ptsxx00:='账户余额不够,请先补交'||to_char(ls_CYQKJE)||'元,再进行本操作！'; Pxxxx00:=SQLERRM; ls_CYQKJE:=0; Pzxcgbz:='3'; rollback; return; else ls_CYQKJE:=0; end if; --医保支付金额 if ((ls_DBZJK0 = 'Y') or (ls_FFBZBM is not null) or (ls_JSBLLX is not null)) and (ls_FBBH00 = 3) and (ls_YYZFJE <> 0) then ls_YBZFJE := ls_GRZHZF + ls_TCJJZF + ls_YBQTZF + ls_YYZFJE; else ls_YBZFJE := ls_GRZHZF + ls_TCJJZF + ls_YBQTZF; end if; if ls_ZFJE00- ls_YBZFJE<0 then Ptsxx00:='结算异常,医保支付的费用('||to_char(ls_YBZFJE)||')大于患者费用('||to_char(ls_ZFJE00)||'),请核实！'; Pxxxx00:=SQLERRM; Pzxcgbz:='4'; rollback; return; end if; --计算现金余额，转账余额 if (ls_FFBZBM is not null) and (ls_FBBH00 = 1) then --自费的付费单病种病人 if ls_ZZJFJE > ls_BZZFJE then --如果转账金额足够(大于病种自付金额)，先扣转账金额的 ad_XJYE00 := ls_XJJFJE; ad_ZZYE00 := ls_ZZJFJE - ls_BZZFJE; else ad_XJYE00 := ls_XJJFJE + ls_ZZJFJE - ls_BZZFJE; ad_ZZYE00 := 0; end if; elsif ((ls_FFBZBM is not null) or (ls_JSBLLX is not null)) and (ls_FBBH00 = 3) then --医保的付费单病种病人 if ls_ZZJFJE > ls_GRZFE0 then --如果转账金额足够(大于医保返回的个人自付金额)，先扣转账金额的 ad_XJYE00 := ls_XJJFJE; ad_ZZYE00 := ls_ZZJFJE - ls_GRZFE0; else ad_XJYE00 := ls_XJJFJE + ls_ZZJFJE - ls_GRZFE0; ad_ZZYE00 := 0; end if; else --政和单病种模式及非单病种模式 if (ls_ZZJFJE > ls_ZFJE00 - ls_YBZFJE) and (ls_ZZJFJE>0) then --(如果有足够转账余额的，先扣除转账的) ad_XJYE00 := ls_XJJFJE; ad_ZZYE00 := ls_ZZJFJE + ls_YBZFJE - ls_ZFJE00; else ad_XJYE00 := ls_XJJFJE + ls_ZZJFJE + ls_YBZFJE - ls_ZFJE00; --现金余额 = 现金交费金额+转帐交费金额+医保支付合计-合计自付金额 ad_ZZYE00 := 0; end if; end if; --省人民:出院结算退"转账"预交金使用"现金"方式退出 if ad_ZZYE00>0 and ls_ZZXJTC='Y' and as_CZBZ00='1' then ad_XJYE00 := ad_XJYE00+ad_ZZYE00; ad_ZZYE00 := 0; end if; --医保病人 if ls_FBBH00=3 then --计算结算自付金额 ls_JSZFJE := ls_ZFJE00 - (ls_GRZHZF + ls_TCJJZF + ls_YBQTZF); if ((ls_DBZJK0 = 'Y') or (ls_FFBZBM is not null) or (ls_JSBLLX is not null)) then --单病种模式 if (ls_YYZFJE>=0) then --如果有医院支付金额(报销金额大于医保支付金额合计)，则个人自付金额可减掉该部分金额 jlg！ ls_JSZFJE := ls_JSZFJE - ls_YYZFJE; else --医院支付金额为负(报销金额小于医保支付金额合计),则结算自付金额直接用医保返回的个人自付金额 jlg! ls_JSZFJE := ls_GRZFE0; end if; end if; select count(*) into ls_COUNT0 from ZY_YBFS00 where ZYID00=ad_ZYID00 and JZDH00=ad_JZDH00 and CXBZ00='0'; --无医保发送记录，则为医保病人按自费结算（不发送医保） if ls_COUNT0 = 0 then --如果是省属单病种病人，则不允许 if (ls_FFBZBM is not null) then Ptsxx00:='该单病种医保病人未找到医保返回的收费记录，结算终止。（系统不允许单病种医保病人按不发送医保的来处理）'; Pxxxx00:=Ptsxx00; Pzxcgbz:='0'; rollback; return; end if; insert into ZY_JZB000(JZDH00,YBDJH0,BRID00,ZYID00,YEXM00,JSLX00,FBBH00,YBZXLB, YBLB00,YBBRLB,HJJE00,BRZJE0,ZFJE00,JZJE00,GFJE00, JMJE00,GRZHZF,TCJJZF,SBGRZH,SBTCJJ,CYYSK0,CYYTK0,YSZJE0, ZZZJE0,XJYE00,ZZYE00,XJFKJE,ZZFKJE,PJH000,JZZT00,FPHSBZ, JZRQ00,JZSJ00,JZY000,CZYKS0,JZBQH0,JMYY00,YBZYH0,BCKBCS, JZFS00,JKJKBZ,CFXMS0,YBXJZF,YPWQZF,XMWQZF,YPBFZF,XMBFZF, YBQFJE,YBZHYE,YLZFJE,CFDXJE,GWYBZ0,JJZFE0,FYBJE0,SYBXZF, SBZFE0,BJZFE0,XZQH00,TYDWMC,CSLX00,FFBZBM) select ad_JZDH00,'0',ls_BRID00,ad_ZYID00,ad_YEXM00,as_CZBZ00,FBBH00,YBZXLB, YBLB00,'0',ls_HJJE00,ls_HJJE00,ls_ZFJE00,ls_JZJE00,ls_JMJE00, ls_YHJE00,0,0,0,0,ls_CYQKJE,ls_CYYTK0,ls_XJJFJE, ls_ZZJFJE,ad_XJYE00,ad_ZZYE00,0,0,ls_PJH000,decode(as_CZBZ00,'2','2','0'),'0', ls_CZRQ00,ls_CZSJ00,Pczy000,Pczyks0,ls_DQBQ00,as_JMYY00,'0',0, ls_CSLX00,'0',0,0,0,0,0,0, 0,0,0,0,0,0,0,0, 0,0,ls_XZQH00,ls_TYDWMC,Pcslx00,ls_FFBZBM from IC_YBBRLB where FBBH00=1 and rownum=1; else insert into ZY_JZB000(JZDH00,YBDJH0,BRID00,ZYID00,YEXM00,JSLX00,FBBH00,YBZXLB, YBLB00,YBBRLB,HJJE00,BRZJE0,ZFJE00,JZJE00,GFJE00, JMJE00,GRZHZF,TCJJZF,YBQTZF,SBGRZH,SBTCJJ,CYYSK0,CYYTK0,YSZJE0, ZZZJE0,XJYE00,ZZYE00,XJFKJE,ZZFKJE,PJH000,JZZT00,FPHSBZ, JZRQ00,JZSJ00,JZY000,CZYKS0,JZBQH0,JMYY00,YBZYH0,BCKBCS, TSBZBH,DBZBM0,JZFS00,JKJKBZ,CFXMS0,YBJZRQ,YBJZSJ,YBRYRQ, YBCYRQ,YBCYSJ,YBXJZF,YPWQZF,XMWQZF,YPBFZF,XMBFZF,YBQFJE, YBZHYE,FYQSRQ,FYJZRQ,GSDJH0,YLZFJE,CFDXJE,GWYBZ0,JJZFE0, FYBJE0,SYBXZF,SBZFE0,BJZFE0, YBYL01,YBYL02,YBYL03,YBYL04,YBYL05,YBYL06,YBYL07,YBYL08,YBYL09,YBYL10, YBYL11,YBYL12,YBYL13,YBYL14,YBYL15,YBYL16,YBYL17,YBYL18,YBYL19,YBYL20, YBYL21,YBYL22,YBYL23,YBYL24,YBYL25,YBYL26,YBYL27,YBYL28,YBYL29,YBYL30, YBYL31,YBYL32,YBYL33,YBYL34,YBYL35,YBYL49,XZQH00,TYDWMC,CSLX00,FFBZBM, YBYL39,YBYL40,YBYL41,YBYL42,YBSM09,YBSM10,YBSM11) select ad_JZDH00,ls_YBFSDH,ls_BRID00,ad_ZYID00,ad_YEXM00,as_CZBZ00,ls_FBBH00,ls_YBZXLB, ls_YBLB00,ls_YBBRLB,ls_HJJE00,ls_HJJE00,ls_JSZFJE,ls_JZJE00,ls_JMJE00, ls_YHJE00,sum(GRZHZF),sum(TCJJZF),sum(QTZFHJ),sum(SBGRZH),sum(SBTCJJ),ls_CYQKJE,ls_CYYTK0,ls_XJJFJE, ls_ZZJFJE,ad_XJYE00,ad_ZZYE00,0,0,ls_PJH000,decode(as_CZBZ00,'2','2','0'),'0', ls_CZRQ00,ls_CZSJ00,Pczy000,Pczyks0,ls_DQBQ00,as_JMYY00,ls_YBZYH0,0, as_TSBZBH,ad_DBZBM0,ls_CSLX00,'0',0,substrb(ls_YBJZSJ,1,8),substrb(ls_YBJZSJ,9,8),null, '20991231',null,sum(GRZFE0+DBGRZF),sum(YPWQZF),sum(XMWQZF),sum(YPBFZF),sum(XMBFZF),null, ls_ybzhye,ls_FYQSRQ,ls_FYJZRQ,ls_GSDJH0,sum(YLZFJE),sum(CFDXJE),sum(GWYBZ0),sum(BJJJZF), sum(FYBFY0),sum(SYBXZF),sum(SBZFE0),sum(BJZFE0), sum(YBYL01),sum(YBYL02),sum(YBYL03),sum(YBYL04),decode(sign(ls_YBYYZF),1,0,sum(YBYL05)),sum(YBYL06),sum(YBYL07),sum(YBYL08),sum(YBYL09),sum(YBYL10), sum(YBYL11),sum(YBYL12),sum(YBYL13),sum(YBYL14),sum(YBYL15),sum(YBYL16),sum(YBYL17),sum(YBYL18),sum(YBYL19),sum(YBYL20), sum(YBYL21),sum(YBYL22),sum(YBYL23),sum(YBYL24),sum(YBYL25),sum(YBYL26),sum(YBYL27),sum(YBYL28),sum(YBYL29),sum(YBYL30), sum(YBYL31),sum(YBYL32),sum(YBYL33),sum(YBYL34),sum(YBYL35),decode(sign(ls_YBYYZF),1,sum(YBYL05),0),ls_XZQH00,ls_TYDWMC,Pcslx00,ls_FFBZBM, sum(YBYL39),sum(YBYL40),sum(YBYL41),sum(YBYL42),max(YBSM09),max(YBSM10),max(YBSM11) from ZY_YBFS00 where ZYID00=ad_ZYID00 and JZDH00=ad_JZDH00 and CXBZ00='0'; end if; else --非医保病人 if (ls_FFBZBM is not null) and (ls_FBBH00 = 1) then ls_JSZFJE := ls_BZZFJE; --自费病人单病种自付金额 = 病种收费金额 + 超标床位费 + 除外项目费 else ls_JSZFJE := ls_ZFJE00; end if; insert into ZY_JZB000(JZDH00,YBDJH0,BRID00,ZYID00,YEXM00,JSLX00,FBBH00,YBZXLB, YBLB00,YBBRLB,HJJE00,BRZJE0,ZFJE00,JZJE00,GFJE00, JMJE00,GRZHZF,TCJJZF,SBGRZH,SBTCJJ,CYYSK0,CYYTK0,YSZJE0, ZZZJE0,XJYE00,ZZYE00,XJFKJE,ZZFKJE,PJH000,JZZT00,FPHSBZ, JZRQ00,JZSJ00,JZY000,CZYKS0,JZBQH0,JMYY00,YBZYH0,BCKBCS, JZFS00,JKJKBZ,CFXMS0,YBXJZF,YPWQZF,XMWQZF,YPBFZF,XMBFZF, YBQFJE,YBZHYE,YLZFJE,CFDXJE,GWYBZ0,JJZFE0,FYBJE0,SYBXZF, SBZFE0,BJZFE0,XZQH00,TYDWMC,CSLX00,FFBZBM) select ad_JZDH00,'0',ls_BRID00,ad_ZYID00,ad_YEXM00,as_CZBZ00,ls_FBBH00,ls_YBZXLB, ls_YBLB00,ls_YBBRLB,ls_HJJE00,ls_HJJE00,ls_JSZFJE,ls_JZJE00,ls_JMJE00, ls_YHJE00,0,0,0,0,ls_CYQKJE,ls_CYYTK0,ls_XJJFJE, ls_ZZJFJE,ad_XJYE00,ad_ZZYE00,0,0,ls_PJH000,decode(as_CZBZ00,'2','2','0'),'0', ls_CZRQ00,ls_CZSJ00,Pczy000,Pczyks0,ls_DQBQ00,as_JMYY00,'0',0, ls_CSLX00,'0',0,0,0,0,0,0, 0,0,0,0,0,0,0,0, 0,0,ls_XZQH00,ls_TYDWMC,Pcslx00,ls_FFBZBM from dual; end if; --如果是付费单病种的，则YBYL04记录该病种的标准收费金额,YBYL33记录超标床位费用，YBYL34记录除外耗材费用,YBYL35记录医保返回的总金额 jlg --重新计算医院支付金额(ZY_JZB000.YBYL05) jlg if (ls_FFBZBM is not null) then update ZY_JZB000 set YBYL04 = ls_FFBZJE, YBYL05 = ls_YYZFJE, YBYL33 = ls_CBCWFY, YBYL34 = ls_CWHCFY, YBYL35 = ls_BCBXF0 where ZYID00 = ad_ZYID00 and JZDH00 = ad_JZDH00; elsif (ls_JSBLLX is not null) then --C-DRG结算流程，重新计算一些值 update ZY_JZB000 set YBYL05 = ls_YYZFJE, YBYL35 = ls_BCBXF0, YBYL43 = SF_ZY_DBZCBFY(ad_ZYID00,ls_BRID00,null,ls_ID0000,3), YBYL44 = SF_ZY_DBZCBFY(ad_ZYID00,ls_BRID00,null,ls_ID0000,0), YBYL45 = SF_ZY_DBZCBFY(ad_ZYID00,ls_BRID00,null,ls_ID0000,2) where ZYID00 = ad_ZYID00 and JZDH00 = ad_JZDH00; end if; --2、填写病人结账明细表信息 --先判断一下要结算的项目是否有对应的发票项目 select count(*),MIN(SFXMID) into ls_COUNT0,ls_SFXMID from ZY_FYMX00 A,BM_YYSFXM C where A.XMBH00 = C.SFXMID and C.ZYFPID not in (select FPXMID from BM_FPXM00 ) and A.JZDH00=ad_JZDH00 and A.ZYID00= ad_ZYID00; -- and A.BRID00 = ls_BRID00; if ls_COUNT0 > 0 then --若有存在没有发票项目对应的项目,则提示信息! Ptsxx00:='病人结算操作失败，发票项目不完整,请记录本信息并与系统管理员联系!'; Pxxxx00:='BM_YYSFXM.SFXMID='||to_char(ls_SFXMID)||'的ZYFPID的值NOT IN BM_FPXM00.FPXMID!'; raise Ecustom; end if; ls_FZPJH0:=ls_PJH000; --开始的票据号取同组的组号 if (LS_SFCFFP='Y') and (ls_FBBH00 not in (1,3)) then--公费病人拆分 insert into ZY_JZMX00(JZDH00,XMBH00,XMMC00,XMJE00,JMJE00,JMSHR0,JMYY00,ZFBL00,SFYLXM,ZFJE00,QZFJE0) select ad_JZDH00,B.FPXMID,B.XMMC00,NVL(sum(A.HJJE00),0),0,null,null,A.ZFBL00,B.SFYLXM,NVL(SUM(A.ZFJE00),0),NVL(SUM(A.QZFJE0),0) from ZY_FYMX00 A,BM_FPXM00 B,BM_YYSFXM C where A.XMBH00 = C.SFXMID and C.ZYFPID = B.FPXMID and A.JZDH00 = ad_JZDH00 and A.ZYID00 = ad_ZYID00 group by B.FPXMID,B.XMMC00,A.ZFBL00,B.SFYLXM; --自费金额冲销后，按公费金额记账，那么虽然自付比例为1,但总金额为0 ，所以不应插入表ZY_JZMX00 delete from ZY_JZMX00 where JZDH00=ad_JZDH00 and XMJE00=0 and ZFJE00=0 and QZFJE0=0 ; delete from ZY_PJSYMX where PJH000 = ls_PJH000; --旧票据明细删除 update ZY_PJSYQK set XJJE00 = 0,ZZJE00 = 0,YHKJE0 = 0 where FZPJH0 = ls_PJH000; --原值去掉 open CUR_ZY_JZMX00_ZFBL00; loop --产生部分自费的发票 ? fetch CUR_ZY_JZMX00_ZFBL00 into ls_JZMX00; exit when CUR_ZY_JZMX00_ZFBL00%notfound; insert into ZY_PJSYMX(PJH000,XMBH00,XMMC00,XMJE00,XMBZ00,ZFBL00,QZFJE0,SFYLXM) values(ls_PJH000,ls_JZMX00.XMBH00,ls_JZMX00.XMMC00,ls_JZMX00.XMJE00,0,ls_JZMX00.ZFBL00,ls_JZMX00.QZFJE0,LS_JZMX00.SFYLXM); update ZY_PJSYQK set XJJE00 = NVL(XJJE00,0)+ls_JZMX00.XMJE00*ls_JZMX00.ZFBL00, ZZJE00 = NVL(ZZJE00,0)+ls_JZMX00.XMJE00*(1-ls_JZMX00.ZFBL00), HJJE00 = NVL(XJJE00,0)+ls_JZMX00.XMJE00, ZFJE00 = NVL(XJJE00,0)+ls_JZMX00.XMJE00*ls_JZMX00.ZFBL00, JZJE00 = NVL(ZZJE00,0)+ls_JZMX00.XMJE00*(1-ls_JZMX00.ZFBL00) where PJH000 = ls_PJH000; if sql%notfound then insert into ZY_PJSYQK(PJH000,PLBH00,PLMC00,PJXH00,XJJE00,ZZJE00,CZY000,CZYXM0, DYRQ00,DYSJ00,SFDY00,PJZT00,XM0000,XB0000,FZPJH0,YEXM00, HJJE00,ZFJE00,JZJE00,JMJE00,YHJE00,GRZHZF,TCJJZF,YYID00) select ls_PJH000,PLBH00,PLMC00,SF_ZY_GETPJXH(Pczy000,3),ls_JZMX00.XMJE00*ls_JZMX00.ZFBL00,ls_JZMX00.XMJE00*(1-ls_JZMX00.ZFBL00),Pczy000,Vczyxm0, ls_CZRQ00,ls_CZSJ00,'Y','0',ls_XM0000,ls_XB0000,ls_FZPJH0,ls_YEXM00, ls_JZMX00.XMJE00,ls_JZMX00.XMJE00*ls_JZMX00.ZFBL00,ls_JZMX00.XMJE00*(1-ls_JZMX00.ZFBL00),0,0,0,0,ls_YYID00 from bm_pjlxb0 where PLBH00 = 3;--PLBH00=3是住院发票 SP_ZY_PJSYQK_INSERT(ls_PJH000, Pczy000, Pczyks0, 'N', Pzxcgbz, Ptsxx00, Pxxxx00); end if; end loop; close CUR_ZY_JZMX00_ZFBL00; else insert into ZY_JZMX00(JZDH00,XMBH00,XMMC00,XMJE00,JMJE00,JMSHR0,JMYY00,ZFBL00,SFYLXM,ZFJE00,QZFJE0) select ad_JZDH00,B.FPXMID,B.XMMC00,NVL(sum(A.HJJE00),0),0,null,null,1,B.SFYLXM,NVL(sum(a.zfje00),0),nvl(sum(a.QZFJE0),0) from ZY_FYMX00 A,BM_FPXM00 B where A.ZYFPID = B.FPXMID and A.JZDH00 = ad_JZDH00 and A.ZYID00 = ad_ZYID00 group by B.FPXMID,B.XMMC00,B.SFYLXM; --合计结算金额 select NVL(sum(XMJE00),0) into ls_HSHJJE from ZY_JZMX00 where JZDH00=ad_JZDH00 and SFYLXM='Y'; --拆分自费金额 if instrb(','||trim(ls_CFFPFB)||',',','||to_char(ls_FBBH00)||',')>0 and (ls_FBBH00 not in (1,3)) then select NVL(sum(ZFJE00),0) into ls_CFZFJE from ZY_JZMX00 where JZDH00=ad_JZDH00 and SFYLXM='Y'; end if; if ls_HSHJJE-ls_CFZFJE<>0 then insert into ZY_PJSYMX(PJH000,XMBH00,XMMC00,XMJE00,XMBZ00,ZFBL00,QZFJE0,SFYLXM) select ls_PJH000,XMBH00,XMMC00,XMJE00-decode(ls_CFZFJE,0,0,ZFJE00),0,ZFBL00,QZFJE0,SFYLXM from ZY_JZMX00 where JZDH00=ad_JZDH00 and SFYLXM='Y' and (ls_CFZFJE=0 or ls_CFZFJE<>0 and XMJE00-ZFJE00<>0); ls_FPZFJE := ls_JSZFJE - ls_CFZFJE; insert into ZY_PJSYQK(PJH000,PLBH00,PLMC00,PJXH00,XJJE00,ZZJE00,CZY000,CZYXM0, DYRQ00,DYSJ00,SFDY00,PJZT00,XM0000,XB0000,YEXM00,HJJE00, ZFJE00,JZJE00,JMJE00,YHJE00,GRZHZF,TCJJZF,YBQTZF,ZYID00,YYID00) select ls_PJH000,PLBH00,PLMC00,decode(ls_HSHJJE-ls_CFZFJE,0,'0',SF_ZY_GETPJXH(Pczy000,3)),ls_HSHJJE-ls_CFZFJE,0,Pczy000,Vczyxm0, ls_CZRQ00,ls_CZSJ00,'Y','0',ls_XM0000,ls_XB0000,ls_YEXM00,ls_HSHJJE-ls_CFZFJE, ls_FPZFJE,ls_JZJE00,ls_JMJE00,ls_YHJE00,ls_GRZHZF,ls_TCJJZF,ls_YBQTZF,ad_ZYID00,ls_YYID00 from BM_PJLXB0 where PLBH00 = 3;--PLBH00=3是住院发票 if sql%notfound then Ptsxx00:='系统字典不完整,所需的票据类型不存在,操作失败!'; Pxxxx00:=SQLERRM||'票据类型不存在: Not exists(select * from bm_pjlxb0 where PLBH00 = 3)'; raise Ecustom; end if; SP_ZY_PJSYQK_INSERT(ls_PJH000, Pczy000, Pczyks0, 'N', Pzxcgbz, Ptsxx00, Pxxxx00); end if; --省人民:市保健病人发票打印拆分为自费和记账2张 if ls_CFZFJE<>0 then if ls_HSHJJE-ls_CFZFJE=0 then ls_PJH001 := ls_PJH000; else select SQ_ZY_PJSYQK_PJH000.nextval into ls_PJH001 from dual; end if; insert into ZY_PJSYMX(PJH000,XMBH00,XMMC00,XMJE00,XMBZ00,ZFBL00,QZFJE0,SFYLXM) select ls_PJH001,XMBH00,XMMC00,ZFJE00,0,ZFBL00,QZFJE0,SFYLXM from ZY_JZMX00 where JZDH00=ad_JZDH00 and SFYLXM='Y' and ZFJE00<>0; --3、填写病人票据信息表的内容 insert into ZY_PJSYQK(PJH000,PLBH00,PLMC00,PJXH00,XJJE00,ZZJE00,CZY000,CZYXM0, DYRQ00,DYSJ00,SFDY00,PJZT00,XM0000,XB0000,YEXM00,HJJE00, ZFJE00,JZJE00,JMJE00,YHJE00,GRZHZF,TCJJZF,YBQTZF,ZYID00,YYID00,FZPJH0) select ls_PJH001,PLBH00,PLMC00,decode(ls_CFZFJE,0,'0',SF_ZY_GETPJXH(Pczy000,3)),ls_CFZFJE,0,Pczy000,Vczyxm0, ls_CZRQ00,ls_CZSJ00,'Y','0',ls_XM0000,ls_XB0000,ls_YEXM00,ls_CFZFJE, ls_CFZFJE,0,0,0,0,0,0,ad_ZYID00,ls_YYID00,ls_PJH000 from BM_PJLXB0 where PLBH00 = 3;--PLBH00=3是住院发票 if sql%notfound then Ptsxx00:='系统字典不完整,所需的票据类型不存在,操作失败!'; Pxxxx00:=SQLERRM||'票据类型不存在: Not exists(select * from bm_pjlxb0 where PLBH00 = 3)'; raise Ecustom; end if; SP_ZY_PJSYQK_INSERT(ls_PJH001, Pczy000, Pczyks0, 'N', Pzxcgbz, Ptsxx00, Pxxxx00); if ls_PJH001 <> ls_PJH000 then update ZY_PJSYQK set SFBCF0='1' where PJH000 = ls_PJH000; update ZY_PJSYQK set SFBCF0='2' where PJH000 = ls_PJH001; end if; end if; end if; --伙食费发票、非医疗项目费用 select NVL(sum(XMJE00),0) INTO ls_HSHJJE from ZY_JZMX00 where JZDH00=ad_JZDH00 and SFYLXM='N'; if ls_HSHJJE<>0 then select SQ_ZY_PJSYQK_PJH000.nextval into ls_PJH001 from dual; select NVL(sum(zfje00),0) INTO ls_HSHJJE from ZY_JZMX00 where JZDH00=ad_JZDH00 and sfylxm='N'; insert into ZY_PJSYMX(PJH000,XMBH00,XMMC00,XMJE00,XMBZ00,ZFBL00,QZFJE0,SFYLXM) select ls_PJH001,XMBH00,XMMC00,XMJE00,0,ZFBL00,QZFJE0,SFYLXM from ZY_JZMX00 where jzdh00=ad_jzdh00 and sfylxm='N'; insert into ZY_PJSYQK(PJH000,PLBH00,PLMC00,PJXH00,XJJE00,ZZJE00,CZY000,CZYXM0,DYRQ00,DYSJ00,SFDY00,PJZT00, XM0000,XB0000,FZPJH0,YEXM00,HJJE00,ZFJE00,JZJE00,JMJE00,YHJE00,ZYID00,YYID00) select ls_PJH001,PLBH00,PLMC00,SF_ZY_GETPJXH(Pczy000,10),ls_HSHJJE,0,Pczy000,Vczyxm0, ls_CZRQ00,ls_CZSJ00,'Y','0',ls_XM0000,ls_XB0000,ls_FZPJH0,ls_YEXM00,ls_HSHJJE,0,0,0,0,ad_ZYID00,ls_YYID00 from bm_pjlxb0 where PLBH00 = 10;--PLBH00=10是住院伙食发票 SP_ZY_PJSYQK_INSERT(ls_PJH001, Pczy000, Pczyks0, 'N', Pzxcgbz, Ptsxx00, Pxxxx00); end if; --6.若病人账户上有剩余金额,还应该将该剩余金额退还给病人(由于医保病人还有医保帐户退回的,可以在医保接口完成后再Update..where DJH000=ad_XJDJH0) if (ls_SSWUTSCL = 'Y')AND(ls_ZY_FFJJ='Y') and (as_CZBZ00 ='1') then --非呆账出院结算，可能需要进行四舍五入 '0':中途结算,'1':出院结算2:呆账结算3:年度结算 ls_SSWR00:=ad_XJYE00-round(ad_XJYE00+0.04,1); ad_XJYE00:=round(ad_XJYE00+0.04,1); if abs(ls_SSWR00) > 0.1 then Ptsxx00:='系统结算四舍五入金额发生错误,该部分的金额不能为大于1的值!'; Pxxxx00:='退还金额:'||to_char(ad_XJYE00)||',四舍五入后金额:'||to_char(round(ad_XJYE00,1)); raise Ecustom; end if; end if; if ls_SSWR00<>0 then select SQ_ZY_BRJFB0_DJH000.nextval into ls_DJH000 from dual; --病人现金的退款单据号 insert into ZY_BRJFB0(DJH000,BRID00,JFLBID,JFJE00,ZFFS00,ZFFSMC,ZPH000,ZPKHYH, PJH000,JFRQ00,JFSJ00,JFCZY0,CZYXM0,BRYE00,JFBZ00,JKBID0, JZDH00,PLBH00,ZYID00,DLR000,DQBQ00,CZYKS0,YYID00) values(ls_DJH000,ls_BRID00,'2',-ls_SSWR00,5,'四舍五入金额','','', ls_PJH000,ls_CZRQ00,ls_CZSJ00,Pczy000,Vczyxm0,0,'1',0, ad_JZDH00,'3',ad_ZYID00,-1,ls_DQBQ00,Pczyks0,ls_YYID00); SP_ZY_BRJFB0_INSERT(ls_DJH000, Pczy000, Pczyks0, 'N', Pzxcgbz, Ptsxx00, Pxxxx00); end if; if as_CZBZ00 ='2' then --呆账病人出院结算 select SQ_ZY_BRJFB0_DJH000.nextval into ls_DJH000 from dual; insert into ZY_BRJFB0(DJH000,BRID00,ZYID00,JFLBID,JFJE00,ZFFS00,ZFFSMC,PJH000, JFRQ00,JFSJ00,JFCZY0,CZYXM0,BRYE00,JFBZ00,JKBID0,JZDH00, PLBH00,JSLX00,DLR000,DQBQ00,CZYKS0,YYID00) select ls_DJH000,ls_BRID00,ad_ZYID00,2,decode(ls_CYQKJE,0,-1*ls_CYYTK0,ls_CYQKJE),9,ZFFSMC,ls_PJH000, ls_CZRQ00,ls_CZSJ00,Pczy000,Vczyxm0,0,decode(sign(decode(ls_CYQKJE,0,-1*ls_CYYTK0,ls_CYQKJE)),1,0,-1,1,0),0,ad_JZDH00, '3','1',-1,ls_DQBQ00,Pczyks0,ls_YYID00 from BM_BRZFFS where ZFFSBH=9; SP_ZY_BRJFB0_INSERT(ls_DJH000, Pczy000, Pczyks0, 'N', Pzxcgbz, Ptsxx00, Pxxxx00); /* select SQ_ZY_BRJFB0_DJH000.nextval into ad_XJDJH0 from dual; select SQ_ZY_PJSYQK_PJH000.nextval into ls_PJH000 from dual; Insert into ZY_BRJFB0(DJH000,BRID00,ZYID00,JFLBID,JFJE00,ZFFS00,ZFFSMC,PJH000, JFRQ00,JFSJ00,JFCZY0,CZYXM0,BRYE00,JFBZ00,JKBID0,JZDH00, PLBH00,JSLX00,DLR000,DQBQ00,CZYKS0,YYID00) values(ad_XJDJH0,ls_BRID00,ad_ZYID00,0,decode(ls_CYQKJE,0,-1*ls_CYYTK0,ls_CYQKJE),9,'逃跑欠费',ls_PJH000, ls_CZRQ00,ls_CZSJ00,Pczy000,Vczyxm0,0,'0',0,ad_JZDH00, ls_PLBH00,'1',-1,ls_DQBQ00,Pczyks0,ls_YYID00); SP_ZY_BRJFB0_INSERT(ad_XJDJH0, Pczy000, Pczyks0, 'N', Pzxcgbz, Ptsxx00, Pxxxx00); ad_XJTKPJ:=ls_PJH000; --jlg 现金退款票据号 as_XJTKXH:= SF_ZY_GETPJXH(Pczy000,ls_PLBH00); Insert into ZY_PJSYQK(PJH000,PLBH00,PLMC00,PJXH00,XJJE00,ZZJE00,YHKJE0,CZY000,CZYXM0,DYRQ00,DYSJ00,SFDY00,PJZT00, XM0000,XB0000,HSCZY0,HSCZXM,HSRQ00,HSSJ00,YEXM00,HJJE00,ZFJE00,JZJE00,JMJE00,YHJE00,ZYID00,YYID00) select ls_PJH000,PLBH00,PLMC00,as_XJTKXH,-ad_XJYE00,0,0,Pczy000,Vczyxm0, ls_CZRQ00,ls_CZSJ00,'Y','0',ls_XM0000,ls_XB0000,Pczy000,Vczyxm0,ls_CZRQ00,ls_CZSJ00,ls_YEXM00,-ad_XJYE00,0,0,0,0,ad_ZYID00,ls_YYID00 from BM_PJLXB0 where PLBH00 = ls_PLBH00 ; --退预交金时使用收款票据 SP_ZY_PJSYQK_INSERT(ls_PJH000, Pczy000, Pczyks0, 'N', Pzxcgbz, Ptsxx00, Pxxxx00);*/ end if; --病人有剩余现金金额，则进行退款；如果现金余额为负数，则需要补交现金金额 if (ad_XJYE00 <> 0) and (as_CZBZ00 <>'2') then select SQ_ZY_BRJFB0_DJH000.nextval into ad_XJDJH0 from dual; select SQ_ZY_PJSYQK_PJH000.nextval into ls_PJH000 from dual; if (ls_SSWUTSCL = 'Y')AND(LS_SF_ZY_FFJJ='Y') and (as_CZBZ00 ='1') then --对于四舍五入且逢分进角，则金额特殊处理 ad_XJYE00:=round(ad_XJYE00+0.04,1); end if; --交费标识 剩余金额为正数，则退款('1')，剩余金额为负数，则补交款('0') select nvl(decode(sign(ad_XJYE00),1,'1','0'),'0') into ls_JFBZ00 from dual; insert into ZY_BRJFB0(DJH000,BRID00,ZYID00,JFLBID,JFJE00,ZFFS00,ZFFSMC,PJH000, JFRQ00,JFSJ00,JFCZY0,CZYXM0,BRYE00,JFBZ00,JKBID0,JZDH00, PLBH00,JSLX00,DLR000,YYID00) select ad_XJDJH0,ls_BRID00,ad_ZYID00,ad_JFLBID,-ad_XJYE00,ZFFSBH,ZFFSMC,ls_PJH000, ls_CZRQ00,ls_CZSJ00,Pczy000,Vczyxm0,0,ls_JFBZ00,0,ad_JZDH00, ls_PLBH00,'1',-1,ls_YYID00 from BM_BRZFFS where ZFFSBH=1; SP_ZY_BRJFB0_INSERT(ad_XJDJH0, Pczy000, Pczyks0, 'N', Pzxcgbz, Ptsxx00, Pxxxx00); ad_XJTKPJ:=ls_PJH000; --jlg 现金退款票据号 as_XJTKXH:= SF_ZY_GETPJXH(Pczy000,ls_PLBH00); insert into ZY_PJSYQK(PJH000,PLBH00,PLMC00,PJXH00,XJJE00,ZZJE00,YHKJE0,CZY000,CZYXM0,DYRQ00,DYSJ00,SFDY00,PJZT00, XM0000,XB0000,HSCZY0,HSCZXM,HSRQ00,HSSJ00,YEXM00,HJJE00,ZFJE00,JZJE00,JMJE00,YHJE00,ZYID00,YYID00) select ls_PJH000,PLBH00,PLMC00,as_XJTKXH,-ad_XJYE00,0,0,Pczy000,Vczyxm0, ls_CZRQ00,ls_CZSJ00,'Y','0',ls_XM0000,ls_XB0000,Pczy000,Vczyxm0,ls_CZRQ00,ls_CZSJ00,ls_YEXM00,-ad_XJYE00,0,0,0,0,ad_ZYID00,ls_YYID00 from BM_PJLXB0 where PLBH00 = ls_PLBH00 ; --退预交金时使用收款票据 SP_ZY_PJSYQK_INSERT(ls_PJH000, Pczy000, Pczyks0, 'N', Pzxcgbz, Ptsxx00, Pxxxx00); end if; --若病人有剩余转账金额,进行退款；如果转账余额为负数，则需要补交转账金额 if (ad_ZZYE00 <> 0) and (as_CZBZ00 <>'2') then select SQ_ZY_BRJFB0_DJH000.nextval into ad_ZZDJH0 from dual; --病人转账的退款单据号 select SQ_ZY_PJSYQK_PJH000.nextval into ls_PJH000 from dual; --交费标识 剩余金额为正数，则退款('1')，剩余金额为负数，则补交款('0') select nvl(decode(sign(ad_ZZYE00),1,'1','0'),'0') into ls_JFBZ00 from dual; select ZPH000,ZPKHYH,ZPDWMC into ls_ZPH000,ls_ZPKHYH,ls_ZPDWMC from ZY_BRJFB0 where ZYID00 = ad_ZYID00 and JZDH00 = ad_JZDH00 and JFLBID = ad_JFLBID and ZFFS00 = 2 and djh000=(select max(djh000) from ZY_BRJFB0 where ZYID00 = ad_ZYID00 and JZDH00 = ad_JZDH00 and JFLBID = ad_JFLBID and ZFFS00 = 2); insert into ZY_BRJFB0(DJH000,BRID00,ZYID00,JFLBID,JFJE00,ZFFS00,ZFFSMC,ZPH000,ZPKHYH,ZPDWMC,PJH000, JFRQ00,JFSJ00,JFCZY0,CZYXM0,BRYE00,JFBZ00,JKBID0,JZDH00,PLBH00,JSLX00,DLR000,YYID00) values(ad_ZZDJH0,ls_BRID00,ad_ZYID00,ad_JFLBID,-ad_ZZYE00,2,'转账',ls_ZPH000,ls_ZPKHYH,ls_ZPDWMC,ls_PJH000, ls_CZRQ00,ls_CZSJ00,Pczy000,Vczyxm0,0,ls_JFBZ00,0,ad_JZDH00,ls_PLBH00,'1',-1,ls_YYID00); SP_ZY_BRJFB0_INSERT(ad_ZZDJH0, Pczy000, Pczyks0, 'N', Pzxcgbz, Ptsxx00, Pxxxx00); ad_ZZTKPJ := ls_PJH000; as_ZZTKXH := SF_ZY_GETPJXH(Pczy000,ls_PLBH00); insert into ZY_PJSYQK(PJH000,PLBH00,PLMC00,PJXH00,XJJE00,ZZJE00,YHKJE0,CZY000,CZYXM0,DYRQ00,DYSJ00,SFDY00,PJZT00, XM0000,XB0000,HSCZY0,HSCZXM,HSRQ00,HSSJ00,YEXM00,HJJE00,ZFJE00,JZJE00,JMJE00,YHJE00,ZYID00,YYID00) select ls_PJH000,PLBH00,PLMC00,as_ZZTKXH,0,-ad_ZZYE00,0,Pczy000,Vczyxm0, ls_CZRQ00,ls_CZSJ00,'Y','0',ls_XM0000,ls_XB0000,Pczy000,Vczyxm0,ls_CZRQ00,ls_CZSJ00,ls_YEXM00,-ad_ZZYE00,0,0,0,0,ad_ZYID00,ls_YYID00 from BM_PJLXB0 where PLBH00 = ls_PLBH00 ; --退预交金时使用收款票据 if sql%notfound then Ptsxx00:='系统字典不完整,所需的票据类型不存在,操作失败!'; Pxxxx00:=SQLERRM||'票据类型不存在: Not exists(select * from bm_pjlxb0 where PLBH00 = 8)'; raise Ecustom; end if; SP_ZY_PJSYQK_INSERT(ls_PJH000, Pczy000, Pczyks0, 'N', Pzxcgbz, Ptsxx00, Pxxxx00); end if; update ZY_BRZHXX set XJYE00=0,ZZYE00=0,ZYE000=0,YKZYE0=0,YBYZF0=0,YBYJS0=0,YBTCJE=0,YBZFJE=0,BZWFY0=0 where ZYID00 = ad_ZYID00 and JFLBID = ad_JFLBID; --1、补充: update ZY_BRZHXX set WJSJE0 = (select nvl(sum(ZFJE00),0) from ZY_FYMX00 where ZYID00=ad_ZYID00 and JZDH00+0=0) where ZYID00=ad_ZYID00 and JFLBID = '2'; if as_CZBZ00='0' then --累计未回收的现金交费金额 select NVL(sum(JFJE00),0) into ls_XJJFJE from ZY_BRJFB0 where ZYID00 = ad_ZYID00 and JZDH00 = 0 and JFLBID = '2' and ZFFS00 in (1,8); --现金 --累计未回收的转帐交费金额 select NVL(sum(JFJE00),0) into ls_ZZJFJE from ZY_BRJFB0 where ZYID00 = ad_ZYID00 and JZDH00 = 0 and JFLBID = '2' and ZFFS00 = 2; --转帐 select nvl(sum(ZFJE00),0) into ls_ZFJE00 from ZY_FYMX00 where ZYID00=ad_ZYID00 and JZDH00=0; if ls_ZZJFJE>ls_ZFJE00 then update ZY_BRZHXX set ZZYE00=ls_ZZJFJE-ls_ZFJE00, XJYE00=ls_XJJFJE, ZYE000=ls_XJJFJE+ls_ZZJFJE-ls_ZFJE00, YKZYE0=ls_XJJFJE+ls_ZZJFJE-ls_ZFJE00 where ZYID00=ad_ZYID00 and JFLBID = '2'; else update ZY_BRZHXX set ZZYE00=0, XJYE00=ls_XJJFJE+ls_ZZJFJE-ls_ZFJE00, ZYE000=ls_XJJFJE+ls_ZZJFJE-ls_ZFJE00, YKZYE0=ls_XJJFJE+ls_ZZJFJE-ls_ZFJE00 where ZYID00=ad_ZYID00 and JFLBID = '2'; end if; end if; --结算后，结算核对状态改为'未核对' update ZY_BRXXB0 set JSHDZT = '0',HDRQ00 = null,HDSJ00 = null,HDR000 = null where ZYID00 = ad_ZYID00; SP_ZY_JZMXXM_SJZL00(ad_JZDH00); Pzxcgbz:=1; if Pcommit='Y' then commit; end if; exception when no_data_found then raise_application_error(-20001,substrb('数据没有找到！*'||sqlerrm,1,120)); when Ecustom then raise_application_error(-20010,substrb(Ptsxx00||'！*',1,120)); when others then raise_application_error(-20266,substrb(nvl(sqlerrm, '原因不明出错')||'！*',1,120)); end;