存储过程统计计算

旧城等待， 2022-09-21 05:40 141阅读 0赞

create or replace package body pkg_tcas_hk_team_bonus is
      /*
      *计算任务
      */
      procedure stp_calculate_task is
    
          l_call_no number;
    
      begin
    
          begin
          
            --获取序列号
            select seq_tcas_running_log.nextval into l_call_no from dual;
            
            --记录开始时间
            pkg_tcas_comm.stp_running_log('pkg_tcas_hk_team_bonus',
                                          'stp_team_calculate',
                                          sysdate,
                                          null,
                                          null,
                                          'start',
                                          0,
                                          l_call_no);
    
            stp_team_calculate;
    
            --记录结束时间
            pkg_tcas_comm.stp_running_log('pkg_tcas_hk_team_bonus',
                                          'stp_team_calculate',
                                          sysdate,
                                          null,
                                          null,
                                          'end',
                                          0,
                                          l_call_no);
          end;
    
    
      end stp_calculate_task;
    
    
    
      /*
      *团队计提奖金计算
      */
      procedure stp_team_calculate is 
    
      begin
    
          --删除已存在的数据
          delete from tr_tcas_hk_team_bonus b 
          where trunc(b.work_month, 'month') = g_commission_month;
          
          insert into tr_tcas_hk_team_bonus
          (
            id,
            emp_code,
            division_code,
            position_name,
            work_month,
            work_time,
            team_bonus,
            division_team_bonus,
            created_emp_code,
            created_tm,
            modified_emp_code,
            modified_tm
          )
          select seq_tcas_hk_report.nextval,
                 t.emp_code,
                 t.division_code,
                 t.position_name,
                 g_commission_month,
                 t.work_time,
                 (case when t.division_work_time=0 then 0 
                       else t.division_commission/t.division_work_time end),
                 (case when t.division_work_time=0 then 0 
                       else t.division_commission/t.division_work_time end),
                 'stp_team_calculate',
                 sysdate,
                 'stp_team_calculate',
                 sysdate 
          from (select z.emp_code,
                       z.division_code,
                       z.position_name,
                       z.work_time,
                       sum(z.personal_commission) over(partition by z.division_code) division_commission,
                       sum(z.coefficient) over(partition by z.division_code) division_work_time 
              from (select y.emp_code,
                          (y.collect_ticket*y.collect_price +
                           y.collect_weight*y.collect_weight_price +
                           y.send_ticket*y.send_price +
                           y.send_weight*y.send_weight_price) personal_commission,
                           y.work_time,
                           (y.work_time/(y.work_time + y.leave_time)) coefficient,
                           y.division_code,
                           y.position_name 
                    from (select x.emp_code,
                                 (case when p.is_person_bonus=0 then x.collect_ticket else x.collect_ticket/2 end) collect_ticket,
                                 (case when p.is_person_bonus=0 then x.collect_weight else x.collect_weight/2 end) collect_weight,
                                 (case when p.is_person_bonus=0 then x.send_ticket else x.send_ticket/2 end) send_ticket,
                                 (case when p.is_person_bonus=0 then x.send_weight else x.send_weight/2 end) send_weight,
                                 p.collect_price,
                                 p.collect_weight_price,
                                 p.send_price,
                                 p.send_weight_price,
                                 nvl(s.work_time, 0) work_time,
                                 nvl(s.leave_time, 0) leave_time,
                                 s.division_code,
                                 s.position_name 
                          from (select q.emp_code,
                                   sum(q.collect_ticket) collect_ticket,
                                   sum(q.collect_weight) collect_weight,
                                   sum(q.send_ticket) send_ticket,
                                   sum(q.send_weight) send_weight 
                                from tt_tcas_hk_qty q 
                                where trunc(q.work_date, 'month') = g_commission_month 
                                group by q.emp_code) x 
                                inner join tm_tcas_hk_price_config p on x.emp_code = p.emp_code and p.is_team_bonus = 1 and trunc(p.effective_date, 'month')<=g_commission_month 
                                inner join tt_tcas_hk_schedule s on x.emp_code = s.emp_code and trunc(s.work_month, 'month')=g_commission_month) y) z) t;
          
          
          for emp in(select b.emp_code,
                            p.effective_date,
                            s.leave_time,
                            s.is_last_day_leave 
                     from tr_tcas_hk_team_bonus b 
                     inner join tm_tcas_hk_price_config p on b.emp_code = p.emp_code 
                     inner join tt_tcas_hk_schedule s on b.emp_code = s.emp_code 
                     where trunc(b.work_month, 'month') = g_commission_month 
                       and trunc(s.work_month, 'month') = g_commission_month)
          loop 
          
              if emp.leave_time >= 2 and emp.leave_time < 4 then 
              
                  update tr_tcas_hk_team_bonus a 
                  set a.team_bonus = a.team_bonus/2 
                  where a.emp_code = emp.emp_code 
                    and trunc(a.work_month, 'month') = g_commission_month;
                    
              end if;
              
              if emp.is_last_day_leave = 1 
                 or emp.leave_time >= 4 
                 or trunc(emp.effective_date) > g_commission_month then 
                  
                 update tr_tcas_hk_team_bonus d 
                  set d.team_bonus = 0 
                  where d.emp_code = emp.emp_code 
                    and trunc(d.work_month, 'month') = g_commission_month;
                    
              end if;
          
          end loop;
          
          commit;
    
      end stp_team_calculate;
    
    
    
      /*
      *获取默认计算月份（当前月的上一个月）
      */
      function get_default_month return date is
      begin
      
        return trunc(add_months(sysdate, -1), 'month');
       
      end get_default_month;
    
    
    
      /*
      *港澳地区团队计提算入口
      */
      procedure stp_main(p_commission_month in date) is
      
        --主调方法序列号
        l_main_call_no number;
        
        --任务名称
        l_job_name varchar2(40) := 'tcas_hk_team_bonus';
        
        --运行状态
        l_runnable number := 0;
        
      begin
    
        if(p_commission_month is null) then
        
          g_commission_month := get_default_month;
          
        else
        
          g_commission_month := trunc(p_commission_month, 'month');
          
        end if;
    
        --得到序列号
        select seq_tcas_running_log.nextval into l_main_call_no from dual;
    
        --记录开始时间
        pkg_tcas_comm.stp_running_log('pkg_tcas_hk_team_bonus',
                                      'stp_main',
                                      sysdate,
                                      null,
                                      null,
                                      'start',
                                      0,
                                      l_main_call_no);
    
    
        pkg_tcas_comm.stp_start_task(l_job_name, l_runnable);
    
        if(l_runnable <> 0) then
    
          stp_calculate_task;
    
          --是否成功完成(1=成功完成,0=没有完成)
          pkg_tcas_comm.stp_end_task(l_job_name, null, 1, l_runnable);
    
        end if;
    
    
        --记录结束时间
        pkg_tcas_comm.stp_running_log('pkg_tcas_hk_team_bonus',
                                      'stp_main',
                                      sysdate,
                                      null,
                                      null,
                                      'end',
                                      0,
                                      l_main_call_no);
    
    
        exception when others then
        
          --回滚事务
          rollback;
          
          --是否成功完成(1=成功完成, 0=没有完成)
          pkg_tcas_comm.stp_end_task(l_job_name, null, 0, l_runnable);
          
          --记录错误日志
          pkg_tcas_comm.stp_running_log('pkg_tcas_hk_team_bonus',
                                        'stp_main',
                                        sysdate,
                                        sqlcode,
                                        sqlerrm,
                                        'error',
                                        0,
                                        l_main_call_no);
          commit;
    
      end stp_main;
    
    
    
    end pkg_tcas_hk_team_bonus;