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 2117: update iem_rt_interactions set expire = G_EXPIRE

2113:
2114: UPDATE iem_outbox_errors SET expire = G_EXPIRE
2115: WHERE rt_media_item_id = p_rt_media_item_id;
2116:
2117: update iem_rt_interactions set expire = G_EXPIRE
2118: where rt_interaction_id = l_rt_interaction_id;
2119:
2120: update iem_rt_media_items set expire = G_EXPIRE
2121: where rt_media_item_id = p_rt_media_item_id;

Line 2137: FROM iem_rt_interactions

2133: relationship_id, status, mc_parameter_id
2134: INTO l_interaction_id, l_ih_creator, l_sr_id, l_sr_action, l_parent_ih_id,
2135: l_lead_id, l_action_id, l_contact_id, l_to_resource_id, l_relationship_id,
2136: l_rt_ih_status, l_mc_parameter_id
2137: FROM iem_rt_interactions
2138: WHERE rt_interaction_id = l_rt_interaction_id
2139: AND expire in (G_ACTIVE, G_QUEUEOUT, G_PROCESSING);
2140: EXCEPTION
2141: WHEN OTHERS THEN

Line 2211: FROM iem_rt_interactions

2207: end if;
2208:
2209: SELECT customer_id, resource_id, creation_date, nvl(spv_resource_id, -1)
2210: INTO l_customer_id, l_resource_id, l_start_date, l_spv_resource_id
2211: FROM iem_rt_interactions
2212: WHERE rt_interaction_id = l_rt_interaction_id;
2213:
2214: -- use supervisor resource id if exists
2215: l_reroute_resource_id := l_resource_id;

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

2331: x_interaction_id => l_interaction_id,
2332: p_interaction_rec => l_interaction_rec
2333: );
2334:
2335: UPDATE iem_rt_interactions set IH_CREATOR = 'Y', interaction_id = l_interaction_id
2336: WHERE rt_interaction_id = l_rt_interaction_id;
2337:
2338: if(l_status <> FND_API.G_RET_STS_SUCCESS) then
2339: raise IHError;

Line 2703: UPDATE iem_rt_interactions SET expire = G_EXPIRE

2699: -- update RTI: send, delete, reroute
2700: IF ((UPPER(p_action) = 'S') OR (UPPER(p_action) = 'D') OR (UPPER(p_action)='V') -- Add resolve
2701: OR (UPPER(p_action) = 'X')) THEN
2702: begin
2703: UPDATE iem_rt_interactions SET expire = G_EXPIRE
2704: WHERE rt_interaction_id = l_rt_interaction_id;
2705:
2706: UPDATE iem_rt_media_items SET expire = G_EXPIRE
2707: WHERE rt_interaction_id = l_rt_interaction_id;

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

2742: select agent_id into l_tran_to_acct_id from iem_agents
2743: where resource_id = p_to_resource_id
2744: and email_account_id = l_email_account_id;
2745:
2746: select IEM_RT_INTERACTIONS_S1.nextval into l_i_sequence from DUAL;
2747: INSERT INTO iem_rt_interactions (
2748: rt_interaction_id, resource_id, customer_id, type,
2749: status, expire, created_by, creation_date, last_updated_by,
2750: last_update_date, last_update_login, contact_id, inb_tag_id,

Line 2747: INSERT INTO iem_rt_interactions (

2743: where resource_id = p_to_resource_id
2744: and email_account_id = l_email_account_id;
2745:
2746: select IEM_RT_INTERACTIONS_S1.nextval into l_i_sequence from DUAL;
2747: INSERT INTO iem_rt_interactions (
2748: rt_interaction_id, resource_id, customer_id, type,
2749: status, expire, created_by, creation_date, last_updated_by,
2750: last_update_date, last_update_login, contact_id, inb_tag_id,
2751: lead_id, parent_interaction_id, service_request_id,

Line 2759: FROM iem_rt_interactions

2755: l_last_updated_by, SYSDATE, l_last_update_login,
2756: l_contact_id, inb_tag_id, l_tran_lead_id,
2757: l_interaction_id, l_sr_id,
2758: l_relationship_id
2759: FROM iem_rt_interactions
2760: WHERE rt_interaction_id = l_rt_interaction_id;
2761:
2762: select IEM_RT_MEDIA_ITEMS_S1.nextval into l_m_sequence from DUAL;
2763: INSERT INTO iem_rt_media_items (

Line 2801: UPDATE iem_rt_interactions SET expire = G_EXPIRE

2797: end if;
2798: end if; -- MLCS second agent
2799:
2800: -- Transferrers RT Interaction and RT media are expired
2801: UPDATE iem_rt_interactions SET expire = G_EXPIRE
2802: WHERE rt_interaction_id = l_rt_interaction_id;
2803:
2804: -- This expires both the inbound and any outbounds
2805: -- associated with the original message. Change

Line 3044: FROM iem_rt_media_items m, iem_rt_interactions i

3040: SELECT m.rfc822_message_id,m.email_account_id, m.agent_account_id,
3041: m.email_type, m.status, m.version, m.rt_interaction_id,
3042: m.db_server_id, m.rt_media_item_id, m.folder_name, m.folder_uid,
3043: i.customer_id
3044: FROM iem_rt_media_items m, iem_rt_interactions i
3045: WHERE i.TYPE = G_OUTBOUND
3046: AND i.RT_INTERACTION_ID = m.RT_INTERACTION_ID
3047: AND m.RESOURCE_ID = p_resource_id
3048: AND m.EMAIL_TYPE = G_OUTBOUND

Line 3319: SELECT iem_rt_interactions.RT_INTERACTION_ID

3315: i NUMBER;
3316: l_message_id NUMBER;
3317:
3318: CURSOR del_rt_csr IS
3319: SELECT iem_rt_interactions.RT_INTERACTION_ID
3320: FROM iem_rt_interactions, iem_rt_media_items
3321: WHERE iem_rt_interactions.expire='N' and iem_rt_interactions.type = 'O'
3322: AND SYSDATE - iem_rt_interactions.LAST_UPDATE_DATE > 30
3323: AND iem_rt_media_items.RT_INTERACTION_ID = iem_rt_interactions.RT_INTERACTION_ID;

Line 3320: FROM iem_rt_interactions, iem_rt_media_items

3316: l_message_id NUMBER;
3317:
3318: CURSOR del_rt_csr IS
3319: SELECT iem_rt_interactions.RT_INTERACTION_ID
3320: FROM iem_rt_interactions, iem_rt_media_items
3321: WHERE iem_rt_interactions.expire='N' and iem_rt_interactions.type = 'O'
3322: AND SYSDATE - iem_rt_interactions.LAST_UPDATE_DATE > 30
3323: AND iem_rt_media_items.RT_INTERACTION_ID = iem_rt_interactions.RT_INTERACTION_ID;
3324:

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

3317:
3318: CURSOR del_rt_csr IS
3319: SELECT iem_rt_interactions.RT_INTERACTION_ID
3320: FROM iem_rt_interactions, iem_rt_media_items
3321: WHERE iem_rt_interactions.expire='N' and iem_rt_interactions.type = 'O'
3322: AND SYSDATE - iem_rt_interactions.LAST_UPDATE_DATE > 30
3323: AND iem_rt_media_items.RT_INTERACTION_ID = iem_rt_interactions.RT_INTERACTION_ID;
3324:
3325: CURSOR del_rt_expire_csr IS

Line 3322: AND SYSDATE - iem_rt_interactions.LAST_UPDATE_DATE > 30

3318: CURSOR del_rt_csr IS
3319: SELECT iem_rt_interactions.RT_INTERACTION_ID
3320: FROM iem_rt_interactions, iem_rt_media_items
3321: WHERE iem_rt_interactions.expire='N' and iem_rt_interactions.type = 'O'
3322: AND SYSDATE - iem_rt_interactions.LAST_UPDATE_DATE > 30
3323: AND iem_rt_media_items.RT_INTERACTION_ID = iem_rt_interactions.RT_INTERACTION_ID;
3324:
3325: CURSOR del_rt_expire_csr IS
3326: SELECT RT_MEDIA_ITEM_ID FROM iem_rt_media_items WHERE expire= 'Y';

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

3319: SELECT iem_rt_interactions.RT_INTERACTION_ID
3320: FROM iem_rt_interactions, iem_rt_media_items
3321: WHERE iem_rt_interactions.expire='N' and iem_rt_interactions.type = 'O'
3322: AND SYSDATE - iem_rt_interactions.LAST_UPDATE_DATE > 30
3323: AND iem_rt_media_items.RT_INTERACTION_ID = iem_rt_interactions.RT_INTERACTION_ID;
3324:
3325: CURSOR del_rt_expire_csr IS
3326: SELECT RT_MEDIA_ITEM_ID FROM iem_rt_media_items WHERE expire= 'Y';
3327:

Line 3402: from IEM_MC_CUSTOM_PARAMS, iem_rt_interactions

3398:
3399: -- Delete IEM_MC_PARAMETERS, and IEM_MC_CUSTOM_PARAM
3400: delete from IEM_MC_CUSTOM_PARAMS where MC_PARAMETER_ID in
3401: (select IEM_MC_CUSTOM_PARAMS.MC_PARAMETER_ID
3402: from IEM_MC_CUSTOM_PARAMS, iem_rt_interactions
3403: where IEM_MC_CUSTOM_PARAMS.MC_PARAMETER_ID = iem_rt_interactions.MC_PARAMETER_ID
3404: and iem_rt_interactions.expire = 'Y');
3405:
3406: delete from IEM_MC_PARAMETERS where MC_PARAMETER_ID in

Line 3403: where IEM_MC_CUSTOM_PARAMS.MC_PARAMETER_ID = iem_rt_interactions.MC_PARAMETER_ID

3399: -- Delete IEM_MC_PARAMETERS, and IEM_MC_CUSTOM_PARAM
3400: delete from IEM_MC_CUSTOM_PARAMS where MC_PARAMETER_ID in
3401: (select IEM_MC_CUSTOM_PARAMS.MC_PARAMETER_ID
3402: from IEM_MC_CUSTOM_PARAMS, iem_rt_interactions
3403: where IEM_MC_CUSTOM_PARAMS.MC_PARAMETER_ID = iem_rt_interactions.MC_PARAMETER_ID
3404: and iem_rt_interactions.expire = 'Y');
3405:
3406: delete from IEM_MC_PARAMETERS where MC_PARAMETER_ID in
3407: (select IEM_MC_PARAMETERS.MC_PARAMETER_ID

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

3400: delete from IEM_MC_CUSTOM_PARAMS where MC_PARAMETER_ID in
3401: (select IEM_MC_CUSTOM_PARAMS.MC_PARAMETER_ID
3402: from IEM_MC_CUSTOM_PARAMS, iem_rt_interactions
3403: where IEM_MC_CUSTOM_PARAMS.MC_PARAMETER_ID = iem_rt_interactions.MC_PARAMETER_ID
3404: and iem_rt_interactions.expire = 'Y');
3405:
3406: delete from IEM_MC_PARAMETERS where MC_PARAMETER_ID in
3407: (select IEM_MC_PARAMETERS.MC_PARAMETER_ID
3408: from IEM_MC_PARAMETERS, iem_rt_interactions

Line 3408: from IEM_MC_PARAMETERS, iem_rt_interactions

3404: and iem_rt_interactions.expire = 'Y');
3405:
3406: delete from IEM_MC_PARAMETERS where MC_PARAMETER_ID in
3407: (select IEM_MC_PARAMETERS.MC_PARAMETER_ID
3408: from IEM_MC_PARAMETERS, iem_rt_interactions
3409: where IEM_MC_PARAMETERS.MC_PARAMETER_ID = iem_rt_interactions.MC_PARAMETER_ID
3410: and iem_rt_interactions.expire = 'Y');
3411:
3412: -- Delete each record from iem_rt_interactions where expire = 'Y'

Line 3409: where IEM_MC_PARAMETERS.MC_PARAMETER_ID = iem_rt_interactions.MC_PARAMETER_ID

3405:
3406: delete from IEM_MC_PARAMETERS where MC_PARAMETER_ID in
3407: (select IEM_MC_PARAMETERS.MC_PARAMETER_ID
3408: from IEM_MC_PARAMETERS, iem_rt_interactions
3409: where IEM_MC_PARAMETERS.MC_PARAMETER_ID = iem_rt_interactions.MC_PARAMETER_ID
3410: and iem_rt_interactions.expire = 'Y');
3411:
3412: -- Delete each record from iem_rt_interactions where expire = 'Y'
3413: delete from iem_rt_interactions where expire = 'Y';

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

3406: delete from IEM_MC_PARAMETERS where MC_PARAMETER_ID in
3407: (select IEM_MC_PARAMETERS.MC_PARAMETER_ID
3408: from IEM_MC_PARAMETERS, iem_rt_interactions
3409: where IEM_MC_PARAMETERS.MC_PARAMETER_ID = iem_rt_interactions.MC_PARAMETER_ID
3410: and iem_rt_interactions.expire = 'Y');
3411:
3412: -- Delete each record from iem_rt_interactions where expire = 'Y'
3413: delete from iem_rt_interactions where expire = 'Y';
3414:

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

3408: from IEM_MC_PARAMETERS, iem_rt_interactions
3409: where IEM_MC_PARAMETERS.MC_PARAMETER_ID = iem_rt_interactions.MC_PARAMETER_ID
3410: and iem_rt_interactions.expire = 'Y');
3411:
3412: -- Delete each record from iem_rt_interactions where expire = 'Y'
3413: delete from iem_rt_interactions where expire = 'Y';
3414:
3415: -- Delete each record from iem_msg_datas where its reference at iem_rt_media_items has expire ='Y'.
3416: delete from iem_msg_datas where msg_key in

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

3409: where IEM_MC_PARAMETERS.MC_PARAMETER_ID = iem_rt_interactions.MC_PARAMETER_ID
3410: and iem_rt_interactions.expire = 'Y');
3411:
3412: -- Delete each record from iem_rt_interactions where expire = 'Y'
3413: delete from iem_rt_interactions where expire = 'Y';
3414:
3415: -- Delete each record from iem_msg_datas where its reference at iem_rt_media_items has expire ='Y'.
3416: delete from iem_msg_datas where msg_key in
3417: (select msg_key from iem_rt_media_items, iem_msg_datas

Line 3432: from IEM_MC_CUSTOM_PARAMS, iem_rt_interactions

3428: -- Delete each record from IEM_MC_CUSTOM_PARAMS and IEM_MC_PARAMETERS where last_update_date is older than 30 days
3429:
3430: delete from IEM_MC_CUSTOM_PARAMS where MC_PARAMETER_ID in
3431: (select IEM_MC_CUSTOM_PARAMS.MC_PARAMETER_ID
3432: from IEM_MC_CUSTOM_PARAMS, iem_rt_interactions
3433: where IEM_MC_CUSTOM_PARAMS.MC_PARAMETER_ID = iem_rt_interactions.MC_PARAMETER_ID
3434: and iem_rt_interactions.expire='N' and iem_rt_interactions.type='O' and
3435: SYSDATE - iem_rt_interactions.LAST_UPDATE_DATE > 30);
3436:

Line 3433: where IEM_MC_CUSTOM_PARAMS.MC_PARAMETER_ID = iem_rt_interactions.MC_PARAMETER_ID

3429:
3430: delete from IEM_MC_CUSTOM_PARAMS where MC_PARAMETER_ID in
3431: (select IEM_MC_CUSTOM_PARAMS.MC_PARAMETER_ID
3432: from IEM_MC_CUSTOM_PARAMS, iem_rt_interactions
3433: where IEM_MC_CUSTOM_PARAMS.MC_PARAMETER_ID = iem_rt_interactions.MC_PARAMETER_ID
3434: and iem_rt_interactions.expire='N' and iem_rt_interactions.type='O' and
3435: SYSDATE - iem_rt_interactions.LAST_UPDATE_DATE > 30);
3436:
3437: delete from IEM_MC_PARAMETERS where MC_PARAMETER_ID in

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

3430: delete from IEM_MC_CUSTOM_PARAMS where MC_PARAMETER_ID in
3431: (select IEM_MC_CUSTOM_PARAMS.MC_PARAMETER_ID
3432: from IEM_MC_CUSTOM_PARAMS, iem_rt_interactions
3433: where IEM_MC_CUSTOM_PARAMS.MC_PARAMETER_ID = iem_rt_interactions.MC_PARAMETER_ID
3434: and iem_rt_interactions.expire='N' and iem_rt_interactions.type='O' and
3435: SYSDATE - iem_rt_interactions.LAST_UPDATE_DATE > 30);
3436:
3437: delete from IEM_MC_PARAMETERS where MC_PARAMETER_ID in
3438: (select IEM_MC_PARAMETERS.MC_PARAMETER_ID

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

3431: (select IEM_MC_CUSTOM_PARAMS.MC_PARAMETER_ID
3432: from IEM_MC_CUSTOM_PARAMS, iem_rt_interactions
3433: where IEM_MC_CUSTOM_PARAMS.MC_PARAMETER_ID = iem_rt_interactions.MC_PARAMETER_ID
3434: and iem_rt_interactions.expire='N' and iem_rt_interactions.type='O' and
3435: SYSDATE - iem_rt_interactions.LAST_UPDATE_DATE > 30);
3436:
3437: delete from IEM_MC_PARAMETERS where MC_PARAMETER_ID in
3438: (select IEM_MC_PARAMETERS.MC_PARAMETER_ID
3439: from IEM_MC_PARAMETERS, iem_rt_interactions

Line 3439: from IEM_MC_PARAMETERS, iem_rt_interactions

3435: SYSDATE - iem_rt_interactions.LAST_UPDATE_DATE > 30);
3436:
3437: delete from IEM_MC_PARAMETERS where MC_PARAMETER_ID in
3438: (select IEM_MC_PARAMETERS.MC_PARAMETER_ID
3439: from IEM_MC_PARAMETERS, iem_rt_interactions
3440: where IEM_MC_PARAMETERS.MC_PARAMETER_ID = iem_rt_interactions.MC_PARAMETER_ID
3441: and iem_rt_interactions.expire='N' and iem_rt_interactions.type='O' and
3442: SYSDATE - iem_rt_interactions.LAST_UPDATE_DATE > 30);
3443:

Line 3440: where IEM_MC_PARAMETERS.MC_PARAMETER_ID = iem_rt_interactions.MC_PARAMETER_ID

3436:
3437: delete from IEM_MC_PARAMETERS where MC_PARAMETER_ID in
3438: (select IEM_MC_PARAMETERS.MC_PARAMETER_ID
3439: from IEM_MC_PARAMETERS, iem_rt_interactions
3440: where IEM_MC_PARAMETERS.MC_PARAMETER_ID = iem_rt_interactions.MC_PARAMETER_ID
3441: and iem_rt_interactions.expire='N' and iem_rt_interactions.type='O' and
3442: SYSDATE - iem_rt_interactions.LAST_UPDATE_DATE > 30);
3443:
3444: FOR cr_rec in del_rt_csr LOOP

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

3437: delete from IEM_MC_PARAMETERS where MC_PARAMETER_ID in
3438: (select IEM_MC_PARAMETERS.MC_PARAMETER_ID
3439: from IEM_MC_PARAMETERS, iem_rt_interactions
3440: where IEM_MC_PARAMETERS.MC_PARAMETER_ID = iem_rt_interactions.MC_PARAMETER_ID
3441: and iem_rt_interactions.expire='N' and iem_rt_interactions.type='O' and
3442: SYSDATE - iem_rt_interactions.LAST_UPDATE_DATE > 30);
3443:
3444: FOR cr_rec in del_rt_csr LOOP
3445: l_rt_interaction_id := cr_rec.RT_INTERACTION_ID;

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

3438: (select IEM_MC_PARAMETERS.MC_PARAMETER_ID
3439: from IEM_MC_PARAMETERS, iem_rt_interactions
3440: where IEM_MC_PARAMETERS.MC_PARAMETER_ID = iem_rt_interactions.MC_PARAMETER_ID
3441: and iem_rt_interactions.expire='N' and iem_rt_interactions.type='O' and
3442: SYSDATE - iem_rt_interactions.LAST_UPDATE_DATE > 30);
3443:
3444: FOR cr_rec in del_rt_csr LOOP
3445: l_rt_interaction_id := cr_rec.RT_INTERACTION_ID;
3446:

Line 3503: delete from iem_rt_interactions where rt_interaction_id = l_rt_interaction_id;

3499: ref_key=l_RT_MEDIA_ITEM_ID and part_type='ATTACHMAIL');
3500:
3501: delete from iem_msg_parts where REF_KEY = l_RT_MEDIA_ITEM_ID;
3502: delete from iem_rt_media_items where RT_MEDIA_ITEM_ID = l_RT_MEDIA_ITEM_ID;
3503: delete from iem_rt_interactions where rt_interaction_id = l_rt_interaction_id;
3504:
3505: END LOOP;
3506:
3507: -- Clean anything left

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

3505: END LOOP;
3506:
3507: -- Clean anything left
3508: delete from iem_msg_parts where DELETE_FLAG = 'Y';
3509: delete from iem_rt_interactions WHERE expire='N' and type = 'O'
3510: AND SYSDATE - LAST_UPDATE_DATE > 30;
3511:
3512:
3513: EXCEPTION

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

3728: if (l_email_data_rec.IH_INTERACTION_ID is not null) then
3729: l_ih_creator := 'Y';
3730: end if;
3731:
3732: select IEM_RT_INTERACTIONS_S1.nextval into l_i_sequence from DUAL;
3733: INSERT INTO iem_rt_interactions (
3734: rt_interaction_id, resource_id, customer_id, contact_id, type,
3735: status, expire, created_by, creation_date, last_updated_by,
3736: last_update_date, last_update_login, parent_interaction_id,

Line 3733: INSERT INTO iem_rt_interactions (

3729: l_ih_creator := 'Y';
3730: end if;
3731:
3732: select IEM_RT_INTERACTIONS_S1.nextval into l_i_sequence from DUAL;
3733: INSERT INTO iem_rt_interactions (
3734: rt_interaction_id, resource_id, customer_id, contact_id, type,
3735: status, expire, created_by, creation_date, last_updated_by,
3736: last_update_date, last_update_login, parent_interaction_id,
3737: service_request_id, inb_tag_id, interaction_id, ih_creator,

Line 4010: UPDATE iem_rt_interactions

4006: else
4007: l_action_id := null;
4008: end if;
4009:
4010: UPDATE iem_rt_interactions
4011: SET expire = G_QUEUEOUT,
4012: status = p_status,
4013: customer_id = decode(p_customer_id, G_NUM_NOP, customer_id, p_customer_id),
4014: action_id = decode(l_action_id, null, action_id, l_action_id),

Line 4135: l_rt_ih_data IEM_RT_INTERACTIONS%ROWTYPE;

4131: IEM_NO_DATA EXCEPTION;
4132:
4133: Type get_next is REF CURSOR;
4134: rt_cur get_next;
4135: l_rt_ih_data IEM_RT_INTERACTIONS%ROWTYPE;
4136: e_nowait EXCEPTION;
4137: PRAGMA EXCEPTION_INIT(e_nowait, -54);
4138:
4139: str VARCHAR2(500);

Line 4173: str := 'SELECT * FROM iem_rt_interactions

4169:
4170: -----------------------Code------------------------
4171: IF ( p_failed = 'T' ) THEN
4172: -- Get the item that is failed at last attempt to process
4173: str := 'SELECT * FROM iem_rt_interactions
4174: WHERE expire = :1 AND last_update_date < sysdate - 0.007 ORDER BY creation_date FOR UPDATE SKIP LOCKED';
4175: OPEN rt_cur FOR str USING G_PROCESSING;
4176: ELSE
4177: -- Get the item that need to be process.

Line 4178: str := 'SELECT * FROM iem_rt_interactions

4174: WHERE expire = :1 AND last_update_date < sysdate - 0.007 ORDER BY creation_date FOR UPDATE SKIP LOCKED';
4175: OPEN rt_cur FOR str USING G_PROCESSING;
4176: ELSE
4177: -- Get the item that need to be process.
4178: str := 'SELECT * FROM iem_rt_interactions
4179: WHERE expire = :1 ORDER BY creation_date FOR UPDATE SKIP LOCKED';
4180: OPEN rt_cur FOR str USING G_QUEUEOUT;
4181: END IF;
4182:

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

4261: ELSE
4262:
4263: -- Mark the item to 'under processing'
4264: BEGIN
4265: UPDATE iem_rt_interactions SET expire = G_PROCESSING, last_update_date = SYSDATE
4266: WHERE rt_interaction_id = l_rt_ih_data.rt_interaction_id;
4267: commit;
4268: END;
4269:

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

4540: l_ih_creator := null;
4541: if ( p_interaction_id is not null ) then
4542: l_ih_creator := 'Y';
4543: end if;
4544: select IEM_RT_INTERACTIONS_S1.nextval into l_i_sequence from DUAL;
4545: INSERT INTO iem_rt_interactions (
4546: rt_interaction_id, resource_id, customer_id, contact_id, type,
4547: status, expire, created_by, creation_date, last_updated_by,
4548: last_update_date, last_update_login, parent_interaction_id,

Line 4545: INSERT INTO iem_rt_interactions (

4541: if ( p_interaction_id is not null ) then
4542: l_ih_creator := 'Y';
4543: end if;
4544: select IEM_RT_INTERACTIONS_S1.nextval into l_i_sequence from DUAL;
4545: INSERT INTO iem_rt_interactions (
4546: rt_interaction_id, resource_id, customer_id, contact_id, type,
4547: status, expire, created_by, creation_date, last_updated_by,
4548: last_update_date, last_update_login, parent_interaction_id,
4549: service_request_id, inb_tag_id, interaction_id, ih_creator,

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

4707:
4708: select count(rt_media_item_id) into l_count1 from iem_rt_media_items
4709: where agent_account_id=l_agent_account_id
4710: and rt_interaction_id
4711: in (select rt_interaction_id from iem_rt_interactions where expire <> G_EXPIRE);
4712:
4713: select count(rt_media_item_id) into l_count2 from iem_rt_media_items
4714: where resource_id= p_resource_id and email_account_id=p_email_account_id
4715: and rt_interaction_id

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

4712:
4713: select count(rt_media_item_id) into l_count2 from iem_rt_media_items
4714: where resource_id= p_resource_id and email_account_id=p_email_account_id
4715: and rt_interaction_id
4716: in (select rt_interaction_id from iem_rt_interactions where expire <> G_EXPIRE
4717: and type = 'I');
4718:
4719: if ( (l_count1 + l_count2) = 0 ) then
4720: x_is_clean := true;