[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_EMAIL_DETAILS_F');
SELECT /*+ ordered full(intr) full(actv) full(mitm) use_hash(mitm,actv,intr,) +*/
intr.interaction_id interaction_id,
max(nvl(intr.resource_id, -1)) resource_id,
max(nvl(intr.party_id, -1)) party_id,
max(intr.start_date_time) start_date_time,
max(nvl(mitm.source_id, -1)) source_id,
max(nvl(irc.route_classification_id, -1)) route_classification_id,
max(nvl(iview.depth, 0)) depth
FROM
jtf_ih_media_items mitm,
jtf_ih_activities actv,
jtf_ih_interactions intr,
--
--Changes for R12
--
(
select /*+ full(im) +*/ name, max(route_classification_id) route_classification_id
from iem_route_classifications im
group by name
) irc,
(
select
interaction_id,
sum(depth) depth
from
(
/* This returns the parent level interactions and hardcoded depth as 1 for email replies-auto replies */
SELECT
actv.interaction_id interaction_id,
1 depth
FROM jtf_ih_activities actv,
/* jtf_ih_media_items imtm,*/
jtf_ih_media_item_lc_segs mseg,
jtf_ih_media_itm_lc_seg_tys mtys
WHERE actv.media_id = mseg.media_id
AND mtys.milcs_type_id = mseg.milcs_type_id
AND mtys.milcs_code IN ('EMAIL_AUTO_REPLY','EMAIL_REPLY')
GROUP BY actv.interaction_id
UNION ALL
/* This will count the no of replies that the interaction tree has. If there is no
child interaction that has a reply ,we need to go to the parent interaction level
and check if that has a reply, if so increment by 1 or leave it as is*/
SELECT root_interaction_id ,
/*NVL(max(decode(milcs_code,'EMAIL_REPLY',DEPTH,'EMAIL_AUTO_REPLY',DEPTH,NULL)),0)+1 DEPTH*/
count(distinct media_id)
FROM
(
SELECT /*+ ordered */
root_interaction_id,
parent,
child,
depth,
mseg.media_id
/* Added */
--,
--first_value(milcs_code) over (partition by mseg.media_id order by mseg.start_Date_time desc) milcs_code
FROM
jtf_ih_media_itm_lc_seg_tys mtys,
jtf_ih_media_item_lc_segs mseg,
jtf_ih_media_items mitm,
jtf_ih_activities actv ,
(
SELECT to_number(decode(instr(sys_connect_by_path(intr3.interact_interaction_idrelates, ':'), ':', 2), 0,
substr(sys_connect_by_path(intr3.interact_interaction_idrelates, ':'), 2),
substr(sys_connect_by_path(intr3.interact_interaction_idrelates, ':'), 2,
instr(sys_connect_by_path(intr3.interact_interaction_idrelates, ':'), ':', 2)-2))) root_interaction_id,
intr3.interact_interaction_idrelates parent,
intr3.interact_interaction_id child,
level depth
FROM jtf_ih_interaction_inters intr3
START WITH intr3.interact_interaction_idrelates in
(select
intr2.interact_interaction_idrelates
from jtf_ih_interaction_inters intr2
where intr2.interact_interaction_idrelates not in
(
select
intr1.interact_interaction_id
from jtf_ih_interaction_inters intr1
)
)
CONNECT BY intr3.interact_interaction_idrelates = PRIOR intr3.interact_interaction_id
) intr
WHERE actv.interaction_id =intr.child
AND mitm.media_id = actv.media_id
AND mitm.media_id = mseg.media_id
AND mseg.milcs_type_id = mtys.milcs_type_id
AND mitm.direction='INBOUND' AND mitm.media_item_type='EMAIL'
/* Added */ AND mtys.milcs_code in ('EMAIL_REPLY','EMAIL_AUTO_REPLY')
) GROUP BY ROOT_INTERACTION_ID
) iview/* Added */
WHERE NOT EXISTS (
select 1 from jtf_ih_interaction_inters inter
WHERE iview.interaction_id = inter.interact_interaction_id
)
group by interaction_id
) iview
WHERE intr.start_date_time between g_collect_start_date and g_collect_end_date
AND
intr.interaction_id = actv.interaction_id
AND intr.interaction_id = iview.interaction_id(+)
AND mitm.media_id = actv.media_id
AND mitm.direction = 'INBOUND'
AND mitm.media_item_type = 'EMAIL'
AND mitm.classification = irc.name(+)
AND intr.interaction_id NOT IN (
SELECT
inter.interact_interaction_id
FROM jtf_ih_interaction_inters inter)
GROUP BY intr.interaction_id;
/* Update the half-hour rows of ICI summary table with the resolution measures */
FORALL i IN l_root_interaction_id.FIRST .. l_root_interaction_id.LAST
MERGE INTO bix_email_details_f bed
USING (
SELECT
l_agent_id(i) agent_id
,l_email_account_id(i) email_account_id
,l_classification_id(i) email_classification_id
,l_party_id(i) party_id
,to_number(to_char(l_start_date_time(i), 'J')) time_id
,1 period_type_id
,trunc(l_start_date_time(i)) period_start_date
,'00:00' period_start_time
,l_one_done_rsln(i) one_rsln_in_period
,l_two_done_rsln(i) two_rsln_in_period
,l_three_done_rsln(i) three_rsln_in_period
,l_four_done_rsln(i) four_rsln_in_period
,l_intr_thread(i) interaction_threads_in_period
FROM DUAL) change
ON (
bed.agent_id = change.agent_id
AND bed.party_id = change.party_id
AND bed.email_account_id = change.email_account_id
AND bed.email_classification_id = change.email_classification_id
AND bed.time_id = change.time_id
AND bed.period_start_time = change.period_start_time
AND bed.period_start_date = change.period_start_date
AND bed.period_type_id = change.period_type_id
AND bed.outcome_id = -1 AND bed.result_id = -1 AND bed.reason_id = -1
)
WHEN MATCHED THEN
UPDATE
SET bed.one_rsln_in_period = decode(change.one_rsln_in_period, 0, bed.one_rsln_in_period,
decode(nvl(bed.one_rsln_in_period, 0) + change.one_rsln_in_period, 0, to_number(null),
nvl(bed.one_rsln_in_period, 0) + change.one_rsln_in_period))
,bed.two_rsln_in_period = decode(change.two_rsln_in_period, 0, bed.two_rsln_in_period,
decode(nvl(bed.two_rsln_in_period, 0) + change.two_rsln_in_period, 0, to_number(null),
nvl(bed.two_rsln_in_period, 0) + change.two_rsln_in_period))
,bed.three_rsln_in_period = decode(change.three_rsln_in_period, 0, bed.three_rsln_in_period,
decode(nvl(bed.three_rsln_in_period, 0) + change.three_rsln_in_period, 0, to_number(null),
nvl(bed.three_rsln_in_period, 0) + change.three_rsln_in_period))
,bed.four_rsln_in_period = decode(change.four_rsln_in_period, 0, bed.four_rsln_in_period,
decode(nvl(bed.four_rsln_in_period, 0) + change.four_rsln_in_period, 0, to_number(null),
nvl(bed.four_rsln_in_period, 0) + change.four_rsln_in_period))
,bed.interaction_threads_in_period = decode(change.interaction_threads_in_period, 0,
bed.interaction_threads_in_period, decode(nvl(bed.interaction_threads_in_period, 0)
+ change.interaction_threads_in_period, 0, to_number(null),
nvl(bed.interaction_threads_in_period, 0) + change.interaction_threads_in_period))
,bed.last_updated_by = g_user_id
,bed.last_update_date = g_sysdate
WHEN NOT MATCHED THEN INSERT (
bed.agent_id
,bed.party_id
,bed.email_account_id
,bed.email_classification_id
,bed.time_id
,bed.period_start_time
,bed.period_start_date
,bed.period_type_id
,outcome_id
,result_id
,reason_id
,bed.created_by
,bed.creation_date
,bed.last_updated_by
,bed.last_update_date
,bed.one_rsln_in_period
,bed.two_rsln_in_period
,bed.three_rsln_in_period
,bed.four_rsln_in_period
,bed.interaction_threads_in_period )
VALUES (
change.agent_id
,change.party_id
,change.email_account_id
,change.email_classification_id
,change.time_id
,change.period_start_time
,change.period_start_date
,change.period_type_id
,-1
,-1
,-1
,g_user_id
,g_sysdate
,g_user_id
,g_sysdate
,decode(change.one_rsln_in_period, 0, to_number(null), change.one_rsln_in_period)
,decode(change.two_rsln_in_period, 0, to_number(null), change.two_rsln_in_period)
,decode(change.three_rsln_in_period, 0, to_number(null), change.three_rsln_in_period)
,decode(change.four_rsln_in_period, 0, to_number(null), change.four_rsln_in_period)
,decode(change.interaction_threads_in_period, 0, to_number(null), change.interaction_threads_in_period));
write_log('Total rows inserted/updated in bix_email_details_f for resolution : ' ||
to_char(l_root_interaction_id.COUNT));
/* Update the bix_interactions_temp table to keep track of depth by interaction */
--
--BIX_INTERACTION_TEMP is used for UPDATE program. This is used to keep track of
--whet we need to subtract - example yesterday an email might have been ONE AND DONE.
--Today the customer replies to it and it is no longer DONE. So we need to go back and subtract
--yesterday's ONE AND DONE
--
FORALL i IN l_root_interaction_id.FIRST .. l_root_interaction_id.LAST
INSERT INTO BIX_INTERACTIONS_TEMP bit (
interaction_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
depth,
request_id,
program_application_id,
program_id,
program_update_date )
VALUES (
l_root_interaction_id(i),
g_user_id,
g_sysdate,
g_user_id,
g_sysdate,
l_curr_depth(i),
g_request_id,
g_program_appl_id,
g_program_id,
g_sysdate);
write_log('Total rows inserted/updated in bix_interactions_temp : ' || to_char(l_root_interaction_id.COUNT));
l_total_rows_deleted NUMBER := 0;
l_rows_deleted NUMBER := 0;
BIS_COLLECTION_UTILITIES.deleteLogForObject('BIX_EMAIL_DETAILS_F');
/* Insert / Update additive measures to summary table bix_email_details_f */
INSERT /*+ APPEND PARALLEL(bed) */ INTO BIX_EMAIL_DETAILS_F bed
(email_account_id,
email_classification_id,
agent_id,
party_id,
time_id,
period_type_id,
period_start_date,
period_start_time,
outcome_id,
result_id,
reason_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
emails_offered_in_period,
emails_fetched_in_period,
emails_replied_in_period,
emails_rpld_by_goal_in_period,
AGENT_EMAILS_RPLD_BY_GOAL,
emails_deleted_in_period,
emails_trnsfrd_out_in_period,
emails_trnsfrd_in_in_period,
emails_assigned_in_period,
emails_auto_routed_in_period,
emails_auto_uptd_sr_in_period,
email_resp_time_in_period,
agent_resp_time_in_period,
sr_created_in_period,
emails_rsl_and_trfd_in_period,
emails_orr_count_in_period,
EMAILS_AUTO_REPLIED_IN_PERIOD,
EMAILS_AUTO_DELETED_IN_PERIOD,
EMAILS_AUTO_RESOLVED_IN_PERIOD,
emails_composed_in_period,
emails_rerouted_in_period,
leads_created_in_period,
request_id,
program_application_id,
program_id,
program_update_date)
(SELECT /*+ PARALLEL(inv2) */
inv2.email_account_id,
inv2.email_classification_id,
inv2.agent_id,
inv2.party_id,
inv2.time_id,
--1/2hr changed period_type_id to 1 from -1
1,
inv2.period_start_date,
inv2.period_start_time,
inv2.outcome_id,
inv2.result_id,
inv2.reason_id,
g_user_id,
g_sysdate,
g_user_id,
g_sysdate,
decode(sum(emails_offered_in_period), 0, to_number(null), sum(emails_offered_in_period)),
decode(sum(emails_fetched_in_period), 0, to_number(null), sum(emails_fetched_in_period)),
decode(sum(emails_replied_in_period), 0, to_number(null), sum(emails_replied_in_period)),
decode(sum(emails_rpld_by_goal_in_period), 0, to_number(null), sum(emails_rpld_by_goal_in_period)),
decode(sum(AGENT_EMAILS_RPLD_BY_GOAL), 0, to_number(null), sum(AGENT_EMAILS_RPLD_BY_GOAL)),
decode(sum(emails_deleted_in_period), 0, to_number(null), sum(emails_deleted_in_period)),
decode(sum(emails_trnsfrd_out_in_period), 0, to_number(null), sum(emails_trnsfrd_out_in_period)),
decode(sum(emails_trnsfrd_in_in_period), 0, to_number(null), sum(emails_trnsfrd_in_in_period)),
decode(sum(emails_assigned_in_period), 0, to_number(null), sum(emails_assigned_in_period)),
decode(sum(emails_auto_routed_in_period), 0, to_number(null), sum(emails_auto_routed_in_period)),
decode(sum(emails_auto_uptd_sr_in_period), 0, to_number(null), sum(emails_auto_uptd_sr_in_period)),
decode(round(sum(email_resp_time_in_period)), 0, to_number(null), round(sum(email_resp_time_in_period))),
decode(round(sum(agent_resp_time_in_period)), 0, to_number(null), round(sum(agent_resp_time_in_period))),
decode(sum(sr_created_in_period), 0, to_number(null), sum(sr_created_in_period)),
decode(sum(emails_rsl_and_trfd_in_period), 0, to_number(null), sum(emails_rsl_and_trfd_in_period)),
decode(sum(emails_orr_count_in_period), 0, to_number(null), sum(emails_orr_count_in_period)),
decode(sum(EMAILS_AUTO_REPLIED_IN_PERIOD), 0, to_number(null), sum(EMAILS_AUTO_REPLIED_IN_PERIOD)),
decode(sum(EMAILS_AUTO_DELETED_IN_PERIOD), 0, to_number(null), sum(EMAILS_AUTO_DELETED_IN_PERIOD)),
decode(sum(EMAILS_AUTO_RESOLVED_IN_PERIOD), 0, to_number(null), sum(EMAILS_AUTO_RESOLVED_IN_PERIOD)),
decode(sum(emails_composed_in_period), 0, to_number(null), sum(emails_composed_in_period)),
decode(sum(EMAILS_REROUTED_IN_PERIOD), 0, to_number(null), sum(EMAILS_REROUTED_IN_PERIOD)),
decode(sum(leads_created_in_period), 0, to_number(null), sum(leads_created_in_period)),
g_request_id,
g_program_appl_id,
g_program_id,
g_sysdate
FROM --This sql fetches the count of a whole bunch of pure email measures like fetched, replied etc
(SELECT /*+ use_hash(mitm) use_hash(mseg) use_hash(inv2) use_hash(inv1) use_hash(mtyp) use_hash(irc)
PARALLEL(mitm) PARALLEL(mseg) PARALLEL(inv2) PARALLEL(inv1) PARALLEL(mtyp) PARALLEL(irc) */
nvl(mitm.source_id, -1) EMAIL_ACCOUNT_ID,
nvl(irc.route_classification_id, -1) EMAIL_CLASSIFICATION_ID,
nvl(mseg.resource_id, -1) AGENT_ID,
nvl(inv1.party_id, -1) PARTY_ID,
trunc(mseg.start_date_time) PERIOD_START_DATE,
to_number(to_char(mseg.start_date_time, 'J')) TIME_ID,
--1/2hr
--nvl(lpad(to_char(mseg.start_date_time,'HH24:'),3,'0') ||
-- decode(sign(to_number(to_char(mseg.start_date_time,'MI'))-29),0,'00',1,'30',-1,'00'),'00:00')
'00:00'
PERIOD_START_TIME,
-1 OUTCOME_ID,
-1 RESULT_ID,
-1 REASON_ID,
0 EMAILS_OFFERED_IN_PERIOD,
sum(decode(mtyp.milcs_code,'EMAIL_FETCH',1)) EMAILS_FETCHED_IN_PERIOD,
sum(decode(mtyp.milcs_code,'EMAIL_REPLY',1)) EMAILS_REPLIED_IN_PERIOD,
sum(decode(mtyp.milcs_code,'EMAIL_REPLY', (mseg.start_date_time - inv2.start_date_time) * 24 * 60 * 60))
AGENT_RESP_TIME_IN_PERIOD,
sum(decode(mtyp.milcs_code,'EMAIL_REPLY',
decode(sign(l_email_service_level - (mseg.start_date_time - mitm.start_date_time) * 24 * 60 * 60),-1,0,1)
, 'EMAIL_AUTO_REPLY',
decode(sign(l_email_service_level - (mseg.start_date_time - mitm.start_date_time) * 24 * 60 * 60),-1,0,1) ))
EMAILS_RPLD_BY_GOAL_IN_PERIOD,
sum(decode(mtyp.milcs_code,'EMAIL_REPLY',
decode(sign(l_email_service_level - (mseg.start_date_time - mitm.start_date_time) * 24 * 60 * 60),-1,0,1)
))
AGENT_EMAILS_RPLD_BY_GOAL,
sum(decode(mtyp.milcs_code,'EMAIL_TRANSFERRED',1,'EMAIL_ESCALATED',1)) EMAILS_TRNSFRD_OUT_IN_PERIOD,
sum(decode(mtyp.milcs_code,'EMAIL_TRANSFER',1)) EMAILS_TRNSFRD_IN_IN_PERIOD,
sum(decode(mtyp.milcs_code,'EMAIL_ASSIGNED',1)) EMAILS_ASSIGNED_IN_PERIOD,
sum(decode(mtyp.milcs_code,'EMAIL_AUTO_ROUTED',1)) EMAILS_AUTO_ROUTED_IN_PERIOD,
sum(decode(mtyp.milcs_code,'EMAIL_AUTO_UPDATED_SR',1))
EMAILS_AUTO_UPTD_SR_IN_PERIOD,
sum(decode(mtyp.milcs_code,'EMAIL_DELETED',1)) EMAILS_DELETED_IN_PERIOD,
sum(decode(mtyp.milcs_code,'EMAIL_REPLY', (mseg.start_date_time - mitm.start_date_time) * 24 * 60 * 60,
'EMAIL_AUTO_REPLY',(mseg.start_date_time - mitm.start_date_time) * 24 * 60 * 60))
EMAIL_RESP_TIME_IN_PERIOD,
0 SR_CREATED_IN_PERIOD,
0 EMAILS_RSL_AND_TRFD_IN_PERIOD,
sum(decode(mtyp.milcs_code,'EMAIL_AUTO_REPLY',1)) EMAILS_AUTO_REPLIED_IN_PERIOD,
sum(decode(mtyp.milcs_code,'EMAIL_AUTO_DELETED',1)) EMAILS_AUTO_DELETED_IN_PERIOD,
sum(decode(mtyp.milcs_code,'EMAIL_RESOLVED',1)) EMAILS_AUTO_RESOLVED_IN_PERIOD,
0 emails_composed_in_period,
0 emails_orr_count_in_period,
sum(decode(mtyp.milcs_code,'EMAIL_REROUTED_DIFF_CLASS',1,
'EMAIL_REROUTED_DIFF_ACCT',1,
'EMAIL_REQUEUED',1 )) EMAILS_REROUTED_IN_PERIOD,
0 LEADS_CREATED_IN_PERIOD
FROM
JTF_IH_MEDIA_ITEMS mitm,
JTF_IH_MEDIA_ITEM_LC_SEGS mseg,
(
SELECT /*+ use_hash(mseg1) use_hash(mtyp1) use_hash(mseg2) use_hash(mtyp2)
PARALLEL(mseg1) PARALLEL(mtyp1) PARALLEL(mseg2) PARALLEL(mtyp2) */
mseg1.media_id media_id,
MAX(mseg2.start_date_time) start_date_time
FROM
jtf_ih_media_item_lc_segs mseg1,
jtf_ih_media_itm_lc_seg_tys mtyp1,
jtf_ih_media_item_lc_segs mseg2,
jtf_ih_media_itm_lc_seg_tys mtyp2
WHERE mseg1.start_date_time BETWEEN g_collect_start_date AND g_collect_end_date
AND mseg1.milcs_type_id = mtyp1.milcs_type_id
AND mtyp1.milcs_code = 'EMAIL_REPLY'
AND mseg1.media_id = mseg2.media_id
AND mseg2.milcs_type_id = mtyp2.milcs_type_id
AND mtyp2.milcs_code IN ('EMAIL_FETCH','EMAIL_TRANSFER', 'EMAIL_ASSIGN_OPEN', 'EMAIL_AUTO_ROUTED', 'EMAIL_ASSIGNED')
GROUP BY mseg1.media_id
) inv2,
(
SELECT /*+ use_hash(actv) use_hash(intr) PARALLEL(actv) PARALLEL(intr) */
actv.media_id media_id,
min(intr.party_id) party_id
FROM
jtf_ih_activities actv,
jtf_ih_interactions intr
WHERE actv.start_date_time BETWEEN g_collect_start_date AND g_collect_end_date
AND actv.interaction_id = intr.interaction_id
GROUP BY actv.media_id
) inv1,
JTF_IH_MEDIA_ITM_LC_SEG_TYS mtyp,
--
--Changes for R12
--
(
select name, max(route_classification_id) route_classification_id
from iem_route_classifications
group by name
) irc
WHERE mitm.media_item_type = 'EMAIL'
AND mitm.direction = 'INBOUND'
AND mitm.classification = irc.name(+)
AND mitm.media_id = inv2.media_id(+)
AND mitm.media_id = inv1.media_id(+)
AND mitm.MEDIA_ID = mseg.MEDIA_ID
AND mseg.MILCS_TYPE_ID = mtyp.MILCS_TYPE_ID
AND mseg.START_DATE_TIME BETWEEN g_collect_start_date and g_collect_end_date
GROUP BY
nvl(mitm.source_id, -1),
nvl(irc.route_classification_id, -1),
mseg.resource_id,
nvl(inv1.party_id, -1),
trunc(mseg.start_date_time),
to_number(to_char(mseg.start_date_time, 'J'))
--1/2hr
--nvl(lpad(to_char(mseg.start_date_time,'HH24:'),3,'0') ||
-- decode(sign(to_number(to_char(mseg.start_date_time,'MI'))-29),0,'00',1,'30',-1,'00'),'00:00')
UNION ALL --This sql counts the number of emails offered in the given period grouped together by day
SELECT /*+ use_hash(mitm) use_hash(inv1) use_hash(irc) PARALLEL(mitm) PARALLEL(inv1) PARALLEL(irc) */
nvl(mitm.source_id, -1) EMAIL_ACCOUNT_ID,
nvl(irc.route_classification_id, -1) EMAIL_CLASSIFICATION_ID,
-1 AGENT_ID,
nvl(inv1.party_id, -1) PARTY_ID,
trunc(mitm.start_date_time) PERIOD_START_DATE,
to_number(to_char(mitm.start_date_time, 'J')) TIME_ID,
--1/2hr
--nvl(lpad(to_char(mitm.start_date_time,'HH24:'),3,'0') ||
-- decode(sign(to_number(to_char(mitm.start_date_time,'MI'))-29),0,'00',1,'30',-1,'00'),'00:00')
'00:00'
PERIOD_START_TIME,
-1 OUTCOME_ID,
-1 RESULT_ID,
-1 REASON_ID,
COUNT(*) EMAILS_OFFERED_IN_PERIOD,
0 EMAILS_FETCHED_IN_PERIOD,
0 EMAILS_REPLIED_IN_PERIOD,
0 AGENT_RESP_TIME_IN_PERIOD,
0 EMAILS_RPLD_BY_GOAL_IN_PERIOD,
0 AGENT_EMAILS_RPLD_BY_GOAL,
0 EMAILS_TRNSFRD_OUT_IN_PERIOD,
0 EMAILS_TRNSFRD_IN_IN_PERIOD,
0 EMAILS_ASSIGNED_IN_PERIOD,
0 EMAILS_AUTO_ROUTED_IN_PERIOD,
0 EMAILS_AUTO_UPTD_SR_IN_PERIOD,
0 EMAILS_DELETED_IN_PERIOD,
0 EMAIL_RESP_TIME_IN_PERIOD,
0 SR_CREATED_IN_PERIOD,
0 EMAILS_RSL_AND_TRFD_IN_PERIOD,
0 EMAILS_AUTO_REPLIED_IN_PERIOD,
0 EMAILS_AUTO_DELETED_IN_PERIOD,
0 EMAILS_AUTO_RESOLVED_IN_PERIOD,
0 emails_composed_in_period,
0 emails_orr_count_in_period,
0 EMAILS_REROUTED_IN_PERIOD,
0 LEADS_CREATED_IN_PERIOD
FROM
JTF_IH_MEDIA_ITEMS mitm,
(
SELECT /*+ use_hash(actv) use_hash(intr) PARALLEL(actv) PARALLEL(intr) */
actv.media_id media_id,
min(intr.party_id) party_id
FROM
jtf_ih_activities actv,
jtf_ih_interactions intr
WHERE actv.start_date_time BETWEEN g_collect_start_date AND g_collect_end_date
AND actv.interaction_id = intr.interaction_id
GROUP BY actv.media_id
) inv1,
--
--Changes for R12
--
(
select name, max(route_classification_id) route_classification_id
from iem_route_classifications
group by name
) irc
WHERE mitm.MEDIA_ITEM_TYPE = 'EMAIL'
AND mitm.DIRECTION = 'INBOUND'
AND mitm.classification = irc.name(+)
AND mitm.media_id = inv1.media_id(+)
AND mitm.START_DATE_TIME BETWEEN g_collect_start_date and g_collect_end_date
GROUP BY
nvl(mitm.source_id, -1),
nvl(irc.route_classification_id, -1),
nvl(inv1.party_id, -1),
trunc(mitm.start_date_time),
to_number(to_char(mitm.start_date_time, 'J'))
--1/2hr
--nvl(lpad(to_char(mitm.start_date_time,'HH24:'),3,'0') ||
-- decode(sign(to_number(to_char(mitm.start_date_time,'MI'))-29),0,'00',1,'30',-1,'00'),'00:00')
UNION ALL -- This sql segment counts the number of emails/SR created
SELECT /*+ use_hash(actv) use_hash(intr) use_hash(mitm) use_hash(irc)
PARALLEL(actv) PARALLEL(intr) PARALLEL(mitm) PARALLEL(irc) */
nvl(mitm.source_id, -1) EMAIL_ACCOUNT_ID,
nvl(irc.route_classification_id, -1) EMAIL_CLASSIFICATION_ID,
nvl(intr.resource_id, -1) AGENT_ID,
nvl(intr.party_id, -1) PARTY_ID,
trunc(actv.start_date_time) PERIOD_START_DATE,
to_number(to_char(actv.start_date_time, 'J')) TIME_ID,
--1/2hr
--nvl(lpad(to_char(actv.start_date_time,'HH24:'),3,'0') ||
-- decode(sign(to_number(to_char(actv.start_date_time,'MI'))-29),0,'00',1,'30',-1,'00'),'00:00')
'00:00'
PERIOD_START_TIME,
-1 OUTCOME_ID,
-1 RESULT_ID,
-1 REASON_ID,
0 EMAILS_OFFERED_IN_PERIOD,
0 EMAILS_FETCHED_IN_PERIOD,
0 EMAILS_REPLIED_IN_PERIOD,
0 AGENT_RESP_TIME_IN_PERIOD,
0 EMAILS_RPLD_BY_GOAL_IN_PERIOD,
0 AGENT_EMAILS_RPLD_BY_GOAL,
0 EMAILS_TRNSFRD_OUT_IN_PERIOD,
0 EMAILS_TRNSFRD_IN_IN_PERIOD,
0 EMAILS_ASSIGNED_IN_PERIOD,
0 EMAILS_AUTO_ROUTED_IN_PERIOD,
0 EMAILS_AUTO_UPTD_SR_IN_PERIOD,
0 EMAILS_DELETED_IN_PERIOD,
0 EMAIL_RESP_TIME_IN_PERIOD,
SUM(DECODE(actv.action_id,13,1)) SR_CREATED_IN_PERIOD,
0 EMAILS_RSL_AND_TRFD_IN_PERIOD,
0 EMAILS_AUTO_REPLIED_IN_PERIOD,
0 EMAILS_AUTO_DELETED_IN_PERIOD,
0 EMAILS_AUTO_RESOLVED_IN_PERIOD,
0 emails_composed_in_period,
0 emails_orr_count_in_period,
0 EMAILS_REROUTED_IN_PERIOD,
SUM(DECODE(actv.action_id,71,1)) LEADS_CREATED_IN_PERIOD
FROM
JTF_IH_ACTIVITIES actv,
JTF_IH_INTERACTIONS intr,
JTF_IH_MEDIA_ITEMS mitm,
(select
actv.interaction_id interaction_id,
max(mitm.classification) classification
from
jtf_ih_activities actv,
jtf_ih_activities actv1,
jtf_ih_media_items mitm
where actv.start_date_time BETWEEN g_collect_start_date AND g_collect_end_date
and actv.interaction_id = actv1.interaction_id
and actv1.media_id = mitm.media_id
and mitm.direction = 'INBOUND'
and mitm.media_item_type = 'EMAIL'
group by actv.interaction_id ) inv2,
--
--Changes for R12
--
(
select name, max(route_classification_id) route_classification_id
from iem_route_classifications
group by name
) irc
WHERE actv.start_date_time BETWEEN g_collect_start_date AND g_collect_end_date
AND ( ( actv.action_id = 13 AND actv.action_item_id = 17 ) OR
( actv.action_id = 71 AND actv.action_item_id = 8 )
)
AND actv.media_id = mitm.media_id
AND mitm.MEDIA_ITEM_TYPE = 'EMAIL'
AND inv2.classification = irc.name(+)
AND actv.interaction_id = intr.interaction_id
AND actv.interaction_id = inv2.interaction_id(+)
GROUP BY
nvl(mitm.source_id, -1),
nvl(irc.route_classification_id, -1),
intr.resource_id,
nvl(intr.party_id, -1),
trunc(actv.start_date_time),
to_number(to_char(actv.start_date_time, 'J'))
--1/2hr
--nvl(lpad(to_char(actv.start_date_time,'HH24:'),3,'0') ||
-- decode(sign(to_number(to_char(actv.start_date_time,'MI'))-29),0,'00',1,'30',-1,'00'),'00:00')
UNION ALL -- This sql counts the number of emails resolved and transferred within the period grouped together by a day
SELECT /*+ use_hash(inv2) use_hash(mitm) use_hash(mseg) use_hash(mtyp) use_hash(irc)
PARALLEL(inv2) PARALLEL(mitm) PARALLEL(mseg) PARALLEL(mtyp) PARALLEL(irc) */
nvl(mitm.source_id, -1) EMAIL_ACCOUNT_ID,
nvl(irc.route_classification_id, -1) EMAIL_CLASSIFICATION_ID,
nvl(mseg.resource_id, -1) AGENT_ID,
nvl(inv2.party_id, -1) PARTY_ID,
trunc(mseg.start_date_time) PERIOD_START_DATE,
to_number(to_char(mseg.start_date_time, 'J')) TIME_ID,
--1/2hr
--nvl(lpad(to_char(mseg.start_date_time,'HH24:'),3,'0') ||
-- decode(sign(to_number(to_char(mseg.start_date_time,'MI'))-29),0,'00',1,'30',-1,'00'),'00:00')
'00:00'
PERIOD_START_TIME,
-1 OUTCOME_ID,
-1 RESULT_ID,
-1 REASON_ID,
0 EMAILS_OFFERED_IN_PERIOD,
0 EMAILS_FETCHED_IN_PERIOD,
0 EMAILS_REPLIED_IN_PERIOD,
0 AGENT_RESP_TIME_IN_PERIOD,
0 EMAILS_RPLD_BY_GOAL_IN_PERIOD,
0 AGENT_EMAILS_RPLD_BY_GOAL,
0 EMAILS_TRNSFRD_OUT_IN_PERIOD,
0 EMAILS_TRNSFRD_IN_IN_PERIOD,
0 EMAILS_ASSIGNED_IN_PERIOD,
0 EMAILS_AUTO_ROUTED_IN_PERIOD,
0 EMAILS_AUTO_UPTD_SR_IN_PERIOD,
0 EMAILS_DELETED_IN_PERIOD,
0 EMAIL_RESP_TIME_IN_PERIOD,
0 SR_CREATED_IN_PERIOD,
count(*) EMAILS_RSL_AND_TRFD_IN_PERIOD,
0 EMAILS_AUTO_REPLIED_IN_PERIOD,
0 EMAILS_AUTO_DELETED_IN_PERIOD,
0 EMAILS_AUTO_RESOLVED_IN_PERIOD,
0 emails_composed_in_period,
0 emails_orr_count_in_period,
0 EMAILS_REROUTED_IN_PERIOD,
0 LEADS_CREATED_IN_PERIOD
FROM
(
SELECT /*+ use_hash(actv) use_hash(intr) PARALLEL(actv) PARALLEL(intr) */
actv.media_id media_id,
min(intr.party_id) party_id
FROM
jtf_ih_activities actv,
jtf_ih_interactions intr
WHERE actv.start_date_time BETWEEN g_collect_start_date AND g_collect_end_date
AND actv.interaction_id = intr.interaction_id
GROUP BY actv.media_id
) inv2,
JTF_IH_MEDIA_ITEMS mitm,
JTF_IH_MEDIA_ITEM_LC_SEGS mseg,
JTF_IH_MEDIA_ITM_LC_SEG_TYS mtyp,
--
--Changes for R12
--
(
select name, max(route_classification_id) route_classification_id
from iem_route_classifications
group by name
) irc
WHERE mitm.MEDIA_ITEM_TYPE = 'EMAIL'
AND mitm.DIRECTION = 'INBOUND'
AND mitm.classification = irc.name(+)
AND mitm.media_id = inv2.media_id(+)
AND mitm.MEDIA_ID = mseg.MEDIA_ID
AND mseg.MILCS_TYPE_ID = mtyp.MILCS_TYPE_ID
AND mtyp.MILCS_CODE IN ('EMAIL_REPLY', 'EMAIL_DELETED')
AND mseg.START_DATE_TIME BETWEEN g_collect_start_date and g_collect_end_date
AND EXISTS (
SELECT /*+ use_hash(mseg1) use_hash(mtys1) PARALLEL(mseg1) PARALLEL(mtys1) */
1
FROM
jtf_ih_media_item_lc_segs mseg1,
jtf_ih_media_itm_lc_seg_tys mtys1
WHERE mseg1.media_id = mitm.media_id
AND mtys1.milcs_type_id = mseg1.milcs_type_id
AND mtys1.milcs_code IN ( 'EMAIL_TRANSFERRED','EMAIL_ESCALATED') )
GROUP BY
nvl(mitm.source_id, -1),
nvl(irc.route_classification_id, -1),
nvl(mseg.resource_id, -1),
nvl(inv2.party_id, -1),
trunc(mseg.start_date_time),
to_number(to_char(mseg.start_date_time, 'J'))
--1/2hr
--nvl(lpad(to_char(mseg.start_date_time,'HH24:'),3,'0') ||
-- decode(sign(to_number(to_char(mseg.start_date_time,'MI'))-29),0,'00',1,'30',-1,'00'),'00:00')
UNION ALL -- This sql segment collects inbound email outcome count
SELECT /*+ use_hash(inv2) use_hash(mitm) use_hash(mseg) use_hash(mtyp) use_hash(irc)
PARALLEL(inv2) PARALLEL(mitm) PARALLEL(mseg) PARALLEL(mtyp) PARALLEL(irc)
use_hash(actv) use_hash(intr) PARALLEL(actv) PARALLEL(intr) */
nvl(mitm.source_id, -1) EMAIL_ACCOUNT_ID,
nvl(irc.route_classification_id, -1) EMAIL_CLASSIFICATION_ID,
nvl(intr.resource_id, -1) AGENT_ID,
nvl(intr.party_id, -1) PARTY_ID,
trunc(intr.last_update_date) PERIOD_START_DATE,
to_number(to_char(intr.last_update_date, 'J')) TIME_ID,
--1/2hr
--nvl(lpad(to_char(intr.last_update_date,'HH24:'),3,'0') ||
-- decode(sign(to_number(to_char(intr.last_update_date,'MI'))-29),0,'00',1,'30',-1,'00'),'00:00')
--Replacing with 00:00 since we are going to ignore 1/2 hr segments
'00:00' PERIOD_START_TIME,
NVL(intr.outcome_id,-1) OUTCOME_ID,
NVL(intr.result_id,-1) RESULT_ID,
NVL(intr.reason_id,-1) REASON_ID,
0 EMAILS_OFFERED_IN_PERIOD,
0 EMAILS_FETCHED_IN_PERIOD,
0 EMAILS_REPLIED_IN_PERIOD,
0 AGENT_RESP_TIME_IN_PERIOD,
0 EMAILS_RPLD_BY_GOAL_IN_PERIOD,
0 AGENT_EMAILS_RPLD_BY_GOAL,
0 EMAILS_TRNSFRD_OUT_IN_PERIOD,
0 EMAILS_TRNSFRD_IN_IN_PERIOD,
0 EMAILS_ASSIGNED_IN_PERIOD,
0 EMAILS_AUTO_ROUTED_IN_PERIOD,
0 EMAILS_AUTO_UPTD_SR_IN_PERIOD,
0 EMAILS_DELETED_IN_PERIOD,
0 EMAIL_RESP_TIME_IN_PERIOD,
0 SR_CREATED_IN_PERIOD,
0 EMAILS_RSL_AND_TRFD_IN_PERIOD,
0 EMAILS_AUTO_REPLIED_IN_PERIOD,
0 EMAILS_AUTO_DELETED_IN_PERIOD,
0 EMAILS_AUTO_RESOLVED_IN_PERIOD,
0 emails_composed_in_period,
COUNT(DISTINCT intr.interaction_id) emails_orr_count_in_period,
0 EMAILS_REROUTED_IN_PERIOD,
0 LEADS_CREATED_IN_PERIOD
FROM
JTF_IH_MEDIA_ITEMS mitm,
JTF_IH_ACTIVITIES actv,
JTF_IH_INTERACTIONS intr,
--
--Changes for R12
--
(
select name, max(route_classification_id) route_classification_id
from iem_route_classifications
group by name
) irc
WHERE mitm.MEDIA_ITEM_TYPE = 'EMAIL'
AND mitm.direction = 'INBOUND'
AND mitm.classification = irc.name(+)
AND mitm.media_id = actv.media_id
AND actv.interaction_id = intr.interaction_id
AND intr.LAST_UPDATE_DATE BETWEEN g_collect_start_date and g_collect_end_date
AND intr.outcome_id IS NOT NULL
GROUP BY
nvl(mitm.source_id, -1),
nvl(irc.route_classification_id, -1),
nvl(intr.resource_id, -1),
nvl(intr.party_id, -1),
trunc(intr.last_update_date),
to_number(to_char(intr.last_update_date, 'J')),
--1/2hr
--nvl(lpad(to_char(intr.last_update_date,'HH24:'),3,'0') ||
--decode(sign(to_number(to_char(intr.last_update_date,'MI'))-29),0,'00',1,'30',-1,'00'),'00:00'),
NVL(intr.outcome_id,-1),
NVL(intr.result_id,-1),
NVL(intr.reason_id,-1)
UNION ALL -- This sql segment collects outbound email outcome count
SELECT /*+ use_hash(inv2) use_hash(mitm) use_hash(mseg) use_hash(mtyp) use_hash(irc)
PARALLEL(inv2) PARALLEL(mitm) PARALLEL(mseg) PARALLEL(mtyp) PARALLEL(irc)
use_hash(actv) use_hash(intr) PARALLEL(actv) PARALLEL(intr) */
nvl(mitm.source_id, -1) EMAIL_ACCOUNT_ID,
nvl(irc.route_classification_id, -1) EMAIL_CLASSIFICATION_ID,
nvl(intr.resource_id, -1) AGENT_ID,
nvl(intr.party_id, -1) PARTY_ID,
trunc(intr.last_update_date) PERIOD_START_DATE,
to_number(to_char(intr.last_update_date, 'J')) TIME_ID,
--1/2hr
--nvl(lpad(to_char(intr.last_update_date,'HH24:'),3,'0') ||
-- decode(sign(to_number(to_char(intr.last_update_date,'MI'))-29),0,'00',1,'30',-1,'00'),'00:00')
'00:00' PERIOD_START_TIME,
NVL(intr.outcome_id,-1) OUTCOME_ID,
NVL(intr.result_id,-1) RESULT_ID,
NVL(intr.reason_id,-1) REASON_ID,
0 EMAILS_OFFERED_IN_PERIOD,
0 EMAILS_FETCHED_IN_PERIOD,
0 EMAILS_REPLIED_IN_PERIOD,
0 AGENT_RESP_TIME_IN_PERIOD,
0 EMAILS_RPLD_BY_GOAL_IN_PERIOD,
0 AGENT_EMAILS_RPLD_BY_GOAL,
0 EMAILS_TRNSFRD_OUT_IN_PERIOD,
0 EMAILS_TRNSFRD_IN_IN_PERIOD,
0 EMAILS_ASSIGNED_IN_PERIOD,
0 EMAILS_AUTO_ROUTED_IN_PERIOD,
0 EMAILS_AUTO_UPTD_SR_IN_PERIOD,
0 EMAILS_DELETED_IN_PERIOD,
0 EMAIL_RESP_TIME_IN_PERIOD,
0 SR_CREATED_IN_PERIOD,
0 EMAILS_RSL_AND_TRFD_IN_PERIOD,
0 EMAILS_AUTO_REPLIED_IN_PERIOD,
0 EMAILS_AUTO_DELETED_IN_PERIOD,
0 EMAILS_AUTO_RESOLVED_IN_PERIOD,
COUNT(distinct mitm.media_id) emails_composed_in_period,
COUNT(DISTINCT intr.interaction_id) emails_orr_count_in_period,
0 EMAILS_REROUTED_IN_PERIOD,
0 LEADS_CREATED_IN_PERIOD
FROM
JTF_IH_MEDIA_ITEMS mitm,
JTF_IH_MEDIA_ITEM_LC_SEGS segs,
JTF_IH_MEDIA_ITM_LC_SEG_TYS seg_type,
JTF_IH_ACTIVITIES actv,
JTF_IH_INTERACTIONS intr,
--
--Changes for R12
--
(
select name, max(route_classification_id) route_classification_id
from iem_route_classifications
group by name
) irc
WHERE mitm.MEDIA_ITEM_TYPE = 'EMAIL'
AND mitm.direction = 'OUTBOUND'
AND mitm.media_id = segs.media_id
AND segs.milcs_type_id = seg_type.milcs_type_id
AND seg_type.milcs_code = 'EMAIL_COMPOSE'
AND mitm.classification = irc.name(+)
AND mitm.media_id = actv.media_id
AND actv.interaction_id = intr.interaction_id
AND intr.LAST_UPDATE_DATE BETWEEN g_collect_start_date and g_collect_end_date
AND intr.outcome_id IS NOT NULL
GROUP BY
nvl(mitm.source_id, -1),
nvl(irc.route_classification_id, -1),
nvl(intr.resource_id, -1),
nvl(intr.party_id, -1),
trunc(intr.last_update_date),
to_number(to_char(intr.last_update_date, 'J')),
--1/2hr
--nvl(lpad(to_char(intr.last_update_date,'HH24:'),3,'0') ||
--decode(sign(to_number(to_char(intr.last_update_date,'MI'))-29),0,'00',1,'30',-1,'00'),'00:00'),
NVL(intr.outcome_id,-1),
NVL(intr.result_id,-1),
NVL(intr.reason_id,-1)
) inv2
GROUP BY
inv2.email_account_id,
inv2.email_classification_id,
inv2.agent_id,
inv2.party_id,
inv2.time_id,
inv2.period_start_date,
inv2.period_start_time,
inv2.outcome_id,
inv2.result_id,
inv2.reason_id);
write_log('Number of rows inserted in table bix_email_details_f : ' || to_char(SQL%ROWCOUNT));
/* Estimating statistics as we are going to update these rows for the open measures */
DBMS_STATS.gather_table_stats(ownname => g_bix_schema,
tabName => 'BIX_EMAIL_DETAILS_F',
cascade => TRUE,
degree => bis_common_parameters.get_degree_of_parallelism,
estimate_percent => 10,
granularity => 'GLOBAL');
PROCEDURE update_queue_measure (p_email_account_id IN OUT NOCOPY g_email_account_id_tab,
p_email_classification_id IN OUT NOCOPY g_email_classification_id_tab,
p_party_id IN OUT NOCOPY g_party_id_tab,
p_period_start_date IN OUT NOCOPY g_period_start_date_tab,
p_emails_in_queue IN OUT NOCOPY g_emails_in_queue_tab,
p_total_queue_time IN OUT NOCOPY g_total_queue_time_tab,
p_oldest_message_in_queue IN OUT NOCOPY g_oldest_message_in_queue_tab,
p_acc_emails_one_day IN OUT NOCOPY g_acc_emails_one_day_tab,
p_acc_emails_three_days IN OUT NOCOPY g_acc_emails_three_days_tab,
p_acc_emails_week IN OUT NOCOPY g_acc_emails_week_tab,
p_acc_emails_week_plus IN OUT NOCOPY g_acc_emails_week_plus_tab) IS
BEGIN
write_log('Start of the procedure update_queue_measure at ' || to_char(sysdate,'mm/dd/yyyy hh24:mi:ss'));
/* Update ICI summary table for the queue measures */
FORALL i in p_email_account_id.FIRST .. p_email_account_id.LAST
MERGE INTO bix_email_details_f bed
USING (
SELECT
p_email_account_id(i) email_account_id,
p_email_classification_id(i) email_classification_id,
-1 agent_id,
p_party_id(i) party_id,
to_number(to_char(p_period_start_date(i), 'J')) time_id,
--1/2hr change the period type id to 1 from -1
1 period_type_id,
trunc(p_period_start_date(i)) period_start_date,
LPAD(TO_CHAR(p_period_start_date(i),'HH24:MI'), 5, '0') period_start_time,
nvl(p_emails_in_queue(i),0) accumulated_emails_in_queue,
nvl(p_total_queue_time(i),0) accumulated_queue_time,
p_oldest_message_in_queue(i) oldest_email_queue_date,
nvl(p_acc_emails_one_day(i),0) accumulated_emails_one_day,
nvl(p_acc_emails_three_days(i),0) accumulated_emails_three_days,
nvl(p_acc_emails_week(i),0) accumulated_emails_week,
nvl(p_acc_emails_week_plus(i),0) accumulated_emails_week_plus
FROM dual) change
ON (bed.email_account_id = change.email_account_id
AND bed.email_classification_id = change.email_classification_id
AND bed.agent_id = change.agent_id
AND bed.party_id = change.party_id
AND bed.time_id = change.time_id
AND bed.period_type_id = change.period_type_id
AND bed.period_start_date = change.period_start_date
AND bed.period_start_time = change.period_start_time
AND bed.outcome_id = -1 AND bed.result_id = -1 AND bed.reason_id = -1 )
WHEN MATCHED THEN
UPDATE
SET
bed.accumulated_emails_in_queue = decode(change.accumulated_emails_in_queue, 0, bed.accumulated_emails_in_queue,
NVL(bed.accumulated_emails_in_queue, 0) + change.accumulated_emails_in_queue),
bed.accumulated_queue_time = decode(change.accumulated_queue_time, 0, bed.accumulated_queue_time,
NVL(bed.accumulated_queue_time, 0) + change.accumulated_queue_time),
bed.accumulated_emails_one_day = decode(change.accumulated_emails_one_day, 0, bed.accumulated_emails_one_day,
NVL(bed.accumulated_emails_one_day, 0) + change.accumulated_emails_one_day),
bed.accumulated_emails_three_days = decode(change.accumulated_emails_three_days, 0, bed.accumulated_emails_three_days,
NVL(bed.accumulated_emails_three_days, 0) + change.accumulated_emails_three_days),
bed.accumulated_emails_week = decode(change.accumulated_emails_week, 0, bed.accumulated_emails_week,
NVL(bed.accumulated_emails_week, 0) + change.accumulated_emails_week),
bed.accumulated_emails_week_plus = decode(change.accumulated_emails_week_plus, 0, bed.accumulated_emails_week_plus,
NVL(bed.accumulated_emails_week_plus, 0) + change.accumulated_emails_week_plus),
bed.oldest_email_queue_date =
DECODE(change.oldest_email_queue_date,NULL,bed.oldest_email_queue_date,
DECODE(bed.oldest_email_queue_date,NULL,change.oldest_email_queue_date,
DECODE(SIGN(bed.oldest_email_queue_date - change.oldest_email_queue_date),
-1,bed.oldest_email_queue_date, change.oldest_email_queue_date))),
bed.last_update_date = g_sysdate,
bed.last_updated_by = g_user_id
WHEN NOT MATCHED THEN INSERT (
bed.email_account_id,
bed.email_classification_id,
bed.agent_id,
bed.party_id,
bed.outcome_id,
bed.result_id,
bed.reason_id,
bed.time_id,
bed.period_type_id,
bed.period_start_date,
bed.period_start_time,
bed.created_by,
bed.creation_date,
bed.last_updated_by,
bed.last_update_date,
bed.accumulated_emails_in_queue,
bed.accumulated_queue_time,
bed.oldest_email_queue_date,
bed.accumulated_emails_one_day,
bed.accumulated_emails_three_days,
bed.accumulated_emails_week,
bed.accumulated_emails_week_plus,
bed.request_id,
bed.program_application_id,
bed.program_id,
bed.program_update_date)
VALUES (
change.email_account_id,
change.email_classification_id,
change.agent_id,
change.party_id,
-1,
-1,
-1,
change.time_id,
change.period_type_id,
change.period_start_date,
change.period_start_time,
g_user_id,
g_sysdate,
g_user_id,
g_sysdate,
decode(change.accumulated_emails_in_queue, 0, to_number(null), change.accumulated_emails_in_queue),
decode(change.accumulated_queue_time, 0, to_number(null), change.accumulated_queue_time),
change.oldest_email_queue_date,
decode(change.accumulated_emails_one_day, 0, to_number(null), change.accumulated_emails_one_day),
decode(change.accumulated_emails_three_days, 0, to_number(null), change.accumulated_emails_three_days),
decode(change.accumulated_emails_week, 0, to_number(null), change.accumulated_emails_week),
decode(change.accumulated_emails_week_plus, 0, to_number(null), change.accumulated_emails_week_plus),
g_request_id,
g_program_appl_id,
g_program_id,
g_sysdate);
write_log('Finished procedure update_queue_measure at ' || to_char(sysdate,'mm/dd/yyyy hh24:mi:ss'));
write_log('Error in update_queue_measure : Error : ' || sqlerrm);
END update_queue_measure;
This splits the data to mutliple time buckets. Then it calls update_queue_measures
which does the actual MERGE of the data
***/
PROCEDURE process_queue_measure_row(p_media_id IN OUT NOCOPY g_media_id_tab,
p_email_account_id IN OUT NOCOPY g_email_account_id_tab,
p_email_classification_id IN OUT NOCOPY g_email_classification_id_tab,
p_party_id IN OUT NOCOPY g_party_id_tab,
p_start_date_time IN OUT NOCOPY g_start_date_time_tab,
p_end_date_time IN OUT NOCOPY g_end_date_time_tab,
p_media_start_date_time IN OUT NOCOPY g_media_start_date_time_tab,
p_period_start_date_time IN OUT NOCOPY g_start_date_time_tab,
p_period_end_date_time IN OUT NOCOPY g_end_date_time_tab)
IS
l_email_account_id g_email_account_id_tab;
update_queue_measure (l_email_account_id,
l_email_classification_id,
l_party_id,
l_period_start_date,
l_emails_in_queue,
l_total_queue_time,
l_oldest_message_in_queue,
l_acc_emails_one_day,
l_acc_emails_three_days,
l_acc_emails_week,
l_acc_emails_week_plus);
update_queue_measure (l_email_account_id,
l_email_classification_id,
l_party_id,
l_period_start_date,
l_emails_in_queue,
l_total_queue_time,
l_oldest_message_in_queue,
l_acc_emails_one_day,
l_acc_emails_three_days,
l_acc_emails_week,
l_acc_emails_week_plus);
be inserted one for each day with accumulated_emails_in_queue as 1 for both days.
If there is no emails in queue for particular combination dimensions
this procedure populates null.
====================================================================+*/
PROCEDURE collect_queue_measures AS
CURSOR queue_measures IS
--
--This is the query for collecting emails which are still in system queue
--PERIOD START DATE will be either global start date or the start of the media segment
--PERIOD END DATE will be the g_collect_end_date since the emails are still in
--QUEUE.
--
SELECT /*+ use_hash(mitm) use_hash(mseg) use_hash(mtys) use_hash(cls) use_hash(inv2) */
mitm.media_id MEDIA_ID,
nvl(mitm.source_id, -1) EMAIL_ACCOUNT_ID,
nvl(cls.route_classification_id, -1) EMAIL_CLASSIFICATION_ID,
nvl(inv2.party_id, -1) PARTY_ID,
mseg.start_date_time START_DATE_TIME,
g_collect_end_date END_DATE_TIME,
mitm.start_date_time MEDIA_START_DATE_TIME,
--1/2hr
decode(sign(mseg.start_date_time - g_collect_start_date), -1, g_collect_start_date,
to_date(to_char(mseg.start_date_time ,'YYYY/MM/DD ')|| '00:'
|| '00',
'YYYY/MM/DD HH24:MI')) PERIOD_START_DATE_TIME,
--1/2hr do we need to modify the collect end data to capture data upto the second?
g_collect_end_date -- - (( 29 * 60 + 59 )/(24*60*60))
PERIOD_END_DATE_TIME
FROM
JTF_IH_MEDIA_ITEMS mitm,
JTF_IH_MEDIA_ITEM_LC_SEGS mseg,
JTF_IH_MEDIA_ITM_LC_SEG_TYS mtys,
--
--Changes for R12
--
(
select name, max(route_classification_id) route_classification_id
from iem_route_classifications
group by name
) cls,
(
--????Changed for party_id
--SELECT /*+ use_hash(actv) use_hash(intr) */
--actv.media_id media_id,
--min(intr.party_id) party_id
--FROM
--jtf_ih_activities actv,
--jtf_ih_interactions intr
--WHERE actv.start_date_time BETWEEN g_collect_start_date AND g_collect_end_date
--AND actv.interaction_id = intr.interaction_id
--GROUP BY actv.media_id
SELECT /*+ use_hash(actv) use_hash(intr) */
distinct actv.media_id media_id,
first_value(intr.party_id)
over(partition by actv.media_id order by actv.interaction_id desc) party_id
FROM
jtf_ih_activities actv,
jtf_ih_interactions intr
WHERE actv.start_date_time BETWEEN g_collect_start_date AND g_collect_end_date
AND actv.interaction_id = intr.interaction_id
) inv2
WHERE mitm.MEDIA_ITEM_TYPE = 'EMAIL'
AND mitm.DIRECTION = 'INBOUND'
AND mitm.classification = cls.name(+)
AND mitm.MEDIA_ID = mseg.MEDIA_ID
AND mseg.START_DATE_TIME < g_collect_end_date
AND mseg.MILCS_TYPE_ID = mtys.MILCS_TYPE_ID
AND mtys.MILCS_CODE IN ('EMAIL_PROCESSING') /* Requeued segment removed for bug 5337716 */
AND mitm.media_id = inv2.media_id
AND NOT EXISTS
(
SELECT /*+ use_hash(mseg1) use_hash(mtys1) */
1
FROM JTF_IH_MEDIA_ITEM_LC_SEGS mseg1,
JTF_IH_MEDIA_ITM_LC_SEG_TYS mtys1
WHERE mseg.MEDIA_ID = mseg1.MEDIA_ID
AND mseg1.MILCS_TYPE_ID = mtys1.MILCS_TYPE_ID
AND mtys1.MILCS_CODE IN
('EMAIL_FETCH', 'EMAIL_RESOLVED', 'EMAIL_AUTO_REDIRECTED', 'EMAIL_AUTO_DELETED',
'EMAIL_AUTO_REPLY', 'EMAIL_OPEN', 'EMAIL_AUTO_ROUTED', 'EMAIL_AUTO_UPDATED_SR',
'EMAIL_ASSIGNED', 'EMAIL_ASSIGN_OPEN','EMAIL_DELETED')
--
--This condition is required since the email might have been fetched once but
--then re-queued. In this case the email is still in QUEUE and wihout the following
--condition will miss the record
--
AND mseg1.START_DATE_TIME >= mseg.START_DATE_TIME
AND mseg1.START_DATE_TIME < g_collect_end_date
)
UNION
--
--This query is for emails which were in QUEUE for some time but are no longer in QUEUE
--
SELECT /*+ use_hash(mitm2) use_hash(inv2) use_hash(inv1) use_hash(mseg2) use_hash(mtys2) use_hash(cls2) */
mitm2.media_id MEDIA_ID,
nvl(mitm2.source_id, -1) EMAIL_ACCOUNT_ID,
nvl(cls2.route_classification_id, -1) EMAIL_CLASSIFICATION_ID,
nvl(inv1.party_id, -1) PARTY_ID,
mseg2.start_date_time START_DATE_TIME,
min(inv2.start_date_time) END_DATE_TIME,
mitm2.start_date_time MEDIA_START_DATE_TIME,
--1/2hr
decode(sign(mseg2.start_date_time - g_collect_start_date), -1, g_collect_start_date,
to_date(to_char(mseg2.start_date_time ,'YYYY/MM/DD ')|| '00:'
|| '00',
'YYYY/MM/DD HH24:MI')) PERIOD_START_DATE_TIME,
--1/2hr removed 1/2 hr lag in collect time
decode(sign(g_collect_end_date - min(inv2.start_date_time)), -1, g_collect_end_date, -- - (( 29 * 60 + 59 )/(24*60*60))
to_date(to_char(min(inv2.start_date_time) ,'YYYY/MM/DD ')|| '00:'
|| '00',
'YYYY/MM/DD HH24:MI')) PERIOD_END_DATE_TIME
FROM
JTF_IH_MEDIA_ITEMS mitm2,
(
SELECT /*+ use_hash(mseg3) use_hash(mtys3) */
mseg3.media_id,
mseg3.resource_id,
mseg3.start_date_time
FROM JTF_IH_MEDIA_ITEM_LC_SEGS mseg3,
JTF_IH_MEDIA_ITM_LC_SEG_TYS mtys3
WHERE mseg3.MILCS_TYPE_ID = mtys3.MILCS_TYPE_ID
AND mtys3.MILCS_CODE IN
('EMAIL_FETCH', 'EMAIL_RESOLVED', 'EMAIL_AUTO_REDIRECTED', 'EMAIL_AUTO_DELETED',
'EMAIL_AUTO_REPLY', 'EMAIL_OPEN', 'EMAIL_AUTO_ROUTED', 'EMAIL_AUTO_UPDATED_SR',
'EMAIL_ASSIGNED', 'EMAIL_ASSIGN_OPEN','EMAIL_DELETED')
AND mseg3.START_DATE_TIME BETWEEN g_collect_start_date AND g_collect_end_date
) inv2,
(
--????Change for party_id
--SELECT /*+ use_hash(actv) use_hash(intr) */
--actv.media_id media_id,
--min(intr.party_id) party_id
--FROM
--jtf_ih_activities actv,
--jtf_ih_interactions intr
--WHERE actv.start_date_time BETWEEN g_collect_start_date AND g_collect_end_date
--AND actv.interaction_id = intr.interaction_id
--GROUP BY actv.media_id
SELECT /*+ use_hash(actv) use_hash(intr) */
distinct actv.media_id media_id,
first_value(intr.party_id)
over(partition by actv.media_id order by actv.interaction_id desc) party_id
FROM
jtf_ih_activities actv,
jtf_ih_interactions intr
WHERE actv.start_date_time BETWEEN g_collect_start_date AND g_collect_end_date
AND actv.interaction_id = intr.interaction_id
) inv1,
JTF_IH_MEDIA_ITEM_LC_SEGS mseg2,
JTF_IH_MEDIA_ITM_LC_SEG_TYS mtys2,
--
--Changes for R12
--
(
select name, max(route_classification_id) route_classification_id
from iem_route_classifications
group by name
) cls2
WHERE mitm2.MEDIA_ITEM_TYPE = 'EMAIL'
AND mitm2.DIRECTION = 'INBOUND'
AND mitm2.classification = cls2.name(+)
AND mitm2.MEDIA_ID = inv2.MEDIA_ID
AND inv2.MEDIA_ID = mseg2.MEDIA_ID
AND mseg2.START_DATE_TIME < g_collect_end_date
AND mseg2.MILCS_TYPE_ID = mtys2.MILCS_TYPE_ID
AND inv2.START_DATE_TIME >= mseg2.START_DATE_TIME
AND mitm2.media_id = inv1.media_id --???test with outer join removed here
AND mtys2.MILCS_CODE IN ('EMAIL_PROCESSING')/* Requeued segment removed for bug 5337716 */
GROUP BY
mitm2.media_id,
nvl(mitm2.source_id, -1),
nvl(cls2.route_classification_id, -1),
nvl(inv1.party_id, -1),
mseg2.start_date_time,
mitm2.start_date_time;
/* Again estimating statistics here as these rows will be updated by open and resolution measures */
DBMS_STATS.gather_table_stats(ownname => g_bix_schema,
tabName => 'BIX_EMAIL_DETAILS_F',
cascade => TRUE,
degree => bis_common_parameters.get_degree_of_parallelism,
estimate_percent => 10,
granularity => 'GLOBAL');
PROCEDURE update_open_measure (p_email_account_id IN OUT NOCOPY g_email_account_id_tab,
p_email_classification_id IN OUT NOCOPY g_email_classification_id_tab,
p_party_id IN OUT NOCOPY g_party_id_tab,
p_agent_id IN OUT NOCOPY g_resource_id_tab,
p_period_start_date IN OUT NOCOPY g_period_start_date_tab,
p_emails_open IN OUT NOCOPY g_emails_open_tab,
p_total_open_age IN OUT NOCOPY g_total_open_age_tab,
p_oldest_open_message IN OUT NOCOPY g_oldest_open_message_tab,
p_acc_emails_one_day IN OUT NOCOPY g_acc_emails_one_day_tab,
p_acc_emails_three_days IN OUT NOCOPY g_acc_emails_three_days_tab,
p_acc_emails_week IN OUT NOCOPY g_acc_emails_week_tab,
p_acc_emails_week_plus IN OUT NOCOPY g_acc_emails_week_plus_tab) IS
BEGIN
write_log('Start of the procedure update_open_measure at ' || to_char(sysdate,'mm/dd/yyyy hh24:mi:ss'));
/* Update ICI summary table with open measure */
FORALL i in p_email_account_id.FIRST .. p_email_account_id.LAST
MERGE INTO bix_email_details_f bed
USING (
SELECT
p_email_account_id(i) email_account_id,
p_email_classification_id(i) email_classification_id,
p_agent_id(i) agent_id,
p_party_id(i) party_id,
to_number(to_char(p_period_start_date(i), 'J')) time_id,
--1/2hr changed period type id to 1 from -1
1 period_type_id,
trunc(p_period_start_date(i)) period_start_date,
LPAD(TO_CHAR(p_period_start_date(i),'HH24:MI'), 5, '0') period_start_time,
nvl(p_emails_open(i),0) accumulated_open_emails,
nvl(p_total_open_age(i),0) accumulated_open_age,
p_oldest_open_message(i) oldest_email_open_date,
nvl(p_acc_emails_one_day(i),0) accumulated_emails_one_day,
nvl(p_acc_emails_three_days(i),0) accumulated_emails_three_days,
nvl(p_acc_emails_week(i),0) accumulated_emails_week,
nvl(p_acc_emails_week_plus(i),0) accumulated_emails_week_plus
FROM dual) change
ON (bed.email_account_id = change.email_account_id
AND bed.email_classification_id = change.email_classification_id
AND bed.agent_id = change.agent_id
AND bed.party_id = change.party_id
AND bed.time_id = change.time_id
AND bed.period_type_id = change.period_type_id
AND bed.period_start_date = change.period_start_date
AND bed.period_start_time = change.period_start_time
AND bed.outcome_id = -1 AND bed.result_id = -1 AND bed.reason_id = -1 )
WHEN MATCHED THEN
UPDATE
SET
bed.accumulated_open_emails = decode(change.accumulated_open_emails, 0, bed.accumulated_open_emails,
NVL(bed.accumulated_open_emails, 0) + change.accumulated_open_emails),
bed.accumulated_open_age = decode(change.accumulated_open_age, 0, bed.accumulated_open_age,
NVL(bed.accumulated_open_age, 0) + change.accumulated_open_age),
bed.accumulated_emails_one_day = decode(change.accumulated_emails_one_day, 0, bed.accumulated_emails_one_day,
NVL(bed.accumulated_emails_one_day, 0) + change.accumulated_emails_one_day),
bed.accumulated_emails_three_days = decode(change.accumulated_emails_three_days, 0, bed.accumulated_emails_three_days,
NVL(bed.accumulated_emails_three_days, 0) + change.accumulated_emails_three_days),
bed.accumulated_emails_week = decode(change.accumulated_emails_week, 0, bed.accumulated_emails_week,
NVL(bed.accumulated_emails_week, 0) + change.accumulated_emails_week),
bed.accumulated_emails_week_plus = decode(change.accumulated_emails_week_plus, 0, bed.accumulated_emails_week_plus,
NVL(bed.accumulated_emails_week_plus, 0) + change.accumulated_emails_week_plus),
bed.oldest_email_open_date =
DECODE(change.oldest_email_open_date,NULL,bed.oldest_email_open_date,
DECODE(bed.oldest_email_open_date,NULL,change.oldest_email_open_date,
DECODE(SIGN(bed.oldest_email_open_date - change.oldest_email_open_date),
-1,bed.oldest_email_open_date, change.oldest_email_open_date))),
bed.last_update_date = g_sysdate,
bed.last_updated_by = g_user_id
WHEN NOT MATCHED THEN INSERT (
bed.email_account_id,
bed.email_classification_id,
bed.agent_id,
bed.party_id,
bed.outcome_id,
bed.result_id,
bed.reason_id,
bed.time_id,
bed.period_type_id,
bed.period_start_date,
bed.period_start_time,
bed.created_by,
bed.creation_date,
bed.last_updated_by,
bed.last_update_date,
bed.accumulated_open_emails,
bed.accumulated_open_age,
bed.oldest_email_open_date,
bed.accumulated_emails_one_day,
bed.accumulated_emails_three_days,
bed.accumulated_emails_week,
bed.accumulated_emails_week_plus,
bed.request_id,
bed.program_application_id,
bed.program_id,
bed.program_update_date)
VALUES (
change.email_account_id,
change.email_classification_id,
change.agent_id,
change.party_id,
-1,
-1,
-1,
change.time_id,
change.period_type_id,
change.period_start_date,
change.period_start_time,
g_user_id,
g_sysdate,
g_user_id,
g_sysdate,
decode(change.accumulated_open_emails, 0, to_number(null), change.accumulated_open_emails),
decode(change.accumulated_open_age, 0, to_number(null), change.accumulated_open_age),
change.oldest_email_open_date,
decode(change.accumulated_emails_one_day, 0, to_number(null), change.accumulated_emails_one_day),
decode(change.accumulated_emails_three_days, 0, to_number(null), change.accumulated_emails_three_days),
decode(change.accumulated_emails_week, 0, to_number(null), change.accumulated_emails_week),
decode(change.accumulated_emails_week_plus, 0, to_number(null), change.accumulated_emails_week_plus),
g_request_id,
g_program_appl_id,
g_program_id,
g_sysdate);
write_log('Finished procedure update_open_measure at ' || to_char(sysdate,'mm/dd/yyyy hh24:mi:ss'));
write_log('Error in update_open_measure : Error : ' || sqlerrm);
END update_open_measure;
This splits the data to mutliple time buckets. Then it calls update_open_measures
which does the actual MERGE of the data
*/
PROCEDURE process_open_measure_row(p_media_id IN OUT NOCOPY g_media_id_tab,
p_email_account_id IN OUT NOCOPY g_email_account_id_tab,
p_email_classification_id IN OUT NOCOPY g_email_classification_id_tab,
p_resource_id IN OUT NOCOPY g_resource_id_tab,
p_party_id IN OUT NOCOPY g_party_id_tab,
p_start_date_time IN OUT NOCOPY g_start_date_time_tab,
p_end_date_time IN OUT NOCOPY g_end_date_time_tab,
p_media_start_date_time IN OUT NOCOPY g_media_start_date_time_tab,
p_period_start_date_time IN OUT NOCOPY g_start_date_time_tab,
p_period_end_date_time IN OUT NOCOPY g_end_date_time_tab) IS
l_email_account_id g_email_account_id_tab;
update_open_measure (l_email_account_id,
l_email_classification_id,
l_party_id,
l_agent_id,
l_period_start_date,
l_emails_open,
l_total_open_age,
l_oldest_open_message,
l_acc_emails_one_day,
l_acc_emails_three_days,
l_acc_emails_week,
l_acc_emails_week_plus);
update_open_measure (l_email_account_id,
l_email_classification_id,
l_party_id,
l_agent_id,
l_period_start_date,
l_emails_open,
l_total_open_age,
l_oldest_open_message,
l_acc_emails_one_day,
l_acc_emails_three_days,
l_acc_emails_week,
l_acc_emails_week_plus);
be inserted one for each day with accumulated_open_emails as 1 for both days.
================================================================================================*/
PROCEDURE collect_open_measures AS
CURSOR open_measures IS
SELECT /*+ use_hash(mitm) use_hash(mseg) use_hash(mtys) use_hash(cls) use_hash(inv2) */
mitm.media_id MEDIA_ID,
nvl(mitm.source_id, -1) EMAIL_ACCOUNT_ID,
nvl(cls.route_classification_id, -1) EMAIL_CLASSIFICATION_ID,
nvl(mseg.resource_id,-1) RESOURCE_ID,
nvl(inv2.party_id, -1) PARTY_ID,
mseg.start_date_time START_DATE_TIME,
g_collect_end_date END_DATE_TIME,
mitm.start_date_time MEDIA_START_DATE_TIME,
--1/2hr
decode(sign(mseg.start_date_time - g_collect_start_date), -1, g_collect_start_date,
to_date(to_char(mseg.start_date_time ,'YYYY/MM/DD ')|| '00:'
|| '00',
'YYYY/MM/DD HH24:MI')) PERIOD_START_DATE_TIME,
--1/2hr
g_collect_end_date
PERIOD_END_DATE_TIME
FROM
JTF_IH_MEDIA_ITEMS mitm,
JTF_IH_MEDIA_ITEM_LC_SEGS mseg,
JTF_IH_MEDIA_ITM_LC_SEG_TYS mtys,
--
--Changes for R12
--
(
select name, max(route_classification_id) route_classification_id
from iem_route_classifications
group by name
) cls,
(
--???Changed for party
--SELECT /*+ use_hash(actv) use_hash(intr) */
--actv.media_id media_id,
--min(intr.party_id) party_id
--FROM
--jtf_ih_activities actv,
--jtf_ih_interactions intr
--WHERE actv.start_date_time BETWEEN g_collect_start_date AND g_collect_end_date
--AND actv.interaction_id = intr.interaction_id
--GROUP BY actv.media_id
SELECT /*+ use_hash(actv) use_hash(intr) */
distinct actv.media_id media_id,
first_value(intr.party_id)
over(partition by actv.media_id order by actv.interaction_id desc) party_id
FROM
jtf_ih_activities actv,
jtf_ih_interactions intr
WHERE actv.start_date_time BETWEEN g_collect_start_date AND g_collect_end_date
AND actv.interaction_id = intr.interaction_id
) inv2
WHERE mitm.media_item_type = 'EMAIL'
AND mitm.direction = 'INBOUND'
AND mitm.classification = cls.name(+)
AND mitm.media_id = mseg.media_id
AND mseg.start_date_time < g_collect_end_date
AND mseg.milcs_type_id = mtys.milcs_type_id
AND mtys.milcs_code IN ('EMAIL_FETCH','EMAIL_TRANSFER', 'EMAIL_ASSIGN_OPEN', 'EMAIL_AUTO_ROUTED', 'EMAIL_ASSIGNED')
AND mitm.media_id = inv2.media_id --??? test with outer join removed here
AND NOT EXISTS
(
SELECT /*+ use_hash(mseg1) use_hash(mtys1) */
1
FROM JTF_IH_MEDIA_ITEM_LC_SEGS mseg1,
JTF_IH_MEDIA_ITM_LC_SEG_TYS mtys1
WHERE mseg.media_id = mseg1.media_id
-- AND mseg.resource_id = mseg1.resource_id
/* Commenting this join out because the supervisor can perform some of the operations below.
Irrespective of which user did it, the email is not open any more */
AND mseg1.milcs_type_id = mtys1.milcs_type_id
AND mtys1.milcs_code IN ('EMAIL_REPLY','EMAIL_DELETED','EMAIL_TRANSFERRED', 'EMAIL_ESCALATED', 'EMAIL_REQUEUED',
'EMAIL_ASSIGNED','EMAIL_REROUTED_DIFF_ACCT', 'EMAIL_REROUTED_DIFF_CLASS')
AND mseg1.START_DATE_TIME >= mseg.START_DATE_TIME
AND mseg1.start_date_time < g_collect_end_date
AND mseg1.milcs_id <> mseg.milcs_id
)
UNION
SELECT /*+ use_hash(mitm) use_hash(inv2) use_hash(inv1) use_hash(mseg) use_hash(mtys) use_hash(cls) */
mitm.media_id MEDIA_ID,
nvl(mitm.source_id, -1) EMAIL_ACCOUNT_ID,
nvl(cls.route_classification_id, -1) EMAIL_CLASSIFICATION_ID,
nvl(mseg.resource_id, -1) RESOURCE_ID,
nvl(inv1.party_id, -1) PARTY_ID,
mseg.start_date_time START_DATE_TIME,
min(inv2.start_date_time) END_DATE_TIME,
mitm.start_date_time MEDIA_START_DATE_TIME,
--1/2hr
decode(sign(mseg.start_date_time - g_collect_start_date), -1, g_collect_start_date,
to_date(to_char(mseg.start_date_time ,'YYYY/MM/DD ')|| '00:'
|| '00',
'YYYY/MM/DD HH24:MI')) PERIOD_START_DATE_TIME,
decode(sign(g_collect_end_date - min(inv2.start_date_time)), -1, g_collect_end_date - (( 29 * 60 + 59 )/(24*60*60)),
to_date(to_char(min(inv2.start_date_time) ,'YYYY/MM/DD ')|| '00:'
|| '00',
'YYYY/MM/DD HH24:MI')) PERIOD_END_DATE_TIME
FROM
JTF_IH_MEDIA_ITEMS mitm,
(
SELECT /*+ use_hash(mseg1) use_hash(mtys1) */
mseg1.media_id MEDIA_ID,
mseg1.resource_id RESOURCE_ID,
mseg1.start_date_time START_DATE_TIME
FROM JTF_IH_MEDIA_ITEM_LC_SEGS mseg1,
JTF_IH_MEDIA_ITM_LC_SEG_TYS mtys1
WHERE mseg1.MILCS_TYPE_ID = mtys1.MILCS_TYPE_ID
AND mtys1.MILCS_CODE IN ('EMAIL_REPLY','EMAIL_DELETED','EMAIL_TRANSFERRED', 'EMAIL_ESCALATED',
'EMAIL_ASSIGNED','EMAIL_REQUEUED', 'EMAIL_REROUTED_DIFF_ACCT', 'EMAIL_REROUTED_DIFF_CLASS')
AND mseg1.START_DATE_TIME BETWEEN g_collect_start_date AND g_collect_end_date
) inv2,
(
--????Change for party_id
--SELECT /*+ use_hash(actv) use_hash(intr) */
--actv.media_id media_id,
--min(intr.party_id) party_id
--FROM
--jtf_ih_activities actv,
--jtf_ih_interactions intr
--WHERE actv.start_date_time BETWEEN g_collect_start_date AND g_collect_end_date
--AND actv.interaction_id = intr.interaction_id
--GROUP BY actv.media_id
SELECT /*+ use_hash(actv) use_hash(intr) */
distinct actv.media_id media_id,
first_value(intr.party_id)
over(partition by actv.media_id order by actv.interaction_id desc) party_id
FROM
jtf_ih_activities actv,
jtf_ih_interactions intr
WHERE actv.start_date_time BETWEEN g_collect_start_date AND g_collect_end_date
AND actv.interaction_id = intr.interaction_id
) inv1,
JTF_IH_MEDIA_ITEM_LC_SEGS mseg,
JTF_IH_MEDIA_ITM_LC_SEG_TYS mtys,
--
--Changes for R12
--
(
select name, max(route_classification_id) route_classification_id
from iem_route_classifications
group by name
) cls
WHERE mitm.media_id = inv2.media_id
AND inv2.media_id = mseg.media_id
-- AND inv2.resource_id = mseg.resource_id
/* Commenting this join out because the supervisor performs some operations like delete and requeued.
Irrespective of which user did it, the email was not open. Lets say Email fetched by agent a, email transferred by a,
email transfer to b, email requeue to c. If we remove this condition, we anyways take min (inv2.start_date_time),
so a will get the email transferred start date time (done by him), b will get requeue start date time (done by c) */
AND mitm.media_item_type = 'EMAIL'
AND mitm.direction = 'INBOUND'
AND mitm.classification = cls.name(+)
AND mseg.start_date_time < g_collect_end_date
AND mseg.milcs_type_id = mtys.milcs_type_id
AND inv2.START_DATE_TIME >= mseg.START_DATE_TIME
AND mtys.milcs_code IN ('EMAIL_FETCH','EMAIL_TRANSFER', 'EMAIL_ASSIGN_OPEN', 'EMAIL_AUTO_ROUTED')
AND mitm.media_id = inv1.media_id --???test with outer join removed here
GROUP BY
mitm.media_id,
nvl(mitm.source_id, -1),
nvl(cls.route_classification_id, -1),
nvl(mseg.resource_id, -1),
nvl(inv1.party_id, -1),
mseg.start_date_time,
mitm.start_date_time;
/* Estimating statistics as these rows will be updated by resolution measures */
DBMS_STATS.gather_table_stats(ownname => g_bix_schema,
tabName => 'BIX_EMAIL_DETAILS_F',
cascade => TRUE,
degree => bis_common_parameters.get_degree_of_parallelism,
estimate_percent => 10,
granularity => 'GLOBAL');
INSERT /*+ append */ INTO bix_email_details_f (
agent_id,
email_account_id,
email_classification_id,
party_id,
time_id,
period_type_id,
period_start_date,
period_start_time,
outcome_id,
result_id,
reason_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
emails_offered_in_period,
emails_fetched_in_period,
emails_replied_in_period,
emails_rpld_by_goal_in_period,
AGENT_EMAILS_RPLD_BY_GOAL,
emails_deleted_in_period,
emails_trnsfrd_out_in_period,
emails_trnsfrd_in_in_period,
emails_rsl_and_trfd_in_period,
emails_assigned_in_period,
emails_auto_routed_in_period,
emails_auto_uptd_sr_in_period,
sr_created_in_period,
oldest_email_open_date,
oldest_email_queue_date,
email_resp_time_in_period,
agent_resp_time_in_period,
accumulated_open_emails,
accumulated_open_age,
accumulated_emails_in_queue,
accumulated_queue_time,
accumulated_emails_one_day,
accumulated_emails_three_days,
accumulated_emails_week,
accumulated_emails_week_plus,
one_rsln_in_period,
two_rsln_in_period,
three_rsln_in_period,
four_rsln_in_period,
interaction_threads_in_period,
emails_orr_count_in_period,
EMAILS_AUTO_REPLIED_IN_PERIOD,
EMAILS_AUTO_DELETED_IN_PERIOD,
EMAILS_AUTO_RESOLVED_IN_PERIOD,
emails_composed_in_period,
emails_rerouted_in_period,
leads_created_in_period,
request_id,
program_application_id,
program_id,
program_update_date )
(SELECT
rlp.agent_id agent_id,
rlp.email_account_id email_account_id,
rlp.email_classification_id email_classification_id,
rlp.party_id party_id,
rlp.time_id time_id,
rlp.period_type_id period_type_id,
rlp.period_start_date period_start_date,
rlp.period_start_time period_start_time,
rlp.outcome_id outcome_id,
rlp.result_id result_id,
rlp.reason_id reason_id,
g_user_id created_by,
g_sysdate creation_date,
g_user_id last_updated_by,
g_sysdate last_update_date,
decode(sum(rlp.emails_offered_in_period), 0, to_number(null), sum(rlp.emails_offered_in_period))
emails_offered_in_period,
decode(sum(rlp.emails_fetched_in_period), 0, to_number(null), sum(rlp.emails_fetched_in_period))
emails_fetched_in_period,
decode(sum(rlp.emails_replied_in_period), 0, to_number(null), sum(rlp.emails_replied_in_period))
emails_replied_in_period,
decode(sum(rlp.emails_rpld_by_goal_in_period), 0, to_number(null), sum(rlp.emails_rpld_by_goal_in_period))
emails_rpld_by_goal_in_period,
decode(sum(rlp.AGENT_EMAILS_RPLD_BY_GOAL), 0, to_number(null), sum(rlp.AGENT_EMAILS_RPLD_BY_GOAL))
AGENT_EMAILS_RPLD_BY_GOAL,
decode(sum(rlp.emails_deleted_in_period), 0, to_number(null), sum(rlp.emails_deleted_in_period))
emails_deleted_in_period,
decode(sum(rlp.emails_trnsfrd_out_in_period), 0, to_number(null), sum(rlp.emails_trnsfrd_out_in_period))
emails_trnsfrd_out_in_period,
decode(sum(rlp.emails_trnsfrd_in_in_period), 0, to_number(null), sum(rlp.emails_trnsfrd_in_in_period))
emails_trnsfrd_in_in_period,
decode(sum(rlp.emails_rsl_and_trfd_in_period), 0, to_number(null), sum(rlp.emails_rsl_and_trfd_in_period))
emails_rsl_and_trfd_in_period,
decode(sum(rlp.emails_assigned_in_period), 0, to_number(null), sum(rlp.emails_assigned_in_period))
emails_assigned_in_period,
decode(sum(rlp.emails_auto_routed_in_period), 0, to_number(null), sum(rlp.emails_auto_routed_in_period))
emails_auto_routed_in_period,
decode(sum(rlp.emails_auto_uptd_sr_in_period), 0, to_number(null), sum(rlp.emails_auto_uptd_sr_in_period))
emails_auto_uptd_sr_in_period,
decode(sum(rlp.sr_created_in_period), 0, to_number(null), sum(rlp.sr_created_in_period))
sr_created_in_period,
min(rlp.oldest_email_open_date) oldest_email_open_date,
min(rlp.oldest_email_queue_date) oldest_email_queue_date,
decode(sum(rlp.email_resp_time_in_period), 0, to_number(null), sum(rlp.email_resp_time_in_period))
email_resp_time_in_period,
decode(sum(rlp.agent_resp_time_in_period), 0, to_number(null), sum(rlp.agent_resp_time_in_period))
agent_resp_time_in_period,
min(rlp.acc_open_emails) acc_open_emails,
min(rlp.acc_open_age) acc_open_age,
min(rlp.acc_emails_in_queue) acc_emails_in_queue,
min(rlp.acc_queue_time) acc_queue_time,
min(rlp.acc_emails_one_day) acc_emails_one_day,
min(rlp.acc_emails_three_days) acc_emails_three_days,
min(rlp.acc_emails_week) acc_emails_week,
min(rlp.acc_emails_week_plus) acc_emails_week_plus,
decode(sum(rlp.one_rsln_in_period), 0, to_number(null), sum(rlp.one_rsln_in_period))
one_rsln_in_period,
decode(sum(rlp.two_rsln_in_period), 0, to_number(null), sum(rlp.two_rsln_in_period))
two_rsln_in_period,
decode(sum(rlp.three_rsln_in_period), 0, to_number(null), sum(rlp.three_rsln_in_period))
three_rsln_in_period,
decode(sum(rlp.four_rsln_in_period), 0, to_number(null), sum(rlp.four_rsln_in_period))
four_rsln_in_period,
decode(sum(rlp.interaction_threads_in_period), 0, to_number(null), sum(rlp.interaction_threads_in_period))
interaction_threads_in_period,
decode(sum(rlp.emails_orr_count_in_period),0,to_number(null),sum(emails_orr_count_in_period)) emails_orr_count_in_period,
decode(sum(rlp.EMAILS_AUTO_REPLIED_IN_PERIOD),0,to_number(null),sum(EMAILS_AUTO_REPLIED_IN_PERIOD)) EMAILS_AUTO_REPLIED_IN_PERIOD,
decode(sum(rlp.EMAILS_AUTO_DELETED_IN_PERIOD),0,to_number(null),sum(EMAILS_AUTO_DELETED_IN_PERIOD)) EMAILS_AUTO_DELETED_IN_PERIOD,
decode(sum(rlp.EMAILS_AUTO_RESOLVED_IN_PERIOD),0,to_number(null),sum(EMAILS_AUTO_RESOLVED_IN_PERIOD)) EMAILS_AUTO_RESOLVED_IN_PERIOD,
decode(sum(rlp.emails_composed_in_period),0,to_number(null),sum(emails_composed_in_period)) emails_composed_in_period,
decode(sum(rlp.EMAILS_REROUTED_IN_PERIOD),0,to_number(null),sum(EMAILS_REROUTED_IN_PERIOD)) EMAILS_REROUTED_IN_PERIOD,
decode(sum(rlp.leads_created_in_period),0,to_number(null),sum(leads_created_in_period)) leads_created_in_period,
g_request_id request_id,
g_program_appl_id program_application_id,
g_program_id program_id,
g_sysdate program_update_date
FROM (
SELECT
inv2.agent_id agent_id,
inv2.email_account_id email_account_id,
inv2.email_classification_id email_classification_id,
inv2.party_id party_id,
decode(inv2.week_id, null, decode(inv2.ent_period_id, null,
decode(inv2.ent_qtr_id, null, decode(inv2.ent_year_id, null, to_number(null), inv2.ent_year_id),
inv2.ent_qtr_id), inv2.ent_period_id), inv2.week_id) time_id,
decode(inv2.week_id, null, decode(inv2.ent_period_id, null,
decode(inv2.ent_qtr_id, null, decode(inv2.ent_year_id, null, to_number(null),
128), 64), 32), 16) period_type_id,
decode(inv2.week_id, null, decode(inv2.ent_period_id, null,
decode(inv2.ent_qtr_id, null, decode(inv2.ent_year_id, null, to_date(null), min(inv2.ent_year_start_date)),
min(inv2.ent_qtr_start_date)), min(inv2.ent_period_start_date)), min(inv2.week_start_date))
period_start_date,
'00:00' period_start_time,
inv2.outcome_id,
inv2.result_id,
inv2.reason_id,
sum(inv2.emails_offered_in_period) emails_offered_in_period,
sum(inv2.emails_fetched_in_period) emails_fetched_in_period,
sum(inv2.emails_replied_in_period) emails_replied_in_period,
sum(inv2.emails_rpld_by_goal_in_period) emails_rpld_by_goal_in_period,
sum(inv2.AGENT_EMAILS_RPLD_BY_GOAL) AGENT_EMAILS_RPLD_BY_GOAL,
sum(inv2.emails_deleted_in_period) emails_deleted_in_period,
sum(inv2.emails_trnsfrd_out_in_period) emails_trnsfrd_out_in_period,
sum(inv2.emails_trnsfrd_in_in_period) emails_trnsfrd_in_in_period,
sum(inv2.emails_rsl_and_trfd_in_period) emails_rsl_and_trfd_in_period,
sum(inv2.emails_assigned_in_period) emails_assigned_in_period,
sum(inv2.emails_auto_routed_in_period) emails_auto_routed_in_period,
sum(inv2.emails_auto_uptd_sr_in_period) emails_auto_uptd_sr_in_period,
sum(inv2.sr_created_in_period) sr_created_in_period,
to_date(null) oldest_email_open_date,
to_date(null) oldest_email_queue_date,
sum(inv2.email_resp_time_in_period) email_resp_time_in_period,
sum(inv2.agent_resp_time_in_period) agent_resp_time_in_period,
sum(inv2.one_rsln_in_period) one_rsln_in_period,
sum(inv2.two_rsln_in_period) two_rsln_in_period,
sum(inv2.three_rsln_in_period) three_rsln_in_period,
sum(inv2.four_rsln_in_period) four_rsln_in_period,
sum(inv2.interaction_threads_in_period) interaction_threads_in_period,
sum(inv2.emails_orr_count_in_period) emails_orr_count_in_period,
sum(inv2.EMAILS_AUTO_REPLIED_IN_PERIOD) EMAILS_AUTO_REPLIED_IN_PERIOD,
sum(inv2.EMAILS_AUTO_DELETED_IN_PERIOD) EMAILS_AUTO_DELETED_IN_PERIOD,
sum(inv2.EMAILS_AUTO_RESOLVED_IN_PERIOD) EMAILS_AUTO_RESOLVED_IN_PERIOD,
sum(inv2.emails_composed_in_period) emails_composed_in_period,
sum(inv2.emails_rerouted_in_period) emails_rerouted_in_period,
sum(inv2.leads_created_in_period) leads_created_in_period,
decode(inv2.week_id, null, decode(inv2.ent_period_id, null,
decode(inv2.ent_qtr_id, null, decode(inv2.ent_year_id, null, to_number(null), min(inv2.year_acc_open_emails)),
min(inv2.qtr_acc_open_emails)), min(inv2.period_acc_open_emails)), min(inv2.week_acc_open_emails))
acc_open_emails,
to_number(null) acc_open_age,
decode(inv2.week_id, null, decode(inv2.ent_period_id, null,
decode(inv2.ent_qtr_id, null, decode(inv2.ent_year_id, null, to_number(null), min(inv2.year_acc_emails_in_queue)),
min(inv2.qtr_acc_emails_in_queue)), min(inv2.period_acc_emails_in_queue)), min(inv2.week_acc_emails_in_queue))
acc_emails_in_queue,
to_number(null) acc_queue_time,
decode(inv2.week_id, null, decode(inv2.ent_period_id, null,
decode(inv2.ent_qtr_id, null, decode(inv2.ent_year_id, null, to_number(null), min(inv2.year_acc_emails_one_day)),
min(inv2.qtr_acc_emails_one_day)), min(inv2.period_acc_emails_one_day)), min(inv2.week_acc_emails_one_day))
acc_emails_one_day,
decode(inv2.week_id, null, decode(inv2.ent_period_id, null,
decode(inv2.ent_qtr_id, null, decode(inv2.ent_year_id, null, to_number(null), min(inv2.year_acc_emails_three_days)),
min(inv2.qtr_acc_emails_three_days)), min(inv2.period_acc_emails_three_days)),
min(inv2.week_acc_emails_three_days)) acc_emails_three_days,
decode(inv2.week_id, null, decode(inv2.ent_period_id, null,
decode(inv2.ent_qtr_id, null, decode(inv2.ent_year_id, null, to_number(null), min(inv2.year_acc_emails_week)),
min(inv2.qtr_acc_emails_week)), min(inv2.period_acc_emails_week)), min(inv2.week_acc_emails_week))
acc_emails_week,
decode(inv2.week_id, null, decode(inv2.ent_period_id, null,
decode(inv2.ent_qtr_id, null, decode(inv2.ent_year_id, null, to_number(null), min(inv2.year_acc_emails_week_plus)),
min(inv2.qtr_acc_emails_week_plus)), min(inv2.period_acc_emails_week_plus)),
min(inv2.week_acc_emails_week_plus)) acc_emails_week_plus
FROM
(SELECT /*+ use_hash(bed) use_hash(ftd) */
bed.agent_id agent_id,
bed.email_account_id email_account_id,
bed.email_classification_id email_classification_id,
bed.party_id party_id,
bed.outcome_id,
bed.result_id,
bed.reason_id,
ftd.ent_year_id ent_year_id,
ftd.ent_year_start_date ent_year_start_date,
ftd.ent_qtr_id ent_qtr_id,
ftd.ent_qtr_start_date ent_qtr_start_date,
ftd.ent_period_id ent_period_id,
ftd.ent_period_start_date ent_period_start_date,
ftd.week_id week_id,
ftd.week_start_date week_start_date,
ftd.report_date_julian report_date_julian,
bed.period_start_date period_start_date,
bed.emails_offered_in_period emails_offered_in_period,
bed.emails_fetched_in_period emails_fetched_in_period,
bed.emails_replied_in_period emails_replied_in_period,
bed.emails_rpld_by_goal_in_period emails_rpld_by_goal_in_period,
bed.AGENT_EMAILS_RPLD_BY_GOAL AGENT_EMAILS_RPLD_BY_GOAL,
bed.emails_deleted_in_period emails_deleted_in_period,
bed.emails_trnsfrd_out_in_period emails_trnsfrd_out_in_period,
bed.emails_trnsfrd_in_in_period emails_trnsfrd_in_in_period,
bed.emails_rsl_and_trfd_in_period emails_rsl_and_trfd_in_period,
bed.emails_assigned_in_period emails_assigned_in_period,
bed.emails_auto_routed_in_period emails_auto_routed_in_period,
bed.emails_auto_uptd_sr_in_period emails_auto_uptd_sr_in_period,
bed.sr_created_in_period sr_created_in_period,
bed.email_resp_time_in_period email_resp_time_in_period,
bed.agent_resp_time_in_period agent_resp_time_in_period,
bed.one_rsln_in_period one_rsln_in_period,
bed.two_rsln_in_period two_rsln_in_period,
bed.three_rsln_in_period three_rsln_in_period,
bed.four_rsln_in_period four_rsln_in_period,
bed.interaction_threads_in_period interaction_threads_in_period,
bed.emails_orr_count_in_period emails_orr_count_in_period,
bed.EMAILS_AUTO_REPLIED_IN_PERIOD EMAILS_AUTO_REPLIED_IN_PERIOD,
bed.EMAILS_AUTO_DELETED_IN_PERIOD EMAILS_AUTO_DELETED_IN_PERIOD,
bed.EMAILS_AUTO_RESOLVED_IN_PERIOD EMAILS_AUTO_RESOLVED_IN_PERIOD,
bed.emails_composed_in_period emails_composed_in_period,
bed.emails_rerouted_in_period emails_rerouted_in_period,
bed.leads_created_in_period leads_created_in_period,
--1/2hr removing day measures since they have already been done
-- first_value(bed.accumulated_open_emails)
-- over (partition by bed.agent_id, bed.email_account_id, bed.email_classification_id, bed.party_id,
-- ftd.report_date_julian
-- order by to_date(to_char(bed.period_start_date, 'dd/mm/yyyy ') ||
-- lpad(bed.period_start_time, 5, '0'), 'dd/mm/yyyy hh24:mi') desc) day_acc_open_emails,
first_value(bed.accumulated_open_emails)
over (partition by bed.agent_id, bed.email_account_id, bed.email_classification_id, bed.party_id,
outcome_id, result_id, reason_id,
ftd.week_id
order by to_date(to_char(bed.period_start_date, 'dd/mm/yyyy ') ||
lpad(bed.period_start_time, 5, '0'), 'dd/mm/yyyy hh24:mi') desc) week_acc_open_emails,
first_value(bed.accumulated_open_emails)
over (partition by bed.agent_id, bed.email_account_id, bed.email_classification_id, bed.party_id,
outcome_id, result_id, reason_id,
ftd.ent_period_id
order by to_date(to_char(bed.period_start_date, 'dd/mm/yyyy ') ||
lpad(bed.period_start_time, 5, '0'), 'dd/mm/yyyy hh24:mi') desc) period_acc_open_emails,
first_value(bed.accumulated_open_emails)
over (partition by bed.agent_id, bed.email_account_id, bed.email_classification_id, bed.party_id,
outcome_id, result_id, reason_id,
ftd.ent_qtr_id
order by to_date(to_char(bed.period_start_date, 'dd/mm/yyyy ') ||
lpad(bed.period_start_time, 5, '0'), 'dd/mm/yyyy hh24:mi') desc) qtr_acc_open_emails,
first_value(bed.accumulated_open_emails)
over (partition by bed.agent_id, bed.email_account_id, bed.email_classification_id, bed.party_id,
outcome_id, result_id, reason_id,
ftd.ent_year_id
order by to_date(to_char(bed.period_start_date, 'dd/mm/yyyy ') ||
lpad(bed.period_start_time, 5, '0'), 'dd/mm/yyyy hh24:mi') desc) year_acc_open_emails,
--1/2hr removing day measures since they have already been done
--first_value(bed.accumulated_emails_in_queue)
-- over (partition by bed.agent_id, bed.email_account_id, bed.email_classification_id, bed.party_id,
-- ftd.report_date_julian
-- order by to_date(to_char(bed.period_start_date, 'dd/mm/yyyy ') ||
-- lpad(bed.period_start_time, 5, '0'), 'dd/mm/yyyy hh24:mi') desc) day_acc_emails_in_queue,
first_value(bed.accumulated_emails_in_queue)
over (partition by bed.agent_id, bed.email_account_id, bed.email_classification_id, bed.party_id,
outcome_id, result_id, reason_id,
ftd.week_id
order by to_date(to_char(bed.period_start_date, 'dd/mm/yyyy ') ||
lpad(bed.period_start_time, 5, '0'), 'dd/mm/yyyy hh24:mi') desc) week_acc_emails_in_queue,
first_value(bed.accumulated_emails_in_queue)
over (partition by bed.agent_id, bed.email_account_id, bed.email_classification_id, bed.party_id,
outcome_id, result_id, reason_id,
ftd.ent_period_id
order by to_date(to_char(bed.period_start_date, 'dd/mm/yyyy ') ||
lpad(bed.period_start_time, 5, '0'), 'dd/mm/yyyy hh24:mi') desc) period_acc_emails_in_queue,
first_value(bed.accumulated_emails_in_queue)
over (partition by bed.agent_id, bed.email_account_id, bed.email_classification_id, bed.party_id,
outcome_id, result_id, reason_id,
ftd.ent_qtr_id
order by to_date(to_char(bed.period_start_date, 'dd/mm/yyyy ') ||
lpad(bed.period_start_time, 5, '0'), 'dd/mm/yyyy hh24:mi') desc) qtr_acc_emails_in_queue,
first_value(bed.accumulated_emails_in_queue)
over (partition by bed.agent_id, bed.email_account_id, bed.email_classification_id, bed.party_id,
outcome_id, result_id, reason_id,
ftd.ent_year_id
order by to_date(to_char(bed.period_start_date, 'dd/mm/yyyy ') ||
lpad(bed.period_start_time, 5, '0'), 'dd/mm/yyyy hh24:mi') desc) year_acc_emails_in_queue,
--1/2hr removing day measures since they have already been done
-- first_value(bed.accumulated_emails_one_day)
-- over (partition by bed.agent_id, bed.email_account_id, bed.email_classification_id, bed.party_id,
-- ftd.report_date_julian
-- order by to_date(to_char(bed.period_start_date, 'dd/mm/yyyy ') ||
-- lpad(bed.period_start_time, 5, '0'), 'dd/mm/yyyy hh24:mi') desc) day_acc_emails_one_day,
first_value(bed.accumulated_emails_one_day)
over (partition by bed.agent_id, bed.email_account_id, bed.email_classification_id, bed.party_id,
outcome_id, result_id, reason_id,
ftd.week_id
order by to_date(to_char(bed.period_start_date, 'dd/mm/yyyy ') ||
lpad(bed.period_start_time, 5, '0'), 'dd/mm/yyyy hh24:mi') desc) week_acc_emails_one_day,
first_value(bed.accumulated_emails_one_day)
over (partition by bed.agent_id, bed.email_account_id, bed.email_classification_id, bed.party_id,
outcome_id, result_id, reason_id,
ftd.ent_period_id
order by to_date(to_char(bed.period_start_date, 'dd/mm/yyyy ') ||
lpad(bed.period_start_time, 5, '0'), 'dd/mm/yyyy hh24:mi') desc) period_acc_emails_one_day,
first_value(bed.accumulated_emails_one_day)
over (partition by bed.agent_id, bed.email_account_id, bed.email_classification_id, bed.party_id,
outcome_id, result_id, reason_id,
ftd.ent_qtr_id
order by to_date(to_char(bed.period_start_date, 'dd/mm/yyyy ') ||
lpad(bed.period_start_time, 5, '0'), 'dd/mm/yyyy hh24:mi') desc) qtr_acc_emails_one_day,
first_value(bed.accumulated_emails_one_day)
over (partition by bed.agent_id, bed.email_account_id, bed.email_classification_id, bed.party_id,
outcome_id, result_id, reason_id,
ftd.ent_year_id
order by to_date(to_char(bed.period_start_date, 'dd/mm/yyyy ') ||
lpad(bed.period_start_time, 5, '0'), 'dd/mm/yyyy hh24:mi') desc) year_acc_emails_one_day,
--1/2hr removing day measures since they have already been done
--first_value(bed.accumulated_emails_three_days)
-- over (partition by bed.agent_id, bed.email_account_id, bed.email_classification_id, bed.party_id,
-- ftd.report_date_julian
-- order by to_date(to_char(bed.period_start_date, 'dd/mm/yyyy ') ||
-- lpad(bed.period_start_time, 5, '0'), 'dd/mm/yyyy hh24:mi') desc) day_acc_emails_three_days,
first_value(bed.accumulated_emails_three_days)
over (partition by bed.agent_id, bed.email_account_id, bed.email_classification_id, bed.party_id,
outcome_id, result_id, reason_id,
ftd.week_id
order by to_date(to_char(bed.period_start_date, 'dd/mm/yyyy ') ||
lpad(bed.period_start_time, 5, '0'), 'dd/mm/yyyy hh24:mi') desc) week_acc_emails_three_days,
first_value(bed.accumulated_emails_three_days)
over (partition by bed.agent_id, bed.email_account_id, bed.email_classification_id, bed.party_id,
outcome_id, result_id, reason_id,
ftd.ent_period_id
order by to_date(to_char(bed.period_start_date, 'dd/mm/yyyy ') ||
lpad(bed.period_start_time, 5, '0'), 'dd/mm/yyyy hh24:mi') desc) period_acc_emails_three_days,
first_value(bed.accumulated_emails_three_days)
over (partition by bed.agent_id, bed.email_account_id, bed.email_classification_id, bed.party_id,
outcome_id, result_id, reason_id,
ftd.ent_qtr_id
order by to_date(to_char(bed.period_start_date, 'dd/mm/yyyy ') ||
lpad(bed.period_start_time, 5, '0'), 'dd/mm/yyyy hh24:mi') desc) qtr_acc_emails_three_days,
first_value(bed.accumulated_emails_three_days)
over (partition by bed.agent_id, bed.email_account_id, bed.email_classification_id, bed.party_id,
outcome_id, result_id, reason_id,
ftd.ent_year_id
order by to_date(to_char(bed.period_start_date, 'dd/mm/yyyy ') ||
lpad(bed.period_start_time, 5, '0'), 'dd/mm/yyyy hh24:mi') desc) year_acc_emails_three_days,
--1/2hr removing day measures since they have already been done
--first_value(bed.accumulated_emails_week)
-- over (partition by bed.agent_id, bed.email_account_id, bed.email_classification_id, bed.party_id,
-- ftd.report_date_julian
-- order by to_date(to_char(bed.period_start_date, 'dd/mm/yyyy ') ||
-- lpad(bed.period_start_time, 5, '0'), 'dd/mm/yyyy hh24:mi') desc) day_acc_emails_week,
first_value(bed.accumulated_emails_week)
over (partition by bed.agent_id, bed.email_account_id, bed.email_classification_id, bed.party_id,
outcome_id, result_id, reason_id,
ftd.week_id
order by to_date(to_char(bed.period_start_date, 'dd/mm/yyyy ') ||
lpad(bed.period_start_time, 5, '0'), 'dd/mm/yyyy hh24:mi') desc) week_acc_emails_week,
first_value(bed.accumulated_emails_week)
over (partition by bed.agent_id, bed.email_account_id, bed.email_classification_id, bed.party_id,
outcome_id, result_id, reason_id,
ftd.ent_period_id
order by to_date(to_char(bed.period_start_date, 'dd/mm/yyyy ') ||
lpad(bed.period_start_time, 5, '0'), 'dd/mm/yyyy hh24:mi') desc) period_acc_emails_week,
first_value(bed.accumulated_emails_week)
over (partition by bed.agent_id, bed.email_account_id, bed.email_classification_id, bed.party_id,
outcome_id, result_id, reason_id,
ftd.ent_qtr_id
order by to_date(to_char(bed.period_start_date, 'dd/mm/yyyy ') ||
lpad(bed.period_start_time, 5, '0'), 'dd/mm/yyyy hh24:mi') desc) qtr_acc_emails_week,
first_value(bed.accumulated_emails_week)
over (partition by bed.agent_id, bed.email_account_id, bed.email_classification_id, bed.party_id,
outcome_id, result_id, reason_id,
ftd.ent_year_id
order by to_date(to_char(bed.period_start_date, 'dd/mm/yyyy ') ||
lpad(bed.period_start_time, 5, '0'), 'dd/mm/yyyy hh24:mi') desc) year_acc_emails_week,
--1/2hr removing day measures since they have already been done
--first_value(bed.accumulated_emails_week_plus)
-- over (partition by bed.agent_id, bed.email_account_id, bed.email_classification_id, bed.party_id,
-- ftd.report_date_julian
-- order by to_date(to_char(bed.period_start_date, 'dd/mm/yyyy ') ||
-- lpad(bed.period_start_time, 5, '0'), 'dd/mm/yyyy hh24:mi') desc) day_acc_emails_week_plus,
first_value(bed.accumulated_emails_week_plus)
over (partition by bed.agent_id, bed.email_account_id, bed.email_classification_id, bed.party_id,
outcome_id, result_id, reason_id,
ftd.week_id
order by to_date(to_char(bed.period_start_date, 'dd/mm/yyyy ') ||
lpad(bed.period_start_time, 5, '0'), 'dd/mm/yyyy hh24:mi') desc) week_acc_emails_week_plus,
first_value(bed.accumulated_emails_week_plus)
over (partition by bed.agent_id, bed.email_account_id, bed.email_classification_id, bed.party_id,
outcome_id, result_id, reason_id,
ftd.ent_period_id
order by to_date(to_char(bed.period_start_date, 'dd/mm/yyyy ') ||
lpad(bed.period_start_time, 5, '0'), 'dd/mm/yyyy hh24:mi') desc) period_acc_emails_week_plus,
first_value(bed.accumulated_emails_week_plus)
over (partition by bed.agent_id, bed.email_account_id, bed.email_classification_id, bed.party_id,
outcome_id, result_id, reason_id,
ftd.ent_qtr_id
order by to_date(to_char(bed.period_start_date, 'dd/mm/yyyy ') ||
lpad(bed.period_start_time, 5, '0'), 'dd/mm/yyyy hh24:mi') desc) qtr_acc_emails_week_plus,
first_value(bed.accumulated_emails_week_plus)
over (partition by bed.agent_id, bed.email_account_id, bed.email_classification_id, bed.party_id,
outcome_id, result_id, reason_id,
ftd.ent_year_id
order by to_date(to_char(bed.period_start_date, 'dd/mm/yyyy ') ||
lpad(bed.period_start_time, 5, '0'), 'dd/mm/yyyy hh24:mi') desc) year_acc_emails_week_plus
FROM bix_email_details_f bed,
fii_time_day ftd
WHERE bed.time_id = ftd.report_date_julian
--1/2hr changed period_Type_id to 1 from -1
--AND bed.period_type_id = -1) inv2
AND bed.period_type_id = 1) inv2
GROUP BY
inv2.agent_id,
inv2.email_account_id,
inv2.email_classification_id,
inv2.party_id,
inv2.outcome_id,
inv2.result_id,
inv2.reason_id,
ROLLUP(
inv2.ent_year_id,
inv2.ent_qtr_id,
inv2.ent_period_id,
inv2.week_id)
HAVING
decode(inv2.week_id, null, decode(inv2.ent_period_id, null,
decode(inv2.ent_qtr_id, null, decode(inv2.ent_year_id, null, to_number(null),
128), 64), 32), 16) IS NOT NULL) rlp
GROUP BY
rlp.agent_id,
rlp.email_account_id,
rlp.email_classification_id,
rlp.party_id,
rlp.time_id,
rlp.period_type_id,
rlp.period_start_date,
rlp.period_start_time,
rlp.outcome_id,
rlp.result_id,
rlp.reason_id);
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;
write_log('Total Rows Inserted/Updated : ' || to_char(g_rows_ins_upd));
/* Insert the status into collect log table */
write_log('Calling procedure WRAPUP');
SELECT
TO_DATE(TO_CHAR(l_start_date,'YYYY/MM/DD') || ' ' ||
LPAD(TO_CHAR(l_start_date,'HH24:'),3,'0') ||
DECODE(SIGN(TO_NUMBER(TO_CHAR(l_start_date,'MI'))-29),0,'00:00',1,'30:00',-1,'00:00'),
'YYYY/MM/DD HH24:MI:SS')
INTO
l_start_date
FROM DUAL;
SELECT
TO_DATE(TO_CHAR(l_end_date,'YYYY/MM/DD') || ' ' ||
LPAD(TO_CHAR(l_end_date,'HH24:'),3,'0') ||
DECODE(SIGN(TO_NUMBER(TO_CHAR(l_end_date,'MI'))-29),0,'00:00',1,'30:00',-1,'00:00'),
'YYYY/MM/DD HH24:MI:SS') - 1/86400
INTO
l_end_date
FROM DUAL;