CREATE OR REPLACE PROCEDURE SP_WZ_KSFLLYTJ
(p_ksrq00 in char,     --统计开始日期
 p_jsrq00 in char,     --统计结束日期
 p_wzsx00 in char,     --物资类别
 p_ckbmbh in number,    --仓库部门编号
 p_YHMSG0    OUT   VARCHAR,    --存储过程提示的错误信息
 p_SYSMSG    OUT   VARCHAR     --系统提示的错误信息
 )
as
ls_yjflbh varchar2(10);
ls_sl0000 wz_rckmx0.crksl0%type;
ls_je0000 wz_rckmx0.zje000%type;
ls_ksbmbh wz_rck000.fhbmbh%type;
ls_bh0000 wz_fltj00.bh0000%type;
ls_yjflmc bm_wzyjfl.yjflmc%type;
ls_BHJS00 number(3);
VCounter  number(3);
ls_or varchar2(200);

cursor c_wz_yjfl00 is
select trim(yjflbh),trim(yjflmc) from bm_wzyjfl where yjflbh like p_wzsx00||'%';


cursor c_wz_fltj00 is
select trim(yjflbh),sum(sl0000) sl0000,sum(je0000) je00000, ksbmbh from
(
 select substr(c.wzdmbm,1,3) yjflbh,-sum(b.crksl0) sl0000,-sum(b.zje000) je0000,a.fhbmbh ksbmbh from
 bm_wzdm00 c,wz_rckmx0 b,wz_rck000 a where a.rckid0=b.rckid0 and b.wzdmid=c.wzdmid
 and a.rcklb0='1' and a.ztbz00 in ('2','3','5') and rcklx0 in (select trim(RCKBBH) from bm_rcklb0 where RCKBMC ='退库'
 and RCKBZ0='1' and sfyx00='Y' )
 and a.qrrq00>=p_ksrq00 and a.qrrq00<=p_jsrq00 and (a.Kfbmbh=p_ckbmbh or p_ckbmbh=99999)and c.wzdmbm like p_wzsx00||'%'
 group by  substr(c.wzdmbm,1,3),a.fhbmbh
 union all
  select substr(c.wzdmbm,1,3) yjflbh,sum(b.crksl0) sl0000,sum(b.zje000) je0000,a.shbmbh ksbmbh from
 bm_wzdm00 c,wz_rckmx0 b,wz_rck000 a where a.rckid0=b.rckid0 and b.wzdmid=c.wzdmid
 and a.rcklb0='2' and a.ztbz00 in ('2','3','5')  and rcklx0 in (select trim(RCKBBH) from bm_rcklb0
 where trim(RCKBMC) in ('退库','领用','仓库调拨出库','供应室领用')
 and RCKBZ0='2' and sfyx00='Y' )
 and a.qrrq00>=p_ksrq00 and a.qrrq00<=p_jsrq00 and (a.Kfbmbh=p_ckbmbh or p_ckbmbh=99999)and c.wzdmbm like p_wzsx00||'%'
 group by  substr(c.wzdmbm,1,3),a.shbmbh
)
group by yjflbh,ksbmbh;
-- MODIFICATION HISTORY
-- Person      Date            Comments
-- chenyw     2010.01.14       创建科室领用材料费用统计过程
-- chenyw     2010.03.30       增加wz_lytj00的字段
-- daihq      2013.05.21       增加wz_lytj00的字段 by KCWZ-20130504-001
begin
delete from wz_lytj00;
delete from wz_fltj00;
ls_bh0000:='01';
ls_BHJS00:=1;
ls_or:='1';
open c_wz_yjfl00;
loop
fetch c_wz_yjfl00 into ls_yjflbh,ls_yjflmc;
exit when c_wz_yjfl00%notfound;
  insert into wz_fltj00(bh0000,FLBH00,FLMC00)
     values(trim(ls_bh0000),trim(ls_yjflbh),trim(ls_yjflmc));
  ls_BHJS00:=ls_BHJS00+1;
  select lpad(to_char(ls_BHJS00),2,'0') into ls_bh0000 from dual;
end loop;
close c_wz_yjfl00;

open c_wz_fltj00;
loop
fetch c_wz_fltj00 into ls_yjflbh,ls_sl0000,ls_je0000,ls_ksbmbh;
exit when c_wz_fltj00%notfound;
BEGIN
ls_or:='2';
Select  trim(BH0000)  into  ls_BH0000  FROM  wz_fltj00  WHERE  trim(FLBH00)= trim(ls_yjflbh) and rownum=1;
ls_or:='2.1';
EXCEPTION
WHEN  NO_DATA_FOUND  THEN
     p_YHMSG0:='获得对应的物品编号时,出现错误,请核对详细信息!';
     p_SYSMSG:='select *  from wz_fltj00 where FLBH00 ='||(ls_yjflbh);
     rollback;
     return;
END;
ls_or:='3';
  SELECT COUNT(1) INTO VCounter FROM wz_lytj00 WHERE KSBH00= ls_ksbmbh;
     if  VCounter>0 then
        SP_EXECUTE_SQL('UPDATE wz_lytj00 SET FLSL'||lpad(ls_BH0000,2,'0')||'= NVL(FLSL'||lpad(ls_BH0000,2,'0')||',0)+'||to_char(ls_sl0000)||',FLJE'||lpad(ls_BH0000,2,'0')||'= NVL(FLJE'||lpad(ls_BH0000,2,'0')||',0)+'||to_char(ls_je0000)||',HJ0000=NVL(HJ0000,0)+'||to_char(ls_sl0000)||',HJJE00=NVL(HJJE00,0)+'||to_char(ls_je0000)||' where  KSBH00 = '||chr(39)||to_char(ls_ksbmbh)||chr(39));
     else
       SP_EXECUTE_SQL('Insert into wz_lytj00(KSBH00,FLSL'||lpad(ls_BH0000,2,'0')||',FLJE'||lpad(ls_BH0000,2,'0')||',HJ0000,HJJE00) values('||chr(39)||ls_ksbmbh||chr(39)||','||to_char(ls_sl0000)||','||to_char(ls_je0000)||','||to_char(ls_sl0000)||','||to_char(ls_je0000)||')');
     end if;
end loop;

close c_wz_fltj00;
insert into  wz_lytj00
(
KSBH00,FLSL01,FLJE01,FLSL02,FLJE02,FLSL03,FLJE03,FLSL04,FLJE04,FLSL05,FLJE05,FLSL06,FLJE06,FLSL07,FLJE07,FLSL08,FLJE08,FLSL09,FLJE09,FLSL10,FLJE10,
FLSL11,FLJE11,FLSL12,FLJE12,FLSL13,FLJE13,FLSL14,FLJE14,FLSL15,FLJE15,FLSL16,FLJE16,FLSL17,FLJE17,FLSL18,FLJE18,FLSL19,FLJE19,FLSL20,FLJE20,
FLSL21,FLJE21,FLSL22,FLJE22,FLSL23,FLJE23,FLSL24,FLJE24,FLSL25,FLJE25,FLSL26,FLJE26,FLSL27,FLJE27,FLSL28,FLJE28,FLSL29,FLJE29,FLSL30,FLJE30,
FLSL31,FLJE31,FLSL32,FLJE32,FLSL33,FLJE33,FLSL34,FLJE34,FLSL35,FLJE35,FLSL36,FLJE36,FLSL37,FLJE37,FLSL38,FLJE38,FLSL39,FLJE39,FLSL40,FLJE40,
FLSL41,FLJE41,FLSL42,FLJE42,FLSL43,FLJE43,FLSL44,FLJE44,FLSL45,FLJE45,FLSL46,FLJE46,FLSL47,FLJE47,FLSL48,FLJE48,FLSL49,FLJE49,FLSL50,FLJE50,
FLSL51,FLJE51,FLSL52,FLJE52,FLSL53,FLJE53,FLSL54,FLJE54,FLSL55,FLJE55,FLSL56,FLJE56,FLSL57,FLJE57,FLSL58,FLJE58,FLSL59,FLJE59,FLSL60,FLJE60,
FLSL61,FLJE61,FLSL62,FLJE62,FLSL63,FLJE63,FLSL64,FLJE64,FLSL65,FLJE65,FLSL66,FLJE66,FLSL67,FLJE67,FLSL68,FLJE68,FLSL69,FLJE69,FLSL70,FLJE70,
FLSL71,FLJE71,FLSL72,FLJE72,FLSL73,FLJE73,FLSL74,FLJE74,FLSL75,FLJE75,FLSL76,FLJE76,FLSL77,FLJE77,FLSL78,FLJE78,FLSL79,FLJE79,FLSL80,FLJE80,
FLSL81,FLJE81,FLSL82,FLJE82,FLSL83,FLJE83,FLSL84,FLJE84,FLSL85,FLJE85,FLSL86,FLJE86,FLSL87,FLJE87,FLSL88,FLJE88,FLSL89,FLJE89,FLSL90,FLJE90,
FLSL91,FLJE91,FLSL92,FLJE92,FLSL93,FLJE93,FLSL94,FLJE94,FLSL95,FLJE95,FLSL96,FLJE96,FLSL97,FLJE97,FLSL98,FLJE98,FLSL99,FLJE99,FLSL100,FLJE100,
FLSL101,FLJE101,FLSL102,FLJE102,FLSL103,FLJE103,FLSL104,FLJE104,FLSL105,FLJE105,FLSL106,FLJE106,FLSL107,FLJE107,FLSL108,FLJE108,FLSL109,FLJE109,FLSL110,FLJE110,
FLSL111,FLJE111,FLSL112,FLJE112,FLSL113,FLJE113,FLSL114,FLJE114,FLSL115,FLJE115,FLSL116,FLJE116,FLSL117,FLJE117,FLSL118,FLJE118,FLSL119,FLJE119,FLSL120,FLJE120,
FLSL121,FLJE121,FLSL122,FLJE122,FLSL123,FLJE123,FLSL124,FLJE124,FLSL125,FLJE125,FLSL126,FLJE126,FLSL127,FLJE127,FLSL128,FLJE128,FLSL129,FLJE129,FLSL130,FLJE130,
FLSL131,FLJE131,FLSL132,FLJE132,FLSL133,FLJE133,FLSL134,FLJE134,FLSL135,FLJE135,FLSL136,FLJE136,FLSL137,FLJE137,FLSL138,FLJE138,FLSL139,FLJE139,FLSL140,FLJE140,
FLSL141,FLJE141,FLSL142,FLJE142,FLSL143,FLJE143,FLSL144,FLJE144,FLSL145,FLJE145,FLSL146,FLJE146,FLSL147,FLJE147,FLSL148,FLJE148,FLSL149,FLJE149,FLSL150,FLJE150,
HJ0000,HJJE00)
select
 99999,
sum(FLSL01),
sum(FLJE01),
sum(FLSL02),
sum(FLJE02),
sum(FLSL03),
sum(FLJE03),
sum(FLSL04),
sum(FLJE04),
sum(FLSL05),
sum(FLJE05),
sum(FLSL06),
sum(FLJE06),
sum(FLSL07),
sum(FLJE07),
sum(FLSL08),
sum(FLJE08),
sum(FLSL09),
sum(FLJE09),
sum(FLSL10),
sum(FLJE10),
sum(FLSL11),
sum(FLJE11),
sum(FLSL12),
sum(FLJE12),
sum(FLSL13),
sum(FLJE13),
sum(FLSL14),
sum(FLJE14),
sum(FLSL15),
sum(FLJE15),
sum(FLSL16),
sum(FLJE16),
sum(FLSL17),
sum(FLJE17),
sum(FLSL18),
sum(FLJE18),
sum(FLSL19),
sum(FLJE19),
sum(FLSL20),
sum(FLJE20),
sum(FLSL21),
sum(FLJE21),
sum(FLSL22),
sum(FLJE22),
sum(FLSL23),
sum(FLJE23),
sum(FLSL24),
sum(FLJE24),
sum(FLSL25),
sum(FLJE25),
sum(FLSL26),
sum(FLJE26),
sum(FLSL27),
sum(FLJE27),
sum(FLSL28),
sum(FLJE28),
sum(FLSL29),
sum(FLJE29),
sum(FLSL30),
sum(FLJE30),
sum(FLSL31),
sum(FLJE31),
sum(FLSL32),
sum(FLJE32),
sum(FLSL33),
sum(FLJE33),
sum(FLSL34),
sum(FLJE34),
sum(FLSL35),
sum(FLJE35),
sum(FLSL36),
sum(FLJE36),
sum(FLSL37),
sum(FLJE37),
sum(FLSL38),
sum(FLJE38),
sum(FLSL39),
sum(FLJE39),
sum(FLSL40),
sum(FLJE40),
sum(FLSL41),
sum(FLJE41),
sum(FLSL42),
sum(FLJE42),
sum(FLSL43),
sum(FLJE43),
sum(FLSL44),
sum(FLJE44),
sum(FLSL45),
sum(FLJE45),
sum(FLSL46),
sum(FLJE46),
sum(FLSL47),
sum(FLJE47),
sum(FLSL48),
sum(FLJE48),
sum(FLSL49),
sum(FLJE49),
sum(FLSL50),
sum(FLJE50),
sum(FLSL51),
sum(FLJE51),
sum(FLSL52),
sum(FLJE52),
sum(FLSL53),
sum(FLJE53),
sum(FLSL54),
sum(FLJE54),
sum(FLSL55),
sum(FLJE55),
sum(FLSL56),
sum(FLJE56),
sum(FLSL57),
sum(FLJE57),
sum(FLSL58),
sum(FLJE58),
sum(FLSL59),
sum(FLJE59),
sum(FLSL60),
sum(FLJE60),
sum(FLSL61),
sum(FLJE61),
sum(FLSL62),
sum(FLJE62),
sum(FLSL63),
sum(FLJE63),
sum(FLSL64),
sum(FLJE64),
sum(FLSL65),
sum(FLJE65),
sum(FLSL66),
sum(FLJE66),
sum(FLSL67),
sum(FLJE67),
sum(FLSL68),
sum(FLJE68),
sum(FLSL69),
sum(FLJE69),
sum(FLSL70),
sum(FLJE70),
sum(FLSL71),
sum(FLJE71),
sum(FLSL72),
sum(FLJE72),
sum(FLSL73),
sum(FLJE73),
sum(FLSL74),
sum(FLJE74),
sum(FLSL75),
sum(FLJE75),
sum(FLSL76),
sum(FLJE76),
sum(FLSL77),
sum(FLJE77),
sum(FLSL78),
sum(FLJE78),
sum(FLSL79),
sum(FLJE79),
sum(FLSL80),
sum(FLJE80),
sum(FLSL81),
sum(FLJE81),
sum(FLSL82),
sum(FLJE82),
sum(FLSL83),
sum(FLJE83),
sum(FLSL84),
sum(FLJE84),
sum(FLSL85),
sum(FLJE85),
sum(FLSL86),
sum(FLJE86),
sum(FLSL87),
sum(FLJE87),
sum(FLSL88),
sum(FLJE88),
sum(FLSL89),
sum(FLJE89),
sum(FLSL90),
sum(FLJE90),
sum(FLSL91),
sum(FLJE91),
sum(FLSL92),
sum(FLJE92),
sum(FLSL93),
sum(FLJE93),
sum(FLSL94),
sum(FLJE94),
sum(FLSL95),
sum(FLJE95),
sum(FLSL96),
sum(FLJE96),
sum(FLSL97),
sum(FLJE97),
sum(FLSL98),
sum(FLJE98),
sum(FLSL99),
sum(FLJE99),
sum(FLSL100),
sum(FLJE100),
sum(FLSL101),
sum(FLJE101),
sum(FLSL102),
sum(FLJE102),
sum(FLSL103),
sum(FLJE103),
sum(FLSL104),
sum(FLJE104),
sum(FLSL105),
sum(FLJE105),
sum(FLSL106),
sum(FLJE106),
sum(FLSL107),
sum(FLJE107),
sum(FLSL108),
sum(FLJE108),
sum(FLSL109),
sum(FLJE109),
sum(FLSL110),
sum(FLJE110),
sum(FLSL111),
sum(FLJE111),
sum(FLSL112),
sum(FLJE112),
sum(FLSL113),
sum(FLJE113),
sum(FLSL114),
sum(FLJE114),
sum(FLSL115),
sum(FLJE115),
sum(FLSL116),
sum(FLJE116),
sum(FLSL117),
sum(FLJE117),
sum(FLSL118),
sum(FLJE118),
sum(FLSL119),
sum(FLJE119),
sum(FLSL120),
sum(FLJE120),
sum(FLSL121),
sum(FLJE121),
sum(FLSL122),
sum(FLJE122),
sum(FLSL123),
sum(FLJE123),
sum(FLSL124),
sum(FLJE124),
sum(FLSL125),
sum(FLJE125),
sum(FLSL126),
sum(FLJE126),
sum(FLSL127),
sum(FLJE127),
sum(FLSL128),
sum(FLJE128),
sum(FLSL129),
sum(FLJE129),
sum(FLSL130),
sum(FLJE130),
sum(FLSL131),
sum(FLJE131),
sum(FLSL132),
sum(FLJE132),
sum(FLSL133),
sum(FLJE133),
sum(FLSL134),
sum(FLJE134),
sum(FLSL135),
sum(FLJE135),
sum(FLSL136),
sum(FLJE136),
sum(FLSL137),
sum(FLJE137),
sum(FLSL138),
sum(FLJE138),
sum(FLSL139),
sum(FLJE139),
sum(FLSL140),
sum(FLJE140),
sum(FLSL141),
sum(FLJE141),
sum(FLSL142),
sum(FLJE142),
sum(FLSL143),
sum(FLJE143),
sum(FLSL144),
sum(FLJE144),
sum(FLSL145),
sum(FLJE145),
sum(FLSL146),
sum(FLJE146),
sum(FLSL147),
sum(FLJE147),
sum(FLSL148),
sum(FLJE148),
sum(FLSL149),
sum(FLJE149),
sum(FLSL150),
sum(FLJE150),
sum(HJ0000),
sum(HJJE00)
from  wz_lytj00;
commit;
EXCEPTION
      WHEN OTHERS THEN
                p_YHMSG0:=ls_or||'##出错原因不详,请记录此信息并和系统管理员联系！';
                p_SYSMSG:=substr(SQLERRM||'执行存储过程错误

                :sp_wz_ksFLlytj('||p_KSRQ00||','||p_JSRQ00||','||LS_BH0000||','
                ||'P_YHMSG0,P_SYSMSG',1,150);
        ROLLBACK;
end;