create or replace procedure sd_hospital.SP_SF_KCDWFY ( as_KSRQ00 in char, --开始日期 as_KSSJ00 in char, --开始时间 as_JSRQ00 in char, --结束日期 as_JSSJ00 in char, --结束时间 as_TJXMLB in number, --统计项目类别 0,发票 1,一级 2 二级 ad_ID0000 out number, --统计报表对应的ID as_YHMSG0 out varchar, --存储过程提示的错误信息 as_SYSMSG out varchar, --系统提示的错误信息 as_YBFLMC in char default '全部', --医保分类名称 as_YYID00 in char default '0' --医院id ) as -- MODIFICATION HISTORY -- Person Date Comments -- zhangyc 2010.09.27 created; -- csf 2011.02.17 扣除体检收入（这里的体检收入指的是单位体检），原需求mzsf-20100915-003有处理过，但体现的数据是错误的,现纠正 -- qks 2011.09.13 需要体现已结算的体检收入，取消体检结算引起的报表数据问题目前不处理。 -- dsm 2012.06.21 加处理GHKS80 for MZSF-20120621-001 -- zhangyc 2013.05.20 增加入参as_YBFLMC 医保分类名称(分类过程SF_SF_YBFLMC) by MZSF-20130513-002; -- linzy 2015.03.20 增加医院ID进行统计 by MZSF-20150105-004 -- zhanghr 2017.07.13 除自费和医保外的其他收入，不从hjje00字段取值，改从JZJE00取值 MZSF-20170717-002 -- nicl 2024.05.09 从5.0迁移无修改 MZSF13-20240505-003 -- dsm 2024.05.11 VM_SF_MZSRHSXMN-->VM_SF_MZSRHSXM,并调整VM_SF_MZSRHSXM视图 -- ---------- ------------ -------------------------------------------------------------------------- ls_BH0000 BM_YYHSXM.BH0000%TYPE; ls_HJJE00 SF_BRFY00.HJJE00%TYPE; ls_XJJE00 SF_BRFY00.HJJE00%TYPE; ls_YJJJE0 SF_BRFY00.HJJE00%TYPE; ls_colnum varchar2(2); ls_FBBH00 BM_BRFBB0.FBBH00%TYPE; Is_JMJE00 SF_FYMX00.JMJE00%TYPE; --所有收入(2011.09.13暂时没用) cursor CUR_SF_XJSR IS select HSBH00,sum(HJJE00),sum(XJJE00),sum(YJJJE0),sum(decode(JMBZ00,'1',JMJE00,0)) JMJE00 from VM_SF_MZSRHSXM A where CZRQ00 >= as_KSRQ00 and CZRQ00 <= as_JSRQ00 and CZRQ00||CZSJ00 >= as_KSRQ00||as_KSSJ00 and CZRQ00||CZSJ00 <= as_JSRQ00||as_JSSJ00 and XMLB00=as_TJXMLB and (as_YYID00='0' or rpad(as_YYID00,6,' ')=A.YYID00) and (SF_SF_YBFLMC(BRID00)=as_YBFLMC OR as_YBFLMC='全部') and FBBH00<>99 group by HSBH00; --医保收入 CURSOR CUR_SF_YBSR IS select HSBH00,sum(HJJE00),sum(XJJE00),sum(YJJJE0),sum(decode(JMBZ00,'1',JMJE00,0)) JMJE00 from VM_SF_YBSRHSXM A where CZRQ00 >= as_KSRQ00 and CZRQ00 <= as_JSRQ00 and CZRQ00||CZSJ00 >= as_KSRQ00||as_KSSJ00 and CZRQ00||CZSJ00 <= as_JSRQ00||as_JSSJ00 and XMLB00=as_TJXMLB and (as_YYID00='0' or rpad(as_YYID00,6,' ')=A.YYID00) and (SF_SF_YBFLMC(BRID00)=as_YBFLMC OR as_YBFLMC='全部') group by HSBH00; --除自费和医保外的其他收入 -- CURSOR CUR_SF_QTSR IS -- select A.HSBH00,sum(A.HJJE00),sum(XJJE00),sum(YJJJE0),FBBH00,sum(decode(JMBZ00,'1',JMJE00,0)) JMJE00 from VM_SF_MZSRHSXMN A -- WHERE CZRQ00 >= as_KSRQ00 -- AND CZRQ00 <= as_JSRQ00 -- AND CZRQ00||CZSJ00 >= as_KSRQ00||as_KSSJ00 -- AND CZRQ00||CZSJ00 <= as_JSRQ00||as_JSSJ00 -- AND A.FBBH00 not in (1,3) -- and XMLB00=as_TJXMLB -- and FBBH00<>99 -- GROUP BY HSBH00,FBBH00; --记账收入 CURSOR CUR_SF_QTSR IS select HSBH00,sum(JZJE00),sum(JZXJJE),sum(JZYJJE),FBBH00,sum(decode(JMBZ00,'1',JMJE00,0)) JMJE00 from (select HSBH00,JZJE00,JZXJJE,JZYJJE,FBBH00,JMBZ00,JMJE00 from VM_SF_MZSRHSXM A where CZRQ00 >= as_KSRQ00 and CZRQ00 <= as_JSRQ00 and CZRQ00||CZSJ00 >= as_KSRQ00||as_KSSJ00 and CZRQ00||CZSJ00 <= as_JSRQ00||as_JSSJ00 and FBBH00 not in (1,3) and XMLB00=as_TJXMLB and (as_YYID00='0' or rpad(as_YYID00,6,' ')=A.YYID00) and (SF_SF_YBFLMC(BRID00)=as_YBFLMC OR as_YBFLMC='全部') and FBBH00<>99 Union all select HSBH00,HJJE00,XJJE00,YJJJE0,FBBH00,JMBZ00,JMJE00 from VM_SF_MZSRHSXM_TJ A where CZRQ00 >= as_KSRQ00 and CZRQ00 <= as_JSRQ00 and CZRQ00||CZSJ00 >= as_KSRQ00||as_KSSJ00 and CZRQ00||CZSJ00 <= as_JSRQ00||as_JSSJ00 and XMLB00=as_TJXMLB and (as_YYID00='0' or rpad(as_YYID00,6,' ')=A.YYID00) and (SF_SF_YBFLMC(BRID00)=as_YBFLMC OR as_YBFLMC='全部') ) GROUP BY HSBH00,FBBH00; begin Select SQ_BM_GHKSTJ_ID0000.nextval into ad_ID0000 from dual; --GHRQ00 核算项目名称 GHKS01 核算项目编号 GHKS02 收入合计 GHKS03 自费病人的现金结算收入 GHKS04 自费病人的卡收入 GHKS05 医保收入 --除上面的字段固定外，其余的费别(除1,3费别)动态生成 GHKS06 本院离休。。。,GHKS80 IC医保结算 insert into BM_GHKSTJ(GHRQ00,GHKS01,GHKS02,GHKS03,GHKS04,GHKS99,ID0000,GHKS80) select ' ',HSBH00,sum(HJJE00),sum(XJJE00),sum(YJJJE0),sum(decode(JMBZ00,'1',JMJE00,0)) JMJE00,ad_ID0000,0 from (select HSBH00,HJJE00,XJJE00,YJJJE0,JMBZ00,JMJE00 from VM_SF_MZSRHSXM A where CZRQ00 >= as_KSRQ00 and CZRQ00 <= as_JSRQ00 and CZRQ00||CZSJ00 >= as_KSRQ00||as_KSSJ00 and CZRQ00||CZSJ00 <= as_JSRQ00||as_JSSJ00 and XMLB00=as_TJXMLB and (as_YYID00='0' or rpad(as_YYID00,6,' ')=A.YYID00) and (SF_SF_YBFLMC(BRID00)=as_YBFLMC OR as_YBFLMC='全部') and FBBH00<>99 Union all select HSBH00,HJJE00,XJJE00,YJJJE0,JMBZ00,JMJE00 from VM_SF_MZSRHSXM_TJ A where CZRQ00 >= as_KSRQ00 and CZRQ00 <= as_JSRQ00 and CZRQ00||CZSJ00 >= as_KSRQ00||as_KSSJ00 and CZRQ00||CZSJ00 <= as_JSRQ00||as_JSSJ00 and XMLB00=as_TJXMLB and (as_YYID00='0' or rpad(as_YYID00,6,' ')=A.YYID00) and (SF_SF_YBFLMC(BRID00)=as_YBFLMC OR as_YBFLMC='全部') ) group by HSBH00; OPEN CUR_SF_YBSR; LOOP FETCH CUR_SF_YBSR INTO ls_BH0000,ls_HJJE00,ls_XJJE00,ls_YJJJE0,Is_JMJE00 ; --医保收入 EXIT WHEN CUR_SF_YBSR%NOTFOUND; Update BM_GHKSTJ Set GHKS03=NVL(GHKS03,0)-ls_XJJE00, GHKS04=NVL(GHKS04,0)-ls_YJJJE0, GHKS80=NVL(GHKS80,0)+ls_YJJJE0, GHKS05=NVL(GHKS05,0)+ls_HJJE00 where GHKS01 = ls_BH0000 and ID0000=ad_ID0000; if sql%notfound then insert into BM_GHKSTJ(GHRQ00,GHKS01,GHKS02,GHKS03,GHKS04,GHKS05,GHKS99,ID0000,GHKS80) values(' ',ls_BH0000,0,-ls_XJJE00,-ls_YJJJE0,ls_HJJE00,Is_jmje00,ad_ID0000,ls_YJJJE0); end if; END LOOP; close CUR_SF_YBSR; --除自费和医保外的其他收入 OPEN CUR_SF_QTSR ; LOOP FETCH CUR_SF_QTSR INTO ls_BH0000,ls_HJJE00,ls_XJJE00,ls_YJJJE0,ls_FBBH00,Is_JMJE00 ; --本院离休 EXIT WHEN CUR_SF_QTSR%NOTFOUND; begin SELECT lpad(to_char(col000+5),2,'0') into ls_colnum from (SELECT FBBH00,ROWNUM col000 FROM BM_BRFBB0 WHERE FBBH00 NOT IN (1,3) order by FBBH00) WHERE FBBH00=ls_FBBH00; exception when others then ls_colnum:='01'; end; SP_EXECUTE_SQL('Update BM_GHKSTJ Set GHKS03=nvl(GHKS03,0)-('||to_char(ls_XJJE00)||'),'|| 'GHKS04=NVL(GHKS04,0)-('||to_char(ls_YJJJE0)||'),'|| 'GHKS'||ls_colnum||'= NVL(GHKS'||ls_colnum||',0)+('||to_char(ls_HJJE00)|| ') where GHKS01 = '||ls_BH0000||' and ID0000='||to_char(ad_ID0000)); END LOOP; CLOSE CUR_SF_QTSR ; update BM_GHKSTJ set ghks01=nvl(ghks01,0),ghks02=nvl(ghks02,0),ghks03=nvl(ghks03,0),ghks04=nvl(ghks04,0), ghks05=nvl(ghks05,0),ghks06=nvl(ghks06,0),ghks07=nvl(ghks07,0),ghks08=nvl(ghks08,0), ghks09=nvl(ghks09,0),ghks10=nvl(ghks10,0),ghks11=nvl(ghks11,0),ghks12=nvl(ghks12,0), ghks13=nvl(ghks13,0),ghks14=nvl(ghks14,0),ghks15=nvl(ghks15,0),ghks16=nvl(ghks16,0), ghks17=nvl(ghks17,0),ghks18=nvl(ghks18,0),ghks19=nvl(ghks19,0),ghks20=nvl(ghks20,0), ghks99=nvl(ghks99,0) where ID0000 = ad_ID0000; --添加一条总计行 Insert into BM_GHKSTJ(GHRQ00,ID0000,GHKS01,GHKS02,GHKS03,GHKS05,GHKS06,GHKS04,GHKS07,GHKS08,GHKS09,GHKS10,GHKS11, GHKS12,GHKS13,GHKS14,GHKS15,GHKS16,GHKS17,GHKS18,GHKS19,GHKS20,GHKS99,GHKS80) select ' ',ad_ID0000,'99999',sum(nvl(GHKS02,0)),sum(nvl(GHKS03,0)),sum(nvl(GHKS05,0)),sum(nvl(GHKS06,0)), sum(nvl(GHKS04,0)),sum(nvl(GHKS07,0)),sum(nvl(GHKS08,0)),sum(nvl(GHKS09,0)),sum(nvl(GHKS10,0)), sum(nvl(GHKS11,0)),sum(nvl(GHKS12,0)),sum(nvl(GHKS13,0)),sum(nvl(GHKS14,0)),sum(nvl(GHKS15,0)), sum(nvl(GHKS16,0)),sum(nvl(GHKS17,0)),sum(nvl(GHKS18,0)),sum(nvl(GHKS19,0)),sum(nvl(GHKS20,0)), sum(nvl(ghks99,0)),sum(nvl(ghks80,0)) from BM_GHKSTJ where ID0000 = ad_ID0000; --commit; exception WHEN OTHERS THEN as_YHMSG0:='出错原因不详,请记录此信息并和系统管理员联系！'; as_SYSMSG:=substr(SQLERRM||'执行存储过程错误:SP_SF_KCDWFY('||as_KSRQ00||','||as_KSSJ00||','||as_JSRQ00||','|| as_JSSJ00||','||'ad_ID0000,as_YHMSG0,as_SYSMSG',1,150); rollback; end;