[Home] [Help]
PACKAGE BODY: APPS.IEM_AGENT_INBOX_MGMT_PVT
Source
1 package body IEM_AGENT_INBOX_MGMT_PVT as
2 /* $Header: iemvaimb.pls 120.3 2006/05/01 15:19:49 chtang noship $*/
3 G_PKG_NAME varchar2(100):='IEM_AGENT_INBOX_MGMT_PVT';
4 PROCEDURE search_messages_in_inbox (p_api_version_number IN NUMBER,
5 p_init_msg_list IN VARCHAR2 := NULL,
6 p_commit IN VARCHAR2 := NULL,
7 p_email_account_id in number,
8 p_classification_id in number,
9 p_subject in varchar2 :=NULL,
10 p_customer_name in varchar2 :=NULL,
11 p_sender_name in varchar2 :=NULL,
12 p_sent_date_from in varchar2 :=NULL,
13 p_sent_date_to in varchar2 :=NULL,
14 p_sent_date_format in varchar2 :=NULL,
15 p_resource_name in varchar2 :=NULL,
16 p_resource_id in number,
17 p_page_flag in number,
18 p_sort_column IN number:=5,
19 p_sort_state IN varchar2 :=NULL,
20 x_message_tbl out nocopy message_tbl,
21 x_return_status OUT NOCOPY VARCHAR2,
22 x_msg_count OUT NOCOPY NUMBER,
23 x_msg_data OUT NOCOPY VARCHAR2) IS
24
25 l_api_name VARCHAR2(255):='search_messages_in_inbox';
26 l_api_version_number NUMBER:=1.0;
27 Type get_message_rec is REF CURSOR ;
28 email_dtl_cur get_message_rec;
29 l_post_mdts iem_agent_inbox_mgmt_pvt.temp_message_type;
30 l_party_name hz_parties.party_name%type;
31 l_string varchar2(32767):='';
32 l_query_string1 varchar2(15000):='';
33 l_query_string2 varchar2(15000):='';
34 l_sort_column varchar2(500):='received_date'; -- default
35 l_sort_order varchar2(20):='desc'; -- default
36 l_order_by varchar2(500):='';
37 l_classification_string varchar2(1000);
38 l_subject_string varchar2(1000);
39 l_customer_string1 varchar2(1000);
40 l_customer_string2 varchar2(1000);
41 l_sender_string varchar2(1000);
42 l_resource_string varchar2(1000);
43 l_resource_id_string varchar2(100);
44 l_received_date_to_string varchar2(1000);
45 l_received_date_from_string varchar2(1000);
46 l_close_string varchar2(200);
47 l_current_user NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('USER_ID')) ;
48 l_agent_account_id NUMBER;
49
50 l_index number := 1;
51 l_count number;
52
53 l_message_id iem_rt_proc_emails.message_id%type;
54 l_email_account_id iem_rt_proc_emails.email_account_id%type;
55 l_sender_name iem_rt_proc_emails.from_address%type;
56 l_subject iem_rt_proc_emails.subject%type;
57 l_classification_name iem_route_classifications.name%type;
58 l_customer_name hz_parties.party_name%type;
59 l_received_date varchar2(500);
60 l_real_received_date iem_rt_proc_emails.received_date%type;
61 l_message_uid iem_rt_proc_emails.message_id%type;
62 l_resource_name jtf_rs_resource_extns_vl.resource_name%type;
63 l_rt_media_item_id iem_rt_media_items.rt_media_item_id%type;
64 l_agent_id iem_rt_proc_emails.resource_id%type;
65
66 l_cursorID INTEGER;
67 l_dummy INTEGER;
68 BEGIN
69 SAVEPOINT search_message_pvt;
70 -- Standard call to check for call compatibility.
71 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
72 p_api_version_number,
73 l_api_name,
74 G_PKG_NAME)
75 THEN
76 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
77 END IF;
78 -- Initialize message list if p_init_msg_list is set to TRUE.
79 IF FND_API.to_Boolean( p_init_msg_list )
80 THEN
81 FND_MSG_PUB.initialize;
82 END IF;
83
84 -- Initialize API return status to SUCCESS
85 x_return_status := FND_API.G_RET_STS_SUCCESS;
86
87 -- detemine sort column
88 if (p_page_flag = 0) then -- for Inbox by account page
89 if (p_sort_column = 0) then
90 l_sort_column := 'from_address';
91 elsif (p_sort_column = 1) then
92 l_sort_column := 'subject';
93 elsif (p_sort_column = 2) then
94 l_sort_column := 'classification_name';
95 elsif (p_sort_column = 3) then
96 l_sort_column := 'customer_name';
97 elsif (p_sort_column = 4) then
98 l_sort_column := 'resource_name';
99 else
100 l_sort_column := 'real_received_date';
101 end if;
102 else -- for Inbox by agent page
103 if (p_sort_column = 0) then
104 l_sort_column := 'from_address';
105 elsif (p_sort_column = 1) then
106 l_sort_column := 'subject';
107 elsif (p_sort_column = 2) then
108 l_sort_column := 'classification_name';
109 elsif (p_sort_column = 3) then
110 l_sort_column := 'customer_name';
111 else
112 l_sort_column := 'real_received_date';
113 end if;
114
115 end if;
116
117
118 -- determine sort state
119 if (p_sort_state = 'ascending') then
120 l_sort_order := 'desc';
121 else
122 l_sort_order := 'asc';
123 end if;
124
125 if (l_sort_column = 'real_received_date') then
126 l_order_by := ' order by ' || l_sort_column || ' ' || l_sort_order;
127 else
128 l_order_by := ' order by UPPER(' || l_sort_column || ') ' || l_sort_order || ', real_received_date asc';
129 end if;
130
131 if (p_customer_name is not null) then
132
133 l_query_string1 := 'select a.message_id, a.email_account_id, a.from_address, a.subject, b.name as classification_name,
134 c.party_name as customer_name,
135 to_char(a.received_date, ''MM/DD/RRRR HH24:MI:SS'') as received_date, a.received_date as real_received_date, a.message_id,
136 d.resource_name, e.rt_media_item_id, a.resource_id
137 from iem_rt_proc_emails a, iem_route_classifications b, hz_parties c, jtf_rs_resource_extns_vl d, iem_rt_media_items e
138 where a.resource_id <> 0 and a.message_id=e.message_id and e.expire=''N'' and a.email_account_id=:email_account_id
139 and a.rt_classification_id=b.route_classification_id and a.customer_id=c.party_id and a.resource_id=d.resource_id ';
140
141 l_customer_string2 := ' and UPPER(c.party_name) like UPPER(:customer_name)';
142 l_query_string1 := l_query_string1 || l_customer_string2;
143
144 else
145
146 l_query_string1 := 'select a.message_id, a.email_account_id, a.from_address, a.subject, b.name as classification_name,
147 decode(a.customer_id, -1, '''', 0, '''', (select party_name from hz_parties where party_id=a.customer_id) ) as customer_name,
148 to_char(a.received_date, ''MM/DD/RRRR HH24:MI:SS'') as received_date, a.received_date as real_received_date, a.message_id, d.resource_name,
149 e.rt_media_item_id, a.resource_id
150 from iem_rt_proc_emails a, iem_route_classifications b, jtf_rs_resource_extns_vl d, iem_rt_media_items e
151 where a.resource_id <> 0 and a.message_id=e.message_id and e.expire=''N'' and a.email_account_id=:email_account_id
152 and a.rt_classification_id=b.route_classification_id and a.resource_id=d.resource_id ';
153 end if;
154
155 -- detemine query string
156 if (p_classification_id <> -1) then
157 l_classification_string := ' and a.rt_classification_id=:classification_id';
158 l_query_string1 := l_query_string1 || l_classification_string;
159 end if;
160
161 if (p_subject is not null) then
162 l_subject_string := ' and UPPER(a.subject) like UPPER(:subject)';
163 l_query_string1 := l_query_string1 || l_subject_string;
164 end if;
165 if (p_sender_name is not null) then
166 l_sender_string := ' and UPPER(a.from_address) like UPPER(:sender_name)';
167 l_query_string1 := l_query_string1 || l_sender_string;
168 end if;
169 if (p_resource_name is not null) then
170 l_resource_string := ' and UPPER(d.resource_name) like UPPER(:resource_name)';
171 l_query_string1 := l_query_string1 || l_resource_string;
172 end if;
173 if (p_resource_id <> -1) then
174 l_resource_id_string := ' and d.resource_id=:resource_id';
175 l_query_string1 := l_query_string1 || l_resource_id_string;
176 end if;
177 if (p_sent_date_to is not null) then
178 l_received_date_to_string := ' and a.received_date < to_date(:received_date_to, :received_date_format)';
179 l_query_string1 := l_query_string1 || l_received_date_to_string;
180 end if;
181 if (p_sent_date_from is not null) then
182 l_received_date_from_string := ' and a.received_date > to_date(:received_date_from, :received_date_format)';
183 l_query_string1 := l_query_string1 || l_received_date_from_string;
184 end if;
185 l_close_string := l_order_by;
186 l_string := l_query_string1 || l_close_string;
187
188 l_cursorID := DBMS_SQL.OPEN_CURSOR;
189 DBMS_SQL.PARSE(l_cursorID, l_string, DBMS_SQL.V7);
190
191 if (p_classification_id <> -1) then
192 DBMS_SQL.BIND_VARIABLE(l_cursorID, ':classification_id', p_classification_id);
193 end if;
194 if (p_subject is not null) then
195 DBMS_SQL.BIND_VARIABLE(l_cursorID, ':subject', p_subject);
196 end if;
197 if (p_sender_name is not null) then
198 DBMS_SQL.BIND_VARIABLE(l_cursorID, ':sender_name', p_sender_name);
199 end if;
200 if (p_resource_name is not null) then
201 DBMS_SQL.BIND_VARIABLE(l_cursorID, ':resource_name', p_resource_name);
202 end if;
203 if (p_resource_id <> -1) then
204 DBMS_SQL.BIND_VARIABLE(l_cursorID, ':resource_id', p_resource_id);
205 end if;
206 if (p_sent_date_to is not null) then
207 DBMS_SQL.BIND_VARIABLE(l_cursorID, ':received_date_to', p_sent_date_to);
208 DBMS_SQL.BIND_VARIABLE(l_cursorID, ':received_date_format', p_sent_date_format);
209 end if;
210 if (p_sent_date_from is not null) then
211 DBMS_SQL.BIND_VARIABLE(l_cursorID, ':received_date_from', p_sent_date_from);
212 DBMS_SQL.BIND_VARIABLE(l_cursorID, ':received_date_format', p_sent_date_format);
213 end if;
214 if (p_customer_name is not null) then
215 DBMS_SQL.BIND_VARIABLE(l_cursorID, ':customer_name', p_customer_name);
216 end if;
217
218 DBMS_SQL.BIND_VARIABLE(l_cursorID, ':email_account_id', p_email_account_id);
219
220 DBMS_SQL.DEFINE_COLUMN(l_cursorID, 1, l_message_id);
221 DBMS_SQL.DEFINE_COLUMN(l_cursorID, 2, l_email_account_id);
222 DBMS_SQL.DEFINE_COLUMN(l_cursorID, 3, l_sender_name, 256);
223 DBMS_SQL.DEFINE_COLUMN(l_cursorID, 4, l_subject, 240);
224 DBMS_SQL.DEFINE_COLUMN(l_cursorID, 5, l_classification_name, 30);
225 DBMS_SQL.DEFINE_COLUMN(l_cursorID, 6, l_customer_name, 360);
226 DBMS_SQL.DEFINE_COLUMN(l_cursorID, 7, l_received_date, 500);
227 DBMS_SQL.DEFINE_COLUMN(l_cursorID, 8, l_real_received_date);
228 DBMS_SQL.DEFINE_COLUMN(l_cursorID, 9, l_message_uid);
229 DBMS_SQL.DEFINE_COLUMN(l_cursorID, 10, l_resource_name, 360);
230 DBMS_SQL.DEFINE_COLUMN(l_cursorID, 11, l_rt_media_item_id);
231 DBMS_SQL.DEFINE_COLUMN(l_cursorID, 12, l_agent_id);
232
233 l_dummy := DBMS_SQL.EXECUTE(l_cursorID);
234
235 LOOP
236 IF DBMS_SQL.FETCH_ROWS(l_cursorID) = 0 or l_index > 500 THEN
237 EXIT;
238 END IF;
239
240 DBMS_SQL.COLUMN_VALUE(l_cursorID, 1, l_message_id);
241 DBMS_SQL.COLUMN_VALUE(l_cursorID, 2, l_email_account_id);
242 DBMS_SQL.COLUMN_VALUE(l_cursorID, 3, l_sender_name);
243 DBMS_SQL.COLUMN_VALUE(l_cursorID, 4, l_subject);
244 DBMS_SQL.COLUMN_VALUE(l_cursorID, 5, l_classification_name);
245 DBMS_SQL.COLUMN_VALUE(l_cursorID, 6, l_customer_name);
246 DBMS_SQL.COLUMN_VALUE(l_cursorID, 7, l_received_date);
247 DBMS_SQL.COLUMN_VALUE(l_cursorID, 8, l_real_received_date);
248 DBMS_SQL.COLUMN_VALUE(l_cursorID, 9, l_message_uid);
249 DBMS_SQL.COLUMN_VALUE(l_cursorID, 10, l_resource_name);
250 DBMS_SQL.COLUMN_VALUE(l_cursorID, 11, l_rt_media_item_id);
251 DBMS_SQL.COLUMN_VALUE(l_cursorID, 12, l_agent_id);
252
253 x_message_tbl(l_index).message_id := l_message_id;
254 x_message_tbl(l_index).email_account_id := l_email_account_id;
255 x_message_tbl(l_index).sender_name := l_sender_name;
256 x_message_tbl(l_index).subject := l_subject;
257 x_message_tbl(l_index).sent_date :=l_received_date;
258 x_message_tbl(l_index).real_received_date :=l_real_received_date;
259 x_message_tbl(l_index).classification_name := l_classification_name;
260 x_message_tbl(l_index).customer_name := l_customer_name;
261 x_message_tbl(l_index).message_uid := l_message_uid;
262 x_message_tbl(l_index).resource_name := l_resource_name;
263 x_message_tbl(l_index).rt_media_item_id := l_rt_media_item_id;
264 x_message_tbl(l_index).agent_id := l_agent_id;
265
266 --l_current_user := 1001608;
267
268 Begin
269 select a.agent_id into l_agent_account_id from iem_agents a, jtf_rs_resource_extns b
270 where a.resource_id=b.resource_id and b.user_id=l_current_user and a.email_account_id=l_email_account_id;
271 Exception
272 WHEN NO_DATA_FOUND THEN
273 l_agent_account_id := 0;
274 End;
275
276 x_message_tbl(l_index).agent_account_id := l_agent_account_id;
277
278
279 l_index := l_index + 1;
280
281 END LOOP;
282
283 DBMS_SQL.CLOSE_CURSOR(l_cursorID);
284
285 -- Standard Check Of p_commit.
286 IF FND_API.To_Boolean(p_commit) THEN
287 COMMIT WORK;
288 END IF;
289 -- Standard callto get message count and if count is 1, get message info.
290 FND_MSG_PUB.Count_And_Get
291 ( p_count => x_msg_count,
292 p_data => x_msg_data
293 );
294 EXCEPTION
295
296 WHEN FND_API.G_EXC_ERROR THEN
297 ROLLBACK TO search_message_pvt;
298 x_return_status := FND_API.G_RET_STS_ERROR ;
299 FND_MSG_PUB.Count_And_Get
300 ( p_count => x_msg_count,
301 p_data => x_msg_data
302 );
303 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
304 ROLLBACK TO search_message_pvt;
305 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
306 FND_MSG_PUB.Count_And_Get
307 ( p_count => x_msg_count,
308 p_data => x_msg_data
309 );
310 WHEN OTHERS THEN
311 ROLLBACK TO search_message_pvt;
312 x_return_status := FND_API.G_RET_STS_ERROR;
313 IF FND_MSG_PUB.Check_Msg_Level
314 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
315 THEN
316 FND_MSG_PUB.Add_Exc_Msg
317 ( G_PKG_NAME,
318 l_api_name
319 );
320 END IF;
321 FND_MSG_PUB.Count_And_Get
322 (p_count => x_msg_count,
323 p_data => x_msg_data
324 );
325
326 END search_messages_in_inbox;
327
328 PROCEDURE get_total_count_in_inbox (p_api_version_number IN NUMBER,
329 p_init_msg_list IN VARCHAR2 := NULL,
330 p_commit IN VARCHAR2 := NULL,
331 p_email_account_id in number,
332 p_classification_id in number,
333 p_subject in varchar2 :=NULL,
334 p_customer_name in varchar2 :=NULL,
335 p_sender_name in varchar2 :=NULL,
336 p_sent_date_from in varchar2 :=NULL,
337 p_sent_date_to in varchar2 :=NULL,
338 p_sent_date_format in varchar2 :=NULL,
339 p_resource_name in varchar2 :=NULL,
340 p_resource_id in number,
341 x_message_count out NOCOPY number,
342 x_return_status OUT NOCOPY VARCHAR2,
343 x_msg_count OUT NOCOPY NUMBER,
344 x_msg_data OUT NOCOPY VARCHAR2) IS
345
346 l_api_name VARCHAR2(255):='get_total_count_in_inbox';
347 l_api_version_number NUMBER:=1.0;
348 Type get_message_rec is REF CURSOR ;
349 email_dtl_cur get_message_rec;
350 l_post_mdts iem_agent_inbox_mgmt_pvt.temp_message_type;
351 l_classification_name iem_route_classifications.name%type;
352 l_party_name hz_parties.party_name%type;
353 l_resource_name jtf_rs_resource_extns_vl.resource_name%type;
354 l_received_date varchar2(500);
355 l_string varchar2(32767):='';
356 l_query_string1 varchar2(15000):='';
357 l_query_string2 varchar2(15000):='';
358 l_classification_string varchar2(1000);
359 l_subject_string varchar2(1000);
360 l_customer_string1 varchar2(1000);
361 l_customer_string2 varchar2(1000);
362 l_sender_string varchar2(1000);
363 l_resource_string varchar2(1000);
364 l_resource_id_string varchar2(100);
365 l_received_date_to_string varchar2(1000);
366 l_received_date_from_string varchar2(1000);
367 l_close_string varchar2(200);
368 l_current_user NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('USER_ID')) ;
369 l_agent_account_id NUMBER;
370
371 l_index number := 1;
372 l_count number;
373
374 l_message_count number;
375
376 l_cursorID INTEGER;
377 l_dummy INTEGER;
378 BEGIN
379 SAVEPOINT search_message_pvt;
380 -- Standard call to check for call compatibility.
381 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
382 p_api_version_number,
383 l_api_name,
384 G_PKG_NAME)
385 THEN
386 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
387 END IF;
388 -- Initialize message list if p_init_msg_list is set to TRUE.
389 IF FND_API.to_Boolean( p_init_msg_list )
390 THEN
391 FND_MSG_PUB.initialize;
392 END IF;
393
394 -- Initialize API return status to SUCCESS
395 x_return_status := FND_API.G_RET_STS_SUCCESS;
396
397 if (p_customer_name is not null) then
398
399 l_query_string1 := 'select count(*)
400 from iem_rt_proc_emails a, iem_route_classifications b, hz_parties c, jtf_rs_resource_extns_vl d, iem_rt_media_items e
401 where a.resource_id <> 0 and a.message_id=e.message_id and e.expire=''N'' and a.email_account_id=:email_account_id
402 and a.rt_classification_id=b.route_classification_id and a.customer_id=c.party_id and a.resource_id=d.resource_id ';
403
404 l_customer_string2 := ' and UPPER(c.party_name) like UPPER(:customer_name)';
405 l_query_string1 := l_query_string1 || l_customer_string2;
406
407 else
408
409 l_query_string1 := 'select count(*)
410 from iem_rt_proc_emails a, iem_route_classifications b, jtf_rs_resource_extns_vl d, iem_rt_media_items e
411 where a.resource_id <> 0 and a.message_id=e.message_id and e.expire=''N'' and a.email_account_id=:email_account_id
412 and a.rt_classification_id=b.route_classification_id and a.resource_id=d.resource_id ';
413 end if;
414
415 -- detemine query string
416 if (p_classification_id <> -1) then
417 l_classification_string := ' and a.rt_classification_id=:classification_id';
418 l_query_string1 := l_query_string1 || l_classification_string;
419 end if;
420 if (p_subject is not null) then
421 l_subject_string := ' and UPPER(a.subject) like UPPER(:subject)';
422 l_query_string1 := l_query_string1 || l_subject_string;
423 end if;
424 if (p_sender_name is not null) then
425 l_sender_string := ' and UPPER(a.sender_name) like UPPER(:sender_name)';
426 l_query_string1 := l_query_string1 || l_sender_string;
427 end if;
428 if (p_resource_name is not null) then
429 l_resource_string := ' and UPPER(d.resource_name) like UPPER(:resource_name)';
430 l_query_string1 := l_query_string1 || l_resource_string;
431 end if;
432 if (p_resource_id <> -1) then
433 l_resource_id_string := ' and d.resource_id=:resource_id';
434 l_query_string1 := l_query_string1 || l_resource_id_string;
435 end if;
436 if (p_sent_date_to is not null) then
437 l_received_date_to_string := ' and a.received_date < to_date(:received_date_to, :received_date_format)';
438 l_query_string1 := l_query_string1 || l_received_date_to_string;
439 end if;
440 if (p_sent_date_from is not null) then
441 l_received_date_from_string := ' and a.received_date > to_date(:received_date_from, :received_date_format)';
442 l_query_string1 := l_query_string1 || l_received_date_from_string;
443 end if;
444
445 l_string := l_query_string1;
446
447 l_cursorID := DBMS_SQL.OPEN_CURSOR;
448 DBMS_SQL.PARSE(l_cursorID, l_string, DBMS_SQL.V7);
449
450 if (p_classification_id <> -1) then
451 DBMS_SQL.BIND_VARIABLE(l_cursorID, ':classification_id', p_classification_id);
452 end if;
453 if (p_subject is not null) then
454 DBMS_SQL.BIND_VARIABLE(l_cursorID, ':subject', p_subject);
455 end if;
456 if (p_sender_name is not null) then
457 DBMS_SQL.BIND_VARIABLE(l_cursorID, ':sender_name', p_sender_name);
458 end if;
459 if (p_resource_name is not null) then
460 DBMS_SQL.BIND_VARIABLE(l_cursorID, ':resource_name', p_resource_name);
461 end if;
462 if (p_resource_id <> -1) then
463 DBMS_SQL.BIND_VARIABLE(l_cursorID, ':resource_id', p_resource_id);
464 end if;
465 if (p_sent_date_to is not null) then
466 DBMS_SQL.BIND_VARIABLE(l_cursorID, ':received_date_to', p_sent_date_to);
467 DBMS_SQL.BIND_VARIABLE(l_cursorID, ':received_date_format', p_sent_date_format);
468 end if;
469 if (p_sent_date_from is not null) then
470 DBMS_SQL.BIND_VARIABLE(l_cursorID, ':received_date_from', p_sent_date_from);
471 DBMS_SQL.BIND_VARIABLE(l_cursorID, ':received_date_format', p_sent_date_format);
472 end if;
473 if (p_customer_name is not null) then
474 DBMS_SQL.BIND_VARIABLE(l_cursorID, ':customer_name', p_customer_name);
475 end if;
476
477 DBMS_SQL.BIND_VARIABLE(l_cursorID, ':email_account_id', p_email_account_id);
478
479 DBMS_SQL.DEFINE_COLUMN(l_cursorID, 1, l_message_count);
480
481 l_dummy := DBMS_SQL.EXECUTE(l_cursorID);
482
483 IF DBMS_SQL.FETCH_ROWS(l_cursorID) <> 0 THEN
484 DBMS_SQL.COLUMN_VALUE(l_cursorID, 1, l_message_count);
485 x_message_count := l_message_count;
486 END IF;
487
488 DBMS_SQL.CLOSE_CURSOR(l_cursorID);
489
490 -- Standard Check Of p_commit.
491 IF FND_API.To_Boolean(p_commit) THEN
492 COMMIT WORK;
493 END IF;
494 -- Standard callto get message count and if count is 1, get message info.
495 FND_MSG_PUB.Count_And_Get
496 ( p_count => x_msg_count,
497 p_data => x_msg_data
498 );
499 EXCEPTION
500
501 WHEN FND_API.G_EXC_ERROR THEN
502 ROLLBACK TO search_message_pvt;
503 x_return_status := FND_API.G_RET_STS_ERROR ;
504 FND_MSG_PUB.Count_And_Get
505 ( p_count => x_msg_count,
506 p_data => x_msg_data
507 );
508 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
509 ROLLBACK TO search_message_pvt;
510 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
511 FND_MSG_PUB.Count_And_Get
512 ( p_count => x_msg_count,
513 p_data => x_msg_data
514 );
515 WHEN OTHERS THEN
516 ROLLBACK TO search_message_pvt;
517 x_return_status := FND_API.G_RET_STS_ERROR;
518 IF FND_MSG_PUB.Check_Msg_Level
519 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
520 THEN
521 FND_MSG_PUB.Add_Exc_Msg
522 ( G_PKG_NAME,
523 l_api_name
524 );
525 END IF;
526 FND_MSG_PUB.Count_And_Get
527 (p_count => x_msg_count,
528 p_data => x_msg_data
529 );
530
531 END get_total_count_in_inbox;
532
533 PROCEDURE show_agent_list (p_api_version_number IN NUMBER,
534 p_init_msg_list IN VARCHAR2 := NULL,
535 p_commit IN VARCHAR2 := NULL,
536 p_email_account_id in number,
537 p_sort_column IN number,
538 p_sort_state IN varchar2,
539 p_resource_role IN number :=1,
540 p_resource_name IN varchar2 := null,
541 p_transferrer_id IN number :=-1,
542 x_resource_count out nocopy resource_count_tbl,
543 x_return_status OUT NOCOPY VARCHAR2,
544 x_msg_count OUT NOCOPY NUMBER,
545 x_msg_data OUT NOCOPY VARCHAR2) IS
546
547 l_api_name VARCHAR2(255):='show_agent_list';
548 l_api_version_number NUMBER:=1.0;
549 l_index number := 1;
550 l_count number;
551 l_resource_name varchar2(720);
552 l_resource_role varchar2(720);
553 l_last_login_time varchar2(500);
554 l_real_last_login_time date;
555 l_resource_id number;
556 l_fetched_emails number;
557 l_string varchar2(32767):='';
558 l_string1 varchar2(10000):='';
559 l_string2 varchar2(10000):='';
560 l_string3 varchar2(10000):='';
561 l_string11 varchar2(1000):='';
562 l_where_clause varchar2(32767):='';
563 l_order_by varchar2(500):='';
564 l_sort_column varchar2(500):='resource_name'; -- default
565 l_sort_order varchar2(20):='asc'; -- default
566 Type get_message_rec is REF CURSOR ;
567 email_dtl_cur get_message_rec;
568 l_cursorID INTEGER;
569 l_dummy INTEGER;
570
571 BEGIN
572
573 SAVEPOINT show_agent_list_pvt;
574 -- Standard call to check for call compatibility.
575 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
576 p_api_version_number,
577 l_api_name,
578 G_PKG_NAME)
579 THEN
580 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
581 END IF;
582 -- Initialize message list if p_init_msg_list is set to TRUE.
583 IF FND_API.to_Boolean( p_init_msg_list )
584 THEN
585 FND_MSG_PUB.initialize;
586 END IF;
587
588 -- Initialize API return status to SUCCESS
589 x_return_status := FND_API.G_RET_STS_SUCCESS;
590
591 -- determine sort state
592 if (p_sort_state = 'ascending') then
593 l_sort_order := 'desc';
594 else
595 l_sort_order := 'asc';
596 end if;
597
598 /* if p_resource_role = 2 then
599 l_resource_role := 23720; -- Supervisor
600 else
601 l_resource_role := 23107; -- Agent
602 end if;
603 */
604
605 if p_resource_role = 2 then
606 l_resource_role := 'EMAIL_CENTER_SUPERVISOR'; -- Supervisor
607 else
608 l_resource_role := 'IEM_SA_AGENT'; -- Agent
609 end if;
610
611 if (p_resource_name is not null) then
612
613 l_where_clause := ' and (upper(rs.source_last_name) like upper(:resource_name) or upper(rs.source_first_name) like upper(:resource_name) or upper(rs.user_name) like upper(:resource_name))';
614 end if;
615
616 if (p_sort_column = 2) then
617
618 l_string1 := 'select resource_id, resource_name, last_login_time, real_last_login_time from (
619 select a.resource_id, concat(concat(rs.source_last_name, '', ''), rs.source_first_name) as resource_name,
620 to_char(max(c.begin_date_time), ''MM/DD/RRRR HH24:MI:SS'') as last_login_time, max(c.begin_date_time) as real_last_login_time
621 from iem_agents a, jtf_rs_resource_extns rs, ieu_sh_sessions c,
622 fnd_responsibility resp, fnd_user_resp_groups respgrp, fnd_user fu
623 where a.resource_id = rs.resource_id and a.resource_id=c.resource_id
624 and a.email_account_id =:email_account_id and c.application_id=680
625 and rs.user_id=respgrp.user_id and respgrp.user_id=fu.user_id
626 and respgrp.responsibility_id=resp.responsibility_id and resp.application_id=680
627 and resp.responsibility_key = :resource_role
628 and trunc(sysdate) between trunc(nvl(respgrp.start_date, sysdate))
629 and trunc(nvl(respgrp.end_date, sysdate))
630 and trunc(sysdate) between trunc(nvl(rs.start_date_active, sysdate))
631 and trunc(nvl(rs.end_date_active, sysdate))
632 and trunc(sysdate) between trunc(nvl(fu.start_date, sysdate))
633 and trunc(nvl(fu.end_date, sysdate))
634 and rs.resource_id in ( select unique rel.role_resource_id from jtf_rs_role_relations rel where
635 rel.role_id in (28, 29, 30) and rel.delete_flag = ''N''
636 and rel.role_resource_type = ''RS_INDIVIDUAL''
637 and trunc(sysdate) between trunc(nvl(rel.start_date_active, sysdate))
638 and trunc(nvl(rel.end_date_active, sysdate)) ) ';
639
640 l_string2 := ' group by a.resource_id, rs.source_last_name, rs.source_first_name
641 union all
642 select a.resource_id, concat(concat(rs.source_last_name, '', ''), rs.source_first_name) as resource_name
643 , '''' as last_login_time, to_date('''', ''dd-mon-yy'') as real_last_login_time
644 from iem_agents a, jtf_rs_resource_extns rs,
645 fnd_responsibility resp, fnd_user_resp_groups respgrp, fnd_user fu
646 where a.resource_id = rs.resource_id and a.email_account_id = :email_account_id
647 and rs.user_id=respgrp.user_id and respgrp.user_id=fu.user_id
648 and respgrp.responsibility_id=resp.responsibility_id and resp.application_id=680
649 and resp.responsibility_key = :resource_role
650 and trunc(sysdate) between trunc(nvl(respgrp.start_date, sysdate))
651 and trunc(nvl(respgrp.end_date, sysdate))
652 and trunc(sysdate) between trunc(nvl(rs.start_date_active, sysdate))
653 and trunc(nvl(rs.end_date_active, sysdate))
654 and trunc(sysdate) between trunc(nvl(fu.start_date, sysdate))
655 and trunc(nvl(fu.end_date, sysdate))
656 and rs.resource_id in ( select unique rel.role_resource_id from jtf_rs_role_relations rel where
657 rel.role_id in (28, 29, 30) and rel.delete_flag = ''N''
658 and rel.role_resource_type = ''RS_INDIVIDUAL''
659 and trunc(sysdate) between trunc(nvl(rel.start_date_active, sysdate))
660 and trunc(nvl(rel.end_date_active, sysdate)) ) ';
661
662 l_string3 := ' and a.resource_id not in
663 (select a.resource_id from iem_agents a, ieu_sh_sessions b where b.application_id=680 and
664 a.resource_id=b.resource_id and a.email_account_id =:email_account_id ) ) order by real_last_login_time ';
665
666 if (p_transferrer_id <> -1) then
667 -- l_string11 := ' and a.resource_id <> ' || p_transferrer_id;
668 l_string11 := ' and a.resource_id <> :transferrer_id ';
669 l_string := l_string1 || l_string11 || l_where_clause || l_string2 || l_string11 || l_where_clause || l_string3 || l_sort_order;
670 else
671 l_string := l_string1 || l_where_clause || l_string2 || l_where_clause || l_string3 || l_sort_order;
672 end if;
673
674 l_cursorID := DBMS_SQL.OPEN_CURSOR;
675 DBMS_SQL.PARSE(l_cursorID, l_string, DBMS_SQL.V7);
676
677 if (p_resource_name is not null) then
678 DBMS_SQL.BIND_VARIABLE(l_cursorID, ':resource_name', p_resource_name);
679 end if;
680
681 DBMS_SQL.BIND_VARIABLE(l_cursorID, ':email_account_id', p_email_account_id);
682 DBMS_SQL.BIND_VARIABLE(l_cursorID, ':resource_role', l_resource_role);
683
684 if (p_transferrer_id <> -1) then
685 DBMS_SQL.BIND_VARIABLE(l_cursorID, ':transferrer_id', p_transferrer_id);
686 end if;
687
688 DBMS_SQL.DEFINE_COLUMN(l_cursorID, 1, l_resource_id);
689 DBMS_SQL.DEFINE_COLUMN(l_cursorID, 2, l_resource_name, 720);
690 DBMS_SQL.DEFINE_COLUMN(l_cursorID, 3, l_last_login_time, 500);
691 DBMS_SQL.DEFINE_COLUMN(l_cursorID, 4, l_real_last_login_time);
692
693
694 l_dummy := DBMS_SQL.EXECUTE(l_cursorID);
695
696 LOOP
697 IF DBMS_SQL.FETCH_ROWS(l_cursorID) = 0 THEN
698 EXIT;
699 END IF;
700
701 DBMS_SQL.COLUMN_VALUE(l_cursorID, 1, l_resource_id);
702 DBMS_SQL.COLUMN_VALUE(l_cursorID, 2, l_resource_name);
703 DBMS_SQL.COLUMN_VALUE(l_cursorID, 3, l_last_login_time);
704 DBMS_SQL.COLUMN_VALUE(l_cursorID, 4, l_real_last_login_time);
705
706 select count(*) into l_fetched_emails from iem_rt_proc_emails a
707 where a.email_account_id=p_email_account_id and a.queue_status is null and resource_id = l_resource_id;
708
709 x_resource_count(l_index).resource_id :=l_resource_id;
710 x_resource_count(l_index).resource_name := l_resource_name;
711 x_resource_count(l_index).email_count := l_fetched_emails;
712 x_resource_count(l_index).last_login_time := l_last_login_time;
713
714 l_index := l_index + 1;
715
716 END LOOP;
717
718 DBMS_SQL.CLOSE_CURSOR(l_cursorID);
719
720 else
721 -- p_sort_column=0 or 1
722
723 if (p_sort_column = 1) then
724 l_sort_column := 'fetched_emails';
725 else
726 l_sort_column := 'resource_name';
727 end if;
728
729 if (l_sort_column = 'fetched_emails') then
730 l_order_by := ' order by ' || l_sort_column || ' ' || l_sort_order;
731 else
732 l_order_by := ' order by UPPER(' || l_sort_column || ') ' || l_sort_order;
733 end if;
734
735 l_string1 := 'select resource_id, fetched_emails, resource_name from
736 ( SELECT agact.resource_id, count(*) fetched_emails, concat(concat(rs.source_last_name, '',''), rs.source_first_name) as resource_name
737 from IEM_AGENTS agact, iem_rt_proc_emails pm,
738 jtf_rs_resource_extns rs, fnd_responsibility resp,fnd_user_resp_groups respgrp, fnd_user fu
739 WHERE pm.resource_id=agact.resource_id
740 and agact.resource_id = rs.resource_id and rs.user_id=respgrp.user_id and respgrp.user_id=fu.user_id
741 and pm.email_account_id=agact.email_account_id and pm.queue_status is null
742 and agact.email_account_id=:email_account_id
743 and respgrp.responsibility_id=resp.responsibility_id and resp.application_id=680
744 and resp.responsibility_key = :resource_role
745 and trunc(sysdate) between trunc(nvl(respgrp.start_date, sysdate))
746 and trunc(nvl(respgrp.end_date, sysdate))
747 and trunc(sysdate) between trunc(nvl(rs.start_date_active, sysdate))
748 and trunc(nvl(rs.end_date_active, sysdate))
749 and trunc(sysdate) between trunc(nvl(fu.start_date, sysdate))
750 and trunc(nvl(fu.end_date, sysdate))
751 and rs.resource_id in ( select unique rel.role_resource_id from jtf_rs_role_relations rel where
752 rel.role_id in (28, 29, 30) and rel.delete_flag = ''N''
753 and rel.role_resource_type = ''RS_INDIVIDUAL''
754 and trunc(sysdate) between trunc(nvl(rel.start_date_active, sysdate))
755 and trunc(nvl(rel.end_date_active, sysdate)) ) ';
756
757 l_string2 := 'group by agact.resource_id, rs.source_last_name, rs.source_first_name
758 union all SELECT agact.resource_id, 0, concat(concat(rs.source_last_name, '', ''), rs.source_first_name) as resource_name
759 from IEM_AGENTS agact, jtf_rs_resource_extns rs, fnd_responsibility resp,fnd_user_resp_groups respgrp, fnd_user fu
760 WHERE agact.resource_id = rs.resource_id and rs.user_id=respgrp.user_id and respgrp.user_id=fu.user_id
761 and agact.email_account_id=:email_account_id
762 and respgrp.responsibility_id=resp.responsibility_id and resp.application_id=680
763 and resp.responsibility_key = :resource_role
764 and trunc(sysdate) between trunc(nvl(respgrp.start_date, sysdate))
765 and trunc(nvl(respgrp.end_date, sysdate))
766 and trunc(sysdate) between trunc(nvl(rs.start_date_active, sysdate))
767 and trunc(nvl(rs.end_date_active, sysdate))
768 and trunc(sysdate) between trunc(nvl(fu.start_date, sysdate))
769 and trunc(nvl(fu.end_date, sysdate))
770 and rs.resource_id in ( select unique rel.role_resource_id from jtf_rs_role_relations rel where
771 rel.role_id in (28, 29, 30) and rel.delete_flag = ''N''
772 and rel.role_resource_type = ''RS_INDIVIDUAL''
773 and trunc(sysdate) between trunc(nvl(rel.start_date_active, sysdate))
774 and trunc(nvl(rel.end_date_active, sysdate)) ) ';
775
776 l_string3 := ' and agact.resource_id not in (select pm.resource_id from iem_rt_proc_emails pm where pm.email_account_id=:email_account_id and pm.queue_status is null) ) ';
777
778 if (p_transferrer_id <> -1) then
779 -- l_string11 := ' and agact.resource_id <> ' || p_transferrer_id;
780 l_string11 := ' and agact.resource_id <> :transferrer_id ';
781 l_string := l_string1 || l_string11 || l_where_clause || l_string2 || l_string11 || l_where_clause || l_string3 || l_order_by;
782 else
783 l_string := l_string1 || l_where_clause || l_string2 || l_where_clause || l_string3 || l_order_by;
784 end if;
785
786 l_cursorID := DBMS_SQL.OPEN_CURSOR;
787 DBMS_SQL.PARSE(l_cursorID, l_string, DBMS_SQL.V7);
788
789 if (p_resource_name is not null) then
790 DBMS_SQL.BIND_VARIABLE(l_cursorID, ':resource_name', p_resource_name);
791 end if;
792
793 DBMS_SQL.BIND_VARIABLE(l_cursorID, ':email_account_id', p_email_account_id);
794 DBMS_SQL.BIND_VARIABLE(l_cursorID, ':resource_role', l_resource_role);
795
796 if (p_transferrer_id <> -1) then
797 DBMS_SQL.BIND_VARIABLE(l_cursorID, ':transferrer_id', p_transferrer_id);
798 end if;
799
800 DBMS_SQL.DEFINE_COLUMN(l_cursorID, 1, l_resource_id);
801 DBMS_SQL.DEFINE_COLUMN(l_cursorID, 2, l_fetched_emails);
802 DBMS_SQL.DEFINE_COLUMN(l_cursorID, 3, l_resource_name, 720);
803
804 l_dummy := DBMS_SQL.EXECUTE(l_cursorID);
805
806 LOOP
807 IF DBMS_SQL.FETCH_ROWS(l_cursorID) = 0 THEN
808 EXIT;
809 END IF;
810
811 DBMS_SQL.COLUMN_VALUE(l_cursorID, 1, l_resource_id);
812 DBMS_SQL.COLUMN_VALUE(l_cursorID, 2, l_fetched_emails);
813 DBMS_SQL.COLUMN_VALUE(l_cursorID, 3, l_resource_name);
814
815 select to_char(max(begin_date_time), 'MM/DD/RRRR HH24:MI:SS') into l_last_login_time
816 from ieu_sh_sessions where application_id=680 and resource_id=l_resource_id;
817
818 x_resource_count(l_index).resource_id :=l_resource_id;
819 x_resource_count(l_index).resource_name := l_resource_name;
820 x_resource_count(l_index).email_count := l_fetched_emails;
821 x_resource_count(l_index).last_login_time := l_last_login_time;
822
823 l_index := l_index + 1;
824
825 END LOOP;
826
827 DBMS_SQL.CLOSE_CURSOR(l_cursorID);
828
829 end if; -- if p_sort_column=2
830 -- Standard Check Of p_commit.
831 IF FND_API.To_Boolean(p_commit) THEN
832 COMMIT WORK;
833 END IF;
834 -- Standard callto get message count and if count is 1, get message info.
835 FND_MSG_PUB.Count_And_Get
836 ( p_count => x_msg_count,
837 p_data => x_msg_data
838 );
839 EXCEPTION
840
841 WHEN FND_API.G_EXC_ERROR THEN
842 ROLLBACK TO show_agent_list_pvt;
843 x_return_status := FND_API.G_RET_STS_ERROR ;
844 FND_MSG_PUB.Count_And_Get
845 ( p_count => x_msg_count,
846 p_data => x_msg_data
847 );
848 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
849 ROLLBACK TO show_agent_list_pvt;
850 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
851 FND_MSG_PUB.Count_And_Get
852 ( p_count => x_msg_count,
853 p_data => x_msg_data
854 );
855 WHEN OTHERS THEN
856 ROLLBACK TO show_agent_list_pvt;
857 x_return_status := FND_API.G_RET_STS_ERROR;
858 IF FND_MSG_PUB.Check_Msg_Level
859 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
860 THEN
861 FND_MSG_PUB.Add_Exc_Msg
862 ( G_PKG_NAME,
863 l_api_name
864 );
865 END IF;
866 FND_MSG_PUB.Count_And_Get
867 (p_count => x_msg_count,
868 p_data => x_msg_data
869 );
870
871
872 END show_agent_list;
873
874 end IEM_AGENT_INBOX_MGMT_PVT ;