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_MEDIA_ITEMS

Line 217: select IEM_RT_MEDIA_ITEMS_S1.nextval into l_m_sequence from DUAL;

213: );
214: -- db_server id used by mid-tier to locate accounts
215: l_db_server_id := -1;
216:
217: select IEM_RT_MEDIA_ITEMS_S1.nextval into l_m_sequence from DUAL;
218: INSERT INTO iem_rt_media_items (
219: rt_interaction_id, rt_media_item_id, resource_id,
220: media_id, message_id, rfc822_message_id, folder_name,
221: folder_uid, email_account_id, db_server_id, email_type,

Line 218: INSERT INTO iem_rt_media_items (

214: -- db_server id used by mid-tier to locate accounts
215: l_db_server_id := -1;
216:
217: select IEM_RT_MEDIA_ITEMS_S1.nextval into l_m_sequence from DUAL;
218: INSERT INTO iem_rt_media_items (
219: rt_interaction_id, rt_media_item_id, resource_id,
220: media_id, message_id, rfc822_message_id, folder_name,
221: folder_uid, email_account_id, db_server_id, email_type,
222: status, expire, version, created_by, creation_date,

Line 428: FROM iem_rt_media_items

424: INTO l_rt_interaction_id, l_rt_media_item_id, l_rfc822_message_id,
425: l_folder_name, l_message_id, l_email_account_id, l_agent_account_id,
426: l_oes_id, l_email_type, l_status, l_version, l_expire, l_edit_mode,
427: l_ih_media_id
428: FROM iem_rt_media_items
429: WHERE rt_media_item_id = p_rt_media_item_id;
430:
431: -- Collect the data thats needed at a later stage.
432: l_inpEmail_type := l_email_type;

Line 461: FROM iem_rt_media_items

457: db_server_id, email_type, status, version, edit_mode, media_id
458: INTO x_rt_interaction_id, x_rt_media_item_id, x_rfc822_message_id,
459: x_folder_name, x_message_id, l_email_account_id1, l_agent_account_id1,
460: x_oes_id, x_email_type, x_status, x_version, x_edit_mode, x_ih_media_id
461: FROM iem_rt_media_items
462: WHERE rt_interaction_id = l_rt_interaction_id
463: AND email_type = p_email_type
464: AND expire in (G_ACTIVE, G_QUEUEOUT);
465: EXCEPTION

Line 671: FROM iem_rt_media_items

667: db_server_id, email_type, status, version, expire
668: INTO x_rt_interaction_id, x_rt_media_item_id, x_rfc822_message_id,
669: x_folder_name, x_mdt_message_id, l_email_account_id, l_agent_account_id,
670: x_oes_id, x_email_type, x_status, x_version, l_expire
671: FROM iem_rt_media_items
672: WHERE agent_account_id = p_agentAccount_id
673: AND message_id = p_message_id
674: AND email_type = p_email_type
675: AND expire IN (G_ACTIVE, G_DORMANT, G_QUEUEOUT);

Line 1126: select IEM_RT_MEDIA_ITEMS_S1.nextval into l_m_sequence from DUAL;

1122: decode(p_action_id, G_NUM_NOP, null, p_action_id),
1123: decode(p_relationship_id, G_NUM_NOP, null, p_relationship_id)
1124: );
1125:
1126: select IEM_RT_MEDIA_ITEMS_S1.nextval into l_m_sequence from DUAL;
1127: INSERT INTO iem_rt_media_items (
1128: rt_interaction_id, rt_media_item_id, resource_id,
1129: media_id, message_id, rfc822_message_id, folder_name,
1130: folder_uid, email_account_id,agent_account_id,

Line 1127: INSERT INTO iem_rt_media_items (

1123: decode(p_relationship_id, G_NUM_NOP, null, p_relationship_id)
1124: );
1125:
1126: select IEM_RT_MEDIA_ITEMS_S1.nextval into l_m_sequence from DUAL;
1127: INSERT INTO iem_rt_media_items (
1128: rt_interaction_id, rt_media_item_id, resource_id,
1129: media_id, message_id, rfc822_message_id, folder_name,
1130: folder_uid, email_account_id,agent_account_id,
1131: db_server_id, email_type, status, expire,

Line 1152: FROM iem_rt_media_items

1148:
1149: -- reply to an inbound?, checking..
1150: SELECT rt_interaction_id, email_type, media_id
1151: INTO l_rt_interaction_id, l_email_type, l_ib_media_id
1152: FROM iem_rt_media_items
1153: WHERE rt_media_item_id = p_rt_media_item_id
1154: AND expire = G_ACTIVE
1155: FOR UPDATE NOWAIT;
1156:

Line 1160: select IEM_RT_MEDIA_ITEMS_S1.nextval into l_m_sequence from DUAL;

1156:
1157: IF (UPPER(l_email_type) = G_INBOUND) THEN
1158:
1159: -- Draft/Reply to an existing Inbound.
1160: select IEM_RT_MEDIA_ITEMS_S1.nextval into l_m_sequence from DUAL;
1161: INSERT INTO iem_rt_media_items (
1162: rt_interaction_id, rt_media_item_id, resource_id,
1163: media_id, message_id, rfc822_message_id, folder_name,
1164: folder_uid, email_account_id,agent_account_id,

Line 1161: INSERT INTO iem_rt_media_items (

1157: IF (UPPER(l_email_type) = G_INBOUND) THEN
1158:
1159: -- Draft/Reply to an existing Inbound.
1160: select IEM_RT_MEDIA_ITEMS_S1.nextval into l_m_sequence from DUAL;
1161: INSERT INTO iem_rt_media_items (
1162: rt_interaction_id, rt_media_item_id, resource_id,
1163: media_id, message_id, rfc822_message_id, folder_name,
1164: folder_uid, email_account_id,agent_account_id,
1165: db_server_id, email_type, status, expire,

Line 1368: FROM iem_rt_media_items

1364: SELECT rt_interaction_id, version, email_type, email_account_id,
1365: agent_account_id, media_id, resource_id, status, message_id
1366: INTO l_rt_interaction_id, l_version, l_email_type, l_email_account_id_old,
1367: l_agent_account_id_old, l_media_id, l_resource_id, l_rt_status, l_msg_id
1368: FROM iem_rt_media_items
1369: WHERE rt_media_item_id = p_rt_media_item_id
1370: AND expire in (G_ACTIVE, G_DORMANT, G_QUEUEOUT)
1371: FOR update nowait;
1372:

Line 1395: FROM iem_rt_media_items

1391:
1392: if ( l_email_type = G_OUTBOUND ) then
1393: SELECT message_id
1394: INTO l_msg_id
1395: FROM iem_rt_media_items
1396: WHERE rt_interaction_id = l_rt_interaction_id
1397: AND email_type = G_INBOUND;
1398: end if;
1399:

Line 1487: UPDATE iem_rt_media_items SET

1483: else
1484: l_edit_mode := p_edit_mode;
1485: end if;
1486:
1487: UPDATE iem_rt_media_items SET
1488: RFC822_MESSAGE_ID = decode(p_rfc822_message_id, G_CHAR_NOP, RFC822_MESSAGE_ID, p_rfc822_message_id),
1489: FOLDER_NAME = decode(p_folder_name, G_CHAR_NOP, FOLDER_NAME, p_folder_name),
1490: FOLDER_UID = decode(p_folder_uid, G_NUM_NOP, FOLDER_UID, p_folder_uid),
1491: EMAIL_ACCOUNT_ID = decode(l_email_account_id, G_NUM_NOP, EMAIL_ACCOUNT_ID, l_email_account_id),

Line 1572: from iem_rt_media_items

1568:
1569: -- write statistics data
1570: begin
1571: select media_id into l_ob_media_id
1572: from iem_rt_media_items
1573: where rt_interaction_id = l_rt_interaction_id and email_type = G_OUTBOUND;
1574: exception
1575: when others then
1576: null;

Line 1584: from iem_rt_media_items

1580: if ( p_status = 'V' OR p_status = 'C') then
1581: l_rt_media_item_id_ib := null;
1582: begin
1583: select rt_media_item_id, media_id into l_rt_media_item_id_ib, l_ib_media_id
1584: from iem_rt_media_items
1585: where rt_interaction_id = l_rt_interaction_id and email_type = G_INBOUND;
1586: exception
1587: when others then
1588: null;

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 1640: UPDATE iem_rt_media_items SET expire = G_EXPIRE

1636: WHERE rt_interaction_id = l_rt_interaction_id;
1637:
1638: if ( l_type = G_OUTBOUND ) then
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;

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 2078: FROM iem_rt_media_items

2074:
2075: -- Get the values that are needed later.
2076: SELECT rt_interaction_id, agent_account_id, email_account_id
2077: INTO l_rt_interaction_id, l_agent_account_id, l_email_account_id
2078: FROM iem_rt_media_items
2079: WHERE rt_media_item_id = p_rt_media_item_id
2080: AND expire in (G_ACTIVE, G_QUEUEOUT)
2081: FOR UPDATE;
2082:

Line 2088: FROM iem_rt_media_items

2084: l_mdt_message_id := null;
2085: BEGIN
2086: SELECT media_id, message_id, folder_uid
2087: INTO l_ib_media_id, l_mdt_message_id, l_uid
2088: FROM iem_rt_media_items
2089: WHERE rt_interaction_id = l_rt_interaction_id
2090: AND expire in (G_ACTIVE, G_QUEUEOUT)
2091: AND email_type = G_INBOUND;
2092: EXCEPTION

Line 2120: update iem_rt_media_items set expire = G_EXPIRE

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;
2122:
2123: goto end_of_wrapup;
2124:

Line 2193: FROM iem_rt_media_items

2189: l_ob_media_id := null;
2190: BEGIN
2191: SELECT media_id, message_id, creation_date
2192: INTO l_ob_media_id, l_outb_message_id, l_creation_date
2193: FROM iem_rt_media_items
2194: WHERE rt_interaction_id = l_rt_interaction_id
2195: AND expire in (G_ACTIVE, G_QUEUEOUT)
2196: AND email_type = G_OUTBOUND;
2197: EXCEPTION

Line 2706: UPDATE iem_rt_media_items SET expire = G_EXPIRE

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;
2708:
2709: if ( (UPPER(p_action) <> 'X') AND l_mdt_message_id IS NOT NULL) then
2710: begin

Line 2762: select IEM_RT_MEDIA_ITEMS_S1.nextval into l_m_sequence from DUAL;

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 (
2764: rt_interaction_id, rt_media_item_id, resource_id,
2765: media_id, message_id, rfc822_message_id, folder_name,
2766: db_server_id, agent_account_id, email_type, status, expire, version,

Line 2763: INSERT INTO iem_rt_media_items (

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 (
2764: rt_interaction_id, rt_media_item_id, resource_id,
2765: media_id, message_id, rfc822_message_id, folder_name,
2766: db_server_id, agent_account_id, email_type, status, expire, version,
2767: created_by, creation_date, last_updated_by, last_update_date,

Line 2774: FROM iem_rt_media_items

2770: MEDIA_ID, MESSAGE_ID, RFC822_MESSAGE_ID,
2771: folder_name, db_server_id, l_tran_to_acct_id, EMAIL_TYPE, 'R', G_ACTIVE,
2772: 0, l_created_by, SYSDATE, l_last_updated_by, SYSDATE,
2773: l_last_update_login, decode(p_transfer_msg_flag, 'T', null, p_transfer_msg_flag)
2774: FROM iem_rt_media_items
2775: WHERE rt_media_item_id = p_rt_media_item_id;
2776:
2777: -- Add MLCS i/b inbound for the second agent
2778: if(l_ib_media_id > 0 AND l_mc_param_action <> 'srautonotification') then

Line 2808: UPDATE iem_rt_media_items SET expire = G_EXPIRE

2804: -- This expires both the inbound and any outbounds
2805: -- associated with the original message. Change
2806: -- this for co-operate
2807:
2808: UPDATE iem_rt_media_items SET expire = G_EXPIRE
2809: WHERE rt_interaction_id = l_rt_interaction_id;
2810:
2811: -- Update mdt. Set the new owner of mailItem and from_agent_id and status
2812: -- and open queue_status.

Line 2859: FROM iem_rt_media_items WHERE rt_interaction_id = l_rt_interaction_id);

2855:
2856: -- Expire iem_outbox_errors records if any
2857: UPDATE iem_outbox_errors SET expire = G_EXPIRE
2858: WHERE rt_media_item_id in (SELECT rt_media_item_id
2859: FROM iem_rt_media_items WHERE rt_interaction_id = l_rt_interaction_id);
2860:
2861: -- write statistics data
2862: -- IEM_OUTBOUND_METHODS values:
2863: -- 1001 AUTO_REPLY

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 3210: SELECT count(*) into l_data FROM iem_rt_media_items

3206: l_data := 0;
3207: x_status := 0;
3208:
3209: IF (UPPER(p_account_type) = G_AGENT_ACCOUNT) THEN
3210: SELECT count(*) into l_data FROM iem_rt_media_items
3211: WHERE agent_account_id = p_account_id
3212: AND expire = G_ACTIVE;
3213:
3214: if (l_data = 0) then

Line 3222: SELECT count(*) into l_data FROM iem_rt_media_items

3218: end if;
3219: ELSE
3220: x_status := 0;
3221: FOR cr_rec in del_status_csr LOOP
3222: SELECT count(*) into l_data FROM iem_rt_media_items
3223: WHERE agent_account_id = cr_rec.agent_account_id
3224: AND expire = G_ACTIVE;
3225:
3226: if (l_data = 0) then

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 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 3326: SELECT RT_MEDIA_ITEM_ID FROM iem_rt_media_items WHERE expire= 'Y';

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:
3328: BEGIN
3329:
3330: -- Standard Start of API savepoint

Line 3395: (select rt_media_item_id from iem_rt_media_items WHERE expire='Y');

3391: -- delete outbox errors
3392: delete from iem_outbox_errors where expire = 'Y';
3393:
3394: delete from iem_msg_parts where REF_KEY in
3395: (select rt_media_item_id from iem_rt_media_items WHERE expire='Y');
3396:
3397: delete from iem_msg_parts where DELETE_FLAG = 'Y';
3398:
3399: -- Delete IEM_MC_PARAMETERS, and IEM_MC_CUSTOM_PARAM

Line 3415: -- Delete each record from iem_msg_datas where its reference at iem_rt_media_items has 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
3418: where iem_rt_media_items.rt_media_item_id = iem_msg_datas.msg_key
3419: and iem_rt_media_items.expire = 'Y');

Line 3417: (select msg_key from iem_rt_media_items, iem_msg_datas

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
3418: where iem_rt_media_items.rt_media_item_id = iem_msg_datas.msg_key
3419: and iem_rt_media_items.expire = 'Y');
3420:
3421: -- Delete each record from iem_rt_media_items where expire = 'Y'.

Line 3418: where iem_rt_media_items.rt_media_item_id = iem_msg_datas.msg_key

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
3418: where iem_rt_media_items.rt_media_item_id = iem_msg_datas.msg_key
3419: and iem_rt_media_items.expire = 'Y');
3420:
3421: -- Delete each record from iem_rt_media_items where expire = 'Y'.
3422: delete from iem_rt_media_items where expire = 'Y';

Line 3419: and iem_rt_media_items.expire = 'Y');

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
3418: where iem_rt_media_items.rt_media_item_id = iem_msg_datas.msg_key
3419: and iem_rt_media_items.expire = 'Y');
3420:
3421: -- Delete each record from iem_rt_media_items where expire = 'Y'.
3422: delete from iem_rt_media_items where expire = 'Y';
3423:

Line 3421: -- Delete each record from iem_rt_media_items where expire = 'Y'.

3417: (select msg_key from iem_rt_media_items, iem_msg_datas
3418: where iem_rt_media_items.rt_media_item_id = iem_msg_datas.msg_key
3419: and iem_rt_media_items.expire = 'Y');
3420:
3421: -- Delete each record from iem_rt_media_items where expire = 'Y'.
3422: delete from iem_rt_media_items where expire = 'Y';
3423:
3424: -- Delete each record from iem_agent_sessions where last_update_date is older than 30 days
3425: delete from iem_agent_sessions where SYSDATE - LAST_UPDATE_DATE > 30;

Line 3422: delete from iem_rt_media_items where expire = 'Y';

3418: where iem_rt_media_items.rt_media_item_id = iem_msg_datas.msg_key
3419: and iem_rt_media_items.expire = 'Y');
3420:
3421: -- Delete each record from iem_rt_media_items where expire = 'Y'.
3422: delete from iem_rt_media_items where expire = 'Y';
3423:
3424: -- Delete each record from iem_agent_sessions where last_update_date is older than 30 days
3425: delete from iem_agent_sessions where SYSDATE - LAST_UPDATE_DATE > 30;
3426:

Line 3449: from iem_rt_media_items

3445: l_rt_interaction_id := cr_rec.RT_INTERACTION_ID;
3446:
3447: select RT_MEDIA_ITEM_ID, media_id, email_type, message_id
3448: into l_RT_MEDIA_ITEM_ID, l_media_id, l_email_type, l_message_id
3449: from iem_rt_media_items
3450: where rt_interaction_id = l_rt_interaction_id;
3451:
3452:
3453: l_media_rec.media_id := l_media_id;

Line 3502: delete from iem_rt_media_items where RT_MEDIA_ITEM_ID = l_RT_MEDIA_ITEM_ID;

3498: where ref_key in (select part_name from iem_msg_parts where
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:

Line 3751: select IEM_RT_MEDIA_ITEMS_S1.nextval into l_m_sequence from DUAL;

3747: );
3748: -- db_server id used by mid-tier to locate accounts
3749: l_db_server_id := -1;
3750:
3751: select IEM_RT_MEDIA_ITEMS_S1.nextval into l_m_sequence from DUAL;
3752: INSERT INTO iem_rt_media_items (
3753: rt_interaction_id, rt_media_item_id, resource_id,
3754: media_id, message_id, rfc822_message_id, folder_name,
3755: folder_uid, email_account_id, db_server_id, email_type,

Line 3752: INSERT INTO iem_rt_media_items (

3748: -- db_server id used by mid-tier to locate accounts
3749: l_db_server_id := -1;
3750:
3751: select IEM_RT_MEDIA_ITEMS_S1.nextval into l_m_sequence from DUAL;
3752: INSERT INTO iem_rt_media_items (
3753: rt_interaction_id, rt_media_item_id, resource_id,
3754: media_id, message_id, rfc822_message_id, folder_name,
3755: folder_uid, email_account_id, db_server_id, email_type,
3756: status, expire, version, created_by, creation_date,

Line 3965: FROM iem_rt_media_items

3961: BEGIN
3962: l_rt_interaction_id := null;
3963: SELECT rt_interaction_id, version, email_type, resource_id
3964: INTO l_rt_interaction_id, l_version, l_email_type, l_resource_id
3965: FROM iem_rt_media_items
3966: WHERE rt_media_item_id = p_rt_media_item_id
3967: AND expire = G_ACTIVE
3968: FOR UPDATE NOWAIT;
3969:

Line 3992: SELECT message_id INTO l_message_id FROM iem_rt_media_items

3988: IF ( x_return_status = FND_API.G_RET_STS_SUCCESS AND l_noop = 0) THEN
3989:
3990: BEGIN
3991: l_message_id := null;
3992: SELECT message_id INTO l_message_id FROM iem_rt_media_items
3993: WHERE rt_interaction_id = l_rt_interaction_id
3994: AND email_type = G_INBOUND;
3995: EXCEPTION
3996: WHEN OTHERS THEN

Line 4000: UPDATE iem_rt_media_items

3996: WHEN OTHERS THEN
3997: NULL;
3998: END;
3999:
4000: UPDATE iem_rt_media_items
4001: SET expire = G_QUEUEOUT
4002: WHERE rt_interaction_id = l_rt_interaction_id and expire <> G_DORMANT;
4003:
4004: if ( p_action_id > 0 ) then

Line 4222: FROM iem_rt_media_items

4218: end if;
4219: begin
4220: l_rt_media_item_id := null;
4221: SELECT rt_media_item_id into l_rt_media_item_id
4222: FROM iem_rt_media_items
4223: WHERE rt_interaction_id = l_rt_ih_data.rt_interaction_id
4224: AND email_type = l_email_type
4225: AND status <> G_NEWREROUTE
4226: AND expire <> G_DORMANT;

Line 4309: FROM iem_rt_media_items

4305: db_server_id, email_type, status, version, expire
4306: INTO x_rt_media_item_id, x_rfc822_message_id,
4307: x_folder_name, x_folder_uid, l_email_account_id, l_agent_account_id,
4308: x_oes_id, x_email_type, x_status, x_version, l_expire
4309: FROM iem_rt_media_items
4310: WHERE rt_interaction_id = l_rt_ih_data.rt_interaction_id
4311: AND email_type = l_email_type
4312: AND status <> G_NEWREROUTE
4313: AND expire <> G_DORMANT;

Line 4561: select IEM_RT_MEDIA_ITEMS_S1.nextval into l_m_sequence from DUAL;

4557: );
4558: -- db_server id used by mid-tier to locate accounts
4559: l_db_server_id := -1;
4560:
4561: select IEM_RT_MEDIA_ITEMS_S1.nextval into l_m_sequence from DUAL;
4562: INSERT INTO iem_rt_media_items (
4563: rt_interaction_id, rt_media_item_id, resource_id,
4564: media_id, message_id, rfc822_message_id, folder_name,
4565: folder_uid, email_account_id, db_server_id, email_type,

Line 4562: INSERT INTO iem_rt_media_items (

4558: -- db_server id used by mid-tier to locate accounts
4559: l_db_server_id := -1;
4560:
4561: select IEM_RT_MEDIA_ITEMS_S1.nextval into l_m_sequence from DUAL;
4562: INSERT INTO iem_rt_media_items (
4563: rt_interaction_id, rt_media_item_id, resource_id,
4564: media_id, message_id, rfc822_message_id, folder_name,
4565: folder_uid, email_account_id, db_server_id, email_type,
4566: status, expire, version, created_by, creation_date,

Line 4708: select count(rt_media_item_id) into l_count1 from iem_rt_media_items

4704: raise IEM_NO_AGENT_ACCT;
4705: end;
4706:
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:

Line 4713: select count(rt_media_item_id) into l_count2 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
4716: in (select rt_interaction_id from iem_rt_interactions where expire <> G_EXPIRE
4717: and type = 'I');

Line 4823: update iem_rt_media_items

4819: -----------------------Code------------------------
4820:
4821: begin
4822:
4823: update iem_rt_media_items
4824: set message_id = p_message_id
4825: where rt_media_item_id = p_rt_media_item_id;
4826:
4827: end;