[Home] [Help]
PACKAGE BODY: APPS.IEM_EMAIL_SERVERS_PVT
Source
1 PACKAGE BODY IEM_EMAIL_SERVERS_PVT as
2 /* $Header: iemvevrb.pls 115.16 2002/12/03 20:02:44 chtang shipped $ */
3 G_PKG_NAME CONSTANT varchar2(30) :='IEM_EMAIL_SERVERS_PVT ';
4
5 PROCEDURE create_item_sss (p_api_version_number IN NUMBER,
6 p_init_msg_list IN VARCHAR2,
7 p_commit IN VARCHAR2,
8 p_server_name IN VARCHAR2,
9 p_dns_name IN VARCHAR2,
10 p_ip_address IN VARCHAR2,
11 p_port IN NUMBER,
12 p_server_type_id IN NUMBER,
13 p_rt_availability IN VARCHAR2,
14 p_server_group_id IN NUMBER,
15 p_CREATED_BY NUMBER,
16 p_CREATION_DATE DATE,
17 p_LAST_UPDATED_BY NUMBER,
18 p_LAST_UPDATE_DATE DATE,
19 p_LAST_UPDATE_LOGIN NUMBER,
20 p_ATTRIBUTE1 VARCHAR2,
21 p_ATTRIBUTE2 VARCHAR2,
22 p_ATTRIBUTE3 VARCHAR2,
23 p_ATTRIBUTE4 VARCHAR2,
24 p_ATTRIBUTE5 VARCHAR2,
25 p_ATTRIBUTE6 VARCHAR2,
26 p_ATTRIBUTE7 VARCHAR2,
27 p_ATTRIBUTE8 VARCHAR2,
28 p_ATTRIBUTE9 VARCHAR2,
29 p_ATTRIBUTE10 VARCHAR2,
30 p_ATTRIBUTE11 VARCHAR2,
31 p_ATTRIBUTE12 VARCHAR2,
32 p_ATTRIBUTE13 VARCHAR2,
33 p_ATTRIBUTE14 VARCHAR2,
34 p_ATTRIBUTE15 VARCHAR2,
35 x_return_status OUT NOCOPY VARCHAR2,
36 x_msg_count OUT NOCOPY NUMBER,
37 x_msg_data OUT NOCOPY VARCHAR2
38 ) is
39 l_api_name VARCHAR2(255):='create_item_sss';
40 l_api_version_number NUMBER:=1.0;
41 l_es_count number:=0;
42 l_seq_id number;
43 l_grp_cnt number;
44 l_type_cnt number;
45 l_CREATED_BY NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('USER_ID'));
46 l_LAST_UPDATED_BY NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('USER_ID')) ;
47 l_LAST_UPDATE_LOGIN NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('LOGIN_ ID')) ;
48
49 IEM_DUP_EMAIL_SERVER EXCEPTION;
50 IEM_NON_EXISTENT_SERVER_GRP EXCEPTION;
51
52 BEGIN
53 -- Standard Start of API savepoint
54 SAVEPOINT create_item_PVT;
55 -- Standard call to check for call compatibility.
56 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
57 p_api_version_number,
58 l_api_name,
59 G_PKG_NAME)
60 THEN
61 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
62 END IF;
63 -- Initialize message list if p_init_msg_list is set to TRUE.
64 IF FND_API.to_Boolean( p_init_msg_list )
65 THEN
66 FND_MSG_PUB.initialize;
67 END IF;
68 -- Initialize API return status to SUCCESS
69 x_return_status := FND_API.G_RET_STS_SUCCESS;
70
71 -- select count(*) into l_es_count from iem_email_servers
72 -- where UPPER(dns_name)=UPPER(p_dns_name)
73 -- and ip_address=p_ip_address and port=p_port;
74
75 -- if l_es_count > 0 then
76 -- raise IEM_DUP_EMAIL_SERVER;
77 -- end if;
78
79 SELECT iem_email_servers_s1.nextval
80 INTO l_seq_id
81 FROM dual;
82
83 /* Check For Existing Server Type Id */
84
85 Select count(*) into l_type_cnt from iem_email_server_types
86 where email_server_type_id=p_server_type_id
87 and rownum=1;
88 IF l_type_cnt=0 THEN
89 FND_MESSAGE.SET_NAME('IEM','IEM_NON_EXISTENT_SERVER_TYPE');
90 APP_EXCEPTION.RAISE_EXCEPTION;
91 END IF;
92
93 /*Check For Existing Server Group Id */
94
95 Select count(*) into l_grp_cnt from iem_server_groups
96 where server_group_id=p_server_group_id
97 and rownum=1;
98 IF l_grp_cnt = 0 then
99 --FND_MESSAGE.SET_NAME('IEM','IEM_NON_EXISTENT_SERVER_GRP');
100 --APP_EXCEPTION.RAISE_EXCEPTION;
101 raise IEM_NON_EXISTENT_SERVER_GRP;
102 END IF;
103 -- raise_application_error(-20002,'Server Group Id ||to_char(p_server_group_id)||' Does not Exist');
104
105 INSERT INTO iem_email_servers
106 (
107 EMAIL_SERVER_ID,
108 SERVER_NAME,
109 DNS_NAME,
110 IP_ADDRESS,
111 PORT,
112 SERVER_TYPE_ID ,
113 RT_AVAILABILITY,
114 SERVER_GROUP_ID,
115 CREATED_BY,
116 CREATION_DATE,
117 LAST_UPDATED_BY,
118 LAST_UPDATE_DATE,
119 LAST_UPDATE_LOGIN,
120 ATTRIBUTE1,
121 ATTRIBUTE2,
122 ATTRIBUTE3,
123 ATTRIBUTE4,
124 ATTRIBUTE5,
125 ATTRIBUTE6,
126 ATTRIBUTE7,
127 ATTRIBUTE8,
128 ATTRIBUTE9,
129 ATTRIBUTE10,
130 ATTRIBUTE11,
131 ATTRIBUTE12,
132 ATTRIBUTE13,
133 ATTRIBUTE14,
134 ATTRIBUTE15 )
135 VALUES
136 (
137 l_seq_id,
138 p_server_name,
139 p_dns_name,
140 p_ip_address,
141 p_port,
142 p_server_type_id,
143 p_rt_availability,
144 p_server_group_id,
145 decode(l_CREATED_BY,null,-1,l_CREATED_BY),
146 sysdate,
147 decode(l_LAST_UPDATED_BY,null,-1,l_LAST_UPDATED_BY),
148 sysdate,
149 decode(l_LAST_UPDATE_LOGIN,null,-1,l_LAST_UPDATE_LOGIN),
150 decode(p_ATTRIBUTE1, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE1),
151 decode(p_ATTRIBUTE2, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE2),
152 decode(p_ATTRIBUTE3, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE3),
153 decode(p_ATTRIBUTE4, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE4),
154 decode(p_ATTRIBUTE5, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE5),
155 decode(p_ATTRIBUTE6, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE6),
156 decode(p_ATTRIBUTE7, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE7),
157 decode(p_ATTRIBUTE8, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE8),
158 decode(p_ATTRIBUTE9, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE9),
159 decode(p_ATTRIBUTE10, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE10),
160 decode(p_ATTRIBUTE11, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE11),
161 decode(p_ATTRIBUTE12, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE12),
162 decode(p_ATTRIBUTE13, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE13),
163 decode(p_ATTRIBUTE14, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE14),
164 decode(p_ATTRIBUTE15, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE15)
165 );
166
167 -- Standard Check Of p_commit.
168 IF FND_API.To_Boolean(p_commit) THEN
169 COMMIT WORK;
170 END IF;
171 -- Standard callto get message count and if count is 1, get message info.
172 FND_MSG_PUB.Count_And_Get
173 ( p_count => x_msg_count,
174 p_data => x_msg_data
175 );
176 EXCEPTION
177
178 -- WHEN IEM_DUP_EMAIL_SERVER THEN
179 -- ROLLBACK TO create_item_PVT;
180 -- FND_MESSAGE.SET_NAME('IEM','IEM_ADMIN_DUP_EMAIL_SERVER');
181 -- FND_MSG_PUB.Add;
182 -- x_return_status := FND_API.G_RET_STS_ERROR ;
183 -- FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
184
185 WHEN IEM_NON_EXISTENT_SERVER_GRP THEN
186 ROLLBACK TO create_item_PVT;
187 FND_MESSAGE.SET_NAME('IEM','IEM_NON_EXISTENT_SERVER_GRP');
188 FND_MSG_PUB.Add;
189 x_return_status := FND_API.G_RET_STS_ERROR ;
190 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
191
192 WHEN DUP_VAL_ON_INDEX THEN
193 ROLLBACK TO create_item_PVT;
194 -- FND_MESSAGE.SET_NAME('IEM','IEM_ADMIN_GRP_DUP_RECORD');
195 FND_MESSAGE.SET_NAME('IEM','IEM_ADMIN_DUP_EMAIL_SERVER');
196 FND_MSG_PUB.Add;
197 x_return_status := FND_API.G_RET_STS_ERROR ;
198 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
199
200 WHEN FND_API.G_EXC_ERROR THEN
201 ROLLBACK TO create_item_PVT;
202 x_return_status := FND_API.G_RET_STS_ERROR ;
203 FND_MSG_PUB.Count_And_Get
204 ( p_count => x_msg_count,
205 p_data => x_msg_data
206 );
207 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
208 ROLLBACK TO create_item_PVT;
209 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
210 FND_MSG_PUB.Count_And_Get
211 ( p_count => x_msg_count,
212 p_data => x_msg_data
213 );
214 WHEN OTHERS THEN
215 ROLLBACK TO create_item_PVT;
216 x_return_status := FND_API.G_RET_STS_ERROR;
217 IF FND_MSG_PUB.Check_Msg_Level
218 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
219 THEN
220 FND_MSG_PUB.Add_Exc_Msg
221 ( G_PKG_NAME ,
222 l_api_name
223 );
224 END IF;
225 FND_MSG_PUB.Count_And_Get
226 ( p_count => x_msg_count ,
227 p_data => x_msg_data
228 );
229
230 END create_item_sss;
231
232
233 PROCEDURE create_item_wrap_sss (p_api_version_number IN NUMBER,
234 p_init_msg_list IN VARCHAR2,
235 p_commit IN VARCHAR2,
236 p_server_name IN VARCHAR2,
237 p_dns_name IN VARCHAR2,
238 p_ip_address IN VARCHAR2,
239 p_port IN NUMBER,
240 p_server_type_id IN NUMBER,
241 p_rt_availability IN VARCHAR2,
242 p_server_group_id IN NUMBER,
243 x_return_status OUT NOCOPY VARCHAR2,
244 x_msg_count OUT NOCOPY NUMBER,
245 x_msg_data OUT NOCOPY VARCHAR2
246 ) is
247 l_api_name VARCHAR2(255):='create_item_wrap_sss';
248 l_api_version_number NUMBER:=1.0;
249 l_seq_id number;
250 l_grp_cnt number;
251 l_type_cnt number;
252 l_stat varchar2(20);
253 l_count number;
254 l_email_server_id number;
255 l_data varchar2(300);
256 l_email_server_type varchar2(20);
257 l_CREATED_BY NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('USER_ID'));
258 l_LAST_UPDATED_BY NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('USER_ID')) ;
259 l_LAST_UPDATE_LOGIN NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('LOGIN_ ID')) ;
260
261 EXCP_EMAIL_SERVER EXCEPTION;
262
263 BEGIN
264 -- Standard Start of API savepoint
265 SAVEPOINT create_item_PVT;
266 -- Standard call to check for call compatibility.
267 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
268 p_api_version_number,
269 l_api_name,
270 G_PKG_NAME)
271 THEN
272 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
273 END IF;
274 -- Initialize message list if p_init_msg_list is set to TRUE.
275 IF FND_API.to_Boolean( p_init_msg_list )
276 THEN
277 FND_MSG_PUB.initialize;
278 END IF;
279 -- Initialize API return status to SUCCESS
280 x_return_status := FND_API.G_RET_STS_SUCCESS;
281
282
283 IEM_EMAIL_SERVERS_PVT.create_item_sss(
284 p_api_version_number =>1.0,
285 p_init_msg_list => FND_API.G_FALSE,
286 p_commit=>FND_API.G_FALSE,
287 p_server_name => p_server_name,
288 p_dns_name => p_dns_name,
289 p_ip_address => p_ip_address,
290 p_port => p_port,
291 p_server_type_id => p_server_type_id,
292 p_rt_availability => p_rt_availability,
293 p_server_group_id => p_server_group_id,
294 p_CREATED_BY =>TO_NUMBER (FND_PROFILE.VALUE('USER_ID')),
295 p_CREATION_DATE =>SYSDATE,
296 p_LAST_UPDATED_BY =>TO_NUMBER (FND_PROFILE.VALUE('USER_ID')),
297 p_LAST_UPDATE_DATE =>SYSDATE,
298 p_LAST_UPDATE_LOGIN=>TO_NUMBER (FND_PROFILE.VALUE('LOGIN_ID')) ,
299 p_ATTRIBUTE1 =>null,
300 p_ATTRIBUTE2 =>null,
301 p_ATTRIBUTE3 =>null,
302 p_ATTRIBUTE4 =>null,
303 p_ATTRIBUTE5 =>null,
304 p_ATTRIBUTE6 =>null,
305 p_ATTRIBUTE7 =>null,
306 p_ATTRIBUTE8 =>null,
307 p_ATTRIBUTE9 =>null,
308 p_ATTRIBUTE10 =>null,
309 p_ATTRIBUTE11 =>null,
310 p_ATTRIBUTE12 =>null,
311 p_ATTRIBUTE13 =>null,
312 p_ATTRIBUTE14 =>null,
313 p_ATTRIBUTE15 =>null,
314 x_return_status =>l_stat,
315 x_msg_count => l_count,
316 x_msg_data => l_data);
317
318 IF l_stat <>'S' then
319 raise EXCP_EMAIL_SERVER;
320 END IF;
321
322 -- Update Cache Audit Trail
323 select email_server_type into l_email_server_type from iem_email_server_types where email_server_type_id=p_server_type_id;
324
325 if (p_ip_address <> FND_API.G_MISS_CHAR) then
326 select email_server_id into l_email_server_id from iem_email_servers
327 where UPPER(dns_name)=UPPER(p_dns_name)
328 and ip_address=p_ip_address and port=p_port;
329 else
330 SELECT iem_email_servers_s1.currval
331 INTO l_email_server_id
332 FROM dual;
333
334 end if;
335
336 IEM_COMP_RT_STATS_PVT.create_item(p_api_version_number =>1.0,
337 p_init_msg_list => FND_API.G_FALSE,
338 p_commit => FND_API.G_FALSE,
339 p_type => l_email_server_type,
340 p_param => 'CREATE',
341 p_value => l_email_server_id,
342 x_return_status => l_stat,
343 x_msg_count => l_count,
344 x_msg_data => l_data
345 );
346 -- Not raise error when failed to insert data into iem_comp_rt_stats, it is not user error.
347
348 -- Standard Check Of p_commit.
349 IF FND_API.To_Boolean(p_commit) THEN
350 COMMIT WORK;
351 END IF;
352 -- Standard callto get message count and if count is 1, get message info.
353 FND_MSG_PUB.Count_And_Get
354 ( p_count => x_msg_count,
355 p_data => x_msg_data
356 );
357 EXCEPTION
358
359 WHEN EXCP_EMAIL_SERVER THEN
360 ROLLBACK TO create_item_PVT;
361 x_return_status := FND_API.G_RET_STS_ERROR;
362 -- FND_MESSAGE.SET_NAME('IEM', 'IEM_SSS_EMAIL_SVR_NOT_CREATED');
363 -- FND_MSG_PUB.Add;
364 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,p_data => x_msg_data);
365
366 WHEN FND_API.G_EXC_ERROR THEN
367 ROLLBACK TO create_item_PVT;
368 x_return_status := FND_API.G_RET_STS_ERROR ;
369 FND_MSG_PUB.Count_And_Get
370 ( p_count => x_msg_count,
371 p_data => x_msg_data
372 );
373 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
374 ROLLBACK TO create_item_PVT;
375 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
376 FND_MSG_PUB.Count_And_Get
377 ( p_count => x_msg_count,
378 p_data => x_msg_data
379 );
380 WHEN OTHERS THEN
381 ROLLBACK TO create_item_PVT;
382 x_return_status := FND_API.G_RET_STS_ERROR;
383 IF FND_MSG_PUB.Check_Msg_Level
384 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
385 THEN
386 FND_MSG_PUB.Add_Exc_Msg
387 ( G_PKG_NAME ,
388 l_api_name
389 );
390 END IF;
391 FND_MSG_PUB.Count_And_Get
392 ( p_count => x_msg_count ,
393 p_data => x_msg_data
394 );
395
396 END create_item_wrap_sss;
397
398 PROCEDURE delete_item (p_api_version_number IN NUMBER,
399 p_init_msg_list IN VARCHAR2,
400 p_commit IN VARCHAR2,
401 p_email_server_id IN NUMBER,
402 p_dns_name IN VARCHAR2,
403 p_ip_address IN VARCHAR2,
404 p_port IN NUMBER,
405 x_return_status OUT NOCOPY VARCHAR2,
406 x_msg_count OUT NOCOPY NUMBER,
407 x_msg_data OUT NOCOPY VARCHAR2
408 ) is
409 l_api_name VARCHAR2(255):='delete_item';
410 l_api_version_number NUMBER:=1.0;
411
412 BEGIN
413 -- Standard Start of API savepoint
414 SAVEPOINT delete_item_PVT;
415 -- Standard call to check for call compatibility.
416 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
417 p_api_version_number,
418 l_api_name,
419 G_PKG_NAME)
420 THEN
421 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
422 END IF;
423 -- Initialize message list if p_init_msg_list is set to TRUE.
424 IF FND_API.to_Boolean( p_init_msg_list )
425 THEN
426 FND_MSG_PUB.initialize;
427 END IF;
428 -- Initialize API return status to SUCCESS
429 x_return_status := FND_API.G_RET_STS_SUCCESS;
430 if p_email_server_id =FND_API.G_MISS_NUM then
431 delete from iem_email_servers
432 where ip_address=p_ip_address and dns_name=p_dns_name and port=p_port;
433 else
434 delete from iem_email_servers
435 where email_server_id=p_email_server_id;
436 end if;
437
438 -- Standard Check Of p_commit.
439 IF FND_API.To_Boolean(p_commit) THEN
440 COMMIT WORK;
441 END IF;
442 -- Standard callto get message count and if count is 1, get message info.
443 FND_MSG_PUB.Count_And_Get
444 ( p_count => x_msg_count,
445 p_data => x_msg_data
446 );
447 EXCEPTION
448 WHEN FND_API.G_EXC_ERROR THEN
449 ROLLBACK TO delete_item_PVT;
450 x_return_status := FND_API.G_RET_STS_ERROR ;
451 FND_MSG_PUB.Count_And_Get
452 ( p_count => x_msg_count,
453 p_data => x_msg_data
454 );
455 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
456 ROLLBACK TO delete_item_PVT;
457 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
458 FND_MSG_PUB.Count_And_Get
459 ( p_count => x_msg_count,
460 p_data => x_msg_data
461 );
462 WHEN OTHERS THEN
463 ROLLBACK TO delete_item_PVT;
464 x_return_status := FND_API.G_RET_STS_ERROR;
465 IF FND_MSG_PUB.Check_Msg_Level
466 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
467 THEN
468 FND_MSG_PUB.Add_Exc_Msg
469 ( G_PKG_NAME ,
470 l_api_name
471 );
472 END IF;
473 FND_MSG_PUB.Count_And_Get
474 ( p_count => x_msg_count ,
475 p_data => x_msg_data
476 );
477
478 END delete_item;
479
480
481 PROCEDURE update_item_sss (p_api_version_number IN NUMBER,
482 p_init_msg_list IN VARCHAR2,
483 p_commit IN VARCHAR2,
484 p_email_server_id IN NUMBER,
485 p_server_name IN varchar2,
486 p_dns_name IN VARCHAR2,
487 p_ip_address IN VARCHAR2,
488 p_port IN NUMBER,
489 p_server_type_id IN number,
490 p_rt_availability in varchar2,
491 p_server_group_id in number,
492 p_LAST_UPDATED_BY NUMBER,
493 p_LAST_UPDATE_DATE DATE,
494 p_LAST_UPDATE_LOGIN NUMBER,
495 p_ATTRIBUTE1 VARCHAR2,
496 p_ATTRIBUTE2 VARCHAR2,
497 p_ATTRIBUTE3 VARCHAR2,
498 p_ATTRIBUTE4 VARCHAR2,
499 p_ATTRIBUTE5 VARCHAR2,
500 p_ATTRIBUTE6 VARCHAR2,
501 p_ATTRIBUTE7 VARCHAR2,
502 p_ATTRIBUTE8 VARCHAR2,
503 p_ATTRIBUTE9 VARCHAR2,
504 p_ATTRIBUTE10 VARCHAR2,
505 p_ATTRIBUTE11 VARCHAR2,
506 p_ATTRIBUTE12 VARCHAR2,
507 p_ATTRIBUTE13 VARCHAR2,
508 p_ATTRIBUTE14 VARCHAR2,
509 p_ATTRIBUTE15 VARCHAR2,
510 x_return_status OUT NOCOPY VARCHAR2,
511 x_msg_count OUT NOCOPY NUMBER,
512 x_msg_data OUT NOCOPY VARCHAR2
513 ) is
514 l_api_name VARCHAR2(255):='update_item_sss';
515 l_api_version_number NUMBER:=1.0;
516 l_es_count NUMBER:=0;
517 l_type_cnt NUMBER;
518 l_grp_cnt NUMBER;
519 l_LAST_UPDATED_BY NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('USER_ID')) ;
520 l_LAST_UPDATE_LOGIN NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('LOGIN_ ID')) ;
521 IEM_DUP_EMAIL_SERVER EXCEPTION;
522 IEM_NON_EXISTENT_SERVER_GRP EXCEPTION;
523
524 BEGIN
525 -- Standard Start of API savepoint
526 SAVEPOINT update_item_PVT;
527 -- Standard call to check for call compatibility.
528 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
529 p_api_version_number,
530 l_api_name,
531 G_PKG_NAME)
532 THEN
533 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
534 END IF;
535 -- Initialize message list if p_init_msg_list is set to TRUE.
536 IF FND_API.to_Boolean( p_init_msg_list )
537 THEN
538 FND_MSG_PUB.initialize;
539 END IF;
540 -- Initialize API return status to SUCCESS
541 x_return_status := FND_API.G_RET_STS_SUCCESS;
542
543 -- select email_server_id into l_es_count from iem_email_servers
544 -- where UPPER(dns_name)=UPPER(p_dns_name)
545 -- and ip_address=p_ip_address and port=p_port;
546
547 -- if l_es_count <> p_email_server_id then
548 -- raise IEM_DUP_EMAIL_SERVER;
549 -- end if;
550
551 if p_server_type_id <>FND_API.G_MISS_NUM then
552 -- Check For Existing Server Type Id
553
554 Select count(*) into l_type_cnt from iem_email_server_types
555 where email_server_type_id=p_server_type_id
556 and rownum=1;
557 IF l_type_cnt=0 THEN
558 FND_MESSAGE.SET_NAME('IEM','IEM_NON_EXISTENT_SERVER_TYPE');
559 APP_EXCEPTION.RAISE_EXCEPTION;
560 END IF;
561 end if;
562
563 /*Check For Existing Server Group Id */
564 if p_server_group_id <> FND_API.G_MISS_NUM then
565 select count(*) into l_grp_cnt from iem_server_groups
566 where server_group_id=p_server_group_id
567 and rownum=1;
568 IF l_grp_cnt = 0 then
569 raise IEM_NON_EXISTENT_SERVER_GRP;
570 --FND_MESSAGE.SET_NAME('IEM','IEM_NON_EXISTENT_SERVER_GRP');
571 --APP_EXCEPTION.RAISE_EXCEPTION;
572 END IF;
573 end if;
574
575 if p_email_server_id = FND_API.G_MISS_NUM then
576
577 UPDATE IEM_EMAIL_SERVERS
578 set server_name=decode(p_server_name,FND_API.G_MISS_CHAR, NULL, NULL, server_name,p_server_name),
579 rt_availability=decode(p_rt_availability,FND_API.G_MISS_CHAR, NULL, NULL,rt_availability,p_rt_availability),
580 server_type_id=decode(p_server_type_id,FND_API.G_MISS_NUM, NULL, NULL,server_type_id,p_server_type_id),
581 server_group_id=decode(p_server_group_id,FND_API.G_MISS_NUM, NULL, NULL,server_group_id,p_server_group_id),
582 LAST_UPDATE_DATE = sysdate,
583 LAST_UPDATED_BY = decode(l_LAST_UPDATED_BY, null,-1,l_LAST_UPDATED_BY),
584 LAST_UPDATE_LOGIN = decode( l_LAST_UPDATE_LOGIN,null,-1,l_LAST_UPDATE_LOGIN),
585 ATTRIBUTE1 = decode( p_ATTRIBUTE1, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE1, p_ATTRIBUTE1),
586 ATTRIBUTE2 = decode( p_ATTRIBUTE2, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE2, p_ATTRIBUTE2),
587 ATTRIBUTE3 = decode( p_ATTRIBUTE3, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE3, p_ATTRIBUTE3),
588 ATTRIBUTE4 = decode( p_ATTRIBUTE4, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE4, p_ATTRIBUTE4),
589 ATTRIBUTE5 = decode( p_ATTRIBUTE5, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE5, p_ATTRIBUTE5),
590 ATTRIBUTE6 = decode( p_ATTRIBUTE6, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE6, p_ATTRIBUTE6),
591 ATTRIBUTE7 = decode( p_ATTRIBUTE7, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE7, p_ATTRIBUTE7),
592 ATTRIBUTE8 = decode( p_ATTRIBUTE8, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE8, p_ATTRIBUTE8),
593 ATTRIBUTE9 = decode( p_ATTRIBUTE9, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE9, p_ATTRIBUTE9),
594 ATTRIBUTE10 = decode( p_ATTRIBUTE10, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE10, p_ATTRIBUTE10),
595 ATTRIBUTE11 = decode( p_ATTRIBUTE11, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE11, p_ATTRIBUTE11),
596 ATTRIBUTE12 = decode( p_ATTRIBUTE12, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE12, p_ATTRIBUTE12),
597 ATTRIBUTE13 = decode( p_ATTRIBUTE13, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE13, p_ATTRIBUTE13),
598 ATTRIBUTE14 = decode( p_ATTRIBUTE14, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE14, p_ATTRIBUTE14),
599 ATTRIBUTE15 = decode( p_ATTRIBUTE15, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE15, p_ATTRIBUTE15)
600 where dns_name=p_dns_name and ip_address=p_ip_address and port=p_port;
601 ELSE
602
603 UPDATE IEM_EMAIL_SERVERS
604 SET server_name=decode(p_server_name,FND_API.G_MISS_CHAR, NULL, NULL,server_name,p_server_name),
605 server_type_id=decode(p_server_type_id,FND_API.G_MISS_NUM, NULL, NULL,server_type_id,p_server_type_id),
606 rt_availability=decode(p_rt_availability,FND_API.G_MISS_CHAR, NULL, NULL,rt_availability,p_rt_availability),
607 server_group_id=decode(p_server_group_id,FND_API.G_MISS_NUM, NULL, NULL,server_group_id,p_server_group_id),
608 dns_name=decode(p_dns_name,FND_API.G_MISS_CHAR, NULL, NULL,dns_name,p_dns_name),
609 ip_address=decode(p_ip_address,FND_API.G_MISS_CHAR, NULL, NULL,ip_address,p_ip_address),
610 port=decode(p_port,FND_API.G_MISS_NUM, NULL, NULL,port,p_port),
611 LAST_UPDATE_DATE = sysdate,
612 LAST_UPDATED_BY = l_LAST_UPDATED_BY,
613 LAST_UPDATE_LOGIN = l_LAST_UPDATE_LOGIN,
614 ATTRIBUTE1 = decode( p_ATTRIBUTE1, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE1, p_ATTRIBUTE1),
615 ATTRIBUTE2 = decode( p_ATTRIBUTE2, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE2, p_ATTRIBUTE2),
616 ATTRIBUTE3 = decode( p_ATTRIBUTE3, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE3, p_ATTRIBUTE3),
617 ATTRIBUTE4 = decode( p_ATTRIBUTE4, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE4, p_ATTRIBUTE4),
618 ATTRIBUTE5 = decode( p_ATTRIBUTE5, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE5, p_ATTRIBUTE5),
619 ATTRIBUTE6 = decode( p_ATTRIBUTE6, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE6, p_ATTRIBUTE6),
620 ATTRIBUTE7 = decode( p_ATTRIBUTE7, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE7, p_ATTRIBUTE7),
621 ATTRIBUTE8 = decode( p_ATTRIBUTE8, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE8, p_ATTRIBUTE8),
622 ATTRIBUTE9 = decode( p_ATTRIBUTE9, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE9, p_ATTRIBUTE9),
623 ATTRIBUTE10 = decode( p_ATTRIBUTE10, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE10, p_ATTRIBUTE10),
624 ATTRIBUTE11 = decode( p_ATTRIBUTE11, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE11, p_ATTRIBUTE11),
625 ATTRIBUTE12 = decode( p_ATTRIBUTE12, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE12, p_ATTRIBUTE12),
626 ATTRIBUTE13 = decode( p_ATTRIBUTE13, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE13, p_ATTRIBUTE13),
627 ATTRIBUTE14 = decode( p_ATTRIBUTE14, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE14, p_ATTRIBUTE14),
628 ATTRIBUTE15 = decode( p_ATTRIBUTE15, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE15, p_ATTRIBUTE15)
629 where EMAIL_SERVER_ID=p_email_server_id;
630 end if;
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
642 -- WHEN IEM_DUP_EMAIL_SERVER THEN
643 -- ROLLBACK TO update_item_pvt;
644 -- FND_MESSAGE.SET_NAME('IEM','IEM_ADMIN_DUP_EMAIL_SERVER');
645 -- FND_MSG_PUB.Add;
646 -- x_return_status := FND_API.G_RET_STS_ERROR ;
647 -- FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
648 WHEN IEM_NON_EXISTENT_SERVER_GRP THEN
649 ROLLBACK TO update_item_pvt;
650 FND_MESSAGE.SET_NAME('IEM','IEM_NON_EXISTENT_SERVER_GRP');
651 FND_MSG_PUB.Add;
652 x_return_status := FND_API.G_RET_STS_ERROR ;
653 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
654
655 WHEN DUP_VAL_ON_INDEX THEN
656 ROLLBACK TO update_item_pvt;
657 -- FND_MESSAGE.SET_NAME('IEM','IEM_ADMIN_GRP_DUP_RECORD');
658 FND_MESSAGE.SET_NAME('IEM','IEM_ADMIN_DUP_EMAIL_SERVER');
659 FND_MSG_PUB.Add;
660 x_return_status := FND_API.G_RET_STS_ERROR ;
661 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
662
663
664 WHEN FND_API.G_EXC_ERROR THEN
665 ROLLBACK TO update_item_PVT;
666 x_return_status := FND_API.G_RET_STS_ERROR ;
667 FND_MSG_PUB.Count_And_Get
668 ( p_count => x_msg_count,
669 p_data => x_msg_data
670 );
671 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
672 ROLLBACK TO update_item_PVT;
673 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
674 FND_MSG_PUB.Count_And_Get
675 ( p_count => x_msg_count,
676 p_data => x_msg_data
677 );
678 WHEN OTHERS THEN
679 ROLLBACK TO update_item_PVT;
680 x_return_status := FND_API.G_RET_STS_ERROR;
681
682
683 IF FND_MSG_PUB.Check_Msg_Level
684 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
685 THEN
686
687 FND_MSG_PUB.Add_Exc_Msg
688 ( G_PKG_NAME ,
689 l_api_name
690 );
691 END IF;
692 FND_MSG_PUB.Count_And_Get
693 ( p_count => x_msg_count ,
694 p_data => x_msg_data
695 );
696
697 END update_item_sss;
698
699
700 PROCEDURE update_item_wrap_sss (p_api_version_number IN NUMBER,
701 p_init_msg_list IN VARCHAR2,
702 p_commit IN VARCHAR2,
703 p_email_server_id IN NUMBER,
704 p_server_name IN varchar2,
705 p_dns_name IN VARCHAR2,
706 p_ip_address IN VARCHAR2,
707 p_port IN NUMBER,
708 p_server_type_id IN number,
709 p_rt_availability in varchar2,
710 p_server_group_id in number,
711 x_return_status OUT NOCOPY VARCHAR2,
712 x_msg_count OUT NOCOPY NUMBER,
713 x_msg_data OUT NOCOPY VARCHAR2
714 ) is
715 l_api_name VARCHAR2(255):='update_item_wrap_sss';
716 l_api_version_number NUMBER:=1.0;
717 l_seq_id number;
718 l_grp_cnt number;
719 l_type_cnt number;
720 l_stat varchar2(20);
721 l_count number;
722 l_data varchar2(300);
723 l_email_server_id number;
724 l_email_server_type varchar2(20);
725 l_CREATED_BY NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('USER_ID'));
726 l_LAST_UPDATED_BY NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('USER_ID')) ;
727 l_LAST_UPDATE_LOGIN NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('LOGIN_ ID')) ;
728
729 EXCP_EMAIL_SERVER EXCEPTION;
730
731 BEGIN
732 -- Standard Start of API savepoint
733 SAVEPOINT create_item_PVT;
734 -- Standard call to check for call compatibility.
735 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
736 p_api_version_number,
737 l_api_name,
738 G_PKG_NAME)
739 THEN
740 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
741 END IF;
742 -- Initialize message list if p_init_msg_list is set to TRUE.
743 IF FND_API.to_Boolean( p_init_msg_list )
744 THEN
745 FND_MSG_PUB.initialize;
746 END IF;
747 -- Initialize API return status to SUCCESS
748 x_return_status := FND_API.G_RET_STS_SUCCESS;
749
750
751 IEM_EMAIL_SERVERS_PVT.update_item_sss(
752 p_api_version_number =>1.0,
753 p_init_msg_list => FND_API.G_FALSE,
754 p_commit=>FND_API.G_FALSE,
755 p_email_server_id => p_email_server_id,
756 p_server_name => p_server_name,
757 p_dns_name => p_dns_name,
758 p_ip_address => p_ip_address,
759 p_port => p_port,
760 p_server_type_id => p_server_type_id,
761 p_rt_availability => p_rt_availability,
762 p_server_group_id => p_server_group_id,
763 p_LAST_UPDATED_BY =>TO_NUMBER (FND_PROFILE.VALUE('USER_ID')),
764 p_LAST_UPDATE_DATE =>SYSDATE,
765 p_LAST_UPDATE_LOGIN=>TO_NUMBER (FND_PROFILE.VALUE('LOGIN_ID')) ,
766 p_ATTRIBUTE1 =>null,
767 p_ATTRIBUTE2 =>null,
768 p_ATTRIBUTE3 =>null,
769 p_ATTRIBUTE4 =>null,
770 p_ATTRIBUTE5 =>null,
771 p_ATTRIBUTE6 =>null,
772 p_ATTRIBUTE7 =>null,
773 p_ATTRIBUTE8 =>null,
774 p_ATTRIBUTE9 =>null,
775 p_ATTRIBUTE10 =>null,
776 p_ATTRIBUTE11 =>null,
777 p_ATTRIBUTE12 =>null,
778 p_ATTRIBUTE13 =>null,
779 p_ATTRIBUTE14 =>null,
780 p_ATTRIBUTE15 =>null,
781 x_return_status =>l_stat,
782 x_msg_count => l_count,
783 x_msg_data => l_data);
784
785 IF l_stat <>'S' then
786 raise EXCP_EMAIL_SERVER;
787 END IF;
788
789 -- Update Cache Audit Trail
790 select email_server_type into l_email_server_type from iem_email_server_types where email_server_type_id=p_server_type_id;
791
792 if p_email_server_id = FND_API.G_MISS_NUM then
793 select email_server_id into l_email_server_id from iem_email_servers
794 where UPPER(dns_name)=UPPER(p_dns_name)
795 and ip_address=p_ip_address and port=p_port;
796 else
797 l_email_server_id := p_email_server_id;
798 end if;
799
800 IEM_COMP_RT_STATS_PVT.create_item(p_api_version_number =>1.0,
801 p_init_msg_list => FND_API.G_FALSE,
802 p_commit => FND_API.G_FALSE,
803 p_type => l_email_server_type,
804 p_param => 'UPDATE',
805 p_value => l_email_server_id,
806 x_return_status => l_stat,
807 x_msg_count => l_count,
808 x_msg_data => l_data
809 );
810 -- Not raise error when failed to insert data into iem_comp_rt_stats, it is not user error.
811
812 -- Standard Check Of p_commit.
813 IF FND_API.To_Boolean(p_commit) THEN
814 COMMIT WORK;
815 END IF;
816 -- Standard callto get message count and if count is 1, get message info.
817 FND_MSG_PUB.Count_And_Get
818 ( p_count => x_msg_count,
819 p_data => x_msg_data
820 );
821 EXCEPTION
822
823 WHEN EXCP_EMAIL_SERVER THEN
824 ROLLBACK TO create_item_PVT;
825 x_return_status := FND_API.G_RET_STS_ERROR;
826 -- FND_MESSAGE.SET_NAME('IEM', 'IEM_SSS_EMAIL_SVR_NOT_CREATED');
827 -- FND_MSG_PUB.Add;
828 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,p_data => x_msg_data);
829
830 WHEN FND_API.G_EXC_ERROR THEN
831 ROLLBACK TO create_item_PVT;
832 x_return_status := FND_API.G_RET_STS_ERROR ;
833 FND_MSG_PUB.Count_And_Get
834 ( p_count => x_msg_count,
835 p_data => x_msg_data
836 );
837 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
838 ROLLBACK TO create_item_PVT;
839 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
840 FND_MSG_PUB.Count_And_Get
841 ( p_count => x_msg_count,
842 p_data => x_msg_data
843 );
844 WHEN OTHERS THEN
845 ROLLBACK TO create_item_PVT;
846 x_return_status := FND_API.G_RET_STS_ERROR;
847 IF FND_MSG_PUB.Check_Msg_Level
848 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
849 THEN
850 FND_MSG_PUB.Add_Exc_Msg
851 ( G_PKG_NAME ,
852 l_api_name
853 );
854 END IF;
855 FND_MSG_PUB.Count_And_Get
856 ( p_count => x_msg_count ,
857 p_data => x_msg_data
858 );
859
860 END update_item_wrap_sss;
861
862 PROCEDURE delete_item_batch
863 (p_api_version_number IN NUMBER,
864 P_init_msg_list IN VARCHAR2,
865 p_commit IN VARCHAR2,
866 p_group_tbl IN jtf_varchar2_Table_100,
867 x_return_status OUT NOCOPY VARCHAR2,
868 x_msg_count OUT NOCOPY NUMBER,
869 x_msg_data OUT NOCOPY VARCHAR2)
870 IS
871 i INTEGER;
872 l_api_name varchar2(30):='delete_item_batch';
873 l_api_version_number number:=1.0;
874 l_email_server_id number;
875 l_stat varchar2(20);
876 l_count number;
877 l_data varchar2(300);
878 l_server_type_id number;
879 l_email_server_type varchar2(20);
880
881 SERVER_GROUP_NOT_DELETED EXCEPTION;
882
883 BEGIN
884
885 --Standard Savepoint
886 SAVEPOINT delete_item_batch;
887
888 -- Standard call to check for call compatibility.
889 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
890 p_api_version_number,
891 l_api_name,
892 G_PKG_NAME)
893 THEN
894 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
895 END IF;
896
897 --Initialize the message list if p_init_msg_list is set to TRUE
898 If FND_API.to_Boolean(p_init_msg_list) THEN
899 FND_MSG_PUB.initialize;
900 END IF;
901
902 --Initialize API status return
903 x_return_status := FND_API.G_RET_STS_SUCCESS;
904
905 --Actual API starts here
906 FOR i IN p_group_tbl.FIRST..p_group_tbl.LAST LOOP
907 l_email_server_id:=p_group_tbl(i);
908
909 -- Update Cache Audit Trail
910 select server_type_id into l_server_type_id from iem_email_servers WHERE EMAIL_SERVER_ID =l_email_server_id;
911
912 select email_server_type into l_email_server_type from iem_email_server_types where email_server_type_id=l_server_type_id;
913
914 IEM_COMP_RT_STATS_PVT.create_item(p_api_version_number =>1.0,
915 p_init_msg_list => FND_API.G_FALSE,
916 p_commit => FND_API.G_FALSE,
917 p_type => l_email_server_type,
918 p_param => 'DELETE',
919 p_value => l_email_server_id,
920 x_return_status => l_stat,
921 x_msg_count => l_count,
922 x_msg_data => l_data
923 );
924 -- Not raise error when failed to insert data into iem_comp_rt_stats, it is not user error.
925
926 DELETE FROM IEM_EMAIL_SERVERS
927 WHERE EMAIL_SERVER_ID =l_email_server_id;
928
929 END LOOP;
930
931 --Standard check of p_commit
932 IF FND_API.to_Boolean(p_commit) THEN
933 COMMIT WORK;
934 END IF;
935
936
937 EXCEPTION
938 WHEN SERVER_GROUP_NOT_DELETED THEN
939 ROLLBACK TO delete_item_batch;
940 x_return_status := FND_API.G_RET_STS_ERROR;
941 FND_MESSAGE.SET_NAME('IEM', 'IEM_SERVER_GROUP_NOT_DELETED');
942 FND_MSG_PUB.ADD;
943 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
944
945 WHEN FND_API.G_EXC_ERROR THEN
946 ROLLBACK TO delete_item_batch;
947 x_return_status := FND_API.G_RET_STS_ERROR ;
948 FND_MSG_PUB.Count_And_Get
949 ( p_count => x_msg_count,p_data => x_msg_data);
950
951 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
952 ROLLBACK TO delete_item_batch;
953 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
954 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,p_data => x_msg_data);
955
956 WHEN OTHERS THEN
957 ROLLBACK TO delete_item_batch;
958 x_return_status := FND_API.G_RET_STS_ERROR;
959 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
960 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME , l_api_name);
961 END IF;
962
963 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count ,p_data => x_msg_data);
964
965 END delete_item_batch;
966
967 END IEM_EMAIL_SERVERS_PVT ;