[Home] [Help]
PACKAGE BODY: APPS.IEM_AGENT_ACT_PVT
Source
1 PACKAGE BODY IEM_AGENT_ACT_PVT as
2 /* $Header: iemvagnb.pls 120.1.12010000.2 2009/07/14 09:09:35 shramana ship $*/
3 G_PKG_NAME CONSTANT varchar2(30) :='IEM_AGENT_ACT_PVT ';
4
5 PROCEDURE create_item (p_api_version_number IN NUMBER,
6 p_init_msg_list IN VARCHAR2 ,
7 p_commit IN VARCHAR2 ,
8 p_resource_id IN VARCHAR2,
9 p_email_account_id IN NUMBER,
10 p_signature IN VARCHAR2,
11 p_CREATED_BY NUMBER,
12 p_CREATION_DATE DATE,
13 p_LAST_UPDATED_BY NUMBER,
14 p_LAST_UPDATE_DATE DATE,
15 p_LAST_UPDATE_LOGIN NUMBER,
16 p_ATTRIBUTE1 VARCHAR2,
17 p_ATTRIBUTE2 VARCHAR2,
18 p_ATTRIBUTE3 VARCHAR2,
19 p_ATTRIBUTE4 VARCHAR2,
20 p_ATTRIBUTE5 VARCHAR2,
21 p_ATTRIBUTE6 VARCHAR2,
22 p_ATTRIBUTE7 VARCHAR2,
23 p_ATTRIBUTE8 VARCHAR2,
24 p_ATTRIBUTE9 VARCHAR2,
25 p_ATTRIBUTE10 VARCHAR2,
26 p_ATTRIBUTE11 VARCHAR2,
27 p_ATTRIBUTE12 VARCHAR2,
28 p_ATTRIBUTE13 VARCHAR2,
29 p_ATTRIBUTE14 VARCHAR2,
30 p_ATTRIBUTE15 VARCHAR2,
31 x_return_status OUT NOCOPY VARCHAR2,
32 x_msg_count OUT NOCOPY NUMBER,
33 x_msg_data OUT NOCOPY VARCHAR2
34 ) is
35 l_api_name VARCHAR2(255):='create_item';
36 l_api_version_number NUMBER:=1.0;
37 l_seq_id number;
38 l_grp_cnt number;
39 l_stat varchar2(20);
40 l_count number;
41 l_data varchar2(300);
42 l_agent_account_id number;
43 l_CREATED_BY NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('USER_ID'));
44 l_LAST_UPDATED_BY NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('USER_ID')) ;
45 l_LAST_UPDATE_LOGIN NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('LOGIN_ ID')) ;
46
47 BEGIN
48 x_return_status := FND_API.G_RET_STS_SUCCESS;
49 select iem_agents_s1.nextval into l_agent_account_id
50 from dual;
51 INSERT INTO IEM_AGENTS (
52 AGENT_ID ,
53 EMAIL_ACCOUNT_ID ,
54 RESOURCE_ID ,
55 SIGNATURE ,
56 CREATED_BY ,
57 CREATION_DATE ,
58 LAST_UPDATED_BY ,
59 LAST_UPDATE_DATE ,
60 LAST_UPDATE_LOGIN ,
61 ATTRIBUTE1 ,
62 ATTRIBUTE2 ,
63 ATTRIBUTE3 ,
64 ATTRIBUTE4 ,
65 ATTRIBUTE5 ,
66 ATTRIBUTE6 ,
67 ATTRIBUTE7 ,
68 ATTRIBUTE8 ,
69 ATTRIBUTE9 ,
70 ATTRIBUTE10 ,
71 ATTRIBUTE11 ,
72 ATTRIBUTE12 ,
73 ATTRIBUTE13 ,
74 ATTRIBUTE14 ,
75 ATTRIBUTE15
76 )
77 VALUES
78 (
79 l_AGENT_ACCOUNT_ID ,
80 p_EMAIL_ACCOUNT_ID ,
81 p_resource_id ,
82 decode(p_SIGNATURE,FND_API.G_MISS_CHAR,NULL,p_signature),
83 decode(p_CREATED_BY,null,-1,p_CREATED_BY),
84 sysdate,
85 decode(p_LAST_UPDATED_BY,null,-1,p_LAST_UPDATED_BY),
86 sysdate,
87 decode(p_LAST_UPDATE_LOGIN,null,-1,p_LAST_UPDATE_LOGIN),
88 p_ATTRIBUTE1,
89 p_ATTRIBUTE2,
90 p_ATTRIBUTE3,
91 p_ATTRIBUTE4,
92 p_ATTRIBUTE5,
93 p_ATTRIBUTE6,
94 p_ATTRIBUTE7,
95 p_ATTRIBUTE8,
96 p_ATTRIBUTE9,
97 p_ATTRIBUTE10,
98 p_ATTRIBUTE11,
99 p_ATTRIBUTE12,
100 p_ATTRIBUTE13,
101 p_ATTRIBUTE14,
102 p_ATTRIBUTE15
103 );
104
105 --Insert into Comp_Rt-Stats for Client cache to update.
106 --No error handling here.
107 IEM_COMP_RT_STATS_PVT.create_item(p_api_version_number =>1.0,
108 p_init_msg_list => FND_API.G_FALSE,
109 p_commit => FND_API.G_FALSE,
110 p_type => 'AGENT_ACCOUNT',
111 p_param => 'CREATE',
112 p_value => l_agent_account_id,
113 x_return_status => l_stat,
114 x_msg_count => l_count,
115 x_msg_data => l_data);
116
117 -- Standard callto get message count and if count is 1, get message info.
118 FND_MSG_PUB.Count_And_Get
119 ( p_count => x_msg_count,
120 p_data => x_msg_data
121 );
122 EXCEPTION
123 WHEN FND_API.G_EXC_ERROR THEN
124 x_return_status := FND_API.G_RET_STS_ERROR ;
125 FND_MSG_PUB.Count_And_Get
126 ( p_count => x_msg_count,
127 p_data => x_msg_data
128 );
129 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
130 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
131 FND_MSG_PUB.Count_And_Get
132 ( p_count => x_msg_count,
133 p_data => x_msg_data
134 );
135 WHEN OTHERS THEN
136 x_return_status := FND_API.G_RET_STS_ERROR;
137 IF FND_MSG_PUB.Check_Msg_Level
138 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
139 THEN
140 FND_MSG_PUB.Add_Exc_Msg
141 ( G_PKG_NAME ,
142 l_api_name
143 );
144 END IF;
145 FND_MSG_PUB.Count_And_Get
146 ( p_count => x_msg_count ,
147 p_data => x_msg_data
148 );
149
150 END create_item;
151
152 PROCEDURE delete_item (p_api_version_number IN NUMBER,
153 p_init_msg_list IN VARCHAR2 ,
154 p_commit IN VARCHAR2 ,
155 p_resource_id in number,
156 p_email_account_id in number,
157 x_return_status OUT NOCOPY VARCHAR2,
158 x_msg_count OUT NOCOPY NUMBER,
159 x_msg_data OUT NOCOPY VARCHAR2
160 ) is
161 l_api_name VARCHAR2(255):='delete_item';
162 l_api_version_number NUMBER:=1.0;
163
164 l_agent_id number;
165 l_stat varchar2(20);
166 l_count number;
167 l_data varchar2(300);
168
169 BEGIN
170 -- Standard call to check for call compatibility.
171 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
172 p_api_version_number,
173 l_api_name,
174 G_PKG_NAME)
175 THEN
176 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
177 END IF;
178 x_return_status := FND_API.G_RET_STS_SUCCESS;
179
180 select agent_id into l_agent_id
181 from IEM_AGENTS
182 where resource_id=p_resource_id
183 and email_account_id=p_email_account_id;
184
185 delete from IEM_AGENTS
186 where resource_id=p_resource_id
187 and email_account_id=p_email_account_id;
188
189 --Insert into Comp_Rt-Stats for Client cache to update.
190 --No error handling here.
191 IEM_COMP_RT_STATS_PVT.create_item(p_api_version_number =>1.0,
192 p_init_msg_list => FND_API.G_FALSE,
193 p_commit => FND_API.G_FALSE,
194 p_type => 'AGENT_ACCOUNT',
195 p_param => 'DELETE',
196 p_value => l_agent_id,
197 x_return_status => l_stat,
198 x_msg_count => l_count,
199 x_msg_data => l_data);
200
201 -- Standard Check Of p_commit.
202 IF FND_API.To_Boolean(p_commit) THEN
203 COMMIT WORK;
204 END IF;
205 -- Standard callto get message count and if count is 1, get message info.
206 FND_MSG_PUB.Count_And_Get
207 ( p_count => x_msg_count,
208 p_data => x_msg_data
209 );
210 EXCEPTION
211 WHEN FND_API.G_EXC_ERROR THEN
212 ROLLBACK TO delete_item_PVT;
213 x_return_status := FND_API.G_RET_STS_ERROR ;
214 FND_MSG_PUB.Count_And_Get
215 ( p_count => x_msg_count,
216 p_data => x_msg_data
217 );
218 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
219 ROLLBACK TO delete_item_PVT;
220 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
221 FND_MSG_PUB.Count_And_Get
222 ( p_count => x_msg_count,
223 p_data => x_msg_data
224 );
225 WHEN OTHERS THEN
226 ROLLBACK TO delete_item_PVT;
227 x_return_status := FND_API.G_RET_STS_ERROR;
228 IF FND_MSG_PUB.Check_Msg_Level
229 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
230 THEN
231 FND_MSG_PUB.Add_Exc_Msg
232 ( G_PKG_NAME ,
233 l_api_name
234 );
235 END IF;
236 FND_MSG_PUB.Count_And_Get
237 ( p_count => x_msg_count ,
238 p_data => x_msg_data
239 );
240
241 END delete_item;
242
243 PROCEDURE create_agntacct_by_agent (p_api_version_number IN NUMBER,
244 p_init_msg_list IN VARCHAR2 ,
245 p_commit IN VARCHAR2 ,
246 p_in_resource_tbl IN jtf_varchar2_Table_100,
247 p_email_account_id in number,
248 x_return_status OUT NOCOPY VARCHAR2,
249 x_msg_count OUT NOCOPY NUMBER,
250 x_msg_data OUT NOCOPY VARCHAR2
251 ) is
252 l_api_name VARCHAR2(255):='create_agntacct_by_agent';
253 l_api_version_number NUMBER:=1.0;
254 l_return_status varchar2(10);
255 l_msg_count number;
256 l_msg_data varchar2(255);
257 l_user_id JTF_RS_RESOURCE_EXTNS.USER_ID%TYPE;
258 l_user_name JTF_RS_RESOURCE_EXTNS.USER_NAME%TYPE;
259 l_res_name varchar2(720);
260 l_resource_param_value_id NUMBER;
261 l_agent_account_count NUMBER;
262 l_error_agent_count NUMBER:=0;
263 l_error_username varchar2(32000);
264 l_data_change Boolean := false;
265
266 BEGIN
267
268 SAVEPOINT create_agntacct_by_agent_PVT;
269 -- Standard call to check for call compatibility.
270 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
271 p_api_version_number,
272 l_api_name,
273 G_PKG_NAME)
274 THEN
275 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
276 END IF;
277
278 -- Initialize message list if p_init_msg_list is set to TRUE.
279 IF FND_API.to_Boolean( p_init_msg_list )
280 THEN
281 FND_MSG_PUB.initialize;
282 END IF;
283
284 x_return_status := FND_API.G_RET_STS_SUCCESS;
285
286 FOR i in 1..p_in_resource_tbl.count() LOOP
287
288 SELECT USER_ID, USER_NAME, SOURCE_LAST_NAME || ', ' || SOURCE_FIRST_NAME as RESOURCE_NAME
289 INTO l_user_id, l_user_name, l_res_name
290 FROM JTF_RS_RESOURCE_EXTNS
291 WHERE RESOURCE_ID = p_in_resource_tbl(i);
292
293
294 -- Check if the agent account already exist. If exist, skip and fetch next resource id in the loop
295 select count(*) into l_agent_account_count from iem_agents where resource_id=p_in_resource_tbl(i)
296 and email_account_id=p_email_account_id;
297
298 IF (l_agent_account_count = 0) THEN
299
300 l_data_change := true;
301
302 /*JTF_RS_RESOURCE_VALUES_PUB.CREATE_RS_RESOURCE_VALUES(
303 P_Api_Version => 1.0,
304 P_Init_Msg_List => FND_API.G_FALSE,
305 P_Commit => FND_API.G_FALSE,
306 P_resource_id => p_in_resource_tbl(i),
307 p_resource_param_id => 1,
308 p_value => 'IEM_DEFAULT_VALUE',
309 P_value_type => p_email_account_id,
310 X_Return_Status => l_return_status,
311 X_Msg_Count => l_msg_count,
312 X_Msg_Data => l_msg_data,
313 X_resource_param_value_id => l_resource_param_value_id);
314
315 IF l_return_status='S' THEN
316 */
317
318 IEM_AGENT_ACT_PVT.create_item(p_api_version_number=>1.0,
319 p_init_msg_list=>'F' ,
320 p_commit=>'F' ,
321 p_resource_id=>p_in_resource_tbl(i),
322 p_email_account_id=>p_email_account_id,
323 p_signature=>null,
324 p_CREATED_BY =>TO_NUMBER (FND_PROFILE.VALUE('USER_ID')),
325 p_CREATION_DATE =>SYSDATE,
326 p_LAST_UPDATED_BY =>TO_NUMBER (FND_PROFILE.VALUE('USER_ID')),
327 p_LAST_UPDATE_DATE =>SYSDATE,
328 p_LAST_UPDATE_LOGIN=>TO_NUMBER (FND_PROFILE.VALUE('LOGIN_ID')) ,
329 p_ATTRIBUTE1 =>null,
330 p_ATTRIBUTE2 =>null,
331 p_ATTRIBUTE3 =>null,
332 p_ATTRIBUTE4 =>null,
333 p_ATTRIBUTE5 =>null,
334 p_ATTRIBUTE6 =>null,
335 p_ATTRIBUTE7 =>null,
336 p_ATTRIBUTE8 =>null,
337 p_ATTRIBUTE9 =>null,
338 p_ATTRIBUTE10 =>null,
339 p_ATTRIBUTE11 =>null,
340 p_ATTRIBUTE12 =>null,
341 p_ATTRIBUTE13 =>null,
342 p_ATTRIBUTE14 =>null,
343 p_ATTRIBUTE15 =>null,
344 x_msg_count=>l_msg_count,
345 x_msg_data=>l_msg_data,
346 x_return_status=>l_return_status);
347
348 IF l_return_status<>'S' THEN
349 if l_error_agent_count < 21 then
350 l_error_username := l_error_username || ' ' || l_user_name || ',';
351 l_error_agent_count := l_error_agent_count + 1;
352 end if;
353 END IF; -- IEM_AGENT_ACT_PVT
354 /*ELSE
355 if l_error_agent_count < 21 then
356 l_error_username := l_error_username || ' ' || l_user_name || ',';
357 l_error_agent_count := l_error_agent_count + 1;
358 end if;
359 END IF; -- JTF_RS_RESOURCE_VALUES_PUB
360 */
361 /*if l_error_agent_count < 21 then
362 l_error_username := l_error_username || ' ' || l_user_name || ',';
363 l_error_agent_count := l_error_agent_count + 1;
364 end if;
365 */
366 END IF;
367
368 END LOOP;
369
370 if l_error_username is not null then
371 x_return_status := FND_API.G_RET_STS_ERROR;
372 l_error_username := RTRIM(l_error_username, ', ');
373
374 if l_error_agent_count > 20 then
375 l_error_username := l_error_username || '...';
376 end if;
377
378 FND_MESSAGE.SET_NAME('IEM', 'IEM_SSS_AGNTACCT1_CREATED');
379 FND_MESSAGE.SET_TOKEN('AGENT', l_error_username);
380 FND_MSG_PUB.ADD;
381 elsif l_data_change = false then
382 x_return_status := 'N'; -- indicate no data change
383 end if;
384
385 -- Standard Check Of p_commit.
386 IF FND_API.To_Boolean(p_commit) THEN
387 COMMIT WORK;
388 END IF;
389 -- Standard callto get message count and if count is 1, get message info.
390 FND_MSG_PUB.Count_And_Get
391 ( p_count => x_msg_count,
392 p_data => x_msg_data
393 );
394 EXCEPTION
395
396
397 WHEN FND_API.G_EXC_ERROR THEN
398 ROLLBACK TO create_agntacct_by_agent_PVT;
399 x_return_status := FND_API.G_RET_STS_ERROR ;
400 FND_MSG_PUB.Count_And_Get
401 ( p_count => x_msg_count,
402 p_data => x_msg_data
403 );
404 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
405 ROLLBACK TO create_agntacct_by_agent_PVT;
406 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
407 FND_MSG_PUB.Count_And_Get
408 ( p_count => x_msg_count,
409 p_data => x_msg_data
410 );
411 WHEN OTHERS THEN
412 ROLLBACK TO create_agntacct_by_agent_PVT;
413 x_return_status := FND_API.G_RET_STS_ERROR;
414 IF FND_MSG_PUB.Check_Msg_Level
415 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
416 THEN
417 FND_MSG_PUB.Add_Exc_Msg
418 ( G_PKG_NAME ,
419 l_api_name
420 );
421 END IF;
422 FND_MSG_PUB.Count_And_Get
423 ( p_count => x_msg_count ,
424 p_data => x_msg_data
425 );
426
427 END create_agntacct_by_agent;
428
429
430 PROCEDURE delete_agntacct_by_agent (p_api_version_number IN NUMBER,
431 p_init_msg_list IN VARCHAR2 ,
432 p_commit IN VARCHAR2 ,
433 p_out_resource_tbl IN jtf_varchar2_Table_100,
434 p_email_account_id in number,
435 x_return_status OUT NOCOPY VARCHAR2,
436 x_msg_count OUT NOCOPY NUMBER,
437 x_msg_data OUT NOCOPY VARCHAR2
438 ) is
439 l_api_name VARCHAR2(255):='delete_agntacct_by_agent';
440 l_api_version_number NUMBER:=1.0;
441 l_return_status varchar2(10);
442 l_msg_count number;
443 l_msg_data varchar2(255);
444 l_user_id JTF_RS_RESOURCE_EXTNS.USER_ID%TYPE;
445 l_user_name JTF_RS_RESOURCE_EXTNS.USER_NAME%TYPE;
446 l_agntacct varchar2(160);
447 l_res_name varchar2(720);
448 l_error_username varchar2(32000);
449 l_error_username1 varchar2(32000);
450 l_error_username2 varchar2(32000);
451 l_error_agent_count number:=0;
452 l_error_agent_count1 number:=0;
453 l_error_agent_count2 number:=0;
454 l_resource_param_value_id number;
455 l_object_version_number number;
456 l_agent_account_id NUMBER;
457 l_agent_account_count NUMBER;
458 l_email_count NUMBER;
459 l_compose_count NUMBER;
460 l_process_count NUMBER;
461 l_data_change Boolean := false;
462 l_resource_param_count number;
463 l_account_name varchar2(210);
464 l_is_clean Boolean;
465
466
467 BEGIN
468 SAVEPOINT delete_agntacct_by_agent_PVT;
469 -- Standard call to check for call compatibility.
470 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
471 p_api_version_number,
472 l_api_name,
473 G_PKG_NAME)
474 THEN
475 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
476 END IF;
477
478 -- Initialize message list if p_init_msg_list is set to TRUE.
479 IF FND_API.to_Boolean( p_init_msg_list )
480 THEN
481 FND_MSG_PUB.initialize;
482 END IF;
483
484 x_return_status := FND_API.G_RET_STS_SUCCESS;
485
486
487 FOR i in 1..p_out_resource_tbl.count() LOOP
488
489 SELECT USER_ID, USER_NAME, SOURCE_LAST_NAME || ', ' || SOURCE_FIRST_NAME as RESOURCE_NAME
490 INTO l_user_id, l_user_name, l_res_name
491 FROM JTF_RS_RESOURCE_EXTNS
492 WHERE RESOURCE_ID = p_out_resource_tbl(i);
493
494
495 -- Check if the agent account already non-exist. If non-exist, skip and fetch next resource id in the loop
496 select count(*) into l_agent_account_count from iem_agents where resource_id=p_out_resource_tbl(i)
497 and email_account_id=p_email_account_id;
498
499 IF (l_agent_account_count <> 0) THEN
500
501 l_data_change := true;
502
503 -- Check any Agent inbox messages or composing messages or in-processing emails in Outbox Processor
504 IEM_CLIENT_PUB.isAgentInboxClean(p_api_version_number=>1.0,
505 p_init_msg_list=>'F' ,
506 p_commit=>'F' ,
507 p_resource_id => p_out_resource_tbl(i),
508 p_email_account_id => p_email_account_id,
509 x_is_clean => l_is_clean,
510 x_msg_count=>l_msg_count,
511 x_msg_data=>l_msg_data,
512 x_return_status=>l_return_status);
513
514 IF l_return_status<>'S' THEN
515 if l_error_agent_count1 < 21 then
516 l_error_username1 := l_error_username1 || ' ' || l_user_name || ',';
517 l_error_agent_count1 := l_error_agent_count1 + 1;
518 end if;
519 ELSE
520
521 IF (l_is_clean = false) THEN
522 if l_error_agent_count2 < 21 then
523 l_error_username2 := l_error_username2 || ' ' || l_user_name || ',';
524 l_error_agent_count2 := l_error_agent_count2 + 1;
525 end if;
526
527 ELSE
528
529 IEM_AGENT_ACT_PVT.delete_item(p_api_version_number=>1.0,
530 p_init_msg_list=>'F' ,
531 p_commit=>'F' ,
532 p_resource_id => p_out_resource_tbl(i),
533 p_email_account_id => p_email_account_id,
534 x_msg_count=>l_msg_count,
535 x_msg_data=>l_msg_data,
536 x_return_status=>l_return_status);
537
538 IF l_return_status<>'S' THEN
539
540 /* select count(*) into l_resource_param_count from jtf_rs_resource_values
541 where value_type=p_email_account_id and resource_id=p_out_resource_tbl(i) and value='IEM_DEFAULT_VALUE';
542
543 if (l_resource_param_count > 0) then
544
545 select resource_param_value_id, object_version_number into l_resource_param_value_id, l_object_version_number from jtf_rs_resource_values
546 where value_type=p_email_account_id and resource_id=p_out_resource_tbl(i) and value='IEM_DEFAULT_VALUE';
547
548 JTF_RS_RESOURCE_VALUES_PUB.DELETE_RS_RESOURCE_VALUES(
549 P_Api_Version => 1.0,
550 P_Init_Msg_List => FND_API.G_FALSE,
551 P_Commit => FND_API.G_FALSE,
552 p_resource_param_value_id => l_resource_param_value_id,
553 p_object_version_number => l_object_version_number,
554 X_Return_Status => l_return_status,
555 X_Msg_Count => l_msg_count,
556 X_Msg_Data => l_msg_data);
557
558 IF l_return_status<>'S' THEN
559 if l_error_agent_count < 21 then
560 l_error_username := l_error_username || ' ' || l_user_name || ',';
561 l_error_agent_count := l_error_agent_count + 1;
562 end if;
563 END IF; -- IF THEN - JTF_RS_RESOURCE_VALUES_PUB
564 end if; -- if (l_resource_param_count > 0) then
565 ELSE
566 */ if l_error_agent_count < 21 then
567 l_error_username := l_error_username || ' ' || l_user_name || ',';
568 l_error_agent_count := l_error_agent_count + 1;
569 end if;
570 END IF; -- IF THEN - IEM_AGENT_ACT_PVT
571
572 END IF; -- is inbox clean
573
574 END IF; -- IF THEN - IEM_CLIENT_PUB
575
576 END IF; -- l_agent_acount_account<>0
577
578 END LOOP;
579
580
581 if l_error_username is not null then
582 x_return_status := FND_API.G_RET_STS_ERROR;
583 l_error_username := RTRIM(l_error_username, ', ');
584
585 if l_error_agent_count > 20 then
586 l_error_username := l_error_username || '...';
587 end if;
588
589 FND_MESSAGE.SET_NAME('IEM', 'IEM_SSS_AGNTACCT1_DELETED');
590 FND_MESSAGE.SET_TOKEN('AGENT', l_error_username);
591 FND_MSG_PUB.ADD;
592
593 end if;
594
595 if l_error_username1 is not null then
596 x_return_status := FND_API.G_RET_STS_ERROR ;
597 l_error_username1 := RTRIM(l_error_username1, ', ');
598
599 if l_error_agent_count1 > 20 then
600 l_error_username1 := l_error_username1 || '...';
601 end if;
602
603
604 FND_MESSAGE.SET_NAME('IEM','IEM_SSS_AGNTACCT9_DELETED');
605 FND_MESSAGE.SET_TOKEN('AGENT', l_error_username1);
606 FND_MESSAGE.SET_TOKEN('ACCOUNT', l_account_name);
607 FND_MSG_PUB.Add;
608 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
609 end if;
610
611 if l_error_username2 is not null then
612 x_return_status := FND_API.G_RET_STS_ERROR;
613 l_error_username2 := RTRIM(l_error_username2, ', ');
614
615 if l_error_agent_count2 > 20 then
616 l_error_username2 := l_error_username2 || '...';
617 end if;
618
619
620 FND_MESSAGE.SET_NAME('IEM', 'IEM_SSS_AGNTACCT5_DELETED');
621 FND_MESSAGE.SET_TOKEN('AGENT', l_error_username2);
622 FND_MESSAGE.SET_TOKEN('ACCOUNT', l_account_name);
623 FND_MSG_PUB.ADD;
624
625 end if;
626
627 if l_error_username is null and l_error_username1 is null and l_error_username2 is null and l_data_change=false then
628 x_return_status := 'N'; -- indicate no change in data
629 end if;
630
631 -- Standard Check Of p_commit.
632 IF FND_API.To_Boolean(p_commit) THEN
633 COMMIT WORK;
634 END IF;
635 -- Standard callto get message count and if count is 1, get message info.
636 FND_MSG_PUB.Count_And_Get
637 ( p_count => x_msg_count,
638 p_data => x_msg_data
639 );
640 EXCEPTION
641 WHEN FND_API.G_EXC_ERROR THEN
642 ROLLBACK TO delete_agntacct_by_agent_PVT;
643 x_return_status := FND_API.G_RET_STS_ERROR ;
644 FND_MSG_PUB.Count_And_Get
645 ( p_count => x_msg_count,
646 p_data => x_msg_data
647 );
648 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
649 ROLLBACK TO delete_agntacct_by_agent_PVT;
650 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
651 FND_MSG_PUB.Count_And_Get
652 ( p_count => x_msg_count,
653 p_data => x_msg_data
654 );
655 WHEN OTHERS THEN
656 ROLLBACK TO delete_agntacct_by_agent_PVT;
657 x_return_status := FND_API.G_RET_STS_ERROR;
658 IF FND_MSG_PUB.Check_Msg_Level
659 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
660 THEN
661 FND_MSG_PUB.Add_Exc_Msg
662 ( G_PKG_NAME ,
663 l_api_name
664 );
665 END IF;
666 FND_MSG_PUB.Count_And_Get
667 ( p_count => x_msg_count ,
668 p_data => x_msg_data
669 );
670
671 END delete_agntacct_by_agent;
672
673
674 PROCEDURE update_agntacct_by_agent_wrap (p_api_version_number IN NUMBER,
675 p_init_msg_list IN VARCHAR2 ,
676 p_commit IN VARCHAR2 ,
677 p_in_resource_tbl IN jtf_varchar2_Table_100,
678 p_out_resource_tbl IN jtf_varchar2_Table_100,
679 p_email_account_id in number,
680 x_return_status OUT NOCOPY VARCHAR2,
681 x_msg_count OUT NOCOPY NUMBER,
682 x_msg_data OUT NOCOPY VARCHAR2
683 ) is
684 l_api_name VARCHAR2(255):='update_agntacct_by_agent_wrap';
685 l_api_version_number NUMBER:=1.0;
686 l_return_status varchar2(10);
687 l_return_status1 varchar2(10):='';
688 l_msg_count number;
689 l_msg_data varchar2(255);
690
691 BEGIN
692 SAVEPOINT agntacct_by_agent_wrap;
693 -- Standard call to check for call compatibility.
694 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
695 p_api_version_number,
696 l_api_name,
697 G_PKG_NAME)
698 THEN
699 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
700 END IF;
701
702 -- Initialize message list if p_init_msg_list is set to TRUE.
703 IF FND_API.to_Boolean( p_init_msg_list )
704 THEN
705 FND_MSG_PUB.initialize;
706 END IF;
707
708 x_return_status := FND_API.G_RET_STS_SUCCESS;
709
710 -- Check for existence of database link
711
712 -- Assign agents to account
713 iem_agent_act_pvt.create_agntacct_by_agent (p_api_version_number =>1.0,
714 p_init_msg_list=>'F' ,
715 p_commit=>'F' ,
716 p_in_resource_tbl => p_in_resource_tbl,
717 p_email_account_id => p_email_account_id,
718 x_return_status =>l_return_status,
719 x_msg_count => l_msg_count,
720 x_msg_data => l_msg_data);
721
722 IF l_return_status='N' THEN
723 l_return_status1 := 'N';
724 ELSIF l_return_status<>'S' and l_return_status<>'N' THEN
725 x_return_status := FND_API.G_RET_STS_ERROR ;
726 FND_MSG_PUB.Count_And_Get
727 ( p_count => x_msg_count,
728 p_data => x_msg_data
729 );
730 END IF;
731
732 -- Unassign agents from account
733 iem_agent_act_pvt.delete_agntacct_by_agent (p_api_version_number =>1.0,
734 p_init_msg_list=>'F' ,
735 p_commit=>'F' ,
736 p_out_resource_tbl => p_out_resource_tbl,
737 p_email_account_id => p_email_account_id,
738 x_return_status =>l_return_status,
739 x_msg_count => l_msg_count,
740 x_msg_data => l_msg_data);
741
742 IF l_return_status='N'and l_return_status1='N' THEN
743 x_return_status := 'N';
744 ELSIF l_return_status<>'S' and l_return_status<>'N' THEN
745 x_return_status := FND_API.G_RET_STS_ERROR ;
746 FND_MSG_PUB.Count_And_Get
747 ( p_count => x_msg_count,
748 p_data => x_msg_data
749 );
750 END IF;
751
752
753 -- Standard Check Of p_commit.
754 IF FND_API.To_Boolean(p_commit) THEN
755 COMMIT WORK;
756 END IF;
757 -- Standard callto get message count and if count is 1, get message info.
758 FND_MSG_PUB.Count_And_Get
759 ( p_count => x_msg_count,
760 p_data => x_msg_data
761 );
762 EXCEPTION
763 WHEN NO_DATA_FOUND THEN
764 ROLLBACK TO agntacct_by_agent_wrap;
765 FND_MESSAGE.SET_NAME('IEM','IEM_SSS_ACCOUNT_NOT_FOUND');
766 FND_MSG_PUB.Add;
767 x_return_status := FND_API.G_RET_STS_ERROR ;
768 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
769
770 WHEN FND_API.G_EXC_ERROR THEN
771 ROLLBACK TO agntacct_by_agent_wrap;
772 x_return_status := FND_API.G_RET_STS_ERROR ;
773 FND_MSG_PUB.Count_And_Get
774 ( p_count => x_msg_count,
775 p_data => x_msg_data
776 );
777 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
778 ROLLBACK TO agntacct_by_agent_wrap;
779 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
780 FND_MSG_PUB.Count_And_Get
781 ( p_count => x_msg_count,
782 p_data => x_msg_data
783 );
784 WHEN OTHERS THEN
785 ROLLBACK TO agntacct_by_agent_wrap;
786 x_return_status := FND_API.G_RET_STS_ERROR;
787 IF FND_MSG_PUB.Check_Msg_Level
788 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
789 THEN
790 FND_MSG_PUB.Add_Exc_Msg
791 ( G_PKG_NAME ,
792 l_api_name
793 );
794 END IF;
795 FND_MSG_PUB.Count_And_Get
796 ( p_count => x_msg_count ,
797 p_data => x_msg_data
798 );
799
800 END update_agntacct_by_agent_wrap;
801
802
803 PROCEDURE create_agntacct_by_account (p_api_version_number IN NUMBER,
804 p_init_msg_list IN VARCHAR2 ,
805 p_commit IN VARCHAR2 ,
806 p_in_email_account_tbl IN jtf_varchar2_Table_100,
807 p_resource_id in number,
808 x_return_status OUT NOCOPY VARCHAR2,
809 x_msg_count OUT NOCOPY NUMBER,
810 x_msg_data OUT NOCOPY VARCHAR2
811 ) is
812 l_api_name VARCHAR2(255):='create_agntacct_by_account';
813 l_api_version_number NUMBER:=1.0;
814 l_return_status varchar2(10);
815 l_msg_count number;
816 l_msg_data varchar2(255);
817 l_email_user IEM_MSTEMAIL_ACCOUNTS.USER_NAME%TYPE;
818 l_user_id JTF_RS_RESOURCE_EXTNS.USER_ID%TYPE;
819 l_user_name JTF_RS_RESOURCE_EXTNS.USER_NAME%TYPE;
820 l_agntacct varchar2(160);
821 l_res_name varchar2(720);
822 l_resource_param_value_id NUMBER;
823 l_agent_account_count NUMBER;
824 l_error_email_user varchar2(32000);
825 l_count NUMBER;
826 l_error_account_count NUMBER:=0;
827 USER_NULL_ERROR EXCEPTION;
828 RESOURCE_INACTIVE_ERROR EXCEPTION;
829 l_data_change Boolean := false;
830 l_account_count number;
831
832 BEGIN
833
834 SAVEPOINT create_agntacct_by_account_PVT;
835 -- Standard call to check for call compatibility.
836 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
837 p_api_version_number,
838 l_api_name,
839 G_PKG_NAME)
840 THEN
841 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
842 END IF;
843
844 -- Initialize message list if p_init_msg_list is set to TRUE.
845 IF FND_API.to_Boolean( p_init_msg_list )
846 THEN
847 FND_MSG_PUB.initialize;
848 END IF;
849
850 x_return_status := FND_API.G_RET_STS_SUCCESS;
851
852 SELECT USER_ID, USER_NAME, SOURCE_LAST_NAME || ', ' || SOURCE_FIRST_NAME as RESOURCE_NAME
853 INTO l_user_id, l_user_name, l_res_name
854 FROM JTF_RS_RESOURCE_EXTNS
855 WHERE RESOURCE_ID = p_resource_id;
856
857 if (l_user_name is null or l_user_name = '') then
858 raise USER_NULL_ERROR;
859 end if;
860
861 SELECT count(*) into l_count
862 FROM jtf_rs_resource_extns
863 WHERE resource_id = p_resource_id
864 AND ( end_date_active is null OR
865 trunc(end_date_active) >= trunc(sysdate) );
866
867 if (l_count = 0) then
868 raise RESOURCE_INACTIVE_ERROR;
869 end if;
870
871 FOR i in 1.. p_in_email_account_tbl.count() LOOP
872
873 select count(*) into l_account_count FROM IEM_MSTEMAIL_ACCOUNTS
874 WHERE EMAIL_ACCOUNT_ID = p_in_email_account_tbl(i);
875
876 if (l_account_count = 1) then
877
878 SELECT USER_NAME
879 INTO l_email_user
880 FROM IEM_MSTEMAIL_ACCOUNTS
881 WHERE EMAIL_ACCOUNT_ID = p_in_email_account_tbl(i);
882
883 -- Check if the agent account already exist. If exist, skip and fetch next resource id in the loop
884 select count(*) into l_agent_account_count from iem_agents where email_account_id= p_in_email_account_tbl(i) and resource_id=p_resource_id;
885
886 IF (l_agent_account_count = 0) THEN
887
888 l_data_change := true;
889
890
891 /* JTF_RS_RESOURCE_VALUES_PUB.CREATE_RS_RESOURCE_VALUES(
892 P_Api_Version => 1.0,
893 P_Init_Msg_List => FND_API.G_FALSE,
894 P_Commit => FND_API.G_FALSE,
895 P_resource_id => p_resource_id,
896 p_resource_param_id => 1,
897 p_value => 'IEM_DEFAULT_VALUE',
898 P_value_type => p_in_email_account_tbl(i),
899 X_Return_Status => l_return_status,
900 X_Msg_Count => l_msg_count,
901 X_Msg_Data => l_msg_data,
902 X_resource_param_value_id => l_resource_param_value_id);
903
904 IF l_return_status='S' THEN
905 */
906 IEM_AGENT_ACT_PVT.create_item(p_api_version_number=>1.0,
907 P_Init_Msg_List =>'F' ,
908 P_Commit => 'F',
909 p_resource_id=>p_resource_id,
910 p_email_account_id=> p_in_email_account_tbl(i),
911 p_signature=>null,
912 p_CREATED_BY =>TO_NUMBER (FND_PROFILE.VALUE('USER_ID')),
913 p_CREATION_DATE =>SYSDATE,
914 p_LAST_UPDATED_BY =>TO_NUMBER (FND_PROFILE.VALUE('USER_ID')),
915 p_LAST_UPDATE_DATE =>SYSDATE,
916 p_LAST_UPDATE_LOGIN=>TO_NUMBER (FND_PROFILE.VALUE('LOGIN_ID')) ,
917 p_ATTRIBUTE1 =>null,
918 p_ATTRIBUTE2 =>null,
919 p_ATTRIBUTE3 =>null,
920 p_ATTRIBUTE4 =>null,
921 p_ATTRIBUTE5 =>null,
922 p_ATTRIBUTE6 =>null,
923 p_ATTRIBUTE7 =>null,
924 p_ATTRIBUTE8 =>null,
925 p_ATTRIBUTE9 =>null,
926 p_ATTRIBUTE10 =>null,
927 p_ATTRIBUTE11 =>null,
928 p_ATTRIBUTE12 =>null,
929 p_ATTRIBUTE13 =>null,
930 p_ATTRIBUTE14 =>null,
931 p_ATTRIBUTE15 =>null,
932 x_msg_count=>l_msg_count,
933 x_msg_data=>l_msg_data,
934 x_return_status=>l_return_status);
935
936 IF l_return_status<>'S' THEN
937 if l_error_account_count < 6 then
938 l_error_email_user := l_error_email_user || ' ' || l_email_user || ',';
939 l_error_account_count := l_error_account_count + 1;
940 end if;
941 END IF; -- IEM_AGENT_ACT_PVT
942 /* ELSE
943 if l_error_account_count < 6 then
944 l_error_email_user := l_error_email_user || ' ' || l_email_user || ',';
945 l_error_account_count := l_error_account_count + 1;
946 end if;
947 END IF; -- JTF_RS_RESOURCE_VALUES_PUB
948
949 ELSE
950 if l_error_account_count < 6 then
951 l_error_email_user := l_error_email_user || ' ' || l_email_user || ',';
952 l_error_account_count := l_error_account_count + 1;
953 end if;
954 */
955 END IF; -- l_agent_acount_account=0
956 end if; -- l_account_count = 1
957 END LOOP;
958
959 if l_error_email_user is not null then
960 x_return_status := FND_API.G_RET_STS_ERROR;
961 l_error_email_user := RTRIM(l_error_email_user, ', ');
962
963 if l_error_account_count > 5 then
964 l_error_email_user := l_error_email_user || '...';
965 end if;
966
967 FND_MESSAGE.SET_NAME('IEM', 'IEM_SSS_AGNTACCT2_CREATED');
968 FND_MESSAGE.SET_TOKEN('ACCOUNT', l_error_email_user);
969 FND_MSG_PUB.ADD;
970 elsif l_data_change = false then
971 x_return_status := 'N'; -- indicate no data change
972 end if;
973
974 -- Standard Check Of p_commit.
975 IF FND_API.To_Boolean(p_commit) THEN
976 COMMIT WORK;
977 END IF;
978 -- Standard callto get message count and if count is 1, get message info.
979 FND_MSG_PUB.Count_And_Get
980 ( p_count => x_msg_count,
981 p_data => x_msg_data
982 );
983 EXCEPTION
984 WHEN USER_NULL_ERROR THEN
985 ROLLBACK TO create_agntacct_by_account_PVT;
986 FND_MESSAGE.SET_NAME('IEM','IEM_SSS_AGENT_USER_NULL');
987 FND_MSG_PUB.Add;
988 x_return_status := FND_API.G_RET_STS_ERROR ;
989 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
990
991 WHEN RESOURCE_INACTIVE_ERROR THEN
992 ROLLBACK TO create_agntacct_by_account_PVT;
993 FND_MESSAGE.SET_NAME('IEM','IEM_SSS_RESOURCE_INACTIVE');
994 FND_MSG_PUB.Add;
995 x_return_status := FND_API.G_RET_STS_ERROR ;
996 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
997
998 WHEN FND_API.G_EXC_ERROR THEN
999 ROLLBACK TO create_agntacct_by_account_PVT;
1000 x_return_status := FND_API.G_RET_STS_ERROR ;
1001 FND_MSG_PUB.Count_And_Get
1002 ( p_count => x_msg_count,
1003 p_data => x_msg_data
1004 );
1005 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1006 ROLLBACK TO create_agntacct_by_account_PVT;
1007 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1008 FND_MSG_PUB.Count_And_Get
1009 ( p_count => x_msg_count,
1010 p_data => x_msg_data
1011 );
1012 WHEN OTHERS THEN
1013 ROLLBACK TO create_agntacct_by_account_PVT;
1014 x_return_status := FND_API.G_RET_STS_ERROR;
1015 IF FND_MSG_PUB.Check_Msg_Level
1016 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1017 THEN
1018 FND_MSG_PUB.Add_Exc_Msg
1019 ( G_PKG_NAME ,
1020 l_api_name
1021 );
1022 END IF;
1023 FND_MSG_PUB.Count_And_Get
1024 ( p_count => x_msg_count ,
1025 p_data => x_msg_data
1026 );
1027
1028 END create_agntacct_by_account;
1029
1030
1031 PROCEDURE delete_agntacct_by_account (p_api_version_number IN NUMBER,
1032 p_init_msg_list IN VARCHAR2 ,
1033 p_commit IN VARCHAR2 ,
1034 p_out_email_account_tbl IN jtf_varchar2_Table_100,
1035 p_resource_id in number,
1036 x_return_status OUT NOCOPY VARCHAR2,
1037 x_msg_count OUT NOCOPY NUMBER,
1038 x_msg_data OUT NOCOPY VARCHAR2
1039 ) is
1040 l_api_name VARCHAR2(255):='delete_agntacct_by_account';
1041 l_api_version_number NUMBER:=1.0;
1042 l_return_status varchar2(10);
1043 l_msg_count number;
1044 l_msg_data varchar2(255);
1045 l_email_user IEM_MSTEMAIL_ACCOUNTS.USER_NAME%TYPE;
1046 l_user_id JTF_RS_RESOURCE_EXTNS.USER_ID%TYPE;
1047 l_user_name JTF_RS_RESOURCE_EXTNS.USER_NAME%TYPE;
1048 l_agntacct varchar2(160);
1049 l_res_name varchar2(720);
1050 l_error_email_user varchar2(32000);
1051 l_error_email_user1 varchar2(32000);
1052 l_error_email_user2 varchar2(32000);
1053 l_error_account_count number:=0;
1054 l_error_account_count1 number:=0;
1055 l_error_account_count2 number:=0;
1056 l_resource_param_value_id number;
1057 l_object_version_number number;
1058 l_agent_account_count NUMBER;
1059 l_agent_account_id NUMBER;
1060 l_email_count NUMBER;
1061 l_compose_count NUMBER;
1062 l_process_count NUMBER;
1063 l_data_change Boolean := false;
1064 l_account_count number;
1065 l_resource_param_count number;
1066 l_is_clean Boolean;
1067
1068 BEGIN
1069 SAVEPOINT delete_agntacct_by_account_PVT;
1070 -- Standard call to check for call compatibility.
1071 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
1072 p_api_version_number,
1073 l_api_name,
1074 G_PKG_NAME)
1075 THEN
1076 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1077 END IF;
1078
1079 -- Initialize message list if p_init_msg_list is set to TRUE.
1080 IF FND_API.to_Boolean( p_init_msg_list )
1081 THEN
1082 FND_MSG_PUB.initialize;
1083 END IF;
1084
1085 x_return_status := FND_API.G_RET_STS_SUCCESS;
1086
1087 SELECT USER_ID, USER_NAME, SOURCE_LAST_NAME || ', ' || SOURCE_FIRST_NAME as RESOURCE_NAME
1088 INTO l_user_id, l_user_name, l_res_name
1089 FROM JTF_RS_RESOURCE_EXTNS
1090 WHERE RESOURCE_ID = p_resource_id;
1091
1092 FOR i in 1..p_out_email_account_tbl.count() LOOP
1093
1094 select count(*) into l_account_count FROM IEM_MSTEMAIL_ACCOUNTS
1095 WHERE EMAIL_ACCOUNT_ID = p_out_email_account_tbl(i);
1096
1097 if (l_account_count = 1) then
1098
1099 SELECT USER_NAME
1100 INTO l_email_user
1101 FROM IEM_MSTEMAIL_ACCOUNTS
1102 WHERE EMAIL_ACCOUNT_ID = p_out_email_account_tbl(i);
1103
1104 l_agntacct:=TO_CHAR(l_user_id)||'-'||l_email_user;
1105
1106 -- Check if the agent account already non-exist. If non-exist, skip and fetch next resource id in the loop
1107 select count(*) into l_agent_account_count from iem_agents where email_account_id=p_out_email_account_tbl(i)
1108 and resource_id=p_resource_id;
1109
1110 IF (l_agent_account_count <> 0) THEN
1111
1112 l_data_change := true;
1113
1114 -- Check any Agent fetched emails, compose messages or in-process messages in Outbox Processor
1115 IEM_CLIENT_PUB.isAgentInboxClean(p_api_version_number=>1.0,
1116 p_init_msg_list=>'F' ,
1117 p_commit=>'F' ,
1118 p_resource_id => p_resource_id,
1119 p_email_account_id => p_out_email_account_tbl(i),
1120 x_is_clean => l_is_clean,
1121 x_msg_count=>l_msg_count,
1122 x_msg_data=>l_msg_data,
1123 x_return_status=>l_return_status);
1124
1125 IF l_return_status<>'S' THEN
1126 if l_error_account_count1 < 6 then
1127 l_error_email_user1 := l_error_email_user1 || ' ' || l_email_user || ',';
1128 l_error_account_count1 := l_error_account_count1 + 1;
1129 end if;
1130 ELSE
1131
1132 IF (l_is_clean = false) THEN
1133 if l_error_account_count2 < 6 then
1134 l_error_email_user2 := l_error_email_user2 || ' ' || l_email_user || ',';
1135 l_error_account_count2 := l_error_account_count2 + 1;
1136 end if;
1137
1138 ELSE
1139
1140 IEM_AGENT_ACT_PVT.delete_item(p_api_version_number=>1.0,
1141 P_Init_Msg_List =>'F',
1142 P_Commit => 'F',
1143 p_resource_id => p_resource_id,
1144 p_email_account_id => p_out_email_account_tbl(i),
1145 x_msg_count=>l_msg_count,
1146 x_msg_data=>l_msg_data,
1147 x_return_status=>l_return_status);
1148
1149 IF l_return_status<>'S' THEN
1150
1151 /* select count(*) into l_resource_param_count from jtf_rs_resource_values
1152 where value_type=p_out_email_account_tbl(i) and resource_id=p_resource_id and value='IEM_DEFAULT_VALUE';
1153
1154 if (l_resource_param_count > 0) then
1155
1156 select resource_param_value_id, object_version_number into l_resource_param_value_id, l_object_version_number from jtf_rs_resource_values
1157 where value_type=p_out_email_account_tbl(i) and resource_id=p_resource_id and value='IEM_DEFAULT_VALUE';
1158
1159 JTF_RS_RESOURCE_VALUES_PUB.DELETE_RS_RESOURCE_VALUES(
1160 P_Api_Version => 1.0,
1161 P_Init_Msg_List => FND_API.G_FALSE,
1162 P_Commit => FND_API.G_FALSE,
1163 p_resource_param_value_id => l_resource_param_value_id,
1164 p_object_version_number => l_object_version_number,
1165 X_Return_Status => l_return_status,
1166 X_Msg_Count => l_msg_count,
1167 X_Msg_Data => l_msg_data);
1168
1169 IF l_return_status<>'S' THEN
1170 if l_error_account_count < 6 then
1171 l_error_email_user := l_error_email_user || ' ' || l_email_user || ',';
1172 l_error_account_count := l_error_account_count + 1;
1173 end if;
1174 END IF; -- IF THEN - JTF_RS_RESOURCE_VALUES_PUB
1175 end if; -- l_resource_param_count > 0
1176 ELSE
1177 */ if l_error_account_count < 6 then
1178 l_error_email_user := l_error_email_user || ' ' || l_email_user || ',';
1179 l_error_account_count := l_error_account_count + 1;
1180 end if;
1181 END IF; -- IF THEN - IEM_AGENT_ACT_PVT
1182
1183 END IF; -- is inbox clean
1184
1185 END IF; -- IF THEN - IEM_CLIENT_PUB
1186 END IF; -- l_agent_acount_account<>0
1187 end if; -- l_account_count=0
1188 END LOOP;
1189
1190 if l_error_email_user is not null then
1191 x_return_status := FND_API.G_RET_STS_ERROR;
1192 l_error_email_user := RTRIM(l_error_email_user, ', ');
1193
1194 if l_error_account_count > 5 then
1195 l_error_email_user := l_error_email_user || '...';
1196 end if;
1197
1198 FND_MESSAGE.SET_NAME('IEM', 'IEM_SSS_AGNTACCT2_DELETED');
1199 FND_MESSAGE.SET_TOKEN('ACCOUNT', l_error_email_user);
1200 FND_MSG_PUB.ADD;
1201
1202 end if;
1203
1204 if l_error_email_user1 is not null then
1205 x_return_status := FND_API.G_RET_STS_ERROR ;
1206 l_error_email_user1 := RTRIM(l_error_email_user1, ', ');
1207
1208 if l_error_account_count1 > 5 then
1209 l_error_email_user1 := l_error_email_user1 || '...';
1210 end if;
1211
1212 FND_MESSAGE.SET_NAME('IEM','IEM_SSS_AGNTACCT10_DELETED');
1213 FND_MESSAGE.SET_TOKEN('ACCOUNT', l_error_email_user1);
1214 FND_MESSAGE.SET_TOKEN('AGENT', l_res_name);
1215 FND_MSG_PUB.Add;
1216 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1217 end if;
1218
1219 if l_error_email_user2 is not null then
1220 x_return_status := FND_API.G_RET_STS_ERROR;
1221 l_error_email_user2 := RTRIM(l_error_email_user2, ', ');
1222
1223 if l_error_account_count2 > 5 then
1224 l_error_email_user2 := l_error_email_user2 || '...';
1225 end if;
1226
1227 FND_MESSAGE.SET_NAME('IEM', 'IEM_SSS_AGNTACCT6_DELETED');
1228 FND_MESSAGE.SET_TOKEN('ACCOUNT', l_error_email_user2);
1229 FND_MESSAGE.SET_TOKEN('AGENT', l_res_name);
1230 FND_MSG_PUB.ADD;
1231
1232 end if;
1233
1234 if l_error_email_user is null and l_error_email_user1 is null and l_error_email_user2 is null and l_data_change=false then
1235 x_return_status := 'N'; -- indicate no change in data
1236 end if;
1237
1238 -- Standard Check Of p_commit.
1239 IF FND_API.To_Boolean(p_commit) THEN
1240 COMMIT WORK;
1241 END IF;
1242 -- Standard callto get message count and if count is 1, get message info.
1243 FND_MSG_PUB.Count_And_Get
1244 ( p_count => x_msg_count,
1245 p_data => x_msg_data
1246 );
1247 EXCEPTION
1248 WHEN FND_API.G_EXC_ERROR THEN
1249 ROLLBACK TO delete_agntacct_by_account_PVT;
1250 x_return_status := FND_API.G_RET_STS_ERROR ;
1251 FND_MSG_PUB.Count_And_Get
1252 ( p_count => x_msg_count,
1253 p_data => x_msg_data
1254 );
1255 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1256 ROLLBACK TO delete_agntacct_by_account_PVT;
1257 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1258 FND_MSG_PUB.Count_And_Get
1259 ( p_count => x_msg_count,
1260 p_data => x_msg_data
1261 );
1262 WHEN OTHERS THEN
1263 ROLLBACK TO delete_agntacct_by_account_PVT;
1264 x_return_status := FND_API.G_RET_STS_ERROR;
1265 IF FND_MSG_PUB.Check_Msg_Level
1266 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1267 THEN
1268 FND_MSG_PUB.Add_Exc_Msg
1269 ( G_PKG_NAME ,
1270 l_api_name
1271 );
1272 END IF;
1273 FND_MSG_PUB.Count_And_Get
1274 ( p_count => x_msg_count ,
1275 p_data => x_msg_data
1276 );
1277
1278 END delete_agntacct_by_account;
1279
1280 PROCEDURE update_agntacct_by_acct_wrap (p_api_version_number IN NUMBER,
1281 p_init_msg_list IN VARCHAR2 ,
1282 p_commit IN VARCHAR2 ,
1283 p_in_email_account_tbl IN jtf_varchar2_Table_100,
1284 p_out_email_account_tbl IN jtf_varchar2_Table_100,
1285 p_resource_id in number,
1286 x_return_status OUT NOCOPY VARCHAR2,
1287 x_msg_count OUT NOCOPY NUMBER,
1288 x_msg_data OUT NOCOPY VARCHAR2
1289 ) is
1290 l_api_name VARCHAR2(255):='update_agntacct_by_acct_wrap';
1291 l_api_version_number NUMBER:=1.0;
1292 l_return_status varchar2(10);
1293 l_return_status1 varchar2(10);
1294 l_msg_count number;
1295 l_msg_data varchar2(255);
1296 l_db_server_id number;
1297 l_oo_link1 varchar2(200);
1298 l_account_count number;
1299
1300 DB_LINK_NOT_FOUND EXCEPTION;
1301
1302 BEGIN
1303 SAVEPOINT agntacct_by_account_wrap;
1304 -- Standard call to check for call compatibility.
1305 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
1306 p_api_version_number,
1307 l_api_name,
1308 G_PKG_NAME)
1309 THEN
1310 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1311 END IF;
1312
1313 -- Initialize message list if p_init_msg_list is set to TRUE.
1314 IF FND_API.to_Boolean( p_init_msg_list )
1315 THEN
1316 FND_MSG_PUB.initialize;
1317 END IF;
1318
1319 x_return_status := FND_API.G_RET_STS_SUCCESS;
1320
1321 -- Assign agents to account
1322 iem_agent_act_pvt.create_agntacct_by_account (p_api_version_number =>1.0,
1323 P_Init_Msg_List =>'F',
1324 P_Commit => 'F',
1325 p_in_email_account_tbl => p_in_email_account_tbl,
1326 p_resource_id => p_resource_id,
1327 x_return_status =>l_return_status,
1328 x_msg_count => l_msg_count,
1329 x_msg_data => l_msg_data);
1330
1331 IF l_return_status = 'N' THEN
1332 l_return_status1:='N';
1333 ELSIF l_return_status<>'S' and l_return_status<>'N' THEN
1334 x_return_status := FND_API.G_RET_STS_ERROR ;
1335 FND_MSG_PUB.Count_And_Get
1336 ( p_count => x_msg_count,
1337 p_data => x_msg_data
1338 );
1339 END IF;
1340
1341 -- Unassign agents from account
1342 iem_agent_act_pvt.delete_agntacct_by_account (p_api_version_number =>1.0,
1343 P_Init_Msg_List =>'F',
1344 P_Commit => 'F',
1345 p_out_email_account_tbl => p_out_email_account_tbl,
1346 p_resource_id => p_resource_id,
1347 x_return_status =>l_return_status,
1348 x_msg_count => l_msg_count,
1349 x_msg_data => l_msg_data);
1350
1351 IF l_return_status='N'and l_return_status1='N' THEN
1352 x_return_status := 'N';
1353 ELSIF l_return_status<>'S' and l_return_status<>'N' THEN
1354 x_return_status := FND_API.G_RET_STS_ERROR ;
1355 FND_MSG_PUB.Count_And_Get
1356 ( p_count => x_msg_count,
1357 p_data => x_msg_data
1358 );
1359 END IF;
1360
1361
1362 -- Standard Check Of p_commit.
1363 IF FND_API.To_Boolean(p_commit) THEN
1364 COMMIT WORK;
1365 END IF;
1366 -- Standard callto get message count and if count is 1, get message info.
1367 FND_MSG_PUB.Count_And_Get
1368 ( p_count => x_msg_count,
1369 p_data => x_msg_data
1370 );
1371 EXCEPTION
1372
1373 WHEN DB_LINK_NOT_FOUND THEN
1374 ROLLBACK TO agntacct_by_account_wrap;
1375 x_return_status := FND_API.G_RET_STS_ERROR ;
1376 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1377
1378 WHEN FND_API.G_EXC_ERROR THEN
1379 ROLLBACK TO agntacct_by_account_wrap;
1380 x_return_status := FND_API.G_RET_STS_ERROR ;
1381 FND_MSG_PUB.Count_And_Get
1382 ( p_count => x_msg_count,
1383 p_data => x_msg_data
1384 );
1385 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1386 ROLLBACK TO agntacct_by_account_wrap;
1387 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1388 FND_MSG_PUB.Count_And_Get
1389 ( p_count => x_msg_count,
1390 p_data => x_msg_data
1391 );
1392 WHEN OTHERS THEN
1393 ROLLBACK TO agntacct_by_account_wrap;
1394 x_return_status := FND_API.G_RET_STS_ERROR;
1395 IF FND_MSG_PUB.Check_Msg_Level
1396 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1397 THEN
1398 FND_MSG_PUB.Add_Exc_Msg
1399 ( G_PKG_NAME ,
1400 l_api_name
1401 );
1402 END IF;
1403 FND_MSG_PUB.Count_And_Get
1404 ( p_count => x_msg_count ,
1405 p_data => x_msg_data
1406 );
1407
1408 END update_agntacct_by_acct_wrap;
1409
1410 --added by siahmed for 12.1.3 project
1411
1412
1413 PROCEDURE update_agent_cherrypick (
1414 p_api_version_number IN NUMBER,
1415 p_init_msg_list IN VARCHAR2 ,
1416 p_commit IN VARCHAR2 ,
1417 p_in_cherrypick_tbl IN jtf_varchar2_Table_100,
1418 p_out_cherrypick_tbl IN jtf_varchar2_Table_100,
1419 p_email_account_id IN number,
1420 x_return_status OUT NOCOPY VARCHAR2,
1421 x_msg_count OUT NOCOPY NUMBER,
1422 x_msg_data OUT NOCOPY VARCHAR2
1423 ) is
1424 l_api_name VARCHAR2(255):='update_agent_cherrypick';
1425 l_api_version_number NUMBER:=1.0;
1426 l_return_status varchar2(10);
1427 l_msg_count number;
1428 l_msg_data varchar2(255);
1429 l_user_id JTF_RS_RESOURCE_EXTNS.USER_ID%TYPE;
1430 l_user_name JTF_RS_RESOURCE_EXTNS.USER_NAME%TYPE;
1431 l_agntacct varchar2(160);
1432 l_res_name varchar2(720);
1433 l_error_username varchar2(32000);
1434 l_error_username1 varchar2(32000);
1435 l_error_username2 varchar2(32000);
1436 l_error_agent_count number:=0;
1437 l_error_agent_count1 number:=0;
1438 l_error_agent_count2 number:=0;
1439 l_resource_param_value_id number;
1440 l_object_version_number number;
1441 l_agent_account_id NUMBER;
1442 l_agent_account_count NUMBER;
1443 l_email_count NUMBER;
1444 l_compose_count NUMBER;
1445 l_process_count NUMBER;
1446 l_data_change Boolean := false;
1447 l_resource_param_count number;
1448 l_account_name varchar2(210);
1449 l_is_clean Boolean;
1450
1451
1452 BEGIN
1453 SAVEPOINT update_agent_cherrypick_PVT;
1454 -- Standard call to check for call compatibility.
1455 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
1456 p_api_version_number,
1457 l_api_name,
1458 G_PKG_NAME)
1459 THEN
1460 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1461 END IF;
1462
1463 -- Initialize message list if p_init_msg_list is set to TRUE.
1464 IF FND_API.to_Boolean( p_init_msg_list )
1465 THEN
1466 FND_MSG_PUB.initialize;
1467 END IF;
1468
1469 x_return_status := FND_API.G_RET_STS_SUCCESS;
1470
1471
1472 FOR i in 1..p_in_cherrypick_tbl.count() LOOP
1473
1474 SELECT USER_ID, USER_NAME, SOURCE_LAST_NAME || ', ' || SOURCE_FIRST_NAME as RESOURCE_NAME
1475 INTO l_user_id, l_user_name, l_res_name
1476 FROM JTF_RS_RESOURCE_EXTNS
1477 WHERE RESOURCE_ID = p_in_cherrypick_tbl(i);
1478
1479
1480 -- Check if the agent account already non-exist. If non-exist, skip and fetch next resource id in the loop
1481 select count(*) into l_agent_account_count
1482 from iem_agents
1483 where resource_id=p_in_cherrypick_tbl(i)
1484 and email_account_id=p_email_account_id;
1485
1486 IF (l_agent_account_count <> 0) THEN
1487
1488 l_data_change := true;
1489
1490 BEGIN
1491 UPDATE iem_agents set cherry_pick_flag = 'Y'
1492 WHERE resource_id=p_in_cherrypick_tbl(i)
1493 and email_account_id=p_email_account_id;
1494
1495 FND_MESSAGE.SET_NAME('IEM','IEM_CHERRY_PICK_ADDED');
1496 FND_MESSAGE.SET_TOKEN('AGENT', p_in_cherrypick_tbl(i) );
1497 FND_MESSAGE.SET_TOKEN('ACCOUNT', p_email_account_id);
1498 EXCEPTION
1499 WHEN NO_DATA_FOUND THEN
1500 FND_MESSAGE.SET_NAME('IEM','IEM_CHERRY_PICK_NO_DATA_ERROR');
1501 FND_MESSAGE.SET_TOKEN('AGENT', p_in_cherrypick_tbl(i) );
1502 FND_MESSAGE.SET_TOKEN('ACCOUNT', p_email_account_id);
1503 WHEN OTHERS THEN
1504 FND_MESSAGE.SET_NAME('IEM','IEM_CHERRY_PICK_OTHERS_ERROR');
1505 FND_MESSAGE.SET_TOKEN('AGENT', p_in_cherrypick_tbl(i) );
1506 FND_MESSAGE.SET_TOKEN('ACCOUNT', p_email_account_id);
1507
1508 END;
1509
1510 END IF; -- l_agent_acount_account<>0
1511 END LOOP;
1512
1513
1514 FOR i in 1..p_out_cherrypick_tbl.count() LOOP
1515
1516 SELECT USER_ID, USER_NAME, SOURCE_LAST_NAME || ', ' || SOURCE_FIRST_NAME as RESOURCE_NAME
1517 INTO l_user_id, l_user_name, l_res_name
1518 FROM JTF_RS_RESOURCE_EXTNS
1519 WHERE RESOURCE_ID = p_out_cherrypick_tbl(i);
1520
1521
1522 -- Check if the agent account already non-exist. If non-exist, skip and fetch next resource id in the loop
1523 select count(*) into l_agent_account_count
1524 from iem_agents
1525 where resource_id=p_out_cherrypick_tbl(i)
1526 and email_account_id=p_email_account_id;
1527
1528 IF (l_agent_account_count <> 0) THEN
1529
1530 l_data_change := true;
1531
1532 BEGIN
1533 UPDATE iem_agents set cherry_pick_flag = null
1534 WHERE resource_id=p_out_cherrypick_tbl(i)
1535 and email_account_id=p_email_account_id;
1536
1537 FND_MESSAGE.SET_NAME('IEM','IEM_CHRYPICK_ADDED');
1538 FND_MESSAGE.SET_TOKEN('AGENT', p_out_cherrypick_tbl(i) );
1539 FND_MESSAGE.SET_TOKEN('ACCOUNT', p_email_account_id);
1540 EXCEPTION
1541 WHEN NO_DATA_FOUND THEN
1542 FND_MESSAGE.SET_NAME('IEM','IEM_CHRYPICK_NO_DATA_ERROR');
1543 FND_MESSAGE.SET_TOKEN('AGENT', p_out_cherrypick_tbl(i) );
1544 FND_MESSAGE.SET_TOKEN('ACCOUNT', p_email_account_id);
1545 WHEN OTHERS THEN
1546 FND_MESSAGE.SET_NAME('IEM','IEM_CHRYPICK_OTHERS_ERROR');
1547 FND_MESSAGE.SET_TOKEN('AGENT', p_out_cherrypick_tbl(i) );
1548 FND_MESSAGE.SET_TOKEN('ACCOUNT', p_email_account_id);
1549
1550 END;
1551
1552 END IF; -- l_agent_acount_account<>0
1553 END LOOP;
1554
1555
1556
1557 -- Standard Check Of p_commit.
1558 IF FND_API.To_Boolean(p_commit) THEN
1559 COMMIT WORK;
1560 END IF;
1561 -- Standard callto get message count and if count is 1, get message info.
1562 FND_MSG_PUB.Count_And_Get
1563 ( p_count => x_msg_count,
1564 p_data => x_msg_data
1565 );
1566 EXCEPTION
1567 WHEN FND_API.G_EXC_ERROR THEN
1568 ROLLBACK TO update_agent_cherrypick_PVT;
1569 x_return_status := FND_API.G_RET_STS_ERROR ;
1570 FND_MSG_PUB.Count_And_Get
1571 ( p_count => x_msg_count,
1572 p_data => x_msg_data
1573 );
1574 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1575 ROLLBACK TO update_agent_cherrypick_PVT;
1576 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1577 FND_MSG_PUB.Count_And_Get
1578 ( p_count => x_msg_count,
1579 p_data => x_msg_data
1580 );
1581 WHEN OTHERS THEN
1582 ROLLBACK TO update_agent_cherrypick_PVT;
1583 x_return_status := FND_API.G_RET_STS_ERROR;
1584 IF FND_MSG_PUB.Check_Msg_Level
1585 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1586 THEN
1587 FND_MSG_PUB.Add_Exc_Msg
1588 ( G_PKG_NAME ,
1589 l_api_name
1590 );
1591 END IF;
1592 FND_MSG_PUB.Count_And_Get
1593 ( p_count => x_msg_count ,
1594 p_data => x_msg_data
1595 );
1596
1597 END update_agent_cherrypick;
1598
1599
1600
1601 PROCEDURE update_acct_cherrypick (p_api_version_number IN NUMBER,
1602 p_init_msg_list IN VARCHAR2 ,
1603 p_commit IN VARCHAR2 ,
1604 p_in_acct_chrypick_tbl IN jtf_varchar2_Table_100,
1605 p_out_acct_chrypick_tbl IN jtf_varchar2_Table_100,
1606 p_resource_id in number,
1607 x_return_status OUT NOCOPY VARCHAR2,
1608 x_msg_count OUT NOCOPY NUMBER,
1609 x_msg_data OUT NOCOPY VARCHAR2
1610 ) is
1611 l_api_name VARCHAR2(255):='update_acct_cherrypick';
1612 l_api_version_number NUMBER:=1.0;
1613 l_return_status varchar2(10);
1614 l_msg_count number;
1615 l_msg_data varchar2(255);
1616 l_user_id JTF_RS_RESOURCE_EXTNS.USER_ID%TYPE;
1617 l_user_name JTF_RS_RESOURCE_EXTNS.USER_NAME%TYPE;
1618 l_agntacct varchar2(160);
1619 l_res_name varchar2(720);
1620 l_error_username varchar2(32000);
1621 l_error_username1 varchar2(32000);
1622 l_error_username2 varchar2(32000);
1623 l_error_agent_count number:=0;
1624 l_error_agent_count1 number:=0;
1625 l_error_agent_count2 number:=0;
1626 l_resource_param_value_id number;
1627 l_object_version_number number;
1628 l_agent_account_id NUMBER;
1629 l_agent_account_count NUMBER;
1630 l_email_count NUMBER;
1631 l_compose_count NUMBER;
1632 l_process_count NUMBER;
1633 l_data_change Boolean := false;
1634 l_resource_param_count number;
1635 l_account_name varchar2(210);
1636 l_is_clean Boolean;
1637
1638
1639 BEGIN
1640 SAVEPOINT update_acct_chrypick_PVT;
1641 -- Standard call to check for call compatibility.
1642 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
1643 p_api_version_number,
1644 l_api_name,
1645 G_PKG_NAME)
1646 THEN
1647 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1648 END IF;
1649
1650 -- Initialize message list if p_init_msg_list is set to TRUE.
1651 IF FND_API.to_Boolean( p_init_msg_list )
1652 THEN
1653 FND_MSG_PUB.initialize;
1654 END IF;
1655
1656 x_return_status := FND_API.G_RET_STS_SUCCESS;
1657
1658
1659 FOR i in 1..p_in_acct_chrypick_tbl.count() LOOP
1660
1661 SELECT USER_ID, USER_NAME, SOURCE_LAST_NAME || ', ' || SOURCE_FIRST_NAME as RESOURCE_NAME
1662 INTO l_user_id, l_user_name, l_res_name
1663 FROM JTF_RS_RESOURCE_EXTNS
1664 WHERE RESOURCE_ID = p_resource_id;
1665
1666
1667 -- Check if the agent account already non-exist. If non-exist, skip and fetch next resource id in the loop
1668 select count(*) into l_agent_account_count
1669 from iem_agents
1670 where resource_id=p_resource_id
1671 and email_account_id=p_in_acct_chrypick_tbl(i);
1672
1673 IF (l_agent_account_count <> 0) THEN
1674
1675 l_data_change := true;
1676
1677 BEGIN
1678 UPDATE iem_agents set cherry_pick_flag = 'Y'
1679 where resource_id=p_resource_id
1680 and email_account_id=p_in_acct_chrypick_tbl(i);
1681
1682 FND_MESSAGE.SET_NAME('IEM','IEM_CHERRY_PICK_ACCT_ADDED');
1683 FND_MESSAGE.SET_TOKEN('ACCOUNT', p_resource_id);
1684 FND_MESSAGE.SET_TOKEN('AGENT', p_in_acct_chrypick_tbl(i) );
1685 EXCEPTION
1686 WHEN NO_DATA_FOUND THEN
1687 FND_MESSAGE.SET_NAME('IEM','IEM_CHERRY_PICK_ACCT_NO_DATA_ERROR');
1688 FND_MESSAGE.SET_TOKEN('ACCOUNT', p_resource_id);
1689 FND_MESSAGE.SET_TOKEN('AGENT', p_in_acct_chrypick_tbl(i) );
1690 WHEN OTHERS THEN
1691 FND_MESSAGE.SET_NAME('IEM','IEM_CHERRY_PICK_ACCT_OTHERS_ERROR');
1692 FND_MESSAGE.SET_TOKEN('ACCOUNT', p_resource_id);
1693 FND_MESSAGE.SET_TOKEN('AGENT', p_in_acct_chrypick_tbl(i));
1694
1695 END;
1696
1697 END IF; -- l_agent_acount_account<>0
1698 END LOOP;
1699
1700
1701 FOR i in 1..p_out_acct_chrypick_tbl.count() LOOP
1702
1703 SELECT USER_ID, USER_NAME, SOURCE_LAST_NAME || ', ' || SOURCE_FIRST_NAME as RESOURCE_NAME
1704 INTO l_user_id, l_user_name, l_res_name
1705 FROM JTF_RS_RESOURCE_EXTNS
1706 WHERE RESOURCE_ID = p_resource_id;
1707
1708
1709 -- Check if the agent account already non-exist. If non-exist, skip and fetch next resource id in the loop
1710 select count(*) into l_agent_account_count
1711 from iem_agents
1712 where resource_id=p_resource_id
1713 and email_account_id=p_out_acct_chrypick_tbl(i);
1714
1715 IF (l_agent_account_count <> 0) THEN
1716
1717 l_data_change := true;
1718
1719 BEGIN
1720 UPDATE iem_agents set cherry_pick_flag = null
1721 where resource_id=p_resource_id
1722 and email_account_id=p_out_acct_chrypick_tbl(i);
1723
1724 FND_MESSAGE.SET_NAME('IEM','IEM_CHRYPICK_ACCT_DELETED');
1725 FND_MESSAGE.SET_TOKEN('ACCOUNT', p_resource_id);
1726 FND_MESSAGE.SET_TOKEN('AGENT', p_out_acct_chrypick_tbl(i) );
1727 EXCEPTION
1728 WHEN NO_DATA_FOUND THEN
1729 FND_MESSAGE.SET_NAME('IEM','IEM_CHRRYPICK_ACCT_DELETE_NO_DATA_ERROR');
1730 FND_MESSAGE.SET_TOKEN('ACCOUNT', p_resource_id);
1731 FND_MESSAGE.SET_TOKEN('AGENT', p_out_acct_chrypick_tbl(i) );
1732 WHEN OTHERS THEN
1733 FND_MESSAGE.SET_NAME('IEM','IEM_CHRYPICK_ACCT_DELETE_OTHERS_ERROR');
1734 FND_MESSAGE.SET_TOKEN('ACCOUNT', p_resource_id);
1735 FND_MESSAGE.SET_TOKEN('AGENT', p_out_acct_chrypick_tbl(i) );
1736
1737 END;
1738
1739 END IF; -- l_agent_acount_account<>0
1740 END LOOP;
1741
1742
1743
1744 -- Standard Check Of p_commit.
1745 IF FND_API.To_Boolean(p_commit) THEN
1746 COMMIT WORK;
1747 END IF;
1748 -- Standard callto get message count and if count is 1, get message info.
1749 FND_MSG_PUB.Count_And_Get
1750 ( p_count => x_msg_count,
1751 p_data => x_msg_data
1752 );
1753 EXCEPTION
1754 WHEN FND_API.G_EXC_ERROR THEN
1755 ROLLBACK TO update_acct_chrypick_PVT;
1756 x_return_status := FND_API.G_RET_STS_ERROR ;
1757 FND_MSG_PUB.Count_And_Get
1758 ( p_count => x_msg_count,
1759 p_data => x_msg_data
1760 );
1761 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1762 ROLLBACK TO update_acct_chrypick_PVT;
1763 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1764 FND_MSG_PUB.Count_And_Get
1765 ( p_count => x_msg_count,
1766 p_data => x_msg_data
1767 );
1768 WHEN OTHERS THEN
1769 ROLLBACK TO update_acct_chrypick_PVT;
1770 x_return_status := FND_API.G_RET_STS_ERROR;
1771 IF FND_MSG_PUB.Check_Msg_Level
1772 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1773 THEN
1774 FND_MSG_PUB.Add_Exc_Msg
1775 ( G_PKG_NAME ,
1776 l_api_name
1777 );
1778 END IF;
1779 FND_MSG_PUB.Count_And_Get
1780 ( p_count => x_msg_count ,
1781 p_data => x_msg_data
1782 );
1783
1784 END update_acct_cherrypick;
1785 --end of addition for 12.1.3 project siahmed
1786
1787
1788 END IEM_AGENT_ACT_PVT ;