eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.BSC_MO_DB_PKG dependencies on BSC_METADATA_OPTIMIZER_PKG

Line 11: g_ddl_table_name VARCHAR2(100) := 'BSC_TMP_DDL_TABLE'||bsc_metadata_optimizer_pkg.g_session_id;

7:
8:
9: g_buckets NUMBER := 1;
10: g_bucket_id NUMBER := 1;
11: g_ddl_table_name VARCHAR2(100) := 'BSC_TMP_DDL_TABLE'||bsc_metadata_optimizer_pkg.g_session_id;
12: g_parallelize boolean := false;
13: g_parallel_threshold number := 50000000; -- never parallelize in 5.2
14: g_tables_processed number := 0;
15: g_bucket_size number := 100000;

Line 42: execute immediate 'truncate table '||BSC_METADATA_OPTIMIZER_PKG.gBSCSchema||'.'||g_ddl_table_name;

38: IF bsc_mo_helper_pkg.tableexists(g_ddl_table_name)=false THEN
39: bsc_mo_helper_pkg.do_ddl(l_stmt, ad_ddl.create_table, g_ddl_table_name);
40: bsc_mo_helper_pkg.do_ddl(l_index, ad_ddl.create_index, g_ddl_table_name||'_U1');
41: ELSE
42: execute immediate 'truncate table '||BSC_METADATA_OPTIMIZER_PKG.gBSCSchema||'.'||g_ddl_table_name;
43: END IF;
44:
45: EXCEPTION WHEN OTHERS THEN
46: l_error := sqlerrm;

Line 85: PROCEDURE sort_data_columns(p_data IN OUT NOCOPY BSC_METADATA_OPTIMIZER_PKG.tab_clsDataField) IS

81: -- Given a list of measures, sort them and return it
82: --
83: -- AUTHOR/DATE - MODIFICATIONS (AUTHOR/DATE/DESCRIPTION):
84: --****************************************************************************
85: PROCEDURE sort_data_columns(p_data IN OUT NOCOPY BSC_METADATA_OPTIMIZER_PKG.tab_clsDataField) IS
86: wasAdded boolean ;
87: l_data_cols BSC_METADATA_OPTIMIZER_PKG.tab_clsDataField;
88: l_col BSC_METADATA_OPTIMIZER_PKG.clsDataField;
89: CURSOR cOrdered IS

Line 87: l_data_cols BSC_METADATA_OPTIMIZER_PKG.tab_clsDataField;

83: -- AUTHOR/DATE - MODIFICATIONS (AUTHOR/DATE/DESCRIPTION):
84: --****************************************************************************
85: PROCEDURE sort_data_columns(p_data IN OUT NOCOPY BSC_METADATA_OPTIMIZER_PKG.tab_clsDataField) IS
86: wasAdded boolean ;
87: l_data_cols BSC_METADATA_OPTIMIZER_PKG.tab_clsDataField;
88: l_col BSC_METADATA_OPTIMIZER_PKG.clsDataField;
89: CURSOR cOrdered IS
90: SELECT value_v, value_n order_index FROM BSC_TMP_BIG_IN_COND
91: WHERE variable_id=10

Line 88: l_col BSC_METADATA_OPTIMIZER_PKG.clsDataField;

84: --****************************************************************************
85: PROCEDURE sort_data_columns(p_data IN OUT NOCOPY BSC_METADATA_OPTIMIZER_PKG.tab_clsDataField) IS
86: wasAdded boolean ;
87: l_data_cols BSC_METADATA_OPTIMIZER_PKG.tab_clsDataField;
88: l_col BSC_METADATA_OPTIMIZER_PKG.clsDataField;
89: CURSOR cOrdered IS
90: SELECT value_v, value_n order_index FROM BSC_TMP_BIG_IN_COND
91: WHERE variable_id=10
92: AND session_id=USERENV('SESSIONID')

Line 132: AND OWNER = BSC_METADATA_OPTIMIZER_PKG.gBSCSchema;

128: SELECT DATA_TYPE, DATA_LENGTH
129: FROM ALL_TAB_COLUMNS
130: WHERE TABLE_NAME = dimTable
131: AND COLUMN_NAME = columnName
132: AND OWNER = BSC_METADATA_OPTIMIZER_PKG.gBSCSchema;
133:
134: l_temp1 NUMBER;
135: l_type VARCHAR2(30) := null;
136: l_length NUMBER;

Line 139: l_temp1 := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gMasterTable, dimTable);

135: l_type VARCHAR2(30) := null;
136: l_length NUMBER;
137:
138: BEGIN
139: l_temp1 := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gMasterTable, dimTable);
140: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
141: bsc_mo_helper_pkg.writetmp('l_temp1 ='||l_temp1);
142: bsc_mo_helper_pkg.writetmp('dimTable = '||dimTable||', column = '||columnName||', source ='||
143: BSC_METADATA_OPTIMIZER_PKG.gMasterTable(l_temp1).Source);

Line 140: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN

136: l_length NUMBER;
137:
138: BEGIN
139: l_temp1 := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gMasterTable, dimTable);
140: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
141: bsc_mo_helper_pkg.writetmp('l_temp1 ='||l_temp1);
142: bsc_mo_helper_pkg.writetmp('dimTable = '||dimTable||', column = '||columnName||', source ='||
143: BSC_METADATA_OPTIMIZER_PKG.gMasterTable(l_temp1).Source);
144: END IF;

Line 143: BSC_METADATA_OPTIMIZER_PKG.gMasterTable(l_temp1).Source);

139: l_temp1 := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gMasterTable, dimTable);
140: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
141: bsc_mo_helper_pkg.writetmp('l_temp1 ='||l_temp1);
142: bsc_mo_helper_pkg.writetmp('dimTable = '||dimTable||', column = '||columnName||', source ='||
143: BSC_METADATA_OPTIMIZER_PKG.gMasterTable(l_temp1).Source);
144: END IF;
145: If BSC_METADATA_OPTIMIZER_PKG.gMasterTable(l_temp1).Source = 'PMF' Then
146: --The dimension table is a View
147: OPEN cUserTabColumns;

Line 145: If BSC_METADATA_OPTIMIZER_PKG.gMasterTable(l_temp1).Source = 'PMF' Then

141: bsc_mo_helper_pkg.writetmp('l_temp1 ='||l_temp1);
142: bsc_mo_helper_pkg.writetmp('dimTable = '||dimTable||', column = '||columnName||', source ='||
143: BSC_METADATA_OPTIMIZER_PKG.gMasterTable(l_temp1).Source);
144: END IF;
145: If BSC_METADATA_OPTIMIZER_PKG.gMasterTable(l_temp1).Source = 'PMF' Then
146: --The dimension table is a View
147: OPEN cUserTabColumns;
148: FETCH cUserTabColumns INTO l_type, l_length;
149: CLOSE cUserTabColumns;

Line 158: raise bsc_metadata_optimizer_pkg.optimizer_exception;

154: End If;
155:
156: IF l_type = 'UNDEFINED' then -- view is invalid
157: bsc_mo_helper_pkg.writeTmp('View '||dimTable||' is invalid', FND_LOG.LEVEL_EXCEPTION);
158: raise bsc_metadata_optimizer_pkg.optimizer_exception;
159: END IF;
160: If l_type IS NULL Then
161: dataLength := 5;
162: return 'VARCHAR2';

Line 178: function reorder_index(p_b_pt_table_name varchar2, colColumns IN BSC_METADATA_OPTIMIZER_PKG.tab_clsDBColumn) return varchar2 is

174: columnName||' :'||sqlerrm);
175: raise;
176: End;
177:
178: function reorder_index(p_b_pt_table_name varchar2, colColumns IN BSC_METADATA_OPTIMIZER_PKG.tab_clsDBColumn) return varchar2 is
179: l_periodicity_id_exists boolean;
180: l_year_exists boolean;
181: l_period_exists boolean;
182: l_type_exists boolean;

Line 235: colCampos IN OUT NOCOPY BSC_METADATA_OPTIMIZER_PKG.tab_clsDBColumn,

231: return l_stmt;
232: end;
233:
234: PROCEDURE addAWColumns(
235: colCampos IN OUT NOCOPY BSC_METADATA_OPTIMIZER_PKG.tab_clsDBColumn,
236: p_b_aw_table IN boolean)
237: IS
238: dbColumn BSC_METADATA_OPTIMIZER_PKG.clsDBColumn;
239: dbColumn_null BSC_METADATA_OPTIMIZER_PKG.clsDBColumn := null;

Line 238: dbColumn BSC_METADATA_OPTIMIZER_PKG.clsDBColumn;

234: PROCEDURE addAWColumns(
235: colCampos IN OUT NOCOPY BSC_METADATA_OPTIMIZER_PKG.tab_clsDBColumn,
236: p_b_aw_table IN boolean)
237: IS
238: dbColumn BSC_METADATA_OPTIMIZER_PKG.clsDBColumn;
239: dbColumn_null BSC_METADATA_OPTIMIZER_PKG.clsDBColumn := null;
240: l_columns BSC_METADATA_OPTIMIZER_PKG.tab_clsDBColumn;
241: BEGIN
242: -- projection

Line 239: dbColumn_null BSC_METADATA_OPTIMIZER_PKG.clsDBColumn := null;

235: colCampos IN OUT NOCOPY BSC_METADATA_OPTIMIZER_PKG.tab_clsDBColumn,
236: p_b_aw_table IN boolean)
237: IS
238: dbColumn BSC_METADATA_OPTIMIZER_PKG.clsDBColumn;
239: dbColumn_null BSC_METADATA_OPTIMIZER_PKG.clsDBColumn := null;
240: l_columns BSC_METADATA_OPTIMIZER_PKG.tab_clsDBColumn;
241: BEGIN
242: -- projection
243: dbColumn := dbColumn_null;

Line 240: l_columns BSC_METADATA_OPTIMIZER_PKG.tab_clsDBColumn;

236: p_b_aw_table IN boolean)
237: IS
238: dbColumn BSC_METADATA_OPTIMIZER_PKG.clsDBColumn;
239: dbColumn_null BSC_METADATA_OPTIMIZER_PKG.clsDBColumn := null;
240: l_columns BSC_METADATA_OPTIMIZER_PKG.tab_clsDBColumn;
241: BEGIN
242: -- projection
243: dbColumn := dbColumn_null;
244: dbColumn.columnName := 'PROJECTION';

Line 245: dbColumn.columnType := BSC_METADATA_OPTIMIZER_PKG.DTVarchar2;

241: BEGIN
242: -- projection
243: dbColumn := dbColumn_null;
244: dbColumn.columnName := 'PROJECTION';
245: dbColumn.columnType := BSC_METADATA_OPTIMIZER_PKG.DTVarchar2;
246: dbColumn.columnLength := 100;
247: dbColumn.iskey := false;
248: colCampos(colCampos.count) := dbColumn;
249: --05/18/2005, Venu asked me to not create B_AW table and move change_vector to B table

Line 253: dbColumn.columnType := BSC_METADATA_OPTIMIZER_PKG.DTNumber;

249: --05/18/2005, Venu asked me to not create B_AW table and move change_vector to B table
250: --Add Change Vector right after projection col
251: dbColumn := dbColumn_null;
252: dbColumn.columnName := 'CHANGE_VECTOR';
253: dbColumn.columnType := BSC_METADATA_OPTIMIZER_PKG.DTNumber;
254: dbColumn.columnLength := 0;
255: dbColumn.isKey := false;
256: colCampos(colCampos.count) := dbColumn;
257:

Line 262: colColumns IN BSC_METADATA_OPTIMIZER_PKG.tab_clsDBColumn,

258: bsc_mo_helper_pkg.writeTmp('added AW column(s) to table');
259: END;
260:
261: PROCEDURE get_column_list(
262: colColumns IN BSC_METADATA_OPTIMIZER_PKG.tab_clsDBColumn,
263: p_column_sql OUT NOCOPY VARCHAR2,
264: p_primary_key_sql OUT NOCOPY VARCHAR2,
265: p_keys_exist OUT NOCOPY boolean ) IS
266: l_index1 number;

Line 267: dbColumn BSC_METADATA_OPTIMIZER_PKG.clsDBColumn;

263: p_column_sql OUT NOCOPY VARCHAR2,
264: p_primary_key_sql OUT NOCOPY VARCHAR2,
265: p_keys_exist OUT NOCOPY boolean ) IS
266: l_index1 number;
267: dbColumn BSC_METADATA_OPTIMIZER_PKG.clsDBColumn;
268: BEGIN
269: p_column_sql := null;
270: l_index1 := colColumns.first;
271: LOOP

Line 326: if (bsc_metadata_optimizer_pkg.g_log) then

322: raise;
323: end if;
324: END;
325: END IF;
326: if (bsc_metadata_optimizer_pkg.g_log) then
327: bsc_mo_helper_pkg.writeTmp(p_ddl);
328: bsc_mo_helper_pkg.writetmp(p_object_type||' '||p_object_name||' created successfully');
329: end if;
330:

Line 353: FUNCTION create_b_prj_table(p_table IN BSC_METADATA_OPTIMIZER_PKG.clsTable, p_column_clause VARCHAR2, p_storage_clause VARCHAR2)

349: BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in get_table_name, Table='||p_table_name);
350: raise;
351: END;
352:
353: FUNCTION create_b_prj_table(p_table IN BSC_METADATA_OPTIMIZER_PKG.clsTable, p_column_clause VARCHAR2, p_storage_clause VARCHAR2)
354: RETURN VARCHAR2 IS
355: l_stmt VARCHAR2(32767);
356: l_table_name varchar2(100);
357: BEGIN

Line 369: FUNCTION create_i_rowid_table(p_table IN BSC_METADATA_OPTIMIZER_PKG.clsTable, p_storage_clause VARCHAR2)

365: BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in create_b_prj_table, Table='||p_table.name||', stmt='||l_stmt||', error='||sqlerrm);
366: raise;
367: END;
368:
369: FUNCTION create_i_rowid_table(p_table IN BSC_METADATA_OPTIMIZER_PKG.clsTable, p_storage_clause VARCHAR2)
370: RETURN VARCHAR2 IS
371: l_stmt VARCHAR2(4000);
372: l_table_name varchar2(100);
373:

Line 412: colColumns IN BSC_METADATA_OPTIMIZER_PKG.tab_clsDBColumn,

408: -- db_obj: database object
409: --****************************************************************************
410: PROCEDURE CreateTableInDB(
411: p_table_name IN VARCHAR2,
412: colColumns IN BSC_METADATA_OPTIMIZER_PKG.tab_clsDBColumn,
413: TableTbsName IN VARCHAR,
414: IndexTbsName IN VARCHAR,
415: p_table IN BSC_METADATA_OPTIMIZER_PKG.clsTable,
416: p_table_type IN VARCHAR2)

Line 415: p_table IN BSC_METADATA_OPTIMIZER_PKG.clsTable,

411: p_table_name IN VARCHAR2,
412: colColumns IN BSC_METADATA_OPTIMIZER_PKG.tab_clsDBColumn,
413: TableTbsName IN VARCHAR,
414: IndexTbsName IN VARCHAR,
415: p_table IN BSC_METADATA_OPTIMIZER_PKG.clsTable,
416: p_table_type IN VARCHAR2)
417: IS
418: dbColumn BSC_METADATA_OPTIMIZER_PKG.clsDBColumn;
419: l_stmt VARCHAR2(32767);

Line 418: dbColumn BSC_METADATA_OPTIMIZER_PKG.clsDBColumn;

414: IndexTbsName IN VARCHAR,
415: p_table IN BSC_METADATA_OPTIMIZER_PKG.clsTable,
416: p_table_type IN VARCHAR2)
417: IS
418: dbColumn BSC_METADATA_OPTIMIZER_PKG.clsDBColumn;
419: l_stmt VARCHAR2(32767);
420: columnList VARCHAR2(8000):=null;
421: l_primary_key VARCHAR2(1000) := null;
422: l_index1 NUMBER;

Line 430: l_table BSC_METADATA_OPTIMIZER_PKG.clsTable;

426: l_index_name VARCHAR2(100);
427: l_error varchar2(2000);
428: l_aw_stmt varchar2(32767);
429: l_aw_index_stmt varchar2(32767);
430: l_table BSC_METADATA_OPTIMIZER_PKG.clsTable;
431:
432: l_b_aw_columns BSC_METADATA_OPTIMIZER_PKG.tab_clsDBColumn;
433:
434: l_table_name VARCHAR2(100);

Line 432: l_b_aw_columns BSC_METADATA_OPTIMIZER_PKG.tab_clsDBColumn;

428: l_aw_stmt varchar2(32767);
429: l_aw_index_stmt varchar2(32767);
430: l_table BSC_METADATA_OPTIMIZER_PKG.clsTable;
431:
432: l_b_aw_columns BSC_METADATA_OPTIMIZER_PKG.tab_clsDBColumn;
433:
434: l_table_name VARCHAR2(100);
435: b_keys_exist boolean ;
436:

Line 447: l_stmt := l_stmt || bsc_metadata_optimizer_pkg.g_partition_clause;

443: get_column_list(colColumns, columnList, l_primary_key, b_keys_exist);
444:
445: l_stmt := 'CREATE TABLE ' || p_table_name||' (' || columnList||') ';
446: if (p_table_type = 'B' and b_keys_exist) then
447: l_stmt := l_stmt || bsc_metadata_optimizer_pkg.g_partition_clause;
448: end if;
449: l_stmt := l_stmt||' TABLESPACE '||TableTbsName || ' '||BSC_METADATA_OPTIMIZER_PKG.gStorageClause;
450: handle_ddl(p_table_name, 'TABLE', l_stmt);
451:

Line 449: l_stmt := l_stmt||' TABLESPACE '||TableTbsName || ' '||BSC_METADATA_OPTIMIZER_PKG.gStorageClause;

445: l_stmt := 'CREATE TABLE ' || p_table_name||' (' || columnList||') ';
446: if (p_table_type = 'B' and b_keys_exist) then
447: l_stmt := l_stmt || bsc_metadata_optimizer_pkg.g_partition_clause;
448: end if;
449: l_stmt := l_stmt||' TABLESPACE '||TableTbsName || ' '||BSC_METADATA_OPTIMIZER_PKG.gStorageClause;
450: handle_ddl(p_table_name, 'TABLE', l_stmt);
451:
452: -- Create B_PRJ table and indexes
453: IF (p_table_type = 'B') THEN

Line 456: l_table_misc_clause := bsc_metadata_optimizer_pkg.g_partition_clause;

452: -- Create B_PRJ table and indexes
453: IF (p_table_type = 'B') THEN
454: -- Bug 4765104, partition only when dim keys exist
455: if (b_keys_exist) then
456: l_table_misc_clause := bsc_metadata_optimizer_pkg.g_partition_clause;
457: add_to_update_tables_list(p_table_name, BSC_DBGEN_STD_METADATA.BSC_PARTITION,
458: bsc_metadata_optimizer_pkg.g_num_partitions);
459: end if;
460: l_table_misc_clause := l_table_misc_clause||' TABLESPACE '||TableTbsName ||

Line 458: bsc_metadata_optimizer_pkg.g_num_partitions);

454: -- Bug 4765104, partition only when dim keys exist
455: if (b_keys_exist) then
456: l_table_misc_clause := bsc_metadata_optimizer_pkg.g_partition_clause;
457: add_to_update_tables_list(p_table_name, BSC_DBGEN_STD_METADATA.BSC_PARTITION,
458: bsc_metadata_optimizer_pkg.g_num_partitions);
459: end if;
460: l_table_misc_clause := l_table_misc_clause||' TABLESPACE '||TableTbsName ||
461: ' '||BSC_METADATA_OPTIMIZER_PKG.gStorageClause;
462: l_table_name := create_b_prj_table(p_table, columnList, l_table_misc_clause);

Line 461: ' '||BSC_METADATA_OPTIMIZER_PKG.gStorageClause;

457: add_to_update_tables_list(p_table_name, BSC_DBGEN_STD_METADATA.BSC_PARTITION,
458: bsc_metadata_optimizer_pkg.g_num_partitions);
459: end if;
460: l_table_misc_clause := l_table_misc_clause||' TABLESPACE '||TableTbsName ||
461: ' '||BSC_METADATA_OPTIMIZER_PKG.gStorageClause;
462: l_table_name := create_b_prj_table(p_table, columnList, l_table_misc_clause);
463: add_to_update_tables_list(p_table_name, BSC_DBGEN_STD_METADATA.BSC_B_PRJ_TABLE, l_table_name);
464: --bug fix 5647971 centralized logic to get index name
465: -- added api in helper package

Line 468: ' '|| BSC_METADATA_OPTIMIZER_PKG.gStorageClause;

464: --bug fix 5647971 centralized logic to get index name
465: -- added api in helper package
466: l_index_name := BSC_MO_HELPER_PKG.generate_index_name(p_table_name,'B','1');
467: l_index_stmt := 'CREATE BITMAP INDEX '||l_index_name||' ON '||p_table_name||'(YEAR) TABLESPACE '||IndexTbsName||
468: ' '|| BSC_METADATA_OPTIMIZER_PKG.gStorageClause;
469: IF (bsc_metadata_optimizer_pkg.g_num_partitions>1 and b_keys_exist) then
470: l_index_stmt := l_index_stmt||' LOCAL ';
471: END IF;
472: -- Create Bitmap indexes on YEAR And CHANGE_VECTOR (AW case)

Line 469: IF (bsc_metadata_optimizer_pkg.g_num_partitions>1 and b_keys_exist) then

465: -- added api in helper package
466: l_index_name := BSC_MO_HELPER_PKG.generate_index_name(p_table_name,'B','1');
467: l_index_stmt := 'CREATE BITMAP INDEX '||l_index_name||' ON '||p_table_name||'(YEAR) TABLESPACE '||IndexTbsName||
468: ' '|| BSC_METADATA_OPTIMIZER_PKG.gStorageClause;
469: IF (bsc_metadata_optimizer_pkg.g_num_partitions>1 and b_keys_exist) then
470: l_index_stmt := l_index_stmt||' LOCAL ';
471: END IF;
472: -- Create Bitmap indexes on YEAR And CHANGE_VECTOR (AW case)
473:

Line 481: '(CHANGE_VECTOR) TABLESPACE '||IndexTbsName || ' '|| BSC_METADATA_OPTIMIZER_PKG.gStorageClause;

477: --bug fix 5647971 centralized logic to get index name
478: -- added api in helper package
479: l_index_name := BSC_MO_HELPER_PKG.generate_index_name(p_table_name,'B','2');
480: l_index_stmt := 'CREATE BITMAP INDEX ' || l_index_name || ' ON '|| p_table_name ||
481: '(CHANGE_VECTOR) TABLESPACE '||IndexTbsName || ' '|| BSC_METADATA_OPTIMIZER_PKG.gStorageClause;
482: IF (bsc_metadata_optimizer_pkg.g_num_partitions>1 and b_keys_exist) then
483: l_index_stmt := l_index_stmt||' LOCAL ';
484: END IF;
485: handle_ddl(l_index_name, 'INDEX', l_index_stmt);

Line 482: IF (bsc_metadata_optimizer_pkg.g_num_partitions>1 and b_keys_exist) then

478: -- added api in helper package
479: l_index_name := BSC_MO_HELPER_PKG.generate_index_name(p_table_name,'B','2');
480: l_index_stmt := 'CREATE BITMAP INDEX ' || l_index_name || ' ON '|| p_table_name ||
481: '(CHANGE_VECTOR) TABLESPACE '||IndexTbsName || ' '|| BSC_METADATA_OPTIMIZER_PKG.gStorageClause;
482: IF (bsc_metadata_optimizer_pkg.g_num_partitions>1 and b_keys_exist) then
483: l_index_stmt := l_index_stmt||' LOCAL ';
484: END IF;
485: handle_ddl(l_index_name, 'INDEX', l_index_stmt);
486: END IF;

Line 493: --AND bsc_metadata_optimizer_pkg.g_num_partitions>1

489:
490: -- create I_ROWID table for Input Tables
491: IF p_table_type ='I' THEN
492: -- 11/16/2005, always generate rowid tables, after discussion with venu/mauricio.
493: --AND bsc_metadata_optimizer_pkg.g_num_partitions>1
494: l_table_name := create_i_rowid_table(p_table, ' TABLESPACE '||TableTbsName ||
495: ' '||BSC_METADATA_OPTIMIZER_PKG.gStorageClause);
496: add_to_update_tables_list(p_table_name, BSC_DBGEN_STD_METADATA.BSC_I_ROWID_TABLE, l_table_name);
497: END IF;

Line 495: ' '||BSC_METADATA_OPTIMIZER_PKG.gStorageClause);

491: IF p_table_type ='I' THEN
492: -- 11/16/2005, always generate rowid tables, after discussion with venu/mauricio.
493: --AND bsc_metadata_optimizer_pkg.g_num_partitions>1
494: l_table_name := create_i_rowid_table(p_table, ' TABLESPACE '||TableTbsName ||
495: ' '||BSC_METADATA_OPTIMIZER_PKG.gStorageClause);
496: add_to_update_tables_list(p_table_name, BSC_DBGEN_STD_METADATA.BSC_I_ROWID_TABLE, l_table_name);
497: END IF;
498:
499: IF l_primary_key IS NOT NULL THEN -- create normal index

Line 507: ' TABLESPACE '||IndexTbsName || ' '|| BSC_METADATA_OPTIMIZER_PKG.gStorageClause;

503: end if;
504: l_index_name := p_table_name ||'_U1';
505: l_index_stmt := 'CREATE UNIQUE INDEX ' || l_index_name || ' ON '|| p_table_name ||
506: ' ('|| l_primary_key || ') '||
507: ' TABLESPACE '||IndexTbsName || ' '|| BSC_METADATA_OPTIMIZER_PKG.gStorageClause;
508: handle_ddl(l_index_name, 'INDEX', l_index_stmt);
509: END IF;
510:
511: EXCEPTION WHEN OTHERS THEN

Line 530: OPEN c1(BSC_METADATA_OPTIMIZER_PKG.gBscSchema);

526: AND OWNER = pSchema;
527:
528: l_error VARCHAR2(1000);
529: BEGIN
530: OPEN c1(BSC_METADATA_OPTIMIZER_PKG.gBscSchema);
531: FETCH c1 INTO l_dummy;
532: IF c1%FOUND THEN
533: l_res := TRUE;
534: END IF;

Line 553: PROCEDURE CreateProjTable(p_table IN BSC_METADATA_OPTIMIZER_PKG.clsTable) IS

549: -- PARAMETERS:
550: -- Tabla: Table. It is an object with all inFORmation about the table
551: --
552: --****************************************************************************
553: PROCEDURE CreateProjTable(p_table IN BSC_METADATA_OPTIMIZER_PKG.clsTable) IS
554:
555: Llave BSC_METADATA_OPTIMIZER_PKG.clsKeyField;
556: Dato BSC_METADATA_OPTIMIZER_PKG.clsDataField;
557: colCampos BSC_METADATA_OPTIMIZER_PKG.tab_clsDBColumn;

Line 555: Llave BSC_METADATA_OPTIMIZER_PKG.clsKeyField;

551: --
552: --****************************************************************************
553: PROCEDURE CreateProjTable(p_table IN BSC_METADATA_OPTIMIZER_PKG.clsTable) IS
554:
555: Llave BSC_METADATA_OPTIMIZER_PKG.clsKeyField;
556: Dato BSC_METADATA_OPTIMIZER_PKG.clsDataField;
557: colCampos BSC_METADATA_OPTIMIZER_PKG.tab_clsDBColumn;
558:
559: dbColumn BSC_METADATA_OPTIMIZER_PKG.clsDBColumn;

Line 556: Dato BSC_METADATA_OPTIMIZER_PKG.clsDataField;

552: --****************************************************************************
553: PROCEDURE CreateProjTable(p_table IN BSC_METADATA_OPTIMIZER_PKG.clsTable) IS
554:
555: Llave BSC_METADATA_OPTIMIZER_PKG.clsKeyField;
556: Dato BSC_METADATA_OPTIMIZER_PKG.clsDataField;
557: colCampos BSC_METADATA_OPTIMIZER_PKG.tab_clsDBColumn;
558:
559: dbColumn BSC_METADATA_OPTIMIZER_PKG.clsDBColumn;
560: NomCampoPeriod VARCHAR2(100);

Line 557: colCampos BSC_METADATA_OPTIMIZER_PKG.tab_clsDBColumn;

553: PROCEDURE CreateProjTable(p_table IN BSC_METADATA_OPTIMIZER_PKG.clsTable) IS
554:
555: Llave BSC_METADATA_OPTIMIZER_PKG.clsKeyField;
556: Dato BSC_METADATA_OPTIMIZER_PKG.clsDataField;
557: colCampos BSC_METADATA_OPTIMIZER_PKG.tab_clsDBColumn;
558:
559: dbColumn BSC_METADATA_OPTIMIZER_PKG.clsDBColumn;
560: NomCampoPeriod VARCHAR2(100);
561: NomCampoSubPeriod VARCHAR2(100);

Line 559: dbColumn BSC_METADATA_OPTIMIZER_PKG.clsDBColumn;

555: Llave BSC_METADATA_OPTIMIZER_PKG.clsKeyField;
556: Dato BSC_METADATA_OPTIMIZER_PKG.clsDataField;
557: colCampos BSC_METADATA_OPTIMIZER_PKG.tab_clsDBColumn;
558:
559: dbColumn BSC_METADATA_OPTIMIZER_PKG.clsDBColumn;
560: NomCampoPeriod VARCHAR2(100);
561: NomCampoSubPeriod VARCHAR2(100);
562: msg VARCHAR2(100);
563: uv_name VARCHAR2(100);

Line 568: Tabla_Keys BSC_METADATA_OPTIMIZER_PKG.tab_clsKeyField;

564: l_stmt VARCHAR2(1000);
565: isBaseTable Boolean;
566: createTable Boolean;
567:
568: Tabla_Keys BSC_METADATA_OPTIMIZER_PKG.tab_clsKeyField;
569: Tabla_Data BSC_METADATA_OPTIMIZER_PKG.tab_clsDataField;
570:
571: l_index NUMBER;
572:

Line 569: Tabla_Data BSC_METADATA_OPTIMIZER_PKG.tab_clsDataField;

565: isBaseTable Boolean;
566: createTable Boolean;
567:
568: Tabla_Keys BSC_METADATA_OPTIMIZER_PKG.tab_clsKeyField;
569: Tabla_Data BSC_METADATA_OPTIMIZER_PKG.tab_clsDataField;
570:
571: l_index NUMBER;
572:
573: New_clsdbColumn BSC_METADATA_OPTIMIZER_PKG.clsDBColumn;

Line 573: New_clsdbColumn BSC_METADATA_OPTIMIZER_PKG.clsDBColumn;

569: Tabla_Data BSC_METADATA_OPTIMIZER_PKG.tab_clsDataField;
570:
571: l_index NUMBER;
572:
573: New_clsdbColumn BSC_METADATA_OPTIMIZER_PKG.clsDBColumn;
574: l_error VARCHAR2(1000);
575:
576: dimTable VARCHAR2(100);
577: l_temp1 NUMBER;

Line 601: l_temp1 := BSC_MO_HELPER_PKG.FindKeyIndex(BSC_METADATA_OPTIMIZER_PKG.gMasterTable, Llave.keyName);

597: EXIT WHEN Tabla_Keys.count=0;
598: Llave := Tabla_Keys(l_index);
599: dbColumn := New_clsdbColumn;
600:
601: l_temp1 := BSC_MO_HELPER_PKG.FindKeyIndex(BSC_METADATA_OPTIMIZER_PKG.gMasterTable, Llave.keyName);
602:
603: dimTable := BSC_METADATA_OPTIMIZER_PKG.gMasterTable(l_temp1).name;
604: dbColumn.columnName := Llave.keyName;
605: dbColumn.columnType := GetDimKeyDataType(dimTable, 'CODE', columnSize);

Line 603: dimTable := BSC_METADATA_OPTIMIZER_PKG.gMasterTable(l_temp1).name;

599: dbColumn := New_clsdbColumn;
600:
601: l_temp1 := BSC_MO_HELPER_PKG.FindKeyIndex(BSC_METADATA_OPTIMIZER_PKG.gMasterTable, Llave.keyName);
602:
603: dimTable := BSC_METADATA_OPTIMIZER_PKG.gMasterTable(l_temp1).name;
604: dbColumn.columnName := Llave.keyName;
605: dbColumn.columnType := GetDimKeyDataType(dimTable, 'CODE', columnSize);
606: dbColumn.columnlength := columnSize;
607: dbColumn.isKey := TRUE;

Line 618: dbColumn.columnType := BSC_METADATA_OPTIMIZER_PKG.DTNumber;

614: END LOOP;
615: --Year
616: dbColumn := New_clsdbColumn;
617: dbColumn.columnName := 'YEAR';
618: dbColumn.columnType := BSC_METADATA_OPTIMIZER_PKG.DTNumber;
619: dbColumn.columnLength := BSC_METADATA_OPTIMIZER_PKG.ORA_DATA_PRECISION_INTEGER;
620: dbColumn.isKey := TRUE;
621: dbColumn.isTimeKey := TRUE;
622: colCampos(colCampos.count) := dbColumn;

Line 619: dbColumn.columnLength := BSC_METADATA_OPTIMIZER_PKG.ORA_DATA_PRECISION_INTEGER;

615: --Year
616: dbColumn := New_clsdbColumn;
617: dbColumn.columnName := 'YEAR';
618: dbColumn.columnType := BSC_METADATA_OPTIMIZER_PKG.DTNumber;
619: dbColumn.columnLength := BSC_METADATA_OPTIMIZER_PKG.ORA_DATA_PRECISION_INTEGER;
620: dbColumn.isKey := TRUE;
621: dbColumn.isTimeKey := TRUE;
622: colCampos(colCampos.count) := dbColumn;
623:

Line 627: dbColumn.columnType := BSC_METADATA_OPTIMIZER_PKG.DTNumber;

623:
624: --Type
625: dbColumn := New_clsdbColumn;
626: dbColumn.columnName := 'TYPE';
627: dbColumn.columnType := BSC_METADATA_OPTIMIZER_PKG.DTNumber;
628: dbColumn.columnLength := BSC_METADATA_OPTIMIZER_PKG.ORA_DATA_PRECISION_BYTE;
629: dbColumn.isKey := TRUE;
630: dbColumn.isTimeKey := TRUE;
631: colCampos(colCampos.count) := dbColumn;

Line 628: dbColumn.columnLength := BSC_METADATA_OPTIMIZER_PKG.ORA_DATA_PRECISION_BYTE;

624: --Type
625: dbColumn := New_clsdbColumn;
626: dbColumn.columnName := 'TYPE';
627: dbColumn.columnType := BSC_METADATA_OPTIMIZER_PKG.DTNumber;
628: dbColumn.columnLength := BSC_METADATA_OPTIMIZER_PKG.ORA_DATA_PRECISION_BYTE;
629: dbColumn.isKey := TRUE;
630: dbColumn.isTimeKey := TRUE;
631: colCampos(colCampos.count) := dbColumn;
632:

Line 636: dbColumn.columnType := BSC_METADATA_OPTIMIZER_PKG.DTNumber;

632:
633: --Period
634: dbColumn := New_clsdbColumn;
635: dbColumn.columnName := 'PERIOD';
636: dbColumn.columnType := BSC_METADATA_OPTIMIZER_PKG.DTNumber;
637: dbColumn.columnLength := BSC_METADATA_OPTIMIZER_PKG.ORA_DATA_PRECISION_INTEGER;
638: dbColumn.isKey := TRUE;
639: dbColumn.isTimeKey := TRUE;
640: colCampos(colCampos.count) := dbColumn;

Line 637: dbColumn.columnLength := BSC_METADATA_OPTIMIZER_PKG.ORA_DATA_PRECISION_INTEGER;

633: --Period
634: dbColumn := New_clsdbColumn;
635: dbColumn.columnName := 'PERIOD';
636: dbColumn.columnType := BSC_METADATA_OPTIMIZER_PKG.DTNumber;
637: dbColumn.columnLength := BSC_METADATA_OPTIMIZER_PKG.ORA_DATA_PRECISION_INTEGER;
638: dbColumn.isKey := TRUE;
639: dbColumn.isTimeKey := TRUE;
640: colCampos(colCampos.count) := dbColumn;
641:

Line 645: dbColumn.columnType := BSC_METADATA_OPTIMIZER_PKG.DTNumber;

641:
642: --Periodicity_Id
643: dbColumn := New_clsdbColumn;
644: dbColumn.columnName := 'PERIODICITY_ID';
645: dbColumn.columnType := BSC_METADATA_OPTIMIZER_PKG.DTNumber;
646: dbColumn.columnLength := 0;
647: dbColumn.isKey := TRUE;
648: dbColumn.isTimeKey := TRUE;
649: colCampos(colCampos.count) := dbColumn;

Line 657: dbColumn.columnType := BSC_METADATA_OPTIMIZER_PKG.DTNumber;

653: --Fix bug#3353111 In Projection Tables created FOR target at dIFferent levels,
654: -- PERIOD_TYPE_ID should be not null and should not included in the index
655: dbColumn := New_clsdbColumn;
656: dbColumn.columnName := 'PERIOD_TYPE_ID';
657: dbColumn.columnType := BSC_METADATA_OPTIMIZER_PKG.DTNumber;
658: dbColumn.columnLength := 0;
659: dbColumn.isKey := FALSE;
660: colCampos(colCampos.count) := dbColumn;
661:

Line 670: dbColumn.columnType := BSC_METADATA_OPTIMIZER_PKG.DTNumber;

666: EXIT WHEN Tabla_Data.count =0;
667: Dato := Tabla_Data(l_index);
668: dbColumn := New_clsdbColumn;
669: dbColumn.columnName := Dato.fieldName;
670: dbColumn.columnType := BSC_METADATA_OPTIMIZER_PKG.DTNumber;
671: dbColumn.columnLength := 0;
672: dbColumn.isKey := FALSE;
673: colCampos(colCampos.count) := dbColumn;
674: EXIT WHEN l_index = Tabla_Data.last;

Line 680: BSC_METADATA_OPTIMIZER_PKG.gSummaryTableTbsName,

676: END LOOP;
677: CreateTableInDB(
678: p_table.projectionTable,
679: colCampos,
680: BSC_METADATA_OPTIMIZER_PKG.gSummaryTableTbsName,
681: BSC_METADATA_OPTIMIZER_PKG.gSummaryIndexTbsName,
682: p_table,
683: 'PROJECTION');
684:

Line 681: BSC_METADATA_OPTIMIZER_PKG.gSummaryIndexTbsName,

677: CreateTableInDB(
678: p_table.projectionTable,
679: colCampos,
680: BSC_METADATA_OPTIMIZER_PKG.gSummaryTableTbsName,
681: BSC_METADATA_OPTIMIZER_PKG.gSummaryIndexTbsName,
682: p_table,
683: 'PROJECTION');
684:
685: EXCEPTION WHEN OTHERS THEN

Line 694: PROCEDURE AddPeriodicityId(BaseTable BSC_METADATA_OPTIMIZER_PKG.clsTable) IS

690:
691:
692:
693:
694: PROCEDURE AddPeriodicityId(BaseTable BSC_METADATA_OPTIMIZER_PKG.clsTable) IS
695: l_stmt VARCHAR2(32000);
696: IndexName VARCHAR2(1000);
697: IndexedColumns VARCHAR2(4000);
698: keyColumn BSC_METADATA_OPTIMIZER_PKG.clsKeyField;

Line 698: keyColumn BSC_METADATA_OPTIMIZER_PKG.clsKeyField;

694: PROCEDURE AddPeriodicityId(BaseTable BSC_METADATA_OPTIMIZER_PKG.clsTable) IS
695: l_stmt VARCHAR2(32000);
696: IndexName VARCHAR2(1000);
697: IndexedColumns VARCHAR2(4000);
698: keyColumn BSC_METADATA_OPTIMIZER_PKG.clsKeyField;
699: BaseTable_keys BSC_METADATA_OPTIMIZER_PKG.tab_clsKeyField;
700:
701: l_index NUMBER;
702: l_error VARCHAR2(1000);

Line 699: BaseTable_keys BSC_METADATA_OPTIMIZER_PKG.tab_clsKeyField;

695: l_stmt VARCHAR2(32000);
696: IndexName VARCHAR2(1000);
697: IndexedColumns VARCHAR2(4000);
698: keyColumn BSC_METADATA_OPTIMIZER_PKG.clsKeyField;
699: BaseTable_keys BSC_METADATA_OPTIMIZER_PKG.tab_clsKeyField;
700:
701: l_index NUMBER;
702: l_error VARCHAR2(1000);
703: l_zero_code_cols VARCHAR2(1000) := null;

Line 725: OPEN cDropIndexes(BaseTable.Name, BSC_METADATA_OPTIMIZER_PKG.gBSCSchema);

721: l_stmt := 'ALTER TABLE ' || BaseTable.Name || ' MODIFY PERIODICITY_ID NOT NULL';
722: BSC_MO_HELPER_PKG.Do_DDL(l_stmt, ad_ddl.alter_table, BaseTable.Name);
723:
724: --Drop existing indexes
725: OPEN cDropIndexes(BaseTable.Name, BSC_METADATA_OPTIMIZER_PKG.gBSCSchema);
726: LOOP
727: FETCH cDropIndexes INTO IndexName;
728: EXIT WHEN cDropIndexes%NOTFOUND;
729: --Drop index

Line 761: ' TABLESPACE '||BSC_METADATA_OPTIMIZER_PKG.gBaseIndexTbsName||' ' || BSC_METADATA_OPTIMIZER_PKG.gStorageClause;

757: null;
758: end;
759: l_stmt := 'CREATE UNIQUE INDEX '|| IndexName||
760: ' ON ' || BaseTable.Name || ' (' || IndexedColumns || ') '||
761: ' TABLESPACE '||BSC_METADATA_OPTIMIZER_PKG.gBaseIndexTbsName||' ' || BSC_METADATA_OPTIMIZER_PKG.gStorageClause;
762: bsc_mo_helper_pkg.Do_DDL (l_stmt, ad_ddl.create_index, IndexName);
763: END IF;
764: END IF;
765: -- Bug#:5214589

Line 799: PROCEDURE CreateDefMaterializedView(p_table IN BSC_METADATA_OPTIMIZER_PKG.clsTable) IS

795: --
796: -- PARAMETERS:
797: -- Tabla: clsTabla object with all info about the table
798: --****************************************************************************
799: PROCEDURE CreateDefMaterializedView(p_table IN BSC_METADATA_OPTIMIZER_PKG.clsTable) IS
800: l_stmt VARCHAR2(100);
801: cal_id NUMBER;
802: num_anos NUMBER;
803: num_anosant NUMBER;

Line 810: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN

806: END_year NUMBER;
807: l_index NUMBER;
808: l_error VARCHAR2(1000);
809: BEGIN
810: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
811: BSC_MO_HELPER_PKG.writeTmp('Inside CreateDefMaterializedView', FND_LOG.LEVEL_PROCEDURE);
812: END IF;
813: --Create the materialized view
814: l_index := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gPeriodicities, p_table.periodicity);

Line 814: l_index := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gPeriodicities, p_table.periodicity);

810: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
811: BSC_MO_HELPER_PKG.writeTmp('Inside CreateDefMaterializedView', FND_LOG.LEVEL_PROCEDURE);
812: END IF;
813: --Create the materialized view
814: l_index := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gPeriodicities, p_table.periodicity);
815: cal_id := BSC_METADATA_OPTIMIZER_PKG.gPeriodicities(l_index).CalENDarID;
816:
817: l_index := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gCalENDars, cal_id);
818: num_anos := BSC_METADATA_OPTIMIZER_PKG.gCalENDars(l_index).NumOfYears;

Line 815: cal_id := BSC_METADATA_OPTIMIZER_PKG.gPeriodicities(l_index).CalENDarID;

811: BSC_MO_HELPER_PKG.writeTmp('Inside CreateDefMaterializedView', FND_LOG.LEVEL_PROCEDURE);
812: END IF;
813: --Create the materialized view
814: l_index := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gPeriodicities, p_table.periodicity);
815: cal_id := BSC_METADATA_OPTIMIZER_PKG.gPeriodicities(l_index).CalENDarID;
816:
817: l_index := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gCalENDars, cal_id);
818: num_anos := BSC_METADATA_OPTIMIZER_PKG.gCalENDars(l_index).NumOfYears;
819: num_anosant := BSC_METADATA_OPTIMIZER_PKG.gCalENDars(l_index).PreviousYears;

Line 817: l_index := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gCalENDars, cal_id);

813: --Create the materialized view
814: l_index := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gPeriodicities, p_table.periodicity);
815: cal_id := BSC_METADATA_OPTIMIZER_PKG.gPeriodicities(l_index).CalENDarID;
816:
817: l_index := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gCalENDars, cal_id);
818: num_anos := BSC_METADATA_OPTIMIZER_PKG.gCalENDars(l_index).NumOfYears;
819: num_anosant := BSC_METADATA_OPTIMIZER_PKG.gCalENDars(l_index).PreviousYears;
820: fiscal_year := BSC_METADATA_OPTIMIZER_PKG.gCalENDars(l_index).CurrFiscalYear;
821:

Line 818: num_anos := BSC_METADATA_OPTIMIZER_PKG.gCalENDars(l_index).NumOfYears;

814: l_index := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gPeriodicities, p_table.periodicity);
815: cal_id := BSC_METADATA_OPTIMIZER_PKG.gPeriodicities(l_index).CalENDarID;
816:
817: l_index := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gCalENDars, cal_id);
818: num_anos := BSC_METADATA_OPTIMIZER_PKG.gCalENDars(l_index).NumOfYears;
819: num_anosant := BSC_METADATA_OPTIMIZER_PKG.gCalENDars(l_index).PreviousYears;
820: fiscal_year := BSC_METADATA_OPTIMIZER_PKG.gCalENDars(l_index).CurrFiscalYear;
821:
822: start_year := fiscal_year - num_anosant;

Line 819: num_anosant := BSC_METADATA_OPTIMIZER_PKG.gCalENDars(l_index).PreviousYears;

815: cal_id := BSC_METADATA_OPTIMIZER_PKG.gPeriodicities(l_index).CalENDarID;
816:
817: l_index := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gCalENDars, cal_id);
818: num_anos := BSC_METADATA_OPTIMIZER_PKG.gCalENDars(l_index).NumOfYears;
819: num_anosant := BSC_METADATA_OPTIMIZER_PKG.gCalENDars(l_index).PreviousYears;
820: fiscal_year := BSC_METADATA_OPTIMIZER_PKG.gCalENDars(l_index).CurrFiscalYear;
821:
822: start_year := fiscal_year - num_anosant;
823: END_year := start_year + num_anos - 1;

Line 820: fiscal_year := BSC_METADATA_OPTIMIZER_PKG.gCalENDars(l_index).CurrFiscalYear;

816:
817: l_index := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gCalENDars, cal_id);
818: num_anos := BSC_METADATA_OPTIMIZER_PKG.gCalENDars(l_index).NumOfYears;
819: num_anosant := BSC_METADATA_OPTIMIZER_PKG.gCalENDars(l_index).PreviousYears;
820: fiscal_year := BSC_METADATA_OPTIMIZER_PKG.gCalENDars(l_index).CurrFiscalYear;
821:
822: start_year := fiscal_year - num_anosant;
823: END_year := start_year + num_anos - 1;
824:

Line 829: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN

825: -- EDW code, obsoleted
826: --BSC_INTEGRATION_MV_GEN.Create_Def_Materialized_View(p_table.name, cal_id, start_year||'-'||END_year);
827: --BSC_MO_HELPER_PKG.CHeckError('BSC_INTEGRATION_MV_GEN.Create_Def_Materialized_View');
828:
829: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
830: BSC_MO_HELPER_PKG.writeTmp('Completed CreateDefMaterializedView FOR '||p_table.name, FND_LOG.LEVEL_PROCEDURE);
831: END IF;
832:
833: EXCEPTION WHEN OTHERS THEN

Line 943: AND UPPER(OWNER) = BSC_METADATA_OPTIMIZER_PKG.gBSCSchema;

939: CURSOR c1 (p1 VARCHAR2) IS
940: SELECT DATA_LENGTH FROM ALL_TAB_COLUMNS
941: WHERE UPPER(TABLE_NAME)= p1
942: AND UPPER(COLUMN_NAME) = 'USER_CODE'
943: AND UPPER(OWNER) = BSC_METADATA_OPTIMIZER_PKG.gBSCSchema;
944:
945: l_error VARCHAR2(1000);
946: BEGIN
947:

Line 948: l_temp := BSC_MO_HELPER_PKG.FindKeyIndex(BSC_METADATA_OPTIMIZER_PKG.gMastertable, Llave);

944:
945: l_error VARCHAR2(1000);
946: BEGIN
947:
948: l_temp := BSC_MO_HELPER_PKG.FindKeyIndex(BSC_METADATA_OPTIMIZER_PKG.gMastertable, Llave);
949: IF (l_temp=-1) THEN
950: Maestra := null;
951: RETURN 256;
952: ELSE

Line 953: Maestra := BSC_METADATA_OPTIMIZER_PKG.gMasterTable(l_temp).name;

949: IF (l_temp=-1) THEN
950: Maestra := null;
951: RETURN 256;
952: ELSE
953: Maestra := BSC_METADATA_OPTIMIZER_PKG.gMasterTable(l_temp).name;
954: END IF;
955:
956: --APPS
957: OPEN c1(Maestra);

Line 968: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN

964: RETURN l_temp;
965:
966: EXCEPTION WHEN OTHERS THEN
967: l_error := sqlerrm;
968: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
969: bsc_mo_helper_pkg.writeTmp('Exception in GetKeyLength:'||l_error);
970: END IF;
971: raise;
972: END;

Line 975: PROCEDURE clearColumn(col IN OUT NOCOPY BSC_METADATA_OPTIMIZER_PKG.clsDBColumn) IS

971: raise;
972: END;
973: */
974:
975: PROCEDURE clearColumn(col IN OUT NOCOPY BSC_METADATA_OPTIMIZER_PKG.clsDBColumn) IS
976: BEGIN
977: col.columnName := null;
978: col.columnType := null;
979: col.columnLength := null;

Line 997: return BSC_METADATA_OPTIMIZER_PKG.DTDate;

993: FETCH cPeriodicityType INTO l_periodicity;
994: CLOSE cPeriodicityType;
995:
996: IF (l_periodicity = 9) THEN -- daily
997: return BSC_METADATA_OPTIMIZER_PKG.DTDate;
998: ELSE
999: return BSC_METADATA_OPTIMIZER_PKG.DTVarchar2;
1000: END IF;
1001: END;

Line 999: return BSC_METADATA_OPTIMIZER_PKG.DTVarchar2;

995:
996: IF (l_periodicity = 9) THEN -- daily
997: return BSC_METADATA_OPTIMIZER_PKG.DTDate;
998: ELSE
999: return BSC_METADATA_OPTIMIZER_PKG.DTVarchar2;
1000: END IF;
1001: END;
1002:
1003:

Line 1005: colCampos IN OUT NOCOPY BSC_METADATA_OPTIMIZER_PKG.tab_clsDBColumn,

1001: END;
1002:
1003:
1004: PROCEDURE addYearPeriodType (
1005: colCampos IN OUT NOCOPY BSC_METADATA_OPTIMIZER_PKG.tab_clsDBColumn,
1006: periodicity IN NUMBER,
1007: addSubPeriod IN BOOLEAN,
1008: tableType IN NUMBER -- 0 for input table
1009: ) IS

Line 1011: dbColumn BSC_METADATA_OPTIMIZER_PKG.clsDBColumn;

1007: addSubPeriod IN BOOLEAN,
1008: tableType IN NUMBER -- 0 for input table
1009: ) IS
1010:
1011: dbColumn BSC_METADATA_OPTIMIZER_PKG.clsDBColumn;
1012: NomCampoPeriod VARCHAR2(100);
1013: NomCampoSubPeriod VARCHAR2(100);
1014: periodicitySource VARCHAR2(100);
1015: l_temp1 NUMBER;

Line 1020: l_temp1 := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gPeriodicities, periodicity);

1016: l_temp2 NUMBER;
1017:
1018: BEGIN
1019:
1020: l_temp1 := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gPeriodicities, periodicity);
1021: l_temp2 := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gCalendars, BSC_METADATA_OPTIMIZER_PKG.gPeriodicities(l_temp1).CalendarID);
1022: periodicitySource := BSC_METADATA_OPTIMIZER_PKG.gCalendars(l_temp2).Source;
1023:
1024: If periodicitySource = 'PMF' and tableType= 0 Then

Line 1021: l_temp2 := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gCalendars, BSC_METADATA_OPTIMIZER_PKG.gPeriodicities(l_temp1).CalendarID);

1017:
1018: BEGIN
1019:
1020: l_temp1 := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gPeriodicities, periodicity);
1021: l_temp2 := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gCalendars, BSC_METADATA_OPTIMIZER_PKG.gPeriodicities(l_temp1).CalendarID);
1022: periodicitySource := BSC_METADATA_OPTIMIZER_PKG.gCalendars(l_temp2).Source;
1023:
1024: If periodicitySource = 'PMF' and tableType= 0 Then
1025: --TIME_FK

Line 1022: periodicitySource := BSC_METADATA_OPTIMIZER_PKG.gCalendars(l_temp2).Source;

1018: BEGIN
1019:
1020: l_temp1 := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gPeriodicities, periodicity);
1021: l_temp2 := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gCalendars, BSC_METADATA_OPTIMIZER_PKG.gPeriodicities(l_temp1).CalendarID);
1022: periodicitySource := BSC_METADATA_OPTIMIZER_PKG.gCalendars(l_temp2).Source;
1023:
1024: If periodicitySource = 'PMF' and tableType= 0 Then
1025: --TIME_FK
1026: dbColumn.columnName := 'TIME_FK';

Line 1028: BSC_METADATA_OPTIMIZER_PKG.gPeriodicities(l_temp1).CalendarID,

1024: If periodicitySource = 'PMF' and tableType= 0 Then
1025: --TIME_FK
1026: dbColumn.columnName := 'TIME_FK';
1027: dbColumn.columnType := getDataTypeForTimeFK(
1028: BSC_METADATA_OPTIMIZER_PKG.gPeriodicities(l_temp1).CalendarID,
1029: periodicity);
1030: IF (dbColumn.columnType = BSC_METADATA_OPTIMIZER_PKG.DTVarchar2 ) THEN
1031: dbColumn.columnLength := 4000;
1032: ELSE

Line 1030: IF (dbColumn.columnType = BSC_METADATA_OPTIMIZER_PKG.DTVarchar2 ) THEN

1026: dbColumn.columnName := 'TIME_FK';
1027: dbColumn.columnType := getDataTypeForTimeFK(
1028: BSC_METADATA_OPTIMIZER_PKG.gPeriodicities(l_temp1).CalendarID,
1029: periodicity);
1030: IF (dbColumn.columnType = BSC_METADATA_OPTIMIZER_PKG.DTVarchar2 ) THEN
1031: dbColumn.columnLength := 4000;
1032: ELSE
1033: dbColumn.columnLength := 0;
1034: END IF;

Line 1039: dbColumn.columnType := BSC_METADATA_OPTIMIZER_PKG.DTNumber;

1035: dbColumn.isKey := TRUE;
1036: dbColumn.isTimeKey := TRUE;
1037: ELSE
1038: dbColumn.columnName := 'YEAR';
1039: dbColumn.columnType := BSC_METADATA_OPTIMIZER_PKG.DTNumber;
1040: dbColumn.columnLength := BSC_METADATA_OPTIMIZER_PKG.ORA_DATA_PRECISION_INTEGER;
1041: dbColumn.isKey := TRUE;
1042: dbColumn.isTimeKey := TRUE;
1043: END IF;

Line 1040: dbColumn.columnLength := BSC_METADATA_OPTIMIZER_PKG.ORA_DATA_PRECISION_INTEGER;

1036: dbColumn.isTimeKey := TRUE;
1037: ELSE
1038: dbColumn.columnName := 'YEAR';
1039: dbColumn.columnType := BSC_METADATA_OPTIMIZER_PKG.DTNumber;
1040: dbColumn.columnLength := BSC_METADATA_OPTIMIZER_PKG.ORA_DATA_PRECISION_INTEGER;
1041: dbColumn.isKey := TRUE;
1042: dbColumn.isTimeKey := TRUE;
1043: END IF;
1044:

Line 1055: dbColumn.columnType := BSC_METADATA_OPTIMIZER_PKG.DTNumber;

1051: --Type
1052: clearColumn(dbColumn);
1053: dbColumn := bsc_mo_helper_pkg.new_clsDBColumn;
1054: dbColumn.columnName := 'TYPE';
1055: dbColumn.columnType := BSC_METADATA_OPTIMIZER_PKG.DTNumber;
1056: dbColumn.columnLength := BSC_METADATA_OPTIMIZER_PKG.ORA_DATA_PRECISION_BYTE;
1057: dbColumn.isKey := TRUE;
1058: dbColumn.isTimeKey := TRUE;
1059:

Line 1056: dbColumn.columnLength := BSC_METADATA_OPTIMIZER_PKG.ORA_DATA_PRECISION_BYTE;

1052: clearColumn(dbColumn);
1053: dbColumn := bsc_mo_helper_pkg.new_clsDBColumn;
1054: dbColumn.columnName := 'TYPE';
1055: dbColumn.columnType := BSC_METADATA_OPTIMIZER_PKG.DTNumber;
1056: dbColumn.columnLength := BSC_METADATA_OPTIMIZER_PKG.ORA_DATA_PRECISION_BYTE;
1057: dbColumn.isKey := TRUE;
1058: dbColumn.isTimeKey := TRUE;
1059:
1060: colCampos(colCampos.last+1) := dbColumn;

Line 1069: dbColumn.columnType := BSC_METADATA_OPTIMIZER_PKG.DTNumber;

1065: --Period
1066: NomCampoPeriod := GetPeriodColumnName(Periodicity);
1067: clearColumn(dbColumn);
1068: dbColumn.columnName := NomCampoPeriod;
1069: dbColumn.columnType := BSC_METADATA_OPTIMIZER_PKG.DTNumber;
1070: dbColumn.columnLength := BSC_METADATA_OPTIMIZER_PKG.ORA_DATA_PRECISION_INTEGER;
1071: dbColumn.iskey := TRUE;
1072: dbColumn.isTimeKey := TRUE;
1073: colCampos(colCampos.last+1) := dbColumn;

Line 1070: dbColumn.columnLength := BSC_METADATA_OPTIMIZER_PKG.ORA_DATA_PRECISION_INTEGER;

1066: NomCampoPeriod := GetPeriodColumnName(Periodicity);
1067: clearColumn(dbColumn);
1068: dbColumn.columnName := NomCampoPeriod;
1069: dbColumn.columnType := BSC_METADATA_OPTIMIZER_PKG.DTNumber;
1070: dbColumn.columnLength := BSC_METADATA_OPTIMIZER_PKG.ORA_DATA_PRECISION_INTEGER;
1071: dbColumn.iskey := TRUE;
1072: dbColumn.isTimeKey := TRUE;
1073: colCampos(colCampos.last+1) := dbColumn;
1074:

Line 1081: dbColumn.columnType := BSC_METADATA_OPTIMIZER_PKG.DTNumber;

1077: NomCampoSubPeriod := GetSubperiodColumnName(Periodicity);
1078: IF NomCampoSubPeriod <> '' AND NomCampoSubPeriod IS NOT NULL THEN
1079: dbColumn := bsc_mo_helper_pkg.new_clsDBColumn;
1080: dbColumn.columnName := NomCampoSubPeriod;
1081: dbColumn.columnType := BSC_METADATA_OPTIMIZER_PKG.DTNumber;
1082: dbColumn.columnLength := BSC_METADATA_OPTIMIZER_PKG.ORA_DATA_PRECISION_INTEGER;
1083: dbColumn.isKey := TRUE;
1084: dbColumn.isTimeKey := TRUE;
1085: colCampos(colCampos.last+1) := dbColumn;

Line 1082: dbColumn.columnLength := BSC_METADATA_OPTIMIZER_PKG.ORA_DATA_PRECISION_INTEGER;

1078: IF NomCampoSubPeriod <> '' AND NomCampoSubPeriod IS NOT NULL THEN
1079: dbColumn := bsc_mo_helper_pkg.new_clsDBColumn;
1080: dbColumn.columnName := NomCampoSubPeriod;
1081: dbColumn.columnType := BSC_METADATA_OPTIMIZER_PKG.DTNumber;
1082: dbColumn.columnLength := BSC_METADATA_OPTIMIZER_PKG.ORA_DATA_PRECISION_INTEGER;
1083: dbColumn.isKey := TRUE;
1084: dbColumn.isTimeKey := TRUE;
1085: colCampos(colCampos.last+1) := dbColumn;
1086: END IF;

Line 1093: PROCEDURE add_columns_to_tables(p_Table BSC_METADATA_OPTIMIZER_PKG.clsTable)IS

1089:
1090: END;
1091:
1092:
1093: PROCEDURE add_columns_to_tables(p_Table BSC_METADATA_OPTIMIZER_PKG.clsTable)IS
1094: l_origin_tables DBMS_SQL.VARCHAR2_TABLE;
1095: l_stmt VARCHAR2(1000);
1096: l_table_type varchar2(10);
1097: l_prj_table varchar2(100);

Line 1112: IF BSC_METADATA_OPTIMIZER_PKG.g_BSC_mv AND l_table_type='T' THEN -- T tables do not exist in MV arch.

1108: --alter all tables in l_origin_tables as they need this new column, eg, T, B and I tables if p_table is a T table
1109: bsc_mo_helper_pkg.writeTmp('Measure '||p_table.data(i).fieldName||' needs to be added ', FND_LOG.LEVEL_STATEMENT, false);
1110: FOR j in l_origin_tables.first..l_origin_tables.last LOOP
1111: l_table_type := BSC_DBGEN_UTILS.get_table_type(l_origin_tables(j));
1112: IF BSC_METADATA_OPTIMIZER_PKG.g_BSC_mv AND l_table_type='T' THEN -- T tables do not exist in MV arch.
1113: null;
1114: ELSIF Not bsc_mo_helper_pkg.table_column_exists(l_origin_tables(j), p_table.data(i).fieldName) THEN
1115: l_stmt := 'ALTER TABLE ' || l_origin_tables(j) || ' ADD '||p_table.data(i).fieldName||' NUMBER';
1116: bsc_mo_helper_pkg.writeTmp(l_stmt, FND_LOG.LEVEL_STATEMENT, false);

Line 1130: l_stmt := 'ALTER MATERIALIZED VIEW LOG ON ' || BSC_METADATA_OPTIMIZER_PKG.gBSCSchema||'.'||l_origin_tables(j)

1126: bsc_mo_helper_pkg.writeTmp(l_stmt, FND_LOG.LEVEL_STATEMENT, false);
1127: BSC_MO_HELPER_PKG.Do_DDL(l_stmt, ad_ddl.alter_table, l_prj_table);
1128: END IF;
1129: end if;
1130: l_stmt := 'ALTER MATERIALIZED VIEW LOG ON ' || BSC_METADATA_OPTIMIZER_PKG.gBSCSchema||'.'||l_origin_tables(j)
1131: || ' ADD ('||p_table.data(i).fieldName||')';
1132: BEGIN
1133: execute immediate l_stmt;
1134: EXCEPTION WHEN OTHERS THEN

Line 1156: p_cols IN OUT NOCOPY BSC_METADATA_OPTIMIZER_PKG.tab_clsDBColumn)

1152: /*
1153: AddPartitionCols
1154: */
1155: PROCEDURE addPartitionCols(
1156: p_cols IN OUT NOCOPY BSC_METADATA_OPTIMIZER_PKG.tab_clsDBColumn)
1157: IS
1158: dbColumn BSC_METADATA_OPTIMIZER_PKG.clsDBColumn;
1159: dbColumn_null BSC_METADATA_OPTIMIZER_PKG.clsDBColumn := null;
1160:

Line 1158: dbColumn BSC_METADATA_OPTIMIZER_PKG.clsDBColumn;

1154: */
1155: PROCEDURE addPartitionCols(
1156: p_cols IN OUT NOCOPY BSC_METADATA_OPTIMIZER_PKG.tab_clsDBColumn)
1157: IS
1158: dbColumn BSC_METADATA_OPTIMIZER_PKG.clsDBColumn;
1159: dbColumn_null BSC_METADATA_OPTIMIZER_PKG.clsDBColumn := null;
1160:
1161: BEGIN
1162: -- batch column

Line 1159: dbColumn_null BSC_METADATA_OPTIMIZER_PKG.clsDBColumn := null;

1155: PROCEDURE addPartitionCols(
1156: p_cols IN OUT NOCOPY BSC_METADATA_OPTIMIZER_PKG.tab_clsDBColumn)
1157: IS
1158: dbColumn BSC_METADATA_OPTIMIZER_PKG.clsDBColumn;
1159: dbColumn_null BSC_METADATA_OPTIMIZER_PKG.clsDBColumn := null;
1160:
1161: BEGIN
1162: -- batch column
1163: dbColumn := dbColumn_null;

Line 1165: dbColumn.columnType := BSC_METADATA_OPTIMIZER_PKG.DTNumber;

1161: BEGIN
1162: -- batch column
1163: dbColumn := dbColumn_null;
1164: dbColumn.columnName := BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME;
1165: dbColumn.columnType := BSC_METADATA_OPTIMIZER_PKG.DTNumber;
1166: dbColumn.columnLength := 0;
1167: dbColumn.iskey := false;
1168: p_cols(p_cols.count) := dbColumn;
1169:

Line 1180: l_table_rec BSC_METADATA_OPTIMIZER_PKG.clsTable,

1176: -- PARAMETERS:
1177: -- Tabla: Table. It is an object with all inFORmation about the table
1178: --****************************************************************************
1179: PROCEDURE CreateOneTable(
1180: l_table_rec BSC_METADATA_OPTIMIZER_PKG.clsTable,
1181: TableTBSName IN VARCHAR2,
1182: IndexTBSName IN VARCHAR2,
1183: p_table_type IN VARCHAR2) IS
1184:

Line 1185: Llave BSC_METADATA_OPTIMIZER_PKG.clsKeyField;

1181: TableTBSName IN VARCHAR2,
1182: IndexTBSName IN VARCHAR2,
1183: p_table_type IN VARCHAR2) IS
1184:
1185: Llave BSC_METADATA_OPTIMIZER_PKG.clsKeyField;
1186: Dato BSC_METADATA_OPTIMIZER_PKG.clsDataField;
1187: colCampos BSC_METADATA_OPTIMIZER_PKG.tab_clsDBColumn;
1188: dbColumn BSC_METADATA_OPTIMIZER_PKG.clsDBColumn;
1189: msg VARCHAR2(100);

Line 1186: Dato BSC_METADATA_OPTIMIZER_PKG.clsDataField;

1182: IndexTBSName IN VARCHAR2,
1183: p_table_type IN VARCHAR2) IS
1184:
1185: Llave BSC_METADATA_OPTIMIZER_PKG.clsKeyField;
1186: Dato BSC_METADATA_OPTIMIZER_PKG.clsDataField;
1187: colCampos BSC_METADATA_OPTIMIZER_PKG.tab_clsDBColumn;
1188: dbColumn BSC_METADATA_OPTIMIZER_PKG.clsDBColumn;
1189: msg VARCHAR2(100);
1190: uv_name VARCHAR2(100);

Line 1187: colCampos BSC_METADATA_OPTIMIZER_PKG.tab_clsDBColumn;

1183: p_table_type IN VARCHAR2) IS
1184:
1185: Llave BSC_METADATA_OPTIMIZER_PKG.clsKeyField;
1186: Dato BSC_METADATA_OPTIMIZER_PKG.clsDataField;
1187: colCampos BSC_METADATA_OPTIMIZER_PKG.tab_clsDBColumn;
1188: dbColumn BSC_METADATA_OPTIMIZER_PKG.clsDBColumn;
1189: msg VARCHAR2(100);
1190: uv_name VARCHAR2(100);
1191: l_stmt VARCHAR2(1000);

Line 1188: dbColumn BSC_METADATA_OPTIMIZER_PKG.clsDBColumn;

1184:
1185: Llave BSC_METADATA_OPTIMIZER_PKG.clsKeyField;
1186: Dato BSC_METADATA_OPTIMIZER_PKG.clsDataField;
1187: colCampos BSC_METADATA_OPTIMIZER_PKG.tab_clsDBColumn;
1188: dbColumn BSC_METADATA_OPTIMIZER_PKG.clsDBColumn;
1189: msg VARCHAR2(100);
1190: uv_name VARCHAR2(100);
1191: l_stmt VARCHAR2(1000);
1192: l_index1 NUMBER;

Line 1194: l_keys BSC_METADATA_OPTIMIZER_PKG.tab_clsKeyField;

1190: uv_name VARCHAR2(100);
1191: l_stmt VARCHAR2(1000);
1192: l_index1 NUMBER;
1193: l_index2 NUMBER;
1194: l_keys BSC_METADATA_OPTIMIZER_PKG.tab_clsKeyField;
1195: l_data BSC_METADATA_OPTIMIZER_PKG.tab_clsDataField;
1196: l_error varchar2(1000);
1197: dimTable VARCHAR2(100);
1198: columnSize NUMBER;

Line 1195: l_data BSC_METADATA_OPTIMIZER_PKG.tab_clsDataField;

1191: l_stmt VARCHAR2(1000);
1192: l_index1 NUMBER;
1193: l_index2 NUMBER;
1194: l_keys BSC_METADATA_OPTIMIZER_PKG.tab_clsKeyField;
1195: l_data BSC_METADATA_OPTIMIZER_PKG.tab_clsDataField;
1196: l_error varchar2(1000);
1197: dimTable VARCHAR2(100);
1198: columnSize NUMBER;
1199: l_temp1 NUMBER;

Line 1217: l_temp1 := BSC_MO_HELPER_PKG.FindKeyIndex(BSC_METADATA_OPTIMIZER_PKG.gMasterTable, Llave.keyName);

1213: LOOP
1214: EXIT WHEN l_keys.count=0;
1215: dbColumn := bsc_mo_helper_pkg.new_clsDBColumn;
1216: Llave := l_keys(l_index1);
1217: l_temp1 := BSC_MO_HELPER_PKG.FindKeyIndex(BSC_METADATA_OPTIMIZER_PKG.gMasterTable, Llave.keyName);
1218: dimTable := BSC_METADATA_OPTIMIZER_PKG.gMasterTable(l_temp1).name;
1219: dbColumn.columnName := Llave.keyName;
1220: dbColumn.columnType := GetDimKeyDataType(dimTable, 'USER_CODE', columnSize);
1221: dbColumn.columnLength := columnSize;

Line 1218: dimTable := BSC_METADATA_OPTIMIZER_PKG.gMasterTable(l_temp1).name;

1214: EXIT WHEN l_keys.count=0;
1215: dbColumn := bsc_mo_helper_pkg.new_clsDBColumn;
1216: Llave := l_keys(l_index1);
1217: l_temp1 := BSC_MO_HELPER_PKG.FindKeyIndex(BSC_METADATA_OPTIMIZER_PKG.gMasterTable, Llave.keyName);
1218: dimTable := BSC_METADATA_OPTIMIZER_PKG.gMasterTable(l_temp1).name;
1219: dbColumn.columnName := Llave.keyName;
1220: dbColumn.columnType := GetDimKeyDataType(dimTable, 'USER_CODE', columnSize);
1221: dbColumn.columnLength := columnSize;
1222: dbColumn.isKey := TRUE;

Line 1245: dbColumn.columnType := BSC_METADATA_OPTIMIZER_PKG.DTNumber;

1241: EXIT WHEN l_data.count = 0;
1242: Dato := l_data (l_index1);
1243: dbColumn := bsc_mo_helper_pkg.new_clsDBColumn;
1244: dbColumn.columnName := Dato.fieldName;
1245: dbColumn.columnType := BSC_METADATA_OPTIMIZER_PKG.DTNumber;
1246: dbColumn.columnLength := 0;
1247: dbColumn.isKey := FALSE;
1248: colCampos(colCampos.last+1) := dbColumn;
1249: EXIT WHEN l_index1 = l_data.last;

Line 1273: l_temp1 := BSC_MO_HELPER_PKG.FindKeyIndex(BSC_METADATA_OPTIMIZER_PKG.gMasterTable, Llave.keyName);

1269: LOOP
1270: EXIT WHEN l_keys.count = 0;
1271: Llave := l_keys(l_index1);
1272: dbColumn := bsc_mo_helper_pkg.new_clsDBColumn;
1273: l_temp1 := BSC_MO_HELPER_PKG.FindKeyIndex(BSC_METADATA_OPTIMIZER_PKG.gMasterTable, Llave.keyName);
1274: IF (l_temp1 = -1) THEN
1275: bsc_mo_helper_pkg.writeTmp('l_temp1=-1 in FindKeyIndex for key = '||Llave.keyName);
1276: END IF;
1277: dimTable := BSC_METADATA_OPTIMIZER_PKG.gMasterTable(l_temp1).name;

Line 1277: dimTable := BSC_METADATA_OPTIMIZER_PKG.gMasterTable(l_temp1).name;

1273: l_temp1 := BSC_MO_HELPER_PKG.FindKeyIndex(BSC_METADATA_OPTIMIZER_PKG.gMasterTable, Llave.keyName);
1274: IF (l_temp1 = -1) THEN
1275: bsc_mo_helper_pkg.writeTmp('l_temp1=-1 in FindKeyIndex for key = '||Llave.keyName);
1276: END IF;
1277: dimTable := BSC_METADATA_OPTIMIZER_PKG.gMasterTable(l_temp1).name;
1278: dbColumn.columnName := Llave.keyName;
1279: dbColumn.columnTYpe := GetDimKeyDataType(dimTable, 'CODE', columnSize);
1280: dbColumn.columnLength := columnSize;
1281: dbColumn.isKey := TRUE;

Line 1291: IF BSC_METADATA_OPTIMIZER_PKG.g_BSC_MV and l_table_rec.impl_type <> 2 THEN

1287: AddYearPeriodType(colCampos, l_table_rec.periodicity, false, l_table_rec.type);
1288: --BSC-MV Note: Need new column: PERIODICITY_ID
1289: --Periodicity_id
1290: --BSC AW- B tables for AW dont require Periodicity id
1291: IF BSC_METADATA_OPTIMIZER_PKG.g_BSC_MV and l_table_rec.impl_type <> 2 THEN
1292: dbColumn := bsc_mo_helper_pkg.new_clsDBColumn;
1293: dbColumn.columnName := 'PERIODICITY_ID';
1294: dbColumn.columnType := BSC_METADATA_OPTIMIZER_PKG.DTNumber;
1295: dbColumn.columnLength := 0;

Line 1294: dbColumn.columnType := BSC_METADATA_OPTIMIZER_PKG.DTNumber;

1290: --BSC AW- B tables for AW dont require Periodicity id
1291: IF BSC_METADATA_OPTIMIZER_PKG.g_BSC_MV and l_table_rec.impl_type <> 2 THEN
1292: dbColumn := bsc_mo_helper_pkg.new_clsDBColumn;
1293: dbColumn.columnName := 'PERIODICITY_ID';
1294: dbColumn.columnType := BSC_METADATA_OPTIMIZER_PKG.DTNumber;
1295: dbColumn.columnLength := 0;
1296: dbColumn.isKey := TRUE;
1297: dbColumn.isTimeKey := TRUE;
1298: colCampos(colCampos.count) := dbColumn;

Line 1313: dbColumn.columnType := BSC_METADATA_OPTIMIZER_PKG.DTNumber;

1309: EXIT WHEN l_data.count = 0;
1310: Dato := l_data (l_index1);
1311: dbColumn := bsc_mo_helper_pkg.new_clsDBColumn;
1312: dbColumn.columnName := Dato.fieldName;
1313: dbColumn.columnType := BSC_METADATA_OPTIMIZER_PKG.DTNumber;
1314: dbColumn.columnLength := 0;
1315: dbColumn.isKey := FALSE;
1316: colCampos(colCampos.last+1) := dbColumn;
1317: EXIT WHEN l_index1 = l_data.last;

Line 1348: l_table_rec BSC_METADATA_OPTIMIZER_PKG.clsTable;

1344: -- DESCRIPTION:
1345: -- Create all system tables in the database.
1346: --****************************************************************************
1347: PROCEDURE CreateAllTables IS
1348: l_table_rec BSC_METADATA_OPTIMIZER_PKG.clsTable;
1349: l_index NUMBER;
1350: arrProjTables DBMS_SQL.VARCHAR2_TABLE;
1351: numProjTables NUMBER := 0;
1352: lTablesCount NUMBER := 0;

Line 1358: lTablesCount := BSC_METADATA_OPTIMIZER_PKG.gTables.count;

1354:
1355: l_varchar_table1 dbms_sql.varchar2_table;
1356: l_varchar_table2 dbms_sql.varchar2_table;
1357: BEGIN
1358: lTablesCount := BSC_METADATA_OPTIMIZER_PKG.gTables.count;
1359: IF (lTablesCount = 0 ) THEN
1360: RETURN;
1361: END IF;
1362:

Line 1380: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN

1376: END IF;
1377:
1378:
1379:
1380: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1381: BSC_MO_HELPER_PKG.writeTmp('Inside CreateAllTables');
1382: END IF;
1383: --BSC-MV Note: From this implementation only base tables are created
1384: --in the database. Also, FOR some special cases summary tables are created

Line 1387: l_index := BSC_METADATA_OPTIMIZER_PKG.gTables.first;

1383: --BSC-MV Note: From this implementation only base tables are created
1384: --in the database. Also, FOR some special cases summary tables are created
1385: --(Example FOR projection in kpi tables because of targets at dIFferent levels)
1386: --Also in this procedure we handle the case when it is running FOR upgrade
1387: l_index := BSC_METADATA_OPTIMIZER_PKG.gTables.first;
1388:
1389: LOOP
1390: l_counter := l_counter + 1;
1391: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN

Line 1391: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN

1387: l_index := BSC_METADATA_OPTIMIZER_PKG.gTables.first;
1388:
1389: LOOP
1390: l_counter := l_counter + 1;
1391: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1392: BSC_MO_HELPER_PKG.writeTmp('Table # :'||l_counter||' out of :'||BSC_METADATA_OPTIMIZER_PKG.gTables.count
1393: ||'... '||BSC_METADATA_OPTIMIZER_PKG.gTables(l_index).name||
1394: ' time is '||to_Char(sysdate, 'hh24:mi:ss'));
1395: END IF;

Line 1392: BSC_MO_HELPER_PKG.writeTmp('Table # :'||l_counter||' out of :'||BSC_METADATA_OPTIMIZER_PKG.gTables.count

1388:
1389: LOOP
1390: l_counter := l_counter + 1;
1391: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1392: BSC_MO_HELPER_PKG.writeTmp('Table # :'||l_counter||' out of :'||BSC_METADATA_OPTIMIZER_PKG.gTables.count
1393: ||'... '||BSC_METADATA_OPTIMIZER_PKG.gTables(l_index).name||
1394: ' time is '||to_Char(sysdate, 'hh24:mi:ss'));
1395: END IF;
1396: l_table_rec := BSC_METADATA_OPTIMIZER_PKG.gTables(l_index);

Line 1393: ||'... '||BSC_METADATA_OPTIMIZER_PKG.gTables(l_index).name||

1389: LOOP
1390: l_counter := l_counter + 1;
1391: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1392: BSC_MO_HELPER_PKG.writeTmp('Table # :'||l_counter||' out of :'||BSC_METADATA_OPTIMIZER_PKG.gTables.count
1393: ||'... '||BSC_METADATA_OPTIMIZER_PKG.gTables(l_index).name||
1394: ' time is '||to_Char(sysdate, 'hh24:mi:ss'));
1395: END IF;
1396: l_table_rec := BSC_METADATA_OPTIMIZER_PKG.gTables(l_index);
1397: IF (l_table_rec.isProductionTable AND l_table_rec.isProductionTableAltered) THEN

Line 1396: l_table_rec := BSC_METADATA_OPTIMIZER_PKG.gTables(l_index);

1392: BSC_MO_HELPER_PKG.writeTmp('Table # :'||l_counter||' out of :'||BSC_METADATA_OPTIMIZER_PKG.gTables.count
1393: ||'... '||BSC_METADATA_OPTIMIZER_PKG.gTables(l_index).name||
1394: ' time is '||to_Char(sysdate, 'hh24:mi:ss'));
1395: END IF;
1396: l_table_rec := BSC_METADATA_OPTIMIZER_PKG.gTables(l_index);
1397: IF (l_table_rec.isProductionTable AND l_table_rec.isProductionTableAltered) THEN
1398: add_columns_to_tables(l_table_rec);
1399: END IF;
1400:

Line 1401: IF l_table_rec.isProductionTable AND BSC_METADATA_OPTIMIZER_PKG.g_Sum_Level_Change <> 1 And l_table_rec.upgradeFlag <> 1 THEN

1397: IF (l_table_rec.isProductionTable AND l_table_rec.isProductionTableAltered) THEN
1398: add_columns_to_tables(l_table_rec);
1399: END IF;
1400:
1401: IF l_table_rec.isProductionTable AND BSC_METADATA_OPTIMIZER_PKG.g_Sum_Level_Change <> 1 And l_table_rec.upgradeFlag <> 1 THEN
1402: null;
1403: ELSIF l_table_rec.TYPE = 0 THEN
1404: --Input table
1405: IF BSC_METADATA_OPTIMIZER_PKG.g_Sum_Level_Change = 1 And l_table_rec.upgradeFlag = 1 THEN

Line 1405: IF BSC_METADATA_OPTIMIZER_PKG.g_Sum_Level_Change = 1 And l_table_rec.upgradeFlag = 1 THEN

1401: IF l_table_rec.isProductionTable AND BSC_METADATA_OPTIMIZER_PKG.g_Sum_Level_Change <> 1 And l_table_rec.upgradeFlag <> 1 THEN
1402: null;
1403: ELSIF l_table_rec.TYPE = 0 THEN
1404: --Input table
1405: IF BSC_METADATA_OPTIMIZER_PKG.g_Sum_Level_Change = 1 And l_table_rec.upgradeFlag = 1 THEN
1406: --In upgrade mode, the input table already exist, we cannot drop/create
1407: NULL;
1408: ELSE
1409: BSC_MO_HELPER_PKG.writeTmp('Calling CreateOneTable1 for '||l_table_rec.name);

Line 1410: CreateOneTable(l_table_rec, BSC_METADATA_OPTIMIZER_PKG.gInputTableTbsName, BSC_METADATA_OPTIMIZER_PKG.gInputIndexTbsName, 'I');

1406: --In upgrade mode, the input table already exist, we cannot drop/create
1407: NULL;
1408: ELSE
1409: BSC_MO_HELPER_PKG.writeTmp('Calling CreateOneTable1 for '||l_table_rec.name);
1410: CreateOneTable(l_table_rec, BSC_METADATA_OPTIMIZER_PKG.gInputTableTbsName, BSC_METADATA_OPTIMIZER_PKG.gInputIndexTbsName, 'I');
1411: END IF;
1412: ELSIF BSC_MO_LOADER_CONFIG_PKG.isBasicTable(l_table_rec.name) THEN
1413: --Base table
1414: IF BSC_METADATA_OPTIMIZER_PKG.g_Sum_Level_Change = 1 and l_table_rec.isProductionTable THEN

Line 1414: IF BSC_METADATA_OPTIMIZER_PKG.g_Sum_Level_Change = 1 and l_table_rec.isProductionTable THEN

1410: CreateOneTable(l_table_rec, BSC_METADATA_OPTIMIZER_PKG.gInputTableTbsName, BSC_METADATA_OPTIMIZER_PKG.gInputIndexTbsName, 'I');
1411: END IF;
1412: ELSIF BSC_MO_LOADER_CONFIG_PKG.isBasicTable(l_table_rec.name) THEN
1413: --Base table
1414: IF BSC_METADATA_OPTIMIZER_PKG.g_Sum_Level_Change = 1 and l_table_rec.isProductionTable THEN
1415: --In upgrade mode, we only need to add periodicity id to the table/index
1416: BSC_MO_HELPER_PKG.writeTmp('Calling AddPeriodicityId2 for '||l_table_rec.name);
1417: AddPeriodicityId (l_table_rec);
1418: ELSE

Line 1419: CreateOneTable(l_table_rec, BSC_METADATA_OPTIMIZER_PKG.gBaseTableTbsName, BSC_METADATA_OPTIMIZER_PKG.gBaseIndexTbsName, 'B');

1415: --In upgrade mode, we only need to add periodicity id to the table/index
1416: BSC_MO_HELPER_PKG.writeTmp('Calling AddPeriodicityId2 for '||l_table_rec.name);
1417: AddPeriodicityId (l_table_rec);
1418: ELSE
1419: CreateOneTable(l_table_rec, BSC_METADATA_OPTIMIZER_PKG.gBaseTableTbsName, BSC_METADATA_OPTIMIZER_PKG.gBaseIndexTbsName, 'B');
1420: BSC_MO_HELPER_PKG.writeTmp('Calling CreateOneTable3 for '||l_table_rec.name);
1421: END IF;
1422: ELSE
1423: --Summary table

Line 1424: IF Not BSC_METADATA_OPTIMIZER_PKG.g_BSC_MV THEN

1420: BSC_MO_HELPER_PKG.writeTmp('Calling CreateOneTable3 for '||l_table_rec.name);
1421: END IF;
1422: ELSE
1423: --Summary table
1424: IF Not BSC_METADATA_OPTIMIZER_PKG.g_BSC_MV THEN
1425: BSC_MO_HELPER_PKG.writeTmp('Calling CreateOneTable4 for '||l_table_rec.name);
1426: CreateOneTable(l_table_rec, BSC_METADATA_OPTIMIZER_PKG.gSummaryTableTbsName, BSC_METADATA_OPTIMIZER_PKG.gSummaryIndexTbsName, 'S');
1427: ELSE
1428: BSC_MO_HELPER_PKG.writeTmp('MV architecture, Not Calling CreateOneTable5 for '||l_table_rec.name);

Line 1426: CreateOneTable(l_table_rec, BSC_METADATA_OPTIMIZER_PKG.gSummaryTableTbsName, BSC_METADATA_OPTIMIZER_PKG.gSummaryIndexTbsName, 'S');

1422: ELSE
1423: --Summary table
1424: IF Not BSC_METADATA_OPTIMIZER_PKG.g_BSC_MV THEN
1425: BSC_MO_HELPER_PKG.writeTmp('Calling CreateOneTable4 for '||l_table_rec.name);
1426: CreateOneTable(l_table_rec, BSC_METADATA_OPTIMIZER_PKG.gSummaryTableTbsName, BSC_METADATA_OPTIMIZER_PKG.gSummaryIndexTbsName, 'S');
1427: ELSE
1428: BSC_MO_HELPER_PKG.writeTmp('MV architecture, Not Calling CreateOneTable5 for '||l_table_rec.name);
1429: --BSC-MV New architecture: None of the summary tables are needed.
1430: --The only tables we need to create are the tables created FOR projection

Line 1441: IF BSC_METADATA_OPTIMIZER_PKG.g_Sum_Level_Change = 1 And l_table_rec.upgradeFlag = 1 THEN

1437: numProjTables := numProjTables + 1;
1438: END IF;
1439: END IF;
1440:
1441: IF BSC_METADATA_OPTIMIZER_PKG.g_Sum_Level_Change = 1 And l_table_rec.upgradeFlag = 1 THEN
1442: --In upgrade mode we need to drop the summary tables used
1443: --by production indicators
1444: BSC_MO_HELPER_PKG.dropTable(l_table_rec.Name);
1445: END IF;

Line 1448: EXIT WHEN l_index = BSC_METADATA_OPTIMIZER_PKG.gTables.last;

1444: BSC_MO_HELPER_PKG.dropTable(l_table_rec.Name);
1445: END IF;
1446: END IF;
1447: END IF;
1448: EXIT WHEN l_index = BSC_METADATA_OPTIMIZER_PKG.gTables.last;
1449: l_index := BSC_METADATA_OPTIMIZER_PKG.gTables.next(l_index);
1450: END LOOP;
1451: commit;
1452:

Line 1449: l_index := BSC_METADATA_OPTIMIZER_PKG.gTables.next(l_index);

1445: END IF;
1446: END IF;
1447: END IF;
1448: EXIT WHEN l_index = BSC_METADATA_OPTIMIZER_PKG.gTables.last;
1449: l_index := BSC_METADATA_OPTIMIZER_PKG.gTables.next(l_index);
1450: END LOOP;
1451: commit;
1452:
1453: IF (g_parallelize) THEN -- tables need to be created by child processes

Line 1469: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN

1465: UPDATE BSC_DB_TABLES
1466: SET PROPERTIES=PROPERTIES||l_varchar_table2(i)||BSC_DBGEN_STD_METADATA.BSC_PROPERTY_SEPARATOR
1467: WHERE table_name = l_varchar_table1(i);
1468:
1469: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1470: BSC_MO_HELPER_PKG.writeTmp('Completed CreateAllTables');
1471: END IF;
1472: END ;
1473:

Line 1495: if bsc_metadata_optimizer_pkg.g_debug then

1491: else
1492: l_stmt:='select id,job_id,status,message from '||p_job_status_table||' where object_name=:1';
1493: end if;
1494: p_number_jobs:=1;
1495: if bsc_metadata_optimizer_pkg.g_debug then
1496: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1497: bsc_mo_helper_pkg.writeTmp(l_stmt||' '||p_object_name);
1498: END IF;
1499: end if;

Line 1496: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN

1492: l_stmt:='select id,job_id,status,message from '||p_job_status_table||' where object_name=:1';
1493: end if;
1494: p_number_jobs:=1;
1495: if bsc_metadata_optimizer_pkg.g_debug then
1496: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1497: bsc_mo_helper_pkg.writeTmp(l_stmt||' '||p_object_name);
1498: END IF;
1499: end if;
1500: if p_object_name is null then

Line 1513: if bsc_metadata_optimizer_pkg.g_debug then

1509: p_number_jobs:=p_number_jobs+1;
1510: end loop;
1511: close cv;
1512: p_number_jobs:=p_number_jobs-1;
1513: if bsc_metadata_optimizer_pkg.g_debug then
1514: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1515: bsc_mo_helper_pkg.writeTmp('The job status');
1516: END IF;
1517: for i in 1..p_number_jobs loop

Line 1514: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN

1510: end loop;
1511: close cv;
1512: p_number_jobs:=p_number_jobs-1;
1513: if bsc_metadata_optimizer_pkg.g_debug then
1514: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1515: bsc_mo_helper_pkg.writeTmp('The job status');
1516: END IF;
1517: for i in 1..p_number_jobs loop
1518: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN

Line 1518: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN

1514: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1515: bsc_mo_helper_pkg.writeTmp('The job status');
1516: END IF;
1517: for i in 1..p_number_jobs loop
1518: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1519: bsc_mo_helper_pkg.writeTmp(p_id(i)||' '||p_job_id(i)||' '||p_status(i)||' '||p_message(i));
1520: END IF;
1521: end loop;
1522: end if;

Line 1543: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN

1539: l_status dbms_sql.varchar2_table;
1540: l_message dbms_sql.varchar2_table;
1541: l_number_jobs number;
1542: Begin
1543: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1544: bsc_mo_helper_pkg.writeTmp('Starting check_all_child_jobs');
1545: END IF;
1546:
1547: if get_child_job_status(

Line 1698: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN

1694: l_start date := sysdate;
1695: Begin
1696:
1697: IF p_number_jobs<=0 THEN
1698: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1699: bsc_mo_helper_pkg.writeTmp('Done wait_on_jobs, zero count');
1700: END IF;
1701: RETURN TRUE;
1702: END IF;

Line 1703: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN

1699: bsc_mo_helper_pkg.writeTmp('Done wait_on_jobs, zero count');
1700: END IF;
1701: RETURN TRUE;
1702: END IF;
1703: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1704: bsc_mo_helper_pkg.writeTmp('Inside wait_on_jobs, jobs are ');
1705: END IF;
1706: bsc_mo_helper_pkg.write_this(p_job_id);
1707: FOR i in 1..p_job_id.count LOOP

Line 1726: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN

1722: l_status:=check_conc_process_status(p_job_id(i));
1723: END IF;
1724: bsc_mo_helper_pkg.writeTmp('status returned for '||p_job_id(i)||':'||l_status||' l_job_status='||l_job_status(i));
1725: IF l_status is null THEN
1726: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1727: bsc_mo_helper_pkg.writeTmp('Compl wait_on_jobs, returning false');
1728: END IF;
1729: RETURN FALSE;
1730: ELSIF l_status='Y' THEN

Line 1734: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN

1730: ELSIF l_status='Y' THEN
1731: l_found:=TRUE;
1732: ELSE
1733: IF l_job_status(i)='Y' THEN
1734: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1735: bsc_mo_helper_pkg.writeTmp('Job '||p_job_id(i)||' has terminated '||to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
1736: END IF;
1737: l_job_status(i):='N';
1738: ELSE -- error

Line 1740: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN

1736: END IF;
1737: l_job_status(i):='N';
1738: ELSE -- error
1739: l_job_status(i) := 'ERROR';
1740: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1741: bsc_mo_helper_pkg.writeTmp('Compl wait_on_jobs, returning false');
1742: END IF;
1743: return false;
1744: END IF;

Line 1753: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN

1749: exit;
1750: END IF;
1751:
1752: END LOOP;
1753: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1754: bsc_mo_helper_pkg.writeTmp('Compl wait_on_jobs, returning true');
1755: END IF;
1756: RETURN TRUE;
1757: Exception when others THEN

Line 1788: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN

1784: g_parallelize := false;
1785: return false;
1786: END IF;
1787: CLOSE cParam;
1788: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1789: bsc_mo_helper_pkg.writeTmp('Done with check_ora_job_parameters, returning true');
1790: END IF;
1791: return true;
1792: EXCEPTION WHEN OTHERS THEN

Line 1823: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN

1819: */
1820:
1821: g_sleep_time := 5;-- check every 5 seconds
1822:
1823: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1824: bsc_mo_helper_pkg.writeTmp('Inside spawn_child_processes, system time is '||to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
1825: END IF;
1826:
1827: -- INsert into Database

Line 1836: bsc_metadata_optimizer_pkg.g_debug := true;

1832: USING g_ddl_bucket_id(i), g_ddl_object(i), g_ddl_object_ddl(i), g_ddl_object_type(i);
1833:
1834: commit;
1835:
1836: bsc_metadata_optimizer_pkg.g_debug := true;
1837: bsc_metadata_optimizer_pkg.gThreadType := 'JOB';
1838:
1839: IF (fnd_global.CONC_REQUEST_ID <> -1) THEN
1840: bsc_metadata_optimizer_pkg.gThreadType := 'CONC';

Line 1837: bsc_metadata_optimizer_pkg.gThreadType := 'JOB';

1833:
1834: commit;
1835:
1836: bsc_metadata_optimizer_pkg.g_debug := true;
1837: bsc_metadata_optimizer_pkg.gThreadType := 'JOB';
1838:
1839: IF (fnd_global.CONC_REQUEST_ID <> -1) THEN
1840: bsc_metadata_optimizer_pkg.gThreadType := 'CONC';
1841: l_bsc_short_name:='BSC';

Line 1840: bsc_metadata_optimizer_pkg.gThreadType := 'CONC';

1836: bsc_metadata_optimizer_pkg.g_debug := true;
1837: bsc_metadata_optimizer_pkg.gThreadType := 'JOB';
1838:
1839: IF (fnd_global.CONC_REQUEST_ID <> -1) THEN
1840: bsc_metadata_optimizer_pkg.gThreadType := 'CONC';
1841: l_bsc_short_name:='BSC';
1842: ELSE
1843: IF (check_ora_job_parameters = false) THEN
1844: l_try_serial := true;

Line 1849: ' Type of thread='||bsc_metadata_optimizer_pkg.gThreadType);

1845: END IF;
1846: END IF;
1847:
1848: bsc_mo_helper_pkg.writeTmp(
1849: ' Type of thread='||bsc_metadata_optimizer_pkg.gThreadType);
1850:
1851: IF l_try_serial THEN
1852: bsc_mo_helper_pkg.writeTmp('Single thread ');
1853: ELSE

Line 1863: IF bsc_metadata_optimizer_pkg.gThreadType='CONC' THEN

1859: FOR bucket_num in 1..g_buckets LOOP
1860:
1861: l_job_id(bucket_num):=null;
1862: begin
1863: IF bsc_metadata_optimizer_pkg.gThreadType='CONC' THEN
1864: l_job_id(bucket_num):=FND_REQUEST.SUBMIT_REQUEST(
1865: application=>l_bsc_short_name,
1866: program=>g_conc_short_name,
1867: argument1=>bucket_num,

Line 1894: IF bsc_metadata_optimizer_pkg.g_debug THEN

1890: l_try_serial:=TRUE;
1891: END;
1892:
1893: IF l_try_serial THEN
1894: IF bsc_metadata_optimizer_pkg.g_debug THEN
1895: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1896: bsc_mo_helper_pkg.writeTmp('Attempt serial load');
1897: END IF;
1898: END IF;

Line 1895: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN

1891: END;
1892:
1893: IF l_try_serial THEN
1894: IF bsc_metadata_optimizer_pkg.g_debug THEN
1895: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1896: bsc_mo_helper_pkg.writeTmp('Attempt serial load');
1897: END IF;
1898: END IF;
1899: create_tables_spawned(bucket_num); -- serial

Line 1910: bsc_metadata_optimizer_pkg.gThreadType)=FALSE THEN -- error

1906: IF wait_on_jobs(
1907: l_job_id,
1908: g_buckets,
1909: l_sleep_time,
1910: bsc_metadata_optimizer_pkg.gThreadType)=FALSE THEN -- error
1911: l_status:=false;
1912: bsc_mo_helper_pkg.TerminateWithMsg('One or more spawned programs failed');
1913: raise bsc_metadata_optimizer_pkg.optimizer_exception;
1914: END IF;

Line 1913: raise bsc_metadata_optimizer_pkg.optimizer_exception;

1909: l_sleep_time,
1910: bsc_metadata_optimizer_pkg.gThreadType)=FALSE THEN -- error
1911: l_status:=false;
1912: bsc_mo_helper_pkg.TerminateWithMsg('One or more spawned programs failed');
1913: raise bsc_metadata_optimizer_pkg.optimizer_exception;
1914: END IF;
1915:
1916: /*IF l_status THEN
1917: --just to note. l_job_id is not used in check_all_child_jobs

Line 1929: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN

1925: */
1926: END IF;
1927: bsc_mo_helper_pkg.do_ddl('drop table '||g_ddl_table_name, ad_ddl.drop_table, g_ddl_table_name);
1928:
1929: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1930: bsc_mo_helper_pkg.writeTmp('Compl spawn_child_processes, system time is '||to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
1931: END IF;
1932:
1933: EXCEPTION WHEN OTHERS THEN