create or replace procedure SP_ZY_KSSRHZ_RBB001 ( as_KSRQ00 in char, --开始日期 as_KSSJ00 in char, --开始时间 as_JSRQ00 in char, --结束日期 as_JSSJ00 in char, --结束时间 as_TJLB00 in char, --统计核算类别 '0' 发票项目 '1' 一级核算项目统计 '2'二级核算项目统计 as_KSLB00 in char, --统计类别 '0' 按开单科室 '1'按执行科室 as_KSBH00 in number, --开单科室编号(-1为全部住院开单执行科室) as_YBMC00 in char, --医保名称 as_JZLB00 in char, --记账类别'0'-全部,'1'-自付,'2'-记账,'3'-减免 as_YYID00 in char, --医院ID >0 各分院 =0全部 ad_ID0000 out number, --统计报表对应ID as_YHMSG0 out varchar, --存储过程提示的错误信息 as_SYSMSG out varchar, --系统提示的错误信息 ad_YSZID0 in number default -1 --统计医生组 ) as -- MODIFICATION HISTORY -- Person Date Comments -- xzw 2008.02.22 根据ZY_fymx00中的kdks00属于哪个分院进行统计; -- liuj 2012.03.02 增加参数使按编号输出sfxmXX值 forZYSF-20120222-001 --zhangyc 2012.05.10 修正参数ZY_KSSRTJBBSFXSWFYFPXM=Y时二级核算统计报错 by ZYSF-20120508-001 -- linzy 2014.06.11 增加医生组入参 ad_YSZID0 by ZYSF-20140609-003 -- jlg 2016.05.26 把ZY_BRFY00去掉 ZYSF9-20160526-001 -- ------- -------------- ------------------------------------------------------------------------------------ ls_KSBH00 ZY_FYMX00.KDBQ00%type; --科室编号 ls_bmbh00 bm_yysftj.bmbh00%type; ls_HJJE00 ZY_FYMX00.HJJE00%TYPE; ls_HSXMBH BM_YYHSXM.BH0000%TYPE; VCounter number(12); ls_BHSX00 char(2); ls_BHSX01 number(12); ls_cs0001 char(1); cursor CUR_ZY_FYMX00_FPXM00 IS select F.BH0000,decode(AS_KSLB00,'0',KDKS00,'1',ZXKS00,'2',nvl(A.YSZID0,0)) KSBH00,decode(as_jzlb00,'0',sum(A.HJJE00),'1',sum(A.HJJE00-a.jzje00-a.gfje00),'2',sum(a.jzje00),sum(a.gfje00)) from ZY_FYMX00 A,BM_FPXM00 F,BM_BRXXB0 D,IC_YBBRLB G,BM_BMBM00 H where A.KDKS00 = H.BMBH00 and A.ZYFPID = F.FPXMID and D.BRID00 = A.BRID00 and D.FBBH00 = G.FBBH00 and D.YBLB00 = G.YBLB00 and A.CZRQ00 >= as_KSRQ00 and A.CZRQ00 <= as_JSRQ00 and A.CZRQ00||A.CZSJ00 >= as_KSRQ00||as_KSSJ00 and A.CZRQ00||A.CZSJ00 <= as_JSRQ00||as_JSSJ00 and ((AS_KSLB00='0' and KDKS00=as_KSBH00) or (AS_KSLB00='1' and ZXKS00=as_KSBH00) or as_KSBH00 = '-1') and (A.YSZID0=ad_YSZID0 or ad_YSZID0=-1) and (G.YBMC00=as_YBMC00 or as_YBMC00 = '所有病人') and (H.YYID00 = as_YYID00 or as_YYID00 = '0') group by F.BH0000,decode(AS_KSLB00,'0',KDKS00,'1',ZXKS00,'2',nvl(A.YSZID0,0)) order by F.BH0000 ; cursor CUR_ZY_FYMX00_YJHSXM IS select F.YJHSBH,decode(AS_KSLB00,'0',KDKS00,'1',ZXKS00,'2',nvl(A.YSZID0,0)) KSBH00,decode(as_jzlb00,'0',sum(A.HJJE00),'1',sum(A.HJJE00-a.jzje00-a.gfje00),'2',sum(a.jzje00),sum(a.gfje00)) from ZY_FYMX00 A,VW_BM_YJHSXM F,BM_BRXXB0 D,IC_YBBRLB G,BM_BMBM00 H where A.KDKS00 = H.BMBH00 and A.HSXMID = F.HSXMID and D.BRID00 = A.BRID00 and D.FBBH00 = G.FBBH00 and D.YBLB00 = G.YBLB00 and A.CZRQ00 >= as_KSRQ00 and A.CZRQ00 <= as_JSRQ00 and A.CZRQ00||A.CZSJ00 >= as_KSRQ00||as_KSSJ00 and A.CZRQ00||A.CZSJ00 <= as_JSRQ00||as_JSSJ00 and ((AS_KSLB00='0' and KDKS00=as_KSBH00) or (AS_KSLB00='1' and ZXKS00=as_KSBH00) or as_KSBH00 = '-1') and (G.YBMC00=as_YBMC00 or as_YBMC00 = '所有病人') and (A.YSZID0=ad_YSZID0 or ad_YSZID0=-1) and (H.YYID00 = as_YYID00 or as_YYID00 = '0') group by F.YJHSBH,decode(AS_KSLB00,'0',KDKS00,'1',ZXKS00,'2',nvl(A.YSZID0,0)) order by F.YJHSBH ; cursor CUR_ZY_FYMX00_EJHSXM IS select F.EJHSBH,decode(AS_KSLB00,'0',KDKS00,'1',ZXKS00,'2',nvl(A.YSZID0,0)) KSBH00,decode(as_jzlb00,'0',sum(A.HJJE00),'1',sum(A.HJJE00-a.jzje00-a.gfje00),'2',sum(a.jzje00),sum(a.gfje00)) from ZY_FYMX00 A,VW_BM_EJHSXM F,BM_BRXXB0 D,IC_YBBRLB G,BM_BMBM00 H where A.KDKS00 = H.BMBH00 and A.HSXMID = F.HSXMID and D.BRID00 = A.BRID00 and D.FBBH00 = G.FBBH00 and D.YBLB00 = G.YBLB00 and A.CZRQ00 >= as_KSRQ00 and A.CZRQ00 <= as_JSRQ00 and A.CZRQ00||A.CZSJ00 >= as_KSRQ00||as_KSSJ00 and A.CZRQ00||A.CZSJ00 <= as_JSRQ00||as_JSSJ00 and ((AS_KSLB00='0' and KDKS00=as_KSBH00) or (AS_KSLB00='1' and ZXKS00=as_KSBH00) or as_KSBH00 = '-1') and (G.YBMC00=as_YBMC00 or as_YBMC00 = '所有病人') and (A.YSZID0=ad_YSZID0 or ad_YSZID0=-1) and (H.YYID00 = as_YYID00 or as_YYID00 = '0') group by F.EJHSBH,decode(AS_KSLB00,'0',KDKS00,'1',ZXKS00,'2',nvl(A.YSZID0,0)) order by F.EJHSBH; cursor CUR_BM_YYSFTJ is select bmbh00 from BM_YYSFTJ where ID0000 = ad_ID0000; begin ls_BHSX01:=0; ls_BHSX00:='00'; select count(1) into VCounter from XT_XTCS00 where name00='ZY_KSSRTJBBSFXSWFYFPXM' and value0='Y'; if VCounter>0 then ls_cs0001:='Y'; else ls_cs0001:='N'; end if; select SQ_BM_GHKSTJ_ID0000.nextval into AD_ID0000 from dual; if ls_cs0001='Y' then if AS_TJLB00='0' then insert into BM_GHKSTJ_KSDY00(ID0000,BH0000,GHKSBH,GHKSMC) select AD_ID0000,0,rownum,BH0000 from ( select distinct BH0000 from BM_FPXM00 order by BH0000 ); elsif AS_TJLB00='1' then insert into BM_GHKSTJ_KSDY00(ID0000,BH0000,GHKSBH,GHKSMC) select AD_ID0000,0,rownum,YJHSBH from ( select distinct YJHSBH from VW_BM_YJHSXM order by YJHSBH ); else insert into BM_GHKSTJ_KSDY00(ID0000,BH0000,GHKSBH,GHKSMC) select AD_ID0000,0,rownum,EJHSBH from ( select distinct EJHSBH from VW_BM_EJHSXM order by EJHSBH ); end if ; end if; if AS_TJLB00='0' then OPEN CUR_ZY_FYMX00_FPXM00; elsif AS_TJLB00='1' then OPEN CUR_ZY_FYMX00_YJHSXM; else OPEN CUR_ZY_FYMX00_EJHSXM; end if ; loop if AS_TJLB00='0' then fetch CUR_ZY_FYMX00_FPXM00 into ls_HSXMBH,ls_KSBH00,ls_HJJE00; exit when CUR_ZY_FYMX00_FPXM00%NOTFOUND; elsif AS_TJLB00='1' then fetch CUR_ZY_FYMX00_YJHSXM into ls_HSXMBH,ls_KSBH00,ls_HJJE00; exit when CUR_ZY_FYMX00_YJHSXM%NOTFOUND; else fetch CUR_ZY_FYMX00_EJHSXM into ls_HSXMBH,ls_KSBH00,ls_HJJE00; exit when CUR_ZY_FYMX00_EJHSXM%NOTFOUND; end if ; if ls_cs0001='N' then if ls_HJJE00<>0 then select Count(1) into VCounter from BM_GHKSTJ_KSDY00 where ID0000=AD_ID0000 and trim(GHKSMC)=ls_HSXMBH; if VCounter>0 then select lpad(to_char(GHKSBH),2,'0') into ls_BHSX00 from BM_GHKSTJ_KSDY00 where ID0000=AD_ID0000 AND GHKSMC=ls_HSXMBH; else ls_BHSX01:=ls_BHSX01+1; select lpad(to_char(ls_BHSX01),2,'0') into ls_BHSX00 from dual; insert into BM_GHKSTJ_KSDY00(ID0000,BH0000,GHKSBH,GHKSMC) VALUES(AD_ID0000,0,ls_BHSX00,ls_HSXMBH); end if; if ls_BHSX00>'98' then AS_YHMSG0:='住院病区收入统计失败,请察看详细信息并与系统管理员联系!'; AS_SYSMSG:='所设置的核算项目超过98个'; rollback; return; end if; select count(1)into VCounter from BM_YYSFTJ where ID0000=AD_ID0000 and BMBH00=ls_KSBH00; if VCounter>0 then SP_EXECUTE_SQL('UPDATE BM_YYSFTJ SET SFXM'||ls_BHSX00||'= NVL(SFXM'||ls_BHSX00||',0)+'||to_char(ls_HJJE00)||',XMHJ00=NVL(XMHJ00,0)+'||to_char(ls_HJJE00)||' where BMBH00 = '||to_char(ls_KSBH00)||' and ID0000='||to_char(ad_ID0000)); else SP_EXECUTE_SQL('insert into BM_YYSFTJ(BMBH00,SFXM'||ls_BHSX00||',ID0000,XMHJ00) values('||to_char(ls_KSBH00)||','||to_char(ls_HJJE00)||','||to_char(AD_ID0000)||','||to_char(ls_HJJE00)||')'); end if; end if; end if; if ls_cs0001='Y' then begin select lpad(to_char(GHKSBH),2,'0') into ls_BHSX00 from BM_GHKSTJ_KSDY00 where ID0000=AD_ID0000 AND GHKSMC=ls_HSXMBH; /* if ls_HSXMBH>'98' then AS_YHMSG0:='住院病区收入统计失败,请察看详细信息并与系统管理员联系!'; AS_SYSMSG:='所设置的核算项目(发票)超过98个'; ROLLBACK; RETURN; end if; */ select count(1)into VCounter from BM_YYSFTJ where ID0000=AD_ID0000 AND BMBH00=ls_KSBH00; if VCounter>0 then SP_EXECUTE_SQL('UPDATE BM_YYSFTJ SET SFXM'||trim(ls_BHSX00)||'= NVL(SFXM'||trim(ls_BHSX00)||',0)+'||to_char(ls_HJJE00)||',XMHJ00=NVL(XMHJ00,0)+'||to_char(ls_HJJE00)||' where BMBH00 = '||to_char(ls_KSBH00)||' and ID0000='||to_char(ad_ID0000)); else SP_EXECUTE_SQL('insert into BM_YYSFTJ(BMBH00,SFXM'||trim(ls_BHSX00)||',ID0000,XMHJ00) values('||to_char(ls_KSBH00)||','||to_char(ls_HJJE00)||','||to_char(AD_ID0000)||','||to_char(ls_HJJE00)||')'); end if; end; end if; end loop; if AS_TJLB00='0' then CLOSE CUR_ZY_FYMX00_FPXM00; elsif AS_TJLB00='1' then CLOSE CUR_ZY_FYMX00_YJHSXM; else CLOSE CUR_ZY_FYMX00_EJHSXM; end if ; if AS_KSBH00=-1 then delete BM_YYSFTJ where ID0000=AD_ID0000 AND XMHJ00=0; insert into BM_YYSFTJ(ID0000,BMBH00,XMHJ00,SFXM01,SFXM02,SFXM03,SFXM04,SFXM05,SFXM06,SFXM07,SFXM08,SFXM09,SFXM10,SFXM11,SFXM12,SFXM13,SFXM14,SFXM15,SFXM16,SFXM17,SFXM18,SFXM19,SFXM20, SFXM21,SFXM22,SFXM23,SFXM24,SFXM25,SFXM26,SFXM27,SFXM28,SFXM29,SFXM30,SFXM31,SFXM32,SFXM33,SFXM34,SFXM35,SFXM36,SFXM37,SFXM38,SFXM39,SFXM40, SFXM41,SFXM42,SFXM43,SFXM44,SFXM45,SFXM46,SFXM47,SFXM48,SFXM49,SFXM50,SFXM51,SFXM52,SFXM53,SFXM54,SFXM55,SFXM56,SFXM57,SFXM58,SFXM59,SFXM60, SFXM61,SFXM62,SFXM63,SFXM64,SFXM65,SFXM66,SFXM67,SFXM68,SFXM69,SFXM70,SFXM71,SFXM72,SFXM73,SFXM74,SFXM75,SFXM76,SFXM77,SFXM78,SFXM79,SFXM80, SFXM81,SFXM82,SFXM83,SFXM84,SFXM85,SFXM86,SFXM87,SFXM88,SFXM89,SFXM90,SFXM91,SFXM92,SFXM93,SFXM94,SFXM95,SFXM96,SFXM97,SFXM98) select ad_ID0000,999999999,sum(XMHJ00),sum(SFXM01),sum(SFXM02),sum(SFXM03),sum(SFXM04),sum(SFXM05),sum(SFXM06),sum(SFXM07),sum(SFXM08),sum(SFXM09),sum(SFXM10),sum(SFXM11),sum(SFXM12),sum(SFXM13), sum(SFXM14),sum(SFXM15),sum(SFXM16),sum(SFXM17),sum(SFXM18),sum(SFXM19),sum(SFXM20),sum(SFXM21),sum(SFXM22),sum(SFXM23),sum(SFXM24),sum(SFXM25),sum(SFXM26),sum(SFXM27),sum(SFXM28), sum(SFXM29),sum(SFXM30),sum(SFXM31),sum(SFXM32),sum(SFXM33),sum(SFXM34),sum(SFXM35),sum(SFXM36),sum(SFXM37),sum(SFXM38),sum(SFXM39),sum(SFXM40),sum(SFXM41),sum(SFXM42),sum(SFXM43), sum(SFXM44),sum(SFXM45),sum(SFXM46),sum(SFXM47),sum(SFXM48),sum(SFXM49),sum(SFXM50),sum(SFXM51),sum(SFXM52),sum(SFXM53),sum(SFXM54),sum(SFXM55),sum(SFXM56),sum(SFXM57),sum(SFXM58), sum(SFXM59),sum(SFXM60),sum(SFXM61),sum(SFXM62),sum(SFXM63),sum(SFXM64),sum(SFXM65),sum(SFXM66),sum(SFXM67),sum(SFXM68),sum(SFXM69),sum(SFXM70),sum(SFXM71),sum(SFXM72),sum(SFXM73), sum(SFXM74),sum(SFXM75),sum(SFXM76),sum(SFXM77),sum(SFXM78),sum(SFXM79),sum(SFXM80),sum(SFXM81),sum(SFXM82),sum(SFXM83),sum(SFXM84),sum(SFXM85),sum(SFXM86),sum(SFXM87),sum(SFXM88), sum(SFXM89),sum(SFXM90),sum(SFXM91),sum(SFXM92),sum(SFXM93),sum(SFXM94),sum(SFXM95),sum(SFXM96),sum(SFXM97),sum(SFXM98) from BM_YYSFTJ where ID0000 = ad_ID0000; end if; open CUR_BM_YYSFTJ; loop fetch CUR_BM_YYSFTJ into ls_bmbh00 ; exit when CUR_BM_YYSFTJ%NOTFOUND; update BM_YYSFTJ set sfxm01=nvl(sfxm01,0),sfxm02=nvl(sfxm02,0),sfxm03=nvl(sfxm03,0),sfxm04=nvl(sfxm04,0),sfxm05=nvl(sfxm05,0),sfxm06=nvl(sfxm06,0),sfxm07=nvl(sfxm07,0), sfxm08=nvl(sfxm08,0),sfxm09=nvl(sfxm09,0),sfxm10=nvl(sfxm10,0),sfxm11=nvl(sfxm11,0),sfxm12=nvl(sfxm12,0),sfxm13=nvl(sfxm13,0),sfxm14=nvl(sfxm14,0),sfxm15=nvl(sfxm15,0), sfxm16=nvl(sfxm16,0),sfxm17=nvl(sfxm17,0),sfxm18=nvl(sfxm18,0),sfxm19=nvl(sfxm19,0),sfxm20=nvl(sfxm20,0),sfxm21=nvl(sfxm21,0),sfxm22=nvl(sfxm22,0),sfxm23=nvl(sfxm23,0), sfxm24=nvl(sfxm24,0),sfxm25=nvl(sfxm25,0),sfxm26=nvl(sfxm26,0),sfxm27=nvl(sfxm27,0),sfxm28=nvl(sfxm28,0),sfxm29=nvl(sfxm29,0),sfxm30=nvl(sfxm30,0),sfxm31=nvl(sfxm31,0), sfxm32=nvl(sfxm32,0),sfxm33=nvl(sfxm33,0),sfxm34=nvl(sfxm34,0),sfxm35=nvl(sfxm35,0),sfxm36=nvl(sfxm36,0),sfxm37=nvl(sfxm37,0),sfxm38=nvl(sfxm38,0),sfxm39=nvl(sfxm39,0), sfxm40=nvl(sfxm40,0),sfxm41=nvl(sfxm41,0),sfxm42=nvl(sfxm42,0),sfxm43=nvl(sfxm43,0),sfxm44=nvl(sfxm44,0),sfxm45=nvl(sfxm45,0),sfxm46=nvl(sfxm46,0),sfxm47=nvl(sfxm47,0), sfxm48=nvl(sfxm48,0),sfxm49=nvl(sfxm49,0),sfxm50=nvl(sfxm50,0),sfxm51=nvl(sfxm51,0),sfxm52=nvl(sfxm52,0),sfxm53=nvl(sfxm53,0),sfxm54=nvl(sfxm54,0),sfxm55=nvl(sfxm55,0) where ID0000 = ad_ID0000 and bmbh00=ls_bmbh00; end loop; close CUR_BM_YYSFTJ; commit; exception when others then AS_YHMSG0:='出错原因不详,请记录此信息并和系统管理员联系！'; AS_SYSMSG:=substr(SQLERRM||'执行存储过程错误:SP_ZY_KSSRHZ_RBB001('||as_KSRQ00||','||as_KSSJ00||','||as_JSRQ00||','||as_JSSJ00|| ','||AS_TJLB00||','||as_KSLB00||','||to_char(as_KSBH00)||','||as_JZLB00||'ad_ID0000,as_YHMSG0,as_SYSMSG',1,200); rollback; end;