[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT a.EMAIL_ACCOUNT_ID,a.RT_CLASSIFICATION_ID,
b.USER_NAME,c.name,count(*) Total,
nvl(max(sysdate-a.received_date)*24*60,0) wait_time
FROM iem_rt_proc_emails a,iem_mstemail_accounts b,
iem_route_classifications c,iem_agents d
WHERE a.resource_id=0
and a.email_account_id=b.email_account_id
and a.rt_classification_id=c.route_classification_id
AND a.email_account_id=d.email_account_id
AND d.resource_id=p_resource_id
AND a.group_id in (select * from TABLE(cast(i_tbl as jtf_number_table)))
and a.message_id not in (select message_id from iem_reroute_hists where agent_id=p_resource_id)
GROUP by a.email_account_id,a.rt_classification_id,b.USER_NAME,c.name;
SELECT a.EMAIL_ACCOUNT_ID,a.RT_CLASSIFICATION_ID,
b.USER_NAME,c.name,count(*) Total,
nvl(max(sysdate-a.received_date)*24*60,0) wait_time
FROM iem_rt_proc_emails a,iem_mstemail_accounts b,
iem_route_classifications c,iem_agents d
WHERE a.resource_id=0
and a.email_account_id=b.email_account_id
and a.rt_classification_id=c.route_classification_id
AND a.email_account_id=d.email_account_id
AND d.resource_id=p_resource_id
AND (a.group_id in (select group_id from jtf_rs_group_members where resource_id=p_resource_id
and delete_flag<>'Y')
or (a.group_id=0))
and a.message_id not in (select message_id from iem_reroute_hists where agent_id=p_resource_id)
GROUP by a.email_account_id,a.rt_classification_id,b.USER_NAME,c.name;
select a.email_account_id,a.rt_classification_id,
b.USER_NAME,c.name,Count(*) Total,
nvl(max(sysdate-a.received_date)*24*60,0) wait_time,
max(decode(a.mail_item_status,'A',1,'N',1,'T',1,0)) email_status
FROM iem_rt_proc_emails a,iem_mstemail_accounts b,
iem_route_classifications c
WHERE a.resource_id=p_resource_id
and a.email_account_id=b.email_account_id
and a.rt_classification_id=c.route_classification_id
and a.queue_status is null
GROUP by a.email_account_id,a.rt_classification_id,b.USER_NAME,c.name;
SAVEPOINT select_mail_count_pvt;
select count(*) into l_count
from jtf_rs_group_members
where resource_id=p_resource_id
and delete_flag<>'Y';
ROLLBACK TO select_mail_count_PVT;
ROLLBACK TO select_mail_count_PVT;
ROLLBACK TO select_mail_count_PVT;
ROLLBACK TO select_mail_count_PVT;
ROLLBACK TO select_mail_count_PVT;
SELECT a.RT_CLASSIFICATION_ID,b.name,COUNT(*) TOTAL
FROM iem_rt_proc_emails a,iem_route_classifications b
where a.email_account_id=p_email_account_id
and a.resource_id =0
and a.rt_classification_id=b.route_classification_id
AND a.group_id in (select * from TABLE(cast(i_tbl as jtf_number_table)))
GROUP by a.rt_classification_id,b.name;
SAVEPOINT select_item_PVT;
ROLLBACK TO select_item_PVT;
ROLLBACK TO select_item_PVT;
ROLLBACK TO select_item_PVT;
ROLLBACK TO select_item_PVT;
cursor c2 is select a.rt_classification_id,b.name,count(*) total
from iem_rt_proc_emails a,iem_route_classifications b
where a.email_account_id=p_email_account_id
and a.resource_id=0
and a.rt_classification_id=b.route_classification_id
group by rt_classification_id,b.name;
SAVEPOINT select_item_PVT;
ROLLBACK TO select_item_PVT;
ROLLBACK TO select_item_PVT;
ROLLBACK TO select_item_PVT;
ROLLBACK TO select_item_PVT;
SAVEPOINT select_item_PVT;
select COUNT(*)
INTO x_count
from iem_rt_proc_emails
where email_account_id=p_email_account_id
and rt_classification_id=p_classification_id
and resource_id=0
and group_id in (select * from TABLE(cast(i_tbl as jtf_number_table)));
ROLLBACK TO select_item_PVT;
ROLLBACK TO select_item_PVT;
ROLLBACK TO select_item_PVT;
ROLLBACK TO select_item_PVT;
SAVEPOINT select_data_PVT;
'select /*FIRST_ROWS*/ * from iem_rt_proc_emails p where message_id not in (select message_id from iem_reroute_hists where resource_id=:res1 ) and email_account_id=:id
and rt_classification_id=:rt
and resource_id=0
and ( p.group_id = 0
or exists (
select null
from jtf_rs_group_members gm
where resource_id=:res
and gm.group_id = p.group_id
and delete_flag <>''Y''
)
)
order by received_date for update skip locked'
using p_resource_id,p_account_id,p_rt_classification,p_resource_id;
'select /*FIRST_ROWS*/ * from iem_rt_proc_emails p where message_id not in (select message_id from iem_reroute_hists where resource_id=:res1 ) and email_account_id=:id
and resource_id=0
and ( p.group_id = 0
or exists (
select null
from jtf_rs_group_members gm
where resource_id=:res
and gm.group_id = p.group_id
and delete_flag <>''Y''
)
)
order by received_date for update skip locked'
using p_resource_id,p_account_id,p_resource_id;
l_tag_key_value.delete;
UPDATE iem_rt_proc_emails
set resource_id=p_resource_id,
queue_status='G'
where message_id=x_email_data.message_id ;
JTF_IH_PUB.Update_Interaction( p_api_version => 1.0,
p_resp_appl_id => TO_NUMBER(FND_PROFILE.VALUE('RESP_APPL_ID')),
p_resp_id => TO_NUMBER(FND_PROFILE.VALUE('RESP_ID')),
p_user_id =>nvl(TO_NUMBER (FND_PROFILE.VALUE('USER_ID')),-1),
p_login_id =>TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID')),
x_return_status => l_ret_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_interaction_rec => l_interaction_rec
);
ROLLBACK TO select_data_PVT;
ROLLBACK TO select_data_PVT;
ROLLBACK TO select_data_PVT;
ROLLBACK TO select_data_PVT;
SELECT IH_MEDIA_ITEM_ID into l_media_id
FROM iem_rt_proc_emails
WHERE MESSAGE_ID=p_message_id;
SELECT * into l_media_data
FROM JTF_IH_MEDIA_ITEMS
WHERE MEDIA_ID=l_media_id;
DELETE FROM iem_rt_proc_emails
WHERE MESSAGE_ID=p_message_id;
delete from iem_reroute_hists
where message_id=p_message_id;
delete from iem_kb_results where message_id=p_message_id;
delete from iem_email_classifications where message_id=p_message_id;
delete from iem_comp_rt_stats where type='WORKFLOW' and param=to_char(p_message_id);
delete from iem_encrypted_tags
where message_id=p_message_id;
select group_id bulk collect into x_tbl
from jtf_rs_group_members
where resource_id=p_resource_id
and delete_flag<>'Y';
PROCEDURE UpdateMailItem (p_api_version_number IN NUMBER,
p_init_msg_list IN VARCHAR2 ,
p_commit IN VARCHAR2 ,
p_email_data in iem_rt_proc_emails%rowtype,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2) IS
l_api_name VARCHAR2(255):='UpdateMailItem';
l_LAST_UPDATED_BY NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('USER_ID')) ;
l_LAST_UPDATE_DATE DATE:=SYSDATE;
l_LAST_UPDATE_LOGIN NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('LOGIN_ ID')) ;
SAVEPOINT update_item_PVT;
UPDATE iem_rt_proc_emails
SET
resource_id =p_email_data.resource_id,
PRIORITY =p_email_data.priority,
MSG_STATUS =p_email_data.msg_status,
SUBJECT =p_email_data.subject,
SENT_DATE =p_email_data.sent_date,
CUSTOMER_ID =p_email_data.customer_id,
CONTACT_ID =p_email_data.CONTACT_ID,
RELATIONSHIP_ID =p_email_data.RELATIONSHIP_ID,
RECEIVED_DATE =p_email_data.received_date,
MAIL_ITEM_STATUS =p_email_data.mail_item_status,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = decode(l_LAST_UPDATED_BY,null,-1,l_LAST_UPDATED_BY),
LAST_UPDATE_LOGIN =decode(l_LAST_UPDATE_LOGIN,null,-1,l_LAST_UPDATE_LOGIN),
ATTRIBUTE1 =p_email_data.attribute1,
ATTRIBUTE2 =p_email_data.attribute2,
ATTRIBUTE3 =p_email_data.attribute3,
ATTRIBUTE4 = p_email_data.attribute4,
ATTRIBUTE5 = p_email_data.attribute5,
ATTRIBUTE6 = p_email_data.attribute6,
ATTRIBUTE7 = p_email_data.attribute7,
ATTRIBUTE8 = p_email_data.attribute8,
ATTRIBUTE9 = p_email_data.attribute9,
ATTRIBUTE10 =p_email_data.attribute10,
ATTRIBUTE11 = p_email_data.attribute11,
ATTRIBUTE12 = p_email_data.attribute12,
ATTRIBUTE13 = p_email_data.attribute13,
ATTRIBUTE14 = p_email_data.attribute14,
ATTRIBUTE15 = p_email_data.attribute15
WHERE message_id=p_email_data.message_id;
ROLLBACK TO update_item_PVT;
ROLLBACK TO update_item_PVT;
ROLLBACK TO update_item_PVT;
END UpdateMailItem;
SELECT *
INTO x_email_data
FROM iem_rt_proc_emails
WHERE message_id=p_message_id;
SELECT *
INTO x_email_data
FROM iem_rt_proc_emails
WHERE message_id=p_message_id;
select decode(p_sort_order,0,'ASC','DESC')
into l_sort_order
from dual;
'SELECT a.message_id,a.rt_classification_id,c.name,b.rt_media_item_id,
b.rt_interaction_id,
a.email_account_id,a.message_flag,a.from_address,a.subject,a.priority,a.msg_status,
to_char(to_date(substr(a.sent_Date,1,20),''DD-MON-YYYY HH24:MI:SS''),''MM/DD/RRRR HH24:MI:SS''),a.mail_item_status,
-- to_char(to_date(substr(a.sent_Date,5,length(a.sent_Date)-13)||substr(a.sent_date,25,4),''Mon DD hh24:mi:ssyyyy''),''MM/DD/RRRR HH24:MI:SS''),a.mail_item_status,
a.from_resource_id,
decode(a.mail_item_status,''R'',1,''S'',1,0) read_status,d.description
FROM iem_rt_proc_emails a,
IEM_RT_MEDIA_ITEMS b,
IEM_ROUTE_CLASSIFICATIONS c,
FND_LOOKUPS d
WHERE A.RT_CLASSIFICATION_ID=C.ROUTE_CLASSIFICATION_ID AND B.EXPIRE=:expire AND A.MESSAGE_ID=B.MESSAGE_ID and a.resource_id=:id and a.email_account_id=:account_id and
substr(a.mail_item_status,1,1)=d.lookup_code and d.lookup_type=:status_type '||l_order_by
using l_expire,p_resource_id,p_email_account_id,l_status_type;
l_temp_tbl.delete;
IF l_temp_tbl.count>0 THEN -- Data Selected Now implement Display Logic
x_total_message:=l_temp_tbl.count;
SAVEPOINT select_data_PVT;
SELECT * INTO x_email_data
FROM iem_rt_proc_emails
WHERE message_id=p_message_id
AND resource_id=0 for update;
update iem_rt_proc_emails
set from_resource_id=p_from_agent_id,
resource_id=p_to_agent_id,
mail_item_status=p_mail_item_status
where message_id=p_message_id;
l_tag_key_value.delete;
ROLLBACK TO select_data_PVT;
ROLLBACK TO select_data_PVT;
ROLLBACK TO select_data_PVT;
ROLLBACK TO select_data_PVT;
insert_arch_dtl_error EXCEPTION;
cursor c1 is select a.classification,b.score from
iem_classifications a,iem_email_classifications b
where b.message_id=p_message_id
and a.classification_id=b.classification_id
order by score asc;
SAVEPOINT select_data_PVT;
select * into l_msg_rec from iem_rt_proc_emails
where message_id=p_message_id;
SELECT * into l_media_data
FROM JTF_IH_MEDIA_ITEMS
WHERE MEDIA_ID=l_msg_rec.ih_media_item_id;
select * into l_header_rec from iem_ms_base_headers
where message_id=p_message_id;
select value into l_msg_text from iem_ms_msgbodys
where message_id=p_message_id and rownum=1;
-- Insert Record into IEM_ARCH_MSG_DTLS
IEM_ARCH_MSGDTLS_PVT.create_item(
P_API_VERSION_NUMBER=>1.0,
P_INIT_MSG_LIST=>'F',
P_COMMIT=>'F',
P_message_id=>p_message_id,
p_inbound_message_id=>null,
P_EMAIL_ACCOUNT_ID=>l_msg_rec.email_account_id,
P_MAILPROC_STATUS=>p_action_flag,
P_RT_CLASSIFICATION_ID=>l_msg_rec.rt_classification_id,
P_MAIL_TYPE=>0,
P_FROM_STR=>l_header_rec.from_str,
P_REPLY_TO_STR=>l_header_rec.reply_to_str,
P_TO_STR=>l_header_rec.to_str,
P_CC_STR=>l_header_rec.cc_str,
P_BCC_STR=>null,
P_SENT_DATE=>l_msg_rec.sent_date,
P_RECEIVED_DATE=>l_msg_rec.received_date,
P_SUBJECT=>l_msg_rec.subject,
P_AGENT_ID=>l_msg_rec.resource_id,
P_GROUP_ID=>l_msg_rec.group_id,
P_IH_MEDIA_ITEM_ID=>l_msg_rec.ih_media_item_id,
P_CUSTOMER_ID=>l_msg_rec.customer_id,
P_MESSAGE_SIZE=>null,
P_CONTACT_ID=>l_msg_rec.contact_id,
P_RELATIONSHIP_ID=>l_msg_rec.relationship_id,
P_TOP_INTENT=>l_top_intent,
P_MESSAGE_TEXT=>l_msg_text,
p_ATTRIBUTE1 =>null,
p_ATTRIBUTE2 =>null,
p_ATTRIBUTE3 =>null,
p_ATTRIBUTE4 =>null,
p_ATTRIBUTE5 =>null,
p_ATTRIBUTE6 =>null,
p_ATTRIBUTE7 =>null,
p_ATTRIBUTE8 =>null,
p_ATTRIBUTE9 =>null,
p_ATTRIBUTE10 =>null,
p_ATTRIBUTE11 =>null,
p_ATTRIBUTE12 =>null,
p_ATTRIBUTE13 =>null,
p_ATTRIBUTE14 =>null,
p_ATTRIBUTE15 =>null,
x_message_id=>l_out_message_id,
X_RETURN_STATUS=>l_ret_status,
X_MSG_COUNT=>l_msg_count,
X_MSG_DATA=>l_msg_data);
raise insert_arch_dtl_error;
-- Delete All RUN TIME DATA and MESSAGE DATA FROM PRIMARY STORE
delete from iem_rt_proc_emails where message_id=p_message_id;
delete from iem_email_classifications where message_id=p_message_id;
delete from iem_kb_results where message_id=p_message_id;
delete from iem_ms_base_headers where message_id=p_message_id;
delete from iem_ms_msgbodys where message_id=p_message_id;
delete from iem_ms_msgparts where message_id=p_message_id;
delete from iem_ms_exthdrs where message_id=p_message_id;
-- Insert the MIME Message into Archived Message Stores
insert into iem_arch_msgs(message_id,message_content,created_by,creation_date,last_updated_by,last_update_date,last_update_login)
(
select message_id,mime_msg,created_by,creation_date,last_updated_by,last_update_date,last_update_login from iem_ms_mimemsgs where message_id=p_message_id and draft_flag=0);
delete from iem_ms_mimemsgs where message_id=p_message_id;