1 Package BSC_METADATA_OPTIMIZER_PKG AS
2 /* $Header: BSCMOPTS.pls 120.9.12000000.3 2007/08/03 11:23:16 ankgoel ship $ */
3 newline varchar2(10):='
4 ';
5
6 optimizer_exception EXCEPTION;
7
8
9 g_bsc_apps_initialized boolean:=false;
10 g_log boolean := false;
11 g_out boolean := false;
12
13 g_debug boolean := false;
14
15 g_log_level number := FND_LOG.LEVEL_ERROR;
16
17 g_session_id NUMBER := userenv('SESSIONID');
18
19 g_num_partitions number:=0;
20 g_partition_clause varchar2(1000);
21
22 -- Table name prefixes
23 g_kpi_tmp_table_pfx constant VARCHAR2(30) := 'BSC_TMP_KPI_DATA_';
24 g_dbmeasure_tmp_table_pfx constant VARCHAR2(30) := 'BSC_TMP_DBMSR_';
25 g_period_circ_check_pfx constant VARCHAR2(30):= 'BSC_TMP_PER_CHK_';
26 g_filtered_indics_pfx constant VARCHAR2(30):= 'BSC_TMP_FILTERS_';
27 -- Last tables for BSC_MO_HELPER_PKG.CreateLastTables
28 g_db_tables_last_pfx constant VARCHAR2(30):='BSC_TBL_LAST_';
29 g_db_tables_rels_last_pfx constant VARCHAR2(30):='BSC_TBLRELS_LAST_';
30 g_kpi_data_last_pfx constant VARCHAR2(30):='BSC_KPI_DATA_LAST_';
31 g_db_tables_cols_last_pfx constant VARCHAR2(30):='BSC_DB_TBLCOLS_LAST_';
32
33 --Table Names appended with Prefixes
34 g_kpi_tmp_table VARCHAR2(30) := g_kpi_tmp_table_pfx||g_session_id;
35
36 --g_dbmeasure_tmp_table VARCHAR2(30) := g_dbmeasure_tmp_table_pfx||g_session_id;
37 g_dbmeasure_tmp_table varchar2(4000) :=
38 '--db measure temp table replacement sql
39 ( select distinct indicator, dim_set_id, sysm.measure_id, sysm.measure_col
40 from BSC_DB_DATASET_DIM_SETS_V ds,
41 (SELECT DATASET_ID, MEASURE_ID1 MEASURE_ID, SOURCE
42 FROM BSC_SYS_DATASETS_B
43 UNION ALL
44 SELECT DATASET_ID, MEASURE_ID2 MEASURE_ID, SOURCE
45 FROM BSC_SYS_DATASETS_B
46 WHERE MEASURE_ID2 IS NOT NULL ) sds,
47 bsc_sys_measures sysm
48 where ds.dataset_id=sds.dataset_id
49 and sds.measure_id = sysm.measure_id)';
50
51 g_period_circ_check VARCHAR2(30):= g_period_circ_check_pfx||g_session_id;
52 g_filtered_indics VARCHAR2(30):= g_filtered_indics_pfx||g_session_id;
53
54 -- Last tables for BSC_MO_HELPER_PKG.CreateLastTables
55 g_db_tables_last VARCHAR2(30) := g_db_tables_last_pfx||g_session_id;
56 g_db_table_rels_last VARCHAR2(30) := g_db_tables_rels_last_pfx||g_session_id;
57 g_kpi_data_last VARCHAR2(30):= g_kpi_data_last_pfx||g_session_id;
58 g_db_tables_cols_last VARCHAR2(30):= g_db_tables_cols_last_pfx||g_session_id;
59
60 g_retcode NUMBER :=0;
61 g_errbuf VARCHAR2(4000) := null;
62 g_processID NUMBER := 0;
63 g_dropAppsTables DBMS_SQL.VARCHAR2_TABLE;
64 VERSION VARCHAR2(10) := '5.3';
65
66 gIndent varchar2(512):='';
67 gSpacing varchar2(10) := ' ';
68 gAppsSchema varchar2(100);
69 gBSCSchema varchar2(100);
70 gApplsysSchema VARCHAR2(100);
71 gUserId number := 0;
72 gGAA_RUN_MODE NUMBER := 0;
73
74
75 -- AW
76 IMPL_TYPE constant VARCHAR2(20):= 'IMPLEMENTATION_TYPE';
77
78 /* LANGUAGES */
79 gInstalled_Languages DBMS_SQL.VARCHAR2_TABLE;
80 gNumInstalled_Languages NUMBER := 0;
81 gNLSLang VARCHAR2(30);
82 gLangCode VARCHAR2(30);
83
84 /* RESERVED FUNCTIONS */
85 gReservedFunctions DBMS_SQL.VARCHAR2_TABLE;
86 gNumReservedFunctions NUMBER := 0;
87
88 /* RESERVED OPERATORS */
89 gReservedOperators DBMS_SQL.VARCHAR2_TABLE;
90 gNumReservedOperators NUMBER := 0;
91
92
93 /* RESERVED WORDS */
94 gArrReservedWords DBMS_SQL.VARCHAR2_TABLE;
95 gNumArrReservedWords NUMBER := 0;
96
97 gSYSTEM_STAGE varchar2(270);
98 gStorageClause varchar2(2000);
99 --gTablespaceClauseTbl varchar2(1000);
100 --gTablespaceClauseIdx varchar2(1000);
101
102 gInputTableTbsName varchar2(1000);
103 gInputIndexTbsName varchar2(1000);
104 gBaseTableTbsName varchar2(1000);
105 gBaseIndexTbsName varchar2(1000);
106 gSummaryTableTbsName varchar2(1000);
107 gSummaryIndexTbsName varchar2(1000);
108 gOtherTableTbsName varchar2(1000);
109 gOtherIndexTbsName varchar2(1000);
110
111 gThereIsStructureChange boolean;
112
113 TYPE CurTyp IS REF CURSOR;
114
115 TYPE clsParent IS RECORD (
116 name VARCHAR2(100),
117 relationColumn VARCHAR2(100));
118 TYPE tab_clsParent IS TABLE OF clsParent INDEX BY BINARY_INTEGER;
119
120 TYPE clsMasterTable IS RECORD (
121 name VARCHAR2(100),
122 keyName VARCHAR2(100),
123 userTable BOOLEAN,
124 EDW_FLAG NUMBER,
125 inputTable VARCHAR2(100),
126 parent_name VARCHAR2(32000), -- comma separated
127 parent_rel_col VARCHAR2(32000), -- comma separated
128 auxillaryFields VARCHAR2(32000) -- comma separated --tab_clsAuxillaryField
129 ,UserKeySize NUMBER
130 ,DispKeySize NUMBER
131 --BIS DIMENSIONS: Use of Bis dimensions with BSC measures
132 --We need to load BIS dimensions in the collection. This new property is to
133 --have the source of the dimension: BSC or PMF
134 ,Source VARCHAR2(10)
135 );
136
137
138 TYPE tab_clsMasterTable IS TABLE OF clsMasterTable INDEX BY BINARY_INTEGER;
139
140 TYPE clsRelationMN IS RECORD (
141 TableA VARCHAR2(100), --Name of the dimension table A of the relation
142 keyNameA VARCHAR2(100), --Name of the key field for dimension table A
143 TableB VARCHAR2(100), --Name of the dimension table B of the relation
144 keyNameB VARCHAR2(100), --Name of the key field for dimension table B
145 TableRel VARCHAR2(100), --Name of the relatin table
146 InputTable VARCHAR2(100) --Name of the input table
147 );
148
149 TYPE tab_clsrelationMN is table of clsRelationMN INDEX BY BINARY_INTEGER;
150
151 gMasterTable tab_clsMasterTable;
152 gRelationsMN tab_clsrelationMN;
153
154 TYPE clsIndicator is RECORD (
155 Code NUMBER,
156 Name BSC_KPIS_VL.NAME%TYPE,
157 IndicatorType NUMBER,
158 ConfigType NUMBER,
159 periodicity NUMBER,
160 OptimizationMode NUMBER,
161 Action_Flag NUMBER,
162 Share_Flag NUMBER,
163 Source_Indicator NUMBER,
164 EDW_Flag NUMBER,
165 Impl_Type NUMBER /* 1= Summary Tables or MVs, 2=AWs*/);
166
167 TYPE tab_clsIndicator is TABLE OF clsIndicator INDEX BY BINARY_INTEGER;
168 gIndicators tab_clsIndicator;
169
170 TYPE clsNumber IS RECORD (
171 value NUMBER);
172
173 TYPE tab_clsNumber is TABLE OF clsNumber INDEX BY BINARY_INTEGER;
174
175 TYPE clsPeriodicity IS RECORD (
176 Code NUMBER,
177 EDW_Flag NUMBER,
178 Yearly_Flag NUMBER,
179 CalendarID NUMBER,
180 PeriodicityType NUMBER,
181 PeriodicityOrigin VARCHAR2(32000));-- comma separated
182
183 TYPE tab_clsPeriodicity is TABLE OF clsPeriodicity INDEX BY BINARY_INTEGER;
184 gPeriodicities tab_clsPeriodicity;
185
186 TYPE clsIndicPeriodicity IS RECORD (
187 Code NUMBER,
188 TargetLevel NUMBER);
189
190 TYPE Tab_clsIndicPeriodicity IS TABLE OF clsIndicPeriodicity INDEX BY BINARY_INTEGER;
191
192
193 TYPE clsCalendar IS RECORD (
194 Code NUMBER,
195 EDW_Flag NUMBER,
196 Name VARCHAR2(300),
197 CurrFiscalYear NUMBER,
198 RangeYrMod NUMBER,
199 NumOfYears NUMBER,
200 PreviousYears NUMBER
201 --BIS DIMENSIONS: BIS TIME dimensions are imported in BSC Calendars.
202 --This new property is to'have the source of the calendar: BSC or PMF
203 ,Source VARCHAR2(10)
204 );
205
206 TYPE tab_clsCalendar is TABLE OF clsCalendar INDEX BY BINARY_INTEGER;
207
208 gCalendars tab_clsCalendar;
209
210
211 PROCEDURE initMVFlags;
212 PROCEDURE initReservedFunctions;
213 PROCEDURE run_metadata_optimizer(
214 Errbuf OUT NOCOPY Varchar2,
215 Retcode OUT NOCOPY Varchar2,
216 p_runMode IN NUMBER, -- 0 ALL, 1 INCREMENTAL, 2 SELECTED , (9 obsolete)
217 p_processID IN NUMBER);
218
219
220 PROCEDURE Documentation(
221 Errbuf out NOCOPY Varchar2,
222 Retcode out NOCOPY Varchar2);
223
224 garrOldIndicators dbms_sql.number_table;
225 gnumOldIndicators NUMBER := 0;
226
227 Type clsOldBTables IS RECORD(
228 Name varchar2(100),
229 periodicity NUMBER,
230 InputTable varchar2(100),
231 Fields VARCHAR2(32000),-- comma separated
232 numFields NUMBER,
233 Indicators VARCHAR2(32000),-- comma separated
234 NumIndicators NUMBER);
235
236 TYPE tab_clsOldBTables is TABLE OF clsOldBTables INDEX BY BINARY_INTEGER;
237
238 gBackedUpBTables DBMS_SQL.VARCHAR2_TABLE;
239 garrOldBTables tab_clsOldBTables; --array that contains the bases tables of the system before process
240 gnumOldBTables NUMBER := 0;
241
242 garrIndics dbms_sql.number_table; --array with the indicators the process will apply on.
243 gnumIndics NUMBER := 0;
244
245 garrIndics4 dbms_sql.number_table; --array with the indicators with non-structural changes
246 gnumIndics4 number := 0;
247
248 garrTables dbms_sql.varchar2_table; --array with the tables of the database that will be re-created.
249 gnumTables number := 0;
250
251 EDW_MATERIALIZED_VIEW_EXT constant VARCHAR2(10):= '_MV_V';
252 EDW_UNION_VIEW_EXT constant VARCHAR2(10) := '_V';
253
254 ColorG Constant NUMBER := 8421504; --Dark Gray
255 DTNumber Constant VARCHAR2(10) := 'NUMBER';
256 DTVarchar2 Constant VARCHAR2(10) := 'VARCHAR2';
257 DTDate Constant VARCHAR2(10) := 'DATE';
258 ORA_DATA_PRECISION_BYTE Constant NUMBER := 3;
259 ORA_DATA_PRECISION_INTEGER Constant NUMBER := 5;
260 ORA_DATA_PRECISION_LONG Constant NUMBER := 11;
261 ORA_DATA_PRECISION_DOUBLE Constant NUMBER := 38;
262 ORA_DATA_DEC_DOUBLE Constant NUMBER := 4;
263
264 C_PFORMULASOURCE CONSTANT VARCHAR2(20) := 'pFormulaSource';
265 C_PAVGL CONSTANT VARCHAR2(10) := 'pAvgL';
266 C_PAVGLTOTAL VARCHAR2(20) := 'pAvgLTotal';
267 C_PAVGLCOUNTER VARCHAR2(20) := 'pAvgLCounter';
268
269
270 TYPE clsMeasureLOV IS RECORD (
271 fieldName VARCHAR2(4000), --field name
272 description VARCHAR2(255), --Description
273 groupCode NUMBER, --Grouping code
274 prjMethod NUMBER, --projection method of the field
275 --0: No Forecast
276 --1: Moving Averge
277 --2: Plan-Based (not used any more)
278 --3: Plan-Based
279 --4: Custom
280 measureType NUMBER, --balance or statistical
281 --1: Statistic
282 --2: Balance
283 -- BSC Autogen
284 source VARCHAR2(30) -- BSC or PMF
285 );
286
287 TYPE tab_clsMeasureLOV is TABLE OF clsMeasureLOV INDEX BY BINARY_INTEGER;
288 gLOV tab_clsMeasureLOV;
289
290 TYPE clsLevels IS RECORD (
291 keyName VARCHAR2(100), --Name of the key field
292 dimTable VARCHAR2(100), --Name of the dimension table associated with this Level
293 Num NUMBER, --Index of the Level inside the indicator
294 Name varchar2(100), --Name of the Level
295 TargetLevel NUMBER, --1- Apply target, 0- Do not apply target
296 Parents1N VARCHAR2(32000),-- comma separated List of parents 1n (objects class clsCadena)
297 ParentsMN VARCHAR2(32000)-- comma separated List of parents mn (objects class clsCadena)
298 );
299
300 TYPE tab_clsLevels IS TABLE OF clsLevels INDEX BY BINARY_INTEGER;
301
302 TYPE tabrec_clsLevels IS RECORD(
303 group_id NUMBER,
304 keyName VARCHAR2(100), --Name of the key field
305 dimTable VARCHAR2(100), --Name of the dimension table associated with this Level
306 Num NUMBER, --Index of the Level inside the indicator
307 Name varchar2(100), --Name of the Level
308 TargetLevel NUMBER, --1- Apply target, 0- Do not apply target
309 Parents1N VARCHAR2(32000),-- comma separated List of parents 1n (objects class clsCadena)
310 ParentsMN VARCHAR2(32000)-- comma separated List of parents mn (objects class clsCadena)
311 );
312
313 TYPE tab_tab_clsLevels IS TABLE OF tabrec_clsLevels INDEX BY BINARY_INTEGER;
314
315 TYPE clsLevelCombinations IS RECORD(
316 Levels VARCHAR2(32000),-- comma separated
317 LevelConfig VARCHAR2(1000));
318
319 TYPE tab_clsLevelCombinations IS TABLE OF clsLevelCombinations INDEX BY BINARY_INTEGER;
320
321 TYPE tabrec_clsLevelCombinations IS RECORD(
322 group_id NUMBER,
323 Levels VARCHAR2(32000),-- comma separated
324 LevelConfig VARCHAR2(1000)
325 );
326
327 TYPE tab_tab_clsLevelCombinations IS TABLE OF tabrec_clsLevelCombinations INDEX BY BINARY_INTEGER;
328
329 TYPE clsKeyField IS RECORD(
330 tableName VARCHAR2(100),
331 keyName VARCHAR2(100),
332 origin VARCHAR2(100),
333 needsCode0 boolean,
334 calculateCode0 boolean,
335 FilterViewName varchar2(100),
336 --BSC-MV Note: need this property to store the index of the dimension whitin the kpi
337 dimIndex NUMBER);
338
339 TYPE tab_clsKeyField IS TABLE OF clsKeyField INDEX BY BINARY_INTEGER;
340
341 TYPE clsDataField IS RECORD (
342 tableName VARCHAR2(100),
343 fieldName VARCHAR2(4000),
344 aggFunction VARCHAR2(100),
345 Origin VARCHAR2(4000),
346 AvgLFlag VARCHAR2(100),
347 AvgLTotalColumn VARCHAR2(100),
348 AvgLCounterColumn VARCHAR2(100),
349 InternalColumnType NUMBER,
350 InternalColumnSource VARCHAR2(4000),
351 -- BSC Autogen
352 Source VARCHAR2(30),
353 -- Column added to production table
354 changeType VARCHAR2(30),
355 measureGroup NUMBER
356 );
357
358 TYPE tab_clsDataField IS TABLE OF clsDataField INDEX BY BINARY_INTEGER;
359
360
361 TYPE clsBasicTable IS RECORD (
362 Name VARCHAR2(100),
363 keys tab_clsKeyField,
364 Data tab_clsDataField,
365 LevelConfig VARCHAR2(100),
366 originTable VARCHAR2(1000));
367
368 TYPE tab_clsBasicTable IS TABLE OF clsBasicTable INDEX BY BINARY_INTEGER;
369
370 TYPE tab_string IS RECORD
371 (value VARCHAR2(32000) );-- comma separated
372
373 TYPE tab_tab_String IS TABLE OF tab_string INDEX BY BINARY_INTEGER;
374
375 TYPE number_table IS RECORD(
376 value DBMS_SQL.number_table);
377
378 --TYPE TwoDNumberTable IS TABLE OF number_table INDEX BY BINARY_INTEGER;
379
380 TYPE clsOriginTable IS RECORD(
381 Name VARCHAR2(1000));
382 TYPE tab_clsOriginTable IS TABLE OF clsOriginTable INDEX BY BINARY_INTEGER;
383
384
385 TYPE clsTable IS RECORD(
386 Name VARCHAR2(1000), --Name of the table
387 Type NUMBER, --Type 0: Input table 1: System table (base, temporal or summary)
388 Periodicity NUMBER, --periodicity
389 originTable VARCHAR2(32000), -- comma sep list of tables where it is originated from (Hard Relation).
390 originTable1 VARCHAR2(32000), -- comma sep list of tables where it is originated from (Soft Relation).
391 Indicator NUMBER, --Indicator code using directly this table
392 Configuration NUMBER, --Configuration of the indicator using directly this table
393 EDW_Flag NUMBER, --If the table belong to a EDW Kpi. 1=YES, 0=NO
394 IsTargetTable Boolean, -- true -The table is only for targets
395 HasTargets Boolean, -- true -The table has targets
396 -- This property is used when the indicator has target at different levels
397 -- This property is used only within indicator tables
398 UsedForTargets Boolean, -- true -The table contains targets and is really used for the indicator.
399 -- This property is used when the indicator has target at different levels
400 -- This property is used only within indicator tables
401 Keys tab_clsKeyField,
402 Data tab_clsDataField,
403 --BSC-MV Note: This property is used for Documentation only
404 dbObjectType VARCHAR2(100),
405 MVName VARCHAR2(100),
409 upgradeFlag NUMBER,
406 --BSC-MV Note: This properties are used only when there is sum level change
407 -- (from NULL to NOTNULL) and for tables used for indicators
408 -- in production
410 currentPeriod NUMBER,
411 --BSC-MV Note: This property is to store the name of the projection table
412 projectionTable VARCHAR2(100),
413 -- existing production table used for optimization... should not be dropped or recreated
414 isProductionTable boolean,
415 -- existing production table altered for adding deleting measures, enh 4350262
416 isProductionTableAltered boolean,
417 Impl_Type NUMBER /* 1= Summary Tables or MVs, 2=AWs*/,
418 -- Column added to production table
419 changeType VARCHAR2(30),
420 -- measure group for this table
421 MeasureGroup NUMBER);
422
423 TYPE tab_clsTable IS TABLE OF clsTable INDEX BY BINARY_INTEGER;
424
425 gTables tab_clsTable;
426
427 gSequence NUMBER :=0;
428
429 TYPE clsDisAggField IS RECORD(
430 tablename VARCHAR2(100),
431 fieldName VARCHAR2(4000),
432 fieldType VARCHAR2(100),
433 Code NUMBER, --dissagregation code
434 Periodicity NUMBER, --periodicity
435 Origin NUMBER, --code of the origin dissagregation
436 Registered boolean, --True if the dissagregation was already registered
437 keys tab_clsKeyField,--List of keys
438 keyStart number, -- start point to global pl_sql table g_disaggKeys
439 keyNum number -- # of keys
440 --From production table
441 ,isProduction boolean
442 );
443
444 TYPE tab_clsDisAggField IS TABLE OF clsDisAggField INDEX BY BINARY_INTEGER;
445
446
447 TYPE clsUniqueField IS RECORD (
448 fieldName VARCHAR2(1000),
449 aggFunction VARCHAR2(400),
450 --List of different dissagregations of the field (objects class clsDesagCampo)
451 key_combinations tab_clsDisaggField,
452 EDW_Flag NUMBER, --If the field belong to a EDW table. 1=YES, 0=NO
453 Impl_type NUMBER,
454 -- BSC Autogen
455 source VARCHAR2(30),
456 measureGroup NUMBER
457 );
458 TYPE tab_clsUniqueField IS TABLE OF clsUniqueField INDEX BY BINARY_INTEGER;
459
460
461 g_unique_measures tab_clsUniqueField ; --Unique list of fields.
462 g_unique_measures_tgt tab_clsUniqueField ;--Unique list of fields fro target tables only.
463 --gColUnicaCamposPreCalc
464 g_unique_measures_precalc tab_clsUniqueField ;--Unique list of fields for pre-calculated kpis.
465
466 --gTablasTempyBasicas
467 g_bt_tables tab_clsTable ; --Collection of temporal and base tables (Collection of clsTablas)
468 --gTablasTempyBasicasTargets
469 g_bt_tables_tgt tab_clsTable ; --Collection of temporal and base tables for targets (Collection of clsTablas)
470 --gTablasTempyBasicasPreCalc
471 g_bt_tables_precalc tab_clsTable ; --Collection of temporal and base tables for pre-calculated indicators (Collection of clsTablas)
472
473 gMaxT NUMBER;
474 gMaxB NUMBER;
475 gMaxI NUMBER;
476
477 TYPE clsDBColumn IS RECORD(
478 columnName VARCHAR2(100),
479 columnTYPE VARCHAR2(100),
480 columnLength NUMBER,
481 isKey BOOLEAN,
482 isTimeKey boolean);
483
484 TYPE tab_clsDBColumn IS TABLE OF clsDBColumn INDEX BY BINARY_INTEGER;
485
486
487
488 Type TNewITables IS RECORD(
489 Name VARCHAR2(100),
490 periodicity NUMBER,
491 Fields VARCHAR2(32000), -- comma sep
492 numFields NUMBER,
493 Indicators VARCHAR2(32000), -- comma sep
494 NumIndicators NUMBER);
495
496 TYPE tab_TNewITables IS TABLE OF TNewITables INDEX BY BINARY_INTEGER;
497
498 garrNewITables tab_TNewITables; --array that contains the input tables of new system
499 gnumNewITables NUMBER;
500
501 --Procedure writeLog(p_message IN VARCHAR2);
502 --Procedure writeOut(p_message IN VARCHAR2);
503
504 --Procedure writeDoc(p_message IN VARCHAR2);
505
506 g_dir VARCHAR2(300);
507 g_file utl_file.file_type;
508 g_fileOpened boolean := false;
509
510 g_filename VARCHAR2(200) := 'METADATA';
511
512 Function getUtlFileDir return VARCHAR2 ;
513
514 -- MV and Upgrade Changes
515 --BSC-MV Note: Profile to indicate if the system is with DBI architecture
516 g_BSC_MV Boolean;
517 g_Adv_Summarization_Level VARCHAR2(100);
518 g_Current_Adv_Sum_Level VARCHAR2(100);
519 g_Sum_Level_Change NUMBER; --0- No change
520 --1- Upgrade to new architecture (from NULL to NOT NULL)
521 --2- Just changing the sum level (Example: from 2 to 3)
525 garrTablesUpgradeT DBMS_SQL.VARCHAR2_TABLE;
522 garrTablesUpgrade DBMS_SQL.VARCHAR2_TABLE; --array with the T, base and input tables used by production indicators.
523 --This array is used only when Sum level is changed from NULL to NOTNULL
524 gnumTablesUpgrade NUMBER;
526 gnumTablesUpgradeT NUMBER;
527
528 Type clsConfigKpiMV IS RECORD(
529 LevelComb VARCHAR2(1000), --Level combination ?110
530 MVName VARCHAR2(100), --MV name
531 DataSource VARCHAR2(100), --Data source; MV or SQL
532 SqlStmt VARCHAR2(4000));--SQL statement
533
534 TYPE tab_clsConfigKpiMV IS TABLE OF clsConfigKpiMV INDEX BY BINARY_INTEGER;
535
536
537 Type clsKPIDimSet IS RECORD(
538 kpi_number NUMBER,
539 dim_set_id NUMBER);
540
541 TYPE tab_clsKPIDimSet IS TABLE OF clsKPIDimSet INDEX BY BINARY_INTEGER;
542
543 --PROCEDURE RenameInputTable(pOld IN VARCHAR2, pNew IN VARCHAR2, pStatus OUT VARCHAR2, pMessage OUT VARCHAR2) ;
544
545
546 --API counters
547
548 ginsertKeys NUMBER:=0;
549 gupdateKeys NUMBER:=0;
550 ginsertData NUMBER:=0;
551 ginsertData1Row NUMBER:=0;
552 ggetAllKeyFields NUMBER:=0;
553 ggetOneKeyField NUMBER:=0;
554 ggetAllDataFields NUMBER:=0;
555 ggetOneDataField NUMBER:=0;
556 ggetDisaggs NUMBER:=0;
557 ggetDisaggKeys NUMBER:=0;
558 ginsertDisAggs NUMBER:=0;
559 ginsertOneDisAgg NUMBER:=0;
560 ginsertDisAggKeys NUMBER:=0;
561 ginsertOneDisAggKey NUMBER:=0;
562 gupdateOneDisAgg NUMBER:=0;
563 gupdateOneDisAggKey NUMBER:=0;
564
565 gconsolidateString NUMBER := 0;
566 gDropTable NUMBER := 0;
567 ggupdateKeys NUMBER := 0;
568
569 ginsertSingleKey NUMBER := 0;
570
571 gUIAPI boolean := false;
572 gThreadType VARCHAR2(10);
573
574 gTesting boolean := true;
575
576 gTables_counter number := 0;
577
578 --API timings
579 g_time_getOneDataField NUMBER := 0;
580 g_time_InsertData1Row NUMBER := 0;
581 g_time_updateKeys NUMBER := 0;
582 g_time_getOneKeyField NUMBER := 0;
583
584 g_time_getAllDataFields NUMBER := 0;
585 g_time_getAllKeyFields NUMBER := 0;
586 g_time_insertKeys NUMBER := 0;
587 g_time_insertData NUMBER := 0;
588 g_time_getDisaggs NUMBER := 0;
589 g_time_getDisaggKeys NUMBER := 0;
590 g_time_insertDisAggs NUMBER := 0;
591 g_time_insertOneDisAgg NUMBER := 0;
592 g_time_insertDisAggKeys NUMBER := 0;
593 g_time_insertOneDisAggKey NUMBER := 0;
594 g_time_updateOneDisAgg NUMBER := 0;
595 g_time_updateOneDisAggKey NUMBER := 0;
596 --g_time_consolidateString NUMBER := 0;
597
598 --g_table
599
600 PROCEDURE logProgress(pStage IN VARCHAR2, pMessage IN VARCHAR2) ;
601
602 FUNCTION is_totally_shared_obj(p_objective IN NUMBER) RETURN BOOLEAN;
603
604 END BSC_METADATA_OPTIMIZER_PKG;
[Home] [Help]