Oracle包和包体

create or replace package PKG_TCAS_DEPOT_CORE_CALC is

  
  --计提月份
  g_commis_month date;
  
  
  /*
  *分摊计算任务
  */
  procedure stp_commission_calc_task;
  
  
  
  /*
  *网点总计提奖金计算
  */
  procedure stp_dept_commission_calc;
  
  
  
  /*
  *仓管员计提奖金计算
  */
  procedure stp_depot_commission_calc;
  
  
  
  /*
  *计算转换仓管员的考勤信息（开始计提月份、是否参与计提、是否折算、转换后考勤数）
  */
  procedure stp_convert_emp_worktime;
  
  
  
  /*
  *更新网点计提奖金表的总考勤数
  */
  procedure stp_update_deptcommis_worktime;
  
  
  
  /*
  *获取网点的票数单价和重量单价
  *p_dept_code: 网点代码
  *p_area_code: 地区代码
  *p_price_type_code: 单价类型
  *p_quantity_price: 票数单价
  *p_weight_price: 重量单价
  */
  procedure stp_get_dept_price(p_dept_code       in  tm_tcas_depot_price.dept_code%type, 
                               p_area_code       out tm_tcas_depot_price_type.area_code%type, 
                               p_price_type_code out tm_tcas_depot_price_type.price_type_code%type,
                               p_quantity_price  out tm_tcas_depot_price_type.price_ballot_qty%type, 
                               p_weight_price    out tm_tcas_depot_price_type.price_weight_qty%type);
  
  
  
  /*
  *获取网点当前计提月份的总考勤数
  *p_dept_code: 网点代码
  */
  function get_dept_worktime(p_dept_code in tt_tcas_depotpbs_emp_info.dept_code%type) return number;
  
  
  
  /*
  *获取某个网点当前计提月份的总奖金
  *p_dept_code: 网点代码
  */
  function get_dept_commission(p_dept_code in tt_tcas_depotpbs_emp_info.dept_code%type) return number;
  
  
  
  /*
  *获取某员工当前计提月份在某个网点的总考勤数(已转计提）
  *p_emp_code: 员工工号
  *p_dept_code: 网点代码
  */
  function get_depot_worktime_by_dept(p_emp_code  in tt_tcas_depotpbs_emp_info.emp_no%type,
                                      p_dept_code in tt_tcas_depotpbs_emp_info.dept_code%type) return number;
                                      
                                      

  /*
  *获取当前计提月份（系统当前月份的上一月）
  */
  function get_commission_month return date;
  
  
  
  /*
  *传递仓管员当前计提月份的奖金汇总数据
  */
  procedure stp_pass_depot_commission;
  
  
  
  /*
  *网点计提分摊计算入口
  */
  procedure stp_main;
  

end PKG_TCAS_DEPOT_CORE_CALC;

create or replace package body pkg_tcas_depot_core_calc is 

  /*
  *分摊计算任务
  */
  procedure stp_commission_calc_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_depot_core_calc',
                                      'stp_dept_commission_calc',
                                      sysdate,
                                      null,
                                      null,
                                      'start',
                                      0,
                                      l_call_no);
                
        stp_dept_commission_calc;
                
        --记录结束时间
        pkg_tcas_comm.stp_running_log('pkg_tcas_depot_core_calc',
                                      'stp_dept_commission_calc',
                                      sysdate,
                                      null,
                                      null,
                                      'end',
                                      0,
                                      l_call_no);
      end;
      
    
      begin 
        --得到序列号
        select seq_tcas_running_log.nextval into l_call_no from dual;
        --记录开始时间
        pkg_tcas_comm.stp_running_log('pkg_tcas_depot_core_calc',
                                      'stp_depot_commission_calc',
                                      sysdate,
                                      null,
                                      null,
                                      'start',
                                      0,
                                      l_call_no);
                                            
        stp_depot_commission_calc;
              
        --记录结束时间
        pkg_tcas_comm.stp_running_log('pkg_tcas_depot_core_calc',
                                      'stp_depot_commission_calc',
                                      sysdate,
                                      null,
                                      null,
                                      'end',
                                      0,
                                      l_call_no);
      end;
      

      begin 
        --得到序列号
        select seq_tcas_running_log.nextval into l_call_no from dual;
        --记录开始时间
        pkg_tcas_comm.stp_running_log('pkg_tcas_depot_core_calc',
                                      'stp_update_deptcommis_worktime',
                                      sysdate,
                                      null,
                                      null,
                                      'start',
                                      0,
                                      l_call_no);
                                              
        stp_update_deptcommis_worktime;
              
              
        --记录结束时间
        pkg_tcas_comm.stp_running_log('pkg_tcas_depot_core_calc',
                                      'stp_update_deptcommis_worktime',
                                      sysdate,
                                      null,
                                      null,
                                      'end',
                                      0,
                                      l_call_no);
      end;
      

      begin 
        --得到序列号
        select seq_tcas_running_log.nextval into l_call_no from dual;
        --记录开始时间
        pkg_tcas_comm.stp_running_log('pkg_tcas_depot_core_calc',
                                      'stp_pass_depot_commission',
                                      sysdate,
                                      null,
                                      null,
                                      'start',
                                      0,
                                      l_call_no);
                                              
        stp_pass_depot_commission;
              
              
        --记录结束时间
        pkg_tcas_comm.stp_running_log('pkg_tcas_depot_core_calc',
                                      'stp_pass_depot_commission',
                                      sysdate,
                                      null,
                                      null,
                                      'end',
                                      0,
                                      l_call_no);
      end;
      
    
  end stp_commission_calc_task;



  /*
  *网点总计提奖金计算
  */
  procedure stp_dept_commission_calc is 
  
      --地区代码
      l_area_code      tm_department.area_code%type := null;
      
      --单价
      l_price_type_code tm_tcas_depot_price_type.price_type_code%type  := null;
      l_quantity_price  tm_tcas_depot_price_type.price_ballot_qty%type := null;
      l_weight_price    tm_tcas_depot_price_type.price_weight_qty%type := null;
      
      --网点总计提奖金
      l_dept_money     tr_tcas_depot_commission.dept_commis_money%type := 0;
      
      --提示信息
      l_prompt_msg     tr_tcas_depot_commission.prompt_message%type := null;
      
      cursor c_query_dept_amount(commission_month_string ti_fact_to_ecastcas.month_code%type) is 
      select dept_code, tickets, weight 
      from ti_fact_to_ecastcas t 
      where t.month_code = commission_month_string 
      and not exists(select 1 
                     from tt_tcas_ignore_dept_type i 
                     where i.type_code = (select type_code 
                                          from tm_department d 
                                          where d.dept_code = t.dept_code 
                                          and rownum<=1));
      
  begin
      
      --删除当前计提月份已存在的数据
      delete from tr_tcas_depot_commission 
      where trunc(commis_month, 'month') = g_commis_month;
      
      for amount in c_query_dept_amount(to_char(g_commis_month, 'yyyymm'))
      loop
      
          stp_get_dept_price(amount.dept_code, 
                             l_area_code, 
                             l_price_type_code, 
                             l_quantity_price, 
                             l_weight_price);
          
          --没有查询到单价配置（没有配置，或为空）
          if((l_area_code is null) 
              or (l_price_type_code is null) 
              or (l_quantity_price is null) 
              or (l_weight_price is null)) then 
            l_dept_money := null;
            l_prompt_msg := '{11}';
            
          elsif(amount.tickets is null) then 
            l_dept_money := null;
            l_prompt_msg := '{12}';
            
          elsif(amount.weight is null) then 
            l_dept_money := null;
            l_prompt_msg := '{13}';
          
          else
            l_dept_money := (l_quantity_price * amount.tickets) + 
                            (l_weight_price * amount.weight);
            l_prompt_msg := null;
          end if;
          
          insert into tr_tcas_depot_commission
            (
             commission_id,
             area_code,
             dept_code,
             commis_month,
             dept_commis_money,
             dept_work_time,
             ballot_qty,
             weight_qty,
             price_type_code,
             price_ballot_qty,
             price_weight_qty,
             created_emp_code,
             created_tm,
             modified_emp_code,
             modified_tm,
             prompt_message
            )
            values
            (
             seq_tcas_depot.nextval,
             l_area_code,
             amount.dept_code,
             g_commis_month,
             l_dept_money,
             null,
             amount.tickets,
             amount.weight,
             l_price_type_code,
             l_quantity_price,
             l_weight_price,
             'sys',
             sysdate,
             'sys',
             sysdate,
             l_prompt_msg
            );
          
      end loop;
      
      commit;

  end stp_dept_commission_calc;
  
  
  
  /*
  *仓管员计提奖金计算
  */
  procedure stp_depot_commission_calc is 
  
      --员工姓名
      l_emp_name tr_tcas_depot_commission_emp.emp_name%type;
      
      --职位名称
      l_position_name tr_tcas_depot_commission_emp.position_name%type;
      
      --职位状态
      l_position_state tr_tcas_depot_commission_emp.position_state%type;
      
      --仓管员所属组织网点
      l_emp_dept_code tr_tcas_depot_commission_emp.emp_dept_code%type;
      
      --员工当前计提月份总考勤数(已转计提）
      l_depot_worktime tr_tcas_depot_commission_emp.emp_commis_time%type := 0;
      
      --网点当前计提月份总奖金
      l_dept_commission tr_tcas_depot_commission_emp.emp_commis_money%type := 0;
      
      --仓管员当前计提月份奖金
      l_emp_commis_money tr_tcas_depot_commission_emp.emp_commis_money%type;
  
      cursor c_query_depot_schedule_trans is 
      select * 
      from (select t.emp_code,
                   t.dept_code,
                   t.person_type,
                   t.begin_commission_month,
                   (sum(t.work_time) over(partition by t.emp_code, t.dept_code)) work_time,
                   (sum(t.convert_worktime) over(partition by t.emp_code, t.dept_code)) depot_convert_work_time,
                   (sum(t.convert_worktime) over(partition by t.dept_code)) dept_worktime,
                   (row_number() over(partition by t.emp_code, t.dept_code order by t.work_date desc)) rn 
            from tt_tcas_depot_schedule_trans t 
            where trunc(t.work_date, 'month') = g_commis_month) x 
      where x.rn = 1;
      
  begin 
  
      --删除当前计提月份已存在的数据
      delete from tr_tcas_depot_commission_emp c 
      where trunc(c.commission_month, 'month') = g_commis_month;
      
      
      --计算转换仓管员的考勤信息
      stp_convert_emp_worktime;

      
      for schedule in c_query_depot_schedule_trans 
      loop 
      
        begin 
          select e.emp_name,
                 e.position_name,
                 e.position_status,
                 e.dept_code 
          into l_emp_name,
               l_position_name,
               l_position_state,
               l_emp_dept_code 
          from tm_employee e 
          where e.emp_code = schedule.emp_code and rownum=1;
          
          exception when others then
            l_emp_name := null;
            l_position_name := null;
            l_position_state := null;
            l_emp_dept_code := null;
        end;
        
        l_depot_worktime  := get_depot_worktime_by_dept(schedule.emp_code, schedule.dept_code);
        l_dept_commission := get_dept_commission(schedule.dept_code);
        
        if((schedule.dept_worktime is null) or (schedule.dept_worktime = 0)) then 
           l_emp_commis_money := 0;
        else 
           l_emp_commis_money := (l_depot_worktime/(schedule.dept_worktime))*l_dept_commission;
        end if;
        
      
        insert into tr_tcas_depot_commission_emp 
        (
          commission_emp_id,
          emp_code,
          commission_month,
          dept_code,
          emp_commis_time,
          emp_convert_time,
          emp_commis_money,
          emp_dept_code,
          emp_name,
          person_type,
          position_name,
          position_state,
          begin_commis_month,
          created_emp_code,
          created_tm,
          modified_emp_code,
          modified_tm
        )
        values 
        (
          seq_tcas_depot.nextval,
          schedule.emp_code,
          g_commis_month,
          schedule.dept_code,
          schedule.work_time,
          schedule.depot_convert_work_time,
          l_emp_commis_money,
          l_emp_dept_code,
          l_emp_name,
          schedule.person_type,
          l_position_name,
          l_position_state,
          schedule.begin_commission_month,
          'sys',
          sysdate,
          'sys',
          sysdate
        );
        
      end loop;
      
      commit;
      
  end stp_depot_commission_calc;
  
  
  
  /*
  *计算转换仓管员的考勤信息
  *（开始计提月份、是否参与计提、是否折算、转换后考勤数）
  */
  procedure stp_convert_emp_worktime is 
      
      --人员类型
      l_person_type tt_tcas_sap_hr_person_type.person_type%type := null;
      
      --开始计提月份
      l_begin_month tm_tcas_begin_commis_month.begin_commis_month%type;
      
      --是否参与计提
      l_is_begin_commis tt_tcas_depot_schedule_trans.is_begin_commission%type;
      
      --是否折算
      l_is_convert tt_tcas_depot_schedule_trans.is_convert%type;
      
      --转换后考勤数
      l_convert_worktime tt_tcas_depot_schedule_trans.convert_worktime%type;
      
      --提示信息
      l_reason tt_tcas_depot_schedule_trans.prompt_message%type;
      
      --人员类型是否包含“非全”
      l_is_contain_feiquan number := 0;
      
      cursor c_query_depot_schedule is 
      select t.depot_pbs_emp_info_id, t.emp_no, t.dept_code, t.work_date, t.work_time,
             (select e.person_type from tm_employee e where e.emp_code=t.emp_no and rownum=1) person_type 
      from tt_tcas_depotpbs_emp_info t 
      where trunc(t.work_date, 'month') = g_commis_month;
      
  begin 
  
      --删除当前计提月份已存在数据
      delete from tt_tcas_depot_schedule_trans t 
      where trunc(t.work_date, 'month') = g_commis_month;
            
      for schedule in c_query_depot_schedule 
      loop
          
        --从人员类型接口表查询人员类型
        begin
          select t.person_type 
          into l_person_type 
          from tt_tcas_sap_hr_person_type t 
          where t.emp_code = schedule.emp_no 
                and trunc(schedule.work_date) >= trunc(t.effective_date) 
                and trunc(schedule.work_date) <= trunc(t.expiry_date) 
                and rownum = 1;
          
          exception when others then
          l_person_type := null;
        end;
        
        --接口表中未查询到“人员类型”，则从员工表中取
        if(l_person_type is null) then 
           l_person_type := schedule.person_type;
        end if;

        l_is_begin_commis := 0;
        l_is_convert := 0;
        l_convert_worktime := schedule.work_time;
        l_is_contain_feiquan := 0;
        
        --查询开始计提月份
        begin 
          select begin_commis_month 
          into l_begin_month 
          from tm_tcas_begin_commis_month m 
          where m.emp_no_commis = schedule.emp_no 
                and rownum=1;
          
          exception when others then 
            l_begin_month := null;
        end;
        
        --开始计提月份未设置或未生效：不计提
        if((l_begin_month is null) or (trunc(l_begin_month, 'month')>g_commis_month)) then 
          if(l_person_type is not null) then 
          
            select instr(l_person_type, '非全', 1, 1) 
            into l_is_contain_feiquan 
            from dual;
            
            if((l_is_contain_feiquan is not null) and (l_is_contain_feiquan>0)) then 
                l_is_convert := 1;
                l_convert_worktime := ((schedule.work_time)*4)/9;
                l_reason := '{14}';
            else
                l_is_convert := 1;
                l_convert_worktime := (schedule.work_time)/2;
                l_reason := '{15}';
            end if;
          else
            l_reason := '{16}';
          end if;
        else
          if(l_person_type is not null) then 
            l_is_begin_commis := 1;
            l_reason := '{18}';
          else
            l_reason := '{19}';
          end if;
        end if;
        
        insert into tt_tcas_depot_schedule_trans
        (
          depot_schedule_translate_id,
          emp_code,
          dept_code,
          work_date,
          work_time,
          person_type,
          begin_commission_month,
          is_begin_commission,
          is_convert,
          convert_worktime,
          depot_pbs_emp_info_id,
          prompt_message,
          created_emp_code,
          created_tm
        )
        values
        (
          seq_tcas_depot.nextval,
          schedule.emp_no,
          schedule.dept_code,
          schedule.work_date,
          schedule.work_time,
          l_person_type,
          l_begin_month,
          l_is_begin_commis,
          l_is_convert,
          l_convert_worktime,
          schedule.depot_pbs_emp_info_id,
          l_reason,
          'sys',
          sysdate
        );
      
      end loop;
      
      commit;
      
  end stp_convert_emp_worktime;
  
  
  
  /*
  *更新当前计提月份网点计提奖金表的总考勤数
  */
  procedure stp_update_deptcommis_worktime is 
  begin 
  
      update tr_tcas_depot_commission c 
      set c.dept_work_time = get_dept_worktime(c.dept_code)
      where trunc(c.commis_month, 'month') = g_commis_month;
      
      commit;
      
  end stp_update_deptcommis_worktime;
  
  
  
  /*
  *获取网点的票数单价和重量单价
  *p_dept_code: 网点代码
  *p_area_code: 地区代码
  *p_price_type_code: 单价类型
  *p_quantity_price: 票数单价
  *p_weight_price: 重量单价
  */
  procedure stp_get_dept_price(p_dept_code        in  tm_tcas_depot_price.dept_code%type, 
                               p_area_code        out tm_tcas_depot_price_type.area_code%type, 
                               p_price_type_code  out tm_tcas_depot_price_type.price_type_code%type,
                               p_quantity_price   out tm_tcas_depot_price_type.price_ballot_qty%type, 
                               p_weight_price     out tm_tcas_depot_price_type.price_weight_qty%type) is 
      
  begin
  
      --查询网点所属的地区代码
      begin
        select d.area_code 
        into p_area_code 
        from tm_department d 
        where d.dept_code = p_dept_code 
              and rownum = 1;
      exception 
        when others then 
        p_area_code := null;
      end;
      
      --查询网点的单价类型
      begin
        select t.price_type_code 
        into p_price_type_code 
        from tm_tcas_depot_price t 
        where t.dept_code = p_dept_code 
              and rownum = 1;
      exception 
        when others then 
        p_price_type_code := null;
      end;
      
      --查询网点的票数单价和重量单价
      if((p_area_code is not null) and (p_price_type_code is not null)) then 
        begin 
          select x.price_ballot_qty, x.price_weight_qty 
          into p_quantity_price, p_weight_price 
          from tm_tcas_depot_price_type x 
          where x.area_code = p_area_code 
                and x.price_type_code = p_price_type_code 
                and rownum = 1;
          
          exception 
            when others then 
            p_quantity_price := null;
            p_weight_price := null;
        end;
      else
        p_quantity_price := null;
        p_weight_price := null;
      end if;
      
  end stp_get_dept_price;
  
  
  
  /*
  *获取某个网点当前计提月份的总考勤数（折算后）
  *p_dept_code: 网点代码
  */
  function get_dept_worktime(p_dept_code in tt_tcas_depotpbs_emp_info.dept_code%type) return number is 
  
      l_dept_worktime tt_tcas_depotpbs_emp_info.work_time%type := 0;
  
  begin
      
     select nvl(sum(t.convert_worktime), 0) 
     into l_dept_worktime 
     from tt_tcas_depot_schedule_trans t 
     where t.dept_code = p_dept_code 
          and trunc(t.work_date, 'month') = g_commis_month;
          
     return l_dept_worktime;

  end get_dept_worktime;
  
  
  
  /*
  *获取某个网点当前计提月份的总奖金
  *p_dept_code: 网点代码
  */
  function get_dept_commission(p_dept_code in tt_tcas_depotpbs_emp_info.dept_code%type) return number is 
      
      l_dept_commission tr_tcas_depot_commission.dept_commis_money%type := 0;
  
  begin
      
      begin 
      
        select nvl(t.dept_commis_money, 0) 
        into l_dept_commission 
        from tr_tcas_depot_commission t 
        where t.dept_code = p_dept_code 
              and trunc(t.commis_month, 'month') = g_commis_month 
              and rownum=1;
              
        exception when others then 
          l_dept_commission := 0;
      end;
      
      return l_dept_commission;
      
  end get_dept_commission;
  
  
  
  /*
  *获取某个员工当前计提月份在某个网点的总计提考勤数(已转计提）
  *p_emp_code: 员工工号
  *p_dept_code: 网点代码
  */
  function get_depot_worktime_by_dept(p_emp_code  in tt_tcas_depotpbs_emp_info.emp_no%type,
                                      p_dept_code in tt_tcas_depotpbs_emp_info.dept_code%type) 
                                      return number is 
                                      
      l_depot_worktime tt_tcas_depot_schedule_trans.work_time%type := 0;
      
  begin
  
     select nvl(sum(t.convert_worktime), 0) 
     into l_depot_worktime 
     from tt_tcas_depot_schedule_trans t 
     where t.emp_code = p_emp_code 
          and t.dept_code = p_dept_code 
          and trunc(t.work_date, 'month') = g_commis_month
          and t.is_begin_commission = 1;
          
     return l_depot_worktime;
  
  end get_depot_worktime_by_dept;
  
  

  
  /*
  *获取当前计提月份（系统当前月份的上一月）
  */
  function get_commission_month return date is 
  begin
  
      return trunc(add_months(sysdate, -1), 'month');
      
  end get_commission_month;
  
  
  
  
  /*
  *传递仓管员当前计提月份的奖金汇总数据
  */
  procedure stp_pass_depot_commission is 
  begin 
  
      delete from tr_tcas_commission_result r 
      where r.created_emp = 'stp_pass_depot_commission' 
      and r.commission_month = to_char(g_commis_month, 'yyyy-mm-dd');
      
      insert into tr_tcas_commission_result 
      (
        result_id,
        endda,
        begda,
        betrg,
        created_tm,
        created_emp,
        commission_month,
        pernr 
       )
       (
        select 
            null,
            to_char(g_commis_month, 'yyyy-mm-dd'),
            to_char(g_commis_month, 'yyyy-mm-dd'),
            to_char(round(commis_money, 2)),
            sysdate,
            'stp_pass_depot_commission',
            to_char(g_commis_month, 'yyyy-mm-dd'),
            emp_code
         from 
         (
            select t.emp_code, sum(t.emp_commis_money) commis_money 
            from tr_tcas_depot_commission_emp t 
            where trunc(t.commission_month) = g_commis_month 
            group by t.emp_code
         )
       );
       
       commit;
  
  end stp_pass_depot_commission;
  



  /*
  *网点计提分摊计算入口
  */
  procedure stp_main is 
    l_main_call_no number;
    l_job_name varchar2(40) := 'STP_DEPOT_CORE_CALC';
    l_runnable number := 0;
  begin
  
    g_commis_month := get_commission_month;
  
    --得到序列号
    select seq_tcas_running_log.nextval into l_main_call_no from dual;
    
    --记录开始时间
    pkg_tcas_comm.stp_running_log('pkg_tcas_depot_core_calc',
                                  '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_commission_calc_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_depot_core_calc',
                                  '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_depot_core_calc',
                                    'stp_main',
                                    sysdate,
                                    sqlcode,
                                    sqlerrm,
                                    'error',
                                    0,
                                    l_main_call_no);
      commit;
      
  end stp_main;
  

end pkg_tcas_depot_core_calc;