CREATE OR REPLACE PROCEDURE SP_YS_TJYZB0
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;
  --门诊药品收入
  cursor CUR_SF_FYMX00_YP is select nvl(sum(a.HJJE00),0) HJJE00,a.KDYS00 from SF_FYMX00 a,sf_brfy00 b,bm_brxxb0  c
  where a.CZRQ00 >= ls_STARDATE and a.CZRQ00 <= ls_ENDDATE and XMBH00>=9999999997 and a.djh000=b.djh000  and b.brid00=c.brid00
   and (c.fbbh00, c.yblb00) IN ( SELECT fbbh00, yblb00  FROM ic_ybbrlb  where ybzxlb not in ('d','a','J','5','4','3'))
  group by KDYS00;                                   
  --指定项目费用，该费用不参与药占比
  cursor C_ZDXM is
  select BH0000 from bm_tyzd00 where zdmc00='医疗收费编码或药品编码指定' and yxbz00='1';
--MODIFICATION HISTORY
-- Person      Date         Comments
-- daihq       2013.07.23   create by MZYS-20130717-001
-- daihq       2013.09.10   增加计算次均费用 by MZYS-20130904-001
begin
	ls_STARDATE := to_char(trunc(sysdate ,'YYYY'),'YYYYMMDD');
	ls_ENDDATE := to_char(sysdate - 1,'YYYYMMDD');
	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;
	delete from YS_TJYZB0_TEMP00 where TJRQ00 <= to_char(sysdate-7,'YYYYMMDD');
	for MZYP in CUR_SF_FYMX00_YP loop
	 lv_MZYPSR := MZYP.HJJE00;
	  --门诊总收入
	  select nvl(sum(nvl(b.HJJE00,0)),0),sum(decode(instr(ls_ZDXMBH,','||trim(to_char(b.xmbh00))||','),0,0,b.hjje00))
	  into lv_MZZSR0,lv_ZDXMFY from SF_BRFY00 a,SF_FYMX00 b,bm_brxxb0 c 
	  where a.DJH000=b.DJH000 and a.CZRQ00 >= ls_STARDATE and a.CZRQ00 <= ls_ENDDATE
	    and a.CZRQ00||a.CZSJ00 >= ls_STARDATE||'00:00:00' and a.CZRQ00||a.CZSJ00 <= ls_ENDDATE||'23:59:59'
	    and b.KDYS00=MZYP.KDYS00
	    and a.brid00=c.brid00
       and (c.fbbh00, c.yblb00) IN ( SELECT fbbh00, yblb00  FROM ic_ybbrlb 
            where ybzxlb not in ('d','a','J','5','4','3'));
	  --均次费用
	  --select nvl(round(decode(SFXM01,0,0,SFXM02/SFXM01),2),0) into lv_CJFY00 from (
	  --select sum(nvl(SFXM01,0)) SFXM01,sum(nvl(SFXM02,0)) SFXM02 from BM_YSCJFY
	  --where TJRQ00 >= to_char(trunc(sysdate,'YYYY'),'YYYYMM') and TJRQ00 <= to_char(sysdate,'YYYYMM') and SFXM99=MZYP.KDYS00
	  --);
	  --总费用 计算方式来自 SP_YS_GRCJFY
	  select nvl(sum(nvl(b.HJJE00,0)),0) into lv_MZZFY0 from SF_BRFY00 a,SF_FYMX00 b,BM_BRXXB0 e,VW_XT_YBBRLB f
	  where a.DJH000=b.DJH000 and a.CZRQ00 >= ls_STARDATE and a.CZRQ00 <= ls_ENDDATE
      and a.BRID00=e.BRID00 and e.FBBH00=f.FBBH00 and e.YBLB00=f.YBLB00
	    and a.CZRQ00||a.CZSJ00 >= ls_STARDATE||'00:00:00' and a.CZRQ00||a.CZSJ00 <= ls_ENDDATE||'23:59:59'
	    and b.KDYS00=MZYP.KDYS00 and  (e.fbbh00, e.yblb00) IN ( SELECT fbbh00, yblb00  FROM ic_ybbrlb 
            where ybzxlb not in ('d','a','J','5','4','3'));
	  --挂号次数
	  select nvl(sum(nvl(GHCS00,0)),0) into lv_GHCS00 from(
  	 -- select count(1) GHCS00 from SF_BRXXB0 a,BM_BRXXB0 b,VW_XT_YBBRLB f
     -- where a.BRID00=b.BRID00 and b.YBLB00=f.YBLB00 and b.FBBH00=f.FBBH00 and GHRQ00 >= ls_STARDATE and GHRQ00<=ls_ENDDATE
     -- and JZYS00=MZYP.KDYS00
     -- and (a.GHLB00>=0 or exists(select 1 from SF_GHF000 where GFLB00=a.GHLB00 and GFLB00<0)) and  (b.fbbh00, b.yblb00) IN ( SELECT fbbh00, yblb00  FROM ic_ybbrlb 
    --        where ybzxlb not in ('d','a','J','5','4','3'))
    --  union all
    --  select -1*count(1) GHCS00 from SF_BRXXB0 a,BM_BRXXB0 b,VW_XT_YBBRLB f
    --  where a.BRID00=b.BRID00 and b.YBLB00=f.YBLB00 and b.FBBH00=f.FBBH00 and THRQ00 >= ls_STARDATE and THRQ00<=ls_ENDDATE
    --  and JZYS00=MZYP.KDYS00
    --  and (a.GHLB00>=0 or exists(select 1 from SF_GHF000 where GFLB00=a.GHLB00 and GFLB00<0))
    --  and   (b.fbbh00, b.yblb00) IN ( SELECT fbbh00, yblb00  FROM ic_ybbrlb 
    --        where ybzxlb not in ('d','a','J','5','4','3')) 
     select sum(GHCS00) GHCS00  from  VW_SF_GHLSXX g,bm_brxxb0 k  where  g.CZRQ00>=ls_STARDATE and g.CZRQ00<=ls_ENDDATE
     and g.brid00=k.brid00 and (k.fbbh00, k.yblb00) IN ( SELECT fbbh00, yblb00  FROM ic_ybbrlb 
  where ybzxlb not in ('d','a','J','5','4','3'))   and  g.JZYS00=MZYP.KDYS00
    );
	  if (lv_MZZSR0-lv_ZDXMFY) = 0 then
	    ls_YZB000 := '0%';
	  else
	    ls_YZB000 := to_char(round(lv_MZYPSR/(lv_MZZSR0-lv_ZDXMFY),4) * 100,'9990.00')||'%';
	  end if;
	  if lv_GHCS00 = 0 then
	    lv_CJFY00 := 0;
	  else
	    lv_CJFY00 := round(lv_MZZFY0/lv_GHCS00,2);
	  end if;
	  select ZWXM00 into ls_KDYSXM from BM_YGBM00 where YGBH00=MZYP.KDYS00 and rownum=1;
    insert into YS_TJYZB0_TEMP00(TJID00,KDYS00,KDYSXM,YZB000,TJRQ00,CJFY00)
    values(SQ_YS_TEMP00_TJID00.nextval,MZYP.KDYS00,ls_KDYSXM,ls_YZB000,ls_SYSDATE,lv_CJFY00);
  end loop;
  commit;
  exception
  when others then
    rollback;
end;
/