115: REM | 26-Apr-2006 psomesul Enh#5124125 - Creating BSC wrapper view of a |
116: REM | BIS type Dim. Obj. |
117: REM | 15-JUN-2006 ashankar Bug#5254737 Made changes to Create_Dim_Object |
118: REM | Method.Removed the parameter value 'TRUE' in |
119: REM | FND_MESSAGE.SET_TOKEN API |
120: REM | 20-FEB-2006 akoduri Bug #5880618 Validate_PMF_Base_View API is |
121: REM | is failing when more than one parent is passed |
122: REM | 29-JUN-2007 akoduri Bug #6155820 Structural change warning is not |
123: REM | displayed during create dimension object |
964: l_prototype_default_value := p_prototype_default_value;
965:
966: --check for not null fields
967: IF (l_dim_obj_name IS NULL) THEN
968: FND_MESSAGE.SET_NAME('BSC','BSC_REQ_FIELD_MISSING');
969: FND_MESSAGE.SET_TOKEN('FIELD_NAME', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DIM_OBJ_SHORT_NAME'));
970: FND_MSG_PUB.ADD;
971: RAISE FND_API.G_EXC_ERROR;
972: END IF;
965:
966: --check for not null fields
967: IF (l_dim_obj_name IS NULL) THEN
968: FND_MESSAGE.SET_NAME('BSC','BSC_REQ_FIELD_MISSING');
969: FND_MESSAGE.SET_TOKEN('FIELD_NAME', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DIM_OBJ_SHORT_NAME'));
970: FND_MSG_PUB.ADD;
971: RAISE FND_API.G_EXC_ERROR;
972: END IF;
973: IF (NOT is_Valid_AlphaNum(l_dim_obj_name)) THEN
970: FND_MSG_PUB.ADD;
971: RAISE FND_API.G_EXC_ERROR;
972: END IF;
973: IF (NOT is_Valid_AlphaNum(l_dim_obj_name)) THEN
974: FND_MESSAGE.SET_NAME('BSC','BSC_ALPHA_NUM_REQUIRED');
975: FND_MESSAGE.SET_TOKEN('VALUE', l_dim_obj_name);
976: FND_MESSAGE.SET_TOKEN('NAME', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DIM_OBJ_SHORT_NAME'));
977: FND_MSG_PUB.ADD;
978: RAISE FND_API.G_EXC_ERROR;
971: RAISE FND_API.G_EXC_ERROR;
972: END IF;
973: IF (NOT is_Valid_AlphaNum(l_dim_obj_name)) THEN
974: FND_MESSAGE.SET_NAME('BSC','BSC_ALPHA_NUM_REQUIRED');
975: FND_MESSAGE.SET_TOKEN('VALUE', l_dim_obj_name);
976: FND_MESSAGE.SET_TOKEN('NAME', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DIM_OBJ_SHORT_NAME'));
977: FND_MSG_PUB.ADD;
978: RAISE FND_API.G_EXC_ERROR;
979: END IF;
972: END IF;
973: IF (NOT is_Valid_AlphaNum(l_dim_obj_name)) THEN
974: FND_MESSAGE.SET_NAME('BSC','BSC_ALPHA_NUM_REQUIRED');
975: FND_MESSAGE.SET_TOKEN('VALUE', l_dim_obj_name);
976: FND_MESSAGE.SET_TOKEN('NAME', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DIM_OBJ_SHORT_NAME'));
977: FND_MSG_PUB.ADD;
978: RAISE FND_API.G_EXC_ERROR;
979: END IF;
980: IF (p_display_name IS NULL) THEN
977: FND_MSG_PUB.ADD;
978: RAISE FND_API.G_EXC_ERROR;
979: END IF;
980: IF (p_display_name IS NULL) THEN
981: FND_MESSAGE.SET_NAME('BSC','BSC_REQ_FIELD_MISSING');
982: FND_MESSAGE.SET_TOKEN('FIELD_NAME', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DISPLAY_NAME'));
983: FND_MSG_PUB.ADD;
984: RAISE FND_API.G_EXC_ERROR;
985: END IF;
978: RAISE FND_API.G_EXC_ERROR;
979: END IF;
980: IF (p_display_name IS NULL) THEN
981: FND_MESSAGE.SET_NAME('BSC','BSC_REQ_FIELD_MISSING');
982: FND_MESSAGE.SET_TOKEN('FIELD_NAME', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DISPLAY_NAME'));
983: FND_MSG_PUB.ADD;
984: RAISE FND_API.G_EXC_ERROR;
985: END IF;
986: IF (l_application_id IS NULL) THEN
983: FND_MSG_PUB.ADD;
984: RAISE FND_API.G_EXC_ERROR;
985: END IF;
986: IF (l_application_id IS NULL) THEN
987: FND_MESSAGE.SET_NAME('BSC','BSC_REQ_FIELD_MISSING');
988: FND_MESSAGE.SET_TOKEN('FIELD_NAME', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'APPLICATION_ID'));
989: FND_MSG_PUB.ADD;
990: RAISE FND_API.G_EXC_ERROR;
991: END IF;
984: RAISE FND_API.G_EXC_ERROR;
985: END IF;
986: IF (l_application_id IS NULL) THEN
987: FND_MESSAGE.SET_NAME('BSC','BSC_REQ_FIELD_MISSING');
988: FND_MESSAGE.SET_TOKEN('FIELD_NAME', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'APPLICATION_ID'));
989: FND_MSG_PUB.ADD;
990: RAISE FND_API.G_EXC_ERROR;
991: END IF;
992: IF (p_data_source IS NULL) THEN
989: FND_MSG_PUB.ADD;
990: RAISE FND_API.G_EXC_ERROR;
991: END IF;
992: IF (p_data_source IS NULL) THEN
993: FND_MESSAGE.SET_NAME('BSC','BSC_REQ_FIELD_MISSING');
994: FND_MESSAGE.SET_TOKEN('FIELD_NAME', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DATA_SOURCE'));
995: FND_MSG_PUB.ADD;
996: RAISE FND_API.G_EXC_ERROR;
997: END IF;
990: RAISE FND_API.G_EXC_ERROR;
991: END IF;
992: IF (p_data_source IS NULL) THEN
993: FND_MESSAGE.SET_NAME('BSC','BSC_REQ_FIELD_MISSING');
994: FND_MESSAGE.SET_TOKEN('FIELD_NAME', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DATA_SOURCE'));
995: FND_MSG_PUB.ADD;
996: RAISE FND_API.G_EXC_ERROR;
997: END IF;
998: IF ((p_data_source <> 'BSC') AND (p_data_source <> 'PMF')) THEN
995: FND_MSG_PUB.ADD;
996: RAISE FND_API.G_EXC_ERROR;
997: END IF;
998: IF ((p_data_source <> 'BSC') AND (p_data_source <> 'PMF')) THEN
999: FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_DATA_SOURCE');
1000: FND_MSG_PUB.ADD;
1001: RAISE FND_API.G_EXC_ERROR;
1002: END IF;
1003: IF (p_data_source = 'BSC') THEN
1003: IF (p_data_source = 'BSC') THEN
1004: l_bsc_dim_obj_rec.Source := 'OLTP';
1005: ELSE
1006: IF ((p_source_type IS NULL) OR ((p_source_type <> 'OLTP') AND (p_source_type <> 'EDW'))) THEN
1007: FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_SOURCE_TYPE');
1008: FND_MSG_PUB.ADD;
1009: RAISE FND_API.G_EXC_ERROR;
1010: ELSE
1011: l_bsc_dim_obj_rec.Source := p_source_type;
1015: SELECT COUNT(1) INTO l_count
1016: FROM BIS_LEVELS_VL
1017: WHERE UPPER(short_name) = UPPER(l_dim_obj_name);
1018: IF (l_count > 0) THEN
1019: FND_MESSAGE.SET_NAME('BSC','BSC_UNIQUE_NAME_REQUIRED');
1020: FND_MESSAGE.SET_TOKEN('SHORT_NAME', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DIM_OBJ_SHORT_NAME'));
1021: FND_MESSAGE.SET_TOKEN('NAME_VALUE', l_dim_obj_name);
1022: FND_MSG_PUB.ADD;
1023: RAISE FND_API.G_EXC_ERROR;
1016: FROM BIS_LEVELS_VL
1017: WHERE UPPER(short_name) = UPPER(l_dim_obj_name);
1018: IF (l_count > 0) THEN
1019: FND_MESSAGE.SET_NAME('BSC','BSC_UNIQUE_NAME_REQUIRED');
1020: FND_MESSAGE.SET_TOKEN('SHORT_NAME', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DIM_OBJ_SHORT_NAME'));
1021: FND_MESSAGE.SET_TOKEN('NAME_VALUE', l_dim_obj_name);
1022: FND_MSG_PUB.ADD;
1023: RAISE FND_API.G_EXC_ERROR;
1024: END IF;
1017: WHERE UPPER(short_name) = UPPER(l_dim_obj_name);
1018: IF (l_count > 0) THEN
1019: FND_MESSAGE.SET_NAME('BSC','BSC_UNIQUE_NAME_REQUIRED');
1020: FND_MESSAGE.SET_TOKEN('SHORT_NAME', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DIM_OBJ_SHORT_NAME'));
1021: FND_MESSAGE.SET_TOKEN('NAME_VALUE', l_dim_obj_name);
1022: FND_MSG_PUB.ADD;
1023: RAISE FND_API.G_EXC_ERROR;
1024: END IF;
1025: --check for uniqueness of l_dim_obj_name in BSC's metadata
1026: SELECT COUNT(1) INTO l_count
1027: FROM BSC_SYS_DIM_LEVELS_VL
1028: WHERE UPPER(short_name) = UPPER(l_dim_obj_name);
1029: IF (l_count > 0) THEN
1030: FND_MESSAGE.SET_NAME('BSC','BSC_UNIQUE_NAME_REQUIRED');
1031: FND_MESSAGE.SET_TOKEN('SHORT_NAME', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DIM_OBJ_SHORT_NAME'));
1032: FND_MESSAGE.SET_TOKEN('NAME_VALUE', l_dim_obj_name);
1033: FND_MSG_PUB.ADD;
1034: RAISE FND_API.G_EXC_ERROR;
1027: FROM BSC_SYS_DIM_LEVELS_VL
1028: WHERE UPPER(short_name) = UPPER(l_dim_obj_name);
1029: IF (l_count > 0) THEN
1030: FND_MESSAGE.SET_NAME('BSC','BSC_UNIQUE_NAME_REQUIRED');
1031: FND_MESSAGE.SET_TOKEN('SHORT_NAME', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DIM_OBJ_SHORT_NAME'));
1032: FND_MESSAGE.SET_TOKEN('NAME_VALUE', l_dim_obj_name);
1033: FND_MSG_PUB.ADD;
1034: RAISE FND_API.G_EXC_ERROR;
1035: END IF;
1028: WHERE UPPER(short_name) = UPPER(l_dim_obj_name);
1029: IF (l_count > 0) THEN
1030: FND_MESSAGE.SET_NAME('BSC','BSC_UNIQUE_NAME_REQUIRED');
1031: FND_MESSAGE.SET_TOKEN('SHORT_NAME', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DIM_OBJ_SHORT_NAME'));
1032: FND_MESSAGE.SET_TOKEN('NAME_VALUE', l_dim_obj_name);
1033: FND_MSG_PUB.ADD;
1034: RAISE FND_API.G_EXC_ERROR;
1035: END IF;
1036: ---Check the dimensions that are being attached may contain both autogenerated and existing
1058: SELECT COUNT(1) INTO l_count
1059: FROM BIS_LEVELS_VL
1060: WHERE UPPER(name) = UPPER(p_display_name);
1061: IF (l_count <> 0) THEN
1062: FND_MESSAGE.SET_NAME('BSC','BSC_UNIQUE_NAME_REQUIRED');
1063: FND_MESSAGE.SET_TOKEN('SHORT_NAME', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DISPLAY_NAME'));
1064: FND_MESSAGE.SET_TOKEN('NAME_VALUE', p_display_name);
1065: FND_MSG_PUB.ADD;
1066: RAISE FND_API.G_EXC_ERROR;
1059: FROM BIS_LEVELS_VL
1060: WHERE UPPER(name) = UPPER(p_display_name);
1061: IF (l_count <> 0) THEN
1062: FND_MESSAGE.SET_NAME('BSC','BSC_UNIQUE_NAME_REQUIRED');
1063: FND_MESSAGE.SET_TOKEN('SHORT_NAME', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DISPLAY_NAME'));
1064: FND_MESSAGE.SET_TOKEN('NAME_VALUE', p_display_name);
1065: FND_MSG_PUB.ADD;
1066: RAISE FND_API.G_EXC_ERROR;
1067: END IF;
1060: WHERE UPPER(name) = UPPER(p_display_name);
1061: IF (l_count <> 0) THEN
1062: FND_MESSAGE.SET_NAME('BSC','BSC_UNIQUE_NAME_REQUIRED');
1063: FND_MESSAGE.SET_TOKEN('SHORT_NAME', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DISPLAY_NAME'));
1064: FND_MESSAGE.SET_TOKEN('NAME_VALUE', p_display_name);
1065: FND_MSG_PUB.ADD;
1066: RAISE FND_API.G_EXC_ERROR;
1067: END IF;
1068: ELSIF (p_data_source = 'BSC') THEN -- Allow insertion into BSC Data Model, with the same display name
1163: IF ((l_bsc_dim_obj_rec.Bsc_Level_User_Key_Size < BSC_BIS_DIM_OBJ_PUB.Dim_Obj_Code_Min_Size) OR
1164: (l_bsc_dim_obj_rec.Bsc_Level_User_Key_Size > BSC_BIS_DIM_OBJ_PUB.Dim_Obj_Code_Max_Size) OR
1165: (l_bsc_dim_obj_rec.Bsc_Level_Disp_Key_Size < BSC_BIS_DIM_OBJ_PUB.Dim_Obj_Name_Min_Size) OR
1166: (l_bsc_dim_obj_rec.Bsc_Level_Disp_Key_Size > BSC_BIS_DIM_OBJ_PUB.Dim_Obj_Name_Max_Size)) THEN
1167: FND_MESSAGE.SET_NAME('BSC','BSC_CODE_NAME_SIZE');
1168: FND_MSG_PUB.ADD;
1169: RAISE FND_API.G_EXC_ERROR;
1170: END IF;
1171: END IF;
1182: l_bsc_dim_obj_rec.Bsc_Level_Name := substr(get_valid_ddlentry_frm_name(l_dim_obj_name),1,30);
1183: END IF;
1184: l_bsc_dim_obj_rec.Bsc_Level_Name := 'BSC_D_'||SUBSTR(l_bsc_dim_obj_rec.Bsc_Level_Name, 1, 21);
1185: IF (NOT is_Valid_Identifier(l_bsc_dim_obj_rec.Bsc_Level_Name)) THEN
1186: FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_SQL_IDENTIFIER');
1187: FND_MESSAGE.SET_TOKEN('SQL_IDENT', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DIM_OBJ_TAB_NAME'));
1188: FND_MESSAGE.SET_TOKEN('SQL_VALUE', l_bsc_dim_obj_rec.Bsc_Level_Name);
1189: FND_MSG_PUB.ADD;
1190: RAISE FND_API.G_EXC_ERROR;
1183: END IF;
1184: l_bsc_dim_obj_rec.Bsc_Level_Name := 'BSC_D_'||SUBSTR(l_bsc_dim_obj_rec.Bsc_Level_Name, 1, 21);
1185: IF (NOT is_Valid_Identifier(l_bsc_dim_obj_rec.Bsc_Level_Name)) THEN
1186: FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_SQL_IDENTIFIER');
1187: FND_MESSAGE.SET_TOKEN('SQL_IDENT', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DIM_OBJ_TAB_NAME'));
1188: FND_MESSAGE.SET_TOKEN('SQL_VALUE', l_bsc_dim_obj_rec.Bsc_Level_Name);
1189: FND_MSG_PUB.ADD;
1190: RAISE FND_API.G_EXC_ERROR;
1191: END IF;
1184: l_bsc_dim_obj_rec.Bsc_Level_Name := 'BSC_D_'||SUBSTR(l_bsc_dim_obj_rec.Bsc_Level_Name, 1, 21);
1185: IF (NOT is_Valid_Identifier(l_bsc_dim_obj_rec.Bsc_Level_Name)) THEN
1186: FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_SQL_IDENTIFIER');
1187: FND_MESSAGE.SET_TOKEN('SQL_IDENT', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DIM_OBJ_TAB_NAME'));
1188: FND_MESSAGE.SET_TOKEN('SQL_VALUE', l_bsc_dim_obj_rec.Bsc_Level_Name);
1189: FND_MSG_PUB.ADD;
1190: RAISE FND_API.G_EXC_ERROR;
1191: END IF;
1192: --generate unique table name if already exists
1228: IF(NOT l_flag) THEN
1229: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1230: END IF;
1231: ELSE
1232: FND_MESSAGE.SET_NAME('BSC','BSC_PRE_DIM_OBJ_SOURCE_TYPE');
1233: FND_MSG_PUB.ADD;
1234: RAISE FND_API.G_EXC_ERROR;
1235: END IF;
1236: END IF;
1252: l_bis_dim_level_rec.Level_Values_View_Name := l_source_table;
1253: END IF;
1254: IF ((l_bis_dim_level_rec.Level_Values_View_Name IS NOT NULL) AND
1255: (NOT is_Valid_Identifier(l_bis_dim_level_rec.Level_Values_View_Name))) THEN
1256: FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_SQL_IDENTIFIER');
1257: FND_MESSAGE.SET_TOKEN('SQL_IDENT', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DIM_OBJ_VIEW_NAME'));
1258: FND_MESSAGE.SET_TOKEN('SQL_VALUE', l_bis_dim_level_rec.Level_Values_View_Name);
1259: FND_MSG_PUB.ADD;
1260: RAISE FND_API.G_EXC_ERROR;
1253: END IF;
1254: IF ((l_bis_dim_level_rec.Level_Values_View_Name IS NOT NULL) AND
1255: (NOT is_Valid_Identifier(l_bis_dim_level_rec.Level_Values_View_Name))) THEN
1256: FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_SQL_IDENTIFIER');
1257: FND_MESSAGE.SET_TOKEN('SQL_IDENT', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DIM_OBJ_VIEW_NAME'));
1258: FND_MESSAGE.SET_TOKEN('SQL_VALUE', l_bis_dim_level_rec.Level_Values_View_Name);
1259: FND_MSG_PUB.ADD;
1260: RAISE FND_API.G_EXC_ERROR;
1261: END IF;
1254: IF ((l_bis_dim_level_rec.Level_Values_View_Name IS NOT NULL) AND
1255: (NOT is_Valid_Identifier(l_bis_dim_level_rec.Level_Values_View_Name))) THEN
1256: FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_SQL_IDENTIFIER');
1257: FND_MESSAGE.SET_TOKEN('SQL_IDENT', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DIM_OBJ_VIEW_NAME'));
1258: FND_MESSAGE.SET_TOKEN('SQL_VALUE', l_bis_dim_level_rec.Level_Values_View_Name);
1259: FND_MSG_PUB.ADD;
1260: RAISE FND_API.G_EXC_ERROR;
1261: END IF;
1262:
1287: l_bis_dim_level_rec.Attribute_Code := l_bis_dim_level_rec.Dimension_Level_Short_Name;
1288: END IF;
1289: IF ((l_bsc_dim_obj_rec.Bsc_Source = 'PMF') AND (p_Master_Level IS NOT NULL)) THEN
1290: IF (p_Master_Level = l_bis_dim_level_rec.Dimension_Level_Short_Name) THEN
1291: FND_MESSAGE.SET_NAME('BIS','BIS_PMF_NO_SAME_DO');
1292: FND_MSG_PUB.ADD;
1293: RAISE FND_API.G_EXC_ERROR;
1294: END IF;
1295: IF (c_Master_Level%ISOPEN) THEN
1301: , l_bis_short_name
1302: , l_bis_name;
1303: CLOSE c_Master_Level;
1304: IF (l_bis_dim_level_rec.Master_Level IS NULL) THEN
1305: FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_LEVEL_NAME');
1306: FND_MESSAGE.SET_TOKEN('BSC_LEVEL_NAME', p_Master_Level);
1307: FND_MSG_PUB.ADD;
1308: RAISE FND_API.G_EXC_ERROR;
1309: END IF;
1302: , l_bis_name;
1303: CLOSE c_Master_Level;
1304: IF (l_bis_dim_level_rec.Master_Level IS NULL) THEN
1305: FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_LEVEL_NAME');
1306: FND_MESSAGE.SET_TOKEN('BSC_LEVEL_NAME', p_Master_Level);
1307: FND_MSG_PUB.ADD;
1308: RAISE FND_API.G_EXC_ERROR;
1309: END IF;
1310: IF (l_bis_short_name IS NOT NULL) THEN
1307: FND_MSG_PUB.ADD;
1308: RAISE FND_API.G_EXC_ERROR;
1309: END IF;
1310: IF (l_bis_short_name IS NOT NULL) THEN
1311: FND_MESSAGE.SET_NAME('BIS','BIS_PMF_LOV_NO_MASTER');
1312: FND_MESSAGE.SET_TOKEN('BIS_OBJ_LOV', l_bis_name);
1313: FND_MSG_PUB.ADD;
1314: RAISE FND_API.G_EXC_ERROR;
1315: END IF;
1308: RAISE FND_API.G_EXC_ERROR;
1309: END IF;
1310: IF (l_bis_short_name IS NOT NULL) THEN
1311: FND_MESSAGE.SET_NAME('BIS','BIS_PMF_LOV_NO_MASTER');
1312: FND_MESSAGE.SET_TOKEN('BIS_OBJ_LOV', l_bis_name);
1313: FND_MSG_PUB.ADD;
1314: RAISE FND_API.G_EXC_ERROR;
1315: END IF;
1316: END IF;
1330: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1331: IF (l_error_tbl.COUNT > 0) THEN
1332: x_msg_data := l_error_tbl(l_error_tbl.COUNT).Error_Description;
1333: IF(INSTR(x_msg_data, ' ') = 0 ) THEN
1334: FND_MESSAGE.SET_NAME('BIS',x_msg_data);
1335: FND_MSG_PUB.ADD;
1336: x_msg_data := NULL;
1337: END IF;
1338: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1462: IF(NOT l_flag) THEN
1463: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1464: END IF;
1465: ELSE
1466: FND_MESSAGE.SET_NAME('BSC','BSC_PRE_DIM_OBJ_SOURCE_TYPE');
1467: FND_MSG_PUB.ADD;
1468: RAISE FND_API.G_EXC_ERROR;
1469: END IF;
1470: END IF;
1560: BEGIN
1561: FND_MSG_PUB.Initialize;
1562: x_return_status := FND_API.G_RET_STS_SUCCESS;
1563: IF (p_dim_obj_short_name IS NULL) THEN
1564: FND_MESSAGE.SET_NAME('BSC','BSC_REQ_FIELD_MISSING');
1565: FND_MESSAGE.SET_TOKEN('FIELD_NAME', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DIM_OBJ_SHORT_NAME'), TRUE);
1566: FND_MSG_PUB.ADD;
1567: RAISE FND_API.G_EXC_ERROR;
1568: END IF;
1561: FND_MSG_PUB.Initialize;
1562: x_return_status := FND_API.G_RET_STS_SUCCESS;
1563: IF (p_dim_obj_short_name IS NULL) THEN
1564: FND_MESSAGE.SET_NAME('BSC','BSC_REQ_FIELD_MISSING');
1565: FND_MESSAGE.SET_TOKEN('FIELD_NAME', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DIM_OBJ_SHORT_NAME'), TRUE);
1566: FND_MSG_PUB.ADD;
1567: RAISE FND_API.G_EXC_ERROR;
1568: END IF;
1569: IF (p_dim_short_names IS NOT NULL) THEN
1660: BEGIN
1661: FND_MSG_PUB.Initialize;
1662: x_return_status := FND_API.G_RET_STS_SUCCESS;
1663: IF (p_dim_obj_short_name IS NULL) THEN
1664: FND_MESSAGE.SET_NAME('BSC','BSC_REQ_FIELD_MISSING');
1665: FND_MESSAGE.SET_TOKEN('FIELD_NAME', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DIM_OBJ_SHORT_NAME'), TRUE);
1666: FND_MSG_PUB.ADD;
1667: RAISE FND_API.G_EXC_ERROR;
1668: END IF;
1661: FND_MSG_PUB.Initialize;
1662: x_return_status := FND_API.G_RET_STS_SUCCESS;
1663: IF (p_dim_obj_short_name IS NULL) THEN
1664: FND_MESSAGE.SET_NAME('BSC','BSC_REQ_FIELD_MISSING');
1665: FND_MESSAGE.SET_TOKEN('FIELD_NAME', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DIM_OBJ_SHORT_NAME'), TRUE);
1666: FND_MSG_PUB.ADD;
1667: RAISE FND_API.G_EXC_ERROR;
1668: END IF;
1669: IF (p_dim_short_names IS NOT NULL) THEN
1825: BEGIN
1826: FND_MSG_PUB.Initialize;
1827: x_return_status := FND_API.G_RET_STS_SUCCESS;
1828: IF (p_dim_obj_short_name IS NULL) THEN
1829: FND_MESSAGE.SET_NAME('BSC','BSC_REQ_FIELD_MISSING');
1830: FND_MESSAGE.SET_TOKEN('FIELD_NAME', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DIM_OBJ_SHORT_NAME'), TRUE);
1831: FND_MSG_PUB.ADD;
1832: RAISE FND_API.G_EXC_ERROR;
1833: END IF;
1826: FND_MSG_PUB.Initialize;
1827: x_return_status := FND_API.G_RET_STS_SUCCESS;
1828: IF (p_dim_obj_short_name IS NULL) THEN
1829: FND_MESSAGE.SET_NAME('BSC','BSC_REQ_FIELD_MISSING');
1830: FND_MESSAGE.SET_TOKEN('FIELD_NAME', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DIM_OBJ_SHORT_NAME'), TRUE);
1831: FND_MSG_PUB.ADD;
1832: RAISE FND_API.G_EXC_ERROR;
1833: END IF;
1834:
1867: INTO l_dim_obj_name
1868: FROM BSC_BIS_DIM_OBJS_VL
1869: WHERE SHORT_NAME = p_dim_obj_short_name;
1870:
1871: FND_MESSAGE.SET_NAME('BIS','BIS_DIM_OBJ_RPTASSOC_ERROR');
1872: FND_MESSAGE.SET_TOKEN('DIM_NAME', l_dim_name);
1873: FND_MESSAGE.SET_TOKEN('DIM_OBJ_NAME', l_dim_obj_name);
1874: FND_MESSAGE.SET_TOKEN('REPORTS_ASSOC', l_regions);
1875: FND_MSG_PUB.ADD;
1868: FROM BSC_BIS_DIM_OBJS_VL
1869: WHERE SHORT_NAME = p_dim_obj_short_name;
1870:
1871: FND_MESSAGE.SET_NAME('BIS','BIS_DIM_OBJ_RPTASSOC_ERROR');
1872: FND_MESSAGE.SET_TOKEN('DIM_NAME', l_dim_name);
1873: FND_MESSAGE.SET_TOKEN('DIM_OBJ_NAME', l_dim_obj_name);
1874: FND_MESSAGE.SET_TOKEN('REPORTS_ASSOC', l_regions);
1875: FND_MSG_PUB.ADD;
1876: RAISE FND_API.G_EXC_ERROR;
1869: WHERE SHORT_NAME = p_dim_obj_short_name;
1870:
1871: FND_MESSAGE.SET_NAME('BIS','BIS_DIM_OBJ_RPTASSOC_ERROR');
1872: FND_MESSAGE.SET_TOKEN('DIM_NAME', l_dim_name);
1873: FND_MESSAGE.SET_TOKEN('DIM_OBJ_NAME', l_dim_obj_name);
1874: FND_MESSAGE.SET_TOKEN('REPORTS_ASSOC', l_regions);
1875: FND_MSG_PUB.ADD;
1876: RAISE FND_API.G_EXC_ERROR;
1877: END IF;
1870:
1871: FND_MESSAGE.SET_NAME('BIS','BIS_DIM_OBJ_RPTASSOC_ERROR');
1872: FND_MESSAGE.SET_TOKEN('DIM_NAME', l_dim_name);
1873: FND_MESSAGE.SET_TOKEN('DIM_OBJ_NAME', l_dim_obj_name);
1874: FND_MESSAGE.SET_TOKEN('REPORTS_ASSOC', l_regions);
1875: FND_MSG_PUB.ADD;
1876: RAISE FND_API.G_EXC_ERROR;
1877: END IF;
1878: END IF;
2141: END IF;
2142: l_dim_obj_name := p_dim_obj_short_name;
2143:
2144: IF (l_dim_obj_name IS NULL) THEN
2145: FND_MESSAGE.SET_NAME('BSC','BSC_REQ_FIELD_MISSING');
2146: FND_MESSAGE.SET_TOKEN('FIELD_NAME', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DIM_OBJ_SHORT_NAME'), TRUE);
2147: FND_MSG_PUB.ADD;
2148: RAISE FND_API.G_EXC_ERROR;
2149: END IF;
2142: l_dim_obj_name := p_dim_obj_short_name;
2143:
2144: IF (l_dim_obj_name IS NULL) THEN
2145: FND_MESSAGE.SET_NAME('BSC','BSC_REQ_FIELD_MISSING');
2146: FND_MESSAGE.SET_TOKEN('FIELD_NAME', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DIM_OBJ_SHORT_NAME'), TRUE);
2147: FND_MSG_PUB.ADD;
2148: RAISE FND_API.G_EXC_ERROR;
2149: END IF;
2150: IF (p_data_source IS NULL) THEN
2147: FND_MSG_PUB.ADD;
2148: RAISE FND_API.G_EXC_ERROR;
2149: END IF;
2150: IF (p_data_source IS NULL) THEN
2151: FND_MESSAGE.SET_NAME('BSC','BSC_REQ_FIELD_MISSING');
2152: FND_MESSAGE.SET_TOKEN('FIELD_NAME', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DATA_SOURCE'), TRUE);
2153: FND_MSG_PUB.ADD;
2154: RAISE FND_API.G_EXC_ERROR;
2155: END IF;
2148: RAISE FND_API.G_EXC_ERROR;
2149: END IF;
2150: IF (p_data_source IS NULL) THEN
2151: FND_MESSAGE.SET_NAME('BSC','BSC_REQ_FIELD_MISSING');
2152: FND_MESSAGE.SET_TOKEN('FIELD_NAME', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DATA_SOURCE'), TRUE);
2153: FND_MSG_PUB.ADD;
2154: RAISE FND_API.G_EXC_ERROR;
2155: END IF;
2156: IF ((p_data_source <> 'BSC') AND (p_data_source <> 'PMF')) THEN
2153: FND_MSG_PUB.ADD;
2154: RAISE FND_API.G_EXC_ERROR;
2155: END IF;
2156: IF ((p_data_source <> 'BSC') AND (p_data_source <> 'PMF')) THEN
2157: FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_DATA_SOURCE');
2158: FND_MSG_PUB.ADD;
2159: RAISE FND_API.G_EXC_ERROR;
2160: END IF;
2161: IF (p_data_source = 'BSC') THEN
2161: IF (p_data_source = 'BSC') THEN
2162: l_bsc_dim_obj_rec.Source := 'OLTP';
2163: ELSE
2164: IF ((p_source_type IS NULL) OR ((p_source_type <> 'OLTP') AND (p_source_type <> 'EDW'))) THEN
2165: FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_SOURCE_TYPE');
2166: FND_MSG_PUB.ADD;
2167: RAISE FND_API.G_EXC_ERROR;
2168: ELSE
2169: l_bsc_dim_obj_rec.Source := p_source_type;
2174: SELECT COUNT(1) INTO l_count
2175: FROM BIS_LEVELS
2176: WHERE short_name = l_dim_obj_name;
2177: IF (l_count = 0) THEN
2178: FND_MESSAGE.SET_NAME('BSC','BSC_MUSER_DELETE_MESSAGE');
2179: FND_MESSAGE.SET_TOKEN('TYPE', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DIMENSION_OBJECT'), TRUE);
2180: FND_MSG_PUB.ADD;
2181: RAISE FND_API.G_EXC_ERROR;
2182: END IF;
2175: FROM BIS_LEVELS
2176: WHERE short_name = l_dim_obj_name;
2177: IF (l_count = 0) THEN
2178: FND_MESSAGE.SET_NAME('BSC','BSC_MUSER_DELETE_MESSAGE');
2179: FND_MESSAGE.SET_TOKEN('TYPE', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DIMENSION_OBJECT'), TRUE);
2180: FND_MSG_PUB.ADD;
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);
2190: FND_MSG_PUB.ADD;
2191: RAISE FND_API.G_EXC_ERROR;
2192: END IF;
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);
2190: FND_MSG_PUB.ADD;
2191: RAISE FND_API.G_EXC_ERROR;
2192: END IF;
2193: SELECT dim_level_id
2219: FROM BIS_LEVELS_VL
2220: WHERE UPPER(short_name) <> UPPER(l_dim_obj_name)
2221: AND UPPER(name) = UPPER(p_display_name);
2222: IF (l_count <> 0) THEN
2223: FND_MESSAGE.SET_NAME('BSC','BSC_UNIQUE_NAME_REQUIRED');
2224: FND_MESSAGE.SET_TOKEN('SHORT_NAME', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DISPLAY_NAME'), TRUE);
2225: FND_MESSAGE.SET_TOKEN('NAME_VALUE', p_display_name);
2226: FND_MSG_PUB.ADD;
2227: RAISE FND_API.G_EXC_ERROR;
2220: WHERE UPPER(short_name) <> UPPER(l_dim_obj_name)
2221: AND UPPER(name) = UPPER(p_display_name);
2222: IF (l_count <> 0) THEN
2223: FND_MESSAGE.SET_NAME('BSC','BSC_UNIQUE_NAME_REQUIRED');
2224: FND_MESSAGE.SET_TOKEN('SHORT_NAME', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DISPLAY_NAME'), TRUE);
2225: FND_MESSAGE.SET_TOKEN('NAME_VALUE', p_display_name);
2226: FND_MSG_PUB.ADD;
2227: RAISE FND_API.G_EXC_ERROR;
2228: END IF;
2221: AND UPPER(name) = UPPER(p_display_name);
2222: IF (l_count <> 0) THEN
2223: FND_MESSAGE.SET_NAME('BSC','BSC_UNIQUE_NAME_REQUIRED');
2224: FND_MESSAGE.SET_TOKEN('SHORT_NAME', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DISPLAY_NAME'), TRUE);
2225: FND_MESSAGE.SET_TOKEN('NAME_VALUE', p_display_name);
2226: FND_MSG_PUB.ADD;
2227: RAISE FND_API.G_EXC_ERROR;
2228: END IF;
2229: ELSIF (p_data_source = 'BSC') THEN -- Allow insertion into BSC Data Model, with the same display 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;
2278: RAISE FND_API.G_EXC_ERROR;
2279: END IF;
2280: l_source_table := p_source_table;
2283: END IF;
2284:
2285: IF((LENGTHB(l_source_table) > 27) AND (l_bsc_dim_obj_rec.Bsc_Source = 'BSC')) THEN
2286:
2287: FND_MESSAGE.SET_NAME('BSC','BSC_DIM_OBJ_TABLE_NAME');
2288: FND_MESSAGE.SET_TOKEN('TAB_NAME', l_source_table);
2289: FND_MSG_PUB.ADD;
2290: RAISE FND_API.G_EXC_ERROR;
2291: END IF;
2284:
2285: IF((LENGTHB(l_source_table) > 27) AND (l_bsc_dim_obj_rec.Bsc_Source = 'BSC')) THEN
2286:
2287: FND_MESSAGE.SET_NAME('BSC','BSC_DIM_OBJ_TABLE_NAME');
2288: FND_MESSAGE.SET_TOKEN('TAB_NAME', l_source_table);
2289: FND_MSG_PUB.ADD;
2290: RAISE FND_API.G_EXC_ERROR;
2291: END IF;
2292:
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
2389: FND_MESSAGE.SET_NAME('BSC','BSC_CODE_SIZE_NOT_DECREASED');
2390: FND_MSG_PUB.ADD;
2391: RAISE FND_API.G_EXC_ERROR;
2392: END IF;
2393: IF ((l_bsc_dim_obj_rec.Bsc_Level_User_Key_Size < BSC_BIS_DIM_OBJ_PUB.Dim_Obj_Code_Min_Size) OR
2393: IF ((l_bsc_dim_obj_rec.Bsc_Level_User_Key_Size < BSC_BIS_DIM_OBJ_PUB.Dim_Obj_Code_Min_Size) OR
2394: (l_bsc_dim_obj_rec.Bsc_Level_User_Key_Size > BSC_BIS_DIM_OBJ_PUB.Dim_Obj_Code_Max_Size) OR
2395: (l_bsc_dim_obj_rec.Bsc_Level_Disp_Key_Size < BSC_BIS_DIM_OBJ_PUB.Dim_Obj_Name_Min_Size) OR
2396: (l_bsc_dim_obj_rec.Bsc_Level_Disp_Key_Size > BSC_BIS_DIM_OBJ_PUB.Dim_Obj_Name_Max_Size)) THEN
2397: FND_MESSAGE.SET_NAME('BSC','BSC_CODE_NAME_SIZE');
2398: FND_MSG_PUB.ADD;
2399: RAISE FND_API.G_EXC_ERROR;
2400: END IF;
2401: SELECT COUNT(1) INTO l_count
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
2407: FND_MESSAGE.SET_NAME('BSC','BSC_UNIQUE_NAME_REQUIRED');
2408: FND_MESSAGE.SET_TOKEN('SHORT_NAME', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'ABBREVIATION'), TRUE);
2409: FND_MESSAGE.SET_TOKEN('NAME_VALUE', l_bsc_dim_obj_rec.Bsc_Level_Abbreviation);
2410: FND_MSG_PUB.ADD;
2411: RAISE FND_API.G_EXC_ERROR;
2404: AND dim_level_id <> l_bsc_dim_obj_rec.Bsc_Level_Id
2405: AND Source = 'BSC';
2406: IF (l_count <> 0) THEN
2407: FND_MESSAGE.SET_NAME('BSC','BSC_UNIQUE_NAME_REQUIRED');
2408: FND_MESSAGE.SET_TOKEN('SHORT_NAME', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'ABBREVIATION'), TRUE);
2409: FND_MESSAGE.SET_TOKEN('NAME_VALUE', l_bsc_dim_obj_rec.Bsc_Level_Abbreviation);
2410: FND_MSG_PUB.ADD;
2411: RAISE FND_API.G_EXC_ERROR;
2412: END IF;
2405: AND Source = 'BSC';
2406: IF (l_count <> 0) THEN
2407: FND_MESSAGE.SET_NAME('BSC','BSC_UNIQUE_NAME_REQUIRED');
2408: FND_MESSAGE.SET_TOKEN('SHORT_NAME', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'ABBREVIATION'), TRUE);
2409: FND_MESSAGE.SET_TOKEN('NAME_VALUE', l_bsc_dim_obj_rec.Bsc_Level_Abbreviation);
2410: FND_MSG_PUB.ADD;
2411: RAISE FND_API.G_EXC_ERROR;
2412: END IF;
2413: IF (l_bsc_dim_obj_rec.Bsc_Level_Name IS NULL) THEN
2439: IF (NOT l_flag) THEN
2440: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2441: END IF;
2442: ELSE
2443: FND_MESSAGE.SET_NAME('BSC','BSC_PRE_DIM_OBJ_SOURCE_TYPE');
2444: FND_MSG_PUB.ADD;
2445: RAISE FND_API.G_EXC_ERROR;
2446: END IF;
2447: END IF;
2464: l_bis_dim_level_rec.Level_Values_View_Name := p_source_table;
2465: END IF;
2466: IF ((l_bis_dim_level_rec.Level_Values_View_Name IS NOT NULL) AND
2467: (NOT is_Valid_Identifier(l_bis_dim_level_rec.Level_Values_View_Name))) THEN
2468: FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_SQL_IDENTIFIER');
2469: FND_MESSAGE.SET_TOKEN('SQL_IDENT', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DIM_OBJ_VIEW_NAME'), TRUE);
2470: FND_MESSAGE.SET_TOKEN('SQL_VALUE', l_bis_dim_level_rec.Level_Values_View_Name);
2471: FND_MSG_PUB.ADD;
2472: RAISE FND_API.G_EXC_ERROR;
2465: END IF;
2466: IF ((l_bis_dim_level_rec.Level_Values_View_Name IS NOT NULL) AND
2467: (NOT is_Valid_Identifier(l_bis_dim_level_rec.Level_Values_View_Name))) THEN
2468: FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_SQL_IDENTIFIER');
2469: FND_MESSAGE.SET_TOKEN('SQL_IDENT', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DIM_OBJ_VIEW_NAME'), TRUE);
2470: FND_MESSAGE.SET_TOKEN('SQL_VALUE', l_bis_dim_level_rec.Level_Values_View_Name);
2471: FND_MSG_PUB.ADD;
2472: RAISE FND_API.G_EXC_ERROR;
2473: END IF;
2466: IF ((l_bis_dim_level_rec.Level_Values_View_Name IS NOT NULL) AND
2467: (NOT is_Valid_Identifier(l_bis_dim_level_rec.Level_Values_View_Name))) THEN
2468: FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_SQL_IDENTIFIER');
2469: FND_MESSAGE.SET_TOKEN('SQL_IDENT', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DIM_OBJ_VIEW_NAME'), TRUE);
2470: FND_MESSAGE.SET_TOKEN('SQL_VALUE', l_bis_dim_level_rec.Level_Values_View_Name);
2471: FND_MSG_PUB.ADD;
2472: RAISE FND_API.G_EXC_ERROR;
2473: END IF;
2474: l_bis_dim_level_rec.where_Clause := p_where_clause;
2501: l_bis_dim_level_rec.Attribute_Code := l_bis_dim_level_rec.Dimension_Level_Short_Name;
2502: END IF;
2503: IF ((l_bsc_dim_obj_rec.Bsc_Source = 'PMF') AND (p_Master_Level IS NOT NULL)) THEN
2504: IF (p_Master_Level = l_bis_dim_level_rec.Dimension_Level_Short_Name) THEN
2505: FND_MESSAGE.SET_NAME('BIS','BIS_PMF_NO_SAME_DO');
2506: FND_MSG_PUB.ADD;
2507: RAISE FND_API.G_EXC_ERROR;
2508: END IF;
2509: IF (c_Master_Level%ISOPEN) THEN
2515: , l_bis_short_name
2516: , l_bis_name;
2517: CLOSE c_Master_Level;
2518: IF (l_bis_dim_level_rec.Master_Level IS NULL) THEN
2519: FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_LEVEL_NAME');
2520: FND_MESSAGE.SET_TOKEN('BSC_LEVEL_NAME', p_Master_Level);
2521: FND_MSG_PUB.ADD;
2522: RAISE FND_API.G_EXC_ERROR;
2523: END IF;
2516: , l_bis_name;
2517: CLOSE c_Master_Level;
2518: IF (l_bis_dim_level_rec.Master_Level IS NULL) THEN
2519: FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_LEVEL_NAME');
2520: FND_MESSAGE.SET_TOKEN('BSC_LEVEL_NAME', p_Master_Level);
2521: FND_MSG_PUB.ADD;
2522: RAISE FND_API.G_EXC_ERROR;
2523: END IF;
2524: IF (l_bis_short_name IS NOT NULL) THEN
2521: FND_MSG_PUB.ADD;
2522: RAISE FND_API.G_EXC_ERROR;
2523: END IF;
2524: IF (l_bis_short_name IS NOT NULL) THEN
2525: FND_MESSAGE.SET_NAME('BIS','BIS_PMF_LOV_NO_MASTER');
2526: FND_MESSAGE.SET_TOKEN('BIS_OBJ_LOV', l_bis_name);
2527: FND_MSG_PUB.ADD;
2528: RAISE FND_API.G_EXC_ERROR;
2529: END IF;
2522: RAISE FND_API.G_EXC_ERROR;
2523: END IF;
2524: IF (l_bis_short_name IS NOT NULL) THEN
2525: FND_MESSAGE.SET_NAME('BIS','BIS_PMF_LOV_NO_MASTER');
2526: FND_MESSAGE.SET_TOKEN('BIS_OBJ_LOV', l_bis_name);
2527: FND_MSG_PUB.ADD;
2528: RAISE FND_API.G_EXC_ERROR;
2529: END IF;
2530: SELECT COUNT(1) INTO l_Count
2530: SELECT COUNT(1) INTO l_Count
2531: FROM BIS_LEVELS
2532: WHERE Master_Level = l_bis_dim_level_rec.Dimension_Level_Short_Name;
2533: IF (l_Count <> 0) THEN
2534: FND_MESSAGE.SET_NAME('BIS','BIS_PMF_UPD_NO_MASTER');
2535: FND_MESSAGE.SET_TOKEN('BIS_OBJ_UPD', NVL(l_bis_dim_level_rec.Dimension_Level_Name, l_bis_dim_level_rec.Dimension_Level_Short_Name));
2536: FND_MSG_PUB.ADD;
2537: RAISE FND_API.G_EXC_ERROR;
2538: END IF;
2531: FROM BIS_LEVELS
2532: WHERE Master_Level = l_bis_dim_level_rec.Dimension_Level_Short_Name;
2533: IF (l_Count <> 0) THEN
2534: FND_MESSAGE.SET_NAME('BIS','BIS_PMF_UPD_NO_MASTER');
2535: FND_MESSAGE.SET_TOKEN('BIS_OBJ_UPD', NVL(l_bis_dim_level_rec.Dimension_Level_Name, l_bis_dim_level_rec.Dimension_Level_Short_Name));
2536: FND_MSG_PUB.ADD;
2537: RAISE FND_API.G_EXC_ERROR;
2538: END IF;
2539: END IF;
2553: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2554: IF (l_error_tbl.COUNT > 0) THEN
2555: x_msg_data := l_error_tbl(l_error_tbl.COUNT).Error_Description;
2556: IF(INSTR(x_msg_data, ' ') = 0 ) THEN
2557: FND_MESSAGE.SET_NAME('BIS',x_msg_data);
2558: FND_MSG_PUB.ADD;
2559: x_msg_data := NULL;
2560: END IF;
2561: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3048: FND_MSG_PUB.Initialize;
3049: x_return_status := FND_API.G_RET_STS_SUCCESS;
3050:
3051: IF (p_dim_obj_short_name IS NULL) THEN
3052: FND_MESSAGE.SET_NAME('BSC','BSC_REQ_FIELD_MISSING');
3053: FND_MESSAGE.SET_TOKEN('FIELD_NAME', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DIM_OBJ_SHORT_NAME'), TRUE);
3054: FND_MSG_PUB.ADD;
3055: RAISE FND_API.G_EXC_ERROR;
3056: END IF;
3049: x_return_status := FND_API.G_RET_STS_SUCCESS;
3050:
3051: IF (p_dim_obj_short_name IS NULL) THEN
3052: FND_MESSAGE.SET_NAME('BSC','BSC_REQ_FIELD_MISSING');
3053: FND_MESSAGE.SET_TOKEN('FIELD_NAME', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DIM_OBJ_SHORT_NAME'), TRUE);
3054: FND_MSG_PUB.ADD;
3055: RAISE FND_API.G_EXC_ERROR;
3056: END IF;
3057:
3065: l_dim_level_id := -1;
3066: END IF;
3067: CLOSE cr_bsc_dim_obj;
3068: IF (l_dim_level_id = -1) THEN
3069: FND_MESSAGE.SET_NAME('BSC','BSC_MUSER_DELETE_MESSAGE');
3070: FND_MESSAGE.SET_TOKEN('TYPE', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DIMENSION_OBJECT'), TRUE);
3071: FND_MSG_PUB.ADD;
3072: RAISE FND_API.G_EXC_ERROR;
3073: END IF;
3066: END IF;
3067: CLOSE cr_bsc_dim_obj;
3068: IF (l_dim_level_id = -1) THEN
3069: FND_MESSAGE.SET_NAME('BSC','BSC_MUSER_DELETE_MESSAGE');
3070: FND_MESSAGE.SET_TOKEN('TYPE', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DIMENSION_OBJECT'), TRUE);
3071: FND_MSG_PUB.ADD;
3072: RAISE FND_API.G_EXC_ERROR;
3073: END IF;
3074:
3227: x_return_status := FND_API.G_RET_STS_SUCCESS;
3228: SAVEPOINT DeleteBSCDimObjectPMD;
3229:
3230: IF (p_dim_obj_short_name IS NULL) THEN
3231: FND_MESSAGE.SET_NAME('BSC','BSC_REQ_FIELD_MISSING');
3232: FND_MESSAGE.SET_TOKEN('FIELD_NAME', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DIM_OBJ_SHORT_NAME'), TRUE);
3233: FND_MSG_PUB.ADD;
3234: RAISE FND_API.G_EXC_ERROR;
3235: END IF;
3228: SAVEPOINT DeleteBSCDimObjectPMD;
3229:
3230: IF (p_dim_obj_short_name IS NULL) THEN
3231: FND_MESSAGE.SET_NAME('BSC','BSC_REQ_FIELD_MISSING');
3232: FND_MESSAGE.SET_TOKEN('FIELD_NAME', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DIM_OBJ_SHORT_NAME'), TRUE);
3233: FND_MSG_PUB.ADD;
3234: RAISE FND_API.G_EXC_ERROR;
3235: END IF;
3236: -- CALL PMF'S API
3242: SELECT COUNT(1) INTO l_Count
3243: FROM BIS_LEVELS
3244: WHERE Master_Level = l_bis_dim_level_rec.Dimension_Level_Short_Name;
3245: IF (l_Count <> 0) THEN
3246: FND_MESSAGE.SET_NAME('BIS','BIS_MASTER_DELETE_DIM_OBJ');
3247: FND_MSG_PUB.ADD;
3248: RAISE FND_API.G_EXC_ERROR;
3249: END IF;
3250: BIS_DIMENSION_LEVEL_PUB.Delete_Dimension_Level
3257: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3258: IF (l_error_tbl.COUNT > 0) THEN
3259: x_msg_data := l_error_tbl(l_error_tbl.COUNT).Error_Description;
3260: IF(INSTR(x_msg_data, ' ') = 0 ) THEN
3261: FND_MESSAGE.SET_NAME('BIS',x_msg_data);
3262: FND_MSG_PUB.ADD;
3263: x_msg_data := NULL;
3264: END IF;
3265: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3371: END IF;
3372: END IF;
3373: END IF;
3374: IF (l_delete_count = 0) THEN
3375: FND_MESSAGE.SET_NAME('BSC','BSC_MUSER_DELETE_MESSAGE');
3376: FND_MESSAGE.SET_TOKEN('TYPE', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DIMENSION_OBJECT'), TRUE);
3377: FND_MSG_PUB.ADD;
3378: RAISE FND_API.G_EXC_ERROR;
3379: END IF;
3372: END IF;
3373: END IF;
3374: IF (l_delete_count = 0) THEN
3375: FND_MESSAGE.SET_NAME('BSC','BSC_MUSER_DELETE_MESSAGE');
3376: FND_MESSAGE.SET_TOKEN('TYPE', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DIMENSION_OBJECT'), TRUE);
3377: FND_MSG_PUB.ADD;
3378: RAISE FND_API.G_EXC_ERROR;
3379: END IF;
3380: IF (p_commit = FND_API.G_TRUE) THEN
3454: x_return_status := FND_API.G_RET_STS_SUCCESS;
3455:
3456: l_dim_Group_id := BSC_BIS_DIMENSION_PUB.Get_Bsc_Dimension_ID(BSC_BIS_DIMENSION_PUB.Unassigned_Dim);
3457: IF (p_dim_obj_short_name IS NULL) THEN
3458: FND_MESSAGE.SET_NAME('BSC','BSC_REQ_FIELD_MISSING');
3459: FND_MESSAGE.SET_TOKEN('FIELD_NAME', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DIM_OBJ_SHORT_NAME'), TRUE);
3460: FND_MSG_PUB.ADD;
3461: RAISE FND_API.G_EXC_ERROR;
3462: END IF;
3455:
3456: l_dim_Group_id := BSC_BIS_DIMENSION_PUB.Get_Bsc_Dimension_ID(BSC_BIS_DIMENSION_PUB.Unassigned_Dim);
3457: IF (p_dim_obj_short_name IS NULL) THEN
3458: FND_MESSAGE.SET_NAME('BSC','BSC_REQ_FIELD_MISSING');
3459: FND_MESSAGE.SET_TOKEN('FIELD_NAME', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DIM_OBJ_SHORT_NAME'), TRUE);
3460: FND_MSG_PUB.ADD;
3461: RAISE FND_API.G_EXC_ERROR;
3462: END IF;
3463:
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');
3489: FND_MESSAGE.SET_TOKEN('SHORT_NAME', l_name);
3490: FND_MSG_PUB.ADD;
3491: RAISE FND_API.G_EXC_ERROR;
3492: END IF;
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');
3489: FND_MESSAGE.SET_TOKEN('SHORT_NAME', l_name);
3490: FND_MSG_PUB.ADD;
3491: RAISE FND_API.G_EXC_ERROR;
3492: END IF;
3493:
3496: FROM BSC_SYS_DIM_LEVEL_RELS
3497: WHERE dim_level_id = l_bsc_dim_id
3498: OR parent_dim_level_id = l_bsc_dim_id;
3499: IF (l_count > 0) THEN
3500: FND_MESSAGE.SET_NAME('BSC','BSC_NOT_DELETE_DIM_OBJ_RELS');
3501: FND_MESSAGE.SET_TOKEN('SHORT_NAME', l_name);
3502: FND_MSG_PUB.ADD;
3503: RAISE FND_API.G_EXC_ERROR;
3504: END IF;
3497: WHERE dim_level_id = l_bsc_dim_id
3498: OR parent_dim_level_id = l_bsc_dim_id;
3499: IF (l_count > 0) THEN
3500: FND_MESSAGE.SET_NAME('BSC','BSC_NOT_DELETE_DIM_OBJ_RELS');
3501: FND_MESSAGE.SET_TOKEN('SHORT_NAME', l_name);
3502: FND_MSG_PUB.ADD;
3503: RAISE FND_API.G_EXC_ERROR;
3504: END IF;
3505: x_return_status := FND_API.G_RET_STS_SUCCESS;
3650: FND_MSG_PUB.Initialize;
3651: x_return_status := FND_API.G_RET_STS_SUCCESS;
3652:
3653: IF ((p_Dim_Level_Rec.Bsc_Source IS NULL) OR (p_Dim_Level_Rec.Bsc_Source <> 'BSC')) THEN
3654: FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_DATA_SOURCE');
3655: FND_MSG_PUB.ADD;
3656: RAISE FND_API.G_EXC_ERROR;
3657: END IF;
3658: IF (p_Dim_Level_Rec.Bsc_Level_Id IS NULL) THEN
3655: FND_MSG_PUB.ADD;
3656: RAISE FND_API.G_EXC_ERROR;
3657: END IF;
3658: IF (p_Dim_Level_Rec.Bsc_Level_Id IS NULL) THEN
3659: FND_MESSAGE.SET_NAME('BSC','BSC_REQ_FIELD_MISSING');
3660: FND_MESSAGE.SET_TOKEN('FIELD_NAME', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DIM_OBJ_ID'), TRUE);
3661: FND_MSG_PUB.ADD;
3662: RAISE FND_API.G_EXC_ERROR;
3663: END IF;
3656: RAISE FND_API.G_EXC_ERROR;
3657: END IF;
3658: IF (p_Dim_Level_Rec.Bsc_Level_Id IS NULL) THEN
3659: FND_MESSAGE.SET_NAME('BSC','BSC_REQ_FIELD_MISSING');
3660: FND_MESSAGE.SET_TOKEN('FIELD_NAME', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DIM_OBJ_ID'), TRUE);
3661: FND_MSG_PUB.ADD;
3662: RAISE FND_API.G_EXC_ERROR;
3663: END IF;
3664: IF (p_Dim_Level_Rec.Bsc_Level_Name IS NULL) THEN
3661: FND_MSG_PUB.ADD;
3662: RAISE FND_API.G_EXC_ERROR;
3663: END IF;
3664: IF (p_Dim_Level_Rec.Bsc_Level_Name IS NULL) THEN
3665: FND_MESSAGE.SET_NAME('BSC','BSC_REQ_FIELD_MISSING');
3666: FND_MESSAGE.SET_TOKEN('FIELD_NAME', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DIM_OBJ_TAB_NAME'), TRUE);
3667: FND_MSG_PUB.ADD;
3668: RAISE FND_API.G_EXC_ERROR;
3669: END IF;
3662: RAISE FND_API.G_EXC_ERROR;
3663: END IF;
3664: IF (p_Dim_Level_Rec.Bsc_Level_Name IS NULL) THEN
3665: FND_MESSAGE.SET_NAME('BSC','BSC_REQ_FIELD_MISSING');
3666: FND_MESSAGE.SET_TOKEN('FIELD_NAME', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DIM_OBJ_TAB_NAME'), TRUE);
3667: FND_MSG_PUB.ADD;
3668: RAISE FND_API.G_EXC_ERROR;
3669: END IF;
3670: --Initialize 'l_Dim_Level_Rec' record with the default values
3678: p_Dim_Level_Rec.Bsc_Level_Abbreviation := SUBSTR(REPLACE(p_Dim_Level_Rec.Bsc_Level_Short_Name, ' ', ''), 1, 8);
3679: END IF;
3680: p_Dim_Level_Rec.Bsc_Level_Abbreviation := UPPER(p_Dim_Level_Rec.Bsc_Level_Abbreviation);
3681: IF (NOT is_Valid_AlphaNum(p_Dim_Level_Rec.Bsc_Level_Abbreviation)) THEN
3682: FND_MESSAGE.SET_NAME('BSC','BSC_ALPHA_NUM_REQUIRED');
3683: FND_MESSAGE.SET_TOKEN('VALUE', p_Dim_Level_Rec.Bsc_Level_Abbreviation);
3684: FND_MESSAGE.SET_TOKEN('NAME', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'ABBREVIATION'), TRUE);
3685: FND_MSG_PUB.ADD;
3686: RAISE FND_API.G_EXC_ERROR;
3679: END IF;
3680: p_Dim_Level_Rec.Bsc_Level_Abbreviation := UPPER(p_Dim_Level_Rec.Bsc_Level_Abbreviation);
3681: IF (NOT is_Valid_AlphaNum(p_Dim_Level_Rec.Bsc_Level_Abbreviation)) THEN
3682: FND_MESSAGE.SET_NAME('BSC','BSC_ALPHA_NUM_REQUIRED');
3683: FND_MESSAGE.SET_TOKEN('VALUE', p_Dim_Level_Rec.Bsc_Level_Abbreviation);
3684: FND_MESSAGE.SET_TOKEN('NAME', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'ABBREVIATION'), TRUE);
3685: FND_MSG_PUB.ADD;
3686: RAISE FND_API.G_EXC_ERROR;
3687: END IF;
3680: p_Dim_Level_Rec.Bsc_Level_Abbreviation := UPPER(p_Dim_Level_Rec.Bsc_Level_Abbreviation);
3681: IF (NOT is_Valid_AlphaNum(p_Dim_Level_Rec.Bsc_Level_Abbreviation)) THEN
3682: FND_MESSAGE.SET_NAME('BSC','BSC_ALPHA_NUM_REQUIRED');
3683: FND_MESSAGE.SET_TOKEN('VALUE', p_Dim_Level_Rec.Bsc_Level_Abbreviation);
3684: FND_MESSAGE.SET_TOKEN('NAME', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'ABBREVIATION'), TRUE);
3685: FND_MSG_PUB.ADD;
3686: RAISE FND_API.G_EXC_ERROR;
3687: END IF;
3688: IF(INSTR(p_Dim_Level_Rec.Bsc_Level_Name, 'BSC_D_') = 1) THEN
3690: END IF;
3691: l_Bsc_Level_Name := 'BSC_D_'||UPPER(p_Dim_Level_Rec.Bsc_Level_Name);
3692: IF(LENGTHB(l_Bsc_Level_Name) > 27) THEN
3693:
3694: FND_MESSAGE.SET_NAME('BSC','BSC_DIM_OBJ_TABLE_NAME');
3695: FND_MESSAGE.SET_TOKEN('TAB_NAME', l_Bsc_Level_Name);
3696: FND_MSG_PUB.ADD;
3697: RAISE FND_API.G_EXC_ERROR;
3698: END IF;
3691: l_Bsc_Level_Name := 'BSC_D_'||UPPER(p_Dim_Level_Rec.Bsc_Level_Name);
3692: IF(LENGTHB(l_Bsc_Level_Name) > 27) THEN
3693:
3694: FND_MESSAGE.SET_NAME('BSC','BSC_DIM_OBJ_TABLE_NAME');
3695: FND_MESSAGE.SET_TOKEN('TAB_NAME', l_Bsc_Level_Name);
3696: FND_MSG_PUB.ADD;
3697: RAISE FND_API.G_EXC_ERROR;
3698: END IF;
3699: IF (NOT is_Valid_Identifier(l_Bsc_Level_Name)) THEN
3696: FND_MSG_PUB.ADD;
3697: RAISE FND_API.G_EXC_ERROR;
3698: END IF;
3699: IF (NOT is_Valid_Identifier(l_Bsc_Level_Name)) THEN
3700: FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_SQL_IDENTIFIER');
3701: FND_MESSAGE.SET_TOKEN('SQL_IDENT', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DIM_OBJ_TAB_NAME'), TRUE);
3702: FND_MESSAGE.SET_TOKEN('SQL_VALUE',l_Bsc_Level_Name);
3703: FND_MSG_PUB.ADD;
3704: RAISE FND_API.G_EXC_ERROR;
3697: RAISE FND_API.G_EXC_ERROR;
3698: END IF;
3699: IF (NOT is_Valid_Identifier(l_Bsc_Level_Name)) THEN
3700: FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_SQL_IDENTIFIER');
3701: FND_MESSAGE.SET_TOKEN('SQL_IDENT', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DIM_OBJ_TAB_NAME'), TRUE);
3702: FND_MESSAGE.SET_TOKEN('SQL_VALUE',l_Bsc_Level_Name);
3703: FND_MSG_PUB.ADD;
3704: RAISE FND_API.G_EXC_ERROR;
3705: END IF;
3698: END IF;
3699: IF (NOT is_Valid_Identifier(l_Bsc_Level_Name)) THEN
3700: FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_SQL_IDENTIFIER');
3701: FND_MESSAGE.SET_TOKEN('SQL_IDENT', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DIM_OBJ_TAB_NAME'), TRUE);
3702: FND_MESSAGE.SET_TOKEN('SQL_VALUE',l_Bsc_Level_Name);
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');
3712: FND_MESSAGE.SET_TOKEN('TABLE_NAME', l_Bsc_Level_Name);
3713: FND_MSG_PUB.ADD;
3714: RAISE FND_API.G_EXC_ERROR;
3715: END IF;
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');
3712: FND_MESSAGE.SET_TOKEN('TABLE_NAME', l_Bsc_Level_Name);
3713: FND_MSG_PUB.ADD;
3714: RAISE FND_API.G_EXC_ERROR;
3715: END IF;
3716: p_Dim_Level_Rec.Bsc_Level_Name :=l_Bsc_Level_Name;
3822: IF ((p_Dim_Level_Rec.Bsc_Source IS NULL) OR (p_Dim_Level_Rec.Bsc_Source <> 'PMF')) THEN
3823: RAISE FND_API.G_EXC_ERROR;
3824: END IF;
3825: IF (p_Dim_Level_Rec.Bsc_Level_Short_Name IS NULL) THEN
3826: FND_MESSAGE.SET_NAME('BSC','BSC_REQ_FIELD_MISSING');
3827: FND_MESSAGE.SET_TOKEN('FIELD_NAME', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DIM_OBJ_SHORT_NAME'), TRUE);
3828: FND_MSG_PUB.ADD;
3829: RAISE FND_API.G_EXC_ERROR;
3830: END IF;
3823: RAISE FND_API.G_EXC_ERROR;
3824: END IF;
3825: IF (p_Dim_Level_Rec.Bsc_Level_Short_Name IS NULL) THEN
3826: FND_MESSAGE.SET_NAME('BSC','BSC_REQ_FIELD_MISSING');
3827: FND_MESSAGE.SET_TOKEN('FIELD_NAME', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DIM_OBJ_SHORT_NAME'), TRUE);
3828: FND_MSG_PUB.ADD;
3829: RAISE FND_API.G_EXC_ERROR;
3830: END IF;
3831: IF ((p_Dim_Level_Rec.Source IS NULL) OR ((p_Dim_Level_Rec.Source <> 'OLTP') AND (p_Dim_Level_Rec.Source <> 'EDW'))) THEN
3828: FND_MSG_PUB.ADD;
3829: RAISE FND_API.G_EXC_ERROR;
3830: END IF;
3831: IF ((p_Dim_Level_Rec.Source IS NULL) OR ((p_Dim_Level_Rec.Source <> 'OLTP') AND (p_Dim_Level_Rec.Source <> 'EDW'))) THEN
3832: FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_SOURCE_TYPE');
3833: FND_MSG_PUB.ADD;
3834: RAISE FND_API.G_EXC_ERROR;
3835: END IF;
3836: --Initialize 'l_Dim_Level_Rec' record with the default values
3911: IF (p_Dim_Level_Rec.Bsc_Level_View_Name IS NULL) THEN
3912: p_Dim_Level_Rec.Bsc_Level_View_Name := bsc_utility.get_valid_bsc_master_tbl_name(p_Dim_Level_Rec.Bsc_Level_Short_Name);
3913: END IF;
3914: IF (NOT is_Valid_Identifier(p_Dim_Level_Rec.Bsc_Level_View_Name)) THEN
3915: FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_SQL_IDENTIFIER');
3916: FND_MESSAGE.SET_TOKEN('SQL_IDENT', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DIM_OBJ_VIEW_NAME'), TRUE);
3917: FND_MESSAGE.SET_TOKEN('SQL_VALUE', p_Dim_Level_Rec.Bsc_Level_View_Name);
3918: FND_MSG_PUB.ADD;
3919: RAISE FND_API.G_EXC_ERROR;
3912: p_Dim_Level_Rec.Bsc_Level_View_Name := bsc_utility.get_valid_bsc_master_tbl_name(p_Dim_Level_Rec.Bsc_Level_Short_Name);
3913: END IF;
3914: IF (NOT is_Valid_Identifier(p_Dim_Level_Rec.Bsc_Level_View_Name)) THEN
3915: FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_SQL_IDENTIFIER');
3916: FND_MESSAGE.SET_TOKEN('SQL_IDENT', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DIM_OBJ_VIEW_NAME'), TRUE);
3917: FND_MESSAGE.SET_TOKEN('SQL_VALUE', p_Dim_Level_Rec.Bsc_Level_View_Name);
3918: FND_MSG_PUB.ADD;
3919: RAISE FND_API.G_EXC_ERROR;
3920: END IF;
3913: END IF;
3914: IF (NOT is_Valid_Identifier(p_Dim_Level_Rec.Bsc_Level_View_Name)) THEN
3915: FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_SQL_IDENTIFIER');
3916: FND_MESSAGE.SET_TOKEN('SQL_IDENT', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DIM_OBJ_VIEW_NAME'), TRUE);
3917: FND_MESSAGE.SET_TOKEN('SQL_VALUE', p_Dim_Level_Rec.Bsc_Level_View_Name);
3918: FND_MSG_PUB.ADD;
3919: RAISE FND_API.G_EXC_ERROR;
3920: END IF;
3921:
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');
3928: FND_MESSAGE.SET_TOKEN('TABLE_NAME', p_Dim_Level_Rec.Bsc_Level_View_Name);
3929: FND_MSG_PUB.ADD;
3930: RAISE FND_API.G_EXC_ERROR;
3931: END IF;
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');
3928: FND_MESSAGE.SET_TOKEN('TABLE_NAME', p_Dim_Level_Rec.Bsc_Level_View_Name);
3929: FND_MSG_PUB.ADD;
3930: RAISE FND_API.G_EXC_ERROR;
3931: END IF;
3932:
4074: IF ((p_Dim_Level_Rec.Bsc_Source IS NULL) OR (p_Dim_Level_Rec.Bsc_Source <> 'PMF')) THEN
4075: RAISE FND_API.G_EXC_ERROR;
4076: END IF;
4077: IF (p_Dim_Level_Rec.Bsc_Level_Short_Name IS NULL) THEN
4078: FND_MESSAGE.SET_NAME('BSC','BSC_REQ_FIELD_MISSING');
4079: FND_MESSAGE.SET_TOKEN('FIELD_NAME', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DIM_OBJ_SHORT_NAME'), TRUE);
4080: FND_MSG_PUB.ADD;
4081: RAISE FND_API.G_EXC_ERROR;
4082: END IF;
4075: RAISE FND_API.G_EXC_ERROR;
4076: END IF;
4077: IF (p_Dim_Level_Rec.Bsc_Level_Short_Name IS NULL) THEN
4078: FND_MESSAGE.SET_NAME('BSC','BSC_REQ_FIELD_MISSING');
4079: FND_MESSAGE.SET_TOKEN('FIELD_NAME', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DIM_OBJ_SHORT_NAME'), TRUE);
4080: FND_MSG_PUB.ADD;
4081: RAISE FND_API.G_EXC_ERROR;
4082: END IF;
4083: IF (p_Dim_Level_Rec.Bsc_Level_Pk_Key IS NULL) THEN
4080: FND_MSG_PUB.ADD;
4081: RAISE FND_API.G_EXC_ERROR;
4082: END IF;
4083: IF (p_Dim_Level_Rec.Bsc_Level_Pk_Key IS NULL) THEN
4084: FND_MESSAGE.SET_NAME('BSC','BSC_REQ_FIELD_MISSING');
4085: FND_MESSAGE.SET_TOKEN('FIELD_NAME', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DIM_OBJ_PK_COL'), TRUE);
4086: FND_MSG_PUB.ADD;
4087: RAISE FND_API.G_EXC_ERROR;
4088: END IF;
4081: RAISE FND_API.G_EXC_ERROR;
4082: END IF;
4083: IF (p_Dim_Level_Rec.Bsc_Level_Pk_Key IS NULL) THEN
4084: FND_MESSAGE.SET_NAME('BSC','BSC_REQ_FIELD_MISSING');
4085: FND_MESSAGE.SET_TOKEN('FIELD_NAME', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DIM_OBJ_PK_COL'), TRUE);
4086: FND_MSG_PUB.ADD;
4087: RAISE FND_API.G_EXC_ERROR;
4088: END IF;
4089: IF (p_Dim_Level_Rec.Bsc_Level_Name_Column IS NULL) THEN
4086: FND_MSG_PUB.ADD;
4087: RAISE FND_API.G_EXC_ERROR;
4088: END IF;
4089: IF (p_Dim_Level_Rec.Bsc_Level_Name_Column IS NULL) THEN
4090: FND_MESSAGE.SET_NAME('BSC','BSC_REQ_FIELD_MISSING');
4091: FND_MESSAGE.SET_TOKEN('FIELD_NAME', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DIM_OBJ_COL'), TRUE);
4092: FND_MSG_PUB.ADD;
4093: RAISE FND_API.G_EXC_ERROR;
4094: END IF;
4087: RAISE FND_API.G_EXC_ERROR;
4088: END IF;
4089: IF (p_Dim_Level_Rec.Bsc_Level_Name_Column IS NULL) THEN
4090: FND_MESSAGE.SET_NAME('BSC','BSC_REQ_FIELD_MISSING');
4091: FND_MESSAGE.SET_TOKEN('FIELD_NAME', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DIM_OBJ_COL'), TRUE);
4092: FND_MSG_PUB.ADD;
4093: RAISE FND_API.G_EXC_ERROR;
4094: END IF;
4095: IF (p_Dim_Level_Rec.Bsc_Level_View_Name IS NULL) THEN
4092: FND_MSG_PUB.ADD;
4093: RAISE FND_API.G_EXC_ERROR;
4094: END IF;
4095: IF (p_Dim_Level_Rec.Bsc_Level_View_Name IS NULL) THEN
4096: FND_MESSAGE.SET_NAME('BSC','BSC_REQ_FIELD_MISSING');
4097: FND_MESSAGE.SET_TOKEN('FIELD_NAME', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DIM_OBJ_VIEW_NAME'), TRUE);
4098: FND_MSG_PUB.ADD;
4099: RAISE FND_API.G_EXC_ERROR;
4100: END IF;
4093: RAISE FND_API.G_EXC_ERROR;
4094: END IF;
4095: IF (p_Dim_Level_Rec.Bsc_Level_View_Name IS NULL) THEN
4096: FND_MESSAGE.SET_NAME('BSC','BSC_REQ_FIELD_MISSING');
4097: FND_MESSAGE.SET_TOKEN('FIELD_NAME', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DIM_OBJ_VIEW_NAME'), TRUE);
4098: FND_MSG_PUB.ADD;
4099: RAISE FND_API.G_EXC_ERROR;
4100: END IF;
4101: IF ((p_Dim_Level_Rec.Bsc_Level_Name IS NOT NULL) AND (is_Valid_Identifier(p_Dim_Level_Rec.Bsc_Level_Name))) THEN
4252: SAVEPOINT CreateBSCMasTabsPMD;
4253:
4254: BSC_APPS.Init_Bsc_Apps;
4255: IF ((p_Dim_Level_Rec.Bsc_Source IS NULL) OR (p_Dim_Level_Rec.Bsc_Source <> 'BSC')) THEN
4256: FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_DATA_SOURCE');
4257: FND_MSG_PUB.ADD;
4258: RAISE FND_API.G_EXC_ERROR;
4259: END IF;
4260: IF (p_Dim_Level_Rec.Bsc_Level_Id IS NULL) THEN
4257: FND_MSG_PUB.ADD;
4258: RAISE FND_API.G_EXC_ERROR;
4259: END IF;
4260: IF (p_Dim_Level_Rec.Bsc_Level_Id IS NULL) THEN
4261: FND_MESSAGE.SET_NAME('BSC','BSC_REQ_FIELD_MISSING');
4262: FND_MESSAGE.SET_TOKEN('FIELD_NAME', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DIM_OBJ_ID'), TRUE);
4263: FND_MSG_PUB.ADD;
4264: RAISE FND_API.G_EXC_ERROR;
4265: END IF;
4258: RAISE FND_API.G_EXC_ERROR;
4259: END IF;
4260: IF (p_Dim_Level_Rec.Bsc_Level_Id IS NULL) THEN
4261: FND_MESSAGE.SET_NAME('BSC','BSC_REQ_FIELD_MISSING');
4262: FND_MESSAGE.SET_TOKEN('FIELD_NAME', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DIM_OBJ_ID'), TRUE);
4263: FND_MSG_PUB.ADD;
4264: RAISE FND_API.G_EXC_ERROR;
4265: END IF;
4266: IF (p_Dim_Level_Rec.Bsc_Level_Name IS NULL) THEN
4263: FND_MSG_PUB.ADD;
4264: RAISE FND_API.G_EXC_ERROR;
4265: END IF;
4266: IF (p_Dim_Level_Rec.Bsc_Level_Name IS NULL) THEN
4267: FND_MESSAGE.SET_NAME('BSC','BSC_REQ_FIELD_MISSING');
4268: FND_MESSAGE.SET_TOKEN('FIELD_NAME', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DIM_OBJ_TAB_NAME'), TRUE);
4269: FND_MSG_PUB.ADD;
4270: RAISE FND_API.G_EXC_ERROR;
4271: END IF;
4264: RAISE FND_API.G_EXC_ERROR;
4265: END IF;
4266: IF (p_Dim_Level_Rec.Bsc_Level_Name IS NULL) THEN
4267: FND_MESSAGE.SET_NAME('BSC','BSC_REQ_FIELD_MISSING');
4268: FND_MESSAGE.SET_TOKEN('FIELD_NAME', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DIM_OBJ_TAB_NAME'), TRUE);
4269: FND_MSG_PUB.ADD;
4270: RAISE FND_API.G_EXC_ERROR;
4271: END IF;
4272: IF (p_Dim_Level_Rec.Bsc_Level_View_Name IS NULL) THEN
4269: FND_MSG_PUB.ADD;
4270: RAISE FND_API.G_EXC_ERROR;
4271: END IF;
4272: IF (p_Dim_Level_Rec.Bsc_Level_View_Name IS NULL) THEN
4273: FND_MESSAGE.SET_NAME('BSC','BSC_REQ_FIELD_MISSING');
4274: FND_MESSAGE.SET_TOKEN('FIELD_NAME', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DIM_OBJ_VIEW_NAME'), TRUE);
4275: FND_MSG_PUB.ADD;
4276: RAISE FND_API.G_EXC_ERROR;
4277: END IF;
4270: RAISE FND_API.G_EXC_ERROR;
4271: END IF;
4272: IF (p_Dim_Level_Rec.Bsc_Level_View_Name IS NULL) THEN
4273: FND_MESSAGE.SET_NAME('BSC','BSC_REQ_FIELD_MISSING');
4274: FND_MESSAGE.SET_TOKEN('FIELD_NAME', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DIM_OBJ_VIEW_NAME'), TRUE);
4275: FND_MSG_PUB.ADD;
4276: RAISE FND_API.G_EXC_ERROR;
4277: END IF;
4278: IF (p_Dim_Level_Rec.Bsc_Level_Abbreviation IS NULL) THEN
4284: END IF;
4285: l_master_table := 'BSC_D_'||UPPER(p_Dim_Level_Rec.Bsc_Level_Name);
4286:
4287: IF (NOT is_Valid_Identifier(l_master_table)) THEN
4288: FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_SQL_IDENTIFIER');
4289: FND_MESSAGE.SET_TOKEN('SQL_IDENT', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DIM_OBJ_TAB_NAME'), TRUE);
4290: FND_MESSAGE.SET_TOKEN('SQL_VALUE', l_master_table);
4291: FND_MSG_PUB.ADD;
4292: RAISE FND_API.G_EXC_ERROR;
4285: l_master_table := 'BSC_D_'||UPPER(p_Dim_Level_Rec.Bsc_Level_Name);
4286:
4287: IF (NOT is_Valid_Identifier(l_master_table)) THEN
4288: FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_SQL_IDENTIFIER');
4289: FND_MESSAGE.SET_TOKEN('SQL_IDENT', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DIM_OBJ_TAB_NAME'), TRUE);
4290: FND_MESSAGE.SET_TOKEN('SQL_VALUE', l_master_table);
4291: FND_MSG_PUB.ADD;
4292: RAISE FND_API.G_EXC_ERROR;
4293: END IF;
4286:
4287: IF (NOT is_Valid_Identifier(l_master_table)) THEN
4288: FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_SQL_IDENTIFIER');
4289: FND_MESSAGE.SET_TOKEN('SQL_IDENT', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DIM_OBJ_TAB_NAME'), TRUE);
4290: FND_MESSAGE.SET_TOKEN('SQL_VALUE', l_master_table);
4291: FND_MSG_PUB.ADD;
4292: RAISE FND_API.G_EXC_ERROR;
4293: END IF;
4294:
4309: AND LEVEL_TABLE_NAME = l_master_table
4310: AND SOURCE <> 'PMF';
4311:
4312: IF (l_count <> 0) THEN
4313: FND_MESSAGE.SET_NAME('BSC','BSC_D_TABLE_NAME_EXIST');
4314: FND_MESSAGE.SET_TOKEN('TABLE_NAME', p_Dim_Level_Rec.Bsc_Level_Name);
4315: FND_MSG_PUB.ADD;
4316: RAISE FND_API.G_EXC_ERROR;
4317: END IF;
4310: AND SOURCE <> 'PMF';
4311:
4312: IF (l_count <> 0) THEN
4313: FND_MESSAGE.SET_NAME('BSC','BSC_D_TABLE_NAME_EXIST');
4314: FND_MESSAGE.SET_TOKEN('TABLE_NAME', p_Dim_Level_Rec.Bsc_Level_Name);
4315: FND_MSG_PUB.ADD;
4316: RAISE FND_API.G_EXC_ERROR;
4317: END IF;
4318: --Need to initiliaze in order to Do_DDL works fine
4571: SAVEPOINT AlterBSCMasTabsPMD;
4572:
4573: BSC_APPS.Init_Bsc_Apps;
4574: IF ((p_Dim_Level_Rec.Bsc_Source IS NULL) OR (p_Dim_Level_Rec.Bsc_Source <> 'BSC')) THEN
4575: FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_DATA_SOURCE');
4576: FND_MSG_PUB.ADD;
4577: RAISE FND_API.G_EXC_ERROR;
4578: END IF;
4579: IF (p_Dim_Level_Rec.Bsc_Level_Id IS NULL) THEN
4576: FND_MSG_PUB.ADD;
4577: RAISE FND_API.G_EXC_ERROR;
4578: END IF;
4579: IF (p_Dim_Level_Rec.Bsc_Level_Id IS NULL) THEN
4580: FND_MESSAGE.SET_NAME('BSC','BSC_REQ_FIELD_MISSING');
4581: FND_MESSAGE.SET_TOKEN('FIELD_NAME', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DIM_OBJ_ID'), TRUE);
4582: FND_MSG_PUB.ADD;
4583: RAISE FND_API.G_EXC_ERROR;
4584: END IF;
4577: RAISE FND_API.G_EXC_ERROR;
4578: END IF;
4579: IF (p_Dim_Level_Rec.Bsc_Level_Id IS NULL) THEN
4580: FND_MESSAGE.SET_NAME('BSC','BSC_REQ_FIELD_MISSING');
4581: FND_MESSAGE.SET_TOKEN('FIELD_NAME', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DIM_OBJ_ID'), TRUE);
4582: FND_MSG_PUB.ADD;
4583: RAISE FND_API.G_EXC_ERROR;
4584: END IF;
4585: IF (p_Dim_Level_Rec.Bsc_Level_Name IS NULL) THEN
4582: FND_MSG_PUB.ADD;
4583: RAISE FND_API.G_EXC_ERROR;
4584: END IF;
4585: IF (p_Dim_Level_Rec.Bsc_Level_Name IS NULL) THEN
4586: FND_MESSAGE.SET_NAME('BSC','BSC_REQ_FIELD_MISSING');
4587: FND_MESSAGE.SET_TOKEN('FIELD_NAME', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DIM_OBJ_TAB_NAME'), TRUE);
4588: FND_MSG_PUB.ADD;
4589: RAISE FND_API.G_EXC_ERROR;
4590: END IF;
4583: RAISE FND_API.G_EXC_ERROR;
4584: END IF;
4585: IF (p_Dim_Level_Rec.Bsc_Level_Name IS NULL) THEN
4586: FND_MESSAGE.SET_NAME('BSC','BSC_REQ_FIELD_MISSING');
4587: FND_MESSAGE.SET_TOKEN('FIELD_NAME', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DIM_OBJ_TAB_NAME'), TRUE);
4588: FND_MSG_PUB.ADD;
4589: RAISE FND_API.G_EXC_ERROR;
4590: END IF;
4591: IF (p_Dim_Level_Rec.Bsc_Level_View_Name IS NULL) THEN
4588: FND_MSG_PUB.ADD;
4589: RAISE FND_API.G_EXC_ERROR;
4590: END IF;
4591: IF (p_Dim_Level_Rec.Bsc_Level_View_Name IS NULL) THEN
4592: FND_MESSAGE.SET_NAME('BSC','BSC_REQ_FIELD_MISSING');
4593: FND_MESSAGE.SET_TOKEN('FIELD_NAME', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DIM_OBJ_VIEW_NAME'), TRUE);
4594: FND_MSG_PUB.ADD;
4595: RAISE FND_API.G_EXC_ERROR;
4596: END IF;
4589: RAISE FND_API.G_EXC_ERROR;
4590: END IF;
4591: IF (p_Dim_Level_Rec.Bsc_Level_View_Name IS NULL) THEN
4592: FND_MESSAGE.SET_NAME('BSC','BSC_REQ_FIELD_MISSING');
4593: FND_MESSAGE.SET_TOKEN('FIELD_NAME', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DIM_OBJ_VIEW_NAME'), TRUE);
4594: FND_MSG_PUB.ADD;
4595: RAISE FND_API.G_EXC_ERROR;
4596: END IF;
4597: l_master_table := UPPER(p_Dim_Level_Rec.Bsc_Level_Name);
4596: END IF;
4597: l_master_table := UPPER(p_Dim_Level_Rec.Bsc_Level_Name);
4598:
4599: IF (NOT is_Valid_Identifier(l_master_table)) THEN
4600: FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_SQL_IDENTIFIER');
4601: FND_MESSAGE.SET_TOKEN('SQL_IDENT', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DIM_OBJ_TAB_NAME'), TRUE);
4602: FND_MESSAGE.SET_TOKEN('SQL_VALUE', l_master_table);
4603: FND_MSG_PUB.ADD;
4604: RAISE FND_API.G_EXC_ERROR;
4597: l_master_table := UPPER(p_Dim_Level_Rec.Bsc_Level_Name);
4598:
4599: IF (NOT is_Valid_Identifier(l_master_table)) THEN
4600: FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_SQL_IDENTIFIER');
4601: FND_MESSAGE.SET_TOKEN('SQL_IDENT', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DIM_OBJ_TAB_NAME'), TRUE);
4602: FND_MESSAGE.SET_TOKEN('SQL_VALUE', l_master_table);
4603: FND_MSG_PUB.ADD;
4604: RAISE FND_API.G_EXC_ERROR;
4605: END IF;
4598:
4599: IF (NOT is_Valid_Identifier(l_master_table)) THEN
4600: FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_SQL_IDENTIFIER');
4601: FND_MESSAGE.SET_TOKEN('SQL_IDENT', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DIM_OBJ_TAB_NAME'), TRUE);
4602: FND_MESSAGE.SET_TOKEN('SQL_VALUE', l_master_table);
4603: FND_MSG_PUB.ADD;
4604: RAISE FND_API.G_EXC_ERROR;
4605: END IF;
4606:
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');
4616: FND_MESSAGE.SET_TOKEN('TABLE_NAME', p_Dim_Level_Rec.Bsc_Level_Name);
4617: FND_MSG_PUB.ADD;
4618: RAISE FND_API.G_EXC_ERROR;
4619: END IF;
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');
4616: FND_MESSAGE.SET_TOKEN('TABLE_NAME', p_Dim_Level_Rec.Bsc_Level_Name);
4617: FND_MSG_PUB.ADD;
4618: RAISE FND_API.G_EXC_ERROR;
4619: END IF;
4620: --check if old master tables, views, input table exists in the system.
4645: IF (NOT l_flag) THEN
4646: --The following part of the code will only be executed if
4647: --master table, input table or view does not exists.
4648: --This secition will be called in the case of data corruption only.
4649: FND_MESSAGE.SET_NAME('BSC','BSC_DB_ERROR');
4650: FND_MSG_PUB.ADD;
4651: RAISE FND_API.G_EXC_ERROR;
4652: ELSE
4653: --check if Level_Pk_Col is differnt, is so update table BSC_DB_TABLES_COLS
4665: IF ((p_Dim_Level_Rec.Bsc_Level_User_Key_Size <> p_Dim_Level_Rec_Old.Bsc_Level_User_Key_Size)
4666: OR (p_Dim_Level_Rec.Bsc_Level_Disp_Key_Size <> p_Dim_Level_Rec_Old.Bsc_Level_Disp_Key_Size)) THEN
4667: IF ((p_Dim_Level_Rec.Bsc_Level_User_Key_Size < p_Dim_Level_Rec_Old.Bsc_Level_User_Key_Size)
4668: OR (p_Dim_Level_Rec.Bsc_Level_Disp_Key_Size < p_Dim_Level_Rec_Old.Bsc_Level_Disp_Key_Size)) THEN
4669: FND_MESSAGE.SET_NAME('BSC','BSC_CODE_SIZE_NOT_DECREASED');
4670: FND_MSG_PUB.ADD;
4671: RAISE FND_API.G_EXC_ERROR;
4672: END IF;
4673: l_Create_View := TRUE;
5703: END IF;
5704: END LOOP;
5705:
5706: IF (l_kpi_names IS NOT NULL) THEN
5707: FND_MESSAGE.SET_NAME('BSC','BSC_PMD_KPI_STRUCT_INVALID');
5708: FND_MESSAGE.SET_TOKEN('INDICATORS', l_kpi_names);
5709: FND_MSG_PUB.ADD;
5710: RAISE FND_API.G_EXC_ERROR;
5711: END IF;
5704: END LOOP;
5705:
5706: IF (l_kpi_names IS NOT NULL) THEN
5707: FND_MESSAGE.SET_NAME('BSC','BSC_PMD_KPI_STRUCT_INVALID');
5708: FND_MESSAGE.SET_TOKEN('INDICATORS', l_kpi_names);
5709: FND_MSG_PUB.ADD;
5710: RAISE FND_API.G_EXC_ERROR;
5711: END IF;
5712: /***********************************************************
5725: , p_dim_short_names => p_dim_short_names
5726: , x_obj_names => l_kpi_names
5727: ))THEN
5728: IF(l_kpi_names IS NOT NULL)THEN
5729: FND_MESSAGE.SET_NAME('BSC','BSC_CHANG_OBJ_DISP_FORMAT');
5730: FND_MESSAGE.SET_TOKEN('OBJS', l_kpi_names);
5731: FND_MSG_PUB.ADD;
5732: RAISE FND_API.G_EXC_ERROR;
5733: END IF;
5726: , x_obj_names => l_kpi_names
5727: ))THEN
5728: IF(l_kpi_names IS NOT NULL)THEN
5729: FND_MESSAGE.SET_NAME('BSC','BSC_CHANG_OBJ_DISP_FORMAT');
5730: FND_MESSAGE.SET_TOKEN('OBJS', l_kpi_names);
5731: FND_MSG_PUB.ADD;
5732: RAISE FND_API.G_EXC_ERROR;
5733: END IF;
5734: END IF;
5848: l_is_found := TRUE;
5849: END IF;
5850: END LOOP;
5851: IF((l_is_found = FALSE) AND (dim_objs_array.COUNT >= BSC_BIS_KPI_MEAS_PUB.Config_Limit_Dim)) THEN
5852: FND_MESSAGE.SET_NAME('BSC','BSC_PMD_IMPACT_KPI_SPACE');
5853: FND_MESSAGE.SET_TOKEN('CONTINUE', BSC_APPS.Get_Lookup_Value('BSC_UI_KPIDESIGNER', 'YES'), TRUE);
5854: FND_MESSAGE.SET_TOKEN('CANCEL', BSC_APPS.Get_Lookup_Value('BSC_UI_KPIDESIGNER', 'NO'), TRUE);
5855: FND_MSG_PUB.ADD;
5856: RAISE FND_API.G_EXC_ERROR;
5849: END IF;
5850: END LOOP;
5851: IF((l_is_found = FALSE) AND (dim_objs_array.COUNT >= BSC_BIS_KPI_MEAS_PUB.Config_Limit_Dim)) THEN
5852: FND_MESSAGE.SET_NAME('BSC','BSC_PMD_IMPACT_KPI_SPACE');
5853: FND_MESSAGE.SET_TOKEN('CONTINUE', BSC_APPS.Get_Lookup_Value('BSC_UI_KPIDESIGNER', 'YES'), TRUE);
5854: FND_MESSAGE.SET_TOKEN('CANCEL', BSC_APPS.Get_Lookup_Value('BSC_UI_KPIDESIGNER', 'NO'), TRUE);
5855: FND_MSG_PUB.ADD;
5856: RAISE FND_API.G_EXC_ERROR;
5857: END IF;
5850: END LOOP;
5851: IF((l_is_found = FALSE) AND (dim_objs_array.COUNT >= BSC_BIS_KPI_MEAS_PUB.Config_Limit_Dim)) THEN
5852: FND_MESSAGE.SET_NAME('BSC','BSC_PMD_IMPACT_KPI_SPACE');
5853: FND_MESSAGE.SET_TOKEN('CONTINUE', BSC_APPS.Get_Lookup_Value('BSC_UI_KPIDESIGNER', 'YES'), TRUE);
5854: FND_MESSAGE.SET_TOKEN('CANCEL', BSC_APPS.Get_Lookup_Value('BSC_UI_KPIDESIGNER', 'NO'), TRUE);
5855: FND_MSG_PUB.ADD;
5856: RAISE FND_API.G_EXC_ERROR;
5857: END IF;
5858: END LOOP;
6303: END IF;
6304: END LOOP;
6305: CLOSE cr_kpi_dimset_dimobj;
6306: IF(l_is_kpi_affected) THEN
6307: FND_MESSAGE.SET_NAME('BSC','BSC_IND_DIMOBJ_LIMIT');
6308: FND_MESSAGE.SET_TOKEN('NAME_LIST',l_affected_kpis);
6309: FND_MSG_PUB.ADD;
6310: RAISE FND_API.G_EXC_ERROR;
6311: END IF;
6304: END LOOP;
6305: CLOSE cr_kpi_dimset_dimobj;
6306: IF(l_is_kpi_affected) THEN
6307: FND_MESSAGE.SET_NAME('BSC','BSC_IND_DIMOBJ_LIMIT');
6308: FND_MESSAGE.SET_TOKEN('NAME_LIST',l_affected_kpis);
6309: FND_MSG_PUB.ADD;
6310: RAISE FND_API.G_EXC_ERROR;
6311: END IF;
6312:
6778: AND GRP.dim_group_id = l_dim_grp_id
6779: AND SYS.SHORT_NAME = p_dim_obj_short_name;
6780:
6781: IF (l_count >= bsc_utility.MAX_DIM_IN_DIM_SET AND l_exist = 0 AND p_dim_obj_source = 'BSC' AND CTYPE.SOURCE = 'BSC') THEN
6782: FND_MESSAGE.SET_NAME('BSC','BSC_DIM_SHUTTLE_OVERFLOW');
6783: FND_MSG_PUB.ADD;
6784: return TRUE;
6785: END IF;
6786:
6803: END IF;
6804: END LOOP;
6805:
6806: IF (l_true) THEN
6807: FND_MESSAGE.SET_NAME('BSC','BSC_DIM_DIMOBJ_MIXED_TYPE');
6808: FND_MESSAGE.SET_TOKEN('DIMENSION', l_dim_name);
6809: FND_MESSAGE.SET_TOKEN('DIM_OBJECT', p_dim_obj_name);
6810: FND_MSG_PUB.ADD;
6811: return l_true;
6804: END LOOP;
6805:
6806: IF (l_true) THEN
6807: FND_MESSAGE.SET_NAME('BSC','BSC_DIM_DIMOBJ_MIXED_TYPE');
6808: FND_MESSAGE.SET_TOKEN('DIMENSION', l_dim_name);
6809: FND_MESSAGE.SET_TOKEN('DIM_OBJECT', p_dim_obj_name);
6810: FND_MSG_PUB.ADD;
6811: return l_true;
6812: END IF;
6805:
6806: IF (l_true) THEN
6807: FND_MESSAGE.SET_NAME('BSC','BSC_DIM_DIMOBJ_MIXED_TYPE');
6808: FND_MESSAGE.SET_TOKEN('DIMENSION', l_dim_name);
6809: FND_MESSAGE.SET_TOKEN('DIM_OBJECT', p_dim_obj_name);
6810: FND_MSG_PUB.ADD;
6811: return l_true;
6812: END IF;
6813: END IF;
7086: END IF;
7087: l_Level_Pk_Col := 'BSC_'||l_Level_Pk_Col;
7088: END IF;
7089: ELSIF (NOT is_Valid_Identifier(l_Level_Pk_Col)) THEN
7090: FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_SQL_IDENTIFIER');
7091: FND_MESSAGE.SET_TOKEN('SQL_IDENT', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DIM_OBJ_PK_COL'), TRUE);
7092: FND_MESSAGE.SET_TOKEN('SQL_VALUE', l_Level_Pk_Col);
7093: FND_MSG_PUB.ADD;
7094: RAISE FND_API.G_EXC_ERROR;
7087: l_Level_Pk_Col := 'BSC_'||l_Level_Pk_Col;
7088: END IF;
7089: ELSIF (NOT is_Valid_Identifier(l_Level_Pk_Col)) THEN
7090: FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_SQL_IDENTIFIER');
7091: FND_MESSAGE.SET_TOKEN('SQL_IDENT', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DIM_OBJ_PK_COL'), TRUE);
7092: FND_MESSAGE.SET_TOKEN('SQL_VALUE', l_Level_Pk_Col);
7093: FND_MSG_PUB.ADD;
7094: RAISE FND_API.G_EXC_ERROR;
7095: END IF;
7088: END IF;
7089: ELSIF (NOT is_Valid_Identifier(l_Level_Pk_Col)) THEN
7090: FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_SQL_IDENTIFIER');
7091: FND_MESSAGE.SET_TOKEN('SQL_IDENT', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DIM_OBJ_PK_COL'), TRUE);
7092: FND_MESSAGE.SET_TOKEN('SQL_VALUE', l_Level_Pk_Col);
7093: FND_MSG_PUB.ADD;
7094: RAISE FND_API.G_EXC_ERROR;
7095: END IF;
7096: IF(is_SQL_Key_Word(l_Level_Pk_Col)) THEN
7191: ,x_Msg_Data => x_Msg_Data
7192: );
7193:
7194: IF (x_Return_Status <> FND_API.G_RET_STS_SUCCESS AND x_Msg_Data IS NOT NULL) THEN
7195: FND_MESSAGE.SET_NAME('BSC',x_Msg_Data);
7196: FND_MESSAGE.SET_TOKEN('DIM_OBJ', l_name, TRUE);
7197: FND_MSG_PUB.ADD;
7198: RAISE FND_API.G_EXC_ERROR;
7199: END IF;
7192: );
7193:
7194: IF (x_Return_Status <> FND_API.G_RET_STS_SUCCESS AND x_Msg_Data IS NOT NULL) THEN
7195: FND_MESSAGE.SET_NAME('BSC',x_Msg_Data);
7196: FND_MESSAGE.SET_TOKEN('DIM_OBJ', l_name, TRUE);
7197: FND_MSG_PUB.ADD;
7198: RAISE FND_API.G_EXC_ERROR;
7199: END IF;
7200: END IF;