prompt 28.2、门诊收费员缴款(对有执行预交金的医院)登记 SP_SF_JKB000_REG000_YJ create or replace procedure SP_SF_JKB000_REG000_YJ ( as_JSRQ00 in char, --缴款结束日期 as_JSSJ00 in char, --缴款结束时间 ad_CZY000 in number, --操作员编码 as_CZYXM0 in char, --操作员姓名（参数无用） as_SFY000 in char, --收费员姓名_胸卡号 as_YJJXH0 in char, --预交金票据的外部流水号（参数无用） as_YHMSG0 out varchar, --存储过程提示的错误信息 as_SYSMSG out varchar, --系统提示的错误信息 as_JKDH00 out varchar, --缴款单号 ad_YYID00 in varchar default '0', --医院ID ad_JKQSSJ in varchar default null --收费员缴款起始日期时间，格式2021101400:00:00 ) -- MODIFICATION HISTORY -- Person Date Comments -- zhanghr 2012.11.03 增加缴非收费员的电子钱包款已经多分院缴款的功能 -- qks 2015.01.14 SF_BRJFB0.ZFFS00=18,民生通支付,PLBH00为空 -- qks 2015.02.13 民生通金额也要体现在BM_JKB000.HJJE00 -- jlg 2016.11.11 处理5->9升级的时候缴款报错的问题 MZSF9-20161111-002 -- jlg 2017.06.02 处理预交金退款票据的信息 MZSF9-20170523-002 -- qks 2017.06.26 增加微信支付(ZFFSBH=24)、支付宝支付(ZFFSBH=25); for MZSF9-20170328-001 -- liwm 2018.06.07 门诊收费存储过程统一添加调用参数日志 for MZSF9-20180604-003 -- linshu 2018.06.15 增加榕医通平台支付(ZFFSBH=28) for MZSF9-20180604-004 -- qks 2018.06.28 修正SF_PJSYQK.PJLYID为空无法缴款问题; for MZSF9-20180628-001 -- qks 2018.08.20 对SF_JZB000.PJH000=0记录也要进行缴款; for MZSF9-20180822-002 -- pwt 2018.12.03 增加ad_YYID00,分院时的缴款; for MZSF9-20181127-003 -- liwm 2019.04.16 增加床位预交金缴款单处理 MZSF9-20190404-003 -- linzt 2020.01.08 自动缴款参数SF_ZJJQYTYJK='Y'时自动缴款期间限制收费员操作 MZSF9-20191227-001 -- jlg 2020.03.06 增加BM_JKB000.PJQSH0与PJZZH0的SF_PJSYQK.WBPJXH的写入处理 MZSF9-20200306-001 -- linzt 2020.04.07 增加BM_JKB000.YYZFJE与BM_JKKJMX表ZFFS00=20的写入处理 MZSF9-20200407-002 -- linzetao 2020.04.16 修改游标C_YYID, 取医院ID的操作员科室(CZYKS0)从票据使用情况表(SF_PJSYQK)改为病人缴费表(SF_BRJFB0) MZSF9-20200416-001 -- linzetao 2020.06.15 修正当收费员只做结账且有票据时也能进行缴款 MZSF9-20200513-002 -- linzetao 2020.07.16 处理启用博思电子票据后缴款票据分段问题 MZSF9-20200524-002 -- linzetao 2020.07.28 优化更新SF_PJSYQK.YYID00栏位值, 更新条件增加收费员 MZSF9-20200727-002 -- linzetao 2021.01.05 BM_JKB000增加云闪付统计 MZSF9-20201209-003 -- jlg 2021.03.18 增加通用字典参数SFYJZFPKJSBSFYXJK_MZ对于收费员电子发票开具失败不能缴款的问题 MZSF9-20210317-003 -- jlg 2021.03.22 增加BM_JKB000.WBPJLY字段的写入 MZSF9-20210317-005 -- liwm 2021.10.14 数据库记录多以后，新增收费员第一次缴款都会很慢问题处理 MZSF9-20211014-002 -- jlg 2022.01.19 处理对于升级的数据库，缴款时报违反唯一约束条件SD_HOSPITAL.PK_BM_JKMXB0_JKMXB0的问题 MZSF9-20220119-002 -- chenHeyi 2023.09.04 门诊结算有些有开发票，有些没开发票时，缴款表统筹基金缺失没开发票病人缴费表数据 as ls_SFY000 BM_JKB000.SFY000%type; --收费员编号 ls_CZYKS0 number; --操作员科室 ls_PLBH00 BM_JKB000.PJBH00%type; --票据类型编号 ls_JFBZ00 SF_BRJFB0.JFBZ00%type; --交费标志 ls_ZFFS00 SF_BRJFB0.ZFFS00%type; --支付方式 ls_PJZFH0 BM_JKB000.PJZFH0%type; --作废单据 ls_PJZFJE BM_JKB000.PJZFJE%type; --票据作废金额=现金金额+转帐金额+银行卡金额+电子钱包 ls_XJJE00 BM_JKB000.XJJE00%type; --现金金额 ls_ZZJE00 BM_JKB000.ZZJE00%type; --转帐金额 ls_GRZHZF BM_JKB000.GRZHZF%type; --个人账户支付 ls_TJJJZF BM_JKB000.TJJJZF%type; --统筹基金支付 ls_SBGRZH BM_JKB000.SBGRZH%type; --商保个人帐户 ls_SBTCJJ BM_JKB000.SBTCJJ%type; --商保统筹支付 ls_JKDH00 BM_JKB000.JKDH00%type; -- ls_PJH000 SF_PJSYQK.PJH000%type; --票据流水号 ls_ZXPJXH BM_JKB000.PJQSH0%type; --票据起始号 ls_PJZZH0 BM_JKB000.PJZZH0%type; --票据终止号 ls_YYID00 XT_YYXX00.YYID00%type; --医院ID ls_YXTDJK char(1); --是否允许收费员替代缴款 ls_FPJSXJ CHAR(1); --缴款表的发票部分是否只计算现金 Y是N 否 ls_LASTRQ char(8); --上次缴款截至日期 ls_CZRQ00 char(8); -- ls_CZSJ00 char(8); -- ls_COUNT0 number(10); -- ls_XMJE00 BM_JKKJMX.XMJE00%type; --项目金额0 ls_XMJE01 BM_JKKJMX.XMJE00%type; --项目金额1 ls_XMJE02 BM_JKKJMX.XMJE00%type; --项目金额2 ls_BEIZHU BM_JKKJMX.BEIZHU%type; --备注 ls_ZPSR00 number(12,2); --支票收入 ls_YHKSR0 number(12,2); --银行卡收入 ls_BZZZQD number(12,2); --不足转账起点 ls_YHKJS0 number(12,2); --银行卡结算 ls_YBJS00 number(12,2); --医保结算 ls_FBRZZF number(12,2); --付病人转诊费 ls_JMBRYF number(12,2); --减免病人医药费 ad_ID0000 number; -- ls_XTSJ00 char(16); -- ls_LSRQSJ char(16); --上次结账的日期时间(区分分院) ls_LSJKSJ char(16); --上次结账的日期时间(不区分分院) ls_SFZDJK XT_XTCS00.VALUE0%type; --是否启用自动缴款参数 ls_WBPJXH SF_PJSYQK.WBPJXH%type; --外部票据序号 ls_WBLYID SF_PJSYQK.WBLYID%type; --外部领用ID ls_WBPJLY SF_PJSYQK.WBPJLY%type; --外部票据来源 Vxxxlog varchar2(2000); cursor CUR_SF_ZFFP00 is --计算作废票据 select a.PLBH00, decode(nvl(a.WBLYID,0),0,nvl(a.PJLYID,0),a.WBLYID) as PJLYID, count(*) as PJZFH0, sum(a.HJJE00) as HJJE00 from SF_PJSYQK a where a.JKDID0 = ls_JKDH00 and a.PJZT00='3' and substrb(a.PJXH00,1,1)<>'-' group by a.PLBH00,decode(nvl(a.WBLYID,0),0,nvl(a.PJLYID,0),a.WBLYID); cursor CUR_SF_FPJE00 is --计算现金票据的金额(拆分的票据进行补打的话，可能会引起缴费金额的重复计算，因此暂时补打的都不算。) select a.PLBH00, decode(nvl(a.WBLYID,0),0,nvl(a.PJLYID,0),a.WBLYID) as PJLYID, sum(nvl(HJJE00,0)) HJJE00, sum(nvl(ZFJE00,0)) ZFJE00, sum(nvl(JZJE00,0)) JZJE00, sum(nvl(JMJE00,0)) JMJE00, sum(nvl(YHJE00,0)) YHJE00, sum(decode(sign(HJJE00),1,0,nvl(HJJE00,0))) HJJE01, sum(decode(sign(ZFJE00),1,0,nvl(ZFJE00,0))) ZFJE01, sum(decode(sign(JZJE00),1,0,nvl(JZJE00,0))) JZJE01, sum(decode(sign(JMJE00),1,0,nvl(JMJE00,0))) JMJE01, sum(decode(sign(YHJE00),1,0,nvl(YHJE00,0))) YHJE01 from SF_PJSYQK a where a.JKDID0 = ls_JKDH00 and (a.JFLBID='0' or (a.JFLBID='1' and ls_FPJSXJ='N')) and a.PJZT00 in ('0','1','2') group by a.PLBH00,decode(nvl(a.WBLYID,0),0,nvl(a.PJLYID,0),a.WBLYID); cursor CUR_SF_ZFJE00 is --计算支付金额 select PLBH00, decode(nvl(WBLYID,0),0,nvl(PJLYID,0),WBLYID) PJLYID, sum(decode(ZFFS00,1 ,JFJE00+JFJE01,0)) XJ0000,sum(decode(ZFFS00,1 ,JFJE01,0)) XJ0001, sum(decode(ZFFS00,2 ,JFJE00+JFJE01,0)) ZZ0000,sum(decode(ZFFS00,2 ,JFJE01,0)) ZZ0001, sum(decode(ZFFS00,8 ,JFJE00+JFJE01,0)) YHK000,sum(decode(ZFFS00,8 ,JFJE01,0)) YHK001, sum(decode(ZFFS00,18,JFJE00+JFJE01,0)) DZQB00,sum(decode(ZFFS00,18,JFJE01,0)) DZQB01, sum(decode(ZFFS00,19,JFJE00+JFJE01,0)) YJJ000,sum(decode(ZFFS00,19,JFJE01,0)) YJJ001, sum(decode(ZFFS00,3 ,JFJE00+JFJE01,0)) GRZH00, sum(decode(ZFFS00,4 ,JFJE00+JFJE01,0)) TCJJ00, sum(decode(ZFFS00,10,JFJE00+JFJE01,0)) SBZH00, sum(decode(ZFFS00,11,JFJE00+JFJE01,0)) SBTC00, sum(decode(ZFFS00,16,JFJE00+JFJE01,0)) GWYBZ0, sum(decode(ZFFS00,20,JFJE00+JFJE01,0)) YYZFJE, sum(decode(ZFFS00,24,JFJE00+JFJE01,0)) WXZF00, sum(decode(ZFFS00,25,JFJE00+JFJE01,0)) ZFBZF0, sum(decode(ZFFS00,28,JFJE00+JFJE01,0)) PTZF00, sum(decode(ZFFS00,8,decode(ZFMXBH,'V',JFJE00+JFJE01,0),0)) YSFZF0 from (select nvl(PLBH00,decode(ZFFS00,18,7,PLBH00)) PLBH00,ZFFS00,ZFMXBH, decode(sign(JFJE00),-1,0,JFJE00) JFJE00, decode(sign(JFJE00),-1,JFJE00,0) JFJE01, (select nvl(PJLYID,0) from SF_PJSYQK s where s.FZPJH0=a.PJH000 and rownum=1) PJLYID, (select nvl(WBLYID,0) from SF_PJSYQK s where s.FZPJH0=a.PJH000 and rownum=1) WBLYID from SF_BRJFB0 a where JKBID0=ls_JKDH00) a group by PLBH00,decode(nvl(WBLYID,0),0,nvl(PJLYID,0),WBLYID); cursor C_YYID is --本次缴款涉及的所有分院列表 select distinct decode(ad_YYID00, '0', YYID00, ad_YYID00) YYID00 from (select distinct (select nvl(YYID00,'0') from BM_BMBM00 where a.CZYKS0=BMBH00) YYID00 from SF_PJSYQK a where DYRQ00||'*' <= as_JSRQ00||'*' and DYRQ00||DYSJ00 <=as_JSRQ00||as_JSSJ00 and CZY000 = ls_SFY000 and ls_SFY000>0 and JKDID0 = 0 union select distinct (select nvl(YYID00,'0') from BM_BMBM00 where a.CZYKS0=BMBH00) YYID00 from SF_BRJFB0 a where JFRQ00||'*' <= as_JSRQ00||'*' and JFRQ00||JFSJ00 <=as_JSRQ00||as_JSSJ00 and JFCZY0 = ls_SFY000 and ls_SFY000>0 and JKBID0 = 0 union select YYID00 from XT_YYXX00 where ls_SFY000=0 --电子钱包缴款 union --自助机结算,没有写入SF_PJSYQK表 select distinct b.YYID00 from SF_JZB000 a,BM_BMBM00 b where a.CZYKS0 = b.BMBH00 and JZRQ00 <= as_JSRQ00 and JZRQ00||JZSJ00 <= as_JSRQ00||as_JSSJ00 and JZRQ00>=substrb(ls_LSJKSJ,1,8) and JZRQ00||JZSJ00 >ls_LSJKSJ --大于上次缴款时间 and JZY000 = ls_SFY000 and a.PJH000+0=0 and nvl(JKBID0,0) = 0 ) t; cursor C_PJSYQK_JKDID is --本单缴款的所有第三方票据记录（含电子发票跟电子预交金票据等） select PLBH00,WBPJLY,WBPJXH,PJH000 from SF_PJSYQK where JKDID0 = ls_JKDH00 --and (PJXH00 is null or PJXH00='0' or PJXH00='-0' or PJXH00='-') --本条件注释，因为换开纸质发票PJXH00是有值的,只需要下面条件就可以了 jlg and WBPJLY is not null and WBPJXH is not null order by PLBH00,WBPJLY,WBPJXH; --增加按WBPJLY排序 begin Vxxxlog := substr('AS_JSRQ00:'||nvl(to_char(AS_JSRQ00), 'null')||','||'AS_JSSJ00:'||nvl(to_char(AS_JSSJ00), 'null')||','||'AD_CZY000:'||nvl(to_char(AD_CZY000), 'null')||','||'AS_CZYXM0:'||nvl(to_char(AS_CZYXM0), 'null')||','||'AS_SFY000:'||nvl(to_char(AS_SFY000), 'null')||','||'AS_YJJXH0:'||nvl(to_char(AS_YJJXH0), 'null'),1,2000); SP_TransLog(sysdate,'SP_SF_JKB000_REG000_YJ',ad_CZY000,null,Vxxxlog); --缴款提前分钟数 select to_number(trim(nvl(max(Value0),'5'))) into ls_COUNT0 from XT_XTCS00 where NAME00='XT_JKTQSJ'; select to_char(sysdate,'YYYYMMDD')||to_char(sysdate-ls_COUNT0/1440,'hh24:mi:ss') into ls_XTSJ00 from dual;--肺科医院增加时间限制 ls_CZRQ00 := as_JSRQ00; ls_CZSJ00 := as_JSSJ00; if (ls_CZRQ00||ls_CZSJ00)>ls_XTSJ00 then as_YHMSG0:='输入的缴款时间要小于当前时间'||to_char(ls_COUNT0)||'分钟!'; as_SYSMSG:=SQLERRM||'输入的缴款时间应'||ls_XTSJ00; rollback; return; end if; --是否允许收费员代缴款 select upper(substrb(nvl(max(Value0),'Y'),1,1)) into ls_YXTDJK from XT_XTCS00 where name00='SF_SFYXSFYDJK'; --缴款表发票是否只计算现金Y是N否包含预交金 select upper(substrb(nvl(max(Value0),'Y'),1,1)) into ls_FPJSXJ from XT_XTCS00 where name00='SF_JKBFPZJSXJ'; if ad_CZY000>0 then --是否启用自动缴款参数 select nvl(regexp_substr(nvl(max(value0),'N,N,N'),'[^,]+',1,1),'N') into ls_SFZDJK from XT_XTCS00 where NAME00='SF_ZJJQYTYJK'; --只有自动缴款参数SF_ZJJQYTYJK='Y'才启用自动缴款限制 if ls_SFZDJK='Y' and to_char(sysdate,'hh24:mi') between '00:00' and '00:10' then as_YHMSG0:='00:00-00:10系统准备进行自动缴款，此区间暂停本操作！'; as_SYSMSG:=SQLERRM; rollback; return; end if; end if; ls_CZRQ00 := as_JSRQ00; ls_CZSJ00 := as_JSSJ00; begin select YGBH00 into ls_SFY000 from BM_YGBM00 where ZWXM00||'_'||XKH000 = as_SFY000; exception when others then as_YHMSG0:='系统查找不到该收费员！'; as_SYSMSG:=SQLERRM||'select * from BM_YGBM00 where ZWXM00_XKH000 = '||as_SFY000; rollback; return; end; if ls_YXTDJK='N' and ls_SFY000<>ad_CZY000 and ad_CZY000>0 then--非超级用户，不能替代缴款 as_YHMSG0:='对不起，只能由收费员本人进行缴款登记，不能由他人代劳！'; as_SYSMSG:=SQLERRM; rollback; return; end if; ls_YYID00 := ad_YYID00; update SF_PJSYQK set YYID00 = (select nvl(YYID00,ls_YYID00) from BM_BMBM00 where CZYKS0=BMBH00) where CZY000 = ls_SFY000 and JKDID0 = 0 and YYID00 is null; update SF_PJSYQK set YYID00 = (select nvl(YYID00,ls_YYID00) from BM_BMBM00 where CZYKS0=BMBH00) where HSCZY0 = ls_SFY000 and JKDID0 = 0 and YYID00 is null; update SF_BRJFB0 set YYID00 = (select nvl(YYID00,ls_YYID00) from BM_BMBM00 where CZYKS0=BMBH00) where JFCZY0 = ls_SFY000 and JKBID0 = 0 and YYID00 is null; --未缴过款的 --入参有传用入参 if ad_JKQSSJ is not null then ls_LSJKSJ := substr(ad_JKQSSJ, 1, 8); else --入参没传，用一个月前开始，不从2000年开始，否则太慢了 select nvl(max(JKRQ00||JKSJ00), to_char(sysdate-30, 'YYYYMMDD')||'00:00:00') into ls_LSJKSJ from BM_JKB000 where SFY000=ls_SFY000 and MZBZ00='0'; end if; --床位预交金处理 update SF_CWPJQK set YYID00 = (select nvl(YYID00,ls_YYID00) from BM_BMBM00 where CZYKS0=BMBH00) where JKDID0 = 0 and CZY000 = ls_SFY000 and YYID00 is null; update SF_CWJFB0 set YYID00 = (select nvl(YYID00,ls_YYID00) from BM_BMBM00 where CZYKS0=BMBH00) where JKBID0 = 0 and JFCZY0 = ls_SFY000 and YYID00 is null; --未缴过款的 --有可能一个收费员到多个分院操作，因此一次缴款只处理一个分院的 for yy in C_YYID loop ls_YYID00:=yy.YYID00; select SQ_BM_JKB000_JKBID0.nextval into ls_JKDH00 from dual; select SQ_SF_PJSYQK_PJH000.nextval into ls_PJH000 from dual; if ad_JKQSSJ is not null then ls_LASTRQ := substr(ad_JKQSSJ, 1, 8); ls_LSRQSJ := ad_JKQSSJ; else --入参没传，用一个月前开始，不从2000年开始，否则太慢了 select nvl(max(JKRQ00),to_char(sysdate-30, 'YYYYMMDD')) into ls_LASTRQ from BM_JKB000 where SFY000=ls_SFY000 and YYID00=ls_YYID00 and MZBZ00='0'; select nvl(max(JKRQ00||JKSJ00),to_char(sysdate-30, 'YYYYMMDD')||'00:00:00') into ls_LSRQSJ from BM_JKB000 where SFY000=ls_SFY000 and YYID00=ls_YYID00 and MZBZ00='0'; end if; if ls_SFY000>0 then--正常缴款 --将该收费员在限定的时间段内所做的所有票据设为已缴款标志 update SF_PJSYQK t set JKDID0 = ls_JKDH00 where DYRQ00||'*' <= as_JSRQ00||'*' and DYRQ00||DYSJ00 <=as_JSRQ00||as_JSSJ00 and CZY000 = ls_SFY000 and JKDID0 = 0 and DZQBJE = 0 and exists (select 1 from BM_BMBM00 where t.CZYKS0=BMBH00 and nvl(YYID00,'0')=ls_YYID00); --再将回收的票据打上标志 update SF_PJSYQK t set HSJKID = ls_JKDH00 where HSRQ00 <= as_JSRQ00 and HSRQ00 >= ls_LASTRQ and HSRQ00||HSSJ00 <= as_JSRQ00||as_JSSJ00 and HSCZY0 = ls_SFY000 and HSJKID+0 = 0 and DZQBJE = 0 and substr(PJXH00,1,1) <> '-' --首位是'-'的说明是系统添加的 and exists (select 1 from BM_BMBM00 where t.CZYKS0=BMBH00 and nvl(YYID00,'0')=ls_YYID00); --SFYJZFPKJSBSFYXJK_MZ：（门诊）收费员存在电子发票开具失败的票据记录，是否允许结账缴款，0不允许，1允许，默认1 if SF_XT_TYZD00('第三方电子发票平台接口','SFYJZFPKJSBSFYXJK_MZ') = '0' then select count(*) into ls_COUNT0 from SF_PJSYQK a where a.JKDID0 = ls_JKDH00 and PLBH00 = '1' and a.PJXH00 ='0' and nvl(a.WBPJXH,'0') = '0' and a.PJZT00 = '0'; if ls_COUNT0 > 0 then as_YHMSG0:='该收费员当前有存在未开具成功的电子票，系统限制不允许结账缴款！请先补充开具这些电子票后再结账缴款'; as_SYSMSG:=SQLERRM; rollback; return; end if; end if; --取出缴款单号下外部票据序号不为空的所有票据 ls_PLBH00:= -1; ls_WBPJLY:= '00'; ls_WBPJXH:= '0'; for pjsy in C_PJSYQK_JKDID loop if ls_PLBH00 <> pjsy.PLBH00 or ls_WBPJLY <> pjsy.WBPJLY or SF_XT_QHWBPJXHSFLX(ls_WBPJXH,pjsy.WBPJXH) = '0' then --票据序号不连续, 取新的外部领用id select SQ_BM_PJLYQK_PJLYID.nextval into ls_WBLYID from dual; end if; update SF_PJSYQK set WBLYID=ls_WBLYID where PJH000=pjsy.PJH000; ls_PLBH00 := pjsy.PLBH00; ls_WBPJLY := pjsy.WBPJLY; ls_WBPJXH := pjsy.WBPJXH; end loop; update SF_BRJFB0 t set JKBID0 = ls_JKDH00 where JFRQ00||'*' <= as_JSRQ00||'*' and JFRQ00||JFSJ00 <=as_JSRQ00||as_JSSJ00 and JFCZY0 = ls_SFY000 and ZFFS00<>18 and JKBID0 = 0 and exists (select 1 from BM_BMBM00 where t.CZYKS0=BMBH00 and nvl(YYID00,'0')=ls_YYID00); --2018.08.20 增加了SF_JZB000.JKBID0字段 update SF_JZB000 a set JKBID0 = ls_JKDH00 where JZRQ00 <= as_JSRQ00 and JZRQ00||JZSJ00 <= as_JSRQ00||as_JSSJ00 and JZRQ00 >= substrb(ls_LSRQSJ,1,8) and JZRQ00||JZSJ00 >ls_LSRQSJ --大于上次缴款时间 and JZY000 = ls_SFY000 and nvl(JKBID0,0) = 0 and exists (select 1 from BM_BMBM00 where BMBH00=a.CZYKS0 and YYID00=ls_YYID00); --床位预交金处理 update SF_CWPJQK t set JKDID0 = ls_JKDH00 where DYRQ00||'*' <= as_JSRQ00||'*' and DYRQ00||DYSJ00 <=as_JSRQ00||as_JSSJ00 and CZY000 = ls_SFY000 and JKDID0 = 0 and exists (select 1 from BM_BMBM00 where t.CZYKS0=BMBH00 and nvl(YYID00,'0')=ls_YYID00) and DZQBJE=0; update SF_CWJFB0 t set JKBID0 = ls_JKDH00 where JFRQ00||'*' <= as_JSRQ00||'*' and JFRQ00||JFSJ00 <=as_JSRQ00||as_JSSJ00 and JFCZY0 = ls_SFY000 and ZFFS00 <> 18 and JKBID0 = 0 and exists (select 1 from BM_BMBM00 where t.CZYKS0=BMBH00 and nvl(YYID00,'0')=ls_YYID00); elsif ls_SFY000=0 then --统一缴电子钱包款(包括自助机款) --将该收费员在限定的时间段内所做的所有票据设为已缴款标志 update SF_PJSYQK a set JKDID0 = ls_JKDH00 where DYRQ00||'*' <= as_JSRQ00||'*' and DYRQ00||DYSJ00 <=as_JSRQ00||as_JSSJ00 and JKDID0 = 0 and DZQBJE <> 0 and exists (select 1 from BM_BMBM00 where a.CZYKS0=BMBH00 and nvl(YYID00,'0')=ls_YYID00); --再将回收的票据打上标志 update SF_PJSYQK a set HSJKID = ls_JKDH00 where HSRQ00 <= as_JSRQ00 and HSRQ00 >= ls_LASTRQ and HSRQ00||HSSJ00 <= as_JSRQ00||as_JSSJ00 and HSJKID+0 = 0 and DZQBJE <> 0 and substr(PJXH00,1,1) <> '-' --首位是'-'的说明是系统添加的 and exists (select 1 from BM_BMBM00 where a.CZYKS0=BMBH00 and nvl(YYID00,'0')=ls_YYID00); update SF_BRJFB0 a set JKBID0 = ls_JKDH00 where JFRQ00||'*' <= as_JSRQ00||'*' and JFRQ00||JFSJ00 <=as_JSRQ00||as_JSSJ00 and ZFFS00 = 18 and JKBID0 = 0 and exists (select 1 from BM_BMBM00 where a.CZYKS0=BMBH00 and nvl(YYID00,'0')=ls_YYID00); end if; --SF_BRJFB0增加PLBH00票类编号字段在缴款时填写 由于可能结算时可能不产生SF_PJSYQK 记录,所以发票要根据结账表来更新 --预交金/挂号单 update SF_BRJFB0 a set PLBH00=(select PLBH00 from SF_PJSYQK where FZPJH0=a.PJH000 and rownum=1) where JKBID0 = ls_JKDH00 and PLBH00 is null; --发票 update SF_BRJFB0 a set PLBH00=1 where JKBID0 = ls_JKDH00 and PLBH00 is null and exists (select 1 from SF_JZB000 where JZDH00=a.JZDH00); --添加结帐明细表的内容(既发票项目的内容) insert into BM_JKMXB0(JKDH00,SFXMID,XMMC00,XMJE00,BZ0000) select ls_JKDH00,b.XMBH00,trim(b.XMMC00),sum(b.XMJE00),'0' from SF_PJSYQK a,SF_PJSYMX b where a.PJH000 = b.PJH000 and a.JKDID0 = ls_JKDH00 group by b.XMBH00,trim(b.XMMC00); --按票据类别，票据领用ID分类 插入缴款结账表记录 insert into BM_JKB000(JKDH00,PJH000,CZY000,JKRQ00,JKSJ00,PJBH00,PJQSH0,PJZZH0, SFY000,MZBZ00,PJLYID,YYID00,HJJE00,ZFJE00,JZJE00,GFJE00, XJJE00,ZZJE00,YHKJE0,DZQBJE,YJJZF0,GRZHZF,TJJJZF,SBGRZH, SBTCJJ,GWYBZ0,HSHJJE,HSZFH0,HSZFE0,HSJZJE,HSGFJE,HSZFJE, HSZZJE,HSYHK0,HSDZQB,HSYJJ0,PJZFH0,PJZFJE,PJZS00,YYZFJE,WBPJLY) select ls_JKDH00,ls_PJH000,ad_CZY000,ls_CZRQ00,ls_CZSJ00,PLBH00,ltrim(min(lpad(trim(nvl(WBPJXH,PJXH00)),30,' '))),ltrim(max(lpad(trim(nvl(WBPJXH,PJXH00)),30,' '))), ls_SFY000,'0',decode(nvl(WBLYID,0),0,nvl(PJLYID,0),WBLYID),ls_YYID00,0,0,0,0, 0,0,0,0,0,0,0,0, 0,0,0,0,0,0,0,0, 0,0,0,0,0,0,count(*),0,max(nvl(a.WBPJLY,'00')) from SF_PJSYQK a where a.JKDID0 = ls_JKDH00 and substr(a.PJXH00,1,1) <> '-' and a.PLBH00 <> 5 group by a.PLBH00,decode(nvl(a.WBLYID,0),0,nvl(a.PJLYID,0),a.WBLYID); --补充未生成的门诊挂号单、门诊发票、门诊预交金退款单、门诊预交金收款单 insert into BM_JKB000(JKDH00,PJH000,CZY000,JKRQ00,JKSJ00,PJBH00,PJQSH0,PJZZH0, SFY000,MZBZ00,PJLYID,YYID00,HJJE00,ZFJE00,JZJE00,GFJE00, XJJE00,ZZJE00,YHKJE0,DZQBJE,YJJZF0,GRZHZF,TJJJZF,SBGRZH, SBTCJJ,GWYBZ0,HSHJJE,HSZFH0,HSZFE0,HSJZJE,HSGFJE,HSZFJE, HSZZJE,HSYHK0,HSDZQB,HSYJJ0,PJZFH0,PJZFJE,YYZFJE,WBPJLY) select ls_JKDH00,ls_PJH000,ad_CZY000,ls_CZRQ00,ls_CZSJ00,PLBH00,0,0, ls_SFY000,'0',0,ls_YYID00,0,0,0,0, 0,0,0,0,0,0,0,0, 0,0,0,0,0,0,0,0, 0,0,0,0,0,0,0,'00' from BM_PJLXB0 where SFYX00='Y' and PLBH00 in ('0','1','2','7') and PLBH00 not in (select PJBH00 from BM_JKB000 where JKDH00=ls_JKDH00 and PJLYID=0);--门诊结算有些有开发票，有些没开发票时，缴款表统筹基金缺失没开发票病人缴费表数据 ；这里目的为了插PJLYID=0，条件应该要有PJLYID=0； --把冲销的发票纳入某一个票据领用ID update SF_PJSYQK a set PJLYID=(case when nvl(a.PJXH00,'-')='-' or a.PJXH00='-0' then a.PJLYID else (select PJLYID from BM_JKB000 where JKDH00=ls_JKDH00 and PJBH00=a.PLBH00 and rownum=1) end), WBLYID=(case when nvl(a.PJXH00,'-') <> '-' and a.PJXH00 <> '-0' then a.WBLYID else (select PJLYID from BM_JKB000 where JKDH00=ls_JKDH00 and PJBH00=a.PLBH00 and rownum=1) end) where JKDID0 = ls_JKDH00 and substr(A.PJXH00,1,1) = '-' and not exists (select 1 from BM_JKB000 where JKDH00=ls_JKDH00 and PJBH00=a.PLBH00 and PJLYID=decode(nvl(a.WBLYID,0),0,nvl(a.PJLYID,0),a.WBLYID)); --作废票据 for zf in CUR_SF_ZFFP00 loop --计算作废的票据情况 update BM_JKB000 set PJZFH0 =nvl(PJZFH0,0) + zf.PJZFH0, --作废票据数 PJZFJE =nvl(PJZFJE,0) + zf.HJJE00 where JKDH00 = ls_JKDH00 and PJBH00 = zf.PLBH00 and PJLYID = zf.PJLYID; end loop; --计算现金票据金额 for zf in CUR_SF_FPJE00 loop update BM_JKB000 set HJJE00=zf.HJJE00, ZFJE00=zf.ZFJE00, JZJE00=zf.JZJE00, GFJE00=zf.JMJE00, HSHJJE=zf.HJJE01, HSZFE0=zf.ZFJE01, HSJZJE=zf.JZJE01, HSGFJE=zf.JMJE01, HSZFH0=HSZFH0+decode(sign(zf.HJJE01),-1,1,0) where JKDH00 = ls_JKDH00 and PJBH00 = zf.PLBH00 and PJLYID = zf.PJLYID; end loop; --支付金额 for zf in CUR_SF_ZFJE00 loop update BM_JKB000 set XJJE00=nvl(XJJE00,0)+zf.XJ0000, ZZJE00=nvl(ZZJE00,0)+zf.ZZ0000, YHKJE0=nvl(YHKJE0,0)+zf.YHK000, DZQBJE=nvl(DZQBJE,0)+zf.DZQB00, HJJE00=nvl(HJJE00,0)+nvl(zf.DZQB00,0), YJJZF0=nvl(YJJZF0,0)+zf.YJJ000, HSZFJE=nvl(HSZFJE,0)+zf.XJ0001, HSZZJE=nvl(HSZZJE,0)+zf.ZZ0001, HSYHK0=nvl(HSYHK0,0)+zf.YHK001, HSDZQB=nvl(HSDZQB,0)+zf.DZQB01, HSYJJ0=nvl(HSYJJ0,0)+zf.YJJ001, GRZHZF=nvl(GRZHZF,0)+zf.GRZH00, TJJJZF=nvl(TJJJZF,0)+zf.TCJJ00, SBGRZH=nvl(SBGRZH,0)+zf.SBZH00, SBTCJJ=nvl(SBTCJJ,0)+zf.SBTC00, GWYBZ0=nvl(GWYBZ0,0)+zf.GWYBZ0, WXZF00=nvl(WXZF00,0)+zf.WXZF00, ZFBZF0=nvl(ZFBZF0,0)+zf.ZFBZF0, PTZF00=nvl(WXZF00,0)+zf.PTZF00, YYZFJE=nvl(YYZFJE,0)+zf.YYZFJE, YSFZF0=nvl(YSFZF0,0)+zf.YSFZF0 where JKDH00 = ls_JKDH00 and PJBH00 = zf.PLBH00 and PJLYID = zf.PJLYID; end loop; as_JKDH00:=ls_JKDH00; --填写BM_JKKJMX表的内容:医大附二缴款报表需要填写明细表数据 2004.06.16 insert into BM_JKKJMX(JKDH00,XMBH00,XMMC00,XMJE00,BEIZHU,BZ0000,XMXH00) values(ls_JKDH00,'01','1、上期余额' ,0,'','0',1); insert into BM_JKKJMX(JKDH00,XMBH00,XMMC00,XMJE00,BEIZHU,BZ0000,XMXH00) values(ls_JKDH00,'02','2、收预交现金' ,0,'','0',2); insert into BM_JKKJMX(JKDH00,XMBH00,XMMC00,XMJE00,BEIZHU,BZ0000,XMXH00) values(ls_JKDH00,'03','3、退预交现金' ,0,'','0',3); insert into BM_JKKJMX(JKDH00,XMBH00,XMMC00,XMJE00,BEIZHU,BZ0000,XMXH00) values(ls_JKDH00,'04','4、现金结算' ,0,'','0',4); insert into BM_JKKJMX(JKDH00,XMBH00,XMMC00,XMJE00,BEIZHU,BZ0000,XMXH00) values(ls_JKDH00,'05','5、本期应缴现金' ,0,'','0',5); insert into BM_JKKJMX(JKDH00,XMBH00,XMMC00,XMJE00,BEIZHU,BZ0000,XMXH00) values(ls_JKDH00,'06','6、实际应缴现金' ,0,'','0',6); insert into BM_JKKJMX(JKDH00,XMBH00,XMMC00,XMJE00,BEIZHU,BZ0000,XMXH00) values(ls_JKDH00,'07','7、本期上缴' ,0,'','0',7); insert into BM_JKKJMX(JKDH00,XMBH00,XMMC00,XMJE00,BEIZHU,BZ0000,XMXH00) values(ls_JKDH00,'08','8、本期余额' ,0,'','0',8); insert into BM_JKKJMX(JKDH00,XMBH00,XMMC00,XMJE00,BEIZHU,BZ0000,XMXH00) values(ls_JKDH00,'09','9、银行卡收入' ,0,'','0',9); insert into BM_JKKJMX(JKDH00,XMBH00,XMMC00,XMJE00,BEIZHU,BZ0000,XMXH00) values(ls_JKDH00,'10','10、支票收入' ,0,'','0',10); --上期余额 begin select XMJE00 into ls_XJJE00 from BM_JKKJMX where XMBH00='08' and JKDH00=(select max(JKDH00) from BM_JKB000 where SFY000= ls_SFY000 and JKDH000 then insert into BM_JKKJMX(JKDH00,XMBH00,XMMC00,XMJE00,BEIZHU,BZ0000,XMXH00) values(ls_JKDH00,'11','11、四舍五入' ,ls_XJJE00,'','0',11); end if; --电子钱包 select nvl(sum(A.JFJE00),0) into ls_XJJE00 from SF_BRJFB0 a where a.JKBID0=ls_JKDH00 and a.JFBZ00 in ('0','1','2') and ZFFS00=18; if ls_XJJE00<>0 then insert into BM_JKKJMX(JKDH00,XMBH00,XMMC00,XMJE00,BEIZHU,BZ0000,XMXH00) values(ls_JKDH00,'12','12、电子钱包收入',ls_XJJE00,'','0',12); end if; --预交金支付 select nvl(sum(A.JFJE00),0) into ls_XJJE00 from SF_BRJFB0 a where a.JKBID0=ls_JKDH00 and a.JFBZ00 in ('0','1','2') and ZFFS00=19; if ls_XJJE00<>0 then insert into BM_JKKJMX(JKDH00,XMBH00,XMMC00,XMJE00,BEIZHU,BZ0000,XMXH00) values(ls_JKDH00,'13','13、预交金支付收入',ls_XJJE00,'','0',13); end if; --ZFFS00=20 医院支付 select nvl(sum(A.JFJE00),0) into ls_XJJE00 from SF_BRJFB0 a where a.JKBID0=ls_JKDH00 and a.JFBZ00 in ('0','1','2') and ZFFS00=20; if ls_XJJE00<>0 then insert into BM_JKKJMX(JKDH00,XMBH00,XMMC00,XMJE00,BEIZHU,BZ0000,XMXH00) values(ls_JKDH00,'20','20、医院支付',ls_XJJE00,'','0',20); end if; --ZFFS00=24 微信支付 select nvl(sum(A.JFJE00),0) into ls_XJJE00 from SF_BRJFB0 a where a.JKBID0=ls_JKDH00 and a.JFBZ00 in ('0','1','2') and ZFFS00=24; if ls_XJJE00<>0 then insert into BM_JKKJMX(JKDH00,XMBH00,XMMC00,XMJE00,BEIZHU,BZ0000,XMXH00) values(ls_JKDH00,'24','24、微信支付',ls_XJJE00,'','0',24); end if; --ZFFS00=25 支付宝支付 select nvl(sum(A.JFJE00),0) into ls_XJJE00 from SF_BRJFB0 a where a.JKBID0=ls_JKDH00 and a.JFBZ00 in ('0','1','2') and ZFFS00=25; if ls_XJJE00<>0 then insert into BM_JKKJMX(JKDH00,XMBH00,XMMC00,XMJE00,BEIZHU,BZ0000,XMXH00) values(ls_JKDH00,'25','25、支付宝支付',ls_XJJE00,'','0',25); end if; --ZFFS00=28 平台支付(榕医通) select nvl(sum(A.JFJE00),0) into ls_XJJE00 from SF_BRJFB0 a where a.JKBID0=ls_JKDH00 and a.JFBZ00 in ('0','1','2') and ZFFS00=28; if ls_XJJE00<>0 then insert into BM_JKKJMX(JKDH00,XMBH00,XMMC00,XMJE00,BEIZHU,BZ0000,XMXH00) values(ls_JKDH00,'28','28、平台支付',ls_XJJE00,'','0',28); end if; --写入缴款起始时间 insert into BM_JKSJ00 (JKRQ00,JKSJ00,JKRY00,JKBZ00,JKDH00,JKQSRQ,JKQSSJ,YYID00) select as_JSRQ00,as_JSRQ00||as_JSSJ00,ls_SFY000,'0',ls_JKDH00,max(jkrq00),max(JKRQ00||JKSJ00),ls_YYID00 from BM_JKB000 where JKDH00