CREATE OR REPLACE PROCEDURE SP_MZZY_YYSFTJ_DXTZ
(
        --ad_ID0000    OUT   NUMBER,
        --as_YHMSG0    OUT   VARCHAR,
        --as_SYSMSG    OUT   VARCHAR,
        as_zyypkl    in    char default 'Y'
)
as
        ls_GHKS00    SF_BRXXB0.GHKS00%TYPE;
        ls_BMMC00    BM_BMBM00.BMMC00%TYPE;
        ls_GHY000    BM_YGBM00.ygbh00%TYPE;
        lv_MZKSBH    BM_YGBM00.BMBH00%TYPE;
        ls_COUNT0    NUMBER(5);
        ls_GHLBBH    CHAR(2);
        VCounter     NUMBER(5);
        ls_MZRC00    NUMBER;
        as_KSRQ00    varchar2(8);
        as_JSRQ00    varchar2(8);
        lvZRQ0000    varchar2(20);
        lvYRQ0000    varchar2(20);
        I number(3,0);
        LsSJFS00     varchar2(20);
        ad_ID0000    number;
  CURSOR  CUR_SF_MZGHL0  IS
          select GHKS00,GHY000,sum(GHCS00) GHCS00 from  VW_SF_GHLSXX
                         WHERE  CZRQ00 >= as_KSRQ00 AND CZRQ00 <= as_JSRQ00
                                and (GHKS00=lv_MZKSBH or lv_MZKSBH=0)
                group by GHKS00,GHY000;

  cursor CUR_MZ_FYHZ is
        select a.GHKS00,a.GHY000,sum(decode(a.SFLB00,'3',decode(as_zyypkl,'Y',a.HJJE00,decode(a.MZFPID,3,0,a.HJJE00)))) MZYPJE,sum(a.HJJE00) MZZJE0,
          --sum(decode(a.SFLB00,'3',decode((select count(1) from BM_YD0000 where YPNM00=a.XMBH00 and LBBH00 in (0,1) and nvl(ZJBZ00,'0')<>'1'),1,a.HJJE00,0),0)) XYCYJE,
          sum(decode(a.SFLB00,'3',decode(a.GHKS00,a.KDKS00,decode((select count(1) from BM_YD0000 where YPNM00=a.XMBH00 and LBBH00 in (0,1) and nvl(ZJBZ00,'0')<>'1'),1,a.HJJE00,0),0),0)) XYCYJE,
          sum(decode(a.GHKS00,a.KDKS00,a.HJJE00,0)) KDMZFY,
          sum(decode(XMBH00,9000004609,HJJE00,9000010253,HJJE00,0)) TSYP00,
          sum(decode(a.SFLB00,'3',decode(a.GHKS00,a.KDKS00,decode((select count(1) from BM_YD0000 where YPNM00=a.XMBH00 and (LBBH00=2 or LBBH00 in (0,1) and nvl(ZJBZ00,'0')='1')),1,a.HJJE00,0),0),0)) MZXCZJ  --西药成药制剂和中药金额
          from SF_FYMX00 a where a.CZRQ00>= as_KSRQ00 AND a.CZRQ00 <= as_JSRQ00 and (a.GHKS00=lv_MZKSBH or lv_MZKSBH=0) and a.GHKS00 is not null group by  a.GHKS00,a.GHY000;
  lv_MZ_FYHZ CUR_MZ_FYHZ%rowtype;

  CURSOR CUR_MZ_YPSYTJ IS
         select a.ghks00,a.ysgzh0,
                count(distinct nvl(a.CFLSH0,'0'))-1   CFZS00,
                sum(decode(cfs000,8  ,1,0))  ghs000,
                sum(decode(cfs000,99,1,301,1,302,1,303,1,0)) ZYCFS0
         from vw_jcbb_ypsyltj a
        where a.czrq00>=as_KSRQ00 and a.czrq00<=as_JSRQ00  and (a.GHKS00=lv_MZKSBH or lv_MZKSBH=0) group by a.ghks00,a.ysgzh0;

  lv_MZ_YPSYTJ CUR_MZ_YPSYTJ%rowtype;

  cursor CUR_SS_KSGZLTJ is
          select a.MZZYBZ,nvl(b.DYMZKS,b.BMBH00) SQKS00,a.SSYS00,
                 sum(a.tdss00)+sum(a.dss000)+sum(a.zss000)+sum(a.xss000)+sum(a.qtss00) hj0000,0 SSZ000
                 from vw_jcbb_ss_ksgzltj a,BM_BMBM00 b where a.SQRQ00>= as_KSRQ00 AND a.SQRQ00 <= as_JSRQ00 and a.sszt00 IN ('2', '4')
                 and decode(a.MZZYBZ,'0','门诊','1','住院')='门诊'  and a.SQKS00=b.BMBH00 and (nvl(b.DYMZKS,b.BMBH00)=lv_MZKSBH or lv_MZKSBH=0) group by a.MZZYBZ,nvl(b.DYMZKS,b.BMBH00),a.SSYS00
          union all
          select a.MZZYBZ,nvl(b.DYMZKS,b.BMBH00) SQKS00,to_char(a.YSZID0),
                 sum(a.tdss00)+sum(a.dss000)+sum(a.zss000)+sum(a.xss000)+sum(a.qtss00) hj0000,
                 sum(case when a.SSKS00=2240 or a.SSKS00=2242 then a.tdss00 else 0 end)*1.4+sum(case when a.SSKS00=2240 or a.SSKS00=2242 then a.dss000 else 0 end)*1+
                 sum(case when a.SSKS00=2240 or a.SSKS00=2242 then a.zss000 else 0 end)*0.7+sum(case when a.SSKS00=2240 or a.SSKS00=2242 then a.xss000 else 0 end)*0.35 SSZ000
                 from vw_jcbb_ss_ksgzltj a,BM_BMBM00 b where a.SQRQ00>= as_KSRQ00 AND a.SQRQ00 <= as_JSRQ00 and a.sszt00 IN ('2', '4')
                 and decode(a.MZZYBZ,'0','门诊','1','住院')='住院'  and a.SQKS00=b.BMBH00 and (nvl(b.DYMZKS,b.BMBH00)=lv_MZKSBH or lv_MZKSBH=0) group by a.MZZYBZ,nvl(b.DYMZKS,b.BMBH00),a.YSZID0;

  lv_SS_KSGZLTJ CUR_SS_KSGZLTJ%rowtype;

  cursor CUR_ZY_CYPJTS is      --出院平均天数 and 科室病人实际占用床日数
        select nvl(b.DYMZKS,b.BMBH00) BMBH00,a.YSZID0,sum(RYRS00) RYRS00,sum(ZRRS00) ZRRS00,sum(ZCRS00) ZCRS00,sum(YXCRS0) YXCRS0,sum(round(YXCYRS,2)) YXCYRS,sum(XYRS00) XYRS00
               from ZH_ZYRBB0 a,BM_BMBM00 b where a.KSH000=b.BMBH00
               and a.ZYRQ00>= as_KSRQ00 AND a.ZYRQ00 <= as_JSRQ00 and (nvl(b.DYMZKS,b.BMBH00)=lv_MZKSBH or lv_MZKSBH=0)  group by nvl(b.DYMZKS,b.BMBH00),a.YSZID0;
  lv_ZY_CYPJTS CUR_ZY_CYPJTS%rowtype;

  cursor CUR_ZY_FYHZ is     --住院费用汇总    2241=麻醉科
         select nvl(b.DYMZKS,b.BMBH00) BRDQKS,a.YSZID0,sum(case when a.SFLB00='3' and (as_zyypkl='Y' or as_zyypkl<>'Y' and a.ZYFPID<>3) then a.HJJE00 else 0 end) ZYYPJE,sum(a.HJJE00) ZYZJE0,
            sum(case when a.SFLB00='2' then a.HJJE00 else 0 end) HCZJE0,  --耗材总金额
            --sum(decode(a.SFLB00,'3',decode((select count(1) from BM_YD0000 where YPNM00=a.XMBH00 and LBBH00 in (0,1) and nvl(ZJBZ00,'0')<>'1'),1,a.HJJE00,0),0)) XYCYJE, --西药成药金额(不包含本院制剂)
            --sum(decode(a.SFLB00,'3',(case when (a.BRDQKS=a.KDKS00 or nvl(b.DYMZKS,-1)=nvl(c.DYMZKS,-2)) and (select count(1) from BM_YD0000 where YPNM00=a.XMBH00 and LBBH00 in (0,1) and nvl(ZJBZ00,'0')<>'1')=1 then
            sum(decode(a.SFLB00,'3',(case when (select count(1) from BM_YD0000 where YPNM00=a.XMBH00 and LBBH00 in (0,1) and nvl(ZJBZ00,'0')<>'1')=1 then
                a.HJJE00 else 0 end),0)) XYCYJE, --西药成药金额(不包含本院制剂)
            --sum(case when (a.BRDQKS=a.KDKS00 or nvl(b.DYMZKS,-1)=nvl(c.DYMZKS,-2)) then a.HJJE00 else 0 end) KDZYFY,
            sum(a.HJJE00) KDZYFY,
            --decode(a.kdks00,2241,2241,0) MZKS00,
            0 MZKS00,
            sum(decode(XMBH00,9000004609,HJJE00,9000010253,HJJE00,0)) TSYP00,
            sum(decode(a.SFLB00,'3',(case when (select count(1) from BM_YD0000 where YPNM00=a.XMBH00 and (LBBH00=2 or LBBH00 in (0,1) and nvl(ZJBZ00,'0')='1'))=1 then
                a.HJJE00 else 0 end),0)) ZYXCZJ  --西药成药制剂和中药金额
          from ZY_FYMX00 a,BM_BMBM00 b
          where a.CZRQ00>= as_KSRQ00 AND a.CZRQ00 <= as_JSRQ00 and a.KDKS00=b.BMBH00 and (nvl(b.DYMZKS,b.BMBH00)=lv_MZKSBH or lv_MZKSBH=0)
          group by  nvl(b.DYMZKS,b.BMBH00),decode(a.kdks00,2241,2241,0),a.YSZID0;

        lv_ZY_FYHZ CUR_ZY_FYHZ%rowtype;

  cursor CUR_CY_FYHZ is     --出院病人费用汇总    2241=麻醉科
        --2014.04.30 2241=麻醉科 单列
        select nvl(b.DYMZKS,b.BMBH00) BRDQKS,a.YSZID0,sum(decode(a.KDKS00,2241,0,a.HJJE00)) CYZJE0,decode(a.kdks00,2241,2241,0) MZKS00,sum(decode(a.KDKS00,2241,a.HJJE00,0)) MZZJE0,
        sum(decode(XMBH00,9000004609,HJJE00,9000010253,HJJE00,0)) TSYP00
          from ZY_FYMX00 a,BM_BMBM00 b,ZY_BRXXB0 c where   a.BRDQKS=b.BMBH00 and a.ZYID00=c.ZYID00
          and c.SJCYRQ>= as_KSRQ00 AND c.SJCYRQ <= as_JSRQ00 and c.BRZT00 in ('3','4','5') and (nvl(b.DYMZKS,b.BMBH00)=lv_MZKSBH or lv_MZKSBH=0) group by  nvl(b.DYMZKS,b.BMBH00),decode(a.kdks00,2241,2241,0),a.YSZID0;

        lv_CY_FYHZ CUR_CY_FYHZ%rowtype;

  cursor Cur_ZY_WZBR is
        select nvl(b.DYMZKS,b.BMBH00) BMBH00,a.YSZID0,count(*) RS0000 from BQ_YJYZ00 a,BM_BMBM00 b,ZY_BRXXB0 c
        where YZZT00 in ('2','3') and a.ksh000=b.bmbh00 and c.ZYID00=a.ZYID00 and ((ZLXMJC like '%病重%') or (ZLXMJC like '%病危%') ) and
            as_JSRQ00>=QYRQ00 and as_KSRQ00<nvl(TZRQ00,'20991231') and (nvl(b.DYMZKS,b.BMBH00)=lv_MZKSBH or lv_MZKSBH=0)  group by nvl(b.DYMZKS,b.BMBH00),a.YSZID0;
    lv_ZY_WZBR Cur_ZY_WZBR%rowtype;

        --门诊中医非药物治疗
        CurSor Cur_SFXM41 is
            select d.GHKS00,d.GHY000,count(distinct d.GHID00) GHS000 from SF_BRXXB0 d where d.GHRQ00  between as_KSRQ00 and as_JSRQ00
                                 and exists (select 1 from SF_FYMX00 b,BM_YYSFXM c where  b.XMBH00=c.SFXMID
                                 and  (
                                     (c.TREEBH like '0919%' or  c.TREEBH like '0920%') and c.xmmc00 not like '%中医辩证论治%'
                                      or c.xmmc00 like '%换药%' and b.zxks00 in (select bmbh00 from bm_bmbm00 where bmmc00 like '肛肠%')
                                      )
                                 AND b.MZH000=d.GHH000)
             group by d.GHKS00,d.GHY000;
       --住院中医非药物治疗
       CurSor Cur_SFXM43 is
            select nvl(e.DYMZKS,e.BMBH00) BMBH00,d.YSZID0,count(distinct d.ZYID00) ZYRC00 from zy_zybrmx d,BM_BMBM00 e where d.zyrq00 between as_KSRQ00 and as_JSRQ00 and d.FLAG00='2'
                                 and d.KSH000=e.BMBH00
                                 and exists ( select 1 from ZY_FYMX00 b,BM_YYSFXM c where d.ZYID00=b.ZYID00
                                 and b.XMBH00=c.SFXMID
                                 and  (
                                      (c.TREEBH like '0919%' or  c.TREEBH like '0920%') and c.xmmc00 not like '%中医辩证论治%'
                                      or c.xmmc00 like '%换药%' and b.zxks00 in (select bmbh00 from bm_bmbm00 where bmmc00 like '肛肠%')
                                      )
                                 )
             group by nvl(e.DYMZKS,e.BMBH00),d.YSZID0;


  lv_KQZYKS  bm_bmbm00.bmbh00%type;
  lv_KQMZKS  bm_bmbm00.bmbh00%type;
  lv_GDNR00  xt_xtcs00.value0%type;

-- MODIFICATION HISTORY
-- Person      Date        Comments
-- qks         2015.03.24  西成药药占比有关的指标改成按申请科室口径统计；开单科室对应BM_BMBM00.DYMZKS如果一致,也属于本科室开单
                        -- 修改 MZXCB0、ZYXCB0统计指标，改为跟130712报表一致
-- qks         2015.04.01  非药物中医治疗率(ZYZYFW)统计指标修改,改为跟130711,130712报表一致
-- qks         2015.07.23  于“口腔科”跟“口腔科(住院)”是分开独立核算，互相对应没有维护，导致目前发送“口腔科(住院)”科日报短信，住院的相关统计指标值都是0
-- qks         2015.07.29  住院费用统计使用开单科室统计; 非药物中医治疗率(ZYZYFW)统计指标修改,改为跟130712报表一致
-- qks         2016.02.25  西成药药占比，分母也要剔除本院制剂金额；
-- qks         2016.07.05  西成药药占比，分母也要剔除中药金额：MZXCZJ、ZYXCZJ改为包含中药金额
-- qks         2017.03.08  短信内容最后面增加[信息中心],新增参数YJ_DXTZGDNR维护; for YJ9-20170308-001
-- qks         2019.01.14  改为自定义归档; for YJ9-20190114-001
begin
  select max(BMBH00) into lv_KQZYKS from bm_bmbm00 where bmmc00='口腔科(病区)';
  select max(BMBH00) into lv_KQMZKS from bm_bmbm00 where bmmc00='口腔科';
  delete from BM_YYSFTJ_DXTZ;
  commit;
  select nvl(max(trim(VALUE0)),'') into lv_GDNR00 from xt_xtcs00 where name00='YJ_DXTZGDNR';
  for i in 1..3 loop
   lv_MZKSBH:=0;
   LsSJFS00:='N';
   if i=1 then
     ad_ID0000:=1;
     as_KSRQ00:=to_char(sysdate-1,'YYYYMMDD');
     as_JSRQ00:=to_char(sysdate-1,'YYYYMMDD');
     LsSJFS00:='Y';
   elsif (i=2) and  (to_char(sysdate,'day')='星期一') then
      ad_ID0000:=2;
     as_KSRQ00:=to_char(sysdate-7,'YYYYMMDD');
     as_JSRQ00:=to_char(sysdate-1,'YYYYMMDD');
     LsSJFS00:='Y';
   elsif (i=3) and (to_char(sysdate,'dd')='01') then
      ad_ID0000:=3;
      as_KSRQ00:=to_char(trunc(add_months(sysdate,-1)),'YYYYMMDD');
     as_JSRQ00:=to_char(last_day(add_months(sysdate,-1)),'YYYYMMDD');
     LsSJFS00:='Y';
   end if;
   if LsSJFS00='Y' then
   OPEN CUR_SF_MZGHL0;
           LOOP
              FETCH CUR_SF_MZGHL0 INTO  ls_GHKS00,ls_GHY000,ls_COUNT0;
              EXIT WHEN CUR_SF_MZGHL0%NOTFOUND;
              Update  BM_YYSFTJ_DXTZ  Set  MZGHL0 = NVL(MZGHL0,0)+ls_COUNT0  where  BMBH00 = ls_GHKS00  and ID0000=ad_ID0000 and YGBH00 = ls_GHY000;
              if SQL%NOTFOUND  then
                 Insert into BM_YYSFTJ_DXTZ(ID0000,BMBH00,YGBH00,MZGHL0)  values(ad_ID0000,ls_GHKS00,ls_GHY000,ls_COUNT0);
              end if;
           END LOOP;
    CLOSE CUR_SF_MZGHL0;

    OPEN CUR_ZY_CYPJTS;
           LOOP
              FETCH CUR_ZY_CYPJTS INTO  lv_ZY_CYPJTS;
              EXIT WHEN CUR_ZY_CYPJTS%NOTFOUND;
               Update  BM_YYSFTJ_DXTZ  Set  ZYYXCR = NVL(ZYYXCR,0)+lv_ZY_CYPJTS.YXCRS0,ZYYXCY = NVL(ZYYXCY,0)+lv_ZY_CYPJTS.YXCYRS,ZYYXZC = NVL(ZYYXZC,0)+lv_ZY_CYPJTS.XYRS00,
                                       ZYSZRS = NVL(ZYSZRS,0)+lv_ZY_CYPJTS.RYRS00,ZYZRRS = NVL(ZYZRRS,0)+lv_ZY_CYPJTS.ZRRS00,ZYZCRS = NVL(ZYZCRS,0)+lv_ZY_CYPJTS.ZCRS00
                 where  BMBH00 = lv_ZY_CYPJTS.BMBH00  and ID0000=ad_ID0000 and YSZID0 = lv_ZY_CYPJTS.YSZID0;
              if SQL%NOTFOUND  then
                 Insert into BM_YYSFTJ_DXTZ(ID0000,BMBH00,YSZID0,ZYYXCR,ZYYXCY,ZYYXZC,ZYSZRS,ZYZRRS,ZYZCRS)  values
                 (ad_ID0000,lv_ZY_CYPJTS.BMBH00,lv_ZY_CYPJTS.YSZID0,lv_ZY_CYPJTS.YXCRS0,lv_ZY_CYPJTS.YXCYRS,lv_ZY_CYPJTS.XYRS00,lv_ZY_CYPJTS.RYRS00,lv_ZY_CYPJTS.ZRRS00,lv_ZY_CYPJTS.ZCRS00);
              end if;
           END LOOP;
    CLOSE CUR_ZY_CYPJTS;




    OPEN CUR_MZ_FYHZ;
           LOOP
              FETCH CUR_MZ_FYHZ INTO  lv_MZ_FYHZ;
              EXIT WHEN CUR_MZ_FYHZ%NOTFOUND;
               Update  BM_YYSFTJ_DXTZ  Set  MZYPFY = NVL(MZYPFY,0)+lv_MZ_FYHZ.MZYPJE,MZZJE0 = NVL(MZZJE0,0)+lv_MZ_FYHZ.MZZJE0,MZXCJE=NVL(MZXCJE,0)+lv_MZ_FYHZ.XYCYJE,KDMZFY=NVL(KDMZFY,0)+lv_MZ_FYHZ.KDMZFY,
                       MZTSYP = NVL(MZTSYP,0)+lv_MZ_FYHZ.TSYP00,
                       MZXCZJ = NVL(MZXCZJ,0)+lv_MZ_FYHZ.MZXCZJ
                 where  BMBH00 = lv_MZ_FYHZ.GHKS00  and ID0000=ad_ID0000 and YGBH00 = lv_MZ_FYHZ.GHY000;
              if SQL%NOTFOUND  then
                 Insert into BM_YYSFTJ_DXTZ(ID0000,BMBH00,YGBH00,MZYPFY,MZZJE0,MZCJFY,MZTSYP,KDMZFY,MZXCZJ)  values(ad_ID0000,lv_MZ_FYHZ.GHKS00,lv_MZ_FYHZ.GHY000,lv_MZ_FYHZ.MZYPJE,lv_MZ_FYHZ.MZZJE0,lv_MZ_FYHZ.XYCYJE,lv_MZ_FYHZ.TSYP00,lv_MZ_FYHZ.KDMZFY,lv_MZ_FYHZ.MZXCZJ);
              end if;
           END LOOP;
    CLOSE CUR_MZ_FYHZ;


    OPEN CUR_ZY_FYHZ;
           LOOP
              FETCH CUR_ZY_FYHZ INTO  lv_ZY_FYHZ;
              EXIT WHEN CUR_ZY_FYHZ%NOTFOUND;
               Update  BM_YYSFTJ_DXTZ  Set  ZYYPFY = NVL(ZYYPFY,0)+lv_ZY_FYHZ.ZYYPJE,ZYZJE0 = NVL(ZYZJE0,0)+lv_ZY_FYHZ.ZYZJE0,ZYHCJE = NVL(ZYHCJE,0)+lv_ZY_FYHZ.HCZJE0,ZYXCJE = NVL(ZYXCJE,0)+lv_ZY_FYHZ.XYCYJE,KDZYFY=NVL(KDZYFY,0)+lv_ZY_FYHZ.KDZYFY,
                                       MZKJE0 = nvl(MZKJE0,0)+decode(lv_ZY_FYHZ.mzks00,2241,lv_ZY_FYHZ.ZYZJE0,0),
                                       ZYTSYP = NVL(ZYTSYP,0)+lv_ZY_FYHZ.TSYP00,
                                       ZYXCZJ = NVL(ZYXCZJ,0)+lv_ZY_FYHZ.ZYXCZJ
                 where  BMBH00 = lv_ZY_FYHZ.BRDQKS  and ID0000=ad_ID0000 and YSZID0 = lv_ZY_FYHZ.YSZID0;
              if SQL%NOTFOUND  then
                 Insert into BM_YYSFTJ_DXTZ(ID0000,BMBH00,YSZID0,ZYYPFY,ZYZJE0,ZYHCJE,ZYXCJE,MZKJE0,ZYTSYP,KDZYFY,ZYXCZJ)  values(ad_ID0000,lv_ZY_FYHZ.BRDQKS,lv_ZY_FYHZ.YSZID0,lv_ZY_FYHZ.ZYYPJE,lv_ZY_FYHZ.ZYZJE0,lv_ZY_FYHZ.HCZJE0,lv_ZY_FYHZ.XYCYJE,decode(lv_ZY_FYHZ.mzks00,2241,lv_ZY_FYHZ.ZYZJE0,0),lv_ZY_FYHZ.TSYP00,lv_ZY_FYHZ.KDZYFY,lv_ZY_FYHZ.ZYXCZJ);
              end if;
              /*
              if lv_ZY_FYHZ.mzks00=2241 then
                Update  BM_YYSFTJ_DXTZ  Set  ZYYPFY = NVL(ZYYPFY,0)+lv_ZY_FYHZ.ZYYPJE,ZYZJE0 = NVL(ZYZJE0,0)+lv_ZY_FYHZ.ZYZJE0,ZYHCJE = NVL(ZYHCJE,0)+lv_ZY_FYHZ.HCZJE0,ZYXCJE = NVL(ZYXCJE,0)+lv_ZY_FYHZ.XYCYJE
                   where  BMBH00 = lv_ZY_FYHZ.mzks00  and ID0000=ad_ID0000 and YSZID0 = -1;
                if SQL%NOTFOUND  then
                   Insert into BM_YYSFTJ_DXTZ(ID0000,BMBH00,YSZID0,ZYYPFY,ZYZJE0,ZYHCJE,ZYXCJE)  values(ad_ID0000,lv_ZY_FYHZ.mzks00,-1,lv_ZY_FYHZ.ZYYPJE,lv_ZY_FYHZ.ZYZJE0,lv_ZY_FYHZ.HCZJE0,lv_ZY_FYHZ.XYCYJE);
                end if;
                --负数对抵
                Update  BM_YYSFTJ_DXTZ  Set  ZYYPFY = NVL(ZYYPFY,0)-lv_ZY_FYHZ.ZYYPJE,ZYZJE0 = NVL(ZYZJE0,0)-lv_ZY_FYHZ.ZYZJE0,ZYHCJE = NVL(ZYHCJE,0)-lv_ZY_FYHZ.HCZJE0,ZYXCJE = NVL(ZYXCJE,0)-lv_ZY_FYHZ.XYCYJE
                   where  BMBH00 = -1  and ID0000=ad_ID0000 and YSZID0 = -1;
                if SQL%NOTFOUND  then
                   Insert into BM_YYSFTJ_DXTZ(ID0000,BMBH00,YSZID0,ZYYPFY,ZYZJE0,ZYHCJE,ZYXCJE)  values(ad_ID0000,-1,-1,-lv_ZY_FYHZ.ZYYPJE,-lv_ZY_FYHZ.ZYZJE0,-lv_ZY_FYHZ.HCZJE0,-lv_ZY_FYHZ.XYCYJE);
                end if;
              end if;
              */
           END LOOP;
    CLOSE CUR_ZY_FYHZ;

    OPEN CUR_CY_FYHZ;
           LOOP
              FETCH CUR_CY_FYHZ INTO  lv_CY_FYHZ;
              EXIT WHEN CUR_CY_FYHZ%NOTFOUND;
               Update  BM_YYSFTJ_DXTZ  Set  CYRZFY = NVL(CYRZFY,0)+lv_CY_FYHZ.CYZJE0,
                                       ZYTSYP= NVL(ZYTSYP,0)+lv_CY_FYHZ.TSYP00
                where  BMBH00 = lv_CY_FYHZ.BRDQKS  and ID0000=ad_ID0000 and YSZID0 = lv_CY_FYHZ.YSZID0;
              if SQL%NOTFOUND  then
                 Insert into BM_YYSFTJ_DXTZ(ID0000,BMBH00,YSZID0,CYRZFY,ZYTSYP)  values(ad_ID0000,lv_CY_FYHZ.BRDQKS,lv_CY_FYHZ.YSZID0,lv_CY_FYHZ.CYZJE0,lv_CY_FYHZ.TSYP00);
              end if;

              if lv_ZY_FYHZ.mzks00=2241 then
                Update  BM_YYSFTJ_DXTZ  Set  CYRZFY = NVL(CYRZFY,0)+lv_CY_FYHZ.MZZJE0 where  BMBH00 = lv_CY_FYHZ.mzks00  and ID0000=ad_ID0000 and YSZID0 = -1;
                if SQL%NOTFOUND  then
                   Insert into BM_YYSFTJ_DXTZ(ID0000,BMBH00,YSZID0,CYRZFY)  values(ad_ID0000,lv_CY_FYHZ.mzks00,-1,lv_CY_FYHZ.MZZJE0);
                end if;
                --负数对抵
                Update  BM_YYSFTJ_DXTZ  Set  CYRZFY = NVL(CYRZFY,0)-lv_CY_FYHZ.MZZJE0 where  BMBH00 = -1  and ID0000=ad_ID0000 and YSZID0 = -1;
                if SQL%NOTFOUND  then
                   Insert into BM_YYSFTJ_DXTZ(ID0000,BMBH00,YSZID0,CYRZFY)  values(ad_ID0000,-1,-1,-lv_CY_FYHZ.MZZJE0);
                end if;
              end if;

           END LOOP;
    CLOSE CUR_CY_FYHZ;



    OPEN Cur_ZY_WZBR;
           LOOP
              FETCH Cur_ZY_WZBR INTO  lv_ZY_WZBR;
              EXIT WHEN Cur_ZY_WZBR%NOTFOUND;
               Update  BM_YYSFTJ_DXTZ  Set  BWBR00 = NVL(BWBR00,0)+lv_ZY_WZBR.RS0000
                 where  BMBH00 = lv_ZY_WZBR.BMBH00  and ID0000=ad_ID0000 and YSZID0 = lv_ZY_WZBR.YSZID0;
              if SQL%NOTFOUND  then
                 Insert into BM_YYSFTJ_DXTZ(ID0000,BMBH00,YSZID0,BWBR00)  values(ad_ID0000,lv_ZY_WZBR.BMBH00,lv_ZY_WZBR.YSZID0,lv_ZY_WZBR.RS0000);
              end if;
           END LOOP;
    CLOSE Cur_ZY_WZBR;


    OPEN CUR_SS_KSGZLTJ;
         LOOP
            FETCH CUR_SS_KSGZLTJ INTO  lv_SS_KSGZLTJ;
            EXIT WHEN CUR_SS_KSGZLTJ%NOTFOUND;
            if lv_SS_KSGZLTJ.MZZYBZ='0'  then
              begin
                Update  BM_YYSFTJ_DXTZ  Set  MZSSZL = NVL(MZSSZL,0)+lv_SS_KSGZLTJ.HJ0000,
                                        MZSSZ0 = NVL(MZSSZ0,0)+lv_SS_KSGZLTJ.SSZ000
                  where  BMBH00 = lv_SS_KSGZLTJ.SQKS00  and ID0000=ad_ID0000 and YGBH00 = lv_SS_KSGZLTJ.SSYS00;
                if SQL%NOTFOUND  then
                  Insert into BM_YYSFTJ_DXTZ(ID0000,BMBH00,YGBH00,MZSSZL,MZSSZ0)
                  values(ad_ID0000,lv_SS_KSGZLTJ.SQKS00,lv_SS_KSGZLTJ.SSYS00,lv_SS_KSGZLTJ.HJ0000,lv_SS_KSGZLTJ.SSZ000);
                end if;
              end;
              else begin
                Update  BM_YYSFTJ_DXTZ  Set  ZYSSL0 = NVL(ZYSSL0,0)+lv_SS_KSGZLTJ.HJ0000,
                                        ZYSSZ0 = NVL(ZYSSZ0,0)+lv_SS_KSGZLTJ.SSZ000
                  where  BMBH00 = lv_SS_KSGZLTJ.SQKS00  and ID0000=ad_ID0000 and YSZID0 = lv_SS_KSGZLTJ.SSYS00;
                if SQL%NOTFOUND  then
                  Insert into BM_YYSFTJ_DXTZ(ID0000,BMBH00,YSZID0,ZYSSL0,ZYSSZ0)
                  values(ad_ID0000,lv_SS_KSGZLTJ.SQKS00,lv_SS_KSGZLTJ.SSYS00,lv_SS_KSGZLTJ.HJ0000,lv_SS_KSGZLTJ.SSZ000);
                end if;
              end;
            end if;
         END LOOP;
  CLOSE CUR_SS_KSGZLTJ;

   update BM_YYSFTJ_DXTZ a set SSHCJE=(select sum(b.HJJE00) from ZY_FYMX00 b,BM_YYSFXM c,BM_BMBM00 d where b.SFLB00='2' and b.XMBH00=c.SFXMID and
             (c.GJBM00 like '910189%' or c.GJBM00 like '910023%' or c.GJBM00 like '910194%'or c.GJBM00 like '93%' or (b.XMDJ00>=1000 and c.GJBM00 like '9%')) and b.ZXKS00<>2302  --介入室
             and b.CZRQ00 between as_KSRQ00 and as_JSRQ00 and b.BRDQKS=d.BMBH00 and nvl(d.DYMZKS,d.BMBH00)=a.BMBH00 and b.YSZID0=a.YSZID0) where  a.ID0000=ad_ID0000 and a.YSZID0 is not null;
   /*update BM_YYSFTJ_DXTZ e set BWBR00=(select count(*)  from BQ_YJYZ00 a,BM_BMBM00 b,ZY_BRXXB0 c
        where YZZT00 in ('2','3') and a.ksh000=b.bmbh00 and c.ZYID00=a.ZYID00 and ((ZLXMJC like '%病重%') or (ZLXMJC like '%病危%') ) and
            as_KSRQ00>=QYRQ00 and as_JSRQ00<nvl(TZRQ00,'20991231')  and nvl(b.DYMZKS,b.BMBH00)=e.BMBH00 and a.YSZID0=e.YSZID0) where  e.ID0000=ad_ID0000 and e.YSZID0 is not null; */

    OPEN CUR_MZ_YPSYTJ;
    LOOP
      FETCH CUR_MZ_YPSYTJ INTO lv_MZ_YPSYTJ;
      EXIT WHEN CUR_MZ_YPSYTJ%NOTFOUND;
          Update  BM_YYSFTJ_DXTZ  Set  MZCFL0 = NVL(MZCFL0,0)+lv_MZ_YPSYTJ.CFZS00,MZZYS0=nvl(MZZYS0,0)+lv_MZ_YPSYTJ.ZYCFS0
             where  BMBH00 = lv_MZ_YPSYTJ.GHKS00  and ID0000=ad_ID0000 and YGBH00 = lv_MZ_YPSYTJ.YSGZH0;
          if SQL%NOTFOUND  then
            Insert into BM_YYSFTJ_DXTZ(ID0000,BMBH00,YGBH00,MZCFL0,MZZYS0)  values(ad_ID0000,lv_MZ_YPSYTJ.GHKS00,lv_MZ_YPSYTJ.YSGZH0,lv_MZ_YPSYTJ.CFZS00,lv_MZ_YPSYTJ.ZYCFS0);
          end if;
    END LOOP;
    CLOSE CUR_MZ_YPSYTJ;

    select count(1) into ls_MZRC00 from vw_ss_yw0000 where SQRQ00>=as_KSRQ00 and SQRQ00<=as_JSRQ00 and SSZT00 in ('2','4') and mzzybz='1' and MZBH00 is not null;
    Update BM_YYSFTJ_DXTZ a set ZYYXCY=decode(a.BMBH00,-1,-ls_MZRC00,ls_MZRC00) where (BMBH00=2241 or BMBH00=-1) and ID0000=ad_ID0000;

    Update  BM_YYSFTJ_DXTZ a  Set  ZYZYTS =(select sum(b.ZQCYTS) from BQ_YPYZ00 b,BM_BMBM00 c where b.YPLBBH='2' and b.QYRQ00 between to_char(to_date(as_KSRQ00,'YYYYMMDD')-7,'YYYYMMDD') and  to_char(to_date(as_JSRQ00,'YYYYMMDD')+7,'YYYYMMDD')
                                       and b.ZXRQ00 between as_KSRQ00 and as_JSRQ00 and b.YZZT00 in ('2','3') and b.CLBZ00='1' and b.KSH000=c.BMBH00 and nvl(c.DYMZKS,c.BMBH00)=a.BMBH00 and b.YSZID0=a.YSZID0)
             where a.ID0000=ad_ID0000 and a.YSZID0 is not null;

    /*
    update BM_YYSFTJ_DXTZ a set MZZYFW=(select count(distinct d.GHID00) from SF_BRXXB0 d where d.GHRQ00  between as_KSRQ00 and as_JSRQ00 and  d.GHKS00=a.BMBH00 and d.GHY000=a.YGBH00
                                 and exists (select 1 from SF_FYMX00 b,BM_YYSFXM c where  b.XMBH00=c.SFXMID
                                 and  (c.treebh like '0920%' or c.treebh like '0919%')  and  c.BH0000  not in ('048000000610','048000000630','048000000640','048000000620')
                                 and  b.MZH000=d.GHH000)
                                 )
                         where a.ID0000=ad_ID0000 and a.YSZID0 is null;
                         */
    /*
    update BM_YYSFTJ_DXTZ a set ZYZYFW=(select count(distinct d.ZYID00) from ZY_BRXXB0 d,BM_BMBM00 e where d.SJCYRQ between as_KSRQ00 and as_JSRQ00 and d.BRZT00 in ('3','4','5')
                                 and exists ( select 1 from ZY_FYMX00 b,BM_YYSFXM c where d.ZYID00=b.ZYID00
                                 and b.XMBH00=c.SFXMID and  (c.treebh like '0920%' or c.treebh like '0919%') and  c.BH0000  not in ('048000000610','048000000630','048000000640','048000000620')
                                 and d.DQKS00=e.BMBH00 and nvl(e.DYMZKS,e.BMBH00)=a.BMBH00  and d.YSZID0=a.YSZID0)
                                 )
                         where a.ID0000=ad_ID0000 and a.YSZID0 is not null;
    */
   /*  --2015.07.29
   update BM_YYSFTJ_DXTZ a set ZYZYFW=(select count(distinct f.ZYID00) from BM_BMBM00 e,ZY_ZYBRMX f
                               where f.ZYRQ00 between as_KSRQ00 and as_JSRQ00 and f.FLAG00='2'
                                 and exists(select 1 from ZY_FYMX00 b,BM_YYSFXM c where f.ZYID00=b.ZYID00
                                 and b.XMBH00=c.SFXMID and  (c.treebh like '0920%' or c.treebh like '0919%') and  c.BH0000  not in ('048000000610','048000000630','048000000640','048000000620')
                                  )
                                 and f.KSH000=e.BMBH00 and nvl(e.DYMZKS,e.BMBH00)=a.BMBH00  and f.YSZID0=a.YSZID0
                                 )
                         where a.ID0000=ad_ID0000 and a.YSZID0 is not null;
   */
   --2015.07.29 非药物中医治疗率(ZYZYFW)统计指标修改,改为跟130712报表一
    for tj in CUR_SFXM41 loop
      Update BM_YYSFTJ_DXTZ set MZZYFW=tj.GHS000
       where BMBH00 = tj.GHKS00  and ID0000=ad_ID0000 and YGBH00 = tj.GHY000;
    end loop;
    for tj in CUR_SFXM43 loop
      Update BM_YYSFTJ_DXTZ set ZYZYFW=tj.ZYRC00
       where BMBH00 = tj.BMBH00  and ID0000=ad_ID0000 and YSZID0 = tj.YSZID0;
    end loop;

   update BM_YYSFTJ_DXTZ set TJJLLX=0 where ID0000=ad_ID0000;
   update BM_YYSFTJ_DXTZ set TJJLLX=9 where ID0000=ad_ID0000 and YSZID0 is not null and TJJLLX=0;

   insert into BM_YYSFTJ_DXTZ(ID0000,BMBH00,MZGHL0,MZYPFY,MZZJE0,MZXCJE,MZCFL0,MZZYS0,MZZYFW,MZTSYP,YSZID0,TJJLLX,
                              ZYYXCR,ZYYXCY,ZYYXZC,ZYSZRS,ZYZRRS,ZYZCRS,ZYYPFY,ZYZJE0,ZYHCJE,ZYXCJE,MZKJE0,ZYTSYP,ZYSSL0,ZYSSZ0,ZYZYFW,CYRZFY,SSHCJE,ZYZYTS,BWBR00,
                              KDZYFY,KDMZFY,MZXCZJ,ZYXCZJ)
       select ad_ID0000,a.BMBH00,sum(nvl(MZGHL0,0)),sum(nvl(MZYPFY,0)),sum(nvl(MZZJE0,0)),sum(nvl(MZXCJE,0)),sum(nvl(MZCFL0,0)),sum(nvl(MZZYS0,0)),
              sum(nvl(MZZYFW,0)),sum(nvl(MZTSYP,0)),max(a.YSZID0),0,
              sum(nvl(ZYYXCR,0)),sum(nvl(ZYYXCY,0)),sum(nvl(ZYYXZC,0)),sum(nvl(ZYSZRS,0)),sum(nvl(ZYZRRS,0)),sum(nvl(ZYZCRS,0)),sum(nvl(ZYYPFY,0)),
              sum(nvl(ZYZJE0,0)),sum(nvl(ZYHCJE,0)),sum(nvl(ZYXCJE,0)),sum(nvl(MZKJE0,0)),sum(nvl(ZYTSYP,0)),sum(nvl(ZYSSL0,0)),sum(nvl(ZYSSZ0,0)),
              sum(nvl(ZYZYFW,0)),sum(nvl(CYRZFY,0)),sum(nvl(SSHCJE,0)),sum(nvl(ZYZYTS,0)),sum(nvl(BWBR00,0)),sum(nvl(KDZYFY,0)),sum(nvl(KDMZFY,0)),
              sum(nvl(MZXCZJ,0)),sum(nvl(ZYXCZJ,0))
              from BM_YYSFTJ_DXTZ a,ZS_YSZ000 b where a.ID0000=ad_ID0000 and a.TJJLLX=9 and a.YSZID0=b.YSZID0(+) group by a.BMBH00,b.YSZMC0;
   delete BM_YYSFTJ_DXTZ where ID0000=ad_ID0000 and YSZID0 is not null and TJJLLX=9;

   insert into BM_YYSFTJ_DXTZ(ID0000,BMBH00,YGBH00,MZGHL0,MZYPFY,MZZJE0,MZXCJE,MZCFL0,MZZYS0,MZZYFW,MZTSYP,YSZID0,TJJLLX,
                              ZYYXCR,ZYYXCY,ZYYXZC,ZYSZRS,ZYZRRS,ZYZCRS,ZYYPFY,ZYZJE0,ZYHCJE,ZYXCJE,MZKJE0,ZYTSYP,ZYSSL0,ZYSSZ0,ZYZYFW,CYRZFY,SSHCJE ,ZYZYTS,BWBR00,
                              KDZYFY,KDMZFY,MZXCZJ,ZYXCZJ)
       select ad_ID0000,BMBH00,99998,sum(nvl(MZGHL0,0)),sum(nvl(MZYPFY,0)),sum(nvl(MZZJE0,0)),sum(nvl(MZXCJE,0)),sum(nvl(MZCFL0,0)),sum(nvl(MZZYS0,0)),
              sum(nvl(MZZYFW,0)),sum(nvl(MZTSYP,0)),99998,1 ,
              sum(nvl(ZYYXCR,0)),sum(nvl(ZYYXCY,0)),sum(nvl(ZYYXZC,0)),sum(nvl(ZYSZRS,0)),sum(nvl(ZYZRRS,0)),sum(nvl(ZYZCRS,0)),sum(nvl(ZYYPFY,0)),
              sum(nvl(ZYZJE0,0)),sum(nvl(ZYHCJE,0)),sum(nvl(ZYXCJE,0)),sum(nvl(MZKJE0,0)),sum(nvl(ZYTSYP,0)),sum(nvl(ZYSSL0,0)),sum(nvl(ZYSSZ0,0)),
              sum(nvl(ZYZYFW,0)),sum(nvl(CYRZFY,0)),sum(nvl(SSHCJE,0)),sum(nvl(ZYZYTS,0)),sum(nvl(BWBR00,0)),sum(nvl(KDZYFY,0)),sum(nvl(KDMZFY,0)),
              sum(nvl(MZXCZJ,0)),sum(nvl(ZYXCZJ,0)) from BM_YYSFTJ_DXTZ where ID0000=ad_ID0000 and TJJLLX=0 group by BMBH00;

   update BM_YYSFTJ_DXTZ a set KSKFCW=(select sum(b.KFCW00) from VW_JCBB_KSSYCW b,BM_BMBM00 c where b.ZYRQ00 between as_KSRQ00 and as_JSRQ00 and b.KSH000=c.BMBH00 and nvl(c.DYMZKS,c.BMBH00)=a.BMBH00)
                             where a.ID0000=ad_ID0000 and a.TJJLLX=1;



   insert into BM_YYSFTJ_DXTZ(ID0000,BMBH00,YGBH00,MZGHL0,MZYPFY,MZZJE0,MZXCJE,MZCFL0,MZZYS0,MZZYFW,MZTSYP,YSZID0,TJJLLX,
                              ZYYXCR,ZYYXCY,ZYYXZC,ZYSZRS,ZYZRRS,ZYZCRS,ZYYPFY,ZYZJE0,ZYHCJE,ZYXCJE,MZKJE0,ZYTSYP,ZYSSL0,ZYSSZ0,ZYZYFW,CYRZFY,SSHCJE,KSKFCW,ZYZYTS,BWBR00,
                              KDZYFY,KDMZFY,MZXCZJ,ZYXCZJ)
            select ad_ID0000,99999,99999,sum(nvl(MZGHL0,0)),sum(nvl(MZYPFY,0)),sum(nvl(MZZJE0,0)),sum(nvl(MZXCJE,0)),sum(nvl(MZCFL0,0)),sum(nvl(MZZYS0,0)),
              sum(nvl(MZZYFW,0)),sum(nvl(MZTSYP,0)),99999,2 ,
              sum(nvl(ZYYXCR,0)),sum(nvl(ZYYXCY,0)),sum(nvl(ZYYXZC,0)),sum(nvl(ZYSZRS,0)),sum(nvl(ZYZRRS,0)),sum(nvl(ZYZCRS,0)),sum(nvl(ZYYPFY,0)),
              sum(nvl(ZYZJE0,0)),sum(nvl(ZYHCJE,0)),sum(nvl(ZYXCJE,0)),sum(nvl(MZKJE0,0)),sum(nvl(ZYTSYP,0)),sum(nvl(ZYSSL0,0)),sum(nvl(ZYSSZ0,0)),
              sum(nvl(ZYZYFW,0)),sum(nvl(CYRZFY,0)),sum(nvl(SSHCJE,0)),sum(nvl(KSKFCW,0)),sum(nvl(ZYZYTS,0)),sum(nvl(BWBR00,0)),sum(nvl(KDZYFY,0)),sum(nvl(KDMZFY,0)),
              sum(nvl(MZXCZJ,0)),sum(nvl(ZYXCZJ,0)) from BM_YYSFTJ_DXTZ where ID0000=ad_ID0000 and TJJLLX=1 ;


   insert into BM_YYSFTJ_DXTZ(ID0000,BMBH00,YGBH00,MZGHL0,MZYPFY,MZZJE0,MZXCJE,MZCFL0,MZZYS0,MZZYFW,MZTSYP,YSZID0,TJJLLX,
                              ZYYXCR,ZYYXCY,ZYYXZC,ZYSZRS,ZYZRRS,ZYZCRS,ZYYPFY,ZYZJE0,ZYHCJE,ZYXCJE,MZKJE0,ZYTSYP,ZYSSL0,ZYSSZ0,ZYZYFW,CYRZFY,SSHCJE,KSKFCW,ZYZYTS,BWBR00,KDZYFY,KDMZFY,MZXCZJ,ZYXCZJ)
            select ID0000,99996,YGBH00,MZGHL0,MZYPFY,MZZJE0,MZXCJE,MZCFL0,MZZYS0,MZZYFW,MZTSYP,YSZID0,0 ,
            ZYYXCR,ZYYXCY,ZYYXZC,ZYSZRS,ZYZRRS,ZYZCRS,ZYYPFY,ZYZJE0,ZYHCJE,ZYXCJE,MZKJE0,ZYTSYP,ZYSSL0,ZYSSZ0,ZYZYFW,CYRZFY,SSHCJE,KSKFCW,ZYZYTS,BWBR00,KDZYFY,KDMZFY,
            MZXCZJ,ZYXCZJ
              from BM_YYSFTJ_DXTZ where ID0000=ad_ID0000 and BMBH00 in (2195,2196,2209,2214,2210,2223) and TJJLLX=0 ;

   insert into BM_YYSFTJ_DXTZ(ID0000,BMBH00,YGBH00,MZGHL0,MZYPFY,MZZJE0,MZXCJE,MZCFL0,MZZYS0,MZZYFW,MZTSYP,YSZID0,TJJLLX,
                              ZYYXCR,ZYYXCY,ZYYXZC,ZYSZRS,ZYZRRS,ZYZCRS,ZYYPFY,ZYZJE0,ZYHCJE,ZYXCJE,MZKJE0,ZYTSYP,ZYSSL0,ZYSSZ0,ZYZYFW,CYRZFY,SSHCJE,KSKFCW,ZYZYTS,BWBR00,KDZYFY,KDMZFY,MZXCZJ,ZYXCZJ)
            select ad_ID0000,99997,99997,sum(nvl(MZGHL0,0)),sum(nvl(MZYPFY,0)),sum(nvl(MZZJE0,0)),sum(nvl(MZXCJE,0)),sum(nvl(MZCFL0,0)),sum(nvl(MZZYS0,0)),
              sum(nvl(MZZYFW,0)),sum(nvl(MZTSYP,0)),99997,1,
              sum(nvl(ZYYXCR,0)),sum(nvl(ZYYXCY,0)),sum(nvl(ZYYXZC,0)),sum(nvl(ZYSZRS,0)),sum(nvl(ZYZRRS,0)),sum(nvl(ZYZCRS,0)),sum(nvl(ZYYPFY,0)),
              sum(nvl(ZYZJE0,0)),sum(nvl(ZYHCJE,0)),sum(nvl(ZYXCJE,0)),sum(nvl(MZKJE0,0)),sum(nvl(ZYTSYP,0)),sum(nvl(ZYSSL0,0)),sum(nvl(ZYSSZ0,0)),
              sum(nvl(ZYZYFW,0)),sum(nvl(CYRZFY,0)),sum(nvl(SSHCJE,0)),sum(nvl(KSKFCW,0)),sum(nvl(ZYZYTS,0)),sum(nvl(BWBR00,0)),sum(nvl(KDZYFY,0)),sum(nvl(KDMZFY,0)),
              sum(nvl(MZXCZJ,0)),sum(nvl(ZYXCZJ,0))
                   from BM_YYSFTJ_DXTZ where ID0000=ad_ID0000
                   and BMBH00 in (2195,2196,2209,2214,2210,2223) and TJJLLX=1;

  Update BM_YYSFTJ_DXTZ set MZYPFY=MZYPFY-nvl(MZTSYP,0),
                           MZZJE0=MZZJE0-nvl(MZTSYP,0),
                           ZYYPFY=ZYYPFY-nvl(ZYTSYP,0),
                           ZYZJE0=ZYZJE0-nvl(ZYTSYP,0),
                           MZXCJE=MZXCJE-nvl(MZTSYP,0),
                           ZYXCJE=ZYXCJE-nvl(ZYTSYP,0)
            where ID0000=ad_ID0000 and BMBH00 in (2244,2150);

  update BM_YYSFTJ_DXTZ set MZYZB0=decode(sign(MZZJE0),0,0,round(MZYPFY*100/MZZJE0,2)),MZCJFY=decode(sign(MZGHL0),0,0,round(MZZJE0    /MZGHL0,2)),
                           MZZYL0=decode(sign(MZCFL0),0,0,round(MZZYS0*100/MZCFL0,2)),MZFWL0=decode(sign(MZGHL0),0,0,round(MZZYFW*100/MZGHL0,2)),
                           --MZXCB0=decode(sign(MZZJE0),0,0,round(MZXCJE*100/MZZJE0,2))
                           --MZXCB0=decode(sign(KDMZFY),0,0,round(MZXCJE*100/KDMZFY,2))
                           MZXCB0=decode(sign(KDMZFY-nvl(MZXCZJ,0)),0,0,round(MZXCJE*100/(KDMZFY-nvl(MZXCZJ,0)),2))
                           where ID0000=ad_ID0000 and (YSZID0 is null or TJJLLX>0);

  update BM_YYSFTJ_DXTZ set ZYPJTS=decode(sign(ZYYXCR),0,0,round(ZYYXCR    /ZYYXCY,2)),KSCWSYL=decode(sign(KSKFCW),0,0,round(ZYYXZC*100/KSKFCW,2)),
                           ZYYZB0=decode(sign(ZYZJE0),0,0,round(ZYYPFY*100/ZYZJE0,2)),ZYHCB0=decode(sign(ZYZJE0-ZYYPFY),0,0,round(ZYHCJE*100/(ZYZJE0-ZYYPFY),2)),
                           CYRJFY=decode(sign(ZYYXCY),0,0,round(CYRZFY    /ZYYXCY,0)),SSHCB0=decode(sign(ZYSSZ0),0,0,round(SSHCJE    /ZYSSZ0,2)),
                           ZYZYL0=decode(sign(ZYYXZC),0,0,round(ZYZYTS*100/ZYYXZC,2)),ZYFWL0=decode(sign(ZYYXCY),0,0,round(ZYZYFW*100/ZYYXCY,2)),
                           ZYCJFY=decode(sign(ZYYXCR),0,0,round((ZYZJE0-nvl(MZKJE0,0))    /ZYYXCR,2)),
                           --ZYXCB0=decode(sign(ZYZJE0),0,0,round(ZYXCJE*100/ZYZJE0,2))
                           --ZYXCB0=decode(sign(KDZYFY),0,0,round(ZYXCJE*100/KDZYFY,2))
                           ZYXCB0=decode(sign(KDZYFY-nvl(ZYXCZJ,0)),0,0,round(ZYXCJE*100/(KDZYFY-nvl(ZYXCZJ,0)),2))
                           where ID0000=ad_ID0000 and (YSZID0 is not null or TJJLLX>0);

  --2015.07.23 把“口腔科(住院)”合并到“口腔科”
  Update BM_YYSFTJ_DXTZ a set (ZYYXCR,ZYYXCY,ZYYXZC,ZYSZRS,ZYZRRS,ZYZCRS,ZYYPFY,ZYZJE0,ZYHCJE,ZYXCJE,MZKJE0,ZYTSYP,ZYSSL0,ZYSSZ0,ZYZYFW,CYRZFY,SSHCJE,KSKFCW,ZYZYTS,BWBR00,KDZYFY,
                             ZYPJTS,ZYYZB0,CYRJFY,ZYZYL0,ZYCJFY,ZYXCB0,ZYXCZJ)
                          =(select ZYYXCR,ZYYXCY,ZYYXZC,ZYSZRS,ZYZRRS,ZYZCRS,ZYYPFY,ZYZJE0,ZYHCJE,ZYXCJE,MZKJE0,ZYTSYP,ZYSSL0,ZYSSZ0,ZYZYFW,CYRZFY,SSHCJE,KSKFCW,ZYZYTS,BWBR00,KDZYFY,
                             ZYPJTS,ZYYZB0,CYRJFY,ZYZYL0,ZYCJFY,ZYXCB0,ZYXCZJ
                            from BM_YYSFTJ_DXTZ where bmbh00=lv_KQZYKS
                            and TJJLLX=a.TJJLLX and ID0000=a.ID0000)
              where bmbh00=lv_KQMZKS and TJJLLX=1 and id0000 in (1,2,3);

  update BM_YYSFTJ_DXTZ set QYYZB0=decode(sign(nvl(MZZJE0,0)+nvl(ZYZJE0,0)),0,0,round((nvl(MZYPFY,0)+nvl(ZYYPFY,0))*100/(nvl(MZZJE0,0)+nvl(ZYZJE0,0)),2)),
                           QYXCB0=decode(sign(nvl(KDMZFY,0)+nvl(KDZYFY,0)-nvl(MZXCZJ,0)-nvl(ZYXCZJ,0)),0,0,round((nvl(MZXCJE,0)+nvl(ZYXCJE,0))*100/(nvl(KDMZFY,0)+nvl(KDZYFY,0)-nvl(MZXCZJ,0)-nvl(ZYXCZJ,0)),2))
                           where ID0000=ad_ID0000 ;

  delete from BM_YYSFTJ_DXTZ where TJJLLX=0 and YSZID0 is null;
  if i=1 then
    update BM_YYSFTJ_DXTZ set TJRQ00=to_char(sysdate-1,'YYYYMMDD'),TJJSRQ=to_char(sysdate-1,'YYYYMMDD') where ID0000=ad_ID0000;
  elsif  i=2 then
    update BM_YYSFTJ_DXTZ set TJRQ00=to_char(sysdate-7,'YYYYMMDD'),TJJSRQ=to_char(sysdate-1,'YYYYMMDD') where ID0000=ad_ID0000;
  elsif i=3 then
    update BM_YYSFTJ_DXTZ set TJRQ00=to_char(trunc(add_months(sysdate,-1)),'YYYYMM'),TJJSRQ=to_char(last_day(add_months(sysdate,-1)),'YYYYMMDD') where ID0000=ad_ID0000;
  end if;

 end if;
 end loop;
 commit;


 --科短信整理
  insert into YJ_DXTZ00(DXID00,DXNRFL,DXNR00,YWRQ00,CZRQ00,CZSJ00,DH0000,JSR000,BRID00)
          select  SQ_YJ_DXTZ00_DXID00.nextval,'科日报','<'||to_char(to_number(substr(a.TJRQ00,5,2)))||'月'||to_char(to_number(substr(a.TJRQ00,7,2)))||'日 '||E.BMMC00||' 日报>'||
           '门诊:挂号量:'||to_char(a.MZGHL0)||',次均费用:'||to_char(a.MZCJFY)||';住院：收入人数:'||to_char(ZYSZRS)||',在科人数:'||
          to_char(ZYYXZC)||',出院人数:'||to_char(ZYYXCY)||',出院人均费用:'||to_char(CYRJFY)||',出院平均住院日:'||to_char(ZYPJTS)||
          ',手术:'||to_char(ZYSSL0)||',病危'||to_char(BWBR00)||lv_GDNR00  NR0000,a.TJRQ00, to_char(sysdate,'yyyymmdd'),'08:00:00',
          d.DH0000,d.ZWXM00,0 from BM_YYSFTJ_DXTZ a,(select b.BMBH00,b.DH0000,b.zwxm00 from BM_YGBM00 b ,BM_TYZD00 c where
          b.xzzw00=c.nbbh00 and ((c.mc0000 like '%科主任%') or (c.mc0000 like '%科副主任%')) and b.DH0000 is not null) d , BM_BMBM00 E
          where TJJLLX in (1) and id0000=1 and a.BMBH00 not in (-1,0) and e.bmxz00 in ('0','1') and a.BMBH00 not in (99997,99996)
          and a.bmbh00 =d.bmbh00 and a.bmbh00=E.BMBH00  and not exists (select  1 from YJ_DXTZ00 where d.DH0000=DH0000 and DXNRFL='科日报' and YWRQ00=a.TJRQ00);

  insert into YJ_DXTZ00(DXID00,DXNRFL,DXNR00,YWRQ00,CZRQ00,CZSJ00,DH0000,JSR000,BRID00)
          select  SQ_YJ_DXTZ00_DXID00.nextval,'科日报','<'||to_char(to_number(substr(a.TJRQ00,5,2)))||'月'||to_char(to_number(substr(a.TJRQ00,7,2)))||'日 '||E.BMMC00||' 日报>'||
           '门诊:挂号量:'||to_char(a.MZGHL0)||',次均费用:'||to_char(a.MZCJFY)||';住院：收入人数:'||to_char(ZYSZRS)||',在科人数:'||
          to_char(ZYYXZC)||',出院人数:'||to_char(ZYYXCY)||',出院人均费用:'||to_char(CYRJFY)||',出院平均住院日:'||to_char(ZYPJTS)||
          ',手术:'||to_char(ZYSSL0)||',病危'||to_char(BWBR00)||lv_GDNR00  NR0000,a.TJRQ00, to_char(sysdate,'yyyymmdd'),'08:00:00',
          d.DH0000,d.ZWXM00,0 from BM_YYSFTJ_DXTZ a,(select 2214 bmbh00,b.DH0000,b.zwxm00 from BM_YGBM00 b ,BM_TYZD00 c where
          b.xzzw00=c.nbbh00 and ((c.mc0000 like '%科主任%') or (c.mc0000 like '%科副主任%')) and b.DH0000 is not null and b.ygbh00 in (2330)) d , BM_BMBM00 E
          where TJJLLX in (1) and id0000=1 and a.BMBH00 not in (-1,0) and e.bmxz00 in ('0','1') and a.BMBH00 not in (99997,99996)
          and a.bmbh00 =d.bmbh00 and a.bmbh00=E.BMBH00  and not exists (select  1 from YJ_DXTZ00 where d.DH0000=DH0000 and DXNRFL='科日报' and YWRQ00=a.TJRQ00 and
           dh0000  in (13950205901) and DXNR00  like '%康复科%');


  insert into YJ_DXTZ00(DXID00,DXNRFL,DXNR00,YWRQ00,CZRQ00,CZSJ00,DH0000,JSR000,BRID00)
  select  SQ_YJ_DXTZ00_DXID00.nextval,'科月报','<'||to_char(to_number(substr(a.TJRQ00,5,2)))||'月'||E.BMMC00||' 月报>'||
           '门诊:挂号量:'||to_char(a.MZGHL0)||',次均费用:'||to_char(a.MZCJFY)||',西成药占比：'||to_char(MZXCB0)||',中药使用率：'||to_char(MZZYL0)||
           ',非药物中医治疗率:'||to_char(MZFWL0)||';住院：收入人数:'||to_char(ZYSZRS)||',在科人数:'||
           to_char(ZYYXZC)||',出院人数:'||to_char(ZYYXCY)||',病床使用率：'||to_char(KSCWSYL)||',出院人均费用:'||to_char(CYRJFY)||',出院平均住院日:'||to_char(ZYPJTS)||
           ',手术:'||to_char(ZYSSL0)||',病危：'||to_char(BWBR00)||',西成药占比:'||to_char(ZYXCB0)||',中药使用率:'||to_char(ZYZYL0)||',非药物中医治疗率'||
           to_char(ZYFWL0)||';门诊住院合并西成药占:'||to_char(QYXCB0)||lv_GDNR00  NR0000,a.TJRQ00, to_char(sysdate,'yyyymmdd'),'08:00:00',d.DH0000,d.ZWXM00,0
           from BM_YYSFTJ_DXTZ a,(select b.BMBH00,b.DH0000,b.zwxm00 from BM_YGBM00 b ,BM_TYZD00 c where
            b.xzzw00=c.nbbh00 and ((c.mc0000 like '%科主任%') or (c.mc0000 like '%科副主任%')) and b.DH0000 is not null) d , BM_BMBM00 E
            where TJJLLX in (1) and id0000=3 and a.BMBH00 not in (-1,0) and e.bmxz00 in ('0','1') and a.BMBH00 not in (99997,99996)
             and a.bmbh00 =d.bmbh00 and a.bmbh00=E.BMBH00 and not exists (select  1 from YJ_DXTZ00 where d.DH0000=DH0000 and DXNRFL='科月报' and YWRQ00=a.TJRQ00);

  insert into YJ_DXTZ00(DXID00,DXNRFL,DXNR00,YWRQ00,CZRQ00,CZSJ00,DH0000,JSR000,BRID00)
  select  SQ_YJ_DXTZ00_DXID00.nextval,'科月报','<'||to_char(to_number(substr(a.TJRQ00,5,2)))||'月'||E.BMMC00||' 月报>'||
           '门诊:挂号量:'||to_char(a.MZGHL0)||',次均费用:'||to_char(a.MZCJFY)||',西成药占比：'||to_char(MZXCB0)||',中药使用率：'||to_char(MZZYL0)||
           ',非药物中医治疗率:'||to_char(MZFWL0)||';住院：收入人数:'||to_char(ZYSZRS)||',在科人数:'||
           to_char(ZYYXZC)||',出院人数:'||to_char(ZYYXCY)||',病床使用率：'||to_char(KSCWSYL)||',出院人均费用:'||to_char(CYRJFY)||',出院平均住院日:'||to_char(ZYPJTS)||
           ',手术:'||to_char(ZYSSL0)||',病危：'||to_char(BWBR00)||',西成药占比:'||to_char(ZYXCB0)||',中药使用率:'||to_char(ZYZYL0)||',非药物中医治疗率'||
           to_char(ZYFWL0)||';门诊住院合并西成药占:'||to_char(QYXCB0)||lv_GDNR00  NR0000,a.TJRQ00, to_char(sysdate,'yyyymmdd'),'08:00:00',d.DH0000,d.ZWXM00,0
           from BM_YYSFTJ_DXTZ a,(select 2214 BMBH00,b.DH0000,b.zwxm00 from BM_YGBM00 b ,BM_TYZD00 c where
            b.xzzw00=c.nbbh00 and ((c.mc0000 like '%科主任%') or (c.mc0000 like '%科副主任%')) and b.DH0000 is not null and b.ygbh00 in (2330)) d , BM_BMBM00 E
            where TJJLLX in (1) and id0000=3 and a.BMBH00 not in (-1,0) and e.bmxz00 in ('0','1') and a.BMBH00 not in (99997,99996)
             and a.bmbh00 =d.bmbh00 and a.bmbh00=E.BMBH00 and not exists (select  1 from YJ_DXTZ00 where d.DH0000=DH0000 and DXNRFL='科月报' and YWRQ00=a.TJRQ00 and
           dh0000 not in (13950205901) and DXNR00  like '%康复科%' );

  insert into YJ_DXTZ00(DXID00,DXNRFL,DXNR00,YWRQ00,CZRQ00,CZSJ00,DH0000,JSR000,BRID00)
  select SQ_YJ_DXTZ00_DXID00.nextval,'科周报','<'||to_char(to_number(substr(a.TJRQ00,5,2)))||'.'||to_char(to_number(substr(a.TJRQ00,7,2)))||'-'||
         to_char(to_number(substr(a.TJJSRQ,5,2)))||'.'||to_char(to_number(substr(a.TJJSRQ,7,2)))||E.BMMC00||' 周报>'||
         '门诊:挂号量:'||to_char(a.MZGHL0)||',次均费用:'||to_char(a.MZCJFY)||',西成药占比：'||to_char(MZXCB0)||',中药使用率：'||to_char(MZZYL0)||
         ',非药物中医治疗率:'||to_char(MZFWL0)||';住院：收入人数:'||to_char(ZYSZRS)||',在科人数:'||
         to_char(ZYYXZC)||',出院人数:'||to_char(ZYYXCY)||',病床使用率：'||to_char(KSCWSYL)||',出院人均费用:'||to_char(CYRJFY)||',出院平均住院日:'||to_char(ZYPJTS)||
         ',手术:'||to_char(ZYSSL0)||',病危：'||to_char(BWBR00)||',西成药占比:'||to_char(ZYXCB0)||',中药使用率:'||to_char(ZYZYL0)||',非药物中医治疗率'||
         to_char(ZYFWL0)||';门诊住院合并西成药占:'||to_char(QYXCB0)||lv_GDNR00  NR0000,a.TJRQ00, to_char(sysdate,'yyyymmdd'),'08:00:00',d.DH0000,d.ZWXM00,0
          from BM_YYSFTJ_DXTZ a,(select b.BMBH00,b.DH0000,b.zwxm00 from BM_YGBM00 b ,BM_TYZD00 c where
         b.xzzw00=c.nbbh00 and ((c.mc0000 like '%科主任%') or (c.mc0000 like '%科副主任%')) and b.DH0000 is not null) d , BM_BMBM00 E
         where TJJLLX in (1) and id0000=2 and a.BMBH00 not in (-1,0)  and e.bmxz00 in ('0','1') and a.BMBH00 not in (99997,99996)
         and a.bmbh00 =d.bmbh00 and a.bmbh00=E.BMBH00 and not exists (select  1 from YJ_DXTZ00 where d.DH0000=DH0000 and DXNRFL='科周报' and YWRQ00=a.TJRQ00);

  insert into YJ_DXTZ00(DXID00,DXNRFL,DXNR00,YWRQ00,CZRQ00,CZSJ00,DH0000,JSR000,BRID00)
  select SQ_YJ_DXTZ00_DXID00.nextval,'科周报','<'||to_char(to_number(substr(a.TJRQ00,5,2)))||'.'||to_char(to_number(substr(a.TJRQ00,7,2)))||'-'||
         to_char(to_number(substr(a.TJJSRQ,5,2)))||'.'||to_char(to_number(substr(a.TJJSRQ,7,2)))||E.BMMC00||' 周报>'||
         '门诊:挂号量:'||to_char(a.MZGHL0)||',次均费用:'||to_char(a.MZCJFY)||',西成药占比：'||to_char(MZXCB0)||',中药使用率：'||to_char(MZZYL0)||
         ',非药物中医治疗率:'||to_char(MZFWL0)||';住院：收入人数:'||to_char(ZYSZRS)||',在科人数:'||
         to_char(ZYYXZC)||',出院人数:'||to_char(ZYYXCY)||',病床使用率：'||to_char(KSCWSYL)||',出院人均费用:'||to_char(CYRJFY)||',出院平均住院日:'||to_char(ZYPJTS)||
         ',手术:'||to_char(ZYSSL0)||',病危：'||to_char(BWBR00)||',西成药占比:'||to_char(ZYXCB0)||',中药使用率:'||to_char(ZYZYL0)||',非药物中医治疗率'||
         to_char(ZYFWL0)||';门诊住院合并西成药占:'||to_char(QYXCB0)||lv_GDNR00  NR0000,a.TJRQ00, to_char(sysdate,'yyyymmdd'),'08:00:00',d.DH0000,d.ZWXM00,0
          from BM_YYSFTJ_DXTZ a,(select 2214 BMBH00,b.DH0000,b.zwxm00 from BM_YGBM00 b ,BM_TYZD00 c where
         b.xzzw00=c.nbbh00 and ((c.mc0000 like '%科主任%') or (c.mc0000 like '%科副主任%')) and b.DH0000 is not null and b.ygbh00 in (2330)) d , BM_BMBM00 E
         where TJJLLX in (1) and id0000=2 and a.BMBH00 not in (-1,0)  and e.bmxz00 in ('0','1') and a.BMBH00 not in (99997,99996)
         and a.bmbh00 =d.bmbh00 and a.bmbh00=E.BMBH00 and not exists (select  1 from YJ_DXTZ00 where d.DH0000=DH0000 and DXNRFL='科周报' and YWRQ00=a.TJRQ00 and
           dh0000 not in (13950205901) and DXNR00  like '%康复科%' );
  commit;

exception
  when no_data_found then
    raise_application_error(-20001,substrb('没有找到数据！*'||sqlerrm||',',1,240));
  when others        then
    raise_application_error(-20002,substrb(nvl(sqlerrm, '原因不明出错')||'！*',1,240));
end;