[Home] [Help]
22:
23: TYPE WorkerList is table of NUMBER index by binary_integer;
24: g_worker WorkerList;
25:
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;
23: TYPE WorkerList is table of NUMBER index by binary_integer;
24: g_worker WorkerList;
25:
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;
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;
36: G_A_REPLY NUMBER;
297: SELECT /*+ ordered */
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
334: jtf_ih_interaction_inters
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
351: SELECT /*+ ordered */
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
411: SELECT
412: incr.interaction_id
413: FROM jtf_ih_interactions incr,
414: jtf_ih_activities actv,
415: jtf_ih_media_items imtm
416: WHERE incr.start_date_time between g_collect_start_date and g_collect_end_date
417: AND actv.interaction_id = incr.interaction_id
418: AND imtm.media_id = actv.media_id
419: AND imtm.media_item_type = 'EMAIL'
438: SELECT
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
464: (SELECT
465: intr.interact_interaction_id
466: FROM jtf_ih_interaction_inters intr,
467: jtf_ih_activities actv,
468: jtf_ih_media_items imtm
469: WHERE intr.creation_date between g_collect_start_date and g_collect_end_date
470: AND actv.interaction_id = intr.interact_interaction_id
471: AND imtm.media_id = actv.media_id
472: AND imtm.media_item_type = 'EMAIL'
479: SELECT
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
499: WHERE intr.interact_interaction_id = inv2.interact_interaction_idrelates)
500: AND EXISTS (
501: SELECT 1
502: FROM jtf_ih_activities actv,
503: jtf_ih_media_items imtm
504: WHERE actv.interaction_id = inv2.interact_interaction_idrelates
505: AND actv.media_id = imtm.media_id
506: AND imtm.media_item_type = 'EMAIL'
507: AND imtm.direction = 'INBOUND')
511: TYPE root_interaction_id_tab IS TABLE OF jtf_ih_interactions.interaction_id%TYPE;
512: TYPE agent_id_tab IS TABLE OF jtf_ih_interactions.resource_id%TYPE;
513: TYPE party_id_tab IS TABLE OF jtf_ih_interactions.party_id%TYPE;
514: TYPE start_date_time_tab IS TABLE OF jtf_ih_interactions.start_date_time%TYPE;
515: TYPE source_id_tab IS TABLE OF jtf_ih_media_items.source_id%TYPE;
516: TYPE route_classification_id_tab IS TABLE OF iem_route_classifications.route_classification_id%TYPE;
517: TYPE one_done_rsln_tab IS TABLE OF bix_email_details_f.one_rsln_in_period%TYPE;
518: TYPE two_done_rsln_tab IS TABLE OF bix_email_details_f.two_rsln_in_period%TYPE;
519: TYPE three_done_rsln_tab IS TABLE OF bix_email_details_f.three_rsln_in_period%TYPE;
630: l_classification_id(i)
631: FROM
632: jtf_ih_interactions intr,
633: jtf_ih_activities actv,
634: jtf_ih_media_items mitm,
635: --
636: --Changes for R12
637: --
638: (
1250: decode(mtyp.milcs_code,'EMAIL_AUTO_DELETED',1) EMAILS_AUTO_DELETED_IN_PERIOD,
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
1586: ), -1) PARTY_ID,
1587: trunc(mitm.start_date_time) PERIOD_START_DATE,
1588: to_number(to_char(mitm.start_date_time, 'J')) TIME_ID
1589: FROM
1590: JTF_IH_MEDIA_ITEMS mitm,
1591: (
1592: select name, max(route_classification_id) route_classification_id
1593: from iem_route_classifications
1594: group by name
1940: actv.action_id ACTION_ID
1941: FROM
1942: JTF_IH_ACTIVITIES actv,
1943: JTF_IH_INTERACTIONS intr,
1944: JTF_IH_MEDIA_ITEMS mitm,
1945: (select /*+ index(actv jtf_ih_activities_n11) use_nl(actv actv1 mitm) */
1946: actv.interaction_id interaction_id,
1947: max(mitm.classification) classification
1948: from
1946: actv.interaction_id interaction_id,
1947: max(mitm.classification) classification
1948: from
1949: jtf_ih_activities actv,
1950: jtf_ih_media_items mitm
1951: where
1952: actv.start_date_time BETWEEN g_collect_start_date AND g_collect_end_date
1953: and actv.media_id = mitm.media_id
1954: and mitm.direction = 'INBOUND'
2320: ), -1) PARTY_ID,
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
2697: NVL(intr.result_id,-1) RESULT_ID,
2698: NVL(intr.reason_id,-1) REASON_ID,
2699: intr.interaction_id INTERACTION_ID
2700: FROM
2701: JTF_IH_MEDIA_ITEMS mitm,
2702: JTF_IH_ACTIVITIES actv,
2703: JTF_IH_INTERACTIONS intr,
2704: (
2705: select name, max(route_classification_id) route_classification_id
3049: 0 accumulated_emails_week_plus,
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,
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: (
3459: select name, max(route_classification_id) route_classification_id
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
3528: group by name
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: (
3946: select name, max(route_classification_id) route_classification_id
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
3991: group by name
6276: -- ?? Need code review for date logic - do we need TRUNC etc
6277: --Check to see if the media or interaction table was updated after the last time the ICI program
6278: --was run. If so, store the date so that we go back and recollect those dates to get the latest
6279: --information. This will happen in case of re-routes where the ACCOUNT/CLASSIFICATION on
6280: --JTF_IH_MEDIA_ITEMS changes or it can happen when the PARTY_ID changes.
6281: --
6282:
6283: SELECT min(med.start_date_time)
6284: INTO l_min_media_date
6281: --
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