[Home] [Help]
PACKAGE BODY: APPS.IEM_ROUTE_CLASS_PVT
Source
1 PACKAGE BODY IEM_ROUTE_CLASS_PVT AS
2 /* $Header: iemvclxb.pls 120.4 2006/06/19 14:33:40 pkesani ship $ */
3 --
4 --
5 -- Purpose: Mantain route classification related operations
6 --
7 -- MODIFICATION HISTORY
8 -- Person Date Comments
9 -- Liang Xia 5/27/2001 added functions
10 -- Liang Xia 6/20/2001 added creating folder on OES when association between email account and classifcation is created
11 -- Liang Xia 11/2/2001 Fixed bug 2086532
12 -- Liang Xia 12/21/2001 Fixed bug 2160160
13 -- Mina Tang 01/17/2002 Added TNS No Listener Exception to delete_folder()
14 -- Liang Xia 01/28/2002 Fixed bug 2193385
15 -- Liang Xia 03/25/2002 Fixed Bug 2279835, 2279824
16 -- KBeagle 07/10/2002 Fix for bug 2456742
17 -- Liang Xia 11/11/2002 Added functions for dynamic classifications (shipped MP-Q)
18 -- Kris Beagle 12/17/2002 Fix for bug 2713006 ICFP-Q:F: ERROR HANDLING: OES LISTENER IS DOWN, ASSOCIATE CLASSIFICATION W/ ACCT.
19 -- Kris Beagle 01/11/2005 Updated for 11i compliance
20 -- Mina Tang 07/26/2005 Implemented soft-delete for R12
21 -- PKESANI 02/16/2006 Removed the where clause "and active_flag='Y'" for bug fix of 4945889
22 -- The change would allow add/delete operations on inactive accounts.
23 -- For Bug 4945916 - Corrected the Save point from
24 -- update_account_class to update_account_class_PVT.
25 -- PKESANI 05/23/2006 Changed the code to get the count of emails with
26 -- the classification, that is to be deleted.
27 -- changed it from iem_post_mdts to iem_rt_proc_emails table.
28 -- --------- ------ ------------------------------------------
29 -- Enter procedure, function bodies as shown below
30 G_PKG_NAME CONSTANT varchar2(30) :='IEM_ROUTE_CLASS_PVT ';
31
32 G_CLASS_ID varchar2(30) ;
33
34 G_created_updated_by NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('USER_ID')) ;
35
36 G_LAST_UPDATE_LOGIN NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('LOGIN_ ID') ) ;
37
38 TYPE t_AcctIdTable is TABLE of iem_account_route_class.email_account_id%TYPE INDEX BY BINARY_INTEGER;
39
40
41 PROCEDURE getRouteClassifications(
42 p_api_version_number IN NUMBER,
43 P_init_msg_list IN VARCHAR2 := null,
44 p_commit IN VARCHAR2 := null,
45
46 emailAccountId IN NUMBER,
47 routeClassifications OUT NOCOPY t_routeClassification,
48 numberOfClassifications OUT NOCOPY NUMBER,
49 x_return_status OUT NOCOPY VARCHAR2,
50 x_msg_count OUT NOCOPY NUMBER,
51 x_msg_data OUT NOCOPY VARCHAR2)
52 IS
53 i INTEGER;
54 l_api_name varchar2(30):='getRouteClassifications';
55 l_api_version_number number:=1.0;
56
57 BEGIN
58
59 --Standard Savepoint
60
61 SAVEPOINT getRouteClassifications;
62
63 -- Standard call to check for call compatibility.
64 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
65 p_api_version_number,
66 l_api_name,
67 G_PKG_NAME)
68 THEN
69 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
70
71 END IF;
72
73
74 --Initialize the message list if p_init_msg_list is set to TRUE
75
76 If FND_API.to_Boolean(p_init_msg_list) THEN
77 FND_MSG_PUB.initialize;
78 END IF;
79
80 --Initialize API status return
81 x_return_status := FND_API.G_RET_STS_SUCCESS;
82
83 routeClassifications(1) := 'Gold';
84
85 routeClassifications(2):= 'Silver';
86 routeClassifications(3) := 'Bronze';
87
88
89 numberOfClassifications := 3;
90
91
92 EXCEPTION
93
94 WHEN FND_API.G_EXC_ERROR THEN
95 ROLLBACK TO getRouteClassifications;
96 x_return_status := FND_API.G_RET_STS_ERROR ;
97 FND_MSG_PUB.Count_And_Get
98
99 ( p_count => x_msg_count,p_data => x_msg_data);
100
101 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
102 ROLLBACK TO getRouteClassifications;
103 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
104
105
106 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,p_data => x_msg_data);
107
108 WHEN OTHERS THEN
109 ROLLBACK TO getRouteClassifications;
110 x_return_status := FND_API.G_RET_STS_ERROR;
111 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
112
113 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME , l_api_name);
114 END IF;
115 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count ,p_data => x_msg_data);
116 END;
117
118
119 PROCEDURE delete_item_batch
120 (p_api_version_number IN NUMBER,
121 P_init_msg_list IN VARCHAR2 := null,
122 p_commit IN VARCHAR2 := null,
123 p_class_ids_tbl IN jtf_varchar2_Table_100,
124 x_return_status OUT NOCOPY VARCHAR2,
125 x_msg_count OUT NOCOPY NUMBER,
126 x_msg_data OUT NOCOPY VARCHAR2)
127 IS
128 i INTEGER;
129 l_api_name varchar2(30):='delete_item_batch';
130
131 l_api_version_number number:=1.0;
132 l_count NUMBER;
133 l_return_status varchar(10);
134 l_data varchar2(255);
135 v_AcctIdTable t_AcctIdTable;
136
137 l_undeleted_class_name_1 varchar2(30);
138
139 l_undeleted_class_name varchar2(3000);
140
141 l_count_msg_postmdt number := 0;
142
143 logMessage varchar2(200);
144
145 CURSOR acct_id_cursor( l_classification_id IN NUMBER ) IS
146 select unique email_account_id from iem_account_route_class where route_classification_id = l_classification_id;
147
148
149 IEM_RT_CLASS_NOT_DELETED EXCEPTION;
150 --IEM_UNEXPT_ERR_DELETE_FOLDER EXCEPTION;
151 BEGIN
152
153 --Standard Savepoint
154 SAVEPOINT delete_item_batch;
155
156
157 -- Standard call to check for call compatibility.
158 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
159
160 p_api_version_number,
161 l_api_name,
162 G_PKG_NAME)
163
164 THEN
165 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
166 END IF;
167
168 --Initialize the message list if p_init_msg_list is set to TRUE
169
170 If FND_API.to_Boolean(p_init_msg_list) THEN
171 FND_MSG_PUB.initialize;
172 END IF;
173
174 --Initialize API status return
175 x_return_status := FND_API.G_RET_STS_SUCCESS;
176
177 --Actual API starts here
178
179 if ( p_class_ids_tbl.count <> 0 ) then
180
181 FOR i IN p_class_ids_tbl.FIRST..p_class_ids_tbl.LAST LOOP
182
183 -- Commented out from R12
184 -- select count(*) into l_count_msg_postmdt from iem_post_mdts where rt_classification_id=p_class_ids_tbl(i);
185
186 select count(*) into l_count_msg_postmdt from iem_rt_proc_emails where rt_classification_id=p_class_ids_tbl(i);
187
188 if l_count_msg_postmdt <> 0 then
189 select name into l_undeleted_class_name_1 from iem_route_classifications where route_classification_id=p_class_ids_tbl(i);
190 l_undeleted_class_name := l_undeleted_class_name||l_undeleted_class_name_1||', ';
191 else
192
193 --First delete classification folder for all the email account that assoicated with this classification
194 --iem_route_class_pvt.delete_folder_on_classId(p_api_version_number =>p_api_version_number,
195 -- p_init_msg_list => p_init_msg_list,
196 -- p_commit => p_init_msg_list,
197 -- p_classification_id =>p_class_ids_tbl(i),
198 -- x_return_status =>l_return_status,
199 -- x_msg_count => l_count,
200 -- x_msg_data => l_data);
201
202
203 --if (l_return_status = FND_API.G_RET_STS_ERROR) then
204
205 -- select name into l_undeleted_class_name_1 from iem_route_classifications where route_classification_id=p_class_ids_tbl(i);
206
207 -- l_undeleted_class_name := l_undeleted_class_name||l_undeleted_class_name_1||', ';
208
209 --elsif (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) then
210 -- raise IEM_UNEXPT_ERR_DELETE_FOLDER;
211
212 --else
213 -- then update priority in iem_account_route_class before deleting an account_route
214 FOR acct_id IN acct_id_cursor(p_class_ids_tbl(i)) LOOP
215 /*
216 if fnd_log.test(FND_LOG.LEVEL_STATEMENT, 'IEM.PLSQL.IEM_ROUTE_PUB.ROUTE.START') then
217
218 logMessage := '[account id ' || to_char(i)||'is
219 : ' || to_char(acct_id.email_account_id) || ']';
220
221 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'IEM.PLSQL.IEM_ROUTE_CLASS_PVT.delete_item_batch.START', logMessage);
222 end if;
223 */
224 Update iem_account_route_class ac set ac.priority=ac.priority-1
225 where ac.email_account_id=acct_id.email_account_id and ac.priority > (Select be.priority from iem_account_route_class be
226 where be.route_classification_id=p_class_ids_tbl(i) and be.email_account_id = acct_id.email_account_id);
227 commit;
228 END LOOP;
229
230 --finially delete from IEM_ROUTE_CLASSIFICATIONS, IEM_ACCOUNT_ROUTE_CLASS and IEM_ROUTE_CLASSIFICATIONS
231 UPDATE IEM_ROUTE_CLASSIFICATIONS
232 SET DELETED_FLAG='Y'
233 WHERE route_classification_id = p_class_ids_tbl(i);
234
235 DELETE
236 FROM IEM_ROUTE_CLASS_RULES
237 WHERE route_classification_id = p_class_ids_tbl(i);
238
239 DELETE
240 FROM IEM_ACCOUNT_ROUTE_CLASS
241 WHERE route_classification_id = p_class_ids_tbl(i);
242 commit;
243
244 --end if;
245
246 end if;
247
248 END LOOP;
249
250 end if;
251
252 --add names of un_deleted classifications into message
253 if l_undeleted_class_name is not null then
254 l_undeleted_class_name := RTRIM(l_undeleted_class_name, ', ');
255 x_return_status := FND_API.G_RET_STS_ERROR;
256 FND_MESSAGE.SET_NAME('IEM', 'IEM_RT_CLASS_FAILED_DEL_CLASS');
257 FND_MESSAGE.SET_TOKEN('CLASSIFICATION', l_undeleted_class_name);
258 FND_MSG_PUB.ADD;
259 end if;
260
261
262 --Standard check of p_commit
263 IF FND_API.to_Boolean(p_commit) THEN
264 COMMIT WORK;
265 END IF;
266
267 FND_MSG_PUB.Count_And_Get
268 ( p_count => x_msg_count,
269 p_data => x_msg_data
270 );
271
272
273
274 EXCEPTION
275
276 --WHEN IEM_UNEXPT_ERR_DELETE_FOLDER THEN
277 -- ROLLBACK TO delete_item_batch;
278 -- x_return_status := FND_API.G_RET_STS_ERROR;
279 --FND_MESSAGE.SET_NAME('IEM', 'IEM_UNEXPT_ERR_DELETE_FOLDER');
280 --FND_MSG_PUB.ADD;
281 -- FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
282
283
284 WHEN FND_API.G_EXC_ERROR THEN
285 ROLLBACK TO delete_item_batch;
286 x_return_status := FND_API.G_RET_STS_ERROR ;
287
288 FND_MSG_PUB.Count_And_Get
289 ( p_count => x_msg_count,p_data => x_msg_data);
290
291
292 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
293
294 ROLLBACK TO delete_item_batch;
295 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
296 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,p_data => x_msg_data);
297
298 WHEN OTHERS THEN
299 ROLLBACK TO delete_item_batch;
300 x_return_status := FND_API.G_RET_STS_ERROR;
301
302 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
303 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME , l_api_name);
304 END IF;
305
306 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count ,p_data => x_msg_data);
307
308 END delete_item_batch;
309
310
311 PROCEDURE create_item_wrap (
312 p_api_version_number IN NUMBER,
313 p_init_msg_list IN VARCHAR2 := null,
314 p_commit IN VARCHAR2 := null,
315
316 p_class_name IN VARCHAR2,
317 p_class_description IN VARCHAR2:= null,
318 p_class_boolean_type_code IN VARCHAR2,
319 p_proc_name IN VARCHAR2 := null,
320
321 p_rule_key_typecode_tbl IN jtf_varchar2_Table_100,
322 p_rule_operator_typecode_tbl IN jtf_varchar2_Table_100,
323 p_rule_value_tbl IN jtf_varchar2_Table_300,
324
325 x_return_status OUT NOCOPY VARCHAR2,
326 x_msg_count OUT NOCOPY NUMBER,
327 x_msg_data OUT NOCOPY VARCHAR2 ) is
328
329 l_api_name VARCHAR2(255):='create_item_wrap';
330
331 l_api_version_number NUMBER:=1.0;
332
333 l_class_id IEM_ROUTE_CLASSIFICATIONS.ROUTE_CLASSIFICATION_ID%TYPE;
334 l_class_rule_id IEM_ROUTE_CLASS_RULES.ROUTE_CLASS_RULE_ID%TYPE;
335 l_return_type VARCHAR2(30);
336
337 l_userid NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('USER_ID')) ;
338 l_login NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('LOGIN_ID')) ;
339
340 l_return_status VARCHAR2(20) := FND_API.G_RET_STS_SUCCESS;
341 l_msg_count NUMBER := 0;
342
343 l_msg_data VARCHAR2(2000);
344
345
346 IEM_RT_CLASS_NOT_CREATED EXCEPTION;
347 IEM_RT_CLASS_RULE_NOT_CREATED EXCEPTION;
348
349 BEGIN
350 -- Standard Start of API savepoint
351
352 SAVEPOINT create_item_wrap;
353
354 -- Standard call to check for call compatibility.
355 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
356 p_api_version_number,
357 l_api_name,
358
359
360 G_PKG_NAME)
361 THEN
362 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
363 END IF;
364
365 -- Initialize message list if p_init_msg_list is set to TRUE.
366
367 IF FND_API.to_Boolean( p_init_msg_list )
368 THEN
369 FND_MSG_PUB.initialize;
370 END IF;
371
372
373 -- Initialize API return status to SUCCESS
374 x_return_status := FND_API.G_RET_STS_SUCCESS;
375
376
377 --API Body
378 --API Body
379 /*
380 FND_LOG_REPOSITORY.init(null,null);
381
382 if fnd_log.test(FND_LOG.LEVEL_STATEMENT, 'IEM.PLSQL.IEM_ROUTE_CLASS_PVT.CREATE_ITEM_WRAP.START') then
383 logMessage := '[create item is called!]';
384 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'IEM.PLSQL.IEM_ROUTE_CLASS_PVT.CREATE_ITEM_WRAP.START', logMessage);
385 end if;
386 */
387 --Now call the create_item() to create the acccount
388 if ( p_class_boolean_type_code = 'DYNAMIC' ) then
389 l_return_type := p_rule_key_typecode_tbl(1);
390 else
391 l_return_type := FND_API.G_MISS_CHAR;
392 end if;
393
394 --Now call the create_item() to create the acccount
395 iem_route_class_pvt.create_item_class (
396
397 p_api_version_number=>p_api_version_number,
398 p_init_msg_list => p_init_msg_list,
399 p_commit => p_commit,
400 p_name => p_class_name,
401 p_description => p_class_description,
402 p_boolean_type_code =>p_class_boolean_type_code,
403 p_proc_name => p_proc_name,
404 p_return_type => l_return_type,
405 x_return_status =>l_return_status,
406 x_msg_count => l_msg_count,
407
408 x_msg_data => l_msg_data);
409
410 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
411 raise IEM_RT_CLASS_NOT_CREATED;
412
413 end if;
414
415
416
417 --Getting the newly created email account id
418 l_class_id := G_CLASS_ID;
419
420 --dbms_output.put_line('route id : '||l_route_id);
421
422 FOR i IN p_rule_key_typecode_tbl.FIRST..p_rule_operator_typecode_tbl.LAST loop
423
424
425 iem_route_class_pvt.create_item_class_rules (
426 p_api_version_number=>p_api_version_number,
427 p_init_msg_list => p_init_msg_list,
428 p_commit => p_commit,
429 p_class_id => l_class_id,
430 p_key_type_code => p_rule_key_typecode_tbl(i),
431 p_operator_type_code => p_rule_operator_typecode_tbl(i),
432 p_value =>p_rule_value_tbl(i),
433 x_return_status =>l_return_status,
434 x_msg_count => l_msg_count,
435 x_msg_data => l_msg_data);
436
437
438
439 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
440
441 raise IEM_RT_CLASS_RULE_NOT_CREATED;
442
443 end if;
444 end loop;
445
446 EXCEPTION
447 WHEN IEM_RT_CLASS_NOT_CREATED THEN
448 ROLLBACK TO create_item_wrap;
449 x_return_status := FND_API.G_RET_STS_ERROR ;
450 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
451
452 WHEN IEM_RT_CLASS_RULE_NOT_CREATED THEN
453 ROLLBACK TO create_item_wrap;
454 FND_MESSAGE.SET_NAME('IEM','IEM_RT_CLASS_RULE_NOT_CREATED');
455 FND_MSG_PUB.Add;
456 x_return_status := FND_API.G_RET_STS_ERROR ;
457 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
458
459
460 WHEN FND_API.G_EXC_ERROR THEN
461 ROLLBACK TO create_item_wrap;
462 x_return_status := FND_API.G_RET_STS_ERROR ;
463 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,p_data => x_msg_data);
464
465
466
467
468 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
469 ROLLBACK TO create_item_wrap;
470 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
471 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
472
473 WHEN OTHERS THEN
474 ROLLBACK TO create_item_wrap;
475 x_return_status := FND_API.G_RET_STS_ERROR;
476 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
477 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME , l_api_name);
478 END IF;
479
480 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count, p_data => x_msg_data );
481
482
483
484 END create_item_wrap;
485
486 PROCEDURE create_item_class (
487 p_api_version_number IN NUMBER,
488 p_init_msg_list IN VARCHAR2 := null,
489 p_commit IN VARCHAR2 := null,
490 p_name IN VARCHAR2,
491 p_description IN VARCHAR2:= null,
492 p_boolean_type_code IN VARCHAR2,
493 p_is_sss IN VARCHAR2 := null,
494 p_proc_name IN VARCHAR2 := null,
495 p_return_type IN VARCHAR2 := null,
496 x_return_status OUT NOCOPY VARCHAR2,
497 x_msg_count OUT NOCOPY NUMBER,
498 x_msg_data OUT NOCOPY VARCHAR2
499 ) is
500 l_api_name VARCHAR2(255):='create_item_class';
501 l_api_version_number NUMBER:=1.0;
502 l_seq_id NUMBER;
503 l_description VARCHAR2(256);
504 l_proc_name VARCHAR2(256);
505 l_return_status VARCHAR2(20) := FND_API.G_RET_STS_SUCCESS;
506 l_msg_count NUMBER := 0;
507 l_msg_data VARCHAR2(2000);
508 l_name_count NUMBER;
509 IEM_RT_CLASS_DUP_NAME EXCEPTION;
510 IEM_ADM_NO_PROCEDURE_NAME EXCEPTION;
511 l_IEM_INVALID_PROCEDURE EXCEPTION;
512 BEGIN
513 -- Standard Start of API savepoint
514
515 SAVEPOINT create_item_class_PVT;
516
517
518 -- Standard call to check for call compatibility.
519 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
520 p_api_version_number,
521 l_api_name,
522 G_PKG_NAME)
523 THEN
524
525 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
526 END IF;
527
528 -- Initialize message list if p_init_msg_list is set to TRUE.
529
530 IF FND_API.to_Boolean( p_init_msg_list )
531 THEN
532 FND_MSG_PUB.initialize;
533
534 END IF;
535
536 -- Initialize API return status to SUCCESS
537 x_return_status := FND_API.G_RET_STS_SUCCESS;
538
539
540 --begins here
541
542 --check duplicate value for attribute Name
543 select count(*) into l_name_count from iem_route_classifications where UPPER(name) = UPPER(p_name) and deleted_flag='N';
544
545 if l_name_count > 0 then
546 raise IEM_RT_CLASS_DUP_NAME;
547 end if;
548
549 if ( p_boolean_type_code = 'DYNAMIC' ) then
550 if p_proc_name is null or p_proc_name = FND_API.G_MISS_CHAR then
551 raise IEM_ADM_NO_PROCEDURE_NAME;
552 else
553 l_proc_name := LTRIM(RTRIM( p_proc_name ) );
554 --validation goes here.
555 IEM_ROUTE_RUN_PROC_PVT.validProcedure(
556 p_api_version_number => P_Api_Version_Number,
557 p_init_msg_list => FND_API.G_FALSE,
558 p_commit => P_Commit,
559 p_ProcName => l_proc_name,
560 p_return_type => p_return_type,
561 x_return_status => l_return_status,
562 x_msg_count => l_msg_count,
563 x_msg_data => l_msg_data
564 );
565 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
566 raise l_IEM_INVALID_PROCEDURE;
567 end if;
568 end if;
569 else
570 l_proc_name := null;
571 end if;
572
573 if p_description=FND_API.G_MISS_CHAR then
574 l_description := null;
575 else
576 l_description := p_description;
577 end if;
578
579 IF FND_API.To_Boolean( p_is_sss ) THEN
580 G_CLASS_ID := 0;
581 ELSE
582 --get next sequential number for route_id
583 SELECT IEM_ROUTE_CLASSIFICATIONS_s1.nextval
584 INTO l_seq_id
585 FROM dual;
586
587 G_CLASS_ID := l_seq_id;
588 END IF;
589
590 INSERT INTO IEM_ROUTE_CLASSIFICATIONS
591 (
592 ROUTE_CLASSIFICATION_ID,
593 NAME,
594 DESCRIPTION,
595 BOOLEAN_TYPE_CODE,
596 procedure_name,
597 deleted_flag,
598 ATTRIBUTE1,
599 ATTRIBUTE2,
600 ATTRIBUTE3,
601 ATTRIBUTE4,
602 ATTRIBUTE5,
603
604 ATTRIBUTE6,
605 ATTRIBUTE7,
606 ATTRIBUTE8,
607 ATTRIBUTE9,
608 ATTRIBUTE10,
609
610 ATTRIBUTE11,
611
612 ATTRIBUTE12,
613 ATTRIBUTE13,
614 ATTRIBUTE14,
615 ATTRIBUTE15,
616 ATTRIBUTE_CATEGORY,
617 CREATED_BY,
618
619 CREATION_DATE,
620 LAST_UPDATED_BY,
621 LAST_UPDATE_DATE,
622 LAST_UPDATE_LOGIN
623
624 )
625 VALUES
626
627 (
628 G_CLASS_ID,
629 p_name,
630 l_description,
631 p_boolean_type_code,
632 l_proc_name,
633 'N',
634 NULL,
635 NULL,
636 NULL,
637
638 NULL,
639 NULL,
640 NULL,
641 NULL,
642 NULL,
643
644 NULL,
645 NULL,
646 NULL,
647 NULL,
648
649 NULL,
650 NULL,
651
652 NULL,
653 NULL,
654 decode(G_created_updated_by,null,-1,G_created_updated_by),
655 sysdate,
656 decode(G_created_updated_by,null,-1,G_created_updated_by),
657 sysdate,
658 decode(G_LAST_UPDATE_LOGIN,null,-1,G_LAST_UPDATE_LOGIN)
659
660 );
661
662
663 -- Standard Check Of p_commit.
664
665 IF FND_API.To_Boolean(p_commit) THEN
666 COMMIT WORK;
667 END IF;
668
669 -- Standard callto get message count and if count is 1, get message info.
670 FND_MSG_PUB.Count_And_Get
671 ( p_count => x_msg_count,
672 p_data => x_msg_data
673 );
674
675
676 EXCEPTION
677 WHEN l_IEM_INVALID_PROCEDURE THEN
678 ROLLBACK TO create_item_class_PVT;
679 x_return_status := FND_API.G_RET_STS_ERROR ;
680 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
681
682 WHEN IEM_RT_CLASS_DUP_NAME THEN
683 ROLLBACK TO create_item_class_PVT;
684 FND_MESSAGE.SET_NAME('IEM','IEM_RT_CLASS_DUP_NAME');
685 FND_MSG_PUB.Add;
686 x_return_status := FND_API.G_RET_STS_ERROR ;
687 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
688
689 WHEN IEM_ADM_NO_PROCEDURE_NAME THEN
690 ROLLBACK TO create_item_routes_PVT;
691 FND_MESSAGE.SET_NAME('IEM','IEM_ADM_NO_PROCEDURE_NAME');
692 FND_MSG_PUB.Add;
693 x_return_status := FND_API.G_RET_STS_ERROR ;
694 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
695
696 WHEN FND_API.G_EXC_ERROR THEN
697 ROLLBACK TO create_item_class_PVT;
698 x_return_status := FND_API.G_RET_STS_ERROR ;
699
700 FND_MSG_PUB.Count_And_Get
701
702
703 ( p_count => x_msg_count,
704 p_data => x_msg_data
705 );
706
707 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
708 ROLLBACK TO create_item_class_PVT;
709 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
710 FND_MSG_PUB.Count_And_Get
711 ( p_count => x_msg_count,
712 p_data => x_msg_data
713 );
714
715
716
717
718 WHEN OTHERS THEN
719 ROLLBACK TO create_item_class_PVT;
720 x_return_status := FND_API.G_RET_STS_ERROR;
721 IF FND_MSG_PUB.Check_Msg_Level
722 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
723 THEN
724 FND_MSG_PUB.Add_Exc_Msg
725 ( G_PKG_NAME ,
726 l_api_name
727 );
728 END IF;
729
730 FND_MSG_PUB.Count_And_Get
731 ( p_count => x_msg_count,
732
733
734 p_data => x_msg_data
735 );
736
737 END create_item_class;
738
739 PROCEDURE create_item_class_rules (
740 p_api_version_number IN NUMBER,
741 p_init_msg_list IN VARCHAR2 := null,
742 p_commit IN VARCHAR2 := null,
743
744 p_class_id IN NUMBER,
745 p_key_type_code IN VARCHAR2,
746 p_operator_type_code IN VARCHAR2,
747 p_value IN VARCHAR2,
748
749 x_return_status OUT NOCOPY VARCHAR2,
750 x_msg_count OUT NOCOPY NUMBER,
751 x_msg_data OUT NOCOPY VARCHAR2
752 ) is
753 l_api_name VARCHAR2(255):='create_item_route_rules';
754 l_api_version_number NUMBER:=1.0;
755 l_seq_id number;
756
757
758 --IEM_INVALID_DATE_FORMAT EXCEPTION;
759
760 BEGIN
761
762 -- Standard Start of API savepoint
763 SAVEPOINT create_item_class_rules_PVT;
764
765 -- Standard call to check for call compatibility.
766 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
767 p_api_version_number,
768 l_api_name,
769 G_PKG_NAME)
770
771 THEN
772 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
773 END IF;
774
775 -- Initialize message list if p_init_msg_list is set to TRUE.
776
777 IF FND_API.to_Boolean( p_init_msg_list )
778 THEN
779 FND_MSG_PUB.initialize;
780
781 END IF;
782
783 -- Initialize API return status to SUCCESS
784
785 x_return_status := FND_API.G_RET_STS_SUCCESS;
786
787
788 /*
789 -- translate display date format to canonical date
790 if ( substrb(p_key_type_code, 4, 1) = 'D' )then
791
792
793 l_value := displayDT_to_canonical(p_value);
794
795 if ( l_value is NULL ) then
796
797 RAISE IEM_INVALID_DATE_FORMAT;
798
799 end if;
800 else
801 l_value := p_value;
802 end if;
803 */
804
805
806
807 SELECT IEM_ROUTE_CLASS_RULES_s1.nextval
808 INTO l_seq_id
809 FROM dual;
810
811
812
813
814 INSERT INTO IEM_ROUTE_CLASS_RULES
815 (
816 ROUTE_CLASS_RULE_ID,
817 ROUTE_CLASSIFICATION_ID,
818 KEY_TYPE_CODE,
819 OPERATOR_TYPE_CODE,
820 VALUE,
821
822 ATTRIBUTE1,
823 ATTRIBUTE2,
824 ATTRIBUTE3,
825 ATTRIBUTE4,
826
827 ATTRIBUTE5,
828 ATTRIBUTE6,
829
830 ATTRIBUTE7,
831 ATTRIBUTE8,
832 ATTRIBUTE9,
833 ATTRIBUTE10,
834 ATTRIBUTE11,
835 ATTRIBUTE12,
836
837 ATTRIBUTE13,
838 ATTRIBUTE14,
839 ATTRIBUTE15,
840
841 ATTRIBUTE_CATEGORY,
842 CREATED_BY,
843 CREATION_DATE,
844 LAST_UPDATED_BY,
845
846 LAST_UPDATE_DATE,
847 LAST_UPDATE_LOGIN
848 )
849 VALUES
850
851 (
852 l_seq_id,
853
854 p_class_id,
855 p_key_type_code,
856 p_operator_type_code,
857 p_value,
858 NULL,
859 NULL,
860
861 NULL,
862 NULL,
863 NULL,
864 NULL,
865
866 NULL,
867
868 NULL,
869 NULL,
870 NULL,
871 NULL,
872 NULL,
873 NULL,
874 NULL,
875 NULL,
876
877 NULL,
878 decode(G_created_updated_by,null,-1,G_created_updated_by),
879 sysdate,
880
881
882 decode(G_created_updated_by,null,-1,G_created_updated_by),
883 sysdate,
884 decode(G_LAST_UPDATE_LOGIN,null,-1,G_LAST_UPDATE_LOGIN)
885 );
886
887 -- Standard Check Of p_commit.
888 IF FND_API.To_Boolean(p_commit) THEN
889 COMMIT WORK;
890 END IF;
891
892 -- Standard callto get message count and if count is 1, get message info.
893
894 FND_MSG_PUB.Count_And_Get
895 ( p_count => x_msg_count,
896 p_data => x_msg_data
897 );
898
899 EXCEPTION
900
901
902 WHEN FND_API.G_EXC_ERROR THEN
903 ROLLBACK TO create_item_class_rules_PVT;
904 x_return_status := FND_API.G_RET_STS_ERROR ;
905
906
907 FND_MSG_PUB.Count_And_Get
908
909 ( p_count => x_msg_count,
910 p_data => x_msg_data
911 );
912
913 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
914 ROLLBACK TO create_item_class_rules_PVT;
915 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
916 FND_MSG_PUB.Count_And_Get
917 ( p_count => x_msg_count,
918 p_data => x_msg_data
919 );
920
921
922
923
924 WHEN OTHERS THEN
925 ROLLBACK TO create_item_class_rules_PVT;
926 x_return_status := FND_API.G_RET_STS_ERROR;
927 IF FND_MSG_PUB.Check_Msg_Level
928 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
929 THEN
930 FND_MSG_PUB.Add_Exc_Msg
931 ( G_PKG_NAME ,
932 l_api_name
933 );
934
935 END IF;
936 FND_MSG_PUB.Count_And_Get
937 ( p_count => x_msg_count ,
938
939
940 p_data => x_msg_data
941 );
942 END create_item_class_rules;
943
944
945 --update iem_routes, update iem_route_rules, insert iem_route_rules
946 PROCEDURE update_item_wrap (p_api_version_number IN NUMBER,
947 p_init_msg_list IN VARCHAR2 := null,
948 p_commit IN VARCHAR2 := null,
949 p_class_id IN NUMBER ,
950 p_name IN VARCHAR2:= null,
951 p_ruling_chain IN VARCHAR2:= null,
952 p_description IN VARCHAR2:= null,
953 p_procedure_name IN VARCHAR2:= null,
954 --below is the data for update
955 p_update_rule_ids_tbl IN jtf_varchar2_Table_100,
956 p_update_rule_keys_tbl IN jtf_varchar2_Table_100,
957 p_update_rule_operators_tbl IN jtf_varchar2_Table_100,
958 p_update_rule_values_tbl IN jtf_varchar2_Table_300,
959 --below is the data for insert
960 p_new_rule_keys_tbl IN jtf_varchar2_Table_100,
961 p_new_rule_operators_tbl IN jtf_varchar2_Table_100,
962 p_new_rule_values_tbl IN jtf_varchar2_Table_300,
963 --below is the data to be removed
964 p_remove_rule_ids_tbl IN jtf_varchar2_Table_100,
965 x_return_status OUT NOCOPY VARCHAR2,
966 x_msg_count OUT NOCOPY NUMBER,
967 x_msg_data OUT NOCOPY VARCHAR2 )is
968
969
970 l_api_name VARCHAR2(255):='update_item_wrap';
971 l_api_version_number NUMBER:=1.0;
972 l_return_status VARCHAR2(20) := FND_API.G_RET_STS_SUCCESS;
973 l_msg_count NUMBER := 0;
974 l_msg_data VARCHAR2(2000);
975 l_proc_name VARCHAR2(256);
976 l_return_type VARCHAR2(30);
977 IEM_NO_RT_CLASS_UPDATE EXCEPTION;
978 IEM_NO_RULE_UPDATE EXCEPTION;
979 IEM_RULE_NOT_DELETED EXCEPTION;
980
981 IEM_RT_CLS_RULE_NOT_CREATED EXCEPTION;
982 IEM_RT_CLS_NO_RULE EXCEPTION;
983 l_class NUMBER;
984 l_rule_count NUMBER;
985
986 BEGIN
987 -- Standard Start of API savepoint
988 SAVEPOINT update_item_wrap;
989
990
991 -- Standard call to check for call compatibility.
992 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
993 p_api_version_number,
994
995
996 l_api_name,
997 G_PKG_NAME)
998 THEN
999 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1000 END IF;
1001
1002 -- Initialize message list if p_init_msg_list is set to TRUE.
1003 IF FND_API.to_Boolean( p_init_msg_list )
1004
1005 THEN
1006 FND_MSG_PUB.initialize;
1007 END IF;
1008
1009
1010 -- Initialize API return status to SUCCESS
1011
1012 x_return_status := FND_API.G_RET_STS_SUCCESS;
1013
1014 --API Body
1015
1016 --check if the route_id exist before update
1017 select count(*) into l_class from iem_route_classifications where route_classification_id = p_class_id;
1018
1019
1020 if l_class < 1 then
1021 raise IEM_NO_RT_CLASS_UPDATE;
1022 end if;
1023
1024 --Dynamic route validation
1025 if ( p_ruling_chain = 'DYNAMIC' ) then
1026 l_proc_name := LTRIM(RTRIM( p_procedure_name ) );
1027 l_return_type := p_update_rule_keys_tbl(1);
1028 --validation goes here
1029 else
1030 l_proc_name := FND_API.G_MISS_CHAR;
1031 l_return_type := FND_API.G_MISS_CHAR;
1032 end if;
1033
1034 --update iem_routes table
1035 iem_route_class_pvt.update_item_class(
1036 p_api_version_number => l_api_version_number,
1037 p_init_msg_list => FND_API.G_FALSE,
1038 p_commit => FND_API.G_FALSE,
1039 p_class_id => p_class_id,
1040 p_description =>p_description,
1041 p_ruling_chain =>p_ruling_chain,
1042 p_proc_name => l_proc_name,
1043 p_return_type => l_return_type,
1044 x_return_status => l_return_status,
1045 x_msg_count => l_msg_count,
1046 x_msg_data => l_msg_data);
1047
1048 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
1049 raise IEM_NO_RT_CLASS_UPDATE;
1050 end if;
1051
1052
1053
1054 --update iem_route_rules table
1055
1056 if ( p_update_rule_ids_tbl.count <>0 ) then
1057
1058 FOR i IN p_update_rule_ids_tbl.FIRST..p_update_rule_ids_tbl.LAST loop
1059 iem_route_class_pvt.update_item_rule(p_api_version_number => l_api_version_number,
1060 p_init_msg_list => FND_API.G_FALSE,
1061 p_commit => FND_API.G_FALSE,
1062
1063
1064 p_route_class_rule_id => p_update_rule_ids_tbl(i),
1065 p_key_type_code =>p_update_rule_keys_tbl(i),
1066 p_operator_type_code =>p_update_rule_operators_tbl(i),
1067 p_value => p_update_rule_values_tbl(i),
1068
1069
1070
1071 x_return_status => l_return_status,
1072 x_msg_count => l_msg_count,
1073 x_msg_data => l_msg_data);
1074
1075 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
1076 raise IEM_NO_RULE_UPDATE;
1077 end if;
1078
1079
1080 end loop;
1081 end if;
1082
1083
1084
1085
1086 -- update by deleting rules from iem_route_rules table
1087 if ( p_remove_rule_ids_tbl.count <> 0 ) then
1088 FORALL i IN p_remove_rule_ids_tbl.FIRST..p_remove_rule_ids_tbl.LAST
1089
1090 DELETE
1091 FROM IEM_ROUTE_CLASS_RULES
1092 WHERE route_class_rule_id = p_remove_rule_ids_tbl(i);
1093
1094
1095
1096 if SQL%NOTFOUND then
1097
1098 raise IEM_RULE_NOT_DELETED;
1099
1100 end if;
1101 end if;
1102
1103 if ( p_new_rule_keys_tbl.count <> 0 ) then
1104 FOR i IN p_new_rule_keys_tbl.FIRST..p_new_rule_keys_tbl.LAST LOOP
1105 iem_route_class_pvt.create_item_class_rules (p_api_version_number=>p_api_version_number,
1106 p_init_msg_list => p_init_msg_list,
1107 p_commit => p_commit,
1108 p_class_id => p_class_id,
1109 p_key_type_code => p_new_rule_keys_tbl(i),
1110 p_operator_type_code => p_new_rule_operators_tbl(i),
1111
1112
1113 p_value =>p_new_rule_values_tbl(i),
1114
1115 x_return_status =>l_return_status,
1116 x_msg_count => l_msg_count,
1117 x_msg_data => l_msg_data);
1118
1119 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
1120 raise IEM_RT_CLS_RULE_NOT_CREATED;
1121 end if;
1122 END LOOP;
1123 end if;
1124
1125
1126 -- check if exist at least one rule for each route
1127 select count(*) into l_rule_count from iem_route_class_rules where route_classification_id = p_class_id;
1128
1129
1130
1131 if l_rule_count < 1 then
1132 raise IEM_RT_CLS_NO_RULE;
1133 end if;
1134
1135 -- Standard Check Of p_commit.
1136 IF FND_API.To_Boolean(p_commit) THEN
1137 COMMIT WORK;
1138 END IF;
1139
1140
1141 EXCEPTION
1142 WHEN IEM_NO_RT_CLASS_UPDATE THEN
1143 ROLLBACK TO update_item_wrap;
1144
1145
1146 FND_MESSAGE.SET_NAME('IEM','IEM_NO_RT_CLASS_UPDATE');
1147 FND_MSG_PUB.Add;
1148 x_return_status := FND_API.G_RET_STS_ERROR ;
1149 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1150
1151 WHEN IEM_NO_RULE_UPDATE THEN
1152 ROLLBACK TO update_item_wrap;
1153
1154 FND_MESSAGE.SET_NAME('IEM','IEM_NO_RULE_UPDATE');
1155 FND_MSG_PUB.Add;
1156 x_return_status := FND_API.G_RET_STS_ERROR ;
1157 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1158
1159
1160 WHEN IEM_RULE_NOT_DELETED THEN
1161
1162 ROLLBACK TO update_item_wrap;
1163 FND_MESSAGE.SET_NAME('IEM','IEM_RULE_NOT_DELETED');
1164 FND_MSG_PUB.Add;
1165 x_return_status := FND_API.G_RET_STS_ERROR ;
1166 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1167
1168
1169 WHEN IEM_RT_CLS_RULE_NOT_CREATED THEN
1170 ROLLBACK TO update_item_wrap;
1171 FND_MESSAGE.SET_NAME('IEM','IEM_RT_CLS_RULE_NOT_CREATED');
1172 FND_MSG_PUB.Add;
1173 x_return_status := FND_API.G_RET_STS_ERROR ;
1174
1175 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1176
1177
1178 WHEN IEM_RT_CLS_NO_RULE THEN
1179 ROLLBACK TO update_item_wrap;
1180 FND_MESSAGE.SET_NAME('IEM','IEM_RT_CLS_NO_RULE');
1181
1182 FND_MSG_PUB.Add;
1183 x_return_status := FND_API.G_RET_STS_ERROR ;
1184 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1185
1186 WHEN FND_API.G_EXC_ERROR THEN
1187 ROLLBACK TO update_item_wrap;
1188 x_return_status := FND_API.G_RET_STS_ERROR ;
1189
1190 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,p_data => x_msg_data);
1191
1192 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1193
1194 ROLLBACK TO update_item_wrap;
1195
1196 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1197 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1198
1199 WHEN OTHERS THEN
1200 ROLLBACK TO update_item_wrap;
1201 x_return_status := FND_API.G_RET_STS_ERROR;
1202 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1203 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME , l_api_name);
1204
1205 END IF;
1206
1207 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count, p_data => x_msg_data );
1208
1209
1210
1211 END update_item_wrap;
1212
1213 PROCEDURE update_item_class (
1214 p_api_version_number IN NUMBER,
1215 p_init_msg_list IN VARCHAR2 := null,
1216 p_commit IN VARCHAR2 := null,
1217 p_class_id IN NUMBER ,
1218 p_proc_name IN VARCHAR2:= null,
1219 p_return_type IN VARCHAR2:= null,
1220 p_description IN VARCHAR2:= null,
1221 p_ruling_chain IN VARCHAR2:= null,
1222 x_return_status OUT NOCOPY VARCHAR2,
1223 x_msg_count OUT NOCOPY NUMBER,
1224 x_msg_data OUT NOCOPY VARCHAR2
1225 ) is
1226 l_api_name VARCHAR2(255):='update_item_class';
1227 l_api_version_number NUMBER:=1.0;
1228 l_return_status VARCHAR2(20) := FND_API.G_RET_STS_SUCCESS;
1229 l_msg_count NUMBER := 0;
1230 l_msg_data VARCHAR2(2000);
1231 l_proc_name VARCHAR2(256);
1232 l_name_count NUMBER;
1233 l_description VARCHAR2(256);
1234 l_ruling_chain VARCHAR2(30);
1235 IEM_RT_CLASS_DUP_NAME EXCEPTION;
1236 l_IEM_INVALID_PROCEDURE EXCEPTION;
1237 IEM_ADMIN_ROUTE_NO_PROC EXCEPTION;
1238
1239 BEGIN
1240 -- Standard Start of API savepoint
1241
1242 SAVEPOINT update_item_class;
1243
1244 -- Standard call to check for call compatibility.
1245 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
1246
1247 p_api_version_number,
1248 l_api_name,
1249 G_PKG_NAME)
1250 THEN
1251 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1252 END IF;
1253
1254
1255
1256 -- Initialize message list if p_init_msg_list is set to TRUE.
1257 IF FND_API.to_Boolean( p_init_msg_list )
1258 THEN
1259 FND_MSG_PUB.initialize;
1260 END IF;
1261
1262
1263 -- Initialize API return status to SUCCESS
1264 x_return_status := FND_API.G_RET_STS_SUCCESS;
1265
1266 --check duplicate value for attribute Name
1267 /* select count(*) into l_name_count from iem_route_classifications where UPPER(name) = UPPER(p_name) and route_classification_id <> p_class_id;
1268
1269
1270
1271 if l_name_count > 0 then
1272 raise IEM_RT_CLASS_DUP_NAME;
1273 end if;
1274 */
1275
1276 if p_ruling_chain = 'DYNAMIC' then
1277 if ( p_proc_name = FND_API.G_MISS_CHAR ) then
1278 raise IEM_ADMIN_ROUTE_NO_PROC;
1279 elsif ( p_proc_name is null ) then
1280 raise IEM_ADMIN_ROUTE_NO_PROC;
1281 -- l_proc_name := FND_API.G_MISS_CHAR;
1282 else
1283 l_proc_name := LTRIM(RTRIM(p_proc_name));
1284 if ( l_proc_name = '') then
1285 raise IEM_ADMIN_ROUTE_NO_PROC;
1286 else
1287 --validation goes here.
1288 IEM_ROUTE_RUN_PROC_PVT.validProcedure(
1289 p_api_version_number => P_Api_Version_Number,
1290 p_init_msg_list => FND_API.G_FALSE,
1291 p_commit => P_Commit,
1292 p_ProcName => l_proc_name,
1293 p_return_type => p_return_type,
1294 x_return_status => l_return_status,
1295 x_msg_count => l_msg_count,
1296 x_msg_data => l_msg_data
1297 );
1298 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
1299 raise l_IEM_INVALID_PROCEDURE;
1300 end if;
1301 end if;
1302 end if;
1303
1304 end if;
1305
1306 if ( p_description = FND_API.G_MISS_CHAR ) then
1307 l_description := null;
1308 --elsif ( p_description is null )then
1309 -- l_description := FND_API.G_MISS_CHAR;
1310 else
1311 l_description := ltrim(rtrim(p_description));
1312 end if;
1313
1314 if ( p_ruling_chain = FND_API.G_MISS_CHAR ) then
1315 l_ruling_chain := null;
1316 --elsif ( p_ruling_chain is null )then
1317 -- l_ruling_chain := FND_API.G_MISS_CHAR;
1318 else
1319 l_ruling_chain := ltrim(rtrim(p_ruling_chain));
1320 end if;
1321
1322 update IEM_ROUTE_CLASSIFICATIONS
1323 set
1324 description=decode(l_description,FND_API.G_MISS_CHAR,description,l_description),
1325 boolean_type_code=decode(l_ruling_chain,FND_API.G_MISS_CHAR,boolean_type_code,l_ruling_chain),
1326 procedure_name=decode(l_proc_name,FND_API.G_MISS_CHAR,procedure_name,l_proc_name),
1327 LAST_UPDATED_BY = decode(G_created_updated_by,null,-1,G_created_updated_by),
1328 LAST_UPDATE_DATE = sysdate,
1329 LAST_UPDATE_LOGIN = decode(G_LAST_UPDATE_LOGIN,null,-1,G_LAST_UPDATE_LOGIN)
1330 where route_classification_id=p_class_id;
1331
1332 -- Standard Check Of p_commit.
1333 IF FND_API.To_Boolean(p_commit) THEN
1334 COMMIT WORK;
1335 END IF;
1336
1337 -- Standard callto get message count and if count is 1, get message info.
1338
1339 FND_MSG_PUB.Count_And_Get
1340 ( p_count => x_msg_count,
1341
1342 p_data => x_msg_data
1343
1344 );
1345 EXCEPTION
1346 WHEN l_IEM_INVALID_PROCEDURE THEN
1347 ROLLBACK TO update_item_class;
1348 x_return_status := FND_API.G_RET_STS_ERROR ;
1349 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1350
1351 WHEN IEM_ADMIN_ROUTE_NO_PROC THEN
1352 ROLLBACK TO update_item_class;
1353 FND_MESSAGE.SET_NAME('IEM','IEM_ADMIN_ROUTE_NO_PROC');
1354 FND_MSG_PUB.Add;
1355 x_return_status := FND_API.G_RET_STS_ERROR ;
1356 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1357
1358 WHEN IEM_RT_CLASS_DUP_NAME THEN
1359 ROLLBACK TO update_item_class;
1360 FND_MESSAGE.SET_NAME('IEM','IEM_RT_CLASS_DUP_NAME');
1361 FND_MSG_PUB.Add;
1362 x_return_status := FND_API.G_RET_STS_ERROR ;
1363 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1364
1365
1366
1367 WHEN FND_API.G_EXC_ERROR THEN
1368 ROLLBACK TO update_item_class;
1369
1370 x_return_status := FND_API.G_RET_STS_ERROR ;
1371 FND_MSG_PUB.Count_And_Get
1372 ( p_count => x_msg_count,
1373 p_data => x_msg_data
1374 );
1375
1376 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1377 ROLLBACK TO update_item_class;
1378 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1379 FND_MSG_PUB.Count_And_Get
1380
1381 ( p_count => x_msg_count,
1382
1383 p_data => x_msg_data
1384
1385 );
1386
1387 WHEN OTHERS THEN
1388 ROLLBACK TO update_item_class;
1389 x_return_status := FND_API.G_RET_STS_ERROR;
1390 IF FND_MSG_PUB.Check_Msg_Level
1391 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1392 THEN
1393 FND_MSG_PUB.Add_Exc_Msg
1394
1395 ( G_PKG_NAME ,
1396 l_api_name
1397 );
1398
1399
1400 END IF;
1401 FND_MSG_PUB.Count_And_Get
1402 ( p_count => x_msg_count ,
1403 p_data => x_msg_data
1404 );
1405
1406 END update_item_class;
1407
1408
1409
1410 PROCEDURE update_item_rule (p_api_version_number IN NUMBER,
1411 p_init_msg_list IN VARCHAR2 := null,
1412 p_commit IN VARCHAR2 := null,
1413
1414 p_route_class_rule_id IN NUMBER ,
1415 p_key_type_code IN VARCHAR2:= null,
1416 p_operator_type_code IN VARCHAR2:=null,
1417 p_value IN VARCHAR2:=null,
1418
1419 x_return_status OUT NOCOPY VARCHAR2,
1420 x_msg_count OUT NOCOPY NUMBER,
1421 x_msg_data OUT NOCOPY VARCHAR2
1422 ) is
1423 l_api_name VARCHAR2(255):='update_item_rule';
1424 l_api_version_number NUMBER:=1.0;
1425
1426 l_rule NUMBER;
1427
1428 IEM_NO_RULE_UPDATE EXCEPTION;
1429 IEM_RULE_KEY_OP_VAL_NULL EXCEPTION;
1430 --IEM_INVALID_DATE_FORMAT EXCEPTION;
1431 BEGIN
1432 -- Standard Start of API savepoint
1433
1434 SAVEPOINT update_item_rule;
1435 -- Standard call to check for call compatibility.
1436 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
1437 p_api_version_number,
1438 l_api_name,
1439 G_PKG_NAME)
1440 THEN
1441
1442 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1443 END IF;
1444
1445
1446 -- Initialize message list if p_init_msg_list is set to TRUE.
1447
1448 IF FND_API.to_Boolean( p_init_msg_list )
1449 THEN
1450 FND_MSG_PUB.initialize;
1451 END IF;
1452 -- Initialize API return status to SUCCESS
1453 x_return_status := FND_API.G_RET_STS_SUCCESS;
1454
1455 -- check if the route_id exist in iem_routes
1456
1457 select count(*) into l_rule from iem_route_class_rules
1458 where route_class_rule_id = p_route_class_rule_id;
1459
1460 if l_rule < 1 then
1461 raise IEM_NO_RULE_UPDATE;
1462 end if;
1463
1464 /*
1465 -- translate display date format to canonical date
1466 if ( substrb(p_key_type_code, 4, 1) = 'D' )then
1467
1468 l_value := displayDT_to_canonical(p_value);
1469
1470
1471 if ( l_value is NULL ) then
1472 RAISE IEM_INVALID_DATE_FORMAT;
1473
1474 end if;
1475
1476 else
1477 l_value := p_value;
1478 end if;
1479 */
1480
1481 if ( p_key_type_code = FND_API.G_MISS_CHAR ) then
1482 raise IEM_RULE_KEY_OP_VAL_NULL;
1483
1484 elsif ( p_operator_type_code = FND_API.G_MISS_CHAR ) then
1485 raise IEM_RULE_KEY_OP_VAL_NULL;
1486
1487 elsif ( p_value = FND_API.G_MISS_CHAR ) then
1488 raise IEM_RULE_KEY_OP_VAL_NULL;
1489 end if;
1490
1491 update IEM_ROUTE_CLASS_RULES
1492 set
1493
1494 key_type_code=decode(p_key_type_code,null,key_type_code,p_key_type_code),
1495 operator_type_code=decode(p_operator_type_code,null,operator_type_code,p_operator_type_code),
1496 value=decode(p_value,null,value,p_value),
1497 LAST_UPDATED_BY = decode(G_created_updated_by,null,-1,G_created_updated_by),
1498 LAST_UPDATE_DATE = sysdate,
1499
1500 LAST_UPDATE_LOGIN = decode(G_LAST_UPDATE_LOGIN,null,-1,G_LAST_UPDATE_LOGIN)
1501 where route_class_rule_id=p_route_class_rule_id;
1502
1503
1504 -- Standard Check Of p_commit.
1505 IF FND_API.To_Boolean(p_commit) THEN
1506 COMMIT WORK;
1507
1508 END IF;
1509
1510 -- Standard callto get message count and if count is 1, get message info.
1511 FND_MSG_PUB.Count_And_Get
1512 ( p_count => x_msg_count,
1513 p_data => x_msg_data
1514 );
1515
1516 EXCEPTION
1517 WHEN IEM_RULE_KEY_OP_VAL_NULL THEN
1518 ROLLBACK TO update_item_rule;
1519 FND_MESSAGE.SET_NAME('IEM','IEM_RULE_KEY_OP_VAL_NULL');
1520 FND_MSG_PUB.Add;
1521 x_return_status := FND_API.G_RET_STS_ERROR ;
1522 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1523
1524 WHEN IEM_NO_RULE_UPDATE THEN
1525 ROLLBACK TO update_item_rule;
1526 FND_MESSAGE.SET_NAME('IEM','IEM_NO_RULE_UPDATE');
1527 FND_MSG_PUB.Add;
1528 x_return_status := FND_API.G_RET_STS_ERROR ;
1529 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1530 /*
1531 WHEN IEM_INVALID_DATE_FORMAT THEN
1532 ROLLBACK TO update_item_rule;
1533 FND_MESSAGE.SET_NAME('IEM','IEM_INVALID_DATE_FORMAT');
1534 FND_MSG_PUB.Add;
1535 x_return_status := FND_API.G_RET_STS_ERROR ;
1536
1537 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1538 */
1539 WHEN FND_API.G_EXC_ERROR THEN
1540 ROLLBACK TO update_item_rule;
1541 x_return_status := FND_API.G_RET_STS_ERROR ;
1542
1543
1544 FND_MSG_PUB.Count_And_Get
1545 ( p_count => x_msg_count,
1546 p_data => x_msg_data
1547 );
1548 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1549 ROLLBACK TO update_item_rule;
1550 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1551 FND_MSG_PUB.Count_And_Get
1552
1553 ( p_count => x_msg_count,
1554 p_data => x_msg_data
1555 );
1556
1557 WHEN OTHERS THEN
1558
1559 ROLLBACK TO update_item_rule;
1560 x_return_status := FND_API.G_RET_STS_ERROR;
1561 IF FND_MSG_PUB.Check_Msg_Level
1562 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1563 THEN
1564 FND_MSG_PUB.Add_Exc_Msg
1565 ( G_PKG_NAME ,
1566 l_api_name
1567 );
1568
1569 END IF;
1570
1571 FND_MSG_PUB.Count_And_Get
1572 ( p_count => x_msg_count ,
1573
1574 p_data => x_msg_data
1575 );
1576
1577 END update_item_rule;
1578
1579
1580
1581 PROCEDURE create_wrap_acct_rt_class (
1582 p_api_version_number IN NUMBER,
1583 p_init_msg_list IN VARCHAR2 := null,
1584 p_commit IN VARCHAR2 := null,
1585 p_email_account_id IN NUMBER,
1586
1587 p_class_id IN NUMBER,
1588 p_enabled_flag IN VARCHAR2,
1589 p_priority IN NUMBER,
1590
1591 x_return_status OUT NOCOPY VARCHAR2,
1592 x_msg_count OUT NOCOPY NUMBER,
1593 x_msg_data OUT NOCOPY VARCHAR2
1594 ) is
1595 l_api_name VARCHAR2(255):='create_item_account_routes';
1596
1597 l_api_version_number NUMBER:=1.0;
1598
1599
1600 l_class number;
1601
1602 l_account number;
1603
1604 l_return_status VARCHAR2(20) := FND_API.G_RET_STS_SUCCESS;
1605 l_msg_count NUMBER := 0;
1606 l_msg_data VARCHAR2(2000);
1607 l_class_name VARCHAR(30);
1608 l_class_name1 VARCHAR(31);
1609
1610
1611 IEM_RT_CLASS_NO_DATA EXCEPTION;
1612 IEM_ADMIN_ACCOUNT_NOT_EXIST EXCEPTION;
1613 IEM_RT_ClASS_ACCT_NOT_UPDATED EXCEPTION;
1614 IEM_RT_ClASS_FAIL_CREAT_FOLDER EXCEPTION;
1615
1616
1617 BEGIN
1618 -- Standard Start of API savepoint
1619 SAVEPOINT create_wrap_acct_rt_class_PVT;
1620
1621 -- Standard call to check for call compatibility.
1622 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
1623 p_api_version_number,
1624
1625 l_api_name,
1626 G_PKG_NAME)
1627 THEN
1628 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1629 END IF;
1630
1631
1632 -- Initialize message list if p_init_msg_list is set to TRUE.
1633 IF FND_API.to_Boolean( p_init_msg_list )
1634 THEN
1635 FND_MSG_PUB.initialize;
1636
1637 END IF;
1638
1639
1640 -- Initialize API return status to SUCCESS
1641 x_return_status := FND_API.G_RET_STS_SUCCESS;
1642
1643
1644 -- check if the route_id exist in iem_routes
1645 select count(*) into l_class from iem_route_classifications
1646
1647 where route_classification_id = p_class_id;
1648
1649 if l_class < 1 then
1650 raise IEM_RT_CLASS_NO_DATA;
1651 end if;
1652
1653
1654 -- ***** Changed from iem_email_accounts ==> iem_mstemail_accounts for 11i compliance *****
1655 -- check if the account_id exist in iem_mstemail_accounts
1656 -- removed the where clause "and active_flag='Y'" for bug fix of 4945889
1657 select count(*) into l_account from iem_mstemail_accounts
1658 where email_account_id = p_email_account_id and deleted_flag='N';
1659
1660 if l_account < 1 then
1661 raise IEM_ADMIN_ACCOUNT_NOT_EXIST;
1662
1663 end if;
1664
1665
1666 iem_route_class_pvt.create_item_account_class(
1667
1668 p_api_version_number =>p_api_version_number,
1669 p_init_msg_list => p_init_msg_list,
1670
1671 p_commit => FND_API.G_FALSE,
1672 p_class_id =>p_class_id,
1673 p_email_account_id =>p_email_account_id,
1674 p_enabled_flag => p_enabled_flag,
1675 p_priority => p_priority,
1676
1677
1678 x_return_status =>l_return_status,
1679 x_msg_count => l_msg_count,
1680
1681 x_msg_data => l_msg_data);
1682
1683 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
1684 raise IEM_RT_ClASS_ACCT_NOT_UPDATED;
1685 end if;
1686
1687 select name into l_class_name from iem_route_classifications where route_classification_id = p_class_id;
1688
1689 if ( l_class_name is null ) then
1690 l_class_name1 := null;
1691
1692 else
1693 l_class_name1 := '/'||l_class_name;
1694
1695 end if;
1696
1697 -- ***** Remove for 11i compliance *****
1698 --iem_route_class_pvt.create_folder(p_api_version_number =>p_api_version_number,
1699 -- p_init_msg_list => p_init_msg_list,
1700 -- p_commit => FND_API.G_FALSE,
1701 -- p_email_account_id =>p_email_account_id,
1702 -- p_classification_name => l_class_name1,
1703 -- x_return_status =>l_return_status,
1704 -- x_msg_count => l_msg_count,
1705 -- x_msg_data => l_msg_data);
1706 -- if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
1707
1708 -- raise IEM_RT_ClASS_FAIL_CREAT_FOLDER;
1709
1710 -- end if;
1711
1712 --dbms_output.put_line('after insert : ');
1713 -- Standard Check Of p_commit.
1714 IF FND_API.To_Boolean(p_commit) THEN
1715 COMMIT WORK;
1716 END IF;
1717 -- Standard callto get message count and if count is 1, get message info.
1718 FND_MSG_PUB.Count_And_Get
1719 ( p_count => x_msg_count,
1720 p_data => x_msg_data
1721 );
1722
1723
1724
1725
1726 EXCEPTION
1727 WHEN IEM_RT_ClASS_FAIL_CREAT_FOLDER THEN
1728 ROLLBACK TO create_wrap_acct_rt_class_PVT;
1729 --FND_MESSAGE.SET_NAME('IEM','IEM_RT_ClASS_FAIL_CREAT_FOLDER');
1730 --FND_MSG_PUB.Add;
1731 x_return_status := FND_API.G_RET_STS_ERROR ;
1732 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1733
1734 WHEN IEM_RT_CLASS_NO_DATA THEN
1735 ROLLBACK TO create_wrap_acct_rt_class_PVT;
1736 FND_MESSAGE.SET_NAME('IEM','IEM_RT_CLASS_NO_DATA');
1737
1738
1739 FND_MSG_PUB.Add;
1740 x_return_status := FND_API.G_RET_STS_ERROR ;
1741 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1742
1743 WHEN IEM_ADMIN_ACCOUNT_NOT_EXIST THEN
1744 ROLLBACK TO create_wrap_acct_rt_class_PVT;
1745 FND_MESSAGE.SET_NAME('IEM','IEM_ADMIN_ACCOUNT_NOT_EXIST');
1746 FND_MSG_PUB.Add;
1747
1748 x_return_status := FND_API.G_RET_STS_ERROR ;
1749 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1750
1751
1752 WHEN IEM_RT_ClASS_ACCT_NOT_UPDATED THEN
1753
1754 ROLLBACK TO create_wrap_acct_rt_class_PVT;
1755 FND_MESSAGE.SET_NAME('IEM','IEM_RT_ClASS_ACCT_NOT_UPDATED');
1756 FND_MSG_PUB.Add;
1757 x_return_status := FND_API.G_RET_STS_ERROR ;
1758 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1759
1760 WHEN FND_API.G_EXC_ERROR THEN
1761 ROLLBACK TO create_wrap_acct_rt_class_PVT;
1762 x_return_status := FND_API.G_RET_STS_ERROR ;
1763
1764 FND_MSG_PUB.Count_And_Get
1765
1766 ( p_count => x_msg_count,
1767 p_data => x_msg_data
1768
1769 );
1770
1771 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1772 ROLLBACK TO create_wrap_acct_rt_class_PVT;
1773 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1774 FND_MSG_PUB.Count_And_Get
1775 ( p_count => x_msg_count,
1776 p_data => x_msg_data
1777 );
1778
1779
1780
1781 WHEN OTHERS THEN
1782 ROLLBACK TO create_wrap_acct_rt_class_PVT;
1783
1784 x_return_status := FND_API.G_RET_STS_ERROR;
1785 IF FND_MSG_PUB.Check_Msg_Level
1786 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1787 THEN
1788 FND_MSG_PUB.Add_Exc_Msg
1789 ( G_PKG_NAME,
1790 l_api_name
1791 );
1792 END IF;
1793
1794 FND_MSG_PUB.Count_And_Get
1795 ( p_count => x_msg_count,
1796
1797 p_data => x_msg_data
1798
1799 );
1800
1801 END create_wrap_acct_rt_class;
1802
1803
1804
1805 PROCEDURE create_item_account_class (
1806 p_api_version_number IN NUMBER,
1807
1808 p_init_msg_list IN VARCHAR2 := NULL,
1809 p_commit IN VARCHAR2 := NULL,
1810 p_email_account_id IN NUMBER,
1811 p_class_id IN NUMBER,
1812
1813
1814 p_enabled_flag IN VARCHAR2,
1815 p_priority IN NUMBER,
1816 x_return_status OUT NOCOPY VARCHAR2,
1817 x_msg_count OUT NOCOPY NUMBER,
1818 x_msg_data OUT NOCOPY VARCHAR2
1819 ) is
1820 l_api_name VARCHAR2(255):='create_item_account_class';
1821
1822 l_api_version_number NUMBER:=1.0;
1823 l_seq_id number;
1824
1825 BEGIN
1826 -- Standard Start of API savepoint
1827 SAVEPOINT create_item_acct_class_PVT;
1828
1829
1830
1831 -- Standard call to check for call compatibility.
1832 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
1833 p_api_version_number,
1834 l_api_name,
1835
1836 G_PKG_NAME)
1837 THEN
1838 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1839 END IF;
1840
1841 -- Initialize message list if p_init_msg_list is set to TRUE.
1842 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1843
1844 FND_MSG_PUB.initialize;
1845
1846 END IF;
1847
1848 -- Initialize API return status to SUCCESS
1849
1850 x_return_status := FND_API.G_RET_STS_SUCCESS;
1851
1852 --actual API begins here
1853 SELECT IEM_ACCOUNT_ROUTE_CLASS_s1.nextval
1854 INTO l_seq_id
1855 FROM dual;
1856
1857 INSERT INTO IEM_ACCOUNT_ROUTE_CLASS
1858
1859 (
1860 ROUTE_CLASSIFICATION_ID,
1861
1862 EMAIL_ACCOUNT_ID,
1863
1864 ACCOUNT_ROUTE_CLASS_ID,
1865 ENABLED_FLAG,
1866 PRIORITY,
1867 ATTRIBUTE1,
1868 ATTRIBUTE2,
1869 ATTRIBUTE3,
1870 ATTRIBUTE4,
1871 ATTRIBUTE5,
1872 ATTRIBUTE6,
1873
1874 ATTRIBUTE7,
1875 ATTRIBUTE8,
1876 ATTRIBUTE9,
1877
1878
1879 ATTRIBUTE10,
1880 ATTRIBUTE11,
1881 ATTRIBUTE12,
1882 ATTRIBUTE13,
1883 ATTRIBUTE14,
1884 ATTRIBUTE15,
1885 ATTRIBUTE_CATEGORY,
1886 CREATED_BY,
1887 CREATION_DATE,
1888
1889 LAST_UPDATED_BY,
1890 LAST_UPDATE_DATE,
1891
1892 LAST_UPDATE_LOGIN
1893
1894 )
1895 VALUES
1896 (
1897 p_class_id,
1898 p_email_account_id,
1899 l_seq_id,
1900 p_enabled_flag,
1901 p_priority,
1902
1903 NULL,
1904
1905 NULL,
1906 NULL,
1907 NULL,
1908 NULL,
1909
1910 NULL,
1911 NULL,
1912 NULL,
1913 NULL,
1914 NULL,
1915 NULL,
1916 NULL,
1917
1918
1919 NULL,
1920 NULL,
1921 NULL,
1922 NULL,
1923 decode(G_created_updated_by,null,-1,G_created_updated_by),
1924 sysdate,
1925
1926 decode(G_created_updated_by,null,-1,G_created_updated_by),
1927 sysdate,
1928 decode(G_LAST_UPDATE_LOGIN,null,-1,G_LAST_UPDATE_LOGIN)
1929 );
1930
1931
1932
1933 -- Standard Check Of p_commit.
1934 IF FND_API.To_Boolean(p_commit) THEN
1935 COMMIT WORK;
1936 END IF;
1937
1938 -- Standard callto get message count and if count is 1, get message info.
1939 FND_MSG_PUB.Count_And_Get
1940
1941 ( p_count => x_msg_count,
1942 p_data => x_msg_data
1943 );
1944
1945
1946 EXCEPTION
1947
1948 WHEN FND_API.G_EXC_ERROR THEN
1949 ROLLBACK TO create_item_acct_class_PVT;
1950 x_return_status := FND_API.G_RET_STS_ERROR ;
1951 FND_MSG_PUB.Count_And_Get
1952 ( p_count => x_msg_count,
1953 p_data => x_msg_data
1954 );
1955
1956
1957 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1958 ROLLBACK TO create_item_acct_class_PVT;
1959
1960 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1961 FND_MSG_PUB.Count_And_Get
1962
1963 ( p_count => x_msg_count,
1964 p_data => x_msg_data
1965 );
1966
1967 WHEN OTHERS THEN
1968 ROLLBACK TO create_item_acct_class_PVT;
1969 x_return_status := FND_API.G_RET_STS_ERROR;
1970 IF FND_MSG_PUB.Check_Msg_Level
1971 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1972
1973
1974 THEN
1975 FND_MSG_PUB.Add_Exc_Msg
1976 ( G_PKG_NAME ,
1977
1978 l_api_name
1979 );
1980 END IF;
1981 FND_MSG_PUB.Count_And_Get
1982 ( p_count => x_msg_count ,
1983 p_data => x_msg_data
1984 );
1985
1986 END create_item_account_class;
1987
1988
1989
1990
1991 -- to update and delete new tuples in iem_account_routes
1992
1993 PROCEDURE update_wrap_account_class (p_api_version_number IN NUMBER,
1994 p_init_msg_list IN VARCHAR2 := null,
1995 p_commit IN VARCHAR2 := null,
1996
1997 p_email_account_id IN NUMBER,
1998 p_class_ids_tbl IN jtf_varchar2_Table_100,
1999 p_upd_enable_flag_tbl IN jtf_varchar2_Table_100,
2000 --p_upd_priority_tbl IN jtf_varchar2_Table_100,
2001
2002 p_delete_class_ids_tbl IN jtf_varchar2_Table_100,
2003
2004 x_return_status OUT NOCOPY VARCHAR2,
2005 x_msg_count OUT NOCOPY NUMBER,
2006 x_msg_data OUT NOCOPY VARCHAR2
2007
2008 ) is
2009 l_api_name VARCHAR2(255):='update_wrap_account_class';
2010 l_api_version_number NUMBER:=1.0;
2011
2012 l_return_status VARCHAR2(20) := FND_API.G_RET_STS_SUCCESS;
2013 l_msg_count NUMBER := 0;
2014 l_msg_data VARCHAR2(2000);
2015
2016 MY_EXCEPTION EXCEPTION;
2017
2018 IEM_ACCOUNT_CLASS_NOT_DELETED EXCEPTION;
2019 IEM_RT_CLASS_ACCT_NOT_UPDATE EXCEPTION;
2020 BEGIN
2021 -- Standard Start of API savepoint
2022 SAVEPOINT update_wrap_acct_class_1_PVT;
2023 -- Standard call to check for call compatibility.
2024
2025 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
2026 p_api_version_number,
2027 l_api_name,
2028 G_PKG_NAME)
2029 THEN
2030 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2031 END IF;
2032
2033
2034
2035 -- Initialize message list if p_init_msg_list is set to TRUE.
2036 IF FND_API.to_Boolean( p_init_msg_list )
2037 THEN
2038
2039 FND_MSG_PUB.initialize;
2040 END IF;
2041 -- Initialize API return status to SUCCESS
2042 x_return_status := FND_API.G_RET_STS_SUCCESS;
2043
2044 -- update first
2045 if ( p_class_ids_tbl.count <> 0 ) then
2046 FOR i IN p_class_ids_tbl.FIRST..p_class_ids_tbl.LAST LOOP
2047 iem_route_class_pvt.update_account_class (p_api_version_number =>p_api_version_number,
2048 p_init_msg_list => p_init_msg_list,
2049
2050 p_commit => FND_API.G_TRUE,
2051
2052 p_class_id => p_class_ids_tbl(i),
2053 p_email_account_id => p_email_account_id,
2054 p_enabled_flag => p_upd_enable_flag_tbl(i),
2055 --p_priority => p_upd_priority_tbl(i),
2056
2057 x_return_status =>l_return_status,
2058 x_msg_count => l_msg_count,
2059
2060 x_msg_data => l_msg_data);
2061 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
2062 raise IEM_RT_CLASS_ACCT_NOT_UPDATE;
2063
2064 end if;
2065 END LOOP;
2066 end if;
2067
2068 --SAVEPOINT update_wrap_acct_class_2_PVT;
2069
2070 if ( p_delete_class_ids_tbl.count <> 0 ) then
2071 iem_route_class_pvt.delete_acct_class_batch
2072 (p_api_version_number => p_api_version_number,
2073 P_init_msg_list => p_init_msg_list,
2074
2075 p_commit => FND_API.G_TRUE,
2076 p_class_ids_tbl => p_delete_class_ids_tbl,
2077
2078 p_account_id => p_email_account_id,
2079 x_return_status => l_return_status,
2080 x_msg_count => l_msg_count,
2081 x_msg_data => l_msg_data) ;
2082 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
2083 raise MY_EXCEPTION;
2084 end if;
2085 end if;
2086
2087 -- Standard Check Of p_commit.
2088 IF FND_API.To_Boolean(p_commit) THEN
2089
2090 COMMIT WORK;
2091
2092 END IF;
2093 -- Standard callto get message count and if count is 1, get message info.
2094 FND_MSG_PUB.Count_And_Get
2095 ( p_count => x_msg_count,
2096 p_data => x_msg_data
2097 );
2098
2099 EXCEPTION
2100 WHEN MY_EXCEPTION THEN
2101 IF FND_API.To_Boolean(p_commit) THEN
2102 COMMIT WORK;
2103 END IF;
2104
2105 --ROLLBACK TO update_wrap_acct_class_2_PVT;
2106 --FND_MESSAGE.SET_NAME('IEM','MY_EXCEPTION');
2107 --FND_MSG_PUB.Add;
2108 x_return_status := FND_API.G_RET_STS_ERROR ;
2109 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
2110 WHEN IEM_RT_CLASS_ACCT_NOT_UPDATE THEN
2111 ROLLBACK TO update_wrap_acct_class_1_PVT;
2112 FND_MESSAGE.SET_NAME('IEM','IEM_RT_CLASS_ACCT_NOT_UPDATE');
2113 FND_MSG_PUB.Add;
2114 x_return_status := FND_API.G_RET_STS_ERROR ;
2115 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
2116 WHEN IEM_ACCOUNT_CLASS_NOT_DELETED THEN
2117 ROLLBACK TO update_wrap_acct_class_2_PVT;
2118
2119
2120 FND_MESSAGE.SET_NAME('IEM','IEM_ACCOUNT_CLASS_NOT_DELETED');
2121 FND_MSG_PUB.Add;
2122 x_return_status := FND_API.G_RET_STS_ERROR ;
2123 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
2124 WHEN FND_API.G_EXC_ERROR THEN
2125 ROLLBACK TO update_wrap_acct_class_1_PVT;
2126 x_return_status := FND_API.G_RET_STS_ERROR ;
2127
2128 FND_MSG_PUB.Count_And_Get
2129 ( p_count => x_msg_count,
2130 p_data => x_msg_data
2131 );
2132
2133
2134
2135 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2136 ROLLBACK TO update_wrap_acct_class_1_PVT;
2137 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2138 FND_MSG_PUB.Count_And_Get
2139 ( p_count => x_msg_count,
2140 p_data => x_msg_data
2141 );
2142
2143
2144 WHEN OTHERS THEN
2145 ROLLBACK TO update_wrap_acct_class_1_PVT;
2146
2147 x_return_status := FND_API.G_RET_STS_ERROR;
2148 IF FND_MSG_PUB.Check_Msg_Level
2149
2150 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2151 THEN
2152 FND_MSG_PUB.Add_Exc_Msg
2153 ( G_PKG_NAME,
2154 l_api_name
2155 );
2156 END IF;
2157 FND_MSG_PUB.Count_And_Get
2158 ( p_count => x_msg_count,
2159
2160
2161 p_data => x_msg_data
2162 );
2163
2164
2165 END update_wrap_account_class;
2166
2167
2168 PROCEDURE update_account_class(p_api_version_number IN NUMBER,
2169 p_init_msg_list IN VARCHAR2 := null,
2170 p_commit IN VARCHAR2 := null,
2171 p_class_id IN NUMBER ,
2172 p_email_account_id IN NUMBER,
2173 p_enabled_flag IN VARCHAR2:= null,
2174 p_priority IN VARCHAR2:= null,
2175 x_return_status OUT NOCOPY VARCHAR2,
2176 x_msg_count OUT NOCOPY NUMBER,
2177 x_msg_data OUT NOCOPY VARCHAR2
2178 ) is
2179 l_api_name VARCHAR2(255):='update_account_class';
2180 l_api_version_number NUMBER:=1.0;
2181 l_class_cnt NUMBER;
2182 l_acct_cnt NUMBER;
2183
2184 l_LAST_UPDATED_BY NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('USER_ID')) ;
2185 l_LAST_UPDATE_DATE DATE:=SYSDATE;
2186 l_LAST_UPDATE_LOGIN NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('LOGIN_ ID')) ;
2187
2188 IEM_RT_CLASS_NO_DATA EXCEPTION;
2189
2190 IEM_ADMIN_ACCOUNT_NOT_EXIST EXCEPTION;
2191
2192 BEGIN
2193 -- Standard Start of API savepoint
2194 SAVEPOINT update_account_class_PVT;
2195 -- Standard call to check for call compatibility.
2196 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
2197
2198 p_api_version_number,
2199 l_api_name,
2200 G_PKG_NAME)
2201 THEN
2202 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2203 END IF;
2204
2205
2206 -- Initialize message list if p_init_msg_list is set to TRUE.
2207 IF FND_API.to_Boolean( p_init_msg_list )
2208 THEN
2209 FND_MSG_PUB.initialize;
2210 END IF;
2211
2212 -- Initialize API return status to SUCCESS
2213 x_return_status := FND_API.G_RET_STS_SUCCESS;
2214
2215 -- Check For Existing IEM Server Group
2216 IF p_class_id <> FND_API.G_MISS_NUM THEN
2217 Select count(*) into l_class_cnt from iem_route_classifications
2218 where route_classification_id=p_class_id;
2219
2220 IF l_class_cnt = 0 then
2221 raise IEM_RT_CLASS_NO_DATA;
2222 END IF;
2223 END IF;
2224
2225
2226 IF p_email_account_id <> FND_API.G_MISS_NUM THEN
2227
2228 -- removed the where clause "and active_flag='Y'" for bug fix of 4945889
2229
2230 Select count(*) into l_acct_cnt from iem_mstemail_accounts
2231 where email_account_id=p_email_account_id and deleted_flag='N' ;
2232
2233 IF l_acct_cnt = 0 then
2234 raise IEM_ADMIN_ACCOUNT_NOT_EXIST;
2235
2236 END IF;
2237 END IF;
2238
2239 if ((p_email_account_id <> FND_API.G_MISS_NUM) and (p_class_id <> FND_API.G_MISS_NUM)) then
2240
2241 update IEM_ACCOUNT_ROUTE_CLASS
2242 set
2243 enabled_flag=decode(p_enabled_flag,FND_API.G_MISS_CHAR,enabled_flag,p_enabled_flag),
2244 priority=decode(p_priority,null,priority,p_priority),
2245 LAST_UPDATED_BY = decode(G_created_updated_by,null,-1,G_created_updated_by),
2246 LAST_UPDATE_DATE = sysdate,
2247 LAST_UPDATE_LOGIN = decode(G_LAST_UPDATE_LOGIN,null,-1,G_LAST_UPDATE_LOGIN)
2248 where route_classification_id = p_class_id and email_account_id = p_email_account_id;
2249
2250
2251 end if;
2252
2253
2254
2255
2256 -- Standard Check Of p_commit.
2257 IF FND_API.To_Boolean(p_commit) THEN
2258 COMMIT WORK;
2259 END IF;
2260 -- Standard callto get message count and if count is 1, get message info.
2261 FND_MSG_PUB.Count_And_Get
2262 ( p_count => x_msg_count,
2263 p_data => x_msg_data
2264 );
2265
2266
2267 EXCEPTION
2268
2269 WHEN IEM_RT_CLASS_NO_DATA THEN
2270 ROLLBACK TO update_account_class_PVT;
2271
2272 FND_MESSAGE.SET_NAME('IEM','IEM_RT_CLASS_NO_DATA');
2273 FND_MSG_PUB.Add;
2274 x_return_status := FND_API.G_RET_STS_ERROR ;
2275 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
2276 WHEN IEM_ADMIN_ACCOUNT_NOT_EXIST THEN
2277 ROLLBACK TO update_account_class_PVT;
2278 FND_MESSAGE.SET_NAME('IEM','IEM_ADMIN_ACCOUNT_NOT_EXIST');
2279 FND_MSG_PUB.Add;
2280
2281 x_return_status := FND_API.G_RET_STS_ERROR ;
2282
2283 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
2284
2285 WHEN FND_API.G_EXC_ERROR THEN
2286 ROLLBACK TO update_account_class_PVT;
2287 x_return_status := FND_API.G_RET_STS_ERROR ;
2288 FND_MSG_PUB.Count_And_Get
2289 ( p_count => x_msg_count,
2290 p_data => x_msg_data
2291 );
2292 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2293 ROLLBACK TO update_account_class_PVT;
2294 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2295
2296
2297 FND_MSG_PUB.Count_And_Get
2298
2299 ( p_count => x_msg_count,
2300 p_data => x_msg_data
2301 );
2302 WHEN OTHERS THEN
2303 ROLLBACK TO update_account_class_PVT;
2304 x_return_status := FND_API.G_RET_STS_ERROR;
2305 IF FND_MSG_PUB.Check_Msg_Level
2306 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2307 THEN
2308 FND_MSG_PUB.Add_Exc_Msg
2309 ( G_PKG_NAME ,
2310
2311
2312 l_api_name
2313 );
2314
2315 END IF;
2316 FND_MSG_PUB.Count_And_Get
2317 ( p_count => x_msg_count,
2318 p_data => x_msg_data
2319 );
2320
2321 END update_account_class;
2322
2323 PROCEDURE delete_acct_class_batch
2324 (p_api_version_number IN NUMBER,
2325
2326 P_init_msg_list IN VARCHAR2 := null,
2327 p_commit IN VARCHAR2 := null,
2328 p_class_ids_tbl IN jtf_varchar2_Table_100,
2329
2330 p_account_id IN NUMBER,
2331 x_return_status OUT NOCOPY VARCHAR2,
2332 x_msg_count OUT NOCOPY NUMBER,
2333 x_msg_data OUT NOCOPY VARCHAR2)
2334 IS
2335 i INTEGER;
2336 l_api_name varchar2(30):='delete_acct_class_batch';
2337
2338 l_api_version_number number:=1.0;
2339 l_return_status varchar2(30);
2340
2341 l_undeleted_class_name varchar2(2000);
2342 l_undeleted_class_name_1 varchar2(30);
2343
2344 l_count_msg_postmdt number := 0;
2345
2346
2347 IEM_ACCOUNT_CLASS_NOT_DELETED EXCEPTION;
2348 BEGIN
2349
2350 --Standard Savepoint
2351 SAVEPOINT delete_acct_class_batch;
2352
2353 -- Standard call to check for call compatibility.
2354
2355 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
2356 p_api_version_number,
2357 l_api_name,
2358
2359 G_PKG_NAME)
2360 THEN
2361 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2362 END IF;
2363
2364 --Initialize the message list if p_init_msg_list is set to TRUE
2365 If FND_API.to_Boolean(p_init_msg_list) THEN
2366
2367 FND_MSG_PUB.initialize;
2368
2369 END IF;
2370
2371 --Initialize API status return
2372 x_return_status := FND_API.G_RET_STS_SUCCESS;
2373
2374 --Actual API starts here
2375 if ( p_class_ids_tbl.count <> 0 ) then
2376
2377 FOR i IN p_class_ids_tbl.FIRST..p_class_ids_tbl.LAST LOOP
2378 --pkesani--
2379 -- select count(*) into l_count_msg_postmdt from iem_post_mdts where rt_classification_id=p_class_ids_tbl(i) and email_account_id = p_account_id;
2380 select count(*) into l_count_msg_postmdt from iem_rt_proc_emails where rt_classification_id=p_class_ids_tbl(i) and email_account_id = p_account_id;
2381
2382 if l_count_msg_postmdt <> 0 then
2383 select name into l_undeleted_class_name_1 from iem_route_classifications where route_classification_id=p_class_ids_tbl(i);
2384 l_undeleted_class_name := l_undeleted_class_name||l_undeleted_class_name_1||', ';
2385 else
2386
2387 -- ***** Removed for 11i compliance *****
2388 --delete the classification folder in OES first
2389 --iem_route_class_pvt.delete_folder(p_api_version_number =>p_api_version_number,
2390 -- p_init_msg_list => p_init_msg_list,
2391 -- p_commit => FND_API.G_FALSE,
2392 -- p_email_account_id =>p_account_id,
2393 -- p_class_id => p_class_ids_tbl(i),
2394 -- x_return_status =>l_return_status,
2395 -- x_msg_count => x_msg_count,
2396 -- x_msg_data => x_msg_data);
2397
2398 --if ( l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) then
2399 -- raise FND_API.G_EXC_UNEXPECTED_ERROR;
2400 --elsif (l_return_status = FND_API.G_RET_STS_ERROR) then
2401 -- select name into l_undeleted_class_name_1 from iem_route_classifications where route_classification_id=p_class_ids_tbl(i);
2402 -- l_undeleted_class_name := l_undeleted_class_name||l_undeleted_class_name_1||', ';
2403 --else
2404
2405 -- update priority before delete an account_classification association
2406 Update iem_account_route_class set priority=priority-1
2407 where email_account_id = p_account_id and priority > (Select priority from iem_account_route_class
2408 where route_classification_id=p_class_ids_tbl(i) and email_account_id=p_account_id);
2409
2410 DELETE
2411 FROM IEM_ACCOUNT_ROUTE_CLASS
2412 WHERE route_classification_id = p_class_ids_tbl(i) and email_account_id = p_account_id;
2413
2414 if SQL%NOTFOUND then
2415 raise IEM_ACCOUNT_CLASS_NOT_DELETED;
2416 end if;
2417
2418 --end if;
2419
2420 end if;
2421
2422 END LOOP;
2423 end if;
2424
2425
2426 --add names of un_deleted classifications into message
2427 if l_undeleted_class_name is not null then
2428 l_undeleted_class_name := RTRIM(l_undeleted_class_name, ', ');
2429 x_return_status := FND_API.G_RET_STS_ERROR;
2430 FND_MESSAGE.SET_NAME('IEM', 'IEM_RT_CLASS_FAILED_DEL_FOLDER');
2431
2432 FND_MESSAGE.SET_TOKEN('CLASSIFICATION', l_undeleted_class_name);
2433 FND_MSG_PUB.ADD;
2434 end if;
2435
2436 --Standard check of p_commit
2437 IF FND_API.to_Boolean(p_commit) THEN
2438 COMMIT WORK;
2439 END IF;
2440
2441
2442 FND_MSG_PUB.Count_And_Get
2443 ( p_count => x_msg_count,
2444 p_data => x_msg_data
2445
2446 );
2447
2448 EXCEPTION
2449
2450 WHEN IEM_ACCOUNT_CLASS_NOT_DELETED THEN
2451 ROLLBACK TO delete_acct_class_batch;
2452 x_return_status := FND_API.G_RET_STS_ERROR;
2453 FND_MESSAGE.SET_NAME('IEM', 'IEM_ACCOUNT_CLASS_NOT_DELETED');
2454 FND_MSG_PUB.ADD;
2455 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
2456
2457 WHEN FND_API.G_EXC_ERROR THEN
2458
2459 ROLLBACK TO delete_acct_class_batch;
2460 x_return_status := FND_API.G_RET_STS_ERROR ;
2461 FND_MSG_PUB.Count_And_Get
2462 ( p_count => x_msg_count,p_data => x_msg_data);
2463
2464 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2465 ROLLBACK TO delete_acct_class_batch;
2466 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2467 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,p_data => x_msg_data);
2468
2469
2470 WHEN OTHERS THEN
2471 ROLLBACK TO delete_acct_class_batch;
2472
2473 x_return_status := FND_API.G_RET_STS_ERROR;
2474 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2475 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME , l_api_name);
2476 END IF;
2477
2478 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count ,p_data => x_msg_data);
2479
2480 END delete_acct_class_batch;
2481
2482 -- ***** Remove for 11i compliance *****
2483 /*
2484 PROCEDURE delete_folder_on_classId
2485 (p_api_version_number IN NUMBER,
2486 P_init_msg_list IN VARCHAR2 := null,
2487 p_commit IN VARCHAR2 := null,
2488 p_classification_id IN NUMBER,
2489 x_return_status OUT NOCOPY VARCHAR2,
2490 x_msg_count OUT NOCOPY NUMBER,
2491 x_msg_data OUT NOCOPY VARCHAR2)
2492 IS
2493 l_api_name VARCHAR2(255):='delete_folder_on_classId';
2494 l_api_version_number NUMBER:=1.0;
2495 l_seq_id NUMBER;
2496 l_return_status varchar2(30);
2497
2498
2499
2500 CURSOR acct_id_cursor( l_classification_id IN NUMBER ) IS
2501 select unique email_account_id from iem_account_route_class where route_classification_id = l_classification_id;
2502
2503 MY_EXCP_MSG_IN_FOLDER EXCEPTION;
2504 IEM_UNEXPT_ERR_DELETE_FOLDER EXCEPTION;
2505
2506 BEGIN
2507 --Standard Savepoint
2508 SAVEPOINT delete_folder_on_classId;
2509 -- Standard call to check for call compatibility.
2510 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
2511 p_api_version_number,
2512 l_api_name,
2513
2514
2515 G_PKG_NAME)
2516 THEN
2517 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2518 END IF;
2519
2520 --Initialize the message list if p_init_msg_list is set to TRUE
2521 If FND_API.to_Boolean(p_init_msg_list) THEN
2522
2523 FND_MSG_PUB.initialize;
2524 END IF;
2525
2526 --Initialize API status return
2527
2528 x_return_status := FND_API.G_RET_STS_SUCCESS;
2529
2530
2531 --actuall API starts here
2532 FOR acct_id IN acct_id_cursor( p_classification_id ) LOOP
2533 -- ***** Remove for 11i compliance ***
2534 --iem_route_class_pvt.delete_folder(p_api_version_number =>p_api_version_number,
2535 -- p_init_msg_list => p_init_msg_list,
2536 -- p_commit => FND_API.G_FALSE,
2537 -- p_email_account_id =>acct_id.email_account_id,
2538 -- p_class_id => p_classification_id,
2539 -- x_return_status =>l_return_status,
2540 -- x_msg_count => x_msg_count,
2541 -- x_msg_data => x_msg_data);
2542
2543
2544 --if (l_return_status = FND_API.G_RET_STS_ERROR) then
2545
2546 -- RAISE MY_EXCP_MSG_IN_FOLDER;
2547 --elsif (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) then
2548 -- RAISE IEM_UNEXPT_ERR_DELETE_FOLDER ;
2549 --end if;
2550 END LOOP;
2551
2552 --Standard check of p_commit
2553 IF FND_API.to_Boolean(p_commit) THEN
2554 COMMIT WORK;
2555 END IF;
2556
2557
2558 FND_MSG_PUB.Count_And_Get
2559 ( p_count => x_msg_count,
2560
2561 p_data => x_msg_data
2562 );
2563
2564 EXCEPTION
2565 WHEN MY_EXCP_MSG_IN_FOLDER THEN
2566 ROLLBACK TO delete_folder_on_classId;
2567 x_return_status := FND_API.G_RET_STS_ERROR;
2568
2569 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
2570
2571
2572 WHEN IEM_UNEXPT_ERR_DELETE_FOLDER THEN
2573 ROLLBACK TO delete_folder_on_classId;
2574 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2575
2576 --FND_MESSAGE.SET_NAME('IEM', 'IEM_UNEXPT_ERR_DELETE_FOLDER');
2577
2578 --FND_MSG_PUB.ADD;
2579 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
2580
2581 WHEN FND_API.G_EXC_ERROR THEN
2582 ROLLBACK TO delete_folder_on_classId;
2583 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2584 FND_MSG_PUB.Count_And_Get
2585 ( p_count => x_msg_count,p_data => x_msg_data);
2586
2587
2588 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2589 ROLLBACK TO delete_folder_on_classId;
2590 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2591 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,p_data => x_msg_data);
2592
2593
2594 WHEN OTHERS THEN
2595 ROLLBACK TO delete_folder_on_classId;
2596 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2597 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2598 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME , l_api_name);
2599 END IF;
2600
2601
2602 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count ,p_data => x_msg_data);
2603
2604 END delete_folder_on_classId;
2605
2606 */
2607 -- ***** Remove for 11i compliance *****
2608 /*
2609 PROCEDURE create_folder (
2610 p_api_version_number IN NUMBER,
2611 p_init_msg_list IN VARCHAR2 := null,
2612 p_commit IN VARCHAR2 := null,
2613 p_email_account_id IN NUMBER,
2614 p_classification_name IN VARCHAR2,
2615
2616 x_return_status OUT NOCOPY VARCHAR2,
2617 x_msg_count OUT NOCOPY NUMBER,
2618 x_msg_data OUT NOCOPY VARCHAR2
2619 ) is
2620 l_api_name VARCHAR2(255):='create_folder';
2621 l_api_version_number NUMBER:=1.0;
2622 l_seq_id NUMBER;
2623
2624
2625 TYPE email_acct_Rec IS RECORD (
2626 email_user iem_email_accounts.email_user%TYPE,
2627 domain iem_email_accounts.domain%TYPE,
2628 email_password iem_email_accounts.email_password%TYPE);
2629
2630
2631 l_email_acct_Rec email_acct_Rec;
2632
2633 l_db_server_id NUMBER;
2634
2635 l_stat varchar2(10);
2636 l_str varchar2(200);
2637 l_ret number;
2638
2639 l_count NUMBER;
2640 l_data varchar2(255);
2641 l_im_link varchar2(200);
2642 l_db_link varchar2(100);
2643
2644 l_im_link1 varchar2(200);
2645 l_folder varchar2(50);
2646 IEM_ADMIN_ACCOUNT_NOT_EXIST EXCEPTION;
2647 IEM_DB_LINK_NOT_AVAILABLE EXCEPTION;
2648 tns_no_listener EXCEPTION;
2649 looking_up_object EXCEPTION;
2650 PRAGMA EXCEPTION_INIT(tns_no_listener, -12541);
2651 PRAGMA EXCEPTION_INIT(looking_up_object, -04052);
2652
2653 BEGIN
2654 -- Standard Start of API savepoint
2655 SAVEPOINT create_folder_PVT;
2656
2657
2658 -- Standard call to check for call compatibility.
2659
2660 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
2661 p_api_version_number,
2662 l_api_name,
2663
2664 G_PKG_NAME)
2665 THEN
2666 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2667 END IF;
2668
2669 -- Initialize message list if p_init_msg_list is set to TRUE.
2670 IF FND_API.to_Boolean( p_init_msg_list )
2671 THEN
2672 FND_MSG_PUB.initialize;
2673
2674
2675 END IF;
2676
2677
2678 -- Initialize API return status to SUCCESS
2679 x_return_status := FND_API.G_RET_STS_SUCCESS;
2680
2681 --begins here
2682 select DB_SERVER_ID
2683 INTO l_db_server_id
2684 FROM IEM_EMAIL_ACCOUNTS
2685 WHERE email_account_id = p_email_account_id;
2686
2687 IEM_DB_CONNECTIONS_PVT.select_item(
2688 p_api_version_number => 1.0,
2689
2690 p_db_server_id => l_db_server_id,
2691
2692 p_is_admin => 'P',
2693 x_db_link => l_im_link1,
2694 x_return_status => l_stat,
2695 x_msg_count => l_count,
2696 x_msg_data => l_data );
2697
2698 if (l_stat <> FND_API.G_RET_STS_SUCCESS) then
2699
2700 RAISE IEM_DB_LINK_NOT_AVAILABLE;
2701 end if;
2702
2703 select email_user, domain, email_password into l_email_acct_Rec from iem_email_accounts
2704 where email_account_id = p_email_account_id;
2705
2706 if SQL%NOTFOUND then
2707 raise IEM_ADMIN_ACCOUNT_NOT_EXIST;
2708 end if;
2709
2710
2711
2712 IF l_im_link1 is null then
2713 l_im_link := null;
2714 ELSE
2715 l_im_link := '@'||l_im_link1;
2716 END IF;
2717
2718 l_str := 'begin :l_ret:=im_api.authenticate'||l_im_link||'(:a_user, :a_domain, :a_password); end;';
2719 EXECUTE IMMEDIATE l_str using OUT l_ret,l_email_acct_Rec.email_user, l_email_acct_Rec.domain, l_email_acct_Rec.email_password;
2720
2721 IF l_ret=0 THEN
2722 x_return_status := FND_API.G_RET_STS_SUCCESS;
2723 ELSE
2724
2725
2726 x_return_status := FND_API.G_RET_STS_ERROR;
2727 END IF;
2728
2729 --now create the folder named by classification name
2730 l_folder := p_classification_name;
2731 --dbms_output.put_line('++the p_classification_name is '||l_folder);
2732
2733 l_str := 'begin :l_ret:=im_api.createfolder'||l_im_link||'(:a_folder);end;';
2734 EXECUTE IMMEDIATE l_str using OUT l_ret, l_folder;
2735 IF l_ret=0 THEN
2736 x_return_status := FND_API.G_RET_STS_SUCCESS;
2737 ELSE
2738 x_return_status := FND_API.G_RET_STS_ERROR;
2739 END IF;
2740
2741
2742
2743 --Standard check of p_commit
2744 IF FND_API.to_Boolean(p_commit) THEN
2745 COMMIT WORK;
2746 END IF;
2747 FND_MSG_PUB.Count_And_Get
2748 ( p_count => x_msg_count,
2749 p_data => x_msg_data
2750 );
2751 EXCEPTION
2752 WHEN IEM_DB_LINK_NOT_AVAILABLE THEN
2753 ROLLBACK TO create_folder_PVT;
2754 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2755
2756 --FND_MESSAGE.SET_NAME('IEM', 'IEM_DB_LINK_NOT_AVAILABLE');
2757 --FND_MSG_PUB.ADD;
2758 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
2759 WHEN IEM_ADMIN_ACCOUNT_NOT_EXIST THEN
2760 ROLLBACK TO create_folder_PVT;
2761
2762 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2763 FND_MESSAGE.SET_NAME('IEM', 'IEM_ADMIN_ACCOUNT_NOT_EXIST');
2764
2765 FND_MSG_PUB.ADD;
2766 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
2767
2768 WHEN tns_no_listener THEN
2769 ROLLBACK TO create_folder_PVT;
2770 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2771 FND_MESSAGE.SET_NAME('IEM', 'IEM_ADMIN_TNS_NO_LISTENER');
2772 FND_MSG_PUB.ADD;
2773 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
2774
2775 WHEN looking_up_object THEN
2776 ROLLBACK TO create_folder_PVT;
2777 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2778 FND_MESSAGE.SET_NAME('IEM', 'IEM_ADMIN_LOOKING_UP_OBJECT');
2779 FND_MSG_PUB.ADD;
2780 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
2781
2782
2783 WHEN FND_API.G_EXC_ERROR THEN
2784 ROLLBACK TO create_folder_PVT;
2785 x_return_status := FND_API.G_RET_STS_ERROR ;
2786
2787 FND_MSG_PUB.Count_And_Get
2788 ( p_count => x_msg_count,
2789 p_data => x_msg_data
2790
2791 );
2792
2793 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2794
2795 ROLLBACK TO create_folder_PVT;
2796 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2797 FND_MSG_PUB.Count_And_Get
2798 ( p_count => x_msg_count,
2799 p_data => x_msg_data
2800 );
2801
2802 WHEN OTHERS THEN
2803 ROLLBACK TO create_folder_PVT;
2804
2805 x_return_status := FND_API.G_RET_STS_ERROR;
2806 IF FND_MSG_PUB.Check_Msg_Level
2807 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2808 THEN
2809
2810 FND_MSG_PUB.Add_Exc_Msg
2811 ( G_PKG_NAME ,
2812 l_api_name
2813 );
2814 END IF;
2815 FND_MSG_PUB.Count_And_Get
2816 ( p_count => x_msg_count,
2817
2818
2819 p_data => x_msg_data
2820 );
2821
2822 END create_folder;
2823
2824 */
2825 -- ***** Remove for 11i compliance ***
2826 /*
2827 PROCEDURE delete_folder (
2828 p_api_version_number IN NUMBER,
2829 p_init_msg_list IN VARCHAR2 := null,
2830 p_commit IN VARCHAR2 := null,
2831 p_email_account_id IN NUMBER,
2832 p_class_id IN NUMBER,
2833 x_return_status OUT NOCOPY VARCHAR2,
2834 x_msg_count OUT NOCOPY NUMBER,
2835 x_msg_data OUT NOCOPY VARCHAR2
2836 ) is
2837
2838 TYPE email_acct_Rec IS RECORD (
2839 email_user iem_email_accounts.email_user%TYPE,
2840
2841 domain iem_email_accounts.domain%TYPE,
2842 email_password iem_email_accounts.email_password%TYPE);
2843
2844 l_email_acct_Rec email_acct_Rec;
2845 l_api_name VARCHAR2(255):='delete_folder';
2846 l_api_version_number NUMBER:=1.0;
2847
2848 l_seq_id NUMBER;
2849
2850 l_db_server_id NUMBER;
2851
2852 l_stat varchar2(10);
2853 l_str varchar2(200);
2854 l_ret number;
2855
2856 l_count NUMBER;
2857 l_data varchar2(255);
2858 l_im_link varchar2(200);
2859 l_db_link varchar2(100);
2860 l_im_link1 varchar2(200);
2861
2862 l_folder varchar2(50);
2863 l_message IEM_IM_WRAPPERS_PVT.msg_table;
2864 l_folderid number;
2865 IEM_ACCT_ID_NOT_EXIST EXCEPTION;
2866 IEM_ADMIN_DB_CONNECTION_FAILED EXCEPTION;
2867 tns_no_listener EXCEPTION;
2868 PRAGMA EXCEPTION_INIT(tns_no_listener, -12541);
2869 BEGIN
2870 -- Standard Start of API savepoint
2871 SAVEPOINT delete_folder_PVT;
2872
2873
2874
2875 -- Standard call to check for call compatibility.
2876 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
2877 p_api_version_number,
2878
2879 l_api_name,
2880 G_PKG_NAME)
2881 THEN
2882 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2883 END IF;
2884
2885 -- Initialize message list if p_init_msg_list is set to TRUE.
2886 IF FND_API.to_Boolean( p_init_msg_list )
2887 THEN
2888
2889 FND_MSG_PUB.initialize;
2890
2891 END IF;
2892
2893
2894 -- Initialize API return status to SUCCESS
2895 x_return_status := FND_API.G_RET_STS_SUCCESS;
2896
2897 --begins here
2898 select DB_SERVER_ID
2899 INTO l_db_server_id
2900 FROM IEM_EMAIL_ACCOUNTS
2901 WHERE email_account_id = p_email_account_id;
2902
2903
2904 IEM_DB_CONNECTIONS_PVT.select_item(
2905 p_api_version_number => 1.0,
2906
2907 p_db_server_id => l_db_server_id,
2908 p_is_admin => 'P',
2909 x_db_link => l_im_link1,
2910 x_return_status => l_stat,
2911 x_msg_count => l_count,
2912 x_msg_data => l_data );
2913
2914 if ( l_stat <> FND_API.G_RET_STS_SUCCESS ) then
2915 raise IEM_ADMIN_DB_CONNECTION_FAILED;
2916 end if;
2917
2918
2919
2920 select email_user, domain, email_password into l_email_acct_Rec from iem_email_accounts
2921 where email_account_id = p_email_account_id;
2922
2923 if SQL%NOTFOUND then
2924
2925 raise IEM_ACCT_ID_NOT_EXIST;
2926
2927 end if;
2928
2929 IF l_im_link1 is null then
2930 l_im_link := null;
2931 ELSE
2932 l_im_link := '@'||l_im_link1;
2933 END IF;
2934
2935 l_str := 'begin :l_ret:=im_api.authenticate'||l_im_link||'(:a_user, :a_domain, :a_password); end;';
2936 EXECUTE IMMEDIATE l_str using OUT l_ret, l_email_acct_Rec.email_user, l_email_acct_Rec.domain,l_email_acct_Rec.email_password;
2937
2938 IF l_ret=0 THEN
2939
2940
2941 x_return_status := FND_API.G_RET_STS_SUCCESS;
2942 ELSE
2943 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2944 END IF;
2945
2946 --get folder name
2947 select name into l_folder from iem_route_classifications where route_classification_id = p_class_id;
2948 l_folder := '/'||l_folder;
2949
2950 --check if the folder is empty
2951 l_ret := IEM_IM_WRAPPERS_PVT.openfolder(l_folder, l_im_link, l_message);
2952
2953 -- TO DELETE the FOLDER
2954
2955
2956 -- if the folder does not exist, do nothing return success.
2957 -- Else if no message in the folder, delete the folder, return status.
2958 -- otherwise return error (when there are message in the folder).
2959 IF l_ret=0 THEN --if the folder exist
2960
2961 IF l_message.COUNT = 0 THEN --if there is no message in the folder
2962 l_str := 'begin :l_ret:=im_api.getfolderid'||l_im_link||'(:a_path,:a_folderid);end;';
2963 EXECUTE IMMEDIATE l_str using OUT l_ret, l_folder, IN OUT l_folderid;
2964
2965 IF l_ret=0 THEN
2966 l_str:='begin :l_ret:=im_api.deletefolder'||l_im_link||'(:a_folder);end;';
2967
2968 EXECUTE IMMEDIATE l_str using OUT l_ret, l_folder;
2969
2970 IF l_ret=0 THEN
2971 x_return_status := FND_API.G_RET_STS_SUCCESS;
2972 ELSE
2973 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2974 END IF;
2975 ELSE
2976 x_return_status := FND_API.G_RET_STS_SUCCESS;
2977 END IF;
2978 ELSE
2979 x_return_status := FND_API.G_RET_STS_ERROR;
2980 END IF;
2981
2982 ELSE
2983 x_return_status := FND_API.G_RET_STS_SUCCESS;
2984
2985 END IF;
2986
2987 --Standard check of p_commit
2988 IF FND_API.to_Boolean(p_commit) THEN
2989 COMMIT WORK;
2990 END IF;
2991
2992 FND_MSG_PUB.Count_And_Get
2993 ( p_count => x_msg_count,
2994 p_data => x_msg_data
2995 );
2996 EXCEPTION
2997 WHEN IEM_ADMIN_DB_CONNECTION_FAILED THEN
2998 ROLLBACK TO delete_folder_PVT;
2999 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3000 --FND_MESSAGE.SET_NAME('IEM', 'IEM_ADMIN_DB_CONNECT');
3001 --FND_MSG_PUB.ADD;
3002 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
3003
3004 WHEN IEM_ACCT_ID_NOT_EXIST THEN
3005 ROLLBACK TO delete_folder_PVT;
3006 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3007 FND_MESSAGE.SET_NAME('IEM', 'IEM_ACCT_ID_NOT_EXIST');
3008 FND_MSG_PUB.ADD;
3009 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
3010
3011 WHEN tns_no_listener THEN
3012 ROLLBACK TO delete_folder_PVT;
3013 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3014 FND_MESSAGE.SET_NAME('IEM', 'IEM_ADMIN_TNS_NO_LISTENER');
3015 FND_MSG_PUB.ADD;
3016 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
3017
3018
3019 WHEN FND_API.G_EXC_ERROR THEN
3020 ROLLBACK TO delete_folder_PVT;
3021 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3022
3023
3024 FND_MSG_PUB.Count_And_Get
3025 ( p_count => x_msg_count,
3026 p_data => x_msg_data
3027
3028 );
3029
3030 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3031 ROLLBACK TO delete_folder_PVT;
3032 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3033 FND_MSG_PUB.Count_And_Get
3034 ( p_count => x_msg_count,
3035 p_data => x_msg_data
3036
3037 );
3038
3039
3040 WHEN OTHERS THEN
3041 ROLLBACK TO delete_folder_PVT;
3042
3043 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3044 IF FND_MSG_PUB.Check_Msg_Level
3045 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3046 THEN
3047 FND_MSG_PUB.Add_Exc_Msg
3048 ( G_PKG_NAME ,
3049 l_api_name
3050
3051 );
3052 END IF;
3053 FND_MSG_PUB.Count_And_Get
3054 ( p_count => x_msg_count,
3055
3056 p_data => x_msg_data
3057
3058 );
3059
3060 END delete_folder;
3061
3062 */
3063
3064 --delete class folder first, then delete association of class and email account
3065 PROCEDURE delete_association_on_acctId
3066 (p_api_version_number IN NUMBER,
3067 P_init_msg_list IN VARCHAR2 := null,
3068 p_commit IN VARCHAR2 := null,
3069 p_email_account_id IN NUMBER,
3070
3071 x_return_status OUT NOCOPY VARCHAR2,
3072 x_msg_count OUT NOCOPY NUMBER,
3073 x_msg_data OUT NOCOPY VARCHAR2)
3074
3075 IS
3076 l_api_name VARCHAR2(255):='delete_association_on_acctId';
3077 l_api_version_number NUMBER:=1.0;
3078 l_seq_id NUMBER;
3079 l_return_status varchar2(30);
3080
3081
3082 CURSOR class_id_cursor( l_account_id IN NUMBER ) IS
3083 select unique route_classification_id from iem_account_route_class where email_account_id = l_account_id;
3084
3085 MY_EXCP_MSG_IN_FOLDER EXCEPTION;
3086 IEM_UNEXPT_ERR_DELETE_FOLDER EXCEPTION;
3087 l_count_msg_postmdt number := 0;
3088
3089 BEGIN
3090
3091 --Standard Savepoint
3092 SAVEPOINT delete_association_on_acctId;
3093 -- Standard call to check for call compatibility.
3094 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
3095
3096 p_api_version_number,
3097 l_api_name,
3098 G_PKG_NAME)
3099 THEN
3100 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3101 END IF;
3102
3103 --Initialize the message list if p_init_msg_list is set to TRUE
3104 If FND_API.to_Boolean(p_init_msg_list) THEN
3105
3106
3107 FND_MSG_PUB.initialize;
3108 END IF;
3109
3110
3111 --Initialize API status return
3112 x_return_status := FND_API.G_RET_STS_SUCCESS;
3113
3114 --actuall API starts here
3115 --FOR class_id IN class_id_cursor( p_email_account_id ) LOOP
3116
3117 -- select count(*) into l_count_msg_postmdt from iem_post_mdts where rt_classification_id=class_id.route_classification_id and rt_classification_id<>0 and email_account_id=p_email_account_id;
3118
3119 -- if l_count_msg_postmdt <> 0 then
3120 -- RAISE MY_EXCP_MSG_IN_FOLDER;
3121 -- end if;
3122
3123 --iem_route_class_pvt.delete_folder(p_api_version_number =>p_api_version_number,
3124 -- p_init_msg_list => p_init_msg_list,
3125 -- p_commit => FND_API.G_FALSE,
3126 -- p_email_account_id =>p_email_account_id,
3127 -- p_class_id => class_id.route_classification_id,
3128 -- x_return_status =>l_return_status,
3129 -- x_msg_count => x_msg_count,
3130 -- x_msg_data => x_msg_data);
3131
3132 --if (l_return_status = FND_API.G_RET_STS_ERROR) then
3133 -- RAISE MY_EXCP_MSG_IN_FOLDER;
3134 --elsif (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) then
3135 -- RAISE IEM_UNEXPT_ERR_DELETE_FOLDER;
3136 --end if;
3137 --END LOOP;
3138
3139 --finially delete association of classifcations with this email account
3140
3141 delete from iem_account_route_class where email_account_id = p_email_account_id;
3142
3143
3144 --Standard check of p_commit
3145 IF FND_API.to_Boolean(p_commit) THEN
3146 COMMIT WORK;
3147 END IF;
3148
3149 FND_MSG_PUB.Count_And_Get
3150 ( p_count => x_msg_count,
3151 p_data => x_msg_data
3152 );
3153
3154 EXCEPTION
3155
3156
3157 WHEN MY_EXCP_MSG_IN_FOLDER THEN
3158 ROLLBACK TO delete_association_on_acctId;
3159 x_return_status := FND_API.G_RET_STS_ERROR;
3160 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
3161
3162 WHEN IEM_UNEXPT_ERR_DELETE_FOLDER THEN
3163 ROLLBACK TO delete_association_on_acctId;
3164 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3165 FND_MESSAGE.SET_NAME('IEM', 'IEM_UNEXPT_ERR_DELETE_FOLDER');
3166 FND_MSG_PUB.ADD;
3167 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
3168
3169 WHEN FND_API.G_EXC_ERROR THEN
3170
3171
3172 ROLLBACK TO delete_association_on_acctId;
3173 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3174 FND_MSG_PUB.Count_And_Get
3175 ( p_count => x_msg_count,p_data => x_msg_data);
3176
3177 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3178 ROLLBACK TO delete_association_on_acctId;
3179 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3180 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,p_data => x_msg_data);
3181
3182 WHEN OTHERS THEN
3183 ROLLBACK TO delete_association_on_acctId;
3184
3185 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3186
3187 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3188 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME , l_api_name);
3189 END IF;
3190
3191 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count ,p_data => x_msg_data);
3192
3193 END delete_association_on_acctId;
3194
3195 END IEM_ROUTE_CLASS_PVT; -- Package Body IEM_ROUTE_CLASS_PVT