eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.IEM_CLIENT_PUB dependencies on IEM_RT_INTERACTIONS

Line 199: select IEM_RT_INTERACTIONS_S1.nextval into l_i_sequence from DUAL;

195: if ( l_email_data_rec.IH_INTERACTION_ID is not null ) then
196: l_ih_creator := 'S'; -- server created
197: end if;
198:
199: select IEM_RT_INTERACTIONS_S1.nextval into l_i_sequence from DUAL;
200: INSERT INTO iem_rt_interactions (
201: rt_interaction_id, resource_id, customer_id, contact_id, type,
202: status, expire, created_by, creation_date, last_updated_by,
203: last_update_date, last_update_login, parent_interaction_id,

Line 200: INSERT INTO iem_rt_interactions (

196: l_ih_creator := 'S'; -- server created
197: end if;
198:
199: select IEM_RT_INTERACTIONS_S1.nextval into l_i_sequence from DUAL;
200: INSERT INTO iem_rt_interactions (
201: rt_interaction_id, resource_id, customer_id, contact_id, type,
202: status, expire, created_by, creation_date, last_updated_by,
203: last_update_date, last_update_login, parent_interaction_id,
204: service_request_id, inb_tag_id, interaction_id, ih_creator,

Line 440: FROM iem_rt_interactions

436: inb_tag_id, lead_id, resource_id, relationship_id
437: INTO x_customer_id, x_contact_id, x_interaction_id, x_parent_ih_id,
438: x_service_request_id, x_service_request_action, x_mc_parameter_id, x_tag_id,
439: x_lead_id, x_resource_id, x_relationship_id
440: FROM iem_rt_interactions
441: WHERE rt_interaction_id = l_rt_interaction_id;
442:
443: EXCEPTION
444: WHEN OTHERS THEN

Line 684: FROM iem_rt_interactions

680: service_request_id, service_request_action, inb_tag_id, lead_id
681: INTO x_customer_id, x_contact_id, x_interaction_id,
682: x_parent_interaction_id, x_service_request_id,
683: x_service_request_action, x_tag_id, x_lead_id
684: FROM iem_rt_interactions
685: WHERE rt_interaction_id = x_rt_interaction_id;
686:
687: SELECT rt_classification_id into x_route_classification_id
688: FROM IEM_RT_PROC_EMAILS

Line 1102: SELECT IEM_RT_INTERACTIONS_S1.nextval INTO l_i_sequence FROM DUAL;

1098: OR p_rt_media_item_id is null) THEN
1099:
1100: -- Pure outbound message as there is no associated rt_media_item_id.
1101: -- Insert into rt tables.
1102: SELECT IEM_RT_INTERACTIONS_S1.nextval INTO l_i_sequence FROM DUAL;
1103: INSERT INTO iem_rt_interactions (
1104: rt_interaction_id, resource_id, customer_id, type,
1105: status, expire, created_by, creation_date, last_updated_by,
1106: last_update_date, last_update_login, interaction_id,

Line 1103: INSERT INTO iem_rt_interactions (

1099:
1100: -- Pure outbound message as there is no associated rt_media_item_id.
1101: -- Insert into rt tables.
1102: SELECT IEM_RT_INTERACTIONS_S1.nextval INTO l_i_sequence FROM DUAL;
1103: INSERT INTO iem_rt_interactions (
1104: rt_interaction_id, resource_id, customer_id, type,
1105: status, expire, created_by, creation_date, last_updated_by,
1106: last_update_date, last_update_login, interaction_id,
1107: service_request_id, mc_parameter_id, service_request_action,

Line 1506: UPDATE iem_rt_interactions SET

1502: -- dbms_output.put_line('In UPDATE ' || SQLERRM);
1503: -- END;
1504:
1505: -- BEGIN
1506: UPDATE iem_rt_interactions SET
1507: CUSTOMER_ID = decode(p_customer_id, G_NUM_NOP, CUSTOMER_ID, p_customer_id),
1508: CONTACT_ID = decode(p_contact_id, G_NUM_NOP, contact_id, p_contact_id),
1509: RELATIONSHIP_ID = decode(p_relationship_id, G_NUM_NOP, relationship_id, p_relationship_id),
1510: INTERACTION_ID = decode(p_interaction_id, G_NUM_NOP, interaction_id, p_interaction_id),

Line 1631: -- Expire iem_rt_media_items and iem_rt_interactions if status is 'Cancel'

1627: end if;
1628: end if; -- 'Cancel'
1629: end if;
1630:
1631: -- Expire iem_rt_media_items and iem_rt_interactions if status is 'Cancel'
1632: -- for pure outbound items.
1633: if (p_status = 'C') then
1634:
1635: SELECT type INTO l_type FROM iem_rt_interactions

Line 1635: SELECT type INTO l_type FROM iem_rt_interactions

1631: -- Expire iem_rt_media_items and iem_rt_interactions if status is 'Cancel'
1632: -- for pure outbound items.
1633: if (p_status = 'C') then
1634:
1635: SELECT type INTO l_type FROM iem_rt_interactions
1636: WHERE rt_interaction_id = l_rt_interaction_id;
1637:
1638: if ( l_type = G_OUTBOUND ) then
1639:

Line 1643: UPDATE iem_rt_interactions SET expire = G_EXPIRE

1639:
1640: UPDATE iem_rt_media_items SET expire = G_EXPIRE
1641: WHERE rt_media_item_id = p_rt_media_item_id;
1642:
1643: UPDATE iem_rt_interactions SET expire = G_EXPIRE
1644: WHERE rt_interaction_id = l_rt_interaction_id;
1645:
1646: end if;
1647: end if;

Line 1780: FROM iem_rt_interactions a, iem_rt_media_items b

1776: decode(a.parent_interaction_id, NULL, fnd_api.g_miss_num, a.parent_interaction_id),
1777: a.service_request_id, a.lead_id, a.contact_id, a.relationship_id
1778: INTO l_rt_interaction_id, l_interaction_id, l_resource_id, l_start_date,
1779: l_parent_ih_id, x_sr_id, x_lead_id, l_contact_id, l_relationship_id
1780: FROM iem_rt_interactions a, iem_rt_media_items b
1781: WHERE b.rt_media_item_id = p_rt_media_item_id
1782: AND a.rt_interaction_id = b.rt_interaction_id
1783: AND a.expire <> G_EXPIRE;
1784:

Line 1855: UPDATE iem_rt_interactions set interaction_id = l_interaction_id,

1851: if(l_status <> FND_API.G_RET_STS_SUCCESS) then
1852: raise IHError;
1853: end if;
1854:
1855: UPDATE iem_rt_interactions set interaction_id = l_interaction_id,
1856: ih_creator = 'Y'
1857: WHERE rt_interaction_id = l_rt_interaction_id;
1858: END IF;
1859:

Line 2135: update iem_rt_interactions set expire = G_EXPIRE

2131:
2132: UPDATE iem_outbox_errors SET expire = G_EXPIRE
2133: WHERE rt_media_item_id = p_rt_media_item_id;
2134:
2135: update iem_rt_interactions set expire = G_EXPIRE
2136: where rt_interaction_id = l_rt_interaction_id;
2137:
2138: update iem_rt_media_items set expire = G_EXPIRE
2139: where rt_media_item_id = p_rt_media_item_id;

Line 2155: FROM iem_rt_interactions

2151: relationship_id, status, mc_parameter_id
2152: INTO l_interaction_id, l_ih_creator, l_sr_id, l_sr_action, l_parent_ih_id,
2153: l_lead_id, l_action_id, l_contact_id, l_to_resource_id, l_relationship_id,
2154: l_rt_ih_status, l_mc_parameter_id
2155: FROM iem_rt_interactions
2156: WHERE rt_interaction_id = l_rt_interaction_id
2157: AND expire in (G_ACTIVE, G_QUEUEOUT, G_PROCESSING);
2158: EXCEPTION
2159: WHEN OTHERS THEN

Line 2237: FROM iem_rt_interactions

2233: end if;
2234:
2235: SELECT customer_id, resource_id, creation_date, nvl(spv_resource_id, -1)
2236: INTO l_customer_id, l_resource_id, l_start_date, l_spv_resource_id
2237: FROM iem_rt_interactions
2238: WHERE rt_interaction_id = l_rt_interaction_id;
2239:
2240: -- use supervisor resource id if exists
2241: l_reroute_resource_id := l_resource_id;

Line 2361: UPDATE iem_rt_interactions set IH_CREATOR = 'Y', interaction_id = l_interaction_id

2357: x_interaction_id => l_interaction_id,
2358: p_interaction_rec => l_interaction_rec
2359: );
2360:
2361: UPDATE iem_rt_interactions set IH_CREATOR = 'Y', interaction_id = l_interaction_id
2362: WHERE rt_interaction_id = l_rt_interaction_id;
2363:
2364: if(l_status <> FND_API.G_RET_STS_SUCCESS) then
2365: raise IHError;

Line 2765: UPDATE iem_rt_interactions SET expire = G_EXPIRE

2761: -- update RTI: send, delete, reroute
2762: IF ((UPPER(p_action) = 'S') OR (UPPER(p_action) = 'D') OR (UPPER(p_action)='V') -- Add resolve
2763: OR (UPPER(p_action) = 'X')) THEN
2764: begin
2765: UPDATE iem_rt_interactions SET expire = G_EXPIRE
2766: WHERE rt_interaction_id = l_rt_interaction_id;
2767:
2768: UPDATE iem_rt_media_items SET expire = G_EXPIRE
2769: WHERE rt_interaction_id = l_rt_interaction_id;

Line 2808: select IEM_RT_INTERACTIONS_S1.nextval into l_i_sequence from DUAL;

2804: select agent_id into l_tran_to_acct_id from iem_agents
2805: where resource_id = p_to_resource_id
2806: and email_account_id = l_email_account_id;
2807:
2808: select IEM_RT_INTERACTIONS_S1.nextval into l_i_sequence from DUAL;
2809: INSERT INTO iem_rt_interactions (
2810: rt_interaction_id, resource_id, customer_id, type,
2811: status, expire, created_by, creation_date, last_updated_by,
2812: last_update_date, last_update_login, contact_id, inb_tag_id,

Line 2809: INSERT INTO iem_rt_interactions (

2805: where resource_id = p_to_resource_id
2806: and email_account_id = l_email_account_id;
2807:
2808: select IEM_RT_INTERACTIONS_S1.nextval into l_i_sequence from DUAL;
2809: INSERT INTO iem_rt_interactions (
2810: rt_interaction_id, resource_id, customer_id, type,
2811: status, expire, created_by, creation_date, last_updated_by,
2812: last_update_date, last_update_login, contact_id, inb_tag_id,
2813: lead_id, parent_interaction_id, service_request_id,

Line 2821: FROM iem_rt_interactions

2817: l_last_updated_by, SYSDATE, l_last_update_login,
2818: l_contact_id, inb_tag_id, l_tran_lead_id,
2819: l_interaction_id, l_sr_id,
2820: l_relationship_id
2821: FROM iem_rt_interactions
2822: WHERE rt_interaction_id = l_rt_interaction_id;
2823:
2824: select IEM_RT_MEDIA_ITEMS_S1.nextval into l_m_sequence from DUAL;
2825: INSERT INTO iem_rt_media_items (

Line 2863: UPDATE iem_rt_interactions SET expire = G_EXPIRE

2859: end if;
2860: end if; -- MLCS second agent
2861:
2862: -- Transferrers RT Interaction and RT media are expired
2863: UPDATE iem_rt_interactions SET expire = G_EXPIRE
2864: WHERE rt_interaction_id = l_rt_interaction_id;
2865:
2866: -- This expires both the inbound and any outbounds
2867: -- associated with the original message. Change

Line 3106: FROM iem_rt_media_items m, iem_rt_interactions i

3102: SELECT m.rfc822_message_id,m.email_account_id, m.agent_account_id,
3103: m.email_type, m.status, m.version, m.rt_interaction_id,
3104: m.db_server_id, m.rt_media_item_id, m.folder_name, m.folder_uid,
3105: i.customer_id
3106: FROM iem_rt_media_items m, iem_rt_interactions i
3107: WHERE i.TYPE = G_OUTBOUND
3108: AND i.RT_INTERACTION_ID = m.RT_INTERACTION_ID
3109: AND m.RESOURCE_ID = p_resource_id
3110: AND m.EMAIL_TYPE = G_OUTBOUND

Line 3381: SELECT iem_rt_interactions.RT_INTERACTION_ID

3377: i NUMBER;
3378: l_message_id NUMBER;
3379:
3380: CURSOR del_rt_csr IS
3381: SELECT iem_rt_interactions.RT_INTERACTION_ID
3382: FROM iem_rt_interactions, iem_rt_media_items
3383: WHERE iem_rt_interactions.expire='N' and iem_rt_interactions.type = 'O'
3384: AND SYSDATE - iem_rt_interactions.LAST_UPDATE_DATE > 30
3385: AND iem_rt_media_items.RT_INTERACTION_ID = iem_rt_interactions.RT_INTERACTION_ID;

Line 3382: FROM iem_rt_interactions, iem_rt_media_items

3378: l_message_id NUMBER;
3379:
3380: CURSOR del_rt_csr IS
3381: SELECT iem_rt_interactions.RT_INTERACTION_ID
3382: FROM iem_rt_interactions, iem_rt_media_items
3383: WHERE iem_rt_interactions.expire='N' and iem_rt_interactions.type = 'O'
3384: AND SYSDATE - iem_rt_interactions.LAST_UPDATE_DATE > 30
3385: AND iem_rt_media_items.RT_INTERACTION_ID = iem_rt_interactions.RT_INTERACTION_ID;
3386:

Line 3383: WHERE iem_rt_interactions.expire='N' and iem_rt_interactions.type = 'O'

3379:
3380: CURSOR del_rt_csr IS
3381: SELECT iem_rt_interactions.RT_INTERACTION_ID
3382: FROM iem_rt_interactions, iem_rt_media_items
3383: WHERE iem_rt_interactions.expire='N' and iem_rt_interactions.type = 'O'
3384: AND SYSDATE - iem_rt_interactions.LAST_UPDATE_DATE > 30
3385: AND iem_rt_media_items.RT_INTERACTION_ID = iem_rt_interactions.RT_INTERACTION_ID;
3386:
3387: CURSOR del_rt_expire_csr IS

Line 3384: AND SYSDATE - iem_rt_interactions.LAST_UPDATE_DATE > 30

3380: CURSOR del_rt_csr IS
3381: SELECT iem_rt_interactions.RT_INTERACTION_ID
3382: FROM iem_rt_interactions, iem_rt_media_items
3383: WHERE iem_rt_interactions.expire='N' and iem_rt_interactions.type = 'O'
3384: AND SYSDATE - iem_rt_interactions.LAST_UPDATE_DATE > 30
3385: AND iem_rt_media_items.RT_INTERACTION_ID = iem_rt_interactions.RT_INTERACTION_ID;
3386:
3387: CURSOR del_rt_expire_csr IS
3388: SELECT RT_MEDIA_ITEM_ID FROM iem_rt_media_items WHERE expire= 'Y';

Line 3385: AND iem_rt_media_items.RT_INTERACTION_ID = iem_rt_interactions.RT_INTERACTION_ID;

3381: SELECT iem_rt_interactions.RT_INTERACTION_ID
3382: FROM iem_rt_interactions, iem_rt_media_items
3383: WHERE iem_rt_interactions.expire='N' and iem_rt_interactions.type = 'O'
3384: AND SYSDATE - iem_rt_interactions.LAST_UPDATE_DATE > 30
3385: AND iem_rt_media_items.RT_INTERACTION_ID = iem_rt_interactions.RT_INTERACTION_ID;
3386:
3387: CURSOR del_rt_expire_csr IS
3388: SELECT RT_MEDIA_ITEM_ID FROM iem_rt_media_items WHERE expire= 'Y';
3389:

Line 3464: from IEM_MC_CUSTOM_PARAMS, iem_rt_interactions

3460:
3461: -- Delete IEM_MC_PARAMETERS, and IEM_MC_CUSTOM_PARAM
3462: delete from IEM_MC_CUSTOM_PARAMS where MC_PARAMETER_ID in
3463: (select IEM_MC_CUSTOM_PARAMS.MC_PARAMETER_ID
3464: from IEM_MC_CUSTOM_PARAMS, iem_rt_interactions
3465: where IEM_MC_CUSTOM_PARAMS.MC_PARAMETER_ID = iem_rt_interactions.MC_PARAMETER_ID
3466: and iem_rt_interactions.expire = 'Y');
3467:
3468: delete from IEM_MC_PARAMETERS where MC_PARAMETER_ID in

Line 3465: where IEM_MC_CUSTOM_PARAMS.MC_PARAMETER_ID = iem_rt_interactions.MC_PARAMETER_ID

3461: -- Delete IEM_MC_PARAMETERS, and IEM_MC_CUSTOM_PARAM
3462: delete from IEM_MC_CUSTOM_PARAMS where MC_PARAMETER_ID in
3463: (select IEM_MC_CUSTOM_PARAMS.MC_PARAMETER_ID
3464: from IEM_MC_CUSTOM_PARAMS, iem_rt_interactions
3465: where IEM_MC_CUSTOM_PARAMS.MC_PARAMETER_ID = iem_rt_interactions.MC_PARAMETER_ID
3466: and iem_rt_interactions.expire = 'Y');
3467:
3468: delete from IEM_MC_PARAMETERS where MC_PARAMETER_ID in
3469: (select IEM_MC_PARAMETERS.MC_PARAMETER_ID

Line 3466: and iem_rt_interactions.expire = 'Y');

3462: delete from IEM_MC_CUSTOM_PARAMS where MC_PARAMETER_ID in
3463: (select IEM_MC_CUSTOM_PARAMS.MC_PARAMETER_ID
3464: from IEM_MC_CUSTOM_PARAMS, iem_rt_interactions
3465: where IEM_MC_CUSTOM_PARAMS.MC_PARAMETER_ID = iem_rt_interactions.MC_PARAMETER_ID
3466: and iem_rt_interactions.expire = 'Y');
3467:
3468: delete from IEM_MC_PARAMETERS where MC_PARAMETER_ID in
3469: (select IEM_MC_PARAMETERS.MC_PARAMETER_ID
3470: from IEM_MC_PARAMETERS, iem_rt_interactions

Line 3470: from IEM_MC_PARAMETERS, iem_rt_interactions

3466: and iem_rt_interactions.expire = 'Y');
3467:
3468: delete from IEM_MC_PARAMETERS where MC_PARAMETER_ID in
3469: (select IEM_MC_PARAMETERS.MC_PARAMETER_ID
3470: from IEM_MC_PARAMETERS, iem_rt_interactions
3471: where IEM_MC_PARAMETERS.MC_PARAMETER_ID = iem_rt_interactions.MC_PARAMETER_ID
3472: and iem_rt_interactions.expire = 'Y');
3473:
3474: -- Delete each record from iem_rt_interactions where expire = 'Y'

Line 3471: where IEM_MC_PARAMETERS.MC_PARAMETER_ID = iem_rt_interactions.MC_PARAMETER_ID

3467:
3468: delete from IEM_MC_PARAMETERS where MC_PARAMETER_ID in
3469: (select IEM_MC_PARAMETERS.MC_PARAMETER_ID
3470: from IEM_MC_PARAMETERS, iem_rt_interactions
3471: where IEM_MC_PARAMETERS.MC_PARAMETER_ID = iem_rt_interactions.MC_PARAMETER_ID
3472: and iem_rt_interactions.expire = 'Y');
3473:
3474: -- Delete each record from iem_rt_interactions where expire = 'Y'
3475: delete from iem_rt_interactions where expire = 'Y';

Line 3472: and iem_rt_interactions.expire = 'Y');

3468: delete from IEM_MC_PARAMETERS where MC_PARAMETER_ID in
3469: (select IEM_MC_PARAMETERS.MC_PARAMETER_ID
3470: from IEM_MC_PARAMETERS, iem_rt_interactions
3471: where IEM_MC_PARAMETERS.MC_PARAMETER_ID = iem_rt_interactions.MC_PARAMETER_ID
3472: and iem_rt_interactions.expire = 'Y');
3473:
3474: -- Delete each record from iem_rt_interactions where expire = 'Y'
3475: delete from iem_rt_interactions where expire = 'Y';
3476:

Line 3474: -- Delete each record from iem_rt_interactions where expire = 'Y'

3470: from IEM_MC_PARAMETERS, iem_rt_interactions
3471: where IEM_MC_PARAMETERS.MC_PARAMETER_ID = iem_rt_interactions.MC_PARAMETER_ID
3472: and iem_rt_interactions.expire = 'Y');
3473:
3474: -- Delete each record from iem_rt_interactions where expire = 'Y'
3475: delete from iem_rt_interactions where expire = 'Y';
3476:
3477: -- Delete each record from iem_msg_datas where its reference at iem_rt_media_items has expire ='Y'.
3478: delete from iem_msg_datas where msg_key in

Line 3475: delete from iem_rt_interactions where expire = 'Y';

3471: where IEM_MC_PARAMETERS.MC_PARAMETER_ID = iem_rt_interactions.MC_PARAMETER_ID
3472: and iem_rt_interactions.expire = 'Y');
3473:
3474: -- Delete each record from iem_rt_interactions where expire = 'Y'
3475: delete from iem_rt_interactions where expire = 'Y';
3476:
3477: -- Delete each record from iem_msg_datas where its reference at iem_rt_media_items has expire ='Y'.
3478: delete from iem_msg_datas where msg_key in
3479: (select msg_key from iem_rt_media_items, iem_msg_datas

Line 3494: from IEM_MC_CUSTOM_PARAMS, iem_rt_interactions

3490: -- Delete each record from IEM_MC_CUSTOM_PARAMS and IEM_MC_PARAMETERS where last_update_date is older than 30 days
3491:
3492: delete from IEM_MC_CUSTOM_PARAMS where MC_PARAMETER_ID in
3493: (select IEM_MC_CUSTOM_PARAMS.MC_PARAMETER_ID
3494: from IEM_MC_CUSTOM_PARAMS, iem_rt_interactions
3495: where IEM_MC_CUSTOM_PARAMS.MC_PARAMETER_ID = iem_rt_interactions.MC_PARAMETER_ID
3496: and iem_rt_interactions.expire='N' and iem_rt_interactions.type='O' and
3497: SYSDATE - iem_rt_interactions.LAST_UPDATE_DATE > 30);
3498:

Line 3495: where IEM_MC_CUSTOM_PARAMS.MC_PARAMETER_ID = iem_rt_interactions.MC_PARAMETER_ID

3491:
3492: delete from IEM_MC_CUSTOM_PARAMS where MC_PARAMETER_ID in
3493: (select IEM_MC_CUSTOM_PARAMS.MC_PARAMETER_ID
3494: from IEM_MC_CUSTOM_PARAMS, iem_rt_interactions
3495: where IEM_MC_CUSTOM_PARAMS.MC_PARAMETER_ID = iem_rt_interactions.MC_PARAMETER_ID
3496: and iem_rt_interactions.expire='N' and iem_rt_interactions.type='O' and
3497: SYSDATE - iem_rt_interactions.LAST_UPDATE_DATE > 30);
3498:
3499: delete from IEM_MC_PARAMETERS where MC_PARAMETER_ID in

Line 3496: and iem_rt_interactions.expire='N' and iem_rt_interactions.type='O' and

3492: delete from IEM_MC_CUSTOM_PARAMS where MC_PARAMETER_ID in
3493: (select IEM_MC_CUSTOM_PARAMS.MC_PARAMETER_ID
3494: from IEM_MC_CUSTOM_PARAMS, iem_rt_interactions
3495: where IEM_MC_CUSTOM_PARAMS.MC_PARAMETER_ID = iem_rt_interactions.MC_PARAMETER_ID
3496: and iem_rt_interactions.expire='N' and iem_rt_interactions.type='O' and
3497: SYSDATE - iem_rt_interactions.LAST_UPDATE_DATE > 30);
3498:
3499: delete from IEM_MC_PARAMETERS where MC_PARAMETER_ID in
3500: (select IEM_MC_PARAMETERS.MC_PARAMETER_ID

Line 3497: SYSDATE - iem_rt_interactions.LAST_UPDATE_DATE > 30);

3493: (select IEM_MC_CUSTOM_PARAMS.MC_PARAMETER_ID
3494: from IEM_MC_CUSTOM_PARAMS, iem_rt_interactions
3495: where IEM_MC_CUSTOM_PARAMS.MC_PARAMETER_ID = iem_rt_interactions.MC_PARAMETER_ID
3496: and iem_rt_interactions.expire='N' and iem_rt_interactions.type='O' and
3497: SYSDATE - iem_rt_interactions.LAST_UPDATE_DATE > 30);
3498:
3499: delete from IEM_MC_PARAMETERS where MC_PARAMETER_ID in
3500: (select IEM_MC_PARAMETERS.MC_PARAMETER_ID
3501: from IEM_MC_PARAMETERS, iem_rt_interactions

Line 3501: from IEM_MC_PARAMETERS, iem_rt_interactions

3497: SYSDATE - iem_rt_interactions.LAST_UPDATE_DATE > 30);
3498:
3499: delete from IEM_MC_PARAMETERS where MC_PARAMETER_ID in
3500: (select IEM_MC_PARAMETERS.MC_PARAMETER_ID
3501: from IEM_MC_PARAMETERS, iem_rt_interactions
3502: where IEM_MC_PARAMETERS.MC_PARAMETER_ID = iem_rt_interactions.MC_PARAMETER_ID
3503: and iem_rt_interactions.expire='N' and iem_rt_interactions.type='O' and
3504: SYSDATE - iem_rt_interactions.LAST_UPDATE_DATE > 30);
3505:

Line 3502: where IEM_MC_PARAMETERS.MC_PARAMETER_ID = iem_rt_interactions.MC_PARAMETER_ID

3498:
3499: delete from IEM_MC_PARAMETERS where MC_PARAMETER_ID in
3500: (select IEM_MC_PARAMETERS.MC_PARAMETER_ID
3501: from IEM_MC_PARAMETERS, iem_rt_interactions
3502: where IEM_MC_PARAMETERS.MC_PARAMETER_ID = iem_rt_interactions.MC_PARAMETER_ID
3503: and iem_rt_interactions.expire='N' and iem_rt_interactions.type='O' and
3504: SYSDATE - iem_rt_interactions.LAST_UPDATE_DATE > 30);
3505:
3506: FOR cr_rec in del_rt_csr LOOP

Line 3503: and iem_rt_interactions.expire='N' and iem_rt_interactions.type='O' and

3499: delete from IEM_MC_PARAMETERS where MC_PARAMETER_ID in
3500: (select IEM_MC_PARAMETERS.MC_PARAMETER_ID
3501: from IEM_MC_PARAMETERS, iem_rt_interactions
3502: where IEM_MC_PARAMETERS.MC_PARAMETER_ID = iem_rt_interactions.MC_PARAMETER_ID
3503: and iem_rt_interactions.expire='N' and iem_rt_interactions.type='O' and
3504: SYSDATE - iem_rt_interactions.LAST_UPDATE_DATE > 30);
3505:
3506: FOR cr_rec in del_rt_csr LOOP
3507: l_rt_interaction_id := cr_rec.RT_INTERACTION_ID;

Line 3504: SYSDATE - iem_rt_interactions.LAST_UPDATE_DATE > 30);

3500: (select IEM_MC_PARAMETERS.MC_PARAMETER_ID
3501: from IEM_MC_PARAMETERS, iem_rt_interactions
3502: where IEM_MC_PARAMETERS.MC_PARAMETER_ID = iem_rt_interactions.MC_PARAMETER_ID
3503: and iem_rt_interactions.expire='N' and iem_rt_interactions.type='O' and
3504: SYSDATE - iem_rt_interactions.LAST_UPDATE_DATE > 30);
3505:
3506: FOR cr_rec in del_rt_csr LOOP
3507: l_rt_interaction_id := cr_rec.RT_INTERACTION_ID;
3508:

Line 3565: delete from iem_rt_interactions where rt_interaction_id = l_rt_interaction_id;

3561: ref_key=l_RT_MEDIA_ITEM_ID and part_type='ATTACHMAIL');
3562:
3563: delete from iem_msg_parts where REF_KEY = l_RT_MEDIA_ITEM_ID;
3564: delete from iem_rt_media_items where RT_MEDIA_ITEM_ID = l_RT_MEDIA_ITEM_ID;
3565: delete from iem_rt_interactions where rt_interaction_id = l_rt_interaction_id;
3566:
3567: END LOOP;
3568:
3569: -- Clean anything left

Line 3571: delete from iem_rt_interactions WHERE expire='N' and type = 'O'

3567: END LOOP;
3568:
3569: -- Clean anything left
3570: delete from iem_msg_parts where DELETE_FLAG = 'Y';
3571: delete from iem_rt_interactions WHERE expire='N' and type = 'O'
3572: AND SYSDATE - LAST_UPDATE_DATE > 30;
3573:
3574:
3575: EXCEPTION

Line 3807: select IEM_RT_INTERACTIONS_S1.nextval into l_i_sequence from DUAL;

3803: if (l_email_data_rec.IH_INTERACTION_ID is not null) then
3804: l_ih_creator := 'Y';
3805: end if;
3806:
3807: select IEM_RT_INTERACTIONS_S1.nextval into l_i_sequence from DUAL;
3808: INSERT INTO iem_rt_interactions (
3809: rt_interaction_id, resource_id, customer_id, contact_id, type,
3810: status, expire, created_by, creation_date, last_updated_by,
3811: last_update_date, last_update_login, parent_interaction_id,

Line 3808: INSERT INTO iem_rt_interactions (

3804: l_ih_creator := 'Y';
3805: end if;
3806:
3807: select IEM_RT_INTERACTIONS_S1.nextval into l_i_sequence from DUAL;
3808: INSERT INTO iem_rt_interactions (
3809: rt_interaction_id, resource_id, customer_id, contact_id, type,
3810: status, expire, created_by, creation_date, last_updated_by,
3811: last_update_date, last_update_login, parent_interaction_id,
3812: service_request_id, inb_tag_id, interaction_id, ih_creator,

Line 4085: UPDATE iem_rt_interactions

4081: else
4082: l_action_id := null;
4083: end if;
4084:
4085: UPDATE iem_rt_interactions
4086: SET expire = G_QUEUEOUT,
4087: status = p_status,
4088: customer_id = decode(p_customer_id, G_NUM_NOP, customer_id, p_customer_id),
4089: action_id = decode(l_action_id, null, action_id, l_action_id),

Line 4210: l_rt_ih_data IEM_RT_INTERACTIONS%ROWTYPE;

4206: IEM_NO_DATA EXCEPTION;
4207:
4208: Type get_next is REF CURSOR;
4209: rt_cur get_next;
4210: l_rt_ih_data IEM_RT_INTERACTIONS%ROWTYPE;
4211: e_nowait EXCEPTION;
4212: PRAGMA EXCEPTION_INIT(e_nowait, -54);
4213:
4214: str VARCHAR2(500);

Line 4248: str := 'SELECT * FROM iem_rt_interactions

4244:
4245: -----------------------Code------------------------
4246: IF ( p_failed = 'T' ) THEN
4247: -- Get the item that is failed at last attempt to process
4248: str := 'SELECT * FROM iem_rt_interactions
4249: WHERE expire = :1 AND last_update_date < sysdate - 0.007 ORDER BY creation_date FOR UPDATE SKIP LOCKED';
4250: OPEN rt_cur FOR str USING G_PROCESSING;
4251: ELSE
4252: -- Get the item that need to be process.

Line 4253: str := 'SELECT * FROM iem_rt_interactions

4249: WHERE expire = :1 AND last_update_date < sysdate - 0.007 ORDER BY creation_date FOR UPDATE SKIP LOCKED';
4250: OPEN rt_cur FOR str USING G_PROCESSING;
4251: ELSE
4252: -- Get the item that need to be process.
4253: str := 'SELECT * FROM iem_rt_interactions
4254: WHERE expire = :1 ORDER BY creation_date FOR UPDATE SKIP LOCKED';
4255: OPEN rt_cur FOR str USING G_QUEUEOUT;
4256: END IF;
4257:

Line 4340: UPDATE iem_rt_interactions SET expire = G_PROCESSING, last_update_date = SYSDATE

4336: ELSE
4337:
4338: -- Mark the item to 'under processing'
4339: BEGIN
4340: UPDATE iem_rt_interactions SET expire = G_PROCESSING, last_update_date = SYSDATE
4341: WHERE rt_interaction_id = l_rt_ih_data.rt_interaction_id;
4342: commit;
4343: END;
4344:

Line 4619: select IEM_RT_INTERACTIONS_S1.nextval into l_i_sequence from DUAL;

4615: l_ih_creator := null;
4616: if ( p_interaction_id is not null ) then
4617: l_ih_creator := 'Y';
4618: end if;
4619: select IEM_RT_INTERACTIONS_S1.nextval into l_i_sequence from DUAL;
4620: INSERT INTO iem_rt_interactions (
4621: rt_interaction_id, resource_id, customer_id, contact_id, type,
4622: status, expire, created_by, creation_date, last_updated_by,
4623: last_update_date, last_update_login, parent_interaction_id,

Line 4620: INSERT INTO iem_rt_interactions (

4616: if ( p_interaction_id is not null ) then
4617: l_ih_creator := 'Y';
4618: end if;
4619: select IEM_RT_INTERACTIONS_S1.nextval into l_i_sequence from DUAL;
4620: INSERT INTO iem_rt_interactions (
4621: rt_interaction_id, resource_id, customer_id, contact_id, type,
4622: status, expire, created_by, creation_date, last_updated_by,
4623: last_update_date, last_update_login, parent_interaction_id,
4624: service_request_id, inb_tag_id, interaction_id, ih_creator,

Line 4786: in (select rt_interaction_id from iem_rt_interactions where expire <> G_EXPIRE);

4782:
4783: select count(rt_media_item_id) into l_count1 from iem_rt_media_items
4784: where agent_account_id=l_agent_account_id
4785: and rt_interaction_id
4786: in (select rt_interaction_id from iem_rt_interactions where expire <> G_EXPIRE);
4787:
4788: select count(rt_media_item_id) into l_count2 from iem_rt_media_items
4789: where resource_id= p_resource_id and email_account_id=p_email_account_id
4790: and rt_interaction_id

Line 4791: in (select rt_interaction_id from iem_rt_interactions where expire <> G_EXPIRE

4787:
4788: select count(rt_media_item_id) into l_count2 from iem_rt_media_items
4789: where resource_id= p_resource_id and email_account_id=p_email_account_id
4790: and rt_interaction_id
4791: in (select rt_interaction_id from iem_rt_interactions where expire <> G_EXPIRE
4792: and type = 'I');
4793:
4794: if ( (l_count1 + l_count2) = 0 ) then
4795: x_is_clean := true;