--
-- Start of DDL script for SP_SF_KJPZSJTJ
-- Generated 25-二月-13  8:45:06 am
-- from FW2-SD_HOSPITAL:1

-- Procedure SP_SF_KJPZSJTJ

CREATE OR REPLACE
procedure  SP_SF_KJPZSJTJ
(
  as_TJYY00    IN    char,     --统计分院
  as_KSRQ00    IN    CHAR,       --开始日期
  as_JSRQ00    IN    CHAR,       --结束日期
  as_mzzybz    in    char        --门诊住院标志
)
as
-- MODIFICATION HISTORY
-- Person      Date        Comments
--  liuj     2011.03.15     create

  ls_tjyy00    sf_kjpzwh.yyid00%TYPE;
 -- ls_pzxmid    sf_kjpzwh.PZXMID%TYPE;
 -- ls_yyid00    sf_kjpzwh.YYID00%TYPE;
  SQLStringSL  varchar2(1000);
  ls_sqls       sf_kjpzwh.sqlsl0%type;
  Ecustom	          exception;  --错误变量
  VErrMsg             varchar2(255);  --变量
  cursor cur_SF_KJPZ00_TEMP is
  select pzxmid from sf_kjpzwh where yyid00= as_tjyy00 and mzzybz=as_mzzybz order by xh0000;
begin
  delete  from SF_KJPZ00_TEMP;
  commit;
  if as_mzzybz='0' then
   begin
   --按发票统计
    insert into sf_kjpz00_temp (pzzy00,kjkm00,jfje00,dfje00,xh0000,bmbh00)
      select C.mzzy00,c.mzkm00,0,nvl(sum(A.HJJE00),0),c.fpxmid||'99'||k.kjbmbh,k.kjbmbh
         from  SF_FYMX00 A,BM_YYSFXM B,BM_KJKMDY C,SF_BRFY00 H,bm_bmbmdy K
         where  A.XMBH00=B.SFXMID and B.HSXMID=C.FPXMID and A.DJH000=H.DJH000 and H.DJH000 <>0
         and H.CZRQ00 >= as_KSRQ00 and H.CZRQ00 <= as_JSRQ00   and K.bmbh00=a.kdks00 and c.tjlb00=1
         and (A.KDKS00 in (select bmbh00 from bm_bmbm00 a,xt_yyxx00 b where a.yyid00=b.yyid00 and b.yyid00=as_TJYY00))
         group by C.mzzy00,c.mzkm00,c.fpxmid,k.kjbmbh;
    end;
    begin
    --医保应收
      insert into sf_kjpz00_temp (pzzy00,kjkm00,jfje00,dfje00,xh0000,bmbh00)
        select  d.mzzy00,d.mzkm00,nvl(sum(nvl(a.jfje00,0)),0) jfje00,0,d.fpxmid||'999',0
	     from   sf_brjfb0 a,sf_jzb000 b,ic_ybbrlb c,bm_kjkmdy d
	     where  a.brid00=b.brid00 and a.jzdh00=b.jzdh00 and b.fbbh00=c.fbbh00 and b.yblb00=c.yblb00  and d.yyid00=as_TJYY00 and d.tjlb00=2
         and a.jfrq00>=as_KSRQ00 and a.zffs00 in(3,4)  and a.jfrq00<=as_JSRQ00 and c.yblb00=d.yblb00 and c.ybzxlb=d.ybzxlb and c.fbbh00=d.fbbh00 and
         ( B.CZYKS0 in (select bmbh00 from bm_bmbm00 a,xt_yyxx00 b where a.yyid00=b.yyid00 and b.yyid00=as_TJYY00))
	    group  by d.mzzy00,d.mzkm00,d.fpxmid
	    having sum(nvl(a.jfje00,0))<>0;
    end;
   end if;
   if as_mzzybz='1' then
    begin
    --按发票统计
      insert into sf_kjpz00_temp (pzzy00,kjkm00,jfje00,dfje00,xh0000,bmbh00)
       select  C.zyzy00,C.zykm00, 0,sum(A.HJJE00) HJJE00 ,C.fpxmid||'99'||i.kjbmbh,I.kjbmbh
         from  ZY_FYMX00 A,BM_YYSFXM B,BM_KJKMDY C,ZY_BRFY00 H,BM_BMBMDY I
         where  A.XMBH00  = B.SFXMID  and  B.HSXMID  = C.FPXMID  and  A.DJH000 = H.DJH000
         and  A.KDKS00  = I.BMBH00   and c.tjlb00=1
         and (A.KDKS00 in (select bmbh00 from bm_bmbm00 a,xt_yyxx00 b where a.yyid00=b.yyid00 and b.yyid00=as_TJYY00))
         and  H.CZRQ00  >=as_KSRQ00  and  H.CZRQ00  <= as_JSRQ00
         group  by  C.zyzy00,C.zykm00,C.fpxmid,I.kjbmbh ;
    end;
    begin
    --医保应收
      insert into sf_kjpz00_temp (pzzy00,kjkm00,jfje00,dfje00,xh0000,bmbh00)
        select f.zyzy00,f.zykm00,sum(D.BRZJE0-D.ZFJE00) YBXJ00,0,f.fpxmid||'999',0
          from ic_ybbrlb C,zy_jzb000 D,bm_kjkmdy F
          where D.yblb00 = C.yblb00  and  D.FBBH00 = C.FBBH00  and  C.FBBH00<>1
          and f.fbbh00=c.fbbh00 and f.yblb00=c.yblb00 and f.ybzxlb=c.ybzxlb and f.yyid00=as_TJYY00
          and f.tjlb00=2 and ( D.CZYKS0 in (select bmbh00 from bm_bmbm00 a,xt_yyxx00 b where a.yyid00=b.yyid00 and b.yyid00=as_TJYY00))
          and  D.JZRQ00 >= as_KSRQ00     and  D.JZRQ00 <= as_JSRQ00
          group by f.zyzy00,f.zykm00,f.fpxmid having sum(D.BRZJE0)<>0;
    end;
   end if;
  for v_pzxmid in cur_SF_KJPZ00_TEMP loop
  begin
    ls_tjyy00 := as_TJYY00;
    select SQLSL0 into SQLStringSL from sf_kjpzwh where pzxmid=v_pzxmid.pzxmid;
    ls_sqls :=sqlstringSL;
     execute immediate ls_sqls   using  as_KSRQ00,as_JSRQ00,ls_tjyy00;
    commit;
  end;
  end loop;
end;
/

-- End of DDL script for SP_SF_KJPZSJTJ