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 --系统提示的错误信息 ) -- MODIFICATION HISTORY -- Person Date Comments -- qks 2012.03.05 要求有关药费统计到开单科室，同时增加一列“卫材”（单列出来，不包含在"合计"中）。by MZSF-20120305-001 -- qks 2012.11.05 修改药费对应的发票项目；增加三个购进金额字段:SFXM91购进西药费,SFXM92购进成药费,SFXM93购进草药费 -- qks 2012.11.30 修改挂号；默认时间 -- qks 2012.12.04 使用会计项目统计 -- zhangwz 2013.05.03 对于挂号次数的统计没有使用上时间，更改之. -- jlg 2017.12.25 整理过程 MZSF9-20171220-001 -- qks 2019.07.15 后台按会计统计改为按发票统计(前台实际要求按发票统计,如:门诊科室收入日报表); for MZSF9-20190715-008 -- qks 2019.07.16 参数SF_YFZXTJKD=Y,要求有关药费统计到开单科室，同时增加一列“卫材”; for MZSF9-20190716-001 -- linzetao 2020.09.16 修正统计类别为执行科室时合计不正确问题; 当参数SF_YFZXTJKD=N时, sflb00=‘2’未按原项目统计问题 MZSF9-20200909-004 -- linzetao 2020.12.28 增加统计预约人数SFXM97 MZSF9-20201218-001 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; ls_GJJXYF SF_FYMX00.HJJE00%type; ls_GJJCYF SF_FYMX00.HJJE00%type; ls_GJJZYF SF_FYMX00.HJJE00%type; SF_KJGHFP XT_XTCS00.VALUE0%type; --后台按会计统计是否改为按发票统计(前台实际要求按发票统计,如:门诊科室收入日报表),0否1是,默认1; ls_XMHJ00 SF_FYMX00.HJJE00%type; SF_ZXTJKD char(1); ls_GHRC00 number(10); ls_Count0 number(10); cursor CUR_SF_MZKSSR_KDFYMX is select decode(SF_KJGHFP,'1',M.BH0000,H.BH0000) BH0000,A.KDKS00,sum(A.HJJE00),sum(A.ZFJE00),sum(A.JZJE00),sum(A.GFJE00), sum(decode(A.MZFPID,1,nvl(round(A.GJDJ00*A.XMSL00,2),0),0)) as GJJXYF, sum(decode(A.MZFPID,2,nvl(round(A.GJDJ00*A.XMSL00,2),0),0)) as GJJCYF, sum(decode(A.MZFPID,3,nvl(round(A.GJDJ00*A.XMSL00,2),0),0)) as GJJZYF from SF_FYMX00 A,BM_YYKJXM H,BM_FPXM00 M --BM_YYSFXM G, where a.KJXMID = H.KJXMID --a.SFLB00<>'3' and and a.MZFPID = M.FPXMID and a.CZRQ00 >= as_KSRQ00 and a.CZRQ00 <= as_JSRQ00 and a.CZRQ00||A.CZSJ00 >= as_KSRQ00||nvl(as_KSSJ00,'00:00:00') and a.CZRQ00||A.CZSJ00 <= as_JSRQ00||nvl(as_JSSJ00,'23:59:59') and (as_KSMC00 ='所有科室' or exists (select 1 from BM_BMBM00 B where B.BMMC00 = as_KSMC00 and A.KDKS00 = B.BMBH00)) and (as_YBMC00 ='所有病人' or exists(select 1 from ic_ybbrlb d where d.fbbh00 = A.brfb00 and d.yblb00 = A.yblb00 and d.ybmc00 = as_YBMC00)) and ((as_SFYB00='0') or (as_SFYB00='1' and A.JFLBID=0) or (as_SFYB00='2' and A.JFLBID <> 0) or (as_SFYB00='3')) and (as_YYID00 = '0' or exists (select 1 from BM_BMBM00 B where A.KDKS00 = B.BMBH00 and B.YYID00 = as_YYID00)) group by decode(SF_KJGHFP,'1',M.BH0000,H.BH0000),A.KDKS00; cursor CUR_SF_MZKSSR_ZXFYMX is select decode(SF_KJGHFP,'1',decode(A.SFLB00,'2',decode(SF_ZXTJKD,'Y','98',M.BH0000),M.BH0000),H.BH0000) BH0000,decode(SF_ZXTJKD,'Y',decode(a.SFLB00,'3',A.KDKS00,A.ZXKS00),A.ZXKS00), sum(A.HJJE00),sum(A.ZFJE00),sum(A.JZJE00),sum(A.GFJE00), sum(decode(A.MZFPID,1,nvl(round(A.GJDJ00*A.XMSL00,2),0),0)) as GJJXYF, sum(decode(A.MZFPID,2,nvl(round(A.GJDJ00*A.XMSL00,2),0),0)) as GJJCYF, sum(decode(A.MZFPID,3,nvl(round(A.GJDJ00*A.XMSL00,2),0),0)) as GJJZYF from SF_FYMX00 A,BM_YYKJXM H,BM_FPXM00 M where a.KJXMID = H.KJXMID --a.SFLB00<>'3' and and a.MZFPID = M.FPXMID and a.CZRQ00 >= as_KSRQ00 and a.CZRQ00 <= as_JSRQ00 and a.CZRQ00||A.CZSJ00 >= as_KSRQ00||nvl(as_KSSJ00,'00:00:00') and a.CZRQ00||A.CZSJ00 <= as_JSRQ00||nvl(as_JSSJ00,'23:59:59') and (as_KSMC00 ='所有科室' or exists (select 1 from BM_BMBM00 B where B.BMMC00 = as_KSMC00 and A.KDKS00 = B.BMBH00)) and (as_YBMC00 ='所有病人' or exists(select 1 from ic_ybbrlb d where d.fbbh00 = A.brfb00 and d.yblb00 = A.yblb00 and d.ybmc00 = as_YBMC00)) and ((as_SFYB00='0') or (as_SFYB00='1' and A.JFLBID=0) or (as_SFYB00='2' and A.JFLBID <> 0) or (as_SFYB00='3')) and (as_YYID00 = '0' or exists (select 1 from BM_BMBM00 B where A.KDKS00 = B.BMBH00 and B.YYID00 = as_YYID00)) group by decode(SF_KJGHFP,'1',decode(A.SFLB00,'2',decode(SF_ZXTJKD,'Y','98',M.BH0000),M.BH0000),H.BH0000),decode(SF_ZXTJKD,'Y',decode(a.SFLB00,'3',A.KDKS00,A.ZXKS00),A.ZXKS00); begin select nvl(max(substrb(value0,1,1)),'N') into SF_ZXTJKD from XT_XTCS00 where name00='SF_YFZXTJKD'; --后台按会计统计是否改为按发票统计(前台实际要求按发票统计,如:门诊科室收入日报表),0否1是,默认1 --select nvl(max(substrb(value0,1,1)),'1') into SF_KJGHFP from XT_XTCS00 where name00='SF_KJGHFP'; SF_KJGHFP := '1'; 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,ls_GJJXYF,ls_GJJCYF,ls_GJJZYF; 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,ls_GJJXYF,ls_GJJCYF,ls_GJJZYF; 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 --2019.07.16 卫材”是独立列,金额不统计在合计中 if SF_ZXTJKD='Y' and as_TJLB00 = '1' and ls_BH0000 ='98' then ls_XMHJ00 := 0; else ls_XMHJ00 := ls_HJJE00; end if; 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) ||',XMHJ00=NVL(XMHJ00,0)+'||to_char(ls_XMHJ00) ||',SFXM91=NVL(SFXM91,0)+'||to_char(ls_GJJXYF) ||',SFXM92=NVL(SFXM92,0)+'||to_char(ls_GJJCYF) ||',SFXM93=NVL(SFXM93,0)+'||to_char(ls_GJJZYF) ||' 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,SFXM91,SFXM92,SFXM93,XMHJ00) values(' ||to_char(ls_BMBH00)||',' ||to_char(ls_HJJE00)||',' ||to_char(ad_ID0000)||',' ||to_char(ls_GJJXYF)||',' ||to_char(ls_GJJCYF)||',' ||to_char(ls_GJJZYF)||',' ||to_char(ls_XMHJ00)||')'); 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 sum(GHCS00) from VW_SF_GHLSXX where GHKS00=A.BMBH00 and CZRQ00 >= as_KSRQ00 and CZRQ00 <= as_JSRQ00 and CZRQ00||CZSJ00 >= as_KSRQ00||nvl(as_KSSJ00,'00:00:00') and CZRQ00||CZSJ00 <= as_JSRQ00||nvl(as_JSSJ00,'23:59:59') and (YBMC00 = as_YBMC00 or as_YBMC00 ='所有病人') ), SFXM97= (select nvl(sum(decode(YYZT00,'1',1,0)),0) from SF_YYBRB0 where YYKSBH=A.BMBH00 and YYRQ00 between as_KSRQ00 and as_JSRQ00) where ID0000 = ad_ID0000; --合计增加卫材 2019.07.16 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, SFXM91,SFXM92,SFXM93,SFXM97,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(SFXM91),sum(SFXM92),sum(SFXM93),sum(SFXM97),sum(SFXM98),sum(SFXM99),sum(XMHJ00) from BM_YYSFTJ where ID0000 = ad_ID0000; -- commit; exception when others then as_YHMSG0:='系统出错，请查看详细信息。如有不明，请与管理员联系！'||SQLERRM; 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;