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;
太过爱你忘了你带给我的痛
我就是我
痛定思痛。
向右看齐
还没有评论，来说两句吧...