create or replace procedure SP_YS_GRCJFY
(
  AS_KSRQ00    in    char,                --开始日期

  AS_JSRQ00    in    char,                --结束日期
  AD_KDKSTJ    in    number,              --是否按开单科室分开统计 1是 0否

  AD_KDYS00    in    number,              --开单医师

  AS_FZXTJ0    in    char,                --按医保分中心 0不按中心统计 1 按大中心统计 2 按分中心统计
  AD_ID0000    out   number,              --统计报表对应的ID
  Pzxcgbz      out   number,              --执行成功标志 1成功 0失败 
  Ptsxx00      out   varchar2,            --执行结果提示信息，成功的情况下也可能有警告信息

  Pxxxx00      out   varchar2,            --执行结果详细信息，成功的情况下也可能有警告信息，一般用来显示参数等指标
  AD_KSBH00    in    number default 0     --部门编号
)
as
-- MODIFICATION HISTORY
-- Person      Date        Comments
-- zhanghr     2010.12.17  create;
-- zhanghr     2010.12.28  增加医保次均费用算法选择 1根据结账表来计算 0 根据费用表来计算 
-- linzy       2011.08.12  增加参数部门编号
-- yangy       2012.12.05  增加不按分中心统计，不加汇总，药品费用统计错误更改
-- yangy       2012.12.11  次均费用等统计可以按科室统计
-- yangy       2013.01.18  增加医生的小计，取消环比和同比

-- zhaoxz      2013.11.19  增加膏方药费和不含膏方药占比
-- zhaoxz      2014.01.14  增加西药成药药占比（不含本院制剂） 
-- zhaoxz      2016.03.15  西药成药药占比口径分母去掉中草药和本院制剂 MZYS9-20160315-001 
-- zhaoxz      2016.10.24  增加参数控制西药成药药占比分母是否需要去掉中草药和本院制剂 MZYS9-20161024-001 
-- linyj       2017.06.28  费用表算法-增加耗占比  SFXM34 耗材总费用 SFXM35 耗占比 MZYS9-20170220-002
-- ---------- ----------- ------------------------------------------------------------------

       ls_KSRQ00    char(8);    --上年开始日期

       ls_JSRQ00    char(8);    --上年结束日期
       ls_KSRQ01    char(8);    --上月开始日期

       ls_JSRQ01    char(8);    --上月结束日期
       ls_XSTB00    char(1);    --显示同比和环比 1 显示0不显示

       Vybcjsf      number(5);  --医保次均费用算法
       Vcounter     number(5);  --计数器

       Ecustom      exception;  --错误 
       ls_KDKSSFWDLKS varchar(10);
       ls_XYCYYZDSFPC varchar(10);
BEGIN
         --医保次均费用算法 1根据结账表来计算 0 根据费用表来计算
    select nvl(to_number(max(value0)),0) into Vybcjsf from XT_XTCS00 where name00='XT_YBCJFYSF';
    select nvl(max(trim(VALUE0)),'N') into ls_XYCYYZDSFPC from XT_XTCS00 where name00='YS_XYCYYZBSFPCZCY';
    
    --统计一个月的，才显示同比和环比
    --if substrb(AS_KSRQ00,1,6)=substrb(AS_JSRQ00,1,6) then
    --   ls_XSTB00:=1;
    --else
    --   ls_XSTB00:=0;   
    --end if;
    
    --由于查询速度太慢，暂时取消同比和环比
    ls_XSTB00:=0;   
    -- 收费项目与次均费用 的开单科室是否限制为登录科室   2011.08.12 
    begin
    select trim(VALUE0) into ls_KDKSSFWDLKS from XT_XTCS00 where NAME00='YS_YSSRTJKDKSSFWDLKS';
    exception
    when others then
     ls_KDKSSFWDLKS:='N';
    end;
    
    select  SQ_BM_YYSFTJ_ID0000.nextval  into  ad_ID0000  from  dual;
    --去年同月
    select to_char(add_months(to_date(substrb(AS_KSRQ00,1,6)||'01','YYYYMMDD'),-12),'YYYYMMDD') into ls_KSRQ00  from dual;
    select to_char(last_day(to_date(ls_KSRQ00,'YYYYMMDD')),'YYYYMMDD') into ls_JSRQ00  from dual;
    --上个月

    select to_char(add_months(to_date(substrb(AS_KSRQ00,1,6)||'01','YYYYMMDD'),-1),'YYYYMMDD') into ls_KSRQ01  from dual;
    select to_char(last_day(to_date(ls_KSRQ01,'YYYYMMDD')),'YYYYMMDD') into ls_JSRQ01  from dual;

    --SFXM88=1 自费 SFXM88=3 医保 其他为公费 BMMC00:开单部门名称 BMBH00：开单科室编码 SFXM99：医生编码 SJYSXM:费别名称
    --SFXM01：本期挂号人次 SFXM02本期总费用 SFXM03 本期药品费用 SFXM04 本期次均费用 SFXM05 本期药品比例 SFXM06 本期次均药品费用

    --SFXM11：同期挂号人次 SFXM12同期总费用 SFXM13 同期药品费用 SFXM14 同期次均费用 SFXM15 同期药品比例 SFXM16 同期次均药品费用
    --                    SFXM17: 挂号人次同比增长 SFXM18 总费用同比增长 SFXM19 次均费用同比增长 

    --SFXM21：上期挂号人次 SFXM22上期总费用 SFXM23 上期药品费用 SFXM24 上期次均费用 SFXM25 上期药品比例 SFXM26 上期次均药品费用
    --                    SFXM27: 挂号人次环比增长 SFXM28 总费用环比增长 SFXM29 次均费用环比增长 SFXM30膏方药费费 SFXM31不含膏方药占比

    --                   SFXM32：西药成药药费（不含本院制剂） SFXM33：西药成药药占比（不含本院制剂） 
    
    --0 根据费用表来次均费用
    if Vybcjsf=0 then
       if AS_FZXTJ0=0 then
        --不按中心统计
          --费用
          insert into BM_YYSFTJ (ID0000,SFXM88,BMBH00,SFXM99,SJYSXM,SFXM01,SFXM02,SFXM03,SFXM30,SFXM32,SFXM34)
              select ad_ID0000,0 FBBH00,decode(AD_KDKSTJ,1,KDKS00,0),b.KDYS00,'全部' YBMC00,0,sum(b.HJJE00)HJJE00,
                     sum(decode(b.SFLB00,'3',b.HJJE00,0)) YPJE00,SUM(CASE WHEN b.sflb00 = '3' AND b.beizhu LIKE '%膏%' THEN b.hjje00 ELSE 0 END) GFYPJE,
                     sum(CASE WHEN (b.MZFPID='1'or b.MZFPID='2') and not exists (select 1 from BM_YD0000 where ZJBZ00='1' and YPNM00=b.XMBH00) THEN b.HJJE00 ELSE 0 END) XYCYJE,
                     sum(CASE WHEN (f.bh0000 like '9%' and f.jsxm00='2')  THEN b.HJJE00 ELSE 0 END) HCZFY0 --耗材总费用

              from SF_FYMX00 b,BM_BRXXB0 e,bm_yysfxm f
              where b.CZRQ00 between AS_KSRQ00 and AS_JSRQ00 and 
                    b.BRID00=e.BRID00 and (b.KDYS00=AD_KDYS00 or nvl(AD_KDYS00,0)=0)
                    and (b.KDKS00=AD_KSBH00  or nvl(AD_KSBH00,0)=0)
                    and b.XMBH00 = f.sfxmid(+)
              group by decode(AD_KDKSTJ,1,KDKS00,0),b.KDYS00;
          --挂号    
          declare cursor c_gh is select  0 FBBH00,count(1) GHCS00,decode(AD_KDKSTJ,1,JZKS00,0) JZKS00,a.JZYS00,'全部' YBMC00
                  from SF_BRXXB0 a,BM_BRXXB0 b
                  where a.BRID00=b.BRID00  and 
                        GHRQ00 between AS_KSRQ00 and AS_JSRQ00 and (a.JZYS00=AD_KDYS00 or nvl(AD_KDYS00,0)=0)
                        and (JZKS00=AD_KSBH00 or nvl(AD_KSBH00,0)=0) and ghlb00 not in (-99999)
                  group by decode(AD_KDKSTJ,1,JZKS00,0),a.JZYS00
                  union all
                  select  0 FBBH00,-1*count(1) GHCS00,decode(AD_KDKSTJ,1,a.JZKS00,0) JZKS00,a.JZYS00,'全部' YBMC00
                  from SF_BRXXB0 a,BM_BRXXB0 b
                  where a.BRID00=b.BRID00 and  a.THRQ00 between AS_KSRQ00 and AS_JSRQ00 and (a.JZYS00=AD_KDYS00 or nvl(AD_KDYS00,0)=0)
                        and (JZKS00=AD_KSBH00 or nvl(AD_KSBH00,0)=0)
                  group by decode(AD_KDKSTJ,1,JZKS00,0),a.JZYS00;
          begin         
            for gh in c_gh loop
              update BM_YYSFTJ set SFXM01=nvl(SFXM01,0)+gh.GHCS00 where ID0000=ad_ID0000 and SFXM88=gh.FBBH00 and BMBH00=gh.JZKS00 and SFXM99=gh.JZYS00 and SJYSXM=gh.YBMC00;
              if sql%notfound then
                 insert into BM_YYSFTJ (ID0000,SFXM88,BMBH00,SFXM99,SJYSXM,SFXM01,SFXM02,SFXM03,SFXM30,SFXM32)
                    values (ad_ID0000,gh.FBBH00,decode(AD_KDKSTJ,1,gh.JZKS00,0),gh.JZYS00,gh.YBMC00,gh.GHCS00,0,0,0,0);
              end if;
            end loop;
          end;
          
          if ls_XSTB00=1 then
             --同比
             --费用
             declare cursor c_fy is
                 select decode(AD_KDKSTJ,1,b.KDKS00,0)KDKS00,b.KDYS00,0 FBBH00,'全部' YBMC00,sum(b.HJJE00)HJJE00,
                        sum(decode(b.SFLB00,'3',b.HJJE00,0)) YPJE00
                 from SF_FYMX00 b,BM_BRXXB0 e
                 where b.CZRQ00 between ls_KSRQ00 and ls_JSRQ00 and 
                    b.BRID00=e.BRID00 and (b.KDYS00=AD_KDYS00 or nvl(AD_KDYS00,0)=0)
                    and (b.KDKS00=AD_KSBH00 or nvl(AD_KSBH00,0)=0)
              group by decode(AD_KDKSTJ,1,KDKS00,0),b.KDYS00;
             begin
               for fy in c_fy loop
                 update BM_YYSFTJ set SFXM12=fy.HJJE00,SFXM13=fy.YPJE00 where ID0000=ad_ID0000 and SFXM88=fy.FBBH00 and BMBH00=fy.KDKS00 and SFXM99=fy.KDYS00 and SJYSXM=fy.YBMC00;
               end loop;
             end;    
             --挂号    
             declare cursor c_gh is select  0 FBBH00,count(1) GHCS00,decode(AD_KDKSTJ,1,JZKS00,0)JZKS00,a.JZYS00,'全部' YBMC00
                  from SF_BRXXB0 a,BM_BRXXB0 b
                  where a.BRID00=b.BRID00 and 
                        GHRQ00 between ls_KSRQ00 and ls_JSRQ00 and  (a.JZYS00=AD_KDYS00 or nvl(AD_KDYS00,0)=0)
                        and (JZKS00=AD_KSBH00 or nvl(AD_KSBH00,0)=0) and ghlb00 not in (-99999)
                  group by decode(AD_KDKSTJ,1,JZKS00,0),a.JZYS00
                  union all
                  select  0 FBBH00,-1*count(1) GHCS00,decode(AD_KDKSTJ,1,JZKS00,0)JZKS00,a.JZYS00,'全部' YBMC00
                  from SF_BRXXB0 a,BM_BRXXB0 b
                  where a.BRID00=b.BRID00 and 
                        THRQ00 between ls_KSRQ00 and ls_JSRQ00 and  (a.JZYS00=AD_KDYS00 or nvl(AD_KDYS00,0)=0)
                        and (JZKS00=AD_KSBH00 or nvl(AD_KSBH00,0)=0)
                  group by decode(AD_KDKSTJ,1,JZKS00,0),a.JZYS00;
                  
             begin         
               for gh in c_gh loop
              update BM_YYSFTJ set SFXM11=nvl(SFXM11,0)+gh.GHCS00 where ID0000=ad_ID0000 and SFXM88=gh.FBBH00 and BMBH00=gh.JZKS00 and SFXM99=gh.JZYS00 and SJYSXM=gh.YBMC00;
               end loop;
             end;
             
             
             --环比
             --费用
             declare cursor c_fy is
                 select decode(AD_KDKSTJ,1,b.KDKS00,0)KDKS00,b.KDYS00,0 FBBH00,'全部' YBMC00,sum(b.HJJE00)HJJE00,
                        sum(decode(b.SFLB00,'3',b.HJJE00,0))YPJE00
                 from SF_FYMX00 b,BM_BRXXB0 e
                 where b.CZRQ00 between ls_KSRQ01 and ls_JSRQ01 and 
                    b.BRID00=e.BRID00 and (b.KDYS00=AD_KDYS00 or nvl(AD_KDYS00,0)=0)
                    and (b.KDKS00=AD_KSBH00 or nvl(AD_KSBH00,0)=0)
              group by decode(AD_KDKSTJ,1,KDKS00,0),b.KDYS00;
             begin
               for fy in c_fy loop
                 update BM_YYSFTJ set SFXM22=fy.HJJE00,SFXM23=fy.YPJE00 where ID0000=ad_ID0000 and SFXM88=fy.FBBH00 and BMBH00=fy.KDKS00 and SFXM99=fy.KDYS00 and SJYSXM=fy.YBMC00;
               end loop;
             end;    
             --挂号    
             declare cursor c_gh is select  0 FBBH00,count(1) GHCS00,decode(AD_KDKSTJ,1,JZKS00,0)JZKS00,a.JZYS00,'全部' YBMC00
                  from SF_BRXXB0 a,BM_BRXXB0 b
                  where a.BRID00=b.BRID00 and 
                        GHRQ00 between ls_KSRQ01 and ls_JSRQ01 and  (a.JZYS00=AD_KDYS00 or nvl(AD_KDYS00,0)=0)
                        and (JZKS00=AD_KSBH00 or nvl(AD_KSBH00,0)=0) and ghlb00 not in (-99999)
                  group by decode(AD_KDKSTJ,1,JZKS00,0),a.JZYS00
                  union all
                  select  0 FBBH00,-1*count(1) GHCS00,decode(AD_KDKSTJ,1,JZKS00,0)JZKS00,a.JZYS00,'全部' YBMC00
                  from SF_BRXXB0 a,BM_BRXXB0 b
                  where a.BRID00=b.BRID00 and 
                        THRQ00 between ls_KSRQ01 and ls_JSRQ01 and  (a.JZYS00=AD_KDYS00 or nvl(AD_KDYS00,0)=0)
                        and (JZKS00=AD_KSBH00 or nvl(AD_KSBH00,0)=0)
                  group by decode(AD_KDKSTJ,1,JZKS00,0),a.JZYS00;
                  
             begin         
               for gh in c_gh loop
              update BM_YYSFTJ set SFXM21=nvl(SFXM21,0)+gh.GHCS00 where ID0000=ad_ID0000 and SFXM88=gh.FBBH00 and BMBH00=gh.JZKS00 and SFXM99=gh.JZYS00 and SJYSXM=gh.YBMC00;
               end loop;
             end;
                       
          
          end if;
       elsif AS_FZXTJ0=1 then
          --费用
          insert into BM_YYSFTJ (ID0000,SFXM88,BMBH00,SFXM99,SJYSXM,SFXM01,SFXM02,SFXM03,SFXM30,SFXM32,SFXM34)
              select ad_ID0000,e.FBBH00,decode(AD_KDKSTJ,1,KDKS00,0),b.KDYS00,f.YBMC01,0,sum(b.HJJE00)HJJE00,
                     sum(decode(b.SFLB00,'3',b.HJJE00,0)) YPJE00,SUM(CASE WHEN b.sflb00 = '3' AND b.beizhu LIKE '%膏%' THEN b.hjje00 ELSE 0 END) GFYPJE,
                     sum(CASE WHEN (b.MZFPID='1'or b.MZFPID='2') and not exists (select 1 from BM_YD0000 where ZJBZ00='1' and YPNM00=b.XMBH00) THEN b.HJJE00 ELSE 0 END) XYCYJE,
                     sum(CASE WHEN (g.bh0000 like '9%' and g.jsxm00='2')  THEN b.HJJE00 ELSE 0 END) HCZFY0 --耗材总费用

              from SF_FYMX00 b,BM_BRXXB0 e,VW_XT_YBBRLB f,bm_yysfxm g
              where b.CZRQ00 between AS_KSRQ00 and AS_JSRQ00 and  
                    b.BRID00=e.BRID00 and e.FBBH00=f.FBBH00 and e.YBLB00=f.YBLB00 and (b.KDYS00=AD_KDYS00 or nvl(AD_KDYS00,0)=0)
                    and (b.KDKS00=AD_KSBH00 or nvl(AD_KSBH00,0)=0)
                    and b.XMBH00 = g.sfxmid(+)
              group by e.FBBH00,f.YBMC01,decode(AD_KDKSTJ,1,b.KDKS00,0),b.KDYS00;
          --挂号    
          declare cursor c_gh is select  b.FBBH00,count(1) GHCS00,decode(AD_KDKSTJ,1,JZKS00,0)JZKS00,a.JZYS00,YBMC01
                  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 between AS_KSRQ00 and AS_JSRQ00 and  (a.JZYS00=AD_KDYS00 or nvl(AD_KDYS00,0)=0)
                  and (a.JZKS00=AD_KSBH00 or nvl(AD_KSBH00,0)=0) and ghlb00 not in (-99999)
                  group by b.FBBH00,decode(AD_KDKSTJ,1,JZKS00,0),a.JZYS00,YBMC01
                  union all
                  select  b.FBBH00,-1*count(1) GHCS00,decode(AD_KDKSTJ,1,JZKS00,0)JZKS00,a.JZYS00,YBMC01
                  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 between AS_KSRQ00 and AS_JSRQ00 and  (a.JZYS00=AD_KDYS00 or nvl(AD_KDYS00,0)=0)
                  and (a.JZKS00=AD_KSBH00 or nvl(AD_KSBH00,0)=0) and ghlb00 not in (-99999)
                  group by b.FBBH00,decode(AD_KDKSTJ,1,a.JZKS00,0),a.JZYS00,f.YBMC01;
          begin         
            for gh in c_gh loop
              update BM_YYSFTJ set SFXM01=nvl(SFXM01,0)+gh.GHCS00 where ID0000=ad_ID0000 and SFXM88=gh.FBBH00 and BMBH00=gh.JZKS00 and SFXM99=gh.JZYS00 and SJYSXM=gh.YBMC01;
              if sql%notfound then
                 insert into BM_YYSFTJ (ID0000,SFXM88,BMBH00,SFXM99,SJYSXM,SFXM01,SFXM02,SFXM03,SFXM30,SFXM32)
                    values (ad_ID0000,gh.FBBH00,decode(AD_KDKSTJ,1,gh.JZKS00,0),gh.JZYS00,gh.YBMC01,gh.GHCS00,0,0,0,0);
              end if;
            end loop;
          end;
          
          if ls_XSTB00=1 then
             --同比
             --费用
             declare cursor c_fy is
                 select decode(AD_KDKSTJ,1,b.KDKS00,0)KDKS00,b.KDYS00,e.FBBH00,f.YBMC01,sum(b.HJJE00)HJJE00,
                        sum(decode(b.SFLB00,'3',b.HJJE00,0)) YPJE00
                 from SF_FYMX00 b,BM_BRXXB0 e,VW_XT_YBBRLB f
                 where b.CZRQ00 between ls_KSRQ00 and ls_JSRQ00 and 
                    b.BRID00=e.BRID00 and e.FBBH00=f.FBBH00 and e.YBLB00=f.YBLB00 and (b.KDYS00=AD_KDYS00 or nvl(AD_KDYS00,0)=0)
                    and (b.KDKS00=AD_KSBH00 or nvl(AD_KSBH00,0)=0)
              group by e.FBBH00,f.YBMC01,decode(AD_KDKSTJ,1,KDKS00,0),b.KDYS00;
             begin
               for fy in c_fy loop
                 update BM_YYSFTJ set SFXM12=fy.HJJE00,SFXM13=fy.YPJE00 where ID0000=ad_ID0000 and SFXM88=fy.FBBH00 and BMBH00=fy.KDKS00 and SFXM99=fy.KDYS00 and SJYSXM=fy.YBMC01;
               end loop;
             end;    
             --挂号    
             declare cursor c_gh is select  b.FBBH00,count(1) GHCS00,decode(AD_KDKSTJ,1,JZKS00,0)JZKS00,a.JZYS00,YBMC01
                  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 between ls_KSRQ00 and ls_JSRQ00 and  (a.JZYS00=AD_KDYS00 or nvl(AD_KDYS00,0)=0)
                        and (JZKS00=AD_KSBH00 or nvl(AD_KSBH00,0)=0) and ghlb00 not in (-99999)
                  group by b.FBBH00,decode(AD_KDKSTJ,1,JZKS00,0),a.JZYS00,YBMC01
                  union all
                  select  b.FBBH00,-1*count(1) GHCS00,decode(AD_KDKSTJ,1,JZKS00,0)JZKS00,a.JZYS00,YBMC01
                  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 between ls_KSRQ00 and ls_JSRQ00 and  (a.JZYS00=AD_KDYS00 or nvl(AD_KDYS00,0)=0)
                        and (JZKS00=AD_KSBH00 or nvl(AD_KSBH00,0)=0) 
                  group by b.FBBH00,decode(AD_KDKSTJ,1,JZKS00,0),a.JZYS00,YBMC01;
                  
             begin         
               for gh in c_gh loop
                 update BM_YYSFTJ set SFXM11=nvl(SFXM11,0)+gh.GHCS00 where ID0000=ad_ID0000 and SFXM88=gh.FBBH00 and BMBH00=gh.JZKS00 and SFXM99=gh.JZYS00 and SJYSXM=gh.YBMC01;
               end loop;
             end;
             
             
             --环比
             --费用
             declare cursor c_fy is
                 select decode(AD_KDKSTJ,1,b.KDKS00,0)KDKS00,b.KDYS00,e.FBBH00,f.YBMC01,sum(b.HJJE00)HJJE00,
                        sum(decode(b.SFLB00,'3',b.HJJE00,0)) YPJE00
                 from SF_FYMX00 b,BM_BRXXB0 e,VW_XT_YBBRLB f
                 where b.CZRQ00 between ls_KSRQ01 and ls_JSRQ01 and 
                    b.BRID00=e.BRID00 and e.FBBH00=f.FBBH00 and e.YBLB00=f.YBLB00 and (b.KDYS00=AD_KDYS00 or nvl(AD_KDYS00,0)=0)
                    and (b.KDKS00=AD_KSBH00 or nvl(AD_KSBH00,0)=0)
              group by e.FBBH00,f.YBMC01,decode(AD_KDKSTJ,1,KDKS00,0),b.KDYS00;
             begin
               for fy in c_fy loop
                 update BM_YYSFTJ set SFXM22=fy.HJJE00,SFXM23=fy.YPJE00 where ID0000=ad_ID0000 and SFXM88=fy.FBBH00 and BMBH00=fy.KDKS00 and SFXM99=fy.KDYS00 and SJYSXM=fy.YBMC01;
               end loop;
             end;    
             --挂号    
             declare cursor c_gh is select  b.FBBH00,count(1) GHCS00,decode(AD_KDKSTJ,1,JZKS00,0)JZKS00,a.JZYS00,YBMC01
                  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 between ls_KSRQ01 and ls_JSRQ01 and  (a.JZYS00=AD_KDYS00 or nvl(AD_KDYS00,0)=0)
                        and (JZKS00=AD_KSBH00 or nvl(AD_KSBH00,0)=0) and ghlb00 not in (-99999)
                  group by b.FBBH00,decode(AD_KDKSTJ,1,JZKS00,0),a.JZYS00,YBMC01
                  union all
                  select  b.FBBH00,-1*count(1) GHCS00,decode(AD_KDKSTJ,1,JZKS00,0)JZKS00,a.JZYS00,YBMC01
                  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 between ls_KSRQ01 and ls_JSRQ01 and  (a.JZYS00=AD_KDYS00 or nvl(AD_KDYS00,0)=0)
                        and (JZKS00=AD_KSBH00 or nvl(AD_KSBH00,0)=0)
                  group by b.FBBH00,decode(AD_KDKSTJ,1,JZKS00,0),a.JZYS00,YBMC01;
                  
             begin         
               for gh in c_gh loop
              update BM_YYSFTJ set SFXM21=nvl(SFXM21,0)+gh.GHCS00 where ID0000=ad_ID0000 and SFXM88=gh.FBBH00 and BMBH00=gh.JZKS00 and SFXM99=gh.JZYS00 and SJYSXM=gh.YBMC01;
               end loop;
             end;
          end if; 
             
               
             
       else --按分中心统计
          --费用
          insert into BM_YYSFTJ (ID0000,SFXM88,BMBH00,SFXM99,SJYSXM,SFXM01,SFXM02,SFXM03,SFXM30,SFXM32,SFXM34)
              select ad_ID0000,e.FBBH00,decode(AD_KDKSTJ,1,KDKS00,0),AD_KDYS00,f.YBMC00,0,sum(b.HJJE00)HJJE00,
                     sum(decode(b.SFLB00,'3',b.HJJE00,0)) YPJE00,SUM(CASE WHEN b.sflb00 = '3' AND b.beizhu LIKE '%膏%' THEN b.hjje00 ELSE 0 END) GFYPJE,
                     sum(CASE WHEN (b.MZFPID='1'or b.MZFPID='2') and not exists (select 1 from BM_YD0000 where ZJBZ00='1' and YPNM00=b.XMBH00) THEN b.HJJE00 ELSE 0 END) XYCYJE,
                     sum(CASE WHEN (g.bh0000 like '9%' and g.jsxm00='2')  THEN b.HJJE00 ELSE 0 END) HCZFY0 --耗材总费用

              from SF_FYMX00 b,BM_BRXXB0 e,IC_YBBRLB f,bm_yysfxm g
              where b.CZRQ00 between AS_KSRQ00 and AS_JSRQ00 and  
                    b.BRID00=e.BRID00 and e.FBBH00=f.FBBH00 and e.YBLB00=f.YBLB00 and b.KDYS00=AD_KDYS00 
                    and (b.KDKS00=AD_KSBH00 or nvl(AD_KSBH00,0)=0)
                    and b.XMBH00 = g.sfxmid(+)
              group by e.FBBH00,f.YBMC00,decode(AD_KDKSTJ,1,KDKS00,0);
          --挂号    
          declare cursor c_gh is select  b.FBBH00,count(1) GHCS00,decode(AD_KDKSTJ,1,JZKS00,0)JZKS00,YBMC00
                  from SF_BRXXB0 a,BM_BRXXB0 b,IC_YBBRLB f   
                  where a.BRID00=b.BRID00 and b.YBLB00=f.YBLB00 and b.FBBH00=f.FBBH00 and 
                        GHRQ00 between AS_KSRQ00 and AS_JSRQ00 and JZYS00=AD_KDYS00
                        and (JZKS00=AD_KSBH00 or nvl(AD_KSBH00,0)=0) and ghlb00 not in (-99999)
                  group by b.FBBH00,decode(AD_KDKSTJ,1,JZKS00,0),YBMC00
                  union all
                  select  b.FBBH00,-1*count(1) GHCS00,decode(AD_KDKSTJ,1,JZKS00,0)JZKS00,YBMC00
                  from SF_BRXXB0 a,BM_BRXXB0 b,IC_YBBRLB f   
                  where a.BRID00=b.BRID00 and b.YBLB00=f.YBLB00 and b.FBBH00=f.FBBH00 and 
                        THRQ00 between AS_KSRQ00 and AS_JSRQ00 and JZYS00=AD_KDYS00
                        and (JZKS00=AD_KSBH00 or nvl(AD_KSBH00,0)=0)
                  group by b.FBBH00,decode(AD_KDKSTJ,1,JZKS00,0),YBMC00;
          begin         
            for gh in c_gh loop
              update BM_YYSFTJ set SFXM01=nvl(SFXM01,0)+gh.GHCS00 where ID0000=ad_ID0000 and SFXM88=gh.FBBH00 and BMBH00=gh.JZKS00 and SJYSXM=gh.YBMC00;
              if sql%notfound then
                 insert into BM_YYSFTJ (ID0000,SFXM88,BMBH00,SFXM99,SJYSXM,SFXM01,SFXM02,SFXM03,SFXM30,SFXM32)
                    values (ad_ID0000,gh.FBBH00,decode(AD_KDKSTJ,1,gh.JZKS00,0),AD_KDYS00,gh.YBMC00,gh.GHCS00,0,0,0,0);
              end if;
            end loop;
          end;
          
          if ls_XSTB00=1 then
             --同比
             --费用
             declare cursor c_fy is
                 select decode(AD_KDKSTJ,1,b.KDKS00,0)KDKS00,e.FBBH00,f.YBMC00,sum(b.HJJE00)HJJE00,
                        sum(decode(b.SFLB00,'3',b.HJJE00,0)) YPJE00
                 from SF_FYMX00 b,BM_BRXXB0 e,IC_YBBRLB f
                 where b.CZRQ00 between ls_KSRQ00 and ls_JSRQ00 and 
                    b.BRID00=e.BRID00 and e.FBBH00=f.FBBH00 and e.YBLB00=f.YBLB00 and b.KDYS00=AD_KDYS00 
                    and (b.KDKS00=AD_KSBH00 or nvl(AD_KSBH00,0)=0)
              group by e.FBBH00,f.YBMC00,decode(AD_KDKSTJ,1,KDKS00,0);
             begin
               for fy in c_fy loop
                 update BM_YYSFTJ set SFXM12=fy.HJJE00,SFXM13=fy.YPJE00 where ID0000=ad_ID0000 and SFXM88=fy.FBBH00 and BMBH00=fy.KDKS00 and SJYSXM=fy.YBMC00;
               end loop;
             end;    
             --挂号    
             declare cursor c_gh is select  b.FBBH00,count(1) GHCS00,decode(AD_KDKSTJ,1,JZKS00,0)JZKS00,YBMC00
                  from SF_BRXXB0 a,BM_BRXXB0 b,IC_YBBRLB f   
                  where a.BRID00=b.BRID00 and b.YBLB00=f.YBLB00 and b.FBBH00=f.FBBH00 and 
                        GHRQ00 between ls_KSRQ00 and ls_JSRQ00 and JZYS00=AD_KDYS00
                        and (JZKS00=AD_KSBH00 or nvl(AD_KSBH00,0)=0) and ghlb00 not in (-99999)
                  group by b.FBBH00,decode(AD_KDKSTJ,1,JZKS00,0),YBMC00
                  union all
                  select  b.FBBH00,-1*count(1) GHCS00,decode(AD_KDKSTJ,1,JZKS00,0)JZKS00,YBMC00
                  from SF_BRXXB0 a,BM_BRXXB0 b,IC_YBBRLB f   
                  where a.BRID00=b.BRID00 and b.YBLB00=f.YBLB00 and b.FBBH00=f.FBBH00 and 
                        THRQ00 between ls_KSRQ00 and ls_JSRQ00 and JZYS00=AD_KDYS00
                        and (JZKS00=AD_KSBH00 or nvl(AD_KSBH00,0)=0)
                  group by b.FBBH00,decode(AD_KDKSTJ,1,JZKS00,0),YBMC00;
                  
             begin         
               for gh in c_gh loop
              update BM_YYSFTJ set SFXM11=nvl(SFXM11,0)+gh.GHCS00 where ID0000=ad_ID0000 and SFXM88=gh.FBBH00 and BMBH00=gh.JZKS00 and SJYSXM=gh.YBMC00;
               end loop;
             end;
             
             
             --环比
             --费用
             declare cursor c_fy is
                 select decode(AD_KDKSTJ,1,b.KDKS00,0)KDKS00,e.FBBH00,f.YBMC00,sum(b.HJJE00)HJJE00,
                        sum(decode(b.SFLB00,'3',b.HJJE00,0)) YPJE00
                 from SF_FYMX00 b,BM_BRXXB0 e,IC_YBBRLB f
                 where b.CZRQ00 between ls_KSRQ01 and ls_JSRQ01 and 
                    b.BRID00=e.BRID00 and e.FBBH00=f.FBBH00 and e.YBLB00=f.YBLB00 and b.KDYS00=AD_KDYS00 
                    and (b.KDKS00=AD_KSBH00 or nvl(AD_KSBH00,0)=0)
              group by e.FBBH00,f.YBMC00,decode(AD_KDKSTJ,1,KDKS00,0);
             begin
               for fy in c_fy loop
                 update BM_YYSFTJ set SFXM22=fy.HJJE00,SFXM23=fy.YPJE00 where ID0000=ad_ID0000 and SFXM88=fy.FBBH00 and BMBH00=fy.KDKS00 and SJYSXM=fy.YBMC00;
               end loop;
             end;    
             --挂号    
             declare cursor c_gh is select  b.FBBH00,count(1) GHCS00,decode(AD_KDKSTJ,1,JZKS00,0)JZKS00,YBMC00
                  from SF_BRXXB0 a,BM_BRXXB0 b,IC_YBBRLB f   
                  where a.BRID00=b.BRID00 and b.YBLB00=f.YBLB00 and b.FBBH00=f.FBBH00 and 
                        GHRQ00 between ls_KSRQ01 and ls_JSRQ01 and JZYS00=AD_KDYS00
                        and (JZKS00=AD_KSBH00 or nvl(AD_KSBH00,0)=0) and ghlb00 not in (-99999)
                  group by b.FBBH00,decode(AD_KDKSTJ,1,JZKS00,0),YBMC00
                  union all
                  select  b.FBBH00,-1*count(1) GHCS00,decode(AD_KDKSTJ,1,JZKS00,0)JZKS00,YBMC00
                  from SF_BRXXB0 a,BM_BRXXB0 b,IC_YBBRLB f   
                  where a.BRID00=b.BRID00 and b.YBLB00=f.YBLB00 and b.FBBH00=f.FBBH00 and 
                        THRQ00 between ls_KSRQ01 and ls_JSRQ01 and JZYS00=AD_KDYS00
                        and (JZKS00=AD_KSBH00 or nvl(AD_KSBH00,0)=0)
                  group by b.FBBH00,decode(AD_KDKSTJ,1,JZKS00,0),YBMC00;
                  
             begin         
               for gh in c_gh loop
              update BM_YYSFTJ set SFXM21=nvl(SFXM21,0)+gh.GHCS00 where ID0000=ad_ID0000 and SFXM88=gh.FBBH00 and BMBH00=gh.JZKS00 and SJYSXM=gh.YBMC00;
               end loop;
             end;
                       
          
          end if;
       end if;
    
    else--按结账表来计算次均费用

    
    
       --费用
       insert into BM_YYSFTJ (ID0000,SFXM88,BMBH00,SFXM99,SJYSXM,SFXM01,SFXM02,SFXM03,SFXM30,SFXM32)
           select ad_ID0000,decode(d.FBBH00,3,1,d.FBBH00),decode(AD_KDKSTJ,1,KDKS00,0),AD_KDYS00,c.FBMC00,0,sum(b.HJJE00)HJJE00,
                  sum(decode(b.SFLB00,'3',b.HJJE00,0)) YPJE00,SUM(CASE WHEN b.sflb00 = '3' AND b.beizhu LIKE '%膏%' THEN b.hjje00 ELSE 0 END) GFYPJE,
                  sum(CASE WHEN (b.MZFPID='1'or b.MZFPID='2') and not exists (select 1 from BM_YD0000 where ZJBZ00='1' and YPNM00=b.XMBH00) THEN b.HJJE00 ELSE 0 END) XYCYJE
           from SF_FYMX00 b,BM_BRFBB0 c,SF_BRXXB0 d
           where b.CZRQ00 between AS_KSRQ00 and AS_JSRQ00 and 
                 b.MZH000=d.GHH000 and decode(d.FBBH00,3,1,d.FBBH00)=c.FBBH00 and b.KDYS00=AD_KDYS00 
                 and (b.KDKS00=AD_KSBH00 or nvl(AD_KSBH00,0)=0)
           group by decode(d.FBBH00,3,1,d.FBBH00),c.FBMC00,decode(AD_KDKSTJ,1,KDKS00,0);
       --挂号    
       declare cursor c_gh is select  decode(FBBH00,3,1,FBBH00)FBBH00,sum(GHCS00) GHCS00,decode(AD_KDKSTJ,1,JZKS00,0)JZKS00 from VW_SF_GHLSXX 
               where CZRQ00 between AS_KSRQ00 and AS_JSRQ00 and JZYS00=AD_KDYS00
               and (JZKS00=AD_KSBH00 or nvl(AD_KSBH00,0)=0) and ghlb00 not in (-99999)
               group by decode(FBBH00,3,1,FBBH00),decode(AD_KDKSTJ,1,JZKS00,0);
       begin         
         for gh in c_gh loop
           update BM_YYSFTJ set SFXM01=gh.GHCS00 where ID0000=ad_ID0000 and SFXM88=gh.FBBH00 and BMBH00=decode(AD_KDKSTJ,1,gh.JZKS00,0);
           if sql%notfound then
              insert into BM_YYSFTJ (ID0000,SFXM88,BMBH00,SFXM99,SJYSXM,SFXM01,SFXM02,SFXM03,SFXM30,SFXM32)
                 select ad_ID0000,FBBH00,decode(AD_KDKSTJ,1,gh.JZKS00,0),AD_KDYS00,FBMC00,gh.GHCS00,0,0,0,0 from BM_BRFBB0 where FBBH00=gh.FBBH00;
           end if;
         end loop;
       end;
       
       --医保费用和挂号

       if AS_FZXTJ0='1' then    
          --大中心 
          
          insert into BM_YYSFTJ (ID0000,SFXM88,BMBH00,SFXM99,SJYSXM,SFXM01,SFXM02,SFXM03,SFXM30,SFXM32)
               select ad_ID0000,3,decode(AD_KDKSTJ,1,d.JZKS00,0),AD_KDYS00,c.YBMC01,count(distinct d.YBGHH0) GHCS00,sum(b.BZ0000*e.HJJE00)HJJE00,
                        sum(decode(e.SFLB00,'3',b.BZ0000*e.HJJE00,0)) YPJE00,
                      sum(case when e.SFLB00='3' and e.BEIZHU like '%膏%' then b.BZ0000*e.HJJE00 else 0 end ) GFYPJE,
                      sum(CASE WHEN (e.MZFPID='1'or e.MZFPID='2') and not exists (select 1 from BM_YD0000 where ZJBZ00='1' and YPNM00=e.XMBH00) THEN b.BZ0000*e.HJJE00 ELSE 0 END) XYCYJE
               from SF_JZB000 a,SF_JZMXXM b,SF_FYMX00 e,VW_XT_YBBRLB c,SF_BRXXB0 d
               where a.JZRQ00 between AS_KSRQ00 and AS_JSRQ00 and a.FBBH00=3 and a.JZDH00=b.JZDH00 and 
                     a.MZID00=d.GHID00 and d.JZYS00=AD_KDYS00 and d.THRQ00 is null and 
                     a.YBLB00=c.YBLB00 and c.FBBH00=3 and b.MXID00=e.MXID00
                     and ( (e.KDKS00=AD_KSBH00) or (nvl(AD_KSBH00,0)=0) )
--               and (exists(select 1 from ZY_FYMX00 e,ZY_BRFY00 f where e.DJH000=f.DJH000 and f.JZDH00=a.JZDH00 and e.KDKS00=AD_KSBH00) or nvl(AD_KSBH00,0)=0 )      
               group by c.YBZXLB,c.YBMC01,decode(AD_KDKSTJ,1,d.JZKS00,0) ;   
       else       
          --分中心

          insert into BM_YYSFTJ (ID0000,SFXM88,BMBH00,SFXM99,SJYSXM,SFXM01,SFXM02,SFXM03,SFXM30,SFXM32)
               select ad_ID0000,3,decode(AD_KDKSTJ,1,d.JZKS00,0),d.JZYS00,c.YBMC00,count(distinct d.YBGHH0) GHCS00,sum(b.BZ0000*e.HJJE00)HJJE00,
                        sum(decode(e.SFLB00,'3',b.BZ0000*e.HJJE00,0)) YPJE00,
                      sum(case when e.SFLB00='3' and e.BEIZHU like '%膏%' then b.BZ0000*e.HJJE00 else 0 end ) GFYPJE,
                      sum(CASE WHEN (e.MZFPID='1'or e.MZFPID='2') and not exists (select 1 from BM_YD0000 where ZJBZ00='1' and YPNM00=e.XMBH00) THEN b.BZ0000*e.HJJE00 ELSE 0 END) XYCYJE
               from SF_JZB000 a,SF_JZMXXM b,SF_FYMX00 e,IC_YBBRLB c,SF_BRXXB0 d
               where a.JZRQ00 between AS_KSRQ00 and AS_JSRQ00 and a.FBBH00=3 and a.JZDH00=b.JZDH00 and 
                     a.MZID00=d.GHID00 and d.JZYS00=AD_KDYS00 and d.THRQ00 is null and 
                     a.YBLB00=c.YBLB00 and c.FBBH00=3 and b.MXID00=e.MXID00
                     and ( (e.KDKS00=AD_KSBH00) or (nvl(AD_KSBH00,0)=0) )
--               and (exists(select 1 from ZY_FYMX00 e,ZY_BRFY00 f where e.DJH000=f.DJH000 and f.JZDH00=a.JZDH00 and e.KDKS00=AD_KSBH00) or nvl(AD_KSBH00,0)=0 )      
               group by d.JZKS00,d.JZYS00,c.YBZXLB,c.YBMC00,decode(AD_KDKSTJ,1,d.JZKS00,0);  
       end if;
       
       select count(*) into Vcounter from BM_YYSFTJ where ID0000=ad_ID0000 and SFXM88=1;
       if Vcounter=0 then
          insert into BM_YYSFTJ (ID0000,SFXM88,BMBH00,SFXM99,SJYSXM,SFXM01,SFXM02,SFXM03,SFXM30,SFXM32)
                 select ad_ID0000,1,BMBH00,AD_KDYS00,'自费',0,0,0,0,0 from BM_YYSFTJ where ID0000=ad_ID0000 and SFXM88=3 group by BMBH00; 
       end if;     
       
       update  BM_YYSFTJ a set (SFXM01,SFXM02,SFXM03,SFXM30,SFXM32)=(select a.SFXM01-nvl(sum(b.SFXM01),0),a.SFXM02-nvl(sum(b.SFXM02),0),a.SFXM03-nvl(sum(b.SFXM03),0),a.SFXM30-nvl(sum(b.SFXM30),0),a.SFXM32-nvl(sum(b.SFXM32),0) 
               from BM_YYSFTJ b where ID0000=ad_ID0000 and SFXM88=3 and a.BMBH00=b.BMBH00)
               where ID0000=ad_ID0000 and SFXM88=1;
               
       --同比
       if ls_XSTB00=1 then
          --费用
          declare cursor c_fy is
              select decode(AD_KDKSTJ,1,b.KDKS00,0)KDKS00,decode(d.FBBH00,3,1,d.FBBH00) FBBH00,sum(b.HJJE00)HJJE00,
                     sum(decode(b.SFLB00,'3',b.HJJE00,0)) YPJE00
              from SF_FYMX00 b,BM_BRFBB0 c,SF_BRXXB0 d
              where b.CZRQ00 between ls_KSRQ00 and ls_JSRQ00 and 
                    b.MZH000=d.GHH000 and decode(d.FBBH00,3,1,d.FBBH00)=c.FBBH00 and b.KDYS00=AD_KDYS00 
                    and (b.KDKS00=AD_KSBH00 or nvl(AD_KSBH00,0)=0)
              group by decode(d.FBBH00,3,1,d.FBBH00),decode(AD_KDKSTJ,1,b.KDKS00,0);
          begin
            for fy in c_fy loop
              update BM_YYSFTJ set SFXM12=fy.HJJE00,SFXM13=fy.YPJE00 where ID0000=ad_ID0000 and SFXM88=fy.FBBH00 and BMBH00=fy.KDKS00;
            end loop;
          end;    
          --挂号    
          declare cursor c_gh is select  decode(AD_KDKSTJ,1,JZKS00,0)JZKS00,decode(FBBH00,3,1,FBBH00)FBBH00,sum(GHCS00) GHCS00 from VW_SF_GHLSXX 
                  where CZRQ00 between ls_KSRQ00 and ls_JSRQ00 and JZYS00=AD_KDYS00
                  and (JZKS00=AD_KSBH00 or nvl(AD_KSBH00,0)=0) and ghlb00 not in (-99999)
                  group by decode(FBBH00,3,1,FBBH00),decode(AD_KDKSTJ,1,JZKS00,0);
          begin         
            for gh in c_gh loop
              update BM_YYSFTJ set SFXM11=gh.GHCS00 where ID0000=ad_ID0000 and SFXM88=gh.FBBH00 and SFXM88=gh.FBBH00 and BMBH00=gh.JZKS00;
            end loop;
          end;
          
          
          
          --医保费用和挂号

          
          if AS_FZXTJ0='1' then    
             --大中心 
             declare cursor c_yb is 
                  select decode(AD_KDKSTJ,1,d.JZKS00,0)JZKS00,c.YBMC01,count(distinct d.YBGHH0) GHCS00,sum(b.XMJE00)HJJE00,
                         sum(decode(b.XMBH00,1,b.XMJE00,2,b.XMJE00,3,b.XMJE00,0))YPJE00
                  from SF_JZB000 a,SF_JZMX00 b,VW_XT_YBBRLB c,SF_BRXXB0 d
                  where a.JZRQ00 between ls_KSRQ00 and ls_JSRQ00 and a.FBBH00=3 and a.JZDH00=b.JZDH00 and 
                        a.MZID00=d.GHID00 and d.JZYS00=AD_KDYS00 and d.THRQ00 is null and 
                        a.YBLB00=c.YBLB00 and c.FBBH00=3 
                  and (exists(select 1 from ZY_FYMX00 e,ZY_BRFY00 f where e.DJH000=f.DJH000 and f.JZDH00=a.JZDH00 and e.KDKS00=AD_KSBH00) or nvl(AD_KSBH00,0)=0 )      
                  group by c.YBZXLB,c.YBMC01,decode(AD_KDKSTJ,1,d.JZKS00,0) ;
             begin         
               for yb in c_yb loop
                 update BM_YYSFTJ set SFXM11=yb.GHCS00,SFXM12=yb.HJJE00,SFXM13=yb.YPJE00 
                    where ID0000=ad_ID0000 and SFXM88=3 and SJYSXM=yb.YBMC01 and BMBH00=yb.JZKS00;
               end loop;
             end;
          else       
             --分中心

             declare cursor c_yb is 
                  select decode(AD_KDKSTJ,1,d.JZKS00,0)JZKS00,c.YBMC00,count(distinct d.YBGHH0) GHCS00,sum(b.XMJE00)HJJE00,
                         sum(decode(b.XMBH00,1,b.XMJE00,2,b.XMJE00,3,b.XMJE00,0))YPJE00
                  from SF_JZB000 a,SF_JZMX00 b,IC_YBBRLB c,SF_BRXXB0 d
                  where a.JZRQ00 between ls_KSRQ00 and ls_JSRQ00 and a.FBBH00=3 and a.JZDH00=b.JZDH00 and 
                        a.MZID00=d.GHID00 and d.JZYS00=AD_KDYS00 and d.THRQ00 is null and 
                        a.YBLB00=c.YBLB00 and c.FBBH00=3  and ghlb00 not in (-99999)
                  and (exists(select 1 from ZY_FYMX00 e,ZY_BRFY00 f where e.DJH000=f.DJH000 and f.JZDH00=a.JZDH00 and e.KDKS00=AD_KSBH00) or nvl(AD_KSBH00,0)=0 )      
                  group by d.JZKS00,d.JZYS00,c.YBMC00,decode(AD_KDKSTJ,1,d.JZKS00,0);
             begin         
               for yb in c_yb loop
                 update BM_YYSFTJ set SFXM11=yb.GHCS00,SFXM12=yb.HJJE00,SFXM13=yb.YPJE00 
                    where ID0000=ad_ID0000 and SFXM88=3 and SJYSXM=yb.YBMC00 and BMBH00=yb.JZKS00;
               end loop;
             end;
          end if;
          update  BM_YYSFTJ a set (SFXM11,SFXM12,SFXM13)=(select a.SFXM11-nvl(sum(b.SFXM11),0),a.SFXM12-nvl(sum(b.SFXM12),0),a.SFXM13-nvl(sum(b.SFXM13),0) 
                  from BM_YYSFTJ b where ID0000=ad_ID0000 and SFXM88=3 and a.BMBH00=b.BMBH00)
                  where ID0000=ad_ID0000 and SFXM88=1;  
          
          --环比
          --费用
          declare cursor c_fy is
              select decode(AD_KDKSTJ,1,b.KDKS00,0)KDKS00,decode(d.FBBH00,3,1,d.FBBH00) FBBH00,sum(b.HJJE00)HJJE00,
                     sum(decode(b.SFLB00,'3',1,b.HJJE00,0)) YPJE00
              from SF_FYMX00 b,BM_BRFBB0 c,SF_BRXXB0 d
              where b.CZRQ00 between ls_KSRQ01 and ls_JSRQ01 and 
                    b.MZH000=d.GHH000 and decode(d.FBBH00,3,1,d.FBBH00)=c.FBBH00 and b.KDYS00=AD_KDYS00 
                    and (b.KDKS00=AD_KSBH00 or nvl(AD_KSBH00,0)=0)
              group by decode(d.FBBH00,3,1,d.FBBH00),decode(AD_KDKSTJ,1,b.KDKS00,0);
          begin
            for fy in c_fy loop
              update BM_YYSFTJ set SFXM22=fy.HJJE00,SFXM23=fy.YPJE00 where ID0000=ad_ID0000 and SFXM88=fy.FBBH00 and BMBH00=fy.KDKS00;
            end loop;
          end;    
          --挂号    
          declare cursor c_gh is select  decode(AD_KDKSTJ,1,JZKS00,0)JZKS00,decode(FBBH00,3,1,FBBH00)FBBH00,sum(GHCS00) GHCS00 from VW_SF_GHLSXX 
                  where CZRQ00 between ls_KSRQ01 and ls_JSRQ01 and JZYS00=AD_KDYS00
                  and (JZKS00=AD_KSBH00 or nvl(AD_KSBH00,0)=0) and ghlb00 not in (-99999)
                  group by decode(FBBH00,3,1,FBBH00),decode(AD_KDKSTJ,1,JZKS00,0);
          begin         
            for gh in c_gh loop
              update BM_YYSFTJ set SFXM21=gh.GHCS00 where ID0000=ad_ID0000 and SFXM88=gh.FBBH00 and SFXM88=gh.FBBH00 and BMBH00=gh.JZKS00;
            end loop;
          end;
          
          --医保费用和挂号

          
          if AS_FZXTJ0='1' then    
             --大中心

             declare cursor c_yb is 
                  select decode(AD_KDKSTJ,1,d.JZKS00,0)JZKS00,c.YBMC01,count(distinct d.YBGHH0) GHCS00,sum(b.XMJE00)HJJE00,
                         sum(decode(b.XMBH00,1,b.XMJE00,2,b.XMJE00,3,b.XMJE00,0))YPJE00
                  from SF_JZB000 a,SF_JZMX00 b,VW_XT_YBBRLB c,SF_BRXXB0 d
                  where a.JZRQ00 between ls_KSRQ01 and ls_JSRQ01 and a.FBBH00=3 and a.JZDH00=b.JZDH00 and 
                        a.MZID00=d.GHID00 and d.JZYS00=AD_KDYS00 and d.THRQ00 is null and 
                        a.YBLB00=c.YBLB00 and c.FBBH00=3
                        and (exists(select 1 from ZY_FYMX00 e,ZY_BRFY00 f where e.DJH000=f.DJH000 and f.JZDH00=a.JZDH00 and e.KDKS00=AD_KSBH00) or nvl(AD_KSBH00,0)=0 )
                  group by c.YBZXLB,c.YBMC01,decode(AD_KDKSTJ,1,d.JZKS00,0) ;
             begin         
               for yb in c_yb loop
                 update BM_YYSFTJ set SFXM21=yb.GHCS00,SFXM22=yb.HJJE00,SFXM23=yb.YPJE00 
                    where ID0000=ad_ID0000 and SFXM88=3 and SJYSXM=yb.YBMC01 and BMBH00=yb.JZKS00;
               end loop;
             end;
          else       
             --分中心

             declare cursor c_yb is 
                  select decode(AD_KDKSTJ,1,d.JZKS00,0)JZKS00,c.YBMC00,count(distinct d.YBGHH0) GHCS00,sum(b.XMJE00)HJJE00,
                         sum(decode(b.XMBH00,1,b.XMJE00,2,b.XMJE00,3,b.XMJE00,0))YPJE00
                  from SF_JZB000 a,SF_JZMX00 b,IC_YBBRLB c,SF_BRXXB0 d
                  where a.JZRQ00 between ls_KSRQ01 and ls_JSRQ01 and a.FBBH00=3 and a.JZDH00=b.JZDH00 and 
                        a.MZID00=d.GHID00 and d.JZYS00=AD_KDYS00 and d.THRQ00 is null and 
                        a.YBLB00=c.YBLB00 and c.FBBH00=3 
                        and (exists(select 1 from ZY_FYMX00 e,ZY_BRFY00 f where e.DJH000=f.DJH000 and f.JZDH00=a.JZDH00 and e.KDKS00=AD_KSBH00) or nvl(AD_KSBH00,0)=0 )                        
                  group by d.JZKS00,d.JZYS00,c.YBMC00,decode(AD_KDKSTJ,1,d.JZKS00,0);
             begin         
               for yb in c_yb loop
                 update BM_YYSFTJ set SFXM21=yb.GHCS00,SFXM22=yb.HJJE00,SFXM23=yb.YPJE00 
                    where ID0000=ad_ID0000 and SFXM88=3 and SJYSXM=yb.YBMC00 and BMBH00=yb.JZKS00;
               end loop;
             end;
          end if;
          update  BM_YYSFTJ a set (SFXM21,SFXM22,SFXM23)=(select a.SFXM21-nvl(sum(b.SFXM21),0),a.SFXM22-nvl(sum(b.SFXM22),0),a.SFXM23-nvl(sum(b.SFXM23),0) 
                  from BM_YYSFTJ b where ID0000=ad_ID0000 and SFXM88=3 and a.BMBH00=b.BMBH00)
                  where ID0000=ad_ID0000 and SFXM88=1;  
       end if;
    end if;
    
    if AD_KDKSTJ=1 then
       update BM_YYSFTJ a set BMMC00=(select BMMC00 from BM_BMBM00 where BMBH00=a.BMBH00) where ID0000=ad_ID0000; 
    end if;
       
    --按医保中心统计时，如果不是按医生统计，则增加一个医生的小计栏

    if (AS_FZXTJ0 = 1 and AD_KDYS00 = 0) then
        insert into BM_YYSFTJ (ID0000,SFXM88,BMBH00,BMMC00,SFXM99,SJYSXM,SFXM01,SFXM02,SFXM03,SFXM30,SFXM32,SFXM11,SFXM12,SFXM13,SFXM21,SFXM22,SFXM23)
           select ad_ID0000,0,BMBH00,BMMC00,SFXM99,'小计',sum(SFXM01),sum(SFXM02),sum(SFXM03),sum(SFXM30),sum(SFXM32),
                  sum(SFXM11),sum(SFXM12),sum(SFXM13),sum(SFXM21),sum(SFXM22),sum(SFXM23) from BM_YYSFTJ 
                  where ID0000=ad_ID0000 group by BMBH00,BMMC00,SFXM99;
    end if;
    
    --汇总

    if not(AS_FZXTJ0 = 0 and AD_KDYS00 >0 and AD_KSBH00=0) then
        insert into BM_YYSFTJ (ID0000,SFXM88,BMBH00,SFXM99,SJYSXM,SFXM01,SFXM02,SFXM03,SFXM30,SFXM32,SFXM11,SFXM12,SFXM13,SFXM21,SFXM22,SFXM23,SFXM34)
           select ad_ID0000,0,0,0,'合计',sum(SFXM01),sum(SFXM02),sum(SFXM03),sum(SFXM30),sum(SFXM32),
                  sum(SFXM11),sum(SFXM12),sum(SFXM13),sum(SFXM21),sum(SFXM22),sum(SFXM23),sum(SFXM34) from BM_YYSFTJ 
                  where ID0000=ad_ID0000 and SJYSXM<>'小计';         
    end if;     
    
    
    update BM_YYSFTJ set SFXM01=nvl(SFXM01,0),SFXM02=nvl(SFXM02,0),SFXM03=nvl(SFXM03,0),SFXM30=nvl(SFXM30,0),SFXM32=nvl(SFXM32,0),
                         SFXM11=nvl(SFXM11,0),SFXM12=nvl(SFXM12,0),SFXM13=nvl(SFXM13,0),
                         SFXM21=nvl(SFXM21,0),SFXM22=nvl(SFXM22,0),SFXM23=nvl(SFXM23,0)
           where ID0000=ad_ID0000;  
                                
    --SFXM01：本期挂号人次 SFXM02本期总费用 SFXM03 本期药品费用      
    --SFXM04 本期次均费用 SFXM05 本期药品比例 SFXM06 本期次均药品费用
    update  BM_YYSFTJ set SFXM04=decode(SFXM01,0,0,SFXM02/SFXM01),
                          SFXM05=decode(SFXM02,0,0,SFXM03/SFXM02),
                          SFXM06=decode(SFXM01,0,0,SFXM03/SFXM01),
                          SFXM14=decode(SFXM11,0,0,SFXM12/SFXM11),
                          SFXM15=decode(SFXM12,0,0,SFXM13/SFXM12),
                          SFXM16=decode(SFXM11,0,0,SFXM13/SFXM11),
                          SFXM24=decode(SFXM21,0,0,SFXM22/SFXM21),
                          SFXM25=decode(SFXM22,0,0,SFXM23/SFXM22),
                          SFXM26=decode(SFXM21,0,0,SFXM23/SFXM21),
                          SFXM31=decode(SFXM02,0,0,(SFXM03-SFXM30)/SFXM02),
                          SFXM33=(case when ls_XYCYYZDSFPC='Y' then decode((SFXM02-SFXM03+SFXM32),0,0,SFXM32/(SFXM02-SFXM03+SFXM32)) else decode(SFXM02,0,0,SFXM32/SFXM02) end),
                          SFXM35=decode(SFXM34,0,0,SFXM34/SFXM02) --耗占比

            where ID0000=ad_ID0000;

    --SFXM17:挂号人次同比增长 SFXM18 总费用同比增长 SFXM19 次均费用同比增长 
    update  BM_YYSFTJ set SFXM17=decode(SFXM11,0,null,(SFXM01-SFXM11)/SFXM11),
                          SFXM18=decode(SFXM12,0,null,(SFXM02-SFXM12)/SFXM12),
                          SFXM19=decode(SFXM14,0,null,null,null,(SFXM04-SFXM14)/SFXM14),
                          SFXM27=decode(SFXM21,0,null,(SFXM01-SFXM21)/SFXM21),
                          SFXM28=decode(SFXM22,0,null,(SFXM02-SFXM22)/SFXM22),
                          SFXM29=decode(SFXM24,0,null,null,null,(SFXM04-SFXM24)/SFXM24)
            where ID0000=ad_ID0000;
        
--commit;
exception
  when no_data_found then
    raise_application_error(-20001,substrb('没有找到数据！*'||sqlerrm||Ptsxx00,1,240));
  when Ecustom       then
    raise_application_error(-20010,substrb(Ptsxx00||'！*',1,240));
  when others        then
    raise_application_error(-20002,substrb(nvl(sqlerrm, '原因不明出错')||'！*',1,240));    
end;