快意报表存储过程标准写法

create or replace procedure callbackAgentOutBound_proc(
temp_e_startDate varchar2,--执行开始时间
temp_e_endDate varchar2,--执行结束时间
temp_s_startDate varchar2,--分配开始时间
temp_s_endDate varchar2,--分配结束时间
temp_act varchar2,--活动(ID,可多选)
temp_org varchar2,--营业部(编号,可多选)
temp_userId varchar2,--坐席(编号,可多选)
p_user_id varchar2, --登录用户
temp_user_org varchar2, --座席部门
act_group varchar2, --分组类别<0默认人员分组，1活动分组>
report_cur_type out report_cur.report_cursor_type)
as
/**********************************************************/
/*功能：座席回访记录汇总表 */
/*版本：4.0.0.0 */
/*关联版本： */
/*创建人:wangkx */
/*创建时间：2012-07-30 */
/*
主要表:asv_tsk_detail_agent,
涉及其他表：act_list,tsys_user,asv_tsk_execute,
数据库:pms_1024,
服务器IP:192.168.72.35
V4.0.0.1 2013-05-09 modify wangxn: 系统出基线包
V4.0.0.4 2013-03-25 modify liusm: [M201401160047]增加数据权限
V4.0.0.5 2014-07-18 modify lumin11193: 将任务状态,外呼状态修改成可扩展
V4.0.3.7 2016-09-28 modify huwy18730: 增加查询条件（act_group为1按活动分组，act_group为0按座席分组，默认0）
*/

sqlCall varchar2(16000);
e_startDate varchar2(50);
e_endDate varchar2(50);
s_startDate varchar2(50);
s_endDate varchar2(50);
userId varchar2(2000);
act varchar(2000);
e_betweenTime varchar2(1000):= ' ';
s_betweenTime varchar2(1000):=' ';
str_split varchar2(5) := ',';
act_condition varchar2(4000):=' ';
org_condition varchar2(10000):=' ';
userorg_condition varchar2(10000):=' ';
user_condition varchar2(10000):=' ';
tableA varchar2(8000):=' ';
tableB varchar2(8000):=' ';
tableC varchar2(8000):=' ';
v_param_value varchar2(30000) := ' ';

v_user_auth_str varchar2(1000) := '';
v_cust_auth_str varchar2(1000) := '';
v_auth_sqlB varchar2(1000) := '';
v_auth_sqlA varchar2(1000) := '';
v_auth_sqlC varchar2(1000) := '';

/******************************************************/
begin
--记录传入参数
v_param_value := '@temp_user_org='||nvl(temp_user_org,'')||'@p_user_id='||nvl(p_user_id,'') ||'@temp_e_startDate=' || nvl(temp_e_startDate,'') || '@temp_e_endDate=' || nvl(temp_e_endDate,'') || '@temp_s_startDate=' || nvl(temp_s_startDate,'')
|| '@temp_s_endDate=' || nvl(temp_s_endDate,'') || '@temp_act=' || nvl(temp_act,'') || '@temp_org=' || nvl(temp_org,'')|| '@temp_userId=' || nvl(temp_userId,'')||'@act_group='||nvl(act_group,'');
LOG_RP_PROC(v_param_value,'','','callbackAgentOutBound_V4.0.2.3','callbackAgentOutBound_proc: 座席回访记录汇总-表输入参数');

--按活动分组需要显示活动名称
if act_group='1' then
tableA := 'select '''' user_name,a.act_id,a.act_name,
nvl((select dic.dict_item_name from tsys_dict_item dic where dic.dict_entry_code = ''ASV_ACT_RESULT'' and dic.dict_item_code = atda.asv_act_result),''未执行'')status,
count(1) cou,
''act_result'' act_result
from asv_tsk_detail_agent atda,act_list a where atda.act_id = a.act_id ';

tableB := 'select '''' user_name,aa.act_id,aa.act_name,
(select dic.dict_item_name from tsys_dict_item dic where dic.dict_entry_code = ''ASV_TSK_STATUS'' and dic.dict_item_code = atdas.asv_tsk_status) status,
count(1) cou,
''tsk_status'' act_result
from asv_tsk_detail_agent atdas,act_list aa where atdas.act_id = aa.act_id ';

tableC :='select '''' user_name,ac.act_id,ac.act_name,
atdac.asv_tsk_status status,
count(1) cou,
''be_overdue'' act_result
from asv_tsk_detail_agent atdac, act_list ac, act_part_member apm
where atdac.act_id = ac.act_id
and atdac.act_tsk_id = apm.act_auto_id(+)
and apm.hope_end_time != 0 and trim(apm.hope_end_time) is not null
and ((rpad(apm.hope_end_time, 14, 9) < rpad(atdac.act_tsk_execute_dt, 14, 0) )
or (atdac.act_tsk_execute_dt = 0 and to_number(to_char(sysdate,''yyyyMMddhhmiss'')) > rpad(apm.hope_end_time, 14, 9))
) ';
else
tableA := 'select atda.act_tsk_execute_id,'''' as act_id,'''' as act_name,
nvl((select dic.dict_item_name from tsys_dict_item dic where dic.dict_entry_code = ''ASV_ACT_RESULT'' and dic.dict_item_code = atda.asv_act_result),''未执行'')status,
count(1) cou,
''act_result'' act_result
from asv_tsk_detail_agent atda,act_list a where atda.act_id = a.act_id ';

tableB := 'select atdas.act_tsk_execute_id,'''' as act_id,'''' as act_name,
(select dic.dict_item_name from tsys_dict_item dic where dic.dict_entry_code = ''ASV_TSK_STATUS'' and dic.dict_item_code = atdas.asv_tsk_status) status,
count(1) cou,
''tsk_status'' act_result
from asv_tsk_detail_agent atdas,act_list aa where atdas.act_id = aa.act_id ';

tableC :='select atdac.act_tsk_execute_id,'''' as act_id,'''' as act_name,
atdac.asv_tsk_status status,
count(1) cou,
''be_overdue'' act_result
from asv_tsk_detail_agent atdac, act_list ac, act_part_member apm
where atdac.act_id = ac.act_id
and atdac.act_tsk_id = apm.act_auto_id(+)
and apm.hope_end_time != 0 and trim(apm.hope_end_time) is not null
and ((rpad(apm.hope_end_time, 14, 9) < rpad(atdac.act_tsk_execute_dt, 14, 0) )
or (atdac.act_tsk_execute_dt = 0 and to_number(to_char(sysdate,''yyyyMMddhhmiss'')) > rpad(apm.hope_end_time, 14, 9))
) ';
end if;

--执行时间 表字段：asv_tsk_detail_agent.act_tsk_execute_dt
if temp_e_startDate is not null then
e_startDate :=to_char(to_date(temp_e_startDate,'yyyy-mm-dd'),'yyyymmdd');
tableA:=tableA||' and atda.act_tsk_execute_dt >= to_number('''|| RPad( e_startDate, 12,'0') ||''')';
tableB:=tableB||' and atdas.act_tsk_execute_dt >= to_number('''|| RPad( e_startDate, 12,'0') ||''')';
tableC:=tableC||' and atdac.act_tsk_execute_dt >= to_number('''|| RPad( e_startDate, 12,'0') ||''')';
end if;

if temp_e_endDate is not null then
e_endDate := to_char(to_date(temp_e_endDate,'yyyy-mm-dd'),'yyyymmdd');
tableA:=tableA||' and atda.act_tsk_execute_dt <= to_number('''|| RPad(e_endDate,12,'9') || ''')';
tableB:=tableB||' and atdas.act_tsk_execute_dt <= to_number('''|| RPad(e_endDate,12,'9') || ''')';
tableC:=tableC||' and atdac.act_tsk_execute_dt <= to_number('''|| RPad(e_endDate,12,'9') || ''')';
end if;

--分配时间 表字段：asv_tsk_detail_agent.act_tsk_distribution_dt
if temp_s_startDate is not null then
s_startDate := to_char(to_date(temp_s_startDate,'yyyy-mm-dd'),'yyyymmdd');
tableA:=tableA||' and atda.act_tsk_distribution_dt >= to_number('''|| RPad( s_startDate, 12,'0') ||''')';
tableB:=tableB||' and atdas.act_tsk_distribution_dt >= to_number('''|| RPad( s_startDate, 12,'0') ||''')';
tableC:=tableC||' and atdac.act_tsk_distribution_dt >= to_number('''|| RPad( s_startDate, 12,'0') ||''')';
end if;

if temp_s_endDate is not null then
s_endDate := to_char(to_date(temp_s_endDate,'yyyy-mm-dd'),'yyyymmdd');
tableA:=tableA||' and atda.act_tsk_distribution_dt <= to_number('''||RPad(s_endDate,12,'9') || ''')';
tableB:=tableB||' and atdas.act_tsk_distribution_dt <= to_number('''||RPad(s_endDate,12,'9') || ''')';
tableC:=tableC||' and atdac.act_tsk_distribution_dt <= to_number('''||RPad(s_endDate,12,'9') || ''')';
end if;

--营业部
if temp_org is null then
org_condition:=' ';
else
tableA:=tableA||' and atda.act_customer_depart in ('''|| replace(temp_org,',',''',''') || ''')';
tableB:=tableB||' and atdas.act_customer_depart in ('''|| replace(temp_org,',',''',''') || ''')';
tableC:=tableC||' and atdac.act_customer_depart in ('''|| replace(temp_org,',',''',''') || ''')';
end if;
--活动
if temp_act is null then
act_condition:=' ';
else
tableA:=tableA||' and atda.act_id in('||temp_act || ')';
tableB:=tableB||' and atdas.act_id in('||temp_act || ')';
tableC:=tableC||' and atdac.act_id in('||temp_act || ')';
end if;

--座席
if temp_userId is null then
user_condition:=' ';
else
userId := trim(both ',' from temp_userId);
tableA:=tableA||' and atda.act_tsk_execute_id in('''|| replace(userId,',',''',''') || ''')';
tableB:=tableB||' and atdas.act_tsk_execute_id in('''|| replace(userId,',',''',''') || ''')';
tableC:=tableC||' and atdac.act_tsk_execute_id in('''|| replace(userId,',',''',''') || ''')';
end if;
--座席部门
if temp_user_org is null then
userorg_condition:=' ';
else
userorg_condition := 'select user_id from tsys_user where org_id in ('''|| replace(temp_user_org,',',''',''') || ''')';
tableA:=tableA||' and atda.act_tsk_execute_id in('||userorg_condition||')';
tableB:=tableB||' and atdas.act_tsk_execute_id in('||userorg_condition||')';
tableC:=tableC||' and atdac.act_tsk_execute_id in('||userorg_condition||')';
end if;

-- 数据权限
v_user_auth_str := pkg_sys_dataright.fn_sys_getAuthUsers(p_user_id,'ASV');
if v_user_auth_str <> 'ALL_RIGHT' then
--v_auth_sqlA := ' and (a.act_create_id in ( ' || v_user_auth_str || ' ) or ( a.act_id in ( select act_id from act_execute_department where dep_code = ( select org_id from tsys_user where user_id = ''' || p_user_id || ''' ) ) ) )';
--v_auth_sqlB := ' and (aa.act_create_id in ( ' || v_user_auth_str || ' ) or ( aa.act_id in ( select act_id from act_execute_department where dep_code = ( select org_id from tsys_user where user_id = ''' || p_user_id || ''' ) ) ) )';
v_auth_sqlA := ' and atda.act_tsk_execute_id in (' ||v_user_auth_str || ')';
v_auth_sqlB := ' and atdas.act_tsk_execute_id in (' ||v_user_auth_str || ')';
v_auth_sqlC := ' and atdac.act_tsk_execute_id in (' ||v_user_auth_str || ')';
end if;

v_cust_auth_str := pkg_sys_dataright.fn_sys_getAuthBranchs(p_user_id,'CM_CUSTOMER');

if v_cust_auth_str <> 'ALL_RIGHT' then
v_auth_sqlA := v_auth_sqlA || ' and atda.act_customer_depart in (' || v_cust_auth_str || ')';
v_auth_sqlB := v_auth_sqlB || ' and atdas.act_customer_depart in (' || v_cust_auth_str || ')';
v_auth_sqlC := v_auth_sqlC || ' and atdac.act_customer_depart in (' || v_cust_auth_str || ')';
end if;

tableA := tableA || v_auth_sqlA;
tableB := tableB || v_auth_sqlB;
tableC := tableC || v_auth_sqlC;

--增加分组（act_group为1时，分组条件增加按活动分组）
if act_group='1' then
tableA := tableA||' group by a.act_id,a.act_name, atda.asv_act_result';
tableB := tableB||' group by aa.act_id,aa.act_name, atdas.asv_tsk_status';
tableC := tableC||' group by atdac.act_tsk_execute_id,ac.act_id,ac.act_name, atdac.asv_tsk_status';
else
tableA := tableA||' group by atda.act_tsk_execute_id, atda.asv_act_result';
tableB := tableB||' group by atdas.act_tsk_execute_id, atdas.asv_tsk_status';
tableC := tableC||' group by atdac.act_tsk_execute_id, atdac.asv_tsk_status';
end if;
/*********************************************************************************************************************/

sqlCall:=tableA || ' union all ' ||tableB || ' union all ' || tableC;

if act_group='0' then
sqlCall:=' select u.user_name||''[''||u.user_id||'']'' as user_name, t.* from ' ||
'(select * from ('||sqlCall||') ) t, tsys_user u ' ||
'where t.act_tsk_execute_id = u.user_id';
end if;

 

-- 调用公共读写日志过程
LOG_RP_PROC(tableA,'','','V4.0.3.7tableA','座席回访记录汇总表A');
LOG_RP_PROC(tableB,'','','V4.0.3.7tableB','座席回访记录汇总表B');
LOG_RP_PROC(tableC,'','','V4.0.3.7tableC','座席回访记录汇总表C');
LOG_RP_PROC(sqlCall,'','','V4.0.3.7table','座席回访记录汇总表');
open report_cur_type for sqlCall;
end callbackAgentOutBound_proc;
/