[Home] [Help]
PACKAGE BODY: APPS.IEM_QUEUE_MANAGEMENT_PVT
Source
1 package body IEM_QUEUE_MANAGEMENT_PVT as
2 /* $Header: iemvqumb.pls 120.4.12010000.2 2008/10/16 09:23:46 shramana ship $*/
3 G_PKG_NAME varchar2(100):='IEM_QUEUE_MANAGEMENT_PVT';
4 PROCEDURE search_messages_in_queue (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_group_id in number,
16 p_sort_column IN number:=5,
17 p_sort_state IN varchar2 :=NULL,
18 x_message_tbl out nocopy message_tbl,
19 x_return_status OUT NOCOPY VARCHAR2,
20 x_msg_count OUT NOCOPY NUMBER,
21 x_msg_data OUT NOCOPY VARCHAR2) IS
22
23 l_api_name VARCHAR2(255):='search_messages_in_queue';
24 l_api_version_number NUMBER:=1.0;
25 Type get_message_rec is REF CURSOR ;
26 email_dtl_cur get_message_rec;
27 l_post_mdts iem_queue_management_pvt.temp_message_type;
28 l_party_name hz_parties.party_name%type;
29 l_string varchar2(32767):='';
30 l_query_string1 varchar2(15000):='';
31 l_query_string2 varchar2(15000):='';
32 l_sort_column varchar2(500):='received_date'; -- default
33 l_sort_order varchar2(20):='desc'; -- default
34 l_order_by varchar2(500):='';
35 l_classification_string varchar2(1000);
36 l_subject_string varchar2(1000);
37 l_customer_string1 varchar2(1000);
38 l_customer_string2 varchar2(1000);
39 l_sender_string varchar2(1000);
40 l_received_date_to_string varchar2(1000);
41 l_received_date_from_string varchar2(1000);
42 l_close_string varchar2(200);
43 l_current_user NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('USER_ID')) ;
44 l_agent_account_id NUMBER;
45 l_all_groups varchar2(400);
46
47 l_index number := 1;
48 l_count number;
49
50 l_message_id iem_rt_proc_emails.message_id%type;
51 l_email_account_id iem_rt_proc_emails.email_account_id%type;
52 l_sender_name iem_rt_proc_emails.from_address%type;
53 l_subject iem_rt_proc_emails.subject%type;
54 l_classification_name iem_route_classifications.name%type;
55 l_customer_name hz_parties.party_name%type;
56 l_received_date varchar2(500);
57 l_real_received_date iem_rt_proc_emails.received_date%type;
58 l_message_uid iem_rt_proc_emails.message_id%type;
59 l_group_name jtf_rs_groups_tl.group_name%type;
60
61 l_cursorID INTEGER;
62 l_dummy INTEGER;
63 BEGIN
64 SAVEPOINT search_message_pvt;
65 -- Standard call to check for call compatibility.
66 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
67 p_api_version_number,
68 l_api_name,
69 G_PKG_NAME)
70 THEN
71 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
72 END IF;
73 -- Initialize message list if p_init_msg_list is set to TRUE.
74 IF FND_API.to_Boolean( p_init_msg_list )
75 THEN
76 FND_MSG_PUB.initialize;
77 END IF;
78
79 -- Initialize API return status to SUCCESS
80 x_return_status := FND_API.G_RET_STS_SUCCESS;
81
82 -- detemine sort column
83 if (p_sort_column = 0) then
84 l_sort_column := 'from_address';
85 elsif (p_sort_column = 1) then
86 l_sort_column := 'subject';
87 elsif (p_sort_column = 2) then
88 l_sort_column := 'classification_name';
89 elsif (p_sort_column = 3) then
90 l_sort_column := 'customer_name';
91 elsif (p_sort_column = 4) then
92 l_sort_column := 'group_name';
93 else
94 l_sort_column := 'real_received_date';
95 end if;
96
97
98 -- determine sort state
99 if (p_sort_state = 'ascending') then
100 l_sort_order := 'desc';
101 else
102 l_sort_order := 'asc';
103 end if;
104
105 if (l_sort_column = 'real_received_date') then
106 l_order_by := ' order by ' || l_sort_column || ' ' || l_sort_order;
107 else
108 l_order_by := ' order by UPPER(' || l_sort_column || ') ' || l_sort_order || ', real_received_date asc';
109 end if;
110
111 l_all_groups := FND_MESSAGE.GET_STRING('IEM', 'IEM_ALL_GROUPS');
112
113 if (p_customer_name is not null and p_group_id = 0) then
114
115 l_query_string1 := 'select a.message_id, a.email_account_id, a.from_address, a.subject, b.name as classification_name,
116 c.party_name as customer_name,
117 to_char(a.received_date, ''MM/DD/RRRR HH24:MI:SS'') as received_date, a.received_date as real_received_date, a.message_id,
118 replace(a.group_id, a.group_id, :all_groups) as group_name
119 from iem_rt_proc_emails a, iem_route_classifications b, hz_parties c
120 where a.resource_id = 0 and a.email_account_id=:email_account_id
121 and a.rt_classification_id=b.route_classification_id and a.customer_id=c.party_id and a.group_id=0 ';
122
123 l_customer_string2 := ' and UPPER(c.party_name) like UPPER(:customer_name)';
124 l_query_string1 := l_query_string1 || l_customer_string2;
125
126 elsif (p_customer_name is not null and p_group_id <> -1) then
127
128 l_query_string1 := 'select a.message_id, a.email_account_id, a.from_address, a.subject, b.name as classification_name,
129 c.party_name as customer_name, 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.group_name
130 from iem_rt_proc_emails a, iem_route_classifications b, hz_parties c, jtf_rs_groups_tl d
131 where a.resource_id = 0 and a.email_account_id=:email_account_id
132 and a.rt_classification_id=b.route_classification_id and a.customer_id=c.party_id
133 and a.group_id=d.group_id and d.language=userenv(''LANG'') and d.group_id=:group_id';
134
135 l_customer_string2 := ' and UPPER(c.party_name) like UPPER(:customer_name)';
136 l_query_string1 := l_query_string1 || l_customer_string2;
137
138 elsif (p_customer_name is not null and p_group_id = -1) then
139
140 l_query_string1 := 'select a.message_id, a.email_account_id, a.from_address, a.subject, b.name as classification_name,
141 c.party_name as customer_name,
142 to_char(a.received_date, ''MM/DD/RRRR HH24:MI:SS'') as received_date, a.received_date as real_received_date, a.message_id,
143 decode(a.group_id, 0, :all_groups, ( select group_name from jtf_rs_groups_tl where language=userenv(''LANG'') and group_id= a.group_id) ) as group_name
144 from iem_rt_proc_emails a, iem_route_classifications b, hz_parties c
145 where a.resource_id = 0 and a.email_account_id=:email_account_id
146 and a.rt_classification_id=b.route_classification_id and a.customer_id=c.party_id ';
147
148 l_customer_string2 := ' and UPPER(c.party_name) like UPPER(:customer_name)';
149 l_query_string1 := l_query_string1 || l_customer_string2;
150
151 elsif (p_customer_name is null and p_group_id = 0) then
152
153 l_query_string1 := 'select a.message_id, a.email_account_id, a.from_address, a.subject, b.name as classification_name,
154 decode(a.customer_id, -1, '''', 0, '''', (select party_name from hz_parties where party_id=a.customer_id) ) as customer_name,
155 to_char(a.received_date, ''MM/DD/RRRR HH24:MI:SS'') as received_date, a.received_date as real_received_date, a.message_id,
156 replace(a.group_id, a.group_id, :all_groups) as group_name
157 from iem_rt_proc_emails a, iem_route_classifications b
158 where a.resource_id = 0 and a.email_account_id=:email_account_id
159 and a.rt_classification_id=b.route_classification_id
160 and a.group_id=0';
161 elsif (p_customer_name is null and p_group_id <> -1) then
162
163 l_query_string1 := 'select a.message_id, a.email_account_id, a.from_address, a.subject, b.name as classification_name,
164 decode(a.customer_id, -1, '''', 0, '''', (select party_name from hz_parties where party_id=a.customer_id) ) as customer_name,
165 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.group_name
166 from iem_rt_proc_emails a, iem_route_classifications b, jtf_rs_groups_tl d
167 where a.resource_id = 0 and a.email_account_id=:email_account_id
168 and a.rt_classification_id=b.route_classification_id
169 and a.group_id=d.group_id and d.language=userenv(''LANG'') and d.group_id=:group_id';
170 else
171
172 l_query_string1 := 'select a.message_id, a.email_account_id, a.from_address, a.subject, b.name as classification_name,
173 decode(a.customer_id, -1, '''', 0, '''', (select party_name from hz_parties where party_id=a.customer_id) ) as customer_name,
174 to_char(a.received_date, ''MM/DD/RRRR HH24:MI:SS'') as received_date, a.received_date as real_received_date, a.message_id,
175 decode(a.group_id, 0, :all_groups, ( select group_name from jtf_rs_groups_tl where language=userenv(''LANG'') and group_id= a.group_id) ) as group_name
176 from iem_rt_proc_emails a, iem_route_classifications b
177 where a.resource_id = 0 and a.email_account_id=:email_account_id
178 and a.rt_classification_id=b.route_classification_id';
179 end if;
180
181 -- detemine query string
182 if (p_classification_id <> -1) then
183
184 l_classification_string := ' and a.rt_classification_id=:classification_id';
185 l_query_string1 := l_query_string1 || l_classification_string;
186 end if;
187 if (p_subject is not null) then
188
189 l_subject_string := ' and UPPER(a.subject) like UPPER(:subject)';
190 l_query_string1 := l_query_string1 || l_subject_string;
191 end if;
192 if (p_sender_name is not null) then
193
194 l_sender_string := ' and UPPER(a.from_address) like UPPER(:sender_name)';
195 l_query_string1 := l_query_string1 || l_sender_string;
196 end if;
197 if (p_sent_date_to is not null) then
198
199 l_received_date_to_string := ' and a.received_date < to_date(:received_date_to, :received_date_format)';
200 l_query_string1 := l_query_string1 || l_received_date_to_string;
201 end if;
202 if (p_sent_date_from is not null) then
203
204 l_received_date_from_string := ' and a.received_date > to_date(:received_date_from, :received_date_format)';
205 l_query_string1 := l_query_string1 || l_received_date_from_string;
206 end if;
207
208 l_close_string := l_order_by;
209 l_string := l_query_string1 || l_close_string;
210
211 l_cursorID := DBMS_SQL.OPEN_CURSOR;
212 DBMS_SQL.PARSE(l_cursorID, l_string, DBMS_SQL.native);
213
214 if (p_classification_id <> -1) then
215 DBMS_SQL.BIND_VARIABLE(l_cursorID, ':classification_id', p_classification_id);
216 end if;
217 if (p_subject is not null) then
218 DBMS_SQL.BIND_VARIABLE(l_cursorID, ':subject', p_subject);
219 end if;
220 if (p_sender_name is not null) then
221 DBMS_SQL.BIND_VARIABLE(l_cursorID, ':sender_name', p_sender_name);
222 end if;
223 if (p_sent_date_to is not null) then
224 DBMS_SQL.BIND_VARIABLE(l_cursorID, ':received_date_to', p_sent_date_to);
225 DBMS_SQL.BIND_VARIABLE(l_cursorID, ':received_date_format', p_sent_date_format);
226 end if;
227 if (p_sent_date_from is not null) then
228 DBMS_SQL.BIND_VARIABLE(l_cursorID, ':received_date_from', p_sent_date_from);
229 DBMS_SQL.BIND_VARIABLE(l_cursorID, ':received_date_format', p_sent_date_format);
230 end if;
231 if (p_customer_name is not null) then
232 DBMS_SQL.BIND_VARIABLE(l_cursorID, ':customer_name', p_customer_name);
233 end if;
234 if (p_group_id <> -1 and p_group_id <> 0) then
235 DBMS_SQL.BIND_VARIABLE(l_cursorID, ':group_id', p_group_id);
236 else
237 DBMS_SQL.BIND_VARIABLE(l_cursorID, ':all_groups', l_all_groups);
238 end if;
239
240 DBMS_SQL.BIND_VARIABLE(l_cursorID, ':email_account_id', p_email_account_id);
241
242 DBMS_SQL.DEFINE_COLUMN(l_cursorID, 1, l_message_id);
243 DBMS_SQL.DEFINE_COLUMN(l_cursorID, 2, l_email_account_id);
244 DBMS_SQL.DEFINE_COLUMN(l_cursorID, 3, l_sender_name, 256);
245 DBMS_SQL.DEFINE_COLUMN(l_cursorID, 4, l_subject, 2000);
246 DBMS_SQL.DEFINE_COLUMN(l_cursorID, 5, l_classification_name, 30);
247 DBMS_SQL.DEFINE_COLUMN(l_cursorID, 6, l_customer_name, 360);
248 DBMS_SQL.DEFINE_COLUMN(l_cursorID, 7, l_received_date, 500);
249 DBMS_SQL.DEFINE_COLUMN(l_cursorID, 8, l_real_received_date);
250 DBMS_SQL.DEFINE_COLUMN(l_cursorID, 9, l_message_uid);
251 DBMS_SQL.DEFINE_COLUMN(l_cursorID, 10, l_group_name, 60);
252
253 l_dummy := DBMS_SQL.EXECUTE(l_cursorID);
254
255
256 LOOP
257 -- IF (DBMS_SQL.FETCH_ROWS(l_cursorID) = 0 or l_index >500) THEN
258 IF (DBMS_SQL.FETCH_ROWS(l_cursorID) = 0) THEN
259 EXIT;
260 END IF;
261
262 DBMS_SQL.COLUMN_VALUE(l_cursorID, 1, l_message_id);
263 DBMS_SQL.COLUMN_VALUE(l_cursorID, 2, l_email_account_id);
264 DBMS_SQL.COLUMN_VALUE(l_cursorID, 3, l_sender_name);
265 DBMS_SQL.COLUMN_VALUE(l_cursorID, 4, l_subject);
266 DBMS_SQL.COLUMN_VALUE(l_cursorID, 5, l_classification_name);
267 DBMS_SQL.COLUMN_VALUE(l_cursorID, 6, l_customer_name);
268 DBMS_SQL.COLUMN_VALUE(l_cursorID, 7, l_received_date);
269 DBMS_SQL.COLUMN_VALUE(l_cursorID, 8, l_real_received_date);
270 DBMS_SQL.COLUMN_VALUE(l_cursorID, 9, l_message_uid);
271 DBMS_SQL.COLUMN_VALUE(l_cursorID, 10, l_group_name);
272
273 x_message_tbl(l_index).message_id := l_message_id;
274 x_message_tbl(l_index).email_account_id := l_email_account_id;
275 x_message_tbl(l_index).sender_name := l_sender_name;
276 x_message_tbl(l_index).subject := l_subject;
277 x_message_tbl(l_index).sent_date :=l_received_date;
278 x_message_tbl(l_index).real_received_date :=l_real_received_date;
279 x_message_tbl(l_index).classification_name := l_classification_name;
280 x_message_tbl(l_index).customer_name := l_customer_name;
281 x_message_tbl(l_index).message_uid := l_message_uid;
282 x_message_tbl(l_index).group_name := l_group_name;
283
284 -- l_current_user := 1000691;
285
286 Begin
287 select a.agent_id into l_agent_account_id from iem_agents a, jtf_rs_resource_extns b
288 where a.resource_id=b.resource_id and b.user_id=l_current_user and a.email_account_id=l_post_mdts.email_account_id;
289 Exception
290 WHEN NO_DATA_FOUND THEN
291 l_agent_account_id := 0;
292 End;
293
294 x_message_tbl(l_index).agent_account_id := l_agent_account_id;
295
296 l_index := l_index + 1;
297
298
299 END LOOP;
300
301 DBMS_SQL.CLOSE_CURSOR(l_cursorID);
302
303 -- Standard Check Of p_commit.
304 IF FND_API.To_Boolean(p_commit) THEN
305 COMMIT WORK;
306 END IF;
307 -- Standard callto get message count and if count is 1, get message info.
308 FND_MSG_PUB.Count_And_Get
309 ( p_count => x_msg_count,
310 p_data => x_msg_data
311 );
312 EXCEPTION
313
314 WHEN FND_API.G_EXC_ERROR THEN
315 ROLLBACK TO search_message_pvt;
316 x_return_status := FND_API.G_RET_STS_ERROR ;
317 FND_MSG_PUB.Count_And_Get
318 ( p_count => x_msg_count,
319 p_data => x_msg_data
320 );
321 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
322 ROLLBACK TO search_message_pvt;
323 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
324 FND_MSG_PUB.Count_And_Get
325 ( p_count => x_msg_count,
326 p_data => x_msg_data
327 );
328 WHEN OTHERS THEN
329 ROLLBACK TO search_message_pvt;
330 x_return_status := FND_API.G_RET_STS_ERROR;
331 IF FND_MSG_PUB.Check_Msg_Level
332 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
333 THEN
334 FND_MSG_PUB.Add_Exc_Msg
335 ( G_PKG_NAME,
336 l_api_name
337 );
338 END IF;
339 FND_MSG_PUB.Count_And_Get
340 (p_count => x_msg_count,
341 p_data => x_msg_data
342 );
343
344 END search_messages_in_queue;
345
346 PROCEDURE get_total_count_in_queue (p_api_version_number IN NUMBER,
347 p_init_msg_list IN VARCHAR2 := NULL,
348 p_commit IN VARCHAR2 := NULL,
349 p_email_account_id in number,
350 p_classification_id in number,
351 p_subject in varchar2 :=NULL,
352 p_customer_name in varchar2 :=NULL,
353 p_sender_name in varchar2 :=NULL,
354 p_sent_date_from in varchar2 :=NULL,
355 p_sent_date_to in varchar2 :=NULL,
356 p_sent_date_format in varchar2 :=NULL,
357 p_group_id in number,
358 x_message_count out NOCOPY number,
359 x_return_status OUT NOCOPY VARCHAR2,
360 x_msg_count OUT NOCOPY NUMBER,
361 x_msg_data OUT NOCOPY VARCHAR2) IS
362
363 l_api_name VARCHAR2(255):='get_total_count_in_queue';
364 l_api_version_number NUMBER:=1.0;
365 Type get_message_rec is REF CURSOR ;
366 email_dtl_cur get_message_rec;
367 l_post_mdts iem_queue_management_pvt.temp_message_type;
368 l_classification_name iem_route_classifications.name%type;
369 l_party_name hz_parties.party_name%type;
370 l_received_date varchar2(500);
371 l_string varchar2(32767):='';
372 l_query_string1 varchar2(15000):='';
373 l_query_string2 varchar2(15000):='';
374 l_classification_string varchar2(1000);
375 l_subject_string varchar2(1000);
376 l_customer_string1 varchar2(1000);
377 l_customer_string2 varchar2(1000);
378 l_sender_string varchar2(1000);
379 l_received_date_to_string varchar2(1000);
380 l_received_date_from_string varchar2(1000);
381 l_close_string varchar2(200);
382 l_current_user NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('USER_ID')) ;
383 l_agent_account_id NUMBER;
384 l_message_count NUMBER;
385
386 l_index number := 1;
387 l_count number;
388 l_subject varchar2(240);
389 l_customer_name varchar2(360);
390 l_sender_name varchar2(128);
391
392 l_cursorID INTEGER;
393 l_dummy INTEGER;
394 BEGIN
395
396 SAVEPOINT get_total_count_pvt;
397 -- Standard call to check for call compatibility.
398 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
399 p_api_version_number,
400 l_api_name,
401 G_PKG_NAME)
402 THEN
403 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
404 END IF;
405 -- Initialize message list if p_init_msg_list is set to TRUE.
406 IF FND_API.to_Boolean( p_init_msg_list )
407 THEN
408 FND_MSG_PUB.initialize;
409 END IF;
410
411 -- Initialize API return status to SUCCESS
412 x_return_status := FND_API.G_RET_STS_SUCCESS;
413
414 if (p_customer_name is not null and p_group_id <> -1) then
418 and a.rt_classification_id=b.route_classification_id and a.customer_id=c.party_id
415 l_query_string1 := 'select count(*)
416 from iem_rt_proc_emails a, iem_route_classifications b, hz_parties c
417 where a.resource_id = 0 and a.email_account_id=:email_account_id
419 and a.group_id=:group_id';
420
421 l_customer_string2 := ' and UPPER(c.party_name) like UPPER(:customer_name)';
422 l_query_string1 := l_query_string1 || l_customer_string2;
423
424 elsif (p_customer_name is not null and p_group_id = -1) then
425 l_query_string1 := 'select count(*)
426 from iem_rt_proc_emails a, iem_route_classifications b, hz_parties c
427 where a.resource_id = 0 and a.email_account_id=:email_account_id
428 and a.rt_classification_id=b.route_classification_id and a.customer_id=c.party_id ';
429
430 l_customer_string2 := ' and UPPER(c.party_name) like UPPER(:customer_name)';
431 l_query_string1 := l_query_string1 || l_customer_string2;
432
433 elsif (p_customer_name is null and p_group_id <> -1) then
434 l_query_string1 := 'select count(*)
435 from iem_rt_proc_emails a, iem_route_classifications b
436 where a.resource_id = 0 and a.email_account_id=:email_account_id
437 and a.rt_classification_id=b.route_classification_id
438 and a.group_id=:group_id';
439 else
440 l_query_string1 := 'select count(*)
441 from iem_rt_proc_emails a, iem_route_classifications b
442 where a.resource_id = 0 and a.email_account_id=:email_account_id
443 and a.rt_classification_id=b.route_classification_id';
444 end if;
445
446 -- detemine query string
447 if (p_classification_id <> -1) then
448 l_classification_string := ' and a.rt_classification_id=:classification_id';
449 l_query_string1 := l_query_string1 || l_classification_string;
450 end if;
451 if (p_subject is not null) then
452 l_subject_string := ' and UPPER(a.subject) like UPPER(:subject)';
453 l_query_string1 := l_query_string1 || l_subject_string;
454 end if;
455 if (p_sender_name is not null) then
456 l_sender_string := ' and UPPER(a.from_address) like UPPER(:sender_name)';
457 l_query_string1 := l_query_string1 || l_sender_string;
458 end if;
459 if (p_sent_date_to is not null) then
460 l_received_date_to_string := ' and a.received_date < to_date(:received_date_to, :received_date_format)';
461 l_query_string1 := l_query_string1 || l_received_date_to_string;
462 end if;
463 if (p_sent_date_from is not null) then
464 l_received_date_from_string := ' and a.received_date > to_date(:received_date_from, :received_date_format)';
465 l_query_string1 := l_query_string1 || l_received_date_from_string;
466 end if;
467
468 l_string := l_query_string1;
469
470 l_cursorID := DBMS_SQL.OPEN_CURSOR;
471 DBMS_SQL.PARSE(l_cursorID, l_string, DBMS_SQL.native);
472
473 if (p_classification_id <> -1) then
474 DBMS_SQL.BIND_VARIABLE(l_cursorID, ':classification_id', p_classification_id);
475 end if;
476 if (p_subject is not null) then
477 DBMS_SQL.BIND_VARIABLE(l_cursorID, ':subject', p_subject);
478 end if;
479 if (p_sender_name is not null) then
480 DBMS_SQL.BIND_VARIABLE(l_cursorID, ':sender_name', p_sender_name);
481 end if;
482 if (p_sent_date_to is not null) then
483 DBMS_SQL.BIND_VARIABLE(l_cursorID, ':received_date_to', p_sent_date_to);
484 DBMS_SQL.BIND_VARIABLE(l_cursorID, ':received_date_format', p_sent_date_format);
485 end if;
486 if (p_sent_date_from is not null) then
487 DBMS_SQL.BIND_VARIABLE(l_cursorID, ':received_date_from', p_sent_date_from);
488 DBMS_SQL.BIND_VARIABLE(l_cursorID, ':received_date_format', p_sent_date_format);
489 end if;
490 if (p_customer_name is not null) then
491 DBMS_SQL.BIND_VARIABLE(l_cursorID, ':customer_name', p_customer_name);
492 end if;
493 if (p_group_id <> -1) then
494 DBMS_SQL.BIND_VARIABLE(l_cursorID, ':group_id', p_group_id);
495 end if;
496
497 DBMS_SQL.BIND_VARIABLE(l_cursorID, ':email_account_id', p_email_account_id);
498
499 DBMS_SQL.DEFINE_COLUMN(l_cursorID, 1, l_message_count);
500
501 l_dummy := DBMS_SQL.EXECUTE(l_cursorID);
502
503 IF DBMS_SQL.FETCH_ROWS(l_cursorID) <> 0 THEN
504 DBMS_SQL.COLUMN_VALUE(l_cursorID, 1, l_message_count);
505 x_message_count := l_message_count;
506 END IF;
507
508
509 DBMS_SQL.CLOSE_CURSOR(l_cursorID);
510 -- Standard Check Of p_commit.
511 IF FND_API.To_Boolean(p_commit) THEN
512 COMMIT WORK;
513 END IF;
514 -- Standard callto get message count and if count is 1, get message info.
515 FND_MSG_PUB.Count_And_Get
516 ( p_count => x_msg_count,
517 p_data => x_msg_data
518 );
519 EXCEPTION
520
521 WHEN FND_API.G_EXC_ERROR THEN
522 ROLLBACK TO get_total_count_pvt;
523 x_return_status := FND_API.G_RET_STS_ERROR ;
524 FND_MSG_PUB.Count_And_Get
525 ( p_count => x_msg_count,
526 p_data => x_msg_data
527 );
528 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
529 ROLLBACK TO get_total_count_pvt;
530 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
531 FND_MSG_PUB.Count_And_Get
532 ( p_count => x_msg_count,
533 p_data => x_msg_data
534 );
535 WHEN OTHERS THEN
536 ROLLBACK TO get_total_count_pvt;
537 x_return_status := FND_API.G_RET_STS_ERROR;
538 IF FND_MSG_PUB.Check_Msg_Level
539 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
540 THEN
541 FND_MSG_PUB.Add_Exc_Msg
545 END IF;
542 ( G_PKG_NAME,
543 l_api_name
544 );
546 FND_MSG_PUB.Count_And_Get
547 (p_count => x_msg_count,
548 p_data => x_msg_data
549 );
550
551 END get_total_count_in_queue;
552
553
554
555 PROCEDURE show_agent_list (p_api_version_number IN NUMBER,
556 p_init_msg_list IN VARCHAR2 := NULL,
557 p_commit IN VARCHAR2 := NULL,
558 p_email_account_id in number,
559 p_sort_column IN number,
560 p_sort_state IN varchar2,
561 x_resource_count out nocopy resource_count_tbl,
562 x_return_status OUT NOCOPY VARCHAR2,
563 x_msg_count OUT NOCOPY NUMBER,
564 x_msg_data OUT NOCOPY VARCHAR2) IS
565
566 l_api_name VARCHAR2(255):='show_agent_list';
567 l_api_version_number NUMBER:=1.0;
568 l_index number := 1;
569 l_count number;
570 l_resource_name varchar2(720);
571 l_last_login_time varchar2(500);
572 l_real_last_login_time date;
573 l_resource_id number;
574 l_fetched_emails number;
575 l_string varchar2(32767):='';
576 l_string2 varchar2(32767):='';
577 l_order_by varchar2(500):='';
578 l_sort_column varchar2(500):='resource_name'; -- default
579 l_sort_order varchar2(20):='asc'; -- default
580 Type get_message_rec is REF CURSOR ;
581 email_dtl_cur get_message_rec;
582
583 l_cursorID INTEGER;
584 l_dummy INTEGER;
585
586 BEGIN
587
588 SAVEPOINT show_agent_list_pvt;
589 -- Standard call to check for call compatibility.
590 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
591 p_api_version_number,
592 l_api_name,
593 G_PKG_NAME)
594 THEN
595 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
596 END IF;
597 -- Initialize message list if p_init_msg_list is set to TRUE.
598 IF FND_API.to_Boolean( p_init_msg_list )
599 THEN
600 FND_MSG_PUB.initialize;
601 END IF;
602
603 -- Initialize API return status to SUCCESS
604 x_return_status := FND_API.G_RET_STS_SUCCESS;
605
606 -- determine sort state
607 if (p_sort_state = 'ascending') then
608 l_sort_order := 'desc';
609 else
610 l_sort_order := 'asc';
611 end if;
612
613 l_cursorID := DBMS_SQL.OPEN_CURSOR;
614
615 if (p_sort_column = 2) then
616
617 l_string := 'select resource_id, resource_name, last_login_time, real_last_login_time from (
618 select a.resource_id, concat(concat(rs.source_last_name, '', ''), rs.source_first_name) as resource_name,
619 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
620 from iem_agents a, jtf_rs_resource_extns rs, ieu_sh_sessions c
621 where a.resource_id = rs.resource_id and a.resource_id=c.resource_id
622 and a.email_account_id =:email_account_id and c.application_id=680
623 and trunc(sysdate) between trunc(nvl(rs.start_date_active, sysdate))
624 and trunc(nvl(rs.end_date_active, sysdate))
625 and rs.user_id in
626 ( select respgrp.user_id from fnd_user_resp_groups respgrp, fnd_user fu, fnd_responsibility resp
627 where respgrp.user_id = fu.user_id
628 and respgrp.responsibility_id=resp.responsibility_id and resp.application_id=680
629 and (resp.responsibility_key = ''EMAIL_CENTER_SUPERVISOR'' or resp.responsibility_key = ''IEM_SA_AGENT'')
630 and trunc(sysdate) between trunc(nvl(respgrp.start_date, sysdate))
631 and trunc(nvl(respgrp.end_date, sysdate))
632 and trunc(sysdate) between trunc(nvl(fu.start_date, sysdate))
633 and trunc(nvl(fu.end_date, sysdate))
634 )
635 and rs.resource_id in ( select unique rel.role_resource_id from jtf_rs_role_relations rel where
636 rel.role_id in (28, 29, 30) and rel.delete_flag = ''N''
637 and rel.role_resource_type = ''RS_INDIVIDUAL''
638 and trunc(sysdate) between trunc(nvl(rel.start_date_active, sysdate))
639 and trunc(nvl(rel.end_date_active, sysdate)) ) ';
640
641 l_string2 := ' group by a.resource_id, rs.source_last_name, rs.source_first_name
642 union all
643 select a.resource_id, concat(concat(rs.source_last_name, '', ''), rs.source_first_name) as resource_name
644 , '''' as last_login_time, to_date('''', ''dd-mon-yy'') as real_last_login_time
645 from iem_agents a, jtf_rs_resource_extns rs
646 where a.resource_id = rs.resource_id and a.email_account_id = :email_account_id
647 and trunc(sysdate) between trunc(nvl(rs.start_date_active, sysdate))
648 and trunc(nvl(rs.end_date_active, sysdate))
649 and rs.user_id in
650 ( select respgrp.user_id from fnd_user_resp_groups respgrp, fnd_user fu, fnd_responsibility resp
651 where respgrp.user_id = fu.user_id
652 and respgrp.responsibility_id=resp.responsibility_id and resp.application_id=680
653 and (resp.responsibility_key = ''EMAIL_CENTER_SUPERVISOR'' or resp.responsibility_key = ''IEM_SA_AGENT'')
654 and trunc(sysdate) between trunc(nvl(respgrp.start_date, sysdate))
655 and trunc(nvl(respgrp.end_date, sysdate))
656 and trunc(sysdate) between trunc(nvl(fu.start_date, sysdate))
657 and trunc(nvl(fu.end_date, sysdate))
661 and rel.role_resource_type = ''RS_INDIVIDUAL''
658 )
659 and rs.resource_id in ( select unique rel.role_resource_id from jtf_rs_role_relations rel where
660 rel.role_id in (28, 29, 30) and rel.delete_flag = ''N''
662 and trunc(sysdate) between trunc(nvl(rel.start_date_active, sysdate))
663 and trunc(nvl(rel.end_date_active, sysdate)) )
664 and a.resource_id not in
665 (select a.resource_id from iem_agents a, ieu_sh_sessions b where b.application_id=680 and
666 a.resource_id=b.resource_id and a.email_account_id =:email_account_id ) ) order by real_last_login_time ';
667
668 l_string := l_string || l_string2 || l_sort_order;
669
670 DBMS_SQL.PARSE(l_cursorID, l_string, DBMS_SQL.native);
671
672 DBMS_SQL.BIND_VARIABLE(l_cursorID, ':email_account_id', p_email_account_id);
673
674 DBMS_SQL.DEFINE_COLUMN(l_cursorID, 1, l_resource_id);
675 DBMS_SQL.DEFINE_COLUMN(l_cursorID, 2, l_resource_name, 722);
676 DBMS_SQL.DEFINE_COLUMN(l_cursorID, 3, l_last_login_time, 128);
677 DBMS_SQL.DEFINE_COLUMN(l_cursorID, 4, l_real_last_login_time);
678
679 l_dummy := DBMS_SQL.EXECUTE(l_cursorID);
680
681 LOOP
682 IF (DBMS_SQL.FETCH_ROWS(l_cursorID) = 0) THEN
683 EXIT;
684 END IF;
685
686 DBMS_SQL.COLUMN_VALUE(l_cursorID, 1, l_resource_id);
687 DBMS_SQL.COLUMN_VALUE(l_cursorID, 2, l_resource_name);
688 DBMS_SQL.COLUMN_VALUE(l_cursorID, 3, l_last_login_time);
689 DBMS_SQL.COLUMN_VALUE(l_cursorID, 4, l_real_last_login_time);
690
691 select count(*) into l_fetched_emails from iem_rt_proc_emails a
692 where a.email_account_id=p_email_account_id and a.queue_status is null and a.resource_id = l_resource_id;
693
694 x_resource_count(l_index).resource_id :=l_resource_id;
695 x_resource_count(l_index).resource_name := l_resource_name;
696 x_resource_count(l_index).email_count := l_fetched_emails;
697 x_resource_count(l_index).last_login_time := l_last_login_time;
698
699 l_index := l_index + 1;
700 END LOOP;
701
702 else
703 -- p_sort_column=0 or 1
704
705 if (p_sort_column = 1) then
706 l_sort_column := 'fetched_emails';
707 else
708 l_sort_column := 'resource_name';
709 end if;
710
711 if (l_sort_column = 'fetched_emails') then
712 l_order_by := ' order by ' || l_sort_column || ' ' || l_sort_order;
713 else
714 l_order_by := ' order by UPPER(' || l_sort_column || ') ' || l_sort_order;
715 end if;
716
717 l_string := 'select resource_id, fetched_emails, resource_name from
718 ( SELECT agact.resource_id, count(*) fetched_emails, concat(concat(rs.source_last_name, '',''), rs.source_first_name) as resource_name
719 from iem_agents agact, iem_rt_proc_emails pm, jtf_rs_resource_extns rs
720 WHERE pm.resource_id=agact.resource_id
721 and agact.resource_id = rs.resource_id
722 and pm.email_account_id=agact.email_account_id and pm.queue_status is null
723 and agact.email_account_id=:email_account_id
724 and trunc(sysdate) between trunc(nvl(rs.start_date_active, sysdate))
725 and trunc(nvl(rs.end_date_active, sysdate))
726 and rs.user_id in
727 ( select respgrp.user_id from fnd_user_resp_groups respgrp, fnd_user fu, fnd_responsibility resp
728 where respgrp.user_id = fu.user_id
729 and respgrp.responsibility_id=resp.responsibility_id and resp.application_id=680
730 and (resp.responsibility_key = ''EMAIL_CENTER_SUPERVISOR'' or resp.responsibility_key = ''IEM_SA_AGENT'')
731 and trunc(sysdate) between trunc(nvl(respgrp.start_date, sysdate))
735 )
732 and trunc(nvl(respgrp.end_date, sysdate))
733 and trunc(sysdate) between trunc(nvl(fu.start_date, sysdate))
734 and trunc(nvl(fu.end_date, sysdate))
736 and rs.resource_id in ( select unique rel.role_resource_id from jtf_rs_role_relations rel where
737 rel.role_id in (28, 29, 30) and rel.delete_flag = ''N''
738 and rel.role_resource_type = ''RS_INDIVIDUAL''
739 and trunc(sysdate) between trunc(nvl(rel.start_date_active, sysdate))
740 and trunc(nvl(rel.end_date_active, sysdate)) )
741 group by agact.resource_id, rs.source_last_name, rs.source_first_name ';
742
743 l_string2 := ' union all SELECT agact.resource_id, 0, concat(concat(rs.source_last_name, '', ''), rs.source_first_name) as resource_name
744 from iem_agents agact, jtf_rs_resource_extns rs
745 WHERE agact.resource_id = rs.resource_id
746 and agact.email_account_id=:email_account_id
747 and trunc(sysdate) between trunc(nvl(rs.start_date_active, sysdate))
748 and trunc(nvl(rs.end_date_active, sysdate))
749 and rs.user_id in
750 ( select respgrp.user_id from fnd_user_resp_groups respgrp, fnd_user fu, fnd_responsibility resp
751 where respgrp.user_id = fu.user_id
752 and respgrp.responsibility_id=resp.responsibility_id and resp.application_id=680
753 and (resp.responsibility_key = ''EMAIL_CENTER_SUPERVISOR'' or resp.responsibility_key = ''IEM_SA_AGENT'')
754 and trunc(sysdate) between trunc(nvl(respgrp.start_date, sysdate))
755 and trunc(nvl(respgrp.end_date, sysdate))
756 and trunc(sysdate) between trunc(nvl(fu.start_date, sysdate))
757 and trunc(nvl(fu.end_date, sysdate))
758 )
759 and rs.resource_id in ( select unique rel.role_resource_id from jtf_rs_role_relations rel where
760 rel.role_id in (28, 29, 30) and rel.delete_flag = ''N''
761 and rel.role_resource_type = ''RS_INDIVIDUAL''
762 and trunc(sysdate) between trunc(nvl(rel.start_date_active, sysdate))
763 and trunc(nvl(rel.end_date_active, sysdate)) )
764 and agact.resource_id not in (select pm.resource_id from iem_rt_proc_emails pm
765 where pm.email_account_id=:email_account_id and pm.queue_status is null) ) ';
766
767 l_string := l_string || l_string2 || l_order_by;
768
769 DBMS_SQL.PARSE(l_cursorID, l_string, DBMS_SQL.native);
770
771 DBMS_SQL.BIND_VARIABLE(l_cursorID, ':email_account_id', p_email_account_id);
772
773 DBMS_SQL.DEFINE_COLUMN(l_cursorID, 1, l_resource_id);
774 DBMS_SQL.DEFINE_COLUMN(l_cursorID, 2, l_fetched_emails);
775 DBMS_SQL.DEFINE_COLUMN(l_cursorID, 3, l_resource_name, 722);
776
777 l_dummy := DBMS_SQL.EXECUTE(l_cursorID);
778
779 LOOP
780 IF (DBMS_SQL.FETCH_ROWS(l_cursorID) = 0) THEN
781 EXIT;
782 END IF;
783
784 DBMS_SQL.COLUMN_VALUE(l_cursorID, 1, l_resource_id);
785 DBMS_SQL.COLUMN_VALUE(l_cursorID, 2, l_fetched_emails);
786 DBMS_SQL.COLUMN_VALUE(l_cursorID, 3, l_resource_name);
787
788 select to_char(max(begin_date_time), 'MM/DD/RRRR HH24:MI:SS') into l_last_login_time
789 from ieu_sh_sessions where application_id=680 and resource_id=l_resource_id;
790
791 x_resource_count(l_index).resource_id :=l_resource_id;
792 x_resource_count(l_index).resource_name := l_resource_name;
793 x_resource_count(l_index).email_count := l_fetched_emails;
794 x_resource_count(l_index).last_login_time := l_last_login_time;
795
796 l_index := l_index + 1;
797
798 END LOOP;
799 end if; -- if p_sort_column=2
800
801 DBMS_SQL.CLOSE_CURSOR(l_cursorID);
802
803 -- Standard Check Of p_commit.
804 IF FND_API.To_Boolean(p_commit) THEN
805 COMMIT WORK;
806 END IF;
807 -- Standard callto get message count and if count is 1, get message info.
808 FND_MSG_PUB.Count_And_Get
809 ( p_count => x_msg_count,
810 p_data => x_msg_data
811 );
812 EXCEPTION
813
814 WHEN FND_API.G_EXC_ERROR THEN
815 ROLLBACK TO show_agent_list_pvt;
816 x_return_status := FND_API.G_RET_STS_ERROR ;
817 FND_MSG_PUB.Count_And_Get
818 ( p_count => x_msg_count,
819 p_data => x_msg_data
820 );
821 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
822 ROLLBACK TO show_agent_list_pvt;
823 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
824 FND_MSG_PUB.Count_And_Get
825 ( p_count => x_msg_count,
826 p_data => x_msg_data
827 );
828 WHEN OTHERS THEN
829 ROLLBACK TO show_agent_list_pvt;
830 x_return_status := FND_API.G_RET_STS_ERROR;
831 IF FND_MSG_PUB.Check_Msg_Level
832 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
833 THEN
834 FND_MSG_PUB.Add_Exc_Msg
835 ( G_PKG_NAME,
836 l_api_name
837 );
838 END IF;
839 FND_MSG_PUB.Count_And_Get
840 (p_count => x_msg_count,
841 p_data => x_msg_data
842 );
843
844
845 END show_agent_list;
846
847 PROCEDURE show_resource_group_list (p_api_version_number IN NUMBER,
848 p_init_msg_list IN VARCHAR2,
849 p_commit IN VARCHAR2,
850 p_email_account_id in number,
851 p_sort_column IN number,
852 p_sort_state IN varchar2,
853 x_resource_group_count out nocopy resource_group_count_tbl,
857
854 x_return_status OUT NOCOPY VARCHAR2,
855 x_msg_count OUT NOCOPY NUMBER,
856 x_msg_data OUT NOCOPY VARCHAR2) IS
858 l_api_name VARCHAR2(255):='show_resource_group_list';
859 l_api_version_number NUMBER:=1.0;
860 l_index number := 1;
861 l_count number;
862 l_group_name varchar2(720);
863 l_group_id number;
864 l_email_count number;
865 l_agent_count number;
866 l_email_account_id varchar2(30);
867 l_string varchar2(32767):='';
868 l_order_by varchar2(500):='';
869 l_sort_column varchar2(500):='group_name'; -- default
870 l_sort_order varchar2(20):='asc'; -- default
871 Type get_message_rec is REF CURSOR ;
872 email_dtl_cur get_message_rec;
873
874 l_cursorID INTEGER;
875 l_dummy INTEGER;
876
877 BEGIN
878 SAVEPOINT show_resource_group_list_pvt;
879 -- Standard call to check for call compatibility.
880 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
881 p_api_version_number,
882 l_api_name,
883 G_PKG_NAME)
884 THEN
885 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
886 END IF;
887 -- Initialize message list if p_init_msg_list is set to TRUE.
888 IF FND_API.to_Boolean( p_init_msg_list )
889 THEN
890 FND_MSG_PUB.initialize;
891 END IF;
892
893 -- Initialize API return status to SUCCESS
894 x_return_status := FND_API.G_RET_STS_SUCCESS;
895
896 -- determine sort state
897 if (p_sort_state = 'ascending') then
898 l_sort_order := 'desc';
899 else
900 l_sort_order := 'asc';
901 end if;
902
903 if (p_email_account_id is not null) then
904 l_email_account_id := to_char(p_email_account_id);
905 end if;
906
907 l_cursorID := DBMS_SQL.OPEN_CURSOR;
908
909 if (p_sort_column = 1) then
910
911 l_string := 'select group_id, group_name, email_count from(
912 select f.group_id, e.group_name, count(*) as email_count from iem_rt_proc_emails f, jtf_rs_groups_vl e where
913 trunc(sysdate) between trunc(nvl(e.start_date_active, sysdate))
914 and trunc(nvl(e.end_date_active, sysdate))
915 and f.group_id=e.group_id and f.resource_id=0 and f.email_account_id=:email_account_id and f.group_id in (
916 select unique c.group_id from
917 jtf_rs_group_members c, iem_agents d, jtf_rs_group_usages gu
918 where
919 c.delete_flag = ''N'' and c.resource_id = d.resource_id and d.email_account_id =:l_email_account_id
920 and c.group_id = gu.group_id and gu.usage = ''CALL''
921 ) group by f.group_id, e.group_name
922 union all
923 select unique e.group_id, group_name, 0 from
924 jtf_rs_group_members c, iem_agents d, jtf_rs_groups_vl e,
925 jtf_rs_group_usages gu where
926 c.group_id = e.group_id
927 and c.delete_flag = ''N'' and c.resource_id = d.resource_id and d.email_account_id =:l_email_account_id
928 and trunc(sysdate) between trunc(nvl(e.start_date_active, sysdate))
929 and trunc(nvl(e.end_date_active, sysdate))
930 and c.group_id = gu.group_id and gu.usage = ''CALL''
931 and e.group_id not in (
932 select unique group_id from iem_rt_proc_emails where email_account_id=:email_account_id
933 and group_id<>0 and resource_id=0)
934 ) order by email_count ';
935
936 l_string := l_string || l_sort_order;
937
938 DBMS_SQL.PARSE(l_cursorID, l_string, DBMS_SQL.native);
939
940 DBMS_SQL.BIND_VARIABLE(l_cursorID, ':email_account_id', p_email_account_id);
941 DBMS_SQL.BIND_VARIABLE(l_cursorID, ':l_email_account_id', l_email_account_id);
942 DBMS_SQL.BIND_VARIABLE(l_cursorID, ':l_email_account_id', l_email_account_id);
943 DBMS_SQL.BIND_VARIABLE(l_cursorID, ':email_account_id', p_email_account_id);
944
945 DBMS_SQL.DEFINE_COLUMN(l_cursorID, 1, l_group_id);
946 DBMS_SQL.DEFINE_COLUMN(l_cursorID, 2, l_group_name, 60);
947 DBMS_SQL.DEFINE_COLUMN(l_cursorID, 3, l_email_count);
948
949 l_dummy := DBMS_SQL.EXECUTE(l_cursorID);
950
951 LOOP
952
953 IF (DBMS_SQL.FETCH_ROWS(l_cursorID) = 0) THEN
954 EXIT;
955 END IF;
956
957 DBMS_SQL.COLUMN_VALUE(l_cursorID, 1, l_group_id);
958 DBMS_SQL.COLUMN_VALUE(l_cursorID, 2, l_group_name);
959 DBMS_SQL.COLUMN_VALUE(l_cursorID, 3, l_email_count);
960
961 -- Start changes for bug no. 7418671
962 --select count(*) into l_agent_count from jtf_rs_roles_tl a, jtf_rs_role_relations b, jtf_rs_group_members c, iem_agents d
963 --where a.role_id in (28, 29, 30) and a.language = USERENV ('LANG') and a.role_id = b.role_id and b.delete_flag = 'N'
964 --and b.role_resource_id = c.resource_id and c.delete_flag = 'N' and c.resource_id = d.resource_id
965 --and d.email_account_id = l_email_account_id and c.group_id = l_group_id;
966 SELECT count(*) into l_agent_count
967 FROM jtf_rs_group_members c, iem_agents d
968 WHERE c.resource_id IN
969 (select b.role_resource_id from jtf_rs_role_relations b
970 where b.role_resource_id=c.resource_id and b.role_id IN (28,29,30)
971 AND b.delete_flag='N'
972 AND TRUNC(sysdate) BETWEEN TRUNC(NVL(b.start_date_active, sysdate))
973 AND TRUNC(NVL(b.end_date_active, sysdate)))
974 AND c.delete_flag = 'N'
975 AND c.resource_id = d.resource_id
976 AND d.email_account_id = l_email_account_id
980 x_resource_group_count(l_index).group_id :=l_group_id;
977 AND c.group_id = l_group_id;
978 -- End changes for bug no. 7418671
979
981 x_resource_group_count(l_index).group_name := l_group_name;
982 x_resource_group_count(l_index).agent_count := l_agent_count;
983 x_resource_group_count(l_index).email_count := l_email_count;
984
985 l_index := l_index + 1;
986 END LOOP;
987 else
988 -- p_sort_column=0 or 2
989
990 if (p_sort_column = 2) then
991 l_sort_column := 'agent_count';
992 else
993 l_sort_column := 'group_name';
994 end if;
995
996 if (l_sort_column = 'agent_count') then
997 l_order_by := ' order by ' || l_sort_column || ' ' || l_sort_order;
998 else
999 l_order_by := ' order by UPPER(' || l_sort_column || ') ' || l_sort_order;
1000 end if;
1001
1002 -- Start changes for bug no. 7418671
1003 --l_string := 'select e.group_id, e.group_name, count(*) agent_count from
1004 -- jtf_rs_role_relations b, jtf_rs_group_members c, iem_agents d, jtf_rs_groups_vl e, jtf_rs_resource_extns res, jtf_rs_group_usages gu
1005 -- where b.delete_flag = ''N'' and b.role_id in (28, 29, 30)
1006 -- and b.role_resource_id = c.resource_id and c.group_id = e.group_id
1007 -- and c.resource_id=res.resource_id
1008 -- and c.group_id = gu.group_id
1009 -- and gu.usage = ''CALL''
1010 -- and trunc(sysdate) between trunc(nvl(b.start_date_active, sysdate))
1011 -- and trunc(nvl(b.end_date_active, sysdate))
1012 -- and trunc(sysdate) between trunc(nvl(e.start_date_active, sysdate))
1013 -- and trunc(nvl(e.end_date_active, sysdate))
1014 -- and trunc(sysdate) between trunc(nvl(res.start_date_active, sysdate))
1015 -- and trunc(nvl(res.end_date_active, sysdate))
1016 -- and c.delete_flag = ''N''
1017 -- and c.resource_id = d.resource_id and d.email_account_id = :l_email_account_id
1018 -- group by e.group_id, e.group_name ';
1019 l_string := 'SELECT e.group_id, e.group_name, COUNT(*) agent_count
1020 FROM jtf_rs_group_members c, iem_agents d, jtf_rs_groups_vl e, jtf_rs_resource_extns res, jtf_rs_group_usages gu
1021 WHERE c.group_id = e.group_id AND c.resource_id = res.resource_id
1022 AND c.group_id = gu.group_id
1023 AND c.resource_id in
1024 (select b.role_resource_id from jtf_rs_role_relations b
1025 where b.role_resource_id=c.resource_id and b.role_id IN (28,29,30) and b.delete_flag=''N''
1026 AND TRUNC(sysdate) BETWEEN TRUNC(NVL(b.start_date_active, sysdate))
1027 AND TRUNC(NVL(b.end_date_active, sysdate)))
1028 AND gu.usage = ''CALL''
1029 AND c.delete_flag = ''N''
1030 AND c.resource_id = d.resource_id
1031 AND TRUNC(sysdate) BETWEEN TRUNC(NVL(e.start_date_active, sysdate))
1032 AND TRUNC(NVL(e.end_date_active, sysdate))
1033 AND TRUNC(sysdate) BETWEEN TRUNC(NVL(res.start_date_active, sysdate))
1034 AND TRUNC(NVL(res.end_date_active, sysdate))
1035 AND d.email_account_id = :l_email_account_id
1036 GROUP BY e.group_id,e.group_name ';
1037 -- End changes for bug no. 7418671
1038
1039
1040 l_string := l_string || l_order_by;
1041
1042 DBMS_SQL.PARSE(l_cursorID, l_string, DBMS_SQL.native);
1043
1044 DBMS_SQL.BIND_VARIABLE(l_cursorID, ':l_email_account_id', l_email_account_id);
1045
1046 DBMS_SQL.DEFINE_COLUMN(l_cursorID, 1, l_group_id);
1047 DBMS_SQL.DEFINE_COLUMN(l_cursorID, 2, l_group_name, 60);
1048 DBMS_SQL.DEFINE_COLUMN(l_cursorID, 3, l_agent_count);
1049
1050 l_dummy := DBMS_SQL.EXECUTE(l_cursorID);
1051
1052 LOOP
1053 IF (DBMS_SQL.FETCH_ROWS(l_cursorID) = 0) THEN
1054 EXIT;
1055 END IF;
1056
1057 DBMS_SQL.COLUMN_VALUE(l_cursorID, 1, l_group_id);
1058 DBMS_SQL.COLUMN_VALUE(l_cursorID, 2, l_group_name);
1059 DBMS_SQL.COLUMN_VALUE(l_cursorID, 3, l_agent_count);
1060
1061 select count(*) into l_email_count from iem_rt_proc_emails where resource_id=0
1062 and email_account_id=p_email_account_id and group_id=l_group_id;
1063
1064 x_resource_group_count(l_index).group_id :=l_group_id;
1065 x_resource_group_count(l_index).group_name := l_group_name;
1066 x_resource_group_count(l_index).agent_count := l_agent_count;
1067 x_resource_group_count(l_index).email_count := l_email_count;
1068
1069 l_index := l_index + 1;
1070
1071 END LOOP;
1072 end if; -- if p_sort_column=1
1073
1074 DBMS_SQL.CLOSE_CURSOR(l_cursorID);
1075
1076
1077 -- Standard Check Of p_commit.
1078 IF FND_API.To_Boolean(p_commit) THEN
1079 COMMIT WORK;
1080 END IF;
1081 -- Standard callto get message count and if count is 1, get message info.
1082 FND_MSG_PUB.Count_And_Get
1083 ( p_count => x_msg_count,
1084 p_data => x_msg_data
1085 );
1086 EXCEPTION
1087
1088 WHEN FND_API.G_EXC_ERROR THEN
1089 ROLLBACK TO show_resource_group_list_pvt;
1090 x_return_status := FND_API.G_RET_STS_ERROR ;
1091 FND_MSG_PUB.Count_And_Get
1092 ( p_count => x_msg_count,
1093 p_data => x_msg_data
1094 );
1095 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1096 ROLLBACK TO show_resource_group_list_pvt;
1097 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1098 FND_MSG_PUB.Count_And_Get
1099 ( p_count => x_msg_count,
1100 p_data => x_msg_data
1101 );
1102 WHEN OTHERS THEN
1103 ROLLBACK TO show_resource_group_list_pvt;
1104 x_return_status := FND_API.G_RET_STS_ERROR;
1105 IF FND_MSG_PUB.Check_Msg_Level
1106 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1107 THEN
1108 FND_MSG_PUB.Add_Exc_Msg
1109 ( G_PKG_NAME,
1110 l_api_name
1111 );
1112 END IF;
1113 FND_MSG_PUB.Count_And_Get
1114 (p_count => x_msg_count,
1115 p_data => x_msg_data
1116 );
1117
1118
1119 END show_resource_group_list;
1120
1121 end IEM_QUEUE_MANAGEMENT_PVT ;