1 PACKAGE BODY BSC_METADATA_DESC as
2 /* $Header: BSCMDDB.pls 120.0 2005/05/31 18:53 appldev noship $ */
3
4 G_PKG_NAME CONSTANT varchar2(30) := 'BSC_METADATA_DESC';
5 g_db_object varchar2(30) := null;
6
7 TYPE Analysis_Group_Rec_Type IS RECORD (
8 dependency_flag BSC_KPI_ANALYSIS_GROUPS.dependency_flag%TYPE
9 ,change_dim_set BSC_KPI_ANALYSIS_GROUPS.change_dim_set%TYPE
10 );
11
12 TYPE t_Analysis_Group_Rec_tbl IS TABLE OF Analysis_Group_Rec_Type
13 INDEX BY BINARY_INTEGER;
14
15 g_margin number;
16 g_desc_db_flag boolean;
17 g_row_num number; -- count the sequence of each row in the metadata description
18
19 ------------------------------------------------------------------------------
20 -- SaveText: This procedure store a log line into the table
21 ------------------------------------------------------------------------------
22 PROCEDURE SaveText(
23 p_Text IN varchar2
24 ) IS
25 l_source varchar2(30);
26 l_session_id number;
27 BEGIN
28
29 l_source := G_PKG_NAME;
30 l_session_id := userenv('SESSIONID');
31 if g_row_num is null then
32 g_row_num := 0;
33 end if;
34 g_row_num := g_row_num + 1 ;
35
36 INSERT INTO BSC_MESSAGE_LOGS (
37 SOURCE,
38 TYPE,
39 MESSAGE,
40 CREATION_DATE,
41 CREATED_BY,
42 LAST_UPDATE_DATE,
43 LAST_UPDATED_BY,
44 LAST_UPDATE_LOGIN
45 ) VALUES(
46 l_source,
47 0,
48 p_Text,
49 sysdate,
50 l_session_id,
51 sysdate,
52 l_session_id,
53 g_row_num
54 );
55 commit;
56
57 EXCEPTION
58 WHEN OTHERS THEN
59 raise;
60
61 END SaveText;
62
63 -- ------------------------------------------------------------------
64 -- Name: put_line
65 -- Desc: For now, just a wrapper on top of fnd_file
66 -- -----------------------------------------------------------------
67 PROCEDURE put_line(
68 p_text VARCHAR2) IS
69 l_len number;
70 l_start number:=1;
71 l_end number:=1;
72 last_reached boolean:=false;
73 BEGIN
74 if p_text is null or p_text='' then
75 return;
76 end if;
77 l_len:=nvl(length(p_text),0);
78 if l_len <=0 then
79 return;
80 end if;
81 while true loop
82 l_end:=l_start+250;
83 if l_end >= l_len then
84 l_end:=l_len;
85 last_reached:=true;
86 end if;
87 /*---------------------------------------------*/
88 -- Select Here the Output:
89 FND_FILE.PUT_LINE(FND_FILE.LOG,substr(p_text, l_start, 250));
90
91 if g_desc_db_flag = true then
92 SaveText(substr(p_text, l_start, 250));
93 end if;
94 -- DBMS_OUTPUT.PUT_LINE(substr(p_text, l_start, 250));
95 /*---------------------------------------------*/
96 l_start:=l_start+250;
97 if last_reached then
98 exit;
99 end if;
100 end loop;
101 END put_line;
102
103 FUNCTION getMargin(margenCharacter VARCHAR2
104 , numCharacters NUMBER
105 ) RETURN VARCHAR2 IS
106 L_margen VARCHAR2(20);
107 L_index NUMBER;
108 BEGIN
109
110 FOR L_index IN 1.. numCharacters -1 LOOP
111 L_margen:=L_margen || margenCharacter;
112 END LOOP;
113 IF margenCharacter = '-' THEN
114 L_margen:=L_margen || '> ';
115 ELSE
116 L_margen:=L_margen || margenCharacter || ' ';
117 END IF;
118 RETURN L_margen;
119
120 EXCEPTION
121 WHEN OTHERS THEN
122 put_line(' Error Running BSC_METADATA_DESC.getMargin ');
123 put_line(SQLERRM||' ');
124 raise;
125 END getMargin ;
126
127 /*--------------------------------------------------------------------------------------*/
128
129 PROCEDURE Describe_kpi_DataSeries (
130 p_kpi_id IN NUMBER
131 ,p_Anaysis_option0 IN NUMBER
132 ,p_Anaysis_option1 IN NUMBER
133 ,p_Anaysis_option2 IN NUMBER
134 ) IS
135 l_not_found_msg VARCHAR2(2000);
136 l_not_found_msg2 VARCHAR2(2000);
137 l_not_found_msg3 VARCHAR2(2000);
138
139 l_kpi_id NUMBER;
140 l_Anaysis_option0 NUMBER;
141 l_Anaysis_option1 NUMBER ;
142 l_Anaysis_option2 NUMBER ;
143 l_Dataset_id NUMBER ;
144 l_Measure_id NUMBER ;
145
146 -------------------------------------
147 -- show the relation between Analysis options, Data Series,
148 -- Datasets and Dimension Sets
149 CURSOR c_KPI_Data_Series IS
150 SELECT A.series_id, A.Name, A.default_value
151 ,A.dataset_id, E.dim_set_id
152 FROM BSC_KPI_ANALYSIS_MEASURES_VL A
153 , bsc_db_dataset_dim_sets_v E
154 WHERE E.INDICATOR = A.INDICATOR
155 AND E.A0 = A.Analysis_Option0
156 AND E.A1 = A.Analysis_Option1
157 AND E.A2 = A.Analysis_Option2
158 AND E.Series_Id = A.Series_Id
159 AND A.INDICATOR = l_Kpi_Id
160 AND A.Analysis_Option0 = l_Anaysis_option0
161 AND A.Analysis_Option1 = l_Anaysis_option1
162 AND A.Analysis_Option2 = l_Anaysis_option2;
163 -------------------------------------
164 -- Show DataSets and Measure Information
165 CURSOR c_Data_Set IS
166 SELECT A.dataset_id, A.Name, A.COLOR_METHOD
167 ,A.SOURCE ,A.Measure_Id1,
168 B.Measure_ID M_FLAG, B.Short_Name Short_Name1, B.MEASURE_COL, B.SOURCE SOURCE1
169 ,A.Measure_Id2
170 FROM BSC_SYS_DATASETS_VL A
171 , BSC_SYS_MEASURES B
172 WHERE B.Measure_ID (+) = A.Measure_ID1
173 AND A.dataset_id = l_Dataset_id;
174 -------------------------------------
175 -- Show Measure Information
176 -- Used to show information in the secod measure of the dataset
177 CURSOR c_measure IS
178 SELECT Short_Name, MEASURE_COL, SOURCE
179 FROM BSC_SYS_MEASURES
180 WHERE Measure_ID = l_Measure_id;
181
182 BEGIN
183 l_Kpi_Id := p_Kpi_Id;
184 l_Anaysis_option0 := p_Anaysis_option0;
185 l_Anaysis_option1 := p_Anaysis_option1;
186 l_Anaysis_option2 := p_Anaysis_option2;
187 if g_margin is null then
188 g_margin := 0;
189 end if;
190 -------------- kpi Data Series
191 l_not_found_msg := getMargin('-', g_margin)||'Objective ID <'||l_Kpi_Id
192 || '>. There is any Data Series defined in table BSC_KPI_ANALYSIS_MEASURES_VL for analysis combination <' || l_Anaysis_option0 || '-'
193 || l_Anaysis_option1 || '-' || l_Anaysis_option2 || '> in the table BSC_KPI_ANALYSIS_MEASURES_VL ' ;
194 FOR bsc_cd IN c_KPI_Data_Series LOOP
195 l_not_found_msg := NULL;
196 put_line(getMargin('-', g_margin)|| 'SERIES_ID is <'||bsc_cd.SERIES_ID ||'> ' );
197 put_line(getMargin('.', g_margin+2)||'NAME is <'||bsc_cd.NAME ||'> ' );
198 put_line(getMargin('.', g_margin+2)||'DEFAULT_VALUE is <'||bsc_cd.DEFAULT_VALUE ||'> ' );
199 put_line(getMargin('.', g_margin+2)||'DIM_SET_ID is <'||bsc_cd.DIM_SET_ID ||'> ' );
200 put_line(getMargin('.', g_margin+2)||'DATASET_ID is <'||bsc_cd.DATASET_ID ||'> ' );
201 -------------- Sys Data sets
202 l_not_found_msg2 := getMargin('-', g_margin+4)||'ERROR. Objective ID <' ||l_Kpi_Id
203 || '> assigned to invalid Data Set Id in table BSC_KPI_ANALYSIS_MEASURES_VL. Dataset Id <'||bsc_cd.DATASET_ID||'> not exitst in table BSC_SYS_DATASETS_VL.' ;
204 l_Dataset_id := bsc_cd.DATASET_ID;
205 FOR bsc_dset_cd IN c_Data_Set LOOP
206 l_not_found_msg2 := NULL;
207 put_line(getMargin('.', g_margin+4)||'NAME (Data set Name)is <'||bsc_dset_cd.NAME ||'> ' );
208 put_line(getMargin('.', g_margin+4)||'SOURCE is <'||bsc_dset_cd.SOURCE ||'> ' );
209 put_line(getMargin('.', g_margin+4)||'COLOR_METHOD is <'||bsc_dset_cd.COLOR_METHOD ||'> ' );
210 put_line(getMargin('.', g_margin+4)||'MEASURE_ID1 is <'||bsc_dset_cd.MEASURE_ID1 ||'> ' );
211 put_line(getMargin('.', g_margin+6)||'SHORT_NAME is <'||bsc_dset_cd.SHORT_NAME1 ||'> ' );
212 put_line(getMargin('.', g_margin+6)||'SOURCE is <'||bsc_dset_cd.SOURCE1 ||'> ' );
213 IF bsc_dset_cd.M_FLAG IS NULL THEN
214 put_line(getMargin('.', g_margin+6)||'ERROR. Measuare Id <'||bsc_dset_cd.MEASURE_ID1 ||'> is invalid. I does not exists in BSC_SYS_MEASURES .' );
215 END IF;
216 IF bsc_dset_cd.MEASURE_ID2 IS NOT NULL THEN
217 put_line(getMargin('.', g_margin+6)||'MEASURE_ID2 is <'||bsc_dset_cd.MEASURE_ID2 ||'> ' );
218 l_not_found_msg3 := getMargin('.', g_margin+8)||'ERROR. Measuare Id <'||bsc_dset_cd.MEASURE_ID2 ||'> is invalid. I does not exists in BSC_SYS_MEASURES .' ;
219 FOR bsc_m_cd IN c_measure LOOP
220 l_not_found_msg3 := NULL;
221 put_line(getMargin('.', g_margin+8)||'SHORT_NAME is <'||bsc_m_cd.SHORT_NAME ||'> ' );
222 put_line(getMargin('.', g_margin+8)||'MEASURE_COL is <'||bsc_m_cd.MEASURE_COL ||'> ' );
223 put_line(getMargin('.', g_margin+8)||'SOURCE is <'||bsc_m_cd.SOURCE ||'> ' );
224 END LOOP;
225 IF (l_not_found_msg3 IS NOT NULL) THEN
226 put_line(l_not_found_msg3);
227 END IF;
228 END IF;
229 END LOOP;
230 IF (l_not_found_msg2 IS NOT NULL) THEN
231 put_line(l_not_found_msg2);
232 END IF;
233 END LOOP;
234 IF (l_not_found_msg IS NOT NULL) THEN
235 put_line(l_not_found_msg);
236 END IF;
237
238 EXCEPTION
239 WHEN OTHERS THEN
240 put_line(' Error Running BSC_METADATA_DESC.Describe_kpi_DataSeries ');
241 put_line(SQLERRM||' ');
242 raise;
243
244 END Describe_kpi_DataSeries;
245
246 PROCEDURE Describe_kpi(
247 p_kpi_id IN NUMBER
248 ) IS
249 -- DECLARE
250 l_KPI_Exception EXCEPTION;
251 l_error_msg VARCHAR2(32000);
252 l_not_found_msg VARCHAR2(2000);
253 l_not_found_msg2 VARCHAR2(2000);
254 l_not_found_msg3 VARCHAR2(2000);
255
256 l_not_Analysis_Options0_msg VARCHAR2(2000);
257 l_not_Analysis_Options1_msg VARCHAR2(2000);
258 l_not_Analysis_Options2_msg VARCHAR2(2000);
259 l_not_Data_Series_msg VARCHAR2(2000);
260 l_not_DataSets_msg VARCHAR2(2000);
261 l_not_Measures_msg VARCHAR2(2000);
262
263 l_database VARCHAR2(100);
264 l_Kpi_Id NUMBER;
265 l_kpi_group_id NUMBER;
266 l_Dim_set_Id NUMBER;
267 l_Dim_Id NUMBER;
268 l_Dim_Obj_Id NUMBER;
269 l_Analysis_Group_Id NUMBER;
270 l_Parent_Option_Id NUMBER;
271 l_Grandparent_Option NUMBER;
272 l_periodicity_id NUMBER;
273 l_Dim_combination VARCHAR2(100);
274
275 l_Analysis_Group_Rec_tbl t_Analysis_Group_Rec_tbl;
276 l_Analysis_Group_Rec_Type Analysis_Group_Rec_Type;
277 l_index NUMBER;
278
279 -------------------------------------
280 CURSOR c_KPI_Info IS
281 SELECT Indicator
282 , Name
283 , Ind_Group_ID
284 , Prototype_Flag
285 , Indicator_Type
286 , Share_Flag
287 , Short_Name
288 , Source_Indicator
289 FROM BSC_KPIS_VL
290 WHERE Indicator = l_Kpi_Id;
291 -------------------------------------
292 CURSOR c_Share_Kpi_Ids IS
293 SELECT indicator
294 , Name
295 , Short_Name
296 FROM BSC_KPIS_VL
297 WHERE Source_Indicator = l_Kpi_Id;
298 -------------------------------------
299 CURSOR c_Tab_Ids IS
300 SELECT TI.TAB_ID, T.NAME
301 FROM BSC_TAB_INDICATORS TI
302 ,BSC_TABS_VL T
303 WHERE TI.INDICATOR = l_Kpi_Id
304 AND T.TAB_ID (+) = TI.TAB_ID;
305 -------------------------------------
306 CURSOR c_KPI_Group IS
307 SELECT IND_GROUP_ID, NAME, TAB_ID
308 FROM BSC_TAB_IND_GROUPS_VL
309 WHERE IND_GROUP_ID = l_kpi_group_id
310 AND ( TAB_ID = -1 OR TAB_ID IN
311 (SELECT TAB_ID FROM BSC_TAB_INDICATORS WHERE INDICATOR = l_Kpi_Id));
312 -------------------------------------
313 CURSOR c_KPI_Responsibilities IS
314 SELECT K.RESPONSIBILITY_ID, R.RESPONSIBILITY_NAME
315 FROM BSC_USER_KPI_ACCESS K,
316 BSC_RESPONSIBILITY_VL R
317 WHERE K.INDICATOR = l_Kpi_Id
318 AND K.RESPONSIBILITY_ID = R.RESPONSIBILITY_ID (+);
319 -------------------------------------
320 CURSOR c_KPI_Dim_sets IS
321 SELECT DIM_SET_ID, NAME
322 FROM BSC_KPI_DIM_SETS_VL
323 WHERE INDICATOR = l_Kpi_Id;
324 -------------------------------------
325 CURSOR c_KPI_Dimensions IS
326 SELECT A.Name, A.Short_Name, B.Dim_Group_Id
327 FROM BSC_SYS_DIM_GROUPS_VL A
328 , BSC_KPI_DIM_GROUPS B
329 WHERE A.Dim_Group_Id (+) = B.Dim_Group_Id
330 AND B.Indicator = l_Kpi_Id
331 AND B.Dim_Set_Id = l_Dim_set_Id;
332 -------------------------------------
333 CURSOR c_KPI_Dim_obj IS
334 SELECT * FROM (
335 SELECT A.Name, A.Short_Name, A.Dim_Level_Id, A.Source, B.Level_Table_Name
336 ,B.LEVEL_VIEW_NAME, B.LEVEL_PK_COL, B.DIM_LEVEL_INDEX
337 FROM BSC_SYS_DIM_LEVELS_VL A
338 , BSC_KPI_DIM_LEVELS_VL B
339 , BSC_SYS_DIM_LEVELS_BY_GROUP C
340 WHERE A.Level_Table_Name (+) = B.Level_Table_Name
341 AND C.dim_level_id = nvl(A.dim_level_id, C.dim_level_id)
342 AND B.Indicator = l_Kpi_Id
343 AND B.Dim_Set_Id = l_Dim_set_Id
344 AND nvl(C.Dim_Group_Id, l_dim_id ) = l_dim_id
345 )
346 ORDER BY DIM_LEVEL_INDEX;
347 -------------------------------------
348 CURSOR c_KPI_Analysis_Groups IS
349 SELECT ANALYSIS_GROUP_ID, DEPENDENCY_FLAG, CHANGE_DIM_SET
350 FROM BSC_KPI_ANALYSIS_GROUPS
351 WHERE INDICATOR = l_Kpi_Id;
352 -------------------------------------
353 CURSOR c_KPI_Analysis_Options0 IS
354 SELECT A.ANALYSIS_GROUP_ID, A.OPTION_ID, A.NAME, A.DIM_SET_ID
355 FROM BSC_KPI_ANALYSIS_OPTIONS_VL A
356 WHERE A.INDICATOR = l_Kpi_Id
357 AND A.ANALYSIS_GROUP_ID = 0
358 AND A.PARENT_OPTION_ID = 0
359 AND A.GRANDPARENT_OPTION_ID = 0
360 ORDER BY OPTION_ID;
361
362 CURSOR c_KPI_Analysis_Options1 IS
363 SELECT A.ANALYSIS_GROUP_ID, A.OPTION_ID, A.NAME, A.DIM_SET_ID
364 FROM BSC_KPI_ANALYSIS_OPTIONS_VL A
365 WHERE A.INDICATOR = l_Kpi_Id
366 AND A.ANALYSIS_GROUP_ID = 1
367 AND A.PARENT_OPTION_ID = l_Parent_Option_Id
368 AND A.GRANDPARENT_OPTION_ID = 0
369 ORDER BY OPTION_ID;
370
371 CURSOR c_KPI_Analysis_Options2 IS
372 SELECT A.ANALYSIS_GROUP_ID, A.OPTION_ID, A.NAME, A.DIM_SET_ID
373 FROM BSC_KPI_ANALYSIS_OPTIONS_VL A
374 WHERE A.INDICATOR = l_Kpi_Id
375 AND A.ANALYSIS_GROUP_ID = 2
376 AND A.PARENT_OPTION_ID = l_Parent_Option_Id
377 AND A.GRANDPARENT_OPTION_ID = l_Grandparent_Option
378 ORDER BY OPTION_ID;
379 ------------------------------------
380 /* data series, Data sets, Measures see procedure
381 Describe_kpi_Dataseries
382 */
383 -------------------------------------
384 CURSOR c_KPI_Periodicities IS
385 SELECT PERIODICITY_ID, DISPLAY_ORDER, CURRENT_PERIOD, TARGET_LEVEL
386 FROM BSC_KPI_PERIODICITIES A
387 WHERE INDICATOR = l_Kpi_Id;
388
389 CURSOR c_SYS_Periodicity IS
390 SELECT A.NAME, A.PERIODICITY_TYPE, A.PERIOD_TYPE_ID, A.RECORD_TYPE_ID
391 , A.CALENDAR_ID
392 , B.NAME CALENDAR_NAME , B.FISCAL_YEAR
393 FROM BSC_SYS_PERIODICITIES_VL A
394 , BSC_SYS_CALENDARS_VL B
395 WHERE A.CALENDAR_ID (+) = B.CALENDAR_ID
396 AND A.PERIODICITY_ID = l_periodicity_id;
397
398 -------------------------------------
399 CURSOR c_KPI_Calculations IS
400 SELECT A.CALCULATION_ID, B.MEANING AS NAME
401 ,A.USER_LEVEL0, A.USER_LEVEL1, A.DEFAULT_VALUE
402 FROM BSC_LOOKUPS B
403 ,BSC_KPI_CALCULATIONS A
404 WHERE B.LOOKUP_TYPE = 'BSC_CALCULATION'
405 AND A.INDICATOR = l_Kpi_Id
406 AND NVL(TO_NUMBER(B.LOOKUP_CODE), A.CALCULATION_ID ) = A.CALCULATION_ID;
407 -------------------------------------
408 CURSOR c_KPI_Properties IS
409 SELECT PROPERTY_CODE, PROPERTY_VALUE, SECONDARY_VALUE
410 FROM BSC_KPI_PROPERTIES
411 WHERE INDICATOR = l_Kpi_Id;
412 -------------------------------------
413 CURSOR c_KPI_Data_Tables IS
414 SELECT PERIODICITY_ID, DIM_SET_ID, LEVEL_COMB
415 ,TABLE_NAME, FILTER_CONDITION, MV_NAME, PROJECTION_SOURCE
416 ,DATA_SOURCE, SQL_STMT,PROJECTION_DATA
417 FROM BSC_KPI_DATA_TABLES
418 WHERE INDICATOR = l_Kpi_Id
419 ORDER BY PERIODICITY_ID, DIM_SET_ID, LEVEL_COMB ;
420 -------------------------------------
421
422 -- invalide DIM Set ids in table BSC_KPI_DIM_GROUPS
423 CURSOR c_invalide_dim_sets1 IS
424 SELECT DISTINCT K.DIM_SET_ID
425 FROM BSC_KPI_DIM_GROUPS K
426 WHERE K.Indicator = l_Kpi_Id
427 AND K.Dim_Set_Id NOT IN (
428 SELECT DIM_SET_ID
429 FROM BSC_KPI_DIM_SETS_VL
430 WHERE INDICATOR = l_Kpi_Id
431 );
432
433 BEGIN
434 l_Kpi_Id := p_kpi_id;
435 put_line('**** METADATA DESCRIPTION OBJECTIVE <'||l_Kpi_Id||'> BEGIN HERE ****');
436 SELECT Name INTO l_database FROM V$DATABASE;
437 l_error_msg := ' Objective ID <'||l_Kpi_Id||'> does not exists in '||l_database||' envionment';
438 --PART 1 OF INFORMATION, get general information from BSC_KPIS_VL table
439 put_line('---------------------------------------------------------------------------');
440 put_line('General Objective Information from BSC_KPIS_VL table');
441 put_line('---------------------------------------------------------------------------');
442 FOR bsc_cd IN c_KPI_Info LOOP
443 l_error_msg := NULL;
444 put_line('-> INDICATOR (Objective Id) is <'||bsc_cd.INDICATOR||'>');
445 put_line('.... NAME (Objective Name) is <'||bsc_cd.NAME||'>');
446 put_line('.... IND_GROUP_ID is <'||bsc_cd.IND_GROUP_ID||'>');
447 put_line('.... PROTOTYPE_FLAG is <'||bsc_cd.PROTOTYPE_FLAG||'>');
448 put_line('.... INDICATOR_TYPE is <'||bsc_cd.INDICATOR_TYPE||'>');
449 IF (bsc_cd.Share_Flag = 1) THEN
450 put_line('.... SHARE_FLAG is <'||bsc_cd.SHARE_FLAG||'>' ||' <- (This is a Master KPI)' );
451 ELSE
452 put_line('.... SHARE_FLAG is <'||bsc_cd.SHARE_FLAG||'>' ||' <- (This is a Shared KPI)' );
453 put_line('.... SOURCE_INDICATOR is <'||bsc_cd.SOURCE_INDICATOR||'>' );
454 END IF;
455 put_line('.... SHORT_NAME is <'||bsc_cd.SHORT_NAME||'>');
456 l_kpi_group_id :=bsc_cd.Ind_Group_ID;
457 END LOOP;
458 IF (l_error_msg IS NOT NULL) THEN
459 RAISE l_KPI_Exception;
460 END IF;
461
462 l_not_found_msg := ' There is not any Shared Objective';
463 put_line('---------------------------------------------------------------------------');
464 put_line('Shared Objectives Associated with Objective <'|| l_Kpi_Id||'> :');
465 put_line('---------------------------------------------------------------------------');
466 FOR bsc_cd1 IN c_Share_Kpi_Ids LOOP
467 l_not_found_msg := null;
468 put_line('-> INDICATOR <'||TO_CHAR(bsc_cd1.Indicator)||'> NAME <'||bsc_cd1.Name||'> Short Name <'||bsc_cd1.Short_Name||'>');
469 END LOOP;
470 IF (l_not_found_msg IS NOT NULL) THEN
471 put_line(l_not_found_msg);
472 END IF;
473
474 l_not_found_msg := ' Objective <'||l_Kpi_Id||'> is not associated to any Scorecard ';
475 put_line('---------------------------------------------------------------------------');
479 l_not_found_msg := NULL;
476 put_line('Scorecard Information from BSC_TAB_INDICATORS and BSC_TABS_VL table');
477 put_line('---------------------------------------------------------------------------');
478 FOR bsc_cd IN c_Tab_Ids LOOP
480 put_line('-> TAB_ID (Scorecard Id) is <'||bsc_cd.TAB_ID||'>');
481 put_line('.... NAME <'||bsc_cd.NAME||'>');
482 IF bsc_cd.NAME IS NULL THEN
483 put_line('.... ERROR. Objective assigned to a not existing Scorecard. TAB_ID <'||bsc_cd.TAB_ID||'> does not exist ' );
484 END IF;
485 END LOOP;
486 IF (l_not_found_msg IS NOT NULL) THEN
487 put_line(l_not_found_msg);
488 END IF;
489
490 l_not_found_msg := 'ERROR. Objective Id <'||l_Kpi_Id||'> is assigned to a not existing Objective Group ID (IND_GROUP_ID)in table BSC_KPIS_B. Objective Group Id does not exist in BSC_TAB_IND_GROUPS_VL ';
491 put_line('---------------------------------------------------------------------------');
492 put_line('Objective Group Information from BSC_TAB_IND_GROUPS_VL table:');
493 put_line('---------------------------------------------------------------------------');
494 FOR bsc_cd IN c_KPI_Group LOOP
495 l_not_found_msg := NULL;
496 put_line('-> IND_GROUP_ID (Objective Group ID) is <'||bsc_cd.IND_GROUP_ID||'>');
497 put_line('.... NAME is <'||bsc_cd.NAME||'>');
498 put_line('.... TAB_ID (Scorecard ID) is <'||bsc_cd.TAB_ID||'>');
499 END LOOP;
500 IF (l_not_found_msg IS NOT NULL) THEN
501 put_line(l_not_found_msg);
502 END IF;
503
504 l_not_found_msg := ' ERROR. Objective ID <'||l_Kpi_Id||'> is not assigned to any Responsibility';
505 put_line('---------------------------------------------------------------------------');
506 put_line('Objective Responsibilities from BSC_USER_KPI_ACCESS table: ');
507 put_line('---------------------------------------------------------------------------');
508 FOR bsc_cd IN c_KPI_Responsibilities LOOP
509 l_not_found_msg := NULL;
510 put_line('-> RESPONSIBILITY_ID is <'||bsc_cd.RESPONSIBILITY_ID||'>');
511 IF bsc_cd.RESPONSIBILITY_NAME IS NOT NULL THEN
512 put_line('.... RESPONSIBILITY_NAME <'||bsc_cd.RESPONSIBILITY_NAME||'>');
513 ELSE
514 put_line('.... ERROR. Objective Responsibility ID <'||bsc_cd.RESPONSIBILITY_ID||'> is invalide. Not found in BSC_RESPONSIBILITY_VL.' );
515 END IF;
516 END LOOP;
517 IF (l_not_found_msg IS NOT NULL) THEN
518 put_line(l_not_found_msg);
519 END IF;
520
521 --PART 2 OF INFORMATION, get information about dimension sets from BSC_KPI_DIM_SETS_VL table
522 l_not_found_msg := ' There is not Dimension Sets defined in BSC_KPI_DIM_SETS_VL table ';
523 put_line('---------------------------------------------------------------------------');
524 put_line('Objective Dimension Sets Information from BSC_KPI_DIM_SETS_VL table:');
525 put_line('---------------------------------------------------------------------------');
526 FOR bsc_cd IN c_KPI_Dim_sets LOOP
527 l_not_found_msg := NULL;
528 put_line('-> DIM_SET_ID (Dimension Set Id) is <'||bsc_cd.DIM_SET_ID||'>');
529 put_line('.... NAME (Dimension Set Name) is <'||bsc_cd.NAME||'>');
530 --PART 3 OF INFORMATION, get information about dimensions within KPI's Dimension Set in table BSC_KPI_DIM_GROUPS
531 l_dim_set_id := bsc_cd.DIM_SET_ID;
532 FOR bsc_dim_cd IN c_KPI_Dimensions LOOP
533 IF bsc_dim_cd.Name IS NOT NULL THEN
534 put_line('---> DIM_GROUP_ID (Dimension Id) is <'||bsc_dim_cd.DIM_GROUP_ID||'> ');
535 put_line('...... NAME (Dimension Name) is <'||bsc_dim_cd.NAME||'>');
536 put_line('...... SHORT_NAME is <'||bsc_dim_cd.SHORT_NAME||'>');
537 --PART 4 OF INFORMATION, get information about dimension objects within KPI's Dimension Set in table BSC_KPI_DIM_LEVELS_VL
538 l_dim_id := bsc_dim_cd.Dim_Group_Id;
539 FOR bsc_dim_obj_cd IN c_KPI_Dim_obj LOOP
540 IF bsc_dim_obj_cd.Dim_Level_Id IS NOT NULL THEN
541 put_line('-----> DIM_LEVEL_ID (Dim. Object Id)is <'||bsc_dim_obj_cd.DIM_LEVEL_ID||'> ');
542 put_line('........ NAME (Dim. Object Name) is <'||bsc_dim_obj_cd.NAME||'>');
543 put_line('........ SHORT_NAME is <'||bsc_dim_obj_cd.SHORT_NAME||'>');
544 put_line('........ LEVEL_TABLE_NAME is <'||bsc_dim_obj_cd.LEVEL_TABLE_NAME||'>');
545 put_line('........ LEVEL_VIEW_NAME is <'||bsc_dim_obj_cd.LEVEL_VIEW_NAME||'>');
546 put_line('........ LEVEL_PK_COL is <'||bsc_dim_obj_cd.LEVEL_PK_COL||'>');
547 put_line('........ DIM_LEVEL_INDEX is <'||bsc_dim_obj_cd.DIM_LEVEL_INDEX||'>');
548 ELSE
549 put_line('-----> DIM_LEVEL_INDEX is <'||bsc_dim_obj_cd.DIM_LEVEL_INDEX||'>');
550 put_line('........ LEVEL_TABLE_NAME is <'||bsc_dim_obj_cd.LEVEL_TABLE_NAME||'>');
551 put_line('........ Error. LEVEL_TABLE_NAME <'||bsc_dim_obj_cd.Level_Table_Name||'> is invalide in table BSC_KPI_DIM_LEVELS_VL' );
552 END IF;
553 END LOOP;
554 ELSE
555 put_line('---> DIM_GROUP_ID (Dimension Id) is <'||bsc_dim_cd.DIM_GROUP_ID||'> ');
556 put_line('....... Error: Invalid Dimension Id <'||bsc_dim_cd.Dim_Group_Id||'> found in table BSC_KPI_DIM_GROUPS' );
557 END IF;
558 END LOOP;
559 END LOOP;
560 IF (l_not_found_msg IS NOT NULL) THEN
561 put_line(l_not_found_msg);
562 END IF;
563 --PART 5 OF INFORMATION
564
565 l_Analysis_Group_Rec_Type.DEPENDENCY_FLAG := 0;
566 l_Analysis_Group_Rec_Type.CHANGE_DIM_SET := NULL;
567 l_Analysis_Group_Rec_tbl(0) := l_Analysis_Group_Rec_Type;
568 l_Analysis_Group_Rec_tbl(1) := l_Analysis_Group_Rec_Type;
569 l_Analysis_Group_Rec_tbl(2) := l_Analysis_Group_Rec_Type;
570
571 -------------- Analsysis Groups General Information.
572 l_not_found_msg := ' There is not any Analysis Group defined in table BSC_KPI_ANALYSIS_GROUPS';
573 put_line('---------------------------------------------------------------------------');
574 put_line('Objective Id <'||l_Kpi_Id||'> Analysis Groups (KPI Groups) Definition:');
575 put_line('---------------------------------------------------------------------------');
576 FOR bsc_AG_cd IN c_KPI_Analysis_Groups LOOP
577 l_not_found_msg := NULL;
578 put_line('-> ANALYSIS_GROUP_ID is <'||bsc_AG_cd.ANALYSIS_GROUP_ID ||'> ' );
579 put_line('... DEPENDENCY_FLAG is <'||bsc_AG_cd.DEPENDENCY_FLAG ||'> ' );
580 put_line('... CHANGE_DIM_SET is <'||bsc_AG_cd.CHANGE_DIM_SET ||'> ' );
581
582 l_Analysis_Group_Rec_Type.DEPENDENCY_FLAG := bsc_AG_cd.DEPENDENCY_FLAG;
583 l_Analysis_Group_Rec_Type.CHANGE_DIM_SET := bsc_AG_cd.CHANGE_DIM_SET;
584 l_Analysis_Group_Rec_tbl(bsc_AG_cd.ANALYSIS_GROUP_ID) := l_Analysis_Group_Rec_Type;
585
586 END LOOP;
587 IF (l_not_found_msg IS NOT NULL) THEN
588 put_line(l_not_found_msg);
589 END IF;
590
591 -----------Analysis Options Structur
592 put_line('---------------------------------------------------------------------------');
593 put_line('Objective ID <'||l_Kpi_Id||'> Analysis Options (KPIs) - Datasets - Measures - Dim. Sets ');
594 put_line('---------------------------------------------------------------------------');
595 l_not_Analysis_Options0_msg := 'Objective Id <'||l_Kpi_Id||'> does not have any Analysis Option Defined for Group ID <0> IN Table BSC_KPI_ANALYSIS_OPTIONS_VL';
596 FOR bsc_AO0_cd IN c_KPI_Analysis_Options0 LOOP
597 l_not_Analysis_Options0_msg := NULL;
601 put_line('.... DIM_SET_ID is <'||bsc_AO0_cd.DIM_SET_ID ||'> ' );
598 put_line('-> ANALYSIS_GROUP_ID <'||bsc_AO0_cd.ANALYSIS_GROUP_ID ||'> - OPTION_ID <'||bsc_AO0_cd.OPTION_ID ||'> ' );
599 put_line('.... NAME is <'||bsc_AO0_cd.NAME ||'> ' );
600 IF l_Analysis_Group_Rec_tbl(0).CHANGE_DIM_SET = 1 THEN
602 END IF;
603 -----------Analysis Options 1
604 l_not_Analysis_Options1_msg := 'Objective Id <'||l_Kpi_Id||'> does not have any Analysis Option Defined for Group ID <1> IN Table BSC_KPI_ANALYSIS_OPTIONS_VL';
605 IF l_Analysis_Group_Rec_tbl(1).DEPENDENCY_FLAG = 1 THEN
606 l_Parent_Option_Id := bsc_AO0_cd.OPTION_ID;
607 ELSE
608 l_Parent_Option_Id := 0;
609 END IF;
610 FOR bsc_AO1_cd IN c_KPI_Analysis_Options1 LOOP
611 l_not_Analysis_Options1_msg := NULL;
612 put_line('---> ANALYSIS_GROUP_ID <'||bsc_AO1_cd.ANALYSIS_GROUP_ID ||'> - OPTION_ID <'||bsc_AO1_cd.OPTION_ID ||'> ' );
613 put_line('...... NAME is <'||bsc_AO1_cd.NAME ||'> ' );
614 IF l_Analysis_Group_Rec_tbl(1).CHANGE_DIM_SET = 1 THEN
615 put_line('..... DIM_SET_ID is <'||bsc_AO1_cd.DIM_SET_ID ||'> ' );
616 END IF;
617 -----------Analysis Options 2
618 l_not_Analysis_Options2_msg := 'Objective Id <'||l_Kpi_Id||'> does not have any Analysis Option Defined for Group ID <2> IN Table BSC_KPI_ANALYSIS_OPTIONS_VL';
619 IF l_Analysis_Group_Rec_tbl(2).DEPENDENCY_FLAG = 1 THEN
620 l_Parent_Option_Id := bsc_AO1_cd.OPTION_ID;
621 ELSE
622 l_Parent_Option_Id := 0;
623 END IF;
624 IF l_Analysis_Group_Rec_tbl(1).DEPENDENCY_FLAG = 1 THEN
625 l_Grandparent_Option := bsc_AO0_cd.OPTION_ID;
626 ELSE
627 l_Grandparent_Option := 0;
628 END IF;
629 FOR bsc_AO2_cd IN c_KPI_Analysis_Options2 LOOP
630 l_not_Analysis_Options2_msg := NULL;
631 put_line('-----> ANALYSIS_GROUP_ID <'||bsc_AO2_cd.ANALYSIS_GROUP_ID ||'> - OPTION_ID <'||bsc_AO2_cd.OPTION_ID ||'> ' );
632 put_line('........ NAME is <'||bsc_AO2_cd.NAME ||'> ' );
633 IF l_Analysis_Group_Rec_tbl(2).CHANGE_DIM_SET = 1 THEN
634 put_line('........ DIM_SET_ID is <'||bsc_AO2_cd.DIM_SET_ID ||'> ' );
635 END IF;
636 g_margin := 8;
637 Describe_kpi_DataSeries (
638 p_kpi_id => l_kpi_id
639 ,p_Anaysis_option0 => bsc_AO0_cd.OPTION_ID
640 ,p_Anaysis_option1 => bsc_AO1_cd.OPTION_ID
641 ,p_Anaysis_option2 => bsc_AO2_cd.OPTION_ID
642 );
643 END LOOP;
644 IF (l_not_Analysis_Options2_msg IS NOT NULL) THEN
645 -- put_line(l_not_Analysis_Options1_msg);
646 g_margin := 6;
647 Describe_kpi_DataSeries (
648 p_kpi_id => l_kpi_id
649 ,p_Anaysis_option0 => bsc_AO0_cd.OPTION_ID
650 ,p_Anaysis_option1 => bsc_AO1_cd.OPTION_ID
651 ,p_Anaysis_option2 => 0
652 );
653 END IF;
654 END LOOP;
655 IF (l_not_Analysis_Options1_msg IS NOT NULL) THEN
656 --put_line(l_not_Analysis_Options1_msg);
657 g_margin := 4;
658 Describe_kpi_DataSeries (
659 p_kpi_id => l_kpi_id
660 ,p_Anaysis_option0 => bsc_AO0_cd.OPTION_ID
661 ,p_Anaysis_option1 => 0
662 ,p_Anaysis_option2 => 0
663 );
664 END IF;
665 END LOOP;
666 IF (l_not_Analysis_Options0_msg IS NOT NULL) THEN
667 put_line(l_not_Analysis_Options0_msg);
668 END IF;
669
670 -------------- kpi Periodicites Information.
671 l_not_found_msg := ' There is not any Periodicity defined in table BSC_KPI_PERIODICITIES ';
672 put_line('---------------------------------------------------------------------------');
673 put_line('Objective ID <'||l_Kpi_Id||'> Periodicities: ');
674 put_line('---------------------------------------------------------------------------');
675 FOR bsc_cd IN c_KPI_Periodicities LOOP
676 l_not_found_msg := NULL;
677 put_line('-> PERIODICITY_ID is <'||bsc_cd.PERIODICITY_ID ||'> ' );
678 -------------- Sys Periodicites Information.
679 l_periodicity_id := bsc_cd.PERIODICITY_ID;
680 l_not_found_msg2 := '--> ERROR. Objective ID <' ||l_Kpi_Id || '> assigned to invalide periodicity in table BSC_KPI_PERIODICITIES. Periodicity Id <' ||l_periodicity_id || '> not exitst in table BSC_SYS_PERIODICITIES_VL' ;
681 FOR bsc_Per_cd IN c_SYS_Periodicity LOOP
682 l_not_found_msg2 := NULL;
683 put_line('.... PERIODICITY_NAME is <'||bsc_Per_cd.NAME ||'> ' );
684 put_line('.... PERIODICITY_TYPE is <'||bsc_Per_cd.PERIODICITY_TYPE ||'> ' );
685 put_line('.... PERIOD_TYPE_ID is <'||bsc_Per_cd.PERIOD_TYPE_ID ||'> ' );
686 put_line('.... RECORD_TYPE_ID is <'||bsc_Per_cd.RECORD_TYPE_ID ||'> ' );
687 put_line('.... CALENDAR_ID is <'||bsc_Per_cd.CALENDAR_ID ||'> ' );
688 IF bsc_Per_cd.CALENDAR_NAME IS NOT NULL THEN
689 put_line('...... CALENDAR_NAME is <'||bsc_Per_cd.CALENDAR_NAME ||'> ' );
690 put_line('...... FISCAL_YEAR is <'||bsc_Per_cd.FISCAL_YEAR ||'> ' );
691 ELSE
692 put_line('...... ERROR. Periodicity Id <'||l_periodicity_id||'> assigned to a invalide Calendar Id in table BSC_SYS_PERIODICITIES_VL . Calendar Id <' || bsc_Per_cd.CALENDAR_ID || '> does not exists IN TABLE BSC_SYS_CALENDARS_VL. ' );
693 END IF;
694 END LOOP;
695 IF (l_not_found_msg2 IS NOT NULL) THEN
696 put_line(l_not_found_msg2);
697 END IF;
698 put_line('.. CURRENT_PERIOD, is <'||bsc_cd.CURRENT_PERIOD ||'> ' );
699 put_line('.. TARGET_LEVEL is <'||bsc_cd.TARGET_LEVEL ||'> ' );
700 END LOOP;
701 IF (l_not_found_msg IS NOT NULL) THEN
702 put_line(l_not_found_msg);
703 END IF;
707 put_line('---------------------------------------------------------------------------');
704
705 --- kpi Calculations ------------------------------------------------------------
706 l_not_found_msg := ' There is not any Calculations Metadata defined in table BSC_KPI_CALCULATIONS';
708 put_line('Objective ID <'||l_Kpi_Id||'> Calculations Definition: ');
709 put_line('---------------------------------------------------------------------------');
710 FOR bsc_cd IN c_KPI_Calculations LOOP
711 l_not_found_msg := NULL;
712 put_line('-> CALCULATION_ID is <'||bsc_cd.CALCULATION_ID||'>' );
713 put_line('.... NAME is <'||bsc_cd.NAME|| '>' );
714 put_line('.... DEFAULT_VALUE is <'||bsc_cd.DEFAULT_VALUE|| '>' );
715
716 END LOOP;
717 IF (l_not_found_msg IS NOT NULL) THEN
718 put_line(l_not_found_msg);
719 END IF;
720
721 --- kpi Properties -------------------------------------------------------
722 l_not_found_msg := ' There is not any KPI Properties defined in table BSC_KPI_PROPERTIES';
723 put_line('---------------------------------------------------------------------------');
724 put_line('Objective ID <'||l_Kpi_Id||'> Properties from table BSC_KPI_PROPERTIES ');
725 put_line('---------------------------------------------------------------------------');
726 FOR bsc_cd IN c_KPI_Properties LOOP
727 l_not_found_msg := NULL;
728 put_line('-> PROPERTY_CODE is <'||bsc_cd.PROPERTY_CODE||'>' );
729 put_line('.... PROPERTY_VALUE is <'||bsc_cd.PROPERTY_VALUE||'>' );
730 put_line('.... SECONDARY_VALUE is <'||bsc_cd.SECONDARY_VALUE||'>' );
731 END LOOP;
732 IF (l_not_found_msg IS NOT NULL) THEN
733 put_line(l_not_found_msg);
734 END IF;
735
736 --- kpi Data Tables Definition--------------------------------------------
737 l_not_found_msg := ' There is not any Data Table Defined in table c_KPI_Data_Tables';
738 put_line('---------------------------------------------------------------------------');
739 put_line('Objective ID <'||l_Kpi_Id||'> Data Tables Definition: ');
740 put_line('---------------------------------------------------------------------------');
741
742 l_periodicity_id := -999;
743 FOR bsc_cd IN c_KPI_Data_Tables LOOP
744 l_not_found_msg := NULL;
745 IF bsc_cd.PERIODICITY_ID <> l_periodicity_id then
746 put_line('-> PERIODICITY_ID is <'||bsc_cd.PERIODICITY_ID||'>' );
747 l_periodicity_id := bsc_cd.PERIODICITY_ID;
748 l_Dim_set_Id := -999;
749 END IF;
750 IF bsc_cd.DIM_SET_ID <> l_Dim_set_Id then
751 put_line('--> DIM_SET_ID is <'||bsc_cd.DIM_SET_ID||'>' );
752 l_Dim_set_Id := bsc_cd.DIM_SET_ID;
753 l_Dim_combination := '-999';
754 END IF;
755 IF bsc_cd.LEVEL_COMB <> l_Dim_combination then
756 put_line('-----> LEVEL_COMB is <'||bsc_cd.LEVEL_COMB||'>' );
757 l_Dim_combination := bsc_cd.LEVEL_COMB;
758 END IF;
759 put_line('........ TABLE_NAME is <'||bsc_cd.TABLE_NAME||'>' );
760 put_line('........ MV_NAME is <'||bsc_cd.MV_NAME||'>' );
761 put_line('........ FILTER_CONDITION is <'||bsc_cd.FILTER_CONDITION||'>' );
762 put_line('........ PROJECTION_SOURCE is <'||bsc_cd.PROJECTION_SOURCE||'>' );
763 put_line('........ DATA_SOURCE is <'||bsc_cd.DATA_SOURCE||'>' );
764 put_line('........ SQL_STMT is <'||bsc_cd.SQL_STMT||'>' );
765 put_line('........ PROJECTION_DATA is <'||bsc_cd.PROJECTION_DATA||'>' );
766 END LOOP;
767 IF (l_not_found_msg IS NOT NULL) THEN
768 put_line(l_not_found_msg);
769 END IF;
770
771 --PART 6 OF INFORMATION INVALIDE METADATA
772 l_not_found_msg := ' Invalide Metadata not found.';
773 put_line('---------------------------------------------------------------------------');
774 put_line('Objective ID <'||l_Kpi_Id||'> Additional Invalide KPI Metadata: ');
775 put_line('---------------------------------------------------------------------------');
776 FOR bsc_cd IN c_invalide_dim_sets1 LOOP
777 l_not_found_msg := NULL;
778 put_line(' Invalide Dimension Set ID <'||bsc_cd.DIM_SET_ID||'> in BSC_KPI_DIM_GROUPS table ' );
779 END LOOP;
780 IF (l_not_found_msg IS NOT NULL) THEN
781 put_line(l_not_found_msg);
782 END IF;
783
784 put_line('**** METADATA DESCRIPTION OBJECTIVE <'||l_Kpi_Id||'> END HERE ****');
785
786
787 EXCEPTION
788 WHEN l_KPI_Exception THEN
789 put_line('Error Running BSC_METADATA_DESC.Describe_kpi');
790 put_line(l_error_msg);
791 put_line('**** INDICATOR DETAILS ENDS HERE ****');
792 WHEN OTHERS THEN
793 put_line('Error Running BSC_METADATA_DESC.Describe_kpi');
794 put_line(SQLERRM||' <'||l_error_msg||'>');
795 put_line('**** INDICATOR DETAILS ENDS HERE ****');
796 --END;
797 --/
798 END Describe_kpi;
799
800 /*===========================================================================+
801 | PROCEDURE Run_Concurrent_ARU_Files
802 +============================================================================*/
803 PROCEDURE Run_Concurrent_Describe_kpi (
804 ERRBUF OUT NOCOPY VARCHAR2
805 ,RETCODE OUT NOCOPY VARCHAR2
806 ,p_kpi_id IN NUMBER
807 ) IS
808
809 BEGIN
810
811 Describe_kpi(p_kpi_id);
812
813 EXCEPTION
814 WHEN OTHERS THEN
815 put_line('Error at BSC_METADATA_DESC.Run_Concurrent_Describe_kpi');
816 put_line('ERRBUF: ' || SQLERRM);
817 ERRBUF := SQLERRM;
818 RETCODE := 2; -- Request completed with errors
819
820 END Run_Concurrent_Describe_kpi;
821
822 /*------------------------------------------------------------------------------------------
823 -- ClearMessages: This procedure delete all row of metadata description from
824 the table BSC_MESSAGE_LOGS that had been created by the currrent session.
825 Additional delete all metadata description not created in the current day
826 -------------------------------------------------------------------------------------------*/
827 PROCEDURE ClearText IS
828 l_session_id number;
829 BEGIN
830
831 l_session_id := userenv('SESSIONID');
832
833 DELETE FROM BSC_MESSAGE_LOGS
834 WHERE SOURCE = G_PKG_NAME
835 AND ( CREATED_BY = l_session_id
836 OR CREATION_DATE <= (SYSDATE -1)
837 );
838
839 g_row_num := 0;
840
841 EXCEPTION
842 WHEN OTHERS THEN
843 raise;
844 END ClearText;
845
846 /*------------------------------------------------------------------------------
847 getQuery: This procedure store a row of metadata description the table
848 BSC_MESSAGE_LOGS
849 ------------------------------------------------------------------------------*/
850 FUNCTION getQuery RETURN varchar2 is
851 l_sql varchar2(500);
852 l_session_id number;
853 BEGIN
854 l_session_id := userenv('SESSIONID');
855
856
857 l_sql := '
858 SELECT MESSAGE FROM BSC_MESSAGE_LOGS
859 WHERE SOURCE = ''' || G_PKG_NAME || '''
860 AND CREATED_BY = ' || l_session_id || '
861 ORDER BY LAST_UPDATE_LOGIN ';
862
863 return l_sql;
864
865 EXCEPTION
866 WHEN OTHERS THEN
867 raise;
868
869 END getQuery;
870 /*-------------------------------------------------------------------------------
871 Describe_kpi
872 Get the KPI (objective) information from the me metadata tables and made
873 a description of the objective metadat which is stored temporaty in
874 the database.
875 It return the query needed to get the metadata description
876 -------------------------------------------------------------------------------*/
877 PROCEDURE Describe_kpi(
878 p_kpi_id IN NUMBER
879 ,x_query OUT NOCOPY varchar2
880 ,x_return_status OUT NOCOPY varchar2
881 ,x_msg_count OUT NOCOPY number
882 ,x_msg_data OUT NOCOPY varchar2
883 ) IS
884 BEGIN
885
886 x_return_status := FND_API.G_RET_STS_SUCCESS;
887 g_desc_db_flag := true;
888 -- Clear Previous Metadata Description from the database
889 ClearText;
890 -- Process the Kpi Metadata
891 Describe_kpi( p_kpi_id => p_kpi_id);
892 -- Build the query to get the metadata description
893 x_query := getQuery;
894 g_desc_db_flag := false;
895
896 EXCEPTION
897 WHEN OTHERS THEN
898 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
899 IF (x_msg_data IS NOT NULL) THEN
900 x_msg_data := x_msg_data||' -> BSC_METADATA_DESC.Describe_kpi ';
901 ELSE
902 x_msg_data := SQLERRM||' at BSC_METADATA_DESC.Describe_kpi ';
903 END IF;
904
905 END Describe_kpi ;
906
907 End BSC_METADATA_DESC;
[Home] [Help]