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 -- qks 2017.07.08 修改错误的统计方式; for YZCX9-20170706-001 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;