[Home] [Help]
14: g_debug_flag VARCHAR2(1) := 'N';
15:
16: G_TIME_DIM_MISSING EXCEPTION;
17:
18: TYPE g_media_id_tab IS TABLE OF jtf_ih_media_items.media_id%TYPE;
19: TYPE g_email_account_id_tab IS TABLE OF jtf_ih_media_items.source_id%TYPE;
20: TYPE g_email_classification_id_tab IS TABLE OF iem_route_classifications.route_classification_id%TYPE;
21: TYPE g_resource_id_tab IS TABLE OF bix_email_details_f.agent_id%TYPE;
22: TYPE g_party_id_tab IS TABLE OF bix_email_details_f.party_id%TYPE;
15:
16: G_TIME_DIM_MISSING EXCEPTION;
17:
18: TYPE g_media_id_tab IS TABLE OF jtf_ih_media_items.media_id%TYPE;
19: TYPE g_email_account_id_tab IS TABLE OF jtf_ih_media_items.source_id%TYPE;
20: TYPE g_email_classification_id_tab IS TABLE OF iem_route_classifications.route_classification_id%TYPE;
21: TYPE g_resource_id_tab IS TABLE OF bix_email_details_f.agent_id%TYPE;
22: TYPE g_party_id_tab IS TABLE OF bix_email_details_f.party_id%TYPE;
23: TYPE g_start_date_time_tab IS TABLE OF jtf_ih_media_item_lc_segs.start_date_time%TYPE;
21: TYPE g_resource_id_tab IS TABLE OF bix_email_details_f.agent_id%TYPE;
22: TYPE g_party_id_tab IS TABLE OF bix_email_details_f.party_id%TYPE;
23: TYPE g_start_date_time_tab IS TABLE OF jtf_ih_media_item_lc_segs.start_date_time%TYPE;
24: TYPE g_end_date_time_tab IS TABLE OF jtf_ih_media_item_lc_segs.end_date_time%TYPE;
25: TYPE g_media_start_date_time_tab IS TABLE OF jtf_ih_media_items.start_date_time%TYPE;
26: TYPE g_period_start_date_tab IS TABLE OF bix_email_details_f.period_start_date%TYPE;
27:
28: TYPE g_emails_open_tab IS TABLE OF bix_email_details_f.accumulated_open_emails%TYPE;
29: TYPE g_total_open_age_tab IS TABLE OF bix_email_details_f.accumulated_open_age%TYPE;
168: max(nvl(mitm.source_id, -1)) source_id,
169: max(nvl(irc.route_classification_id, -1)) route_classification_id,
170: max(nvl(iview.depth, 0)) depth
171: FROM
172: jtf_ih_media_items mitm,
173: jtf_ih_activities actv,
174: jtf_ih_interactions intr,
175: --
176: --Changes for R12
190: SELECT
191: actv.interaction_id interaction_id,
192: 1 depth
193: FROM jtf_ih_activities actv,
194: /* jtf_ih_media_items imtm,*/
195: jtf_ih_media_item_lc_segs mseg,
196: jtf_ih_media_itm_lc_seg_tys mtys
197: WHERE actv.media_id = mseg.media_id
198: AND mtys.milcs_type_id = mseg.milcs_type_id
218: --first_value(milcs_code) over (partition by mseg.media_id order by mseg.start_Date_time desc) milcs_code
219: FROM
220: jtf_ih_media_itm_lc_seg_tys mtys,
221: jtf_ih_media_item_lc_segs mseg,
222: jtf_ih_media_items mitm,
223: jtf_ih_activities actv ,
224: (
225: SELECT to_number(decode(instr(sys_connect_by_path(intr3.interact_interaction_idrelates, ':'), ':', 2), 0,
226: substr(sys_connect_by_path(intr3.interact_interaction_idrelates, ':'), 2),
278: TYPE root_interaction_id_tab IS TABLE OF jtf_ih_interactions.interaction_id%TYPE;
279: TYPE agent_id_tab IS TABLE OF jtf_ih_interactions.resource_id%TYPE;
280: TYPE party_id_tab IS TABLE OF jtf_ih_interactions.party_id%TYPE;
281: TYPE start_date_time_tab IS TABLE OF jtf_ih_interactions.start_date_time%TYPE;
282: TYPE source_id_tab IS TABLE OF jtf_ih_media_items.source_id%TYPE;
283: TYPE route_classification_id_tab IS TABLE OF iem_route_classifications.route_classification_id%TYPE;
284: TYPE one_done_rsln_tab IS TABLE OF bix_email_details_f.one_rsln_in_period%TYPE;
285: TYPE two_done_rsln_tab IS TABLE OF bix_email_details_f.two_rsln_in_period%TYPE;
286: TYPE three_done_rsln_tab IS TABLE OF bix_email_details_f.three_rsln_in_period%TYPE;
705: 'EMAIL_REROUTED_DIFF_ACCT',1,
706: 'EMAIL_REQUEUED',1 )) EMAILS_REROUTED_IN_PERIOD,
707: 0 LEADS_CREATED_IN_PERIOD
708: FROM
709: JTF_IH_MEDIA_ITEMS mitm,
710: JTF_IH_MEDIA_ITEM_LC_SEGS mseg,
711: (
712: SELECT /*+ use_hash(mseg1) use_hash(mtyp1) use_hash(mseg2) use_hash(mtyp2)
713: PARALLEL(mseg1) PARALLEL(mtyp1) PARALLEL(mseg2) PARALLEL(mtyp2) */
802: 0 emails_orr_count_in_period,
803: 0 EMAILS_REROUTED_IN_PERIOD,
804: 0 LEADS_CREATED_IN_PERIOD
805: FROM
806: JTF_IH_MEDIA_ITEMS mitm,
807: (
808: SELECT /*+ use_hash(actv) use_hash(intr) PARALLEL(actv) PARALLEL(intr) */
809: actv.media_id media_id,
810: min(intr.party_id) party_id
878: SUM(DECODE(actv.action_id,71,1)) LEADS_CREATED_IN_PERIOD
879: FROM
880: JTF_IH_ACTIVITIES actv,
881: JTF_IH_INTERACTIONS intr,
882: JTF_IH_MEDIA_ITEMS mitm,
883: (select
884: actv.interaction_id interaction_id,
885: max(mitm.classification) classification
886: from
885: max(mitm.classification) classification
886: from
887: jtf_ih_activities actv,
888: jtf_ih_activities actv1,
889: jtf_ih_media_items mitm
890: where actv.start_date_time BETWEEN g_collect_start_date AND g_collect_end_date
891: and actv.interaction_id = actv1.interaction_id
892: and actv1.media_id = mitm.media_id
893: and mitm.direction = 'INBOUND'
970: WHERE actv.start_date_time BETWEEN g_collect_start_date AND g_collect_end_date
971: AND actv.interaction_id = intr.interaction_id
972: GROUP BY actv.media_id
973: ) inv2,
974: JTF_IH_MEDIA_ITEMS mitm,
975: JTF_IH_MEDIA_ITEM_LC_SEGS mseg,
976: JTF_IH_MEDIA_ITM_LC_SEG_TYS mtyp,
977: --
978: --Changes for R12
1049: COUNT(DISTINCT intr.interaction_id) emails_orr_count_in_period,
1050: 0 EMAILS_REROUTED_IN_PERIOD,
1051: 0 LEADS_CREATED_IN_PERIOD
1052: FROM
1053: JTF_IH_MEDIA_ITEMS mitm,
1054: JTF_IH_ACTIVITIES actv,
1055: JTF_IH_INTERACTIONS intr,
1056: --
1057: --Changes for R12
1120: COUNT(DISTINCT intr.interaction_id) emails_orr_count_in_period,
1121: 0 EMAILS_REROUTED_IN_PERIOD,
1122: 0 LEADS_CREATED_IN_PERIOD
1123: FROM
1124: JTF_IH_MEDIA_ITEMS mitm,
1125: JTF_IH_MEDIA_ITEM_LC_SEGS segs,
1126: JTF_IH_MEDIA_ITM_LC_SEG_TYS seg_type,
1127: JTF_IH_ACTIVITIES actv,
1128: JTF_IH_INTERACTIONS intr,
1567: --1/2hr do we need to modify the collect end data to capture data upto the second?
1568: g_collect_end_date -- - (( 29 * 60 + 59 )/(24*60*60))
1569: PERIOD_END_DATE_TIME
1570: FROM
1571: JTF_IH_MEDIA_ITEMS mitm,
1572: JTF_IH_MEDIA_ITEM_LC_SEGS mseg,
1573: JTF_IH_MEDIA_ITM_LC_SEG_TYS mtys,
1574: --
1575: --Changes for R12
1650: to_date(to_char(min(inv2.start_date_time) ,'YYYY/MM/DD ')|| '00:'
1651: || '00',
1652: 'YYYY/MM/DD HH24:MI')) PERIOD_END_DATE_TIME
1653: FROM
1654: JTF_IH_MEDIA_ITEMS mitm2,
1655: (
1656: SELECT /*+ use_hash(mseg3) use_hash(mtys3) */
1657: mseg3.media_id,
1658: mseg3.resource_id,
2171: --1/2hr
2172: g_collect_end_date
2173: PERIOD_END_DATE_TIME
2174: FROM
2175: JTF_IH_MEDIA_ITEMS mitm,
2176: JTF_IH_MEDIA_ITEM_LC_SEGS mseg,
2177: JTF_IH_MEDIA_ITM_LC_SEG_TYS mtys,
2178: --
2179: --Changes for R12
2248: to_date(to_char(min(inv2.start_date_time) ,'YYYY/MM/DD ')|| '00:'
2249: || '00',
2250: 'YYYY/MM/DD HH24:MI')) PERIOD_END_DATE_TIME
2251: FROM
2252: JTF_IH_MEDIA_ITEMS mitm,
2253: (
2254: SELECT /*+ use_hash(mseg1) use_hash(mtys1) */
2255: mseg1.media_id MEDIA_ID,
2256: mseg1.resource_id RESOURCE_ID,