1 PACKAGE BODY IEM_RULES_ENGINE_PUB AS
2 /* $Header: iemprulb.pls 120.8 2011/11/21 10:43:51 shramana ship $ */
3 --
4 --
5 -- Purpose: Email Processing Engine to process emails based on the rules
6 --
7 -- MODIFICATION HISTORY
8 -- Person Date Comments
9 -- Liang Xia 06/10/2002 Create
10 -- Liang Xia 12/04/2002 Fixed GSCC warning: NOCOPY,No G_MISS..
11 -- Liang Xia 07/06/2003 Added Document Mapping validation
12 -- Liang Xia 08/17/2003 Added Auto-Redirect rule type
13 -- Liang Xia 09/24/2003 Added extra validation on AUTOACKNOWLEDGE,
14 -- AUTOREPLYSPECDOC to check if the document is exist
15 -- Liang Xia 08/16/2004 Appending Rule_id for Document Retrieval to retrieval result later.( 11.5.10)
16 -- Liang Xia 04/06/2005 Fixed GSCC sql.46 ( bug 4256769 )
17 -- Liang Xia 06/24/2005 Fixed GSCC sql.46 ( bug 4452895 )
18 -- PKESANI 02/22/2006 ACSR project - code fix
19 -- lkullamb 07/13/2009 added parameter3 column to iem_action_dtls table, hence the procedure changed
20 -- auto_process_email
21 -- --------- ------ ------------------------------------------
22
23 /*GLOBAL VARIABLES FOR PRIVATE USE
24 ==================================*/
25 G_PKG_NAME VARCHAR2(100):= 'IEM_RULES_ENGINE_PUB';
26
27 --The record type for passing rules
28 TYPE Rules_rec_type is RECORD (
29 key iem_route_rules.key_type_code%type,
30 operator iem_route_rules.operator_type_code%type,
31 value iem_route_rules.value%type);
32
33 --The table type for passing rules
34 TYPE Rules_tbl_type IS table of Rules_rec_type INDEX BY BINARY_INTEGER;
35
36
37
38 /* PRIVATE PROCEDURES NOT AVAILABLE TO THE PUBLIC
39 ================================================*/
40
41 /* Evaluate And conditions */
42 PROCEDURE evaluateAnd(keyVals IN iem_route_pub.keyVals_tbl_type, rules IN Rules_tbl_type, fireClass OUT NOCOPY Boolean)
43 IS
44
45 x number := 1;
46 y number := 1;
47
48 keyToFind iem_route_class_rules.key_type_code%type;
49 operator iem_route_class_rules.operator_type_code%type;
50 valueToFind iem_route_class_rules.value%type;
51
52 foundKey Boolean;
53 foundKeyValue Boolean;
54
55 numberOfKeys Number;
56 numberOfRules Number;
57
58 errorMessage varchar2(2000);
59
60 Begin
61
62 numberOfKeys := keyVals.count;
63 numberofRules := rules.count;
64
65 --Evaluate each rule one at a time
66 while x <= numberOfRules loop
67
68 --Copy the rule into temp variables to save repetitive calls to the UDT
69 keyToFind := rules(x).key;
70 valueToFind := rules(x).value;
71 operator := rules(x).operator;
72
73 y := 1;
74 foundKey := false;
75 foundKeyValue := false;
76
77 --Search through all the keys that got passed in
78 while y <= numberOfKeys loop
79
80 --Make the key comparison
81 if keyToFind = keyVals(y).key then
82 foundKey := true;
83 --If the key is found then see if the value matches up based on the operator
84 if iem_operators_pvt.satisfied(keyVals(y).value, operator, valueToFind, keyVals(y).datatype) then
85 foundKeyValue := true;
86 end if;
87 --Exit since we found what we wanted
88 EXIT;
89 end if;
90
91 y := y + 1;
92 end loop;
93
94 --If we were unable to find the key or the value then exit since this is AND chaining
95 if (foundKey = false or foundKeyValue = false) then
96 fireClass := false;
97 EXIT;
98 else
99 fireClass := true;
100 end if;
101
102 x := x + 1;
103 end loop;
104
105 EXCEPTION
106 When others then
107
108 if( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
109 errorMessage := '[' || sqlcode || sqlerrm || ']' || ' Others';
110 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'IEM.PLSQL.IEM_RULES_ENGINE_PUB.EVALUATEAND.OTHERS', errorMessage);
111 end if;
112
113 End evaluateAnd;
114
115
116 /* Evaluate OR conditions */
117 PROCEDURE evaluateOr(keyVals IN iem_route_pub.keyVals_tbl_type, rules IN Rules_tbl_type, fireClass OUT NOCOPY Boolean)
118 IS
119
120 x number := 1;
121 y number := 1;
122
123 keyToFind iem_route_class_rules.key_type_code%type;
124 operator iem_route_class_rules.operator_type_code%type;
125 valueToFind iem_route_class_rules.value%type;
126
127 foundKeyValue Boolean;
128
129 numberOfKeys Number;
130 numberOfRules Number;
131
132 errorMessage varchar2(2000);
133
134 Begin
135
136 numberOfKeys := keyVals.count;
137 numberofRules := rules.count;
138
139 --Evaluate each rule one at a time
140 while x <= numberOfRules loop
141
142 --Copy the rule into temp variables to save repetitive calls to the UDT
143 keyToFind := rules(x).key;
144 valueToFind := rules(x).value;
145 operator := rules(x).operator;
146
147 y := 1;
148 foundKeyValue := false;
149
150 --Search through all the keys that got passed in
151 while y <= numberOfKeys loop
152
153 --Make the key comparison case insensitive
154 if upper(keyToFind) = upper(keyVals(y).key) then
155 --If the key is found then see if the value matches up based on the operator
156 if iem_operators_pvt.satisfied(keyVals(y).value, operator, valueToFind, keyVals(y).datatype) then
157 foundKeyValue := true;
158 end if;
159 --Exit since we found what we wanted
160 EXIT;
161 end if;
162
163 y := y + 1;
164 end loop;
165
166 --If we found a key value pair then exit since this is OR chaining
167 if foundKeyValue then
168 fireClass := true;
169 EXIT;
170 else
171 fireClass := false;
172 end if;
173
174 x := x + 1;
175 end loop;
176
177 EXCEPTION
178 When others then
179
180 if( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
181 errorMessage := '[' || sqlcode || sqlerrm || ']' || ' Others';
182 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'IEM.PLSQL.IEM_RULES_ENGINE_PUB.EVALUATEOR.OTHERS', errorMessage);
183 end if;
184
185 End evaluateOr;
186
187
188 PROCEDURE auto_process_email(
189 p_api_version_number IN Number,
190 p_init_msg_list IN VARCHAR2 := null,
191 p_commit IN VARCHAR2 := null,
192 p_rule_type IN VARCHAR2,
193 p_keyVals_tbl IN IEM_ROUTE_PUB.keyVals_tbl_type,
194 p_accountId IN Number,
195 x_result OUT NOCOPY VARCHAR2,
196 x_action OUT NOCOPY Varchar2,
197 x_parameters OUT NOCOPY IEM_RULES_ENGINE_PUB.parameter_tbl_type,
198 x_return_status OUT NOCOPY VARCHAR2,
199 x_msg_count OUT NOCOPY NUMBER,
200 x_msg_data OUT NOCOPY VARCHAR2)
201
202 IS
203
204 l_api_version_number CONSTANT NUMBER:= 1.0;
205 l_api_name CONSTANT VARCHAR2(30):= 'auto_process_email';
206
207 x number:= 1;
208 y number:= 1;
209 ruleHold Rules_tbl_type;
210 satisfied Boolean := false;
211 l_action VARCHAR2(30);
212 enabledFlag varchar(1):= 'Y';
213 booleanTypeCode iem_emailprocs.boolean_type_code%type;
214
215 l_module VARCHAR2(30);
216 l_emailproc_id NUMBER;
217 L_SERVICE_REQUEST_ID VARCHAR2(30);
218 l_doc_total NUMBER;
219 l_doc_exist_auto_reply boolean := true;
220
221 logMessage varchar2(2000);
222 errorMessage varchar2(2000);
223
224 l_log_enabled BOOLEAN := false;
225 l_exception_log BOOLEAN := false;
226 l_encrypted_id NUMBER;
227 l_subject varchar2(2000); -- change for bug 12321865
228
229 IEM_UNKNOWN_RULE_TYPE_EXP EXCEPTION;
230
231 --The active emailprocs for auto_acknowledge in the system
232 cursor c_auto_processings (p_enabled_flag iem_account_emailprocs.enabled_flag%type,
233 p_account_id iem_email_accounts.email_account_id%type,
234 p_rule_type iem_emailprocs.rule_type%type)
235 is
236 select
237 a.emailproc_id,
238 a.boolean_type_code,
239 a.all_email
240 from
241 iem_emailprocs a,
242 iem_account_emailprocs b
243 where
244 a.emailproc_id = b.emailproc_id
245 and
246 a.rule_type = p_rule_type
247 and
248 b.enabled_flag = p_enabled_flag
249 and
250 b.email_account_id = p_account_id
251 -- and
252 -- a.emailproc_id <> 0
253 order by b.priority;
254
255 --All the rules for a classification
256 cursor c_rules (p_emailproc_id iem_emailprocs.emailproc_id%type)
257 is
258 select
259 key_type_code,
260 operator_type_code,
261 value
262 from
263 iem_emailproc_rules
264 where
265 emailproc_id = p_emailproc_id;
266
267 --Get parameter(s)
268 cursor c_params(p_emailproc_id iem_emailprocs.emailproc_id%type)
269 is
270 select b.parameter1, b.parameter2,b.parameter3, b.parameter_tag
271 from iem_actions a, iem_action_dtls b
272 where a.action_id = b.action_id and a.emailproc_id = p_emailproc_id;
273
274 --Verify that there are document under category for Document Mapping
275 BEGIN
276
277 --Standard begin of API savepoint
278 SAVEPOINT Auto_Process_Email_PUB;
279
280 -- Standard call to check for call compatibility.
281 IF NOT FND_API.Compatible_API_Call(
282 l_api_version_number,
283 p_api_version_number,
284 l_api_name,
285 G_PKG_NAME)
286 THEN
287 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
288 END IF;
289
290 -- Initialize message list if p_init_msg_list is set to TRUE.
291 IF FND_API.to_Boolean( p_init_msg_list ) THEN
292 FND_MSG_PUB.initialize;
293 END IF;
294
295 -- Initialize API return status to SUCCESS
296 x_return_status := FND_API.G_RET_STS_SUCCESS;
297
298 --=================--
299 -- Begining of API --
300 --=================--
301 FND_LOG_REPOSITORY.init(null,null);
302
303 l_log_enabled := FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL;
304
305
306 l_exception_log := FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL;
307
308
309 IF (p_rule_type = 'AUTODELETE' ) THEN
310 l_module := 'AUTODELETE';
311 ELSIF (p_rule_type = 'AUTOACKNOWLEDGE' ) THEN
312 l_module := 'AUTOACKNOWLEDGE';
313 ELSIF (p_rule_type = 'AUTOPROCESSING' ) THEN
314 l_module := 'AUTOPROCESSING';
315 ELSIF (p_rule_type = 'AUTOREDIRECT' ) THEN
316 l_module := 'AUTOREDIRECT';
317 ELSIF (p_rule_type = 'AUTORRRS' ) THEN
318 l_module := 'AUTORRRS';
319 ELSIF (p_rule_type = 'DOCUMENTRETRIEVAL' ) THEN
320 l_module := 'DOCUMENTRETRIEVAL';
321 ELSE
322 if l_log_enabled then
323 logMessage := '[Error unknown RuleType: p_rule_type= ' || p_rule_type || ' p_account_id=' || to_char(p_accountid) || ']';
324 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'IEM.PLSQL.IEM_RULES_ENGINE_PUB.AUTO_PROCESS_EMAIL.START', logMessage);
325 end if;
326
327 raise IEM_UNKNOWN_RULE_TYPE_EXP;
328 END IF;
329
330 if l_log_enabled then
331 logMessage := '[p_rule_type= ' || p_rule_type || ' p_account_id=' || to_char(p_accountid) || ']';
332 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'IEM.PLSQL.IEM_RULES_ENGINE_PUB.AUTO_PROCESS_EMAIL.'||l_module||'.START', logMessage);
333 end if;
334
335
336 --Initialize x_result to fals
337 x_result := FND_API.G_FALSE;
338
339 --Iniitalize the Rule data structure
340 --ruleHold.delete;
341
342
343 --Check to see if the passed in PL/SQL table has valid key-vals
344 If p_keyVals_tbl.count > 0 then
345
346 if l_log_enabled then
347 logMessage := '[p_keyValsCount=' || to_char(p_keyVals_tbl.count) || ']';
348 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'IEM.PLSQL.IEM_RULES_ENGINE_PUB.AUTO_PROCESS_EMAIL.'||l_module||'.KEY_VALS_MORE_THAN_0', logMessage);
349 end if;
350
351
352 --Get all the active routes in the system
353 For v_emailprocs in c_auto_processings (enabledFlag, p_accountId, p_rule_type) Loop
354
355 --Reset local variables for each rule
356 l_doc_exist_auto_reply := true;
357
358 --Check 'All Emails' is set or not. If set, return result without evaluate rules
359 IF v_emailprocs.all_email = 'Y' THEN
360 --Get action name. Currently one rule type corresponding to one action
361 select action into l_action from iem_actions where emailproc_id = v_emailprocs.emailproc_id;
362
363 --- PK
364 if l_action = 'AUTOCREATESR' then
365
366 --Check if Tag exists
367 l_subject := IEM_ROUTE_PUB.get_key_value
368 ( p_keyVals_tbl=>p_keyVals_tbl,
369 p_key_name=>'IEMSSUBJECT');
370
371 IEM_EMAIL_PROC_PVT.IEM_RETURN_ENCRYPTID
372 (p_subject=>l_subject,
373 x_id=>l_encrypted_id,
374 x_Status=>x_return_status);
375
376 if l_encrypted_id is NULL then
377 x_action := l_action;
378 --Get parameters
379 y := 1;
380 l_emailproc_id := v_emailprocs.emailproc_id;
381 For v_params in c_params(l_emailproc_id) Loop
382 x_parameters(y).parameter1 := v_params.parameter1;
383 x_parameters(y).parameter2 := v_params.parameter2;
384 x_parameters(y).parameter3 := v_params.parameter3;
385 x_parameters(y).type := v_params.parameter_tag;
386
387 if l_log_enabled then
388 logMessage := '[All Email is set! Emailproc_id=' || l_emailproc_id || ' action='||l_action||' parameter1='|| v_params.parameter1 ||' parameter2='|| v_params.parameter2 ||'parameter3='|| v_params.parameter3 || ']';
389 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'IEM.PLSQL.IEM_RULES_ENGINE_PUB.AUTO_PROCESS_EMAIL.'||l_module||'.PARAMS', logMessage);
390 end if;
391 y := y + 1;
392 End Loop;
393
394 x_result := FND_API.G_TRUE;
395 exit;
396
397 else --Action is AutoCreateSR, but TAG exists
398 if l_log_enabled then
399 logMessage := '[All Email is set! Emailproc_id=' || l_emailproc_id || ' action='||l_action||'. But TAG exists . So continue eval next rule.';
400 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'IEM.PLSQL.IEM_RULES_ENGINE_PUB.AUTO_PROCESS_EMAIL.'||l_module||'.PARAMS', logMessage);
401 end if;
402 end if;
403 --Check if SR id is null or not
404 elsif l_action = 'UPDSERVICEREQID' then
405 l_service_request_id := IEM_ROUTE_PUB.get_key_value
406 ( p_keyVals_tbl=>p_keyVals_tbl,
407 p_key_name=>'IEMNBZTSRVSRID');
408
409 if is_valid( l_service_request_id )= FND_API.G_TRUE then
410
411 x_action := l_action;
412 --Get parameter for template_id
413 y := 1;
414 l_emailproc_id := v_emailprocs.emailproc_id;
415 For v_params in c_params(l_emailproc_id) Loop
416 x_parameters(y).parameter1 := v_params.parameter1;
417 x_parameters(y).parameter2 := v_params.parameter2;
418 x_parameters(y).parameter3 := v_params.parameter3;
419 x_parameters(y).type := v_params.parameter_tag;
420
421 if l_log_enabled then
422 logMessage := '[All Email is set! Emailproc_id=' || l_emailproc_id || ' action='||l_action||' parameter1='|| v_params.parameter1 ||' parameter2='|| v_params.parameter2 || ']';
423 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'IEM.PLSQL.IEM_RULES_ENGINE_PUB.AUTO_PROCESS_EMAIL.'||l_module||'.PARAMS', logMessage);
424 end if;
425 y := y + 1;
426 End Loop;
427
428 x_result := FND_API.G_TRUE;
429 exit;
430 --Action is UpdateSR, but no valid SR_id
431 else
432 if l_log_enabled then
433 logMessage := '[All Email is set! Emailproc_id=' || l_emailproc_id || ' action='||l_action||'. But ServiceRequest ID is null. So continue eval next rule.';
434 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'IEM.PLSQL.IEM_RULES_ENGINE_PUB.AUTO_PROCESS_EMAIL.'||l_module||'.PARAMS', logMessage);
435 end if;
436 end if;
437
438 else -- Action <> 'UPDSERVICEREQID'
439 x_action := l_action;
440 y := 1;
441 l_emailproc_id := v_emailprocs.emailproc_id;
442
443 x_parameters.delete;
444 For v_params in c_params(l_emailproc_id) Loop
445 x_parameters(y).parameter1 := v_params.parameter1;
446 x_parameters(y).parameter2 := v_params.parameter2;
447 x_parameters(y).parameter3 := v_params.parameter3;
448 x_parameters(y).type := v_params.parameter_tag;
449
450 if l_log_enabled then
451 logMessage := '[All Email is set! Emailproc_id=' || l_emailproc_id || ' action='||l_action||' parameter1='|| v_params.parameter1 ||' parameter2='|| v_params.parameter2 || ']';
452 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'IEM.PLSQL.IEM_RULES_ENGINE_PUB.AUTO_PROCESS_EMAIL.'||l_module||'.PARAMS', logMessage);
453 end if;
454 y := y + 1;
455 End Loop;
456
457 -- For bug 3809733, to return Emailproc_id ( to be stored in iem_post_mdts.category_map_id. exmple:if return 123, store as -123)
458 -- so that corresponding Category_IDs could be used later
459 if l_module = 'DOCUMENTRETRIEVAL' then
460 x_parameters(y).parameter1 := 'RULE_ID';
461 x_parameters(y).parameter2 := l_emailproc_id;
462 end if;
463
464 --Verify if there is document under category for DOCUMENT MAPPING
465 --If not, continue next rule evaluation.
466 if l_action = 'MES_CATEGORY_MAPPING' then
467 l_doc_total := get_document_total(x_parameters(1).parameter1);
468 if (l_doc_total > 0 ) then
469 --exit with correct result
470 x_result := FND_API.G_TRUE;
471 exit;
472 end if;
473 elsif l_action = 'AUTOACKNOWLEDGE' then
474 if is_document_exist( x_parameters(1).parameter1,x_parameters(1).parameter2 )= FND_API.G_TRUE then
475 x_result := FND_API.G_TRUE;
476 exit;
477 end if;
478 elsif l_action='AUTOREPLYSPECIFIEDDOC' then
479 FOR m IN x_parameters.FIRST..x_parameters.LAST loop
480 if is_document_exist( x_parameters(m).parameter1,x_parameters(m).parameter2 )= FND_API.G_FALSE then
481 l_doc_exist_auto_reply := false;
482 exit;
483 end if;
484 end loop;
485
486 if l_doc_exist_auto_reply then
487 x_result := FND_API.G_TRUE;
488 exit;
489 end if;
490 else --NO validation on other action, so result is true, return parameters
491 x_result := FND_API.G_TRUE;
492 exit;
493 end if; --end if l_action = 'MES_CATEGORY_MAPPING'
494
495 end if; --end if l_action = 'UPDSERVICEREQID'
496 ELSE
497 -- END IF; --v_emailprocs.all_email = 'Y'
498
499 --The boolean operator for the rule chaining
500 booleanTypeCode := v_emailprocs.boolean_type_code;
501
502 --Iniitalize the Rule data structure
503 ruleHold.delete;
504 x := 1;
505
506 --Get all the rules for the route and load it into the data structure
507 For v_rules in c_rules(v_emailprocs.emailproc_id) Loop
508
509 ruleHold(x).key := v_rules.key_type_code;
510 ruleHold(x).operator := v_rules.operator_type_code;
511 ruleHold(x).value := v_rules.value;
512
513 if l_log_enabled then
514 logMessage := '[' || ruleHold(x).key || ruleHold(x).operator || ruleHold(x).value || ']';
515 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'IEM.PLSQL.IEM_RULES_ENGINE_PUB.AUTO_PROCESS_EMAIL.'||l_module||'.RULES', logMessage);
516 end if;
517
518
519 x := x + 1;
520 End Loop;
521
522
523 --Depending on the operator call different evaluation functions
524 if booleanTypeCode = 'AND' then
525 evaluateAnd(p_keyVals_tbl, ruleHold, satisfied);
526 elsif booleanTypeCode = 'OR' then
527 evaluateOr(p_keyVals_tbl, ruleHold, satisfied);
528 end if;
529
530
531 --If the rules got satisfied then return result
532 if satisfied then
533
534 --Get action name. Currently one rule type corresponding to one action
535 select action into l_action from iem_actions where emailproc_id = v_emailprocs.emailproc_id;
536
537 --- PK
538 if l_action = 'AUTOCREATESR' then
539
540 --Check if Tag exists
541 l_subject := IEM_ROUTE_PUB.get_key_value
542 ( p_keyVals_tbl=>p_keyVals_tbl,
543 p_key_name=>'IEMSSUBJECT');
544
545 IEM_EMAIL_PROC_PVT.IEM_RETURN_ENCRYPTID
546 (p_subject=>l_subject,
547 x_id=>l_encrypted_id,
548 x_Status=>x_return_status);
549
550 if l_encrypted_id is NULL then
551 x_action := l_action;
552 --Get parameters
553 y := 1;
554 l_emailproc_id := v_emailprocs.emailproc_id;
555 For v_params in c_params(l_emailproc_id) Loop
556 x_parameters(y).parameter1 := v_params.parameter1;
557 x_parameters(y).parameter2 := v_params.parameter2;
558 x_parameters(y).parameter3 := v_params.parameter3;
559 x_parameters(y).type := v_params.parameter_tag;
560
561 if l_log_enabled then
562 logMessage := '[All Email is set! Emailproc_id=' || l_emailproc_id || ' action='||l_action||' parameter1='|| v_params.parameter1 ||' parameter2='|| v_params.parameter2 || ']';
563 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'IEM.PLSQL.IEM_RULES_ENGINE_PUB.AUTO_PROCESS_EMAIL.'||l_module||'.PARAMS', logMessage);
564 end if;
565 y := y + 1;
566 End Loop;
567
568 x_result := FND_API.G_TRUE;
569 exit;
570
571 else --Action is AutoCreateSR, but TAG exists
572 if l_log_enabled then
573 logMessage := '[All Email is set! Emailproc_id=' || l_emailproc_id || ' action='||l_action||'. But TAG exists . So continue eval next rule.';
574 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'IEM.PLSQL.IEM_RULES_ENGINE_PUB.AUTO_PROCESS_EMAIL.'||l_module||'.PARAMS', logMessage);
575 end if;
576 end if;
577 -- end if; -- AUTOCREATESR commented by Ranjan
578
579 --Check if SR id is null or not
580 elsif l_action = 'UPDSERVICEREQID' then -- convert if to elsif by ranjan
581 l_service_request_id := IEM_ROUTE_PUB.get_key_value
582 ( p_keyVals_tbl=>p_keyVals_tbl,
583 p_key_name=>'IEMNBZTSRVSRID');
584
585 if is_valid( l_service_request_id )= FND_API.G_TRUE then
586
587 x_action := l_action;
588 --Get parameter for template_id
589 y := 1;
590 l_emailproc_id := v_emailprocs.emailproc_id;
591 For v_params in c_params(l_emailproc_id) Loop
592 x_parameters(y).parameter1 := v_params.parameter1;
593 x_parameters(y).parameter2 := v_params.parameter2;
594 x_parameters(y).parameter3 := v_params.parameter3;
595 x_parameters(y).type := v_params.parameter_tag;
596
597 if l_log_enabled then
598 logMessage := '[Emailproc_id=' || l_emailproc_id || ' action='||l_action||' parameter1='|| v_params.parameter1 ||' parameter2='|| v_params.parameter2 || ']';
599 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'IEM.PLSQL.IEM_RULES_ENGINE_PUB.AUTO_PROCESS_EMAIL.'||l_module||'.PARAMS', logMessage);
600 end if;
601 y := y + 1;
602 End Loop;
603
604 x_result := FND_API.G_TRUE;
605 exit;
606 else
607 if l_log_enabled then
608 logMessage := '[Rule satisfied for Emailproc_id=' || l_emailproc_id || ' action='||l_action||'. But ServiceRequest ID is null. So continue eval next rule.';
609 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'IEM.PLSQL.IEM_RULES_ENGINE_PUB.AUTO_PROCESS_EMAIL.'||l_module||'.PARAMS', logMessage);
610 end if;
611 end if;
612 --Getting parameter, then do validation on particular action
613 else
614
615 x_action := l_action;
616 --Get parameter for template_id
617 y := 1;
618 l_emailproc_id := v_emailprocs.emailproc_id;
619 For v_params in c_params(l_emailproc_id) Loop
620 x_parameters(y).parameter1 := v_params.parameter1;
621 x_parameters(y).parameter2 := v_params.parameter2;
622 x_parameters(y).parameter3 := v_params.parameter3;
623 x_parameters(y).type := v_params.parameter_tag;
624
625 if l_log_enabled then
626 logMessage := '[Emailproc_id=' || l_emailproc_id || ' action='||l_action||' parameter1='|| v_params.parameter1 ||' parameter2='|| v_params.parameter2 || ']';
627 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'IEM.PLSQL.IEM_RULES_ENGINE_PUB.AUTO_PROCESS_EMAIL.'||l_module||'.PARAMS', logMessage);
628 end if;
629 y := y + 1;
630 End Loop;
631
632 -- For bug 3809733, to return Emailproc_id ( to be stored in iem_post_mdts.category_map_id. exmple:if return 123, store as -123)
633 -- so that corresponding Category_IDs could be used later
634 if l_module = 'DOCUMENTRETRIEVAL' then
635 x_parameters(y).parameter1 := 'RULE_ID';
636 x_parameters(y).parameter2 := l_emailproc_id;
637 end if;
638
639 --Verify if there is document under category for DOCUMENT MAPPING
640 --If not, continue next rule evaluation.
641 if l_action = 'MES_CATEGORY_MAPPING' then
642 l_doc_total := get_document_total(x_parameters(1).parameter1);
643 if (l_doc_total > 0 ) then
644 --exit with correct result
645 x_result := FND_API.G_TRUE;
646 exit;
647 end if;
648 elsif l_action = 'AUTOACKNOWLEDGE' then
649 if is_document_exist( x_parameters(1).parameter1,x_parameters(1).parameter2 )= FND_API.G_TRUE then
650 x_result := FND_API.G_TRUE;
651 exit;
652 end if;
653 elsif l_action='AUTOREPLYSPECIFIEDDOC' then
654 FOR m IN x_parameters.FIRST..x_parameters.LAST loop
655 if is_document_exist( x_parameters(m).parameter1,x_parameters(m).parameter2 )= FND_API.G_FALSE then
656 l_doc_exist_auto_reply := false;
657 exit;
658 end if;
659 end loop;
660
661 if ( l_doc_exist_auto_reply ) then
662 x_result := FND_API.G_TRUE;
663 exit;
664 end if;
665 else --NO validation on other action, so result is true, return parameters
666 x_result := FND_API.G_TRUE;
667 exit;
668 end if; --end if l_action = 'MES_CATEGORY_MAPPING'
669
670
671 end if;
672
673 end if; --end of if satisfied
674 END IF; --end of All_email is checked
675 End Loop;
676 Else --in case no key-value passed in, still check if all_email is set to 'Y'. If yes, processing correspondingly
677 --Get all the active emailprocs in the system
678 For v_emailprocs in c_auto_processings (enabledFlag, p_accountId, p_rule_type) Loop
679
680 --Check 'All Emails' is set or not. If set, return result without evaluate rules
681 IF v_emailprocs.all_email = 'Y' THEN
682 --Get action name. Currently one rule type corresponding to one action
683 select action into l_action from iem_actions where emailproc_id = v_emailprocs.emailproc_id;
684
685 --- PK
686 if l_action = 'AUTOCREATESR' then
687
688 --Check if Tag exists
689 l_subject := IEM_ROUTE_PUB.get_key_value
690 ( p_keyVals_tbl=>p_keyVals_tbl,
691 p_key_name=>'IEMSSUBJECT');
692
693 IEM_EMAIL_PROC_PVT.IEM_RETURN_ENCRYPTID
694 (p_subject=>l_subject,
695 x_id=>l_encrypted_id,
696 x_Status=>x_return_status);
697
698 if l_encrypted_id is NULL then
699 x_action := l_action;
700 --Get parameters
701 y := 1;
702 l_emailproc_id := v_emailprocs.emailproc_id;
703 For v_params in c_params(l_emailproc_id) Loop
704 x_parameters(y).parameter1 := v_params.parameter1;
705 x_parameters(y).parameter2 := v_params.parameter2;
706 x_parameters(y).parameter3 := v_params.parameter3;
707 x_parameters(y).type := v_params.parameter_tag;
708
709 if l_log_enabled then
710 logMessage := '[All Email is set! Emailproc_id=' || l_emailproc_id || ' action='||l_action||' parameter1='|| v_params.parameter1 ||' parameter2='|| v_params.parameter2 || ']';
711 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'IEM.PLSQL.IEM_RULES_ENGINE_PUB.AUTO_PROCESS_EMAIL.'||l_module||'.PARAMS', logMessage);
712 end if;
713 y := y + 1;
714 End Loop;
715
716 x_result := FND_API.G_TRUE;
717 exit;
718
719 else --Action is AutoCreateSR, but TAG exists
720 if l_log_enabled then
721 logMessage := '[All Email is set! Emailproc_id=' || l_emailproc_id || ' action='||l_action||'. But TAG exists . So continue eval next rule.';
722 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'IEM.PLSQL.IEM_RULES_ENGINE_PUB.AUTO_PROCESS_EMAIL.'||l_module||'.PARAMS', logMessage);
723 end if;
724 end if;
725
726 --Check if SR id is null or not
727 elsif l_action = 'UPDSERVICEREQID' then
728 l_service_request_id := IEM_ROUTE_PUB.get_key_value
729 ( p_keyVals_tbl=>p_keyVals_tbl,
730 p_key_name=>'IEMNBZTSRVSRID');
731
732 if is_valid( l_service_request_id )= FND_API.G_TRUE then
733
734 x_action := l_action;
735 --Get parameter for template_id
736 y := 1;
737 l_emailproc_id := v_emailprocs.emailproc_id;
738 For v_params in c_params(l_emailproc_id) Loop
739 x_parameters(y).parameter1 := v_params.parameter1;
740 x_parameters(y).parameter2 := v_params.parameter2;
741 x_parameters(y).parameter3 := v_params.parameter3;
742 x_parameters(y).type := v_params.parameter_tag;
743
744 if l_log_enabled then
745 logMessage := '[No key-val passed in but ALL Email is set! Emailproc_id=' || l_emailproc_id || ' action='||l_action||' parameter1='|| v_params.parameter1 ||' parameter2='|| v_params.parameter2 || ']';
746 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'IEM.PLSQL.IEM_RULES_ENGINE_PUB.AUTO_PROCESS_EMAIL.'||l_module||'.PARAMS', logMessage);
747 end if;
748 y := y + 1;
749 End Loop;
750
751 x_result := FND_API.G_TRUE;
752 exit;
753 --Action is UpdateSR, but no valid SR_id
754 else
755 if l_log_enabled then
756 logMessage := '[All Email is set! Emailproc_id=' || l_emailproc_id || ' action='||l_action||'. But ServiceRequest ID is null. So continue eval next rule.';
757 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'IEM.PLSQL.IEM_RULES_ENGINE_PUB.AUTO_PROCESS_EMAIL.'||l_module||'.PARAMS', logMessage);
758 end if;
759 end if;
760 else
761 --Getting parameter, then do validation on particular action
762 x_action := l_action;
763 y := 1;
764 l_emailproc_id := v_emailprocs.emailproc_id;
765
766 x_parameters.delete;
767 For v_params in c_params(l_emailproc_id) Loop
768 x_parameters(y).parameter1 := v_params.parameter1;
769 x_parameters(y).parameter2 := v_params.parameter2;
770 x_parameters(y).parameter3 := v_params.parameter3;
771 x_parameters(y).type := v_params.parameter_tag;
772
773 if l_log_enabled then
774 logMessage := '[No key-val passed in but ALL Email is set! Emailproc_id=' || l_emailproc_id || ' action='||l_action||' parameter1='|| v_params.parameter1 ||' parameter2='|| v_params.parameter2 || ']';
775 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'IEM.PLSQL.IEM_RULES_ENGINE_PUB.AUTO_PROCESS_EMAIL.'||l_module||'.PARAMS', logMessage);
776 end if;
777 y := y + 1;
778 End Loop;
779
780 -- For bug 3809733, to return Emailproc_id ( to be stored in iem_post_mdts.category_map_id. exmple:if return 123, store as -123)
781 -- so that corresponding Category_IDs could be used later
782 if l_module = 'DOCUMENTRETRIEVAL' then
783 x_parameters(y).parameter1 := 'RULE_ID';
784 x_parameters(y).parameter2 := l_emailproc_id;
785 end if;
786
787 --Verify if there is document under category for DOCUMENT MAPPING
788 --If not, continue next rule evaluation.
789 if l_action = 'MES_CATEGORY_MAPPING' then
790 l_doc_total := get_document_total(x_parameters(1).parameter1);
791 if (l_doc_total > 0 ) then
792 --exit with correct result
793 x_result := FND_API.G_TRUE;
794 exit;
795 end if;
796 elsif l_action = 'AUTOACKNOWLEDGE' then
797 if is_document_exist( x_parameters(1).parameter1,x_parameters(1).parameter2 )= FND_API.G_TRUE then
798 x_result := FND_API.G_TRUE;
799 exit;
800 end if;
801 elsif l_action='AUTOREPLYSPECIFIEDDOC' then
802 FOR m IN x_parameters.FIRST..x_parameters.LAST loop
803 if is_document_exist( x_parameters(m).parameter1,x_parameters(m).parameter2 )= FND_API.G_FALSE then
804 l_doc_exist_auto_reply := false;
805 exit;
806 end if;
807 end loop;
808
809 if l_doc_exist_auto_reply then
810 x_result := FND_API.G_TRUE;
811 exit;
812 end if;
813 else --NO validation on other action, so result is true, return parameters
814 x_result := FND_API.G_TRUE;
815 exit;
816 end if; --end if l_action = 'MES_CATEGORY_MAPPING'
817
818
819 end if; --end if l_action = 'UPDSERVICEREQID'
820 END IF; --v_emailprocs.all_email = 'Y'
821
822 End Loop;
823 End if;
824 --==========--
825 --End of API--
826 --==========--
827
828 --Standard check of p_commit
829 If FND_API.To_Boolean(p_commit) then
830 COMMIT WORK;
831 End if;
832
833 --Standard call to get message count and if count is 1 then get message info
834 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
835
836 EXCEPTION
837 when NO_DATA_FOUND THEN
838 Rollback to Auto_Process_Email_PUB;
839 x_return_status := FND_API.G_RET_STS_ERROR;
840 FND_MESSAGE.SET_NAME('IEM','IEM_NO_ACTION');
841 FND_MSG_PUB.Add;
842 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
843
844 if l_exception_log then
845 errorMessage := '[There is no action in iem_actions corresponding to the email_proc_id' ||l_emailproc_id||']' ;
846 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'IEM.PLSQL.IEM_RULES_ENGINE_PUB.AUTO_PROCESS_EMAIL', errorMessage);
847 end if;
848
849 WHEN IEM_UNKNOWN_RULE_TYPE_EXP THEN
850 Rollback to Auto_Process_Email_PUB;
851 x_return_status := FND_API.G_RET_STS_ERROR;
852 FND_MESSAGE.SET_NAME('IEM','IEM_UNKNOWN_RULE_TYPE_EXP');
853 FND_MSG_PUB.Add;
854 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
855
856 if l_exception_log then
857 errorMessage := '[Unknown Rule type... p_rule_type=' || p_rule_type ||']' ;
858 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'IEM.PLSQL.IEM_RULES_ENGINE_PUB.AUTO_PROCESS_EMAIL', errorMessage);
859 end if;
860
861 WHEN FND_API.G_EXC_ERROR THEN
862 Rollback to Auto_Process_Email_PUB;
863 x_return_status := FND_API.G_RET_STS_ERROR;
864 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
865
866 if l_exception_log then
867 errorMessage := '[' || sqlcode || sqlerrm || ']' || ' Execution Error';
868 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'IEM.PLSQL.IEM_RULES_ENGINE_PUB.AUTO_PROCESS_EMAIL.EXEC_ERROR', errorMessage);
869 end if;
870
871 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
872 Rollback to Auto_Process_Email_PUB;
873 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
874 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
875
876 if l_exception_log then
877 errorMessage := '[' || sqlcode || sqlerrm || ']' || ' Unexpected Execution Error';
878 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'IEM.PLSQL.IEM_RULES_ENGINE_PUB.AUTO_PROCESS_EMAIL.UNEXP_EXEC_ERROR', errorMessage);
879 end if;
880
881 WHEN OTHERS THEN
882 Rollback to Auto_Process_Email_PUB;
883 x_return_status := FND_API.G_RET_STS_ERROR;
884 IF fnd_msg_pub.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
885 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
886 END IF;
887 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
888
889 if l_exception_log then
890 errorMessage := '[' || sqlcode || sqlerrm || ']' || ' Others';
891 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'IEM.PLSQL.IEM_RULES_ENGINE_PUB.AUTO_PROCESS_EMAIL.OTHERS', errorMessage);
892 end if;
893
894 END auto_process_email;
895
896 function is_valid ( p_value VARCHAR2 )
897 return VARCHAR2
898 is
899 l_value varchar2(256);
900 x_result varchar2(30) := FND_API.G_FALSE;
901 begin
902 l_value := TRIM(LTRIM(p_value));
903
904 if l_value is null then
905 x_result := FND_API.G_FALSE;
906 else
907 x_result := FND_API.G_TRUE;
908 end if;
909
910 return x_result;
911 end;
912
913 function get_document_total ( p_cat_id VARCHAR2 )
914 return number
915 is
916 l_value varchar2(256);
917 x_total number := 0;
918 begin
919 l_value := TRIM(LTRIM(p_cat_id));
920
921 if l_value is not null then
922
923
924 select count(ib.item_id) into x_total
925 from amv_c_chl_item_match cim,
926 jtf_amv_items_vl ib
927 where cim.channel_category_id = p_cat_id
928 and cim.channel_id is null
929 and cim.approval_status_type ='APPROVED'
930 and cim.table_name_code ='ITEM'
931 and cim.available_for_channel_date <= sysdate
932 and cim.item_id = ib.item_id
933 and nvl(ib.effective_start_date, sysdate) <= sysdate + 1
934 and nvl(ib.expiration_date, sysdate) >= sysdate;
935
936 /*
937 Select count(*) into x_total
938 from
939 (
940 SELECT i.item_id,i.item_name,i.description,i.item_type,i.last_update_date
941 FROM jtf_amv_items_vl i,jtf_amv_attachments_v a
942 WHERE i.item_id = a.attachment_used_by_id (+)
943 AND i.item_id IN
944 (
945 select ib.item_id
946 from amv_c_chl_item_match cim,
947 jtf_amv_items_vl ib
948 where cim.channel_category_id = p_cat_id
949 and cim.channel_id is null
950 and cim.approval_status_type ='APPROVED'
951 and cim.table_name_code ='ITEM'
952 and cim.available_for_channel_date <= sysdate
953 and cim.item_id = ib.item_id
954 and nvl(ib.effective_start_date, sysdate) <= sysdate + 1
955 and nvl(ib.expiration_date, sysdate) >= sysdate
956 )
957 GROUP BY i.item_id,
958 i.item_name,
959 i.description,
960 i.item_type,
961 i.last_update_date
962 );
963 */
964 end if;
965
966 return x_total;
967 end;
968
969
970 function is_document_exist ( p_cat_id VARCHAR2, p_doc_id VARCHAR2 )
971 return VARCHAR2
972 is
973 l_value varchar2(256);
974 l_total number :=0 ;
975 x_result varchar2(30) := FND_API.G_FALSE;
976 begin
977 l_value := TRIM(LTRIM(p_cat_id));
978
979 if l_value is not null then
980
981 select count(ib.item_id) into l_total
982 from amv_c_chl_item_match cim,
983 jtf_amv_items_vl ib
984 where cim.channel_category_id = l_value
985 and cim.channel_id is null
986 and cim.approval_status_type ='APPROVED'
987 and cim.table_name_code ='ITEM'
988 and ib.item_id = p_doc_id
989 and cim.available_for_channel_date <= sysdate
990 and cim.item_id = ib.item_id
991 and nvl(ib.effective_start_date, sysdate) <= sysdate + 1
992 and nvl(ib.expiration_date, sysdate) >= sysdate;
993
994 end if;
995
996 if ( l_total > 0 ) then
997 x_result := FND_API.G_TRUE;
998 else
999 x_result := FND_API.G_FALSE;
1000 end if;
1001
1002 return x_result;
1003 end;
1004
1005 END IEM_RULES_ENGINE_PUB;
[Home] [Help]