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