create or replace procedure SP_ZY_KSSRHZ_RBB000 ( 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':执行科室,'2':医生组 as_KSBH00 in char, --统计的科室编号 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 ls_FPBH00 BM_YYHSXM.BH0000%TYPE; ls_FPXMMC BM_FPXM00.XMMC00%TYPE; ls_TJLB00 BM_FPXM00.BH0000%TYPE; ls_HJJE00 ZY_FYMX00.HJJE00%TYPE; cursor CUR_ZY_BM_FPXM00_TJ IS select '0' TJLB00,BH0000,XMMC00 from BM_FPXM00 union all select distinct '1' TJLB00,YJHSBH,HSXMMC from VW_BM_YJHSXM union all select '2' TJLB00,EJHSBH,HSXMMC from VW_BM_EJHSXM; -- MODIFICATION HISTORY -- Person Date Comments -- xzw 2008.02.22 根据分院进行统计KDBQ00->KDKS00; -- jinfl 2013.01.09 解决统计一级核算时多条显示问题 ZYSF-20130109-001 -- linzy 2014.06.11 增加入参ad_YSZID0 统计医生组 by ZYSF-20140609-003 -- jlg 2016.05.26 把ZY_BRFY00去掉 ZYSF9-20160526-001 -- ------- ------------ -------------------------------------------------------- begin select SQ_BM_MZSRTJ_ID0000.nextval into ad_ID0000 from dual; if as_TJLB00='0' then insert into BM_MZSRTJ(ID0000,XMBH01,XMBH02,XMJE01) select ad_ID0000,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_YYSFXM E,BM_FPXM00 F,BM_BRXXB0 D,IC_YBBRLB G,BM_BMBM00 H where A.XMBH00 = E.SFXMID and A.KDKS00 = H.BMBH00 and E.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 (H.YYID00 = as_YYID00 or as_YYID00 = '0') and (ad_YSZID0=-1 or ad_YSZID0=nvl(A.YSZID0,0)) and (G.YBMC00=as_YBMC00 or as_YBMC00 = '所有病人') group by F.BH0000,decode(AS_KSLB00,'0',KDKS00,'1',ZXKS00,'2',nvl(A.YSZID0,0)); elsif as_TJLB00='1' then insert into BM_MZSRTJ(ID0000,XMBH01,XMBH02,XMJE01) select ad_ID0000,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,BM_YYSFXM E,VW_BM_YJHSXM F,BM_BRXXB0 D,IC_YBBRLB G,BM_BMBM00 H where A.XMBH00 = E.SFXMID and A.KDKS00 = H.BMBH00 and E.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 (H.YYID00 = as_YYID00 or as_YYID00 = '0') and (ad_YSZID0=-1 or ad_YSZID0=nvl(A.YSZID0,0)) and (G.YBMC00=as_YBMC00 or as_YBMC00 = '所有病人') group by F.YJHSBH,decode(AS_KSLB00,'0',KDKS00,'1',ZXKS00,'2',nvl(A.YSZID0,0)); else insert into BM_MZSRTJ(ID0000,XMBH01,XMBH02,XMJE01) select ad_ID0000,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,BM_YYSFXM E,VW_BM_EJHSXM F,BM_BRXXB0 D,IC_YBBRLB G,BM_BMBM00 H where A.XMBH00 = E.SFXMID and A.KDKS00 = H.BMBH00 and E.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 (H.YYID00 = as_YYID00 or as_YYID00 = '0') and (G.YBMC00=as_YBMC00 or as_YBMC00 = '所有病人') and (ad_YSZID0=-1 or ad_YSZID0=nvl(A.YSZID0,0)) group by F.EJHSBH,decode(AS_KSLB00,'0',KDKS00,'1',ZXKS00,'2',nvl(A.YSZID0,0)); end if; if AS_KSLB00='2' then update BM_MZSRTJ A set XMMC02=(select YSZMC0 from ZS_YSZ000 where YSZID0=to_number(A.XMBH02)) where ID0000=ad_ID0000; else update BM_MZSRTJ A set XMMC02=(select BMMC00 from BM_BMBM00 where BMBH00=to_number(A.XMBH02)) where ID0000=ad_ID0000; end if; --添加一条合计数 insert into BM_MZSRTJ(ID0000,XMBH01,XMMC02,XMJE01) select ad_ID0000,'00',' 合计',sum(NVL(XMJE01,0)) from BM_MZSRTJ where ID0000 = ad_ID0000; open CUR_ZY_BM_FPXM00_TJ; loop fetch CUR_ZY_BM_FPXM00_TJ into ls_TJLB00,ls_FPBH00,ls_FPXMMC; exit when CUR_ZY_BM_FPXM00_TJ%NOTFOUND; if ls_TJLB00=as_TJLB00 then select nvl(sum(XMJE01),0) into ls_HJJE00 from BM_MZSRTJ where ID0000 = ad_ID0000 and XMBH01 = ls_FPBH00; insert into BM_MZSRTJ(ID0000,XMBH01,XMMC01,XMBH02,XMMC02,XMJE01) values(ad_ID0000,ls_FPBH00,ls_FPXMMC,'-1',' 小计',ls_HJJE00); end if; end loop; close CUR_ZY_BM_FPXM00_TJ; commit; exception when OTHERS then as_YHMSG0:='出错原因不详,请记录此信息并和系统管理员联系！'; as_SYSMSG:=substr(SQLERRM||'执行存储过程错误:SP_ZY_KSSRHZ_RBB000('||as_KSRQ00||','||as_KSSJ00||','||as_JSRQ00||','|| as_JSSJ00||','||'ad_ID0000,as_YHMSG0,as_SYSMSG',1,150); rollback; end;