[Home] [Help]
296: /* if the interaction is a new one with no child , then the depth is 1 */
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
333: FROM
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
350: BEGIN
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
410: (
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
437: UNION ALL
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
463: START WITH interact_interaction_id IN
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
478: UNION
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
498: SELECT 1 FROM jtf_ih_interaction_inters intr
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'
629: l_email_account_id(i),
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: --
1208: nvl(mseg.resource_id, -1) AGENT_ID,
1209: nvl(( SELECT
1210: distinct first_value(intr.party_id) over(order by actv.interaction_id desc) party_id
1211: FROM
1212: jtf_ih_activities actv,
1213: jtf_ih_interactions intr
1214: WHERE
1215: mitm.media_id = actv.media_id
1216: AND actv.interaction_id = intr.interaction_id
1577: -1 AGENT_ID,
1578: nvl(( SELECT
1579: distinct first_value(intr.party_id) over(order by actv.interaction_id desc) party_id
1580: FROM
1581: jtf_ih_activities actv,
1582: jtf_ih_interactions intr
1583: WHERE
1584: mitm.media_id = actv.media_id
1585: AND actv.interaction_id = intr.interaction_id
1888: program_update_date
1889: FROM
1890: (
1891: /* Query 3 */
1892: SELECT /*+ index(actv jtf_ih_activities_n11) */
1893: nvl(email_account_id, -1) EMAIL_ACCOUNT_ID,
1894: nvl(email_classification_id, -1) EMAIL_CLASSIFICATION_ID,
1895: nvl(agent_id, -1) AGENT_ID,
1896: nvl(party_id, -1) PARTY_ID,
1938: trunc(actv.start_date_time) PERIOD_START_DATE,
1939: to_number(to_char(actv.start_date_time, 'J')) TIME_ID,
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,
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
1949: jtf_ih_activities actv,
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
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
2311: nvl(mseg.resource_id, -1) AGENT_ID,
2312: nvl(( SELECT
2313: distinct first_value(intr.party_id) over(order by actv.interaction_id desc) party_id
2314: FROM
2315: jtf_ih_activities actv,
2316: jtf_ih_interactions intr
2317: WHERE
2318: mitm.media_id = actv.media_id
2319: AND actv.interaction_id = intr.interaction_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
2706: from iem_route_classifications
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: --
3059: --Changes for R12
3060: --
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
3460: from iem_route_classifications
3508: nvl(mseg.resource_id, -1) RESOURCE_ID,
3509: nvl(( SELECT
3510: distinct first_value(intr.party_id) over(order by actv.interaction_id desc) party_id
3511: FROM
3512: jtf_ih_activities actv,
3513: jtf_ih_interactions intr
3514: WHERE
3515: mitm.media_id = actv.media_id
3516: AND actv.interaction_id = intr.interaction_id
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
3947: from iem_route_classifications
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
4013: AND segs.START_DATE_TIME BETWEEN g_collect_start_date AND g_collect_end_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