Oracle包和包体

我就是我 2022-08-12 16:19 163阅读 0赞

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;