[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
IF (FND_PROFILE.DEFINED('BIX_DM_DELETE_SIZE')) THEN
g_commit_chunk_size := TO_NUMBER(FND_PROFILE.VALUE('BIX_DM_DELETE_SIZE'));
BIS_COLLECTION_UTILITIES.deleteLogForObject('BIX_AGENT_SESSION_F');
BIS_COLLECTION_UTILITIES.deleteLogForObject('BIX_AGENT_SESSION_F');
INSERT /*+ append parallel(bss) */ INTO bix_agent_session_stg bss
(bss.agent_id,
bss.server_group_id,
bss.schedule_id,
bss.campaign_id,
bss.application_id,
bss.time_id,
bss.period_type_id,
bss.period_start_date,
bss.period_start_time,
bss.day_of_week,
bss.created_by,
bss.creation_date,
bss.last_updated_by,
bss.last_update_date,
bss.idle_time,
bss.request_id,
bss.program_application_id,
bss.program_id,
bss.program_update_date )
(SELECT /*+ parallel(bas) */
bas.agent_id,
bas.server_group_id,
-1,
-1,
bas.application_id,
bas.time_id,
bas.period_type_id,
bas.period_start_date,
bas.period_start_time,
bas.day_of_week,
g_user_id,
g_sysdate,
g_user_id,
g_sysdate,
decode(nvl(sum(bas.login_time),0) - nvl(sum(bas.work_time), 0), 0, to_number(null),
nvl(sum(bas.login_time),0) - nvl(sum(bas.work_time), 0)),
g_request_id,
g_program_appl_id,
g_program_id,
g_sysdate
FROM bix_agent_session_stg bas
WHERE bas.application_id = 696
GROUP BY
bas.agent_id,
bas.server_group_id,
bas.application_id,
bas.time_id,
bas.period_type_id,
bas.period_start_date,
bas.period_start_time,
bas.day_of_week);
PROCEDURE insert_available_row(p_activity_id in g_activity_id_tab,
p_agent_id in g_resource_id_tab,
p_activity_begin_date in g_begin_date_time_tab,
p_activity_end_date in g_end_date_time_tab,
p_server_group_id in g_server_group_id_tab,
p_application_id in g_application_id_tab,
p_schedule_id in g_schedule_id_tab,
p_campaign_id in g_campaign_id_tab)
IS
TYPE available_time_tab is TABLE OF bix_agent_session_f.available_time%TYPE;
write_log('Start of the procedure insert_available_row at : ' || to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
SELECT trunc(l_begin_date)
INTO l_period_start
FROM DUAL;
/* Loop through the session record and insert a record for each half hour bucket */
WHILE ( l_period_start < l_end_date )
LOOP
j := j + 1;
/* Bulk insert all the rows in the staging area */
IF (l_agent_id.COUNT > 0) THEN
FORALL i IN l_agent_id.FIRST .. l_agent_id.LAST
INSERT /*+ append */ INTO bix_agent_session_stg bas (
bas.agent_id
,bas.server_group_id
,bas.schedule_id
,bas.campaign_id
,bas.application_id
,bas.time_id
,bas.period_type_id
,bas.period_start_date
,bas.period_start_time
,bas.day_of_week
,bas.last_update_date
,bas.last_updated_by
,bas.creation_date
,bas.created_by
,bas.last_update_login
,bas.available_time
,bas.request_id
,bas.program_application_id
,bas.program_id
,bas.program_update_date)
VALUES (
l_agent_id(i)
,l_server_group_id(i)
,l_schedule_id(i)
,l_campaign_id(i)
,l_application_id(i)
,to_number(to_char(l_period_start_date(i), 'J'))
,1
,TRUNC(l_period_start_date(i))
,'00:00'
,TO_CHAR(l_period_start_date(i),'D')
,g_sysdate
,g_user_id
,g_sysdate
,g_user_id
,g_user_id
,decode(l_available_time(i), 0, to_number(null), l_available_time(i))
,g_request_id
,g_program_appl_id
,g_program_id
,g_sysdate);
write_log('Total rows inserted in the staging area for half hour time bucket : ' || to_char(l_agent_id.COUNT));
write_log('Finished procedure insert_available_row at : ' || to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
write_log('Error in insert_available_row : Error : ' || sqlerrm);
END insert_available_row;
SELECT /*+ parallel(iss) parallel(isa1) parallel(isa2) parallel(bis1) parallel(res) parallel(csh)
pq_distribute(iss hash,hash) pq_distribute(isa1 hash,hash)
pq_distribute(isa2 hash,hash) pq_distribute(bis1 hash,hash)
pq_distribute(res hash,hash) pq_distribute(csh hash,hash)
use_hash(iss,isa1,isa2,bis1,res,csh) */
isa1.activity_id activity_id
,iss.resource_id resource_id
,isa1.begin_date_time begin_date_time
,nvl(isa1.deliver_date_time, nvl(isa1.end_date_time, isa1.begin_date_time))
end_date_time
,nvl(res.server_group_id,-1) server_group_id
,iss.application_id application_id
,decode(isa2.category_type, 'CSCH', nvl(csh.schedule_id, -1), -1)
schedule_id
,decode(isa2.category_type, 'CSCH', nvl(csh.campaign_id, -1), -1)
campaign_id
FROM
ieu_sh_sessions iss
,ieu_sh_activities isa1
,ieu_sh_activities isa2
,jtf_rs_resource_extns res
,ams_campaign_schedules_b csh
WHERE isa1.last_update_date > g_collect_start_date
AND isa1.last_update_date <= g_collect_end_date
AND iss.session_id = isa1.session_id
AND iss.application_id = 696
AND isa1.activity_type_code = 'MEDIA'
AND isa1.parent_cycle_id = isa2.activity_id
AND isa2.activity_type_code = 'MEDIA_CYCLE'
AND iss.resource_id = res.resource_id
AND decode(isa2.category_type, 'CSCH', to_number(nvl(isa2.category_value, -1)), -1) = csh.schedule_id(+);
insert_available_row(
l_activity_id,
l_resource_id,
l_begin_date_time,
l_end_date_time,
l_server_group_id,
l_application_id,
l_schedule_id,
l_campaign_id);
PROCEDURE insert_work_row(p_activity_id in g_activity_id_tab,
p_agent_id in g_resource_id_tab,
p_activity_begin_date in g_begin_date_time_tab,
p_activity_end_date in g_end_date_time_tab,
p_server_group_id in g_server_group_id_tab,
p_application_id in g_application_id_tab,
p_schedule_id in g_schedule_id_tab,
p_campaign_id in g_campaign_id_tab)
IS
TYPE work_time_tab is TABLE OF bix_agent_session_f.work_time%TYPE;
write_log('Start of the procedure insert_work_row at : ' || to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
SELECT trunc(l_begin_date)
INTO l_period_start
FROM DUAL;
/* Loop through the session record and insert a record for each half hour bucket */
WHILE ( l_period_start < l_end_date )
LOOP
j := j + 1;
/* Bulk insert all the rows in the staging area */
IF (l_agent_id.COUNT > 0) THEN
FORALL i IN l_agent_id.FIRST .. l_agent_id.LAST
INSERT /*+ append */ INTO bix_agent_session_stg bas (
bas.agent_id
,bas.server_group_id
,bas.schedule_id
,bas.campaign_id
,bas.application_id
,bas.time_id
,bas.period_type_id
,bas.period_start_date
,bas.period_start_time
,bas.day_of_week
,bas.last_update_date
,bas.last_updated_by
,bas.creation_date
,bas.created_by
,bas.last_update_login
,bas.work_time
,bas.request_id
,bas.program_application_id
,bas.program_id
,bas.program_update_date)
VALUES (
l_agent_id(i)
,l_server_group_id(i)
,l_schedule_id(i)
,l_campaign_id(i)
,l_application_id(i)
,to_number(to_char(l_period_start_date(i), 'J'))
,1
,TRUNC(l_period_start_date(i))
,'00:00'
,TO_CHAR(l_period_start_date(i),'D')
,g_sysdate
,g_user_id
,g_sysdate
,g_user_id
,g_user_id
,decode(l_work_time(i), 0, to_number(null), l_work_time(i))
,g_request_id
,g_program_appl_id
,g_program_id
,g_sysdate);
write_log('Total rows inserted in the staging area for half hour time bucket : ' || to_char(l_agent_id.COUNT));
write_log('Finished procedure insert_work_row at : ' || to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
write_log('Error in insert_work_row : Error : ' || sqlerrm);
END insert_work_row;
SELECT /*+ parallel(iss) parallel(isa) parallel(isamed) parallel(res) parallel(csh)
pq_distribute(iss hash,hash) pq_distribute(isa hash,hash)
pq_distribute(isamed hash,hash) pq_distribute(res hash,hash)
pq_distribute(csh hash,hash)
use_hash(iss,isa,isamed,res,csh)*/
isa.activity_id activity_id
,iss.resource_id resource_id
,isa.begin_date_time begin_date_time
,nvl(isa.end_date_time, nvl(isamed.end_Date_time,isa.begin_Date_time)) end_date_time
,nvl(res.server_group_id,-1) server_group_id
,iss.application_id application_id
,decode(isa.category_type, 'CSCH', nvl(csh.schedule_id, -1), -1)
schedule_id
,decode(isa.category_type, 'CSCH', nvl(csh.campaign_id, -1), -1)
campaign_id
FROM
ieu_sh_sessions iss
,ieu_sh_activities isa
,jtf_rs_resource_extns res
,ams_campaign_schedules_b csh
,(select parent_cycle_id,max(isamed.end_Date_time) end_date_time from ieu_sh_Activities isamed,ieu_sh_sessions isamedsess
where activity_type_code='MEDIA'
and isamedsess.session_id=isamed.session_id
and isamedsess.application_id=696
group by parent_cycle_id) isamed
WHERE isa.last_update_date > g_collect_start_date
AND isa.last_update_date <= g_collect_end_date
AND iss.session_id = isa.session_id
AND iss.application_id = 696
AND isa.activity_type_code = 'MEDIA_CYCLE'
AND isa.activity_id=isamed.parent_cycle_id(+)
AND iss.resource_id = res.resource_id
AND decode(isa.category_type, 'CSCH', to_number(nvl(isa.category_value, -1)), -1) = csh.schedule_id(+);
insert_work_row(
l_activity_id,
l_resource_id,
l_begin_date_time,
l_end_date_time,
l_server_group_id,
l_application_id,
l_schedule_id,
l_campaign_id);
PROCEDURE insert_login_row(p_session_id in g_session_id_tab,
p_agent_id in g_resource_id_tab,
p_session_begin_date in g_begin_date_time_tab,
p_session_end_date in g_end_date_time_tab,
p_server_group_id in g_server_group_id_tab,
p_application_id in g_application_id_tab)
IS
TYPE login_time_tab is TABLE OF bix_agent_session_f.login_time%TYPE;
write_log('Start of the procedure insert_login_row at : ' || to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
SELECT trunc(l_begin_date)
INTO l_period_start
FROM DUAL;
/* Loop through the session record and insert a record for each half hour bucket */
WHILE ( l_period_start < l_end_date )
LOOP
j := j + 1;
/* Bulk insert all the rows in the staging area */
IF (l_agent_id.COUNT > 0) THEN
FORALL i IN l_agent_id.FIRST .. l_agent_id.LAST
INSERT /*+ append */ INTO bix_agent_session_stg bas (
bas.agent_id
,bas.server_group_id
,bas.schedule_id
,bas.campaign_id
,bas.application_id
,bas.time_id
,bas.period_type_id
,bas.period_start_date
,bas.period_start_time
,bas.day_of_week
,bas.last_update_date
,bas.last_updated_by
,bas.creation_date
,bas.created_by
,bas.last_update_login
,bas.login_time
,bas.request_id
,bas.program_application_id
,bas.program_id
,bas.program_update_date)
VALUES (
l_agent_id(i)
,l_server_group_id(i)
,-1
,-1
,l_application_id(i)
,to_number(to_char(l_period_start_date(i), 'J'))
,1
,TRUNC(l_period_start_date(i))
,'00:00'
,TO_CHAR(l_period_start_date(i),'D')
,g_sysdate
,g_user_id
,g_sysdate
,g_user_id
,g_user_id
,decode(l_login_time(i), 0, to_number(null), l_login_time(i))
,g_request_id
,g_program_appl_id
,g_program_id
,g_sysdate);
write_log('Total rows inserted in the staging area for half hour time bucket : ' || to_char(l_agent_id.COUNT));
INSERT /*+ append */ INTO bix_sessions bis1 (
bis1.session_id,
bis1.created_by,
bis1.creation_date,
bis1.last_updated_by,
bis1.last_update_date,
bis1.curr_collect_date,
bis1.last_collect_date,
bis1.request_id,
bis1.program_application_id,
bis1.program_id,
bis1.program_update_date )
VALUES (
l_session_id(i),
g_user_id,
g_sysdate,
g_user_id,
g_sysdate,
l_collect_date(i),
l_collect_date(i),
g_request_id,
g_program_appl_id,
g_program_id,
g_sysdate);
write_log('Finished procedure insert_login_row at : ' || to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
write_log('Error in insert_login_row : Error : ' || sqlerrm);
END insert_login_row;
select /*+ use_hash(res) parallel(res) parallel(inv) */ inv.session_id, inv.resource_id, inv.begin_date_time,
nvl (inv.end_date_time, lead (inv.prevsd, 1, inv.maxval) over
(partition by inv.resource_id order by begin_date_time)) end_date_time,
nvl (res.server_group_id, -1) server_group_id,
decode (inv.application_id, 696, 696, 680, 680, 0) application_id
from jtf_rs_resource_extns res,
(select /*+ parallel(x) */ type, resource_id, begin_date_time,
end_date_time, session_id, application_id, lag (begin_date_time)
over (partition by resource_id order by begin_date_time) prevsd,
max (begin_date_time)
over (partition by resource_id order by begin_date_time) maxval
from (
select /*+ parallel(sess1) */ 1 type, resource_id, begin_date_time, end_date_time,
session_id, application_id
from ieu_sh_sessions sess1
WHERE last_update_date > g_collect_start_date
AND last_update_date <= g_collect_end_date
union all
select /*+ parallel(msegs) */ 2 type, resource_id,
start_date_time begin_date_time, null end_date_time,
null session_id, null application_id
from jtf_ih_media_item_lc_segs msegs,
jtf_ih_media_itm_lc_seg_tys segs
where msegs.milcs_type_id = segs.milcs_type_id
and segs.milcs_code in ('EMAIL_FETCH', 'EMAIL_REPLY',
'EMAIL_DELETED', 'EMAIL_OPEN', 'EMAIL_REQUEUED',
'EMAIL_REROUTED_DIFF_CLASS', 'EMAIL_REROUTED_DIFF_ACCT',
'EMAIL_SENT', 'EMAIL_TRANSFERRED', 'EMAIL_ASSIGN',
'EMAIL_COMPOSE', 'WITH_AGENT', 'EMAIL_ESCALATED')) x) inv
where inv.resource_id = res.resource_id
and type = 1;
( SELECT + full(msegs)
resource_id, start_date_time FROM jtf_ih_media_item_lc_segs msegs
,jtf_ih_media_itm_lc_seg_tys segs
WHERE msegs.milcs_type_id = segs.milcs_type_id
AND segs.milcs_code IN
('EMAIL_FETCH'
,'EMAIL_REPLY'
,'EMAIL_DELETED'
,'EMAIL_OPEN'
,'EMAIL_REQUEUED'
,'EMAIL_REROUTED_DIFF_CLASS'
,'EMAIL_REROUTED_DIFF_ACCT'
,'EMAIL_SENT'
,'EMAIL_TRANSFERRED'
,'EMAIL_ASSIGN'
,'EMAIL_COMPOSE'
,'WITH_AGENT'
,'EMAIL_ESCALATED'
)
) mseg
,jtf_rs_resource_extns res
,(
SELECT + full(sess1) session_id, resource_id, application_id, begin_date_time, end_date_time,
lead(begin_date_time, 1)
over (partition by resource_id order by begin_date_time) next_sess_begin_date_time
FROM ieu_sh_sessions sess1
WHERE last_update_date > g_collect_start_date
AND last_update_date <= g_collect_end_date
) inv1
WHERE inv1.resource_id = res.resource_id
AND mseg.resource_id(+) = inv1.resource_id
AND mseg.start_date_time(+) >= inv1.begin_date_time
AND mseg.start_date_time(+) < nvl(inv1.next_sess_begin_date_time, g_sysdate)
GROUP BY inv1.session_id, inv1.resource_id, inv1.begin_date_time, inv1.end_date_time, res.server_group_id, inv1.application_id;
insert_login_row(
l_session_id,
l_resource_id,
l_begin_date_time,
l_end_date_time,
l_server_group_id,
l_application_id);
INSERT /*+ append parallel(bas) */ INTO bix_agent_session_f bas
(bas.agent_id,
bas.server_group_id,
bas.schedule_id,
bas.campaign_id,
bas.application_id,
bas.time_id,
bas.period_type_id,
bas.period_start_date,
bas.period_start_time,
bas.day_of_week,
bas.created_by,
bas.creation_date,
bas.last_updated_by,
bas.last_update_date,
bas.login_time,
bas.work_time,
bas.available_time,
bas.idle_time,
bas.agent_cost,
bas.request_id,
bas.program_application_id,
bas.program_id,
bas.program_update_date )
(SELECT /*+ parallel(bstg) */
bstg.agent_id agent_id,
bstg.server_group_id server_group_id,
bstg.schedule_id schedule_id,
bstg.campaign_id campaign_id,
bstg.application_id application_id,
bstg.time_id time_id,
bstg.period_type_id period_type_id,
bstg.period_start_date period_start_date,
bstg.period_start_time period_start_time,
bstg.day_of_week day_of_week,
g_user_id,
g_sysdate,
g_user_id,
g_sysdate,
sum(bstg.login_time) login_time,
sum(bstg.work_time) work_time,
sum(bstg.available_time) available_time,
sum(bstg.idle_time) idle_time,
decode(nvl(sum(bstg.login_time),0) * g_agent_cost, 0, to_number(null),
nvl(sum(bstg.login_time),0) * g_agent_cost) agent_cost,
g_request_id,
g_program_appl_id,
g_program_id,
g_sysdate
FROM bix_agent_session_stg bstg
GROUP BY
bstg.agent_id,
bstg.server_group_id,
bstg.schedule_id,
bstg.campaign_id,
bstg.application_id,
bstg.time_id,
bstg.period_type_id,
bstg.period_start_date,
bstg.period_start_time,
bstg.day_of_week);
INSERT /*+ APPEND PARALLEL(bea) */ INTO BIX_AGENT_SESSION_F bea
(bea.agent_id,
bea.server_group_id,
bea.schedule_id,
bea.campaign_id,
bea.application_id,
bea.time_id,
bea.period_type_id,
bea.period_start_date,
bea.period_start_time,
bea.day_of_week,
bea.created_by,
bea.creation_date,
bea.last_updated_by,
bea.last_update_date,
bea.login_time,
bea.work_time,
bea.available_time,
bea.idle_time,
bea.agent_cost,
bea.request_id,
bea.program_application_id,
bea.program_id,
bea.program_update_date )
(SELECT /*+ PARALLEL(inv1) */
inv1.agent_id,
inv1.server_group_id,
inv1.schedule_id,
inv1.campaign_id,
inv1.application_id,
inv1.time_id,
inv1.period_type_id,
inv1.period_start_date,
'00:00',
inv1.day_of_week,
g_user_id,
g_sysdate,
g_user_id,
g_sysdate,
sum(inv1.login_time),
sum(inv1.work_time),
sum(inv1.available_time),
sum(inv1.idle_time),
sum(inv1.agent_cost),
g_request_id,
g_program_appl_id,
g_program_id,
g_sysdate
FROM
(SELECT /*+ parallel(bes) parallel(ftd)
pq_distribute(bes hash,hash) pq_distribute(ftd hash,hash)
use_hash(bes,ftd) */
bes.agent_id agent_id,
bes.server_group_id,
bes.schedule_id schedule_id,
bes.campaign_id campaign_id,
bes.application_id application_id,
bes.day_of_week day_of_week,
decode(ftd.week_id, null, decode(ftd.ent_period_id, null, decode(ftd.ent_qtr_id, null, decode(ftd.ent_year_id, null,
to_number(null), ftd.ent_year_id), ftd.ent_qtr_id), ftd.ent_period_id), ftd.week_id) time_id,
decode(ftd.week_id, null, decode(ftd.ent_period_id, null, decode(ftd.ent_qtr_id, null, decode(ftd.ent_year_id, null,
to_number(null), 128), 64), 32), 16) period_type_id,
decode(ftd.week_id, null, decode(ftd.ent_period_id, null,
decode(ftd.ent_qtr_id, null, decode(ftd.ent_year_id, null, to_date(null), min(ftd.ent_year_start_date)),
min(ftd.ent_qtr_start_date)), min(ftd.ent_period_start_date)), min(ftd.week_start_date)) period_start_date,
sum(bes.login_time) login_time,
sum(bes.work_time) work_time,
sum(bes.available_time) available_time,
sum(bes.idle_time) idle_time,
sum(bes.agent_cost) agent_cost
FROM BIX_AGENT_SESSION_F bes,
fii_time_day ftd
WHERE bes.time_id = ftd.report_date_julian
AND bes.period_type_id = 1
GROUP BY
bes.agent_id,
bes.server_group_id,
bes.schedule_id,
bes.campaign_id,
bes.application_id,
bes.day_of_week,
ROLLUP (
ftd.ent_year_id,
ftd.ent_qtr_id,
ftd.ent_period_id,
ftd.week_id)
HAVING
decode(ftd.week_id, null, decode(ftd.ent_period_id, null, decode(ftd.ent_qtr_id, null, decode(ftd.ent_year_id, null,
to_number(null), 128), 64), 32), 16) IS NOT NULL) inv1
GROUP BY
inv1.agent_id,
inv1.server_group_id,
inv1.schedule_id,
inv1.campaign_id,
inv1.application_id,
inv1.time_id,
inv1.period_type_id,
inv1.period_start_date,
inv1.day_of_week);
write_log('Total rows inserted after rolling up in BIX_AGENT_SESSION_F : ' || to_char(g_rows_ins_upd));
SELECT count(*)
INTO l_count1
FROM fii_time_day
WHERE report_date between trunc(p_start_date) and trunc(p_end_date);
SELECT (trunc(p_end_date) - trunc(p_start_date)) + 1
INTO l_count2
FROM dual;