create or replace procedure up_mid_66880002(v_rq number, --计算日期
rc1 out bssys.tp_cursor,
rc2 out bssys.tp_cursor)
as
nb_rq number;
nb_ExecTime date;
check_rq date;
nb_rows number(10);
v_status int;
v_msg varchar2(200);
begin
nb_ExecTime := sysdate;
--当处理日期为空时,取系统日期作为处理日期
if v_rq is null then
nb_rq := to_number(to_char(sysdate, 'yyyymmdd'));
else
nb_rq := v_rq;
end if;
--校验日期格式
begin
select to_date(nb_rq, 'yyyymmdd') into check_rq from dual;
exception
when others then
v_status := -2;
v_msg := '日期格式错误';
open rc1 for
select v_status as errorcode, v_msg as errormsg from dual;
return;
end;
--根据员工角色、员工角色分配查询出所有“工作平台坐席人员”、“质检人员”
--初始化坐席统计汇总表数据
/*merge into t_zxtjhz a
using(
select t2.ygbh, decode(t3.jsbh,4,1,30,1,409,2,1) as mtlx
from t_yg t1, t_yg_ygjsfp t2, t_yg_js t3
where t1.ygbh = t2.ygbh and t2.jsbh = t3.jsbh and t1.ygzt = 1
and t2.zt = 1 and (t3.jsbh = 4 or t3.jsbh = 40)
) b
on (a.rq = nb_rq and a.ygbh = b.ygbh and a.mtlx = b.mtlx)
when matched then update set
zxsc = 0,hrcs = 0,hrsc = 0,pjhrsc = 0,hccs = 0,hcsc = 0,pjhcsc = 0,fwcs = 0,fwsc = 0,
pjfwsc = 0,zjs = 0,zjsc = 0,fds = 0,pxs = 0,pjdf = 0,sscgs = 0,cjbhg = 0,bzjs = 0,bzjpjf = 0
when not matched then insert(a.rq, a.ygbh, a.mtlx) values(nb_rq, b.ygbh, b.mtlx);
nb_rows := sql%rowcount ;
commit;
p_cc_log(nb_rq, 'up_mid_66880002', nb_ExecTime, sysdate, '0', nb_rows,'0-初始化计算员工成功!');*/
--1.计算在线时长(只统计了1:话务,2:全媒体,91:UC签出,21%:话务签出)
nb_ExecTime := sysdate;
merge into t_zxtjhz a
using (select t1.ygbh, sum(t1.hs) zxsc, 1 as mtlx
from t_hwczlsk t1
where t1.rq = nb_rq
and t1.czlx like '21%'
and t1.sfcg &