CREATE OR REPLACE PROCEDURE SP_SF_YBBRJS_BB0NEW -- MODIFICATION HISTORY -- Person Date Comments -- xuzw 2009.09.16 create; -- XUZW 2009.09.25 独立分中心也显示城镇医保和居民医保 -- jinfl 2013.05.07 农合屏蔽YBLB00='@' MZSF-20130502-001 -- daihq 2014.07.04 修改商保个人帐户、商保统筹基金,ic_ybbrlb.ybbhsb包含14，才算进XMHJ00 for MZSF-20140625-001 -- qiulf 2016.07.19 增加参数SF_YBBRBBAJZJRSX根据记账明细显示 for MZSF-20160622-001 -- zhuyr 2018.08.29 增加医院支付金额 for MZSF-20180824-002 -- gzj 2019.02.18 增加入参as_SFJZFP实现按精准扶贫病人统计 for MZSF-20181229-001 ( as_KSRQ00 IN CHAR, --开始日期 as_JSRQ00 IN CHAR, --结束日期 as_KSSJ00 IN CHAR, --开始时间 as_JSSJ00 IN CHAR, --结束时间 as_CZYXM0 IN CHAR, --操作员姓名 as_YBZXMC IN CHAR, --医保中心名称 --as_TJLB00 IN CHAR, --统计类别'0':按汇总显示,'1':按明细显示 --as_BQMC00 IN CHAR, --病区名称 as_YYID00 IN CHAR, --分院ID as_ZTJS00 IN CHAR, --0统计中按实际日期统计，1按出院日期统计 ad_ID0000 OUT NUMBER, --统计报表对应的ID as_YHMSG0 OUT VARCHAR, --存储过程提示的错误信息 as_SYSMSG OUT VARCHAR, --系统提示的错误信息 as_SFJZFP in char default 'N' --是否按精准扶贫病人统计 ) AS lv_YBFLMC IC_YBBRLB.YBMC00%TYPE; ls_YBMC00 IC_YBBRLB.YBMC00%TYPE; ls_BRID00 BM_BRXXB0.BRID00%TYPE; ls_ZYID00 zy_brxxb0.zyid00%type; ls_DQBQ00 zy_brxxb0.dqbq00%type; ls_JFCZY0 zy_jzb000.JZY000%type; ls_ZFJE00 ZY_BRJFB0.JFJE00%TYPE; ls_GRZHZF ZY_BRJFB0.JFJE00%TYPE; ls_TCJJZF ZY_BRJFB0.JFJE00%TYPE; ls_SBGRZH ZY_BRJFB0.JFJE00%TYPE; ls_SBTCJJ ZY_BRJFB0.JFJE00%TYPE; ls_JJZFE0 ZY_BRJFB0.JFJE00%TYPE; ls_SYBXZF ZY_BRJFB0.JFJE00%TYPE; ls_JZJE00 ZY_BRJFB0.JFJE00%TYPE; ls_JMJE00 ZY_BRJFB0.JFJE00%TYPE; ls_ZYTS00 BM_GHKSTJ.GHKS11%TYPE; --病人的住院天数 ls_COUNT0 NUMBER(12); ls_DQKS00 ZY_BRXXB0.DQKS00%TYPE; ls_SBZFE0 ZY_BRJFB0.JFJE00%TYPE; ls_BJZFE0 ZY_BRJFB0.JFJE00%TYPE; lv_YBBHSB ic_ybbrlb.ybbhsb%type; ls_PXXH00 char(1); ls_AJZJRSX number(5); ls_JZDH00 SF_JZB000.JZDH00%type; ls_YBYL05 SF_JZB000.YBYL05%type; CURSOR CUR_ZY_YBBRJS_BBTJ00 IS select decode(ls_AJZJRSX,1,A.JZDH00,0) JZDH00,1 PXXH00,'城镇居民' YBFLMC,C.YBMC00,C.YBBHSB,A.BRID00,A.JZY000,sum(A.zfje00),sum(A.grzhzf),sum(A.tcjjzf),sum(A.sbgrzh),sum(A.sbtcjj),sum(A.jjzfe0),sum(A.sybxzf),sum(A.jzje00),sum(A.jmje00),sum(A.sbzfe0),sum(A.bjzfe0),sum(A.YBYL05) from SF_JZB000 A,IC_YBBRLB C,BM_YGBM00 E where A.YBLB00 = C.YBLB00 and A.FBBH00 = C.FBBH00 and C.FBBH00 = 3 and A.YBBRLB='C' and c.sfxnh0='0' and A.JZRQ00 >= as_KSRQ00 and A.JZRQ00 <= as_JSRQ00 and A.JZY000 = E.YGBH00 and A.JZRQ00||A.JZSJ00 >= as_KSRQ00||as_KSSJ00 and A.JZRQ00||A.JZSJ00 <= as_JSRQ00||as_JSSJ00 and (E.ZWXM00 = as_CZYXM0 or as_CZYXM0 = '0') and (C.YBMC00 = as_YBZXMC or as_YBZXMC = '0') AND A.CZYKS0 in (select d.bmbh00 from bm_bmbm00 d where (d.yyid00=as_YYID00 OR as_YYID00='0')) and (as_SFJZFP='N' or (as_SFJZFP='Y' and decode(nvl(A.YBSM13,'0'),'Y','1','N','0',nvl(A.YBSM13,'0'))='1')) GROUP BY C.YBMC00,A.BRID00,A.JZY000,C.YBBHSB,decode(ls_AJZJRSX,1,A.JZDH00,0) union all select decode(ls_AJZJRSX,1,A.JZDH00,0) JZDH00,2 PXXH00,'职工居民' YBFLMC,c.YBMC00,c.YBBHSB,a.BRID00,a.JZY000,sum(A.zfje00),sum(A.grzhzf),sum(A.tcjjzf),sum(A.sbgrzh),sum(A.sbtcjj),sum(A.jjzfe0),sum(A.sybxzf),sum(A.jzje00),sum(A.jmje00),sum(A.sbzfe0),sum(A.bjzfe0),sum(A.YBYL05) from SF_JZB000 A,IC_YBBRLB C,BM_YGBM00 E where A.YBLB00 = C.YBLB00 and A.FBBH00 = C.FBBH00 and C.FBBH00 = 3 and A.YBBRLB<>'C' and c.sfxnh0='0' and A.JZRQ00 >= as_KSRQ00 and A.JZRQ00 <= as_JSRQ00 and A.JZY000 = E.YGBH00 and A.JZRQ00||A.JZSJ00 >= as_KSRQ00||as_KSSJ00 and A.JZRQ00||A.JZSJ00 <= as_JSRQ00||as_JSSJ00 and (E.ZWXM00 = as_CZYXM0 or as_CZYXM0 = '0') and (C.YBMC00 = as_YBZXMC or as_YBZXMC = '0') AND A.CZYKS0 in (select d.bmbh00 from bm_bmbm00 d where (d.yyid00=as_YYID00 OR as_YYID00='0')) and (as_SFJZFP='N' or (as_SFJZFP='Y' and decode(nvl(A.YBSM13,'0'),'Y','1','N','0',nvl(A.YBSM13,'0'))='1')) GROUP BY C.YBMC00,A.BRID00,A.JZY000,C.YBBHSB,decode(ls_AJZJRSX,1,A.JZDH00,0) union all select decode(ls_AJZJRSX,1,A.JZDH00,0) JZDH00,3 PXXH00,'工伤生育' YBFLMC,c.YBMC00,c.YBBHSB,a.BRID00,a.JZY000,sum(A.zfje00),sum(A.grzhzf),sum(A.tcjjzf),sum(A.sbgrzh),sum(A.sbtcjj),sum(A.jjzfe0),sum(A.sybxzf),sum(A.jzje00),sum(A.jmje00),sum(A.sbzfe0),sum(A.bjzfe0),sum(A.YBYL05) from SF_JZB000 A,IC_YBBRLB C,BM_YGBM00 E,SF_BRXXB0 F where A.YBLB00 = C.YBLB00 and A.FBBH00 = C.FBBH00 and C.FBBH00 = 3 and A.YBBRLB<>'C' and c.sfxnh0='0' AND A.MZID00=F.GHID00 and A.JZRQ00 >= as_KSRQ00 and A.JZRQ00 <= as_JSRQ00 and A.JZY000 = E.YGBH00 AND F.BXLB00 in ('生育','工伤') and A.JZRQ00||A.JZSJ00 >= as_KSRQ00||as_KSSJ00 and A.JZRQ00||A.JZSJ00 <= as_JSRQ00||as_JSSJ00 and (E.ZWXM00 = as_CZYXM0 or as_CZYXM0 = '0') and (C.YBMC00 = as_YBZXMC or as_YBZXMC = '0') AND A.CZYKS0 in (select d.bmbh00 from bm_bmbm00 d where (d.yyid00=as_YYID00 OR as_YYID00='0')) and (as_SFJZFP='N' or (as_SFJZFP='Y' and decode(nvl(A.YBSM13,'0'),'Y','1','N','0',nvl(A.YBSM13,'0'))='1')) GROUP BY C.YBMC00,A.BRID00,A.JZY000,C.YBBHSB,decode(ls_AJZJRSX,1,A.JZDH00,0) union all select decode(ls_AJZJRSX,1,A.JZDH00,0) JZDH00,4 PXXH00,'农合' YBFLMC,c.YBMC00,c.YBBHSB,a.BRID00,a.JZY000,sum(A.zfje00),sum(A.grzhzf),sum(A.tcjjzf),sum(A.sbgrzh),sum(A.sbtcjj),sum(A.jjzfe0),sum(A.sybxzf),sum(A.jzje00),sum(A.jmje00),sum(A.sbzfe0),sum(A.bjzfe0),sum(A.YBYL05) from SF_JZB000 A,IC_YBBRLB C,BM_YGBM00 E where A.YBLB00 = C.YBLB00 and A.FBBH00 = C.FBBH00 and C.FBBH00 = 3 and c.sfxnh0='1' --AND a.YBLB00='@' and A.JZRQ00 >= as_KSRQ00 and A.JZRQ00 <= as_JSRQ00 and A.JZY000 = E.YGBH00 and A.JZRQ00||A.JZSJ00 >= as_KSRQ00||as_KSSJ00 and A.JZRQ00||A.JZSJ00 <= as_JSRQ00||as_JSSJ00 and (E.ZWXM00 = as_CZYXM0 or as_CZYXM0 = '0') and (C.YBMC00 = as_YBZXMC or as_YBZXMC = '0') AND A.CZYKS0 in (select d.bmbh00 from bm_bmbm00 d where (d.yyid00=as_YYID00 OR as_YYID00='0')) and (as_SFJZFP='N' or (as_SFJZFP='Y' and decode(nvl(A.YBSM13,'0'),'Y','1','N','0',nvl(A.YBSM13,'0'))='1')) GROUP BY C.YBMC00,A.BRID00,A.JZY000,C.YBBHSB,decode(ls_AJZJRSX,1,A.JZDH00,0) union all /*select 6 PXXH00,'独立分中心' YBFLMC,c.YBMC00,c.YBBHSB,a.BRID00,a.JZY000,sum(A.zfje00),sum(A.grzhzf),sum(A.tcjjzf),sum(A.sbgrzh),sum(A.sbtcjj),sum(A.jjzfe0),sum(A.sybxzf),sum(A.jzje00),sum(A.jmje00),sum(A.sbzfe0),sum(A.bjzfe0) from SF_JZB000 A,IC_YBBRLB C,BM_YGBM00 E where A.YBLB00 = C.YBLB00 and A.FBBH00 = C.FBBH00 and C.FBBH00 = 3 and c.sfxnh0='2' and A.JZRQ00 >= as_KSRQ00 and A.JZRQ00 <= as_JSRQ00 and A.JZY000 = E.YGBH00 and A.JZRQ00||A.JZSJ00 >= as_KSRQ00||as_KSSJ00 and A.JZRQ00||A.JZSJ00 <= as_JSRQ00||as_JSSJ00 and (E.ZWXM00 = as_CZYXM0 or as_CZYXM0 = '0') and (C.YBMC00 = as_YBZXMC or as_YBZXMC = '0') AND A.CZYKS0 in (select d.bmbh00 from bm_bmbm00 d where (d.yyid00=as_YYID00 OR as_YYID00='0')) and (as_SFJZFP='N' or (as_SFJZFP='Y' and decode(nvl(A.YBSM13,'0'),'Y','1','N','0',nvl(A.YBSM13,'0'))='1')) GROUP BY C.YBMC00,A.BRID00,A.JZY000,C.YBBHSB */ select decode(ls_AJZJRSX,1,A.JZDH00,0) JZDH00,6 PXXH00,'城镇居民(独立分中心)' YBFLMC,c.YBMC00,c.YBBHSB,a.BRID00,a.JZY000,sum(A.zfje00),sum(A.grzhzf),sum(A.tcjjzf),sum(A.sbgrzh),sum(A.sbtcjj),sum(A.jjzfe0),sum(A.sybxzf),sum(A.jzje00),sum(A.jmje00),sum(A.sbzfe0),sum(A.bjzfe0),sum(A.YBYL05) from SF_JZB000 A,IC_YBBRLB C,BM_YGBM00 E where A.YBLB00 = C.YBLB00 and A.FBBH00 = C.FBBH00 and C.FBBH00 = 3 and c.sfxnh0='2' and a.ybbrlb='C' and A.JZRQ00 >= as_KSRQ00 and A.JZRQ00 <= as_JSRQ00 and A.JZY000 = E.YGBH00 and A.JZRQ00||A.JZSJ00 >= as_KSRQ00||as_KSSJ00 and A.JZRQ00||A.JZSJ00 <= as_JSRQ00||as_JSSJ00 and (E.ZWXM00 = as_CZYXM0 or as_CZYXM0 = '0') and (C.YBMC00 = as_YBZXMC or as_YBZXMC = '0') AND A.CZYKS0 in (select d.bmbh00 from bm_bmbm00 d where (d.yyid00=as_YYID00 OR as_YYID00='0')) and (as_SFJZFP='N' or (as_SFJZFP='Y' and decode(nvl(A.YBSM13,'0'),'Y','1','N','0',nvl(A.YBSM13,'0'))='1')) GROUP BY C.YBMC00,A.BRID00,A.JZY000,C.YBBHSB,decode(ls_AJZJRSX,1,A.JZDH00,0) union all select decode(ls_AJZJRSX,1,A.JZDH00,0) JZDH00,7 PXXH00,'职工居民(独立分中心)' YBFLMC,c.YBMC00,c.YBBHSB,a.BRID00,a.JZY000,sum(A.zfje00),sum(A.grzhzf),sum(A.tcjjzf),sum(A.sbgrzh),sum(A.sbtcjj),sum(A.jjzfe0),sum(A.sybxzf),sum(A.jzje00),sum(A.jmje00),sum(A.sbzfe0),sum(A.bjzfe0),sum(A.YBYL05) from SF_JZB000 A,IC_YBBRLB C,BM_YGBM00 E where A.YBLB00 = C.YBLB00 and A.FBBH00 = C.FBBH00 and C.FBBH00 = 3 and c.sfxnh0='2' and a.ybbrlb<>'C' and A.JZRQ00 >= as_KSRQ00 and A.JZRQ00 <= as_JSRQ00 and A.JZY000 = E.YGBH00 and A.JZRQ00||A.JZSJ00 >= as_KSRQ00||as_KSSJ00 and A.JZRQ00||A.JZSJ00 <= as_JSRQ00||as_JSSJ00 and (E.ZWXM00 = as_CZYXM0 or as_CZYXM0 = '0') and (C.YBMC00 = as_YBZXMC or as_YBZXMC = '0') AND A.CZYKS0 in (select d.bmbh00 from bm_bmbm00 d where (d.yyid00=as_YYID00 OR as_YYID00='0')) and (as_SFJZFP='N' or (as_SFJZFP='Y' and decode(nvl(A.YBSM13,'0'),'Y','1','N','0',nvl(A.YBSM13,'0'))='1')) GROUP BY C.YBMC00,A.BRID00,A.JZY000,C.YBBHSB,decode(ls_AJZJRSX,1,A.JZDH00,0) union all select decode(ls_AJZJRSX,1,A.JZDH00,0) JZDH00,5 PXXH00,'异地医保' YBFLMC,c.YBMC00,c.YBBHSB,a.BRID00,a.JZY000,sum(A.zfje00),sum(A.grzhzf),sum(A.tcjjzf),sum(A.sbgrzh),sum(A.sbtcjj),sum(A.jjzfe0),sum(A.sybxzf),sum(A.jzje00),sum(A.jmje00),sum(A.sbzfe0),sum(A.bjzfe0),sum(A.YBYL05) from SF_JZB000 A,IC_YBBRLB C,BM_YGBM00 E where A.YBLB00 = C.YBLB00 and A.FBBH00 = C.FBBH00 and C.FBBH00 = 3 and c.sfxnh0='3' and A.JZRQ00 >= as_KSRQ00 and A.JZRQ00 <= as_JSRQ00 and A.JZY000 = E.YGBH00 and A.JZRQ00||A.JZSJ00 >= as_KSRQ00||as_KSSJ00 and A.JZRQ00||A.JZSJ00 <= as_JSRQ00||as_JSSJ00 and (E.ZWXM00 = as_CZYXM0 or as_CZYXM0 = '0') and (C.YBMC00 = as_YBZXMC or as_YBZXMC = '0') AND A.CZYKS0 in (select d.bmbh00 from bm_bmbm00 d where (d.yyid00=as_YYID00 OR as_YYID00='0')) and (as_SFJZFP='N' or (as_SFJZFP='Y' and decode(nvl(A.YBSM13,'0'),'Y','1','N','0',nvl(A.YBSM13,'0'))='1')) GROUP BY C.YBMC00,A.BRID00,A.JZY000,C.YBBHSB,decode(ls_AJZJRSX,1,A.JZDH00,0) ; begin ls_AJZJRSX :=0; select nvl(max(to_number(VALUE0)),0) into ls_AJZJRSX from XT_XTCS00 where NAME00='SF_YBBRBBAJZJRSX'; Select SQ_BM_GHKSTJ_ID0000.nextval into ad_ID0000 from dual; OPEN CUR_ZY_YBBRJS_BBTJ00; LOOP FETCH CUR_ZY_YBBRJS_BBTJ00 INTO ls_JZDH00,ls_PXXH00,lv_YBFLMC,ls_YBMC00,lv_YBBHSB,ls_BRID00,ls_JFCZY0,ls_ZFJE00,LS_GRZHZF,LS_TCJJZF,LS_SBGRZH,LS_SBTCJJ,ls_JJZFE0,ls_SYBXZF,LS_JZJE00,ls_JMJE00,ls_SBZFE0,ls_BJZFE0,ls_YBYL05; EXIT WHEN CUR_ZY_YBBRJS_BBTJ00%NOTFOUND; --select to_date(NVL(cyrq00,to_char(sysdate,'YYYYMMDD')),'YYYYMMDD')-to_date(ryrq00,'YYYYMMDD') into ls_ZYTS00 from zy_brxxb0 where zyid00 = ls_ZYID00; if NVL(ls_ZFJE00,0) <>0 then --自付金额 Update BM_GHKSTJ Set GHKS03 = NVL(GHKS03,0)+ls_ZFJE00 ,XMHJ00=NVL(XMHJ00,0)+ls_ZFJE00 ,GHKS11 = ls_ZYTS00 where DJH000 = ls_JZDH00 and GHRQ00 = ls_YBMC00 and BEIZHU=lv_YBFLMC and ID0000=ad_ID0000 and GHKS01 = ls_BRID00 and GHKS02 = ls_JFCZY0 and GHKS10 = ls_DQBQ00 and GHKS12=ls_DQKS00; if SQL%NOTFOUND then Insert into BM_GHKSTJ(DJH000,GHRQ00,GHKS01,GHKS02,GHKS03,ID0000,XMHJ00,GHKS10,GHKS11,GHKS12,BEIZHU,GHKS99) values(ls_JZDH00,ls_YBMC00,ls_BRID00,ls_JFCZY0,ls_ZFJE00,ad_ID0000,ls_ZFJE00,ls_DQBQ00,ls_ZYTS00,ls_DQKS00,lv_YBFLMC,ls_PXXH00); end if; end if; if NVL(ls_GRZHZF,0) <> 0 then --医保个人帐户 Update BM_GHKSTJ Set GHKS04 = NVL(GHKS04,0)+ls_GRZHZF ,XMHJ00=NVL(XMHJ00,0)+ls_GRZHZF ,GHKS11 = ls_ZYTS00 where DJH000 = ls_JZDH00 and GHRQ00 = ls_YBMC00 and BEIZHU=lv_YBFLMC and ID0000=ad_ID0000 and GHKS01 = ls_BRID00 and GHKS02 = ls_JFCZY0 and GHKS10 = ls_DQBQ00 and GHKS12=ls_DQKS00; if SQL%NOTFOUND then Insert into BM_GHKSTJ(DJH000,GHRQ00,GHKS01,GHKS02,GHKS04,ID0000,XMHJ00,GHKS10,GHKS11,GHKS12,BEIZHU,GHKS99) values(ls_JZDH00,ls_YBMC00,ls_BRID00,ls_JFCZY0,ls_GRZHZF,ad_ID0000,ls_GRZHZF,ls_DQBQ00,ls_ZYTS00,ls_DQKS00,lv_YBFLMC,ls_PXXH00); end if; end if; if NVL(LS_TCJJZF,0)<>0 then --医保统筹基金 Update BM_GHKSTJ Set GHKS05 = NVL(GHKS05,0)+LS_TCJJZF ,XMHJ00=NVL(XMHJ00,0)+LS_TCJJZF ,GHKS11 = ls_ZYTS00 where DJH000 = ls_JZDH00 and GHRQ00 = ls_YBMC00 and BEIZHU=lv_YBFLMC and ID0000=ad_ID0000 and GHKS01 = ls_BRID00 and GHKS02 = ls_JFCZY0 and GHKS10 = ls_DQBQ00 and GHKS12=ls_DQKS00; if SQL%NOTFOUND then Insert into BM_GHKSTJ(DJH000,GHRQ00,GHKS01,GHKS02,GHKS05,ID0000,XMHJ00,GHKS10,GHKS11,GHKS12,BEIZHU,GHKS99) values(ls_JZDH00,ls_YBMC00,ls_BRID00,ls_JFCZY0,LS_TCJJZF,ad_ID0000,LS_TCJJZF,ls_DQBQ00,ls_ZYTS00,ls_DQKS00,lv_YBFLMC,ls_PXXH00); end if; end if; if NVL(ls_YBYL05,0)<>0 then --医院支付，医院支付不归入医保金额中 Update BM_GHKSTJ Set GHKS17 = NVL(GHKS17,0)+ls_YBYL05 ,XMHJ00=NVL(XMHJ00,0)+ls_YBYL05,GHKS11 = ls_ZYTS00 where DJH000 = ls_JZDH00 and GHRQ00 = ls_YBMC00 and BEIZHU=lv_YBFLMC and ID0000=ad_ID0000 and GHKS01 = ls_BRID00 and GHKS02 = ls_JFCZY0 and GHKS10 = ls_DQBQ00 and GHKS12=ls_DQKS00; if SQL%NOTFOUND then Insert into BM_GHKSTJ(DJH000,GHRQ00,GHKS01,GHKS02,GHKS17,ID0000,XMHJ00,GHKS10,GHKS11,GHKS12,BEIZHU,GHKS99) values(ls_JZDH00,ls_YBMC00,ls_BRID00,ls_JFCZY0,ls_YBYL05,ad_ID0000,ls_YBYL05,ls_DQBQ00,ls_ZYTS00,ls_DQKS00,lv_YBFLMC,ls_PXXH00); end if; end if; if NVL(ls_SBGRZH,0)<>0 then --商保个人帐户 Update BM_GHKSTJ Set GHKS06 = NVL(GHKS06,0)+ls_SBGRZH ,XMHJ00=NVL(XMHJ00,0)+decode(sign(Instrb(','||trim(lv_YBBHSB)||',',',14,')),1,0,ls_SBGRZH),GHKS11 = ls_ZYTS00 --DECODE(lv_YBBHSB,'Y',0,ls_SBGRZH) where DJH000 = ls_JZDH00 and GHRQ00 = ls_YBMC00 and BEIZHU=lv_YBFLMC and ID0000=ad_ID0000 and GHKS01 = ls_BRID00 and GHKS02 = ls_JFCZY0 and GHKS10 = ls_DQBQ00 and GHKS12=ls_DQKS00; if SQL%NOTFOUND then Insert into BM_GHKSTJ(DJH000,GHRQ00,GHKS01,GHKS02,GHKS06,ID0000,XMHJ00,GHKS10,GHKS11,GHKS12,BEIZHU,GHKS99) values(ls_JZDH00,ls_YBMC00,ls_BRID00,ls_JFCZY0,ls_SBGRZH,ad_ID0000,decode(sign(Instrb(','||trim(lv_YBBHSB)||',',',14,')),1,0,ls_SBGRZH),ls_DQBQ00,ls_ZYTS00,ls_DQKS00,lv_YBFLMC,ls_PXXH00); end if; end if; if NVL(ls_SBTCJJ,0) <>0 then --商保统筹基金 Update BM_GHKSTJ Set GHKS07 = NVL(GHKS07,0)+ls_SBTCJJ ,XMHJ00=NVL(XMHJ00,0) +decode(sign(Instrb(','||trim(lv_YBBHSB)||',',',14,')),1,0,ls_SBTCJJ),GHKS11 = ls_ZYTS00 --decode(lv_YBBHSB,'Y',0,ls_SBTCJJ) where DJH000 = ls_JZDH00 and GHRQ00 = ls_YBMC00 and BEIZHU=lv_YBFLMC and ID0000=ad_ID0000 and GHKS01 = ls_BRID00 and GHKS02 = ls_JFCZY0 and GHKS10 = ls_DQBQ00 and GHKS12=ls_DQKS00; if SQL%NOTFOUND then Insert into BM_GHKSTJ(DJH000,GHRQ00,GHKS01,GHKS02,GHKS07,ID0000,XMHJ00,GHKS10,GHKS11,GHKS12,BEIZHU,GHKS99) values(ls_JZDH00,ls_YBMC00,ls_BRID00,ls_JFCZY0,ls_SBTCJJ,ad_ID0000,decode(sign(Instrb(','||trim(lv_YBBHSB)||',',',14,')),1,0,ls_SBTCJJ),ls_DQBQ00,ls_ZYTS00,ls_DQKS00,lv_YBFLMC,ls_PXXH00); end if; end if; if (NVL(ls_JZJE00,0)<>0)AND(NVL(LS_GRZHZF,0)=0) AND(NVL(LS_TCJJZF,0)=0) then --公费记账金额 Update BM_GHKSTJ Set GHKS08 = NVL(GHKS08,0)+ls_JZJE00 ,XMHJ00=NVL(XMHJ00,0)+ls_JZJE00 ,GHKS11 = ls_ZYTS00 where DJH000 = ls_JZDH00 and GHRQ00 = ls_YBMC00 and BEIZHU=lv_YBFLMC and ID0000=ad_ID0000 and GHKS01 = ls_BRID00 and GHKS02 = ls_JFCZY0 and GHKS10 = ls_DQBQ00 and GHKS12=ls_DQKS00; if SQL%NOTFOUND then Insert into BM_GHKSTJ(DJH000,GHRQ00,GHKS01,GHKS02,GHKS08,ID0000,XMHJ00,GHKS10,GHKS11,GHKS12,BEIZHU,GHKS99) values(ls_JZDH00,ls_YBMC00,ls_BRID00,ls_JFCZY0,ls_JZJE00,ad_ID0000,ls_JZJE00,ls_DQBQ00,ls_ZYTS00,ls_DQKS00,lv_YBFLMC,ls_PXXH00); end if; end if; if (NVL(ls_JMJE00,0)<>0)AND(NVL(LS_GRZHZF,0)=0) AND(NVL(LS_TCJJZF,0)=0) then --减免金额 Update BM_GHKSTJ Set GHKS09 = NVL(GHKS09,0)+ls_JMJE00 ,XMHJ00=NVL(XMHJ00,0)+ls_JMJE00 ,GHKS11 = ls_ZYTS00 where DJH000 = ls_JZDH00 and GHRQ00 = ls_YBMC00 and BEIZHU=lv_YBFLMC and ID0000=ad_ID0000 and GHKS01 = ls_BRID00 and GHKS02 = ls_JFCZY0 and GHKS10 = ls_DQBQ00 and GHKS12=ls_DQKS00; if SQL%NOTFOUND then Insert into BM_GHKSTJ(DJH000,GHRQ00,GHKS01,GHKS02,GHKS09,ID0000,XMHJ00,GHKS10,GHKS11,GHKS12,BEIZHU,GHKS99) values(ls_JZDH00,ls_YBMC00,ls_BRID00,ls_JFCZY0,ls_JMJE00,ad_ID0000,ls_JMJE00,ls_DQBQ00,ls_ZYTS00,ls_DQKS00,lv_YBFLMC,ls_PXXH00); end if; end if; if NVL(ls_JJZFE0,0) <>0 then --保健基金支付 Update BM_GHKSTJ Set GHKS13 = NVL(GHKS13,0)+ls_JJZFE0 ,XMHJ00=NVL(XMHJ00,0)+ls_JJZFE0 ,GHKS11 = ls_ZYTS00 where DJH000 = ls_JZDH00 and GHRQ00 = ls_YBMC00 and BEIZHU=lv_YBFLMC and ID0000=ad_ID0000 and GHKS01 = ls_BRID00 and GHKS02 = ls_JFCZY0 and GHKS10 = ls_DQBQ00 and GHKS12=ls_DQKS00; if SQL%NOTFOUND then Insert into BM_GHKSTJ(DJH000,GHRQ00,GHKS01,GHKS02,GHKS13,ID0000,XMHJ00,GHKS10,GHKS11,GHKS12,BEIZHU,GHKS99) values(ls_JZDH00,ls_YBMC00,ls_BRID00,ls_JFCZY0,ls_JJZFE0,ad_ID0000,ls_JJZFE0,ls_DQBQ00,ls_ZYTS00,ls_DQKS00,lv_YBFLMC,ls_PXXH00); end if; end if; if NVL(ls_SYBXZF,0) <>0 then --商业保险支付 Update BM_GHKSTJ Set GHKS14 = NVL(GHKS14,0)+ls_SYBXZF , XMHJ00=NVL(XMHJ00,0)+decode(sign(Instrb(','||trim(lv_YBBHSB)||',',',14,')),1,0,ls_SYBXZF), GHKS11 = ls_ZYTS00 where DJH000 = ls_JZDH00 and GHRQ00 = ls_YBMC00 and BEIZHU=lv_YBFLMC and ID0000=ad_ID0000 and GHKS01 = ls_BRID00 and GHKS02 = ls_JFCZY0 and GHKS10 = ls_DQBQ00 and GHKS12=ls_DQKS00; if SQL%NOTFOUND then Insert into BM_GHKSTJ(DJH000,GHRQ00,GHKS01,GHKS02,GHKS14,ID0000,XMHJ00,GHKS10,GHKS11,GHKS12,BEIZHU,GHKS99) values(ls_JZDH00,ls_YBMC00,ls_BRID00,ls_JFCZY0,ls_SYBXZF,ad_ID0000,decode(sign(Instrb(','||trim(lv_YBBHSB)||',',',14,')),1,0,ls_SYBXZF),ls_DQBQ00,ls_ZYTS00,ls_DQKS00,lv_YBFLMC,ls_PXXH00); end if; end if; if NVL(ls_SBZFE0,0) <>0 then --商保基金支付（省属三家市属九家医疗机构） Update BM_GHKSTJ Set GHKS15 = NVL(GHKS15,0)+ls_SBZFE0 ,GHKS11 = ls_ZYTS00 where DJH000 = ls_JZDH00 and GHRQ00 = ls_YBMC00 and BEIZHU=lv_YBFLMC and ID0000=ad_ID0000 and GHKS01 = ls_BRID00 and GHKS02 = ls_JFCZY0 and GHKS10 = ls_DQBQ00 and GHKS12=ls_DQKS00; if SQL%NOTFOUND then Insert into BM_GHKSTJ(DJH000,GHRQ00,GHKS01,GHKS02,GHKS15,ID0000,XMHJ00,GHKS10,GHKS11,GHKS12,BEIZHU,GHKS99) values(ls_JZDH00,ls_YBMC00,ls_BRID00,ls_JFCZY0,ls_SBZFE0,ad_ID0000,ls_SBZFE0,ls_DQBQ00,ls_ZYTS00,ls_DQKS00,lv_YBFLMC,ls_PXXH00); end if; end if; if NVL(ls_BJZFE0,0) <>0 then --保健基金支付（省属三家市属九家医疗机构） Update BM_GHKSTJ Set GHKS16 = NVL(GHKS16,0)+ls_BJZFE0 ,GHKS11 = ls_ZYTS00 where DJH000 = ls_JZDH00 and GHRQ00 = ls_YBMC00 and BEIZHU=lv_YBFLMC and ID0000=ad_ID0000 and GHKS01 = ls_BRID00 and GHKS02 = ls_JFCZY0 and GHKS10 = ls_DQBQ00 and GHKS12=ls_DQKS00; if SQL%NOTFOUND then Insert into BM_GHKSTJ(DJH000,GHRQ00,GHKS01,GHKS02,GHKS16,ID0000,XMHJ00,GHKS10,GHKS11,GHKS12,BEIZHU,GHKS99) values(ls_JZDH00,ls_YBMC00,ls_BRID00,ls_JFCZY0,ls_BJZFE0,ad_ID0000,ls_BJZFE0,ls_DQBQ00,ls_ZYTS00,ls_DQKS00,lv_YBFLMC,ls_PXXH00); end if; end if; END LOOP; CLOSE CUR_ZY_YBBRJS_BBTJ00; --总金额 select count(distinct GHKS01) into ls_COUNT0 from BM_GHKSTJ where ID0000=ad_ID0000; Insert into BM_GHKSTJ(ID0000,GHRQ00,GHKS01,GHKS02,GHKS03,GHKS04,GHKS05,GHKS06,GHKS07,GHKS08,XMHJ00,GHKS11,GHKS13,GHKS14,GHKS15,GHKS16,GHKS17,GHKS99) select ad_ID0000,' 合计(共'||to_char(ls_COUNT0)||'人)',0,0,sum(GHKS03),sum(GHKS04),sum(GHKS05),sum(GHKS06),sum(GHKS07), sum(GHKS08),sum(XMHJ00),sum(GHKS11),sum(GHKS13),sum(GHKS14),SUM(GHKS15),SUM(GHKS16),SUM(GHKS17),12 from BM_GHKSTJ where ID0000 = ad_ID0000 ; --大中心金额 select count(distinct GHKS01) into ls_COUNT0 from BM_GHKSTJ where ID0000=ad_ID0000 AND GHKS99 in (1,2,3); Insert into BM_GHKSTJ(ID0000,GHRQ00,GHKS01,GHKS02,GHKS03,GHKS04,GHKS05,GHKS06,GHKS07,GHKS08,XMHJ00,GHKS11,GHKS13,GHKS14,GHKS15,GHKS16,GHKS17,GHKS99) select ad_ID0000,' 合计(共'||to_char(ls_COUNT0)||'人)',-4,-4,sum(GHKS03),sum(GHKS04),sum(GHKS05),sum(GHKS06),sum(GHKS07), sum(GHKS08),sum(XMHJ00),sum(GHKS11),sum(GHKS13),sum(GHKS14),SUM(GHKS15),SUM(GHKS16),SUM(GHKS17),10 from BM_GHKSTJ where ID0000 = ad_ID0000 and GHKS99 in (1,2,3); --农合金额 select count(distinct GHKS01) into ls_COUNT0 from BM_GHKSTJ where ID0000=ad_ID0000 AND BEIZHU='农合'; Insert into BM_GHKSTJ(ID0000,GHRQ00,GHKS01,GHKS02,GHKS03,GHKS04,GHKS05,GHKS06,GHKS07,GHKS08,XMHJ00,GHKS11,GHKS13,GHKS14,GHKS15,GHKS16,GHKS17,GHKS99) select ad_ID0000,' 合计(共'||to_char(ls_COUNT0)||'人)',-5,-5,sum(GHKS03),sum(GHKS04),sum(GHKS05),sum(GHKS06),sum(GHKS07), sum(GHKS08),sum(XMHJ00),sum(GHKS11),sum(GHKS13),sum(GHKS14),SUM(GHKS15),SUM(GHKS16),SUM(GHKS17),4 from BM_GHKSTJ where ID0000 = ad_ID0000 and BEIZHU='农合'; --异地医保 select count(distinct GHKS01) into ls_COUNT0 from BM_GHKSTJ where ID0000=ad_ID0000 AND BEIZHU='异地医保'; Insert into BM_GHKSTJ(ID0000,GHRQ00,GHKS01,GHKS02,GHKS03,GHKS04,GHKS05,GHKS06,GHKS07,GHKS08,XMHJ00,GHKS11,GHKS13,GHKS14,GHKS15,GHKS16,GHKS17,GHKS99) select ad_ID0000,' 合计(共'||to_char(ls_COUNT0)||'人)',-6,-6,sum(GHKS03),sum(GHKS04),sum(GHKS05),sum(GHKS06),sum(GHKS07), sum(GHKS08),sum(XMHJ00),sum(GHKS11),sum(GHKS13),sum(GHKS14),SUM(GHKS15),SUM(GHKS16),SUM(GHKS17),5 from BM_GHKSTJ where ID0000 = ad_ID0000 and BEIZHU='异地医保'; --独立分中心 /*select count(distinct GHKS01) into ls_COUNT0 from BM_GHKSTJ where ID0000=ad_ID0000 AND BEIZHU='独立分中心'; Insert into BM_GHKSTJ(ID0000,GHRQ00,GHKS01,GHKS02,GHKS03,GHKS04,GHKS05,GHKS06,GHKS07,GHKS08,XMHJ00,GHKS11,GHKS13,GHKS14,GHKS15,GHKS16,GHKS99) select ad_ID0000,' 合计(共'||to_char(ls_COUNT0)||'人)',-7,-7,sum(GHKS03),sum(GHKS04),sum(GHKS05),sum(GHKS06),sum(GHKS07), sum(GHKS08),sum(XMHJ00),sum(GHKS11),sum(GHKS13),sum(GHKS14),SUM(GHKS15),SUM(GHKS16),6 from BM_GHKSTJ where ID0000 = ad_ID0000 and BEIZHU='独立分中心'; */ select count(distinct GHKS01) into ls_COUNT0 from BM_GHKSTJ where ID0000=ad_ID0000 AND GHKS99 in (6,7); Insert into BM_GHKSTJ(ID0000,GHRQ00,GHKS01,GHKS02,GHKS03,GHKS04,GHKS05,GHKS06,GHKS07,GHKS08,XMHJ00,GHKS11,GHKS13,GHKS14,GHKS15,GHKS16,GHKS17,GHKS99) select ad_ID0000,' 合计(共'||to_char(ls_COUNT0)||'人)',-9,-9,sum(GHKS03),sum(GHKS04),sum(GHKS05),sum(GHKS06),sum(GHKS07), sum(GHKS08),sum(XMHJ00),sum(GHKS11),sum(GHKS13),sum(GHKS14),SUM(GHKS15),SUM(GHKS16),SUM(GHKS17),8 from BM_GHKSTJ where ID0000 = ad_ID0000 and GHKS99 in (6,7); select count(distinct GHKS01) into ls_COUNT0 from BM_GHKSTJ where ID0000=ad_ID0000 AND BEIZHU='城镇居民(独立分中心)'; if ls_COUNT0>0 then Insert into BM_GHKSTJ(ID0000,GHRQ00,GHKS01,GHKS02,GHKS03,GHKS04,GHKS05,GHKS06,GHKS07,GHKS08,XMHJ00,GHKS11,GHKS13,GHKS14,GHKS15,GHKS16,GHKS17,GHKS99) select ad_ID0000,' 小计(共'||to_char(ls_COUNT0)||'人)',-7,-7,sum(GHKS03),sum(GHKS04),sum(GHKS05),sum(GHKS06),sum(GHKS07), sum(GHKS08),sum(XMHJ00),sum(GHKS11),sum(GHKS13),sum(GHKS14),SUM(GHKS15),SUM(GHKS16),SUM(GHKS17),6 from BM_GHKSTJ where ID0000 = ad_ID0000 and BEIZHU='城镇居民(独立分中心)'; end if; select count(distinct GHKS01) into ls_COUNT0 from BM_GHKSTJ where ID0000=ad_ID0000 AND BEIZHU='职工居民(独立分中心)'; if ls_COUNT0>0 then Insert into BM_GHKSTJ(ID0000,GHRQ00,GHKS01,GHKS02,GHKS03,GHKS04,GHKS05,GHKS06,GHKS07,GHKS08,XMHJ00,GHKS11,GHKS13,GHKS14,GHKS15,GHKS16,GHKS17,GHKS99) select ad_ID0000,' 小计(共'||to_char(ls_COUNT0)||'人)',-8,-8,sum(GHKS03),sum(GHKS04),sum(GHKS05),sum(GHKS06),sum(GHKS07), sum(GHKS08),sum(XMHJ00),sum(GHKS11),sum(GHKS13),sum(GHKS14),SUM(GHKS15),SUM(GHKS16),SUM(GHKS17),7 from BM_GHKSTJ where ID0000 = ad_ID0000 and BEIZHU='职工居民(独立分中心)'; end if; --独立分中心 end select count(distinct GHKS01) into ls_COUNT0 from BM_GHKSTJ where ID0000=ad_ID0000 AND GHKS99=1; if ls_COUNT0>0 then --城镇居民 Insert into BM_GHKSTJ(ID0000,GHRQ00,GHKS01,GHKS02,GHKS03,GHKS04,GHKS05,GHKS06,GHKS07,GHKS08,XMHJ00,GHKS11,GHKS13,GHKS14,GHKS15,GHKS16,GHKS17,GHKS99) select ad_ID0000,' 小计(共'||to_char(ls_COUNT0)||'人)',-1,-1,sum(GHKS03),sum(GHKS04),sum(GHKS05),sum(GHKS06),sum(GHKS07), sum(GHKS08),sum(XMHJ00),sum(GHKS11),sum(GHKS13),sum(GHKS14),SUM(GHKS15),SUM(GHKS16),SUM(GHKS17),1 from BM_GHKSTJ where ID0000 = ad_ID0000 and BEIZHU='城镇居民' ; end if; select count(distinct GHKS01) into ls_COUNT0 from BM_GHKSTJ where ID0000=ad_ID0000 AND GHKS99=2; if ls_COUNT0>0 then --职工居民 Insert into BM_GHKSTJ(ID0000,GHRQ00,GHKS01,GHKS02,GHKS03,GHKS04,GHKS05,GHKS06,GHKS07,GHKS08,XMHJ00,GHKS11,GHKS13,GHKS14,GHKS15,GHKS16,GHKS17,GHKS99) select ad_ID0000,' 小计(共'||to_char(ls_COUNT0)||'人)',-2,-2,sum(GHKS03),sum(GHKS04),sum(GHKS05),sum(GHKS06),sum(GHKS07), sum(GHKS08),sum(XMHJ00),sum(GHKS11),sum(GHKS13),sum(GHKS14),SUM(GHKS15),SUM(GHKS16),SUM(GHKS17),2 from BM_GHKSTJ where ID0000 = ad_ID0000 and BEIZHU='职工居民' ; end if; select count(distinct GHKS01) into ls_COUNT0 from BM_GHKSTJ where ID0000=ad_ID0000 AND GHKS99=3; if ls_COUNT0>0 then --工伤生育 Insert into BM_GHKSTJ(ID0000,GHRQ00,GHKS01,GHKS02,GHKS03,GHKS04,GHKS05,GHKS06,GHKS07,GHKS08,XMHJ00,GHKS11,GHKS13,GHKS14,GHKS15,GHKS16,GHKS17,GHKS99) select ad_ID0000,' 小计(共'||to_char(ls_COUNT0)||'人)',-3,-3,sum(GHKS03),sum(GHKS04),sum(GHKS05),sum(GHKS06),sum(GHKS07), sum(GHKS08),sum(XMHJ00),sum(GHKS11),sum(GHKS13),sum(GHKS14),SUM(GHKS15),SUM(GHKS16),SUM(GHKS17),3 from BM_GHKSTJ where ID0000 = ad_ID0000 and BEIZHU='工伤生育' ; end if; EXCEPTION WHEN OTHERS THEN as_YHMSG0:='出错原因不详,请记录此信息并和系统管理员联系！'; as_SYSMSG:=substr(SQLERRM||'执行存储过程错误:SP_ZY_YBBRJS_BB0000('||as_KSRQ00||','||as_JSRQ00||','||as_CZYXM0||'ad_ID0000,as_YHMSG0,as_SYSMSG',1,150); ROLLBACK; END;