[Home] [Help]
PACKAGE BODY: APPS.IEM_EMAILPROC_PVT
Source
1 PACKAGE BODY IEM_EMAILPROC_PVT AS
2 /* $Header: iemvrulb.pls 120.1 2005/07/26 15:19:58 appldev 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
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,
295 b.action, a.enabled_flag
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;
414 l_documents(x).enabled_flag := v_emailProcs.enabled_flag;
415 x := x + 1;
416 END LOOP;
417
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
560 and priority >
561 ( Select priority from iem_account_emailprocs
562 where emailproc_id=p_emailProc_id
563 and email_account_id=p_acct_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_parameter_tag_tbl IN jtf_varchar2_Table_100,
636
637 x_return_status OUT NOCOPY VARCHAR2,
638 x_msg_count OUT NOCOPY NUMBER,
639 x_msg_data OUT NOCOPY VARCHAR2 )is
640
641 l_api_name VARCHAR2(255):='update_emailproc_wrap';
642 l_api_version_number NUMBER:=1.0;
643
644 l_return_status VARCHAR2(20) := FND_API.G_RET_STS_SUCCESS;
645 l_msg_count NUMBER := 0;
646 l_msg_data VARCHAR2(2000);
647
648 l_action_id IEM_ACTIONS.ACTION_ID%type;
649 l_param2_count NUMBER := 0;
650 l_param_tag_count NUMBER := 0;
651
652 l_param2 VARCHAR2(256);
653 l_param_tag VARCHAR2(30);
654
655 IEM_NO_ROUTE_UPDATE EXCEPTION;
656 IEM_NO_RULE_UPDATE EXCEPTION;
657
658 IEM_RULE_NOT_DELETED EXCEPTION;
659 IEM_ROUTE_RULE_NOT_CREATED EXCEPTION;
660 IEM_ADMIN_EMAILPROC_NO_RULE EXCEPTION;
661 l_IEM_FAIL_TO_CALL EXCEPTION;
662 IEM_ACTION_DTLS_NOT_CREATE EXCEPTION;
663 IEM_ADM_AUTOPRC_NO_PARAMETERS EXCEPTION;
664 IEM_ADM_INVALID_PROC_NAME EXCEPTION;
665 IEM_PROC_REDIRECT_SAME_ACCT EXCEPTION;
666
667 l_route NUMBER;
668 l_rule_count NUMBER;
669 l_proc_name VARCHAR2(256);
670 l_all_emails VARCHAR2(1);
671 l_redirect_same_acct NUMBER;
672
673 BEGIN
674 -- Standard Start of API savepoint
675 SAVEPOINT update_item_wrap;
676
677 -- Standard call to check for call compatibility.
678 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
679
680 p_api_version_number,
681 l_api_name,
682 G_PKG_NAME)
683 THEN
684 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
685
686 END IF;
687
688 -- Initialize message list if p_init_msg_list is set to TRUE.
689 IF FND_API.to_Boolean( p_init_msg_list )
690 THEN
691 FND_MSG_PUB.initialize;
692 END IF;
693
694
695 -- Initialize API return status to SUCCESS
696 x_return_status := FND_API.G_RET_STS_SUCCESS;
697
698 --API Body
699
700
701 --check if the route_id exist before update
702 select count(*) into l_route from iem_emailprocs where emailproc_id = p_emailproc_id;
703
704 if l_route < 1 then
705 raise IEM_NO_ROUTE_UPDATE;
706 end if;
707
708
709 --Auto-Processing: Execute Procedure/workflow validation
710 if ( p_action = 'EXECPROCEDURE' ) then
711 if ( p_parameter1_tbl.count < 1 ) then
712 raise IEM_ADM_AUTOPRC_NO_PARAMETERS;
713 elsif ( ( p_parameter1_tbl(1) is null) or (p_parameter1_tbl(1) ='')) then
714 raise IEM_ADM_AUTOPRC_NO_PARAMETERS;
715 else
716 IEM_TAG_RUN_PROC_PVT.validProcedure(
717 p_api_version_number => P_Api_Version_Number,
718 p_init_msg_list => FND_API.G_FALSE,
719 p_commit => P_Commit,
720 p_ProcName => p_parameter1_tbl(1),
721 x_return_status => l_return_status,
722 x_msg_count => l_msg_count,
723 x_msg_data => l_msg_data
724 );
725 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
726 raise IEM_ADM_INVALID_PROC_NAME;
727 end if;
728 end if;
729 --Auto-Redirect: Do not auto-redirect to the same email account validation
730 elsif ( p_action = 'AUTOREDIRECT_INTERNAL' ) then
731 if ( p_parameter1_tbl.count < 1 ) then
732 raise IEM_ADM_AUTOPRC_NO_PARAMETERS;
733 elsif ( ( p_parameter1_tbl(1) is null) or (p_parameter1_tbl(1) ='')) then
734 raise IEM_ADM_AUTOPRC_NO_PARAMETERS;
735 else
736 select count(*) into l_redirect_same_acct
737 from iem_account_emailprocs
738 where email_account_id=p_parameter1_tbl(1) and emailproc_id=p_emailproc_id;
739
740 if ( l_redirect_same_acct > 0 ) then
741 raise IEM_PROC_REDIRECT_SAME_ACCT;
742 end if;
743 end if;
744
745 end if;
746
747 --update iem_routes table
748
749 iem_emailproc_hdl_pvt.update_item_emailproc(
750 p_api_version_number => l_api_version_number,
751 p_init_msg_list => FND_API.G_FALSE,
752 p_commit => FND_API.G_FALSE,
753 p_emailproc_id => p_emailproc_id,
754 p_name => p_name,
755 p_description =>p_description,
756 p_ruling_chain =>p_ruling_chain,
757 p_all_email => p_all_email,
758 p_rule_type => p_rule_type,
759 x_return_status => l_return_status,
760 x_msg_count => l_msg_count,
761 x_msg_data => l_msg_data);
762
763
764 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
765 raise l_IEM_FAIL_TO_CALL;
766 end if;
767
768
769 --update iem_route_rules table
770 if ( p_update_rule_ids_tbl.count <>0 ) then
771
772 FOR i IN p_update_rule_ids_tbl.FIRST..p_update_rule_ids_tbl.LAST loop
773 iem_emailproc_hdl_pvt.update_item_rule(p_api_version_number => l_api_version_number,
774 p_init_msg_list => FND_API.G_FALSE,
775 p_commit => FND_API.G_FALSE,
776 p_emailproc_rule_id => p_update_rule_ids_tbl(i),
777 p_key_type_code =>p_update_rule_keys_tbl(i),
778 p_operator_type_code =>p_update_rule_operators_tbl(i),
779 p_value => p_update_rule_values_tbl(i),
780 x_return_status => l_return_status,
781 x_msg_count => l_msg_count,
782 x_msg_data => l_msg_data);
783
784 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
785 raise IEM_NO_RULE_UPDATE;
786 end if;
787 end loop;
788 end if;
789
790
791 -- update by deleting rules from iem_route_rules table
792 if ( p_remove_rule_ids_tbl.count <> 0 ) then
793 FORALL i IN p_remove_rule_ids_tbl.FIRST..p_remove_rule_ids_tbl.LAST
794 DELETE
795 FROM IEM_EMAILPROC_RULES
796 WHERE emailproc_rule_id = p_remove_rule_ids_tbl(i);
797
798 if SQL%NOTFOUND then
799 raise IEM_RULE_NOT_DELETED;
800 end if;
801 end if;
802
803 if ( p_new_rule_keys_tbl.count <> 0 ) then
804 FOR i IN p_new_rule_keys_tbl.FIRST..p_new_rule_keys_tbl.LAST LOOP
805 iem_emailproc_hdl_pvt.create_item_emailproc_rules (p_api_version_number=>p_api_version_number,
806 p_init_msg_list => p_init_msg_list,
807 p_commit => p_commit,
808 p_emailproc_id => p_emailproc_id,
809 p_key_type_code => p_new_rule_keys_tbl(i),
810 p_operator_type_code => p_new_rule_operators_tbl(i),
811 p_value =>p_new_rule_values_tbl(i),
812
813 x_return_status =>l_return_status,
814 x_msg_count => l_msg_count,
815 x_msg_data => l_msg_data);
816
817 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
818 raise IEM_ROUTE_RULE_NOT_CREATED;
819 end if;
820 END LOOP;
821 end if;
822
823 -- check if exist at least one rule for each route
824
825 select all_email into l_all_emails
826 from iem_emailprocs where emailproc_id = p_emailproc_id;
827
828 if l_all_emails<>'Y' then
829 select count(*) into l_rule_count from iem_emailproc_rules where emailproc_id = p_emailproc_id;
830
831 if l_rule_count < 1 then
832 raise IEM_ADMIN_EMAILPROC_NO_RULE;
833 end if;
834 end if;
835
836 -- updating action
837 select action_id into l_action_id from iem_actions where emailproc_id = p_emailproc_id;
838
839 if ( p_action <> FND_API.G_MISS_CHAR ) or ( p_action is not null ) then
840 update iem_actions set action=p_action where action_id = l_action_id;
841 end if;
842
843 -- updating action dtls
844 delete from iem_action_dtls where action_id = l_action_id;
845
846 if ( p_parameter2_tbl is not null ) then
847 l_param2_count := p_parameter2_tbl.count;
848 end if;
849
850 if ( p_parameter_tag_tbl is not null ) then
851 l_param_tag_count := p_parameter_tag_tbl.count;
852 end if;
853
854 IF ( p_parameter1_tbl is not null ) and ( p_parameter1_tbl.count > 0 ) then
855
856 FOR i IN p_parameter1_tbl.FIRST..p_parameter1_tbl.LAST loop
857
858 if ( i <= l_param2_count ) then
859 l_param2 := p_parameter2_tbl(i);
860 else
861 l_param2 := null; --FND_API.G_MISS_CHAR;--null; --FND_API.G_MISS_CHAR;
862 end if;
863
864
865 if ( i <= l_param_tag_count ) then
866 l_param_tag := p_parameter_tag_tbl(i);
867 else
868 l_param_tag := null; --FND_API.G_MISS_CHAR;--null; --FND_API.G_MISS_CHAR;
869 end if;
870
871 iem_emailproc_hdl_pvt.create_item_action_dtls (
872 p_api_version_number=>p_api_version_number,
873 p_init_msg_list => p_init_msg_list,
874 p_commit => FND_API.G_FALSE,
875 p_action_id => l_action_id,
876 p_param1 => p_parameter1_tbl(i),
877 p_param2 => l_param2,
878 p_param_tag => l_param_tag,
879 x_return_status =>l_return_status,
880 x_msg_count => l_msg_count,
881 x_msg_data => l_msg_data);
882
883 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
884 raise IEM_ACTION_DTLS_NOT_CREATE;
885 end if;
886 end loop;
887 END IF;
888
889 commit work;
890
891 EXCEPTION
892 WHEN l_IEM_FAIL_TO_CALL THEN
893 ROLLBACK TO update_item_wrap;
894 -- FND_MESSAGE.SET_NAME('IEM','IEM_NO_ROUTE_UPDATE');
895
896 -- FND_MSG_PUB.Add;
897 x_return_status := FND_API.G_RET_STS_ERROR ;
898 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
899
900 WHEN IEM_ADM_AUTOPRC_NO_PARAMETERS THEN
901 ROLLBACK TO update_item_wrap;
902 FND_MESSAGE.SET_NAME('IEM','IEM_ADM_AUTOPRC_NO_PARAMETERS');
903
904 FND_MSG_PUB.Add;
905 x_return_status := FND_API.G_RET_STS_ERROR ;
906 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
907
908 WHEN IEM_PROC_REDIRECT_SAME_ACCT THEN
909 ROLLBACK TO update_item_wrap;
910 FND_MESSAGE.SET_NAME('IEM','IEM_PROC_REDIRECT_SAME_ACCT');
911
912 FND_MSG_PUB.Add;
913 x_return_status := FND_API.G_RET_STS_ERROR ;
914 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
915
916 WHEN IEM_ADM_INVALID_PROC_NAME THEN
917 ROLLBACK TO update_item_wrap;
918 -- FND_MESSAGE.SET_NAME('IEM','IEM_ADM_INVALID_PROC_NAME');
919
920 -- FND_MSG_PUB.Add;
921 x_return_status := FND_API.G_RET_STS_ERROR ;
922 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
923
924 WHEN IEM_NO_ROUTE_UPDATE THEN
925 ROLLBACK TO update_item_wrap;
926 FND_MESSAGE.SET_NAME('IEM','IEM_NO_ROUTE_UPDATE');
927
928 FND_MSG_PUB.Add;
929 x_return_status := FND_API.G_RET_STS_ERROR ;
930 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
931 WHEN IEM_NO_RULE_UPDATE THEN
932 ROLLBACK TO update_item_wrap;
933 FND_MESSAGE.SET_NAME('IEM','IEM_NO_RULE_UPDATE');
934 FND_MSG_PUB.Add;
935 x_return_status := FND_API.G_RET_STS_ERROR ;
936 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
937
938 WHEN IEM_RULE_NOT_DELETED THEN
939
940 ROLLBACK TO update_item_wrap;
941 FND_MESSAGE.SET_NAME('IEM','IEM_RULE_NOT_DELETED');
942 FND_MSG_PUB.Add;
943 x_return_status := FND_API.G_RET_STS_ERROR ;
944
945 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
946
947 WHEN IEM_ROUTE_RULE_NOT_CREATED THEN
948 ROLLBACK TO update_item_wrap;
949 FND_MESSAGE.SET_NAME('IEM','IEM_ROUTE_RULE_NOT_CREATED');
950 FND_MSG_PUB.Add;
951 x_return_status := FND_API.G_RET_STS_ERROR ;
952 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
953
954
955 WHEN IEM_ADMIN_EMAILPROC_NO_RULE THEN
956 ROLLBACK TO update_item_wrap;
957 FND_MESSAGE.SET_NAME('IEM','IEM_ADMIN_EMAILPROC_NO_RULE');
958 FND_MSG_PUB.Add;
959
960 x_return_status := FND_API.G_RET_STS_ERROR ;
961 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
962
963 WHEN IEM_ACTION_DTLS_NOT_CREATE THEN
964 ROLLBACK TO update_item_wrap;
965 FND_MESSAGE.SET_NAME('IEM','IEM_ACTION_DTLS_NOT_CREATE');
966 FND_MSG_PUB.Add;
967
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 WHEN FND_API.G_EXC_ERROR THEN
971 ROLLBACK TO update_item_wrap;
972 x_return_status := FND_API.G_RET_STS_ERROR ;
973 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,p_data => x_msg_data);
974
975
976 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
977 ROLLBACK TO update_item_wrap;
978 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
979 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
980
981
982 WHEN OTHERS THEN
983 ROLLBACK TO update_item_wrap;
984 x_return_status := FND_API.G_RET_STS_ERROR;
985 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
986 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME , l_api_name);
987 END IF;
988
989
990 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count, p_data => x_msg_data );
991
992 END update_emailproc_wrap;
993
994 PROCEDURE create_emailproc_wrap (
995 p_api_version_number IN NUMBER,
996 p_init_msg_list IN VARCHAR2 := null,
997 p_commit IN VARCHAR2 := null,
998 p_route_name IN VARCHAR2,
999 p_route_description IN VARCHAR2:= null,
1000 p_route_boolean_type_code IN VARCHAR2,
1001 p_rule_type IN VARCHAR2,
1002 p_action IN VARCHAR2,
1003 p_all_email IN VARCHAR2,
1004 p_rule_key_typecode_tbl IN jtf_varchar2_Table_100 ,
1005 p_rule_operator_typecode_tbl IN jtf_varchar2_Table_100,
1006 p_rule_value_tbl IN jtf_varchar2_Table_300,
1007 p_parameter1_tbl IN jtf_varchar2_Table_300,
1008 p_parameter2_tbl IN jtf_varchar2_Table_300,
1009 p_parameter_tag_tbl IN jtf_varchar2_Table_100,
1010 x_return_status OUT NOCOPY VARCHAR2,
1011 x_msg_count OUT NOCOPY NUMBER,
1012 x_msg_data OUT NOCOPY VARCHAR2 ) is
1013
1014
1015 l_api_name VARCHAR2(255):='create_emailproc_wrap';
1016 l_api_version_number NUMBER:=1.0;
1017
1018 l_emailproc_id IEM_EMAILPROCS.EMAILPROC_ID%TYPE;
1019 l_route_rule_id IEM_EMAILPROC_RULES.EMAILPROC_RULE_ID%TYPE;
1020 l_action_id IEM_ACTIONS.ACTION_ID%type;
1021 l_param2_count NUMBER := 0;
1022 l_param_tag_count NUMBER := 0;
1023
1024 l_param2 VARCHAR2(256);
1025 l_param_tag VARCHAR2(30);
1026 l_userid NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('USER_ID')) ;
1027 l_login NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('LOGIN_ID')) ;
1028
1029 l_return_status VARCHAR2(20) := FND_API.G_RET_STS_SUCCESS;
1030 l_msg_count NUMBER := 0;
1031 l_msg_data VARCHAR2(2000);
1032
1033 IEM_EMAILPROC_NOT_CREATED EXCEPTION;
1034 IEM_EMAILPROC_RULE_NOT_CREATED EXCEPTION;
1035 IEM_ACTION_NOT_CREATED EXCEPTION;
1036 IEM_ACTION_DTLS_NOT_CREATE EXCEPTION;
1037 IEM_ADM_AUTOPRC_NO_PARAMETERS EXCEPTION;
1038 IEM_ADM_INVALID_PROC_NAME EXCEPTION;
1039 BEGIN
1040
1041 -- Standard Start of API savepoint
1042 SAVEPOINT create_item_wrap;
1043
1044 -- Standard call to check for call compatibility.
1045 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
1046 p_api_version_number,
1047 l_api_name,
1048 G_PKG_NAME)
1049 THEN
1050
1051 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1052 END IF;
1053
1054
1055 -- Initialize message list if p_init_msg_list is set to TRUE.
1056 IF FND_API.to_Boolean( p_init_msg_list )
1057 THEN
1058 FND_MSG_PUB.initialize;
1059 END IF;
1060
1061 -- Initialize API return status to SUCCESS
1062 x_return_status := FND_API.G_RET_STS_SUCCESS;
1063
1064 --API Body
1065 --Auto-Processing: Execute Procedure/workflow validation
1066 if ( p_action = 'EXECPROCEDURE' ) then
1067 if ( p_parameter1_tbl.count < 1 ) then
1068 raise IEM_ADM_AUTOPRC_NO_PARAMETERS;
1069 elsif ( ( p_parameter1_tbl(1) is null) or (p_parameter1_tbl(1) ='')) then
1070 raise IEM_ADM_AUTOPRC_NO_PARAMETERS;
1071 else
1072 IEM_TAG_RUN_PROC_PVT.validProcedure(
1073 p_api_version_number => P_Api_Version_Number,
1074 p_init_msg_list => FND_API.G_FALSE,
1075 p_commit => P_Commit,
1076 p_ProcName => p_parameter1_tbl(1),
1077 x_return_status => l_return_status,
1078 x_msg_count => l_msg_count,
1079 x_msg_data => l_msg_data
1080 );
1081 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
1082 raise IEM_ADM_INVALID_PROC_NAME;
1083 end if;
1084 end if;
1085 end if;
1086
1087 --Create iem_emailprocs
1088 iem_emailproc_hdl_pvt.create_item_emailprocs (
1089 p_api_version_number=>p_api_version_number,
1090 p_init_msg_list => p_init_msg_list,
1091 p_commit => FND_API.G_FALSE,
1092 p_name => p_route_name,
1093 p_description => p_route_description,
1094 p_boolean_type_code =>p_route_boolean_type_code,
1095 p_all_email => p_all_email,
1096 p_rule_type => p_rule_type,
1097 x_emailproc_id => l_emailproc_id,
1098 x_return_status =>l_return_status,
1099 x_msg_count => l_msg_count,
1100 x_msg_data => l_msg_data);
1101
1102
1103 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
1104 raise IEM_EMAILPROC_NOT_CREATED;
1105 end if;
1106
1107
1108 --Create iem_emailproc_rules
1109 if p_rule_key_typecode_tbl.count<> 0 then
1110 FOR i IN p_rule_key_typecode_tbl.FIRST..p_rule_key_typecode_tbl.LAST loop
1111
1112 iem_emailproc_hdl_pvt.create_item_emailproc_rules (
1113 p_api_version_number=>p_api_version_number,
1114 p_init_msg_list => p_init_msg_list,
1115 p_commit => FND_API.G_FALSE,
1116 p_emailproc_id => l_emailproc_id,
1117 p_key_type_code => p_rule_key_typecode_tbl(i),
1118 p_operator_type_code => p_rule_operator_typecode_tbl(i),
1119 p_value =>p_rule_value_tbl(i),
1120 x_return_status =>l_return_status,
1121 x_msg_count => l_msg_count,
1122 x_msg_data => l_msg_data);
1123
1124 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
1125 raise IEM_EMAILPROC_RULE_NOT_CREATED;
1126 end if;
1127 end loop;
1128 end if;
1129
1130 -- create iem_actions
1131 -- IF ( p_rule_type = 'AUTOACKNOWLEDGE' or p_rule_type = 'AUTOPROCESSING' or p_rule_type = 'AUTORRRS' ) then
1132 iem_emailproc_hdl_pvt.create_item_actions (
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_action_name => p_action,
1138 x_action_id => l_action_id,
1139 x_return_status =>l_return_status,
1140 x_msg_count => l_msg_count,
1141 x_msg_data => l_msg_data);
1142
1143 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
1144 raise IEM_ACTION_NOT_CREATED;
1145 end if;
1146
1147
1148 if ( p_parameter2_tbl is not null ) then
1149 l_param2_count := p_parameter2_tbl.count;
1150 end if;
1151
1152 if ( p_parameter_tag_tbl is not null ) then
1153 l_param_tag_count := p_parameter_tag_tbl.count;
1154 end if;
1155
1156 IF ( p_parameter1_tbl is not null ) and ( p_parameter1_tbl.count > 0 ) then
1157
1158
1159 FOR i IN p_parameter1_tbl.FIRST..p_parameter1_tbl.LAST loop
1160
1161 if ( i <= l_param2_count ) then
1162 l_param2 := p_parameter2_tbl(i);
1163 else
1164 l_param2 := null; --FND_API.G_MISS_CHAR;--null; --FND_API.G_MISS_CHAR;
1165 end if;
1166
1167
1168 if ( i <= l_param_tag_count ) then
1169 l_param_tag := p_parameter_tag_tbl(i);
1170 else
1171 l_param_tag := null; --FND_API.G_MISS_CHAR;--null; --FND_API.G_MISS_CHAR;
1172 end if;
1173
1174 iem_emailproc_hdl_pvt.create_item_action_dtls (
1175 p_api_version_number=>p_api_version_number,
1176 p_init_msg_list => p_init_msg_list,
1177 p_commit => FND_API.G_FALSE,
1178 p_action_id => l_action_id,
1179 p_param1 => p_parameter1_tbl(i),
1180 p_param2 => l_param2,
1181 p_param_tag => l_param_tag,
1182 x_return_status =>l_return_status,
1183 x_msg_count => l_msg_count,
1184 x_msg_data => l_msg_data);
1185
1186 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
1187 raise IEM_ACTION_DTLS_NOT_CREATE;
1188 end if;
1189 end loop;
1190 END IF;
1191
1192 -- END IF;
1193
1194 IF FND_API.To_Boolean(p_commit) THEN
1195 COMMIT WORK;
1196 END IF;
1197
1198 EXCEPTION
1199 WHEN IEM_EMAILPROC_NOT_CREATED THEN
1200 ROLLBACK TO create_item_wrap;
1201 x_return_status := FND_API.G_RET_STS_ERROR ;
1202 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1203
1204 WHEN IEM_ADM_AUTOPRC_NO_PARAMETERS THEN
1205 ROLLBACK TO create_item_wrap;
1206 FND_MESSAGE.SET_NAME('IEM','IEM_ADM_AUTOPRC_NO_PARAMETERS');
1207 FND_MSG_PUB.Add;
1208 x_return_status := FND_API.G_RET_STS_ERROR ;
1209 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1210
1211 WHEN IEM_ADM_INVALID_PROC_NAME THEN
1212 ROLLBACK TO create_item_wrap;
1213 -- FND_MESSAGE.SET_NAME('IEM','IEM_ADM_INVALID_PROC_NAME');
1214 -- FND_MSG_PUB.Add;
1215 x_return_status := FND_API.G_RET_STS_ERROR ;
1216 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1217
1218 WHEN IEM_EMAILPROC_RULE_NOT_CREATED THEN
1219 ROLLBACK TO create_item_wrap;
1220 FND_MESSAGE.SET_NAME('IEM','IEM_EMAILPROC_RULE_NOT_CREATED');
1221 FND_MSG_PUB.Add;
1222 x_return_status := FND_API.G_RET_STS_ERROR ;
1223 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1224
1225 WHEN IEM_ACTION_NOT_CREATED THEN
1226 ROLLBACK TO create_item_wrap;
1227 FND_MESSAGE.SET_NAME('IEM','IEM_ACTION_NOT_CREATED');
1228 FND_MSG_PUB.Add;
1229 x_return_status := FND_API.G_RET_STS_ERROR ;
1230 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1231 WHEN IEM_ACTION_DTLS_NOT_CREATE THEN
1232
1233 ROLLBACK TO create_item_wrap;
1234 FND_MESSAGE.SET_NAME('IEM','IEM_ACTION_DTLS_NOT_CREATE');
1235 FND_MSG_PUB.Add;
1236 x_return_status := FND_API.G_RET_STS_ERROR ;
1237 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1238
1239 WHEN FND_API.G_EXC_ERROR THEN
1240 ROLLBACK TO create_item_wrap;
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
1245 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1246
1247 ROLLBACK TO create_item_wrap;
1248 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1249 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1250
1251 WHEN OTHERS THEN
1252 ROLLBACK TO create_item_wrap;
1253 x_return_status := FND_API.G_RET_STS_ERROR;
1254 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1255 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME , l_api_name);
1256 END IF;
1257
1258 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count, p_data => x_msg_data );
1259
1260 END create_emailproc_wrap;
1261
1262
1263
1264 PROCEDURE create_wrap_account_emailprocs (
1265 p_api_version_number IN NUMBER,
1266 p_init_msg_list IN VARCHAR2 := null,
1267 p_commit IN VARCHAR2 := null,
1268 p_email_account_id IN NUMBER,
1269 p_emailproc_id IN NUMBER,
1270 p_enabled_flag IN VARCHAR2,
1271 p_priority IN NUMBER,
1272 x_return_status OUT NOCOPY VARCHAR2,
1273 x_msg_count OUT NOCOPY NUMBER,
1274 x_msg_data OUT NOCOPY VARCHAR2
1275 ) is
1276 l_api_name VARCHAR2(255):='create_wrap_account_emailprocs';
1277 l_api_version_number NUMBER:=1.0;
1278 l_count number;
1279 l_account number;
1280 l_redirect_same_acct number := 0;
1281
1282 l_return_status VARCHAR2(20) := FND_API.G_RET_STS_SUCCESS;
1283 l_msg_count NUMBER := 0;
1284 l_msg_data VARCHAR2(2000);
1285
1286 IEM_ADMIN_ROUTE_NOT_EXIST EXCEPTION;
1287 IEM_ADMIN_ACCOUNT_NOT_EXIST EXCEPTION;
1288 IEM_ACCOUNT_ROUTE_NOT_UPDATED EXCEPTION;
1289 IEM_NOT_REDIRECT_SAME_ACCT EXCEPTION;
1290 BEGIN
1291 -- Standard Start of API savepoint
1292 SAVEPOINT create_wrap_account_routes_PVT;
1293
1294 -- Standard call to check for call compatibility.
1295 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
1296
1297 p_api_version_number,
1298 l_api_name,
1299 G_PKG_NAME)
1300 THEN
1301 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1302 END IF;
1303
1304 -- Initialize message list if p_init_msg_list is set to TRUE.
1305 IF FND_API.to_Boolean( p_init_msg_list )
1306 THEN
1307
1308 FND_MSG_PUB.initialize;
1309 END IF;
1310
1311
1312 -- Initialize API return status to SUCCESS
1313 x_return_status := FND_API.G_RET_STS_SUCCESS;
1314
1315
1316 -- check if the route_id exist in iem_routes
1317 select count(*) into l_count from iem_emailprocs
1318 where emailproc_id = p_emailproc_id;
1319
1320 if l_count < 1 then
1321
1322 raise IEM_ADMIN_ROUTE_NOT_EXIST;
1323 end if;
1324
1325 -- check if the account_id exist in iem_email_accounts
1326 -- Changed for 115.11 schema compliance
1327 select count(*) into l_account from iem_mstemail_accounts
1328 where email_account_id = p_email_account_id;
1329
1330 if l_account < 1 then
1331 raise IEM_ADMIN_ACCOUNT_NOT_EXIST;
1332 end if;
1333
1334 select count(*) into l_redirect_same_acct
1335 from iem_emailprocs a, iem_actions b, iem_action_dtls c
1336 where a.emailproc_id = p_emailproc_id and a.emailproc_id=b.emailproc_id
1337 and a.rule_type='AUTOREDIRECT' and b.action='AUTOREDIRECT_INTERNAL'
1338 and b.action_id=c.action_id and c.parameter1=to_char(p_email_account_id);
1339
1340 if ( l_redirect_same_acct > 0 ) then
1341 raise IEM_NOT_REDIRECT_SAME_ACCT;
1342 end if;
1343
1344 iem_emailproc_hdl_pvt.create_item_account_emailprocs(
1345 p_api_version_number =>p_api_version_number,
1346 p_init_msg_list => p_init_msg_list,
1347 p_commit => p_commit,
1348 p_emailproc_id =>p_emailproc_id,
1349 p_email_account_id =>p_email_account_id,
1350 p_enabled_flag => p_enabled_flag,
1351 p_priority => p_priority,
1352 x_return_status =>l_return_status,
1353 x_msg_count => l_msg_count,
1354 x_msg_data => l_msg_data);
1355
1356 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
1357 raise IEM_ACCOUNT_ROUTE_NOT_UPDATED;
1358
1359 end if;
1360
1361 -- Standard Check Of p_commit.
1362 IF FND_API.To_Boolean(p_commit) THEN
1363 COMMIT WORK;
1364 END IF;
1365 -- Standard callto get message count and if count is 1, get message info.
1366 FND_MSG_PUB.Count_And_Get
1367 ( p_count => x_msg_count,
1368 p_data => x_msg_data
1369 );
1370
1371
1372 EXCEPTION
1373 WHEN IEM_NOT_REDIRECT_SAME_ACCT THEN
1374 ROLLBACK TO create_wrap_account_routes_PVT;
1375 FND_MESSAGE.SET_NAME('IEM','IEM_NOT_REDIRECT_SAME_ACCT');
1376
1377 FND_MSG_PUB.Add;
1378 x_return_status := FND_API.G_RET_STS_ERROR ;
1379 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1380
1381 WHEN IEM_ADMIN_ROUTE_NOT_EXIST THEN
1382 ROLLBACK TO create_wrap_account_routes_PVT;
1383 FND_MESSAGE.SET_NAME('IEM','IEM_ADMIN_ROUTE_NOT_EXIST');
1384
1385 FND_MSG_PUB.Add;
1386 x_return_status := FND_API.G_RET_STS_ERROR ;
1387 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1388
1389
1390 WHEN IEM_ADMIN_ACCOUNT_NOT_EXIST THEN
1391 ROLLBACK TO create_wrap_account_routes_PVT;
1392 FND_MESSAGE.SET_NAME('IEM','IEM_ADMIN_ACCOUNT_NOT_EXIST');
1393 FND_MSG_PUB.Add;
1394
1395 x_return_status := FND_API.G_RET_STS_ERROR ;
1396 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1397
1398 WHEN IEM_ACCOUNT_ROUTE_NOT_UPDATED THEN
1399
1400 ROLLBACK TO create_wrap_account_routes_PVT;
1401 FND_MESSAGE.SET_NAME('IEM','IEM_ACCOUNT_ROUTE_NOT_UPDATED');
1402 FND_MSG_PUB.Add;
1403 x_return_status := FND_API.G_RET_STS_ERROR ;
1404 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1405
1406 WHEN FND_API.G_EXC_ERROR THEN
1407 ROLLBACK TO create_wrap_account_routes_PVT;
1408 x_return_status := FND_API.G_RET_STS_ERROR ;
1409
1410 FND_MSG_PUB.Count_And_Get
1411 ( p_count => x_msg_count,
1412 p_data => x_msg_data
1413
1414 );
1415
1416 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1417 ROLLBACK TO create_wrap_account_routes_PVT;
1418 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1419 FND_MSG_PUB.Count_And_Get
1420 ( p_count => x_msg_count,
1421 p_data => x_msg_data
1422 );
1423
1424
1425 WHEN OTHERS THEN
1426 ROLLBACK TO create_wrap_account_routes_PVT;
1427
1428 x_return_status := FND_API.G_RET_STS_ERROR;
1429 IF FND_MSG_PUB.Check_Msg_Level
1430 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1431 THEN
1432 FND_MSG_PUB.Add_Exc_Msg
1433 ( G_PKG_NAME,
1434 l_api_name
1435 );
1436 END IF;
1437 FND_MSG_PUB.Count_And_Get
1438 ( p_count => x_msg_count,
1439
1440 p_data => x_msg_data
1441
1442 );
1443
1444 END create_wrap_account_emailprocs;
1445
1446 -- to update and delete new ruples in iem_account_routes
1447 PROCEDURE update_wrap_account_emailprocs (
1448 p_api_version_number IN NUMBER,
1449 p_init_msg_list IN VARCHAR2 := null,
1450 p_commit IN VARCHAR2 := null,
1451 p_email_account_id IN NUMBER,
1452 p_emailproc_ids_tbl IN jtf_varchar2_Table_100,
1453 p_upd_enable_flag_tbl IN jtf_varchar2_Table_100,
1454 p_delete_emailproc_ids_tbl IN jtf_varchar2_Table_100,
1455 p_rule_type IN varchar2,
1456 x_return_status OUT NOCOPY VARCHAR2,
1457 x_msg_count OUT NOCOPY NUMBER,
1458 x_msg_data OUT NOCOPY VARCHAR2
1459 ) is
1460 l_api_name VARCHAR2(255):='update_wrap_account_emailprocs';
1461 l_api_version_number NUMBER:=1.0;
1462 l_return_status VARCHAR2(20) := FND_API.G_RET_STS_SUCCESS;
1463 l_msg_count NUMBER := 0;
1464 l_msg_data VARCHAR2(2000);
1465 IEM_ACCT_EMAILPROC_NOT_DELETED EXCEPTION;
1466 IEM_ACCT_EMAILPROC_NOT_UPDATED EXCEPTION;
1467 BEGIN
1468 -- Standard Start of API savepoint
1469 SAVEPOINT update_wrap_acct_emailproc_PVT;
1470
1471 -- Standard call to check for call compatibility.
1472 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
1473 p_api_version_number,
1474 l_api_name,
1475 G_PKG_NAME)
1476
1477 THEN
1478 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1479 END IF;
1480
1481 -- Initialize message list if p_init_msg_list is set to TRUE.
1482 IF FND_API.to_Boolean( p_init_msg_list )
1483 THEN
1484
1485 FND_MSG_PUB.initialize;
1486 END IF;
1487 -- Initialize API return status to SUCCESS
1488 x_return_status := FND_API.G_RET_STS_SUCCESS;
1489
1490 -- update first
1491
1492 if ( p_emailproc_ids_tbl.count <> 0 ) then
1493 FOR i IN p_emailproc_ids_tbl.FIRST..p_emailproc_ids_tbl.LAST LOOP
1494 iem_emailproc_hdl_pvt.update_account_emailprocs
1495 (p_api_version_number =>p_api_version_number,
1496 p_init_msg_list => FND_API.G_FALSE,
1497 p_commit => FND_API.G_TRUE,
1498 p_emailproc_id => p_emailproc_ids_tbl(i),
1499 p_email_account_id => p_email_account_id,
1500 p_enabled_flag => p_upd_enable_flag_tbl(i),
1501 x_return_status =>l_return_status,
1502 x_msg_count => l_msg_count,
1503 x_msg_data => l_msg_data);
1504
1505 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
1506 raise IEM_ACCT_EMAILPROC_NOT_UPDATED;
1507 end if;
1508 END LOOP;
1509 end if;
1510
1511
1512 if ( p_delete_emailproc_ids_tbl.count <> 0 ) then
1513 iem_emailproc_hdl_pvt.delete_acct_emailproc_batch
1514 (p_api_version_number => p_api_version_number,
1515 P_init_msg_list => FND_API.G_FALSE,
1516 p_commit => FND_API.G_TRUE,
1517 p_emailproc_ids_tbl => p_delete_emailproc_ids_tbl,
1518 p_account_id => p_email_account_id,
1519 p_rule_type => p_rule_type,
1520 x_return_status => l_return_status,
1521 x_msg_count => l_msg_count,
1522 x_msg_data => l_msg_data) ;
1523 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
1524 raise IEM_ACCT_EMAILPROC_NOT_DELETED;
1525 end if;
1526
1527 end if;
1528
1529
1530 -- Standard Check Of p_commit.
1531 IF FND_API.To_Boolean(p_commit) THEN
1532 COMMIT WORK;
1533 END IF;
1534
1535 -- Standard callto get message count and if count is 1, get message info.
1536 FND_MSG_PUB.Count_And_Get
1537
1538 ( p_count => x_msg_count,
1539 p_data => x_msg_data
1540 );
1541
1542 EXCEPTION
1543
1544 WHEN IEM_ACCT_EMAILPROC_NOT_UPDATED THEN
1545 ROLLBACK TO update_wrap_acct_emailproc_PVT;
1546 FND_MESSAGE.SET_NAME('IEM','IEM_ACCT_EMAILPROC_NOT_UPDATED');
1547 FND_MSG_PUB.Add;
1548 x_return_status := FND_API.G_RET_STS_ERROR ;
1549
1550 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1551
1552 WHEN IEM_ACCT_EMAILPROC_NOT_DELETED THEN
1553 ROLLBACK TO update_wrap_acct_emailproc_PVT;
1554 FND_MESSAGE.SET_NAME('IEM','IEM_ACCT_EMAILPROC_NOT_DELETED');
1555 FND_MSG_PUB.Add;
1556 x_return_status := FND_API.G_RET_STS_ERROR ;
1557 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1558
1559 WHEN FND_API.G_EXC_ERROR THEN
1560 ROLLBACK TO update_wrap_acct_emailproc_PVT;
1561 x_return_status := FND_API.G_RET_STS_ERROR ;
1562 FND_MSG_PUB.Count_And_Get
1563 ( p_count => x_msg_count,
1564 p_data => x_msg_data
1565 );
1566
1567 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1568 ROLLBACK TO update_wrap_acct_emailproc_PVT;
1569 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1570 FND_MSG_PUB.Count_And_Get
1571 ( p_count => x_msg_count,
1572 p_data => x_msg_data
1573 );
1574
1575 WHEN OTHERS THEN
1576 ROLLBACK TO update_wrap_acct_emailproc_PVT;
1577 x_return_status := FND_API.G_RET_STS_ERROR;
1578
1579 IF FND_MSG_PUB.Check_Msg_Level
1580 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1581 THEN
1582 FND_MSG_PUB.Add_Exc_Msg
1583 ( G_PKG_NAME,
1584 l_api_name
1585 );
1586 END IF;
1587 FND_MSG_PUB.Count_And_Get
1588 ( p_count => x_msg_count,
1589 p_data => x_msg_data
1590 );
1591 END update_wrap_account_emailprocs;
1592
1593
1594 -- Enter further code below as specified in the Package spec.
1595 PROCEDURE delete_item_emailproc
1596 (p_api_version_number IN NUMBER,
1597 P_init_msg_list IN VARCHAR2 := null,
1598 p_commit IN VARCHAR2 := null,
1599 p_emailproc_id IN NUMBER,
1600 p_rule_type IN VARCHAR2,
1601 x_return_status OUT NOCOPY VARCHAR2,
1602 x_msg_count OUT NOCOPY NUMBER,
1603 x_msg_data OUT NOCOPY VARCHAR2)
1604 IS
1605 i INTEGER;
1606 l_api_name varchar2(30):='delete_item_batch';
1607 l_api_version_number number:=1.0;
1608
1609 CURSOR acct_id_cursor( l_emailproc_id IN NUMBER ) IS
1610 select email_account_id from iem_account_emailprocs where emailproc_id = l_emailproc_id;
1611
1612 CURSOR action_id_cursor( l_emailproc_id IN NUMBER ) IS
1613 select action_id from iem_actions where emailproc_id = l_emailproc_id;
1614 IEM_ROUTE_NOT_DELETED EXCEPTION;
1615 BEGIN
1616
1617
1618
1619 --Standard Savepoint
1620 SAVEPOINT delete_item_batch;
1621
1622 -- Standard call to check for call compatibility.
1623 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
1624 p_api_version_number,
1625 l_api_name,
1626 G_PKG_NAME)
1627 THEN
1628 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1629 END IF;
1630
1631 --Initialize the message list if p_init_msg_list is set to TRUE
1632 If FND_API.to_Boolean(p_init_msg_list) THEN
1633 FND_MSG_PUB.initialize;
1634 END IF;
1635
1636 --Initialize API status return
1637 x_return_status := FND_API.G_RET_STS_SUCCESS;
1638
1639 --Actual API starts here
1640 DELETE
1641 FROM IEM_EMAILPROCS
1642 WHERE emailproc_id = p_emailproc_id;
1643
1644 if SQL%NOTFOUND then
1645 raise IEM_ROUTE_NOT_DELETED;
1646 end if;
1647
1648 --Delete the accounts, rules associated with this route
1649 --if ( p_route_ids_tbl.count <> 0 ) then
1650
1651 -- FOR i IN p_route_ids_tbl.FIRST..p_route_ids_tbl.LAST LOOP
1652
1653 -- update priority after delete an account_route
1654 --Fixme for account rule type association
1655 FOR acct_id IN acct_id_cursor(p_emailproc_id) LOOP
1656 Update iem_account_emailprocs set priority=priority-1
1657
1658 where email_account_id=acct_id.email_account_id
1659 and emailproc_id in
1660 ( select emailproc_id
1661 from iem_emailprocs
1662 where rule_type=p_rule_type )
1663 and priority > (Select priority from iem_account_emailprocs
1664 where emailproc_id=p_emailproc_id and email_account_id = acct_id.email_account_id);
1665 END LOOP;
1666
1667 --remove association
1668 DELETE
1669 FROM iem_account_emailprocs
1670 WHERE emailproc_id = p_emailproc_id;
1671
1672 --remove iem_emailproc_rules
1673 DELETE
1674 FROM IEM_EMAILPROC_RULES
1675 WHERE emailproc_id=p_emailproc_id;
1676
1677 --remove iem_action_dtls
1678 FOR v_action_id IN action_id_cursor(p_emailproc_id) LOOP
1679 delete from iem_action_dtls where action_id = v_action_id.action_id;
1680 END LOOP;
1681
1682 --remove iem_email_actions
1683 delete from iem_actions where emailproc_id = p_emailproc_id;
1684
1685
1686 --Standard check of p_commit
1687 IF FND_API.to_Boolean(p_commit) THEN
1688 COMMIT WORK;
1689 END IF;
1690
1691
1692 EXCEPTION
1693
1694 WHEN IEM_ROUTE_NOT_DELETED THEN
1695 ROLLBACK TO delete_item_batch;
1696 x_return_status := FND_API.G_RET_STS_ERROR;
1697 FND_MESSAGE.SET_NAME('IEM', 'IEM_ROUTE_NOT_DELETED');
1698
1699 FND_MSG_PUB.ADD;
1700 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1701
1702 WHEN FND_API.G_EXC_ERROR THEN
1703 ROLLBACK TO delete_item_batch;
1704 x_return_status := FND_API.G_RET_STS_ERROR ;
1705 FND_MSG_PUB.Count_And_Get
1706 ( p_count => x_msg_count,p_data => x_msg_data);
1707
1708
1709 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1710 ROLLBACK TO delete_item_batch;
1711 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1712 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,p_data => x_msg_data);
1713
1714
1715 WHEN OTHERS THEN
1716 ROLLBACK TO delete_item_batch;
1717 x_return_status := FND_API.G_RET_STS_ERROR;
1718 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1719 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME , l_api_name);
1720 END IF;
1721
1722 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count ,p_data => x_msg_data);
1723
1724 END delete_item_emailproc;
1725
1726
1727 PROCEDURE delete_acct_emailproc_by_acct
1728 (p_api_version_number IN NUMBER,
1729 P_init_msg_list IN VARCHAR2 := null,
1730 p_commit IN VARCHAR2 := null,
1731 p_email_account_id IN NUMBER,
1732 x_return_status OUT NOCOPY VARCHAR2,
1733 x_msg_count OUT NOCOPY NUMBER,
1734 x_msg_data OUT NOCOPY VARCHAR2)
1735 IS
1736 i INTEGER;
1737 l_api_name varchar2(30):='delete_acct_emailproc_by_acct';
1738 l_api_version_number number:=1.0;
1739
1740 BEGIN
1741
1742 --Standard Savepoint
1743 SAVEPOINT delete_acct_emailproc_by_acct;
1744
1745 -- Standard call to check for call compatibility.
1746 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
1747 p_api_version_number,
1748 l_api_name,
1749 G_PKG_NAME)
1750 THEN
1751 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1752 END IF;
1753
1754
1755
1756 --Initialize the message list if p_init_msg_list is set to TRUE
1757 If FND_API.to_Boolean(p_init_msg_list) THEN
1758 FND_MSG_PUB.initialize;
1759 END IF;
1760
1761 --Initialize API status return
1762 x_return_status := FND_API.G_RET_STS_SUCCESS;
1763
1764 --Actual API starts here
1765 DELETE
1766 FROM IEM_ACCOUNT_EMAILPROCS
1767 WHERE email_account_id = p_email_account_id;
1768
1769
1770 --Standard check of p_commit
1771 IF FND_API.to_Boolean(p_commit) THEN
1772 COMMIT WORK;
1773 END IF;
1774
1775 FND_MSG_PUB.Count_And_Get
1776 ( p_count => x_msg_count,p_data => x_msg_data);
1777
1778 EXCEPTION
1779 WHEN FND_API.G_EXC_ERROR THEN
1780 ROLLBACK TO delete_acct_emailproc_by_acct;
1781 x_return_status := FND_API.G_RET_STS_ERROR ;
1782 FND_MSG_PUB.Count_And_Get
1783
1784 ( p_count => x_msg_count,p_data => x_msg_data);
1785
1786 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1787 ROLLBACK TO delete_acct_emailproc_by_acct;
1788 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1789 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,p_data => x_msg_data);
1790
1791
1792 WHEN OTHERS THEN
1793 ROLLBACK TO delete_acct_emailproc_by_acct;
1794 x_return_status := FND_API.G_RET_STS_ERROR;
1795 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1796 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME , l_api_name);
1797
1798 END IF;
1799 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count ,p_data => x_msg_data);
1800
1801 END delete_acct_emailproc_by_acct;
1802
1803 END IEM_EMAILPROC_PVT; -- Package Body IEM_EMAILPROC_PVT