存储过程统计计算

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;