1 Package Body BSC_DBGEN_BSC_READER AS
2 /* $Header: BSCBSRDB.pls 120.19.12000000.2 2007/02/14 10:36:51 rkumar ship $*/
3
4 g_sys_measures BSC_DBGEN_STD_METADATA.tab_clsMeasure;
5 g_sys_measures_loaded boolean :=false;
6 g_error VARCHAR2(4000);
7
8 procedure init is
9 begin
10 if g_initialized=false then
11 bsc_apps.Init_bsc_apps;
12 bsc_apps.Init_Big_In_Cond_Table;
13 g_initialized := true;
14 end if;
15 end;
16
17 --****************************************************************************
18 -- IsIndicatorPnL
19 -- DESCRIPTION:
20 -- Return TRUE if the indicator is type PnL
21 -- PARAMETERS:
22 -- Ind: Indicator code
23 --
24 -- AUTHOR/DATE - MODIFICATIONS (AUTHOR/DATE/DESCRIPTION):
25 --***************************************************************************
26 Function IsIndicatorPnL(Ind IN Integer) return Boolean IS
27 CURSOR cBalance IS
28 SELECT indicator_type, config_type FROM BSC_KPIS_VL where INDICATOR= Ind;
29 l_indicator_type NUMBER;
30 l_config_type NUMBER;
31 BEGIN
32 If l_indicator_type = 1 And l_config_type = 3 Then
33 return true;
34 Else
35 return false;
36 END IF;
37 EXCEPTION WHEN OTHERS THEN
38 fnd_file.put_line(fnd_file.log, 'Exception in IsIndicatorPnL for '||Ind||' : '||sqlerrm);
39 RAISE;
40 End;
41
42 --****************************************************************************
43 -- EsIndicatorBalance
44 --
45 -- DESCRIPTION:
46 -- Returns TRUE is the indicator is type Balance
47 --
48 -- PARAMETERS:
49 -- Ind: Indicator code
50 --
51 -- AUTHOR/DATE - MODIFICATIONS (AUTHOR/DATE/DESCRIPTION):
52 --***************************************************************************
53 Function IsIndicatorBalance(Ind IN NUMBER) return Boolean IS
54 CURSOR cBalance IS
55 SELECT indicator_type, config_type FROM BSC_KPIS_VL where INDICATOR= Ind;
56 l_indicator_type NUMBER;
57 l_config_type NUMBER;
58 BEGIN
59 OPEN cBalance;
60 FETCH cBalance INTO l_indicator_type, l_config_type;
61 CLOSE cBalance;
62
63 If l_indicator_type = 1 And l_Config_Type = 2 Then
64 return true;
65 Else
66 return false;
67 END IF;
68 EXCEPTION WHEN OTHERS THEN
69 fnd_file.put_line(fnd_file.log, 'Exception in IsIndicatorBalance for '||ind||' : '||sqlerrm);
70 RAISE;
71 End;
72
73 --****************************************************************************
74 -- IsIndicatorBalanceOrPnL : EsIndicatorBalanceoPyg
75 --
76 -- DESCRIPTION:
77 -- Return TRUE if the indicator is type Balance or PnL
78 --
79 -- PARAMETERS:
80 -- Ind: indicator code
81 --
82 -- AUTHOR/DATE - MODIFICATIONS (AUTHOR/DATE/DESCRIPTION):
83 --***************************************************************************
84 Function IsIndicatorBalanceOrPnL(Ind IN Integer) return Boolean IS
85 CURSOR cBalance IS
86 SELECT indicator_type, config_type FROM BSC_KPIS_VL where INDICATOR= Ind;
87 l_indicator_type NUMBER;
88 l_config_type NUMBER;
89 BEGIN
90 OPEN cBalance;
91 FETCH cBalance INTO l_indicator_type, l_config_type;
92 CLOSE cBalance;
93 If l_indicator_type = 1 And (l_Config_Type = 2 OR l_Config_Type = 3) Then
94 return true;
95 Else
96 return false;
97 END IF;
98 EXCEPTION WHEN OTHERS THEN
99 fnd_file.put_line(fnd_file.log, 'Exception in IsIndicatorBalanceOrPnL for '||ind||' : '||sqlerrm);
100 RAISE;
101 End;
102
103 --***************************************************************************
104 -- DESCRIPTION:
105 -- Get the number of data columns of the indicator for the given
106 -- dimension set.
107 -- PARAMETERS:
108 -- Indic: indicator code
109 -- AUTHOR/DATE - MODIFICATIONS (AUTHOR/DATE/DESCRIPTION):
110 --**************************************************************************
111 Function get_num_measures(p_indicator IN NUMBER, p_dim_set IN NUMBER) RETURN NUMBER IS
112 l_num_measures number;
113 CURSOR cNumCols IS
114 SELECT COUNT(M.MEASURE_COL) NUM_DATA_COLUMNS
115 FROM BSC_SYS_MEASURES M, BSC_DB_MEASURE_BY_DIM_SET_V I
116 WHERE I.MEASURE_ID = M.MEASURE_ID
117 AND I.DIM_SET_ID = p_dim_set
118 AND I.INDICATOR = p_indicator
119 AND M.TYPE = 0 AND NVL(M.SOURCE, 'BSC') in ( 'BSC', 'PMF');
120 BEGIN
121 OPEN cNumCols;
122 FETCH cNumCols INTO l_num_measures;
123 CLOSE cNumCols;
124 return l_num_measures;
125 EXCEPTION WHEN OTHERS THEN
126 raise;
127 End;
128
129
130 PROCEDURE load_sys_measures IS
131 l_stmt varchar2(1000);
132 l_measure BSC_DBGEN_STD_METADATA.clsMeasure;
133 l_measure_null BSC_DBGEN_STD_METADATA.clsMeasure;
134 --BSC-PMF Integration: Need to filter out PMF measures
135 CURSOR c1 IS
136 SELECT ms.MEASURE_COL, ms.HELP, ms.MEASURE_GROUP_ID, ms.PROJECTION_ID, NVL(ms.MEASURE_TYPE, 1) MTYPE, sysm.source
137 FROM BSC_DB_MEASURE_COLS_VL ms,
138 BSC_SYS_MEASURES sysm
139 WHERE ms.measure_col = sysm.measure_col
140 ORDER BY MEASURE_COL;
141 cRow c1%ROWTYPE;
142
143 BEGIN
144
145 OPEN c1;
146 LOOP
147 FETCH c1 INTO cRow;
148 EXIT WHEN c1%NOTFOUND;
149 l_measure := l_measure_null;
150 l_measure.measure_name := cRow.MEASURE_COL;
151 IF (cRow.HELP IS NOT NULL) THEN
152 l_measure.Description := substr(cRow.HELP, 1,240);
153 END IF;
154 IF (cRow.MEASURE_GROUP_ID IS NOT NULL) THEN
155 l_measure.measure_group := cRow.MEASURE_GROUP_ID;
156 ELSE
157 l_measure.measure_group := -1;
158 END IF;
159 l_measure.measure_source := cRow.source;
160 --projection method of the field
161 --0: No Forecast
162 --1: Moving Averge
163 --2: Plan-Based (not used any more)
164 --3: Plan-Based
165 --4: Custom
166 IF (cRow.PROJECTION_ID IS NOT NULL) THEN
167 bsc_dbgen_utils.add_property(l_measure.properties, BSC_DBGEN_STD_METADATA.PROJECTION_ID, to_char(cRow.PROJECTION_ID));
168 Else
169 bsc_dbgen_utils.add_property(l_measure.properties, BSC_DBGEN_STD_METADATA.PROJECTION_ID, 0);--no projection
170 END IF;
171 l_measure.measure_type := cRow.MTYPE;
172 g_sys_measures(g_sys_measures.count+1) := l_measure;
173 END Loop;
174 CLOSE c1;
175 g_sys_measures_loaded := true;
176 return;
177 exception when others then
178 l_stmt := sqlerrm;
179 bsc_mo_helper_pkg.writeTmp('Exception in load_system_measures: '||l_stmt, FND_LOG.LEVEL_UNEXPECTED, true);
180 bsc_mo_helper_pkg.TerminateWithError('BSC_VAR_LIST_INIT_FAILED', 'load_system_measures');
181 raise;
182 End;
183
184 --****************************************************************************
185 --sys_measure_exists
186 --
187 -- DESCRIPTION:
188 -- Return TRUE if the given field exist in the collection gLov
189 --
190 -- PARAMETERS:
191 -- measure: field name
192 --
193 -- AUTHOR/DATE - MODIFICATIONS (AUTHOR/DATE/DESCRIPTION):
194 --***************************************************************************
195
196 Function sys_measure_exists(p_measure_name IN VARCHAR2, p_measure_source IN VARCHAR2) RETURN BOOLEAN IS
197 l_measure BSC_DBGEN_STD_METADATA.clsMeasure;
198 i NUMBER ;
199 BEGIN
200 IF (g_sys_measures_loaded=false) THEN
201 load_sys_measures;
202 END IF;
203 IF (g_sys_measures.count = 0) THEN
204 return false;
205 END IF;
206 i := g_sys_measures.first;
207 LOOP
208 l_measure := g_sys_measures(i);
209 IF (upper(l_measure.measure_Name) = upper(p_measure_name) AND upper(l_measure.measure_source) = upper(p_measure_source) ) THEN
210 return True;
211 END IF;
212 EXIT WHEN i = g_sys_measures.last;
213 i := g_sys_measures.next(i);
214 END LOOP;
215 return false;
216 EXCEPTION WHEN OTHERS THEN
217 fnd_file.put_line(fnd_file.log, 'Exception in sys_measure_exists for field '||p_measure_name||' : '||sqlerrm);
218 raise;
219 End ;
220
221 --****************************************************************************
222 --measure_exists :
223 -- DESCRIPTION:
224 -- Returns TRUE if the field exist in the collection. The collection
225 -- if of objects of class clsCampoDatos
226 --
227 -- PARAMETERS:
228 -- colMeasures: collection
229 -- measure: field name
230 -- AUTHOR/DATE - MODIFICATIONS (AUTHOR/DATE/DESCRIPTION):
231 --***************************************************************************
232 Function measure_exists(p_measure_collection IN BSC_DBGEN_STD_METADATA.tab_clsMeasure, p_measure_name IN VARCHAR2, p_measure_source IN VARCHAR2)
233 RETURN BOOLEAN IS
234 l_measure BSC_DBGEN_STD_METADATA.clsMeasure;
235 i NUMBER;
236
237 BEGIn
238
239 IF p_measure_collection.count = 0 THEN
240 return FALSE;
241 END IF;
242
243 i := p_measure_collection.first;
244 LOOP
245 l_measure := p_measure_collection(i);
246 If (UPPER(l_measure.measure_name) = UPPER(p_measure_name) AND upper(l_measure.measure_source)=upper(p_measure_source)) Then
247 return true;
248 END IF;
249 EXIT WHEN i = p_measure_collection.last;
250 i := p_measure_collection.next(i);
251 END LOOP;
252 return false;
253
254 EXCEPTION WHEN OTHERS THEN
255 Fnd_File.Put_Line(Fnd_File.Log, 'Exception dataFieldExists, '||sqlerrm);
256 raise;
257 End;
258
259
260
261 --****************************************************************************
262 --InsertDataColumnInDBMeasureCols
263 --
264 -- DESCRIPTION:
265 -- Creates the record for the internal column in BSC_DB_MEASURE_COLS_TL
266 --
267 -- AUTHOR/DATE - MODIFICATIONS (AUTHOR/DATE/DESCRIPTION):
268 --***************************************************************************
269 PROCEDURE InsertInDBMeasureCols(P_Measure_Col IN BSC_METADATA_OPTIMIZER_PKG.clsMeasureLov) IS
270
271 l_stmt VARCHAR2(1000);
272 i NUMBER;
273
274 BEGIN
275 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
276 bsc_mo_helper_pkg.writeTmp( 'Inside InsertInDBMeasureCols, P_Measure_Col = ');
277 END IF;
278
279 bsc_mo_helper_pkg.write_this(P_Measure_Col);
280 --Delete the records if exists
281 l_stmt := 'DELETE FROM BSC_DB_MEASURE_COLS_TL WHERE MEASURE_COL = :1';
282 EXECUTE IMMEDIATE l_stmt using P_Measure_Col.fieldName;
283
284 --Because it is a TL table, we need to insert the record for every supported language
285 i := BSC_METADATA_OPTIMIZER_PKG.gInstalled_Languages.first;
286
287 LOOP
288 EXIT WHEN BSC_METADATA_OPTIMIZER_PKG.gInstalled_Languages.count = 0;
289 INSERT INTO BSC_DB_MEASURE_COLS_TL (
290 MEASURE_COL, LANGUAGE, SOURCE_LANG,
291 HELP, MEASURE_GROUP_ID, PROJECTION_ID, MEASURE_TYPE)
292 VALUES (P_Measure_Col.fieldName, BSC_METADATA_OPTIMIZER_PKG.gInstalled_Languages(i), BSC_METADATA_OPTIMIZER_PKG.gLangCode,
293 P_Measure_Col.Description, P_Measure_Col.groupCode, P_Measure_Col.prjMethod,P_Measure_Col.measureType );
294 EXIT WHEN i = BSC_METADATA_OPTIMIZER_PKG.gInstalled_Languages.last;
295 i := BSC_METADATA_OPTIMIZER_PKG.gInstalled_Languages.next(i);
296 END LOOP;
297 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
298 bsc_mo_helper_pkg.writeTmp( 'Compl. InsertInDBMeasureCols');
299 END IF;
300
301
302 EXCEPTION WHEN OTHERS THEN
303 g_error := sqlerrm;
304 Fnd_File.Put_Line(Fnd_File.Log, 'Exception in InsertInDBMeasureCols '||g_error);
305 RAISE;
306
307 End;
308
309
310 --****************************************************************************
311 --AddInternalColumnInDB
312 --
313 -- DESCRIPTION:
314 -- Creates the record for the internal column in BSC_DB_MEASURE_COLS_TL
315 -- and also added to the collection gLov.
316 -- Projection method and type (balance or statistic) are deduced from
317 -- the base columns.
318 --
319 -- AUTHOR/DATE - MODIFICATIONS (AUTHOR/DATE/DESCRIPTION):
320 --***************************************************************************
321 /*
322 PROCEDURE AddInternalColumnInDB(internalColumn IN VARCHAR2, InternalColumnType NUMBER,
323 baseColumns IN dbms_sql.varchar2_table , numBaseColumns IN NUMBER) IS
324 L_Measure_Col BSC_METADATA_OPTIMIZER_PKG.clsmeasureLov;
325 i NUMBER;
326 prjMethod NUMBER;
327
328
329 l_temp number;
330 BEGIN
331
332 L_Measure_Col.fieldName := internalColumn;
333 l_temp := bsc_mo_helper_pkg.findIndex(BSC_METADATA_OPTIMIZER_PKG.gLov, baseColumns(baseColumns.first));
334 L_Measure_Col.groupCode := BSC_METADATA_OPTIMIZER_PKG.gLov(l_temp).groupCode;
335 L_Measure_Col.Description := BSC_MO_HELPER_PKG.Get_LookUp_Value('BSC_UI_BACKEND', 'INTERNAL_COLUMN');
336
337 IF (InternalColumnType =1) THEN
338 --Formula
339 --The projection method of the calculated column is deduced from the
340 --projection method of the operands:
341 --If the projection method for one of the operands is 'No forecast'
342 --then the projection method for the calculated column is 'No forecast'
343 --Else, If the projection method of one of the operands is 'Custom' then:
344 --If the projection method of one of the operands is 'Plan-based' then
345 --the projection method of the calculated column is 'Plan-based'
346 --Else, the projection method is 'Moving Average'
347 --Else, if the projection method of one of the operands is 'Plan-based' then
348 --the projection method of the calculated column is 'Plan-based'.
349 --Else, the projection method of the calculated column is 'Moving Average'
350 --Projection methods:
351 --0: No Forecast
352 --1: Moving Averge
353 --3: Plan-Based
354 --4: Custom
355
356 L_Measure_Col.prjMethod := 1; --Moving average has the lowest priority
357 i := baseColumns.first;
358 LOOP
359 EXIT WHEN baseColumns.count = 0;
360 l_temp := bsc_mo_helper_pkg.findIndex(BSC_METADATA_OPTIMIZER_PKG.gLov, baseColumns(i));
361 prjMethod := BSC_METADATA_OPTIMIZER_PKG.gLov(l_temp).prjMethod;
362
363 If prjMethod = 0 Then
364 --No forecast
365 L_Measure_Col.prjMethod := 0;
366 EXIT;
367 END IF;
368
369 If prjMethod = 3 Then
370 --Plan-Based
371 L_Measure_Col.prjMethod := 3;
372 Else
373 --Moving Average of Custom
374 If L_Measure_Col.prjMethod <> 3 Then
375 L_Measure_Col.prjMethod := 1;
376 END IF;
377 END IF;
378 EXIT WHEN i = baseColumns.last;
379 i := baseColumns.next(i);
380 END LOOP;
381
382 --The type of the calculated column (Balance or Statistics) is
383 --deduced from the type of the operands. If at least one of the operands
384 --is Balance Type, then the calculated column is Balance.
385 --Measure types:
386 --1: Statistic
387 --2: Balance
388 i := baseColumns.first;
389 LOOP
390 EXIT WHEN baseColumns.count = 0;
391 l_temp := bsc_mo_helper_pkg.findIndex(BSC_METADATA_OPTIMIZER_PKG.gLov, baseColumns(i));
392 L_Measure_Col.measureType := BSC_METADATA_OPTIMIZER_PKG.gLov(l_temp).measureType;
393 If L_Measure_Col.measureType = 2 Then
394 EXIT;
395 END IF;
396 EXIT WHEN i = baseColumns.last;
397 i := baseColumns.next(i);
398 END LOOP;
399 ELSIF (InternalColumnType=2 OR InternalColumnType=3) THEN
400 --Total and counter for Average at Lowest Level
401
402 --Projection method and type are the same of the base column
403 --In this case there is only one base column
404 l_temp := bsc_mo_helper_pkg.findIndex(BSC_METADATA_OPTIMIZER_PKG.gLov, baseColumns(baseColumns.first));
405 L_Measure_Col.prjMethod := BSC_METADATA_OPTIMIZER_PKG.gLov(l_temp).prjMethod;
406 L_Measure_Col.measureType := BSC_METADATA_OPTIMIZER_PKG.gLov(l_temp).measureType;
407 END IF;
408
409 If Not sys_measure_exists(internalColumn, 'BSC') Then
410 IF (g_sys_measures.count>0) THEN
411 g_sys_measures(g_sys_measures.last+1) := L_Measure_Col;
412 ELSE
413 g_sys_measures(1) := L_Measure_Col;
414 END IF;
415 Else
416 --Update the filed with the new information
417 l_temp := bsc_mo_helper_pkg.findIndex(g_sys_measures, internalColumn);
418 g_sys_measures(l_temp).groupCode := L_Measure_Col.groupCode;
419 g_sys_measures(l_temp).Description := L_Measure_Col.Description;
420 g_sys_measures(l_temp).measureType := L_Measure_Col.measureType;
421 g_sys_measures(l_temp).prjMethod := L_Measure_Col.prjMethod;
422 END IF;
423 InsertInDBMeasureCols( L_Measure_Col);
424
425 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
426 bsc_mo_helper_pkg.writeTmp( 'Compl AddInternalColumnInDB');
427 END IF;
428
429
430 EXCEPTION WHEN OTHERS THEN
431 g_error := sqlerrm;
432 Fnd_File.Put_Line(Fnd_File.Log, 'Exception in AddInternalColumnInDB : '||g_error);
433 raise;
434 End;
435 */
436
437
438 --****************************************************************************
439 --SetMeasurePropertyDB
440 -- DESCRIPTION:
441 -- Update the given proeprty of the meaaure in the column
442 -- S_COLOR_FORMULA of BSC_SYS_MEAURES
443 -- given data column
444 -- AUTHOR/DATE - MODIFICATIONS (AUTHOR/DATE/DESCRIPTION):
445 --***************************************************************************
446
447 PROCEDURE SetMeasurePropertyDB(dataColumn IN VARCHAR2, propertyName IN VARCHAR2, propertyValue IN VARCHAR2)
448 IS
449 l_stmt VARCHAR2(1000);
450
451 BEGIN
452
453
454 UPDATE BSC_SYS_MEASURES
455 SET S_COLOR_FORMULA = BSC_APPS.SET_PROPERTY_VALUE(S_COLOR_FORMULA, propertyName, propertyValue)
456 WHERE UPPER(MEASURE_COL) = upper(dataColumn)
457 AND TYPE = 0 AND NVL(SOURCE, 'BSC') = 'BSC';
458
459 EXCEPTION WHEN OTHERS THEN
460 g_error := sqlerrm;
461 Fnd_File.Put_Line(Fnd_File.Log, 'Exception in SetMeasurePropertyDB : '||g_error);
462 raise;
463 End;
464
465 --***************************************************************************
466 --GetAgregFunction : GetAggregateFunction
467 -- DESCRIPTION:
468 -- Returns in p_aggregation_method and pAvgL the aggregation function of the
469 -- given data column
470 -- AUTHOR/DATE - MODIFICATIONS (AUTHOR/DATE/DESCRIPTION):
471 --***************************************************************************
472 PROCEDURE Get_Aggregate_Function(dataColumn IN VARCHAR2, p_aggregation_method IN OUT NOCOPY VARCHAR2, pAvgL IN OUT NOCOPY VARCHAR2,
473 AvgLTotalColumn IN OUT NOCOPY VARCHAR2, AvgLCounterColumn IN OUT NOCOPY VARCHAR2) IS
474 l_stmt VARCHAR2(1000);
475 aggFunction VARCHAR2(1000);
476 CURSOR C1(p1 VARCHAR2, p2 VARCHAR2, p3 VARCHAR2, p4 VARCHAR2) IS
477 SELECT NVL(OPERATION, 'SUM') AS OPER,
478 NVL(BSC_APPS.GET_PROPERTY_VALUE(S_COLOR_FORMULA, p1),'N') AS PAVGL,
479 BSC_APPS.GET_PROPERTY_VALUE(S_COLOR_FORMULA, p2) AS PAVGLTOTAL,
480 BSC_APPS.GET_PROPERTY_VALUE(S_COLOR_FORMULA, p3) AS PAVGLCOUNTER
481 FROM BSC_SYS_MEASURES
482 WHERE UPPER(MEASURE_COL) = UPPER(p4)
483 AND TYPE = 0
484 AND NVL(SOURCE, 'BSC') = 'BSC';
485
486 cRow c1%ROWTYPE;
487
488 BEGIN
489
490 OPEN c1(BSC_METADATA_OPTIMIZER_PKG.C_PAVGL,
491 BSC_METADATA_OPTIMIZER_PKG.C_PAVGLTOTAL,
492 BSC_METADATA_OPTIMIZER_PKG.C_PAVGLCOUNTER,
493 dataColumn);
494 FETCH c1 INto cRow;
495 If c1%NOTFOUND Then
496 p_aggregation_method := null;
497 pAvgL := null;
498 AvgLTotalColumn := null;
499 AvgLCounterColumn := null;
500 Else
501 p_aggregation_method := cRow.OPER;
502 pAvgL := cRow.PAVGL;
503 AvgLTotalColumn := null;
504 If (crow.PAVGLTOTAL is not null) Then
505 AvgLTotalColumn := cRow.PAVGLTOTAL;
506 END IF;
507 AvgLCounterColumn := null;
508 If (cRow.PAVGLCOUNTER IS NOT NULL) Then
509 AvgLCounterColumn := cRow.PAVGLCOUNTER;
510 END IF;
511 END IF;
512 close c1;
513 EXCEPTION WHEN OTHERS THEN
514 g_error := sqlerrm;
515 Fnd_File.Put_Line(Fnd_File.Log, 'Exception in GetAggregateFunction '||g_error);
516 RAISE;
517
518 End;
519
520 --***************************************************************************
521 --getNextInternalColumnName
522 -- DESCRIPTION:
523 -- Returns the next Internal Column Name
524 -- BSCIC<next value from sequence BSC_INTERNAL_COLUMN_S>
525 --
526 -- AUTHOR/DATE - MODIFICATIONS (AUTHOR/DATE/DESCRIPTION):
527 --***************************************************************************
528 Function get_Next_Internal_Column_Name RETURN VARCHAR2 IS
529 l_seq NUMBER;
530
531
532 BEGIN
533 SELECT BSC_INTERNAL_COLUMN_S.NEXTVAL INTO l_seq FROM DUAL;
534 return 'BSCIC'||l_seq;
535 End;
536
537 -- PRIVATE API
538 FUNCTION Get_All_Measures_For_Fact(p_fact IN VARCHAR2, p_dim_set IN NUMBER)
539 return BSC_DBGEN_STD_METADATA.tab_clsMeasure IS
540 l_measure_name varchar2(1000);
541 measures dbms_sql.varchar2_table;
542 l_num_measures NUMBER;
543 l_aggregation_method varchar2(1000);
544 l_measure BSC_DBGEN_STD_METADATA.clsMeasure;
545 l_measure_null BSC_DBGEN_STD_METADATA.clsMeasure;
546 colMeasures BSC_DBGEN_STD_METADATA.tab_clsMeasure;
547 i NUMBER;
548 msg VARCHAR2(1000);
549
550 pFormulaSource VARCHAR2(1000);
551 pAvgL VARCHAR2(1000);
552 pAvgLTotal VARCHAR2(1000);
553 pAvgLCounter VARCHAR2(1000);
554 FuncAgregSingleColumn VARCHAR2(1000);
555 pAvgLSingleColumn VARCHAR2(1000);
556 AvgLTotalColumn VARCHAR2(1000);
557 AvgLCounterColumn VARCHAR2(1000);
558 l_measure_type NUMBER;
559 l_measure_id NUMBER;
560
561 l_stmt2 VARCHAR2(1000):= 'SELECT distinct nvl(M.MEASURE_COL, C.COLUMN_NAME), NVL(M.OPERATION, ''SUM'') AS OPER,
562 BSC_APPS.GET_PROPERTY_VALUE(M.S_COLOR_FORMULA, :1) AS PFORMULASOURCE,
563 NVL(BSC_APPS.GET_PROPERTY_VALUE(M.S_COLOR_FORMULA, :2 ),''N'') AS PAVGL,
564 BSC_APPS.GET_PROPERTY_VALUE(M.S_COLOR_FORMULA, :3) AS PAVGLTOTAL,
565 BSC_APPS.GET_PROPERTY_VALUE(M.S_COLOR_FORMULA, :4) AS PAVGLCOUNTER,
566 M.MEASURE_ID, nvl(cols.measure_type, 1) measure_type, m.source
567 FROM BSC_SYS_MEASURES M, BSC_DB_TABLES_COLS C, BSC_DB_MEASURE_COLS_VL COLS
568 WHERE M.MEASURE_COL(+) = C.COLUMN_NAME
569 AND COLS.measure_col(+) = c.column_name
570 AND C.COLUMN_TYPE = ''A''
571 AND C.TABLE_NAME LIKE ''BSC_S%'||p_fact||'_'||p_dim_set||'%'||'''
572 AND M.TYPE(+) = 0';
573 TYPE CurTyp IS REF CURSOR;
574 cv CurTyp;
575 l_measure_source varchar2(30);
576 BEGIN
577
578 OPEN cv for l_stmt2 USING BSC_METADATA_OPTIMIZER_PKG.C_PFORMULASOURCE,
579 BSC_METADATA_OPTIMIZER_PKG.C_PAVGL,
580 BSC_METADATA_OPTIMIZER_PKG.C_PAVGLTOTAL,
581 BSC_METADATA_OPTIMIZER_PKG.C_PAVGLCOUNTER;
582 LOOP
583 FETCH cv INTO l_measure_name, l_aggregation_method, pFormulaSource, pAvgL, pAvgLTotal, pAvgLCounter, l_measure_id, l_measure_type, l_measure_source;
584 EXIT WHEN cv%NOTFOUND;
585 Measures := BSC_DBGEN_UTILS.get_measure_list(l_measure_name);
586 l_num_measures := Measures.count;
587 i := Measures.first;
588 LOOP
589 EXIT WHEN Measures.count = 0;
590 If Not measure_exists(colMeasures, Measures(i), l_measure_source) Then
591 --Get the aggregation function and Avgl flag of the column (single column)
592 Get_Aggregate_Function (Measures(i), FuncAgregSingleColumn, pAvgLSingleColumn, AvgLTotalColumn, AvgLCounterColumn);
593 If FuncAgregSingleColumn IS NULL Then
594 FuncAgregSingleColumn := l_aggregation_method;
595 END IF;
596 If pAvgLSingleColumn IS NULL Then
597 pAvgLSingleColumn := pAvgL;
598 END IF;
599 l_measure := l_measure_null;
600 l_measure.measure_Name := Measures(i);
601 l_measure.measure_id := l_measure_id;
602 l_measure.measure_type := l_measure_type;
603 l_measure.AGGREGATION_method := FuncAgregSingleColumn;
604 l_measure.datatype := 'NUMBER';
605 l_measure.measure_source := l_measure_source;
606 bsc_dbgen_utils.add_property(l_measure.properties, BSC_DBGEN_STD_METADATA.AVGL_SINGLE_COLUMN, pAvgLSingleColumn);
607 If pAvgLSingleColumn = 'Y' Then
608 bsc_dbgen_utils.add_property(l_measure.properties, BSC_DBGEN_STD_METADATA.AVGL_TOTAL_COLUMN, AvgLTotalColumn);
609 bsc_dbgen_utils.add_property(l_measure.properties, BSC_DBGEN_STD_METADATA.AVGL_COUNTER_COLUMN, AvgLCounterColumn);
610 bsc_dbgen_utils.add_property(l_measure.properties, BSC_DBGEN_STD_METADATA.SOURCE_FORMULA, AvgLTotalColumn||'/'||AvgLCounterColumn);
611 END IF;
612 END IF;
613 EXIT WHEN i = Measures.last;
614 i := Measures.next(i);
615 END LOOP;
616 colMeasures(colMeasures.count +1 ) := l_measure;
617 END Loop;
618 close cv;
619 return colMeasures;
620 EXCEPTION WHEN OTHERS THEN
621 raise;
622 END;
623
624
625
626 --***************************************************************************
627 -- DESCRIPTION:
628 -- handle missing levels, if Country<-State<-City is the actual relationship
629 -- and if only Country and City are configured for the KPI, return the
630 -- parent child relationship between Country and City as true if it can
631 -- be derived thru some rollup
632 -- PARAMETERS:
633 -- AUTHOR/DATE - MODIFICATIONS (AUTHOR/DATE/DESCRIPTION):
634 --***************************************************************************
635 function is_parent_1N_any_level(p_child_level IN VARCHAR2, p_parent_level IN VARCHAR2,
636 p_missing_levels OUT nocopy dbms_sql.varchar2_table ) RETURN boolean AS
637 CURSOR cParent IS
638 WITH tree AS
639 (
640 SELECT childlvl.level_Table_name child_lvl
641 , parentlvl.level_Table_name parent_lvl
642 , LEVEL lvl
643 FROM bsc_sys_dim_level_rels rels, bsc_sys_dim_levels_b childlvl, bsc_sys_dim_levels_b parentlvl
644 WHERE rels.parent_dim_level_id = parentlvl.dim_level_id
645 AND rels.dim_level_id = childlvl.dim_level_id
646 START WITH parent_dim_level_id = parentlvl.dim_level_id
647 AND parentlvl.level_table_name = p_parent_level
648 CONNECT BY rels.parent_dim_level_id||rels.relation_type = PRIOR rels.dim_level_id||1
649 )
650 SELECT parent_lvl, child_lvl
651 FROM tree
652 CONNECT BY PRIOR parent_lvl = child_lvl
653 AND PRIOR lvl = lvl + 1
654 START WITH child_lvl = p_child_level
655 AND lvl =
656 (
657 SELECT MIN(lvl)
658 FROM tree
659 WHERE child_lvl = p_child_level
660 );
661
662 b_parent boolean := false;
663 BEGIN
664 FOR i IN cParent LOOP
665 b_parent := true;
666 IF (i.parent_lvl <> p_parent_level) THEN
667 p_missing_levels(p_missing_levels.count+1) := i.parent_lvl;
668 END IF;
669 END LOOP;
670 return b_parent;
671 EXCEPTION WHEN OTHERS THEN
672 raise;
673 End;
674
675 --***************************************************************************
676 -- DESCRIPTION:
677 -- PARAMETERS:
678 -- AUTHOR/DATE - MODIFICATIONS (AUTHOR/DATE/DESCRIPTION):
679 --***************************************************************************
680 Function is_parent_1N(p_child_level IN VARCHAR2, p_parent_level IN VARCHAR2 ) RETURN boolean IS
681
682 CURSOR cParent is
683 SELECT count(1)
684 FROM BSC_SYS_DIM_LEVELS_B child_lvl, BSC_SYS_DIM_LEVELS_B parent_lvl, BSC_SYS_DIM_LEVEL_RELS Rels
685 WHERE
686 child_lvl.LEVEL_TABLE_NAME = p_child_level
687 AND parent_lvl.level_table_name= p_parent_level
688 AND child_lvl.DIM_LEVEL_ID = Rels.DIM_LEVEL_ID
689 AND parent_lvl.DIM_LEVEL_ID = Rels.PARENT_DIM_LEVEL_ID
690 AND Rels.RELATION_TYPE = 1;
691 l_count NUMBER;
692 BEGIN
693
694 OPEN cParent;
695 FETCH cParent INTO l_count;
696 CLOSE cParent;
697
698 IF (l_count > 0) THEN
699 return true;
700 ELSE
701 return false;
702 END IF;
703 EXCEPTION WHEN OTHERS THEN
704 raise;
705 End;
706
707 --***************************************************************************
708 -- DESCRIPTION:
709 -- PARAMETERS:
710 -- AUTHOR/DATE - MODIFICATIONS (AUTHOR/DATE/DESCRIPTION):
711 --***************************************************************************
712 Function is_parent_MN(p_child_level IN VARCHAR2, p_parent_level IN VARCHAR2 ) RETURN boolean IS
713 CURSOR cParent IS
714 SELECT count(1)
715 FROM BSC_SYS_DIM_LEVELS_B child_lvl, BSC_SYS_DIM_LEVELS_B parent_lvl, BSC_SYS_DIM_LEVEL_RELS Rels
716 WHERE
717 child_lvl.LEVEL_TABLE_NAME = p_child_level
718 AND parent_lvl.level_table_name= p_parent_level
719 AND child_lvl.DIM_LEVEL_ID = Rels.DIM_LEVEL_ID
720 AND parent_lvl.DIM_LEVEL_ID = Rels.PARENT_DIM_LEVEL_ID
721 AND Rels.RELATION_TYPE = 2;
722 l_count NUMBER;
723 BEGIN
724 OPEN cParent;
725 FETCH cParent INTO l_count;
726 CLOSE cParent;
727 IF (l_count > 0) THEN
728 return true;
729 ELSE
730 return false;
731 END IF;
732 EXCEPTION WHEN OTHERS THEN
733 g_error := sqlerrm;
734 fnd_file.put_line(fnd_file.log, 'Exception in is_parent_MN : '||g_error||', p_child_level='||p_child_level||', p_parent_level='||p_parent_level);
735 raise;
736 End;
737
738
739 --****************************************************************************
740 -- DESCRIPTION:
741 -- Returns the index of the Levels family of the collection
742 -- l_dimensions which the given dimension belongs to.
743 --
744 -- PARAMETERS:
745 -- l_dimensions: Levels families collection
746 -- Maestra: dimension table name
747 --
748 -- AUTHOR/DATE - MODIFICATIONS (AUTHOR/DATE/DESCRIPTION):
749 --***************************************************************************
750
751 Function get_Dimension_Index(p_dimensions IN BSC_DBGEN_STD_METADATA.tab_clsDimension,
752 p_level IN VARCHAR2,
753 p_include_missing_levels IN BOOLEAN,
754 p_missing_levels OUT nocopy DBMS_SQL.VARCHAR2_TABLE)
755 return NUMBER IS
756 l_dimension BSC_DBGEN_STD_METADATA.clsDimension;
757 l_level BSC_DBGEN_STD_METADATA.clsLevel;
758 l_ct NUMBER := 0;
759 j NUMBER := 0;
760 l_groups DBMS_SQL.NUMBER_TABLE;
761 l_missing_levels DBMS_SQL.VARCHAR2_TABLE;
762 BEGIN
763 IF (p_dimensions.count =0 ) THEN
764 return -1;
765 END IF;
766 IF (p_include_missing_levels = false) THEN
767 FOR i IN p_dimensions.first..p_dimensions.last LOOP
768 l_dimension := p_dimensions(i);
769 FOR j IN l_dimension.Hierarchies(1).Levels.first..l_dimension.Hierarchies(1).Levels.last LOOP
770 l_level := l_dimension.Hierarchies(1).levels(j);
771 If is_parent_1N(p_level, l_level.level_table_name) Then
772 return i;
773 END IF;
774 If is_parent_MN(p_level, l_level.level_table_name) Then
775 return i;
776 END IF;
777 END LOOP;
778 END LOOP;
779 ELSE
780 FOR i IN p_dimensions.first..p_dimensions.last LOOP
781 l_dimension := p_dimensions(i);
782 FOR j IN l_dimension.Hierarchies(1).Levels.first..l_dimension.Hierarchies(1).Levels.last LOOP
783 l_level := l_dimension.Hierarchies(1).levels(j);
784 If is_parent_1N_any_level(p_level, l_level.level_table_name, l_missing_levels ) Then
785 return i;
786 END IF;
787 If is_parent_MN(p_level, l_level.level_table_name) Then
788 return i;
789 END IF;
790 END LOOP;
791 END LOOP;
792 END IF;
793 return -1;
794 EXCEPTION WHEN OTHERS THEN
795 g_error := sqlerrm;
796 fnd_file.put_line(fnd_file.log, 'Exception in getDimensionIndex '||g_error);
797 RAISE;
798 End ;
799
800
801 PROCEDURE insert_parents(p_periodicity IN NUMBER, p_parents IN VARCHAR2, p_periodicity_list IN BSC_DBGEN_STD_METADATA.tab_clsPeriodicity) IS
802 l_parents_list DBMS_SQL.NUMBER_TABLE;
803 l_table_name VARCHAR2(100) := 'bsc_tmp_per_circ_'||userenv('SESSIONID');
804 l_stmt VARCHAR2(1000) := 'INSERT INTO '||l_table_name||'(periodicity, source) values (:1, :2)';
805 l_index NUMBER;
806 l_per_id_list DBMS_SQL.NUMBER_TABLE;
807 BEGIN
808 l_parents_list := bsc_mo_helper_pkg.decomposeStringtoNumber(p_parents, ',');
809 IF (p_periodicity_list.count>0) THEN
810 FOR i IN p_periodicity_list.first..p_periodicity_list.last LOOP
811 l_per_id_list(l_per_id_list.count+1) :=p_periodicity_list(i).periodicity_id;
812 END LOOP;
813 END IF;
814 IF l_parents_list.count>0 THEN
815 FOR i IN l_parents_list.first..l_parents_list.last LOOP
816 IF bsc_mo_helper_pkg.searchNumberExists(l_per_id_list, l_per_id_list.count, l_parents_list(i)) THEN
817 execute immediate l_stmt USING p_periodicity, l_parents_list(i);
818 END IF;
819 end loop;
820 END IF;
821 EXCEPTION WHEN OTHERS THEN
822 g_error := sqlerrm;
823 fnd_file.put_line(fnd_file.log, 'Exception in bsc_dbgen_bsc_reader.insert_parents '||g_error);
824 RAISE;
825 END;
826
827 FUNCTION configure_parent_periods(p_periodicity_list IN OUT nocopy BSC_DBGEN_STD_METADATA.tab_clsPeriodicity)
828 RETURN BSC_DBGEN_STD_METADATA.tab_clsPeriodicity IS
829 PRAGMA AUTONOMOUS_TRANSACTION;
830 l_table_name VARCHAR2(100) := 'bsc_tmp_per_circ_'||userenv('SESSIONID');
831 l_stmt VARCHAR2(1000) := 'CREATE TABLE '||l_table_name||'(periodicity NUMBER, source NUMBER)';
832 CURSOR cPeriods (p_periodicity NUMBER) IS
833 SELECT SOURCE
834 FROM BSC_SYS_PERIODICITIES_VL
835 WHERE PERIODICITY_ID=p_periodicity;
836 l_parents VARCHAR2(4000);
837 l_temp NUMBER;
838 l_periodicity_list BSC_DBGEN_STD_METADATA.tab_clsPeriodicity;
839 TYPE CurTyp IS REF CURSOR;
840 cv CurTyp;
841 BEGIN
842 l_periodicity_list := p_periodicity_list;
843 bsc_mo_helper_pkg.dropTable(l_table_name);
844 bsc_mo_helper_pkg.Do_DDL(l_stmt, ad_ddl.create_table, l_table_name);
845 IF p_periodicity_list.count>0 THEN
846 FOR i IN p_periodicity_list.first..p_periodicity_list.last LOOP
847 OPEN cPeriods(p_periodicity_list(i).periodicity_id);
848 FETCH cPeriods INTO l_parents;
849 insert_parents(p_periodicity_list(i).periodicity_id, l_parents, p_periodicity_list);
850 CLOSE cPeriods;
851 END LOOP;
852 commit;
853 END IF;
854 l_stmt := ' select distinct source from '||l_table_name||' connect by periodicity = prior source start with periodicity = :1';
855 IF (p_periodicity_list.count>0) THEN
856 FOR i IN p_periodicity_list.first..p_periodicity_list.last LOOP
857 BEGIN
858 OPEN cv FOR l_stmt USING p_periodicity_list(i).periodicity_id;
859 LOOP
860 FETCH cv INTO l_temp;
861 EXIT WHEN cv%NOTFOUND;
862 p_periodicity_list(i).parent_periods(p_periodicity_list(i).parent_periods.count+1) := l_temp;
863 END LOOP;
864 CLOSE cv;
865 EXCEPTION WHEN OTHERS THEN
866 raise;
867 END ;
868 END LOOP;
869 END IF;
870 bsc_mo_helper_pkg.dropTable(l_table_name);
871 commit;
872 return p_periodicity_list;
873 EXCEPTION WHEN OTHERS THEN
874 g_error := sqlerrm;
875 fnd_file.put_line(fnd_file.log, 'Exception in bsc_dbgen_bsc_reader.configure_parent_periods '||g_error);
876 RAISE;
877 END;
878
879
880 FUNCTION Get_Fact_Info(p_process_id IN NUMBER, p_prototype_flag IN NUMBER, p_fact_list IN DBMS_SQL.NUMBER_TABLE ) return BSC_DBGEN_STD_METADATA.tab_clsFact IS
881 l_stmt varchar2(1000);
882 l_Code number;
883 l_Name BSC_KPIS_VL.NAME%TYPE;
884 l_IndicatorType number;
885 l_ConfigType number;
886 l_per_inter number;
887 l_OptimizationMode number;
888 l_Action_Flag number;
889 l_Share_Flag number;
890 l_Source_Indicator number;
891 TYPE CurTyp IS REF CURSOR;
892 cv CurTyp;
893 l_fact BSC_DBGEN_STD_METADATA.clsFact;
894 l_fact_list BSC_DBGEN_STD_METADATA.tab_clsFact;
895 strWhereInIndics VARCHAR2(1000);
896 BEGIN
897
898
899 l_stmt := 'SELECT DISTINCT INDICATOR, NAME, PROTOTYPE_FLAG,
900 INDICATOR_TYPE, CONFIG_TYPE, PERIODICITY_ID,
901 SHARE_FLAG, SOURCE_INDICATOR
902 FROM BSC_KPIS_VL ';
903 IF (p_process_id IS NOT NULL) THEN
904 l_stmt := l_stmt || ' where prototype_flag =:prototype_flag and indicator in
905 (SELECT to_number(fact_name) FROM BSC_DB_GEN_KPI_LIST WHERE process_id = :process_ID)';
906 l_Stmt := l_stmt || ' ORDER BY INDICATOR ';
907 OPEN cv FOR l_stmt using p_prototype_flag, p_process_id;
908 ELSE
909 strWhereInIndics := BSC_DBGEN_UTILS.Get_New_Big_In_Cond_Number(10, 'INDICATOR');
910 IF (l_fact_list.count>0) THEN
911 FOR i IN l_fact_list.first..l_fact_list.last LOOP
912 BSC_DBGEN_UTILS.Add_Value_Big_In_Cond_Number( 9, p_fact_list(i));
913 END LOOP;
914 END IF;
915 l_stmt := l_stmt || ' where '||strWhereInIndics;
916 l_Stmt := l_stmt || ' ORDER BY INDICATOR ';
917 OPEN cv FOR l_stmt;
918 END IF;
919 LOOP
920 FETCH cv into l_code, l_name, l_action_flag,
921 l_IndicatorType, l_configType, l_per_inter, l_share_flag, l_source_indicator;
922 EXIT WHEN cv%NOTFOUND;
923 l_optimizationMode := BSC_DBGEN_UTILS.get_KPI_Property_Value(l_Code, 'DB_TRANSFORM', 1);
924 IF l_Action_Flag <> 2 THEN
925 l_Action_Flag := 3;
926 END IF;
927 l_fact.Fact_ID := l_code;
928 l_fact.Fact_Name := l_name;
929 l_fact.fact_type := l_indicatorType;
930 l_fact.Application_short_name := 'BSC';
931 bsc_dbgen_utils.add_property(l_fact.properties, BSC_DBGEN_STD_METADATA.PROTOTYPE_FLAG, l_action_flag);
932 bsc_dbgen_utils.add_property(l_fact.properties, BSC_DBGEN_STD_METADATA.CONFIG_TYPE, l_ConfigType);
933 bsc_dbgen_utils.add_property(l_fact.properties, BSC_DBGEN_STD_METADATA.PERIODICITY_ID, l_per_inter);
934 bsc_dbgen_utils.add_property(l_fact.properties, BSC_DBGEN_STD_METADATA.OPTIMIZATION_MODE, l_OptimizationMode);
935 bsc_dbgen_utils.add_property(l_fact.properties, BSC_DBGEN_STD_METADATA.SHARE_FLAG,l_share_flag);
936 bsc_dbgen_utils.add_property(l_fact.properties, BSC_DBGEN_STD_METADATA.SOURCE_INDICATOR, nvl(l_source_indicator, 0));
937 l_fact_list(l_fact_list.count+1) := l_fact;
938 END Loop;
939 CLOSE cv;
940 EXCEPTION WHEN OTHERS THEN
941 g_error := sqlerrm;
942 fnd_file.put_line(fnd_file.log, 'Exception in bsc_dbgen_bsc_reader.get_fact_info '||g_error);
943 RAISE;
944
945 END;
946 FUNCTION Get_Fact_Info(p_process_id IN NUMBER, p_prototype_flag IN NUMBER) return BSC_DBGEN_STD_METADATA.tab_clsFact IS
947 l_id_list DBMS_SQL.NUMBER_TABLE;
948 BEGIN
949 return Get_Fact_Info(p_process_id, p_prototype_flag, l_id_list);
950 END;
951
952 FUNCTION Get_Facts_To_Recreate(p_process_id IN NUMBER) return BSC_DBGEN_STD_METADATA.tab_clsFact IS
953 BEGIN
954 return get_fact_info(p_process_id, 3);
955 END;
956
957 FUNCTION Get_Facts_To_Delete(p_process_id IN NUMBER) return BSC_DBGEN_STD_METADATA.tab_clsFact IS
958 BEGIN
959 return get_fact_info(p_process_id, 2);
960 END;
961
962 FUNCTION Get_Facts_To_Recalculate(p_process_id IN NUMBER) return BSC_DBGEN_STD_METADATA.tab_clsFact IS
963 l_stmt Varchar2(1000);
964 strWhereInIndics Varchar2(1000);
965 strWhereNotInIndics Varchar2(1000);
966 strWhereInMeasures Varchar2(1000);
967 i NUMBER := 0;
968 arrMeasuresCols DBMS_SQL.VARCHAR2_TABLE;
969 arrRelatedMeasuresIds DBMS_SQL.NUMBER_TABLE;
970
971 --measureCol Varchar2(1000);
972 Operands DBMS_SQL.VARCHAR2_TABLE;
973 NumOperands NUMBER;
974 l_measureID NUMBER;
975 l_measureCol VARCHAR2(500);
976 TYPE CurTyp IS REF CURSOR;
977 cv CurTyp;
978
979 l_error varchar2(400);
980 l_indicator_id NUMBER;
981 l_fact_list BSC_DBGEN_STD_METADATA.tab_clsFact;
982 l_fact BSC_DBGEN_STD_METADATA.clsFact;
983 l_fact_ids DBMS_SQL.NUMBER_TABLE;
984 l_num_measures NUMBER;
985 BEGIN
986 l_fact_list := get_fact_info(p_process_id, 4);
987 IF (l_fact_list.count = 0) THEN
988 return l_fact_list;
989 END IF;
990 --Init and array with the measures used by the indicators flagged for
991 --non-structural changes
992 l_num_measures := 0;
993 strWhereInIndics := BSC_DBGEN_UTILS.Get_New_Big_In_Cond_Number(9, 'I.INDICATOR');
994 i:= 0;
995 LOOP
996 EXIT WHEN i=l_fact_list.count;
997 BSC_DBGEN_UTILS.Add_Value_Big_In_Cond_Number( 9, l_fact_list(i).fact_id);
998 i:=i+1;
999 END LOOP;
1000 --PMF-BSC Integration: Filter out PMF measures
1001 l_stmt := 'SELECT DISTINCT M.MEASURE_COL FROM BSC_SYS_MEASURES M, BSC_DB_MEASURE_BY_DIM_SET_V I'
1002 || ' WHERE I.MEASURE_ID = M.MEASURE_ID AND ('|| strWhereInIndics ||' )'||
1003 ' AND M.TYPE = 0 AND NVL(M.SOURCE, ''BSC'') in (''PMF'', ''BSC'') ';
1004 OPEN cv FOR l_stmt;
1005 LOOP
1006 FETCH cv INTO l_measureCol;
1007 EXIT when cv%NOTFOUND;
1008 arrMeasuresCols(l_num_measures) := l_measureCol;
1009 END Loop;
1010 CLOSE cv;
1011 /*The measures in the array arrMeasuresCols are the ones that could be changed
1012 For that reason the indicators were flaged to 4
1013 We need to see in all system measures if there is a formula using that measure.
1014 IF that happen we need to add that measure. Any kpi using that meaure should be flaged too.*/
1015 strWhereNotInIndics := ' NOT ( ' || strWhereInIndics || ')';
1016 l_stmt := 'SELECT DISTINCT M.MEASURE_ID, M.MEASURE_COL '
1017 ||'FROM BSC_SYS_MEASURES M, BSC_DB_MEASURE_BY_DIM_SET_V I '||
1018 ' WHERE I.MEASURE_ID = M.MEASURE_ID AND ('|| strWhereNotInIndics ||' ) '||
1019 ' AND M.TYPE = 0 AND NVL(M.SOURCE, ''BSC'') in (''BSC'', ''PMF'')';
1020 OPEN cv FOR l_stmt;
1021 LOOP
1022 FETCH cv into l_measureID, l_measureCol;
1023 EXIT WHEN cv%NOTFOUND;
1024 NumOperands := BSC_MO_HELPER_PKG.GetFieldExpression(Operands, l_measureCol);
1025 i:= Operands.first;
1026 LOOP
1027 EXIT WHEN Operands.count =0 ;
1028 IF BSC_MO_HELPER_PKG.SearchStringExists(arrMeasuresCols, arrMeasuresCols.count, Operands(i)) THEN
1029 --One operand of the formula is one of the measures of a indicator flagged with 4
1030 --We need to add this formula (measure) to the related ones
1031 arrRelatedMeasuresIds(arrRelatedMeasuresIds.count+1) := l_measureID;
1032 END IF;
1033 EXIT WHEN i = Operands.last;
1034 i:= Operands.next(i);
1035 END LOOP;
1036 END Loop;
1037 CLOSE cv;
1038 --Now we need to add to the indicator list all the indicators using any of the measures
1039 --in arrRelatedMeasuresIds()
1040 IF arrRelatedMeasuresIds.count > 0 THEN
1041 strWhereInMeasures := BSC_DBGEN_UTILS.Get_New_Big_In_Cond_Number( 9, 'MEASURE_ID');
1042 i:= arrRelatedMeasuresIds.first;
1043 LOOP
1044 EXIT WHEN i=arrRelatedMeasuresIds.last;
1045 BSC_DBGEN_UTILS.Add_Value_Big_In_Cond_Number( 9, arrRelatedMeasuresIds(i));
1046 i:= arrRelatedMeasuresIds.next(i);
1047 END LOOP;
1048 l_stmt := 'SELECT DISTINCT INDICATOR FROM BSC_DB_MEASURE_BY_DIM_SET_V '||
1049 ' WHERE ('|| strWhereInMeasures || ')';
1050 open cv for L_stmt;
1051 LOOP
1052 FETCH cv into l_indicator_id;/*Indicator*/
1053 EXIT WHEN cv%NOTFOUND;
1054 IF Not bsc_mo_helper_pkg.SearchNumberExists(l_fact_ids, l_fact_ids.count, l_indicator_id) THEN
1055 l_fact_ids(l_fact_ids.count+1) := l_indicator_id;
1056 END IF;
1057 END Loop;
1058 CLOSE cv;
1059 END IF;
1060 return Get_Fact_Info(null, null, l_fact_ids);
1061 EXCEPTION WHEN OTHERS THEN
1062 bsc_mo_helper_pkg.writeTmp('Exception in Get_Facts_To_Recalculate : '||sqlerrm, FND_LOG.LEVEL_EXCEPTION, true);
1063 raise;
1064 END;
1065
1066 FUNCTION Fact_ID_Exists(p_facts IN BSC_DBGEN_STD_METADATA.tab_clsFact, p_fact_id IN NUMBER) RETURN BOOLEAN IS
1067 BEGIN
1068 IF (p_facts.count=0) THEN
1069 return false;
1070 END IF;
1071 FOR i IN p_facts.first..p_facts.last LOOP
1072 IF (p_facts(i).fact_id = p_fact_id) THEN
1073 return true;
1074 END IF;
1075 END LOOP;
1076 return false;
1077 END;
1078
1079 -- PUBLIC APIs
1080
1081 --first find the highest S table for this kpi, dim_set, then reuse get_levels_for_table api
1082 --note we ignore the periodicity here
1083 FUNCTION get_highest_s_table(p_fact IN VARCHAR2, p_dim_set IN NUMBER) return VARCHAR2 IS
1084 CURSOR cSTable(p_table varchar2) IS
1085 select table_name, count(1) ct from bsc_db_calculations
1086 where table_name like p_table
1087 and calculation_type=4
1088 group by table_name
1089 having count(1)=
1090 (
1091 select max(ct) from(
1092 select table_name, count(1) ct from bsc_db_calculations
1093 where table_name like p_table
1094 and calculation_type=4
1095 group by table_name )
1096 );
1097
1098 l_s_tablename VARCHAR2(300);
1099 l_dummy number;
1100 BEGIn
1101 OPEN cSTable('BSC_S_'||p_fact||'_'||p_dim_set||'%');
1102 FETCH cSTable INTO l_s_tablename, l_dummy;
1103 CLOSE cSTable;
1104 return l_s_tablename;
1105 EXCEPTION WHEN OTHERS THEN
1106 g_error := sqlerrm;
1107 fnd_file.put_line(fnd_file.log, 'Exception in bsc_dbgen_bsc_reader.get_highest_s_table '||g_error);
1108 RAISE;
1109 END;
1110
1111 FUNCTION get_lowest_s_table(p_fact IN VARCHAR2, p_dim_set IN NUMBER) return VARCHAR2 IS
1112 CURSOR cSTable(p_fact_pattern varchar2, p_s_pattern varchar2) IS
1113 select table_name from bsc_db_Tables_rels
1114 where table_name like p_fact_pattern
1115 and source_table_name not like p_s_pattern;
1116 l_s_tablename VARCHAR2(300);
1117 BEGIn
1118 OPEN cSTable('BSC_S_'||p_fact||'_'||p_dim_set||'%', 'BSC_S%');
1119 FETCH cSTable INTO l_s_tablename;
1120 CLOSE cSTable;
1121 return l_s_tablename;
1122 END;
1123
1124 FUNCTION Get_Facts_To_Process(p_process_id IN NUMBER) return BSC_DBGEN_STD_METADATA.tab_clsFact IS
1125 l_fact_list BSC_DBGEN_STD_METADATA.tab_clsFact;
1126 l_fact_list_temp BSC_DBGEN_STD_METADATA.tab_clsFact;
1127 BEGIN
1128 l_fact_list := Get_Facts_To_Recreate(p_process_id);
1129 l_fact_list_temp := Get_Facts_To_Delete(p_process_id);
1130 IF l_fact_list_temp.count >0 THEN
1131 FOR i IN l_fact_list_temp.first..l_fact_list_temp.last LOOP
1132 IF fact_id_exists(l_fact_list, l_fact_list_temp(i).fact_id)=false THEN
1133 l_fact_list(l_fact_list.count+1) := l_fact_list_temp(i);
1134 END IF;
1135 END LOOP;
1136 END IF;
1137 l_fact_list_temp := Get_Facts_To_recalculate(p_process_id);
1138 IF l_fact_list_temp.count >0 THEN
1139 FOR i IN l_fact_list_temp.first..l_fact_list_temp.last LOOP
1140 IF fact_id_exists(l_fact_list, l_fact_list_temp(i).fact_id)=false THEN
1141 l_fact_list(l_fact_list.count+1) := l_fact_list_temp(i);
1142 END IF;
1143 END LOOP;
1144 END IF;
1145 return l_fact_list;
1146 EXCEPTION WHEN OTHERS THEN
1147 g_error := sqlerrm;
1148 fnd_file.put_line(fnd_file.log, 'Exception in bsc_dbgen_bsc_reader.get_lowest_s_table'||g_error);
1149 RAISE;
1150 END;
1151
1152
1153
1154 FUNCTION Get_Measures_For_Fact_dbgen(p_fact IN VARCHAR2, p_dim_set IN NUMBER) return BSC_DBGEN_STD_METADATA.tab_clsMeasure IS
1155 l_stmt VARCHAR2(1000);
1156 l_measure_name varchar2(1000);
1157 measures dbms_sql.varchar2_table;
1158 l_num_measures NUMBER;
1159 l_aggregation_method varchar2(1000);
1160 l_measure BSC_DBGEN_STD_METADATA.clsMeasure;
1161 l_measure_null BSC_DBGEN_STD_METADATA.clsMeasure;
1162 colMeasures BSC_DBGEN_STD_METADATA.tab_clsMeasure;
1163 i NUMBER;
1164 msg VARCHAR2(1000);
1165
1166 pFormulaSource VARCHAR2(1000);
1167 pAvgL VARCHAR2(1000);
1168 pAvgLTotal VARCHAR2(1000);
1169 pAvgLCounter VARCHAR2(1000);
1170 FuncAgregSingleColumn VARCHAR2(1000);
1171 pAvgLSingleColumn VARCHAR2(1000);
1172 AvgLTotalColumn VARCHAR2(1000);
1173 AvgLCounterColumn VARCHAR2(1000);
1174 baseColumn dbms_sql.varchar2_table;
1175
1176 l_stmt2 VARCHAR2(1000):= 'SELECT M.MEASURE_COL, NVL(M.OPERATION, ''SUM'') AS OPER,
1177 BSC_APPS.GET_PROPERTY_VALUE(M.S_COLOR_FORMULA, :1) AS PFORMULASOURCE,
1178 NVL(BSC_APPS.GET_PROPERTY_VALUE(M.S_COLOR_FORMULA, :2 ),''N'') AS PAVGL,
1179 BSC_APPS.GET_PROPERTY_VALUE(M.S_COLOR_FORMULA, :3) AS PAVGLTOTAL,
1180 BSC_APPS.GET_PROPERTY_VALUE(M.S_COLOR_FORMULA, :4) AS PAVGLCOUNTER,
1181 M.source
1182 FROM BSC_SYS_MEASURES M, BSC_DB_MEASURE_BY_DIM_SET_V I
1183 WHERE I.MEASURE_ID = M.MEASURE_ID
1184 AND I.MEASURE_COL = M.MEASURE_COL
1185 AND I.DIM_SET_ID = :5
1186 AND I.INDICATOR = :6
1187 AND M.TYPE = 0
1188 AND NVL(M.SOURCE, ''BSC'') in(''PMF'', ''BSC'') ';
1189 TYPE CurTyp IS REF CURSOR;
1190 cv CurTyp;
1191 l_measure_source varchar2(30);
1192 BEGIN
1193 bsc_mo_helper_pkg.writeTmp( 'Inside GetMeasuresForFact, System time is '||bsc_mo_helper_pkg.get_time, fnd_log.level_statement, true);
1194 OPEN cv for l_stmt2 USING BSC_METADATA_OPTIMIZER_PKG.C_PFORMULASOURCE,
1195 BSC_METADATA_OPTIMIZER_PKG.C_PAVGL,
1196 BSC_METADATA_OPTIMIZER_PKG.C_PAVGLTOTAL,
1197 BSC_METADATA_OPTIMIZER_PKG.C_PAVGLCOUNTER, p_dim_set, to_number(p_fact);
1198
1199 LOOP
1200 FETCH cv INTO l_measure_name, l_aggregation_method, pFormulaSource, pAvgL, pAvgLTotal, pAvgLCounter, l_measure_source ;
1201 EXIT WHEN cv%NOTFOUND;
1202 Measures := BSC_DBGEN_UTILS.get_measure_list(l_measure_name);
1203 l_num_measures := Measures.count;
1204 i := Measures.first;
1205 LOOP
1206 EXIT WHEN Measures.count = 0;
1207 If sys_measure_exists(Measures(i), l_measure_source) Then
1208 If Not measure_exists(colMeasures, Measures(i), l_measure_source) Then
1209 --Get the aggregation function and Avgl flag of the column (single column)
1210 Get_Aggregate_Function (Measures(i), FuncAgregSingleColumn, pAvgLSingleColumn, AvgLTotalColumn, AvgLCounterColumn);
1211 If FuncAgregSingleColumn IS NULL Then
1212 FuncAgregSingleColumn := l_aggregation_method;
1213 END IF;
1214 If pAvgLSingleColumn IS NULL Then
1215 pAvgLSingleColumn := pAvgL;
1216 END IF;
1217 l_measure := l_measure_null;
1218 l_measure.measure_Name := Measures(i);
1219 l_measure.AGGREGATION_method := FuncAgregSingleColumn;
1220 l_measure.datatype := 'NUMBER';
1221 l_measure.measure_source := l_measure_source;
1222 --l_measure.Origen is not set
1223 bsc_dbgen_utils.add_property(l_measure.properties, BSC_DBGEN_STD_METADATA.AVGL_SINGLE_COLUMN, pAvgLSingleColumn);
1224 If pAvgLSingleColumn = 'Y' Then
1225 --This is a single column, we can have AvgL on a single column.
1226 --We need two internal columns: one for total and one for counter
1227 --Also we need to add the internal columns in gLov and in
1228 --BSC_DB_MEASURES_COLS_TL table
1229 baseColumn(0) := Measures(i);
1230 If AvgLTotalColumn IS NULL Then
1231 AvgLTotalColumn := get_Next_Internal_Column_Name;
1232 --Update the measure property pAvgLTotal in the database
1233 SetMeasurePropertyDB (Measures(i), BSC_METADATA_OPTIMIZER_PKG.C_PAVGLTOTAL, AvgLTotalColumn);
1234 END IF;
1235 bsc_dbgen_utils.add_property(l_measure.properties, BSC_DBGEN_STD_METADATA.AVGL_TOTAL_COLUMN, AvgLTotalColumn);
1236 --AddInternalColumnInDB(AvgLTotalColumn, 2, baseColumn, 1);
1237 If AvgLCounterColumn IS NULL Then
1238 AvgLCounterColumn := get_Next_Internal_Column_Name;
1239 --Update the measure property pAvgLCounter in the database
1240 SetMeasurePropertyDB (Measures(i), BSC_METADATA_OPTIMIZER_PKG.C_PAVGLCOUNTER, AvgLCounterColumn);
1241 END IF;
1242 bsc_dbgen_utils.add_property(l_measure.properties, BSC_DBGEN_STD_METADATA.AVGL_COUNTER_COLUMN, AvgLCounterColumn);
1243 --AddInternalColumnInDB(AvgLCounterColumn, 3, baseColumn, 1);
1244 END IF;
1245 bsc_dbgen_utils.add_property(l_measure.properties, BSC_DBGEN_STD_METADATA.INTERNAL_COLUMN_TYPE, 0); -- Normal
1246 colMeasures(colMeasures.count+1) := l_measure;
1247
1248 If pAvgLSingleColumn = 'Y' Then
1249 --Add the two internal column for AvgL in the collection
1250 --Column for Total
1251 l_measure := l_measure_null;
1252 l_measure.measure_name := AvgLTotalColumn;
1253 l_measure.aggregation_method := 'SUM';
1254 l_measure.datatype := 'NUMBER';
1255 --l_measure.Origen is not set
1256 bsc_dbgen_utils.add_property(l_measure.properties, BSC_DBGEN_STD_METADATA.AVGL_FLAG, 'N');
1257 --l_measure.avgLTotalColumn does not apply
1258 --l_measure.avgLCounterColumn does not apply
1259
1260 bsc_dbgen_utils.add_property(l_measure.properties, BSC_DBGEN_STD_METADATA.INTERNAL_COLUMN_TYPE, 2); --Internal column for Total of AvgL
1261 bsc_dbgen_utils.add_property(l_measure.properties, BSC_DBGEN_STD_METADATA.INTERNAL_COLUMN_SOURCE, Measures(i));
1262 colMeasures(colMeasures.count+1) := l_measure;
1263 --Column for Counter
1264 l_measure := l_measure_null;
1265 l_measure.measure_Name := AvgLCounterColumn;
1266 l_measure.AGGREGATION_method := 'SUM';
1267 l_measure.datatype := 'NUMBER';
1268 --l_measure.Origen is not set
1269 bsc_dbgen_utils.add_property(l_measure.properties, BSC_DBGEN_STD_METADATA.AVGL_FLAG, 'N');
1270 --l_measure.avgLTotalColumn does not apply
1271 --l_measure.avgLCounterColumn does not apply
1272 bsc_dbgen_utils.add_property(l_measure.properties, BSC_DBGEN_STD_METADATA.INTERNAL_COLUMN_TYPE, 3); --Internal column for Counter of AvgL
1273 bsc_dbgen_utils.add_property(l_measure.properties, BSC_DBGEN_STD_METADATA.INTERNAL_COLUMN_SOURCE, Measures(i));
1274 colMeasures(colMeasures.count+1) := l_measure;
1275 END IF;
1276 END IF;
1277
1278 ELSE
1279 raise bsc_metadata_optimizer_pkg.optimizer_exception ;
1280 EXIT ;
1281 END IF;
1282 EXIT WHEN i = Measures.last;
1283 i := Measures.next(i);
1284 END LOOP;
1285
1286 --Now add internal column if the formula needs to calculated in another column
1287 If pFormulaSource IS NOT NULL Then
1288 --Add the internal column in gLov and in BSC_DB_MEASURES_COLS_TL table
1289 --AddInternalColumnInDB(pFormulaSource, 1, Measures, l_num_measures);
1290 l_measure := l_measure_null;
1291 l_measure.measure_Name := pFormulaSource;
1292 l_measure.aggregation_method := l_aggregation_method;
1293 l_measure.datatype := 'NUMBER';
1294 --l_measure.Origen is not set
1295 bsc_dbgen_utils.add_property(l_measure.properties, BSC_DBGEN_STD_METADATA.AVGL_FLAG, pAvgL);
1296 If pAvgL = 'Y' Then
1297 --This is a formula calculated in another column, we can have AvgL on a that.
1298 --We need to internal columns: one for total and one for counter
1299 --Also we need to add the internal columns in gLov and in
1300 --BSC_DB_MEASURES_COLS_TL table
1301 If pAvgLTotal IS NULL Then
1302 pAvgLTotal := get_Next_Internal_Column_Name ;
1303 --Update the measure property pAvgLTotal in the database
1304 SetMeasurePropertyDB( l_measure_name, BSC_METADATA_OPTIMIZER_PKG.C_PAVGLTOTAL, pAvgLTotal);
1305 END IF;
1306 --AddInternalColumnInDB(pAvgLTotal, 2, Measures, l_num_measures);
1307 bsc_dbgen_utils.add_property(l_measure.properties, BSC_DBGEN_STD_METADATA.AVGL_TOTAL_COLUMN, pAvgLTotal);
1308 If pAvgLCounter IS NULL Then
1309 pAvgLCounter := get_Next_Internal_Column_Name;
1310 --Update the measure property pAvgLTotal in the database
1311 SetMeasurePropertyDB( l_measure_name, BSC_METADATA_OPTIMIZER_PKG.C_PAVGLCOUNTER, pAvgLCounter);
1312 END IF;
1313 --AddInternalColumnInDB( pAvgLCounter, 3, Measures, l_num_measures);
1314 bsc_dbgen_utils.add_property(l_measure.properties, BSC_DBGEN_STD_METADATA.AVGL_COUNTER_COLUMN, pAvgLCounter);
1315 END IF;
1316 bsc_dbgen_utils.add_property(l_measure.properties, BSC_DBGEN_STD_METADATA.INTERNAL_COLUMN_TYPE, 1); --Internal column for formula
1317 bsc_dbgen_utils.add_property(l_measure.properties, BSC_DBGEN_STD_METADATA.INTERNAL_COLUMN_SOURCE, l_measure_name); -- Formula Example A/
1318 colMeasures(colMeasures.last +1 ) := l_measure;
1319 If pAvgL = 'Y' Then
1320 --Add the two internal column for AvgL in the collection
1321 --Bug 2993089: When the column is not a formula but has the option
1322 --Apply rollup to formula', the columns for Average at lowest level
1323 --are already in colCamporDatos.
1324 --We need to evaluate this situation adding te condition
1325 --If Not Existel_measure(colMeasures, <internal column for AvgL>)
1326 --Column for Total
1327 If Not measure_exists(colMeasures, pAvgLTotal, 'BSC') Then
1328 l_measure := l_measure_null;
1329 l_measure.measure_Name := pAvgLTotal;
1330 l_measure.AGGREGATION_method := 'SUM';
1331 l_measure.datatype := 'NUMBER';
1332 --l_measure.Origen is not set
1333 bsc_dbgen_utils.add_property(l_measure.properties, BSC_DBGEN_STD_METADATA.AVGL_FLAG, 'N');
1334 bsc_dbgen_utils.add_property(l_measure.properties, BSC_DBGEN_STD_METADATA.INTERNAL_COLUMN_TYPE, 2); -- 'Internal column for Total of AvgL
1335 bsc_dbgen_utils.add_property(l_measure.properties, BSC_DBGEN_STD_METADATA.INTERNAL_COLUMN_SOURCE, l_measure_name); -- Formula Example A/B
1336 colMeasures(colMeasures.last+1) := l_measure ;
1337 END IF;
1338 --Column for Counter
1339 If Not measure_exists(colMeasures, pAvgLCounter, 'BSC') Then
1340 l_measure := l_measure_null;
1341 l_measure.measure_name := pAvgLCounter;
1342 l_measure.AGGREGATION_METHOD := 'SUM';
1343 l_measure.datatype := 'NUMBER';
1344 --l_measure.Origen is not set
1345 bsc_dbgen_utils.add_property(l_measure.properties, BSC_DBGEN_STD_METADATA.AVGL_FLAG, 'N');
1346 --l_measure.avgLTotalColumn does not apply
1347 --l_measure.avgLCounterColumn does not apply
1348 bsc_dbgen_utils.add_property(l_measure.properties, BSC_DBGEN_STD_METADATA.INTERNAL_COLUMN_TYPE, 3); --Internal column for Counter of AvgL
1349 bsc_dbgen_utils.add_property(l_measure.properties, BSC_DBGEN_STD_METADATA.INTERNAL_COLUMN_SOURCE, l_measure_name); -- Formula Example A/B
1350 colMeasures(colMeasures.last+1) := l_measure ;
1351 END IF;
1352 END IF;
1353 END IF;
1354 END Loop;
1355 close cv;
1356
1357 bsc_mo_helper_pkg.writeTmp( 'Compl. GetMeasuresForFact, System time is '||bsc_mo_helper_pkg.get_time, fnd_log.level_statement, true);
1358 return colMeasures;
1359 EXCEPTION WHEN OTHERS THEN
1360 g_error := sqlerrm;
1361 fnd_file.put_line(fnd_file.log, 'Exception in bsc_dbgen_bsc_reader.get_measures_for_fact, p_fact='||p_fact||', dimset='||p_dim_set||g_error);
1362 RAISE;
1363 END;
1364
1365 FUNCTION Get_Measures_For_Fact(p_fact IN VARCHAR2, p_dim_set IN NUMBER, p_include_derived_columns IN BOOLEAN default false) return BSC_DBGEN_STD_METADATA.tab_clsMeasure IS
1366
1367 CURSOR cMeasureList IS
1368 SELECT M.MEASURE_COL, NVL(M.OPERATION, 'SUM') AS OPER, m.source measure_source
1369 FROM BSC_SYS_MEASURES M, BSC_DB_MEASURE_BY_DIM_SET_V I
1370 WHERE I.MEASURE_ID = M.MEASURE_ID
1371 AND I.DIM_SET_ID = p_dim_set
1372 AND I.INDICATOR = to_number(p_fact)
1373 AND M.TYPE = 0
1374 AND NVL(M.SOURCE, 'BSC') in('PMF', 'BSC');
1375
1376 CURSOR cProperties(p_col VARCHAR2) IS
1377 SELECT sysm.measure_id, nvl(COLS.MEASURE_GROUP_ID,-1) measure_group_id, nvl(COLS.PROJECTION_ID, 0) projection_id , NVL(COLS.MEASURE_TYPE, 1) MEASURE_TYPE
1378 FROM BSC_DB_MEASURE_COLS_VL COLS , BSC_SYS_MEASURES sysm
1379 WHERE sysm.measure_col = cols.measure_col(+)
1380 AND sysm.measure_col = p_col;
1381 l_measure_row cProperties%ROWTYPE;
1382 ColMeasures BSC_DBGEN_STD_METADATA.tab_clsMeasure;
1383 l_num_measures NUMBER;
1384 l_measures_list DBMS_SQL.VARCHAR2_TABLE;
1385 l_measure BSC_DBGEN_STD_METADATA.clsMeasure;
1386 l_measure_null BSC_DBGEN_STD_METADATA.clsMeasure;
1387
1388 BEGIN
1389 IF (p_include_derived_columns) THEN
1390 return get_all_measures_for_fact(p_fact, p_dim_set);
1391 END IF;
1392 --BSC-PMF Integration: Even though a PMF measure cannot be present in a BSC
1393 --dimension set, I am going to do the validation to filter out PMF measures
1394 FOR i IN cMeasureList LOOP
1395 l_measures_list := BSC_DBGEN_UTILS.get_measure_list(i.measure_col);
1396 l_num_measures := l_measures_list.count;
1397 IF (l_measures_list.count>0) THEN
1398 FOR j IN l_measures_list.first..l_measures_list.last LOOP
1399 If Not measure_exists(colMeasures, l_measures_list(j), i.measure_source) Then
1400 --Get the aggregation function and Avgl flag of the column (single column)
1401 OPEN cProperties(l_measures_list(j));
1402 FETCH cProperties INTO l_measure_row;
1403 CLOSE cProperties;
1404 l_measure := l_measure_null;
1405 l_measure.measure_Name := l_measures_list(j);
1406 l_measure.aggregation_method := i.oper;
1407 l_measure.datatype := 'NUMBER';
1408 l_measure.measure_id := l_measure_row.measure_id;
1409 l_measure.measure_source := i.measure_source;
1410 -- Possible measure type values
1411 --1: Statistic
1412 --2: Balance
1413 l_measure.Measure_Type := l_measure_row.measure_type;
1414 l_measure.measure_group := l_measure_row.measure_group_id;
1415 bsc_dbgen_utils.add_property(l_measure.properties, BSC_DBGEN_STD_METADATA.PROJECTION_ID, l_measure_row.projection_id);
1416 colMeasures(colMeasures.count+1) := l_measure;
1417 END IF;
1418 END LOOP;
1419 END IF;
1420 END LOOP;
1421 return colMeasures;
1422
1423 EXCEPTION WHEN OTHERS THEN
1424 fnd_file.put_line(FND_FILE.LOG, 'Error in Get_Measures_For_Fact:fact='||p_fact||', error='||sqlerrm);
1425 raise;
1426 END;
1427
1428 --****************************************************************************
1429 --GetPeriodicities: GetColPeriodicidadesIndic
1430 -- DESCRIPTION:
1431 -- Get the collection of periodicity codes of the indicator
1432 -- PARAMETERS:
1433 -- Indic: indicator code
1434 -- AUTHOR/DATE - MODIFICATIONS (AUTHOR/DATE/DESCRIPTION):
1435 --***************************************************************************
1436
1437 FUNCTION Get_Periodicities_For_Fact(p_fact IN VARCHAR2) RETURN BSC_DBGEN_STD_METADATA.tab_ClsPeriodicity IS
1438 colPeriodicities BSC_DBGEN_STD_METADATA.tab_ClsPeriodicity;
1439 CURSOR cPeriodicities IS
1440 SELECT kpi.PERIODICITY_ID, NVL(TARGET_LEVEL, 1) AS TARGET_LEVEL, s.calendar_id
1441 FROM BSC_KPI_PERIODICITIES kpi, bsc_sys_periodicities s
1442 WHERE
1443 kpi.periodicity_id = s.periodicity_id
1444 AND kpi.INDICATOR = to_number(p_fact)
1445 ORDER BY PERIODICITY_ID;
1446 l_periodicity BSC_DBGEN_STD_METADATA.ClsPeriodicity ;
1447 cRow cPeriodicities%ROWTYPE;
1448 BEGIN
1449
1450 OPEN cPeriodicities;
1451 LOOP
1452 FETCH cPeriodicities INTO cRow;
1453 EXIT WHEN cPeriodicities%NOTFOUND;
1454 l_periodicity.periodicity_id := cRow.periodicity_id;
1455 l_periodicity.calendar_id := cRow.calendar_id;
1456 bsc_dbgen_utils.add_property(l_periodicity.properties, BSC_DBGEN_STD_METADATA.TARGET_LEVEL, cRow.target_level);
1457 colPeriodicities(colPeriodicities.count+1) := l_periodicity;
1458 END LOOP;
1459 close cPeriodicities;
1460 colPeriodicities := configure_parent_periods(colPeriodicities);
1461 return colPeriodicities;
1462 EXCEPTION WHEN OTHERS THEN
1463
1464 fnd_file.put_line(FND_FILE.LOG, 'Error in BSC_DBGEN_BSC_READER.Get_Periodicities_For_Fact:fact='||p_fact||',error='||sqlerrm);
1465 raise;
1466 End;
1467
1468
1469 --****************************************************************************
1470 --
1471 --
1472 -- DESCRIPTION:
1473 -- Get the collection of drill families of the indicator
1474 --
1475 -- PARAMETERS:
1476 -- Indic: indicator code
1477 -- p_dim_set: p_dim_set
1478 --
1479 -- AUTHOR/DATE - MODIFICATIONS (AUTHOR/DATE/DESCRIPTION):
1480 --***************************************************************************
1481 Function get_dimensions_for_fact(p_fact IN VARCHAR2, p_dim_set IN NUMBER, p_include_missing_levels IN boolean)
1482 RETURN BSC_DBGEN_STD_METADATA.tab_clsDimension IS
1483
1484 l_dimensions BSC_DBGEN_STD_METADATA.tab_clsDimension;
1485 DimensionLevels BSC_DBGEN_STD_METADATA.clsDimension;
1486 l_missing_levels DBMS_SQL.VARCHAR2_TABLE;
1487 cLevel BSC_DBGEN_STD_METADATA.clsLevel;
1488
1489 Level_null BSC_DBGEN_STD_METADATA.clsLevel;
1490 Parents1N varchar2(1000);
1491 ParentsMN varchar2(1000);
1492 tParents1N varchar2(1000);
1493 tParentsMN varchar2(1000);
1494
1495 l_level_index NUMBER;
1496 l_level_table VARCHAR2(1000);
1497 l_level_pk VARCHAR2(1000);
1498 l_level_name VARCHAR2(1000);
1499 l_level_id NUMBER;
1500 l_level_fk VARCHAR2(100);
1501 TargetLevel NUMBER;
1502 l_stmt varchar2(1000);
1503 l_dimension_index NUMBER;
1504 msg VARCHAR2(1000);
1505 l_count number;
1506 l_ct Number;
1507 TYPE CurTyp IS REF CURSOR;
1508 cv CurTyp;
1509 l_group_id NUMBER := 0;
1510
1511 cursor cMissing(p_missing_level VARCHAR2) IS
1512 SELECT DISTINCT LEVEL_PK_COL, NAME, 1 TAR_LEVEL
1513 FROM BSC_SYS_DIM_LEVELS_VL WHERE LEVEL_TABLE_NAME = p_missing_level;
1514 l_missing_level_info cMissing%ROWTYPE;
1515 l_missing_level BSC_DBGEN_STD_METADATA.clsLevel;
1516 l_current_level_index NUMBER := 0;
1517
1518 l_level_parent BSC_DBGEN_STD_METADATA.clsLevel;
1519 l_level_null BSC_DBGEN_STD_METADATA.clsLevel;
1520
1521 BEGIN
1522 l_stmt := 'SELECT DISTINCT kpidim.DIM_LEVEL_INDEX, kpidim.LEVEL_TABLE_NAME, kpidim.LEVEL_PK_COL, kpidim.NAME, NVL(kpidim.TARGET_LEVEL,1) AS TAR_LEVEL' ||
1523 ' , sysdim.dim_level_id, kpidim.parent_level_rel FROM BSC_KPI_DIM_LEVELS_VL kpidim, BSC_SYS_DIM_LEVELS_B sysdim
1524 WHERE
1525 kpidim.level_table_name = sysdim.level_table_name
1526 AND kpidim.INDICATOR = :1 AND kpidim.DIM_SET_ID = :2 AND kpidim.STATUS = 2';
1527
1528 IF IsIndicatorBalanceOrPnL(to_number(p_fact)) Then
1529 --The level 0 which is the Type of Account drill is excluded. This drill is
1530 --not considered to generate the tables
1531 l_stmt:= l_stmt||' AND DIM_LEVEL_INDEX <> 0';
1532 END IF;
1533 l_stmt := l_stmt||' ORDER BY DIM_LEVEL_INDEX';
1534 OPEN cv FOR l_stmt using to_number(p_fact), p_dim_set;
1535 LOOP
1536 Fetch cv into l_level_index, l_level_table, l_level_pk, l_level_name, TargetLevel, l_level_id, l_level_fk ;
1537 EXIT WHEN cv%NOTFOUND;
1538 cLevel := level_null;
1539 cLevel := get_level_info(l_level_table);
1540 cLevel.level_id := l_level_id;
1541 cLevel.level_pk := l_level_pk;
1542 cLevel.level_table_name := l_level_table;
1543 cLevel.level_Name := l_level_table;
1544 cLevel.level_fk := l_level_Fk;
1545 bsc_dbgen_utils.add_property(cLevel.properties, BSC_DBGEN_STD_METADATA.TARGET_LEVEL, TargetLevel);
1546 l_dimension_index := get_dimension_index(l_dimensions, l_level_table, p_include_missing_levels, l_missing_levels);
1547 --Get the index of the drill family which this drill belongs to.
1548 If l_dimension_index <> -1 Then
1549 --Level belongs to family l_dimension_index.
1550 -- If there are missing levels, add it to the list of levels
1551 IF (l_missing_levels.count<>0) THEN
1552 FOR i IN l_missing_levels.first..l_missing_levels.last LOOP
1553 OPEN cMissing(l_missing_levels(i));
1554 FETCH cMissing INTO l_missing_level_info;
1555 CLOSE cMissing;
1556 l_missing_level := level_null;
1557 l_missing_level.level_pk := l_missing_level_info.level_pk_col;
1558 l_missing_level.level_table_name := l_missing_levels(i);
1559 l_missing_level.level_name := l_missing_levels(i);
1560 bsc_dbgen_utils.add_property(l_missing_level.properties, BSC_DBGEN_STD_METADATA.MISSING_LEVEL, 'Y');
1561 l_current_level_index := l_current_level_index + 1;
1562 l_missing_level.level_index := l_current_level_index ;
1563 l_dimensions(l_dimensions.count).Hierarchies(1).Levels(l_current_level_index) := l_missing_level;
1564 END LOOP;
1565 ELSE
1566 l_current_level_index := l_current_level_index+1;
1567 END IF;
1568 -- needed to handle missing levels
1569 cLevel.level_index := l_current_level_index;
1570 --Review target levels
1571 IF bsc_dbgen_utils.get_property_value(cLevel.properties, BSC_DBGEN_STD_METADATA.TARGET_LEVEL) = '1' Then
1572 --If target apply to this level, then
1573 --it must apply for all parents
1574 IF l_dimensions(l_dimension_index).Hierarchies(1).Levels.count>0 THEN
1575 FOR j IN l_dimensions(l_dimension_index).Hierarchies(1).Levels.first..l_dimensions(l_dimension_index).Hierarchies(1).Levels.last LOOP
1576 bsc_dbgen_utils.add_property(l_dimensions(l_dimension_index).Hierarchies(1).Levels(j).properties, BSC_DBGEN_STD_METADATA.TARGET_LEVEL, 1);
1577 END LOOP;
1578 END IF;
1579 END IF;
1580 -- set parent levels
1581 l_count := l_dimensions(l_dimension_index).hierarchies(1).levels.first;
1582 LOOP
1583 EXIT WHEN l_dimensions(l_dimension_index).hierarchies(1).levels.count= 0;
1584 l_level_parent := l_level_null;
1585 l_level_parent := l_dimensions(l_dimension_index).hierarchies(1).levels(l_count);
1586 If is_parent_1N(l_level_table, l_level_parent.level_name) Then
1587 --There is 1n relationship with this drill
1588 cLevel.Parents1N(cLevel.Parents1N.count+1) := l_level_parent.level_name;
1589 --The 1n relations of the parent drill are also (by transitivity)
1590 --1n with the current drill
1591 IF (l_level_parent.parents1N.count>0)THEN
1592 FOR j IN l_level_parent.parents1N.first..l_level_parent.parents1N.last LOOP
1593 cLevel.Parents1N(cLevel.Parents1N.count+1) := l_level_parent.parents1N(j);
1594 END LOOP;
1595 END IF;
1596 END IF;
1597 EXIT WHEN l_count = l_dimensions(l_dimension_index).hierarchies(1).levels.last;
1598 l_count := l_dimensions(l_dimension_index).hierarchies(1).levels.next(l_count);
1599 END LOOP;
1600 l_dimensions(l_dimensions.count).Hierarchies(1).Levels(l_current_level_index) := cLevel;
1601 ELSE
1602 --The Level does not belong to any family previously created.
1603 --So, create a new dimension with this Level
1604 --Review target level
1605 --This is the first Level in this family, then target must apply
1606 l_current_level_index := 1;
1607 cLevel.level_index := l_current_level_index;
1608 bsc_dbgen_utils.add_property(cLevel.properties, BSC_DBGEN_STD_METADATA.TARGET_LEVEL, 1);
1609 l_dimensions(l_dimensions.count+1).Hierarchies(1).Levels(1) := cLevel;
1610 END IF;
1611 END Loop;
1612 close cv;
1613
1614 return l_dimensions;
1615 EXCEPTION WHEN OTHERS THEN
1616 fnd_file.put_line(FND_FILE.LOG, 'Error in BSC_DBGEN_BSC_READER.Get_Dimensions_For_Fact:fact='||p_fact||', dimset='||p_dim_set||', missing levels='||bsc_mo_helper_pkg.boolean_decode(p_include_missing_levels)||', error='||sqlerrm);
1617 raise;
1618 END;
1619
1620
1621 function get_parents_for_level(
1622 p_level_name varchar2,
1623 p_num_levels number default 1000000
1624 ) RETURN BSC_DBGEN_STD_METADATA.tab_ClsLevelRelationship IS
1625 CURSOR cParents IS
1626 select parent_lvl.level_table_name parent_level, lvl.level_table_name child_level, 'CODE' parent_pk, parent_lvl.level_pk_col child_fk, level
1627 from
1628 bsc_sys_dim_level_rels rels,
1629 bsc_sys_dim_levels_b lvl,
1630 bsc_sys_dim_levels_b parent_lvl
1631 where
1632 lvl.dim_level_id = rels.dim_level_id and
1633 rels.parent_dim_level_id = parent_lvl.dim_level_id and
1634 rels.relation_type <> 2 and
1635 level <= p_num_levels
1636 connect by rels.dim_level_id= PRIOR rels.parent_dim_level_id
1637 and rels.relation_type<>2
1638 and rels.dim_level_id <> rels.parent_dim_level_id
1639 start with rels.dim_level_id in
1640 (select dim_level_id from bsc_sys_dim_levels_b where level_table_name = p_level_name)
1641 order by level;
1642
1643 l_lvl_rel BSC_DBGEN_STD_METADATA.ClsLevelRelationship;
1644 l_tab_rels BSC_DBGEN_STD_METADATA.tab_ClsLevelRelationship;
1645 l_count NUMBER := 1;
1646 BEGIN
1647 FOR i IN cParents
1648 LOOP
1649 l_lvl_rel.Parent_Level := i.parent_level;
1650 l_lvl_rel.child_level := i.child_level;
1651 l_lvl_rel.child_level_fk := i.child_fk;
1652 l_lvl_rel.Parent_Level_pk := i.parent_pk;
1653 l_tab_rels(l_count) := l_lvl_rel;
1654 l_count := l_count + 1;
1655 END LOOP;
1656 return l_tab_rels;
1657 EXCEPTION WHEN OTHERS THEN
1658 fnd_file.put_line(FND_FILE.LOG, 'Error in BSC_DBGEN_BSC_READER.Get_Parents_For_Level:p_level='||p_level_name||', p_num_levels='||p_num_levels||', error='||sqlerrm);
1659 raise;
1660 END;
1661
1662 function get_children_for_level(
1663 p_level_name varchar2,
1664 p_num_levels number default 1000000
1665 ) RETURN BSC_DBGEN_STD_METADATA.tab_ClsLevelRelationship IS
1666 CURSOR cChildren IS
1667 select parent_lvl.level_table_name parent_level, lvl.level_table_name child_level, 'CODE' parent_pk, rels.relation_col child_fk, level
1668 from
1669 bsc_sys_dim_level_rels rels,
1670 bsc_sys_dim_levels_b lvl,
1671 bsc_sys_dim_levels_b parent_lvl
1672 where
1673 lvl.dim_level_id = rels.dim_level_id and
1674 rels.parent_dim_level_id = parent_lvl.dim_level_id and
1675 rels.relation_type <> 2 and
1676 level <= p_num_levels
1677 connect by PRIOR rels.dim_level_id||rels.relation_type = rels.parent_dim_level_id||1
1678 and rels.dim_level_id <> rels.parent_dim_level_id
1679 start with rels.parent_dim_level_id in
1680 (select dim_level_id from bsc_sys_dim_levels_b where level_table_name = p_level_name)
1681 order by level;
1682 l_lvl_rel BSC_DBGEN_STD_METADATA.ClsLevelRelationship;
1683 l_tab_rels BSC_DBGEN_STD_METADATA.tab_ClsLevelRelationship;
1684 BEGIN
1685 FOR i IN cChildren
1686 LOOP
1687 l_lvl_rel.Parent_Level := i.parent_level;
1688 l_lvl_rel.child_level := i.child_level;
1689 l_lvl_rel.child_level_fk := i.child_fk;
1690 l_lvl_rel.Parent_Level_pk := i.parent_pk;
1691 l_tab_rels(l_tab_rels.count + 1) := l_lvl_rel;
1692 END LOOP;
1693 return l_tab_rels;
1694 EXCEPTION WHEN OTHERS THEN
1695 fnd_file.put_line(FND_FILE.LOG, 'Error in BSC_DBGEN_BSC_READER.get_children_for_level:p_level='||p_level_name||', p_num_levels='||p_num_levels||', error='||sqlerrm);
1696 raise;
1697 END;
1698
1699 function get_level_info(
1700 p_level varchar2
1701 ) return BSC_DBGEN_STD_METADATA.clsLevel is
1702 l_level BSC_DBGEN_STD_METADATA.clsLevel ;
1703 CURSOR c_level IS
1704 SELECT dim_level_id , level_pk_col
1705 FROM bsc_sys_dim_levels_b
1706 WHERE level_table_name = p_level;
1707 Begin
1708 l_level.Level_Name := p_level;
1709 OPEN c_level;
1710 FETCH c_level INTO l_level.Level_id, l_level.level_fk;
1711 CLOSE c_level;
1712 l_level.Level_PK := 'CODE';
1713 l_level.Level_PK_Datatype := BSC_DBGEN_UTILS.get_datatype(p_level, l_level.Level_PK);
1714 l_level.level_type := 0; -- normal
1715 return l_level;
1716 Exception when others then
1717 fnd_file.put_line(FND_FILE.LOG, 'Error in BSC_DBGEN_BSC_READER.Get_Level_Info:level='||p_level||', error='||sqlerrm);
1718 raise;
1719 End;
1720
1721 function get_facts_for_levels(p_levels dbms_sql.varchar2_table) return BSC_DBGEN_STD_METADATA.tab_clsFact is
1722 l_facts BSC_DBGEN_STD_METADATA.tab_clsFact ;
1723 l_fact BSC_DBGEN_STD_METADATA.clsFact;
1724 l_stmt VARCHAR2(1000);
1725 TYPE CurTyp IS REF CURSOR;
1726 cv CurTyp;
1727 l_dim_sets DBMS_SQL.NUMBER_TABLE;
1728 Begin
1729 IF (p_levels.count=0) THEN
1730 return l_facts;
1731 END IF;
1732 init;
1733 l_stmt := BSC_DBGEN_UTILS.Get_New_Big_In_Cond_Varchar2(1, 'level_table_name');
1734 For i IN p_levels.first..p_levels.last LOOP
1735 BSC_DBGEN_UTILS.Add_Value_Big_In_Cond_Varchar2 (1, p_levels(i));
1736 END LOOP;
1737 --insert the children
1738 l_stmt := 'SELECT distinct dim.INDICATOR, dim.DIM_SET_ID, kpi.name , ''BSC''
1739 FROM BSC_KPI_DIM_LEVELS_B dim, BSC_KPIS_VL kpi
1740 WHERE dim.indicator = kpi.indicator and ('|| l_stmt||') order by indicator, dim_set_id';
1741 OPEN cv FOR l_stmt;
1742 LOOP
1743 FETCH cv INTO l_fact.fact_id, l_fact.dimension_set(1), l_fact.fact_name, l_fact.application_short_name;
1744 EXIT WHEN cv%NOTFOUND;
1745 IF (l_facts.count>0) THEN
1746 -- if its the same fact, but different dim set, then add to dim_set
1747 IF (l_facts(l_facts.last).fact_id = l_fact.fact_id) THEN
1748 l_facts(l_facts.last).dimension_set(l_facts(l_facts.last).dimension_set.last+1) := l_fact.dimension_set(1);
1749 ELSE
1750 l_facts(l_facts.last+1) := l_fact;
1751 END IF;
1752 ELSE
1753 l_facts(l_facts.count+1) := l_fact;
1754 END IF;
1755 END LOOP;
1756 CLOSE cv;
1757 return l_facts;
1758 Exception when others then
1759 fnd_file.put_line(FND_FILE.LOG, 'Exception in get_facts_for_levels :'||sqlerrm);
1760 for i in p_levels.first..p_levels.last loop
1761 fnd_file.put_line(FND_FILE.LOG, 'Level '||i||':'||p_levels(i));
1762 end loop;
1763 raise;
1764 End;
1765
1766
1767 Function get_dim_sets_for_fact(p_fact IN VARCHAR2) return DBMS_SQL.NUMBER_TABLE IS
1768 l_dim_sets dbms_sql.number_table;
1769 l_dim_set NUMBER;
1770 CURSOR cDimSets IS
1771 SELECT DISTINCT DIM_SET_ID
1772 FROM BSC_DB_DATASET_DIM_SETS_V
1773 WHERE INDICATOR = to_number(p_fact)
1774 ORDER BY DIM_SET_ID;
1775 BEGIN
1776 OPEN cDimSets;
1777 LOOP
1778 FETCH cDimSets INTO l_dim_set;
1779 EXIT WHEN cDimSets%NOTFOUND;
1780 --BSC-PMF Integration: Only get BSC dimension sets
1781 If get_num_measures(p_fact, l_dim_set) > 0 Then
1782 l_dim_sets(l_dim_sets.count+1) := l_dim_set;
1783 END IF;
1784 END LOOP;
1785 CLOSE cDimSets;
1786 return l_dim_sets;
1787 Exception when others then
1788 fnd_file.put_line(FND_FILE.LOG, 'Error in BSC_DBGEN_BSC_READER.Get_dim_sets_for_fact:'||p_fact||':'||sqlerrm);
1789 raise;
1790 End;
1791
1792
1793 function get_s_views(
1794 p_fact IN VARCHAR2,
1795 p_dim_set IN NUMBER)
1796 return dbms_sql.varchar2_table is
1797 cursor cList is
1798 select distinct mv_name from bsc_kpi_data_tables
1799 where indicator = to_number(p_fact)
1800 and dim_set_id = p_dim_set
1801 and mv_name not like 'BSC_S_%ZMV';
1802 l_mv_list dbms_sql.varchar2_table ;
1803 Begin
1804 FOR i IN cList LOOP
1805 l_mv_list(l_mv_list.count+1) := i.mv_name;
1806 END LOOP;
1807 return l_mv_list;
1808 Exception when others then
1809 fnd_file.put_line(FND_FILE.LOG, 'Error in BSC_DBGEN_BSC_READER.Get_s_views:'||p_fact||','||p_dim_set||':'||sqlerrm);
1810 raise;
1811 End;
1812
1813
1814 function get_levels_for_table(
1815 p_table_name varchar2,
1816 p_table_type VARCHAR2) return BSC_DBGEN_STD_METADATA.tab_clsLevel is
1817 l_level BSC_DBGEN_STD_METADATA.clsLevel ;
1818 cursor cLevels(p_s_table VARCHAR2) IS
1819 select distinct dim_level_id, level_table_name, level_pk_col from bsc_db_tables_cols cols, bsc_sys_dim_levels_b lvl
1820 where
1821 cols.table_name like p_s_table
1822 and cols.column_type='P'
1823 and cols.column_name = lvl.level_pk_col;
1824 l_table_name VARCHAR2(100) ;
1825 l_level_list BSC_DBGEN_STD_METADATA.tab_clsLevel ;
1826 Begin
1827 IF (p_table_type='MV' or p_Table_type='VIEW') THEN
1828 IF (p_table_name like '%ZMV') THEN
1829 l_table_name :=substr(p_table_name, 1, instr(p_table_name, '_ZMV'))||'%';
1830 ELSE
1831 l_table_name :=substr(p_table_name, 1, instr(p_table_name, '_MV'))||'%';
1832 END IF;
1833 ELSE
1834 l_table_name := p_table_name;
1835 END IF;
1836 FOR i IN cLevels(l_table_name) LOOP
1837 l_level := get_level_info(l_table_name);
1838 l_level.level_id := i.dim_level_id;
1839 l_level.level_name := i.level_table_name;
1840 l_level.level_table_name := i.level_table_name;
1841 l_level.level_fk := i.level_pk_col;
1842 l_level.level_pk := 'CODE';
1843 l_level.level_pk_datatype := BSC_DBGEN_UTILS.get_datatype(l_level.level_table_name, l_level.level_pk);
1844 l_level_list(l_level_list.count+1) := l_level;
1845 END LOOP;
1846 return l_level_list;
1847 Exception when others then
1848 fnd_file.put_line(FND_FILE.LOG, 'Error in BSC_DBGEN_BSC_READER.Get_levels_for_Table:'||p_table_name||','||p_table_type||':'||sqlerrm);
1849 raise;
1850 End;
1851
1852
1853 --
1854 function column_exists_in_table (p_b_table in varchar2, p_column in varchar2) return boolean is
1855 l_count number;
1856 begin
1857 select count(1) into l_count from bsc_db_tables_cols where table_name=p_b_table and column_name=p_column;
1858 if (l_count>0) then
1859 return true;
1860 end if;
1861 return false;
1862 end;
1863
1864 -- used to see if an BSCIC column is from a B table
1865 function is_BSCIC_column_from_b_table(p_s_table in varchar2, p_b_table in varchar2, p_column in varchar2) return boolean is
1866 cursor cFormula is
1867 select source_formula
1868 from bsc_db_tables_cols
1869 where table_name=p_s_table
1870 and column_type='A'
1871 and column_name=p_column;
1872 l_column_name varchar2(400);
1873 begin
1874 open cFormula;
1875 fetch cFormula into l_column_name;
1876 close cFormula;
1877 l_column_name := replace(l_column_name, 'SUM(', '');
1878 l_column_name := replace(l_column_name, 'COUNT(', '');
1879 l_column_name := replace(l_column_name, ')', '');
1880 return column_exists_in_table(p_b_table, l_column_name);
1881 end;
1882
1883 function is_b_table_a_source(p_s_table in varchar2, p_b_table in varchar2) return boolean is
1884 l_count number;
1885 begin
1886 select count(1) into l_count from bsc_db_tables_rels
1887 where table_name = p_s_table and source_table_name=p_b_table;
1888 if l_count>0 then
1889 return true;
1890 end if;
1891 return false;
1892 end;
1893
1894 function get_b_table_measures_for_fact(
1895 p_fact varchar2,
1896 p_dim_set varchar2,
1897 p_base_table varchar2,
1898 p_include_derived_columns boolean)
1899 return BSC_DBGEN_STD_METADATA.tab_clsMeasure IS
1900 CURSOR cMeasures(p_s_table IN VARCHAR2) is
1901 select distinct s_table.column_name
1902 , s_table.source_formula
1903 , measures.MEASURE_GROUP_ID
1904 , measures.PROJECTION_ID
1905 , NVL(measures.MEASURE_TYPE, 1) MEASURE_TYPE
1906 , sysm.MEASURE_ID
1907 From bsc_db_tables_cols s_table,
1908 bsc_db_tables_cols b_table,
1909 BSC_DB_MEASURE_COLS_VL measures,
1910 BSC_SYS_MEASURES sysm
1911 where s_table.table_name = p_s_table
1912 and b_table.table_name = p_base_table
1913 and s_table.column_name = b_table.column_name
1914 and s_table.column_type = 'A'
1915 and measures.measure_col = s_table.column_name
1916 AND measures.measure_col = sysm.measure_col;
1917
1918 CURSOR cMeasuresIncludeDerived (p_s_table IN VARCHAR2)is
1919 select distinct s_table.column_name, s_table.source_formula,
1920 measures.MEASURE_GROUP_ID, measures.PROJECTION_ID, NVL(measures.MEASURE_TYPE, 1) MEASURE_TYPE,
1921 sysm.MEASURE_ID
1922 from
1923 bsc_db_tables_cols s_table,
1924 bsc_db_tables_cols b_table,
1925 BSC_DB_MEASURE_COLS_VL measures,
1926 BSC_SYS_MEASURES sysm
1927 where s_table.table_name =p_s_table
1928 and b_table.table_name = p_base_table
1929 and s_table.column_name = b_table.column_name
1930 and s_table.column_type = 'A'
1931 and measures.measure_col = s_table.column_name
1932 AND measures.measure_col = sysm.measure_col
1933 union all
1934 select distinct s_table.column_name, s_table.source_formula,
1935 measures.MEASURE_GROUP_ID, measures.PROJECTION_ID, NVL(measures.MEASURE_TYPE, 1) MEASURE_TYPE,
1936 sysm.MEASURE_ID
1937 from
1938 bsc_db_tables_cols s_table,
1939 bsc_db_tables_cols b_table,
1940 BSC_DB_MEASURE_COLS_VL measures,
1941 BSC_SYS_MEASURES sysm
1942 where s_table.table_name =p_s_table
1943 and b_table.table_name(+) = p_base_table
1944 and s_table.column_name = b_table.column_name(+)
1945 and s_table.column_name like 'BSCIC%'
1946 and s_table.column_type = 'A'
1947 and measures.measure_col = s_table.column_name
1948 AND measures.measure_col = sysm.measure_col(+);
1949 l_measure BSC_DBGEN_STD_METADATA.clsMeasure;
1950 l_measure_list BSC_DBGEN_STD_METADATA.tab_clsMeasure;
1951 -- there can be multiple Base tables feeding this kpi
1952 -- this may be because of different lowest level periodicities or different measures from diff. tables
1953 CURSOR cLowestStable IS
1954 SELECT table_name FROM
1955 BSC_DB_TABLES_RELS rels
1956 WHERE table_name like 'BSC_S%'||p_fact||'_'||p_dim_set||'%'
1957 AND source_table_name not like 'BSC_S%'
1958 AND p_base_table IN (select table_name from bsc_db_tables_rels rels2 connect by table_name=prior source_table_name start with table_name = rels.table_name);
1959
1960
1961 l_lowest_s_table VARCHAR2(100);
1962 Begin
1963
1964 -- Here there may be 2 or more lowest level S tables, for eg monthly and weekly periodicity
1965 FOR i IN cLowestSTable LOOP
1966 IF is_b_table_a_source(i.table_name, p_base_table) THEN
1967 l_lowest_s_table := i.table_name;
1968 exit;
1969 END IF;
1970 END LOOP;
1971
1972 IF (p_include_derived_columns=false) THEN
1973 FOR i IN cMeasures(l_lowest_s_table) LOOP
1974 l_measure.measure_id := i.measure_id;
1975 l_measure.measure_Name := i.column_name;
1976 l_measure.Measure_Type := i.measure_type;
1977 -- just get the operator
1978 l_measure.aggregation_method := substr(i.source_formula, 1, instr(i.source_formula, '(')-1);
1979 l_measure.datatype := 'NUMBER';
1980 l_measure.measure_group := i.measure_group_id;
1981 bsc_dbgen_utils.add_property(l_measure.properties, BSC_DBGEN_STD_METADATA.PROJECTION_ID, i.projection_id);
1982 bsc_dbgen_utils.add_property(l_measure.properties, BSC_DBGEN_STD_METADATA.SOURCE_FORMULA, i.source_formula);
1983 l_measure_list(l_measure_list.count+1) := l_measure;
1984 END LOOP;
1985 ELSE
1986 FOR i IN cMeasuresIncludeDerived(l_lowest_s_table) LOOP
1987 l_measure.measure_id := i.measure_id;
1988 l_measure.measure_Name := i.column_name;
1989 l_measure.Measure_Type := i.measure_type;
1990 -- just get the operator
1991 l_measure.aggregation_method := substr(i.source_formula, 1, instr(i.source_formula, '(')-1);
1992 l_measure.datatype := 'NUMBER';
1993 l_measure.measure_group := i.measure_group_id;
1994 bsc_dbgen_utils.add_property(l_measure.properties, BSC_DBGEN_STD_METADATA.PROJECTION_ID, i.projection_id);
1995 bsc_dbgen_utils.add_property(l_measure.properties, BSC_DBGEN_STD_METADATA.SOURCE_FORMULA, i.source_formula);
1996 -- Bug 4540103 if its a BSCIC column check which B table its from
1997 if i.column_name like 'BSCIC%' then
1998 if is_BSCIC_column_from_b_table(l_lowest_s_table, p_base_table, i.column_name) then
1999 l_measure_list(l_measure_list.count+1) := l_measure;
2000 end if;
2001 else
2002 l_measure_list(l_measure_list.count+1) := l_measure;
2003 end if;
2004 END LOOP;
2005 END IF;
2006 return l_measure_list;
2007
2008 Exception when others then
2009 fnd_file.put_line(FND_FILE.LOG, 'Error in BSC_DBGEN_BSC_READER.Get_b_table_measures_for_fact:'||p_fact||','||p_dim_set||','||p_base_table||','||bsc_mo_helper_pkg.boolean_decode(p_include_derived_columns)||':'||sqlerrm);
2010 raise;
2011 End;
2012
2013 function get_periodicity_for_table(
2014 p_table varchar2) return NUMBER is
2015 CURSOR cTablePer IS
2016 SELECT periodicity_id from bsc_db_tables where table_name=p_table;
2017 l_per NUMBER;
2018 Begin
2019 OPEN cTablePer;
2020 FETCH cTablePer into l_per;
2021 CLOSE cTablePer;
2022 return l_per;
2023
2024 Exception when others then
2025 fnd_file.put_line(FND_FILE.LOG, 'Error in BSC_DBGEN_BSC_READER.Get_periodicity_for_table:'||p_table||':'||sqlerrm);
2026 raise;
2027 End;
2028
2029 function get_db_calendar_column(
2030 p_calendar_id number,
2031 p_periodicity_id number) return varchar2 is
2032 CURSOR cDBColumn IS
2033 SELECT db_column_name FROM bsc_sys_periodicities
2034 WHERE periodicity_id = p_periodicity_id AND calendar_id = p_calendar_id;
2035 l_db_column VARCHAR2(100);
2036 Begin
2037 OPEN cDBColumn;
2038 FETCH cDBColumn INTO l_db_column;
2039 CLOSE cDBColumn;
2040 return l_db_column;
2041 Exception when others then
2042 fnd_file.put_line(FND_FILE.LOG, 'Error in BSC_DBGEN_BSC_READER.Get_db_calendar_column: calendar='||p_calendar_id||',per id='||p_periodicity_id||':'||sqlerrm);
2043 raise;
2044 End;
2045
2046
2047 function get_zero_code_levels(
2048 p_fact varchar2,
2049 p_dim_set varchar2) return BSC_DBGEN_STD_METADATA.tab_clsLevel is
2050 CURSOR cSTable(p_num_zero_code in number) IS
2051 select table_name, count(1) ct from bsc_db_calculations
2052 where table_name like 'BSC_S_%'||p_fact||'_'||p_dim_set||'%'
2053 and calculation_type=4
2054 group by table_name
2055 having count(1)= p_num_zero_code;
2056
2057 l_s_table_name VARCHAR2(300);
2058 l_num_zero_code number;
2059 /*5014050 */
2060 cursor c1(p_kpi number) is
2061 select
2062 a.DIM_LEVEL_ID,a.LEVEL_TABLE_NAME,a.level_pk_col
2063 from
2064 bsc_sys_dim_levels_b a,
2065 (select a.LEVEL_TABLE_NAME,0 RELATION_TYPE
2066 from
2067 bsc_kpi_dim_levels_b a
2068 where
2069 a.indicator=p_kpi
2070 and a.PARENT_LEVEL_INDEX is null
2071 union all
2072 select a.LEVEL_TABLE_NAME,e.RELATION_TYPE
2073 from
2074 bsc_kpi_dim_levels_b a,
2075 bsc_kpi_dim_levels_b b,
2076 bsc_sys_dim_levels_b c,
2077 bsc_sys_dim_levels_b d,
2078 bsc_sys_dim_level_rels e
2079 where
2080 a.indicator=p_kpi
2081 and b.indicator=p_kpi
2082 and a.PARENT_LEVEL_INDEX=b.DIM_LEVEL_INDEX
2083 and a.LEVEL_TABLE_NAME=c.LEVEL_TABLE_NAME
2084 and b.LEVEL_TABLE_NAME=d.LEVEL_TABLE_NAME
2085 and e.DIM_LEVEL_ID=c.DIM_LEVEL_ID
2086 and e.PARENT_DIM_LEVEL_ID=d.DIM_LEVEL_ID) b
2087 where b.LEVEL_TABLE_NAME=a.LEVEL_TABLE_NAME
2088 and b.relation_type<>1;
2089 l_level BSC_DBGEN_STD_METADATA.clsLevel ;
2090 l_level_list BSC_DBGEN_STD_METADATA.tab_clsLevel ;
2091 Begin
2092 --l_s_table_name := get_highest_s_table(p_fact, p_dim_set);
2093 --return get_levels_for_table(l_s_table_name, 'TABLE');
2094 /*5014050
2095 this issue came from m:n relations. now, we look for all levels of the kpi and minus those that are child levels*/
2096 FOR i IN c1(to_number(p_fact)) LOOP
2097 l_level.level_id := i.dim_level_id;
2098 l_level.level_name := i.level_table_name;
2099 l_level.level_table_name := i.level_table_name;
2100 l_level.level_fk := i.level_pk_col;
2101 l_level.level_pk := 'CODE';
2102 l_level.level_pk_datatype := BSC_DBGEN_UTILS.get_datatype(l_level.level_table_name, l_level.level_pk);
2103 l_level_list(l_level_list.count+1) := l_level;
2104 END LOOP;
2105 return l_level_list;
2106 Exception when others then
2107 fnd_file.put_line(FND_FILE.LOG, 'Error in BSC_DBGEN_BSC_READER.Get_zero_code_levels:'||p_fact||','||p_dim_set||':'||sqlerrm);
2108 raise;
2109 End;
2110
2111 function get_base_tables_for_dim_set(
2112 p_fact in varchar2,
2113 p_dim_set in number,
2114 p_targets in boolean) return dbms_sql.varchar2_table is
2115 CURSOR cBTables(p_prefix VARCHAR2) IS
2116 select distinct rels.table_name
2117 from bsc_db_Tables_rels rels,
2118 bsc_db_tables src
2119 where
2120 rels.source_table_name = src.table_name
2121 and src.table_type=0
2122 and rels.table_name like 'BSC_B%'
2123 connect by rels.table_name=prior rels.source_table_name
2124 start with rels.table_name in -- lowest level S tables
2125 ( SELECT table_name FROM
2126 BSC_DB_TABLES_RELS rels
2127 WHERE table_name like p_prefix||p_fact||'_'||p_dim_set||'%'
2128 AND source_table_name not like 'BSC_S%'
2129 ) ;
2130
2131 l_table_list dbms_sql.varchar2_table ;
2132 l_prefix varchar2(10) := 'BSC_S_';
2133 -- this may be because of different lowest level periodicities or different measures from diff. tables
2134
2135 Begin
2136 IF (p_targets) THEN
2137 l_prefix := 'BSC_SB_';
2138 END IF;
2139 FOR i IN cBTables(l_prefix) LOOP
2140 l_table_list(l_table_list.count+1) := i.table_name;
2141 END LOOP;
2142 return l_table_list;
2143 Exception when others then
2144 fnd_file.put_line(FND_FILE.LOG, 'Error in BSC_DBGEN_BSC_READER.Get_base_tables_for_dim_set:'||p_fact||','||p_dim_set||','||bsc_mo_helper_pkg.boolean_decode(p_targets)||':'||sqlerrm);
2145 raise;
2146 END;
2147
2148
2149 /*function get_filter_for_dim_level(
2150 p_fact varchar2,
2151 p_level varchar2) return varchar2 is
2152 l_stmt VARCHAR2(1000);
2153 CURSOR cFilter IS
2154 select sysdim.dim_level_id, source_type, source_code
2155 from bsc_kpi_dim_levels_b kpidim,
2156 bsc_sys_dim_levels_b sysdim,
2157 bsc_sys_filters_views filters
2158 where
2159 kpidim.level_table_name = sysdim.level_table_name
2160 and kpidim.level_view_name <> sysdim.level_view_name
2161 and sysdim.dim_level_id = filters.dim_level_id
2162 and filters.level_view_name = kpidim.level_view_name
2163 and kpidim.indicator = to_number(p_fact)
2164 and kpidim.level_table_name = p_level;
2165 l_row cFilter%ROWTYPE;
2166 Begin
2167 l_stmt := '(select dim_level_value from bsc_sys_filters where ';
2168 OPEN cFilter;
2169 FETCH cFilter INTO l_row;
2170 IF (cFilter%FOUND) THEN
2171 l_stmt := l_stmt||' source_type='||l_row.source_type||' and source_code='||l_row.source_code||' and dim_level_id='||l_row.dim_level_id||')';
2172 return l_stmt;
2173 ELSE
2174 return null;
2175 END IF;
2176 CLOSE cFilter;
2177
2178 Exception when others then
2179 raise;
2180 End;*/
2181
2182 function get_filter_for_dim_level(
2183 p_fact varchar2,
2184 p_level varchar2) return varchar2 is
2185 l_stmt VARCHAR2(1000);
2186 CURSOR cFilter IS
2187 SELECT level_view_name
2188 FROM bsc_kpi_dim_levels_b
2189 WHERE indicator=to_number(p_fact)
2190 AND level_table_name = p_level;
2191 l_level_view varchar2(100);
2192 Begin
2193 OPEN cFilter;
2194 FETCH cFilter INTO l_level_view ;
2195 IF (cFilter%FOUND) THEN
2196 l_level_view := '(select code from '||l_level_view ||')';
2197 CLOSE cFilter;
2198 return l_level_view;
2199 ELSE
2200 CLOSE cFilter;
2201 return null;
2202 END IF;
2203 Exception when others then
2204 fnd_file.put_line(FND_FILE.LOG, 'Error in BSC_DBGEN_BSC_READER.Get_Filter_for_dim_level:'||p_fact||','||p_level||':'||sqlerrm);
2205 raise;
2206 End;
2207
2208 function get_year_periodicity_for_fact(p_fact varchar2) return number is
2209 cursor cYearPeriodicity IS
2210 select p.periodicity_id from
2211 bsc_sys_periodicities p, bsc_kpis_vl k
2212 where
2213 p.yearly_flag =1
2214 and p.calendar_id=k.calendar_id
2215 and k.indicator = to_number(p_fact);
2216
2217 l_year_periodicity NUMBER;
2218 Begin
2219 OPEN cYearPeriodicity;
2220 FETCH cYearPeriodicity INTO l_year_periodicity;
2221 CLOSE cYearPeriodicity;
2222 return l_year_periodicity;
2223
2224 end;
2225
2226 /* Changed Aug 11, 2005 by Arun
2227 Bug 4549520
2228 Discussed this with Venu, he asked me to change the code to first check bsc_db_tables.
2229 If its null, then we goto bsc_sys_calendars
2230 */
2231
2232 function get_current_year_for_fact(
2233 p_fact varchar2) return number is
2234
2235 -- changed for bug 4549520
2236 cursor cCurrentYearFromCal IS
2237 select c.fiscal_year
2238 from bsc_sys_calendars_b c, bsc_kpis_vl k
2239 where c.calendar_id = k.calendar_id
2240 and k.indicator = to_number(p_fact);
2241
2242 l_current_year NUMBER;
2243 l_year_periodicity NUMBER;
2244 Begin
2245 l_year_periodicity := get_year_periodicity_for_fact(p_fact);
2246 OPEN cCurrentYearFromCal;
2247 FETCH cCurrentYearFromCal INTO l_current_year;
2248 CLOSE cCurrentYearFromCal;
2249 return l_current_year;
2250
2251 Exception when others then
2252 fnd_file.put_line(FND_FILE.LOG, 'Error in BSC_DBGEN_BSC_READER.get_current_year_for_fact:'||p_fact||':'||sqlerrm);
2253 raise;
2254 End;
2255
2256 --added Jan 12, 2006
2257 function get_current_period_for_table(
2258 p_table_name varchar2) return number is
2259 cursor cPeriod is
2260 select current_period
2261 from bsc_db_tables
2262 where table_name=p_table_name;
2263 l_period number;
2264 begin
2265 open cPeriod;
2266 fetch cPeriod into l_period;
2267 close cPeriod;
2268 return l_period;
2269 end;
2270
2271 function get_current_year_for_table(
2272 p_table_name varchar2) return number is
2273 cursor cPeriod is
2274 select current_year
2275 from bsc_sys_calendars_b cal
2276 , bsc_sys_periodicities per
2277 , bsc_db_tables dbtbl
2278 where dbtbl.table_name = p_table_name
2279 and dbtbl.periodicity_id = per.periodicity_id
2280 and per.calendar_id = cal.calendar_id;
2281 l_year number;
2282 begin
2283 open cPeriod;
2284 fetch cPeriod into l_year;
2285 close cPeriod;
2286 return l_year;
2287 end;
2288
2289 function get_current_period_for_fact(
2290 p_fact varchar2,
2291 p_periodicity number) return number is
2292 ------------------------------
2293 cursor cCurrentPeriod IS
2294 select current_period from bsc_kpi_periodicities
2295 where indicator=to_number(p_fact)
2296 and periodicity_id=p_periodicity;
2297 l_current_period NUMBER;
2298 ------------------------------
2299 cursor cSourcePeriodicities(pp_periodicity number) is
2300 select source, db_column_name, calendar_id from bsc_sys_periodicities
2301 where periodicity_id=pp_periodicity;
2302 ------------------------------
2303 cursor cKPIPeriodicity is
2304 select periodicity_id from bsc_kpi_periodicities
2305 where indicator = to_number(p_fact);
2306 ------------------------------
2307 l_kpi_periodicity number;
2308 l_stmt VARCHAR2(1000);
2309 TYPE CurTyp IS REF CURSOR;
2310 cv CurTyp;
2311 l_current_year NUMBER;
2312 l_source_periodicities VARCHAR2(1000);
2313 l_db_col VARCHAR2(100);
2314 l_cal_id NUMBER;
2315 l_number_table DBMS_SQL.NUMBER_TABLE;
2316
2317
2318 Begin
2319 OPEN cCurrentPeriod;
2320 FETCH cCurrentPeriod INTO l_current_period;
2321 CLOSE cCurrentPeriod;
2322 IF (l_current_period is not null) THEN
2323 return l_current_period;
2324 END IF;
2325
2326 -- IF it is null, then find the source periodicity for this periodicity
2327 -- This is reqd as AW will may call this API for semester periodicity
2328 -- and semester periodicity may not exist for most objectives
2329 -- In this case, we find the sources of semester, see if any of them
2330 -- is attached to the objective.
2331 -- Lets say month is a valid periodicty for the objective and a source
2332 -- for semester. Now, we get the current_period for month, say 7.
2333 -- Then we get the current_year for month using the get_current_year API
2334 -- Once we have the year and the month, we can query bsc_db_calendar
2335 -- to get the value for Semester... whew... :D
2336
2337 -- Get the source_periodicity, db_column_name for p_periodicity
2338
2339 OPEN cSourcePeriodicities(p_periodicity);
2340 FETCH cSourcePeriodicities into l_source_periodicities, l_db_col, l_cal_id;
2341 CLOSE cSourcePeriodicities;
2342 l_source_periodicities:= ','||l_source_periodicities ||',';
2343
2344
2345 OPEN cKPIPeriodicity;
2346 LOOP
2347 FETCH cKPIPeriodicity INTO l_kpi_periodicity ;
2348 EXIT WHEN cKPIPeriodicity%NOTFOUND;
2349 IF (instr(l_source_periodicities, ','||l_kpi_periodicity||',')>0) THEN -- this is a source periodicity
2350 EXIT;
2351 ELSE
2352 l_kpi_periodicity := null;
2353 END IF;
2354 END LOOP;
2355
2356 -- Assuming month is a periodicity in the objective
2357 -- we now get the current_period for month, say 7
2358 l_current_period := get_current_period_for_fact (p_fact, l_kpi_periodicity);
2359
2360 -- Get the current year also, now that we have the current_period for month, say 2003
2361 l_current_year:= get_current_year_for_fact(p_fact);
2362
2363 -- Select semester (eg.) from bsc_db_calendar where calendar_id=2 and month=7 and year = 2003
2364 l_stmt := 'select '||l_db_col ||' from bsc_db_calendar where calendar_id=:1 and '||
2365 get_db_calendar_column(l_cal_id, l_kpi_periodicity) ||' = :2 and year = :3';
2366 OPEN cv FOR l_stmt using l_cal_id, l_current_period, l_current_year;
2367 FETCH cv INTO l_current_period;
2368 CLOSE cv;
2369 return l_current_period;
2370
2371 Exception when others then
2372 fnd_file.put_line(FND_FILE.LOG, 'Error in BSC_DBGEN_BSC_READER.Get_current_period_for_fact:'||p_fact||','||p_periodicity||':'||sqlerrm);
2373 raise;
2374
2375 End;
2376
2377
2378 -- this API is only called from the BSC Metadata Optimizer UI for AW support
2379 function is_projection_enabled_for_kpi(
2380 p_kpi in varchar2
2381 ) return varchar2 is
2382 l_dim_sets dbms_sql.number_table;
2383 l_measures BSC_DBGEN_STD_METADATA.tab_clsMeasure;
2384 l_properties BSC_DBGEN_STD_METADATA.tab_ClsProperties;
2385 Begin
2386 l_dim_sets := bsc_dbgen_metadata_reader.get_dim_sets_for_fact(p_kpi);
2387 --DBMS_OUTPUT.PUT_LINE('L_DIM_SETS.COUNT = '||TO_CHAR(l_dim_sets.count));
2388 IF (l_dim_sets.count > 0) THEN
2389 FOR i IN l_dim_sets.first..l_dim_sets.last LOOP
2390 l_measures := bsc_dbgen_metadata_reader.get_measures_for_fact(p_kpi, l_dim_sets(i));
2391 IF (l_measures.count > 0) THEN
2392 FOR j IN l_measures.first..l_measures.last LOOP
2393 l_properties := l_measures(j).Properties;
2394 IF (bsc_dbgen_utils.get_property_value(l_properties, BSC_DBGEN_STD_METADATA.PROJECTION_ID) <> '0') THEN
2395 RETURN 'Y';
2396 END IF;
2397 END LOOP;
2398 END IF;
2399 END LOOP;
2400 END IF;
2401 RETURN 'N';
2402 Exception when others then
2403 fnd_file.put_line(FND_FILE.LOG, 'Error in BSC_DBGEN_BSC_READER.is_projection_enabled_for_kpi:'||p_kpi||':'||sqlerrm);
2404 raise;
2405
2406 End;
2407
2408 function get_all_facts_in_aw return dbms_sql.varchar2_table is
2409 CURSOR cAWFacts IS
2410 SELECT kpi.indicator
2411 FROM BSC_KPIS_VL KPI,
2412 BSC_KPI_PROPERTIES PROP
2413 WHERE KPI.INDICATOR = PROP.INDICATOR
2414 AND PROP.PROPERTY_CODE = BSC_METADATA_OPTIMIZER_PKG.IMPL_TYPE
2415 AND PROP.PROPERTY_VALUE = '2';
2416 l_facts DBMS_SQL.VARCHAR2_TABLE;
2417 Begin
2418 FOR i IN cAWFacts
2419 LOOP
2420 l_facts(l_facts.count+1) := i.indicator;
2421 END LOOP;
2422 return l_facts;
2423 Exception when others then
2424 fnd_file.put_line(FND_FILE.LOG, 'Error in BSC_DBGEN_BSC_READER.Get_all_facts_in_aw:'||sqlerrm);
2425 raise;
2426
2427 End;
2428
2429 --get the ZMV for a kpi and dimset
2430 function get_z_s_views(
2431 p_fact IN VARCHAR2,
2432 p_dim_set IN NUMBER)
2433 return dbms_sql.varchar2_table is
2434 cursor cList is
2435 SELECT DISTINCT mv_name
2436 FROM bsc_kpi_data_tables
2437 WHERE indicator = to_number(p_fact)
2438 AND dim_set_id = p_dim_set
2439 AND mv_name like 'BSC%ZMV';
2440 l_mv_list dbms_sql.varchar2_table ;
2441 Begin
2442 FOR i IN cList LOOP
2443 l_mv_list(l_mv_list.count+1) := i.mv_name;
2444 END LOOP;
2445 return l_mv_list;
2446 Exception when others then
2447 fnd_file.put_line(FND_FILE.LOG, 'Error in BSC_DBGEN_BSC_READER.get_z_s_views:'||p_fact||','||p_dim_set||':'||sqlerrm);
2448 raise;
2449
2450 End;
2451
2452 Function get_all_levels_for_fact(p_fact IN VARCHAR2)
2453 RETURN DBMS_SQL.VARCHAR2_TABLE IS
2454 l_stmt VARCHAR2(1000);
2455 l_level VARCHAR2(1000);
2456 l_level_index number;
2457 l_return DBMS_SQL.VARCHAR2_TABLE;
2458 TYPE CurTyp IS REF CURSOR;
2459 cv CurTyp;
2460 BEGIN
2461 l_stmt := 'SELECT DISTINCT LEVEL_TABLE_NAME, DIM_LEVEL_INDEX ' ||
2462 ' FROM BSC_KPI_DIM_LEVELS_VL WHERE INDICATOR = :1 AND STATUS = 2';
2463 IF IsIndicatorBalanceOrPnL(to_number(p_fact)) Then
2464 --The level 0 which is the Type of Account drill is excluded. This level is not considered to generate the tables
2465 l_stmt:= l_stmt||' AND DIM_LEVEL_INDEX <> 0';
2466 END IF;
2467 l_stmt := l_stmt||' ORDER BY DIM_LEVEL_INDEX';
2468 OPEN cv FOR l_stmt using to_number(p_fact);
2469 LOOP
2470 FETCH cv INTO l_level, l_level_index;
2471 EXIT WHEN cv%NOTFOUND;
2472 l_return(l_return.count+1) := l_level;
2473 END LOOP;
2474 CLOSE cv;
2475 return l_return;
2476 Exception when others then
2477 fnd_file.put_line(FND_FILE.LOG, 'Error in BSC_DBGEN_BSC_READER.Get_all_levels_for_fact:'||p_fact||':'||sqlerrm);
2478 raise;
2479 END;
2480
2481 function get_dimension_level_short_name(p_dim_level_table_name IN VARCHAR2) return VARCHAR2
2482 IS
2483 CURSOR cShortName IS
2484 SELECT short_name
2485 FROM bsc_sys_dim_levels_b
2486 WHERE level_table_name = p_dim_level_table_name;
2487 l_short_name VARCHAR2(100);
2488 BEGIN
2489 OPEN cShortName;
2490 FETCH cShortName INTO l_short_name;
2491 CLOSE cShortName;
2492 return l_short_name;
2493 END;
2494
2495
2496 function get_measures_for_short_names(p_short_names in dbms_sql.varchar2_table) return dbms_sql.varchar2_table is
2497 l_measure_cols dbms_sql.varchar2_table;
2498 l_session_id number := userenv('SESSIONID');
2499 --l_counter number := 0;
2500 l_index number;
2501 l_variable_id number := 1;
2502 CURSOR c1(p_session_id NUMBER, p_variable_id NUMBER) IS
2503 SELECT nvl(sysm.measure_col, tmp.value_v) measure_col
2504 FROM bsc_sys_measures sysm, bsc_tmp_big_in_cond tmp
2505 where tmp.session_id = p_session_id
2506 and tmp.variable_id = p_variable_id
2507 and tmp.value_v = sysm.short_name(+)
2508 order by tmp.value_n;
2509 begin
2510 DELETE bsc_tmp_big_in_cond WHERE session_id=l_session_id and variable_id=l_variable_id;
2511 l_index := p_short_names.first;
2512 LOOP
2513 EXIT WHEN p_short_names.count=0;
2514 --l_counter := l_counter+1;
2515 INSERT INTO BSC_TMP_BIG_IN_COND (session_id, variable_id, value_n, value_v)
2516 VALUES (l_session_id, l_variable_id, l_index/*l_counter*/, p_short_names(l_index));
2517 EXIT WHEN l_index = p_short_names.last;
2518 l_index := p_short_names.next(l_index);
2519 END LOOP;
2520 l_index := p_short_names.first;
2521 FOR i IN c1(l_session_id, l_variable_id) LOOP
2522 l_measure_cols(l_index) := i.measure_col;
2523 IF (l_index<> p_short_names.last) THEN
2524 l_index:= p_short_names.next(l_index);
2525 END IF;
2526 END LOOP;
2527 return l_measure_cols;
2528 Exception when others then
2529 fnd_file.put_line(FND_FILE.LOG, 'Error in BSC_DBGEN_BSC_READER.Get_measures_for_short_names:'||sqlerrm);
2530 raise;
2531 end;
2532
2533
2534
2535 function get_dim_levels_for_short_names(p_short_names in dbms_sql.varchar2_table) return dbms_sql.varchar2_table is
2536 l_dim_levels dbms_sql.varchar2_table;
2537 l_session_id number := userenv('SESSIONID');
2538 --l_counter number := 0;
2539 l_index number;
2540 l_variable_id number := 1;
2541 CURSOR c1(p_session_id NUMBER, p_variable_id NUMBER) IS
2542 SELECT nvl(sysd.level_table_name, tmp.value_v) level_table_name
2543 FROM bsc_sys_dim_levels_b sysd, bsc_tmp_big_in_cond tmp
2544 where tmp.session_id = p_session_id
2545 and tmp.variable_id = p_variable_id
2546 and tmp.value_v = sysd.short_name(+)
2547 order by tmp.value_n;
2548 l_counter number;
2549 begin
2550 DELETE bsc_tmp_big_in_cond WHERE session_id=l_session_id and variable_id=l_variable_id;
2551 l_index := p_short_names.first;
2552 LOOP
2553 EXIT WHEN p_short_names.count=0;
2554 INSERT INTO BSC_TMP_BIG_IN_COND (session_id, variable_id, value_n, value_v)
2555 VALUES (l_session_id, l_variable_id, l_index/*l_counter*/, p_short_names(l_index));
2556 EXIT WHEN l_index = p_short_names.last;
2557 l_index := p_short_names.next(l_index);
2558 END LOOP;
2559 l_index := p_short_names.first;
2560 FOR i IN c1(l_session_id, l_variable_id) LOOP
2561 l_dim_levels(l_index) := i.level_table_name;
2562 IF (l_index<> p_short_names.last) THEN
2563 l_index:= p_short_names.next(l_index);
2564 END IF;
2565 END LOOP;
2566 return l_dim_levels;
2567 Exception when others then
2568 fnd_file.put_line(FND_FILE.LOG, 'Error in BSC_DBGEN_BSC_READER.Get_dim_levels_for_short_names:'||':'||sqlerrm);
2569 raise;
2570 end;
2571
2572 function get_fact_implementation_type(p_fact in varchar2) return varchar2 is
2573 cursor cImplType is
2574 SELECT property_value
2575 FROM
2576 BSC_KPI_PROPERTIES
2577 WHERE INDICATOR = p_fact
2578 AND PROPERTY_CODE = BSC_METADATA_OPTIMIZER_PKG.IMPL_TYPE;
2579 l_impl_type varchar2(30);
2580
2581 l_mv_level number;
2582 begin
2583 open cImplType;
2584 fetch cImplType into l_impl_type;
2585 close cimplType;
2586 if (l_impl_type = 2) then
2587 l_impl_type := 'AW';--Analytical Workspaces
2588 else
2589 SELECT fnd_profile.value('BSC_ADVANCED_SUMMARIZATION_LEVEL') into l_mv_level from dual;
2590 if (l_mv_level) is null then
2591 l_impl_type:='ST'; -- summary tables
2592 else
2593 l_impl_type := 'MV';-- Materialized view
2594 end if;
2595 end if;
2596 return l_impl_type;
2597 Exception when others then
2598 fnd_file.put_line(FND_FILE.LOG, 'Error in BSC_DBGEN_BSC_READER.Get_fact_implementation_type:'||p_fact||':'||sqlerrm);
2599 raise;
2600 end;
2601
2602 function is_level_used_by_prod_aw_fact(p_level_name in varchar2) return boolean is
2603 l_count number;
2604 begin
2605 select count(1) into l_count
2606 from bsc_kpi_dim_levels_b lvl,
2607 bsc_kpi_properties prop,
2608 bsc_kpis_vl kpis
2609 where kpis.indicator= lvl.indicator
2610 and kpis.indicator=prop.indicator
2611 and lvl.level_table_name=p_level_name
2612 and prop.property_code='IMPLEMENTATION_TYPE'
2613 and prop.property_value=2
2614 and kpis.prototype_flag not in (2,3,4);
2615 --dbms_output.put_line('is_level-used_by_prod_aw_fact='||l_count);
2616 if (l_count>0) then
2617 return true;
2618 end if;
2619 return false;
2620 Exception when others then
2621 fnd_file.put_line(FND_FILE.LOG, 'Error in BSC_DBGEN_BSC_READER.is_level_used_by_prod_aw_fact:'||p_level_name||':'||sqlerrm);
2622 raise;
2623 end;
2624
2625 function is_level_used_by_aw_fact(p_level_name in varchar2) return boolean is
2626 l_count number;
2627 l_session_id number;
2628 begin
2629 if is_level_used_by_prod_aw_fact(p_level_name) then
2630 return true;
2631 end if;
2632 --dbms_output.put_line('assume count='||BSC_DBGEN_METADATA_READER.g_assume_production_facts.count);
2633
2634 -- Not used by any production facts, now check if any processing facts have it
2635 -- This memory variable is populated by GDB during process time
2636 IF (BSC_DBGEN_METADATA_READER.g_assume_production_facts.count=0) THEN
2637 return false;
2638 END IF;
2639 -- now see if objectives being processed have these levels
2640 l_session_id := userenv('SESSIONID');
2641 delete bsc_tmp_big_in_cond where session_id=l_session_id and variable_id=1;
2642 FORALL i in 1..BSC_DBGEN_METADATA_READER.g_assume_production_facts.count
2643 insert into bsc_tmp_big_in_cond (session_id, variable_id, value_n) values (l_session_id, 1,
2644 to_number(BSC_DBGEN_METADATA_READER.g_assume_production_facts(i)));
2645
2646 select count(1) into l_count
2647 from bsc_kpi_dim_levels_b lvl,
2648 bsc_kpi_properties prop,
2649 bsc_kpis_vl kpis
2650 where kpis.indicator= lvl.indicator
2651 and kpis.indicator=prop.indicator
2652 and lvl.level_table_name=p_level_name
2653 and prop.property_code='IMPLEMENTATION_TYPE'
2654 and prop.property_value=2
2655 and kpis.prototype_flag<>2
2656 and kpis.indicator in
2657 (select value_n from bsc_tmp_big_in_cond where session_id=l_session_id
2658 and variable_id=1);
2659 if l_count > 0 then
2660 return true;
2661 end if;
2662 --dbms_output.put_line('returning false in is_level_used_by_aw_fact');
2663 return false;
2664 Exception when others then
2665 fnd_file.put_line(FND_FILE.LOG, 'Error in BSC_DBGEN_BSC_READER.is_level_used_by_aw_fact:'||p_level_name||':'||sqlerrm);
2666 raise;
2667 end;
2668
2669 function get_parents_for_level_aw(
2670 p_level_name varchar2,
2671 p_num_levels number default 1000000
2672 ) RETURN BSC_DBGEN_STD_METADATA.tab_ClsLevelRelationship IS
2673 CURSOR cParents IS
2674 select parent_lvl.level_table_name parent_level, lvl.level_table_name child_level, 'CODE' parent_pk,
2675 -- bug 5168537
2676 --rels.relation_col child_fk,
2677 parent_lvl.level_pk_col child_fk,
2678 level
2679 from
2680 bsc_sys_dim_level_rels rels,
2681 bsc_sys_dim_levels_b lvl,
2682 bsc_sys_dim_levels_b parent_lvl
2683 where
2684 lvl.dim_level_id = rels.dim_level_id and
2685 rels.parent_dim_level_id = parent_lvl.dim_level_id and
2686 rels.relation_type <> 2 and
2687 level <= p_num_levels
2688 connect by rels.dim_level_id= PRIOR rels.parent_dim_level_id
2689 and rels.relation_type<>2
2690 and rels.dim_level_id <> rels.parent_dim_level_id
2691 start with rels.dim_level_id in
2692 (select dim_level_id from bsc_sys_dim_levels_b where level_table_name = p_level_name)
2693 order by level;
2694
2695 l_lvl_rel BSC_DBGEN_STD_METADATA.ClsLevelRelationship;
2696 l_tab_rels BSC_DBGEN_STD_METADATA.tab_ClsLevelRelationship;
2697 l_count NUMBER := 1;
2698
2699
2700 BEGIN
2701 FOR i IN cParents
2702 LOOP
2703 l_lvl_rel.Parent_Level := i.parent_level;
2704 l_lvl_rel.child_level := i.child_level;
2705 l_lvl_rel.child_level_fk := i.child_fk;
2706 l_lvl_rel.Parent_Level_pk := i.parent_pk;
2707 -- Add it only if BOTH parent and child are used by an AW objective
2708 if is_level_used_by_aw_fact(i.parent_level) and is_level_used_by_aw_fact(i.child_level) then
2709 l_tab_rels(l_count) := l_lvl_rel;
2710 l_count := l_count + 1;
2711 end if;
2712 END LOOP;
2713 return l_tab_rels;
2714 Exception when others then
2715 fnd_file.put_line(FND_FILE.LOG, 'Error in BSC_DBGEN_BSC_READER.Get_parent_levels_for_aw:'||p_level_name||',levels='||p_num_levels||', error:'||sqlerrm);
2716 raise;
2717 END;
2718
2719 function get_children_for_level_aw(
2720 p_level_name varchar2,
2721 p_num_levels number default 1000000
2722 ) RETURN BSC_DBGEN_STD_METADATA.tab_ClsLevelRelationship IS
2723 CURSOR cChildren IS
2724 select parent_lvl.level_table_name parent_level, lvl.level_table_name child_level, 'CODE' parent_pk, rels.relation_col child_fk, level
2725 from
2726 bsc_sys_dim_level_rels rels,
2727 bsc_sys_dim_levels_b lvl,
2728 bsc_sys_dim_levels_b parent_lvl
2729 where
2730 lvl.dim_level_id = rels.dim_level_id and
2731 rels.parent_dim_level_id = parent_lvl.dim_level_id and
2732 rels.relation_type <> 2 and
2733 level <= p_num_levels
2734 connect by PRIOR rels.dim_level_id||rels.relation_type = rels.parent_dim_level_id||1
2735 and rels.dim_level_id <> rels.parent_dim_level_id
2736 start with rels.parent_dim_level_id in
2737 (select dim_level_id from bsc_sys_dim_levels_b where level_table_name = p_level_name)
2738 order by level;
2739 l_lvl_rel BSC_DBGEN_STD_METADATA.ClsLevelRelationship;
2740 l_tab_rels BSC_DBGEN_STD_METADATA.tab_ClsLevelRelationship;
2741 BEGIN
2742 FOR i IN cChildren
2743 LOOP
2744 l_lvl_rel.Parent_Level := i.parent_level;
2745 l_lvl_rel.child_level := i.child_level;
2746 l_lvl_rel.child_level_fk := i.child_fk;
2747 l_lvl_rel.Parent_Level_pk := i.parent_pk;
2748 -- Aug 8, 2005, added to handle dim. level changes within aw
2749 -- Add it only if BOTH parent and child are used by an AW objective
2750 if is_level_used_by_aw_fact(i.parent_level) and is_level_used_by_aw_fact(i.child_level) then
2751 l_tab_rels(l_tab_rels.count + 1) := l_lvl_rel;
2752 end if;
2753 END LOOP;
2754 return l_tab_rels;
2755 Exception when others then
2756 fnd_file.put_line(FND_FILE.LOG, 'Error in BSC_DBGEN_BSC_READER.Get_children_for_level_aw:'||p_level_name||':'||sqlerrm);
2757 raise;
2758 END;
2759
2760
2761 function get_target_per_for_b_table(p_fact in varchar2, p_dim_set in number, p_b_table in varchar2) return dbms_sql.varchar2_table is
2762 cursor cTgtPeriodicities is
2763 select periodicity_id from bsc_db_tables where table_name in
2764 (
2765 select distinct table_name from bsc_db_tables_rels rels
2766 where
2767 rels.table_name like 'BSC_S%'||p_fact||'_'||p_dim_set||'%'
2768 and rels.source_table_name not like 'BSC_S%'||p_fact||'_'||p_dim_set||'%'
2769 connect by prior rels.table_name=rels.source_table_name
2770 and rels.relation_type<>2
2771 start with rels.source_table_name = p_b_table
2772 );
2773 l_periodicities dbms_sql.varchar2_table;
2774 l_stmt varchar2(1000);
2775 TYPE CurTyp IS REF CURSOR;
2776 cv CurTyp;
2777 l_pattern varchar2(30);
2778 l_per number;
2779 BEGIN
2780
2781 l_pattern := 'BSC_S%'||p_fact||'_'||p_dim_set||'%';
2782 FOR i IN cTgtPeriodicities LOOP
2783 l_periodicities(l_periodicities.count+1):=i.periodicity_id;
2784 END LOOP;
2785 return l_periodicities;
2786 Exception when others then
2787 fnd_file.put_line(FND_FILE.LOG, 'Error in BSC_DBGEN_BSC_READER.get_target_per_for_b_table:'||p_fact||','||p_dim_set||','||p_b_table||':'||sqlerrm);
2788 raise;
2789 END;
2790
2791
2792 function get_last_update_date_for_fact(p_fact in varchar2) return date is
2793 cursor cdate is
2794 select last_update_date from bsc_kpis_vl
2795 where indicator=p_fact;
2796 l_date date;
2797 begin
2798 open cdate;
2799 fetch cdate into l_date;
2800 close cdate;
2801 return l_date;
2802 end;
2803
2804 function get_fact_cols_from_b_table(
2805 p_fact in varchar2,
2806 p_dim_set in number,
2807 p_b_table_name in varchar2,
2808 p_col_type in varchar2
2809 ) return BSC_DBGEN_STD_METADATA.tab_clsColumnMaps
2810 --BSC_DBGEN_STD_METADATA.tab_clsLevel
2811 is
2812 --Find Summary Table fed by this B table
2813 cursor cSummary(p_pattern in varchar2) is
2814 select distinct table_name from bsc_db_tables_rels
2815 where instr(table_name, p_pattern)=1
2816 and source_table_name not like p_pattern||'%'
2817 connect by source_table_name = prior table_name
2818 start with source_table_name = p_b_table_name;
2819
2820 l_s_table varchar2(100);
2821
2822 cursor cPath(p_parent_level varchar2, p_child_level varchar2) is
2823 WITH tree AS
2824 (
2825 SELECT table_name child_lvl
2826 , source_Table_name parent_lvl
2827 , LEVEL lvl
2828 FROM bsc_db_tables_rels rels
2829 START WITH source_table_name = p_parent_level
2830 CONNECT BY source_table_name = PRIOR table_name
2831 )
2832 SELECT parent_lvl, child_lvl, lvl
2833 FROM tree
2834 CONNECT BY PRIOR parent_lvl = child_lvl
2835 AND PRIOR lvl = lvl + 1
2836 START WITH child_lvl = p_child_level
2837 AND lvl =
2838 (
2839 SELECT MIN(lvl)
2840 FROM tree
2841 WHERE child_lvl = p_child_level
2842 )
2843 union
2844 select source_table_name,table_name , -1 from bsc_db_Tables_rels
2845 where table_name=p_parent_level
2846 order by lvl ;
2847
2848 cursor cCols (p_table_name in varchar2, p_col_type1 in varchar2, p_col_type2 in varchar2) is
2849 select column_name, source_column source_column_name from bsc_db_Tables_cols
2850 where table_name = p_table_name
2851 and column_type in(p_col_type1, p_col_type2);
2852
2853
2854 l_var1 dbms_sql.varchar2_table;
2855 l_var2 dbms_sql.varchar2_table;
2856 l_var3 dbms_sql.varchar2_table;
2857 l_var4 dbms_sql.varchar2_table;
2858
2859 l_col_maps_table varchar2(100) := 'BSC_TMP_OPT_COL_MAPS';
2860 l_stmt varchar2(1000) :='create global temporary table '||l_col_maps_table||' (column_name varchar2(100), source_column_name varchar2(100), table_name varchar2(100), source_table_name varchar2(100))';
2861 PRAGMA AUTONOMOUS_TRANSACTION;
2862 l_col_type1 VARCHAR2(10);
2863 l_col_type2 VARCHAR2(10);
2864 TYPE CurTyp IS REF CURSOR;
2865 cv CurTyp;
2866
2867 l_col_map BSC_DBGEN_STD_METADATA.clsColumnMaps;
2868 l_col_maps BSC_DBGEN_STD_METADATA.tab_clsColumnMaps;
2869
2870 Begin
2871 init;
2872 if (bsc_apps.table_exists(l_col_maps_table)=false) then
2873 bsc_apps.Do_DDL(l_stmt, ad_ddl.create_table, l_col_maps_table);
2874 end if;
2875
2876 if (p_col_type='ALL') then
2877 l_col_type1 := 'A';
2878 l_col_type2 := 'P';
2879 elsif (p_col_type='KEYS') then
2880 l_col_type1 := 'P';
2881 l_col_type2 := 'P';
2882 elsif (p_col_type='MEASURES') then
2883 l_col_type1 := 'A';
2884 l_col_type2 := 'A';
2885 end if;
2886
2887 --rkumar: modified for bug#5506476
2888 IF BSC_MO_HELPER_PKG.FindIndex(BSC_METADATA_OPTIMIZER_PKG.g_bt_tables_tgt,p_b_table_name) = -1 THEN
2889 open cSummary('BSC_S_'||p_fact);
2890 fetch cSummary into l_s_table;
2891 close cSummary;
2892 ELSE
2893 open cSummary('BSC_SB_'||p_fact);
2894 fetch cSummary into l_s_table;
2895 close cSummary;
2896 END IF;
2897
2898 -- get shortest path and their correpsonding tables/source_tables/cols/source_cols
2899 for i in cPath(p_b_table_name, l_s_table) loop
2900 --match_columns
2901 for j in cCols(i.child_lvl, l_col_type1, l_col_type2) loop
2902 l_var1(l_var1.count+1) := i.child_lvl;
2903 l_var2(l_var2.count+1) := i.parent_lvl;
2904 l_var3(l_var3.count+1) := j.column_name;
2905 l_var4(l_var4.count+1) := j.source_column_name;
2906 end loop;
2907 end loop;
2908
2909 forall i in 1..l_var1.count
2910 execute immediate 'insert into '||l_col_maps_table||'(table_name, source_table_name, column_name, source_column_name) '
2911 ||' values (:1, :2, :3, :4)' using l_var1(i), l_var2(i), l_var3(i), l_var4(i);
2912
2913 l_stmt := 'SELECT column_name FROM '||l_col_maps_table||' WHERE TABLE_NAME LIKE :1
2914 CONNECT BY TABLE_NAME = PRIOR SOURCE_TABLE_NAME
2915 AND COLUMN_NAME = PRIOR SOURCE_COLUMN_NAME
2916 START WITH TABLE_NAME = :2';
2917 OPEN cv FOR l_stmt USING 'BSC_B%', l_s_table;
2918 LOOP
2919 FETCH cv INTO l_col_map.source_column_name;
2920 EXIT WHEN cv%NOTFOUND;
2921 l_col_maps(l_col_maps.count+1) := l_col_map;
2922 end loop;
2923 CLOSE cv;
2924
2925 l_stmt := 'SELECT column_name FROM '||l_col_maps_table||
2926 ' WHERE TABLE_NAME= :1
2927 CONNECT BY PRIOR TABLE_NAME = SOURCE_TABLE_NAME
2928 AND PRIOR COLUMN_NAME = SOURCE_COLUMN_NAME
2929 START WITH SOURCE_TABLE_NAME= :2 AND SOURCE_COLUMN_NAME =:3';
2930
2931 -- now requery temp table to get the target column fed by this source_column
2932 for i in 1..l_col_maps.count loop
2933 open cv for l_stmt using l_s_table, p_b_table_name, l_col_maps(i).source_column_name;
2934 fetch cv into l_col_maps(i).column_name;
2935 close cv;
2936 end loop;
2937 commit;
2938 return l_col_maps;
2939 Exception when others then
2940 commit;
2941 fnd_file.put_line(FND_FILE.LOG, 'Error in BSC_DBGEN_BSC_READER.Get_fact_cols_from_b_table:'||p_fact||','||p_dim_set||','||p_b_table_name||','||p_col_type||':'||sqlerrm);
2942 raise;
2943
2944 End;
2945
2946
2947 procedure set_table_property(p_table_name in varchar2, p_property_name in varchar2, p_property_value in varchar2) is
2948 cursor get_old_value is
2949 select properties from bsc_db_tables where table_name=p_table_name;
2950 l_old_value varchar2(4000);
2951 l_final_value varchar2(4000);
2952 l_pos number;
2953 l_property_value varchar2(4000);
2954
2955 begin
2956 open get_old_value;
2957 fetch get_old_value into l_old_value;
2958 close get_old_value;
2959 l_property_value := p_property_name||BSC_DBGEN_STD_METADATA.BSC_ASSIGNMENT||
2960 p_property_value||BSC_DBGEN_STD_METADATA.BSC_PROPERTY_SEPARATOR;
2961 if (l_old_value is null) then
2962 update bsc_db_tables
2963 set properties = l_property_value
2964 where table_name = p_table_name;
2965 return;
2966 end if;
2967 -- already has some value, update it in place
2968 l_pos := instr(l_old_value, BSC_DBGEN_STD_METADATA.BSC_PROPERTY_SEPARATOR||p_property_name||BSC_DBGEN_STD_METADATA.BSC_ASSIGNMENT);
2969 if l_pos = 0 then -- check if first value
2970 l_pos := instr(l_old_value, p_property_name||BSC_DBGEN_STD_METADATA.BSC_ASSIGNMENT);
2971 end if;
2972 if l_pos =0 then -- this value does not exist, so append it
2973 update bsc_db_tables
2974 set properties = properties||l_property_value
2975 where table_name = p_table_name;
2976 return;
2977 end if;
2978 -- value exists, so update old value
2979 if l_pos = 1 then --first value
2980 l_final_value := l_property_Value;
2981 l_final_value := l_final_value||substr(l_old_value,
2982 instr(l_old_value, BSC_DBGEN_STD_METADATA.BSC_PROPERTY_SEPARATOR, l_pos)+
2983 length(BSC_DBGEN_STD_METADATA.BSC_PROPERTY_SEPARATOR) );
2984 else -- intermediate value
2985 l_final_value := substr(l_old_value, 1, l_pos-1+length(BSC_DBGEN_STD_METADATA.BSC_PROPERTY_SEPARATOR))||l_property_value;
2986 l_final_value := l_final_value||substr(l_old_value,
2987 instr(l_old_value, BSC_DBGEN_STD_METADATA.BSC_PROPERTY_SEPARATOR, l_pos, 2)+
2988 length(BSC_DBGEN_STD_METADATA.BSC_PROPERTY_SEPARATOR) );
2989 end if;
2990 -- now add the rest of the string back
2991
2992 update bsc_db_tables
2993 set properties = l_final_value
2994 where table_name = p_table_name;
2995 return;
2996 Exception when others then
2997 fnd_file.put_line(FND_FILE.LOG, 'Error in BSC_DBGEN_BSC_READER.set_table_property:table='||p_table_name||', property='||p_property_name||', value='||p_property_value||':'||sqlerrm);
2998 raise;
2999 end;
3000
3001 END BSC_DBGEN_BSC_READER ;
[Home] [Help]