[Home] [Help]
PACKAGE BODY: APPS.FUN_RULE_PUB
Source
1 PACKAGE BODY FUN_RULE_PUB AS
2 /*$Header: FUNXTMRULENGINB.pls 120.26 2010/04/13 00:54:39 rravikir ship $ */
3
4 --------------------------------------
5 -- declaration of private global varibles
6 --------------------------------------
7
8 TYPE t_parameter_rec IS RECORD
9 (
10 name VARCHAR2(30),
11 value VARCHAR2(240)
12 );
13
14 TYPE t_parameter_list IS TABLE OF t_parameter_rec INDEX BY BINARY_INTEGER;
15
16 TYPE multi_param_value IS TABLE OF VARCHAR2(1024) INDEX BY BINARY_INTEGER;
17
18 m_RuleResultTable fun_rule_results_table;
19
20 g_parameter_list t_parameter_list;
21 g_parameter_count NUMBER := 0;
22
23 m_instance_label VARCHAR2(150) := NULL;
24 m_org_id NUMBER := NULL;
25 m_instance_context VARCHAR2(1) := 'N';
26
27
28 sDFFTableName VARCHAR2(30);
29
30 g_sRuleDetailSql VARCHAR2(2000) := 'SELECT FRO.RESULT_TYPE, FRD.RULE_DETAIL_ID, FRD.OPERATOR ,
31 FRD.RULE_OBJECT_ID,FRO.FLEXFIELD_NAME , FRO.FLEXFIELD_APP_SHORT_NAME,
32 UPPER(NVL(FRO.MULTI_RULE_RESULT_FLAG, ''N'')), UPPER(USE_DEFAULT_VALUE_FLAG)
33 FROM FUN_RULE_DETAILS FRD , FUN_RULE_OBJECTS_VL FRO
34 WHERE FRO.RULE_OBJECT_ID = FRD.RULE_OBJECT_ID
35 AND FRO.RULE_OBJECT_NAME=:1
36 AND FRO.APPLICATION_ID = :2
37 AND NVL(FRD.ENABLED_FLAG,''N'') = ''Y''
38 ORDER BY FRD.SEQ';
39
40 g_sRuleDetailMOACSql VARCHAR2(2000) := 'SELECT FRO.RESULT_TYPE, FRD.RULE_DETAIL_ID, FRD.OPERATOR ,
41 FRD.RULE_OBJECT_ID,FRO.FLEXFIELD_NAME , FRO.FLEXFIELD_APP_SHORT_NAME,
42 UPPER(NVL(FRO.MULTI_RULE_RESULT_FLAG, ''N'')), UPPER(USE_DEFAULT_VALUE_FLAG)
43 FROM FUN_RULE_DETAILS FRD , FUN_RULE_OBJECTS_VL FRO
44 WHERE FRO.RULE_OBJECT_ID = FRD.RULE_OBJECT_ID
45 AND FRO.RULE_OBJECT_NAME=:1
46 AND FRO.APPLICATION_ID = :2
47 AND NVL(FRD.ENABLED_FLAG,''N'') = ''Y''
48 AND ( (INSTANCE_LABEL IS NULL AND :3 IS NULL) OR
49 (INSTANCE_LABEL IS NOT NULL AND :4 IS NOT NULL AND INSTANCE_LABEL = :5))
50 AND
51 ( (ORG_ID IS NULL AND :6 IS NULL) OR
52 (ORG_ID IS NOT NULL AND :7 IS NOT NULL AND ORG_ID = :8))
53 ORDER BY FRD.SEQ';
54
55 g_sCriteriaParamSql VARCHAR2(2000) := 'SELECT FRCP.PARAM_NAME,
56 FRC.CONDITION, FRC.PARAM_VALUE, FRCP.DATA_TYPE,
57 FRC.CASE_SENSITIVE_FLAG , FRC.CRITERIA_ID
58 FROM FUN_RULE_CRIT_PARAMS_B FRCP,
59 FUN_RULE_CRITERIA FRC
60 WHERE FRC.RULE_DETAIL_ID = :1
61 AND FRC.CRITERIA_PARAM_ID = FRCP.CRITERIA_PARAM_ID
62 UNION
63 SELECT LOOKUP_CODE,
64 FRC.CONDITION, FRC.PARAM_VALUE, ''STRINGS'',
65 FRC.CASE_SENSITIVE_FLAG , FRC.CRITERIA_ID
66 FROM FUN_LOOKUPS FLV,
67 FUN_RULE_CRIT_PARAMS_B FRCP,
68 FUN_RULE_CRITERIA FRC
69 WHERE LOOKUP_TYPE = ''FUN_RULE_SEED_PARAMS''
70 AND FRC.RULE_DETAIL_ID = :2
71 AND FRC.CRITERIA_PARAM_ID = FRCP.CRITERIA_PARAM_ID
72 AND FRCP.PARAM_NAME = FLV.LOOKUP_CODE';
73
74 g_sMultiCriteriaParamValueSql VARCHAR2(1000) := 'SELECT FRMP.PARAM_VALUE
75 FROM FUN_RULE_PARAM_VALUES FRMP,
76 FUN_RULE_CRITERIA FRC
77 WHERE FRC.RULE_DETAIL_ID = :1
78 AND FRC.CRITERIA_ID = FRMP.CRITERIA_ID
79 AND FRC.CRITERIA_ID = :2 ';
80
81 g_sValueSetSql VARCHAR2(1000) := 'SELECT FFVS.FORMAT_TYPE
82 FROM FUN_RULE_OBJECTS_B FRO,
83 FND_FLEX_VALUE_SETS FFVS
84 WHERE FRO.FLEX_VALUE_SET_ID = FFVS.FLEX_VALUE_SET_ID
85 AND FRO.RULE_OBJECT_ID = :1 ';
86
87
88 g_sRuleObjectSql VARCHAR2(1000) := 'SELECT RESULT_TYPE, RULE_OBJECT_ID,
89 FLEXFIELD_NAME , FLEXFIELD_APP_SHORT_NAME
90 FROM FUN_RULE_OBJECTS_B
91 WHERE RULE_OBJECT_NAME = :1';
92
93 g_sRuleObjectMOACSql VARCHAR2(1000) := 'SELECT RESULT_TYPE, RULE_OBJECT_ID,
94 FLEXFIELD_NAME , FLEXFIELD_APP_SHORT_NAME
95 FROM FUN_RULE_OBJECTS_B
96 WHERE RULE_OBJECT_NAME = :1
97 AND ( (INSTANCE_LABEL IS NULL AND :2 IS NULL) OR
98 (INSTANCE_LABEL IS NOT NULL AND :3 IS NOT NULL AND INSTANCE_LABEL = :4))
99 AND
100 ( (ORG_ID IS NULL AND :5 IS NULL) OR
101 (ORG_ID IS NOT NULL AND :6 IS NOT NULL AND ORG_ID = :7))';
102
103
104 g_sResultValuesSql VARCHAR2(1000) := 'SELECT RULE_DETAIL_ID , RESULT_VALUE , RESULT_APPLICATION_ID , RULE_NAME
105 FROM FUN_RULE_DETAILS WHERE RULE_DETAIL_ID = :1' ;
106
107
108 g_sDefaultValuesSql varchar2(1000) := 'SELECT DEFAULT_VALUE , DEFAULT_APPLICATION_ID FROM FUN_RULE_OBJ_ATTRIBUTES
109 WHERE RULE_OBJECT_ID = :1 ';
110
111
112 g_sRuleObjectSql_orig VARCHAR2(2000) := g_sRuleObjectSql;
113 g_sRuleDetailSql_orig VARCHAR2(2000) := g_sRuleDetailSql;
114
115 C_LIKE varchar2(10) := 'LIKE';
116 C_CONTAINS varchar2(10) := 'CONTAIN';
117 C_EQUALS varchar2(10) := 'EQUALS';
118 C_NOT_EQUALS varchar2(30) := 'NOT_EQUALS';
119 C_IN varchar2(10) := 'IN';
120 C_NOT_IN varchar2(10) := 'NOT_IN';
121 C_GREATER_THAN varchar2(10) := 'GREATER';
122 C_LESSER_THAN varchar2(10) := 'LESS';
123 C_GREATER_THAN_EQUAL varchar2(40) := 'GREATER_EQUALS';
124 C_LESSER_THAN_EQUAL varchar2(40) := 'LESS_EQUALS';
125 C_BETWEEN varchar2(30) := 'BETWEEN';
126 C_EMPTY VARCHAR2(10) := 'EMPTY';
127 C_NO varchar2(10) := 'N';
128 C_YES varchar2(10) := 'Y';
129 C_STRINGS varchar2(10) := 'STRINGS';
130 C_NUMERIC varchar2(10) := 'NUMERIC';
131 C_DATE varchar2(10) := 'DATE';
132 C_MESSAGE varchar2(10) := 'MESSAGES';
133 C_RICHTEXT varchar2(10) := 'RICHTEXT';
134 C_VALUESET varchar2(10) := 'VALUESET';
135 C_MULTIVALUE varchar2(10) := 'MULTIVALUE';
136
137 C_AND varchar2(10) := 'AND';
138 C_OR varchar2(10) := 'OR';
139
140
141 C_INVALID_ORG NUMBER := -2;
142
143 NO_DEFAULT_VALUE_EXCEPTION EXCEPTION;
144 INVAILD_COLUMN_NAME EXCEPTION;
145 -----------------------------------------------------------------
146 -- Private procedures and functions used internally by Rule
147 -- Engine.
148 -----------------------------------------------------------------
149
150 PROCEDURE getMultiValueParamsArray(p_multi_param_value OUT NOCOPY multi_param_value,
151 p_rule_detail_id IN NUMBER,
152 p_rule_criteria_id IN NUMBER,
153 p_data_type IN VARCHAR2);
154
155 FUNCTION getResultValueDataType(p_ObjectType IN VARCHAR2 ,
156 p_RuleObjectId IN NUMBER) RETURN VARCHAR2;
157
158
159 PROCEDURE setResultValues(p_ret_val IN BOOLEAN,
160 p_isAnyActiveRule IN BOOLEAN,
161 p_rule_object_id IN NUMBER,
162 p_result_type IN VARCHAR2,
163 p_rule_object_name IN VARCHAR2,
164 p_flexfield_name IN VARCHAR2,
165 p_flexfield_app_short_name IN VARCHAR2,
166 p_rule_detail_id IN NUMBER);
167
168 FUNCTION populateRuleResultObjects RETURN FUN_RULE_RESULT;
169
170 FUNCTION checkIfMultiValueResultIsNull RETURN BOOLEAN;
171
172 FUNCTION matchCriteria(p_ParamName IN VARCHAR2, p_Condition VARCHAR2,
173 multiValueParamsArray IN multi_param_value, p_DataType IN VARCHAR2,
174 critObjectValue IN t_parameter_list, p_CaseSensitive IN VARCHAR2) RETURN BOOLEAN;
175
176 FUNCTION ContainsKey(critObjectValue IN t_parameter_list,
177 p_ParamName IN VARCHAR2) RETURN BOOLEAN;
178
179 FUNCTION get(critObjectValue IN t_parameter_list,
180 p_ParamName IN VARCHAR2) RETURN VARCHAR2;
181
182 FUNCTION getIndex(critObjectValue IN t_parameter_list,
183 p_ParamName IN VARCHAR2) RETURN NUMBER;
184
185 FUNCTION getDFFResultValue(p_RuleDetailId IN NUMBER,
186 p_FlexFieldName IN VARCHAR2,
187 p_FlexFieldAppShortName IN VARCHAR2) RETURN VARCHAR2;
188
189 FUNCTION getDFFDefaultValue(p_RuleDetailId IN NUMBER,
190 p_FlexFieldName IN VARCHAR2,
191 p_FlexFieldAppShortName IN VARCHAR2) RETURN VARCHAR2;
192
193 FUNCTION getResultValue(p_ruleDetailId IN NUMBER, p_ObjectType IN VARCHAR2) RETURN VARCHAR2;
194
195 FUNCTION getDefaultValue(p_ruleObjectId IN NUMBER, p_ObjectType IN VARCHAR2) RETURN VARCHAR2;
196
197 PROCEDURE getRuleObjectInfo(p_RuleObjectName IN VARCHAR2);
198
199 FUNCTION isMatched(p_ParamName VARCHAR2, p_DataType VARCHAR2,
200 p_Condition VARCHAR2, p_ParamValue VARCHAR2,
201 p_ValueToBeCompared VARCHAR2,
202 p_CaseSensitive VARCHAR2) RETURN BOOLEAN;
203
204 FUNCTION getComparedData(p_Obj1 IN VARCHAR2, p_Obj2 IN VARCHAR2,
205 p_DataType IN VARCHAR2, p_CaseSensitive IN VARCHAR2) RETURN NUMBER;
206
207 FUNCTION compareTo(p_Obj1 IN VARCHAR2, p_Obj2 IN VARCHAR2 , p_DataType IN VARCHAR2) RETURN NUMBER;
208
209
210 FUNCTION isMatchedLikeContain(p_ParamName IN VARCHAR2, p_DataType IN VARCHAR2,
211 p_ParamValue IN VARCHAR2,p_ValueToBeCompared IN VARCHAR2,
212 p_CaseSensitive IN VARCHAR2) RETURN BOOLEAN;
213
214 FUNCTION isMatchedEquals(p_ParamName IN VARCHAR2, p_DataType IN VARCHAR2,
215 p_ParamValue IN VARCHAR2,p_ValueToBeCompared IN VARCHAR2,
216 p_CaseSensitive IN VARCHAR2) RETURN BOOLEAN;
217
218 FUNCTION isMatchedNotEquals(p_ParamName IN VARCHAR2, p_DataType IN VARCHAR2,
219 p_ParamValue IN VARCHAR2,p_ValueToBeCompared IN VARCHAR2,
220 p_CaseSensitive IN VARCHAR2) RETURN BOOLEAN;
221
222 FUNCTION isMatchedGreater(p_ParamName IN VARCHAR2, p_DataType IN VARCHAR2,
223 p_ParamValue IN VARCHAR2,p_ValueToBeCompared IN VARCHAR2,
224 p_CaseSensitive IN VARCHAR2) RETURN BOOLEAN;
225
226 FUNCTION isMatchedLesser(p_ParamName IN VARCHAR2, p_DataType IN VARCHAR2,
227 p_ParamValue IN VARCHAR2,p_ValueToBeCompared IN VARCHAR2,
228 p_CaseSensitive IN VARCHAR2) RETURN BOOLEAN;
229
230 FUNCTION isMatchedGreaterEqual(p_ParamName IN VARCHAR2, p_DataType IN VARCHAR2,
231 p_ParamValue IN VARCHAR2,p_ValueToBeCompared IN VARCHAR2,
232 p_CaseSensitive IN VARCHAR2) RETURN BOOLEAN;
233
234 FUNCTION isMatchedLesserEqual(p_ParamName IN VARCHAR2, p_DataType IN VARCHAR2,
235 p_ParamValue IN VARCHAR2,p_ValueToBeCompared IN VARCHAR2,
236 p_CaseSensitive IN VARCHAR2) RETURN BOOLEAN;
237
238 FUNCTION isMatchedEmpty(p_ValueToBeCompared IN VARCHAR2) RETURN BOOLEAN;
239
240 FUNCTION isRuleValid(p_param_view_name in varchar2, l_where_clause IN VARCHAR2) RETURN BOOLEAN;
241 FUNCTION populateGTBulkTable(p_insert_statement IN VARCHAR2) RETURN BOOLEAN;
242
243 PROCEDURE refreshGTBulkTable;
244
245 PROCEDURE TEST;
246
247 PROCEDURE clearInstanceContext;
248
249 PROCEDURE init_parameter_list IS
250 BEGIN
251 g_parameter_count := 0;
252
253 m_ruleDetailId := NULL;
254 m_resultApplicationId := NULL;
255 m_ruleName := NULL;
256 m_resultValue := NULL;
257 m_resultValueDataType := NULL;
258 m_multiRuleResultFlag := NULL;
259 m_useDefaultValueFlag := NULL;
260 m_noRulesSatisfied := FALSE;
261 m_attributeCategory := NULL;
262 m_attribute1 := NULL;
263 m_attribute2 := NULL;
264 m_attribute3 := NULL;
265 m_attribute4 := NULL;
266 m_attribute5 := NULL;
267 m_attribute6 := NULL;
268 m_attribute7 := NULL;
269 m_attribute8 := NULL;
270 m_attribute9 := NULL;
271 m_attribute10 := NULL;
272 m_attribute11 := NULL;
273 m_attribute12 := NULL;
274 m_attribute13 := NULL;
275 m_attribute14 := NULL;
276 m_attribute15 := NULL;
277
278 END init_parameter_list;
279
280 /**
281 * This procedure sets the instance context for the Rule Object Instance.
282 * Once set, the rule object id will be derived from the Rule Object Instance
283 * and will be used throughout.
284 *
285 * p_application_short_name Application Short Name
286 * p_rule_object_name Name of rule object
287 * p_instance_label Instance label of rule object
288 * p_org_id org id for the rule object instance.
289 */
290
291 PROCEDURE set_instance_context(p_rule_object_name IN VARCHAR2, p_application_short_name IN VARCHAR2,
292 p_instance_label IN VARCHAR2 , p_org_id IN NUMBER) IS
293
294 L_DUMMY VARCHAR2(1) := 'N';
295 l_org_id NUMBER := null;
296 l_temp_org_id NUMBER; -- Added for Bug 9025784
297
298 BEGIN
299 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
300 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'set_instance_context :->p_application_short_name='||p_application_short_name||'**p_rule_object_name='||p_rule_object_name||'**p_instance_label='||p_instance_label, FALSE);
301 end if;
302
303 -- Added for Bug 9025784 - Start
304 -- Validating the Org Id before querying the rule object
305 l_temp_org_id := p_org_id;
306
307 IF(l_temp_org_id IS NOT NULL AND FUN_RULE_VALIDATE_PKG.validate_org_id(l_temp_org_id)) THEN
308 l_org_id := l_temp_org_id;
309 END IF;
310 -- Added for Bug 9025784 - End
311
312 SELECT 'Y', RULE_OBJECT_ID INTO L_DUMMY, m_ruleObjectId FROM
313 FUN_RULE_OBJECTS_B FRO, FND_APPLICATION APPL
314 WHERE RULE_OBJECT_NAME = p_rule_object_name
315 AND FRO.APPLICATION_ID = APPL.APPLICATION_ID
316 AND APPL.APPLICATION_SHORT_NAME = p_application_short_name
317 AND
318 ( (INSTANCE_LABEL IS NULL AND p_instance_label IS NULL) OR
319 (INSTANCE_LABEL IS NOT NULL AND p_instance_label IS NOT NULL AND INSTANCE_LABEL = p_instance_label))
320 AND
321 ( (ORG_ID IS NULL AND l_org_id IS NULL) OR
322 (ORG_ID IS NOT NULL AND l_org_id IS NOT NULL AND ORG_ID = l_org_id))
323 AND PARENT_RULE_OBJECT_ID IS NOT NULL;
324
325 m_instance_label := p_instance_label;
326 m_instance_context := 'Y';
327
328 --If p_org_id is passed, then validate it and then store it for later use
329 --in MOAC changes.
330
331 IF (l_org_id IS NOT NULL) THEN
332 IF(NOT FUN_RULE_VALIDATE_PKG.validate_org_id(l_org_id)) THEN
333 fnd_message.set_name('FUN', 'FUN_RULE_INVALID_ORG_ID');
334 app_exception.raise_exception;
335 END IF;
336 END IF;
337
338 m_org_id := l_org_id;
339
340 --No need to validate the p_application_short_name, because the above SQL anyway
341 --throws NO DATA FOUND otherwise.
342
343 EXCEPTION
344 WHEN NO_DATA_FOUND THEN
345 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
346 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION , 'FUN_RULE_PUB.set_instance_context:->NO_DATA_FOUND', FALSE);
347 END IF;
348
349 fnd_message.set_name('FUN','FUN_RULE_INVAID_ROB_INSTANCE');
350 app_exception.raise_exception;
351 END;
352
353
354 PROCEDURE add_parameter(name VARCHAR2, value VARCHAR2) IS
355 l_parameter_rec t_parameter_rec;
356 BEGIN
357
358 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
359 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'add_parameter:->name='||name||'**Value='||value, FALSE);
360 end if;
361
362 l_parameter_rec.name := name;
363 l_parameter_rec.value := value;
364
365 g_parameter_count := g_parameter_count + 1;
366 g_parameter_list(g_parameter_count) := l_parameter_rec;
367 END add_parameter;
368
369
370 PROCEDURE add_parameter(name VARCHAR2, value DATE) IS
371 BEGIN
372 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
373 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'add_parameter DATE:->name='||name||'**Value='||value, FALSE);
374 end if;
375
376 add_parameter(name, fnd_date.date_to_canonical(value));
377 END add_parameter;
378
379
380 PROCEDURE add_parameter(name VARCHAR2, value NUMBER) IS
381 BEGIN
382 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
383 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'add_parameter NUMBER:->name='||name||'**Value='||value, FALSE);
384 end if;
385
386 add_parameter(name, fnd_number.number_to_canonical(value));
387 END add_parameter;
388
389 FUNCTION get_string RETURN VARCHAR2 IS
390 BEGIN
391 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
392 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'get_string :->m_resultValue='||m_resultValue, FALSE);
393 end if;
394
395 RETURN m_resultValue;
396 END;
397
398
399 FUNCTION get_number RETURN VARCHAR2 IS
400 BEGIN
401 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
402 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'get_number :->m_resultValue='||m_resultValue, FALSE);
403 end if;
404
405 RETURN fnd_number.canonical_to_number(m_resultValue);
406 END;
407
408 FUNCTION get_date RETURN VARCHAR2 IS
409 BEGIN
410 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
411 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'get_date :->m_resultValue='||m_resultValue, FALSE);
412 end if;
413
414 RETURN fnd_date.canonical_to_date(m_resultValue);
415 END;
416
417 PROCEDURE apply_rule(p_application_short_name IN VARCHAR2, p_rule_object_name IN VARCHAR2)
418 IS
419
420 l_application_id NUMBER;
421 destination_cursor INTEGER;
422 l_num_rows_processed INTEGER;
423 params_cursor INTEGER;
424 params_rows_processed INTEGER;
425
426 l_ret_val boolean := true;
427 l_all_rule_retval boolean := false; --to track at least one rule is satisfied or not.
428
429 l_result_type FUN_RULE_OBJECTS_B.RESULT_TYPE%TYPE;
430 l_rule_detail_id FUN_RULE_DETAILS.RULE_DETAIL_ID%TYPE;
431 l_prev_rule_detail_id FUN_RULE_DETAILS.RULE_DETAIL_ID%TYPE;
432 l_operator FUN_RULE_DETAILS.OPERATOR%TYPE;
433 l_rule_object_id FUN_RULE_DETAILS.RULE_OBJECT_ID%TYPE;
434 l_flexfield_name FUN_RULE_OBJECTS_B.FLEXFIELD_NAME%TYPE;
435 l_flexfield_app_short_name FUN_RULE_OBJECTS_B.FLEXFIELD_APP_SHORT_NAME%TYPE;
436 l_multiRuleResultFlag FUN_RULE_OBJECTS_B.MULTI_RULE_RESULT_FLAG%TYPE;
437 l_useDefaultValueFlag FUN_RULE_OBJECTS_B.USE_DEFAULT_VALUE_FLAG%TYPE;
438
439 l_param_name FUN_RULE_CRIT_PARAMS_B.PARAM_NAME%TYPE;
440 l_condition FUN_RULE_CRITERIA.CONDITION%TYPE;
441 l_param_value FUN_RULE_CRITERIA.PARAM_VALUE%TYPE;
442 l_data_type FUN_RULE_CRIT_PARAMS_B.DATA_TYPE%TYPE;
443 l_case_sensitive FUN_RULE_CRITERIA.CASE_SENSITIVE_FLAG%TYPE;
444 l_criteria_id FUN_RULE_CRITERIA.CRITERIA_ID%TYPE;
445
446 l_ParamMultiValueList multi_param_value;
447
448 l_return_result VARCHAR2(240);
449 l_isAnyActiveRule boolean := false;
450 l_count NUMBER := 1;
451 l_set_result_values boolean := false;
452
453 RuleResultTable fun_Rule_Results_Table := fun_Rule_Results_Table();
454 -- m_RuleResultTable RuleResultTableType;
455
456 l_old_moac_access_mode VARCHAR2(1);
457 l_old_org_id NUMBER;
458
459 BEGIN
460 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
461 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'apply_rule :->p_application_short_name='||p_application_short_name||'**p_rule_object_name='||p_rule_object_name, FALSE);
462 end if;
463
464
465 /*Rule Object Instance MOAC Changes:
466 * If p_org_id is passed and the set_instance_context is called, then set the MOAC context based on
467 * following logic.
468 * If Product team has not called MO_GLOBAL.INIT then raise an exception.
469 * Else, get the access_mode. If access_mode is not S, then set to S and the passed p_og_id.
470 */
471 l_old_moac_access_mode := MO_GLOBAL.get_access_mode();
472 l_old_org_id := MO_GLOBAL.get_current_org_id();
473
474 --Does validation and then sets the policy context to S, if its not S and
475 --the passed org id value is not same as current org id value.
476
477 IF (m_org_id IS NOT NULL) THEN
478 FUN_RULE_MOAC_PKG.SET_MOAC_ACCESS_MODE(m_org_id);
479 END IF;
480
481
482 l_application_id := FUN_RULE_UTILITY_PKG.getApplicationID(p_application_short_name);
483
484 IF(l_application_id IS NULL) THEN
485 l_application_id := FND_GLOBAL.RESP_APPL_ID;
486 END IF;
487
488 destination_cursor := DBMS_SQL.OPEN_CURSOR;
489
490
491 --IF Instance Context is set, then append the where clause with an extra bind variable i.e
492 --AND INSTANCE_LABEL = :3
493
494 IF (m_instance_context = 'Y') THEN
495 g_sRuleDetailSql := g_sRuleDetailMOACSql;
496 ELSE
497 g_sRuleDetailSql := g_sRuleDetailSql_orig;
498 END IF;
499
500 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
501 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'apply_rule :->g_sRuleDetailSql='||g_sRuleDetailSql, FALSE);
502 end if;
503
504 DBMS_SQL.PARSE(destination_cursor, g_sRuleDetailSql,DBMS_SQL.native);
505 dbms_sql.bind_variable(destination_cursor , '1' , p_rule_object_name);
506 dbms_sql.bind_variable(destination_cursor , '2' , l_application_id);
507
508 IF (m_instance_context = 'Y') THEN
509 dbms_sql.bind_variable(destination_cursor , '3' , m_instance_label);
510 dbms_sql.bind_variable(destination_cursor , '4' , m_instance_label);
511 dbms_sql.bind_variable(destination_cursor , '5' , m_instance_label);
512
513 dbms_sql.bind_variable(destination_cursor , '6' , m_org_id);
514 dbms_sql.bind_variable(destination_cursor , '7' , m_org_id);
515 dbms_sql.bind_variable(destination_cursor , '8' , m_org_id);
516 END IF;
517
518 dbms_sql.define_column(destination_cursor, 1, l_result_type , 30);
519 dbms_sql.define_column(destination_cursor, 2, l_rule_detail_id);
520 dbms_sql.define_column(destination_cursor, 3, l_operator , 3);
521 dbms_sql.define_column(destination_cursor, 4, l_rule_object_id);
522 dbms_sql.define_column(destination_cursor, 5, l_flexfield_name , 80);
523 dbms_sql.define_column(destination_cursor, 6, l_flexfield_app_short_name , 30);
524 dbms_sql.define_column(destination_cursor, 7, l_multiRuleResultFlag , 10);
525 dbms_sql.define_column(destination_cursor, 8, l_useDefaultValueFlag , 10);
526
527 l_num_rows_processed := DBMS_SQL.EXECUTE(destination_cursor);
528
529 while(dbms_sql.fetch_rows(destination_cursor) > 0 ) loop
530 l_isAnyActiveRule := true; --i.e Atleast one Rule is present.
531
532 dbms_sql.column_value(destination_cursor, 1, l_result_type);
533 dbms_sql.column_value(destination_cursor, 2, l_rule_detail_id );
534 dbms_sql.column_value(destination_cursor, 3, l_operator);
535 dbms_sql.column_value(destination_cursor, 4, l_rule_object_id);
536 dbms_sql.column_value(destination_cursor, 5, l_flexfield_name);
537 dbms_sql.column_value(destination_cursor, 6, l_flexfield_app_short_name);
538 dbms_sql.column_value(destination_cursor, 7, l_multiRuleResultFlag);
539 dbms_sql.column_value(destination_cursor, 8, l_useDefaultValueFlag);
540 m_multiRuleResultFlag := l_multiRuleResultFlag;
541 m_useDefaultValueFlag := l_useDefaultValueFlag;
542
543
544 IF (L_OPERATOR = C_OR) THEN
545 l_ret_val := false;
546 ELSE
547 l_ret_val := true;
548 END IF;
549
550 params_cursor := DBMS_SQL.OPEN_CURSOR;
551
552 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
553 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'apply_rule :->g_sCriteriaParamSql='||g_sCriteriaParamSql, FALSE);
554 end if;
555
556 DBMS_SQL.PARSE(params_cursor, g_sCriteriaParamSql,DBMS_SQL.native);
557 dbms_sql.bind_variable(params_cursor , '1' , l_rule_detail_id);
558 dbms_sql.bind_variable(params_cursor , '2' , l_rule_detail_id);
559
560 dbms_sql.define_column(params_cursor, 1, l_param_name , 30);
561 dbms_sql.define_column(params_cursor, 2, l_condition , 15);
562 dbms_sql.define_column(params_cursor, 3, l_param_value , 1024);
563 dbms_sql.define_column(params_cursor, 4, l_data_type, 15);
564 dbms_sql.define_column(params_cursor, 5, l_case_sensitive, 1);
565 dbms_sql.define_column(params_cursor, 6, l_criteria_id);
566
567 params_rows_processed := DBMS_SQL.EXECUTE(params_cursor);
568
569 while(dbms_sql.fetch_rows(params_cursor) > 0 ) loop
570 dbms_sql.column_value(params_cursor, 1, l_param_name );
571 dbms_sql.column_value(params_cursor, 2, l_condition );
572 dbms_sql.column_value(params_cursor, 3, l_param_value);
573 dbms_sql.column_value(params_cursor, 4, l_data_type);
574 dbms_sql.column_value(params_cursor, 5, l_case_sensitive);
575 dbms_sql.column_value(params_cursor, 6, l_criteria_id);
576
577
578 if(l_param_name = 'APPLICATION') then
579 if(NOT containsKey(g_parameter_list,'APPLICATION')) then
580 add_parameter('APPLICATION' , FND_GLOBAL.RESP_APPL_ID);
581 end if;
582 end if;
583
584 if(l_param_name = 'RESPONSIBILITY') then
585 if(NOT containsKey(g_parameter_list,'RESPONSIBILITY')) then
586 add_parameter('RESPONSIBILITY' , FND_GLOBAL.RESP_ID);
587 end if;
588 end if;
589
590
591 /********************************************************************************
592 MOAC Handle:
593 1) When the product team calls applyRule(), and if the ORGANIZATION parameter is
594 explicitly passed, we should just use that value.
595 2) If it is not, then see if the access mode is single. If it is, use that org
596 as the parameter value.
597 3) If it is not, then any criteria which uses ORGANIZATION as the parameter
598 fails - always.
599 **********************************************************************************/
600
601 if(l_param_name = 'ORGANIZATION') then
602 if(NOT containsKey(g_parameter_list,'ORGANIZATION')) then
603 add_parameter('ORGANIZATION' , FUN_RULE_UTILITY_PKG.get_moac_org_id);
604 end if;
605 end if;
606
607 if(l_param_name = 'USER') then
608 if(NOT containsKey(g_parameter_list,'USER')) then
609 add_parameter('USER' , FND_GLOBAL.USER_ID);
610 end if;
611 end if;
612
613
614 getMultiValueParamsArray(l_ParamMultiValueList, l_rule_detail_id, l_criteria_id , l_data_type);
615
616 IF(l_operator = C_AND) THEN
617 l_ret_val := matchCriteria(l_param_name, l_condition, l_ParamMultiValueList,
618 l_data_type, g_parameter_list, l_case_sensitive) AND l_ret_val ;
619 ELSIF(l_operator = C_OR) THEN
620 l_ret_val := matchCriteria(l_param_name, l_condition, l_ParamMultiValueList,
621 l_data_type, g_parameter_list, l_case_sensitive) OR l_ret_val ;
622 END IF;
623 end loop;
624 DBMS_SQL.CLOSE_CURSOR(params_cursor);
625
626 l_all_rule_retval := l_all_rule_retval OR l_ret_val;
627
628 setResultValues(l_ret_val,
629 l_isAnyActiveRule,
630 l_rule_object_id,
631 l_result_type,
632 p_rule_object_name,
633 l_flexfield_name,
634 l_flexfield_app_short_name,
635 l_rule_detail_id);
636
637
638 if(l_multiRuleResultFlag = 'N') then
639 if (l_ret_val) then exit; end if;
640 else
641 --For AR's Multi Rule Result we populate RuleResultObject here.
642 if(l_ret_val) then
643 RuleResultTable.extend;
644 RuleResultTable(l_count) := populateRuleResultObjects;
645 l_count := l_count+1;
646 end if;
647 end if;
648
649 end loop;
650
651 DBMS_SQL.CLOSE_CURSOR(destination_cursor);
652
653 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
654 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'apply_rule :->Before calling setResultValues', FALSE);
655 end if;
656
657 if(NOT l_all_rule_retval) then --implies that not a single Rule is satisfied.
658 setResultValues(l_ret_val,
659 l_isAnyActiveRule,
660 l_rule_object_id,
661 l_result_type,
662 p_rule_object_name,
663 l_flexfield_name,
664 l_flexfield_app_short_name,
665 l_rule_detail_id);
666 else
667 /*In case of Multi Rule Result, we should not make m_noRulesSatisfied as false
668 * if at least one result is returned i.e bAllRuleRetVal is true.
669 * */
670 if(l_multiRuleResultFlag = 'Y') then
671 m_noRulesSatisfied := false;
672 end if;
673
674 end if;
675
676
677 --If (no Rules are satisfied OR Not a single rule is there) and use default flag is NOT Y
678 --then return false;
679 if((m_noRulesSatisfied OR (NOT l_isAnyActiveRule)) AND m_useDefaultValueFlag <> 'Y') then
680 RAISE NO_DEFAULT_VALUE_EXCEPTION;
681 end if;
682
683
684 if(l_multiRuleResultFlag = 'Y') then --For Multi RUle Result Object Type
685 if(NOT l_all_rule_retval) then
686 RuleResultTable.extend;
687 RuleResultTable(l_count) := populateRuleResultObjects;
688 end if;
689 m_RuleResultTable := RuleResultTable;
690 end if;
691
692
693 /*Rule Object Instance MOAC Changes:
694 *Revert back the access mode and org id to the l_old_acess_mode and l_old_org_id
695 *And Clear The Instance Context if set.
696 */
697 IF (m_org_id IS NOT NULL) THEN
698 FUN_RULE_MOAC_PKG.SET_MOAC_POLICY_CONTEXT(l_old_moac_access_mode , l_old_org_id , m_org_id);
699 END IF;
700
701 clearInstanceContext;
702
703 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
704 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'End apply_rule', FALSE);
705 end if;
706
707 EXCEPTION
708 WHEN NO_DATA_FOUND THEN
709 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
710 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION , 'FUN_RULE_PUB.apply_rule:->NO_DATA_FOUND', FALSE);
711 END IF;
712
713 RAISE;
714
715 WHEN NO_DEFAULT_VALUE_EXCEPTION THEN
716 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
717 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION , 'FUN_RULE_PUB.apply_rule:->NO_DEFAULT_VALUE_EXCEPTION', FALSE);
718 END IF;
719
720 fnd_message.set_name('FUN','NO_DEFAULT_VALUE_EXCEPTION');
721 app_exception.raise_exception;
722
723 WHEN OTHERS THEN
724 IF DBMS_SQL.IS_OPEN(destination_cursor) THEN
725 DBMS_SQL.CLOSE_CURSOR(destination_cursor);
726 END IF;
727 IF DBMS_SQL.IS_OPEN(params_cursor) THEN
728 DBMS_SQL.CLOSE_CURSOR(params_cursor);
729 END IF;
730 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
731 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION , 'FUN_RULE_PUB.APLY_RULE:->'||SQLERRM, FALSE);
732 END IF;
733
734 RAISE;
735 END apply_rule;
736
737
738 FUNCTION apply_rule(p_application_short_name IN VARCHAR2, p_rule_object_name IN VARCHAR2)
739 RETURN BOOLEAN
740 IS
741
742 l_application_id NUMBER;
743 destination_cursor INTEGER;
744 l_num_rows_processed INTEGER;
745 params_cursor INTEGER;
746 params_rows_processed INTEGER;
747
748 l_ret_val boolean := true;
749 l_all_rule_retval boolean := false; --to track at least one rule is satisfied or not.
750
751 l_result_type FUN_RULE_OBJECTS_B.RESULT_TYPE%TYPE;
752 l_rule_detail_id FUN_RULE_DETAILS.RULE_DETAIL_ID%TYPE;
753 l_prev_rule_detail_id FUN_RULE_DETAILS.RULE_DETAIL_ID%TYPE;
754 l_operator FUN_RULE_DETAILS.OPERATOR%TYPE;
755 l_rule_object_id FUN_RULE_DETAILS.RULE_OBJECT_ID%TYPE;
756 l_flexfield_name FUN_RULE_OBJECTS_B.FLEXFIELD_NAME%TYPE;
757 l_flexfield_app_short_name FUN_RULE_OBJECTS_B.FLEXFIELD_APP_SHORT_NAME%TYPE;
758 l_multiRuleResultFlag FUN_RULE_OBJECTS_B.MULTI_RULE_RESULT_FLAG%TYPE;
759 l_useDefaultValueFlag FUN_RULE_OBJECTS_B.USE_DEFAULT_VALUE_FLAG%TYPE;
760
761 l_param_name FUN_RULE_CRIT_PARAMS_B.PARAM_NAME%TYPE;
762 l_condition FUN_RULE_CRITERIA.CONDITION%TYPE;
763 l_param_value FUN_RULE_CRITERIA.PARAM_VALUE%TYPE;
764 l_data_type FUN_RULE_CRIT_PARAMS_B.DATA_TYPE%TYPE;
765 l_case_sensitive FUN_RULE_CRITERIA.CASE_SENSITIVE_FLAG%TYPE;
766 l_criteria_id FUN_RULE_CRITERIA.CRITERIA_ID%TYPE;
767
768 l_ParamMultiValueList multi_param_value;
769
770 l_return_result VARCHAR2(240);
771 l_isAnyActiveRule boolean := false;
772 l_count NUMBER := 1;
773 l_set_result_values boolean := false;
774
775 RuleResultTable fun_Rule_Results_Table := fun_Rule_Results_Table();
776 -- m_RuleResultTable RuleResultTableType;
777
778 l_old_moac_access_mode VARCHAR2(1);
779 l_old_org_id NUMBER;
780
781 BEGIN
782 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
783 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.APPLY_RULE Boolean:->p_application_short_name='||p_application_short_name||'**p_rule_object_name='||p_rule_object_name, FALSE);
784 end if;
785
786 /*Rule Object Instance MOAC Changes:
787 * If p_org_id is passed and the set_instance_context is called, then set the MOAC context based on
788 * following logic.
789 * If Product team has not called MO_GLOBAL.INIT then raise an exception.
790 * Else, get the access_mode. If access_mode is not S, then set to S and the passed p_og_id.
791 */
792 l_old_moac_access_mode := MO_GLOBAL.get_access_mode();
793 l_old_org_id := MO_GLOBAL.get_current_org_id();
794
795 --Does validation and then sets the policy context to S, if its not S and
796 --the passed org id value is not same as current org id value.
797
798 IF (m_org_id IS NOT NULL) THEN
799 FUN_RULE_MOAC_PKG.SET_MOAC_ACCESS_MODE(m_org_id);
800 END IF;
801
802 l_application_id := FUN_RULE_UTILITY_PKG.getApplicationID(p_application_short_name);
803
804 IF(l_application_id IS NULL) THEN
805 l_application_id := FND_GLOBAL.RESP_APPL_ID;
806 END IF;
807
808 destination_cursor := DBMS_SQL.OPEN_CURSOR;
809
810 --IF Instance Context is set, then append the where clause with an extra bind variable i.e
811 --AND INSTANCE_LABEL = :3
812
813 IF (m_instance_context = 'Y') THEN
814 g_sRuleDetailSql := g_sRuleDetailMOACSql;
815 ELSE
816 g_sRuleDetailSql := g_sRuleDetailSql_orig;
817 END IF;
818
819 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
820 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.APPLY_RULE Boolean:->g_sRuleDetailSql='||g_sRuleDetailSql, FALSE);
821 end if;
822
823 DBMS_SQL.PARSE(destination_cursor, g_sRuleDetailSql,DBMS_SQL.native);
824 dbms_sql.bind_variable(destination_cursor , '1' , p_rule_object_name);
825 dbms_sql.bind_variable(destination_cursor , '2' , l_application_id);
826
827 IF (m_instance_context = 'Y') THEN
828 dbms_sql.bind_variable(destination_cursor , '3' , m_instance_label);
829 dbms_sql.bind_variable(destination_cursor , '4' , m_instance_label);
830 dbms_sql.bind_variable(destination_cursor , '5' , m_instance_label);
831
832 dbms_sql.bind_variable(destination_cursor , '6' , m_org_id);
833 dbms_sql.bind_variable(destination_cursor , '7' , m_org_id);
834 dbms_sql.bind_variable(destination_cursor , '8' , m_org_id);
835
836 END IF;
837
838
839 dbms_sql.define_column(destination_cursor, 1, l_result_type , 30);
840 dbms_sql.define_column(destination_cursor, 2, l_rule_detail_id);
841 dbms_sql.define_column(destination_cursor, 3, l_operator , 3);
842 dbms_sql.define_column(destination_cursor, 4, l_rule_object_id);
843 dbms_sql.define_column(destination_cursor, 5, l_flexfield_name , 80);
844 dbms_sql.define_column(destination_cursor, 6, l_flexfield_app_short_name , 30);
845 dbms_sql.define_column(destination_cursor, 7, l_multiRuleResultFlag , 10);
846 dbms_sql.define_column(destination_cursor, 8, l_useDefaultValueFlag , 10);
847
848 l_num_rows_processed := DBMS_SQL.EXECUTE(destination_cursor);
849
850 while(dbms_sql.fetch_rows(destination_cursor) > 0 ) loop
851 l_isAnyActiveRule := true; --i.e Atleast one Rule is present.
852
853 dbms_sql.column_value(destination_cursor, 1, l_result_type);
854 dbms_sql.column_value(destination_cursor, 2, l_rule_detail_id );
855 dbms_sql.column_value(destination_cursor, 3, l_operator);
856 dbms_sql.column_value(destination_cursor, 4, l_rule_object_id);
857 dbms_sql.column_value(destination_cursor, 5, l_flexfield_name);
858 dbms_sql.column_value(destination_cursor, 6, l_flexfield_app_short_name);
859 dbms_sql.column_value(destination_cursor, 7, l_multiRuleResultFlag);
860 dbms_sql.column_value(destination_cursor, 8, l_useDefaultValueFlag);
861 m_multiRuleResultFlag := l_multiRuleResultFlag;
862 m_useDefaultValueFlag := l_useDefaultValueFlag;
863
864 IF (L_OPERATOR = C_OR) THEN
865 l_ret_val := false;
866 ELSE
867 l_ret_val := true;
868 END IF;
869
870 params_cursor := DBMS_SQL.OPEN_CURSOR;
871
872 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
873 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.APPLY_RULE Boolean:->g_sCriteriaParamSql='||g_sCriteriaParamSql, FALSE);
874 end if;
875
876 DBMS_SQL.PARSE(params_cursor, g_sCriteriaParamSql,DBMS_SQL.native);
877 dbms_sql.bind_variable(params_cursor , '1' , l_rule_detail_id);
878 dbms_sql.bind_variable(params_cursor , '2' , l_rule_detail_id);
879
880 dbms_sql.define_column(params_cursor, 1, l_param_name , 30);
881 dbms_sql.define_column(params_cursor, 2, l_condition , 15);
882 dbms_sql.define_column(params_cursor, 3, l_param_value , 1024);
883 dbms_sql.define_column(params_cursor, 4, l_data_type, 15);
884 dbms_sql.define_column(params_cursor, 5, l_case_sensitive, 1);
885 dbms_sql.define_column(params_cursor, 6, l_criteria_id);
886
887 params_rows_processed := DBMS_SQL.EXECUTE(params_cursor);
888
889 while(dbms_sql.fetch_rows(params_cursor) > 0 ) loop
890 dbms_sql.column_value(params_cursor, 1, l_param_name );
891 dbms_sql.column_value(params_cursor, 2, l_condition );
892 dbms_sql.column_value(params_cursor, 3, l_param_value);
893 dbms_sql.column_value(params_cursor, 4, l_data_type);
894 dbms_sql.column_value(params_cursor, 5, l_case_sensitive);
895 dbms_sql.column_value(params_cursor, 6, l_criteria_id);
896
897 if(l_param_name = 'APPLICATION') then
898 if(NOT containsKey(g_parameter_list,'APPLICATION')) then
899 add_parameter('APPLICATION' , FND_GLOBAL.RESP_APPL_ID);
900 end if;
901 end if;
902
903 if(l_param_name = 'RESPONSIBILITY') then
904 if(NOT containsKey(g_parameter_list,'RESPONSIBILITY')) then
905 add_parameter('RESPONSIBILITY' , FND_GLOBAL.RESP_ID);
906 end if;
907 end if;
908
909
910 /********************************************************************************
911 MOAC Handle:
912 1) When the product team calls applyRule(), and if the ORGANIZATION parameter is
913 explicitly passed, we should just use that value.
914 2) If it is not, then see if the access mode is single. If it is, use that org
915 as the parameter value.
916 3) If it is not, then any criteria which uses ORGANIZATION as the parameter
917 fails - always.
918 **********************************************************************************/
919
920 if(l_param_name = 'ORGANIZATION') then
921 if(NOT containsKey(g_parameter_list,'ORGANIZATION')) then
922 add_parameter('ORGANIZATION' , FUN_RULE_UTILITY_PKG.get_moac_org_id);
923 end if;
924 end if;
925
926 if(l_param_name = 'USER') then
927 if(NOT containsKey(g_parameter_list,'USER')) then
928 add_parameter('USER' , FND_GLOBAL.USER_ID);
929 end if;
930 end if;
931
932
933 getMultiValueParamsArray(l_ParamMultiValueList, l_rule_detail_id, l_criteria_id , l_data_type);
934
935 IF(l_operator = C_AND) THEN
936 l_ret_val := matchCriteria(l_param_name, l_condition, l_ParamMultiValueList,
937 l_data_type, g_parameter_list, l_case_sensitive) AND l_ret_val ;
938 ELSIF(l_operator = C_OR) THEN
939 l_ret_val := matchCriteria(l_param_name, l_condition, l_ParamMultiValueList,
940 l_data_type, g_parameter_list, l_case_sensitive) OR l_ret_val ;
941 END IF;
942 end loop;
943 DBMS_SQL.CLOSE_CURSOR(params_cursor);
944
945 l_all_rule_retval := l_all_rule_retval OR l_ret_val;
946
947 setResultValues(l_ret_val,
948 l_isAnyActiveRule,
949 l_rule_object_id,
950 l_result_type,
951 p_rule_object_name,
952 l_flexfield_name,
953 l_flexfield_app_short_name,
954 l_rule_detail_id);
955
956
957 if(l_multiRuleResultFlag = 'N') then
958 if (l_ret_val) then exit; end if;
959 else
960 --For AR's Multi Rule Result we populate RuleResultObject here.
961 if(l_ret_val) then
962 RuleResultTable.extend;
963 RuleResultTable(l_count) := populateRuleResultObjects;
964 l_count := l_count+1;
965 end if;
966 end if;
967
968 end loop;
969
970 DBMS_SQL.CLOSE_CURSOR(destination_cursor);
971
972 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
973 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.APPLY_RULE Boolean:->before setResultValues', FALSE);
974 end if;
975
976 if(NOT l_all_rule_retval) then --implies that not a single Rule is satisfied.
977 setResultValues(l_ret_val,
978 l_isAnyActiveRule,
979 l_rule_object_id,
980 l_result_type,
981 p_rule_object_name,
982 l_flexfield_name,
983 l_flexfield_app_short_name,
984 l_rule_detail_id);
985 else
986 /*In case of Multi Rule Result, we should not make m_noRulesSatisfied as false
987 * if at least one result is returned i.e bAllRuleRetVal is true.
988 * */
989 if(l_multiRuleResultFlag = 'Y') then
990 m_noRulesSatisfied := false;
991 end if;
992 end if;
993
994 --If no Rules are satisfied and use default flag is NOT Y
995 --then return false;
996 if((m_noRulesSatisfied OR (NOT l_isAnyActiveRule)) AND m_useDefaultValueFlag <> 'Y') then
997 /*Rule Object Instance MOAC Changes:
998 * Revert back the access mode and org id to the l_old_acess_mode and l_old_org_id
999 * And Clear The Instance Context if set.
1000 */
1001
1002 IF (m_org_id IS NOT NULL) THEN
1003 FUN_RULE_MOAC_PKG.SET_MOAC_POLICY_CONTEXT(l_old_moac_access_mode , l_old_org_id , m_org_id);
1004 END IF;
1005 clearInstanceContext;
1006 return false;
1007 end if;
1008
1009 if(l_multiRuleResultFlag = 'Y') then --For Multi Rule Result Object Type
1010 if(NOT l_all_rule_retval) then
1011 RuleResultTable.extend;
1012 RuleResultTable(l_count) := populateRuleResultObjects;
1013 end if;
1014 m_RuleResultTable := RuleResultTable;
1015 end if;
1016
1017 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1018 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'End FUN_RULE_PUB.APPLY_RULE Boolean', FALSE);
1019 end if;
1020
1021 /*Rule Object Instance MOAC Changes:
1022 * Revert back the access mode and org id to the l_old_acess_mode and l_old_org_id
1023 * And Clear The Instance Context if set.
1024 */
1025
1026 IF (m_org_id IS NOT NULL) THEN
1027 FUN_RULE_MOAC_PKG.SET_MOAC_POLICY_CONTEXT(l_old_moac_access_mode , l_old_org_id , m_org_id);
1028 END IF;
1029 clearInstanceContext;
1030 return true;
1031
1032 EXCEPTION
1033 WHEN NO_DATA_FOUND THEN
1034 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1035 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION , 'FUN_RULE_PUB.apply_rule BOOLEAN:->NO_DATA_FOUND', FALSE);
1036 END IF;
1037
1038 RAISE;
1039
1040 WHEN OTHERS THEN
1041 IF DBMS_SQL.IS_OPEN(destination_cursor) THEN
1042 DBMS_SQL.CLOSE_CURSOR(destination_cursor);
1043 END IF;
1044 IF DBMS_SQL.IS_OPEN(params_cursor) THEN
1045 DBMS_SQL.CLOSE_CURSOR(params_cursor);
1046 END IF;
1047 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1048 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION , 'FUN_RULE_PUB.APLY_RULE Boolean:->'||SQLERRM, FALSE);
1049 END IF;
1050
1051 RAISE;
1052 END apply_rule;
1053
1054 FUNCTION apply_rule_wrapper(p_application_short_name IN VARCHAR2, p_rule_object_name IN VARCHAR2)
1055 RETURN NUMBER
1056 IS
1057 BEGIN
1058 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1059 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.apply_rule_wrapper', FALSE);
1060 end if;
1061
1062 IF (apply_rule(p_application_short_name, p_rule_object_name)) THEN
1063 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1064 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'FUN_RULE_PUB.apply_rule_wrapper-> SuccessFul Rule Evaluation', FALSE);
1065 end if;
1066
1067 RETURN 1;
1068 ELSE
1069 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1070 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'FUN_RULE_PUB.apply_rule_wrapper-> Failed Rule Evaluation', FALSE);
1071 end if;
1072
1073 RETURN 0;
1074 END IF;
1075 END apply_rule_wrapper;
1076
1077
1078 FUNCTION populateRuleResultObjects RETURN FUN_RULE_RESULT IS
1079 l_RuleResultObject FUN_RULE_RESULT;
1080 BEGIN
1081 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1082 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.populateRuleResultObjects', FALSE);
1083 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.populateRuleResultObjects:->m_resultValueDataType='||m_resultValueDataType, FALSE);
1084 end if;
1085
1086 -- l_RuleResultObject := RuleResultObject(m_ruleDetailId);
1087 IF(m_resultValueDataType = C_NUMERIC) THEN
1088 l_RuleResultObject := FUN_RULE_RESULT( get_rule_detail_id,
1089 get_string,
1090 get_number,
1091 null,
1092 get_result_application_id,
1093 get_rule_name,
1094 get_attribute_category,
1095 get_attribute1,
1096 get_attribute2,
1097 get_attribute3,
1098 get_attribute4,
1099 get_attribute5,
1100 get_attribute6,
1101 get_attribute7,
1102 get_attribute8,
1103 get_attribute9,
1104 get_attribute10,
1105 get_attribute11,
1106 get_attribute12,
1107 get_attribute13,
1108 get_attribute14,
1109 get_attribute15,
1110 get_message_app_name);
1111
1112 ELSIF(m_resultValueDataType = C_DATE) THEN
1113 l_RuleResultObject := FUN_RULE_RESULT( get_rule_detail_id,
1114 get_string,
1115 null,
1116 get_date,
1117 get_result_application_id,
1118 get_rule_name,
1119 get_attribute_category,
1120 get_attribute1,
1121 get_attribute2,
1122 get_attribute3,
1123 get_attribute4,
1124 get_attribute5,
1125 get_attribute6,
1126 get_attribute7,
1127 get_attribute8,
1128 get_attribute9,
1129 get_attribute10,
1130 get_attribute11,
1131 get_attribute12,
1132 get_attribute13,
1133 get_attribute14,
1134 get_attribute15,
1135 get_message_app_name);
1136
1137 ELSE
1138 l_RuleResultObject := FUN_RULE_RESULT( get_rule_detail_id,
1139 get_string,
1140 null,
1141 null,
1142 get_result_application_id,
1143 get_rule_name,
1144 get_attribute_category,
1145 get_attribute1,
1146 get_attribute2,
1147 get_attribute3,
1148 get_attribute4,
1149 get_attribute5,
1150 get_attribute6,
1151 get_attribute7,
1152 get_attribute8,
1153 get_attribute9,
1154 get_attribute10,
1155 get_attribute11,
1156 get_attribute12,
1157 get_attribute13,
1158 get_attribute14,
1159 get_attribute15,
1160 get_message_app_name);
1161
1162 END IF;
1163
1164 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1165 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'End FUN_RULE_PUB.populateRuleResultObjects', FALSE);
1166 end if;
1167
1168 return l_RuleResultObject;
1169 END populateRuleResultObjects;
1170
1171 PROCEDURE setResultValues(p_ret_val IN BOOLEAN,
1172 p_isAnyActiveRule IN BOOLEAN,
1173 p_rule_object_id IN NUMBER,
1174 p_result_type IN VARCHAR2,
1175 p_rule_object_name IN VARCHAR2,
1176 p_flexfield_name IN VARCHAR2,
1177 p_flexfield_app_short_name IN VARCHAR2,
1178 p_rule_detail_id IN NUMBER)
1179 IS
1180 l_ret_val BOOLEAN := p_ret_val;
1181 l_isAnyActiveRule BOOLEAN := p_isAnyActiveRule;
1182 l_rule_object_id NUMBER := p_rule_object_id;
1183 l_result_type VARCHAR2(80):= p_result_type;
1184 l_rule_object_name VARCHAR2(80):= p_rule_object_name;
1185 l_flexfield_name VARCHAR2(80):= p_flexfield_name;
1186 l_flexfield_app_short_name VARCHAR2(80):= p_flexfield_app_short_name;
1187 l_rule_detail_id NUMBER := P_rule_detail_id;
1188 l_return_result VARCHAR2(240);
1189
1190 BEGIN
1191 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1192 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.setResultValues', FALSE);
1193 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.setResultValues:->p_rule_object_id='||to_char(p_rule_object_id), FALSE);
1194 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.setResultValues:->p_result_type='||p_result_type, FALSE);
1195 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.setResultValues:->p_rule_object_name='||p_rule_object_name, FALSE);
1196 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.setResultValues:->p_result_type='||p_result_type, FALSE);
1197 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.setResultValues:->p_flexfield_name='||p_flexfield_name, FALSE);
1198 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.setResultValues:->p_flexfield_app_short_name='||p_flexfield_app_short_name, FALSE);
1199 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.setResultValues:->p_rule_detail_id='||to_char(p_rule_detail_id), FALSE);
1200 end if;
1201
1202 /*getDFFResultValue returns Result having result value as
1203 *Attribute Category.
1204 *Similairy getDFFDefaultValue returns result having result value as
1205 *Attribute Category from DFF table having rule_detail_id as -99.
1206 */
1207
1208 m_resultValueDataType := getResultValueDataType(p_result_type , p_rule_object_id);
1209
1210 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1211 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.setResultValues:->m_resultValueDataType='||m_resultValueDataType, FALSE);
1212 end if;
1213
1214 --If No Active Rules Are present, then Default Value must be passed.
1215 if(NOT l_isAnyActiveRule) then
1216
1217 l_ret_val := false;
1218 getRuleObjectInfo(p_rule_object_name);
1219 l_result_type := m_ruleObjectType;
1220 l_rule_object_id := m_ruleObjectId;
1221 l_flexfield_name := m_flexFieldName;
1222 l_flexfield_app_short_name := m_flexFieldAppShortName;
1223 end if;
1224
1225 if(l_ret_val) then
1226 if(l_result_type = C_MULTIVALUE ) then
1227
1228 l_return_result := getDFFResultValue(l_rule_detail_id ,
1229 l_flexfield_name,
1230 l_flexfield_app_short_name);
1231
1232 else
1233
1234 l_return_result := getResultValue(l_rule_detail_id,
1235 l_result_type);
1236
1237 end if;
1238 else
1239
1240 m_noRulesSatisfied := TRUE;
1241
1242 if(l_result_type = C_MULTIVALUE) then
1243 l_return_result := getDFFDefaultValue(l_rule_detail_id ,
1244 l_flexfield_name,
1245 l_flexfield_app_short_name);
1246
1247 else
1248 l_return_result := getDefaultValue(l_rule_object_id,
1249 l_result_type);
1250 end if;
1251
1252 end if;
1253
1254 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1255 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'End FUN_RULE_PUB.setResultValues', FALSE);
1256 end if;
1257
1258 END setResultValues;
1259
1260
1261 FUNCTION checkIfMultiValueResultIsNull RETURN BOOLEAN IS
1262 l_bMultiValueResultNull BOOLEAN := false;
1263 BEGIN
1264 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1265 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.checkIfMultiValueResultIsNull', FALSE);
1266 end if;
1267
1268 for i in 1..15 loop
1269 l_bMultiValueResultNull := (l_bMultiValueResultNull OR
1270 ( get_attribute_at_index(i) is null OR
1271 get_attribute_at_index(i) = '')
1272 );
1273 end loop;
1274 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1275 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'End FUN_RULE_PUB.checkIfMultiValueResultIsNull', FALSE);
1276 end if;
1277
1278 return l_bMultiValueResultNull;
1279 END checkIfMultiValueResultIsNull;
1280
1281 FUNCTION get_attribute_at_index(p_Index IN NUMBER) RETURN VARCHAR2 IS
1282 BEGIN
1283 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1284 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.get_attribute_at_index:->p_Index='||to_char(p_Index), FALSE);
1285 end if;
1286
1287 if (p_Index =1 ) then return get_attribute1;
1288 elsif (p_Index =2 ) then return get_attribute2;
1289 elsif (p_Index =3 ) then return get_attribute3;
1290 elsif (p_Index =4 ) then return get_attribute4;
1291 elsif (p_Index =5 ) then return get_attribute5;
1292 elsif (p_Index =6 ) then return get_attribute6;
1293 elsif (p_Index =7 ) then return get_attribute7;
1294 elsif (p_Index =8 ) then return get_attribute8;
1295 elsif (p_Index =9 ) then return get_attribute9;
1296 elsif (p_Index =10 ) then return get_attribute10;
1297 elsif (p_Index =11 ) then return get_attribute11;
1298 elsif (p_Index =12 ) then return get_attribute12;
1299 elsif (p_Index =13 ) then return get_attribute13;
1300 elsif (p_Index =14 ) then return get_attribute14;
1301 elsif (p_Index =15 ) then return get_attribute15;
1302 else return NULL;
1303 end if;
1304
1305 END get_attribute_at_index;
1306
1307 FUNCTION getResultValueDataType(p_ObjectType IN VARCHAR2 , p_RuleObjectId IN NUMBER) RETURN VARCHAR2 IS
1308 params_cursor INTEGER;
1309 params_rows_processed INTEGER;
1310
1311 l_ValueSetType VARCHAR2(10);
1312
1313 BEGIN
1314 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1315 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.getResultValueDataType:->p_ObjectType'||p_ObjectType||'**p_RuleObjectId='||to_char(p_RuleObjectId), FALSE);
1316 end if;
1317
1318 if(p_ObjectType IS null OR p_ObjectType = '') then
1319 return C_STRINGS;
1320
1321 elsif(C_MESSAGE = p_ObjectType OR C_RICHTEXT = p_ObjectType OR C_MULTIVALUE = p_ObjectType) then
1322 return C_STRINGS;
1323
1324 elsif (C_VALUESET = p_ObjectType) then
1325
1326 params_cursor := DBMS_SQL.OPEN_CURSOR;
1327 DBMS_SQL.PARSE(params_cursor, g_sValueSetSql,DBMS_SQL.native);
1328 dbms_sql.bind_variable(params_cursor , '1' , p_RuleObjectId);
1329
1330 dbms_sql.define_column(params_cursor, 1, l_ValueSetType , 10);
1331
1332 params_rows_processed := DBMS_SQL.EXECUTE(params_cursor);
1333
1334 if(dbms_sql.fetch_rows(params_cursor) > 0 ) then
1335 dbms_sql.column_value(params_cursor, 1, l_ValueSetType );
1336 end if;
1337 end if;
1338
1339 DBMS_SQL.CLOSE_CURSOR(params_cursor);
1340
1341 if( l_ValueSetType = 'C' ) then return C_STRINGS;
1342 elsif ( l_ValueSetType = 'N' ) then return C_NUMERIC;
1343 elsif ( l_ValueSetType = 'D' OR l_ValueSetType = 'T' OR l_ValueSetType = 't' OR l_ValueSetType = 'X') then return C_DATE;
1344 else return C_STRINGS;
1345 end if;
1346
1347 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1348 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'End FUN_RULE_PUB.getResultValueDataType', FALSE);
1349 end if;
1350
1351 return C_STRINGS;
1352 EXCEPTION
1353
1354 WHEN NO_DATA_FOUND THEN
1355 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1356 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION , 'FUN_RULE_PUB.getResultValueDataType:->NO_DATA_FOUND', FALSE);
1357 END IF;
1358
1359 RAISE;
1360
1361 WHEN OTHERS THEN
1362 IF DBMS_SQL.IS_OPEN(params_cursor) THEN
1363 DBMS_SQL.CLOSE_CURSOR(params_cursor);
1364 END IF;
1365 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1366 fnd_log.message(FND_LOG.LEVEL_EXCEPTION, 'FUN_RULE_PUB.getResultValueDataType:->g_sValueSetSql='||g_sValueSetSql, FALSE);
1367 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION , 'FUN_RULE_PUB.getResultValueDataType:->'||SQLERRM, FALSE);
1368 END IF;
1369
1370 RAISE;
1371 END getResultValueDataType;
1372
1373
1374 PROCEDURE getMultiValueParamsArray(p_multi_param_value OUT NOCOPY multi_param_value,
1375 p_rule_detail_id IN NUMBER,
1376 p_rule_criteria_id IN NUMBER,
1377 p_data_type IN VARCHAR2) IS
1378
1379 params_cursor INTEGER;
1380 params_rows_processed INTEGER;
1381
1382 l_param_value FUN_RULE_PARAM_VALUES.PARAM_VALUE%TYPE;
1383 l_counter NUMBER := 1;
1384
1385 BEGIN
1386 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1387 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.getMultiValueParamsArray', FALSE);
1388 end if;
1389
1390 params_cursor := DBMS_SQL.OPEN_CURSOR;
1391 DBMS_SQL.PARSE(params_cursor, g_sMultiCriteriaParamValueSql,DBMS_SQL.native);
1392 dbms_sql.bind_variable(params_cursor , '1' , p_rule_detail_id);
1393 dbms_sql.bind_variable(params_cursor , '2' , p_rule_criteria_id);
1394
1395 dbms_sql.define_column(params_cursor, 1, l_param_value , 1024);
1396
1397 params_rows_processed := DBMS_SQL.EXECUTE(params_cursor);
1398
1399 while(dbms_sql.fetch_rows(params_cursor) > 0 ) loop
1400 dbms_sql.column_value(params_cursor, 1, l_param_value );
1401 p_multi_param_value(l_counter) := l_param_value;
1402 l_counter := l_counter + 1;
1403 end loop;
1404
1405 DBMS_SQL.CLOSE_CURSOR(params_cursor);
1406
1407 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1408 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'End FUN_RULE_PUB.getMultiValueParamsArray', FALSE);
1409 end if;
1410
1411 EXCEPTION
1412
1413 WHEN NO_DATA_FOUND THEN
1414 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1415 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION , 'FUN_RULE_PUB.getMultiValueParamsArray:->NO_DATA_FOUND', FALSE);
1416 END IF;
1417
1418 RAISE;
1419
1420 WHEN OTHERS THEN
1421 IF DBMS_SQL.IS_OPEN(params_cursor) THEN
1422 DBMS_SQL.CLOSE_CURSOR(params_cursor);
1423 END IF;
1424 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1425 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION , 'FUN_RULE_PUB.getMultiValueParamsArray:->g_sMultiCriteriaParamValueSql='||g_sMultiCriteriaParamValueSql, FALSE);
1426 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION , 'FUN_RULE_PUB.getMultiValueParamsArray:->'||SQLERRM, FALSE);
1427 END IF;
1428 RAISE;
1429
1430 END getMultiValueParamsArray;
1431
1432 FUNCTION matchCriteria(p_ParamName IN VARCHAR2, p_Condition VARCHAR2,
1433 multiValueParamsArray IN multi_param_value, p_DataType IN VARCHAR2,
1434 critObjectValue IN t_parameter_list, p_CaseSensitive IN VARCHAR2) RETURN BOOLEAN IS
1435
1436 l_ret_val boolean;
1437 l_valueToBeCompared VARCHAR2(240);
1438 BEGIN
1439 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1440 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'End FUN_RULE_PUB.matchCriteria', FALSE);
1441 end if;
1442
1443 /*If the condition is IN and NOT_IN that means there exists multiple values.
1444 * So the retVal must be OR'ed instead of AND'ed
1445 * */
1446
1447 IF (p_Condition = C_NOT_IN OR p_Condition = C_IN ) THEN
1448 l_ret_val := FALSE;
1449 ELSE
1450 l_ret_val := TRUE;
1451 END IF;
1452
1453 if(ContainsKey(critObjectValue , p_ParamName)) THEN
1454 l_valueToBeCompared := get(critObjectValue, p_ParamName);
1455 --MOAC Handling.
1456 IF (p_ParamName = 'ORGANIZATION' AND l_valueToBeCompared = C_INVALID_ORG) THEN
1457 RETURN FALSE;
1458 END IF;
1459
1460 FOR i in 1..multiValueParamsArray.count LOOP
1461 if(p_Condition = C_NOT_IN OR p_Condition = C_IN) then
1462 l_ret_val := isMatched(p_ParamName,p_DataType,p_Condition,multiValueParamsArray(i),l_ValueToBeCompared,p_CaseSensitive) OR l_ret_val ;
1463 else
1464 l_ret_val := isMatched(p_ParamName,p_DataType,p_Condition,multiValueParamsArray(i),l_ValueToBeCompared,p_CaseSensitive) AND l_ret_val ;
1465 end if;
1466
1467 END LOOP;
1468 return l_ret_val;
1469 end if;
1470 return false;
1471
1472 END matchCriteria;
1473
1474 FUNCTION getDFFResultValue(p_RuleDetailId IN NUMBER,p_FlexFieldName IN VARCHAR2,
1475 p_FlexFieldAppShortName IN VARCHAR2) RETURN VARCHAR2 IS
1476
1477 CURSOR DFF_CUR(p_FlexFieldName IN VARCHAR2, p_FlexFieldAppShortName IN VARCHAR2) IS
1478 SELECT DISTINCT FDF.APPLICATION_TABLE_NAME
1479 FROM FND_DESCRIPTIVE_FLEXS FDF
1480 WHERE FDF.DESCRIPTIVE_FLEXFIELD_NAME = p_FlexFieldName
1481 AND APPLICATION_ID IN (SELECT APPLICATION_ID FROM FND_APPLICATION_VL WHERE APPLICATION_SHORT_NAME = p_FlexFieldAppShortName);
1482
1483 source_cursor INTEGER;
1484 l_num_rows_processed INTEGER;
1485
1486 l_RuleDetailId NUMBER;
1487 concatentated_ids VARCHAR2(250);
1488
1489
1490 BEGIN
1491
1492 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1493 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.getDFFResultValue:->p_FlexFieldName='||p_FlexFieldName||'***p_FlexFieldAppShortName='||p_FlexFieldAppShortName, FALSE);
1494 end if;
1495
1496 for i in DFF_CUR(p_FlexFieldName, p_FlexFieldAppShortName) loop
1497 sDFFTableName := i.application_table_name;
1498 end loop;
1499
1500 -- Prepare a cursor to select from the source table:
1501 source_cursor := dbms_sql.open_cursor;
1502
1503 /*If the Rule Object is an instance, then query the DFF table with RULE_OBJECT_ID as well.*/
1504
1505 if(m_instance_context = 'Y') then
1506 DBMS_SQL.PARSE(source_cursor,
1507 'SELECT DFF.RULE_DETAIL_ID, ATTRIBUTE_CATEGORY, ATTRIBUTE1, ATTRIBUTE2,
1508 ATTRIBUTE3, ATTRIBUTE4, ATTRIBUTE5, ATTRIBUTE6, ATTRIBUTE7,
1509 ATTRIBUTE8, ATTRIBUTE9, ATTRIBUTE10, ATTRIBUTE11, ATTRIBUTE12,
1510 ATTRIBUTE13, ATTRIBUTE14, ATTRIBUTE15 , RULE_NAME , RESULT_APPLICATION_ID
1511 FROM '|| sDFFTableName ||' DFF , FUN_RULE_DETAILS FRD
1512 WHERE DFF.RULE_DETAIL_ID = :1 AND DFF.RULE_OBJECT_ID = :2 AND DFF.RULE_DETAIL_ID = FRD.RULE_DETAIL_ID'
1513 , DBMS_SQL.native);
1514 else
1515 DBMS_SQL.PARSE(source_cursor,
1516 'SELECT DFF.RULE_DETAIL_ID, ATTRIBUTE_CATEGORY, ATTRIBUTE1, ATTRIBUTE2,
1517 ATTRIBUTE3, ATTRIBUTE4, ATTRIBUTE5, ATTRIBUTE6, ATTRIBUTE7,
1518 ATTRIBUTE8, ATTRIBUTE9, ATTRIBUTE10, ATTRIBUTE11, ATTRIBUTE12,
1519 ATTRIBUTE13, ATTRIBUTE14, ATTRIBUTE15 , RULE_NAME , RESULT_APPLICATION_ID
1520 FROM '|| sDFFTableName ||' DFF , FUN_RULE_DETAILS FRD
1521 WHERE DFF.RULE_DETAIL_ID = :1 AND DFF.RULE_DETAIL_ID = FRD.RULE_DETAIL_ID'
1522 , DBMS_SQL.native);
1523 end if;
1524
1525 dbms_sql.bind_variable(source_cursor , '1' , p_RuleDetailId);
1526
1527 if(m_instance_context = 'Y') then
1528 dbms_sql.bind_variable(source_cursor , '2' , m_ruleObjectId);
1529 end if;
1530
1531 DBMS_SQL.DEFINE_COLUMN(source_cursor, 1, l_RuleDetailId); DBMS_SQL.DEFINE_COLUMN(source_cursor, 2, m_attributeCategory, 150);
1532 DBMS_SQL.DEFINE_COLUMN(source_cursor, 3, m_attribute1 , 150); DBMS_SQL.DEFINE_COLUMN(source_cursor, 4, m_attribute2 , 150);
1533 DBMS_SQL.DEFINE_COLUMN(source_cursor, 5, m_attribute3 , 150); DBMS_SQL.DEFINE_COLUMN(source_cursor, 6, m_attribute4 , 150);
1534 DBMS_SQL.DEFINE_COLUMN(source_cursor, 7, m_attribute5 , 150); DBMS_SQL.DEFINE_COLUMN(source_cursor, 8, m_attribute6 , 150);
1535 DBMS_SQL.DEFINE_COLUMN(source_cursor, 9, m_attribute7 , 150); DBMS_SQL.DEFINE_COLUMN(source_cursor, 10, m_attribute8 , 150);
1536 DBMS_SQL.DEFINE_COLUMN(source_cursor, 11, m_attribute9 , 150); DBMS_SQL.DEFINE_COLUMN(source_cursor, 12, m_attribute10 , 150);
1537 DBMS_SQL.DEFINE_COLUMN(source_cursor, 13, m_attribute11 , 150); DBMS_SQL.DEFINE_COLUMN(source_cursor, 14, m_attribute12 , 150);
1538 DBMS_SQL.DEFINE_COLUMN(source_cursor, 15, m_attribute13 , 150); DBMS_SQL.DEFINE_COLUMN(source_cursor, 16, m_attribute14 , 150);
1539 DBMS_SQL.DEFINE_COLUMN(source_cursor, 17, m_attribute15 , 150);
1540 DBMS_SQL.DEFINE_COLUMN(source_cursor, 18, m_ruleName , 80);
1541 DBMS_SQL.DEFINE_COLUMN(source_cursor, 19, m_resultApplicationId);
1542
1543
1544 l_num_rows_processed := DBMS_SQL.EXECUTE(source_cursor);
1545
1546 while(dbms_sql.fetch_rows(source_cursor) > 0 ) loop
1547 DBMS_SQL.column_value(source_cursor, 1, l_RuleDetailId); DBMS_SQL.column_value(source_cursor, 2, m_attributeCategory);
1548 DBMS_SQL.column_value(source_cursor, 3, m_attribute1); DBMS_SQL.column_value(source_cursor, 4, m_attribute2);
1549 DBMS_SQL.column_value(source_cursor, 5, m_attribute3); DBMS_SQL.column_value(source_cursor, 6, m_attribute4);
1550 DBMS_SQL.column_value(source_cursor, 7, m_attribute5); DBMS_SQL.column_value(source_cursor, 8, m_attribute6);
1551 DBMS_SQL.column_value(source_cursor, 9, m_attribute7); DBMS_SQL.column_value(source_cursor, 10, m_attribute8);
1552 DBMS_SQL.column_value(source_cursor, 11, m_attribute9); DBMS_SQL.column_value(source_cursor, 12, m_attribute10);
1553 DBMS_SQL.column_value(source_cursor, 13, m_attribute11); DBMS_SQL.column_value(source_cursor, 14, m_attribute12);
1554 DBMS_SQL.column_value(source_cursor, 15, m_attribute13); DBMS_SQL.column_value(source_cursor, 16, m_attribute14);
1555 DBMS_SQL.column_value(source_cursor, 17, m_attribute15);
1556 DBMS_SQL.column_value(source_cursor, 18, m_ruleName);
1557 DBMS_SQL.column_value(source_cursor, 19, m_resultApplicationId);
1558
1559 m_ruleDetailId := l_RuleDetailId;
1560 -- m_resultValue := m_attributeCategory;
1561 fnd_flex_descval.set_column_value('ATTRIBUTE_CATEGORY', m_attributeCategory);
1562 fnd_flex_descval.set_column_value('ATTRIBUTE1', m_attribute1);
1563 fnd_flex_descval.set_column_value('ATTRIBUTE2', m_attribute2);
1564 fnd_flex_descval.set_column_value('ATTRIBUTE3', m_attribute3);
1565 fnd_flex_descval.set_column_value('ATTRIBUTE4', m_attribute4);
1566 fnd_flex_descval.set_column_value('ATTRIBUTE5', m_attribute5);
1567 fnd_flex_descval.set_column_value('ATTRIBUTE6', m_attribute6);
1568 fnd_flex_descval.set_column_value('ATTRIBUTE7', m_attribute7);
1569 fnd_flex_descval.set_column_value('ATTRIBUTE8', m_attribute8);
1570 fnd_flex_descval.set_column_value('ATTRIBUTE8', m_attribute9);
1571 fnd_flex_descval.set_column_value('ATTRIBUTE10', m_attribute10);
1572 fnd_flex_descval.set_column_value('ATTRIBUTE11', m_attribute11);
1573 fnd_flex_descval.set_column_value('ATTRIBUTE12', m_attribute12);
1574 fnd_flex_descval.set_column_value('ATTRIBUTE13', m_attribute13);
1575 fnd_flex_descval.set_column_value('ATTRIBUTE14', m_attribute14);
1576 fnd_flex_descval.set_column_value('ATTRIBUTE15', m_attribute15);
1577
1578
1579 IF FND_FLEX_DESCVAL.validate_desccols(p_FlexFieldAppShortName, p_FlexFieldName) THEN
1580 IF (NVL(LENGTH(FND_FLEX_DESCVAL.concatenated_ids),0) > 1) THEN
1581 m_resultValue := FND_FLEX_DESCVAL.concatenated_ids;
1582 ELSE
1583 m_resultValue := FND_FLEX_DESCVAL.concatenated_values;
1584 END IF;
1585 END IF;
1586
1587 end loop;
1588
1589 DBMS_SQL.CLOSE_CURSOR(source_cursor);
1590
1591 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1592 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'End FUN_RULE_PUB.getDFFResultValue', FALSE);
1593 end if;
1594
1595 return m_attributeCategory;
1596
1597 EXCEPTION
1598
1599 WHEN NO_DATA_FOUND THEN
1600 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1601 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION , 'FUN_RULE_PUB.getDFFResultValue:->NO_DATA_FOUND', FALSE);
1602 END IF;
1603
1604 RAISE;
1605
1606 WHEN OTHERS THEN
1607 IF DBMS_SQL.IS_OPEN(source_cursor) THEN
1608 DBMS_SQL.CLOSE_CURSOR(source_cursor);
1609 END IF;
1610 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1611 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION , 'FUN_RULE_PUB.getDFFResultValue:->'||SQLERRM, FALSE);
1612 END IF;
1613
1614 RAISE;
1615
1616 END getDFFResultValue;
1617
1618
1619 FUNCTION getDFFDefaultValue(p_RuleDetailId IN NUMBER,
1620 p_FlexFieldName IN VARCHAR2,
1621 p_FlexFieldAppShortName IN VARCHAR2) RETURN VARCHAR2 IS
1622
1623 CURSOR DFF_CUR(p_FlexFieldName IN VARCHAR2, p_FlexFieldAppShortName IN VARCHAR2) IS
1624 SELECT DISTINCT FDF.APPLICATION_TABLE_NAME
1625 FROM FND_DESCRIPTIVE_FLEXS FDF
1626 WHERE FDF.DESCRIPTIVE_FLEXFIELD_NAME = p_FlexFieldName
1627 AND APPLICATION_ID IN (SELECT APPLICATION_ID FROM FND_APPLICATION_VL WHERE APPLICATION_SHORT_NAME = p_FlexFieldAppShortName);
1628
1629 l_DFFTableName VARCHAR2(30);
1630
1631 source_cursor INTEGER;
1632 l_num_rows_processed INTEGER;
1633
1634
1635 params_cursor INTEGER;
1636 params_ignore INTEGER;
1637 l_RuleDetailId NUMBER;
1638 concatentated_ids VARCHAR2(250);
1639
1640
1641 BEGIN
1642
1643 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1644 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.getDFFDefaultValue:->p_FlexFieldName='||p_FlexFieldName||'***p_FlexFieldAppShortName='||p_FlexFieldAppShortName, FALSE);
1645 end if;
1646
1647 for i in DFF_CUR(p_FlexFieldName, p_FlexFieldAppShortName) loop
1648 sDFFTableName := i.application_table_name;
1649 end loop;
1650
1651 -- Prepare a cursor to select from the source table:
1652 source_cursor := dbms_sql.open_cursor;
1653
1654 /*If the Rule Object is an instance, then query the DFF table with RULE_OBJECT_ID as well.*/
1655
1656 if(m_instance_context = 'Y') then
1657 DBMS_SQL.PARSE(source_cursor,
1658 'SELECT DFF.RULE_DETAIL_ID, ATTRIBUTE_CATEGORY, ATTRIBUTE1, ATTRIBUTE2,
1659 ATTRIBUTE3, ATTRIBUTE4, ATTRIBUTE5, ATTRIBUTE6, ATTRIBUTE7,
1660 ATTRIBUTE8, ATTRIBUTE9, ATTRIBUTE10, ATTRIBUTE11, ATTRIBUTE12,
1661 ATTRIBUTE13, ATTRIBUTE14, ATTRIBUTE15
1662 FROM '|| sDFFTableName ||' DFF
1663 WHERE DFF.RULE_DETAIL_ID = -99 AND RULE_OBJECT_ID = :1'
1664 , DBMS_SQL.native);
1665 else
1666 DBMS_SQL.PARSE(source_cursor,
1667 'SELECT DFF.RULE_DETAIL_ID, ATTRIBUTE_CATEGORY, ATTRIBUTE1, ATTRIBUTE2,
1668 ATTRIBUTE3, ATTRIBUTE4, ATTRIBUTE5, ATTRIBUTE6, ATTRIBUTE7,
1669 ATTRIBUTE8, ATTRIBUTE9, ATTRIBUTE10, ATTRIBUTE11, ATTRIBUTE12,
1670 ATTRIBUTE13, ATTRIBUTE14, ATTRIBUTE15
1671 FROM '|| sDFFTableName ||' DFF
1672 WHERE DFF.RULE_DETAIL_ID = -99 '
1673 , DBMS_SQL.native);
1674
1675 end if;
1676
1677 if(m_instance_context = 'Y') then
1678 dbms_sql.bind_variable(source_cursor , '1' , m_ruleObjectId);
1679 end if;
1680
1681
1682 DBMS_SQL.DEFINE_COLUMN(source_cursor, 1, l_RuleDetailId); DBMS_SQL.DEFINE_COLUMN(source_cursor, 2, m_attributeCategory, 150);
1683 DBMS_SQL.DEFINE_COLUMN(source_cursor, 3, m_attribute1 , 150); DBMS_SQL.DEFINE_COLUMN(source_cursor, 4, m_attribute2 , 150);
1684 DBMS_SQL.DEFINE_COLUMN(source_cursor, 5, m_attribute3 , 150); DBMS_SQL.DEFINE_COLUMN(source_cursor, 6, m_attribute4 , 150);
1685 DBMS_SQL.DEFINE_COLUMN(source_cursor, 7, m_attribute5 , 150); DBMS_SQL.DEFINE_COLUMN(source_cursor, 8, m_attribute6 , 150);
1686 DBMS_SQL.DEFINE_COLUMN(source_cursor, 9, m_attribute7 , 150); DBMS_SQL.DEFINE_COLUMN(source_cursor, 10, m_attribute8 , 150);
1687 DBMS_SQL.DEFINE_COLUMN(source_cursor, 11, m_attribute9 , 150); DBMS_SQL.DEFINE_COLUMN(source_cursor, 12, m_attribute10 , 150);
1688 DBMS_SQL.DEFINE_COLUMN(source_cursor, 13, m_attribute11 , 150); DBMS_SQL.DEFINE_COLUMN(source_cursor, 14, m_attribute12 , 150);
1689 DBMS_SQL.DEFINE_COLUMN(source_cursor, 15, m_attribute13 , 150); DBMS_SQL.DEFINE_COLUMN(source_cursor, 16, m_attribute14 , 150);
1690 DBMS_SQL.DEFINE_COLUMN(source_cursor, 17, m_attribute15 , 150);
1691
1692 l_num_rows_processed := DBMS_SQL.EXECUTE(source_cursor);
1693
1694 while(dbms_sql.fetch_rows(source_cursor) > 0 ) loop
1695 DBMS_SQL.column_value(source_cursor, 1, l_RuleDetailId); DBMS_SQL.column_value(source_cursor, 2, m_attributeCategory);
1696 DBMS_SQL.column_value(source_cursor, 3, m_attribute1); DBMS_SQL.column_value(source_cursor, 4, m_attribute2);
1697 DBMS_SQL.column_value(source_cursor, 5, m_attribute3); DBMS_SQL.column_value(source_cursor, 6, m_attribute4);
1698 DBMS_SQL.column_value(source_cursor, 7, m_attribute5); DBMS_SQL.column_value(source_cursor, 8, m_attribute6);
1699 DBMS_SQL.column_value(source_cursor, 9, m_attribute7); DBMS_SQL.column_value(source_cursor, 10, m_attribute8);
1700 DBMS_SQL.column_value(source_cursor, 11, m_attribute9); DBMS_SQL.column_value(source_cursor, 12, m_attribute10);
1701 DBMS_SQL.column_value(source_cursor, 13, m_attribute11); DBMS_SQL.column_value(source_cursor, 14, m_attribute12);
1702 DBMS_SQL.column_value(source_cursor, 15, m_attribute13); DBMS_SQL.column_value(source_cursor, 16, m_attribute14);
1703 DBMS_SQL.column_value(source_cursor, 17, m_attribute15);
1704
1705 m_ruleDetailId := -99;
1706 m_ruleName := 'Default Result';
1707 -- m_resultValue := m_attributeCategory;
1708 fnd_flex_descval.set_column_value('ATTRIBUTE_CATEGORY', m_attributeCategory);
1709 fnd_flex_descval.set_column_value('ATTRIBUTE1', m_attribute1);
1710 fnd_flex_descval.set_column_value('ATTRIBUTE2', m_attribute2);
1711 fnd_flex_descval.set_column_value('ATTRIBUTE3', m_attribute3);
1712 fnd_flex_descval.set_column_value('ATTRIBUTE4', m_attribute4);
1713 fnd_flex_descval.set_column_value('ATTRIBUTE5', m_attribute5);
1714 fnd_flex_descval.set_column_value('ATTRIBUTE6', m_attribute6);
1715 fnd_flex_descval.set_column_value('ATTRIBUTE7', m_attribute7);
1716 fnd_flex_descval.set_column_value('ATTRIBUTE8', m_attribute8);
1717 fnd_flex_descval.set_column_value('ATTRIBUTE8', m_attribute9);
1718 fnd_flex_descval.set_column_value('ATTRIBUTE10', m_attribute10);
1719 fnd_flex_descval.set_column_value('ATTRIBUTE11', m_attribute11);
1720 fnd_flex_descval.set_column_value('ATTRIBUTE12', m_attribute12);
1721 fnd_flex_descval.set_column_value('ATTRIBUTE13', m_attribute13);
1722 fnd_flex_descval.set_column_value('ATTRIBUTE14', m_attribute14);
1723 fnd_flex_descval.set_column_value('ATTRIBUTE15', m_attribute15);
1724
1725 IF FND_FLEX_DESCVAL.validate_desccols(p_FlexFieldAppShortName, p_FlexFieldName) THEN
1726 IF (NVL(LENGTH(FND_FLEX_DESCVAL.concatenated_ids),0) > 1) THEN
1727 m_resultValue := FND_FLEX_DESCVAL.concatenated_ids;
1728 ELSE
1729 m_resultValue := FND_FLEX_DESCVAL.concatenated_values;
1730 END IF;
1731 END IF;
1732
1733 end loop;
1734
1735 DBMS_SQL.CLOSE_CURSOR(source_cursor);
1736
1737 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1738 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'End FUN_RULE_PUB.getDFFDefaultValue', FALSE);
1739 end if;
1740
1741 return m_resultValue;
1742
1743 EXCEPTION
1744 WHEN OTHERS THEN
1745 IF DBMS_SQL.IS_OPEN(source_cursor) THEN
1746 DBMS_SQL.CLOSE_CURSOR(source_cursor);
1747 END IF;
1748 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1749 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION , 'FUN_RULE_PUB.getDFFDefaultValue:->'||SQLERRM, FALSE);
1750 END IF;
1751
1752 RAISE;
1753
1754 END getDFFDefaultValue;
1755
1756
1757 FUNCTION getResultValue(p_ruleDetailId IN NUMBER, p_ObjectType IN VARCHAR2) RETURN VARCHAR2
1758 IS
1759 source_cursor INTEGER;
1760 l_num_rows_processed INTEGER;
1761
1762 l_rule_detail_id NUMBER;
1763 l_result_value VARCHAR2(1024);
1764 l_result_application_id NUMBER;
1765 l_rule_name VARCHAR2(30);
1766
1767 BEGIN
1768 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1769 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.getResultValue:->p_ruleDetailId='||to_char(p_ruleDetailId)||'***p_ObjectType='||p_ObjectType, FALSE);
1770 end if;
1771
1772 source_cursor := DBMS_SQL.OPEN_CURSOR;
1773
1774 DBMS_SQL.PARSE(source_cursor, g_sResultValuesSql, DBMS_SQL.native);
1775 dbms_sql.bind_variable(source_cursor , '1' , p_ruleDetailId);
1776
1777 dbms_sql.define_column(source_cursor, 1, l_rule_detail_id);
1778 dbms_sql.define_column(source_cursor, 2, l_result_value , 1024);
1779 dbms_sql.define_column(source_cursor, 3, l_result_application_id);
1780 dbms_sql.define_column(source_cursor, 4, l_rule_name , 30);
1781
1782 l_num_rows_processed := DBMS_SQL.EXECUTE(source_cursor);
1783
1784 while(dbms_sql.fetch_rows(source_cursor) > 0 ) loop
1785 dbms_sql.column_value(source_cursor, 1, l_rule_detail_id);
1786 dbms_sql.column_value(source_cursor, 2, l_result_value);
1787 dbms_sql.column_value(source_cursor, 3, l_result_application_id);
1788 dbms_sql.column_value(source_cursor, 4, l_rule_name);
1789
1790 m_ruleDetailId := l_rule_detail_id;
1791 m_resultApplicationId := l_result_application_id;
1792 m_resultValue := l_result_value;
1793 m_ruleName := l_rule_name;
1794 exit;
1795 end loop;
1796
1797 DBMS_SQL.CLOSE_CURSOR(source_cursor);
1798
1799 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1800 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'End FUN_RULE_PUB.getResultValue', FALSE);
1801 end if;
1802
1803 return m_resultValue;
1804
1805 EXCEPTION
1806 WHEN NO_DATA_FOUND THEN
1807 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1808 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION , 'FUN_RULE_PUB.getResultValue:->NO_DATA_FOUND', FALSE);
1809 END IF;
1810
1811 RAISE;
1812
1813 WHEN OTHERS THEN
1814 IF DBMS_SQL.IS_OPEN(source_cursor) THEN
1815 DBMS_SQL.CLOSE_CURSOR(source_cursor);
1816 END IF;
1817 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1818 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION , 'FUN_RULE_PUB.getResultValue:->'||SQLERRM, FALSE);
1819 END IF;
1820
1821 RAISE;
1822
1823 END getResultValue;
1824
1825
1826 FUNCTION getDefaultValue(p_ruleObjectId IN NUMBER, p_ObjectType IN VARCHAR2) RETURN VARCHAR2
1827 IS
1828 source_cursor INTEGER;
1829 l_num_rows_processed INTEGER;
1830
1831 l_rule_detail_id NUMBER;
1832 l_default_value VARCHAR2(1024);
1833 l_default_application_id NUMBER;
1834
1835 BEGIN
1836
1837 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1838 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.getDefaultValue', FALSE);
1839 end if;
1840
1841 /*Assumption : For default value, Rule_Detail_Id = -99
1842 * Rule name = "Default Result"
1843 */
1844
1845 source_cursor := DBMS_SQL.OPEN_CURSOR;
1846
1847 DBMS_SQL.PARSE(source_cursor, g_sDefaultValuesSql, DBMS_SQL.native);
1848 dbms_sql.bind_variable(source_cursor , '1' , p_ruleObjectId);
1849
1850 dbms_sql.define_column(source_cursor, 1, l_default_value , 1024);
1851 dbms_sql.define_column(source_cursor, 2, l_default_application_id);
1852
1853 l_num_rows_processed := DBMS_SQL.EXECUTE(source_cursor);
1854
1855 while(dbms_sql.fetch_rows(source_cursor) > 0 ) loop
1856 dbms_sql.column_value(source_cursor, 1, l_default_value);
1857 dbms_sql.column_value(source_cursor, 2, l_default_application_id);
1858
1859 m_ruleDetailId := -99;
1860 m_resultApplicationId := l_default_application_id;
1861 m_resultValue := l_default_value;
1862 m_ruleName := 'Default Result';
1863 exit;
1864 end loop;
1865
1866 DBMS_SQL.CLOSE_CURSOR(source_cursor);
1867 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1868 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'End FUN_RULE_PUB.getDefaultValue', FALSE);
1869 end if;
1870
1871 return m_resultValue;
1872
1873 EXCEPTION
1874
1875 WHEN NO_DATA_FOUND THEN
1876 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1877 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION , 'FUN_RULE_PUB.getDefaultValue:->NO_DATA_FOUND', FALSE);
1878 END IF;
1879
1880 RAISE;
1881
1882 WHEN OTHERS THEN
1883 IF DBMS_SQL.IS_OPEN(source_cursor) THEN
1884 DBMS_SQL.CLOSE_CURSOR(source_cursor);
1885 END IF;
1886 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1887 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION , 'FUN_RULE_PUB.getDefaultValue:->'||SQLERRM, FALSE);
1888 END IF;
1889
1890 RAISE;
1891
1892 END getDefaultValue;
1893
1894 PROCEDURE getRuleObjectInfo(p_RuleObjectName IN VARCHAR2) IS
1895 source_cursor INTEGER;
1896 l_num_rows_processed INTEGER;
1897
1898 BEGIN
1899
1900 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1901 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.getRuleObjectInfo:->p_RuleObjectName='||p_RuleObjectName, FALSE);
1902 end if;
1903
1904 --IF Instance Context is set, then append the where clause with an extra bind variable i.e
1905 --AND INSTANCE_LABEL = :2
1906
1907 IF (m_instance_context = 'Y') THEN
1908 g_sRuleObjectSql := g_sRuleObjectMOACSql;
1909 ELSE
1910 g_sRuleObjectSql := g_sRuleObjectSql_orig;
1911 END IF;
1912
1913 m_ruleObjectType := null;
1914 m_ruleObjectId := 0;
1915 m_flexFieldName := null;
1916 m_flexFieldAppShortName := null;
1917
1918 source_cursor := DBMS_SQL.OPEN_CURSOR;
1919
1920 DBMS_SQL.PARSE(source_cursor, g_sRuleObjectSql, DBMS_SQL.native);
1921 dbms_sql.bind_variable(source_cursor , '1' , p_RuleObjectName);
1922
1923 IF (m_instance_context = 'Y') THEN
1924 dbms_sql.bind_variable(source_cursor , '2' , m_instance_label);
1925 dbms_sql.bind_variable(source_cursor , '3' , m_instance_label);
1926 dbms_sql.bind_variable(source_cursor , '4' , m_instance_label);
1927
1928 dbms_sql.bind_variable(source_cursor , '5' , m_org_id);
1929 dbms_sql.bind_variable(source_cursor , '6' , m_org_id);
1930 dbms_sql.bind_variable(source_cursor , '7' , m_org_id);
1931 END IF;
1932
1933 dbms_sql.define_column(source_cursor, 1, m_ruleObjectType , 15);
1934 dbms_sql.define_column(source_cursor, 2, m_ruleObjectId);
1935 dbms_sql.define_column(source_cursor, 3, m_flexFieldName , 30);
1936 dbms_sql.define_column(source_cursor, 4, m_flexFieldAppShortName , 10);
1937
1938 l_num_rows_processed := DBMS_SQL.EXECUTE(source_cursor);
1939
1940 while(dbms_sql.fetch_rows(source_cursor) > 0 ) loop
1941 dbms_sql.column_value(source_cursor, 1, m_ruleObjectType);
1942 dbms_sql.column_value(source_cursor, 2, m_ruleObjectId);
1943 dbms_sql.column_value(source_cursor, 3, m_flexFieldName);
1944 dbms_sql.column_value(source_cursor, 4, m_flexFieldAppShortName);
1945
1946 exit;
1947 end loop;
1948
1949 DBMS_SQL.CLOSE_CURSOR(source_cursor);
1950
1951 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1952 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'End FUN_RULE_PUB.getRuleObjectInfo', FALSE);
1953 end if;
1954
1955 EXCEPTION
1956
1957
1958 WHEN NO_DATA_FOUND THEN
1959 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1960 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION , 'FUN_RULE_PUB.getRuleObjectInfo:->NO_DATA_FOUND', FALSE);
1961 END IF;
1962
1963 RAISE;
1964
1965 WHEN OTHERS THEN
1966 IF DBMS_SQL.IS_OPEN(source_cursor) THEN
1967 DBMS_SQL.CLOSE_CURSOR(source_cursor);
1968 END IF;
1969 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1970 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION , 'FUN_RULE_PUB.getRuleObjectInfo:->g_sRuleObjectSql='||g_sRuleObjectSql, FALSE);
1971 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION , 'FUN_RULE_PUB.getRuleObjectInfo:->'||SQLERRM, FALSE);
1972 END IF;
1973 RAISE;
1974
1975 END getRuleObjectInfo;
1976
1977 FUNCTION ContainsKey(critObjectValue IN t_parameter_list,
1978 p_ParamName IN VARCHAR2) RETURN BOOLEAN IS
1979 BEGIN
1980 FOR i in 1..critObjectValue.count LOOP
1981 IF(critObjectValue(i).name = p_ParamName) THEN
1982 RETURN TRUE;
1983 END IF;
1984 END LOOP;
1985 RETURN FALSE;
1986 END ContainsKey;
1987
1988 FUNCTION get(critObjectValue IN t_parameter_list,
1989 p_ParamName IN VARCHAR2) RETURN VARCHAR2 IS
1990 BEGIN
1991 FOR i in 1..critObjectValue.count LOOP
1992 IF(critObjectValue(i).name = p_ParamName) THEN
1993 RETURN critObjectValue(i).value;
1994 END IF;
1995 END LOOP;
1996 RETURN NULL;
1997 END get;
1998
1999 FUNCTION getIndex(critObjectValue IN t_parameter_list,
2000 p_ParamName IN VARCHAR2) RETURN NUMBER IS
2001 BEGIN
2002 FOR i in 1..critObjectValue.count LOOP
2003 IF(critObjectValue(i).name = p_ParamName) THEN
2004 RETURN i;
2005 END IF;
2006 END LOOP;
2007 RETURN 0;
2008 END getIndex;
2009
2010
2011 FUNCTION isMatched(p_ParamName VARCHAR2, p_DataType VARCHAR2,
2012 p_Condition VARCHAR2, p_ParamValue VARCHAR2,
2013 p_ValueToBeCompared VARCHAR2,
2014 p_CaseSensitive VARCHAR2) RETURN BOOLEAN IS
2015 BEGIN
2016 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2017 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.isMatched:->p_ParamName='||p_ParamName, FALSE);
2018 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.isMatched:->p_DataType='||p_DataType, FALSE);
2019 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.isMatched:->p_Condition='||p_Condition, FALSE);
2020 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.isMatched:->p_ParamValue='||p_ParamValue, FALSE);
2021 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.isMatched:->p_ValueToBeCompared='||p_ValueToBeCompared, FALSE);
2022 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.isMatched:->p_CaseSensitive='||p_CaseSensitive, FALSE);
2023 end if;
2024
2025 IF p_Condition = C_EMPTY THEN
2026 RETURN (isMatchedEmpty(p_ValueToBeCompared));
2027 ELSIF (p_Condition IS null OR p_Condition = '' OR
2028 p_ParamValue IS null OR p_ParamValue = '' OR
2029 p_ValueToBeCompared IS null OR p_ValueToBeCompared = '') THEN
2030 RETURN false;
2031 ELSIF p_Condition = C_LIKE THEN
2032 RETURN (isMatchedLikeContain(p_ParamName, p_DataType, p_ParamValue,
2033 p_ValueToBeCompared , p_CaseSensitive));
2034 ELSIF p_Condition = C_EQUALS THEN
2035 RETURN (isMatchedEquals(p_ParamName, p_DataType, p_ParamValue,
2036 p_ValueToBeCompared , p_CaseSensitive));
2037 ELSIF p_Condition = C_IN THEN
2038 RETURN (isMatchedEquals(p_ParamName, p_DataType, p_ParamValue,
2039 p_ValueToBeCompared , p_CaseSensitive));
2040 ELSIF p_Condition = C_NOT_EQUALS THEN
2041 RETURN (isMatchedNotEquals(p_ParamName, p_DataType, p_ParamValue,
2042 p_ValueToBeCompared , p_CaseSensitive));
2043 ELSIF p_Condition = C_NOT_IN THEN
2044 RETURN (isMatchedNotEquals(p_ParamName, p_DataType, p_ParamValue,
2045 p_ValueToBeCompared , p_CaseSensitive));
2046 ELSIF p_Condition = C_GREATER_THAN THEN
2047 RETURN (isMatchedGreater(p_ParamName, p_DataType, p_ParamValue,
2048 p_ValueToBeCompared , p_CaseSensitive));
2049 ELSIF p_Condition = C_LESSER_THAN THEN
2050 RETURN (isMatchedLesser(p_ParamName, p_DataType, p_ParamValue,
2051 p_ValueToBeCompared , p_CaseSensitive));
2052 ELSIF p_Condition = C_GREATER_THAN_EQUAL THEN
2053 RETURN (isMatchedGreaterEqual(p_ParamName, p_DataType, p_ParamValue,
2054 p_ValueToBeCompared , p_CaseSensitive));
2055 ELSIF p_Condition = C_LESSER_THAN_EQUAL THEN
2056 RETURN (isMatchedLesserEqual(p_ParamName, p_DataType, p_ParamValue,
2057 p_ValueToBeCompared , p_CaseSensitive));
2058 ELSIF p_Condition = C_CONTAINS THEN
2059 RETURN (isMatchedLikeContain(p_ParamName, p_DataType, p_ParamValue,
2060 p_ValueToBeCompared , p_CaseSensitive));
2061 END IF;
2062
2063 END isMatched;
2064
2065 FUNCTION getComparedData(p_Obj1 IN VARCHAR2, p_Obj2 IN VARCHAR2,
2066 p_DataType IN VARCHAR2, p_CaseSensitive IN VARCHAR2) RETURN NUMBER IS
2067
2068 l_Obj1 VARCHAR2(300);
2069 l_Obj2 VARCHAR2(300);
2070 l_DataType VARCHAR2(30);
2071 l_CaseSensitive VARCHAR2(1);
2072 BEGIN
2073
2074 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2075 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.getComparedData', FALSE);
2076 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.getComparedData:->p_Obj1='||p_Obj1, FALSE);
2077 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.getComparedData:->p_Obj2='||p_Obj2, FALSE);
2078 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.getComparedData:->p_DataType='||p_DataType, FALSE);
2079 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.getComparedData:->p_CaseSensitive='||p_CaseSensitive, FALSE);
2080 end if;
2081
2082 IF(p_Obj1 IS NULL OR p_Obj2 IS NULL OR p_Obj1 = '' OR p_Obj2 = '') THEN
2083 return -1;
2084 END IF;
2085
2086 IF(p_DataType IS NULL OR p_DataType = '') THEN
2087 l_DataType := C_STRINGS;
2088 ELSE
2089 l_DataType := p_DataType;
2090 END IF;
2091
2092 IF(p_CaseSensitive IS NULL OR p_CaseSensitive = '') THEN
2093 l_CaseSensitive := C_NO;
2094 ELSE
2095 l_CaseSensitive := p_CaseSensitive;
2096 END IF;
2097
2098 IF(p_DataType = C_STRINGS AND p_CaseSensitive = C_NO) THEN
2099 l_Obj1 := UPPER(p_Obj1);
2100 l_Obj2 := UPPER(p_Obj2);
2101 ELSE
2102 l_Obj1 := p_Obj1;
2103 l_Obj2 := p_Obj2;
2104 END IF;
2105
2106 IF (l_DataType = C_STRINGS) THEN
2107 RETURN (compareTo(l_Obj1, l_Obj2 , l_DataType));
2108 ELSIF (l_DataType = C_NUMERIC) THEN
2109 RETURN (compareTo(l_Obj1, l_Obj2 , l_DataType));
2110 ELSIF (l_DataType = C_DATE) THEN
2111 RETURN (compareTo(l_Obj1, l_Obj2 , l_DataType));
2112 END IF;
2113
2114 RETURN -1;
2115 END getComparedData;
2116
2117 FUNCTION compareTo(p_Obj1 IN VARCHAR2, p_Obj2 IN VARCHAR2, p_DataType IN VARCHAR2)
2118 RETURN NUMBER IS
2119
2120 l_Obj1 VARCHAR2(300);
2121 l_Obj2 VARCHAR2(300);
2122
2123 BEGIN
2124 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2125 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.compareTo', FALSE);
2126 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.compareTo:->p_Obj1='||p_Obj1, FALSE);
2127 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.compareTo:->p_Obj2='||p_Obj2, FALSE);
2128 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.compareTo:->p_DataType='||p_DataType, FALSE);
2129 end if;
2130
2131 l_Obj1 := p_Obj1; l_Obj2 := p_Obj2;
2132
2133 IF(l_Obj1 IS NULL OR l_Obj2 IS NULL OR l_Obj1 = '' OR l_Obj2 = '') THEN
2134 return -1;
2135 END IF;
2136
2137 --Date is stored in the DB as RRRR/MM/DD HH:MI:SS format
2138
2139 IF(p_DataType = C_NUMERIC) THEN
2140 IF(FND_NUMBER.CANONICAL_TO_NUMBER(l_Obj1) > FND_NUMBER.CANONICAL_TO_NUMBER(l_Obj2))THEN
2141 RETURN 1;
2142 ELSIF(FND_NUMBER.CANONICAL_TO_NUMBER(l_Obj1) < FND_NUMBER.CANONICAL_TO_NUMBER(l_Obj2))THEN
2143 RETURN -1;
2144 ELSIF(FND_NUMBER.CANONICAL_TO_NUMBER(l_Obj1) = FND_NUMBER.CANONICAL_TO_NUMBER(l_Obj2))THEN
2145 RETURN 0;
2146 END IF;
2147 ELSIF (p_DataType = C_DATE) THEN
2148 IF(FND_DATE.CANONICAL_TO_DATE(l_Obj1) > FND_DATE.CANONICAL_TO_DATE(l_Obj2))THEN
2149 RETURN 1;
2150 ELSIF(FND_DATE.CANONICAL_TO_DATE(l_Obj1) < FND_DATE.CANONICAL_TO_DATE(l_Obj2))THEN
2151 RETURN -1;
2152 ELSIF(FND_DATE.CANONICAL_TO_DATE(l_Obj1) = FND_DATE.CANONICAL_TO_DATE(l_Obj2))THEN
2153 RETURN 0;
2154 END IF;
2155 ELSE
2156 IF(l_Obj1 > l_Obj2)THEN
2157 RETURN 1;
2158 ELSIF(l_Obj1 < l_Obj2)THEN
2159 RETURN -1;
2160 ELSIF(l_Obj1 = l_Obj2)THEN
2161 RETURN 0;
2162 END IF;
2163 END IF;
2164 return -1;
2165 END compareTo;
2166
2167
2168 FUNCTION isMatchedLikeContain(p_ParamName IN VARCHAR2, p_DataType IN VARCHAR2,
2169 p_ParamValue IN VARCHAR2,p_ValueToBeCompared IN VARCHAR2,
2170 p_CaseSensitive IN VARCHAR2) RETURN BOOLEAN IS
2171
2172 l_ValueToBeCompared VARCHAR2(240);
2173 l_ParamValue VARCHAR2(240);
2174
2175 BEGIN
2176 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2177 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.isMatchedLikeContain', FALSE);
2178 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.isMatchedLikeContain:->p_ParamName='||p_ParamName, FALSE);
2179 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.isMatchedLikeContain:->p_DataType='||p_DataType, FALSE);
2180 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.isMatchedLikeContain:->p_ParamValue='||p_ParamValue, FALSE);
2181 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.isMatchedLikeContain:->p_ValueToBeCompared='||p_ValueToBeCompared, FALSE);
2182 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.isMatchedLikeContain:->p_CaseSensitive='||p_CaseSensitive, FALSE);
2183 end if;
2184
2185 IF (p_CaseSensitive = C_NO) THEN
2186 l_ValueToBeCompared := UPPER(p_ValueToBeCompared);
2187 l_ParamValue := UPPER(p_ParamValue);
2188 ELSE
2189 l_ValueToBeCompared := p_ValueToBeCompared;
2190 l_ParamValue := p_ParamValue;
2191 END IF;
2192
2193 IF (INSTR(p_ValueToBeCompared , p_ParamValue) > 0 ) THEN
2194 RETURN TRUE;
2195 ELSE
2196 RETURN FALSE;
2197 END IF;
2198 END isMatchedLikeContain;
2199
2200 FUNCTION isMatchedEmpty(p_ValueToBeCompared IN VARCHAR2) RETURN BOOLEAN IS
2201
2202 l_ValueToBeCompared VARCHAR2(240);
2203 l_ParamValue VARCHAR2(240);
2204
2205 BEGIN
2206 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2207 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.isMatchedEmpty', FALSE);
2208 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.isMatchedEmpty:->p_ValueToBeCompared='||p_ValueToBeCompared, FALSE);
2209 end if;
2210
2211 IF(p_ValueToBeCompared IS NULL or p_ValueToBeCompared = '') THEN
2212 RETURN TRUE;
2213 ELSE
2214 RETURN FALSE;
2215 END IF;
2216 END isMatchedEmpty;
2217
2218 FUNCTION isMatchedEquals(p_ParamName IN VARCHAR2, p_DataType IN VARCHAR2,
2219 p_ParamValue IN VARCHAR2,p_ValueToBeCompared IN VARCHAR2,
2220 p_CaseSensitive IN VARCHAR2) RETURN BOOLEAN IS
2221 BEGIN
2222 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2223 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.isMatchedEquals', FALSE);
2224 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.isMatchedEquals:->p_ParamName='||p_ParamName, FALSE);
2225 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.isMatchedEquals:->p_DataType='||p_DataType, FALSE);
2226 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.isMatchedEquals:->p_ParamValue='||p_ParamValue, FALSE);
2227 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.isMatchedEquals:->p_ValueToBeCompared='||p_ValueToBeCompared, FALSE);
2228 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.isMatchedEquals:->p_CaseSensitive='||p_CaseSensitive, FALSE);
2229 end if;
2230
2231
2232 IF (getComparedData(p_ValueToBeCompared, p_ParamValue, p_DataType , p_CaseSensitive) = 0 ) THEN
2233 RETURN TRUE;
2234 ELSE
2235 RETURN FALSE;
2236 END IF;
2237 END isMatchedEquals;
2238
2239 FUNCTION isMatchedNotEquals(p_ParamName IN VARCHAR2, p_DataType IN VARCHAR2,
2240 p_ParamValue IN VARCHAR2,p_ValueToBeCompared IN VARCHAR2,
2241 p_CaseSensitive IN VARCHAR2) RETURN BOOLEAN IS
2242 BEGIN
2243 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2244 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.isMatchedNotEquals', FALSE);
2245 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.isMatchedNotEquals:->p_ParamName='||p_ParamName, FALSE);
2246 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.isMatchedNotEquals:->p_DataType='||p_DataType, FALSE);
2247 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.isMatchedNotEquals:->p_ParamValue='||p_ParamValue, FALSE);
2248 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.isMatchedNotEquals:->p_ValueToBeCompared='||p_ValueToBeCompared, FALSE);
2249 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.isMatchedNotEquals:->p_CaseSensitive='||p_CaseSensitive, FALSE);
2250 end if;
2251
2252 IF (getComparedData(p_ValueToBeCompared, p_ParamValue, p_DataType , p_CaseSensitive) <> 0 ) THEN
2253 RETURN TRUE;
2254 ELSE
2255 RETURN FALSE;
2256 END IF;
2257 END isMatchedNotEquals;
2258
2259 FUNCTION isMatchedGreater(p_ParamName IN VARCHAR2, p_DataType IN VARCHAR2,
2260 p_ParamValue IN VARCHAR2,p_ValueToBeCompared IN VARCHAR2,
2261 p_CaseSensitive IN VARCHAR2) RETURN BOOLEAN IS
2262 BEGIN
2263 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2264 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.isMatchedGreater', FALSE);
2265 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.isMatchedGreater:->p_ParamName='||p_ParamName, FALSE);
2266 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.isMatchedGreater:->p_DataType='||p_DataType, FALSE);
2267 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.isMatchedGreater:->p_ParamValue='||p_ParamValue, FALSE);
2268 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.isMatchedGreater:->p_ValueToBeCompared='||p_ValueToBeCompared, FALSE);
2269 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.isMatchedGreater:->p_CaseSensitive='||p_CaseSensitive, FALSE);
2270 end if;
2271
2272 IF (getComparedData(p_ValueToBeCompared, p_ParamValue, p_DataType , p_CaseSensitive) > 0 ) THEN
2273 RETURN TRUE;
2274 ELSE
2275 RETURN FALSE;
2276 END IF;
2277 END isMatchedGreater;
2278
2279 FUNCTION isMatchedLesser(p_ParamName IN VARCHAR2, p_DataType IN VARCHAR2,
2280 p_ParamValue IN VARCHAR2,p_ValueToBeCompared IN VARCHAR2,
2281 p_CaseSensitive IN VARCHAR2) RETURN BOOLEAN IS
2282 BEGIN
2283 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2284 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.isMatchedLesser', FALSE);
2285 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.isMatchedLesser:->p_ParamName='||p_ParamName, FALSE);
2286 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.isMatchedLesser:->p_DataType='||p_DataType, FALSE);
2287 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.isMatchedLesser:->p_ParamValue='||p_ParamValue, FALSE);
2288 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.isMatchedLesser:->p_ValueToBeCompared='||p_ValueToBeCompared, FALSE);
2289 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.isMatchedLesser:->p_CaseSensitive='||p_CaseSensitive, FALSE);
2290 end if;
2291
2292 IF (getComparedData(p_ValueToBeCompared, p_ParamValue, p_DataType , p_CaseSensitive) < 0 ) THEN
2293 RETURN TRUE;
2294 ELSE
2295 RETURN FALSE;
2296 END IF;
2297 END isMatchedLesser;
2298
2299 FUNCTION isMatchedGreaterEqual(p_ParamName IN VARCHAR2, p_DataType IN VARCHAR2,
2300 p_ParamValue IN VARCHAR2,p_ValueToBeCompared IN VARCHAR2,
2301 p_CaseSensitive IN VARCHAR2) RETURN BOOLEAN IS
2302 BEGIN
2303 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2304 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.isMatchedGreaterEqual', FALSE);
2305 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.isMatchedGreaterEqual:->p_ParamName='||p_ParamName, FALSE);
2306 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.isMatchedGreaterEqual:->p_DataType='||p_DataType, FALSE);
2307 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.isMatchedGreaterEqual:->p_ParamValue='||p_ParamValue, FALSE);
2308 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.isMatchedGreaterEqual:->p_ValueToBeCompared='||p_ValueToBeCompared, FALSE);
2309 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.isMatchedGreaterEqual:->p_CaseSensitive='||p_CaseSensitive, FALSE);
2310 end if;
2311
2312
2313 IF (getComparedData(p_ValueToBeCompared, p_ParamValue, p_DataType , p_CaseSensitive) >= 0 ) THEN
2314 RETURN TRUE;
2315 ELSE
2316 RETURN FALSE;
2317 END IF;
2318 END isMatchedGreaterEqual;
2319
2320 FUNCTION isMatchedLesserEqual(p_ParamName IN VARCHAR2, p_DataType IN VARCHAR2,
2321 p_ParamValue IN VARCHAR2,p_ValueToBeCompared IN VARCHAR2,
2322 p_CaseSensitive IN VARCHAR2) RETURN BOOLEAN IS
2323 BEGIN
2324 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2325 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.isMatchedLesserEqual', FALSE);
2326 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.isMatchedLesserEqual:->p_ParamName='||p_ParamName, FALSE);
2327 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.isMatchedLesserEqual:->p_DataType='||p_DataType, FALSE);
2328 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.isMatchedLesserEqual:->p_ParamValue='||p_ParamValue, FALSE);
2329 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.isMatchedLesserEqual:->p_ValueToBeCompared='||p_ValueToBeCompared, FALSE);
2330 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.isMatchedLesserEqual:->p_CaseSensitive='||p_CaseSensitive, FALSE);
2331 end if;
2332
2333 IF (getComparedData(p_ValueToBeCompared, p_ParamValue, p_DataType , p_CaseSensitive) <= 0 ) THEN
2334 RETURN TRUE;
2335 ELSE
2336 RETURN FALSE;
2337 END IF;
2338 END isMatchedLesserEqual;
2339
2340 FUNCTION get_rule_detail_id RETURN NUMBER IS
2341 BEGIN
2342 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2343 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.get_rule_detail_id', FALSE);
2344 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.get_rule_detail_id:->m_ruleDetailId'||to_char(m_ruleDetailId), FALSE);
2345 end if;
2346
2347 return m_ruleDetailId;
2348
2349 END get_rule_detail_id;
2350
2351 FUNCTION get_result_application_id RETURN NUMBER IS
2352 BEGIN
2353 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2354 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.get_result_application_id', FALSE);
2355 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.get_result_application_id:->m_resultApplicationId'||to_char(m_resultApplicationId), FALSE);
2356 end if;
2357
2358 return m_resultApplicationId;
2359
2360 END get_result_application_id;
2361
2362 FUNCTION get_rule_name RETURN VARCHAR2 IS
2363 BEGIN
2364 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2365 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.get_rule_name', FALSE);
2366 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.get_rule_name:->m_ruleName'||m_ruleName, FALSE);
2367 end if;
2368
2369 return m_ruleName;
2370
2371 END get_rule_name;
2372
2373 FUNCTION get_attribute_category RETURN VARCHAR2 IS
2374 BEGIN
2375 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2376 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.get_attribute_category', FALSE);
2377 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.get_attribute_category:->m_attributeCategory'||m_attributeCategory, FALSE);
2378 end if;
2379
2380 return m_attributeCategory;
2381
2382 END get_attribute_category;
2383
2384 FUNCTION get_attribute1 RETURN VARCHAR2 IS
2385 BEGIN
2386 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2387 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.get_attribute1', FALSE);
2388 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.get_attribute1:->m_attribute1='||m_attribute1, FALSE);
2389 end if;
2390
2391 return m_attribute1;
2392
2393 END get_attribute1;
2394
2395 FUNCTION get_attribute2 RETURN VARCHAR2 IS
2396 BEGIN
2397 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2398 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.get_attribute2', FALSE);
2399 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.get_attribute2:->m_attribute2='||m_attribute2, FALSE);
2400 end if;
2401
2402
2403 return m_attribute2;
2404
2405 END get_attribute2;
2406
2407 FUNCTION get_attribute3 RETURN VARCHAR2 IS
2408 BEGIN
2409 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2410 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.get_attribute3', FALSE);
2411 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.get_attribute3:->m_attribute3='||m_attribute3, FALSE);
2412 end if;
2413
2414
2415 return m_attribute3;
2416
2417 END get_attribute3;
2418
2419 FUNCTION get_attribute4 RETURN VARCHAR2 IS
2420 BEGIN
2421 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2422 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.get_attribute4', FALSE);
2423 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.get_attribute4:->m_attribute4='||m_attribute4, FALSE);
2424 end if;
2425
2426
2427 return m_attribute4;
2428
2429 END get_attribute4;
2430
2431 FUNCTION get_attribute5 RETURN VARCHAR2 IS
2432 BEGIN
2433 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2434 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.get_attribute5', FALSE);
2435 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.get_attribute5:->m_attribute1='||m_attribute5, FALSE);
2436 end if;
2437
2438
2439 return m_attribute5;
2440
2441 END get_attribute5;
2442
2443 FUNCTION get_attribute6 RETURN VARCHAR2 IS
2444 BEGIN
2445 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2446 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.get_attribute6', FALSE);
2447 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.get_attribute6:->m_attribute6='||m_attribute6, FALSE);
2448 end if;
2449
2450
2451 return m_attribute6;
2452 END get_attribute6;
2453
2454 FUNCTION get_attribute7 RETURN VARCHAR2 IS
2455 BEGIN
2456 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2457 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.get_attribute7', FALSE);
2458 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.get_attribute7:->m_attribute7='||m_attribute7, FALSE);
2459 end if;
2460
2461
2462 return m_attribute7;
2463 END get_attribute7;
2464
2465 FUNCTION get_attribute8 RETURN VARCHAR2 IS
2466 BEGIN
2467 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2468 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.get_attribute8', FALSE);
2469 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.get_attribute8:->m_attribute8='||m_attribute8, FALSE);
2470 end if;
2471
2472
2473 return m_attribute8;
2474 END get_attribute8;
2475
2476 FUNCTION get_attribute9 RETURN VARCHAR2 IS
2477 BEGIN
2478 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2479 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.get_attribute9', FALSE);
2480 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.get_attribute9:->m_attribute9='||m_attribute9, FALSE);
2481 end if;
2482
2483
2484 return m_attribute9;
2485 END get_attribute9;
2486
2487 FUNCTION get_attribute10 RETURN VARCHAR2 IS
2488 BEGIN
2489 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2490 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.get_attribute10', FALSE);
2491 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.get_attribute10:->m_attribute10='||m_attribute10, FALSE);
2492 end if;
2493
2494
2495 return m_attribute10;
2496
2497 END get_attribute10;
2498
2499 FUNCTION get_attribute11 RETURN VARCHAR2 IS
2500 BEGIN
2501 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2502 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.get_attribute11', FALSE);
2503 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.get_attribute11:->m_attribute11='||m_attribute11, FALSE);
2504 end if;
2505
2506
2507 return m_attribute11;
2508 END get_attribute11;
2509
2510 FUNCTION get_attribute12 RETURN VARCHAR2 IS
2511 BEGIN
2512 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2513 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.get_attribute12', FALSE);
2514 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.get_attribute12:->m_attribute12'||m_attribute12, FALSE);
2515 end if;
2516
2517
2518 return m_attribute12;
2519 END get_attribute12;
2520
2521 FUNCTION get_attribute13 RETURN VARCHAR2 IS
2522 BEGIN
2523 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2524 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.get_attribute13', FALSE);
2525 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.get_attribute13:->m_attribute13='||m_attribute13, FALSE);
2526 end if;
2527
2528
2529 return m_attribute13;
2530 END get_attribute13;
2531
2532 FUNCTION get_attribute14 RETURN VARCHAR2 IS
2533 BEGIN
2534 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2535 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.get_attribute14', FALSE);
2536 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.get_attribute14:->m_attribute14'||m_attribute14, FALSE);
2537 end if;
2538
2539
2540 return m_attribute14;
2541 END get_attribute14;
2542
2543 FUNCTION get_attribute15 RETURN VARCHAR2 IS
2544 BEGIN
2545 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2546 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.get_attribute15', FALSE);
2547 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.get_attribute15:->m_attribute15='||m_attribute15, FALSE);
2548 end if;
2549
2550 return m_attribute15;
2551 END get_attribute15;
2552
2553 FUNCTION get_message_app_name RETURN VARCHAR2 IS
2554 BEGIN
2555 if (m_resultApplicationId = 0 OR m_resultApplicationId is null) then
2556 return 'FUN';
2557 else
2558 return(FUN_RULE_UTILITY_PKG.getApplicationShortName(m_resultApplicationId));
2559 end if;
2560 END get_message_app_name;
2561
2562 FUNCTION GET_MULTI_RULE_RESULTS_TABLE RETURN fun_rule_results_table is
2563 BEGIN
2564 return m_RuleResultTable;
2565 END GET_MULTI_RULE_RESULTS_TABLE;
2566
2567 PROCEDURE apply_rule_bulk(p_application_short_name IN VARCHAR2,
2568 p_rule_object_name IN VARCHAR2,
2569 p_param_view_name IN VARCHAR2,
2570 p_additional_where_clause IN VARCHAR2,
2571 p_primary_key_column_name IN VARCHAR2 DEFAULT 'ID') IS
2572
2573 l_application_id NUMBER;
2574 l_theCursor INTEGER;
2575 l_stringToParse VARCHAR2(20000);
2576 l_colValue VARCHAR2(4000);
2577 l_counter PLS_INTEGER := 0;
2578 l_descTbl DBMS_SQL.DESC_TAB;
2579 l_numColumns INTEGER;
2580
2581 source_cursor INTEGER;
2582 l_num_rows_processed INTEGER;
2583 l_crit_param_name VARCHAR2(100);
2584
2585 destination_cursor INTEGER;
2586 params_cursor INTEGER;
2587 params_rows_processed INTEGER;
2588
2589 l_result_type FUN_RULE_OBJECTS_B.RESULT_TYPE%TYPE;
2590 l_rule_detail_id FUN_RULE_DETAILS.RULE_DETAIL_ID%TYPE;
2591 l_prev_rule_detail_id FUN_RULE_DETAILS.RULE_DETAIL_ID%TYPE;
2592 l_operator FUN_RULE_DETAILS.OPERATOR%TYPE;
2593 l_rule_object_id FUN_RULE_DETAILS.RULE_OBJECT_ID%TYPE;
2594 l_flexfield_name FUN_RULE_OBJECTS_B.FLEXFIELD_NAME%TYPE;
2595 l_flexfield_app_short_name FUN_RULE_OBJECTS_B.FLEXFIELD_APP_SHORT_NAME%TYPE;
2596 l_multiRuleResultFlag FUN_RULE_OBJECTS_B.MULTI_RULE_RESULT_FLAG%TYPE;
2597
2598 l_param_name FUN_RULE_CRIT_PARAMS_B.PARAM_NAME%TYPE;
2599 l_condition FUN_RULE_CRITERIA.CONDITION%TYPE;
2600 l_param_value FUN_RULE_CRITERIA.PARAM_VALUE%TYPE;
2601 l_data_type FUN_RULE_CRIT_PARAMS_B.DATA_TYPE%TYPE;
2602 l_case_sensitive FUN_RULE_CRITERIA.CASE_SENSITIVE_FLAG%TYPE;
2603 l_criteria_id FUN_RULE_CRITERIA.CRITERIA_ID%TYPE;
2604
2605 l_ParamMultiValueList multi_param_value;
2606
2607 l_result_value VARCHAR2(1024);
2608
2609 l_select_query VARCHAR2(20000);
2610 l_default_select_query VARCHAR2(20000);
2611 l_where_clause VARCHAR2(2000) := '';
2612 l_insert_statement VARCHAR2(20000);
2613
2614 paramVal_cursor INTEGER;
2615 paramVal_rows_processed INTEGER;
2616
2617 l_counter NUMBER := 1;
2618 l_count NUMBER;
2619 l_isRuleValid BOOLEAN := FALSE;
2620
2621 l_paramPresent BOOLEAN := FALSE;
2622 l_isAnyRuleOk BOOLEAN := FALSE;
2623 l_noRuleActive BOOLEAN := FALSE;
2624
2625 l_old_moac_access_mode VARCHAR2(1);
2626 l_old_org_id NUMBER;
2627 l_date_format VARCHAR2(50) := 'YYYY/MM/DD HH24:MI:SS';
2628 l_param_type VARCHAR2(20);
2629 l_criteria_parameter_id FUN_RULE_CRIT_PARAMS_B.CRITERIA_PARAM_ID%TYPE;
2630
2631 BEGIN
2632 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2633 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.apply_rule_bulk', FALSE);
2634 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.apply_rule_bulk:->p_application_short_name='||p_application_short_name, FALSE);
2635 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.apply_rule_bulk:->p_rule_object_name='||p_rule_object_name, FALSE);
2636 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.apply_rule_bulk:->p_param_view_name='||p_param_view_name, FALSE);
2637 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.apply_rule_bulk:->p_additional_where_clause='||p_additional_where_clause, FALSE);
2638 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.apply_rule_bulk:->p_primary_key_column_name='||p_primary_key_column_name, FALSE);
2639 end if;
2640
2641
2642
2643 IF ( p_application_short_name IS NULL OR p_application_short_name = '') THEN
2644 fnd_message.set_name('FND','FND_NO_APP_SHORT_NAME');
2645 fnd_message.set_token('APP_SHORT_NAME',p_application_short_name);
2646 fnd_msg_pub.add;
2647 RAISE fnd_api.g_exc_unexpected_error;
2648 END IF;
2649
2650 IF ( p_rule_object_name IS NULL OR p_rule_object_name = '') THEN
2651 fnd_message.set_name('FND','FND_NO_RULE_OBJECT_NAME');
2652 fnd_message.set_token('RULE_OBJECT_NAME',p_rule_object_name);
2653 fnd_msg_pub.add;
2654 RAISE fnd_api.g_exc_unexpected_error;
2655 END IF;
2656
2657 IF ( p_param_view_name IS NULL OR p_param_view_name = '') THEN
2658 fnd_message.set_name('FND','FND_NO_PARAM_VIEW_NAME');
2659 fnd_message.set_token('PARAM_VIEW_NAME',p_param_view_name);
2660 fnd_msg_pub.add;
2661 RAISE fnd_api.g_exc_unexpected_error;
2662 END IF;
2663
2664
2665 /*Rule Object Instance MOAC Changes:
2666 * If p_org_id is passed and the set_instance_context is called, then set the MOAC context based on
2667 * following logic.
2668 * If Product team has not called MO_GLOBAL.INIT then raise an exception.
2669 * Else, get the access_mode. If access_mode is not S, then set to S and the passed p_og_id.
2670 */
2671 l_old_moac_access_mode := MO_GLOBAL.get_access_mode();
2672 l_old_org_id := MO_GLOBAL.get_current_org_id();
2673
2674 --Does validation and then sets the policy context to S, if its not S and
2675 --the passed org id value is not same as current org id value.
2676
2677 IF (m_org_id IS NOT NULL) THEN
2678 FUN_RULE_MOAC_PKG.SET_MOAC_ACCESS_MODE(m_org_id);
2679 END IF;
2680
2681
2682 refreshGTBulkTable;
2683 init_parameter_list;
2684 l_application_id := FUN_RULE_UTILITY_PKG.getApplicationID(p_application_short_name);
2685
2686
2687
2688 IF(l_application_id IS NULL) THEN
2689 l_application_id := FND_GLOBAL.RESP_APPL_ID;
2690 END IF;
2691
2692 IF (p_additional_where_clause IS NULL) THEN
2693 l_stringToParse := 'select * from ' || p_param_view_name || ' where ROWNUM = 1';
2694 ELSE
2695 l_stringToParse := 'select * from ' || p_param_view_name || ' where ' || p_additional_where_clause || ' AND ROWNUM = 1';
2696 END IF;
2697
2698
2699 l_theCursor := dbms_sql.open_cursor;
2700 dbms_sql.parse( c => l_theCursor,
2701 statement => l_stringToParse,
2702 language_flag => dbms_sql.native);
2703
2704 dbms_sql.describe_columns(l_theCursor, l_numColumns, l_descTbl);
2705
2706
2707 destination_cursor := DBMS_SQL.OPEN_CURSOR;
2708
2709 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2710 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.apply_rule_bulk:->g_sRuleDetailSql='||g_sRuleDetailSql, FALSE);
2711 end if;
2712
2713 --IF Instance Context is set, then append the where clause with an extra bind variable i.e
2714 --AND INSTANCE_LABEL = :3
2715
2716 IF (m_instance_context = 'Y') THEN
2717 g_sRuleDetailSql := g_sRuleDetailMOACSql;
2718 ELSE
2719 g_sRuleDetailSql := g_sRuleDetailSql_orig;
2720 END IF;
2721
2722
2723 DBMS_SQL.PARSE(destination_cursor, g_sRuleDetailSql,DBMS_SQL.native);
2724 dbms_sql.bind_variable(destination_cursor , '1' , p_rule_object_name);
2725 dbms_sql.bind_variable(destination_cursor , '2' , l_application_id);
2726
2727 IF (m_instance_context = 'Y') THEN
2728 dbms_sql.bind_variable(destination_cursor , '3' , m_instance_label);
2729 dbms_sql.bind_variable(destination_cursor , '4' , m_instance_label);
2730 dbms_sql.bind_variable(destination_cursor , '5' , m_instance_label);
2731
2732 dbms_sql.bind_variable(destination_cursor , '6' , m_org_id);
2733 dbms_sql.bind_variable(destination_cursor , '7' , m_org_id);
2734 dbms_sql.bind_variable(destination_cursor , '8' , m_org_id);
2735 END IF;
2736
2737 dbms_sql.define_column(destination_cursor, 1, l_result_type , 30);
2738 dbms_sql.define_column(destination_cursor, 2, l_rule_detail_id);
2739 dbms_sql.define_column(destination_cursor, 3, l_operator , 3);
2740 dbms_sql.define_column(destination_cursor, 4, l_rule_object_id);
2741 dbms_sql.define_column(destination_cursor, 5, l_flexfield_name , 80);
2742 dbms_sql.define_column(destination_cursor, 6, l_flexfield_app_short_name , 30);
2743 dbms_sql.define_column(destination_cursor, 7, l_multiRuleResultFlag , 10);
2744
2745 l_num_rows_processed := DBMS_SQL.EXECUTE(destination_cursor);
2746
2747 --If No rules active.
2748 if(l_num_rows_processed = 0) then
2749 l_noRuleActive := TRUE;
2750 end if;
2751
2752 while(dbms_sql.fetch_rows(destination_cursor) > 0 ) loop
2753 l_count := 1;
2754 /* Initialize the var l_paramPresent for each rule. If any param matches then make it true
2755 * and insert the record into FUN_RULE_BULK_RESULT_GT table.
2756 */
2757 l_paramPresent := FALSE;
2758
2759
2760 l_select_query := 'SELECT '||p_primary_key_column_name||' , ';
2761 l_where_clause := '';
2762 l_insert_statement := 'insert into FUN_RULE_BULK_RESULT_GT(ID, RESULT_VALUE , RULE_NAME ,RESULT_APPLICATION_ID, RULE_DETAIL_ID ) ';
2763
2764 dbms_sql.column_value(destination_cursor, 1, l_result_type);
2765 dbms_sql.column_value(destination_cursor, 2, l_rule_detail_id );
2766 dbms_sql.column_value(destination_cursor, 3, l_operator);
2767 dbms_sql.column_value(destination_cursor, 4, l_rule_object_id);
2768 dbms_sql.column_value(destination_cursor, 5, l_flexfield_name);
2769 dbms_sql.column_value(destination_cursor, 6, l_flexfield_app_short_name);
2770 dbms_sql.column_value(destination_cursor, 7, l_multiRuleResultFlag);
2771
2772 params_cursor := DBMS_SQL.OPEN_CURSOR;
2773
2774 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2775 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.apply_rule_bulk:->g_sCriteriaParamSql='||g_sCriteriaParamSql, FALSE);
2776 end if;
2777
2778 DBMS_SQL.PARSE(params_cursor, g_sCriteriaParamSql,DBMS_SQL.native);
2779
2780 dbms_sql.bind_variable(params_cursor , '1' , l_rule_detail_id);
2781 dbms_sql.bind_variable(params_cursor , '2' , l_rule_detail_id);
2782
2783 dbms_sql.define_column(params_cursor, 1, l_param_name , 30);
2784 dbms_sql.define_column(params_cursor, 2, l_condition , 15);
2785 dbms_sql.define_column(params_cursor, 3, l_param_value , 1024);
2786 dbms_sql.define_column(params_cursor, 4, l_data_type, 15);
2787 dbms_sql.define_column(params_cursor, 5, l_case_sensitive, 1);
2788 dbms_sql.define_column(params_cursor, 6, l_criteria_id);
2789
2790 params_rows_processed := DBMS_SQL.EXECUTE(params_cursor);
2791
2792 while(dbms_sql.fetch_rows(params_cursor) > 0 ) loop
2793 dbms_sql.column_value(params_cursor, 1, l_param_name );
2794 dbms_sql.column_value(params_cursor, 2, l_condition );
2795 dbms_sql.column_value(params_cursor, 3, l_param_value);
2796 dbms_sql.column_value(params_cursor, 4, l_data_type);
2797 dbms_sql.column_value(params_cursor, 5, l_case_sensitive);
2798 dbms_sql.column_value(params_cursor, 6, l_criteria_id);
2799 /*
2800 START: RULES ENGINE ENHANCEMNT FOR CUSTOM PARAMETERS
2801 If the parameter is a custom parameter, then select the
2802 column name as the param name. Down the line, a query is
2803 built with param name in the where condition. Custom Param
2804 Name given while creating is not identical with the column
2805 name.If the parameter chosen is a custom parameter, change the
2806 name of the param name to the associated column name
2807 */
2808
2809 SELECT parameter_type,criteria.criteria_param_id INTO l_param_type,l_criteria_parameter_id
2810 FROM fun_rule_crit_params_b param, fun_rule_criteria criteria
2811 WHERE criteria.criteria_id = l_criteria_id
2812 AND criteria.criteria_param_id = param.criteria_param_id;
2813
2814 IF (l_param_type = 'CUSTOM') THEN
2815 SELECT COLUMN_NAME into l_param_name
2816 FROM FUN_RULE_CRIT_PARAMS_B
2817 WHERE criteria_param_id = l_criteria_parameter_id;
2818 END IF;
2819 /*END: RULES ENGINE ENHANCEMNT FOR CUSTOM PARAMETERS*/
2820
2821 IF (l_data_type = C_DATE) THEN
2822 l_param_value := fnd_date.canonical_to_date(l_param_value);
2823 ELSIF (l_data_type = C_NUMERIC) THEN
2824 l_param_value := fnd_number.canonical_to_number(l_param_value);
2825 END IF;
2826
2827 for i in 1..l_numColumns
2828 loop
2829 l_crit_param_name := l_descTbl(i).col_name;
2830 if(l_crit_param_name = l_param_name) then
2831 l_paramPresent := TRUE;
2832 paramVal_cursor := DBMS_SQL.OPEN_CURSOR;
2833
2834 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2835 fnd_log.message(FND_LOG.LEVEL_STATEMENT,
2836 'Start FUN_RULE_PUB.apply_rule_bulk:->g_sMultiCriteriaParamValueSql='||g_sMultiCriteriaParamValueSql, FALSE);
2837 end if;
2838
2839 DBMS_SQL.PARSE(paramVal_cursor, g_sMultiCriteriaParamValueSql,DBMS_SQL.native);
2840 dbms_sql.bind_variable(paramVal_cursor , '1' , l_rule_detail_id);
2841 dbms_sql.bind_variable(paramVal_cursor , '2' , l_criteria_id);
2842
2843 dbms_sql.define_column(paramVal_cursor, 1, l_param_value , 1024);
2844
2845 paramVal_rows_processed := DBMS_SQL.EXECUTE(paramVal_cursor);
2846
2847 if(l_condition = C_IN) then
2848 l_where_clause := l_where_clause || l_param_name || ' IN (';
2849 while(dbms_sql.fetch_rows(paramVal_cursor) > 0 ) loop
2850 dbms_sql.column_value(paramVal_cursor, 1, l_param_value );
2851 if(l_data_type = C_STRINGS) then
2852 l_where_clause := l_where_clause || ' '''|| l_param_value ||''', ';
2853 elsif(l_data_type = C_DATE) then
2854 l_where_clause := l_where_clause || ' to_date('''|| l_param_value ||''','''|| l_date_format ||''' ), ';
2855 else
2856 l_where_clause := l_where_clause || ' '|| l_param_value ||' ,' ;
2857 end if;
2858 end loop;
2859 l_where_clause := l_where_clause ||')';
2860 l_where_clause := substr(l_where_clause,1,instr(l_where_clause,',',-1)-1) || substr(l_where_clause,instr(l_where_clause,',',-1)+1);
2861 elsif (l_condition = C_NOT_IN) then
2862 l_where_clause := l_where_clause || l_param_name || ' NOT IN (';
2863 while(dbms_sql.fetch_rows(paramVal_cursor) > 0 ) loop
2864 dbms_sql.column_value(paramVal_cursor, 1, l_param_value );
2865 if(l_data_type = C_STRINGS) then
2866 l_where_clause := l_where_clause || ' '''|| l_param_value ||''', ';
2867 elsif(l_data_type = C_DATE) then
2868 l_where_clause := l_where_clause || ' to_date('''|| l_param_value ||''', '''|| l_date_format ||''' ), ';
2869 else
2870 l_where_clause := l_where_clause || ' '|| l_param_value ||' ,' ;
2871 end if;
2872 end loop;
2873 l_where_clause := l_where_clause ||' ) ';
2874 l_where_clause := substr(l_where_clause,1,instr(l_where_clause,',',-1)-1) || substr(l_where_clause,instr(l_where_clause,',',-1)+1);
2875 elsif (l_condition = C_LIKE OR l_condition = C_CONTAINS) then
2876 l_where_clause := l_where_clause || l_param_name || ' LIKE (';
2877 while(dbms_sql.fetch_rows(paramVal_cursor) > 0 ) loop
2878 dbms_sql.column_value(paramVal_cursor, 1, l_param_value );
2879 if(l_data_type = C_STRINGS OR l_data_type = C_DATE) then
2880 l_where_clause := l_where_clause || ' '''||'%'|| l_param_value ||'%'||''' ,' ;
2881 NULL;
2882 else
2883 l_where_clause := l_where_clause || ' %'|| l_param_value ||'% ,' ;
2884 end if;
2885 end loop;
2886 l_where_clause := l_where_clause ||' ) ';
2887 l_where_clause := substr(l_where_clause,1,instr(l_where_clause,',',-1)-1) || substr(l_where_clause,instr(l_where_clause,',',-1)+1);
2888 elsif (l_condition = C_EQUALS) then
2889 l_where_clause := l_where_clause || l_param_name || ' = (';
2890 while(dbms_sql.fetch_rows(paramVal_cursor) > 0 ) loop
2891 dbms_sql.column_value(paramVal_cursor, 1, l_param_value );
2892 if(l_data_type = C_STRINGS) then
2893 l_where_clause := l_where_clause || ' '''|| l_param_value ||''', ' ;
2894 elsif(l_data_type = C_DATE) then
2895 l_where_clause := l_where_clause || ' to_date('''|| l_param_value ||''', '''|| l_date_format ||''' ), ';
2896 else
2897 l_where_clause := l_where_clause || ' '|| l_param_value ||' ,' ;
2898 end if;
2899 end loop;
2900 l_where_clause := l_where_clause ||' ) ';
2901 l_where_clause := substr(l_where_clause,1,instr(l_where_clause,',',-1)-1) || substr(l_where_clause,instr(l_where_clause,',',-1)+1);
2902 elsif (l_condition = C_NOT_EQUALS) then
2903 l_where_clause := l_where_clause || l_param_name || ' <> (';
2904 while(dbms_sql.fetch_rows(paramVal_cursor) > 0 ) loop
2905 dbms_sql.column_value(paramVal_cursor, 1, l_param_value );
2906 if(l_data_type = C_STRINGS) then
2907 l_where_clause := l_where_clause || ' '''|| l_param_value ||''', ' ;
2908 elsif(l_data_type = C_DATE) then
2909 l_where_clause := l_where_clause || ' to_date('''|| l_param_value ||''', '''|| l_date_format ||''' ), ';
2910 else
2911 l_where_clause := l_where_clause || ' '|| l_param_value ||' ,' ;
2912 end if;
2913 end loop;
2914 l_where_clause := l_where_clause ||' ) ';
2915 l_where_clause := substr(l_where_clause,1,instr(l_where_clause,',',-1)-1) || substr(l_where_clause,instr(l_where_clause,',',-1)+1);
2916 elsif (l_condition = C_GREATER_THAN) then
2917 l_where_clause := l_where_clause || l_param_name || ' > (';
2918 while(dbms_sql.fetch_rows(paramVal_cursor) > 0 ) loop
2919 dbms_sql.column_value(paramVal_cursor, 1, l_param_value );
2920 if(l_data_type = C_STRINGS) then
2921 l_where_clause := l_where_clause || ' '''|| l_param_value ||''', ' ;
2922 elsif(l_data_type = C_DATE) then
2923 l_where_clause := l_where_clause || ' to_date('''|| l_param_value ||''', '''|| l_date_format ||''' ), ';
2924 else
2925 l_where_clause := l_where_clause || ' '|| l_param_value ||' ,' ;
2926 end if;
2927 end loop;
2928 l_where_clause := l_where_clause ||' ) ';
2929 l_where_clause := substr(l_where_clause,1,instr(l_where_clause,',',-1)-1) || substr(l_where_clause,instr(l_where_clause,',',-1)+1);
2930 elsif (l_condition = C_LESSER_THAN) then
2931 l_where_clause := l_where_clause || l_param_name || ' < (';
2932 while(dbms_sql.fetch_rows(paramVal_cursor) > 0 ) loop
2933 dbms_sql.column_value(paramVal_cursor, 1, l_param_value );
2934 if(l_data_type = C_STRINGS) then
2935 l_where_clause := l_where_clause || ' '''|| l_param_value ||''', ' ;
2936 elsif(l_data_type = C_DATE) then
2937 l_where_clause := l_where_clause || ' to_date('''|| l_param_value ||''', '''|| l_date_format ||''' ), ';
2938 else
2939 l_where_clause := l_where_clause || ' '|| l_param_value ||' ,' ;
2940 end if;
2941 end loop;
2942 l_where_clause := l_where_clause ||' ) ';
2943 l_where_clause := substr(l_where_clause,1,instr(l_where_clause,',',-1)-1) || substr(l_where_clause,instr(l_where_clause,',',-1)+1);
2944 elsif (l_condition = C_GREATER_THAN_EQUAL) then
2945 l_where_clause := l_where_clause || l_param_name || ' >= (';
2946 while(dbms_sql.fetch_rows(paramVal_cursor) > 0 ) loop
2947 dbms_sql.column_value(paramVal_cursor, 1, l_param_value );
2948 if(l_data_type = C_STRINGS) then
2949 l_where_clause := l_where_clause || ' '''|| l_param_value ||''', ' ;
2950 elsif(l_data_type = C_DATE) then
2951 l_where_clause := l_where_clause || ' to_date('''|| l_param_value ||''', '''|| l_date_format ||''' ), ';
2952 else
2953 l_where_clause := l_where_clause || ' '|| l_param_value ||' ,' ;
2954 end if;
2955 end loop;
2956 l_where_clause := l_where_clause ||' ) ';
2957 l_where_clause := substr(l_where_clause,1,instr(l_where_clause,',',-1)-1) || substr(l_where_clause,instr(l_where_clause,',',-1)+1);
2958 elsif (l_condition = C_LESSER_THAN_EQUAL) then
2959 l_where_clause := l_where_clause || l_param_name || ' <= (';
2960 while(dbms_sql.fetch_rows(paramVal_cursor) > 0 ) loop
2961 dbms_sql.column_value(paramVal_cursor, 1, l_param_value );
2962 if(l_data_type = C_STRINGS) then
2963 l_where_clause := l_where_clause || ' '''|| l_param_value ||''', ' ;
2964 elsif(l_data_type = C_DATE) then
2965 l_where_clause := l_where_clause || ' to_date('''|| l_param_value ||''', '''|| l_date_format ||''' ), ';
2966 else
2967 l_where_clause := l_where_clause || ' '|| l_param_value ||' ,' ;
2968 end if;
2969 end loop;
2970 l_where_clause := l_where_clause ||' ) ';
2971 l_where_clause := substr(l_where_clause,1,instr(l_where_clause,',',-1)-1) || substr(l_where_clause,instr(l_where_clause,',',-1)+1);
2972 end if;
2973 l_where_clause := l_where_clause || ' ' || l_operator || ' ';
2974 DBMS_SQL.CLOSE_CURSOR(paramVal_cursor);
2975 else
2976 NULL;
2977 end if; --If l_crit_param_name = l_param_name
2978 end loop; --End loop of l_descCol
2979 end loop; --End Loop params sql
2980
2981 IF (l_where_clause IS NOT NULL) THEN
2982 l_where_clause := rtrim(l_where_clause);
2983 l_where_clause := substr(l_where_clause, 0, length(l_where_clause)-3);
2984 END IF;
2985
2986
2987 /*Check for value of l_paramPresent. If this is false, that means not a single column in the
2988 *product team's GT table has got correct criteria parameter names.
2989 */
2990
2991 if(NOT l_paramPresent) then
2992 raise INVAILD_COLUMN_NAME;
2993 end if;
2994
2995 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2996 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'FUN_RULE_PUB.apply_rule_bulk:->before setResultValues', FALSE);
2997 end if;
2998
2999 if(l_rule_detail_id IS NOT NULL) then
3000 l_isRuleValid := isRuleValid(p_param_view_name , l_where_clause);
3001
3002 --Check for a single ruile that gets satisfied, else this is to track
3003 --the default result to be returned.
3004
3005 l_isAnyRuleOk := l_isAnyRuleOk OR l_isRuleValid;
3006
3007 if (l_isRuleValid) then
3008 setResultValues(l_isRuleValid,
3009 TRUE,
3010 l_rule_object_id,
3011 l_result_type,
3012 p_rule_object_name,
3013 l_flexfield_name,
3014 l_flexfield_app_short_name,
3015 l_rule_detail_id);
3016 l_select_query := l_select_query ||' '''||m_resultValue||''', '''||m_ruleName||''', '||NVL(m_resultApplicationId, FND_GLOBAL.resp_appl_id)||' , '||m_ruleDetailId||' FROM '||p_param_view_name||' WHERE ';
3017 l_default_select_query := l_select_query || ' AND ROWNUM =1 ';
3018 if(l_where_clause IS NOT NULL) then
3019 l_select_query := l_select_query || l_where_clause;
3020 end if;
3021 end if;
3022 end if;
3023
3024
3025 IF (p_additional_where_clause IS NOT NULL) THEN
3026 l_select_query := l_select_query || ' AND ' || p_additional_where_clause;
3027 END IF;
3028
3029 DBMS_SQL.CLOSE_CURSOR(params_cursor);
3030
3031 if(l_isRuleValid) then
3032 l_insert_statement := l_insert_statement || ' ' || l_select_query ||
3033 ' AND NOT EXISTS(SELECT 1 FROM FUN_RULE_BULK_RESULT_GT WHERE ID='|| p_param_view_name||'.'||p_primary_key_column_name||')';
3034 IF (populateGTBulkTable(l_insert_statement)) THEN
3035 NULL;
3036 END IF;
3037 end if;
3038
3039 end loop;
3040
3041 /*If not a single rule is satisfied then simply insert the default result SELECT statement*/
3042
3043 /* bug 7337383 */
3044 -- if (NOT l_isAnyRuleOk) then
3045 -- refreshGTBulkTable;
3046
3047 l_select_query := 'SELECT '||p_primary_key_column_name||' , ';
3048 l_where_clause := ' AND NOT EXISTS(SELECT 1 FROM FUN_RULE_BULK_RESULT_GT WHERE ID='|| p_param_view_name||'.'||p_primary_key_column_name||')';
3049 l_insert_statement := 'insert into FUN_RULE_BULK_RESULT_GT(ID, RESULT_VALUE , RULE_NAME ,RESULT_APPLICATION_ID, RULE_DETAIL_ID ) ';
3050
3051 if(l_noRuleActive) then
3052
3053 setResultValues(l_isRuleValid,
3054 FALSE,
3055 l_rule_object_id,
3056 l_result_type,
3057 p_rule_object_name,
3058 l_flexfield_name,
3059 l_flexfield_app_short_name,
3060 l_rule_detail_id);
3061 else
3062
3063 setResultValues(l_isRuleValid,
3064 TRUE,
3065 l_rule_object_id,
3066 l_result_type,
3067 p_rule_object_name,
3068 l_flexfield_name,
3069 l_flexfield_app_short_name,
3070 l_rule_detail_id);
3071 end if;
3072
3073 l_select_query := l_select_query ||' '''||m_resultValue||''', '''||m_ruleName||''', '||NVL(m_resultApplicationId, FND_GLOBAL.resp_appl_id)||' , '||m_ruleDetailId||' FROM '||p_param_view_name||' WHERE 1=1 ';
3074 l_select_query := l_select_query || l_where_clause;
3075
3076 IF (p_additional_where_clause IS NOT NULL) THEN
3077 l_select_query := l_select_query || ' AND ' || p_additional_where_clause;
3078 END IF;
3079
3080 l_insert_statement := l_insert_statement || ' ' || l_select_query;
3081 IF (populateGTBulkTable(l_insert_statement)) THEN
3082 NULL;
3083 END IF;
3084
3085 /* bug 7337383 */
3086 -- end if;
3087
3088 DBMS_SQL.CLOSE_CURSOR(destination_cursor);
3089
3090 -- start bug 7385974
3091 IF DBMS_SQL.IS_OPEN(l_theCursor) THEN
3092 DBMS_SQL.CLOSE_CURSOR(l_theCursor);
3093 END IF;
3094 -- end bug 7385974
3095
3096 /*Rule Object Instance MOAC Changes:
3097 * Revert back the access mode and org id to the l_old_acess_mode and l_old_org_id
3098 * And Clear The Instance Context if set.
3099 */
3100
3101 IF (m_org_id IS NOT NULL) THEN
3102 FUN_RULE_MOAC_PKG.SET_MOAC_POLICY_CONTEXT(l_old_moac_access_mode , l_old_org_id , m_org_id);
3103 END IF;
3104 clearInstanceContext;
3105
3106 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
3107 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'End FUN_RULE_PUB.apply_rule_bulk', FALSE);
3108 end if;
3109
3110 EXCEPTION
3111 WHEN NO_DATA_FOUND THEN
3112 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3113 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION , 'FUN_RULE_PUB.apply_rule_bulk:->NO_DATA_FOUND', FALSE);
3114 END IF;
3115
3116 RAISE;
3117
3118 WHEN INVAILD_COLUMN_NAME THEN
3119 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3120 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION , 'FUN_RULE_PUB.apply_rule_bulk:->INVAILD_COLUMN_NAME', FALSE);
3121 END IF;
3122
3123 fnd_message.set_name('FUN','FUN_RULE_NO_VALID_COLUMNS');
3124 FND_MESSAGE.SET_TOKEN('TABLE_NAME', p_param_view_name);
3125 FND_MESSAGE.SET_TOKEN('RULE_OBJECT_NAME', p_rule_object_name);
3126 app_exception.raise_exception;
3127
3128 WHEN OTHERS THEN
3129 -- start bug 7385974
3130 IF DBMS_SQL.IS_OPEN(l_theCursor) THEN
3131 DBMS_SQL.CLOSE_CURSOR(l_theCursor);
3132 END IF;
3133 -- end bug 7385974
3134 IF DBMS_SQL.IS_OPEN(destination_cursor) THEN
3135 DBMS_SQL.CLOSE_CURSOR(destination_cursor);
3136 END IF;
3137 IF DBMS_SQL.IS_OPEN(params_cursor) THEN
3138 DBMS_SQL.CLOSE_CURSOR(params_cursor);
3139 END IF;
3140 IF DBMS_SQL.IS_OPEN(paramVal_cursor) THEN
3141 DBMS_SQL.CLOSE_CURSOR(paramVal_cursor);
3142 END IF;
3143 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3144 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION , 'FUN_RULE_PUB.apply_rule_bulk Others:->'||SQLERRM, FALSE);
3145 END IF;
3146
3147 RAISE;
3148
3149 END apply_rule_bulk;
3150
3151 PROCEDURE refreshGTBulkTable
3152 IS
3153 source_cursor INTEGER;
3154 ignore INTEGER;
3155
3156 BEGIN
3157 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
3158 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.refreshGTBulkTable', FALSE);
3159 end if;
3160
3161 -- Prepare a cursor to select from the source table:
3162 source_cursor := DBMS_SQL.OPEN_CURSOR;
3163
3164 DBMS_SQL.PARSE(source_cursor, 'delete from FUN_RULE_BULK_RESULT_GT', DBMS_SQL.native);
3165
3166 ignore := DBMS_SQL.EXECUTE(source_cursor);
3167
3168 DBMS_SQL.CLOSE_CURSOR(source_cursor);
3169
3170 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
3171 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.refreshGTBulkTable', FALSE);
3172 end if;
3173
3174 EXCEPTION
3175 WHEN NO_DATA_FOUND THEN
3176 IF DBMS_SQL.IS_OPEN(source_cursor) THEN
3177 DBMS_SQL.CLOSE_CURSOR(source_cursor);
3178 END IF;
3179 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3180 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION , 'FUN_RULE_PUB.refreshGTBulkTable:->'||SQLERRM, FALSE);
3181 END IF;
3182 --No Need of Raising in this case.
3183
3184 WHEN OTHERS THEN
3185 IF DBMS_SQL.IS_OPEN(source_cursor) THEN
3186 DBMS_SQL.CLOSE_CURSOR(source_cursor);
3187 END IF;
3188 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3189 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION , 'FUN_RULE_PUB.refreshGTBulkTable:->'||SQLERRM, FALSE);
3190 END IF;
3191 RAISE;
3192
3193 END refreshGTBulkTable;
3194
3195 FUNCTION populateGTBulkTable(p_insert_statement IN VARCHAR2) RETURN BOOLEAN
3196 IS
3197 destination_cursor INTEGER;
3198 ignore INTEGER;
3199
3200 BEGIN
3201 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
3202 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.populateGTBulkTable', FALSE);
3203 end if;
3204
3205 -- Prepare a cursor to insert into the destination table:
3206 destination_cursor := DBMS_SQL.OPEN_CURSOR;
3207
3208 DBMS_SQL.PARSE(destination_cursor,p_insert_statement,DBMS_SQL.native);
3209
3210 ignore := DBMS_SQL.EXECUTE(destination_cursor);
3211 DBMS_SQL.CLOSE_CURSOR(destination_cursor);
3212
3213 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
3214 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'End FUN_RULE_PUB.populateGTBulkTable', FALSE);
3215 end if;
3216
3217 IF(ignore > 0) THEN RETURN TRUE;
3218 ELSE RETURN FALSE;
3219 END IF;
3220
3221 EXCEPTION
3222 WHEN OTHERS THEN
3223 IF DBMS_SQL.IS_OPEN(destination_cursor) THEN
3224 DBMS_SQL.CLOSE_CURSOR(destination_cursor);
3225 END IF;
3226 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3227 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION , 'FUN_RULE_PUB.populateGTBulkTable:->'||p_insert_statement, FALSE);
3228 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION , 'FUN_RULE_PUB.populateGTBulkTable:->'||SQLERRM, FALSE);
3229 END IF;
3230
3231 RAISE;
3232
3233 END populateGTBulkTable;
3234
3235 PROCEDURE TEST
3236 IS
3237 CURSOR C1 IS SELECT * FROM FUN_RULE_BULK_RESULT_GT;
3238 BEGIN
3239 for c_rec in c1 loop
3240 --dbms_output.put_line('rule_name='||c_rec.rule_name||'***result_value='||c_rec.result_value);
3241 NULL;
3242 end loop;
3243 END TEST;
3244
3245 FUNCTION isRuleValid(p_param_view_name in varchar2, l_where_clause IN VARCHAR2) RETURN BOOLEAN
3246 IS
3247 source_cursor INTEGER;
3248 l_num_rows_processed INTEGER;
3249 l_num NUMBER;
3250 x_where_clause VARCHAR2(20000) := l_where_clause;
3251
3252 l_select VARCHAR2(20000) := 'SELECT COUNT(1) FROM '|| p_param_view_name ||' WHERE ';
3253 l_present BOOLEAN := FALSE;
3254 BEGIN
3255
3256 IF (x_where_clause IS NOT NULL) THEN
3257 l_select := l_select || ' ' || x_where_clause || ' ';
3258 ELSE
3259 l_select := l_select || ' 1 = 1';
3260 END IF;
3261
3262
3263 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
3264 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.isRuleValid', FALSE);
3265 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.isRuleValid:->p_param_view_name='||p_param_view_name, FALSE);
3266 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.isRuleValid:->l_where_clause'||x_where_clause, FALSE);
3267 end if;
3268
3269 source_cursor := DBMS_SQL.OPEN_CURSOR;
3270 DBMS_SQL.PARSE(source_cursor, l_select, DBMS_SQL.native);
3271
3272 DBMS_SQL.DEFINE_COLUMN(source_cursor, 1, l_num);
3273 l_num_rows_processed := DBMS_SQL.EXECUTE(source_cursor);
3274
3275 /*IF its not a valid SQL statement, then here it goes to Exception
3276 *Else return TRUE.
3277 */
3278 IF DBMS_SQL.FETCH_ROWS(source_cursor)>0 THEN
3279 -- get column values of the row
3280 DBMS_SQL.COLUMN_VALUE(source_cursor, 1, l_num);
3281 IF(l_num > 0) THEN
3282 l_present := true;
3283 END IF;
3284 END IF;
3285
3286 DBMS_SQL.CLOSE_CURSOR(source_cursor);
3287 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
3288 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'End FUN_RULE_PUB.isRuleValid', FALSE);
3289 end if;
3290
3291 RETURN l_present;
3292
3293 EXCEPTION
3294 WHEN OTHERS THEN
3295 IF DBMS_SQL.IS_OPEN(source_cursor) THEN
3296 DBMS_SQL.CLOSE_CURSOR(source_cursor);
3297 END IF;
3298 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3299 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION , 'FUN_RULE_PUB.isRuleValid:->'||SQLERRM, FALSE);
3300 END IF;
3301 RAISE;
3302
3303 END isRuleValid;
3304
3305 FUNCTION GET_ALL_RULE_NAMES RETURN VARCHAR2
3306 IS
3307 results FUN_RULE_RESULTS_TABLE := fun_Rule_Results_Table();
3308 l_rule_name VARCHAR2(200);
3309 l_concatenated_rules_name VARCHAR2(2000) := '';
3310 BEGIN
3311 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
3312 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'End FUN_RULE_PUB.GET_ALL_RULE_NAMES', FALSE);
3313 end if;
3314
3315 IF(m_multiRuleResultFlag = 'Y') THEN
3316 results := FUN_RULE_PUB.get_multi_rule_results_table;
3317 for i in 1..results.COUNT loop
3318 l_rule_name := results(i).get_rule_name;
3319 l_concatenated_rules_name := l_concatenated_rules_name || l_rule_name || ' ; ';
3320 end loop;
3321 return l_concatenated_rules_name;
3322 ELSE
3323 return m_ruleName;
3324 END IF;
3325 END GET_ALL_RULE_NAMES;
3326
3327 /*Clears the instance context*/
3328 PROCEDURE clearInstanceContext IS
3329 BEGIN
3330
3331 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
3332 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.clearInstanceContext', FALSE);
3333 end if;
3334
3335 IF (NVL(m_instance_context , 'N') = 'Y') THEN
3336 m_instance_label := NULL;
3337 m_org_id := NULL;
3338 m_instance_context := 'N';
3339 END IF;
3340
3341 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
3342 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'End FUN_RULE_PUB.clearInstanceContext', FALSE);
3343 end if;
3344 END clearInstanceContext;
3345
3346 END FUN_RULE_PUB;