[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
REM | Update_Dim_Object() for bug#3842366 |
REM | 17-AUG-2004 wleung modified Bug#3784852 Assign_Unassign_Dimensions |
REM | 09-SEP-2004 visuri Shifted Remove_Empty_Dims_For_DimSet() from |
REM | BSC_BIS_KPI_MEAS_PUB to BSC_BIS_DIMENSION_PUB |
REM | 14-SEP-2004 KYADAMAK added function get_valid_ddlentry_frm_name() |
REM | 08-OCT-2004 rpenneru added Modified for bug#3939995 |
REM | 21-OCT-2004 akoduri added Modified for bug#3930280 |
REM | 03-FEB-2005 kyadamak modified for the bug#4091924 |
REM | 08-Feb-04 skchoudh Enh#3873195 drill_to_form_function column |
REM | is added |
REM | 14-FEB-2005 ashankar modified Alter_One_By_N_Tables and |
REM | Alter_M_By_N_Tables.Added the cascading logic |
REM | to update RELATION_COL in BSC_SYS_DIM_LEVEL_RELS |
REM | 15-FEB-05 ppandey Enh #4016669, support ID, Value for Autogen DO |
REM | 16-FEB-2005 ashankar Bug#4184438 Added the Synch Up API |
REM | BSC_SYNC_MVLOGS.Sync_dim_table_mv_log |
REM | 11-Mar-05 ankgoel Enh#2690720 - AG Report enh |
REM | 30-Mar-05 ankgoel Support 'All' enable/disable for BSC dim objects|
REM | from Report Designer |
REM | 31-MAR-05 adrao Modified API check_sametype_dims to remove |
REM | disctinction betweem BSC and BIS Dimesion Objs |
REM | 08-APR-2005 kyadamak generating unique master table for PMF dimension|
REM | objects for the bug# 4290359 |
REM | 02-May-2005 visuri Modified for Bug#4323383 |
REM | 18-Jul-2005 ppandey Enh #4417483, Restrict Internal/Calendar Dims |
REM | 20-Jul-2005 ppandey Bug #4495539, MIXED Dim Obj not allowed from DD |
REM | 22-Jul-2005 kyadamak Modified for bug#4091924 |
REM | 09-AUG-2005 adrao Fixed Bug#4383962 for prototype value gen |
REM | 11-AUG-2005 ppandey Bug #4324947 Validation for Dim,Dim Obj in Rpt |
REM | 27-Sep-2005 ankgoel Bug#4625598,4626579 Uptake common API to get dim|
REM | level values |
REM | 27-SEP-2005 ashankar Bug#4630859 Removed the duplicate objectives |
REM | 28-SEP-2005 ashankar Bug#4630892 Added a new API is_Obj_Display_Frmt_Change |
REM | which will check for format changes and the API |
REM | is_KPI_Flag_For_DimObject check for Structural |
REM | Changes |
REM | 25-OCT-2005 kyadamak Removed literals for Enhancement#4618419 |
REM | 27-DEC-2005 kyadamak Calling BIA API for bug#4875047 |
REM | 02-Jan-2006 akoduri Bug#4611303 - Support For Enable/Disable All |
REM | In Report Designer |
REM | 06-Jan-2006 akoduri Enh#4739401 - Hide Dimensions/Dim Objects |
REM | 13-jan-2005 ashankar Bug#4947293 calling the API sync_dimension_table|
REM | dynamically |
REM | 31-JAN-2006 adrao Added APIs |
REM | - Is_Recursive_Relationship |
REM | - Get_Unique_Level_Pk_Col |
REM | |
REM | Also modified the logic to ensure that when a |
REM | PMF DO is under a recursive relationship, the |
REM | corresponding BSC View is also changed |
REM | 10-FEB-2006 akoduri Bug#4997042 Cascade 'All' property from dim |
REM | designer to dim groups of Reports |
REM | 15-FEB-2006 visuri bug#4757375 Calendar Create Performance Issue |
REM | 31-MAR-2006 akoduri Bug #5048186 No View creation for obsoleted |
REM | BIS Dimension objects |
REM | 26-Apr-2006 psomesul Enh#5124125 - Creating BSC wrapper view of a |
REM | BIS type Dim. Obj. |
REM | 15-JUN-2006 ashankar Bug#5254737 Made changes to Create_Dim_Object |
REM | Method.Removed the parameter value 'TRUE' in |
REM | FND_MESSAGE.SET_TOKEN API |
REM | 20-FEB-2006 akoduri Bug #5880618 Validate_PMF_Base_View API is |
REM | is failing when more than one parent is passed |
REM | 29-JUN-2007 akoduri Bug #6155820 Structural change warning is not |
REM | displayed during create dimension object |
REM | 21-SEP-2007 ankgoel Bug#6391292 - Handled validation of Manager's |
REM | dim object view separately |
REM | 09-JAN-2008 lbodired Bug#6707712 PL/SQL error while creating custom |
REM | Period for the calendar |
REM +=======================================================================+
*/
CONFIG_LIMIT_DIM CONSTANT NUMBER := 8;
SELECT dim_level_id
FROM BSC_SYS_DIM_LEVEL_RELS
WHERE parent_dim_level_id = p_Dim_Level_Id
AND RELATION_TYPE <> 2;
SELECT B.Short_Name
FROM BIS_LEVELS A
, BIS_DIMENSIONS B
WHERE A.Short_Name = p_Dim_Obj_Short_Name
AND A.Dimension_Id = B.Dimension_Id;
UPDATE BSC_SYS_DIM_LEVELS_BY_GROUP
SET Total_Flag = p_All_Enabled
WHERE Dim_Level_Id = l_Bsc_Dim_Obj_ID
AND Dim_Group_Id = l_Bsc_Group_ID;
update relation col in case of m_by_n relation type
Modified for bug#4091924
*********************************************************************************************/
PROCEDURE Update_M_By_N_Relation_col
( p_Dim_Level_Id IN NUMBER
, p_Parent_Dim_Level_Id IN NUMBER
, p_Relation_Col IN VARCHAR2
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
) IS
CURSOR c_Relation_Col IS
SELECT K.indicator
,K.dim_set_id
,K.dim_level_index
,K.level_table_name
FROM bsc_kpi_dim_levels_vl K
,bsc_sys_dim_levels_b S
WHERE S.level_Table_name = K.level_table_name
AND S.dim_level_id IN ( p_Dim_Level_Id,p_Parent_Dim_Level_Id)
AND K.parent_level_rel IS NOT NULL
AND K.table_relation IS NOT NULL;
UPDATE bsc_sys_dim_level_rels
SET relation_col = p_Relation_Col
WHERE dim_level_id = p_Dim_Level_Id
AND parent_dim_level_id = p_Parent_Dim_Level_Id
AND relation_type = 2;
UPDATE bsc_sys_dim_level_rels
SET relation_col = p_Relation_Col
WHERE dim_level_id = p_Parent_Dim_Level_Id
AND parent_dim_level_id = p_Dim_Level_Id
AND relation_type = 2;
UPDATE bsc_kpi_dim_levels_b
SET table_relation = p_Relation_Col
WHERE indicator = CD.indicator
AND dim_set_id = CD.dim_set_id
AND dim_level_index = CD.dim_level_index;
END Update_M_By_N_Relation_col;
IF (p_Dim_Short_Name IS NOT NULL) THEN -- Update Mode
UPDATE bsc_sys_dim_levels_by_group
SET total_flag = p_All_Enabled
WHERE dim_level_id = BSC_BIS_DIM_OBJ_PUB.Get_Bsc_Dim_Obj_ID(p_Dim_Obj_Short_Name)
AND dim_group_id = BSC_BIS_DIMENSION_PUB.Get_Bsc_Dimension_ID(p_Dim_Short_Name);
UPDATE bsc_sys_dim_levels_by_group
SET total_flag = p_All_Enabled
WHERE dim_level_id = BSC_BIS_DIM_OBJ_PUB.Get_Bsc_Dim_Obj_ID(p_Dim_Obj_Short_Name);
will be inserted into the following tables.
1 BIS_LEVELS.
2. BIS_LEVELS_TL
3. BSC_SYS_DIM_LEVELS_B
4. BSC_SYS_DIM_LEVELS_TL
Key
p_dim_obj_short_name
Validations:
1. p_dimension_id, p_dim_obj_short_name, p_display_name, p_data_source, p_application_id
and p_source_type must not be null.
2. p_dim_obj_short_name and p_display_name must be unique.
*/
PROCEDURE Create_Dim_Object
( p_commit IN VARCHAR2 := FND_API.G_TRUE
, p_dim_obj_short_name IN VARCHAR2
, p_display_name IN VARCHAR2
, p_application_id IN NUMBER
, p_description IN VARCHAR2
, p_data_source IN VARCHAR2
, p_source_table IN VARCHAR2
, p_where_clause IN VARCHAR2 := NULL
, p_comparison_label_code IN VARCHAR2
, p_table_column IN VARCHAR2
, p_source_type IN VARCHAR2
, p_maximum_code_size IN NUMBER
, p_maximum_name_size IN NUMBER
, p_all_item_text IN VARCHAR2
, p_comparison_item_text IN VARCHAR2
, p_prototype_default_value IN VARCHAR2
, p_dimension_values_order IN NUMBER
, p_comparison_order IN NUMBER
, p_dim_short_names IN VARCHAR2
, p_Master_Level IN VARCHAR2 := NULL
, p_Long_Lov IN VARCHAR2 := FND_API.G_FALSE
, p_Search_Function IN VARCHAR2 := NULL
, p_Dim_Obj_Enabled IN VARCHAR2 := FND_API.G_FALSE
, p_View_Object_Name IN VARCHAR2 := NULL
, p_Default_Values_Api IN VARCHAR2 := NULL
, p_All_Enabled IN NUMBER := NULL
, p_is_default_short_name IN VARCHAR2
, p_Drill_To_Form_Function IN VARCHAR2 := NULL
, p_Restrict_Dim_Validate IN VARCHAR2 := NULL
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
) IS
l_bsc_dim_obj_rec BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type;
SELECT Short_Name
, Master_Level
, Name
FROM BIS_LEVELS_VL
WHERE Short_Name = p_Master_Level;
SELECT NVL(MAX(dim_level_id) + 1, 0)
INTO l_count
FROM BSC_SYS_DIM_LEVELS_B;
SELECT COUNT(1) INTO l_count
FROM (SELECT COUNT(1) rec_count
FROM BSC_SYS_DIM_LEVELS_VL
WHERE UPPER(Short_Name) = UPPER(l_temp_var)
UNION
SELECT COUNT(1) rec_count
FROM BIS_LEVELS_VL
WHERE UPPER(Short_Name) = UPPER(l_temp_var))
WHERE rec_count > 0;
SELECT COUNT(1) INTO l_count
FROM BIS_LEVELS_VL
WHERE UPPER(short_name) = UPPER(l_dim_obj_name);
SELECT COUNT(1) INTO l_count
FROM BSC_SYS_DIM_LEVELS_VL
WHERE UPPER(short_name) = UPPER(l_dim_obj_name);
SELECT COUNT(1) INTO l_count
FROM BIS_LEVELS_VL
WHERE UPPER(name) = UPPER(p_display_name);
ELSIF (p_data_source = 'BSC') THEN -- Allow insertion into BSC Data Model, with the same display name
SELECT COUNT(1) INTO l_count
FROM BIS_LEVELS_VL
WHERE UPPER(name) = UPPER(l_pmf_disp_name);
SELECT COUNT(1) INTO l_count
FROM BIS_LEVELS_VL
WHERE UPPER(name) = UPPER(l_pmf_disp_name);
( p_selected_dimensions => l_Dim_Tab
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
SELECT COUNT(1) INTO l_count
FROM BSC_SYS_DIM_LEVELS_B
WHERE UPPER(abbreviation) = UPPER(l_bsc_dim_obj_rec.Bsc_Level_Abbreviation);
SELECT COUNT(1) INTO l_count
FROM BSC_SYS_DIM_LEVELS_B
WHERE UPPER(abbreviation) = UPPER(l_temp_var);
SELECT COUNT(1) INTO l_count
FROM BSC_SYS_DIM_LEVELS_B
WHERE UPPER(Level_Table_Name) = UPPER(l_temp_var);
from Create/Update Dim Object screen. Only from RPD, 'All' flag can be '0' in
Create case.
*/
IF ((l_bsc_dim_obj_rec.Bsc_Source = 'BSC') AND (p_dim_short_names IS NOT NULL)) THEN
BSC_BIS_DIM_OBJ_PUB.Set_Bsc_All_Enable_Flag
( p_commit => FND_API.G_FALSE
, p_Dim_Obj_Short_Name => l_dim_obj_name
, p_All_Enabled => p_All_Enabled
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
SELECT COUNT(1) INTO l_count
FROM BSC_SYS_DIM_LEVELS_BY_GROUP A
, BSC_SYS_DIM_LEVELS_B B
WHERE A.Dim_Group_Id = l_dim_grp_id
AND A.Dim_Level_Id = B.Dim_Level_Id
AND B.Short_Name = p_dim_obj_short_name;
SELECT COUNT(1)
INTO l_count
FROM BSC_SYS_DIM_LEVELS_BY_GROUP A
,BSC_SYS_DIM_LEVELS_B L
,BSC_SYS_DIM_GROUPS_TL D
WHERE D.SHORT_NAME = l_assign
AND L.SHORT_NAME = p_dim_obj_short_name
AND L.DIM_LEVEL_ID = A.DIM_LEVEL_ID
AND D.DIM_GROUP_ID = A.DIM_GROUP_ID;
, p_Entity_Action_Type => BSC_UTILITY.c_UPDATE
, x_Return_Status => x_return_status
, x_Msg_Count => x_msg_count
, x_Msg_Data => x_msg_data
);
SELECT DIM_NAME
INTO l_dim_name
FROM BSC_BIS_DIM_VL
WHERE SHORT_NAME = l_unassign;
SELECT NAME
INTO l_dim_obj_name
FROM BSC_BIS_DIM_OBJS_VL
WHERE SHORT_NAME = p_dim_obj_short_name;
SELECT B.Short_Name
FROM BIS_LEVELS A
, BIS_DIMENSIONS B
WHERE A.Short_Name = p_Dim_Obj_Short_Name
AND A.Dimension_Id = B.Dimension_Id;
UPDATE DIMENSION
*********************************************************************************/
/*
This procedure allows user to update a dimension. These changes will
be reflected into the following metadata: -
1 BIS_LEVELS.
2. BIS_LEVELS_TL
3. BSC_SYS_DIM_LEVELS_B
4. BSC_SYS_DIM_LEVELS_TL
Key:
'p_dim_obj_short_name'
Validations:
1. p_dimension_id, p_dim_obj_short_name, p_display_name, p_data_source, p_application_id
and p_source_type must not be null.
2. p_dim_obj_short_name and p_display_name must be unique.
*/
PROCEDURE Update_Dim_Object
( p_commit IN VARCHAR2 := FND_API.G_TRUE
, p_dim_obj_short_name IN VARCHAR2
, p_display_name IN VARCHAR2
, p_application_id IN NUMBER
, p_description IN VARCHAR2
, p_data_source IN VARCHAR2
, p_source_table IN VARCHAR2
, p_where_clause IN VARCHAR2 := NULL
, p_comparison_label_code IN VARCHAR2
, p_table_column IN VARCHAR2
, p_source_type IN VARCHAR2
, p_maximum_code_size IN NUMBER
, p_maximum_name_size IN NUMBER
, p_all_item_text IN VARCHAR2
, p_comparison_item_text IN VARCHAR2
, p_prototype_default_value IN VARCHAR2
, p_dimension_values_order IN NUMBER
, p_comparison_order IN NUMBER
, p_assign_dim_short_names IN VARCHAR2
, p_unassign_dim_short_names IN VARCHAR2
, p_time_stamp IN VARCHAR2 := NULL -- Granular Locking
, p_Master_Level IN VARCHAR2 := NULL
, p_Long_Lov IN VARCHAR2 := FND_API.G_FALSE
, p_Search_Function IN VARCHAR2 := NULL
, p_Dim_Obj_Enabled IN VARCHAR2 := FND_API.G_FALSE
, p_View_Object_Name IN VARCHAR2 := NULL
, p_Default_Values_Api IN VARCHAR2 := NULL
, p_All_Enabled IN NUMBER := NULL
, p_Drill_To_Form_Function IN VARCHAR2 := NULL
, p_Restrict_Dim_Validate IN VARCHAR2 := NULL
, p_Hide IN VARCHAR2 := FND_API.G_FALSE
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
) IS
l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
SELECT short_name
FROM BSC_SYS_DIM_GROUPS_VL
WHERE dim_group_id IN (SELECT dim_group_id
FROM BSC_SYS_DIM_LEVELS_BY_GROUP
WHERE dim_level_id = l_bsc_dim_obj_rec.Bsc_Level_Id);
SELECT DISTINCT A.indicator Indicator
, A.dim_set_id Dim_Set_Id
FROM BSC_KPI_DIM_LEVELS_B A
, BSC_SYS_DIM_LEVELS_B D
, BSC_KPIS_B B
WHERE A.Level_Table_Name = D.Level_Table_Name
AND B.Indicator = A.Indicator
AND B.Share_Flag <> 2
AND D.Dim_Level_Id = l_bsc_dim_obj_rec.Bsc_Level_Id;
SELECT B.SHORT_NAME
FROM BIS_LEVELS A
, BIS_DIMENSIONS B
WHERE B.DIMENSION_ID = A.DIMENSION_ID
AND A.SHORT_NAME = p_dim_obj_short_name;
SELECT Short_Name
, Master_Level
, Name
FROM BIS_LEVELS_VL
WHERE Short_Name = p_Master_Level;
SELECT DISTINCT A.INDICATOR Indicator,
A.DIM_SET_ID Dim_Set_Id
FROM BSC_KPI_DIM_LEVELS_VL A,
BSC_SYS_DIM_LEVELS_VL B,
BSC_KPIS_B C
WHERE A.LEVEL_TABLE_NAME=B.LEVEL_TABLE_NAME
AND C.INDICATOR = A.INDICATOR
AND C.SHARE_FLAG <> 2
AND INSTR(','||l_child_dim_obj_list||',', ','||b.dim_level_id||',') > 0;
SAVEPOINT UpdateBSCDimObjectPMD;
SELECT COUNT(1) INTO l_count
FROM BIS_LEVELS
WHERE short_name = l_dim_obj_name;
FND_MESSAGE.SET_NAME('BSC','BSC_MUSER_DELETE_MESSAGE');
SELECT COUNT(1) INTO l_count
FROM BSC_SYS_DIM_LEVELS_B
WHERE Short_Name = l_dim_obj_name;
FND_MESSAGE.SET_NAME('BSC','BSC_MUSER_DELETE_MESSAGE');
SELECT dim_level_id
, level_table_name
, user_key_size
, disp_key_size
, NVL(source, 'BSC')
, abbreviation
, level_pk_col
, name
, level_view_name
INTO l_bsc_dim_obj_rec.Bsc_Level_Id
, l_bsc_dim_obj_rec.Bsc_Level_Name --p_source_table
, l_bsc_dim_obj_rec.Bsc_Level_User_Key_Size --p_maximum_code_size
, l_bsc_dim_obj_rec.Bsc_Level_Disp_Key_Size --p_maximum_name_size
, l_bsc_dim_obj_rec.Bsc_Source --p_data_source
, l_bsc_dim_obj_rec.Bsc_Level_Abbreviation --p_prototype_default_value
, l_bsc_dim_obj_rec.Bsc_Pk_Col --p_table_column
, l_bis_dim_level_rec.Dimension_Level_Name
, l_bsc_dim_obj_rec.Bsc_Level_View_Name
FROM BSC_SYS_DIM_LEVELS_VL
WHERE short_name = l_dim_obj_name;
SELECT COUNT(1) INTO l_count
FROM BIS_LEVELS_VL
WHERE UPPER(short_name) <> UPPER(l_dim_obj_name)
AND UPPER(name) = UPPER(p_display_name);
ELSIF (p_data_source = 'BSC') THEN -- Allow insertion into BSC Data Model, with the same display name
SELECT COUNT(1) INTO l_count
FROM BIS_LEVELS_VL
WHERE UPPER(short_name) <> UPPER(l_dim_obj_name)
AND UPPER(name) = UPPER(l_pmf_disp_name);
SELECT COUNT(1) INTO l_count
FROM BIS_LEVELS_VL
WHERE UPPER(name) = UPPER(l_pmf_disp_name);
FND_MESSAGE.SET_NAME('BSC','BSC_NO_UPDATE_DATA_SOURCE');
BSC_BIS_LOCKS_PUB.Lock_Update_Dimension_Object
( p_Dim_Object_Id => l_bsc_dim_obj_rec.Bsc_Level_Id
,p_Selected_Dimensions => l_Dim_Tab
,p_time_stamp => p_time_stamp -- Granular Locking
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
SELECT COUNT(1) INTO l_count
FROM BSC_SYS_DIM_LEVELS_B
WHERE UPPER(abbreviation) = UPPER(l_bsc_dim_obj_rec.Bsc_Level_Abbreviation)
AND dim_level_id <> l_bsc_dim_obj_rec.Bsc_Level_Id
AND Source = 'BSC';
SELECT level_id INTO l_bis_dim_level_rec.Dimension_Level_ID
FROM BIS_LEVELS
WHERE short_name = l_dim_obj_name;
SELECT COUNT(1) INTO l_Count
FROM BIS_LEVELS
WHERE Master_Level = l_bis_dim_level_rec.Dimension_Level_Short_Name;
BIS_DIMENSION_LEVEL_PUB.Update_Dimension_Level
( p_api_version => 1.0
, p_commit => FND_API.G_FALSE
, p_validation_level => FND_API.G_VALID_LEVEL_FULL
, p_Dimension_Level_Rec => l_bis_dim_level_rec
, x_return_status => x_return_status
, x_error_Tbl => l_error_tbl
);
SELECT dim_level_id INTO l_bsc_dim_id
FROM BSC_SYS_DIM_LEVELS_B
WHERE UPPER(short_name) = UPPER(l_dim_obj_name);
BSC_BIS_KPI_MEAS_PUB.Delete_Dim_Objs_In_DSet
( p_commit => FND_API.G_FALSE
, p_kpi_id => l_MTab_Tbl(i).p_kpi_id
, p_dim_set_id => l_MTab_Tbl(i).p_dim_set_id
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
BSC_DIMENSION_LEVELS_PUB.Update_Dim_Level
( p_commit => FND_API.G_FALSE
, p_Dim_Level_Rec => l_bsc_dim_obj_rec
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
we are safe not to update 'All' flag for other dimension-dim object
relationships while coming from Dimension Designer.
*/
BSC_BIS_DIM_OBJ_PUB.Set_Bsc_All_Enable_Flag
( p_commit => FND_API.G_FALSE
, p_Dim_Obj_Short_Name => l_dim_obj_name
, p_Dim_Short_Name => p_assign_dim_short_names
, p_All_Enabled => p_All_Enabled
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
When a dimension object is updated, it is possible to assign
unassing the dimension objects from the dimensions.It is possible
that these dimensions might be used in some Kpis and part of the
list button. so we need to call the Validate_List_Button logic
here.
/************************************************************/
IF (l_bsc_dim_obj_rec.Bsc_Source = 'BSC') THEN
BSC_COMMON_DIM_LEVELS_PUB.Validate_List_Button
(
p_Kpi_Id => NULL
, p_Dim_Level_Id => l_bsc_dim_obj_rec.Bsc_Level_Id
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
ROLLBACK TO UpdateBSCDimObjectPMD;
ROLLBACK TO UpdateBSCDimObjectPMD;
ROLLBACK TO UpdateBSCDimObjectPMD;
x_msg_data := x_msg_data||' -> BSC_BIS_DIM_OBJ_PUB.Update_Dim_Object ';
x_msg_data := SQLERRM||' at BSC_BIS_DIM_OBJ_PUB.Update_Dim_Object ';
ROLLBACK TO UpdateBSCDimObjectPMD;
x_msg_data := x_msg_data||' -> BSC_BIS_DIM_OBJ_PUB.Update_Dim_Object ';
x_msg_data := SQLERRM||' at BSC_BIS_DIM_OBJ_PUB.Update_Dim_Object ';
END Update_Dim_Object;
UPDATE DIMENSION
*********************************************************************************/
PROCEDURE Update_Dim_Object
( p_commit IN VARCHAR2 := FND_API.G_TRUE
, p_dim_obj_short_name IN VARCHAR2
, p_display_name IN VARCHAR2
, p_application_id IN NUMBER
, p_description IN VARCHAR2
, p_data_source IN VARCHAR2
, p_source_table IN VARCHAR2
, p_where_clause IN VARCHAR2 := NULL
, p_comparison_label_code IN VARCHAR2
, p_table_column IN VARCHAR2
, p_source_type IN VARCHAR2
, p_maximum_code_size IN NUMBER
, p_maximum_name_size IN NUMBER
, p_all_item_text IN VARCHAR2
, p_comparison_item_text IN VARCHAR2
, p_prototype_default_value IN VARCHAR2
, p_dimension_values_order IN NUMBER
, p_comparison_order IN NUMBER
, p_time_stamp IN VARCHAR2 := NULL -- Granular Locking
, p_Master_Level IN VARCHAR2 := NULL
, p_Long_Lov IN VARCHAR2 := FND_API.G_FALSE
, p_Search_Function IN VARCHAR2 := NULL
, p_Dim_Obj_Enabled IN VARCHAR2 := FND_API.G_FALSE
, p_View_Object_Name IN VARCHAR2 := NULL
, p_Default_Values_Api IN VARCHAR2 := NULL
, p_All_Enabled IN NUMBER := NULL
, p_Drill_To_Form_Function IN VARCHAR2 := NULL
, p_Hide IN VARCHAR2 := FND_API.G_FALSE
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
) IS
BEGIN
FND_MSG_PUB.Initialize;
BSC_BIS_DIM_OBJ_PUB.Update_Dim_Object
( p_commit => FND_API.G_FALSE
, p_dim_obj_short_name => p_dim_obj_short_name
, p_display_name => p_display_name
, p_application_id => p_application_id
, p_description => p_description
, p_data_source => p_data_source
, p_source_table => p_source_table
, p_where_clause => p_where_clause
, p_comparison_label_code => p_comparison_label_code
, p_table_column => p_table_column
, p_source_type => p_source_type
, p_maximum_code_size => p_maximum_code_size
, p_maximum_name_size => p_maximum_name_size
, p_all_item_text => p_all_item_text
, p_comparison_item_text => p_comparison_item_text
, p_prototype_default_value => p_prototype_default_value
, p_dimension_values_order => p_dimension_values_order
, p_comparison_order => p_comparison_order
, p_assign_dim_short_names => NULL
, p_unassign_dim_short_names => NULL
, p_time_stamp => p_time_stamp -- Granular Locking
, p_Master_Level => p_Master_Level
, p_Long_Lov => p_Long_Lov
, p_Search_Function => p_Search_Function
, p_Dim_Obj_Enabled => p_Dim_Obj_Enabled
, p_View_Object_Name => p_View_Object_Name
, p_Default_Values_Api => p_Default_Values_Api
, p_Drill_To_Form_Function => p_Drill_To_Form_Function
, p_All_Enabled => p_All_Enabled
, p_Hide => p_Hide
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
x_msg_data := x_msg_data||' -> BSC_BIS_DIM_OBJ_PUB.Update_Dim_Object ';
x_msg_data := SQLERRM||' at BSC_BIS_DIM_OBJ_PUB.Update_Dim_Object ';
x_msg_data := x_msg_data||' -> BSC_BIS_DIM_OBJ_PUB.Update_Dim_Object ';
x_msg_data := SQLERRM||' at BSC_BIS_DIM_OBJ_PUB.Update_Dim_Object ';
END Update_Dim_Object;
UPDATE DIMENSION
*********************************************************************************/
PROCEDURE Update_Dim_Object
( p_commit IN VARCHAR2 := FND_API.G_TRUE
, p_dim_obj_short_name IN VARCHAR2
, p_display_name IN VARCHAR2
, p_application_id IN NUMBER
, p_description IN VARCHAR2
, p_data_source IN VARCHAR2
, p_source_table IN VARCHAR2
, p_where_clause IN VARCHAR2 := NULL
, p_comparison_label_code IN VARCHAR2
, p_table_column IN VARCHAR2
, p_source_type IN VARCHAR2
, p_maximum_code_size IN NUMBER
, p_maximum_name_size IN NUMBER
, p_all_item_text IN VARCHAR2
, p_comparison_item_text IN VARCHAR2
, p_prototype_default_value IN VARCHAR2
, p_dimension_values_order IN NUMBER
, p_comparison_order IN NUMBER
, p_assign_dim_short_names IN VARCHAR2
, p_time_stamp IN VARCHAR2 := NULL -- Granular Locking
, p_Master_Level IN VARCHAR2 := NULL
, p_Long_Lov IN VARCHAR2 := FND_API.G_FALSE
, p_Search_Function IN VARCHAR2 := NULL
, p_Dim_Obj_Enabled IN VARCHAR2 := FND_API.G_FALSE
, p_View_Object_Name IN VARCHAR2 := NULL
, p_Default_Values_Api IN VARCHAR2 := NULL
, p_All_Enabled IN NUMBER := NULL
, p_Drill_To_Form_Function IN VARCHAR2 := NULL
, p_Restrict_Dim_Validate IN VARCHAR2 := NULL
, p_Hide IN VARCHAR2 := FND_API.G_FALSE
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
) IS
l_dim_level_id BSC_SYS_DIM_LEVELS_BY_GROUP.dim_level_id%TYPE;
SELECT dim_level_id
FROM BSC_SYS_DIM_LEVELS_B
WHERE short_name = p_dim_obj_short_name;
SELECT short_name
FROM BSC_SYS_DIM_GROUPS_VL
WHERE dim_group_id IN (SELECT dim_group_id
FROM BSC_SYS_DIM_LEVELS_BY_GROUP
WHERE dim_level_id = l_dim_level_id);
FND_MESSAGE.SET_NAME('BSC','BSC_MUSER_DELETE_MESSAGE');
BSC_BIS_DIM_OBJ_PUB.Update_Dim_Object
( p_commit => FND_API.G_FALSE
, p_dim_obj_short_name => p_dim_obj_short_name
, p_display_name => p_display_name
, p_application_id => p_application_id
, p_description => p_description
, p_data_source => p_data_source
, p_source_table => p_source_table
, p_where_clause => p_where_clause
, p_comparison_label_code => p_comparison_label_code
, p_table_column => p_table_column
, p_source_type => p_source_type
, p_maximum_code_size => p_maximum_code_size
, p_maximum_name_size => p_maximum_name_size
, p_all_item_text => p_all_item_text
, p_comparison_item_text => p_comparison_item_text
, p_prototype_default_value => p_prototype_default_value
, p_dimension_values_order => p_dimension_values_order
, p_comparison_order => p_comparison_order
, p_assign_dim_short_names => p_assign_dim_short_names
, p_unassign_dim_short_names => l_unas_dim_short_names
, p_time_stamp => p_time_stamp -- Granular Locking
, p_Master_Level => p_Master_Level
, p_Long_Lov => p_Long_Lov
, p_Search_Function => p_Search_Function
, p_Dim_Obj_Enabled => p_Dim_Obj_Enabled
, p_View_Object_Name => p_View_Object_Name
, p_Default_Values_Api => p_Default_Values_Api
, p_All_Enabled => p_All_Enabled
, p_Drill_To_Form_Function => p_Drill_To_Form_Function
, p_Restrict_Dim_Validate => p_Restrict_Dim_Validate
, p_Hide => p_Hide
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
x_msg_data := x_msg_data||' -> BSC_BIS_DIM_OBJ_PUB.Update_Dim_Object ';
x_msg_data := SQLERRM||' at BSC_BIS_DIM_OBJ_PUB.Update_Dim_Object ';
x_msg_data := x_msg_data||' -> BSC_BIS_DIM_OBJ_PUB.Update_Dim_Object ';
x_msg_data := SQLERRM||' at BSC_BIS_DIM_OBJ_PUB.Update_Dim_Object ';
END Update_Dim_Object;
DELETE DIMENSION
*********************************************************************************/
/*
This procedure allows user to delete a dimension. Respective records will be
deleted from the following metadata: -
1 BIS_LEVELS.
2. BIS_LEVELS_TL
3. BSC_SYS_DIM_LEVELS_B
4. BSC_SYS_DIM_LEVELS_TL
Validations:
1. dimension must not be associated with any group.
*/
PROCEDURE Delete_Dim_Object
( p_commit IN VARCHAR2 := FND_API.G_TRUE
, p_dim_obj_short_name IN VARCHAR2
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
) IS
l_bsc_dimension_rec BSC_DIMENSION_GROUPS_PUB.Bsc_Dim_Group_Rec_Type;
l_is_denorm_deleted VARCHAR(1);
l_delete_count NUMBER := 0;
SELECT dim_level_id
, source
, name
FROM BSC_SYS_DIM_LEVELS_VL
WHERE short_name = p_dim_obj_short_name;
SAVEPOINT DeleteBSCDimObjectPMD;
SELECT COUNT(1) INTO l_count FROM BIS_LEVELS
WHERE short_name = p_dim_obj_short_name;
SELECT COUNT(1) INTO l_Count
FROM BIS_LEVELS
WHERE Master_Level = l_bis_dim_level_rec.Dimension_Level_Short_Name;
FND_MESSAGE.SET_NAME('BIS','BIS_MASTER_DELETE_DIM_OBJ');
BIS_DIMENSION_LEVEL_PUB.Delete_Dimension_Level
( p_commit => FND_API.G_FALSE
, p_validation_level => FND_API.G_VALID_LEVEL_FULL
, p_Dimension_Level_Rec => l_bis_dim_level_rec
, x_return_status => x_return_status
, x_error_Tbl => l_error_tbl
);
l_delete_count := l_delete_count + 1;
BSC_BIS_DIM_OBJ_PUB.validateBscDimensionToDelete
( p_dim_obj_short_name => p_dim_obj_short_name
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
BSC_DIMENSION_GROUPS_PUB.Delete_Dim_Levels_In_Group
( p_commit => FND_API.G_FALSE
, p_Dim_Grp_Rec => l_bsc_dimension_rec
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
BSC_DIMENSION_LEVELS_PUB.Delete_Dim_Level
( p_commit => FND_API.G_FALSE
, p_Dim_Level_Rec => l_bsc_dim_obj_rec
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
l_delete_count := l_delete_count + 1;
l_is_denorm_deleted := FND_API.G_TRUE;
EXECUTE IMMEDIATE l_Sql USING IN p_dim_obj_short_name,IN BIS_UTIL.G_DROP_TABLE,OUT x_msg_data,OUT l_is_denorm_deleted;
IF(l_is_denorm_deleted=FND_API.G_FALSE) THEN
RAISE FND_API.G_EXC_ERROR;
IF (l_delete_count = 0) THEN
FND_MESSAGE.SET_NAME('BSC','BSC_MUSER_DELETE_MESSAGE');
ROLLBACK TO DeleteBSCDimObjectPMD;
ROLLBACK TO DeleteBSCDimObjectPMD;
ROLLBACK TO DeleteBSCDimObjectPMD;
x_msg_data := x_msg_data||' -> BSC_BIS_DIM_OBJ_PUB.Delete_Dim_Object ';
x_msg_data := SQLERRM||' at BSC_BIS_DIM_OBJ_PUB.Delete_Dim_Object ';
ROLLBACK TO DeleteBSCDimObjectPMD;
x_msg_data := x_msg_data||' -> BSC_BIS_DIM_OBJ_PUB.Delete_Dim_Object ';
x_msg_data := SQLERRM||' at BSC_BIS_DIM_OBJ_PUB.Delete_Dim_Object ';
END Delete_Dim_Object;
FUNCTION validateBscDimensionToDelete
*********************************************************************************/
PROCEDURE validateBscDimensionToDelete
( p_commit IN VARCHAR2 := FND_API.G_TRUE
, p_dim_obj_short_name IN VARCHAR2
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
) IS
l_bsc_dim_id BSC_SYS_DIM_LEVELS_B.dim_level_id%TYPE;
SELECT dim_level_id
, name
INTO l_bsc_dim_id
, l_name
FROM BSC_SYS_DIM_LEVELS_VL
WHERE short_name = p_dim_obj_short_name;
/*SELECT Edw_Flag INTO l_edw_flag
FROM BSC_SYS_DIM_LEVELS_B
WHERE dim_level_id = l_bsc_dim_id;
x_msg_data := 'The dimension object can''t be deleted, since BSC_SYS_DIM_LEVELS_B.Edw_Flag <> 1';
SELECT COUNT(1) INTO l_count
FROM BSC_SYS_DIM_LEVELS_BY_GROUP
WHERE Dim_Level_Id = l_bsc_dim_id
AND Dim_Group_Id <> l_dim_Group_id;
FND_MESSAGE.SET_NAME('BSC','BSC_NOT_DELETE_DIM_OBJ_GRPS');
SELECT COUNT(1) INTO l_count
FROM BSC_SYS_DIM_LEVEL_RELS
WHERE dim_level_id = l_bsc_dim_id
OR parent_dim_level_id = l_bsc_dim_id;
FND_MESSAGE.SET_NAME('BSC','BSC_NOT_DELETE_DIM_OBJ_RELS');
x_msg_data := x_msg_data||' -> BSC_BIS_DIM_OBJ_PUB.validateBscDimensionToDelete ';
x_msg_data := SQLERRM||' at BSC_BIS_DIM_OBJ_PUB.validateBscDimensionToDelete ';
x_msg_data := x_msg_data||' -> BSC_BIS_DIM_OBJ_PUB.validateBscDimensionToDelete ';
x_msg_data := SQLERRM||' at BSC_BIS_DIM_OBJ_PUB.validateBscDimensionToDelete ';
END validateBscDimensionToDelete;
SELECT V.NAME
FROM BSC_SYS_DIM_LEVELS_VL V,
BSC_SYS_DIM_LEVELS_BY_GROUP B
WHERE V.DIM_LEVEL_ID = B.DIM_LEVEL_ID
AND B.DIM_GROUP_ID = p_group_id
ORDER BY B.DIM_LEVEL_INDEX;
SELECT A.name
FROM BSC_SYS_DIM_LEVELS_VL A
, BSC_SYS_DIM_LEVELS_BY_GROUP B
WHERE A.dim_level_id = B.dim_level_id
AND B.dim_group_id = (SELECT dim_group_id
FROM BSC_SYS_DIM_GROUPS_VL
WHERE Short_Name = p_dim_short_name);
SELECT COUNT(1) INTO l_count
FROM BSC_SYS_DIM_LEVELS_B
WHERE DIM_LEVEL_ID <> p_Dim_Level_Rec.Bsc_Level_Id
AND LEVEL_TABLE_NAME = l_Bsc_Level_Name;
SELECT Column_Name
FROM ALL_TAB_COLUMNS
WHERE Table_Name = UPPER(p_Dim_Level_Rec.Bsc_Level_Name)
AND Column_Name LIKE l_prefix
AND Owner = BSC_APPS.get_user_schema('APPS');
SELECT Column_Name
FROM ALL_TAB_COLUMNS
WHERE Table_Name = UPPER(p_Dim_Level_Rec.Bsc_Level_Name)
AND (Column_Name LIKE '%DESCRIPTION%'
OR Column_Name LIKE '%NAME%'
OR Column_Name LIKE '%PK')
AND ROWNUM < 2
AND Owner = BSC_APPS.get_user_schema('APPS');
SELECT COUNT(1) INTO l_count
FROM BSC_SYS_DIM_LEVELS_B
WHERE DIM_LEVEL_ID <> p_Dim_Level_Rec.Bsc_Level_Id
AND UPPER(LEVEL_TABLE_NAME) = p_Dim_Level_Rec.Bsc_Level_View_Name;
SELECT property_value
FROM bsc_sys_init
WHERE property_code = 'TEMP_END_KPI';
to force the system to updated the pmf view.
********* Please READ: ********** */
l_END_TO_END_KPI_FLAG := 'N';
l_Rel_Select1 VARCHAR2(1000);
l_Rel_Select2 VARCHAR2(1000);
SELECT
b.relation_col AS rel_col,
(SELECT level_pk_col
FROM bsc_sys_dim_levels_b
WHERE dim_level_id =b.parent_dim_level_id) AS pk_col
FROM
bsc_sys_dim_level_rels_v b
WHERE
b.dim_level_id= p_Dim_Level_Rec.Bsc_Level_Id;
l_Rel_Select1 := l_Rel_Select1 || ',' || par_rec.pk_col || ',' || par_rec.pk_col || '_USR';
l_Rel_Select2 := l_Rel_Select2 || ','
|| 'DECODE( ' || 'TO_CHAR(' || par_rec.rel_col || ') ' || ', '
|| 'TO_CHAR(' || BSC_BIS_DIM_OBJ_PUB.DIM_OBJ_VIEW_ZCODE || ') ' || ', '
|| 'TO_CHAR(' || BSC_BIS_DIM_OBJ_PUB.DIM_OBJ_VIEW_ZCODE_ALIAS || ') ' || ', '
|| 'TO_CHAR(' || par_rec.rel_col || ')) '
|| ', ' ||' TO_CHAR('|| par_rec.rel_col || ') ';
' (CODE, USER_CODE, NAME' || l_Rel_Select1 || ') ' ||
' AS SELECT DISTINCT ' ||
' DECODE( ' || 'TO_CHAR(' || p_Dim_Level_Rec.Bsc_Level_Pk_Key || ') ' || ', '
|| 'TO_CHAR(' || BSC_BIS_DIM_OBJ_PUB.DIM_OBJ_VIEW_ZCODE || ') ' || ', '
|| 'TO_CHAR(' || BSC_BIS_DIM_OBJ_PUB.DIM_OBJ_VIEW_ZCODE_ALIAS || ') ' || ', '
|| 'TO_CHAR(' || p_Dim_Level_Rec.Bsc_Level_Pk_Key || ')) ' || ', ' ||
'TO_CHAR(' || p_Dim_Level_Rec.Bsc_Level_Pk_Key || ') ' || ', ' ||
p_Dim_Level_Rec.Bsc_Level_Name_Column || l_Rel_Select2 ||
' FROM ' || p_Dim_Level_Rec.Bsc_Level_Name;
' AS SELECT ROWNUM, ROWNUM, A.' || p_Dim_Level_Rec.Bsc_Level_Name_Column ||
' FROM (SELECT DISTINCT ' ||
p_Dim_Level_Rec.Bsc_Level_Name_Column ||
' FROM ' || p_Dim_Level_Rec.Bsc_Level_Name ||
' WHERE UPPER(' || p_Dim_Level_Rec.Bsc_Level_Name_Column || ') NOT LIKE ''UNASSIGNED%''' ||
' AND UPPER(' || p_Dim_Level_Rec.Bsc_Level_Name_Column || ') NOT LIKE ''INVALID%''' ||
' AND ROWNUM < 26) A';
UPDATE BSC_SYS_DIM_LEVELS_B
SET Table_Type = 1
WHERE Short_Name = p_Dim_Level_Rec.Bsc_Level_Short_Name;
UPDATE BSC_SYS_DIM_LEVELS_B
SET Table_Type = -1
WHERE Short_Name = p_Dim_Level_Rec.Bsc_Level_Short_Name;
UPDATE BSC_SYS_DIM_LEVELS_B
SET Table_Type = -1
WHERE Short_Name = p_Dim_Level_Rec.Bsc_Level_Short_Name;
SELECT column_name
FROM ALL_TAB_COLUMNS
WHERE TABLE_NAME = l_master_table
AND column_name NOT IN ('LANGUAGE', 'SOURCE_LANG')
AND column_name IS NOT NULL
AND OWNER = BSC_APPS.get_user_schema;
SELECT COUNT(1) INTO l_count
FROM BSC_SYS_DIM_LEVELS_B
WHERE DIM_LEVEL_ID <> p_Dim_Level_Rec.Bsc_Level_Id
AND LEVEL_TABLE_NAME = l_master_table
AND SOURCE <> 'PMF';
l_sql_stmt := ' SELECT COUNT(1) FROM USER_OBJECTS '||
' WHERE OBJECT_NAME = :1';
l_sql_stmt := ' SELECT COUNT(1) FROM USER_OBJECTS '||
' WHERE OBJECT_NAME = :1';
' AS SELECT '||l_col_names||' FROM '||l_master_table||' WHERE 1 = 2';
' SELECT '||l_col_names||
' FROM '||l_master_table||
' WHERE LANGUAGE = USERENV(''LANG''))';
l_sql_stmt := ' INSERT INTO '||l_master_table||
' (CODE, USER_CODE, NAME, LANGUAGE, SOURCE_LANG) '||
' SELECT '||i_project||' AS CODE, '||
' '''||TO_CHAR(i_project)||''' AS USER_CODE, '||
' '''||l_code_name||''' AS NAME, L.LANGUAGE_CODE, '||
' USERENV(''LANG'') '||
' FROM FND_LANGUAGES L '||
' WHERE L.INSTALLED_FLAG IN (''I'', ''B'') '||
' AND NOT EXISTS '||
' ( SELECT NULL FROM '||l_master_table||
' T WHERE T.CODE = :1 '||
' AND T.LANGUAGE = L.LANGUAGE_CODE) ';
INSERT INTO BSC_DB_TABLES_RELS
(Table_Name, Source_Table_Name, Relation_Type)
VALUES (l_master_table, l_input_table, 0);
INSERT INTO BSC_DB_TABLES
(Table_Name, Table_Type, Periodicity_Id,
Source_Data_Type, Source_File_Name)
VALUES (l_input_table, 2, 0, 0, NULL);
SELECT column_name
FROM ALL_TAB_COLUMNS
WHERE TABLE_NAME = l_master_table
AND column_name IS NOT NULL
AND column_name NOT IN ('LANGUAGE', 'SOURCE_LANG')
AND OWNER = BSC_APPS.get_user_schema;
SELECT parent_dim_level_id
FROM bsc_sys_dim_level_rels
WHERE dim_level_id = p_Dim_Level_Rec.Bsc_Level_Id
AND relation_type = 2;
SELECT DISTINCT TAB.Table_Name AS TABLE_NAME
, NVL(COL.Source_Column, COL.Column_Name) AS COLUMN_NAME
FROM BSC_DB_TABLES_COLS COL
, BSC_DB_TABLES TAB
WHERE TAB.Table_Name = COL.Table_Name
AND TAB.Table_Type = 0
AND (UPPER(COL.Column_Name)) = UPPER(p_Dim_Level_Rec.Bsc_Pk_Col);
SELECT B.Level_Pk_Col
FROM BSC_SYS_DIM_LEVEL_RELS A
, BSC_SYS_DIM_LEVELS_B B
WHERE A.Dim_Level_Id = p_Dim_Level_Rec.Bsc_Level_Id
AND B.Dim_Level_Id = A.Parent_Dim_Level_Id
AND A.Relation_Type = 1;
SELECT COUNT(1) INTO l_count
FROM BSC_SYS_DIM_LEVELS_B
WHERE DIM_LEVEL_ID <> p_Dim_Level_Rec.Bsc_Level_Id
AND LEVEL_TABLE_NAME = l_master_table;
l_sql_stmt := ' SELECT COUNT(1) FROM USER_OBJECTS '||
' WHERE OBJECT_NAME = :1';
l_sql_stmt := ' SELECT COUNT(1) FROM USER_OBJECTS '||
' WHERE OBJECT_NAME = :1';
l_sql_stmt := ' SELECT COUNT(1) FROM USER_OBJECTS '||
' WHERE OBJECT_NAME = :1';
UPDATE BSC_DB_TABLES_COLS
SET Source_Column = p_Dim_Level_Rec_Old.Bsc_Pk_Col
WHERE UPPER(Column_Name) = UPPER(p_Dim_Level_Rec_Old.Bsc_Pk_Col)
AND Source_Column IS NULL;
UPDATE BSC_DB_TABLES_COLS
SET Column_Name = p_Dim_Level_Rec.Bsc_Pk_Col
WHERE UPPER(Column_Name) = UPPER(p_Dim_Level_Rec_Old.Bsc_Pk_Col);
l_sql_stmt := ' SELECT COUNT(1) FROM USER_OBJECTS '||
' WHERE OBJECT_NAME = :1';
' AS SELECT * FROM '||p_Dim_Level_Rec_Old.Bsc_Level_Name;
UPDATE BSC_DB_TABLES_RELS
SET Table_Name = l_master_table
WHERE Source_Table_Name = l_input_table;
' SELECT '||l_col_names||
' FROM '||l_master_table||
' WHERE LANGUAGE = USERENV(''LANG''))';
SELECT Dim_Level_Id
FROM BSC_SYS_DIM_LEVEL_RELS
WHERE Parent_Dim_Level_Id = p_Dim_Level_Rec.Bsc_Level_Id
AND Relation_Type = 2;
SELECT Abbreviation
INTO l_abbreviation
FROM BSC_SYS_DIM_LEVELS_B
WHERE dim_level_id = cd.Dim_Level_Id;
l_sql_stmt := ' SELECT COUNT(1) FROM USER_OBJECTS '||
' WHERE OBJECT_NAME = :1';
l_sql_stmt := ' SELECT COUNT(1) FROM USER_OBJECTS '||
' WHERE OBJECT_NAME = :1';
l_sql_stmt := ' SELECT COUNT(1) FROM USER_OBJECTS '||
' WHERE OBJECT_NAME = :1';
' AS SELECT * FROM '||l_old_mas_name;
Update_M_By_N_Relation_col
( p_Dim_Level_Id => cd.Dim_Level_Id
, p_Parent_Dim_Level_Id => p_Dim_Level_Rec.Bsc_Level_Id
, p_Relation_Col => l_master_table
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
UPDATE BSC_DB_TABLES_RELS
SET Table_Name = l_master_table
WHERE Source_Table_Name = l_input_table;
l_Tbl_Statements.DELETE;
l_sql_stmt := 'UPDATE '||l_master_table||' SET '||
p_Dim_Level_Rec.Bsc_Pk_Col||' = '||p_Dim_Level_Rec_Old.Bsc_Pk_Col;
l_Tbl_Statements.DELETE;
l_sql_stmt := 'UPDATE '||l_input_table||' SET '||
p_Dim_Level_Rec.Bsc_Pk_Col||'_USR = '||p_Dim_Level_Rec_Old.Bsc_Pk_Col||'_USR';
SELECT column_name
FROM ALL_TAB_COLUMNS
WHERE TABLE_NAME = l_master_table
AND column_name NOT IN ('LANGUAGE', 'SOURCE_LANG')
AND column_name IS NOT NULL
AND OWNER = BSC_APPS.get_user_schema;
SELECT Index_Name
FROM ALL_IND_COLUMNS
WHERE Table_Name = l_master_table
AND Column_Name = UPPER(p_Dim_Level_Rec_Old.Bsc_Pk_Col)
AND INDEX_OWNER = BSC_APPS.get_user_schema;
SELECT Dim_Level_Id
FROM BSC_SYS_DIM_LEVEL_RELS
WHERE parent_dim_level_id = p_Dim_Level_Rec.Bsc_Level_Id
AND relation_type = 1;
SELECT Level_Table_Name
INTO l_master_table
FROM BSC_SYS_DIM_LEVELS_B
WHERE dim_level_id = cd.dim_level_id;
l_sql_stmt := ' SELECT COUNT(1) FROM USER_OBJECTS '||
' WHERE OBJECT_NAME = :1';
l_sql_stmt := ' SELECT COUNT(1) FROM USER_OBJECTS '||
' WHERE OBJECT_NAME = :1';
SELECT COUNT(1) INTO l_count
FROM ALL_INDEXES
WHERE INDEX_NAME = l_index_Name
AND OWNER = BSC_APPS.get_user_schema;
l_Tbl_Statements.DELETE;
l_sql_stmt := 'UPDATE '||l_master_table||' SET '||
p_Dim_Level_Rec.Bsc_Pk_Col ||' = '||p_Dim_Level_Rec_Old.Bsc_Pk_Col;
l_Tbl_Statements.DELETE;
l_sql_stmt := 'UPDATE '||l_master_table||' SET '||
p_Dim_Level_Rec.Bsc_Pk_Col||'_USR = '||p_Dim_Level_Rec_Old.Bsc_Pk_Col||'_USR';
l_Tbl_Statements.DELETE;
l_sql_stmt := 'UPDATE '||l_input_table||' SET '||
p_Dim_Level_Rec.Bsc_Pk_Col||'_USR = '||p_Dim_Level_Rec_Old.Bsc_Pk_Col||'_USR';
Here we need to update the relation_col column in BSC_SYS_DIM_LEVEL_RELS
table whenever we are changing the level_pk_col of the parent.
/*******************************************************/
UPDATE BSC_SYS_DIM_LEVEL_RELS
SET RELATION_COL = p_Dim_Level_Rec.Bsc_Pk_Col
WHERE dim_level_id = cd.Dim_Level_Id
AND parent_dim_level_id = p_Dim_Level_Rec.Bsc_Level_Id
AND relation_type = 1;
' SELECT '||l_col_names||
' FROM '||l_master_table||
' WHERE LANGUAGE = USERENV(''LANG''))';
l_key_words := l_key_words||'DAY, DECIMAL, DECLARE, DEFAULT, DELETE, DESC, DISTINCT, DO, DROP, ELSE, ELSIF, END, ';
l_key_words := l_key_words||'IMMEDIATE, IN, INCREMENT, INDEX, INDICATOR, INITIAL, INSERT, INTEGER, INTERFACE, ';
l_key_words := l_key_words||'SAVEPOINT, SECOND, SELECT, SEPARATE, SESSION, SET, SHARE, SIZE, SMALLINT, SPACE, SQL, ';
l_key_words := l_key_words||'TRIGGER, TRUE, TYPE, UI, UID, UNION, UNIQUE, UPDATE, USER, VALIDATE, VALUES, VARCHAR, ';
SELECT dimension_id
FROM BIS_DIMENSIONS
WHERE short_name = p_dim_short_name;
SELECT dim_level_id
, level_table_name
, user_key_size
, disp_key_size
, NVL(source, 'BSC')
, abbreviation
, level_pk_col
FROM BSC_SYS_DIM_LEVELS_VL
WHERE short_name = p_dim_obj_short_name;
SELECT Short_Name
FROM BSC_SYS_DIM_GROUPS_VL
WHERE dim_group_id IN
(SELECT dim_group_id
FROM BSC_SYS_DIM_LEVELS_BY_GROUP
WHERE dim_level_id = l_bsc_dim_obj_rec.Bsc_Level_Id);
SELECT DISTINCT C.Name||'['||C.Indicator||']' Name
, C.Indicator
FROM BSC_KPI_DIM_LEVELS_B A
, BSC_SYS_DIM_LEVELS_B D
, BSC_KPIS_VL C
WHERE A.Level_Table_Name = D.Level_Table_Name
AND D.Dim_Level_Id = l_bsc_dim_obj_rec.Bsc_Level_Id
AND C.share_flag <> 2
AND C.Indicator = A.Indicator;
SELECT DISTINCT C.Name||'['||C.Indicator||']' Name
,C.Indicator
FROM BSC_KPI_DIM_LEVELS_B A
, BSC_SYS_DIM_LEVELS_B D
, BSC_KPIS_VL C
WHERE A.Level_Table_Name = D.Level_Table_Name
AND C.share_flag <> 2
AND C.Indicator = A.Indicator
AND INSTR(','||l_child_dim_obj_list||',', ','||D.dim_level_id||',') > 0;
SELECT DISTINCT B.Name||'['||B.Indicator||']' Name
,B.Indicator
FROM BSC_KPI_DIM_GROUPS A
, BSC_KPIS_VL B
, BSC_SYS_DIM_GROUPS_VL C
WHERE A.INDICATOR = B.INDICATOR
AND A.dim_group_id = C.Dim_Group_ID
AND B.share_flag <> 2
AND INSTR(l_new_dim_Grps, ','||C.Short_Name||',') > 0;
SELECT COUNT(0)
INTO l_count
FROM BSC_SYS_DIM_LEVELS_BY_GROUP A
, BSC_SYS_DIM_GROUPS_VL B
WHERE A.Dim_Level_Id = l_bsc_dim_obj_rec.Bsc_Level_Id
AND B.Short_Name = l_dim_Grp_name
AND B.Dim_Group_Id = A.Dim_Group_Id;
SELECT A. INDICATOR
,A.DIM_SET_ID
FROM BSC_KPI_DIM_GROUPS A
,BSC_SYS_DIM_GROUPS_VL B
WHERE A.DIM_GROUP_ID = B.DIM_GROUP_ID
AND B.SHORT_NAME = l_dim_short_name;
SELECT B.SHORT_NAME
FROM BSC_SYS_DIM_LEVELS_B B
,BSC_KPI_DIM_LEVEL_PROPERTIES KDL
WHERE B.DIM_LEVEL_ID = KDL.DIM_LEVEL_ID
AND KDL.indicator = l_kpi_id
AND KDL.dim_set_id = l_dim_set_id;
kpi_dim_set_array.delete;
dim_objs_array.delete;
SELECT Source
FROM BSC_SYS_DIM_LEVELS_B
WHERE Dim_Level_Id = p_dim_obj_id;
SELECT Source
FROM BSC_SYS_DIM_LEVELS_B
WHERE Short_Name = p_short_Name;
SELECT Level_ID
FROM BIS_LEVELS
WHERE Short_Name = p_Short_Name;
SELECT Dim_Level_ID
FROM BSC_SYS_DIM_LEVELS_B
WHERE Short_Name = p_Short_Name;
SELECT A.Short_Name
, A.Source PMF_Source
, A.Level_Values_View_Name
, A.Hide_In_Design
, B.Level_Table_Name
, B.Dim_Level_Id
FROM BIS_LEVELS A
, BSC_SYS_DIM_LEVELS_B B
WHERE A.Short_Name = B.Short_Name
AND B.Source = 'PMF'
AND A.Source = 'OLTP';
SELECT A.Short_Name
, A.Source PMF_Source
, A.Level_Values_View_Name
, A.Hide_In_Design
, B.Level_Table_Name
, B.Dim_Level_Id
FROM BIS_LEVELS A
, BSC_SYS_DIM_LEVELS_B B
WHERE A.Short_Name = B.Short_Name
AND B.Source = 'PMF'
AND A.Source = 'OLTP'
AND B.Short_Name = p_Short_Name;
SELECT A.Short_Name
, B.Level_Table_Name
FROM BIS_LEVELS A
, BSC_SYS_DIM_LEVELS_B B
WHERE A.Short_Name = B.Short_Name
AND B.Source = 'PMF'
AND A.Source = 'EDW'
AND B.TABLE_TYPE = 1; -- Identify EDW with existing Views
UPDATE BSC_SYS_DIM_LEVELS_B
SET Table_Type = -1
WHERE Short_Name = PMF_EDW.Short_Name;
SELECT TABLE_TYPE
INTO l_Return
FROM BSC_SYS_DIM_LEVELS_B
WHERE SHORT_NAME = p_Short_Name;
SELECT LEV.INDICATOR,LEV.dim_set_id,COUNT(LEV.dim_level_index)
FROM BSC_KPI_DIM_LEVELS_B LEV,
BSC_KPI_DIM_LEVEL_PROPERTIES prop
WHERE lev.INDICATOR = prop.INDICATOR
AND lev.dim_Set_id = prop.dim_set_id
AND prop.dim_level_id = p_dim_id
GROUP BY lev.INDICATOR,lev.dim_set_id;
SELECT NAME INTO l_kpi_name
FROM BSC_KPIS_VL
WHERE INDICATOR = l_kpi_id;
x_msg_data := x_msg_data||' -> BSC_BIS_DIM_OBJ_PUB.Update_Dim_Object ';
x_msg_data := SQLERRM||' at BSC_BIS_DIM_OBJ_PUB.Update_Dim_Object ';
SELECT R.SHORT_NAME CHILD_SHORT_NAME
, R.RELATION_COL PARENT_COL
, L.LEVEL_VALUES_VIEW_NAME VIEW_NAME
FROM BSC_SYS_DIM_LEVEL_RELS_V R
, BIS_LEVELS L
WHERE R.SHORT_NAME = L.SHORT_NAME
AND L.SHORT_NAME = p_Dim_Obj_Short_Name
AND R.SOURCE = 'PMF';
SELECT L.LEVEL_VALUES_VIEW_NAME
INTO l_View_Name
FROM BIS_LEVELS L
WHERE L.SHORT_NAME = p_Dim_Obj_Short_Name;
SELECT table_type
FROM bsc_sys_dim_levels_b
WHERE short_name = p_Dim_Obj_Short_Name;
l_Select_Clause VARCHAR2(4000);
l_Select_Clause := C_SELECT_CLAUSE || ', ' || p_Parent_Column || ' ';
l_Select_Clause := C_SELECT_PARENT_CLAUSE;
l_Select_Clause := C_SELECT_CLAUSE;
l_Sql := C_SELECT || l_Select_Clause
|| C_FROM || p_View_Name
|| C_WHERE || C_WHERE_CLAUSE ;
l_Sql := 'SELECT COUNT(1) FROM ('|| l_Sql || ')' ;
SELECT column_name
FROM user_tab_columns
WHERE table_name = p_view_name;
SELECT status
FROM user_objects
WHERE object_name = p_view_name
AND object_type = 'VIEW';
SELECT DISTINCT SOURCE,DIM_NAME,DIM_ID,SHORT_NAME
FROM BSC_BIS_DIM_VL
WHERE SOURCE IS NOT NULL
AND INSTR(','||p_dim_short_names ||',',','||short_name||',') > 0;
SELECT SYS.SOURCE,SYS.SHORT_NAME,SYS.NAME
FROM BSC_SYS_DIM_LEVELS_VL SYS,
BSC_SYS_DIM_LEVELS_BY_GROUP GRP
WHERE SYS.dim_level_id = GRP.dim_level_id
AND GRP.dim_group_id = l_dim_grp_id;
SELECT COUNT(1) into l_count
FROM BSC_SYS_DIM_LEVELS_BY_GROUP GRP
WHERE GRP.dim_group_id = l_dim_grp_id;
SELECT COUNT(1) into l_exist
FROM BSC_SYS_DIM_LEVELS_VL SYS,
BSC_SYS_DIM_LEVELS_BY_GROUP GRP
WHERE SYS.dim_level_id = GRP.dim_level_id
AND GRP.dim_group_id = l_dim_grp_id
AND SYS.SHORT_NAME = p_dim_obj_short_name;
SELECT dim_level_id
, level_table_name
, user_key_size
, disp_key_size
, NVL(source, 'BSC')
, abbreviation
, level_pk_col
FROM BSC_SYS_DIM_LEVELS_VL
WHERE short_name = p_dim_obj_short_name;
SELECT DISTINCT C.Name||'['||C.Indicator||']' Name
,C.Indicator
FROM BSC_KPI_DIM_LEVELS_B A
, BSC_SYS_DIM_LEVELS_B D
, BSC_KPIS_VL C
WHERE A.Level_Table_Name = D.Level_Table_Name
AND C.share_flag <> 2
AND C.Indicator = A.Indicator
AND INSTR(','||l_child_dim_obj_list||',', ','||D.dim_level_id||',') > 0;
SELECT DISTINCT C.Name||'['||C.Indicator||']' Name
, C.Indicator
FROM BSC_KPI_DIM_LEVELS_B A
, BSC_SYS_DIM_LEVELS_B D
, BSC_KPIS_VL C
WHERE A.Level_Table_Name = D.Level_Table_Name
AND D.Dim_Level_Id = l_bsc_dim_obj_rec.Bsc_Level_Id
AND C.share_flag <> 2
AND C.Indicator = A.Indicator;
SELECT R.RELATION_COL,
R.DATA_SOURCE,
R.DATA_SOURCE_TYPE
FROM BSC_SYS_DIM_LEVEL_RELS_V R
WHERE R.DIM_LEVEL_ID = R.PARENT_DIM_LEVEL_ID
AND R.SHORT_NAME = p_Short_Name
AND R.PARENT_SHORT_NAME = p_Short_Name
AND R.SOURCE = BSC_UTILITY.c_PMF
AND R.PARENT_SOURCE = BSC_UTILITY.c_PMF;
SELECT D.LEVEL_PK_COL
FROM BSC_SYS_DIM_LEVELS_B D
WHERE D.SHORT_NAME = p_Dim_Level_Rec.Bsc_Level_Short_Name;
SELECT COUNT(1) INTO l_count
FROM BSC_SYS_DIM_LEVELS_B
WHERE SHORT_NAME <> p_Dim_Level_Rec.Bsc_Level_Short_Name
AND UPPER(LEVEL_PK_COL) = UPPER(l_temp_var);
SELECT bsc.name, bis.LEVEL_VALUES_VIEW_NAME, bsc.source
INTO l_name, l_Dim_Obj_View_Name, l_source
FROM bsc_sys_dim_levels_vl bsc, bis_levels_vl bis
WHERE bsc.short_name = bis.short_name
AND bsc.short_name = p_Dim_Obj_Short_Name;
SELECT
bis_dim.short_name
FROM
ak_regions reg,
ak_region_items reg_item,
bis_dimensions bis_dim
WHERE
reg_item.attribute2 = l_Dim_DimObj_Sht_Name
AND reg.region_code = reg_item.region_code
AND bis_dim.short_name = reg.attribute12
AND bis_util.is_seeded(bis_dim.created_by,'T','F') = 'F'
AND NVL(hide_in_design,'F') = 'T';
UPDATE
bsc_sys_dim_levels_by_group
SET
total_flag = p_All_Flag
WHERE
dim_level_id = BSC_BIS_DIM_OBJ_PUB.Get_Bsc_Dim_Obj_ID(p_Dim_Obj_Short_Name)
AND dim_group_id = BSC_BIS_DIMENSION_PUB.Get_Bsc_Dimension_ID(l_Dim_Short_Name);