[Home] [Help]
PACKAGE BODY: APPS.IEM_EMAILPROC_HDL_PVT
Source
1 PACKAGE BODY IEM_EMAILPROC_HDL_PVT AS
2 /* $Header: iemvprob.pls 120.0.12010000.2 2009/07/11 16:52:07 lkullamb ship $ */
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
449 if ( substrb(p_key_type_code, 4, 1) = 'D' )then
450
451 l_value := displayDT_to_canonical(p_value);
452
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
585 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
586 END IF;
587 -- Initialize message list if p_init_msg_list is set to TRUE.
588
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,
820 ATTRIBUTE8,
821 ATTRIBUTE9,
822 ATTRIBUTE10,
823 ATTRIBUTE11,
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;
993 end if;
994 */
995
996
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
1125 SAVEPOINT create_item_actions_PVT;
1126
1127 -- Standard call to check for call compatibility.
1128 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
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 );
1230
1231 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1232 ROLLBACK TO create_item_actions_PVT;
1233 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
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_param3 IN VARCHAR2,
1269 p_param_tag IN VARCHAR2,
1270 x_return_status OUT NOCOPY VARCHAR2,
1271 x_msg_count OUT NOCOPY NUMBER,
1272 x_msg_data OUT NOCOPY VARCHAR2
1273 ) is
1274 l_api_name VARCHAR2(255):='create_item_action_dtls';
1275 l_api_version_number NUMBER:=1.0;
1276 l_seq_id number;
1277
1278 BEGIN
1279
1280 -- Standard Start of API savepoint
1281 SAVEPOINT create_item_action_dtls_pvt;
1282
1283 -- Standard call to check for call compatibility.
1284 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
1285 p_api_version_number,
1286
1287 l_api_name,
1288 G_PKG_NAME)
1289 THEN
1290 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1291 END IF;
1292
1293 -- Initialize message list if p_init_msg_list is set to TRUE.
1294
1295 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1296 FND_MSG_PUB.initialize;
1297 END IF;
1298
1299 -- Initialize API return status to SUCCESS
1300
1301 x_return_status := FND_API.G_RET_STS_SUCCESS;
1302
1303 --actual API begins here
1304 SELECT IEM_ACTION_DTLS_s1.nextval
1305 INTO l_seq_id
1306 FROM dual;
1307
1308 INSERT INTO IEM_ACTION_DTLS
1309 (
1310 ACTION_DTL_ID,
1311 ACTION_ID,
1312 PARAMETER1,
1313 PARAMETER2,
1314 PARAMETER_TAG,
1315 ATTRIBUTE1,
1316 ATTRIBUTE2,
1317 ATTRIBUTE3,
1318 ATTRIBUTE4,
1319 ATTRIBUTE5,
1320 ATTRIBUTE6,
1321 ATTRIBUTE7,
1322 ATTRIBUTE8,
1323 ATTRIBUTE9,
1324 ATTRIBUTE10,
1325 ATTRIBUTE11,
1326 ATTRIBUTE12,
1327 ATTRIBUTE13,
1328 ATTRIBUTE14,
1329 ATTRIBUTE15,
1330 ATTRIBUTE_CATEGORY,
1331 CREATED_BY,
1332 CREATION_DATE,
1333 LAST_UPDATED_BY,
1334 LAST_UPDATE_DATE,
1335 LAST_UPDATE_LOGIN,
1336 PARAMETER3
1337 )
1338 VALUES
1339 (
1340 l_seq_id,
1341 p_action_id,
1342 p_param1,
1343 p_param2,
1344 p_param_tag,
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 NULL,
1360 NULL,
1361 decode(G_created_updated_by,null,-1,G_created_updated_by),
1362 sysdate,
1363 decode(G_created_updated_by,null,-1,G_created_updated_by),
1364 sysdate,
1365 decode(G_LAST_UPDATE_LOGIN,null,-1,G_LAST_UPDATE_LOGIN),
1366 p_param3
1367 );
1368
1369 -- Standard Check Of p_commit.
1370
1371
1372 IF FND_API.To_Boolean(p_commit) THEN
1373 COMMIT WORK;
1374 END IF;
1375
1376 -- Standard callto get message count and if count is 1, get message info.
1377 FND_MSG_PUB.Count_And_Get
1378 ( p_count => x_msg_count,
1379 p_data => x_msg_data
1380 );
1381
1382 EXCEPTION
1383 WHEN FND_API.G_EXC_ERROR THEN
1384
1385 ROLLBACK TO create_item_action_dtls_pvt;
1386
1387 x_return_status := FND_API.G_RET_STS_ERROR ;
1388 FND_MSG_PUB.Count_And_Get
1389 ( p_count => x_msg_count,
1390 p_data => x_msg_data
1391 );
1392
1393 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1394 ROLLBACK TO create_item_action_dtls_pvt;
1395 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1396 FND_MSG_PUB.Count_And_Get
1397 ( p_count => x_msg_count,
1398
1399 p_data => x_msg_data
1400 );
1401
1402
1403 WHEN OTHERS THEN
1404 ROLLBACK TO create_item_action_dtls_pvt;
1405 x_return_status := FND_API.G_RET_STS_ERROR;
1406 IF FND_MSG_PUB.Check_Msg_Level
1407 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1408 THEN
1409 FND_MSG_PUB.Add_Exc_Msg
1410 ( G_PKG_NAME ,
1411 l_api_name
1412
1413 );
1414 END IF;
1415 FND_MSG_PUB.Count_And_Get
1416
1417 ( p_count => x_msg_count ,
1418 p_data => x_msg_data
1419 );
1420
1421 END create_item_action_dtls;
1422
1423
1424 PROCEDURE delete_acct_emailproc_batch
1425 (p_api_version_number IN NUMBER,
1426 P_init_msg_list IN VARCHAR2 := null,
1427 p_commit IN VARCHAR2 := null,
1428 p_emailproc_ids_tbl IN jtf_varchar2_Table_100,
1429 p_account_id IN NUMBER,
1430 p_rule_type IN VARCHAR2,
1431 x_return_status OUT NOCOPY VARCHAR2,
1432 x_msg_count OUT NOCOPY NUMBER,
1433 x_msg_data OUT NOCOPY VARCHAR2)
1434 IS
1435 i INTEGER;
1436 l_api_name varchar2(30):='delete_acct_emailproc_batch';
1437 l_api_version_number number:=1.0;
1438
1439
1440 BEGIN
1441
1442 --Standard Savepoint
1443
1444 SAVEPOINT delete_acct_emailproc_batch;
1445
1446 -- Standard call to check for call compatibility.
1447 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
1448 p_api_version_number,
1449 l_api_name,
1450 G_PKG_NAME)
1451 THEN
1452 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1453 END IF;
1454
1455
1456 --Initialize the message list if p_init_msg_list is set to TRUE
1457
1458 If FND_API.to_Boolean(p_init_msg_list) THEN
1459 FND_MSG_PUB.initialize;
1460 END IF;
1461
1462 --Initialize API status return
1463 x_return_status := FND_API.G_RET_STS_SUCCESS;
1464
1465 --Actual API starts here
1466 if ( p_emailproc_ids_tbl.count <> 0 ) then
1467
1468 FOR i IN p_emailproc_ids_tbl.FIRST..p_emailproc_ids_tbl.LAST LOOP
1469
1470 -- update priority after delete an account_emailproc
1471 Update iem_account_emailprocs set priority=priority-1
1472 where email_account_id=p_account_id
1473 and priority >
1474 (Select priority from iem_account_emailprocs
1475 where emailproc_id=p_emailproc_ids_tbl(i)
1476 and email_account_id=p_account_id)
1477 and emailproc_id in
1478 ( select emailproc_id from iem_emailprocs
1479 where rule_type= p_rule_type);
1480
1481 DELETE
1482 FROM IEM_ACCOUNT_EMAILPROCS
1483 WHERE emailproc_id = p_emailproc_ids_tbl(i) and email_account_id = p_account_id;
1484
1485 END LOOP;
1486 end if;
1487
1488
1489
1490 --if SQL%NOTFOUND then
1491 -- raise IEM_ACCOUNT_ROUTE_NOT_DELETED;
1492 --end if;
1493
1494 --Standard check of p_commit
1495 IF FND_API.to_Boolean(p_commit) THEN
1496 COMMIT WORK;
1497 END IF;
1498
1499
1500 EXCEPTION
1501
1502 WHEN FND_API.G_EXC_ERROR THEN
1503 ROLLBACK TO delete_acct_emailproc_batch;
1504 x_return_status := FND_API.G_RET_STS_ERROR ;
1505 FND_MSG_PUB.Count_And_Get
1506 ( p_count => x_msg_count,p_data => x_msg_data);
1507
1508 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1509
1510 ROLLBACK TO delete_acct_emailproc_batch;
1511
1512 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1513 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,p_data => x_msg_data);
1514
1515 WHEN OTHERS THEN
1516 ROLLBACK TO delete_acct_emailproc_batch;
1517 x_return_status := FND_API.G_RET_STS_ERROR;
1518 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1519 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME , l_api_name);
1520 END IF;
1521
1522 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count ,p_data => x_msg_data);
1523
1524
1525 END delete_acct_emailproc_batch;
1526
1527 END IEM_EMAILPROC_HDL_PVT ;