[Home] [Help]
PACKAGE BODY: APPS.IEM_ROUTE_PVT
Source
1 PACKAGE BODY IEM_ROUTE_PVT AS
2 /* $Header: iemvroub.pls 120.0 2005/06/02 13:41:41 appldev noship $ */
3
4 --
5 --
6 -- Purpose: Mantain route related operations
7 --
8 -- MODIFICATION HISTORY
9 -- Person Date Comments
10 -- Liang Xia 4/24/2001 Created
11 -- Liang Xia 6/7/2001 added checking duplication on IEM_ROUTES.name for PROCEDURE
12 -- create_item_routes and update_item_route
13 -- added updating priority in IEM_ACCOUNT_ROUTES for delete_item_batch
14 -- Liang Xia 6/7/2002 added validation for dynamic Route
15 -- Liang Xia 11/6/2002 release the validation for ALL_EMAILS and fixed part of "No MISS.." GSCC warning.
16 -- Liang Xia 12/2/2002 Fixed PLSQL standard: "No MISS.." "NOCOPY" GSCC warning.
17 -- Liang Xia 12/06/2004 Changed for 115.11 schema: iem_mstemail_account
18 -- --------- ------ ------------------------------------------
19
20 -- Enter procedure, function bodies as shown below
21 G_PKG_NAME CONSTANT varchar2(30) :='IEM_ROUTE_PVT ';
22 G_ROUTE_ID varchar2(30) ;
23 G_created_updated_by NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('USER_ID')) ;
24
25 G_LAST_UPDATE_LOGIN NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('LOGIN_ ID') ) ;
26
27 PROCEDURE delete_acct_route_by_acct
28 (p_api_version_number IN NUMBER,
29 P_init_msg_list IN VARCHAR2 := null,
30 p_commit IN VARCHAR2 := null,
31 p_email_account_id IN NUMBER,
32 x_return_status OUT NOCOPY VARCHAR2,
33 x_msg_count OUT NOCOPY NUMBER,
34 x_msg_data OUT NOCOPY VARCHAR2)
35 IS
36 i INTEGER;
37 l_api_name varchar2(30):='delete_acct_route_by_acct';
38 l_api_version_number number:=1.0;
39
40 IEM_ROUTE_NOT_DELETED EXCEPTION;
41 BEGIN
42
43 --Standard Savepoint
44 SAVEPOINT delete_acct_route_by_acct;
45
46 -- Standard call to check for call compatibility.
47 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
48 p_api_version_number,
49 l_api_name,
50 G_PKG_NAME)
51 THEN
52 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
53 END IF;
54
55
56
57 --Initialize the message list if p_init_msg_list is set to TRUE
58 If FND_API.to_Boolean(p_init_msg_list) THEN
59 FND_MSG_PUB.initialize;
60 END IF;
61
62 --Initialize API status return
63 x_return_status := FND_API.G_RET_STS_SUCCESS;
64
65 --Actual API starts here
66 DELETE
67 FROM IEM_ACCOUNT_ROUTES
68 WHERE email_account_id = p_email_account_id;
69
70
71 --Standard check of p_commit
72 IF FND_API.to_Boolean(p_commit) THEN
73 COMMIT WORK;
74 END IF;
75
76 FND_MSG_PUB.Count_And_Get
77 ( p_count => x_msg_count,p_data => x_msg_data);
78
79 EXCEPTION
80 WHEN FND_API.G_EXC_ERROR THEN
81 ROLLBACK TO delete_acct_route_by_acct;
82 x_return_status := FND_API.G_RET_STS_ERROR ;
83 FND_MSG_PUB.Count_And_Get
84
85 ( p_count => x_msg_count,p_data => x_msg_data);
86
87 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
88 ROLLBACK TO delete_acct_route_by_acct;
89 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
90 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,p_data => x_msg_data);
91
92
93 WHEN OTHERS THEN
94 ROLLBACK TO delete_acct_route_by_acct;
95 x_return_status := FND_API.G_RET_STS_ERROR;
96 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
97 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME , l_api_name);
98
99 END IF;
100 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count ,p_data => x_msg_data);
101
102 END delete_acct_route_by_acct;
103
104
105 PROCEDURE delete_item_batch
106 (p_api_version_number IN NUMBER,
107 P_init_msg_list IN VARCHAR2 := null,
108 p_commit IN VARCHAR2 := null,
109 p_route_ids_tbl IN jtf_varchar2_Table_100,
110 x_return_status OUT NOCOPY VARCHAR2,
111 x_msg_count OUT NOCOPY NUMBER,
112 x_msg_data OUT NOCOPY VARCHAR2)
113 IS
114 i INTEGER;
115 l_api_name varchar2(30):='delete_item_batch';
116 l_api_version_number number:=1.0;
117
118 CURSOR acct_id_cursor( l_route_id IN NUMBER ) IS
119 select email_account_id from iem_account_routes where route_id = l_route_id;
120
121 IEM_ROUTE_NOT_DELETED EXCEPTION;
122 BEGIN
123
124
125
126 --Standard Savepoint
127 SAVEPOINT delete_item_batch;
128
129 -- Standard call to check for call compatibility.
130 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
131 p_api_version_number,
132 l_api_name,
133 G_PKG_NAME)
134 THEN
135 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
136 END IF;
137
138
139
140 --Initialize the message list if p_init_msg_list is set to TRUE
141 If FND_API.to_Boolean(p_init_msg_list) THEN
142 FND_MSG_PUB.initialize;
143 END IF;
144
145 --Initialize API status return
146 x_return_status := FND_API.G_RET_STS_SUCCESS;
147
148 --Actual API starts here
149 FORALL i IN p_route_ids_tbl.FIRST..p_route_ids_tbl.LAST
150 DELETE
151 FROM IEM_ROUTES
152
153 WHERE route_id = p_route_ids_tbl(i);
154
155
156 if SQL%NOTFOUND then
157 raise IEM_ROUTE_NOT_DELETED;
158 end if;
159
160 --Delete the accounts, rules associated with this route
161 if ( p_route_ids_tbl.count <> 0 ) then
162
163 FOR i IN p_route_ids_tbl.FIRST..p_route_ids_tbl.LAST LOOP
164
165 -- update priority after delete an account_route
166
167 FOR acct_id IN acct_id_cursor(p_route_ids_tbl(i)) LOOP
168 Update iem_account_routes set priority=priority-1
169
170 where email_account_id=acct_id.email_account_id and priority > (Select priority from iem_account_routes
171 where route_id=p_route_ids_tbl(i) and email_account_id = acct_id.email_account_id);
172 END LOOP;
173
174 DELETE
175 FROM IEM_ACCOUNT_ROUTES
176 WHERE route_id = p_route_ids_tbl(i);
177
178
179 DELETE
180
181 FROM IEM_ROUTE_RULES
182 WHERE route_id=p_route_ids_tbl(i);
183 END LOOP;
184
185 end if;
186
187 --Standard check of p_commit
188 IF FND_API.to_Boolean(p_commit) THEN
189 COMMIT WORK;
190 END IF;
191
192
193 EXCEPTION
194
195 WHEN IEM_ROUTE_NOT_DELETED THEN
196 ROLLBACK TO delete_item_batch;
197 x_return_status := FND_API.G_RET_STS_ERROR;
198 FND_MESSAGE.SET_NAME('IEM', 'IEM_ROUTE_NOT_DELETED');
199
200 FND_MSG_PUB.ADD;
201 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
202
203 WHEN FND_API.G_EXC_ERROR THEN
204 ROLLBACK TO delete_item_batch;
205 x_return_status := FND_API.G_RET_STS_ERROR ;
206 FND_MSG_PUB.Count_And_Get
207 ( p_count => x_msg_count,p_data => x_msg_data);
208
209
210 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
211 ROLLBACK TO delete_item_batch;
212 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
213 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,p_data => x_msg_data);
214
215
216 WHEN OTHERS THEN
217 ROLLBACK TO delete_item_batch;
218 x_return_status := FND_API.G_RET_STS_ERROR;
219 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
220 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME , l_api_name);
221 END IF;
222
223 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count ,p_data => x_msg_data);
224
225 END delete_item_batch;
226
227 PROCEDURE create_item_wrap (
228
229 p_api_version_number IN NUMBER,
230 p_init_msg_list IN VARCHAR2 := null,
231 p_commit IN VARCHAR2 := null,
232 p_route_name IN VARCHAR2,
233 p_route_description IN VARCHAR2:= null,
234 p_route_boolean_type_code IN VARCHAR2,
235 p_proc_name IN VARCHAR2 := null,
236 p_all_email IN VARCHAR2 := null,
237 p_rule_key_typecode_tbl IN jtf_varchar2_Table_100,
238 p_rule_operator_typecode_tbl IN jtf_varchar2_Table_100,
239 p_rule_value_tbl IN jtf_varchar2_Table_300,
240 x_return_status OUT NOCOPY VARCHAR2,
241 x_msg_count OUT NOCOPY NUMBER,
242 x_msg_data OUT NOCOPY VARCHAR2 ) is
243
244
245 l_api_name VARCHAR2(255):='create_item_wrap';
246 l_api_version_number NUMBER:=1.0;
247
248 l_route_id IEM_ROUTES.ROUTE_ID%TYPE;
249 l_route_rule_id IEM_ROUTE_RULES.ROUTE_RULE_ID%TYPE;
250 l_return_type VARCHAR2(30);
251
252
253 l_userid NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('USER_ID')) ;
254 l_login NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('LOGIN_ID')) ;
255
256 l_return_status VARCHAR2(20) := FND_API.G_RET_STS_SUCCESS;
257 l_msg_count NUMBER := 0;
258 l_msg_data VARCHAR2(2000);
259
260 logMessage VARCHAR2(2000);
261
262
263 IEM_ROUTE_NOT_CREATED EXCEPTION;
264 IEM_ROUTE_RULE_NOT_CREATED EXCEPTION;
265
266 BEGIN
267
268 -- Standard Start of API savepoint
269 SAVEPOINT create_item_wrap;
270
271 -- Standard call to check for call compatibility.
272 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
273 p_api_version_number,
274 l_api_name,
275 G_PKG_NAME)
276 THEN
277
278 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
279 END IF;
280
281
282 -- Initialize message list if p_init_msg_list is set to TRUE.
283 IF FND_API.to_Boolean( p_init_msg_list )
284 THEN
285 FND_MSG_PUB.initialize;
286 END IF;
287
288 -- Initialize API return status to SUCCESS
289 x_return_status := FND_API.G_RET_STS_SUCCESS;
290
291 --API Body
292 /*
293 FND_LOG_REPOSITORY.init(null,null);
294
295 if fnd_log.test(FND_LOG.LEVEL_STATEMENT, 'IEM.PLSQL.IEM_ROUTE_PVT.CREATE_ITEM_WRAP.START') then
296 logMessage := '[create item is called!]';
297 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'IEM.PLSQL.IEM_ROUTE_PVT.CREATE_ITEM_WRAP.START', logMessage);
298 end if;
299 */
300 --Now call the create_item() to create the acccount
301 if ( p_route_boolean_type_code = 'DYNAMIC' ) then
302 l_return_type := p_rule_key_typecode_tbl(1);
303 else
304 l_return_type := FND_API.G_MISS_CHAR;
305 end if;
306
307 iem_route_pvt.create_item_routes (
308 p_api_version_number=>p_api_version_number,
309 p_init_msg_list => p_init_msg_list,
310 p_commit => FND_API.G_FALSE,
311 p_name => p_route_name,
312 p_description => p_route_description,
313 p_boolean_type_code =>p_route_boolean_type_code,
314 p_proc_name => p_proc_name,
315 p_all_email => p_all_email,
316 p_return_type => l_return_type,
317 x_return_status =>l_return_status,
318 x_msg_count => l_msg_count,
319 x_msg_data => l_msg_data);
320
321
322 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
323
324 raise IEM_ROUTE_NOT_CREATED;
325 end if;
326
327
328 --Getting the newly created email account id
329 l_route_id := G_ROUTE_ID;
330
331 -- dbms_output.put_line('route id : '||l_route_id);
332
333 if p_rule_key_typecode_tbl.count > 0 then
334 FOR i IN p_rule_key_typecode_tbl.FIRST..p_rule_key_typecode_tbl.LAST loop
335
336 iem_route_pvt.create_item_route_rules (
337
338
339 p_api_version_number=>p_api_version_number,
340 p_init_msg_list => p_init_msg_list,
341 p_commit => p_commit,
342 p_route_id => l_route_id,
343 p_key_type_code => p_rule_key_typecode_tbl(i),
344 p_operator_type_code => p_rule_operator_typecode_tbl(i),
345 p_value =>p_rule_value_tbl(i),
346 x_return_status =>l_return_status,
347 x_msg_count => l_msg_count,
348 x_msg_data => l_msg_data);
349
350
351 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
352 raise IEM_ROUTE_RULE_NOT_CREATED;
353 end if;
354 end loop;
355 end if;
356 -- Standard Check Of p_commit.
357 IF FND_API.To_Boolean(p_commit) THEN
358 COMMIT WORK;
359 END IF;
360
361 -- Standard callto get message count and if count is 1, get message info.
362 FND_MSG_PUB.Count_And_Get
363 ( p_count => x_msg_count,
364 p_data => x_msg_data
365 );
366 EXCEPTION
367 WHEN IEM_ROUTE_NOT_CREATED THEN
368 ROLLBACK TO create_item_wrap;
369 x_return_status := FND_API.G_RET_STS_ERROR ;
370 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
371
372
373 WHEN IEM_ROUTE_RULE_NOT_CREATED THEN
374
375 ROLLBACK TO create_item_wrap;
376 FND_MESSAGE.SET_NAME('IEM','IEM_ROUTE_RULE_NOT_CREATED');
377 FND_MSG_PUB.Add;
378 x_return_status := FND_API.G_RET_STS_ERROR ;
379 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
380
381
382 WHEN FND_API.G_EXC_ERROR THEN
383 ROLLBACK TO create_item_wrap;
384 x_return_status := FND_API.G_RET_STS_ERROR ;
385 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,p_data => x_msg_data);
386
387
388 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
389
390 ROLLBACK TO create_item_wrap;
391 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
392 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
393
394 WHEN OTHERS THEN
395 ROLLBACK TO create_item_wrap;
396 x_return_status := FND_API.G_RET_STS_ERROR;
397 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
398 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME , l_api_name);
399 END IF;
400
401 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count, p_data => x_msg_data );
402
403 END create_item_wrap;
404
405
406
407 PROCEDURE create_item_routes (
408 p_api_version_number IN NUMBER,
409 p_init_msg_list IN VARCHAR2 := null,
410 p_commit IN VARCHAR2 := null,
411 p_name IN VARCHAR2,
412 p_description IN VARCHAR2:= null,
413 p_boolean_type_code IN VARCHAR2,
414 p_proc_name IN VARCHAR2 := null,
415 p_all_email IN VARCHAR2 := null,
416 p_return_type IN VARCHAR2 := null,
417 x_return_status OUT NOCOPY VARCHAR2,
418 x_msg_count OUT NOCOPY NUMBER,
419 x_msg_data OUT NOCOPY VARCHAR2
420 ) is
421 l_api_name VARCHAR2(255):='create_item_routes';
422 l_api_version_number NUMBER:=1.0;
423 l_seq_id NUMBER;
424 l_proc_name VARCHAR2(256);
425 l_name_count NUMBER;
426 l_all_email VARCHAR2(1);
427 l_description VARCHAR2(256);
428 l_return_status VARCHAR2(20) := FND_API.G_RET_STS_SUCCESS;
429 l_msg_count NUMBER := 0;
430 l_msg_data VARCHAR2(2000);
431
432 IEM_ADMIN_ROUTE_DUP_NAME EXCEPTION;
433 l_IEM_INVALID_PROCEDURE EXCEPTION;
434 IEM_ADM_NO_PROCEDURE_NAME EXCEPTION;
435
436 BEGIN
437 -- Standard Start of API savepoint
438 SAVEPOINT create_item_routes_PVT;
439
440 -- Standard call to check for call compatibility.
441
442 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
443 p_api_version_number,
444 l_api_name,
445 G_PKG_NAME)
446 THEN
447 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
448 END IF;
449
450
451 -- Initialize message list if p_init_msg_list is set to TRUE.
452 IF FND_API.to_Boolean( p_init_msg_list )
453 THEN
454 FND_MSG_PUB.initialize;
455 END IF;
456
457
458 -- Initialize API return status to SUCCESS
459 x_return_status := FND_API.G_RET_STS_SUCCESS;
460
461 --begins here
462
463 --check duplicate value for attribute Name
464 select count(*) into l_name_count from iem_routes where UPPER(name) = UPPER(p_name);
465
466 if l_name_count > 0 then
467 raise IEM_ADMIN_ROUTE_DUP_NAME;
468 end if;
469
470 if p_all_email is null or p_all_email = FND_API.G_MISS_CHAR then
471 l_all_email := 'N';
472 elsif ( p_all_email <> 'N' and p_all_email<>'Y') then
473 l_all_email := 'N';
474 else
475 l_all_email := p_all_email;
476 end if;
477
478 if ( p_boolean_type_code = 'DYNAMIC' ) then
479 if p_proc_name is null or p_proc_name = FND_API.G_MISS_CHAR then
480 raise IEM_ADM_NO_PROCEDURE_NAME;
481 else
482 l_proc_name := LTRIM(RTRIM( p_proc_name ) );
483 --validation goes here.
484 IEM_ROUTE_RUN_PROC_PVT.validProcedure(
485 p_api_version_number => P_Api_Version_Number,
486 p_init_msg_list => FND_API.G_FALSE,
487 p_commit => P_Commit,
488 p_ProcName => l_proc_name,
489 p_return_type => p_return_type,
490 x_return_status => l_return_status,
491 x_msg_count => l_msg_count,
492 x_msg_data => l_msg_data
493 );
494 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
495 raise l_IEM_INVALID_PROCEDURE;
496 end if;
497 end if;
498 else
499 l_proc_name := null;
500 end if;
501
502 if p_description=FND_API.G_MISS_CHAR then
503 l_description := null;
504 else
505 l_description := p_description;
506 end if;
507
508 --get next sequential number for route_id
509 SELECT IEM_ROUTES_s1.nextval
510 INTO l_seq_id
511 FROM dual;
512
513 G_ROUTE_ID := l_seq_id;
514
515 INSERT INTO IEM_ROUTES
516 (
517 ROUTE_ID,
518 NAME,
519 DESCRIPTION,
520 BOOLEAN_TYPE_CODE,
521 PROCEDURE_NAME,
522 all_email,
523 ATTRIBUTE1,
524 ATTRIBUTE2,
525 ATTRIBUTE3,
526 ATTRIBUTE4,
527 ATTRIBUTE5,
528 ATTRIBUTE6,
529 ATTRIBUTE7,
530 ATTRIBUTE8,
531 ATTRIBUTE9,
532 ATTRIBUTE10,
533 ATTRIBUTE11,
534 ATTRIBUTE12,
535 ATTRIBUTE13,
536 ATTRIBUTE14,
537 ATTRIBUTE15,
538 ATTRIBUTE_CATEGORY,
539 CREATED_BY,
540 CREATION_DATE,
541 LAST_UPDATED_BY,
542 LAST_UPDATE_DATE,
543 LAST_UPDATE_LOGIN
544 )
545 VALUES
546 (
547
548 l_seq_id,
549 p_name,
550 l_description,
551 p_boolean_type_code,
552 l_proc_name,
553 l_all_email,
554 NULL,
555 NULL,
556 NULL,
557 NULL,
558 NULL,
559 NULL,
560 NULL,
561 NULL,
562 NULL,
563
564 NULL,
565
566 NULL,
567 NULL,
568 NULL,
569 NULL,
570 NULL,
571 NULL,
572 decode(G_created_updated_by,null,-1,G_created_updated_by),
573 sysdate,
574 decode(G_created_updated_by,null,-1,G_created_updated_by),
575 sysdate,
576 decode(G_LAST_UPDATE_LOGIN,null,-1,G_LAST_UPDATE_LOGIN)
577
578
579 );
580
581 -- Standard Check Of p_commit.
582 IF FND_API.To_Boolean(p_commit) THEN
583 COMMIT WORK;
584 END IF;
585
586 -- Standard callto get message count and if count is 1, get message info.
587 FND_MSG_PUB.Count_And_Get
588 ( p_count => x_msg_count,
589 p_data => x_msg_data
590 );
591
592
593
594 EXCEPTION
595 WHEN l_IEM_INVALID_PROCEDURE THEN
596 ROLLBACK TO create_item_routes_PVT;
597 x_return_status := FND_API.G_RET_STS_ERROR ;
598 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
599
600 WHEN IEM_ADMIN_ROUTE_DUP_NAME THEN
601 ROLLBACK TO create_item_routes_PVT;
602 FND_MESSAGE.SET_NAME('IEM','IEM_ADMIN_ROUTE_DUP_NAME');
603 FND_MSG_PUB.Add;
604 x_return_status := FND_API.G_RET_STS_ERROR ;
605 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
606
607 WHEN IEM_ADM_NO_PROCEDURE_NAME THEN
608 ROLLBACK TO create_item_routes_PVT;
609 FND_MESSAGE.SET_NAME('IEM','IEM_ADM_NO_PROCEDURE_NAME');
610 FND_MSG_PUB.Add;
611 x_return_status := FND_API.G_RET_STS_ERROR ;
612 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
613
614 WHEN FND_API.G_EXC_ERROR THEN
615 ROLLBACK TO create_item_routes_PVT;
616 x_return_status := FND_API.G_RET_STS_ERROR ;
617
618 FND_MSG_PUB.Count_And_Get
619
620 ( p_count => x_msg_count,
621 p_data => x_msg_data
622 );
623
624 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
625 ROLLBACK TO create_item_routes_PVT;
626 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
627 FND_MSG_PUB.Count_And_Get
628 ( p_count => x_msg_count,
629 p_data => x_msg_data
630 );
631
632
633 WHEN OTHERS THEN
634
635 ROLLBACK TO create_item_routes_PVT;
636 x_return_status := FND_API.G_RET_STS_ERROR;
637 IF FND_MSG_PUB.Check_Msg_Level
638 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
639 THEN
640 FND_MSG_PUB.Add_Exc_Msg
641 ( G_PKG_NAME ,
642 l_api_name
643 );
644 END IF;
645
646 FND_MSG_PUB.Count_And_Get
647 ( p_count => x_msg_count,
648 p_data => x_msg_data
649
650 );
651
652 END create_item_routes;
653
654 --function to create cannonical function
655 FUNCTION displayDT_to_canonical ( displayDT IN VARCHAR2 )
656 return VARCHAR2
657 is
658 user_mask varchar2(265) := 'DD-MON-YYYY';
659
660 canonicalMask varchar2(265) := 'YYYYMMDD';
661 BEGIN
662 RETURN to_char( to_date( displayDT, user_mask), canonicalMask);
663 EXCEPTION
664
665 WHEN OTHERS THEN
666 RETURN (NULL);
667 END displayDT_to_canonical;
668
669
670
671 PROCEDURE create_item_route_rules (
672 p_api_version_number IN NUMBER,
673 p_init_msg_list IN VARCHAR2 := null,
674 p_commit IN VARCHAR2 := null,
675 p_route_id IN NUMBER,
676 p_key_type_code IN VARCHAR2,
677 p_operator_type_code IN VARCHAR2,
678 p_value IN VARCHAR2,
679 x_return_status OUT NOCOPY VARCHAR2,
680 x_msg_count OUT NOCOPY NUMBER,
681 x_msg_data OUT NOCOPY VARCHAR2
682 ) is
683 l_api_name VARCHAR2(255):='create_item_route_rules';
684 l_api_version_number NUMBER:=1.0;
685
686 l_seq_id number;
687
688 --IEM_INVALID_DATE_FORMAT EXCEPTION;
689
690 BEGIN
691 -- Standard Start of API savepoint
692
693 SAVEPOINT create_item_route_rules_PVT;
694 -- Standard call to check for call compatibility.
695 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
696 p_api_version_number,
697 l_api_name,
698 G_PKG_NAME)
699
700 THEN
701 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
702 END IF;
703
704 -- Initialize message list if p_init_msg_list is set to TRUE.
705 IF FND_API.to_Boolean( p_init_msg_list )
706 THEN
707
708 FND_MSG_PUB.initialize;
709 END IF;
710
711 -- Initialize API return status to SUCCESS
712 x_return_status := FND_API.G_RET_STS_SUCCESS;
713
714
715
716 /*
717 -- translate display date format to canonical date
718 if ( substrb(p_key_type_code, 4, 1) = 'D' )then
719
720 l_value := displayDT_to_canonical(p_value);
721
722
723 if ( l_value is NULL ) then
724 RAISE IEM_INVALID_DATE_FORMAT;
725 end if;
726 else
727
728 l_value := p_value;
729 end if;
730 */
731
732
733 SELECT IEM_ROUTE_RULES_s1.nextval
734 INTO l_seq_id
735 FROM dual;
736
737
738
739 INSERT INTO IEM_ROUTE_RULES
740 (
741
742 ROUTE_RULE_ID,
743 ROUTE_ID,
744 KEY_TYPE_CODE,
745 OPERATOR_TYPE_CODE,
746 VALUE,
747 ATTRIBUTE1,
748 ATTRIBUTE2,
749 ATTRIBUTE3,
750 ATTRIBUTE4,
751 ATTRIBUTE5,
752
753 ATTRIBUTE6,
754 ATTRIBUTE7,
755
756 ATTRIBUTE8,
757 ATTRIBUTE9,
758 ATTRIBUTE10,
759 ATTRIBUTE11,
760 ATTRIBUTE12,
761 ATTRIBUTE13,
762 ATTRIBUTE14,
763 ATTRIBUTE15,
764 ATTRIBUTE_CATEGORY,
765 CREATED_BY,
766 CREATION_DATE,
767
768 LAST_UPDATED_BY,
769
770 LAST_UPDATE_DATE,
771 LAST_UPDATE_LOGIN
772 )
773 VALUES
774 (
775 l_seq_id,
776 p_route_id,
777 p_key_type_code,
778 p_operator_type_code,
779 p_value,
780 NULL,
781
782
783 NULL,
784 NULL,
785 NULL,
786 NULL,
787 NULL,
788 NULL,
789 NULL,
790 NULL,
791 NULL,
792 NULL,
793 NULL,
794 NULL,
795 NULL,
796
797
798 NULL,
799 NULL,
800 decode(G_created_updated_by,null,-1,G_created_updated_by),
801 sysdate,
802 decode(G_created_updated_by,null,-1,G_created_updated_by),
803 sysdate,
804 decode(G_LAST_UPDATE_LOGIN,null,-1,G_LAST_UPDATE_LOGIN)
805 );
806
807 -- Standard Check Of p_commit.
808 IF FND_API.To_Boolean(p_commit) THEN
809
810 COMMIT WORK;
811
812 END IF;
813
814 -- Standard callto get message count and if count is 1, get message info.
815 FND_MSG_PUB.Count_And_Get
816 ( p_count => x_msg_count,
817 p_data => x_msg_data
818 );
819
820 EXCEPTION
821
822
823
824 WHEN FND_API.G_EXC_ERROR THEN
825 ROLLBACK TO create_item_route_rules_PVT;
826
827 x_return_status := FND_API.G_RET_STS_ERROR ;
828 FND_MSG_PUB.Count_And_Get
829 ( p_count => x_msg_count,
830 p_data => x_msg_data
831 );
832
833 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
834 ROLLBACK TO create_item_route_rules_PVT;
835 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
836 FND_MSG_PUB.Count_And_Get
837
838 ( p_count => x_msg_count,
839 p_data => x_msg_data
840 );
841
842
843 WHEN OTHERS THEN
844 ROLLBACK TO create_item_route_rules_PVT;
845 x_return_status := FND_API.G_RET_STS_ERROR;
846 IF FND_MSG_PUB.Check_Msg_Level
847 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
848 THEN
849 FND_MSG_PUB.Add_Exc_Msg
850 ( G_PKG_NAME ,
851
852 l_api_name
853 );
854 END IF;
855 FND_MSG_PUB.Count_And_Get
856
857 ( p_count => x_msg_count ,
858 p_data => x_msg_data
859 );
860 END create_item_route_rules;
861
862
863
864 PROCEDURE create_item_account_routes (
865 p_api_version_number IN NUMBER,
866 p_init_msg_list IN VARCHAR2 := null,
867 p_commit IN VARCHAR2 := null,
868 p_email_account_id IN NUMBER,
869 p_route_id IN NUMBER,
870 p_destination_group_id IN NUMBER,
871 p_default_grp_id IN NUMBER,
872 p_enabled_flag IN VARCHAR2,
873 p_priority IN NUMBER,
874 x_return_status OUT NOCOPY VARCHAR2,
875 x_msg_count OUT NOCOPY NUMBER,
876 x_msg_data OUT NOCOPY VARCHAR2
877
878 ) is
879 l_api_name VARCHAR2(255):='create_item_account_routes';
880 l_api_version_number NUMBER:=1.0;
881 l_seq_id number;
882
883 BEGIN
884
885 -- Standard Start of API savepoint
886 SAVEPOINT create_item_acct_routes_PVT;
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
892 l_api_name,
893 G_PKG_NAME)
894 THEN
895 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
896 END IF;
897
898 -- Initialize message list if p_init_msg_list is set to TRUE.
899
900 IF FND_API.to_Boolean( p_init_msg_list ) THEN
901 FND_MSG_PUB.initialize;
902 END IF;
903
904 -- Initialize API return status to SUCCESS
905
906 x_return_status := FND_API.G_RET_STS_SUCCESS;
907
908 --actual API begins here
909 SELECT IEM_ACCOUNT_ROUTES_s1.nextval
910 INTO l_seq_id
911 FROM dual;
912
913 INSERT INTO IEM_ACCOUNT_ROUTES
914
915 (
916 ROUTE_ID,
917 EMAIL_ACCOUNT_ID,
918 ACCOUNT_ROUTE_ID,
919
920 DESTINATION_GROUP_ID,
921 DEFAULT_GROUP_ID,
922 ENABLED_FLAG,
923 PRIORITY,
924 ATTRIBUTE1,
925 ATTRIBUTE2,
926 ATTRIBUTE3,
927 ATTRIBUTE4,
928 ATTRIBUTE5,
929
930 ATTRIBUTE6,
931 ATTRIBUTE7,
932 ATTRIBUTE8,
933
934 ATTRIBUTE9,
935 ATTRIBUTE10,
936 ATTRIBUTE11,
937 ATTRIBUTE12,
938 ATTRIBUTE13,
939 ATTRIBUTE14,
940 ATTRIBUTE15,
941 ATTRIBUTE_CATEGORY,
942 CREATED_BY,
943 CREATION_DATE,
944
945 LAST_UPDATED_BY,
946 LAST_UPDATE_DATE,
947
948 LAST_UPDATE_LOGIN
949 )
950 VALUES
951 (
952 p_route_id,
953 p_email_account_id,
954 l_seq_id,
955 p_destination_group_id,
956 p_default_grp_id,
957 p_enabled_flag,
958
959 p_priority,
960
961 NULL,
962 NULL,
963 NULL,
964 NULL,
965 NULL,
966 NULL,
967 NULL,
968 NULL,
969 NULL,
970 NULL,
971 NULL,
972 NULL,
973
974
975 NULL,
976 NULL,
977 NULL,
978 NULL,
979 decode(G_created_updated_by,null,-1,G_created_updated_by),
980 sysdate,
981 decode(G_created_updated_by,null,-1,G_created_updated_by),
982 sysdate,
983 decode(G_LAST_UPDATE_LOGIN,null,-1,G_LAST_UPDATE_LOGIN)
984 );
985
986 -- Standard Check Of p_commit.
987
988
989 IF FND_API.To_Boolean(p_commit) THEN
990 COMMIT WORK;
991 END IF;
992
993 -- Standard callto get message count and if count is 1, get message info.
994 FND_MSG_PUB.Count_And_Get
995 ( p_count => x_msg_count,
996 p_data => x_msg_data
997 );
998
999 EXCEPTION
1000 WHEN FND_API.G_EXC_ERROR THEN
1001
1002 ROLLBACK TO create_item_acct_routes_PVT;
1003
1004 x_return_status := FND_API.G_RET_STS_ERROR ;
1005 FND_MSG_PUB.Count_And_Get
1006 ( p_count => x_msg_count,
1007 p_data => x_msg_data
1008 );
1009
1010 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1011 ROLLBACK TO create_item_acct_routes_PVT;
1012 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1013 FND_MSG_PUB.Count_And_Get
1014 ( p_count => x_msg_count,
1015
1016 p_data => x_msg_data
1017 );
1018
1019
1020 WHEN OTHERS THEN
1021 ROLLBACK TO create_item_acct_routes_PVT;
1022 x_return_status := FND_API.G_RET_STS_ERROR;
1023 IF FND_MSG_PUB.Check_Msg_Level
1024 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1025 THEN
1026 FND_MSG_PUB.Add_Exc_Msg
1027 ( G_PKG_NAME ,
1028 l_api_name
1029
1030 );
1031 END IF;
1032 FND_MSG_PUB.Count_And_Get
1033
1034 ( p_count => x_msg_count ,
1035 p_data => x_msg_data
1036 );
1037
1038 END create_item_account_routes;
1039
1040 --update iem_routes, update iem_route_rules, insert iem_route_rules
1041 PROCEDURE update_item_wrap (p_api_version_number IN NUMBER,
1042 p_init_msg_list IN VARCHAR2 := null,
1043 p_commit IN VARCHAR2 := null,
1044 p_route_id IN NUMBER,
1045 p_name IN VARCHAR2:= null,
1046 p_ruling_chain IN VARCHAR2:= null,
1047 p_description IN VARCHAR2:= null,
1048 p_procedure_name IN VARCHAR2:= null,
1049 p_all_emails IN VARCHAR2:= null,
1050 --below is the data for update
1051 p_update_rule_ids_tbl IN jtf_varchar2_Table_100,
1052 p_update_rule_keys_tbl IN jtf_varchar2_Table_100,
1053 p_update_rule_operators_tbl IN jtf_varchar2_Table_100,
1054 p_update_rule_values_tbl IN jtf_varchar2_Table_300,
1055 --below is the data for insert
1056 p_new_rule_keys_tbl IN jtf_varchar2_Table_100,
1057 p_new_rule_operators_tbl IN jtf_varchar2_Table_100,
1058 p_new_rule_values_tbl IN jtf_varchar2_Table_300,
1059 --below is the data to be removed
1060 p_remove_rule_ids_tbl IN jtf_varchar2_Table_100,
1061 x_return_status OUT NOCOPY VARCHAR2,
1062 x_msg_count OUT NOCOPY NUMBER,
1063 x_msg_data OUT NOCOPY VARCHAR2 )is
1064
1065 l_api_name VARCHAR2(255):='update_item_wrap';
1066 l_api_version_number NUMBER:=1.0;
1067
1068 l_return_status VARCHAR2(20) := FND_API.G_RET_STS_SUCCESS;
1069 l_msg_count NUMBER := 0;
1070 l_msg_data VARCHAR2(2000);
1071
1072 IEM_NO_ROUTE_UPDATE EXCEPTION;
1073 IEM_NO_RULE_UPDATE EXCEPTION;
1074
1075 IEM_RULE_NOT_DELETED EXCEPTION;
1076 IEM_ROUTE_RULE_NOT_CREATED EXCEPTION;
1077 IEM_ADMIN_ROUTE_NO_RULE ExcePTION;
1078 l_IEM_FAIL_TO_CALL EXCEPTION;
1079
1080 l_route NUMBER;
1081 l_rule_count NUMBER;
1082 l_proc_name VARCHAR2(256);
1083 l_return_type VARCHAR2(30);
1084 l_description VARCHAR2(256);
1085 BEGIN
1086 -- Standard Start of API savepoint
1087 SAVEPOINT update_item_wrap;
1088
1089 -- Standard call to check for call compatibility.
1090 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
1091
1092 p_api_version_number,
1093 l_api_name,
1094 G_PKG_NAME)
1095 THEN
1096 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1097
1098 END IF;
1099
1100 -- Initialize message list if p_init_msg_list is set to TRUE.
1101 IF FND_API.to_Boolean( p_init_msg_list )
1102 THEN
1103 FND_MSG_PUB.initialize;
1104 END IF;
1105
1106
1107 -- Initialize API return status to SUCCESS
1108 x_return_status := FND_API.G_RET_STS_SUCCESS;
1109
1110 --API Body
1111
1112
1113 --check if the route_id exist before update
1114 select count(*) into l_route from iem_routes where route_id = p_route_id;
1115
1116 if l_route < 1 then
1117 raise IEM_NO_ROUTE_UPDATE;
1118 end if;
1119
1120 --Dynamic route validation
1121 if ( p_ruling_chain = 'DYNAMIC' ) then
1122 l_proc_name := LTRIM(RTRIM( p_procedure_name ) );
1123 l_return_type := p_update_rule_keys_tbl(1);
1124 --validation goes here
1125 else
1126 l_proc_name := FND_API.G_MISS_CHAR;
1127 l_return_type := FND_API.G_MISS_CHAR;
1128 end if;
1129
1130 --update iem_routes table
1131 if p_description is null then
1132 l_description := FND_API.G_MISS_CHAR;
1133 elsif l_description = FND_API.G_MISS_CHAR then
1134 l_description := null;
1135 else
1136 l_description := p_description;
1137 end if;
1138 iem_route_pvt.update_item_route(
1139 p_api_version_number => l_api_version_number,
1140 p_init_msg_list => FND_API.G_FALSE,
1141 p_commit => FND_API.G_FALSE,
1142 p_route_id => p_route_id,
1143 p_name => p_name,
1144 p_all_emails => p_all_emails,
1145 p_description =>l_description,
1146 p_ruling_chain =>p_ruling_chain,
1147 p_proc_name => l_proc_name,
1148 p_return_type => l_return_type,
1149 x_return_status => l_return_status,
1150 x_msg_count => l_msg_count,
1151 x_msg_data => l_msg_data);
1152
1153
1154 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
1155 raise l_IEM_FAIL_TO_CALL;
1156 end if;
1157
1158
1159 --update iem_route_rules table
1160 if ( p_update_rule_ids_tbl.count <>0 ) then
1161
1162 FOR i IN p_update_rule_ids_tbl.FIRST..p_update_rule_ids_tbl.LAST loop
1163 iem_route_pvt.update_item_rule(p_api_version_number => l_api_version_number,
1164 p_init_msg_list => FND_API.G_FALSE,
1165 p_commit => FND_API.G_FALSE,
1166 p_route_rule_id => p_update_rule_ids_tbl(i),
1167 p_key_type_code =>p_update_rule_keys_tbl(i),
1168 p_operator_type_code =>p_update_rule_operators_tbl(i),
1169 p_value => p_update_rule_values_tbl(i),
1170
1171 x_return_status => l_return_status,
1172 x_msg_count => l_msg_count,
1173 x_msg_data => l_msg_data);
1174
1175 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
1176 raise IEM_NO_RULE_UPDATE;
1177 end if;
1178 end loop;
1179 end if;
1180
1181
1182 -- update by deleting rules from iem_route_rules table
1183 if ( p_remove_rule_ids_tbl.count <> 0 ) then
1184 FORALL i IN p_remove_rule_ids_tbl.FIRST..p_remove_rule_ids_tbl.LAST
1185 DELETE
1186 FROM IEM_ROUTE_RULES
1187 WHERE route_rule_id = p_remove_rule_ids_tbl(i);
1188
1189 if SQL%NOTFOUND then
1190 raise IEM_RULE_NOT_DELETED;
1191 end if;
1192 end if;
1193
1194 if ( p_new_rule_keys_tbl.count <> 0 ) then
1195 FOR i IN p_new_rule_keys_tbl.FIRST..p_new_rule_keys_tbl.LAST LOOP
1196 iem_route_pvt.create_item_route_rules (p_api_version_number=>p_api_version_number,
1197 p_init_msg_list => p_init_msg_list,
1198 p_commit => p_commit,
1199 p_route_id => p_route_id,
1200 p_key_type_code => p_new_rule_keys_tbl(i),
1201 p_operator_type_code => p_new_rule_operators_tbl(i),
1202
1203 p_value =>p_new_rule_values_tbl(i),
1204
1205 x_return_status =>l_return_status,
1206 x_msg_count => l_msg_count,
1207 x_msg_data => l_msg_data);
1208
1209 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
1210 raise IEM_ROUTE_RULE_NOT_CREATED;
1211 end if;
1212 END LOOP;
1213 end if;
1214
1215 -- check if exist at least one rule for each route
1216
1217 select count(*) into l_rule_count from iem_route_rules where route_id = p_route_id;
1218
1219 if p_all_emails<>'Y' then
1220 if l_rule_count < 1 then
1221 raise IEM_ADMIN_ROUTE_NO_RULE;
1222 end if;
1223 end if;
1224
1225 commit work;
1226
1227 EXCEPTION
1228 WHEN l_IEM_FAIL_TO_CALL THEN
1229 ROLLBACK TO update_item_wrap;
1230 -- FND_MESSAGE.SET_NAME('IEM','IEM_NO_ROUTE_UPDATE');
1231
1232 -- FND_MSG_PUB.Add;
1233 x_return_status := FND_API.G_RET_STS_ERROR ;
1234 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1235
1236 WHEN IEM_NO_ROUTE_UPDATE THEN
1237 ROLLBACK TO update_item_wrap;
1238 FND_MESSAGE.SET_NAME('IEM','IEM_NO_ROUTE_UPDATE');
1239
1240 FND_MSG_PUB.Add;
1241 x_return_status := FND_API.G_RET_STS_ERROR ;
1242 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1243 WHEN IEM_NO_RULE_UPDATE THEN
1244 ROLLBACK TO update_item_wrap;
1245 FND_MESSAGE.SET_NAME('IEM','IEM_NO_RULE_UPDATE');
1246 FND_MSG_PUB.Add;
1247 x_return_status := FND_API.G_RET_STS_ERROR ;
1248 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1249
1250 WHEN IEM_RULE_NOT_DELETED THEN
1251
1252 ROLLBACK TO update_item_wrap;
1253 FND_MESSAGE.SET_NAME('IEM','IEM_RULE_NOT_DELETED');
1254 FND_MSG_PUB.Add;
1255 x_return_status := FND_API.G_RET_STS_ERROR ;
1256
1257 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1258
1259 WHEN IEM_ROUTE_RULE_NOT_CREATED THEN
1260 ROLLBACK TO update_item_wrap;
1261 FND_MESSAGE.SET_NAME('IEM','IEM_ROUTE_RULE_NOT_CREATED');
1262 FND_MSG_PUB.Add;
1263 x_return_status := FND_API.G_RET_STS_ERROR ;
1264 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1265
1266
1267 WHEN IEM_ADMIN_ROUTE_NO_RULE THEN
1268 ROLLBACK TO update_item_wrap;
1269 FND_MESSAGE.SET_NAME('IEM','IEM_ADMIN_ROUTE_NO_RULE');
1270 FND_MSG_PUB.Add;
1271
1272 x_return_status := FND_API.G_RET_STS_ERROR ;
1273 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1274
1275 WHEN FND_API.G_EXC_ERROR THEN
1276 ROLLBACK TO update_item_wrap;
1277 x_return_status := FND_API.G_RET_STS_ERROR ;
1278 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,p_data => x_msg_data);
1279
1280
1281 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1282 ROLLBACK TO update_item_wrap;
1283 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1284 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1285
1286
1287 WHEN OTHERS THEN
1288 ROLLBACK TO update_item_wrap;
1289 x_return_status := FND_API.G_RET_STS_ERROR;
1290 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1291 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME , l_api_name);
1292 END IF;
1293
1294
1295 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count, p_data => x_msg_data );
1296
1297 END update_item_wrap;
1298
1299 PROCEDURE update_item_route (
1300 p_api_version_number IN NUMBER,
1301 p_init_msg_list IN VARCHAR2 := null,
1302 p_commit IN VARCHAR2 := null,
1303 p_route_id IN NUMBER,
1304 p_name IN VARCHAR2:= null,
1305 p_description IN VARCHAR2:= null,
1306 p_all_emails IN VARCHAR2:= null,
1307 p_proc_name IN VARCHAR2:= null,
1308 p_return_type IN VARCHAR2:= null,
1309 p_ruling_chain IN VARCHAR2:= null,
1310 x_return_status OUT NOCOPY VARCHAR2,
1311 x_msg_count OUT NOCOPY NUMBER,
1312 x_msg_data OUT NOCOPY VARCHAR2
1313 ) is
1314 l_api_name VARCHAR2(255):='update_item_route';
1315 l_api_version_number NUMBER:=1.0;
1316 l_return_status VARCHAR2(20) := FND_API.G_RET_STS_SUCCESS;
1317 l_msg_count NUMBER := 0;
1318 l_msg_data VARCHAR2(2000);
1319 l_proc_name VARCHAR2(256);
1320 l_name_count NUMBER;
1321 IEM_ADMIN_ROUTE_DUP_NAME EXCEPTION;
1322 IEM_ADMIN_ROUTE_NO_PROC EXCEPTION;
1323 l_IEM_INVALID_PROCEDURE EXCEPTION;
1324 BEGIN
1325
1326 -- Standard Start of API savepoint
1327 SAVEPOINT update_item_route;
1328
1329 -- Standard call to check for call compatibility.
1330 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
1331 p_api_version_number,
1332 l_api_name,
1333 G_PKG_NAME)
1334 THEN
1335
1336 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1337 END IF;
1338
1339
1340 -- Initialize message list if p_init_msg_list is set to TRUE.
1341 IF FND_API.to_Boolean( p_init_msg_list )
1342 THEN
1343 FND_MSG_PUB.initialize;
1344 END IF;
1345
1346 -- Initialize API return status to SUCCESS
1347 x_return_status := FND_API.G_RET_STS_SUCCESS;
1348
1349 --check duplicate value for attribute Name
1350
1351 select count(*) into l_name_count from iem_routes where UPPER(name) = UPPER(p_name) and route_id <> p_route_id;
1352
1353
1354 if l_name_count > 0 then
1355 raise IEM_ADMIN_ROUTE_DUP_NAME;
1356 end if;
1357
1358 if p_ruling_chain = 'DYNAMIC' then
1359 if ( p_proc_name is null ) then
1360 raise IEM_ADMIN_ROUTE_NO_PROC;
1361 else
1362 l_proc_name := LTRIM(RTRIM(p_proc_name));
1363 if ( l_proc_name = '') then
1364 raise IEM_ADMIN_ROUTE_NO_PROC;
1365
1366 else
1367 --validation goes here.
1368 IEM_ROUTE_RUN_PROC_PVT.validProcedure(
1369 p_api_version_number => P_Api_Version_Number,
1370 p_init_msg_list => FND_API.G_FALSE,
1371 p_commit => P_Commit,
1372 p_ProcName => l_proc_name,
1373 p_return_type => p_return_type,
1374 x_return_status => l_return_status,
1375 x_msg_count => l_msg_count,
1376 x_msg_data => l_msg_data
1377 );
1378 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
1379 raise l_IEM_INVALID_PROCEDURE;
1380 end if;
1381 end if;
1382 end if;
1383
1384 end if;
1385
1386 update IEM_ROUTES
1387 set
1388 name=decode(p_name,null,name,p_name),
1389 description=decode(p_description,FND_API.G_MISS_CHAR,null,null,description,p_description),
1390 boolean_type_code=decode(p_ruling_chain,null,boolean_type_code,p_ruling_chain),
1391 procedure_name=decode(l_proc_name,FND_API.G_MISS_CHAR,null,null,procedure_name,l_proc_name),
1392 all_email=decode(p_all_emails,FND_API.G_MISS_CHAR,null,null,all_email,p_all_emails),
1393 LAST_UPDATED_BY = decode(G_created_updated_by,null,-1,G_created_updated_by),
1394 LAST_UPDATE_DATE = sysdate,
1395 LAST_UPDATE_LOGIN = decode(G_LAST_UPDATE_LOGIN,null,-1,G_LAST_UPDATE_LOGIN)
1396
1397 where route_id=p_route_id;
1398
1399 -- Standard Check Of p_commit.
1400 IF FND_API.To_Boolean(p_commit) THEN
1401 COMMIT WORK;
1402 END IF;
1403
1404 -- Standard callto get message count and if count is 1, get message info.
1405 FND_MSG_PUB.Count_And_Get
1406 ( p_count => x_msg_count,
1407 p_data => x_msg_data
1408 );
1409
1410
1411 EXCEPTION
1412 WHEN l_IEM_INVALID_PROCEDURE THEN
1413 ROLLBACK TO update_item_route;
1414 x_return_status := FND_API.G_RET_STS_ERROR ;
1415 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1416
1417 WHEN IEM_ADMIN_ROUTE_DUP_NAME THEN
1418 ROLLBACK TO update_item_route;
1419 FND_MESSAGE.SET_NAME('IEM','IEM_ADMIN_ROUTE_DUP_NAME');
1420 FND_MSG_PUB.Add;
1421 x_return_status := FND_API.G_RET_STS_ERROR ;
1422 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1423
1424 WHEN IEM_ADMIN_ROUTE_NO_PROC THEN
1425 ROLLBACK TO update_item_route;
1426 FND_MESSAGE.SET_NAME('IEM','IEM_ADMIN_ROUTE_NO_PROC');
1427 FND_MSG_PUB.Add;
1428 x_return_status := FND_API.G_RET_STS_ERROR ;
1429 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1430
1431 WHEN FND_API.G_EXC_ERROR THEN
1432 ROLLBACK TO update_item_route;
1433 x_return_status := FND_API.G_RET_STS_ERROR ;
1434 FND_MSG_PUB.Count_And_Get
1435
1436
1437 ( p_count => x_msg_count,
1438 p_data => x_msg_data
1439 );
1440
1441 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1442 ROLLBACK TO update_item_route;
1443 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1444 FND_MSG_PUB.Count_And_Get
1445 ( p_count => x_msg_count,
1446 p_data => x_msg_data
1447 );
1448
1449
1450 WHEN OTHERS THEN
1451
1452 ROLLBACK TO update_item_route;
1453 x_return_status := FND_API.G_RET_STS_ERROR;
1454 IF FND_MSG_PUB.Check_Msg_Level
1455 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1456 THEN
1457 FND_MSG_PUB.Add_Exc_Msg
1458 ( G_PKG_NAME ,
1459 l_api_name
1460 );
1461 END IF;
1462 FND_MSG_PUB.Count_And_Get
1463
1464 ( p_count => x_msg_count ,
1465 p_data => x_msg_data
1466
1467 );
1468
1469 END update_item_route;
1470
1471
1472 PROCEDURE update_item_rule
1473 (p_api_version_number IN NUMBER,
1474 p_init_msg_list IN VARCHAR2 := null,
1475 p_commit IN VARCHAR2 := null,
1476 p_route_rule_id IN NUMBER := null,
1477 p_key_type_code IN VARCHAR2:= null,
1478 p_operator_type_code IN VARCHAR2:= null,
1479 p_value IN VARCHAR2:= null,
1480 x_return_status OUT NOCOPY VARCHAR2,
1481 x_msg_count OUT NOCOPY NUMBER,
1482 x_msg_data OUT NOCOPY VARCHAR2
1483 ) is
1484 l_api_name VARCHAR2(255):='update_item_rule';
1485 l_api_version_number NUMBER:=1.0;
1486
1487 l_rule NUMBER;
1488
1489 IEM_NO_RULE_UPDATE EXCEPTION;
1490 --IEM_INVALID_DATE_FORMAT EXCEPTION;
1491 BEGIN
1492 -- Standard Start of API savepoint
1493
1494 SAVEPOINT update_item_rule;
1495 -- Standard call to check for call compatibility.
1496 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
1497 p_api_version_number,
1498 l_api_name,
1499 G_PKG_NAME)
1500 THEN
1501 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1502
1503 END IF;
1504
1505 -- Initialize message list if p_init_msg_list is set to TRUE.
1506 IF FND_API.to_Boolean( p_init_msg_list )
1507 THEN
1508
1509 FND_MSG_PUB.initialize;
1510 END IF;
1511 -- Initialize API return status to SUCCESS
1512 x_return_status := FND_API.G_RET_STS_SUCCESS;
1513
1514 -- check if the route_id exist in iem_routes
1515 select count(*) into l_rule from iem_route_rules
1516
1517 where route_rule_id = p_route_rule_id;
1518
1519 if l_rule < 1 then
1520 raise IEM_NO_RULE_UPDATE;
1521 end if;
1522
1523
1524 /*
1525 -- translate display date format to canonical date
1526 if ( substrb(p_key_type_code, 4, 1) = 'D' )then
1527
1528 l_value := displayDT_to_canonical(p_value);
1529
1530
1531 if ( l_value is NULL ) then
1532 RAISE IEM_INVALID_DATE_FORMAT;
1533 end if;
1534 else
1535 l_value := p_value;
1536 end if;
1537 */
1538
1539
1540
1541 update IEM_ROUTE_RULES
1542 set
1543 key_type_code=decode(p_key_type_code,null,key_type_code,p_key_type_code),
1544 operator_type_code=decode(p_operator_type_code,null,operator_type_code,p_operator_type_code),
1545 value=decode(p_value,null,value,p_value),
1546 LAST_UPDATED_BY = decode(G_created_updated_by,null,-1,G_created_updated_by),
1547 LAST_UPDATE_DATE = sysdate,
1548 LAST_UPDATE_LOGIN = decode(G_LAST_UPDATE_LOGIN,null,-1,G_LAST_UPDATE_LOGIN)
1549 where route_rule_id=p_route_rule_id;
1550
1551
1552
1553 -- Standard Check Of p_commit.
1554 IF FND_API.To_Boolean(p_commit) THEN
1555 COMMIT WORK;
1556 END IF;
1557
1558 -- Standard callto get message count and if count is 1, get message info.
1559 FND_MSG_PUB.Count_And_Get
1560 ( p_count => x_msg_count,
1561 p_data => x_msg_data
1562 );
1563 EXCEPTION
1564 WHEN IEM_NO_RULE_UPDATE THEN
1565 ROLLBACK TO update_item_rule;
1566 FND_MESSAGE.SET_NAME('IEM','IEM_NO_RULE_UPDATE');
1567
1568 FND_MSG_PUB.Add;
1569 x_return_status := FND_API.G_RET_STS_ERROR ;
1570 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1571
1572 /*
1573 WHEN IEM_INVALID_DATE_FORMAT THEN
1574 ROLLBACK TO update_item_rule;
1575 FND_MESSAGE.SET_NAME('IEM','IEM_INVALID_DATE_FORMAT');
1576 FND_MSG_PUB.Add;
1577 x_return_status := FND_API.G_RET_STS_ERROR ;
1578 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1579 */
1580 WHEN FND_API.G_EXC_ERROR THEN
1581 ROLLBACK TO update_item_rule;
1582
1583 x_return_status := FND_API.G_RET_STS_ERROR ;
1584 FND_MSG_PUB.Count_And_Get
1585
1586 ( p_count => x_msg_count,
1587 p_data => x_msg_data
1588 );
1589 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1590 ROLLBACK TO update_item_rule;
1591 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1592 FND_MSG_PUB.Count_And_Get
1593 ( p_count => x_msg_count,
1594 p_data => x_msg_data
1595 );
1596 WHEN OTHERS THEN
1597
1598 ROLLBACK TO update_item_rule;
1599
1600 x_return_status := FND_API.G_RET_STS_ERROR;
1601 IF FND_MSG_PUB.Check_Msg_Level
1602 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1603 THEN
1604 FND_MSG_PUB.Add_Exc_Msg
1605 ( G_PKG_NAME ,
1606 l_api_name
1607 );
1608 END IF;
1609 FND_MSG_PUB.Count_And_Get
1610 ( p_count => x_msg_count ,
1611 p_data => x_msg_data
1612
1613
1614 );
1615
1616 END update_item_rule;
1617
1618
1619
1620 PROCEDURE create_wrap_account_routes (
1621 p_api_version_number IN NUMBER,
1622 p_init_msg_list IN VARCHAR2 := null,
1623 p_commit IN VARCHAR2 := null,
1624 p_email_account_id IN NUMBER,
1625 p_route_id IN NUMBER,
1626 p_destination_group_id IN NUMBER,
1627 p_default_grp_id IN NUMBER,
1628 p_enabled_flag IN VARCHAR2,
1629 p_priority IN NUMBER,
1630 x_return_status OUT NOCOPY VARCHAR2,
1631 x_msg_count OUT NOCOPY NUMBER,
1632 x_msg_data OUT NOCOPY VARCHAR2
1633 ) is
1634 l_api_name VARCHAR2(255):='create_item_account_routes';
1635 l_api_version_number NUMBER:=1.0;
1636
1637 l_route number;
1638
1639 l_account number;
1640
1641
1642 l_return_status VARCHAR2(20) := FND_API.G_RET_STS_SUCCESS;
1643 l_msg_count NUMBER := 0;
1644 l_msg_data VARCHAR2(2000);
1645
1646 IEM_ADMIN_ROUTE_NOT_EXIST EXCEPTION;
1647 IEM_ADMIN_ACCOUNT_NOT_EXIST EXCEPTION;
1648 IEM_ACCOUNT_ROUTE_NOT_UPDATED EXCEPTION;
1649 BEGIN
1650 -- Standard Start of API savepoint
1651 SAVEPOINT create_wrap_account_routes_PVT;
1652
1653 -- Standard call to check for call compatibility.
1654 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
1655
1656 p_api_version_number,
1657 l_api_name,
1658 G_PKG_NAME)
1659 THEN
1660 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1661 END IF;
1662
1663 -- Initialize message list if p_init_msg_list is set to TRUE.
1664 IF FND_API.to_Boolean( p_init_msg_list )
1665 THEN
1666
1667 FND_MSG_PUB.initialize;
1668 END IF;
1669
1670
1671 -- Initialize API return status to SUCCESS
1672 x_return_status := FND_API.G_RET_STS_SUCCESS;
1673
1674
1675 -- check if the route_id exist in iem_routes
1676 select count(*) into l_route from iem_routes
1677 where route_id = p_route_id;
1678
1679 if l_route < 1 then
1680
1681 raise IEM_ADMIN_ROUTE_NOT_EXIST;
1682 end if;
1683
1684 -- check if the account_id exist in iem_email_accounts
1685
1686 select count(*) into l_account from iem_mstemail_accounts
1687 where email_account_id = p_email_account_id;
1688
1689 if l_account < 1 then
1690 raise IEM_ADMIN_ACCOUNT_NOT_EXIST;
1691 end if;
1692
1693
1694
1695 iem_route_pvt.create_item_account_routes(
1696 p_api_version_number =>p_api_version_number,
1697 p_init_msg_list => p_init_msg_list,
1698 p_commit => p_commit,
1699
1700
1701 p_route_id =>p_route_id,
1702 p_email_account_id =>p_email_account_id,
1703 p_destination_group_id => p_destination_group_id,
1704 p_default_grp_id => p_default_grp_id,
1705 p_enabled_flag => p_enabled_flag,
1706 p_priority => p_priority,
1707
1708
1709 x_return_status =>l_return_status,
1710 x_msg_count => l_msg_count,
1711 x_msg_data => l_msg_data);
1712
1713 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
1714 raise IEM_ACCOUNT_ROUTE_NOT_UPDATED;
1715
1716 end if;
1717
1718
1719 --dbms_output.put_line('after insert : ');
1720 -- Standard Check Of p_commit.
1721 IF FND_API.To_Boolean(p_commit) THEN
1722
1723 COMMIT WORK;
1724 END IF;
1725 -- Standard callto get message count and if count is 1, get message info.
1726 FND_MSG_PUB.Count_And_Get
1727 ( p_count => x_msg_count,
1728 p_data => x_msg_data
1729 );
1730
1731
1732 EXCEPTION
1733 WHEN IEM_ADMIN_ROUTE_NOT_EXIST THEN
1734 ROLLBACK TO create_wrap_account_routes_PVT;
1735 FND_MESSAGE.SET_NAME('IEM','IEM_ADMIN_ROUTE_NOT_EXIST');
1736
1737 FND_MSG_PUB.Add;
1738 x_return_status := FND_API.G_RET_STS_ERROR ;
1739 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1740
1741 WHEN IEM_ADMIN_ACCOUNT_NOT_EXIST THEN
1742 ROLLBACK TO create_wrap_account_routes_PVT;
1743 FND_MESSAGE.SET_NAME('IEM','IEM_ADMIN_ACCOUNT_NOT_EXIST');
1744 FND_MSG_PUB.Add;
1745
1746 x_return_status := FND_API.G_RET_STS_ERROR ;
1747 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1748
1749 WHEN IEM_ACCOUNT_ROUTE_NOT_UPDATED THEN
1750
1751 ROLLBACK TO create_wrap_account_routes_PVT;
1752 FND_MESSAGE.SET_NAME('IEM','IEM_ACCOUNT_ROUTE_NOT_UPDATED');
1753 FND_MSG_PUB.Add;
1754 x_return_status := FND_API.G_RET_STS_ERROR ;
1755 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1756
1757 WHEN FND_API.G_EXC_ERROR THEN
1758 ROLLBACK TO create_wrap_account_routes_PVT;
1759 x_return_status := FND_API.G_RET_STS_ERROR ;
1760
1761 FND_MSG_PUB.Count_And_Get
1762 ( p_count => x_msg_count,
1763 p_data => x_msg_data
1764
1765 );
1766
1767 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1768 ROLLBACK TO create_wrap_account_routes_PVT;
1769 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1770 FND_MSG_PUB.Count_And_Get
1771 ( p_count => x_msg_count,
1772 p_data => x_msg_data
1773 );
1774
1775
1776 WHEN OTHERS THEN
1777 ROLLBACK TO create_wrap_account_routes_PVT;
1778
1779 x_return_status := FND_API.G_RET_STS_ERROR;
1780 IF FND_MSG_PUB.Check_Msg_Level
1781 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1782 THEN
1783 FND_MSG_PUB.Add_Exc_Msg
1784 ( G_PKG_NAME,
1785 l_api_name
1786 );
1787 END IF;
1788 FND_MSG_PUB.Count_And_Get
1789 ( p_count => x_msg_count,
1790
1791 p_data => x_msg_data
1792
1793 );
1794
1795 END create_wrap_account_routes;
1796
1797 PROCEDURE update_account_routes(p_api_version_number IN NUMBER,
1798 p_init_msg_list IN VARCHAR2 := null,
1799 p_commit IN VARCHAR2 := null,
1800 p_route_id IN NUMBER,
1801 p_email_account_id IN NUMBER,
1802 p_destination_grp_id IN VARCHAR2:= null,
1803 p_default_grp_id IN VARCHAR2:= null,
1804 p_enabled_flag IN VARCHAR2:= null,
1805 p_priority IN VARCHAR2:= null,
1806 x_return_status OUT NOCOPY VARCHAR2,
1807 x_msg_count OUT NOCOPY NUMBER,
1808 x_msg_data OUT NOCOPY VARCHAR2
1809 ) is
1810 l_api_name VARCHAR2(255):='update_account_routes';
1811 l_api_version_number NUMBER:=1.0;
1812 l_route_cnt NUMBER;
1813 l_acct_cnt NUMBER;
1814 l_LAST_UPDATED_BY NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('USER_ID')) ;
1815 l_LAST_UPDATE_DATE DATE:=SYSDATE;
1816 l_LAST_UPDATE_LOGIN NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('LOGIN_ ID')) ;
1817
1818 IEM_ADM_G_MISS_FOR_NOTNULL EXCEPTION;
1819
1820 BEGIN
1821 -- Standard Start of API savepoint
1822 SAVEPOINT update_account_routes_PVT;
1823 -- Standard call to check for call compatibility.
1824 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
1825 p_api_version_number,
1826 l_api_name,
1827 G_PKG_NAME)
1828 THEN
1829 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1830 END IF;
1831 -- Initialize message list if p_init_msg_list is set to TRUE.
1832
1833 IF FND_API.to_Boolean( p_init_msg_list )
1834
1835 THEN
1836 FND_MSG_PUB.initialize;
1837 END IF;
1838 -- Initialize API return status to SUCCESS
1839 x_return_status := FND_API.G_RET_STS_SUCCESS;
1840
1841 -- Check For Existing IEM Server Group
1842 IF p_route_id <> FND_API.G_MISS_NUM THEN
1843 Select count(*) into l_route_cnt from iem_routes
1844 where route_id=p_route_id;
1845
1846
1847 IF l_route_cnt = 0 then
1848 FND_MESSAGE.SET_NAME('IEM','IEM_ADMIN_ROUTE_NOT_EXIST');
1849
1850 APP_EXCEPTION.RAISE_EXCEPTION;
1851 END IF;
1852 END IF;
1853
1854 IF p_email_account_id <> FND_API.G_MISS_NUM THEN
1855 /*Check For Existing DB Server Group Id */
1856
1857 Select count(*) into l_acct_cnt from iem_mstemail_accounts
1858 where email_account_id=p_email_account_id;
1859
1860
1861 IF l_acct_cnt = 0 then
1862 FND_MESSAGE.SET_NAME('IEM','IEM_ADMIN_ACCOUNT_NOT_EXIST');
1863 APP_EXCEPTION.RAISE_EXCEPTION;
1864
1865 END IF;
1866 END IF;
1867
1868 if ( p_destination_grp_id = FND_API.G_MISS_CHAR ) Then
1869 raise IEM_ADM_G_MISS_FOR_NOTNULL;
1870 elsif ( p_default_grp_id = FND_API.G_MISS_CHAR ) then
1871 raise IEM_ADM_G_MISS_FOR_NOTNULL;
1872 elsif ( p_enabled_flag = FND_API.G_MISS_CHAR) then
1873 raise IEM_ADM_G_MISS_FOR_NOTNULL;
1874 elsif ( p_priority = FND_API.G_MISS_CHAR) then
1875 raise IEM_ADM_G_MISS_FOR_NOTNULL;
1876 end if;
1877
1878
1879 if ((p_email_account_id <> FND_API.G_MISS_NUM) and (p_route_id <> FND_API.G_MISS_NUM)) then
1880 update IEM_ACCOUNT_ROUTES
1881 set
1882 destination_group_id = decode(p_destination_grp_id,null,destination_group_id,p_destination_grp_id),
1883 default_group_id =decode(p_default_grp_id,null,default_group_id,p_default_grp_id),
1884 enabled_flag=decode(p_enabled_flag,null,enabled_flag,p_enabled_flag),
1885 priority=decode(p_priority,null,priority,p_priority),
1886 LAST_UPDATED_BY = decode(G_created_updated_by,null,-1,G_created_updated_by),
1887 LAST_UPDATE_DATE = sysdate,
1888 LAST_UPDATE_LOGIN = decode(G_LAST_UPDATE_LOGIN,null,-1,G_LAST_UPDATE_LOGIN)
1889 where route_id = p_route_id and email_account_id = p_email_account_id;
1890 end if;
1891
1892
1893
1894 -- Standard Check Of p_commit.
1895 IF FND_API.To_Boolean(p_commit) THEN
1896 COMMIT WORK;
1897
1898 END IF;
1899 -- Standard callto get message count and if count is 1, get message info.
1900 FND_MSG_PUB.Count_And_Get
1901 ( p_count => x_msg_count,
1902 p_data => x_msg_data
1903
1904 );
1905 EXCEPTION
1906 WHEN IEM_ADM_G_MISS_FOR_NOTNULL THEN
1907 ROLLBACK TO update_account_routes_PVT;
1908 FND_MESSAGE.SET_NAME('IEM','IEM_ADM_G_MISS_FOR_NOTNULL');
1909 FND_MSG_PUB.Add;
1910 x_return_status := FND_API.G_RET_STS_ERROR ;
1911 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1912 WHEN FND_API.G_EXC_ERROR THEN
1913 ROLLBACK TO update_account_routes_PVT;
1914 x_return_status := FND_API.G_RET_STS_ERROR ;
1915 FND_MSG_PUB.Count_And_Get
1916
1917 ( p_count => x_msg_count,
1918 p_data => x_msg_data
1919 );
1920 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1921 ROLLBACK TO update_account_routes_PVT;
1922 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1923
1924 FND_MSG_PUB.Count_And_Get
1925 ( p_count => x_msg_count,
1926 p_data => x_msg_data
1927 );
1928 WHEN OTHERS THEN
1929 ROLLBACK TO update_account_routes_PVT;
1930
1931 x_return_status := FND_API.G_RET_STS_ERROR;
1932 IF FND_MSG_PUB.Check_Msg_Level
1933 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1934 THEN
1935 FND_MSG_PUB.Add_Exc_Msg
1936 ( G_PKG_NAME ,
1937 l_api_name
1938
1939 );
1940 END IF;
1941 FND_MSG_PUB.Count_And_Get
1942 ( p_count => x_msg_count,
1943 p_data => x_msg_data
1944
1945 );
1946
1947 END update_account_routes;
1948
1949
1950
1951 PROCEDURE delete_acct_route_batch
1952 (p_api_version_number IN NUMBER,
1953 P_init_msg_list IN VARCHAR2 := null,
1954 p_commit IN VARCHAR2 := null,
1955 p_route_ids_tbl IN jtf_varchar2_Table_100,
1956 p_account_id IN NUMBER,
1957 x_return_status OUT NOCOPY VARCHAR2,
1958 x_msg_count OUT NOCOPY NUMBER,
1959 x_msg_data OUT NOCOPY VARCHAR2)
1960 IS
1961 i INTEGER;
1962 l_api_name varchar2(30):='delete_acct_route_batch';
1963 l_api_version_number number:=1.0;
1964
1965 IEM_ACCOUNT_ROUTE_NOT_DELETED EXCEPTION;
1966
1967 BEGIN
1968
1969 --Standard Savepoint
1970
1971 SAVEPOINT delete_acct_route_batch;
1972
1973 -- Standard call to check for call compatibility.
1974 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
1975 p_api_version_number,
1976 l_api_name,
1977 G_PKG_NAME)
1978 THEN
1979 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1980 END IF;
1981
1982
1983 --Initialize the message list if p_init_msg_list is set to TRUE
1984
1985 If FND_API.to_Boolean(p_init_msg_list) THEN
1986 FND_MSG_PUB.initialize;
1987 END IF;
1988
1989 --Initialize API status return
1990 x_return_status := FND_API.G_RET_STS_SUCCESS;
1991
1992 --Actual API starts here
1993 if ( p_route_ids_tbl.count <> 0 ) then
1994
1995 FOR i IN p_route_ids_tbl.FIRST..p_route_ids_tbl.LAST LOOP
1996
1997
1998
1999 -- update priority after delete an account_route
2000 Update iem_account_routes set priority=priority-1
2001 where email_account_id=p_account_id and priority > (Select priority from iem_account_routes
2002 where route_id=p_route_ids_tbl(i) and email_account_id=p_account_id);
2003
2004 DELETE
2005 FROM IEM_ACCOUNT_ROUTES
2006 WHERE route_id = p_route_ids_tbl(i) and email_account_id = p_account_id;
2007
2008 END LOOP;
2009 end if;
2010
2011
2012
2013 --if SQL%NOTFOUND then
2014 -- raise IEM_ACCOUNT_ROUTE_NOT_DELETED;
2015 --end if;
2016
2017 --Standard check of p_commit
2018 IF FND_API.to_Boolean(p_commit) THEN
2019 COMMIT WORK;
2020 END IF;
2021
2022
2023 EXCEPTION
2024 WHEN IEM_ACCOUNT_ROUTE_NOT_DELETED THEN
2025 ROLLBACK TO delete_acct_route_batch;
2026
2027
2028 x_return_status := FND_API.G_RET_STS_ERROR;
2029 FND_MESSAGE.SET_NAME('IEM', 'IEM_ACCOUNT_ROUTE_NOT_DELETED');
2030 FND_MSG_PUB.ADD;
2031 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
2032
2033 WHEN FND_API.G_EXC_ERROR THEN
2034 ROLLBACK TO delete_acct_route_batch;
2035 x_return_status := FND_API.G_RET_STS_ERROR ;
2036 FND_MSG_PUB.Count_And_Get
2037 ( p_count => x_msg_count,p_data => x_msg_data);
2038
2039 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2040
2041 ROLLBACK TO delete_acct_route_batch;
2042
2043 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2044 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,p_data => x_msg_data);
2045
2046 WHEN OTHERS THEN
2047 ROLLBACK TO delete_acct_route_batch;
2048 x_return_status := FND_API.G_RET_STS_ERROR;
2049 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2050 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME , l_api_name);
2051 END IF;
2052
2053 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count ,p_data => x_msg_data);
2054
2055
2056 END delete_acct_route_batch;
2057
2058
2059
2060 -- to update and delete new tuples in iem_account_routes
2061 PROCEDURE update_wrap_account_routes
2062 (p_api_version_number IN NUMBER,
2063 p_init_msg_list IN VARCHAR2 := null,
2064 p_commit IN VARCHAR2 := null,
2065 p_email_account_id IN NUMBER,
2066 p_route_ids_tbl IN jtf_varchar2_Table_100,
2067 p_upd_dest_ids_tbl IN jtf_varchar2_Table_100,
2068
2069 p_upd_default_ids_tbl IN jtf_varchar2_Table_100,
2070 p_upd_enable_flag_tbl IN jtf_varchar2_Table_100,
2071 --p_upd_priority_tbl IN jtf_varchar2_Table_100,
2072
2073 p_delete_route_ids_tbl IN jtf_varchar2_Table_100,
2074
2075 x_return_status OUT NOCOPY VARCHAR2,
2076 x_msg_count OUT NOCOPY NUMBER,
2077 x_msg_data OUT NOCOPY VARCHAR2
2078 ) is
2079 l_api_name VARCHAR2(255):='update_wrap_account_routes';
2080
2081 l_api_version_number NUMBER:=1.0;
2082
2083
2084 l_return_status VARCHAR2(20) := FND_API.G_RET_STS_SUCCESS;
2085
2086 l_msg_count NUMBER := 0;
2087 l_msg_data VARCHAR2(2000);
2088
2089 IEM_ACCOUNT_ROUTE_NOT_DELETED EXCEPTION;
2090 IEM_ACCOUNT_ROUTE_NOT_UPDATED EXCEPTION;
2091 BEGIN
2092 -- Standard Start of API savepoint
2093 SAVEPOINT update_wrap_acct_routes_1_PVT;
2094
2095 -- Standard call to check for call compatibility.
2096 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
2097 p_api_version_number,
2098 l_api_name,
2099 G_PKG_NAME)
2100
2101 THEN
2102 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2103 END IF;
2104
2105 -- Initialize message list if p_init_msg_list is set to TRUE.
2106 IF FND_API.to_Boolean( p_init_msg_list )
2107 THEN
2108
2109 FND_MSG_PUB.initialize;
2110 END IF;
2111 -- Initialize API return status to SUCCESS
2112 x_return_status := FND_API.G_RET_STS_SUCCESS;
2113
2114 -- update first
2115
2116 if ( p_route_ids_tbl.count <> 0 ) then
2117 FOR i IN p_route_ids_tbl.FIRST..p_route_ids_tbl.LAST LOOP
2118
2119 iem_route_pvt.update_account_routes (p_api_version_number =>p_api_version_number,
2120 p_init_msg_list => FND_API.G_FALSE,
2121 p_commit => FND_API.G_TRUE,
2122
2123
2124 p_route_id => p_route_ids_tbl(i),
2125 p_email_account_id => p_email_account_id,
2126 p_destination_grp_id => p_upd_dest_ids_tbl(i),
2127 p_default_grp_id =>p_upd_default_ids_tbl(i),
2128 p_enabled_flag => p_upd_enable_flag_tbl(i),
2129
2130
2131 x_return_status =>l_return_status,
2132 x_msg_count => l_msg_count,
2133 x_msg_data => l_msg_data);
2134
2135 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
2136
2137 raise IEM_ACCOUNT_ROUTE_NOT_UPDATED;
2138 end if;
2139 END LOOP;
2140 end if;
2141
2142 SAVEPOINT update_wrap_acct_routes_2_PVT;
2143
2144 if ( p_route_ids_tbl.count <> 0 ) then
2145
2146 -- FOR i IN p_route_ids_tbl.FIRST..p_route_ids_tbl.LAST LOOP
2147 iem_route_pvt.delete_acct_route_batch
2148 (p_api_version_number => p_api_version_number,
2149 P_init_msg_list => FND_API.G_FALSE,
2150
2151 p_commit => FND_API.G_TRUE,
2152 p_route_ids_tbl => p_delete_route_ids_tbl,
2153 p_account_id => p_email_account_id,
2154 x_return_status => l_return_status,
2155 x_msg_count => l_msg_count,
2156 x_msg_data => l_msg_data) ;
2157 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
2158 raise IEM_ACCOUNT_ROUTE_NOT_DELETED;
2159 end if;
2160
2161 -- END LOOP;
2162 end if;
2163 -- dbms_output.put_line('route_id : ' || p_route_id);
2164
2165 -- dbms_output.put_line('Destination_group_id: ' || p_email_account_id);
2166
2167
2168
2169
2170 --dbms_output.put_line('after insert : ');
2171 -- Standard Check Of p_commit.
2172 IF FND_API.To_Boolean(p_commit) THEN
2173 COMMIT WORK;
2174 END IF;
2175
2176 -- Standard callto get message count and if count is 1, get message info.
2177 FND_MSG_PUB.Count_And_Get
2178
2179 ( p_count => x_msg_count,
2180 p_data => x_msg_data
2181 );
2182
2183 EXCEPTION
2184
2185 WHEN IEM_ACCOUNT_ROUTE_NOT_UPDATED THEN
2186 ROLLBACK TO update_wrap_acct_routes_1_PVT;
2187 FND_MESSAGE.SET_NAME('IEM','IEM_ACCOUNT_ROUTE_NOT_UPDATED');
2188 FND_MSG_PUB.Add;
2189 x_return_status := FND_API.G_RET_STS_ERROR ;
2190
2191 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
2192
2193 WHEN IEM_ACCOUNT_ROUTE_NOT_DELETED THEN
2194 ROLLBACK TO update_wrap_acct_routes_2_PVT;
2195 FND_MESSAGE.SET_NAME('IEM','IEM_ACCOUNT_ROUTE_NOT_DELETED');
2196 FND_MSG_PUB.Add;
2197 x_return_status := FND_API.G_RET_STS_ERROR ;
2198 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
2199 WHEN FND_API.G_EXC_ERROR THEN
2200 ROLLBACK TO update_wrap_acct_routes_2_PVT;
2201 x_return_status := FND_API.G_RET_STS_ERROR ;
2202 FND_MSG_PUB.Count_And_Get
2203 ( p_count => x_msg_count,
2204 p_data => x_msg_data
2205
2206
2207 );
2208
2209 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2210 ROLLBACK TO update_wrap_acct_routes_2_PVT;
2211 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2212 FND_MSG_PUB.Count_And_Get
2213 ( p_count => x_msg_count,
2214 p_data => x_msg_data
2215 );
2216
2217 WHEN OTHERS THEN
2218 ROLLBACK TO update_wrap_acct_routes_2_PVT;
2219 x_return_status := FND_API.G_RET_STS_ERROR;
2220
2221
2222 IF FND_MSG_PUB.Check_Msg_Level
2223 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2224 THEN
2225 FND_MSG_PUB.Add_Exc_Msg
2226 ( G_PKG_NAME,
2227 l_api_name
2228 );
2229 END IF;
2230 FND_MSG_PUB.Count_And_Get
2231 ( p_count => x_msg_count,
2232 p_data => x_msg_data
2233 );
2234
2235
2236
2237 END update_wrap_account_routes;
2238 -- Enter further code below as specified in the Package spec.
2239
2240 END IEM_ROUTE_PVT; -- Package Body IEM_ROUTE_PVT