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_MO_HELPER_PKG

Line 38: IF bsc_mo_helper_pkg.tableexists(g_ddl_table_name)=false THEN

34:
35: l_index VARCHAR2(1000) := 'CREATE UNIQUE INDEX '||g_ddl_table_name||'_U1 ON '||g_ddl_table_name||' (bucket_id, object_name)';
36: l_error VARCHAR2(1000);
37: BEGIN
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;

Line 39: bsc_mo_helper_pkg.do_ddl(l_stmt, ad_ddl.create_table, g_ddl_table_name);

35: l_index VARCHAR2(1000) := 'CREATE UNIQUE INDEX '||g_ddl_table_name||'_U1 ON '||g_ddl_table_name||' (bucket_id, object_name)';
36: l_error VARCHAR2(1000);
37: BEGIN
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;

Line 40: bsc_mo_helper_pkg.do_ddl(l_index, ad_ddl.create_index, g_ddl_table_name||'_U1');

36: l_error VARCHAR2(1000);
37: BEGIN
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:

Line 47: BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in CreateDDLTable:'||l_error);

43: END IF;
44:
45: EXCEPTION WHEN OTHERS THEN
46: l_error := sqlerrm;
47: BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in CreateDDLTable:'||l_error);
48: raise;
49: END;
50:
51: PROCEDURE InsertDDL(pObjectName IN VARCHAR2,

Line 62: bsc_mo_helper_pkg.writetmp('Incrementing bucket id to '||g_bucket_id||', # Tables processed='||g_tables_processed||', g_bucket_size='||g_bucket_size);

58: IF (pObjectTYPE = 'TABLE') THEN
59: IF (mod(g_tables_processed, g_bucket_size) = 0 AND g_tables_processed <> 0 ) THEN
60: g_bucket_id := g_bucket_id + 1;
61: g_buckets := g_bucket_id;
62: bsc_mo_helper_pkg.writetmp('Incrementing bucket id to '||g_bucket_id||', # Tables processed='||g_tables_processed||', g_bucket_size='||g_bucket_size);
63: END IF;
64: END IF;
65: g_ddl_bucket_id(g_tables_processed) := g_bucket_id ;
66: g_ddl_object(g_tables_processed) := pObjectName;

Line 73: BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in InsertDDL:'||l_error);

69: g_tables_processed := g_tables_processed + 1;
70:
71: EXCEPTION WHEN OTHERS THEN
72: l_error := sqlerrm;
73: BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in InsertDDL:'||l_error);
74: raise;
75: END;
76:
77: --****************************************************************************

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 141: bsc_mo_helper_pkg.writetmp('l_temp1 ='||l_temp1);

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;
145: If BSC_METADATA_OPTIMIZER_PKG.gMasterTable(l_temp1).Source = 'PMF' Then

Line 142: bsc_mo_helper_pkg.writetmp('dimTable = '||dimTable||', column = '||columnName||', source ='||

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;
145: If BSC_METADATA_OPTIMIZER_PKG.gMasterTable(l_temp1).Source = 'PMF' Then
146: --The dimension table is a View

Line 157: bsc_mo_helper_pkg.writeTmp('View '||dimTable||' is invalid', FND_LOG.LEVEL_EXCEPTION);

153: CLOSE cAllTabColumns;
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;

Line 173: BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in GetDimKeyDataType, dimTable='||dimTable|| ', columnName='||

169: return l_type;
170: End If;
171:
172: EXCEPTION WHEN OTHERS THEN
173: BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in GetDimKeyDataType, dimTable='||dimTable|| ', columnName='||
174: columnName||' :'||sqlerrm);
175: raise;
176: End;
177:

Line 258: bsc_mo_helper_pkg.writeTmp('added AW column(s) to table');

254: dbColumn.columnLength := 0;
255: dbColumn.isKey := false;
256: colCampos(colCampos.count) := dbColumn;
257:
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,

Line 293: bsc_mo_helper_pkg.writeTmp('get_col_list, p_primary_key_sql ='||p_primary_key_sql);

289: EXIT WHEN l_index1 = colColumns.last;
290: l_index1 := colColumns.next(l_index1);
291: END LOOP;
292: p_column_sql := substr(p_column_sql, 1, length(p_column_sql)-1);
293: bsc_mo_helper_pkg.writeTmp('get_col_list, p_primary_key_sql ='||p_primary_key_sql);
294: p_primary_key_sql := substr(p_primary_key_sql, 1, length(p_primary_key_sql)-1);
295: bsc_mo_helper_pkg.writeTmp('get_col_list, p_primary_key_sql ='||p_primary_key_sql);
296: EXCEPTION WHEN OTHERS THEN
297: BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in get_column_list:'||sqlerrm);

Line 295: bsc_mo_helper_pkg.writeTmp('get_col_list, p_primary_key_sql ='||p_primary_key_sql);

291: END LOOP;
292: p_column_sql := substr(p_column_sql, 1, length(p_column_sql)-1);
293: bsc_mo_helper_pkg.writeTmp('get_col_list, p_primary_key_sql ='||p_primary_key_sql);
294: p_primary_key_sql := substr(p_primary_key_sql, 1, length(p_primary_key_sql)-1);
295: bsc_mo_helper_pkg.writeTmp('get_col_list, p_primary_key_sql ='||p_primary_key_sql);
296: EXCEPTION WHEN OTHERS THEN
297: BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in get_column_list:'||sqlerrm);
298: raise;
299: END;

Line 297: BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in get_column_list:'||sqlerrm);

293: bsc_mo_helper_pkg.writeTmp('get_col_list, p_primary_key_sql ='||p_primary_key_sql);
294: p_primary_key_sql := substr(p_primary_key_sql, 1, length(p_primary_key_sql)-1);
295: bsc_mo_helper_pkg.writeTmp('get_col_list, p_primary_key_sql ='||p_primary_key_sql);
296: EXCEPTION WHEN OTHERS THEN
297: BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in get_column_list:'||sqlerrm);
298: raise;
299: END;
300:
301: PROCEDURE handle_ddl(p_object_name varchar2, p_object_type varchar2, p_ddl varchar2) IS

Line 309: BSC_MO_HELPER_PKG.do_ddl('drop index '||p_object_name, ad_ddl.drop_index, p_object_name);

305: return;
306: END IF;
307: IF (p_object_type = 'INDEX') THEN
308: begin
309: BSC_MO_HELPER_PKG.do_ddl('drop index '||p_object_name, ad_ddl.drop_index, p_object_name);
310: exception when others then
311: null;
312: end;
313: BSC_MO_HELPER_PKG.Do_DDL (p_ddl, ad_ddl.create_index, p_object_name);

Line 313: BSC_MO_HELPER_PKG.Do_DDL (p_ddl, ad_ddl.create_index, p_object_name);

309: BSC_MO_HELPER_PKG.do_ddl('drop index '||p_object_name, ad_ddl.drop_index, p_object_name);
310: exception when others then
311: null;
312: end;
313: BSC_MO_HELPER_PKG.Do_DDL (p_ddl, ad_ddl.create_index, p_object_name);
314: ELSIF p_object_type='TABLE' THEN
315: BEGIN
316: BSC_MO_HELPER_PKG.Do_DDL (p_ddl, ad_ddl.create_table, p_object_name);
317: exception when others then

Line 316: BSC_MO_HELPER_PKG.Do_DDL (p_ddl, ad_ddl.create_table, p_object_name);

312: end;
313: BSC_MO_HELPER_PKG.Do_DDL (p_ddl, ad_ddl.create_index, p_object_name);
314: ELSIF p_object_type='TABLE' THEN
315: BEGIN
316: BSC_MO_HELPER_PKG.Do_DDL (p_ddl, ad_ddl.create_table, p_object_name);
317: exception when others then
318: if sqlcode = -955 then --already exists for some reason, so drop and recreate
319: BSC_MO_HELPER_PKG.Do_DDL ('drop table '||p_object_name, ad_ddl.drop_table, p_object_name);
320: BSC_MO_HELPER_PKG.Do_DDL (p_ddl, ad_ddl.create_table, p_object_name);

Line 319: BSC_MO_HELPER_PKG.Do_DDL ('drop table '||p_object_name, ad_ddl.drop_table, p_object_name);

315: BEGIN
316: BSC_MO_HELPER_PKG.Do_DDL (p_ddl, ad_ddl.create_table, p_object_name);
317: exception when others then
318: if sqlcode = -955 then --already exists for some reason, so drop and recreate
319: BSC_MO_HELPER_PKG.Do_DDL ('drop table '||p_object_name, ad_ddl.drop_table, p_object_name);
320: BSC_MO_HELPER_PKG.Do_DDL (p_ddl, ad_ddl.create_table, p_object_name);
321: else
322: raise;
323: end if;

Line 320: BSC_MO_HELPER_PKG.Do_DDL (p_ddl, ad_ddl.create_table, p_object_name);

316: BSC_MO_HELPER_PKG.Do_DDL (p_ddl, ad_ddl.create_table, p_object_name);
317: exception when others then
318: if sqlcode = -955 then --already exists for some reason, so drop and recreate
319: BSC_MO_HELPER_PKG.Do_DDL ('drop table '||p_object_name, ad_ddl.drop_table, p_object_name);
320: BSC_MO_HELPER_PKG.Do_DDL (p_ddl, ad_ddl.create_table, p_object_name);
321: else
322: raise;
323: end if;
324: END;

Line 327: bsc_mo_helper_pkg.writeTmp(p_ddl);

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:
331: EXCEPTION WHEN OTHERS THEN

Line 328: bsc_mo_helper_pkg.writetmp(p_object_type||' '||p_object_name||' created successfully');

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:
331: EXCEPTION WHEN OTHERS THEN
332: BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in handle_ddl, Table='||p_object_name||',ddl='||p_ddl||', error='||sqlerrm);

Line 332: BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in handle_ddl, Table='||p_object_name||',ddl='||p_ddl||', error='||sqlerrm);

328: bsc_mo_helper_pkg.writetmp(p_object_type||' '||p_object_name||' created successfully');
329: end if;
330:
331: EXCEPTION WHEN OTHERS THEN
332: BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in handle_ddl, Table='||p_object_name||',ddl='||p_ddl||', error='||sqlerrm);
333: raise;
334: END;
335:
336: FUNCTION get_table_name(p_table_name varchar2) return VARCHAR2 IS

Line 339: IF NOT bsc_mo_helper_pkg.tableexists(p_table_name) THEN

335:
336: FUNCTION get_table_name(p_table_name varchar2) return VARCHAR2 IS
337: l_index number:=0;
338: BEGIN
339: IF NOT bsc_mo_helper_pkg.tableexists(p_table_name) THEN
340: return p_table_name;
341: END IF;
342: LOOP
343: IF NOT bsc_mo_helper_pkg.tableexists(p_table_name||l_index) THEN

Line 343: IF NOT bsc_mo_helper_pkg.tableexists(p_table_name||l_index) THEN

339: IF NOT bsc_mo_helper_pkg.tableexists(p_table_name) THEN
340: return p_table_name;
341: END IF;
342: LOOP
343: IF NOT bsc_mo_helper_pkg.tableexists(p_table_name||l_index) THEN
344: return p_table_name||l_index;
345: END IF;
346: l_index := l_index+1;
347: END LOOP;

Line 349: BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in get_table_name, Table='||p_table_name);

345: END IF;
346: l_index := l_index+1;
347: END LOOP;
348: EXCEPTION WHEN OTHERS THEN
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)

Line 358: bsc_mo_helper_pkg.writeTmp('create_b_prj_table for '||p_table.name);

354: RETURN VARCHAR2 IS
355: l_stmt VARCHAR2(32767);
356: l_table_name varchar2(100);
357: BEGIN
358: bsc_mo_helper_pkg.writeTmp('create_b_prj_table for '||p_table.name);
359: l_table_name := get_table_name(p_table.name||'_PRJ');
360: l_stmt := 'CREATE TABLE ' || l_table_name||' (' ||p_column_clause||') '||p_storage_clause;
361: handle_ddl(l_table_name, 'TABLE', l_stmt);
362:

Line 365: BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in create_b_prj_table, Table='||p_table.name||', stmt='||l_stmt||', error='||sqlerrm);

361: handle_ddl(l_table_name, 'TABLE', l_stmt);
362:
363: return l_table_name;
364: EXCEPTION WHEN OTHERS THEN
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)

Line 381: BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in create_i_rowid_table, Table='||p_table.name||', stmt='||l_stmt||', error:'||sqlerrm);

377: l_stmt := 'CREATE TABLE ' || l_table_name||' (ROW_ID ROWID, '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||' NUMBER) '||p_storage_clause;
378: handle_ddl(l_table_name, 'TABLE', l_stmt);
379: return l_table_name;
380: EXCEPTION WHEN OTHERS THEN
381: BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in create_i_rowid_table, Table='||p_table.name||', stmt='||l_stmt||', error:'||sqlerrm);
382: raise;
383: END;
384:
385: PROCEDURE add_to_update_tables_list(p_Table_name VARCHAR2, p_property VARCHAR2, p_value VARCHAR2) IS

Line 392: BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in add_to_update_tables_list, Table='||p_table_name|| ', property='||

388: l_update_rec.table_name := p_table_name;
389: l_update_rec.property := p_property||BSC_DBGEN_STD_METADATA.BSC_ASSIGNMENT||p_value;
390: g_update_tables(g_update_tables.count+1) := l_update_rec;
391: EXCEPTION WHEN OTHERS THEN
392: BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in add_to_update_tables_list, Table='||p_table_name|| ', property='||
393: p_property||', value='||p_value||', error:'||sqlerrm);
394: raise;
395: END;
396:

Line 466: l_index_name := BSC_MO_HELPER_PKG.generate_index_name(p_table_name,'B','1');

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
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 ';

Line 479: l_index_name := BSC_MO_HELPER_PKG.generate_index_name(p_table_name,'B','2');

475: -- For AW create separate index on CHANGE_VECTOR
476: IF (p_table.Impl_type=2) THEN -- create an AW type index
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 ';

Line 502: BSC_MO_HELPER_PKG.writeTmp('index columns after reordering is :'||l_primary_key);

498:
499: IF l_primary_key IS NOT NULL THEN -- create normal index
500: if (p_table_name like 'BSC_S%_PT' ) then
501: l_primary_key := reorder_index(p_table_name, colColumns);
502: BSC_MO_HELPER_PKG.writeTmp('index columns after reordering is :'||l_primary_key);
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 || ') '||

Line 513: BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in CreateTableInDB, Table='||p_table_name|| ':'||l_error);

509: END IF;
510:
511: EXCEPTION WHEN OTHERS THEN
512: l_error := sqlerrm;
513: BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in CreateTableInDB, Table='||p_table_name|| ':'||l_error);
514: BSC_MO_HELPER_PKG.writeTmp('Table creation stmt ='||l_stmt, FND_LOG.LEVEL_UNEXPECTED, true );
515: BSC_MO_HELPER_PKG.writeTmp('Index creation stmt ='||l_index_stmt, FND_LOG.LEVEL_UNEXPECTED, true );
516: raise;
517: END ;

Line 514: BSC_MO_HELPER_PKG.writeTmp('Table creation stmt ='||l_stmt, FND_LOG.LEVEL_UNEXPECTED, true );

510:
511: EXCEPTION WHEN OTHERS THEN
512: l_error := sqlerrm;
513: BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in CreateTableInDB, Table='||p_table_name|| ':'||l_error);
514: BSC_MO_HELPER_PKG.writeTmp('Table creation stmt ='||l_stmt, FND_LOG.LEVEL_UNEXPECTED, true );
515: BSC_MO_HELPER_PKG.writeTmp('Index creation stmt ='||l_index_stmt, FND_LOG.LEVEL_UNEXPECTED, true );
516: raise;
517: END ;
518:

Line 515: BSC_MO_HELPER_PKG.writeTmp('Index creation stmt ='||l_index_stmt, FND_LOG.LEVEL_UNEXPECTED, true );

511: EXCEPTION WHEN OTHERS THEN
512: l_error := sqlerrm;
513: BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in CreateTableInDB, Table='||p_table_name|| ':'||l_error);
514: BSC_MO_HELPER_PKG.writeTmp('Table creation stmt ='||l_stmt, FND_LOG.LEVEL_UNEXPECTED, true );
515: BSC_MO_HELPER_PKG.writeTmp('Index creation stmt ='||l_index_stmt, FND_LOG.LEVEL_UNEXPECTED, true );
516: raise;
517: END ;
518:
519: FUNCTION ExistIndex(IndexName IN VARCHAR2) RETURN boolean IS

Line 539: BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in ExistIndex:'||l_error);

535: Close c1;
536: RETURN l_res;
537: EXCEPTION WHEN OTHERS THEN
538: l_error := sqlerrm;
539: BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in ExistIndex:'||l_error);
540: raise;
541: END ;
542:
543: --****************************************************************************

Line 587: IF bsc_mo_helper_pkg.tableexists(p_table.projectionTable) THEN

583: --VARCHAR2 and NUMBER respectively. From now on I will get the data type
584: --direclty from the dimension table.
585:
586: --Table should not exist
587: IF bsc_mo_helper_pkg.tableexists(p_table.projectionTable) THEN
588: bsc_mo_helper_pkg.droptable(p_table.projectionTable);
589: END IF;
590:
591: --Create the table in the database

Line 588: bsc_mo_helper_pkg.droptable(p_table.projectionTable);

584: --direclty from the dimension table.
585:
586: --Table should not exist
587: IF bsc_mo_helper_pkg.tableexists(p_table.projectionTable) THEN
588: bsc_mo_helper_pkg.droptable(p_table.projectionTable);
589: END IF;
590:
591: --Create the table in the database
592: Tabla_Keys := p_table.keys;

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 687: BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in CreateProjTable:'||l_error);

683: 'PROJECTION');
684:
685: EXCEPTION WHEN OTHERS THEN
686: l_error := sqlerrm;
687: BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in CreateProjTable:'||l_error);
688: raise;
689: END ;
690:
691:

Line 710: IF bsc_mo_helper_pkg.tableExists(BaseTable.Name) THEN

706: CURSOR cDropIndexes(p_table IN VARCHAR2, p_owner IN VARCHAR2) IS
707: SELECT INDEX_NAME FROM ALL_INDEXES WHERE TABLE_NAME= p_table and table_owner=p_owner;
708: BEGIN
709:
710: IF bsc_mo_helper_pkg.tableExists(BaseTable.Name) THEN
711: IF Not bsc_mo_helper_pkg.table_column_exists(BaseTable.Name, 'PERIODICITY_ID') THEN
712: --Add periodicity_id
713: l_stmt := 'ALTER TABLE ' || BaseTable.Name || ' ADD PERIODICITY_ID NUMBER';
714: BSC_MO_HELPER_PKG.Do_DDL(l_stmt, ad_ddl.alter_table, BaseTable.Name);

Line 711: IF Not bsc_mo_helper_pkg.table_column_exists(BaseTable.Name, 'PERIODICITY_ID') THEN

707: SELECT INDEX_NAME FROM ALL_INDEXES WHERE TABLE_NAME= p_table and table_owner=p_owner;
708: BEGIN
709:
710: IF bsc_mo_helper_pkg.tableExists(BaseTable.Name) THEN
711: IF Not bsc_mo_helper_pkg.table_column_exists(BaseTable.Name, 'PERIODICITY_ID') THEN
712: --Add periodicity_id
713: l_stmt := 'ALTER TABLE ' || BaseTable.Name || ' ADD PERIODICITY_ID NUMBER';
714: BSC_MO_HELPER_PKG.Do_DDL(l_stmt, ad_ddl.alter_table, BaseTable.Name);
715:

Line 714: BSC_MO_HELPER_PKG.Do_DDL(l_stmt, ad_ddl.alter_table, BaseTable.Name);

710: IF bsc_mo_helper_pkg.tableExists(BaseTable.Name) THEN
711: IF Not bsc_mo_helper_pkg.table_column_exists(BaseTable.Name, 'PERIODICITY_ID') THEN
712: --Add periodicity_id
713: l_stmt := 'ALTER TABLE ' || BaseTable.Name || ' ADD PERIODICITY_ID NUMBER';
714: BSC_MO_HELPER_PKG.Do_DDL(l_stmt, ad_ddl.alter_table, BaseTable.Name);
715:
716: --Update periodicity_id
717: l_stmt := 'UPDATE ' || BaseTable.Name||' SET PERIODICITY_ID =:1 ';
718: EXECUTE IMMEDIATE l_stmt using BaseTable.Periodicity;

Line 722: BSC_MO_HELPER_PKG.Do_DDL(l_stmt, ad_ddl.alter_table, BaseTable.Name);

718: EXECUTE IMMEDIATE l_stmt using BaseTable.Periodicity;
719:
720: --Set periodicity_id NOT NULL
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

Line 731: BSC_MO_HELPER_PKG.Do_DDL(l_stmt, ad_ddl.drop_index, IndexName);

727: FETCH cDropIndexes INTO IndexName;
728: EXIT WHEN cDropIndexes%NOTFOUND;
729: --Drop index
730: l_stmt := 'DROP INDEX ' || IndexName;
731: BSC_MO_HELPER_PKG.Do_DDL(l_stmt, ad_ddl.drop_index, IndexName);
732: END LOOP;
733: CLOSE cDropIndexes;
734: --Re-create index to include PERIODICITY_ID
735: IndexName := BaseTable.Name || '_U1';

Line 754: bsc_mo_helper_pkg.do_ddl('drop index '||IndexName, ad_ddl.drop_index, IndexName);

750: IndexedColumns := IndexedColumns ||l_zero_code_cols;
751: IndexedColumns := substr(IndexedColumns, 1, Length(IndexedColumns)-1);
752: begin
753: -- just for safety, in case of corrupt left over tables
754: bsc_mo_helper_pkg.do_ddl('drop index '||IndexName, ad_ddl.drop_index, IndexName);
755: -- ignore all exceptions here
756: exception when others then
757: null;
758: end;

Line 762: bsc_mo_helper_pkg.Do_DDL (l_stmt, ad_ddl.create_index, IndexName);

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
766: -- Alter B_PRJ Table if required

Line 770: IF Not bsc_mo_helper_pkg.table_column_exists(l_prj_table, 'PERIODICITY_ID') THEN

766: -- Alter B_PRJ Table if required
767: l_prj_table := BSC_DBGEN_METADATA_READER.get_table_properties(BaseTable.Name,
768: BSC_DBGEN_STD_METADATA.BSC_B_PRJ_TABLE);
769: if (l_prj_table is not null) then
770: IF Not bsc_mo_helper_pkg.table_column_exists(l_prj_table, 'PERIODICITY_ID') THEN
771: --Add periodicity_id
772: l_stmt := 'ALTER TABLE ' || l_prj_table || ' ADD PERIODICITY_ID NUMBER';
773: BSC_MO_HELPER_PKG.Do_DDL(l_stmt, ad_ddl.alter_table, BaseTable.Name);
774: --Update periodicity_id

Line 773: BSC_MO_HELPER_PKG.Do_DDL(l_stmt, ad_ddl.alter_table, BaseTable.Name);

769: if (l_prj_table is not null) then
770: IF Not bsc_mo_helper_pkg.table_column_exists(l_prj_table, 'PERIODICITY_ID') THEN
771: --Add periodicity_id
772: l_stmt := 'ALTER TABLE ' || l_prj_table || ' ADD PERIODICITY_ID NUMBER';
773: BSC_MO_HELPER_PKG.Do_DDL(l_stmt, ad_ddl.alter_table, BaseTable.Name);
774: --Update periodicity_id
775: l_stmt := 'UPDATE ' || l_prj_table||' SET PERIODICITY_ID =:1 ';
776: EXECUTE IMMEDIATE l_stmt using BaseTable.Periodicity;
777: --Set periodicity_id NOT NULL

Line 779: BSC_MO_HELPER_PKG.Do_DDL(l_stmt, ad_ddl.alter_table, BaseTable.Name);

775: l_stmt := 'UPDATE ' || l_prj_table||' SET PERIODICITY_ID =:1 ';
776: EXECUTE IMMEDIATE l_stmt using BaseTable.Periodicity;
777: --Set periodicity_id NOT NULL
778: l_stmt := 'ALTER TABLE ' || l_prj_table || ' MODIFY PERIODICITY_ID NOT NULL';
779: BSC_MO_HELPER_PKG.Do_DDL(l_stmt, ad_ddl.alter_table, BaseTable.Name);
780: END IF;
781: end if;
782: EXCEPTION WHEN OTHERS THEN
783: l_error := sqlerrm;

Line 784: BSC_MO_HELPER_PKG.writeTmp('Error in statement:'||l_stmt, FND_LOG.LEVEL_EXCEPTION, true);

780: END IF;
781: end if;
782: EXCEPTION WHEN OTHERS THEN
783: l_error := sqlerrm;
784: BSC_MO_HELPER_PKG.writeTmp('Error in statement:'||l_stmt, FND_LOG.LEVEL_EXCEPTION, true);
785: BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in AddPeriodicityId:'||l_error);
786: raise;
787: END ;
788:

Line 785: BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in AddPeriodicityId:'||l_error);

781: end if;
782: EXCEPTION WHEN OTHERS THEN
783: l_error := sqlerrm;
784: BSC_MO_HELPER_PKG.writeTmp('Error in statement:'||l_stmt, FND_LOG.LEVEL_EXCEPTION, true);
785: BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in AddPeriodicityId:'||l_error);
786: raise;
787: END ;
788:
789: --****************************************************************************

Line 811: BSC_MO_HELPER_PKG.writeTmp('Inside CreateDefMaterializedView', FND_LOG.LEVEL_PROCEDURE);

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);
815: cal_id := BSC_METADATA_OPTIMIZER_PKG.gPeriodicities(l_index).CalENDarID;

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 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 827: --BSC_MO_HELPER_PKG.CHeckError('BSC_INTEGRATION_MV_GEN.Create_Def_Materialized_View');

823: END_year := start_year + num_anos - 1;
824:
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;

Line 830: BSC_MO_HELPER_PKG.writeTmp('Completed CreateDefMaterializedView FOR '||p_table.name, FND_LOG.LEVEL_PROCEDURE);

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
834: l_error := sqlerrm;

Line 835: BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in CreateDefMaterializedView:'||l_error);

831: END IF;
832:
833: EXCEPTION WHEN OTHERS THEN
834: l_error := sqlerrm;
835: BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in CreateDefMaterializedView:'||l_error);
836: raise;
837:
838: END ;
839:

Line 875: BSC_MO_HELPER_PKG.TerminateWithError ('BSC_SUBPERIOD_FAILED', 'GetSubperiodColumnName');

871:
872: EXCEPTION WHEN OTHERS THEN
873: l_error := sqlerrm ;
874: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Exception in GetSubperiodColumnName, Periodicity = '||Periodicity||' : '||l_error);
875: BSC_MO_HELPER_PKG.TerminateWithError ('BSC_SUBPERIOD_FAILED', 'GetSubperiodColumnName');
876: RAISE;
877: END;
878:
879:

Line 919: BSC_MO_HELPER_PKG.TerminateWithError('BSC_RETR_PERIOD_FAILED' , 'GetPeriodColumnName');

915:
916: EXCEPTION WHEN OTHERS THEN
917: l_error := sqlerrm;
918: FND_FILE.put_line(FND_FILE.LOG, 'Exception in GetPeriodColumnName:'||l_error);
919: BSC_MO_HELPER_PKG.TerminateWithError('BSC_RETR_PERIOD_FAILED' , 'GetPeriodColumnName');
920: raise;
921: END ;
922:
923: --****************************************************************************

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 969: bsc_mo_helper_pkg.writeTmp('Exception in GetKeyLength:'||l_error);

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;
973: */

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 1053: dbColumn := bsc_mo_helper_pkg.new_clsDBColumn;

1049: END IF;
1050:
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;

Line 1079: dbColumn := bsc_mo_helper_pkg.new_clsDBColumn;

1075: --SubPeriod
1076: IF (addSubPeriod) THEN
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;

Line 1099: bsc_mo_helper_pkg.writeTmp('Inside Alter Tables for '||p_Table.name||' with data.count= '||p_table.data.count, FND_LOG.LEVEL_STATEMENT, true);

1095: l_stmt VARCHAR2(1000);
1096: l_table_type varchar2(10);
1097: l_prj_table varchar2(100);
1098: BEGIN
1099: bsc_mo_helper_pkg.writeTmp('Inside Alter Tables for '||p_Table.name||' with data.count= '||p_table.data.count, FND_LOG.LEVEL_STATEMENT, true);
1100: IF (p_table.data.count=0) THEN
1101: bsc_mo_helper_pkg.writeTmp('Completed Alter Tables zero data count= ', FND_LOG.LEVEL_STATEMENT, true);
1102: return;
1103: END IF;

Line 1101: bsc_mo_helper_pkg.writeTmp('Completed Alter Tables zero data count= ', FND_LOG.LEVEL_STATEMENT, true);

1097: l_prj_table varchar2(100);
1098: BEGIN
1099: bsc_mo_helper_pkg.writeTmp('Inside Alter Tables for '||p_Table.name||' with data.count= '||p_table.data.count, FND_LOG.LEVEL_STATEMENT, true);
1100: IF (p_table.data.count=0) THEN
1101: bsc_mo_helper_pkg.writeTmp('Completed Alter Tables zero data count= ', FND_LOG.LEVEL_STATEMENT, true);
1102: return;
1103: END IF;
1104: l_origin_tables := BSC_DBGEN_UTILS.get_source_table_names(p_table.name);
1105: l_origin_tables(l_origin_tables.count) := p_table.name;

Line 1109: bsc_mo_helper_pkg.writeTmp('Measure '||p_table.data(i).fieldName||' needs to be added ', FND_LOG.LEVEL_STATEMENT, false);

1105: l_origin_tables(l_origin_tables.count) := p_table.name;
1106: FOR i IN p_table.data.first..p_table.data.last LOOP
1107: IF p_table.data(i).changeType='NEW' THEN -- new column, insert into db_tables_cols
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;

Line 1114: ELSIF Not bsc_mo_helper_pkg.table_column_exists(l_origin_tables(j), p_table.data(i).fieldName) THEN

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);
1117: BSC_MO_HELPER_PKG.Do_DDL(l_stmt, ad_ddl.alter_table, l_origin_tables(j));
1118: --IF B table is being altered,, B_PRJ table also needs to b altered

Line 1116: bsc_mo_helper_pkg.writeTmp(l_stmt, FND_LOG.LEVEL_STATEMENT, false);

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);
1117: BSC_MO_HELPER_PKG.Do_DDL(l_stmt, ad_ddl.alter_table, l_origin_tables(j));
1118: --IF B table is being altered,, B_PRJ table also needs to b altered
1119: END IF;
1120: IF BSC_MO_LOADER_CONFIG_PKG.isBasicTable(l_origin_tables(j)) THEN

Line 1117: BSC_MO_HELPER_PKG.Do_DDL(l_stmt, ad_ddl.alter_table, l_origin_tables(j));

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);
1117: BSC_MO_HELPER_PKG.Do_DDL(l_stmt, ad_ddl.alter_table, l_origin_tables(j));
1118: --IF B table is being altered,, B_PRJ table also needs to b altered
1119: END IF;
1120: IF BSC_MO_LOADER_CONFIG_PKG.isBasicTable(l_origin_tables(j)) THEN
1121: -- Alter B_PRJ Table if required

Line 1124: IF Not bsc_mo_helper_pkg.table_column_exists(l_prj_table, p_table.data(i).fieldName) THEN

1120: IF BSC_MO_LOADER_CONFIG_PKG.isBasicTable(l_origin_tables(j)) THEN
1121: -- Alter B_PRJ Table if required
1122: l_prj_table := BSC_DBGEN_METADATA_READER.get_table_properties(l_origin_tables(j), BSC_DBGEN_STD_METADATA.BSC_B_PRJ_TABLE);
1123: if (l_prj_table is not null) then
1124: IF Not bsc_mo_helper_pkg.table_column_exists(l_prj_table, p_table.data(i).fieldName) THEN
1125: l_stmt := 'ALTER TABLE ' || l_prj_table || ' ADD '||p_table.data(i).fieldName||' NUMBER';
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;

Line 1126: bsc_mo_helper_pkg.writeTmp(l_stmt, FND_LOG.LEVEL_STATEMENT, false);

1122: l_prj_table := BSC_DBGEN_METADATA_READER.get_table_properties(l_origin_tables(j), BSC_DBGEN_STD_METADATA.BSC_B_PRJ_TABLE);
1123: if (l_prj_table is not null) then
1124: IF Not bsc_mo_helper_pkg.table_column_exists(l_prj_table, p_table.data(i).fieldName) THEN
1125: l_stmt := 'ALTER TABLE ' || l_prj_table || ' ADD '||p_table.data(i).fieldName||' NUMBER';
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)

Line 1127: BSC_MO_HELPER_PKG.Do_DDL(l_stmt, ad_ddl.alter_table, l_prj_table);

1123: if (l_prj_table is not null) then
1124: IF Not bsc_mo_helper_pkg.table_column_exists(l_prj_table, p_table.data(i).fieldName) THEN
1125: l_stmt := 'ALTER TABLE ' || l_prj_table || ' ADD '||p_table.data(i).fieldName||' NUMBER';
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||')';

Line 1141: bsc_mo_helper_pkg.writeTmp('Exception while adding measure to MV log '||l_stmt, FND_LOG.LEVEL_STATEMENT, true);

1137: OR SQLCODE=-12027) -- ORA-12027: duplicate filter column
1138: THEN
1139: null;
1140: ELSE
1141: bsc_mo_helper_pkg.writeTmp('Exception while adding measure to MV log '||l_stmt, FND_LOG.LEVEL_STATEMENT, true);
1142: RAISE;
1143: END IF;
1144: END;
1145: END IF;

Line 1149: bsc_mo_helper_pkg.writeTmp('Completed Alter Tables zero data count= ', FND_LOG.LEVEL_STATEMENT, true);

1145: END IF;
1146: END LOOP;
1147: END IF;
1148: END LOOP;
1149: bsc_mo_helper_pkg.writeTmp('Completed Alter Tables zero data count= ', FND_LOG.LEVEL_STATEMENT, true);
1150: END;
1151:
1152: /*
1153: AddPartitionCols

Line 1215: dbColumn := bsc_mo_helper_pkg.new_clsDBColumn;

1211: --Keys
1212: l_index1 := l_keys.first;
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;

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 1233: dbColumn := bsc_mo_helper_pkg.new_clsDBColumn;

1229: EXIT WHEN l_index1 = l_keys.last;
1230: l_index1 := l_keys.next(l_index1);
1231: END LOOP;
1232: --Year
1233: dbColumn := bsc_mo_helper_pkg.new_clsDBColumn;
1234: AddYearPeriodType(colCampos, l_table_rec.periodicity, true, l_table_rec.type);
1235: --Data colunms
1236:
1237: IF (l_data.count > 0) THEN

Line 1243: dbColumn := bsc_mo_helper_pkg.new_clsDBColumn;

1239: END IF;
1240: LOOP
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;

Line 1272: dbColumn := bsc_mo_helper_pkg.new_clsDBColumn;

1268: END IF;
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;

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 1275: bsc_mo_helper_pkg.writeTmp('l_temp1=-1 in FindKeyIndex for key = '||Llave.keyName);

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;
1278: dbColumn.columnName := Llave.keyName;
1279: dbColumn.columnTYpe := GetDimKeyDataType(dimTable, 'CODE', columnSize);

Line 1292: dbColumn := bsc_mo_helper_pkg.new_clsDBColumn;

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;
1296: dbColumn.isKey := TRUE;

Line 1311: dbColumn := bsc_mo_helper_pkg.new_clsDBColumn;

1307: --Data columns
1308: LOOP
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;

Line 1336: bsc_mo_helper_pkg.terminateWithMsg(fnd_message.get);

1332: l_error := sqlerrm;
1333: FND_FILE.put_line(FND_FILE.LOG, 'Exception in CreateOneTable:'||l_error);
1334: fnd_message.set_name ('BSC', 'BSC_TABLENAME_CREATION_FAILED');
1335: fnd_message.set_token('TABLE_NAME', l_table_rec.name);
1336: bsc_mo_helper_pkg.terminateWithMsg(fnd_message.get);
1337: raise;
1338: END;
1339:
1340:

Line 1370: BSC_MO_HELPER_PKG.writeTmp('Bucket size = '||lTablesCount||'*2/('||g_max_buckets||') ='||g_bucket_size);

1366: g_max_buckets := g_max_buckets/2 ; -- consume only 1/2 max processes
1367: -- each table can have two indexes
1368: g_bucket_size := ceil(lTablesCount*2/g_max_buckets);
1369: -- assuming
1370: BSC_MO_HELPER_PKG.writeTmp('Bucket size = '||lTablesCount||'*2/('||g_max_buckets||') ='||g_bucket_size);
1371: if g_max_buckets > 1 then
1372: CreateDDLTable;
1373: else
1374: g_parallelize := false;

Line 1381: BSC_MO_HELPER_PKG.writeTmp('Inside CreateAllTables');

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
1385: --(Example FOR projection in kpi tables because of targets at dIFferent levels)

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 1409: BSC_MO_HELPER_PKG.writeTmp('Calling CreateOneTable1 for '||l_table_rec.name);

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);
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

Line 1416: BSC_MO_HELPER_PKG.writeTmp('Calling AddPeriodicityId2 for '||l_table_rec.name);

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
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);

Line 1420: BSC_MO_HELPER_PKG.writeTmp('Calling CreateOneTable3 for '||l_table_rec.name);

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
1424: IF Not BSC_METADATA_OPTIMIZER_PKG.g_BSC_MV THEN

Line 1425: BSC_MO_HELPER_PKG.writeTmp('Calling CreateOneTable4 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);
1429: --BSC-MV New architecture: None of the summary tables are needed.

Line 1428: BSC_MO_HELPER_PKG.writeTmp('MV architecture, Not Calling CreateOneTable5 for '||l_table_rec.name);

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
1431: IF l_table_rec.projectionTable is not null THEN
1432: -- Need to check this because one projection table corresponds

Line 1434: IF Not bsc_mo_helper_pkg.searchStringExists(arrProjTables, arrProjTables.count, l_table_rec.projectionTable) THEN

1430: --The only tables we need to create are the tables created FOR projection
1431: IF l_table_rec.projectionTable is not null THEN
1432: -- Need to check this because one projection table corresponds
1433: -- to many summary tables (same level but dIFferent periodicities)
1434: IF Not bsc_mo_helper_pkg.searchStringExists(arrProjTables, arrProjTables.count, l_table_rec.projectionTable) THEN
1435: CreateProjTable(l_table_rec);
1436: arrProjTables(arrProjTables.count) := l_table_rec.projectionTable;
1437: numProjTables := numProjTables + 1;
1438: END IF;

Line 1444: BSC_MO_HELPER_PKG.dropTable(l_table_rec.Name);

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;
1446: END IF;
1447: END IF;
1448: EXIT WHEN l_index = BSC_METADATA_OPTIMIZER_PKG.gTables.last;

Line 1457: bsc_mo_helper_pkg.writeTmp('updating properties in bsc_db_tables, count='||g_update_tables.count);

1453: IF (g_parallelize) THEN -- tables need to be created by child processes
1454: spawn_child_processes;
1455: END IF;
1456:
1457: bsc_mo_helper_pkg.writeTmp('updating properties in bsc_db_tables, count='||g_update_tables.count);
1458: FOR i IN 1..g_update_tables.count LOOP
1459: l_varchar_table1(i) := g_update_tables(i).table_name;
1460: l_varchar_table2(i) := g_update_tables(i).property;
1461: bsc_mo_helper_pkg.writeTmp('table_name='||l_varchar_table1(i)||', property='||l_varchar_table2(i));

Line 1461: bsc_mo_helper_pkg.writeTmp('table_name='||l_varchar_table1(i)||', property='||l_varchar_table2(i));

1457: bsc_mo_helper_pkg.writeTmp('updating properties in bsc_db_tables, count='||g_update_tables.count);
1458: FOR i IN 1..g_update_tables.count LOOP
1459: l_varchar_table1(i) := g_update_tables(i).table_name;
1460: l_varchar_table2(i) := g_update_tables(i).property;
1461: bsc_mo_helper_pkg.writeTmp('table_name='||l_varchar_table1(i)||', property='||l_varchar_table2(i));
1462: END LOOP;
1463: -- Update Table Properties
1464: FORALL i IN 1..g_update_tables.count
1465: UPDATE BSC_DB_TABLES

Line 1470: BSC_MO_HELPER_PKG.writeTmp('Completed CreateAllTables');

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:
1474:

Line 1497: bsc_mo_helper_pkg.writeTmp(l_stmt||' '||p_object_name);

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
1501: open cv for l_stmt;

Line 1515: bsc_mo_helper_pkg.writeTmp('The job status');

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
1519: bsc_mo_helper_pkg.writeTmp(p_id(i)||' '||p_job_id(i)||' '||p_status(i)||' '||p_message(i));

Line 1519: bsc_mo_helper_pkg.writeTmp(p_id(i)||' '||p_job_id(i)||' '||p_status(i)||' '||p_message(i));

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;
1523: return true;

Line 1526: bsc_mo_helper_pkg.writeTmp('Exception in get_child_job_status '||g_status_message, fnd_log.level_statement, TRUE);

1522: end if;
1523: return true;
1524: Exception when others then
1525: g_status_message:=sqlerrm;
1526: bsc_mo_helper_pkg.writeTmp('Exception in get_child_job_status '||g_status_message, fnd_log.level_statement, TRUE);
1527: return false;
1528: End;
1529:
1530:

Line 1544: bsc_mo_helper_pkg.writeTmp('Starting check_all_child_jobs');

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(
1548: p_job_status_table,

Line 1565: bsc_mo_helper_pkg.writeTmp('Exception in check_all_child_jobs '||g_status_message, fnd_log.level_statement, TRUE);

1561: end loop;
1562: return true;
1563: Exception when others then
1564: g_status_message:=sqlerrm;
1565: bsc_mo_helper_pkg.writeTmp('Exception in check_all_child_jobs '||g_status_message, fnd_log.level_statement, TRUE);
1566: return false;
1567: End;
1568:
1569:

Line 1611: --BSC_MO_HELPER_PKG.Do_DDL('drop table '||l_table_name, ad_ddl.drop_table, l_table_name);

1607: FETCH cv INTO l_object_name, l_object_ddl, l_object_type;
1608: EXIT WHEN cv%NOTFOUND;
1609: BEGIN
1610: null;
1611: --BSC_MO_HELPER_PKG.Do_DDL('drop table '||l_table_name, ad_ddl.drop_table, l_table_name);
1612: EXCEPTION WHEN OTHERS THEN
1613: null;
1614: END;
1615:

Line 1618: BSC_MO_HELPER_PKG.Do_DDL(l_object_ddl, ad_ddl.create_table, l_object_name);

1614: END;
1615:
1616: BEGIN
1617: IF (l_object_type='TABLE') THEN
1618: BSC_MO_HELPER_PKG.Do_DDL(l_object_ddl, ad_ddl.create_table, l_object_name);
1619: ELSE
1620: BSC_MO_HELPER_PKG.Do_DDL(l_object_ddl, ad_ddl.create_index, l_object_name);
1621: END IF;
1622: writelog(l_object_type ||' '||l_object_name||' created successfully.');

Line 1620: BSC_MO_HELPER_PKG.Do_DDL(l_object_ddl, ad_ddl.create_index, l_object_name);

1616: BEGIN
1617: IF (l_object_type='TABLE') THEN
1618: BSC_MO_HELPER_PKG.Do_DDL(l_object_ddl, ad_ddl.create_table, l_object_name);
1619: ELSE
1620: BSC_MO_HELPER_PKG.Do_DDL(l_object_ddl, ad_ddl.create_index, l_object_name);
1621: END IF;
1622: writelog(l_object_type ||' '||l_object_name||' created successfully.');
1623: EXCEPTION WHEN OTHERS THEN
1624: l_error := sqlerrm;

Line 1655: bsc_mo_helper_pkg.writeTmp('Exception in check_job_status '||g_status_message, fnd_log.level_statement, TRUE);

1651: RETURN 'N';
1652: END IF;
1653: Exception when others THEN
1654: g_status_message:=sqlerrm;
1655: bsc_mo_helper_pkg.writeTmp('Exception in check_job_status '||g_status_message, fnd_log.level_statement, TRUE);
1656: RETURN null;
1657: END;
1658:
1659: FUNCTION check_conc_process_status(p_conc_id number) RETURN varchar2 is

Line 1699: bsc_mo_helper_pkg.writeTmp('Done wait_on_jobs, zero count');

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;
1703: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN

Line 1704: bsc_mo_helper_pkg.writeTmp('Inside wait_on_jobs, jobs are ');

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
1708: l_job_status(i):='Y';

Line 1706: bsc_mo_helper_pkg.write_this(p_job_id);

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
1708: l_job_status(i):='Y';
1709: END LOOP;
1710: LOOP

Line 1724: bsc_mo_helper_pkg.writeTmp('status returned for '||p_job_id(i)||':'||l_status||' l_job_status='||l_job_status(i));

1720: l_status:=check_job_status(p_job_id(i));
1721: ELSE
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;

Line 1727: bsc_mo_helper_pkg.writeTmp('Compl wait_on_jobs, returning false');

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
1731: l_found:=TRUE;

Line 1735: bsc_mo_helper_pkg.writeTmp('Job '||p_job_id(i)||' has terminated '||to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));

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
1739: l_job_status(i) := 'ERROR';

Line 1741: bsc_mo_helper_pkg.writeTmp('Compl wait_on_jobs, returning false');

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;
1745: END IF;

Line 1754: bsc_mo_helper_pkg.writeTmp('Compl wait_on_jobs, returning true');

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
1758:

Line 1789: bsc_mo_helper_pkg.writeTmp('Done with check_ora_job_parameters, returning true');

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
1793: bsc_mo_helper_pkg.writeTmp('Exception in check_ora_job_parameters : '||sqlerrm, fnd_log.level_statement, TRUE);

Line 1793: bsc_mo_helper_pkg.writeTmp('Exception in check_ora_job_parameters : '||sqlerrm, fnd_log.level_statement, TRUE);

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
1793: bsc_mo_helper_pkg.writeTmp('Exception in check_ora_job_parameters : '||sqlerrm, fnd_log.level_statement, TRUE);
1794: return false;
1795: END;
1796:
1797:

Line 1824: bsc_mo_helper_pkg.writeTmp('Inside spawn_child_processes, system time is '||to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));

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
1828: --forall and execute immediate not compatible with 8i

Line 1848: bsc_mo_helper_pkg.writeTmp(

1844: l_try_serial := true;
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 ');

Line 1852: bsc_mo_helper_pkg.writeTmp('Single thread ');

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
1854: bsc_mo_helper_pkg.writeTmp('Launch multiple threads ('||g_buckets||'). ');
1855: END IF;
1856:

Line 1854: bsc_mo_helper_pkg.writeTmp('Launch multiple threads ('||g_buckets||'). ');

1850:
1851: IF l_try_serial THEN
1852: bsc_mo_helper_pkg.writeTmp('Single thread ');
1853: ELSE
1854: bsc_mo_helper_pkg.writeTmp('Launch multiple threads ('||g_buckets||'). ');
1855: END IF;
1856:
1857:
1858: -- buckets are striped starting with 1 sequentially

Line 1870: bsc_mo_helper_pkg.writeTmp('Concurrent Request '||l_job_id(bucket_num)||' launched at '||

1866: program=>g_conc_short_name,
1867: argument1=>bucket_num,
1868: argument2=>g_ddl_table_name);
1869: commit;
1870: bsc_mo_helper_pkg.writeTmp('Concurrent Request '||l_job_id(bucket_num)||' launched at '||
1871: to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
1872: IF l_job_id(bucket_num)<=0 THEN
1873: l_try_serial:=TRUE;
1874: END IF;

Line 1876: bsc_mo_helper_pkg.writeTmp('Not a Concurrent program, trying DBMS JOBS');

1872: IF l_job_id(bucket_num)<=0 THEN
1873: l_try_serial:=TRUE;
1874: END IF;
1875: ELSIF l_try_serial = false THEN --not a concurrent program and job init.ora params ok
1876: bsc_mo_helper_pkg.writeTmp('Not a Concurrent program, trying DBMS JOBS');
1877: DBMS_JOB.SUBMIT(l_job_id(bucket_num),
1878: 'BSC_MO_DB_PKG.create_tables_spawned('||bucket_num||');',
1879: sysdate + (10/86400)); -- next second
1880: commit;--this commit is very imp

Line 1881: bsc_mo_helper_pkg.writeTmp(' submitted dbms_job : id is '||l_job_id(bucket_num));

1877: DBMS_JOB.SUBMIT(l_job_id(bucket_num),
1878: 'BSC_MO_DB_PKG.create_tables_spawned('||bucket_num||');',
1879: sysdate + (10/86400)); -- next second
1880: commit;--this commit is very imp
1881: bsc_mo_helper_pkg.writeTmp(' submitted dbms_job : id is '||l_job_id(bucket_num));
1882: bsc_mo_helper_pkg.writeTmp('Job '||l_job_id(bucket_num)||' launched '||
1883: to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
1884: IF l_job_id(bucket_num)<=0 THEN
1885: l_try_serial:=TRUE;

Line 1882: bsc_mo_helper_pkg.writeTmp('Job '||l_job_id(bucket_num)||' launched '||

1878: 'BSC_MO_DB_PKG.create_tables_spawned('||bucket_num||');',
1879: sysdate + (10/86400)); -- next second
1880: commit;--this commit is very imp
1881: bsc_mo_helper_pkg.writeTmp(' submitted dbms_job : id is '||l_job_id(bucket_num));
1882: bsc_mo_helper_pkg.writeTmp('Job '||l_job_id(bucket_num)||' launched '||
1883: to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
1884: IF l_job_id(bucket_num)<=0 THEN
1885: l_try_serial:=TRUE;
1886: END IF;

Line 1889: bsc_mo_helper_pkg.writeTmp('Error launching parallel slaves '||sqlerrm||'. Attempt serial load', fnd_log.level_statement, TRUE);

1885: l_try_serial:=TRUE;
1886: END IF;
1887: END IF;
1888: exception when others THEN
1889: bsc_mo_helper_pkg.writeTmp('Error launching parallel slaves '||sqlerrm||'. Attempt serial load', fnd_log.level_statement, TRUE);
1890: l_try_serial:=TRUE;
1891: END;
1892:
1893: IF l_try_serial THEN

Line 1896: bsc_mo_helper_pkg.writeTmp('Attempt serial load');

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
1900: END IF;

Line 1912: bsc_mo_helper_pkg.TerminateWithMsg('One or more spawned programs failed');

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;
1915:
1916: /*IF l_status THEN

Line 1927: bsc_mo_helper_pkg.do_ddl('drop table '||g_ddl_table_name, ad_ddl.drop_table, g_ddl_table_name);

1923: END IF;
1924: END IF;
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;

Line 1930: bsc_mo_helper_pkg.writeTmp('Compl spawn_child_processes, system time is '||to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));

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
1934:

Line 1935: bsc_mo_helper_pkg.writeTmp('Exception in spawn_child_processes :'||sqlerrm, fnd_log.level_statement, TRUE);

1931: END IF;
1932:
1933: EXCEPTION WHEN OTHERS THEN
1934:
1935: bsc_mo_helper_pkg.writeTmp('Exception in spawn_child_processes :'||sqlerrm, fnd_log.level_statement, TRUE);
1936: raise;
1937:
1938: END;
1939: