1 PACKAGE BODY IEM_ROUTE_CLASS_PUB AS
2 /* $Header: iempclsb.pls 120.2 2005/06/23 13:04:09 appldev ship $ */
3 --
4 --
5 -- Purpose: Mantain route classification related operations
6 --
7 -- MODIFICATION HISTORY
8 -- Person Date Comments
9 -- Liang Xia 01/10/2002 fix bug that if the number of rules for a classification is less than
10 -- that of classification with higher priority, this classification
11 -- is never satisfied. ( No bug logged, shipped with FP-M )
12 -- Liang Xia 11/18/2002 Added dynamic classification. Shipped in MP-Q ( 11.5.9 )
13 -- Liang Xia 12/06/2004 Changed for 115.11 schema complaince
14 -- Liang Xia 06/24/2005 Fixed GSCC sql.46 ( bug 4452895 )
15 -- --------- ------ ------------------------------------------
16
17 /*GLOBAL VARIABLES FOR PRIVATE USE
18 ==================================*/
19 G_PKG_NAME VARCHAR2(100):= 'IEM_ROUTE_CLASS_PUB';
20
21 --The record type for passing rules
22 TYPE Rules_rec_type is RECORD (
23 key iem_route_rules.key_type_code%type,
24 operator iem_route_rules.operator_type_code%type,
25 value iem_route_rules.value%type);
26
27 --The table type for passing rules
28 TYPE Rules_tbl_type IS table of Rules_rec_type INDEX BY BINARY_INTEGER;
29
30
31
32 /* PRIVATE PROCEDURES NOT AVAILABLE TO THE PUBLIC
33 ================================================*/
34
35 /* Evaluate And conditions */
36 PROCEDURE evaluateAnd(keyVals IN iem_route_pub.keyVals_tbl_type, rules IN Rules_tbl_type, fireClass OUT NOCOPY Boolean)
37 IS
38
39 x number := 1;
40 y number := 1;
41
42 keyToFind iem_route_class_rules.key_type_code%type;
43 operator iem_route_class_rules.operator_type_code%type;
44 valueToFind iem_route_class_rules.value%type;
45
46 foundKey Boolean;
47 foundKeyValue Boolean;
48
49 numberOfKeys Number;
50 numberOfRules Number;
51
52 errorMessage varchar2(2000);
53
54 Begin
55
56
57 numberOfKeys := keyVals.count;
58 numberofRules := rules.count;
59
60 --Evaluate each rule one at a time
61 while x <= numberOfRules loop
62
63 --Copy the rule into temp variables to save repetitive calls to the UDT
64 keyToFind := rules(x).key;
65 valueToFind := rules(x).value;
66 operator := rules(x).operator;
67
68 y := 1;
69 foundKey := false;
70 foundKeyValue := false;
71
72 --Search through all the keys that got passed in
73 while y <= numberOfKeys loop
74
75 --Make the key comparison
76 if keyToFind = keyVals(y).key then
77 foundKey := true;
78
79 --If the key is found then see if the value matches up based on the operator
80 if iem_operators_pvt.satisfied(keyVals(y).value, operator, valueToFind, keyVals(y).datatype) then
81 foundKeyValue := true;
82 end if;
83 --Exit since we found what we wanted
84 EXIT;
85 end if;
86
87 y := y + 1;
88 end loop;
89
90 --If we were unable to find the key or the value then exit since this is AND chaining
91 if (foundKey = false or foundKeyValue = false) then
92 fireClass := false;
93 EXIT;
94 else
95 fireClass := true;
96 end if;
97
98 x := x + 1;
99 end loop;
100
101 EXCEPTION
102 When others then
103
104 if( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
105 errorMessage := '[' || sqlcode || sqlerrm || ']' || ' Others';
106 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'IEM.PLSQL.IEM_ROUTE_CLASS_PUB.EVALUATEAND.OTHERS', errorMessage);
107 end if;
108
109 End evaluateAnd;
110
111
112 /* Evaluate OR conditions */
113 PROCEDURE evaluateOr(keyVals IN iem_route_pub.keyVals_tbl_type, rules IN Rules_tbl_type, fireClass OUT NOCOPY Boolean)
114 IS
115
116 x number := 1;
117 y number := 1;
118
119 keyToFind iem_route_class_rules.key_type_code%type;
120 operator iem_route_class_rules.operator_type_code%type;
121 valueToFind iem_route_class_rules.value%type;
122
123 foundKeyValue Boolean;
124
125 numberOfKeys Number;
126 numberOfRules Number;
127
128 errorMessage varchar2(2000);
129
130 Begin
131
132 numberOfKeys := keyVals.count;
133 numberofRules := rules.count;
134
135 --Evaluate each rule one at a time
136 while x <= numberOfRules loop
137
138 --Copy the rule into temp variables to save repetitive calls to the UDT
139 keyToFind := rules(x).key;
140 valueToFind := rules(x).value;
141 operator := rules(x).operator;
142
143 y := 1;
144 foundKeyValue := false;
145
146 --Search through all the keys that got passed in
147 while y <= numberOfKeys loop
148
149 --Make the key comparison case insensitive
150 if upper(keyToFind) = upper(keyVals(y).key) then
151 --If the key is found then see if the value matches up based on the operator
152 if iem_operators_pvt.satisfied(keyVals(y).value, operator, valueToFind, keyVals(y).datatype) then
153 foundKeyValue := true;
154 end if;
155 --Exit since we found what we wanted
156 EXIT;
157 end if;
158
159 y := y + 1;
160 end loop;
161
162 --If we found a key value pair then exit since this is OR chaining
163 if foundKeyValue then
164 fireClass := true;
165 EXIT;
166 else
167 fireClass := false;
168 end if;
169
170 x := x + 1;
171 end loop;
172
173 EXCEPTION
174 When others then
175 if( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
176 errorMessage := '[' || sqlcode || sqlerrm || ']' || ' Others';
177 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'IEM.PLSQL.IEM_ROUTE_CLASS_PUB.EVALUATEOR.OTHERS', errorMessage);
178 end if;
179 End evaluateOr;
180
181 PROCEDURE classify(
182 p_api_version_number IN Number,
183 p_init_msg_list IN VARCHAR2 := NULL,
184 p_commit IN VARCHAR2 := NULL,
185 p_keyVals_tbl IN IEM_ROUTE_PUB.keyVals_tbl_type,
186 p_accountId IN Number,
187 x_classificationId OUT NOCOPY Number,
188 x_return_status OUT NOCOPY VARCHAR2,
189 x_msg_count OUT NOCOPY NUMBER,
190 x_msg_data OUT NOCOPY VARCHAR2)
191
192 IS
193 l_api_version_number CONSTANT NUMBER:= 1.0;
194 l_api_name CONSTANT VARCHAR2(30):= 'Classify';
195
196
197 --The active classifications in the system
198 cursor c_classifications (p_enabled_flag iem_account_route_class.enabled_flag%type,
199 p_account_id iem_mstemail_accounts.email_account_id%type)
200 is
201 select
202 a.route_classification_id,
203 a.procedure_name,
204 a.boolean_type_code
205 from
206 iem_route_classifications a,
207 iem_account_route_class b
208 where
209 a.route_classification_id = b.route_classification_id
210 and
211 b.enabled_flag = p_enabled_flag
212 and
213 b.email_account_id = p_account_id
214 and
215 a.route_classification_id <> 0
216 order by b.priority;
217
218 --All the rules for a classification
219 cursor c_rules (p_route_classification_id iem_route_classifications.route_classification_id%type)
220 is
221 select
222 key_type_code,
223 operator_type_code,
224 value
225 from
226 iem_route_class_rules
227 where
228 route_classification_id = p_route_classification_id;
229
230
231 x number:= 1;
232 ruleHold Rules_tbl_type;
233 classSatisfied Boolean := false;
234 enabledFlag varchar(1):= 'Y';
235 booleanTypeCode iem_route_classifications.boolean_type_code%type;
236
237 runTimekeyVals_tbl IEM_ROUTE_PUB.keyVals_tbl_type;
238 procedureName iem_route_classifications.procedure_name%type;
239 returnParamType iem_route_class_rules.key_type_code%type;
240 runTimeSuccess Boolean := true;
241
242 l_result VARCHAR2(256);
243 l_return_status VARCHAR2(20) := FND_API.G_RET_STS_SUCCESS;
244 l_msg_count NUMBER := 0;
245 l_msg_data VARCHAR2(2000);
246
247 l_log_enabled BOOLEAN := false;
248 l_exception_log BOOLEAN := false;
249 logMessage varchar2(2000);
250 errorMessage varchar2(2000);
251
252 BEGIN
253
254 --Standard begin of API savepoint
255 SAVEPOINT Classify_PUB;
256
257 -- Standard call to check for call compatibility.
258 IF NOT FND_API.Compatible_API_Call(
259 l_api_version_number,
260 p_api_version_number,
261 l_api_name,
262 G_PKG_NAME)
263 THEN
264 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
265 END IF;
266
267 -- Initialize message list if p_init_msg_list is set to TRUE.
268 IF FND_API.to_Boolean( p_init_msg_list ) THEN
269 FND_MSG_PUB.initialize;
270 END IF;
271
272 -- Initialize API return status to SUCCESS
273 x_return_status := FND_API.G_RET_STS_SUCCESS;
274
275 --=================--
276 -- Begining of API --
277 --=================--
278 FND_LOG_REPOSITORY.init(null,null);
279
280
281 l_log_enabled := FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ;
282
283 l_exception_log := FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ;
284
285 if l_log_enabled then
286 logMessage := '[p_account_id=' || to_char(p_accountid) || ']';
287 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'IEM.PLSQL.IEM_ROUTE_CLASS_PUB.ROUTE.START', logMessage);
288 end if;
289
290
291 --Initialize group to 0
292 x_classificationId := 0;
293
294 --Iniitalize the Rule data structure
295 --ruleHold.delete;
296
297 --Check to see if the passed in PL/SQL table has valid key-vals
298 If p_keyVals_tbl.count > 0 then
299
300 if l_log_enabled then
301 logMessage := '[p_keyValsCount=' || to_char(p_keyVals_tbl.count) || ']';
302 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'IEM.PLSQL.IEM_ROUTE_CLASS_PUB.ROUTE.KEY_VALS_MORE_THAN_0', logMessage);
303 end if;
304
305
306 --Get all the active routes in the system
307 For v_classifications in c_classifications (enabledFlag, p_accountId) Loop
308
309 --The boolean operator for the rule chaining
310 booleanTypeCode := v_classifications.boolean_type_code;
311 procedureName := v_classifications.procedure_name;
312
313 --Iniitalize the Rule data structure
314 ruleHold.delete;
315 x := 1;
316 -- Identify classification type
317 IF ( booleanTypeCode = 'DYNAMIC' ) THEN
318 if l_log_enabled then
319 logMessage := '[DYNAMIC procedure_name='||procedureName|| ']';
320 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'IEM.PLSQL.IEM_ROUTE_CLASS_PUB.DYNAMIC_CLASS', logMessage);
321 end if;
322
323 runTimekeyVals_tbl.delete;
324 runTimeSuccess := true;
325
326 --Get rules for the dynamic route and load it into the data structure
327 For v_rules in c_rules(v_classifications.route_classification_id) Loop
328 returnParamType:= v_rules.key_type_code;
329
330 -- begin PROCEDURE processing
331 BEGIN
332 IEM_ROUTE_RUN_PROC_PVT.run_Procedure(
333 p_api_version_number =>P_Api_Version_Number,
334 p_init_msg_list => FND_API.G_FALSE,
335 p_commit => FND_API.G_FALSE,--P_Commit,
336 p_procedure_name => procedureName,
337 p_key_value => p_keyVals_tbl,
338 p_param_type => returnParamType,
339 x_result => l_result,
340 x_return_status => l_return_status,
341 x_msg_count => l_msg_count,
342 x_msg_data => l_msg_data);
343
344 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
345 if l_log_enabled then
346 logMessage := '[ERROR when execute procedure for RouteID: '||v_classifications.route_classification_id ||']';
347 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'IEM.PLSQL.IEM_ROUTE_CLASS_PUB.DYNAMIC_CLASS', logMessage);
348 end if;
349 runTimeSuccess := false;
350 else
351 -- Insert data in key-value pair table
352 if returnParamType = 'IEMNNUMBER' then
353 runTimekeyVals_tbl(x).key := 'IEMNDYNROUTERETURNVAL';
354 runTimekeyVals_tbl(x).value := l_result;
355 runTimekeyVals_tbl(x).datatype := 'N';
356 elsif returnParamType = 'IEMSVARCHAR2' then
357 runTimekeyVals_tbl(x).key := 'IEMSDYNROUTERETURNVAL';
358 runTimekeyVals_tbl(x).value := l_result;
359 runTimekeyVals_tbl(x).datatype := 'S';
360 end if;
361
362 if l_log_enabled then
363 logMessage := '[DYNAMIC ROUTE RETURNED VALUE =' || l_result || ']';
364 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'IEM.PLSQL.IEM_ROUTE_CLASS_PUB.DYNAMIC_CLASS', logMessage);
365 end if;
366 end if;
367 EXCEPTION
368 WHEN OTHERS THEN
369 runTimeSuccess := false;
370 if l_log_enabled then
371 logMessage := '[ERROR (Others) when execute procedure for keyId: '||v_classifications.route_classification_id ||'. error:'||sqlerrm||']';
372 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'IEM.PLSQL.IEM_ROUTE_CLASS_PUB.DYNAMIC_CLASS', logMessage);
373 end if;
374 END; -- end of PROCEDURE processing
375
376 -- Exit when run procedure failed
377 if runTimeSuccess then
378 if returnParamType = 'IEMNNUMBER' then
379 ruleHold(x).key := 'IEMNDYNROUTERETURNVAL';
380 elsif returnParamType = 'IEMSVARCHAR2' then
381 ruleHold(x).key := 'IEMSDYNROUTERETURNVAL';
382 end if;
383
384
385 ruleHold(x).operator := v_rules.operator_type_code;
386 ruleHold(x).value := v_rules.value;
387
388 if l_log_enabled then
389 logMessage := '[DYNAMIC ROUTE' || ruleHold(x).key || ruleHold(x).operator || ruleHold(x).value || ']';
390 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'IEM.PLSQL.IEM_ROUTE_CLASS_PUB.DYNAMIC_CLASS', logMessage);
391 end if;
392 else
393 exit;
394 end if;
395
396 x := x + 1;
397 End Loop;
398
399 --Depending on the operator call different evaluation functions
400 if runTimeSuccess then
401 evaluateAnd(runTimekeyVals_tbl, ruleHold, classSatisfied);
402 else
403 classSatisfied := false;
404 end if;
405
406 if classSatisfied then
407 x_classificationId := v_classifications.route_classification_id;
408 EXIT;
409 end if;
410
411
412 ELSIF ( booleanTypeCode = 'AND' ) or ( booleanTypeCode = 'OR') THEN
413 --Get all the rules for the route and load it into the data structure
414 For v_rules in c_rules(v_classifications.route_classification_id) Loop
415 ruleHold(x).key := v_rules.key_type_code;
416 ruleHold(x).operator := v_rules.operator_type_code;
417 ruleHold(x).value := v_rules.value;
418
419 if l_log_enabled then
420 logMessage := '[' || ruleHold(x).key || ruleHold(x).operator || ruleHold(x).value || ']';
421 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'IEM.PLSQL.IEM_ROUTE_CLASS_PUB.CLASSIFY.RULES', logMessage);
422 end if;
423
424
425 x := x + 1;
426 End Loop;
427
428
429 --Depending on the operator call different evaluation functions
430 if booleanTypeCode = 'AND' then
431 evaluateAnd(p_keyVals_tbl, ruleHold, classSatisfied);
432 elsif booleanTypeCode = 'OR' then
433 evaluateOr(p_keyVals_tbl, ruleHold, classSatisfied);
434 end if;
435
436
437 --If the rules got satisfied then check if group is valid
438 if classSatisfied then
439 x_classificationId := v_classifications.route_classification_id;
440 EXIT;
441 end if;
442 END IF; -- end if boolean_type is Dynamic
443 End Loop;
444
445 End if;
446 --==========--
447 --End of API--
448 --==========--
449
450 --Standard check of p_commit
451 If FND_API.To_Boolean(p_commit) then
452 COMMIT WORK;
453 End if;
454
455 --Standard call to get message count and if count is 1 then get message info
456 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
457
458 EXCEPTION
459 WHEN FND_API.G_EXC_ERROR THEN
460 Rollback to Classify_PUB;
461 x_return_status := FND_API.G_RET_STS_ERROR;
462 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
463 if l_exception_log then
464 errorMessage := '[' || sqlcode || sqlerrm || ']' || ' Execution Error';
465 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'IEM.PLSQL.IEM_ROUTE_CLASS_PUB.ROUTE.EXEC_ERROR', errorMessage);
466 end if;
467 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
468 Rollback to Classify_PUB;
469 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
470 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
471 if l_exception_log then
472 errorMessage := '[' || sqlcode || sqlerrm || ']' || ' Unexpected Execution Error';
473 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'IEM.PLSQL.IEM_ROUTE_CLASS_PUB.ROUTE.UNEXP_EXEC_ERROR', errorMessage);
474 end if;
475 WHEN OTHERS THEN
476 Rollback to Classify_PUB;
477 x_return_status := FND_API.G_RET_STS_ERROR;
478 IF fnd_msg_pub.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
479 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
480 END IF;
481 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
482
483 if l_exception_log then
484 errorMessage := '[' || sqlcode || sqlerrm || ']' || ' Others';
485 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'IEM.PLSQL.IEM_ROUTE_CLASS_PUB.ROUTE.OTHERS', errorMessage);
486 end if;
487
488
489
490 END;
491
492 END IEM_ROUTE_CLASS_PUB;
[Home] [Help]