[Home] [Help]
PACKAGE BODY: APPS.IEM_EMAILPROC_PVT
Source
1 PACKAGE BODY IEM_EMAILPROC_PVT AS
2 /* $Header: iemvrulb.pls 120.1.12010000.3 2009/07/13 15:21:07 shramana ship $ */
3
4 --
5 --
6 -- Purpose: Mantain Email Processing Rules Engine related operations
7 --
8 -- MODIFICATION HISTORY
9 -- Person Date Comments
10 -- Liang Xia 8/1/2002 Created
11 -- Liang Xia 11/15/2002 Added dynamic Classification
12 -- Fixed NOCOPY, FND_API.G_MISS.. GSCC warning
13 -- Liang Xia 12/04/2002 Completely fixed NOCOPY FND_API.G_MISS GSCC warning
14 -- Liang Xia 06/10/2003 Added Document Retrieval Rule type
15 -- Liang Xia 08/11/2003 Added Auto-Redirect Rule type
16 -- Liang Xia 01/20/2004 Bugfix:3362872. Do not auto-redirect to the same account.
17 -- Liang Xia 12/03/2004 Changed for 115.11 schema: iem_mstemail_accounts. file version:115.9
18 -- Mina Tang 07/26/2005 Implemented soft delete for R12
19 -- --------- ------ ------------------------------------------
20
21
22 PROCEDURE loadEmailProc (
23 p_api_version_number IN NUMBER,
24 p_init_msg_list IN VARCHAR2 := null,
25 p_commit IN VARCHAR2 := null,
26 x_classification OUT NOCOPY emailProc_tbl,
27 x_autoDelete OUT NOCOPY emailProc_tbl,
28 x_autoAck OUT NOCOPY emailProc_tbl,
29 x_autoProc OUT NOCOPY emailProc_tbl,
30 x_redirect OUT NOCOPY emailProc_tbl,
31 x_3Rs OUT NOCOPY emailProc_tbl,
32 x_document OUT NOCOPY emailProc_tbl,
33 x_route OUT NOCOPY emailProc_tbl,
34 x_return_status OUT NOCOPY VARCHAR2,
35 x_msg_count OUT NOCOPY NUMBER,
36 x_msg_data OUT NOCOPY VARCHAR2
37 )
38 IS
39 l_api_name varchar2(30):='loadEmailProc';
40 l_api_version_number number:=1.0;
41
42 x number :=1;
43 l_classifications emailProc_tbl;
44 l_routes emailProc_tbl;
45 l_autoDeletes emailProc_tbl;
46 l_autoAcks emailProc_tbl;
47 l_autoProcs emailProc_tbl;
48 l_redirects emailProc_tbl;
49 l_3Rs emailProc_tbl;
50 l_documents emailProc_tbl;
51
52 l_action VARCHAR2(30);
53
54 IEM_TAG_NOT_DELETED EXCEPTION;
55
56 cursor c_classifications is
57 select rt.route_classification_id, rt.name, rt.description, fu.user_name,
58 to_char(rt.creation_date) creation_date, rt.boolean_type_code
59 from iem_route_classifications rt, fnd_user fu
60 where fu.user_id = rt.created_by and rt.route_classification_id<>0 and rt.deleted_flag='N'
61 order by UPPER(name) asc;
62
63 cursor c_routes is
64 select rt.route_id, rt.name, rt.description, rt.boolean_type_code, fu.user_name, to_char(rt.creation_date) creation_date
65 from iem_routes rt, fnd_user fu where fu.user_id = rt.created_by
66 order by UPPER(rt.name) asc;
67
68 cursor c_emailProcs(v_rule_type varchar2) is
69 select ep.emailproc_id, ep.name, ep.description, ep.rule_type, fu.user_name, to_char(ep.creation_date) creation_date
70 from iem_emailprocs ep, fnd_user fu
71 where ep.created_by=fu.user_id and ep.rule_type= v_rule_type
72 order by UPPER(name);
73 BEGIN
74
75 --Standard Savepoint
76 SAVEPOINT loadEmailProc_pvt;
77
78 -- Standard call to check for call compatibility.
79 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
80 p_api_version_number,
81 l_api_name,
82 G_PKG_NAME)
83 THEN
84 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
85 END IF;
86
87 --Initialize the message list if p_init_msg_list is set to TRUE
88 If FND_API.to_Boolean(p_init_msg_list) THEN
89 FND_MSG_PUB.initialize;
90 END IF;
91
92 --Initialize API status return
93 x_return_status := FND_API.G_RET_STS_SUCCESS;
94
95 --Actual API starts here
96 x := 1;
97 FOR v_classifications IN c_classifications LOOP
98 l_classifications(x).emailProc_id := v_classifications.route_classification_id;
99 l_classifications(x).name := v_classifications.name;
100 l_classifications(x).description := v_classifications.description;
101 l_classifications(x).rule_type := 'CLASSIFICATION';
102 l_classifications(x).type := v_classifications.boolean_type_code;
103 l_classifications(x).created_by := v_classifications.user_name;
104 l_classifications(x).creation_date := v_classifications.creation_date;
105 x := x + 1;
106 END LOOP;
107
108 x := 1;
109 FOR v_routes IN c_routes LOOP
110 l_routes(x).emailProc_id := v_routes.route_id;
111 l_routes(x).name := v_routes.name;
112 l_routes(x).description := v_routes.description;
113 l_routes(x).rule_type := 'ROUTE';
114 l_routes(x).type := v_routes.boolean_type_code;
115 l_routes(x).created_by := v_routes.user_name;
116 l_routes(x).creation_date := v_routes.creation_date;
117 x := x + 1;
118 END LOOP;
119
120 x := 1;
121 FOR v_emailProcs IN c_emailProcs('AUTODELETE') LOOP
122 l_autoDeletes(x).emailProc_id := v_emailProcs.emailproc_id;
123 l_autoDeletes(x).name := v_emailProcs.name;
124 l_autoDeletes(x).description := v_emailProcs.description;
125 l_autoDeletes(x).rule_type := 'AUTODELETE';
126 l_autoDeletes(x).created_by := v_emailProcs.user_name;
127 l_autoDeletes(x).creation_date := v_emailProcs.creation_date;
128 x := x + 1;
129 END LOOP;
130
131 x := 1;
132 FOR v_emailProcs IN c_emailProcs('AUTOACKNOWLEDGE') LOOP
133 l_autoAcks(x).emailProc_id := v_emailProcs.emailproc_id;
134 l_autoAcks(x).name := v_emailProcs.name;
135 l_autoAcks(x).description := v_emailProcs.description;
136 l_autoAcks(x).rule_type := 'AUTOACKNOWLEDGE';
137 l_autoAcks(x).created_by := v_emailProcs.user_name;
138 l_autoAcks(x).creation_date := v_emailProcs.creation_date;
139 x := x + 1;
140 END LOOP;
141
142 x := 1;
143 FOR v_emailProcs IN c_emailProcs('AUTORRRS') LOOP
144 l_3Rs(x).emailProc_id := v_emailProcs.emailproc_id;
145 l_3Rs(x).name := v_emailProcs.name;
146 l_3Rs(x).description := v_emailProcs.description;
147 l_3Rs(x).rule_type := 'RRRS';
148 l_3Rs(x).created_by := v_emailProcs.user_name;
149 l_3Rs(x).creation_date := v_emailProcs.creation_date;
150
151 select action into l_action from iem_actions
152 where emailproc_id = v_emailProcs.emailproc_id;
153 l_3Rs(x).action := l_action;
154
155 x := x + 1;
156
157 END LOOP;
158
159 x := 1;
160 FOR v_emailProcs IN c_emailProcs('DOCUMENTRETRIEVAL') LOOP
161 l_documents(x).emailProc_id := v_emailProcs.emailproc_id;
162 l_documents(x).name := v_emailProcs.name;
163 l_documents(x).description := v_emailProcs.description;
164 l_documents(x).rule_type := 'DOCUMENTRETRIEVAL';
165 l_documents(x).created_by := v_emailProcs.user_name;
166 l_documents(x).creation_date := v_emailProcs.creation_date;
167
168 select action into l_action from iem_actions
169 where emailproc_id = v_emailProcs.emailproc_id;
170 l_documents(x).action := l_action;
171
172 x := x + 1;
173
174 END LOOP;
175
176 x := 1;
177 FOR v_emailProcs IN c_emailProcs('AUTOPROCESSING') LOOP
178 l_autoProcs(x).emailProc_id := v_emailProcs.emailproc_id;
179 l_autoProcs(x).name := v_emailProcs.name;
180 l_autoProcs(x).description := v_emailProcs.description;
181 l_autoProcs(x).rule_type := 'AUTOPROCESSING';
182 l_autoProcs(x).created_by := v_emailProcs.user_name;
183 l_autoProcs(x).creation_date := v_emailProcs.creation_date;
184 x := x + 1;
185 END LOOP;
186
187 x := 1;
188 FOR v_emailProcs IN c_emailProcs('AUTOREDIRECT') LOOP
189 l_redirects(x).emailProc_id := v_emailProcs.emailproc_id;
190 l_redirects(x).name := v_emailProcs.name;
191 l_redirects(x).description := v_emailProcs.description;
192 l_redirects(x).rule_type := 'AUTOREDIRECT';
193 l_redirects(x).created_by := v_emailProcs.user_name;
194 l_redirects(x).creation_date := v_emailProcs.creation_date;
195
196 select action into l_action from iem_actions
197 where emailproc_id = v_emailProcs.emailproc_id;
198 l_redirects(x).action := l_action;
199
200 x := x + 1;
201 END LOOP;
202
203 x_classification := l_classifications;
204 x_autoDelete := l_autoDeletes;
205 x_autoAck := l_autoAcks;
206 x_autoProc := l_autoProcs;
207 x_redirect := l_redirects;
208 x_3Rs := l_3Rs;
209 x_document := l_documents;
210 x_route := l_routes;
211
212 --Standard check of p_commit
213 IF FND_API.to_Boolean(p_commit) THEN
214 COMMIT WORK;
215 END IF;
216
217
218 EXCEPTION
219 WHEN FND_API.G_EXC_ERROR THEN
220 ROLLBACK TO loadEmailProc_pvt;
221 x_return_status := FND_API.G_RET_STS_ERROR ;
222 FND_MSG_PUB.Count_And_Get
223 ( p_count => x_msg_count,p_data => x_msg_data);
224
225
226 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
227 ROLLBACK TO loadEmailProc_pvt;
228 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
229 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,p_data => x_msg_data);
230
231
232 WHEN OTHERS THEN
233 ROLLBACK TO loadEmailProc_pvt;
234 x_return_status := FND_API.G_RET_STS_ERROR;
235 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
236 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME , l_api_name);
237 END IF;
238
239 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count ,p_data => x_msg_data);
240
241 END ;
242
243
244 PROCEDURE loadAcctEmailProc (
245 p_api_version_number IN NUMBER,
246 p_init_msg_list IN VARCHAR2 := null,
247 p_commit IN VARCHAR2 := null,
248 p_acct_id IN NUMBER,
249 x_classification OUT NOCOPY acctEmailProc_tbl,
250 x_autoDelete OUT NOCOPY acctEmailProc_tbl,
251 x_autoAck OUT NOCOPY acctEmailProc_tbl,
252 x_autoProc OUT NOCOPY acctEmailProc_tbl,
253 x_redirect OUT NOCOPY acctEmailProc_tbl,
254 x_3Rs OUT NOCOPY acctEmailProc_tbl,
255 x_document OUT NOCOPY acctEmailProc_tbl,
256 x_route OUT NOCOPY acctEmailProc_tbl,
257 x_return_status OUT NOCOPY VARCHAR2,
258 x_msg_count OUT NOCOPY NUMBER,
259 x_msg_data OUT NOCOPY VARCHAR2
260 )
261 IS
262 l_api_name varchar2(30):='loadAcctEmailProc';
263 l_api_version_number number:=1.0;
264
265 x number :=1;
266 l_classifications acctEmailProc_tbl;
267 l_routes acctEmailProc_tbl;
268 l_autoDeletes acctEmailProc_tbl;
269 l_autoAcks acctEmailProc_tbl;
270 l_autoProcs acctEmailProc_tbl;
271 l_redirects acctEmailProc_tbl;
272 l_3Rs acctEmailProc_tbl;
273 l_documents acctEmailProc_tbl;
274
275 IEM_TAG_NOT_DELETED EXCEPTION;
276
277 cursor c_classifications(v_acct_Id number) is
278 select a.account_route_class_id, a.priority, r.route_classification_id,
279 r.name,r.description, r.boolean_type_code, a.enabled_flag
280 from iem_route_classifications r, iem_account_route_class a
281 where r.route_classification_id=a.route_classification_id
282 and a.email_account_id = v_acct_Id and r.route_classification_id<>0 and r.deleted_flag='N'
283 order by a.priority asc;
284
285 cursor c_emailProcs( v_acct_Id number, v_rule_type varchar2) is
286 select a.account_emailProc_id, a.priority, r.emailproc_id, r.name,r.description,
287 a.enabled_flag
288 from iem_emailprocs r, iem_account_emailprocs a
289 where r.emailproc_id = a.emailproc_id and a.email_account_id = v_acct_Id
290 and r.rule_type = v_rule_type order by a.priority asc;
291
295 b.action, a.enabled_flag
292 --used to retrieve rule types with different actions
293 cursor c_emailProc_rrr( v_acct_Id number, v_rule_type varchar2) is
294 select a.account_emailProc_id, a.priority, r.emailproc_id, r.name, r.description,
296 from iem_emailprocs r, iem_account_emailprocs a, iem_actions b
297 where r.emailproc_id = a.emailproc_id and a.email_account_id = v_acct_Id
298 and r.rule_type = v_rule_type and a.emailproc_id = b.emailproc_id order by a.priority asc;
299
300 cursor c_routes(v_acct_Id number) is
301 select a.account_route_id, a.priority, r.route_id, r.name,r.description,
302 r.boolean_type_code, a.enabled_flag
303 from iem_routes r, iem_account_routes a
304 where r.route_id=a.route_id and a.email_account_id = v_acct_Id
305 order by a.priority asc;
306
307 BEGIN
308
309 --Standard Savepoint
310 SAVEPOINT loadAcctEmailProc_pvt;
311
312 -- Standard call to check for call compatibility.
313 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
314 p_api_version_number,
315 l_api_name,
316 G_PKG_NAME)
317 THEN
318 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
319 END IF;
320
321 --Initialize the message list if p_init_msg_list is set to TRUE
322 If FND_API.to_Boolean(p_init_msg_list) THEN
323 FND_MSG_PUB.initialize;
324 END IF;
325
326 --Initialize API status return
327 x_return_status := FND_API.G_RET_STS_SUCCESS;
328
329 --Actual API starts here
330 x := 1;
331 FOR v_classifications IN c_classifications(p_acct_id) LOOP
332 l_classifications(x).account_emailProc_id := v_classifications.account_route_class_id;
333 l_classifications(x).emailProc_id := v_classifications.route_classification_id;
334 l_classifications(x).name := v_classifications.name;
335 l_classifications(x).description := v_classifications.description;
336 l_classifications(x).type := v_classifications.boolean_type_code;
337 l_classifications(x).rule_type := 'CLASSIFICATION';
338 l_classifications(x).priority := v_classifications.priority;
339 l_classifications(x).enabled_flag := v_classifications.enabled_flag;
340 x := x + 1;
341 END LOOP;
342
343 x := 1;
344 FOR v_routes IN c_routes(p_acct_id) LOOP
345 l_routes(x).account_emailProc_id := v_routes.account_route_id;
346 l_routes(x).emailProc_id := v_routes.route_id;
347 l_routes(x).name := v_routes.name;
348 l_routes(x).description := v_routes.description;
349 l_routes(x).rule_type := 'ROUTE';
350 l_routes(x).priority := v_routes.priority;
351 l_routes(x).type := v_routes.boolean_type_code;
352 l_routes(x).enabled_flag := v_routes.enabled_flag;
353 x := x + 1;
354 END LOOP;
355
356 x := 1;
357 FOR v_emailProcs IN c_emailProcs(p_acct_id,'AUTODELETE') LOOP
358 l_autoDeletes(x).account_emailProc_id := v_emailProcs.account_emailProc_id;
359 l_autoDeletes(x).emailProc_id := v_emailProcs.emailproc_id;
360 l_autoDeletes(x).name := v_emailProcs.name;
361 l_autoDeletes(x).description := v_emailProcs.description;
362 l_autoDeletes(x).rule_type := 'AUTODELETE';
363 l_autoDeletes(x).priority := v_emailProcs.priority;
364 l_autoDeletes(x).enabled_flag := v_emailProcs.enabled_flag;
365 x := x + 1;
366 END LOOP;
367
368 x := 1;
369 FOR v_emailProcs IN c_emailProcs(p_acct_id,'AUTOACKNOWLEDGE') LOOP
370 l_autoAcks(x).account_emailProc_id := v_emailProcs.account_emailProc_id;
371 l_autoAcks(x).emailProc_id := v_emailProcs.emailproc_id;
372 l_autoAcks(x).name := v_emailProcs.name;
373 l_autoAcks(x).description := v_emailProcs.description;
374 l_autoAcks(x).rule_type := 'AUTOACKNOWLEDGE';
375 l_autoAcks(x).priority := v_emailProcs.priority;
376 l_autoAcks(x).enabled_flag := v_emailProcs.enabled_flag;
377 x := x + 1;
378 END LOOP;
379
380 x := 1;
381 FOR v_emailProcs IN c_emailProcs(p_acct_id,'AUTOPROCESSING') LOOP
382 l_autoProcs(x).account_emailProc_id := v_emailProcs.account_emailProc_id;
383 l_autoProcs(x).emailProc_id := v_emailProcs.emailproc_id;
384 l_autoProcs(x).name := v_emailProcs.name;
385 l_autoProcs(x).description := v_emailProcs.description;
386 l_autoProcs(x).rule_type := 'AUTOPROCESSING';
387 l_autoProcs(x).priority := v_emailProcs.priority;
388 l_autoProcs(x).enabled_flag := v_emailProcs.enabled_flag;
389 x := x + 1;
390 END LOOP;
391
392 x := 1;
393 FOR v_emailProcs IN c_emailProc_rrr(p_acct_id,'AUTORRRS') LOOP
394 l_3Rs(x).account_emailProc_id := v_emailProcs.account_emailProc_id;
395 l_3Rs(x).emailProc_id := v_emailProcs.emailproc_id;
396 l_3Rs(x).name := v_emailProcs.name;
397 l_3Rs(x).description := v_emailProcs.description;
398 l_3Rs(x).rule_type := 'AUTORRRS';
399 l_3Rs(x).priority := v_emailProcs.priority;
400 l_3Rs(x).action := v_emailProcs.action;
401 l_3Rs(x).enabled_flag := v_emailProcs.enabled_flag;
402 x := x + 1;
403 END LOOP;
404
405 x := 1;
406 FOR v_emailProcs IN c_emailProc_rrr(p_acct_id,'DOCUMENTRETRIEVAL') LOOP
407 l_documents(x).account_emailProc_id := v_emailProcs.account_emailProc_id;
408 l_documents(x).emailProc_id := v_emailProcs.emailproc_id;
409 l_documents(x).name := v_emailProcs.name;
410 l_documents(x).description := v_emailProcs.description;
411 l_documents(x).rule_type := 'DOCUMENTRETRIEVAL';
412 l_documents(x).priority := v_emailProcs.priority;
413 l_documents(x).action := v_emailProcs.action;
417
414 l_documents(x).enabled_flag := v_emailProcs.enabled_flag;
415 x := x + 1;
416 END LOOP;
418 x := 1;
419 FOR v_emailProcs IN c_emailProc_rrr(p_acct_id,'AUTOREDIRECT') LOOP
420 l_redirects(x).account_emailProc_id := v_emailProcs.account_emailProc_id;
421 l_redirects(x).emailProc_id := v_emailProcs.emailproc_id;
422 l_redirects(x).name := v_emailProcs.name;
423 l_redirects(x).description := v_emailProcs.description;
424 l_redirects(x).rule_type := 'AUTOREDIRECT';
425 l_redirects(x).priority := v_emailProcs.priority;
426 l_redirects(x).action := v_emailProcs.action;
427 l_redirects(x).enabled_flag := v_emailProcs.enabled_flag;
428 x := x + 1;
429 END LOOP;
430
431 x_classification := l_classifications;
432 x_autoDelete := l_autoDeletes;
433 x_autoAck := l_autoAcks;
434 x_autoProc := l_autoProcs;
435 x_redirect := l_redirects;
436 x_3Rs := l_3Rs;
437 x_document := l_documents;
438 x_route := l_routes;
439
440 --Standard check of p_commit
441 IF FND_API.to_Boolean(p_commit) THEN
442 COMMIT WORK;
443 END IF;
444
445
446 EXCEPTION
447 WHEN FND_API.G_EXC_ERROR THEN
448 ROLLBACK TO loadAcctEmailProc_pvt;
449 x_return_status := FND_API.G_RET_STS_ERROR ;
450 FND_MSG_PUB.Count_And_Get
451 ( p_count => x_msg_count,p_data => x_msg_data);
452
453
454 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
455 ROLLBACK TO loadAcctEmailProc_pvt;
456 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
457 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,p_data => x_msg_data);
458
459
460 WHEN OTHERS THEN
461 ROLLBACK TO loadAcctEmailProc_pvt;
462 x_return_status := FND_API.G_RET_STS_ERROR;
463 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
464 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME , l_api_name);
465 END IF;
466
467 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count ,p_data => x_msg_data);
468
469 END ;
470
471
472 PROCEDURE deleteAcctEmailProc (
473 p_api_version_number IN NUMBER,
474 p_init_msg_list IN VARCHAR2 := null,
475 p_commit IN VARCHAR2 := null,
476 p_acct_id IN NUMBER,
477 p_rule_type In VARCHAR2,
478 p_emailProc_id IN NUMBER,
479 x_return_status OUT NOCOPY VARCHAR2,
480 x_msg_count OUT NOCOPY NUMBER,
481 x_msg_data OUT NOCOPY VARCHAR2
482 )
483
484 IS
485 l_api_name varchar2(30):='deleteAcctEmailProc';
486 l_api_version_number number:=1.0;
487 l_delete_class_ids_tbl jtf_varchar2_Table_100:=jtf_varchar2_Table_100();
488
489 l_return_status VARCHAR2(20) := FND_API.G_RET_STS_SUCCESS;
490 l_msg_count NUMBER := 0;
491 l_msg_data VARCHAR2(2000);
492
493 IEM_EMAILPROC_NOT_DELETED EXCEPTION;
494 MY_EXCEPTION EXCEPTION;
495
496 cursor c_actions(v_emailProc_id number) is
497 select action_id from iem_actions where emailproc_id = v_emailProc_id;
498
499 BEGIN
500
501 --Standard Savepoint
502 SAVEPOINT loadAcctEmailProc_pvt;
503
504 -- Standard call to check for call compatibility.
505 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
506 p_api_version_number,
507 l_api_name,
508 G_PKG_NAME)
509 THEN
510 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
511 END IF;
512
513 --Initialize the message list if p_init_msg_list is set to TRUE
514 If FND_API.to_Boolean(p_init_msg_list) THEN
515 FND_MSG_PUB.initialize;
516 END IF;
517
518 --Initialize API status return
519 x_return_status := FND_API.G_RET_STS_SUCCESS;
520
521 --Actual API starts here
522 IF ( p_rule_type = 'CLASSIFICATION') THEN
523
524 l_delete_class_ids_tbl.extend;
525 l_delete_class_ids_tbl(1) := to_char(p_emailProc_id);
526
527 if ( l_delete_class_ids_tbl.count <> 0 ) then
528 iem_route_class_pvt.delete_acct_class_batch
529 (p_api_version_number => p_api_version_number,
530 P_init_msg_list => p_init_msg_list,
531 p_commit => FND_API.G_FALSE,
532 p_class_ids_tbl => l_delete_class_ids_tbl,
533
534 p_account_id => p_acct_id,
535 x_return_status => l_return_status,
536 x_msg_count => l_msg_count,
537 x_msg_data => l_msg_data) ;
538 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
539 raise MY_EXCEPTION;
540 end if;
541 end if;
542
543 ELSIF ( p_rule_type = 'ROUTE') THEN
544 -- update priority after delete an account_routes
545 Update iem_account_routes set priority=priority-1
546 where email_account_id=p_acct_id
547 and priority >
548 ( Select priority from iem_account_routes
549 where route_id=p_emailProc_id
550 and email_account_id=p_acct_id);
551
552 DELETE
553 FROM IEM_ACCOUNT_ROUTES
554 WHERE route_id = p_emailProc_id and email_account_id = p_acct_id;
555
556 ELSE
557 -- update priority before delete an account_emailprocs
558 Update iem_account_emailprocs set priority=priority-1
559 where email_account_id=p_acct_id
563 and email_account_id=p_acct_id)
560 and priority >
561 ( Select priority from iem_account_emailprocs
562 where emailproc_id=p_emailProc_id
564 and emailproc_id in
565 ( select emailproc_id from iem_emailprocs
566 where rule_type = p_rule_type );
567 DELETE
568 FROM iem_account_emailprocs
569 WHERE emailproc_id = p_emailProc_id and email_account_id = p_acct_id;
570
571 END IF;
572
573 --Standard check of p_commit
574 IF FND_API.to_Boolean(p_commit) THEN
575 COMMIT WORK;
576 END IF;
577
578 EXCEPTION
579 WHEN MY_EXCEPTION THEN
580 ROLLBACK TO loadAcctEmailProc_pvt;
581
582 x_return_status := FND_API.G_RET_STS_ERROR ;
583 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
584 WHEN FND_API.G_EXC_ERROR THEN
585 ROLLBACK TO loadAcctEmailProc_pvt;
586 x_return_status := FND_API.G_RET_STS_ERROR ;
587 FND_MSG_PUB.Count_And_Get
588 ( p_count => x_msg_count,p_data => x_msg_data);
589
590
591 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
592 ROLLBACK TO loadAcctEmailProc_pvt;
593 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
594 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,p_data => x_msg_data);
595
596
597 WHEN OTHERS THEN
598 ROLLBACK TO loadAcctEmailProc_pvt;
599 x_return_status := FND_API.G_RET_STS_ERROR;
600 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
601 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME , l_api_name);
602 END IF;
603
604 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count ,p_data => x_msg_data);
605
606 END ;
607
608 --update iem_routes, update iem_route_rules, insert iem_route_rules
609 PROCEDURE update_emailproc_wrap (
610 p_api_version_number IN NUMBER,
611 p_init_msg_list IN VARCHAR2 := null,
612 p_commit IN VARCHAR2 := null,
613 p_emailproc_id IN NUMBER,
614 p_name IN VARCHAR2:= null,
615 p_ruling_chain IN VARCHAR2:= null,
616 p_description IN VARCHAR2:= null,
617 p_all_email IN VARCHAR2:= null,
618 p_rule_type IN VARCHAR2:= null,
619
620 --below is the data for update
621 p_update_rule_ids_tbl IN jtf_varchar2_Table_100,
622 p_update_rule_keys_tbl IN jtf_varchar2_Table_100,
623 p_update_rule_operators_tbl IN jtf_varchar2_Table_100,
624 p_update_rule_values_tbl IN jtf_varchar2_Table_300,
625 --below is the data for insert
626 p_new_rule_keys_tbl IN jtf_varchar2_Table_100,
627 p_new_rule_operators_tbl IN jtf_varchar2_Table_100,
628 p_new_rule_values_tbl IN jtf_varchar2_Table_300,
629 --below is the data to be removed
630 p_remove_rule_ids_tbl IN jtf_varchar2_Table_100,
631 --below is the action and action parameter to be updated
632 p_action IN VARCHAR2 := null,
633 p_parameter1_tbl IN jtf_varchar2_Table_300,
634 p_parameter2_tbl IN jtf_varchar2_Table_300,
635 p_parameter3_tbl IN jtf_varchar2_Table_300,
636 p_parameter_tag_tbl IN jtf_varchar2_Table_100,
637
638 x_return_status OUT NOCOPY VARCHAR2,
639 x_msg_count OUT NOCOPY NUMBER,
640 x_msg_data OUT NOCOPY VARCHAR2 )is
641
642 l_api_name VARCHAR2(255):='update_emailproc_wrap';
643 l_api_version_number NUMBER:=1.0;
644
645 l_return_status VARCHAR2(20) := FND_API.G_RET_STS_SUCCESS;
646 l_msg_count NUMBER := 0;
647 l_msg_data VARCHAR2(2000);
648
649 l_action_id IEM_ACTIONS.ACTION_ID%type;
650 l_param2_count NUMBER := 0;
651 l_param3_count NUMBER := 0;
652 l_param_tag_count NUMBER := 0;
653
654 l_param2 VARCHAR2(256);
655 l_param3 VARCHAR2(256);
656 l_param_tag VARCHAR2(30);
657
658 IEM_NO_ROUTE_UPDATE EXCEPTION;
659 IEM_NO_RULE_UPDATE EXCEPTION;
660
661 IEM_RULE_NOT_DELETED EXCEPTION;
662 IEM_ROUTE_RULE_NOT_CREATED EXCEPTION;
663 IEM_ADMIN_EMAILPROC_NO_RULE EXCEPTION;
664 l_IEM_FAIL_TO_CALL EXCEPTION;
665 IEM_ACTION_DTLS_NOT_CREATE EXCEPTION;
666 IEM_ADM_AUTOPRC_NO_PARAMETERS EXCEPTION;
667 IEM_ADM_INVALID_PROC_NAME EXCEPTION;
668 IEM_PROC_REDIRECT_SAME_ACCT EXCEPTION;
669
670 l_route NUMBER;
671 l_rule_count NUMBER;
672 l_proc_name VARCHAR2(256);
673 l_all_emails VARCHAR2(1);
674 l_redirect_same_acct NUMBER;
675
676 BEGIN
677 -- Standard Start of API savepoint
678 SAVEPOINT update_item_wrap;
679
680 -- Standard call to check for call compatibility.
681 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
682
686 THEN
683 p_api_version_number,
684 l_api_name,
685 G_PKG_NAME)
687 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
688
689 END IF;
690
691 -- Initialize message list if p_init_msg_list is set to TRUE.
692 IF FND_API.to_Boolean( p_init_msg_list )
693 THEN
694 FND_MSG_PUB.initialize;
695 END IF;
696
697
698 -- Initialize API return status to SUCCESS
699 x_return_status := FND_API.G_RET_STS_SUCCESS;
700
701 --API Body
702
703
704 --check if the route_id exist before update
705 select count(*) into l_route from iem_emailprocs where emailproc_id = p_emailproc_id;
706
707 if l_route < 1 then
708 raise IEM_NO_ROUTE_UPDATE;
709 end if;
710
711
712 --Auto-Processing: Execute Procedure/workflow validation
713 if ( p_action = 'EXECPROCEDURE' ) then
714 if ( p_parameter1_tbl.count < 1 ) then
715 raise IEM_ADM_AUTOPRC_NO_PARAMETERS;
716 elsif ( ( p_parameter1_tbl(1) is null) or (p_parameter1_tbl(1) ='')) then
717 raise IEM_ADM_AUTOPRC_NO_PARAMETERS;
718 else
719 IEM_TAG_RUN_PROC_PVT.validProcedure(
720 p_api_version_number => P_Api_Version_Number,
721 p_init_msg_list => FND_API.G_FALSE,
722 p_commit => P_Commit,
723 p_ProcName => p_parameter1_tbl(1),
724 x_return_status => l_return_status,
725 x_msg_count => l_msg_count,
726 x_msg_data => l_msg_data
727 );
728 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
729 raise IEM_ADM_INVALID_PROC_NAME;
730 end if;
731 end if;
732 --Auto-Redirect: Do not auto-redirect to the same email account validation
733 elsif ( p_action = 'AUTOREDIRECT_INTERNAL' ) then
734 if ( p_parameter1_tbl.count < 1 ) then
735 raise IEM_ADM_AUTOPRC_NO_PARAMETERS;
736 elsif ( ( p_parameter1_tbl(1) is null) or (p_parameter1_tbl(1) ='')) then
737 raise IEM_ADM_AUTOPRC_NO_PARAMETERS;
738 else
739 select count(*) into l_redirect_same_acct
740 from iem_account_emailprocs
741 where email_account_id=p_parameter1_tbl(1) and emailproc_id=p_emailproc_id;
742
743 if ( l_redirect_same_acct > 0 ) then
744 raise IEM_PROC_REDIRECT_SAME_ACCT;
745 end if;
746 end if;
747
748 end if;
749
750 --update iem_routes table
751
752 iem_emailproc_hdl_pvt.update_item_emailproc(
753 p_api_version_number => l_api_version_number,
754 p_init_msg_list => FND_API.G_FALSE,
755 p_commit => FND_API.G_FALSE,
756 p_emailproc_id => p_emailproc_id,
757 p_name => p_name,
758 p_description =>p_description,
759 p_ruling_chain =>p_ruling_chain,
760 p_all_email => p_all_email,
761 p_rule_type => p_rule_type,
762 x_return_status => l_return_status,
763 x_msg_count => l_msg_count,
764 x_msg_data => l_msg_data);
765
766
767 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
768 raise l_IEM_FAIL_TO_CALL;
769 end if;
770
771
772 --update iem_route_rules table
773 if ( p_update_rule_ids_tbl.count <>0 ) then
774
775 FOR i IN p_update_rule_ids_tbl.FIRST..p_update_rule_ids_tbl.LAST loop
776 iem_emailproc_hdl_pvt.update_item_rule(p_api_version_number => l_api_version_number,
777 p_init_msg_list => FND_API.G_FALSE,
778 p_commit => FND_API.G_FALSE,
779 p_emailproc_rule_id => p_update_rule_ids_tbl(i),
780 p_key_type_code =>p_update_rule_keys_tbl(i),
781 p_operator_type_code =>p_update_rule_operators_tbl(i),
782 p_value => p_update_rule_values_tbl(i),
783 x_return_status => l_return_status,
784 x_msg_count => l_msg_count,
785 x_msg_data => l_msg_data);
786
787 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
788 raise IEM_NO_RULE_UPDATE;
789 end if;
790 end loop;
791 end if;
792
793
794 -- update by deleting rules from iem_route_rules table
795 if ( p_remove_rule_ids_tbl.count <> 0 ) then
796 FORALL i IN p_remove_rule_ids_tbl.FIRST..p_remove_rule_ids_tbl.LAST
797 DELETE
798 FROM IEM_EMAILPROC_RULES
799 WHERE emailproc_rule_id = p_remove_rule_ids_tbl(i);
800
801 if SQL%NOTFOUND then
802 raise IEM_RULE_NOT_DELETED;
803 end if;
804 end if;
805
806 if ( p_new_rule_keys_tbl.count <> 0 ) then
807 FOR i IN p_new_rule_keys_tbl.FIRST..p_new_rule_keys_tbl.LAST LOOP
808 iem_emailproc_hdl_pvt.create_item_emailproc_rules (p_api_version_number=>p_api_version_number,
809 p_init_msg_list => p_init_msg_list,
810 p_commit => p_commit,
811 p_emailproc_id => p_emailproc_id,
812 p_key_type_code => p_new_rule_keys_tbl(i),
813 p_operator_type_code => p_new_rule_operators_tbl(i),
814 p_value =>p_new_rule_values_tbl(i),
815
816 x_return_status =>l_return_status,
817 x_msg_count => l_msg_count,
818 x_msg_data => l_msg_data);
819
820 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
821 raise IEM_ROUTE_RULE_NOT_CREATED;
822 end if;
823 END LOOP;
824 end if;
825
826 -- check if exist at least one rule for each route
827
828 select all_email into l_all_emails
829 from iem_emailprocs where emailproc_id = p_emailproc_id;
830
831 if l_all_emails<>'Y' then
832 select count(*) into l_rule_count from iem_emailproc_rules where emailproc_id = p_emailproc_id;
833
834 if l_rule_count < 1 then
835 raise IEM_ADMIN_EMAILPROC_NO_RULE;
836 end if;
837 end if;
838
839 -- updating action
840 select action_id into l_action_id from iem_actions where emailproc_id = p_emailproc_id;
841
842 if ( p_action <> FND_API.G_MISS_CHAR ) or ( p_action is not null ) then
843 update iem_actions set action=p_action where action_id = l_action_id;
844 end if;
845
846 -- updating action dtls
847 delete from iem_action_dtls where action_id = l_action_id;
848
849 if ( p_parameter2_tbl is not null ) then
850 l_param2_count := p_parameter2_tbl.count;
851 end if;
852
853 if ( p_parameter3_tbl is not null ) then
854 l_param3_count := p_parameter3_tbl.count;
855 end if;
856
857
858
859 if ( p_parameter_tag_tbl is not null ) then
860 l_param_tag_count := p_parameter_tag_tbl.count;
861 end if;
862
863 IF ( p_parameter1_tbl is not null ) and ( p_parameter1_tbl.count > 0 ) then
864
865 FOR i IN p_parameter1_tbl.FIRST..p_parameter1_tbl.LAST loop
866
867 if ( i <= l_param2_count ) then
868 l_param2 := p_parameter2_tbl(i);
869 else
870 l_param2 := null; --FND_API.G_MISS_CHAR;--null; --FND_API.G_MISS_CHAR;
871 end if;
872
873 if ( i <= l_param3_count ) then
874 l_param3 := p_parameter3_tbl(i);
875 else
876 l_param3 := null; --FND_API.G_MISS_CHAR;--null; --FND_API.G_MISS_CHAR;
877 end if;
878
879
880
881 if ( i <= l_param_tag_count ) then
882 l_param_tag := p_parameter_tag_tbl(i);
883 else
884 l_param_tag := null; --FND_API.G_MISS_CHAR;--null; --FND_API.G_MISS_CHAR;
885 end if;
886
887 iem_emailproc_hdl_pvt.create_item_action_dtls (
888 p_api_version_number=>p_api_version_number,
889 p_init_msg_list => p_init_msg_list,
890 p_commit => FND_API.G_FALSE,
891 p_action_id => l_action_id,
892 p_param1 => p_parameter1_tbl(i),
893 p_param2 => l_param2,
894 p_param3 => l_param3,
895 p_param_tag => l_param_tag,
896 x_return_status =>l_return_status,
897 x_msg_count => l_msg_count,
898 x_msg_data => l_msg_data);
899
900 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
901 raise IEM_ACTION_DTLS_NOT_CREATE;
902 end if;
903 end loop;
904 END IF;
905
906 commit work;
907
908 EXCEPTION
909 WHEN l_IEM_FAIL_TO_CALL THEN
910 ROLLBACK TO update_item_wrap;
911 -- FND_MESSAGE.SET_NAME('IEM','IEM_NO_ROUTE_UPDATE');
912
913 -- FND_MSG_PUB.Add;
914 x_return_status := FND_API.G_RET_STS_ERROR ;
915 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
916
917 WHEN IEM_ADM_AUTOPRC_NO_PARAMETERS THEN
918 ROLLBACK TO update_item_wrap;
919 FND_MESSAGE.SET_NAME('IEM','IEM_ADM_AUTOPRC_NO_PARAMETERS');
920
921 FND_MSG_PUB.Add;
922 x_return_status := FND_API.G_RET_STS_ERROR ;
923 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
924
925 WHEN IEM_PROC_REDIRECT_SAME_ACCT THEN
926 ROLLBACK TO update_item_wrap;
927 FND_MESSAGE.SET_NAME('IEM','IEM_PROC_REDIRECT_SAME_ACCT');
928
929 FND_MSG_PUB.Add;
930 x_return_status := FND_API.G_RET_STS_ERROR ;
931 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
932
933 WHEN IEM_ADM_INVALID_PROC_NAME THEN
934 ROLLBACK TO update_item_wrap;
935 -- FND_MESSAGE.SET_NAME('IEM','IEM_ADM_INVALID_PROC_NAME');
936
937 -- FND_MSG_PUB.Add;
938 x_return_status := FND_API.G_RET_STS_ERROR ;
939 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
940
941 WHEN IEM_NO_ROUTE_UPDATE THEN
942 ROLLBACK TO update_item_wrap;
943 FND_MESSAGE.SET_NAME('IEM','IEM_NO_ROUTE_UPDATE');
944
945 FND_MSG_PUB.Add;
946 x_return_status := FND_API.G_RET_STS_ERROR ;
947 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
948 WHEN IEM_NO_RULE_UPDATE THEN
949 ROLLBACK TO update_item_wrap;
950 FND_MESSAGE.SET_NAME('IEM','IEM_NO_RULE_UPDATE');
951 FND_MSG_PUB.Add;
952 x_return_status := FND_API.G_RET_STS_ERROR ;
953 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
954
955 WHEN IEM_RULE_NOT_DELETED THEN
956
957 ROLLBACK TO update_item_wrap;
958 FND_MESSAGE.SET_NAME('IEM','IEM_RULE_NOT_DELETED');
959 FND_MSG_PUB.Add;
960 x_return_status := FND_API.G_RET_STS_ERROR ;
961
962 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
963
964 WHEN IEM_ROUTE_RULE_NOT_CREATED THEN
965 ROLLBACK TO update_item_wrap;
966 FND_MESSAGE.SET_NAME('IEM','IEM_ROUTE_RULE_NOT_CREATED');
967 FND_MSG_PUB.Add;
968 x_return_status := FND_API.G_RET_STS_ERROR ;
969 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
970
971
972 WHEN IEM_ADMIN_EMAILPROC_NO_RULE THEN
973 ROLLBACK TO update_item_wrap;
974 FND_MESSAGE.SET_NAME('IEM','IEM_ADMIN_EMAILPROC_NO_RULE');
975 FND_MSG_PUB.Add;
976
977 x_return_status := FND_API.G_RET_STS_ERROR ;
978 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
979
980 WHEN IEM_ACTION_DTLS_NOT_CREATE THEN
981 ROLLBACK TO update_item_wrap;
982 FND_MESSAGE.SET_NAME('IEM','IEM_ACTION_DTLS_NOT_CREATE');
983 FND_MSG_PUB.Add;
984
985 x_return_status := FND_API.G_RET_STS_ERROR ;
986 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
987 WHEN FND_API.G_EXC_ERROR THEN
988 ROLLBACK TO update_item_wrap;
989 x_return_status := FND_API.G_RET_STS_ERROR ;
990 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,p_data => x_msg_data);
991
992
993 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
994 ROLLBACK TO update_item_wrap;
995 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
996 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
997
998
999 WHEN OTHERS THEN
1000 ROLLBACK TO update_item_wrap;
1001 x_return_status := FND_API.G_RET_STS_ERROR;
1002 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1003 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME , l_api_name);
1004 END IF;
1005
1006
1007 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count, p_data => x_msg_data );
1008
1009 END update_emailproc_wrap;
1010
1011 PROCEDURE create_emailproc_wrap (
1012 p_api_version_number IN NUMBER,
1013 p_init_msg_list IN VARCHAR2 := null,
1014 p_commit IN VARCHAR2 := null,
1015 p_route_name IN VARCHAR2,
1016 p_route_description IN VARCHAR2:= null,
1017 p_route_boolean_type_code IN VARCHAR2,
1018 p_rule_type IN VARCHAR2,
1019 p_action IN VARCHAR2,
1020 p_all_email IN VARCHAR2,
1021 p_rule_key_typecode_tbl IN jtf_varchar2_Table_100 ,
1022 p_rule_operator_typecode_tbl IN jtf_varchar2_Table_100,
1023 p_rule_value_tbl IN jtf_varchar2_Table_300,
1024 p_parameter1_tbl IN jtf_varchar2_Table_300,
1025 p_parameter2_tbl IN jtf_varchar2_Table_300,
1026 p_parameter3_tbl IN jtf_varchar2_Table_300,
1027 p_parameter_tag_tbl IN jtf_varchar2_Table_100,
1028 x_return_status OUT NOCOPY VARCHAR2,
1029 x_msg_count OUT NOCOPY NUMBER,
1030 x_msg_data OUT NOCOPY VARCHAR2 ) is
1031
1032
1033 l_api_name VARCHAR2(255):='create_emailproc_wrap';
1034 l_api_version_number NUMBER:=1.0;
1035
1036 l_emailproc_id IEM_EMAILPROCS.EMAILPROC_ID%TYPE;
1037 l_route_rule_id IEM_EMAILPROC_RULES.EMAILPROC_RULE_ID%TYPE;
1038 l_action_id IEM_ACTIONS.ACTION_ID%type;
1039 l_param2_count NUMBER := 0;
1040 l_param3_count NUMBER := 0;
1041 l_param_tag_count NUMBER := 0;
1042
1043 l_param2 VARCHAR2(256);
1044 l_param3 VARCHAR2(256);
1045 l_param_tag VARCHAR2(30);
1046 l_userid NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('USER_ID')) ;
1047 l_login NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('LOGIN_ID')) ;
1048
1049 l_return_status VARCHAR2(20) := FND_API.G_RET_STS_SUCCESS;
1050 l_msg_count NUMBER := 0;
1051 l_msg_data VARCHAR2(2000);
1052
1053 IEM_EMAILPROC_NOT_CREATED EXCEPTION;
1054 IEM_EMAILPROC_RULE_NOT_CREATED EXCEPTION;
1055 IEM_ACTION_NOT_CREATED EXCEPTION;
1056 IEM_ACTION_DTLS_NOT_CREATE EXCEPTION;
1057 IEM_ADM_AUTOPRC_NO_PARAMETERS EXCEPTION;
1058 IEM_ADM_INVALID_PROC_NAME EXCEPTION;
1059 BEGIN
1060
1061 -- Standard Start of API savepoint
1062 SAVEPOINT create_item_wrap;
1063
1064 -- Standard call to check for call compatibility.
1065 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
1066 p_api_version_number,
1067 l_api_name,
1068 G_PKG_NAME)
1069 THEN
1070
1071 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1072 END IF;
1073
1074
1075 -- Initialize message list if p_init_msg_list is set to TRUE.
1076 IF FND_API.to_Boolean( p_init_msg_list )
1077 THEN
1078 FND_MSG_PUB.initialize;
1079 END IF;
1080
1081 -- Initialize API return status to SUCCESS
1082 x_return_status := FND_API.G_RET_STS_SUCCESS;
1083
1084 --API Body
1085 --Auto-Processing: Execute Procedure/workflow validation
1086 if ( p_action = 'EXECPROCEDURE' ) then
1087 if ( p_parameter1_tbl.count < 1 ) then
1088 raise IEM_ADM_AUTOPRC_NO_PARAMETERS;
1089 elsif ( ( p_parameter1_tbl(1) is null) or (p_parameter1_tbl(1) ='')) then
1090 raise IEM_ADM_AUTOPRC_NO_PARAMETERS;
1091 else
1092 IEM_TAG_RUN_PROC_PVT.validProcedure(
1093 p_api_version_number => P_Api_Version_Number,
1094 p_init_msg_list => FND_API.G_FALSE,
1095 p_commit => P_Commit,
1096 p_ProcName => p_parameter1_tbl(1),
1097 x_return_status => l_return_status,
1098 x_msg_count => l_msg_count,
1099 x_msg_data => l_msg_data
1100 );
1101 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
1102 raise IEM_ADM_INVALID_PROC_NAME;
1103 end if;
1104 end if;
1105 end if;
1106
1107 --Create iem_emailprocs
1108 iem_emailproc_hdl_pvt.create_item_emailprocs (
1109 p_api_version_number=>p_api_version_number,
1110 p_init_msg_list => p_init_msg_list,
1111 p_commit => FND_API.G_FALSE,
1112 p_name => p_route_name,
1113 p_description => p_route_description,
1114 p_boolean_type_code =>p_route_boolean_type_code,
1115 p_all_email => p_all_email,
1116 p_rule_type => p_rule_type,
1117 x_emailproc_id => l_emailproc_id,
1118 x_return_status =>l_return_status,
1119 x_msg_count => l_msg_count,
1120 x_msg_data => l_msg_data);
1121
1122
1123 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
1124 raise IEM_EMAILPROC_NOT_CREATED;
1125 end if;
1126
1127
1128 --Create iem_emailproc_rules
1129 if p_rule_key_typecode_tbl.count<> 0 then
1130 FOR i IN p_rule_key_typecode_tbl.FIRST..p_rule_key_typecode_tbl.LAST loop
1131
1132 iem_emailproc_hdl_pvt.create_item_emailproc_rules (
1133 p_api_version_number=>p_api_version_number,
1134 p_init_msg_list => p_init_msg_list,
1135 p_commit => FND_API.G_FALSE,
1136 p_emailproc_id => l_emailproc_id,
1137 p_key_type_code => p_rule_key_typecode_tbl(i),
1138 p_operator_type_code => p_rule_operator_typecode_tbl(i),
1139 p_value =>p_rule_value_tbl(i),
1140 x_return_status =>l_return_status,
1141 x_msg_count => l_msg_count,
1142 x_msg_data => l_msg_data);
1143
1144 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
1145 raise IEM_EMAILPROC_RULE_NOT_CREATED;
1146 end if;
1147 end loop;
1148 end if;
1149
1150 -- create iem_actions
1151 -- IF ( p_rule_type = 'AUTOACKNOWLEDGE' or p_rule_type = 'AUTOPROCESSING' or p_rule_type = 'AUTORRRS' ) then
1152 iem_emailproc_hdl_pvt.create_item_actions (
1153 p_api_version_number=>p_api_version_number,
1154 p_init_msg_list => p_init_msg_list,
1155 p_commit => FND_API.G_FALSE,
1156 p_emailproc_id => l_emailproc_id,
1157 p_action_name => p_action,
1158 x_action_id => l_action_id,
1159 x_return_status =>l_return_status,
1160 x_msg_count => l_msg_count,
1161 x_msg_data => l_msg_data);
1162
1163 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
1164 raise IEM_ACTION_NOT_CREATED;
1165 end if;
1166
1167
1168 if ( p_parameter2_tbl is not null ) then
1169 l_param2_count := p_parameter2_tbl.count;
1170 end if;
1171
1172 if ( p_parameter3_tbl is not null ) then
1173 l_param3_count := p_parameter3_tbl.count;
1174 end if;
1175
1176
1177
1178 if ( p_parameter_tag_tbl is not null ) then
1179 l_param_tag_count := p_parameter_tag_tbl.count;
1180 end if;
1181
1182 IF ( p_parameter1_tbl is not null ) and ( p_parameter1_tbl.count > 0 ) then
1183
1184
1185 FOR i IN p_parameter1_tbl.FIRST..p_parameter1_tbl.LAST loop
1186
1187 if ( i <= l_param2_count ) then
1188 l_param2 := p_parameter2_tbl(i);
1189 else
1190 l_param2 := null; --FND_API.G_MISS_CHAR;--null; --FND_API.G_MISS_CHAR;
1191 end if;
1192
1193 if ( i <= l_param3_count ) then
1194 l_param3 := p_parameter3_tbl(i);
1195 else
1196 l_param3 := null; --FND_API.G_MISS_CHAR;--null; --FND_API.G_MISS_CHAR;
1197 end if;
1198
1199
1200 if ( i <= l_param_tag_count ) then
1201 l_param_tag := p_parameter_tag_tbl(i);
1202 else
1203 l_param_tag := null; --FND_API.G_MISS_CHAR;--null; --FND_API.G_MISS_CHAR;
1204 end if;
1205
1206 iem_emailproc_hdl_pvt.create_item_action_dtls (
1207 p_api_version_number=>p_api_version_number,
1208 p_init_msg_list => p_init_msg_list,
1209 p_commit => FND_API.G_FALSE,
1210 p_action_id => l_action_id,
1211 p_param1 => p_parameter1_tbl(i),
1212 p_param2 => l_param2,
1213 p_param3 => l_param3,
1214 p_param_tag => l_param_tag,
1215 x_return_status =>l_return_status,
1216 x_msg_count => l_msg_count,
1217 x_msg_data => l_msg_data);
1218
1219 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
1220 raise IEM_ACTION_DTLS_NOT_CREATE;
1224
1221 end if;
1222 end loop;
1223 END IF;
1225 -- END IF;
1226
1227 IF FND_API.To_Boolean(p_commit) THEN
1228 COMMIT WORK;
1229 END IF;
1230
1231 EXCEPTION
1232 WHEN IEM_EMAILPROC_NOT_CREATED THEN
1233 ROLLBACK TO create_item_wrap;
1234 x_return_status := FND_API.G_RET_STS_ERROR ;
1235 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1236
1237 WHEN IEM_ADM_AUTOPRC_NO_PARAMETERS THEN
1238 ROLLBACK TO create_item_wrap;
1239 FND_MESSAGE.SET_NAME('IEM','IEM_ADM_AUTOPRC_NO_PARAMETERS');
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
1244 WHEN IEM_ADM_INVALID_PROC_NAME THEN
1245 ROLLBACK TO create_item_wrap;
1246 -- FND_MESSAGE.SET_NAME('IEM','IEM_ADM_INVALID_PROC_NAME');
1247 -- FND_MSG_PUB.Add;
1248 x_return_status := FND_API.G_RET_STS_ERROR ;
1249 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1250
1251 WHEN IEM_EMAILPROC_RULE_NOT_CREATED THEN
1252 ROLLBACK TO create_item_wrap;
1253 FND_MESSAGE.SET_NAME('IEM','IEM_EMAILPROC_RULE_NOT_CREATED');
1254 FND_MSG_PUB.Add;
1255 x_return_status := FND_API.G_RET_STS_ERROR ;
1256 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1257
1258 WHEN IEM_ACTION_NOT_CREATED THEN
1259 ROLLBACK TO create_item_wrap;
1260 FND_MESSAGE.SET_NAME('IEM','IEM_ACTION_NOT_CREATED');
1261 FND_MSG_PUB.Add;
1262 x_return_status := FND_API.G_RET_STS_ERROR ;
1263 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1264 WHEN IEM_ACTION_DTLS_NOT_CREATE THEN
1265
1266 ROLLBACK TO create_item_wrap;
1267 FND_MESSAGE.SET_NAME('IEM','IEM_ACTION_DTLS_NOT_CREATE');
1268 FND_MSG_PUB.Add;
1269 x_return_status := FND_API.G_RET_STS_ERROR ;
1270 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1271
1272 WHEN FND_API.G_EXC_ERROR THEN
1273 ROLLBACK TO create_item_wrap;
1274 x_return_status := FND_API.G_RET_STS_ERROR ;
1275 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,p_data => x_msg_data);
1276
1277
1278 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1279
1280 ROLLBACK TO create_item_wrap;
1281 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1282 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1283
1284 WHEN OTHERS THEN
1285 ROLLBACK TO create_item_wrap;
1286 x_return_status := FND_API.G_RET_STS_ERROR;
1287 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1288 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME , l_api_name);
1289 END IF;
1290
1291 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count, p_data => x_msg_data );
1292
1293 END create_emailproc_wrap;
1294
1295
1296
1297 PROCEDURE create_wrap_account_emailprocs (
1298 p_api_version_number IN NUMBER,
1299 p_init_msg_list IN VARCHAR2 := null,
1300 p_commit IN VARCHAR2 := null,
1301 p_email_account_id IN NUMBER,
1302 p_emailproc_id IN NUMBER,
1303 p_enabled_flag IN VARCHAR2,
1304 p_priority IN NUMBER,
1305 x_return_status OUT NOCOPY VARCHAR2,
1306 x_msg_count OUT NOCOPY NUMBER,
1307 x_msg_data OUT NOCOPY VARCHAR2
1308 ) is
1309 l_api_name VARCHAR2(255):='create_wrap_account_emailprocs';
1310 l_api_version_number NUMBER:=1.0;
1311 l_count number;
1312 l_account number;
1313 l_redirect_same_acct number := 0;
1314
1315 l_return_status VARCHAR2(20) := FND_API.G_RET_STS_SUCCESS;
1316 l_msg_count NUMBER := 0;
1317 l_msg_data VARCHAR2(2000);
1318
1319 IEM_ADMIN_ROUTE_NOT_EXIST EXCEPTION;
1320 IEM_ADMIN_ACCOUNT_NOT_EXIST EXCEPTION;
1321 IEM_ACCOUNT_ROUTE_NOT_UPDATED EXCEPTION;
1322 IEM_NOT_REDIRECT_SAME_ACCT EXCEPTION;
1323 BEGIN
1324 -- Standard Start of API savepoint
1325 SAVEPOINT create_wrap_account_routes_PVT;
1326
1327 -- Standard call to check for call compatibility.
1328 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
1329
1330 p_api_version_number,
1331 l_api_name,
1332 G_PKG_NAME)
1333 THEN
1334 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1335 END IF;
1336
1337 -- Initialize message list if p_init_msg_list is set to TRUE.
1338 IF FND_API.to_Boolean( p_init_msg_list )
1339 THEN
1340
1341 FND_MSG_PUB.initialize;
1342 END IF;
1343
1344
1345 -- Initialize API return status to SUCCESS
1346 x_return_status := FND_API.G_RET_STS_SUCCESS;
1347
1348
1349 -- check if the route_id exist in iem_routes
1350 select count(*) into l_count from iem_emailprocs
1351 where emailproc_id = p_emailproc_id;
1352
1353 if l_count < 1 then
1354
1355 raise IEM_ADMIN_ROUTE_NOT_EXIST;
1356 end if;
1357
1358 -- check if the account_id exist in iem_email_accounts
1359 -- Changed for 115.11 schema compliance
1360 select count(*) into l_account from iem_mstemail_accounts
1361 where email_account_id = p_email_account_id;
1362
1363 if l_account < 1 then
1367 select count(*) into l_redirect_same_acct
1364 raise IEM_ADMIN_ACCOUNT_NOT_EXIST;
1365 end if;
1366
1368 from iem_emailprocs a, iem_actions b, iem_action_dtls c
1369 where a.emailproc_id = p_emailproc_id and a.emailproc_id=b.emailproc_id
1370 and a.rule_type='AUTOREDIRECT' and b.action='AUTOREDIRECT_INTERNAL'
1371 and b.action_id=c.action_id and c.parameter1=to_char(p_email_account_id);
1372
1373 if ( l_redirect_same_acct > 0 ) then
1374 raise IEM_NOT_REDIRECT_SAME_ACCT;
1375 end if;
1376
1377 iem_emailproc_hdl_pvt.create_item_account_emailprocs(
1378 p_api_version_number =>p_api_version_number,
1379 p_init_msg_list => p_init_msg_list,
1380 p_commit => p_commit,
1381 p_emailproc_id =>p_emailproc_id,
1382 p_email_account_id =>p_email_account_id,
1383 p_enabled_flag => p_enabled_flag,
1384 p_priority => p_priority,
1385 x_return_status =>l_return_status,
1386 x_msg_count => l_msg_count,
1387 x_msg_data => l_msg_data);
1388
1389 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
1390 raise IEM_ACCOUNT_ROUTE_NOT_UPDATED;
1391
1392 end if;
1393
1394 -- Standard Check Of p_commit.
1395 IF FND_API.To_Boolean(p_commit) THEN
1396 COMMIT WORK;
1397 END IF;
1398 -- Standard callto get message count and if count is 1, get message info.
1399 FND_MSG_PUB.Count_And_Get
1400 ( p_count => x_msg_count,
1401 p_data => x_msg_data
1402 );
1403
1404
1405 EXCEPTION
1406 WHEN IEM_NOT_REDIRECT_SAME_ACCT THEN
1407 ROLLBACK TO create_wrap_account_routes_PVT;
1408 FND_MESSAGE.SET_NAME('IEM','IEM_NOT_REDIRECT_SAME_ACCT');
1409
1410 FND_MSG_PUB.Add;
1411 x_return_status := FND_API.G_RET_STS_ERROR ;
1412 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1413
1414 WHEN IEM_ADMIN_ROUTE_NOT_EXIST THEN
1415 ROLLBACK TO create_wrap_account_routes_PVT;
1416 FND_MESSAGE.SET_NAME('IEM','IEM_ADMIN_ROUTE_NOT_EXIST');
1417
1418 FND_MSG_PUB.Add;
1419 x_return_status := FND_API.G_RET_STS_ERROR ;
1420 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1421
1422
1423 WHEN IEM_ADMIN_ACCOUNT_NOT_EXIST THEN
1424 ROLLBACK TO create_wrap_account_routes_PVT;
1425 FND_MESSAGE.SET_NAME('IEM','IEM_ADMIN_ACCOUNT_NOT_EXIST');
1426 FND_MSG_PUB.Add;
1427
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 IEM_ACCOUNT_ROUTE_NOT_UPDATED THEN
1432
1433 ROLLBACK TO create_wrap_account_routes_PVT;
1434 FND_MESSAGE.SET_NAME('IEM','IEM_ACCOUNT_ROUTE_NOT_UPDATED');
1435 FND_MSG_PUB.Add;
1436 x_return_status := FND_API.G_RET_STS_ERROR ;
1437 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1438
1439 WHEN FND_API.G_EXC_ERROR THEN
1440 ROLLBACK TO create_wrap_account_routes_PVT;
1441 x_return_status := FND_API.G_RET_STS_ERROR ;
1442
1443 FND_MSG_PUB.Count_And_Get
1444 ( p_count => x_msg_count,
1445 p_data => x_msg_data
1446
1447 );
1448
1449 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1450 ROLLBACK TO create_wrap_account_routes_PVT;
1451 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1452 FND_MSG_PUB.Count_And_Get
1453 ( p_count => x_msg_count,
1454 p_data => x_msg_data
1455 );
1456
1457
1458 WHEN OTHERS THEN
1459 ROLLBACK TO create_wrap_account_routes_PVT;
1460
1461 x_return_status := FND_API.G_RET_STS_ERROR;
1462 IF FND_MSG_PUB.Check_Msg_Level
1463 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1464 THEN
1465 FND_MSG_PUB.Add_Exc_Msg
1466 ( G_PKG_NAME,
1467 l_api_name
1468 );
1469 END IF;
1470 FND_MSG_PUB.Count_And_Get
1471 ( p_count => x_msg_count,
1472
1473 p_data => x_msg_data
1474
1475 );
1476
1477 END create_wrap_account_emailprocs;
1478
1479 -- to update and delete new ruples in iem_account_routes
1480 PROCEDURE update_wrap_account_emailprocs (
1481 p_api_version_number IN NUMBER,
1482 p_init_msg_list IN VARCHAR2 := null,
1483 p_commit IN VARCHAR2 := null,
1484 p_email_account_id IN NUMBER,
1485 p_emailproc_ids_tbl IN jtf_varchar2_Table_100,
1486 p_upd_enable_flag_tbl IN jtf_varchar2_Table_100,
1487 p_delete_emailproc_ids_tbl IN jtf_varchar2_Table_100,
1488 p_rule_type IN varchar2,
1489 x_return_status OUT NOCOPY VARCHAR2,
1490 x_msg_count OUT NOCOPY NUMBER,
1491 x_msg_data OUT NOCOPY VARCHAR2
1492 ) is
1493 l_api_name VARCHAR2(255):='update_wrap_account_emailprocs';
1494 l_api_version_number NUMBER:=1.0;
1495 l_return_status VARCHAR2(20) := FND_API.G_RET_STS_SUCCESS;
1496 l_msg_count NUMBER := 0;
1497 l_msg_data VARCHAR2(2000);
1498 IEM_ACCT_EMAILPROC_NOT_DELETED EXCEPTION;
1499 IEM_ACCT_EMAILPROC_NOT_UPDATED EXCEPTION;
1500 BEGIN
1501 -- Standard Start of API savepoint
1502 SAVEPOINT update_wrap_acct_emailproc_PVT;
1503
1507 l_api_name,
1504 -- Standard call to check for call compatibility.
1505 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
1506 p_api_version_number,
1508 G_PKG_NAME)
1509
1510 THEN
1511 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1512 END IF;
1513
1514 -- Initialize message list if p_init_msg_list is set to TRUE.
1515 IF FND_API.to_Boolean( p_init_msg_list )
1516 THEN
1517
1518 FND_MSG_PUB.initialize;
1519 END IF;
1520 -- Initialize API return status to SUCCESS
1521 x_return_status := FND_API.G_RET_STS_SUCCESS;
1522
1523 -- update first
1524
1525 if ( p_emailproc_ids_tbl.count <> 0 ) then
1526 FOR i IN p_emailproc_ids_tbl.FIRST..p_emailproc_ids_tbl.LAST LOOP
1527 iem_emailproc_hdl_pvt.update_account_emailprocs
1528 (p_api_version_number =>p_api_version_number,
1529 p_init_msg_list => FND_API.G_FALSE,
1530 p_commit => FND_API.G_TRUE,
1531 p_emailproc_id => p_emailproc_ids_tbl(i),
1532 p_email_account_id => p_email_account_id,
1533 p_enabled_flag => p_upd_enable_flag_tbl(i),
1534 x_return_status =>l_return_status,
1535 x_msg_count => l_msg_count,
1536 x_msg_data => l_msg_data);
1537
1538 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
1539 raise IEM_ACCT_EMAILPROC_NOT_UPDATED;
1540 end if;
1541 END LOOP;
1542 end if;
1543
1544
1545 if ( p_delete_emailproc_ids_tbl.count <> 0 ) then
1546 iem_emailproc_hdl_pvt.delete_acct_emailproc_batch
1547 (p_api_version_number => p_api_version_number,
1548 P_init_msg_list => FND_API.G_FALSE,
1549 p_commit => FND_API.G_TRUE,
1550 p_emailproc_ids_tbl => p_delete_emailproc_ids_tbl,
1551 p_account_id => p_email_account_id,
1552 p_rule_type => p_rule_type,
1553 x_return_status => l_return_status,
1554 x_msg_count => l_msg_count,
1555 x_msg_data => l_msg_data) ;
1556 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
1557 raise IEM_ACCT_EMAILPROC_NOT_DELETED;
1558 end if;
1559
1560 end if;
1561
1562
1563 -- Standard Check Of p_commit.
1564 IF FND_API.To_Boolean(p_commit) THEN
1565 COMMIT WORK;
1566 END IF;
1567
1568 -- Standard callto get message count and if count is 1, get message info.
1569 FND_MSG_PUB.Count_And_Get
1570
1571 ( p_count => x_msg_count,
1572 p_data => x_msg_data
1573 );
1574
1575 EXCEPTION
1576
1577 WHEN IEM_ACCT_EMAILPROC_NOT_UPDATED THEN
1578 ROLLBACK TO update_wrap_acct_emailproc_PVT;
1579 FND_MESSAGE.SET_NAME('IEM','IEM_ACCT_EMAILPROC_NOT_UPDATED');
1580 FND_MSG_PUB.Add;
1581 x_return_status := FND_API.G_RET_STS_ERROR ;
1582
1583 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1584
1585 WHEN IEM_ACCT_EMAILPROC_NOT_DELETED THEN
1586 ROLLBACK TO update_wrap_acct_emailproc_PVT;
1587 FND_MESSAGE.SET_NAME('IEM','IEM_ACCT_EMAILPROC_NOT_DELETED');
1588 FND_MSG_PUB.Add;
1589 x_return_status := FND_API.G_RET_STS_ERROR ;
1590 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1591
1592 WHEN FND_API.G_EXC_ERROR THEN
1593 ROLLBACK TO update_wrap_acct_emailproc_PVT;
1594 x_return_status := FND_API.G_RET_STS_ERROR ;
1595 FND_MSG_PUB.Count_And_Get
1596 ( p_count => x_msg_count,
1597 p_data => x_msg_data
1598 );
1599
1600 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1601 ROLLBACK TO update_wrap_acct_emailproc_PVT;
1602 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1603 FND_MSG_PUB.Count_And_Get
1604 ( p_count => x_msg_count,
1605 p_data => x_msg_data
1606 );
1607
1608 WHEN OTHERS THEN
1609 ROLLBACK TO update_wrap_acct_emailproc_PVT;
1610 x_return_status := FND_API.G_RET_STS_ERROR;
1611
1612 IF FND_MSG_PUB.Check_Msg_Level
1613 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1614 THEN
1615 FND_MSG_PUB.Add_Exc_Msg
1616 ( G_PKG_NAME,
1617 l_api_name
1618 );
1619 END IF;
1620 FND_MSG_PUB.Count_And_Get
1621 ( p_count => x_msg_count,
1622 p_data => x_msg_data
1623 );
1624 END update_wrap_account_emailprocs;
1625
1626
1627 -- Enter further code below as specified in the Package spec.
1628 PROCEDURE delete_item_emailproc
1629 (p_api_version_number IN NUMBER,
1630 P_init_msg_list IN VARCHAR2 := null,
1631 p_commit IN VARCHAR2 := null,
1632 p_emailproc_id IN NUMBER,
1633 p_rule_type IN VARCHAR2,
1634 x_return_status OUT NOCOPY VARCHAR2,
1635 x_msg_count OUT NOCOPY NUMBER,
1636 x_msg_data OUT NOCOPY VARCHAR2)
1637 IS
1638 i INTEGER;
1639 l_api_name varchar2(30):='delete_item_batch';
1640 l_api_version_number number:=1.0;
1641
1642 CURSOR acct_id_cursor( l_emailproc_id IN NUMBER ) IS
1643 select email_account_id from iem_account_emailprocs where emailproc_id = l_emailproc_id;
1644
1645 CURSOR action_id_cursor( l_emailproc_id IN NUMBER ) IS
1646 select action_id from iem_actions where emailproc_id = l_emailproc_id;
1647 IEM_ROUTE_NOT_DELETED EXCEPTION;
1648 BEGIN
1649
1650
1651
1652 --Standard Savepoint
1653 SAVEPOINT delete_item_batch;
1654
1655 -- Standard call to check for call compatibility.
1656 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
1657 p_api_version_number,
1658 l_api_name,
1659 G_PKG_NAME)
1660 THEN
1661 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1662 END IF;
1663
1664 --Initialize the message list if p_init_msg_list is set to TRUE
1665 If FND_API.to_Boolean(p_init_msg_list) THEN
1666 FND_MSG_PUB.initialize;
1667 END IF;
1668
1669 --Initialize API status return
1670 x_return_status := FND_API.G_RET_STS_SUCCESS;
1671
1672 --Actual API starts here
1673 DELETE
1674 FROM IEM_EMAILPROCS
1675 WHERE emailproc_id = p_emailproc_id;
1676
1677 if SQL%NOTFOUND then
1678 raise IEM_ROUTE_NOT_DELETED;
1679 end if;
1680
1681 --Delete the accounts, rules associated with this route
1682 --if ( p_route_ids_tbl.count <> 0 ) then
1683
1684 -- FOR i IN p_route_ids_tbl.FIRST..p_route_ids_tbl.LAST LOOP
1685
1686 -- update priority after delete an account_route
1687 --Fixme for account rule type association
1688 FOR acct_id IN acct_id_cursor(p_emailproc_id) LOOP
1689 Update iem_account_emailprocs set priority=priority-1
1690
1691 where email_account_id=acct_id.email_account_id
1692 and emailproc_id in
1693 ( select emailproc_id
1694 from iem_emailprocs
1695 where rule_type=p_rule_type )
1696 and priority > (Select priority from iem_account_emailprocs
1697 where emailproc_id=p_emailproc_id and email_account_id = acct_id.email_account_id);
1698 END LOOP;
1699
1700 --remove association
1701 DELETE
1702 FROM iem_account_emailprocs
1703 WHERE emailproc_id = p_emailproc_id;
1704
1705 --remove iem_emailproc_rules
1706 DELETE
1707 FROM IEM_EMAILPROC_RULES
1708 WHERE emailproc_id=p_emailproc_id;
1709
1710 --remove iem_action_dtls
1711 FOR v_action_id IN action_id_cursor(p_emailproc_id) LOOP
1712 delete from iem_action_dtls where action_id = v_action_id.action_id;
1713 END LOOP;
1714
1715 --remove iem_email_actions
1716 delete from iem_actions where emailproc_id = p_emailproc_id;
1717
1718
1719 --Standard check of p_commit
1720 IF FND_API.to_Boolean(p_commit) THEN
1721 COMMIT WORK;
1722 END IF;
1723
1724
1725 EXCEPTION
1726
1727 WHEN IEM_ROUTE_NOT_DELETED THEN
1728 ROLLBACK TO delete_item_batch;
1729 x_return_status := FND_API.G_RET_STS_ERROR;
1730 FND_MESSAGE.SET_NAME('IEM', 'IEM_ROUTE_NOT_DELETED');
1731
1732 FND_MSG_PUB.ADD;
1733 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1734
1735 WHEN FND_API.G_EXC_ERROR THEN
1736 ROLLBACK TO delete_item_batch;
1737 x_return_status := FND_API.G_RET_STS_ERROR ;
1738 FND_MSG_PUB.Count_And_Get
1739 ( p_count => x_msg_count,p_data => x_msg_data);
1740
1741
1742 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1743 ROLLBACK TO delete_item_batch;
1744 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1745 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,p_data => x_msg_data);
1746
1747
1748 WHEN OTHERS THEN
1749 ROLLBACK TO delete_item_batch;
1750 x_return_status := FND_API.G_RET_STS_ERROR;
1751 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1752 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME , l_api_name);
1753 END IF;
1754
1755 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count ,p_data => x_msg_data);
1756
1757 END delete_item_emailproc;
1758
1759
1760 PROCEDURE delete_acct_emailproc_by_acct
1761 (p_api_version_number IN NUMBER,
1762 P_init_msg_list IN VARCHAR2 := null,
1763 p_commit IN VARCHAR2 := null,
1764 p_email_account_id IN NUMBER,
1765 x_return_status OUT NOCOPY VARCHAR2,
1766 x_msg_count OUT NOCOPY NUMBER,
1767 x_msg_data OUT NOCOPY VARCHAR2)
1768 IS
1769 i INTEGER;
1770 l_api_name varchar2(30):='delete_acct_emailproc_by_acct';
1771 l_api_version_number number:=1.0;
1772
1773 BEGIN
1774
1775 --Standard Savepoint
1776 SAVEPOINT delete_acct_emailproc_by_acct;
1777
1778 -- Standard call to check for call compatibility.
1779 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
1780 p_api_version_number,
1781 l_api_name,
1782 G_PKG_NAME)
1783 THEN
1784 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1785 END IF;
1786
1787
1788
1789 --Initialize the message list if p_init_msg_list is set to TRUE
1790 If FND_API.to_Boolean(p_init_msg_list) THEN
1791 FND_MSG_PUB.initialize;
1792 END IF;
1793
1794 --Initialize API status return
1795 x_return_status := FND_API.G_RET_STS_SUCCESS;
1796
1797 --Actual API starts here
1798 DELETE
1799 FROM IEM_ACCOUNT_EMAILPROCS
1800 WHERE email_account_id = p_email_account_id;
1801
1802
1803 --Standard check of p_commit
1804 IF FND_API.to_Boolean(p_commit) THEN
1805 COMMIT WORK;
1806 END IF;
1807
1811 EXCEPTION
1808 FND_MSG_PUB.Count_And_Get
1809 ( p_count => x_msg_count,p_data => x_msg_data);
1810
1812 WHEN FND_API.G_EXC_ERROR THEN
1813 ROLLBACK TO delete_acct_emailproc_by_acct;
1814 x_return_status := FND_API.G_RET_STS_ERROR ;
1815 FND_MSG_PUB.Count_And_Get
1816
1817 ( p_count => x_msg_count,p_data => x_msg_data);
1818
1819 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1820 ROLLBACK TO delete_acct_emailproc_by_acct;
1821 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1822 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,p_data => x_msg_data);
1823
1824
1825 WHEN OTHERS THEN
1826 ROLLBACK TO delete_acct_emailproc_by_acct;
1827 x_return_status := FND_API.G_RET_STS_ERROR;
1828 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1829 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME , l_api_name);
1830
1831 END IF;
1832 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count ,p_data => x_msg_data);
1833
1834 END delete_acct_emailproc_by_acct;
1835
1836 END IEM_EMAILPROC_PVT; -- Package Body IEM_EMAILPROC_PVT