[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
INSERT INTO BSC_MESSAGE_LOGS (
SOURCE,
TYPE,
MESSAGE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN
) VALUES(
l_source,
0,
p_Text,
sysdate,
l_session_id,
sysdate,
l_session_id,
g_row_num
);
SELECT A.series_id, A.Name, A.default_value
,A.dataset_id, E.dim_set_id
FROM BSC_KPI_ANALYSIS_MEASURES_VL A
, bsc_db_dataset_dim_sets_v E
WHERE E.INDICATOR = A.INDICATOR
AND E.A0 = A.Analysis_Option0
AND E.A1 = A.Analysis_Option1
AND E.A2 = A.Analysis_Option2
AND E.Series_Id = A.Series_Id
AND A.INDICATOR = l_Kpi_Id
AND A.Analysis_Option0 = l_Anaysis_option0
AND A.Analysis_Option1 = l_Anaysis_option1
AND A.Analysis_Option2 = l_Anaysis_option2;
SELECT A.dataset_id, A.Name, A.COLOR_METHOD
,A.SOURCE ,A.Measure_Id1,
B.Measure_ID M_FLAG, B.Short_Name Short_Name1, B.MEASURE_COL, B.SOURCE SOURCE1
,A.Measure_Id2
FROM BSC_SYS_DATASETS_VL A
, BSC_SYS_MEASURES B
WHERE B.Measure_ID (+) = A.Measure_ID1
AND A.dataset_id = l_Dataset_id;
SELECT Short_Name, MEASURE_COL, SOURCE
FROM BSC_SYS_MEASURES
WHERE Measure_ID = l_Measure_id;
SELECT Indicator
, Name
, Ind_Group_ID
, Prototype_Flag
, Indicator_Type
, Share_Flag
, Short_Name
, Source_Indicator
FROM BSC_KPIS_VL
WHERE Indicator = l_Kpi_Id;
SELECT indicator
, Name
, Short_Name
FROM BSC_KPIS_VL
WHERE Source_Indicator = l_Kpi_Id;
SELECT TI.TAB_ID, T.NAME
FROM BSC_TAB_INDICATORS TI
,BSC_TABS_VL T
WHERE TI.INDICATOR = l_Kpi_Id
AND T.TAB_ID (+) = TI.TAB_ID;
SELECT IND_GROUP_ID, NAME, TAB_ID
FROM BSC_TAB_IND_GROUPS_VL
WHERE IND_GROUP_ID = l_kpi_group_id
AND ( TAB_ID = -1 OR TAB_ID IN
(SELECT TAB_ID FROM BSC_TAB_INDICATORS WHERE INDICATOR = l_Kpi_Id));
SELECT K.RESPONSIBILITY_ID, R.RESPONSIBILITY_NAME
FROM BSC_USER_KPI_ACCESS K,
BSC_RESPONSIBILITY_VL R
WHERE K.INDICATOR = l_Kpi_Id
AND K.RESPONSIBILITY_ID = R.RESPONSIBILITY_ID (+);
SELECT DIM_SET_ID, NAME
FROM BSC_KPI_DIM_SETS_VL
WHERE INDICATOR = l_Kpi_Id;
SELECT A.Name, A.Short_Name, B.Dim_Group_Id
FROM BSC_SYS_DIM_GROUPS_VL A
, BSC_KPI_DIM_GROUPS B
WHERE A.Dim_Group_Id (+) = B.Dim_Group_Id
AND B.Indicator = l_Kpi_Id
AND B.Dim_Set_Id = l_Dim_set_Id;
SELECT * FROM (
SELECT A.Name, A.Short_Name, A.Dim_Level_Id, A.Source, B.Level_Table_Name
,B.LEVEL_VIEW_NAME, B.LEVEL_PK_COL, B.DIM_LEVEL_INDEX
FROM BSC_SYS_DIM_LEVELS_VL A
, BSC_KPI_DIM_LEVELS_VL B
, BSC_SYS_DIM_LEVELS_BY_GROUP C
WHERE A.Level_Table_Name (+) = B.Level_Table_Name
AND C.dim_level_id = nvl(A.dim_level_id, C.dim_level_id)
AND B.Indicator = l_Kpi_Id
AND B.Dim_Set_Id = l_Dim_set_Id
AND nvl(C.Dim_Group_Id, l_dim_id ) = l_dim_id
)
ORDER BY DIM_LEVEL_INDEX;
SELECT ANALYSIS_GROUP_ID, DEPENDENCY_FLAG, CHANGE_DIM_SET
FROM BSC_KPI_ANALYSIS_GROUPS
WHERE INDICATOR = l_Kpi_Id;
SELECT A.ANALYSIS_GROUP_ID, A.OPTION_ID, A.NAME, A.DIM_SET_ID
FROM BSC_KPI_ANALYSIS_OPTIONS_VL A
WHERE A.INDICATOR = l_Kpi_Id
AND A.ANALYSIS_GROUP_ID = 0
AND A.PARENT_OPTION_ID = 0
AND A.GRANDPARENT_OPTION_ID = 0
ORDER BY OPTION_ID;
SELECT A.ANALYSIS_GROUP_ID, A.OPTION_ID, A.NAME, A.DIM_SET_ID
FROM BSC_KPI_ANALYSIS_OPTIONS_VL A
WHERE A.INDICATOR = l_Kpi_Id
AND A.ANALYSIS_GROUP_ID = 1
AND A.PARENT_OPTION_ID = l_Parent_Option_Id
AND A.GRANDPARENT_OPTION_ID = 0
ORDER BY OPTION_ID;
SELECT A.ANALYSIS_GROUP_ID, A.OPTION_ID, A.NAME, A.DIM_SET_ID
FROM BSC_KPI_ANALYSIS_OPTIONS_VL A
WHERE A.INDICATOR = l_Kpi_Id
AND A.ANALYSIS_GROUP_ID = 2
AND A.PARENT_OPTION_ID = l_Parent_Option_Id
AND A.GRANDPARENT_OPTION_ID = l_Grandparent_Option
ORDER BY OPTION_ID;
SELECT PERIODICITY_ID, DISPLAY_ORDER, CURRENT_PERIOD, TARGET_LEVEL
FROM BSC_KPI_PERIODICITIES A
WHERE INDICATOR = l_Kpi_Id;
SELECT A.NAME, A.PERIODICITY_TYPE, A.PERIOD_TYPE_ID, A.RECORD_TYPE_ID
, A.CALENDAR_ID
, B.NAME CALENDAR_NAME , B.FISCAL_YEAR
FROM BSC_SYS_PERIODICITIES_VL A
, BSC_SYS_CALENDARS_VL B
WHERE A.CALENDAR_ID (+) = B.CALENDAR_ID
AND A.PERIODICITY_ID = l_periodicity_id;
SELECT A.CALCULATION_ID, B.MEANING AS NAME
,A.USER_LEVEL0, A.USER_LEVEL1, A.DEFAULT_VALUE
FROM BSC_LOOKUPS B
,BSC_KPI_CALCULATIONS A
WHERE B.LOOKUP_TYPE = 'BSC_CALCULATION'
AND A.INDICATOR = l_Kpi_Id
AND NVL(TO_NUMBER(B.LOOKUP_CODE), A.CALCULATION_ID ) = A.CALCULATION_ID;
SELECT PROPERTY_CODE, PROPERTY_VALUE, SECONDARY_VALUE
FROM BSC_KPI_PROPERTIES
WHERE INDICATOR = l_Kpi_Id;
SELECT PERIODICITY_ID, DIM_SET_ID, LEVEL_COMB
,TABLE_NAME, FILTER_CONDITION, MV_NAME, PROJECTION_SOURCE
,DATA_SOURCE, SQL_STMT,PROJECTION_DATA
FROM BSC_KPI_DATA_TABLES
WHERE INDICATOR = l_Kpi_Id
ORDER BY PERIODICITY_ID, DIM_SET_ID, LEVEL_COMB ;
SELECT DISTINCT K.DIM_SET_ID
FROM BSC_KPI_DIM_GROUPS K
WHERE K.Indicator = l_Kpi_Id
AND K.Dim_Set_Id NOT IN (
SELECT DIM_SET_ID
FROM BSC_KPI_DIM_SETS_VL
WHERE INDICATOR = l_Kpi_Id
);
SELECT Name INTO l_database FROM V$DATABASE;
Additional delete all metadata description not created in the current day
-------------------------------------------------------------------------------------------*/
PROCEDURE ClearText IS
l_session_id number;
DELETE FROM BSC_MESSAGE_LOGS
WHERE SOURCE = G_PKG_NAME
AND ( CREATED_BY = l_session_id
OR CREATION_DATE <= (SYSDATE -1)
);
SELECT MESSAGE FROM BSC_MESSAGE_LOGS
WHERE SOURCE = ''' || G_PKG_NAME || '''
AND CREATED_BY = ' || l_session_id || '
ORDER BY LAST_UPDATE_LOGIN ';