CREATE OR REPLACE PROCEDURE SP_WZ_JSMX00 --物资减少明细报表
(
       an_KSMC00  in   number,     --科室ID
       as_TJLB00  in   char,       --统计类别
       as_WZLB00  in   varchar,    --物资类别
       as_KSRQ00  in   char,       --开始日期
       as_JSRQ00  in   char,       --结束日期
       ad_ID0000  out  number,     --统计序列号
       as_YHMSG0  out  varchar,    --存储过程提示的错误信息
       as_SYSMSG  out  varchar     --系统提示的错误信息
)
as
    ls_SJYLBH  BM_YPYL00.SJYLBH%TYPE;
    ls_YLBH00  BM_YPYL00.YLBH00%TYPE;
    ls_YPNM00  BM_YD0000.YPNM00%TYPE;
    ls_CRBMBH  YK_YPMXZ0.CRBMBH%TYPE;
    ls_XH0000  char(5);
    ls_JE0000  varchar2(20);
    ls_SQLTXT  varchar2(1000);

    cursor WZ_JSMX00 is
      select c.SJYLBH,a.CRBMBH,SUM(a.GJFCJE),to_char(dense_rank() over(order by c.SJYLBH),'FM00') AS XH0000
      from YK_YPMXZ0 a,BM_YD0000 b,BM_YPYL00 c
      where a.YPNM00=b.YPNM00 and b.YLBH00=c.YLBH00 and a.JZXZ00='C'
      and  a.JZRQ00 between as_KSRQ00 and as_JSRQ00
      and ((an_KSMC00='9999') or (a.YKBMBH=an_KSMC00))
      and a.YKBMBH in (select BMBH00 from BM_BMBM00 where BMXZ00='c' and flag00='1')
      group by c.SJYLBH,a.CRBMBH;

    cursor WZ_JSMX01 is
      select b.YLBH00,a.CRBMBH,SUM(a.GJFCJE),to_char(dense_rank() over(order by b.YLBH00),'FM00') AS XH0000
      from YK_YPMXZ0 a,BM_YD0000 b,BM_YPYL00 c
      where a.YPNM00=b.YPNM00 and b.YLBH00=c.YLBH00 and a.JZXZ00='C'
      and  a.JZRQ00 between as_KSRQ00 and as_JSRQ00 and c.SJYLBH=as_WZLB00
      and ((an_KSMC00='9999') or (a.YKBMBH=an_KSMC00))
      and a.YKBMBH in (select BMBH00 from BM_BMBM00 where BMXZ00='c' and flag00='1')
      group by b.YLBH00,a.CRBMBH;

    cursor WZ_JSMX02 is
      select a.YPNM00,a.CRBMBH,SUM(a.GJFCJE),to_char(dense_rank() over(order by a.YPNM00),'FM00') AS XH0000
      from YK_YPMXZ0 a
      where a.JZXZ00='C'
      and  a.JZRQ00 between as_KSRQ00 and as_JSRQ00
      and ((an_KSMC00='9999') or (a.YKBMBH=an_KSMC00))
      and a.YKBMBH in (select BMBH00 from BM_BMBM00 where BMXZ00='c' and flag00='1')
      group by a.YPNM00,a.CRBMBH;

-- MODIFICATION HISTORY
-- Person      Date        Comments
-- gzj         2018.07.26  create:物资减少明细报表  KCWZ9-20180727-001
-- gzj         2018.11.09  根据领用部门来统计物资减少的金额  KCWZ9-20181012-001
-- gzj         2019.01.28  类别横向显示，科室竖向显示，二级明细暂不统计  KCWZ9-20190128-001
-- ---------   ----------  -------------------------------------------
begin
   select  SQ_WZ_TEMP00_TJID00.nextval into ad_ID0000  from  dual;

   --减少明细汇总
   if as_TJLB00='0' then
     insert into WZ_EJJSXB(EJJSID,BH0000,KSMC00,HJ0000)
     select ad_ID0000, a.CRBMBH, DECODE(a.crbmbh,'88888','报损出库',(select BMMC00 from VW_YF_YPGHDW where BMBH00=a.CRBMBH)), sum(nvl(GJFCJE,0))
     from YK_YPMXZ0 a,BM_YD0000 b
     where a.JZXZ00='C' and  a.YPNM00=b.YPNM00
     and ((an_KSMC00='9999') or (a.YKBMBH=an_KSMC00)) and a.JZRQ00 between as_KSRQ00 and as_JSRQ00
     and a.YKBMBH in (select BMBH00 from BM_BMBM00 where BMXZ00='c' and flag00='1')
     group by a.CRBMBH;

     open WZ_JSMX00;
     loop
       fetch WZ_JSMX00 into ls_SJYLBH,ls_CRBMBH,ls_JE0000,ls_XH0000;
       exit when WZ_JSMX00%NOTFOUND;

         ls_SQLTXT := 'update WZ_EJJSXB set EJFL'||ls_XH0000||'='||ls_JE0000||' where EJJSID='||ad_ID0000||
                      ' and BH0000='''||ls_CRBMBH||'''';
         SP_EXECUTE_SQL(ls_SQLTXT);

         insert into  WZ_TEMP00(TJID00,WZXH00,WZMC00) values(ad_ID0000,trim(ls_XH0000),trim(ls_SJYLBH));

     end loop;
     close WZ_JSMX00;
   end if;
   --一级明细
   if as_TJLB00='1' then
     insert into WZ_EJJSXB(EJJSID,BH0000,KSMC00,HJ0000)
     select ad_ID0000, a.CRBMBH,  DECODE(a.crbmbh,'88888','报损出库',(select BMMC00 from VW_YF_YPGHDW where BMBH00=a.CRBMBH)), sum(nvl(GJFCJE,0))
     from YK_YPMXZ0 a,BM_YD0000 b,BM_YPYL00 c
     where a.YPNM00=b.YPNM00 and b.YLBH00=c.YLBH00 and c.SJYLBH=as_WZLB00
     and a.JZXZ00='C' and  a.JZRQ00 between as_KSRQ00 and as_JSRQ00
     and ((an_KSMC00='9999') or (a.YKBMBH=an_KSMC00))
     and a.YKBMBH in (select BMBH00 from BM_BMBM00 where BMXZ00='c' and flag00='1')
     group by a.CRBMBH;
     open WZ_JSMX01;
     loop
       fetch WZ_JSMX01 into ls_YLBH00,ls_CRBMBH,ls_JE0000,ls_XH0000;
       exit when WZ_JSMX01%NOTFOUND;

         ls_SQLTXT := 'update WZ_EJJSXB set EJFL'||ls_XH0000||'='||ls_JE0000||' where EJJSID='||ad_ID0000||
                      ' and BH0000='''||ls_CRBMBH||'''';
         SP_EXECUTE_SQL(ls_SQLTXT);

         insert into  WZ_TEMP00(TJID00,WZXH00,WZMC00) values(ad_ID0000,trim(ls_XH0000),trim(ls_YLBH00));

     end loop;
     close WZ_JSMX01;
   end if;
   --二级明细  --统计物品较多，与医院沟通后另做报表
   /*if as_TJLB00='2' then
     insert into WZ_EJJSXB(EJJSID,BH0000,KSMC00,HJ0000)
     select ad_ID0000, a.CRBMBH, (select BMMC00 from VW_YF_YPGHDW where BMBH00=a.CRBMBH), sum(nvl(GJFCJE,0))
     from YK_YPMXZ0 a,BM_YD0000 b
     where a.YPNM00=b.YPNM00 and B.YLBH00=as_WZLB00
     and a.JZXZ00='C' and  a.JZRQ00 between as_KSRQ00 and as_JSRQ00
     and ((an_KSMC00='9999') or (a.YKBMBH=an_KSMC00))
     and a.YKBMBH in (select BMBH00 from BM_BMBM00 where BMXZ00='c' and flag00='1')
     group by a.CRBMBH;
     open WZ_JSMX02;
     loop
       fetch WZ_JSMX02 into ls_YPNM00,ls_CRBMBH,ls_JE0000,ls_XH0000;
       exit when WZ_JSMX02%NOTFOUND;

         ls_SQLTXT := 'update WZ_EJJSXB set EJFL'||ls_XH0000||'='||ls_JE0000||' where EJJSID='||ad_ID0000||
                      ' and BH0000='''||ls_CRBMBH||'''';
         SP_EXECUTE_SQL(ls_SQLTXT);

         insert into  WZ_TEMP00(TJID00,WZXH00,WZMC00) values(ad_ID0000,trim(ls_XH0000),trim(ls_YPNM00));

     end loop;
     close WZ_JSMX02;
   end if;*/

     --插入总计行
     Insert into  WZ_EJJSXB (EJJSID,    BH0000,     KSMC00,     HJ0000,     EJFL01,     EJFL02,
            EJFL03,     EJFL04,     EJFL05,     EJFL06,     EJFL07,
            EJFL08,     EJFL09,     EJFL10,     EJFL11,     EJFL12,
            EJFL13,     EJFL14,     EJFL15,     EJFL16,     EJFL17,
            EJFL18,     EJFL19,     EJFL20      )
     select ad_ID0000, 99999999,  '总计：', sum(HJ0000), sum(EJFL01), sum(EJFL02),
            sum(EJFL03), sum(EJFL04), sum(EJFL05), sum(EJFL06), sum(EJFL07), sum(EJFL08), sum(EJFL09), sum(EJFL10),
            sum(EJFL11), sum(EJFL12), sum(EJFL13), sum(EJFL14), sum(EJFL15), sum(EJFL16), sum(EJFL17), sum(EJFL18),
            sum(EJFL19), sum(EJFL20)
       from WZ_EJJSXB where EJJSID=ad_ID0000;

  commit;
exception
        when others then
                  as_YHMSG0:='数据库错误：出错原因未知';
                  as_SYSMSG:=substr(SQLERRM||'执行存储过程错误:SP_WZ_JSMX00',1,150);
                  rollback;
end;