prompt 统计门诊医院的药占比 SP_YS_ZLYZBSJ_LN create or replace procedure SP_YS_ZLYZBSJ_LN(ad_TS0000 number default 1) --ad_TS0000 整理数据天数 as lv_MZYPSR SF_FYMX00.HJJE00%type; lv_MZZSR0 SF_BRFY00.HJJE00%type; ls_STARDATE char(8); ls_ENDDATE char(8); ls_SYSDATE char(8); ls_YZB000 YS_TJYZB0_TEMP00.YZB000%type; ls_KDYSXM BM_YGBM00.ZWXM00%type; ls_ZDXMBH varchar2(4000); lv_ZDXMFY SF_BRFY00.HJJE00%type; lv_CJFY00 YS_TJYZB0_TEMP00.CJFY00%type; lv_GHCS00 number(6); lv_MZZFY0 SF_FYMX00.HJJE00%type; lv_count number; --指定项目费用，该费用不参与药占比 cursor C_ZDXM is select BH0000 from bm_tyzd00 where zdmc00='医疗收费编码或药品编码指定' and yxbz00='1'; v_YYID00 XT_YYXX00.YYID00%TYPE; --医院ID --MODIFICATION HISTORY -- Person Date Comments -- dsm 2016.05.11 create for MZYS-20160504-001 -- cc 2016.07.19 除省老年外，其他医院不统计耗材(hcfy00)与低价药（BM_YD0000.EYPMC0=‘低价药’）这两指标 begin select count(*) into lv_count from BM_YSFYTJ where rownum<10; ls_STARDATE := to_char(trunc(sysdate ,'YYYY'),'YYYYMMDD'); select nvl(max(RQ0000),ls_STARDATE) into ls_STARDATE from BM_YSFYTJ where RQ0000>=ls_STARDATE; if lv_count>0 then ls_STARDATE:=to_char(to_date(ls_STARDATE,'YYYYMMDD')+1,'YYYYMMDD'); end if; ls_ENDDATE := to_char(to_date(ls_STARDATE,'YYYYMMDD')+ad_TS0000-1,'YYYYMMDD'); if ls_ENDDATE>=to_char(sysdate,'YYYYMMDD') then ls_ENDDATE:=to_char(sysdate-1,'YYYYMMDD'); end if; ls_SYSDATE := to_char(sysdate,'YYYYMMDD'); ls_ZDXMBH := ','; lv_GHCS00 := 0; for V_ZDXM in C_ZDXM loop ls_ZDXMBH:=ls_ZDXMBH||trim(V_ZDXM.BH0000)||','; end loop; while ls_STARDATE<=ls_ENDDATE loop select trim(YYID00) into v_YYID00 from xt_yyxx00 where flag00='1' and ROWNUM=1; --lv_MZYPSR := MZYP.HJJE00; if v_YYID00='220035' then --门诊总收入 insert into BM_YSFYTJ(RQ0000,YSBH00,ZFY000,BCYFY0,YPFY00,HCFY00) select ls_STARDATE,b.KDYS00,nvl(sum(nvl(b.HJJE00,0)),0),sum(decode(instr(ls_ZDXMBH,','||trim(to_char(b.xmbh00))||','),0,0,b.hjje00)) ,sum(decode(sign(b.XMBH00-9999999997),-1,0,b.HJJE00)) ,sum(decode(e.YJHSBH,'23',b.HJJE00,'25',b.HJJE00,0)) from SF_BRFY00 a,SF_FYMX00 b,BM_YYSFXM d,VW_BM_YJHSXM e --,bm_brxxb0 c where a.DJH000=b.DJH000 and a.CZRQ00 >= ls_STARDATE and a.CZRQ00 <= ls_STARDATE --and a.brid00=c.brid00 and b.XMBH00=d.SFXMID and d.HSXMID=e.HSXMID(+) group by b.KDYS00; --and (c.fbbh00, c.yblb00) IN ( SELECT fbbh00, yblb00 FROM ic_ybbrlb -- where ybzxlb not in ('d','a','J','5','4','3')) group by b.KDYS00; else --门诊总收入 insert into BM_YSFYTJ(RQ0000,YSBH00,ZFY000,BCYFY0,YPFY00,HCFY00) select ls_STARDATE,b.KDYS00,nvl(sum(nvl(b.HJJE00,0)),0),sum(decode(instr(ls_ZDXMBH,','||trim(to_char(b.xmbh00))||','),0,0,b.hjje00)) ,sum(decode(sign(b.XMBH00-9999999997),-1,0,b.HJJE00)) --,sum(decode(e.YJHSBH,'23',b.HJJE00,'25',b.HJJE00,0)) --除省老年外，其他医院不统计耗材的费用 update by cc ,0 from SF_BRFY00 a,SF_FYMX00 b,BM_YYSFXM d--,VW_BM_YJHSXM e --,bm_brxxb0 c where a.DJH000=b.DJH000 and a.CZRQ00 >= ls_STARDATE and a.CZRQ00 <= ls_STARDATE --and a.brid00=c.brid00 and b.XMBH00=d.SFXMID --and d.HSXMID=e.HSXMID(+) group by b.KDYS00; --and (c.fbbh00, c.yblb00) IN ( SELECT fbbh00, yblb00 FROM ic_ybbrlb -- where ybzxlb not in ('d','a','J','5','4','3')) group by b.KDYS00; end if; --挂号次数 update BM_YSFYTJ a set GHCS00=( select sum(GHCS00) from VW_SF_GHLSXX g where g.CZRQ00=ls_STARDATE and g.JZYS00=a.YSBH00 ) where RQ0000=ls_STARDATE ; insert into BM_YSFYTJ(RQ0000,GHCS00,YSBH00) select ls_STARDATE,sum(GHCS00) GHCS00,g.JZYS00 from VW_SF_GHLSXX g where g.CZRQ00=ls_STARDATE and not exists(select 1 from BM_YSFYTJ where RQ0000=ls_STARDATE and YSBH00=g.JZYS00) group by g.JZYS00 ; for c_ypje in ( select YSGZH0,sum(JCJE00) JCJE00,sum(DJJE00) DJJE00 from ( select c.YSGZH0, decode(e.YPXZ00,'1',round(d.LSDJ00*d.YPZSL0,2),'2',round(d.LSDJ00*d.YPZSL0,2),0) JCJE00, decode(e.EYPMC0,'低价药',round(d.LSDJ00*d.YPZSL0,2),0) DJJE00 from SF_BRFY00 a,YF_MZCF00 c,YF_MZCFMX d,BM_YD0000 e where a.CZRQ00=ls_STARDATE and a.DJH000=c.DJH000 and c.CFLSH0=d.CFLSH0 and d.YPNM00=e.YPNM00 ) group by YSGZH0) loop update BM_YSFYTJ set JCYPFY=c_ypje.JCJE00, DJYPFY=c_ypje.DJJE00 where YSBH00=c_ypje.YSGZH0 and RQ0000=ls_STARDATE; if SQL%NOTFOUND then insert into BM_YSFYTJ(RQ0000,YSBH00,JCYPFY,DJYPFY) values(ls_STARDATE,c_ypje.YSGZH0,c_ypje.JCJE00,c_ypje.DJJE00); end if; end loop; select count(*) into lv_count from BM_YSFYTJ where RQ0000=ls_STARDATE; if lv_count=0 then insert into BM_YSFYTJ(YSBH00,RQ0000) values(0,ls_STARDATE); end if; ls_STARDATE:=to_char(to_date(ls_STARDATE,'YYYYMMDD')+1,'YYYYMMDD'); commit; end loop; exception when others then --raise_application_error(-20002,substrb(nvl(sqlerrm, '原因不明出错')||'!*',1,220)); rollback; end; / --%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% prompt 获取医生本年药占比 create or replace function SF_YS_getYSBNYZB(ad_YSBH00 in number) return varchar2 is ls_STARDATE varchar2(8); ls_ZFY000 SF_BRFY00.HJJE00%type; ls_BCYFY0 SF_BRFY00.HJJE00%type; ls_GHCS00 BM_YSFYTJ.GHCS00%type; ls_YPFY00 SF_BRFY00.HJJE00%type; ls_JCYPFY SF_BRFY00.HJJE00%type; ls_DJYPFY SF_BRFY00.HJJE00%type; ls_HCFY00 SF_BRFY00.HJJE00%type; ls_result varchar2(500); ls_count number; v_YYID00 XT_YYXX00.YYID00%TYPE; --医院ID --MODIFICATION HISTORY -- Person Date Comments -- dsm 2016.05.11 create for MZYS-20160504-001 -- cc 2016.07.19 除省老年外，其他医院不显示耗材(hcfy00)费用与低价药（BM_YD0000.EYPMC0=‘低价药’）这两指标 begin ls_result:=''; select count(*) into ls_count from XT_XTCS00 where NAME00='YS_SFXSQNYZB' and VALUE0='Y'; if ls_count>0 then ls_STARDATE := to_char(trunc(sysdate ,'YYYY'),'YYYYMMDD'); select trim(YYID00) into v_YYID00 from xt_yyxx00 where flag00='1' and ROWNUM=1; if v_YYID00='220035' then select sum(ZFY000) ,sum(BCYFY0) ,sum(GHCS00) ,sum(YPFY00) ,sum(JCYPFY) ,sum(DJYPFY) ,sum(HCFY00) into ls_ZFY000,ls_BCYFY0,ls_GHCS00,ls_YPFY00,ls_JCYPFY,ls_DJYPFY,ls_HCFY00 from BM_YSFYTJ where RQ0000>=ls_STARDATE and RQ0000=ls_STARDATE and RQ0000