CREATE OR REPLACE PROCEDURE SP_KS_ZJHZ00 --科室增减汇总
(
       as_SFSF00  in   number,               --是否可收费  1.可收费，2.不可收费；3.全部
       as_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
-- linshu      2021.05.07  create KCWZ9-20210414-001
-- ---------   ----------  -------------------------------------------
as
begin
   select  SQ_WZ_TEMP00_TJID00.nextval into ad_ID0000  from  dual;
   if as_KSMC00='9999' then
     if as_SFSF00=1 then
         -- 期初结存
         insert into WZ_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 b.tzrq00 is null   and a.wzsx00 in ('0','1','2')
            and  a.MXZLSH in (select max(MXZLSH) from KS_YPMXZ0 where
                 JZRQ00 <= as_KSRQ00 and JZRQ00||JZSJ00 <= as_KSRQ00||as_KSSJ00 group by YFBMBH,YPNM00);

       elsif as_SFSF00=2 then
              insert into WZ_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 b.tzrq00 is null   and ((as_KSMC00='9999') or (a.YFBMBH=as_KSMC00)) and (a.wzsx00 in ('3','4') or a.wzsx00 is null )
            and  a.MXZLSH in (select max(MXZLSH) from KS_YPMXZ0 where
                 JZRQ00 <= as_KSRQ00 and JZRQ00||JZSJ00 <= as_KSRQ00||as_KSSJ00 group by YFBMBH,YPNM00);
         else
             insert into WZ_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 b.tzrq00 is null   and ((as_KSMC00='9999') or (a.YFBMBH=as_KSMC00))
            and  a.MXZLSH in (select max(MXZLSH) from KS_YPMXZ0 where
                 JZRQ00 <= as_KSRQ00 and JZRQ00||JZSJ00 <= as_KSRQ00||as_KSSJ00 group by YFBMBH,YPNM00);
        end if;
   else
       if as_SFSF00=1 then
         -- 期初结存
         insert into WZ_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 b.tzrq00 is null  and ((as_KSMC00='9999') or (a.YFBMBH=as_KSMC00)) and a.wzsx00 in ('0','1','2')
            and  a.MXZLSH in (select max(MXZLSH) from KS_YPMXZ0 where YFBMBH=as_KSMC00 AND
                 JZRQ00 <= as_KSRQ00 and JZRQ00||JZSJ00 <= as_KSRQ00||as_KSSJ00 group by YPNM00);

       elsif as_SFSF00=2 then
              insert into WZ_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 b.tzrq00 is null  and ((as_KSMC00='9999') or (a.YFBMBH=as_KSMC00)) and (a.wzsx00 in ('3','4') or a.wzsx00 is null )
            and  a.MXZLSH in (select max(MXZLSH) from KS_YPMXZ0 where YFBMBH=as_KSMC00 AND
                 JZRQ00 <= as_KSRQ00 and JZRQ00||JZSJ00 <= as_KSRQ00||as_KSSJ00 group by YPNM00);
         else
             insert into WZ_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 b.tzrq00 is null  and ((as_KSMC00='9999') or (a.YFBMBH=as_KSMC00))
            and  a.MXZLSH in (select max(MXZLSH) from KS_YPMXZ0 where  YFBMBH=as_KSMC00 AND
                 JZRQ00 <= as_KSRQ00 and JZRQ00||JZSJ00 <= as_KSRQ00||as_KSSJ00 group by YPNM00);
        end if;
    end if;

    IF as_SFSF00=1 then
     -- 本期增加
     insert into WZ_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 b.tzrq00 is null  and ((as_KSMC00='9999') or (a.YFBMBH=as_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  as_SFSF00=2 then
          insert into WZ_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 b.tzrq00 is null   and ((as_KSMC00='9999') or (a.YFBMBH=as_KSMC00))and (a.wzsx00 in ('3','4') 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 WZ_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 b.tzrq00 is null  and ((as_KSMC00='9999') or (a.YFBMBH=as_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 as_SFSF00=1 then
     -- 物资入库
     insert into WZ_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 b.tzrq00 is null  and ((as_KSMC00='9999') or (a.YFBMBH=as_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  as_SFSF00=2 then
          insert into WZ_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 b.tzrq00 is null  and ((as_KSMC00='9999') or (a.YFBMBH=as_KSMC00))and (a.wzsx00 in ('3','4') 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 WZ_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 b.tzrq00 is null  and ((as_KSMC00='9999') or (a.YFBMBH=as_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 as_SFSF00=1 then
     -- 供应室入库
     insert into WZ_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 b.tzrq00 is null  and ((as_KSMC00='9999') or (a.YFBMBH=as_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  as_SFSF00=2 then
          insert into WZ_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 b.tzrq00 is null  and ((as_KSMC00='9999') or (a.YFBMBH=as_KSMC00))and (a.wzsx00 in ('3','4') 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 WZ_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 b.tzrq00 is null  and ((as_KSMC00='9999') or (a.YFBMBH=as_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 as_SFSF00=1 then
     -- 其他入库
     insert into WZ_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 b.tzrq00 is null  and ((as_KSMC00='9999') or (a.YFBMBH=as_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  as_SFSF00=2 then
          insert into WZ_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 b.tzrq00 is null  and ((as_KSMC00='9999') or (a.YFBMBH=as_KSMC00))and (a.wzsx00 in ('3','4') 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 WZ_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 b.tzrq00 is null  and ((as_KSMC00='9999') or (a.YFBMBH=as_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 as_SFSF00=1 then
      -- 本期减少
      insert into WZ_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 b.tzrq00 is null  and ((as_KSMC00='9999') or (a.YFBMBH=as_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  as_SFSF00=2 then
             insert into WZ_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 b.tzrq00 is null  and ((as_KSMC00='9999') or (a.YFBMBH=as_KSMC00)) and (a.wzsx00 in ('3','4') 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 WZ_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 b.tzrq00 is null  and ((as_KSMC00='9999') or (a.YFBMBH=as_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 as_SFSF00=1 then
      -- 处方出库
      insert into WZ_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 b.tzrq00 is null  and ((as_KSMC00='9999') or (a.YFBMBH=as_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  as_SFSF00=2 then
             insert into WZ_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 b.tzrq00 is null  and ((as_KSMC00='9999') or (a.YFBMBH=as_KSMC00)) and (a.wzsx00 in ('3','4') 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 WZ_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 b.tzrq00 is null  and ((as_KSMC00='9999') or (a.YFBMBH=as_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 as_SFSF00=1 then
      -- 其他出库
      insert into WZ_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 b.tzrq00 is null  and ((as_KSMC00='9999') or (a.YFBMBH=as_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  as_SFSF00=2 then
             insert into WZ_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 b.tzrq00 is null  and ((as_KSMC00='9999') or (a.YFBMBH=as_KSMC00)) and (a.wzsx00 in ('3','4') 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 WZ_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 b.tzrq00 is null  and ((as_KSMC00='9999') or (a.YFBMBH=as_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 as_KSMC00='9999' then
         if as_SFSF00=1 then
          -- 本期结存
         insert into WZ_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 b.tzrq00 is null  and a.wzsx00 in ('0','1','2')
            and  a.MXZLSH in (select max(MXZLSH) from KS_YPMXZ0 where
                 JZRQ00 <= as_JSRQ00 and JZRQ00||JZSJ00 <= as_JSRQ00||as_JSSJ00 group by YFBMBH,YPNM00);
            elsif  as_SFSF00=2 then
              insert into WZ_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 b.tzrq00 is null  and (a.wzsx00 in ('3','4') or a.wzsx00 is null )
            and  a.MXZLSH in (select max(MXZLSH) from KS_YPMXZ0 where
                 JZRQ00 <= as_JSRQ00 and JZRQ00||JZSJ00 <= as_JSRQ00||as_JSSJ00 group by YFBMBH,YPNM00);
             else
                  insert into WZ_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 b.tzrq00 is null  and ((as_KSMC00='9999') or (a.YFBMBH=as_KSMC00))
            and  a.MXZLSH in (select max(MXZLSH) from KS_YPMXZ0 where
                 JZRQ00 <= as_JSRQ00 and JZRQ00||JZSJ00 <= as_JSRQ00||as_JSSJ00 group by YFBMBH,YPNM00);
           end if ;
      else
          if as_SFSF00=1 then
          -- 本期结存
         insert into WZ_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 b.tzrq00 is null  and ((as_KSMC00='9999') or (a.YFBMBH=as_KSMC00)) and a.wzsx00 in ('0','1','2')
            and  a.MXZLSH in (select max(MXZLSH) from KS_YPMXZ0 where YFBMBH=as_KSMC00 AND
                 JZRQ00 <= as_JSRQ00 and JZRQ00||JZSJ00 <= as_JSRQ00||as_JSSJ00 group by YPNM00);
            elsif  as_SFSF00=2 then
              insert into WZ_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 b.tzrq00 is null  and ((as_KSMC00='9999') or (a.YFBMBH=as_KSMC00)) and (a.wzsx00 in ('3','4') or a.wzsx00 is null )
            and  a.MXZLSH in (select max(MXZLSH) from KS_YPMXZ0 where YFBMBH=as_KSMC00 AND
                 JZRQ00 <= as_JSRQ00 and JZRQ00||JZSJ00 <= as_JSRQ00||as_JSSJ00 group by YPNM00);
             else
                  insert into WZ_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 b.tzrq00 is null  and ((as_KSMC00='9999') or (a.YFBMBH=as_KSMC00))
            and  a.MXZLSH in (select max(MXZLSH) from KS_YPMXZ0 where YFBMBH=as_KSMC00 AND
                 JZRQ00 <= as_JSRQ00 and JZRQ00||JZSJ00 <= as_JSRQ00||as_JSSJ00 group by YPNM00);
           end if ;
     end if;


      -- 生成报表 GYS000改成XZZD01
     Insert into WZ_TEMP00(TJID00,PH0000,TEMP00,SDJ000,SZJSL0,ZJSL00,XZZD01,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 WZ_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 WZ_TEMP00(TJID00,PH0000,TEMP00,SDJ000,SZJSL0,ZJSL00,XZZD01,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 WZ_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 WZ_TEMP00(TJID00,PH0000,TEMP00,SDJ000,SZJSL0,ZJSL00,XZZD01,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 WZ_TEMP00
           where TJID00=ad_ID0000 and TJPX00 in (0,1,2,3,4,5,6,7,8);

     delete from WZ_TEMP00 where TJID00=ad_ID0000 and TJPX00 in (0,1,2,3,4,5,6,7,8);
     commit;
exception
   when others then
     as_YHMSG0:='数据库错误：出错原因未知';
     as_SYSMSG:=substr(SQLERRM||'执行存储过程错误:SP_KS_ZJHZ00',1,150);
     rollback;
end;