[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT nested_region_code FROM ak_region_items
WHERE region_code = l_region_code AND item_style = 'NESTED_REGION';
SELECT actual_data INTO l_value
FROM bsc_bis_measures_data
WHERE user_id = p_user_id AND
responsibility_id = p_responsibility_id AND
indicator = p_kpi_code AND
analysis_option0 = p_analysis_option0 AND
analysis_option1 = p_analysis_option1 AND
analysis_option2 = p_analysis_option2 AND
series_id = p_series_id;
SELECT DISTINCT DF.A0_DEFAULT,DF.A1_DEFAULT,DF.A2_DEFAULT,MS.SERIES_ID
INTO x_analysis_option0,x_analysis_option1,x_analysis_option2,x_series_id
FROM BSC_DB_COLOR_AO_DEFAULTS_V DF,
BSC_KPI_ANALYSIS_MEASURES_B MS
WHERE
DEFAULT_VALUE =1 AND
DF.INDICATOR = MS.INDICATOR AND
DF.A0_DEFAULT = MS.ANALYSIS_OPTION0 AND
DF.A1_DEFAULT = MS.ANALYSIS_OPTION1 AND
DF.A2_DEFAULT = MS.ANALYSIS_OPTION2 AND
DF.INDICATOR =p_kpi_code;
SELECT
dataset_id
INTO
x_dataset_id
FROM
bsc_db_dataset_dim_sets_v
WHERE
indicator = p_kpi_code AND
A0 = p_analysis_option0 AND
A1 = p_analysis_option1 AND
A2 = p_analysis_option2 AND
series_id = p_series_id;
SELECT
d.source,
d.measure_id1,
d.operation,
d.measure_id2,
d.color_method,
m1.measure_col,
m1.operation,
m1.short_name,
m2.measure_col,
m2.operation,
d.format_id
INTO
x_source,
x_measure_id1,
x_operation,
x_measure_id2,
x_color_method,
x_measure_col1,
x_measure_operation1,
x_measure_short_name,
x_measure_col2,
x_measure_operation2,
x_format_id
FROM
bsc_sys_datasets_b d,
bsc_sys_measures m1,
bsc_sys_measures m2
WHERE
d.dataset_id = p_dataset_id AND
d.measure_id1 = m1.measure_id(+) AND
d.measure_id2 = m2.measure_id(+);
SELECT short_name INTO l_short_name
FROM bis_dimensions
WHERE dimension_id = p_dimension_id;
SELECT
dim_set_id
INTO
x_dimset_id
FROM
bsc_db_dataset_dim_sets_v
WHERE
indicator = p_kpi_code AND
A0 = p_analysis_option0 AND
A1 = p_analysis_option1 AND
A2 = p_analysis_option2 AND
series_id = p_series_id;
l_select_string VARCHAR2(32000);
BIS_PMF_GET_DIMLEVELS_PUB.GET_DIMLEVEL_SELECT_STRING (
p_DimLevelShortName => p_time_dim_level_short_name
,p_bis_source => p_source
,x_Select_String => l_select_string
,x_table_name => l_view_name
,x_id_name => l_id_name
,x_value_name => l_value_name
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
l_sql := 'SELECT DISTINCT '||l_id_name||', '||l_value_name||', start_date, end_date'||
' FROM '||l_view_name;
l_sql := 'SELECT DISTINCT '||l_id_name||', '||l_value_name||', '||l_curr_date||' AS start_date, '||l_curr_date||' AS end_date'||
' FROM '||l_view_name;
SELECT budget_data INTO l_value
FROM bsc_bis_measures_data
WHERE user_id = p_user_id AND
responsibility_id = p_responsibility_id AND
indicator = p_kpi_code AND
analysis_option0 = p_analysis_option0 AND
analysis_option1 = p_analysis_option1 AND
analysis_option2 = p_analysis_option2 AND
series_id = p_series_id;
SELECT plan_id INTO l_plan_id
FROM bisbv_business_plans
WHERE short_name = 'STANDARD';
l_select_string VARCHAR2(32000);
l_sql := 'SELECT DISTINCT l.source'||
' FROM bis_levels l, bis_dimensions d'||
' WHERE d.dimension_id = l.dimension_id AND d.short_name = :1';
l_sql := 'SELECT DISTINCT l.short_name'||
' FROM bis_levels l, bis_dimensions d'||
' WHERE d.dimension_id = l.dimension_id AND'||
' d.short_name = :1 AND l.short_name LIKE ''TOTAL%''';
BIS_PMF_GET_DIMLEVELS_PUB.GET_DIMLEVEL_SELECT_STRING (
p_DimLevelShortName => x_total_dimlevel_short_name
,p_bis_source => l_source
,x_Select_String => l_select_string
,x_table_name => l_view_name
,x_id_name => l_id_name
,x_value_name => l_value_name
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
l_sql := 'SELECT DISTINCT '||l_id_name||', '||l_value_name||
' FROM '||l_view_name;
l_sql := 'SELECT BID.SHORT_NAME DIMENSION_SHORTNAME, '||
' BKL.LEVEL_SHORTNAME '||
' FROM BSC_KPI_DIM_LEVELS_VL BKL,'||
' BIS_LEVELS BIL,'||
' BIS_DIMENSIONS BID'||
' WHERE BKL.INDICATOR = :1 AND'||
' BKL.DIM_SET_ID = :2 AND'||
' BKL.LEVEL_SOURCE = ''PMF'' AND'||
' (BKL.DEFAULT_VALUE = ''C'' OR BKL.DEFAULT_VALUE = ''LD'' ) AND'||
' BKL.LEVEL_SHORTNAME = BIL.SHORT_NAME AND'||
' BIL.DIMENSION_ID = BID.DIMENSION_ID'||
' ORDER BY DIMENSION_SHORTNAME, BKL.DIM_LEVEL_INDEX';
l_sql := 'SELECT BID.SHORT_NAME DIMENSION_SHORTNAME, '||
' BKL.LEVEL_SHORTNAME, '||
' DECODE(BKL.DEFAULT_VALUE,''C'', 1, ''LD'', 1, 0) DEFAULT_FLAG '||
' FROM BSC_KPI_DIM_LEVELS_VL BKL,'||
' BIS_LEVELS BIL,'||
' BIS_DIMENSIONS BID'||
' WHERE BKL.INDICATOR = :1 AND'||
' BKL.DIM_SET_ID = :2 AND'||
' BKL.LEVEL_SOURCE = ''PMF'' AND'||
' BKL.LEVEL_SHORTNAME = BIL.SHORT_NAME AND'||
' BIL.DIMENSION_ID = BID.DIMENSION_ID';
l_insert_sql VARCHAR2(32000);
l_update_sql VARCHAR2(32000);
DELETE FROM
bsc_bis_measures_data m
WHERE
m.user_id = p_user_id AND
m.responsibility_id = p_responsibility_id AND
m.indicator IN (
SELECT
tk.indicator
FROM
bsc_tab_indicators tk
WHERE
tk.tab_id = p_tab_id
) AND (
m.indicator NOT IN (
SELECT
a.indicator
FROM
bsc_user_kpi_access a
WHERE
a.responsibility_id = p_responsibility_id
) OR
m.indicator = (
SELECT
k.indicator
FROM
bsc_kpis_b k
WHERE
k.indicator = m.indicator AND (
k.prototype_flag = 2 OR
k.last_update_date > m.last_update_date)
));
l_sql := 'SELECT tk.indicator'||
' FROM bsc_tab_indicators tk, bsc_user_kpi_access ka'||
' WHERE tk.tab_id = :1 AND tk.indicator = ka.indicator AND ka.responsibility_id = :2';
l_caching_sql := 'SELECT caching_key'||
' FROM bsc_bis_measures_data'||
' WHERE user_id = :1 AND responsibility_id = :2 AND indicator = :3 AND'||
' analysis_option0 = :4 AND analysis_option1 = :5 AND analysis_option2 = :6 AND'||
' series_id = :7';
l_update_sql := 'UPDATE bsc_bis_measures_data'||
' SET actual_data = :a, budget_data = :b, caching_key = :c,'||
' last_updated_by = :d, last_update_date = :e'||
' WHERE user_id = :f AND responsibility_id = :g AND indicator = :h AND'||
' analysis_option0 = :i AND analysis_option1 = :j AND analysis_option2 = :k AND'||
' series_id = :l';
l_insert_sql := 'INSERT INTO bsc_bis_measures_data (user_id, responsibility_id, indicator,'||
' analysis_option0, analysis_option1, analysis_option2, series_id,'||
' caching_key, actual_data, budget_data, created_by, creation_date,'||
' last_updated_by, last_update_date, last_update_login)'||
' VALUES (:a, :b, :c, :d, :e, :f, :g, :h, :i, :j, :k, :l, :m, :n, :o)';
l_kpi_info_rec.insert_update_flag := 'U';
l_kpi_info_rec.insert_update_flag := 'I';
IF l_kpi_info_tbl(i).insert_update_flag = 'U' THEN
-- Update actual and target in BSC_BIS_MEASURES_DATA
EXECUTE IMMEDIATE l_update_sql USING l_kpi_info_tbl(i).actual_value,
l_kpi_info_tbl(i).target_value, p_caching_key, l_db_user_id,
l_sysdate, p_user_id, p_responsibility_id, l_kpi_info_tbl(i).kpi_code,
l_kpi_info_tbl(i).analysis_option0, l_kpi_info_tbl(i).analysis_option1,
l_kpi_info_tbl(i).analysis_option2, l_kpi_info_tbl(i).series_id;
EXECUTE IMMEDIATE l_insert_sql USING p_user_id, p_responsibility_id,
l_kpi_info_tbl(i).kpi_code, l_kpi_info_tbl(i).analysis_option0,
l_kpi_info_tbl(i).analysis_option1, l_kpi_info_tbl(i).analysis_option2,
l_kpi_info_tbl(i).series_id, p_caching_key,
l_kpi_info_tbl(i).actual_value, l_kpi_info_tbl(i).target_value,
l_db_user_id, l_sysdate, l_db_user_id, l_sysdate, l_db_user_id;
l_sql := 'SELECT attribute_name, session_value, session_description'||
' FROM bis_user_attributes'||
' WHERE user_id = :1 AND page_id = :2 AND dimension = :3';
l_sql := 'SELECT attribute_name, session_value, session_description'||
' FROM bis_user_attributes'||
' WHERE user_id = :1 AND page_id = :2 AND attribute_name = :3';
l_measure_attribute_codes.DELETE;
l_sql := 'SELECT BID.SHORT_NAME DIMENSION_SHORTNAME, '||
' BKL.LEVEL_SHORTNAME '||
' FROM BSC_KPI_DIM_LEVELS_VL BKL,'||
' BIS_LEVELS BIL,'||
' BIS_DIMENSIONS BID'||
' WHERE BKL.INDICATOR = :1 AND'||
' BKL.DIM_SET_ID = :2 AND'||
' BKL.LEVEL_SOURCE = ''PMF'' AND'||
' (BKL.DEFAULT_VALUE = ''C'') AND'||
' BKL.LEVEL_SHORTNAME = BIL.SHORT_NAME AND'||
' BIL.DIMENSION_ID = BID.DIMENSION_ID'||
' ORDER BY DIMENSION_SHORTNAME, BKL.DIM_LEVEL_INDEX';
l_sql := 'SELECT BID.SHORT_NAME DIMENSION_SHORTNAME, '||
' BKL.LEVEL_SHORTNAME'||
' FROM BSC_KPI_DIM_LEVELS_VL BKL,'||
' BIS_LEVELS BIL,'||
' BIS_DIMENSIONS BID'||
' WHERE BKL.INDICATOR = :1 AND'||
' BKL.DIM_SET_ID = :2 AND'||
' BKL.LEVEL_SOURCE = ''PMF'' AND'||
' BKL.LEVEL_SHORTNAME = BIL.SHORT_NAME AND'||
' BIL.DIMENSION_ID = BID.DIMENSION_ID AND'||
' BID.SHORT_NAME = :3';
SELECT attribute7, attribute14 FROM ak_region_items
WHERE region_code = l_region_code AND attribute_code = l_attribute_code;
l_insert_sql VARCHAR2(32000);
l_update_sql VARCHAR2(32000);
l_sql := 'SELECT tk.indicator'||
' FROM bsc_tab_indicators tk, bsc_user_kpi_access ka'||
' WHERE tk.tab_id = :1 AND tk.indicator = ka.indicator AND ka.responsibility_id = :2';
l_caching_sql := 'SELECT caching_key'||
' FROM bsc_bis_measures_data'||
' WHERE user_id = :1 AND responsibility_id = :2 AND indicator = :3 AND'||
' analysis_option0 = :4 AND analysis_option1 = :5 AND analysis_option2 = :6 AND'||
' series_id = :7';
l_update_sql := 'UPDATE bsc_bis_measures_data'||
' SET actual_data = :a, budget_data = :b, caching_key = :c,'||
' last_updated_by = :d, last_update_date = :e'||
' WHERE user_id = :f AND responsibility_id = :g AND indicator = :h AND'||
' analysis_option0 = :i AND analysis_option1 = :j AND analysis_option2 = :k AND'||
' series_id = :l';
l_insert_sql := 'INSERT INTO bsc_bis_measures_data (user_id, responsibility_id, indicator,'||
' analysis_option0, analysis_option1, analysis_option2, series_id,'||
' caching_key, actual_data, budget_data, created_by, creation_date,'||
' last_updated_by, last_update_date, last_update_login)'||
' VALUES (:a, :b, :c, :d, :e, :f, :g, :h, :i, :j, :k, :l, :m, :n, :o)';
DELETE FROM
bsc_bis_measures_data m
WHERE
m.user_id = p_user_id AND
m.responsibility_id = p_responsibility_id AND
m.indicator = p_kpi_info_tbl(i).kpi_code
AND (
m.indicator NOT IN (
SELECT
a.indicator
FROM
bsc_user_kpi_access a
WHERE
a.responsibility_id = p_responsibility_id
) OR
m.indicator = (
SELECT
k.indicator
FROM
bsc_kpis_b k
WHERE
k.indicator = m.indicator AND (
k.prototype_flag = 2 OR
k.last_update_date > m.last_update_date)
));
-- The record exists --> Calculate data and update if caching key is different
-- Update actual and target in BSC_BIS_MEASURES_DATA
EXECUTE IMMEDIATE l_update_sql USING p_kpi_info_tbl(i).actual_value,
p_kpi_info_tbl(i).target_value, p_caching_key, l_db_user_id,
l_sysdate, p_user_id, p_responsibility_id, p_kpi_info_tbl(i).kpi_code,
p_kpi_info_tbl(i).analysis_option0, p_kpi_info_tbl(i).analysis_option1,
p_kpi_info_tbl(i).analysis_option2, p_kpi_info_tbl(i).series_id;
--dbms_output.put_line('*update executed');
-- The record does not exists --> Calculate data and Insert
-- Insert actual and target in BSC_BIS_MEASURES_DATA
EXECUTE IMMEDIATE l_insert_sql USING p_user_id, p_responsibility_id,
p_kpi_info_tbl(i).kpi_code, p_kpi_info_tbl(i).analysis_option0,
p_kpi_info_tbl(i).analysis_option1, p_kpi_info_tbl(i).analysis_option2,
p_kpi_info_tbl(i).series_id, p_caching_key,
p_kpi_info_tbl(i).actual_value, p_kpi_info_tbl(i).target_value,
l_db_user_id, l_sysdate, l_db_user_id, l_sysdate, l_db_user_id;
--dbms_output.put_line('*insert executed');