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;