create or replace procedure SP_ZH_CYBRBB ( V_FLAG00 in number default 0 ) as -- MODIFICATION HISTORY -- Person Date Comments -- SECHEN 2008.07.18 not in 改为not exists -- chenqw 2009.07.03 增加无麻醉统计 -- zhr 2009.08.15 修正当V_FLAG00='1' v_maxnum没有初始值错误 -- chenqw 2009.11.24 当V_FLAG00='1' v_maxnum改为5000 -- zhanghr 2011.09.28 增加出院病人分科费用 -- zhanghr 2011.10.07 ZH_CYBRBB增加KJYWPZ,KJYWJE,DDD000等字段 -- zhanghr 2011.11.01 统计时改RYRQ00为SJRYRQ -- jinfl 2012.02.09 统计增加中草药药费 ZHCX-20120131-001 -- linzy 2014.06.06 修改药品收入的统计方式 SF_FYMX00.SFLB00 为3 by YZCX9-20140604-001 -- qks 2017.07.06 修改错误的统计方式; -- --------- -------------- ---------------------------------------------------------------------------- v_ZFY000 number(10,2); v_YPF000 NUMBER(10,2); v_ZCYYPF number(10,2); v_WMZZFY number(10,2);--无麻醉总费用 v_WMZYPF number(10,2);--无麻醉药品费 v_MZF000 number(10,2);--麻醉总费用 v_MZYPF0 number(10,2);--麻醉药品费 V_KJYWPZ number(10) ;--抗菌药物品种 V_KJYWJE number(12,4);--抗菌药物金额 V_DDD000 number(12,4);--DDD值 v_maxnum NUMBER(5); V_MZKSBH BM_BMBM00.BMBH00%type; --麻醉科室编号 Vqsrq00 char(8); --起始日期 v_bz0000 char(1); Vcounter number(10); --计数器变量 begin --表示统计V_FLAG00='1'正式统计 ='0'表示临时统计 --删除今日数据 BZ0000='0'表示过程状态 '2'表示当天统计当天的数据 delete ZH_CYBRBB where BZ0000<>'1';--当天统计当天出院的 delete ZH_CYFKFY where BZ0000<>'1'; select nvl(max(BMBH00),-1) into V_MZKSBH from vw_bm_mzbm00; SP_ZY_XZDQKS(to_char(sysdate-1,'yyyymmdd'),to_char(sysdate,'yyyymmdd'),'N'); select nvl(to_char(to_date(max(CYRQ00),'yyyymmdd')+1,'yyyymmdd'),to_char(sysdate,'yyyy')||'0101') into Vqsrq00 from ZH_CYBRBB; insert into ZH_CYBRBB(ZYID00,YPF000,ZFY000,CYRQ00,BZ0000) select ZYID00,0 YPF000,0 ZFY000,JSRQ00,'0' BZ0000 from BQ_BRLDXX A --2008.07.18 sechen ZYID00 not in (select ZYID00 from ZH_CYBRBB) 改为 not exists (select ZYID00 from ZH_CYBRBB B where A.zyid00=b.zyid00) where JSZT00='出院' AND YXBZ00='1' and JSRQ00 between Vqsrq00 and decode(V_FLAG00,1,to_char(sysdate-1,'yyyymmdd'),to_char(sysdate,'yyyymmdd')) and not exists (select ZYID00 from ZH_CYBRBB B where A.zyid00=b.zyid00); commit; declare cursor c_zyid00 is select ZYID00,BZ0000,CYRQ00 from ZH_CYBRBB where BZ0000='0' order by ZYID00; begin for v_zyid00 in c_zyid00 loop select nvl(sum(decode(A.SFLB00,'3',A.HJJE00,0)),0), nvl(sum(a.HJJE00),0), nvl(sum(decode(a.ZXKS00,V_MZKSBH,a.HJJE00,decode(a.KDKS00,V_MZKSBH,a.HJJE00,0))),0), nvl(sum(decode(A.ZXKS00,V_MZKSBH,decode(A.SFLB00,'3',A.HJJE00,0),decode(A.KDKS00,V_MZKSBH,decode(A.SFLB00,'3',A.HJJE00,0),0))),0), --nvl(sum(decode(C.LBBH00,'2',A.HJJE00,0)),0) nvl(sum(decode(A.SFLB00,'3',decode(a.ZYFPID,3,A.HJJE00,0),0)),0) into V_YPF000,V_ZFY000,v_MZF000,v_MZYPF0,v_ZCYYPF from ZY_FYMX00 a where a.ZYID00=v_zyid00.ZYID00; select count(distinct YPNM00),nvl(sum(round(YPZSL0*LSDJ00,2)),0),nvl(sum(round(YPZSL0*ZHL000/DDD000,2)),0) into V_KJYWPZ,V_KJYWJE,V_DDD000 from VW_YK_KSSJB0_ZY where ZYID00=V_zyid00.ZYID00; update ZH_CYBRBB a set ZFY000=v_ZFY000,YPF000=v_YPF000, WMZZFY=v_ZFY000-v_MZF000,WMZYPF=v_YPF000-v_MZYPF0,MZF000=v_MZF000,MZYPF0=v_MZYPF0, KJYWPZ=V_KJYWPZ,KJYWJE=V_KJYWJE, DDD000=V_DDD000, ZYTS00=(select greatest(to_date(SJCYRQ,'YYYYMMDD')-to_date(SJRYRQ,'YYYYMMDD'),1) from ZY_BRXXB0 where ZYID00=a.ZYID00), CYBQ00=(select BQH000 from BQ_BRLDXX where YXBZ00='1' and JSZT00='出院' and ZYID00=a.ZYID00), CYKS00=(select KSH000 from BQ_BRLDXX where YXBZ00='1' and JSZT00='出院' and ZYID00=a.ZYID00), ZCYYPF=v_ZCYYPF where ZYID00=V_zyid00.ZYID00 and BZ0000='0'; --增加分科费用表 insert into ZH_CYFKFY (ZYID00,BQH000,KSH000,YPF000,ZFY000,CYRQ00,BZ0000,WMZZFY,WMZYPF,MZF000,MZYPF0,ZCYYPF) select v_zyid00.ZYID00,A.KDBQ00,A.BRDQKS, nvl(sum(decode(A.SFLB00,'3',A.HJJE00,0)),0),nvl(sum(A.HJJE00),0),v_zyid00.CYRQ00,'0',0,0, nvl(sum(decode(A.ZXKS00,V_MZKSBH,A.HJJE00,decode(A.KDKS00,V_MZKSBH,A.HJJE00,0))),0), nvl(sum(decode(A.ZXKS00,V_MZKSBH,decode(A.SFLB00,'3',A.HJJE00,0),decode(A.KDKS00,V_MZKSBH,decode(A.SFLB00,'3',A.HJJE00,0),0))),0), --nvl(sum(decode(C.LBBH00,'2',A.HJJE00,0)),0) nvl(sum(decode(A.SFLB00,'3',decode(a.ZYFPID,3,A.HJJE00,0),0)),0) from ZY_FYMX00 a where a.ZYID00=v_zyid00.ZYID00 group by A.KDBQ00,A.BRDQKS; update ZH_CYFKFY set WMZZFY=ZFY000-MZF000,WMZYPF=YPF000-MZYPF0 where ZYID00= v_zyid00.ZYID00; --当天统计的数据，需要删除重新统计 update ZH_CYBRBB set BZ0000=decode(CYRQ00,to_char(sysdate,'YYYYMMDD'),'2','1') where ZYID00= v_zyid00.ZYID00; update ZH_CYFKFY set BZ0000=decode(CYRQ00,to_char(sysdate,'YYYYMMDD'),'2','1') where ZYID00= v_zyid00.ZYID00; commit; end loop; end; end; / --%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% CREATE OR REPLACE PROCEDURE "SP_ZH_QYSLTJ_PJ0000" ( 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 DEFAULT '0', --费用发生类别：'0':全部 '1':自付 '2': 记帐 '3': 减免 as_ybmc00 in char DEFAULT '所有病人', --医保名称 as_YYID00 in char DEFAULT '0', --医院ID >0 各分院 =0全部 as_MZZYBZ in char DEFAULT '0', --门诊住院标志。0：全院，1：门诊,2：住院 ad_ID0000 OUT NUMBER, --统计报表对应的ID as_YHMSG0 OUT VARCHAR, --存储过程提示的错误信息 as_SYSMSG OUT VARCHAR --系统提示的错误信息 ) as -- MODIFICATION HISTORY -- Person Date Comments -- SECHEN 2008.07.18 新增 -- SECHEN 2008.12.18 增加上报科室 -- SECHEN 2008.12.26 过滤部门编码表中没有的科室 -- yangy 2009.03.02 宁德市一 不需要合计上报科室统计 -- jinfl 2011.07.15 增加门诊住院的标志。 --说明： --sechen BM_YYSFTJ 中的 YPHJ00 存放上报科室ID SBKSID ； BMMC00 存放上报科室名称 SBKSMC 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_SBKSID BM_BMBM00.SBKSID%TYPE; --上报科室ID ls_SBKSMC BM_SBKS00.SBKSMC%TYPE; --上报科室名称 ls_GHRC00 NUMBER(10); ls_Count0 NUMBER(10); ls_SBKSZJ char(1); --财务信息管理查询全院收入统计是否添加各上报科室总计。Y 是 N否，默认为Y CURSOR CUR_SF_MZKSSR_KDFYMX IS SELECT H.BH0000,decode(as_TJLB00,'0',A.KDKS00,'1',A.ZXKS00) KSBH00,sum(A.HJJE00),sum(A.ZFJE00),sum(A.JZJE00),sum(A.GFJE00) from SF_FYMX00 A,BM_BMBM00 B,BM_BRXXB0 E,BM_FPXM00 H,ic_ybbrlb d where A.KDKS00 = B.BMBH00 and a.BRID00 = E.BRID00 and A.MZFPID = H.FPXMID and d.fbbh00=e.fbbh00 and d.yblb00=e.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 (d.ybmc00 = as_ybmc00 or as_ybmc00 ='所有病人') --2008.12.26 sechen 过滤那些BM_BMBM00表中没有的科室 and a.kdks00 in(select bmbh00 from bm_bmbm00) and a.zxks00 in(select bmbh00 from bm_bmbm00) and (as_MZZYBZ='0' or as_MZZYBZ='1') group by H.BH0000,decode(as_TJLB00,'0',A.KDKS00,'1',A.ZXKS00) union all select F.BH0000,decode(as_TJLB00,'0',A.KDKS00,'1',A.ZXKS00) KSBH00,sum(A.HJJE00),sum(A.ZFJE00),sum(A.JZJE00),sum(A.GFJE00) FROM ZY_FYMX00 A,BM_FPXM00 F,BM_BRXXB0 D,IC_YBBRLB G,BM_BMBM00 H where A.KDKS00 = H.BMBH00 and A.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 (G.YBMC00=as_YBMC00 or as_YBMC00 = '所有病人') --2008.12.26 sechen 过滤那些BM_BMBM00表中没有的科室 and a.kdks00 in(select bmbh00 from bm_bmbm00) and a.zxks00 in(select bmbh00 from bm_bmbm00) and (as_MZZYBZ='0' or as_MZZYBZ='2') group by F.BH0000,decode(as_TJLB00,'0',A.KDKS00,'1',A.ZXKS00); BEGIN select nvl(trim(max(value0)),'Y') into ls_SBKSZJ from xt_xtcs00 where name00='ZH_CWXX_SBKSZJ'; select SQ_BM_YYSFTJ_ID0000.nextval into ad_ID0000 from dual; OPEN CUR_SF_MZKSSR_KDFYMX; 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; 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 --2008.12.18 sechen 提取上报科室ID YPHJ00 上报科室名称 bmmc00 select count(*) into ls_Count0 from bm_bmbm00 where bmbh00=ls_bmbh00; select nvl(sbksid,9999) into ls_sbksid from bm_bmbm00 where bmbh00=ls_bmbh00; if (ls_Count0 = 0) or (ls_sbksid = 9999) then ls_sbksmc:='其它'; else select sbksmc into ls_sbksmc from bm_sbks00 where sbksid=ls_sbksid; end if; SP_EXECUTE_SQL('Insert into BM_YYSFTJ(BMBH00,SFXM'||(ls_BH0000)||',ID0000,XMHJ00,YPHJ00,BMMC00) values('||to_char(ls_BMBH00)||','||to_char(ls_HJJE00)||','||to_char(ad_ID0000)||','||to_char(ls_HJJE00)||','||ls_sbksid||','''||ls_sbksmc||''')'); end if; END LOOP; CLOSE CUR_SF_MZKSSR_KDFYMX; --增加挂号人次 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' and (as_MZZYBZ='0' or as_MZZYBZ='1') ) where ID0000 = ad_ID0000; --2008.12.25 sechen 添加各上报科室总计 if ls_SBKSZJ='Y' then Insert into BM_YYSFTJ(ID0000,YPHJ00,BMMC00,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,SFXM31,SFXM32,SFXM33,SFXM34,SFXM35,SFXM36,SFXM37,SFXM38,SFXM39,SFXM40, SFXM41,SFXM42,SFXM43,SFXM44,SFXM45,SFXM46,SFXM47,SFXM48,SFXM49,SFXM50,SFXM51,SFXM52,SFXM53,SFXM54,SFXM55,SFXM56,SFXM57,SFXM58,SFXM59,SFXM60, SFXM61,SFXM62,SFXM63,SFXM64,SFXM65,SFXM66,SFXM67,SFXM68,SFXM69,SFXM70,SFXM71,SFXM72,SFXM73,SFXM74,SFXM75,SFXM76,SFXM77,SFXM78,SFXM79,SFXM80, SFXM81,SFXM82,SFXM83,SFXM84,SFXM85,SFXM86,SFXM87,SFXM88,SFXM89,SFXM90,SFXM91,SFXM92,SFXM93,SFXM94,SFXM95,SFXM96,SFXM97,SFXM98,SFXM99,XMHJ00) select ad_ID0000,YPHJ00,BMMC00,-999999998,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(SFXM31),sum(SFXM32),sum(SFXM33),sum(SFXM34),sum(SFXM35),sum(SFXM36),sum(SFXM37),sum(SFXM38),sum(SFXM39),sum(SFXM40),sum(SFXM41),sum(SFXM42),sum(SFXM43), sum(SFXM44),sum(SFXM45),sum(SFXM46),sum(SFXM47),sum(SFXM48),sum(SFXM49),sum(SFXM50),sum(SFXM51),sum(SFXM52),sum(SFXM53),sum(SFXM54),sum(SFXM55),sum(SFXM56),sum(SFXM57),sum(SFXM58), sum(SFXM59),sum(SFXM60),sum(SFXM61),sum(SFXM62),sum(SFXM63),sum(SFXM64),sum(SFXM65),sum(SFXM66),sum(SFXM67),sum(SFXM68),sum(SFXM69),sum(SFXM70),sum(SFXM71),sum(SFXM72),sum(SFXM73), sum(SFXM74),sum(SFXM75),sum(SFXM76),sum(SFXM77),sum(SFXM78),sum(SFXM79),sum(SFXM80),sum(SFXM81),sum(SFXM82),sum(SFXM83),sum(SFXM84),sum(SFXM85),sum(SFXM86),sum(SFXM87),sum(SFXM88), sum(SFXM89),sum(SFXM90),sum(SFXM91),sum(SFXM92),sum(SFXM93),sum(SFXM94),sum(SFXM95),sum(SFXM96),sum(SFXM97),sum(SFXM98),sum(SFXM99),sum(XMHJ00) from BM_YYSFTJ where ID0000 = ad_ID0000 group by yphj00,BMMC00; end if; --添加一条总计行 Insert into BM_YYSFTJ(ID0000,YPHJ00,BMMC00,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,SFXM99,XMHJ00) select ad_ID0000,-999999999,'全院',-999999998,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(SFXM99),sum(XMHJ00) from BM_YYSFTJ where ID0000 = ad_ID0000 --sechen 过滤重复的统计值 and bmbh00 not in(-999999999,-999999998); --2008.12.25 sechen 把有具体科室的名称的首列上报科室名去除，美观布局 update bm_yysftj set bmmc00='' where ID0000 = ad_ID0000 and bmbh00<>-999999998; commit; EXCEPTION WHEN OTHERS THEN as_YHMSG0:='出错原因不详,请记录此信息并和系统管理员联系！'; as_SYSMSG:=substr(SQLERRM||'执行存储过程错误:SP_ZH_QYSLTJ('||as_KSRQ00||','||as_KSSJ00||','||as_JSRQ00||','|| as_JSSJ00||','||as_KSMC00||'ad_ID0000,as_YHMSG0,as_SYSMSG',1,150); ROLLBACK; END; / ------------------------------------------------------------------------------------------------------ CREATE OR REPLACE PROCEDURE "SP_ZH_QYSLTJ_HS0000" ( 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 DEFAULT '0', --费用发生类别：'0':全部 '1':自付 '2': 记帐 '3': 减免 as_ybmc00 in char DEFAULT '所有病人', --医保名称 as_YYID00 in char DEFAULT '0', --医院ID >0 各分院 =0全部 as_MZZYBZ in char DEFAULT '0', --门诊住院标志。0：全院，1：门诊,2：住院 ad_ID0000 OUT NUMBER, --统计报表对应的ID as_YHMSG0 OUT VARCHAR, --存储过程提示的错误信息 as_SYSMSG OUT VARCHAR --系统提示的错误信息 ) as -- MODIFICATION HISTORY -- Person Date Comments -- SECHEN 2008.07.18 新增 -- SECHEN 2008.12.18 增加上报科室 -- SECHEN 2008.12.26 过滤部门编码表中没有的科室 -- yangy 2009.03.02 宁德市一 不需要合计上报科室统计 -- jinfl 2011.07.15 增加门诊住院标志 --说明： --sechen BM_YYSFTJ 中的 YPHJ00 存放上报科室ID SBKSID ； BMMC00 存放上报科室名称 SBKSMC 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_SBKSID BM_BMBM00.SBKSID%TYPE; --上报科室ID ls_SBKSMC BM_SBKS00.SBKSMC%TYPE; --上报科室名称 ls_Count0 NUMBER(10); ls_SBKSZJ char(1); --财务信息管理查询全院收入统计是否添加各上报科室总计。Y 是 N否，默认为Y CURSOR CUR_SF_KDHSSR_FYMX00 IS SELECT K.XSXLH0,decode(as_TJLB00,'0',A.KDKS00,'1',A.ZXKS00) KSBH00,sum(A.HJJE00),sum(A.ZFJE00),sum(A.JZJE00),sum(A.GFJE00) from SF_FYMX00 A,BM_BMBM00 B,BM_BRXXB0 E,ic_ybbrlb d,BM_YYHSXM K where A.KDKS00 = B.BMBH00 and A.BRID00 = E.BRID00 and d.yblb00=e.yblb00 and d.fbbh00=e.fbbh00 and A.HSXMID = K.HSXMID and K.FLAG00=1 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 (d.ybmc00 = as_ybmc00 or as_ybmc00 ='所有病人') --2008.12.26 sechen 过滤那些BM_BMBM00表中没有的科室 and a.kdks00 in(select bmbh00 from bm_bmbm00) and a.zxks00 in(select bmbh00 from bm_bmbm00) and (as_MZZYBZ='0' or as_MZZYBZ='1') group by K.XSXLH0,decode(as_TJLB00,'0',A.KDKS00,'1',A.ZXKS00) union all --住院使用二级核算 select K.XSXLH0,decode(as_TJLB00,'0',A.KDKS00,'1',A.ZXKS00) KSBH00,sum(A.HJJE00),sum(A.ZFJE00),sum(A.JZJE00),sum(A.GFJE00) FROM ZY_FYMX00 A,VW_BM_EJHSXM F,BM_BRXXB0 D,IC_YBBRLB G,BM_BMBM00 H,BM_YYHSXM K where A.KDKS00 = H.BMBH00 and A.HSXMID = F.HSXMID and D.BRID00 = A.BRID00 and D.FBBH00 = G.FBBH00 and D.YBLB00 = G.YBLB00 and F.HSXMID = K.HSXMID and K.FLAG00=1 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 (G.YBMC00=as_YBMC00 or as_YBMC00 = '所有病人') --2008.12.26 sechen 过滤那些BM_BMBM00表中没有的科室 and a.kdks00 in(select bmbh00 from bm_bmbm00) and a.zxks00 in(select bmbh00 from bm_bmbm00) and (as_MZZYBZ='0' or as_MZZYBZ='2') --2008.7.18 sechen 二级核算，暂用参数中的K.XLH000 group by K.XSXLH0,decode(as_TJLB00,'0',A.KDKS00,'1',A.ZXKS00); --group by F.EJHSBH,decode(as_TJLB00,'0',A.KDKS00,'1',A.ZXKS00); BEGIN select nvl(trim(max(value0)),'Y') into ls_SBKSZJ from xt_xtcs00 where name00='ZH_CWXX_SBKSZJ'; select SQ_BM_YYSFTJ_ID0000.nextval into ad_ID0000 from dual; OPEN CUR_SF_KDHSSR_FYMX00; LOOP FETCH CUR_SF_KDHSSR_FYMX00 INTO ls_BH0000,ls_BMBH00,ls_HJJE00,ls_ZFJE00,ls_JZJE00,ls_GFJE00; EXIT WHEN CUR_SF_KDHSSR_FYMX00%NOTFOUND; select count(*) into ls_Count0 from BM_YYSFTJ where ID0000=ad_ID0000 and BMBH00=ls_BMBH00; 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 --2008.12.18 sechen 提取上报科室ID YPHJ00 上报科室名称 bmmc00 select count(*) into ls_Count0 from bm_bmbm00 where bmbh00=ls_bmbh00; select nvl(sbksid,9999) into ls_sbksid from bm_bmbm00 where bmbh00=ls_bmbh00; if (ls_Count0 = 0) or (ls_sbksid = 9999) then ls_sbksmc:='其它'; else select sbksmc into ls_sbksmc from bm_sbks00 where sbksid=ls_sbksid; end if; SP_EXECUTE_SQL('Insert into BM_YYSFTJ(BMBH00,SFXM'||(ls_BH0000)||',ID0000,XMHJ00,YPHJ00,BMMC00) values('||to_char(ls_BMBH00)||','||to_char(ls_HJJE00)||','||to_char(ad_ID0000)||','||to_char(ls_HJJE00)||','||ls_sbksid||','''||ls_sbksmc||''')'); end if; END LOOP; CLOSE CUR_SF_KDHSSR_FYMX00; --增加挂号人次 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' and (as_MZZYBZ='0' or as_MZZYBZ='1') ) where ID0000 = ad_ID0000; --添加各上报科室总计 if ls_SBKSZJ='Y' then Insert into BM_YYSFTJ(ID0000,YPHJ00,BMMC00,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,SFXM31,SFXM32,SFXM33,SFXM34,SFXM35,SFXM36,SFXM37,SFXM38,SFXM39,SFXM40, SFXM41,SFXM42,SFXM43,SFXM44,SFXM45,SFXM46,SFXM47,SFXM48,SFXM49,SFXM50,SFXM51,SFXM52,SFXM53,SFXM54,SFXM55,SFXM56,SFXM57,SFXM58,SFXM59,SFXM60, SFXM61,SFXM62,SFXM63,SFXM64,SFXM65,SFXM66,SFXM67,SFXM68,SFXM69,SFXM70,SFXM71,SFXM72,SFXM73,SFXM74,SFXM75,SFXM76,SFXM77,SFXM78,SFXM79,SFXM80, SFXM81,SFXM82,SFXM83,SFXM84,SFXM85,SFXM86,SFXM87,SFXM88,SFXM89,SFXM90,SFXM91,SFXM92,SFXM93,SFXM94,SFXM95,SFXM96,SFXM97,SFXM98,SFXM99,XMHJ00) select ad_ID0000,YPHJ00,BMMC00,-999999998,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(SFXM31),sum(SFXM32),sum(SFXM33),sum(SFXM34),sum(SFXM35),sum(SFXM36),sum(SFXM37),sum(SFXM38),sum(SFXM39),sum(SFXM40),sum(SFXM41),sum(SFXM42),sum(SFXM43), sum(SFXM44),sum(SFXM45),sum(SFXM46),sum(SFXM47),sum(SFXM48),sum(SFXM49),sum(SFXM50),sum(SFXM51),sum(SFXM52),sum(SFXM53),sum(SFXM54),sum(SFXM55),sum(SFXM56),sum(SFXM57),sum(SFXM58), sum(SFXM59),sum(SFXM60),sum(SFXM61),sum(SFXM62),sum(SFXM63),sum(SFXM64),sum(SFXM65),sum(SFXM66),sum(SFXM67),sum(SFXM68),sum(SFXM69),sum(SFXM70),sum(SFXM71),sum(SFXM72),sum(SFXM73), sum(SFXM74),sum(SFXM75),sum(SFXM76),sum(SFXM77),sum(SFXM78),sum(SFXM79),sum(SFXM80),sum(SFXM81),sum(SFXM82),sum(SFXM83),sum(SFXM84),sum(SFXM85),sum(SFXM86),sum(SFXM87),sum(SFXM88), sum(SFXM89),sum(SFXM90),sum(SFXM91),sum(SFXM92),sum(SFXM93),sum(SFXM94),sum(SFXM95),sum(SFXM96),sum(SFXM97),sum(SFXM98),sum(SFXM99),sum(XMHJ00) from BM_YYSFTJ where ID0000 = ad_ID0000 group by yphj00,BMMC00; end if; --添加一条总计行 Insert into BM_YYSFTJ(ID0000,YPHJ00,BMMC00,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,SFXM31,SFXM32,SFXM33,SFXM34,SFXM35,SFXM36,SFXM37,SFXM38,SFXM39,SFXM40, SFXM41,SFXM42,SFXM43,SFXM44,SFXM45,SFXM46,SFXM47,SFXM48,SFXM49,SFXM50,SFXM51,SFXM52,SFXM53,SFXM54,SFXM55,SFXM56,SFXM57,SFXM58,SFXM59,SFXM60, SFXM61,SFXM62,SFXM63,SFXM64,SFXM65,SFXM66,SFXM67,SFXM68,SFXM69,SFXM70,SFXM71,SFXM72,SFXM73,SFXM74,SFXM75,SFXM76,SFXM77,SFXM78,SFXM79,SFXM80, SFXM81,SFXM82,SFXM83,SFXM84,SFXM85,SFXM86,SFXM87,SFXM88,SFXM89,SFXM90,SFXM91,SFXM92,SFXM93,SFXM94,SFXM95,SFXM96,SFXM97,SFXM98,SFXM99,XMHJ00) select ad_ID0000,-999999999,'全院',-999999998,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(SFXM31),sum(SFXM32),sum(SFXM33),sum(SFXM34),sum(SFXM35),sum(SFXM36),sum(SFXM37),sum(SFXM38),sum(SFXM39),sum(SFXM40),sum(SFXM41),sum(SFXM42),sum(SFXM43), sum(SFXM44),sum(SFXM45),sum(SFXM46),sum(SFXM47),sum(SFXM48),sum(SFXM49),sum(SFXM50),sum(SFXM51),sum(SFXM52),sum(SFXM53),sum(SFXM54),sum(SFXM55),sum(SFXM56),sum(SFXM57),sum(SFXM58), sum(SFXM59),sum(SFXM60),sum(SFXM61),sum(SFXM62),sum(SFXM63),sum(SFXM64),sum(SFXM65),sum(SFXM66),sum(SFXM67),sum(SFXM68),sum(SFXM69),sum(SFXM70),sum(SFXM71),sum(SFXM72),sum(SFXM73), sum(SFXM74),sum(SFXM75),sum(SFXM76),sum(SFXM77),sum(SFXM78),sum(SFXM79),sum(SFXM80),sum(SFXM81),sum(SFXM82),sum(SFXM83),sum(SFXM84),sum(SFXM85),sum(SFXM86),sum(SFXM87),sum(SFXM88), sum(SFXM89),sum(SFXM90),sum(SFXM91),sum(SFXM92),sum(SFXM93),sum(SFXM94),sum(SFXM95),sum(SFXM96),sum(SFXM97),sum(SFXM98),sum(SFXM99),sum(XMHJ00) from BM_YYSFTJ where ID0000 = ad_ID0000 and bmbh00 not in(-999999999,-999999998); --2008.12.25 sechen 把有具体科室的名称的首列上报科室名去除，美观布局 update bm_yysftj set bmmc00='' where ID0000 = ad_ID0000 and bmbh00<>-999999998; commit; EXCEPTION WHEN OTHERS THEN as_YHMSG0:='出错原因不详,请记录此信息并和系统管理员联系！'; as_SYSMSG:=substr(SQLERRM||'执行存储过程错误:SP_ZH_QYSLTJ_HS0000('||as_KSRQ00||','||as_KSSJ00||','||as_JSRQ00||','|| as_JSSJ00||','||as_KSMC00||'ad_ID0000,as_YHMSG0,as_SYSMSG',1,150); ROLLBACK; END; / ---------------------------------------------------------------------------------------------------------