[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT BSC_UPDATE_UTIL.Get_Free_Div_Zero_Expression(expression) NEWEXPRESSION FROM DUAL;
select distinct levels.level_table_name child_level, parent_levels.level_table_name parent_level,
relation_col parent_fk, level
from bsc_sys_dim_level_rels rels,
bsc_sys_dim_levels_b levels,
bsc_sys_dim_levels_b parent_levels,
bsc_kpi_dim_levels_b kpi_levels
where rels.dim_level_id = levels.dim_level_id
and levels.level_table_name = kpi_levels.level_table_name
and rels.parent_dim_level_id = parent_levels.dim_level_id
and kpi_levels.indicator = p_indicator
and kpi_levels.dim_set_id = p_dimset
connect by prior rels.dim_level_id||rels.relation_type = rels.parent_dim_level_id||1 -- relation_type=1
start with parent_dim_level_id = (select dim_level_id from bsc_sys_dim_levels_b where level_table_name = p_level_name)
order by level;
SELECT RELATION_COL
FROM BSC_SYS_DIM_LEVEL_RELS RELS, BSC_SYS_DIM_LEVELS_B LVLA, BSC_SYS_DIM_LEVELS_B LVLB
WHERE LVLA.level_table_name = p_lower_dim_table
AND LVLB.level_table_name = p_higher_dim_table
AND LVLA.dim_level_id = rels.dim_level_id
AND LVLB.dim_level_id = rels.parent_dim_level_id;
l_select_keys_clause VARCHAR2(4000);
l_select_rest VARCHAR2(4000);
l_select_rest := p_sql_stmt;
bsc_mo_helper_pkg.writeTmp('Dimensions have been joined to, so we need to change the select clause', FND_LOG.level_Statement, false);
l_select_keys_clause := 'SELECT ';
l_select_keys_clause := l_select_keys_clause || '0 ' || p_keys(j).keyName || ', ';
l_select_keys_clause := l_select_keys_clause || l_zmv||'.';
l_select_keys_clause := l_select_keys_clause ||p_keys(j).keyName || ', ';
bsc_mo_helper_pkg.writeTmp('l_select_keys_clause='||l_select_keys_clause, FND_LOG.level_Statement, false);
p_sql_stmt := l_select_keys_clause||' '||l_select_rest||' '||l_from_clause||' '||l_where_clause||' '||l_group_by_clause;
l_select_key_clause VARCHAR2(4000);
arrCombinationsB.delete;
l_select_key_clause := 'SELECT ';
l_zero_code_states.delete;
l_select_key_clause := l_select_key_clause || '0 ' || keyColumn.keyName || ', ';
l_select_key_clause := l_select_key_clause || keyColumn.keyName || ', ';
l_select_key_clause := l_select_key_clause || keyColumn.keyName || ', ';
sql_stmt := l_select_key_clause || sql_stmt || ' FROM ' || MVName;
SELECT count(1)
FROM BSC_KPI_DIM_LEVELS_B K, BSC_SYS_DIM_LEVELS_B S
WHERE UPPER(K.LEVEL_TABLE_NAME) = UPPER(S.LEVEL_TABLE_NAME)
AND K.INDICATOR = pIndicator
AND K.DIM_SET_ID = pConfig
AND UPPER(S.LEVEL_VIEW_NAME) <> UPPER(K.LEVEL_VIEW_NAME)
AND K.STATUS = 2;
l_stmt varchar2(4000) := 'SELECT kpi.indicator||''_''|| i.dim_set_id hash_index, COUNT(M.MEASURE_COL) NUM_DATA_COLUMNS
FROM BSC_SYS_MEASURES M, '||BSC_METADATA_OPTIMIZER_PKG.g_dbmeasure_tmp_table||' I,
BSC_KPIS_VL kpi
WHERE I.MEASURE_ID = M.MEASURE_ID
AND kpi.indicator = i.indicator
AND M.TYPE = 0
AND NVL(M.SOURCE, ''BSC'') IN (''BSC'', ''PMF'')
AND NVL(M.SOURCE, ''BSC'') <> decode(kpi.short_name, null, ''PMF'', ''-1'')
GROUP BY kpi.indicator||''_''|| i.dim_set_id ';
'SELECT COUNT(M.MEASURE_COL) NUM_DATA_COLUMNS
FROM BSC_SYS_MEASURES M, '||BSC_METADATA_OPTIMIZER_PKG.g_dbmeasure_tmp_table||' I
WHERE I.MEASURE_ID = M.MEASURE_ID
AND I.DIM_SET_ID = :1
AND I.INDICATOR = :2
AND M.TYPE = 0
AND NVL(M.SOURCE, ''BSC'') IN (''BSC'', ''PMF'')
AND NVL(M.SOURCE, ''BSC'') <> :3';
select count(1) from user_objects where object_name = BSC_METADATA_OPTIMIZER_PKG.g_dbmeasure_tmp_table;
SELECT COUNT(M.MEASURE_COL) NUM_DATA_COLUMNS
FROM BSC_SYS_MEASURES M, BSC_DB_MEASURE_BY_DIM_SET_V I
WHERE I.MEASURE_ID = M.MEASURE_ID
AND I.DIM_SET_ID = DimSet
AND I.INDICATOR = Indic
AND M.TYPE = 0
AND NVL(M.SOURCE, 'BSC') in('BSC', 'PMF')
AND NVL(M.SOURCE, 'BSC') <> pIgnore;
SELECT short_name INTO l_short_name FROM bsc_kpis_vl where indicator = Indic;
SELECT NVL(SOURCE, 'BSC') DSSOURCE
FROM BSC_SYS_DIM_LEVELS_B S, BSC_KPI_DIM_LEVELS_B K
WHERE S.LEVEL_TABLE_NAME = K.LEVEL_TABLE_NAME
AND K.INDICATOR = Indic AND K.DIM_SET_ID = DimSet AND K.STATUS = 2;
SELECT DISTINCT DIM_SET_ID
FROM BSC_DB_DATASET_DIM_SETS_V
WHERE INDICATOR = Indic
ORDER BY DIM_SET_ID;
DELETE FROM BSC_KPI_DATA_TABLES WHERE INDICATOR = Indicator.code AND DIM_SET_ID = Configuration;
l_stmt := 'INSERT INTO BSC_KPI_DATA_TABLES ( INDICATOR,PERIODICITY_ID,
DIM_SET_ID, LEVEL_COMB, TABLE_NAME, FILTER_CONDITION ';
l_stmt := l_stmt ||' ) SELECT :1, PERIODICITY_ID, :2, LEVEL_COMB, TABLE_NAME, FILTER_CONDITION ';
UPDATE BSC_KPI_DATA_TABLES SET PROJECTION_SOURCE = 1,
PROJECTION_DATA = pt_name
WHERE INDICATOR = Indicator.code
AND DIM_SET_ID = COnfiguration
AND TABLE_NAME = l_table.name;
UPDATE BSC_KPI_DATA_TABLES SET PROJECTION_SOURCE = 1,
PROJECTION_DATA = pt_name
WHERE INDICATOR = Indicator.code
AND DIM_SET_ID = COnfiguration
AND TABLE_NAME = l_table.name;
BSC_MO_HELPER_PKG.writeTmp('1 Going to delete '||BSC_METADATA_OPTIMIZER_PKG.gTables(i).name);
BSC_METADATA_OPTIMIZER_PKG.gTables.delete(i);
BSC_METADATA_OPTIMIZER_PKG.gTables.delete(i);
DELETE FROM BSC_KPI_DATA_TABLES WHERE INDICATOR = Indicator.Code
AND DIM_SET_ID = Configuration;
Table_keys.delete; -- cleanup
Table_data.delete; -- cleanup
l_stmt := 'INSERT INTO BSC_KPI_DATA_TABLES (INDICATOR, PERIODICITY_ID, DIM_SET_ID, LEVEL_COMB,
TABLE_NAME, FILTER_CONDITION, MV_NAME, PROJECTION_SOURCE, DATA_SOURCE, SQL_STMT, PROJECTION_DATA)
VALUES(:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11)';
/*INSERT INTO BSC_KPI_DATA_TABLES
(INDICATOR, PERIODICITY_ID, DIM_SET_ID,
LEVEL_COMB, TABLE_NAME, FILTER_CONDITION)
VALUES(Indicator.Code, L_Periodicity.Code, Configuration,
nvl(Basica.levelConfig, '?'), TableName, cond);*/
INSERT INTO BSC_KPI_DATA_TABLES values l_tbl_kpidata(ii);
l_tbl_kpidata.delete;
INSERT INTO BSC_KPI_DATA_TABLES
(INDICATOR, PERIODICITY_ID, DIM_SET_ID, LEVEL_COMB,
TABLE_NAME, FILTER_CONDITION, MV_NAME, PROJECTION_SOURCE,
DATA_SOURCE, SQL_STMT, PROJECTION_DATA)
SELECT INDICATOR, L_Periodicity.Code , DIM_SET_ID, LEVEL_COMB,
SUBSTR(TABLE_NAME,1,INSTR(TABLE_NAME, '_', -1))||L_Periodicity.Code TABLE_NAME,
FILTER_CONDITION, MV_NAME, PROJECTION_SOURCE, DATA_SOURCE, SQL_STMT, PROJECTION_DATA
FROM BSC_KPI_DATA_TABLES
WHERE INDICATOR = Indicator.Code
AND PERIODICITY_ID = first_periodicity_id
AND DIM_SET_ID = Configuration;
SELECT LEVEL_VIEW_NAME FROM BSC_KPI_DIM_LEVELS_B
WHERE INDICATOR = pIndicator
AND DIM_SET_ID = pConfiguration
AND LEVEL_PK_COL = pKeyCol;
l_stmt := 'select 1 from bsc_kpi_dim_levels_b where indicator =:1
and level_pk_col = :2
and level_table_name in (
select level_table_name from bsc_sys_dim_levels_b
where short_name in ('|| gRecDims||')
or dim_level_id in
(select dim_level_id from bsc_sys_dim_level_rels
where dim_level_id = parent_dim_level_id))
';
Basic_keys.delete;
PROCEDURE InsertInDBMeasureCols(p_measure IN BSC_METADATA_OPTIMIZER_PKG.clsMeasureLOV) IS
l_stmt VARCHAR2(1000);
bsc_mo_helper_pkg.writeTmp( 'Inside InsertInDBMeasureCols, p_measure = ');
l_stmt := 'DELETE FROM BSC_DB_MEASURE_COLS_TL WHERE MEASURE_COL = :1';
INSERT INTO BSC_DB_MEASURE_COLS_TL (
MEASURE_COL, LANGUAGE, SOURCE_LANG,
HELP, MEASURE_GROUP_ID, PROJECTION_ID, MEASURE_TYPE)
VALUES (p_measure.fieldName, BSC_METADATA_OPTIMIZER_PKG.gInstalled_Languages(i), BSC_METADATA_OPTIMIZER_PKG.gLangCode,
p_measure.Description, p_measure.groupCode, p_measure.prjMethod,p_measure.measureType );
bsc_mo_helper_pkg.writeTmp( 'Compl. InsertInDBMeasureCols');
BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in InsertInDBMeasureCols '||g_error);
bsc_mo_helper_pkg.writeTmp( 'Going to InsertInDBMeasureCols');
InsertInDBMeasureCols( l_measure);
UPDATE BSC_SYS_MEASURES
SET S_COLOR_FORMULA = BSC_APPS.SET_PROPERTY_VALUE(S_COLOR_FORMULA, propertyName, propertyValue)
WHERE UPPER(MEASURE_COL) = upper(dataColumn)
AND TYPE = 0 AND NVL(SOURCE, 'BSC') = 'BSC';
SELECT NVL(OPERATION, 'SUM') AS OPER,
NVL(BSC_APPS.GET_PROPERTY_VALUE(S_COLOR_FORMULA, p1),'N') AS PAVGL,
BSC_APPS.GET_PROPERTY_VALUE(S_COLOR_FORMULA, p2) AS PAVGLTOTAL,
BSC_APPS.GET_PROPERTY_VALUE(S_COLOR_FORMULA, p3) AS PAVGLCOUNTER
FROM BSC_SYS_MEASURES
WHERE UPPER(MEASURE_COL) = UPPER(p4)
AND TYPE = 0
AND NVL(SOURCE, 'BSC') = 'BSC';
SELECT BSC_INTERNAL_COLUMN_S.NEXTVAL INTO l_seq FROM DUAL;
l_stmt2 VARCHAR2(10000):= 'SELECT M.MEASURE_COL, NVL(M.OPERATION, ''SUM'') AS OPER,
BSC_APPS.GET_PROPERTY_VALUE(M.S_COLOR_FORMULA, :1) AS PFORMULASOURCE,
NVL(BSC_APPS.GET_PROPERTY_VALUE(M.S_COLOR_FORMULA, :2 ),''N'') AS PAVGL,
BSC_APPS.GET_PROPERTY_VALUE(M.S_COLOR_FORMULA, :3) AS PAVGLTOTAL,
BSC_APPS.GET_PROPERTY_VALUE(M.S_COLOR_FORMULA, :4) AS PAVGLCOUNTER '||
-- BSC Autogen
', nvl(M.SOURCE, ''BSC'')
FROM BSC_SYS_MEASURES M, '||BSC_METADATA_OPTIMIZER_PKG.g_dbmeasure_tmp_table||' I
WHERE I.MEASURE_ID = M.MEASURE_ID
AND I.DIM_SET_ID = :5
AND I.INDICATOR = :6
AND M.TYPE = 0';
select short_name into l_source from bsc_kpis_vl
where indicator=Indic;
Combination.delete;
Combination.delete;
DimLevelCombinations.delete;
colRelsMN.delete;
l_stmt := 'SELECT DISTINCT DIM_LEVEL_INDEX, LEVEL_TABLE_NAME, LEVEL_PK_COL, NAME, NVL(TARGET_LEVEL,1) AS TAR_LEVEL' ||
' FROM BSC_KPI_DIM_LEVELS_VL WHERE INDICATOR = :1 AND DIM_SET_ID = :2 AND STATUS = 2';
DimensionLevels.delete;
DimensionLevels.delete;
SELECT PERIODICITY_ID, NVL(TARGET_LEVEL, 1) AS TAR_LEVEL
FROM BSC_KPI_PERIODICITIES
WHERE INDICATOR = Indic ORDER BY PERIODICITY_ID;
BSC_MO_HELPER_PKG.writeTmp('2 Going to delete '||BSC_METADATA_OPTIMIZER_PKG.gTables(i).name);
bsc_metadata_optimizer_pkg.gTables.delete(i);
SELECT DISTINCT DIM_SET_ID FROM BSC_DB_DATASET_DIM_SETS_V
WHERE INDICATOR = Indic ORDER BY DIM_SET_ID;