[Home] [Help]
110: Cursor CheckJobsExist(P_Rbs_Ver_Id IN Number) Is
111: Select
112: Count(*)
113: From
114: Pa_Rbs_Elements
115: Where
116: Job_Id Is Not Null
117: And User_Created_Flag = 'Y'
118: And Rbs_Version_Id = P_Rbs_Ver_Id;
765: --freezed version,give them new element identifiers.
766:
767: Pa_Debug.G_Stage:= 'Updating element identifiers of the rbs elements for the working version which do not match latest freeze.';
768: -- Bug 3635614 changed the update statement by removing the start with and connect by prior clause and used where clause instead
769: Update pa_rbs_elements
770: Set
771: Element_Identifier = Pa_Rbs_Element_Identifier_S.NextVal
772: Where
773: Rbs_Element_Id IN (
773: Rbs_Element_Id IN (
774: Select
775: Distinct a.Rbs_Element_Id
776: From
777: Pa_Rbs_Elements a,
778: Pa_Rbs_Elements b
779: Where
780: a.Element_Identifier = b.Element_Identifier
781: And a.Rbs_Version_Id = P_Rbs_Version_Id
774: Select
775: Distinct a.Rbs_Element_Id
776: From
777: Pa_Rbs_Elements a,
778: Pa_Rbs_Elements b
779: Where
780: a.Element_Identifier = b.Element_Identifier
781: And a.Rbs_Version_Id = P_Rbs_Version_Id
782: And b.Rbs_Version_Id = l_Latest_Freezed_Version_Id
797: --If l_use_for_alloc_flag='Y' Then
798:
799: Pa_Debug.G_Stage:= 'Copying All Elements With user_created_Flag = N';
800:
801: Pa_Debug.G_Stage:= 'Delete All Records From Pa_Rbs_Elements_Temp';
802: Begin
803:
804: Delete
805: From Pa_Rbs_Elements_Temp;
801: Pa_Debug.G_Stage:= 'Delete All Records From Pa_Rbs_Elements_Temp';
802: Begin
803:
804: Delete
805: From Pa_Rbs_Elements_Temp;
806:
807: Exception
808: When No_Data_Found Then
809: null;
809: null;
810:
811: End;
812:
813: Pa_Debug.G_Stage:= 'Insert Pa_Rbs_Elements_Temp table with new element_ids';
814:
815: Insert Into Pa_Rbs_Elements_Temp(
816: New_Element_Id,
817: Old_Element_Id,
811: End;
812:
813: Pa_Debug.G_Stage:= 'Insert Pa_Rbs_Elements_Temp table with new element_ids';
814:
815: Insert Into Pa_Rbs_Elements_Temp(
816: New_Element_Id,
817: Old_Element_Id,
818: Old_Parent_Element_Id,
819: New_Parent_Element_Id )
817: Old_Element_Id,
818: Old_Parent_Element_Id,
819: New_Parent_Element_Id )
820: (Select
821: Pa_Rbs_Elements_S.NextVal,
822: Rbs_Element_Id,
823: Parent_Element_Id,
824: Null
825: From
822: Rbs_Element_Id,
823: Parent_Element_Id,
824: Null
825: From
826: Pa_Rbs_Elements
827: Where
828: Rbs_Version_Id = P_Rbs_Version_Id
829: and user_created_flag = 'Y' );
830:
827: Where
828: Rbs_Version_Id = P_Rbs_Version_Id
829: and user_created_flag = 'Y' );
830:
831: Update Pa_Rbs_Elements_Temp Tmp1
832: Set New_Parent_Element_Id =
833: (Select
834: New_Element_Id
835: From
832: Set New_Parent_Element_Id =
833: (Select
834: New_Element_Id
835: From
836: Pa_Rbs_Elements_Temp Tmp2
837: Where
838: Tmp1.Old_Parent_Element_Id = Tmp2.Old_Element_Id);
839:
840: Pa_Debug.G_Stage:= 'Insert all records with user_created_flag=N with new element ids updated with new parent element ids';
840: Pa_Debug.G_Stage:= 'Insert all records with user_created_flag=N with new element ids updated with new parent element ids';
841:
842: /*Bug 4377886 : Included explicitly the column names in the INSERT statement
843: to remove the GSCC Warning File.Sql.33 */
844: Insert Into pa_rbs_elements
845: (
846: RBS_ELEMENT_ID,
847: RBS_ELEMENT_NAME_ID,
848: RBS_VERSION_ID,
922: fnd_global.user_id,
923: fnd_global.login_id,
924: 1
925: FROM
926: Pa_Rbs_Elements Ele,
927: Pa_Rbs_Elements_Temp Tmp
928: WHERE
929: Tmp.Old_Element_Id=Ele.Rbs_Element_Id;
930:
923: fnd_global.login_id,
924: 1
925: FROM
926: Pa_Rbs_Elements Ele,
927: Pa_Rbs_Elements_Temp Tmp
928: WHERE
929: Tmp.Old_Element_Id=Ele.Rbs_Element_Id;
930:
931: End IF; --End of changes made for bug 3659078. modified for bug 3703364