[Home] [Help]
PACKAGE BODY: APPS.IEM_EMAILACCOUNT_PUB
Source
1 PACKAGE BODY IEM_EMAILACCOUNT_PUB as
2 /* $Header: iempactb.pls 120.11 2011/12/25 17:17:57 lkullamb ship $ */
3 G_PKG_NAME CONSTANT varchar2(30) :='IEM_EMAILACCOUNT_PUB ';
4
5 PROCEDURE Get_EmailAccount_List (p_api_version_number IN NUMBER,
6 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
7 p_commit IN VARCHAR2 := FND_API.G_FALSE,
8 p_RESOURCE_ID IN NUMBER:=null,
9 x_return_status OUT NOCOPY VARCHAR2,
10 x_msg_count OUT NOCOPY NUMBER,
11 x_msg_data OUT NOCOPY VARCHAR2,
12 x_Email_Acnt_tbl OUT NOCOPY EMACNT_tbl_type
13 ) is
14 CURSOR email_details_csr IS
15
16 SELECT a.from_name,
17 a.user_name,
18 a.email_account_id
19 FROM IEM_MSTEMAIL_ACCOUNTS A,
20 JTF_RS_RESOURCE_VALUES B
21 WHERE (B.resource_id=p_RESOURCE_ID)
22 AND (A.email_account_id=B.VALUE_TYPE);
23
24 CURSOR email_details_no_resource_csr IS
25
26 SELECT a.from_name,
27 a.user_name,
28 a.email_account_id
29 FROM IEM_MSTEMAIL_ACCOUNTS A ;
30 l_email_index number:=1;
31
32 l_api_name VARCHAR2(255):='Get_EmailAccount_List';
33 l_api_version_number NUMBER:=1.0;
34
35 BEGIN
36 -- Standard Start of API savepoint
37 SAVEPOINT Get_EmailAccount_List_PUB;
38 -- Standard call to check for call compatibility.
39 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
40 p_api_version_number,
41 l_api_name,
42 G_PKG_NAME)
43 THEN
44 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
45 END IF;
46 -- Initialize message list if p_init_msg_list is set to TRUE.
47 IF FND_API.to_Boolean( p_init_msg_list )
48 THEN
49 FND_MSG_PUB.initialize;
50 END IF;
51 -- Initialize API return status to SUCCESS
52 x_return_status := FND_API.G_RET_STS_SUCCESS;
53 IF p_resource_id is not null then
54 FOR c_email_rec in email_details_csr
55 LOOP
56 -- x_Email_Acnt_tbl(l_email_index).server_id:=c_email_rec.mail_server_id;
57 x_Email_Acnt_tbl(l_email_index).account_name:=c_email_rec.from_name;
58 x_Email_Acnt_tbl(l_email_index).db_user:=c_email_rec.user_name;
59 -- x_Email_Acnt_tbl(l_email_index).domain:=c_email_rec.user_domain;
60 -- x_Email_Acnt_tbl(l_email_index).account_password:=c_email_rec.user_password;
61 x_Email_Acnt_tbl(l_email_index).account_id:=c_email_rec.email_account_id;
62
63 l_email_index:=l_email_index+1;
64
65 END LOOP;
66 ELSE
67 FOR c_email_rec in email_details_no_resource_csr
68 LOOP
69 -- x_Email_Acnt_tbl(l_email_index).server_id:=c_email_rec.mail_server_id;
70 x_Email_Acnt_tbl(l_email_index).account_name:=c_email_rec.from_name;
71 x_Email_Acnt_tbl(l_email_index).db_user:=c_email_rec.user_name;
72 -- x_Email_Acnt_tbl(l_email_index).domain:=c_email_rec.user_domain;
73 -- x_Email_Acnt_tbl(l_email_index).account_password:=c_email_rec.user_password;
74 x_Email_Acnt_tbl(l_email_index).account_id:=c_email_rec.email_account_id;
75
76 l_email_index:=l_email_index+1;
77
78 END LOOP;
79 END IF;
80 -- Standard Check Of p_commit.
81 IF FND_API.To_Boolean(p_commit) THEN
82 COMMIT WORK;
83 END IF;
84 -- Standard callto get message count and if count is 1, get message info.
85 FND_MSG_PUB.Count_And_Get
86 ( p_count => x_msg_count,
87 p_data => x_msg_data
88 );
89 EXCEPTION
90 WHEN FND_API.G_EXC_ERROR THEN
91 ROLLBACK TO Get_EmailAccount_List_PUB;
92 x_return_status := FND_API.G_RET_STS_ERROR ;
93 FND_MSG_PUB.Count_And_Get
94 ( p_count => x_msg_count,
95 p_data => x_msg_data
96 );
97 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
98 ROLLBACK TO Get_EmailAccount_List_PUB;
99 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
100 FND_MSG_PUB.Count_And_Get
101 ( p_count => x_msg_count,
102 p_data => x_msg_data
103 );
104 WHEN OTHERS THEN
105 ROLLBACK TO Get_EmailAccount_List_PUB;
106 x_return_status := FND_API.G_RET_STS_ERROR;
107 IF FND_MSG_PUB.Check_Msg_Level
108 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
109 THEN
110 FND_MSG_PUB.Add_Exc_Msg
111 ( G_PKG_NAME ,
112 l_api_name
113 );
114 END IF;
115 FND_MSG_PUB.Count_And_Get
116 ( p_count => x_msg_count ,
117 p_data => x_msg_data
118 );
119
120 END Get_EmailAccount_List;
121
122 Procedure getEmailHeaders(
123 p_AgentName IN VARCHAR2,
124 p_top_n IN INTEGER default 0,
125 p_top_option IN INTEGER default 1,
126 p_folder_path IN VARCHAR2 default 'ALL',
127 message_headers OUT NOCOPY msg_header_table
128 ) is
129 begin
130 null;
131 end getEmailHeaders;
132
133 PROCEDURE ListAgentAccounts (p_api_version_number IN NUMBER,
134 p_init_msg_list IN VARCHAR2 ,
135 p_commit IN VARCHAR2 ,
136 p_RESOURCE_ID IN NUMBER,
137 x_return_status OUT NOCOPY VARCHAR2,
138 x_msg_count OUT NOCOPY NUMBER,
139 x_msg_data OUT NOCOPY VARCHAR2,
140 x_Agent_Acnt_tbl OUT NOCOPY AGENTACNT_tbl_type
141 ) is
142 CURSOR agent_accounts_csr IS
143
144 SELECT nvl(nvl(b.reply_to_address,b.return_address),b.email_address) reply_to_address,
145 a.signature,
146 a.agent_id,
147 a.email_account_id
148 FROM IEM_AGENTS A,
149 IEM_MSTEMAIL_ACCOUNTS B
150 WHERE (A.resource_id=p_RESOURCE_ID)
151 AND (A.email_account_id=B.EMAIL_ACCOUNT_ID)
152 ORDER BY a.agent_id;
153
154 l_email_index number:=1;
155
156 l_api_name VARCHAR2(255):='ListAgentAccounts';
157 l_api_version_number NUMBER:=1.0;
158 l_user_name varchar2(500);
159 l_res_name varchar2(1000);
160 l_flag number;
161
162 BEGIN
163 -- Standard Start of API savepoint
164 SAVEPOINT ListAgentAccounts_PUB;
165 -- Standard call to check for call compatibility.
166 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
167 p_api_version_number,
168 l_api_name,
169 G_PKG_NAME)
170 THEN
171 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
172 END IF;
173 -- Initialize message list if p_init_msg_list is set to TRUE.
174 IF FND_API.to_Boolean( p_init_msg_list )
175 THEN
176 FND_MSG_PUB.initialize;
177 END IF;
178 -- Initialize API return status to SUCCESS
179 x_return_status := FND_API.G_RET_STS_SUCCESS;
180 IF p_resource_id is not null then
181 FOR agent_account_rec in agent_accounts_csr
182 LOOP
183 SELECT USER_NAME, SOURCE_LAST_NAME || ', ' || SOURCE_FIRST_NAME as RESOURCE_NAME
184 INTO l_user_name, l_res_name
185 FROM JTF_RS_RESOURCE_EXTNS
186 WHERE RESOURCE_ID = p_resource_id;
187 select sender_flag into l_flag from iem_mstemail_accounts
188 where email_account_id= agent_account_rec.email_account_id;
189 IF l_flag=0 then -- From Name selected from Account.
190 select from_name into l_res_name
191 from iem_mstemail_accounts
192 where email_account_id= agent_account_rec.email_account_id;
193 END IF;
194 x_Agent_Acnt_tbl(l_email_index).account_name:=l_user_name;
195 x_Agent_Acnt_tbl(l_email_index).reply_to_address:=agent_account_rec.reply_to_address;
196 x_Agent_Acnt_tbl(l_email_index).from_address:=agent_account_rec.reply_to_address;
197 x_Agent_Acnt_tbl(l_email_index).from_name:=l_res_name;
198 x_Agent_Acnt_tbl(l_email_index).user_name:=l_user_name;
199 x_Agent_Acnt_tbl(l_email_index).signature:=agent_account_rec.signature;
200 x_Agent_Acnt_tbl(l_email_index).email_account_id:=agent_account_rec.email_account_id;
201 x_Agent_Acnt_tbl(l_email_index).agent_account_id:=agent_account_rec.agent_id;
202
203 l_email_index:=l_email_index+1;
204
205 END LOOP;
206 END IF;
207 -- Standard Check Of p_commit.
208 IF FND_API.To_Boolean(p_commit) THEN
209 COMMIT WORK;
210 END IF;
211 -- Standard callto get message count and if count is 1, get message info.
212 FND_MSG_PUB.Count_And_Get
213 ( p_count => x_msg_count,
214 p_data => x_msg_data
215 );
216 EXCEPTION
217 WHEN FND_API.G_EXC_ERROR THEN
218 ROLLBACK TO ListAgentAccounts_PUB;
219 x_return_status := FND_API.G_RET_STS_ERROR ;
220 FND_MSG_PUB.Count_And_Get
221 ( p_count => x_msg_count,
222 p_data => x_msg_data
223 );
224 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
225 ROLLBACK TO ListAgentAccounts_PUB;
226 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
227 FND_MSG_PUB.Count_And_Get
228 ( p_count => x_msg_count,
229 p_data => x_msg_data
230 );
231 WHEN OTHERS THEN
232 ROLLBACK TO ListAgentAccounts_PUB;
233 x_return_status := FND_API.G_RET_STS_ERROR;
234 IF FND_MSG_PUB.Check_Msg_Level
235 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
236 THEN
237 FND_MSG_PUB.Add_Exc_Msg
238 ( G_PKG_NAME,
239 l_api_name
240 );
241 END IF;
242 FND_MSG_PUB.Count_And_Get
243 ( p_count => x_msg_count,
244 p_data => x_msg_data
245 );
246
247 END ListAgentAccounts;
248 -- 12.1.2 Development. Bug 8829918
249 PROCEDURE ListAgentCPAccounts (p_api_version_number IN NUMBER,
250 p_init_msg_list IN VARCHAR2 ,
251 p_commit IN VARCHAR2 ,
252 p_RESOURCE_ID IN NUMBER,
253 x_return_status OUT NOCOPY VARCHAR2,
254 x_msg_count OUT NOCOPY NUMBER,
255 x_msg_data OUT NOCOPY VARCHAR2,
256 x_Agent_Acnt_tbl OUT NOCOPY AGENTACNT_tbl_type
257 ) is
258 CURSOR agent_accounts_csr IS
259
260 SELECT nvl(nvl(b.reply_to_address,b.return_address),b.email_address) reply_to_address,
261 a.signature,
262 a.agent_id,
263 a.email_account_id
264 FROM IEM_AGENTS A,
265 IEM_MSTEMAIL_ACCOUNTS B
266 WHERE (A.resource_id=p_RESOURCE_ID)
267 AND (A.email_account_id=B.EMAIL_ACCOUNT_ID)
268 AND A.cherry_pick_flag = 'Y'
269 ORDER BY a.agent_id;
270
271 l_email_index number:=1;
272
273 l_api_name VARCHAR2(255):='ListAgentCPAccounts';
274 l_api_version_number NUMBER:=1.0;
275 l_user_name varchar2(500);
276 l_res_name varchar2(1000);
277 l_flag number;
278
279 BEGIN
280 -- Standard Start of API savepoint
281 SAVEPOINT ListAgentCPAccounts_PUB;
282 -- Standard call to check for call compatibility.
283 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
284 p_api_version_number,
285 l_api_name,
286 G_PKG_NAME)
287 THEN
288 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
289 END IF;
290 -- Initialize message list if p_init_msg_list is set to TRUE.
291 IF FND_API.to_Boolean( p_init_msg_list )
292 THEN
293 FND_MSG_PUB.initialize;
294 END IF;
295 -- Initialize API return status to SUCCESS
296 x_return_status := FND_API.G_RET_STS_SUCCESS;
297 IF p_resource_id is not null then
298 FOR agent_account_rec in agent_accounts_csr
299 LOOP
300 SELECT USER_NAME, SOURCE_LAST_NAME || ', ' || SOURCE_FIRST_NAME as RESOURCE_NAME
301 INTO l_user_name, l_res_name
302 FROM JTF_RS_RESOURCE_EXTNS
303 WHERE RESOURCE_ID = p_resource_id;
304 select sender_flag into l_flag from iem_mstemail_accounts
305 where email_account_id= agent_account_rec.email_account_id;
306 IF l_flag=0 then -- From Name selected from Account.
307 select from_name into l_res_name
308 from iem_mstemail_accounts
309 where email_account_id= agent_account_rec.email_account_id;
310 END IF;
311 x_Agent_Acnt_tbl(l_email_index).account_name:=l_user_name;
312 x_Agent_Acnt_tbl(l_email_index).reply_to_address:=agent_account_rec.reply_to_address;
313 x_Agent_Acnt_tbl(l_email_index).from_address:=agent_account_rec.reply_to_address;
314 x_Agent_Acnt_tbl(l_email_index).from_name:=l_res_name;
315 x_Agent_Acnt_tbl(l_email_index).user_name:=l_user_name;
316 x_Agent_Acnt_tbl(l_email_index).signature:=agent_account_rec.signature;
317 x_Agent_Acnt_tbl(l_email_index).email_account_id:=agent_account_rec.email_account_id;
318 x_Agent_Acnt_tbl(l_email_index).agent_account_id:=agent_account_rec.agent_id;
319
320 l_email_index:=l_email_index+1;
321
322 END LOOP;
323 END IF;
324 -- Standard Check Of p_commit.
325 IF FND_API.To_Boolean(p_commit) THEN
326 COMMIT WORK;
327 END IF;
328 -- Standard callto get message count and if count is 1, get message info.
329 FND_MSG_PUB.Count_And_Get
330 ( p_count => x_msg_count,
331 p_data => x_msg_data
332 );
333 EXCEPTION
334 WHEN FND_API.G_EXC_ERROR THEN
335 ROLLBACK TO ListAgentCPAccounts_PUB;
336 x_return_status := FND_API.G_RET_STS_ERROR ;
337 FND_MSG_PUB.Count_And_Get
338 ( p_count => x_msg_count,
339 p_data => x_msg_data
340 );
341 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
342 ROLLBACK TO ListAgentCPAccounts_PUB;
343 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
344 FND_MSG_PUB.Count_And_Get
345 ( p_count => x_msg_count,
346 p_data => x_msg_data
347 );
348 WHEN OTHERS THEN
349 ROLLBACK TO ListAgentCPAccounts_PUB;
350 x_return_status := FND_API.G_RET_STS_ERROR;
351 IF FND_MSG_PUB.Check_Msg_Level
352 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
353 THEN
354 FND_MSG_PUB.Add_Exc_Msg
355 ( G_PKG_NAME,
356 l_api_name
357 );
358 END IF;
359 FND_MSG_PUB.Count_And_Get
360 ( p_count => x_msg_count,
361 p_data => x_msg_data
365 -- 12.1.2 Development. Bug 8829918
362 );
363
364 END ListAgentCPAccounts;
366
367 PROCEDURE ListAgentAccountDetails (p_api_version_number IN NUMBER,
368 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
369 p_commit IN VARCHAR2 := FND_API.G_FALSE,
370 p_EMAIL_ACCOUNT_ID IN NUMBER,
371 p_ROLEid IN NUMBER:=-1,
372 p_Resource_id IN NUMBER:=-1,
373 p_search_criteria IN VARCHAR2:=null,
374 p_display_size in NUMBER:=null,
375 p_page_count in NUMBER:=null,
376 p_sort_by in VARCHAR2:='F',
377 p_sort_order in NUMBER:=1,
378 x_return_status OUT NOCOPY VARCHAR2,
379 x_msg_count OUT NOCOPY NUMBER,
380 x_search_count OUT NOCOPY NUMBER,
381 x_msg_data OUT NOCOPY VARCHAR2,
382 x_Agent_Acnt_Dtl_data OUT NOCOPY AGNTACNTDETAILS_tbl_type
383 ) is
384
385 l_api_name VARCHAR2(255):='ListAgentAccountDetails';
386 l_api_version_number NUMBER:=1.0;
387 Type get_data is REF CURSOR;-- RETURN Agent_Acnt_Dtl_tbl;
388 email_cur get_data;
389 l_counter number:=0;
390 l_order_by varchar2(255);
391 l_order varchar2(255);
392 l_where varchar2(1000);
393 l_stmt varchar2(600);
394 l_temp_tbl AGNTACNTDETAILS_tbl_type;
395 -- l_start_index number:=0;
396 l_first_index number:=0;
397 l_last_index number:=0;
398 l_roleid NUMBER:= 0;
399 l_string varchar2(32767):='';
400 l_resource_id number;
401 l_resource_name varchar2(360);
402 l_user_name varchar2(256);
403 l_responsibility_name varchar2(100);
404 l_last_login_time varchar2(256);
405 l_cursorID INTEGER;
406 l_dummy INTEGER;
407 l_role_Str varchar2(1000);
408
409 BEGIN
410 -- Standard Start of API savepoint
411 SAVEPOINT ListAgentAccountDetails_PUB;
412 -- Standard call to check for call compatibility.
413 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
414 p_api_version_number,
415 l_api_name,
416 G_PKG_NAME)
417 THEN
418 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
419 END IF;
420 SAVEPOINT ListAgentAccountDetails_pvt;
421 x_return_status := FND_API.G_RET_STS_SUCCESS;
422
423 l_where:=' AND agnt.email_account_id= :email_account_id';
424 IF p_search_criteria is not null THEN
425 l_where:= l_where||' and (upper(res.source_last_name) like upper(:search_criteria) or upper(res.source_first_name) like upper(:search_criteria) or upper(res.user_name) like upper(:search_criteria)) ';
426 END IF;
427 IF p_roleid <> -1 THEN
428
429 if p_roleid = 2 then
430 l_role_str:='resp.responsibility_key = ''EMAIL_CENTER_SUPERVISOR''' ;
431 else
432 l_role_str:='resp.responsibility_key = ''IEM_SA_AGENT''' ;
433 end if;
434 l_where:= l_where||' and resp.application_id=680 and '||l_role_str;
435 ELSE
436 l_where:= l_where||' and resp.responsibility_key in (''EMAIL_CENTER_SUPERVISOR'', ''IEM_SA_AGENT'' ) and (res.user_id,respgrp.responsibility_id)
437 IN (select respgrp.user_id,max(respgrp.responsibility_id)
438 from fnd_user_resp_groups respgrp,fnd_responsibility resp
439 where respgrp.responsibility_id=resp.responsibility_id
440 and resp.application_id=680 and (resp.responsibility_key =''EMAIL_CENTER_SUPERVISOR'' or resp.responsibility_key=''IEM_SA_AGENT'')
441 group by respgrp.user_id) ';
442 END IF;
443 IF p_resource_id <> -1 THEN
444 l_where:= l_where||' and agnt.resource_id<> :resource_id';
445 END IF;
446
447 IF p_sort_order=1 THEN
448 l_order:=' ASC';
449 ELSE
450 l_order:=' DESC';
451 END IF;
452 IF p_sort_by = 'F' THEN
453 l_order_by:=' Order BY res.source_last_name '||l_order || ', res.source_first_name '||l_order;
454 ELSIF p_sort_by='U' THEN
455 l_order_by:=' ORDER BY res.user_name '||l_order;
456 ELSIF p_sort_by='R' THEN
457 l_order_by:=' ORDER BY resp.responsibility_name '||l_order;
458 END IF;
459
460 l_string := 'select agnt.resource_id, concat(concat(res.source_last_name, '', ''), res.source_first_name) as resource_name, res.user_name,
461 resptl.responsibility_name
462 from iem_agents agnt, fnd_responsibility resp,fnd_user_resp_groups respgrp,
463 jtf_rs_resource_extns res, fnd_user fu,fnd_responsibility_tl resptl
464 where agnt.resource_id=res.resource_id and res.user_id=respgrp.user_id
465 and resp.application_id=680
466 and resp.responsibility_id=respgrp.responsibility_id
467 and respgrp.user_id=fu.user_id
468 and resptl.application_id=680
469 and resptl.responsibility_id=resp.responsibility_id
470 and resptl.LANGUAGE = USERENV (''LANG'')
471 and trunc(sysdate) between trunc(nvl(respgrp.start_date, sysdate))
472 and trunc(nvl(respgrp.end_date, sysdate))
473 and trunc(sysdate) between trunc(nvl(fu.start_date, sysdate))
474 and trunc(nvl(fu.end_date, sysdate))
475 and trunc(sysdate) between trunc(nvl(res.start_date_active, sysdate))
476 and trunc(nvl(res.end_date_active, sysdate))
477 and res.resource_id in ( select unique rel.role_resource_id from jtf_rs_role_relations rel where
478 rel.role_id in (28, 29, 30) and rel.delete_flag = ''N''
479 and rel.role_resource_type = ''RS_INDIVIDUAL''
480 and trunc(sysdate) between trunc(nvl(rel.start_date_active, sysdate))
484 DBMS_SQL.PARSE(l_cursorID, l_string, DBMS_SQL.native);
481 and trunc(nvl(rel.end_date_active, sysdate)) ) ';
482 l_string := l_string ||l_where||l_order_by;
483 l_cursorID := DBMS_SQL.OPEN_CURSOR;
485
486 IF p_search_criteria is not null THEN
487 DBMS_SQL.BIND_VARIABLE(l_cursorID, ':search_criteria', p_search_criteria);
488 end if;
489 /*
490 IF p_roleid <> -1 THEN
491 DBMS_SQL.BIND_VARIABLE(l_cursorID, ':roleid', l_roleid);
492 END IF;
493 */
494 IF p_resource_id <> -1 THEN
495 DBMS_SQL.BIND_VARIABLE(l_cursorID, ':resource_id', p_resource_id);
496 END IF;
497
498 DBMS_SQL.BIND_VARIABLE(l_cursorID, ':email_account_id', p_email_account_id);
499
500 DBMS_SQL.DEFINE_COLUMN(l_cursorID, 1, l_resource_id);
501 DBMS_SQL.DEFINE_COLUMN(l_cursorID, 2, l_resource_name, 360);
502 DBMS_SQL.DEFINE_COLUMN(l_cursorID, 3, l_user_name, 256);
503 DBMS_SQL.DEFINE_COLUMN(l_cursorID, 4, l_responsibility_name, 100);
504
505 l_dummy := DBMS_SQL.EXECUTE(l_cursorID);
506 l_temp_tbl.delete;
507 l_counter:=1;
508
509 LOOP
510 IF (DBMS_SQL.FETCH_ROWS(l_cursorID) = 0) THEN
511 EXIT;
512 END IF;
513
514 DBMS_SQL.COLUMN_VALUE(l_cursorID, 1, l_resource_id);
515 DBMS_SQL.COLUMN_VALUE(l_cursorID, 2, l_resource_name);
516 DBMS_SQL.COLUMN_VALUE(l_cursorID, 3, l_user_name);
517 DBMS_SQL.COLUMN_VALUE(l_cursorID, 4, l_responsibility_name);
518
519 select to_char(max(begin_date_time), 'MM/DD/RRRR HH24:MI:SS') into l_last_login_time
520 from ieu_sh_sessions where application_id=680 and resource_id=l_resource_id;
521
522 l_temp_tbl(l_counter).resource_id := l_resource_id;
523 l_temp_tbl(l_counter).resource_name := l_resource_name;
524 l_temp_tbl(l_counter).user_name := l_user_name;
525 l_temp_tbl(l_counter).role := l_responsibility_name;
526 l_temp_tbl(l_counter).last_login_time := l_last_login_time;
527
528 l_counter:=l_counter+1;
529
530 END LOOP;
531
532 DBMS_SQL.CLOSE_CURSOR(l_cursorID);
533
534 x_search_count:=0;
535 x_search_count:=l_temp_tbl.count;
536 IF l_temp_tbl.count>0 THEN
537 --x_total_message:=l_temp_tbl.count;
538 IF p_display_size is null THEN
539 x_Agent_Acnt_Dtl_data:=l_temp_tbl;
540 ELSE
541 IF p_page_count is not null THEN
542 l_first_index:=p_page_count*p_display_size - p_display_size+1;
543 l_last_index:=p_page_count*p_display_size;
544 ELSIF p_page_count is null THEN
545 l_first_index:=1;
546 l_last_index:=p_display_size;
547 END IF;
548 IF l_last_index>x_search_count THEN
549 l_last_index:=x_search_count;
550 END IF;
551 FOR l_index in l_first_index..l_last_index LOOP
552 x_Agent_Acnt_Dtl_data(l_index):=l_temp_tbl(l_index);
553 END LOOP;
554 END IF;
555 END IF;
556
557 -- Standard Check Of p_commit.
558 IF FND_API.To_Boolean(p_commit) THEN
559 COMMIT WORK;
560 END IF;
561 -- Standard callto get message count and if count is 1, get message info.
562 FND_MSG_PUB.Count_And_Get
563 ( p_count => x_msg_count,
564 p_data => x_msg_data
565 );
566 EXCEPTION
567 WHEN FND_API.G_EXC_ERROR THEN
568 ROLLBACK TO ListAgentAccountDetails_PUB;
569 x_return_status := FND_API.G_RET_STS_ERROR ;
570 FND_MSG_PUB.Count_And_Get
571 ( p_count => x_msg_count,
572 p_data => x_msg_data
573 );
574 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
575 ROLLBACK TO ListAgentAccountDetails_PUB;
576 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
577 FND_MSG_PUB.Count_And_Get
578 ( p_count => x_msg_count,
579 p_data => x_msg_data
580 );
581 WHEN OTHERS THEN
582 ROLLBACK TO ListAgentAccountDetails_PUB;
583 x_return_status := FND_API.G_RET_STS_ERROR;
584 IF FND_MSG_PUB.Check_Msg_Level
585 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
586 THEN
587 FND_MSG_PUB.Add_Exc_Msg
588 ( G_PKG_NAME,
589 l_api_name
590 );
591 END IF;
592 FND_MSG_PUB.Count_And_Get
593 ( p_count => x_msg_count,
594 p_data => x_msg_data
595 );
596
597 END ListAgentAccountDetails;
598
599 PROCEDURE ListAccountDetails (p_api_version_number IN NUMBER,
600 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
601 p_commit IN VARCHAR2 := FND_API.G_FALSE,
602 p_EMAIL_ACCOUNT_ID IN NUMBER :=null,
603 x_return_status OUT NOCOPY VARCHAR2,
604 x_msg_count OUT NOCOPY NUMBER,
605 x_msg_data OUT NOCOPY VARCHAR2,
606 x_Acnt_Details_tbl OUT NOCOPY ACNTDETAILS_tbl_type
607 ) is
608 CURSOR account_details_csr IS
609
610 SELECT from_name,
611 user_name,
612 email_address,
613 nvl(nvl(reply_to_address,return_address),email_address) reply_to_address,
614 email_account_id,
615 out_host,
616 out_port,
617 template_category,
618 ssl_connection_flag
619 FROM IEM_MSTEMAIL_ACCOUNTS
620 WHERE email_account_id=p_EMAIL_ACCOUNT_ID;
621
622 l_email_index number:=1;
623
624 l_api_name VARCHAR2(255):='ListAccountDetails';
625 l_api_version_number NUMBER:=1.0;
626
627 BEGIN
628 -- Standard Start of API savepoint
629 SAVEPOINT ListAccountDetails_PUB;
630 -- Standard call to check for call compatibility.
631 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
632 p_api_version_number,
633 l_api_name,
634 G_PKG_NAME)
635 THEN
636 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
637 END IF;
638 -- Initialize message list if p_init_msg_list is set to TRUE.
639 IF FND_API.to_Boolean( p_init_msg_list )
640 THEN
641 FND_MSG_PUB.initialize;
642 END IF;
643 -- Initialize API return status to SUCCESS
644 x_return_status := FND_API.G_RET_STS_SUCCESS;
645 IF p_email_account_id is not null then
646 FOR account_det_rec in account_details_csr
647 LOOP
648 x_Acnt_Details_tbl(l_email_index).account_name:=account_det_rec.from_name;
649 x_Acnt_Details_tbl(l_email_index).email_user:=account_det_rec.user_name;
650 x_Acnt_Details_tbl(l_email_index).email_address:=account_det_rec.email_address;
651 x_Acnt_Details_tbl(l_email_index).reply_to_address:=account_det_rec.reply_to_address;
652 x_Acnt_Details_tbl(l_email_index).from_name:=account_det_rec.from_name;
653 x_Acnt_Details_tbl(l_email_index).email_account_id:=account_det_rec.email_account_id;
654 x_Acnt_Details_tbl(l_email_index).smtp_server:=account_det_rec.out_host;
655 x_Acnt_Details_tbl(l_email_index).port:=account_det_rec.out_port;
656 x_Acnt_Details_tbl(l_email_index).template_category_id:=account_det_rec.template_category;
660 END LOOP;
657 x_Acnt_Details_tbl(l_email_index).ssl_connection_flag:=account_det_rec.ssl_connection_flag;
658 l_email_index:=l_email_index+1;
659
661 END IF;
662 -- Standard Check Of p_commit.
663 IF FND_API.To_Boolean(p_commit) THEN
664 COMMIT WORK;
665 END IF;
666 -- Standard callto get message count and if count is 1, get message info.
667 FND_MSG_PUB.Count_And_Get
668 ( p_count => x_msg_count,
669 p_data => x_msg_data
670 );
671 EXCEPTION
672 WHEN FND_API.G_EXC_ERROR THEN
673 ROLLBACK TO ListAccountDetails_PUB;
674 x_return_status := FND_API.G_RET_STS_ERROR ;
675 FND_MSG_PUB.Count_And_Get
676 ( p_count => x_msg_count,
677 p_data => x_msg_data
678 );
679 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
680 ROLLBACK TO ListAccountDetails_PUB;
681 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
682 FND_MSG_PUB.Count_And_Get
683 ( p_count => x_msg_count,
684 p_data => x_msg_data
685 );
686 WHEN OTHERS THEN
687 ROLLBACK TO ListAccountDetails_PUB;
688 x_return_status := FND_API.G_RET_STS_ERROR;
689 IF FND_MSG_PUB.Check_Msg_Level
690 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
691 THEN
692 FND_MSG_PUB.Add_Exc_Msg
693 ( G_PKG_NAME,
694 l_api_name
695 );
696 END IF;
697 FND_MSG_PUB.Count_And_Get
698 ( p_count => x_msg_count,
699 p_data => x_msg_data
700 );
701
702 END ListAccountDetails;
703 END IEM_EMAILACCOUNT_PUB ;