[Home] [Help]
591: , x_msg_data OUT NOCOPY VARCHAR2
592: ) IS
593: BEGIN
594: SAVEPOINT CreateBSCDimObjPMD;
595: FND_MSG_PUB.Initialize;
596: x_return_status := FND_API.G_RET_STS_SUCCESS;
597:
598: Create_Dim_Object
599: ( p_commit => p_commit
631: EXCEPTION
632: WHEN FND_API.G_EXC_ERROR THEN
633: ROLLBACK TO CreateBSCDimObjPMD;
634: IF (x_msg_data IS NULL) THEN
635: FND_MSG_PUB.Count_And_Get
636: ( p_encoded => FND_API.G_FALSE
637: , p_count => x_msg_count
638: , p_data => x_msg_data
639: );
641: x_return_status := FND_API.G_RET_STS_ERROR;
642: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
643: ROLLBACK TO CreateBSCDimObjPMD;
644: IF (x_msg_data IS NULL) THEN
645: FND_MSG_PUB.Count_And_Get
646: ( p_encoded => FND_API.G_FALSE
647: , p_count => x_msg_count
648: , p_data => x_msg_data
649: );
709: l_next_dim_grp_id NUMBER;
710: l_is_default_short_name VARCHAR2(1);
711: BEGIN
712: SAVEPOINT CreateBSCDimObjPMD;
713: FND_MSG_PUB.Initialize;
714: x_return_status := FND_API.G_RET_STS_SUCCESS;
715:
716: l_is_default_short_name := p_is_default_short_name;
717:
788: EXCEPTION
789: WHEN FND_API.G_EXC_ERROR THEN
790: ROLLBACK TO CreateBSCDimObjPMD;
791: IF (x_msg_data IS NULL) THEN
792: FND_MSG_PUB.Count_And_Get
793: ( p_encoded => FND_API.G_FALSE
794: , p_count => x_msg_count
795: , p_data => x_msg_data
796: );
798: x_return_status := FND_API.G_RET_STS_ERROR;
799: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
800: ROLLBACK TO CreateBSCDimObjPMD;
801: IF (x_msg_data IS NULL) THEN
802: FND_MSG_PUB.Count_And_Get
803: ( p_encoded => FND_API.G_FALSE
804: , p_count => x_msg_count
805: , p_data => x_msg_data
806: );
915: WHERE Short_Name = p_Master_Level;
916: BEGIN
917:
918: SAVEPOINT CreateBSCDimObjPMD;
919: FND_MSG_PUB.Initialize;
920: x_return_status := FND_API.G_RET_STS_SUCCESS;
921:
922: IF ((p_dim_obj_short_name IS NOT NULL) AND
923: (p_is_default_short_name <> 'T')) THEN
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
974: FND_MESSAGE.SET_NAME('BSC','BSC_ALPHA_NUM_REQUIRED');
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
981: FND_MESSAGE.SET_NAME('BSC','BSC_REQ_FIELD_MISSING');
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
987: FND_MESSAGE.SET_NAME('BSC','BSC_REQ_FIELD_MISSING');
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
993: FND_MESSAGE.SET_NAME('BSC','BSC_REQ_FIELD_MISSING');
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
999: FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_DATA_SOURCE');
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
1004: l_bsc_dim_obj_rec.Source := 'OLTP';
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;
1012: END IF;
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
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
1037: -- source type because of this
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
1069: SELECT COUNT(1) INTO l_count
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;
1172:
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
1193: l_flag := TRUE;
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;
1237:
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:
1263: l_bis_dim_level_rec.where_Clause := p_where_clause;
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
1296: CLOSE c_Master_Level;
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
1311: FND_MESSAGE.SET_NAME('BIS','BIS_PMF_LOV_NO_MASTER');
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;
1317:
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;
1339: END IF;
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;
1471:
1496: CLOSE c_Master_Level;
1497: END IF;
1498: ROLLBACK TO CreateBSCDimObjPMD;
1499: IF (x_msg_data IS NULL) THEN
1500: FND_MSG_PUB.Count_And_Get
1501: ( p_encoded => FND_API.G_FALSE
1502: , p_count => x_msg_count
1503: , p_data => x_msg_data
1504: );
1509: CLOSE c_Master_Level;
1510: END IF;
1511: ROLLBACK TO CreateBSCDimObjPMD;
1512: IF (x_msg_data IS NULL) THEN
1513: FND_MSG_PUB.Count_And_Get
1514: ( p_encoded => FND_API.G_FALSE
1515: , p_count => x_msg_count
1516: , p_data => x_msg_data
1517: );
1557: l_dim_grp_id BSC_SYS_DIM_GROUPS_TL.Dim_Group_Id%TYPE;
1558:
1559: l_count NUMBER;
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);
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
1570: l_dim_short_names := p_dim_short_names;
1611: x_return_status := FND_API.G_RET_STS_SUCCESS;
1612: EXCEPTION
1613: WHEN FND_API.G_EXC_ERROR THEN
1614: IF (x_msg_data IS NULL) THEN
1615: FND_MSG_PUB.Count_And_Get
1616: ( p_encoded => FND_API.G_FALSE
1617: , p_count => x_msg_count
1618: , p_data => x_msg_data
1619: );
1620: END IF;
1621: x_return_status := FND_API.G_RET_STS_ERROR;
1622: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1623: IF (x_msg_data IS NULL) THEN
1624: FND_MSG_PUB.Count_And_Get
1625: ( p_encoded => FND_API.G_FALSE
1626: , p_count => x_msg_count
1627: , p_data => x_msg_data
1628: );
1657: ) IS
1658: l_dim_short_names VARCHAR2(32000);
1659: l_dim_name BSC_SYS_DIM_GROUPS_TL.short_name%TYPE;
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);
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
1670: l_dim_short_names := p_dim_short_names;
1702: x_return_status := FND_API.G_RET_STS_SUCCESS;
1703: EXCEPTION
1704: WHEN FND_API.G_EXC_ERROR THEN
1705: IF (x_msg_data IS NULL) THEN
1706: FND_MSG_PUB.Count_And_Get
1707: ( p_encoded => FND_API.G_FALSE
1708: , p_count => x_msg_count
1709: , p_data => x_msg_data
1710: );
1711: END IF;
1712: x_return_status := FND_API.G_RET_STS_ERROR;
1713: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1714: IF (x_msg_data IS NULL) THEN
1715: FND_MSG_PUB.Count_And_Get
1716: ( p_encoded => FND_API.G_FALSE
1717: , p_count => x_msg_count
1718: , p_data => x_msg_data
1719: );
1822: l_dim_obj_name VARCHAR2(300);
1823: l_flag BOOLEAN;
1824:
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);
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:
1835: IF (p_unassign_dim_short_names IS NOT NULL) THEN
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;
1879: END IF;
1938: x_return_status := FND_API.G_RET_STS_SUCCESS;
1939: EXCEPTION
1940: WHEN FND_API.G_EXC_ERROR THEN
1941: IF (x_msg_data IS NULL) THEN
1942: FND_MSG_PUB.Count_And_Get
1943: ( p_encoded => FND_API.G_FALSE
1944: , p_count => x_msg_count
1945: , p_data => x_msg_data
1946: );
1947: END IF;
1948: x_return_status := FND_API.G_RET_STS_ERROR;
1949: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1950: IF (x_msg_data IS NULL) THEN
1951: FND_MSG_PUB.Count_And_Get
1952: ( p_encoded => FND_API.G_FALSE
1953: , p_count => x_msg_count
1954: , p_data => x_msg_data
1955: );
2132: AND INSTR(','||l_child_dim_obj_list||',', ','||b.dim_level_id||',') > 0;
2133: ------------------------------------------------------------------
2134:
2135: BEGIN
2136: FND_MSG_PUB.Initialize;
2137: x_return_status := FND_API.G_RET_STS_SUCCESS;
2138: SAVEPOINT UpdateBSCDimObjectPMD;
2139: IF(p_dim_obj_short_name IS NOT NULL) THEN
2140: l_application_id := p_application_id;
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
2151: FND_MESSAGE.SET_NAME('BSC','BSC_REQ_FIELD_MISSING');
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
2157: FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_DATA_SOURCE');
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
2162: l_bsc_dim_obj_rec.Source := 'OLTP';
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;
2170: END IF;
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
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
2194: , level_table_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
2230: SELECT COUNT(1) INTO l_count
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;
2281: IF ((p_source_table IS NOT NULL) AND (l_bsc_dim_obj_rec.Bsc_Source = 'BSC') AND (INSTR(l_source_table, 'BSC_D_') <> 1)) THEN
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:
2293: IF (l_bsc_dim_obj_rec.Bsc_Source = 'BSC') THEN
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
2394: (l_bsc_dim_obj_rec.Bsc_Level_User_Key_Size > BSC_BIS_DIM_OBJ_PUB.Dim_Obj_Code_Max_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
2402: FROM BSC_SYS_DIM_LEVELS_B
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
2414: l_bsc_dim_obj_rec.Bsc_Level_Name := 'BSC_D_'||SUBSTR(REPLACE(l_dim_obj_name, ' ', '_') , 1, 22);
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;
2448:
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;
2475: l_bis_dim_level_rec.source := l_bsc_dim_obj_rec.Source;
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
2510: CLOSE c_Master_Level;
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
2525: FND_MESSAGE.SET_NAME('BIS','BIS_PMF_LOV_NO_MASTER');
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
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;
2540:
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;
2562: END IF;
2828: CLOSE c_Master_Level;
2829: END IF;
2830: ROLLBACK TO UpdateBSCDimObjectPMD;
2831: IF (x_msg_data IS NULL) THEN
2832: FND_MSG_PUB.Count_And_Get
2833: ( p_encoded => FND_API.G_FALSE
2834: , p_count => x_msg_count
2835: , p_data => x_msg_data
2836: );
2841: CLOSE c_Master_Level;
2842: END IF;
2843: ROLLBACK TO UpdateBSCDimObjectPMD;
2844: IF (x_msg_data IS NULL) THEN
2845: FND_MSG_PUB.Count_And_Get
2846: ( p_encoded => FND_API.G_FALSE
2847: , p_count => x_msg_count
2848: , p_data => x_msg_data
2849: );
2909: , x_msg_count OUT NOCOPY NUMBER
2910: , x_msg_data OUT NOCOPY VARCHAR2
2911: ) IS
2912: BEGIN
2913: FND_MSG_PUB.Initialize;
2914: x_return_status := FND_API.G_RET_STS_SUCCESS;
2915:
2916: BSC_BIS_DIM_OBJ_PUB.Update_Dim_Object
2917: ( p_commit => FND_API.G_FALSE
2957: x_return_status := FND_API.G_RET_STS_SUCCESS;
2958: EXCEPTION
2959: WHEN FND_API.G_EXC_ERROR THEN
2960: IF (x_msg_data IS NULL) THEN
2961: FND_MSG_PUB.Count_And_Get
2962: ( p_encoded => FND_API.G_FALSE
2963: , p_count => x_msg_count
2964: , p_data => x_msg_data
2965: );
2966: END IF;
2967: x_return_status := FND_API.G_RET_STS_ERROR;
2968: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2969: IF (x_msg_data IS NULL) THEN
2970: FND_MSG_PUB.Count_And_Get
2971: ( p_encoded => FND_API.G_FALSE
2972: , p_count => x_msg_count
2973: , p_data => x_msg_data
2974: );
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;
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');
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:
3058: IF (cr_bsc_dim_obj%ISOPEN) THEN
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:
3075: FOR cd IN c_dimension_names LOOP
3142: IF (cr_bsc_dim_obj%ISOPEN) THEN
3143: CLOSE cr_bsc_dim_obj;
3144: END IF;
3145: IF (x_msg_data IS NULL) THEN
3146: FND_MSG_PUB.Count_And_Get
3147: ( p_encoded => FND_API.G_FALSE
3148: , p_count => x_msg_count
3149: , p_data => x_msg_data
3150: );
3154: IF (cr_bsc_dim_obj%ISOPEN) THEN
3155: CLOSE cr_bsc_dim_obj;
3156: END IF;
3157: IF (x_msg_data IS NULL) THEN
3158: FND_MSG_PUB.Count_And_Get
3159: ( p_encoded => FND_API.G_FALSE
3160: , p_count => x_msg_count
3161: , p_data => x_msg_data
3162: );
3222: , name
3223: FROM BSC_SYS_DIM_LEVELS_VL
3224: WHERE short_name = p_dim_obj_short_name;
3225: BEGIN
3226: FND_MSG_PUB.Initialize;
3227: x_return_status := FND_API.G_RET_STS_SUCCESS;
3228: SAVEPOINT DeleteBSCDimObjectPMD;
3229:
3230: IF (p_dim_obj_short_name IS NULL) THEN
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
3237: --check if short_name exists in the PMF system
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
3251: ( p_commit => FND_API.G_FALSE
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;
3266: 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
3381: COMMIT;
3388: CLOSE c_bsc_levels;
3389: END IF;
3390: ROLLBACK TO DeleteBSCDimObjectPMD;
3391: IF (x_msg_data IS NULL) THEN
3392: FND_MSG_PUB.Count_And_Get
3393: ( p_encoded => FND_API.G_FALSE
3394: , p_count => x_msg_count
3395: , p_data => x_msg_data
3396: );
3401: CLOSE c_bsc_levels;
3402: END IF;
3403: ROLLBACK TO DeleteBSCDimObjectPMD;
3404: IF (x_msg_data IS NULL) THEN
3405: FND_MSG_PUB.Count_And_Get
3406: ( p_encoded => FND_API.G_FALSE
3407: , p_count => x_msg_count
3408: , p_data => x_msg_data
3409: );
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;
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
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:
3464: SELECT dim_level_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:
3494: --check if the dimension is associated in relationships, if so raise an exception
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;
3506:
3506:
3507: EXCEPTION
3508: WHEN FND_API.G_EXC_ERROR THEN
3509: IF (x_msg_data IS NULL) THEN
3510: FND_MSG_PUB.Count_And_Get
3511: ( p_encoded => FND_API.G_FALSE
3512: , p_count => x_msg_count
3513: , p_data => x_msg_data
3514: );
3515: END IF;
3516: x_return_status := FND_API.G_RET_STS_ERROR;
3517: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3518: IF (x_msg_data IS NULL) THEN
3519: FND_MSG_PUB.Count_And_Get
3520: ( p_encoded => FND_API.G_FALSE
3521: , p_count => x_msg_count
3522: , p_data => x_msg_data
3523: );
3646: l_temp_var VARCHAR(50);
3647: l_count NUMBER := 0;
3648: l_Bsc_Level_Name VARCHAR(33);
3649: BEGIN
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');
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
3659: FND_MESSAGE.SET_NAME('BSC','BSC_REQ_FIELD_MISSING');
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
3665: FND_MESSAGE.SET_NAME('BSC','BSC_REQ_FIELD_MISSING');
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
3671: IF (p_Dim_Level_Rec.Bsc_Level_User_Key_Size IS NULL) THEN
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
3689: p_Dim_Level_Rec.Bsc_Level_Name := SUBSTR(p_Dim_Level_Rec.Bsc_Level_Name, 7, LENGTH(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
3700: FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_SQL_IDENTIFIER');
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
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;
3717:
3732: RETURN TRUE;
3733: EXCEPTION
3734: WHEN FND_API.G_EXC_ERROR THEN
3735: IF (x_msg_data IS NULL) THEN
3736: FND_MSG_PUB.Count_And_Get
3737: ( p_encoded => FND_API.G_FALSE
3738: , p_count => x_msg_count
3739: , p_data => x_msg_data
3740: );
3742: x_return_status := FND_API.G_RET_STS_ERROR;
3743: RETURN FALSE;
3744: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3745: IF (x_msg_data IS NULL) THEN
3746: FND_MSG_PUB.Count_And_Get
3747: ( p_encoded => FND_API.G_FALSE
3748: , p_count => x_msg_count
3749: , p_data => x_msg_data
3750: );
3805: AND ROWNUM < 2
3806: AND Owner = BSC_APPS.get_user_schema('APPS');
3807:
3808: BEGIN
3809: FND_MSG_PUB.Initialize;
3810: x_return_status := FND_API.G_RET_STS_SUCCESS;
3811: -- Check if underlying View or table exists.
3812:
3813: --Default values if view does not exists
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
3832: FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_SOURCE_TYPE');
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
3837: IF (p_Dim_Level_Rec.Bsc_Level_User_Key_Size IS NULL) THEN
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:
3922: SELECT COUNT(1) INTO l_count
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:
3933: RETURN TRUE;
3939: IF (allTabCol2%ISOPEN) THEN
3940: CLOSE allTabCol2;
3941: END IF;
3942: IF (x_msg_data IS NULL) THEN
3943: FND_MSG_PUB.Count_And_Get
3944: ( p_encoded => FND_API.G_FALSE
3945: , p_count => x_msg_count
3946: , p_data => x_msg_data
3947: );
3955: IF (allTabCol2%ISOPEN) THEN
3956: CLOSE allTabCol2;
3957: END IF;
3958: IF (x_msg_data IS NULL) THEN
3959: FND_MSG_PUB.Count_And_Get
3960: ( p_encoded => FND_API.G_FALSE
3961: , p_count => x_msg_count
3962: , p_data => x_msg_data
3963: );
4065: bsc_sys_dim_level_rels_v b
4066: WHERE
4067: b.dim_level_id= p_Dim_Level_Rec.Bsc_Level_Id;
4068: BEGIN
4069: FND_MSG_PUB.Initialize;
4070: x_return_status := FND_API.G_RET_STS_SUCCESS;
4071: SAVEPOINT CreateBSCPmfViewPMD;
4072:
4073: BSC_APPS.Init_Bsc_Apps;
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
4084: FND_MESSAGE.SET_NAME('BSC','BSC_REQ_FIELD_MISSING');
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
4090: FND_MESSAGE.SET_NAME('BSC','BSC_REQ_FIELD_MISSING');
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
4096: FND_MESSAGE.SET_NAME('BSC','BSC_REQ_FIELD_MISSING');
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
4102:
4176: EXCEPTION
4177: WHEN FND_API.G_EXC_ERROR THEN
4178: ROLLBACK TO CreateBSCPmfViewPMD;
4179: IF (x_msg_data IS NULL) THEN
4180: FND_MSG_PUB.Count_And_Get
4181: ( p_encoded => FND_API.G_FALSE
4182: , p_count => x_msg_count
4183: , p_data => x_msg_data
4184: );
4187: RETURN FALSE;
4188: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4189: ROLLBACK TO CreateBSCPmfViewPMD;
4190: IF (x_msg_data IS NULL) THEN
4191: FND_MSG_PUB.Count_And_Get
4192: ( p_encoded => FND_API.G_FALSE
4193: , p_count => x_msg_count
4194: , p_data => x_msg_data
4195: );
4246: l_rollback_ddl_stmts BSC_APPS.t_array_ddl_stmts;
4247: l_num_rollback_ddl_stmts NUMBER := 0;
4248:
4249: BEGIN
4250: FND_MSG_PUB.Initialize;
4251: x_return_status := FND_API.G_RET_STS_SUCCESS;
4252: SAVEPOINT CreateBSCMasTabsPMD;
4253:
4254: BSC_APPS.Init_Bsc_Apps;
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
4261: FND_MESSAGE.SET_NAME('BSC','BSC_REQ_FIELD_MISSING');
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
4267: FND_MESSAGE.SET_NAME('BSC','BSC_REQ_FIELD_MISSING');
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
4273: FND_MESSAGE.SET_NAME('BSC','BSC_REQ_FIELD_MISSING');
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
4279: p_Dim_Level_Rec.Bsc_Level_Abbreviation := SUBSTR(REPLACE(p_Dim_Level_Rec.Bsc_Level_Short_Name, ' ', ''), 1, 8);
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:
4295: l_input_table := 'BSC_DI_'||p_Dim_Level_Rec.Bsc_Level_Id;
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
4319:
4450: EXCEPTION
4451: WHEN FND_API.G_EXC_ERROR THEN
4452: ROLLBACK TO CreateBSCMasTabsPMD;
4453: IF (x_msg_data IS NULL) THEN
4454: FND_MSG_PUB.Count_And_Get
4455: ( p_encoded => FND_API.G_FALSE
4456: , p_count => x_msg_count
4457: , p_data => x_msg_data
4458: );
4466: RETURN FALSE;
4467: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4468: ROLLBACK TO CreateBSCMasTabsPMD;
4469: IF (x_msg_data IS NULL) THEN
4470: FND_MSG_PUB.Count_And_Get
4471: ( p_encoded => FND_API.G_FALSE
4472: , p_count => x_msg_count
4473: , p_data => x_msg_data
4474: );
4565: AND B.Dim_Level_Id = A.Parent_Dim_Level_Id
4566: AND A.Relation_Type = 1;
4567:
4568: BEGIN
4569: FND_MSG_PUB.Initialize;
4570: x_return_status := FND_API.G_RET_STS_SUCCESS;
4571: SAVEPOINT AlterBSCMasTabsPMD;
4572:
4573: BSC_APPS.Init_Bsc_Apps;
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
4580: FND_MESSAGE.SET_NAME('BSC','BSC_REQ_FIELD_MISSING');
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
4586: FND_MESSAGE.SET_NAME('BSC','BSC_REQ_FIELD_MISSING');
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
4592: FND_MESSAGE.SET_NAME('BSC','BSC_REQ_FIELD_MISSING');
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);
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:
4607: l_input_table := 'BSC_DI_'||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.
4621: --if they do not exists than call BSC_BIS_DIM_OBJ_PUB.Create_Bsc_Master_Tabs otherwise proceed further
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
4654: IF(UPPER(p_Dim_Level_Rec.Bsc_Pk_Col) <> UPPER(p_Dim_Level_Rec_Old.Bsc_Pk_Col)) THEN
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;
4674:
4799:
4800: WHEN FND_API.G_EXC_ERROR THEN
4801: ROLLBACK TO AlterBSCMasTabsPMD;
4802: IF (x_msg_data IS NULL) THEN
4803: FND_MSG_PUB.Count_And_Get
4804: ( p_encoded => FND_API.G_FALSE
4805: , p_count => x_msg_count
4806: , p_data => x_msg_data
4807: );
4810: RETURN FALSE;
4811: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4812: ROLLBACK TO AlterBSCMasTabsPMD;
4813: IF (x_msg_data IS NULL) THEN
4814: FND_MSG_PUB.Count_And_Get
4815: ( p_encoded => FND_API.G_FALSE
4816: , p_count => x_msg_count
4817: , p_data => x_msg_data
4818: );
4864: FROM BSC_SYS_DIM_LEVEL_RELS
4865: WHERE Parent_Dim_Level_Id = p_Dim_Level_Rec.Bsc_Level_Id
4866: AND Relation_Type = 2;
4867: BEGIN
4868: FND_MSG_PUB.Initialize;
4869: x_return_status := FND_API.G_RET_STS_SUCCESS;
4870: BSC_APPS.Init_Bsc_Apps;
4871:
4872: SAVEPOINT AlterBSCMxNPMD;
5053: RETURN FALSE;
5054: WHEN FND_API.G_EXC_ERROR THEN
5055: ROLLBACK TO AlterBSCMxNPMD;
5056: IF (x_msg_data IS NULL) THEN
5057: FND_MSG_PUB.Count_And_Get
5058: ( p_encoded => FND_API.G_FALSE
5059: , p_count => x_msg_count
5060: , p_data => x_msg_data
5061: );
5064: RETURN FALSE;
5065: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5066: ROLLBACK TO AlterBSCMxNPMD;
5067: IF (x_msg_data IS NULL) THEN
5068: FND_MSG_PUB.Count_And_Get
5069: ( p_encoded => FND_API.G_FALSE
5070: , p_count => x_msg_count
5071: , p_data => x_msg_data
5072: );
5140: FROM BSC_SYS_DIM_LEVEL_RELS
5141: WHERE parent_dim_level_id = p_Dim_Level_Rec.Bsc_Level_Id
5142: AND relation_type = 1;
5143: BEGIN
5144: FND_MSG_PUB.Initialize;
5145: x_return_status := FND_API.G_RET_STS_SUCCESS;
5146: BSC_APPS.Init_Bsc_Apps;
5147:
5148: SAVEPOINT AlterBSCOnexNPMD;
5380: CLOSE c_Parent_Index_Name;
5381: END IF;
5382: ROLLBACK TO AlterBSCOnexNPMD;
5383: IF (x_msg_data IS NULL) THEN
5384: FND_MSG_PUB.Count_And_Get
5385: ( p_encoded => FND_API.G_FALSE
5386: , p_count => x_msg_count
5387: , p_data => x_msg_data
5388: );
5394: CLOSE c_Parent_Index_Name;
5395: END IF;
5396: ROLLBACK TO AlterBSCOnexNPMD;
5397: IF (x_msg_data IS NULL) THEN
5398: FND_MSG_PUB.Count_And_Get
5399: ( p_encoded => FND_API.G_FALSE
5400: , p_count => x_msg_count
5401: , p_data => x_msg_data
5402: );
5583: AND B.share_flag <> 2
5584: AND INSTR(l_new_dim_Grps, ','||C.Short_Name||',') > 0;
5585: BEGIN
5586:
5587: FND_MSG_PUB.Initialize;
5588: IF ((p_dim_obj_short_name IS NULL) AND (p_Source IS NULL)) THEN
5589: RETURN NULL;
5590: END IF;
5591: IF (NOT BSC_UTILITY.isBscInProductionMode()) THEN
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: /***********************************************************
5713: Check for display format changes here
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;
5735: END IF;
5737: RETURN NULL;
5738: EXCEPTION
5739: WHEN FND_API.G_EXC_ERROR THEN
5740: IF (l_Msg_Data IS NULL) THEN
5741: FND_MSG_PUB.Count_And_Get
5742: ( p_encoded => FND_API.G_FALSE
5743: , p_count => l_msg_count
5744: , p_data => l_Msg_Data
5745: );
5746: END IF;
5747: RETURN l_Msg_Data;
5748: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5749: IF (l_Msg_Data IS NULL) THEN
5750: FND_MSG_PUB.Count_And_Get
5751: ( p_encoded => FND_API.G_FALSE
5752: , p_count => l_msg_count
5753: , p_data => l_Msg_Data
5754: );
5804: l_no_dim_object VARCHAR2(32000);
5805:
5806: BEGIN
5807:
5808: FND_MSG_PUB.Initialize;
5809: IF(p_Dim_Short_Names IS NOT NULL) THEN
5810: l_dim_sht_names := p_Dim_Short_Names;
5811: WHILE(Is_More(p_dim_short_names => l_dim_sht_names,p_dim_name =>l_dim_short_name)) LOOP
5812: OPEN cr_kpidimset_dim ;
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;
5859: END LOOP;
5867: RETURN NULL;
5868: EXCEPTION
5869: WHEN FND_API.G_EXC_ERROR THEN
5870: IF (l_Msg_Data IS NULL) THEN
5871: FND_MSG_PUB.Count_And_Get
5872: ( p_encoded => FND_API.G_FALSE
5873: , p_count => l_msg_count
5874: , p_data => l_Msg_Data
5875: );
6076: END IF;
6077: EXCEPTION
6078: WHEN FND_API.G_EXC_ERROR THEN
6079: IF (x_msg_data IS NULL) THEN
6080: FND_MSG_PUB.Count_And_Get
6081: ( p_encoded => FND_API.G_FALSE
6082: , p_count => x_msg_count
6083: , p_data => x_msg_data
6084: );
6085: END IF;
6086: x_return_status := FND_API.G_RET_STS_ERROR;
6087: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
6088: IF (x_msg_data IS NULL) THEN
6089: FND_MSG_PUB.Count_And_Get
6090: ( p_encoded => FND_API.G_FALSE
6091: , p_count => x_msg_count
6092: , p_data => x_msg_data
6093: );
6149: , x_msg_data OUT NOCOPY VARCHAR2
6150: ) IS
6151: l_flag BOOLEAN := TRUE;
6152: BEGIN
6153: FND_MSG_PUB.Initialize;
6154: x_return_status := FND_API.G_RET_STS_SUCCESS;
6155:
6156: l_flag := BSC_BIS_DIM_OBJ_PUB.Initialize_Pmf_Recs
6157: ( p_Dim_Level_Rec => p_Dim_Level_Rec
6173: -- so, we need to process all the Dimension Objects, even if some fail
6174: EXCEPTION
6175: WHEN FND_API.G_EXC_ERROR THEN
6176: IF (x_msg_data IS NULL) THEN
6177: FND_MSG_PUB.Count_And_Get
6178: ( p_encoded => FND_API.G_FALSE
6179: , p_count => x_msg_count
6180: , p_data => x_msg_data
6181: );
6182: END IF;
6183: x_return_status := FND_API.G_RET_STS_ERROR;
6184: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
6185: IF (x_msg_data IS NULL) THEN
6186: FND_MSG_PUB.Count_And_Get
6187: ( p_encoded => FND_API.G_FALSE
6188: , p_count => x_msg_count
6189: , p_data => x_msg_data
6190: );
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:
6313: EXCEPTION
6316: IF (cr_kpi_dimset_dimobj%ISOPEN) THEN
6317: CLOSE cr_kpi_dimset_dimobj;
6318: END IF;
6319: IF (x_msg_data IS NULL) THEN
6320: FND_MSG_PUB.Count_And_Get
6321: ( p_encoded => FND_API.G_FALSE
6322: , p_count => x_msg_count
6323: , p_data => x_msg_data
6324: );
6329: IF (cr_kpi_dimset_dimobj%ISOPEN) THEN
6330: CLOSE cr_kpi_dimset_dimobj;
6331: END IF;
6332: IF (x_msg_data IS NULL) THEN
6333: FND_MSG_PUB.Count_And_Get
6334: ( p_encoded => FND_API.G_FALSE
6335: , p_count => x_msg_count
6336: , p_data => x_msg_data
6337: );
6416: AND R.SOURCE = 'PMF';
6417:
6418: BEGIN
6419:
6420: FND_MSG_PUB.Initialize;
6421: x_Return_Status := FND_API.G_RET_STS_SUCCESS;
6422:
6423: l_Count := 0;
6424: l_Parent_Cols := NULL;
6506: WHERE short_name = p_Dim_Obj_Short_Name;
6507:
6508: BEGIN
6509:
6510: FND_MSG_PUB.Initialize;
6511: x_Return_Status := FND_API.G_RET_STS_SUCCESS;
6512:
6513: IF (c_bsc_table_type_flag%ISOPEN) THEN
6514: CLOSE c_bsc_table_type_flag;
6553: )
6554: IS
6555: BEGIN
6556:
6557: FND_MSG_PUB.Initialize;
6558: x_Return_Status := FND_API.G_RET_STS_SUCCESS;
6559:
6560: IF ('PMF' = BIS_PMF_GET_DIMLEVELS_PVT.get_dim_level_source(p_Dim_Obj_Short_Name)) THEN
6561: IF (INSTR(p_Dim_Obj_Short_Name, 'FII_ROLLING_') = 0) THEN
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:
6787: IF(CTYPE.SOURCE <> p_dim_obj_source AND BSC_UTILITY.Is_Internal_Dimension(l_dim_short_name) = FND_API.G_FALSE) THEN
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;
6814:
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;
7065: x_return_status := FND_API.G_RET_STS_SUCCESS;
7066:
7067: IF (p_Dim_Level_Rec.Bsc_Dim_Level_Long_Name IS NOT NULL) THEN
7068: l_Dim_Object_Name := REPLACE(p_Dim_Level_Rec.Bsc_Level_Name, ' ', '');
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
7097: l_Level_Pk_Col := l_Level_Pk_Col||'_CODE';
7118:
7119: EXCEPTION
7120: WHEN FND_API.G_EXC_ERROR THEN
7121: IF (x_msg_data IS NULL) THEN
7122: FND_MSG_PUB.Count_And_Get
7123: ( p_encoded => FND_API.G_FALSE
7124: , p_count => x_msg_count
7125: , p_data => x_msg_data
7126: );
7128: x_Return_Status := FND_API.G_RET_STS_ERROR;
7129: RETURN NULL;
7130: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
7131: IF (x_msg_data IS NULL) THEN
7132: FND_MSG_PUB.Count_And_Get
7133: ( p_encoded => FND_API.G_FALSE
7134: , p_count => x_msg_count
7135: , p_data => x_msg_data
7136: );
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;
7201:
7207: );
7208:
7209: EXCEPTION
7210: WHEN OTHERS THEN
7211: FND_MSG_PUB.Count_And_Get
7212: ( p_encoded => FND_API.G_FALSE
7213: , p_count => x_msg_count
7214: , p_data => x_Msg_Data
7215: );
7247: AND bis_dim.short_name = reg.attribute12
7248: AND bis_util.is_seeded(bis_dim.created_by,'T','F') = 'F'
7249: AND NVL(hide_in_design,'F') = 'T';
7250: BEGIN
7251: FND_MSG_PUB.Initialize;
7252: x_Return_Status := FND_API.G_RET_STS_SUCCESS;
7253: l_Dim_DimObj_Sht_Name := p_Dim_Short_Name || '+' || p_Dim_Obj_Short_Name;
7254: IF (l_Dim_DimObj_Sht_Name IS NOT NULL) THEN
7255: OPEN c_dim_groups;
7266: END LOOP;
7267: END IF;
7268: EXCEPTION
7269: WHEN OTHERS THEN
7270: FND_MSG_PUB.Count_And_Get
7271: ( p_encoded => FND_API.G_FALSE
7272: , p_count => x_msg_count
7273: , p_data => x_Msg_Data
7274: );