prompt 特殊病种统计(按发票项目)  SP_SF_TSBZFP_HZ0000
create or replace procedure SP_SF_TSBZFP_HZ0000
(
     as_KSRQ00    in   char,
     as_KSSJ00    in   char,
     as_JSRQ00    in   char,
     as_JSSJ00    in   char,
     as_YYID00    in   varchar2,
     as_YBLX00    in   char default '0',    --0全部, 1普通医保, 2城镇医保
     as_YBMC00    in   varchar2,
     ad_ID0000    out  number,
     as_YHMSG0    out  varchar,
     as_SYSMSG    out  varchar,
     as_TSBZBH    in   varchar2 default '',   --特殊病种编号
     as_BRXM00    in   varchar2 default '',   --病人姓名
     as_BRBLH0    in   varchar2 default ''    --病人病历号
)
-- MODIFICATION HISTORY
-- Person      Date        Comments
-- linzetao    2020.09.02  create by MZSF9-20200731-001
-- linzetao    2021.04.30  增加入参as_TSBZBH,as_BRXM00,as_BRBLH0   MZSF9-20210414-004
-- chenHeyi    2022.08.26  城镇医保条件:BM_YBBRLB.CBLX00=2(居民医保)也视为城镇医保  MZSF9-20220824-002
-- chenHeyi    2023.03.30  修正游标条件and e.YBLB00 = h.YBZXLB，改为 and d.YBZXLB = h.YBZXLB 
as
     LS_COUNT0  number(5);
     LS_TSBZBH  SF_JZB000.TSBZBH%type;
     LS_TSBZMC  BM_TSBZB0.MC0000%type;
     LS_YBBRLB  SF_JZB000.YBBRLB%type;
     LS_BH0000  varchar2(10);
     LS_HJJE00  SF_FYMX00.HJJE00%type;
     LS_ZFJE00  SF_JZB000.zfje00%type;
     LS_GRZHZF  SF_JZB000.GRZHZF%type;
     LS_TCJJZF  SF_JZB000.TCJJZF%type;
        
   cursor  CUR_SF_FPXM00  is  
     select YBBRLB,TSBZMC,BH0000,sum(HJJE00) HJJE00 from (
        select decode(nvl(h.CBLX00,' '),'2','C',e.YBBRLB) as YBBRLB,e.TSBZBH,(select MC0000 from BM_TSBZB0 where BH0000=e.TSBZBH and YBZXLB=d.YBZXLB and rownum=1) TSBZMC,b.BH0000,(a.HJJE00*f.BZ0000) HJJE00
          from SF_FYMX00 a,BM_FPXM00 b,BM_BRXXB0 c,IC_YBBRLB d,SF_JZB000 e,SF_JZMXXM f,BM_BMBM00 g, BM_YBBRLB h
         where a.MZFPID = b.FPXMID  
           and a.MXID00 = f.MXID00
           and e.JZDH00 = f.JZDH00
           and a.BRID00 = c.BRID00
           and c.YBLB00 = d.YBLB00
           and c.FBBH00 = d.FBBH00
           and d.YBZXLB = h.YBZXLB and e.FBBH00=h.FBBH00 and e.YBBRLB=h.YBBRLB
           and e.CZYKS0 = g.BMBH00(+)
           and e.JZRQ00 >= as_KSRQ00
           and e.JZRQ00 <= as_JSRQ00
           and e.JZRQ00||e.JZSJ00 >= as_KSRQ00||as_KSSJ00
           and e.JZRQ00||e.JZSJ00 <= as_JSRQ00||as_JSSJ00
           and (as_YYID00 = '0' or as_YYID00 = g.YYID00)
           and (as_YBMC00 is null or as_YBMC00 = d.YBMC00)
           and (as_YBLX00 = '0' or (as_YBLX00 = '1' and e.YBBRLB<>'C' and nvl(h.CBLX00,' ')<>'2') or (as_YBLX00 = '2' and (e.YBBRLB = 'C' or nvl(h.CBLX00,' ')='2')))
           and e.tsbzbh is not null
           and (as_TSBZBH is null or e.tsbzbh=as_TSBZBH)
           and (as_BRXM00 is null or c.BRXM00=as_BRXM00)
           and (as_BRBLH0 is null or c.BRBLH0=as_BRBLH0)
           and e.FBBH00='3')
      group by YBBRLB,TSBZMC,BH0000
      having sum(HJJE00)<>0;
      
   cursor CUR_SF_FPXM00_JZB000 is
     select YBBRLB,TSBZMC,sum(ZFJE00) ZFJE00,sum(GRZHZF) GRZHZF,sum(TCJJZF) TCJJZF from (
        select decode(nvl(h.CBLX00,' '),'2','C',e.YBBRLB) as YBBRLB,e.TSBZBH,(select MC0000 from BM_TSBZB0 where BH0000=e.TSBZBH and YBZXLB=d.YBZXLB and rownum=1) TSBZMC,e.ZFJE00,e.GRZHZF,e.TCJJZF
          from BM_BRXXB0 c,IC_YBBRLB d,SF_JZB000 e,BM_BMBM00 g, BM_YBBRLB h
         where e.BRID00 = c.BRID00
           and c.YBLB00 = d.YBLB00
           and c.FBBH00 = d.FBBH00
           and d.YBZXLB = h.YBZXLB and e.FBBH00=h.FBBH00 and e.YBBRLB=h.YBBRLB
           and e.CZYKS0 = g.BMBH00(+)
           and e.JZRQ00 >= as_KSRQ00
           and e.JZRQ00 <= as_JSRQ00
           and e.JZRQ00||e.JZSJ00 >= as_KSRQ00||as_KSSJ00
           and e.JZRQ00||e.JZSJ00 <= as_JSRQ00||as_JSSJ00
           and (as_YYID00 = '0' or as_YYID00 = g.YYID00)
           and (as_YBMC00 is null or as_YBMC00 = d.YBMC00)
           and (as_YBLX00 = '0' or (as_YBLX00 = '1' and e.YBBRLB<>'C' and nvl(h.CBLX00,' ')<>'2') or (as_YBLX00 = '2' and (e.YBBRLB = 'C' or nvl(h.CBLX00,' ')='2')))
           and e.tsbzbh is not null
           and (as_TSBZBH is null or e.tsbzbh=as_TSBZBH)
           and (as_BRXM00 is null or c.BRXM00=as_BRXM00)
           and (as_BRBLH0 is null or c.BRBLH0=as_BRBLH0)
           and e.FBBH00='3')                       
      group by YBBRLB,TSBZMC
      having sum(ZFJE00)<>0 or sum(GRZHZF)<>0 or sum(TCJJZF)<>0;
 
begin
    LS_COUNT0:=0;
    LS_ZFJE00:=0;
    LS_GRZHZF:=0;
    LS_TCJJZF:=0;
    select  SQ_BM_GHKSTJ_ID0000.nextval into ad_ID0000 from dual;
    open CUR_SF_FPXM00;
    loop
      fetch CUR_SF_FPXM00 into LS_YBBRLB,LS_TSBZMC,LS_BH0000,ls_HJJE00;
      exit when CUR_SF_FPXM00%notfound;
      select count(1) into LS_COUNT0 from BM_GHKSTJ where ID0000=ad_ID0000 and GHRQ00=LS_TSBZMC and BEIZH1=LS_YBBRLB;
      if LS_COUNT0>0 then
        sp_execute_sql('update BM_GHKSTJ set GHKS'||ls_BH0000||'= nvl(GHKS'||ls_BH0000||',0)+'||to_char(ls_HJJE00)||',XMHJ00=nvl(XMHJ00,0)+'||to_char(ls_HJJE00)||' where BEIZH1='''||LS_YBBRLB||''' and GHRQ00 = '''||LS_TSBZMC||''' and ID0000='||to_char(ad_ID0000));
      else
        sp_execute_sql('insert into BM_GHKSTJ(BEIZH1,GHRQ00,GHKS'||ls_BH0000||',ID0000,XMHJ00,DYID00) values('''||LS_YBBRLB||''','''||LS_TSBZMC||''','||to_char(ls_HJJE00)||','||to_char(AD_ID0000)||','||to_char(ls_HJJE00)|| ',0'||')');
      end if;
    end loop;
    close CUR_SF_FPXM00 ; 
    
    open CUR_SF_FPXM00_JZB000;
    loop
      fetch CUR_SF_FPXM00_JZB000 into LS_YBBRLB,LS_TSBZMC,LS_ZFJE00,LS_GRZHZF,LS_TCJJZF;
      exit when CUR_SF_FPXM00_JZB000%notfound;
      select count(1) into LS_COUNT0 from BM_GHKSTJ where ID0000=ad_ID0000 and GHRQ00=LS_TSBZMC and BEIZH1=LS_YBBRLB;
      if LS_COUNT0>0 then
        sp_execute_sql('update BM_GHKSTJ set GHKS80=nvl(GHKS80,0)+'||to_char(LS_ZFJE00)||',GHKS81=NVL(GHKS81,0)+'||to_char(LS_GRZHZF)||',GHKS82=NVL(GHKS82,0)+'||to_char(LS_TCJJZF)||' where BEIZH1='''||LS_YBBRLB||''' and GHRQ00 = '''||LS_TSBZMC||''' and ID0000='||to_char(ad_ID0000));
      else
        sp_execute_sql('insert into BM_GHKSTJ(BEIZH1,GHRQ00,ID0000,XMHJ00,GHKS80,GHKS81,GHKS82,DYID00) values('''||LS_YBBRLB||''','''||LS_TSBZMC||''','||to_char(AD_ID0000)||','||to_char(LS_ZFJE00)||','||to_char(LS_GRZHZF)||','||to_char(LS_TCJJZF)|| ',0'||')');
      end if;
    end loop;
    close CUR_SF_FPXM00_JZB000;
                      
exception
      when others then
                as_YHMSG0:='系统出错，请查看详细信息。如有不明，请与管理员联系！'||SQLERRM;
                as_SYSMSG:=substr(SQLERRM||'执行存储过程错误:SP_SF_TSBZFP_HZ0000('||as_KSRQ00||','||as_JSRQ00||'ad_ID0000,as_YHMSG0,as_SYSMSG',1,150);
                rollback;
end;
/
show error;

--%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%