[Home] [Help]
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,
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,
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;
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
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);
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,
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,
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:
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,
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,
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:
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:
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),
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;
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;
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
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;
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:
2081:
2082: -- Get the values that are needed later.
2083: SELECT rt_interaction_id, agent_account_id, email_account_id
2084: INTO l_rt_interaction_id, l_agent_account_id, l_email_account_id
2085: FROM iem_rt_media_items
2086: WHERE rt_media_item_id = p_rt_media_item_id
2087: AND expire in (G_ACTIVE, G_QUEUEOUT)
2088: FOR UPDATE;
2089:
2091: l_mdt_message_id := null;
2092: BEGIN
2093: SELECT media_id, message_id, folder_uid
2094: INTO l_ib_media_id, l_mdt_message_id, l_uid
2095: FROM iem_rt_media_items
2096: WHERE rt_interaction_id = l_rt_interaction_id
2097: AND expire in (G_ACTIVE, G_QUEUEOUT)
2098: AND email_type = G_INBOUND;
2099: EXCEPTION
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;
2140:
2141: goto end_of_wrapup;
2142:
2215: l_ob_media_id := null;
2216: BEGIN
2217: SELECT media_id, message_id, creation_date
2218: INTO l_ob_media_id, l_outb_message_id, l_creation_date
2219: FROM iem_rt_media_items
2220: WHERE rt_interaction_id = l_rt_interaction_id
2221: AND expire in (G_ACTIVE, G_QUEUEOUT)
2222: AND email_type = G_OUTBOUND;
2223: EXCEPTION
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;
2770:
2771: if ( (UPPER(p_action) <> 'X') AND l_mdt_message_id IS NOT NULL) then
2772: begin
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 (
2826: rt_interaction_id, rt_media_item_id, resource_id,
2827: media_id, message_id, rfc822_message_id, folder_name,
2828: db_server_id, agent_account_id, email_type, status, expire, version,
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 (
2826: rt_interaction_id, rt_media_item_id, resource_id,
2827: media_id, message_id, rfc822_message_id, folder_name,
2828: db_server_id, agent_account_id, email_type, status, expire, version,
2829: created_by, creation_date, last_updated_by, last_update_date,
2832: MEDIA_ID, MESSAGE_ID, RFC822_MESSAGE_ID,
2833: folder_name, db_server_id, l_tran_to_acct_id, EMAIL_TYPE, 'R', G_ACTIVE,
2834: 0, l_created_by, SYSDATE, l_last_updated_by, SYSDATE,
2835: l_last_update_login, decode(p_transfer_msg_flag, 'T', null, p_transfer_msg_flag)
2836: FROM iem_rt_media_items
2837: WHERE rt_media_item_id = p_rt_media_item_id;
2838:
2839: -- Add MLCS i/b inbound for the second agent
2840: if(l_ib_media_id > 0 AND l_mc_param_action <> 'srautonotification') then
2866: -- This expires both the inbound and any outbounds
2867: -- associated with the original message. Change
2868: -- this for co-operate
2869:
2870: UPDATE iem_rt_media_items SET expire = G_EXPIRE
2871: WHERE rt_interaction_id = l_rt_interaction_id;
2872:
2873: -- Update mdt. Set the new owner of mailItem and from_agent_id and status
2874: -- and open queue_status.
2917:
2918: -- Expire iem_outbox_errors records if any
2919: UPDATE iem_outbox_errors SET expire = G_EXPIRE
2920: WHERE rt_media_item_id in (SELECT rt_media_item_id
2921: FROM iem_rt_media_items WHERE rt_interaction_id = l_rt_interaction_id);
2922:
2923: -- write statistics data
2924: -- IEM_OUTBOUND_METHODS values:
2925: -- 1001 AUTO_REPLY
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
3268: l_data := 0;
3269: x_status := 0;
3270:
3271: IF (UPPER(p_account_type) = G_AGENT_ACCOUNT) THEN
3272: SELECT count(*) into l_data FROM iem_rt_media_items
3273: WHERE agent_account_id = p_account_id
3274: AND expire = G_ACTIVE;
3275:
3276: if (l_data = 0) then
3280: end if;
3281: ELSE
3282: x_status := 0;
3283: FOR cr_rec in del_status_csr LOOP
3284: SELECT count(*) into l_data FROM iem_rt_media_items
3285: WHERE agent_account_id = cr_rec.agent_account_id
3286: AND expire = G_ACTIVE;
3287:
3288: if (l_data = 0) then
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:
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:
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:
3390: BEGIN
3391:
3392: -- Standard Start of API savepoint
3453: -- delete outbox errors
3454: delete from iem_outbox_errors where expire = 'Y';
3455:
3456: delete from iem_msg_parts where REF_KEY in
3457: (select rt_media_item_id from iem_rt_media_items WHERE expire='Y');
3458:
3459: delete from iem_msg_parts where DELETE_FLAG = 'Y';
3460:
3461: -- Delete IEM_MC_PARAMETERS, and IEM_MC_CUSTOM_PARAM
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
3480: where iem_rt_media_items.rt_media_item_id = iem_msg_datas.msg_key
3481: and iem_rt_media_items.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
3480: where iem_rt_media_items.rt_media_item_id = iem_msg_datas.msg_key
3481: and iem_rt_media_items.expire = 'Y');
3482:
3483: -- Delete each record from iem_rt_media_items 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
3480: where iem_rt_media_items.rt_media_item_id = iem_msg_datas.msg_key
3481: and iem_rt_media_items.expire = 'Y');
3482:
3483: -- Delete each record from iem_rt_media_items where expire = 'Y'.
3484: delete from iem_rt_media_items where expire = 'Y';
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
3480: where iem_rt_media_items.rt_media_item_id = iem_msg_datas.msg_key
3481: and iem_rt_media_items.expire = 'Y');
3482:
3483: -- Delete each record from iem_rt_media_items where expire = 'Y'.
3484: delete from iem_rt_media_items where expire = 'Y';
3485:
3479: (select msg_key from iem_rt_media_items, iem_msg_datas
3480: where iem_rt_media_items.rt_media_item_id = iem_msg_datas.msg_key
3481: and iem_rt_media_items.expire = 'Y');
3482:
3483: -- Delete each record from iem_rt_media_items where expire = 'Y'.
3484: delete from iem_rt_media_items where expire = 'Y';
3485:
3486: -- Delete each record from iem_agent_sessions where last_update_date is older than 30 days
3487: delete from iem_agent_sessions where SYSDATE - LAST_UPDATE_DATE > 30;
3480: where iem_rt_media_items.rt_media_item_id = iem_msg_datas.msg_key
3481: and iem_rt_media_items.expire = 'Y');
3482:
3483: -- Delete each record from iem_rt_media_items where expire = 'Y'.
3484: delete from iem_rt_media_items where expire = 'Y';
3485:
3486: -- Delete each record from iem_agent_sessions where last_update_date is older than 30 days
3487: delete from iem_agent_sessions where SYSDATE - LAST_UPDATE_DATE > 30;
3488:
3507: l_rt_interaction_id := cr_rec.RT_INTERACTION_ID;
3508:
3509: select RT_MEDIA_ITEM_ID, media_id, email_type, message_id
3510: into l_RT_MEDIA_ITEM_ID, l_media_id, l_email_type, l_message_id
3511: from iem_rt_media_items
3512: where rt_interaction_id = l_rt_interaction_id;
3513:
3514:
3515: l_media_rec.media_id := l_media_id;
3560: where ref_key in (select part_name from iem_msg_parts where
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:
3822: );
3823: -- db_server id used by mid-tier to locate accounts
3824: l_db_server_id := -1;
3825:
3826: select IEM_RT_MEDIA_ITEMS_S1.nextval into l_m_sequence from DUAL;
3827: INSERT INTO iem_rt_media_items (
3828: rt_interaction_id, rt_media_item_id, resource_id,
3829: media_id, message_id, rfc822_message_id, folder_name,
3830: folder_uid, email_account_id, db_server_id, email_type,
3823: -- db_server id used by mid-tier to locate accounts
3824: l_db_server_id := -1;
3825:
3826: select IEM_RT_MEDIA_ITEMS_S1.nextval into l_m_sequence from DUAL;
3827: INSERT INTO iem_rt_media_items (
3828: rt_interaction_id, rt_media_item_id, resource_id,
3829: media_id, message_id, rfc822_message_id, folder_name,
3830: folder_uid, email_account_id, db_server_id, email_type,
3831: status, expire, version, created_by, creation_date,
4036: BEGIN
4037: l_rt_interaction_id := null;
4038: SELECT rt_interaction_id, version, email_type, resource_id
4039: INTO l_rt_interaction_id, l_version, l_email_type, l_resource_id
4040: FROM iem_rt_media_items
4041: WHERE rt_media_item_id = p_rt_media_item_id
4042: AND expire = G_ACTIVE
4043: FOR UPDATE NOWAIT;
4044:
4063: IF ( x_return_status = FND_API.G_RET_STS_SUCCESS AND l_noop = 0) THEN
4064:
4065: BEGIN
4066: l_message_id := null;
4067: SELECT message_id INTO l_message_id FROM iem_rt_media_items
4068: WHERE rt_interaction_id = l_rt_interaction_id
4069: AND email_type = G_INBOUND;
4070: EXCEPTION
4071: WHEN OTHERS THEN
4071: WHEN OTHERS THEN
4072: NULL;
4073: END;
4074:
4075: UPDATE iem_rt_media_items
4076: SET expire = G_QUEUEOUT
4077: WHERE rt_interaction_id = l_rt_interaction_id and expire <> G_DORMANT;
4078:
4079: if ( p_action_id > 0 ) then
4293: end if;
4294: begin
4295: l_rt_media_item_id := null;
4296: SELECT rt_media_item_id into l_rt_media_item_id
4297: FROM iem_rt_media_items
4298: WHERE rt_interaction_id = l_rt_ih_data.rt_interaction_id
4299: AND email_type = l_email_type
4300: AND status <> G_NEWREROUTE
4301: AND expire <> G_DORMANT;
4380: db_server_id, email_type, status, version, expire
4381: INTO x_rt_media_item_id, x_rfc822_message_id,
4382: x_folder_name, x_folder_uid, l_email_account_id, l_agent_account_id,
4383: x_oes_id, x_email_type, x_status, x_version, l_expire
4384: FROM iem_rt_media_items
4385: WHERE rt_interaction_id = l_rt_ih_data.rt_interaction_id
4386: AND email_type = l_email_type
4387: AND status <> G_NEWREROUTE
4388: AND expire <> G_DORMANT;
4632: );
4633: -- db_server id used by mid-tier to locate accounts
4634: l_db_server_id := -1;
4635:
4636: select IEM_RT_MEDIA_ITEMS_S1.nextval into l_m_sequence from DUAL;
4637: INSERT INTO iem_rt_media_items (
4638: rt_interaction_id, rt_media_item_id, resource_id,
4639: media_id, message_id, rfc822_message_id, folder_name,
4640: folder_uid, email_account_id, db_server_id, email_type,
4633: -- db_server id used by mid-tier to locate accounts
4634: l_db_server_id := -1;
4635:
4636: select IEM_RT_MEDIA_ITEMS_S1.nextval into l_m_sequence from DUAL;
4637: INSERT INTO iem_rt_media_items (
4638: rt_interaction_id, rt_media_item_id, resource_id,
4639: media_id, message_id, rfc822_message_id, folder_name,
4640: folder_uid, email_account_id, db_server_id, email_type,
4641: status, expire, version, created_by, creation_date,
4779: raise IEM_NO_AGENT_ACCT;
4780: end;
4781:
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:
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
4791: in (select rt_interaction_id from iem_rt_interactions where expire <> G_EXPIRE
4792: and type = 'I');
4894: -----------------------Code------------------------
4895:
4896: begin
4897:
4898: update iem_rt_media_items
4899: set message_id = p_message_id
4900: where rt_media_item_id = p_rt_media_item_id;
4901:
4902: end;