CREATE OR REPLACE PROCEDURE SP_YB_MZCJFY_BB0000
(
        as_KSRQ00    in    char,       --开始日期
        as_JSRQ00    in    char,       --结束日期
        as_YBDZX0    in    char,       --医保大中心
        as_YBFZX0    in    char,       --医保分中心
        as_TJLB00    in    char,       --统计类别  1:门诊次均费用 2:门诊医生次均费用 3:住院次均费用 4:住院各医保大中心次均费用
        as_DZXQX0    in    char default '无', --大中心权限条件
        as_KSQX00    in    char default '无', --科室权限条件
        ad_ID0000    out   number,     --统计报表对应的ID
        as_YHMSG0    out   varchar,    --存储过程提示的错误信息
        as_SYSMSG    out   varchar     --系统提示的错误信息
)
as
-- MODIFICATION HISTORY
-- Person      Date        Comments
-- jinfl      2013.05.14     create;  YBFYJK-20130423-001
-- liuj       2013.06.20    增加传入权限控制 for YBFYJK-20130524-001
-- liuj       2013.06.26    修改传入默认值为空问题 for YBFYJK-20130619-001
-- liuj       2013.06.27    分中心查询问题 for YBFYJK-20130619-002
-- jinfl      2013.10.28    查询挂号号时以特殊病种分类,并且增加SF_JZMXXM表关联  YBFYJK-20131028-001
-- liuj       2103.11.20    增加统计类别4:住院各医保大中心次均费用 for YBFYJK-20131024-002
-- liuj       2013.12.11    整理归档 for YBFYJK-20131206-002
-- daihq      2014.09.17    增加SFXM05:非医保药品金额 SFXM10:费用占比 SMXM11:人次占比  SMXM12:非医保药品占比 for YBFYJK-20140825-001
-----------------------------------------------------------
  ls_dzxqx0 YB_DZXQX0.dzxqx0%type;
  ls_ksqx00 YB_DZXQX0.ksqx00%type;
  lv_ZFY000 BM_YYSFTJ.SFXM01%type;
  lv_ZRC000 BM_YYSFTJ.SFXM01%type;
  is_error varchar(100);
begin
   ls_dzxqx0:=as_DZXQX0;
   ls_ksqx00:=as_KSQX00;
   if ls_dzxqx0 is null then
     ls_dzxqx0:='无';
   end if;
   if ls_ksqx00 is null then
     ls_ksqx00:='无';
   end if;
   select  SQ_BM_YYSFTJ_ID0000.nextval  into  ad_ID0000  from  dual;
   if as_TJLB00='1' then
    insert into BM_YYSFTJ(ID0000,BMBH00,BMMC00,XMHJ00,SFXM01,SFXM02,SFXM03,SFXM04,SFXM05)
     select ad_ID0000,nvl(PXXH00,0),BMMC00,sum(GHCS00) GHCS00,sum(HJJE00) HJJE00,sum(YPJE00) YPJE00,
            sum(JCJE00) JCJE00,sum(HCJE00) HCJE00,sum(YPZFJE) YPZFJE from
     (select c.PXXH00,c.BMBH00,c.BMMC00,count(distinct a.mzid00) GHCS00,a.tsbzbh ,sum(d.HJJE00*j.BZ0000) HJJE00,
     sum(decode(sign(h.MZFPID-4),-1,d.HJJE00*j.BZ0000,0)) YPJE00,sum(decode(instr(i.XMMC00,'检查'),'0',0,d.HJJE00*j.BZ0000)) JCJE00,
     sum(decode(JSXM00,'2',d.HJJE00*j.BZ0000,0)) HCJE00,sum(decode(sign(h.MZFPID-4),-1,d.ZFJE00*j.BZ0000,0)) YPZFJE
      from SF_JZB000 a,SF_BRXXB0 b,BM_BMBM00 c,SF_FYMX00 d,SF_BRFY00 e ,BM_YYSFXM h,BM_FPXM00 i,SF_JZMXXM j
     where a.JZDH00=j.JZDH00 and j.MXID00=d.MXID00 and a.MZID00=b.GHID00 and b.GHKS00=c.BMBH00 and H.MZFPID=I.FPXMID and d.DJH000=e.DJH000
        and d.XMBH00=h.SFXMID --and a.FBBH00='3'
        and a.JZRQ00 between as_KSRQ00 and as_JSRQ00 and (a.YBLB00,a.FBBH00) in (select YBLB00,FBBH00 from IC_YBBRLB where 1=1 and (YBLB00=as_YBFZX0 or as_YBFZX0='-2') and (
      (YBZXLB=as_YBDZX0 or as_YBDZX0='-1' or as_YBDZX0='-2') or (YBZXLB='1' and SFXNH0='3' and as_YBDZX0='-1')) and ((ls_dzxqx0='无') or (instr(','||ls_dzxqx0||',',','||YBZXLB||',')>0)))
      and ((ls_ksqx00='无') or (instr(','||ls_ksqx00||',',','||b.GHKS00||',')>0))
     group by c.BH0000,c.BMMC00,c.BMBH00,c.PXXH00 ,a.tsbzbh
     ) group by BMBH00,BMMC00,PXXH00;
   end if;

   if as_TJLB00='2' then
    insert into BM_YYSFTJ(ID0000,BMBH00,BMMC00,SJYSXM,XMHJ00,SFXM01,SFXM02,SFXM03,SFXM04,SFXM05)
     select ad_ID0000,nvl(PXXH00,0),BMMC00,JZYSXM,sum(GHCS00) GHCS00,sum(HJJE00) HJJE00,sum(YPJE00) YPJE00,
            sum(JCJE00) JCJE00,sum(HCJE00) HCJE00,sum(YPZFJE) YPZFJE from
     (select c.PXXH00,c.BMBH00,c.BMMC00,b.JZYS00,b.JZYSXM,count(distinct b.GHID00) GHCS00,sum(d.HJJE00*j.BZ0000) HJJE00,a.tsbzbh ,
     sum(decode(sign(h.MZFPID-4),-1,d.HJJE00*j.BZ0000,0)) YPJE00,sum(decode(instr(i.XMMC00,'检查'),'0',0,d.HJJE00*j.BZ0000)) JCJE00,
     sum(decode(JSXM00,'2',d.HJJE00*j.BZ0000,0)) HCJE00,sum(decode(sign(h.MZFPID-4),-1,d.ZFJE00*j.BZ0000,0)) YPZFJE
      from SF_JZB000 a,SF_BRXXB0 b,BM_BMBM00 c,SF_FYMX00 d,SF_BRFY00 e ,BM_YYSFXM h,BM_FPXM00 i ,SF_JZMXXM j
     where a.MZID00=b.GHID00 and b.GHKS00=c.BMBH00 and H.MZFPID=I.FPXMID and d.DJH000=e.DJH000 and a.JZDH00=j.JZDH00 and j.MXID00=d.MXID00  and d.XMBH00=h.SFXMID --and a.FBBH00='3'
     and a.JZRQ00 between as_KSRQ00 and as_JSRQ00 and (a.YBLB00,a.FBBH00) in (select YBLB00,FBBH00 from IC_YBBRLB where 1=1 and (YBLB00=as_YBFZX0 or as_YBFZX0='-2') and (
      (YBZXLB=as_YBDZX0 or as_YBDZX0='-1' or as_YBDZX0='-2') or (YBZXLB='1' and SFXNH0='3' and as_YBDZX0='-1')) and ((ls_dzxqx0='无') or (instr(','||ls_dzxqx0||',',','||YBZXLB||',')>0)) )
     and ((ls_ksqx00='无') or (instr(','||ls_ksqx00||',',','||b.GHKS00||',')>0))
     group by c.BH0000,c.BMMC00,c.BMBH00,c.PXXH00,b.JZYS00,b.JZYSXM,a.tsbzbh
     ) group by BMBH00,BMMC00,JZYS00,JZYSXM,PXXH00;
   end if;

   if as_TJLB00='3' then
    insert into BM_YYSFTJ(ID0000,BMBH00,BMMC00,XMHJ00,SFXM01,SFXM02,SFXM03,SFXM04,SFXM05)
     select ad_ID0000,nvl(PXXH00,0),BMMC00,sum(ZYCS00) ZYCS00,sum(HJJE00) HJJE00,sum(YPJE00) YPJE00,
            sum(JCJE00) JCJE00,sum(HCJE00) HCJE00,sum(YPZFJE) YPZFJE from
     (select c.PXXH00,c.BMBH00,c.BMMC00,count(distinct b.ZYID00)ZYCS00,sum(d.HJJE00*j.BZ0000)HJJE00,
     sum(decode(sign(h.MZFPID-4),-1,d.HJJE00*j.BZ0000,0)) YPJE00,sum(decode(instr(i.XMMC00,'检查'),'0',0,d.HJJE00*j.BZ0000)) JCJE00,
     sum(decode(JSXM00,'2',d.HJJE00*j.BZ0000,0)) HCJE00,sum(decode(sign(h.MZFPID-4),-1,d.ZFJE00*j.BZ0000,0)) YPZFJE
      from ZY_JZB000 a,ZY_BRXXB0 b,BM_BMBM00 c,ZY_FYMX00 d,ZY_BRFY00 e ,BM_YYSFXM h,BM_FPXM00 i,ZY_JZMXXM j
     where a.ZYID00=b.ZYID00 and b.DQKS00=c.BMBH00 and H.ZYFPID=I.FPXMID and d.DJH000=e.DJH000 and e.JZDH00=a.JZDH00 and d.XMBH00=h.SFXMID and a.JZDH00=j.JZDH00 and j.MXID00=d.MXID00
     and b.CYRQ00 between as_KSRQ00 and as_JSRQ00 and (a.YBLB00,a.FBBH00) in (select YBLB00,FBBH00 from IC_YBBRLB where 1=1 and (YBLB00=as_YBFZX0 or as_YBFZX0='-2') and (
       (YBZXLB=as_YBDZX0 or as_YBDZX0='-1' or as_YBDZX0='-2') or (YBZXLB='1' and SFXNH0='3' and as_YBDZX0='-1')) and ((ls_dzxqx0='无') or (instr(','||ls_dzxqx0||',',','||YBZXLB||',')>0)) )
     and ((ls_ksqx00='无') or (instr(','||ls_ksqx00||',',','||b.DQKS00||',')>0))
     group by c.BH0000,c.BMMC00,c.BMBH00,c.PXXH00
     ) group by BMBH00,BMMC00,PXXH00;
   end if;

   if as_TJLB00='4' then
   is_error :='@1@';
    insert into BM_YYSFTJ(ID0000,BMBH00,SJYSXM,BMMC00,XMHJ00,SFXM01,SFXM02,SFXM03,SFXM04,SFXM05)
     select ad_ID0000,99999,ybzxlb,BMMC00,sum(ZYCS00) ZYCS00,sum(HJJE00) HJJE00,sum(YPJE00) YPJE00,
            sum(JCJE00) JCJE00,sum(HCJE00) HCJE00,sum(YPZFJE) YPZFJE from
     (select b.ybzxlb,nvl((select ybmc00 from ic_ybbrlb where ybzxlb=yblb00 and ybzxlb=b.ybzxlb and rownum=1),'其他') BMMC00,count(distinct b.ZYID00)ZYCS00,sum(d.HJJE00*j.BZ0000)HJJE00,
     sum(decode(sign(h.MZFPID-4),-1,d.HJJE00*j.BZ0000,0)) YPJE00,sum(decode(instr(i.XMMC00,'检查'),'0',0,d.HJJE00*j.BZ0000)) JCJE00,
     sum(decode(JSXM00,'2',d.HJJE00*j.BZ0000,0)) HCJE00,sum(decode(sign(h.MZFPID-4),-1,d.ZFJE00*j.BZ0000,0)) YPZFJE
      from ZY_JZB000 a,ZY_BRXXB0 b,ZY_FYMX00 d,ZY_BRFY00 e ,BM_YYSFXM h,BM_FPXM00 i,ZY_JZMXXM j
     where a.ZYID00=b.ZYID00 and H.ZYFPID=I.FPXMID and d.DJH000=e.DJH000 and e.JZDH00=a.JZDH00 and d.XMBH00=h.SFXMID and a.JZDH00=j.JZDH00 and j.MXID00=d.MXID00
     and b.CYRQ00 between as_KSRQ00 and as_JSRQ00 and (a.YBLB00,a.FBBH00) in (select YBLB00,FBBH00 from IC_YBBRLB where 1=1 and (YBLB00=as_YBFZX0 or as_YBFZX0='-2') and (
       (YBZXLB=as_YBDZX0 or as_YBDZX0='-1' or as_YBDZX0='-2') or (YBZXLB='1' and SFXNH0='3' and as_YBDZX0='-1')) and ((ls_dzxqx0='无') or (instr(','||ls_dzxqx0||',',','||YBZXLB||',')>0)) )
     and ((ls_ksqx00='无') or (instr(','||ls_ksqx00||',',','||b.DQKS00||',')>0))
     group by b.ybzxlb
     ) group by ybzxlb,BMMC00;
      is_error :='@2@';
   end if;
 is_error :='@3@';
-- 本次修改99999 改 999999
   insert into BM_YYSFTJ(ID0000,BMBH00,BMMC00,XMHJ00,SFXM01,SFXM02,SFXM03,SFXM04,SJYSXM,SFXM05)
     select ad_ID0000,999999,'合计',sum(XMHJ00),sum(SFXM01),sum(SFXM02),sum(SFXM03),sum(SFXM04),' ',sum(SFXM05)
     from BM_YYSFTJ where ID0000=ad_ID0000;
      is_error :='@3.1@';
-- 本次修改99999 改 999999
   select SFXM01,XMHJ00 into lv_ZFY000,lv_ZRC000 from BM_YYSFTJ where ID0000=ad_ID0000 and BMBH00=999999;
   --XMHJ00:挂号人数 SFXM01:总金额 SFXM02: 药品费  SFXM03:检查费 SFXM04:耗材费
   --SFXM06:药品比 SFXM07:检查费比 SFXM08:耗材费比 SFXM09:次均费用 SFXM05:非医保药品金额
   --SFXM10:费用占比 SFXM11:人次占比  SFXM12:非医保药品占比
    is_error :='@3.2@';
   update BM_YYSFTJ set SFXM06=SFXM02/decode(SFXM01,'0','1',SFXM01),SFXM07=SFXM03/decode(SFXM01,'0','1',SFXM01),
   SFXM08=SFXM04/decode(SFXM01,'0','1',SFXM01),SFXM09=SFXM01/decode(XMHJ00,'0','1',XMHJ00),
   SFXM10 = SFXM01/decode(lv_ZFY000,'0','1',lv_ZFY000),SFXM11 = XMHJ00/decode(lv_ZRC000,'0','1',lv_ZRC000),
   SFXM12 = SFXM05/decode(SFXM02,'0','1',SFXM02)
   where ID0000=ad_ID0000;
 is_error :='@4@';
   commit;
---本次修改  SP_YB_ZYXNH0_BB0000 改SP_YB_MZCJFY_BB0000  
exception
      when others then
                as_YHMSG0:=is_error||'出错原因不详,请记录此信息并和系统管理员联系！';
                as_SYSMSG:=substr(sqlerrm||'执行存储过程错误:SP_YB_MZCJFY_BB0000('||as_KSRQ00||','||as_JSRQ00||',ad_ID0000,as_YHMSG0,as_SYSMSG',1,150);
		rollback;
end;
/