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