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