eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.BSC_BIS_DIM_OBJ_PUB dependencies on BSC_SYS_DIM_LEVELS_B

Line 407: l_Bsc_Dim_Obj_ID BSC_SYS_DIM_LEVELS_B.Dim_Level_ID%TYPE;

403: , x_return_status OUT NOCOPY VARCHAR2
404: , x_msg_count OUT NOCOPY NUMBER
405: , x_msg_data OUT NOCOPY VARCHAR2
406: ) IS
407: l_Bsc_Dim_Obj_ID BSC_SYS_DIM_LEVELS_B.Dim_Level_ID%TYPE;
408: l_Bsc_Group_ID BSC_SYS_DIM_GROUPS_TL.Dim_Group_ID%TYPE;
409: l_Dim_Short_Name BIS_DIMENSIONS.Short_Name%TYPE;
410:
411: CURSOR c_Bis_Levels IS

Line 431: UPDATE BSC_SYS_DIM_LEVELS_BY_GROUP

427: CLOSE c_Bis_Levels;
428: l_Bsc_Group_ID := BSC_BIS_DIMENSION_PUB.Get_Bsc_Dimension_ID(l_Dim_Short_Name);
429: l_Bsc_Dim_Obj_ID := BSC_BIS_DIM_OBJ_PUB.Get_Bsc_Dim_Obj_ID(p_Dim_Obj_Short_Name);
430:
431: UPDATE BSC_SYS_DIM_LEVELS_BY_GROUP
432: SET Total_Flag = p_All_Enabled
433: WHERE Dim_Level_Id = l_Bsc_Dim_Obj_ID
434: AND Dim_Group_Id = l_Bsc_Group_ID;
435: END IF;

Line 471: ,bsc_sys_dim_levels_b S

467: ,K.dim_set_id
468: ,K.dim_level_index
469: ,K.level_table_name
470: FROM bsc_kpi_dim_levels_vl K
471: ,bsc_sys_dim_levels_b S
472: WHERE S.level_Table_name = K.level_table_name
473: AND S.dim_level_id IN ( p_Dim_Level_Id,p_Parent_Dim_Level_Id)
474: AND K.parent_level_rel IS NOT NULL
475: AND K.table_relation IS NOT NULL;

Line 531: UPDATE bsc_sys_dim_levels_by_group

527: SAVEPOINT SyncBSCAllInPMD;
528:
529: IF ((p_All_Enabled IS NOT NULL) AND (p_All_Enabled = 0)) THEN
530: IF (p_Dim_Short_Name IS NOT NULL) THEN -- Update Mode
531: UPDATE bsc_sys_dim_levels_by_group
532: SET total_flag = p_All_Enabled
533: WHERE dim_level_id = BSC_BIS_DIM_OBJ_PUB.Get_Bsc_Dim_Obj_ID(p_Dim_Obj_Short_Name)
534: AND dim_group_id = BSC_BIS_DIMENSION_PUB.Get_Bsc_Dimension_ID(p_Dim_Short_Name);
535: ELSE -- Create Mode

Line 536: UPDATE bsc_sys_dim_levels_by_group

532: SET total_flag = p_All_Enabled
533: WHERE dim_level_id = BSC_BIS_DIM_OBJ_PUB.Get_Bsc_Dim_Obj_ID(p_Dim_Obj_Short_Name)
534: AND dim_group_id = BSC_BIS_DIMENSION_PUB.Get_Bsc_Dimension_ID(p_Dim_Short_Name);
535: ELSE -- Create Mode
536: UPDATE bsc_sys_dim_levels_by_group
537: SET total_flag = p_All_Enabled
538: WHERE dim_level_id = BSC_BIS_DIM_OBJ_PUB.Get_Bsc_Dim_Obj_ID(p_Dim_Obj_Short_Name);
539: END IF;
540: END IF;

Line 832: 3. BSC_SYS_DIM_LEVELS_B

828: This procedure allow user to create a new dimension category whose records
829: will be inserted into the following tables.
830: 1 BIS_LEVELS.
831: 2. BIS_LEVELS_TL
832: 3. BSC_SYS_DIM_LEVELS_B
833: 4. BSC_SYS_DIM_LEVELS_TL
834:
835: Key
836: p_dim_obj_short_name

Line 887: l_temp_var BSC_SYS_DIM_LEVELS_B.Short_Name%TYPE;

883: l_bis_name BIS_LEVELS_TL.Name%TYPE;
884: l_user_id FND_USER.user_id%TYPE;
885: l_login_id NUMBER;
886: l_count NUMBER;
887: l_temp_var BSC_SYS_DIM_LEVELS_B.Short_Name%TYPE;
888: l_alias VARCHAR2(4);
889:
890: l_dim_short_names VARCHAR2(32000);
891: l_flag BOOLEAN := FALSE;

Line 892: l_dim_obj_name BSC_SYS_DIM_LEVELS_B.Short_Name%TYPE;

888: l_alias VARCHAR2(4);
889:
890: l_dim_short_names VARCHAR2(32000);
891: l_flag BOOLEAN := FALSE;
892: l_dim_obj_name BSC_SYS_DIM_LEVELS_B.Short_Name%TYPE;
893: l_application_id BIS_LEVELS.Application_Id%TYPE;
894: -- Start Granular Locking added by Aditya
895: l_Dim_Tab BSC_BIS_LOCKS_PUB.t_numberTable;
896: l_dim_Grp_names VARCHAR2(32000);

Line 929: FROM BSC_SYS_DIM_LEVELS_B;

925: l_application_id := p_application_id;
926: ELSE
927: SELECT NVL(MAX(dim_level_id) + 1, 0)
928: INTO l_count
929: FROM BSC_SYS_DIM_LEVELS_B;
930: IF (p_dim_obj_short_name IS NULL) THEN
931: l_dim_obj_name := c_BSC_DIM_OBJ||l_count;
932: ELSE
933: l_dim_obj_name := p_dim_obj_short_name;

Line 1109: --BSC_SYS_DIM_LEVELS_B.dim_level_id

1105: ELSE
1106: l_bis_dim_level_rec.Dimension_Short_Name := BSC_BIS_DIMENSION_PUB.Unassigned_Dim;
1107: END IF;
1108: -- End Granular Locking
1109: --BSC_SYS_DIM_LEVELS_B.dim_level_id
1110: l_bsc_dim_obj_rec.Bsc_Level_Id := BSC_DIMENSION_LEVELS_PVT.Get_Next_Value('BSC_SYS_DIM_LEVELS_B', 'DIM_LEVEL_ID');
1111: l_bsc_dim_obj_rec.Bsc_Source := p_data_source;--BSC_SYS_DIM_LEVELS_B.source
1112: l_bsc_dim_obj_rec.Bsc_Level_User_Key_Size := p_maximum_code_size;--BSC_SYS_DIM_LEVELS_B.user_key_size
1113: l_bsc_dim_obj_rec.Bsc_Level_Disp_Key_Size := p_maximum_name_size;--BSC_SYS_DIM_LEVELS_B.disp_key_size

Line 1110: l_bsc_dim_obj_rec.Bsc_Level_Id := BSC_DIMENSION_LEVELS_PVT.Get_Next_Value('BSC_SYS_DIM_LEVELS_B', 'DIM_LEVEL_ID');

1106: l_bis_dim_level_rec.Dimension_Short_Name := BSC_BIS_DIMENSION_PUB.Unassigned_Dim;
1107: END IF;
1108: -- End Granular Locking
1109: --BSC_SYS_DIM_LEVELS_B.dim_level_id
1110: l_bsc_dim_obj_rec.Bsc_Level_Id := BSC_DIMENSION_LEVELS_PVT.Get_Next_Value('BSC_SYS_DIM_LEVELS_B', 'DIM_LEVEL_ID');
1111: l_bsc_dim_obj_rec.Bsc_Source := p_data_source;--BSC_SYS_DIM_LEVELS_B.source
1112: l_bsc_dim_obj_rec.Bsc_Level_User_Key_Size := p_maximum_code_size;--BSC_SYS_DIM_LEVELS_B.user_key_size
1113: l_bsc_dim_obj_rec.Bsc_Level_Disp_Key_Size := p_maximum_name_size;--BSC_SYS_DIM_LEVELS_B.disp_key_size
1114: IF (l_bsc_dim_obj_rec.Bsc_Level_User_Key_Size IS NULL) THEN

Line 1111: l_bsc_dim_obj_rec.Bsc_Source := p_data_source;--BSC_SYS_DIM_LEVELS_B.source

1107: END IF;
1108: -- End Granular Locking
1109: --BSC_SYS_DIM_LEVELS_B.dim_level_id
1110: l_bsc_dim_obj_rec.Bsc_Level_Id := BSC_DIMENSION_LEVELS_PVT.Get_Next_Value('BSC_SYS_DIM_LEVELS_B', 'DIM_LEVEL_ID');
1111: l_bsc_dim_obj_rec.Bsc_Source := p_data_source;--BSC_SYS_DIM_LEVELS_B.source
1112: l_bsc_dim_obj_rec.Bsc_Level_User_Key_Size := p_maximum_code_size;--BSC_SYS_DIM_LEVELS_B.user_key_size
1113: l_bsc_dim_obj_rec.Bsc_Level_Disp_Key_Size := p_maximum_name_size;--BSC_SYS_DIM_LEVELS_B.disp_key_size
1114: IF (l_bsc_dim_obj_rec.Bsc_Level_User_Key_Size IS NULL) THEN
1115: l_bsc_dim_obj_rec.Bsc_Level_User_Key_Size := BSC_BIS_DIM_OBJ_PUB.Dim_Obj_Code_Default_Size;

Line 1112: l_bsc_dim_obj_rec.Bsc_Level_User_Key_Size := p_maximum_code_size;--BSC_SYS_DIM_LEVELS_B.user_key_size

1108: -- End Granular Locking
1109: --BSC_SYS_DIM_LEVELS_B.dim_level_id
1110: l_bsc_dim_obj_rec.Bsc_Level_Id := BSC_DIMENSION_LEVELS_PVT.Get_Next_Value('BSC_SYS_DIM_LEVELS_B', 'DIM_LEVEL_ID');
1111: l_bsc_dim_obj_rec.Bsc_Source := p_data_source;--BSC_SYS_DIM_LEVELS_B.source
1112: l_bsc_dim_obj_rec.Bsc_Level_User_Key_Size := p_maximum_code_size;--BSC_SYS_DIM_LEVELS_B.user_key_size
1113: l_bsc_dim_obj_rec.Bsc_Level_Disp_Key_Size := p_maximum_name_size;--BSC_SYS_DIM_LEVELS_B.disp_key_size
1114: IF (l_bsc_dim_obj_rec.Bsc_Level_User_Key_Size IS NULL) THEN
1115: l_bsc_dim_obj_rec.Bsc_Level_User_Key_Size := BSC_BIS_DIM_OBJ_PUB.Dim_Obj_Code_Default_Size;
1116: END IF;

Line 1113: l_bsc_dim_obj_rec.Bsc_Level_Disp_Key_Size := p_maximum_name_size;--BSC_SYS_DIM_LEVELS_B.disp_key_size

1109: --BSC_SYS_DIM_LEVELS_B.dim_level_id
1110: l_bsc_dim_obj_rec.Bsc_Level_Id := BSC_DIMENSION_LEVELS_PVT.Get_Next_Value('BSC_SYS_DIM_LEVELS_B', 'DIM_LEVEL_ID');
1111: l_bsc_dim_obj_rec.Bsc_Source := p_data_source;--BSC_SYS_DIM_LEVELS_B.source
1112: l_bsc_dim_obj_rec.Bsc_Level_User_Key_Size := p_maximum_code_size;--BSC_SYS_DIM_LEVELS_B.user_key_size
1113: l_bsc_dim_obj_rec.Bsc_Level_Disp_Key_Size := p_maximum_name_size;--BSC_SYS_DIM_LEVELS_B.disp_key_size
1114: IF (l_bsc_dim_obj_rec.Bsc_Level_User_Key_Size IS NULL) THEN
1115: l_bsc_dim_obj_rec.Bsc_Level_User_Key_Size := BSC_BIS_DIM_OBJ_PUB.Dim_Obj_Code_Default_Size;
1116: END IF;
1117: IF (l_bsc_dim_obj_rec.Bsc_Level_Disp_Key_Size IS NULL) THEN

Line 1141: FROM BSC_SYS_DIM_LEVELS_B

1137: END IF;
1138:
1139: IF (l_bsc_dim_obj_rec.Bsc_Source = 'BSC') THEN
1140: SELECT COUNT(1) INTO l_count
1141: FROM BSC_SYS_DIM_LEVELS_B
1142: WHERE UPPER(abbreviation) = UPPER(l_bsc_dim_obj_rec.Bsc_Level_Abbreviation);
1143: IF (l_count <> 0) THEN
1144: l_flag := TRUE;
1145: l_alias := NULL;

Line 1149: FROM BSC_SYS_DIM_LEVELS_B

1145: l_alias := NULL;
1146: l_temp_var := SUBSTR(l_bsc_dim_obj_rec.Bsc_Level_Abbreviation, 1, 7);
1147: WHILE (l_flag) LOOP
1148: SELECT COUNT(1) INTO l_count
1149: FROM BSC_SYS_DIM_LEVELS_B
1150: WHERE UPPER(abbreviation) = UPPER(l_temp_var);
1151: IF (l_count = 0) THEN
1152: l_flag := FALSE;
1153: l_bsc_dim_obj_rec.Bsc_Level_Abbreviation := l_temp_var;

Line 1174: l_bsc_dim_obj_rec.Bsc_Level_Short_Name := l_dim_obj_name;--BSC_SYS_DIM_LEVELS_B.short_name

1170: END IF;
1171: END IF;
1172:
1173:
1174: l_bsc_dim_obj_rec.Bsc_Level_Short_Name := l_dim_obj_name;--BSC_SYS_DIM_LEVELS_B.short_name
1175: l_bsc_dim_obj_rec.Bsc_Pk_Col := l_table_column;
1176: l_bsc_dim_obj_rec.Bsc_Level_Name := l_source_table;
1177: IF (l_bsc_dim_obj_rec.Bsc_Source = 'BSC') THEN
1178: IF (l_bsc_dim_obj_rec.Bsc_Level_Name IS NULL) THEN

Line 1198: FROM BSC_SYS_DIM_LEVELS_B

1194: l_alias := NULL;
1195: l_temp_var := l_bsc_dim_obj_rec.Bsc_Level_Name;
1196: WHILE (l_flag) LOOP
1197: SELECT COUNT(1) INTO l_count
1198: FROM BSC_SYS_DIM_LEVELS_B
1199: WHERE UPPER(Level_Table_Name) = UPPER(l_temp_var);
1200: IF (l_count = 0) THEN
1201: l_flag := FALSE;
1202: l_bsc_dim_obj_rec.Bsc_Level_Name := l_temp_var;

Line 1355: l_bsc_dim_obj_rec.Bsc_Level_Comp_Order_By := p_comparison_order;--BSC_SYS_DIM_LEVELS_B.comp_order_by

1351: l_bsc_dim_obj_rec.Bsc_Dim_Tot_Disp_Name := p_all_item_text;--BSC_SYS_DIM_LEVELS_TL.total_disp_name
1352: l_bsc_dim_obj_rec.Bsc_Language := NULL;--BSC_SYS_DIM_LEVELS_TL.language
1353: l_bsc_dim_obj_rec.Bsc_Level_Column_Name := NULL;
1354: l_bsc_dim_obj_rec.Bsc_Level_Column_Type := NULL;
1355: l_bsc_dim_obj_rec.Bsc_Level_Comp_Order_By := p_comparison_order;--BSC_SYS_DIM_LEVELS_B.comp_order_by
1356: l_bsc_dim_obj_rec.Bsc_Level_Custom_Group := 0;--BSC_SYS_DIM_LEVELS_B.custom_group
1357: l_bsc_dim_obj_rec.Bsc_Level_Index := 0;
1358: l_bsc_dim_obj_rec.Bsc_Level_Table_Type := 1;--BSC_SYS_DIM_LEVELS_B.table_type
1359: l_bsc_dim_obj_rec.Bsc_Level_Value_Order_By := p_dimension_values_order;--BSC_SYS_DIM_LEVELS_B.value_order_by

Line 1356: l_bsc_dim_obj_rec.Bsc_Level_Custom_Group := 0;--BSC_SYS_DIM_LEVELS_B.custom_group

1352: l_bsc_dim_obj_rec.Bsc_Language := NULL;--BSC_SYS_DIM_LEVELS_TL.language
1353: l_bsc_dim_obj_rec.Bsc_Level_Column_Name := NULL;
1354: l_bsc_dim_obj_rec.Bsc_Level_Column_Type := NULL;
1355: l_bsc_dim_obj_rec.Bsc_Level_Comp_Order_By := p_comparison_order;--BSC_SYS_DIM_LEVELS_B.comp_order_by
1356: l_bsc_dim_obj_rec.Bsc_Level_Custom_Group := 0;--BSC_SYS_DIM_LEVELS_B.custom_group
1357: l_bsc_dim_obj_rec.Bsc_Level_Index := 0;
1358: l_bsc_dim_obj_rec.Bsc_Level_Table_Type := 1;--BSC_SYS_DIM_LEVELS_B.table_type
1359: l_bsc_dim_obj_rec.Bsc_Level_Value_Order_By := p_dimension_values_order;--BSC_SYS_DIM_LEVELS_B.value_order_by
1360: l_bsc_dim_obj_rec.Bsc_Source_Language := NULL;--source_lang

Line 1358: l_bsc_dim_obj_rec.Bsc_Level_Table_Type := 1;--BSC_SYS_DIM_LEVELS_B.table_type

1354: l_bsc_dim_obj_rec.Bsc_Level_Column_Type := NULL;
1355: l_bsc_dim_obj_rec.Bsc_Level_Comp_Order_By := p_comparison_order;--BSC_SYS_DIM_LEVELS_B.comp_order_by
1356: l_bsc_dim_obj_rec.Bsc_Level_Custom_Group := 0;--BSC_SYS_DIM_LEVELS_B.custom_group
1357: l_bsc_dim_obj_rec.Bsc_Level_Index := 0;
1358: l_bsc_dim_obj_rec.Bsc_Level_Table_Type := 1;--BSC_SYS_DIM_LEVELS_B.table_type
1359: l_bsc_dim_obj_rec.Bsc_Level_Value_Order_By := p_dimension_values_order;--BSC_SYS_DIM_LEVELS_B.value_order_by
1360: l_bsc_dim_obj_rec.Bsc_Source_Language := NULL;--source_lang
1361: l_bsc_dim_obj_rec.Bsc_Source_Level_Long_Name := NULL;
1362: l_bsc_dim_obj_rec.Bsc_Relation_Column := NULL;

Line 1359: l_bsc_dim_obj_rec.Bsc_Level_Value_Order_By := p_dimension_values_order;--BSC_SYS_DIM_LEVELS_B.value_order_by

1355: l_bsc_dim_obj_rec.Bsc_Level_Comp_Order_By := p_comparison_order;--BSC_SYS_DIM_LEVELS_B.comp_order_by
1356: l_bsc_dim_obj_rec.Bsc_Level_Custom_Group := 0;--BSC_SYS_DIM_LEVELS_B.custom_group
1357: l_bsc_dim_obj_rec.Bsc_Level_Index := 0;
1358: l_bsc_dim_obj_rec.Bsc_Level_Table_Type := 1;--BSC_SYS_DIM_LEVELS_B.table_type
1359: l_bsc_dim_obj_rec.Bsc_Level_Value_Order_By := p_dimension_values_order;--BSC_SYS_DIM_LEVELS_B.value_order_by
1360: l_bsc_dim_obj_rec.Bsc_Source_Language := NULL;--source_lang
1361: l_bsc_dim_obj_rec.Bsc_Source_Level_Long_Name := NULL;
1362: l_bsc_dim_obj_rec.Bsc_Relation_Column := NULL;
1363: l_bsc_dim_obj_rec.Bsc_Relation_Type := NULL;

Line 1587: FROM BSC_SYS_DIM_LEVELS_BY_GROUP A

1583: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1584: END IF;
1585: -- Granular Locking - Set the timestamp of Dimension Group
1586: SELECT COUNT(1) INTO l_count
1587: FROM BSC_SYS_DIM_LEVELS_BY_GROUP A
1588: , BSC_SYS_DIM_LEVELS_B B
1589: WHERE A.Dim_Group_Id = l_dim_grp_id
1590: AND A.Dim_Level_Id = B.Dim_Level_Id
1591: AND B.Short_Name = p_dim_obj_short_name;

Line 1588: , BSC_SYS_DIM_LEVELS_B B

1584: END IF;
1585: -- Granular Locking - Set the timestamp of Dimension Group
1586: SELECT COUNT(1) INTO l_count
1587: FROM BSC_SYS_DIM_LEVELS_BY_GROUP A
1588: , BSC_SYS_DIM_LEVELS_B B
1589: WHERE A.Dim_Group_Id = l_dim_grp_id
1590: AND A.Dim_Level_Id = B.Dim_Level_Id
1591: AND B.Short_Name = p_dim_obj_short_name;
1592: IF (l_count = 0) THEN

Line 1761: FROM BSC_SYS_DIM_LEVELS_BY_GROUP A

1757: , p_dim_name => l_assign)
1758: ) LOOP
1759: SELECT COUNT(1)
1760: INTO l_count
1761: FROM BSC_SYS_DIM_LEVELS_BY_GROUP A
1762: ,BSC_SYS_DIM_LEVELS_B L
1763: ,BSC_SYS_DIM_GROUPS_TL D
1764: WHERE D.SHORT_NAME = l_assign
1765: AND L.SHORT_NAME = p_dim_obj_short_name

Line 1762: ,BSC_SYS_DIM_LEVELS_B L

1758: ) LOOP
1759: SELECT COUNT(1)
1760: INTO l_count
1761: FROM BSC_SYS_DIM_LEVELS_BY_GROUP A
1762: ,BSC_SYS_DIM_LEVELS_B L
1763: ,BSC_SYS_DIM_GROUPS_TL D
1764: WHERE D.SHORT_NAME = l_assign
1765: AND L.SHORT_NAME = p_dim_obj_short_name
1766: AND L.DIM_LEVEL_ID = A.DIM_LEVEL_ID

Line 2002: 3. BSC_SYS_DIM_LEVELS_B

1998: This procedure allows user to update a dimension. These changes will
1999: be reflected into the following metadata: -
2000: 1 BIS_LEVELS.
2001: 2. BIS_LEVELS_TL
2002: 3. BSC_SYS_DIM_LEVELS_B
2003: 4. BSC_SYS_DIM_LEVELS_TL
2004:
2005: Key:
2006: 'p_dim_obj_short_name'

Line 2055: l_temp_var BSC_SYS_DIM_LEVELS_B.abbreviation%TYPE;

2051: l_bsc_dim_obj_rec BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type;
2052: l_bsc_drop_tables BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type;
2053: l_MTab_Tbl BSC_BIS_DIM_OBJ_PUB.KPI_Dim_Set_Table_Type;
2054:
2055: l_temp_var BSC_SYS_DIM_LEVELS_B.abbreviation%TYPE;
2056: l_source_table VARCHAR(33);
2057: l_bsc_dim_id BSC_SYS_DIM_LEVELS_B.Dim_Level_Id%TYPE;
2058: l_level_table_name BIS_LEVELS.LEVEL_VALUES_VIEW_NAME%TYPE;
2059: l_dim_obj_name BSC_SYS_DIM_LEVELS_B.Short_Name%TYPE;

Line 2057: l_bsc_dim_id BSC_SYS_DIM_LEVELS_B.Dim_Level_Id%TYPE;

2053: l_MTab_Tbl BSC_BIS_DIM_OBJ_PUB.KPI_Dim_Set_Table_Type;
2054:
2055: l_temp_var BSC_SYS_DIM_LEVELS_B.abbreviation%TYPE;
2056: l_source_table VARCHAR(33);
2057: l_bsc_dim_id BSC_SYS_DIM_LEVELS_B.Dim_Level_Id%TYPE;
2058: l_level_table_name BIS_LEVELS.LEVEL_VALUES_VIEW_NAME%TYPE;
2059: l_dim_obj_name BSC_SYS_DIM_LEVELS_B.Short_Name%TYPE;
2060: l_dim_Grp_name BSC_SYS_DIM_GROUPS_TL.short_name%TYPE;
2061: l_application_id BIS_LEVELS.Application_Id%TYPE;

Line 2059: l_dim_obj_name BSC_SYS_DIM_LEVELS_B.Short_Name%TYPE;

2055: l_temp_var BSC_SYS_DIM_LEVELS_B.abbreviation%TYPE;
2056: l_source_table VARCHAR(33);
2057: l_bsc_dim_id BSC_SYS_DIM_LEVELS_B.Dim_Level_Id%TYPE;
2058: l_level_table_name BIS_LEVELS.LEVEL_VALUES_VIEW_NAME%TYPE;
2059: l_dim_obj_name BSC_SYS_DIM_LEVELS_B.Short_Name%TYPE;
2060: l_dim_Grp_name BSC_SYS_DIM_GROUPS_TL.short_name%TYPE;
2061: l_application_id BIS_LEVELS.Application_Id%TYPE;
2062: l_bis_short_name BIS_LEVELS.Short_Name%TYPE;
2063: l_bis_name BIS_LEVELS_TL.Name%TYPE;

Line 2086: FROM BSC_SYS_DIM_LEVELS_BY_GROUP

2082: CURSOR c_dimension_names IS
2083: SELECT short_name
2084: FROM BSC_SYS_DIM_GROUPS_VL
2085: WHERE dim_group_id IN (SELECT dim_group_id
2086: FROM BSC_SYS_DIM_LEVELS_BY_GROUP
2087: WHERE dim_level_id = l_bsc_dim_obj_rec.Bsc_Level_Id);
2088:
2089: CURSOR c_kpi_dim_set IS
2090: SELECT DISTINCT A.indicator Indicator

Line 2093: , BSC_SYS_DIM_LEVELS_B D

2089: CURSOR c_kpi_dim_set IS
2090: SELECT DISTINCT A.indicator Indicator
2091: , A.dim_set_id Dim_Set_Id
2092: FROM BSC_KPI_DIM_LEVELS_B A
2093: , BSC_SYS_DIM_LEVELS_B D
2094: , BSC_KPIS_B B
2095: WHERE A.Level_Table_Name = D.Level_Table_Name
2096: AND B.Indicator = A.Indicator
2097: AND B.Share_Flag <> 2

Line 2185: FROM BSC_SYS_DIM_LEVELS_B

2181: RAISE FND_API.G_EXC_ERROR;
2182: END IF;
2183: --check if short_name exists in the BSC system
2184: SELECT COUNT(1) INTO l_count
2185: FROM BSC_SYS_DIM_LEVELS_B
2186: WHERE Short_Name = l_dim_obj_name;
2187: IF (l_count = 0) THEN
2188: FND_MESSAGE.SET_NAME('BSC','BSC_MUSER_DELETE_MESSAGE');
2189: FND_MESSAGE.SET_TOKEN('TYPE', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DIMENSION_OBJECT'), TRUE);

Line 2273: --BSC_SYS_DIM_LEVELS_B.dim_level_id

2269: l_bsc_drop_tables.Bsc_Level_Abbreviation := l_bsc_dim_obj_rec.Bsc_Level_Abbreviation;
2270: l_bsc_drop_tables.Bsc_Pk_Col := l_bsc_dim_obj_rec.Bsc_Pk_Col;
2271: l_bsc_drop_tables.Bsc_Level_View_Name := l_bsc_dim_obj_rec.Bsc_Level_View_Name;
2272:
2273: --BSC_SYS_DIM_LEVELS_B.dim_level_id
2274: -- Dimension Object type can not be changed (BSC/PMF).
2275: IF ((p_data_source IS NOT NULL) AND (l_bsc_dim_obj_rec.Bsc_Source <> p_data_source)) THEN
2276: FND_MESSAGE.SET_NAME('BSC','BSC_NO_UPDATE_DATA_SOURCE');
2277: FND_MSG_PUB.ADD;

Line 2328: l_bsc_dim_obj_rec.Bsc_Level_Short_Name := l_dim_obj_name;--BSC_SYS_DIM_LEVELS_B.short_name

2324: IF (p_prototype_default_value IS NOT NULL) THEN
2325: l_bsc_dim_obj_rec.Bsc_Level_Abbreviation := SUBSTR(p_prototype_default_value, 1, 11);
2326: END IF;
2327:
2328: l_bsc_dim_obj_rec.Bsc_Level_Short_Name := l_dim_obj_name;--BSC_SYS_DIM_LEVELS_B.short_name
2329:
2330: IF (c_dim_short_name%ISOPEN) THEN
2331: CLOSE c_dim_short_name;
2332: END IF;

Line 2384: l_bsc_dim_obj_rec.Bsc_Level_Short_Name := l_dim_obj_name;--BSC_SYS_DIM_LEVELS_B.short_name

2380: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2381: END IF;
2382: -- End Granular Locking
2383:
2384: l_bsc_dim_obj_rec.Bsc_Level_Short_Name := l_dim_obj_name;--BSC_SYS_DIM_LEVELS_B.short_name
2385: --generate unique abbreviation if source is BSC
2386: IF (l_bsc_dim_obj_rec.Bsc_Source = 'BSC') THEN
2387: IF ((l_bsc_dim_obj_rec.Bsc_Level_User_Key_Size < l_bsc_drop_tables.Bsc_Level_User_Key_Size)
2388: OR (l_bsc_dim_obj_rec.Bsc_Level_Disp_Key_Size < l_bsc_drop_tables.Bsc_Level_Disp_Key_Size)) THEN

Line 2402: FROM BSC_SYS_DIM_LEVELS_B

2398: FND_MSG_PUB.ADD;
2399: RAISE FND_API.G_EXC_ERROR;
2400: END IF;
2401: SELECT COUNT(1) INTO l_count
2402: FROM BSC_SYS_DIM_LEVELS_B
2403: WHERE UPPER(abbreviation) = UPPER(l_bsc_dim_obj_rec.Bsc_Level_Abbreviation)
2404: AND dim_level_id <> l_bsc_dim_obj_rec.Bsc_Level_Id
2405: AND Source = 'BSC';
2406: IF (l_count <> 0) THEN

Line 2571: FROM BSC_SYS_DIM_LEVELS_B

2567:
2568: --CALL BSC API
2569: --Get BSC Dimension ID into 'l_bsc_dim_id'
2570: SELECT dim_level_id INTO l_bsc_dim_id
2571: FROM BSC_SYS_DIM_LEVELS_B
2572: WHERE UPPER(short_name) = UPPER(l_dim_obj_name);
2573:
2574: l_bsc_dim_obj_rec.Bsc_Dim_Comp_Disp_Name := p_comparison_item_text;
2575: l_bsc_dim_obj_rec.Bsc_Dim_Level_Long_Name := p_display_name;

Line 3031: l_dim_level_id BSC_SYS_DIM_LEVELS_BY_GROUP.dim_level_id%TYPE;

3027: , x_return_status OUT NOCOPY VARCHAR2
3028: , x_msg_count OUT NOCOPY NUMBER
3029: , x_msg_data OUT NOCOPY VARCHAR2
3030: ) IS
3031: l_dim_level_id BSC_SYS_DIM_LEVELS_BY_GROUP.dim_level_id%TYPE;
3032: l_unas_dim_short_names VARCHAR2(32000);
3033:
3034:
3035: CURSOR cr_bsc_dim_obj IS

Line 3037: FROM BSC_SYS_DIM_LEVELS_B

3033:
3034:
3035: CURSOR cr_bsc_dim_obj IS
3036: SELECT dim_level_id
3037: FROM BSC_SYS_DIM_LEVELS_B
3038: WHERE short_name = p_dim_obj_short_name;
3039:
3040: CURSOR c_dimension_names IS
3041: SELECT short_name

Line 3044: FROM BSC_SYS_DIM_LEVELS_BY_GROUP

3040: CURSOR c_dimension_names IS
3041: SELECT short_name
3042: FROM BSC_SYS_DIM_GROUPS_VL
3043: WHERE dim_group_id IN (SELECT dim_group_id
3044: FROM BSC_SYS_DIM_LEVELS_BY_GROUP
3045: WHERE dim_level_id = l_dim_level_id);
3046:
3047: BEGIN
3048: FND_MSG_PUB.Initialize;

Line 3195: 3. BSC_SYS_DIM_LEVELS_B

3191: This procedure allows user to delete a dimension. Respective records will be
3192: deleted from the following metadata: -
3193: 1 BIS_LEVELS.
3194: 2. BIS_LEVELS_TL
3195: 3. BSC_SYS_DIM_LEVELS_B
3196: 4. BSC_SYS_DIM_LEVELS_TL
3197:
3198: Validations:
3199: 1. dimension must not be associated with any group.

Line 3446: l_bsc_dim_id BSC_SYS_DIM_LEVELS_B.dim_level_id%TYPE;

3442: , x_return_status OUT NOCOPY VARCHAR2
3443: , x_msg_count OUT NOCOPY NUMBER
3444: , x_msg_data OUT NOCOPY VARCHAR2
3445: ) IS
3446: l_bsc_dim_id BSC_SYS_DIM_LEVELS_B.dim_level_id%TYPE;
3447: l_name BSC_SYS_DIM_LEVELS_TL.Name%TYPE;
3448: l_count NUMBER;
3449: l_edw_flag BSC_SYS_DIM_LEVELS_B.Edw_Flag%TYPE;
3450:

Line 3449: l_edw_flag BSC_SYS_DIM_LEVELS_B.Edw_Flag%TYPE;

3445: ) IS
3446: l_bsc_dim_id BSC_SYS_DIM_LEVELS_B.dim_level_id%TYPE;
3447: l_name BSC_SYS_DIM_LEVELS_TL.Name%TYPE;
3448: l_count NUMBER;
3449: l_edw_flag BSC_SYS_DIM_LEVELS_B.Edw_Flag%TYPE;
3450:
3451: l_dim_Group_id BSC_SYS_DIM_GROUPS_TL.Dim_Group_Id%TYPE;
3452: BEGIN
3453: FND_MSG_PUB.Initialize;

Line 3474: FROM BSC_SYS_DIM_LEVELS_B

3470:
3471:
3472: -- CAN'T DELETE DIMENSION, validataion one.
3473: /*SELECT Edw_Flag INTO l_edw_flag
3474: FROM BSC_SYS_DIM_LEVELS_B
3475: WHERE dim_level_id = l_bsc_dim_id;
3476: IF (l_edw_flag <> 1) THEN
3477: --this message is hard coded because it needs more investigation if it is really required
3478: x_msg_data := 'The dimension object can''t be deleted, since BSC_SYS_DIM_LEVELS_B.Edw_Flag <> 1';

Line 3478: x_msg_data := 'The dimension object can''t be deleted, since BSC_SYS_DIM_LEVELS_B.Edw_Flag <> 1';

3474: FROM BSC_SYS_DIM_LEVELS_B
3475: WHERE dim_level_id = l_bsc_dim_id;
3476: IF (l_edw_flag <> 1) THEN
3477: --this message is hard coded because it needs more investigation if it is really required
3478: x_msg_data := 'The dimension object can''t be deleted, since BSC_SYS_DIM_LEVELS_B.Edw_Flag <> 1';
3479: RAISE FND_API.G_EXC_ERROR;
3480: END IF;*/
3481:
3482: --check if the dimension is associated with any group, if so raise an exception

Line 3484: FROM BSC_SYS_DIM_LEVELS_BY_GROUP

3480: END IF;*/
3481:
3482: --check if the dimension is associated with any group, if so raise an exception
3483: SELECT COUNT(1) INTO l_count
3484: FROM BSC_SYS_DIM_LEVELS_BY_GROUP
3485: WHERE Dim_Level_Id = l_bsc_dim_id
3486: AND Dim_Group_Id <> l_dim_Group_id;
3487: IF (l_count > 0) THEN
3488: FND_MESSAGE.SET_NAME('BSC','BSC_NOT_DELETE_DIM_OBJ_GRPS');

Line 3555: BSC_SYS_DIM_LEVELS_BY_GROUP B

3551:
3552: CURSOR c_dimesnion_names IS
3553: SELECT V.NAME
3554: FROM BSC_SYS_DIM_LEVELS_VL V,
3555: BSC_SYS_DIM_LEVELS_BY_GROUP B
3556: WHERE V.DIM_LEVEL_ID = B.DIM_LEVEL_ID
3557: AND B.DIM_GROUP_ID = p_group_id
3558: ORDER BY B.DIM_LEVEL_INDEX;
3559: BEGIN

Line 3589: , BSC_SYS_DIM_LEVELS_BY_GROUP B

3585:
3586: CURSOR c_dimension_obj_name IS
3587: SELECT A.name
3588: FROM BSC_SYS_DIM_LEVELS_VL A
3589: , BSC_SYS_DIM_LEVELS_BY_GROUP B
3590: WHERE A.dim_level_id = B.dim_level_id
3591: AND B.dim_group_id = (SELECT dim_group_id
3592: FROM BSC_SYS_DIM_GROUPS_VL
3593: WHERE Short_Name = p_dim_short_name);

Line 3707: FROM BSC_SYS_DIM_LEVELS_B

3703: FND_MSG_PUB.ADD;
3704: RAISE FND_API.G_EXC_ERROR;
3705: END IF;
3706: SELECT COUNT(1) INTO l_count
3707: FROM BSC_SYS_DIM_LEVELS_B
3708: WHERE DIM_LEVEL_ID <> p_Dim_Level_Rec.Bsc_Level_Id
3709: AND LEVEL_TABLE_NAME = l_Bsc_Level_Name;
3710: IF (l_count <> 0) THEN
3711: FND_MESSAGE.SET_NAME('BSC','BSC_D_TABLE_NAME_EXIST');

Line 3816: --BSC_SYS_DIM_LEVELS_B.Level_Pk_Col should be short_name to keep it unique

3812:
3813: --Default values if view does not exists
3814: p_Dim_Level_Rec.Bsc_Level_Name_Column := 'VALUE';
3815: p_Dim_Level_Rec.Bsc_Level_Pk_Key := 'ID';
3816: --BSC_SYS_DIM_LEVELS_B.Level_Pk_Col should be short_name to keep it unique
3817: --p_Dim_Level_Rec.Bsc_Pk_Col := p_Dim_Level_Rec.Bsc_Level_Short_Name;
3818:
3819: --Comented for Bug#4758995
3820: --p_Dim_Level_Rec.Bsc_Pk_Col := REPLACE(p_Dim_Level_Rec.Bsc_Level_Short_Name ,' ', '_');

Line 3923: FROM BSC_SYS_DIM_LEVELS_B

3919: RAISE FND_API.G_EXC_ERROR;
3920: END IF;
3921:
3922: SELECT COUNT(1) INTO l_count
3923: FROM BSC_SYS_DIM_LEVELS_B
3924: WHERE DIM_LEVEL_ID <> p_Dim_Level_Rec.Bsc_Level_Id
3925: AND UPPER(LEVEL_TABLE_NAME) = p_Dim_Level_Rec.Bsc_Level_View_Name;
3926: IF (l_count <> 0) THEN
3927: FND_MESSAGE.SET_NAME('BSC','BSC_D_TABLE_NAME_EXIST');

Line 4062: FROM bsc_sys_dim_levels_b

4058: CURSOR c_par_dim_ids IS
4059: SELECT
4060: b.relation_col AS rel_col,
4061: (SELECT level_pk_col
4062: FROM bsc_sys_dim_levels_b
4063: WHERE dim_level_id =b.parent_dim_level_id) AS pk_col
4064: FROM
4065: bsc_sys_dim_level_rels_v b
4066: WHERE

Line 4152: UPDATE BSC_SYS_DIM_LEVELS_B

4148: END IF;
4149:
4150: BEGIN
4151: --update Table_Type = 1, which is a indication of view exists
4152: UPDATE BSC_SYS_DIM_LEVELS_B
4153: SET Table_Type = 1
4154: WHERE Short_Name = p_Dim_Level_Rec.Bsc_Level_Short_Name;
4155:
4156: BSC_APPS.Do_Ddl_AT(l_sql, ad_ddl.create_view, p_Dim_Level_Rec.Bsc_Level_View_Name, BSC_APPS.fnd_apps_schema, BSC_APPS.bsc_apps_short_name);

Line 4163: UPDATE BSC_SYS_DIM_LEVELS_B

4159: --if exception, it means PMF View does not exists,
4160: --in this case update Table_Type = -1, which is a indication of view does not exists
4161: WHEN OTHERS THEN
4162: IF (NOT BSC_UTILITY.is_Table_View_Exists(p_Dim_Level_Rec.Bsc_Level_View_Name)) THEN
4163: UPDATE BSC_SYS_DIM_LEVELS_B
4164: SET Table_Type = -1
4165: WHERE Short_Name = p_Dim_Level_Rec.Bsc_Level_Short_Name;
4166: END IF;
4167: END;

Line 4170: UPDATE BSC_SYS_DIM_LEVELS_B

4166: END IF;
4167: END;
4168: ELSE
4169: --update Table_Type = -1, which is a indication of view does not exists
4170: UPDATE BSC_SYS_DIM_LEVELS_B
4171: SET Table_Type = -1
4172: WHERE Short_Name = p_Dim_Level_Rec.Bsc_Level_Short_Name;
4173: END IF;
4174:

Line 4231: l_master_table BSC_SYS_DIM_LEVELS_B.Level_Table_Name%TYPE;

4227: )
4228: RETURN BOOLEAN IS
4229: l_sql_stmt VARCHAR2(32000);
4230:
4231: l_master_table BSC_SYS_DIM_LEVELS_B.Level_Table_Name%TYPE;
4232: l_input_table BSC_SYS_DIM_LEVELS_B.Level_Table_Name%TYPE;
4233: l_view_name BSC_SYS_DIM_LEVELS_B.Level_View_Name%TYPE;
4234: l_col_names VARCHAR2(32000) := NULL;
4235: l_code_name VARCHAR2(25);

Line 4232: l_input_table BSC_SYS_DIM_LEVELS_B.Level_Table_Name%TYPE;

4228: RETURN BOOLEAN IS
4229: l_sql_stmt VARCHAR2(32000);
4230:
4231: l_master_table BSC_SYS_DIM_LEVELS_B.Level_Table_Name%TYPE;
4232: l_input_table BSC_SYS_DIM_LEVELS_B.Level_Table_Name%TYPE;
4233: l_view_name BSC_SYS_DIM_LEVELS_B.Level_View_Name%TYPE;
4234: l_col_names VARCHAR2(32000) := NULL;
4235: l_code_name VARCHAR2(25);
4236: l_count NUMBER := 0;

Line 4233: l_view_name BSC_SYS_DIM_LEVELS_B.Level_View_Name%TYPE;

4229: l_sql_stmt VARCHAR2(32000);
4230:
4231: l_master_table BSC_SYS_DIM_LEVELS_B.Level_Table_Name%TYPE;
4232: l_input_table BSC_SYS_DIM_LEVELS_B.Level_Table_Name%TYPE;
4233: l_view_name BSC_SYS_DIM_LEVELS_B.Level_View_Name%TYPE;
4234: l_col_names VARCHAR2(32000) := NULL;
4235: l_code_name VARCHAR2(25);
4236: l_count NUMBER := 0;
4237:

Line 4307: FROM BSC_SYS_DIM_LEVELS_B

4303: p_Dim_Level_Rec.Bsc_Level_Name := l_master_table;
4304: p_Dim_Level_Rec.Bsc_Level_View_Name := l_view_name;
4305:
4306: SELECT COUNT(1) INTO l_count
4307: FROM BSC_SYS_DIM_LEVELS_B
4308: WHERE DIM_LEVEL_ID <> p_Dim_Level_Rec.Bsc_Level_Id
4309: AND LEVEL_TABLE_NAME = l_master_table
4310: AND SOURCE <> 'PMF';
4311:

Line 4523: l_master_table BSC_SYS_DIM_LEVELS_B.Level_Table_Name%TYPE;

4519: , x_msg_data OUT NOCOPY VARCHAR2
4520: )
4521: RETURN BOOLEAN IS
4522: l_sql_stmt VARCHAR2(32000);
4523: l_master_table BSC_SYS_DIM_LEVELS_B.Level_Table_Name%TYPE;
4524: l_input_table BSC_SYS_DIM_LEVELS_B.Level_Table_Name%TYPE;
4525: l_view_name BSC_SYS_DIM_LEVELS_B.Level_View_Name%TYPE;
4526: l_col_names VARCHAR2(32000) := NULL;
4527: l_old_mas_name VARCHAR2(60);

Line 4524: l_input_table BSC_SYS_DIM_LEVELS_B.Level_Table_Name%TYPE;

4520: )
4521: RETURN BOOLEAN IS
4522: l_sql_stmt VARCHAR2(32000);
4523: l_master_table BSC_SYS_DIM_LEVELS_B.Level_Table_Name%TYPE;
4524: l_input_table BSC_SYS_DIM_LEVELS_B.Level_Table_Name%TYPE;
4525: l_view_name BSC_SYS_DIM_LEVELS_B.Level_View_Name%TYPE;
4526: l_col_names VARCHAR2(32000) := NULL;
4527: l_old_mas_name VARCHAR2(60);
4528: l_abbr VARCHAR2(30);

Line 4525: l_view_name BSC_SYS_DIM_LEVELS_B.Level_View_Name%TYPE;

4521: RETURN BOOLEAN IS
4522: l_sql_stmt VARCHAR2(32000);
4523: l_master_table BSC_SYS_DIM_LEVELS_B.Level_Table_Name%TYPE;
4524: l_input_table BSC_SYS_DIM_LEVELS_B.Level_Table_Name%TYPE;
4525: l_view_name BSC_SYS_DIM_LEVELS_B.Level_View_Name%TYPE;
4526: l_col_names VARCHAR2(32000) := NULL;
4527: l_old_mas_name VARCHAR2(60);
4528: l_abbr VARCHAR2(30);
4529: l_count NUMBER := 0;

Line 4563: , BSC_SYS_DIM_LEVELS_B B

4559:
4560: CURSOR c_One_To_N_Index IS
4561: SELECT B.Level_Pk_Col
4562: FROM BSC_SYS_DIM_LEVEL_RELS A
4563: , BSC_SYS_DIM_LEVELS_B B
4564: WHERE A.Dim_Level_Id = p_Dim_Level_Rec.Bsc_Level_Id
4565: AND B.Dim_Level_Id = A.Parent_Dim_Level_Id
4566: AND A.Relation_Type = 1;
4567:

Line 4611: FROM BSC_SYS_DIM_LEVELS_B

4607: l_input_table := 'BSC_DI_'||p_Dim_Level_Rec.Bsc_Level_Id;
4608: l_view_name := 'BSC_D_'||p_Dim_Level_Rec.Bsc_Level_Id||'_VL';
4609:
4610: SELECT COUNT(1) INTO l_count
4611: FROM BSC_SYS_DIM_LEVELS_B
4612: WHERE DIM_LEVEL_ID <> p_Dim_Level_Rec.Bsc_Level_Id
4613: AND LEVEL_TABLE_NAME = l_master_table;
4614: IF (l_count <> 0) THEN
4615: FND_MESSAGE.SET_NAME('BSC','BSC_D_TABLE_NAME_EXIST');

Line 4853: l_abbreviation BSC_SYS_DIM_LEVELS_B.Abbreviation%TYPE;

4849: , x_msg_data OUT NOCOPY VARCHAR2
4850: )
4851: RETURN BOOLEAN IS
4852: l_count NUMBER;
4853: l_abbreviation BSC_SYS_DIM_LEVELS_B.Abbreviation%TYPE;
4854: l_sql_stmt VARCHAR2(32000);
4855: l_old_mas_name BSC_SYS_DIM_LEVELS_B.Level_Table_Name%TYPE;
4856: l_master_table BSC_SYS_DIM_LEVELS_B.Level_Table_Name%TYPE;
4857: l_input_table BSC_SYS_DIM_LEVELS_B.Level_Table_Name%TYPE;

Line 4855: l_old_mas_name BSC_SYS_DIM_LEVELS_B.Level_Table_Name%TYPE;

4851: RETURN BOOLEAN IS
4852: l_count NUMBER;
4853: l_abbreviation BSC_SYS_DIM_LEVELS_B.Abbreviation%TYPE;
4854: l_sql_stmt VARCHAR2(32000);
4855: l_old_mas_name BSC_SYS_DIM_LEVELS_B.Level_Table_Name%TYPE;
4856: l_master_table BSC_SYS_DIM_LEVELS_B.Level_Table_Name%TYPE;
4857: l_input_table BSC_SYS_DIM_LEVELS_B.Level_Table_Name%TYPE;
4858: l_Tbl_Statements BSC_APPS.Autonomous_Statements_Tbl_Type;
4859: e_mlog_exception EXCEPTION;

Line 4856: l_master_table BSC_SYS_DIM_LEVELS_B.Level_Table_Name%TYPE;

4852: l_count NUMBER;
4853: l_abbreviation BSC_SYS_DIM_LEVELS_B.Abbreviation%TYPE;
4854: l_sql_stmt VARCHAR2(32000);
4855: l_old_mas_name BSC_SYS_DIM_LEVELS_B.Level_Table_Name%TYPE;
4856: l_master_table BSC_SYS_DIM_LEVELS_B.Level_Table_Name%TYPE;
4857: l_input_table BSC_SYS_DIM_LEVELS_B.Level_Table_Name%TYPE;
4858: l_Tbl_Statements BSC_APPS.Autonomous_Statements_Tbl_Type;
4859: e_mlog_exception EXCEPTION;
4860: l_error_msg VARCHAR2(4000);

Line 4857: l_input_table BSC_SYS_DIM_LEVELS_B.Level_Table_Name%TYPE;

4853: l_abbreviation BSC_SYS_DIM_LEVELS_B.Abbreviation%TYPE;
4854: l_sql_stmt VARCHAR2(32000);
4855: l_old_mas_name BSC_SYS_DIM_LEVELS_B.Level_Table_Name%TYPE;
4856: l_master_table BSC_SYS_DIM_LEVELS_B.Level_Table_Name%TYPE;
4857: l_input_table BSC_SYS_DIM_LEVELS_B.Level_Table_Name%TYPE;
4858: l_Tbl_Statements BSC_APPS.Autonomous_Statements_Tbl_Type;
4859: e_mlog_exception EXCEPTION;
4860: l_error_msg VARCHAR2(4000);
4861:

Line 4881: FROM BSC_SYS_DIM_LEVELS_B

4877: END IF;
4878: FOR cd IN c_MN_Tables LOOP
4879: SELECT Abbreviation
4880: INTO l_abbreviation
4881: FROM BSC_SYS_DIM_LEVELS_B
4882: WHERE dim_level_id = cd.Dim_Level_Id;
4883:
4884: IF (cd.Dim_Level_Id > p_Dim_Level_Rec.Bsc_Level_Id) THEN
4885: l_input_table := 'BSC_DI_'||p_Dim_Level_Rec.Bsc_Level_Id||'_'||cd.Dim_Level_Id;

Line 5109: l_master_table BSC_SYS_DIM_LEVELS_B.Level_Table_Name%TYPE;

5105: )
5106: RETURN BOOLEAN IS
5107: l_count NUMBER;
5108: l_sql_stmt VARCHAR2(32000);
5109: l_master_table BSC_SYS_DIM_LEVELS_B.Level_Table_Name%TYPE;
5110: l_input_table BSC_SYS_DIM_LEVELS_B.Level_Table_Name%TYPE;
5111: l_view_name BSC_SYS_DIM_LEVELS_B.Level_Table_Name%TYPE;
5112: l_col_names VARCHAR2(32000);
5113: l_index_Name VARCHAR2(100) := NULL;

Line 5110: l_input_table BSC_SYS_DIM_LEVELS_B.Level_Table_Name%TYPE;

5106: RETURN BOOLEAN IS
5107: l_count NUMBER;
5108: l_sql_stmt VARCHAR2(32000);
5109: l_master_table BSC_SYS_DIM_LEVELS_B.Level_Table_Name%TYPE;
5110: l_input_table BSC_SYS_DIM_LEVELS_B.Level_Table_Name%TYPE;
5111: l_view_name BSC_SYS_DIM_LEVELS_B.Level_Table_Name%TYPE;
5112: l_col_names VARCHAR2(32000);
5113: l_index_Name VARCHAR2(100) := NULL;
5114: l_index_Count NUMBER;

Line 5111: l_view_name BSC_SYS_DIM_LEVELS_B.Level_Table_Name%TYPE;

5107: l_count NUMBER;
5108: l_sql_stmt VARCHAR2(32000);
5109: l_master_table BSC_SYS_DIM_LEVELS_B.Level_Table_Name%TYPE;
5110: l_input_table BSC_SYS_DIM_LEVELS_B.Level_Table_Name%TYPE;
5111: l_view_name BSC_SYS_DIM_LEVELS_B.Level_Table_Name%TYPE;
5112: l_col_names VARCHAR2(32000);
5113: l_index_Name VARCHAR2(100) := NULL;
5114: l_index_Count NUMBER;
5115: l_flag BOOLEAN;

Line 5158: FROM BSC_SYS_DIM_LEVELS_B

5154: l_Create_View := FALSE;
5155:
5156: SELECT Level_Table_Name
5157: INTO l_master_table
5158: FROM BSC_SYS_DIM_LEVELS_B
5159: WHERE dim_level_id = cd.dim_level_id;
5160:
5161: l_master_table := UPPER(l_master_table);
5162: l_input_table := 'BSC_DI_'||cd.Dim_Level_Id;

Line 5550: FROM BSC_SYS_DIM_LEVELS_BY_GROUP

5546: SELECT Short_Name
5547: FROM BSC_SYS_DIM_GROUPS_VL
5548: WHERE dim_group_id IN
5549: (SELECT dim_group_id
5550: FROM BSC_SYS_DIM_LEVELS_BY_GROUP
5551: WHERE dim_level_id = l_bsc_dim_obj_rec.Bsc_Level_Id);
5552:
5553: CURSOR c_Kpi_Dim_Set IS
5554: SELECT DISTINCT C.Name||'['||C.Indicator||']' Name

Line 5557: , BSC_SYS_DIM_LEVELS_B D

5553: CURSOR c_Kpi_Dim_Set IS
5554: SELECT DISTINCT C.Name||'['||C.Indicator||']' Name
5555: , C.Indicator
5556: FROM BSC_KPI_DIM_LEVELS_B A
5557: , BSC_SYS_DIM_LEVELS_B D
5558: , BSC_KPIS_VL C
5559: WHERE A.Level_Table_Name = D.Level_Table_Name
5560: AND D.Dim_Level_Id = l_bsc_dim_obj_rec.Bsc_Level_Id
5561: AND C.share_flag <> 2

Line 5568: , BSC_SYS_DIM_LEVELS_B D

5564: CURSOR c_Kpi_Dim_Set1 IS
5565: SELECT DISTINCT C.Name||'['||C.Indicator||']' Name
5566: ,C.Indicator
5567: FROM BSC_KPI_DIM_LEVELS_B A
5568: , BSC_SYS_DIM_LEVELS_B D
5569: , BSC_KPIS_VL C
5570: WHERE A.Level_Table_Name = D.Level_Table_Name
5571: AND C.share_flag <> 2
5572: AND C.Indicator = A.Indicator

Line 5645: FROM BSC_SYS_DIM_LEVELS_BY_GROUP A

5641: , p_dim_name => l_dim_Grp_name
5642: )) LOOP
5643: SELECT COUNT(0)
5644: INTO l_count
5645: FROM BSC_SYS_DIM_LEVELS_BY_GROUP A
5646: , BSC_SYS_DIM_GROUPS_VL B
5647: WHERE A.Dim_Level_Id = l_bsc_dim_obj_rec.Bsc_Level_Id
5648: AND B.Short_Name = l_dim_Grp_name
5649: AND B.Dim_Group_Id = A.Dim_Group_Id;

Line 5797: FROM BSC_SYS_DIM_LEVELS_B B

5793: AND B.SHORT_NAME = l_dim_short_name;
5794:
5795: CURSOR cr_dimobj_in_dimset IS
5796: SELECT B.SHORT_NAME
5797: FROM BSC_SYS_DIM_LEVELS_B B
5798: ,BSC_KPI_DIM_LEVEL_PROPERTIES KDL
5799: WHERE B.DIM_LEVEL_ID = KDL.DIM_LEVEL_ID
5800: AND KDL.indicator = l_kpi_id
5801: AND KDL.dim_set_id = l_dim_set_id;

Line 5899: l_Data_Source BSC_SYS_DIM_LEVELS_B.Source%TYPE := NULL;

5895: FUNCTION Get_Dim_Obj_Source
5896: ( p_dim_obj_id IN NUMBER := NULL
5897: , p_short_Name IN VARCHAR2 := NULL
5898: ) RETURN VARCHAR2 IS
5899: l_Data_Source BSC_SYS_DIM_LEVELS_B.Source%TYPE := NULL;
5900:
5901: CURSOR c_dim_obj_source_id IS
5902: SELECT Source
5903: FROM BSC_SYS_DIM_LEVELS_B

Line 5903: FROM BSC_SYS_DIM_LEVELS_B

5899: l_Data_Source BSC_SYS_DIM_LEVELS_B.Source%TYPE := NULL;
5900:
5901: CURSOR c_dim_obj_source_id IS
5902: SELECT Source
5903: FROM BSC_SYS_DIM_LEVELS_B
5904: WHERE Dim_Level_Id = p_dim_obj_id;
5905:
5906: CURSOR c_dim_obj_source_name IS
5907: SELECT Source

Line 5908: FROM BSC_SYS_DIM_LEVELS_B

5904: WHERE Dim_Level_Id = p_dim_obj_id;
5905:
5906: CURSOR c_dim_obj_source_name IS
5907: SELECT Source
5908: FROM BSC_SYS_DIM_LEVELS_B
5909: WHERE Short_Name = p_short_Name;
5910: BEGIN
5911: IF (p_dim_obj_id IS NOT NULL) THEN
5912: IF (c_dim_obj_source_id%ISOPEN) THEN

Line 5970: ( p_Short_Name IN BSC_SYS_DIM_LEVELS_B.Short_Name%TYPE

5966: /*********************************************************************************************
5967: Returns the Dimension Object ID of BSC
5968: *********************************************************************************************/
5969: FUNCTION Get_Bsc_Dim_Obj_ID
5970: ( p_Short_Name IN BSC_SYS_DIM_LEVELS_B.Short_Name%TYPE
5971: ) RETURN NUMBER IS
5972:
5973: l_dim_id BSC_SYS_DIM_LEVELS_B.Dim_Level_ID%TYPE;
5974:

Line 5973: l_dim_id BSC_SYS_DIM_LEVELS_B.Dim_Level_ID%TYPE;

5969: FUNCTION Get_Bsc_Dim_Obj_ID
5970: ( p_Short_Name IN BSC_SYS_DIM_LEVELS_B.Short_Name%TYPE
5971: ) RETURN NUMBER IS
5972:
5973: l_dim_id BSC_SYS_DIM_LEVELS_B.Dim_Level_ID%TYPE;
5974:
5975: CURSOR c_Dim_Group_Id IS
5976: SELECT Dim_Level_ID
5977: FROM BSC_SYS_DIM_LEVELS_B

Line 5977: FROM BSC_SYS_DIM_LEVELS_B

5973: l_dim_id BSC_SYS_DIM_LEVELS_B.Dim_Level_ID%TYPE;
5974:
5975: CURSOR c_Dim_Group_Id IS
5976: SELECT Dim_Level_ID
5977: FROM BSC_SYS_DIM_LEVELS_B
5978: WHERE Short_Name = p_Short_Name;
5979: BEGIN
5980: IF (c_Dim_Group_Id%ISOPEN) THEN
5981: CLOSE c_Dim_Group_Id;

Line 6016: , BSC_SYS_DIM_LEVELS_B B

6012: , A.Hide_In_Design
6013: , B.Level_Table_Name
6014: , B.Dim_Level_Id
6015: FROM BIS_LEVELS A
6016: , BSC_SYS_DIM_LEVELS_B B
6017: WHERE A.Short_Name = B.Short_Name
6018: AND B.Source = 'PMF'
6019: AND A.Source = 'OLTP';
6020:

Line 6030: , BSC_SYS_DIM_LEVELS_B B

6026: , A.Hide_In_Design
6027: , B.Level_Table_Name
6028: , B.Dim_Level_Id
6029: FROM BIS_LEVELS A
6030: , BSC_SYS_DIM_LEVELS_B B
6031: WHERE A.Short_Name = B.Short_Name
6032: AND B.Source = 'PMF'
6033: AND A.Source = 'OLTP'
6034: AND B.Short_Name = p_Short_Name;

Line 6119: , BSC_SYS_DIM_LEVELS_B B

6115: CURSOR c_Pmf_Dim_Obj_Edw is
6116: SELECT A.Short_Name
6117: , B.Level_Table_Name
6118: FROM BIS_LEVELS A
6119: , BSC_SYS_DIM_LEVELS_B B
6120: WHERE A.Short_Name = B.Short_Name
6121: AND B.Source = 'PMF'
6122: AND A.Source = 'EDW'
6123: AND B.TABLE_TYPE = 1; -- Identify EDW with existing Views

Line 6127: UPDATE BSC_SYS_DIM_LEVELS_B

6123: AND B.TABLE_TYPE = 1; -- Identify EDW with existing Views
6124: BEGIN
6125:
6126: FOR PMF_EDW IN c_Pmf_Dim_Obj_Edw LOOP
6127: UPDATE BSC_SYS_DIM_LEVELS_B
6128: SET Table_Type = -1
6129: WHERE Short_Name = PMF_EDW.Short_Name;
6130:
6131: l_sql := 'DROP VIEW ' || PMF_EDW.Level_Table_Name;

Line 6210: p_Short_Name IN BSC_SYS_DIM_LEVELS_B.Short_Name%TYPE

6206: END IF;
6207: END Init_Create_Pmf_Recs;
6208: --=======================================================================================
6209: FUNCTION Get_Table_Type_Value(
6210: p_Short_Name IN BSC_SYS_DIM_LEVELS_B.Short_Name%TYPE
6211: ) RETURN NUMBER IS
6212:
6213: l_Return NUMBER;
6214:

Line 6219: FROM BSC_SYS_DIM_LEVELS_B

6215: BEGIN
6216:
6217: SELECT TABLE_TYPE
6218: INTO l_Return
6219: FROM BSC_SYS_DIM_LEVELS_B
6220: WHERE SHORT_NAME = p_Short_Name;
6221:
6222: RETURN l_Return;
6223:

Line 6502: l_flag bsc_sys_dim_levels_b.table_type%TYPE;

6498: , x_Msg_Data OUT NOCOPY VARCHAR2
6499: )
6500: IS
6501:
6502: l_flag bsc_sys_dim_levels_b.table_type%TYPE;
6503: CURSOR c_bsc_table_type_flag IS
6504: SELECT table_type
6505: FROM bsc_sys_dim_levels_b
6506: WHERE short_name = p_Dim_Obj_Short_Name;

Line 6505: FROM bsc_sys_dim_levels_b

6501:
6502: l_flag bsc_sys_dim_levels_b.table_type%TYPE;
6503: CURSOR c_bsc_table_type_flag IS
6504: SELECT table_type
6505: FROM bsc_sys_dim_levels_b
6506: WHERE short_name = p_Dim_Obj_Short_Name;
6507:
6508: BEGIN
6509:

Line 6754: BSC_SYS_DIM_LEVELS_BY_GROUP GRP

6750:
6751: CURSOR C_SOURCE_DIM_OBJS IS
6752: SELECT SYS.SOURCE,SYS.SHORT_NAME,SYS.NAME
6753: FROM BSC_SYS_DIM_LEVELS_VL SYS,
6754: BSC_SYS_DIM_LEVELS_BY_GROUP GRP
6755: WHERE SYS.dim_level_id = GRP.dim_level_id
6756: AND GRP.dim_group_id = l_dim_grp_id;
6757:
6758: BEGIN

Line 6771: FROM BSC_SYS_DIM_LEVELS_BY_GROUP GRP

6767: l_dim_grp_id := CTYPE.DIM_ID;
6768: l_dim_short_name := CTYPE.SHORT_NAME;
6769:
6770: SELECT COUNT(1) into l_count
6771: FROM BSC_SYS_DIM_LEVELS_BY_GROUP GRP
6772: WHERE GRP.dim_group_id = l_dim_grp_id;
6773:
6774: SELECT COUNT(1) into l_exist
6775: FROM BSC_SYS_DIM_LEVELS_VL SYS,

Line 6776: BSC_SYS_DIM_LEVELS_BY_GROUP GRP

6772: WHERE GRP.dim_group_id = l_dim_grp_id;
6773:
6774: SELECT COUNT(1) into l_exist
6775: FROM BSC_SYS_DIM_LEVELS_VL SYS,
6776: BSC_SYS_DIM_LEVELS_BY_GROUP GRP
6777: WHERE SYS.dim_level_id = GRP.dim_level_id
6778: AND GRP.dim_group_id = l_dim_grp_id
6779: AND SYS.SHORT_NAME = p_dim_obj_short_name;
6780:

Line 6896: , BSC_SYS_DIM_LEVELS_B D

6892: CURSOR c_Kpi_Dim_Set1 IS
6893: SELECT DISTINCT C.Name||'['||C.Indicator||']' Name
6894: ,C.Indicator
6895: FROM BSC_KPI_DIM_LEVELS_B A
6896: , BSC_SYS_DIM_LEVELS_B D
6897: , BSC_KPIS_VL C
6898: WHERE A.Level_Table_Name = D.Level_Table_Name
6899: AND C.share_flag <> 2
6900: AND C.Indicator = A.Indicator

Line 6907: , BSC_SYS_DIM_LEVELS_B D

6903: CURSOR c_Kpi_Dim_Set IS
6904: SELECT DISTINCT C.Name||'['||C.Indicator||']' Name
6905: , C.Indicator
6906: FROM BSC_KPI_DIM_LEVELS_B A
6907: , BSC_SYS_DIM_LEVELS_B D
6908: , BSC_KPIS_VL C
6909: WHERE A.Level_Table_Name = D.Level_Table_Name
6910: AND D.Dim_Level_Id = l_bsc_dim_obj_rec.Bsc_Level_Id
6911: AND C.share_flag <> 2

Line 7060: FROM BSC_SYS_DIM_LEVELS_B D

7056: l_count NUMBER;
7057:
7058: CURSOR c_Lvl_Pk_Col IS
7059: SELECT D.LEVEL_PK_COL
7060: FROM BSC_SYS_DIM_LEVELS_B D
7061: WHERE D.SHORT_NAME = p_Dim_Level_Rec.Bsc_Level_Short_Name;
7062: BEGIN
7063:
7064: FND_MSG_PUB.Initialize;

Line 7106: FROM BSC_SYS_DIM_LEVELS_B

7102: l_alias := NULL;
7103: l_temp_var := l_Level_Pk_Col;
7104: WHILE (l_flag) LOOP
7105: SELECT COUNT(1) INTO l_count
7106: FROM BSC_SYS_DIM_LEVELS_B
7107: WHERE SHORT_NAME <> p_Dim_Level_Rec.Bsc_Level_Short_Name
7108: AND UPPER(LEVEL_PK_COL) = UPPER(l_temp_var);
7109: IF (l_count = 0) THEN
7110: l_flag := FALSE;

Line 7260: bsc_sys_dim_levels_by_group

7256: LOOP
7257: FETCH c_dim_groups INTO l_Dim_Short_Name;
7258: EXIT WHEN c_dim_groups%NOTFOUND;
7259: UPDATE
7260: bsc_sys_dim_levels_by_group
7261: SET
7262: total_flag = p_All_Flag
7263: WHERE
7264: dim_level_id = BSC_BIS_DIM_OBJ_PUB.Get_Bsc_Dim_Obj_ID(p_Dim_Obj_Short_Name)