[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_insert_count NUMBER := 0;
g_delete_count NUMBER := 0;
g_update_count NUMBER := 0;
PROCEDURE insert_log_table
IS
l_proc_name VARCHAR2(20) := 'INSERT_LOG_TABLE';
/* Insert status into log table */
INSERT INTO BIX_DM_COLLECT_LOG
(
collect_id,
collect_concur_id,
object_name,
object_type,
run_start_date,
run_end_date,
collect_start_date,
collect_end_date,
collect_status,
collect_excep_mesg,
rows_deleted,
rows_inserted,
rows_updated,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date
)
VALUES
(
BIX_DM_COLLECT_LOG_S.NEXTVAL,
null,
g_table_name,
'TABLE',
g_run_start_date,
g_run_end_date,
g_collect_start_date,
g_collect_end_date,
g_status,
g_error_msg,
g_delete_count,
g_insert_count,
g_update_count,
sysdate,
g_user_id,
sysdate,
g_user_id,
g_login_id,
g_request_id,
g_program_appl_id,
g_program_id,
sysdate
);
END insert_log_table;
PROCEDURE delete_in_chunks(p_table_name IN VARCHAR2,
p_type IN NUMBER,
p_rows_deleted OUT nocopy NUMBER)
IS
l_delete_statement VARCHAR2(4000);
l_rows_deleted NUMBER;
l_proc_name VARCHAR2(20) := 'DELETE_IN_CHUNKS';
l_rows_deleted := 0;
DELETE FROM BIX_DM_AGENT_SESSBYCAMP_SUM
WHERE period_start_date_time between g_rounded_collect_start_date
AND g_rounded_collect_end_date
AND rownum <= g_commit_chunk_size;
DELETE FROM BIX_DM_GROUP_SESSBYCAMP_SUM
WHERE period_start_date_time between g_rounded_collect_start_date
AND g_rounded_collect_end_date
AND rownum <= g_commit_chunk_size;
DELETE FROM BIX_DM_AGENT_SESSBYCAMP_SUM
WHERE request_id = g_request_id
AND rownum <= g_commit_chunk_size;
DELETE FROM BIX_DM_GROUP_SESSBYCAMP_SUM
WHERE request_id = g_request_id
AND rownum <= g_commit_chunk_size;
l_rows_deleted := l_rows_deleted + SQL%ROWCOUNT;
g_message := 'Deleted ' || l_rows_deleted || ' rows from table '
|| p_table_name ;
p_rows_deleted := l_rows_deleted;
g_proc_name := 'BIX_DM_SESSBYCAMP_PKG.DELETE_IN_CHUNKS';
g_error_msg := 'Invalid IF condition in delete ';
END delete_in_chunks;
PROCEDURE INSERT_WORKTIME_ROW(p_resource_id in NUMBER,
p_server_group_id in NUMBER,
p_campaign_id in NUMBER,
p_campaign_schedule_id in NUMBER,
p_start_date in DATE,
p_secs in NUMBER,
p_ddl_type OUT nocopy VARCHAR2)
IS
l_proc_name VARCHAR2(20) := 'INSERT_WORKTIME_ROW';
SELECT 'Y'
INTO l_exists
FROM BIX_DM_AGENT_SESSBYCAMP_SUM
WHERE resource_id = p_resource_id
AND period_start_date_time = p_start_date
AND server_group_id = p_server_group_id
AND campaign_id = p_campaign_id
AND campaign_schedule_id = p_campaign_schedule_id;
INSERT INTO BIX_DM_AGENT_SESSBYCAMP_SUM
( agent_sessbycamp_sum_id
,resource_id
,server_group_id
,campaign_id
,campaign_schedule_id
,period_start_date
,period_start_time
,period_start_date_time
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
,work_time
,available_time
,request_id
,program_application_id
,program_id
,program_update_date
) VALUES
( BIX_DM_AGENT_SESSBYCAMP_SUM_S.NEXTVAL
,p_resource_id
,p_server_group_id
,p_campaign_id
,p_campaign_schedule_id
,TRUNC(p_start_date)
,TO_CHAR(p_start_date,'HH24:MI')
,p_start_date
,SYSDATE
,g_user_id
,SYSDATE
,g_user_id
,g_user_id
,p_secs
,0
,g_request_id
,g_program_appl_id
,g_program_id
,SYSDATE );
UPDATE BIX_DM_AGENT_SESSBYCAMP_SUM
SET work_time = work_time + p_secs,
last_update_date = SYSDATE,
last_updated_by = g_user_id,
program_update_date = SYSDATE
WHERE resource_id = p_resource_id
AND period_start_date_time = p_start_date
AND server_group_id = p_server_group_id
AND campaign_schedule_id = p_campaign_schedule_id;
END INSERT_WORKTIME_ROW;
SELECT sess.resource_id resource_id,
campsch.campaign_id campaign_id,
campsch.schedule_id campaign_schedule_id,
res.server_group_id server_group_id,
act.begin_date_time begin_date_time,
act.end_date_time end_date_time
FROM ieu_sh_sessions sess,
ieu_sh_activities act,
--ieu_uwq_media_types_tl med,
jtf_rs_resource_extns res,
ams_campaign_schedules_b campsch
WHERE sess.session_id = act.session_id
AND sess.resource_id = res.resource_id
AND sess.application_id = 696
AND act.activity_type_code = 'MEDIA_CYCLE'
AND act.category_type = 'CSCH' --campaign schedule
--AND act.category_value = to_char(campsch.schedule_id)
AND decode(act.category_type,'CSCH',to_number(nvl(act.category_value,-1)),-1) = campsch.schedule_id
--AND med.media_type_id = 10009
--AND med.media_type_id = act.media_type_id
AND act.media_type_id = 10009 -- outbound calls
AND sess.begin_date_time <= g_rounded_collect_end_date
AND (sess.end_date_time >= g_rounded_collect_start_date
OR sess.end_date_time is NULL ) ;
SELECT TO_CHAR(l_begin_date,'YYYY/MM/DD')||
LPAD(TO_CHAR(l_begin_date,'HH24:'),3,'0')||
DECODE(SIGN(TO_NUMBER(TO_CHAR(l_begin_date,'MI'))-29),
0,'00',1,'30',-1,'00')
INTO l_temp FROM DUAL;
INSERT_WORKTIME_ROW(work_time.resource_id,
work_time.server_group_id,
work_time.campaign_id,
work_time.campaign_schedule_id,
l_period_start,
l_secs,
l_ddl_type);
g_message := 'Finished collecting agent work time : Inserted ' ||
l_row_count || ' rows into BIM_DM_AGENT_SESSBYCAMP_SUM' ;
g_insert_count := l_row_count;
SELECT sess.resource_id resource_id,
res.server_group_id server_group_id,
campsch.campaign_id campaign_id,
campsch.schedule_id campaign_schedule_id,
act1.begin_date_time begin_date_time,
act1.deliver_date_time end_date_time
FROM ieu_sh_sessions sess,
ieu_sh_activities act1,
ieu_sh_activities act2,
--ieu_uwq_media_types_tl med,
jtf_rs_resource_extns res,
ams_campaign_schedules_b campsch
WHERE act1.begin_date_time <= g_rounded_collect_end_date
AND (act1.deliver_date_time >= g_rounded_collect_start_date
OR (act1.deliver_date_time is NULL AND act1.end_date_time IS NULL)
)
AND sess.application_id = 696
AND sess.session_id = act1.session_id
AND sess.resource_id = res.resource_id
AND act1.activity_type_code = 'MEDIA'
AND act1.parent_cycle_id = act2.activity_id
AND act2.activity_type_code = 'MEDIA_CYCLE'
AND act2.category_type = 'CSCH' --campaign schedule
--AND act2.category_value = to_char(campsch.schedule_id)
AND decode(act2.category_type,'CSCH',to_number(nvl(act2.category_value,-1)),-1) = campsch.schedule_id
--AND med.media_type_id = 10009 --outbound calls
--AND med.media_type_id = act2.media_type_id
AND act2.media_type_id = 10009 --outbound calls
UNION ALL
SELECT sess.resource_id resource_id,
res.server_group_id server_group_id,
campsch.campaign_id campaign_id,
campsch.schedule_id campaign_schedule_id,
act1.begin_date_time begin_date_time,
act1.end_date_time end_date_time
FROM ieu_sh_sessions sess,
ieu_sh_activities act1,
ieu_sh_activities act2,
jtf_rs_resource_extns res,
ams_campaign_schedules_b campsch
WHERE act1.begin_date_time <= g_rounded_collect_end_date
AND act1.end_date_time >= g_rounded_collect_start_date
AND act1.deliver_date_time IS NULL
AND sess.application_id = 696
AND sess.session_id = act1.session_id
AND sess.resource_id = res.resource_id
AND act1.activity_type_code = 'MEDIA'
AND act1.parent_cycle_id = act2.activity_id
AND act2.activity_type_code = 'MEDIA_CYCLE'
AND act2.category_type = 'CSCH' --campaign schedule
--AND act2.category_value = to_char(campsch.schedule_id)
AND decode(act2.category_type,'CSCH',to_number(nvl(act2.category_value,-1)),-1) = campsch.schedule_id
AND act2.media_type_id = 10009 --outbound calls
;
SELECT TO_CHAR(l_begin_date,'YYYY/MM/DD')||
LPAD(TO_CHAR(l_begin_date,'HH24:'),3,'0') ||
DECODE(SIGN(TO_NUMBER(TO_CHAR(l_begin_date,'MI'))-29),
0,'00',1,'30',-1,'00')
INTO l_temp FROM DUAL;
UPDATE BIX_DM_AGENT_SESSBYCAMP_SUM
SET available_time = nvl(available_time,0)+l_secs,
last_update_date = SYSDATE,
last_updated_by = g_user_id,
program_update_date = SYSDATE
WHERE resource_id = avail_time.resource_id
AND server_group_id = avail_time.server_group_id
AND campaign_id = avail_time.campaign_id
AND campaign_schedule_id = avail_time.campaign_schedule_id
AND period_start_date_time = l_period_start;
g_update_count := l_row_count;
g_message := 'Finished collecting agent available time : Updated ' ||
l_row_count || ' rows in BIM_DM_AGENT_SESSBYCAMP_SUM' ;
SELECT grp_denorm.parent_group_id group_id,
agt_sum.server_group_id server_group_id,
agt_sum.campaign_id campaign_id,
agt_sum.campaign_schedule_id campaign_schedule_id,
agt_sum.period_start_date,
agt_sum.period_start_time,
agt_sum.period_start_date_time,
SUM(agt_sum.available_time) available_time,
SUM(agt_sum.work_time) work_time
FROM BIX_DM_AGENT_SESSBYCAMP_sum agt_sum,
jtf_rs_group_members grp_mem,
jtf_rs_groups_denorm grp_denorm
WHERE agt_sum.period_start_date_time BETWEEN g_rounded_collect_start_date
AND g_rounded_collect_end_date
AND agt_sum.resource_id = grp_mem.resource_id
AND grp_mem.group_id = grp_denorm.group_id
--
--add the following to take care of cases where
--agent belongs to two groups which roll up to the
--same parent group to avoid duplicating the values
--for the parent group
--
AND NVL(grp_mem.delete_flag,'N') <> 'Y'
AND agt_sum.period_start_date_time BETWEEN
NVL(grp_denorm.start_date_active,agt_sum.period_start_date_time)
AND NVL(grp_denorm.end_date_active,SYSDATE)
AND grp_mem.group_member_id =
(select max(mem1.group_member_id)
from jtf_rs_group_members mem1
where mem1.group_id in
(select den1.group_id
from jtf_rs_groups_denorm den1
where den1.parent_group_id = grp_denorm.parent_group_id
AND agt_sum.period_start_date_time BETWEEN
NVL(den1.start_date_active,agt_sum.period_start_date_time)
AND NVL(den1.end_date_active,SYSDATE)
)
AND mem1.resource_id = grp_mem.resource_id
AND nvl(mem1.delete_flag,'N') <> 'Y'
)
GROUP BY grp_denorm.parent_group_id,
agt_sum.server_group_id,
agt_sum.campaign_id,
agt_sum.campaign_schedule_id,
agt_sum.period_start_date_time,
agt_sum.period_start_date,
agt_sum.period_start_time;
g_insert_count := 0;
g_delete_count := 0;
g_update_count := 0;
/* Delete data between these dates and re-compute */
delete_in_chunks( 'BIX_DM_GROUP_SESSBYCAMP_SUM', 1, g_delete_count);
g_message := 'Start Inserting rows into BIX_DM_GROUP_SESSION_SUM table';
INSERT INTO BIX_DM_GROUP_SESSBYCAMP_SUM
( group_sessbycamp_sum_id
,group_id
,server_group_id
,campaign_id
,campaign_schedule_id
,period_start_date
,period_start_time
,period_start_date_time
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
,available_time
,work_time
,request_id
,program_application_id
,program_id
,program_update_date )
VALUES (
BIX_DM_GROUP_CALL_SUM_S.NEXTVAL
,groupinfo.group_id
,groupinfo.server_group_id
,groupinfo.campaign_id
,groupinfo.campaign_schedule_id
,groupinfo.period_start_date
,groupinfo.period_start_time
,groupinfo.period_start_date_time
,SYSDATE
,g_user_id
,SYSDATE
,g_user_id
,g_user_id
,groupinfo.available_time
,groupinfo.work_time
,g_request_id
,g_program_appl_id
,g_program_id
,SYSDATE
);
g_message := 'Finished inserting rows into BIX_DM_GROUP_SESSBYCAMP_SUM. Inserted ' || l_row_count || ' rows ';
g_insert_count := l_row_count;
insert_log_table;
delete_in_chunks('BIX_DM_GROUP_SESSBYCAMP_SUM', 2, l_row_count);
delete_in_chunks('BIX_DM_AGENT_SESSBYCAMP_SUM', 2, l_row_count);
UPDATE BIX_DM_COLLECT_LOG
SET collect_status = 'FAILURE',
rows_inserted = 0,
rows_updated = 0,
collect_excep_mesg = g_error_msg
WHERE request_id = g_request_id
AND object_name = 'BIX_DM_GROUP_SESSBYCAMP_SUM';
insert_log_table;
l_delete_count NUMBER := 0;
g_insert_count := 0;
g_delete_count := 0;
g_update_count := 0;
/* Delete data between these dates and re-compute */
delete_in_chunks( 'BIX_DM_AGENT_SESSBYCAMP_SUM', 1, g_delete_count);
/* Insert the status into BIX_DM_COLLECT_LOG table */
g_run_end_date := sysdate;
insert_log_table;
COMMIT; --commit after all rows are inserted in BIX_DM_AGENT_SESSBYCAMP_sum
delete_in_chunks('BIX_DM_AGENT_SESSBYCAMP_SUM', 2, l_delete_count);
g_insert_count := 0;
g_update_count := 0;
insert_log_table;
IF FND_PROFILE.DEFINED('BIX_DM_DELETE_SIZE')
THEN
g_commit_chunk_size := TO_NUMBER(FND_PROFILE.VALUE('BIX_DM_DELETE_SIZE'));
SELECT TO_DATE(TO_CHAR(g_collect_start_date,'YYYY/MM/DD')||
LPAD(TO_CHAR(g_collect_start_date,'HH24:'),3,'0')||
DECODE(SIGN(TO_NUMBER(TO_CHAR(g_collect_start_date,'MI'))-29),
0,'00:00',1,'30:00',-1,'00:00'), 'YYYY/MM/DDHH24:MI:SS')
INTO g_rounded_collect_start_date
FROM DUAL;
SELECT TO_DATE(
TO_CHAR(g_collect_end_date,'YYYY/MM/DD')||
LPAD(TO_CHAR(g_collect_end_date,'HH24:'),3,'0')||
DECODE(SIGN(TO_NUMBER(TO_CHAR(g_collect_end_date,'MI'))-29),
0,'29:59',1,'59:59',-1,'29:59'), 'YYYY/MM/DDHH24:MI:SS')
INTO g_rounded_collect_end_date
FROM DUAL;
Success log tables were already inserted, so just commit
*/
COMMIT;