CREATE OR REPLACE PROCEDURE SP_KS_ZJHZB0 --增减汇总
(
       an_SFSF00 in   NUMBER,               --是否可收费  1.可收费，2.不可收费；3.全部
       an_KSMC00  IN   NUMBER,               --科室名称
       as_KSRQ00  IN   CHAR,                 --开始日期
       as_KSSJ00  IN   CHAR,                 --开始时间
       as_JSRQ00  IN   CHAR,                 --结束日期
       as_JSSJ00  IN   CHAR,                 --结束时间
       ad_ID0000  OUT  NUMBER               --统计序列号
      -- as_YHMSG0  OUT  VARCHAR,              --存储过程提示的错误信息
      -- as_SYSMSG  OUT  VARCHAR               --系统提示的错误信息
)
-- modification history
-- Person      Date        Comments
-- CYF       2020.11.24   科室物资增减汇总表
-- pl        2021.02.23   新增供应室分类

-- ---------   ----------  -------------------------------------------
as

BEGIN
   select  SQ_KS_TEMP00_TJID00.nextval into ad_ID0000  from  dual;
   IF an_SFSF00=1 then
     -- 期初结存
     insert into KS_TEMP00(TJID00,TJPX00,TEMP00,WZMC00,DJ0000,WZSXMC)
     select  ad_ID0000, 0, '期初结存', a.YPNM00, a.JCGJJE, b.YLBH00
       from  KS_YPMXZ0 a,BM_YD0000 b
      where  a.YPNM00=b.YPNM00 and ((an_KSMC00='9999') or (a.YFBMBH=an_KSMC00)) and a.wzsx00 in ('0','1','2')
        and  a.MXZLSH in (select max(MXZLSH) from KS_YPMXZ0 where YFBMBH=an_KSMC00 AND 
             JZRQ00 <= as_KSRQ00 and JZRQ00||JZSJ00 <= as_KSRQ00||as_KSSJ00 group by YPNM00);

   elsif an_SFSF00=2 then
          insert into KS_TEMP00(TJID00,TJPX00,TEMP00,WZMC00,DJ0000,WZSXMC)
     select  ad_ID0000, 0, '期初结存', a.YPNM00, a.JCGJJE, b.YLBH00
       from  KS_YPMXZ0 a,BM_YD0000 b
      where  a.YPNM00=b.YPNM00 and ((an_KSMC00='9999') or (a.YFBMBH=an_KSMC00)) and (a.wzsx00='3' or a.wzsx00 is null )
        and  a.MXZLSH in (select max(MXZLSH) from KS_YPMXZ0 where YFBMBH=an_KSMC00 AND 
             JZRQ00 <= as_KSRQ00 and JZRQ00||JZSJ00 <= as_KSRQ00||as_KSSJ00 group by YPNM00);
     else
         insert into KS_TEMP00(TJID00,TJPX00,TEMP00,WZMC00,DJ0000,WZSXMC)
     select  ad_ID0000, 0, '期初结存', a.YPNM00, a.JCGJJE, b.YLBH00
       from  KS_YPMXZ0 a,BM_YD0000 b
      where  a.YPNM00=b.YPNM00 and ((an_KSMC00='9999') or (a.YFBMBH=an_KSMC00))
        and  a.MXZLSH in (select max(MXZLSH) from KS_YPMXZ0 where YFBMBH=an_KSMC00 AND 
             JZRQ00 <= as_KSRQ00 and JZRQ00||JZSJ00 <= as_KSRQ00||as_KSSJ00 group by YPNM00);
    end if;

    IF an_SFSF00=1 then
     -- 本期增加
     insert into KS_TEMP00(TJID00,TJPX00,TEMP00,WZMC00,DJ0000,WZSXMC)
     select  ad_ID0000, 1, '本期增加', a.YPNM00, sum(nvl(a.GJSLJE,0)), b.YLBH00
       from  KS_YPMXZ0 a,BM_YD0000 b
      where  a.YPNM00=b.YPNM00 and ((an_KSMC00='9999') or (a.YFBMBH=an_KSMC00))and a.wzsx00 in ('0','1','2')
        and  a.JZXZ00='R' 
        and  a.JZRQ00 between as_KSRQ00 and as_JSRQ00 and JZRQ00||JZSJ00 >= as_KSRQ00||as_KSSJ00 and JZRQ00||JZSJ00 <= as_JSRQ00||as_JSSJ00
      group by a.YPNM00, b.YLBH00;

     elsif  an_SFSF00=2 then
          insert into KS_TEMP00(TJID00,TJPX00,TEMP00,WZMC00,DJ0000,WZSXMC)
     select  ad_ID0000, 1, '本期增加', a.YPNM00, sum(nvl(a.GJSLJE,0)), b.YLBH00
       from  KS_YPMXZ0 a,BM_YD0000 b
      where  a.YPNM00=b.YPNM00 and ((an_KSMC00='9999') or (a.YFBMBH=an_KSMC00))and (a.wzsx00='3'or a.wzsx00 is null   )
        and  a.JZXZ00='R' 
        and  a.JZRQ00 between as_KSRQ00 and as_JSRQ00 and JZRQ00||JZSJ00 >= as_KSRQ00||as_KSSJ00 and JZRQ00||JZSJ00 <= as_JSRQ00||as_JSSJ00
      group by a.YPNM00, b.YLBH00;
      else
            insert into KS_TEMP00(TJID00,TJPX00,TEMP00,WZMC00,DJ0000,WZSXMC)
     select  ad_ID0000, 1, '本期增加', a.YPNM00, sum(nvl(a.GJSLJE,0)), b.YLBH00
       from  KS_YPMXZ0 a,BM_YD0000 b
      where  a.YPNM00=b.YPNM00 and ((an_KSMC00='9999') or (a.YFBMBH=an_KSMC00))
        and  a.JZXZ00='R' 
        and  a.JZRQ00 between as_KSRQ00 and as_JSRQ00 and JZRQ00||JZSJ00 >= as_KSRQ00||as_KSSJ00 and JZRQ00||JZSJ00 <= as_JSRQ00||as_JSSJ00
      group by a.YPNM00, b.YLBH00;
      end if ;

    IF an_SFSF00=1 then
     -- 物资入库
     insert into KS_TEMP00(TJID00,TJPX00,TEMP00,WZMC00,DJ0000,WZSXMC)
     select  ad_ID0000, 4, '库房入库', a.YPNM00, sum(nvl(a.GJSLJE,0)), b.YLBH00
       from  KS_YPMXZ0 a,BM_YD0000 b
      where  a.YPNM00=b.YPNM00 and ((an_KSMC00='9999') or (a.YFBMBH=an_KSMC00))and a.wzsx00 in ('0','1','2')
        and  a.JZXZ00='R' and a.rclxbh='03'  
        and  a.JZRQ00 between as_KSRQ00 and as_JSRQ00 and JZRQ00||JZSJ00 >= as_KSRQ00||as_KSSJ00 and JZRQ00||JZSJ00 <= as_JSRQ00||as_JSSJ00
      group by a.YPNM00, b.YLBH00;

     elsif  an_SFSF00=2 then
          insert into KS_TEMP00(TJID00,TJPX00,TEMP00,WZMC00,DJ0000,WZSXMC)
     select  ad_ID0000, 4, '库房入库', a.YPNM00, sum(nvl(a.GJSLJE,0)), b.YLBH00
       from  KS_YPMXZ0 a,BM_YD0000 b
      where  a.YPNM00=b.YPNM00 and ((an_KSMC00='9999') or (a.YFBMBH=an_KSMC00))and (a.wzsx00='3'or a.wzsx00 is null   )
        and  a.JZXZ00='R' and a.rclxbh='03'  
        and  a.JZRQ00 between as_KSRQ00 and as_JSRQ00 and JZRQ00||JZSJ00 >= as_KSRQ00||as_KSSJ00 and JZRQ00||JZSJ00 <= as_JSRQ00||as_JSSJ00
      group by a.YPNM00, b.YLBH00;
      else
            insert into KS_TEMP00(TJID00,TJPX00,TEMP00,WZMC00,DJ0000,WZSXMC)
     select  ad_ID0000, 4, '库房入库', a.YPNM00, sum(nvl(a.GJSLJE,0)), b.YLBH00
       from  KS_YPMXZ0 a,BM_YD0000 b
      where  a.YPNM00=b.YPNM00 and ((an_KSMC00='9999') or (a.YFBMBH=an_KSMC00))
        and  a.JZXZ00='R' and a.rclxbh='03'
        and  a.JZRQ00 between as_KSRQ00 and as_JSRQ00 and JZRQ00||JZSJ00 >= as_KSRQ00||as_KSSJ00 and JZRQ00||JZSJ00 <= as_JSRQ00||as_JSSJ00
      group by a.YPNM00, b.YLBH00;
      end if ;

    IF an_SFSF00=1 then
     -- 供应室入库
     insert into KS_TEMP00(TJID00,TJPX00,TEMP00,WZMC00,DJ0000,WZSXMC)
     select  ad_ID0000, 8, '供应室入库', a.YPNM00, sum(nvl(a.GJSLJE,0)), b.YLBH00
       from  KS_YPMXZ0 a,BM_YD0000 b
      where  a.YPNM00=b.YPNM00 and ((an_KSMC00='9999') or (a.YFBMBH=an_KSMC00))and a.wzsx00 in ('0','1','2')
        and  a.JZXZ00='R' and a.rclxbh='01'  
        and  a.JZRQ00 between as_KSRQ00 and as_JSRQ00 and JZRQ00||JZSJ00 >= as_KSRQ00||as_KSSJ00 and JZRQ00||JZSJ00 <= as_JSRQ00||as_JSSJ00
      group by a.YPNM00, b.YLBH00;

     elsif  an_SFSF00=2 then
          insert into KS_TEMP00(TJID00,TJPX00,TEMP00,WZMC00,DJ0000,WZSXMC)
     select  ad_ID0000, 8, '供应室入库', a.YPNM00, sum(nvl(a.GJSLJE,0)), b.YLBH00
       from  KS_YPMXZ0 a,BM_YD0000 b
      where  a.YPNM00=b.YPNM00 and ((an_KSMC00='9999') or (a.YFBMBH=an_KSMC00))and (a.wzsx00='3'or a.wzsx00 is null   )
        and  a.JZXZ00='R' and a.rclxbh='01'  
        and  a.JZRQ00 between as_KSRQ00 and as_JSRQ00 and JZRQ00||JZSJ00 >= as_KSRQ00||as_KSSJ00 and JZRQ00||JZSJ00 <= as_JSRQ00||as_JSSJ00
      group by a.YPNM00, b.YLBH00;
      else
            insert into KS_TEMP00(TJID00,TJPX00,TEMP00,WZMC00,DJ0000,WZSXMC)
     select  ad_ID0000, 8, '供应室入库', a.YPNM00, sum(nvl(a.GJSLJE,0)), b.YLBH00
       from  KS_YPMXZ0 a,BM_YD0000 b
      where  a.YPNM00=b.YPNM00 and ((an_KSMC00='9999') or (a.YFBMBH=an_KSMC00))
        and  a.JZXZ00='R' and a.rclxbh='01' 
        and  a.JZRQ00 between as_KSRQ00 and as_JSRQ00 and JZRQ00||JZSJ00 >= as_KSRQ00||as_KSSJ00 and JZRQ00||JZSJ00 <= as_JSRQ00||as_JSSJ00
      group by a.YPNM00, b.YLBH00;
      end if ;

    IF an_SFSF00=1 then
     -- 其他入库
     insert into KS_TEMP00(TJID00,TJPX00,TEMP00,WZMC00,DJ0000,WZSXMC)
     select  ad_ID0000, 5, '其他入库', a.YPNM00, sum(nvl(a.GJSLJE,0)), b.YLBH00
       from  KS_YPMXZ0 a,BM_YD0000 b
      where  a.YPNM00=b.YPNM00 and ((an_KSMC00='9999') or (a.YFBMBH=an_KSMC00))and a.wzsx00 in ('0','1','2')
        and  a.JZXZ00='R' and a.rclxbh not in ('01','03')  
        and  a.JZRQ00 between as_KSRQ00 and as_JSRQ00 and JZRQ00||JZSJ00 >= as_KSRQ00||as_KSSJ00 and JZRQ00||JZSJ00 <= as_JSRQ00||as_JSSJ00
      group by a.YPNM00, b.YLBH00;

     elsif  an_SFSF00=2 then
          insert into KS_TEMP00(TJID00,TJPX00,TEMP00,WZMC00,DJ0000,WZSXMC)
     select  ad_ID0000, 5, '其他入库', a.YPNM00, sum(nvl(a.GJSLJE,0)), b.YLBH00
       from  KS_YPMXZ0 a,BM_YD0000 b
      where  a.YPNM00=b.YPNM00 and ((an_KSMC00='9999') or (a.YFBMBH=an_KSMC00))and (a.wzsx00='3'or a.wzsx00 is null   )
        and  a.JZXZ00='R' and a.rclxbh not in ('01','03')  
        and  a.JZRQ00 between as_KSRQ00 and as_JSRQ00 and JZRQ00||JZSJ00 >= as_KSRQ00||as_KSSJ00 and JZRQ00||JZSJ00 <= as_JSRQ00||as_JSSJ00
      group by a.YPNM00, b.YLBH00;
      else
            insert into KS_TEMP00(TJID00,TJPX00,TEMP00,WZMC00,DJ0000,WZSXMC)
     select  ad_ID0000, 5, '其他入库', a.YPNM00, sum(nvl(a.GJSLJE,0)), b.YLBH00
       from  KS_YPMXZ0 a,BM_YD0000 b
      where  a.YPNM00=b.YPNM00 and ((an_KSMC00='9999') or (a.YFBMBH=an_KSMC00))
        and  a.JZXZ00='R' and a.rclxbh not in ('01','03') 
        and  a.JZRQ00 between as_KSRQ00 and as_JSRQ00 and JZRQ00||JZSJ00 >= as_KSRQ00||as_KSSJ00 and JZRQ00||JZSJ00 <= as_JSRQ00||as_JSSJ00
      group by a.YPNM00, b.YLBH00;
      end if ;

      IF an_SFSF00=1 then
      -- 本期减少
      insert into KS_TEMP00(TJID00,TJPX00,TEMP00,WZMC00,DJ0000,WZSXMC)
     select  ad_ID0000, 2, '本期减少', a.YPNM00, sum(nvl(a.GJFCJE,0)), b.YLBH00
       from  KS_YPMXZ0 a,BM_YD0000 b
      where  a.YPNM00=b.YPNM00 and ((an_KSMC00='9999') or (a.YFBMBH=an_KSMC00)) and a.wzsx00 in ('0','1','2')
        and  a.JZXZ00='C' 
         and a.JZRQ00 between as_KSRQ00 and as_JSRQ00 and JZRQ00||JZSJ00 >= as_KSRQ00||as_KSSJ00 and JZRQ00||JZSJ00 <= as_JSRQ00||as_JSSJ00
               group by a.YPNM00, b.YLBH00;
     elsif  an_SFSF00=2 then
             insert into KS_TEMP00(TJID00,TJPX00,TEMP00,WZMC00,DJ0000,WZSXMC)
     select  ad_ID0000, 2, '本期减少', a.YPNM00, sum(nvl(a.GJFCJE,0)), b.YLBH00
       from  KS_YPMXZ0 a,BM_YD0000 b
      where  a.YPNM00=b.YPNM00 and ((an_KSMC00='9999') or (a.YFBMBH=an_KSMC00)) and (a.wzsx00='3' or a.wzsx00 is null )
        and  a.JZXZ00='C' 
         and a.JZRQ00 between as_KSRQ00 and as_JSRQ00 and JZRQ00||JZSJ00 >= as_KSRQ00||as_KSSJ00 and JZRQ00||JZSJ00 <= as_JSRQ00||as_JSSJ00
               group by a.YPNM00, b.YLBH00;
      else
               insert into KS_TEMP00(TJID00,TJPX00,TEMP00,WZMC00,DJ0000,WZSXMC)
     select  ad_ID0000, 2, '本期减少', a.YPNM00, sum(nvl(a.GJFCJE,0)), b.YLBH00
       from  KS_YPMXZ0 a,BM_YD0000 b
      where  a.YPNM00=b.YPNM00 and ((an_KSMC00='9999') or (a.YFBMBH=an_KSMC00))
        and  a.JZXZ00='C' 
         and a.JZRQ00 between as_KSRQ00 and as_JSRQ00 and JZRQ00||JZSJ00 >= as_KSRQ00||as_KSSJ00 and JZRQ00||JZSJ00 <= as_JSRQ00||as_JSSJ00
               group by a.YPNM00, b.YLBH00;
       end if ;

      IF an_SFSF00=1 then
      -- 处方出库
      insert into KS_TEMP00(TJID00,TJPX00,TEMP00,WZMC00,DJ0000,WZSXMC)
     select  ad_ID0000, 6, '处方出库', a.YPNM00, sum(nvl(a.GJFCJE,0)), b.YLBH00
       from  KS_YPMXZ0 a,BM_YD0000 b
      where  a.YPNM00=b.YPNM00 and ((an_KSMC00='9999') or (a.YFBMBH=an_KSMC00)) and a.wzsx00 in ('0','1','2')
        and  a.JZXZ00='C' and a.rclxbh='03'  
         and a.JZRQ00 between as_KSRQ00 and as_JSRQ00 and JZRQ00||JZSJ00 >= as_KSRQ00||as_KSSJ00 and JZRQ00||JZSJ00 <= as_JSRQ00||as_JSSJ00
               group by a.YPNM00, b.YLBH00;
     elsif  an_SFSF00=2 then
             insert into KS_TEMP00(TJID00,TJPX00,TEMP00,WZMC00,DJ0000,WZSXMC)
     select  ad_ID0000, 6, '处方出库', a.YPNM00, sum(nvl(a.GJFCJE,0)), b.YLBH00
       from  KS_YPMXZ0 a,BM_YD0000 b
      where  a.YPNM00=b.YPNM00 and ((an_KSMC00='9999') or (a.YFBMBH=an_KSMC00)) and (a.wzsx00='3' or a.wzsx00 is null )
        and  a.JZXZ00='C' and a.rclxbh='03'  
         and a.JZRQ00 between as_KSRQ00 and as_JSRQ00 and JZRQ00||JZSJ00 >= as_KSRQ00||as_KSSJ00 and JZRQ00||JZSJ00 <= as_JSRQ00||as_JSSJ00
               group by a.YPNM00, b.YLBH00;
      else
               insert into KS_TEMP00(TJID00,TJPX00,TEMP00,WZMC00,DJ0000,WZSXMC)
     select  ad_ID0000, 6, '处方出库', a.YPNM00, sum(nvl(a.GJFCJE,0)), b.YLBH00
       from  KS_YPMXZ0 a,BM_YD0000 b
      where  a.YPNM00=b.YPNM00 and ((an_KSMC00='9999') or (a.YFBMBH=an_KSMC00))
        and  a.JZXZ00='C' and a.rclxbh='03'  
         and a.JZRQ00 between as_KSRQ00 and as_JSRQ00 and JZRQ00||JZSJ00 >= as_KSRQ00||as_KSSJ00 and JZRQ00||JZSJ00 <= as_JSRQ00||as_JSSJ00
               group by a.YPNM00, b.YLBH00;
       end if ;

      IF an_SFSF00=1 then
      -- 其他出库
      insert into KS_TEMP00(TJID00,TJPX00,TEMP00,WZMC00,DJ0000,WZSXMC)
     select  ad_ID0000, 7, '其他出库', a.YPNM00, sum(nvl(a.GJFCJE,0)), b.YLBH00
       from  KS_YPMXZ0 a,BM_YD0000 b
      where  a.YPNM00=b.YPNM00 and ((an_KSMC00='9999') or (a.YFBMBH=an_KSMC00)) and a.wzsx00 in ('0','1','2')
        and  a.JZXZ00='C' and a.rclxbh<>'03'  
         and a.JZRQ00 between as_KSRQ00 and as_JSRQ00 and JZRQ00||JZSJ00 >= as_KSRQ00||as_KSSJ00 and JZRQ00||JZSJ00 <= as_JSRQ00||as_JSSJ00
               group by a.YPNM00, b.YLBH00;
     elsif  an_SFSF00=2 then
             insert into KS_TEMP00(TJID00,TJPX00,TEMP00,WZMC00,DJ0000,WZSXMC)
     select  ad_ID0000, 7, '其他出库', a.YPNM00, sum(nvl(a.GJFCJE,0)), b.YLBH00
       from  KS_YPMXZ0 a,BM_YD0000 b
      where  a.YPNM00=b.YPNM00 and ((an_KSMC00='9999') or (a.YFBMBH=an_KSMC00)) and (a.wzsx00='3' or a.wzsx00 is null )
        and  a.JZXZ00='C' and a.rclxbh<>'03' 
         and a.JZRQ00 between as_KSRQ00 and as_JSRQ00 and JZRQ00||JZSJ00 >= as_KSRQ00||as_KSSJ00 and JZRQ00||JZSJ00 <= as_JSRQ00||as_JSSJ00
               group by a.YPNM00, b.YLBH00;
      else
               insert into KS_TEMP00(TJID00,TJPX00,TEMP00,WZMC00,DJ0000,WZSXMC)
     select  ad_ID0000, 7, '其他出库', a.YPNM00, sum(nvl(a.GJFCJE,0)), b.YLBH00
       from  KS_YPMXZ0 a,BM_YD0000 b
      where  a.YPNM00=b.YPNM00 and ((an_KSMC00='9999') or (a.YFBMBH=an_KSMC00))
        and  a.JZXZ00='C' and a.rclxbh<>'03' 
         and a.JZRQ00 between as_KSRQ00 and as_JSRQ00 and JZRQ00||JZSJ00 >= as_KSRQ00||as_KSSJ00 and JZRQ00||JZSJ00 <= as_JSRQ00||as_JSSJ00
               group by a.YPNM00, b.YLBH00;
       end if ;


      IF an_SFSF00=1 then
      -- 本期结存
     insert into KS_TEMP00(TJID00,TJPX00,TEMP00,WZMC00,DJ0000,WZSXMC)
     select  ad_ID0000, 3, '本期结存', a.YPNM00, a.JCGJJE, b.YLBH00
       from  KS_YPMXZ0 a,BM_YD0000 b
      where  a.YPNM00=b.YPNM00 and ((an_KSMC00='9999') or (a.YFBMBH=an_KSMC00)) and a.wzsx00 in ('0','1','2')
        and  a.MXZLSH in (select max(MXZLSH) from KS_YPMXZ0 where YFBMBH=an_KSMC00 AND   
             JZRQ00 <= as_JSRQ00 and JZRQ00||JZSJ00 <= as_JSRQ00||as_JSSJ00 group by YPNM00);
        elsif  an_SFSF00=2 then
          insert into KS_TEMP00(TJID00,TJPX00,TEMP00,WZMC00,DJ0000,WZSXMC)
     select  ad_ID0000, 3, '本期结存', a.YPNM00, a.JCGJJE, b.YLBH00
       from  KS_YPMXZ0 a,BM_YD0000 b
      where  a.YPNM00=b.YPNM00 and ((an_KSMC00='9999') or (a.YFBMBH=an_KSMC00)) and (a.wzsx00='3' or a.wzsx00 is null )
        and  a.MXZLSH in (select max(MXZLSH) from KS_YPMXZ0 where YFBMBH=an_KSMC00 AND  
             JZRQ00 <= as_JSRQ00 and JZRQ00||JZSJ00 <= as_JSRQ00||as_JSSJ00 group by YPNM00);
         else
              insert into KS_TEMP00(TJID00,TJPX00,TEMP00,WZMC00,DJ0000,WZSXMC)
     select  ad_ID0000, 3, '本期结存', a.YPNM00, a.JCGJJE, b.YLBH00
       from  KS_YPMXZ0 a,BM_YD0000 b
      where  a.YPNM00=b.YPNM00 and ((an_KSMC00='9999') or (a.YFBMBH=an_KSMC00))
        and  a.MXZLSH in (select max(MXZLSH) from KS_YPMXZ0 where YFBMBH=an_KSMC00 AND   
             JZRQ00 <= as_JSRQ00 and JZRQ00||JZSJ00 <= as_JSRQ00||as_JSSJ00 group by YPNM00);
       end if ;


      -- 生成报表
     Insert into KS_TEMP00(TJID00,PH0000,TEMP00,SDJ000,SZJSL0,ZJSL00,GYS000,ZJJE00,SJSSL0,JSSL00,JSJE00,SJCS00,HGH000)    
          select ad_ID0000, a.WZSXMC,
                 (select YLMC00 from BM_YPYL00 where YLBH00=b.SJYLBH)||'--'||b.YLMC00,
                 sum(decode(a.TJPX00,0,a.DJ0000,0)),
                 sum(decode(a.TJPX00,1,a.DJ0000,0)),
                 sum(decode(a.TJPX00,4,a.DJ0000,0)),
                 sum(decode(a.TJPX00,8,a.DJ0000,0)),
                 sum(decode(a.TJPX00,5,a.DJ0000,0)),
                 sum(decode(a.TJPX00,2,a.DJ0000,0)),
                 sum(decode(a.TJPX00,6,a.DJ0000,0)),
                 sum(decode(a.TJPX00,7,a.DJ0000,0)),
                 sum(decode(a.TJPX00,3,a.DJ0000,0)),
                 '2'
            from KS_TEMP00 a,BM_YPYL00 b
           where trim(a.WZSXMC)=b.YLBH00 and b.LBBH00='9'
             and a.TJID00=ad_ID0000 and a.TJPX00 in (0,1,2,3,4,5,6,7,8)
        group by a.WZSXMC,b.SJYLBH,b.YLMC00;

     Insert into KS_TEMP00(TJID00,PH0000,TEMP00,SDJ000,SZJSL0,ZJSL00,GYS000,ZJJE00,SJSSL0,JSSL00,JSJE00,SJCS00,HGH000) 
          select ad_ID0000, b.SJYLBH,
                 (select YLMC00 from BM_YPYL00 where YLBH00=b.SJYLBH)||'(小计)',
                 sum(decode(a.TJPX00,0,a.DJ0000,0)),
                 sum(decode(a.TJPX00,1,a.DJ0000,0)),
                 sum(decode(a.TJPX00,4,a.DJ0000,0)),
                 sum(decode(a.TJPX00,8,a.DJ0000,0)),
                 sum(decode(a.TJPX00,5,a.DJ0000,0)),
                 sum(decode(a.TJPX00,2,a.DJ0000,0)),
                 sum(decode(a.TJPX00,6,a.DJ0000,0)),
                 sum(decode(a.TJPX00,7,a.DJ0000,0)),
                 sum(decode(a.TJPX00,3,a.DJ0000,0)),
                 '1'
            from KS_TEMP00 a,BM_YPYL00 b
           where trim(a.WZSXMC)=b.YLBH00 and b.LBBH00='9'
             and a.TJID00=ad_ID0000 and a.TJPX00 in (0,1,2,3,4,5,6,7,8)
        group by b.SJYLBH;

     --总计
     Insert into KS_TEMP00(TJID00,PH0000,TEMP00,SDJ000,SZJSL0,ZJSL00,GYS000,ZJJE00,SJSSL0,JSSL00,JSJE00,SJCS00,HGH000)    --(TJID00,PH0000,TEMP00,SDJ000,SZJSL0,SJSSL0,SJCS00,HGH000)
          select ad_ID0000, '9999', '总计：',
                 sum(decode(TJPX00,0,DJ0000,0)),
                 sum(decode(TJPX00,1,DJ0000,0)),
                 sum(decode(TJPX00,4,DJ0000,0)),
                 sum(decode(TJPX00,8,DJ0000,0)),
                 sum(decode(TJPX00,5,DJ0000,0)),
                 sum(decode(TJPX00,2,DJ0000,0)),
                 sum(decode(TJPX00,6,DJ0000,0)),
                 sum(decode(TJPX00,7,DJ0000,0)),
                 sum(decode(TJPX00,3,DJ0000,0)),
                 '0'
            from KS_TEMP00
           where TJID00=ad_ID0000 and TJPX00 in (0,1,2,3,4,5,6,7,8);

     delete from KS_TEMP00 where TJID00=ad_ID0000 and TJPX00 in (0,1,2,3,4,5,6,7,8);
     commit;
  EXCEPTION
        WHEN OTHERS THEN
      raise_application_error(-20002,substr(SQLERRM||'执行存储过程错误:SP_WZ_ZJHZB0',1,150));
                  rollback;
END;