[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_last_updated_by NUMBER;
l_last_update_login NUMBER;
l_last_updated_by :=NVL(to_number(FND_PROFILE.VALUE('USER_ID')),-1);
l_last_update_login := NVL(to_number(FND_PROFILE.VALUE('LOGIN_ID')), -1);
select decode(p_classification_id,G_NUM_NOP2, null,
p_classification_id) into l_classification_id from DUAL;
SELECT name INTO x_route_classification
FROM iem_route_classifications
WHERE ROUTE_CLASSIFICATION_ID = l_email_data_rec.RT_CLASSIFICATION_ID;
select IEM_RT_INTERACTIONS_S1.nextval into l_i_sequence from DUAL;
INSERT INTO iem_rt_interactions (
rt_interaction_id, resource_id, customer_id, contact_id, type,
status, expire, created_by, creation_date, last_updated_by,
last_update_date, last_update_login, parent_interaction_id,
service_request_id, inb_tag_id, interaction_id, ih_creator,
lead_id )
VALUES (
l_i_sequence, p_resource_id, l_customer_id, l_contact_id,
G_INBOUND, G_WORK_IN_PROGRESS, G_ACTIVE, l_created_by,
SYSDATE, l_last_updated_by, SYSDATE, l_last_update_login,
l_parent_ih_id, l_sr_id, l_tag_id,
l_email_data_rec.IH_INTERACTION_ID, l_ih_creator,
l_lead_id
);
select IEM_RT_MEDIA_ITEMS_S1.nextval into l_m_sequence from DUAL;
INSERT INTO iem_rt_media_items (
rt_interaction_id, rt_media_item_id, resource_id,
media_id, message_id, rfc822_message_id, folder_name,
folder_uid, email_account_id, db_server_id, email_type,
status, expire, version, created_by, creation_date,
last_updated_by, last_update_date, last_update_login )
VALUES (
l_i_sequence, l_m_sequence, p_resource_id,
l_email_data_rec.IH_MEDIA_ITEM_ID,
l_email_data_rec.MESSAGE_ID,
null,
x_folder_name,
-1,
l_email_data_rec.EMAIL_ACCOUNT_ID,
l_db_server_id,
G_INBOUND, G_UNMOVED, G_ACTIVE,0, l_created_by, SYSDATE,
l_last_updated_by, SYSDATE, l_last_update_login
);
UPDATE IEM_RT_PROC_EMAILS SET queue_status = NULL
WHERE message_id = l_email_data_rec.MESSAGE_ID;
SELECT rt_interaction_id, rt_media_item_id, rfc822_message_id,
folder_name, message_id, email_account_id, agent_account_id,
db_server_id, email_type, status, version, expire, edit_mode,
media_id
INTO l_rt_interaction_id, l_rt_media_item_id, l_rfc822_message_id,
l_folder_name, l_message_id, l_email_account_id, l_agent_account_id,
l_oes_id, l_email_type, l_status, l_version, l_expire, l_edit_mode,
l_ih_media_id
FROM iem_rt_media_items
WHERE rt_media_item_id = p_rt_media_item_id;
SELECT customer_id, contact_id, interaction_id, parent_interaction_id,
service_request_id, service_request_action, mc_parameter_id,
inb_tag_id, lead_id, resource_id, relationship_id
INTO x_customer_id, x_contact_id, x_interaction_id, x_parent_ih_id,
x_service_request_id, x_service_request_action, x_mc_parameter_id, x_tag_id,
x_lead_id, x_resource_id, x_relationship_id
FROM iem_rt_interactions
WHERE rt_interaction_id = l_rt_interaction_id;
SELECT rt_interaction_id, rt_media_item_id, rfc822_message_id,
folder_name, message_id, email_account_id, agent_account_id,
db_server_id, email_type, status, version, edit_mode, media_id
INTO x_rt_interaction_id, x_rt_media_item_id, x_rfc822_message_id,
x_folder_name, x_message_id, l_email_account_id1, l_agent_account_id1,
x_oes_id, x_email_type, x_status, x_version, x_edit_mode, x_ih_media_id
FROM iem_rt_media_items
WHERE rt_interaction_id = l_rt_interaction_id
AND email_type = p_email_type
AND expire in (G_ACTIVE, G_QUEUEOUT);
SELECT rt_interaction_id, rt_media_item_id, rfc822_message_id,
folder_name, message_id, email_account_id, agent_account_id,
db_server_id, email_type, status, version, expire
INTO x_rt_interaction_id, x_rt_media_item_id, x_rfc822_message_id,
x_folder_name, x_mdt_message_id, l_email_account_id, l_agent_account_id,
x_oes_id, x_email_type, x_status, x_version, l_expire
FROM iem_rt_media_items
WHERE agent_account_id = p_agentAccount_id
AND message_id = p_message_id
AND email_type = p_email_type
AND expire IN (G_ACTIVE, G_DORMANT, G_QUEUEOUT);
SELECT customer_id, contact_id, interaction_id, parent_interaction_id,
service_request_id, service_request_action, inb_tag_id, lead_id
INTO x_customer_id, x_contact_id, x_interaction_id,
x_parent_interaction_id, x_service_request_id,
x_service_request_action, x_tag_id, x_lead_id
FROM iem_rt_interactions
WHERE rt_interaction_id = x_rt_interaction_id;
SELECT rt_classification_id into x_route_classification_id
FROM IEM_RT_PROC_EMAILS
WHERE message_id = x_mdt_message_id;
SELECT name into x_route_classification
FROM iem_route_classifications
WHERE ROUTE_CLASSIFICATION_ID = x_route_classification_id;
l_last_updated_by NUMBER;
l_last_update_login NUMBER;
l_last_updated_by :=NVL(to_number(FND_PROFILE.VALUE('USER_ID')),-1);
l_last_update_login :=NVL(to_number(FND_PROFILE.VALUE('LOGIN_ID')), -1);
l_last_updated_by NUMBER;
l_last_update_login NUMBER;
l_last_updated_by :=NVL(to_number(FND_PROFILE.VALUE('USER_ID')),-1);
l_last_update_login :=NVL(to_number(FND_PROFILE.VALUE('LOGIN_ID')), -1);
SELECT A.EMAIL_ACCOUNT_ID
INTO l_email_account_id_ih
FROM IEM_AGENTS A
WHERE A.agent_id = p_account_id;
p_login_id => l_last_update_login,
x_return_status => l_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_media_rec => l_media_rec,
x_media_id => l_media_id
);
SELECT IEM_RT_INTERACTIONS_S1.nextval INTO l_i_sequence FROM DUAL;
INSERT INTO iem_rt_interactions (
rt_interaction_id, resource_id, customer_id, type,
status, expire, created_by, creation_date, last_updated_by,
last_update_date, last_update_login, interaction_id,
service_request_id, mc_parameter_id, service_request_action,
contact_id, lead_id, parent_interaction_id,
action_id, relationship_id)
VALUES (
l_i_sequence, p_resource_id,
decode(p_customer_id,-1, null, p_customer_id),
G_OUTBOUND, p_status, G_ACTIVE, l_created_by,
SYSDATE,l_last_updated_by, SYSDATE, l_last_update_login,
decode(p_interaction_id, G_NUM_NOP, null, p_interaction_id),
decode(p_service_request_id, G_NUM_NOP, null, p_service_request_id),
decode(p_mc_parameter_id, G_NUM_NOP, null, p_mc_parameter_id),
decode(p_service_request_action, G_CHAR_NOP, null, p_service_request_action),
decode(p_contact_id, G_NUM_NOP, null, p_contact_id),
decode(p_lead_id, G_NUM_NOP, null, p_lead_id),
decode(p_parent_ih_id, G_NUM_NOP, null, p_parent_ih_id),
decode(p_action_id, G_NUM_NOP, null, p_action_id),
decode(p_relationship_id, G_NUM_NOP, null, p_relationship_id)
);
select IEM_RT_MEDIA_ITEMS_S1.nextval into l_m_sequence from DUAL;
INSERT INTO iem_rt_media_items (
rt_interaction_id, rt_media_item_id, resource_id,
media_id, message_id, rfc822_message_id, folder_name,
folder_uid, email_account_id,agent_account_id,
db_server_id, email_type, status, expire,
version, created_by, creation_date, last_updated_by,
last_update_date, last_update_login
)
VALUES (
l_i_sequence, l_m_sequence, p_resource_id, l_ob_media_id, null,
p_rfc822_message_id, p_folder_name,p_folder_uid,
l_email_account_id, l_agent_account_id, l_db_server_id,
G_OUTBOUND, p_status, G_ACTIVE, '0', l_created_by, SYSDATE,
l_last_updated_by, SYSDATE, l_last_update_login
);
SELECT rt_interaction_id, email_type, media_id
INTO l_rt_interaction_id, l_email_type, l_ib_media_id
FROM iem_rt_media_items
WHERE rt_media_item_id = p_rt_media_item_id
AND expire = G_ACTIVE
FOR UPDATE NOWAIT;
select IEM_RT_MEDIA_ITEMS_S1.nextval into l_m_sequence from DUAL;
INSERT INTO iem_rt_media_items (
rt_interaction_id, rt_media_item_id, resource_id,
media_id, message_id, rfc822_message_id, folder_name,
folder_uid, email_account_id,agent_account_id,
db_server_id, email_type, status, expire,
version, created_by, creation_date, last_updated_by,
last_update_date, last_update_login
)
VALUES (
l_rt_interaction_id, l_m_sequence, p_resource_id, l_ob_media_id,
null, p_rfc822_message_id, p_folder_name,p_folder_uid,
l_email_account_id, l_agent_account_id, l_db_server_id,
G_OUTBOUND, p_status, G_ACTIVE, '0', l_created_by, SYSDATE,
l_last_updated_by, SYSDATE, l_last_update_login
);
PROCEDURE updateMediaDetails (p_api_version_number IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_commit IN VARCHAR2,
p_rfc822_message_id IN VARCHAR2,
p_folder_name IN VARCHAR2,
p_folder_uid IN NUMBER,
p_account_id IN NUMBER,
p_account_type IN VARCHAR2,
p_status IN VARCHAR2,
p_customer_id IN NUMBER,
p_rt_media_item_id IN NUMBER,
p_version IN NUMBER,
p_interaction_id IN NUMBER,
p_service_request_id IN NUMBER,
p_mc_parameter_id IN NUMBER,
p_service_request_action IN VARCHAR2,
p_contact_id IN NUMBER,
p_parent_interaction_id IN NUMBER,
p_tag_id IN VARCHAR2,
p_edit_mode IN VARCHAR2,
p_lead_id IN NUMBER,
p_relationship_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_version OUT NOCOPY NUMBER
) IS
l_api_name VARCHAR2(255);
l_last_updated_by NUMBER;
l_last_update_login NUMBER;
SELECT session_id from IEU_SH_SESSIONS
WHERE BEGIN_DATE_TIME = (SELECT MAX(BEGIN_DATE_TIME)
FROM IEU_SH_SESSIONS
WHERE RESOURCE_ID = l_resource_id
AND ACTIVE_FLAG = 'T'
AND APPLICATION_ID = 680);
SAVEPOINT updateMediaDetails_pvt;
l_api_name :='updateMediaDetails';
l_last_updated_by :=NVL(to_number(FND_PROFILE.VALUE('USER_ID')),-1);
l_last_update_login := NVL(to_number(FND_PROFILE.VALUE('LOGIN_ID')), -1);
SELECT rt_interaction_id, version, email_type, email_account_id,
agent_account_id, media_id, resource_id, status, message_id
INTO l_rt_interaction_id, l_version, l_email_type, l_email_account_id_old,
l_agent_account_id_old, l_media_id, l_resource_id, l_rt_status, l_msg_id
FROM iem_rt_media_items
WHERE rt_media_item_id = p_rt_media_item_id
AND expire in (G_ACTIVE, G_DORMANT, G_QUEUEOUT)
FOR update nowait;
SELECT message_id
INTO l_msg_id
FROM iem_rt_media_items
WHERE rt_interaction_id = l_rt_interaction_id
AND email_type = G_INBOUND;
UPDATE IEM_RT_PROC_EMAILS SET mail_item_status = 'S' where message_id = l_msg_id;
p_login_id => l_last_update_login,
x_return_status => l_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
x_milcs_id => x_milcs_id,
p_media_lc_rec => l_media_lc_rec);
UPDATE IEM_RT_PROC_EMAILS SET mail_item_status = 'R' where message_id = l_msg_id;
p_login_id => l_last_update_login,
x_return_status => l_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
x_milcs_id => x_milcs_id,
p_media_lc_rec => l_media_lc_rec);
UPDATE IEM_RT_PROC_EMAILS SET mail_item_status = 'R' where message_id = l_msg_id;
UPDATE iem_rt_media_items SET
RFC822_MESSAGE_ID = decode(p_rfc822_message_id, G_CHAR_NOP, RFC822_MESSAGE_ID, p_rfc822_message_id),
FOLDER_NAME = decode(p_folder_name, G_CHAR_NOP, FOLDER_NAME, p_folder_name),
FOLDER_UID = decode(p_folder_uid, G_NUM_NOP, FOLDER_UID, p_folder_uid),
EMAIL_ACCOUNT_ID = decode(l_email_account_id, G_NUM_NOP, EMAIL_ACCOUNT_ID, l_email_account_id),
AGENT_ACCOUNT_ID = decode(l_agent_account_id, G_NUM_NOP, AGENT_ACCOUNT_ID, l_agent_account_id),
STATUS = decode(p_status, G_CHAR_NOP, STATUS, p_status),
VERSION = l_version,
LAST_UPDATED_BY = l_last_updated_by,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATE_LOGIN = l_last_update_login,
EDIT_MODE = decode(l_edit_mode, G_CHAR_NOP, EDIT_MODE, l_edit_mode)
WHERE rt_media_item_id = p_rt_media_item_id;
UPDATE iem_rt_interactions SET
CUSTOMER_ID = decode(p_customer_id, G_NUM_NOP, CUSTOMER_ID, p_customer_id),
CONTACT_ID = decode(p_contact_id, G_NUM_NOP, contact_id, p_contact_id),
RELATIONSHIP_ID = decode(p_relationship_id, G_NUM_NOP, relationship_id, p_relationship_id),
INTERACTION_ID = decode(p_interaction_id, G_NUM_NOP, interaction_id, p_interaction_id),
SERVICE_REQUEST_ID = decode(p_service_request_id, G_NUM_NOP, service_request_id, p_service_request_id),
MC_PARAMETER_ID = decode(p_mc_parameter_id, G_NUM_NOP, mc_parameter_id, p_mc_parameter_id),
SERVICE_REQUEST_ACTION = decode(p_service_request_action, G_CHAR_NOP, service_request_action, p_service_request_action),
PARENT_INTERACTION_ID = decode(p_parent_interaction_id, G_NUM_NOP, parent_interaction_id, p_parent_interaction_id),
INB_TAG_ID = decode(p_tag_id, G_CHAR_NOP, inb_tag_id, p_tag_id),
LAST_UPDATED_BY = l_last_updated_by,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATE_LOGIN = l_last_update_login,
LEAD_ID = decode(p_lead_id, G_NUM_NOP, lead_id, p_lead_id)
WHERE rt_interaction_id = l_rt_interaction_id;
p_login_id => l_last_update_login,
P_REASON_CODE => null,
P_REQUEST_METHOD => null,
P_REQUESTED_MEDIA_TYPE_ID => null,
P_WORK_ITEM_TYPE_CODE => null,
P_WORK_ITEM_PK_ID => null,
p_end_activity_flag => 'Y',
x_activity_id => l_activity_id,
x_msg_count => l_count,
x_msg_data => l_data,
x_return_status => l_ret_status
);
select media_id into l_ob_media_id
from iem_rt_media_items
where rt_interaction_id = l_rt_interaction_id and email_type = G_OUTBOUND;
select rt_media_item_id, media_id into l_rt_media_item_id_ib, l_ib_media_id
from iem_rt_media_items
where rt_interaction_id = l_rt_interaction_id and email_type = G_INBOUND;
IEM_MSG_STAT_PUB.deleteMSGStat(
p_api_version_number => 1.0,
p_init_msg_list => fnd_api.g_false,
p_commit => fnd_api.g_false,
p_outBoundMediaID => l_ob_media_id,
p_inBoundMediaID => -1,
x_return_status => l_ret_status,
x_msg_count => l_count,
x_msg_data => l_data
);
SELECT type INTO l_type FROM iem_rt_interactions
WHERE rt_interaction_id = l_rt_interaction_id;
UPDATE iem_rt_media_items SET expire = G_EXPIRE
WHERE rt_media_item_id = p_rt_media_item_id;
UPDATE iem_rt_interactions SET expire = G_EXPIRE
WHERE rt_interaction_id = l_rt_interaction_id;
ROLLBACK TO updateMediaDetails_pvt;
ROLLBACK TO updateMediaDetails_pvt;
ROLLBACK TO updateMediaDetails_pvt;
ROLLBACK TO updateMediaDetails_pvt;
END updateMediaDetails;
l_last_updated_by NUMBER;
l_last_update_login NUMBER;
l_last_updated_by :=NVL(to_number(FND_PROFILE.VALUE('USER_ID')),-1);
l_last_update_login :=NVL(to_number(FND_PROFILE.VALUE('LOGIN_ID')), -1);
SELECT a.rt_interaction_id, decode(a.interaction_id, -1, NULL, a.interaction_id),
a.resource_id, a.creation_date,
decode(a.parent_interaction_id, NULL, fnd_api.g_miss_num, a.parent_interaction_id),
a.service_request_id, a.lead_id, a.contact_id, a.relationship_id
INTO l_rt_interaction_id, l_interaction_id, l_resource_id, l_start_date,
l_parent_ih_id, x_sr_id, x_lead_id, l_contact_id, l_relationship_id
FROM iem_rt_interactions a, iem_rt_media_items b
WHERE b.rt_media_item_id = p_rt_media_item_id
AND a.rt_interaction_id = b.rt_interaction_id
AND a.expire <> G_EXPIRE;
select PARTY_TYPE into l_party_type from HZ_PARTIES
where party_id = p_customer_id;
select decode(p_result_id,-1, NULL, p_result_id) into l_result_id from DUAL;
select decode(p_reason_id,-1, NULL, p_reason_id) into l_reason_id from DUAL;
p_login_id => l_last_update_login,
x_return_status => l_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
x_interaction_id => l_interaction_id,
p_interaction_rec => l_interaction_rec
);
UPDATE iem_rt_interactions set interaction_id = l_interaction_id,
ih_creator = 'Y'
WHERE rt_interaction_id = l_rt_interaction_id;
l_last_updated_by NUMBER;
l_last_update_login NUMBER;
SELECT session_id from IEU_SH_SESSIONS
WHERE BEGIN_DATE_TIME = (SELECT MAX(BEGIN_DATE_TIME)
FROM IEU_SH_SESSIONS
WHERE RESOURCE_ID = l_resource_id
AND ACTIVE_FLAG = 'T'
AND APPLICATION_ID = 680);
l_last_updated_by :=NVL(to_number(FND_PROFILE.VALUE('USER_ID')),-1);
l_last_update_login := NVL(to_number(FND_PROFILE.VALUE('LOGIN_ID')), -1);
SELECT rt_interaction_id, agent_account_id, email_account_id
INTO l_rt_interaction_id, l_agent_account_id, l_email_account_id
FROM iem_rt_media_items
WHERE rt_media_item_id = p_rt_media_item_id
AND expire in (G_ACTIVE, G_QUEUEOUT)
FOR UPDATE;
SELECT media_id, message_id, folder_uid
INTO l_ib_media_id, l_mdt_message_id, l_uid
FROM iem_rt_media_items
WHERE rt_interaction_id = l_rt_interaction_id
AND expire in (G_ACTIVE, G_QUEUEOUT)
AND email_type = G_INBOUND;
UPDATE iem_outbox_errors SET expire = G_EXPIRE
WHERE rt_media_item_id = p_rt_media_item_id;
update iem_rt_interactions set expire = G_EXPIRE
where rt_interaction_id = l_rt_interaction_id;
update iem_rt_media_items set expire = G_EXPIRE
where rt_media_item_id = p_rt_media_item_id;
SELECT decode(interaction_id, -1, null, interaction_id),
ih_creator, service_request_id, service_request_action,
decode(parent_interaction_id, NULL, fnd_api.g_miss_num, parent_interaction_id),
lead_id, nvl(action_id, -1), nvl(contact_id, -1), nvl(to_resource_id, -1),
relationship_id, status, mc_parameter_id
INTO l_interaction_id, l_ih_creator, l_sr_id, l_sr_action, l_parent_ih_id,
l_lead_id, l_action_id, l_contact_id, l_to_resource_id, l_relationship_id,
l_rt_ih_status, l_mc_parameter_id
FROM iem_rt_interactions
WHERE rt_interaction_id = l_rt_interaction_id
AND expire in (G_ACTIVE, G_QUEUEOUT, G_PROCESSING);
select action into l_mc_param_action
from iem_mc_parameters where mc_parameter_id = l_mc_parameter_id;
select PARTY_TYPE into l_party_type from HZ_PARTIES
where party_id = p_customer_id;
SELECT media_id, message_id, creation_date
INTO l_ob_media_id, l_outb_message_id, l_creation_date
FROM iem_rt_media_items
WHERE rt_interaction_id = l_rt_interaction_id
AND expire in (G_ACTIVE, G_QUEUEOUT)
AND email_type = G_OUTBOUND;
SELECT email_account_id
INTO l_email_account_id
FROM IEM_AGENTS
WHERE agent_id = l_agent_account_id;
SELECT customer_id, resource_id, creation_date, nvl(spv_resource_id, -1)
INTO l_customer_id, l_resource_id, l_start_date, l_spv_resource_id
FROM iem_rt_interactions
WHERE rt_interaction_id = l_rt_interaction_id;
select decode(p_activity_type_id,-1, NULL, p_activity_type_id) into l_activity_type_id from DUAL;
select decode(p_result_id,-1, NULL, p_result_id) into l_result_id from DUAL;
select decode(p_reason_id,-1, NULL, p_reason_id) into l_reason_id from DUAL;
l_activity_type := 'EMAIL_DELETED';
p_login_id => l_last_update_login,
x_return_status => l_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
x_interaction_id => l_interaction_id,
p_interaction_rec => l_interaction_rec
);
UPDATE iem_rt_interactions set IH_CREATOR = 'Y', interaction_id = l_interaction_id
WHERE rt_interaction_id = l_rt_interaction_id;
SELECT doc_id, doc_ref into l_activity_rec.doc_id, l_activity_rec.doc_ref
FROM JTF_IH_ACTIVITIES WHERE INTERACTION_ID = l_parent_ih_id
AND ACTION_ID in (22,30,31,33,65,72,74);
p_login_id => l_last_update_login,
x_return_status => l_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_media_rec => l_media_rec,
x_media_id => l_ob_media_id
);
p_login_id => l_last_update_login,
x_return_status => l_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
x_milcs_id => x_milcs_id,
p_media_lc_rec => l_media_lc_rec);
p_login_id => l_last_update_login,
x_return_status => l_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_media_rec => l_media_rec
);
p_login_id => l_last_update_login,
x_return_status => l_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
x_milcs_id => x_milcs_id,
p_media_lc_rec => l_media_lc_rec);
SELECT doc_id, doc_ref into l_activity_rec.doc_id, l_activity_rec.doc_ref
FROM JTF_IH_ACTIVITIES WHERE INTERACTION_ID = l_parent_ih_id
AND ACTION_ID in (22,30,31,33,65,72,74);
p_login_id => l_last_update_login,
x_return_status => l_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_activity_rec => l_activity_rec,
x_activity_id => x_activity_id_i
);
p_login_id => l_last_update_login,
x_return_status => l_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_activity_rec => l_activity_rec,
x_activity_id => x_activity_id_o
);
IF (UPPER(l_sr_action) = 'UPDATE') THEN
l_action_id_sr := 14;
p_login_id => l_last_update_login,
x_return_status => l_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_activity_rec => l_activity_rec,
x_activity_id => x_activity_id_o
);
p_login_id => l_last_update_login,
x_return_status => l_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_activity_rec => l_activity_rec,
x_activity_id => x_activity_id_o
);
p_login_id => l_last_update_login,
x_return_status => l_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_interaction_rec => l_interaction_rec
);
UPDATE iem_rt_interactions SET expire = G_EXPIRE
WHERE rt_interaction_id = l_rt_interaction_id;
UPDATE iem_rt_media_items SET expire = G_EXPIRE
WHERE rt_interaction_id = l_rt_interaction_id;
update IEM_RT_PROC_EMAILS set queue_status = null
where message_id = l_mdt_message_id;
select agent_id into l_tran_to_acct_id from iem_agents
where resource_id = p_to_resource_id
and email_account_id = l_email_account_id;
select IEM_RT_INTERACTIONS_S1.nextval into l_i_sequence from DUAL;
INSERT INTO iem_rt_interactions (
rt_interaction_id, resource_id, customer_id, type,
status, expire, created_by, creation_date, last_updated_by,
last_update_date, last_update_login, contact_id, inb_tag_id,
lead_id, parent_interaction_id, service_request_id,
relationship_id )
SELECT l_i_sequence, p_to_resource_id, l_ih_customer_id, TYPE,
G_WORK_IN_PROGRESS, G_ACTIVE, l_created_by, SYSDATE,
l_last_updated_by, SYSDATE, l_last_update_login,
l_contact_id, inb_tag_id, l_tran_lead_id,
l_interaction_id, l_sr_id,
l_relationship_id
FROM iem_rt_interactions
WHERE rt_interaction_id = l_rt_interaction_id;
select IEM_RT_MEDIA_ITEMS_S1.nextval into l_m_sequence from DUAL;
INSERT INTO iem_rt_media_items (
rt_interaction_id, rt_media_item_id, resource_id,
media_id, message_id, rfc822_message_id, folder_name,
db_server_id, agent_account_id, email_type, status, expire, version,
created_by, creation_date, last_updated_by, last_update_date,
last_update_login, edit_mode )
SELECT l_i_sequence, l_m_sequence, p_to_resource_id,
MEDIA_ID, MESSAGE_ID, RFC822_MESSAGE_ID,
folder_name, db_server_id, l_tran_to_acct_id, EMAIL_TYPE, 'R', G_ACTIVE,
0, l_created_by, SYSDATE, l_last_updated_by, SYSDATE,
l_last_update_login, decode(p_transfer_msg_flag, 'T', null, p_transfer_msg_flag)
FROM iem_rt_media_items
WHERE rt_media_item_id = p_rt_media_item_id;
p_login_id => l_last_update_login,
x_return_status => l_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
x_milcs_id => x_milcs_id,
p_media_lc_rec => l_media_lc_rec);
UPDATE iem_rt_interactions SET expire = G_EXPIRE
WHERE rt_interaction_id = l_rt_interaction_id;
UPDATE iem_rt_media_items SET expire = G_EXPIRE
WHERE rt_interaction_id = l_rt_interaction_id;
UPDATE IEM_RT_PROC_EMAILS
SET resource_id = p_to_resource_id,
from_resource_id = l_reroute_resource_id,
mail_item_status = 'T',
queue_status = null,
message_flag = p_transfer_msg_flag
WHERE message_id = l_mdt_message_id;
p_login_id => l_last_update_login,
P_REASON_CODE => null,
P_REQUEST_METHOD => null,
P_REQUESTED_MEDIA_TYPE_ID => null,
P_WORK_ITEM_TYPE_CODE => null,
P_WORK_ITEM_PK_ID => null,
p_end_activity_flag => 'Y',
x_activity_id => l_activity_id,
x_msg_count => l_count,
x_msg_data => l_data,
x_return_status => l_ret_status
);
UPDATE iem_outbox_errors SET expire = G_EXPIRE
WHERE rt_media_item_id in (SELECT rt_media_item_id
FROM iem_rt_media_items WHERE rt_interaction_id = l_rt_interaction_id);
select count(OUTBOUND_MSG_STATS_ID) into l_use_suggested
from iem_outbound_msg_stats
where media_id = l_ob_media_id
and USES_SUGGESTIONS_Y_N = 'Y';
IEM_MSG_STAT_PUB.deleteMSGStat(
p_api_version_number => 1.0,
p_init_msg_list => fnd_api.g_false,
p_commit => fnd_api.g_false,
p_outBoundMediaID => l_ob_media_id,
p_inBoundMediaID => nvl(l_ib_media_id, -1),
x_return_status => l_ret_status,
x_msg_count => l_count,
x_msg_data => l_data
);
l_last_updated_by NUMBER;
l_last_update_login NUMBER;
SELECT m.rfc822_message_id,m.email_account_id, m.agent_account_id,
m.email_type, m.status, m.version, m.rt_interaction_id,
m.db_server_id, m.rt_media_item_id, m.folder_name, m.folder_uid,
i.customer_id
FROM iem_rt_media_items m, iem_rt_interactions i
WHERE i.TYPE = G_OUTBOUND
AND i.RT_INTERACTION_ID = m.RT_INTERACTION_ID
AND m.RESOURCE_ID = p_resource_id
AND m.EMAIL_TYPE = G_OUTBOUND
AND m.expire = G_ACTIVE
ORDER BY m.rt_interaction_id;
l_last_updated_by :=NVL(to_number(FND_PROFILE.VALUE('USER_ID')),-1);
l_last_update_login := NVL(to_number(FND_PROFILE.VALUE('LOGIN_ID')), -1);
SELECT agent_id
FROM iem_agents
WHERE email_account_id = p_account_id
ORDER BY agent_id;
SELECT count(*) into l_data FROM iem_rt_media_items
WHERE agent_account_id = p_account_id
AND expire = G_ACTIVE;
SELECT count(*) into l_data FROM iem_rt_media_items
WHERE agent_account_id = cr_rec.agent_account_id
AND expire = G_ACTIVE;
l_last_update_login NUMBER;
SELECT iem_rt_interactions.RT_INTERACTION_ID
FROM iem_rt_interactions, iem_rt_media_items
WHERE iem_rt_interactions.expire='N' and iem_rt_interactions.type = 'O'
AND SYSDATE - iem_rt_interactions.LAST_UPDATE_DATE > 30
AND iem_rt_media_items.RT_INTERACTION_ID = iem_rt_interactions.RT_INTERACTION_ID;
SELECT RT_MEDIA_ITEM_ID FROM iem_rt_media_items WHERE expire= 'Y';
l_last_update_login := NVL(to_number(FND_PROFILE.VALUE('LOGIN_ID')), -1);
select PART_NAME into l_tmp_ref_name from iem_msg_parts where to_char(ref_key) = l_tmp_ref_key
and PART_TYPE = 'ATTACHMAIL' and rownum < 2;
update iem_msg_parts set DELETE_FLAG = 'Y' where to_char(REF_KEY) = l_tmp_ref_key;
update iem_msg_parts
set delete_flag='Y'
where ref_key in (select part_name from iem_msg_parts where
ref_key=rt_expire_csr_rec.RT_MEDIA_ITEM_ID and part_type='ATTACHMAIL');
delete from iem_outbox_errors where expire = 'Y';
delete from iem_msg_parts where REF_KEY in
(select rt_media_item_id from iem_rt_media_items WHERE expire='Y');
delete from iem_msg_parts where DELETE_FLAG = 'Y';
delete from IEM_MC_CUSTOM_PARAMS where MC_PARAMETER_ID in
(select IEM_MC_CUSTOM_PARAMS.MC_PARAMETER_ID
from IEM_MC_CUSTOM_PARAMS, iem_rt_interactions
where IEM_MC_CUSTOM_PARAMS.MC_PARAMETER_ID = iem_rt_interactions.MC_PARAMETER_ID
and iem_rt_interactions.expire = 'Y');
delete from IEM_MC_PARAMETERS where MC_PARAMETER_ID in
(select IEM_MC_PARAMETERS.MC_PARAMETER_ID
from IEM_MC_PARAMETERS, iem_rt_interactions
where IEM_MC_PARAMETERS.MC_PARAMETER_ID = iem_rt_interactions.MC_PARAMETER_ID
and iem_rt_interactions.expire = 'Y');
delete from iem_rt_interactions where expire = 'Y';
delete from iem_msg_datas where msg_key in
(select msg_key from iem_rt_media_items, iem_msg_datas
where iem_rt_media_items.rt_media_item_id = iem_msg_datas.msg_key
and iem_rt_media_items.expire = 'Y');
delete from iem_rt_media_items where expire = 'Y';
delete from iem_agent_sessions where SYSDATE - LAST_UPDATE_DATE > 30;
delete from IEM_MC_CUSTOM_PARAMS where MC_PARAMETER_ID in
(select IEM_MC_CUSTOM_PARAMS.MC_PARAMETER_ID
from IEM_MC_CUSTOM_PARAMS, iem_rt_interactions
where IEM_MC_CUSTOM_PARAMS.MC_PARAMETER_ID = iem_rt_interactions.MC_PARAMETER_ID
and iem_rt_interactions.expire='N' and iem_rt_interactions.type='O' and
SYSDATE - iem_rt_interactions.LAST_UPDATE_DATE > 30);
delete from IEM_MC_PARAMETERS where MC_PARAMETER_ID in
(select IEM_MC_PARAMETERS.MC_PARAMETER_ID
from IEM_MC_PARAMETERS, iem_rt_interactions
where IEM_MC_PARAMETERS.MC_PARAMETER_ID = iem_rt_interactions.MC_PARAMETER_ID
and iem_rt_interactions.expire='N' and iem_rt_interactions.type='O' and
SYSDATE - iem_rt_interactions.LAST_UPDATE_DATE > 30);
select RT_MEDIA_ITEM_ID, media_id, email_type, message_id
into l_RT_MEDIA_ITEM_ID, l_media_id, l_email_type, l_message_id
from iem_rt_media_items
where rt_interaction_id = l_rt_interaction_id;
p_login_id => l_last_update_login,
x_return_status => l_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_media_rec => l_media_rec
);
select PART_NAME into l_tmp_ref_name from iem_msg_parts where to_char(ref_key) = l_tmp_ref_key
and PART_TYPE = 'ATTACHMAIL' and rownum < 2;
update iem_msg_parts set DELETE_FLAG = 'Y' where to_char(REF_KEY) = l_tmp_ref_key;
update iem_msg_parts
set delete_flag='Y'
where ref_key in (select part_name from iem_msg_parts where
ref_key=l_RT_MEDIA_ITEM_ID and part_type='ATTACHMAIL');
delete from iem_msg_parts where REF_KEY = l_RT_MEDIA_ITEM_ID;
delete from iem_rt_media_items where RT_MEDIA_ITEM_ID = l_RT_MEDIA_ITEM_ID;
delete from iem_rt_interactions where rt_interaction_id = l_rt_interaction_id;
delete from iem_msg_parts where DELETE_FLAG = 'Y';
delete from iem_rt_interactions WHERE expire='N' and type = 'O'
AND SYSDATE - LAST_UPDATE_DATE > 30;
l_last_updated_by NUMBER;
l_last_update_login NUMBER;
l_last_updated_by :=NVL(to_number(FND_PROFILE.VALUE('USER_ID')),-1);
l_last_update_login := NVL(to_number(FND_PROFILE.VALUE('LOGIN_ID')), -1);
SELECT name INTO x_route_classification
FROM iem_route_classifications
WHERE ROUTE_CLASSIFICATION_ID = l_email_data_rec.RT_CLASSIFICATION_ID;
select IEM_RT_INTERACTIONS_S1.nextval into l_i_sequence from DUAL;
INSERT INTO iem_rt_interactions (
rt_interaction_id, resource_id, customer_id, contact_id, type,
status, expire, created_by, creation_date, last_updated_by,
last_update_date, last_update_login, parent_interaction_id,
service_request_id, inb_tag_id, interaction_id, ih_creator,
lead_id, relationship_id )
VALUES (
l_i_sequence, p_to_resource_id, l_customer_id, l_contact_id,
G_INBOUND, G_WORK_IN_PROGRESS, G_ACTIVE, l_created_by,
SYSDATE, l_last_updated_by, SYSDATE, l_last_update_login,
l_parent_ih_id, l_sr_id, l_tag_id,
l_email_data_rec.IH_INTERACTION_ID, l_ih_creator,
l_lead_id, l_relationship_id
);
select IEM_RT_MEDIA_ITEMS_S1.nextval into l_m_sequence from DUAL;
INSERT INTO iem_rt_media_items (
rt_interaction_id, rt_media_item_id, resource_id,
media_id, message_id, rfc822_message_id, folder_name,
folder_uid, email_account_id, db_server_id, email_type,
status, expire, version, created_by, creation_date,
last_updated_by, last_update_date, last_update_login )
VALUES (
l_i_sequence, l_m_sequence, p_to_resource_id,
l_email_data_rec.IH_MEDIA_ITEM_ID,
l_email_data_rec.MESSAGE_ID,
null,
x_folder_name,
-1,
l_email_data_rec.EMAIL_ACCOUNT_ID,
l_db_server_id,
G_INBOUND, G_UNMOVED, G_ACTIVE,0, l_created_by, SYSDATE,
l_last_updated_by, SYSDATE, l_last_update_login
);
UPDATE IEM_RT_PROC_EMAILS SET queue_status = NULL WHERE message_id = p_message_id;
p_login_id => l_last_update_login,
x_return_status => l_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
x_milcs_id => l_milcs_id,
p_media_lc_rec => l_media_lc_rec);
p_login_id => l_last_update_login,
x_return_status => l_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
x_milcs_id => l_milcs_id,
p_media_lc_rec => l_media_lc_rec);
l_last_updated_by NUMBER;
l_last_update_login NUMBER;
l_last_updated_by :=NVL(to_number(FND_PROFILE.VALUE('USER_ID')),-1);
l_last_update_login := NVL(to_number(FND_PROFILE.VALUE('LOGIN_ID')), -1);
SELECT rt_interaction_id, version, email_type, resource_id
INTO l_rt_interaction_id, l_version, l_email_type, l_resource_id
FROM iem_rt_media_items
WHERE rt_media_item_id = p_rt_media_item_id
AND expire = G_ACTIVE
FOR UPDATE NOWAIT;
SELECT message_id INTO l_message_id FROM iem_rt_media_items
WHERE rt_interaction_id = l_rt_interaction_id
AND email_type = G_INBOUND;
UPDATE iem_rt_media_items
SET expire = G_QUEUEOUT
WHERE rt_interaction_id = l_rt_interaction_id and expire <> G_DORMANT;
UPDATE iem_rt_interactions
SET expire = G_QUEUEOUT,
status = p_status,
customer_id = decode(p_customer_id, G_NUM_NOP, customer_id, p_customer_id),
action_id = decode(l_action_id, null, action_id, l_action_id),
action_item_id = decode(p_activity_type_id,-1, NULL, p_activity_type_id),
result_id = decode(p_result_id,-1, NULL, p_result_id),
outcome_id = decode(p_outcome_id,-1, NULL, p_outcome_id),
reason_id = decode(p_reason_id,-1, NULL, p_reason_id),
to_resource_id = decode(p_to_resource_id, G_NUM_NOP, to_resource_id, p_to_resource_id)
WHERE rt_interaction_id = l_rt_interaction_id
AND expire = G_ACTIVE;
UPDATE IEM_RT_PROC_EMAILS SET queue_status = 'Q'
WHERE message_id = l_message_id;
str := 'SELECT * FROM iem_rt_interactions
WHERE expire = :1 AND last_update_date < sysdate - 0.007 ORDER BY creation_date FOR UPDATE SKIP LOCKED';
str := 'SELECT * FROM iem_rt_interactions
WHERE expire = :1 ORDER BY creation_date FOR UPDATE SKIP LOCKED';
IEM_PARAMETERS_PVT.select_profile(p_api_version_number =>1.0,
P_INIT_MSG_LIST => 'F',
P_COMMIT => 'F',
p_profile_name => 'IEM_OP_MAX_FAIL_RETRIES',
x_profile_value => l_max_try_val,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data );
SELECT rt_media_item_id into l_rt_media_item_id
FROM iem_rt_media_items
WHERE rt_interaction_id = l_rt_ih_data.rt_interaction_id
AND email_type = l_email_type
AND status <> G_NEWREROUTE
AND expire <> G_DORMANT;
SELECT count(outbox_error_id) INTO l_try
FROM iem_outbox_errors
WHERE rt_media_item_id = l_rt_media_item_id;
UPDATE iem_rt_interactions SET expire = G_PROCESSING, last_update_date = SYSDATE
WHERE rt_interaction_id = l_rt_ih_data.rt_interaction_id;
SELECT rt_media_item_id, rfc822_message_id,
folder_name, folder_uid, email_account_id, agent_account_id,
db_server_id, email_type, status, version, expire
INTO x_rt_media_item_id, x_rfc822_message_id,
x_folder_name, x_folder_uid, l_email_account_id, l_agent_account_id,
x_oes_id, x_email_type, x_status, x_version, l_expire
FROM iem_rt_media_items
WHERE rt_interaction_id = l_rt_ih_data.rt_interaction_id
AND email_type = l_email_type
AND status <> G_NEWREROUTE
AND expire <> G_DORMANT;
l_last_updated_by NUMBER;
l_last_update_login NUMBER;
l_last_updated_by :=NVL(to_number(FND_PROFILE.VALUE('USER_ID')),-1);
l_last_update_login := NVL(to_number(FND_PROFILE.VALUE('LOGIN_ID')), -1);
SELECT name INTO l_folder_name
FROM iem_route_classifications
WHERE ROUTE_CLASSIFICATION_ID = l_email_data_rec.RT_CLASSIFICATION_ID;
select IEM_RT_INTERACTIONS_S1.nextval into l_i_sequence from DUAL;
INSERT INTO iem_rt_interactions (
rt_interaction_id, resource_id, customer_id, contact_id, type,
status, expire, created_by, creation_date, last_updated_by,
last_update_date, last_update_login, parent_interaction_id,
service_request_id, inb_tag_id, interaction_id, ih_creator,
lead_id)
VALUES (
l_i_sequence, p_to_resource_id, l_customer_id, l_contact_id,
G_INBOUND, G_WORK_IN_PROGRESS, G_ACTIVE, l_created_by,
SYSDATE, l_last_updated_by, SYSDATE, l_last_update_login,
l_parent_ih_id, l_sr_id, l_tag_id, p_interaction_id, l_ih_creator,
l_lead_id
);
select IEM_RT_MEDIA_ITEMS_S1.nextval into l_m_sequence from DUAL;
INSERT INTO iem_rt_media_items (
rt_interaction_id, rt_media_item_id, resource_id,
media_id, message_id, rfc822_message_id, folder_name,
folder_uid, email_account_id, db_server_id, email_type,
status, expire, version, created_by, creation_date,
last_updated_by, last_update_date, last_update_login )
VALUES (
l_i_sequence, l_m_sequence, p_to_resource_id,
l_email_data_rec.IH_MEDIA_ITEM_ID,
l_email_data_rec.MESSAGE_ID,
null,
l_folder_name,
-1,
l_email_data_rec.EMAIL_ACCOUNT_ID,
l_db_server_id,
G_INBOUND, UPPER(p_reason), G_ACTIVE,0, l_created_by, SYSDATE,
l_last_updated_by, SYSDATE, l_last_update_login
);
l_last_updated_by NUMBER;
l_last_update_login NUMBER;
l_last_updated_by :=NVL(to_number(FND_PROFILE.VALUE('USER_ID')),-1);
l_last_update_login :=NVL(to_number(FND_PROFILE.VALUE('LOGIN_ID')), -1);
select agent_id into l_agent_account_id
from iem_agents where email_account_id = p_email_account_id
and resource_id = p_resource_id;
select user_name into l_user_name from jtf_rs_resource_extns
where resource_id = p_resource_id;
select count(rt_media_item_id) into l_count1 from iem_rt_media_items
where agent_account_id=l_agent_account_id
and rt_interaction_id
in (select rt_interaction_id from iem_rt_interactions where expire <> G_EXPIRE);
select count(rt_media_item_id) into l_count2 from iem_rt_media_items
where resource_id= p_resource_id and email_account_id=p_email_account_id
and rt_interaction_id
in (select rt_interaction_id from iem_rt_interactions where expire <> G_EXPIRE
and type = 'I');
PROCEDURE updateOutboundMessageID(p_api_version_number IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_commit IN VARCHAR2,
p_rt_media_item_id IN NUMBER,
p_message_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) IS
l_api_name VARCHAR2(255);
l_last_updated_by NUMBER;
l_last_update_login NUMBER;
SAVEPOINT updateOutboundMessageID_pvt;
l_api_name :='updateOutboundMessageID';
l_last_updated_by :=NVL(to_number(FND_PROFILE.VALUE('USER_ID')),-1);
l_last_update_login :=NVL(to_number(FND_PROFILE.VALUE('LOGIN_ID')), -1);
update iem_rt_media_items
set message_id = p_message_id
where rt_media_item_id = p_rt_media_item_id;
ROLLBACK TO updateOutboundMessageID_pvt;
ROLLBACK TO updateOutboundMessageID_pvt;
ROLLBACK TO updateOutboundMessageID_pvt;
END updateOutboundMessageID;