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';
--MODIFICATION HISTORY
-- Person      Date         Comments
-- dsm         2016.05.11   create for MZYS-20160504-001 
-- dsm         2016.07.29   增加整理中药的费用 for MZYS-20160729-001
begin
  select count(*) into lv_count from BM_YSFYTJ where rownum<10;
	ls_STARDATE := to_char(trunc(sysdate ,'YYYY'),'YYYYMMDD'); ---如果要整理往年的数据,第1次运行过程要把sysdate-->add_months(sysdate,-12)
  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
	 --lv_MZYPSR := MZYP.HJJE00;
	  --门诊总收入
	  insert into BM_YSFYTJ(RQ0000,YSBH00,ZFY000,BCYFY0,YPFY00,HCFY00,ZYYPFY,KSBH00)
	  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))   ---老年的特殊处理,针对其他医院可能没有用
	   ,sum(decode(b.XMBH00,'9999999999',b.HJJE00,0))
	   ,b.KDKS00
	   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.KDKS00,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;

	  --挂号次数

    update  BM_YSFYTJ a set GHCS00=( select sum(GHCS00)  from  VW_SF_GHLSXX g  where  g.CZRQ00=ls_STARDATE 
                         and g.JZYS00=a.YSBH00 and JZKS00=a.KSBH00  
                   ) where RQ0000=ls_STARDATE  ;
	  
	  insert into BM_YSFYTJ(RQ0000,GHCS00,YSBH00,KSBH00)
	  select ls_STARDATE,sum(GHCS00) GHCS00,g.JZYS00,g.JZKS00  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 and KSBH00=g.JZKS00) group by g.JZKS00,g.JZYS00  ;         
    for c_ypje in (
    select GHKS00,YSGZH0,sum(JCJE00) JCJE00,sum(DJJE00) DJJE00 from (
      select c.GHKS00,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 GHKS00,YSGZH0)
    loop
      update BM_YSFYTJ set JCYPFY=c_ypje.JCJE00,
                           DJYPFY=c_ypje.DJJE00
       where YSBH00=c_ypje.YSGZH0 and RQ0000=ls_STARDATE and KSBH00=c_ypje.GHKS00;
      if SQL%NOTFOUND then 
      	insert into BM_YSFYTJ(RQ0000,YSBH00,JCYPFY,DJYPFY,KSBH00)
      	values(ls_STARDATE,c_ypje.YSGZH0,c_ypje.JCJE00,c_ypje.DJJE00,c_ypje.GHKS00);
      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;