prompt 82、生成门诊收费员现金日报表对应的会计信息的存储过程 SP_SF_JKKJXX_TJ0000 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_COMMIT in char default 'Y' --是否提交 Y是 N否，默认Y ) 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 修改公务员补助A.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 --zhuyr 2018.06.10 将5.0该存储过程脚本导入9.0环境中 by MZSF9-20180608-001 -- qks 2019.08.06 去掉ls_YYID00='222211'这部分特殊处理脚本；当对应IC_YBBRLB.YBBHSB包含16,表示公务员补助独立于统筹支付;去掉IC_YBBRLB.YBBHSB包含14判断条件,直接改用nvl(A.MZBZJE,0)+nvl(A.SYBXJE,0); ZFFS00=18 电子钱包也归入ZFFS00=26 民生通支付; for MZSF9-20190805-005 -- qks 2020.01.15 解决部分自助机操作员统计所属分院不对问题; for MZSF9-20200115-001 --linzetao 2020.02.21 处理预交金收入和退款使用相同票据时的退款数据 for MZSF9-20200218-001 --linzetao 2020.05.26 修正参数SF_XJSHBSJXJ=N时预交金收入不包括银行卡而预交金退出却包括银行卡收入, 造成实缴现金数据不正确问题 MZSF9-20200415-004 -- qks 2020.06.02 预交金额补打导致PLBHLX值不对,修正PLBHLX取值; for MZSF9-20200601-001 -- jlg 2020.09.27 重整过程，增加入参as_COMMIT MZSF9-20200918-001 -- linzetao 2023.07.11 处理HIS内部字典“ReportExclude_CZY”中设置的不列入统计的收费员 MZSF9-20230706-002 -- linzetao 2024.03.11 调整统计类型按结算日期统计(as_TJLX00=1)时移除相关票据表判定 MZSF9-20240311-002 -- chenHeyi 2024.04.09 优化SP_SF_JKKJXX_TJ0000过程执行缓慢的问题 MZSF9-20240408-001 -- chenHeyi 2024.12.06 预交金退款有两种数据PLBH00=（7，2），要累加如：GHKS92=nvl(GHKS92,0)+AA.XMJE01*AA.PLBHLX ------------ ------------- ---------------------------------------------------------------------------- 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_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_BTJSFY BM_TYZD00.MC0000%type; 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 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 nvl(max(VALUE0),'0') into ls_QYDZQB from XT_XTCS00 where NAME00='SF_SFQYDZQB'; select nvl(max(VALUE0),'Y') into LS_XJSRSHBSFTJ from XT_XTCS00 where NAME00='SF_XJSRSHBSFTJ'; select nvl(max(VALUE0),'Y') into LS_YHSFKC from XT_XTCS00 where NAME00='SF_XJSHBSJXJ'; --预交金收入是否包含银行卡金额 Y:包含 N:不包含 默认值:Y包含 ls_BTJSFY := trim(SF_XT_GETYBSXZD('ReportExclude_CZY', 'SP_SF_JKKJXX_TJ0000', '3', '3', -1)); 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 (A.JFCZY0=ld_YGBH00 or ld_YGBH00=-1) and (ls_BTJSFY is null or instr(','||ls_BTJSFY||',',','||A.JFCZY0||',') = 0) 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 (A.JZY000=ld_YGBH00 or ld_YGBH00=-1) and (ls_BTJSFY is null or instr(','||ls_BTJSFY||',',','||A.JZY000||',') = 0) ) 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; insert into BM_GHKSTJ(ID0000,GHKS199,GHRQ00) values(ad_ID0000, 1,' 1、挂号单收入 '); insert into BM_GHKSTJ(ID0000,GHKS199,GHRQ00) values(ad_ID0000, 2,' 2、预交金收入 '); insert into BM_GHKSTJ(ID0000,GHKS199,GHRQ00) values(ad_ID0000, 3,' 3、预交金现金退出 '); insert into BM_GHKSTJ(ID0000,GHKS199,GHRQ00) values(ad_ID0000, 4,' 4、票据收入 '); 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)记账 '); if SF_SFQYYKTPT = 'Y' then --一卡通 insert into BM_GHKSTJ(ID0000,GHKS199,GHRQ00) values(ad_ID0000,8.1,' (A)一卡通 '); end if; if SF_SFQYYYTPT = 'Y' then --银医通 insert into BM_GHKSTJ(ID0000,GHKS199,GHRQ00) values(ad_ID0000,8.1,' (A)银医通 '); end if; 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,10.1,' 其中：①医保结算'); insert into BM_GHKSTJ(ID0000,GHKS199,GHRQ00) values(ad_ID0000,10.2,' ②新农合结算'); 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,' 5、退款收据 '); 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,' 其中：①退医保'); insert into BM_GHKSTJ(ID0000,GHKS199,GHRQ00) values(ad_ID0000,17.2,' ②退新农合'); insert into BM_GHKSTJ(ID0000,GHKS199,GHRQ00) values(ad_ID0000,18,' 6、换(旧)收据 '); insert into BM_GHKSTJ(ID0000,GHKS199,GHRQ00) values(ad_ID0000,19,' 7、应缴金额 '); insert into BM_GHKSTJ(ID0000,GHKS199,GHRQ00) values(ad_ID0000,20,' 8、支票收入 '); insert into BM_GHKSTJ(ID0000,GHKS199,GHRQ00) values(ad_ID0000,20.1,' 其中: (1)支票收入(医保) '); insert into BM_GHKSTJ(ID0000,GHKS199,GHRQ00) values(ad_ID0000,20.2,' (2)支票收入(新农合) '); insert into BM_GHKSTJ(ID0000,GHKS199,GHRQ00) values(ad_ID0000,21,' 9、银行卡收入 '); insert into BM_GHKSTJ(ID0000,GHKS199,GHRQ00) values(ad_ID0000,21.01,' A、微信支付 '); if SF_SFQYJKTPT='Y' then --民生通(健康通) insert into BM_GHKSTJ(ID0000,GHKS199,GHRQ00) values(ad_ID0000,21.02,' B、健康通支付 '); end if; if SF_SFQYYKTPT = 'Y' then --一卡通 insert into BM_GHKSTJ(ID0000,GHKS199,GHRQ00) values(ad_ID0000,21.1,' B、一卡通支付 '); end if; if SF_SFQYYYTPT = 'Y' then --银医通 insert into BM_GHKSTJ(ID0000,GHKS199,GHRQ00) values(ad_ID0000,21.1,' B、银医通支付 '); end if; insert into BM_GHKSTJ(ID0000,GHKS199,GHRQ00) values(ad_ID0000,21.03,' C、支付宝支付 '); if LS_QYYJJPT='Y' then --区域预交金平台 insert into BM_GHKSTJ(ID0000,GHKS199,GHRQ00) values(ad_ID0000,21.04,' D、区域预交金 '); end if; insert into BM_GHKSTJ(ID0000,GHKS199,GHRQ00) values(ad_ID0000,22,'10、不足转账起点 '); insert into BM_GHKSTJ(ID0000,GHKS199,GHRQ00) values(ad_ID0000,23,'11、银行卡结算 '); insert into BM_GHKSTJ(ID0000,GHKS199,GHRQ00) values(ad_ID0000,24,'12、记账 '); insert into BM_GHKSTJ(ID0000,GHKS199,GHRQ00) values(ad_ID0000,25,'13、医保中心结算 '); insert into BM_GHKSTJ(ID0000,GHKS199,GHRQ00) values(ad_ID0000,25.1,'其中：①医保结算 '); insert into BM_GHKSTJ(ID0000,GHKS199,GHRQ00) values(ad_ID0000,25.2,' ②新农合结算 '); insert into BM_GHKSTJ(ID0000,GHKS199,GHRQ00) values(ad_ID0000,26,'14、减免病人医药费 '); insert into BM_GHKSTJ(ID0000,GHKS199,GHRQ00) values(ad_ID0000,27,'15、实交现金 '); --insert into BM_GHKSTJ(ID0000,DYID00,GHRQ00) values(ad_ID0000,28,'16、9~14合计 '); insert into BM_GHKSTJ(ID0000,GHKS199,GHRQ00) values(ad_ID0000,28,'16、8~15合计 '); --现金挂号单收入,原来的求和有问题,主要是SF_PJSYQK同一个票据号,有一条记录,SF_BRJFB0有多条记录 declare cursor C_XJGH00 is select nvl(sum(B.XJJE00+B.ZZJE00+B.YHKJE0+B.DZQBJE),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 (A.JFCZY0=ld_YGBH00 or ld_YGBH00=-1) and (ls_BTJSFY is null or instr(','||ls_BTJSFY||',',','||A.JFCZY0||',') = 0) 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; --预交金收入,预交金收款单或预交金退款单 --隔天补打预交也统计在内 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, decode(sign(B.XJJE00+B.ZZJE00+B.YHKJE0+B.DZQBJE)-0,-1,-1,1)*decode(nvl(substrb(b.PJXH00,1,1),'0'),'-',-1,1) PLBHLX, ltrim(min(lpad(trim(replace(B.pjxh00,'-','')),30,' ')))||' - '||ltrim(max(lpad(trim(replace(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 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 (B.CZY000=ld_YGBH00 or ld_YGBH00=-1) and (ls_BTJSFY is null or instr(','||ls_BTJSFY||',',','||B.CZY000||',') = 0) --解决部分自助机操作员统计所属分院不对问题 and ( as_YYID00='0' or exists (select 1 from BM_BMBM00 where BMBH00=B.CZYKS0 and trim(YYID00)=as_YYID00)) --票据状态有效 --and B.PJZT00 = '0' group by B.CZY000,PLBH00,decode(sign(B.XJJE00+B.ZZJE00+B.YHKJE0+B.DZQBJE)-0,-1,-1,1)*decode(nvl(substrb(b.PJXH00,1,1),'0'),'-',-1,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 and AA.PLBHLX=1 then --预交金收款单的现金，银行卡，支票部分 update BM_GHKSTJ set GHKS91=nvl(GHKS91,0)+AA.XMJE01,GHKS95=nvl(GHKS95,0)+AA.YHKSR1,GHKS51=nvl(GHKS51,0)+AA.ZPSR01,BEIZH1=AA.BEIZH1,GHKS71=AA.DZQB01 where ID0000=ld_ID0000 and DYID00=AA.CZY000; elsif AA.PLBH00=7 and AA.PLBHLX=-1 then --预交金收款单总金额为负数，则计入预交金退款单 update BM_GHKSTJ set GHKS92=nvl(GHKS92,0)+AA.XMJE01*AA.PLBHLX,GHKS96=nvl(GHKS96,0)+AA.YHKSR1*AA.PLBHLX,GHKS52=nvl(GHKS52,0)+AA.ZPSR01*AA.PLBHLX,BEIZH2=AA.BEIZH1,GHKS72=AA.DZQB01*AA.PLBHLX where ID0000=ld_ID0000 and DYID00=AA.CZY000; 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=AA.BEIZH1,GHKS72=AA.DZQB01 where ID0000=ld_ID0000 and DYID00=AA.CZY000; end if; else if AA.PLBH00=7 and AA.PLBHLX=1 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=7 and AA.PLBHLX=-1 then --预交金收款单总金额为负数，则计入预交金退款单 update BM_GHKSTJ set GHKS92=nvl(GHKS92,0)+AA.XMJE01*AA.PLBHLX,GHKS96=nvl(GHKS96,0)+AA.YHKSR1*AA.PLBHLX,GHKS52=nvl(GHKS52,0)+AA.ZPSR01*AA.PLBHLX,BEIZH2=substr(nvl(BEIZH2,0)||','||AA.BEIZH1,1,200),GHKS72=nvl(GHKS72,0)+AA.DZQB01*AA.PLBHLX 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(BEIZH2,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; else --隔天补打预交按交费时间统计不统计在打印时间内 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,PLBHLX, ltrim(min(lpad(trim(replace(B.pjxh00,'-','')),30,' ')))||' - '||ltrim(max(lpad(trim(replace(B.pjxh00,'-','')),30,' '))) BEIZH1 from ( select B.XJJE00,B.ZZJE00,B.YHKJE0,B.DZQBJE,B.CZY000,B.PLBH00,B.pjxh00,decode(sign((B.XJJE00+B.ZZJE00+B.YHKJE0+B.DZQBJE)-0),-1,-1,1)*decode(nvl(substrb(b.PJXH00,1,1),'0'),'-',-1,1) PLBHLX from SF_PJSYQK B where exists(select 1 from SF_BRJFB0 A where A.PJH000 = B.FZPJH0 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 (B.CZY000=ld_YGBH00 or ld_YGBH00=-1) and (ls_BTJSFY is null or instr(','||ls_BTJSFY||',',','||B.CZY000||',') = 0) and (as_YYID00='0' or exists (select 1 from BM_BMBM00 where BMBH00=B.CZYKS0 and trim(YYID00)=as_YYID00)) --and B.PJZT00 = '0' union all select B.XJJE00,B.ZZJE00,B.YHKJE0,B.DZQBJE,B.CZY000,B.PLBH00,B.pjxh00,decode(sign((B.XJJE00+B.ZZJE00+B.YHKJE0+B.DZQBJE)-0),-1,-1,1)*decode(nvl(substrb(b.PJXH00,1,1),'0'),'-',-1,1) PLBHLX 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 nvl(B.BDBZ00,'N')='Y' --补打预交金 --and B.PJH000=A.PJH000 and B.FZPJH0 = 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 (ls_BTJSFY is null or instr(','||ls_BTJSFY||',',','||B.CZY000||',') = 0) and ( as_YYID00='0' or exists (select 1 from BM_BMBM00 where BMBH00=B.CZYKS0 and trim(YYID00)=as_YYID00)) --and B.PJZT00 = '0' ) B group by B.CZY000,b.PLBH00,b.PLBHLX; 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 and AA.PLBHLX=1 then --预交金收款单的现金，银行卡，支票部分 update BM_GHKSTJ set GHKS91=nvl(GHKS91,0)+AA.XMJE01,GHKS95=nvl(GHKS95,0)+AA.YHKSR1,GHKS51=nvl(GHKS51,0)+AA.ZPSR01,BEIZH1=AA.BEIZH1,GHKS71=AA.DZQB01 where ID0000=ld_ID0000 and DYID00=AA.CZY000; elsif AA.PLBH00=7 and AA.PLBHLX=-1 then --预交金收款单总金额为负数，则计入预交金退款单 update BM_GHKSTJ set GHKS92=nvl(GHKS92,0)+AA.XMJE01*AA.PLBHLX,GHKS96=nvl(GHKS96,0)+AA.YHKSR1*AA.PLBHLX,GHKS52=nvl(GHKS52,0)+AA.ZPSR01*AA.PLBHLX,BEIZH2=AA.BEIZH1,GHKS72=AA.DZQB01*AA.PLBHLX where ID0000=ld_ID0000 and DYID00=AA.CZY000; 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=AA.BEIZH1,GHKS72=AA.DZQB01 where ID0000=ld_ID0000 and DYID00=AA.CZY000; end if; else if AA.PLBH00=7 and AA.PLBHLX=1 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=7 and AA.PLBHLX=-1 then --预交金收款单总金额为负数，则计入预交金退款单 update BM_GHKSTJ set GHKS92=nvl(GHKS92,0)+AA.XMJE01*AA.PLBHLX,GHKS96=nvl(GHKS96,0)+AA.YHKSR1*AA.PLBHLX,GHKS52=nvl(GHKS52,0)+AA.ZPSR01*AA.PLBHLX,BEIZH2=substr(nvl(BEIZH2,0)||','||AA.BEIZH1,1,200),GHKS72=nvl(GHKS72,0)+AA.DZQB01*AA.PLBHLX 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(BEIZH2,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; --票据收入, 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 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 (ls_BTJSFY is null or instr(','||ls_BTJSFY||',',','||A.CZY000||',') = 0) 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) group by B.JFCZY0,ZFFS00; 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 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=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 or AA.ZFFS00=18 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 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=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 or AA.ZFFS00=18 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 (ls_BTJSFY is null or instr(','||ls_BTJSFY||',',','||B.CZY000||',') = 0) 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=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 or AA.ZFFS00=18 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=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 or AA.ZFFS00=18 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=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 or AA.ZFFS00=18 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=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 or AA.ZFFS00=18 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; --按发票日期统计 if nvl(as_TJLX00,'0')='0' then -- 票据收入, 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.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 (ls_BTJSFY is null or instr(','||ls_BTJSFY||',',','||B.CZY000||',') = 0) 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_FYMX00 where JZDH00 = A.JZDH00 and JFLBID = 1 ) group by JZY000; begin for AA in C_YJJJS0 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 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(nvl(A.MZBZJE,0)+nvl(A.SYBXJE,0)+decode(d.sfjsdw,'N',0,A.GRZHZF)+A.TCJJZF+decode(sign(Instrb(','||trim(d.ybbhsb)||',',',16,')),1,A.GWYBZ0,0)),0)XMJE10,JZY000 CZY000 from SF_JZB000 A,IC_YBBRLB D where A.YBLB00=D.YBLB00 and A.FBBH00=D.FBBH00 and A.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 (ls_BTJSFY is null or instr(','||ls_BTJSFY||',',','||B.CZY000||',') = 0) 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(nvl(A.MZBZJE,0)+nvl(A.SYBXJE,0)+decode(d.sfjsdw,'N',0,A.GRZHZF)+A.TCJJZF+decode(sign(Instrb(','||trim(d.ybbhsb)||',',',16,')),1,A.GWYBZ0,0)),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 A.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 (ls_BTJSFY is null or instr(','||ls_BTJSFY||',',','||B.CZY000||',') = 0) 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.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 (ls_BTJSFY is null or instr(','||ls_BTJSFY||',',','||A.CZY000||',') = 0) and (as_YYID00='0' or exists (select 1 from BM_BMBM00 where BMBH00=A.CZYKS0 and trim(YYID00)=as_YYID00 )) ) 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,A.BRID00, abs(nvl(A.MZBZJE,0)+nvl(A.SYBXJE,0)+decode(d.sfjsdw,'N',0,A.GRZHZF)+nvl(A.TCJJZF,0)+decode(sign(Instrb(','||trim(d.ybbhsb)||',',',16,')),1,A.GWYBZ0,0)) 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 (ls_BTJSFY is null or instr(','||ls_BTJSFY||',',','||B.CZY000||',') = 0) 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 select count(*) into ls_count0 from sf_brjfb0 where JZDH00=AA.CXJZDH and JFLBID+0=0 and BRID00=AA.BRID00; 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(nvl(A.MZBZJE,0)+nvl(A.SYBXJE,0)+decode(d.sfjsdw,'N',0,A.GRZHZF)+nvl(A.TCJJZF,0)+decode(sign(Instrb(','||trim(d.ybbhsb)||',',',16,')),1,A.GWYBZ0,0)) XMJE17 from SF_JZB000 A,IC_YBBRLB D where A.YBLB00=D.YBLB00 and A.FBBH00=D.FBBH00 and A.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 (ls_BTJSFY is null or instr(','||ls_BTJSFY||',',','||B.CZY000||',') = 0) 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.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 (ls_BTJSFY is null or instr(','||ls_BTJSFY||',',','||A.CZY000||',') = 0) ) 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) 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.记账 update BM_GHKSTJ set GHKS24=nvl(GHKS24,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 or AA.ZFFS00=18 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.记账 update BM_GHKSTJ set GHKS24=nvl(GHKS24,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 or AA.ZFFS00=18 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 (A.CZY000=ld_YGBH00 or ld_YGBH00=-1) and (ls_BTJSFY is null or instr(','||ls_BTJSFY||',',','||A.CZY000||',') = 0) 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 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; else --按结算日期统计 -- 票据收入, 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 a.HJJE00 > 0 and (as_YYID00='0' or exists(select 1 from BM_BMBM00 where BMBH00=a.CZYKS0 and trim(YYID00)=as_YYID00)) and (ls_BTJSFY is null or instr(','||ls_BTJSFY||',',','||a.JZY000||',') = 0) and (a.JZY000 = ld_YGBH00 or ld_YGBH00=-1) --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 (B.CZY000=ld_YGBH00 or ld_YGBH00=-1) -- and (ls_BTJSFY is null or instr(','||ls_BTJSFY||',',','||B.CZY000||',') = 0) -- 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_FYMX00 where JZDH00 = A.JZDH00 and JFLBID = 1 ) group by JZY000; begin for AA in C_YJJJS0 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 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(nvl(A.MZBZJE,0)+nvl(A.SYBXJE,0)+decode(d.sfjsdw,'N',0,A.GRZHZF)+A.TCJJZF+decode(sign(Instrb(','||trim(d.ybbhsb)||',',',16,')),1,A.GWYBZ0,0)),0)XMJE10,JZY000 CZY000 from SF_JZB000 A,IC_YBBRLB D where A.YBLB00=D.YBLB00 and A.FBBH00=D.FBBH00 and A.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 (as_YYID00='0' or exists(select 1 from BM_BMBM00 where BMBH00=a.CZYKS0 and trim(YYID00)=as_YYID00)) and (ls_BTJSFY is null or instr(','||ls_BTJSFY||',',','||a.JZY000||',') = 0) and (a.JZY000 = ld_YGBH00 or ld_YGBH00=-1) and a.HJJE00 > 0 --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 (ls_BTJSFY is null or instr(','||ls_BTJSFY||',',','||B.CZY000||',') = 0) -- 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 票据收入, 新农合医保结算(按结算日期统计) 2024.03.11 改为按结算表(SF_JZB000)统计 declare cursor C_YBJS0 is select nvl(sum(nvl(A.MZBZJE,0)+nvl(A.SYBXJE,0)+decode(d.sfjsdw,'N',0,A.GRZHZF)+A.TCJJZF+decode(sign(Instrb(','||trim(d.ybbhsb)||',',',16,')),1,A.GWYBZ0,0)),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 A.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 (as_YYID00='0' or exists(select 1 from BM_BMBM00 where BMBH00=a.CZYKS0 and trim(YYID00)=as_YYID00)) and (ls_BTJSFY is null or instr(','||ls_BTJSFY||',',','||a.JZY000||',') = 0) and (a.JZY000 = ld_YGBH00 or ld_YGBH00=-1) and a.HJJE00 > 0 --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 (ls_BTJSFY is null or instr(','||ls_BTJSFY||',',','||B.CZY000||',') = 0) -- 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 (ls_BTJSFY is null or instr(','||ls_BTJSFY||',',','||A.CZY000||',') = 0) 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; --计算退款的医保款,非自付款 2024.03.11 改为按结算日期 declare cursor C_TK0000 is --自费病人负的结帐单号可能在缴费表找不到记录,MZSF-20180426-005增加 nvl(A.YBLB00,'0') select abs(nvl(ZFJE00,0)+nvl(JZJE00,0)) XMJE00,JZY000,CXJZDH,JZDH00,A.BRID00, abs(nvl(A.MZBZJE,0)+nvl(A.SYBXJE,0)+decode(d.sfjsdw,'N',0,A.GRZHZF)+nvl(A.TCJJZF,0)+decode(sign(Instrb(','||trim(d.ybbhsb)||',',',16,')),1,A.GWYBZ0,0)) XMJE17 from SF_JZB000 A,IC_YBBRLB D where nvl(A.YBLB00,'0')=D.YBLB00 and A.FBBH00=D.FBBH00 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 A.HJJE00 < 0 --负单 and (as_YYID00='0' or exists(select 1 from BM_BMBM00 where BMBH00=a.CZYKS0 and trim(YYID00)=as_YYID00)) and (ls_BTJSFY is null or instr(','||ls_BTJSFY||',',','||a.JZY000||',') = 0) and (a.JZY000 = ld_YGBH00 or ld_YGBH00=-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 (ls_BTJSFY is null or instr(','||ls_BTJSFY||',',','||B.CZY000||',') = 0) -- 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 select count(*) into ls_count0 from sf_brjfb0 where JZDH00=AA.CXJZDH and JFLBID+0=0 and BRID00=AA.BRID00; 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(nvl(A.MZBZJE,0)+nvl(A.SYBXJE,0)+decode(d.sfjsdw,'N',0,A.GRZHZF)+nvl(A.TCJJZF,0)+decode(sign(Instrb(','||trim(d.ybbhsb)||',',',16,')),1,A.GWYBZ0,0)) 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.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 A.HJJE00 < 0 and (as_YYID00='0' or exists(select 1 from BM_BMBM00 where BMBH00=a.CZYKS0 and trim(YYID00)=as_YYID00)) and (ls_BTJSFY is null or instr(','||ls_BTJSFY||',',','||a.JZY000||',') = 0) and (a.JZY000 = ld_YGBH00 or ld_YGBH00=-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 (ls_BTJSFY is null or instr(','||ls_BTJSFY||',',','||B.CZY000||',') = 0) -- 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 (A.CZY000=ld_YGBH00 or ld_YGBH00=-1) and (ls_BTJSFY is null or instr(','||ls_BTJSFY||',',','||A.CZY000||',') = 0) ) 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.记账 update BM_GHKSTJ set GHKS24=nvl(GHKS24,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 or AA.ZFFS00=18 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.记账 update BM_GHKSTJ set GHKS24=nvl(GHKS24,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 or AA.ZFFS00=18 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 (A.CZY000=ld_YGBH00 or ld_YGBH00=-1) and (ls_BTJSFY is null or instr(','||ls_BTJSFY||',',','||A.CZY000||',') = 0) 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; 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)); ls_XMJE03 := ABS(nvl(sfy.XJSR02,0)+nvl(sfy.ZPSR02,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、应缴金额 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合计 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、实交现金 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; --预交金包括银行卡收入, 则实交现金减去微信支付、支付宝支付、民生通支付、区域平台支付 if LS_YHSFKC = 'Y' then 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))||' where ID0000 = '||to_char(ad_ID0000)||' and GHKS199 = 27'); --实交现金 else SP_EXECUTE_SQL('update BM_GHKSTJ set GHKS'||ls_KSBH00||' = '||to_char(Lv_SJXJ00)||' where ID0000 = '||to_char(ad_ID0000)||' and GHKS199 = 27'); --实交现金 end if; 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; --这样就可以排序了 if nvl(lv_TJFS01,'0')='1' then 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; if as_COMMIT = 'Y' then commit; end if; exception when others then as_YHMSG0:='数据库错误：出错原因未知！'; as_SYSMSG:=substr(sqlERRM,1,150); rollback; end; / show error; --%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%