CREATE OR REPLACE PROCEDURE SP_SF_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 default '0', --医院ID ad_ID0000 out number, as_YHMSG0 out varchar, --存储过程提示的错误信息 as_SYSMSG out varchar, --系统提示的错误信息 as_TJFS00 in char default '0,0', --0,0:所有 1,1:显示有金额收费员and 实交现金=实交现金-(1)挂号单收入-(4.1)现金+(5.1)票据退款 as_TJLX00 in char default '0' --0:按发票打印日期统计 1:按结算日期统计 ) as -- MODIFICATION HISTORY -- qks 2008.08.05 由于一些医院数据库版本比较低，要进行代码精简 MZSF-20080805-003; -- qks 2008.08.16 统计冲销结账单号时，数据应归到新收费员，不是原收费员。AA.CZY000-->JZDH.JZY000; -- xuzw 2010.03.09 由于医保接口增加公务员补助,结算医保金额要把该金额加入运算 -- qks 2010.03.12 预交金病人结算后，记帐金额也体现在 门诊现金收入审核表 中的 4、(4)记帐 以及 12、记帐 两处 -- qks 2010.03.27 修改公务员补助GWYBZ0、商保(SBTCJJ、SBGRZH)是否包含在统筹金额中的问题; -- qks 2010.06.23 参数SF_SFQYDZQB控制是否显示电子钱包金额; 现金结算时电子钱包支付暂不考虑！ -- qks 2010.07.28 游标C_CXJZDH增加条件A.CXJZDH<>0 --zhangyc 2010.11.05 修改医院id取值(select SF_SF_GETYYID00() YYID00 from dual) --zhangyc 2011.04.22 增加参数SF_XJSRSHBSFTJ(莆田学院附属医院=N)当天补打以前预交金,现金不统计在当天内 by MZSF-20110419-003 其它医院暂时不修正 --zhangyc 2011.07.05 银行卡收入没有扣掉作废金额 by MZSF-20110701-001 lml归档 --jinfl 2012.02.28 增加收费员所属医院ID MZSF-20120224-002 --dsm 2012.04.26 除了收费员之外的都归入其它 for MZSF-20120420-003 --zhangyc 2012.05.15 增加一列合计值过滤数据为0用,以及入参as_TJFS00 by MZSF-20120419-004 --zhangyc 2012.11.01 修正BEIZH1=nvl(BEIZH1,0)+AA.BEIZH1-->BEIZH1=nvl(BEIZH1,0)||','||AA.BEIZH1 by MZSF-20121101-001 --jinfl 2013.03.07 支票收入区分出医保病人和新农合病人 MZSF-20130126-001 --zhangyc 2013.10.25 修正当收费员超过31数据错误 by MZSF-20131023-001 --zhangyc 2013.11.18 扩展as_TJFS00功能 第一位是否显示有金额的收费员'1':是 第三位实缴现金是否启用新公式(实交现金=实交现金-(1)挂号单收入-(4.1)现金+(5.1)票据退款)'1'是 by MZSF-20131111-001 -- liuj 2014.01.14 医保结算区分出医保跟新农合 for MZSF-20130110-002 --zhangyc 2014.03.12 收费处挂号把挂号单记账金额一起统计（游标C_XJ0000中A.PLBH00=1改为A.PLBH00=1 in (0,1)） BY MZSF-20140306-002 --zhangyc 2014.04.29 修正收费员太多无法正常显示问题以及其中医保农合统计错误 by MZSF-20140428-001 --linzy 2014.05.05 修改农合结算+医保结算<>医保 by MZSF-20140423-002. --zhangyc 2014.05.08 修改泉州南安市医院（225534）判断否则其它医院会错 by MZSF-20140507-002 --zhangyc 2014.07.23 增加参数SF_XJSHBSJXJ 预交金收入是否包含银行卡金额 Y:包含 N:不包含 默认值:Y包含 by MZSF-20140704-003 --zhangyc 2014.07.28 修改参数SF_XJSRSHBSFTJ控制写法预交金收入如果是补打状态按缴费日期统计 by MZSF-20140715-001 --zhangyc 2015.05.15 增加入参as_TJLX00(0:按发票打印日期统计 1:按结算日期统计)by MZSF-20150429-001 --zhangyc 2015.07.07 增加微信支付收入(21.01)应缴金额和实交现金扣除到微信支付by MZSF-20150629-001 --zhangyc 2015.07.22 增加健康通(民生通)支付功能 by MZSF-20150722-001 --zhangyc 2016.09.14 修正医院现场收费+加上自助超过90导致无法正常显示 by MZSF-20160912-003 --zhangyc 2016.11.12 9.1-->A,9.2-->B by MZSF-20161027-002 --zhangyc 2016.11.17 修正SF_BRJFB0无记录时无法正常显示金额 by MZSF-20161117-001 --qks 2017.01.11 修正医院现场收费+加上自助超过100导致无法正常显示,目前加到150个; by MZSF-20170109-001 --qks 2017.03.08 同安中院出现“函数的参数过多”问题 by MZSF-20170308-001 --zhangyc 2017.06.05 增加微信、支付宝支付 by MZSF-20170508-001 --zhangyc 2018.01.17 增加显示区域预交支付 by MZSF-20180105-002 --dsm 2018.04.26 南安市医院存在SF_JZB0000.YBLB00=null的数据,这样如果被冲洗时退款的单据统计不出来,导致实缴现金计算错误 MZSF-20180426-005 --zhuyr 2018.12.06 莆田附属，在15、实交现金下增加15.1、择日住院病例的内容 by MZSF-20181119-002 --pwt 2019.02.12 莆田学院附属医院 实交现金不包含择日住院病例，单独改为16，同时实交现金扣除择日住院病例金额 by MZSF-20190128-002 --zhuyr 2019.04.17 处理传入全部收费员时，择日住院病例项目内容为空的问题 by MZSF-20190417-002 --qiulf 2019.09.03 修改按发票日期统计时，过滤补打的发票 by MZSF-20190801-002 -- dsm 2020.01.07 修正MZSF-20190801-002过滤补打的发票bug by MZSF-20200107-001 --qiulf 2020.09.18 通过病人缴费表获取公务员补助、商保的金额 by MZSF-20200909-001 --linbin 2021.07.26 增加平台支付 by MZSF-20210709-004 ------------ ------------- ---------------------------------------------------------------------------- ld_YGBH00 BM_YGBM00.YGBH00%TYPE; ls_JKDH00 BM_JKB000.JKDH00%TYPE; ls_COUNT0 NUMBER(5); ls_KSRQSJ char(16); ls_JSRQSJ char(16); ls_XMJE00 BM_JKKJMX.XMJE00%TYPE ; --项目金额0 ls_ZPSR00 NUMBER(12,2); --支票收入 ls_YHKSR0 NUMBER(12,2); --银行卡收入 ls_ZPSR01 NUMBER(12,2); --支票收入 ls_YHKSR1 NUMBER(12,2); --银行卡收入 ls_ZPSR02 NUMBER(12,2); --支票收入 ls_YHKSR2 NUMBER(12,2); --银行卡收入 ls_ZPSR03 NUMBER(12,2); --支票收入 ls_YHKSR3 NUMBER(12,2); --银行卡收入 ls_BZZZQD NUMBER(12,2); --不足转账起点 ls_YHKJS0 NUMBER(12,2); --银行卡结算 ls_YBJS00 NUMBER(12,2); --医保结算 ls_JMBRYF 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_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_XMJE73 NUMBER(12,2); ls_XMJE74 NUMBER(12,2); ls_XMJE75 NUMBER(12,2); ls_XMJE76 NUMBER(12,2); ls_XMJE77 NUMBER(12,2); ls_XMJE78 NUMBER(12,2); ls_XMJE79 NUMBER(12,2); ls_XMJE80 NUMBER(12,2); ls_XMJE81 NUMBER(12,2); ls_JZWJS0 NUMBER(12,2); --记账未结算 ls_JZYJS0 NUMBER(12,2); --记账已结算 ls_KYJSR0 NUMBER(12,2); --卡押金收入 ls_KYJTC0 NUMBER(12,2); --卡押金退出 ls_KYJCE0 NUMBER(12,2); --卡押金差额 ls_YBZHZR NUMBER(12,2); --医保账户转入 ls_YJJCE0 NUMBER(12,2); --本期预交金差额 ls_bqywsr NUMBER(12,2); --本期业务收入 ls_bqwjsr NUMBER(12,2); --本期未结收入 ls_BQYJJS NUMBER(12,2); --本期预交减少(预交金发生费用) ls_SQYJSR NUMBER(12,2); --上期预交金收入余额 ls_SQKJSR NUMBER(12,2); --上期卡押金收入余额 ls_HJYJCE NUMBER(12,2); --合计本期预交金差额 ls_HJKJCE NUMBER(12,2); --合计本期卡押金差额 ls_SQHJYJ NUMBER(12,2); --上期合计预交总额 ls_SQHJFY NUMBER(12,2); --上期合计费用总额 ls_BQYJSR NUMBER(12,2); --本期已结票据收入 ld_ID0000 NUMBER(10); ls_KSBH00 BM_GHKSTJ_KSDY00.BH0000%TYPE; ls_KSBHJS NUMBER(4); ls_JZDH00 SF_JZB000.JZDH00%TYPE; ls_ICSFDM SF_FYMX00.XMBH00%TYPE; ls_YYID00 XT_YYXX00.YYID00%TYPE; ls_QYDZQB XT_XTCS00.VALUE0%TYPE; --是否启用电子钱包 ls_cou NUMBER(5); LS_XJSRSHBSFTJ XT_XTCS00.VALUE0%TYPE; --报表当天是否统计补打当天之前的预交金金额 BY MZSF-20110419-003 lv_TJFS01 varchar2(10); --是否显示有金额收费员 lv_TJFS02 varchar2(10); --实交现金=实交现金-(1)挂号单收入-(4.1)现金+(5.1)票据退款 Lv_SJXJ00 NUMBER(12,2); --实交现金 LS_YHSFKC varchar2(10); --预交金收入是否扣除银行卡 Y:扣除 N:不扣除 默认值:N不扣除 LS_WXZFPJ number(12,2);--微信支付票据 LS_WXZFSR number(12,2);--微信支付收入 LS_ZFBZFPJ number(12,2);--支付宝支付票据 LS_ZFBZFSR number(12,2);--支付宝支付收入 LS_MST0PJ number(12,2);--民生支付票据 LS_MST0SR number(12,2);--民生支付收入 LS_QYPTZF number(12,2);--区域预交金合计 SF_SFQYYKTPT varchar2(100);--是否启用一卡通平台接口 Y:启用 N:不启用 默认:N不启用 SF_SFQYYYTPT varchar2(100);--是否启用银医通共享功能 Y:启用 N:不启用 SF_SFQYJKTPT varchar2(100);--是否开启民生通支付功能 Y:启用 N:不启用 LS_QYYJJPT varchar2(100);--是否开启区域预交金功能 Y:启用 N:不启用 ls_ZRZYYJ number(12,2);--择日住院病例 CURSOR CUR_SF_BRJFB0_SFY000 IS select DYID00 YGBH00, substrb(GHRQ00,1,10)ZWXM00, GHKS01 XMJE01,GHKS02 XMJE02,GHKS03 XMJE03,GHKS04 XMJE04, GHKS05 XMJE05,GHKS06 XMJE06,GHKS07 XMJE07,GHKS08 XMJE08, GHKS09 XMJE09,GHKS10 XMJE10,GHKS11 XMJE11,GHKS12 XMJE12, GHKS13 XMJE13,GHKS14 XMJE14,GHKS15 XMJE15,GHKS16 XMJE16, GHKS17 XMJE17,GHKS18 XMJE18,GHKS19 XMJE19,GHKS20 XMJE20, GHKS21 XMJE21,GHKS22 XMJE22,GHKS23 XMJE23,GHKS24 XMJE24, GHKS25 XMJE25,GHKS26 XMJE26,GHKS27 XMJE27,GHKS28 XMJE28, GHKS29 XMJE29, GHKS90 XJSR00,GHKS91 XJSR01,GHKS92 XJSR02,GHKS93 XJSR03, GHKS94 YHKSR0,GHKS95 YHKSR1,GHKS96 YHKSR2,GHKS97 YHKSR3, GHKS40 QYPTZF, GHKS50 ZPSR00,GHKS51 ZPSR01,GHKS52 ZPSR02,GHKS53 ZPSR03, GHKS54 WXZFPJ,GHKS55 WXZFSR, GHKS56 MST0PJ,GHKS57 MST0SR,GHKS58 ZFBZFPJ,GHKS59 ZFBZFSR, GHKS60 KYJSR0,GHKS61 KYJTC0,GHKS62 BQSSXJ,GHKS63 YBZHZR, GHKS64 BQSJXJ,GHKS65 BQJSDK,GHKS66 BQYJCE,GHKS67 KYJCE0, BEIZHU BEIZH0,BEIZH1,BEIZH2,BEIZH3,GHKS70 DZQB00, GHKS71 DZQB01,GHKS72 DZQB02,GHKS73 DZQB03,GHKS74 XMJE74,GHKS75 XMJE75, GHKS76 XMJE76,GHKS77 XMJE77,GHKS78 XMJE78,GHKS79 XMJE79,GHKS81 JZYJS0, GHKS82 JZWJS0 from BM_GHKSTJ where ID0000=ld_ID0000; --GHKS05=门诊发票的现金部分, GHKS06=门诊发票的支票部分, GHKS07=门诊发票的银行卡部分, GHKS08=门诊发票的记账部分 --GHKS09=预交金结算合计, GHKS10=医保结算医保支付金额, GHKS11=其它支付金额 --GHKS90现金挂号收入的现金部分, GHKS91预交金收款单的现金部分, GHKS92预交金退款单的现金部分, GHKS93门诊发票的现金部分 --GHKS94现金挂号收入的银行卡部分,GHKS95预交金收款单的银行卡部分,GHKS96预交金退款单的银行卡部分,GHKS97门诊发票的银行卡部分 --GHKS50现金挂号收入的支票部分, GHKS51预交金收款单的支票部分, GHKS52预交金退款单的支票部分, GHKS53门诊发票的支票部分 --GHKS60其中：卡押金收入 GHKS61其中：卡押金退出 GHKS62本期实收现金 GHKS63加上：医保统筹账户转入 --GHKS64本期应上交现金 GHKS65减去：本期预交金结算抵扣业务收入 GHKS66本期预交金余额(差额) GHKS67其中：卡押金余额(差额) --GHKS70现金挂号收入的电子钱包部分(暂时没区分出来), GHKS71预交金收款单的电子钱包部分, GHKS72预交金退款单的电子钱包部分, --GHKS73门诊发票的电子钱包部分,GHKS29电子钱包收入 begin LS_WXZFPJ:=0;--微信支付票据 LS_WXZFSR:=0;--微信支付收入 LS_ZFBZFPJ:=0;--支付宝支付票据 LS_ZFBZFSR:=0;--支付宝支付收入 LS_MST0PJ:=0;--民生支付票据 LS_MST0SR:=0;--民生支付收入 LS_QYPTZF:=0;--区域预交金合计 ls_KSBH00:='01'; ls_KSBHJS:=1; lv_TJFS01:=substrb(as_TJFS00,1,1); lv_TJFS02:=substrb(as_TJFS00,3,1); --SELECT trim(YYID00) into ls_YYID00 FROM XT_YYXX00 WHERE ROWNUM=1; select SF_SF_GETYYID00() YYID00 into ls_YYID00 from dual; select SF_SF_TYZD00('一卡通平台参数','SF_SFQYYKTPT') into SF_SFQYYKTPT from dual; select SF_SF_TYZD00('银医通共享平台','payopen') into SF_SFQYYYTPT from dual; select SF_SF_TYZD00('民生通系统参数','PmstOpen') into SF_SFQYJKTPT from dual; select SF_SF_TYZD00('福建预交金统一平台','Open_MZ') into LS_QYYJJPT from dual; SELECT to_NUMBER(nvl(max(VALUE0),0)) into ls_ICSFDM FROM XT_XTCS00 WHERE NAME00='IC_SFDM'; SELECT nvl(max(VALUE0),'0') into ls_QYDZQB FROM XT_XTCS00 WHERE NAME00='SF_SFQYDZQB'; begin select trim(value0) into LS_XJSRSHBSFTJ from xt_xtcs00 where name00='SF_XJSRSHBSFTJ'; exception when others then LS_XJSRSHBSFTJ:='Y'; end; --预交金收入是否包含银行卡金额 Y:包含 N:不包含 默认值:Y包含 begin select trim(value0) into LS_YHSFKC from xt_xtcs00 where name00='SF_XJSHBSJXJ'; exception when others then LS_YHSFKC:='Y'; end; Select SQ_BM_YYSFTJ_ID0000.nextval into ad_ID0000 from dual; Select SQ_BM_YYSFTJ_ID0000.nextval into ld_ID0000 from dual; --临时员工编码表 --插入所有的收费员 if as_SFY000<>'所有收费员' then select YGBH00 into ld_YGBH00 from BM_YGBM00 B where B.ZWXM00||'_'||B.XKH000 = as_SFY000; else ld_YGBH00:=-1; end if; ----------------------电子钱包单独列------------------------------------------ insert into BM_GHKSTJ(ID0000,DYID00,GHRQ00) values(ld_ID0000,-1,'其它'); --------------------------------------------------------------------------- insert into BM_GHKSTJ(ID0000,DYID00,GHRQ00) select ld_ID0000,YGBH00,ZWXM00 from BM_YGBM00 B where B.YGBH00 in ( select distinct A.JFCZY0 from SF_BRJFB0 A where 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 (JFCZY0=ld_YGBH00 or ld_YGBH00=-1) -- by MZSF-20161117-001 union all select distinct A.JZY000 from SF_JZB000 A where 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 (JZY000=ld_YGBH00 or ld_YGBH00=-1) ) and (YGXZ00 like '%3%' or YGXZ00 like '%5%'); for sfy in CUR_SF_BRJFB0_SFY000 loop Insert into BM_GHKSTJ_KSDY00(ID0000,BH0000,GHKSBH,GHKSMC) values(ad_ID0000,ls_KSBH00,sfy.YGBH00,sfy.ZWXM00); ls_KSBHJS:=ls_KSBHJS+1; if ls_KSBHJS>=100 then Select lpad(to_char(ls_KSBHJS),3,'0') into ls_KSBH00 from dual; else Select lpad(to_char(ls_KSBHJS),2,'0') into ls_KSBH00 from dual; end if; END LOOP; if ls_YYID00='222211' THEN --平潭医院特殊 insert into BM_GHKSTJ(ID0000,GHKS199,GHRQ00) values(ad_ID0000, -1, ' 1、上期预交金收入余额 '); insert into BM_GHKSTJ(ID0000,GHKS199,GHRQ00) values(ad_ID0000, 0, ' 2、其中：卡押金余额 '); insert into BM_GHKSTJ(ID0000,GHKS199,GHRQ00) values(ad_ID0000, 2, ' 3、预交金收入 '); insert into BM_GHKSTJ(ID0000,GHKS199,GHRQ00) values(ad_ID0000, 2.1, ' 4、其中：卡押金收入 '); insert into BM_GHKSTJ(ID0000,GHKS199,GHRQ00) values(ad_ID0000, 3, ' 5、预交金现金退出 '); insert into BM_GHKSTJ(ID0000,GHKS199,GHRQ00) values(ad_ID0000, 3.1, ' 6、其中：卡押金退出 '); insert into BM_GHKSTJ(ID0000,GHKS199,GHRQ00) values(ad_ID0000, 3.2, ' 7、本期实收现金 '); insert into BM_GHKSTJ(ID0000,GHKS199,GHRQ00) values(ad_ID0000, 3.3, ' 8、加上：医保统筹账户转入'); insert into BM_GHKSTJ(ID0000,GHKS199,GHRQ00) values(ad_ID0000, 3.4, ' 9、本期应上交现金 '); insert into BM_GHKSTJ(ID0000,GHKS199,GHRQ00) values(ad_ID0000, 3.5, ' 10、减去：本期预交金结算抵扣业务收入'); insert into BM_GHKSTJ(ID0000,GHKS199,GHRQ00) values(ad_ID0000, 3.6, ' 11、本期预交金余额 '); insert into BM_GHKSTJ(ID0000,GHKS199,GHRQ00) values(ad_ID0000, 3.7, ' 12、其中：卡押金余额 '); insert into BM_GHKSTJ(ID0000,GHKS199,GHRQ00) values(ad_ID0000, 3.8, ' 13、本期累计预交金余额 '); insert into BM_GHKSTJ(ID0000,GHKS199,GHRQ00) values(ad_ID0000, 3.9, ' 14、本期累计卡押金余额 '); insert into BM_GHKSTJ(ID0000,GHKS199,GHRQ00) values(ad_ID0000,3.91, ' 15、本期业务收入 '); insert into BM_GHKSTJ(ID0000,GHKS199,GHRQ00) values(ad_ID0000, 4, ' 16、已结算票据收入 '); insert into BM_GHKSTJ(ID0000,GHKS199,GHRQ00) values(ad_ID0000, 5, ' 其中: (1)现金 '); insert into BM_GHKSTJ(ID0000,GHKS199,GHRQ00) values(ad_ID0000, 6, ' (2)支票 '); insert into BM_GHKSTJ(ID0000,GHKS199,GHRQ00) values(ad_ID0000, 7, ' (3)银行卡 '); insert into BM_GHKSTJ(ID0000,GHKS199,GHRQ00) values(ad_ID0000, 8, ' (4)记账 '); insert into BM_GHKSTJ(ID0000,GHKS199,GHRQ00) values(ad_ID0000, 9, ' (5)就诊卡结算 '); insert into BM_GHKSTJ(ID0000,GHKS199,GHRQ00) values(ad_ID0000, 10, ' (6)医保中心结算 '); insert into BM_GHKSTJ(ID0000,GHKS199,GHRQ00) values(ad_ID0000, 11, ' (7)其它结算 '); insert into BM_GHKSTJ(ID0000,GHKS199,GHRQ00) values(ad_ID0000, 12, ' (8)补打收据款(非医保病人)'); insert into BM_GHKSTJ(ID0000,GHKS199,GHRQ00) values(ad_ID0000, 13, ' (9)换新收据 '); insert into BM_GHKSTJ(ID0000,GHKS199,GHRQ00) values(ad_ID0000, 14, ' 17、退款收据 '); insert into BM_GHKSTJ(ID0000,GHKS199,GHRQ00) values(ad_ID0000, 15, ' 其中: (1)票据退款 '); insert into BM_GHKSTJ(ID0000,GHKS199,GHRQ00) values(ad_ID0000, 16, ' (2)就诊卡退款 '); insert into BM_GHKSTJ(ID0000,GHKS199,GHRQ00) values(ad_ID0000, 17, ' (3)退医保款 '); insert into BM_GHKSTJ(ID0000,GHKS199,GHRQ00) values(ad_ID0000,17.1, ' 18、未结算收入 '); insert into BM_GHKSTJ(ID0000,GHKS199,GHRQ00) values(ad_ID0000, 18, ' 19、换(旧)收据 '); insert into BM_GHKSTJ(ID0000,GHKS199,GHRQ00) values(ad_ID0000, 19, ' 20、应缴金额 '); insert into BM_GHKSTJ(ID0000,GHKS199,GHRQ00) values(ad_ID0000, 20, ' 21、支票收入 '); insert into BM_GHKSTJ(ID0000,GHKS199,GHRQ00) values(ad_ID0000, 21, ' 22、银行卡收入 '); insert into BM_GHKSTJ(ID0000,GHKS199,GHRQ00) values(ad_ID0000, 22, ' 23、不足转账起点 '); insert into BM_GHKSTJ(ID0000,GHKS199,GHRQ00) values(ad_ID0000, 23, ' 24、银行卡结算 '); insert into BM_GHKSTJ(ID0000,GHKS199,GHRQ00) values(ad_ID0000, 24, ' 25、记账 '); insert into BM_GHKSTJ(ID0000,GHKS199,GHRQ00) values(ad_ID0000, 25, ' 26、医保中心结算 '); insert into BM_GHKSTJ(ID0000,GHKS199,GHRQ00) values(ad_ID0000, 26, ' 27、减免病人医药费 '); insert into BM_GHKSTJ(ID0000,GHKS199,GHRQ00) values(ad_ID0000, 27, ' 28、实交现金 '); insert into BM_GHKSTJ(ID0000,GHKS199,GHRQ00) values(ad_ID0000, 28, ' 29、21~27合计 '); --现金挂号单收入,原来的求和有问题,主要是SF_PJSYQK同一个票据号,有一条记录,SF_BRJFB0有多条记录 declare cursor C_XJGH00 is select NVL(sum(B.XJJE00+B.ZZJE00+B.YHKJE0),0) XMJE00,JFCZY0 CZY000, ltrim(min(lpad(trim(B.pjxh00),30,' ')))||' - '||ltrim(max(lpad(trim(B.pjxh00),30,' '))) BEIZH0 from SF_BRJFB0 A,SF_PJSYQK B where A.PJH000 = B.FZPJH0 and A.JFJE00 <> 0 --考虑程序的问题,交费金额为零的也添加,这样相互关联就出现数据重复 and B.PLBH00 = 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 B.PJZT00 = '0' --有效的票据 and (JFCZY0=ld_YGBH00 or ld_YGBH00=-1) and exists (select 1 from BM_BMBM00 where BMBH00=A.CZYKS0 and (trim(YYID00)=as_YYID00 or as_YYID00='0')) group by A.JFCZY0; begin for AA in C_XJGH00 loop --现金挂号收入的现金部分 Update BM_GHKSTJ set GHKS90=AA.XMJE00,BEIZHU=AA.BEIZH0 where ID0000=ld_ID0000 and DYID00=AA.CZY000; end loop; end; --预交金收入,预交金收款单或预交金退款单 --隔天补打预交也统计在内--begin--- if LS_XJSRSHBSFTJ='Y' then declare cursor C_YJJSKD is select NVL(sum(B.XJJE00),0)XMJE01 ,NVL(sum(B.ZZJE00),0) ZPSR01,NVL(sum(B.YHKJE0),0) YHKSR1,CZY000,PLBH00, ltrim(min(lpad(trim(B.pjxh00),30,' ')))||' - '||ltrim(max(lpad(trim(B.pjxh00),30,' '))) BEIZH1 from SF_PJSYQK B where exists(select 1 from SF_BRJFB0 A where A.PJH000 = B.FZPJH0 and A.JFJE00 <> 0) --and A.JFJE00 <> 0 --考虑程序的问题,交费金额为零的也添加,这样相互关联就出现数据重复 and B.PLBH00 in (7,2) --门诊预交金收款单 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 (CZY000=ld_YGBH00 or ld_YGBH00=-1) and exists (select 1 from BM_BMBM00 where BMBH00=B.CZYKS0 and (trim(YYID00)=as_YYID00 or as_YYID00='0')) group by B.CZY000,PLBH00; --and substr(B.PJXH00,1,1) <> '-',注意:与住院不同 --and A.JZDH00 <> 1 --作废的门诊预交金收款票据不计算在内！ begin for AA in C_YJJSKD loop if AA.PLBH00=7 then --预交金收款单的现金，银行卡，支票部分 Update BM_GHKSTJ set GHKS91=AA.XMJE01,GHKS95=AA.YHKSR1,GHKS51=AA.ZPSR01,BEIZH1=AA.BEIZH1 where ID0000=ld_ID0000 and DYID00=AA.CZY000; elsif AA.PLBH00=2 then --预交金退款单的现金，银行卡，支票部分 Update BM_GHKSTJ set GHKS92=AA.XMJE01,GHKS96=AA.YHKSR1,GHKS52=AA.ZPSR01,BEIZH2=AA.BEIZH1 where ID0000=ld_ID0000 and DYID00=AA.CZY000; end if; end loop; end; --隔天补打预交也统计在内--end--- else --隔天补打预交按交费时间统计不统计在打印时间内--begin--- declare cursor C_YJJSKD is select NVL(sum(B.XJJE00),0)XMJE01 ,NVL(sum(B.ZZJE00),0) ZPSR01,NVL(sum(B.YHKJE0),0) YHKSR1,CZY000,PLBH00, ltrim(min(lpad(trim(B.pjxh00),30,' ')))||' - '||ltrim(max(lpad(trim(B.pjxh00),30,' '))) BEIZH1 from ( select B.XJJE00,B.ZZJE00,B.YHKJE0,B.DZQBJE,B.CZY000,B.PLBH00,B.pjxh00 from SF_PJSYQK B where exists(select 1 from SF_BRJFB0 A where A.PJH000 = B.FZPJH0 and A.JFJE00 <> 0) --and A.JFJE00 <> 0 --考虑程序的问题,交费金额为零的也添加,这样相互关联就出现数据重复 and nvl(B.BDBZ00,'N')='N' --没有补打 and B.PLBH00 in (7,2) --门诊预交金收款单 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 (CZY000=ld_YGBH00 or ld_YGBH00=-1) and exists (select 1 from BM_BMBM00 where BMBH00=B.CZYKS0 and (trim(YYID00)=as_YYID00 or as_YYID00='0')) union all select B.XJJE00,B.ZZJE00,B.YHKJE0,B.DZQBJE,B.CZY000,B.PLBH00,B.pjxh00 from SF_PJSYQK B,SF_BRJFB0 A where exists(select 1 from SF_BRJFB0 A where A.PJH000 = B.FZPJH0 and A.JFJE00 <> 0) --and A.JFJE00 <> 0 --考虑程序的问题,交费金额为零的也添加,这样相互关联就出现数据重复 and nvl(B.BDBZ00,'N')='Y' --补打预交金 and B.PJH000=A.PJH000 and B.PLBH00 in (7,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 (B.CZY000=ld_YGBH00 or ld_YGBH00=-1) and exists (select 1 from BM_BMBM00 where BMBH00=B.CZYKS0 and (trim(YYID00)=as_YYID00 or as_YYID00='0')) ) B group by B.CZY000,b.PLBH00; begin for AA in C_YJJSKD loop if AA.PLBH00=7 then --预交金收款单的现金，银行卡，支票部分 Update BM_GHKSTJ set GHKS91=AA.XMJE01,GHKS95=AA.YHKSR1,GHKS51=AA.ZPSR01,BEIZH1=AA.BEIZH1 where ID0000=ld_ID0000 and DYID00=AA.CZY000; elsif AA.PLBH00=2 then --预交金退款单的现金，银行卡，支票部分 Update BM_GHKSTJ set GHKS92=AA.XMJE01,GHKS96=AA.YHKSR1,GHKS52=AA.ZPSR01,BEIZH2=AA.BEIZH1 where ID0000=ld_ID0000 and DYID00=AA.CZY000; end if; end loop; end; end if; --隔天补打预交按交费时间统计不统计在打印时间内--end--- --卡押金收入、卡押金退出、卡押金退出 declare cursor C_KYJYW is select b.czy000,sum(decode(sign(a.hjje00),1,a.hjje00,0)) kyjsr0,sum(decode(sign(a.hjje00),-1,a.hjje00,0)) kyjtc0 ,sum(a.hjje00) kyjce0 from sf_fymx00 a,sf_brfy00 b where a.djh000=b.djh000 and a.xmbh00=ls_ICSFDM and B.CZRQ00 >= as_KSRQ00 and B.CZRQ00 <= as_JSRQ00 and B.CZRQ00||B.CZSJ00 >= as_KSRQ00||as_KSSJ00 and B.CZRQ00||B.CZSJ00 <= as_JSRQ00||as_JSSJ00 and (B.CZY000=ld_YGBH00 or ld_YGBH00=-1) and exists (select 1 from BM_BMBM00 where BMBH00=B.CZYKS0 and (trim(YYID00)=as_YYID00 or as_YYID00='0')) group by b.czy000; begin for AA in C_KYJYW loop Update BM_GHKSTJ set GHKS60=AA.KYJSR0,GHKS61=AA.KYJTC0,GHKS67=AA.KYJCE0 where ID0000=ld_ID0000 and DYID00=AA.CZY000; end loop; end; --医保统筹账户转入 declare cursor C_YBZHZR is select JFCZY0 CZY000,NVL(sum(B.JFJE00),0) YBZHZR from SF_BRJFB0 B where B.PJH000 in (select A.FZPJH0 from SF_PJSYQK A where substr(A.PJXH00,1,1) <> '-' and A.DYRQ00 >= as_KSRQ00 and A.DYRQ00||A.DYSJ00 >= as_KSRQ00||as_KSSJ00 and A.DYRQ00 <= as_JSRQ00 and A.DYRQ00||A.DYSJ00 <= as_JSRQ00||as_JSSJ00 and (A.CZY000=ld_YGBH00 or ld_YGBH00=-1) and exists (select 1 from BM_BMBM00 where BMBH00=A.CZYKS0 and (trim(YYID00)=as_YYID00 or as_YYID00='0'))) and B.ZFFS00 in (3,4) group by B.JFCZY0; begin for AA in C_YBZHZR loop Update BM_GHKSTJ set GHKS63=AA.YBZHZR where ID0000=ld_ID0000 and DYID00=AA.CZY000; end loop; end; -- 票据收入, declare cursor C_XJ0000 is select NVL(sum(B.JFJE00),0) XMJE00,B.JFCZY0 CZY000,ZFFS00 from SF_BRJFB0 B where B.PJH000 in (select A.FZPJH0 from SF_PJSYQK A where 1=1 and A.DYRQ00 >= as_KSRQ00 and A.DYRQ00 <= as_JSRQ00 and A.DYRQ00||A.DYSJ00 >= as_KSRQ00||as_KSSJ00 and A.DYRQ00||A.DYSJ00 <= as_JSRQ00||as_JSSJ00 and ((ls_YYID00='225534' and A.PLBH00 in (0,1)) --泉州南安市医院（225534） or (ls_YYID00<>'225534' and A.PLBH00 in (1))) --and A.PLBH00 = 1 --and A.PLBH00 in (0,1) -- by MZSF-20140306-002 --and a.BDBZ00='N' and (A.CZY000=ld_YGBH00 or ld_YGBH00=-1) and exists (select 1 from BM_BMBM00 where BMBH00=A.CZYKS0 and (trim(YYID00)=as_YYID00 or as_YYID00='0'))) and B.ZFFS00 in (1,2,7,8) and B.jfrQ00 >= as_KSRQ00 and B.jfrQ00 <= as_JSRQ00 and B.jfrQ00||jfsj00 >= as_KSRQ00||as_KSSJ00 and B.jfrQ00||jfsj00 <= as_JSRQ00||as_JSSJ00 group by B.JFCZY0,ZFFS00; begin for AA in C_XJ0000 loop if AA.ZFFS00=1 then --1.现金 Update BM_GHKSTJ set GHKS05=nvl(GHKS05,0)+AA.XMJE00 where ID0000=ld_ID0000 and DYID00=AA.CZY000; elsif AA.ZFFS00=2 then --2.支票 Update BM_GHKSTJ set GHKS06=nvl(GHKS06,0)+AA.XMJE00 where ID0000=ld_ID0000 and DYID00=AA.CZY000; elsif AA.ZFFS00=8 then --8.银行卡 Update BM_GHKSTJ set GHKS07=nvl(GHKS07,0)+AA.XMJE00 where ID0000=ld_ID0000 and DYID00=AA.CZY000; elsif AA.ZFFS00=7 then --7.记账 Update BM_GHKSTJ set GHKS08=nvl(GHKS08,0)+AA.XMJE00 where ID0000=ld_ID0000 and DYID00=AA.CZY000; end if; end loop; end; declare cursor C_CXJZDH is --被冲消的结帐单号(可能被多次冲消) select CXJZDH,YBDJH0,BRID00,JZY000 from SF_JZB000 A where PJH000 in (select FZPJH0 from SF_PJSYQK B where B.PLBH00 = 1 and (B.CZY000=ld_YGBH00 or ld_YGBH00=-1) and exists (select 1 from BM_BMBM00 where BMBH00=B.CZYKS0 and (trim(YYID00)=as_YYID00 or as_YYID00='0')) 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) = '-' ); cursor C_CXBRJF is select NVL(sum(B.JFJE00),0) XMJE00,B.JFCZY0 CZY000,ZFFS00 from SF_BRJFB0 B where JZDH00 in (select k.jzdh00 from SF_JZB000 k start with k.jzdh00=ls_JZDH00 connect by prior k.cxjzdh=k.jzdh00) and B.ZFFS00 in (1,2,7,8) group by B.JFCZY0,ZFFS00; cursor C_CXBR00 is select NVL(sum(B.JFJE00),0) XMJE00,B.JFCZY0 CZY000,ZFFS00 from SF_BRJFB0 B where JZDH00=ls_JZDH00 and B.ZFFS00 in (1,2,7,8) group by B.JFCZY0,ZFFS00; begin for JZDH in C_CXJZDH loop ls_JZDH00:=JZDH.CXJZDH; SELECT sum(nvl(JFJE00,0)) into ls_xmje28 from SF_BRJFB0 where BRID00=jzdh.BRID00 and JZDH00=ls_JZDH00; if ls_xmje28<0 then for AA in C_CXBRJF loop if AA.ZFFS00=1 then --1.现金 Update BM_GHKSTJ set GHKS05=nvl(GHKS05,0)+AA.XMJE00 where ID0000=ld_ID0000 and DYID00=JZDH.JZY000; elsif AA.ZFFS00=2 then --2.支票 Update BM_GHKSTJ set GHKS06=nvl(GHKS06,0)+AA.XMJE00 where ID0000=ld_ID0000 and DYID00=JZDH.JZY000; elsif AA.ZFFS00=8 then --8.银行卡 Update BM_GHKSTJ set GHKS07=nvl(GHKS07,0)+AA.XMJE00 where ID0000=ld_ID0000 and DYID00=JZDH.JZY000; elsif AA.ZFFS00=7 then --7.记账 Update BM_GHKSTJ set GHKS08=nvl(GHKS08,0)+AA.XMJE00 where ID0000=ld_ID0000 and DYID00=JZDH.JZY000; end if; end loop; else for AA in C_CXBR00 loop if AA.ZFFS00=1 then --1.现金 Update BM_GHKSTJ set GHKS05=nvl(GHKS05,0)+AA.XMJE00 where ID0000=ld_ID0000 and DYID00=JZDH.JZY000; elsif AA.ZFFS00=2 then --2.支票 Update BM_GHKSTJ set GHKS06=nvl(GHKS06,0)+AA.XMJE00 where ID0000=ld_ID0000 and DYID00=JZDH.JZY000; elsif AA.ZFFS00=8 then --8.银行卡 Update BM_GHKSTJ set GHKS07=nvl(GHKS07,0)+AA.XMJE00 where ID0000=ld_ID0000 and DYID00=JZDH.JZY000; elsif AA.ZFFS00=7 then --7.记账 Update BM_GHKSTJ set GHKS08=nvl(GHKS08,0)+AA.XMJE00 where ID0000=ld_ID0000 and DYID00=JZDH.JZY000; end if; end loop; end if; end loop; end; -- 票据收入, 4.就诊卡结算 declare cursor C_YJJJS0 is select NVL(sum(A.HJJE00),0) XMJE09,JZY000 CZY000 from SF_JZB000 A where A.PJH000 in (select FZPJH0 from SF_PJSYQK B where B.PLBH00 = 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 substr(B.PJXH00,1,1) <> '-' and B.PJZT00 = '0' and (B.CZY000=ld_YGBH00 or ld_YGBH00=-1) and exists (select 1 from BM_BMBM00 where BMBH00=B.CZYKS0 and (trim(YYID00)=as_YYID00 or as_YYID00='0'))) and exists( select 1 from SF_BRFY00 where JZDH00 = A.JZDH00 and JFLBID = 1 ) group by JZY000; begin for AA in C_YJJJS0 loop Update BM_GHKSTJ set GHKS09=AA.XMJE09 where ID0000=ld_ID0000 and DYID00=AA.CZY000; end loop; end; --Update 票据收入, 5.医保结算 declare cursor C_YBJS0 is select NVL(sum(SF_SF_HQYBZFJE(A.JZDH00,A.BRID00,10,SBGRZH)+SF_SF_HQYBZFJE(A.JZDH00,A.BRID00,11,SBTCJJ)+GRZHZF+TCJJZF),0)XMJE10,JZY000 CZY000 from SF_JZB000 A where A.PJH000 in ( select FZPJH0 from SF_PJSYQK B where B.PLBH00 = 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 substr(B.PJXH00,1,1) <> '-' and (B.CZY000=ld_YGBH00 or ld_YGBH00=-1) and exists (select 1 from BM_BMBM00 where BMBH00=B.CZYKS0 and (trim(YYID00)=as_YYID00 or as_YYID00='0'))) group by A.JZY000; --and B.PJZT00 = '0'; begin for AA in C_YBJS0 loop Update BM_GHKSTJ set GHKS10=AA.XMJE10 where ID0000=ld_ID0000 and DYID00=AA.CZY000; end loop; end; --Update 其它结算 declare cursor C_QTJS0 is select NVL(sum(B.JFJE00),0) XMJE11,JFCZY0 CZY000 from SF_BRJFB0 B where B.PJH000 in ( select A.FZPJH0 from SF_PJSYQK A where substr(A.PJXH00,1,1) <> '-' and A.DYRQ00 >= as_KSRQ00 and A.DYRQ00||A.DYSJ00 >= as_KSRQ00||as_KSSJ00 and A.DYRQ00 <= as_JSRQ00 and A.DYRQ00||A.DYSJ00 <= as_JSRQ00||as_JSSJ00 and A.PLBH00 = 1 and (A.CZY000=ld_YGBH00 or ld_YGBH00=-1) and exists (select 1 from BM_BMBM00 where BMBH00=A.CZYKS0 and (trim(YYID00)=as_YYID00 or as_YYID00='0'))) and B.JFBZ00 = 0 --是收款的标志 and B.ZFFS00 not in (1,2,3,4,10,11,7,8,9) --支付方式:现金,转帐,医保帐户,医保统筹,商保帐户,商保统筹,银行卡,记账,逃跑欠费 group by B.JFCZY0; begin for AA in C_QTJS0 loop Update BM_GHKSTJ set GHKS11=AA.XMJE11 where ID0000=ld_ID0000 and DYID00=AA.CZY000; end loop; end; --计算退款的医保款,非自付款 declare cursor C_TK0000 is --自费病人负的结帐单号可能在缴费表找不到记录 select abs(nvl(ZFJE00,0)+nvl(JZJE00,0)) XMJE00,JZY000,CXJZDH,JZDH00, abs(nvl(SF_SF_HQYBZFJE(A.JZDH00,A.BRID00,10,SBGRZH),0)+nvl(SF_SF_HQYBZFJE(A.JZDH00,A.BRID00,11,SBTCJJ),0)+nvl(GRZHZF,0)+nvl(TCJJZF,0)) XMJE17 from SF_JZB000 A where a.PJH000 in (select FZPJH0 from SF_PJSYQK B where B.PLBH00 = 1 and (B.CZY000=ld_YGBH00 or ld_YGBH00=-1) and exists (select 1 from BM_BMBM00 where BMBH00=B.CZYKS0 and (trim(YYID00)=as_YYID00 or as_YYID00='0')) 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 SF_BRJFB0 where JFLBID = 0 and JZDH00 = A.JZDH00); begin for AA in C_TK0000 loop select count(*) into ls_count0 from sf_brjfb0 where JZDH00=AA.CXJZDH and JFLBID=0; if ls_count0>0 then Update BM_GHKSTJ set GHKS15=nvl(GHKS15,0)+AA.XMJE00,GHKS17=nvl(GHKS17,0)+AA.XMJE17 where ID0000=ld_ID0000 and DYID00=AA.JZY000; else Update BM_GHKSTJ set GHKS16=nvl(GHKS16,0)+AA.XMJE00,GHKS17=nvl(GHKS17,0)+AA.XMJE17 where ID0000=ld_ID0000 and DYID00=AA.JZY000; end if; end loop; end; --计算所有的银行卡收入,支票收入,记账收入 declare cursor C_ZPSR00 is select NVL(sum(B.JFJE00),0)XMJE20,JFCZY0 CZY000,ZFFS00 from SF_BRJFB0 B where B.PJH000 in (select A.FZPJH0 from SF_PJSYQK A where substr(A.PJXH00,1,1) <> '-' and A.DYRQ00 >= as_KSRQ00 and A.DYRQ00||A.DYSJ00 >= as_KSRQ00||as_KSSJ00 and A.DYRQ00 <= as_JSRQ00 and A.DYRQ00||A.DYSJ00 <= as_JSRQ00||as_JSSJ00 and A.pjzt00<>'1' --lml 20110705 by MZSF-20110701-001 and (A.CZY000=ld_YGBH00 or ld_YGBH00=-1) and exists (select 1 from BM_BMBM00 where BMBH00=A.CZYKS0 and (trim(YYID00)=as_YYID00 or as_YYID00='0'))) and B.ZFFS00 in (2,8,7) group by B.JFCZY0,ZFFS00; begin for AA in C_ZPSR00 loop if AA.ZFFS00=2 then --支票收入 Update BM_GHKSTJ set GHKS20=AA.XMJE20 where ID0000=ld_ID0000 and DYID00=AA.CZY000; elsif AA.ZFFS00=8 then --银行卡收入 Update BM_GHKSTJ set GHKS21=AA.XMJE20 where ID0000=ld_ID0000 and DYID00=AA.CZY000; elsif AA.ZFFS00=7 then --7.记账 Update BM_GHKSTJ set GHKS24=AA.XMJE20 where ID0000=ld_ID0000 and DYID00=AA.CZY000; end if; end loop; end; for sfy in CUR_SF_BRJFB0_SFY000 loop ls_ZPSR00 := 0 ; --支票收入 ls_YHKSR0 := 0 ; --银行卡收入 ls_BZZZQD := 0 ; --不足转账起点 ls_YHKJS0 := 0 ; --银行卡结算 ls_YBJS00 := 0 ; --医保结算 ls_JMBRYF := 0 ; --减免病人医药费 select BH0000 into ls_KSBH00 from BM_GHKSTJ_KSDY00 where ID0000 = ad_ID0000 and GHKSBH = sfy.YGBH00; --Update 现金挂号单收入,原来的求和有问题,主要是SF_PJSYQK同一个票据号,有一条记录,SF_BRJFB0有多条记录 ls_XMJE01 := NVL(sfy.XJSR00,0)+NVL(sfy.ZPSR00,0)+NVL(sfy.YHKSR0,0); --Update 预交金收入,或预交金收款单 if LS_YHSFKC='N' then --预交金收入是否包含银行卡金额 Y:包含 N:不包含 默认值:Y包含 ls_XMJE02 := NVL(sfy.XJSR01,0)+NVL(sfy.ZPSR01,0); ls_XMJE03 := ABS(NVL(sfy.XJSR02,0)+NVL(sfy.ZPSR02,0)); else ls_XMJE02 := NVL(sfy.XJSR01,0)+NVL(sfy.ZPSR01,0)+NVL(sfy.YHKSR1,0); ls_XMJE03 := ABS(NVL(sfy.XJSR02,0)+NVL(sfy.ZPSR02,0)+NVL(sfy.YHKSR2,0)); end if; --Update 预交金现金退出,或预交金退款单 --ls_XMJE03 := ABS(NVL(sfy.XJSR02,0)+NVL(sfy.ZPSR02,0)+NVL(sfy.YHKSR2,0)); --2005.05.25 ls_XMJE04 := NVL(sfy.XJSR03,0)+NVL(sfy.ZPSR03,0)+NVL(sfy.YHKSR3,0); --Update 票据收入, 1.现金 ls_XMJE05:=sfy.XMJE05; --Update 票据收入, 2.支票 --ls_XMJE06 := 0; ls_XMJE06:=sfy.XMJE06; --Update 银行卡收入,8.银行卡 ls_XMJE07:=sfy.XMJE07; --Update 记账,7.记账 ls_XMJE08:=sfy.XMJE08; --Update 票据收入, 4.就诊卡结算 ls_XMJE09:=sfy.XMJE09; --预交金差额 ls_YJJCE0 := ls_XMJE02-ls_XMJE03-ls_XMJE09; --Update 票据收入, 5.医保结算 ls_XMJE10:=sfy.XMJE10; --Update 其它结算 ls_XMJE11:=sfy.XMJE11; --补打收据款 ls_XMJE12 := 0; --换新收据 ls_XMJE13 := 0; --就诊卡退款 ls_XMJE16 :=sfy.XMJE16; --换旧收据 ls_XMJE18 := 0; --支票收入 ls_XMJE20 :=sfy.XMJE20; --银行卡收入,计算所有的银行卡的收入 ls_XMJE21 :=sfy.XMJE21; --不足转账起点 ls_BZZZQD := 0 ; ls_XMJE22 := 0 ; --银行卡结算 ls_XMJE23 := ls_XMJE07; --记账 --ls_XMJE24 := ls_XMJE08; --Update 记账,7.记账 ls_XMJE24:=sfy.XMJE24; --医保结算 ls_XMJE25 := ls_XMJE10; --减免病人医药费 ls_JMBRYF := 0 ; ls_XMJE26 := 0 ; --Update BM_JKKJMX set XMJE00 = NVL(ls_JMBRYF,0) where JKDH00 = ls_JKDH00 and BZ0000='0' and XMBH00 = '23'; --计算退款的医保款,非医保款 ls_XMJE15:=sfy.XMJE15; ls_XMJE17:=sfy.XMJE17; ls_XMJE14 := NVL(ls_XMJE15,0)+NVL(ls_XMJE16,0)+NVL(ls_XMJE17,0); --退款合计金额 --重新结算票据金额 ls_XMJE04 := NVL(ls_XMJE05,0)+NVL(ls_XMJE06,0)+NVL(ls_XMJE07,0)+NVL(ls_XMJE08,0)+NVL(ls_XMJE09,0)+NVL(ls_XMJE10,0)+NVL(ls_XMJE11,0)+NVL(ls_XMJE12,0)+NVL(ls_XMJE13,0); ls_KYJSR0 := sfy.KYJSR0; ls_KYJTC0 := sfy.KYJTC0; ls_KYJCE0 := sfy.KYJCE0; ls_YBZHZR := sfy.YBZHZR; --重新计算医保结算金额 --ls_XMJE10 := NVL(ls_XMJE10,0) - NVL(ls_XMJE17,0); --SP_EXECUTE_SQL('Update BM_GHKSTJ set GHKS'||ls_KSBH00||'='||to_char(NVL(ls_XMJE01,0))||',BEIZHU ='''||sfy.BEIZH0||''' where ID0000='||to_char(ad_ID0000)||' and GHKS199 = 1 '); --现金挂号单收入 SP_EXECUTE_SQL('Update BM_GHKSTJ set GHKS'||ls_KSBH00||'='||to_char(NVL(ls_XMJE02,0))||',BEIZHU ='''||sfy.BEIZH1||''' where ID0000='||to_char(ad_ID0000)||' and GHKS199 = 2 '); --预交金收入 SP_EXECUTE_SQL('Update BM_GHKSTJ set GHKS'||ls_KSBH00||'='||to_char(NVL(ls_XMJE03,0))||',BEIZHU ='''||sfy.BEIZH2||''' where ID0000='||to_char(ad_ID0000)||' and GHKS199 = 3 '); --预交金现金退出 SP_EXECUTE_SQL('Update BM_GHKSTJ set GHKS'||ls_KSBH00||'='||to_char(NVL(ls_XMJE04,0))||',BEIZHU ='''||sfy.BEIZH3||''' where ID0000='||to_char(ad_ID0000)||' and GHKS199 = 4 '); --票据收入 SP_EXECUTE_SQL('Update BM_GHKSTJ set GHKS'||ls_KSBH00||'='||to_char(NVL(ls_XMJE05,0))||' where ID0000='||to_char(ad_ID0000)||' and GHKS199 = 5 '); --现金 SP_EXECUTE_SQL('Update BM_GHKSTJ set GHKS'||ls_KSBH00||'='||to_char(NVL(ls_XMJE06,0))||' where ID0000='||to_char(ad_ID0000)||' and GHKS199 = 6 '); --支票 SP_EXECUTE_SQL('Update BM_GHKSTJ set GHKS'||ls_KSBH00||'='||to_char(NVL(ls_XMJE07,0))||' where ID0000='||to_char(ad_ID0000)||' and GHKS199 = 7 '); --银行卡 SP_EXECUTE_SQL('Update BM_GHKSTJ set GHKS'||ls_KSBH00||'='||to_char(NVL(ls_XMJE08,0))||' where ID0000='||to_char(ad_ID0000)||' and GHKS199 = 8 '); --记帐 SP_EXECUTE_SQL('Update BM_GHKSTJ set GHKS'||ls_KSBH00||'='||to_char(NVL(ls_XMJE09,0))||' where ID0000='||to_char(ad_ID0000)||' and GHKS199 = 9 '); --票据就诊卡 SP_EXECUTE_SQL('Update BM_GHKSTJ set GHKS'||ls_KSBH00||'='||to_char(NVL(ls_XMJE10,0))||' where ID0000='||to_char(ad_ID0000)||' and GHKS199 = 10'); --票据医保结算 SP_EXECUTE_SQL('Update BM_GHKSTJ set GHKS'||ls_KSBH00||'='||to_char(NVL(ls_XMJE11,0))||' where ID0000='||to_char(ad_ID0000)||' and GHKS199 = 11'); --其它结算 SP_EXECUTE_SQL('Update BM_GHKSTJ set GHKS'||ls_KSBH00||'='||to_char(NVL(ls_XMJE12,0))||' where ID0000='||to_char(ad_ID0000)||' and GHKS199 = 12'); --补打收据款 SP_EXECUTE_SQL('Update BM_GHKSTJ set GHKS'||ls_KSBH00||'='||to_char(NVL(ls_XMJE13,0))||' where ID0000='||to_char(ad_ID0000)||' and GHKS199 = 13'); --换新收据 SP_EXECUTE_SQL('Update BM_GHKSTJ set GHKS'||ls_KSBH00||'='||to_char(NVL(ls_XMJE14,0))||' where ID0000='||to_char(ad_ID0000)||' and GHKS199 = 14'); --退款收据 SP_EXECUTE_SQL('Update BM_GHKSTJ set GHKS'||ls_KSBH00||'='||to_char(NVL(ls_XMJE15,0))||' where ID0000='||to_char(ad_ID0000)||' and GHKS199 = 15'); --票据退款(现金) SP_EXECUTE_SQL('Update BM_GHKSTJ set GHKS'||ls_KSBH00||'='||to_char(NVL(ls_XMJE16,0))||' where ID0000='||to_char(ad_ID0000)||' and GHKS199 = 16'); --就诊卡退款 SP_EXECUTE_SQL('Update BM_GHKSTJ set GHKS'||ls_KSBH00||'='||to_char(NVL(ls_XMJE17,0))||' where ID0000='||to_char(ad_ID0000)||' and GHKS199 = 17'); --退医保款 SP_EXECUTE_SQL('Update BM_GHKSTJ set GHKS'||ls_KSBH00||'='||to_char(NVL(ls_XMJE18,0))||' where ID0000='||to_char(ad_ID0000)||' and GHKS199 = 18'); --换旧收据 SP_EXECUTE_SQL('Update BM_GHKSTJ set GHKS'||ls_KSBH00||'='||to_char(NVL(ls_XMJE20,0))||' where ID0000='||to_char(ad_ID0000)||' and GHKS199 = 20'); --支票收入 SP_EXECUTE_SQL('Update BM_GHKSTJ set GHKS'||ls_KSBH00||'='||to_char(NVL(ls_XMJE21,0))||' where ID0000='||to_char(ad_ID0000)||' and GHKS199 = 21'); --银行卡收入 SP_EXECUTE_SQL('Update BM_GHKSTJ set GHKS'||ls_KSBH00||'='||to_char(NVL(ls_XMJE22,0))||' where ID0000='||to_char(ad_ID0000)||' and GHKS199 = 22'); --不足转账起点 SP_EXECUTE_SQL('Update BM_GHKSTJ set GHKS'||ls_KSBH00||'='||to_char(NVL(ls_XMJE23,0))||' where ID0000='||to_char(ad_ID0000)||' and GHKS199 = 23'); --银行卡结算 SP_EXECUTE_SQL('Update BM_GHKSTJ set GHKS'||ls_KSBH00||'='||to_char(NVL(ls_XMJE24,0))||' where ID0000='||to_char(ad_ID0000)||' and GHKS199 = 24'); --记账 SP_EXECUTE_SQL('Update BM_GHKSTJ set GHKS'||ls_KSBH00||'='||to_char(NVL(ls_XMJE10,0)-NVL(ls_XMJE17,0))||' where ID0000='||to_char(ad_ID0000)||' and GHKS199 = 25'); --医保结算 SP_EXECUTE_SQL('Update BM_GHKSTJ set GHKS'||ls_KSBH00||'='||to_char(NVL(ls_XMJE26,0))||' where ID0000='||to_char(ad_ID0000)||' and GHKS199 = 26'); --减面医药费 SP_EXECUTE_SQL('Update BM_GHKSTJ set GHKS'||ls_KSBH00||'='||to_char(NVL(ls_KYJSR0,0))||' where ID0000='||to_char(ad_ID0000)||' and GHKS199 = 2.1'); --卡押金收入 SP_EXECUTE_SQL('Update BM_GHKSTJ set GHKS'||ls_KSBH00||'='||to_char(NVL(ls_KYJTC0,0))||' where ID0000='||to_char(ad_ID0000)||' and GHKS199 = 3.1'); --卡押金退出 SP_EXECUTE_SQL('Update BM_GHKSTJ set GHKS'||ls_KSBH00||'='||to_char(NVL(ls_YBZHZR,0))||' where ID0000='||to_char(ad_ID0000)||' and GHKS199 = 3.3'); --医保账户转入 SP_EXECUTE_SQL('Update BM_GHKSTJ set GHKS'||ls_KSBH00||'='||to_char(NVL(ls_XMJE09,0))||' where ID0000='||to_char(ad_ID0000)||' and GHKS199 = 3.5'); --预交金结算 SP_EXECUTE_SQL('Update BM_GHKSTJ set GHKS'||ls_KSBH00||'='||to_char(NVL(ls_YJJCE0,0))||' where ID0000='||to_char(ad_ID0000)||' and GHKS199 = 3.6'); --本期预交金余额 SP_EXECUTE_SQL('Update BM_GHKSTJ set GHKS'||ls_KSBH00||'='||to_char(NVL(ls_KYJCE0,0))||' where ID0000='||to_char(ad_ID0000)||' and GHKS199 = 3.7'); --卡押金差额 --计算应缴金额 MZSF-20080805-003 --19, 7、应缴金额 select sum(NVL(decode(ls_KSBH00, '01',GHKS01,'02',GHKS02,'03',GHKS03,'04',GHKS04,'05',GHKS05,'06',GHKS06,'07',GHKS07,'08',GHKS08,'09',GHKS09,'10',GHKS10, '11',GHKS11,'12',GHKS12,'13',GHKS13,'14',GHKS14,'15',GHKS15,'16',GHKS16,'17',GHKS17,'18',GHKS18,'19',GHKS19,'20',GHKS20, '21',GHKS21,'22',GHKS22,'23',GHKS23,'24',GHKS24,'25',GHKS25,'26',GHKS26,'27',GHKS27,'28',GHKS28,'29',GHKS29,'30',GHKS30, '31',GHKS31,'32',GHKS32,'33',GHKS33,'34',GHKS34,'35',GHKS35,'36',GHKS36,'37',GHKS37,'38',GHKS38,'39',GHKS39,'40',GHKS40, '41',GHKS41,'42',GHKS42,'43',GHKS43,'44',GHKS44,'45',GHKS45,'46',GHKS46,'47',GHKS47,'48',GHKS48,'49',GHKS49,'50',GHKS50, '51',GHKS51,'52',GHKS52,'53',GHKS53,'54',GHKS54,'55',GHKS55,'56',GHKS56,'57',GHKS57,'58',GHKS58,'59',GHKS59,'60',GHKS60, '61',GHKS61,'62',GHKS62,'63',GHKS63,'64',GHKS64,'65',GHKS65,'66',GHKS66,'67',GHKS67,'68',GHKS68,'69',GHKS69,'70',GHKS70, '71',GHKS71,'72',GHKS72,'73',GHKS73,'74',GHKS74,'75',GHKS75,'76',GHKS76,'77',GHKS77,'78',GHKS78,'79',GHKS79,'80',GHKS80, '81',GHKS81,'82',GHKS82,'83',GHKS83,'84',GHKS84,'85',GHKS85,'86',GHKS86,'87',GHKS87,'88',GHKS88,'89',GHKS89,'90',GHKS90, 0),0)) into ls_XMJE19 from BM_GHKSTJ where ID0000 = ad_ID0000 and ( (LS_YHSFKC<>'N' and GHKS199 in (1,2,5,6,7,8,10,12)) or (LS_YHSFKC='N' and GHKS199 in (1,2,5,6,7,8,10,12,21)) ); --预交金收入是否包含银行卡金额 Y:包含 N:不包含 默认值:Y包含 --LS_YHSFKC =N 时预交金收入不包含银行卡 所以应缴金额应把银行卡金额加进去 21 --LS_YHSFKC =Y 时预交金收入包含银行卡 所以应缴金额不把银行卡金额加进去 21 --28, 16、9~14合计 select sum(NVL(decode(ls_KSBH00,'01',GHKS01,'02',GHKS02,'03',GHKS03,'04',GHKS04,'05',GHKS05,'06',GHKS06,'07',GHKS07,'08',GHKS08,'09',GHKS09,'10',GHKS10, '11',GHKS11,'12',GHKS12,'13',GHKS13,'14',GHKS14,'15',GHKS15,'16',GHKS16,'17',GHKS17,'18',GHKS18,'19',GHKS19,'20',GHKS20, '21',GHKS21,'22',GHKS22,'23',GHKS23,'24',GHKS24,'25',GHKS25,'26',GHKS26,'27',GHKS27,'28',GHKS28,'29',GHKS29,'30',GHKS30, '31',GHKS31,'32',GHKS32,'33',GHKS33,'34',GHKS34,'35',GHKS35,'36',GHKS36,'37',GHKS37,'38',GHKS38,'39',GHKS39,'40',GHKS40, '41',GHKS41,'42',GHKS42,'43',GHKS43,'44',GHKS44,'45',GHKS45,'46',GHKS46,'47',GHKS47,'48',GHKS48,'49',GHKS49,'50',GHKS50, '51',GHKS51,'52',GHKS52,'53',GHKS53,'54',GHKS54,'55',GHKS55,'56',GHKS56,'57',GHKS57,'58',GHKS58,'59',GHKS59,'60',GHKS60, '61',GHKS61,'62',GHKS62,'63',GHKS63,'64',GHKS64,'65',GHKS65,'66',GHKS66,'67',GHKS67,'68',GHKS68,'69',GHKS69,'70',GHKS70, '71',GHKS71,'72',GHKS72,'73',GHKS73,'74',GHKS74,'75',GHKS75,'76',GHKS76,'77',GHKS77,'78',GHKS78,'79',GHKS79,'80',GHKS80, '81',GHKS81,'82',GHKS82,'83',GHKS83,'84',GHKS84,'85',GHKS85,'86',GHKS86,'87',GHKS87,'88',GHKS88,'89',GHKS89,'90',GHKS90, 0),0)) into ls_XMJE28 from BM_GHKSTJ where ID0000 = ad_ID0000 and GHKS199 in (3,15,17); --27, 15、实交现金 select sum(NVL(decode(ls_KSBH00,'01',GHKS01,'02',GHKS02,'03',GHKS03,'04',GHKS04,'05',GHKS05,'06',GHKS06,'07',GHKS07,'08',GHKS08,'09',GHKS09,'10',GHKS10, '11',GHKS11,'12',GHKS12,'13',GHKS13,'14',GHKS14,'15',GHKS15,'16',GHKS16,'17',GHKS17,'18',GHKS18,'19',GHKS19,'20',GHKS20, '21',GHKS21,'22',GHKS22,'23',GHKS23,'24',GHKS24,'25',GHKS25,'26',GHKS26,'27',GHKS27,'28',GHKS28,'29',GHKS29,'30',GHKS30, '31',GHKS31,'32',GHKS32,'33',GHKS33,'34',GHKS34,'35',GHKS35,'36',GHKS36,'37',GHKS37,'38',GHKS38,'39',GHKS39,'40',GHKS40, '41',GHKS41,'42',GHKS42,'43',GHKS43,'44',GHKS44,'45',GHKS45,'46',GHKS46,'47',GHKS47,'48',GHKS48,'49',GHKS49,'50',GHKS50, '51',GHKS51,'52',GHKS52,'53',GHKS53,'54',GHKS54,'55',GHKS55,'56',GHKS56,'57',GHKS57,'58',GHKS58,'59',GHKS59,'60',GHKS60, '61',GHKS61,'62',GHKS62,'63',GHKS63,'64',GHKS64,'65',GHKS65,'66',GHKS66,'67',GHKS67,'68',GHKS68,'69',GHKS69,'70',GHKS70, '71',GHKS71,'72',GHKS72,'73',GHKS73,'74',GHKS74,'75',GHKS75,'76',GHKS76,'77',GHKS77,'78',GHKS78,'79',GHKS79,'80',GHKS80, '81',GHKS81,'82',GHKS82,'83',GHKS83,'84',GHKS84,'85',GHKS85,'86',GHKS86,'87',GHKS87,'88',GHKS88,'89',GHKS89,'90',GHKS90, 0),0)) into ls_XMJE27 from BM_GHKSTJ where ID0000 = ad_ID0000 and GHKS199 in (20,21,22,23,24,25,26); SP_EXECUTE_SQL('Update BM_GHKSTJ set GHKS'||ls_KSBH00||' = '||to_char(NVL(ls_XMJE19,0) - NVL(ls_XMJE28,0))||' where ID0000 = '||to_char(ad_ID0000)||' and GHKS199 = 19'); --应缴金额 SP_EXECUTE_SQL('Update BM_GHKSTJ set GHKS'||ls_KSBH00||' = '||to_char(NVL(ls_XMJE19,0) - NVL(ls_XMJE28,0))||' where ID0000 = '||to_char(ad_ID0000)||' and GHKS199 = 28'); --应缴金额 --发现退医保款有问题,所以添加了医保款的部分 Lv_SJXJ00:=NVL(ls_XMJE19,0) - NVL(ls_XMJE28,0) - NVL(ls_XMJE27,0); SP_EXECUTE_SQL('Update BM_GHKSTJ set GHKS'||ls_KSBH00||' = '||to_char(Lv_SJXJ00)||' where ID0000 = '||to_char(ad_ID0000)||' and GHKS199 = 27'); --实交现金 SP_EXECUTE_SQL('Update BM_GHKSTJ set GHKS'||ls_KSBH00||' = '||to_char(NVL(ls_XMJE02,0) - NVL(ls_XMJE03,0))||' where ID0000 = '||to_char(ad_ID0000)||' and GHKS199 = 3.2'); --本期实收现金 SP_EXECUTE_SQL('Update BM_GHKSTJ set GHKS'||ls_KSBH00||' = '||to_char(NVL(ls_XMJE02,0) - NVL(ls_XMJE03,0) - NVL(ls_YBZHZR,0))||' where ID0000 = '||to_char(ad_ID0000)||' and GHKS199 = 3.4'); --本期应交现金 END LOOP; 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)+ NVL(GHKS80,0) + NVL(GHKS81,0)+ NVL(GHKS82,0)+ NVL(GHKS83,0)+ NVL(GHKS84,0)+ NVL(GHKS85,0)+ NVL(GHKS86,0)+ NVL(GHKS87,0)+ NVL(GHKS88,0)+ NVL(GHKS89,0)+ NVL(GHKS90,0) where ID0000 = ad_ID0000; select nvl(sum(a.hjje00),0) into ls_bqywsr from sf_fymx00 a,sf_brfy00 b where a.djh000=b.djh000 and B.CZRQ00 >= as_KSRQ00 and B.CZRQ00 <= as_JSRQ00 and B.CZRQ00||B.CZSJ00 >= as_KSRQ00||as_KSSJ00 and B.CZRQ00||B.CZSJ00 <= as_JSRQ00||as_JSSJ00 and exists (select 1 from BM_BMBM00 where BMBH00=B.CZYKS0 and (trim(YYID00)=as_YYID00 or as_YYID00='0')); UPDATE BM_GHKSTJ SET XMHJ00=ls_bqywsr where ID0000 = ad_ID0000 AND GHKS199=3.91; --本期业务收入 select NVL(SUM(XMHJ00),0) into ls_BQYJSR FROM BM_GHKSTJ WHERE ID0000 = ad_ID0000 AND GHKS199=4; --本期已结票据收入 ls_bqwjsr:= ls_bqywsr-ls_BQYJSR; --未结算收入=本期已结收入-本期结算金额 UPDATE BM_GHKSTJ SET XMHJ00=ls_bqwjsr where ID0000 = ad_ID0000 AND GHKS199=17.1; --未结算收入 --上期预交收入余额 直接从预交金余额中间表取，如果没有，再从业务表取 --select nvl(sum(sqjc00),0) into ls_SQYJSR from sf_yjjye0 where czrq00=as_KSRQ00 and brid00=0; --if ls_SQYJSR=0 then select nvl(sum(jfje00),0) into ls_SQHJYJ from sf_brjfb0 where jfrq00<=as_KSRQ00 and jfrq00||jfsj00 0 --考虑程序的问题,交费金额为零的也添加,这样相互关联就出现数据重复 and B.PLBH00 = 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 B.PJZT00 = '0' --有效的票据 and (JFCZY0=ld_YGBH00 or ld_YGBH00=-1) and ( as_YYID00='0' or exists (select 1 from BM_BMBM00 where BMBH00=A.CZYKS0 and trim(YYID00)=as_YYID00 )) group by A.JFCZY0; begin for AA in C_XJGH00 loop --现金挂号收入的现金部分,电子钱包未分开 select count(*) into ls_cou from BM_YGBM00 where YGBH00=AA.CZY000 and (YGXZ00 like '%3%' or YGXZ00 like '%5%'); if ls_cou>0 then Update BM_GHKSTJ set GHKS90=AA.XMJE00,BEIZHU=AA.BEIZH0 where ID0000=ld_ID0000 and DYID00=AA.CZY000; else Update BM_GHKSTJ set GHKS90=nvl(GHKS90,0)+AA.XMJE00,BEIZHU=nvl(BEIZHU,'')+AA.BEIZH0 where ID0000=ld_ID0000 and DYID00=-1; end if; end loop; end; --预交金收入,预交金收款单或预交金退款单 -- by MZSF-20110419-003 莆田学院附属医院(223368) 暂时独立统计，因为其它医院暂时保留原来统计方式 --隔天补打预交也统计在内--begin--- if LS_XJSRSHBSFTJ='Y' then declare cursor C_YJJSKD is select NVL(sum(B.XJJE00),0)XMJE01 ,NVL(sum(B.ZZJE00),0) ZPSR01,NVL(sum(B.YHKJE0),0) YHKSR1,NVL(sum(B.DZQBJE),0) DZQB01,CZY000,PLBH00, ltrim(min(lpad(trim(B.pjxh00),30,' ')))||' - '||ltrim(max(lpad(trim(B.pjxh00),30,' '))) BEIZH1 from SF_PJSYQK B where exists(select 1 from SF_BRJFB0 A where A.PJH000 = B.FZPJH0 and A.JFJE00 <> 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 LS_XJSRSHBSFTJ='N') or (LS_XJSRSHBSFTJ='Y')) and ( as_YYID00='0' or exists (select 1 from BM_BMBM00 where BMBH00=A.CZYKS0 and trim(YYID00)=as_YYID00)) ) --and A.JFJE00 <> 0 --考虑程序的问题,交费金额为零的也添加,这样相互关联就出现数据重复 and B.PLBH00 in (7,2) --门诊预交金收款单 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 (CZY000=ld_YGBH00 or ld_YGBH00=-1) group by B.CZY000,PLBH00; --and substr(B.PJXH00,1,1) <> '-',注意:与住院不同 --and A.JZDH00 <> 1 --作废的门诊预交金收款票据不计算在内！ begin for AA in C_YJJSKD loop select count(*) into ls_cou from BM_YGBM00 where YGBH00=AA.CZY000 and (YGXZ00 like '%3%' or YGXZ00 like '%5%'); if ls_cou>0 then if AA.PLBH00=7 then --预交金收款单的现金，银行卡，支票部分 Update BM_GHKSTJ set GHKS91=AA.XMJE01,GHKS95=AA.YHKSR1,GHKS51=AA.ZPSR01,BEIZH1=AA.BEIZH1,GHKS71=AA.DZQB01 where ID0000=ld_ID0000 and DYID00=AA.CZY000; elsif AA.PLBH00=2 then --预交金退款单的现金，银行卡，支票部分 Update BM_GHKSTJ set GHKS92=AA.XMJE01,GHKS96=AA.YHKSR1,GHKS52=AA.ZPSR01,BEIZH2=AA.BEIZH1,GHKS72=AA.DZQB01 where ID0000=ld_ID0000 and DYID00=AA.CZY000; end if; else if AA.PLBH00=7 then --预交金收款单的现金，银行卡，支票部分 Update BM_GHKSTJ set GHKS91=nvl(GHKS91,0)+AA.XMJE01,GHKS95=nvl(GHKS95,0)+AA.YHKSR1,GHKS51=nvl(GHKS51,0)+AA.ZPSR01,BEIZH1=substr(nvl(BEIZH1,0)||','||AA.BEIZH1,1,200),GHKS71=nvl(GHKS71,0)+AA.DZQB01 where ID0000=ld_ID0000 and DYID00=-1; elsif AA.PLBH00=2 then --预交金退款单的现金，银行卡，支票部分 Update BM_GHKSTJ set GHKS92=nvl(GHKS92,0)+AA.XMJE01,GHKS96=nvl(GHKS96,0)+AA.YHKSR1,GHKS52=nvl(GHKS52,0)+AA.ZPSR01,BEIZH2=substr(nvl(BEIZH1,0)||','||AA.BEIZH1,1,200),GHKS72=nvl(GHKS72,0)+AA.DZQB01 where ID0000=ld_ID0000 and DYID00=-1; end if; end if; end loop; end; --隔天补打预交也统计在内--end--- else --隔天补打预交按交费时间统计不统计在打印时间内--begin--- declare cursor C_YJJSKD is select NVL(sum(B.XJJE00),0)XMJE01 ,NVL(sum(B.ZZJE00),0) ZPSR01,NVL(sum(B.YHKJE0),0) YHKSR1,NVL(sum(B.DZQBJE),0) DZQB01,CZY000,PLBH00, ltrim(min(lpad(trim(B.pjxh00),30,' ')))||' - '||ltrim(max(lpad(trim(B.pjxh00),30,' '))) BEIZH1 from ( select B.XJJE00,B.ZZJE00,B.YHKJE0,B.DZQBJE,B.CZY000,B.PLBH00,B.pjxh00 from SF_PJSYQK B where exists(select 1 from SF_BRJFB0 A where A.PJH000 = B.FZPJH0 and A.JFJE00 <> 0) --and A.JFJE00 <> 0 --考虑程序的问题,交费金额为零的也添加,这样相互关联就出现数据重复 and nvl(B.BDBZ00,'N')='N' --没有补打 and B.PLBH00 in (7,2) --门诊预交金收款单 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 (CZY000=ld_YGBH00 or ld_YGBH00=-1) and ( as_YYID00='0' or exists (select 1 from BM_BMBM00 where BMBH00=B.CZYKS0 and trim(YYID00)=as_YYID00)) union all select B.XJJE00,B.ZZJE00,B.YHKJE0,B.DZQBJE,B.CZY000,B.PLBH00,B.pjxh00 from SF_PJSYQK B,SF_BRJFB0 A where exists(select 1 from SF_BRJFB0 A where A.PJH000 = B.FZPJH0 and A.JFJE00 <> 0) --and A.JFJE00 <> 0 --考虑程序的问题,交费金额为零的也添加,这样相互关联就出现数据重复 and nvl(B.BDBZ00,'N')='Y' --补打预交金 and B.PJH000=A.PJH000 and B.PLBH00 in (7,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 (B.CZY000=ld_YGBH00 or ld_YGBH00=-1) and ( as_YYID00='0' or exists (select 1 from BM_BMBM00 where BMBH00=B.CZYKS0 and trim(YYID00)=as_YYID00)) ) B group by B.CZY000,b.PLBH00; begin for AA in C_YJJSKD loop select count(*) into ls_cou from BM_YGBM00 where YGBH00=AA.CZY000 and (YGXZ00 like '%3%' or YGXZ00 like '%5%'); if ls_cou>0 then if AA.PLBH00=7 then --预交金收款单的现金，银行卡，支票部分 Update BM_GHKSTJ set GHKS91=AA.XMJE01,GHKS95=AA.YHKSR1,GHKS51=AA.ZPSR01,BEIZH1=AA.BEIZH1,GHKS71=AA.DZQB01 where ID0000=ld_ID0000 and DYID00=AA.CZY000; elsif AA.PLBH00=2 then --预交金退款单的现金，银行卡，支票部分 Update BM_GHKSTJ set GHKS92=AA.XMJE01,GHKS96=AA.YHKSR1,GHKS52=AA.ZPSR01,BEIZH2=AA.BEIZH1,GHKS72=AA.DZQB01 where ID0000=ld_ID0000 and DYID00=AA.CZY000; end if; else if AA.PLBH00=7 then --预交金收款单的现金，银行卡，支票部分 Update BM_GHKSTJ set GHKS91=nvl(GHKS91,0)+AA.XMJE01,GHKS95=nvl(GHKS95,0)+AA.YHKSR1,GHKS51=nvl(GHKS51,0)+AA.ZPSR01,BEIZH1=substr(nvl(BEIZH1,0)||','||AA.BEIZH1,1,200),GHKS71=nvl(GHKS71,0)+AA.DZQB01 where ID0000=ld_ID0000 and DYID00=-1; elsif AA.PLBH00=2 then --预交金退款单的现金，银行卡，支票部分 Update BM_GHKSTJ set GHKS92=nvl(GHKS92,0)+AA.XMJE01,GHKS96=nvl(GHKS96,0)+AA.YHKSR1,GHKS52=nvl(GHKS52,0)+AA.ZPSR01,BEIZH2=substr(nvl(BEIZH1,0)||','||AA.BEIZH1,1,200),GHKS72=nvl(GHKS72,0)+AA.DZQB01 where ID0000=ld_ID0000 and DYID00=-1; end if; end if; end loop; end; end if; --隔天补打预交按交费时间统计不统计在打印时间内--end--- -- 票据收入, declare cursor C_XJ0000 is select NVL(sum(B.JFJE00),0) XMJE00,B.JFCZY0 CZY000,ZFFS00,JFLBID from SF_BRJFB0 B where B.PJH000 in (select A.FZPJH0 from SF_PJSYQK A where A.DYRQ00 >= as_KSRQ00 and A.DYRQ00 <= as_JSRQ00 and A.DYRQ00||A.DYSJ00 >= as_KSRQ00||as_KSSJ00 and A.DYRQ00||A.DYSJ00 <= as_JSRQ00||as_JSSJ00 and ((ls_YYID00='225534' and A.PLBH00 in (0,1)) --泉州南安市医院（225534） or (ls_YYID00<>'225534' and A.PLBH00 in (1))) --and A.PLBH00 = 1 --and A.PLBH00 in (0,1) -- by MZSF-20140306-002 --and a.BDBZ00='N' and (A.CZY000=ld_YGBH00 or ld_YGBH00=-1) and (as_YYID00='0' or exists (select 1 from BM_BMBM00 where BMBH00=A.CZYKS0 and trim(YYID00)=as_YYID00 ))) and B.ZFFS00 in (1,2,7,8,18,24,25,26,28) and (B.JFLBID=1 or( B.JFLBID=0 and B.JZDH00>0 and B.JFJE00>0)) AND b.jfrq00 >= as_KSRQ00 and B.jfrq00 <= as_JSRQ00 and B.jfrq00||B.jfSJ00 >= as_KSRQ00||as_KSSJ00 and B.jfrq00||B.jfSJ00 <= as_JSRQ00||as_JSSJ00 group by B.JFCZY0,ZFFS00,B.JFLBID; begin for AA in C_XJ0000 loop select count(*) into ls_cou from BM_YGBM00 where YGBH00=AA.CZY000 and (YGXZ00 like '%3%' or YGXZ00 like '%5%'); if ls_cou>0 then if AA.ZFFS00=1 or (AA.ZFFS00 in(24,25) and AA.JFLBID=0) then --1.现金 Update BM_GHKSTJ set GHKS05=nvl(GHKS05,0)+AA.XMJE00 where ID0000=ld_ID0000 and DYID00=AA.CZY000; elsif AA.ZFFS00=2 then --2.支票 Update BM_GHKSTJ set GHKS06=nvl(GHKS06,0)+AA.XMJE00 where ID0000=ld_ID0000 and DYID00=AA.CZY000; elsif AA.ZFFS00=8 then --8.银行卡 Update BM_GHKSTJ set GHKS07=nvl(GHKS07,0)+AA.XMJE00 where ID0000=ld_ID0000 and DYID00=AA.CZY000; elsif AA.ZFFS00=7 then --7.记账 Update BM_GHKSTJ set GHKS08=nvl(GHKS08,0)+AA.XMJE00 where ID0000=ld_ID0000 and DYID00=AA.CZY000; elsif AA.ZFFS00=18 then --18.电子钱包 Update BM_GHKSTJ set GHKS73=nvl(GHKS73,0)+AA.XMJE00 where ID0000=ld_ID0000 and DYID00=AA.CZY000; elsif AA.ZFFS00=24 then --24.微信支付 Update BM_GHKSTJ set GHKS54=nvl(GHKS54,0)+AA.XMJE00 where ID0000=ld_ID0000 and DYID00=AA.CZY000; elsif AA.ZFFS00=25 then --25.支付宝支付 Update BM_GHKSTJ set GHKS58=nvl(GHKS58,0)+AA.XMJE00 where ID0000=ld_ID0000 and DYID00=AA.CZY000; elsif AA.ZFFS00=26 then --26.民生通支付 Update BM_GHKSTJ set GHKS56=nvl(GHKS56,0)+AA.XMJE00 where ID0000=ld_ID0000 and DYID00=AA.CZY000; elsif AA.ZFFS00=28 then --28.平台支付/区域预交金 Update BM_GHKSTJ set GHKS40=nvl(GHKS40,0)+AA.XMJE00 where ID0000=ld_ID0000 and DYID00=AA.CZY000; end if; else if AA.ZFFS00=1 or (AA.ZFFS00 in(24,25) and AA.JFLBID=0) then --1.现金 Update BM_GHKSTJ set GHKS05=nvl(GHKS05,0)+AA.XMJE00 where ID0000=ld_ID0000 and DYID00=-1; elsif AA.ZFFS00=2 then --2.支票 Update BM_GHKSTJ set GHKS06=nvl(GHKS06,0)+AA.XMJE00 where ID0000=ld_ID0000 and DYID00=-1; elsif AA.ZFFS00=8 then --8.银行卡 Update BM_GHKSTJ set GHKS07=nvl(GHKS07,0)+AA.XMJE00 where ID0000=ld_ID0000 and DYID00=-1; elsif AA.ZFFS00=7 then --7.记账 Update BM_GHKSTJ set GHKS08=nvl(GHKS08,0)+AA.XMJE00 where ID0000=ld_ID0000 and DYID00=-1; elsif AA.ZFFS00=18 then --18.电子钱包 Update BM_GHKSTJ set GHKS73=nvl(GHKS73,0)+AA.XMJE00 where ID0000=ld_ID0000 and DYID00=-1; elsif AA.ZFFS00=24 then --24.微信支付 Update BM_GHKSTJ set GHKS54=nvl(GHKS54,0)+AA.XMJE00 where ID0000=ld_ID0000 and DYID00=-1; elsif AA.ZFFS00=25 then --25.支付宝支付 Update BM_GHKSTJ set GHKS58=nvl(GHKS58,0)+AA.XMJE00 where ID0000=ld_ID0000 and DYID00=-1; elsif AA.ZFFS00=26 then --26.民生通支付 Update BM_GHKSTJ set GHKS56=nvl(GHKS56,0)+AA.XMJE00 where ID0000=ld_ID0000 and DYID00=-1; elsif AA.ZFFS00=28 then --28.平台支付/区域预交金 Update BM_GHKSTJ set GHKS40=nvl(GHKS40,0)+AA.XMJE00 where ID0000=ld_ID0000 and DYID00=-1; end if; end if; end loop; end; declare cursor C_CXJZDH is --被冲消的结帐单号(可能被多次冲消) select CXJZDH,YBDJH0,BRID00,JZY000 from SF_JZB000 A where PJH000 in (select FZPJH0 from SF_PJSYQK B where B.PLBH00 = 1 and (B.CZY000=ld_YGBH00 or ld_YGBH00=-1) and (as_YYID00='0' or exists (select 1 from BM_BMBM00 where BMBH00=B.CZYKS0 and trim(YYID00)=as_YYID00 )) 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 A.CXJZDH<>0; cursor C_CXBRJF is select NVL(sum(B.JFJE00),0) XMJE00,B.JFCZY0 CZY000,ZFFS00 from SF_BRJFB0 B where JZDH00 in (select k.jzdh00 from SF_JZB000 k start with k.jzdh00=ls_JZDH00 connect by prior k.cxjzdh=k.jzdh00) and B.ZFFS00 in (1,2,7,8,18,24,25,26,28) group by B.JFCZY0,ZFFS00; cursor C_CXBR00 is select NVL(sum(B.JFJE00),0) XMJE00,B.JFCZY0 CZY000,ZFFS00 from SF_BRJFB0 B where JZDH00=ls_JZDH00 and B.ZFFS00 in (1,2,7,8,18,24,25,26,28) group by B.JFCZY0,ZFFS00; begin for JZDH in C_CXJZDH loop ls_JZDH00:=JZDH.CXJZDH; SELECT sum(nvl(JFJE00,0)) into ls_xmje28 from SF_BRJFB0 where BRID00=jzdh.BRID00 and JZDH00=ls_JZDH00; if ls_xmje28<0 then for AA in C_CXBRJF loop select count(*) into ls_cou from BM_YGBM00 where YGBH00=JZDH.JZY000 and (YGXZ00 like '%3%' or YGXZ00 like '%5%'); if ls_cou>0 then if AA.ZFFS00=1 then --1.现金 Update BM_GHKSTJ set GHKS05=nvl(GHKS05,0)+AA.XMJE00 where ID0000=ld_ID0000 and DYID00=JZDH.JZY000; --原为AA.CZY000现改为JZDH.JZY000 2008.08.16 zhr elsif AA.ZFFS00=2 then --2.支票 Update BM_GHKSTJ set GHKS06=nvl(GHKS06,0)+AA.XMJE00 where ID0000=ld_ID0000 and DYID00=JZDH.JZY000; elsif AA.ZFFS00=8 then --8.银行卡 Update BM_GHKSTJ set GHKS07=nvl(GHKS07,0)+AA.XMJE00 where ID0000=ld_ID0000 and DYID00=JZDH.JZY000; elsif AA.ZFFS00=7 then --7.记账 Update BM_GHKSTJ set GHKS08=nvl(GHKS08,0)+AA.XMJE00 where ID0000=ld_ID0000 and DYID00=JZDH.JZY000; elsif AA.ZFFS00=18 then --18.电子钱包 Update BM_GHKSTJ set GHKS73=nvl(GHKS73,0)+AA.XMJE00 where ID0000=ld_ID0000 and DYID00=JZDH.JZY000; elsif AA.ZFFS00=24 then --24.微信 Update BM_GHKSTJ set GHKS54=nvl(GHKS54,0)+AA.XMJE00 where ID0000=ld_ID0000 and DYID00=JZDH.JZY000; elsif AA.ZFFS00=25 then --25.支付宝 Update BM_GHKSTJ set GHKS58=nvl(GHKS58,0)+AA.XMJE00 where ID0000=ld_ID0000 and DYID00=JZDH.JZY000; elsif AA.ZFFS00=26 then --26.民生通 Update BM_GHKSTJ set GHKS56=nvl(GHKS56,0)+AA.XMJE00 where ID0000=ld_ID0000 and DYID00=JZDH.JZY000; elsif AA.ZFFS00=28 then --28.平台支付/区域预交金 Update BM_GHKSTJ set GHKS40=nvl(GHKS40,0)+AA.XMJE00 where ID0000=ld_ID0000 and DYID00=JZDH.JZY000; end if; else if AA.ZFFS00=1 then --1.现金 Update BM_GHKSTJ set GHKS05=nvl(GHKS05,0)+AA.XMJE00 where ID0000=ld_ID0000 and DYID00=-1; elsif AA.ZFFS00=2 then --2.支票 Update BM_GHKSTJ set GHKS06=nvl(GHKS06,0)+AA.XMJE00 where ID0000=ld_ID0000 and DYID00=-1; elsif AA.ZFFS00=8 then --8.银行卡 Update BM_GHKSTJ set GHKS07=nvl(GHKS07,0)+AA.XMJE00 where ID0000=ld_ID0000 and DYID00=-1; elsif AA.ZFFS00=7 then --7.记账 Update BM_GHKSTJ set GHKS08=nvl(GHKS08,0)+AA.XMJE00 where ID0000=ld_ID0000 and DYID00=-1; elsif AA.ZFFS00=18 then --18.电子钱包 Update BM_GHKSTJ set GHKS73=nvl(GHKS73,0)+AA.XMJE00 where ID0000=ld_ID0000 and DYID00=-1; elsif AA.ZFFS00=24 then --24.微信 Update BM_GHKSTJ set GHKS54=nvl(GHKS54,0)+AA.XMJE00 where ID0000=ld_ID0000 and DYID00=-1; elsif AA.ZFFS00=25 then --25.支付宝 Update BM_GHKSTJ set GHKS58=nvl(GHKS58,0)+AA.XMJE00 where ID0000=ld_ID0000 and DYID00=-1; elsif AA.ZFFS00=26 then --26.民生通 Update BM_GHKSTJ set GHKS56=nvl(GHKS56,0)+AA.XMJE00 where ID0000=ld_ID0000 and DYID00=-1; elsif AA.ZFFS00=28 then --28.平台支付/区域预交金 Update BM_GHKSTJ set GHKS40=nvl(GHKS40,0)+AA.XMJE00 where ID0000=ld_ID0000 and DYID00=-1; end if; end if; end loop; else for AA in C_CXBR00 loop select count(*) into ls_cou from BM_YGBM00 where YGBH00=JZDH.JZY000 and (YGXZ00 like '%3%' or YGXZ00 like '%5%'); if ls_cou>0 then if AA.ZFFS00=1 then --1.现金 Update BM_GHKSTJ set GHKS05=nvl(GHKS05,0)+AA.XMJE00 where ID0000=ld_ID0000 and DYID00=JZDH.JZY000; elsif AA.ZFFS00=2 then --2.支票 Update BM_GHKSTJ set GHKS06=nvl(GHKS06,0)+AA.XMJE00 where ID0000=ld_ID0000 and DYID00=JZDH.JZY000; elsif AA.ZFFS00=8 then --8.银行卡 Update BM_GHKSTJ set GHKS07=nvl(GHKS07,0)+AA.XMJE00 where ID0000=ld_ID0000 and DYID00=JZDH.JZY000; elsif AA.ZFFS00=7 then --7.记账 Update BM_GHKSTJ set GHKS08=nvl(GHKS08,0)+AA.XMJE00 where ID0000=ld_ID0000 and DYID00=JZDH.JZY000; elsif AA.ZFFS00=18 then --18.电子钱包 Update BM_GHKSTJ set GHKS73=nvl(GHKS73,0)+AA.XMJE00 where ID0000=ld_ID0000 and DYID00=JZDH.JZY000; elsif AA.ZFFS00=24 then --24.微信 Update BM_GHKSTJ set GHKS54=nvl(GHKS54,0)+AA.XMJE00 where ID0000=ld_ID0000 and DYID00=JZDH.JZY000; elsif AA.ZFFS00=25 then --25.支付宝 Update BM_GHKSTJ set GHKS58=nvl(GHKS58,0)+AA.XMJE00 where ID0000=ld_ID0000 and DYID00=JZDH.JZY000; elsif AA.ZFFS00=26 then --26.民生通 Update BM_GHKSTJ set GHKS56=nvl(GHKS56,0)+AA.XMJE00 where ID0000=ld_ID0000 and DYID00=JZDH.JZY000; elsif AA.ZFFS00=28 then --28.平台支付/区域预交金 Update BM_GHKSTJ set GHKS40=nvl(GHKS40,0)+AA.XMJE00 where ID0000=ld_ID0000 and DYID00=JZDH.JZY000; end if; else if AA.ZFFS00=1 then --1.现金 Update BM_GHKSTJ set GHKS05=nvl(GHKS05,0)+AA.XMJE00 where ID0000=ld_ID0000 and DYID00=-1; elsif AA.ZFFS00=2 then --2.支票 Update BM_GHKSTJ set GHKS06=nvl(GHKS06,0)+AA.XMJE00 where ID0000=ld_ID0000 and DYID00=-1; elsif AA.ZFFS00=8 then --8.银行卡 Update BM_GHKSTJ set GHKS07=nvl(GHKS07,0)+AA.XMJE00 where ID0000=ld_ID0000 and DYID00=-1; elsif AA.ZFFS00=7 then --7.记账 Update BM_GHKSTJ set GHKS08=nvl(GHKS08,0)+AA.XMJE00 where ID0000=ld_ID0000 and DYID00=-1; elsif AA.ZFFS00=18 then --18.电子钱包 Update BM_GHKSTJ set GHKS73=nvl(GHKS73,0)+AA.XMJE00 where ID0000=ld_ID0000 and DYID00=-1; elsif AA.ZFFS00=24 then --24.微信 Update BM_GHKSTJ set GHKS54=nvl(GHKS54,0)+AA.XMJE00 where ID0000=ld_ID0000 and DYID00=-1; elsif AA.ZFFS00=25 then --25.支付宝 Update BM_GHKSTJ set GHKS58=nvl(GHKS58,0)+AA.XMJE00 where ID0000=ld_ID0000 and DYID00=-1; elsif AA.ZFFS00=26 then --26.民生通 Update BM_GHKSTJ set GHKS56=nvl(GHKS56,0)+AA.XMJE00 where ID0000=ld_ID0000 and DYID00=-1; elsif AA.ZFFS00=28 then --28.平台支付/区域预交金 Update BM_GHKSTJ set GHKS40=nvl(GHKS40,0)+AA.XMJE00 where ID0000=ld_ID0000 and DYID00=-1; end if; end if; end loop; end if; end loop; end; --按发票日期统计--begin-- if nvl(as_TJLX00,'0')='0' then -- 票据收入, 4.就诊卡结算 declare cursor C_YJJJS0 is --## qks 2010.03.12 增加XMJE08_1 select NVL(sum(A.HJJE00),0) XMJE09,NVL(sum(A.JZJE00),0) XMJE08_1,JZY000 CZY000 from SF_JZB000 A where A.PJH000 in (select FZPJH0 from SF_PJSYQK B where B.PLBH00 = 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 substr(B.PJXH00,1,1) <> '-' --and B.PJZT00 = '0' --and nvl(trim(B.BDBZ00),'N')='N' and (B.CZY000=ld_YGBH00 or ld_YGBH00=-1) and ( as_YYID00='0' or exists (select 1 from BM_BMBM00 where BMBH00=B.CZYKS0 and trim(YYID00)=as_YYID00 ))) and exists( select 1 from SF_BRFY00 where JZDH00 = A.JZDH00 and JFLBID = 1 ) 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 group by JZY000; begin for AA in C_YJJJS0 loop --## qks 2010.03.12 增加XMJE08_1,写入GHKS08,GHKS24记帐金额 select count(*) into ls_cou from BM_YGBM00 where YGBH00=AA.CZY000 and (YGXZ00 like '%3%' or YGXZ00 like '%5%'); if ls_cou>0 then Update BM_GHKSTJ set GHKS09=AA.XMJE09,GHKS08=nvl(GHKS08,0)+AA.XMJE08_1,GHKS24=nvl(GHKS24,0)+AA.XMJE08_1 where ID0000=ld_ID0000 and DYID00=AA.CZY000; else Update BM_GHKSTJ set GHKS09=nvl(GHKS09,0)+AA.XMJE09,GHKS08=nvl(GHKS08,0)+AA.XMJE08_1,GHKS24=nvl(GHKS24,0)+AA.XMJE08_1 where ID0000=ld_ID0000 and DYID00=-1; end if; end loop; end; --Update 票据收入, 5.医保结算 declare cursor C_YBJS0 is select NVL(sum(decode(sign(Instrb(','||trim(d.ybbhsb)||',',',14,')),1,0,SF_SF_HQYBZFJE(A.JZDH00,A.BRID00,10,SBGRZH)+SF_SF_HQYBZFJE(A.JZDH00,A.BRID00,11,SBTCJJ))+decode(d.sfjsdw,'N',0,GRZHZF)+TCJJZF+decode(sign(Instrb(','||trim(d.ybbhsb)||',',',15,')),1,0,SF_SF_HQYBZFJE(A.JZDH00,A.BRID00,16,GWYBZ0))),0)XMJE10,JZY000 CZY000 from SF_JZB000 A,IC_YBBRLB D where A.YBLB00=D.YBLB00 and A.FBBH00=D.FBBH00 and d.fbbh00=3 and A.PJH000 in ( select FZPJH0 from SF_PJSYQK B where B.PLBH00 = 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 substr(B.PJXH00,1,1) <> '-' and (B.CZY000=ld_YGBH00 or ld_YGBH00=-1) --and nvl(trim(B.BDBZ00),'N')='N' and (as_YYID00='0' or exists (select 1 from BM_BMBM00 where BMBH00=B.CZYKS0 and trim(YYID00)=as_YYID00))) 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 group by A.JZY000; begin for AA in C_YBJS0 loop select count(*) into ls_cou from BM_YGBM00 where YGBH00=AA.CZY000 and (YGXZ00 like '%3%' or YGXZ00 like '%5%'); if ls_cou>0 then Update BM_GHKSTJ set GHKS10=AA.XMJE10 where ID0000=ld_ID0000 and DYID00=AA.CZY000; else Update BM_GHKSTJ set GHKS10=nvl(GHKS10,0)+AA.XMJE10 where ID0000=ld_ID0000 and DYID00=-1; end if; end loop; end; --Update 票据收入, 新农合医保结算 declare cursor C_YBJS0 is select NVL(sum(decode(sign(Instrb(','||trim(d.ybbhsb)||',',',14,')),1,0,SF_SF_HQYBZFJE(A.JZDH00,A.BRID00,10,SBGRZH)+SF_SF_HQYBZFJE(A.JZDH00,A.BRID00,11,SBTCJJ))+decode(d.sfjsdw,'N',0,GRZHZF)+TCJJZF+decode(sign(Instrb(','||trim(d.ybbhsb)||',',',15,')),1,0,SF_SF_HQYBZFJE(A.JZDH00,A.BRID00,16,GWYBZ0))),0)XMJE10,JZY000 CZY000 from SF_JZB000 A,IC_YBBRLB D where A.YBLB00=D.YBLB00 and A.FBBH00=D.FBBH00 and D.sfxnh0='1' AND d.fbbh00=3 and A.PJH000 in ( select FZPJH0 from SF_PJSYQK B where B.PLBH00 = 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 substr(B.PJXH00,1,1) <> '-' and (B.CZY000=ld_YGBH00 or ld_YGBH00=-1) -- and nvl(trim(B.BDBZ00),'N')='N' and (as_YYID00='0' or exists (select 1 from BM_BMBM00 where BMBH00=B.CZYKS0 and trim(YYID00)=as_YYID00))) 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 group by A.JZY000; --and B.PJZT00 = '0'; begin for AA in C_YBJS0 loop ---GHKS76 票据新农合zyc select count(*) into ls_cou from BM_YGBM00 where YGBH00=AA.CZY000 and (YGXZ00 like '%3%' or YGXZ00 like '%5%'); if ls_cou>0 then Update BM_GHKSTJ set GHKS76=AA.XMJE10 where ID0000=ld_ID0000 and DYID00=AA.CZY000; else Update BM_GHKSTJ set GHKS76=nvl(GHKS76,0)+AA.XMJE10 where ID0000=ld_ID0000 and DYID00=-1; end if; end loop; end; --Update 其它结算 declare cursor C_QTJS0 is select NVL(sum(B.JFJE00),0) XMJE11,JFCZY0 CZY000 from SF_BRJFB0 B where B.PJH000 in ( select A.FZPJH0 from SF_PJSYQK A where substr(A.PJXH00,1,1) <> '-' and A.DYRQ00 >= as_KSRQ00 and A.DYRQ00||A.DYSJ00 >= as_KSRQ00||as_KSSJ00 and A.DYRQ00 <= as_JSRQ00 and A.DYRQ00||A.DYSJ00 <= as_JSRQ00||as_JSSJ00 and A.PLBH00 = 1 and (A.CZY000=ld_YGBH00 or ld_YGBH00=-1) -- and nvl(trim(A.BDBZ00),'N')='N' and (as_YYID00='0' or exists (select 1 from BM_BMBM00 where BMBH00=A.CZYKS0 and trim(YYID00)=as_YYID00 ))) and b.jfRQ00 >= as_KSRQ00 and B.JFRQ00||b.JFSJ00 >= as_KSRQ00||as_KSSJ00 and B.JFRQ00 <= as_JSRQ00 and B.JFRQ00||b.JFSJ00 <= as_JSRQ00||as_JSSJ00 and B.JFBZ00 = 0 --是收款的标志 and B.ZFFS00 not in (1,2,3,4,10,11,7,8,9,16,18,24,25,26,28) --支付方式:现金,转帐,医保帐户,医保统筹,商保帐户,商保统筹,银行卡,记账,逃跑欠费,公务员补助,电子钱包 group by B.JFCZY0; begin for AA in C_QTJS0 loop select count(*) into ls_cou from BM_YGBM00 where YGBH00=AA.CZY000 and (YGXZ00 like '%3%' or YGXZ00 like '%5%'); if ls_cou>0 then Update BM_GHKSTJ set GHKS11=AA.XMJE11 where ID0000=ld_ID0000 and DYID00=AA.CZY000; else Update BM_GHKSTJ set GHKS11=nvl(GHKS11,0)+AA.XMJE11 where ID0000=ld_ID0000 and DYID00=-1; end if; end loop; end; --计算退款的医保款,非自付款 declare cursor C_TK0000 is --自费病人负的结帐单号可能在缴费表找不到记录,MZSF-20180426-005增加 nvl(A.YBLB00,'0') select abs(nvl(ZFJE00,0)+nvl(JZJE00,0)) XMJE00,JZY000,CXJZDH,JZDH00, abs(decode(sign(Instrb(','||trim(d.ybbhsb)||',',',14,')),1,0,SF_SF_HQYBZFJE(A.JZDH00,A.BRID00,10,SBGRZH)+SF_SF_HQYBZFJE(A.JZDH00,A.BRID00,11,SBTCJJ))+decode(d.sfjsdw,'N',0,GRZHZF)+nvl(TCJJZF,0)+decode(sign(Instrb(','||trim(d.ybbhsb)||',',',15,')),1,0,SF_SF_HQYBZFJE(A.JZDH00,A.BRID00,16,GWYBZ0))) XMJE17 from SF_JZB000 A,IC_YBBRLB D where nvl(A.YBLB00,'0')=D.YBLB00 and A.FBBH00=D.FBBH00 and a.PJH000 in (select FZPJH0 from SF_PJSYQK B where B.PLBH00 = 1 and (B.CZY000=ld_YGBH00 or ld_YGBH00=-1) and (as_YYID00='0' or exists (select 1 from BM_BMBM00 where BMBH00=B.CZYKS0 and trim(YYID00)=as_YYID00 )) 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 SF_BRJFB0 where JFLBID = 0 and JZDH00 = A.JZDH00); begin for AA in C_TK0000 loop select count(*) into ls_count0 from sf_brjfb0 where JZDH00=AA.CXJZDH and JFLBID=0; select count(*) into ls_cou from BM_YGBM00 where YGBH00=AA.JZY000 and (YGXZ00 like '%3%' or YGXZ00 like '%5%'); if ls_cou>0 then if ls_count0>0 then Update BM_GHKSTJ set GHKS15=nvl(GHKS15,0)+AA.XMJE00,GHKS17=nvl(GHKS17,0)+AA.XMJE17 where ID0000=ld_ID0000 and DYID00=AA.JZY000; else Update BM_GHKSTJ set GHKS16=nvl(GHKS16,0)+AA.XMJE00,GHKS17=nvl(GHKS17,0)+AA.XMJE17 where ID0000=ld_ID0000 and DYID00=AA.JZY000; end if; else if ls_count0>0 then Update BM_GHKSTJ set GHKS15=nvl(GHKS15,0)+AA.XMJE00,GHKS17=nvl(GHKS17,0)+AA.XMJE17 where ID0000=ld_ID0000 and DYID00=-1; else Update BM_GHKSTJ set GHKS16=nvl(GHKS16,0)+AA.XMJE00,GHKS17=nvl(GHKS17,0)+AA.XMJE17 where ID0000=ld_ID0000 and DYID00=-1; end if; end if; end loop; end; --计算退款的新农合医保款 declare cursor C_TK0000 is select JZY000,abs(decode(sign(Instrb(','||trim(d.ybbhsb)||',',',14,')),1,0,SF_SF_HQYBZFJE(A.JZDH00,A.BRID00,10,SBGRZH)+SF_SF_HQYBZFJE(A.JZDH00,A.BRID00,11,SBTCJJ))+decode(d.sfjsdw,'N',0,GRZHZF)+nvl(TCJJZF,0)+decode(sign(Instrb(','||trim(d.ybbhsb)||',',',15,')),1,0,SF_SF_HQYBZFJE(A.JZDH00,A.BRID00,16,GWYBZ0))) XMJE17 from SF_JZB000 A,IC_YBBRLB D where A.YBLB00=D.YBLB00 and A.FBBH00=D.FBBH00 and d.fbbh00=3 and d.sfxnh0='1' and a.PJH000 in (select FZPJH0 from SF_PJSYQK B where B.PLBH00 = 1 and (B.CZY000=ld_YGBH00 or ld_YGBH00=-1) and (as_YYID00='0' or exists (select 1 from BM_BMBM00 where BMBH00=B.CZYKS0 and trim(YYID00)=as_YYID00 )) 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) = '-' ); begin for AA in C_TK0000 loop --GHKS78 退款新农合 select count(*) into ls_cou from BM_YGBM00 where YGBH00=AA.JZY000 and (YGXZ00 like '%3%' or YGXZ00 like '%5%'); if ls_cou>0 then Update BM_GHKSTJ set GHKS78=nvl(GHKS78,0)+AA.XMJE17,GHKS79=GHKS17-(nvl(GHKS78,0)+AA.XMJE17) where ID0000=ld_ID0000 and DYID00=AA.JZY000; else Update BM_GHKSTJ set GHKS78=nvl(GHKS78,0)+AA.XMJE17,GHKS79=GHKS17-(nvl(GHKS78,0)+AA.XMJE17) where ID0000=ld_ID0000 and DYID00=-1; end if; end loop; end; --计算所有的银行卡收入,支票收入,记账收入,电子钱包 declare cursor C_ZPSR00 is select sum(XMJE20) XMJE20,CZY000,ZFFS00 from ( select NVL(sum(B.JFJE00),0)XMJE20,JFCZY0 CZY000,ZFFS00 from SF_BRJFB0 B where B.PJH000 in (select A.FZPJH0 from SF_PJSYQK A where substr(A.PJXH00,1,1) <> '-' and A.DYRQ00 >= as_KSRQ00 and A.DYRQ00||A.DYSJ00 >= as_KSRQ00||as_KSSJ00 and A.DYRQ00 <= as_JSRQ00 and A.DYRQ00||A.DYSJ00 <= as_JSRQ00||as_JSSJ00 --and A.pjzt00<>'1' --lml 20110705 by MZSF-20110701-001 -- and nvl(trim(A.BDBZ00),'N')='N' and (A.CZY000=ld_YGBH00 or ld_YGBH00=-1) ) and (as_YYID00='0' or exists (select 1 from BM_BMBM00 where BMBH00=B.CZYKS0 and (trim(YYID00)=as_YYID00 )) ) and B.ZFFS00 in (2,8,7,18,24,25,26,28) and b.jfRQ00 >= as_KSRQ00 and B.JFRQ00||b.JFSJ00 >= as_KSRQ00||as_KSSJ00 and B.JFRQ00 <= as_JSRQ00 and B.JFRQ00||b.JFSJ00 <= as_JSRQ00||as_JSSJ00 group by B.JFCZY0,ZFFS00 union all select NVL(sum(B.JFJE00),0) XMJE20,JFCZY0 CZY000,ZFFS00 from SF_BRJFB0 B where b.JFLBID=0 and b.JZDH00>1 and B.PJH000 in (select A.FZPJH0 from SF_PJSYQK A where substr(A.PJXH00,1,1) = '-' and A.DYRQ00 >= as_KSRQ00 and A.DYRQ00||A.DYSJ00 >= as_KSRQ00||as_KSSJ00 and A.DYRQ00 <= as_JSRQ00 and A.DYRQ00||A.DYSJ00 <= as_JSRQ00||as_JSSJ00 --and A.pjzt00<>'1' --lml 20110705 by MZSF-20110701-001 -- and nvl(trim(A.BDBZ00),'N')='N' and (A.CZY000=ld_YGBH00 or ld_YGBH00=-1) ) and (as_YYID00='0' or exists (select 1 from BM_BMBM00 where BMBH00=B.CZYKS0 and (trim(YYID00)=as_YYID00 )) ) and B.ZFFS00 in (2,8,7,18,24,25,26,28) and b.jfRQ00 >= as_KSRQ00 and B.JFRQ00||b.JFSJ00 >= as_KSRQ00||as_KSSJ00 and B.JFRQ00 <= as_JSRQ00 and B.JFRQ00||b.JFSJ00 <= as_JSRQ00||as_JSSJ00 group by B.JFCZY0,ZFFS00 ) group by CZY000,ZFFS00; begin for AA in C_ZPSR00 loop select count(*) into ls_cou from BM_YGBM00 where YGBH00=AA.CZY000 and (YGXZ00 like '%3%' or YGXZ00 like '%5%'); if ls_cou>0 then if AA.ZFFS00=2 then --支票收入 Update BM_GHKSTJ set GHKS20=nvl(GHKS20,0)+AA.XMJE20 where ID0000=ld_ID0000 and DYID00=AA.CZY000; elsif AA.ZFFS00=8 then --银行卡收入 Update BM_GHKSTJ set GHKS21=nvl(GHKS21,0)+AA.XMJE20 where ID0000=ld_ID0000 and DYID00=AA.CZY000; elsif AA.ZFFS00=7 then --7.记账 --## qks 2010.03.12 GHKS24=AA.XMJE20修改为GHKS24=nvl(GHKS24,0)+AA.XMJE20 Update BM_GHKSTJ set GHKS24=nvl(GHKS24,0)+AA.XMJE20 where ID0000=ld_ID0000 and DYID00=AA.CZY000; elsif AA.ZFFS00=18 then --18.电子钱包 Update BM_GHKSTJ set GHKS29=nvl(GHKS29,0)+AA.XMJE20 where ID0000=ld_ID0000 and DYID00=AA.CZY000; elsif AA.ZFFS00=24 then --24.微信支付收入GHKS55 Update BM_GHKSTJ set GHKS55=nvl(GHKS55,0)+AA.XMJE20 where ID0000=ld_ID0000 and DYID00=AA.CZY000; elsif AA.ZFFS00=25 then --25.支付宝支付收入GHKS59 Update BM_GHKSTJ set GHKS59=nvl(GHKS59,0)+AA.XMJE20 where ID0000=ld_ID0000 and DYID00=AA.CZY000; elsif AA.ZFFS00=26 then --26 民生通收入GHKS57 Update BM_GHKSTJ set GHKS57=nvl(GHKS57,0)+AA.XMJE20 where ID0000=ld_ID0000 and DYID00=AA.CZY000; elsif AA.ZFFS00=28 then --28 平台支付/区域预交金 GHKS40 Update BM_GHKSTJ set GHKS40=nvl(GHKS40,0)+AA.XMJE20 where ID0000=ld_ID0000 and DYID00=AA.CZY000; end if; else if AA.ZFFS00=2 then --支票收入 Update BM_GHKSTJ set GHKS20=nvl(GHKS20,0)+AA.XMJE20 where ID0000=ld_ID0000 and DYID00=-1; elsif AA.ZFFS00=8 then --银行卡收入 Update BM_GHKSTJ set GHKS21=nvl(GHKS21,0)+AA.XMJE20 where ID0000=ld_ID0000 and DYID00=-1; elsif AA.ZFFS00=7 then --7.记账 --## qks 2010.03.12 GHKS24=AA.XMJE20修改为GHKS24=nvl(GHKS24,0)+AA.XMJE20 Update BM_GHKSTJ set GHKS24=nvl(GHKS24,0)+AA.XMJE20 where ID0000=ld_ID0000 and DYID00=-1; elsif AA.ZFFS00=18 then --18.电子钱包 Update BM_GHKSTJ set GHKS29=nvl(GHKS29,0)+AA.XMJE20 where ID0000=ld_ID0000 and DYID00=-1; elsif AA.ZFFS00=24 then --24.微信支付收入GHKS55 Update BM_GHKSTJ set GHKS55=nvl(GHKS55,0)+AA.XMJE20 where ID0000=ld_ID0000 and DYID00=-1; elsif AA.ZFFS00=25 then --25.支付宝支付收入GHKS59 Update BM_GHKSTJ set GHKS59=nvl(GHKS59,0)+AA.XMJE20 where ID0000=ld_ID0000 and DYID00=-1; elsif AA.ZFFS00=26 then --26 民生通收入GHKS57 Update BM_GHKSTJ set GHKS57=nvl(GHKS57,0)+AA.XMJE20 where ID0000=ld_ID0000 and DYID00=-1; elsif AA.ZFFS00=28 then --28 平台支付/区域预交金 GHKS40 Update BM_GHKSTJ set GHKS40=nvl(GHKS40,0)+AA.XMJE20 where ID0000=ld_ID0000 and DYID00=-1; end if; end if; end loop; end; --计算支票收入新农合病人的支票收入 declare cursor C_ZPSR00 is select NVL(sum(B.JFJE00),0)XMJE20,JFCZY0 CZY000,ZFFS00 from SF_BRJFB0 B where B.PJH000 in (select A.FZPJH0 from SF_PJSYQK A where substr(A.PJXH00,1,1) <> '-' and A.DYRQ00 >= as_KSRQ00 and A.DYRQ00||A.DYSJ00 >= as_KSRQ00||as_KSSJ00 and A.DYRQ00 <= as_JSRQ00 and A.DYRQ00||A.DYSJ00 <= as_JSRQ00||as_JSSJ00 --and A.pjzt00<>'1' --lml 20110705 by MZSF-20110701-001 -- and nvl(trim(A.BDBZ00),'N')='N' and (A.CZY000=ld_YGBH00 or ld_YGBH00=-1) and exists (select 1 from BM_BRXXB0 b,IC_YBBRLB c where b.YBLB00=c.yblb00 and b.FBBH00=c.FBBH00 and b.BRID00=a.BRID00 and c.FBBH00=3 and c.sfxnh0='1') ) and (as_YYID00='0' or exists (select 1 from BM_BMBM00 where BMBH00=B.CZYKS0 and (trim(YYID00)=as_YYID00 )) ) and B.ZFFS00=2 and b.jfRQ00 >= as_KSRQ00 and B.JFRQ00||b.JFSJ00 >= as_KSRQ00||as_KSSJ00 and B.JFRQ00 <= as_JSRQ00 and B.JFRQ00||b.JFSJ00 <= as_JSRQ00||as_JSSJ00 group by B.JFCZY0,ZFFS00; begin for AA in C_ZPSR00 loop select count(*) into ls_cou from BM_YGBM00 where YGBH00=AA.CZY000 and (YGXZ00 like '%3%' or YGXZ00 like '%5%'); if ls_cou>0 then Update BM_GHKSTJ set GHKS74=nvl(GHKS74,0)+AA.XMJE20 where ID0000=ld_ID0000 and DYID00=AA.CZY000; else Update BM_GHKSTJ set GHKS74=nvl(GHKS74,0)+AA.XMJE20 where ID0000=ld_ID0000 and DYID00=-1; end if; end loop; end; --按发票日期统计--end-- else --按结算日期统计--begin-- -- 票据收入, 4.就诊卡结算(按结算日期统计) declare cursor C_YJJJS0 is select NVL(sum(A.HJJE00),0) XMJE09,NVL(sum(A.JZJE00),0) XMJE08_1,JZY000 CZY000 from SF_JZB000 A where 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 exists (select 1 from SF_PJSYQK B where B.FZPJH0=A.PJH000 and B.PLBH00 = 1 and substr(B.PJXH00,1,1) <> '-' --and B.PJZT00 = '0' --and nvl(trim(B.BDBZ00),'N')='N' and (B.CZY000=ld_YGBH00 or ld_YGBH00=-1) and (as_YYID00='0' or exists (select 1 from BM_BMBM00 where BMBH00=B.CZYKS0 and trim(YYID00)=as_YYID00) ) ) and exists( select 1 from SF_BRFY00 where JZDH00 = A.JZDH00 and JFLBID = 1 ) group by JZY000; begin for AA in C_YJJJS0 loop --## qks 2010.03.12 增加XMJE08_1,写入GHKS08,GHKS24记帐金额 select count(*) into ls_cou from BM_YGBM00 where YGBH00=AA.CZY000 and (YGXZ00 like '%3%' or YGXZ00 like '%5%'); if ls_cou>0 then Update BM_GHKSTJ set GHKS09=AA.XMJE09,GHKS08=nvl(GHKS08,0)+AA.XMJE08_1,GHKS24=nvl(GHKS24,0)+AA.XMJE08_1 where ID0000=ld_ID0000 and DYID00=AA.CZY000; else Update BM_GHKSTJ set GHKS09=nvl(GHKS09,0)+AA.XMJE09,GHKS08=nvl(GHKS08,0)+AA.XMJE08_1,GHKS24=nvl(GHKS24,0)+AA.XMJE08_1 where ID0000=ld_ID0000 and DYID00=-1; end if; end loop; end; --Update 票据收入, 5.医保结算(按结算日期统计) declare cursor C_YBJS0 is select NVL(sum(decode(sign(Instrb(','||trim(d.ybbhsb)||',',',14,')),1,0,SF_SF_HQYBZFJE(A.JZDH00,A.BRID00,10,SBGRZH)+SF_SF_HQYBZFJE(A.JZDH00,A.BRID00,11,SBTCJJ))+decode(d.sfjsdw,'N',0,GRZHZF)+TCJJZF+decode(sign(Instrb(','||trim(d.ybbhsb)||',',',15,')),1,0,SF_SF_HQYBZFJE(A.JZDH00,A.BRID00,16,GWYBZ0))),0)XMJE10,JZY000 CZY000 from SF_JZB000 A,IC_YBBRLB D where A.YBLB00=D.YBLB00 and A.FBBH00=D.FBBH00 and d.fbbh00=3 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 exists (select 1 from SF_PJSYQK B where B.FZPJH0=A.PJH000 and B.PLBH00 = 1 and substr(B.PJXH00,1,1) <> '-' and (B.CZY000=ld_YGBH00 or ld_YGBH00=-1) and (as_YYID00='0' or exists (select 1 from BM_BMBM00 where BMBH00=B.CZYKS0 and trim(YYID00)=as_YYID00) ) ) group by A.JZY000; begin for AA in C_YBJS0 loop select count(*) into ls_cou from BM_YGBM00 where YGBH00=AA.CZY000 and (YGXZ00 like '%3%' or YGXZ00 like '%5%'); if ls_cou>0 then Update BM_GHKSTJ set GHKS10=AA.XMJE10 where ID0000=ld_ID0000 and DYID00=AA.CZY000; else Update BM_GHKSTJ set GHKS10=nvl(GHKS10,0)+AA.XMJE10 where ID0000=ld_ID0000 and DYID00=-1; end if; end loop; end; --Update 票据收入, 新农合医保结算(按结算日期统计) declare cursor C_YBJS0 is select NVL(sum(decode(sign(Instrb(','||trim(d.ybbhsb)||',',',14,')),1,0,SF_SF_HQYBZFJE(A.JZDH00,A.BRID00,10,SBGRZH)+SF_SF_HQYBZFJE(A.JZDH00,A.BRID00,11,SBTCJJ))+decode(d.sfjsdw,'N',0,GRZHZF)+TCJJZF+decode(sign(Instrb(','||trim(d.ybbhsb)||',',',15,')),1,0,SF_SF_HQYBZFJE(A.JZDH00,A.BRID00,16,GWYBZ0))),0)XMJE10,JZY000 CZY000 from SF_JZB000 A,IC_YBBRLB D where A.YBLB00=D.YBLB00 and A.FBBH00=D.FBBH00 and D.sfxnh0='1' AND d.fbbh00=3 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 exists (select 1 from SF_PJSYQK B where B.FZPJH0=A.PJH000 and B.PLBH00 = 1 and substr(B.PJXH00,1,1) <> '-' and (B.CZY000=ld_YGBH00 or ld_YGBH00=-1) and (as_YYID00='0' or exists (select 1 from BM_BMBM00 where BMBH00=B.CZYKS0 and trim(YYID00)=as_YYID00) ) ) group by A.JZY000; begin for AA in C_YBJS0 loop ---GHKS76 票据新农合 zyc select count(*) into ls_cou from BM_YGBM00 where YGBH00=AA.CZY000 and (YGXZ00 like '%3%' or YGXZ00 like '%5%'); if ls_cou>0 then Update BM_GHKSTJ set GHKS76=AA.XMJE10 where ID0000=ld_ID0000 and DYID00=AA.CZY000; else Update BM_GHKSTJ set GHKS76=nvl(GHKS76,0)+AA.XMJE10 where ID0000=ld_ID0000 and DYID00=-1; end if; end loop; end; --Update 其它结算(按结算日期统计) declare cursor C_QTJS0 is select NVL(sum(B.JFJE00),0) XMJE11,JFCZY0 CZY000 from SF_BRJFB0 B where B.JFBZ00 = 0 --是收款的标志 and B.ZFFS00 not in (1,2,3,4,10,11,7,8,9,16,18,24,25,26,28) --支付方式:现金,转帐,医保帐户,医保统筹,商保帐户,商保统筹,银行卡,记账,逃跑欠费,公务员补助,电子钱包 and B.JFRQ00 >= as_KSRQ00 and B.JFRQ00 <= as_JSRQ00 and B.JFRQ00||B.JFSJ00 >= as_KSRQ00||as_KSSJ00 and B.JFRQ00||B.JFSJ00 <= as_JSRQ00||as_JSSJ00 and exists ( select 1 from SF_PJSYQK A where A.FZPJH0=B.PJH000 and substr(A.PJXH00,1,1) <> '-' and A.PLBH00 = 1 and (A.CZY000=ld_YGBH00 or ld_YGBH00=-1) and (as_YYID00='0' or exists (select 1 from BM_BMBM00 where BMBH00=A.CZYKS0 and trim(YYID00)=as_YYID00 ) ) ) group by B.JFCZY0; begin for AA in C_QTJS0 loop select count(*) into ls_cou from BM_YGBM00 where YGBH00=AA.CZY000 and (YGXZ00 like '%3%' or YGXZ00 like '%5%'); if ls_cou>0 then Update BM_GHKSTJ set GHKS11=AA.XMJE11 where ID0000=ld_ID0000 and DYID00=AA.CZY000; else Update BM_GHKSTJ set GHKS11=nvl(GHKS11,0)+AA.XMJE11 where ID0000=ld_ID0000 and DYID00=-1; end if; end loop; end; --计算退款的医保款,非自付款 declare cursor C_TK0000 is --自费病人负的结帐单号可能在缴费表找不到记录,MZSF-20180426-005增加 nvl(A.YBLB00,'0') select abs(nvl(ZFJE00,0)+nvl(JZJE00,0)) XMJE00,JZY000,CXJZDH,JZDH00, abs(decode(sign(Instrb(','||trim(d.ybbhsb)||',',',14,')),1,0,SF_SF_HQYBZFJE(A.JZDH00,A.BRID00,10,SBGRZH)+SF_SF_HQYBZFJE(A.JZDH00,A.BRID00,11,SBTCJJ))+decode(d.sfjsdw,'N',0,GRZHZF)+nvl(TCJJZF,0)+decode(sign(Instrb(','||trim(d.ybbhsb)||',',',15,')),1,0,SF_SF_HQYBZFJE(A.JZDH00,A.BRID00,16,GWYBZ0))) XMJE17 from SF_JZB000 A,IC_YBBRLB D where nvl(A.YBLB00,'0')=D.YBLB00 and A.FBBH00=D.FBBH00 and a.PJH000 in (select FZPJH0 from SF_PJSYQK B where B.PLBH00 = 1 and (B.CZY000=ld_YGBH00 or ld_YGBH00=-1) and (as_YYID00='0' or exists (select 1 from BM_BMBM00 where BMBH00=B.CZYKS0 and trim(YYID00)=as_YYID00 )) 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 SF_BRJFB0 where JFLBID = 0 and JZDH00 = A.JZDH00); begin for AA in C_TK0000 loop select count(*) into ls_count0 from sf_brjfb0 where JZDH00=AA.CXJZDH and JFLBID=0; select count(*) into ls_cou from BM_YGBM00 where YGBH00=AA.JZY000 and (YGXZ00 like '%3%' or YGXZ00 like '%5%'); if ls_cou>0 then if ls_count0>0 then Update BM_GHKSTJ set GHKS15=nvl(GHKS15,0)+AA.XMJE00,GHKS17=nvl(GHKS17,0)+AA.XMJE17 where ID0000=ld_ID0000 and DYID00=AA.JZY000; else Update BM_GHKSTJ set GHKS16=nvl(GHKS16,0)+AA.XMJE00,GHKS17=nvl(GHKS17,0)+AA.XMJE17 where ID0000=ld_ID0000 and DYID00=AA.JZY000; end if; else if ls_count0>0 then Update BM_GHKSTJ set GHKS15=nvl(GHKS15,0)+AA.XMJE00,GHKS17=nvl(GHKS17,0)+AA.XMJE17 where ID0000=ld_ID0000 and DYID00=-1; else Update BM_GHKSTJ set GHKS16=nvl(GHKS16,0)+AA.XMJE00,GHKS17=nvl(GHKS17,0)+AA.XMJE17 where ID0000=ld_ID0000 and DYID00=-1; end if; end if; end loop; end; --计算退款的新农合医保款 declare cursor C_TK0000 is select JZY000,abs(decode(sign(Instrb(','||trim(d.ybbhsb)||',',',14,')),1,0,SF_SF_HQYBZFJE(A.JZDH00,A.BRID00,10,SBGRZH)+SF_SF_HQYBZFJE(A.JZDH00,A.BRID00,11,SBTCJJ))+decode(d.sfjsdw,'N',0,GRZHZF)+nvl(TCJJZF,0)+decode(sign(Instrb(','||trim(d.ybbhsb)||',',',15,')),1,0,SF_SF_HQYBZFJE(A.JZDH00,A.BRID00,16,GWYBZ0))) XMJE17 from SF_JZB000 A,IC_YBBRLB D where A.YBLB00=D.YBLB00 and A.FBBH00=D.FBBH00 and d.fbbh00=3 and d.sfxnh0='1' and a.PJH000 in (select FZPJH0 from SF_PJSYQK B where B.PLBH00 = 1 and (B.CZY000=ld_YGBH00 or ld_YGBH00=-1) and (as_YYID00='0' or exists (select 1 from BM_BMBM00 where BMBH00=B.CZYKS0 and trim(YYID00)=as_YYID00 )) 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) = '-' ); begin for AA in C_TK0000 loop --GHKS78 退款新农合 select count(*) into ls_cou from BM_YGBM00 where YGBH00=AA.JZY000 and (YGXZ00 like '%3%' or YGXZ00 like '%5%'); if ls_cou>0 then Update BM_GHKSTJ set GHKS78=nvl(GHKS78,0)+AA.XMJE17,GHKS79=GHKS17-(nvl(GHKS78,0)+AA.XMJE17) where ID0000=ld_ID0000 and DYID00=AA.JZY000; else Update BM_GHKSTJ set GHKS78=nvl(GHKS78,0)+AA.XMJE17,GHKS79=GHKS17-(nvl(GHKS78,0)+AA.XMJE17) where ID0000=ld_ID0000 and DYID00=-1; end if; end loop; end; --计算所有的银行卡收入,支票收入,记账收入,电子钱包(按结算日期统计) declare cursor C_ZPSR00 is select NVL(sum(B.JFJE00),0)XMJE20,JFCZY0 CZY000,ZFFS00 from SF_BRJFB0 B where B.ZFFS00 in (2,8,7,18,24,25,26,28) and B.JFRQ00 >= as_KSRQ00 and B.JFRQ00 <= as_JSRQ00 and B.JFRQ00||B.JFSJ00 >= as_KSRQ00||as_KSSJ00 and B.JFRQ00||B.JFSJ00 <= as_JSRQ00||as_JSSJ00 and exists (select 1 from SF_PJSYQK A where substr(A.PJXH00,1,1) <> '-' and A.FZPJH0=B.PJH000 --and A.pjzt00<>'1' --and nvl(trim(A.BDBZ00),'N')='N' and (A.CZY000=ld_YGBH00 or ld_YGBH00=-1) ) and (as_YYID00='0' or exists (select 1 from BM_BMBM00 where BMBH00=B.CZYKS0 and (trim(YYID00)=as_YYID00 )) ) group by B.JFCZY0,ZFFS00; begin for AA in C_ZPSR00 loop select count(*) into ls_cou from BM_YGBM00 where YGBH00=AA.CZY000 and (YGXZ00 like '%3%' or YGXZ00 like '%5%'); if ls_cou>0 then if AA.ZFFS00=2 then --支票收入 Update BM_GHKSTJ set GHKS20=nvl(GHKS20,0)+AA.XMJE20 where ID0000=ld_ID0000 and DYID00=AA.CZY000; elsif AA.ZFFS00=8 then --银行卡收入 Update BM_GHKSTJ set GHKS21=nvl(GHKS21,0)+AA.XMJE20 where ID0000=ld_ID0000 and DYID00=AA.CZY000; elsif AA.ZFFS00=7 then --7.记账 --## qks 2010.03.12 GHKS24=AA.XMJE20修改为GHKS24=nvl(GHKS24,0)+AA.XMJE20 Update BM_GHKSTJ set GHKS24=nvl(GHKS24,0)+AA.XMJE20 where ID0000=ld_ID0000 and DYID00=AA.CZY000; elsif AA.ZFFS00=18 then --18.电子钱包 Update BM_GHKSTJ set GHKS29=nvl(GHKS29,0)+AA.XMJE20 where ID0000=ld_ID0000 and DYID00=AA.CZY000; elsif AA.ZFFS00=24 then --24.微信支付收入GHKS55 Update BM_GHKSTJ set GHKS55=nvl(GHKS55,0)+AA.XMJE20 where ID0000=ld_ID0000 and DYID00=AA.CZY000; elsif AA.ZFFS00=25 then --25.支付宝支付收入GHKS59 Update BM_GHKSTJ set GHKS59=nvl(GHKS59,0)+AA.XMJE20 where ID0000=ld_ID0000 and DYID00=AA.CZY000; elsif AA.ZFFS00=26 then --26 民生通收入GHKS57 Update BM_GHKSTJ set GHKS57=nvl(GHKS57,0)+AA.XMJE20 where ID0000=ld_ID0000 and DYID00=AA.CZY000; elsif AA.ZFFS00=28 then --28 平台支付/区域预交金GHKS40 Update BM_GHKSTJ set GHKS40=nvl(GHKS40,0)+AA.XMJE20 where ID0000=ld_ID0000 and DYID00=AA.CZY000; end if; else if AA.ZFFS00=2 then --支票收入 Update BM_GHKSTJ set GHKS20=nvl(GHKS20,0)+AA.XMJE20 where ID0000=ld_ID0000 and DYID00=-1; elsif AA.ZFFS00=8 then --银行卡收入 Update BM_GHKSTJ set GHKS21=nvl(GHKS21,0)+AA.XMJE20 where ID0000=ld_ID0000 and DYID00=-1; elsif AA.ZFFS00=7 then --7.记账 --## qks 2010.03.12 GHKS24=AA.XMJE20修改为GHKS24=nvl(GHKS24,0)+AA.XMJE20 Update BM_GHKSTJ set GHKS24=nvl(GHKS24,0)+AA.XMJE20 where ID0000=ld_ID0000 and DYID00=-1; elsif AA.ZFFS00=18 then --18.电子钱包 Update BM_GHKSTJ set GHKS29=nvl(GHKS29,0)+AA.XMJE20 where ID0000=ld_ID0000 and DYID00=-1; elsif AA.ZFFS00=24 then --24.微信支付收入GHKS55 Update BM_GHKSTJ set GHKS55=nvl(GHKS55,0)+AA.XMJE20 where ID0000=ld_ID0000 and DYID00=-1; elsif AA.ZFFS00=25 then --25.支付宝支付收入GHKS59 Update BM_GHKSTJ set GHKS59=nvl(GHKS59,0)+AA.XMJE20 where ID0000=ld_ID0000 and DYID00=-1; elsif AA.ZFFS00=26 then --26 民生通收入GHKS57 Update BM_GHKSTJ set GHKS57=nvl(GHKS57,0)+AA.XMJE20 where ID0000=ld_ID0000 and DYID00=-1; elsif AA.ZFFS00=28 then --28 平台支付/区域预交金GHKS40 Update BM_GHKSTJ set GHKS40=nvl(GHKS40,0)+AA.XMJE20 where ID0000=ld_ID0000 and DYID00=-1; end if; end if; end loop; end; --计算支票收入新农合病人的支票收入(按结算日期统计) declare cursor C_ZPSR00 is select NVL(sum(B.JFJE00),0)XMJE20,JFCZY0 CZY000,ZFFS00 from SF_BRJFB0 B where B.ZFFS00=2 and B.JFRQ00 >= as_KSRQ00 and B.JFRQ00 <= as_JSRQ00 and B.JFRQ00||B.JFSJ00 >= as_KSRQ00||as_KSSJ00 and B.JFRQ00||B.JFSJ00 <= as_JSRQ00||as_JSSJ00 and exists (select 1 from SF_PJSYQK A where substr(A.PJXH00,1,1) <> '-' and A.FZPJH0=B.PJH000 --and A.pjzt00<>'1' --and nvl(trim(A.BDBZ00),'N')='N' and (A.CZY000=ld_YGBH00 or ld_YGBH00=-1) and exists (select 1 from BM_BRXXB0 b,IC_YBBRLB c where b.YBLB00=c.yblb00 and b.FBBH00=c.FBBH00 and b.BRID00=a.BRID00 and c.FBBH00=3 and c.sfxnh0='1') ) and (as_YYID00='0' or exists (select 1 from BM_BMBM00 where BMBH00=B.CZYKS0 and (trim(YYID00)=as_YYID00 )) ) group by B.JFCZY0,ZFFS00; begin for AA in C_ZPSR00 loop select count(*) into ls_cou from BM_YGBM00 where YGBH00=AA.CZY000 and (YGXZ00 like '%3%' or YGXZ00 like '%5%'); if ls_cou>0 then Update BM_GHKSTJ set GHKS74=nvl(GHKS74,0)+AA.XMJE20 where ID0000=ld_ID0000 and DYID00=AA.CZY000; else Update BM_GHKSTJ set GHKS74=nvl(GHKS74,0)+AA.XMJE20 where ID0000=ld_ID0000 and DYID00=-1; end if; end loop; end; end if; --按结算日期统计--end-- for sfy in CUR_SF_BRJFB0_SFY000 loop ls_ZPSR00 := 0 ; --支票收入 ls_YHKSR0 := 0 ; --银行卡收入 ls_BZZZQD := 0 ; --不足转账起点 ls_YHKJS0 := 0 ; --银行卡结算 ls_YBJS00 := 0 ; --医保结算 ls_JMBRYF := 0 ; --减免病人医药费 select BH0000 into ls_KSBH00 from BM_GHKSTJ_KSDY00 where ID0000 = ad_ID0000 and GHKSBH = sfy.YGBH00; --Update 现金挂号单收入,原来的求和有问题,主要是SF_PJSYQK同一个票据号,有一条记录,SF_BRJFB0有多条记录 ls_XMJE01 := NVL(sfy.XJSR00,0)+NVL(sfy.ZPSR00,0)+NVL(sfy.YHKSR0,0)+NVL(sfy.DZQB00,0); --Update 预交金收入,或预交金收款单 if LS_YHSFKC='N' then --预交金收入是否包含银行卡金额 Y:包含 N:不包含 默认值:Y包含 ls_XMJE02 := NVL(sfy.XJSR01,0)+NVL(sfy.ZPSR01,0)+NVL(sfy.DZQB01,0); ls_XMJE03 := ABS(NVL(sfy.XJSR02,0)+NVL(sfy.ZPSR02,0)+NVL(sfy.YHKSR2,0)+NVL(sfy.DZQB02,0)); else ls_XMJE02 := NVL(sfy.XJSR01,0)+NVL(sfy.ZPSR01,0)+NVL(sfy.YHKSR1,0)+NVL(sfy.DZQB01,0); ls_XMJE03 := ABS(NVL(sfy.XJSR02,0)+NVL(sfy.ZPSR02,0)+NVL(sfy.YHKSR2,0)+NVL(sfy.DZQB02,0)); end if; --Update 预交金现金退出,或预交金退款单 --ls_XMJE03 := ABS(NVL(sfy.XJSR02,0)+NVL(sfy.ZPSR02,0)+NVL(sfy.YHKSR2,0+NVL(sfy.DZQB02,0))); --2005.05.25 ls_XMJE04 := NVL(sfy.XJSR03,0)+NVL(sfy.ZPSR03,0)+NVL(sfy.YHKSR3,0); --Update 票据收入, 1.现金 ls_XMJE05:=sfy.XMJE05; --Update 票据收入, 2.支票 --ls_XMJE06 := 0; ls_XMJE06:=sfy.XMJE06; --Update 银行卡收入,8.银行卡 ls_XMJE07:=sfy.XMJE07; --Update 记账,7.记账 ls_XMJE08:=sfy.XMJE08; --Update 票据收入, 4.就诊卡结算 ls_XMJE09:=sfy.XMJE09; --Update 票据收入, 5.医保结算 ls_XMJE10:=sfy.XMJE10; --Update 其它结算 ls_XMJE11:=sfy.XMJE11; --票据收入:电子钱包 ls_XMJE73:=sfy.DZQB03; --补打收据款 ls_XMJE12 := 0; --换新收据 ls_XMJE13 := 0; --就诊卡退款 ls_XMJE16 :=sfy.XMJE16; --换旧收据 ls_XMJE18 := 0; --支票收入 ls_XMJE20 :=sfy.XMJE20; --银行卡收入,计算所有的银行卡的收入 ls_XMJE21 :=sfy.XMJE21; --不足转账起点 ls_BZZZQD := 0 ; ls_XMJE22 := 0 ; --银行卡结算 ls_XMJE23 := ls_XMJE07; --记账 --ls_XMJE24 := ls_XMJE08; --Update 记账,7.记账 ls_XMJE24:=sfy.XMJE24; --医保结算 ls_XMJE25 := ls_XMJE10; --减免病人医药费 ls_JMBRYF := 0 ; ls_XMJE26 := 0 ; --Update BM_JKKJMX set XMJE00 = NVL(ls_JMBRYF,0) where JKDH00 = ls_JKDH00 and BZ0000='0' and XMBH00 = '23'; --计算退款的医保款,非医保款 ls_XMJE15:=sfy.XMJE15; ls_XMJE17:=sfy.XMJE17; --电子钱包收入 ls_XMJE29:=sfy.XMJE29; ls_XMJE14 := NVL(ls_XMJE15,0)+NVL(ls_XMJE16,0)+NVL(ls_XMJE17,0); --退款合计金额 --重新结算票据金额 ls_XMJE04 := NVL(ls_XMJE05,0)+NVL(ls_XMJE06,0)+NVL(ls_XMJE07,0)+NVL(ls_XMJE08,0)+NVL(ls_XMJE09,0)+NVL(ls_XMJE10,0)+NVL(ls_XMJE11,0)+NVL(ls_XMJE12,0)+NVL(ls_XMJE13,0)+NVL(ls_XMJE73,0); --重新计算医保结算金额 --ls_XMJE10 := NVL(ls_XMJE10,0) - NVL(ls_XMJE17,0); --10.1 票据收入其中：①医保结算 10.2 ②新农合结算 --20.1 支票收入其中：①医保结算 20.2 ②新农合结算 --17.1 退医保款其中：①退医保 17.2 ②退新农合 --25.1 医保结算其中：①医保结算 25.2 ②新农合结算 ls_XMJE74 :=nvl(sfy.XMJE74,0); --支票收入新农合 ls_XMJE75 :=nvl(sfy.XMJE20,0)-nvl(sfy.XMJE74,0); --支票收入医保 --ls_XMJE75 :=sfy.XMJE75; ls_XMJE76 :=nvl(sfy.XMJE76,0); --票据收入新农合 ls_XMJE77 :=nvl(sfy.XMJE10,0)-nvl(sfy.XMJE76,0); --票据收入医保 --ls_XMJE77 :=sfy.XMJE77; ls_XMJE78 :=nvl(sfy.XMJE78,0); --退医保款新农合 ls_XMJE79 :=nvl(sfy.XMJE17,0)-nvl(sfy.XMJE78,0); --退医保款医保 --ls_XMJE79 :=sfy.XMJE79; ls_XMJE80 :=nvl(ls_XMJE76,0)-nvl(ls_XMJE78,0); --医保结算农合 ls_XMJE81 :=nvl(ls_XMJE77,0)-nvl(ls_XMJE79,0); --医保结算医保 --微信支付 LS_WXZFPJ:=sfy.WXZFPJ;--微信支付票据 LS_WXZFSR:=sfy.WXZFSR;--微信支付收入 --微信支付总收入 SP_EXECUTE_SQL('Update BM_GHKSTJ set GHKS'||ls_KSBH00||'='||to_char(NVL(LS_WXZFSR,0))||'where ID0000='||to_char(ad_ID0000)||' and GHKS199 = 21.01'); --民生通支付 LS_MST0PJ:=sfy.MST0PJ;--民生通票据 LS_MST0SR:=sfy.MST0SR;--民生通收入 --民生通总收入 SP_EXECUTE_SQL('Update BM_GHKSTJ set GHKS'||ls_KSBH00||'='||to_char(NVL(LS_MST0SR,0))||'where ID0000='||to_char(ad_ID0000)||' and GHKS199 = 21.02'); --支付宝支付 LS_ZFBZFPJ:=sfy.ZFBZFPJ;--支付宝票据 LS_ZFBZFSR:=sfy.ZFBZFSR;--支付宝通收入 --支付宝总收入 SP_EXECUTE_SQL('Update BM_GHKSTJ set GHKS'||ls_KSBH00||'='||to_char(NVL(LS_ZFBZFSR,0))||'where ID0000='||to_char(ad_ID0000)||' and GHKS199 = 21.03'); LS_QYPTZF:= nvl(sfy.QYPTZF,0); --区域平台支付 SP_EXECUTE_SQL('Update BM_GHKSTJ set GHKS'||ls_KSBH00||'='||to_char(nvl(LS_QYPTZF,0))||'where ID0000='||to_char(ad_ID0000)||' and GHKS199 = 21.04'); --ls_XMJE80 :=sfy.XMJE76-sfy.XMJE78; --ls_XMJE81 :=sfy.XMJE77-sfy.XMJE79; SP_EXECUTE_SQL('Update BM_GHKSTJ set GHKS'||ls_KSBH00||'='||to_char(nvl(ls_XMJE01,0))||',BEIZHU ='''||sfy.BEIZH0||''' where ID0000='||to_char(ad_ID0000)||' and GHKS199 = 1 '); --现金挂号单收入 SP_EXECUTE_SQL('Update BM_GHKSTJ set GHKS'||ls_KSBH00||'='||to_char(nvl(ls_XMJE02,0))||',BEIZHU ='''||sfy.BEIZH1||''' where ID0000='||to_char(ad_ID0000)||' and GHKS199 = 2 '); --预交金收入 SP_EXECUTE_SQL('Update BM_GHKSTJ set GHKS'||ls_KSBH00||'='||to_char(nvl(ls_XMJE03,0))||',BEIZHU ='''||sfy.BEIZH2||''' where ID0000='||to_char(ad_ID0000)||' and GHKS199 = 3 '); --预交金现金退出 SP_EXECUTE_SQL('Update BM_GHKSTJ set GHKS'||ls_KSBH00||'='||to_char(nvl(ls_XMJE04,0))||',BEIZHU ='''||sfy.BEIZH3||''' where ID0000='||to_char(ad_ID0000)||' and GHKS199 = 4 '); --票据收入 SP_EXECUTE_SQL('Update BM_GHKSTJ set GHKS'||ls_KSBH00||'='||to_char(nvl(ls_XMJE05,0))||' where ID0000='||to_char(ad_ID0000)||' and GHKS199 = 5 '); --现金 SP_EXECUTE_SQL('Update BM_GHKSTJ set GHKS'||ls_KSBH00||'='||to_char(nvl(ls_XMJE06,0))||' where ID0000='||to_char(ad_ID0000)||' and GHKS199 = 6 '); --支票 SP_EXECUTE_SQL('Update BM_GHKSTJ set GHKS'||ls_KSBH00||'='||to_char(nvl(ls_XMJE07,0))||' where ID0000='||to_char(ad_ID0000)||' and GHKS199 = 7 '); --银行卡 SP_EXECUTE_SQL('Update BM_GHKSTJ set GHKS'||ls_KSBH00||'='||to_char(nvl(ls_XMJE08,0))||' where ID0000='||to_char(ad_ID0000)||' and GHKS199 = 8 '); --记帐 SP_EXECUTE_SQL('Update BM_GHKSTJ set GHKS'||ls_KSBH00||'='||to_char(nvl(ls_XMJE09,0))||' where ID0000='||to_char(ad_ID0000)||' and GHKS199 = 9 '); --票据就诊卡 SP_EXECUTE_SQL('Update BM_GHKSTJ set GHKS'||ls_KSBH00||'='||to_char(nvl(ls_XMJE10,0))||' where ID0000='||to_char(ad_ID0000)||' and GHKS199 = 10'); --票据医保结算 SP_EXECUTE_SQL('Update BM_GHKSTJ set GHKS'||ls_KSBH00||'='||to_char(nvl(ls_XMJE11,0))||' where ID0000='||to_char(ad_ID0000)||' and GHKS199 = 11'); --其它结算 SP_EXECUTE_SQL('Update BM_GHKSTJ set GHKS'||ls_KSBH00||'='||to_char(nvl(ls_XMJE12,0))||' where ID0000='||to_char(ad_ID0000)||' and GHKS199 = 12'); --补打收据款 SP_EXECUTE_SQL('Update BM_GHKSTJ set GHKS'||ls_KSBH00||'='||to_char(nvl(ls_XMJE13,0))||' where ID0000='||to_char(ad_ID0000)||' and GHKS199 = 13'); --换新收据 SP_EXECUTE_SQL('Update BM_GHKSTJ set GHKS'||ls_KSBH00||'='||to_char(nvl(ls_XMJE14,0))||' where ID0000='||to_char(ad_ID0000)||' and GHKS199 = 14'); --退款收据 SP_EXECUTE_SQL('Update BM_GHKSTJ set GHKS'||ls_KSBH00||'='||to_char(nvl(ls_XMJE15,0))||' where ID0000='||to_char(ad_ID0000)||' and GHKS199 = 15'); --票据退款(现金) SP_EXECUTE_SQL('Update BM_GHKSTJ set GHKS'||ls_KSBH00||'='||to_char(nvl(ls_XMJE16,0))||' where ID0000='||to_char(ad_ID0000)||' and GHKS199 = 16'); --就诊卡退款 SP_EXECUTE_SQL('Update BM_GHKSTJ set GHKS'||ls_KSBH00||'='||to_char(nvl(ls_XMJE17,0))||' where ID0000='||to_char(ad_ID0000)||' and GHKS199 = 17'); --退医保款 SP_EXECUTE_SQL('Update BM_GHKSTJ set GHKS'||ls_KSBH00||'='||to_char(nvl(ls_XMJE18,0))||' where ID0000='||to_char(ad_ID0000)||' and GHKS199 = 18'); --换旧收据 SP_EXECUTE_SQL('Update BM_GHKSTJ set GHKS'||ls_KSBH00||'='||to_char(nvl(ls_XMJE20,0))||' where ID0000='||to_char(ad_ID0000)||' and GHKS199 = 20'); --支票收入 SP_EXECUTE_SQL('Update BM_GHKSTJ set GHKS'||ls_KSBH00||'='||to_char(nvl(ls_XMJE21,0))||' where ID0000='||to_char(ad_ID0000)||' and GHKS199 = 21'); --银行卡收入 SP_EXECUTE_SQL('Update BM_GHKSTJ set GHKS'||ls_KSBH00||'='||to_char(nvl(ls_XMJE22,0))||' where ID0000='||to_char(ad_ID0000)||' and GHKS199 = 22'); --不足转账起点 SP_EXECUTE_SQL('Update BM_GHKSTJ set GHKS'||ls_KSBH00||'='||to_char(nvl(ls_XMJE23,0))||' where ID0000='||to_char(ad_ID0000)||' and GHKS199 = 23'); --银行卡结算 SP_EXECUTE_SQL('Update BM_GHKSTJ set GHKS'||ls_KSBH00||'='||to_char(nvl(ls_XMJE24,0))||' where ID0000='||to_char(ad_ID0000)||' and GHKS199 = 24'); --记账 SP_EXECUTE_SQL('Update BM_GHKSTJ set GHKS'||ls_KSBH00||'='||to_char(NVL(ls_XMJE10,0)-NVL(ls_XMJE17,0))||' where ID0000='||to_char(ad_ID0000)||' and GHKS199 = 25'); --医保结算 SP_EXECUTE_SQL('Update BM_GHKSTJ set GHKS'||ls_KSBH00||'='||to_char(NVL(ls_XMJE26,0))||' where ID0000='||to_char(ad_ID0000)||' and GHKS199 = 26'); --减面医药费 SP_EXECUTE_SQL('Update BM_GHKSTJ set GHKS'||ls_KSBH00||'='||to_char(NVL(ls_XMJE29,0))||' where ID0000='||to_char(ad_ID0000)||' and GHKS199 = 21.1'); --电子钱包总收入 SP_EXECUTE_SQL('Update BM_GHKSTJ set GHKS'||ls_KSBH00||'='||to_char(NVL(ls_XMJE73,0))||' where ID0000='||to_char(ad_ID0000)||' and GHKS199 = 8.1'); --电子钱包票据收入 SP_EXECUTE_SQL('Update BM_GHKSTJ set GHKS'||ls_KSBH00||'='||to_char(NVL(ls_XMJE74,0))||' where ID0000='||to_char(ad_ID0000)||' and GHKS199 = 20.2'); --支票收入(新农合) SP_EXECUTE_SQL('Update BM_GHKSTJ set GHKS'||ls_KSBH00||'='||to_char(NVL(ls_XMJE75,0))||' where ID0000='||to_char(ad_ID0000)||' and GHKS199 = 20.1'); --支票收入(医保) SP_EXECUTE_SQL('Update BM_GHKSTJ set GHKS'||ls_KSBH00||'='||to_char(NVL(ls_XMJE76,0))||' where ID0000='||to_char(ad_ID0000)||' and GHKS199 = 10.2'); SP_EXECUTE_SQL('Update BM_GHKSTJ set GHKS'||ls_KSBH00||'='||to_char(NVL(ls_XMJE77,0))||' where ID0000='||to_char(ad_ID0000)||' and GHKS199 = 10.1'); SP_EXECUTE_SQL('Update BM_GHKSTJ set GHKS'||ls_KSBH00||'='||to_char(NVL(ls_XMJE78,0))||' where ID0000='||to_char(ad_ID0000)||' and GHKS199 = 17.2'); SP_EXECUTE_SQL('Update BM_GHKSTJ set GHKS'||ls_KSBH00||'='||to_char(NVL(ls_XMJE79,0))||' where ID0000='||to_char(ad_ID0000)||' and GHKS199 = 17.1'); SP_EXECUTE_SQL('Update BM_GHKSTJ set GHKS'||ls_KSBH00||'='||to_char(NVL(ls_XMJE80,0))||' where ID0000='||to_char(ad_ID0000)||' and GHKS199 = 25.2'); SP_EXECUTE_SQL('Update BM_GHKSTJ set GHKS'||ls_KSBH00||'='||to_char(NVL(ls_XMJE81,0))||' where ID0000='||to_char(ad_ID0000)||' and GHKS199 = 25.1'); --计算应缴金额 MZSF-20080805-003 --19, 7、应缴金额 -- select sum(NVL(decode(ls_KSBH00,'01',GHKS01,'02',GHKS02,'03',GHKS03,'04',GHKS04,'05',GHKS05,'06',GHKS06,'07',GHKS07,'08',GHKS08,'09',GHKS09,'10',GHKS10, -- '11',GHKS11,'12',GHKS12,'13',GHKS13,'14',GHKS14,'15',GHKS15,'16',GHKS16,'17',GHKS17,'18',GHKS18,'19',GHKS19,'20',GHKS20, -- '21',GHKS21,'22',GHKS22,'23',GHKS23,'24',GHKS24,'25',GHKS25,'26',GHKS26,'27',GHKS27,'28',GHKS28,'29',GHKS29,'30',GHKS30, -- '31',GHKS31,'32',GHKS32,'33',GHKS33,'34',GHKS34,'35',GHKS35,'36',GHKS36,'37',GHKS37,'38',GHKS38,'39',GHKS39,'40',GHKS40, -- '41',GHKS41,'42',GHKS42,'43',GHKS43,'44',GHKS44,'45',GHKS45,'46',GHKS46,'47',GHKS47,'48',GHKS48,'49',GHKS49,'50',GHKS50, -- '51',GHKS51,'52',GHKS52,'53',GHKS53,'54',GHKS54,'55',GHKS55,'56',GHKS56,'57',GHKS57,'58',GHKS58,'59',GHKS59,'60',GHKS60, -- '61',GHKS61,'62',GHKS62,'63',GHKS63,'64',GHKS64,'65',GHKS65,'66',GHKS66,'67',GHKS67,'68',GHKS68,'69',GHKS69,'70',GHKS70, -- '71',GHKS71,'72',GHKS72,'73',GHKS73,'74',GHKS74,'75',GHKS75,'76',GHKS76,'77',GHKS77,'78',GHKS78,'79',GHKS79,'80',GHKS80, -- '81',GHKS81,'82',GHKS82,'83',GHKS83,'84',GHKS84,'85',GHKS85,'86',GHKS86,'87',GHKS87,'88',GHKS88,'89',GHKS89,'90',GHKS90, -- '91',GHKS91,'92',GHKS92,'93',GHKS93,'94',GHKS94,'95',GHKS95,'96',GHKS96,'97',GHKS97,'98',GHKS98,'99',GHKS99,'100',GHKS100, -- '101',GHKS101,'102',GHKS102,'103',GHKS103,'104',GHKS104,'105',GHKS105,'106',GHKS106,'107',GHKS107,'108',GHKS108,'109',GHKS109,'110',GHKS110, -- '111',GHKS111,'112',GHKS112,'113',GHKS113,'114',GHKS114,'115',GHKS115,'116',GHKS116,'117',GHKS117,'118',GHKS118,'119',GHKS119,'120',GHKS120, -- '121',GHKS121,'122',GHKS122,'123',GHKS123,'124',GHKS124,'125',GHKS125,'126',GHKS126,'127',GHKS127,'128',GHKS128,'129',GHKS129,'130',GHKS130, -- '131',GHKS131,'132',GHKS132,'133',GHKS133,'134',GHKS134,'135',GHKS135,'136',GHKS136,'137',GHKS137,'138',GHKS138,'139',GHKS139,'140',GHKS140, -- '141',GHKS141,'142',GHKS142,'143',GHKS143,'144',GHKS144,'145',GHKS145,'146',GHKS146,'147',GHKS147,'148',GHKS148,'149',GHKS149,'150',GHKS150, -- 0),0)) -- into ls_XMJE19 from BM_GHKSTJ where ID0000 = ad_ID0000 -- and ( -- (LS_YHSFKC<>'N' and GHKS199 in (1,2,5,6,7,8,10,12,8.1)) or (LS_YHSFKC='N' and GHKS199 in (1,2,5,6,7,8,10,12,8.1,21)) -- ); SP_EXECUTE_SQL(' insert into BM_GHKSTJ(ID0000,GHRQ00,GHKS200,GHKS199) select '||to_char(ad_ID0000)||',''GHKS200'',nvl(sum(GHKS'||ls_KSBH00||'),0),-1 from BM_GHKSTJ where ID0000='||to_char(ad_ID0000)||' and ( ('''||LS_YHSFKC||'''<>''N'' and GHKS199 in (1,2,5,6,7,8,10,12,8.1)) or ('''||LS_YHSFKC||'''=''N'' and GHKS199 in (1,2,5,6,7,8,10,12,8.1,21)) )'); --预交金收入是否包含银行卡金额 Y:包含 N:不包含 默认值:Y包含 --LS_YHSFKC =N 时预交金收入不包含银行卡 所以应缴金额应把银行卡金额加进去 21 --LS_YHSFKC =Y 时预交金收入包含银行卡 所以应缴金额不把银行卡金额加进去 21 --28, 16、9~14合计 -- select sum(NVL(decode(ls_KSBH00,'01',GHKS01,'02',GHKS02,'03',GHKS03,'04',GHKS04,'05',GHKS05,'06',GHKS06,'07',GHKS07,'08',GHKS08,'09',GHKS09,'10',GHKS10, -- '11',GHKS11,'12',GHKS12,'13',GHKS13,'14',GHKS14,'15',GHKS15,'16',GHKS16,'17',GHKS17,'18',GHKS18,'19',GHKS19,'20',GHKS20, -- '21',GHKS21,'22',GHKS22,'23',GHKS23,'24',GHKS24,'25',GHKS25,'26',GHKS26,'27',GHKS27,'28',GHKS28,'29',GHKS29,'30',GHKS30, -- '31',GHKS31,'32',GHKS32,'33',GHKS33,'34',GHKS34,'35',GHKS35,'36',GHKS36,'37',GHKS37,'38',GHKS38,'39',GHKS39,'40',GHKS40, -- '41',GHKS41,'42',GHKS42,'43',GHKS43,'44',GHKS44,'45',GHKS45,'46',GHKS46,'47',GHKS47,'48',GHKS48,'49',GHKS49,'50',GHKS50, -- '51',GHKS51,'52',GHKS52,'53',GHKS53,'54',GHKS54,'55',GHKS55,'56',GHKS56,'57',GHKS57,'58',GHKS58,'59',GHKS59,'60',GHKS60, -- '61',GHKS61,'62',GHKS62,'63',GHKS63,'64',GHKS64,'65',GHKS65,'66',GHKS66,'67',GHKS67,'68',GHKS68,'69',GHKS69,'70',GHKS70, -- '71',GHKS71,'72',GHKS72,'73',GHKS73,'74',GHKS74,'75',GHKS75,'76',GHKS76,'77',GHKS77,'78',GHKS78,'79',GHKS79,'80',GHKS80, -- '81',GHKS81,'82',GHKS82,'83',GHKS83,'84',GHKS84,'85',GHKS85,'86',GHKS86,'87',GHKS87,'88',GHKS88,'89',GHKS89,'90',GHKS90, -- '91',GHKS91,'92',GHKS92,'93',GHKS93,'94',GHKS94,'95',GHKS95,'96',GHKS96,'97',GHKS97,'98',GHKS98,'99',GHKS99,'100',GHKS100, -- '101',GHKS101,'102',GHKS102,'103',GHKS103,'104',GHKS104,'105',GHKS105,'106',GHKS106,'107',GHKS107,'108',GHKS108,'109',GHKS109,'110',GHKS110, -- '111',GHKS111,'112',GHKS112,'113',GHKS113,'114',GHKS114,'115',GHKS115,'116',GHKS116,'117',GHKS117,'118',GHKS118,'119',GHKS119,'120',GHKS120, -- '121',GHKS121,'122',GHKS122,'123',GHKS123,'124',GHKS124,'125',GHKS125,'126',GHKS126,'127',GHKS127,'128',GHKS128,'129',GHKS129,'130',GHKS130, -- '131',GHKS131,'132',GHKS132,'133',GHKS133,'134',GHKS134,'135',GHKS135,'136',GHKS136,'137',GHKS137,'138',GHKS138,'139',GHKS139,'140',GHKS140, -- '141',GHKS141,'142',GHKS142,'143',GHKS143,'144',GHKS144,'145',GHKS145,'146',GHKS146,'147',GHKS147,'148',GHKS148,'149',GHKS149,'150',GHKS150, -- 0),0)) -- into ls_XMJE28 from BM_GHKSTJ where ID0000 = ad_ID0000 and GHKS199 in (3,15,17); SP_EXECUTE_SQL('insert into BM_GHKSTJ(ID0000,GHRQ00,GHKS200,GHKS199) select '||to_char(ad_ID0000)||',''GHKS200'',nvl(sum(GHKS'||ls_KSBH00||'),0),-2 from BM_GHKSTJ where ID0000='||to_char(ad_ID0000)||' and GHKS199 in (3,15,17)'); --27, 15、实交现金 -- select sum(NVL(decode(ls_KSBH00,'01',GHKS01,'02',GHKS02,'03',GHKS03,'04',GHKS04,'05',GHKS05,'06',GHKS06,'07',GHKS07,'08',GHKS08,'09',GHKS09,'10',GHKS10, -- '11',GHKS11,'12',GHKS12,'13',GHKS13,'14',GHKS14,'15',GHKS15,'16',GHKS16,'17',GHKS17,'18',GHKS18,'19',GHKS19,'20',GHKS20, -- '21',GHKS21,'22',GHKS22,'23',GHKS23,'24',GHKS24,'25',GHKS25,'26',GHKS26,'27',GHKS27,'28',GHKS28,'29',GHKS29,'30',GHKS30, -- '31',GHKS31,'32',GHKS32,'33',GHKS33,'34',GHKS34,'35',GHKS35,'36',GHKS36,'37',GHKS37,'38',GHKS38,'39',GHKS39,'40',GHKS40, -- '41',GHKS41,'42',GHKS42,'43',GHKS43,'44',GHKS44,'45',GHKS45,'46',GHKS46,'47',GHKS47,'48',GHKS48,'49',GHKS49,'50',GHKS50, -- '51',GHKS51,'52',GHKS52,'53',GHKS53,'54',GHKS54,'55',GHKS55,'56',GHKS56,'57',GHKS57,'58',GHKS58,'59',GHKS59,'60',GHKS60, -- '61',GHKS61,'62',GHKS62,'63',GHKS63,'64',GHKS64,'65',GHKS65,'66',GHKS66,'67',GHKS67,'68',GHKS68,'69',GHKS69,'70',GHKS70, -- '71',GHKS71,'72',GHKS72,'73',GHKS73,'74',GHKS74,'75',GHKS75,'76',GHKS76,'77',GHKS77,'78',GHKS78,'79',GHKS79,'80',GHKS80, -- '81',GHKS81,'82',GHKS82,'83',GHKS83,'84',GHKS84,'85',GHKS85,'86',GHKS86,'87',GHKS87,'88',GHKS88,'89',GHKS89,'90',GHKS90, -- '91',GHKS91,'92',GHKS92,'93',GHKS93,'94',GHKS94,'95',GHKS95,'96',GHKS96,'97',GHKS97,'98',GHKS98,'99',GHKS99,'100',GHKS100, -- '101',GHKS101,'102',GHKS102,'103',GHKS103,'104',GHKS104,'105',GHKS105,'106',GHKS106,'107',GHKS107,'108',GHKS108,'109',GHKS109,'110',GHKS110, -- '111',GHKS111,'112',GHKS112,'113',GHKS113,'114',GHKS114,'115',GHKS115,'116',GHKS116,'117',GHKS117,'118',GHKS118,'119',GHKS119,'120',GHKS120, -- '121',GHKS121,'122',GHKS122,'123',GHKS123,'124',GHKS124,'125',GHKS125,'126',GHKS126,'127',GHKS127,'128',GHKS128,'129',GHKS129,'130',GHKS130, -- '131',GHKS131,'132',GHKS132,'133',GHKS133,'134',GHKS134,'135',GHKS135,'136',GHKS136,'137',GHKS137,'138',GHKS138,'139',GHKS139,'140',GHKS140, -- '141',GHKS141,'142',GHKS142,'143',GHKS143,'144',GHKS144,'145',GHKS145,'146',GHKS146,'147',GHKS147,'148',GHKS148,'149',GHKS149,'150',GHKS150, -- 0),0)) -- into ls_XMJE27 from BM_GHKSTJ where ID0000 = ad_ID0000 and GHKS199 in (20,21,22,23,24,24.1,24.2,25,26,21.1); SP_EXECUTE_SQL('insert into BM_GHKSTJ(ID0000,GHRQ00,GHKS200,GHKS199) select '||to_char(ad_ID0000)||',''GHKS200'',nvl(sum(GHKS'||ls_KSBH00||'),0),-3 from BM_GHKSTJ where ID0000='||to_char(ad_ID0000)||' and GHKS199 in (20,21,22,23,24,24.1,24.2,25,26,21.1)'); select nvl(sum(decode(GHKS199,-1,GHKS200,0)),0),nvl(sum(decode(GHKS199,-2,GHKS200,0)),0),nvl(sum(decode(GHKS199,-3,GHKS200,0)),0) into ls_XMJE19,ls_XMJE28,ls_XMJE27 from BM_GHKSTJ where ID0000=ad_ID0000 and GHRQ00='GHKS200'; delete from BM_GHKSTJ where ID0000=ad_ID0000 and GHRQ00='GHKS200'; --应缴金额(不扣除掉微信支付、支付宝支付) by MZSF-20170508-001 SP_EXECUTE_SQL('Update BM_GHKSTJ set GHKS'||ls_KSBH00||' = '||to_char(NVL(ls_XMJE19,0) - NVL(ls_XMJE28,0)-nvl(LS_MST0SR,0))||' where ID0000 = '||to_char(ad_ID0000)||' and GHKS199 = 19'); --应缴金额 SP_EXECUTE_SQL('Update BM_GHKSTJ set GHKS'||ls_KSBH00||' = '||to_char(NVL(ls_XMJE19,0) - NVL(ls_XMJE28,0)-nvl(LS_MST0SR,0))||' where ID0000 = '||to_char(ad_ID0000)||' and GHKS199 = 28'); --应缴金额 --SP_EXECUTE_SQL('Update BM_GHKSTJ set GHKS'||ls_KSBH00||' = '||to_char(NVL(ls_XMJE19,0) - NVL(ls_XMJE28,0)-nvl(LS_WXZFSR,0)-nvl(LS_ZFBZFSR,0)-nvl(LS_MST0SR,0))||' where ID0000 = '||to_char(ad_ID0000)||' and GHKS199 = 19'); --应缴金额 --SP_EXECUTE_SQL('Update BM_GHKSTJ set GHKS'||ls_KSBH00||' = '||to_char(NVL(ls_XMJE19,0) - NVL(ls_XMJE28,0)-nvl(LS_WXZFSR,0)-nvl(LS_ZFBZFSR,0)-nvl(LS_MST0SR,0))||' where ID0000 = '||to_char(ad_ID0000)||' and GHKS199 = 28'); --应缴金额 --发现退医保款有问题,所以添加了医保款的部分 Lv_SJXJ00:=NVL(ls_XMJE19,0) - NVL(ls_XMJE28,0) - NVL(ls_XMJE27,0); if nvl(lv_TJFS02,'0')='1' then ----实交现金=实交现金-(1)挂号单收入-(4.1)现金+(5.1)票据退款 Lv_SJXJ00:=nvl(Lv_SJXJ00,0)-NVL(ls_XMJE01,0)-NVL(ls_XMJE05,0)+NVL(ls_XMJE15,0); end if; --择日住院病例 select NVL(sum(-B.XJJE00),0) into ls_ZRZYYJ from ZY_BRJFB0 A,ZY_PJSYQK B where A.PJH000 = B.PJH000 and B.PLBH00 in (4,8) 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 = sfy.YGBH00 --and B.PJZT00='0' --and nvl(trim(B.BDBZ00),'N')='N' and A.JZDH00 <> 1 --作废的预交金票据不计算在内！ and A.ZRZYLS is not null and exists (select 1 from BM_BMBM00 where BMBH00=A.CZYKS0 and (trim(YYID00)=as_YYID00 or as_YYID00='0')); SP_EXECUTE_SQL('Update BM_GHKSTJ set GHKS'||ls_KSBH00||' = '||to_char(Lv_SJXJ00-nvl(LS_WXZFSR,0)-nvl(LS_ZFBZFSR,0)-nvl(LS_MST0SR,0)-nvl(LS_QYPTZF,0)-nvl(ls_ZRZYYJ,0))||' where ID0000 = '||to_char(ad_ID0000)||' and GHKS199 = 27'); --实交现金 SP_EXECUTE_SQL('Update BM_GHKSTJ set GHKS'||ls_KSBH00||' = '||to_char(nvl(ls_ZRZYYJ,0))||' where ID0000 = '||to_char(ad_ID0000)||' and GHKS199 = 27.1'); --择日住院病例 END LOOP; 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)+ NVL(GHKS80,0) + NVL(GHKS81,0)+ NVL(GHKS82,0)+ NVL(GHKS83,0)+ NVL(GHKS84,0)+ NVL(GHKS85,0)+ NVL(GHKS86,0)+ NVL(GHKS87,0)+ NVL(GHKS88,0)+ NVL(GHKS89,0)+ NVL(GHKS90,0) + NVL(GHKS91,0)+ NVL(GHKS92,0)+ NVL(GHKS93,0)+ NVL(GHKS94,0)+ NVL(GHKS95,0)+ NVL(GHKS96,0)+ NVL(GHKS97,0)+ NVL(GHKS98,0)+ NVL(GHKS99,0)+ NVL(GHKS100,0) + NVL(GHKS101,0)+ NVL(GHKS102,0)+ NVL(GHKS103,0)+ NVL(GHKS104,0)+ NVL(GHKS105,0)+ NVL(GHKS106,0)+ NVL(GHKS107,0)+ NVL(GHKS108,0)+ NVL(GHKS109,0)+ NVL(GHKS110,0) + NVL(GHKS111,0)+ NVL(GHKS112,0)+ NVL(GHKS113,0)+ NVL(GHKS114,0)+ NVL(GHKS115,0)+ NVL(GHKS116,0)+ NVL(GHKS117,0)+ NVL(GHKS118,0)+ NVL(GHKS119,0)+ NVL(GHKS120,0) + NVL(GHKS121,0)+ NVL(GHKS122,0)+ NVL(GHKS123,0)+ NVL(GHKS124,0)+ NVL(GHKS125,0)+ NVL(GHKS126,0)+ NVL(GHKS127,0)+ NVL(GHKS128,0)+ NVL(GHKS129,0)+ NVL(GHKS130,0) + NVL(GHKS131,0)+ NVL(GHKS132,0)+ NVL(GHKS133,0)+ NVL(GHKS134,0)+ NVL(GHKS135,0)+ NVL(GHKS136,0)+ NVL(GHKS137,0)+ NVL(GHKS138,0)+ NVL(GHKS139,0)+ NVL(GHKS140,0) + NVL(GHKS141,0)+ NVL(GHKS142,0)+ NVL(GHKS143,0)+ NVL(GHKS144,0)+ NVL(GHKS145,0)+ NVL(GHKS146,0)+ NVL(GHKS147,0)+ NVL(GHKS148,0)+ NVL(GHKS149,0)+ NVL(GHKS150,0) where ID0000 = ad_ID0000; UPDATE BM_GHKSTJ SET DYID00=GHKS199*100 where ID0000 = ad_ID0000 AND DYID00=0; --这样就可以排序了 END if; if nvl(lv_TJFS01,'0')='1' 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,GHKS80, GHKS81,GHKS82,GHKS83,GHKS84,GHKS85,GHKS86,GHKS87,GHKS88,GHKS89,GHKS90, GHKS91,GHKS92,GHKS93,GHKS94,GHKS95,GHKS96,GHKS97,GHKS98,GHKS99,GHKS100, GHKS101,GHKS102,GHKS103,GHKS104,GHKS105,GHKS106,GHKS107,GHKS108,GHKS109,GHKS110, GHKS111,GHKS112,GHKS113,GHKS114,GHKS115,GHKS116,GHKS117,GHKS118,GHKS119,GHKS120, GHKS121,GHKS122,GHKS123,GHKS124,GHKS125,GHKS126,GHKS127,GHKS128,GHKS129,GHKS130, GHKS131,GHKS132,GHKS133,GHKS134,GHKS135,GHKS136,GHKS137,GHKS138,GHKS139,GHKS140, GHKS141,GHKS142,GHKS143,GHKS144,GHKS145,GHKS146,GHKS147,GHKS148,GHKS149,GHKS150, 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(GHKS80), sum(GHKS81),sum(GHKS82),sum(GHKS83),sum(GHKS84),sum(GHKS85),sum(GHKS86),sum(GHKS87),sum(GHKS88),sum(GHKS89),sum(GHKS90), sum(GHKS91),sum(GHKS92),sum(GHKS93),sum(GHKS94),sum(GHKS95),sum(GHKS96),sum(GHKS97),sum(GHKS98),sum(GHKS99),sum(GHKS100), sum(GHKS101),sum(GHKS102),sum(GHKS103),sum(GHKS104),sum(GHKS105),sum(GHKS106),sum(GHKS107),sum(GHKS108),sum(GHKS109),sum(GHKS110), sum(GHKS111),sum(GHKS112),sum(GHKS113),sum(GHKS114),sum(GHKS115),sum(GHKS116),sum(GHKS117),sum(GHKS118),sum(GHKS119),sum(GHKS120), sum(GHKS121),sum(GHKS122),sum(GHKS123),sum(GHKS124),sum(GHKS125),sum(GHKS126),sum(GHKS127),sum(GHKS128),sum(GHKS129),sum(GHKS130), sum(GHKS131),sum(GHKS132),sum(GHKS133),sum(GHKS134),sum(GHKS135),sum(GHKS136),sum(GHKS137),sum(GHKS138),sum(GHKS139),sum(GHKS140), sum(GHKS141),sum(GHKS142),sum(GHKS143),sum(GHKS144),sum(GHKS145),sum(GHKS146),sum(GHKS147),sum(GHKS148),sum(GHKS149),sum(GHKS150), sum(XMHJ00),0 from BM_GHKSTJ where ID0000 = ad_ID0000; end if; commit; EXCEPTION WHEN OTHERS THEN as_YHMSG0:='数据库错误：出错原因未知！'; as_SYSMSG:=substr(sqlERRM,1,150); ROLLBACK; END;