[Home] [Help]
PACKAGE BODY: APPS.IEM_EMAILACCOUNT_PUB
Source
1 PACKAGE BODY IEM_EMAILACCOUNT_PUB as
2 /* $Header: iempactb.pls 120.9 2006/05/03 15:30:44 rtripath 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
249 PROCEDURE ListAgentAccountDetails (p_api_version_number IN NUMBER,
250 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
251 p_commit IN VARCHAR2 := FND_API.G_FALSE,
252 p_EMAIL_ACCOUNT_ID IN NUMBER,
253 p_ROLEid IN NUMBER:=-1,
254 p_Resource_id IN NUMBER:=-1,
255 p_search_criteria IN VARCHAR2:=null,
256 p_display_size in NUMBER:=null,
257 p_page_count in NUMBER:=null,
258 p_sort_by in VARCHAR2:='F',
259 p_sort_order in NUMBER:=1,
260 x_return_status OUT NOCOPY VARCHAR2,
261 x_msg_count OUT NOCOPY NUMBER,
262 x_search_count OUT NOCOPY NUMBER,
263 x_msg_data OUT NOCOPY VARCHAR2,
264 x_Agent_Acnt_Dtl_data OUT NOCOPY AGNTACNTDETAILS_tbl_type
265 ) is
266
267 l_api_name VARCHAR2(255):='ListAgentAccountDetails';
268 l_api_version_number NUMBER:=1.0;
269 Type get_data is REF CURSOR;-- RETURN Agent_Acnt_Dtl_tbl;
270 email_cur get_data;
271 l_counter number:=0;
272 l_order_by varchar2(255);
273 l_order varchar2(255);
274 l_where varchar2(1000);
275 l_stmt varchar2(600);
276 l_temp_tbl AGNTACNTDETAILS_tbl_type;
277 -- l_start_index number:=0;
278 l_first_index number:=0;
279 l_last_index number:=0;
280 l_roleid NUMBER:= 0;
281 l_string varchar2(32767):='';
282 l_resource_id number;
283 l_resource_name varchar2(360);
284 l_user_name varchar2(256);
285 l_responsibility_name varchar2(100);
286 l_last_login_time varchar2(256);
287 l_cursorID INTEGER;
288 l_dummy INTEGER;
289 l_role_Str varchar2(1000);
290
291 BEGIN
292 -- Standard Start of API savepoint
293 SAVEPOINT ListAgentAccountDetails_PUB;
294 -- Standard call to check for call compatibility.
295 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
296 p_api_version_number,
297 l_api_name,
298 G_PKG_NAME)
299 THEN
300 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
301 END IF;
302 SAVEPOINT ListAgentAccountDetails_pvt;
303 x_return_status := FND_API.G_RET_STS_SUCCESS;
304
305 l_where:=' AND agnt.email_account_id= :email_account_id';
306 IF p_search_criteria is not null THEN
307 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)) ';
308 END IF;
309 IF p_roleid <> -1 THEN
310
311 if p_roleid = 2 then
312 l_role_str:='resp.responsibility_key = ''EMAIL_CENTER_SUPERVISOR''' ;
313 else
314 l_role_str:='resp.responsibility_key = ''IEM_SA_AGENT''' ;
315 end if;
316 l_where:= l_where||' and resp.application_id=680 and '||l_role_str;
317 ELSE
318 l_where:= l_where||' and resp.responsibility_key in (''EMAIL_CENTER_SUPERVISOR'', ''IEM_SA_AGENT'' ) and (res.user_id,respgrp.responsibility_id)
319 IN (select respgrp.user_id,max(respgrp.responsibility_id)
320 from fnd_user_resp_groups respgrp,fnd_responsibility resp
321 where respgrp.responsibility_id=resp.responsibility_id
322 and resp.application_id=680 and (resp.responsibility_key =''EMAIL_CENTER_SUPERVISOR'' or resp.responsibility_key=''IEM_SA_AGENT'')
323 group by respgrp.user_id) ';
324 END IF;
325 IF p_resource_id <> -1 THEN
326 l_where:= l_where||' and agnt.resource_id<> :resource_id';
327 END IF;
328
329 IF p_sort_order=1 THEN
330 l_order:=' ASC';
331 ELSE
332 l_order:=' DESC';
333 END IF;
334 IF p_sort_by = 'F' THEN
335 l_order_by:=' Order BY res.source_last_name '||l_order || ', res.source_first_name '||l_order;
336 ELSIF p_sort_by='U' THEN
337 l_order_by:=' ORDER BY res.user_name '||l_order;
338 ELSIF p_sort_by='R' THEN
339 l_order_by:=' ORDER BY resp.responsibility_name '||l_order;
340 END IF;
341
342 l_string := 'select agnt.resource_id, concat(concat(res.source_last_name, '', ''), res.source_first_name) as resource_name, res.user_name,
343 resptl.responsibility_name
344 from iem_agents agnt, fnd_responsibility resp,fnd_user_resp_groups respgrp,
345 jtf_rs_resource_extns res, fnd_user fu,fnd_responsibility_tl resptl
346 where agnt.resource_id=res.resource_id and res.user_id=respgrp.user_id
347 and resp.application_id=680
348 and resp.responsibility_id=respgrp.responsibility_id
349 and respgrp.user_id=fu.user_id
350 and resptl.application_id=680
351 and resptl.responsibility_id=resp.responsibility_id
352 and resptl.LANGUAGE = USERENV (''LANG'')
353 and trunc(sysdate) between trunc(nvl(respgrp.start_date, sysdate))
354 and trunc(nvl(respgrp.end_date, sysdate))
355 and trunc(sysdate) between trunc(nvl(fu.start_date, sysdate))
356 and trunc(nvl(fu.end_date, sysdate))
357 and trunc(sysdate) between trunc(nvl(res.start_date_active, sysdate))
358 and trunc(nvl(res.end_date_active, sysdate))
359 and res.resource_id in ( select unique rel.role_resource_id from jtf_rs_role_relations rel where
360 rel.role_id in (28, 29, 30) and rel.delete_flag = ''N''
361 and rel.role_resource_type = ''RS_INDIVIDUAL''
362 and trunc(sysdate) between trunc(nvl(rel.start_date_active, sysdate))
363 and trunc(nvl(rel.end_date_active, sysdate)) ) ';
364 l_string := l_string ||l_where||l_order_by;
365 l_cursorID := DBMS_SQL.OPEN_CURSOR;
366 DBMS_SQL.PARSE(l_cursorID, l_string, DBMS_SQL.native);
367
368 IF p_search_criteria is not null THEN
369 DBMS_SQL.BIND_VARIABLE(l_cursorID, ':search_criteria', p_search_criteria);
370 end if;
371 /*
372 IF p_roleid <> -1 THEN
373 DBMS_SQL.BIND_VARIABLE(l_cursorID, ':roleid', l_roleid);
374 END IF;
375 */
376 IF p_resource_id <> -1 THEN
377 DBMS_SQL.BIND_VARIABLE(l_cursorID, ':resource_id', p_resource_id);
378 END IF;
379
380 DBMS_SQL.BIND_VARIABLE(l_cursorID, ':email_account_id', p_email_account_id);
381
382 DBMS_SQL.DEFINE_COLUMN(l_cursorID, 1, l_resource_id);
383 DBMS_SQL.DEFINE_COLUMN(l_cursorID, 2, l_resource_name, 360);
384 DBMS_SQL.DEFINE_COLUMN(l_cursorID, 3, l_user_name, 256);
385 DBMS_SQL.DEFINE_COLUMN(l_cursorID, 4, l_responsibility_name, 100);
386
387 l_dummy := DBMS_SQL.EXECUTE(l_cursorID);
388 l_temp_tbl.delete;
389 l_counter:=1;
393 EXIT;
390
391 LOOP
392 IF (DBMS_SQL.FETCH_ROWS(l_cursorID) = 0) THEN
394 END IF;
395
396 DBMS_SQL.COLUMN_VALUE(l_cursorID, 1, l_resource_id);
397 DBMS_SQL.COLUMN_VALUE(l_cursorID, 2, l_resource_name);
398 DBMS_SQL.COLUMN_VALUE(l_cursorID, 3, l_user_name);
399 DBMS_SQL.COLUMN_VALUE(l_cursorID, 4, l_responsibility_name);
400
401 select to_char(max(begin_date_time), 'MM/DD/RRRR HH24:MI:SS') into l_last_login_time
402 from ieu_sh_sessions where application_id=680 and resource_id=l_resource_id;
403
404 l_temp_tbl(l_counter).resource_id := l_resource_id;
405 l_temp_tbl(l_counter).resource_name := l_resource_name;
406 l_temp_tbl(l_counter).user_name := l_user_name;
407 l_temp_tbl(l_counter).role := l_responsibility_name;
408 l_temp_tbl(l_counter).last_login_time := l_last_login_time;
409
410 l_counter:=l_counter+1;
411
412 END LOOP;
413
414 DBMS_SQL.CLOSE_CURSOR(l_cursorID);
415
416 x_search_count:=0;
417 x_search_count:=l_temp_tbl.count;
418 IF l_temp_tbl.count>0 THEN
419 --x_total_message:=l_temp_tbl.count;
420 IF p_display_size is null THEN
421 x_Agent_Acnt_Dtl_data:=l_temp_tbl;
422 ELSE
423 IF p_page_count is not null THEN
424 l_first_index:=p_page_count*p_display_size - p_display_size+1;
425 l_last_index:=p_page_count*p_display_size;
426 ELSIF p_page_count is null THEN
427 l_first_index:=1;
428 l_last_index:=p_display_size;
429 END IF;
430 IF l_last_index>x_search_count THEN
431 l_last_index:=x_search_count;
432 END IF;
433 FOR l_index in l_first_index..l_last_index LOOP
434 x_Agent_Acnt_Dtl_data(l_index):=l_temp_tbl(l_index);
435 END LOOP;
436 END IF;
437 END IF;
438
439 -- Standard Check Of p_commit.
440 IF FND_API.To_Boolean(p_commit) THEN
441 COMMIT WORK;
442 END IF;
443 -- Standard callto get message count and if count is 1, get message info.
444 FND_MSG_PUB.Count_And_Get
445 ( p_count => x_msg_count,
446 p_data => x_msg_data
447 );
448 EXCEPTION
449 WHEN FND_API.G_EXC_ERROR THEN
450 ROLLBACK TO ListAgentAccountDetails_PUB;
451 x_return_status := FND_API.G_RET_STS_ERROR ;
452 FND_MSG_PUB.Count_And_Get
453 ( p_count => x_msg_count,
454 p_data => x_msg_data
455 );
456 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
457 ROLLBACK TO ListAgentAccountDetails_PUB;
458 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
459 FND_MSG_PUB.Count_And_Get
460 ( p_count => x_msg_count,
461 p_data => x_msg_data
462 );
463 WHEN OTHERS THEN
464 ROLLBACK TO ListAgentAccountDetails_PUB;
465 x_return_status := FND_API.G_RET_STS_ERROR;
466 IF FND_MSG_PUB.Check_Msg_Level
467 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
468 THEN
469 FND_MSG_PUB.Add_Exc_Msg
470 ( G_PKG_NAME,
471 l_api_name
472 );
473 END IF;
474 FND_MSG_PUB.Count_And_Get
475 ( p_count => x_msg_count,
476 p_data => x_msg_data
477 );
478
479 END ListAgentAccountDetails;
480
481 PROCEDURE ListAccountDetails (p_api_version_number IN NUMBER,
482 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
483 p_commit IN VARCHAR2 := FND_API.G_FALSE,
484 p_EMAIL_ACCOUNT_ID IN NUMBER :=null,
485 x_return_status OUT NOCOPY VARCHAR2,
486 x_msg_count OUT NOCOPY NUMBER,
487 x_msg_data OUT NOCOPY VARCHAR2,
488 x_Acnt_Details_tbl OUT NOCOPY ACNTDETAILS_tbl_type
489 ) is
490 CURSOR account_details_csr IS
491
492 SELECT from_name,
493 user_name,
494 email_address,
495 nvl(nvl(reply_to_address,return_address),email_address) reply_to_address,
496 email_account_id,
497 out_host,
498 out_port,
499 template_category
500 FROM IEM_MSTEMAIL_ACCOUNTS
501 WHERE email_account_id=p_EMAIL_ACCOUNT_ID;
502
503 l_email_index number:=1;
504
505 l_api_name VARCHAR2(255):='ListAccountDetails';
506 l_api_version_number NUMBER:=1.0;
507
508 BEGIN
509 -- Standard Start of API savepoint
510 SAVEPOINT ListAccountDetails_PUB;
511 -- Standard call to check for call compatibility.
512 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
513 p_api_version_number,
514 l_api_name,
515 G_PKG_NAME)
516 THEN
517 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
518 END IF;
519 -- Initialize message list if p_init_msg_list is set to TRUE.
520 IF FND_API.to_Boolean( p_init_msg_list )
521 THEN
522 FND_MSG_PUB.initialize;
523 END IF;
524 -- Initialize API return status to SUCCESS
525 x_return_status := FND_API.G_RET_STS_SUCCESS;
526 IF p_email_account_id is not null then
527 FOR account_det_rec in account_details_csr
528 LOOP
529 x_Acnt_Details_tbl(l_email_index).account_name:=account_det_rec.from_name;
530 x_Acnt_Details_tbl(l_email_index).email_user:=account_det_rec.user_name;
531 x_Acnt_Details_tbl(l_email_index).email_address:=account_det_rec.email_address;
535 x_Acnt_Details_tbl(l_email_index).smtp_server:=account_det_rec.out_host;
532 x_Acnt_Details_tbl(l_email_index).reply_to_address:=account_det_rec.reply_to_address;
533 x_Acnt_Details_tbl(l_email_index).from_name:=account_det_rec.from_name;
534 x_Acnt_Details_tbl(l_email_index).email_account_id:=account_det_rec.email_account_id;
536 x_Acnt_Details_tbl(l_email_index).port:=account_det_rec.out_port;
537 x_Acnt_Details_tbl(l_email_index).template_category_id:=account_det_rec.template_category;
538 l_email_index:=l_email_index+1;
539
540 END LOOP;
541 END IF;
542 -- Standard Check Of p_commit.
543 IF FND_API.To_Boolean(p_commit) THEN
544 COMMIT WORK;
545 END IF;
546 -- Standard callto get message count and if count is 1, get message info.
547 FND_MSG_PUB.Count_And_Get
548 ( p_count => x_msg_count,
549 p_data => x_msg_data
550 );
551 EXCEPTION
552 WHEN FND_API.G_EXC_ERROR THEN
553 ROLLBACK TO ListAccountDetails_PUB;
554 x_return_status := FND_API.G_RET_STS_ERROR ;
555 FND_MSG_PUB.Count_And_Get
556 ( p_count => x_msg_count,
557 p_data => x_msg_data
558 );
559 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
560 ROLLBACK TO ListAccountDetails_PUB;
561 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
562 FND_MSG_PUB.Count_And_Get
563 ( p_count => x_msg_count,
564 p_data => x_msg_data
565 );
566 WHEN OTHERS THEN
567 ROLLBACK TO ListAccountDetails_PUB;
568 x_return_status := FND_API.G_RET_STS_ERROR;
569 IF FND_MSG_PUB.Check_Msg_Level
570 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
571 THEN
572 FND_MSG_PUB.Add_Exc_Msg
573 ( G_PKG_NAME,
574 l_api_name
575 );
576 END IF;
577 FND_MSG_PUB.Count_And_Get
578 ( p_count => x_msg_count,
579 p_data => x_msg_data
580 );
581
582 END ListAccountDetails;
583 END IEM_EMAILACCOUNT_PUB ;