prompt 72.门诊收入（按核算项目分类） VM_SF_MZSRHSXM create or replace view VM_SF_MZSRHSXM ( HSBH00,--核算项目 HSXMMC,--核算名称 HJJE00,--合计金额 XJJE00,--现金模式合计 YJJJE0,--预交金模式合计 JZJE00,--记账金额合计 JZXJJE,--现金模式 记账金额合计 JZYJJE,--预交金模式记账金额合计 CZRQ00,--操作日期 CZSJ00,--操作时间 FBBH00,--费别编号 JFLBID,--交费类别 JMJE00,--减免金额 JMBZ00,--减免标志 XMLB00,--项目类别 0,发票 1,一级 2 二级 JZDH00,--结账单号 KDKS00,--开单科室 BRID00,--病人id YBLB00,--医保类别 XMBH00, ZXKS00, BH0000, CZY000, YYID00 --开单科室对应的医院信息 ) as -- MODIFICATION HISTORY -- Person Date Comments --zhangyc 2012.04.25 增加列jzdh00,kdks00,brid00,yblb00 by MZSF-20120423-002 --zhangyc 2012.05.08 增加列XMBH00,ZXKS00,BH0000 BY MZSF-20120508-001 --zhangyc 2012.10.09 增加CZY000 by MZSF-20120807-003 --linzy 2015.03.20 增加字段YYID00 by MZSF-20150105-004 --dsm 2017.03.21 CZRQ00,CZSJ00 必须取SF_BRFY00 for MZSF-20170320-002 --zhanghr 2017.07.14 增加记账金额等字段 JZJE00, JZXJJE, JZYJJE ------------- ------------ --------------------------------------------------------------- select C.BH0000, C.XMMC00, A.HJJE00, decode(D.JFLBID,0,A.HJJE00,0), decode(D.JFLBID,1,A.HJJE00,0), A.JZJE00, decode(D.JFLBID,0,A.JZJE00,0), decode(D.JFLBID,1,A.JZJE00,0), D.CZRQ00, D.CZSJ00, D.BRFB00, D.JFLBID, A.JMJE00, A.JMBZ00, 0, D.JZDH00, A.kdks00, DD.brid00, DD.YBLB00, A.XMBH00, A.ZXKS00, C.BH0000, D.CZY000, (select YYID00 from BM_BMBM00 where BMBH00=A.KDKS00) YYID00 from SF_FYMX00 A,BM_YYSFXM B,BM_FPXM00 C,SF_BRFY00 D,BM_BRXXB0 DD where A.XMBH00=B.SFXMID and B.MZFPID=C.FPXMID AND A.DJH000=D.DJH000 AND D.brid00=DD.brid00 union all select substr(C.BH0000,1,2), C.HSXMMC, A.HJJE00, decode(D.JFLBID,0,A.HJJE00,0), decode(D.JFLBID,1,A.HJJE00,0), A.JZJE00, decode(D.JFLBID,0,A.JZJE00,0), decode(D.JFLBID,1,A.JZJE00,0), D.CZRQ00, D.CZSJ00, D.BRFB00, D.JFLBID, A.JMJE00, A.JMBZ00, 1, D.JZDH00, A.kdks00, DD.brid00, DD.YBLB00, A.XMBH00, A.ZXKS00, C.BH0000, D.CZY000, (select YYID00 from BM_BMBM00 where BMBH00=A.KDKS00) YYID00 from SF_FYMX00 A,BM_YYSFXM B,BM_YYHSXM C,SF_BRFY00 D ,BM_BRXXB0 DD where A.XMBH00=B.SFXMID and B.HSXMID=C.HSXMID AND A.DJH000=D.DJH000 AND D.brid00=DD.brid00 union all select C.BH0000, C.HSXMMC, A.HJJE00, decode(D.JFLBID,0,A.HJJE00,0), decode(D.JFLBID,1,A.HJJE00,0), A.JZJE00, decode(D.JFLBID,0,A.JZJE00,0), decode(D.JFLBID,1,A.JZJE00,0), D.CZRQ00, D.CZSJ00, D.BRFB00, D.JFLBID, A.JMJE00, A.JMBZ00, 2, D.JZDH00, A.kdks00, DD.brid00, DD.YBLB00, A.XMBH00, A.ZXKS00, C.BH0000, D.CZY000, (select YYID00 from BM_BMBM00 where BMBH00=A.KDKS00) YYID00 from SF_FYMX00 A,BM_YYSFXM B,BM_YYHSXM C,SF_BRFY00 D,BM_BRXXB0 DD where A.XMBH00=B.SFXMID and B.HSXMID=C.HSXMID AND A.DJH000=D.DJH000 AND D.brid00=DD.brid00 / --%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% PROMPT 193、 门诊日收入报表(不包括单位体检费用) SP_SF_KCDWFY create or replace procedure 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.7.13 除自费和医保外的其他收入，不从hjje00字段取值，改从JZJE00取值 -- ---------- ------------ -------------------------------------------------------------------------- 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_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 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; / show error; --%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%