CREATE OR REPLACE PROCEDURE SP_ZH_MZXXCX( V_FLAG00 in char, --报表标志 V_KSYSBZ in number default 0 ) AS C_STARTD char(8); --开始日期 C_STARTT char(8); --开始时间 C_STOPDA char(8); --结束日期 C_STOPTI char(8); --结束时间 C_STARTD_OLD char(8); C_STOPDA_OLD char(8); C_NOW000 char(8); --今天 C_ID0000 number(10); --序列号 C_IDQN00 number(10); --去年序列号 C_INSERTNM number(2); --字段 C_FLAG00 BM_ZHTJ00_TEMP00.FLAG00%type; PSQLTEXT varchar2(32767) ; --动态SQL语句 C_BSYYID varchar2(8); --用来标识医院ID，来辨别分院 C_YBLB00 varchar2(2); --用来标识医保类别 C_TJBMBH BM_BMBM00.BMBH00%type; C_TCTJBZ varchar2(10); ls_MZRBSFJSSJGHS XT_XTCS00.VALUE0%type; LS_SFKCZY char(1);--是否扣除中药金额 LS_KCOPEN number(5);--医保抗肿瘤国家谈判药品不占药占比药品目录 LS_STR000 varchar2(32767); LS_STR001 varchar2(32767); LS_STR002 varchar2(32767); LS_STR003 varchar2(32767); LS_YPBTC0 number(5); LS_PJFYC0 number(5); LS_YPPJFC number(5); LS_YPFYIN number(5); LS_YLFYIN number(5); ZH_MZRBSFBGLGHLB number(5); ZH_SFSYBRXXBGHLBTJGHRW number(5); ls_ZYFBH0 XT_XTCS00.VALUE0%type; --中药房部门编号 -- E_ex exception; --date revisor comments --2008.05.26 ZHR 算挂号数时不能包含预挂号。 --2008.06.27 SECHEN 按照医院的要求就是增加'门诊草药处方'占门诊处方的比例、'门诊成药处方'占门诊处方的比例、‘草药’占药品收入的比例、‘成药’占药品收入的比例四列。 --2008.11.13 SECHEN 由综合查询中的财务信息的门诊费用分析的字段排列是动态生成的，位置由挂号类型等决定，默认的一般是正常的挂号，再加两个 合计 其它 ,宁德由于 -- 特殊需要，再多加一个,造成前台显示错位，此处单独处理 --2008.11.24 chenyw 由于一些医院存在多个分院时，所以增加一个C_BSYYID 标识医院ID来区分不同的医院；按不同医院来统计 --2008.12.29 SECHEN 取消2008.11.13的修改，判断放到前台程序中去 --2009.01.20 chenyw 增加一个医保类别; --2009.09.15 zhr 语句优化 --2012.02.09 jinfl 增加不包含中草药的药品比率 ZHCX-20120131-001 --2013.06.13 laijg 增加指定项目费用，该费用不参与药占比，平均费用的计算 by ZHCX-20130529-001 --2015.07.10 daihq 当天挂号量和退号量增加限制条件SF_BRXXB0.GHLB00在字典表bm_ghlbb0.SFTJ00='Y' for ZHCX-20150624-001 --2015.09.17 daihq ZHCX-20150624-001修改内容增加参数ZH_MZRBSFBGLGHLB控制 for ZHCX-20150902-001 --2015.11.20 daihq 增加西药费、成药费、草药费的计算 for ZHCX-20151111-001 --2015.11.25 zhangyc 增加基药统计 by ZHCX-20151123-002 --2016.01.09 dsm 增加参数ZH_MZFXTCTJBMBH控制是否剔除体检部门费用 for ZHCX-20160108-001 --2016.01.25 daihq 增加参数ZH_MZRBSFJSSJGHS控制是否计算实际挂号数（总挂号数减去指定类别的挂号数），平均费用改成除以实际挂号数 for ZHCX-20160115-001 --2016.11.21 chenhn 实现参数ZH_QYSRTJSFKC控制是否扣除中药金额 for ZHCX-20161111-001 --2017.06.15 huangjy 增加耗材费用和耗占比 SFXM40,SFXM42 for ZHCX-20170606-002 --2018.11.03 huangjy 去掉医大附二算法，改为依据SFLB00字段判断计算材料费 for ZHCX-20181017-001 --2019.07.04 qiulf 增加参数ZH_SFSYBRXXBGHLBTJGHRW控制使用SF_BRXXB0.GHLB00来统计挂号人数 for ZHCX-20190507-002 --2019.11.13 huangjy 增加药品费用住院药占比和门诊药占比需要剔除特定的药品 for ZHCX-20191112-001 --2019.11.26 qiulf 增加医保抗肿瘤国家谈判药品不占药占比药品 ZHCX-20191126-001 --2019.12.09 qiulf 除国谈药品费还要除中草药，对整个过程进行优化，使用动态语句进行查询 ZHCX-20191209-001 --2019.12.19 dsm 增加TEMP120(中药),TEMP121(国谈) for ZHCX-20191219-001 --2020.02.11 dsm 过程输出的列与前台错乱了ZH_SFSYBRXXBGHLBTJGHRW=Y时 for ZHCX-20200205-001 --2020.03.09 huangjy 增加YS_SFZTCZYF控制，剔除特定药房草药费。中药费，除草药费用按新规则计算 for ZHCX-20200302-001 --2020.05.07 qiulf 修正ZHCX-20200302-001需求修改造成除草药外药品比率数据错误 for ZHCX-20200507-002 --2020.06.19 qiulf 修正ZH_MZRBSFJSSJGHS参数不等于N时，罗源医院去年同比无法统计的问题,删除调用SP_ZH_MZXXCX_LASTYEAR过程。 ZHCX-20200619-002 --2020.12.16 huangjy 修正今年去年比率扣除报错的问题。for ZHCX-20201215-002 --2021.01.27 qiulf 修正YS_SFZTCZYF参数启用时，除中草药外药品比率为空的问题 by ZHCX-20210127-001 --2021.02.24 qiulf 修正除中草药外药品比率计算错误的bug by ZHCX-20210224-001 --2021.02.24 qiulf 修正统计挂号人数 bug by ZHCX-20210220-001 --2021.03.22 qiulf 修正ZH_SFSYBRXXBGHLBTJGHRW=Y时,前台显示数据前移动了一格的问题 by ZHCX-20210319-001 --2021.06.17 qiulf fm9990.00改为fm9999999.00 by ZHCX-20210608-001 --2021.07.26 qiulf 修正YS_SFZTCZYF为Y时 除草药比率除0 的问题 by ZHCX-20210726-001 --2021.11.10 qiulf 多了一行挂号为其他，造成数据前移了 by ZHCX-20211109-001 --2022.01.11 qiulf 统计一年数据，动态语句太长超过4000，优化语句 by ZHCX-20220111-001 --2022.06.23 huangjy 增加检查费，检验费，医疗费(扣除检药部分) by ZHCX-20220613-001 --2022.07.15 huangjy 优化检查费，检验费查询速度 by ZHCX-20220715-001 --2024.05.24 huangjy 修正门诊中药，成药处方比率 by ZHCX-20240524-001 ------------ ------- ----------------------------------------------------------------------------- begin select count(1) into LS_KCOPEN from BM_TYZD00 where ZDMC00='医保抗肿瘤国家谈判药品不占药占比药品目录' and BH0000='Open' and trim(MC0000)='Y'; select count(1) into ZH_MZRBSFBGLGHLB from XT_XTCS00 where trim(NAME00)='ZH_MZRBSFBGLGHLB' and trim(VALUE0)='Y'; begin select trim(value0) into LS_SFKCZY from xt_xtcs00 where name00='ZH_QYSRTJSFKC'; exception when others then LS_SFKCZY:='N'; end; if LS_SFKCZY is null then LS_SFKCZY:='N'; end if; C_FLAG00:=trim(V_FLAG00); delete from BM_ZHTJ00_TEMP00 where trim(FLAG00) like '%'||trim(C_FLAG00)||'%'; commit; --2016.01.25 daihq 读取参数 begin select nvl(max(trim(value0)),'N') into ls_MZRBSFJSSJGHS from XT_XTCS00 where NAME00='ZH_MZRBSFJSSJGHS'; exception when others then ls_MZRBSFJSSJGHS:='N'; end; --2016.01.25 daihq 修改本过程时请同步修改过程SP_ZH_MZXXCX_LASTYEAR --if trim(ls_MZRBSFJSSJGHS)<>'N' then -- SP_ZH_MZXXCX_LASTYEAR(trim(V_FLAG00)||'T',V_KSYSBZ); --end if; begin select nvl(max(value0),'N') into ls_ZYFBH0 from XT_XTCS00 where NAME00 = 'YS_SFZTCZYF'; exception when others then ls_ZYFBH0:='N'; end; --材料费计算办法 '0' 医大附二算法, 只计算二级核算项目的特殊材料和DSA材料 '1'根据收费字典jsxm00来计算 --select nvl(max(trim(value0)),'0') into V_cljsfs from XT_XTCS00 where name00='SF_CLJSFS'; select STARTD,STARTT,STOPDA,STOPTI,nvl(trim(TEMP90),'%'),nvl(trim(TEMP91),'%'),nvl(TEMP92,'0') into C_STARTD,C_STARTT,C_STOPDA,C_STOPTI,C_BSYYID,C_YBLB00,C_TCTJBZ from BM_ZHTJ00_TEMP00 where FLAG00=PACKAGE_ZHCX.GET_SUMDATE; delete from BM_ZHTJ00_TEMP00 where FLAG00=PACKAGE_ZHCX.GET_SUMDATE; -- select '20170601','00:00:00','20170621','23:59:59','%','%' into C_STARTD,C_STARTT,C_STOPDA,C_STOPTI,C_BSYYID,C_YBLB00 from dual; select nvl(C_STARTD,TO_char(SYSDATE,'YYYYMMDD')), nvl(C_STARTT,'00:00:00') , nvl(C_STOPDA,TO_char(SYSDATE,'YYYYMMDD')), nvl(C_STOPTI,'23:59:59') into C_STARTD,C_STARTT,C_STOPDA,C_STOPTI from dual; select TO_char(SYSDATE,'YYYYMMDD') into C_NOW000 from dual; if C_STOPDA>C_NOW000 then C_STOPDA:=C_NOW000; C_STOPTI:='23:59:59'; end if; select SQ_BM_YYSFTJ_ID0000.NEXTVAL into C_ID0000 from dual; delete from BM_YYSFTJ where ID0000=C_ID0000; delete BM_YYSFTJ where ID0000<=C_ID0000-100; commit; begin C_TJBMBH:=to_number(C_TCTJBZ); exception when others then C_TJBMBH:=0; end; --从业务表SF_FYMX00、SF_BRXXB0中按科室(医生)抽取(预汇总)数据到临时表BM_YYSFTJ -- BMBH00(科室代号)、SFXM01(挂号类别,-9999为费用纪录)、SFXM02(挂号数量)、 -- SFXM03(医药费用)、SFXM04(药品费用)、SFXM99(开单医生代号) --以上科室、医生 -- 2008.6.27 sechen SFXM05(草药比例) --V_KSYSBZ=2时 BMBH00为开单医生代号，SFXM99为科室代号 --以上医生、科室 --总费用(医药费用) --2008.6.27 sechen 增加SFXM05(草药处方数),SFXM06(成药处方数),SFXM07(草药收入),SFXM08(成药收入),SFXM96(总处方数) --2013.06.13 laijg SFXM100（通用字段指定项目费用） --2015.11.20 daihq SFXM101 西药费 --2015.11.25 zhangyc SFXM111 基药金额 SFXM112 基药比率 -----------药品类费用统计 2019.12.05 qiulf-begin-------------------------- --ID0000,BMBH00 科室,SFXM01(挂号类别,-9999为费用纪录),SFXM04 药品费用,SFXM101 西药费,SFXM07 草药费,SFXM08 成药费,SFXM09 除草药费,SFXM113 指定药房的草药费,SFXM110 国谈药费,SFXM05 草药处方数,SFXM06 成药处方数,SFXM96 总处方数,SFXM111 基药费,SFXM99 开单医生 LS_STR000 := 'ID0000,BMBH00,SFXM01,SFXM04,SFXM101,SFXM07,SFXM08,SFXM09,SFXM113,SFXM110,SFXM05,SFXM06,SFXM96,SFXM111,SFXM03,SFXM100,SFXM40,SFXM02'; LS_STR001 := 'select '||C_ID0000||',a.GHKS00,-9999, nvl(sum(round(b.LSDJ00*b.YPZSL0,2)),0),sum(decode(a.YPDLBH,''0'',round(b.LSDJ00*b.YPZSL0,2),0)),sum(decode(a.YPDLBH,''2'',round(b.LSDJ00*b.YPZSL0,2),0)),'|| 'sum(decode(a.YPDLBH,''1'',round(b.LSDJ00*b.YPZSL0,2),0)),'|| 'sum(decode(a.YPDLBH,''2'',0,round(b.LSDJ00*b.YPZSL0,2))),'; --剔除中药费是否只剔除中药房，Y：是；N：否。默认为N。Y后可维护中药房部门编号，如Y,72 if ls_ZYFBH0 = 'N' then LS_STR001 := LS_STR001||'0,'; else LS_STR001 := LS_STR001||'sum(decode(a.YPDLBH,''2'',decode(sign(instr('','||ls_ZYFBH0||','','',''||a.YFBMBH||'','')),1,round(b.LSDJ00*b.YPZSL0,2),0))),'; end if; LS_STR001 := LS_STR001||'sum(decode((select count(1) from BM_TYZD00 where ZDMC00=''医保抗肿瘤国家谈判药品不占药占比药品目录'' and DJ0000>0 and DJ0000=b.YPNM00),1,round(b.LSDJ00*b.YPZSL0,2),0)),'|| -- 'sum(decode(a.YPDLBH,''2'',1,0)),sum(decode(a.YPDLBH,''1'',1,0)),count(a.CFLSH0),'|| --草药处方数，成药处方数，总处方数 '0,0,0,'|| --草药处方数，成药处方数，总处方数 'sum(decode((select count(1) from BM_YD0000 where YPNM00 = b.YPNM00 and YPXZ00 in (''1'',''2'')),1,round(b.LSDJ00*b.YPZSL0,2),0)),0,0,0,0 '; LS_STR002 := ' from YF_MZCF00 a,YF_MZCFMX b,SF_BRFY00 c,BM_BRXXB0 d,BM_BMBM00 e'; LS_STR003 := ' where a.CFLSH0=b.CFLSH0 and a.DJH000=c.DJH000 and a.GHKS00=e.BMBH00 and c.BRID00=d.BRID00 and c.DJH000+0>0 and b.SFZBY0=''N'' and c.CZRQ00>= '''||C_STARTD||''' and c.CZRQ00<= '''||C_STOPDA||''' '|| ' and c.CZRQ00||c.CZSJ00 between '''||C_STARTD||C_STARTT||''' and '''||C_STOPDA||C_STOPTI|| ''' and e.YYID00 like '''||C_BSYYID||''' and d.YBLB00 like '''||C_YBLB00||''' '|| 'and a.GHKS00 <>'||C_TJBMBH; if V_KSYSBZ = 0 then PSQLTEXT := 'insert into BM_YYSFTJ ('||trim(LS_STR000)||') '||trim(LS_STR001)||' '||trim(LS_STR002)||' '||trim(LS_STR003) || ' group by a.GHKS00'; elsif V_KSYSBZ = 2 then PSQLTEXT := 'insert into BM_YYSFTJ ('||trim(LS_STR000)||',SFXM99'||') '||trim(LS_STR001)||',a.YSGZH0 '||trim(LS_STR002)||' '||trim(LS_STR003) || ' group by a.YSGZH0,a.GHKS00'; else PSQLTEXT := 'insert into BM_YYSFTJ ('||trim(LS_STR000)||',SFXM99'||') '||trim(LS_STR001)||',a.YSGZH0 '||trim(LS_STR002)||' '||trim(LS_STR003) || ' group by a.GHKS00,a.YSGZH0'; end if; -- raise E_ex; -- insert into tmpSQL(ID,SQL) values(C_ID0000,PSQLTEXT); 测试 SP_EXECUTE_SQL(PSQLTEXT); -----------药品类费用统计 2019.12.05 qiulf-end-------------------------- ------------草药处方数和成药处方数begin-------------------------------------- LS_STR000 := 'ID0000,BMBH00,SFXM01,SFXM04,SFXM07,SFXM08,SFXM09,SFXM110,SFXM05,SFXM06,SFXM96,SFXM40,SFXM02'; LS_STR001 := 'select '||C_ID0000||',a.GHKS00,-9999, 0,0,0,0,0, sum(decode(a.YPDLBH,''2'',1,0)),'|| 'sum(decode(a.YPDLBH,''1'',1,0)),count(a.CFLSH0),0,0'; LS_STR002 := 'from YF_MZCF00 a,SF_BRFY00 c,BM_BRXXB0 d,BM_BMBM00 e'; LS_STR003 := 'where a.DJH000=c.DJH000 and a.GHKS00=e.BMBH00 and c.BRID00=d.BRID00 and c.DJH000+0>0 and c.CZRQ00>= '''||C_STARTD||''' and c.CZRQ00<= '''||C_STOPDA||''' '|| ' and c.CZRQ00||c.CZSJ00 between '''||C_STARTD||C_STARTT||''' and '''||C_STOPDA||C_STOPTI|| ''' and e.YYID00 like '''||C_BSYYID||''' and d.YBLB00 like '''||C_YBLB00||''' '|| 'and a.GHKS00 <>'||C_TJBMBH||' and exists (select 1 from YF_MZCFMX where CFLSH0=a.CFLSH0 and SFZBY0=''N'')'; if V_KSYSBZ = 0 then PSQLTEXT := 'insert into BM_YYSFTJ ('||trim(LS_STR000)||') '||trim(LS_STR001)||' '||trim(LS_STR002)||' '||trim(LS_STR003) || ' group by a.GHKS00'; elsif V_KSYSBZ = 2 then PSQLTEXT := 'insert into BM_YYSFTJ ('||trim(LS_STR000)||',SFXM99'||') '||trim(LS_STR001)||',a.YSGZH0 '||trim(LS_STR002)||' '||trim(LS_STR003) || ' group by a.YSGZH0,a.GHKS00'; else PSQLTEXT := 'insert into BM_YYSFTJ ('||trim(LS_STR000)||',SFXM99'||') '||trim(LS_STR001)||',a.YSGZH0 '||trim(LS_STR002)||' '||trim(LS_STR003) || ' group by a.GHKS00,a.YSGZH0'; end if; --raise E_ex; SP_EXECUTE_SQL(PSQLTEXT); ------------草药处方数和成药处方数end-------------------------------------- --SFXM114 检查费 S--begin LS_STR000 := 'ID0000,BMBH00,SFXM01,SFXM04,SFXM101,SFXM07,SFXM08,SFXM09,SFXM110,SFXM05,SFXM06,SFXM96,SFXM111,SFXM114,SFXM100,SFXM40,SFXM02'; LS_STR001 := 'select '||C_ID0000||',a.KDKS00,-9999,0,0,0,0,0,0,0,0,0,0,nvl(sum(a.HJJE00),0),0,0,0 '; LS_STR002 := 'from SF_FYMX00 a,SF_BRFY00 c,BM_BRXXB0 d,BM_BMBM00 e,BM_YYSFXM m'; LS_STR003 := 'where a.DJH000=c.DJH000 and c.BRID00=d.BRID00(+) and a.KDKS00=e.BMBH00 and c.CZRQ00>='''||C_STARTD||''' and c.CZRQ00<='''||C_STOPDA||''' '|| ' and c.CZRQ00||c.CZSJ00 between '''||C_STARTD||C_STARTT||''' and '''||C_STOPDA||C_STOPTI|| ''' and e.YYID00 like '''||C_BSYYID||''' and d.YBLB00 like '''||C_YBLB00||''' '|| ' and a.XMBH00=m.SFXMID and m.MZFPID+0 in (8) '; if V_KSYSBZ = 0 then PSQLTEXT := 'insert into BM_YYSFTJ ('||trim(LS_STR000)||') '||trim(LS_STR001)||' '||trim(LS_STR002)||' '||trim(LS_STR003) || ' group by a.KDKS00'; elsif V_KSYSBZ =2 then PSQLTEXT := 'insert into BM_YYSFTJ ('||trim(LS_STR000)||',SFXM99) '||trim(LS_STR001)||',a.KDYS00 '||trim(LS_STR002)||' '||trim(LS_STR003) || ' group by a.KDYS00,a.KDKS00'; else PSQLTEXT := 'insert into BM_YYSFTJ ('||trim(LS_STR000)||',SFXM99) '||trim(LS_STR001)||',a.KDYS00 '||trim(LS_STR002)||' '||trim(LS_STR003) || ' group by a.KDKS00,a.KDYS00'; end if; SP_EXECUTE_SQL(PSQLTEXT); --SFXM114 检查费 --end --SFXM115 检验费--begin LS_STR000 := 'ID0000,BMBH00,SFXM01,SFXM04,SFXM101,SFXM07,SFXM08,SFXM09,SFXM110,SFXM05,SFXM06,SFXM96,SFXM111,SFXM115,SFXM100,SFXM40,SFXM02'; LS_STR001 := 'select '||C_ID0000||',a.KDKS00,-9999,0,0,0,0,0,0,0,0,0,0,nvl(sum(a.HJJE00),0),0,0,0 '; LS_STR002 := 'from SF_FYMX00 a,SF_BRFY00 c,BM_BRXXB0 d,BM_BMBM00 e,BM_YYSFXM m'; LS_STR003 := 'where a.DJH000=c.DJH000 and c.BRID00=d.BRID00(+) and a.KDKS00=e.BMBH00 and c.CZRQ00>='''||C_STARTD||''' and c.CZRQ00<='''||C_STOPDA||''' '|| ' and c.CZRQ00||c.CZSJ00 between '''||C_STARTD||C_STARTT||''' and '''||C_STOPDA||C_STOPTI|| ''' and e.YYID00 like '''||C_BSYYID||''' and d.YBLB00 like '''||C_YBLB00||''' '|| ' and a.XMBH00=m.SFXMID and m.MZFPID+0 in (9,15,16) '; if V_KSYSBZ = 0 then PSQLTEXT := 'insert into BM_YYSFTJ ('||trim(LS_STR000)||') '||trim(LS_STR001)||' '||trim(LS_STR002)||' '||trim(LS_STR003) || ' group by a.KDKS00'; elsif V_KSYSBZ =2 then PSQLTEXT := 'insert into BM_YYSFTJ ('||trim(LS_STR000)||',SFXM99) '||trim(LS_STR001)||',a.KDYS00 '||trim(LS_STR002)||' '||trim(LS_STR003) || ' group by a.KDYS00,a.KDKS00'; else PSQLTEXT := 'insert into BM_YYSFTJ ('||trim(LS_STR000)||',SFXM99) '||trim(LS_STR001)||',a.KDYS00 '||trim(LS_STR002)||' '||trim(LS_STR003) || ' group by a.KDKS00,a.KDYS00'; end if; SP_EXECUTE_SQL(PSQLTEXT); --SFXM115 检查费 --end ------------其他费用统计 2019.12.05 qiulf-begin------------------------- --ID0000,BMBH00,SFXM01(挂号类别,-9999为费用纪录),SFXM03 总费用,SFXM100指定项目费用,SFXM40耗材费,SFXM99开单医生 LS_STR000 := 'ID0000,BMBH00,SFXM01,SFXM04,SFXM101,SFXM07,SFXM08,SFXM09,SFXM110,SFXM05,SFXM06,SFXM96,SFXM111,SFXM03,SFXM100,SFXM40,SFXM02'; LS_STR001 := 'select '||C_ID0000||',a.KDKS00,-9999,0,0,0,0,0,0,0,0,0,0,sum(a.HJJE00),nvl(sum(decode((select count(1) from BM_TYZD00 where ZDMC00=''医疗收费编码或药品编码指定'' and YXBZ00=''1'' and trim(BH0000)=trim(a.XMBH00)),1,a.HJJE00,0)),0), '|| 'sum(decode(a.SFLB00,''2'',a.HJJE00,0)),0 '; LS_STR002 := 'from SF_FYMX00 a,SF_BRFY00 c,BM_BRXXB0 d,BM_BMBM00 e'; LS_STR003 := 'where a.DJH000=c.DJH000 and c.BRID00=d.BRID00(+) and a.KDKS00=e.BMBH00 and c.CZRQ00>='''||C_STARTD||''' and c.CZRQ00<='''||C_STOPDA||''' '|| ' and c.CZRQ00||c.CZSJ00 between '''||C_STARTD||C_STARTT||''' and '''||C_STOPDA||C_STOPTI|| ''' and e.YYID00 like '''||C_BSYYID||''' and d.YBLB00 like '''||C_YBLB00||''' '|| 'and a.KDKS00 <>'||C_TJBMBH; if V_KSYSBZ = 0 then PSQLTEXT := 'insert into BM_YYSFTJ ('||trim(LS_STR000)||') '||trim(LS_STR001)||' '||trim(LS_STR002)||' '||trim(LS_STR003) || ' group by a.KDKS00,a.XMBH00'; elsif V_KSYSBZ =2 then PSQLTEXT := 'insert into BM_YYSFTJ ('||trim(LS_STR000)||',SFXM99) '||trim(LS_STR001)||',a.KDYS00 '||trim(LS_STR002)||' '||trim(LS_STR003) || ' group by a.KDYS00,a.KDKS00,a.XMBH00'; else PSQLTEXT := 'insert into BM_YYSFTJ ('||trim(LS_STR000)||',SFXM99) '||trim(LS_STR001)||',a.KDYS00 '||trim(LS_STR002)||' '||trim(LS_STR003) || ' group by a.KDKS00,a.KDYS00,a.XMBH00'; end if; SP_EXECUTE_SQL(PSQLTEXT); ------------其他费用统计 2019.12.05 qiulf-end------------------------- ------------挂号量统计-begin----------------------------------------- --ID0000,BMBH00挂号科室,SFXM01挂号类别,SFXM02挂号人数,SFXM99挂号医生 LS_STR000 := 'ID0000,BMBH00,SFXM01,SFXM04,SFXM101,SFXM07,SFXM08,SFXM09,SFXM110,SFXM05,SFXM06,SFXM96,SFXM111,SFXM03,SFXM100,SFXM40,SFXM02'; LS_STR001 := 'select '||C_ID0000||',a.GHKS00,a.GHLB00,0,0,0,0,0,0,0,0,0,0,0,0,0,count(a.GHID00)'; LS_STR002 := 'from SF_BRXXB0 a,BM_BMBM00 b,BM_BRXXB0 c'; LS_STR003 := 'where a.GHKS00=b.BMBH00 and a.BRID00=c.BRID00(+) and a.GHLB00 >=0 and a.GHRQ00>='''||C_STARTD||''' and a.GHRQ00<='''||C_STOPDA||''' '|| ' and a.GHRQ00||a.GHSJ00 between '''||C_STARTD||C_STARTT||''' and '''||C_STOPDA||C_STOPTI|| ''' and b.YYID00 like '''||C_BSYYID||''' and c.YBLB00 like '''||C_YBLB00||''' '|| 'and a.GHKS00 <>'||C_TJBMBH; if ZH_MZRBSFBGLGHLB = 0 then LS_STR003 := LS_STR003||' and a.GHLB00 in (select LBBH00 from BM_GHLBB0 where SFTJ00=''Y'' )'; end if; if V_KSYSBZ= 0 then PSQLTEXT := 'insert into BM_YYSFTJ ('||trim(LS_STR000)||') '||trim(LS_STR001)||' '||trim(LS_STR002)||' '||trim(LS_STR003) || ' group by a.GHKS00,a.GHLB00'; elsif V_KSYSBZ= 2 then PSQLTEXT := 'insert into BM_YYSFTJ ('||trim(LS_STR000)||',SFXM99) '||trim(LS_STR001)||',a.JZYS00 '||trim(LS_STR002)||' '||trim(LS_STR003) || ' group by a.JZYS00,a.GHLB00,a.GHKS00'; else PSQLTEXT := 'insert into BM_YYSFTJ ('||trim(LS_STR000)||',SFXM99) '||trim(LS_STR001)||',a.JZYS00 '||trim(LS_STR002)||' '||trim(LS_STR003) || ' group by a.GHKS00,a.GHLB00,a.JZYS00'; end if; SP_EXECUTE_SQL(PSQLTEXT); ------------挂号量统计-end----------------------------------------- ------------退号统计---begin-------------------------------------- --ID0000,BMBH00挂号科室,SFXM01挂号类别,SFXM02挂号人数,SFXM99挂号医生 LS_STR000 := 'ID0000,BMBH00,SFXM01,SFXM04,SFXM101,SFXM07,SFXM08,SFXM09,SFXM110,SFXM05,SFXM06,SFXM96,SFXM111,SFXM03,SFXM100,SFXM40,SFXM02'; LS_STR001 := 'select '||C_ID0000||',a.GHKS00,a.GHLB00,0,0,0,0,0,0,0,0,0,0,0,0,0,-1*count(a.GHID00)'; LS_STR002 := 'from SF_BRXXB0 a,BM_BMBM00 b,BM_BRXXB0 c'; LS_STR003 := 'where a.GHKS00=b.BMBH00 and a.BRID00=c.BRID00(+) and a.GHLB00 >=0 and a.THRQ00 >='''||C_STARTD||''' and a.THRQ00<='''||C_STOPDA||''' '|| ' and a.THRQ00||a.THSJ00 between '''||C_STARTD||C_STARTT||''' and '''||C_STOPDA||C_STOPTI|| ''' and b.YYID00 like '''||C_BSYYID||''' and c.YBLB00 like '''||C_YBLB00||''' '|| 'and a.GHKS00 <>'||C_TJBMBH; if ZH_MZRBSFBGLGHLB = 0 then LS_STR003 := LS_STR003||' and a.GHLB00 in (select LBBH00 from BM_GHLBB0 where SFTJ00=''Y'' )'; end if; if V_KSYSBZ= 0 then PSQLTEXT := 'insert into BM_YYSFTJ ('||trim(LS_STR000)||') '||trim(LS_STR001)||' '||trim(LS_STR002)||' '||trim(LS_STR003) || ' group by a.GHKS00,a.GHLB00'; elsif V_KSYSBZ = 2 then PSQLTEXT := 'insert into BM_YYSFTJ ('||trim(LS_STR000)||',SFXM99) '||trim(LS_STR001)||',a.JZYS00 '||trim(LS_STR002)||' '||trim(LS_STR003) || ' group by a.JZYS00,a.GHLB00,a.GHKS00'; else PSQLTEXT := 'insert into BM_YYSFTJ ('||trim(LS_STR000)||',SFXM99) '||trim(LS_STR001)||',a.JZYS00 '||trim(LS_STR002)||' '||trim(LS_STR003) || ' group by a.GHKS00,a.GHLB00,a.JZYS00'; end if; SP_EXECUTE_SQL(PSQLTEXT); ------------退号统计---end-------------------------------------- --统计总表 -----------------------------------------------------统计总表begin-------------------------------------------------------- --挂号统计到总表BM_ZHTJ00_TEMP00 --ZH_SFSYBRXXBGHLBTJGHRW参数：是否使用SF_BRXXB0的GHLB00来分类统计挂号人数，Y:使用 N:不使用 select count(1) into ZH_SFSYBRXXBGHLBTJGHRW from XT_XTCS00 where NAME00='ZH_SFSYBRXXBGHLBTJGHRW' and trim(VALUE0)='Y'; LS_STR000 := ''; LS_STR001 := ''; C_INSERTNM := 0; -- 1 if ZH_SFSYBRXXBGHLBTJGHRW = 0 then for TJGHXX in (select LBBH00 from BM_GHLBB0 where SFYX00='Y' and LBBH00>=0 order by LBBH00) loop C_INSERTNM := C_INSERTNM + 1; LS_STR000 := LS_STR000||'TEMP'||trim(to_char(C_INSERTNM,'00'))||','; LS_STR001 := LS_STR001||'decode(sum(decode(a.SFXM01,'||TJGHXX.LBBH00||',SFXM02,0)),0,null,sum(decode(a.SFXM01,'||TJGHXX.LBBH00||',SFXM02,0))),'; end loop; C_INSERTNM := C_INSERTNM + 1; LS_STR000 := LS_STR000||'TEMP'||trim(to_char(C_INSERTNM,'00'))||','; LS_STR001 := LS_STR001||'decode(sum(decode((select count(1) from BM_GHLBB0 where LBBH00=a.SFXM01 and SFYX00 = ''Y'' and LBBH00>=0),0,SFXM02,0)),0,null,sum(decode((select count(1) from BM_GHLBB0 where LBBH00=a.SFXM01 and SFYX00 = ''Y'' and LBBH00>=0),0,SFXM02,0))),'; else --ZHCX-20200205-001 add and ID0000=C_ID0000 for TJGHXX in (select SFXM01 from BM_YYSFTJ where SFXM01 <> -9999 and ID0000=C_ID0000 group by SFXM01) loop C_INSERTNM := C_INSERTNM + 1; LS_STR000 := LS_STR000||'TEMP'||trim(to_char(C_INSERTNM,'00'))||','; LS_STR001 := LS_STR001||'sum(decode(a.SFXM01,'||TJGHXX.SFXM01||',SFXM02,0)),'; end loop; C_INSERTNM := C_INSERTNM + 1; --多了一行挂号为其他的 C_INSERTNM := C_INSERTNM + 1; end if; --医疗费用 C_INSERTNM := C_INSERTNM + 1; LS_YLFYIN := C_INSERTNM; LS_STR000 := LS_STR000||'TEMP'||trim(to_char(C_INSERTNM,'00'))||','; LS_STR001 := LS_STR001||'trim(to_char(sum(SFXM03-SFXM04),''999999990.00'')),'; --药品费用 C_INSERTNM:=C_INSERTNM+1; LS_YPFYIN := C_INSERTNM; LS_STR000 := LS_STR000||'TEMP'||trim(to_char(C_INSERTNM,'00'))||','; LS_STR001 := LS_STR001||'trim(to_char(sum(SFXM04),''999999990.00'')),'; --药品比率 C_INSERTNM:=C_INSERTNM+1; LS_STR000 := LS_STR000||'TEMP'||trim(to_char(C_INSERTNM,'00'))||','; if LS_SFKCZY='Y' then --除草药比率 LS_STR001 := LS_STR001||'trim(decode(sum(SFXM03-SFXM100),0,''0'',to_char(sum(SFXM09)*100/sum(SFXM03-SFXM100),''fm99999990.00''))||''%''),'; else LS_STR001 := LS_STR001||'trim(decode(sum(SFXM03-SFXM100),0,''0'',to_char(sum(SFXM04)*100/sum(SFXM03-SFXM100),''fm99999990.00''))||''%''),'; end if; --今年去年药品比率差字段 LS_YPBTC0 := C_INSERTNM; --平均费用,药品平均费用 C_INSERTNM:=C_INSERTNM+1; LS_STR000 := LS_STR000||'TEMP'||trim(to_char(C_INSERTNM,'00'))||','; --今年去年平均费用差字段 LS_PJFYC0 := C_INSERTNM; C_INSERTNM:=C_INSERTNM+1; LS_STR000 := LS_STR000||'TEMP'||trim(to_char(C_INSERTNM,'00'))||','; --今年去年药品平均费用差字段 LS_YPPJFC := C_INSERTNM; LS_STR001 := LS_STR001||'decode(sum(SFXM02),0,''0'',to_char(sum(SFXM03)/sum(SFXM02),''fm99999990.00'')),decode(sum(SFXM02),0,''0'',to_char(sum(SFXM04)/sum(SFXM02),''fm99999990.00'')),'; --草药处方比率 C_INSERTNM:=C_INSERTNM+1; LS_STR000 := LS_STR000||'TEMP'||trim(to_char(C_INSERTNM,'00'))||','; LS_STR001 := LS_STR001||'decode(sum(SFXM96),0,''0'',to_char(sum(SFXM05)*100/sum(SFXM96),''fm99999990.00''))||''%'','; --成药处方比率 C_INSERTNM:=C_INSERTNM+1; LS_STR000 := LS_STR000||'TEMP'||trim(to_char(C_INSERTNM,'00'))||','; LS_STR001 := LS_STR001||'trim(decode(sum(SFXM96),0,''0'',to_char(sum(SFXM06)*100/sum(SFXM96),''fm99999990.00''))||''%''),'; --草药费占药品比率 C_INSERTNM:=C_INSERTNM+1; LS_STR000 := LS_STR000||'TEMP'||trim(to_char(C_INSERTNM,'00'))||','; LS_STR001 := LS_STR001||'trim(decode(sum(SFXM04),0,''0'',to_char(sum(SFXM07)*100/sum(SFXM04),''fm99999990.00''))||''%''),'; --成药费占药品比率 C_INSERTNM:=C_INSERTNM+1; LS_STR000 := LS_STR000||'TEMP'||trim(to_char(C_INSERTNM,'00'))||','; LS_STR001 := LS_STR001||'trim(decode(sum(SFXM04),0,''0'',to_char(sum(SFXM08)*100/sum(SFXM04),''fm99999990.00''))||''%''),'; --除草药外药品比率 C_INSERTNM:=C_INSERTNM+1; LS_STR000 := LS_STR000||'TEMP'||trim(to_char(C_INSERTNM,'00'))||','; if ls_ZYFBH0 = 'N' then LS_STR001 := LS_STR001||'trim(decode(sum(SFXM03),0,''0'',to_char(sum(SFXM09)*100/sum(SFXM03),''fm99999990.00''))||''%''),'; else -- 修正YS_SFZTCZYF为Y时 除草药比率除0 的问题 by ZHCX-20210726-001 LS_STR001 := LS_STR001||'trim(decode(sum(nvl(SFXM03,0))-sum(nvl(SFXM113,0)),0,''0'',to_char((sum(SFXM09)-sum(nvl(SFXM113,0)))*100/(sum(SFXM03)-sum(nvl(SFXM113,0))),''fm99999990.00''))||''%''),'; end if; --TEMP114 检查费,TEMP115 检验费, TEMP116 LS_STR000 := LS_STR000||'TEMP114,TEMP115,TEMP116,'; --TEMP116 医疗费=总费用-药品费用-检查费- 检验费, --lS_STR001 := LS_STR001||'trim(to_char(sum(SFXM114),''999999990.00'')),trim(to_char(sum(SFXM115),''999999990.00'')), trim(to_char(sum(SFXM03-SFXM04-SFXM114-SFXM115),''999999990.00'')),'; lS_STR001 := LS_STR001||'trim(to_char(sum(nvl(SFXM114,0)),''999999990.00'')),trim(to_char(sum(nvl(SFXM115,0)),''999999990.00'')),trim(to_char(sum(nvl(SFXM03,0)-nvl(SFXM04,0)-nvl(SFXM114,0)-nvl(SFXM115,0)),''999999990.00'')),'; --TEMP108 除国谈药品费用（也除草药）,TEMP109除国谈药品比率（也除草药） if LS_KCOPEN > 0 then LS_STR000 := LS_STR000||'TEMP108,TEMP109,'; lS_STR001 := LS_STR001||'sum(SFXM09-SFXM110),trim(decode(sum(SFXM03),0,''0'',to_char(sum(SFXM09-SFXM110)*100/sum(SFXM03),''fm99999990.00''))||''%''),';-- end if; --FLAG00 统计标识,GHHJ00 总挂号数,FYHJ00 总费用,TEMP96 总处方数,TEMP45 耗材费,TEMP47 耗材比率,TEMP100指定项目,TEMP101 西药费,TEMP102 草药费,TEMP103 成药费,TEMP111基药费,TEMP112基药占药品比率 LS_STR000 := 'FLAG00,GHHJ00,FYHJ00,TEMP96,'||trim(LS_STR000)||'TEMP45,TEMP47,TEMP100,TEMP101,TEMP102,TEMP103,TEMP111,TEMP112,TEMP120,TEMP121'; LS_STR001 := 'select '''||trim(C_FLAG00)||''',sum(SFXM02),sum(SFXM03),sum(SFXM96),'||trim(LS_STR001)|| 'sum(SFXM40),decode(sum(SFXM03),0,''0'',to_char(sum(SFXM40)*100/sum(SFXM03),''fm99999990.00''))||''%'','|| 'sum(SFXM100),sum(SFXM101),sum(SFXM07),sum(SFXM08),sum(SFXM111),trim(decode(sum(SFXM04),0,''0'',to_char(sum(SFXM111)*100/sum(SFXM04),''fm99999990.00''))||''%''),sum(SFXM107),sum(SFXM110)'; if V_KSYSBZ = 0 then --科室 PSQLTEXT := 'insert into BM_ZHTJ00_TEMP00('||trim(LS_STR000)||',BMBH00) '||trim(LS_STR001) || ',BMBH00 from BM_YYSFTJ a where a.ID0000='||C_ID0000||' group by a.BMBH00'; SP_EXECUTE_SQL(PSQLTEXT); update BM_ZHTJ00_TEMP00 a set BMMC00=(select BMMC00 from BM_BMBM00 x where x.BMBH00=a.BMBH00) where FLAG00=C_FLAG00; PSQLTEXT := 'insert into BM_ZHTJ00_TEMP00('||trim(LS_STR000)||',BMBH00,BMMC00) '||trim(LS_STR001) || ',-9999,''全院'' from BM_YYSFTJ a where a.ID0000='||C_ID0000; SP_EXECUTE_SQL(PSQLTEXT); elsif V_KSYSBZ = 2 then --医生、科室 --小计 PSQLTEXT := 'insert into BM_ZHTJ00_TEMP00('||trim(LS_STR000)||',BMBH00,TEMP98) '||trim(LS_STR001) || ',decode(SFXM99,0,99999,SFXM99),-9999 from BM_YYSFTJ a where a.ID0000='||C_ID0000||' group by a.SFXM99'; SP_EXECUTE_SQL(PSQLTEXT); update BM_ZHTJ00_TEMP00 a set TEMP99=nvl(( select trim(ZWXM00) from BM_YGBM00 where YGBH00=a.BMBH00),'其他') where FLAG00=C_FLAG00 and a.BMBH00 > 0; --全院 PSQLTEXT := 'insert into BM_ZHTJ00_TEMP00('||trim(LS_STR000)||',BMBH00,TEMP99) '||trim(LS_STR001) || ',-9999,''全院'' from BM_YYSFTJ a where a.ID0000='||C_ID0000; SP_EXECUTE_SQL(PSQLTEXT); --明细 PSQLTEXT := 'insert into BM_ZHTJ00_TEMP00('||trim(LS_STR000)||',BMBH00,TEMP98,BMMC00) '||trim(LS_STR001) || ',decode(SFXM99,0,99999,SFXM99),decode(BMBH00,0,99999,BMBH00),''其他'' from BM_YYSFTJ a where a.ID0000='||C_ID0000||' group by a.SFXM99,a.BMBH00'; SP_EXECUTE_SQL(PSQLTEXT); update BM_ZHTJ00_TEMP00 a set BMMC00= nvl((select BMMC00 from BM_BMBM00 x where x.BMBH00=a.TEMP98),'其他') where FLAG00=C_FLAG00 and a.TEMP98>0; else --科室、医生 --小计 PSQLTEXT := 'insert into BM_ZHTJ00_TEMP00('||trim(LS_STR000)||',BMBH00,TEMP98) '||trim(LS_STR001) || ',decode(BMBH00,0,99999,BMBH00),-9999 from BM_YYSFTJ a where a.ID0000='||C_ID0000||' group by a.BMBH00'; SP_EXECUTE_SQL(PSQLTEXT); update BM_ZHTJ00_TEMP00 a set BMMC00= nvl((select BMMC00 from BM_BMBM00 x where x.BMBH00=a.BMBH00),'其他') where FLAG00=C_FLAG00 and a.BMBH00 >0; --全院 PSQLTEXT := 'insert into BM_ZHTJ00_TEMP00('||trim(LS_STR000)||',BMBH00,BMMC00) '||trim(LS_STR001) || ',-9999,''全院'' from BM_YYSFTJ a where a.ID0000='||C_ID0000; SP_EXECUTE_SQL(PSQLTEXT); --明细 PSQLTEXT := 'insert into BM_ZHTJ00_TEMP00('||trim(LS_STR000)||',BMBH00,TEMP98,TEMP99) '||trim(LS_STR001) || ',decode(BMBH00,0,99999,BMBH00),decode(SFXM99,0,99999,SFXM99),''其他'' from BM_YYSFTJ a where a.ID0000='||C_ID0000||' group by a.BMBH00,a.SFXM99'; SP_EXECUTE_SQL(PSQLTEXT); update BM_ZHTJ00_TEMP00 a set TEMP99=nvl(( select trim(ZWXM00) from BM_YGBM00 where YGBH00=a.TEMP98),'其他') where FLAG00=C_FLAG00 and a.TEMP98>0; end if; update BM_ZHTJ00_TEMP00 a set TEMP120=TEMP102 where FLAG00=C_FLAG00 ;--and a.TEMP98>0; --2016.01.25 daihq 增加 TEMP104 实际挂号数 if trim(ls_MZRBSFJSSJGHS)<>'N' then if V_KSYSBZ = 0 then --实际挂号数统计 --全院 PSQLTEXT:='UPDATE BM_ZHTJ00_TEMP00 SET TEMP104'|| '=nvl((select trim(to_char(sum(SFXM02),''9999999999'')) from BM_YYSFTJ where ID0000='||trim(to_char(C_ID0000))|| ' and SFXM01 not in ('||trim(ls_MZRBSFJSSJGHS)||')),''0'') where FLAG00='''|| trim(C_FLAG00)||''' and BMBH00=-9999 '; SP_EXECUTE_SQL(PSQLTEXT); --部门 PSQLTEXT:='UPDATE BM_ZHTJ00_TEMP00 SET TEMP104'|| '=nvl((select trim(to_char(sum(SFXM02),''9999999999'')) from BM_YYSFTJ where ID0000='||trim(to_char(C_ID0000))|| ' and BMBH00=BM_ZHTJ00_TEMP00.BMBH00 and SFXM01 not in ('||trim(ls_MZRBSFJSSJGHS)||')),''0'') where FLAG00='''|| trim(C_FLAG00)||''' and BMBH00<>-9999'; SP_EXECUTE_SQL(PSQLTEXT); --全院 --平均费用 PSQLTEXT:='UPDATE BM_ZHTJ00_TEMP00 SET TEMP'||trim(to_char(LS_PJFYC0,'00'))|| '=decode(to_number(TEMP104),0,''0.00'',trim(to_char(round((to_number(FYHJ00)-to_number(TEMP100))/to_number(TEMP104),2),''999999990.00''))) where FLAG00='''|| trim(C_FLAG00)||''' and BMBH00=-9999 '; SP_EXECUTE_SQL(PSQLTEXT); --药品平均费用 PSQLTEXT:='UPDATE BM_ZHTJ00_TEMP00 SET TEMP'||trim(to_char(LS_YPPJFC,'00'))|| '=decode(TEMP104,0,''0'',to_char(TEMP'||trim(to_char(LS_YPFYIN,'00'))||'/TEMP104,''fm99999990.00'')) where FLAG00='''|| trim(C_FLAG00)||''' and BMBH00=-9999 '; SP_EXECUTE_SQL(PSQLTEXT); --部门 --平均费用 PSQLTEXT:='UPDATE BM_ZHTJ00_TEMP00 SET TEMP'||trim(to_char(LS_PJFYC0,'00'))|| '=decode(to_number(TEMP104),0,''0.00'',trim(to_char(round((to_number(FYHJ00)-to_number(TEMP100))/to_number(TEMP104),2),''999999990.00''))) where FLAG00='''|| trim(C_FLAG00)||''' and BMBH00<>-9999 '; SP_EXECUTE_SQL(PSQLTEXT); --药品平均费用 PSQLTEXT:='UPDATE BM_ZHTJ00_TEMP00 SET TEMP'||trim(to_char(LS_YPPJFC,'00'))|| '=decode(TEMP104,0,''0'',to_char(TEMP'||trim(to_char(LS_YPFYIN,'00'))||'/TEMP104,''fm99999990.00'')) where FLAG00='''|| trim(C_FLAG00)||''' and BMBH00<>-9999 '; SP_EXECUTE_SQL(PSQLTEXT); else --小计 PSQLTEXT:='UPDATE BM_ZHTJ00_TEMP00 SET TEMP104'|| '=nvl((select trim(to_char(sum(SFXM02),''9999999999'')) from BM_YYSFTJ where ID0000='||trim(to_char(C_ID0000))|| ' and BMBH00=BM_ZHTJ00_TEMP00.BMBH00 and SFXM01 not in ('||trim(ls_MZRBSFJSSJGHS)||')),''0'') where FLAG00='''|| trim(C_FLAG00)||''' and BMBH00<>-9999 and to_number(TEMP98)=-9999 '; SP_EXECUTE_SQL(PSQLTEXT); --全院 PSQLTEXT:='UPDATE BM_ZHTJ00_TEMP00 SET TEMP104'|| '=nvl((select trim(to_char(sum(SFXM02),''9999999999'')) from BM_YYSFTJ where ID0000='||trim(to_char(C_ID0000))|| ' and SFXM01 not in ('||trim(ls_MZRBSFJSSJGHS)||')),''0'') where FLAG00='''|| trim(C_FLAG00)||''' and BMBH00=-9999 '; SP_EXECUTE_SQL(PSQLTEXT); --部门 PSQLTEXT:='UPDATE BM_ZHTJ00_TEMP00 SET TEMP104'|| '=nvl((select trim(to_char(sum(SFXM02),''9999999999'')) from BM_YYSFTJ where ID0000='||trim(to_char(C_ID0000))|| ' and BMBH00=BM_ZHTJ00_TEMP00.BMBH00 and SFXM99=to_number(BM_ZHTJ00_TEMP00.TEMP98) and SFXM01 not in ('||trim(ls_MZRBSFJSSJGHS)||')),''0'') where FLAG00='''|| trim(C_FLAG00)||''' and BMBH00<>-9999 and to_number(TEMP98)<>-9999 '; SP_EXECUTE_SQL(PSQLTEXT); --平均费用 PSQLTEXT:='UPDATE BM_ZHTJ00_TEMP00 SET TEMP'||trim(to_char(LS_PJFYC0,'00'))|| '=decode(to_number(TEMP104),0,''0.00'',trim(to_char(round((to_number(FYHJ00)-to_number(TEMP100))/to_number(TEMP104),2),''999999990.00''))) where FLAG00='''|| trim(C_FLAG00)||''' and BMBH00<>-9999 and to_number(TEMP98)=-9999 '; SP_EXECUTE_SQL(PSQLTEXT); --药品平均费用 PSQLTEXT:='UPDATE BM_ZHTJ00_TEMP00 SET TEMP'||trim(to_char(LS_YPPJFC,'00'))|| '=decode(TEMP104,0,''0'',to_char(TEMP'||trim(to_char(LS_YPFYIN,'00'))||'/TEMP104,''fm99999990.00'')) where FLAG00='''|| trim(C_FLAG00)||''' and BMBH00<>-9999 and to_number(TEMP98)=-9999 '; SP_EXECUTE_SQL(PSQLTEXT); --平均费用 PSQLTEXT:='UPDATE BM_ZHTJ00_TEMP00 SET TEMP'||trim(to_char(LS_PJFYC0,'00'))|| '=decode(to_number(TEMP104),0,''0.00'',trim(to_char(round((to_number(FYHJ00)-to_number(TEMP100))/to_number(TEMP104),2),''999999990.00''))) where FLAG00='''|| trim(C_FLAG00)||''' and BMBH00=-9999 '; SP_EXECUTE_SQL(PSQLTEXT); --药品平均费用 PSQLTEXT:='UPDATE BM_ZHTJ00_TEMP00 SET TEMP'||trim(to_char(LS_YPPJFC,'00'))|| '=decode(TEMP104,0,''0'',to_char(TEMP'||trim(to_char(LS_YPFYIN,'00'))||'/TEMP104,''fm99999990.00'')) where FLAG00='''|| trim(C_FLAG00)||''' and BMBH00=-9999 '; SP_EXECUTE_SQL(PSQLTEXT); --平均费用 PSQLTEXT:='UPDATE BM_ZHTJ00_TEMP00 SET TEMP'||trim(to_char(LS_PJFYC0,'00'))|| '=decode(to_number(TEMP104),0,''0.00'',trim(to_char(round((to_number(FYHJ00)-to_number(TEMP100))/to_number(TEMP104),2),''999999990.00''))) where FLAG00='''|| trim(C_FLAG00)||''' and BMBH00<>-9999 and to_number(TEMP98)<>-9999 '; SP_EXECUTE_SQL(PSQLTEXT); --药品平均费用 PSQLTEXT:='UPDATE BM_ZHTJ00_TEMP00 SET TEMP'||trim(to_char(LS_YPPJFC,'00'))|| '=decode(TEMP104,0,''0'',to_char(TEMP'||trim(to_char(LS_YPFYIN,'00'))||'/TEMP104,''fm99999990.00'')) where FLAG00='''|| trim(C_FLAG00)||''' and BMBH00<>-9999 and to_number(TEMP98)<>-9999 '; SP_EXECUTE_SQL(PSQLTEXT); end if; -----------------------------------------------------统计总表end-------------------------------------------------------- -----------------------------------------------------统计今年去年比较begin---------------------------------------------- select SQ_BM_YYSFTJ_ID0000.nextval into C_IDQN00 from dual; --2016.01.25 daihq 取去年日期 select to_char(add_months(to_date(C_STARTD,'YYYYMMDD'),-12),'YYYYMMDD') into C_STARTD_OLD from dual; select to_char(add_months(to_date(C_STOPDA,'YYYYMMDD'),-12),'YYYYMMDD') into C_STOPDA_OLD from dual; --ID0000,BMBH00,SFXM01(挂号类别,-9999为费用纪录),SFXM03 总费用,SFXM100指定项目费用,SFXM40耗材费,SFXM99开单医生 LS_STR000 := 'ID0000,BMBH00,SFXM01,SFXM04,SFXM101,SFXM07,SFXM08,SFXM09,SFXM110,SFXM05,SFXM06,SFXM96,SFXM111,SFXM03,SFXM100,SFXM40,SFXM02'; LS_STR001 := 'select '||C_IDQN00||',a.KDKS00,-9999,sum(decode(xmbh00,9999999997,a.HJJE00,9999999998,a.HJJE00,9999999999,a.HJJE00,0)),0,0,0, sum(decode(xmbh00,9999999997,a.HJJE00,9999999998,a.HJJE00,0)),0,0,0,0,0,sum(a.HJJE00),nvl(sum(decode((select count(1) from BM_TYZD00 where ZDMC00=''医疗收费编码或药品编码指定'' and YXBZ00=''1'' and trim(BH0000)=trim(a.XMBH00)),1,a.HJJE00,0)),0), '|| 'sum(decode(a.SFLB00,''2'',a.HJJE00,0)),0 '; LS_STR002 := 'from SF_FYMX00 a,SF_BRFY00 c,BM_BRXXB0 d,BM_BMBM00 e'; LS_STR003 := 'where a.DJH000=c.DJH000 and c.BRID00=d.BRID00(+) and a.KDKS00=e.BMBH00 and c.CZRQ00>='''||C_STARTD_OLD||''' and c.CZRQ00<='''||C_STOPDA_OLD||''' '|| ' and c.CZRQ00||c.CZSJ00 between '''||C_STARTD_OLD||C_STARTT||''' and '''||C_STOPDA_OLD||C_STOPTI|| ''' and e.YYID00 like '''||C_BSYYID||''' and d.YBLB00 like '''||C_YBLB00||''' '|| 'and a.KDKS00 <>'||C_TJBMBH; if V_KSYSBZ = 0 then PSQLTEXT := 'insert into BM_YYSFTJ ('||trim(LS_STR000)||') '||trim(LS_STR001)||' '||trim(LS_STR002)||' '||trim(LS_STR003) || ' group by a.KDKS00,a.XMBH00'; elsif V_KSYSBZ =2 then PSQLTEXT := 'insert into BM_YYSFTJ ('||trim(LS_STR000)||',SFXM99) '||trim(LS_STR001)||',a.KDYS00 '||trim(LS_STR002)||' '||trim(LS_STR003) || ' group by a.KDYS00,a.KDKS00,a.XMBH00'; else PSQLTEXT := 'insert into BM_YYSFTJ ('||trim(LS_STR000)||',SFXM99) '||trim(LS_STR001)||',a.KDYS00 '||trim(LS_STR002)||' '||trim(LS_STR003) || ' group by a.KDKS00,a.KDYS00,a.XMBH00'; end if; SP_EXECUTE_SQL(PSQLTEXT); ------------挂号量统计-begin----------------------------------------- --ID0000,BMBH00挂号科室,SFXM01挂号类别,SFXM02挂号人数,SFXM99挂号医生 LS_STR000 := 'ID0000,BMBH00,SFXM01,SFXM04,SFXM101,SFXM07,SFXM08,SFXM09,SFXM110,SFXM05,SFXM06,SFXM96,SFXM111,SFXM03,SFXM100,SFXM40,SFXM02'; LS_STR001 := 'select '||C_IDQN00||',a.GHKS00,a.GHLB00,0,0,0,0,0,0,0,0,0,0,0,0,0,count(a.GHID00)'; LS_STR002 := 'from SF_BRXXB0 a,BM_BMBM00 b,BM_BRXXB0 c'; LS_STR003 := 'where a.GHKS00=b.BMBH00 and a.BRID00=c.BRID00(+) and a.GHLB00 >=0 and a.GHRQ00>='''||C_STARTD_OLD||''' and a.GHRQ00<='''||C_STOPDA_OLD||''' '|| ' and a.GHRQ00||a.GHSJ00 between '''||C_STARTD_OLD||C_STARTT||''' and '''||C_STOPDA_OLD||C_STOPTI|| ''' and b.YYID00 like '''||C_BSYYID||''' and c.YBLB00 like '''||C_YBLB00||''' '|| 'and a.GHKS00 <>'||C_TJBMBH; if ZH_MZRBSFBGLGHLB = 0 then LS_STR003 := LS_STR003||' and a.GHLB00 in (select LBBH00 from BM_GHLBB0 where SFTJ00=''Y'' )'; end if; if V_KSYSBZ= 0 then PSQLTEXT := 'insert into BM_YYSFTJ ('||trim(LS_STR000)||') '||trim(LS_STR001)||' '||trim(LS_STR002)||' '||trim(LS_STR003) || ' group by a.GHKS00,a.GHLB00'; elsif V_KSYSBZ= 2 then PSQLTEXT := 'insert into BM_YYSFTJ ('||trim(LS_STR000)||',SFXM99) '||trim(LS_STR001)||',a.JZYS00 '||trim(LS_STR002)||' '||trim(LS_STR003) || ' group by a.JZYS00,a.GHLB00,a.GHKS00'; else PSQLTEXT := 'insert into BM_YYSFTJ ('||trim(LS_STR000)||',SFXM99) '||trim(LS_STR001)||',a.JZYS00 '||trim(LS_STR002)||' '||trim(LS_STR003) || ' group by a.GHKS00,a.GHLB00,a.JZYS00'; end if; SP_EXECUTE_SQL(PSQLTEXT); ------------挂号量统计-end----------------------------------------- ------------退号统计---begin-------------------------------------- --ID0000,BMBH00挂号科室,SFXM01挂号类别,SFXM02挂号人数,SFXM99挂号医生 LS_STR000 := 'ID0000,BMBH00,SFXM01,SFXM04,SFXM101,SFXM07,SFXM08,SFXM09,SFXM110,SFXM05,SFXM06,SFXM96,SFXM111,SFXM03,SFXM100,SFXM40,SFXM02'; LS_STR001 := 'select '||C_IDQN00||',a.GHKS00,a.GHLB00,0,0,0,0,0,0,0,0,0,0,0,0,0,-1*count(a.GHID00)'; LS_STR002 := 'from SF_BRXXB0 a,BM_BMBM00 b,BM_BRXXB0 c'; LS_STR003 := 'where a.GHKS00=b.BMBH00 and a.BRID00=c.BRID00(+) and a.GHLB00 >=0 and a.THRQ00 >='''||C_STARTD_OLD||''' and a.THRQ00<='''||C_STOPDA_OLD||''' '|| ' and a.THRQ00||a.THSJ00 between '''||C_STARTD_OLD||C_STARTT||''' and '''||C_STOPDA_OLD||C_STOPTI|| ''' and b.YYID00 like '''||C_BSYYID||''' and c.YBLB00 like '''||C_YBLB00||''' '|| 'and a.GHKS00 <>'||C_TJBMBH; if ZH_MZRBSFBGLGHLB = 0 then LS_STR003 := LS_STR003||' and a.GHLB00 in (select LBBH00 from BM_GHLBB0 where SFTJ00=''Y'' )'; end if; if V_KSYSBZ= 0 then PSQLTEXT := 'insert into BM_YYSFTJ ('||trim(LS_STR000)||') '||trim(LS_STR001)||' '||trim(LS_STR002)||' '||trim(LS_STR003) || ' group by a.GHKS00,a.GHLB00'; elsif V_KSYSBZ = 2 then PSQLTEXT := 'insert into BM_YYSFTJ ('||trim(LS_STR000)||',SFXM99) '||trim(LS_STR001)||',a.JZYS00 '||trim(LS_STR002)||' '||trim(LS_STR003) || ' group by a.JZYS00,a.GHLB00,a.GHKS00'; else PSQLTEXT := 'insert into BM_YYSFTJ ('||trim(LS_STR000)||',SFXM99) '||trim(LS_STR001)||',a.JZYS00 '||trim(LS_STR002)||' '||trim(LS_STR003) || ' group by a.GHKS00,a.GHLB00,a.JZYS00'; end if; SP_EXECUTE_SQL(PSQLTEXT); ------------退号统计---end-------------------------------------- -----------------------------------------------------统计总表begin-------------------------------------------------------- LS_STR000 := ''; LS_STR001 := ''; --总费用 LS_STR000 := LS_STR000||'TEMP'||trim(to_char(LS_YLFYIN,'00'))||','; LS_STR001 := LS_STR001||'trim(to_char(sum(SFXM03),''999999990.00'')),'; --药品费用 LS_STR000 := LS_STR000||'TEMP'||trim(to_char(LS_YPFYIN,'00'))||','; LS_STR001 := LS_STR001||'trim(to_char(sum(SFXM04),''999999990.00'')),'; LS_STR000 := LS_STR000||'TEMP'||trim(to_char(LS_YPBTC0,'00'))||','; if LS_SFKCZY='Y' then --除草药比率 LS_STR001 := LS_STR001||'trim(decode(sum(SFXM03-SFXM100),0,''0'',to_char(sum(SFXM09)*100/sum(SFXM03-SFXM100),''fm99999990.00''))||''%''),'; else LS_STR001 := LS_STR001||'trim(decode(sum(SFXM03-SFXM100),0,''0'',to_char(sum(SFXM04)*100/sum(SFXM03-SFXM100),''fm99999990.00''))||''%''),'; end if; --FLAG00 统计标识,GHHJ00 总挂号数,FYHJ00 总费用,TEMP96 总处方数,TEMP45 耗材费,TEMP47 耗材比率,TEMP100指定项目,TEMP101 西药费,TEMP102 草药费,TEMP103 成药费,TEMP111基药费,TEMP112基药占药品比率 LS_STR000 := 'FLAG00,GHHJ00,FYHJ00,TEMP96,'||trim(LS_STR000)||'TEMP45,TEMP47,TEMP100,TEMP101,TEMP102,TEMP103,TEMP111,TEMP112,TEMP120,TEMP121'; LS_STR001 := 'select '''||trim(C_FLAG00)||'T'||''',sum(SFXM02),sum(SFXM03),sum(SFXM96),'||trim(LS_STR001)|| 'sum(SFXM40),decode(sum(SFXM03),0,''0'',to_char(sum(SFXM40)*100/sum(SFXM03),''fm99999990.00''))||''%'','|| 'sum(SFXM100),sum(SFXM101),sum(SFXM07),sum(SFXM08),sum(SFXM111),trim(decode(sum(SFXM04),0,''0'',to_char(sum(SFXM111)*100/sum(SFXM04),''fm99999990.00''))||''%''),sum(SFXM107),sum(SFXM110)'; if V_KSYSBZ = 0 then --科室 PSQLTEXT := 'insert into BM_ZHTJ00_TEMP00('||trim(LS_STR000)||',BMBH00) '||trim(LS_STR001)|| ',BMBH00 from BM_YYSFTJ a where a.ID0000='||C_IDQN00||' group by a.BMBH00'; SP_EXECUTE_SQL(PSQLTEXT); PSQLTEXT := 'insert into BM_ZHTJ00_TEMP00('||trim(LS_STR000)||',BMBH00,BMMC00) '||trim(LS_STR001)|| ',-9999,''全院'' from BM_YYSFTJ a where a.ID0000='||C_IDQN00; SP_EXECUTE_SQL(PSQLTEXT); elsif V_KSYSBZ = 2 then --医生、科室 --小计 PSQLTEXT := 'insert into BM_ZHTJ00_TEMP00('||trim(LS_STR000)||',BMBH00,TEMP98) '||trim(LS_STR001)|| ',decode(SFXM99,0,99999,SFXM99),-9999 from BM_YYSFTJ a where a.ID0000='||C_IDQN00||' group by a.SFXM99'; SP_EXECUTE_SQL(PSQLTEXT); --全院 PSQLTEXT := 'insert into BM_ZHTJ00_TEMP00('||trim(LS_STR000)||',BMBH00,TEMP99) '||trim(LS_STR001)|| ',-9999,''全院'' from BM_YYSFTJ a where a.ID0000='||C_ID0000; SP_EXECUTE_SQL(PSQLTEXT); --明细 PSQLTEXT := 'insert into BM_ZHTJ00_TEMP00('||trim(LS_STR000)||',BMBH00,TEMP98,BMMC00) '||trim(LS_STR001)|| ',decode(SFXM99,0,99999,SFXM99),decode(BMBH00,0,99999,BMBH00),''其他'' from BM_YYSFTJ a where a.ID0000='||C_IDQN00||' group by a.SFXM99,a.BMBH00'; SP_EXECUTE_SQL(PSQLTEXT); else --科室、医生 --小计 PSQLTEXT := 'insert into BM_ZHTJ00_TEMP00('||trim(LS_STR000)||',BMBH00,TEMP98) '||trim(LS_STR001)|| ',decode(BMBH00,0,99999,BMBH00),-9999 from BM_YYSFTJ a where a.ID0000='||C_IDQN00||' group by a.BMBH00'; SP_EXECUTE_SQL(PSQLTEXT); --全院 PSQLTEXT := 'insert into BM_ZHTJ00_TEMP00('||trim(LS_STR000)||',BMBH00,BMMC00) '||trim(LS_STR001)|| ',-9999,''全院'' from BM_YYSFTJ a where a.ID0000='||C_IDQN00; SP_EXECUTE_SQL(PSQLTEXT); --明细 PSQLTEXT := 'insert into BM_ZHTJ00_TEMP00('||trim(LS_STR000)||',BMBH00,TEMP98,TEMP99) '||trim(LS_STR001)|| ',decode(BMBH00,0,99999,BMBH00),decode(SFXM99,0,99999,SFXM99),''其他'' from BM_YYSFTJ a where a.ID0000='||C_ID0000||' group by a.BMBH00,a.SFXM99'; SP_EXECUTE_SQL(PSQLTEXT); end if; --2016.01.25 daihq 增加 TEMP104 实际挂号数 if V_KSYSBZ = 0 then --实际挂号数统计 --全院 PSQLTEXT:='UPDATE BM_ZHTJ00_TEMP00 SET TEMP104'|| '=nvl((select trim(to_char(sum(SFXM02),''9999999999'')) from BM_YYSFTJ where ID0000='||trim(to_char(C_IDQN00))|| ' and SFXM01 not in ('||trim(ls_MZRBSFJSSJGHS)||')),''0'') where FLAG00='''|| trim(C_FLAG00)||'T'||''' and BMBH00=-9999 '; SP_EXECUTE_SQL(PSQLTEXT); --部门 PSQLTEXT:='UPDATE BM_ZHTJ00_TEMP00 SET TEMP104'|| '=nvl((select trim(to_char(sum(SFXM02),''9999999999'')) from BM_YYSFTJ where ID0000='||trim(to_char(C_IDQN00))|| ' and BMBH00=BM_ZHTJ00_TEMP00.BMBH00 and SFXM01 not in ('||trim(ls_MZRBSFJSSJGHS)||')),''0'') where FLAG00='''|| trim(C_FLAG00)||'T'||''' and BMBH00<>-9999'; SP_EXECUTE_SQL(PSQLTEXT); --全院 --平均费用 PSQLTEXT:='UPDATE BM_ZHTJ00_TEMP00 SET TEMP'||trim(to_char(LS_PJFYC0,'00'))|| '=decode(to_number(TEMP104),0,''0.00'',trim(to_char(round((to_number(FYHJ00)-to_number(TEMP100))/to_number(TEMP104),2),''999999990.00''))) where FLAG00='''|| trim(C_FLAG00)||'T'||''' and BMBH00=-9999 '; SP_EXECUTE_SQL(PSQLTEXT); --药品平均费用 PSQLTEXT:='UPDATE BM_ZHTJ00_TEMP00 SET TEMP'||trim(to_char(LS_YPPJFC,'00'))|| '=decode(TEMP104,0,''0'',to_char(TEMP'||trim(to_char(LS_YPFYIN,'00'))||'/TEMP104,''fm99999990.00'')) where FLAG00='''|| trim(C_FLAG00)||'T'||''' and BMBH00=-9999 '; SP_EXECUTE_SQL(PSQLTEXT); --今年和去年药品比率差 TEMP105 PSQLTEXT:='UPDATE BM_ZHTJ00_TEMP00 A SET TEMP105'|| '=(select to_char(to_number(replace(A.TEMP'||trim(to_char(LS_YPBTC0,'00'))||',''%''))- '|| ' to_number(replace(TEMP'||trim(to_char(LS_YPBTC0,'00'))||',''%'')),''9990.00'')||''%'' from BM_ZHTJ00_TEMP00 '|| ' where FLAG00='''||trim(C_FLAG00)||'T'||''' and BMBH00=-9999 )'|| ' where FLAG00='''||trim(C_FLAG00)||''' and BMBH00=-9999 '; SP_EXECUTE_SQL(PSQLTEXT); --今年和去年平均费用差 TEMP106 PSQLTEXT:='UPDATE BM_ZHTJ00_TEMP00 A SET TEMP106'|| '=(select to_char(to_number(A.TEMP'||trim(to_char(LS_PJFYC0,'00'))||')- '|| ' to_number(TEMP'||trim(to_char(LS_PJFYC0,'00'))||'),''9990.00'')from BM_ZHTJ00_TEMP00 '|| ' where FLAG00='''||trim(C_FLAG00)||'T'||''' and BMBH00=-9999 )'|| ' where FLAG00='''||trim(C_FLAG00)||''' and BMBH00=-9999 '; SP_EXECUTE_SQL(PSQLTEXT); --今年和去年药品平均费用差 TEMP107 PSQLTEXT:='UPDATE BM_ZHTJ00_TEMP00 A SET TEMP107'|| '=(select to_char(to_number(A.TEMP'||trim(to_char(LS_YPPJFC,'00'))||')- '|| ' to_number(TEMP'||trim(to_char(LS_YPPJFC,'00'))||'),''9990.00'') from BM_ZHTJ00_TEMP00 '|| ' where FLAG00='''||trim(C_FLAG00)||'T'||''' and BMBH00=-9999 )'|| ' where FLAG00='''||trim(C_FLAG00)||''' and BMBH00=-9999 '; SP_EXECUTE_SQL(PSQLTEXT); --部门 --平均费用 PSQLTEXT:='UPDATE BM_ZHTJ00_TEMP00 SET TEMP'||trim(to_char(LS_PJFYC0,'00'))|| '=decode(to_number(TEMP104),0,''0.00'',trim(to_char(round((to_number(FYHJ00)-to_number(TEMP100))/to_number(TEMP104),2),''999999990.00''))) where FLAG00='''|| trim(C_FLAG00)||'T'||''' and BMBH00<>-9999 '; SP_EXECUTE_SQL(PSQLTEXT); --药品平均费用 PSQLTEXT:='UPDATE BM_ZHTJ00_TEMP00 SET TEMP'||trim(to_char(LS_YPPJFC,'00'))|| '=decode(TEMP104,0,''0'',to_char(TEMP'||trim(to_char(LS_YPFYIN,'00'))||'/TEMP104,''fm99999990.00'')) where FLAG00='''|| trim(C_FLAG00)||'T'||''' and BMBH00<>-9999 '; SP_EXECUTE_SQL(PSQLTEXT); --今年和去年药品比率差 TEMP105 PSQLTEXT:='UPDATE BM_ZHTJ00_TEMP00 A SET TEMP105'|| '=(select to_char(to_number(replace(A.TEMP'||trim(to_char(LS_YPBTC0,'00'))||',''%''))- '|| ' to_number(replace(TEMP'||trim(to_char(LS_YPBTC0,'00'))||',''%'')),''9990.00'')||''%'' from BM_ZHTJ00_TEMP00 '|| ' where FLAG00='''||trim(C_FLAG00)||'T'||''' and BMBH00<>-9999 and BMBH00=A.BMBH00)'|| ' where FLAG00='''||trim(C_FLAG00)||''' and BMBH00<>-9999 '; SP_EXECUTE_SQL(PSQLTEXT); --今年和去年平均费用差 TEMP106 PSQLTEXT:='UPDATE BM_ZHTJ00_TEMP00 A SET TEMP106'|| '=(select to_char(to_number(A.TEMP'||trim(to_char(LS_PJFYC0,'00'))||')- '|| ' to_number(TEMP'||trim(to_char(LS_PJFYC0,'00'))||'),''9990.00'') from BM_ZHTJ00_TEMP00 '|| ' where FLAG00='''||trim(C_FLAG00)||'T'||''' and BMBH00<>-9999 and BMBH00=A.BMBH00)'|| ' where FLAG00='''||trim(C_FLAG00)||''' and BMBH00<>-9999 '; SP_EXECUTE_SQL(PSQLTEXT); --今年和去年药品平均费用差 TEMP107 PSQLTEXT:='UPDATE BM_ZHTJ00_TEMP00 A SET TEMP107'|| '=(select to_char(to_number(A.TEMP'||trim(to_char(LS_YPPJFC,'00'))||')- '|| ' to_number(TEMP'||trim(to_char(LS_YPPJFC,'00'))||'),''9990.00'') from BM_ZHTJ00_TEMP00 '|| ' where FLAG00='''||trim(C_FLAG00)||'T'||''' and BMBH00<>-9999 and BMBH00=A.BMBH00)'|| ' where FLAG00='''||trim(C_FLAG00)||''' and BMBH00<>-9999 '; SP_EXECUTE_SQL(PSQLTEXT); else --小计 PSQLTEXT:='UPDATE BM_ZHTJ00_TEMP00 SET TEMP104'|| '=nvl((select trim(to_char(sum(SFXM02),''9999999999'')) from BM_YYSFTJ where ID0000='||trim(to_char(C_IDQN00))|| ' and BMBH00=BM_ZHTJ00_TEMP00.BMBH00 and SFXM01 not in ('||trim(ls_MZRBSFJSSJGHS)||')),''0'') where FLAG00='''|| trim(C_FLAG00)||'T'||''' and BMBH00<>-9999 and to_number(TEMP98)=-9999 '; SP_EXECUTE_SQL(PSQLTEXT); --全院 PSQLTEXT:='UPDATE BM_ZHTJ00_TEMP00 SET TEMP104'|| '=nvl((select trim(to_char(sum(SFXM02),''9999999999'')) from BM_YYSFTJ where ID0000='||trim(to_char(C_IDQN00))|| ' and SFXM01 not in ('||trim(ls_MZRBSFJSSJGHS)||')),''0'') where FLAG00='''|| trim(C_FLAG00)||'T'||''' and BMBH00=-9999 '; SP_EXECUTE_SQL(PSQLTEXT); --部门 PSQLTEXT:='UPDATE BM_ZHTJ00_TEMP00 SET TEMP104'|| '=nvl((select trim(to_char(sum(SFXM02),''9999999999'')) from BM_YYSFTJ where ID0000='||trim(to_char(C_IDQN00))|| ' and BMBH00=BM_ZHTJ00_TEMP00.BMBH00 and SFXM99=to_number(BM_ZHTJ00_TEMP00.TEMP98) and SFXM01 not in ('||trim(ls_MZRBSFJSSJGHS)||')),''0'') where FLAG00='''|| trim(C_FLAG00)||'T'||''' and BMBH00<>-9999 and to_number(TEMP98)<>-9999 '; SP_EXECUTE_SQL(PSQLTEXT); --平均费用 PSQLTEXT:='UPDATE BM_ZHTJ00_TEMP00 SET TEMP'||trim(to_char(LS_PJFYC0,'00'))|| '=decode(to_number(TEMP104),0,''0.00'',trim(to_char(round((to_number(FYHJ00)-to_number(TEMP100))/to_number(TEMP104),2),''999999990.00''))) where FLAG00='''|| trim(C_FLAG00)||'T'||''' and BMBH00<>-9999 and to_number(TEMP98)=-9999 '; SP_EXECUTE_SQL(PSQLTEXT); --药品平均费用 PSQLTEXT:='UPDATE BM_ZHTJ00_TEMP00 SET TEMP'||trim(to_char(LS_YPPJFC,'00'))|| '=decode(TEMP104,0,''0'',to_char(TEMP'||trim(to_char(LS_YPFYIN,'00'))||'/TEMP104,''fm99999990.00'')) where FLAG00='''|| trim(C_FLAG00)||'T'||''' and BMBH00<>-9999 and to_number(TEMP98)=-9999 '; SP_EXECUTE_SQL(PSQLTEXT); --今年和去年药品比率差 TEMP105 PSQLTEXT:='UPDATE BM_ZHTJ00_TEMP00 A SET TEMP105'|| '=(select to_char(to_number(replace(A.TEMP'||trim(to_char(LS_YPBTC0,'00'))||',''%''))- '|| ' to_number(replace(TEMP'||trim(to_char(LS_YPBTC0,'00'))||',''%'')),''9990.00'')||''%'' from BM_ZHTJ00_TEMP00 '|| ' where FLAG00='''||trim(C_FLAG00)||'T'||''' and BMBH00=-9999 and to_number(TEMP98)=-9999 )'|| ' where FLAG00='''||trim(C_FLAG00)||''' and BMBH00=-9999 and to_number(TEMP98)=-9999'; SP_EXECUTE_SQL(PSQLTEXT); --今年和去年平均费用差 TEMP106 PSQLTEXT:='UPDATE BM_ZHTJ00_TEMP00 A SET TEMP106'|| '=(select to_char(to_number(A.TEMP'||trim(to_char(LS_PJFYC0,'00'))||')- '|| ' to_number(TEMP'||trim(to_char(LS_PJFYC0,'00'))||'),''9990.00'')from BM_ZHTJ00_TEMP00 '|| ' where FLAG00='''||trim(C_FLAG00)||'T'||''' and BMBH00=-9999 and to_number(TEMP98)=-9999)'|| ' where FLAG00='''||trim(C_FLAG00)||''' and BMBH00=-9999 and to_number(TEMP98)=-9999'; SP_EXECUTE_SQL(PSQLTEXT); --今年和去年药品平均费用差 TEMP107 PSQLTEXT:='UPDATE BM_ZHTJ00_TEMP00 A SET TEMP107'|| '=(select to_char(to_number(A.TEMP'||trim(to_char(LS_YPPJFC,'00'))||')- '|| ' to_number(TEMP'||trim(to_char(LS_YPPJFC,'00'))||'),''9990.00'') from BM_ZHTJ00_TEMP00 '|| ' where FLAG00='''||trim(C_FLAG00)||'T'||''' and BMBH00=-9999 and to_number(TEMP98)=-9999)'|| ' where FLAG00='''||trim(C_FLAG00)||''' and BMBH00=-9999 and to_number(TEMP98)=-9999'; SP_EXECUTE_SQL(PSQLTEXT); --平均费用 PSQLTEXT:='UPDATE BM_ZHTJ00_TEMP00 SET TEMP'||trim(to_char(LS_PJFYC0,'00'))|| '=decode(to_number(TEMP104),0,''0.00'',trim(to_char(round((to_number(FYHJ00)-to_number(TEMP100))/to_number(TEMP104),2),''999999990.00''))) where FLAG00='''|| trim(C_FLAG00)||'T'||''' and BMBH00=-9999 '; SP_EXECUTE_SQL(PSQLTEXT); --药品平均费用 PSQLTEXT:='UPDATE BM_ZHTJ00_TEMP00 SET TEMP'||trim(to_char(LS_YPPJFC,'00'))|| '=decode(TEMP104,0,''0'',to_char(TEMP'||trim(to_char(LS_YPFYIN,'00'))||'/TEMP104,''fm99999990.00'')) where FLAG00='''|| trim(C_FLAG00)||'T'||''' and BMBH00=-9999 '; SP_EXECUTE_SQL(PSQLTEXT); --今年和去年药品比率差 TEMP105 PSQLTEXT:='UPDATE BM_ZHTJ00_TEMP00 A SET TEMP105'|| '=(select to_char(to_number(replace(A.TEMP'||trim(to_char(LS_YPBTC0,'00'))||',''%''))- '|| ' to_number(replace(TEMP'||trim(to_char(LS_YPBTC0,'00'))||',''%'')),''9990.00'')||''%'' from BM_ZHTJ00_TEMP00 '|| ' where FLAG00='''||trim(C_FLAG00)||'T'||''' and BMBH00=-9999 )'|| ' where FLAG00='''||trim(C_FLAG00)||''' and BMBH00=-9999 '; SP_EXECUTE_SQL(PSQLTEXT); --今年和去年平均费用差 TEMP106 PSQLTEXT:='UPDATE BM_ZHTJ00_TEMP00 A SET TEMP106'|| '=(select to_char(to_number(A.TEMP'||trim(to_char(LS_PJFYC0,'00'))||')- '|| ' to_number(TEMP'||trim(to_char(LS_PJFYC0,'00'))||'),''9990.00'')from BM_ZHTJ00_TEMP00 '|| ' where FLAG00='''||trim(C_FLAG00)||'T'||''' and BMBH00=-9999 )'|| ' where FLAG00='''||trim(C_FLAG00)||''' and BMBH00=-9999 '; SP_EXECUTE_SQL(PSQLTEXT); --今年和去年药品平均费用差 TEMP107 PSQLTEXT:='UPDATE BM_ZHTJ00_TEMP00 A SET TEMP107'|| '=(select to_char(to_number(A.TEMP'||trim(to_char(LS_YPPJFC,'00'))||')- '|| ' to_number(TEMP'||trim(to_char(LS_YPPJFC,'00'))||'),''9990.00'') from BM_ZHTJ00_TEMP00 '|| ' where FLAG00='''||trim(C_FLAG00)||'T'||''' and BMBH00=-9999 )'|| ' where FLAG00='''||trim(C_FLAG00)||''' and BMBH00=-9999 '; SP_EXECUTE_SQL(PSQLTEXT); --平均费用 PSQLTEXT:='UPDATE BM_ZHTJ00_TEMP00 SET TEMP'||trim(to_char(LS_PJFYC0,'00'))|| '=decode(to_number(TEMP104),0,''0.00'',trim(to_char(round((to_number(FYHJ00)-to_number(TEMP100))/to_number(TEMP104),2),''999999990.00''))) where FLAG00='''|| trim(C_FLAG00)||'T'||''' and BMBH00<>-9999 and to_number(TEMP98)<>-9999 '; SP_EXECUTE_SQL(PSQLTEXT); --药品平均费用 PSQLTEXT:='UPDATE BM_ZHTJ00_TEMP00 SET TEMP'||trim(to_char(LS_YPPJFC,'00'))|| '=decode(TEMP104,0,''0'',to_char(TEMP'||trim(to_char(LS_YPFYIN,'00'))||'/TEMP104,''fm99999990.00'')) where FLAG00='''|| trim(C_FLAG00)||'T'||''' and BMBH00<>-9999 and to_number(TEMP98)<>-9999 '; SP_EXECUTE_SQL(PSQLTEXT); --今年和去年药品比率差 TEMP105 PSQLTEXT:='UPDATE BM_ZHTJ00_TEMP00 A SET TEMP105'|| '=(select to_char(to_number(replace(A.TEMP'||trim(to_char(LS_YPBTC0,'00'))||',''%''))- '|| ' to_number(replace(TEMP'||trim(to_char(LS_YPBTC0,'00'))||',''%'')),''9990.00'')||''%'' from BM_ZHTJ00_TEMP00 '|| ' where FLAG00='''||trim(C_FLAG00)||'T'||''' and BMBH00<>-9999 and to_number(TEMP98)<>-9999 and BMBH00=A.BMBH00 and TEMP98=a.TEMP98)'|| ' where FLAG00='''||trim(C_FLAG00)||''' and BMBH00<>-9999 and to_number(TEMP98)<>-9999 '; SP_EXECUTE_SQL(PSQLTEXT); --今年和去年平均费用差 TEMP106 PSQLTEXT:='UPDATE BM_ZHTJ00_TEMP00 A SET TEMP106'|| '=(select to_char(to_number(A.TEMP'||trim(to_char(LS_PJFYC0,'00'))||')- '|| ' to_number(TEMP'||trim(to_char(LS_PJFYC0,'00'))||'),''9990.00'')from BM_ZHTJ00_TEMP00 '|| ' where FLAG00='''||trim(C_FLAG00)||'T'||''' and BMBH00<>-9999 and to_number(TEMP98)<>-9999 and BMBH00=A.BMBH00 and TEMP98=a.TEMP98)'|| ' where FLAG00='''||trim(C_FLAG00)||''' and BMBH00<>-9999 and to_number(TEMP98)<>-9999 '; SP_EXECUTE_SQL(PSQLTEXT); --今年和去年药品平均费用差 TEMP107 PSQLTEXT:='UPDATE BM_ZHTJ00_TEMP00 A SET TEMP107'|| '=(select to_char(to_number(A.TEMP'||trim(to_char(LS_YPPJFC,'00'))||')- '|| ' to_number(TEMP'||trim(to_char(LS_YPPJFC,'00'))||'),''9990.00'') from BM_ZHTJ00_TEMP00 '|| ' where FLAG00='''||trim(C_FLAG00)||'T'||''' and BMBH00<>-9999 and to_number(TEMP98)<>-9999 and BMBH00=A.BMBH00 and TEMP98=a.TEMP98)'|| ' where FLAG00='''||trim(C_FLAG00)||''' and BMBH00<>-9999 and to_number(TEMP98)<>-9999 '; SP_EXECUTE_SQL(PSQLTEXT); end if; -----------------------------------------------------统计总表end-------------------------------------------------------- end if; -----------------------------------------------------统计今年去年比较end---------------------------------------------- --删除临时表数据 delete from BM_YYSFTJ where ID0000=C_ID0000; --insert into bm_zhtj00_temp00_back select * from BM_ZHTJ00_TEMP00; commit; exception --when E_ex then -- RAISE_APPLICATION_ERROR(-20002,substr(LS_STR001,1,50)); -- rollback; when OTHERS then RAISE_APPLICATION_ERROR(-20001,substr('插入数据错误！*SP_ZH_MZXXCX('||V_FLAG00||','||V_KSYSBZ||')*'||SQLCODE||SQLERRM,1,50)); rollback; end;