[Home] [Help]
23: l_origin_name VARCHAR2(100);
24: CURSOR cTableType IS
25: select count(1)
26: from bsc_db_tables tbl,
27: bsc_db_tables_rels rels
28: where
29: rels.table_name = p_table_name
30: and rels.source_table_name = tbl.table_name
31: and tbl.table_type = 0;
376: --****************************************************************************
377: -- GetIndicTableType
378: --
379: -- DESCRIPTION:
380: -- Return the type of the table according to BSC_DB_TABLES_RELS and BSC_DB_TABLES
381: -- arrIndicTables() contains the tables used directly by the indicator
382: -- 0 - Input table
383: -- 1 - Base table
384: -- 2 - Temporal Table
391: l_table_name VARCHAR2(100);
392: l_table_type NUMBER;
393: CURSOR c1(p1 VARCHAR2) IS
394: SELECT T.TABLE_NAME, T.TABLE_TYPE
395: FROM BSC_DB_TABLES T, BSC_DB_TABLES_RELS R
396: WHERE T.TABLE_NAME = R.SOURCE_TABLE_NAME
397: AND R.TABLE_NAME = p1;
398: l_return NUMBER;
399: BEGIN
472: l_index := arrTables.next(l_index);
473: END LOOP;
474: bsc_mo_helper_pkg.add_value_bulk(1, l_varchar2_list);
475: l_varchar2_list.delete;
476: l_stmt := 'SELECT SOURCE_TABLE_NAME FROM BSC_DB_TABLES_RELS WHERE '|| strWhereInTables;
477: OPEN CV for l_stmt;
478: LOOP
479: FETCH CV INTO l_table;
480: EXIT WHEN CV%NOTFOUND;
514: AND DIM_SET_ID = p2
515: AND TABLE_NAME IS NOT NULL;
516:
517: CURSOR c2 (p1 NUMBER, p2 NUMBER) IS
518: SELECT DISTINCT SOURCE_TABLE_NAME FROM BSC_DB_TABLES_RELS
519: WHERE TABLE_NAME IN (
520: SELECT TABLE_NAME
521: FROM BSC_KPI_DATA_TABLES_V
522: WHERE INDICATOR = p1
682:
683: l_dummy varchar2(1000);
684:
685: cursor MissingSTables IS
686: select table_name from bsc_db_Tables_rels
687: connect by source_table_name = prior table_name
688: start with source_table_name in
689: (
690: select value_v from bsc_tmp_big_in_cond
691: where variable_id=10
692: and session_id = userenv('SESSIONID')
693: )
694: union -- add deleted periodicities
695: select table_name from bsc_db_tables_rels
696: where substr(table_name, 1, instr(table_name, '_', -1)) in
697: (select substr(value_v, 1, instr(value_v, '_', -1))
698: from bsc_tmp_big_in_cond
699: where variable_id = 10
766: l_tblrels_table_name dbms_sql.varchar2_table;
767: l_tblrels_src_table_name dbms_sql.varchar2_table;
768: l_tblrels_relation_type dbms_sql.number_table;
769:
770: TYPE tab_clsRels IS TABLE OF BSC_DB_TABLES_RELS%ROWTYPE index by binary_integer;
771: l_rels_record BSC_DB_TABLES_RELS%ROWTYPE ;
772: l_tab_rels tab_clsRels;
773:
774: TYPE tab_clsDBTables IS TABLE OF BSC_DB_TABLES%ROWTYPE index by binary_integer;
767: l_tblrels_src_table_name dbms_sql.varchar2_table;
768: l_tblrels_relation_type dbms_sql.number_table;
769:
770: TYPE tab_clsRels IS TABLE OF BSC_DB_TABLES_RELS%ROWTYPE index by binary_integer;
771: l_rels_record BSC_DB_TABLES_RELS%ROWTYPE ;
772: l_tab_rels tab_clsRels;
773:
774: TYPE tab_clsDBTables IS TABLE OF BSC_DB_TABLES%ROWTYPE index by binary_integer;
775: l_db_tables_record BSC_DB_TABLES%ROWTYPE ;
831: --
832: -- this procedure will add any missing S tables to l_tbl_delete
833: add_dependant_tables(l_del_s_tables, l_tbl_delete);
834: FORALL i IN 1..l_tbl_delete.count
835: DELETE FROM BSC_DB_TABLES_RELS WHERE TABLE_NAME = l_tbl_delete(i);
836: FORALL i IN 1..l_tbl_delete.count
837: DELETE FROM BSC_DB_TABLES WHERE TABLE_NAME = l_tbl_delete(i);
838: FORALL i IN 1..l_tbl_delete.count
839: DELETE FROM BSC_DB_CALCULATIONS WHERE TABLE_NAME = l_tbl_delete(i) AND CALCULATION_TYPE in (l_calc4,l_calc5);
860: BSC_MO_HELPER_PKG.writeTmp('Processing gTables('||l_index1||') '||bsc_mo_helper_pkg.get_time);
861: BSC_MO_HELPER_PKG.write_this(Tabla);
862: END IF;
863: bsc_mo_helper_pkg.writeTmp('Going to Delete Metadata tables for '||UPPER(Tabla.Name), FND_LOG.LEVEL_STATEMENT, false);
864: --BSC_DB_TABLES_RELS
865: If Tabla.Type = 1 Then
866: -- Changed to bulk deletes/inserts for better performance - bug 4559323
867: PeriodicityOrigin := 0;
868: OriTableName := null;
1217:
1218: -- BULK INSERTS
1219: BEGIN
1220: FORALL i IN 1..l_tab_rels.count
1221: INSERT INTO BSC_DB_TABLES_RELS VALUES l_tab_rels(i);
1222: EXCEPTION
1223: WHEN others THEN
1224: FOR i IN 1.. SQL%BULK_EXCEPTIONS.COUNT LOOP
1225: bsc_mo_helper_pkg.writeTmp('Error ' || i || ' occurred during '||