[Home] [Help]
PACKAGE BODY: APPS.BSC_MO_UI_PKG
Source
1 Package Body BSC_MO_UI_PKG AS
2 /* $Header: BSCMOUIB.pls 120.15 2007/04/25 12:55:02 ashankar ship $ */
3
4 g_recursion_ctr NUMBER := 0;
5 g_ctr NUMBER := 0;
6
7 PROCEDURE truncateTable(pTableName IN VARCHAR2, pSchema IN VARCHAR2 DEFAULT null) IS
8 l_schema VARCHAR2(30);
9 l_stmt VARCHAR2(300);
10 BEGIN
11 l_schema := pSchema;
12 IF (l_schema IS NULL) THEN
13 l_schema := BSC_MO_HELPER_PKG.getBSCSchema;
14 END IF;
15 l_stmt := 'TRUNCATE TABLE '||l_schema||'.'||pTableName;
16 execute immediate l_stmt;
17 END;
18
19
20 /****************************************************************************
21 -- InsertRelatedTables
22 -- DESCRIPTION:
23 -- Insert in the array garrTables() all the tables in the current
24 -- graph that have any relation with the tables in the array
25 -- arrTables().
26 -- AUTHOR/DATE - MODIFICATIONS (AUTHOR/DATE/DESCRIPTION):
27 --****************************************************************************/
28
29 PROCEDURE InsertRelatedTables(numTables IN NUMBER) IS
30
31 arrNewTables dbms_sql.varchar2_table;
32 numNewTables number := 0;
33 strWhereInNewTables varchar2(1000);
34 strWhereNotInNewTables varchar2(1000);
35 l_stmt varchar2(1000);
36 l_table varchar2(100);
37 cv CurTyp;
38 strWhereInChildTables VARCHAR2(1000);
39 strWhereInParentTables VARCHAR2(1000);
40 l_error varchar2(1000);
41 l_start_time date := sysdate;
42
43 BEGIN
44 IF BSC_METADATA_OPTIMIZER_PKG.gBSCSchema IS NULL THEN
45 BSC_METADATA_OPTIMIZER_PKG.gBSCSchema := BSC_MO_HELPER_PKG.getBSCSchema;
46 END IF;
47 numNewTables := 0;
48
49 g_recursion_ctr := g_recursion_ctr +1 ;
50
51 If numTables > 0 Then
52 --insert the children
53 l_stmt := ' INSERT INTO '||BSC_METADATA_OPTIMIZER_PKG.gBSCSchema||'.BSC_TMP_BIG_IN_COND (session_id, variable_id, value_v)
54 SELECT distinct userenv(''SESSIONID''), 0, TABLE_NAME
55 FROM BSC_DB_TABLES_RELS rels
56 where source_table_name IN
57 (SELECT /*+ index(tmp bsc_tmp_big_in_cond_n1)*/
58 tmp.value_v
59 from bsc_tmp_big_in_cond tmp
60 where tmp.session_id = userenv(''SESSIONID'') and tmp.variable_id = 0)
61 minus
62 select /*+ index(cond bsc_tmp_big_in_cond_n1)*/ distinct userenv(''SESSIONID''), 0, value_v
63 from bsc_tmp_big_in_cond cond where cond.session_id =userenv(''SESSIONID'')
64 and cond.variable_id = 0';
65 EXECUTE IMMEDIATE l_stmt;
66 numNewTables := sql%ROWCOUNT;
67 l_start_Time := sysdate;
68
69 --insert the parents
70 l_stmt := 'INSERT INTO '||BSC_METADATA_OPTIMIZER_PKG.gBSCSchema||'.BSC_TMP_BIG_IN_COND (session_id, variable_id, value_v)
71 SELECT distinct userenv(''SESSIONID''), 0, SOURCE_TABLE_NAME
72 FROM BSC_DB_TABLES_RELS
73 WHERE table_name IN
74 (SELECT /*+ index(cond bsc_tmp_big_in_cond_n1)*/ value_v
75 from BSC_TMP_BIG_IN_COND WHERE session_id = userenv(''SESSIONID'') and variable_id = 0)
76 minus
77 select /*+ index(cond bsc_tmp_big_in_cond_n1)*/ userenv(''SESSIONID''), 0, value_v
78 from bsc_tmp_big_in_cond cond where session_id =userenv(''SESSIONID'') and variable_id = 0';
79
80 EXECUTE IMMEDIATE l_stmt;
81 numNewTables := numNewTables + sql%ROWCOUNT;
82 l_start_Time := sysdate;
83
84 If numNewTables > 0 Then
85 l_stmt := 'INSERT INTO '||BSC_METADATA_OPTIMIZER_PKG.gBSCSchema||'.BSC_TMP_BIG_IN_COND (session_id, variable_id, value_v)
86 SELECT/*+ ordered */
87 distinct userenv(''SESSIONID''), 0, datab.TABLE_NAME
88 FROM
89 BSC_TMP_BIG_IN_COND tmp,
90 BSC_TMP_OPT_KPI_DATA dataa, BSC_TMP_OPT_KPI_DATA datab
91 WHERE
92 tmp.session_id = userenv(''SESSIONID'') and tmp.variable_id = 0
93 and dataa.indicator = datab.indicator
94 and dataa.table_name <> datab.table_name
95 and tmp.value_v=dataa.table_name
96 AND datab.TABLE_NAME IS NOT NULL
97 minus
98 select userenv(''SESSIONID''), 0, value_v
99 from bsc_tmp_big_in_cond cond where session_id =userenv(''SESSIONID'') and variable_id = 0' ;
100 EXECUTE IMMEDIATE l_stmt;
101 numNewTables := numNewTables + sql%ROWCOUNT;
102 InsertRelatedTables (numNewTables);
103 End If;
104 End If;
105
106 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
107 bsc_mo_helper_pkg.writeTmp( 'Compl. InsertRelatedTables');
108 END IF;
109
110 EXCEPTION WHEN OTHERS THEN
111 l_error := sqlerrm;
112 bsc_mo_helper_pkg.writeTmp( 'Error in InsertRelatedTables :'||l_error, FND_LOG.LEVEL_UNEXPECTED);
113 RAISE;
114 End;
115
116
117 Procedure MarkIndicsAndTables(pProcessId IN NUMBER) IS
118
119 l_stmt varchar2(1000);
120 strWhereInIndics varchar2(1000);
121 strWhereNotInIndics varchar2(1000);
122 strWhereInTables varchar2(1000);
123 i number := 0;
124 l_table varchar2(100);
125 cv CurTyp;
126 l_error VARCHAR2(400);
127 l_start_time date := sysdate;
128
129 BEGIN
130 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
131 bsc_mo_helper_pkg.writeTmp('Inside MarkIndicsAndTables');
132 END IF;
133 --Initialize the array garrTables the tables used by the indicators in the array garrIndics()
134 --EDW Integration note:
135 --In BSC_KPI_DATA_TABLES, Metadata Optimizer is storing the name of the view (Example: BSC_3001_0_0_5_V)
136 --and the name of the S table for BSC Kpis (Example: BSC_3002_0_0_5)
137 --In this procedure we need to get tables names from a view BSC_KPI_DATA_TABLES_V.
138
139 BSC_METADATA_OPTIMIZER_PKG.gnumTables := 0;
140 BSC_METADATA_OPTIMIZER_PKG.garrTables.delete;
141
142 IF BSC_METADATA_OPTIMIZER_PKG.gnumIndics = 0 THEN
143 return;
144 END IF;
145
146 l_stmt := 'delete '||BSC_METADATA_OPTIMIZER_PKG.gBSCSchema||'.BSC_TMP_BIG_IN_COND where session_id = userenv(''SESSIONID'') and variable_id = 0';
147 execute immediate l_stmt;
148
149 strWhereInIndics := ' INDICATOR IN (SELECT INDICATOR FROM BSC_TMP_OPT_UI_KPIS WHERE PROCESS_ID = '||TO_CHAR(pProcessId)||')';
150 strWhereNotInIndics := 'NOT ('|| strWhereInIndics ||')';
151
152 l_stmt := 'INSERT INTO '||BSC_METADATA_OPTIMIZER_PKG.gBSCSchema||'.BSC_TMP_BIG_IN_COND (session_id, variable_id, value_v )
153 SELECT DISTINCT userenv(''SESSIONID''), 0, TABLE_NAME FROM BSC_TMP_OPT_KPI_DATA DATA, BSC_TMP_OPT_UI_KPIS TMP
154 WHERE TMP.INDICATOR = DATA.INDICATOR AND TMP.PROCESS_ID = :1';
155 execute immediate l_stmt using pProcessId;
156
157 BSC_METADATA_OPTIMIZER_PKG.gnumTables := sql%rowcount;
158
159 IF BSC_METADATA_OPTIMIZER_PKG.gnumTables > 0 THEN
160 --Insert in the array garrTables() all the tables in the current
161 --graph that have any relation with them
162 InsertRelatedTables(BSC_METADATA_OPTIMIZER_PKG.gnumTables);
163 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
164 bsc_mo_helper_pkg.writeTmp('Done with InsertRelatedTables');
165 END IF;
166
167 l_stmt := 'INSERT INTO BSC_TMP_OPT_UI_KPIS (INDICATOR, PROTOTYPE_FLAG, PROCESS_ID)
168 SELECT DISTINCT INDICATOR, 3, :1 FROM BSC_TMP_OPT_KPI_DATA
169 WHERE TABLE_NAME IN
170 (SELECT /*+ index(cond bsc_tmp_big_in_cond_n1)*/VALUE_V FROM BSC_TMP_BIG_IN_COND cond WHERE SESSION_ID = :2 )
171 AND INDICATOR NOT IN
172 (SELECT INDICATOR FROM BSC_TMP_OPT_UI_KPIS WHERE PROCESS_ID = :3)';
173 execute immediate l_stmt using pProcessId, USERENV('SESSIONID'), pProcessId;
174 BSC_METADATA_OPTIMIZER_PKG.gnumIndics := BSC_METADATA_OPTIMIZER_PKG.gnumIndics + sql%ROWCOUNT;
175 END IF;
176
177 EXCEPTION WHEN OTHERS THEN
178 l_error := sqlerrm;
179 bsc_mo_helper_pkg.writeTmp('Exception in MarkIndicsAndTables : '||l_error);
180 raise;
181 End;
182
183
184 --****************************************************************************
185 -- MarkIndicsForNonStrucChanges
186 -- DESCRIPTION:
187 -- The array garrIndics4() is initialized with currently flagged indicators
188 -- for non-structural changes. (Protoype_Flag = 4)
189 -- This procedure adds to the same array the related indicators.
190 -- Designer is only flagging the indicators
191 -- that are using the measure direclty. We need to flag other indicators
192 -- using the same measures alone or as part of a formula.
193 --
194 -- AUTHOR/DATE - MODIFICATIONS (AUTHOR/DATE/DESCRIPTION):
195 --***************************************************************************
196 PROCEDURE MarkIndicsForNonStrucChanges IS
197 l_stmt Varchar2(1000);
198 strWhereInIndics Varchar2(1000);
199 strWhereNotInIndics Varchar2(1000);
200 strWhereInMeasures Varchar2(1000);
201 i NUMBER := 0;
202 arrMeasuresCols DBMS_SQL.VARCHAR2_TABLE;
203 numMeasures NUMBER;
204 arrRelatedMeasuresIds DBMS_SQL.NUMBER_TABLE;
205
206 --measureCol Varchar2(1000);
207 Operands DBMS_SQL.VARCHAR2_TABLE;
208 NumOperands NUMBER;
209
210 l_measureID NUMBER;
211 l_measureCol VARCHAR2(500);
212 cv CurTyp;
213
214 l_error varchar2(400);
215 l_stack VARCHAR2(32000);
216
217 BEGIN
218 IF BSC_METADATA_OPTIMIZER_PKG.gnumIndics4 <= 0 THEN
219 return;
220 END IF;
221 --Init and array with the measures used by the indicators flagged for
222 --non-structural changes
223
224 numMeasures := 0;
225 strWhereInIndics := bsc_mo_helper_pkg.Get_New_Big_In_Cond_Number(9, 'I.INDICATOR');
226 i:= 0;
227 LOOP
228 EXIT WHEN i=BSC_METADATA_OPTIMIZER_PKG.gnumIndics4;
229 bsc_mo_helper_pkg.Add_Value_Big_In_Cond_Number( 9, BSC_METADATA_OPTIMIZER_PKG.garrIndics4(i));
230 i:=i+1;
231 END LOOP;
232
233 --PMF-BSC Integration: Filter out PMF measures
234 l_stmt := 'SELECT DISTINCT M.MEASURE_COL FROM BSC_SYS_MEASURES M, BSC_DB_MEASURE_BY_KPI_V I'
235 || ' WHERE I.MEASURE_ID = M.MEASURE_ID AND ('|| strWhereInIndics ||' )'||
236 ' AND M.TYPE = 0 AND NVL(M.SOURCE, ''BSC'') = ''BSC'' ';
237 OPEN cv FOR l_stmt;
238
239 LOOP
240 FETCH cv INTO l_measureCol;
241 EXIT when cv%NOTFOUND;
242 arrMeasuresCols(numMeasures) := l_measureCol;
243 numMeasures := numMeasures + 1;
244 END Loop;
245 CLOSE cv;
246
247
248 /*The measures in the array arrMeasuresCols are the ones that could be changed
249 For that reason the indicators were flagged to 4
250 We need to see in all system measures if there is a formula using that measure.
251 IF that happen we need to add that measure. Any kpi using that meaure should be flaged too.*/
252
253 strWhereNotInIndics := ' NOT ( ' || strWhereInIndics || ')';
254
255 l_stmt := 'SELECT DISTINCT M.MEASURE_ID, M.MEASURE_COL '
256 ||'FROM BSC_SYS_MEASURES M, BSC_DB_MEASURE_BY_KPI_V I '||
257 ' WHERE I.MEASURE_ID = M.MEASURE_ID AND ('|| strWhereNotInIndics ||' ) '||
258 ' AND M.TYPE = 0 AND NVL(M.SOURCE, ''BSC'') = ''BSC''';
259
260 OPEN cv FOR l_stmt;
261
262 LOOP
263 FETCH cv into l_measureID, l_measureCol;
264 EXIT WHEN cv%NOTFOUND;
265 NumOperands := bsc_mo_helper_pkg.GetFieldExpression(Operands, l_measureCol);
266 i:= Operands.first;
267 LOOP
268 EXIT WHEN Operands.count =0 ;
269 IF bsc_mo_helper_pkg.SearchStringExists(arrMeasuresCols, numMeasures, Operands(i)) THEN
270 --One operand of the formula is one of the measures of a indicator flagged with 4
271 --We need to add this formula (measure) to the related ones
272 arrRelatedMeasuresIds(arrRelatedMeasuresIds.count) := l_measureID;
273 END IF;
274 EXIT WHEN i = Operands.last;
275 i:= Operands.next(i);
276 END LOOP;
277 END Loop;
278 close cv;
279
280 l_stack := l_stack ||' Check 3, arrRelatedMeasuresIds.count = '|| to_char(arrRelatedMeasuresIds.count);
281 --Now we need to add to garrIndics4() all the indicators using any of the measures
282 --in arrRelatedMeasuresIds()
283
284 IF arrRelatedMeasuresIds.count > 0 THEN
285 strWhereInMeasures := bsc_mo_helper_pkg.Get_New_Big_In_Cond_Number( 9, 'MEASURE_ID');
286 i:= arrRelatedMeasuresIds.first;
287
288 LOOP
289 EXIT WHEN i=arrRelatedMeasuresIds.last;
290 bsc_mo_helper_pkg.Add_Value_Big_In_Cond_Number( 9, arrRelatedMeasuresIds(i));
291 i:= arrRelatedMeasuresIds.next(i);
292 END LOOP;
293
294 l_stmt := 'SELECT DISTINCT INDICATOR FROM BSC_DB_MEASURE_BY_KPI_V '||
295 ' WHERE ('|| strWhereInMeasures || ')';
296 open cv for L_stmt;
297 LOOP
298 fetch cv into l_measureCol;
299 EXIT WHEN cv%NOTFOUND;
300 IF Not bsc_mo_helper_pkg.SearchNumberExists(BSC_METADATA_OPTIMIZER_PKG.garrIndics4, BSC_METADATA_OPTIMIZER_PKG.gnumIndics4, l_measureCol) THEN
301 BSC_METADATA_OPTIMIZER_PKG.garrIndics4(BSC_METADATA_OPTIMIZER_PKG.gnumIndics4) := l_measureCol;
302 BSC_METADATA_OPTIMIZER_PKG.gnumIndics4 := BSC_METADATA_OPTIMIZER_PKG.gnumIndics4 + 1;
303 END IF;
304 END Loop;
305 close cv;
306 l_stack := l_stack ||' Check 5 ';
307 END IF;
308
309 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
310 bsc_mo_helper_pkg.writeTmp('Completed MarkIndicsForNonStrucChanges', FND_LOG.LEVEL_PROCEDURE);
311 END IF;
312
313 EXCEPTION WHEN OTHERS THEN
314 l_error := sqlerrm;
315 bsc_mo_helper_pkg.writeTmp('Exception in MarkIndicsForNonStrucChanges : '||l_error);
316 bsc_mo_helper_pkg.writeTmp('Local Stack dump = '||l_stack);
317 raise;
318 End;
319
320
321 PROCEDURE create_ui_kpi_table IS
322 l_stmt varchar2(1000) := 'CREATE /*GLOBAL TEMPORARY */ TABLE BSC_TMP_OPT_UI_KPIS(
323 INDICATOR NUMBER, PROTOTYPE_FLAG NUMBER, PROCESS_ID NUMBER) ';
324
325 --PRAGMA AUTONOMOUS_TRANSACTION;
326 BEGIN
327 --bsc_mo_helper_pkg.dropTable('BSC_TMP_OPT_UI_KPIS');
328 BSC_MO_HELPER_PKG.Do_DDL(l_stmt, ad_ddl.create_table, 'BSC_TMP_OPT_UI_KPIS');
329 --commit;
330 END;
331
332 --Procedure added for bug 3911548
333 procedure create_tmp_opt_kpi_data is
334 l_stmt varchar2(1000);
335 begin
336 IF (NOT BSC_DBGEN_UTILS.IS_TMP_TABLE_EXISTED('BSC_TMP_OPT_KPI_DATA')) THEN
337 begin
338 bsc_mo_helper_pkg.dropTable('BSC_TMP_OPT_KPI_DATA');
339 exception when others then
340 null;
341 end;
342
343 l_stmt := 'CREATE GLOBAL TEMPORARY TABLE BSC_TMP_OPT_KPI_DATA (INDICATOR NUMBER, TABLE_NAME VARCHAR2(100)) ON COMMIT PRESERVE ROWS';
344 BSC_MO_HELPER_PKG.Do_DDL(l_stmt, ad_ddl.create_table, 'BSC_TMP_OPT_KPI_DATA');
345 l_stmt := 'create unique index bsc_tmp_opt_kpi_data_u1 on bsc_tmp_opt_kpi_data(indicator, table_name)';
346 BSC_MO_HELPER_PKG.Do_DDL(l_stmt, ad_ddl.create_index, 'BSC_TMP_OPT_KPI_DATA_U1');
347 dbms_stats.gather_table_stats(BSC_METADATA_OPTIMIZER_PKG.gBSCSchema, 'BSC_TMP_OPT_KPI_DATA');
348 dbms_stats.gather_index_stats(BSC_METADATA_OPTIMIZER_PKG.gBSCSchema, 'BSC_TMP_OPT_KPI_DATA_U1');
349 END IF;
350 -- table may not exist after upgrade, so have to make it dynamic
351 execute immediate 'DELETE BSC_TMP_OPT_KPI_DATA';
352 --l_stmt := 'INSERT INTO BSC_TMP_OPT_KPI_DATA
353 -- SELECT DISTINCT INDICATOR, TABLE_NAME
354 -- FROM BSC_KPI_DATA_TABLES
355 -- WHERE TABLE_NAME IS NOT NULL';
356 l_stmt := 'INSERT INTO BSC_TMP_OPT_KPI_DATA
357 SELECT DISTINCT
358 TO_NUMBER
359 (
360 SUBSTR
361 (
362 TABLE_NAME,
363 INSTR(TABLE_NAME,''_'',1,2)+1,
364 INSTR(TABLE_NAME,''_'',1,3)-INSTR(TABLE_NAME,''_'',1,2)-1
365 )
366 ),
367 TABLE_NAME
368 FROM BSC_DB_TABLES_RELS
369 WHERE TABLE_NAME LIKE ''BSC_S%''
370 AND (SOURCE_TABLE_NAME LIKE ''BSC_B%''
371 OR SOURCE_TABLE_NAME LIKE ''BSC_T%'')';
372 execute immediate l_stmt;
373 end;
374
375 PROCEDURE CreateDBMeasureByKpiView IS
376 l_stmt varchar2(1000);
377 BEGIN
378 bsc_mo_helper_pkg.writeTmp('Started CreateDBMeasureByKpiView', FND_LOG.LEVEL_PROCEDURE);
379 l_stmt := ' CREATE OR REPLACE VIEW BSC_DB_MEASURE_BY_KPI_V( INDICATOR, MEASURE_ID ) AS
380 SELECT DB.INDICATOR, DS.MEASURE_ID1 AS MEASURE_ID
381 FROM BSC_KPI_ANALYSIS_MEASURES_B DB, BSC_SYS_DATASETS_B DS
382 WHERE DB.DATASET_ID = DS.DATASET_ID
383 UNION
384 SELECT DB.INDICATOR, DS.MEASURE_ID2 AS MEASURE_ID
385 FROM BSC_KPI_ANALYSIS_MEASURES_B DB, BSC_SYS_DATASETS_B DS
386 WHERE DB.DATASET_ID = DS.DATASET_ID AND DS.MEASURE_ID2 IS NOT NULL ';
387 execute immediate l_stmt;
388 bsc_mo_helper_pkg.writeTmp('Completed CreateDBMeasureByKpiView', FND_LOG.LEVEL_PROCEDURE);
389 exception when others then
390 bsc_mo_helper_pkg.writeTmp('Error in CreateDBMeasureByKpiView :'||sqlerrm, FND_LOG.LEVEL_EXCEPTION, true);
391 raise;
392 END;
393
394 -- pProcessId = -ICX_Session_Id (For cleanup)
395 PROCEDURE updateRelatedIndicators(
396 pMode IN VARCHAR2,
397 pProcessId IN NUMBER
398 ) IS
399 PRAGMA AUTONOMOUS_TRANSACTION;
400
401 l_stmt varchar2(1000);
402 l_Code number;
403 l_Name varchar2(100);
404 l_IndicatorType number;
405 l_ConfigType number;
406 l_per_inter number;
407 l_OptimizationMode number;
408 l_Action_Flag number;
409 l_Share_Flag number;
410 l_Source_Indicator number;
411 l_EDW_Flag number;
412 strWhereInIndics Varchar2(1000);
413 strWhereNotInIndics Varchar2(1000);
414 strWhereInIndics4 Varchar2(1000);
415 strWhereNotInIndics4 Varchar2(1000);
416 i number;
417 cv CurTyp;
418 l_indicator number;
419 l_indicator4 number;
420 l_table VARCHAR2(100);
421 l_error VARCHAR2(400);
422 l_start_time date := sysdate;
423 l_total_kpis NUMBER := 0;
424
425 BEGIN
426 bsc_apps.init_bsc_apps;
427 BSC_METADATA_OPTIMIZER_PKG.g_log := false;
428 BSC_METADATA_OPTIMIZER_PKG.gUIAPI := true;
429 IF BSC_METADATA_OPTIMIZER_PKG.gBSCSchema IS NULL THEN
430 BSC_METADATA_OPTIMIZER_PKG.gBSCSchema := BSC_MO_HELPER_PKG.getBSCSchema;
431 END IF;
432 BSC_METADATA_OPTIMIZER_PKG.garrIndics.delete;
433 BSC_METADATA_OPTIMIZER_PKG.gnumIndics := 0;
434 BSC_METADATA_OPTIMIZER_PKG.garrIndics4.delete;
435 BSC_METADATA_OPTIMIZER_PKG.gnumIndics4 := 0;
436 BSC_METADATA_OPTIMIZER_PKG.garrTables.delete;
437 BSC_METADATA_OPTIMIZER_PKG.gnumTables := 0;
438
439 IF (NOT BSC_MO_HELPER_PKG.tableExists('BSC_TMP_OPT_UI_KPIS')) THEN
440 create_ui_kpi_table;
441 END IF;
442
443 --EXECUTE IMMEDIATE 'delete BSC_TMP_OPT_UI_KPIS where process_Id = -200 or process_Id = 0 or process_Id is null';
444 --truncateTable('BSC_TMP_OPT_UI_KPIS', BSC_METADATA_OPTIMIZER_PKG.gBSCSchema);
445 l_stmt := 'DELETE BSC_TMP_OPT_UI_KPIS WHERE PROCESS_ID = :1 ';
446 EXECUTE IMMEDIATE l_stmt USING pProcessId;
447 COMMIT;
448
449 -- Default list for Selected Objectives
450 if (pMode = 'SELECTED' OR pMode = 'SELECTED_REPORTS' OR pMode = 'SELECTED_SIMULATIONS') then
451 l_stmt := ' INSERT INTO BSC_TMP_OPT_UI_KPIS (indicator, prototype_flag, process_id)
452 SELECT INDICATOR, prototype_flag, :1
453 FROM BSC_KPIS_VL
454 WHERE PROTOTYPE_FLAG NOT IN (1,2,3,4)';
455 execute immediate l_stmt USING pProcessId;
456 commit;
457 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
458 BSC_MO_HELPER_PKG.writeTmp( 'Completed updateRelatedIndicators');
459 END IF;
460 return;
461 end if;
462
463 create_tmp_opt_kpi_data;
464
465 if (pMode = 'ALL') THEN
466 l_stmt := ' INSERT INTO BSC_TMP_OPT_UI_KPIS (indicator, prototype_flag, process_id)
467 SELECT INDICATOR, prototype_flag, :1
468 FROM BSC_KPIS_VL
469 WHERE BSC_DBGEN_UTILS.GET_OBJECTIVE_TYPE(SHORT_NAME) = :2 ';
470 execute immediate l_stmt USING pProcessId, 'OBJECTIVE';
471 commit;
472 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
473 BSC_MO_HELPER_PKG.writeTmp( 'Completed updateRelatedIndicators');
474 END IF;
475 return;
476 end if;
477
478 -- Only Modified mode, now, all other Modes would have returned.
479 SELECT count(1) INTO l_total_kpis FROM BSC_KPIS_B;
480 l_stmt := ' INSERT INTO BSC_TMP_OPT_UI_KPIS (indicator, prototype_flag, process_id)
481 SELECT INDICATOR, prototype_flag, :1
482 FROM BSC_KPIS_VL
483 WHERE (PROTOTYPE_FLAG = 2 OR PROTOTYPE_FLAG = 3) ';
484 execute immediate l_stmt USING pProcessId;
485
486 IF (SQL%ROWCOUNT = l_total_kpis) THEN
487 commit;
488 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
489 BSC_MO_HELPER_PKG.writeTmp( 'Completed updateRelatedIndicators');
490 END IF;
491 return;
492 END If;
493
494 BSC_METADATA_OPTIMIZER_PKG.gnumIndics := SQL%ROWCOUNT;
495 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
496 BSC_MO_HELPER_PKG.writeTmp('# of Indics = '||BSC_METADATA_OPTIMIZER_PKG.gnumIndics);
497 END IF;
498
499 MarkIndicsAndTables(pProcessId);
500
501 BSC_METADATA_OPTIMIZER_PKG.gThereisStructureChange := False;
502
503 --Add indicators with flag = 4 (reconfigure update)
504 --in the collection gIndicadores
505 --Of course if the indicator is already in gIndicadores (Structural changes) we do not change it.
506 --Init an array with the Kpis in prototype 4 (changes in loader configuration)
507
508 l_stmt := 'SELECT INDICATOR FROM BSC_KPIS_B WHERE PROTOTYPE_FLAG = 4
509 MINUS
510 SELECT INDICATOR FROM BSC_TMP_OPT_ui_kpis WHERE process_id = :1
511 ORDER BY INDICATOR';
512 open cv for l_stmt USING pProcessId;
513
514 LOOP
515 fetch cv into l_indicator4;
516 exit when cv%NOTFOUND;
517 BSC_METADATA_OPTIMIZER_PKG.garrIndics4(BSC_METADATA_OPTIMIZER_PKG.gnumIndics4) := l_indicator4;
518 BSC_METADATA_OPTIMIZER_PKG.gnumIndics4 := BSC_METADATA_OPTIMIZER_PKG.gnumIndics4 + 1;
519 END Loop;
520 close cv;
521
522 --We need to add the related indicators. Designer is only flagging the indicators
523 --that are using the measure direclty. We need to flag other indicators
524 --using the same measures alone or as part of a formula.
525
526 IF BSC_METADATA_OPTIMIZER_PKG.gnumIndics4 > 0 THEN
527 CreateDBMeasureByKpiView;
528 MarkIndicsForNonStrucChanges;
529 --Add the indicators from garrIndics4() to gIndicadores
530 strWhereInIndics4 := BSC_MO_HELPER_PKG.Get_New_Big_In_Cond_Number( 2, 'INDICATOR');
531 i:= 0;
532 LOOP
533 exit when i = BSC_METADATA_OPTIMIZER_PKG.gnumIndics4;
534 BSC_MO_HELPER_PKG.Add_Value_Big_In_Cond_Number( 2, BSC_METADATA_OPTIMIZER_PKG.garrIndics4(i));
535 i:= i+1;
536 END LOOP;
537 strWhereNotInIndics4 := 'NOT (' || strWhereInIndics4 || ')';
538 l_stmt := 'INSERT INTO BSC_TMP_OPT_UI_KPIS( INDICATOR, PROTOTYPE_FLAG, process_id)
539 SELECT DISTINCT INDICATOR, 4, :1
540 FROM BSC_KPIS_VL WHERE (' || strWhereInIndics4 || ')';
541 IF BSC_METADATA_OPTIMIZER_PKG.gnumIndics > 0 THEN
542 l_stmt := l_stmt || ' minus select indicator, 4, :2 from BSC_TMP_OPT_ui_kpis WHERE process_id = :3 ';
543 execute immediate l_stmt USING pProcessId, pProcessId, pProcessId;
544 ELSE
545 execute immediate l_stmt USING pProcessId;
546 END IF;
547 END IF;
548
549 IF BSC_METADATA_OPTIMIZER_PKG.g_Sum_Level_Change <> 0 THEN
550 -- summarization change, add production indicators to the list.
551 l_stmt := ' INSERT INTO BSC_TMP_OPT_UI_KPIS (indicator, prototype_flag, process_id)
552 SELECT INDICATOR, prototype_flag, :1 FROM BSC_KPIS_VL a
553 WHERE NOT EXISTS (SELECT 1 FROM BSC_TMP_OPT_UI_KPIS b WHERE process_id = :2 and a.indicator = b.indicator)';
554 execute immediate l_stmt USING pProcessId, pProcessId;
555
556 END IF;
557
558 -- take care of shared kpis, which are NOT marked by the builder, just in case
559 l_stmt := ' INSERT INTO BSC_TMP_OPT_UI_KPIS (indicator, prototype_flag, process_id)
560 SELECT child.INDICATOR, parent.prototype_flag, :1
561 FROM BSC_KPIS_VL parent,
562 BSC_KPIS_VL child,
563 BSC_TMP_OPT_UI_KPIS uitmp
564 where uitmp.indicator = parent.indicator
565 and uitmp.process_id = :2
566 and parent.share_flag = 1
567 and child.share_flag = 2
568 and parent.indicator = child.source_indicator
569 AND NOT EXISTS (SELECT 1 FROM BSC_TMP_OPT_UI_KPIS c WHERE process_id = :3 and c.indicator = child.indicator)';
570 execute immediate l_stmt USING pProcessId, pProcessId, pProcessId;
571
572 -- insert remaining KPIS finally, UI wants it
573 l_stmt := ' INSERT INTO BSC_TMP_OPT_UI_KPIS (indicator, prototype_flag, process_id)
574 SELECT INDICATOR, prototype_flag, :1 FROM BSC_KPIS_VL a
575 WHERE NOT EXISTS (SELECT 1 FROM BSC_TMP_OPT_UI_KPIS b WHERE process_id = :2 and a.indicator = b.indicator)';
576 execute immediate l_stmt USING pProcessId, pProcessId;
577
578 -- Get rid of the autogen. report generated objectives for ALL and MODIFIED modes
579 IF (pMode='MODIFIED') THEN
580 l_stmt := 'delete from BSC_TMP_OPT_UI_KPIS tmp
581 where process_id = :1
582 and indicator in
583 (select indicator from bsc_kpis_vl kpis
584 where kpis.short_name is not null
585 and BSC_DBGEN_UTILS.get_objective_type(kpis.short_name) <> ''OBJECTIVE'') ';
586 execute immediate l_stmt USING pProcessId;
587 END IF;
588 commit;
589
590 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
591 BSC_MO_HELPER_PKG.writeTmp( 'Completed updateRelatedIndicators');
592 END IF;
593
594 EXCEPTION WHEN OTHERS THEN
595 l_error := sqlerrm;
596 BSC_MO_HELPER_PKG.writeTmp('Exception in updateRelatedIndicators : '||l_error, FND_LOG.LEVEL_EXCEPTION);
597 raise;
598 END;
599
600 -- Incremental Run call from UI
601 -- Update bsc_kpis_b for all indicators
602
603 PROCEDURE RenameInputTable(pOld IN VARCHAR2, pNew IN VARCHAR2, pStatus OUT NOCOPY VARCHAR2, pMessage OUT NOCOPY VARCHAR2)
604 IS
605 PRAGMA AUTONOMOUS_TRANSACTION;
606
607 l_stmt VARCHAR2(1000);
608 l_count NUMBER;
609
610 BEGIN
611
612 IF pOld = pNew tHEN
613 return;
614 END IF;
615
616 bsc_apps.init_bsc_apps;
617
618 BSC_METADATA_OPTIMIZER_PKG.g_log := false;
619 BSC_METADATA_OPTIMIZER_PKG.gUIAPI := true;
620
621 pStatus := 'N';
622 pMessage := null;
623
624 -- Bug 3830308 : Added owner clause because of new GSCC validation added on aug 9, 2004
625 --------------
626 IF BSC_METADATA_OPTIMIZER_PKG.gBSCSchema IS NULL THEN
627 BSC_METADATA_OPTIMIZER_PKG.gBSCSchema := BSC_MO_HELPER_PKG.getBSCSchema;
628 END IF;
629
630 IF BSC_METADATA_OPTIMIZER_PKG.gAppsSchema IS NULL THEN
631 BSC_METADATA_OPTIMIZER_PKG.gAppsSchema := BSC_MO_HELPER_PKG.getAppsSchema;
632 END IF;
633 --------------
634 -- Check if table already exists
635 -- Bug 3830308 : Added owner clause because of new GSCC validation added on aug 9, 2004
636 select count(1) INTO l_count
637 from all_objects where object_name = pNew
638 and owner IN (BSC_METADATA_OPTIMIZER_PKG.gBSCSchema, BSC_METADATA_OPTIMIZER_PKG.gAppsSchema);
639
640 IF (l_count > 0) THEN
641 fnd_message.set_name('BSC', 'BSC_DUPLICATED_TABLENAME');
642 pMessage := fnd_message.get;
643 return;
644 END IF;
645
646
647 --BSC_DB_TABLES
648 UPDATE BSC_DB_TABLES set table_name = pNew where table_name = pOld;
649
650 --BSC_DB_TABLES_COLS
651 UPDATE BSC_DB_TABLES_COLS set table_name = pNew where table_name = pOld;
652
653 --BSC_DB_TABLES_RELS
654 UPDATE BSC_DB_TABLES_RELS set source_table_name = pNew where source_table_name = pOld;
655
656 -- Need not update BSC_DB_CALCULATIONS as it wont have anything for Input tables
657 execute immediate 'alter table '||BSC_METADATA_OPTIMIZER_PKG.gBSCSchema||'.'||pOld||' rename to '||pNew;
658 begin
659 execute immediate 'drop synonym '||pOld;
660 exception when others then
661 null;
662 end;
663 execute immediate 'create synonym '||pNew||' for '||BSC_METADATA_OPTIMIZER_PKG.gBSCSchema||'.'||pNew;
664 --BSC_MO_HELPER_PKG.createCopyTable(pOld, pNew, BSC_APPS.get_tablespace_name(BSC_APPS.input_table_tbs_type));
665 --BSC_MO_HELPER_PKG.CreateCopyIndexes (pOld, pNew, BSC_APPS.get_tablespace_name(BSC_APPS.input_index_tbs_type));
666 --BSC_MO_HELPER_PKG.dropTable(pOld);
667 pStatus := 'Y';
668
669 EXCEPTION WHEN OTHERS THEN
670 pMessage := sqlerrm;
671 pStatus := 'N';
672 rollback;
673 raise;
674 END;
675
676 PROCEDURE create_ui_table IS
677 l_stmt varchar2(1000) := 'CREATE GLOBAL TEMPORARY TABLE BSC_TMP_OPT_UI_LEVELS(
678 INDICATOR NUMBER,
679 DIM_SET_ID NUMBER,
680 DIM_DISPLAY_ORDER NUMBER,
681 LEVEL_DISPLAY_ORDER NUMBER,
682 LEVEL_TABLE_NAME VARCHAR2(100),
683 LEVEL_DISPLAY_NAME VARCHAR2(300),
684 TARGET_LEVEL NUMBER)
685 ON COMMIT PRESERVE ROWS';
686
687 PRAGMA AUTONOMOUS_TRANSACTION;
688 BEGIN
689 BSC_MO_HELPER_PKG.Do_DDL(l_stmt, ad_ddl.create_table, 'BSC_TMP_OPT_UI_LEVELS');
690 END;
691
692
693 PROCEDURE initializeIndicator (pIndicator IN NUMBER) IS
694 -- used by UI API
695 CURSOR cAPI IS
696 SELECT DISTINCT INDICATOR, NAME, PROTOTYPE_FLAG,
697 INDICATOR_TYPE, CONFIG_TYPE, PERIODICITY_ID,
698 SHARE_FLAG, SOURCE_INDICATOR,
699 EDW_FLAG FROM BSC_KPIS_VL WHERE
700 INDICATOR = pIndicator;
701 cIndAPI cAPI%ROWTYPE;
702
703 BEGIN
704 IF (BSC_METADATA_OPTIMIZER_PKG.gUIAPI) THEN
705 -- need to initialize gIndicators with this Ind
706 OPEN cAPI;
707 FETCH cAPI INTO cIndAPI;
708 BSC_MO_HELPER_PKG.AddIndicator( BSC_METADATA_OPTIMIZER_PKG.gIndicators, cIndAPI.Indicator, cIndAPI.name,
709 cIndAPI.indicator_type, cIndAPI.Config_Type,
710 cIndAPI.periodicity_id, 1, cIndAPI.prototype_flag, cIndAPI.share_flag,
711 cIndAPI.source_indicator, cIndAPI.edw_flag, 1);
712 END IF;
713 CLOSE cAPI;
714 END;
715
716
717 PROCEDURE updateTargets(pIndicator IN NUMBER) IS
718 l_stmt VARCHAR2(1000) := 'UPDATE BSC_TMP_OPT_UI_LEVELS UI SET TARGET_LEVEL =
719 ( SELECT TARGET_LEVEL FROM bsc_kpi_dim_levels_vl DIM
720 WHERE UI.DIM_SET_ID = DIM.DIM_SET_ID
721 AND UI.INDICATOR = DIM.INDICATOR
722 AND UI.INDICATOR = : 1
723 AND UI.LEVEL_TABLE_NAME= DIM.LEVEL_TABLE_NAME)
724 WHERE UI.indicator = :2';
725 BEGIN
726
727 EXECUTE IMMEDIATE l_stmt USING pIndicator, pIndicator;
728 END;
729
730
731 PROCEDURE insert_dimension_set (--pReturnArray IN OUT tab_clsIndicatorLevels,
732 pIndicator IN NUMBER, p_set IN NUMBER,
733 p_levels IN BSC_METADATA_OPTIMIZER_PKG.tab_tab_clsLevels ) IS
734 l_groupids DBMS_SQL.NUMBER_TABLE;
735 l_dim_index NUMBER;
736
737 l_level_index NUMBER :=0 ;
738 l_stmt VARCHAR2(300) := ' INSERT INTO BSC_TMP_OPT_UI_LEVELS (INDICATOR, DIM_SET_ID, DIM_DISPLAY_ORDER, LEVEL_DISPLAY_ORDER, LEVEL_TABLE_NAME, LEVEL_DISPLAY_NAME) '||
739 ' values (:1, :2, :3, :4, :5, :6)';
740 DimensionLevels BSC_METADATA_OPTIMIZER_PKG.Tab_clsLevels;
741
742 --l_indLevel clsIndicatorLevels;
743
744 BEGIN
745
746 l_groupids := BSC_MO_HELPER_PKG.getGroupIds(p_levels);
747 l_dim_index := l_groupids.first;
748
749 LOOP
750 EXIT WHEN l_groupids.count = 0;
751 DimensionLevels := BSC_MO_HELPER_PKG.get_Tab_clsLevels(p_levels, l_groupids(l_dim_index)) ;
752 l_level_index := DimensionLevels.first;
753 LOOP
754 EXIT WHEN DimensionLevels.count = 0;
755
756 execute immediate l_stmt USING pIndicator, p_set, l_dim_index, l_level_index,
757 DimensionLevels(l_level_index).dimTable, DimensionLevels(l_level_index).name;
758
759 EXIT WHEN l_level_index = DimensionLevels.last;
760 l_level_index := DimensionLevels.next(l_level_index);
761 END LOOP;
762 EXIT WHEN l_dim_index = l_groupids.last;
763 l_dim_index := l_groupids.next(l_dim_index);
764 END LOOP;
765
766 END;
767
768
769 PROCEDURE GetLevelsForIndicator(pIndicator IN NUMBER) IS -- RETURN tab_clsIndicatorLevels IS
770 l_stmt VARCHAR2(1000);
771 l_count NUMBER;
772 l_levels BSC_METADATA_OPTIMIZER_PKG.tab_tab_clsLevels ;
773 colConfigurations DBMS_SQL.NUMBER_TABLE;
774 l_config_index NUMBER;
775 l_insert_count NUMBER := 0;
776 cv CurTyp;
777 BEGIN
778 BSC_METADATA_OPTIMIZER_PKG.g_log := false;
779 BSC_METADATA_OPTIMIZER_PKG.gUIAPI := true;
780 IF BSC_METADATA_OPTIMIZER_PKG.gBSCSchema IS NULL THEN
781 BSC_METADATA_OPTIMIZER_PKG.gBSCSchema := BSC_MO_HELPER_PKG.getBSCSchema;
782 END IF;
783 IF (NOT BSC_MO_HELPER_PKG.tableExists('BSC_TMP_OPT_UI_LEVELS')) THEN
784 create_ui_table;
785 ELSE
786 execute immediate 'delete BSC_TMP_OPT_UI_LEVELS';
787 commit;
788 END IF;
789
790 --l_stmt := 'select count(1) from BSC_TMP_OPT_UI_LEVELS where indicator = :1 ';
791 --OPEN CV FOR l_stmt USING pIndicator;
792 --FETCH CV INTO l_insert_count;
793 --CLOSE CV;
794 --IF (l_insert_count <>0 ) THEN
795 -- return; -- already exists, dont do anything, table to be cleaned up by UI
796 -- -- everytime the metadata optimizer UI is launched, the cleanup will be done, as its a perm. temp table
797 --END IF;
798
799 initializeIndicator(pIndicator);
800 BSC_MO_HELPER_PKG.InitializeMasterTables;
801 colConfigurations := bsc_mo_indicator_pkg.GetColConfigForIndic(pIndicator);
802 l_config_index := colConfigurations.first;
803 LOOP
804 EXIT WHEN colConfigurations.count = 0;
805 l_levels.delete;
806 l_levels := BSC_MO_INDICATOR_PKG.getLevelCollection(pIndicator, colConfigurations(l_config_index));
807 insert_dimension_set(/*l_return_array, */pIndicator, colConfigurations(l_config_index), l_levels);
808 EXIT WHEN l_config_index = colConfigurations.last;
809 l_config_index := colConfigurations.next(l_config_index);
810 END LOOP;
811 select count(1) INTO l_count FROM bsc_kpi_dim_levels_vl
812 where indicator = pIndicator
813 and target_level = 0;
814 IF (l_count > 0) THEN
815 -- update table for target levels
816 updateTargets(pIndicator);
817 END IF;
818 -- added for possible corner case, bug 4158914
819 commit;
820 END;
821
822 FUNCTION GetDescriptionForColumn(pTableName IN VARCHAR2, pColumnName IN VARCHAR2) RETURN VARCHAR2 IS
823 cursor cCols IS
824 select lvl.help description from
825 bsc_sys_dim_levels_vl lvl
826 where
827 upper(pColumnName) = upper(lvl.level_pk_col)
828 UNION
829 select measure.help description from
830 bsc_db_measure_cols_vl measure
831 WHERE upper(pColumnName) = upper(measure.measure_col);
832 cRow cCols%ROWTYPE;
833 l_count NUMBER := 0;
834 CURSOR cTable (l_table IN VARCHAR2) IS
835 SELECT TABLE_NAME, TABLE_TYPE, PERIODICITY_ID, EDW_FLAG, TARGET_FLAG
836 FROM BSC_DB_TABLES
837 WHERE TABLE_TYPE <> 2
838 AND TABLE_NAME = l_table
839 ORDER BY TABLE_NAME;
840 cRow1 cTable%ROWTYPE;
841 NomPeriodicity VARCHAR2(100);
842 NomCampoPeriod VARCHAR2(100);
843 MaxPeriod NUMBER;
844 NomCampoSubPeriod VARCHAR2(100);
845 MaxSubPeriod NUMBER;
846 isBaseTable boolean := false;
847
848 CURSOR cPeriods (pPer NUMBER) IS
849 SELECT YEARLY_FLAG
850 FROM BSC_SYS_PERIODICITIES_VL
851 WHERE PERIODICITY_ID = pPer
852 ORDER BY PERIODICITY_ID;
853
854 yearly_flag number := 0;
855
856
857 l_description VARCHAR2(1024);
858 l_stmt VARCHAR2(1000) :=
859 'select table_name from bsc_kpi_data_tables where mv_name = :1';
860 cv CurTyp;
861
862 l_table_name VARCHAR2(300);
863
864 l_test NUMBER := -1;
865 BEGIN
866 l_table_name := pTableName;
867
868 If fnd_profile.value('BSC_ADVANCED_SUMMARIZATION_LEVEL') IS NULL Then
869 BSC_METADATA_OPTIMIZER_PKG.g_BSC_MV := False;
870 Else
871 BSC_METADATA_OPTIMIZER_PKG.g_BSC_MV := True;
872 End If;
873
874 If BSC_METADATA_OPTIMIZER_PKG.g_BSC_MV Then
875
876 select count(1) into l_test from bsc_db_tables tab, bsc_db_tables_rels rels
877 where rels.table_name = l_table_name
878 and rels.source_table_name = tab.table_name
879 and tab.table_type = 0 ; -- input table
880 IF (l_test > 0) THEN
881 isBaseTable := true;
882 ELSE
883 isBaseTable := false;
884 END IF;
885 End If;
886 OPEN cTable (l_table_name);
887 FETCH cTable INTO cRow1;
888 IF (cTable%NOTFOUND) THEN -- mv
889 CLOSE cTable;
890 OPEN cv for l_stmt USING l_table_name;
891 FETCH CV INTO l_table_name;
892 CLOSE CV;
893 OPEN cTable (l_table_name);
894 FETCH cTable INTO cRow1;
895 END IF;
896 CLOSE cTable;
897 OPEN cPeriods(cRow1.periodicity_id);
898 FETCH cPeriods INTO yearly_flag;
899 CLOSE cPeriods;
900
901 If cRow1.TABLE_TYPE = 0 Then
902 --input table
903 NomPeriodicity := BSC_MO_DOC_PKG.GetPeriodicityName(cRow1.PERIODICITY_ID);
904 NomPeriodicity := NomPeriodicity || ' (' || BSC_MO_DOC_PKG.GetPeriodicityCalendarName(cRow1.PERIODICITY_ID) || ')';
905 NomCampoPeriod := BSC_MO_DB_PKG.GetPeriodColumnName(cRow1.PERIODICITY_ID);
906 MaxPeriod := BSC_MO_DOC_PKG.GetMaxPeriod(cRow1.PERIODICITY_ID);
907 NomCampoSubPeriod := BSC_MO_DB_PKG.GetSubperiodColumnName(cRow1.PERIODICITY_ID);
908 MaxSubPeriod := BSC_MO_DOC_PKG.GetMaxSubPeriodUsr(cRow1.PERIODICITY_ID);
909
910 Else
911 --system table
912 NomPeriodicity := BSC_MO_DOC_PKG.GetPeriodicityName(cRow1.PERIODICITY_ID) ;
913 NomPeriodicity := NomPeriodicity || ' (' || BSC_MO_DOC_PKG.GetPeriodicityCalendarName(cRow1.PERIODICITY_ID) || ')';
914 NomCampoPeriod := 'PERIOD';
915 MaxPeriod := BSC_MO_DOC_PKG.GetMaxPeriod(cRow1.PERIODICITY_ID);
916 NomCampoSubPeriod := null;
917 MaxSubPeriod := 0;
918 End If;
919
920 OPEN cCols;
921 FETCH cCols INTO l_description;
922 CLOSE cCols;
923
924 --Bug 3900047
925 -- Added condition UPPER(pColumnName) = 'TIME_FK' to return Desc as Internal Column
926 --Bug 3919130 superseds bug 3900047
927 -- Description for the TIME_FK should be Date
928 IF (l_description IS NULL) THEN
929 If UPPER(pColumnName) = 'PERIODICITY_ID' OR UPPER(pColumnName) = 'PERIOD_TYPE_ID' Then
930 l_description := BSC_MO_HELPER_PKG.Get_LookUp_Value('BSC_UI_BACKEND', 'INTERNAL_COLUMN');
931 ElsIf UPPER(pColumnName) = 'TIME_FK' Then
932 l_description := BSC_MO_HELPER_PKG.Get_LookUp_Value('BSC_UI_COMMON', 'DATE');
933 ElsIf UPPER(pColumnName) = 'YEAR' Then
934 l_description := BSC_MO_HELPER_PKG.Get_LookUp_Value('BSC_UI_BACKEND', 'YEAR_1999_2000_ACTUAL_YEAR');
935 ElsIf UPPER(pColumnName) = 'TYPE' Then
936 l_description := BSC_MO_HELPER_PKG.Get_LookUp_Value('BSC_UI_BACKEND', 'TYPE_0_ACTUAL_1_PLAN');
937 IF (cRow1.target_flag =1) THEN -- this is a target table
938 l_description := BSC_MO_HELPER_PKG.Get_LookUp_Value('BSC_UI_BACKEND', 'TYPE1_PLAN');
939 END IF;
940 ElsIf UPPER(pColumnName) = UPPER(NomCampoPeriod) Then
941
942 If (BSC_METADATA_OPTIMIZER_PKG.g_BSC_MV And (cRow1.Table_Type = 0 Or isBaseTable)) Or (Not BSC_METADATA_OPTIMIZER_PKG.g_BSC_MV) Then
943
944 If Yearly_Flag = 1 Then
945 l_description := l_description || '0'; --bug#3980028
946 Else
947 l_description := l_description||BSC_MO_HELPER_PKG.Get_LookUp_Value('BSC_UI_COMMON', 'PERIOD') ||
948 BSC_MO_HELPER_PKG.Get_LookUp_Value('BSC_UI_COMMON', 'SYMBOL_COLON') || ' 1' || ' ' ||
949 BSC_MO_HELPER_PKG.Get_LookUp_Value('BSC_UI_IVIEWER', 'TO') || ' ' || MaxPeriod;
950 End If;
951 Else
952 --Do not mention more info about period. MV has multiple periodicities.
953 l_description := l_description||BSC_MO_HELPER_PKG.Get_LookUp_Value('BSC_UI_COMMON', 'PERIOD');
954 End If;
955 ElsIf UPPER(pColumnName) = UPPER(NomCampoSubPeriod) Then
956 l_description := l_description ||BSC_MO_HELPER_PKG.Get_LookUp_Value(
957 'BSC_UI_BACKEND', 'SUBPERIOD_1_TO') || ' ' || MaxSubPeriod;
958 End If;
959
960 END IF;
961
962 return l_description;
963 Exception when others then
964 return null;
965 END;
966
967 PROCEDURE launchOptimizer(pMode IN VARCHAR2, pRequestID OUT NOCOPY NUMBER, pStatus OUT NOCOPY VARCHAR2, pMessage OUT NOCOPY VARCHAR2) IS
968 l_mode NUMBER;
969 l_reqid NUMBER;
970 BEGIN
971 IF (pMode = 'INCREMENTAL') THEN
972 l_mode := 1;
973 ELSE
974 l_mode := 0;
975 END IF;
976
977 pRequestID := FND_REQUEST.SUBMIT_REQUEST(
978 application=>'BSC',
979 program=>'BSC_METADATA_OPTIMIZER',
980 argument1=>l_mode);
981 commit;
982 pStatus := 'Y';
983 pMessage := null;
984
985 EXCEPTION WHEN OTHERS THEN
986 pMessage := sqlerrm;
987 pStatus := 'N';
988 raise;
989 END;
990
991 FUNCTION getColDetails(pColType IN VARCHAR2, pTableName IN VARCHAR2, pTabType IN VARCHAR2) return CLOB IS
992
993 cursor cDims (l_schema IN VARCHAR2) IS
994 select lvl.name, cols.column_name
995 from
996 all_tab_columns cols,
997 bsc_sys_dim_levels_vl lvl
998 where cols.table_name = pTableName
999 and cols.owner = l_schema
1000 and cols.column_name = upper(lvl.level_pk_col)
1001 and cols.column_name not in ('YEAR', 'TYPE', 'PERIOD', 'TIME_FK', 'PERIODICITY_ID', 'PERIOD_TYPE_ID')
1002 order by lvl.name,cols.column_name; --order by clause for bug 3869698
1003
1004 --query modified for bug fix 3826281
1005 --As extra columns were also returned as measure from the previous query
1006 --Added one more condition to filter out those columns
1007 cursor cMeasures (l_schema IN VARCHAR2) IS
1008 select datasets.name, cols.column_name
1009 from
1010 all_tab_columns cols,
1011 bsc_sys_measures measure, bsc_sys_datasets_vl datasets
1012 where cols.table_name = pTableName
1013 and cols.owner = l_schema
1014 and cols.column_name not in ('YEAR', 'TYPE', 'PERIOD', 'TIME_FK', 'PERIODICITY_ID', 'PERIOD_TYPE_ID')
1015 and cols.column_name = upper(measure.measure_col(+))
1016 and measure.measure_id = datasets.measure_id1 (+)
1017 --and clause added for bug 3826281
1018 and datasets.name is not null
1019 and cols.column_name not in
1020 (select cols.column_name
1021 from
1022 all_tab_columns cols,
1023 bsc_sys_dim_levels_vl lvl
1024 where cols.table_name = pTableName
1025 and cols.owner = l_schema
1026 and cols.column_name = upper(lvl.level_pk_col))
1027 order by datasets.name,cols.column_name; --order by clause for bug 3869698
1028
1029 cursor getDimObjName IS
1030 select Name from bsc_sys_dim_levels_vl where LEVEL_TABLE_NAME = pTableName
1031 UNION
1032 select Name from bsc_sys_dim_levels_vl, bsc_Db_tables_rels r
1033 where LEVEL_TABLE_NAME = r.table_name
1034 and r.source_table_name = pTableName;
1035
1036 cursor getDimObjNameForMN(l_schema IN VARCHAR2) IS
1037 select lvl.name, cols.column_name
1038 from all_tab_columns cols, bsc_sys_dim_levels_vl lvl
1039 where cols.table_name = pTableName
1040 and cols.owner = l_schema
1041 and cols.column_name = upper(lvl.level_pk_col)
1042 union
1043 select lvl.name, cols.column_name
1044 from all_tab_columns cols,
1045 bsc_sys_dim_levels_vl lvl, bsc_db_tables_rels r
1046 where r.source_table_name = pTableName
1047 and cols.table_name = r.TABLE_NAME
1048 and cols.owner = l_schema
1049 and cols.column_name = upper(lvl.level_pk_col)
1050 order by 1,2;
1051
1052 l_column VARCHAR2(30);
1053 l_description VARCHAR2(400);
1054
1055 l_return VARCHAR2(32000) := null;
1056
1057 l_schema VARCHAR2(30) ;
1058
1059 l_ret_clob CLOB;
1060 BEGIN
1061
1062 IF (pTableName like 'BSC%MV') THEN
1063 l_schema := bsc_mo_helper_pkg.getAppsSchema;
1064 ELSE
1065 l_schema := bsc_mo_helper_pkg.getBSCSchema;
1066 END IF;
1067
1068 IF (pTabType <> 2) THEN
1069 IF (pColType like 'DIM%') THEN
1070 OPEN cDims(l_schema);
1071 LOOP
1072 FETCH cDims INTO l_description, l_column;
1073 EXIT WHEN cDims%NOTFOUND;
1074 IF (L_return IS NOT NULL) THEN
1075 l_return := l_return || ', ';
1076 END IF;
1077 IF (trim(l_description) IS NOT NULL) THEN
1078 if( (length(l_return)+length(l_description)) <31999) then
1079 l_return := l_return || l_description;
1080 else
1081 IF(l_ret_clob IS NULL) THEN
1082 WF_NOTIFICATION.NewClob(l_ret_clob, l_return);
1083 ELSE
1084 WF_NOTIFICATION.WriteToClob(l_ret_clob,l_return);
1085 END IF;
1086 l_return := l_description;
1087 end if;
1088 ELSE
1089 if( (length(l_return)+length(l_column)) <31999) then
1090 l_return := l_return || l_column;
1091 else
1092 IF(l_ret_clob IS NULL) THEN
1093 WF_NOTIFICATION.NewClob(l_ret_clob, l_return);
1094 ELSE
1095 WF_NOTIFICATION.WriteToClob(l_ret_clob,l_return);
1096 END IF;
1097 l_return := l_column;
1098 end if;
1099 END IF;
1100 END LOOP;
1101 CLOSE cDims;
1102 ELSE
1103 OPEN cMeasures(l_schema);
1104 LOOP
1105 FETCH cMeasures INTO l_description, l_column;
1106 EXIT WHEN cMeasures%NOTFOUND;
1107 IF (L_return IS NOT NULL) THEN
1108 l_return := l_return || ', ';
1109 END IF;
1110 IF (trim(l_description) IS NOT NULL) THEN
1111 if( (length(l_return)+length(l_description)) <31999) then
1112 l_return := l_return || l_description;
1113 else
1114 IF(l_ret_clob IS NULL) THEN
1115 WF_NOTIFICATION.NewClob(l_ret_clob, l_return);
1116 ELSE
1117 WF_NOTIFICATION.WriteToClob(l_ret_clob,l_return);
1118 END IF;
1119 l_return := l_description;
1120 end if;
1121 ELSE
1122 if( (length(l_return)+length(l_column)) <31999) then
1123 l_return := l_return || l_column;
1124 else
1125 IF(l_ret_clob IS NULL) THEN
1126 WF_NOTIFICATION.NewClob(l_ret_clob, l_return);
1127 ELSE
1128 WF_NOTIFICATION.WriteToClob(l_ret_clob,l_return);
1129 END IF;
1130 l_return := l_column;
1131 end if;
1132 END IF;
1133 END LOOP;
1134 CLOSE cMeasures;
1135 END IF;
1136 ELSE
1137 IF (pColType like 'DIM%') THEN
1138 OPEN getDimObjName;
1139 FETCH getDimObjName INTO l_return;
1140 CLOSE getDimObjName;
1141 IF (l_return is null) then -- it will be null for MN Dim Tables
1142 OPEN getDimObjNameForMN(l_schema);
1143 LOOP
1144 FETCH getDimObjNameForMN INTO l_description, l_column;
1145 EXIT WHEN getDimObjNameForMN%NOTFOUND;
1146 IF (L_return IS NOT NULL) THEN
1147 l_return := l_return || ', ';
1148 END IF;
1149 IF (trim(l_description) IS NOT NULL) THEN
1150 if( (length(l_return)+length(l_description)) <31999) then
1151 l_return := l_return || l_description;
1152 else
1153 IF(l_ret_clob IS NULL) THEN
1154 WF_NOTIFICATION.NewClob(l_ret_clob, l_return);
1155 ELSE
1156 WF_NOTIFICATION.WriteToClob(l_ret_clob,l_return);
1157 END IF;
1158 l_return := l_description;
1159 end if;
1160 ELSE
1161 if( (length(l_return)+length(l_column)) <31999) then
1162 l_return := l_return || l_column;
1163 else
1164 IF(l_ret_clob IS NULL) THEN
1165 WF_NOTIFICATION.NewClob(l_ret_clob, l_return);
1166 ELSE
1167 WF_NOTIFICATION.WriteToClob(l_ret_clob,l_return);
1168 END IF;
1169 l_return := l_column;
1170 end if;
1171 END IF;
1172 END LOOP;
1173 CLOSE getDimObjNameForMN;
1174 END IF;
1175 END IF;
1176 END IF;
1177 IF(l_return IS NOT NULL) THEN
1178 IF(l_ret_clob IS NULL) THEN
1179 WF_NOTIFICATION.NewClob(l_ret_clob, l_return);
1180 ELSE
1181 WF_NOTIFICATION.WriteToClob(l_ret_clob,l_return);
1182 END IF;
1183 END IF;
1184 return l_ret_clob;
1185 END;
1186
1187 -- pProcessId = -ICX_Session_Id (For cleanup)
1188 PROCEDURE getRelatedIndicators(
1189 pKPIList IN VARCHAR2,
1190 pProcessId IN NUMBER
1191 ) IS
1192 PRAGMA AUTONOMOUS_TRANSACTION;
1193
1194 l_stmt VARCHAR2(1000);
1195 l_total_kpis NUMBER;
1196 l_selected_kpis NUMBER;
1197 l_old_pos NUMBER;
1198 l_cur_pos NUMBER;
1199 l_cur_kpi VARCHAR2(32);
1200 l_error VARCHAR2(400);
1201
1202 BEGIN
1203 IF LENGTH(pKPIList) <= 0 THEN
1204 RETURN;
1205 END IF;
1206
1207 BSC_METADATA_OPTIMIZER_PKG.g_log := false;
1208 BSC_METADATA_OPTIMIZER_PKG.gUIAPI := true;
1209 IF BSC_METADATA_OPTIMIZER_PKG.gBSCSchema IS NULL THEN
1210 BSC_METADATA_OPTIMIZER_PKG.gBSCSchema := BSC_MO_HELPER_PKG.getBSCSchema;
1211 END IF;
1212
1213 IF (NOT BSC_MO_HELPER_PKG.tableExists('BSC_TMP_OPT_UI_KPIS')) THEN
1214 create_ui_kpi_table;
1215 END IF;
1216 create_tmp_opt_kpi_data;
1217
1218 --EXECUTE IMMEDIATE 'DELETE BSC_TMP_OPT_UI_KPIS WHERE PROCESS_ID = -200 OR PROCESS_ID = 0 OR PROCESS_ID IS NULL';
1219 l_stmt := 'DELETE BSC_TMP_OPT_UI_KPIS WHERE PROCESS_ID = :1 ';
1220 EXECUTE IMMEDIATE l_stmt USING pProcessId;
1221 COMMIT;
1222
1223 BSC_METADATA_OPTIMIZER_PKG.garrIndics.delete;
1224 BSC_METADATA_OPTIMIZER_PKG.gnumIndics := 0;
1225 --BSC_METADATA_OPTIMIZER_PKG.garrIndics4.delete;
1226 --BSC_METADATA_OPTIMIZER_PKG.gnumIndics4 := 0;
1227 BSC_METADATA_OPTIMIZER_PKG.garrTables.delete;
1228 BSC_METADATA_OPTIMIZER_PKG.gnumTables := 0;
1229
1230 l_cur_pos := 1;
1231 l_old_pos := 1;
1232 l_selected_kpis := 0;
1233 WHILE (l_cur_pos > 0) LOOP
1234 l_selected_kpis := l_selected_kpis + 1;
1235 l_cur_pos := INSTR(pKPIList, ',', l_old_pos);
1236 IF l_cur_pos <= 0 THEN
1237 l_cur_kpi := SUBSTR(pKPIList, l_old_pos);
1238 ELSE
1239 l_cur_kpi := SUBSTR(pKPIList, l_old_pos, l_cur_pos-l_old_pos);
1240 END IF;
1241 --DBMS_OUTPUT.PUT_LINE(TO_CHAR(l_selected_kpis)||' '||l_cur_kpi);
1242 IF LENGTH(l_cur_kpi) > 0 THEN
1243 l_stmt := ' INSERT INTO BSC_TMP_OPT_UI_KPIS (indicator, prototype_flag, process_id)
1244 SELECT INDICATOR, PROTOTYPE_FLAG, :1
1245 FROM BSC_KPIS_B
1246 WHERE INDICATOR = :2 ';
1247 EXECUTE IMMEDIATE l_stmt using pProcessId, l_cur_kpi;
1248 END IF;
1249 l_old_pos := l_cur_pos+1;
1250 END LOOP;
1251
1252 IF (l_selected_kpis = 0) THEN
1253 RETURN;
1254 END IF;
1255 SELECT COUNT(1) INTO l_total_kpis FROM BSC_KPIS_B;
1256 IF (l_selected_kpis = l_total_kpis) THEN
1257 COMMIT;
1258 RETURN;
1259 END IF;
1260 BSC_METADATA_OPTIMIZER_PKG.gnumIndics := l_selected_kpis;
1261 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1262 BSC_MO_HELPER_PKG.writeTmp('# of Indics = '||BSC_METADATA_OPTIMIZER_PKG.gnumIndics);
1263 END IF;
1264
1265 MarkIndicsAndTables(pProcessId);
1266
1267 -- take care of shared kpis
1268 l_stmt := ' INSERT INTO BSC_TMP_OPT_UI_KPIS (indicator, prototype_flag, process_id)
1269 SELECT k.INDICATOR, k.PROTOTYPE_FLAG, :1
1270 FROM BSC_KPIS_VL k,
1271 BSC_TMP_OPT_UI_KPIS t
1272 WHERE ((k.SHARE_FLAG = 2 AND k.SOURCE_INDICATOR = t.INDICATOR)
1273 OR (k.SHARE_FLAG = 2 AND k.SOURCE_INDICATOR IN
1274 (SELECT I.SOURCE_INDICATOR FROM BSC_KPIS_B I WHERE I.SHARE_FLAG = 2 AND I.INDICATOR = t.INDICATOR))
1275 OR (k.SHARE_FLAG = 1 AND k.INDICATOR IN
1276 (SELECT I.SOURCE_INDICATOR FROM BSC_KPIS_B I WHERE I.SHARE_FLAG = 2 AND I.INDICATOR = t.INDICATOR)))
1277 AND t.PROCESS_ID = :2
1278 AND NOT EXISTS (SELECT 1 FROM BSC_TMP_OPT_UI_KPIS c WHERE c.indicator = k.indicator AND c.PROCESS_ID = :3)';
1279 EXECUTE IMMEDIATE l_stmt using pProcessId, pProcessId, pProcessId;
1280
1281 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1282 BSC_MO_HELPER_PKG.writeTmp( 'Completed getRelatedIndicators');
1283 END IF;
1284 COMMIT;
1285
1286 EXCEPTION WHEN OTHERS THEN
1287 ROLLBACK;
1288 l_error := sqlerrm;
1289 BSC_MO_HELPER_PKG.writeTmp('Exception in getRelatedIndicators : '||l_error, FND_LOG.LEVEL_EXCEPTION);
1290 raise;
1291 END;
1292
1293 PROCEDURE deleteBSCSession(pSession IN NUMBER) IS
1294 BEGIN
1295 delete bsc_current_sessions where session_id = pSession;
1296 END;
1297
1298 -- Remove entries in BSC_TMP_OPT_UI_KPIS
1299 PROCEDURE cleanUITempTable IS
1300 l_del_stmt varchar2(2000);
1301 BEGIN
1302 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1303 BSC_MO_HELPER_PKG.writeTmp( 'Started cleanUITempTable');
1304 END IF;
1305
1306 -- Removed entries for concurrent requests that were finished
1307 DELETE BSC_TMP_OPT_UI_KPIS
1308 WHERE PROCESS_ID > 0
1309 AND TO_CHAR(PROCESS_ID) NOT IN (
1310 SELECT /*+ INDEX(R FND_CONCURRENT_REQUESTS_N6)*/ R.ARGUMENT2
1311 FROM FND_CONCURRENT_REQUESTS R, FND_CONCURRENT_PROGRAMS_VL P, FND_APPLICATION A
1312 WHERE A.APPLICATION_SHORT_NAME = BSC_MO_HELPER_PKG.getBSCSchema
1313 AND A.APPLICATION_ID = P.APPLICATION_ID
1314 AND P.APPLICATION_ID = R.PROGRAM_APPLICATION_ID
1315 AND P.CONCURRENT_PROGRAM_ID = R.CONCURRENT_PROGRAM_ID
1316 AND P.CONCURRENT_PROGRAM_NAME = 'BSC_METADATA_OPTIMIZER'
1317 AND R.PHASE_CODE IN ('P','R')
1318 AND R.ARGUMENT2 IS NOT NULL);
1319 COMMIT;
1320
1321 -- Removed entries for invalid ui sessions
1322 DELETE BSC_TMP_OPT_UI_KPIS
1323 WHERE PROCESS_ID < 0
1324 AND PROCESS_ID NOT IN (
1325 SELECT SESSION_ID*-1
1326 FROM ICX_SESSIONS
1327 WHERE (FND_SESSION_MANAGEMENT.CHECK_SESSION(SESSION_ID,NULL,NULL,'N') = 'VALID'));
1328 COMMIT;
1329
1330 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1331 BSC_MO_HELPER_PKG.writeTmp( 'Completed cleanUITempTable');
1332 END IF;
1333
1334 EXCEPTION WHEN OTHERS THEN
1335 BSC_MO_HELPER_PKG.writeTmp('Exception in cleanUITempTable : '||sqlerrm, FND_LOG.LEVEL_EXCEPTION);
1336 raise;
1337 END;
1338
1339 /*------------------------------------------------------------------------------------------
1340 Procedure checkSystemLock
1341 This procedure loops through the BSC_TMP_OPT_UI_KPIS table and checks
1342 for locks for all the Objectives corresponding to the process_id
1343 <parameters>
1344 p_all_objectives: 1 = all, 0 = modified or selected
1345 p_program_id: The program ID
1346 p_user_id: Application User ID
1347 p_process_id: The process ID
1348 -------------------------------------------------------------------------------------------*/
1349 Procedure checkSystemLock (
1350 p_all_objectives IN number
1351 ,p_program_id IN number
1352 ,p_user_id IN number
1353 ,p_process_id IN number
1354 ,x_return_status OUT NOCOPY varchar2
1355 ,x_msg_count OUT NOCOPY number
1356 ,x_msg_data OUT NOCOPY varchar2
1357 ) IS
1358
1359 l_api_name CONSTANT VARCHAR2(30) := 'checkSystemLock';
1360
1361 CURSOR c_get_all_objectives(
1362 c_process_id NUMBER
1363 ) IS
1364 SELECT DISTINCT INDICATOR
1365 FROM BSC_TMP_OPT_UI_KPIS
1366 WHERE PROCESS_ID = c_process_id;
1367
1368 CURSOR c_get_objectives(
1369 c_process_id NUMBER
1370 ) IS
1371 SELECT DISTINCT INDICATOR
1372 FROM BSC_TMP_OPT_UI_KPIS
1373 WHERE PROCESS_ID = c_process_id
1374 AND PROTOTYPE_FLAG IN (1,2,3,4,-3);
1375
1376 BEGIN
1377 --DBMS_OUTPUT.PUT_LINE('checkSystemLock');
1378 SAVEPOINT BSCMOUIPKGCheckSystemLock;
1379 FND_MSG_PUB.Initialize;
1380 x_return_status := FND_API.G_RET_STS_SUCCESS;
1381
1382 IF (p_all_objectives = 1) THEN
1383 FOR cobj IN c_get_all_objectives(p_process_id) LOOP
1384 BSC_LOCKS_PUB.CHECK_SYSTEM_LOCK
1385 (
1386 p_object_key => cobj.INDICATOR
1387 ,p_object_type => 'OBJECTIVE'
1388 ,p_program_id => p_program_id
1389 ,p_user_id => p_user_id
1390 ,p_cascade_lock_level => '0'
1391 ,x_return_status => x_return_status
1392 ,x_msg_count => x_msg_count
1393 ,x_msg_data => x_msg_data
1394 );
1395 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1396 ROLLBACK TO BSCMOUIPKGCheckSystemLock;
1397 EXIT;
1398 END IF;
1399 END LOOP;
1400 ELSE
1401 FOR cobj IN c_get_objectives(p_process_id) LOOP
1402 BSC_LOCKS_PUB.CHECK_SYSTEM_LOCK
1403 (
1404 p_object_key => cobj.INDICATOR
1405 ,p_object_type => 'OBJECTIVE'
1406 ,p_program_id => p_program_id
1407 ,p_user_id => p_user_id
1408 ,p_cascade_lock_level => '0'
1409 ,x_return_status => x_return_status
1410 ,x_msg_count => x_msg_count
1411 ,x_msg_data => x_msg_data
1412 );
1413 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1414 ROLLBACK TO BSCMOUIPKGCheckSystemLock;
1415 EXIT;
1416 END IF;
1417 END LOOP;
1418 END IF;
1419
1420 EXCEPTION
1421 WHEN FND_API.G_EXC_ERROR THEN
1422 ROLLBACK TO BSCMOUIPKGCheckSystemLock;
1423 x_return_status := FND_API.G_RET_STS_ERROR;
1424 FND_MSG_PUB.Count_And_Get(
1425 p_encoded => 'F'
1426 ,p_count => x_msg_count
1427 ,p_data => x_msg_data
1428 );
1429 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1430 ROLLBACK TO BSCMOUIPKGCheckSystemLock;
1431 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1432 FND_MSG_PUB.Count_And_Get(
1433 p_encoded => 'F'
1434 ,p_count => x_msg_count
1435 ,p_data => x_msg_data
1436 );
1437 WHEN OTHERS THEN
1438 ROLLBACK TO BSCMOUIPKGCheckSystemLock;
1439 FND_MSG_PUB.Add_Exc_Msg(
1440 G_PKG_NAME,
1441 l_api_name,
1442 SQLERRM
1443 );
1444 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1445 FND_MSG_PUB.Count_And_Get(
1446 p_encoded => 'F'
1447 ,p_count => x_msg_count
1448 ,p_data => x_msg_data
1449 );
1450 END checkSystemLock;
1451
1452 /*------------------------------------------------------------------------------------------
1453 Procedure getSystemLock
1454 This procedure loops through the BSC_TMP_OPT_UI_KPIS table
1455 and locks all the Objectives corresponding to the process_id
1456 <parameters>
1457 p_all_objectives: 1 = all, 0 = modified or selected
1458 p_program_id: The program ID
1459 p_query_time: The query time at the start of the process flow
1460 p_user_id: Application User ID
1461 p_process_id: The process ID
1462 -------------------------------------------------------------------------------------------*/
1463 Procedure getSystemLock (
1464 p_all_objectives IN number
1465 ,p_query_time IN date
1466 ,p_program_id IN number
1467 ,p_user_id IN number
1468 ,p_process_id IN number
1469 ,x_return_status OUT NOCOPY varchar2
1470 ,x_msg_count OUT NOCOPY number
1471 ,x_msg_data OUT NOCOPY varchar2
1472 ) IS
1473
1474 l_api_name CONSTANT VARCHAR2(30) := 'getSystemLock';
1475
1476 CURSOR c_get_all_objectives(
1477 c_process_id NUMBER
1478 ) IS
1479 SELECT INDICATOR
1480 FROM BSC_TMP_OPT_UI_KPIS
1481 WHERE PROCESS_ID = c_process_id;
1482
1483 CURSOR c_get_objectives(
1484 c_process_id NUMBER
1485 ) IS
1486 SELECT DISTINCT INDICATOR
1487 FROM BSC_TMP_OPT_UI_KPIS
1488 WHERE PROCESS_ID = c_process_id
1489 AND PROTOTYPE_FLAG IN (1,2,3,4,-3);
1490
1491 BEGIN
1492 --DBMS_OUTPUT.PUT_LINE('getSystemLock');
1493 SAVEPOINT BSCMOUIPKGGetSystemLock;
1494 FND_MSG_PUB.Initialize;
1495 x_return_status := FND_API.G_RET_STS_SUCCESS;
1496
1497 IF (p_all_objectives = 1) THEN
1498 FOR cobj IN c_get_all_objectives(p_process_id) LOOP
1499 BSC_LOCKS_PUB.GET_SYSTEM_LOCK
1500 (
1501 p_object_key => cobj.INDICATOR
1502 ,p_object_type => 'OBJECTIVE'
1503 ,p_lock_type => 'W'
1504 ,p_query_time => p_query_time
1505 ,p_program_id => p_program_id
1506 ,p_user_id => p_user_id
1507 ,p_cascade_lock_level => '0'
1508 ,x_return_status => x_return_status
1509 ,x_msg_count => x_msg_count
1510 ,x_msg_data => x_msg_data
1511 );
1512 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1513 ROLLBACK TO BSCMOUIPKGGetSystemLock;
1514 EXIT;
1515 END IF;
1516 END LOOP;
1517 ELSE
1518 FOR cobj IN c_get_objectives(p_process_id) LOOP
1519 BSC_LOCKS_PUB.GET_SYSTEM_LOCK
1520 (
1521 p_object_key => cobj.INDICATOR
1522 ,p_object_type => 'OBJECTIVE'
1523 ,p_lock_type => 'W'
1524 ,p_query_time => p_query_time
1525 ,p_program_id => p_program_id
1526 ,p_user_id => p_user_id
1527 ,p_cascade_lock_level => '0'
1528 ,x_return_status => x_return_status
1529 ,x_msg_count => x_msg_count
1530 ,x_msg_data => x_msg_data
1531 );
1532 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1533 ROLLBACK TO BSCMOUIPKGGetSystemLock;
1534 EXIT;
1535 END IF;
1536 END LOOP;
1537 END IF;
1538
1539 EXCEPTION
1540 WHEN FND_API.G_EXC_ERROR THEN
1541 ROLLBACK TO BSCMOUIPKGGetSystemLock;
1542 x_return_status := FND_API.G_RET_STS_ERROR;
1543 FND_MSG_PUB.Count_And_Get(
1544 p_encoded => 'F'
1545 ,p_count => x_msg_count
1546 ,p_data => x_msg_data
1547 );
1548 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1549 ROLLBACK TO BSCMOUIPKGGetSystemLock;
1550 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1551 FND_MSG_PUB.Count_And_Get(
1552 p_encoded => 'F'
1553 ,p_count => x_msg_count
1554 ,p_data => x_msg_data
1555 );
1556 WHEN OTHERS THEN
1557 ROLLBACK TO BSCMOUIPKGGetSystemLock;
1558 FND_MSG_PUB.Add_Exc_Msg(
1559 G_PKG_NAME,
1560 l_api_name,
1561 SQLERRM
1562 );
1563 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1564 FND_MSG_PUB.Count_And_Get(
1565 p_encoded => 'F'
1566 ,p_count => x_msg_count
1567 ,p_data => x_msg_data
1568 );
1569 END getSystemLock;
1570
1571 END BSC_MO_UI_PKG;