[Home] [Help]
26: TYPE g_media_id_tab IS TABLE OF jtf_ih_media_items.media_id%TYPE;
27: TYPE g_email_account_id_tab IS TABLE OF jtf_ih_media_items.source_id%TYPE;
28: TYPE g_email_classification_id_tab IS TABLE OF iem_route_classifications.route_classification_id%TYPE;
29: TYPE g_resource_id_tab IS TABLE OF bix_email_details_f.agent_id%TYPE;
30: TYPE g_start_date_time_tab IS TABLE OF jtf_ih_media_item_lc_segs.start_date_time%TYPE;
31: TYPE g_end_date_time_tab IS TABLE OF jtf_ih_media_item_lc_segs.end_date_time%TYPE;
32: TYPE g_media_start_date_time_tab IS TABLE OF jtf_ih_media_items.start_date_time%TYPE;
33:
34: G_PROCESSING NUMBER;
27: TYPE g_email_account_id_tab IS TABLE OF jtf_ih_media_items.source_id%TYPE;
28: TYPE g_email_classification_id_tab IS TABLE OF iem_route_classifications.route_classification_id%TYPE;
29: TYPE g_resource_id_tab IS TABLE OF bix_email_details_f.agent_id%TYPE;
30: TYPE g_start_date_time_tab IS TABLE OF jtf_ih_media_item_lc_segs.start_date_time%TYPE;
31: TYPE g_end_date_time_tab IS TABLE OF jtf_ih_media_item_lc_segs.end_date_time%TYPE;
32: TYPE g_media_start_date_time_tab IS TABLE OF jtf_ih_media_items.start_date_time%TYPE;
33:
34: G_PROCESSING NUMBER;
35: G_REPLY NUMBER;
298: 1
299: INTO p_curr_depth
300: FROM jtf_ih_activities actv,
301: jtf_ih_media_items imtm,
302: jtf_ih_media_item_lc_segs mseg,
303: jtf_ih_media_itm_lc_seg_tys mtys
304: WHERE actv.interaction_id = p_interaction_id
305: AND imtm.media_id = actv.media_id
306: AND mseg.media_id = imtm.media_id
335: START WITH interact_interaction_idrelates = p_interaction_id
336: CONNECT BY interact_interaction_idrelates = PRIOR interact_interaction_id
337: ORDER BY creation_date DESC) inv2, jtf_ih_activities actv,
338: jtf_ih_media_items mitm,
339: jtf_ih_media_item_lc_segs mseg,
340: jtf_ih_media_itm_lc_seg_tys mtys
341: WHERE actv.interaction_id = inv2.interaction_id
342: AND mitm.media_id = actv.media_id
343: AND mitm.media_id = mseg.media_id
352: 1
353: INTO p_parent_depth
354: FROM jtf_ih_activities actv,
355: jtf_ih_media_items imtm,
356: jtf_ih_media_item_lc_segs mseg,
357: jtf_ih_media_itm_lc_seg_tys mtys
358: WHERE actv.interaction_id = p_interaction_id
359: AND imtm.media_id = actv.media_id
360: AND mseg.media_id = imtm.media_id
428: inter.interact_interaction_id = incr.interaction_id
429: )
430: AND NOT EXISTS (
431: SELECT 1
432: FROM jtf_ih_media_item_lc_segs mseg1,
433: jtf_ih_media_itm_lc_seg_tys mtys1
434: WHERE mseg1.media_id = imtm.media_id
435: AND mseg1.milcs_type_id = mtys1.milcs_type_id
436: AND mtys1.milcs_code IN ( 'EMAIL_REPLY','EMAIL_AUTO_REPLY') )
439: incr.interaction_id
440: FROM jtf_ih_interactions incr,
441: jtf_ih_activities actv,
442: jtf_ih_media_items imtm,
443: jtf_ih_media_item_lc_segs mseg,
444: jtf_ih_media_itm_lc_seg_tys mtys
445: WHERE actv.interaction_id = incr.interaction_id
446: AND imtm.media_id = actv.media_id
447: AND imtm.media_item_type = 'EMAIL'
480: intr.interact_interaction_id
481: FROM jtf_ih_interaction_inters intr,
482: jtf_ih_activities actv,
483: jtf_ih_media_items imtm,
484: jtf_ih_media_item_lc_segs mseg,
485: jtf_ih_media_itm_lc_seg_tys mtys
486: WHERE actv.interaction_id = intr.interact_interaction_id
487: AND imtm.media_id = actv.media_id
488: AND mseg.media_id = imtm.media_id
1201: sum(EMAILS_REROUTED_IN_PERIOD) EMAILS_REROUTED_IN_PERIOD,
1202: 0 LEADS_CREATED_IN_PERIOD
1203: FROM
1204: (
1205: SELECT /*+ index (mseg jtf_ih_media_item_lc_segs_n3) use_nl(mseg mitm) */
1206: nvl(mitm.source_id, -1) EMAIL_ACCOUNT_ID,
1207: nvl(irc.route_classification_id, -1) EMAIL_CLASSIFICATION_ID,
1208: nvl(mseg.resource_id, -1) AGENT_ID,
1209: nvl(( SELECT
1222: decode(mtyp.milcs_code,'EMAIL_REPLY', (mseg.start_date_time -
1223: (
1224: SELECT MAX(mseg2.start_date_time) start_date_time
1225: FROM
1226: jtf_ih_media_item_lc_segs mseg2,
1227: jtf_ih_media_itm_lc_seg_tys mtyp2
1228: WHERE mseg.media_id = mseg2.media_id
1229: AND mseg2.milcs_type_id = mtyp2.milcs_type_id
1230: AND mtyp2.milcs_code IN ('EMAIL_FETCH','EMAIL_TRANSFER', 'EMAIL_ASSIGN_OPEN', 'EMAIL_AUTO_ROUTED', 'EMAIL_ASSIGNED')
1251: decode(mtyp.milcs_code,'EMAIL_RESOLVED',1) EMAILS_AUTO_RESOLVED_IN_PERIOD,
1252: decode(mtyp.milcs_code,'EMAIL_REROUTED_DIFF_CLASS',1,'EMAIL_REROUTED_DIFF_ACCT',1,'EMAIL_REQUEUED',1) EMAILS_REROUTED_IN_PERIOD
1253: FROM
1254: JTF_IH_MEDIA_ITEMS mitm,
1255: JTF_IH_MEDIA_ITEM_LC_SEGS mseg,
1256: JTF_IH_MEDIA_ITM_LC_SEG_TYS mtyp,
1257: (
1258: select name, max(route_classification_id) route_classification_id
1259: from iem_route_classifications
2304: 0 accumulated_emails_week_plus,
2305: 0 EMAILS_REROUTED_IN_PERIOD,
2306: 0 LEADS_CREATED_IN_PERIOD
2307: FROM
2308: (SELECT /*+ index (mseg jtf_ih_media_item_lc_segs_n3) use_nl(mseg mitm) */
2309: nvl(mitm.source_id, -1) EMAIL_ACCOUNT_ID,
2310: nvl(irc.route_classification_id, -1) EMAIL_CLASSIFICATION_ID,
2311: nvl(mseg.resource_id, -1) AGENT_ID,
2312: nvl(( SELECT
2321: trunc(mseg.start_date_time) PERIOD_START_DATE,
2322: to_number(to_char(mseg.start_date_time, 'J')) TIME_ID
2323: FROM
2324: JTF_IH_MEDIA_ITEMS mitm,
2325: JTF_IH_MEDIA_ITEM_LC_SEGS mseg,
2326: JTF_IH_MEDIA_ITM_LC_SEG_TYS mtyp,
2327: (
2328: select name, max(route_classification_id) route_classification_id
2329: from iem_route_classifications
2339: AND EXISTS (
2340: SELECT
2341: 1
2342: FROM
2343: jtf_ih_media_item_lc_segs mseg1,
2344: jtf_ih_media_itm_lc_seg_tys mtys1
2345: WHERE mseg1.media_id = mitm.media_id
2346: AND mtys1.milcs_type_id = mseg1.milcs_type_id
2347: AND mtys1.milcs_code IN ( 'EMAIL_TRANSFERRED','EMAIL_ESCALATED') )
3050: 0 EMAILS_REROUTED_IN_PERIOD,
3051: 0 LEADS_CREATED_IN_PERIOD
3052: FROM
3053: JTF_IH_MEDIA_ITEMS mitm,
3054: JTF_IH_MEDIA_ITEM_LC_SEGS segs,
3055: JTF_IH_MEDIA_ITM_LC_SEG_TYS seg_type,
3056: JTF_IH_ACTIVITIES actv,
3057: JTF_IH_INTERACTIONS intr,
3058: --
3440: 1 FLAG
3441: FROM
3442: (
3443: /* Query 7 -Part I*/
3444: SELECT /*+ ordered INDEX(MSEG JTF_IH_MEDIA_ITEM_LC_SEGS_N3) use_nl(mseg,mitm,act,int) */
3445: mitm.media_id MEDIA_ID,
3446: mseg.milcs_id MILCS_ID,
3447: nvl(mitm.source_id, -1) EMAIL_ACCOUNT_ID,
3448: nvl(cls.route_classification_id, -1) EMAIL_CLASSIFICATION_ID,
3450: first_value(int.party_id) over(partition by act.media_id order by act.interaction_id DESC) PARTY_ID,
3451: mseg.start_date_time SEG_START_DATE_TIME,
3452: mitm.start_date_time MEDIA_START_DATE_TIME
3453: FROM
3454: JTF_IH_MEDIA_ITEM_LC_SEGS mseg,
3455: JTF_IH_MEDIA_ITEMS mitm,
3456: JTF_IH_ACTIVITIES act,
3457: JTF_IH_INTERACTIONS int,
3458: (
3471: AND mseg.milcs_type_id in (g_fetch,g_transfer,g_a_routed,g_assigned,g_assign_open)
3472: AND NOT EXISTS
3473: (
3474: SELECT 1
3475: FROM JTF_IH_MEDIA_ITEM_LC_SEGS mseg1
3476: WHERE mseg.media_id = mseg1.media_id
3477: /* Commenting this join out because the supervisor can perform some of the operations below.
3478: Irrespective of which user did it, the email is not open any more */
3479: -- AND mseg.resource_id = mseg1.resource_id
3500: MEDIA_START_DATE_TIME,
3501: 0 FLAG
3502: FROM
3503: (
3504: SELECT /*+ ordered INDEX(MSEG JTF_IH_MEDIA_ITEM_LC_SEGS_N3) use_nl(mseg,mitm)*/
3505: mitm.media_id MEDIA_ID,
3506: nvl(mitm.source_id, -1) EMAIL_ACCOUNT_ID,
3507: nvl(cls.route_classification_id, -1) EMAIL_CLASSIFICATION_ID,
3508: nvl(mseg.resource_id, -1) RESOURCE_ID,
3519: START_DATE_TIME,
3520: inv2.start_date_time END_DATE_TIME,
3521: mitm.start_date_time MEDIA_START_DATE_TIME
3522: FROM
3523: JTF_IH_MEDIA_ITEM_LC_SEGS mseg,
3524: JTF_IH_MEDIA_ITEMS mitm,
3525: (
3526: select name, max(route_classification_id) route_classification_id
3527: from iem_route_classifications
3527: from iem_route_classifications
3528: group by name
3529: ) cls ,
3530: (
3531: SELECT /*+ INDEX(MSEG1 JTF_IH_MEDIA_ITEM_LC_SEGS_N3) */
3532: mseg1.media_id MEDIA_ID,
3533: mseg1.resource_id RESOURCE_ID,
3534: mseg1.start_date_time START_DATE_TIME
3535: FROM JTF_IH_MEDIA_ITEM_LC_SEGS mseg1
3531: SELECT /*+ INDEX(MSEG1 JTF_IH_MEDIA_ITEM_LC_SEGS_N3) */
3532: mseg1.media_id MEDIA_ID,
3533: mseg1.resource_id RESOURCE_ID,
3534: mseg1.start_date_time START_DATE_TIME
3535: FROM JTF_IH_MEDIA_ITEM_LC_SEGS mseg1
3536: WHERE mseg1.milcs_type_id IN (g_deleted,g_transferred,g_reply,g_assigned,g_rerouted_acct,g_rerouted_class,g_Requeued,g_escalated)
3537: AND mseg1.START_DATE_TIME < g_collect_end_date
3538: ) inv2
3539: WHERE mitm.media_id=mseg.media_id
3928: max(media_start_date_time) MEDIA_START_DATE_TIME,
3929: 1 FLAG
3930: FROM
3931: (
3932: SELECT /*+ ordered index(mseg JTF_IH_MEDIA_ITEM_LC_SEGS_N3) use_nl(mseg,mitm,act,int)*/
3933: mitm.media_id MEDIA_ID,
3934: mseg.milcs_id MILCS_ID,
3935: nvl(mitm.source_id, -1) EMAIL_ACCOUNT_ID,
3936: nvl(cls.route_classification_id, -1) EMAIL_CLASSIFICATION_ID,
3937: first_value(int.party_id) over(partition by act.media_id order by act.interaction_id DESC) PARTY_ID,
3938: mseg.start_date_time SEG_START_DATE_TIME,
3939: mitm.start_date_time MEDIA_START_DATE_TIME
3940: FROM
3941: JTF_IH_MEDIA_ITEM_LC_SEGS mseg,
3942: JTF_IH_MEDIA_ITEMS mitm,
3943: JTF_IH_ACTIVITIES act,
3944: JTF_IH_INTERACTIONS int,
3945: (
3960: AND NOT EXISTS
3961: (
3962: SELECT
3963: 1
3964: FROM JTF_IH_MEDIA_ITEM_LC_SEGS mseg1
3965: WHERE mseg.MEDIA_ID = mseg1.MEDIA_ID
3966: AND mseg1.MILCS_TYPE_ID in (
3967: G_FETCH, G_RESOLVED, G_A_REDIRECTED, G_A_DELETED, G_A_REPLY, G_OPEN, G_A_ROUTED, G_A_UPDATED_SR,
3968: G_ASSIGNED, G_ASSIGN_OPEN,G_DELETED)
3971: )
3972: )
3973: GROUP BY media_id, milcs_id,nvl(email_account_id, -1), nvl(email_classification_id, -1) ,nvl(party_id, -1)
3974: UNION
3975: SELECT /*+ ordered index(mseg2 JTF_IH_MEDIA_ITEM_LC_SEGS_N3) use_nl(mseg2,mitm2) */
3976: mitm2.media_id MEDIA_ID,
3977: nvl(mitm2.source_id, -1) EMAIL_ACCOUNT_ID,
3978: nvl(cls2.route_classification_id, -1) EMAIL_CLASSIFICATION_ID,
3979: nvl(inv1.party_id, -1) PARTY_ID,
3982: min(inv2.start_date_time) END_DATE_TIME,
3983: mitm2.start_date_time MEDIA_START_DATE_TIME,
3984: 0 FLAG
3985: FROM
3986: JTF_IH_MEDIA_ITEM_LC_SEGS mseg2,
3987: JTF_IH_MEDIA_ITEMS mitm2,
3988: (
3989: select name, max(route_classification_id) route_classification_id
3990: from iem_route_classifications
3990: from iem_route_classifications
3991: group by name
3992: ) cls2,
3993: (
3994: SELECT /*+ index(mseg3 JTF_IH_MEDIA_ITEM_LC_SEGS_N3) */
3995: mseg3.media_id,
3996: mseg3.resource_id,
3997: mseg3.start_date_time
3998: FROM JTF_IH_MEDIA_ITEM_LC_SEGS mseg3
3994: SELECT /*+ index(mseg3 JTF_IH_MEDIA_ITEM_LC_SEGS_N3) */
3995: mseg3.media_id,
3996: mseg3.resource_id,
3997: mseg3.start_date_time
3998: FROM JTF_IH_MEDIA_ITEM_LC_SEGS mseg3
3999: WHERE mseg3.milcs_type_id IN (G_FETCH,G_OPEN,G_A_ROUTED,G_RESOLVED,G_ASSIGNED,G_ASSIGN_OPEN,G_A_UPDATED_SR,G_A_REPLY,G_A_DELETED,G_A_REDIRECTED)
4000: AND mseg3.START_DATE_TIME < g_collect_end_date
4001: ) inv2,
4002: (
3999: WHERE mseg3.milcs_type_id IN (G_FETCH,G_OPEN,G_A_ROUTED,G_RESOLVED,G_ASSIGNED,G_ASSIGN_OPEN,G_A_UPDATED_SR,G_A_REPLY,G_A_DELETED,G_A_REDIRECTED)
4000: AND mseg3.START_DATE_TIME < g_collect_end_date
4001: ) inv2,
4002: (
4003: SELECT /*+ ordered index(segs JTF_IH_MEDIA_ITEM_LC_SEGS_N3) */
4004: distinct actv.media_id media_id,
4005: first_value(intr.party_id)
4006: over(partition by actv.media_id order by actv.interaction_id desc) party_id
4007: FROM
4004: distinct actv.media_id media_id,
4005: first_value(intr.party_id)
4006: over(partition by actv.media_id order by actv.interaction_id desc) party_id
4007: FROM
4008: jtf_ih_media_item_lc_segs segs,
4009: jtf_ih_activities actv,
4010: jtf_ih_interactions intr
4011: WHERE segs.media_id = actv.media_id
4012: AND actv.interaction_id = intr.interaction_id
6282:
6283: SELECT min(med.start_date_time)
6284: INTO l_min_media_date
6285: FROM jtf_ih_media_items med, jtf_ih_interactions int, jtf_ih_activities act,
6286: jtf_ih_media_item_lc_segs segs, jtf_ih_media_itm_lc_seg_tys tys
6287: WHERE med.media_item_type = 'EMAIL'
6288: AND int.interaction_id = act.interaction_id
6289: AND med.media_id = act.media_id
6290: AND med.media_id = segs.media_id