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_HELPER_PKG dependencies on BSC_DB_TABLES

Line 66: FROM BSC_DB_TABLES_RELS

62:
63: FUNCTION getSourceTable(p_table IN VARCHAR2) return VARCHAR2 IS
64: CURSOR cSource is
65: SELECT SOURCE_TABLE_NAME
66: FROM BSC_DB_TABLES_RELS
67: WHERE TABLE_NAME = p_table;
68: l_table VARCHAR2(100);
69: BEGIN
70: OPEN cSource;

Line 124: from bsc_db_tables_rels

120: l_stmt := l_stmt || ' select distinct
121: substr(table_name, instr(table_name, ''_'', 1, 2)+1, instr(table_name, ''_'', 1, 3)-instr(table_name, ''_'', 1, 2)-1) indicator,
122: substr(table_name, instr(table_name, ''_'', 1, 3)+1, instr(table_name, ''_'', 1, 4)-instr(table_name, ''_'', 1, 3)-1) dim_set_id,
123: table_name
124: from bsc_db_tables_rels
125: where table_name like ''BSC_S%''
126: and (source_table_name like ''BSC_B%'' or source_table_name like ''BSC_T%'' )';
127:
128: do_ddl(l_stmt, ad_ddl.create_table, TableName);

Line 164: (select table_name from bsc_db_tables_rels

160: FUNCTION find_objectives_for_table_old(p_table IN VARCHAR2) return BSC_METADATA_OPTIMIZER_PKG.tab_clsKPIDimSet IS
161: CURSOR cObjectives IS
162: select distinct indicator, dim_set_id from bsc_kpi_data_tables
163: where table_name in
164: (select table_name from bsc_db_tables_rels
165: where table_name like 'BSC_S%'
166: connect by prior table_name = source_table_name
167: start with table_name = p_table);
168: l_objective number;

Line 192: (select table_name from bsc_db_tables_rels

188: FUNCTION find_objectives_for_table_new(p_table IN VARCHAR2) return BSC_METADATA_OPTIMIZER_PKG.tab_clsKPIDimSet IS
189: l_stmt VARCHAR2(1000) :=
190: 'select distinct indicator, dim_set_id from '||BSC_METADATA_OPTIMIZER_PKG.g_kpi_tmp_table||'
191: where table_name in
192: (select table_name from bsc_db_tables_rels
193: where table_name like ''BSC_S%''
194: connect by prior table_name = source_table_name
195: start with table_name = :1)';
196: l_objective number;

Line 234: select periodicity_id from bsc_db_tables

230: END;
231:
232: FUNCTION getPeriodicityForTable(p_table_name IN VARCHAR2) return NUMBER IS
233: CURSOR cPeriodicity IS
234: select periodicity_id from bsc_db_tables
235: where table_name = upper(p_table_name);
236: l_value NUMBER;
237: BEGIN
238: OPEN cPeriodicity;

Line 248: from bsc_db_tables tbl,

244: Function is_base_table(p_table IN VARCHAR2) RETURN Boolean IS
245: l_count NUMBER;
246: CURSOR cTableType IS
247: select count(1)
248: from bsc_db_tables tbl,
249: bsc_db_tables_rels rels
250: where
251: rels.table_name = p_table
252: and rels.source_table_name = tbl.table_name

Line 249: bsc_db_tables_rels rels

245: l_count NUMBER;
246: CURSOR cTableType IS
247: select count(1)
248: from bsc_db_tables tbl,
249: bsc_db_tables_rels rels
250: where
251: rels.table_name = p_table
252: and rels.source_table_name = tbl.table_name
253: and tbl.table_type = 0;

Line 1557: SELECT PROJECT_FLAG FROM BSC_DB_TABLES

1553:
1554: Function CalcProjectionTable(TableName IN VARCHAR2) return BOOLEAN IS
1555:
1556: CURSOR C1(p1 VARCHAR2) IS
1557: SELECT PROJECT_FLAG FROM BSC_DB_TABLES
1558: WHERE TABLE_NAME = p1;
1559: l_proj NUMBER ;
1560: l_ret boolean;
1561:

Line 3209: l_stmt := 'SELECT TABLE_NAME FROM BSC_DB_TABLES_RELS WHERE '|| strWhereInChildTables;

3205: Add_Value_Big_In_Cond_Varchar2 (5, arrTables(i));
3206: END LOOP; */
3207:
3208: --insert the children
3209: l_stmt := 'SELECT TABLE_NAME FROM BSC_DB_TABLES_RELS WHERE '|| strWhereInChildTables;
3210: OPEN cv FOR l_stmt;
3211: LOOP
3212: FETCH cv INTO l_table;
3213: EXIT WHEN cv%NOTFOUND;

Line 3229: l_stmt := 'SELECT SOURCE_TABLE_NAME FROM BSC_DB_TABLES_RELS WHERE '|| strWhereInParentTables;

3225: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
3226: writeTmp('No. of new children = '|| numNewTables);
3227: END IF;
3228: --insert the parents
3229: l_stmt := 'SELECT SOURCE_TABLE_NAME FROM BSC_DB_TABLES_RELS WHERE '|| strWhereInParentTables;
3230: l_stmt := replace(l_stmt, 'UPPER(TABLE_NAME)', 'TABLE_NAME');
3231: l_stmt := replace(l_stmt, 'UPPER(VALUE_V)', 'VALUE_V');
3232: OPEN cv FOR l_stmt;
3233: FETCH cv BULK COLLECT INTO l_arr_tables;

Line 4064: select distinct source_table_name from bsc_db_tables_rels

4060: -- of selected indicators
4061:
4062: PROCEDURE InsertDirectTables(arrTables in out nocopy dbms_Sql.varchar2_table, p_variable_id IN NUMBER) IS
4063: CURSOR cStoITables IS
4064: select distinct source_table_name from bsc_db_tables_rels
4065: connect by table_name = prior source_table_name
4066: start with table_name in (
4067: SELECT VALUE_V FROM BSC_TMP_BIG_IN_COND WHERE SESSION_ID = bsc_metadata_optimizer_pkg.g_session_id
4068: AND VARIABLE_ID = p_variable_id);

Line 4087: l_stmt VARCHAR2(1000):= 'select distinct source_table_name from bsc_db_tables_rels

4083: FUNCTION GetAffectedProductionTables(inIndicators in VARCHAR2) return dbms_Sql.varchar2_table IS
4084:
4085: cv CurTyp;
4086:
4087: l_stmt VARCHAR2(1000):= 'select distinct source_table_name from bsc_db_tables_rels
4088: connect by table_name = prior source_table_name
4089: start with table_name in (';
4090:
4091: l_tables dbms_Sql.varchar2_table ;

Line 4234: l_dontDropTables := ' select source_table_name from bsc_db_tables_rels

4230: -- the tables. so get the list of tables used by these indicators
4231: ---------------------------------------------------------------------
4232: --Step 5. For indicators in 4, get list of tables going from S to I tables
4233: ---------------------------------------------------------------------
4234: l_dontDropTables := ' select source_table_name from bsc_db_tables_rels
4235: connect by table_name = prior source_table_name start with table_name in
4236: (select table_name from '||BSC_METADATA_OPTIMIZER_PKG.g_kpi_tmp_table||' where indicator in ('||l_dontProcessIndics||') )';
4237: writeTmp( 'l_dontDropTables ='||l_dontDropTables, FND_LOG.LEVEL_STATEMENT, false);
4238: ---------------------------------------------------

Line 4241: l_dropTables := 'select table_name from bsc_db_tables where ('||strWhereInDirectTables||') '||' and table_name not in ('||

4237: writeTmp( 'l_dontDropTables ='||l_dontDropTables, FND_LOG.LEVEL_STATEMENT, false);
4238: ---------------------------------------------------
4239: --Step 6. Subtract 1-5
4240: ---------------------------------------------------
4241: l_dropTables := 'select table_name from bsc_db_tables where ('||strWhereInDirectTables||') '||' and table_name not in ('||
4242: l_dontDropTables||')';
4243: writeTmp( 'l_dropTables ='||l_dropTables, FND_LOG.LEVEL_STATEMENT, false);
4244: BSC_METADATA_OPTIMIZER_PKG.gnumTables := 0;
4245: BSC_METADATA_OPTIMIZER_PKG.garrTables.delete;

Line 4463: SELECT TABLE_NAME FROM BSC_DB_TABLES WHERE TABLE_TYPE <> 2;

4459: l_table VARCHAR2(100);
4460: l_error VARCHAR2(400);
4461:
4462: CURSOR cTables IS
4463: SELECT TABLE_NAME FROM BSC_DB_TABLES WHERE TABLE_TYPE <> 2;
4464: CURSOR cIndics4 IS
4465: SELECT INDICATOR FROM BSC_KPIS_B WHERE PROTOTYPE_FLAG = 4 ORDER BY INDICATOR;
4466:
4467: l_original_count number := 0;

Line 4945: FROM BSC_DB_TABLES_RELS R, BSC_DB_TABLES T

4941: cv CurTyp;
4942:
4943: CURSOR cBaseTables IS
4944: SELECT R.TABLE_NAME
4945: FROM BSC_DB_TABLES_RELS R, BSC_DB_TABLES T
4946: WHERE R.SOURCE_TABLE_NAME = T.TABLE_NAME
4947: AND T.TABLE_TYPE = 0;
4948:
4949: BEGIN

Line 5031: Creates the tables BSC_DB_TABLES_LAST, BSC_DB_TABLES_RELS_LAST,

5027: /****************************************************************************
5028: CreateLastTables
5029:
5030: DESCRIPTION:
5031: Creates the tables BSC_DB_TABLES_LAST, BSC_DB_TABLES_RELS_LAST,
5032: BSC_KPI_DATA_TABLES_LAST
5033:
5034: AUTHOR/DATE - MODIFICATIONS (AUTHOR/DATE/DESCRIPTION):
5035: ***************************************************************************/

Line 5052: TableName := 'BSC_DB_TABLES';

5048: IF (BSC_METADATA_OPTIMIZER_PKG.gBscSchema IS NULL) THEN
5049: BSC_METADATA_OPTIMIZER_PKG.gBscSchema := getBSCSchema ;
5050: END IF;
5051:
5052: TableName := 'BSC_DB_TABLES';
5053:
5054: IF BSC_METADATA_OPTIMIZER_PKG.gGAA_RUN_MODE <>0 AND BSC_METADATA_OPTIMIZER_PKG.gIndicators.count < l_threshold THEN
5055: l_where_clause := ' WHERE table_name in (
5056: SELECT SOURCE_TABLE_NAME FROM BSC_DB_TABLES_RELS

Line 5056: SELECT SOURCE_TABLE_NAME FROM BSC_DB_TABLES_RELS

5052: TableName := 'BSC_DB_TABLES';
5053:
5054: IF BSC_METADATA_OPTIMIZER_PKG.gGAA_RUN_MODE <>0 AND BSC_METADATA_OPTIMIZER_PKG.gIndicators.count < l_threshold THEN
5055: l_where_clause := ' WHERE table_name in (
5056: SELECT SOURCE_TABLE_NAME FROM BSC_DB_TABLES_RELS
5057: CONNECT BY TABLE_NAME=prior SOURCE_TABLE_NAME
5058: start with table_name in
5059: (select distinct table_name from '||BSC_METADATA_OPTIMIZER_PKG.g_kpi_tmp_table||' where indicator in
5060: (select indicator from bsc_tmp_opt_ui_kpis where process_id='||BSC_METADATA_OPTIMIZER_PKG.g_processID||')

Line 5063: SELECT TABLE_NAME FROM BSC_DB_TABLES_RELS

5059: (select distinct table_name from '||BSC_METADATA_OPTIMIZER_PKG.g_kpi_tmp_table||' where indicator in
5060: (select indicator from bsc_tmp_opt_ui_kpis where process_id='||BSC_METADATA_OPTIMIZER_PKG.g_processID||')
5061: )
5062: UNION
5063: SELECT TABLE_NAME FROM BSC_DB_TABLES_RELS
5064: CONNECT BY TABLE_NAME=prior SOURCE_TABLE_NAME
5065: start with table_name in
5066: (select distinct table_name from '||BSC_METADATA_OPTIMIZER_PKG.g_kpi_tmp_table||' where indicator in
5067: (select indicator from bsc_tmp_opt_ui_kpis where process_id='||BSC_METADATA_OPTIMIZER_PKG.g_processID||')

Line 5074: TableName := 'BSC_DB_TABLES_RELS';

5070: END IF;
5071: CreateCopyTable(TableName, BSC_METADATA_OPTIMIZER_PKG.g_db_tables_last, BSC_METADATA_OPTIMIZER_PKG.gSummaryTableTbsName, l_where_clause);
5072: CreateCopyIndexes (TableName, BSC_METADATA_OPTIMIZER_PKG.g_db_tables_last, BSC_METADATA_OPTIMIZER_PKG.gSummaryIndexTbsName);
5073: dbms_stats.gather_table_stats(BSC_METADATA_OPTIMIZER_PKG.gBscSchema, BSC_METADATA_OPTIMIZER_PKG.g_db_tables_last);
5074: TableName := 'BSC_DB_TABLES_RELS';
5075: CreateCopyTable(TableName, BSC_METADATA_OPTIMIZER_PKG.g_db_table_rels_last, BSC_METADATA_OPTIMIZER_PKG.gSummaryTableTbsName, l_where_clause);
5076: CreateCopyIndexes(TableName, BSC_METADATA_OPTIMIZER_PKG.g_db_table_rels_last, BSC_METADATA_OPTIMIZER_PKG.gSummaryIndexTbsName);
5077: dbms_stats.gather_table_stats(BSC_METADATA_OPTIMIZER_PKG.gBscSchema, BSC_METADATA_OPTIMIZER_PKG.g_db_table_rels_last);
5078: IF BSC_METADATA_OPTIMIZER_PKG.gGAA_RUN_MODE <>0 AND BSC_METADATA_OPTIMIZER_PKG.gIndicators.count < l_threshold THEN

Line 5094: TableName := 'BSC_DB_TABLES_COLS';

5090: (select source_table_name from '||BSC_METADATA_OPTIMIZER_PKG.g_db_table_rels_last||'
5091: connect by table_name = prior source_table_name
5092: start with table_name in (select table_name from '||BSC_METADATA_OPTIMIZER_PKG.g_kpi_data_last||'))';
5093: END IF;
5094: TableName := 'BSC_DB_TABLES_COLS';
5095: CreateCopyTable(TableName, BSC_METADATA_OPTIMIZER_PKG.g_db_tables_cols_last, BSC_METADATA_OPTIMIZER_PKG.gSummaryTableTbsName, l_where_clause);
5096: CreateCopyIndexes(TableName, BSC_METADATA_OPTIMIZER_PKG.g_db_tables_cols_last, BSC_METADATA_OPTIMIZER_PKG.gSummaryIndexTbsName);
5097: dbms_stats.gather_table_stats(BSC_METADATA_OPTIMIZER_PKG.gBscSchema, BSC_METADATA_OPTIMIZER_PKG.g_db_tables_cols_last);
5098:

Line 5315: -- Note: This procedure uses BSC_DB_TABLES_RELS_LAST

5311: -- DESCRIPTION:
5312: -- Insert in the arry arrChildTables() all the tables in the graph
5313: -- that are affected by the tables in the array arrTables(), including
5314: -- themself.
5315: -- Note: This procedure uses BSC_DB_TABLES_RELS_LAST
5316: --
5317: -- AUTHOR/DATE - MODIFICATIONS (AUTHOR/DATE/DESCRIPTION):
5318: --****************************************************************************
5319:

Line 5331: --l_stmt VARCHAR2(2000) := 'SELECT TABLE_NAME FROM BSC_DB_TABLES_RELS_LAST WHERE UPPER(SOURCE_TABLE_NAME) = :1' ;

5327: l_source_table_name VARCHAR2(300);
5328: arrTablesAux dbms_sql.varchar2_table;
5329: numTablesAux NUMBER;
5330:
5331: --l_stmt VARCHAR2(2000) := 'SELECT TABLE_NAME FROM BSC_DB_TABLES_RELS_LAST WHERE UPPER(SOURCE_TABLE_NAME) = :1' ;
5332: CURSOR cChildTables (pOriginTable IN VARCHAR2) IS
5333: select table_name from bsc_db_tables_rels
5334: connect by prior table_name = source_Table_name
5335: start with source_table_name = pOriginTable;

Line 5333: select table_name from bsc_db_tables_rels

5329: numTablesAux NUMBER;
5330:
5331: --l_stmt VARCHAR2(2000) := 'SELECT TABLE_NAME FROM BSC_DB_TABLES_RELS_LAST WHERE UPPER(SOURCE_TABLE_NAME) = :1' ;
5332: CURSOR cChildTables (pOriginTable IN VARCHAR2) IS
5333: select table_name from bsc_db_tables_rels
5334: connect by prior table_name = source_Table_name
5335: start with source_table_name = pOriginTable;
5336: l_error VARCHAR2(4000);
5337:

Line 5417: (SELECT SOURCE_TABLE_NAME FROM BSC_DB_TABLES_RELS

5413: WHERE R.SOURCE_TABLE_NAME = IT.TABLE_NAME
5414: AND IT.TABLE_TYPE = 0
5415: AND R.TABLE_NAME = BT.TABLE_NAME
5416: AND IT.TABLE_NAME IN
5417: (SELECT SOURCE_TABLE_NAME FROM BSC_DB_TABLES_RELS
5418: CONNECT BY TABLE_NAME=prior SOURCE_TABLE_NAME
5419: start with table_name in
5420: (select distinct table_name from '||BSC_METADATA_OPTIMIZER_PKG.g_kpi_tmp_table||' where indicator in
5421: (select indicator from bsc_tmp_opt_ui_kpis where process_id=:1)

Line 5627: FROM BSC_DB_TABLES_RELS WHERE TABLE_NAME IN (

5623: END LOOP;
5624: CLOSE cv;
5625: --BSC-MV Note: Drop all MV used for targets for those KPIs
5626: l_stmt := 'SELECT DISTINCT BSC_BIA_WRAPPER.Get_Sum_Table_MV_Name(SOURCE_TABLE_NAME) MV_NAME
5627: FROM BSC_DB_TABLES_RELS WHERE TABLE_NAME IN (
5628: SELECT TABLE_NAME
5629: FROM '||BSC_METADATA_OPTIMIZER_PKG.g_kpi_tmp_table||'
5630: WHERE (' || strWhereInIndics || ') AND TABLE_NAME IS NOT NULL ) AND RELATION_TYPE = 1';
5631:

Line 5706: FROM BSC_DB_TABLES_RELS WHERE TABLE_NAME IN (

5702: CLOSE cv;
5703:
5704: --BSC-MV Note: Drop all MV used for targets for those KPIs
5705: l_stmt := 'SELECT DISTINCT BSC_BIA_WRAPPER.Get_Sum_Table_MV_Name(SOURCE_TABLE_NAME) MV_NAME
5706: FROM BSC_DB_TABLES_RELS WHERE TABLE_NAME IN (
5707: SELECT TABLE_NAME FROM BSC_KPI_DATA_TABLES WHERE ('|| strWhereInIndics4 || ')';
5708: If BSC_METADATA_OPTIMIZER_PKG.garrIndics.count > 0 Then
5709: l_stmt := l_stmt || ' AND (' || strWhereNotInIndics || ')';
5710: End If;

Line 5767: --BSC_DB_TABLES

5763: EXIT WHEN i=BSC_METADATA_OPTIMIZER_PKG.garrTables.last;
5764: i := BSC_METADATA_OPTIMIZER_PKG.garrTables.next(i);
5765: END LOOP;
5766: IF BSC_METADATA_OPTIMIZER_PKG.garrTables.count > 0 THEN
5767: --BSC_DB_TABLES
5768: l_stmt := 'DELETE FROM BSC_DB_TABLES WHERE '||strWhereInTables;
5769: l_stmt := replace(l_stmt, 'UPPER(TABLE_NAME)', 'table_name');
5770: l_stmt := replace(l_stmt, 'UPPER(VALUE_V)', 'value_v');
5771: Execute immediate l_stmt;

Line 5768: l_stmt := 'DELETE FROM BSC_DB_TABLES WHERE '||strWhereInTables;

5764: i := BSC_METADATA_OPTIMIZER_PKG.garrTables.next(i);
5765: END LOOP;
5766: IF BSC_METADATA_OPTIMIZER_PKG.garrTables.count > 0 THEN
5767: --BSC_DB_TABLES
5768: l_stmt := 'DELETE FROM BSC_DB_TABLES WHERE '||strWhereInTables;
5769: l_stmt := replace(l_stmt, 'UPPER(TABLE_NAME)', 'table_name');
5770: l_stmt := replace(l_stmt, 'UPPER(VALUE_V)', 'value_v');
5771: Execute immediate l_stmt;
5772: --BSC_DB_TABLES_RELS

Line 5772: --BSC_DB_TABLES_RELS

5768: l_stmt := 'DELETE FROM BSC_DB_TABLES WHERE '||strWhereInTables;
5769: l_stmt := replace(l_stmt, 'UPPER(TABLE_NAME)', 'table_name');
5770: l_stmt := replace(l_stmt, 'UPPER(VALUE_V)', 'value_v');
5771: Execute immediate l_stmt;
5772: --BSC_DB_TABLES_RELS
5773: l_stmt := 'DELETE FROM BSC_DB_TABLES_RELS WHERE '|| strWhereInTables;
5774: l_stmt := replace(l_stmt, 'UPPER(TABLE_NAME)', 'table_name');
5775: l_stmt := replace(l_stmt, 'UPPER(VALUE_V)', 'value_v');
5776: Execute immediate l_stmt;

Line 5773: l_stmt := 'DELETE FROM BSC_DB_TABLES_RELS WHERE '|| strWhereInTables;

5769: l_stmt := replace(l_stmt, 'UPPER(TABLE_NAME)', 'table_name');
5770: l_stmt := replace(l_stmt, 'UPPER(VALUE_V)', 'value_v');
5771: Execute immediate l_stmt;
5772: --BSC_DB_TABLES_RELS
5773: l_stmt := 'DELETE FROM BSC_DB_TABLES_RELS WHERE '|| strWhereInTables;
5774: l_stmt := replace(l_stmt, 'UPPER(TABLE_NAME)', 'table_name');
5775: l_stmt := replace(l_stmt, 'UPPER(VALUE_V)', 'value_v');
5776: Execute immediate l_stmt;
5777: --BSC_DB_TABLES_COLS

Line 5777: --BSC_DB_TABLES_COLS

5773: l_stmt := 'DELETE FROM BSC_DB_TABLES_RELS WHERE '|| strWhereInTables;
5774: l_stmt := replace(l_stmt, 'UPPER(TABLE_NAME)', 'table_name');
5775: l_stmt := replace(l_stmt, 'UPPER(VALUE_V)', 'value_v');
5776: Execute immediate l_stmt;
5777: --BSC_DB_TABLES_COLS
5778: l_stmt := 'DELETE FROM BSC_DB_TABLES_COLS WHERE '|| strWhereInTables;
5779: l_stmt := replace(l_stmt, 'UPPER(TABLE_NAME)', 'table_name');
5780: l_stmt := replace(l_stmt, 'UPPER(VALUE_V)', 'value_v');
5781: Execute immediate l_stmt;

Line 5778: l_stmt := 'DELETE FROM BSC_DB_TABLES_COLS WHERE '|| strWhereInTables;

5774: l_stmt := replace(l_stmt, 'UPPER(TABLE_NAME)', 'table_name');
5775: l_stmt := replace(l_stmt, 'UPPER(VALUE_V)', 'value_v');
5776: Execute immediate l_stmt;
5777: --BSC_DB_TABLES_COLS
5778: l_stmt := 'DELETE FROM BSC_DB_TABLES_COLS WHERE '|| strWhereInTables;
5779: l_stmt := replace(l_stmt, 'UPPER(TABLE_NAME)', 'table_name');
5780: l_stmt := replace(l_stmt, 'UPPER(VALUE_V)', 'value_v');
5781: Execute immediate l_stmt;
5782: --BSC_DB_CALCULATIONS

Line 5893: --Update the range of years in BSC_DB_TABLES for tables

5889: IF Calendar.RangeYrMod = 1 THEN
5890: --There was a change in the range of years
5891: num_anos := Calendar.NumOfYears;
5892: num_anosant := Calendar.PreviousYears;
5893: --Update the range of years in BSC_DB_TABLES for tables
5894: --belonging to this calendar.
5895: --Remember that BSC Calendar is code -1 in gCalendars
5896: UPDATE BSC_DB_TABLES SET NUM_OF_YEARS = num_anos, PREVIOUS_YEARS = num_anosant
5897: WHERE PERIODICITY_ID IN (

Line 5896: UPDATE BSC_DB_TABLES SET NUM_OF_YEARS = num_anos, PREVIOUS_YEARS = num_anosant

5892: num_anosant := Calendar.PreviousYears;
5893: --Update the range of years in BSC_DB_TABLES for tables
5894: --belonging to this calendar.
5895: --Remember that BSC Calendar is code -1 in gCalendars
5896: UPDATE BSC_DB_TABLES SET NUM_OF_YEARS = num_anos, PREVIOUS_YEARS = num_anosant
5897: WHERE PERIODICITY_ID IN (
5898: SELECT PERIODICITY_ID FROM BSC_SYS_PERIODICITIES
5899: WHERE CALENDAR_ID = Calendar.Code );
5900: IF Calendar.Source = 'BSC' THEN

Line 6011: FROM bsc_db_tables_cols cols

6007: AND cond.session_id = :2
6008: AND last.column_type = :3
6009: MINUS
6010: SELECT cols.table_name, cols.column_name
6011: FROM bsc_db_tables_cols cols
6012: , bsc_db_tables_rels rels
6013: , bsc_tmp_big_in_cond cond
6014: WHERE cond.value_v = rels.source_table_name
6015: AND rels.table_name = cols.table_name

Line 6012: , bsc_db_tables_rels rels

6008: AND last.column_type = :3
6009: MINUS
6010: SELECT cols.table_name, cols.column_name
6011: FROM bsc_db_tables_cols cols
6012: , bsc_db_tables_rels rels
6013: , bsc_tmp_big_in_cond cond
6014: WHERE cond.value_v = rels.source_table_name
6015: AND rels.table_name = cols.table_name
6016: AND cond.variable_id = :4

Line 6054: DELETE bsc_db_tables_cols

6050: BSC_MO_HELPER_PKG.Do_DDL(l_stmt, ad_ddl.alter_table, p_Table_name );
6051: ELSE
6052: writeTmp(p_table_name||'.'||p_column_name||' doesnt exist, so not calling alter table as this was possibly dropped earlier');
6053: END IF;
6054: DELETE bsc_db_tables_cols
6055: WHERE table_name = p_Table_name
6056: AND column_name = p_column_name;
6057: END;
6058:

Line 6214: SELECT DISTINCT SOURCE_TABLE_NAME FROM BSC_DB_TABLES_RELS

6210: FROM BSC_KPI_DATA_TABLES
6211: WHERE TABLE_NAME IS NOT NULL )
6212: SELECT table_name from kpi_data
6213: UNION
6214: SELECT DISTINCT SOURCE_TABLE_NAME FROM BSC_DB_TABLES_RELS
6215: START WITH table_name IN (SELECT TABLE_NAME from kpi_data)
6216: CONNECT BY PRIOR source_table_name = table_name ; */
6217: CURSOR cAllTables IS
6218: select table_name from bsc_db_tables where table_type=0

Line 6218: select table_name from bsc_db_tables where table_type=0

6214: SELECT DISTINCT SOURCE_TABLE_NAME FROM BSC_DB_TABLES_RELS
6215: START WITH table_name IN (SELECT TABLE_NAME from kpi_data)
6216: CONNECT BY PRIOR source_table_name = table_name ; */
6217: CURSOR cAllTables IS
6218: select table_name from bsc_db_tables where table_type=0
6219: union all
6220: SELECT DISTINCT table_name
6221: from BSC_DB_TABLES_RELS
6222: START WITH source_TABLE_NAME IN

Line 6221: from BSC_DB_TABLES_RELS

6217: CURSOR cAllTables IS
6218: select table_name from bsc_db_tables where table_type=0
6219: union all
6220: SELECT DISTINCT table_name
6221: from BSC_DB_TABLES_RELS
6222: START WITH source_TABLE_NAME IN
6223: (select table_name from bsc_db_tables where table_type=0)
6224: CONNECT BY PRIOR TABLE_NAME = source_TABLE_NAME;
6225:

Line 6223: (select table_name from bsc_db_tables where table_type=0)

6219: union all
6220: SELECT DISTINCT table_name
6221: from BSC_DB_TABLES_RELS
6222: START WITH source_TABLE_NAME IN
6223: (select table_name from bsc_db_tables where table_type=0)
6224: CONNECT BY PRIOR TABLE_NAME = source_TABLE_NAME;
6225:
6226: l_drop_these VARCHAR2(32000);
6227: l_drop_threshold NUMBER := 200;

Line 6281: --Tables that are in BSC_DB_TABLES (excluding input tables for dimensions) and are not

6277: --BSC_MO_LOADER_CONFIG_PKG.InsertOriginTables (arrKpiTables, arrAllTables);
6278: numAllTables := arrAllTables.count;
6279: --So far the array arrAllTables() contains all input, base and summary tables
6280: --used in the whole system
6281: --Tables that are in BSC_DB_TABLES (excluding input tables for dimensions) and are not
6282: --in the array arrAllTables() are not used. We need to delete those tables from database
6283: -- and BSC metadata.
6284: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
6285: BSC_MO_HELPER_PKG.writeTmp('numAllTables = '||numAllTables||', arrAllTables.count = '||arrAllTables.count||' '||get_time);

Line 6289: --Tables that are in BSC_DB_TABLES (excluding input tables for dimensions) and are not

6285: BSC_MO_HELPER_PKG.writeTmp('numAllTables = '||numAllTables||', arrAllTables.count = '||arrAllTables.count||' '||get_time);
6286: END IF;
6287: --So far the array arrAllTables() contains all input, base and summary tables
6288: --used in the whole system
6289: --Tables that are in BSC_DB_TABLES (excluding input tables for dimensions) and are not
6290: --in the array arrAllTables() are not used. We need to delete those tables from database
6291: -- and BSC metadata.
6292: numNotUsedTables := 0;
6293: strWhereInCondition := Get_New_Big_In_Cond_Varchar2(1, 'TABLE_NAME');

Line 6296: l_stmt := 'SELECT DISTINCT TABLE_NAME FROM BSC_DB_TABLES WHERE TABLE_TYPE <> 2 AND NOT (' ||

6292: numNotUsedTables := 0;
6293: strWhereInCondition := Get_New_Big_In_Cond_Varchar2(1, 'TABLE_NAME');
6294: Add_Value_Bulk(1, arrAllTables);
6295:
6296: l_stmt := 'SELECT DISTINCT TABLE_NAME FROM BSC_DB_TABLES WHERE TABLE_TYPE <> 2 AND NOT (' ||
6297: strWhereInCondition || ')';
6298:
6299: l_stmt := 'SELECT DISTINCT TABLE_NAME FROM BSC_DB_TABLES WHERE TABLE_TYPE <> :1
6300: minus

Line 6299: l_stmt := 'SELECT DISTINCT TABLE_NAME FROM BSC_DB_TABLES WHERE TABLE_TYPE <> :1

6295:
6296: l_stmt := 'SELECT DISTINCT TABLE_NAME FROM BSC_DB_TABLES WHERE TABLE_TYPE <> 2 AND NOT (' ||
6297: strWhereInCondition || ')';
6298:
6299: l_stmt := 'SELECT DISTINCT TABLE_NAME FROM BSC_DB_TABLES WHERE TABLE_TYPE <> :1
6300: minus
6301: select upper(value_v) from bsc_tmp_big_in_cond where variable_id=:2 and session_id = :3';
6302: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
6303: BSC_MO_HELPER_PKG.writeTmp('l_stmt = '||l_stmt||' '||get_time);

Line 6340: --BSC_DB_TABLES

6336: IF (numNotUsedTables>l_drop_threshold) THEN
6337: Add_Value_Bulk(1, arrNotUsedTables);
6338: END IF;
6339: --Delete tables from BSC metadata
6340: --BSC_DB_TABLES
6341:
6342: IF (numNotUsedTables>l_drop_threshold) THEN
6343: writeTmp('Deleting entries from BSC_DB_TABLES '||get_time);
6344: bsc_metadata_optimizer_pkg.logProgress('CLEANUP', 'Deleting entries from BSC_DB_TABLES');

Line 6343: writeTmp('Deleting entries from BSC_DB_TABLES '||get_time);

6339: --Delete tables from BSC metadata
6340: --BSC_DB_TABLES
6341:
6342: IF (numNotUsedTables>l_drop_threshold) THEN
6343: writeTmp('Deleting entries from BSC_DB_TABLES '||get_time);
6344: bsc_metadata_optimizer_pkg.logProgress('CLEANUP', 'Deleting entries from BSC_DB_TABLES');
6345: EXECUTE IMMEDIATE ' DELETE FROM BSC_DB_TABLES WHERE '||strWhereInCondition;
6346: writeTmp('Deleting entries from BSC_DB_TABLES_RELS '||get_time);
6347: bsc_metadata_optimizer_pkg.logProgress('CLEANUP', 'Deleting entries from BSC_DB_TABLES_RELS');

Line 6344: bsc_metadata_optimizer_pkg.logProgress('CLEANUP', 'Deleting entries from BSC_DB_TABLES');

6340: --BSC_DB_TABLES
6341:
6342: IF (numNotUsedTables>l_drop_threshold) THEN
6343: writeTmp('Deleting entries from BSC_DB_TABLES '||get_time);
6344: bsc_metadata_optimizer_pkg.logProgress('CLEANUP', 'Deleting entries from BSC_DB_TABLES');
6345: EXECUTE IMMEDIATE ' DELETE FROM BSC_DB_TABLES WHERE '||strWhereInCondition;
6346: writeTmp('Deleting entries from BSC_DB_TABLES_RELS '||get_time);
6347: bsc_metadata_optimizer_pkg.logProgress('CLEANUP', 'Deleting entries from BSC_DB_TABLES_RELS');
6348: EXECUTE IMMEDIATE ' DELETE FROM BSC_DB_TABLES_RELS WHERE '|| strWhereInCondition;

Line 6345: EXECUTE IMMEDIATE ' DELETE FROM BSC_DB_TABLES WHERE '||strWhereInCondition;

6341:
6342: IF (numNotUsedTables>l_drop_threshold) THEN
6343: writeTmp('Deleting entries from BSC_DB_TABLES '||get_time);
6344: bsc_metadata_optimizer_pkg.logProgress('CLEANUP', 'Deleting entries from BSC_DB_TABLES');
6345: EXECUTE IMMEDIATE ' DELETE FROM BSC_DB_TABLES WHERE '||strWhereInCondition;
6346: writeTmp('Deleting entries from BSC_DB_TABLES_RELS '||get_time);
6347: bsc_metadata_optimizer_pkg.logProgress('CLEANUP', 'Deleting entries from BSC_DB_TABLES_RELS');
6348: EXECUTE IMMEDIATE ' DELETE FROM BSC_DB_TABLES_RELS WHERE '|| strWhereInCondition;
6349: writeTmp('Deleting entries from BSC_DB_TABLES_COLS '||get_time);

Line 6346: writeTmp('Deleting entries from BSC_DB_TABLES_RELS '||get_time);

6342: IF (numNotUsedTables>l_drop_threshold) THEN
6343: writeTmp('Deleting entries from BSC_DB_TABLES '||get_time);
6344: bsc_metadata_optimizer_pkg.logProgress('CLEANUP', 'Deleting entries from BSC_DB_TABLES');
6345: EXECUTE IMMEDIATE ' DELETE FROM BSC_DB_TABLES WHERE '||strWhereInCondition;
6346: writeTmp('Deleting entries from BSC_DB_TABLES_RELS '||get_time);
6347: bsc_metadata_optimizer_pkg.logProgress('CLEANUP', 'Deleting entries from BSC_DB_TABLES_RELS');
6348: EXECUTE IMMEDIATE ' DELETE FROM BSC_DB_TABLES_RELS WHERE '|| strWhereInCondition;
6349: writeTmp('Deleting entries from BSC_DB_TABLES_COLS '||get_time);
6350: bsc_metadata_optimizer_pkg.logProgress('CLEANUP', 'Deleting entries from BSC_DB_TABLES_COLS');

Line 6347: bsc_metadata_optimizer_pkg.logProgress('CLEANUP', 'Deleting entries from BSC_DB_TABLES_RELS');

6343: writeTmp('Deleting entries from BSC_DB_TABLES '||get_time);
6344: bsc_metadata_optimizer_pkg.logProgress('CLEANUP', 'Deleting entries from BSC_DB_TABLES');
6345: EXECUTE IMMEDIATE ' DELETE FROM BSC_DB_TABLES WHERE '||strWhereInCondition;
6346: writeTmp('Deleting entries from BSC_DB_TABLES_RELS '||get_time);
6347: bsc_metadata_optimizer_pkg.logProgress('CLEANUP', 'Deleting entries from BSC_DB_TABLES_RELS');
6348: EXECUTE IMMEDIATE ' DELETE FROM BSC_DB_TABLES_RELS WHERE '|| strWhereInCondition;
6349: writeTmp('Deleting entries from BSC_DB_TABLES_COLS '||get_time);
6350: bsc_metadata_optimizer_pkg.logProgress('CLEANUP', 'Deleting entries from BSC_DB_TABLES_COLS');
6351: EXECUTE IMMEDIATE ' DELETE FROM BSC_DB_TABLES_COLS WHERE '|| strWhereInCondition;

Line 6348: EXECUTE IMMEDIATE ' DELETE FROM BSC_DB_TABLES_RELS WHERE '|| strWhereInCondition;

6344: bsc_metadata_optimizer_pkg.logProgress('CLEANUP', 'Deleting entries from BSC_DB_TABLES');
6345: EXECUTE IMMEDIATE ' DELETE FROM BSC_DB_TABLES WHERE '||strWhereInCondition;
6346: writeTmp('Deleting entries from BSC_DB_TABLES_RELS '||get_time);
6347: bsc_metadata_optimizer_pkg.logProgress('CLEANUP', 'Deleting entries from BSC_DB_TABLES_RELS');
6348: EXECUTE IMMEDIATE ' DELETE FROM BSC_DB_TABLES_RELS WHERE '|| strWhereInCondition;
6349: writeTmp('Deleting entries from BSC_DB_TABLES_COLS '||get_time);
6350: bsc_metadata_optimizer_pkg.logProgress('CLEANUP', 'Deleting entries from BSC_DB_TABLES_COLS');
6351: EXECUTE IMMEDIATE ' DELETE FROM BSC_DB_TABLES_COLS WHERE '|| strWhereInCondition;
6352: writeTmp('Deleting entries from BSC_DB_CALCULATIONS '||get_time);

Line 6349: writeTmp('Deleting entries from BSC_DB_TABLES_COLS '||get_time);

6345: EXECUTE IMMEDIATE ' DELETE FROM BSC_DB_TABLES WHERE '||strWhereInCondition;
6346: writeTmp('Deleting entries from BSC_DB_TABLES_RELS '||get_time);
6347: bsc_metadata_optimizer_pkg.logProgress('CLEANUP', 'Deleting entries from BSC_DB_TABLES_RELS');
6348: EXECUTE IMMEDIATE ' DELETE FROM BSC_DB_TABLES_RELS WHERE '|| strWhereInCondition;
6349: writeTmp('Deleting entries from BSC_DB_TABLES_COLS '||get_time);
6350: bsc_metadata_optimizer_pkg.logProgress('CLEANUP', 'Deleting entries from BSC_DB_TABLES_COLS');
6351: EXECUTE IMMEDIATE ' DELETE FROM BSC_DB_TABLES_COLS WHERE '|| strWhereInCondition;
6352: writeTmp('Deleting entries from BSC_DB_CALCULATIONS '||get_time);
6353: bsc_metadata_optimizer_pkg.logProgress('CLEANUP', 'Deleting entries from BSC_DB_CALCULATIONS');

Line 6350: bsc_metadata_optimizer_pkg.logProgress('CLEANUP', 'Deleting entries from BSC_DB_TABLES_COLS');

6346: writeTmp('Deleting entries from BSC_DB_TABLES_RELS '||get_time);
6347: bsc_metadata_optimizer_pkg.logProgress('CLEANUP', 'Deleting entries from BSC_DB_TABLES_RELS');
6348: EXECUTE IMMEDIATE ' DELETE FROM BSC_DB_TABLES_RELS WHERE '|| strWhereInCondition;
6349: writeTmp('Deleting entries from BSC_DB_TABLES_COLS '||get_time);
6350: bsc_metadata_optimizer_pkg.logProgress('CLEANUP', 'Deleting entries from BSC_DB_TABLES_COLS');
6351: EXECUTE IMMEDIATE ' DELETE FROM BSC_DB_TABLES_COLS WHERE '|| strWhereInCondition;
6352: writeTmp('Deleting entries from BSC_DB_CALCULATIONS '||get_time);
6353: bsc_metadata_optimizer_pkg.logProgress('CLEANUP', 'Deleting entries from BSC_DB_CALCULATIONS');
6354: EXECUTE IMMEDIATE ' DELETE FROM BSC_DB_CALCULATIONS WHERE '|| strWhereInCondition;

Line 6351: EXECUTE IMMEDIATE ' DELETE FROM BSC_DB_TABLES_COLS WHERE '|| strWhereInCondition;

6347: bsc_metadata_optimizer_pkg.logProgress('CLEANUP', 'Deleting entries from BSC_DB_TABLES_RELS');
6348: EXECUTE IMMEDIATE ' DELETE FROM BSC_DB_TABLES_RELS WHERE '|| strWhereInCondition;
6349: writeTmp('Deleting entries from BSC_DB_TABLES_COLS '||get_time);
6350: bsc_metadata_optimizer_pkg.logProgress('CLEANUP', 'Deleting entries from BSC_DB_TABLES_COLS');
6351: EXECUTE IMMEDIATE ' DELETE FROM BSC_DB_TABLES_COLS WHERE '|| strWhereInCondition;
6352: writeTmp('Deleting entries from BSC_DB_CALCULATIONS '||get_time);
6353: bsc_metadata_optimizer_pkg.logProgress('CLEANUP', 'Deleting entries from BSC_DB_CALCULATIONS');
6354: EXECUTE IMMEDIATE ' DELETE FROM BSC_DB_CALCULATIONS WHERE '|| strWhereInCondition;
6355: ELSE

Line 6357: writeTmp('Deleting entries from BSC_DB_TABLES '||get_time);

6353: bsc_metadata_optimizer_pkg.logProgress('CLEANUP', 'Deleting entries from BSC_DB_CALCULATIONS');
6354: EXECUTE IMMEDIATE ' DELETE FROM BSC_DB_CALCULATIONS WHERE '|| strWhereInCondition;
6355: ELSE
6356: l_drop_these := substr(l_drop_these, 1, length(l_drop_these)-1)||')';
6357: writeTmp('Deleting entries from BSC_DB_TABLES '||get_time);
6358: bsc_metadata_optimizer_pkg.logProgress('CLEANUP', 'Deleting entries from BSC_DB_TABLES');
6359: EXECUTE IMMEDIATE ' DELETE FROM BSC_DB_TABLES WHERE table_name IN '||l_drop_these;
6360: writeTmp('Deleting entries from BSC_DB_TABLES_RELS '||get_time);
6361: bsc_metadata_optimizer_pkg.logProgress('CLEANUP', 'Deleting entries from BSC_DB_TABLES_RELS');

Line 6358: bsc_metadata_optimizer_pkg.logProgress('CLEANUP', 'Deleting entries from BSC_DB_TABLES');

6354: EXECUTE IMMEDIATE ' DELETE FROM BSC_DB_CALCULATIONS WHERE '|| strWhereInCondition;
6355: ELSE
6356: l_drop_these := substr(l_drop_these, 1, length(l_drop_these)-1)||')';
6357: writeTmp('Deleting entries from BSC_DB_TABLES '||get_time);
6358: bsc_metadata_optimizer_pkg.logProgress('CLEANUP', 'Deleting entries from BSC_DB_TABLES');
6359: EXECUTE IMMEDIATE ' DELETE FROM BSC_DB_TABLES WHERE table_name IN '||l_drop_these;
6360: writeTmp('Deleting entries from BSC_DB_TABLES_RELS '||get_time);
6361: bsc_metadata_optimizer_pkg.logProgress('CLEANUP', 'Deleting entries from BSC_DB_TABLES_RELS');
6362: EXECUTE IMMEDIATE ' DELETE FROM BSC_DB_TABLES_RELS WHERE table_name IN '||l_drop_these;

Line 6359: EXECUTE IMMEDIATE ' DELETE FROM BSC_DB_TABLES WHERE table_name IN '||l_drop_these;

6355: ELSE
6356: l_drop_these := substr(l_drop_these, 1, length(l_drop_these)-1)||')';
6357: writeTmp('Deleting entries from BSC_DB_TABLES '||get_time);
6358: bsc_metadata_optimizer_pkg.logProgress('CLEANUP', 'Deleting entries from BSC_DB_TABLES');
6359: EXECUTE IMMEDIATE ' DELETE FROM BSC_DB_TABLES WHERE table_name IN '||l_drop_these;
6360: writeTmp('Deleting entries from BSC_DB_TABLES_RELS '||get_time);
6361: bsc_metadata_optimizer_pkg.logProgress('CLEANUP', 'Deleting entries from BSC_DB_TABLES_RELS');
6362: EXECUTE IMMEDIATE ' DELETE FROM BSC_DB_TABLES_RELS WHERE table_name IN '||l_drop_these;
6363: writeTmp('Deleting entries from BSC_DB_TABLES_COLS '||get_time);

Line 6360: writeTmp('Deleting entries from BSC_DB_TABLES_RELS '||get_time);

6356: l_drop_these := substr(l_drop_these, 1, length(l_drop_these)-1)||')';
6357: writeTmp('Deleting entries from BSC_DB_TABLES '||get_time);
6358: bsc_metadata_optimizer_pkg.logProgress('CLEANUP', 'Deleting entries from BSC_DB_TABLES');
6359: EXECUTE IMMEDIATE ' DELETE FROM BSC_DB_TABLES WHERE table_name IN '||l_drop_these;
6360: writeTmp('Deleting entries from BSC_DB_TABLES_RELS '||get_time);
6361: bsc_metadata_optimizer_pkg.logProgress('CLEANUP', 'Deleting entries from BSC_DB_TABLES_RELS');
6362: EXECUTE IMMEDIATE ' DELETE FROM BSC_DB_TABLES_RELS WHERE table_name IN '||l_drop_these;
6363: writeTmp('Deleting entries from BSC_DB_TABLES_COLS '||get_time);
6364: bsc_metadata_optimizer_pkg.logProgress('CLEANUP', 'Deleting entries from BSC_DB_TABLES_COLS');

Line 6361: bsc_metadata_optimizer_pkg.logProgress('CLEANUP', 'Deleting entries from BSC_DB_TABLES_RELS');

6357: writeTmp('Deleting entries from BSC_DB_TABLES '||get_time);
6358: bsc_metadata_optimizer_pkg.logProgress('CLEANUP', 'Deleting entries from BSC_DB_TABLES');
6359: EXECUTE IMMEDIATE ' DELETE FROM BSC_DB_TABLES WHERE table_name IN '||l_drop_these;
6360: writeTmp('Deleting entries from BSC_DB_TABLES_RELS '||get_time);
6361: bsc_metadata_optimizer_pkg.logProgress('CLEANUP', 'Deleting entries from BSC_DB_TABLES_RELS');
6362: EXECUTE IMMEDIATE ' DELETE FROM BSC_DB_TABLES_RELS WHERE table_name IN '||l_drop_these;
6363: writeTmp('Deleting entries from BSC_DB_TABLES_COLS '||get_time);
6364: bsc_metadata_optimizer_pkg.logProgress('CLEANUP', 'Deleting entries from BSC_DB_TABLES_COLS');
6365: EXECUTE IMMEDIATE ' DELETE FROM BSC_DB_TABLES_COLS WHERE table_name IN '||l_drop_these;

Line 6362: EXECUTE IMMEDIATE ' DELETE FROM BSC_DB_TABLES_RELS WHERE table_name IN '||l_drop_these;

6358: bsc_metadata_optimizer_pkg.logProgress('CLEANUP', 'Deleting entries from BSC_DB_TABLES');
6359: EXECUTE IMMEDIATE ' DELETE FROM BSC_DB_TABLES WHERE table_name IN '||l_drop_these;
6360: writeTmp('Deleting entries from BSC_DB_TABLES_RELS '||get_time);
6361: bsc_metadata_optimizer_pkg.logProgress('CLEANUP', 'Deleting entries from BSC_DB_TABLES_RELS');
6362: EXECUTE IMMEDIATE ' DELETE FROM BSC_DB_TABLES_RELS WHERE table_name IN '||l_drop_these;
6363: writeTmp('Deleting entries from BSC_DB_TABLES_COLS '||get_time);
6364: bsc_metadata_optimizer_pkg.logProgress('CLEANUP', 'Deleting entries from BSC_DB_TABLES_COLS');
6365: EXECUTE IMMEDIATE ' DELETE FROM BSC_DB_TABLES_COLS WHERE table_name IN '||l_drop_these;
6366: writeTmp('Deleting entries from BSC_DB_CALCULATIONS '||get_time);

Line 6363: writeTmp('Deleting entries from BSC_DB_TABLES_COLS '||get_time);

6359: EXECUTE IMMEDIATE ' DELETE FROM BSC_DB_TABLES WHERE table_name IN '||l_drop_these;
6360: writeTmp('Deleting entries from BSC_DB_TABLES_RELS '||get_time);
6361: bsc_metadata_optimizer_pkg.logProgress('CLEANUP', 'Deleting entries from BSC_DB_TABLES_RELS');
6362: EXECUTE IMMEDIATE ' DELETE FROM BSC_DB_TABLES_RELS WHERE table_name IN '||l_drop_these;
6363: writeTmp('Deleting entries from BSC_DB_TABLES_COLS '||get_time);
6364: bsc_metadata_optimizer_pkg.logProgress('CLEANUP', 'Deleting entries from BSC_DB_TABLES_COLS');
6365: EXECUTE IMMEDIATE ' DELETE FROM BSC_DB_TABLES_COLS WHERE table_name IN '||l_drop_these;
6366: writeTmp('Deleting entries from BSC_DB_CALCULATIONS '||get_time);
6367: bsc_metadata_optimizer_pkg.logProgress('CLEANUP', 'Deleting entries from BSC_DB_CALCULATIONS');

Line 6364: bsc_metadata_optimizer_pkg.logProgress('CLEANUP', 'Deleting entries from BSC_DB_TABLES_COLS');

6360: writeTmp('Deleting entries from BSC_DB_TABLES_RELS '||get_time);
6361: bsc_metadata_optimizer_pkg.logProgress('CLEANUP', 'Deleting entries from BSC_DB_TABLES_RELS');
6362: EXECUTE IMMEDIATE ' DELETE FROM BSC_DB_TABLES_RELS WHERE table_name IN '||l_drop_these;
6363: writeTmp('Deleting entries from BSC_DB_TABLES_COLS '||get_time);
6364: bsc_metadata_optimizer_pkg.logProgress('CLEANUP', 'Deleting entries from BSC_DB_TABLES_COLS');
6365: EXECUTE IMMEDIATE ' DELETE FROM BSC_DB_TABLES_COLS WHERE table_name IN '||l_drop_these;
6366: writeTmp('Deleting entries from BSC_DB_CALCULATIONS '||get_time);
6367: bsc_metadata_optimizer_pkg.logProgress('CLEANUP', 'Deleting entries from BSC_DB_CALCULATIONS');
6368: EXECUTE IMMEDIATE ' DELETE FROM BSC_DB_CALCULATIONS WHERE table_name IN '||l_drop_these;

Line 6365: EXECUTE IMMEDIATE ' DELETE FROM BSC_DB_TABLES_COLS WHERE table_name IN '||l_drop_these;

6361: bsc_metadata_optimizer_pkg.logProgress('CLEANUP', 'Deleting entries from BSC_DB_TABLES_RELS');
6362: EXECUTE IMMEDIATE ' DELETE FROM BSC_DB_TABLES_RELS WHERE table_name IN '||l_drop_these;
6363: writeTmp('Deleting entries from BSC_DB_TABLES_COLS '||get_time);
6364: bsc_metadata_optimizer_pkg.logProgress('CLEANUP', 'Deleting entries from BSC_DB_TABLES_COLS');
6365: EXECUTE IMMEDIATE ' DELETE FROM BSC_DB_TABLES_COLS WHERE table_name IN '||l_drop_these;
6366: writeTmp('Deleting entries from BSC_DB_CALCULATIONS '||get_time);
6367: bsc_metadata_optimizer_pkg.logProgress('CLEANUP', 'Deleting entries from BSC_DB_CALCULATIONS');
6368: EXECUTE IMMEDIATE ' DELETE FROM BSC_DB_CALCULATIONS WHERE table_name IN '||l_drop_these;
6369: END IF;

Line 7117: FROM BSC_SYS_DIM_LEVELS_B S, BSC_DB_TABLES_RELS R

7113: cv CurTyp;
7114: cursor c1 is
7115: SELECT S.DIM_LEVEL_ID, S.LEVEL_TABLE_NAME, S.TABLE_TYPE, S.source,
7116: S.LEVEL_PK_COL, S.USER_KEY_SIZE, S.DISP_KEY_SIZE, NVL(S.EDW_FLAG, 0) AS EDW_FLAG, R.SOURCE_TABLE_NAME
7117: FROM BSC_SYS_DIM_LEVELS_B S, BSC_DB_TABLES_RELS R
7118: WHERE S.LEVEL_TABLE_NAME = R.TABLE_NAME (+)
7119: ORDER BY LEVEL_TABLE_NAME;
7120: cRow1 c1%ROWTYPE;
7121:

Line 7152: FROM BSC_SYS_DIM_LEVEL_RELS D, BSC_DB_TABLES_RELS T

7148: cRow3 c3%ROWTYPE;
7149:
7150: cursor c4 is
7151: SELECT DISTINCT D.RELATION_COL, T.SOURCE_TABLE_NAME
7152: FROM BSC_SYS_DIM_LEVEL_RELS D, BSC_DB_TABLES_RELS T
7153: WHERE D.RELATION_TYPE = 2 AND D.RELATION_COL = T.TABLE_NAME (+);
7154:
7155: cRow4 c4%ROWTYPE;
7156: