[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| select query. |
| |
| 12-MAR-2003 ADRAO FIXED Bug #2834277 |
| 20-MAR-03 PWALI for bug #2843082 |
| 13-MAY-2003 PWALI Bug #2942895, SQL BIND COMPLIANCE |
| 04-APR-03 ASHANKAR Fix for the bug#2883880 added new procedure Update_Bsc_Dataset |
| 13-JUN-03 ADEULGAO Bug#2878840, Modified function Create_Bsc_Dimension to have |
| single DIM group for including all DIM LEVELS imported |
| 05-DEC-03 PAJOHRI Removed use of All_Objects, Bug #3236002 |
| 27-FEB-2004 adeulgao fixed bug#3431750 |
| 25-OCT-2005 kyadamak Removed literals for Enhancement#4618419 |
+======================================================================================+
*/
G_PKG_NAME varchar2(30) := 'BSC_PMF_UI_API_PUB';
g_Bsc_Pmf_Dim_Tbl.delete(i);
g_Bsc_Kpi_Tbl.delete(i);
Update_Bsc_Dataset( p_commit
,p_Bsc_Pmf_Ui_Rec
,x_return_status
,x_msg_count
,x_msg_data);
select distinct(name)
into g_Bsc_Pmf_Ui_Rec.Measure_Long_Name
from bis_indicators_vl
where short_name = p_Bsc_Pmf_Ui_Rec.Measure_Short_Name;
select distinct(dimension_name)
into g_Bsc_Pmf_Ui_Rec.Dimension_Long_Name
from bisfv_dimensions
where dimension_short_name = p_Bsc_Pmf_Ui_Rec.Dimension_Short_Name;
select distinct source
-- into g_source
into g_Bsc_Pmf_Dim_Tbl(i).Dimension_Level_Source
from bisfv_dimension_levels
where upper(dimension_level_short_name) = upper(p_Bsc_Pmf_Dim_Tbl(i).Dimension_Level_Short_Name);
select distinct dimension_level_name, level_values_view_name, 'ID', 'value'
-- select distinct dimension_level_name, level_values_view_name, 'rownum', 'value'
into g_Bsc_Pmf_Dim_Tbl(i).Dimension_Level_Long_Name,
g_Bsc_Pmf_Dim_Tbl(i).Dimension_Level_View_Name,
g_Bsc_Pmf_Dim_Tbl(i).Dimension_Level_Pk_Key,
g_Bsc_Pmf_Dim_Tbl(i).Dimension_Level_Name_Column
from bisbv_dimension_levels
where upper(dimension_level_short_name) = upper(p_Bsc_Pmf_Dim_Tbl(i).Dimension_Level_Short_Name);
l_sql := 'select max(length(value)) ' ||
'from ' || g_Bsc_Pmf_Dim_Tbl(i).Dimension_Level_View_Name;
select distinct dimension_level_name
,dimension_level_short_name || '_LTC'
,level_values_view_name
into g_Bsc_Pmf_Dim_Tbl(i).Dimension_Level_Long_Name
,g_Bsc_Pmf_Dim_Tbl(i).Dimension_Level_View_Name
,l_alternate_level_view
from bisfv_dimension_levels
where dimension_level_short_name = p_Bsc_Pmf_Dim_Tbl(i).Dimension_Level_Short_Name;
l_sql1 := ' select distinct level_table_col_name ' ||
' from edw_level_Table_atts_md_v ' ||
' where key_type=''UK'' and ' ||
' upper(level_Table_name) = upper(''' || g_Bsc_Pmf_Dim_Tbl(i).Dimension_Level_View_Name || ''') and ' ||
' upper(level_table_col_name) like ''%PK_KEY%''';
l_sql1 := ' SELECT column_name ' ||
' FROM ALL_TAB_COLUMNS ' ||
' WHERE table_name = UPPER(:1) AND ' ||
' column_name LIKE ''%PK_KEY%'''||
' AND OWNER = :2 ';
l_sql1 := ' select level_table_col_name ' ||
' from edw_level_Table_atts_md_v ' ||
' where upper(level_Table_name) = upper(''' || g_Bsc_Pmf_Dim_Tbl(i).Dimension_Level_View_Name || ''') and ' ||
' (upper(level_table_col_name) like ''%DESCRIPTION%'' or ' ||
' upper(level_table_col_name) like ''NAME%'') and ' ||
' rownum < 2';
l_sql1 := ' select column_name ' ||
' from ALL_TAB_COLUMNS ' ||
' where table_name = upper(:1) and ' ||
' (column_name like ''%DESCRIPTION%'' or ' ||
' column_name like ''NAME%'') ' ||
' AND OWNER = :2 '||
' AND rownum < 2';
l_sql := 'select max(length(' || g_Bsc_Pmf_Dim_Tbl(i).Dimension_Level_Name_Column || ')) ' ||
'from ' || g_Bsc_Pmf_Dim_Tbl(i).Dimension_Level_View_Name;
select distinct dim_level_id
into g_Bsc_Dim_Group_Rec.Bsc_Level_Id
from BSC_SYS_DIM_LEVELS_B
where SHORT_NAME = g_Bsc_Pmf_Dim_Tbl(i).Dimension_Level_Short_Name;
procedure Update_Bsc_Dataset(
p_commit IN VARCHAR2 := FND_API.G_TRUE
,p_Bsc_Pmf_Ui_Rec IN BSC_PMF_UI_API_PUB.Bsc_Pmf_Ui_Rec_Type
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
) is
l_language VARCHAR2(2000);
SELECT language_code
FROM fnd_languages
WHERE installed_flag IN ('I','B') AND language_code <> USERENV('LANG');
SELECT A.MEASURE_ID, B.DATASET_ID
INTO l_Measure_Id,
l_dataset_id
FROM BSC_SYS_MEASURES A,
BSC_SYS_DATASETS_B B
WHERE A.SHORT_NAME = l_Dataset_Rec.Bsc_Measure_Short_Name
AND A.SOURCE = 'PMF'
AND A.MEASURE_ID = B.MEASURE_ID1;
SELECT T.NAME,
T.DESCRIPTION,
T.SOURCE_LANG
INTO l_Dataset_Rec.Bsc_Dataset_Name,
l_Dataset_Rec.Bsc_Dataset_Help,
l_Dataset_Rec.Bsc_Source_Language
FROM BIS_INDICATORS_TL T,
BIS_INDICATORS B
WHERE T.INDICATOR_ID = B.INDICATOR_ID
AND B.SHORT_NAME = l_Dataset_Rec.Bsc_Measure_Short_Name
AND T.LANGUAGE = l_language;
BSC_DATASETS_PUB.Update_Dataset
(
p_commit => p_commit
, p_Dataset_Rec => l_Dataset_Rec
, p_update_dset_calc => FALSE
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
END Update_Bsc_Dataset;
select max(dim_set_id) + 1
into g_Bsc_Dimset_Rec.Bsc_Dim_Set_Id
from BSC_KPI_DIM_SETS_TL
where indicator = g_Bsc_Pmf_Ui_Rec.Kpi_Id;
select TOTAL_DISP_NAME, COMP_DISP_NAME
into g_Bsc_Dimset_Rec.Bsc_Dim_Tot_Disp_Name,
g_Bsc_Dimset_Rec.Bsc_Dim_Comp_Disp_Name
from BSC_SYS_DIM_LEVELS_VL
where SHORT_NAME = g_Bsc_Pmf_Dim_Tbl(i).Dimension_Level_Short_Name;
select distinct dim_group_id
into g_Bsc_Dimset_Rec.Bsc_Dim_Level_Group_Id
from BSC_SYS_DIM_GROUPS_VL
where upper(name) = upper(g_Bsc_Dim_Group_Rec.Bsc_Dim_Level_Group_Name);
select distinct a.dataset_id
into g_Bsc_Anal_Opt_Rec.Bsc_Dataset_Id
from BSC_SYS_DATASETS_B a,
BSC_SYS_MEASURES b
where upper(b.short_name) = upper(p_Bsc_Pmf_Ui_Rec.Measure_Short_Name)
and a.measure_id1 = b.measure_id
and rownum < 2;
select count(option_id) + 1
into g_Bsc_Anal_Opt_Rec.Bsc_Option_Group0
from BSC_KPI_ANALYSIS_OPTIONS_B
where indicator = g_Bsc_Anal_Opt_Rec.Bsc_Kpi_Id
and analysis_group_id = g_Bsc_Anal_Opt_Rec.Bsc_Analysis_Group_Id;
select count(option_id)
into l_count
from BSC_KPI_ANALYSIS_OPTIONS_B
where indicator = g_Bsc_Anal_Opt_Rec.Bsc_Kpi_Id
and analysis_group_id = g_Bsc_Anal_Opt_Rec.Bsc_Analysis_Group_Id;
select count(option_id)
into l_count
from BSC_KPI_ANALYSIS_OPTIONS_VL
where name = 'Option 0'
and indicator = g_Bsc_Anal_Opt_Rec.Bsc_Kpi_Id;
select dataset_id
into l_count
from BSC_KPI_ANALYSIS_MEASURES_B
where indicator = g_Bsc_Anal_Opt_Rec.Bsc_Kpi_Id
and analysis_option0 = 0
and analysis_option1 = g_Bsc_Anal_Opt_Rec.Bsc_Option_Group1
and analysis_option2 = g_Bsc_Anal_Opt_Rec.Bsc_Option_Group2
and series_id = 0;
BSC_ANALYSIS_OPTION_PUB.Update_Analysis_Options( FND_API.G_TRUE
,g_Bsc_Anal_Opt_Rec
,x_return_status
,x_msg_count
,x_msg_data);
select distinct dim_level_id
into v_Bsc_Dim_Group_Rec.Bsc_Level_Id
from BSC_SYS_DIM_LEVELS_B
where SHORT_NAME = p_Bsc_Pmf_Dim_Rec.Dimension_Level_Short_Name;
select distinct source
into x_Bsc_Pmf_Dim_Rec.Dimension_Level_Source
from bisfv_dimension_levels
where upper(dimension_level_short_name) = upper(p_Dim_Level_Short_Name);
select distinct dimension_level_name, level_values_view_name, 'ID', 'value'
-- select distinct dimension_level_name, level_values_view_name, 'rownum', 'value'
into x_Bsc_Pmf_Dim_Rec.Dimension_Level_Long_Name,
x_Bsc_Pmf_Dim_Rec.Dimension_Level_View_Name,
x_Bsc_Pmf_Dim_Rec.Dimension_Level_Pk_Key,
x_Bsc_Pmf_Dim_Rec.Dimension_Level_Name_Column
from bisbv_dimension_levels
where upper(dimension_level_short_name) = upper(p_Dim_Level_Short_Name);
select distinct dimension_level_name
,dimension_level_short_name || '_LTC'
,level_values_view_name
into x_Bsc_Pmf_Dim_Rec.Dimension_Level_Long_Name
,x_Bsc_Pmf_Dim_Rec.Dimension_Level_View_Name
,l_alternate_level_view
from bisbv_dimension_levels
where upper(dimension_level_short_name) = upper(p_Dim_Level_Short_Name);
l_sql1 := ' select distinct level_table_col_name ' ||
' from edw_level_Table_atts_md_v ' ||
' where key_type=''UK'' and ' ||
' upper(level_Table_name) = upper(:1) and ' ||
' upper(level_table_col_name) like ''%PK_KEY%''';
l_sql1 := 'select level_table_col_name ' ||
' from edw_level_Table_atts_md_v ' ||
' where upper(level_Table_name) = upper(:1) and ' ||
' (upper(level_table_col_name) like ''%DESCRIPTION%'' or ' ||
' upper(level_table_col_name) like ''NAME%'') and ' ||
' rownum < 2';
l_sql := 'select max(length(' || x_Bsc_Pmf_Dim_Rec.Dimension_Level_Name_Column || '))' ||
' from ' || x_Bsc_Pmf_Dim_Rec.Dimension_Level_View_Name ||
' order by NVL(:1,:2) ';
select count(LEVEL_TABLE_NAME)
into l_count
from bsc_sys_dim_levels_vl
where SHORT_NAME = 'p_Short_Name';
select LEVEL_TABLE_NAME
into l_view_name
from bsc_sys_dim_levels_vl
where SHORT_NAME = p_Short_Name;
select count(object_name)
into l_count
from user_objects
where object_name = upper(l_view_name);
SELECT region_code
FROM ak_region_items
WHERE attribute1='MEASURE'
AND attribute2 = p_Measure_Short_Name
ORDER BY creation_date DESC;
SELECT DISTINCT nested_region_code
FROM ak_region_items
WHERE region_code = l_Region_Code
AND item_style = 'NESTED_REGION';
SELECT attribute1
FROM ak_regions
WHERE region_code = l_Region_Code;
SELECT attribute2, attribute1, required_flag
FROM ak_region_items
WHERE region_code = l_Region_Code
AND (attribute1 = 'DIMENSION LEVEL' OR attribute1 = 'HIDE PARAMETER' OR attribute1 = 'HIDE VIEW BY DIMENSION')
ORDER BY attribute2, attribute1;
SELECT attribute2, attribute1, region_code, required_flag
FROM ak_region_items
WHERE (region_code = l_Region_Code OR region_code = l_nested_region_code)
AND (attribute1 = 'DIMENSION LEVEL' OR attribute1 = 'HIDE PARAMETER' OR attribute1 = 'HIDE VIEW BY DIMENSION')
ORDER BY attribute2, attribute1, region_code;