create or replace procedure SP_ZY_JKKJXX_TJ0000 ( as_KSRQ00 in char, as_JSRQ00 in char, as_KSSJ00 in char, as_JSSJ00 in char, as_SFY000 in char, --收费员姓名 as_YYID00 in char, --医院ID ad_ID0000 out number, --统计表ID as_YHMSG0 out varchar, --存储过程提示的错误信息 as_SYSMSG out varchar, --系统提示的错误信息 as_TJFS00 in char default '0' --0:所有 ) -- MODIFICATION HISTORY -- Person Date Comments -- xzw 2007.11.06 对商保病人进行特殊处理 -- xzw 2008.01.17 医保费用包含商保费用问题进行重新处理,根据IC_YBBRLB中的字段YBBHSB的值进行处理; -- qks 2009.12.21 计算3(3)应退还转账 增加条件 and B.PJZT00='0' -- xzw 2010.03.08 医保金额增加公务员补助 -- zyc 2010.03.23 计算3(3)应退还转账下查询条件(A.JFLBID改为substr(A.JFLBID,1,1)) -- qks 2010.03.24 修改公务员补助GWYBZ0、商保(ad_SBZHJE、ad_SBTJJE) 是否包含在统筹金额中的问题; -- lml 2011.01.26 增加拆分发票的统计:修改为BDBZ00 in ('0','8','9') -- jinfl 2012.02.29 增加收费员所属医院ID MZSF-20120224-0024 -- gzj 2018.06.01 5.0移植到9.0 ZYSF9-20180531-001 -- jlg 2018.06.11 处理结算冲销缴费数据的过滤问题 ZYSF9-20180611-001 -- qks 2018.08.04 1、处理共济账户支付YBYL13(南平政和为大额医保补偿)独立统计,民政补助金额(独立于统筹基金金额),商保支付金额(独立于统筹基金金额); 2、 3、预交金退出 金额不对问题; for ZYSF9-20180731-001 -- qks 2018.08.20 6、退款票据 也需要加入YBQTZF统计; for ZYSF9-20180820-002 -- jlg 2019.07.10 修改记账金额、退记账款、实交现金的计算方式(upd by xtz) ZYSF9-20190709-003 -- qks 2019.11.08 重新修改取值：计算3、预交金退出 ; for ZYSF9-20191108-004 -- qks 2019.11.15 2、住院票据 以及 6、退款票据 对应的“修正值”取值修改，增加了JZJE00; for ZYSF9-20191118-001 -- jlg 2020.09.29 重整过程 ZYSF9-20200928-002 -- linzetao 2021.01.19 移除IC_YBBRLB.YBBHSB处理 ZYSF9-20210109-001 -- linzetao 2021.02.01 修正"住院票据金额"与对应的明细项合计不一致问题 ZYSF9-20210201-001 -- linzetao 2021.02.05 调整"住院票据金额"中"冲预收款"和"冲原单据"的统计 ZYSF9-20210208-001 -- linzetao 2021.03.22 增加"挂账结清"项目 ZYSF9-20210304-002 -- linzetao 2021.04.28 整理归档现场调整统计挂账结清增加分院区条件 ZYSF9-20210419-001 -- linzetao 2021.05.21 增加"挂账未结清"统计项目 ZYSF9-20210517-001 -- linzetao 2021.05.27 增加"血费平台支付"统计项目 ZYSF9-20210514-002 -- zhangyc 2021.12.30 CUR_ZY_BRJFB0_SFY000 增加查询结账表（如果ZY_BRJFB0无数据，无法查询到结账表数据）by ZYSF9-20211230-001 -- luowf 2022.02.21 增加用血直免报销金额 ZYSF9-20220217-002 -- linzetao 2022.08.10 增加聚合支付统计金额(ZFFS00=28,ZFMXBH=B) ZYSF9-20220808-001 -- linzetao 2022.08.31 住院票据项目下增加“医院支付”明细项 ZYSF9-20220830-001 -- linzetao 2022.08.31 调整“预交金退出”项目显示格式 ZYSF9-20220831-001 -- chenHeyi 2023.03.28 结清挂账金额LS_GZJQJE的算法，减去应退款与取消结算应退款 ZYSF9-20230321-002 -- jlg 2023.06.12 根据门诊住院费别分开原则，将原来从BM_BRXXB0取费别信息的改为从ZY_BRXXB0取对应费别信息 ZYSF9-20230610-001 -- chenHeyi 2023.07.21 增加"腕带付退款",即普通操作员结算退款通过腕带付扫码支付时的收款 ZYSF9-20230714-001 -- liwm 2024.04.30 医大附二现场慢，破坏CUR_ZY_BRJFB0_SFY000游标JZY000的索引 ZYSF13-20240430-003 -- qks 2024.05.02 解决分院无法统计; 2(1)冲预收款=冲住院费用 :ls_XMJE03 增加-LS_YYZFSK, 要把退款的医院支付加上; 医院支付取值(DYID00 = 140; -LS_YYZFTK -->LS_YYZFSK)。 ZYSF13-20240504-002 -- jiangjh 2025.04.16 挂账未结清金额 增加系统参数ZYSF_GZWJQJESFQYKSSJ是否启用开始时间 ZYSF13-20250410-001 as ls_YGBH00 BM_YGBM00.YGBH00%type; ls_ZWXM00 BM_YGBM00.ZWXM00%type; ls_JKDH00 BM_JKB000.JKDH00%type; ls_XMJE00 BM_JKKJMX.XMJE00%type; --项目金额0 ls_BEIZH0 BM_JKKJMX.BEIZHU%type; --备注 ls_BEIZH1 BM_JKKJMX.BEIZHU%type; --备注1 ls_BEIZH2 BM_JKKJMX.BEIZHU%type; --备注1 ls_TEXT00 varchar2(600); ls_COUNT0 number(5); ls_ZPSR00 number(12,2); --支票收入 ls_YHKSR0 number(12,2); --银行卡收入 ls_YHKJS0 number(12,2); --银行卡结算 ls_YBJS00 number(12,2); --医保结算 ls_JMBRYF number(12,2); --减免病人医药费 ls_CZYFY0 number(12,2); --冲住院费用 LS_JZJE00 number(12,2); --记账金额 ls_TJZJE0 number(12,2); --退记账款 ls_JZYSK0 number(12,2); --记账应收款 ls_CYYSK0 number(12,2); --出院病人应收款 ls_XMJE01 number(12,2); ls_XMJE02 number(12,2); ls_XMJE03 number(12,2); ls_XMJE04 number(12,2); ls_XMJE05 number(12,2); ls_XMJE06 number(12,2); ls_XMJE07 number(12,2); ls_XMJE08 number(12,2); ls_XMJE09 number(12,2); ls_XMJE10 number(12,2); ls_XMJE11 number(12,2); ls_XMJE12 number(12,2); ls_XMJE13 number(12,2); ls_XMJE14 number(12,2); ls_XMJE15 number(12,2); ls_XMJE15_1 number(12,2); ls_XMJE16 number(12,2); ls_XMJE17 number(12,2); ls_XMJE18 number(12,2); ls_XMJE19 number(12,2); ls_XMJE20 number(12,2); ls_XMJE21 number(12,2); ls_XMJE22 number(12,2); ls_XMJE23 number(12,2); ls_XMJE24 number(12,2); ls_XMJE25 number(12,2); ls_XMJE26 number(12,2); ls_XMJE27 number(12,2); ls_XMJE28 number(12,2); ls_XMJE29 number(12,2); --保存在医院系统中计算的医保个人帐户支付的金额 ls_XMJE30 number(12,2); --暂时未使用 ls_XMJE31 number(12,2); --暂时未使用 ls_XMJE32 number(12,2); --暂时未使用 ls_XMJE33 number(12,2); ls_XMJE34 number(12,2); ls_XMJE35 number(12,2); --收预交金 ls_XMJE36 number(12,2); --退预交金 ls_XMJE48 number(12,2); --医院支付金额 ls_XMJE49 number(12,2); --民政救助 ls_sswr00 number(12,2); --四舍五入金额 ls_sswr01 number(12,2); --四舍五入金额 ls_KSBH00 BM_GHKSTJ_KSDY00.BH0000%type; ls_KSBHJS number(4); LS_YYZFSK number(12,2); --医院支付金额 LS_YYZFSK_TC number(12,2); --医院支付金额_统筹 LS_YYZFTK number(12,2); --医院支付金额退 LS_YYZFTK_TC number(12,2); --医院支付金额退_统筹 ls_MZBZJE number(12,2); --民政补助金额 ls_MZBZJETK number(12,2); --民政补助金额退 ls_SFXMYB varchar2(10); --是否厦门医保接口 ls_SYBXZF number(12,2); --商业保险支付金额 ls_SYBXZFTK number(12,2); --商业保险支付额退 ls_XMJE50 number(12,2); LS_YHTKJE number(12,2); --应退还银行卡 LS_YHTKJE_ZC number(12,2); LS_YHTKJE_CX number(12,2); --应退还银行卡 LS_YHTKJE_TEMP number(12,2); --应退还银行卡 LS_WXKJE number(12,2); --应退还微信 LS_WXKJE_ZC number(12,2); --应退微信正常 LS_WXKJE_CX number(12,2); --应退还微信 LS_WXKJE_TEMP number(12,2); --应退还微信 LS_ZFBKJE number(12,2); --应退还支付宝 LS_ZFBKJE_ZC number(12,2); --应退支付宝正常 LS_ZFBKJE_CX number(12,2); --应退还支付宝 LS_ZFBKJE_TEMP number(12,2); --应退还支付宝 LS_GZJQJE number(12,2); --挂账结清金额 LS_GZJQJE_S number(12,2); --挂账结清金额收 LS_GZJQJE_T number(12,2); --挂账结清金额退 LS_GZJQJE_S_CX number(12,2); --挂账结清金额收(取消结算) LS_GZJQJE_T_CX number(12,2); --挂账结清金额退(取消结算) LS_GZWJQ0 number(12,2); --挂账未结清金额 LS_GZWJQ0_YSK number(12,2); --挂账未结清应收款 LS_GZWJQ0_YTK number(12,2); --挂账未结清应退款 LS_DBZ000 number(12,2); LS_DBZ001 number(12,2); ls_ZRZYYJ number(12,2); --择日住院预交金转入 ls_MZBZJE1 number(12,2); --民证补助 ZY_YHTTKPOS varchar2(100); LS_WXZF00_SR number(12,2); --微信支付收入 LS_WXZF00_TK number(12,2); --微信支付退款 LS_WXZF00 number(12,2); --微信支付 LS_ZFBZF0_SR number(12,2); --支付宝支付收入 LS_ZFBZF0_TK number(12,2); --支付宝支付退款 LS_ZFBZF0 number(12,2); --支付宝支付 LS_XFPTZF number(12,2); --血费支付平台 ls_yyid00 xt_yyxx00.yyid00%type; LS_YHSJJE number(12,2); --银行实际金额 LS_QYPTZF number(12,2); --区域预交金合计 LS_RYTZF0 number(12,2); --榕医通支付金额 LS_JHZFJE number(12,2); --聚合支付 LS_QYYJJPT varchar2(100); --是否开启区域预交金功能 Y:启用 N:不启用 LS_YXZMJE number(12,2); --用血直免金额 ZYSF9-20220217-002 LS_SFZMJE XT_XTCS00.VALUE0%type; --是否显示用血直免金额 ZYSF9-20220217-002 LS_WDFCZY BM_TYZD00.MC0000%type; --字典参数WDF_SFCZY0 腕带付收费员 LS_WDFTKJE number(12,2); --用腕带付退款金额 ZYSF_GZWJQJESFQYKSSJ XT_XTCS00.VALUE0%type; --挂账未结清金额是否启用开始时间 Y.是 N.否 默认 N cursor CUR_ZY_BRJFB0_SFY000 is select distinct aa.YGBH00,aa.ZWXM00 from ( select YGBH00,ZWXM00 from BM_YGBM00 B where exists( select 1 from ZY_BRJFB0 A where A.JFCZY0 = B.YGBH00 and A.JFRQ00 >= as_KSRQ00 and A.JFRQ00 <= as_JSRQ00 and A.JFRQ00||A.JFSJ00 >= as_KSRQ00||as_KSSJ00 and A.JFRQ00||A.JFSJ00 <= as_JSRQ00||as_JSSJ00 ) and (B.ZWXM00||'_'||B.XKH000 = as_SFY000 or as_SFY000='所有收费员') union all select YGBH00,ZWXM00 from BM_YGBM00 B where exists( select 1 from ZY_JZB000 A where A.JZY000+0 = B.YGBH00 and A.JZRQ00 >= as_KSRQ00 and A.JZRQ00 <= as_JSRQ00 and A.JZRQ00||A.JZSJ00 >= as_KSRQ00||as_KSSJ00 and A.JZRQ00||A.JZSJ00 <= as_JSRQ00||as_JSSJ00 ) and (B.ZWXM00||'_'||B.XKH000 = as_SFY000 or as_SFY000='所有收费员') ) aa ; begin --7、应缴金额=1、预交金收入+2、住院票据金额-3、预交金退出-4、退款收据+(4)现金暂存 ls_XMJE29:=0; --保存在医院系统中计算的医保个人帐户支付的金额 ls_KSBH00:='01'; ls_KSBHJS:=1; LS_YHTKJE:=0; LS_YHSJJE:=0; --银行实际金额 LS_QYPTZF:=0; --区域预交金合计 LS_RYTZF0:=0; --榕医通支付金额 LS_JHZFJE:=0; --聚合支付 /* if as_YYID00 = '225085' then ls_yyid00:=0; end if; */ ls_yyid00 :=nvl(as_YYID00,'0'); --厦门医保接口 select nvl(max(trim(value0)),'N') into ls_SFXMYB from XT_XTCS00 where NAME00 ='ZY_SFXMYBJK'; --住院系统现金收入审核表和缴款表银行卡收入是否扣除应退还POS金额 Y:扣除 N:不扣除 默认值Y select nvl(max(trim(value0)),'Y') into ZY_YHTTKPOS from XT_XTCS00 where NAME00 ='ZY_YHTTKPOS'; select SF_SF_TYZD00('福建预交金统一平台','Open_ZY') into LS_QYYJJPT from dual; select SF_SF_TYZD00('腕带付床头卡接口参数','WDF_SFCZY0') into LS_WDFCZY from dual; select SQ_BM_YYSFTJ_ID0000.nextval into ad_ID0000 from dual; --是否显示用血直免报销 ZYSF9-20220217-002 select nvl(max(trim(VALUE0)),'0') into LS_SFZMJE from XT_XTCS00 where NAME00 ='ZY_SFXSYXZMBX'; open CUR_ZY_BRJFB0_SFY000; loop fetch CUR_ZY_BRJFB0_SFY000 into ls_YGBH00,ls_ZWXM00; exit when CUR_ZY_BRJFB0_SFY000%notfound; insert into BM_GHKSTJ_KSDY00(ID0000,BH0000,GHKSBH,GHKSMC) values(ad_ID0000,ls_KSBH00,ls_YGBH00,ls_ZWXM00); ls_KSBHJS:=ls_KSBHJS+1; select lpad(to_char(ls_KSBHJS),2,'0') into ls_KSBH00 from dual; end loop; close CUR_ZY_BRJFB0_SFY000; insert into BM_GHKSTJ(ID0000,DYID00,GHRQ00,GHKS80) values(ad_ID0000,1 ,' 1、预交金收入',0); insert into BM_GHKSTJ(ID0000,DYID00,GHRQ00,GHKS80) values(ad_ID0000,37 ,' (1)、收预交金',1); insert into BM_GHKSTJ(ID0000,DYID00,GHRQ00,GHKS80) values(ad_ID0000,38 ,' (2)、退预交金',2); insert into BM_GHKSTJ(ID0000,DYID00,GHRQ00,GHKS80) values(ad_ID0000,2 ,' 2、住院票据金额',3); insert into BM_GHKSTJ(ID0000,DYID00,GHRQ00,GHKS80) values(ad_ID0000,3 ,' 其中: (1)冲预收款',4); insert into BM_GHKSTJ(ID0000,DYID00,GHRQ00,GHKS80) values(ad_ID0000,4 ,' (2)冲原收据',5); insert into BM_GHKSTJ(ID0000,DYID00,GHRQ00,GHKS80) values(ad_ID0000,5 ,' (3)补交现金',6); insert into BM_GHKSTJ(ID0000,DYID00,GHRQ00,GHKS80) values(ad_ID0000,6 ,' (4)补交支票',7); --insert into BM_GHKSTJ(ID0000,DYID00,GHRQ00,GHKS80) values(ad_ID0000,7 ,' (5)银行卡结算',8); insert into BM_GHKSTJ(ID0000,DYID00,GHRQ00,GHKS80) values(ad_ID0000,8 ,' (6)医保结算',9); insert into BM_GHKSTJ(ID0000,DYID00,GHRQ00,GHKS80) values(ad_ID0000,11 ,' (7)减免病人医药费',10); insert into BM_GHKSTJ(ID0000,DYID00,GHRQ00,GHKS80) values(ad_ID0000,12 ,' (8)记帐金额',11); insert into BM_GHKSTJ(ID0000,DYID00,GHRQ00,GHKS80) values(ad_ID0000,9 ,' (9)出院病人应收款',12); insert into BM_GHKSTJ(ID0000,DYID00,GHRQ00,GHKS80) values(ad_ID0000,140,' (10)医院支付',12.1); insert into BM_GHKSTJ(ID0000,DYID00,GHRQ00,GHKS80) values(ad_ID0000,13 ,' 3、预交金退出',13); insert into BM_GHKSTJ(ID0000,DYID00,GHRQ00,GHKS80) values(ad_ID0000,14 ,' 其中: (1)冲住院费用',14); insert into BM_GHKSTJ(ID0000,DYID00,GHRQ00,GHKS80) values(ad_ID0000,16 ,' 含: (1.1)应退还转账',14.2); insert into BM_GHKSTJ(ID0000,DYID00,GHRQ00,GHKS80) values(ad_ID0000,17 ,' (1.2)现金暂存',14.3); --应退还银行卡 insert into BM_GHKSTJ(ID0000,DYID00,GHRQ00,GHKS80) values(ad_ID0000,177,' (1.3)应退还Pos',14.4); --应退还微信 insert into BM_GHKSTJ(ID0000,DYID00,GHRQ00,GHKS80) values(ad_ID0000,178,' (1.4)应退还微信',14.5); --应退还支付宝 insert into BM_GHKSTJ(ID0000,DYID00,GHRQ00,GHKS80) values(ad_ID0000,179,' (1.5)应退还支付宝',14.6); insert into BM_GHKSTJ(ID0000,DYID00,GHRQ00,GHKS80) values(ad_ID0000,15,' (2)现金退出',15); --insert into BM_GHKSTJ(ID0000,DYID00,GHRQ00,GHKS80) values(ad_ID0000,18,' (5)四舍五入',18); insert into BM_GHKSTJ(ID0000,DYID00,GHRQ00,GHKS80) values(ad_ID0000,20,' 4、退款收据',19); insert into BM_GHKSTJ(ID0000,DYID00,GHRQ00,GHKS80) values(ad_ID0000,21,' 其中:(1)退非医保款',20); insert into BM_GHKSTJ(ID0000,DYID00,GHRQ00,GHKS80) values(ad_ID0000,22,' (2)退医保款',21); insert into BM_GHKSTJ(ID0000,DYID00,GHRQ00,GHKS80) values(ad_ID0000,23,' (3)退记帐款',22); insert into BM_GHKSTJ(ID0000,DYID00,GHRQ00,GHKS80) values(ad_ID0000,39,' (4)退减免款',23); insert into BM_GHKSTJ(ID0000,DYID00,GHRQ00,GHKS80) values(ad_ID0000,40,' (5)退出院病人应收款',24); insert into BM_GHKSTJ(ID0000,DYID00,GHRQ00,GHKS80) values(ad_ID0000,19,' 5、收回旧收据',25); insert into BM_GHKSTJ(ID0000,DYID00,GHRQ00,GHKS80) values(ad_ID0000,10,' 6、补开新收据',26); insert into BM_GHKSTJ(ID0000,DYID00,GHRQ00,GHKS80) values(ad_ID0000,24,' 7、应缴金额 ',27); insert into BM_GHKSTJ(ID0000,DYID00,GHRQ00,GHKS80) values(ad_ID0000,25,' 8、支票收入 ',28); insert into BM_GHKSTJ(ID0000,DYID00,GHRQ00,GHKS80) values(ad_ID0000,26,' 9、银行卡收入 ',29); --insert into BM_GHKSTJ(ID0000,DYID00,GHRQ00,GHKS80) values(ad_ID0000,28,'10、银行卡结算 ',30); insert into BM_GHKSTJ(ID0000,DYID00,GHRQ00,GHKS80) values(ad_ID0000,29,'11、医保结算 ',31); insert into BM_GHKSTJ(ID0000,DYID00,GHRQ00,GHKS80) values(ad_ID0000,49,'11.1、其中民政补助金额',31.1);--民政救助 if ls_SFXMYB='Y' then insert into BM_GHKSTJ(ID0000,DYID00,GHRQ00,GHKS80) values(ad_ID0000,50,'11.2、商业保险支付',31.2); end if; insert into BM_GHKSTJ(ID0000,DYID00,GHRQ00,GHKS80) values(ad_ID0000,30,'12、出院病人应收款',32); insert into BM_GHKSTJ(ID0000,DYID00,GHRQ00,GHKS80) values(ad_ID0000,31,'13、减免病人医药费',33); insert into BM_GHKSTJ(ID0000,DYID00,GHRQ00,GHKS80) values(ad_ID0000,32,'14、记帐应收款',34); insert into BM_GHKSTJ(ID0000,DYID00,GHRQ00,GHKS80) values(ad_ID0000,210,'49、挂账结清',34.05); insert into BM_GHKSTJ(ID0000,DYID00,GHRQ00,GHKS80) values(ad_ID0000,211,'50、挂账未结清',34.06); --其他支付方式：医院支付、微信支付、支付宝支付 insert into BM_GHKSTJ(ID0000,DYID00,GHRQ00,GHKS80) values(ad_ID0000,48,'48、医院支付',34.02);--医院支付 --insert into BM_GHKSTJ(ID0000,DYID00,GHRQ00,GHKS80) values(ad_ID0000,481,'48.1、医院支付_统筹',34.03);--医院支付_统筹 --insert into BM_GHKSTJ(ID0000,DYID00,GHRQ00,GHKS80) values(ad_ID0000,482,'48.2、医院支付_预交金',34.04);--医院支付_预交金 insert into BM_GHKSTJ(ID0000,DYID00,GHRQ00,GHKS80) values(ad_ID0000,124,'A、微信支付',34.10);--微信支付 insert into BM_GHKSTJ(ID0000,DYID00,GHRQ00,GHKS80) values(ad_ID0000,125,'B、支付宝支付',34.11);--支付宝 insert into BM_GHKSTJ(ID0000,DYID00,GHRQ00,GHKS80) values(ad_ID0000,126,'C、血费平台支付',34.12);--血费平台支付 insert into BM_GHKSTJ(ID0000,DYID00,GHRQ00,GHKS80) values(ad_ID0000,127,'D、聚合支付',34.13);--血费平台支付 --区域预交金，榕医通 if (LS_QYYJJPT='Y') then insert into BM_GHKSTJ(ID0000,DYID00,GHRQ00,GHKS80) values(ad_ID0000,130,'区域预交金合计',34.50);--区域预交金合计 insert into BM_GHKSTJ(ID0000,DYID00,GHRQ00,GHKS80) values(ad_ID0000,131,' 其中:(A)榕医通',34.51);--榕医通 end if; insert into BM_GHKSTJ(ID0000,DYID00,GHRQ00,GHKS80) values(ad_ID0000,34,'16、四舍五入',35); insert into BM_GHKSTJ(ID0000,DYID00,GHRQ00,GHKS80) values(ad_ID0000,51,'17、择日住院病例',35.1); insert into BM_GHKSTJ(ID0000,DYID00,GHRQ00,GHKS80) values(ad_ID0000,35,'19、实交现金',36); insert into BM_GHKSTJ(ID0000,DYID00,GHRQ00,GHKS80) values(ad_ID0000,36,'20、7-15合计',37); --ZYSF9-20220217-002 if (LS_SFZMJE = '1') then insert into BM_GHKSTJ(ID0000,DYID00,GHRQ00,GHKS80) values(ad_ID0000,280,'21、用血直免报销',38); end if; --ZYSF9-20230714-001 有设置腕带付收费员的 if nvl(LS_WDFCZY,' ')<>' ' or LS_WDFCZY is not null then insert into BM_GHKSTJ(ID0000,DYID00,GHRQ00,GHKS80) values(ad_ID0000,285,'22、腕带付退款',39); end if; open CUR_ZY_BRJFB0_SFY000; loop fetch CUR_ZY_BRJFB0_SFY000 into ls_YGBH00,ls_ZWXM00; exit when CUR_ZY_BRJFB0_SFY000%notfound; ls_ZPSR00 :=0; --支票收入 ls_YHKSR0 :=0; --银行卡收入 ls_YHKJS0 :=0; --银行卡结算 ls_YBJS00 :=0; --医保结算 ls_JMBRYF :=0; --减免病人医药费 ls_XMJE01 :=0; ls_XMJE02 :=0; ls_XMJE03 :=0; ls_XMJE04 :=0; ls_XMJE05 :=0; ls_XMJE06 :=0; ls_XMJE07 :=0; ls_XMJE08 :=0; ls_XMJE09 :=0; ls_XMJE10 :=0; ls_XMJE11 :=0; ls_XMJE12 :=0; ls_XMJE13 :=0; ls_XMJE14 :=0; ls_XMJE15 :=0; ls_XMJE15_1:=0; ls_XMJE16 :=0; ls_XMJE17 :=0; ls_XMJE18 :=0; ls_XMJE19 :=0; ls_XMJE20 :=0; ls_XMJE21 :=0; ls_XMJE22 :=0; ls_XMJE23 :=0; ls_XMJE24 :=0; ls_XMJE25 :=0; ls_XMJE26 :=0; ls_XMJE27 :=0; ls_XMJE28 :=0; ls_xmje35 :=0; ls_sswr00 :=0; ls_sswr01 :=0; ls_XMJE48 :=0; --医院支付金额 ls_XMJE49 :=0; ls_XMJE50 :=0; LS_YYZFSK :=0; --收医院支付票据金额 LS_YYZFSK_TC :=0; LS_YYZFTK :=0; --退医院支付票据金额 LS_YYZFTK_TC :=0; ls_ZRZYYJ :=0; --择日住院预交金转入 ls_MZBZJE1 :=0; --缴费表的民政补助金额 LS_WXZF00_SR:=0; --微信支付收入 LS_WXZF00_TK:=0; --微信支付退款 LS_WXZF00 :=0; --微信支付 LS_ZFBZF0_SR:=0; --支付宝支付收入 LS_ZFBZF0_TK:=0; --支付宝支付退款 LS_ZFBZF0 :=0; --支付宝支付 LS_YXZMJE := 0; --用血直免金额 ZYSF9-20220217-002 LS_WDFTKJE:=0; select BH0000 into ls_KSBH00 from BM_GHKSTJ_KSDY00 where ID0000 = ad_ID0000 and GHKSBH = ls_YGBH00; --==预交金 --Update 1.预交金收入 select nvl(sum(B.XJJE00),0),nvl(sum(B.ZZJE00),0),nvl(sum(YHKJE0),0), nvl(sum(DECODE(A.ZFFS00,8,B.YHKJE0,0)),0), ltrim(min(lpad(trim(B.pjxh00),30,' ')))||' - '||ltrim(max(lpad(trim(B.pjxh00),30,' '))),0 into ls_XMJE00,ls_ZPSR00,ls_YHKSR0,LS_YHSJJE,ls_BEIZH0,ls_ZRZYYJ from ZY_BRJFB0 A,ZY_PJSYQK B where A.PJH000 = B.PJH000 and B.PLBH00 in (4,8) and a.JSLX00 in ('0','1','2') and A.JFRQ00 >= as_KSRQ00 and A.JFRQ00 <= as_JSRQ00 and A.JFRQ00||A.JFSJ00 >= as_KSRQ00||as_KSSJ00 and A.JFRQ00||A.JFSJ00 <= as_JSRQ00||as_JSSJ00 and substr(B.PJXH00,1,1) <> '-' and A.JFCZY0 = ls_YGBH00 and B.PJZT00='0' and A.JZDH00 <> 1 --作废的预交金票据不计算在内! and exists (select 1 from BM_BMBM00 where BMBH00=A.CZYKS0 and (trim(YYID00)=ls_yyid00 or ls_yyid00='0')); ls_XMJE01 := nvl(ls_XMJE00,0)+nvl(ls_ZPSR00,0)+nvl(ls_YHKSR0,0); --1.1收预交金 select nvl(sum(B.XJJE00),0)+nvl(sum(B.ZZJE00),0)+nvl(sum(YHKJE0),0) into ls_XMJE35 from ZY_BRJFB0 A,ZY_PJSYQK B where A.PJH000 = B.PJH000 and B.PLBH00 in (4,8) and a.JSLX00 in ('0','1','2') and A.JFRQ00 >= as_KSRQ00 and A.JFRQ00 <= as_JSRQ00 and A.JFRQ00||A.JFSJ00 >= as_KSRQ00||as_KSSJ00 and A.JFRQ00||A.JFSJ00 <= as_JSRQ00||as_JSSJ00 and substr(B.PJXH00,1,1) <> '-' and A.JFBZ00='0' and B.PJZT00='0' and A.JFCZY0 = ls_YGBH00 and A.JZDH00 <> 1 --作废的预交金票据不计算在内! and exists (select 1 from BM_BMBM00 where BMBH00=A.CZYKS0 and (trim(YYID00)=ls_yyid00 or ls_yyid00='0')); --1.2退预交金 select nvl(sum(B.XJJE00),0)+nvl(sum(B.ZZJE00),0)+nvl(sum(YHKJE0),0) into ls_XMJE36 from ZY_BRJFB0 A,ZY_PJSYQK B where A.PJH000 = B.PJH000 and B.PLBH00 in (4,8) and a.JSLX00 in ('0','1','2') and A.JFRQ00 >= as_KSRQ00 and A.JFRQ00 <= as_JSRQ00 and A.JFRQ00||A.JFSJ00 >= as_KSRQ00||as_KSSJ00 and A.JFRQ00||A.JFSJ00 <= as_JSRQ00||as_JSSJ00 and substr(B.PJXH00,1,1) <> '-' and A.JFBZ00='1' and B.PJZT00='0' and A.JFCZY0 = ls_YGBH00 and A.JZDH00 <> 1 --作废的预交金票据不计算在内! and exists (select 1 from BM_BMBM00 where BMBH00=A.CZYKS0 and (trim(YYID00)=ls_yyid00 or ls_yyid00='0')); --收：微信和支付 select nvl(sum(decode(A.ZFFS00,24,A.JFJE00,0)),0)WXZF00,nvl(sum(decode(A.ZFFS00,25,A.JFJE00,0)),0)ZFBZF0 into LS_WXZF00_SR,LS_ZFBZF0_SR from ZY_BRJFB0 A,ZY_PJSYQK B where A.PJH000 = B.PJH000 and A.ZFFS00 in (24,25) and a.JSLX00 in ('0','1','2') and A.JFRQ00 >= as_KSRQ00 and A.JFRQ00 <= as_JSRQ00 and A.JFRQ00||A.JFSJ00 >= as_KSRQ00||as_KSSJ00 and A.JFRQ00||A.JFSJ00 <= as_JSRQ00||as_JSSJ00 and substr(B.PJXH00,1,1) <> '-' and A.JFBZ00='0' and B.PJZT00='0' and A.JFCZY0 = ls_YGBH00 and A.JZDH00 <> 1 --作废的预交金票据不计算在内! and exists (select 1 from BM_BMBM00 where BMBH00=A.CZYKS0 and (trim(YYID00)=ls_yyid00 or ls_yyid00='0')); --退：微信和支付 select nvl(sum(decode(A.ZFFS00,24,A.JFJE00,0)),0)WXZF00,nvl(sum(decode(A.ZFFS00,25,A.JFJE00,0)),0)ZFBZF0 into LS_WXZF00_TK,LS_ZFBZF0_TK from ZY_BRJFB0 A,ZY_PJSYQK B where A.PJH000 = B.FZPJH0 --2018.04.25 扣除应退款金额 and A.ZFFS00 in (24,25) and a.JSLX00 in ('0','1','2') and A.JFRQ00 >= as_KSRQ00 and A.JFRQ00 <= as_JSRQ00 and A.JFRQ00||A.JFSJ00 >= as_KSRQ00||as_KSSJ00 and A.JFRQ00||A.JFSJ00 <= as_JSRQ00||as_JSSJ00 and substr(B.PJXH00,1,1) <> '-' and A.JFBZ00='1' and B.PJZT00='0' and A.JFCZY0 = ls_YGBH00 and A.JZDH00 <> 1 --作废的预交金票据不计算在内! and exists (select 1 from BM_BMBM00 where BMBH00=A.CZYKS0 and (trim(YYID00)=ls_yyid00 or ls_yyid00='0')); --血费平台支付 select nvl(sum(A.JFJE00),0) into LS_XFPTZF from ZY_BRJFB0 A where A.ZFFS00 in (28) and A.ZFMXBH='XF' and A.JSLX00 in ('0','1','2') and A.JFRQ00 >= as_KSRQ00 and A.JFRQ00 <= as_JSRQ00 and A.JFRQ00||A.JFSJ00 >= as_KSRQ00||as_KSSJ00 and A.JFRQ00||A.JFSJ00 <= as_JSRQ00||as_JSSJ00 and A.JFCZY0 = ls_YGBH00 and A.JZDH00 <> 1 --作废的预交金票据不计算在内! and exists (select 1 from BM_BMBM00 where BMBH00=A.CZYKS0 and (trim(YYID00)=ls_yyid00 or ls_yyid00='0')); --腕带付退款金额 if (nvl(LS_WDFCZY,' ')<>' ' or LS_WDFCZY is not null) and instrb(','||LS_WDFCZY||',' , ','||ls_YGBH00||',')=0 then select nvl(sum(A.JFJE00),0) into LS_WDFTKJE from ZY_BRJFB0 A,ZY_PJSYQK B where A.PJH000 = B.PJH000 and a.JSLX00 in ('0','1','2') and exists (select 1 from BM_ZFRZ00 where DDLSH0=a.JYLSH0 and instrb(','||LS_WDFCZY||',' , ','||to_char(CZY000)||',')>0) and A.JFRQ00 >= as_KSRQ00 and A.JFRQ00 <= as_JSRQ00 and A.JFRQ00||A.JFSJ00 >= as_KSRQ00||as_KSSJ00 and A.JFRQ00||A.JFSJ00 <= as_JSRQ00||as_JSSJ00 and substr(B.PJXH00,1,1) <> '-' and A.JFBZ00='1' and B.PJZT00='0' and A.JFCZY0 = ls_YGBH00 and A.JZDH00 <> 1 --作废的预交金票据不计算在内! and exists (select 1 from BM_BMBM00 where BMBH00=A.CZYKS0 and (trim(YYID00)=ls_yyid00 or ls_yyid00='0')); end if; LS_WXZF00:=nvl(LS_WXZF00_SR,0)+nvl(LS_WXZF00_TK,0);--微信支付 LS_ZFBZF0:=nvl(LS_ZFBZF0_SR,0)+nvl(LS_ZFBZF0_TK,0);--支付宝 --平台支付榕医通 by ZYSF-20180410-004 /* ZYSF9-20220217-002 select sum(nvl(A.JFJE00,0)) QYPTZF,sum(decode(nvl(A.ZFMXBH,'A'),'A',A.JFJE00,0))RYTZF0,sum(decode(nvl(A.ZFMXBH,'A'),'A',0,A.JFJE00))JHZFJE into LS_QYPTZF,LS_RYTZF0,LS_JHZFJE */ select sum(nvl(A.JFJE00,0)) QYPTZF,sum(decode(nvl(A.ZFMXBH,'A'),'A',A.JFJE00,0)) RYTZF0, sum(decode(nvl(A.ZFMXBH,'A'),'B',A.JFJE00,0)) JHZFJE,sum(decode(A.ZFMXBH,'XF',A.JFJE00,0)) into LS_QYPTZF,LS_RYTZF0,LS_JHZFJE,LS_YXZMJE from ZY_BRJFB0 A,ZY_PJSYQK B where A.PJH000 = B.PJH000 and A.ZFFS00 in (28) and a.JSLX00 in ('0','1','2') and A.JFRQ00 >= as_KSRQ00 and A.JFRQ00 <= as_JSRQ00 and A.JFRQ00||A.JFSJ00 >= as_KSRQ00||as_KSSJ00 and A.JFRQ00||A.JFSJ00 <= as_JSRQ00||as_JSSJ00 and substr(B.PJXH00,1,1) <> '-' --and A.JFBZ00='0' and B.PJZT00='0' and A.JFCZY0 = ls_YGBH00 and A.JZDH00 <> 1 --作废的预交金票据不计算在内! and exists (select 1 from BM_BMBM00 where BMBH00=A.CZYKS0 and (trim(YYID00)=ls_yyid00 or ls_yyid00='0')); --2、住院票据 select nvl(sum(HJJE00),0),--住院票据 --nvl(sum(SBGRZH+SBTCJJ+decode(d.sfjsdw,'N',0,GRZHZF)+TCJJZF),0), --2.6医保结算 --sum(decode(trim(D.YBBHSB),'Y',nvl(decode(d.sfjsdw,'N',0,GRZHZF)+TCJJZF+GWYBZ0,0),nvl(SBGRZH+SBTCJJ+decode(d.sfjsdw,'N',0,GRZHZF)+TCJJZF+GWYBZ0,0))),--2.6医保结算 modify by xzw --nvl(sum(decode(d.sfjsdw,'N',0,GRZHZF)+TCJJZF+decode(sign(Instrb(','||trim(d.ybbhsb)||',',',15,')),1,0,GWYBZ0)+decode(sign(Instrb(','||trim(d.ybbhsb)||',',',14,')),1,0,SBGRZH+SBTCJJ) -- +nvl(a.YBQTZF,0)),0), nvl(sum(decode(d.sfjsdw,'N',0,GRZHZF)+TCJJZF+nvl(a.YBQTZF,0)),0), nvl(abs(sum(JZJE00)),0), --记账金额 --nvl(abs(sum(decode(a.FBBH00,3,0,JZJE00))),0), nvl(sum(GFJE00),0), --减免病人医药费 nvl(sum(a.CYYSK0),0), --出院病人应收款 nvl(sum(YBYL05),0), --医院支付金额 nvl(sum(A.BRZJE0-(A.TCJJZF+A.GRZHZF+A.ZFJE00+A.YBYL05+A.JZJE00)),0),--修正值 --nvl(sum(YBYL23),0), -- nvl(sum(nvl(YBYL01,0)+nvl(YBYL18,0)),0), --民政救助 --sum(nvl(YBYL18,0)), --民政救助 nvl(sum(MZBZJE),0), --民政救助 --2018.08.04 --sum(decode(sign(Instrb(','||trim(d.YBBHSB)||',',',18,')),1,0,nvl(A.YBYL18,0))),--民政救助 nvl(sum(SYBXZF),0), nvl(sum(A.YHTKJE),0), nvl(sum(A.WXTK00),0), nvl(sum(A.ZFBTK0),0), nvl(sum(least(0,a.ZFJE00+a.YBYL05)),0) into ls_XMJE02,ls_YBJS00,ls_JZJE00,ls_JMBRYF,ls_CYYSK0, LS_YYZFSK, LS_DBZ000, ls_MZBZJE, ls_SYBXZF, LS_YHTKJE_ZC, LS_WXKJE_ZC, LS_ZFBKJE_ZC, LS_YYZFSK_TC from ZY_JZB000 A,ZY_BRXXB0 C,IC_YBBRLB D where A.ZYID00=C.ZYID00 and A.YBLB00=D.YBLB00 and A.FBBH00=D.FBBH00 and A.PJH000 in (select FZPJH0 from ZY_PJSYQK where DYRQ00 >= as_KSRQ00 and DYRQ00 <= as_JSRQ00 and DYRQ00||DYSJ00 >= as_KSRQ00||as_KSSJ00 and DYRQ00||DYSJ00 <= as_JSRQ00||as_JSSJ00 and CZY000=ls_YGBH00 and BDBZ00 in ('0','8','9') and PLBH00=3 and substr(PJXH00,1,1)<>'-' and exists (select 1 from BM_BMBM00 where BMBH00=CZYKS0 and (trim(YYID00)=ls_yyid00 or ls_yyid00='0'))); --获取实交现金要减民政补助部分的,要从ZY_BRJFB0取 2017.03.30 select nvl(sum(JFJE00),0) into ls_MZBZJE1 from ZY_BRJFB0 a where JFRQ00>= as_KSRQ00 and JFRQ00 <= as_JSRQ00 and JFRQ00||JFSJ00 >= as_KSRQ00||as_KSSJ00 and JFRQ00||JFSJ00 <= as_JSRQ00||as_JSSJ00 and JFCZY0=ls_YGBH00 and ZFFS00 in(22) and a.JSLX00 in ('0','1','2') and A.JZDH00 <> 1; --2(2)冲原收据,或叫冲原发票 select nvl(abs(sum(a.ZFJE00+decode(d.sfjsdw,'N',a.GRZHZF,0)-a.CYYSK0/*+a.YBYL05-greatest(a.ZFJE00+a.YBYL05,0)*/)),0) into ls_XMJE04 from ZY_JZB000 A,ZY_BRXXB0 C,IC_YBBRLB D where A.ZYID00=C.ZYID00 and A.YBLB00=D.YBLB00 and A.FBBH00=D.FBBH00 and A.PJH000 in (select FZPJH0 from ZY_PJSYQK where DYRQ00 >= as_KSRQ00 and DYRQ00 <= as_JSRQ00 and DYRQ00||DYSJ00 >= as_KSRQ00||as_KSSJ00 and DYRQ00||DYSJ00 <= as_JSRQ00||as_JSSJ00 and CZY000=ls_YGBH00 and BDBZ00 in ('0','8','9') and PLBH00=3 and substr(PJXH00,1,1)='-' and exists (select 1 from BM_BMBM00 where BMBH00=CZYKS0 and (trim(YYID00)=ls_yyid00 or ls_yyid00='0'))); select ltrim(min(lpad(trim(B.pjxh00),30,' ')))||' - '||ltrim(max(lpad(trim(B.pjxh00),30,' '))) into ls_BEIZH1 from ZY_PJSYQK B where B.PLBH00=3 and B.CZY000 = ls_YGBH00 and B.DYRQ00 >= as_KSRQ00 and B.DYRQ00 <= as_JSRQ00 and B.DYRQ00||B.DYSJ00 >= as_KSRQ00||as_KSSJ00 and B.DYRQ00||B.DYSJ00 <= as_JSRQ00||as_JSSJ00 and substr(B.PJXH00,1,1) <> '-' and exists (select 1 from BM_BMBM00 where BMBH00=B.CZYKS0 and (trim(YYID00)=ls_yyid00 or ls_yyid00='0')); select ltrim(min(lpad(trim(B.pjxh00),30,' ')))||' - '||ltrim(max(lpad(trim(B.pjxh00),30,' '))) into ls_BEIZH2 from ZY_PJSYQK B where B.PLBH00=10 and B.CZY000 = ls_YGBH00 and B.DYRQ00 >= as_KSRQ00 and B.DYRQ00 <= as_JSRQ00 and B.DYRQ00||B.DYSJ00 >= as_KSRQ00||as_KSSJ00 and B.DYRQ00||B.DYSJ00 <= as_JSRQ00||as_JSSJ00 and substr(B.PJXH00,1,1) <> '-' and exists (select 1 from BM_BMBM00 where BMBH00=B.CZYKS0 and (trim(YYID00)=ls_yyid00 or ls_yyid00='0')); ls_BEIZH1:=ls_BEIZH1||','||ls_BEIZH2; --2(3)补交现金,支票金额 ls_XMJE05:=ls_XMJE05-ls_XMJE06; --2(1)冲预收款=冲住院费用 ,改为倒算 2.1=2-2.2-2.3-2.4-2.5-2.6-2.7-2.8-2.9-收医院支付票据金额(LS_YYZFSK) --2024.05.02 增加-LS_YYZFSK, 要把退款的医院支付加上，否则预交金收入+冲预收款+补交现金-预交金退出-医院支付=实交现金 不会成立 ls_XMJE03:=ls_XMJE02-ls_XMJE04-ls_XMJE05-ls_XMJE06-ls_YBJS00-ls_JZJE00-ls_JMBRYF-ls_CYYSK0-LS_YYZFSK; -- +LS_YYZFSK_TC;-- -LS_YYZFSK; --计算应退还中的金额四舍五入部分 select nvl(sum(jfje00),0) into ls_sswr01 from zy_brjfb0 A, zy_pjsyqk B where A.JFCZY0 = ls_YGBH00 and B.PLBH00 =3 and A.pjh000 = B.fzpjh0 and a.JSLX00 in ('0','1','2') and A.ZFFS00 = 5 and A.JFRQ00 >= as_KSRQ00 and A.JFRQ00 <= as_JSRQ00 and A.JFRQ00||A.JFSJ00 >= as_KSRQ00||as_KSSJ00 and A.JFRQ00||A.JFSJ00 <= as_JSRQ00||as_JSSJ00 and b.BDBZ00 in ('0','8','9') and exists (select 1 from BM_BMBM00 where BMBH00=A.CZYKS0 and (trim(YYID00)=ls_yyid00 or ls_yyid00='0')); --计算3、预交金退出金额 --计算3、预交金退出 --重新修改取值：计算3、预交金退出 ; for ZYSF9-20191108-004 select nvl(sum(HSJE00),0) into ls_XMJE12 from VW_ZY_YJJHS0 a where HSCZY0= ls_YGBH00 and HSRQ00 between as_KSRQ00 and as_JSRQ00 and HSRQ00||HSSJ00 between as_KSRQ00||as_KSSJ00 and as_JSRQ00||as_JSSJ00 and exists (select 1 from BM_BMBM00 where BMBH00=a.CZYKS0 and (trim(YYID00)=ls_yyid00 or ls_yyid00='0')); --计算3(3)应退还转账 select nvl(abs(sum(JFJE00)),0),nvl(sum(JFJE00),0) into ls_XMJE15,ls_XMJE15_1 from zy_brjfb0 A, zy_pjsyqk B where A.pjh000 = B.fzpjh0 and a.JSLX00 in ('0','1','2') and A.JFCZY0 = ls_YGBH00 and A.JFRQ00 >= as_KSRQ00 and A.JFRQ00 <= as_JSRQ00 and A.JFRQ00||A.JFSJ00 >= as_KSRQ00||as_KSSJ00 and A.JFRQ00||A.JFSJ00 <= as_JSRQ00||as_JSSJ00 and B.PLBH00 = 3 --预交金的部分不添加,否则冲住院费用相减时会不平 and A.ZFFS00 = 2 and substr(A.JFLBID,1,1)= 2 and B.PJZT00='0' and exists (select 1 from BM_BMBM00 where BMBH00=A.CZYKS0 and (trim(YYID00)=ls_yyid00 or ls_yyid00='0')); --3(4)计算现金暂存的 select nvl(sum(CYYTK0),0) into ls_XMJE16 from ZY_JZB000 A, ZY_PJSYQK B where A.PJH000 = B.FZPJH0 and B.PLBH00 = 3 and B.CZY000 = ls_YGBH00 and B.DYRQ00 >= as_KSRQ00 and B.DYRQ00 <= as_JSRQ00 and B.DYRQ00||B.DYSJ00 >= as_KSRQ00||as_KSSJ00 and B.DYRQ00||B.DYSJ00 <= as_JSRQ00||as_JSSJ00 --and substr(B.PJXH00,1,1) <> '-' and B.PJZT00= '0' and B.BDBZ00 in ('0','8','9') and exists (select 1 from BM_BMBM00 where BMBH00=B.CZYKS0 and (trim(YYID00)=ls_yyid00 or ls_yyid00='0')); --3(2)应退还现金,用倒算方法 = 退出金额 - 冲住院费用 - 转帐 - 现金暂存-应退还POS LS_YHTKJE_TEMP:=LS_YHTKJE; ls_XMJE14:=ls_XMJE12-ls_XMJE03-ls_XMJE16-ls_XMJE15-LS_YHTKJE_TEMP; ---收回旧收据(补打发票) select nvl(sum(xjje00+zzje00+yhkje0),0) into ls_XMJE10 from ZY_PJSYQK B where B.PLBH00 in(3,10) --and PJZT00='0' and B.CZY000 = ls_YGBH00 and substr(B.PJXH00,1,1) <> '-' and B.DYRQ00 >= as_KSRQ00 and B.DYRQ00 <= as_JSRQ00 and B.DYRQ00||B.DYSJ00 >= as_KSRQ00||as_KSSJ00 and B.DYRQ00||B.DYSJ00 <= as_JSRQ00||as_JSSJ00 and B.BDBZ00='1'---补打发票 and exists (select 1 from BM_BMBM00 where BMBH00=B.CZYKS0 and (trim(YYID00)=ls_yyid00 or ls_yyid00='0')); --6、退款票据 select abs(nvl(sum(HJJE00),0)),--退款收据 --abs(nvl(sum(decode(d.sfjsdw,'N',0,GRZHZF)+TCJJZF+decode(sign(Instrb(','||trim(d.ybbhsb)||',',',15,')),1,0,GWYBZ0)+decode(sign(Instrb(','||trim(d.ybbhsb)||',',',14,')),1,0,SBGRZH+SBTCJJ)+nvl(a.YBQTZF,0)),0)), abs(nvl(sum(decode(d.sfjsdw,'N',0,GRZHZF)+TCJJZF+nvl(a.YBQTZF,0)),0)), abs(nvl(sum(JZJE00),0)),--退记账款 --abs(nvl(sum(decode(a.FBBH00,3,0,JZJE00)),0)), abs(nvl(sum(GFJE00),0)),--退减免款 abs(nvl(sum(CYYSK0),0)),--退出院病人应收款 nvl(sum(YBYL05),0), --医院支付金额 nvl(sum(A.BRZJE0-(A.TCJJZF+A.GRZHZF+A.ZFJE00+A.YBYL05+A.JZJE00)),0),--修正值 sum(nvl(MZBZJE,0)), --民政救助 --2018.08.04 abs(nvl(sum(SYBXZF),0)), nvl(sum(A.YHTKJE),0), nvl(sum(A.WXTK00),0), nvl(sum(A.ZFBTK0),0), count(*), nvl(sum(greatest(0,a.ZFJE00+a.YBYL05)),0) into ls_XMJE19,ls_XMJE20,ls_TJZJE0,ls_XMJE25,ls_XMJE26, LS_YYZFTK, LS_DBZ001, ls_MZBZJETK, LS_SYBXZFTK, LS_YHTKJE_CX, LS_WXKJE_CX, LS_ZFBKJE_CX, ls_COUNT0, ls_YYZFTK_TC from ZY_JZB000 A,ZY_BRXXB0 C,IC_YBBRLB D where A.ZYID00=C.ZYID00 and A.YBLB00=D.YBLB00 and A.FBBH00=D.FBBH00 and A.PJH000 in (select FZPJH0 from ZY_PJSYQK where CZY000 = ls_YGBH00 and DYRQ00 >= as_KSRQ00 and DYRQ00 <= as_JSRQ00 and DYRQ00||DYSJ00 >= as_KSRQ00||as_KSSJ00 and DYRQ00||DYSJ00 <= as_JSRQ00||as_JSSJ00 and BDBZ00 in ('0','8','9') and PLBH00=3 and substr(PJXH00,1,1)='-' and exists (select 1 from BM_BMBM00 where BMBH00=CZYKS0 and (trim(YYID00)=ls_yyid00 or ls_yyid00='0')) ); --6(1)退非医保款 ls_XMJE19:=ls_XMJE19-ls_XMJE20-ls_TJZJE0-ls_XMJE25-ls_XMJE26; --医院支付金额=收医院支付票据金额+退医院支付票据金额 如果收为正，冲时会为负数，所以用+ LS_XMJE48:=nvl(LS_YYZFSK,0)+nvl(LS_YYZFTK,0); --民政救助 ls_XMJE49:=nvl(ls_MZBZJE,0)+nvl(ls_MZBZJETK,0); --应退pos LS_YHTKJE:=nvl(LS_YHTKJE_ZC,0)+nvl(LS_YHTKJE_CX,0); --应退微信 LS_WXKJE:=nvl(LS_WXKJE_ZC,0)+nvl(LS_WXKJE_CX,0); --应退支付宝 LS_ZFBKJE:=nvl(LS_ZFBKJE_ZC,0)+nvl(LS_ZFBKJE_CX,0); --预交金收入 ls_XMJE01:=nvl(ls_XMJE35,0) + nvl(ls_XMJE36,0); --结清挂账金额(结算) select nvl(sum(CYYSK0),0),nvl(sum(CYYTK0),0) into LS_GZJQJE_S,LS_GZJQJE_T from ZY_JZB000 where (ZYID00,JZDH00) in ( select a.ZYID00,a.JZDH00 from ZY_BRJFB0 A,ZY_PJSYQK B where A.PJH000 = B.PJH000 and B.PLBH00 in (4,8,20) and a.JSLX00 in ('0','1','2') and A.JFRQ00 >= as_KSRQ00 and A.JFRQ00 <= as_JSRQ00 and A.JFRQ00||A.JFSJ00 >= as_KSRQ00||as_KSSJ00 and A.JFRQ00||A.JFSJ00 <= as_JSRQ00||as_JSSJ00 --and A.JFBZ00='0' and B.PJZT00='0' and A.JFCZY0 = ls_YGBH00 and A.JZDH00 <> 1 --作废的预交金票据不计算在内! and A.JZDH00 > 0 and A.DJH000=(select max(DJH000) from ZY_BRJFB0 where ZYID00=A.ZYID00 and PLBH00 in (4,8,20) and JZDH00=A.JZDH00 and JFJE00<>0 and JFRQ00 between as_KSRQ00 and as_JSRQ00 and JFRQ00||JFSJ00 between as_KSRQ00||as_KSSJ00 and as_JSRQ00||as_JSSJ00) and exists (select 1 from BM_BMBM00 where BMBH00=A.CZYKS0 and (trim(YYID00)=ls_yyid00 or ls_yyid00='0')) and exists (select 1 from ZY_JZB000 where ZYID00=a.ZYID00 and JZDH00=a.JZDH00 and JSLX00='2' --挂账结算 and JZZT00<>'2' --非挂账结清状态 and GZJQRQ between as_KSRQ00 and as_JSRQ00 and GZJQRQ||GZJQSJ between as_KSRQ00||as_KSSJ00 and as_JSRQ00||as_JSSJ00)); --取消结算出院应收款(CYYSK0)、出院应退款(CYYTK0) select nvl(sum(CYYSK0),0),nvl(sum(CYYTK0),0) into LS_GZJQJE_S_CX,LS_GZJQJE_T_CX from ZY_JZB000 a where a.JZY000 = ls_YGBH00 and a.JZRQ00 between as_KSRQ00 and as_JSRQ00 and a.JZRQ00||a.JZSJ00 >= as_KSRQ00||as_KSSJ00 and a.JZRQ00||a.JZSJ00 <= as_JSRQ00||as_JSSJ00 and a.JSLX00 = '2' and a.HJJE00 < 0 and a.GZJQRQ is not null and exists (select 1 from BM_BMBM00 where BMBH00=A.CZYKS0 and (trim(YYID00)=ls_yyid00 or ls_yyid00='0')); LS_GZJQJE := LS_GZJQJE_S+LS_GZJQJE_S_CX-LS_GZJQJE_T-LS_GZJQJE_T_CX; --挂账未结清金额是否启用开始时间 Y.是 N.否 默认 N select nvl(max(trim(value0)),'N') into ZYSF_GZWJQJESFQYKSSJ from XT_XTCS00 where NAME00 ='ZYSF_GZWJQJESFQYKSSJ'; if ZYSF_GZWJQJESFQYKSSJ='Y' then --挂账未结清金额 select nvl(sum(a.CYYSK0),0),nvl(sum(a.CYYTK0),0) into LS_GZWJQ0_YSK,LS_GZWJQ0_YTK from ZY_JZB000 A where JZRQ00 between as_KSRQ00 and as_JSRQ00 and JZRQ00||JZSJ00 between as_KSRQ00||as_KSSJ00 and as_JSRQ00||as_JSSJ00 --a.JZRQ00 <= as_JSRQ00 --and a.JZRQ00||a.JZSJ00 <= as_JSRQ00||as_JSSJ00 and a.JSLX00='2' --and ( GZJQRQ is null -- or not (GZJQRQ between '20200701' and '20201231') -- or not (GZJQRQ||GZJQSJ between '20200701'||'00:00:00' and '20201231'||'23:59:59')) and (a.GZJQRQ is null or a.GZJQRQ > as_JSRQ00 or a.GZJQRQ||a.GZJQSJ > as_JSRQ00||as_JSSJ00) and a.JZY000= ls_YGBH00 and exists (select 1 from BM_BMBM00 where BMBH00=a.CZYKS0 and (trim(YYID00)=ls_yyid00 or ls_yyid00='0')) and nvl(CXJZDH,0)=0 and not exists(select 1 from ZY_JZB000 where ZYID00=a.ZYID00 and CXJZDH=a.JZDH00 and JZRQ00<=as_JSRQ00 and JZRQ00||JZSJ00<=as_JSRQ00||as_JSSJ00); else --挂账未结清金额 select nvl(sum(a.CYYSK0),0),nvl(sum(a.CYYTK0),0) into LS_GZWJQ0_YSK,LS_GZWJQ0_YTK from ZY_JZB000 A where --JZRQ00 between as_KSRQ00 and as_JSRQ00 --and JZRQ00||JZSJ00 between as_KSRQ00||as_KSSJ00 and as_JSRQ00||as_JSSJ00 a.JZRQ00 <= as_JSRQ00 and a.JZRQ00||a.JZSJ00 <= as_JSRQ00||as_JSSJ00 and a.JSLX00='2' --and ( GZJQRQ is null -- or not (GZJQRQ between '20200701' and '20201231') -- or not (GZJQRQ||GZJQSJ between '20200701'||'00:00:00' and '20201231'||'23:59:59')) and (a.GZJQRQ is null or a.GZJQRQ > as_JSRQ00 or a.GZJQRQ||a.GZJQSJ > as_JSRQ00||as_JSSJ00) and a.JZY000= ls_YGBH00 and exists (select 1 from BM_BMBM00 where BMBH00=a.CZYKS0 and (trim(YYID00)=ls_yyid00 or ls_yyid00='0')) and nvl(CXJZDH,0)=0 and not exists(select 1 from ZY_JZB000 where ZYID00=a.ZYID00 and CXJZDH=a.JZDH00 and JZRQ00<=as_JSRQ00 and JZRQ00||JZSJ00<=as_JSRQ00||as_JSSJ00); end if; LS_GZWJQ0 := LS_GZWJQ0_YSK-LS_GZWJQ0_YTK; --银行卡结算 --ls_TEXT00:='Update BM_GHKSTJ set GHKS'||ls_KSBH00||' =0 ' -- ||' where ID0000 ='|| to_char(ad_ID0000)||' and DYID00 in(7,28)';--银行卡结算 --SP_EXECUTE_SQL(ls_TEXT00); --记账应收款 ls_JZYSK0:=nvl(ls_JZJE00,0)-nvl(ls_TJZJE0,0); ls_TEXT00:='Update BM_GHKSTJ set GHKS'||ls_KSBH00||' = ' ||to_char(nvl(ls_XMJE35,0),'9999999990.99') ||' where ID0000 ='|| to_char(ad_ID0000)||' and DYID00 = 37';--收预交金 SP_EXECUTE_SQL(ls_TEXT00); ls_TEXT00:='Update BM_GHKSTJ set GHKS'||ls_KSBH00||' = ' ||to_char(nvl(ls_XMJE36,0),'9999999990.99') ||' where ID0000 ='|| to_char(ad_ID0000)||' and DYID00 = 38';--退预交金 SP_EXECUTE_SQL(ls_TEXT00); ls_TEXT00:='Update BM_GHKSTJ set GHKS'||ls_KSBH00||' = ' ||to_char(nvl(ls_XMJE01,0),'9999999990.99')||' ,BEIZHU =' ||''''||ls_BEIZH0||''''||' where ID0000 =' || to_char(ad_ID0000)||' and DYID00 = 1'; SP_EXECUTE_SQL(ls_TEXT00); ls_TEXT00:='Update BM_GHKSTJ set GHKS'||ls_KSBH00||' = ' ||to_char(nvl(ls_ZPSR00,0),'9999999990.99') ||' where ID0000 ='|| to_char(ad_ID0000)||' and DYID00 = 25'; SP_EXECUTE_SQL(ls_TEXT00); if ZY_YHTTKPOS='N' then --银行卡收入不扣除应退pos LS_YHTKJE_TEMP:=0; else --银行卡收入扣除应退还Pos LS_YHTKJE_TEMP:=LS_YHTKJE; end if; ls_YHKSR0:=nvl(ls_YHKSR0,0)-nvl(LS_YHTKJE_TEMP,0); LS_YHSJJE:=nvl(LS_YHSJJE,0)-nvl(LS_YHTKJE_TEMP,0); --2018.04.25 扣除应退款金额 --LS_YHSJJE:=nvl(LS_YHSJJE,0); ls_TEXT00:='Update BM_GHKSTJ set GHKS' ||ls_KSBH00||' = '||to_char(nvl(LS_YHSJJE,0),'9999999990.99') ||' where ID0000 ='|| to_char(ad_ID0000)||' and DYID00 = 26'; --银行卡收入(扣除掉微信和支付宝) SP_EXECUTE_SQL(ls_TEXT00); ls_TEXT00:='Update BM_GHKSTJ set GHKS'||ls_KSBH00 ||' = '||to_char(nvl(ls_YHKJS0,0),'9999999990.99') ||' where ID0000 ='||to_char (ad_ID0000)||' and DYID00 = 27'; SP_EXECUTE_SQL(ls_TEXT00); ls_TEXT00:='Update BM_GHKSTJ set GHKS'||LS_KSBH00||' = ' ||to_char(nvl(ls_YBJS00,0)-nvl(ls_XMJE20,0),'9999999990.99') ||' where ID0000 ='||to_char(ad_ID0000)||' and DYID00 = 29'; --医保结算 SP_EXECUTE_SQL(ls_TEXT00); ls_TEXT00:='Update BM_GHKSTJ set GHKS'||LS_KSBH00||' = ' ||to_char(nvl(ls_YBJS00,0),'9999999990.99') ||' where ID0000 ='||to_char(ad_ID0000)||' and DYID00 = 8'; --医保结算 SP_EXECUTE_SQL(ls_TEXT00); ls_TEXT00:='Update BM_GHKSTJ set GHKS'||LS_KSBH00 ||' = '||to_char(nvl(ls_XMJE02,0),'9999999990.99') ||',BEIZHU ='||''''|| ls_BEIZH1||'''' ||' where ID0000 ='||to_char(ad_ID0000)||' and DYID00 = 2'; --应收款收回 SP_EXECUTE_SQL(ls_TEXT00); ls_TEXT00:='Update BM_GHKSTJ set GHKS' ||LS_KSBH00||' = '||to_char(nvl(ls_XMJE03,0)+nvl(LS_YYZFTK,0)+LS_DBZ001,'9999999990.99') ||' where ID0000 ='||to_char(ad_ID0000)||' and DYID00 = 3'; --冲预收款 SP_EXECUTE_SQL(ls_TEXT00); ls_TEXT00:='Update BM_GHKSTJ set GHKS'||LS_KSBH00 ||' = '||to_char(nvl(ls_XMJE04,0)/*-nvl(LS_YYZFTK_TC,0)*/,'9999999990.99') ||' where ID0000 = '||to_char(ad_ID0000)||' and DYID00 = 4'; --冲原收据 SP_EXECUTE_SQL(ls_TEXT00); ls_TEXT00:='Update BM_GHKSTJ set GHKS' ||LS_KSBH00||' = '||to_char(nvl(ls_XMJE05,0),'9999999990.99') ||' where ID0000 = '||to_char(ad_ID0000)||' and DYID00 = 5'; --补交现金 SP_EXECUTE_SQL(ls_TEXT00); ls_TEXT00:='Update BM_GHKSTJ set GHKS'||LS_KSBH00 ||' = '||to_char(nvl(ls_XMJE06,0),'9999999990.99') ||' where ID0000 = '||to_char(ad_ID0000)||' and DYID00 = 6'; --补交支票 SP_EXECUTE_SQL(ls_TEXT00); ls_TEXT00:='Update BM_GHKSTJ set GHKS' ||LS_KSBH00||' = '||to_char(nvl(LS_JZJE00,0),'9999999990.99') ||' where ID0000 = '||to_char(ad_ID0000)||' and DYID00 = 12'; --记账金额 SP_EXECUTE_SQL(ls_TEXT00); ls_TEXT00:='Update BM_GHKSTJ set GHKS' ||LS_KSBH00||' = '||to_char(nvl(ls_JMBRYF,0),'9999999990.99') ||' where ID0000 ='||to_char(ad_ID0000)||' and DYID00 = 11'; --特殊结算 SP_EXECUTE_SQL(ls_TEXT00); ls_TEXT00:='Update BM_GHKSTJ set GHKS' ||LS_KSBH00||' = '||to_char(nvl(ls_cyysk0,0),'9999999990.99') ||' where ID0000 ='||to_char(ad_ID0000)||' and DYID00 = 9'; --出院病人应收款 SP_EXECUTE_SQL(ls_TEXT00); ls_TEXT00:='Update BM_GHKSTJ set GHKS' ||LS_KSBH00||' = '||to_char(nvl(LS_YYZFSK,0),'9999999990.99') ||' where ID0000 ='||to_char(ad_ID0000)||' and DYID00 = 140'; --医院支付 --2024.05.02 -LS_YYZFTK -->LS_YYZFSK SP_EXECUTE_SQL(ls_TEXT00); ls_TEXT00:='Update BM_GHKSTJ set GHKS' ||LS_KSBH00||' = '||to_char(nvl(ls_XMJE12,0),'9999999990.99') ||' where ID0000 = '||to_char(ad_ID0000)||' and DYID00 = 13'; --预交金退出 SP_EXECUTE_SQL(ls_TEXT00); ls_TEXT00:='Update BM_GHKSTJ set GHKS' ||LS_KSBH00||' = '||to_char(nvl(ls_XMJE03,0),'9999999990.99') ||' where ID0000 = '||to_char(ad_ID0000)||' and DYID00 = 14'; --冲住院费用 = 冲预收款 SP_EXECUTE_SQL(ls_TEXT00); ls_TEXT00:='Update BM_GHKSTJ set GHKS'||LS_KSBH00 ||' = '||to_char(nvl(ls_XMJE14,0),'9999999990.99') ||' where ID0000 = '||to_char(ad_ID0000)||' and DYID00 = 15'; --计算应退还现金 SP_EXECUTE_SQL(ls_TEXT00); ls_TEXT00:='Update BM_GHKSTJ set GHKS' ||LS_KSBH00||' = '||to_char(nvl(ls_sswr00,0)+nvl(ls_sswr01,0),'9999999990.99') ||' where ID0000 = '||to_char(ad_ID0000)||' and DYID00 = 34'; --计算应退还四舍五入金额 SP_EXECUTE_SQL(ls_TEXT00); ls_TEXT00:='Update BM_GHKSTJ set GHKS'||LS_KSBH00||' = ' ||to_char(nvl(ls_XMJE15,0),'9999999990.99')||' where ID0000 = ' ||to_char(ad_ID0000)||' and DYID00 = 16'; --计算应退还转账 SP_EXECUTE_SQL(ls_TEXT00); ls_TEXT00:='Update BM_GHKSTJ set GHKS' ||LS_KSBH00||' = '||to_char(nvl(ls_XMJE16,0),'9999999990.99') ||' where ID0000 = '||to_char(ad_ID0000)||' and DYID00 = 17'; --现金暂存 SP_EXECUTE_SQL(ls_TEXT00); --应退还Pos DYID00=177 ls_TEXT00:='Update BM_GHKSTJ set GHKS' ||LS_KSBH00||' = '||trim(to_char(LS_YHTKJE,'999999990.00')) ||' where ID0000 = '||to_char(ad_ID0000)||' and DYID00 = 177'; --应退还Pos SP_EXECUTE_SQL(ls_TEXT00); --应退还微信 DYID00=178 ls_TEXT00:='Update BM_GHKSTJ set GHKS' ||LS_KSBH00||' = '||trim(to_char(LS_WXKJE,'999999990.00')) ||' where ID0000 = '||to_char(ad_ID0000)||' and DYID00 = 178'; --应退还微信 SP_EXECUTE_SQL(ls_TEXT00); --应退还支付宝 DYID00=179 ls_TEXT00:='Update BM_GHKSTJ set GHKS' ||LS_KSBH00||' = '||trim(to_char(LS_ZFBKJE,'999999990.00')) ||' where ID0000 = '||to_char(ad_ID0000)||' and DYID00 = 179'; --应退还支付宝 SP_EXECUTE_SQL(ls_TEXT00); if ZY_YHTTKPOS='N' then --应退还现金不扣除应退pos LS_YHTKJE_TEMP:=0; else --应退还现金要扣掉应退还pos LS_YHTKJE_TEMP:=LS_YHTKJE; end if; ls_TEXT00:='Update BM_GHKSTJ set GHKS'||LS_KSBH00 ||' = '||to_char(nvl(ls_XMJE12,0)-nvl(ls_XMJE03,0) -nvl(ls_XMJE15,0)-nvl(ls_XMJE16,0)-nvl(LS_YHTKJE_TEMP,0),'9999999990.99')--+nvl(ls_sswr01,0) ||' where ID0000 ='||to_char(ad_ID0000)||' and DYID00 = 15'; --重新计算应退还现金,发现预交金退款时有问题 SP_EXECUTE_SQL(ls_TEXT00); ls_TEXT00:='Update BM_GHKSTJ set GHKS' ||LS_KSBH00||' = nvl(GHKS'||LS_KSBH00 ||',0)+'||to_char(nvl(ls_XMJE10,0),'9999999990.99') ||' where ID0000 ='||to_char( ad_ID0000)||' and DYID00 = 19'; --收回旧收据 SP_EXECUTE_SQL(ls_TEXT00); ls_TEXT00:='Update BM_GHKSTJ set GHKS'||LS_KSBH00 ||' = '||to_char(nvl(ls_XMJE10,0),'9999999990.99') ||' where ID0000 ='||to_char(ad_ID0000)||' and DYID00 = 10'; --补开新收据 SP_EXECUTE_SQL(ls_TEXT00); --退非医保款=退非医保款+(退医院支付票据金额)LS_YYZFTK ls_TEXT00:='Update BM_GHKSTJ set GHKS' ||LS_KSBH00||' = '||to_char(nvl(ls_XMJE19,0)+0 /*+nvl(LS_YYZFTK,0))*/,'9999999990.99') ||' where ID0000 = '||to_char(ad_ID0000)||' and DYID00 = 21'; --退非医保款 SP_EXECUTE_SQL(ls_TEXT00); ls_TEXT00:='Update BM_GHKSTJ set GHKS' ||LS_KSBH00||' = '||to_char(nvl(ls_XMJE20,0),'9999999990.99') ||' where ID0000 = '||to_char(ad_ID0000)||' and DYID00 = 22'; --退医保款 SP_EXECUTE_SQL(ls_TEXT00); ls_TEXT00:='Update BM_GHKSTJ set GHKS' ||LS_KSBH00||' = '||to_char(nvl(ls_TJZJE0,0),'9999999990.99') ||' where ID0000 = '||to_char(ad_ID0000)||' and DYID00 = 23'; --退记账款 SP_EXECUTE_SQL(ls_TEXT00); ls_TEXT00:='Update BM_GHKSTJ set GHKS'||LS_KSBH00||' = ' ||to_char(nvl(ls_xmje25,0),'9999999990.99') ||' where ID0000 = '||to_char(ad_ID0000)||' and DYID00 = 39'; --退减免医药费 SP_EXECUTE_SQL(ls_TEXT00); ls_TEXT00:='Update BM_GHKSTJ set GHKS'||LS_KSBH00||' = ' ||to_char(nvl(ls_XMJE26,0),'9999999990.99') ||' where ID0000 = '||to_char(ad_ID0000)||' and DYID00 = 40'; --退出院病人应收款 SP_EXECUTE_SQL(ls_TEXT00); ls_TEXT00:='Update BM_GHKSTJ set GHKS'||LS_KSBH00||' = ' ||to_char(nvl(ls_XMJE19,0)+nvl(ls_XMJE20,0) +nvl(ls_TJZJE0,0)+nvl(ls_xmje25,0)+nvl(ls_xmje26,0),'9999999990.99') ||' where ID0000 ='||to_char(ad_ID0000)||' and DYID00 = 20'; --退款小计 SP_EXECUTE_SQL(ls_TEXT00); ls_TEXT00:='Update BM_GHKSTJ set GHKS'||LS_KSBH00 ||' = '||to_char(nvl(ls_JZYSK0,0),'9999999990.99') ||' where ID0000 ='||to_char(ad_ID0000)||' and DYID00 = 32'; --应收记账款 SP_EXECUTE_SQL(ls_TEXT00); --医院支付金额 ls_TEXT00:='Update BM_GHKSTJ set GHKS'||LS_KSBH00 ||' = '||to_char(nvl(ls_XMJE48,0),'9999999990.99') ||' where ID0000 ='||to_char(ad_ID0000)||' and DYID00 = 48'; --医院支付金额 SP_EXECUTE_SQL(ls_TEXT00); --ls_TEXT00:='Update BM_GHKSTJ set GHKS'||LS_KSBH00 -- ||' = '||to_char(nvl(ls_YYZFSK_TC,0)+nvl(ls_YYZFTK_TC,0)) -- ||' where ID0000 ='||to_char(ad_ID0000)||' and DYID00 = 481'; --医院支付金额_统筹 --SP_EXECUTE_SQL(ls_TEXT00); --ls_TEXT00:='Update BM_GHKSTJ set GHKS'||LS_KSBH00 -- ||' = '||to_char(nvl(ls_XMJE48,0)-nvl(ls_YYZFSK_TC,0)-nvl(ls_YYZFTK_TC,0)) -- ||' where ID0000 ='||to_char(ad_ID0000)||' and DYID00 = 482'; --医院支付金额_预交金 --SP_EXECUTE_SQL(ls_TEXT00); --微信支付 BM_GHKSTJ.DYID00=124 LS_TEXT00:='update BM_GHKSTJ set GHKS'||LS_KSBH00 ||' = '||to_char(nvl(LS_WXZF00,0),'9999999990.99') ||' where ID0000 ='||to_char(ad_ID0000)||' and DYID00 = 124'; --微信支付 SP_EXECUTE_SQL(ls_TEXT00); --支付宝支付 BM_GHKSTJ.DYID00=125 LS_TEXT00:='update BM_GHKSTJ set GHKS'||LS_KSBH00 ||' = '||to_char(nvl(LS_ZFBZF0,0),'9999999990.99') ||' where ID0000 ='||to_char(ad_ID0000)||' and DYID00 = 125'; --支付宝支付 SP_EXECUTE_SQL(ls_TEXT00); --血费平台支付 BM_GHKSTJ.DYID00=126 LS_TEXT00:='update BM_GHKSTJ set GHKS'||LS_KSBH00 ||' = '||to_char(nvl(LS_XFPTZF,0),'9999999990.99') ||' where ID0000 ='||to_char(ad_ID0000)||' and DYID00 = 126'; --血费平台支付 SP_EXECUTE_SQL(ls_TEXT00); --聚合支付 BM_GHKSTJ.DYID00=127 LS_TEXT00:='update BM_GHKSTJ set GHKS'||LS_KSBH00 ||' = '||to_char(nvl(LS_JHZFJE,0),'9999999990.99') ||' where ID0000 ='||to_char(ad_ID0000)||' and DYID00 = 127'; --聚合支付 SP_EXECUTE_SQL(ls_TEXT00); --挂账结清 BM_GHKSTJ.DYID00=210 LS_TEXT00:='update BM_GHKSTJ set GHKS'||LS_KSBH00 ||' = '||to_char(nvl(LS_GZJQJE,0),'9999999990.99') ||' where ID0000 ='||to_char(ad_ID0000)||' and DYID00 = 210'; --挂账结清 SP_EXECUTE_SQL(ls_TEXT00); --挂账未结清 BM_GHKSTJ.DYID00=211 LS_TEXT00:='update BM_GHKSTJ set GHKS'||LS_KSBH00 ||' = '||to_char(nvl(LS_GZWJQ0,0),'9999999990.99') ||' where ID0000 ='||to_char(ad_ID0000)||' and DYID00 = 211'; --挂账未结清 SP_EXECUTE_SQL(ls_TEXT00); --平台支付榕医通 by ZYSF-20180410-004 LS_TEXT00:='update BM_GHKSTJ set GHKS'||LS_KSBH00 ||' = '||trim(to_char(nvl(LS_QYPTZF,0),'99999999990.00')) ||' where ID0000 ='||to_char(ad_ID0000)||' and DYID00 = 130'; --区域预交金合计 SP_EXECUTE_SQL(ls_TEXT00); LS_TEXT00:='update BM_GHKSTJ set GHKS'||LS_KSBH00 ||' = '||trim(to_char(nvl(LS_RYTZF0,0),'99999999990.00')) ||' where ID0000 ='||to_char(ad_ID0000)||' and DYID00 = 131'; --榕医通 SP_EXECUTE_SQL(ls_TEXT00); --用血直免报销金额 ZYSF9-20220217-002 if (LS_SFZMJE = '1') then LS_TEXT00:='update BM_GHKSTJ set GHKS'||LS_KSBH00 ||' = '||trim(to_char(nvl(LS_YXZMJE,0),'99999999990.00')) ||' where ID0000 ='||to_char(ad_ID0000)||' and DYID00 = 280'; SP_EXECUTE_SQL(ls_TEXT00); end if; --腕带付退款金额 if nvl(LS_WDFCZY,' ')<>' ' or LS_WDFCZY is not null then LS_TEXT00:='update BM_GHKSTJ set GHKS'||LS_KSBH00 ||' = '||trim(to_char(nvl(LS_WDFTKJE,0),'99999999990.00')) ||' where ID0000 ='||to_char(ad_ID0000)||' and DYID00 = 285'; SP_EXECUTE_SQL(ls_TEXT00); end if; --择日住院预交金转入 ls_TEXT00:='Update BM_GHKSTJ set GHKS'||LS_KSBH00 ||' = '||to_char(nvl(ls_ZRZYYJ,0),'9999999990.99') ||' where ID0000 ='||to_char(ad_ID0000)||' and DYID00 = 51'; --择日住院预交金转入 SP_EXECUTE_SQL(ls_TEXT00); --民政救助 ls_TEXT00:='Update BM_GHKSTJ set GHKS'||LS_KSBH00 ||' = '||to_char(nvl(ls_XMJE49,0),'9999999990.99') ||' where ID0000 ='||to_char(ad_ID0000)||' and DYID00 = 49'; --民政救助 SP_EXECUTE_SQL(ls_TEXT00); if ls_SFXMYB='Y' then ls_XMJE50:=nvl(ls_SYBXZF,0)-nvl(ls_SYBXZFTK,0); ls_TEXT00:='Update BM_GHKSTJ set GHKS'||LS_KSBH00 ||' = '||to_char(ls_XMJE50,'9999999990.99') ||' where ID0000 ='||to_char(ad_ID0000)||' and DYID00 = 50'; --商业保险支付 SP_EXECUTE_SQL(ls_TEXT00); end if; ls_TEXT00:='Update BM_GHKSTJ set GHKS'||LS_KSBH00||' = ' ||to_char(nvl(ls_JMBRYF-ls_XMJE25,0)+0,'9999999990.99') ||' where ID0000 ='||to_char(ad_ID0000)||' and DYID00 = 31'; --减免病人医药费 SP_EXECUTE_SQL(ls_TEXT00); ls_TEXT00:='Update BM_GHKSTJ set GHKS'||LS_KSBH00||' = ' ||to_char(nvl(ls_cyysk0-ls_XMJE26,0)+0,'9999999990.99') ||' where ID0000 ='||to_char(ad_ID0000)||' and DYID00 = 30'; --出院病人应收款 SP_EXECUTE_SQL(ls_TEXT00); ls_XMJE21:=nvl(ls_XMJE01,0)+nvl(ls_XMJE02,0);--+nvl(ls_XMJE15,0); ls_XMJE34:=nvl(ls_XMJE12,0)+nvl(ls_XMJE19,0) +nvl(ls_XMJE20,0)+nvl(ls_TJZJE0,0) +nvl(ls_xmje25,0)+nvl(ls_xmje26,0); --应缴金额=应缴金额-nvl(ls_XMJE15_1,0) by ZYSF-20161108-001 (同缴款表保存一致) ls_TEXT00:='Update BM_GHKSTJ set GHKS'||LS_KSBH00||' = ' ||to_char(nvl(ls_XMJE21,0)-nvl(ls_XMJE34,0)+nvl(ls_XMJE16,0)-nvl(ls_XMJE15_1,0),'9999999990.99') --ls_XMJE16暂存现金(余钱逃跑病人) ||' where ID0000 ='||to_char( ad_ID0000) ||' and DYID00 = 24'; --应缴金额 SP_EXECUTE_SQL(ls_TEXT00); --7-15合计=7-15合计-nvl(ls_XMJE15_1,0) by ZYSF-20161108-001 (同缴款表保存一致) ls_TEXT00:='Update BM_GHKSTJ set GHKS'||LS_KSBH00||' = ' ||to_char(nvl(ls_XMJE21,0)-nvl(ls_XMJE34,0)+nvl(ls_XMJE16,0)-nvl(ls_XMJE15_1,0),'9999999990.99') --ls_XMJE16暂存现金(余钱逃跑病人) ||' where ID0000 ='||to_char(ad_ID0000) ||' and DYID00 = 36'; --20、7-15合计 SP_EXECUTE_SQL(ls_TEXT00); if ZY_YHTTKPOS='N' then --银行卡扣除掉应退POs 所以实交现金应加上应退POS LS_YHTKJE_TEMP:=LS_YHTKJE; else LS_YHTKJE_TEMP:=0; end if; --实交现金=实交现金-nvl(ls_XMJE15_1,0) by ZYSF-20161108-001 (同缴款表保存一致) ls_TEXT00:='Update BM_GHKSTJ set GHKS' ||LS_KSBH00||' = ' ||to_char( nvl(ls_XMJE21,0) -nvl(ls_XMJE34,0)+nvl(ls_XMJE16,0)--ls_XMJE16暂存现金(余钱逃跑病人) -nvl(ls_ZPSR00,0) -nvl(ls_XMJE15_1,0) -nvl(ls_YHKSR0,0) -- -(nvl(ls_YHKSR0,0)-nvl(LS_WXKJE,0)-nvl(LS_ZFBKJE,0)) +nvl(LS_YHTKJE_TEMP,0) -nvl(ls_YHKJS0,0) -nvl(ls_YBJS00,0) +nvl(ls_XMJE20,0) -nvl(ls_JMBRYF,0) +nvl(ls_XMJE25,0) -nvl(Ls_JZYSK0,0) -nvl(ls_CYYSK0,0) +nvl(ls_xmje26,0) -nvl(ls_sswr01,0) -nvl(ls_XMJE48,0) --医院支付金额 -nvl(ls_XMJE50,0) -nvl(ls_ZRZYYJ,0) -nvl(ls_MZBZJE1,0) --11.1、其中民政补助金额 +nvl(LS_GZJQJE,0) --加上挂账结清金额 -nvl(LS_XFPTZF,0) --扣除血费平台支付 ,'9999999990.99') ||' where ID0000 ='||to_char(ad_ID0000) ||' and DYID00 = 35'; --实交现金 SP_EXECUTE_SQL(ls_TEXT00); end loop; close CUR_ZY_BRJFB0_SFY000; update BM_GHKSTJ set XMHJ00 = nvl(GHKS01,0)+ nvl(GHKS02,0)+ nvl(GHKS03,0)+ nvl(GHKS04,0)+ nvl(GHKS05,0)+ nvl(GHKS06,0)+ nvl(GHKS07,0)+ nvl(GHKS08,0)+ nvl(GHKS09,0)+ nvl(GHKS10,0) + nvl(GHKS11,0)+ nvl(GHKS12,0)+ nvl(GHKS13,0)+ nvl(GHKS14,0)+ nvl(GHKS15,0)+ nvl(GHKS16,0)+ nvl(GHKS17,0)+ nvl(GHKS18,0)+ nvl(GHKS19,0)+ nvl(GHKS20,0) + nvl(GHKS21,0)+ nvl(GHKS22,0)+ nvl(GHKS23,0)+ nvl(GHKS24,0)+ nvl(GHKS25,0)+ nvl(GHKS26,0)+ nvl(GHKS27,0)+ nvl(GHKS28,0)+ nvl(GHKS29,0)+ nvl(GHKS30,0) + nvl(GHKS31,0)+ nvl(GHKS32,0)+ nvl(GHKS33,0)+ nvl(GHKS34,0)+ nvl(GHKS35,0)+ nvl(GHKS36,0)+ nvl(GHKS37,0)+ nvl(GHKS38,0)+ nvl(GHKS39,0)+ nvl(GHKS40,0) + nvl(GHKS41,0)+ nvl(GHKS42,0)+ nvl(GHKS43,0)+ nvl(GHKS44,0)+ nvl(GHKS45,0)+ nvl(GHKS46,0)+ nvl(GHKS47,0)+ nvl(GHKS48,0)+ nvl(GHKS49,0)+ nvl(GHKS50,0) + nvl(GHKS51,0)+ nvl(GHKS52,0)+ nvl(GHKS53,0)+ nvl(GHKS54,0)+ nvl(GHKS55,0)+ nvl(GHKS56,0)+ nvl(GHKS57,0)+ nvl(GHKS58,0)+ nvl(GHKS59,0)+ nvl(GHKS60,0) + nvl(GHKS61,0)+ nvl(GHKS62,0)+ nvl(GHKS63,0)+ nvl(GHKS64,0)+ nvl(GHKS65,0)+ nvl(GHKS66,0)+ nvl(GHKS67,0)+ nvl(GHKS68,0)+ nvl(GHKS69,0)+ nvl(GHKS70,0) + nvl(GHKS71,0)+ nvl(GHKS72,0)+ nvl(GHKS73,0)+ nvl(GHKS74,0)+ nvl(GHKS75,0)+ nvl(GHKS76,0)+ nvl(GHKS77,0)+ nvl(GHKS78,0)+ nvl(GHKS79,0) where ID0000 = ad_ID0000; if as_TJFS00<>0 then --BY ZYSF-20120419-001 本合计值在前台不显示 只是过滤非零的收费员 insert into BM_GHKSTJ(ID0000,GHRQ00,GHKS01,GHKS02,GHKS03,GHKS04,GHKS05,GHKS06,GHKS07,GHKS08,GHKS09,GHKS10, GHKS11,GHKS12,GHKS13,GHKS14,GHKS15,GHKS16,GHKS17,GHKS18,GHKS19,GHKS20, GHKS21,GHKS22,GHKS23,GHKS24,GHKS25,GHKS26,GHKS27,GHKS28,GHKS29,GHKS30, GHKS31,GHKS32,GHKS33,GHKS34,GHKS35,GHKS36,GHKS37,GHKS38,GHKS39,GHKS40, GHKS41,GHKS42,GHKS43,GHKS44,GHKS45,GHKS46,GHKS47,GHKS48,GHKS49,GHKS50, GHKS51,GHKS52,GHKS53,GHKS54,GHKS55,GHKS56,GHKS57,GHKS58,GHKS59,GHKS60, GHKS61,GHKS62,GHKS63,GHKS64,GHKS65,GHKS66,GHKS67,GHKS68,GHKS69,GHKS70, GHKS71,GHKS72,GHKS73,GHKS74,GHKS75,GHKS76,GHKS77,GHKS78,GHKS79, XMHJ00,DYID00) select ad_ID0000,'999999',sum(GHKS01),sum(GHKS02),sum(GHKS03),sum(GHKS04),sum(GHKS05),sum(GHKS06),sum(GHKS07),sum(GHKS08),sum(GHKS09),sum(GHKS10), sum(GHKS11),sum(GHKS12),sum(GHKS13),sum(GHKS14),sum(GHKS15),sum(GHKS16),sum(GHKS17),sum(GHKS18),sum(GHKS19),sum(GHKS20), sum(GHKS21),sum(GHKS22),sum(GHKS23),sum(GHKS24),sum(GHKS25),sum(GHKS26),sum(GHKS27),sum(GHKS28),sum(GHKS29),sum(GHKS30), sum(GHKS31),sum(GHKS32),sum(GHKS33),sum(GHKS34),sum(GHKS35),sum(GHKS36),sum(GHKS37),sum(GHKS38),sum(GHKS39),sum(GHKS40), sum(GHKS41),sum(GHKS42),sum(GHKS43),sum(GHKS44),sum(GHKS45),sum(GHKS46),sum(GHKS47),sum(GHKS48),sum(GHKS49),sum(GHKS50), sum(GHKS51),sum(GHKS52),sum(GHKS53),sum(GHKS54),sum(GHKS55),sum(GHKS56),sum(GHKS57),sum(GHKS58),sum(GHKS59),sum(GHKS60), sum(GHKS61),sum(GHKS62),sum(GHKS63),sum(GHKS64),sum(GHKS65),sum(GHKS66),sum(GHKS67),sum(GHKS68),sum(GHKS69),sum(GHKS70), sum(GHKS71),sum(GHKS72),sum(GHKS73),sum(GHKS74),sum(GHKS75),sum(GHKS76),sum(GHKS77),sum(GHKS78),sum(GHKS79), sum(XMHJ00),0 from BM_GHKSTJ where ID0000 = ad_ID0000; end if; --insert into BM_GHKSTJ_0502 select * from BM_GHKSTJ where id0000=ad_ID0000; COMMIT; exception when others then as_YHMSG0:='数据库错误：出错原因未知！'; as_SYSMSG:=substr(sqlerrm,1,150); rollback; end;