[Home] [Help]
PACKAGE BODY: APPS.IEM_EMAILPROC_HDL_PVT
Source
1 PACKAGE BODY IEM_EMAILPROC_HDL_PVT AS
2 /* $Header: iemvprob.pls 120.0 2005/06/02 14:08:12 appldev noship $ */
3 --
4 --
5 -- Purpose: Mantain IEM_EMAILPROCS, IEM_EMAILPROC_RULES, IEM_ACCOUNT_EMAILPROCS, IEM_ACTIONS, IEM_ACTION_DTLS
6 -- related operations
7 --
8 -- MODIFICATION HISTORY
9 -- Person Date Comments
10 -- Liang Xia 8/12/2002 Created
11 -- --------- ------ ------------------------------------------
12
13 -- Enter procedure, function bodies as shown below
14 G_PKG_NAME CONSTANT varchar2(30) :='IEM_EMAILPROC_HDL_PVT ';
15 G_ROUTE_ID varchar2(30) ;
16 G_EMAILPROC_ID varchar2(30);
17 G_created_updated_by NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('USER_ID')) ;
18
19 G_LAST_UPDATE_LOGIN NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('LOGIN_ ID') ) ;
20
21
22
23
24 --function to create cannonical function
25 FUNCTION displayDT_to_canonical ( displayDT IN VARCHAR2 )
26 return VARCHAR2
27 is
28 user_mask varchar2(265) := 'DD-MON-YYYY';
29
30 canonicalMask varchar2(265) := 'YYYYMMDD';
31 BEGIN
32 RETURN to_char( to_date( displayDT, user_mask), canonicalMask);
33 EXCEPTION
34
35 WHEN OTHERS THEN
36 RETURN (NULL);
37 END displayDT_to_canonical;
38
39
40
41
42
43 PROCEDURE create_item_account_emailprocs (
44 p_api_version_number IN NUMBER,
45 p_init_msg_list IN VARCHAR2 := null,
46 p_commit IN VARCHAR2 := null,
47 p_email_account_id IN NUMBER,
48 p_emailproc_id IN NUMBER,
49 p_enabled_flag IN VARCHAR2,
50 p_priority IN NUMBER,
51 x_return_status OUT NOCOPY VARCHAR2,
52 x_msg_count OUT NOCOPY NUMBER,
53 x_msg_data OUT NOCOPY VARCHAR2
54
55 ) is
56 l_api_name VARCHAR2(255):='create_item_account_routes';
57 l_api_version_number NUMBER:=1.0;
58 l_seq_id number;
59
60 BEGIN
61
62 -- Standard Start of API savepoint
63 SAVEPOINT create_item_acct_routes_PVT;
64
65 -- Standard call to check for call compatibility.
66 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
67 p_api_version_number,
68
69 l_api_name,
70 G_PKG_NAME)
71 THEN
72 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
73 END IF;
74
75 -- Initialize message list if p_init_msg_list is set to TRUE.
76
77 IF FND_API.to_Boolean( p_init_msg_list ) THEN
78 FND_MSG_PUB.initialize;
79 END IF;
80
81 -- Initialize API return status to SUCCESS
82
83 x_return_status := FND_API.G_RET_STS_SUCCESS;
84
85 --actual API begins here
86 SELECT IEM_ACCOUNT_EMAILPROCS_s1.nextval
87 INTO l_seq_id
88 FROM dual;
89
90 INSERT INTO IEM_ACCOUNT_EMAILPROCS
91 (
92 EMAILPROC_ID,
93 EMAIL_ACCOUNT_ID,
94 ACCOUNT_EMAILPROC_ID,
95 ENABLED_FLAG,
96 PRIORITY,
97 ATTRIBUTE1,
98 ATTRIBUTE2,
99 ATTRIBUTE3,
100 ATTRIBUTE4,
101 ATTRIBUTE5,
102 ATTRIBUTE6,
103 ATTRIBUTE7,
104 ATTRIBUTE8,
105 ATTRIBUTE9,
106 ATTRIBUTE10,
107 ATTRIBUTE11,
108 ATTRIBUTE12,
109 ATTRIBUTE13,
110 ATTRIBUTE14,
111 ATTRIBUTE15,
112 ATTRIBUTE_CATEGORY,
113 CREATED_BY,
114 CREATION_DATE,
115 LAST_UPDATED_BY,
116 LAST_UPDATE_DATE,
117 LAST_UPDATE_LOGIN
118 )
119 VALUES
120 (
121 p_emailproc_id,
122 p_email_account_id,
123 l_seq_id,
124 p_enabled_flag,
125 p_priority,
126 NULL,
127 NULL,
128 NULL,
129 NULL,
130 NULL,
131 NULL,
132 NULL,
133 NULL,
134 NULL,
135 NULL,
136 NULL,
137 NULL,
138 NULL,
139 NULL,
140 NULL,
141 NULL,
142 decode(G_created_updated_by,null,-1,G_created_updated_by),
143 sysdate,
144 decode(G_created_updated_by,null,-1,G_created_updated_by),
145 sysdate,
146 decode(G_LAST_UPDATE_LOGIN,null,-1,G_LAST_UPDATE_LOGIN)
147 );
148
149 -- Standard Check Of p_commit.
150
151
152 IF FND_API.To_Boolean(p_commit) THEN
153 COMMIT WORK;
154 END IF;
155
156 -- Standard callto get message count and if count is 1, get message info.
157 FND_MSG_PUB.Count_And_Get
158 ( p_count => x_msg_count,
159 p_data => x_msg_data
160 );
161
162 EXCEPTION
163 WHEN FND_API.G_EXC_ERROR THEN
164
165 ROLLBACK TO create_item_acct_routes_PVT;
166
167 x_return_status := FND_API.G_RET_STS_ERROR ;
168 FND_MSG_PUB.Count_And_Get
169 ( p_count => x_msg_count,
170 p_data => x_msg_data
171 );
172
173 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
174 ROLLBACK TO create_item_acct_routes_PVT;
175 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
176 FND_MSG_PUB.Count_And_Get
177 ( p_count => x_msg_count,
178
179 p_data => x_msg_data
180 );
181
182
183 WHEN OTHERS THEN
184 ROLLBACK TO create_item_acct_routes_PVT;
185 x_return_status := FND_API.G_RET_STS_ERROR;
186 IF FND_MSG_PUB.Check_Msg_Level
187 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
188 THEN
189 FND_MSG_PUB.Add_Exc_Msg
190 ( G_PKG_NAME ,
191 l_api_name
192
193 );
194 END IF;
195 FND_MSG_PUB.Count_And_Get
196
197 ( p_count => x_msg_count ,
198 p_data => x_msg_data
199 );
200
201 END create_item_account_emailprocs;
202
203
204 PROCEDURE update_item_emailproc (
205 p_api_version_number IN NUMBER,
206 p_init_msg_list IN VARCHAR2 := null,
207 p_commit IN VARCHAR2 := null,
208 p_emailproc_id IN NUMBER,
209 p_name IN VARCHAR2:= null,
210 p_description IN VARCHAR2:= null,
211 p_ruling_chain IN VARCHAR2:= null,
212 p_all_email IN VARCHAR2:= null,
213 p_rule_type IN VARCHAR2:= null,
214 x_return_status OUT NOCOPY VARCHAR2,
215 x_msg_count OUT NOCOPY NUMBER,
216 x_msg_data OUT NOCOPY VARCHAR2
217 ) is
218 l_api_name VARCHAR2(255):='update_item_emailproc';
219 l_api_version_number NUMBER:=1.0;
220 l_return_status VARCHAR2(20) := FND_API.G_RET_STS_SUCCESS;
221 l_msg_count NUMBER := 0;
222 l_msg_data VARCHAR2(2000);
223 l_proc_name VARCHAR2(256);
224 l_name_count NUMBER;
225 IEM_ADM_DUP_NAME EXCEPTION;
226 IEM_ADMIN_ROUTE_NO_PROC EXCEPTION;
227 l_IEM_INVALID_PROCEDURE EXCEPTION;
228 IEM_ADM_G_MISS_FOR_NOTNULL EXCEPTION;
229 BEGIN
230
231 -- Standard Start of API savepoint
232 SAVEPOINT update_item_route;
233
234 -- Standard call to check for call compatibility.
235 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
236 p_api_version_number,
237 l_api_name,
238 G_PKG_NAME)
239 THEN
240
241 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
242 END IF;
243
244
245 -- Initialize message list if p_init_msg_list is set to TRUE.
246 IF FND_API.to_Boolean( p_init_msg_list )
247 THEN
248 FND_MSG_PUB.initialize;
249 END IF;
250
251 -- Initialize API return status to SUCCESS
252 x_return_status := FND_API.G_RET_STS_SUCCESS;
253
254 --check duplicate value for attribute Name
255 select count(*) into l_name_count
256 from iem_emailprocs
257 where UPPER(name) = UPPER(p_name) and rule_type=p_rule_type and emailproc_id <> p_emailproc_id;
258
259 if l_name_count > 0 then
260 raise IEM_ADM_DUP_NAME;
261 end if;
262
263 /*
264 if p_ruling_chain = 'DYNAMIC' then
265 if ( p_proc_name is null ) then
266 raise IEM_ADMIN_ROUTE_NO_PROC;
267 else
268 l_proc_name := LTRIM(RTRIM(p_proc_name));
269 if ( l_proc_name = '') then
270 raise IEM_ADMIN_ROUTE_NO_PROC;
271
272 else
273 --validation goes here.
274 IEM_ROUTE_RUN_PROC_PVT.validProcedure(
275 p_api_version_number => P_Api_Version_Number,
276 p_init_msg_list => FND_API.G_FALSE,
277 p_commit => P_Commit,
278 p_ProcName => l_proc_name,
279 p_return_type => p_return_type,
280 x_return_status => l_return_status,
281 x_msg_count => l_msg_count,
282 x_msg_data => l_msg_data
283 );
284 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
285 raise l_IEM_INVALID_PROCEDURE;
286 end if;
287 end if;
288 end if;
289
290 end if;
291 */
292
293 if ( p_name = FND_API.G_MISS_CHAR ) then
294 raise IEM_ADM_G_MISS_FOR_NOTNULL;
295 elsif ( p_ruling_chain = FND_API.G_MISS_CHAR ) then
296 raise IEM_ADM_G_MISS_FOR_NOTNULL;
297 elsif ( p_all_email = FND_API.G_MISS_CHAR ) then
298 raise IEM_ADM_G_MISS_FOR_NOTNULL;
299 elsif ( p_rule_type = FND_API.G_MISS_CHAR ) then
300 raise IEM_ADM_G_MISS_FOR_NOTNULL;
301 end if;
302
303 update IEM_EMAILPROCS
304 set
305 name=decode(p_name,null,name,p_name),
306 description=decode(p_description,FND_API.G_MISS_CHAR,null,null,description,p_description),
307 boolean_type_code=decode(p_ruling_chain,null,boolean_type_code,p_ruling_chain),
308 all_email=decode(p_all_email,null,all_email,p_all_email),
309 rule_type=decode(p_rule_type,null, rule_type, p_rule_type),
310 LAST_UPDATED_BY = decode(G_created_updated_by,FND_API.G_MISS_CHAR,-1,G_created_updated_by),
311 LAST_UPDATE_DATE = sysdate,
312 LAST_UPDATE_LOGIN = decode(G_LAST_UPDATE_LOGIN,FND_API.G_MISS_CHAR,-1,G_LAST_UPDATE_LOGIN)
313
314 where emailproc_id=p_emailproc_id;
315
316 -- Standard Check Of p_commit.
317 IF FND_API.To_Boolean(p_commit) THEN
318 COMMIT WORK;
319 END IF;
320
321 -- Standard callto get message count and if count is 1, get message info.
322 FND_MSG_PUB.Count_And_Get
323 ( p_count => x_msg_count,
324 p_data => x_msg_data
325 );
326
327
328 EXCEPTION
329 WHEN l_IEM_INVALID_PROCEDURE THEN
330 ROLLBACK TO update_item_route;
331 x_return_status := FND_API.G_RET_STS_ERROR ;
332 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
333
334 WHEN IEM_ADM_G_MISS_FOR_NOTNULL THEN
335 ROLLBACK TO update_item_route;
336 FND_MESSAGE.SET_NAME('IEM','IEM_ADM_G_MISS_FOR_NOTNULL');
337 FND_MSG_PUB.Add;
338 x_return_status := FND_API.G_RET_STS_ERROR ;
339 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
340
341 WHEN IEM_ADM_DUP_NAME THEN
342 ROLLBACK TO update_item_route;
343 FND_MESSAGE.SET_NAME('IEM','IEM_ADM_DUP_NAME');
344 FND_MSG_PUB.Add;
345 x_return_status := FND_API.G_RET_STS_ERROR ;
346 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
347
348 WHEN IEM_ADMIN_ROUTE_NO_PROC THEN
349 ROLLBACK TO update_item_route;
350 FND_MESSAGE.SET_NAME('IEM','IEM_ADMIN_ROUTE_NO_PROC');
351 FND_MSG_PUB.Add;
352 x_return_status := FND_API.G_RET_STS_ERROR ;
353 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
354
355 WHEN FND_API.G_EXC_ERROR THEN
356 ROLLBACK TO update_item_route;
357 x_return_status := FND_API.G_RET_STS_ERROR ;
358 FND_MSG_PUB.Count_And_Get
359
360
361 ( p_count => x_msg_count,
362 p_data => x_msg_data
363 );
364
365 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
366 ROLLBACK TO update_item_route;
367 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
368 FND_MSG_PUB.Count_And_Get
369 ( p_count => x_msg_count,
370 p_data => x_msg_data
371 );
372
373
374 WHEN OTHERS THEN
375
376 ROLLBACK TO update_item_route;
377 x_return_status := FND_API.G_RET_STS_ERROR;
378 IF FND_MSG_PUB.Check_Msg_Level
379 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
380 THEN
381 FND_MSG_PUB.Add_Exc_Msg
382 ( G_PKG_NAME ,
383 l_api_name
384 );
385 END IF;
386 FND_MSG_PUB.Count_And_Get
387
388 ( p_count => x_msg_count ,
389 p_data => x_msg_data
390
391 );
392
393 END update_item_emailproc;
394
395
396 PROCEDURE update_item_rule (p_api_version_number IN NUMBER,
397 p_init_msg_list IN VARCHAR2 := null,
398 p_commit IN VARCHAR2 := null,
399 p_emailproc_rule_id IN NUMBER,
400 p_key_type_code IN VARCHAR2:= null,
401 p_operator_type_code IN VARCHAR2:= null,
402 p_value IN VARCHAR2:= null,
403 x_return_status OUT NOCOPY VARCHAR2,
404 x_msg_count OUT NOCOPY NUMBER,
405 x_msg_data OUT NOCOPY VARCHAR2
406 ) is
407 l_api_name VARCHAR2(255):='update_item_rule';
408 l_api_version_number NUMBER:=1.0;
409
410 l_rule NUMBER;
411
412 IEM_NO_RULE_UPDATE EXCEPTION;
413 IEM_ADM_G_MISS_FOR_NOTNULL EXCEPTION;
414 --IEM_INVALID_DATE_FORMAT EXCEPTION;
415 BEGIN
416 -- Standard Start of API savepoint
417
418 SAVEPOINT update_item_rule;
419 -- Standard call to check for call compatibility.
420 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
421 p_api_version_number,
422 l_api_name,
423 G_PKG_NAME)
424 THEN
425 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
426
427 END IF;
428
429 -- Initialize message list if p_init_msg_list is set to TRUE.
430 IF FND_API.to_Boolean( p_init_msg_list )
431 THEN
432
433 FND_MSG_PUB.initialize;
434 END IF;
435 -- Initialize API return status to SUCCESS
436 x_return_status := FND_API.G_RET_STS_SUCCESS;
437
438 -- check if the route_id exist in iem_routes
439 select count(*) into l_rule from iem_emailproc_rules
440 where emailproc_rule_id = p_emailproc_rule_id;
441
442 if l_rule < 1 then
443 raise IEM_NO_RULE_UPDATE;
444 end if;
445
446
447 /*
448 -- translate display date format to canonical date
452
449 if ( substrb(p_key_type_code, 4, 1) = 'D' )then
450
451 l_value := displayDT_to_canonical(p_value);
453
454 if ( l_value is NULL ) then
455 RAISE IEM_INVALID_DATE_FORMAT;
456 end if;
457 else
458 l_value := p_value;
459 end if;
460 */
461
462 if ( p_key_type_code = FND_API.G_MISS_CHAR ) then
463 raise IEM_ADM_G_MISS_FOR_NOTNULL;
464 elsif ( p_operator_type_code = FND_API.G_MISS_CHAR ) then
465 raise IEM_ADM_G_MISS_FOR_NOTNULL;
466 elsif ( p_value = FND_API.G_MISS_CHAR ) then
467 raise IEM_ADM_G_MISS_FOR_NOTNULL;
468 end if;
469
470 update IEM_EMAILPROC_RULES
471 set
472 key_type_code=decode(p_key_type_code,null,key_type_code,p_key_type_code),
473 operator_type_code=decode(p_operator_type_code,null,operator_type_code,p_operator_type_code),
474 value=decode(p_value,null,value,p_value),
475 LAST_UPDATED_BY = decode(G_created_updated_by,null,-1,G_created_updated_by),
476 LAST_UPDATE_DATE = sysdate,
477 LAST_UPDATE_LOGIN = decode(G_LAST_UPDATE_LOGIN,null,-1,G_LAST_UPDATE_LOGIN)
478 where emailproc_rule_id=p_emailproc_rule_id;
479
480 -- Standard Check Of p_commit.
481 IF FND_API.To_Boolean(p_commit) THEN
482 COMMIT WORK;
483 END IF;
484
485 -- Standard callto get message count and if count is 1, get message info.
486 FND_MSG_PUB.Count_And_Get
487 ( p_count => x_msg_count,
488 p_data => x_msg_data
489 );
490 EXCEPTION
491 WHEN IEM_NO_RULE_UPDATE THEN
492 ROLLBACK TO update_item_rule;
493 FND_MESSAGE.SET_NAME('IEM','IEM_NO_RULE_UPDATE');
494
495 FND_MSG_PUB.Add;
496 x_return_status := FND_API.G_RET_STS_ERROR ;
497 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
498
499 WHEN IEM_ADM_G_MISS_FOR_NOTNULL THEN
500 ROLLBACK TO update_item_rule;
501 FND_MESSAGE.SET_NAME('IEM','IEM_ADM_G_MISS_FOR_NOTNULL');
502
503 FND_MSG_PUB.Add;
504 x_return_status := FND_API.G_RET_STS_ERROR ;
505 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
506 /*
507 WHEN IEM_INVALID_DATE_FORMAT THEN
508 ROLLBACK TO update_item_rule;
509 FND_MESSAGE.SET_NAME('IEM','IEM_INVALID_DATE_FORMAT');
510 FND_MSG_PUB.Add;
511 x_return_status := FND_API.G_RET_STS_ERROR ;
512 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
513 */
514 WHEN FND_API.G_EXC_ERROR THEN
515 ROLLBACK TO update_item_rule;
516
517 x_return_status := FND_API.G_RET_STS_ERROR ;
518 FND_MSG_PUB.Count_And_Get
519
520 ( p_count => x_msg_count,
521 p_data => x_msg_data
522 );
523 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
524 ROLLBACK TO update_item_rule;
525 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
526 FND_MSG_PUB.Count_And_Get
527 ( p_count => x_msg_count,
528 p_data => x_msg_data
529 );
530 WHEN OTHERS THEN
531
532 ROLLBACK TO update_item_rule;
533
534 x_return_status := FND_API.G_RET_STS_ERROR;
535 IF FND_MSG_PUB.Check_Msg_Level
536 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
537 THEN
538 FND_MSG_PUB.Add_Exc_Msg
539 ( G_PKG_NAME ,
540 l_api_name
541 );
542 END IF;
543 FND_MSG_PUB.Count_And_Get
544 ( p_count => x_msg_count ,
545 p_data => x_msg_data
546
547
548 );
549
550 END update_item_rule;
551
552
553
554
555 PROCEDURE update_account_emailprocs
556 (p_api_version_number IN NUMBER,
557 p_init_msg_list IN VARCHAR2 := null,
558 p_commit IN VARCHAR2 := null,
559 p_emailproc_id IN NUMBER,
560 p_email_account_id IN NUMBER,
561 p_enabled_flag IN VARCHAR2:= NULL,
562 p_priority IN VARCHAR2:= NULL,
563 x_return_status OUT NOCOPY VARCHAR2,
564 x_msg_count OUT NOCOPY NUMBER,
565 x_msg_data OUT NOCOPY VARCHAR2
566 ) is
567 l_api_name VARCHAR2(255):='update_account_emailprocs';
568 l_api_version_number NUMBER:=1.0;
569 l_emailproc_cnt NUMBER;
570 l_acct_cnt NUMBER;
571 l_LAST_UPDATED_BY NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('USER_ID')) ;
572 l_LAST_UPDATE_DATE DATE:=SYSDATE;
573 l_LAST_UPDATE_LOGIN NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('LOGIN_ ID')) ;
574 l_enabled_flag VARCHAR2(1);
575
576 BEGIN
577 -- Standard Start of API savepoint
578 SAVEPOINT update_account_emailprocs_PVT;
579 -- Standard call to check for call compatibility.
580 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
581 p_api_version_number,
582 l_api_name,
583 G_PKG_NAME)
584 THEN
588
585 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
586 END IF;
587 -- Initialize message list if p_init_msg_list is set to TRUE.
589 IF FND_API.to_Boolean( p_init_msg_list )
590
591 THEN
592 FND_MSG_PUB.initialize;
593 END IF;
594 -- Initialize API return status to SUCCESS
595 x_return_status := FND_API.G_RET_STS_SUCCESS;
596
597 -- Check For Existing IEM Server Group
598
599 Select count(*) into l_emailproc_cnt from iem_emailprocs
600 where emailproc_id=p_emailproc_id;
601
602 IF l_emailproc_cnt = 0 then
603 FND_MESSAGE.SET_NAME('IEM','IEM_ADM_EMAILPROC_NOT_EXIST');
604 APP_EXCEPTION.RAISE_EXCEPTION;
605 END IF;
606
607 --Changed for 115.11 schema: iem_mstemail_accounts
608 Select count(*) into l_acct_cnt from iem_mstemail_accounts
609 where email_account_id=p_email_account_id;
610
611 IF l_acct_cnt = 0 then
612 FND_MESSAGE.SET_NAME('IEM','IEM_ADMIN_ACCOUNT_NOT_EXIST');
613 APP_EXCEPTION.RAISE_EXCEPTION;
614 END IF;
615
616 if (p_enabled_flag is null ) then
617 update IEM_ACCOUNT_EMAILPROCS
618 set
619 priority=decode(p_priority,null,priority,p_priority),
620 LAST_UPDATED_BY = decode(G_created_updated_by,null,-1,G_created_updated_by),
621 LAST_UPDATE_DATE = sysdate,
622 LAST_UPDATE_LOGIN = decode(G_LAST_UPDATE_LOGIN,null,-1,G_LAST_UPDATE_LOGIN)
623 where emailproc_id = p_emailproc_id and email_account_id = p_email_account_id;
624 elsif(p_enabled_flag = FND_API.G_MISS_CHAR ) then
625 update IEM_ACCOUNT_EMAILPROCS
626 set
627 enabled_flag='N',
628 priority=decode(p_priority,null,priority,p_priority),
629 LAST_UPDATED_BY = decode(G_created_updated_by,null,-1,G_created_updated_by),
630 LAST_UPDATE_DATE = sysdate,
631 LAST_UPDATE_LOGIN = decode(G_LAST_UPDATE_LOGIN,null,-1,G_LAST_UPDATE_LOGIN)
632 where emailproc_id = p_emailproc_id and email_account_id = p_email_account_id;
633 elsif (p_enabled_flag = 'Y' or p_enabled_flag = 'N' ) then
634 update IEM_ACCOUNT_EMAILPROCS
635 set
636 enabled_flag=p_enabled_flag,
637 priority=decode(p_priority,null,priority,p_priority),
638 LAST_UPDATED_BY = decode(G_created_updated_by,null,-1,G_created_updated_by),
639 LAST_UPDATE_DATE = sysdate,
640 LAST_UPDATE_LOGIN = decode(G_LAST_UPDATE_LOGIN,null,-1,G_LAST_UPDATE_LOGIN)
641 where emailproc_id = p_emailproc_id and email_account_id = p_email_account_id;
642 else
643 FND_MESSAGE.SET_NAME('IEM','IEM_ACCT_EMLPROC_INVLD_ENABLED');
644 APP_EXCEPTION.RAISE_EXCEPTION;
645 end if;
646
647
648 -- Standard Check Of p_commit.
649 IF FND_API.To_Boolean(p_commit) THEN
650 COMMIT WORK;
651
652 END IF;
653 -- Standard callto get message count and if count is 1, get message info.
654 FND_MSG_PUB.Count_And_Get
655 ( p_count => x_msg_count,
656 p_data => x_msg_data
657
658 );
659 EXCEPTION
660
661 WHEN FND_API.G_EXC_ERROR THEN
662 ROLLBACK TO update_account_emailprocs_PVT;
663 x_return_status := FND_API.G_RET_STS_ERROR ;
664 FND_MSG_PUB.Count_And_Get
665
666 ( p_count => x_msg_count,
667 p_data => x_msg_data
668 );
669 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
670 ROLLBACK TO update_account_emailprocs_PVT;
671 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
672
673 FND_MSG_PUB.Count_And_Get
674 ( p_count => x_msg_count,
675 p_data => x_msg_data
676 );
677 WHEN OTHERS THEN
678 ROLLBACK TO update_account_emailprocs_PVT;
679
680 x_return_status := FND_API.G_RET_STS_ERROR;
681 IF FND_MSG_PUB.Check_Msg_Level
682 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
683 THEN
684 FND_MSG_PUB.Add_Exc_Msg
685 ( G_PKG_NAME ,
686 l_api_name
687
688 );
689 END IF;
690 FND_MSG_PUB.Count_And_Get
691 ( p_count => x_msg_count,
692 p_data => x_msg_data
693
694 );
695
696 END update_account_emailprocs;
697
698
699
700
701 -- Enter further code below as specified in the Package spec.
702
703
704 PROCEDURE create_item_emailprocs (
705 p_api_version_number IN NUMBER,
706 p_init_msg_list IN VARCHAR2 := null,
707 p_commit IN VARCHAR2 := null,
708 p_name IN VARCHAR2,
709 p_description IN VARCHAR2:= null,
710 p_boolean_type_code IN VARCHAR2,
711 P_rule_type IN VARCHAR2,
712 p_all_email IN VARCHAR2,
713 x_emailproc_id OUT NOCOPY NUMBER,
714 x_return_status OUT NOCOPY VARCHAR2,
715 x_msg_count OUT NOCOPY NUMBER,
716 x_msg_data OUT NOCOPY VARCHAR2
717 ) is
718 l_api_name VARCHAR2(255):='create_item_routes';
719 l_api_version_number NUMBER:=1.0;
720 l_seq_id NUMBER;
721 l_proc_name VARCHAR2(256);
722 l_name_count NUMBER;
723
724 l_return_status VARCHAR2(20) := FND_API.G_RET_STS_SUCCESS;
725 l_msg_count NUMBER := 0;
726 l_msg_data VARCHAR2(2000);
727
728 l_all_email VARCHAR2(1);
729 l_description VARCHAR2(256);
730 IEM_ADM_DUP_NAME EXCEPTION;
731 l_IEM_INVALID_PROCEDURE EXCEPTION;
732
733 BEGIN
734 -- Standard Start of API savepoint
735 SAVEPOINT create_item_routes_PVT;
736
737 -- Standard call to check for call compatibility.
738
739 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
740 p_api_version_number,
741 l_api_name,
742 G_PKG_NAME)
743 THEN
744 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
745 END IF;
746
747
748 -- Initialize message list if p_init_msg_list is set to TRUE.
749 IF FND_API.to_Boolean( p_init_msg_list )
750 THEN
751 FND_MSG_PUB.initialize;
752 END IF;
753
754
755 -- Initialize API return status to SUCCESS
756 x_return_status := FND_API.G_RET_STS_SUCCESS;
757
758 --begins here
759
760 --check duplicate value for attribute Name
761 select count(*) into l_name_count from iem_emailprocs where rule_type=p_rule_type and UPPER(name) = UPPER(p_name);
762
763 if l_name_count > 0 then
764 raise IEM_ADM_DUP_NAME;
765 end if;
766
767 if ( p_all_email = FND_API.G_MISS_CHAR ) or ( p_all_email is null ) then
768 l_all_email := 'N';
769 else
770 l_all_email := p_all_email;
771 end if;
772
773 if ( p_description = FND_API.G_MISS_CHAR ) or ( p_description is null ) then
774 l_description := null;
775 else
776 l_description := LTRIM(RTRIM(p_description));
777 end if;
778
779 /*
780 if ( p_boolean_type_code = 'DYNAMIC' ) then
781 l_proc_name := LTRIM(RTRIM( p_proc_name ) );
782 --validation goes here.
783 IEM_ROUTE_RUN_PROC_PVT.validProcedure(
784 p_api_version_number => P_Api_Version_Number,
785 p_init_msg_list => FND_API.G_FALSE,
786 p_commit => P_Commit,
787 p_ProcName => l_proc_name,
788 p_return_type => p_return_type,
789 x_return_status => l_return_status,
790 x_msg_count => l_msg_count,
791 x_msg_data => l_msg_data
792 );
793 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
794 raise l_IEM_INVALID_PROCEDURE;
795 end if;
796 else
797 l_proc_name := null;
798 end if;
799 */
800 --get next sequential number for route_id
801 SELECT IEM_EMAILPROCS_s1.nextval
802 INTO l_seq_id
803 FROM dual;
804
805 INSERT INTO IEM_EMAILPROCS
806 (
807 EMAILPROC_ID,
808 NAME,
809 DESCRIPTION,
810 BOOLEAN_TYPE_CODE,
811 ALL_EMAIL,
812 RULE_TYPE,
813 ATTRIBUTE1,
814 ATTRIBUTE2,
815 ATTRIBUTE3,
816 ATTRIBUTE4,
817 ATTRIBUTE5,
818 ATTRIBUTE6,
819 ATTRIBUTE7,
823 ATTRIBUTE11,
820 ATTRIBUTE8,
821 ATTRIBUTE9,
822 ATTRIBUTE10,
824 ATTRIBUTE12,
825 ATTRIBUTE13,
826 ATTRIBUTE14,
827 ATTRIBUTE15,
828 ATTRIBUTE_CATEGORY,
829 CREATED_BY,
830 CREATION_DATE,
831 LAST_UPDATED_BY,
832 LAST_UPDATE_DATE,
833 LAST_UPDATE_LOGIN
834 )
835 VALUES
836 (
837 l_seq_id,
838 p_name,
839 l_description,
840 p_boolean_type_code,
841 l_all_email,
842 P_RULE_TYPE,
843 NULL,
844 NULL,
845 NULL,
846 NULL,
847 NULL,
848 NULL,
849 NULL,
850 NULL,
851 NULL,
852 NULL,
853 NULL,
854 NULL,
855 NULL,
856 NULL,
857 NULL,
858 NULL,
859 decode(G_created_updated_by,null,-1,G_created_updated_by),
860 sysdate,
861 decode(G_created_updated_by,null,-1,G_created_updated_by),
862 sysdate,
863 decode(G_LAST_UPDATE_LOGIN,null,-1,G_LAST_UPDATE_LOGIN)
864 );
865
866 x_emailproc_id := l_seq_id;
867
868 -- Standard Check Of p_commit.
869 IF FND_API.To_Boolean(p_commit) THEN
870 COMMIT WORK;
871 END IF;
872
873 -- Standard callto get message count and if count is 1, get message info.
874 FND_MSG_PUB.Count_And_Get
875 ( p_count => x_msg_count,
876 p_data => x_msg_data
877 );
878
879
880
881 EXCEPTION
882 WHEN l_IEM_INVALID_PROCEDURE THEN
883 ROLLBACK TO create_item_routes_PVT;
884 x_return_status := FND_API.G_RET_STS_ERROR ;
885 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
886
887 WHEN IEM_ADM_DUP_NAME THEN
888 ROLLBACK TO create_item_routes_PVT;
889 FND_MESSAGE.SET_NAME('IEM','IEM_ADM_DUP_NAME');
890 FND_MSG_PUB.Add;
891 x_return_status := FND_API.G_RET_STS_ERROR ;
892 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
893
894 WHEN FND_API.G_EXC_ERROR THEN
895 ROLLBACK TO create_item_routes_PVT;
896 x_return_status := FND_API.G_RET_STS_ERROR ;
897
898 FND_MSG_PUB.Count_And_Get
899
900 ( p_count => x_msg_count,
901 p_data => x_msg_data
902 );
903
904 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
905 ROLLBACK TO create_item_routes_PVT;
906 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
907 FND_MSG_PUB.Count_And_Get
908 ( p_count => x_msg_count,
909 p_data => x_msg_data
910 );
911
912
913 WHEN OTHERS THEN
914
915 ROLLBACK TO create_item_routes_PVT;
916 x_return_status := FND_API.G_RET_STS_ERROR;
917 IF FND_MSG_PUB.Check_Msg_Level
918 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
919 THEN
920 FND_MSG_PUB.Add_Exc_Msg
921 ( G_PKG_NAME ,
922 l_api_name
923 );
924 END IF;
925
926 FND_MSG_PUB.Count_And_Get
927 ( p_count => x_msg_count,
928 p_data => x_msg_data
929
930 );
931
932 END create_item_emailprocs;
933
934
935 PROCEDURE create_item_emailproc_rules (
936 p_api_version_number IN NUMBER,
937 p_init_msg_list IN VARCHAR2 := null,
938 p_commit IN VARCHAR2 := null,
939 p_emailproc_id IN NUMBER,
940 p_key_type_code IN VARCHAR2,
941 p_operator_type_code IN VARCHAR2,
942 p_value IN VARCHAR2,
943 x_return_status OUT NOCOPY VARCHAR2,
944 x_msg_count OUT NOCOPY NUMBER,
945 x_msg_data OUT NOCOPY VARCHAR2
946 ) is
947 l_api_name VARCHAR2(255):='create_item_emailproc_rules';
948 l_api_version_number NUMBER:=1.0;
949
950 l_seq_id number;
951
952 --IEM_INVALID_DATE_FORMAT EXCEPTION;
953
954 BEGIN
955 -- Standard Start of API savepoint
956
957 SAVEPOINT create_emailproc_rules_PVT;
958 -- Standard call to check for call compatibility.
959 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
960 p_api_version_number,
961 l_api_name,
962 G_PKG_NAME)
963
964 THEN
965 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
966 END IF;
967
968 -- Initialize message list if p_init_msg_list is set to TRUE.
969 IF FND_API.to_Boolean( p_init_msg_list )
970 THEN
971
972 FND_MSG_PUB.initialize;
973 END IF;
974
975 -- Initialize API return status to SUCCESS
976 x_return_status := FND_API.G_RET_STS_SUCCESS;
977
978
979
980 /*
981 -- translate display date format to canonical date
982 if ( substrb(p_key_type_code, 4, 1) = 'D' )then
983
984 l_value := displayDT_to_canonical(p_value);
985
986
987 if ( l_value is NULL ) then
988 RAISE IEM_INVALID_DATE_FORMAT;
989 end if;
990 else
991
992 l_value := p_value;
996
993 end if;
994 */
995
997 SELECT IEM_EMAILPROC_RULES_s1.nextval
998 INTO l_seq_id
999 FROM dual;
1000
1001 INSERT INTO IEM_EMAILPROC_RULES
1002 (
1003 EMAILPROC_RULE_ID,
1004 EMAILPROC_ID,
1005 KEY_TYPE_CODE,
1006 OPERATOR_TYPE_CODE,
1007 VALUE,
1008 ATTRIBUTE1,
1009 ATTRIBUTE2,
1010 ATTRIBUTE3,
1011 ATTRIBUTE4,
1012 ATTRIBUTE5,
1013 ATTRIBUTE6,
1014 ATTRIBUTE7,
1015 ATTRIBUTE8,
1016 ATTRIBUTE9,
1017 ATTRIBUTE10,
1018 ATTRIBUTE11,
1019 ATTRIBUTE12,
1020 ATTRIBUTE13,
1021 ATTRIBUTE14,
1022 ATTRIBUTE15,
1023 ATTRIBUTE_CATEGORY,
1024 CREATED_BY,
1025 CREATION_DATE,
1026 LAST_UPDATED_BY,
1027 LAST_UPDATE_DATE,
1028 LAST_UPDATE_LOGIN
1029 )
1030 VALUES
1031 (
1032 l_seq_id,
1033 p_emailproc_id,
1034 p_key_type_code,
1035 p_operator_type_code,
1036 p_value,
1037 NULL,
1038 NULL,
1039 NULL,
1040 NULL,
1041 NULL,
1042 NULL,
1043 NULL,
1044 NULL,
1045 NULL,
1046 NULL,
1047 NULL,
1048 NULL,
1049 NULL,
1050 NULL,
1051 NULL,
1052 NULL,
1053 decode(G_created_updated_by,null,-1,G_created_updated_by),
1054 sysdate,
1055 decode(G_created_updated_by,null,-1,G_created_updated_by),
1056 sysdate,
1057 decode(G_LAST_UPDATE_LOGIN,null,-1,G_LAST_UPDATE_LOGIN)
1058 );
1059
1060 -- Standard Check Of p_commit.
1061 IF FND_API.To_Boolean(p_commit) THEN
1062
1063 COMMIT WORK;
1064
1065 END IF;
1066
1067 -- Standard callto get message count and if count is 1, get message info.
1068 FND_MSG_PUB.Count_And_Get
1069 ( p_count => x_msg_count,
1070 p_data => x_msg_data
1071 );
1072
1073 EXCEPTION
1074 WHEN FND_API.G_EXC_ERROR THEN
1075 ROLLBACK TO create_emailproc_rules_PVT;
1076 x_return_status := FND_API.G_RET_STS_ERROR ;
1077 FND_MSG_PUB.Count_And_Get
1078 ( p_count => x_msg_count,
1079 p_data => x_msg_data
1080 );
1081
1082 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1083 ROLLBACK TO create_emailproc_rules_PVT;
1084 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1085 FND_MSG_PUB.Count_And_Get
1086 ( p_count => x_msg_count,
1087 p_data => x_msg_data
1088 );
1089
1090 WHEN OTHERS THEN
1091 ROLLBACK TO create_emailproc_rules_PVT;
1092 x_return_status := FND_API.G_RET_STS_ERROR;
1093 IF FND_MSG_PUB.Check_Msg_Level
1094 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1095 THEN
1096 FND_MSG_PUB.Add_Exc_Msg
1097 ( G_PKG_NAME ,
1098 l_api_name
1099 );
1100 END IF;
1101 FND_MSG_PUB.Count_And_Get
1102 ( p_count => x_msg_count ,
1103 p_data => x_msg_data
1104 );
1105 END create_item_emailproc_rules;
1106
1107 PROCEDURE create_item_actions (
1108 p_api_version_number IN NUMBER,
1109 p_init_msg_list IN VARCHAR2 := null,
1110 p_commit IN VARCHAR2 := null,
1111 p_emailproc_id IN NUMBER,
1112 p_action_name IN VARCHAR2,
1113 x_action_id OUT NOCOPY NUMBER,
1114 x_return_status OUT NOCOPY VARCHAR2,
1115 x_msg_count OUT NOCOPY NUMBER,
1116 x_msg_data OUT NOCOPY VARCHAR2
1117 ) is
1118 l_api_name VARCHAR2(255):='create_item_actions';
1119 l_api_version_number NUMBER:=1.0;
1120 l_seq_id number;
1121
1122 BEGIN
1123
1124 -- Standard Start of API savepoint
1128 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
1125 SAVEPOINT create_item_actions_PVT;
1126
1127 -- Standard call to check for call compatibility.
1129 p_api_version_number,
1130
1131 l_api_name,
1132 G_PKG_NAME)
1133 THEN
1134 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1135 END IF;
1136
1137 -- Initialize message list if p_init_msg_list is set to TRUE.
1138
1139 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1140 FND_MSG_PUB.initialize;
1141 END IF;
1142
1143 -- Initialize API return status to SUCCESS
1144
1145 x_return_status := FND_API.G_RET_STS_SUCCESS;
1146
1147 --actual API begins here
1148 SELECT IEM_ACTIONS_s1.nextval
1149 INTO l_seq_id
1150 FROM dual;
1151
1152 INSERT INTO IEM_ACTIONS
1153 (
1154 ACTION_ID,
1155 EMAILPROC_ID,
1156 ACTION,
1157 ATTRIBUTE1,
1158 ATTRIBUTE2,
1159 ATTRIBUTE3,
1160 ATTRIBUTE4,
1161 ATTRIBUTE5,
1162 ATTRIBUTE6,
1163 ATTRIBUTE7,
1164 ATTRIBUTE8,
1165 ATTRIBUTE9,
1166 ATTRIBUTE10,
1167 ATTRIBUTE11,
1168 ATTRIBUTE12,
1169 ATTRIBUTE13,
1170 ATTRIBUTE14,
1171 ATTRIBUTE15,
1172 ATTRIBUTE_CATEGORY,
1173 CREATED_BY,
1174 CREATION_DATE,
1175 LAST_UPDATED_BY,
1176 LAST_UPDATE_DATE,
1177 LAST_UPDATE_LOGIN
1178 )
1179 VALUES
1180 (
1181 l_seq_id,
1182 p_emailproc_id,
1183 p_action_name,
1184 NULL,
1185 NULL,
1186 NULL,
1187 NULL,
1188 NULL,
1189 NULL,
1190 NULL,
1191 NULL,
1192 NULL,
1193 NULL,
1194 NULL,
1195 NULL,
1196 NULL,
1197 NULL,
1198 NULL,
1199 NULL,
1200 decode(G_created_updated_by,null,-1,G_created_updated_by),
1201 sysdate,
1202 decode(G_created_updated_by,null,-1,G_created_updated_by),
1203 sysdate,
1204 decode(G_LAST_UPDATE_LOGIN,null,-1,G_LAST_UPDATE_LOGIN)
1205 );
1206
1207 x_action_id := l_seq_id;
1208
1209 -- Standard Check Of p_commit.
1210 IF FND_API.To_Boolean(p_commit) THEN
1211 COMMIT WORK;
1212 END IF;
1213
1214 -- Standard callto get message count and if count is 1, get message info.
1215 FND_MSG_PUB.Count_And_Get
1216 ( p_count => x_msg_count,
1217 p_data => x_msg_data
1218 );
1219
1220 EXCEPTION
1221 WHEN FND_API.G_EXC_ERROR THEN
1222
1223 ROLLBACK TO create_item_actions_PVT;
1224
1225 x_return_status := FND_API.G_RET_STS_ERROR ;
1226 FND_MSG_PUB.Count_And_Get
1227 ( p_count => x_msg_count,
1228 p_data => x_msg_data
1229 );
1233 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1230
1231 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1232 ROLLBACK TO create_item_actions_PVT;
1234 FND_MSG_PUB.Count_And_Get
1235 ( p_count => x_msg_count,
1236
1237 p_data => x_msg_data
1238 );
1239
1240
1241 WHEN OTHERS THEN
1242 ROLLBACK TO create_item_actions_PVT;
1243 x_return_status := FND_API.G_RET_STS_ERROR;
1244 IF FND_MSG_PUB.Check_Msg_Level
1245 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1246 THEN
1247 FND_MSG_PUB.Add_Exc_Msg
1248 ( G_PKG_NAME ,
1249 l_api_name
1250
1251 );
1252 END IF;
1253 FND_MSG_PUB.Count_And_Get
1254
1255 ( p_count => x_msg_count ,
1256 p_data => x_msg_data
1257 );
1258
1259 END create_item_actions;
1260
1261 PROCEDURE create_item_action_dtls (
1262 p_api_version_number IN NUMBER,
1263 p_init_msg_list IN VARCHAR2 := null,
1264 p_commit IN VARCHAR2 := null,
1265 p_action_id IN NUMBER,
1266 p_param1 IN VARCHAR2,
1267 p_param2 IN VARCHAR2,
1268 p_param_tag IN VARCHAR2,
1269 x_return_status OUT NOCOPY VARCHAR2,
1270 x_msg_count OUT NOCOPY NUMBER,
1271 x_msg_data OUT NOCOPY VARCHAR2
1272 ) is
1273 l_api_name VARCHAR2(255):='create_item_action_dtls';
1274 l_api_version_number NUMBER:=1.0;
1275 l_seq_id number;
1276
1277 BEGIN
1278
1279 -- Standard Start of API savepoint
1280 SAVEPOINT create_item_action_dtls_pvt;
1281
1282 -- Standard call to check for call compatibility.
1283 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
1284 p_api_version_number,
1285
1286 l_api_name,
1287 G_PKG_NAME)
1288 THEN
1289 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1290 END IF;
1291
1292 -- Initialize message list if p_init_msg_list is set to TRUE.
1293
1294 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1295 FND_MSG_PUB.initialize;
1296 END IF;
1297
1298 -- Initialize API return status to SUCCESS
1299
1300 x_return_status := FND_API.G_RET_STS_SUCCESS;
1301
1302 --actual API begins here
1303 SELECT IEM_ACTION_DTLS_s1.nextval
1304 INTO l_seq_id
1305 FROM dual;
1306
1307 INSERT INTO IEM_ACTION_DTLS
1308 (
1309 ACTION_DTL_ID,
1310 ACTION_ID,
1311 PARAMETER1,
1312 PARAMETER2,
1313 PARAMETER_TAG,
1314 ATTRIBUTE1,
1315 ATTRIBUTE2,
1316 ATTRIBUTE3,
1317 ATTRIBUTE4,
1318 ATTRIBUTE5,
1319 ATTRIBUTE6,
1320 ATTRIBUTE7,
1321 ATTRIBUTE8,
1322 ATTRIBUTE9,
1323 ATTRIBUTE10,
1324 ATTRIBUTE11,
1325 ATTRIBUTE12,
1326 ATTRIBUTE13,
1327 ATTRIBUTE14,
1328 ATTRIBUTE15,
1329 ATTRIBUTE_CATEGORY,
1330 CREATED_BY,
1331 CREATION_DATE,
1332 LAST_UPDATED_BY,
1333 LAST_UPDATE_DATE,
1334 LAST_UPDATE_LOGIN
1335 )
1336 VALUES
1337 (
1338 l_seq_id,
1339 p_action_id,
1340 p_param1,
1341 p_param2,
1342 p_param_tag,
1343 NULL,
1344 NULL,
1345 NULL,
1346 NULL,
1347 NULL,
1348 NULL,
1349 NULL,
1350 NULL,
1351 NULL,
1352 NULL,
1353 NULL,
1354 NULL,
1355 NULL,
1356 NULL,
1357 NULL,
1358 NULL,
1359 decode(G_created_updated_by,null,-1,G_created_updated_by),
1360 sysdate,
1361 decode(G_created_updated_by,null,-1,G_created_updated_by),
1362 sysdate,
1363 decode(G_LAST_UPDATE_LOGIN,null,-1,G_LAST_UPDATE_LOGIN)
1364 );
1365
1369 IF FND_API.To_Boolean(p_commit) THEN
1366 -- Standard Check Of p_commit.
1367
1368
1370 COMMIT WORK;
1371 END IF;
1372
1373 -- Standard callto get message count and if count is 1, get message info.
1374 FND_MSG_PUB.Count_And_Get
1375 ( p_count => x_msg_count,
1376 p_data => x_msg_data
1377 );
1378
1379 EXCEPTION
1380 WHEN FND_API.G_EXC_ERROR THEN
1381
1382 ROLLBACK TO create_item_action_dtls_pvt;
1383
1384 x_return_status := FND_API.G_RET_STS_ERROR ;
1385 FND_MSG_PUB.Count_And_Get
1386 ( p_count => x_msg_count,
1387 p_data => x_msg_data
1388 );
1389
1390 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1391 ROLLBACK TO create_item_action_dtls_pvt;
1392 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1393 FND_MSG_PUB.Count_And_Get
1394 ( p_count => x_msg_count,
1395
1396 p_data => x_msg_data
1397 );
1398
1399
1400 WHEN OTHERS THEN
1401 ROLLBACK TO create_item_action_dtls_pvt;
1402 x_return_status := FND_API.G_RET_STS_ERROR;
1403 IF FND_MSG_PUB.Check_Msg_Level
1404 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1405 THEN
1406 FND_MSG_PUB.Add_Exc_Msg
1407 ( G_PKG_NAME ,
1408 l_api_name
1409
1410 );
1411 END IF;
1412 FND_MSG_PUB.Count_And_Get
1413
1414 ( p_count => x_msg_count ,
1415 p_data => x_msg_data
1416 );
1417
1418 END create_item_action_dtls;
1419
1420
1421 PROCEDURE delete_acct_emailproc_batch
1422 (p_api_version_number IN NUMBER,
1423 P_init_msg_list IN VARCHAR2 := null,
1424 p_commit IN VARCHAR2 := null,
1425 p_emailproc_ids_tbl IN jtf_varchar2_Table_100,
1426 p_account_id IN NUMBER,
1427 p_rule_type IN VARCHAR2,
1428 x_return_status OUT NOCOPY VARCHAR2,
1429 x_msg_count OUT NOCOPY NUMBER,
1430 x_msg_data OUT NOCOPY VARCHAR2)
1431 IS
1432 i INTEGER;
1433 l_api_name varchar2(30):='delete_acct_emailproc_batch';
1434 l_api_version_number number:=1.0;
1435
1436
1437 BEGIN
1438
1439 --Standard Savepoint
1440
1441 SAVEPOINT delete_acct_emailproc_batch;
1442
1443 -- Standard call to check for call compatibility.
1444 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
1445 p_api_version_number,
1446 l_api_name,
1447 G_PKG_NAME)
1448 THEN
1449 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1450 END IF;
1451
1452
1453 --Initialize the message list if p_init_msg_list is set to TRUE
1454
1455 If FND_API.to_Boolean(p_init_msg_list) THEN
1456 FND_MSG_PUB.initialize;
1457 END IF;
1458
1459 --Initialize API status return
1460 x_return_status := FND_API.G_RET_STS_SUCCESS;
1461
1462 --Actual API starts here
1463 if ( p_emailproc_ids_tbl.count <> 0 ) then
1464
1465 FOR i IN p_emailproc_ids_tbl.FIRST..p_emailproc_ids_tbl.LAST LOOP
1466
1467 -- update priority after delete an account_emailproc
1468 Update iem_account_emailprocs set priority=priority-1
1469 where email_account_id=p_account_id
1470 and priority >
1471 (Select priority from iem_account_emailprocs
1472 where emailproc_id=p_emailproc_ids_tbl(i)
1473 and email_account_id=p_account_id)
1474 and emailproc_id in
1475 ( select emailproc_id from iem_emailprocs
1476 where rule_type= p_rule_type);
1477
1478 DELETE
1479 FROM IEM_ACCOUNT_EMAILPROCS
1480 WHERE emailproc_id = p_emailproc_ids_tbl(i) and email_account_id = p_account_id;
1481
1482 END LOOP;
1483 end if;
1484
1485
1486
1487 --if SQL%NOTFOUND then
1488 -- raise IEM_ACCOUNT_ROUTE_NOT_DELETED;
1489 --end if;
1490
1491 --Standard check of p_commit
1492 IF FND_API.to_Boolean(p_commit) THEN
1493 COMMIT WORK;
1494 END IF;
1495
1496
1497 EXCEPTION
1498
1499 WHEN FND_API.G_EXC_ERROR THEN
1500 ROLLBACK TO delete_acct_emailproc_batch;
1501 x_return_status := FND_API.G_RET_STS_ERROR ;
1502 FND_MSG_PUB.Count_And_Get
1503 ( p_count => x_msg_count,p_data => x_msg_data);
1504
1505 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1506
1507 ROLLBACK TO delete_acct_emailproc_batch;
1508
1509 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1510 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,p_data => x_msg_data);
1511
1512 WHEN OTHERS THEN
1513 ROLLBACK TO delete_acct_emailproc_batch;
1514 x_return_status := FND_API.G_RET_STS_ERROR;
1515 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1516 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME , l_api_name);
1517 END IF;
1518
1519 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count ,p_data => x_msg_data);
1520
1521
1522 END delete_acct_emailproc_batch;
1523
1524 END IEM_EMAILPROC_HDL_PVT ;