[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE InsertRelatedTables(numTables IN NUMBER) IS
arrNewTables dbms_sql.varchar2_table;
l_stmt := ' INSERT INTO '||BSC_METADATA_OPTIMIZER_PKG.gBSCSchema||'.BSC_TMP_BIG_IN_COND (session_id, variable_id, value_v)
SELECT distinct userenv(''SESSIONID''), 0, TABLE_NAME
FROM BSC_DB_TABLES_RELS rels
where source_table_name IN
(SELECT /*+ index(tmp bsc_tmp_big_in_cond_n1)*/
tmp.value_v
from bsc_tmp_big_in_cond tmp
where tmp.session_id = userenv(''SESSIONID'') and tmp.variable_id = 0)
minus
select /*+ index(cond bsc_tmp_big_in_cond_n1)*/ distinct userenv(''SESSIONID''), 0, value_v
from bsc_tmp_big_in_cond cond where cond.session_id =userenv(''SESSIONID'')
and cond.variable_id = 0';
l_stmt := 'INSERT INTO '||BSC_METADATA_OPTIMIZER_PKG.gBSCSchema||'.BSC_TMP_BIG_IN_COND (session_id, variable_id, value_v)
SELECT distinct userenv(''SESSIONID''), 0, SOURCE_TABLE_NAME
FROM BSC_DB_TABLES_RELS
WHERE table_name IN
(SELECT /*+ index(cond bsc_tmp_big_in_cond_n1)*/ value_v
from BSC_TMP_BIG_IN_COND WHERE session_id = userenv(''SESSIONID'') and variable_id = 0)
minus
select /*+ index(cond bsc_tmp_big_in_cond_n1)*/ userenv(''SESSIONID''), 0, value_v
from bsc_tmp_big_in_cond cond where session_id =userenv(''SESSIONID'') and variable_id = 0';
l_stmt := 'INSERT INTO '||BSC_METADATA_OPTIMIZER_PKG.gBSCSchema||'.BSC_TMP_BIG_IN_COND (session_id, variable_id, value_v)
SELECT/*+ ordered */
distinct userenv(''SESSIONID''), 0, datab.TABLE_NAME
FROM
BSC_TMP_BIG_IN_COND tmp,
BSC_TMP_OPT_KPI_DATA dataa, BSC_TMP_OPT_KPI_DATA datab
WHERE
tmp.session_id = userenv(''SESSIONID'') and tmp.variable_id = 0
and dataa.indicator = datab.indicator
and dataa.table_name <> datab.table_name
and tmp.value_v=dataa.table_name
AND datab.TABLE_NAME IS NOT NULL
minus
select userenv(''SESSIONID''), 0, value_v
from bsc_tmp_big_in_cond cond where session_id =userenv(''SESSIONID'') and variable_id = 0' ;
InsertRelatedTables (numNewTables);
bsc_mo_helper_pkg.writeTmp( 'Compl. InsertRelatedTables');
bsc_mo_helper_pkg.writeTmp( 'Error in InsertRelatedTables :'||l_error, FND_LOG.LEVEL_UNEXPECTED);
BSC_METADATA_OPTIMIZER_PKG.garrTables.delete;
l_stmt := 'delete '||BSC_METADATA_OPTIMIZER_PKG.gBSCSchema||'.BSC_TMP_BIG_IN_COND where session_id = userenv(''SESSIONID'') and variable_id = 0';
strWhereInIndics := ' INDICATOR IN (SELECT INDICATOR FROM BSC_TMP_OPT_UI_KPIS WHERE PROCESS_ID = '||TO_CHAR(pProcessId)||')';
l_stmt := 'INSERT INTO '||BSC_METADATA_OPTIMIZER_PKG.gBSCSchema||'.BSC_TMP_BIG_IN_COND (session_id, variable_id, value_v )
SELECT DISTINCT userenv(''SESSIONID''), 0, TABLE_NAME FROM BSC_TMP_OPT_KPI_DATA DATA, BSC_TMP_OPT_UI_KPIS TMP
WHERE TMP.INDICATOR = DATA.INDICATOR AND TMP.PROCESS_ID = :1';
InsertRelatedTables(BSC_METADATA_OPTIMIZER_PKG.gnumTables);
bsc_mo_helper_pkg.writeTmp('Done with InsertRelatedTables');
l_stmt := 'INSERT INTO BSC_TMP_OPT_UI_KPIS (INDICATOR, PROTOTYPE_FLAG, PROCESS_ID)
SELECT DISTINCT INDICATOR, 3, :1 FROM BSC_TMP_OPT_KPI_DATA
WHERE TABLE_NAME IN
(SELECT /*+ index(cond bsc_tmp_big_in_cond_n1)*/VALUE_V FROM BSC_TMP_BIG_IN_COND cond WHERE SESSION_ID = :2 )
AND INDICATOR NOT IN
(SELECT INDICATOR FROM BSC_TMP_OPT_UI_KPIS WHERE PROCESS_ID = :3)';
l_stmt := 'SELECT DISTINCT M.MEASURE_COL FROM BSC_SYS_MEASURES M, BSC_DB_MEASURE_BY_KPI_V I'
|| ' WHERE I.MEASURE_ID = M.MEASURE_ID AND ('|| strWhereInIndics ||' )'||
' AND M.TYPE = 0 AND NVL(M.SOURCE, ''BSC'') = ''BSC'' ';
l_stmt := 'SELECT DISTINCT M.MEASURE_ID, M.MEASURE_COL '
||'FROM BSC_SYS_MEASURES M, BSC_DB_MEASURE_BY_KPI_V I '||
' WHERE I.MEASURE_ID = M.MEASURE_ID AND ('|| strWhereNotInIndics ||' ) '||
' AND M.TYPE = 0 AND NVL(M.SOURCE, ''BSC'') = ''BSC''';
l_stmt := 'SELECT DISTINCT INDICATOR FROM BSC_DB_MEASURE_BY_KPI_V '||
' WHERE ('|| strWhereInMeasures || ')';
execute immediate 'DELETE BSC_TMP_OPT_KPI_DATA';
l_stmt := 'INSERT INTO BSC_TMP_OPT_KPI_DATA
SELECT DISTINCT
TO_NUMBER
(
SUBSTR
(
TABLE_NAME,
INSTR(TABLE_NAME,''_'',1,2)+1,
INSTR(TABLE_NAME,''_'',1,3)-INSTR(TABLE_NAME,''_'',1,2)-1
)
),
TABLE_NAME
FROM BSC_DB_TABLES_RELS
WHERE TABLE_NAME LIKE ''BSC_S%''
AND (SOURCE_TABLE_NAME LIKE ''BSC_B%''
OR SOURCE_TABLE_NAME LIKE ''BSC_T%'')';
SELECT DB.INDICATOR, DS.MEASURE_ID1 AS MEASURE_ID
FROM BSC_KPI_ANALYSIS_MEASURES_B DB, BSC_SYS_DATASETS_B DS
WHERE DB.DATASET_ID = DS.DATASET_ID
UNION
SELECT DB.INDICATOR, DS.MEASURE_ID2 AS MEASURE_ID
FROM BSC_KPI_ANALYSIS_MEASURES_B DB, BSC_SYS_DATASETS_B DS
WHERE DB.DATASET_ID = DS.DATASET_ID AND DS.MEASURE_ID2 IS NOT NULL ';
PROCEDURE updateRelatedIndicators(
pMode IN VARCHAR2,
pProcessId IN NUMBER
) IS
PRAGMA AUTONOMOUS_TRANSACTION;
BSC_METADATA_OPTIMIZER_PKG.garrIndics.delete;
BSC_METADATA_OPTIMIZER_PKG.garrIndics4.delete;
BSC_METADATA_OPTIMIZER_PKG.garrTables.delete;
l_stmt := 'DELETE BSC_TMP_OPT_UI_KPIS WHERE PROCESS_ID = :1 ';
if (pMode = 'SELECTED' OR pMode = 'SELECTED_REPORTS' OR pMode = 'SELECTED_SIMULATIONS') then
l_stmt := ' INSERT INTO BSC_TMP_OPT_UI_KPIS (indicator, prototype_flag, process_id)
SELECT INDICATOR, prototype_flag, :1
FROM BSC_KPIS_VL
WHERE PROTOTYPE_FLAG NOT IN (1,2,3,4)';
BSC_MO_HELPER_PKG.writeTmp( 'Completed updateRelatedIndicators');
l_stmt := ' INSERT INTO BSC_TMP_OPT_UI_KPIS (indicator, prototype_flag, process_id)
SELECT INDICATOR, prototype_flag, :1
FROM BSC_KPIS_VL
WHERE BSC_DBGEN_UTILS.GET_OBJECTIVE_TYPE(SHORT_NAME) = :2 ';
BSC_MO_HELPER_PKG.writeTmp( 'Completed updateRelatedIndicators');
SELECT count(1) INTO l_total_kpis FROM BSC_KPIS_B;
l_stmt := ' INSERT INTO BSC_TMP_OPT_UI_KPIS (indicator, prototype_flag, process_id)
SELECT INDICATOR, prototype_flag, :1
FROM BSC_KPIS_VL
WHERE (PROTOTYPE_FLAG = 2 OR PROTOTYPE_FLAG = 3) ';
BSC_MO_HELPER_PKG.writeTmp( 'Completed updateRelatedIndicators');
l_stmt := 'SELECT INDICATOR FROM BSC_KPIS_B WHERE PROTOTYPE_FLAG = 4
MINUS
SELECT INDICATOR FROM BSC_TMP_OPT_ui_kpis WHERE process_id = :1
ORDER BY INDICATOR';
l_stmt := 'INSERT INTO BSC_TMP_OPT_UI_KPIS( INDICATOR, PROTOTYPE_FLAG, process_id)
SELECT DISTINCT INDICATOR, 4, :1
FROM BSC_KPIS_VL WHERE (' || strWhereInIndics4 || ')';
l_stmt := l_stmt || ' minus select indicator, 4, :2 from BSC_TMP_OPT_ui_kpis WHERE process_id = :3 ';
l_stmt := ' INSERT INTO BSC_TMP_OPT_UI_KPIS (indicator, prototype_flag, process_id)
SELECT INDICATOR, prototype_flag, :1 FROM BSC_KPIS_VL a
WHERE NOT EXISTS (SELECT 1 FROM BSC_TMP_OPT_UI_KPIS b WHERE process_id = :2 and a.indicator = b.indicator)';
l_stmt := ' INSERT INTO BSC_TMP_OPT_UI_KPIS (indicator, prototype_flag, process_id)
SELECT child.INDICATOR, parent.prototype_flag, :1
FROM BSC_KPIS_VL parent,
BSC_KPIS_VL child,
BSC_TMP_OPT_UI_KPIS uitmp
where uitmp.indicator = parent.indicator
and uitmp.process_id = :2
and parent.share_flag = 1
and child.share_flag = 2
and parent.indicator = child.source_indicator
AND NOT EXISTS (SELECT 1 FROM BSC_TMP_OPT_UI_KPIS c WHERE process_id = :3 and c.indicator = child.indicator)';
l_stmt := ' INSERT INTO BSC_TMP_OPT_UI_KPIS (indicator, prototype_flag, process_id)
SELECT INDICATOR, prototype_flag, :1 FROM BSC_KPIS_VL a
WHERE NOT EXISTS (SELECT 1 FROM BSC_TMP_OPT_UI_KPIS b WHERE process_id = :2 and a.indicator = b.indicator)';
l_stmt := 'delete from BSC_TMP_OPT_UI_KPIS tmp
where process_id = :1
and indicator in
(select indicator from bsc_kpis_vl kpis
where kpis.short_name is not null
and BSC_DBGEN_UTILS.get_objective_type(kpis.short_name) <> ''OBJECTIVE'') ';
BSC_MO_HELPER_PKG.writeTmp( 'Completed updateRelatedIndicators');
BSC_MO_HELPER_PKG.writeTmp('Exception in updateRelatedIndicators : '||l_error, FND_LOG.LEVEL_EXCEPTION);
select count(1) INTO l_count
from all_objects where object_name = pNew
and owner IN (BSC_METADATA_OPTIMIZER_PKG.gBSCSchema, BSC_METADATA_OPTIMIZER_PKG.gAppsSchema);
UPDATE BSC_DB_TABLES set table_name = pNew where table_name = pOld;
UPDATE BSC_DB_TABLES_COLS set table_name = pNew where table_name = pOld;
UPDATE BSC_DB_TABLES_RELS set source_table_name = pNew where source_table_name = pOld;
SELECT DISTINCT INDICATOR, NAME, PROTOTYPE_FLAG,
INDICATOR_TYPE, CONFIG_TYPE, PERIODICITY_ID,
SHARE_FLAG, SOURCE_INDICATOR,
EDW_FLAG FROM BSC_KPIS_VL WHERE
INDICATOR = pIndicator;
PROCEDURE updateTargets(pIndicator IN NUMBER) IS
l_stmt VARCHAR2(1000) := 'UPDATE BSC_TMP_OPT_UI_LEVELS UI SET TARGET_LEVEL =
( SELECT TARGET_LEVEL FROM bsc_kpi_dim_levels_vl DIM
WHERE UI.DIM_SET_ID = DIM.DIM_SET_ID
AND UI.INDICATOR = DIM.INDICATOR
AND UI.INDICATOR = : 1
AND UI.LEVEL_TABLE_NAME= DIM.LEVEL_TABLE_NAME)
WHERE UI.indicator = :2';
PROCEDURE insert_dimension_set (--pReturnArray IN OUT tab_clsIndicatorLevels,
pIndicator IN NUMBER, p_set IN NUMBER,
p_levels IN BSC_METADATA_OPTIMIZER_PKG.tab_tab_clsLevels ) IS
l_groupids DBMS_SQL.NUMBER_TABLE;
l_stmt VARCHAR2(300) := ' INSERT INTO BSC_TMP_OPT_UI_LEVELS (INDICATOR, DIM_SET_ID, DIM_DISPLAY_ORDER, LEVEL_DISPLAY_ORDER, LEVEL_TABLE_NAME, LEVEL_DISPLAY_NAME) '||
' values (:1, :2, :3, :4, :5, :6)';
l_insert_count NUMBER := 0;
execute immediate 'delete BSC_TMP_OPT_UI_LEVELS';
l_levels.delete;
insert_dimension_set(/*l_return_array, */pIndicator, colConfigurations(l_config_index), l_levels);
select count(1) INTO l_count FROM bsc_kpi_dim_levels_vl
where indicator = pIndicator
and target_level = 0;
updateTargets(pIndicator);
select lvl.help description from
bsc_sys_dim_levels_vl lvl
where
upper(pColumnName) = upper(lvl.level_pk_col)
UNION
select measure.help description from
bsc_db_measure_cols_vl measure
WHERE upper(pColumnName) = upper(measure.measure_col);
SELECT TABLE_NAME, TABLE_TYPE, PERIODICITY_ID, EDW_FLAG, TARGET_FLAG
FROM BSC_DB_TABLES
WHERE TABLE_TYPE <> 2
AND TABLE_NAME = l_table
ORDER BY TABLE_NAME;
SELECT YEARLY_FLAG
FROM BSC_SYS_PERIODICITIES_VL
WHERE PERIODICITY_ID = pPer
ORDER BY PERIODICITY_ID;
'select table_name from bsc_kpi_data_tables where mv_name = :1';
select count(1) into l_test from bsc_db_tables tab, bsc_db_tables_rels rels
where rels.table_name = l_table_name
and rels.source_table_name = tab.table_name
and tab.table_type = 0 ; -- input table
select lvl.name, cols.column_name
from
all_tab_columns cols,
bsc_sys_dim_levels_vl lvl
where cols.table_name = pTableName
and cols.owner = l_schema
and cols.column_name = upper(lvl.level_pk_col)
and cols.column_name not in ('YEAR', 'TYPE', 'PERIOD', 'TIME_FK', 'PERIODICITY_ID', 'PERIOD_TYPE_ID')
order by lvl.name,cols.column_name; --order by clause for bug 3869698
select datasets.name, cols.column_name
from
all_tab_columns cols,
bsc_sys_measures measure, bsc_sys_datasets_vl datasets
where cols.table_name = pTableName
and cols.owner = l_schema
and cols.column_name not in ('YEAR', 'TYPE', 'PERIOD', 'TIME_FK', 'PERIODICITY_ID', 'PERIOD_TYPE_ID')
and cols.column_name = upper(measure.measure_col(+))
and measure.measure_id = datasets.measure_id1 (+)
--and clause added for bug 3826281
and datasets.name is not null
and cols.column_name not in
(select cols.column_name
from
all_tab_columns cols,
bsc_sys_dim_levels_vl lvl
where cols.table_name = pTableName
and cols.owner = l_schema
and cols.column_name = upper(lvl.level_pk_col))
order by datasets.name,cols.column_name; --order by clause for bug 3869698
select Name from bsc_sys_dim_levels_vl where LEVEL_TABLE_NAME = pTableName
UNION
select Name from bsc_sys_dim_levels_vl, bsc_Db_tables_rels r
where LEVEL_TABLE_NAME = r.table_name
and r.source_table_name = pTableName;
select lvl.name, cols.column_name
from all_tab_columns cols, bsc_sys_dim_levels_vl lvl
where cols.table_name = pTableName
and cols.owner = l_schema
and cols.column_name = upper(lvl.level_pk_col)
union
select lvl.name, cols.column_name
from all_tab_columns cols,
bsc_sys_dim_levels_vl lvl, bsc_db_tables_rels r
where r.source_table_name = pTableName
and cols.table_name = r.TABLE_NAME
and cols.owner = l_schema
and cols.column_name = upper(lvl.level_pk_col)
order by 1,2;
l_selected_kpis NUMBER;
l_stmt := 'DELETE BSC_TMP_OPT_UI_KPIS WHERE PROCESS_ID = :1 ';
BSC_METADATA_OPTIMIZER_PKG.garrIndics.delete;
BSC_METADATA_OPTIMIZER_PKG.garrTables.delete;
l_selected_kpis := 0;
l_selected_kpis := l_selected_kpis + 1;
l_stmt := ' INSERT INTO BSC_TMP_OPT_UI_KPIS (indicator, prototype_flag, process_id)
SELECT INDICATOR, PROTOTYPE_FLAG, :1
FROM BSC_KPIS_B
WHERE INDICATOR = :2 ';
IF (l_selected_kpis = 0) THEN
RETURN;
SELECT COUNT(1) INTO l_total_kpis FROM BSC_KPIS_B;
IF (l_selected_kpis = l_total_kpis) THEN
COMMIT;
BSC_METADATA_OPTIMIZER_PKG.gnumIndics := l_selected_kpis;
l_stmt := ' INSERT INTO BSC_TMP_OPT_UI_KPIS (indicator, prototype_flag, process_id)
SELECT k.INDICATOR, k.PROTOTYPE_FLAG, :1
FROM BSC_KPIS_VL k,
BSC_TMP_OPT_UI_KPIS t
WHERE ((k.SHARE_FLAG = 2 AND k.SOURCE_INDICATOR = t.INDICATOR)
OR (k.SHARE_FLAG = 2 AND k.SOURCE_INDICATOR IN
(SELECT I.SOURCE_INDICATOR FROM BSC_KPIS_B I WHERE I.SHARE_FLAG = 2 AND I.INDICATOR = t.INDICATOR))
OR (k.SHARE_FLAG = 1 AND k.INDICATOR IN
(SELECT I.SOURCE_INDICATOR FROM BSC_KPIS_B I WHERE I.SHARE_FLAG = 2 AND I.INDICATOR = t.INDICATOR)))
AND t.PROCESS_ID = :2
AND NOT EXISTS (SELECT 1 FROM BSC_TMP_OPT_UI_KPIS c WHERE c.indicator = k.indicator AND c.PROCESS_ID = :3)';
PROCEDURE deleteBSCSession(pSession IN NUMBER) IS
BEGIN
delete bsc_current_sessions where session_id = pSession;
DELETE BSC_TMP_OPT_UI_KPIS
WHERE PROCESS_ID > 0
AND TO_CHAR(PROCESS_ID) NOT IN (
SELECT /*+ INDEX(R FND_CONCURRENT_REQUESTS_N6)*/ R.ARGUMENT2
FROM FND_CONCURRENT_REQUESTS R, FND_CONCURRENT_PROGRAMS_VL P, FND_APPLICATION A
WHERE A.APPLICATION_SHORT_NAME = BSC_MO_HELPER_PKG.getBSCSchema
AND A.APPLICATION_ID = P.APPLICATION_ID
AND P.APPLICATION_ID = R.PROGRAM_APPLICATION_ID
AND P.CONCURRENT_PROGRAM_ID = R.CONCURRENT_PROGRAM_ID
AND P.CONCURRENT_PROGRAM_NAME = 'BSC_METADATA_OPTIMIZER'
AND R.PHASE_CODE IN ('P','R')
AND R.ARGUMENT2 IS NOT NULL);
DELETE BSC_TMP_OPT_UI_KPIS
WHERE PROCESS_ID < 0
AND PROCESS_ID NOT IN (
SELECT SESSION_ID*-1
FROM ICX_SESSIONS
WHERE (FND_SESSION_MANAGEMENT.CHECK_SESSION(SESSION_ID,NULL,NULL,'N') = 'VALID'));
p_all_objectives: 1 = all, 0 = modified or selected
p_program_id: The program ID
p_user_id: Application User ID
p_process_id: The process ID
-------------------------------------------------------------------------------------------*/
Procedure checkSystemLock (
p_all_objectives IN number
,p_program_id IN number
,p_user_id IN number
,p_process_id IN number
,x_return_status OUT NOCOPY varchar2
,x_msg_count OUT NOCOPY number
,x_msg_data OUT NOCOPY varchar2
) IS
l_api_name CONSTANT VARCHAR2(30) := 'checkSystemLock';
SELECT DISTINCT INDICATOR
FROM BSC_TMP_OPT_UI_KPIS
WHERE PROCESS_ID = c_process_id;
SELECT DISTINCT INDICATOR
FROM BSC_TMP_OPT_UI_KPIS
WHERE PROCESS_ID = c_process_id
AND PROTOTYPE_FLAG IN (1,2,3,4,-3);
p_all_objectives: 1 = all, 0 = modified or selected
p_program_id: The program ID
p_query_time: The query time at the start of the process flow
p_user_id: Application User ID
p_process_id: The process ID
-------------------------------------------------------------------------------------------*/
Procedure getSystemLock (
p_all_objectives IN number
,p_query_time IN date
,p_program_id IN number
,p_user_id IN number
,p_process_id IN number
,x_return_status OUT NOCOPY varchar2
,x_msg_count OUT NOCOPY number
,x_msg_data OUT NOCOPY varchar2
) IS
l_api_name CONSTANT VARCHAR2(30) := 'getSystemLock';
SELECT INDICATOR
FROM BSC_TMP_OPT_UI_KPIS
WHERE PROCESS_ID = c_process_id;
SELECT DISTINCT INDICATOR
FROM BSC_TMP_OPT_UI_KPIS
WHERE PROCESS_ID = c_process_id
AND PROTOTYPE_FLAG IN (1,2,3,4,-3);