APPS.BIX_EMAILS_SUMMARY_PKG dependencies on JTF_IH_INTERACTION_INTERS

Line 311: FROM jtf_ih_interaction_inters inter

307: AND mtys.milcs_type_id = mseg.milcs_type_id
308: AND mtys.milcs_code IN ('EMAIL_REPLY','EMAIL_AUTO_REPLY')
309: AND NOT EXISTS (
310: SELECT 1
311: FROM jtf_ih_interaction_inters inter
312: WHERE inter.interact_interaction_idrelates = actv.interaction_id )
313: AND rownum <= 1;
314: EXCEPTION
315: WHEN NO_DATA_FOUND THEN

Line 334: jtf_ih_interaction_inters

330: (SELECT
331: interact_interaction_id interaction_id /* Child interaction*/,
332: level depth
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,

Line 393: interactions in jtf_ih_interaction_inters table. If the customer reply back to the agent response then the entry will

389: END get_curr_prev_depth;
390:
391:
392: /* This procedure collects One, two , three and Four done resolutiions. Email center keeps the threads of email
393: interactions in jtf_ih_interaction_inters table. If the customer reply back to the agent response then the entry will
394: be created in the above table with old and new interaction. This table stores both parent and child interaction id.
395:
396: We can find the depth of thread from this table. If the table does not have entry in this
397: table for replied email, then the email interaction is one and done ,

Line 423: FROM jtf_ih_interaction_inters inter

419: AND imtm.media_item_type = 'EMAIL'
420: AND imtm.direction = 'INBOUND'
421: AND NOT EXISTS (
422: SELECT 1
423: FROM jtf_ih_interaction_inters inter
424: WHERE
425: --?????? comment out???
426: inter.INTERACT_INTERACTION_IDRELATES = incr.interaction_id
427: OR

Line 455: FROM jtf_ih_interaction_inters inter

451: AND mtys.milcs_code IN ( 'EMAIL_REPLY','EMAIL_AUTO_REPLY')
452: AND mseg.start_date_time BETWEEN g_collect_start_date AND g_collect_end_date
453: AND NOT EXISTS (
454: SELECT 1
455: FROM jtf_ih_interaction_inters inter
456: WHERE inter.INTERACT_INTERACTION_IDRELATES = incr.interaction_id
457: OR inter.interact_interaction_id = incr.interaction_id)
458: UNION ALL
459: SELECT interact_interaction_idrelates

Line 462: FROM jtf_ih_interaction_inters

458: UNION ALL
459: SELECT interact_interaction_idrelates
460: FROM (
461: SELECT interact_interaction_idrelates
462: FROM jtf_ih_interaction_inters
463: START WITH interact_interaction_id IN
464: (SELECT
465: intr.interact_interaction_id
466: FROM jtf_ih_interaction_inters intr,

Line 466: FROM jtf_ih_interaction_inters intr,

462: FROM jtf_ih_interaction_inters
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

Line 476: FROM jtf_ih_interaction_inters inter

472: AND imtm.media_item_type = 'EMAIL'
473: AND imtm.direction = 'INBOUND'
474: AND NOT EXISTS (
475: SELECT 1
476: FROM jtf_ih_interaction_inters inter
477: WHERE inter.INTERACT_INTERACTION_IDRELATES = intr.interact_interaction_id)
478: UNION
479: SELECT
480: intr.interact_interaction_id

Line 481: FROM jtf_ih_interaction_inters intr,

477: WHERE inter.INTERACT_INTERACTION_IDRELATES = intr.interact_interaction_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

Line 494: FROM jtf_ih_interaction_inters inter

490: AND mtys.milcs_code IN ( 'EMAIL_REPLY','EMAIL_AUTO_REPLY')
491: AND mseg.start_date_time BETWEEN g_collect_start_date AND g_collect_end_date
492: AND NOT EXISTS (
493: SELECT 1
494: FROM jtf_ih_interaction_inters inter
495: WHERE inter.INTERACT_INTERACTION_IDRELATES = intr.interact_interaction_id))
496: CONNECT BY PRIOR interact_interaction_idrelates = interact_interaction_id ) inv2
497: WHERE NOT EXISTS (
498: SELECT 1 FROM jtf_ih_interaction_inters intr

Line 498: SELECT 1 FROM jtf_ih_interaction_inters intr

494: FROM jtf_ih_interaction_inters inter
495: WHERE inter.INTERACT_INTERACTION_IDRELATES = intr.interact_interaction_id))
496: CONNECT BY PRIOR interact_interaction_idrelates = interact_interaction_id ) inv2
497: WHERE NOT EXISTS (
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,