[Home] [Help]
396: /*********************************************************************************
397: Set the ALL Enable Flag, must be called only for PMF type Dimension Objects
398: *********************************************************************************/
399: PROCEDURE Set_All_Enable_Flag
400: ( p_commit IN VARCHAR2 := FND_API.G_TRUE
401: , p_Dim_Obj_Short_Name IN VARCHAR2
402: , p_All_Enabled IN NUMBER
403: , x_return_status OUT NOCOPY VARCHAR2
404: , x_msg_count OUT NOCOPY NUMBER
432: SET Total_Flag = p_All_Enabled
433: WHERE Dim_Level_Id = l_Bsc_Dim_Obj_ID
434: AND Dim_Group_Id = l_Bsc_Group_ID;
435: END IF;
436: IF (p_commit = FND_API.G_TRUE) THEN
437: COMMIT;
438: END IF;
439: x_return_status := FND_API.G_RET_STS_SUCCESS;
440:
435: END IF;
436: IF (p_commit = FND_API.G_TRUE) THEN
437: COMMIT;
438: END IF;
439: x_return_status := FND_API.G_RET_STS_SUCCESS;
440:
441: EXCEPTION
442: WHEN OTHERS THEN
443: ROLLBACK TO SyncPMFAllInPMD;
440:
441: EXCEPTION
442: WHEN OTHERS THEN
443: ROLLBACK TO SyncPMFAllInPMD;
444: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
445: IF (x_msg_data IS NOT NULL) THEN
446: x_msg_data := x_msg_data||' -> BSC_BIS_DIM_OBJ_PUB.Set_All_Enable_Flag ';
447: ELSE
448: x_msg_data := SQLERRM||' at BSC_BIS_DIM_OBJ_PUB.Set_All_Enable_Flag ';
475: AND K.table_relation IS NOT NULL;
476:
477: BEGIN
478: BSC_APPS.Init_Bsc_Apps;
479: x_return_status := FND_API.G_RET_STS_SUCCESS;
480:
481: UPDATE bsc_sys_dim_level_rels
482: SET relation_col = p_Relation_Col
483: WHERE dim_level_id = p_Dim_Level_Id
500:
501:
502: EXCEPTION
503: WHEN OTHERS THEN
504: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
505: IF (x_msg_data IS NOT NULL) THEN
506: x_msg_data := x_msg_data||' -> BSC_BIS_DIM_OBJ_PUB.Refresh_BSC_Dim_View_In_Pmf';
507: ELSE
508: x_msg_data := SQLERRM||' at BSC_BIS_DIM_OBJ_PUB.Refresh_BSC_Dim_View_In_Pmf';
512:
513: -- Called from Create and Update flow for BSC type dim objects only.
514: -- This will support enable/disable of 'All' flag from Report Designer.
515: PROCEDURE Set_Bsc_All_Enable_Flag
516: ( p_commit IN VARCHAR2 := FND_API.G_TRUE
517: , p_Dim_Obj_Short_Name IN VARCHAR2
518: , p_Dim_Short_Name IN VARCHAR2 := NULL
519: , p_All_Enabled IN NUMBER
520: , x_return_status OUT NOCOPY VARCHAR2
538: WHERE dim_level_id = BSC_BIS_DIM_OBJ_PUB.Get_Bsc_Dim_Obj_ID(p_Dim_Obj_Short_Name);
539: END IF;
540: END IF;
541:
542: IF (p_commit = FND_API.G_TRUE) THEN
543: COMMIT;
544: END IF;
545: x_return_status := FND_API.G_RET_STS_SUCCESS;
546:
541:
542: IF (p_commit = FND_API.G_TRUE) THEN
543: COMMIT;
544: END IF;
545: x_return_status := FND_API.G_RET_STS_SUCCESS;
546:
547: EXCEPTION
548: WHEN OTHERS THEN
549: ROLLBACK TO SyncBSCAllInPMD;
546:
547: EXCEPTION
548: WHEN OTHERS THEN
549: ROLLBACK TO SyncBSCAllInPMD;
550: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
551: IF (x_msg_data IS NOT NULL) THEN
552: x_msg_data := x_msg_data||' -> BSC_BIS_DIM_OBJ_PUB.Set_Bsc_All_Enable_Flag ';
553: ELSE
554: x_msg_data := SQLERRM||' at BSC_BIS_DIM_OBJ_PUB.Set_Bsc_All_Enable_Flag ';
558: /*********************************************************************************
559: CREATE DIMENSION
560: *********************************************************************************/
561: PROCEDURE Create_Dim_Object
562: ( p_commit IN VARCHAR2 := FND_API.G_TRUE
563: , p_dim_obj_short_name IN VARCHAR2
564: , p_display_name IN VARCHAR2
565: , p_application_id IN NUMBER
566: , p_description IN VARCHAR2
578: , p_dimension_values_order IN NUMBER
579: , p_comparison_order IN NUMBER
580: , p_dim_short_names IN VARCHAR2
581: , p_Master_Level IN VARCHAR2 := NULL
582: , p_Long_Lov IN VARCHAR2 := FND_API.G_FALSE
583: , p_Search_Function IN VARCHAR2 := NULL
584: , p_Dim_Obj_Enabled IN VARCHAR2 := FND_API.G_FALSE
585: , p_View_Object_Name IN VARCHAR2 := NULL
586: , p_Default_Values_Api IN VARCHAR2 := NULL
580: , p_dim_short_names IN VARCHAR2
581: , p_Master_Level IN VARCHAR2 := NULL
582: , p_Long_Lov IN VARCHAR2 := FND_API.G_FALSE
583: , p_Search_Function IN VARCHAR2 := NULL
584: , p_Dim_Obj_Enabled IN VARCHAR2 := FND_API.G_FALSE
585: , p_View_Object_Name IN VARCHAR2 := NULL
586: , p_Default_Values_Api IN VARCHAR2 := NULL
587: , p_All_Enabled IN NUMBER := NULL
588: , p_Drill_To_Form_Function IN VARCHAR2 := NULL
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
600: , p_dim_obj_short_name => p_dim_obj_short_name
628: , x_msg_count => x_msg_count
629: , x_msg_data => x_msg_data
630: );
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
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: );
640: END IF;
637: , p_count => x_msg_count
638: , p_data => x_msg_data
639: );
640: END IF;
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
638: , p_data => x_msg_data
639: );
640: END IF;
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
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: );
650: END IF;
647: , p_count => x_msg_count
648: , p_data => x_msg_data
649: );
650: END IF;
651: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
652: WHEN NO_DATA_FOUND THEN
653: ROLLBACK TO CreateBSCDimObjPMD;
654: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
655: IF (x_msg_data IS NOT NULL) THEN
650: END IF;
651: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
652: WHEN NO_DATA_FOUND THEN
653: ROLLBACK TO CreateBSCDimObjPMD;
654: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
655: IF (x_msg_data IS NOT NULL) THEN
656: x_msg_data := x_msg_data||' -> BSC_BIS_DIM_OBJ_PUB.Create_Dim_Object ';
657: ELSE
658: x_msg_data := SQLERRM||' at BSC_BIS_DIM_OBJ_PUB.Create_Dim_Object ';
658: x_msg_data := SQLERRM||' at BSC_BIS_DIM_OBJ_PUB.Create_Dim_Object ';
659: END IF;
660: WHEN OTHERS THEN
661: ROLLBACK TO CreateBSCDimObjPMD;
662: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
663: IF (x_msg_data IS NOT NULL) THEN
664: x_msg_data := x_msg_data||' -> BSC_BIS_DIM_OBJ_PUB.Create_Dim_Object ';
665: ELSE
666: x_msg_data := SQLERRM||' at BSC_BIS_DIM_OBJ_PUB.Create_Dim_Object ';
670: /*
671: ** Called from JAVA Dimension Designer
672: */
673: PROCEDURE Create_Dim_Object
674: ( p_commit IN VARCHAR2 := FND_API.G_FALSE
675: , p_dim_obj_short_name IN VARCHAR2
676: , p_display_name IN VARCHAR2
677: , p_application_id IN NUMBER
678: , p_description IN VARCHAR2
690: , p_dimension_values_order IN NUMBER
691: , p_comparison_order IN NUMBER
692: , p_dim_short_names IN VARCHAR2
693: , p_Master_Level IN VARCHAR2 := NULL
694: , p_Long_Lov IN VARCHAR2 := FND_API.G_FALSE
695: , p_Search_Function IN VARCHAR2 := NULL
696: , p_Dim_Obj_Enabled IN VARCHAR2 := FND_API.G_FALSE
697: , p_View_Object_Name IN VARCHAR2 := NULL
698: , p_Default_Values_Api IN VARCHAR2 := NULL
692: , p_dim_short_names IN VARCHAR2
693: , p_Master_Level IN VARCHAR2 := NULL
694: , p_Long_Lov IN VARCHAR2 := FND_API.G_FALSE
695: , p_Search_Function IN VARCHAR2 := NULL
696: , p_Dim_Obj_Enabled IN VARCHAR2 := FND_API.G_FALSE
697: , p_View_Object_Name IN VARCHAR2 := NULL
698: , p_Default_Values_Api IN VARCHAR2 := NULL
699: , p_All_Enabled IN NUMBER := NULL
700: , p_is_default_short_name IN VARCHAR2
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:
718: -- Create Dimension if p_Force_Dimension_Create = 'T' (AG)
718: -- Create Dimension if p_Force_Dimension_Create = 'T' (AG)
719: IF (p_Force_Dimension_Create = 'T') THEN
720:
721: BSC_BIS_DIMENSION_PUB.Create_Dimension
722: ( p_commit => FND_API.G_FALSE
723: , p_dim_short_name => p_dim_short_names
724: , p_display_name => p_display_name
725: , p_description => p_description
726: , p_dim_obj_short_names => NULL
729: , x_msg_count => x_msg_count
730: , x_msg_data => x_msg_data
731: );
732:
733: IF ((x_return_status IS NOT NULL) AND (x_return_status <> FND_API.G_RET_STS_SUCCESS)) THEN
734: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
735: END IF;
736:
737: l_is_default_short_name := 'F';
730: , x_msg_data => x_msg_data
731: );
732:
733: IF ((x_return_status IS NOT NULL) AND (x_return_status <> FND_API.G_RET_STS_SUCCESS)) THEN
734: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
735: END IF;
736:
737: l_is_default_short_name := 'F';
738: END IF;
772: , x_msg_count => x_msg_count
773: , x_msg_data => x_msg_data
774: );
775:
776: IF ((x_return_status IS NOT NULL) AND (x_return_status <> FND_API.G_RET_STS_SUCCESS)) THEN
777: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
778: END IF;
779:
780: -- Save Prototype Values if NOT NULL (AG)
773: , x_msg_data => x_msg_data
774: );
775:
776: IF ((x_return_status IS NOT NULL) AND (x_return_status <> FND_API.G_RET_STS_SUCCESS)) THEN
777: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
778: END IF;
779:
780: -- Save Prototype Values if NOT NULL (AG)
781: IF (p_Prototype_Values.COUNT > 0) THEN
785: );
786: END IF;
787:
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
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: );
797: END IF;
794: , p_count => x_msg_count
795: , p_data => x_msg_data
796: );
797: END IF;
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
795: , p_data => x_msg_data
796: );
797: END IF;
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
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: );
807: END IF;
804: , p_count => x_msg_count
805: , p_data => x_msg_data
806: );
807: END IF;
808: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
809: WHEN NO_DATA_FOUND THEN
810: ROLLBACK TO CreateBSCDimObjPMD;
811: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
812: IF (x_msg_data IS NOT NULL) THEN
807: END IF;
808: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
809: WHEN NO_DATA_FOUND THEN
810: ROLLBACK TO CreateBSCDimObjPMD;
811: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
812: IF (x_msg_data IS NOT NULL) THEN
813: x_msg_data := x_msg_data||' -> BSC_BIS_DIM_OBJ_PUB.Create_Dim_Object ';
814: ELSE
815: x_msg_data := SQLERRM||' at BSC_BIS_DIM_OBJ_PUB.Create_Dim_Object ';
815: x_msg_data := SQLERRM||' at BSC_BIS_DIM_OBJ_PUB.Create_Dim_Object ';
816: END IF;
817: WHEN OTHERS THEN
818: ROLLBACK TO CreateBSCDimObjPMD;
819: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
820: IF (x_msg_data IS NOT NULL) THEN
821: x_msg_data := x_msg_data||' -> BSC_BIS_DIM_OBJ_PUB.Create_Dim_Object ';
822: ELSE
823: x_msg_data := SQLERRM||' at BSC_BIS_DIM_OBJ_PUB.Create_Dim_Object ';
841: 2. p_dim_obj_short_name and p_display_name must be unique.
842: */
843:
844: PROCEDURE Create_Dim_Object
845: ( p_commit IN VARCHAR2 := FND_API.G_TRUE
846: , p_dim_obj_short_name IN VARCHAR2
847: , p_display_name IN VARCHAR2
848: , p_application_id IN NUMBER
849: , p_description IN VARCHAR2
861: , p_dimension_values_order IN NUMBER
862: , p_comparison_order IN NUMBER
863: , p_dim_short_names IN VARCHAR2
864: , p_Master_Level IN VARCHAR2 := NULL
865: , p_Long_Lov IN VARCHAR2 := FND_API.G_FALSE
866: , p_Search_Function IN VARCHAR2 := NULL
867: , p_Dim_Obj_Enabled IN VARCHAR2 := FND_API.G_FALSE
868: , p_View_Object_Name IN VARCHAR2 := NULL
869: , p_Default_Values_Api IN VARCHAR2 := NULL
863: , p_dim_short_names IN VARCHAR2
864: , p_Master_Level IN VARCHAR2 := NULL
865: , p_Long_Lov IN VARCHAR2 := FND_API.G_FALSE
866: , p_Search_Function IN VARCHAR2 := NULL
867: , p_Dim_Obj_Enabled IN VARCHAR2 := FND_API.G_FALSE
868: , p_View_Object_Name IN VARCHAR2 := NULL
869: , p_Default_Values_Api IN VARCHAR2 := NULL
870: , p_All_Enabled IN NUMBER := NULL
871: , p_is_default_short_name IN VARCHAR2
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
924: l_dim_obj_name := TRIM(p_dim_obj_short_name);
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');
975: FND_MESSAGE.SET_TOKEN('VALUE', l_dim_obj_name);
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');
982: FND_MESSAGE.SET_TOKEN('FIELD_NAME', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DISPLAY_NAME'));
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');
988: FND_MESSAGE.SET_TOKEN('FIELD_NAME', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'APPLICATION_ID'));
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');
994: FND_MESSAGE.SET_TOKEN('FIELD_NAME', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DATA_SOURCE'));
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');
1000: FND_MSG_PUB.ADD;
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';
1005: ELSE
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;
1013: END IF;
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
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
1038: l_mix_type_dim := FALSE;
1047: , x_msg_count => x_msg_count
1048: , x_msg_data => x_msg_data
1049: );
1050: IF (l_mix_type_dim) THEN
1051: RAISE FND_API.G_EXC_ERROR;
1052: END IF;
1053: END IF;
1054:
1055: -- start checkin for Default names
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
1070: FROM BIS_LEVELS_VL
1098: , x_return_status => x_return_status
1099: , x_msg_count => x_msg_count
1100: , x_msg_data => x_msg_data
1101: );
1102: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1103: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1104: END IF;
1105: ELSE
1106: l_bis_dim_level_rec.Dimension_Short_Name := BSC_BIS_DIMENSION_PUB.Unassigned_Dim;
1099: , x_msg_count => x_msg_count
1100: , x_msg_data => x_msg_data
1101: );
1102: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1103: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1104: END IF;
1105: ELSE
1106: l_bis_dim_level_rec.Dimension_Short_Name := BSC_BIS_DIMENSION_PUB.Unassigned_Dim;
1107: END IF;
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:
1173:
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;
1194: l_alias := NULL;
1214: , x_msg_count => x_msg_count
1215: , x_msg_data => x_msg_data
1216: );
1217: IF(NOT l_flag) THEN
1218: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1219: END IF;
1220: ELSIF (l_bsc_dim_obj_rec.Bsc_Source = 'PMF') THEN
1221: IF ((l_bsc_dim_obj_rec.Source = 'OLTP') OR (l_bsc_dim_obj_rec.Source = 'EDW')) THEN
1222: l_flag := BSC_BIS_DIM_OBJ_PUB.Initialize_Pmf_Recs
1225: , x_msg_count => x_msg_count
1226: , x_msg_data => x_msg_data
1227: );
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;
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:
1238: --CALL PMF'S API
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;
1264: l_bis_dim_level_rec.application_id := l_application_id;
1265: l_bis_dim_level_rec.Source := l_bsc_dim_obj_rec.Source;
1266: l_bis_dim_level_rec.Comparison_Label_Code := p_comparison_label_code;
1267: --SELECT bis_levels_s.NextVal INTO l_bis_dim_level_rec.Dimension_Level_ID FROM DUAL;
1268:
1269: IF ((p_Long_Lov IS NULL) OR ((p_Long_Lov <> FND_API.G_TRUE) AND (p_Long_Lov <> FND_API.G_FALSE))) THEN
1270: l_bis_dim_level_rec.Long_Lov := FND_API.G_FALSE;
1271: ELSE
1272: l_bis_dim_level_rec.Long_Lov := p_Long_Lov;
1273: END IF;
1266: l_bis_dim_level_rec.Comparison_Label_Code := p_comparison_label_code;
1267: --SELECT bis_levels_s.NextVal INTO l_bis_dim_level_rec.Dimension_Level_ID FROM DUAL;
1268:
1269: IF ((p_Long_Lov IS NULL) OR ((p_Long_Lov <> FND_API.G_TRUE) AND (p_Long_Lov <> FND_API.G_FALSE))) THEN
1270: l_bis_dim_level_rec.Long_Lov := FND_API.G_FALSE;
1271: ELSE
1272: l_bis_dim_level_rec.Long_Lov := p_Long_Lov;
1273: END IF;
1274:
1271: ELSE
1272: l_bis_dim_level_rec.Long_Lov := p_Long_Lov;
1273: END IF;
1274:
1275: IF ((p_Dim_Obj_Enabled IS NULL) OR ((p_Dim_Obj_Enabled <> FND_API.G_TRUE) AND (p_Dim_Obj_Enabled <> FND_API.G_FALSE))) THEN
1276: l_bis_dim_level_rec.Enabled := FND_API.G_FALSE;
1277: ELSE
1278: l_bis_dim_level_rec.Enabled := p_Dim_Obj_Enabled;
1279: END IF;
1272: l_bis_dim_level_rec.Long_Lov := p_Long_Lov;
1273: END IF;
1274:
1275: IF ((p_Dim_Obj_Enabled IS NULL) OR ((p_Dim_Obj_Enabled <> FND_API.G_TRUE) AND (p_Dim_Obj_Enabled <> FND_API.G_FALSE))) THEN
1276: l_bis_dim_level_rec.Enabled := FND_API.G_FALSE;
1277: ELSE
1278: l_bis_dim_level_rec.Enabled := p_Dim_Obj_Enabled;
1279: END IF;
1280: IF (l_bsc_dim_obj_rec.Bsc_Source = 'PMF') THEN
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;
1297: END IF;
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');
1312: FND_MESSAGE.SET_TOKEN('BIS_OBJ_LOV', l_bis_name);
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:
1318: l_bis_dim_level_rec.Default_Search := p_Search_Function;
1320: -- Assigns Drill_To_Form_Function
1321: l_bis_dim_level_rec.Drill_To_Form_Function := p_Drill_To_Form_Function;
1322: BIS_DIMENSION_LEVEL_PUB.Create_Dimension_Level
1323: ( p_api_version => 1.0
1324: , p_commit => FND_API.G_FALSE
1325: , p_validation_level => FND_API.G_VALID_LEVEL_FULL
1326: , p_Dimension_Level_Rec => l_bis_dim_level_rec
1327: , x_return_status => x_return_status
1328: , x_error_Tbl => l_error_tbl
1321: l_bis_dim_level_rec.Drill_To_Form_Function := p_Drill_To_Form_Function;
1322: BIS_DIMENSION_LEVEL_PUB.Create_Dimension_Level
1323: ( p_api_version => 1.0
1324: , p_commit => FND_API.G_FALSE
1325: , p_validation_level => FND_API.G_VALID_LEVEL_FULL
1326: , p_Dimension_Level_Rec => l_bis_dim_level_rec
1327: , x_return_status => x_return_status
1328: , x_error_Tbl => l_error_tbl
1329: );
1326: , p_Dimension_Level_Rec => l_bis_dim_level_rec
1327: , x_return_status => x_return_status
1328: , x_error_Tbl => l_error_tbl
1329: );
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);
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;
1340: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1341: END IF;
1342:
1336: x_msg_data := NULL;
1337: END IF;
1338: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1339: END IF;
1340: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1341: END IF;
1342:
1343: --CALL BSC API
1344: l_bsc_dim_obj_rec.Bsc_Dim_Comp_Disp_Name := p_comparison_item_text;--BSC_SYS_DIM_LEVELS_TL.comp_disp_name
1370: l_level_table_name := l_bsc_dim_obj_rec.Bsc_Level_Name;
1371: l_bsc_dim_obj_rec.Bsc_Level_Name := l_bsc_dim_obj_rec.Bsc_Level_View_Name;
1372: END IF;
1373: BSC_DIMENSION_LEVELS_PUB.Create_Dim_Level
1374: ( p_commit => FND_API.G_FALSE
1375: , p_Dim_Level_Rec => l_bsc_dim_obj_rec
1376: , p_create_tables => FALSE
1377: , x_return_status => x_return_status
1378: , x_msg_count => x_msg_count
1377: , x_return_status => x_return_status
1378: , x_msg_count => x_msg_count
1379: , x_msg_data => x_msg_data
1380: );
1381: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1382: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1383: END IF;
1384: IF (l_bsc_dim_obj_rec.Bsc_Source = 'PMF') THEN
1385: BSC_BIS_DIM_OBJ_PUB.Assign_Dimensions
1378: , x_msg_count => x_msg_count
1379: , x_msg_data => x_msg_data
1380: );
1381: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1382: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1383: END IF;
1384: IF (l_bsc_dim_obj_rec.Bsc_Source = 'PMF') THEN
1385: BSC_BIS_DIM_OBJ_PUB.Assign_Dimensions
1386: ( p_commit => FND_API.G_FALSE
1382: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1383: END IF;
1384: IF (l_bsc_dim_obj_rec.Bsc_Source = 'PMF') THEN
1385: BSC_BIS_DIM_OBJ_PUB.Assign_Dimensions
1386: ( p_commit => FND_API.G_FALSE
1387: , p_dim_obj_short_name => l_dim_obj_name
1388: , p_dim_short_names => BSC_BIS_DIMENSION_PUB.Unassigned_Dim
1389: , x_return_status => x_return_status
1390: , x_msg_count => x_msg_count
1389: , x_return_status => x_return_status
1390: , x_msg_count => x_msg_count
1391: , x_msg_data => x_msg_data
1392: );
1393: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1394: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1395: END IF;
1396: BSC_BIS_DIM_OBJ_PUB.Set_All_Enable_Flag
1397: ( p_commit => FND_API.G_FALSE
1390: , x_msg_count => x_msg_count
1391: , x_msg_data => x_msg_data
1392: );
1393: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1394: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1395: END IF;
1396: BSC_BIS_DIM_OBJ_PUB.Set_All_Enable_Flag
1397: ( p_commit => FND_API.G_FALSE
1398: , p_Dim_Obj_Short_Name => l_dim_obj_name
1393: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1394: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1395: END IF;
1396: BSC_BIS_DIM_OBJ_PUB.Set_All_Enable_Flag
1397: ( p_commit => FND_API.G_FALSE
1398: , p_Dim_Obj_Short_Name => l_dim_obj_name
1399: , p_All_Enabled => p_All_Enabled
1400: , x_return_status => x_return_status
1401: , x_msg_count => x_msg_count
1400: , x_return_status => x_return_status
1401: , x_msg_count => x_msg_count
1402: , x_msg_data => x_msg_data
1403: );
1404: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1405: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1406: END IF;
1407: END IF;
1408: IF (p_dim_short_names IS NOT NULL) THEN
1401: , x_msg_count => x_msg_count
1402: , x_msg_data => x_msg_data
1403: );
1404: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1405: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1406: END IF;
1407: END IF;
1408: IF (p_dim_short_names IS NOT NULL) THEN
1409: BSC_BIS_DIM_OBJ_PUB.Assign_Unassign_Dimensions
1406: END IF;
1407: END IF;
1408: IF (p_dim_short_names IS NOT NULL) THEN
1409: BSC_BIS_DIM_OBJ_PUB.Assign_Unassign_Dimensions
1410: ( p_commit => FND_API.G_FALSE
1411: , p_dim_obj_short_name => l_dim_obj_name
1412: , p_assign_dim_short_names => p_dim_short_names
1413: , p_unassign_dim_short_names => BSC_BIS_DIMENSION_PUB.Unassigned_Dim
1414: , p_Restrict_Dim_Validate => p_Restrict_Dim_Validate
1415: , x_return_status => x_return_status
1416: , x_msg_count => x_msg_count
1417: , x_msg_data => x_msg_data
1418: );
1419: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1420: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1421: END IF;
1422: END IF;
1423:
1416: , x_msg_count => x_msg_count
1417: , x_msg_data => x_msg_data
1418: );
1419: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1420: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1421: END IF;
1422: END IF;
1423:
1424: /* Set 'All' flag for BSC type dim objects (specific to RPD)
1427: Create case.
1428: */
1429: IF ((l_bsc_dim_obj_rec.Bsc_Source = 'BSC') AND (p_dim_short_names IS NOT NULL)) THEN
1430: BSC_BIS_DIM_OBJ_PUB.Set_Bsc_All_Enable_Flag
1431: ( p_commit => FND_API.G_FALSE
1432: , p_Dim_Obj_Short_Name => l_dim_obj_name
1433: , p_All_Enabled => p_All_Enabled
1434: , x_return_status => x_return_status
1435: , x_msg_count => x_msg_count
1435: , x_msg_count => x_msg_count
1436: , x_msg_data => x_msg_data
1437: );
1438: END IF;
1439: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1440: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1441: END IF;
1442:
1443: IF (l_bsc_dim_obj_rec.Bsc_Source = 'BSC') THEN
1436: , x_msg_data => x_msg_data
1437: );
1438: END IF;
1439: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1440: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1441: END IF;
1442:
1443: IF (l_bsc_dim_obj_rec.Bsc_Source = 'BSC') THEN
1444: l_flag := BSC_BIS_DIM_OBJ_PUB.Create_Bsc_Master_Tabs
1447: , x_msg_count => x_msg_count
1448: , x_msg_data => x_msg_data
1449: );
1450: IF(NOT l_flag) THEN
1451: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1452: END IF;
1453: ELSIF (l_bsc_dim_obj_rec.Bsc_Source = 'PMF') THEN
1454: IF ((l_bsc_dim_obj_rec.Source = 'OLTP') OR (l_bsc_dim_obj_rec.Source = 'EDW')) THEN
1455: l_bsc_dim_obj_rec.Bsc_Level_Name := l_level_table_name;
1459: , x_msg_count => x_msg_count
1460: , x_msg_data => x_msg_data
1461: );
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;
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:
1472: /********************************************************
1479: , x_return_status => x_return_status
1480: , x_msg_count => x_msg_count
1481: , x_msg_data => x_msg_data
1482: );
1483: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1484: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1485: END IF;
1486: /***********************************************************/
1487:
1480: , x_msg_count => x_msg_count
1481: , x_msg_data => x_msg_data
1482: );
1483: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1484: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1485: END IF;
1486: /***********************************************************/
1487:
1488:
1485: END IF;
1486: /***********************************************************/
1487:
1488:
1489: IF (p_commit = FND_API.G_TRUE) THEN
1490: COMMIT;
1491: END IF;
1492: x_return_status := FND_API.G_RET_STS_SUCCESS;
1493: EXCEPTION
1488:
1489: IF (p_commit = FND_API.G_TRUE) THEN
1490: COMMIT;
1491: END IF;
1492: x_return_status := FND_API.G_RET_STS_SUCCESS;
1493: EXCEPTION
1494: WHEN FND_API.G_EXC_ERROR THEN
1495: IF (c_Master_Level%ISOPEN) THEN
1496: CLOSE c_Master_Level;
1490: COMMIT;
1491: END IF;
1492: x_return_status := FND_API.G_RET_STS_SUCCESS;
1493: EXCEPTION
1494: WHEN FND_API.G_EXC_ERROR THEN
1495: IF (c_Master_Level%ISOPEN) THEN
1496: CLOSE c_Master_Level;
1497: END IF;
1498: ROLLBACK TO CreateBSCDimObjPMD;
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: );
1505: END IF;
1502: , p_count => x_msg_count
1503: , p_data => x_msg_data
1504: );
1505: END IF;
1506: x_return_status := FND_API.G_RET_STS_ERROR;
1507: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1508: IF (c_Master_Level%ISOPEN) THEN
1509: CLOSE c_Master_Level;
1510: END IF;
1503: , p_data => x_msg_data
1504: );
1505: END IF;
1506: x_return_status := FND_API.G_RET_STS_ERROR;
1507: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1508: IF (c_Master_Level%ISOPEN) THEN
1509: CLOSE c_Master_Level;
1510: END IF;
1511: ROLLBACK TO CreateBSCDimObjPMD;
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: );
1518: END IF;
1515: , p_count => x_msg_count
1516: , p_data => x_msg_data
1517: );
1518: END IF;
1519: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1520: WHEN NO_DATA_FOUND THEN
1521: IF (c_Master_Level%ISOPEN) THEN
1522: CLOSE c_Master_Level;
1523: END IF;
1521: IF (c_Master_Level%ISOPEN) THEN
1522: CLOSE c_Master_Level;
1523: END IF;
1524: ROLLBACK TO CreateBSCDimObjPMD;
1525: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1526: IF (x_msg_data IS NOT NULL) THEN
1527: x_msg_data := x_msg_data||' -> BSC_BIS_DIM_OBJ_PUB.Create_Dim_Object ';
1528: ELSE
1529: x_msg_data := SQLERRM||' at BSC_BIS_DIM_OBJ_PUB.Create_Dim_Object ';
1532: IF (c_Master_Level%ISOPEN) THEN
1533: CLOSE c_Master_Level;
1534: END IF;
1535: ROLLBACK TO CreateBSCDimObjPMD;
1536: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1537: IF (x_msg_data IS NOT NULL) THEN
1538: x_msg_data := x_msg_data||' -> BSC_BIS_DIM_OBJ_PUB.Create_Dim_Object ';
1539: ELSE
1540: x_msg_data := SQLERRM||' at BSC_BIS_DIM_OBJ_PUB.Create_Dim_Object ';
1544: /*********************************************************************************
1545: ASSIGN DIMENSIONS TO DIMENSION OBJECT
1546: *********************************************************************************/
1547: PROCEDURE Assign_Dimensions
1548: ( p_commit IN VARCHAR2 := FND_API.G_TRUE
1549: , p_dim_obj_short_name IN VARCHAR2
1550: , p_dim_short_names IN VARCHAR2
1551: , x_return_status OUT NOCOPY VARCHAR2
1552: , x_msg_count OUT NOCOPY NUMBER
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);
1566: FND_MSG_PUB.ADD;
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;
1571: WHILE (is_more( p_dim_short_names => l_dim_short_names
1578: , x_return_status => x_return_status
1579: , x_msg_count => x_msg_count
1580: , x_msg_data => x_msg_data
1581: );
1582: IF ((x_return_status = FND_API.G_RET_STS_ERROR) OR (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR)) THEN
1583: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1584: END IF;
1585: -- Granular Locking - Set the timestamp of Dimension Group
1586: SELECT COUNT(1) INTO l_count
1579: , x_msg_count => x_msg_count
1580: , x_msg_data => x_msg_data
1581: );
1582: IF ((x_return_status = FND_API.G_RET_STS_ERROR) OR (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR)) THEN
1583: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1584: END IF;
1585: -- Granular Locking - Set the timestamp of Dimension Group
1586: SELECT COUNT(1) INTO l_count
1587: FROM BSC_SYS_DIM_LEVELS_BY_GROUP A
1590: AND A.Dim_Level_Id = B.Dim_Level_Id
1591: AND B.Short_Name = p_dim_obj_short_name;
1592: IF (l_count = 0) THEN
1593: BSC_BIS_DIMENSION_PUB.Assign_Dimension_Objects
1594: ( p_commit => FND_API.G_FALSE
1595: , p_dim_short_name => l_dim_name
1596: , p_dim_obj_short_names => p_dim_obj_short_name
1597: , p_time_stamp => NULL
1598: , x_return_status => x_return_status
1598: , x_return_status => x_return_status
1599: , x_msg_count => x_msg_count
1600: , x_msg_data => x_msg_data
1601: );
1602: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1603: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1604: END IF;
1605: END IF;
1606: END LOOP;
1599: , x_msg_count => x_msg_count
1600: , x_msg_data => x_msg_data
1601: );
1602: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1603: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1604: END IF;
1605: END IF;
1606: END LOOP;
1607: END IF;
1604: END IF;
1605: END IF;
1606: END LOOP;
1607: END IF;
1608: IF (p_commit = FND_API.G_TRUE) THEN
1609: COMMIT;
1610: END IF;
1611: x_return_status := FND_API.G_RET_STS_SUCCESS;
1612: EXCEPTION
1607: END IF;
1608: IF (p_commit = FND_API.G_TRUE) THEN
1609: COMMIT;
1610: END IF;
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
1609: COMMIT;
1610: END IF;
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
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;
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
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
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: );
1629: END IF;
1626: , p_count => x_msg_count
1627: , p_data => x_msg_data
1628: );
1629: END IF;
1630: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1631: WHEN NO_DATA_FOUND THEN
1632: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1633: IF (x_msg_data IS NOT NULL) THEN
1634: x_msg_data := x_msg_data||' -> BSC_BIS_DIM_OBJ_PUB.Assign_Dimensions ';
1628: );
1629: END IF;
1630: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1631: WHEN NO_DATA_FOUND THEN
1632: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1633: IF (x_msg_data IS NOT NULL) THEN
1634: x_msg_data := x_msg_data||' -> BSC_BIS_DIM_OBJ_PUB.Assign_Dimensions ';
1635: ELSE
1636: x_msg_data := SQLERRM||' at BSC_BIS_DIM_OBJ_PUB.Assign_Dimensions ';
1635: ELSE
1636: x_msg_data := SQLERRM||' at BSC_BIS_DIM_OBJ_PUB.Assign_Dimensions ';
1637: END IF;
1638: WHEN OTHERS THEN
1639: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1640: IF (x_msg_data IS NOT NULL) THEN
1641: x_msg_data := x_msg_data||' -> BSC_BIS_DIM_OBJ_PUB.Assign_Dimensions ';
1642: ELSE
1643: x_msg_data := SQLERRM||' at BSC_BIS_DIM_OBJ_PUB.Assign_Dimensions ';
1647: /*********************************************************************************
1648: ASSIGN DIMENSIONS TO DIMENSION OBJECT
1649: *********************************************************************************/
1650: PROCEDURE Unassign_Dimensions
1651: ( p_commit IN VARCHAR2 := FND_API.G_TRUE
1652: , p_dim_obj_short_name IN VARCHAR2
1653: , p_dim_short_names IN VARCHAR2
1654: , x_return_status OUT NOCOPY VARCHAR2
1655: , x_msg_count OUT NOCOPY NUMBER
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);
1666: FND_MSG_PUB.ADD;
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;
1671: WHILE (is_more( p_dim_short_names => l_dim_short_names
1678: , x_return_status => x_return_status
1679: , x_msg_count => x_msg_count
1680: , x_msg_data => x_msg_data
1681: );
1682: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1683: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1684: END IF;
1685: -- Granular Locking - Set the timestamp of Dimension Group
1686: BSC_BIS_DIMENSION_PUB.Unassign_Dimension_Objects
1679: , x_msg_count => x_msg_count
1680: , x_msg_data => x_msg_data
1681: );
1682: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1683: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1684: END IF;
1685: -- Granular Locking - Set the timestamp of Dimension Group
1686: BSC_BIS_DIMENSION_PUB.Unassign_Dimension_Objects
1687: ( p_commit => FND_API.G_FALSE
1683: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1684: END IF;
1685: -- Granular Locking - Set the timestamp of Dimension Group
1686: BSC_BIS_DIMENSION_PUB.Unassign_Dimension_Objects
1687: ( p_commit => FND_API.G_FALSE
1688: , p_dim_short_name => l_dim_name
1689: , p_dim_obj_short_names => p_dim_obj_short_name
1690: , x_return_status => x_return_status
1691: , x_msg_count => x_msg_count
1690: , x_return_status => x_return_status
1691: , x_msg_count => x_msg_count
1692: , x_msg_data => x_msg_data
1693: );
1694: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1695: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1696: END IF;
1697: END LOOP;
1698: END IF;
1691: , x_msg_count => x_msg_count
1692: , x_msg_data => x_msg_data
1693: );
1694: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1695: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1696: END IF;
1697: END LOOP;
1698: END IF;
1699: IF (p_commit = FND_API.G_TRUE) THEN
1695: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1696: END IF;
1697: END LOOP;
1698: END IF;
1699: IF (p_commit = FND_API.G_TRUE) THEN
1700: COMMIT;
1701: END IF;
1702: x_return_status := FND_API.G_RET_STS_SUCCESS;
1703: EXCEPTION
1698: END IF;
1699: IF (p_commit = FND_API.G_TRUE) THEN
1700: COMMIT;
1701: END IF;
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
1700: COMMIT;
1701: END IF;
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
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;
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
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
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: );
1720: END IF;
1717: , p_count => x_msg_count
1718: , p_data => x_msg_data
1719: );
1720: END IF;
1721: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1722: WHEN NO_DATA_FOUND THEN
1723: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1724: IF (x_msg_data IS NOT NULL) THEN
1725: x_msg_data := x_msg_data||' -> BSC_BIS_DIM_OBJ_PUB.Unassign_Dimensions ';
1719: );
1720: END IF;
1721: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1722: WHEN NO_DATA_FOUND THEN
1723: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1724: IF (x_msg_data IS NOT NULL) THEN
1725: x_msg_data := x_msg_data||' -> BSC_BIS_DIM_OBJ_PUB.Unassign_Dimensions ';
1726: ELSE
1727: x_msg_data := SQLERRM||' at BSC_BIS_DIM_OBJ_PUB.Unassign_Dimensions ';
1726: ELSE
1727: x_msg_data := SQLERRM||' at BSC_BIS_DIM_OBJ_PUB.Unassign_Dimensions ';
1728: END IF;
1729: WHEN OTHERS THEN
1730: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1731: IF (x_msg_data IS NOT NULL) THEN
1732: x_msg_data := x_msg_data||' -> BSC_BIS_DIM_OBJ_PUB.Unassign_Dimensions ';
1733: ELSE
1734: x_msg_data := SQLERRM||' at BSC_BIS_DIM_OBJ_PUB.Unassign_Dimensions ';
1788: , x_Return_Status => x_return_status
1789: , x_Msg_Count => x_msg_count
1790: , x_Msg_Data => x_msg_data
1791: );
1792: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1793: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1794: END IF;
1795:
1796:
1789: , x_Msg_Count => x_msg_count
1790: , x_Msg_Data => x_msg_data
1791: );
1792: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1793: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1794: END IF;
1795:
1796:
1797: END Restrict_Internal_Dimensions;
1800: /*********************************************************************************
1801: ASSIGN DIMENSIONS TO DIMENSION OBJECT
1802: *********************************************************************************/
1803: PROCEDURE Assign_Unassign_Dimensions
1804: ( p_commit IN VARCHAR2 := FND_API.G_TRUE
1805: , p_dim_obj_short_name IN VARCHAR2
1806: , p_assign_dim_short_names IN VARCHAR2
1807: , p_unassign_dim_short_names IN VARCHAR2
1808: , p_Restrict_Dim_Validate IN VARCHAR2 := NULL
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);
1831: FND_MSG_PUB.ADD;
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
1836: l_unassigns := p_unassign_dim_short_names;
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;
1880: END LOOP;
1879: END IF;
1880: END LOOP;
1881: IF (l_unassign_dims IS NOT NULL) THEN
1882: BSC_BIS_DIM_OBJ_PUB.Unassign_Dimensions
1883: ( p_commit => FND_API.G_FALSE
1884: , p_dim_obj_short_name => p_dim_obj_short_name
1885: , p_dim_short_names => l_unassign_dims
1886: , x_return_status => x_return_status
1887: , x_msg_count => x_msg_count
1886: , x_return_status => x_return_status
1887: , x_msg_count => x_msg_count
1888: , x_msg_data => x_msg_data
1889: );
1890: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1891: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1892: END IF;
1893: END IF;
1894: END IF;
1887: , x_msg_count => x_msg_count
1888: , x_msg_data => x_msg_data
1889: );
1890: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1891: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1892: END IF;
1893: END IF;
1894: END IF;
1895: IF (p_Restrict_Dim_Validate IS NOT NULL) THEN
1901: , x_msg_count => x_msg_count
1902: , x_msg_data => x_msg_data
1903: );
1904: END IF;
1905: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1906: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1907: END IF;
1908:
1909: IF (p_assign_dim_short_names IS NOT NULL) THEN
1902: , x_msg_data => x_msg_data
1903: );
1904: END IF;
1905: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1906: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1907: END IF;
1908:
1909: IF (p_assign_dim_short_names IS NOT NULL) THEN
1910: BSC_BIS_DIM_OBJ_PUB.Assign_Dimensions
1907: END IF;
1908:
1909: IF (p_assign_dim_short_names IS NOT NULL) THEN
1910: BSC_BIS_DIM_OBJ_PUB.Assign_Dimensions
1911: ( p_commit => FND_API.G_FALSE
1912: , p_dim_obj_short_name => p_dim_obj_short_name
1913: , p_dim_short_names => p_assign_dim_short_names
1914: , x_return_status => x_return_status
1915: , x_msg_count => x_msg_count
1914: , x_return_status => x_return_status
1915: , x_msg_count => x_msg_count
1916: , x_msg_data => x_msg_data
1917: );
1918: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1919: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1920: END IF;
1921: END IF;
1922: -- Bug 3784852 validate empty dimension after unassign/assign dim objs. Remove empty from dim sets for all objectives
1915: , x_msg_count => x_msg_count
1916: , x_msg_data => x_msg_data
1917: );
1918: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1919: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1920: END IF;
1921: END IF;
1922: -- Bug 3784852 validate empty dimension after unassign/assign dim objs. Remove empty from dim sets for all objectives
1923: IF (p_unassign_dim_short_names IS NOT NULL) THEN
1921: END IF;
1922: -- Bug 3784852 validate empty dimension after unassign/assign dim objs. Remove empty from dim sets for all objectives
1923: IF (p_unassign_dim_short_names IS NOT NULL) THEN
1924: BSC_BIS_DIMENSION_PUB.Remove_Empty_Dims_For_DimSet
1925: ( p_commit => FND_API.G_FALSE
1926: , p_dim_short_names => p_unassign_dim_short_names
1927: , x_return_status => x_return_status
1928: , x_msg_count => x_msg_count
1929: , x_msg_data => x_msg_data
1927: , x_return_status => x_return_status
1928: , x_msg_count => x_msg_count
1929: , x_msg_data => x_msg_data
1930: );
1931: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1932: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1933: END IF;
1934: END IF;
1935: IF (p_commit = FND_API.G_TRUE) THEN
1928: , x_msg_count => x_msg_count
1929: , x_msg_data => x_msg_data
1930: );
1931: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1932: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1933: END IF;
1934: END IF;
1935: IF (p_commit = FND_API.G_TRUE) THEN
1936: COMMIT;
1931: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1932: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1933: END IF;
1934: END IF;
1935: IF (p_commit = FND_API.G_TRUE) THEN
1936: COMMIT;
1937: END IF;
1938: x_return_status := FND_API.G_RET_STS_SUCCESS;
1939: EXCEPTION
1934: END IF;
1935: IF (p_commit = FND_API.G_TRUE) THEN
1936: COMMIT;
1937: 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
1936: COMMIT;
1937: 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
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;
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
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
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: );
1956: END IF;
1953: , p_count => x_msg_count
1954: , p_data => x_msg_data
1955: );
1956: END IF;
1957: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1958: WHEN NO_DATA_FOUND THEN
1959: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1960: IF (x_msg_data IS NOT NULL) THEN
1961: x_msg_data := x_msg_data||' -> BSC_BIS_DIM_OBJ_PUB.Assign_Unassign_Dimensions ';
1955: );
1956: END IF;
1957: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1958: WHEN NO_DATA_FOUND THEN
1959: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1960: IF (x_msg_data IS NOT NULL) THEN
1961: x_msg_data := x_msg_data||' -> BSC_BIS_DIM_OBJ_PUB.Assign_Unassign_Dimensions ';
1962: ELSE
1963: x_msg_data := SQLERRM||' at BSC_BIS_DIM_OBJ_PUB.Assign_Unassign_Dimensions ';
1962: ELSE
1963: x_msg_data := SQLERRM||' at BSC_BIS_DIM_OBJ_PUB.Assign_Unassign_Dimensions ';
1964: END IF;
1965: WHEN OTHERS THEN
1966: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1967: IF (x_msg_data IS NOT NULL) THEN
1968: x_msg_data := x_msg_data||' -> BSC_BIS_DIM_OBJ_PUB.Assign_Unassign_Dimensions ';
1969: ELSE
1970: x_msg_data := SQLERRM||' at BSC_BIS_DIM_OBJ_PUB.Assign_Unassign_Dimensions ';
2010: and p_source_type must not be null.
2011: 2. p_dim_obj_short_name and p_display_name must be unique.
2012: */
2013: PROCEDURE Update_Dim_Object
2014: ( p_commit IN VARCHAR2 := FND_API.G_TRUE
2015: , p_dim_obj_short_name IN VARCHAR2
2016: , p_display_name IN VARCHAR2
2017: , p_application_id IN NUMBER
2018: , p_description IN VARCHAR2
2032: , p_assign_dim_short_names IN VARCHAR2
2033: , p_unassign_dim_short_names IN VARCHAR2
2034: , p_time_stamp IN VARCHAR2 := NULL -- Granular Locking
2035: , p_Master_Level IN VARCHAR2 := NULL
2036: , p_Long_Lov IN VARCHAR2 := FND_API.G_FALSE
2037: , p_Search_Function IN VARCHAR2 := NULL
2038: , p_Dim_Obj_Enabled IN VARCHAR2 := FND_API.G_FALSE
2039: , p_View_Object_Name IN VARCHAR2 := NULL
2040: , p_Default_Values_Api IN VARCHAR2 := NULL
2034: , p_time_stamp IN VARCHAR2 := NULL -- Granular Locking
2035: , p_Master_Level IN VARCHAR2 := NULL
2036: , p_Long_Lov IN VARCHAR2 := FND_API.G_FALSE
2037: , p_Search_Function IN VARCHAR2 := NULL
2038: , p_Dim_Obj_Enabled IN VARCHAR2 := FND_API.G_FALSE
2039: , p_View_Object_Name IN VARCHAR2 := NULL
2040: , p_Default_Values_Api IN VARCHAR2 := NULL
2041: , p_All_Enabled IN NUMBER := NULL
2042: , p_Drill_To_Form_Function IN VARCHAR2 := NULL
2040: , p_Default_Values_Api IN VARCHAR2 := NULL
2041: , p_All_Enabled IN NUMBER := NULL
2042: , p_Drill_To_Form_Function IN VARCHAR2 := NULL
2043: , p_Restrict_Dim_Validate IN VARCHAR2 := NULL
2044: , p_Hide IN VARCHAR2 := FND_API.G_FALSE
2045: , x_return_status OUT NOCOPY VARCHAR2
2046: , x_msg_count OUT NOCOPY NUMBER
2047: , x_msg_data OUT NOCOPY VARCHAR2
2048: ) IS
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;
2141: END IF;
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');
2152: FND_MESSAGE.SET_TOKEN('FIELD_NAME', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DATA_SOURCE'), TRUE);
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');
2158: FND_MSG_PUB.ADD;
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';
2163: ELSE
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;
2171: END IF;
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
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
2195: , user_key_size
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
2231: FROM BIS_LEVELS_VL
2255: , x_msg_count => x_msg_count
2256: , x_msg_data => x_msg_data
2257: );
2258: IF (l_mix_type_dim) THEN
2259: RAISE FND_API.G_EXC_ERROR;
2260: END IF;
2261: END IF;
2262:
2263: --intialize records with existing values
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
2282: l_source_table := 'BSC_D_'||UPPER(l_source_table);
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
2294: IF ((l_bsc_dim_obj_rec.Bsc_Level_Abbreviation <> NVL(p_prototype_default_value, l_bsc_dim_obj_rec.Bsc_Level_Abbreviation)) OR
2375: ,x_return_status => x_return_status
2376: ,x_msg_count => x_msg_count
2377: ,x_msg_data => x_msg_data
2378: );
2379: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2380: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2381: END IF;
2382: -- End Granular Locking
2383:
2376: ,x_msg_count => x_msg_count
2377: ,x_msg_data => x_msg_data
2378: );
2379: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2380: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2381: END IF;
2382: -- End Granular Locking
2383:
2384: l_bsc_dim_obj_rec.Bsc_Level_Short_Name := l_dim_obj_name;--BSC_SYS_DIM_LEVELS_B.short_name
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
2395: (l_bsc_dim_obj_rec.Bsc_Level_Disp_Key_Size < BSC_BIS_DIM_OBJ_PUB.Dim_Obj_Name_Min_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
2403: WHERE UPPER(abbreviation) = UPPER(l_bsc_dim_obj_rec.Bsc_Level_Abbreviation)
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);
2415: END IF;
2419: , x_msg_count => x_msg_count
2420: , x_msg_data => x_msg_data
2421: );
2422: IF(NOT l_flag) THEN
2423: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2424: END IF;
2425: ELSIF (l_bsc_dim_obj_rec.Bsc_Source = 'PMF') THEN
2426: IF ((l_bsc_dim_obj_rec.Source = 'OLTP') OR (l_bsc_dim_obj_rec.Source = 'EDW')) THEN
2427:
2436: , x_msg_count => x_msg_count
2437: , x_msg_data => x_msg_data
2438: );
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;
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:
2449: SELECT level_id INTO l_bis_dim_level_rec.Dimension_Level_ID
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;
2476: l_bis_dim_level_rec.application_id := l_application_id;
2474: l_bis_dim_level_rec.where_Clause := p_where_clause;
2475: l_bis_dim_level_rec.source := l_bsc_dim_obj_rec.Source;
2476: l_bis_dim_level_rec.application_id := l_application_id;
2477: l_bis_dim_level_rec.Comparison_Label_Code := p_comparison_label_code;
2478: IF ((p_Long_Lov IS NULL) OR ((p_Long_Lov <> FND_API.G_TRUE) AND (p_Long_Lov <> FND_API.G_FALSE))) THEN
2479: l_bis_dim_level_rec.Long_Lov := FND_API.G_FALSE;
2480: ELSE
2481: l_bis_dim_level_rec.Long_Lov := p_Long_Lov;
2482: END IF;
2475: l_bis_dim_level_rec.source := l_bsc_dim_obj_rec.Source;
2476: l_bis_dim_level_rec.application_id := l_application_id;
2477: l_bis_dim_level_rec.Comparison_Label_Code := p_comparison_label_code;
2478: IF ((p_Long_Lov IS NULL) OR ((p_Long_Lov <> FND_API.G_TRUE) AND (p_Long_Lov <> FND_API.G_FALSE))) THEN
2479: l_bis_dim_level_rec.Long_Lov := FND_API.G_FALSE;
2480: ELSE
2481: l_bis_dim_level_rec.Long_Lov := p_Long_Lov;
2482: END IF;
2483:
2480: ELSE
2481: l_bis_dim_level_rec.Long_Lov := p_Long_Lov;
2482: END IF;
2483:
2484: IF ((p_Dim_Obj_Enabled IS NULL) OR ((p_Dim_Obj_Enabled <> FND_API.G_TRUE) AND (p_Dim_Obj_Enabled <> FND_API.G_FALSE))) THEN
2485: l_bis_dim_level_rec.Enabled := FND_API.G_FALSE;
2486: ELSE
2487: l_bis_dim_level_rec.Enabled := p_Dim_Obj_Enabled;
2488: END IF;
2481: l_bis_dim_level_rec.Long_Lov := p_Long_Lov;
2482: END IF;
2483:
2484: IF ((p_Dim_Obj_Enabled IS NULL) OR ((p_Dim_Obj_Enabled <> FND_API.G_TRUE) AND (p_Dim_Obj_Enabled <> FND_API.G_FALSE))) THEN
2485: l_bis_dim_level_rec.Enabled := FND_API.G_FALSE;
2486: ELSE
2487: l_bis_dim_level_rec.Enabled := p_Dim_Obj_Enabled;
2488: END IF;
2489:
2486: ELSE
2487: l_bis_dim_level_rec.Enabled := p_Dim_Obj_Enabled;
2488: END IF;
2489:
2490: IF( BIS_UTILITIES_PUB.Value_Not_Missing(p_hide) = FND_API.G_TRUE ) THEN
2491: l_bis_dim_level_rec.hide := p_hide;
2492: END IF;
2493:
2494: IF (l_bsc_dim_obj_rec.Bsc_Source = 'PMF') THEN
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;
2511: END IF;
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');
2526: FND_MESSAGE.SET_TOKEN('BIS_OBJ_LOV', l_bis_name);
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:
2541: l_bis_dim_level_rec.Default_Search := p_Search_Function;
2540:
2541: l_bis_dim_level_rec.Default_Search := p_Search_Function;
2542: l_bis_dim_level_rec.Drill_To_Form_Function := p_Drill_To_Form_Function;
2543: -- Primary_Dim will be used only ine case of LDT upload in update mode it should take as provided from PMD API.
2544: l_bis_dim_level_rec.Primary_Dim := FND_API.G_TRUE;
2545: BIS_DIMENSION_LEVEL_PUB.Update_Dimension_Level
2546: ( p_api_version => 1.0
2547: , p_commit => FND_API.G_FALSE
2548: , p_validation_level => FND_API.G_VALID_LEVEL_FULL
2543: -- Primary_Dim will be used only ine case of LDT upload in update mode it should take as provided from PMD API.
2544: l_bis_dim_level_rec.Primary_Dim := FND_API.G_TRUE;
2545: BIS_DIMENSION_LEVEL_PUB.Update_Dimension_Level
2546: ( p_api_version => 1.0
2547: , p_commit => FND_API.G_FALSE
2548: , p_validation_level => FND_API.G_VALID_LEVEL_FULL
2549: , p_Dimension_Level_Rec => l_bis_dim_level_rec
2550: , x_return_status => x_return_status
2551: , x_error_Tbl => l_error_tbl
2544: l_bis_dim_level_rec.Primary_Dim := FND_API.G_TRUE;
2545: BIS_DIMENSION_LEVEL_PUB.Update_Dimension_Level
2546: ( p_api_version => 1.0
2547: , p_commit => FND_API.G_FALSE
2548: , p_validation_level => FND_API.G_VALID_LEVEL_FULL
2549: , p_Dimension_Level_Rec => l_bis_dim_level_rec
2550: , x_return_status => x_return_status
2551: , x_error_Tbl => l_error_tbl
2552: );
2549: , p_Dimension_Level_Rec => l_bis_dim_level_rec
2550: , x_return_status => x_return_status
2551: , x_error_Tbl => l_error_tbl
2552: );
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);
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;
2563: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2564: END IF;
2565:
2559: x_msg_data := NULL;
2560: END IF;
2561: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2562: END IF;
2563: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2564: END IF;
2565:
2566: --CALL API TO UPDATE INTO FND_LOOKUP_VALUES
2567:
2626:
2627:
2628: FOR i IN 0..(l_MTab_Tbl.COUNT-1) LOOP
2629: BSC_BIS_KPI_MEAS_PUB.Delete_Dim_Objs_In_DSet
2630: ( p_commit => FND_API.G_FALSE
2631: , p_kpi_id => l_MTab_Tbl(i).p_kpi_id
2632: , p_dim_set_id => l_MTab_Tbl(i).p_dim_set_id
2633: , x_return_status => x_return_status
2634: , x_msg_count => x_msg_count
2633: , x_return_status => x_return_status
2634: , x_msg_count => x_msg_count
2635: , x_msg_data => x_msg_data
2636: );
2637: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2638: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2639: END IF;
2640: END LOOP;
2641: IF (l_bsc_dim_obj_rec.Bsc_Source = 'PMF') THEN
2634: , x_msg_count => x_msg_count
2635: , x_msg_data => x_msg_data
2636: );
2637: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2638: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2639: END IF;
2640: END LOOP;
2641: IF (l_bsc_dim_obj_rec.Bsc_Source = 'PMF') THEN
2642: l_level_table_name := l_bsc_dim_obj_rec.Bsc_Level_Name;
2642: l_level_table_name := l_bsc_dim_obj_rec.Bsc_Level_Name;
2643: l_bsc_dim_obj_rec.Bsc_Level_Name := l_bsc_dim_obj_rec.Bsc_Level_View_Name;
2644: END IF;
2645: BSC_DIMENSION_LEVELS_PUB.Update_Dim_Level
2646: ( p_commit => FND_API.G_FALSE
2647: , p_Dim_Level_Rec => l_bsc_dim_obj_rec
2648: , x_return_status => x_return_status
2649: , x_msg_count => x_msg_count
2650: , x_msg_data => x_msg_data
2648: , x_return_status => x_return_status
2649: , x_msg_count => x_msg_count
2650: , x_msg_data => x_msg_data
2651: );
2652: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2653: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2654: END IF;
2655: FOR i IN 0..(l_MTab_Tbl.COUNT-1) LOOP
2656: IF (l_struct_change) THEN
2649: , x_msg_count => x_msg_count
2650: , x_msg_data => x_msg_data
2651: );
2652: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2653: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2654: END IF;
2655: FOR i IN 0..(l_MTab_Tbl.COUNT-1) LOOP
2656: IF (l_struct_change) THEN
2657: BSC_BIS_KPI_MEAS_PUB.Create_Dim_Objs_In_DSet
2654: END IF;
2655: FOR i IN 0..(l_MTab_Tbl.COUNT-1) LOOP
2656: IF (l_struct_change) THEN
2657: BSC_BIS_KPI_MEAS_PUB.Create_Dim_Objs_In_DSet
2658: ( p_commit => FND_API.G_FALSE
2659: , p_kpi_id => l_MTab_Tbl(i).p_kpi_id
2660: , p_dim_set_id => l_MTab_Tbl(i).p_dim_set_id
2661: , x_return_status => x_return_status
2662: , x_msg_count => x_msg_count
2661: , x_return_status => x_return_status
2662: , x_msg_count => x_msg_count
2663: , x_msg_data => x_msg_data
2664: );
2665: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2666: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2667: END IF;
2668: ELSE
2669: BSC_BIS_KPI_MEAS_PUB.Create_Dim_Objs_In_DSet
2662: , x_msg_count => x_msg_count
2663: , x_msg_data => x_msg_data
2664: );
2665: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2666: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2667: END IF;
2668: ELSE
2669: BSC_BIS_KPI_MEAS_PUB.Create_Dim_Objs_In_DSet
2670: ( p_commit => FND_API.G_FALSE
2666: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2667: END IF;
2668: ELSE
2669: BSC_BIS_KPI_MEAS_PUB.Create_Dim_Objs_In_DSet
2670: ( p_commit => FND_API.G_FALSE
2671: , p_kpi_id => l_MTab_Tbl(i).p_kpi_id
2672: , p_dim_set_id => l_MTab_Tbl(i).p_dim_set_id
2673: , p_kpi_flag_change => BSC_DESIGNER_PVT.G_ActionFlag.Normal
2674: , x_return_status => x_return_status
2674: , x_return_status => x_return_status
2675: , x_msg_count => x_msg_count
2676: , x_msg_data => x_msg_data
2677: );
2678: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2679: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2680: END IF;
2681: END IF;
2682: END LOOP;
2675: , x_msg_count => x_msg_count
2676: , x_msg_data => x_msg_data
2677: );
2678: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2679: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2680: END IF;
2681: END IF;
2682: END LOOP;
2683: IF (l_bsc_dim_obj_rec.Bsc_Source = 'PMF') THEN
2681: END IF;
2682: END LOOP;
2683: IF (l_bsc_dim_obj_rec.Bsc_Source = 'PMF') THEN
2684: BSC_BIS_DIM_OBJ_PUB.Set_All_Enable_Flag
2685: ( p_commit => FND_API.G_FALSE
2686: , p_Dim_Obj_Short_Name => l_bis_dim_level_rec.Dimension_Level_Short_Name
2687: , p_All_Enabled => p_All_Enabled
2688: , x_return_status => x_return_status
2689: , x_msg_count => x_msg_count
2688: , x_return_status => x_return_status
2689: , x_msg_count => x_msg_count
2690: , x_msg_data => x_msg_data
2691: );
2692: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2693: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2694: END IF;
2695: END IF;
2696: BSC_BIS_DIM_OBJ_PUB.Assign_Unassign_Dimensions
2689: , x_msg_count => x_msg_count
2690: , x_msg_data => x_msg_data
2691: );
2692: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2693: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2694: END IF;
2695: END IF;
2696: BSC_BIS_DIM_OBJ_PUB.Assign_Unassign_Dimensions
2697: ( p_commit => FND_API.G_FALSE
2693: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2694: END IF;
2695: END IF;
2696: BSC_BIS_DIM_OBJ_PUB.Assign_Unassign_Dimensions
2697: ( p_commit => FND_API.G_FALSE
2698: , p_dim_obj_short_name => l_dim_obj_name
2699: , p_assign_dim_short_names => p_assign_dim_short_names
2700: , p_unassign_dim_short_names => p_unassign_dim_short_names
2701: , p_Restrict_Dim_Validate => p_Restrict_Dim_Validate
2702: , x_return_status => x_return_status
2703: , x_msg_count => x_msg_count
2704: , x_msg_data => x_msg_data
2705: );
2706: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2707: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2708: END IF;
2709: IF (l_bsc_dim_obj_rec.Bsc_Source = 'PMF') THEN
2710: BSC_BIS_DIM_OBJ_PUB.Set_All_Enable_Flag
2703: , x_msg_count => x_msg_count
2704: , x_msg_data => x_msg_data
2705: );
2706: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2707: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2708: END IF;
2709: IF (l_bsc_dim_obj_rec.Bsc_Source = 'PMF') THEN
2710: BSC_BIS_DIM_OBJ_PUB.Set_All_Enable_Flag
2711: ( p_commit => FND_API.G_FALSE
2707: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2708: END IF;
2709: IF (l_bsc_dim_obj_rec.Bsc_Source = 'PMF') THEN
2710: BSC_BIS_DIM_OBJ_PUB.Set_All_Enable_Flag
2711: ( p_commit => FND_API.G_FALSE
2712: , p_Dim_Obj_Short_Name => l_bis_dim_level_rec.Dimension_Level_Short_Name
2713: , p_All_Enabled => p_All_Enabled
2714: , x_return_status => x_return_status
2715: , x_msg_count => x_msg_count
2714: , x_return_status => x_return_status
2715: , x_msg_count => x_msg_count
2716: , x_msg_data => x_msg_data
2717: );
2718: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2719: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2720: END IF;
2721: ELSIF ((l_bsc_dim_obj_rec.Bsc_Source = 'BSC') AND (p_assign_dim_short_names IS NOT NULL) AND (p_unassign_dim_short_names IS NULL) AND (INSTR(p_assign_dim_short_names, ',') = 0)) THEN
2722: /* Set 'All' flag for BSC type dim objects (specific to Auto Generated RPD - reserved for future)
2715: , x_msg_count => x_msg_count
2716: , x_msg_data => x_msg_data
2717: );
2718: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2719: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2720: END IF;
2721: ELSIF ((l_bsc_dim_obj_rec.Bsc_Source = 'BSC') AND (p_assign_dim_short_names IS NOT NULL) AND (p_unassign_dim_short_names IS NULL) AND (INSTR(p_assign_dim_short_names, ',') = 0)) THEN
2722: /* Set 'All' flag for BSC type dim objects (specific to Auto Generated RPD - reserved for future)
2723: We are checking if p_assign_dim_short_names contains only 1 dimension
2725: we are safe not to update 'All' flag for other dimension-dim object
2726: relationships while coming from Dimension Designer.
2727: */
2728: BSC_BIS_DIM_OBJ_PUB.Set_Bsc_All_Enable_Flag
2729: ( p_commit => FND_API.G_FALSE
2730: , p_Dim_Obj_Short_Name => l_dim_obj_name
2731: , p_Dim_Short_Name => p_assign_dim_short_names
2732: , p_All_Enabled => p_All_Enabled
2733: , x_return_status => x_return_status
2733: , x_return_status => x_return_status
2734: , x_msg_count => x_msg_count
2735: , x_msg_data => x_msg_data
2736: );
2737: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2738: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2739: END IF;
2740: END IF;
2741:
2734: , x_msg_count => x_msg_count
2735: , x_msg_data => x_msg_data
2736: );
2737: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2738: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2739: END IF;
2740: END IF;
2741:
2742: l_primary_dim_sht_name := Retrieve_Primary_Dimension(l_dim_obj_name);
2749: , x_Return_Status => x_return_status
2750: , x_Msg_Count => x_msg_count
2751: , x_Msg_Data => x_msg_data
2752: );
2753: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2754: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2755: END IF;
2756: END IF;
2757:
2750: , x_Msg_Count => x_msg_count
2751: , x_Msg_Data => x_msg_data
2752: );
2753: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2754: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2755: END IF;
2756: END IF;
2757:
2758: -- Start Granular Locking
2761: , x_return_status => x_return_status
2762: , x_msg_count => x_msg_count
2763: , x_msg_data => x_msg_data
2764: );
2765: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2766: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2767: END IF;
2768: IF ((l_create_pmf) AND (l_bsc_dim_obj_rec.Bsc_Source = 'PMF')) THEN
2769: BSC_DIMENSION_LEVELS_PUB.Drop_Dim_Level_Tabs
2762: , x_msg_count => x_msg_count
2763: , x_msg_data => x_msg_data
2764: );
2765: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2766: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2767: END IF;
2768: IF ((l_create_pmf) AND (l_bsc_dim_obj_rec.Bsc_Source = 'PMF')) THEN
2769: BSC_DIMENSION_LEVELS_PUB.Drop_Dim_Level_Tabs
2770: ( p_Dim_Level_Rec => l_bsc_drop_tables
2771: , x_return_status => x_return_status
2772: , x_msg_count => x_msg_count
2773: , x_msg_data => x_msg_data
2774: );
2775: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2776: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2777: END IF;
2778: l_bsc_dim_obj_rec.Bsc_Level_Name := l_level_table_name;
2779: l_flag := BSC_BIS_DIM_OBJ_PUB.Create_Pmf_Views
2772: , x_msg_count => x_msg_count
2773: , x_msg_data => x_msg_data
2774: );
2775: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2776: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2777: END IF;
2778: l_bsc_dim_obj_rec.Bsc_Level_Name := l_level_table_name;
2779: l_flag := BSC_BIS_DIM_OBJ_PUB.Create_Pmf_Views
2780: ( p_Dim_Level_Rec => l_bsc_dim_obj_rec
2782: , x_msg_count => x_msg_count
2783: , x_msg_data => x_msg_data
2784: );
2785: IF (NOT l_flag) THEN
2786: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2787: END IF;
2788: END IF;
2789: IF ((l_create_bsc) AND (l_bsc_dim_obj_rec.Bsc_Source = 'BSC')) THEN
2790: l_flag := BSC_BIS_DIM_OBJ_PUB.Alter_Bsc_Master_Tabs
2794: , x_msg_count => x_msg_count
2795: , x_msg_data => x_msg_data
2796: );
2797: IF(NOT l_flag) THEN
2798: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2799: END IF;
2800: END IF;
2801: /*************************************************************
2802: When a dimension object is updated, it is possible to assign
2813: , x_return_status => x_return_status
2814: , x_msg_count => x_msg_count
2815: , x_msg_data => x_msg_data
2816: );
2817: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2818: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2819: END IF;
2820: END IF;
2821: IF (p_commit = FND_API.G_TRUE) THEN
2814: , x_msg_count => x_msg_count
2815: , x_msg_data => x_msg_data
2816: );
2817: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2818: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2819: END IF;
2820: END IF;
2821: IF (p_commit = FND_API.G_TRUE) THEN
2822: COMMIT;
2817: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2818: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2819: END IF;
2820: END IF;
2821: IF (p_commit = FND_API.G_TRUE) THEN
2822: COMMIT;
2823: END IF;
2824: x_return_status := FND_API.G_RET_STS_SUCCESS;
2825: EXCEPTION
2820: END IF;
2821: IF (p_commit = FND_API.G_TRUE) THEN
2822: COMMIT;
2823: END IF;
2824: x_return_status := FND_API.G_RET_STS_SUCCESS;
2825: EXCEPTION
2826: WHEN FND_API.G_EXC_ERROR THEN
2827: IF (c_Master_Level%ISOPEN) THEN
2828: CLOSE c_Master_Level;
2822: COMMIT;
2823: END IF;
2824: x_return_status := FND_API.G_RET_STS_SUCCESS;
2825: EXCEPTION
2826: WHEN FND_API.G_EXC_ERROR THEN
2827: IF (c_Master_Level%ISOPEN) THEN
2828: CLOSE c_Master_Level;
2829: END IF;
2830: ROLLBACK TO UpdateBSCDimObjectPMD;
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: );
2837: END IF;
2834: , p_count => x_msg_count
2835: , p_data => x_msg_data
2836: );
2837: END IF;
2838: x_return_status := FND_API.G_RET_STS_ERROR;
2839: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2840: IF (c_Master_Level%ISOPEN) THEN
2841: CLOSE c_Master_Level;
2842: END IF;
2835: , p_data => x_msg_data
2836: );
2837: END IF;
2838: x_return_status := FND_API.G_RET_STS_ERROR;
2839: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2840: IF (c_Master_Level%ISOPEN) THEN
2841: CLOSE c_Master_Level;
2842: END IF;
2843: ROLLBACK TO UpdateBSCDimObjectPMD;
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: );
2850: END IF;
2847: , p_count => x_msg_count
2848: , p_data => x_msg_data
2849: );
2850: END IF;
2851: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2852: WHEN NO_DATA_FOUND THEN
2853: IF (c_Master_Level%ISOPEN) THEN
2854: CLOSE c_Master_Level;
2855: END IF;
2853: IF (c_Master_Level%ISOPEN) THEN
2854: CLOSE c_Master_Level;
2855: END IF;
2856: ROLLBACK TO UpdateBSCDimObjectPMD;
2857: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2858: IF (x_msg_data IS NOT NULL) THEN
2859: x_msg_data := x_msg_data||' -> BSC_BIS_DIM_OBJ_PUB.Update_Dim_Object ';
2860: ELSE
2861: x_msg_data := SQLERRM||' at BSC_BIS_DIM_OBJ_PUB.Update_Dim_Object ';
2864: IF (c_Master_Level%ISOPEN) THEN
2865: CLOSE c_Master_Level;
2866: END IF;
2867: ROLLBACK TO UpdateBSCDimObjectPMD;
2868: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2869: IF (x_msg_data IS NOT NULL) THEN
2870: x_msg_data := x_msg_data||' -> BSC_BIS_DIM_OBJ_PUB.Update_Dim_Object ';
2871: ELSE
2872: x_msg_data := SQLERRM||' at BSC_BIS_DIM_OBJ_PUB.Update_Dim_Object ';
2876: /*********************************************************************************
2877: UPDATE DIMENSION
2878: *********************************************************************************/
2879: PROCEDURE Update_Dim_Object
2880: ( p_commit IN VARCHAR2 := FND_API.G_TRUE
2881: , p_dim_obj_short_name IN VARCHAR2
2882: , p_display_name IN VARCHAR2
2883: , p_application_id IN NUMBER
2884: , p_description IN VARCHAR2
2896: , p_dimension_values_order IN NUMBER
2897: , p_comparison_order IN NUMBER
2898: , p_time_stamp IN VARCHAR2 := NULL -- Granular Locking
2899: , p_Master_Level IN VARCHAR2 := NULL
2900: , p_Long_Lov IN VARCHAR2 := FND_API.G_FALSE
2901: , p_Search_Function IN VARCHAR2 := NULL
2902: , p_Dim_Obj_Enabled IN VARCHAR2 := FND_API.G_FALSE
2903: , p_View_Object_Name IN VARCHAR2 := NULL
2904: , p_Default_Values_Api IN VARCHAR2 := NULL
2898: , p_time_stamp IN VARCHAR2 := NULL -- Granular Locking
2899: , p_Master_Level IN VARCHAR2 := NULL
2900: , p_Long_Lov IN VARCHAR2 := FND_API.G_FALSE
2901: , p_Search_Function IN VARCHAR2 := NULL
2902: , p_Dim_Obj_Enabled IN VARCHAR2 := FND_API.G_FALSE
2903: , p_View_Object_Name IN VARCHAR2 := NULL
2904: , p_Default_Values_Api IN VARCHAR2 := NULL
2905: , p_All_Enabled IN NUMBER := NULL
2906: , p_Drill_To_Form_Function IN VARCHAR2 := NULL
2903: , p_View_Object_Name IN VARCHAR2 := NULL
2904: , p_Default_Values_Api IN VARCHAR2 := NULL
2905: , p_All_Enabled IN NUMBER := NULL
2906: , p_Drill_To_Form_Function IN VARCHAR2 := NULL
2907: , p_Hide IN VARCHAR2 := FND_API.G_FALSE
2908: , x_return_status OUT NOCOPY VARCHAR2
2909: , x_msg_count OUT NOCOPY NUMBER
2910: , x_msg_data OUT NOCOPY VARCHAR2
2911: ) IS
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
2918: , p_dim_obj_short_name => p_dim_obj_short_name
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
2918: , p_dim_obj_short_name => p_dim_obj_short_name
2919: , p_display_name => p_display_name
2920: , p_application_id => p_application_id
2921: , p_description => p_description
2947: , x_return_status => x_return_status
2948: , x_msg_count => x_msg_count
2949: , x_msg_data => x_msg_data
2950: );
2951: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2952: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2953: END IF;
2954: IF (p_commit = FND_API.G_TRUE) THEN
2955: COMMIT;
2948: , x_msg_count => x_msg_count
2949: , x_msg_data => x_msg_data
2950: );
2951: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2952: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2953: END IF;
2954: IF (p_commit = FND_API.G_TRUE) THEN
2955: COMMIT;
2956: END IF;
2950: );
2951: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2952: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2953: END IF;
2954: IF (p_commit = FND_API.G_TRUE) THEN
2955: COMMIT;
2956: END IF;
2957: x_return_status := FND_API.G_RET_STS_SUCCESS;
2958: EXCEPTION
2953: END IF;
2954: IF (p_commit = FND_API.G_TRUE) THEN
2955: COMMIT;
2956: END IF;
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
2955: COMMIT;
2956: END IF;
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
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;
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
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
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: );
2975: END IF;
2972: , p_count => x_msg_count
2973: , p_data => x_msg_data
2974: );
2975: END IF;
2976: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2977: WHEN NO_DATA_FOUND THEN
2978: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2979: IF (x_msg_data IS NOT NULL) THEN
2980: x_msg_data := x_msg_data||' -> BSC_BIS_DIM_OBJ_PUB.Update_Dim_Object ';
2974: );
2975: END IF;
2976: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2977: WHEN NO_DATA_FOUND THEN
2978: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2979: IF (x_msg_data IS NOT NULL) THEN
2980: x_msg_data := x_msg_data||' -> BSC_BIS_DIM_OBJ_PUB.Update_Dim_Object ';
2981: ELSE
2982: x_msg_data := SQLERRM||' at BSC_BIS_DIM_OBJ_PUB.Update_Dim_Object ';
2981: ELSE
2982: x_msg_data := SQLERRM||' at BSC_BIS_DIM_OBJ_PUB.Update_Dim_Object ';
2983: END IF;
2984: WHEN OTHERS THEN
2985: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2986: IF (x_msg_data IS NOT NULL) THEN
2987: x_msg_data := x_msg_data||' -> BSC_BIS_DIM_OBJ_PUB.Update_Dim_Object ';
2988: ELSE
2989: x_msg_data := SQLERRM||' at BSC_BIS_DIM_OBJ_PUB.Update_Dim_Object ';
2993: /*********************************************************************************
2994: UPDATE DIMENSION
2995: *********************************************************************************/
2996: PROCEDURE Update_Dim_Object
2997: ( p_commit IN VARCHAR2 := FND_API.G_TRUE
2998: , p_dim_obj_short_name IN VARCHAR2
2999: , p_display_name IN VARCHAR2
3000: , p_application_id IN NUMBER
3001: , p_description IN VARCHAR2
3014: , p_comparison_order IN NUMBER
3015: , p_assign_dim_short_names IN VARCHAR2
3016: , p_time_stamp IN VARCHAR2 := NULL -- Granular Locking
3017: , p_Master_Level IN VARCHAR2 := NULL
3018: , p_Long_Lov IN VARCHAR2 := FND_API.G_FALSE
3019: , p_Search_Function IN VARCHAR2 := NULL
3020: , p_Dim_Obj_Enabled IN VARCHAR2 := FND_API.G_FALSE
3021: , p_View_Object_Name IN VARCHAR2 := NULL
3022: , p_Default_Values_Api IN VARCHAR2 := NULL
3016: , p_time_stamp IN VARCHAR2 := NULL -- Granular Locking
3017: , p_Master_Level IN VARCHAR2 := NULL
3018: , p_Long_Lov IN VARCHAR2 := FND_API.G_FALSE
3019: , p_Search_Function IN VARCHAR2 := NULL
3020: , p_Dim_Obj_Enabled IN VARCHAR2 := FND_API.G_FALSE
3021: , p_View_Object_Name IN VARCHAR2 := NULL
3022: , p_Default_Values_Api IN VARCHAR2 := NULL
3023: , p_All_Enabled IN NUMBER := NULL
3024: , p_Drill_To_Form_Function IN VARCHAR2 := NULL
3022: , p_Default_Values_Api IN VARCHAR2 := NULL
3023: , p_All_Enabled IN NUMBER := NULL
3024: , p_Drill_To_Form_Function IN VARCHAR2 := NULL
3025: , p_Restrict_Dim_Validate IN VARCHAR2 := NULL
3026: , p_Hide IN VARCHAR2 := FND_API.G_FALSE
3027: , x_return_status OUT NOCOPY VARCHAR2
3028: , x_msg_count OUT NOCOPY NUMBER
3029: , x_msg_data OUT NOCOPY VARCHAR2
3030: ) IS
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');
3053: FND_MESSAGE.SET_TOKEN('FIELD_NAME', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DIM_OBJ_SHORT_NAME'), TRUE);
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
3059: 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
3076: IF (l_unas_dim_short_names IS NULL) THEN
3079: l_unas_dim_short_names := l_unas_dim_short_names||', '||cd.short_name;
3080: END IF;
3081: END LOOP;
3082: BSC_BIS_DIM_OBJ_PUB.Update_Dim_Object
3083: ( p_commit => FND_API.G_FALSE
3084: , p_dim_obj_short_name => p_dim_obj_short_name
3085: , p_display_name => p_display_name
3086: , p_application_id => p_application_id
3087: , p_description => p_description
3114: , x_return_status => x_return_status
3115: , x_msg_count => x_msg_count
3116: , x_msg_data => x_msg_data
3117: );
3118: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3119: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3120: END IF;
3121: /********************************************************
3122: Check no of independent dimension objects in dimension set
3115: , x_msg_count => x_msg_count
3116: , x_msg_data => x_msg_data
3117: );
3118: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3119: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3120: END IF;
3121: /********************************************************
3122: Check no of independent dimension objects in dimension set
3123: ********************************************************/
3128: , x_return_status => x_return_status
3129: , x_msg_count => x_msg_count
3130: , x_msg_data => x_msg_data
3131: );
3132: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3133: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3134: END IF;
3135: /********************************************************/
3136: IF (p_commit = FND_API.G_TRUE) THEN
3129: , x_msg_count => x_msg_count
3130: , x_msg_data => x_msg_data
3131: );
3132: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3133: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3134: END IF;
3135: /********************************************************/
3136: IF (p_commit = FND_API.G_TRUE) THEN
3137: COMMIT;
3132: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3133: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3134: END IF;
3135: /********************************************************/
3136: IF (p_commit = FND_API.G_TRUE) THEN
3137: COMMIT;
3138: END IF;
3139: x_return_status := FND_API.G_RET_STS_SUCCESS;
3140: EXCEPTION
3135: /********************************************************/
3136: IF (p_commit = FND_API.G_TRUE) THEN
3137: COMMIT;
3138: END IF;
3139: x_return_status := FND_API.G_RET_STS_SUCCESS;
3140: EXCEPTION
3141: WHEN FND_API.G_EXC_ERROR THEN
3142: IF (cr_bsc_dim_obj%ISOPEN) THEN
3143: CLOSE cr_bsc_dim_obj;
3137: COMMIT;
3138: END IF;
3139: x_return_status := FND_API.G_RET_STS_SUCCESS;
3140: EXCEPTION
3141: WHEN FND_API.G_EXC_ERROR THEN
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
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: );
3151: END IF;
3148: , p_count => x_msg_count
3149: , p_data => x_msg_data
3150: );
3151: END IF;
3152: x_return_status := FND_API.G_RET_STS_ERROR;
3153: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3154: IF (cr_bsc_dim_obj%ISOPEN) THEN
3155: CLOSE cr_bsc_dim_obj;
3156: END IF;
3149: , p_data => x_msg_data
3150: );
3151: END IF;
3152: x_return_status := FND_API.G_RET_STS_ERROR;
3153: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
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
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: );
3163: END IF;
3160: , p_count => x_msg_count
3161: , p_data => x_msg_data
3162: );
3163: END IF;
3164: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3165: WHEN NO_DATA_FOUND THEN
3166: IF (cr_bsc_dim_obj%ISOPEN) THEN
3167: CLOSE cr_bsc_dim_obj;
3168: END IF;
3165: WHEN NO_DATA_FOUND THEN
3166: IF (cr_bsc_dim_obj%ISOPEN) THEN
3167: CLOSE cr_bsc_dim_obj;
3168: END IF;
3169: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3170: IF (x_msg_data IS NOT NULL) THEN
3171: x_msg_data := x_msg_data||' -> BSC_BIS_DIM_OBJ_PUB.Update_Dim_Object ';
3172: ELSE
3173: x_msg_data := SQLERRM||' at BSC_BIS_DIM_OBJ_PUB.Update_Dim_Object ';
3175: WHEN OTHERS THEN
3176: IF (cr_bsc_dim_obj%ISOPEN) THEN
3177: CLOSE cr_bsc_dim_obj;
3178: END IF;
3179: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3180: IF (x_msg_data IS NOT NULL) THEN
3181: x_msg_data := x_msg_data||' -> BSC_BIS_DIM_OBJ_PUB.Update_Dim_Object ';
3182: ELSE
3183: x_msg_data := SQLERRM||' at BSC_BIS_DIM_OBJ_PUB.Update_Dim_Object ';
3198: Validations:
3199: 1. dimension must not be associated with any group.
3200: */
3201: PROCEDURE Delete_Dim_Object
3202: ( p_commit IN VARCHAR2 := FND_API.G_TRUE
3203: , p_dim_obj_short_name IN VARCHAR2
3204: , x_return_status OUT NOCOPY VARCHAR2
3205: , x_msg_count OUT NOCOPY NUMBER
3206: , x_msg_data OUT NOCOPY VARCHAR2
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
3231: FND_MESSAGE.SET_NAME('BSC','BSC_REQ_FIELD_MISSING');
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
3238: SELECT COUNT(1) INTO l_count 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
3252: , p_validation_level => FND_API.G_VALID_LEVEL_FULL
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
3252: , p_validation_level => FND_API.G_VALID_LEVEL_FULL
3253: , p_Dimension_Level_Rec => l_bis_dim_level_rec
3254: , x_return_status => x_return_status
3255: , x_error_Tbl => l_error_tbl
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
3252: , p_validation_level => FND_API.G_VALID_LEVEL_FULL
3253: , p_Dimension_Level_Rec => l_bis_dim_level_rec
3254: , x_return_status => x_return_status
3255: , x_error_Tbl => l_error_tbl
3256: );
3253: , p_Dimension_Level_Rec => l_bis_dim_level_rec
3254: , x_return_status => x_return_status
3255: , x_error_Tbl => l_error_tbl
3256: );
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);
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;
3267: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3268: END IF;
3269: l_delete_count := l_delete_count + 1;
3263: x_msg_data := NULL;
3264: END IF;
3265: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3266: END IF;
3267: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3268: END IF;
3269: l_delete_count := l_delete_count + 1;
3270: END IF;
3271:
3287: ,x_return_status => x_return_status
3288: ,x_msg_count => x_msg_count
3289: ,x_msg_data => x_msg_data
3290: ) ;
3291: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3292: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3293: END IF;
3294: -- Granular Locking END
3295: BSC_BIS_DIM_OBJ_PUB.validateBscDimensionToDelete
3288: ,x_msg_count => x_msg_count
3289: ,x_msg_data => x_msg_data
3290: ) ;
3291: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3292: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3293: END IF;
3294: -- Granular Locking END
3295: BSC_BIS_DIM_OBJ_PUB.validateBscDimensionToDelete
3296: ( p_dim_obj_short_name => p_dim_obj_short_name
3297: , x_return_status => x_return_status
3298: , x_msg_count => x_msg_count
3299: , x_msg_data => x_msg_data
3300: );
3301: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3302: RAISE FND_API.G_EXC_ERROR;
3303: END IF;
3304: IF (l_bsc_dim_obj_rec.Bsc_Source = 'BSC') THEN
3305: BSC_DESIGNER_PVT.Dim_Object_Change(l_bsc_dim_obj_rec.Bsc_Level_Id);
3298: , x_msg_count => x_msg_count
3299: , x_msg_data => x_msg_data
3300: );
3301: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3302: RAISE FND_API.G_EXC_ERROR;
3303: END IF;
3304: IF (l_bsc_dim_obj_rec.Bsc_Source = 'BSC') THEN
3305: BSC_DESIGNER_PVT.Dim_Object_Change(l_bsc_dim_obj_rec.Bsc_Level_Id);
3306: END IF;
3307: --remove association with unassigned Dimension if exists
3308: l_bsc_dimension_rec.Bsc_Dim_Level_Group_Short_Name := BSC_BIS_DIMENSION_PUB.Unassigned_Dim;
3309: l_bsc_dimension_rec.Bsc_Level_Id := l_bsc_dim_obj_rec.Bsc_Level_Id;
3310: BSC_DIMENSION_GROUPS_PUB.Delete_Dim_Levels_In_Group
3311: ( p_commit => FND_API.G_FALSE
3312: , p_Dim_Grp_Rec => l_bsc_dimension_rec
3313: , x_return_status => x_return_status
3314: , x_msg_count => x_msg_count
3315: , x_msg_data => x_msg_data
3313: , x_return_status => x_return_status
3314: , x_msg_count => x_msg_count
3315: , x_msg_data => x_msg_data
3316: );
3317: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3318: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3319: END IF;
3320: --CALL BSC API
3321: BSC_DIMENSION_LEVELS_PUB.Delete_Dim_Level
3314: , x_msg_count => x_msg_count
3315: , x_msg_data => x_msg_data
3316: );
3317: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3318: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3319: END IF;
3320: --CALL BSC API
3321: BSC_DIMENSION_LEVELS_PUB.Delete_Dim_Level
3322: ( p_commit => FND_API.G_FALSE
3318: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3319: END IF;
3320: --CALL BSC API
3321: BSC_DIMENSION_LEVELS_PUB.Delete_Dim_Level
3322: ( p_commit => FND_API.G_FALSE
3323: , p_Dim_Level_Rec => l_bsc_dim_obj_rec
3324: , x_return_status => x_return_status
3325: , x_msg_count => x_msg_count
3326: , x_msg_data => x_msg_data
3324: , x_return_status => x_return_status
3325: , x_msg_count => x_msg_count
3326: , x_msg_data => x_msg_data
3327: );
3328: IF ((x_return_status = FND_API.G_RET_STS_ERROR) OR (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR)) THEN
3329: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3330: END IF;
3331: /*************************************************************
3332: List Button validation.For a list button the condition is that
3325: , x_msg_count => x_msg_count
3326: , x_msg_data => x_msg_data
3327: );
3328: IF ((x_return_status = FND_API.G_RET_STS_ERROR) OR (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR)) THEN
3329: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3330: END IF;
3331: /*************************************************************
3332: List Button validation.For a list button the condition is that
3333: all the dimesnion sets within the tab (irrespective of whether they are
3344: , x_msg_count => x_msg_count
3345: , x_msg_data => x_msg_data
3346: );
3347:
3348: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3349: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3350: END IF;
3351: END IF;
3352: l_delete_count := l_delete_count + 1;
3345: , x_msg_data => x_msg_data
3346: );
3347:
3348: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3349: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3350: END IF;
3351: END IF;
3352: l_delete_count := l_delete_count + 1;
3353: /****************************************************
3356: environment.Since this file is the part of MD/DD ARU we have made the call to
3357: the PL/SQL procedure "BSC_PMA_APIS_PUB.sync_dimension_table" dynamic so that
3358: the package gets complied on the pure BIS409 enviornments.
3359: /****************************************************/
3360: IF(BIS_UTILITIES_PUB.Enable_Generated_Source_Report = FND_API.G_TRUE) THEN
3361: l_is_denorm_deleted := FND_API.G_TRUE;
3362: BEGIN
3363: l_Sql := 'BEGIN IF(BSC_PMA_APIS_PUB.sync_dimension_table (:2,:3,:4)) THEN :1 :=FND_API.G_TRUE; ELSE :1:=FND_API.G_FALSE; END IF;END;';
3364: EXECUTE IMMEDIATE l_Sql USING IN p_dim_obj_short_name,IN BIS_UTIL.G_DROP_TABLE,OUT x_msg_data,OUT l_is_denorm_deleted;
3357: the PL/SQL procedure "BSC_PMA_APIS_PUB.sync_dimension_table" dynamic so that
3358: the package gets complied on the pure BIS409 enviornments.
3359: /****************************************************/
3360: IF(BIS_UTILITIES_PUB.Enable_Generated_Source_Report = FND_API.G_TRUE) THEN
3361: l_is_denorm_deleted := FND_API.G_TRUE;
3362: BEGIN
3363: l_Sql := 'BEGIN IF(BSC_PMA_APIS_PUB.sync_dimension_table (:2,:3,:4)) THEN :1 :=FND_API.G_TRUE; ELSE :1:=FND_API.G_FALSE; END IF;END;';
3364: EXECUTE IMMEDIATE l_Sql USING IN p_dim_obj_short_name,IN BIS_UTIL.G_DROP_TABLE,OUT x_msg_data,OUT l_is_denorm_deleted;
3365: EXCEPTION
3359: /****************************************************/
3360: IF(BIS_UTILITIES_PUB.Enable_Generated_Source_Report = FND_API.G_TRUE) THEN
3361: l_is_denorm_deleted := FND_API.G_TRUE;
3362: BEGIN
3363: l_Sql := 'BEGIN IF(BSC_PMA_APIS_PUB.sync_dimension_table (:2,:3,:4)) THEN :1 :=FND_API.G_TRUE; ELSE :1:=FND_API.G_FALSE; END IF;END;';
3364: EXECUTE IMMEDIATE l_Sql USING IN p_dim_obj_short_name,IN BIS_UTIL.G_DROP_TABLE,OUT x_msg_data,OUT l_is_denorm_deleted;
3365: EXCEPTION
3366: WHEN OTHERS THEN
3367: NULL;
3365: EXCEPTION
3366: WHEN OTHERS THEN
3367: NULL;
3368: END;
3369: IF(l_is_denorm_deleted=FND_API.G_FALSE) THEN
3370: RAISE FND_API.G_EXC_ERROR;
3371: END IF;
3372: END IF;
3373: END IF;
3366: WHEN OTHERS THEN
3367: NULL;
3368: END;
3369: IF(l_is_denorm_deleted=FND_API.G_FALSE) THEN
3370: RAISE FND_API.G_EXC_ERROR;
3371: END IF;
3372: END IF;
3373: END IF;
3374: IF (l_delete_count = 0) THEN
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;
3382: END IF;
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;
3382: END IF;
3383: x_return_status := FND_API.G_RET_STS_SUCCESS;
3384:
3379: END IF;
3380: IF (p_commit = FND_API.G_TRUE) THEN
3381: COMMIT;
3382: END IF;
3383: x_return_status := FND_API.G_RET_STS_SUCCESS;
3384:
3385: EXCEPTION
3386: WHEN FND_API.G_EXC_ERROR THEN
3387: IF (c_bsc_levels%ISOPEN) THEN
3382: END IF;
3383: x_return_status := FND_API.G_RET_STS_SUCCESS;
3384:
3385: EXCEPTION
3386: WHEN FND_API.G_EXC_ERROR THEN
3387: IF (c_bsc_levels%ISOPEN) THEN
3388: CLOSE c_bsc_levels;
3389: END IF;
3390: ROLLBACK TO DeleteBSCDimObjectPMD;
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: );
3397: END IF;
3394: , p_count => x_msg_count
3395: , p_data => x_msg_data
3396: );
3397: END IF;
3398: x_return_status := FND_API.G_RET_STS_ERROR;
3399: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3400: IF (c_bsc_levels%ISOPEN) THEN
3401: CLOSE c_bsc_levels;
3402: END IF;
3395: , p_data => x_msg_data
3396: );
3397: END IF;
3398: x_return_status := FND_API.G_RET_STS_ERROR;
3399: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3400: IF (c_bsc_levels%ISOPEN) THEN
3401: CLOSE c_bsc_levels;
3402: END IF;
3403: ROLLBACK TO DeleteBSCDimObjectPMD;
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: );
3410: END IF;
3407: , p_count => x_msg_count
3408: , p_data => x_msg_data
3409: );
3410: END IF;
3411: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3412: WHEN NO_DATA_FOUND THEN
3413: IF (c_bsc_levels%ISOPEN) THEN
3414: CLOSE c_bsc_levels;
3415: END IF;
3413: IF (c_bsc_levels%ISOPEN) THEN
3414: CLOSE c_bsc_levels;
3415: END IF;
3416: ROLLBACK TO DeleteBSCDimObjectPMD;
3417: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3418: IF (x_msg_data IS NOT NULL) THEN
3419: x_msg_data := x_msg_data||' -> BSC_BIS_DIM_OBJ_PUB.Delete_Dim_Object ';
3420: ELSE
3421: x_msg_data := SQLERRM||' at BSC_BIS_DIM_OBJ_PUB.Delete_Dim_Object ';
3424: IF (c_bsc_levels%ISOPEN) THEN
3425: CLOSE c_bsc_levels;
3426: END IF;
3427: ROLLBACK TO DeleteBSCDimObjectPMD;
3428: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3429: IF (x_msg_data IS NOT NULL) THEN
3430: x_msg_data := x_msg_data||' -> BSC_BIS_DIM_OBJ_PUB.Delete_Dim_Object ';
3431: ELSE
3432: x_msg_data := SQLERRM||' at BSC_BIS_DIM_OBJ_PUB.Delete_Dim_Object ';
3436: /*********************************************************************************
3437: FUNCTION validateBscDimensionToDelete
3438: *********************************************************************************/
3439: PROCEDURE validateBscDimensionToDelete
3440: ( p_commit IN VARCHAR2 := FND_API.G_TRUE
3441: , p_dim_obj_short_name IN VARCHAR2
3442: , x_return_status OUT NOCOPY VARCHAR2
3443: , x_msg_count OUT NOCOPY NUMBER
3444: , x_msg_data OUT NOCOPY VARCHAR2
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
3458: FND_MESSAGE.SET_NAME('BSC','BSC_REQ_FIELD_MISSING');
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
3465: , name
3475: WHERE dim_level_id = l_bsc_dim_id;
3476: IF (l_edw_flag <> 1) THEN
3477: --this message is hard coded because it needs more investigation if it is really required
3478: x_msg_data := 'The dimension object can''t be deleted, since BSC_SYS_DIM_LEVELS_B.Edw_Flag <> 1';
3479: RAISE FND_API.G_EXC_ERROR;
3480: END IF;*/
3481:
3482: --check if the dimension is associated with any group, if so raise an exception
3483: SELECT COUNT(1) INTO l_count
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
3495: SELECT COUNT(1) INTO l_count
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:
3507: EXCEPTION
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:
3507: EXCEPTION
3508: WHEN FND_API.G_EXC_ERROR THEN
3509: IF (x_msg_data IS NULL) THEN
3504: END IF;
3505: x_return_status := FND_API.G_RET_STS_SUCCESS;
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
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;
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
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
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: );
3524: END IF;
3521: , p_count => x_msg_count
3522: , p_data => x_msg_data
3523: );
3524: END IF;
3525: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3526: WHEN NO_DATA_FOUND THEN
3527: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3528: IF (x_msg_data IS NOT NULL) THEN
3529: x_msg_data := x_msg_data||' -> BSC_BIS_DIM_OBJ_PUB.validateBscDimensionToDelete ';
3523: );
3524: END IF;
3525: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3526: WHEN NO_DATA_FOUND THEN
3527: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3528: IF (x_msg_data IS NOT NULL) THEN
3529: x_msg_data := x_msg_data||' -> BSC_BIS_DIM_OBJ_PUB.validateBscDimensionToDelete ';
3530: ELSE
3531: x_msg_data := SQLERRM||' at BSC_BIS_DIM_OBJ_PUB.validateBscDimensionToDelete ';
3530: ELSE
3531: x_msg_data := SQLERRM||' at BSC_BIS_DIM_OBJ_PUB.validateBscDimensionToDelete ';
3532: END IF;
3533: WHEN OTHERS THEN
3534: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3535: IF (x_msg_data IS NOT NULL) THEN
3536: x_msg_data := x_msg_data||' -> BSC_BIS_DIM_OBJ_PUB.validateBscDimensionToDelete ';
3537: ELSE
3538: x_msg_data := SQLERRM||' at BSC_BIS_DIM_OBJ_PUB.validateBscDimensionToDelete ';
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');
3655: FND_MSG_PUB.ADD;
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');
3660: FND_MESSAGE.SET_TOKEN('FIELD_NAME', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DIM_OBJ_ID'), TRUE);
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');
3666: FND_MESSAGE.SET_TOKEN('FIELD_NAME', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DIM_OBJ_TAB_NAME'), TRUE);
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
3672: p_Dim_Level_Rec.Bsc_Level_User_Key_Size := BSC_BIS_DIM_OBJ_PUB.Dim_Obj_Code_Default_Size; --default size
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));
3690: END IF;
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');
3701: FND_MESSAGE.SET_TOKEN('SQL_IDENT', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DIM_OBJ_TAB_NAME'), TRUE);
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
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:
3718: p_Dim_Level_Rec.Bsc_Level_View_Name := 'BSC_D_'||p_Dim_Level_Rec.Bsc_Level_Id||'_VL';
3722: , x_return_status => x_return_status
3723: , x_msg_count => x_msg_count
3724: , x_msg_data => x_msg_data
3725: );
3726: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3727: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3728: END IF;
3729:
3730: x_return_status := FND_API.G_RET_STS_SUCCESS;
3723: , x_msg_count => x_msg_count
3724: , x_msg_data => x_msg_data
3725: );
3726: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3727: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3728: END IF;
3729:
3730: x_return_status := FND_API.G_RET_STS_SUCCESS;
3731:
3726: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3727: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3728: END IF;
3729:
3730: x_return_status := FND_API.G_RET_STS_SUCCESS;
3731:
3732: RETURN TRUE;
3733: EXCEPTION
3734: WHEN FND_API.G_EXC_ERROR THEN
3730: x_return_status := FND_API.G_RET_STS_SUCCESS;
3731:
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
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: );
3741: END IF;
3738: , p_count => x_msg_count
3739: , p_data => x_msg_data
3740: );
3741: END IF;
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
3740: );
3741: END IF;
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
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: );
3751: END IF;
3748: , p_count => x_msg_count
3749: , p_data => x_msg_data
3750: );
3751: END IF;
3752: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3753: RETURN FALSE;
3754: WHEN NO_DATA_FOUND THEN
3755: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3756: IF (x_msg_data IS NOT NULL) THEN
3751: END IF;
3752: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3753: RETURN FALSE;
3754: WHEN NO_DATA_FOUND THEN
3755: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3756: IF (x_msg_data IS NOT NULL) THEN
3757: x_msg_data := x_msg_data||' -> BSC_BIS_DIM_OBJ_PUB.Initialize_Bsc_Recs ';
3758: ELSE
3759: x_msg_data := SQLERRM||' at BSC_BIS_DIM_OBJ_PUB.Initialize_Bsc_Recs ';
3759: x_msg_data := SQLERRM||' at BSC_BIS_DIM_OBJ_PUB.Initialize_Bsc_Recs ';
3760: END IF;
3761: RETURN FALSE;
3762: WHEN OTHERS THEN
3763: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3764: IF (x_msg_data IS NOT NULL) THEN
3765: x_msg_data := x_msg_data||' -> BSC_BIS_DIM_OBJ_PUB.Initialize_Bsc_Recs ';
3766: ELSE
3767: x_msg_data := SQLERRM||' at BSC_BIS_DIM_OBJ_PUB.Initialize_Bsc_Recs ';
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
3814: p_Dim_Level_Rec.Bsc_Level_Name_Column := 'VALUE';
3819: --Comented for Bug#4758995
3820: --p_Dim_Level_Rec.Bsc_Pk_Col := REPLACE(p_Dim_Level_Rec.Bsc_Level_Short_Name ,' ', '_');
3821:
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);
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');
3833: FND_MSG_PUB.ADD;
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
3838: p_Dim_Level_Rec.Bsc_Level_User_Key_Size := BSC_BIS_DIM_OBJ_PUB.Dim_Obj_Code_Default_Size; --default size
3841: p_Dim_Level_Rec.Bsc_Level_Disp_Key_Size := BSC_BIS_DIM_OBJ_PUB.Dim_Obj_Name_Default_Size; --default size
3842: END IF;
3843: IF (p_Dim_Level_Rec.Bsc_Level_Abbreviation IS NULL) THEN
3844: IF (p_Dim_Level_Rec.Bsc_Level_Short_Name IS NULL) THEN
3845: RAISE FND_API.G_EXC_ERROR;
3846: ELSE
3847: p_Dim_Level_Rec.Bsc_Level_Abbreviation := SUBSTR(REPLACE(p_Dim_Level_Rec.Bsc_Level_Short_Name, ' ', ''), 1, 8);
3848: END IF;
3849: END IF;
3854: , x_return_status => x_return_status
3855: , x_msg_count => x_msg_count
3856: , x_msg_data => x_msg_data
3857: );
3858: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3859: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3860: END IF;
3861:
3862:
3855: , x_msg_count => x_msg_count
3856: , x_msg_data => x_msg_data
3857: );
3858: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3859: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3860: END IF;
3861:
3862:
3863: IF (p_Dim_Level_Rec.Source = 'OLTP') 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
3923: FROM BSC_SYS_DIM_LEVELS_B
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;
3934: EXCEPTION
3931: END IF;
3932:
3933: RETURN TRUE;
3934: EXCEPTION
3935: WHEN FND_API.G_EXC_ERROR THEN
3936: IF (allTabCol1%ISOPEN) THEN
3937: CLOSE allTabCol1;
3938: END IF;
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: );
3948: END IF;
3945: , p_count => x_msg_count
3946: , p_data => x_msg_data
3947: );
3948: END IF;
3949: x_return_status := FND_API.G_RET_STS_ERROR;
3950: RETURN FALSE;
3951: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3952: IF (allTabCol1%ISOPEN) THEN
3953: CLOSE allTabCol1;
3947: );
3948: END IF;
3949: x_return_status := FND_API.G_RET_STS_ERROR;
3950: RETURN FALSE;
3951: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3952: IF (allTabCol1%ISOPEN) THEN
3953: CLOSE allTabCol1;
3954: END IF;
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: );
3964: END IF;
3961: , p_count => x_msg_count
3962: , p_data => x_msg_data
3963: );
3964: END IF;
3965: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3966: RETURN FALSE;
3967: WHEN NO_DATA_FOUND THEN
3968: IF (allTabCol1%ISOPEN) THEN
3969: CLOSE allTabCol1;
3970: END IF;
3971: IF (allTabCol2%ISOPEN) THEN
3972: CLOSE allTabCol2;
3973: END IF;
3974: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3975: IF (x_msg_data IS NOT NULL) THEN
3976: x_msg_data := x_msg_data||' -> BSC_BIS_DIM_OBJ_PUB.Initialize_Pmf_Recs ';
3977: ELSE
3978: x_msg_data := SQLERRM||' at BSC_BIS_DIM_OBJ_PUB.Initialize_Pmf_Recs ';
3984: END IF;
3985: IF (allTabCol2%ISOPEN) THEN
3986: CLOSE allTabCol2;
3987: END IF;
3988: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3989: IF (x_msg_data IS NOT NULL) THEN
3990: x_msg_data := x_msg_data||' -> BSC_BIS_DIM_OBJ_PUB.Initialize_Pmf_Recs ';
3991: ELSE
3992: x_msg_data := SQLERRM||' at BSC_BIS_DIM_OBJ_PUB.Initialize_Pmf_Recs ';
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;
4074: IF ((p_Dim_Level_Rec.Bsc_Source IS NULL) OR (p_Dim_Level_Rec.Bsc_Source <> 'PMF')) THEN
4071: SAVEPOINT CreateBSCPmfViewPMD;
4072:
4073: BSC_APPS.Init_Bsc_Apps;
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);
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');
4085: FND_MESSAGE.SET_TOKEN('FIELD_NAME', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DIM_OBJ_PK_COL'), TRUE);
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');
4091: FND_MESSAGE.SET_TOKEN('FIELD_NAME', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DIM_OBJ_COL'), TRUE);
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');
4097: FND_MESSAGE.SET_TOKEN('FIELD_NAME', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DIM_OBJ_VIEW_NAME'), TRUE);
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:
4103: /********** Please READ before chage this code **********
4109: ********* Please READ: ********** */
4110:
4111: -- Modified for Zero Code issue for MVs for Bug#3739872
4112: -- added if condition enableVarchar2Implementation for Bug#3836170
4113: IF (BSC_BIS_CUSTOM_KPI_UTIL_PUB.enableVarchar2Implementation = FND_API.G_TRUE) THEN
4114: -- This defenition is to support KPI END TO END
4115:
4116: FOR par_rec IN c_par_dim_ids LOOP
4117: l_Rel_Select1 := l_Rel_Select1 || ',' || par_rec.pk_col || ',' || par_rec.pk_col || '_USR';
4173: END IF;
4174:
4175: RETURN TRUE;
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
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: );
4185: END IF;
4182: , p_count => x_msg_count
4183: , p_data => x_msg_data
4184: );
4185: END IF;
4186: x_return_status := FND_API.G_RET_STS_ERROR;
4187: RETURN FALSE;
4188: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4189: ROLLBACK TO CreateBSCPmfViewPMD;
4190: IF (x_msg_data IS NULL) THEN
4184: );
4185: END IF;
4186: x_return_status := FND_API.G_RET_STS_ERROR;
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
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: );
4196: END IF;
4193: , p_count => x_msg_count
4194: , p_data => x_msg_data
4195: );
4196: END IF;
4197: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4198: RETURN FALSE;
4199: WHEN NO_DATA_FOUND THEN
4200: ROLLBACK TO CreateBSCPmfViewPMD;
4201: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4197: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4198: RETURN FALSE;
4199: WHEN NO_DATA_FOUND THEN
4200: ROLLBACK TO CreateBSCPmfViewPMD;
4201: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4202: IF (x_msg_data IS NOT NULL) THEN
4203: x_msg_data := x_msg_data||' -> BSC_BIS_DIM_OBJ_PUB.Create_Pmf_Views ';
4204: ELSE
4205: x_msg_data := SQLERRM||' at BSC_BIS_DIM_OBJ_PUB.Create_Pmf_Views ';
4206: END IF;
4207: RETURN FALSE;
4208: WHEN OTHERS THEN
4209: ROLLBACK TO CreateBSCPmfViewPMD;
4210: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4211: IF (x_msg_data IS NOT NULL) THEN
4212: x_msg_data := x_msg_data||' -> BSC_BIS_DIM_OBJ_PUB.Create_Pmf_Views ';
4213: ELSE
4214: x_msg_data := SQLERRM||' at BSC_BIS_DIM_OBJ_PUB.Create_Pmf_Views ';
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;
4255: IF ((p_Dim_Level_Rec.Bsc_Source IS NULL) OR (p_Dim_Level_Rec.Bsc_Source <> 'BSC')) THEN
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');
4262: FND_MESSAGE.SET_TOKEN('FIELD_NAME', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DIM_OBJ_ID'), TRUE);
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');
4268: FND_MESSAGE.SET_TOKEN('FIELD_NAME', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DIM_OBJ_TAB_NAME'), TRUE);
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');
4274: FND_MESSAGE.SET_TOKEN('FIELD_NAME', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DIM_OBJ_VIEW_NAME'), TRUE);
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);
4280: END IF;
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;
4296:
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:
4320: l_sql_stmt := ' SELECT COUNT(1) FROM USER_OBJECTS '||
4443: INSERT INTO BSC_DB_TABLES
4444: (Table_Name, Table_Type, Periodicity_Id,
4445: Source_Data_Type, Source_File_Name)
4446: VALUES (l_input_table, 2, 0, 0, NULL);
4447: x_return_status := FND_API.G_RET_STS_SUCCESS;
4448:
4449: RETURN TRUE;
4450: EXCEPTION
4451: WHEN FND_API.G_EXC_ERROR THEN
4447: x_return_status := FND_API.G_RET_STS_SUCCESS;
4448:
4449: RETURN TRUE;
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
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: );
4459: END IF;
4457: , p_data => x_msg_data
4458: );
4459: END IF;
4460:
4461: x_return_status := FND_API.G_RET_STS_ERROR;
4462:
4463: -- try to rollback ddl stmts
4464: BSC_APPS.Execute_DDL_Stmts_AT(l_rollback_ddl_stmts, l_num_rollback_ddl_stmts, BSC_APPS.fnd_apps_schema, BSC_APPS.bsc_apps_short_name);
4465:
4463: -- try to rollback ddl stmts
4464: BSC_APPS.Execute_DDL_Stmts_AT(l_rollback_ddl_stmts, l_num_rollback_ddl_stmts, BSC_APPS.fnd_apps_schema, BSC_APPS.bsc_apps_short_name);
4465:
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
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: );
4475: END IF;
4472: , p_count => x_msg_count
4473: , p_data => x_msg_data
4474: );
4475: END IF;
4476: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4477:
4478:
4479: -- try to rollback ddl stmts
4480: BSC_APPS.Execute_DDL_Stmts_AT(l_rollback_ddl_stmts, l_num_rollback_ddl_stmts, BSC_APPS.fnd_apps_schema, BSC_APPS.bsc_apps_short_name);
4481:
4482: RETURN FALSE;
4483: WHEN NO_DATA_FOUND THEN
4484: ROLLBACK TO CreateBSCMasTabsPMD;
4485: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4486: IF (x_msg_data IS NOT NULL) THEN
4487: x_msg_data := x_msg_data||' -> BSC_BIS_DIM_OBJ_PUB.Create_Bsc_Master_Tabs ';
4488: ELSE
4489: x_msg_data := SQLERRM||' at BSC_BIS_DIM_OBJ_PUB.Create_Bsc_Master_Tabs ';
4493: BSC_APPS.Execute_DDL_Stmts_AT(l_rollback_ddl_stmts, l_num_rollback_ddl_stmts, BSC_APPS.fnd_apps_schema, BSC_APPS.bsc_apps_short_name);
4494:
4495: RETURN FALSE;
4496: WHEN OTHERS THEN
4497: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4498: IF (x_msg_data IS NOT NULL) THEN
4499: x_msg_data := x_msg_data||' -> BSC_BIS_DIM_OBJ_PUB.Create_Bsc_Master_Tabs ';
4500: ELSE
4501: x_msg_data := SQLERRM||' at BSC_BIS_DIM_OBJ_PUB.Create_Bsc_Master_Tabs ';
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;
4574: IF ((p_Dim_Level_Rec.Bsc_Source IS NULL) OR (p_Dim_Level_Rec.Bsc_Source <> 'BSC')) THEN
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');
4581: FND_MESSAGE.SET_TOKEN('FIELD_NAME', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DIM_OBJ_ID'), TRUE);
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');
4587: FND_MESSAGE.SET_TOKEN('FIELD_NAME', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DIM_OBJ_TAB_NAME'), TRUE);
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');
4593: FND_MESSAGE.SET_TOKEN('FIELD_NAME', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DIM_OBJ_VIEW_NAME'), TRUE);
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;
4608: l_view_name := 'BSC_D_'||p_Dim_Level_Rec.Bsc_Level_Id||'_VL';
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
4622: l_flag := TRUE;
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
4655: UPDATE BSC_DB_TABLES_COLS
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:
4675: l_sql_stmt := 'ALTER TABLE '||p_Dim_Level_Rec_Old.Bsc_Level_Name||
4774: , x_msg_count => x_msg_count
4775: , x_msg_data => x_msg_data
4776: );
4777: IF (NOT l_flag) THEN
4778: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4779: END IF;
4780: l_flag := BSC_BIS_DIM_OBJ_PUB.Alter_One_By_N_Tables
4781: ( p_Dim_Level_Rec => p_Dim_Level_Rec
4782: , p_Dim_Level_Rec_Old => p_Dim_Level_Rec_Old
4784: , x_msg_count => x_msg_count
4785: , x_msg_data => x_msg_data
4786: );
4787: IF (NOT l_flag) THEN
4788: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4789: END IF;
4790: RETURN TRUE;
4791: END IF;
4792: EXCEPTION
4793: WHEN e_mlog_exception THEN
4794: ROLLBACK TO AlterBSCMasTabsPMD;
4795: x_msg_data := NULL;
4796: x_msg_data := l_error_msg || ' -> BSC_BIS_DIM_OBJ_PUB.Alter_Bsc_Master_Tabs';
4797: x_return_status := FND_API.G_RET_STS_ERROR;
4798: RETURN FALSE;
4799:
4800: WHEN FND_API.G_EXC_ERROR THEN
4801: ROLLBACK TO AlterBSCMasTabsPMD;
4796: x_msg_data := l_error_msg || ' -> BSC_BIS_DIM_OBJ_PUB.Alter_Bsc_Master_Tabs';
4797: x_return_status := FND_API.G_RET_STS_ERROR;
4798: RETURN FALSE;
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
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: );
4808: END IF;
4805: , p_count => x_msg_count
4806: , p_data => x_msg_data
4807: );
4808: END IF;
4809: x_return_status := FND_API.G_RET_STS_ERROR;
4810: RETURN FALSE;
4811: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4812: ROLLBACK TO AlterBSCMasTabsPMD;
4813: IF (x_msg_data IS NULL) THEN
4807: );
4808: END IF;
4809: x_return_status := FND_API.G_RET_STS_ERROR;
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
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: );
4819: END IF;
4816: , p_count => x_msg_count
4817: , p_data => x_msg_data
4818: );
4819: END IF;
4820: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4821: RETURN FALSE;
4822: WHEN NO_DATA_FOUND THEN
4823: ROLLBACK TO AlterBSCMasTabsPMD;
4824: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4820: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4821: RETURN FALSE;
4822: WHEN NO_DATA_FOUND THEN
4823: ROLLBACK TO AlterBSCMasTabsPMD;
4824: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4825: IF (x_msg_data IS NOT NULL) THEN
4826: x_msg_data := x_msg_data||' -> BSC_BIS_DIM_OBJ_PUB.Alter_Bsc_Master_Tabs ';
4827: ELSE
4828: x_msg_data := SQLERRM||' at BSC_BIS_DIM_OBJ_PUB.Alter_Bsc_Master_Tabs ';
4829: END IF;
4830: RETURN FALSE;
4831: WHEN OTHERS THEN
4832: ROLLBACK TO AlterBSCMasTabsPMD;
4833: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4834: IF (x_msg_data IS NOT NULL) THEN
4835: x_msg_data := x_msg_data||' -> BSC_BIS_DIM_OBJ_PUB.Alter_Bsc_Master_Tabs ';
4836: ELSE
4837: x_msg_data := SQLERRM||' at BSC_BIS_DIM_OBJ_PUB.Alter_Bsc_Master_Tabs ';
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;
4873: IF ((p_Dim_Level_Rec.Bsc_Level_User_Key_Size = p_Dim_Level_Rec_Old.Bsc_Level_User_Key_Size) AND
4897:
4898: EXECUTE IMMEDIATE l_sql_stmt INTO l_count USING l_input_table;
4899: IF (l_count = 0) THEN
4900: x_msg_data := 'Input Table does not exists '||l_input_table;
4901: RAISE FND_API.G_EXC_ERROR;
4902: END IF;
4903: --if abbreviation is different than drop and create master tables
4904: IF (UPPER(p_Dim_Level_Rec.Bsc_Level_Abbreviation) <> UPPER(p_Dim_Level_Rec_Old.Bsc_Level_Abbreviation)) THEN
4905: IF (UPPER(p_Dim_Level_Rec_Old.Bsc_Level_Abbreviation) > UPPER(l_abbreviation)) THEN
4921:
4922: EXECUTE IMMEDIATE l_sql_stmt INTO l_count USING l_old_mas_name;
4923: IF (l_count = 0) THEN
4924: x_msg_data := 'Master Table does not exists '||l_old_mas_name;
4925: RAISE FND_API.G_EXC_ERROR;
4926: END IF;
4927: l_sql_stmt := ' CREATE TABLE '||l_master_table||' '||' TABLESPACE '||
4928: BSC_APPS.Get_Tablespace_Name(BSC_APPS.Dimension_Table_Tbs_Type)||' '||BSC_APPS.bsc_storage_clause||
4929: ' AS SELECT * FROM '||l_old_mas_name;
4942: , x_msg_count => x_msg_count
4943: , x_msg_data => x_msg_data
4944: );
4945:
4946: IF(x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
4947: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4948: END IF;
4949:
4950: l_sql_stmt := 'DROP TABLE '||l_old_mas_name;
4943: , x_msg_data => x_msg_data
4944: );
4945:
4946: IF(x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
4947: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4948: END IF;
4949:
4950: l_sql_stmt := 'DROP TABLE '||l_old_mas_name;
4951: -- -----
5048: WHEN e_mlog_exception THEN
5049: ROLLBACK TO AlterBSCMxNPMD;
5050: x_msg_data := NULL;
5051: x_msg_data := l_error_msg || ' -> BSC_BIS_DIM_OBJ_PUB.Alter_M_By_N_Tables ';
5052: x_return_status := FND_API.G_RET_STS_ERROR;
5053: RETURN FALSE;
5054: WHEN FND_API.G_EXC_ERROR THEN
5055: ROLLBACK TO AlterBSCMxNPMD;
5056: IF (x_msg_data IS NULL) THEN
5050: x_msg_data := NULL;
5051: x_msg_data := l_error_msg || ' -> BSC_BIS_DIM_OBJ_PUB.Alter_M_By_N_Tables ';
5052: x_return_status := FND_API.G_RET_STS_ERROR;
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
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: );
5062: END IF;
5059: , p_count => x_msg_count
5060: , p_data => x_msg_data
5061: );
5062: END IF;
5063: x_return_status := FND_API.G_RET_STS_ERROR;
5064: RETURN FALSE;
5065: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5066: ROLLBACK TO AlterBSCMxNPMD;
5067: IF (x_msg_data IS NULL) THEN
5061: );
5062: END IF;
5063: x_return_status := FND_API.G_RET_STS_ERROR;
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
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: );
5073: END IF;
5070: , p_count => x_msg_count
5071: , p_data => x_msg_data
5072: );
5073: END IF;
5074: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5075: RETURN FALSE;
5076: WHEN NO_DATA_FOUND THEN
5077: ROLLBACK TO AlterBSCMxNPMD;
5078: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5074: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5075: RETURN FALSE;
5076: WHEN NO_DATA_FOUND THEN
5077: ROLLBACK TO AlterBSCMxNPMD;
5078: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5079: IF (x_msg_data IS NOT NULL) THEN
5080: x_msg_data := x_msg_data||' -> BSC_BIS_DIM_OBJ_PUB.Alter_M_By_N_Tables ';
5081: ELSE
5082: x_msg_data := SQLERRM||' at BSC_BIS_DIM_OBJ_PUB.Alter_M_By_N_Tables ';
5083: END IF;
5084: RETURN FALSE;
5085: WHEN OTHERS THEN
5086: ROLLBACK TO AlterBSCMxNPMD;
5087: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5088: IF (x_msg_data IS NOT NULL) THEN
5089: x_msg_data := x_msg_data||' -> BSC_BIS_DIM_OBJ_PUB.Alter_M_By_N_Tables ';
5090: ELSE
5091: x_msg_data := SQLERRM||' at BSC_BIS_DIM_OBJ_PUB.Alter_M_By_N_Tables ';
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;
5149: IF ((p_Dim_Level_Rec.Bsc_Level_User_Key_Size = p_Dim_Level_Rec_Old.Bsc_Level_User_Key_Size) AND
5167:
5168: EXECUTE IMMEDIATE l_sql_stmt INTO l_count USING l_master_table;
5169: IF (l_count = 0) THEN
5170: x_msg_data := 'Master Table does not exists '||l_master_table;
5171: RAISE FND_API.G_EXC_ERROR;
5172: END IF;
5173:
5174: l_sql_stmt := ' SELECT COUNT(1) FROM USER_OBJECTS '||
5175: ' WHERE OBJECT_NAME = :1';
5176:
5177: EXECUTE IMMEDIATE l_sql_stmt INTO l_count USING l_input_table;
5178: IF (l_count = 0) THEN
5179: x_msg_data := 'Input Table does not exists '||l_input_table;
5180: RAISE FND_API.G_EXC_ERROR;
5181: END IF;
5182: --if level_pk_col is different than change the names of table columns
5183: IF (UPPER(p_Dim_Level_Rec.Bsc_Pk_Col) <> UPPER(p_Dim_Level_Rec_Old.Bsc_Pk_Col)) THEN
5184: l_Create_View := TRUE;
5372: END IF;
5373: ROLLBACK TO AlterBSCOnexNPMD;
5374: x_msg_data := NULL;
5375: x_msg_data := l_error_msg || ' -> BSC_BIS_DIM_OBJ_PUB.Alter_One_By_N_Tables ';
5376: x_return_status := FND_API.G_RET_STS_ERROR;
5377: RETURN FALSE;
5378: WHEN FND_API.G_EXC_ERROR THEN
5379: IF (c_Parent_Index_Name%ISOPEN) THEN
5380: CLOSE c_Parent_Index_Name;
5374: x_msg_data := NULL;
5375: x_msg_data := l_error_msg || ' -> BSC_BIS_DIM_OBJ_PUB.Alter_One_By_N_Tables ';
5376: x_return_status := FND_API.G_RET_STS_ERROR;
5377: RETURN FALSE;
5378: WHEN FND_API.G_EXC_ERROR THEN
5379: IF (c_Parent_Index_Name%ISOPEN) THEN
5380: CLOSE c_Parent_Index_Name;
5381: END IF;
5382: ROLLBACK TO AlterBSCOnexNPMD;
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: );
5389: END IF;
5386: , p_count => x_msg_count
5387: , p_data => x_msg_data
5388: );
5389: END IF;
5390: x_return_status := FND_API.G_RET_STS_ERROR;
5391: RETURN FALSE;
5392: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5393: IF (c_Parent_Index_Name%ISOPEN) THEN
5394: CLOSE c_Parent_Index_Name;
5388: );
5389: END IF;
5390: x_return_status := FND_API.G_RET_STS_ERROR;
5391: RETURN FALSE;
5392: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5393: IF (c_Parent_Index_Name%ISOPEN) THEN
5394: CLOSE c_Parent_Index_Name;
5395: END IF;
5396: ROLLBACK TO AlterBSCOnexNPMD;
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: );
5403: END IF;
5400: , p_count => x_msg_count
5401: , p_data => x_msg_data
5402: );
5403: END IF;
5404: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5405: RETURN FALSE;
5406: WHEN NO_DATA_FOUND THEN
5407: IF (c_Parent_Index_Name%ISOPEN) THEN
5408: CLOSE c_Parent_Index_Name;
5407: IF (c_Parent_Index_Name%ISOPEN) THEN
5408: CLOSE c_Parent_Index_Name;
5409: END IF;
5410: ROLLBACK TO AlterBSCOnexNPMD;
5411: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5412: IF (x_msg_data IS NOT NULL) THEN
5413: x_msg_data := x_msg_data||' -> BSC_BIS_DIM_OBJ_PUB.Alter_One_By_N_Tables ';
5414: ELSE
5415: x_msg_data := SQLERRM||' at BSC_BIS_DIM_OBJ_PUB.Alter_One_By_N_Tables ';
5419: IF (c_Parent_Index_Name%ISOPEN) THEN
5420: CLOSE c_Parent_Index_Name;
5421: END IF;
5422: ROLLBACK TO AlterBSCOnexNPMD;
5423: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5424: IF (x_msg_data IS NOT NULL) THEN
5425: x_msg_data := x_msg_data||' -> BSC_BIS_DIM_OBJ_PUB.Alter_One_By_N_Tables ';
5426: ELSE
5427: x_msg_data := SQLERRM||' at BSC_BIS_DIM_OBJ_PUB.Alter_One_By_N_Tables ';
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
5714: /************************************************************/
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;
5736:
5735: END IF;
5736:
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
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;
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
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: );
5755: END IF;
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;
5860: END IF;
5865: CLOSE cr_kpidimset_dim;
5866: END IF;
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
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: );
5876: END IF;
6074:
6075: END LOOP;
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
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;
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
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
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: );
6094: END IF;
6091: , p_count => x_msg_count
6092: , p_data => x_msg_data
6093: );
6094: END IF;
6095: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6096: WHEN OTHERS THEN
6097: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6098: IF (x_msg_data IS NOT NULL) THEN
6099: x_msg_data := x_msg_data||' -> BSC_BIS_DIM_OBJ_PUB.Refresh_BSC_PMF_Dim_View';
6093: );
6094: END IF;
6095: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6096: WHEN OTHERS THEN
6097: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6098: IF (x_msg_data IS NOT NULL) THEN
6099: x_msg_data := x_msg_data||' -> BSC_BIS_DIM_OBJ_PUB.Refresh_BSC_PMF_Dim_View';
6100: ELSE
6101: x_msg_data := SQLERRM||' at BSC_BIS_DIM_OBJ_PUB.Refresh_BSC_PMF_Dim_View';
6132: BSC_APPS.Do_Ddl_AT(l_sql, ad_ddl.drop_view, PMF_EDW.Level_Table_Name, BSC_APPS.fnd_apps_schema, BSC_APPS.bsc_apps_short_name);
6133: END LOOP;
6134: EXCEPTION
6135: WHEN OTHERS THEN
6136: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6137: IF (x_msg_data IS NOT NULL) THEN
6138: x_msg_data := x_msg_data||' -> BSC_BIS_DIM_OBJ_PUB.Remove_BSC_PMF_EDW_Dim_View';
6139: ELSE
6140: x_msg_data := SQLERRM||' at BSC_BIS_DIM_OBJ_PUB.Remove_BSC_PMF_EDW_Dim_View';
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
6158: , x_return_status => x_return_status
6171:
6172: -- This is called from concurrent programs and upgrade scripts
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
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;
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
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
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: );
6191: END IF;
6188: , p_count => x_msg_count
6189: , p_data => x_msg_data
6190: );
6191: END IF;
6192: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6193: WHEN NO_DATA_FOUND THEN
6194: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6195: IF (x_msg_data IS NOT NULL) THEN
6196: x_msg_data := x_msg_data||' -> BSC_BIS_DIM_OBJ_PUB.Init_Create_Pmf_Recs ';
6190: );
6191: END IF;
6192: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6193: WHEN NO_DATA_FOUND THEN
6194: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6195: IF (x_msg_data IS NOT NULL) THEN
6196: x_msg_data := x_msg_data||' -> BSC_BIS_DIM_OBJ_PUB.Init_Create_Pmf_Recs ';
6197: ELSE
6198: x_msg_data := SQLERRM||' at BSC_BIS_DIM_OBJ_PUB.Init_Create_Pmf_Recs ';
6197: ELSE
6198: x_msg_data := SQLERRM||' at BSC_BIS_DIM_OBJ_PUB.Init_Create_Pmf_Recs ';
6199: END IF;
6200: WHEN OTHERS THEN
6201: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6202: IF (x_msg_data IS NOT NULL) THEN
6203: x_msg_data := x_msg_data||' -> BSC_BIS_DIM_OBJ_PUB.Init_Create_Pmf_Recs ';
6204: ELSE
6205: x_msg_data := SQLERRM||' at BSC_BIS_DIM_OBJ_PUB.Init_Create_Pmf_Recs ';
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
6314:
6311: END IF;
6312:
6313: EXCEPTION
6314:
6315: WHEN FND_API.G_EXC_ERROR THEN
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
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: );
6325: END IF;
6323: , p_data => x_msg_data
6324: );
6325: END IF;
6326:
6327: x_return_status := FND_API.G_RET_STS_ERROR;
6328: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
6329: IF (cr_kpi_dimset_dimobj%ISOPEN) THEN
6330: CLOSE cr_kpi_dimset_dimobj;
6331: END IF;
6324: );
6325: END IF;
6326:
6327: x_return_status := FND_API.G_RET_STS_ERROR;
6328: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
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
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: );
6338: END IF;
6335: , p_count => x_msg_count
6336: , p_data => x_msg_data
6337: );
6338: END IF;
6339: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6340:
6341: WHEN OTHERS THEN
6342: IF (cr_kpi_dimset_dimobj%ISOPEN) THEN
6343: CLOSE cr_kpi_dimset_dimobj;
6341: WHEN OTHERS THEN
6342: IF (cr_kpi_dimset_dimobj%ISOPEN) THEN
6343: CLOSE cr_kpi_dimset_dimobj;
6344: END IF;
6345: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6346: IF (x_msg_data IS NOT NULL) THEN
6347: x_msg_data := x_msg_data||' -> BSC_BIS_DIM_OBJ_PUB.Update_Dim_Object ';
6348: ELSE
6349: x_msg_data := SQLERRM||' at BSC_BIS_DIM_OBJ_PUB.Update_Dim_Object ';
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;
6425:
6433: l_Count := l_Count + 1;
6434:
6435: END LOOP;
6436:
6437: l_Parent_Exists := FND_API.G_FALSE;
6438:
6439: IF (l_Count = 0) THEN
6440: IF (p_Dim_Obj_View_Name IS NOT NULL) THEN
6441: l_View_Name := p_Dim_Obj_View_Name;
6450: l_View_Name := NULL;
6451: END;
6452: END IF;
6453: ELSIF (l_Count > 0) THEN
6454: l_Parent_Exists := FND_API.G_TRUE;
6455: END IF;
6456:
6457: IF (p_Dim_Obj_Short_Name = 'HRI_PER_USRDR_H') THEN
6458: l_View_Return_Value := BSC_BIS_DIM_OBJ_PUB.Validate_PMF_Base_View_Mgr(
6469: END IF;
6470:
6471: IF (l_View_Return_Value = C_TABLE_NOT_EXIST) THEN
6472: x_Msg_Data := 'BSC_DIM_VIEW_NOT_EXIST';
6473: RAISE FND_API.G_EXC_ERROR;
6474: ELSIF (l_View_Return_Value = C_UNKNOWN_ERROR) THEN
6475: x_Msg_Data := 'BSC_DIM_VIEW_NOT_EXIST';
6476: RAISE FND_API.G_EXC_ERROR;
6477: ELSIF (l_View_Return_Value = C_COLUMN_NOT_EXIST) THEN
6472: x_Msg_Data := 'BSC_DIM_VIEW_NOT_EXIST';
6473: RAISE FND_API.G_EXC_ERROR;
6474: ELSIF (l_View_Return_Value = C_UNKNOWN_ERROR) THEN
6475: x_Msg_Data := 'BSC_DIM_VIEW_NOT_EXIST';
6476: RAISE FND_API.G_EXC_ERROR;
6477: ELSIF (l_View_Return_Value = C_COLUMN_NOT_EXIST) THEN
6478: x_Msg_Data := 'BSC_DIM_VIEW_INVALID';
6479: RAISE FND_API.G_EXC_ERROR;
6480: END IF;
6475: x_Msg_Data := 'BSC_DIM_VIEW_NOT_EXIST';
6476: RAISE FND_API.G_EXC_ERROR;
6477: ELSIF (l_View_Return_Value = C_COLUMN_NOT_EXIST) THEN
6478: x_Msg_Data := 'BSC_DIM_VIEW_INVALID';
6479: RAISE FND_API.G_EXC_ERROR;
6480: END IF;
6481:
6482:
6483: EXCEPTION
6480: END IF;
6481:
6482:
6483: EXCEPTION
6484: WHEN FND_API.G_EXC_ERROR THEN
6485: x_Return_Status := FND_API.G_RET_STS_ERROR;
6486: WHEN OTHERS THEN
6487: x_Msg_Data := 'BSC_DIM_VIEW_NOT_EXIST';
6488: x_Return_Status := FND_API.G_RET_STS_ERROR;
6481:
6482:
6483: EXCEPTION
6484: WHEN FND_API.G_EXC_ERROR THEN
6485: x_Return_Status := FND_API.G_RET_STS_ERROR;
6486: WHEN OTHERS THEN
6487: x_Msg_Data := 'BSC_DIM_VIEW_NOT_EXIST';
6488: x_Return_Status := FND_API.G_RET_STS_ERROR;
6489: END Validate_PMF_Views;
6484: WHEN FND_API.G_EXC_ERROR THEN
6485: x_Return_Status := FND_API.G_RET_STS_ERROR;
6486: WHEN OTHERS THEN
6487: x_Msg_Data := 'BSC_DIM_VIEW_NOT_EXIST';
6488: x_Return_Status := FND_API.G_RET_STS_ERROR;
6489: END Validate_PMF_Views;
6490:
6491: /*
6492: Validates BSC type dim object view source
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;
6515: END IF;
6519:
6520: IF (c_bsc_table_type_flag%FOUND) THEN
6521: IF (l_flag <> 1) THEN -- '1' means that BSC view exists
6522: x_Msg_Data := 'BSC_DIM_VIEW_NOT_EXIST';
6523: RAISE FND_API.G_EXC_ERROR;
6524: END IF;
6525: END IF;
6526:
6527: CLOSE c_bsc_table_type_flag;
6526:
6527: CLOSE c_bsc_table_type_flag;
6528:
6529: EXCEPTION
6530: WHEN FND_API.G_EXC_ERROR THEN
6531: x_Return_Status := FND_API.G_RET_STS_ERROR;
6532: IF (c_bsc_table_type_flag%ISOPEN) THEN
6533: CLOSE c_bsc_table_type_flag;
6534: END IF;
6527: CLOSE c_bsc_table_type_flag;
6528:
6529: EXCEPTION
6530: WHEN FND_API.G_EXC_ERROR THEN
6531: x_Return_Status := FND_API.G_RET_STS_ERROR;
6532: IF (c_bsc_table_type_flag%ISOPEN) THEN
6533: CLOSE c_bsc_table_type_flag;
6534: END IF;
6535: WHEN OTHERS THEN
6533: CLOSE c_bsc_table_type_flag;
6534: END IF;
6535: WHEN OTHERS THEN
6536: x_Msg_Data := 'BSC_DIM_VIEW_NOT_EXIST';
6537: x_Return_Status := FND_API.G_RET_STS_ERROR;
6538: IF (c_bsc_table_type_flag%ISOPEN) THEN
6539: CLOSE c_bsc_table_type_flag;
6540: END IF;
6541: END Validate_BSC_Views;
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
6562: Validate_PMF_Views
6576: );
6577: END IF;
6578:
6579: EXCEPTION
6580: WHEN FND_API.G_EXC_ERROR THEN
6581: x_Return_Status := FND_API.G_RET_STS_ERROR;
6582: WHEN OTHERS THEN
6583: x_Msg_Data := 'BSC_DIM_VIEW_NOT_EXIST';
6584: x_Return_Status := FND_API.G_RET_STS_ERROR;
6577: END IF;
6578:
6579: EXCEPTION
6580: WHEN FND_API.G_EXC_ERROR THEN
6581: x_Return_Status := FND_API.G_RET_STS_ERROR;
6582: WHEN OTHERS THEN
6583: x_Msg_Data := 'BSC_DIM_VIEW_NOT_EXIST';
6584: x_Return_Status := FND_API.G_RET_STS_ERROR;
6585: END Validate_Dim_object_Views;
6580: WHEN FND_API.G_EXC_ERROR THEN
6581: x_Return_Status := FND_API.G_RET_STS_ERROR;
6582: WHEN OTHERS THEN
6583: x_Msg_Data := 'BSC_DIM_VIEW_NOT_EXIST';
6584: x_Return_Status := FND_API.G_RET_STS_ERROR;
6585: END Validate_Dim_object_Views;
6586:
6587: --=======================================================================================
6588: FUNCTION Validate_PMF_Base_View (
6602: IF (p_View_Name IS NULL) THEN
6603: RETURN C_TABLE_NOT_EXIST;
6604: END IF;
6605:
6606: IF (p_Parent_Id_Exists = FND_API.G_TRUE) THEN
6607: IF (p_Parent_Column IS NOT NULL) THEN
6608: l_Select_Clause := C_SELECT_CLAUSE || ', ' || p_Parent_Column || ' ';
6609: ELSE
6610: l_Select_Clause := C_SELECT_PARENT_CLAUSE;
6616: l_Sql := C_SELECT || l_Select_Clause
6617: || C_FROM || p_View_Name
6618: || C_WHERE || C_WHERE_CLAUSE ;
6619:
6620: IF (p_Parent_Id_Exists = FND_API.G_TRUE) THEN
6621: l_Sql := 'SELECT COUNT(1) FROM ('|| l_Sql || ')' ;
6622: EXECUTE IMMEDIATE l_Sql INTO l_Count;
6623: ELSE
6624: EXECUTE IMMEDIATE l_Sql INTO l_Id, l_Value;
6696: END IF;
6697: END LOOP;
6698: CLOSE c_column_names ;
6699:
6700: IF (p_parent_id_exists = FND_API.G_TRUE AND p_parent_column IS NOT NULL) THEN
6701: IF ((NOT l_is_id) OR (NOT l_is_value) OR (NOT l_is_parent_id)) THEN
6702: RETURN C_COLUMN_NOT_EXIST;
6703: END IF;
6704: ELSE
6755: WHERE SYS.dim_level_id = GRP.dim_level_id
6756: AND GRP.dim_group_id = l_dim_grp_id;
6757:
6758: BEGIN
6759: x_return_status := FND_API.G_RET_STS_SUCCESS;
6760: l_true := FALSE;
6761:
6762: -- added to relax checking for mixed type of Dimension Objects within a Dimension
6763: -- for Autogenerated reports and removing the disctiction, BSC 5.3
6760: l_true := FALSE;
6761:
6762: -- added to relax checking for mixed type of Dimension Objects within a Dimension
6763: -- for Autogenerated reports and removing the disctiction, BSC 5.3
6764: IF (p_Restrict_Dim_Validate IS NOT NULL OR BIS_UTILITIES_PUB.Enable_Generated_Source_Report = FND_API.G_FALSE) THEN
6765: FOR CTYPE IN C_SOURCE_DIM LOOP
6766: l_dim_name := CTYPE.DIM_NAME;
6767: l_dim_grp_id := CTYPE.DIM_ID;
6768: l_dim_short_name := CTYPE.SHORT_NAME;
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
6788: l_true := TRUE;
6789: FOR CD IN C_SOURCE_DIM_OBJS LOOP
6790: l_dim_obj_name := cd.name;
6791: IF(l_source IS NULL) THEN
6811: return l_true;
6812: END IF;
6813: END IF;
6814:
6815: x_return_status := FND_API.G_RET_STS_SUCCESS;
6816: RETURN l_true;
6817:
6818: EXCEPTION
6819: WHEN OTHERS THEN
6816: RETURN l_true;
6817:
6818: EXCEPTION
6819: WHEN OTHERS THEN
6820: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6821: IF (x_msg_data IS NULL) THEN
6822: x_msg_data := SQLERRM||' at BSC_BIS_DIMENSION_PUB.is_Valid_Dim_Grp_Rels ';
6823: END IF;
6824:
7027: l_Count := l_Count + 1;
7028: END LOOP;
7029:
7030: IF (l_Count = 1) THEN
7031: RETURN FND_API.G_TRUE;
7032: ELSE
7033: RETURN FND_API.G_FALSE;
7034: END IF;
7035:
7029:
7030: IF (l_Count = 1) THEN
7031: RETURN FND_API.G_TRUE;
7032: ELSE
7033: RETURN FND_API.G_FALSE;
7034: END IF;
7035:
7036: EXCEPTION
7037: WHEN OTHERS THEN
7034: END IF;
7035:
7036: EXCEPTION
7037: WHEN OTHERS THEN
7038: RETURN FND_API.G_FALSE;
7039: END Is_Recursive_Relationship;
7040: /******************************************************************************/
7041:
7042:
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, ' ', '');
7069: ELSE
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';
7098: END IF;
7116:
7117: RETURN l_Level_Pk_Col;
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
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: );
7127: END IF;
7124: , p_count => x_msg_count
7125: , p_data => x_msg_data
7126: );
7127: END IF;
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
7126: );
7127: END IF;
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
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: );
7137: END IF;
7134: , p_count => x_msg_count
7135: , p_data => x_msg_data
7136: );
7137: END IF;
7138: x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
7139: RETURN NULL;
7140: WHEN NO_DATA_FOUND THEN
7141: x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
7142: IF (x_msg_data IS NOT NULL) THEN
7137: END IF;
7138: x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
7139: RETURN NULL;
7140: WHEN NO_DATA_FOUND THEN
7141: x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
7142: IF (x_msg_data IS NOT NULL) THEN
7143: x_msg_data := x_msg_data||' -> BSC_BIS_DIM_OBJ_PUB.Get_Unique_Level_Pk_Col ';
7144: ELSE
7145: x_msg_data := SQLERRM||' at BSC_BIS_DIM_OBJ_PUB.Get_Unique_Level_Pk_Col ';
7145: x_msg_data := SQLERRM||' at BSC_BIS_DIM_OBJ_PUB.Get_Unique_Level_Pk_Col ';
7146: END IF;
7147: RETURN NULL;
7148: WHEN OTHERS THEN
7149: x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
7150: IF (x_msg_data IS NOT NULL) THEN
7151: x_msg_data := x_msg_data||' -> BSC_BIS_DIM_OBJ_PUB.Get_Unique_Level_Pk_Col ';
7152: ELSE
7153: x_msg_data := SQLERRM||' at BSC_BIS_DIM_OBJ_PUB.Get_Unique_Level_Pk_Col ';
7190: ,x_Msg_Count => x_Msg_Count
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;
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:
7202: BSC_BIS_DIM_OBJ_PUB.Refresh_BSC_PMF_Dim_View(
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: );
7216: END Validate_Refresh_BSC_PMF_Views;
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;
7256: 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: );
7275: x_Return_Status := FND_API.G_RET_STS_ERROR;
7271: ( p_encoded => FND_API.G_FALSE
7272: , p_count => x_msg_count
7273: , p_data => x_Msg_Data
7274: );
7275: x_Return_Status := FND_API.G_RET_STS_ERROR;
7276: END Cascade_Dim_Props_Into_Dim_Grp;
7277:
7278:
7279: END BSC_BIS_DIM_OBJ_PUB;