-- Start of DDL Script for Procedure SD_HOSPITAL.SP_SF_MZKSSR_RBB000 -- Generated 2018-05-10 17:51:23 from SD_HOSPITAL@KFYY CREATE OR REPLACE PROCEDURE sp_sf_mzkssr_rbb000 ( as_KSRQ00 IN CHAR, --开始日期 as_KSSJ00 IN CHAR, --开始时间 as_JSRQ00 IN CHAR, --结束日期 as_JSSJ00 IN CHAR, --结束时间 as_KSMC00 IN CHAR, --开单病区名称 as_SFYB00 IN CHAR, --'0':所有病人,'1':现金病人,'2':持卡病人,'3':记账,'4':公费 as_TJLB00 IN CHAR, --'0':按开单科室,'1':按执行科室 as_FYFSLB IN CHAR, --费用发生类别：'0':全部 '1':自付 '2': 记帐 '3': 减免 as_ybmc00 in char, --医保名称 as_YYID00 in char, --医院ID >0 各分院 =0全部 ad_ID0000 OUT NUMBER, --统计报表对应的ID as_YHMSG0 OUT VARCHAR, --存储过程提示的错误信息 as_SYSMSG OUT VARCHAR --系统提示的错误信息 ) as ls_BMBH00 SF_FYMX00.KDBQ00%TYPE; --部门编号 ls_XMBH00 SF_FYMX00.XMBH00%TYPE; ls_HJJE00 SF_FYMX00.HJJE00%TYPE; ls_ZFJE00 SF_FYMX00.ZFJE00%TYPE; ls_JZJE00 SF_FYMX00.JZJE00%TYPE; ls_GFJE00 SF_FYMX00.GFJE00%TYPE; ls_BH0000 BM_FPXM00.BH0000%TYPE; SF_ZXTJKD CHAR(1); ls_GHRC00 number(10); ls_Count0 number(10); LS_VALUE0 number(5); CURSOR CUR_SF_MZKSSR_KDFYMX IS SELECT H.BH0000,A.KDKS00,sum(A.HJJE00),sum(A.ZFJE00),sum(A.JZJE00),sum(A.GFJE00) from SF_FYMX00 A,BM_BMBM00 B,SF_BRFY00 C,BM_BRXXB0 E,BM_YYSFXM G,BM_FPXM00 H,ic_ybbrlb d where A.KDKS00 = B.BMBH00 and A.DJH000 = C.DJH000 and C.BRID00 = E.BRID00 and A.XMBH00 = G.SFXMID and G.MZFPID = H.FPXMID and d.fbbh00=e.fbbh00 and d.yblb00=e.yblb00 and C.CZRQ00 >= as_KSRQ00 and C.CZRQ00 <= as_JSRQ00 and C.CZRQ00||C.CZSJ00 >= as_KSRQ00||as_KSSJ00 and C.CZRQ00||C.CZSJ00 <= as_JSRQ00||as_JSSJ00 and (B.BMMC00 = as_KSMC00 or as_KSMC00 ='所有科室') and (d.ybmc00 = as_ybmc00 or as_ybmc00 ='所有病人') and ((as_SFYB00='0') or (as_SFYB00='1' and C.JFLBID=0) or (as_SFYB00='2' and C.JFLBID <> 0) or (as_SFYB00='3')) and (B.YYID00 = as_YYID00 OR as_YYID00 = '0') and (d.FBBH00<>LS_VALUE0 or LS_VALUE0=0)-- by MZSF-20150409-001 group by H.BH0000,A.KDKS00; CURSOR CUR_SF_MZKSSR_ZXFYMX IS SELECT decode(SF_ZXTJKD,'Y',decode(G.JSXM00,'2','98',H.BH0000),H.BH0000),decode(SF_ZXTJKD,'Y',decode(sign(A.XMBH00-9999999995),1,A.KDKS00,A.ZXKS00),A.ZXKS00) ,sum(A.HJJE00),sum(A.ZFJE00),sum(A.JZJE00),sum(A.GFJE00) from SF_FYMX00 A,BM_BMBM00 B,SF_BRFY00 C,BM_BRXXB0 E,BM_YYSFXM G,BM_FPXM00 H,ic_ybbrlb d where A.ZXKS00 = B.BMBH00 and A.DJH000 = C.DJH000 and C.BRID00 = E.BRID00 and A.XMBH00 = G.SFXMID and G.MZFPID = H.FPXMID and d.fbbh00=e.fbbh00 and d.yblb00=e.yblb00 and C.CZRQ00 >= as_KSRQ00 and C.CZRQ00 <= as_JSRQ00 and C.CZRQ00||C.CZSJ00 >= as_KSRQ00||as_KSSJ00 and C.CZRQ00||C.CZSJ00 <= as_JSRQ00||as_JSSJ00 and (B.BMMC00 = as_KSMC00 or as_KSMC00 ='所有科室') and (d.ybmc00 = as_ybmc00 or as_ybmc00 ='所有病人') and ((as_SFYB00='0') or (as_SFYB00='1' and C.JFLBID=0) or (as_SFYB00='2' and C.JFLBID <> 0) or (as_SFYB00='3')) and (B.YYID00 = as_YYID00 OR as_YYID00 = '0') and (d.FBBH00<>LS_VALUE0 or LS_VALUE0=0)-- by MZSF-20150409-001 group by decode(SF_ZXTJKD,'Y',decode(G.JSXM00,'2','98',H.BH0000),H.BH0000), decode(SF_ZXTJKD,'Y',decode(sign(A.XMBH00-9999999995),1,A.KDKS00,A.ZXKS00),A.ZXKS00); -- MODIFICATION HISTORY -- Person Date Comments -- qks 2012.03.05 要求有关药费统计到开单科室，同时增加一列“卫材”（单列出来，不包含在"合计"中）。by MZSF-20120305-001 -- jinfl 2013.04.10 解决卫材无法显示问题 MZSF-20130314-001 -- laijg 2013.06.04 合计与细项相加不等 by MZSF-20130529-001 -- zhangyc 2015.05.27 增加参数SF_MZSRBBSFGLFB作为条件过滤数据 by MZSF-20150409-001 ---------- ----------- ----------------------------------------------- BEGIN select nvl(max(substrb(value0,1,1)),'N') into SF_ZXTJKD from XT_XTCS00 where name00='SF_YFZXTJKD'; begin select to_number(nvl(trim(VALUE0),'0')) into LS_VALUE0 from XT_XTCS00 where name00='SF_MZSRBBSFGLFB'; exception when others then LS_VALUE0:=0; end; select SQ_BM_YYSFTJ_ID0000.nextval into ad_ID0000 from dual; if as_TJLB00 = '0' then OPEN CUR_SF_MZKSSR_KDFYMX; else OPEN CUR_SF_MZKSSR_ZXFYMX; end if; LOOP if as_TJLB00 = '0' then FETCH CUR_SF_MZKSSR_KDFYMX INTO ls_BH0000,ls_BMBH00,ls_HJJE00,ls_ZFJE00,ls_JZJE00,ls_GFJE00; EXIT WHEN CUR_SF_MZKSSR_KDFYMX%NOTFOUND; else FETCH CUR_SF_MZKSSR_ZXFYMX INTO ls_BH0000,ls_BMBH00,ls_HJJE00,ls_ZFJE00,ls_JZJE00,ls_GFJE00; EXIT WHEN CUR_SF_MZKSSR_ZXFYMX%NOTFOUND; end if; if as_FYFSLB= '1' then ls_HJJE00 := ls_ZFJE00; end if; --'1':自付 if as_FYFSLB= '2' then ls_HJJE00 := ls_JZJE00; end if; --'2': 记帐 if as_FYFSLB= '3' then ls_HJJE00 := ls_GFJE00; end if; --'3': 减免 select count(*) into ls_Count0 from BM_YYSFTJ where ID0000=ad_ID0000 and BMBH00=ls_BMBH00; --“卫材”是独立列,金额不统计在合计中 add by 2012.03.05 if SF_ZXTJKD='Y' and as_TJLB00 = '1' and ls_BH0000 ='98' then if ls_Count0>0 then SP_EXECUTE_SQL('Update BM_YYSFTJ Set SFXM'||ls_BH0000||'=NVL(SFXM'||ls_BH0000||',0)+'||to_char(ls_HJJE00)||' where BMBH00='||to_char(ls_BMBH00)||' and ID0000='||to_char(ad_ID0000)); else SP_EXECUTE_SQL('Insert into BM_YYSFTJ(BMBH00,SFXM'||(ls_BH0000)||',ID0000) values('||to_char(ls_BMBH00)||','||to_char(ls_HJJE00)||','||to_char(ad_ID0000)||')'); end if; else if ls_Count0>0 then SP_EXECUTE_SQL('Update BM_YYSFTJ Set SFXM'||ls_BH0000||'=NVL(SFXM'||ls_BH0000||',0)+'||to_char(ls_HJJE00)||',XMHJ00=NVL(XMHJ00,0)+'||to_char(ls_HJJE00)||' where BMBH00='||to_char(ls_BMBH00)||' and ID0000='||to_char(ad_ID0000)); else SP_EXECUTE_SQL('Insert into BM_YYSFTJ(BMBH00,SFXM'||(ls_BH0000)||',ID0000,XMHJ00) values('||to_char(ls_BMBH00)||','||to_char(ls_HJJE00)||','||to_char(ad_ID0000)||','||to_char(ls_HJJE00)||')'); end if; end if; END LOOP; if as_TJLB00 = '0' then CLOSE CUR_SF_MZKSSR_KDFYMX; else CLOSE CUR_SF_MZKSSR_ZXFYMX; end if; --增加挂号人次 SFXM99 update BM_YYSFTJ A SET SFXM99= /*( SELECT COUNT(*) from SF_BRXXB0 B,BM_BRXXB0 C,IC_YBBRLB D where B.BRID00 = C.BRID00 and C.FBBH00 = D.FBBH00 and C.YBLB00 = D.YBLB00 and B.GHKS00 = A.BMBH00 and (d.YBMC00 = as_ybmc00 or as_ybmc00 ='所有病人') and B.GHRQ00 >= as_KSRQ00 and B.GHRQ00 <= as_JSRQ00 and THBZ00 <> '0')*/ (SELECT sum(GHCS00) from VW_SF_GHLSXX B where (B.YBMC00 = as_ybmc00 or as_ybmc00 ='所有病人') and B.CZRQ00 >= as_KSRQ00 and B.CZRQ00 <= as_JSRQ00 and B.GHKS00= A.BMBH00 and B.CZRQ00||B.CZSJ00 >= as_KSRQ00||as_KSSJ00 and B.CZRQ00||B.CZSJ00 <= as_JSRQ00||as_JSSJ00 ) where ID0000 = ad_ID0000; Insert into BM_YYSFTJ(BMBH00,SFXM99,ID0000) select ghks00 ,sum(ghcs00),to_char(ad_ID0000) from VW_SF_GHLSXX B where (B.YBMC00 = as_ybmc00 or as_ybmc00 ='所有病人') and B.CZRQ00 >= as_KSRQ00 and B.CZRQ00 <= as_JSRQ00 and B.CZRQ00||B.CZSJ00 >= as_KSRQ00||as_KSSJ00 and B.CZRQ00||B.CZSJ00 <= as_JSRQ00||as_JSSJ00 and not exists (select 1 from BM_YYSFTJ where ID0000 = ad_ID0000 and BM_YYSFTJ.BMBH00=B.GHKS00 ) GROUP BY ghks00,to_char(ad_ID0000); --合计增加卫材 update BM_YYSFTJ set XMHJ00=nvl(XMHJ00,0)+nvl(SFXM98,0) where ID0000 = ad_ID0000; --添加一条总计行 Insert into BM_YYSFTJ(ID0000,BMBH00,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,SFXM98,SFXM99,XMHJ00) select ad_ID0000,999999999,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(SFXM98),SUM(SFXM99),sum(XMHJ00) from BM_YYSFTJ where ID0000 = ad_ID0000; commit; EXCEPTION WHEN OTHERS THEN as_YHMSG0:='出错原因不详,请记录此信息并和系统管理员联系！'; as_SYSMSG:=substr(SQLERRM||'执行存储过程错误:SP_SF_MZKSSR_RBB000('||as_KSRQ00||','||as_KSSJ00||','||as_JSRQ00||','|| as_JSSJ00||','||as_KSMC00||'ad_ID0000,as_YHMSG0,as_SYSMSG',1,150); ROLLBACK; END; / -- End of DDL Script for Procedure SD_HOSPITAL.SP_SF_MZKSSR_RBB000