[Home] [Help]
105: EXIT WHEN l_count = bsc_metadata_optimizer_pkg.g_dropAppsTables.last;
106: l_count := bsc_metadata_optimizer_pkg.g_dropAppsTables.next(l_count);
107: END LOOP;
108: END;
109: -- performance fix, query bsc_kpi_Data_tables instead of bsc_kpi_data_tables_last
110: PROCEDURE CreateKPIDataTableTmp IS
111: TableName varchar2(30);
112: l_stmt varchar2(1000);
113:
115: bsc_metadata_optimizer_pkg.logProgress('INIT', 'Starting CreateKPIDataTableTmp');
116: TableName := BSC_METADATA_OPTIMIZER_PKG.g_kpi_tmp_table ;
117: DropTable(TableName);
118: l_stmt := 'create table '||TableName||' TABLESPACE '|| BSC_METADATA_OPTIMIZER_PKG.gSummaryTableTbsName||' as ';
119: --select distinct indicator, dim_set_id, table_name from bsc_kpi_data_tables';
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
143: writeTmp('Exception in CreateKPIDataTableTmp stmt= :'||l_stmt, FND_LOG.LEVEL_EXCEPTION, true);
144: raise;
145: END;
146:
147: -- performance fix, query bsc_kpi_Data_tables_ind instead of bsc_kpi_data_tables_last
148: PROCEDURE CreateDBMeasureByDimSetTmp IS
149: TableName varchar2(30);
150: l_stmt varchar2(1000);
151: l_count NUMBER;
158: END;
159:
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
3249:
3250: If numNewTables > 0 Then
3251: --if one table of one indicator is marked then all tables of this indicator are marked
3252: --EDW Integration note:
3253: --In BSC_KPI_DATA_TABLES, Metadata Optimizer is storing the name of the view (Example: BSC_3001_0_0_5_V)
3254: --and the name of the S table for BSC Kpis (Example: BSC_3002_0_0_5)
3255: --In this procedure we need to get tables names from a view BSC_KPI_DATA_TABLES_V.
3256:
3257: --BSC-MV Note: We are going to use BSC_KPI_DATA_TABLES in all the code.
3251: --if one table of one indicator is marked then all tables of this indicator are marked
3252: --EDW Integration note:
3253: --In BSC_KPI_DATA_TABLES, Metadata Optimizer is storing the name of the view (Example: BSC_3001_0_0_5_V)
3254: --and the name of the S table for BSC Kpis (Example: BSC_3002_0_0_5)
3255: --In this procedure we need to get tables names from a view BSC_KPI_DATA_TABLES_V.
3256:
3257: --BSC-MV Note: We are going to use BSC_KPI_DATA_TABLES in all the code.
3258: --EDW logic is not used and need to be reviewd in the future.
3259:
3253: --In BSC_KPI_DATA_TABLES, Metadata Optimizer is storing the name of the view (Example: BSC_3001_0_0_5_V)
3254: --and the name of the S table for BSC Kpis (Example: BSC_3002_0_0_5)
3255: --In this procedure we need to get tables names from a view BSC_KPI_DATA_TABLES_V.
3256:
3257: --BSC-MV Note: We are going to use BSC_KPI_DATA_TABLES in all the code.
3258: --EDW logic is not used and need to be reviewd in the future.
3259:
3260: strWhereNotInNewTables := 'NOT (' || strWhereInNewTables || ')';
3261: l_stmt := 'SELECT TABLE_NAME FROM '||BSC_METADATA_OPTIMIZER_PKG.g_kpi_tmp_table||' WHERE INDICATOR IN (
4077:
4078: END;
4079:
4080: -- Given a list of production indicators, get the list of production tables they need
4081: -- Start with BSC_KPI_DATA_TABLES to get the S table name and then move from S to I table
4082:
4083: FUNCTION GetAffectedProductionTables(inIndicators in VARCHAR2) return dbms_Sql.varchar2_table IS
4084:
4085: cv CurTyp;
4130: END IF;
4131:
4132: --Initialize the array garrTables the tables used by the indicators in the array garrIndics()
4133: --EDW Integration note:
4134: --In BSC_KPI_DATA_TABLES, Metadata Optimizer is storing the name of the view (Example: BSC_3001_0_0_5_V)
4135: --and the name of the S table for BSC Kpis (Example: BSC_3002_0_0_5)
4136: --In this procedure we need to get tables names from a view BSC_KPI_DATA_TABLES_V.
4137:
4138: BSC_METADATA_OPTIMIZER_PKG.gnumTables := 0;
4132: --Initialize the array garrTables the tables used by the indicators in the array garrIndics()
4133: --EDW Integration note:
4134: --In BSC_KPI_DATA_TABLES, Metadata Optimizer is storing the name of the view (Example: BSC_3001_0_0_5_V)
4135: --and the name of the S table for BSC Kpis (Example: BSC_3002_0_0_5)
4136: --In this procedure we need to get tables names from a view BSC_KPI_DATA_TABLES_V.
4137:
4138: BSC_METADATA_OPTIMIZER_PKG.gnumTables := 0;
4139: BSC_METADATA_OPTIMIZER_PKG.garrTables.delete;
4140:
4147: END IF;
4148: Add_Value_Bulk(20, BSC_METADATA_OPTIMIZER_PKG.garrIndics);
4149: strWhereNotInIndics := 'NOT ('|| strWhereInIndics ||')';
4150: --Bug 5138449, dont use tmp table, use kpi_data_tables as we need all S tables, not just the lowest level S tables
4151: l_stmt := ' SELECT DISTINCT TABLE_NAME FROM BSC_KPI_DATA_TABLES WHERE ('||
4152: strWhereInIndics|| ') AND TABLE_NAME IS NOT NULL';
4153: writeTmp( 'l_stmt = '||l_stmt, FND_LOG.LEVEL_STATEMENT, false);
4154: OPEN cv for l_stmt;
4155: LOOP
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: ***************************************************************************/
5036: PROCEDURE CreateLastTables IS
5078: IF BSC_METADATA_OPTIMIZER_PKG.gGAA_RUN_MODE <>0 AND BSC_METADATA_OPTIMIZER_PKG.gIndicators.count < l_threshold THEN
5079: l_where_clause := ' WHERE indicator in
5080: (select indicator from bsc_tmp_opt_ui_kpis where process_id='||BSC_METADATA_OPTIMIZER_PKG.g_processID||')';
5081: END IF;
5082: TableName := 'BSC_KPI_DATA_TABLES';
5083: --DropTable(TableName||'_LAST');
5084: CreateCopyTable(TableName, BSC_METADATA_OPTIMIZER_PKG.g_kpi_data_last, BSC_METADATA_OPTIMIZER_PKG.gSummaryTableTbsName, l_where_clause);
5085: CreateCopyIndexes(TableName, BSC_METADATA_OPTIMIZER_PKG.g_kpi_data_last, BSC_METADATA_OPTIMIZER_PKG.gSummaryIndexTbsName);
5086: dbms_stats.gather_table_stats(BSC_METADATA_OPTIMIZER_PKG.gBscSchema, BSC_METADATA_OPTIMIZER_PKG.g_kpi_data_last);
5596:
5597: IF BSC_METADATA_OPTIMIZER_PKG.garrIndics.count > 0 THEN
5598: --Set the prototype flag to 3 for the indicators that are going to be re-created
5599: --So, if Metadata fail, those indicators are marked and the next time
5600: --will be recreated and no matter that the configuration of BSC_KPI_DATA_TABLES
5601: --had been deleted.
5602: --Also, since the documentation is done for all indicators, the documentation
5603: --re-load all indicator from the database. So, it is necessary to update the
5604: --prototype flag with the correct one (remember that due to the relations between
5610: LAST_UPDATE_DATE = SYSDATE WHERE '|| strWhereInIndics;
5611: execute immediate l_stmt using BSC_METADATA_OPTIMIZER_PKG.gUserId;
5612: END IF;
5613: --BSC-MV Note: Drop all the MV used for those KPis
5614: l_stmt := 'SELECT DISTINCT MV_NAME FROM BSC_KPI_DATA_TABLES WHERE ('|| strWhereInIndics ||') AND MV_NAME IS NOT NULL';
5615: writeTmp(l_stmt);
5616: OPEN cv FOR l_stmt;
5617: LOOP
5618: FETCH cv INTO mv_name;
5637: BSC_MO_HELPER_PKG.CheckError('BSC_BIA_WRAPPER.Drop_Summary_MV_VB');
5638: END LOOP;
5639: Close cv;
5640: --BSC-MV Note: Drop all tables created for projections
5641: l_stmt := 'SELECT DISTINCT PROJECTION_DATA FROM BSC_KPI_DATA_TABLES WHERE ('|| strWhereInIndics || ')
5642: AND PROJECTION_DATA IS NOT NULL';
5643: OPEN cv FOR l_stmt;
5644: LOOP
5645: FETCH cv INTO pt_name;
5648: Droptable( pt_name);
5649: End If;
5650: END LOOP;
5651: CLOSE cv;
5652: --Update column TABLE_NAME to NULL in BSC_KPI_DATA_TABLES
5653: --BSC-MV Note: Set MV_NAME to NULL in BSC_KPI_DATA_TABLES
5654: l_stmt := 'UPDATE BSC_KPI_DATA_TABLES
5655: SET TABLE_NAME = NULL, MV_NAME = NULL, DATA_SOURCE = NULL,
5656: SQL_STMT = NULL, PROJECTION_SOURCE = 0, PROJECTION_DATA = NULL
5649: End If;
5650: END LOOP;
5651: CLOSE cv;
5652: --Update column TABLE_NAME to NULL in BSC_KPI_DATA_TABLES
5653: --BSC-MV Note: Set MV_NAME to NULL in BSC_KPI_DATA_TABLES
5654: l_stmt := 'UPDATE BSC_KPI_DATA_TABLES
5655: SET TABLE_NAME = NULL, MV_NAME = NULL, DATA_SOURCE = NULL,
5656: SQL_STMT = NULL, PROJECTION_SOURCE = 0, PROJECTION_DATA = NULL
5657: WHERE '|| strWhereInIndics;
5650: END LOOP;
5651: CLOSE cv;
5652: --Update column TABLE_NAME to NULL in BSC_KPI_DATA_TABLES
5653: --BSC-MV Note: Set MV_NAME to NULL in BSC_KPI_DATA_TABLES
5654: l_stmt := 'UPDATE BSC_KPI_DATA_TABLES
5655: SET TABLE_NAME = NULL, MV_NAME = NULL, DATA_SOURCE = NULL,
5656: SQL_STMT = NULL, PROJECTION_SOURCE = 0, PROJECTION_DATA = NULL
5657: WHERE '|| strWhereInIndics;
5658: writeTmp(l_stmt, fnd_log.level_statement, false);
5660: END IF;
5661: IF BSC_METADATA_OPTIMIZER_PKG.garrIndics4.count > 0 THEN
5662: --Set the prototype flag to 4 for the indicators that are going to be re-configured
5663: --So, if Metadata fail, those indicators are marked and the next time
5664: --will be re-configured no matter that the configuration of BSC_KPI_DATA_TABLES
5665: --had been deleted.
5666: --Also, since the documentation is done for all indicators, the documentation
5667: --re-load all indicator from the database. So, it is necessary to update the
5668: --prototype flag with the correct one (remember that due to the relations between
5679: Execute immediate l_stmt using BSC_METADATA_OPTIMIZER_PKG.gUserId;
5680: END IF;
5681: --BSC-MV Note: If there is summarization level change (example from 3 to 2 or 2 to 3)
5682: --It is necessary to drop the existing MV of the indicator. They will be re-created
5683: --and bSC_KPI_DATA_TABLES will be reconfigured
5684: If BSC_METADATA_OPTIMIZER_PKG.g_Sum_Level_Change = 2 Then
5685: --BSC-MV Note: Drop all the MV used for those KPis
5686: l_stmt := 'SELECT DISTINCT MV_NAME FROM BSC_KPI_DATA_TABLES WHERE ('|| strWhereInIndics4 ||')';
5687: If BSC_METADATA_OPTIMIZER_PKG.garrIndics.count > 0 Then
5682: --It is necessary to drop the existing MV of the indicator. They will be re-created
5683: --and bSC_KPI_DATA_TABLES will be reconfigured
5684: If BSC_METADATA_OPTIMIZER_PKG.g_Sum_Level_Change = 2 Then
5685: --BSC-MV Note: Drop all the MV used for those KPis
5686: l_stmt := 'SELECT DISTINCT MV_NAME FROM BSC_KPI_DATA_TABLES WHERE ('|| strWhereInIndics4 ||')';
5687: If BSC_METADATA_OPTIMIZER_PKG.garrIndics.count > 0 Then
5688: l_stmt := l_stmt ||' AND (' || strWhereNotInIndics || ')';
5689: End If;
5690: l_stmt := l_stmt ||' AND MV_NAME IS NOT NULL';
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;
5711:
6206: l_index NUMBER;
6207:
6208: /*CURSOR cAllTables IS
6209: with kpi_data as(SELECT DISTINCT TABLE_NAME
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
6589: IF (BSC_METADATA_OPTIMIZER_PKG.gIndicators(i).Action_Flag=2) THEN
6590: --Case 2
6591: DELETE FROM BSC_KPIS_B WHERE INDICATOR = BSC_METADATA_OPTIMIZER_PKG.gIndicators(i).Code;
6592: DELETE FROM BSC_KPIS_TL WHERE INDICATOR = BSC_METADATA_OPTIMIZER_PKG.gIndicators(i).code;
6593: DELETE FROM BSC_KPI_DATA_TABLES WHERE INDICATOR = BSC_METADATA_OPTIMIZER_PKG.gIndicators(i).code;
6594: writeTmp('Deleting entries for indicator='||BSC_METADATA_OPTIMIZER_PKG.gIndicators(i).Code||', old value='||BSC_METADATA_OPTIMIZER_PKG.gIndicators(i).Action_Flag, FND_LOG.LEVEL_STATEMENT, false);
6595: ELSIF (BSC_METADATA_OPTIMIZER_PKG.gIndicators(i).Action_Flag=1 OR BSC_METADATA_OPTIMIZER_PKG.gIndicators(i).Action_Flag=3) THEN
6596: --Case 1, 3
6597: UPDATE BSC_KPIS_B SET PROTOTYPE_FLAG = 0,