CREATE OR REPLACE PROCEDURE SP_IC_ICYE00
(
  Pksrq00 in char,  --开始日期
  Pjsrq00 in char,  --结束日期
  Pcommit in char   --是否提交 Y是N否
)
-- MODIFICATION HISTORY
-- Person      Date        Comments
-- zhanghr   2012.08.18     整理
-- zhangwz   2012.09.16     增加对回收卡的处理
-- jlg       2018.03.08     增加当日员工的合计记录 YGBH00=-2    JCBB9-20180306-001
as
  Vksrq00  IC_ICYE00.CZRQ00%type;
  cursor C_ICYW00 is
    select FLAG00,CZRQ00,YGBH00,sum(SL0000)SL0000  from
       (
       --建卡-1,回收卡 1
       select decode(a.YWCZ00,'5',-1,'E',-1,'A',-1,1) FLAG00,a.YGBH00,a.CZRQ00,count(*) SL0000
              from IC_YWLS00 a,IC_YHXX00 b
              where a.ICKH00=b.ICKH00 and b.KLDH00<>'YB-SBK'
                and a.CZRQ00 between Vksrq00 and Pjsrq00
                and a.YWCZ00 in ('1','2','C','5','A','E','F')
              group by decode(a.YWCZ00,'5',-1,'E',-1,'A',-1,1),a.YGBH00,a.CZRQ00
             --YWCZ: 0入库,1售病人卡,2售员工卡,3报停,4报废,5退卡,6取消报停,7重制空白卡,
                  -- 8医保卡登记,9外院卡登记 A作废卡回收 B厦门健康卡登记 C换卡(收卡费)''D''换卡(不收卡费)';
       union all
       --领用和退回
       select decode(CZLX00,'0',2,'1',-2,'2',3,'4',-3,'3',-4) FLAG00,ICSYR0,ICFFRQ,ICKLYS
             from BM_ICLYQK where ICFFRQ between Vksrq00 and Pjsrq00
             --CZLX: 0收费员领用1收费员上缴2仓库入库3仓库报废4仓库退货
       )
       group by CZRQ00,YGBH00,FLAG00 order by CZRQ00,YGBH00;

  cursor C_YWRQ00 is
    select distinct CZRQ00 from IC_ICYE00 where CZRQ00 between Vksrq00 and Pjsrq00 order by CZRQ00;

begin
  delete from IC_ICYE00 where CZRQ00 between Pksrq00 and Pjsrq00;
  select to_char(to_date(nvl(max(CZRQ00),'20101231'),'yyyymmdd')+1,'yyyymmdd') into Vksrq00 from IC_ICYE00;

  for yw in C_ICYW00 loop
    -- 1建卡 -1回收 2领用 -2上缴 3入库 -3退货 -4报废
    if yw.FLAG00=1 then
       update IC_ICYE00 set BQFS00=BQFS00+yw.SL0000 where CZRQ00=yw.CZRQ00 and YGBH00=yw.YGBH00;
       if sql%notfound then
          insert into IC_ICYE00(YGBH00,CZRQ00,SQYE00,BQFS00,BQTC00,BQLICK,BQSJSL,BQBFSL,BQYE00,BQSJYE,SQSJYE)
                 values (yw.YGBH00,yw.CZRQ00,0,yw.SL0000,0,0,0,0,0,0,0);
       end if;
    elsif yw.FLAG00=-1 then
       update IC_ICYE00 set BQTC00=BQTC00+yw.SL0000 where CZRQ00=yw.CZRQ00 and YGBH00=yw.YGBH00;
       if sql%notfound then
          insert into IC_ICYE00(YGBH00,CZRQ00,SQYE00,BQFS00,BQTC00,BQLICK,BQSJSL,BQBFSL,BQYE00,BQSJYE,SQSJYE)
                 values (yw.YGBH00,yw.CZRQ00,0,0,yw.SL0000,0,0,0,0,0,0);
       end if;
    elsif yw.FLAG00=2 then
       update IC_ICYE00 set BQLICK=BQLICK+yw.SL0000 where CZRQ00=yw.CZRQ00 and YGBH00=yw.YGBH00;
       if sql%notfound then
          insert into IC_ICYE00(YGBH00,CZRQ00,SQYE00,BQFS00,BQTC00,BQLICK,BQSJSL,BQBFSL,BQYE00,BQSJYE,SQSJYE)
                 values (yw.YGBH00,yw.CZRQ00,0,0,0,yw.SL0000,0,0,0,0,0);
       end if;
       --出仓
       update IC_ICYE00 set BQFS00=BQFS00+yw.SL0000 where CZRQ00=yw.CZRQ00 and YGBH00=-1;
       if sql%notfound then
          insert into IC_ICYE00(YGBH00,CZRQ00,SQYE00,BQFS00,BQTC00,BQLICK,BQSJSL,BQBFSL,BQYE00,BQSJYE,SQSJYE)
                 values (-1,yw.CZRQ00,0,yw.SL0000,0,0,0,0,0,0,0);
       end if;

    elsif yw.FLAG00=-2 then
       update IC_ICYE00 set BQSJSL=BQSJSL+yw.SL0000 where CZRQ00=yw.CZRQ00 and YGBH00=yw.YGBH00;
       if sql%notfound then
          insert into IC_ICYE00(YGBH00,CZRQ00,SQYE00,BQFS00,BQTC00,BQLICK,BQSJSL,BQBFSL,BQYE00,BQSJYE,SQSJYE)
                 values (yw.YGBH00,yw.CZRQ00,0,0,0,0,yw.SL0000,0,0,0,0);
       end if;
       update IC_ICYE00 set BQTC00=BQTC00+yw.SL0000 where CZRQ00=yw.CZRQ00 and YGBH00=-1;
       if sql%notfound then
          insert into IC_ICYE00(YGBH00,CZRQ00,SQYE00,BQFS00,BQTC00,BQLICK,BQSJSL,BQBFSL,BQYE00,BQSJYE,SQSJYE)
                 values (-1,yw.CZRQ00,0,0,yw.SL0000,0,0,0,0,0,0);
       end if;

    elsif yw.FLAG00=3 then
       update IC_ICYE00 set BQLICK=BQLICK+yw.SL0000 where CZRQ00=yw.CZRQ00 and YGBH00=yw.YGBH00;
       if sql%notfound then
          insert into IC_ICYE00(YGBH00,CZRQ00,SQYE00,BQFS00,BQTC00,BQLICK,BQSJSL,BQBFSL,BQYE00,BQSJYE,SQSJYE)
                 values (yw.YGBH00,yw.CZRQ00,0,0,0,yw.SL0000,0,0,0,0,0);
       end if;
    elsif yw.FLAG00=-3 then
       update IC_ICYE00 set BQSJSL=BQSJSL+yw.SL0000 where CZRQ00=yw.CZRQ00 and YGBH00=yw.YGBH00;
       if sql%notfound then
          insert into IC_ICYE00(YGBH00,CZRQ00,SQYE00,BQFS00,BQTC00,BQLICK,BQSJSL,BQBFSL,BQYE00,BQSJYE,SQSJYE)
                 values (yw.YGBH00,yw.CZRQ00,0,0,0,0,yw.SL0000,0,0,0,0);
       end if;
    elsif yw.FLAG00=-4 then
       update IC_ICYE00 set BQBFSL=BQBFSL+yw.SL0000 where CZRQ00=yw.CZRQ00 and YGBH00=yw.YGBH00;
       if sql%notfound then
          insert into IC_ICYE00(YGBH00,CZRQ00,SQYE00,BQFS00,BQTC00,BQLICK,BQSJSL,BQBFSL,BQYE00,BQSJYE,SQSJYE)
                 values (yw.YGBH00,yw.CZRQ00,0,0,0,0,0,yw.SL0000,0,0,0);
       end if;
    end if;
  end loop;

  for yw in C_YWRQ00 loop
    update IC_ICYE00 a set SQYE00=(select nvl(max(BQYE00),0) from IC_ICYE00 b where b.YGBH00=a.YGBH00
                                      and b.CZRQ00=(select max(CZRQ00) from IC_ICYE00 c where c.YGBH00=a.YGBH00 and c.CZRQ00<a.CZRQ00))
           where CZRQ00=yw.CZRQ00;
    update IC_ICYE00 a set SQSJYE=(select nvl(max(BQSJYE),0) from IC_ICYE00 b where b.YGBH00=a.YGBH00
                                      and b.CZRQ00=(select max(CZRQ00) from IC_ICYE00 c where c.YGBH00=a.YGBH00 and c.CZRQ00<a.CZRQ00))
           where CZRQ00=yw.CZRQ00;
    update IC_ICYE00 a set BQYE00=nvl(SQYE00,0)-nvl(BQFS00,0)+nvl(BQTC00,0)+nvl(BQLICK,0)-nvl(BQSJSL,0)-nvl(BQBFSL,0),
                           BQSJYE=nvl(SQSJYE,0)-nvl(BQFS00,0)+nvl(BQLICK,0)-nvl(BQBFSL,0)
           where CZRQ00=yw.CZRQ00;
    -- 添加员工合计值记录
    insert into IC_ICYE00(YGBH00,CZRQ00,SQYE00,BQFS00,BQTC00,BQLICK,BQSJSL,BQBFSL,BQYE00,BQSJYE,SQSJYE)
      select -2,max(CZRQ00),sum(SQYE00),sum(BQFS00),sum(BQTC00),sum(BQLICK),sum(BQSJSL),sum(BQBFSL),sum(BQYE00),sum(BQSJYE),sum(SQSJYE)
        from IC_ICYE00 where CZRQ00=yw.CZRQ00 and YGBH00>=0;
  end loop;

  if Pcommit='Y' then
     commit;
  end if;

end;