[Home] [Help]
PACKAGE BODY: APPS.FEM_CONDITIONS_API
Source
1 PACKAGE BODY FEM_CONDITIONS_API AS
2 --$Header: FEMCONDB.pls 120.14.12010000.3 2008/12/12 02:00:51 huli ship $
3
4 --------------------------------------------------------------------------------
5 -- PRIVATE CONSTANTS
6 --------------------------------------------------------------------------------
7 G_FEM constant varchar2(3) := 'FEM';
8 G_PKG_NAME constant varchar2(30) := 'FEM_CONDITIONS_API';
9 G_BLOCK constant varchar2(80) := lower(G_FEM||'.PLSQL.'||G_PKG_NAME);
10
11 -- Log Level Constants
12 G_LOG_LEVEL_STATEMENT constant number := FND_LOG.Level_Statement; --1--
13 G_LOG_LEVEL_PROCEDURE constant number := FND_LOG.Level_Procedure; --2--
14 G_LOG_LEVEL_EVENT constant number := FND_LOG.Level_Event; --3--
15 G_LOG_LEVEL_EXCEPTION constant number := FND_LOG.Level_Exception; --4--
16 G_LOG_LEVEL_ERROR constant number := FND_LOG.Level_Error; --5--
17 G_LOG_LEVEL_UNEXPECTED constant number := FND_LOG.Level_Unexpected;--6--
18
19 --------------------------------------------------------------------------------
20 -- PACKAGE VARIABLES
21 --------------------------------------------------------------------------------
22 z_conditionObjectId NUMBER;
23 z_ruleEffectiveDate VARCHAR2(30);
24 z_factTableName VARCHAR2(30);
25 z_tableAlias VARCHAR2(30);
26 z_returnPredicateType VARCHAR2(10);
27 z_displayPredicate VARCHAR2(1);
28 z_loggingTurnedOn VARCHAR2(1);
29
30 z_dataStepValues DATA_STEP_VALUES_TAB;
31 z_dataStepValuesCtr BINARY_INTEGER;
32
33 z_dataStepPredicates DATA_DIM_STEP_PREDICATE_TAB;
34 z_dataStepPredicatesCtr BINARY_INTEGER;
35
36 z_invalidDataComponents INVALID_DATA_COMPONENTS_TAB;
37 z_invalidDataComponentsCtr BINARY_INTEGER;
38
39 z_dimStepPredicates DATA_DIM_STEP_PREDICATE_TAB;
40 z_dimStepPredicatesCtr BINARY_INTEGER;
41
42
43 z_errCode NUMBER;
44 z_errMsg VARCHAR2(250);
45
46 z_invalidDimComponents INVALID_DIM_COMPONENTS_TAB;
47 z_invalidDimComponentsCtr BINARY_INTEGER;
48
49 z_conditionObjectDefId NUMBER;
50 z_conditionObjectDefName VARCHAR2(150);
51 z_conditionObjectFolderName VARCHAR2(150);
52
53 z_componentObjectDefName VARCHAR2(150);
54 z_componentObjectFolderName VARCHAR2(150);
55
56 --------------------------------------------------------------------------------
57 -- PRIVATE FUNCTIONS AND PROCEDURES
58 --------------------------------------------------------------------------------
59
60 FUNCTION getHierValueSetId (
61 p_dimension_id in number
62 ,p_hierarchy_obj_id in number
63 ) RETURN number;
64
65 FUNCTION getDimensionValueSetId (
66 p_dimension_id in number
67 ,p_cond_component_obj_def_id in number
68 ) RETURN number;
69
70 FUNCTION getDimHierQuery (
71 p_hierarchy_table_name in varchar2
72 ,p_relation_code in varchar2
73 ,p_hierarchy_obj_def_id in number
74 ,p_node_list in varchar2
75 ,p_value_set_id in number
76 ,p_effective_date_varchar in varchar2
77 ) RETURN varchar2;
78
79 FUNCTION getDimFlatHierQuery (
80 p_hierarchy_table_name in varchar2
81 ,p_relation_code in varchar2
82 ,p_hierarchy_obj_def_id in number
83 ,p_node_list in varchar2
84 ,p_value_set_id in number
85 ) RETURN varchar2;
86
87
88
89 /*************************************************************************
90
91 tableNameIsValid
92
93 Private function used to validate a table against registered tables
94 in FEM_TABLES_V. (Only use enabled tables)
95
96 *************************************************************************/
97
98 FUNCTION tableNameIsValid(p_tableName IN VARCHAR2) RETURN BOOLEAN IS
99
100 CURSOR getTableName IS
101 SELECT TABLE_NAME
102 FROM FEM_TABLES_V
103 WHERE TABLE_NAME = p_tableName;
104
105 l_tableName VARCHAR2(30);
106
107 BEGIN
108
109 IF (p_tableName = 'FEM_ACTIVITIES' OR p_tableName = 'FEM_COST_OBJECTS') THEN
110 RETURN TRUE;
111 ELSE
112
113 OPEN getTableName;
114 FETCH getTableName INTO l_tableName;
115 IF (getTableName%NOTFOUND) THEN
116 RETURN FALSE;
117 ELSE
118 RETURN TRUE;
119 END IF;
120 CLOSE getTableName;
121 END IF;
122 END tableNameIsValid;
123
124
125
126
127
128 /*************************************************************************
129
130 columnNameIsValid
131
132 Private function used to validate a column on a given table
133 against registered tables in FEM_TAB_COLUMNS_V. (Only used enabled columns)
134
135 *************************************************************************/
136
137 FUNCTION columnNameIsValid(
138 p_tableName IN VARCHAR2,
139 p_columnName IN VARCHAR2,
140 x_isDimension OUT NOCOPY BOOLEAN
141 ) RETURN BOOLEAN IS
142
143 CURSOR getColumnName IS
144 SELECT COLUMN_NAME,FEM_DATA_TYPE_CODE
145 FROM FEM_TAB_COLUMNS_V
146 WHERE TABLE_NAME = p_tableName
147 AND COLUMN_NAME = p_columnName;
148
149 CURSOR getCompDimColumnName IS
150 SELECT COLUMN_NAME
151 FROM ALL_TAB_COLUMNS
152 WHERE TABLE_NAME = p_tableName
153 AND COLUMN_NAME = p_columnName
154 AND OWNER = FEM_APP;
155
156 l_columnName VARCHAR2(30);
157 l_dataTypeCode VARCHAR2(30);
158
159 BEGIN
160 IF (p_tableName = 'FEM_ACTIVITIES' OR p_tableName = 'FEM_COST_OBJECTS') THEN
161 OPEN getCompDimColumnName;
162 FETCH getCompDimColumnName INTO l_columnName;
163 IF (getCompDimColumnName%NOTFOUND) THEN
164 RETURN FALSE;
165 ELSE
166 RETURN TRUE;
167 END IF;
168 CLOSE getCompDimColumnName;
169 ELSE
170
171 OPEN getColumnName;
172 FETCH getColumnName INTO l_columnName,l_dataTypeCode;
173 IF (getColumnName%NOTFOUND) THEN
174 RETURN FALSE;
175 ELSE
176 IF (l_dataTypeCode = 'DIMENSION') THEN
177 x_isDimension := TRUE;
178 ELSE
179 x_isDimension := FALSE;
180 END IF;
181
182 RETURN TRUE;
183 END IF;
184
185 CLOSE getColumnName;
186 END IF;
187 END columnNameIsValid;
188
189
190
191 FUNCTION columnNameIsValid(
192 p_tableName IN VARCHAR2,
193 p_columnName IN VARCHAR2
194 ) RETURN BOOLEAN IS
195
196 l_isDimension BOOLEAN;
197 l_isValidColumn BOOLEAN;
198
199 BEGIN
200 l_isValidColumn := columnNameIsValid(p_tableName, p_columnName,l_isDimension);
201 RETURN l_isValidColumn;
202 END;
203 /*************************************************************************
204
205 compDimColumnNameIsValid
206
207 Private function used to validate a composite dimension column in
208 FEM_COLUMN_REQUIREMNT_VL
209
210 *************************************************************************/
211
212 FUNCTION compDimColumnNameIsValid(
213 p_tableName IN VARCHAR2,
214 p_columnName IN VARCHAR2) RETURN BOOLEAN IS
215
216
217 CURSOR getCostObjColumnName IS
218 SELECT COLUMN_NAME
219 FROM FEM_COLUMN_REQUIREMNT_VL
220 WHERE COST_OBJ_DIM_REQUIREMENT_CODE IS NOT NULL
221 AND COST_OBJ_DIM_COMPONENT_FLAG = 'Y'
222 AND COLUMN_NAME = p_columnName;
223
224 CURSOR getActivityColumnName IS
225 SELECT COLUMN_NAME
226 FROM FEM_COLUMN_REQUIREMNT_VL
227 WHERE ACTIVITY_DIM_REQUIREMENT_CODE IS NOT NULL
228 AND ACTIVITY_DIM_COMPONENT_FLAG = 'Y'
229 AND COLUMN_NAME = p_columnName;
230
231 l_columnName VARCHAR2(30);
232
233 BEGIN
234 IF (p_tableName = 'FEM_ACTIVITIES') THEN
235 IF (p_columnName = 'ACTIVITY_ID') THEN
236 RETURN TRUE;
237 ELSE
238 OPEN getActivityColumnName;
239 FETCH getActivityColumnName INTO l_columnName;
240 IF (getActivityColumnName%NOTFOUND) THEN
241 RETURN FALSE;
242 ELSE
243 RETURN TRUE;
244 END IF;
245 CLOSE getActivityColumnName;
246 END IF;
247
248 ELSIF (p_tableName = 'FEM_COST_OBJECTS') THEN
249 IF (p_columnName = 'COST_OBJECT_ID') THEN
250 RETURN TRUE;
251 ELSE
252 OPEN getCostObjColumnName ;
253 FETCH getCostObjColumnName INTO l_columnName;
254 IF (getCostObjColumnName%NOTFOUND) THEN
255 RETURN FALSE;
256 ELSE
257 RETURN TRUE;
258 END IF;
259 CLOSE getCostObjColumnName;
260 END IF;
261 END IF;
262 END compDimColumnNameIsValid;
263
264 /*************************************************************************
265
266 getColumnDataType
267
268
269
270 *************************************************************************/
271
272 FUNCTION getColumnDataType(
273 p_synonymName IN VARCHAR2,
274 p_columnName IN VARCHAR2,
275 p_isDimension IN BOOLEAN,
276 p_dimensionId IN NUMBER) RETURN VARCHAR2 IS
277
278
279
280
281 l_returnStatus VARCHAR2(1);
282 l_msgCount NUMBER;
283 l_msgData VARCHAR2(240);
284
285
286 l_schemaName ALL_SYNONYMS.TABLE_OWNER%TYPE;
287 l_tableName ALL_SYNONYMS.TABLE_NAME%TYPE;
288 l_dataType ALL_TAB_COLUMNS.DATA_TYPE%TYPE;
289
290 BEGIN
291
292 IF (p_isDimension) THEN
293 SELECT DECODE (MEMBER_DATA_TYPE_CODE, 'NUMBER', 'NUMBER', 'DATE', 'DATE', 'STRING')
294 INTO l_dataType
295 FROM FEM_XDIM_DIMENSIONS
296 WHERE DIMENSION_ID = p_dimensionId;
297
298 /*TO DO: handle exceptions*/
299
300 ELSE
301
302 FEM_DATABASE_UTIL_PKG.Get_Table_Owner(
303 p_api_version => 1.0,
304 p_commit => FND_API.G_FALSE,
305 p_init_msg_list => FND_API.G_TRUE,
306 p_encoded => FND_API.G_TRUE,
307 x_return_status => l_returnStatus,
308 x_msg_count => l_msgCount,
309 x_msg_data => l_msgData,
310 p_syn_name => p_synonymName,
311 x_tab_name => l_tableName,
312 x_tab_owner => l_schemaName);
313
314
315 /*TO DO: handle exceptions*/
316
317
318 SELECT DECODE (DATA_TYPE, 'NUMBER', 'NUMBER', 'DATE', 'DATE', 'STRING')
319 INTO l_dataType
320 FROM ALL_TAB_COLUMNS
321 WHERE TABLE_NAME = l_tableName
322 AND COLUMN_NAME = p_columnName
323 AND OWNER = l_schemaName;
324
325 /*TO DO: handle exceptions*/
326 END IF;
327
328 RETURN l_dataType;
329 END getColumnDataType;
330
331 /*************************************************************************
332
333 operatorValue
334
335 Private function that returns the meaning of a operator code as stored
336 in FND_LOOKUP_VALUES.
337
338 *************************************************************************/
339
340 FUNCTION operatorValue(p_operatorCode IN VARCHAR2) RETURN VARCHAR2 IS
341
342 l_operatorValue VARCHAR2(30);
343
344 BEGIN
345 l_operatorValue :=
346 CASE p_operatorCode
347 WHEN 'GREATER' THEN G_GREATER
348 WHEN 'LESSER' THEN G_LESSER
349 WHEN 'EQUAL' THEN G_EQUAL
350 WHEN 'GREATER_EQUAL' THEN G_GREATER_EQUAL
351
352 WHEN 'LESSER_EQUAL' THEN G_LESSER_EQUAL
353 WHEN 'NOT_EQUAL' THEN G_NOT_EQUAL
354 WHEN 'BETWEEN' THEN G_BETWEEN
355 WHEN 'NOT_BETWEEN' THEN G_NOT_BETWEEN
356 ELSE '[Invalid Operator Code]'
357 END;
358
359 RETURN l_operatorValue;
360 END operatorValue;
361
362
363
364 /*************************************************************************
365
366 getValidComponentSql
367
371 *************************************************************************/
368 Private function which retrieves the sql statement for obtaining the
369 dimension and/or data components of a condition.
370
372
373 FUNCTION getValidComponentSql(
374 p_returnPredicateType IN VARCHAR2,
375 p_displayPredicate IN VARCHAR2,
376 p_inputFactTable IN VARCHAR2,
377 x_numOfBindVariables OUT NOCOPY NUMBER) RETURN LONG IS
378
379 l_dataCmpSql LONG;
380 l_sqlStmt LONG;
381 l_intermediateDimStmt LONG;
382
383 BEGIN
384
385 IF (p_returnPredicateType = 'DATA') THEN
386 IF (p_displayPredicate = 'Y') THEN
387 l_sqlStmt := G_QUERY_FOR_DISPLAY_DATA_CMPS;
388 x_numOfBindVariables := 1;
389 ELSIF (p_displayPredicate = 'N') THEN
390 l_sqlStmt := G_QUERY_FOR_DATA_CMPS;
391 x_numOfBindVariables := 2;
392 END IF;
393
394 ELSIF (p_returnPredicateType = 'DIM') THEN
395 l_sqlStmt := G_QUERY_FOR_DIM_CMPS;
396 x_numOfBindVariables := 1;
397
398 ELSIF (p_returnPredicateType = 'BOTH') THEN
399 IF (p_displayPredicate = 'Y') THEN
400 l_sqlStmt := G_QUERY_FOR_DISPLAY_DATA_CMPS
401 ||G_UNION
402 ||G_QUERY_FOR_DIM_CMPS;
403 x_numOfBindVariables := 2;
404
405 ELSIF (p_displayPredicate = 'N') THEN
406 l_sqlStmt := G_QUERY_FOR_DATA_CMPS
407 ||G_UNION
408 ||G_QUERY_FOR_DIM_CMPS;
409 x_numOfBindVariables := 3;
410 END IF;
411 END IF;
412
413 RETURN l_sqlStmt ;
414
415 END getValidComponentSql;
416
417
418 /*************************************************************************
419
420 openConditionCursor
421
422 This procedure opens the cursor to fetch all valid components of Condition
423 based on the type of component (Data or Dimension) being processed
424
425 *************************************************************************/
426
427 PROCEDURE openConditionCursor(
428 x_compCv OUT NOCOPY COMP_CUR_TYPE,
429 p_sqlStmt IN LONG,
430 p_numOfBindVariables IN NUMBER,
431 p_conditionObjDefId IN NUMBER,
432 p_displayPredicate IN VARCHAR2,
433 p_factTableName IN VARCHAR2) IS
434
435 BEGIN
436
437 IF (p_numOfBindVariables = 1) THEN
438 OPEN x_compCv FOR p_sqlStmt USING
439 p_conditionObjDefId;
440
441 ELSIF (p_numOfBindVariables = 2) THEN
442 IF (p_displayPredicate = 'Y') THEN
443 OPEN x_compCv FOR p_sqlStmt USING
444 p_conditionObjDefId,
445 p_conditionObjDefId;
446 ELSIF (p_displayPredicate = 'N') THEN
447 OPEN x_compCv FOR p_sqlStmt USING
448 p_conditionObjDefId,
449 p_factTableName;
450 END IF;
451
452 ELSIF (p_numOfBindVariables = 3) THEN
453 OPEN x_compCv FOR p_sqlStmt USING
454 p_conditionObjDefId,
455 p_factTableName,
456 p_conditionObjDefId;
457 END IF;
458
459 END openConditionCursor;
460
461
462 /*************************************************************************
463
464 validateHierarchyNode
465
466 Private function used for validating the existance of a specific node on
467 a given hierarchy definition.
468
469 *************************************************************************/
470
471 FUNCTION validateHierarchyNode(p_tableName IN VARCHAR2,
472 p_node IN NUMBER,
473 p_value_set_id IN NUMBER,
474 p_objId IN NUMBER,
475 p_objDefId IN NUMBER,
476 p_effectiveDate IN VARCHAR2) RETURN BOOLEAN IS
477
478 l_sql VARCHAR2(1000);
479
480 TYPE HIERARCHY_CUR_TYPE IS REF CURSOR;
481 hierarchyCursor HIERARCHY_CUR_TYPE;
482
483 l_childId NUMBER;
484 l_nodeExists BOOLEAN;
485
486 l_value_set_string varchar2(2000);
487
488 BEGIN
489
490 IF (p_tableName = 'FEM_COST_OBJECTS_HIER') THEN
491 l_sql := 'SELECT CHILD_ID '
492 ||'FROM ' || p_tableName
493 ||' WHERE CHILD_ID = :1 AND hierarchy_obj_id = :2 AND TO_DATE('''||p_effectiveDate||''',''YYYY/MM/DD HH24:MI:SS'') BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE'
494 ||' UNION SELECT PARENT_ID '
495 ||'FROM ' || p_tableName
496 ||' WHERE PARENT_ID = :3 AND hierarchy_obj_id = :4 AND TO_DATE('''||p_effectiveDate||''',''YYYY/MM/DD HH24:MI:SS'') BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE';
497
498 OPEN hierarchyCursor FOR l_sql USING p_node, p_objId, p_node, p_objId;
499
500 ELSE
501
502 l_value_set_string := null;
503
504 if (p_value_set_id is not null) then
505
506 l_value_set_string := ' AND CHILD_VALUE_SET_ID = '||p_value_set_id;
507
508 end if;
509
510 l_sql :=
511 ' SELECT CHILD_ID '
512 ||' FROM ' || p_tableName
513 ||' WHERE CHILD_ID = :1 AND hierarchy_obj_def_id = :2'
514 || l_value_set_string;
515
516 OPEN hierarchyCursor FOR l_sql USING p_node, p_objDefId;
517
518 END IF;
519
523
520 --OPEN hierarchyCursor FOR l_sql USING p_node, p_objDefId;
521
522 FETCH hierarchyCursor INTO l_childId;
524 IF hierarchyCursor%NOTFOUND THEN
525 l_nodeExists := FALSE;
526 ELSE
527 l_nodeExists := TRUE;
528 END IF;
529
530 CLOSE hierarchyCursor;
531
532 RETURN l_nodeExists;
533
534 END validateHierarchyNode;
535
536
537 /*************************************************************************
538
539 specificValueStep
540
541 Private procedure which generates the predicate for a Data Component Step
542 of type 'Specific Value'
543
544 *************************************************************************/
545
546 PROCEDURE specificValueStep(
547 p_tableName IN VARCHAR2,
548 p_tableAlias IN VARCHAR2,
549 p_columnName IN VARCHAR2,
550 p_isDimension IN BOOLEAN,
551 x_stepSpecificString OUT NOCOPY LONG) IS
552
553 l_columnPrefix VARCHAR2(30);
554 l_stepSpecificString LONG;
555 --l_dataStepValuesCount NUMBER := z_dataStepValues.COUNT;
556
557 --3/23/2006
558 l_isDimension BOOLEAN;
559 l_sqlStmt VARCHAR2(500);
560 l_displayCodeCol FEM_XDIM_DIMENSIONS.MEMBER_DISPLAY_CODE_COL%TYPE;
561 l_memberTableName FEM_XDIM_DIMENSIONS.MEMBER_B_TABLE_NAME%TYPE;
562 l_displayCodeVal FEM_LEDGERS_B.LEDGER_DISPLAY_CODE%TYPE;
563 l_displayCodeSQL VARCHAR2(500);
564
565 BEGIN
566
567 IF (p_tableAlias IS NULL) THEN
568 l_columnPrefix := NULL;
569 ELSE
570 l_columnPrefix := p_tableAlias||G_PERIOD;
571 END IF;
572
573 FOR i IN z_dataStepValues.FIRST..z_dataStepValues.LAST LOOP
574
575 --3/23/06 NEW LOGIC HERE
576 IF (z_dataStepValues(i).OPERATOR_CODE IN ('GREATER','GREATER_EQUAL','LESSER','LESSER_EQUAL')) THEN
577 --if column is a dimension column then query fem_xdim_dimensions to get display code column name and member table name
578 IF (p_isDimension) THEN
579
580 SELECT MEMBER_DISPLAY_CODE_COL, MEMBER_B_TABLE_NAME
581 INTO l_displayCodeCol, l_memberTableName
582 FROM FEM_XDIM_DIMENSIONS
583 WHERE MEMBER_COL = p_columnName;
584
585 --only proceed with obtaining display codes if the display code column is different from member column;
586 --ie. display code col exists on member table
587 IF (l_displayCodeCol <> p_columnName) THEN
588 --get display codes for selected value
589 l_sqlStmt := 'SELECT '|| l_displayCodeCol||' FROM '||l_memberTableName||' WHERE '||p_columnName||' = :1';
590
591 EXECUTE IMMEDIATE l_sqlStmt
592 INTO l_displayCodeVal
593 USING z_dataStepValues(i).VALUE;
594
595 --build data step predicate using display codes
596
597 l_displayCodeSQL:= 'SELECT '||p_columnName||
598 ' FROM '||l_memberTableName||
599 ' WHERE '||l_displayCodeCol||G_SPACE||z_dataStepValues(i).OPERATOR_VALUE||G_SPACE
600 ||''''||l_displayCodeVal||'''';
601
602 l_stepSpecificString := l_stepSpecificString
603 ||G_OPEN
604 ||l_columnPrefix||p_columnName||' IN '
605 ||G_OPEN||l_displayCodeSQL||G_CLOSE
606 ||G_CLOSE;
607
608 ELSE --DISPLAY CODE AND COLUMN NAME EQUAL
609 l_stepSpecificString := l_stepSpecificString
610 ||G_OPEN
611 ||l_columnPrefix||p_columnName
612 ||G_SPACE||z_dataStepValues(i).OPERATOR_VALUE||G_SPACE
613 ||z_dataStepValues(i).VALUE
614 ||G_CLOSE;
615
616 END IF;
617
618 ELSE --IF NOT A DIMENSION
619
620 l_stepSpecificString := l_stepSpecificString
621 ||G_OPEN
622 ||l_columnPrefix||p_columnName
623 ||G_SPACE||z_dataStepValues(i).OPERATOR_VALUE||G_SPACE
624 ||z_dataStepValues(i).VALUE
625 ||G_CLOSE;
626 END IF;
627
628
629 IF (i < z_dataStepValues.LAST) THEN
630 l_stepSpecificString := l_stepSpecificString||G_SPACE||G_OR||G_SPACE;
631 END IF;
632
633
634 ELSIF (z_dataStepValues(i).OPERATOR_CODE = 'EQUAL') THEN
635
636 l_stepSpecificString := l_stepSpecificString
637 ||G_OPEN
638 ||l_columnPrefix||p_columnName
639 ||G_SPACE||z_dataStepValues(i).OPERATOR_VALUE||G_SPACE
640 ||z_dataStepValues(i).VALUE
641 ||G_CLOSE;
642
643 /* IF (i = z_dataStepValues.FIRST) THEN
644 l_stepSpecificString := l_stepSpecificString
645 ||l_columnPrefix||p_columnName
646 ||G_SPACE||z_dataStepValues(i).OPERATOR_VALUE||G_SPACE
647 ||z_dataStepValues(i).VALUE;
648 ELSE
649 l_stepSpecificString := l_stepSpecificString
650 ||G_OPEN
651 ||l_columnPrefix||p_columnName
652 ||G_SPACE||z_dataStepValues(i).OPERATOR_VALUE||G_SPACE
653 ||z_dataStepValues(i).VALUE
654 ||G_CLOSE;
655 END IF;*/
656 IF (i < z_dataStepValues.LAST) THEN
657 l_stepSpecificString := l_stepSpecificString||G_SPACE||G_OR||G_SPACE;
658 END IF;
659
660 ELSIF (z_dataStepValues(i).OPERATOR_CODE = 'NOT_EQUAL') THEN
661
662 l_stepSpecificString := l_stepSpecificString
663 ||G_OPEN
664 ||l_columnPrefix||p_columnName
665 ||G_SPACE||G_GREATER||G_SPACE
669 ||G_SPACE||G_LESSER||G_SPACE
666 ||z_dataStepValues(i).VALUE
667 ||G_SPACE||G_OR||G_SPACE
668 ||l_columnPrefix||p_columnName
670 ||z_dataStepValues(i).VALUE
671 ||G_CLOSE;
672
673 /*IF (i = z_dataStepValues.FIRST) THEN
674 l_stepSpecificString := l_stepSpecificString
675 ||l_columnPrefix||p_columnName
676 ||G_SPACE||G_GREATER||G_SPACE
677 ||z_dataStepValues(i).VALUE
678 ||G_SPACE||G_OR||G_SPACE
679 ||l_columnPrefix||p_columnName
680 ||G_SPACE||G_LESSER||G_SPACE
681 ||z_dataStepValues(i).VALUE;
682 ELSE
683 l_stepSpecificString := l_stepSpecificString
684 ||G_OPEN
685 ||l_columnPrefix||p_columnName
686 ||G_SPACE||G_GREATER||G_SPACE
687 ||z_dataStepValues(i).VALUE
688 ||G_SPACE||G_OR||G_SPACE
689 ||l_columnPrefix||p_columnName
690 ||G_SPACE||G_LESSER||G_SPACE
691 ||z_dataStepValues(i).VALUE
692 ||G_CLOSE;
693 END IF;*/
694 IF (i < z_dataStepValues.LAST) THEN
695 l_stepSpecificString := l_stepSpecificString||G_SPACE||G_AND||G_SPACE;
696 END IF;
697
698 /*ELSE --ERROR UNHANDLED OPERATOR
699 l_stepSpecificString := l_stepSpecificString
700 ||G_OPEN
701 ||l_columnPrefix||p_columnName
702 ||G_SPACE||z_dataStepValues(i).OPERATOR_VALUE||G_SPACE
703 ||z_dataStepValues(i).VALUE
704 ||G_CLOSE;
705
706 IF (i < z_dataStepValues.LAST) THEN
707 l_stepSpecificString := l_stepSpecificString||G_SPACE||G_OR||G_SPACE;
708 END IF;*/
709 END IF;
710
711 END LOOP;
712 --**l_stepSpecificString := G_OPEN||l_stepSpecificString||G_CLOSE;
713
714 x_stepSpecificString := l_stepSpecificString;
715
716 EXCEPTION
717 WHEN OTHERS THEN
718 z_errCode := -1;
719 z_errMsg := 'Procedure: specificValueStep: '||SQLERRM;
720 RAISE;
721
722 END specificValueStep;
723
724
725 /*************************************************************************
726
727 anotherColumnStep
728
729 Private procedure which generates the predicate for a Data Component Step
730 of type 'Another Column'
731
732
733 *************************************************************************/
734
735 PROCEDURE anotherColumnStep(
736 p_tableName IN VARCHAR2,
737 p_tableAlias IN VARCHAR2,
738 p_columnName IN VARCHAR2,
739 x_stepSpecificString OUT NOCOPY LONG) IS
740
741 l_columnPrefix VARCHAR2(30);
742 l_stepSpecificString LONG;
743
744 BEGIN
745
746 IF (p_tableAlias IS NULL) THEN
747 l_columnPrefix := NULL;
748 ELSE
749 l_columnPrefix := p_tableAlias||G_PERIOD;
750 END IF;
751
752 FOR i IN z_dataStepValues.FIRST..z_dataStepValues.LAST LOOP
753 IF (z_dataStepValues(i).OPERATOR_CODE <> 'NOT_EQUAL') THEN
754
755 l_stepSpecificString := l_stepSpecificString
756 ||G_OPEN
757 ||l_columnPrefix||p_columnName
758 ||G_SPACE||z_dataStepValues(i).OPERATOR_VALUE||G_SPACE
759 ||l_columnPrefix||z_dataStepValues(i).VALUE
760 ||G_CLOSE;
761
762 /* IF (i = z_dataStepValues.FIRST) THEN
763 l_stepSpecificString := l_stepSpecificString
764 ||l_columnPrefix||p_columnName
765 ||G_SPACE||z_dataStepValues(i).OPERATOR_VALUE||G_SPACE
766 ||l_columnPrefix||z_dataStepValues(i).VALUE;
767 ELSE
768 l_stepSpecificString := l_stepSpecificString
769 ||G_OPEN
770 ||l_columnPrefix||p_columnName
771 ||G_SPACE||z_dataStepValues(i).OPERATOR_VALUE||G_SPACE
772 ||l_columnPrefix||z_dataStepValues(i).VALUE
773 ||G_CLOSE;
774 END IF;*/
775
776 IF (i < z_dataStepValues.LAST) THEN
777 l_stepSpecificString := l_stepSpecificString||G_SPACE||G_OR||G_SPACE;
778 END IF;
779
780 ELSIF (z_dataStepValues(i).OPERATOR_CODE = 'NOT_EQUAL') THEN
781
782 l_stepSpecificString := l_stepSpecificString
783 ||G_OPEN
784 ||l_columnPrefix||p_columnName
785 ||G_SPACE||G_GREATER||G_SPACE
786 ||l_columnPrefix||z_dataStepValues(i).VALUE
787 ||G_SPACE||G_OR||G_SPACE
788 ||l_columnPrefix||p_columnName
789 ||G_SPACE||G_LESSER||G_SPACE
790 ||l_columnPrefix||z_dataStepValues(i).VALUE
791 ||G_CLOSE;
792
793 /*IF (i = z_dataStepValues.FIRST) THEN
794 l_stepSpecificString := l_stepSpecificString
795 ||l_columnPrefix||p_columnName
796 ||G_SPACE||G_GREATER||G_SPACE
797 ||l_columnPrefix||z_dataStepValues(i).VALUE
798 ||G_SPACE||G_OR||G_SPACE
799 ||l_columnPrefix||p_columnName
800 ||G_SPACE||G_LESSER||G_SPACE
801 ||l_columnPrefix||z_dataStepValues(i).VALUE;
802 ELSE
803 l_stepSpecificString := l_stepSpecificString
804 ||G_OPEN
805 ||l_columnPrefix||p_columnName
806 ||G_SPACE||G_GREATER||G_SPACE
807 ||l_columnPrefix||z_dataStepValues(i).VALUE
808 ||G_SPACE||G_OR||G_SPACE
809 ||l_columnPrefix||p_columnName
810 ||G_SPACE||G_LESSER||G_SPACE
811 ||l_columnPrefix||z_dataStepValues(i).VALUE
812 ||G_CLOSE;
813 END IF;*/
814
815 IF (i < z_dataStepValues.LAST) THEN
819 END IF;
816 l_stepSpecificString := l_stepSpecificString||G_SPACE||G_AND||G_SPACE;
817 END IF;
818
820 END LOOP;
821
822 x_stepSpecificString := l_stepSpecificString;
823
824 EXCEPTION
825 WHEN OTHERS THEN
826 z_errCode := -1;
827 z_errMsg := 'Procedure: anotherColumnStep: '||SQLERRM;
828 RAISE;
829
830 END anotherColumnStep;
831
832
833 /*************************************************************************
834
835 rangeStep
836
837 Private procedure which generates the predicate for a Data Component Step
838 of type 'Range Value'
839
840
841 *************************************************************************/
842
843 PROCEDURE rangeStep(
844 p_tableName IN VARCHAR2,
845 p_tableAlias IN VARCHAR2,
846 p_columnName IN VARCHAR2,
847 p_isDimension IN BOOLEAN,
848 x_stepSpecificString OUT NOCOPY LONG) IS
849
850 l_columnPrefix VARCHAR2(30);
851 l_stepSpecificString LONG;
852 --l_dataStepValuesCount NUMBER := z_dataStepValues.COUNT;
853
854 --3/23/2006
855 l_isDimension BOOLEAN;
856 l_sqlStmt VARCHAR2(500);
857 l_displayCodeCol FEM_XDIM_DIMENSIONS.MEMBER_DISPLAY_CODE_COL%TYPE;
858 l_memberTableName FEM_XDIM_DIMENSIONS.MEMBER_B_TABLE_NAME%TYPE;
859 l_minRangeDisplayCode FEM_LEDGERS_B.LEDGER_DISPLAY_CODE%TYPE;
860 l_maxRangeDisplayCode FEM_LEDGERS_B.LEDGER_DISPLAY_CODE%TYPE;
861 l_displayCodeSQL VARCHAR2(500);
862
863 BEGIN
864
865 IF (p_tableAlias IS NULL) THEN
866 l_columnPrefix := NULL;
867 ELSE
868 l_columnPrefix := p_tableAlias||G_PERIOD;
869 END IF;
870
871 FOR i IN z_dataStepValues.FIRST..z_dataStepValues.LAST LOOP
872
873 --3/23/06 NEW LOGIC HERE
874 --if column is a dimension column then query fem_xdim_dimensions to get display code column name and member table name
875 IF (p_isDimension) THEN
876
877 SELECT MEMBER_DISPLAY_CODE_COL, MEMBER_B_TABLE_NAME
878 INTO l_displayCodeCol, l_memberTableName
879 FROM FEM_XDIM_DIMENSIONS
880 WHERE MEMBER_COL = p_columnName;
881
882 --only proceed with obtaining display codes if the display code column is different from member column;
883 --ie. display code col exists on member table
884 IF (l_displayCodeCol <> p_columnName) THEN
885 --get display codes for min and max range values
886 l_sqlStmt := 'SELECT '|| l_displayCodeCol||' FROM '||l_memberTableName||' WHERE '||p_columnName||' = :1';
887
888 EXECUTE IMMEDIATE l_sqlStmt
889 INTO l_minRangeDisplayCode
890 USING z_dataStepValues(i).VALUE;
891
892 EXECUTE IMMEDIATE l_sqlStmt
893 INTO l_maxRangeDisplayCode
894 USING z_dataStepValues(i).MAX_RANGE_VALUE;
895
896 --build data step predicate using display codes
897
898 l_displayCodeSQL:= 'SELECT '||p_columnName||
899 ' FROM '||l_memberTableName||
900 ' WHERE '||l_displayCodeCol||G_SPACE||z_dataStepValues(i).OPERATOR_VALUE||G_SPACE
901 ||''''||l_minRangeDisplayCode||''''||' AND '||''''||l_maxRangeDisplayCode||'''';
902
903 l_stepSpecificString := l_stepSpecificString
904 ||G_OPEN
905 ||l_columnPrefix||p_columnName||' IN '
906 ||G_OPEN||l_displayCodeSQL||G_CLOSE
907 ||G_CLOSE;
908
909 ELSE
910 l_stepSpecificString := l_stepSpecificString
911 ||G_OPEN
912 ||l_columnPrefix||p_columnName
913 ||G_SPACE||z_dataStepValues(i).OPERATOR_VALUE||G_SPACE
914 ||z_dataStepValues(i).VALUE
915 ||G_SPACE||G_AND||G_SPACE
916 ||z_dataStepValues(i).MAX_RANGE_VALUE
917 ||G_CLOSE;
918
919 END IF;
920
921 ELSE --IF NOT A DIMENSION
922
923 l_stepSpecificString := l_stepSpecificString
924 ||G_OPEN
925 ||l_columnPrefix||p_columnName
926 ||G_SPACE||z_dataStepValues(i).OPERATOR_VALUE||G_SPACE
927 ||z_dataStepValues(i).VALUE
928 ||G_SPACE||G_AND||G_SPACE
929 ||z_dataStepValues(i).MAX_RANGE_VALUE
930 ||G_CLOSE;
931 END IF;
932
933
934 IF (z_dataStepValues(i).OPERATOR_CODE = 'BETWEEN') THEN
935
936 /*l_stepSpecificString := l_stepSpecificString
937 ||G_OPEN
938 ||l_columnPrefix||p_columnName
939 ||G_SPACE||z_dataStepValues(i).OPERATOR_VALUE||G_SPACE
940 ||z_dataStepValues(i).VALUE
941 ||G_SPACE||G_AND||G_SPACE
942 ||z_dataStepValues(i).MAX_RANGE_VALUE
943 ||G_CLOSE;*/
944
945 IF (i < z_dataStepValues.LAST) THEN
946 l_stepSpecificString := l_stepSpecificString||G_SPACE||G_OR||G_SPACE;
947 END IF;
948
949 ELSIF (z_dataStepValues(i).OPERATOR_CODE = 'NOT_BETWEEN') THEN
950
951 /*l_stepSpecificString := l_stepSpecificString
952 ||G_OPEN
953 ||l_columnPrefix||p_columnName
954 ||G_SPACE||z_dataStepValues(i).OPERATOR_VALUE||G_SPACE
955 ||z_dataStepValues(i).VALUE
956 ||G_SPACE||G_AND||G_SPACE
960 IF (i < z_dataStepValues.LAST) THEN
957 ||z_dataStepValues(i).MAX_RANGE_VALUE
958 ||G_CLOSE;*/
959
961 l_stepSpecificString := l_stepSpecificString||G_SPACE||G_AND||G_SPACE;
962 END IF;
963
964 /*ELSE --ERROR -- UNHANDLED OPERATOR
965 l_stepSpecificString := l_stepSpecificString
966 ||G_OPEN
967 ||l_columnPrefix||p_columnName
968 ||G_SPACE||z_dataStepValues(i).OPERATOR_VALUE||G_SPACE
969 ||z_dataStepValues(i).VALUE
970 ||G_CLOSE;
971
972 IF (i < z_dataStepValues.LAST) THEN
973 l_stepSpecificString := l_stepSpecificString||G_SPACE||G_OR||G_SPACE;
974 END IF;*/
975 END IF;
976
977 END LOOP;
978
979 --** l_stepSpecificString := G_OPEN||l_stepSpecificString||G_CLOSE;
980 x_stepSpecificString := l_stepSpecificString;
981
982 EXCEPTION
983 WHEN OTHERS THEN
984 z_errCode := -1;
985 z_errMsg := 'Procedure: rangeStep: '||SQLERRM;
986 RAISE;
987
988 END rangeStep;
989
990
991 /*************************************************************************
992
993 logPredicateToOutputFile
994
995 Private procedure used to log the Conditions predicate to an output file.
996
997 *************************************************************************/
998
999 PROCEDURE logPredicateToOutputFile(
1000 p_conditionObjId IN DEF_OBJECT_ID%TYPE,
1001 p_returnPredicateType IN VARCHAR2) IS
1002
1003 BEGIN
1004
1005 FND_FILE.put_line(FND_FILE.OUTPUT, 'Condition Name: '||z_conditionObjectDefName);
1006 FND_FILE.put_line(FND_FILE.OUTPUT, '');
1007
1008 IF (p_returnPredicateType = 'DATA') THEN
1009
1010 IF (z_dataStepPredicates.COUNT > 0) THEN
1011 FOR i IN z_dataStepPredicates.FIRST..z_dataStepPredicates.LAST LOOP
1012 IF (z_dataStepPredicates(i).Step_Specific_Predicate IS NOT NULL) THEN
1013 FND_FILE.put_line(FND_FILE.OUTPUT
1014 ,z_dataStepPredicates(i).Step_Specific_Predicate);
1015 IF (i < z_dataStepPredicates.LAST) THEN
1016 FND_FILE.put_line(FND_FILE.OUTPUT,G_SPACE||G_AND||G_SPACE);
1017 END IF;
1018 END IF;
1019 END LOOP;
1020 END IF;
1021
1022 ELSIF (p_returnPredicateType = 'DIM') THEN
1023
1024 IF (z_dimStepPredicates.COUNT > 0) THEN
1025 FOR i IN z_dimStepPredicates.FIRST..z_dimStepPredicates.LAST LOOP
1026 IF (z_dimStepPredicates(i).Step_Specific_Predicate IS NOT NULL) THEN
1027 FND_FILE.put_line(FND_FILE.OUTPUT
1028 ,z_dimStepPredicates(i).Step_Specific_Predicate);
1029 IF (i < z_dimStepPredicates.LAST) THEN
1030 FND_FILE.put_line(FND_FILE.OUTPUT,G_SPACE||G_AND||G_SPACE);
1031 END IF;
1032 END IF;
1033 END LOOP;
1034 END IF;
1035
1036 ELSIF (p_returnPredicateType = 'BOTH') THEN
1037
1038 IF (z_dataStepPredicates.COUNT > 0) THEN
1039 FOR i IN z_dataStepPredicates.FIRST..z_dataStepPredicates.LAST LOOP
1040 IF (z_dataStepPredicates(i).Step_Specific_Predicate IS NOT NULL) THEN
1041 FND_FILE.put_line(FND_FILE.OUTPUT
1042 ,z_dataStepPredicates(i).Step_Specific_Predicate);
1043 IF (i < z_dataStepPredicates.LAST) THEN
1044 FND_FILE.put_line(FND_FILE.OUTPUT,G_SPACE||G_AND||G_SPACE);
1045 END IF;
1046 END IF;
1047 END LOOP;
1048 END IF;
1049
1050 IF (z_dimStepPredicates.COUNT > 0) THEN
1051 IF (z_dataStepPredicates.COUNT > 0) THEN
1052 FND_FILE.put_line(FND_FILE.OUTPUT,G_SPACE||G_AND||G_SPACE);
1053 END IF;
1054 FOR i IN z_dimStepPredicates.FIRST..z_dimStepPredicates.LAST LOOP
1055 IF (z_dimStepPredicates(i).Step_Specific_Predicate IS NOT NULL) THEN
1056
1057 --FND_FILE.put_line(FND_FILE.OUTPUT,'LENGTH '||LENGTH(z_dimStepPredicates(i).Step_Specific_Predicate));
1058
1059 FND_FILE.put_line(FND_FILE.OUTPUT
1060 ,z_dimStepPredicates(i).Step_Specific_Predicate);
1061 IF (i < z_dimStepPredicates.LAST) THEN
1062 FND_FILE.put_line(FND_FILE.OUTPUT,G_SPACE||G_AND||G_SPACE);
1063 END IF;
1064 END IF;
1065 END LOOP;
1066 END IF;
1067 END IF;
1068
1069
1070 EXCEPTION
1071 WHEN OTHERS THEN
1072 z_errCode := -1;
1073 z_errMsg := 'Procedure: logPredicateToOutputFile: '||SQLERRM;
1074 RAISE;
1075
1076 END logPredicateToOutputFile;
1077
1078
1079 /*************************************************************************
1080
1081 generateDataStepPredicate
1082
1083 This procedure generates the predicate for a particular Data Component
1084 Step.
1085
1086 *************************************************************************/
1087
1088 PROCEDURE generateDataStepPredicate(
1089 p_dataComponentDefId IN NUMBER,
1090 p_factTable IN VARCHAR2,
1091 p_tableAlias IN VARCHAR2,
1092 p_firstColumnName IN VARCHAR2,
1093 p_stepType IN VARCHAR2,
1094 p_stepSequence IN NUMBER,
1095 p_operator IN VARCHAR2,
1096 p_operatorValue IN VARCHAR2,
1097 p_isDimension IN BOOLEAN,
1098 p_byDimensionColumn IN VARCHAR2,
1102 cursor getDataCmpStepDtl is
1099 p_byDimensionValue IN VARCHAR2,
1100 x_stepSpecificString OUT NOCOPY LONG) IS
1101
1103 select
1104 a.criteria_sequence
1105 ,a.value
1106 ,a.max_range_value
1107 from
1108 Fem_Cond_Data_Cmp_St_Dtl a
1109 where a.COND_DATA_CMP_OBJ_DEF_ID = p_dataComponentDefId
1110 and a.TABLE_NAME = p_factTable
1111 and a.STEP_SEQUENCE = p_stepSequence
1112 order by a.criteria_Sequence;
1113
1114 l_criteriaSequence NUMBER(9,0);
1115 l_value VARCHAR2(255);
1116 l_maxRangeValue VARCHAR2(255);
1117 l_dataType ALL_TAB_COLUMNS.DATA_TYPE%TYPE;
1118 l_canonicalDTMask varchar2(26) := 'YYYY/MM/DD HH24:MI:SS';
1119 l_byDimensionValue VARCHAR2(40) := p_byDimensionValue;
1120 l_columnPrefix VARCHAR2(30);
1121
1122
1123 BEGIN
1124
1125 --Deleting all records in z_dataStepValues---
1126
1127 z_dataStepValues.DELETE;
1128 z_dataStepValuesCtr := 0;
1129 ------------------------------------------------------------
1130
1131 OPEN getDataCmpStepDtl;
1132 LOOP
1133 <<NEXT_DATA_CMP_STEP_DTL>>
1134 FETCH getDataCmpStepDtl into
1135 l_criteriaSequence
1136 ,l_value
1137 ,l_maxRangeValue;
1138 EXIT WHEN getDataCmpStepDtl%NOTFOUND;
1139
1140 /*--If Step_Type is 'Another Column' validate the SECOND column--*/
1141 IF (p_stepType = G_DATA_ANOTHER_COL) THEN
1142 IF NOT columnNameIsValid(p_tableName => p_factTable
1143 ,p_columnName =>l_value) THEN
1144 -- IF (z_loggingTurnedOn = 'Y') THEN
1145 fnd_message.set_name(APPLICATION => FEM_APP,NAME => G_INVALID_COLUMN);
1146 fnd_message.set_token(TOKEN => G_COLUMN_TOKEN, VALUE => l_value, TRANSLATE => FALSE);
1147 fnd_message.set_token(TOKEN => G_TABLE_TOKEN, VALUE =>p_factTable,TRANSLATE => FALSE);
1148 fnd_msg_pub.add_detail(p_message_type => 'W');
1149
1150 /*FEM_ENGINES_PKG.PUT_MESSAGE(
1151 p_app_name => FEM_APP,
1152 p_msg_name => G_INVALID_COLUMN,
1153 p_token1 => G_COLUMN_TOKEN,
1154 p_value1 => l_value,
1155 p_token2 => G_TABLE_TOKEN,
1156 p_value2 => p_factTable);*/
1157 -- p_token3 => 'FND_MESSAGE_TYPE',
1158 -- p_value3 => 'W');
1159
1160
1161 -- END IF;
1162 RETURN;
1163 END IF;
1164 END IF;
1165
1166 l_dataType := getColumnDataType(p_synonymName => p_factTable,
1167 p_columnName => p_firstColumnName,
1168 p_isDimension => FALSE,
1169 p_dimensionId => NULL);
1170
1171
1172 IF (p_stepType <> G_DATA_ANOTHER_COL) THEN
1173 IF (l_dataType = 'STRING') THEN
1174 l_value := ''''||l_value||'''';
1175 IF (p_stepType = G_RANGE_OF_VALUES) THEN
1176 l_maxRangeValue := ''''||l_maxRangeValue ||'''';
1177 END IF;
1178 ELSIF (l_dataType = 'DATE') THEN
1179 l_value:= 'TO_DATE'||G_OPEN ||''''||l_value||''''||','||''''||l_canonicalDTMask||''''||G_CLOSE;
1180 IF (p_stepType = G_RANGE_OF_VALUES) THEN
1181 l_maxRangeValue := 'TO_DATE'||G_OPEN ||''''||l_maxRangeValue ||''''||','||''''||l_canonicalDTMask||''''||G_CLOSE;
1182 END IF;
1183 END IF;
1184 END IF;
1185
1186 /*---------------------------------------------------------------*/
1187
1188 z_dataStepValues(z_dataStepValuesCtr).OPERATOR_CODE
1189 := p_operator;
1190 z_dataStepValues(z_dataStepValuesCtr).OPERATOR_VALUE
1191 := p_operatorValue;
1192 z_dataStepValues(z_dataStepValuesCtr).VALUE
1193 := l_value;
1194 z_dataStepValues(z_dataStepValuesCtr).MAX_RANGE_VALUE
1195 := l_maxRangeValue;
1196 z_dataStepValuesCtr := z_dataStepValuesCtr + 1;
1197
1198 END LOOP;
1199 CLOSE getDataCmpStepDtl;
1200
1201 IF (z_dataStepValues.COUNT > 0) THEN
1202 IF (p_stepType = G_DATA_ANOTHER_COL) THEN
1203 anotherColumnStep(p_tableName => p_factTable
1204 ,p_tableAlias => p_tableAlias
1205 ,p_columnName => p_firstColumnName
1206 ,x_stepSpecificString => x_stepSpecificString
1207 );
1208 ELSIF (p_stepType = G_SPECIFIC_VALUE) THEN
1209 specificValueStep(p_tableName => p_factTable
1210 ,p_tableAlias => p_tableAlias
1211 ,p_columnName => p_firstColumnName
1212 ,p_isDimension => p_isDimension
1213 ,x_stepSpecificString => x_stepSpecificString
1214 );
1215 ELSIF (p_stepType = G_RANGE_OF_VALUES) THEN
1216 rangeStep(p_tableName => p_factTable
1217 ,p_tableAlias => p_tableAlias
1218 ,p_columnName => p_firstColumnName
1219 ,p_isDimension => p_isDimension
1220 ,x_stepSpecificString => x_stepSpecificString
1221 );
1222 END IF;
1223 END IF;
1224
1225 --TEST*********************
1226 IF (z_dataStepValues.COUNT > 1) THEN
1227 x_stepSpecificString := G_OPEN || x_stepSpecificString || G_CLOSE;
1228 END IF;
1229
1230
1231 --MAPPING BY DIMENSION TYPE SUPPORT... MUST USE APPEND OR CONDITION TO PREDICATE
1232 IF (p_byDimensionColumn = p_firstColumnName) THEN
1233
1234 IF (p_tableAlias IS NULL) THEN
1238 END IF;
1235 l_columnPrefix := NULL;
1236 ELSE
1237 l_columnPrefix := p_tableAlias||G_PERIOD;
1239
1240 IF (l_dataType = 'STRING') THEN
1241 l_byDimensionValue := ''''||l_byDimensionValue ||'''';
1242 ELSIF (l_dataType = 'DATE') THEN
1243 l_byDimensionValue := 'TO_DATE'||G_OPEN ||''''||l_byDimensionValue
1244 ||''''||','||''''||l_canonicalDTMask||''''||G_CLOSE;
1245 END IF;
1246
1247 x_stepSpecificString := G_OPEN||x_stepSpecificString||G_SPACE||G_OR||G_SPACE
1248 ||G_OPEN||l_columnPrefix||p_byDimensionColumn||G_SPACE||G_EQUAL||G_SPACE||l_byDimensionValue
1249 ||G_CLOSE||G_CLOSE;
1250 END IF;
1251
1252 EXCEPTION
1253 WHEN OTHERS THEN
1254 z_errCode := -1;
1255 z_errMsg := 'Procedure: generateDataStepPredicate: '||SQLERRM;
1256 RAISE;
1257
1258 END generateDataStepPredicate;
1259
1260
1261 /*************************************************************************
1262
1263 generateDataPredicate
1264
1265 This procedure generates predicates for all the Data Component Steps
1266 and populates the z_dataStepPredicates PLSQL table with the individual
1267 Data Component Step predicates
1268
1269 *************************************************************************/
1270
1271 PROCEDURE generateDataPredicate(p_dataComponentDefId IN NUMBER
1272 ,p_factTableName IN VARCHAR2
1273 ,p_tableAlias IN VARCHAR2
1274 ,p_byDimensionColumn IN VARCHAR2
1275 ,p_byDimensionValue IN VARCHAR2
1276 ) IS
1277
1278 cursor getDataCmpSteps is
1279 select
1280 a.column_name
1281 ,a.step_type
1282 ,a.step_sequence
1283 ,a.operator
1284 from
1285 Fem_Cond_Data_Cmp_Steps a
1286 where a.COND_DATA_CMP_OBJ_DEF_ID = p_dataComponentDefId
1287 and a.TABLE_NAME = p_factTableName
1288 order by a.step_sequence;
1289
1290 l_columnName VARCHAR2(30);
1291
1292 l_stepType VARCHAR2(30);
1293 l_stepSequence NUMBER;
1294 l_operator VARCHAR2(30);
1295 l_operatorValue VARCHAR2(30);
1296
1297 l_stepSpecificString LONG;
1298 l_isDimension BOOLEAN;
1299
1300 BEGIN
1301
1302 OPEN getDataCmpSteps;
1303 LOOP
1304 <<NEXT_DATA_CMP_STEP>>
1305 FETCH getDataCmpSteps INTO
1306 l_columnName
1307 ,l_stepType
1308 ,l_stepSequence
1309 ,l_operator;
1310 EXIT WHEN getDataCmpSteps%NOTFOUND;
1311
1312 IF NOT columnNameIsValid(p_tableName => p_factTableName
1313 ,p_columnName => l_columnName
1314 ,x_isDimension => l_isDimension) THEN
1315
1316 --IF (z_loggingTurnedOn = 'Y') THEN
1317 fnd_message.set_name(APPLICATION => FEM_APP,NAME => G_INVALID_COLUMN);
1318 fnd_message.set_token(TOKEN => G_COLUMN_TOKEN, VALUE => l_columnName, TRANSLATE => FALSE);
1319 fnd_message.set_token(TOKEN => G_TABLE_TOKEN, VALUE =>p_factTableName,TRANSLATE => FALSE);
1320 fnd_msg_pub.add_detail(p_message_type => 'W');
1321 /* FEM_ENGINES_PKG.PUT_MESSAGE(
1322 p_app_name => FEM_APP,
1323 p_msg_name => G_INVALID_COLUMN,
1324 p_token1 => G_COLUMN_TOKEN,
1325 p_value1 => l_columnName,
1326 p_token2 => G_TABLE_TOKEN,
1327 p_value2 => p_factTableName);*/
1328
1329
1330 -- END IF;
1331
1332 GOTO NEXT_DATA_CMP_STEP;
1333 END IF;
1334
1335
1336 l_operatorValue := operatorValue(l_operator);
1337
1338 IF (l_operatorValue = '[Invalid Operator Code]') THEN
1339
1340 -- IF (z_loggingTurnedOn = 'Y') THEN
1341
1342 fnd_message.set_name(APPLICATION => FEM_APP,NAME => G_INVALID_OPERATOR);
1343 fnd_message.set_token(TOKEN => G_OPERATOR_TOKEN, VALUE => l_operator, TRANSLATE => FALSE);
1344 fnd_msg_pub.add_detail(p_message_type => 'W');
1345
1346 /* FEM_ENGINES_PKG.PUT_MESSAGE(
1347 p_app_name => FEM_APP,
1348 p_msg_name => G_INVALID_OPERATOR,
1349 p_token1 => G_OPERATOR_TOKEN,
1350 p_value1 => l_operator);*/
1351 -- END IF;
1352
1353 GOTO NEXT_DATA_CMP_STEP;
1354 END IF;
1355
1356
1357 generateDataStepPredicate(
1358 p_dataComponentDefId => p_dataComponentDefId
1359 ,p_factTable => p_factTableName
1360 ,p_tableAlias => p_tableAlias
1361 ,p_firstColumnName => l_columnName
1362 ,p_stepType => l_stepType
1363 ,p_stepSequence => l_stepSequence
1364 ,p_operator => l_operator
1365 ,p_operatorValue => l_operatorValue
1366 ,p_isDimension => l_isDimension
1367 ,p_byDimensionColumn => p_byDimensionColumn
1368 ,p_byDimensionValue => p_byDimensionValue
1369 ,x_stepSpecificString => l_stepSpecificString);
1370
1371
1372 --Add Step Predicate to Steps Predicate Table--
1373 IF (l_stepSpecificString IS NOT NULL) THEN
1374 z_dataStepPredicates(z_dataStepPredicatesCtr).DATA_DIM_COMPONENT_DEF_ID
1375 := p_dataComponentDefId;
1376 z_dataStepPredicates(z_dataStepPredicatesCtr).STEP_SPECIFIC_PREDICATE
1377 := l_stepSpecificString;
1381
1378 z_dataStepPredicatesCtr := z_dataStepPredicatesCtr + 1;
1379 END IF;
1380 ------------------------------------------------
1382 END LOOP;
1383
1384 CLOSE getDataCmpSteps;
1385
1386
1387 EXCEPTION
1388
1389 WHEN OTHERS THEN
1390 z_errCode := -1;
1391 z_errMsg := 'Procedure: generateDataPredicate: '||SQLERRM;
1392 RAISE;
1393
1394 END generateDataPredicate;
1395
1396
1397 /*************************************************************************
1398
1399 generateDimValuePredicate
1400
1401 Private procedure which generates the predicate for a Dimension Component
1402 of type 'Value'.
1403
1404 *************************************************************************/
1405
1406 PROCEDURE generateDimValuePredicate(
1407 p_columnPrefix IN VARCHAR2,
1408 p_columnName IN VARCHAR2,
1409 p_value IN VARCHAR2,
1410 x_stepSpecificString OUT NOCOPY LONG) IS
1411
1412
1413 l_stepSpecificString LONG;
1414
1415 BEGIN
1416
1417
1418 l_stepSpecificString := l_stepSpecificString
1419 ||G_OPEN
1420 ||p_columnPrefix||p_columnName
1421 ||G_SPACE||G_EQUAL||G_SPACE
1422 ||p_value
1423 ||G_CLOSE;
1424
1425 x_stepSpecificString := l_stepSpecificString;
1426
1427 EXCEPTION
1428
1429 WHEN OTHERS THEN
1430 z_errCode := -1;
1431 z_errMsg := 'Procedure: generateDimValuePredicate: '||SQLERRM;
1432 RAISE;
1433
1434 END generateDimValuePredicate;
1435
1436
1437 /*************************************************************************
1438
1439 generateDimAttributePredicate
1440
1441 Private procedure which generates the predicate for a Dimension Component
1442 of type 'Attribute'.
1443
1444 *************************************************************************/
1445
1446 PROCEDURE generateDimAttributePredicate(
1447 p_dimComponentDefId IN NUMBER,
1448 p_dimensionId IN NUMBER,
1449 p_columnPrefix IN VARCHAR2,
1450 p_columnName IN VARCHAR2,
1451 x_stepSpecificString OUT NOCOPY LONG) IS
1452
1453
1454 CURSOR getDimAttrs IS
1455 SELECT DISTINCT dim_attr_varchar_label
1456 FROM fem_cond_dim_cmp_dtl
1457 WHERE cond_dim_cmp_obj_def_id = p_dimComponentDefId;
1458
1459 l_stepSpecificString LONG;
1460 l_attrString LONG;
1461 l_attrColumnName VARCHAR2(30);
1462 l_attrValueColumnName VARCHAR2(30);
1463 l_attrTableName VARCHAR2(30);
1464 l_attrValue VARCHAR2(1000);
1465 l_attrId NUMBER;
1466 l_operator VARCHAR2(30);
1467 l_attrVarcharLabel VARCHAR2(30);
1468 l_attrDataType VARCHAR2(30);
1469 l_dataType VARCHAR2(30);
1470 l_canonicalDTMask varchar2(26) := 'YYYY/MM/DD HH24:MI:SS';
1471 l_attrDimId NUMBER;
1472 l_count NUMBER := 0;
1473 l_defaultVersionId NUMBER;
1474
1475 l_value_set_id number;
1476 l_value_set_string varchar2(2000);
1477
1478 CURSOR getDimAttrSteps IS
1479 SELECT dim_attr_varchar_label,
1480 dim_attr_value
1481 FROM fem_cond_dim_cmp_dtl
1482 WHERE cond_dim_cmp_obj_def_id = p_dimComponentDefId
1483 AND dim_attr_varchar_label = l_attrVarcharLabel;
1484
1485 BEGIN
1486
1487 -- Get dimension value set id for the Condition Components GVSC
1488 -- (returns null for non VSR dimensions)
1489 l_value_set_id :=
1490 getDimensionValueSetId (
1491 p_dimension_id => p_dimensionId
1492 ,p_cond_component_obj_def_id => p_dimComponentDefId
1493 );
1494
1495 -- Initialize Value Set String to NULL
1496 l_value_set_string := null;
1497
1498 if (l_value_set_id is not null) then
1499
1500 l_value_set_string := ' AND VALUE_SET_ID = '||l_value_set_id;
1501
1502 end if;
1503
1504
1505 OPEN getDimAttrs;
1506
1507 LOOP
1508
1509 FETCH getDimAttrs
1510 INTO
1511 l_attrVarcharLabel;
1512
1513 EXIT WHEN getDimAttrs%NOTFOUND;
1514
1515 l_operator := 'AND';
1516
1517 BEGIN
1518
1519 OPEN getDimAttrSteps;
1520
1521 LOOP
1522
1523 <<NEXT_DIM_ATTR_STEP>>
1524
1525 FETCH getDimAttrSteps
1526 INTO l_attrVarcharLabel,
1527 l_attrValue;
1528
1529 EXIT WHEN getDimAttrSteps%NOTFOUND;
1530
1531 BEGIN
1532
1533 SELECT A.MEMBER_COL,
1534 B.ATTRIBUTE_VALUE_COLUMN_NAME,
1535 A.ATTRIBUTE_TABLE_NAME,
1536 B.ATTRIBUTE_ID,
1537 DECODE (SUBSTR(B.ATTRIBUTE_DATA_TYPE_CODE,1,4), 'DIME', 'DIMENSION', 'NUMB', 'NUMBER', 'DATE', 'DATE', 'STRING'),
1538 B.ATTRIBUTE_DIMENSION_ID,
1539 C.VERSION_ID
1540 INTO l_attrColumnName,
1541 l_attrValueColumnName,
1542 l_attrTableName,
1543 l_attrId,
1544 l_attrDataType,
1545 l_attrDimId,
1546 l_defaultVersionId
1547 FROM FEM_XDIM_DIMENSIONS A,
1548 FEM_DIM_ATTRIBUTES_VL B,
1549 FEM_DIM_ATTR_VERSIONS_VL C
1550 WHERE A.DIMENSION_ID = p_dimensionId
1551 AND A.DIMENSION_ID = B.DIMENSION_ID
1552 -- AND B.ATTRIBUTE_ID = l_attrId;
1553 AND B.ATTRIBUTE_VARCHAR_LABEL = l_attrVarcharLabel
1557 EXCEPTION
1554 AND B.ATTRIBUTE_ID = C.ATTRIBUTE_ID
1555 AND C.DEFAULT_VERSION_FLAG = 'Y';
1556
1558 WHEN NO_DATA_FOUND THEN
1559 --LOG INVALID VALUES
1560 GOTO NEXT_DIM_ATTR_STEP;
1561
1562 END;
1563
1564 l_dataType := l_attrDataType;
1565
1566 IF (l_attrDataType = 'DIMENSION') THEN
1567 l_dataType := getColumnDataType(p_synonymName => NULL,
1568 p_columnName => NULL,
1569 p_isDimension => TRUE,
1570 p_dimensionId => l_attrDimId);
1571 END IF;
1572
1573 IF (l_dataType = 'STRING') THEN
1574 l_attrValue := ''''||l_attrValue||'''';
1575 ELSIF (l_dataType = 'DATE') THEN
1576 l_attrValue := 'TO_DATE'||G_OPEN ||''''||l_attrValue||''''||','||''''||l_canonicalDTMask||''''||G_CLOSE;
1577 END IF;
1578
1579 IF (l_attrString IS NULL) THEN
1580 l_attrString := G_OPEN||p_columnPrefix||p_columnName
1581 ||G_SPACE||'IN'||G_SPACE
1582 ||G_OPEN
1583 ||' SELECT ' || l_attrColumnName
1584 ||' FROM ' || l_attrTableName
1585 ||' WHERE ATTRIBUTE_ID = ' || l_attrId
1586 ||' AND VERSION_ID = '|| l_defaultVersionId
1587 ||' AND '|| l_attrValueColumnName || ' = '||l_attrValue
1588 || l_value_set_string
1589 ||G_CLOSE||G_CLOSE;
1590
1591 ELSE
1592 l_attrString := l_attrString
1593 ||' '||l_operator||' '
1594 ||G_OPEN
1595 ||p_columnPrefix||p_columnName
1596 ||G_SPACE||'IN'||G_SPACE
1597 ||G_OPEN
1598 ||' SELECT ' || l_attrColumnName
1599 ||' FROM ' || l_attrTableName
1600 ||' WHERE ATTRIBUTE_ID = ' || l_attrId
1601 ||' AND VERSION_ID = '|| l_defaultVersionId
1602 ||' AND '|| l_attrValueColumnName || ' = '||l_attrValue
1603 || l_value_set_string
1604 ||G_CLOSE||G_CLOSE;
1605 END IF;
1606
1607 l_operator := 'OR';
1608 l_count := l_count + 1;
1609 END LOOP;
1610
1611 IF (l_count > 1) THEN
1612 l_attrString := G_OPEN||l_attrString||G_CLOSE;
1613 END IF;
1614
1615 l_count := 0;
1616
1617 IF (l_stepSpecificString IS NOT NULL) THEN
1618 l_stepSpecificString := l_stepSpecificString || ' AND '|| l_attrString;
1619 ELSE
1620 l_stepSpecificString := l_attrString;
1621 END IF;
1622
1623 l_attrString := NULL;
1624
1625 CLOSE getDimAttrSteps;
1626 EXCEPTION
1627 WHEN NO_DATA_FOUND THEN
1628 RETURN;
1629 --LOG INVALID VALUES
1630 --PRINT('EXCEPTION');
1631
1632 END;
1633
1634 END LOOP;
1635 x_stepSpecificString := l_stepSpecificString;
1636
1637 EXCEPTION
1638
1639 WHEN OTHERS THEN
1640 z_errCode := -1;
1641 z_errMsg := 'Procedure: generateDimAttributePredicate: '||SQLERRM;
1642 RAISE;
1643
1644 END generateDimAttributePredicate;
1645
1646
1647 /*************************************************************************
1648
1649 getHierValueSetId
1650
1651 *************************************************************************/
1652 FUNCTION getHierValueSetId (
1653 p_dimension_id in number
1654 ,p_hierarchy_obj_id in number
1655 ) RETURN number IS
1656
1657 l_value_set_id number;
1658 l_value_set_required_flag varchar2(1);
1659
1660 BEGIN
1661
1662 l_value_set_id := null;
1663
1664 select value_set_required_flag
1665 into l_value_set_required_flag
1666 from fem_xdim_dimensions
1667 where dimension_id = p_dimension_id;
1668
1669 if (l_value_set_required_flag = 'Y') then
1670
1671 select value_set_id
1672 into l_value_set_id
1673 from fem_hier_value_sets
1674 where hierarchy_obj_id = p_hierarchy_obj_id;
1675
1676 end if;
1677
1678 return l_value_set_id;
1679
1680 EXCEPTION
1681
1682 when others then
1683 return l_value_set_id;
1684
1685 END getHierValueSetId;
1686
1687
1688 /*************************************************************************
1689
1690 getDimensionValueSetId
1691
1692 *************************************************************************/
1693 FUNCTION getDimensionValueSetId (
1694 p_dimension_id in number
1695 ,p_cond_component_obj_def_id in number
1696 ) RETURN number IS
1697
1698 l_value_set_id number;
1699 l_value_set_required_flag varchar2(1);
1700
1701 BEGIN
1702
1703 l_value_set_id := null;
1704
1705 select value_set_required_flag
1706 into l_value_set_required_flag
1707 from fem_xdim_dimensions
1708 where dimension_id = p_dimension_id;
1709
1710 if (l_value_set_required_flag = 'Y') then
1711
1712 select value_set_id
1713 into l_value_set_id
1714 from fem_global_vs_combo_defs
1715 where dimension_id = p_dimension_id
1716 and global_vs_combo_id = (
1717 select obj.local_vs_combo_id
1718 from fem_object_catalog_b obj
1722 );
1719 ,fem_object_definition_b def
1720 where obj.object_id = def.object_id
1721 and def.object_definition_id = p_cond_component_obj_def_id
1723
1724 end if;
1725
1726 return l_value_set_id;
1727
1728 EXCEPTION
1729
1730 when others then
1731 return l_value_set_id;
1732
1733 END getDimensionValueSetId;
1734
1735
1736 /*============================================================================+
1737 | PROCEDURE
1738 | getDimHierQuery
1739 |
1740 | DESCRIPTION
1741 | Function for returning the query string for finding a list of nodes in an
1742 | unflattened hierarchy definition based on the passed parameters.
1743 |
1744 | SCOPE - PRIVATE
1745 +============================================================================*/
1746
1747 FUNCTION getDimHierQuery (
1748 p_hierarchy_table_name in varchar2
1749 ,p_relation_code in varchar2
1750 ,p_hierarchy_obj_def_id in number
1751 ,p_node_list in varchar2
1752 ,p_value_set_id in number
1753 ,p_effective_date_varchar in varchar2
1754 ) RETURN varchar2
1755 IS
1756
1757 l_query_string long;
1758 l_value_set_string varchar2(2000);
1759
1760 BEGIN
1761
1762 l_value_set_string := null;
1763
1764 if (p_value_set_id is not null) then
1765
1766 l_value_set_string :=
1767 ' AND PARENT_VALUE_SET_ID = '||p_value_set_id
1768 ||' AND CHILD_VALUE_SET_ID = '||p_value_set_id;
1769
1770 end if;
1771
1772 l_query_string :=
1773 case p_relation_code
1774 when 'DESC_OF' then
1775 ' SELECT CHILD_ID'
1776 ||' FROM '||p_hierarchy_table_name
1777 ||' START WITH PARENT_ID IN ('||p_node_list||')'
1778 ||' AND HIERARCHY_OBJ_DEF_ID = '||p_hierarchy_obj_def_id
1779 ||' AND PARENT_ID <> CHILD_ID'
1780 || l_value_set_string
1781 ||' CONNECT BY PRIOR CHILD_ID = PARENT_ID'
1782 ||' AND HIERARCHY_OBJ_DEF_ID = '||p_hierarchy_obj_def_id
1783 ||' AND PARENT_ID <> CHILD_ID'
1784 || l_value_set_string
1785 when 'LAST_DESC_OF' then
1786 ' SELECT CHILD_ID'
1787 ||' FROM '||p_hierarchy_table_name
1788 ||' WHERE CHILD_ID NOT IN ('
1789 ||' SELECT PARENT_ID'
1790 ||' FROM '||p_hierarchy_table_name
1791 ||' WHERE HIERARCHY_OBJ_DEF_ID = '||p_hierarchy_obj_def_id
1792 ||' AND PARENT_ID <> CHILD_ID'
1793 || l_value_set_string
1794 ||' )'
1795 ||' START WITH PARENT_ID IN ('||p_node_list||')'
1796 ||' AND HIERARCHY_OBJ_DEF_ID = '||p_hierarchy_obj_def_id
1797 ||' AND PARENT_ID <> CHILD_ID'
1798 || l_value_set_string
1799 ||' CONNECT BY PRIOR CHILD_ID = PARENT_ID'
1800 ||' AND HIERARCHY_OBJ_DEF_ID = '||p_hierarchy_obj_def_id
1801 ||' AND PARENT_ID <> CHILD_ID'
1802 || l_value_set_string
1803 when 'NODE' then
1804 ' SELECT CHILD_ID'
1805 ||' FROM '||p_hierarchy_table_name
1806 ||' WHERE CHILD_ID IN ('||p_node_list||')'
1807 ||' AND HIERARCHY_OBJ_DEF_ID = '||p_hierarchy_obj_def_id
1808 || l_value_set_string
1809 when 'NODE_AND_DESC' then
1810 ' SELECT CHILD_ID'
1811 ||' FROM '||p_hierarchy_table_name
1812 ||' WHERE CHILD_ID IN ('||p_node_list||')'
1813 ||' AND HIERARCHY_OBJ_DEF_ID = '||p_hierarchy_obj_def_id
1814 || l_value_set_string
1815 ||' UNION ALL'
1816 ||' SELECT CHILD_ID'
1817 ||' FROM '||p_hierarchy_table_name
1818 ||' START WITH PARENT_ID IN ('||p_node_list||')'
1819 ||' AND HIERARCHY_OBJ_DEF_ID = '||p_hierarchy_obj_def_id
1820 ||' AND PARENT_ID <> CHILD_ID'
1821 || l_value_set_string
1822 ||' CONNECT BY PRIOR CHILD_ID = PARENT_ID'
1823 ||' AND HIERARCHY_OBJ_DEF_ID = '||p_hierarchy_obj_def_id
1824 ||' AND PARENT_ID <> CHILD_ID'
1825 || l_value_set_string
1826 else '[Invalid Relation Code]'
1827 end;
1828
1829 if (l_query_string = '[Invalid Relation Code]') then
1830
1831 FND_MESSAGE.set_name(APPLICATION => FEM_APP,NAME => G_INVALID_RELATION);
1832 FND_MESSAGE.set_token(TOKEN => G_RELATION_TOKEN, VALUE => p_relation_code, TRANSLATE => FALSE);
1833 FND_MSG_PUB.add_detail(p_message_type => 'W');
1834
1835 return null;
1836
1837 end if;
1838
1839 return l_query_string;
1840
1841 END getDimHierQuery;
1842
1843
1844 /*============================================================================+
1845 | PROCEDURE
1846 | getDimFlatHierQuery
1847 |
1848 | DESCRIPTION
1849 | Function for returning the query string for finding a list of nodes in a
1850 | flattened hierarchy definition based on the passed parameters.
1851 |
1852 | SCOPE - PRIVATE
1853 +============================================================================*/
1854
1855 FUNCTION getDimFlatHierQuery (
1856 p_hierarchy_table_name in varchar2
1857 ,p_relation_code in varchar2
1858 ,p_hierarchy_obj_def_id in number
1859 ,p_node_list in varchar2
1860 ,p_value_set_id in number
1861 ) RETURN varchar2
1862 IS
1863
1867 BEGIN
1864 l_query_string long;
1865 l_value_set_string varchar2(2000);
1866
1868
1869 l_value_set_string := null;
1870
1871 if (p_value_set_id is not null) then
1872
1873 l_value_set_string :=
1874 ' AND PARENT_VALUE_SET_ID = '||p_value_set_id
1875 ||' AND CHILD_VALUE_SET_ID = '||p_value_set_id;
1876
1877 end if;
1878
1879 l_query_string :=
1880 case p_relation_code
1881 when 'DESC_OF' then
1882 ' SELECT CHILD_ID'
1883 ||' FROM '||p_hierarchy_table_name
1884 ||' WHERE PARENT_ID IN ('||p_node_list||')'
1885 ||' AND HIERARCHY_OBJ_DEF_ID = '||p_hierarchy_obj_def_id
1886 ||' AND PARENT_ID <> CHILD_ID'
1887 || l_value_set_string
1888 when 'LAST_DESC_OF' then
1889 ' SELECT CHILD_ID'
1890 ||' FROM '||p_hierarchy_table_name
1891 ||' WHERE PARENT_ID IN ('||p_node_list||')'
1892 ||' AND HIERARCHY_OBJ_DEF_ID = '||p_hierarchy_obj_def_id
1893 ||' AND PARENT_ID <> CHILD_ID'
1894 || l_value_set_string
1895 ||' AND CHILD_ID IN ('
1896 ||' SELECT CHILD_ID'
1897 ||' FROM '||p_hierarchy_table_name
1898 ||' WHERE PARENT_ID = CHILD_ID'
1899 ||' AND HIERARCHY_OBJ_DEF_ID = '||p_hierarchy_obj_def_id
1900 ||' AND PARENT_DEPTH_NUM > 1'
1901 || l_value_set_string
1902 ||' )'
1903 when 'NODE' then
1904 ' SELECT CHILD_ID'
1905 ||' FROM '||p_hierarchy_table_name
1906 ||' WHERE CHILD_ID IN ('||p_node_list||')'
1907 ||' AND HIERARCHY_OBJ_DEF_ID = '||p_hierarchy_obj_def_id
1908 ||' AND PARENT_DEPTH_NUM = 1'
1909 || l_value_set_string
1910 when 'NODE_AND_DESC' then
1911 ' SELECT CHILD_ID'
1912 ||' FROM '||p_hierarchy_table_name
1913 ||' WHERE CHILD_ID IN ('||p_node_list||')'
1914 ||' AND HIERARCHY_OBJ_DEF_ID = '||p_hierarchy_obj_def_id
1915 ||' AND PARENT_DEPTH_NUM = 1'
1916 || l_value_set_string
1917 ||' UNION ALL'
1918 ||' SELECT CHILD_ID'
1919 ||' FROM '||p_hierarchy_table_name
1920 ||' WHERE PARENT_ID IN ('||p_node_list||')'
1921 ||' AND HIERARCHY_OBJ_DEF_ID = '||p_hierarchy_obj_def_id
1922 ||' AND PARENT_ID <> CHILD_ID'
1923 || l_value_set_string
1924 else '[Invalid Relation Code]'
1925 end;
1926
1927 if (l_query_string = '[Invalid Relation Code]') then
1928
1929 FND_MESSAGE.set_name(APPLICATION => FEM_APP,NAME => G_INVALID_RELATION);
1930 FND_MESSAGE.set_token(TOKEN => G_RELATION_TOKEN, VALUE => p_relation_code, TRANSLATE => FALSE);
1931 FND_MSG_PUB.add_detail(p_message_type => 'W');
1932
1933 return null;
1934
1935 end if;
1936
1937 return l_query_string;
1938
1939 END getDimFlatHierQuery;
1940
1941
1942 /*************************************************************************
1943
1944 generateDimHierPredicate
1945
1946 Private procedure which generates the predicate for a Dimension Component
1947 of type 'Hierarchy'.
1948
1949 *************************************************************************/
1950
1951 PROCEDURE generateDimHierPredicate(
1952 p_dimComponentDefId IN NUMBER,
1953 p_dimensionId IN NUMBER,
1954 p_columnPrefix IN VARCHAR2,
1955 p_columnName IN VARCHAR2,
1956 p_effectiveDate IN VARCHAR2,
1957 x_stepSpecificString OUT NOCOPY LONG
1958 ) IS
1959
1960 cursor dim_hiers_csr is
1961 select distinct hierarchy_obj_id
1962 from fem_cond_dim_cmp_dtl
1963 where cond_dim_cmp_obj_def_id = p_dimComponentDefId;
1964
1965 cursor dim_hier_steps_csr (p_hierarchy_obj_id in number) is
1966 select node
1967 ,relation_code
1968 from fem_cond_dim_cmp_dtl
1969 where cond_dim_cmp_obj_def_id = p_dimComponentDefId
1970 and hierarchy_obj_id = p_hierarchy_obj_id;
1971
1972 -------------------
1973 -- Declare Types --
1974 -------------------
1975 type node_table is table of fem_cond_dim_cmp_dtl.node%type;
1976 type relation_code_table is table of fem_cond_dim_cmp_dtl.relation_code%type;
1977
1978 -----------------------
1979 -- Declare variables --
1980 -----------------------
1981 l_node_tbl node_table;
1982 l_relation_code_tbl relation_code_table;
1983
1984 l_hierarchy_obj_id number;
1985 l_hierarchy_obj_def_id number;
1986 l_hierarchy_obj_name varchar2(150);
1987 l_hierarchy_folder_name varchar2(150);
1988 l_hierarchy_table_name varchar2(30);
1989
1990 --l_node_list varchar2(2000);
1991 --l_desc_list varchar2(2000);
1992 --l_last_desc_list varchar2(2000);
1993
1994 l_node_list varchar2(32767);
1995 l_desc_list varchar2(32767);
1996 l_last_desc_list varchar2(32767);
1997
1998
1999 l_hier_query long;
2000 l_node_query long;
2001 l_desc_query long;
2002 l_last_desc_query long;
2003 l_all_desc_query long;
2004
2005 l_flattened_flag varchar2(1);
2006 l_flattened_code varchar2(30);
2010 l_stepSpecificString long;
2007 l_node_exists boolean;
2008 l_value_set_id number;
2009
2011
2012 BEGIN
2013
2014 -- Get hierarchy table name for the specified dimension
2015 begin
2016 select hierarchy_table_name
2017 INTO l_hierarchy_table_name
2018 from fem_xdim_dimensions
2019 where dimension_id = p_dimensionId;
2020 exception
2021 when no_data_found then
2022 -- LOG INVALID VALUES
2023 return;
2024 end;
2025
2026 -- Get dimension value set id for the Condition Components GVSC
2027 -- (returns null for non VSR dimensions)
2028 l_value_set_id :=
2029 getDimensionValueSetId (
2030 p_dimension_id => p_dimensionId
2031 ,p_cond_component_obj_def_id => p_dimComponentDefId
2032 );
2033
2034 open dim_hiers_csr;
2035
2036 loop <<NEXT_DIM_HIER>>
2037
2038 -- Fetch dimension hierarchies one by one
2039 fetch dim_hiers_csr
2040 into l_hierarchy_obj_id;
2041
2042 exit when dim_hiers_csr%NOTFOUND;
2043
2044 -- Get hierarchy definition for the specified hierarchy object
2045 Fem_Rule_Set_Manager.Get_ValidDefinition_Pub(
2046 p_object_id => l_hierarchy_obj_id
2047 ,p_rule_effective_date => p_effectiveDate
2048 ,x_object_definition_id => l_hierarchy_obj_def_id
2049 ,x_err_Code => z_errCode
2050 ,x_err_Msg => z_errMsg
2051 );
2052
2053 -- ERROR CHECK: Halt processing on this hierarchy if valid hierarchy
2054 -- definition not found!!!!!
2055 if (z_errcode <> 0) then
2056 --Message has been logged in Fem_Rule_Set_Manager.Get_ValidDefinition_Pub
2057 goto NEXT_DIM_HIER;
2058 end if;
2059
2060 -- Check if hierarchy will be flattened
2061 select flattened_rows_flag
2062 into l_flattened_flag
2063 from fem_hierarchies
2064 where hierarchy_obj_id = l_hierarchy_obj_id;
2065
2066 if (l_flattened_flag = 'Y') then
2067
2068 -- If hierarchy allows flattening, make sure flattening is complete on
2069 -- the hierarchy definition
2070 select flattened_rows_completion_code
2071 INTO l_flattened_code
2072 from fem_hier_definitions
2073 where hierarchy_obj_def_id = l_hierarchy_obj_def_id;
2074
2075 if (l_flattened_code <> 'COMPLETED') then
2076 raise hier_flattened_exception;
2077 end if;
2078
2079 end if;
2080
2081 open dim_hier_steps_csr(l_hierarchy_obj_id);
2082
2083 -- Fetch all dimension hierarchy steps (nodes and their relationships)
2084 -- at once.
2085 fetch dim_hier_steps_csr
2086 bulk collect into l_node_tbl
2087 ,l_relation_code_tbl;
2088
2089 close dim_hier_steps_csr;
2090
2091 -- Initialize all lists to null
2092 l_node_list := null;
2093 l_desc_list := null;
2094 l_last_desc_list := null;
2095
2096 for i in 1..l_node_tbl.LAST loop
2097
2098 -- Validate that node exists in the current hierarchy definition
2099 l_node_exists := validateHierarchyNode(
2100 p_tableName => l_hierarchy_table_name
2101 ,p_node => l_node_tbl(i)
2102 ,p_value_set_id => l_value_set_id
2103 ,p_objId => l_hierarchy_obj_id
2104 ,p_objDefId => l_hierarchy_obj_def_id
2105 ,p_effectiveDate => p_effectiveDate
2106 );
2107
2108 if (l_node_exists) then
2109
2110 -- Make a list of all the condition nodes in a Cost Object hierarchy
2111 -- or condition nodes in a Normal hierarchy that have "Node" relationships
2112 -- (Node Only and Node and Descendants Of).
2113 if ( l_hierarchy_table_name = 'FEM_COST_OBJECTS_HIER'
2114 or l_relation_code_tbl(i) in ('NODE','NODE_AND_DESC') ) then
2115
2116 if (l_node_list is null) then
2117 l_node_list := l_node_tbl(i);
2118 else
2119 l_node_list := l_node_list||','||l_node_tbl(i);
2120 end if;
2121
2122 end if;
2123
2124 -- Make a list of all the condition nodes in a Normal Hierarchy
2125 -- that have "Descendant" relationships (Descendants Of,
2126 -- Node and Descendants Of), and another list of condition nodes
2127 -- in a Normal Hierarchy that have "Last Descendant" relationships
2128 -- (Last Descendants Of).
2129 if (l_hierarchy_table_name <> 'FEM_COST_OBJECTS_HIER') then
2130
2131 -- Node list with "Descendant" relationships
2132 if (l_relation_code_tbl(i) in ('DESC_OF','NODE_AND_DESC')) then
2133
2134 if (l_desc_list is null) then
2135 l_desc_list := l_node_tbl(i);
2136 else
2137 l_desc_list := l_desc_list||','||l_node_tbl(i);
2138 end if;
2139
2140 -- Node list with "Last Descendant" relationships
2141 elsif (l_relation_code_tbl(i) = 'LAST_DESC_OF') then
2142
2143 if (l_last_desc_list is null) then
2144 l_last_desc_list := l_node_tbl(i);
2145 else
2146 l_last_desc_list := l_last_desc_list||','||l_node_tbl(i);
2147 end if;
2148
2149 end if;
2153 else -- Node does not exist
2150
2151 end if;
2152
2154
2155 --IF (z_loggingTurnedOn = 'Y') THEN
2156
2157 FEM_UTILS.GetObjNameandFolderUsingDef(
2158 p_obj_def_id => l_hierarchy_obj_def_id
2159 ,x_object_name => l_hierarchy_obj_name
2160 ,x_folder_name => l_hierarchy_folder_name
2161 );
2162
2163 fnd_message.set_name(APPLICATION => FEM_APP, NAME => G_INVALID_NODE);
2164 fnd_message.set_token(TOKEN => G_NODE_TOKEN, VALUE => l_node_tbl(i), TRANSLATE => FALSE);
2165 fnd_message.set_token(TOKEN => G_HIERARCHY_TOKEN, VALUE => l_hierarchy_obj_name, TRANSLATE => FALSE);
2166 fnd_msg_pub.add_detail(p_message_type => 'W');
2167
2168 /*
2169 FEM_ENGINES_PKG.PUT_MESSAGE(
2170 p_app_name => FEM_APP,
2171 p_msg_name => G_INVALID_NODE,
2172 p_token1 => G_NODE_TOKEN,
2173 p_value1 => l_node_tbl(i),
2174 p_token2 => G_HIERARCHY_TOKEN,
2175 p_value2 => l_hierarchy_obj_name);
2176 */
2177 -- END IF;
2178
2179 end if;
2180
2181 end loop;
2182
2183 -- Get the query for "Descendant" relationships
2184 if (l_desc_list is not null) then
2185
2186 if (l_flattened_flag = 'Y') then
2187
2188 l_desc_query := getDimFlatHierQuery (
2189 p_hierarchy_table_name => l_hierarchy_table_name
2190 ,p_relation_code => 'DESC_OF'
2191 ,p_hierarchy_obj_def_id => l_hierarchy_obj_def_id
2192 ,p_node_list => l_desc_list
2193 ,p_value_set_id => l_value_set_id
2194 );
2195
2196 else
2197
2198 l_desc_query := getDimHierQuery (
2199 p_hierarchy_table_name => l_hierarchy_table_name
2200 ,p_relation_code => 'DESC_OF'
2201 ,p_hierarchy_obj_def_id => l_hierarchy_obj_def_id
2202 ,p_node_list => l_desc_list
2203 ,p_value_set_id => l_value_set_id
2204 ,p_effective_date_varchar => p_effectiveDate
2205 );
2206
2207 end if;
2208
2209 end if;
2210
2211 -- Get the query for "Last Descendant" relationships
2212 if (l_last_desc_list is not null) then
2213
2214 if (l_flattened_flag = 'Y') then
2215
2216 l_last_desc_query := getDimFlatHierQuery (
2217 p_hierarchy_table_name => l_hierarchy_table_name
2218 ,p_relation_code => 'LAST_DESC_OF'
2219 ,p_hierarchy_obj_def_id => l_hierarchy_obj_def_id
2220 ,p_node_list => l_last_desc_list
2221 ,p_value_set_id => l_value_set_id
2222 );
2223
2224 else
2225
2226 l_last_desc_query := getDimHierQuery (
2227 p_hierarchy_table_name => l_hierarchy_table_name
2228 ,p_relation_code => 'LAST_DESC_OF'
2229 ,p_hierarchy_obj_def_id => l_hierarchy_obj_def_id
2230 ,p_node_list => l_last_desc_list
2231 ,p_value_set_id => l_value_set_id
2232 ,p_effective_date_varchar => p_effectiveDate
2233 );
2234
2235 end if;
2236
2237 end if;
2238
2239 -- Combine both "Descendant" and "Last Descendant" queries into one
2240 -- "All Descendant" query.
2241 if (l_desc_query is not null) then
2242
2243 l_all_desc_query := l_desc_query;
2244
2245 if (l_last_desc_query is not null) then
2246 l_all_desc_query := l_all_desc_query||' UNION ALL '||l_last_desc_query;
2247 end if;
2248
2249 elsif (l_last_desc_query is not null) then
2250
2251 l_all_desc_query := l_last_desc_query;
2252
2253 end if;
2254
2255 -- Create the hierarchy query by now combining the "Node" component
2256 -- and the "All Descendents" component.
2257 if (l_all_desc_query is not null) then
2258
2259 -- Get the query for "Node" relationships if there is a valid
2260 -- "Node" list.
2261 if (l_node_list is not null) then
2262
2263 if (l_flattened_flag = 'Y') then
2264
2265 l_node_query := getDimFlatHierQuery (
2266 p_hierarchy_table_name => l_hierarchy_table_name
2267 ,p_relation_code => 'NODE'
2268 ,p_hierarchy_obj_def_id => l_hierarchy_obj_def_id
2269 ,p_node_list => l_node_list
2270 ,p_value_set_id => l_value_set_id
2271 );
2272
2273 else
2274
2275 l_node_query := getDimHierQuery (
2276 p_hierarchy_table_name => l_hierarchy_table_name
2277 ,p_relation_code => 'NODE'
2278 ,p_hierarchy_obj_def_id => l_hierarchy_obj_def_id
2279 ,p_node_list => l_node_list
2280 ,p_value_set_id => l_value_set_id
2281 ,p_effective_date_varchar => p_effectiveDate
2282 );
2283
2284 end if;
2285
2286 -- Concatenate the "Node" query with the "All Descendants" query
2287 -- to from a single query.
2288 l_hier_query := l_node_query||' UNION ALL '||l_all_desc_query;
2289
2290 else
2291
2295 end if;
2292 -- Otherwise just use the "All Descendents" query.
2293 l_hier_query := l_all_desc_query;
2294
2296
2297 elsif (l_node_list is not null) then
2298
2299 -- Use the Node List directly as there is no "All Descendants" query
2300 l_hier_query := l_node_list;
2301
2302 else
2303
2304 -- If neither "Node" and "All Descendant" components are specified,
2305 -- then skip to the next hierarchy.
2306 goto NEXT_DIM_HIER;
2307
2308 end if;
2309
2310 l_hier_query := p_columnPrefix||p_columnName||' IN ('||l_hier_query||')';
2311
2312 -- Add the hierarchy query to the Step Where Clause string.
2313 if (l_stepSpecificString is not null) then
2314 l_stepSpecificString := l_stepSpecificString||' AND '|| l_hier_query;
2315 else
2316 l_stepSpecificString := l_hier_query ;
2317 end if;
2318
2319 end loop;
2320
2321 close dim_hiers_csr;
2322
2323 -- Set the Step where clause string value to be returned.
2324 x_stepSpecificString := l_stepSpecificString;
2325
2326 END generateDimHierPredicate;
2327
2328
2329
2330 /*************************************************************************
2331
2332 generateDimPredicate
2333
2334 This procedure generates predicates for all the Dimension Components of a
2335 Condition and populates the z_dimStepPredicates PLSQL table with the
2336 individual Dimension Component predicates
2337
2338 *************************************************************************/
2339
2340 PROCEDURE generateDimPredicate(p_dimComponentDefId IN NUMBER
2341 ,p_factTableName IN VARCHAR2
2342 ,p_tableAlias IN VARCHAR2
2343 ,p_componentFlag IN VARCHAR2
2344 ,p_effectiveDate IN VARCHAR2
2345 ,p_byDimensionId IN NUMBER
2346 ,p_byDimensionValue IN VARCHAR2) IS
2347
2348
2349 l_columnName VARCHAR2(30);
2350 l_columnPrefix VARCHAR2(30);
2351 l_stepType VARCHAR2(30);
2352 l_stepSequence NUMBER;
2353 l_operator VARCHAR2(30);
2354 l_dimCompType VARCHAR2(1);
2355 l_dimensionColumn VARCHAR2(30);
2356 l_dimensionId NUMBER;
2357 l_dimensionValue VARCHAR2(40);
2358 l_byDimensionValue VARCHAR2(40) := p_byDimensionValue;
2359
2360 l_stepSpecificString LONG;
2361
2362 l_dataType ALL_TAB_COLUMNS.DATA_TYPE%TYPE;
2363 l_canonicalDTMask varchar2(26) := 'YYYY/MM/DD HH24:MI:SS';
2364 l_tokenValue VARCHAR2(30);
2365
2366
2367
2368 BEGIN
2369 BEGIN
2370 SELECT dim_comp_type, dim_column, dim_id, value
2371 INTO l_dimCompType, l_dimensionColumn, l_dimensionId, l_dimensionValue
2372 FROM fem_cond_dim_components
2373 WHERE cond_dim_cmp_obj_def_id = p_dimComponentDefId;
2374 EXCEPTION
2375 WHEN NO_DATA_FOUND THEN
2376 RAISE INVALID_DIM_COMP_EXCEPTION;
2377 END;
2378
2379 IF p_factTableName IS NOT NULL THEN
2380 IF NOT columnNameIsValid(p_tableName => p_factTableName
2381 ,p_columnName => l_dimensionColumn) THEN
2382
2383 -- IF (z_loggingTurnedOn = 'Y') THEN
2384
2385 fnd_message.set_name(APPLICATION => FEM_APP,NAME => G_INVALID_DIMENSION);
2386 fnd_message.set_token(TOKEN => G_COLUMN_TOKEN, VALUE => l_dimensionColumn, TRANSLATE => FALSE);
2387 fnd_message.set_token(TOKEN => G_TABLE_TOKEN, VALUE =>p_factTableName,TRANSLATE => FALSE);
2388 fnd_msg_pub.add_detail(p_message_type => 'W');
2389
2390 /* FEM_ENGINES_PKG.PUT_MESSAGE(
2391 p_app_name => FEM_APP,
2392 p_msg_name => G_INVALID_DIMENSION,
2393 p_token1 => G_COLUMN_TOKEN,
2394 p_value1 => l_dimensionColumn,
2395 p_token2 => G_TABLE_TOKEN,
2396 p_value2 => p_factTableName);*/
2397 -- p_token3 => 'FND_MESSAGE_TYPE',
2398 -- p_value3 => 'W');
2399
2400 -- END IF;
2401
2402 RETURN;
2403
2404 END IF;
2405 IF (p_factTableName = 'FEM_ACTIVITIES' OR p_factTableName = 'FEM_COST_OBJECTS') THEN
2406 IF NOT compDimColumnNameIsValid(p_tableName => p_factTableName
2407 ,p_columnName => l_dimensionColumn) THEN
2408
2409 l_tokenValue :=
2410 CASE p_factTableName
2411 WHEN 'FEM_ACTIVITIES' THEN 'ACTIVITY_ID'
2412 WHEN 'FEM_COST_OBJECTS' THEN 'COST_OBJECT_ID'
2413 END;
2414
2415 fnd_message.set_name(APPLICATION => FEM_APP,NAME => G_INVALID_COMP_DIMENSION);
2416 fnd_message.set_token(TOKEN => G_COLUMN_TOKEN, VALUE => l_dimensionColumn, TRANSLATE => FALSE);
2417 fnd_message.set_token(TOKEN => G_COMP_DIM_TOKEN, VALUE =>l_tokenValue,TRANSLATE => FALSE);
2418 fnd_msg_pub.add_detail(p_message_type => 'W');
2419
2420 RETURN;
2421 END IF;
2422 END IF;
2423
2424 END IF;
2425
2426 IF (p_factTableName IS NULL) THEN
2427 l_columnPrefix := NULL;
2428 ELSE
2429 IF (p_tableAlias IS NULL) THEN
2430 l_columnPrefix := NULL;
2431 ELSE
2432 l_columnPrefix := p_tableAlias||G_PERIOD;
2433 END IF;
2434 END IF;
2435
2439 p_dimensionId => l_dimensionId);
2436 l_dataType := getColumnDataType(p_synonymName => NULL,
2437 p_columnName => NULL,
2438 p_isDimension => TRUE,
2440 --Test Value (V) case--
2441 IF (l_dimCompType = 'V') THEN
2442
2443 /*** LOCAL MAPPING CONDITION Bug 4118584****/
2444
2445 IF (l_dimensionValue = '%') THEN
2446 --RESULT WILL RETURN ALL VALUES SO JUST NO NEED TO PROCESS COMPONENT.
2447 RETURN;
2448 END IF;
2449
2450
2451 IF (l_dataType = 'STRING') THEN
2452 l_dimensionValue := ''''||l_dimensionValue||'''';
2453 ELSIF (l_dataType = 'DATE') THEN
2454 l_dimensionValue := 'TO_DATE'||G_OPEN ||''''||l_dimensionValue||''''||','||''''||l_canonicalDTMask||''''||G_CLOSE;
2455 END IF;
2456
2457
2458 generateDimValuePredicate(
2459 p_columnPrefix => l_columnPrefix,
2460 p_columnName => l_dimensionColumn,
2461 p_value => l_dimensionValue,
2462 x_stepSpecificString => l_stepSpecificString);
2463
2464 ELSIF (l_dimCompType = 'A') THEN
2465 generateDimAttributePredicate(
2466 p_dimComponentDefId => p_dimComponentDefId,
2467 p_dimensionId => l_dimensionId,
2468 p_columnPrefix => l_columnPrefix,
2469 p_columnName => l_dimensionColumn,
2470 x_stepSpecificString => l_stepSpecificString);
2471 ELSIF (l_dimCompType = 'H') THEN
2472 generateDimHierPredicate(
2473 p_dimComponentDefId => p_dimComponentDefId,
2474 p_dimensionId => l_dimensionId,
2475 p_columnPrefix => l_columnPrefix,
2476 p_columnName => l_dimensionColumn,
2477 p_effectiveDate => p_effectiveDate,
2478 x_stepSpecificString => l_stepSpecificString);
2479 END IF;
2480
2481 --MAPPING BY DIMENSION TYPE SUPPORT... MUST USE APPEND OR CONDITION TO PREDICATE
2482 IF (p_byDimensionId = l_dimensionId) THEN
2483
2484 IF (l_dataType = 'STRING') THEN
2485 l_byDimensionValue := ''''||l_byDimensionValue ||'''';
2486 ELSIF (l_dataType = 'DATE') THEN
2487 l_byDimensionValue := 'TO_DATE'||G_OPEN ||''''||l_byDimensionValue
2488 ||''''||','||''''||l_canonicalDTMask||''''||G_CLOSE;
2489 END IF;
2490
2491 l_stepSpecificString := G_OPEN||l_stepSpecificString||G_SPACE||G_OR||G_SPACE
2492 ||G_OPEN||l_columnPrefix||l_dimensionColumn||G_SPACE||G_EQUAL||G_SPACE||l_byDimensionValue
2493 ||G_CLOSE||G_CLOSE;
2494 END IF;
2495 --END MAPPING BY DIMENSION TYPE SUPPORT
2496
2497 --Add Step Predicate to Steps Predicate Table--
2498 IF (l_stepSpecificString IS NOT NULL) THEN
2499 z_dimStepPredicates(z_dimStepPredicatesCtr).DATA_DIM_COMPONENT_DEF_ID
2500 := p_dimComponentDefId;
2501 z_dimStepPredicates(z_dimStepPredicatesCtr).STEP_SPECIFIC_PREDICATE
2502 := l_stepSpecificString;
2503 z_dimStepPredicatesCtr:= z_dimStepPredicatesCtr + 1;
2504 END IF;
2505
2506 ------------------------------------------------
2507
2508 END generateDimPredicate;
2509
2510
2511 /*************************************************************************
2512
2513 GENERATE_CONDITION_PREDICATE
2514
2515 Procecure used to generate a predicate (where clause) for a given condition
2516 object based on the effective date. The predicate can be generated for
2517 either a dimension component or a data componenet, or both.
2518 *************************************************************************/
2519 PROCEDURE GENERATE_CONDITION_PREDICATE(
2520 p_api_version IN NUMBER DEFAULT g_api_version,
2521 p_init_msg_list IN VARCHAR2 DEFAULT g_false,
2522 p_commit IN VARCHAR2 DEFAULT g_false,
2523 p_encoded IN VARCHAR2 DEFAULT g_true,
2524 p_condition_obj_id IN NUMBER,
2525 p_rule_effective_date IN VARCHAR2,
2526 p_input_fact_table_name IN VARCHAR2,
2527 p_table_alias IN VARCHAR2,
2528 p_display_predicate IN VARCHAR2,
2529 p_return_predicate_type IN VARCHAR2,
2530 p_logging_turned_on IN VARCHAR2,
2531 p_by_dimension_column IN VARCHAR2,
2532 p_by_dimension_id IN NUMBER,
2533 p_by_dimension_value IN VARCHAR2,
2534 x_return_status OUT NOCOPY VARCHAR2,
2535 x_msg_count OUT NOCOPY NUMBER,
2536 x_msg_data OUT NOCOPY VARCHAR2,
2537 x_predicate_string OUT NOCOPY LONG) IS
2538
2539
2540 x EXCEPTION;
2541 l_conditionObjDefId NUMBER;
2542 l_conditionObjType VARCHAR2(30);
2543 l_ruleEffectiveDate DATE;
2544 l_tableAlias VARCHAR2(30);
2545
2546 l_sqlStmt LONG;
2547 l_numOfBindVariables NUMBER;
2548
2549 comp_cv COMP_CUR_TYPE;
2550
2551 l_componentObjId NUMBER;
2552 l_componentFlag VARCHAR2(1);
2553 l_componentObjDefId NUMBER;
2554 l_componentDimColumn VARCHAR2(30);
2555 l_componentTypeCode VARCHAR2(30);
2556 l_factTableOnDataComp VARCHAR2(30);
2557 l_hierarchyObjId NUMBER;
2558 l_componentDimId NUMBER;
2559 l_componentDimValue VARCHAR2(30);
2560 l_componentCounter NUMBER;
2561
2565 BEGIN
2562 l_dataPredicate LONG;
2563 l_dimensionPredicate LONG;
2564
2566
2567 x_return_status := g_success;
2568
2569 FND_MSG_PUB.Delete_Msg();
2570
2571 /*----Reinitializing PLSQL Tables----------*/
2572 /*--Data Step Predicate table--*/
2573 z_dataStepPredicates.DELETE;
2574 z_dataStepPredicatesCtr := 0;
2575
2576 /*--Invalid Data Component Table--*/
2577 z_invalidDataComponents.DELETE;
2578 z_invalidDataComponentsCtr := 0;
2579
2580 /*--Dimension Step Predicate table--*/
2581 z_dimStepPredicates.DELETE;
2582 z_dimStepPredicatesCtr:= 0;
2583
2584 /*--Invalid Dimension Component Table--*/
2585 z_invalidDimComponents.DELETE;
2586 z_invalidDimComponentsCtr := 0;
2587
2588 /*-----------------------------------------*/
2589
2590 x_predicate_string := '';
2591
2592 /*--Setting Global Variables--*/
2593 z_conditionObjectId := p_condition_obj_id;
2594 z_ruleEffectiveDate := p_rule_effective_date;
2595 z_factTableName := p_input_fact_table_name;
2596 z_tableAlias := p_table_alias;
2597 z_returnPredicateType := p_return_predicate_type;
2598 z_displayPredicate := p_display_predicate;
2599 z_loggingTurnedOn := p_logging_turned_on;
2600 /*----------------------------*/
2601
2602 /*----- Get Condition Obj Def ID-----*/
2603
2604 Fem_Rule_Set_Manager.Get_ValidDefinition_Pub(p_condition_obj_id
2605 ,p_rule_effective_date
2606 ,l_conditionObjDefId
2607 ,x_err_Code => z_errCode
2608 ,x_err_Msg => z_errMsg);
2609
2610
2611 IF (z_errCode <> 0) THEN
2612 RAISE NO_CONDITION_EXCEPTION;
2613 END IF;
2614
2615 z_conditionObjectDefId := l_conditionObjDefId;
2616
2617 FEM_UTILS.GetObjNameandFolderUsingDef
2618 (p_Obj_Def_ID => l_conditionObjDefId
2619 ,x_Object_Name => z_conditionObjectDefName
2620 ,x_Folder_Name => z_conditionObjectFolderName);
2621
2622 /*----------------------------------*/
2623
2624 /*----If Calling Module is not Conditions UI
2625 validate Table Name-------------------*/
2626 IF (p_input_fact_table_name IS NULL) THEN
2627 IF (p_display_predicate = 'N') THEN
2628 RAISE NO_FACT_TABLE_EXCEPTION;
2629 END IF;
2630 ELSIF (p_input_fact_table_name IS NOT NULL) THEN
2631 IF NOT tableNameIsValid(p_input_fact_table_name) THEN
2632 RAISE INVALID_FACT_TABLE_EXCEPTION;
2633 END IF;
2634 END IF;
2635 /*-------------------------------------------*/
2636
2637 /* Get Condition Object Type */
2638 --l_conditionObjType := getObjectType(p_condition_obj_id);
2639
2640 /* Get Rule Effective Date in DATE format */
2641 --l_ruleEffectiveDate:= FND_DATE.CANONICAL_TO_DATE(p_rule_effective_date);
2642
2643
2644 /* Get Valid Component SQL */
2645 l_sqlStmt:= getValidComponentSql(
2646 p_returnPredicateType => p_return_predicate_type,
2647 p_displayPredicate => p_display_predicate,
2648 p_inputFactTable => p_input_fact_table_name,
2649 x_numOfBindVariables => l_numOfBindVariables);
2650
2651
2652 /* Open Cursor */
2653 openConditionCursor(
2654 comp_cv,
2655 l_sqlStmt,
2656 l_numOfBindVariables,
2657 l_conditionObjDefId,
2658 p_display_predicate,
2659 p_input_fact_table_name);
2660
2661 l_componentCounter := 0;
2662
2663 LOOP
2664
2665 <<PROCESS_NEXT_DATA_DIM_CMP>>
2666
2667 FETCH comp_cv into
2668 l_componentObjId
2669 ,l_componentFlag
2670 ,l_componentObjDefId
2671 ,l_componentTypeCode
2672 ,l_factTableOnDataComp;
2673
2674
2675 EXIT WHEN comp_cv%NOTFOUND;
2676
2677 l_componentCounter := l_componentCounter + 1;
2678
2679 FEM_UTILS.GetObjNameandFolderUsingDef
2680 (p_Obj_Def_ID => l_componentObjDefId
2681 ,x_Object_Name => z_componentObjectDefName
2682 ,x_Folder_Name => z_componentObjectFolderName);
2683
2684 /*--Validate every Table on every Data Component
2685 if Calling Module is Conditions UI----*/
2686 /*-- IF (p_display_predicate = 'Y') THEN
2687 IF NOT tableNameIsValid(l_factTableOnDataComp) THEN
2688 --Skipping this Table,Comp Def as Table is Invalid--
2689 -- IF (z_loggingTurnedOn = 'Y') THEN
2690 trackInvalidDataComponents
2691 (p_invalidDataCmpDefId => l_componentObjDefId
2692 ,p_invalidTableName => l_factTableOnDataComp
2693 ,p_invalidColumnName => NULL
2694 ,p_invalidSecondColumnName => NULL) ;
2695 -- END IF;
2696
2697 GOTO PROCESS_NEXT_DATA_DIM_CMP;
2698 END IF;
2699 END IF;--*/
2700 /*----------------------------------------------------------*/
2701
2702 IF (l_componentFlag = 'T') THEN
2703 IF (p_display_predicate = 'Y') THEN
2704 l_tableAlias := l_factTableOnDataComp;
2705 ELSE
2706 l_tableAlias := p_table_alias;
2707 END IF;
2708 generateDataPredicate(
2712 ,p_byDimensionColumn => p_by_dimension_column
2709 p_dataComponentDefId => l_componentObjDefId
2710 ,p_factTableName => l_factTableOnDataComp
2711 ,p_tableAlias => l_tableAlias
2713 ,p_byDimensionValue => p_by_dimension_value);
2714 ELSE
2715 generateDimPredicate(
2716 p_dimComponentDefId => l_componentObjDefId
2717 ,p_factTableName => p_input_fact_table_name
2718 ,p_tableAlias => p_table_alias
2719 ,p_componentFlag => l_componentFlag
2720 ,p_effectiveDate => p_rule_effective_date
2721 ,p_byDimensionId => p_by_dimension_id
2722 ,p_byDimensionValue => p_by_dimension_value);
2723 END IF;
2724
2725 END LOOP;
2726
2727 CLOSE comp_cv;
2728
2729
2730 IF (l_componentCounter = 0) THEN
2731 RAISE NO_COMPONENTS_EXCEPTION;
2732 END IF;
2733
2734 ----Generate final Data predicate string---------
2735
2736 IF (z_dataStepPredicates.COUNT > 0) THEN
2737 FOR i IN z_dataStepPredicates.FIRST..z_dataStepPredicates.LAST LOOP
2738 IF (z_dataStepPredicates(i).STEP_SPECIFIC_PREDICATE IS NOT NULL) THEN
2739 l_dataPredicate := l_dataPredicate
2740 ||z_dataStepPredicates(i).Step_Specific_Predicate;
2741 IF (i < z_dataStepPredicates.LAST ) THEN
2742 l_dataPredicate := l_dataPredicate ||G_SPACE||G_AND||G_SPACE;
2743 END IF;
2744 END IF;
2745
2746 END LOOP;
2747 --l_dataPredicate := G_OPEN||l_dataPredicate ||G_CLOSE;
2748 END IF;
2749 --------------------------------------------------
2750 ----Generate final Dim predicate string---------
2751
2752 IF (z_dimStepPredicates.COUNT > 0) THEN
2753
2754 FOR i IN z_dimStepPredicates.FIRST..z_dimStepPredicates.LAST LOOP
2755 IF (z_dimStepPredicates(i).Step_Specific_Predicate IS NOT NULL) THEN
2756 l_dimensionPredicate := l_dimensionPredicate
2757 ||z_dimStepPredicates(i).Step_Specific_Predicate;
2758 IF (i < z_dimStepPredicates.LAST ) THEN
2759 l_dimensionPredicate := l_dimensionPredicate ||G_SPACE||G_AND||G_SPACE;
2760 END IF;
2761 END IF;
2762
2763 END LOOP;
2764 -- l_dimensionPredicate := G_OPEN||l_dimensionPredicate ||G_CLOSE;
2765 END IF;
2766 --------------------------------------------------
2767 /*---Generating Final Predicate---*/
2768
2769 IF (p_return_predicate_type = 'DATA') THEN
2770 x_predicate_string := l_dataPredicate ;
2771 ELSIF (p_return_predicate_type = 'DIM') THEN
2772 x_predicate_string := l_dimensionPredicate;
2773
2774 ELSIF (p_return_predicate_type = 'BOTH') THEN
2775 IF ((z_dataStepPredicates.COUNT > 0) AND
2776 (z_dimStepPredicates.COUNT > 0)) THEN
2777 x_predicate_string := G_OPEN||l_dataPredicate||G_CLOSE
2778 ||G_SPACE||G_AND||G_SPACE||
2779 G_OPEN||l_dimensionPredicate||G_CLOSE;
2780
2781 ELSIF ((z_dataStepPredicates.COUNT > 0) AND
2782 (z_dimStepPredicates.COUNT = 0)) THEN
2783 x_predicate_string := l_dataPredicate;
2784 ELSIF ((z_dataStepPredicates.COUNT = 0) AND
2785 (z_dimStepPredicates.COUNT > 0)) THEN
2786 x_predicate_string := l_dimensionPredicate;
2787 ELSIF ((z_dataStepPredicates.COUNT = 0) AND
2788 (z_dimStepPredicates.COUNT = 0)) THEN
2789 RAISE NO_VALID_COMPONENTS_EXCEPTION;
2790 END IF;
2791 END IF;
2792 --------------------------------------------------
2793 /*--Logging Predicate string to Concurrent Manager output ---*/
2794 /*--file for Condition UI --*/
2795
2796 IF (p_display_predicate = 'Y') THEN
2797 logPredicateToOutputFile(
2798 p_conditionObjId => p_condition_obj_id,
2799 p_returnPredicateType => p_return_predicate_type);
2800 END IF;
2801 --------------------------------------------------
2802
2803 -- x_err_code := z_errCode;
2804 --x_err_msg := z_errMsg ;
2805
2806 FND_MSG_PUB.Count_and_Get(
2807 p_encoded => p_encoded,
2808 p_count => x_msg_count,
2809 p_data => x_msg_data);
2810
2811
2812 EXCEPTION
2813 WHEN NO_CONDITION_EXCEPTION THEN
2814 FEM_ENGINES_PKG.PUT_MESSAGE(
2815 p_app_name => FEM_APP,
2816 p_msg_name => G_NO_VERSION,
2817 p_token1 => G_CONDITION_TOKEN,
2818 p_value1 => p_condition_obj_id,
2819 p_token2 => G_EFFECTIVE_DATE_TOKEN,
2820 p_value2 => p_rule_effective_date);
2821 FND_MSG_PUB.Count_and_Get(
2822 p_encoded => p_encoded,
2823 p_count => x_msg_count,
2824 p_data => x_msg_data);
2825 x_return_status := g_error;
2826 RETURN;
2827
2828 WHEN INVALID_FACT_TABLE_EXCEPTION THEN
2829
2830
2831 FEM_ENGINES_PKG.PUT_MESSAGE(
2832 p_app_name => FEM_APP,
2833 p_msg_name => G_INVALID_TABLE,
2834 p_token1 => G_TABLE_TOKEN,
2835 p_value1 => p_input_fact_table_name);
2836
2837 FND_MSG_PUB.Count_and_Get(
2838 p_encoded => p_encoded,
2839 p_count => x_msg_count,
2840 p_data => x_msg_data);
2841 x_return_status := g_error;
2842 RETURN;
2843
2844 WHEN NO_FACT_TABLE_EXCEPTION THEN
2845
2849
2846 FEM_ENGINES_PKG.PUT_MESSAGE(
2847 p_app_name => FEM_APP,
2848 p_msg_name => G_NO_TABLE);
2850 FND_MSG_PUB.Count_and_Get(
2851 p_encoded => p_encoded,
2852 p_count => x_msg_count,
2853 p_data => x_msg_data);
2854 x_return_status := g_error;
2855
2856 RETURN;
2857
2858 WHEN HIER_FLATTENED_EXCEPTION THEN
2859 FEM_ENGINES_PKG. PUT_MESSAGE(
2860 p_app_name => FEM_APP,
2861 p_msg_name => G_HIER_FLATTENED);
2862
2863 FND_MSG_PUB.Count_and_Get(
2864 p_encoded => p_encoded,
2865 p_count => x_msg_count,
2866 p_data => x_msg_data);
2867 x_return_status := g_error;
2868 RETURN;
2869
2870 WHEN NO_VALID_COMPONENTS_EXCEPTION THEN
2871
2872 FEM_ENGINES_PKG. PUT_MESSAGE(
2873 p_app_name => FEM_APP,
2874 p_msg_name => G_NO_VALID_COMPS);
2875
2876 FND_MSG_PUB.Count_and_Get(
2877 p_encoded => p_encoded,
2878 p_count => x_msg_count,
2879 p_data => x_msg_data);
2880 x_return_status := g_error;
2881
2882 RETURN;
2883
2884 WHEN INVALID_DIM_COMP_EXCEPTION THEN
2885
2886 FEM_ENGINES_PKG. PUT_MESSAGE(
2887 p_app_name => FEM_APP,
2888 p_msg_name => G_INVALID_DIM_COMPONENT,
2889 p_token1 => G_CONDITION_TOKEN,
2890 p_value1 => z_conditionObjectDefName,
2891 p_token2 => G_COMPONENT_TOKEN,
2892 p_value2 => z_componentObjectDefName);
2893
2894 FND_MSG_PUB.Count_and_Get(
2895 p_encoded => p_encoded,
2896 p_count => x_msg_count,
2897 p_data => x_msg_data);
2898
2899 x_return_status := g_unexp;
2900
2901 RETURN;
2902
2903 WHEN NO_COMPONENTS_EXCEPTION THEN
2904 FEM_ENGINES_PKG. PUT_MESSAGE(
2905 p_app_name => FEM_APP,
2906 p_msg_name => G_NO_COMPONENTS);
2907
2908 FND_MSG_PUB.Count_and_Get(
2909 p_encoded => p_encoded,
2910 p_count => x_msg_count,
2911 p_data => x_msg_data);
2912 x_return_status := g_error;
2913
2914 RETURN;
2915
2916 WHEN OTHERS THEN
2917 -- x_err_code := SQLCODE;
2918 -- x_err_msg := G_UNHANDLED_EXCEPTION;
2919 --FEM_ENGINES_PKG.USER_MESSAGE(G_UNHANDLED_EXCEPTION);
2920 FEM_ENGINES_PKG. PUT_MESSAGE(
2921 p_app_name => FEM_APP,
2922 p_msg_name => G_UNHANDLED_EXCEPTION,
2923 p_token1 => G_SQL_ERR_TOKEN,
2924 p_value1 => SQLERRM);
2925
2926 FND_MSG_PUB.Count_and_Get(
2927 p_encoded => p_encoded,
2928 p_count => x_msg_count,
2929 p_data => x_msg_data);
2930 x_return_status := g_error;
2931
2932 RETURN;
2933
2934
2935
2936 END GENERATE_CONDITION_PREDICATE;
2937
2938
2939
2940
2941 /*************************************************************************
2942
2943 DISPLAY_CONDITION_PREDICATE
2944
2945 Wrapper routine for calling the Condition API as a Concurrent Request.
2946 Used by the Conditions User Interface to log the predicate to an output
2947 file.
2948
2949 *************************************************************************/
2950
2951 PROCEDURE DISPLAY_CONDITION_PREDICATE(
2952 x_err_code OUT NOCOPY NUMBER,
2953 x_err_msg OUT NOCOPY VARCHAR2,
2954 p_condition_obj_id IN NUMBER,
2955 p_rule_effective_date IN VARCHAR2) IS
2956
2957 l_predicate_string LONG;
2958 l_return_status VARCHAR2(100);
2959
2960 l_msg_count NUMBER;
2961 l_msg_data VARCHAR2(4000);
2962 l_msg_out NUMBER;
2963 l_message VARCHAR2(4000);
2964 l_concurrent_status BOOLEAN;
2965
2966 BEGIN
2967 /*GENERATE_CONDITION_PREDICATE(
2968 x_err_code => x_err_code,
2969 x_err_msg => x_err_msg,
2970 p_condition_obj_id => p_condition_obj_id,
2971 p_rule_effective_date => p_rule_effective_date,
2972 p_input_fact_table_name => NULL,
2973 p_table_alias => NULL,
2974 p_display_predicate => 'Y',
2975 p_return_predicate_type => 'BOTH',
2976 p_logging_turned_on => 'Y',
2977 x_predicate_string => l_predicateString);*/
2978
2979
2980 GENERATE_CONDITION_PREDICATE(
2981 p_api_version => g_api_version,
2982 p_init_msg_list => g_false,
2983 p_commit => g_false,
2984 p_encoded => g_true,
2985 p_condition_obj_id => p_condition_obj_id,
2986 p_rule_effective_date => p_rule_effective_date,
2987 p_input_fact_table_name => NULL,
2988 p_table_alias => NULL,
2989 p_display_predicate => 'Y',
2990 p_return_predicate_type => 'BOTH',
2991 p_logging_turned_on => 'Y',
2992 p_by_dimension_column => NULL,
2993 p_by_dimension_id => NULL,
2994 p_by_dimension_value => NULL,
2995 x_return_status => l_return_status,
2996 x_msg_count => l_msg_count,
2997 x_msg_data => x_err_msg,
3001 l_msg_data := x_err_msg;
2998 x_predicate_string => l_predicate_string);
2999
3000
3002
3003 IF (l_msg_count = 1)
3004 THEN
3005 FND_MESSAGE.Set_Encoded(l_msg_data);
3006 l_message := FND_MESSAGE.Get;
3007
3008 FEM_ENGINES_PKG.User_Message(
3009 p_msg_text => l_message);
3010
3011
3012 ELSIF (l_msg_count > 1)
3013 THEN
3014 FOR i IN 1..l_msg_count
3015 LOOP
3016 FND_MSG_PUB.Get(
3017 p_msg_index => i,
3018 p_encoded => FND_API.G_FALSE,
3019 p_data => l_message,
3020 p_msg_index_out => l_msg_out);
3021
3022 FEM_ENGINES_PKG.User_Message(
3023 p_msg_text => l_message);
3024
3025 END LOOP;
3026 END IF;
3027
3028 FND_MSG_PUB.Initialize;
3029
3030 IF (l_return_status = g_error) THEN
3031 l_concurrent_status := fnd_concurrent.set_completion_status('ERROR',null);
3032 ELSE
3033 l_concurrent_status := fnd_concurrent.set_completion_status('NORMAL',null);
3034 END IF;
3035
3036 END DISPLAY_CONDITION_PREDICATE;
3037
3038
3039 /*************************************************************************
3040
3041 GENERATE_CONDITION_PREDICATE
3042
3043 Procecure used to generate a predicate (where clause) for a given condition
3044 object based on the effective date. The predicate can be generated for
3045 either a dimension component or a data componenet, or both.
3046 *************************************************************************/
3047
3048 PROCEDURE GENERATE_CONDITION_PREDICATE(
3049 x_err_code OUT NOCOPY NUMBER,
3050 x_err_msg OUT NOCOPY VARCHAR2,
3051 p_condition_obj_id IN NUMBER,
3052 p_rule_effective_date IN VARCHAR2,
3053 p_input_fact_table_name IN VARCHAR2,
3054 p_table_alias IN VARCHAR2,
3055 p_display_predicate IN VARCHAR2,
3056 p_return_predicate_type IN VARCHAR2,
3057 p_logging_turned_on IN VARCHAR2,
3058 x_predicate_string OUT NOCOPY LONG) IS
3059
3060 x EXCEPTION;
3061 l_conditionObjDefId NUMBER;
3062 l_conditionObjType VARCHAR2(30);
3063 l_ruleEffectiveDate DATE;
3064 l_tableAlias VARCHAR2(30);
3065
3066 l_sqlStmt LONG;
3067 l_numOfBindVariables NUMBER;
3068
3069 comp_cv COMP_CUR_TYPE;
3070
3071 l_componentObjId NUMBER;
3072 l_componentFlag VARCHAR2(1);
3073 l_componentObjDefId NUMBER;
3074 l_componentDimColumn VARCHAR2(30);
3075 l_componentTypeCode VARCHAR2(30);
3076 l_factTableOnDataComp VARCHAR2(30);
3077 l_hierarchyObjId NUMBER;
3078 l_componentDimId NUMBER;
3079 l_componentDimValue VARCHAR2(30);
3080 l_componentCounter NUMBER;
3081
3082 l_dataPredicate LONG;
3083 l_dimensionPredicate LONG;
3084
3085 BEGIN
3086
3087 /*----Reinitializing PLSQL Tables----------*/
3088 /*--Data Step Predicate table--*/
3089 z_dataStepPredicates.DELETE;
3090 z_dataStepPredicatesCtr := 0;
3091
3092 /*--Invalid Data Component Table--*/
3093 z_invalidDataComponents.DELETE;
3094 z_invalidDataComponentsCtr := 0;
3095
3096 /*--Dimension Step Predicate table--*/
3097 z_dimStepPredicates.DELETE;
3098 z_dimStepPredicatesCtr:= 0;
3099
3100 /*--Invalid Dimension Component Table--*/
3101 z_invalidDimComponents.DELETE;
3102 z_invalidDimComponentsCtr := 0;
3103
3104 /*-----------------------------------------*/
3105
3106 x_predicate_string := '';
3107
3108 /*--Setting Global Variables--*/
3109 z_conditionObjectId := p_condition_obj_id;
3110 z_ruleEffectiveDate := p_rule_effective_date;
3111 z_factTableName := p_input_fact_table_name;
3112 z_tableAlias := p_table_alias;
3113 z_returnPredicateType := p_return_predicate_type;
3114 z_displayPredicate := p_display_predicate;
3115 z_loggingTurnedOn := p_logging_turned_on;
3116 /*----------------------------*/
3117
3118 /*----- Get Condition Obj Def ID-----*/
3119
3120 Fem_Rule_Set_Manager.Get_ValidDefinition_Pub(p_condition_obj_id
3121 ,p_rule_effective_date
3122 ,l_conditionObjDefId
3123 ,x_err_Code => z_errCode
3124 ,x_err_Msg => z_errMsg);
3125
3126
3127 IF (z_errCode <> 0) THEN
3128 RAISE NO_CONDITION_EXCEPTION;
3129 END IF;
3130
3131
3132 z_conditionObjectDefId := l_conditionObjDefId;
3133
3134 FEM_UTILS.GetObjNameandFolderUsingDef
3135 (p_Obj_Def_ID => l_conditionObjDefId
3136 ,x_Object_Name => z_conditionObjectDefName
3137 ,x_Folder_Name => z_conditionObjectFolderName);
3138
3139 /*----------------------------------*/
3140
3141 /*----If Calling Module is not Conditions UI
3142 validate Table Name-------------------*/
3143 IF (p_input_fact_table_name IS NULL) THEN
3144 IF (p_display_predicate = 'N') THEN
3145 RAISE NO_FACT_TABLE_EXCEPTION;
3146 END IF;
3147 ELSIF (p_input_fact_table_name IS NOT NULL) THEN
3148 IF NOT tableNameIsValid(p_input_fact_table_name) THEN
3149 RAISE INVALID_FACT_TABLE_EXCEPTION;
3150 END IF;
3151 END IF;
3152 /*-------------------------------------------*/
3153
3157 /* Get Rule Effective Date in DATE format */
3154 /* Get Condition Object Type */
3155 --l_conditionObjType := getObjectType(p_condition_obj_id);
3156
3158 --l_ruleEffectiveDate:= FND_DATE.CANONICAL_TO_DATE(p_rule_effective_date);
3159
3160
3161 /* Get Valid Component SQL */
3162 l_sqlStmt:= getValidComponentSql(
3163 p_returnPredicateType => p_return_predicate_type,
3164 p_displayPredicate => p_display_predicate,
3165 p_inputFactTable => p_input_fact_table_name,
3166 x_numOfBindVariables => l_numOfBindVariables);
3167
3168
3169 /* Open Cursor */
3170 openConditionCursor(
3171 comp_cv,
3172 l_sqlStmt,
3173 l_numOfBindVariables,
3174 l_conditionObjDefId,
3175 p_display_predicate,
3176 p_input_fact_table_name);
3177
3178 l_componentCounter := 0;
3179
3180 LOOP
3181
3182 <<PROCESS_NEXT_DATA_DIM_CMP>>
3183
3184 FETCH comp_cv into
3185 l_componentObjId
3186 ,l_componentFlag
3187 ,l_componentObjDefId
3188 ,l_componentTypeCode
3189 ,l_factTableOnDataComp;
3190
3191
3192 EXIT WHEN comp_cv%NOTFOUND;
3193
3194 l_componentCounter := l_componentCounter + 1;
3195
3196 FEM_UTILS.GetObjNameandFolderUsingDef
3197 (p_Obj_Def_ID => l_componentObjDefId
3198 ,x_Object_Name => z_componentObjectDefName
3199 ,x_Folder_Name => z_componentObjectFolderName);
3200
3201 /*--Validate every Table on every Data Component
3202 if Calling Module is Conditions UI----*/
3203 /*-- IF (p_display_predicate = 'Y') THEN
3204 IF NOT tableNameIsValid(l_factTableOnDataComp) THEN
3205 --Skipping this Table,Comp Def as Table is Invalid--
3206 -- IF (z_loggingTurnedOn = 'Y') THEN
3207 trackInvalidDataComponents
3208 (p_invalidDataCmpDefId => l_componentObjDefId
3209 ,p_invalidTableName => l_factTableOnDataComp
3210 ,p_invalidColumnName => NULL
3211 ,p_invalidSecondColumnName => NULL) ;
3212 --END IF;
3213
3214 GOTO PROCESS_NEXT_DATA_DIM_CMP;
3215 END IF;
3216 END IF;--*/
3217 /*----------------------------------------------------------*/
3218
3219 IF (l_componentFlag = 'T') THEN
3220 IF (p_display_predicate = 'Y') THEN
3221 l_tableAlias := l_factTableOnDataComp;
3222 ELSE
3223 l_tableAlias := p_table_alias;
3224 END IF;
3225 generateDataPredicate(
3226 p_dataComponentDefId => l_componentObjDefId
3227 ,p_factTableName => l_factTableOnDataComp
3228 ,p_tableAlias => l_tableAlias
3229 ,p_byDimensionColumn => NULL
3230 ,p_byDimensionValue => NULL);
3231 ELSE
3232 generateDimPredicate(
3233 p_dimComponentDefId => l_componentObjDefId
3234 ,p_factTableName => p_input_fact_table_name
3235 ,p_tableAlias => p_table_alias
3236 ,p_componentFlag => l_componentFlag
3237 ,p_effectiveDate => p_rule_effective_date
3238 ,p_byDimensionId => NULL
3239 ,p_byDimensionValue => NULL);
3240 END IF;
3241
3242 END LOOP;
3243
3244 CLOSE comp_cv;
3245
3246
3247 IF (l_componentCounter = 0) THEN
3248 RAISE NO_COMPONENTS_EXCEPTION;
3249 END IF;
3250
3251 ----Generate final Data predicate string---------
3252
3253 IF (z_dataStepPredicates.COUNT > 0) THEN
3254 FOR i IN z_dataStepPredicates.FIRST..z_dataStepPredicates.LAST LOOP
3255 IF (z_dataStepPredicates(i).STEP_SPECIFIC_PREDICATE IS NOT NULL) THEN
3256 l_dataPredicate := l_dataPredicate
3257 ||z_dataStepPredicates(i).Step_Specific_Predicate;
3258 IF (i < z_dataStepPredicates.LAST ) THEN
3259 l_dataPredicate := l_dataPredicate ||G_SPACE||G_AND||G_SPACE;
3260 END IF;
3261 END IF;
3262
3263 END LOOP;
3264 --l_dataPredicate := G_OPEN||l_dataPredicate ||G_CLOSE;
3265 END IF;
3266 --------------------------------------------------
3267 ----Generate final Dim predicate string---------
3268
3269 IF (z_dimStepPredicates.COUNT > 0) THEN
3270
3271 FOR i IN z_dimStepPredicates.FIRST..z_dimStepPredicates.LAST LOOP
3272 IF (z_dimStepPredicates(i).Step_Specific_Predicate IS NOT NULL) THEN
3273 l_dimensionPredicate := l_dimensionPredicate
3274 ||z_dimStepPredicates(i).Step_Specific_Predicate;
3275 IF (i < z_dimStepPredicates.LAST ) THEN
3276 l_dimensionPredicate := l_dimensionPredicate ||G_SPACE||G_AND||G_SPACE;
3277 END IF;
3278 END IF;
3279
3280 END LOOP;
3281 -- l_dimensionPredicate := G_OPEN||l_dimensionPredicate ||G_CLOSE;
3282 END IF;
3283 --------------------------------------------------
3284 /*---Generating Final Predicate---*/
3285
3286 IF (p_return_predicate_type = 'DATA') THEN
3287 x_predicate_string := l_dataPredicate ;
3288 ELSIF (p_return_predicate_type = 'DIM') THEN
3289 x_predicate_string := l_dimensionPredicate;
3290
3291 ELSIF (p_return_predicate_type = 'BOTH') THEN
3292 IF ((z_dataStepPredicates.COUNT > 0) AND
3296 G_OPEN||l_dimensionPredicate||G_CLOSE;
3293 (z_dimStepPredicates.COUNT > 0)) THEN
3294 x_predicate_string := G_OPEN||l_dataPredicate||G_CLOSE
3295 ||G_SPACE||G_AND||G_SPACE||
3297
3298 ELSIF ((z_dataStepPredicates.COUNT > 0) AND
3299 (z_dimStepPredicates.COUNT = 0)) THEN
3300 x_predicate_string := l_dataPredicate;
3301 ELSIF ((z_dataStepPredicates.COUNT = 0) AND
3302 (z_dimStepPredicates.COUNT > 0)) THEN
3303 x_predicate_string := l_dimensionPredicate;
3304 ELSIF ((z_dataStepPredicates.COUNT = 0) AND
3305 (z_dimStepPredicates.COUNT = 0)) THEN
3306 RAISE NO_VALID_COMPONENTS_EXCEPTION;
3307 END IF;
3308 END IF;
3309 --------------------------------------------------
3310 /*--Logging Predicate string to Concurrent Manager output ---*/
3311 /*--file for Condition UI --*/
3312
3313 IF (p_display_predicate = 'Y') THEN
3314 logPredicateToOutputFile(
3315 p_conditionObjId => p_condition_obj_id,
3316 p_returnPredicateType => p_return_predicate_type);
3317 END IF;
3318 --------------------------------------------------
3319
3320 x_err_code := z_errCode;
3321 x_err_msg := z_errMsg ;
3322
3323 EXCEPTION
3324 WHEN NO_CONDITION_EXCEPTION THEN
3325 x_err_code := -1;
3326 x_err_msg := G_NO_VERSION;
3327 FEM_ENGINES_PKG.PUT_MESSAGE(
3328 p_app_name => FEM_APP,
3329 p_msg_name => x_err_msg,
3330 p_token1 => G_CONDITION_TOKEN,
3331 p_value1 => p_condition_obj_id,
3332 p_token2 => G_EFFECTIVE_DATE_TOKEN,
3333 p_value2 => p_rule_effective_date);
3334 RETURN;
3335
3336 WHEN INVALID_FACT_TABLE_EXCEPTION THEN
3337 x_err_code := -1;
3338 x_err_msg := G_INVALID_TABLE;
3339 FEM_ENGINES_PKG.PUT_MESSAGE(
3340 p_app_name => FEM_APP,
3341 p_msg_name => x_err_msg,
3342 p_token1 => G_TABLE_TOKEN,
3343 p_value1 => p_input_fact_table_name);
3344 RETURN;
3345
3346 WHEN NO_FACT_TABLE_EXCEPTION THEN
3347 x_err_code := -1;
3348 x_err_msg := G_NO_TABLE;
3349 FEM_ENGINES_PKG.PUT_MESSAGE(
3350 p_app_name => FEM_APP,
3351 p_msg_name => x_err_msg);
3352 RETURN;
3353
3354 WHEN HIER_FLATTENED_EXCEPTION THEN
3355 x_err_code := -1;
3356 x_err_msg := G_HIER_FLATTENED;
3357 FEM_ENGINES_PKG.PUT_MESSAGE(
3358 p_app_name => FEM_APP,
3359 p_msg_name => x_err_msg);
3360 RETURN;
3361
3362 WHEN NO_VALID_COMPONENTS_EXCEPTION THEN
3363 x_err_code := -1;
3364 x_err_msg := G_NO_VALID_COMPS;
3365 FEM_ENGINES_PKG.PUT_MESSAGE(
3366 p_app_name => FEM_APP,
3367 p_msg_name => x_err_msg);
3368 RETURN;
3369
3370 WHEN NO_COMPONENTS_EXCEPTION THEN
3371 x_err_code := -1;
3372 x_err_msg := G_NO_COMPONENTS;
3373 FEM_ENGINES_PKG.PUT_MESSAGE(
3374 p_app_name => FEM_APP,
3375 p_msg_name => x_err_msg);
3376 RETURN;
3377
3378 WHEN INVALID_DIM_COMP_EXCEPTION THEN
3379 x_err_code := -1;
3380 x_err_msg := G_INVALID_DIM_COMPONENT;
3381 FEM_ENGINES_PKG. PUT_MESSAGE(
3382 p_app_name => FEM_APP,
3383 p_msg_name => x_err_msg,
3384 p_token1 => G_CONDITION_TOKEN,
3385 p_value1 => z_conditionObjectDefName,
3386 p_token2 => G_COMPONENT_TOKEN,
3387 p_value2 => z_componentObjectDefName);
3388
3389
3390 RETURN;
3391
3392 WHEN OTHERS THEN
3393 x_err_code := SQLCODE;
3394 x_err_msg := G_UNHANDLED_EXCEPTION;
3395 --FEM_ENGINES_PKG.USER_MESSAGE(x_err_msg);
3396 FEM_ENGINES_PKG. PUT_MESSAGE(
3397 p_app_name => FEM_APP,
3398 p_msg_name => G_UNHANDLED_EXCEPTION);
3399 RETURN;
3400
3401
3402
3403 END GENERATE_CONDITION_PREDICATE;
3404
3405
3406 /*************************************************************************
3407
3408 GENERATE_CONDITION_PREDICATE
3409
3410 Procecure used to generate a predicate (where clause) for a given condition
3411 object based on the effective date. The predicate can be generated for
3412 either a dimension component or a data componenet, or both.
3413 *************************************************************************/
3414
3415 PROCEDURE GENERATE_CONDITION_PREDICATE(
3416 p_api_version IN NUMBER DEFAULT g_api_version,
3417 p_init_msg_list IN VARCHAR2 DEFAULT g_false,
3418 p_commit IN VARCHAR2 DEFAULT g_false,
3419 p_encoded IN VARCHAR2 DEFAULT g_true,
3420 p_condition_obj_id IN NUMBER,
3421 p_rule_effective_date IN VARCHAR2,
3422 p_input_fact_table_name IN VARCHAR2,
3423 p_table_alias IN VARCHAR2,
3424 p_display_predicate IN VARCHAR2,
3425 p_return_predicate_type IN VARCHAR2,
3426 p_logging_turned_on IN VARCHAR2,
3430 x_predicate_string OUT NOCOPY LONG) IS
3427 x_return_status OUT NOCOPY VARCHAR2,
3428 x_msg_count OUT NOCOPY NUMBER,
3429 x_msg_data OUT NOCOPY VARCHAR2,
3431
3432
3433
3434 BEGIN
3435
3436 GENERATE_CONDITION_PREDICATE(
3437 p_api_version => p_api_version,
3438 p_init_msg_list => p_init_msg_list,
3439 p_commit => p_commit,
3440 p_encoded => p_encoded,
3441 p_condition_obj_id => p_condition_obj_id,
3442 p_rule_effective_date => p_rule_effective_date,
3443 p_input_fact_table_name => p_input_fact_table_name,
3444 p_table_alias => p_table_alias,
3445 p_display_predicate => p_display_predicate,
3446 p_return_predicate_type => p_return_predicate_type,
3447 p_logging_turned_on => p_logging_turned_on,
3448 p_by_dimension_column => NULL,
3449 p_by_dimension_id => NULL,
3450 p_by_dimension_value => NULL,
3451 x_return_status => x_return_status,
3452 x_msg_count => x_msg_count,
3453 x_msg_data => x_msg_data,
3454 x_predicate_string => x_predicate_string);
3455
3456 END GENERATE_CONDITION_PREDICATE;
3457
3458
3459 /*============================================================================+
3460 | PROCEDURE
3461 | Generate_Dim_Hier_Query
3462 |
3463 | DESCRIPTION
3464 | This procedure returns a query string for finding a list of nodes in a
3465 | hierarchy definition based on the passed hierarchy object id,
3466 | effective date, node id (and value set id for VSR dimensions), and
3467 | relation code.
3468 |
3469 | All passed parameters are checked to ensure they are valid.
3470 |
3471 | SCOPE - PUBLIC
3472 +============================================================================*/
3473
3474 PROCEDURE Generate_Dim_Hier_Query (
3475 p_api_version in number default g_api_version
3476 ,p_init_msg_list in varchar2 default g_false
3477 ,p_commit in varchar2 default g_false
3478 ,p_encoded in varchar2 default g_true
3479 ,x_return_status out nocopy varchar2
3480 ,x_msg_count out nocopy number
3481 ,x_msg_data out nocopy varchar2
3482 ---------------------------------------------------
3483 ,p_dimension_id in number
3484 ,p_hierarchy_object_id in number
3485 ,p_effective_date in varchar2
3486 ,p_relation_code in varchar2
3487 ,p_node_id in varchar2
3488 ,p_value_set_id in number default null
3489 ,x_query_string out nocopy long
3490 )
3491 IS
3492
3493 l_api_name constant varchar2(30) := 'Generate_Dim_Hier_Query';
3494 l_api_version constant number := 1.0;
3495
3496 l_return_status varchar2(1);
3497 l_msg_count number;
3498 l_msg_data varchar2(2000);
3499
3500 l_err_code number;
3501 l_err_msg varchar2(2000);
3502
3503 l_dimension_varchar_label varchar2(30);
3504
3505 l_hierarchy_table_name varchar2(30);
3506 l_hierarchy_folder_name varchar2(150);
3507 l_hierarchy_obj_def_id number;
3508 l_hierarchy_obj_def_name varchar2(150);
3509
3510 l_value_set_required_flag varchar2(1);
3511 l_value_set_id number;
3512 l_flattened_flag varchar2(1);
3513 l_flattened_code varchar2(30);
3514
3515 l_node_exists boolean;
3516
3517 l_query_string long;
3518
3519 BEGIN
3520
3521 -- Standard Start of API Savepoint
3522 savepoint Generate_Dim_Hier_Query_PVT;
3523
3524 -- Standard call to check for call compatibility
3525 if not FND_API.Compatible_API_Call (
3526 p_current_version_number => l_api_version
3527 ,p_caller_version_number => p_api_version
3528 ,p_api_name => l_api_name
3529 ,p_pkg_name => G_PKG_NAME
3530 ) then
3531 raise FND_API.G_EXC_UNEXPECTED_ERROR;
3532 end if;
3533
3534 -- Initialize Message Stack on FND_MSG_PUB
3535 if (FND_API.To_Boolean(p_init_msg_list)) then
3536 FND_MSG_PUB.Initialize;
3537 end if;
3538
3539 FEM_ENGINES_PKG.Tech_Message (
3540 p_severity => G_LOG_LEVEL_PROCEDURE
3541 ,p_module => G_BLOCK||'.'||l_api_name
3542 ,p_msg_text => 'BEGIN'
3543 );
3544
3545 -- Initialize API return status to success
3546 x_return_status := FND_API.G_RET_STS_SUCCESS;
3547
3548 if (p_dimension_id is null) then
3549 FEM_ENGINES_PKG.PUT_MESSAGE (
3550 p_app_name => G_FEM
3551 ,p_msg_name => G_NULL_PARAM_VALUE_ERR
3552 ,p_token1 => G_PARAM_TOKEN
3553 ,p_value1 => 'p_dimension_id'
3554 );
3555 raise FND_API.G_EXC_ERROR;
3556 end if;
3557
3558 if (p_hierarchy_object_id is null) then
3559 FEM_ENGINES_PKG.PUT_MESSAGE (
3560 p_app_name => G_FEM
3561 ,p_msg_name => G_NULL_PARAM_VALUE_ERR
3562 ,p_token1 => G_PARAM_TOKEN
3563 ,p_value1 => 'p_hierarchy_object_id'
3564 );
3568 if (p_effective_date is null) then
3565 raise FND_API.G_EXC_ERROR;
3566 end if;
3567
3569 FEM_ENGINES_PKG.PUT_MESSAGE (
3570 p_app_name => G_FEM
3571 ,p_msg_name => G_NULL_PARAM_VALUE_ERR
3572 ,p_token1 => G_PARAM_TOKEN
3573 ,p_value1 => 'p_effective_date'
3574 );
3575 raise FND_API.G_EXC_ERROR;
3576 end if;
3577
3578 if (p_relation_code is null) then
3579 FEM_ENGINES_PKG.PUT_MESSAGE (
3580 p_app_name => G_FEM
3581 ,p_msg_name => G_NULL_PARAM_VALUE_ERR
3582 ,p_token1 => G_PARAM_TOKEN
3583 ,p_value1 => 'p_relation_code'
3584 );
3585 raise FND_API.G_EXC_ERROR;
3586 end if;
3587
3588 if (p_node_id is null) then
3589 FEM_ENGINES_PKG.PUT_MESSAGE (
3590 p_app_name => G_FEM
3591 ,p_msg_name => G_NULL_PARAM_VALUE_ERR
3592 ,p_token1 => G_PARAM_TOKEN
3593 ,p_value1 => 'p_node_id'
3594 );
3595 raise FND_API.G_EXC_ERROR;
3596 end if;
3597
3598 -- get hierarchy obj def id for object
3599 Fem_Rule_Set_Manager.Get_ValidDefinition_Pub (
3600 p_object_id => p_hierarchy_object_id
3601 ,p_rule_effective_date => p_effective_date
3602 ,x_object_definition_id => l_hierarchy_obj_def_id
3603 ,x_err_code => l_err_code
3604 ,x_err_msg => l_err_msg
3605 );
3606
3607 -- error check: halt processing on this step if valid hier def not found.
3608 if (l_err_code <> 0) then
3609 --Message has been logged in Fem_Rule_Set_Manager.Get_ValidDefinition_Pub
3610 raise FND_API.G_EXC_ERROR;
3611 end if;
3612
3613 -- Check to see if this is a valid hierarchy dimension
3614 begin
3615 select a.hierarchy_table_name
3616 ,value_set_required_flag
3617 ,dimension_varchar_label
3618 into l_hierarchy_table_name
3619 ,l_value_set_required_flag
3620 ,l_dimension_varchar_label
3621 from fem_xdim_dimensions_vl a
3622 where a.dimension_id = p_dimension_id;
3623 exception
3624 when others then
3625 -- Invalid Dimension Id
3626 FEM_ENGINES_PKG.PUT_MESSAGE (
3627 p_app_name => G_FEM
3628 ,p_msg_name => G_BAD_DIM_ID_ERR
3629 ,p_token1 => G_DIM_ID_TOKEN
3630 ,p_value1 => p_dimension_id
3631 );
3632 raise FND_API.G_EXC_ERROR;
3633 end;
3634
3635 if (l_hierarchy_table_name is null) then
3636 -- Invalid Dimension
3637 FEM_ENGINES_PKG.PUT_MESSAGE (
3638 p_app_name => G_FEM
3639 ,p_msg_name => G_DIM_BAD_DIM_LABEL
3640 ,p_token1 => G_DIM_LABEL_TOKEN
3641 ,p_value1 => l_dimension_varchar_label
3642 );
3643 raise FND_API.G_EXC_ERROR;
3644 end if;
3645
3646 -- Validate the Value Set Id for VSR dimensions
3647 if (l_value_set_required_flag = 'Y') then
3648
3649 l_value_set_id := p_value_set_id;
3650 if (l_value_set_id is null) then
3651
3652 l_value_set_id := getHierValueSetId (
3653 p_dimension_id => p_dimension_id
3654 ,p_hierarchy_obj_id => p_hierarchy_object_id
3655 );
3656
3657 if (l_value_set_id is null) then
3658
3659 -- Value Set Id cannot be null for VSR dimensions
3660 FEM_ENGINES_PKG.PUT_MESSAGE (
3661 p_app_name => G_FEM
3662 ,p_msg_name => G_NULL_PARAM_VALUE_ERR
3663 ,p_token1 => G_PARAM_TOKEN
3664 ,p_value1 => 'p_value_set_id'
3665 );
3666 raise FND_API.G_EXC_ERROR;
3667
3668 end if;
3669
3670 end if;
3671
3672 end if;
3673
3674 -- Check if hierarchy will be flattened
3675 select flattened_rows_flag
3676 into l_flattened_flag
3677 from fem_hierarchies
3678 where hierarchy_obj_id = p_hierarchy_object_id;
3679
3680 if (l_flattened_flag = 'Y') then
3681
3682 -- If hierarchy is flattened, make sure flattening is complete
3683 select flattened_rows_completion_code
3684 into l_flattened_code
3685 from fem_hier_definitions
3686 where hierarchy_obj_def_id = l_hierarchy_obj_def_id;
3687
3688 if (l_flattened_code <> 'COMPLETED') then
3689 FEM_ENGINES_PKG.PUT_MESSAGE (
3690 p_app_name => G_FEM
3691 ,p_msg_name => G_HIER_FLATTENED
3692 );
3693 raise FND_API.G_EXC_ERROR;
3694 end if;
3695
3696 end if;
3697
3698 l_node_exists := validateHierarchyNode (
3699 p_tableName => l_hierarchy_table_name
3700 ,p_node => p_node_id
3701 ,p_value_set_id => l_value_set_id
3702 ,p_objId => p_hierarchy_object_id
3703 ,p_objDefId => l_hierarchy_obj_def_id
3704 ,p_effectiveDate => p_effective_date
3705 );
3706
3707 if not l_node_exists then
3708
3709 FEM_UTILS.GetObjNameandFolderUsingDef (
3710 p_obj_def_id => l_hierarchy_obj_def_id
3711 ,x_object_name => l_hierarchy_obj_def_name
3712 ,x_folder_name => l_hierarchy_folder_name
3713 );
3714
3715 FEM_ENGINES_PKG.PUT_MESSAGE (
3716 p_app_name => G_FEM
3717 ,p_msg_name => G_INVALID_NODE
3718 ,p_token1 => G_NODE_TOKEN
3719 ,p_value1 => p_node_id
3720 ,p_token2 => G_HIERARCHY_TOKEN
3724 raise FND_API.G_EXC_ERROR;
3721 ,p_value2 => l_hierarchy_obj_def_name
3722 );
3723
3725
3726 end if;
3727
3728 if (l_flattened_flag = 'Y') then
3729
3730 l_query_string := getDimFlatHierQuery (
3731 p_hierarchy_table_name => l_hierarchy_table_name
3732 ,p_relation_code => p_relation_code
3733 ,p_hierarchy_obj_def_id => l_hierarchy_obj_def_id
3734 ,p_node_list => p_node_id
3735 ,p_value_set_id => l_value_set_id
3736 );
3737
3738 else
3739
3740 l_query_string := getDimHierQuery (
3741 p_hierarchy_table_name => l_hierarchy_table_name
3742 ,p_relation_code => p_relation_code
3743 ,p_hierarchy_obj_def_id => l_hierarchy_obj_def_id
3744 ,p_node_list => p_node_id
3745 ,p_value_set_id => l_value_set_id
3746 ,p_effective_date_varchar => p_effective_date
3747 );
3748
3749 end if;
3750
3751 if (l_query_string is null) then
3752
3753 FEM_ENGINES_PKG.PUT_MESSAGE (
3754 p_app_name => G_FEM
3755 ,p_msg_name => G_INVALID_RELATION
3756 ,p_token1 => G_RELATION_TOKEN
3757 ,p_value1 => p_relation_code
3758 );
3759
3760 raise FND_API.G_EXC_ERROR;
3761
3762 end if;
3763
3764 x_query_string := l_query_string;
3765
3766 -----------------------
3767 -- Finalize API Call --
3768 -----------------------
3769 -- Standard check of p_commit
3770 if FND_API.To_Boolean(p_commit) then
3771 commit work;
3772 end if;
3773
3774 -- Standard call to get message count and if count is 1, get message info
3775 FND_MSG_PUB.Count_And_Get (
3776 p_count => x_msg_count
3777 ,p_data => x_msg_data
3778 );
3779
3780 FEM_ENGINES_PKG.Tech_Message (
3781 p_severity => G_LOG_LEVEL_PROCEDURE
3782 ,p_module => G_BLOCK||'.'||l_api_name
3783 ,p_msg_text => 'END'
3784 );
3785
3786 EXCEPTION
3787
3788 when FND_API.G_EXC_ERROR then
3789 rollback to Generate_Dim_Hier_Query_PVT;
3790 x_return_status := FND_API.G_RET_STS_ERROR;
3791 FND_MSG_PUB.Count_And_Get(
3792 p_count => x_msg_count
3793 ,p_data => x_msg_data
3794 );
3795
3796 when FND_API.G_EXC_UNEXPECTED_ERROR then
3797 rollback to Generate_Dim_Hier_Query_PVT;
3798 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3799 FND_MSG_PUB.Count_And_Get(
3800 p_count => x_msg_count
3801 ,p_data => x_msg_data
3802 );
3803
3804 when others then
3805 rollback to Generate_Dim_Hier_Query_PVT;
3806 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3807 if (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)) then
3808 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
3809 end if;
3810 FND_MSG_PUB.Count_And_Get(
3811 p_count => x_msg_count
3812 ,p_data => x_msg_data
3813 );
3814
3815 END Generate_Dim_Hier_Query;
3816
3817
3818 /*============================================================================+
3819 | FUNCTION
3820 | Get_Dim_Member_Display_Code
3821 |
3822 | DESCRIPTION
3823 | This Function returns the Member Display Code for a given Dimension ID
3824 | and Member ID. This Method will be used in queries so that's why method
3825 | is not using Out Parameters like x_return_status, x_message_count,
3826 | and x_msg_data to get the status.
3827 |
3828 | SCOPE - PUBLIC
3829 +============================================================================*/
3830
3831 FUNCTION Get_Dim_Member_Display_Code (
3832 p_dimension_id in number
3833 ,p_member_id in varchar2
3834 ) RETURN varchar2
3835 IS
3836
3837 l_member_display_code varchar2(150):= null;
3838
3839 BEGIN
3840
3841 select FEM_DIMENSION_UTIL_PKG.Get_Dim_Member_Display_Code(
3842 xdim.dimension_id
3843 ,p_member_id
3844 ,decode(xdim.value_set_required_flag
3845 ,'Y',FEM_DIMENSION_UTIL_PKG.Dimension_Value_Set_Id(xdim.dimension_id)
3846 ,null))
3847 into l_member_display_code
3848 from fem_xdim_dimensions_vl xdim
3849 where xdim.dimension_id = p_dimension_id;
3850
3851 return l_member_display_code;
3852
3853 EXCEPTION
3854
3855 when others then
3856 return null;
3857
3858 END Get_Dim_Member_Display_Code;
3859
3860
3861 /*============================================================================+
3862 | FUNCTION
3863 | Get_Dim_Member_Name
3864 |
3865 | DESCRIPTION
3866 | This Function returns the Member Name for a given Dimension ID and
3867 | Member ID. This Method will be used in queries so that's why method
3868 | is not using Out Parameters like x_return_status, x_message_count,
3869 | x_msg_data to get the status.
3870 |
3871 | SCOPE - PUBLIC
3872 +============================================================================*/
3873
3874 FUNCTION Get_Dim_Member_Name (
3875 p_dimension_id in number
3876 ,p_member_id in varchar2
3877 ) RETURN varchar2
3878 IS
3879
3880 l_member_name varchar2(150):= null;
3881
3882 BEGIN
3883
3884 select FEM_DIMENSION_UTIL_PKG.Get_Dim_Member_Name(
3885 xdim.dimension_id
3886 ,p_member_id
3887 ,decode(xdim.value_set_required_flag
3888 ,'Y',FEM_DIMENSION_UTIL_PKG.Dimension_Value_Set_Id(xdim.dimension_id)
3889 ,null))
3890 into l_member_name
3891 from fem_xdim_dimensions_vl xdim
3892 where xdim.dimension_id = p_dimension_id;
3893
3894 return l_member_name;
3895
3896 EXCEPTION
3897
3898 when others then
3899 return null;
3900
3901 END Get_Dim_Member_Name;
3902
3903
3904 END FEM_CONDITIONS_API;