[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Pa_Debug.G_Stage := 'Call Pa_Rbs_Versions_Pkg.Insert_Row() procedure.';
Pa_Rbs_Versions_Pkg.Insert_Row(
P_Version_Number => P_Version_Number,
P_Rbs_Header_Id => P_Rbs_Header_Id,
P_Record_Version_Number => P_Record_Version_Number,
P_Name => P_Name,
P_Description => P_Description,
P_Version_Start_Date => P_Version_Start_Date,
P_Version_End_Date => P_Version_End_Date,
P_Job_Group_Id => P_Job_Group_Id,
P_Rule_Based_Flag => P_Rule_Based_Flag,
P_Validated_Flag => P_Validated_Flag,
P_Status_Code => P_Status_Code,
P_Creation_Date => Pa_Rbs_Versions_Pvt.G_Creation_Date,
P_Created_By => Pa_Rbs_Versions_Pvt.G_Created_By,
P_Last_Update_Date => Pa_Rbs_Versions_Pvt.G_Last_Update_Date,
P_Last_Updated_By => Pa_Rbs_Versions_Pvt.G_Last_Updated_By,
P_Last_Update_Login => Pa_Rbs_Versions_Pvt.G_Last_Update_Login,
X_Record_Version_Number => X_Record_Version_Number,
X_Rbs_Version_Id => X_RBS_Version_Id,
X_Error_Msg_Data => X_Error_Msg_Data);
Pa_Debug.G_Stage := 'Check if Pa_Rbs_Versions_Pkg.Insert_Row() procedure returned error msg.';
Procedure Update_Working_Version(
P_RBS_Version_Id IN Number,
P_Name IN Varchar2,
P_Description IN Varchar2,
P_Version_Start_Date IN Date ,
P_Job_Group_Id IN Number,
P_Record_Version_Number IN Number,
X_Record_Version_Number OUT NOCOPY Number,
X_Error_Msg_Data OUT NOCOPY Varchar2 )
Is
Begin
Pa_Debug.G_Stage := 'Entering Update_Working_Version() Pvt.';
Pa_Debug.TrackPath('ADD','Update_Working_Version Pvt');
Pa_Debug.G_Stage := 'Call Pa_Rbs_Versions_Pkg.Update_Row() procedure.';
Pa_Rbs_Versions_Pkg.Update_Row(
P_RBS_Version_Id => P_RBS_Version_Id,
P_Name => P_Name,
P_Description => P_Description,
P_Version_Start_Date => P_Version_Start_Date,
P_Job_Group_Id => P_Job_Group_Id,
P_Record_Version_Number => P_Record_Version_Number,
P_Last_Update_Date => Pa_Rbs_Versions_Pvt.G_Last_Update_Date,
P_Last_Updated_By => Pa_Rbs_Versions_Pvt.G_Last_Updated_By,
P_Last_Update_Login => Pa_Rbs_Versions_Pvt.G_Last_Update_Login,
X_Record_Version_Number => X_Record_Version_Number,
X_Error_Msg_Data => X_Error_Msg_Data);
Pa_Debug.G_Stage := 'Check if Pa_Rbs_Versions_Pkg.Update_Row() procedure returned error msg.';
Pa_Debug.G_Stage := 'Leaving Update_Working_Version() Pvt procedure.';
Pa_Debug.TrackPath('STRIP','Update_Working_Version Pvt');
End Update_Working_Version;
Procedure Delete_Working_Version(
P_Mode IN Varchar2 Default Null,
P_RBS_Version_Id IN Number,
P_Record_Version_Number IN Number,
X_Error_Msg_Data OUT NOCOPY Varchar2)
Is
Begin
Pa_Debug.G_Stage := 'Entering Delete_Working_Version() Pvt.';
Pa_Debug.TrackPath('ADD','Delete_Working_Version Pvt');
Pa_Debug.G_Stage := 'Call Pa_Rbs_Versions_Pkg.Delete_Row() procedure.';
Pa_Rbs_Versions_Pkg.Delete_Row(
P_RBS_Version_Id => P_RBS_Version_Id,
P_Record_Version_Number => P_Record_Version_Number,
X_Error_Msg_Data => X_Error_Msg_Data);
Pa_Debug.G_Stage := 'Check if Pa_Rbs_Versions_Pkg.Delete_Row() procedure returned error msg.';
Pa_Debug.G_Stage := 'Delete the working version element/nodes.';
Delete
From
Pa_Rbs_Elements
Where
Rbs_Version_Id = P_Rbs_Version_Id;
Pa_Debug.G_Stage := 'Leaving Delete_Working_Version() Pvt procedure.';
Pa_Debug.TrackPath('STRIP','Delete_Working_Version Pvt');
End Delete_Working_Version;
l_Last_Update_Date Date := SysDate;
l_Last_Updated_By Number := Fnd_Global.User_Id;
l_Last_Update_Login Number := Fnd_Global.Login_Id;
Select
Job_Group_Id,
Rule_Based_Flag,
Version_End_Date
From
Pa_Rbs_Versions_B
Where
Rbs_Version_Id = P_Id;
Select
Description
From
Pa_Rbs_Versions_TL
Where
Rbs_Version_Id = P_Id
--MLS changes.
--And Source_Lang = UserEnv('LANG');
Select
Record_Version_Number,
Rbs_Version_Id
From
Pa_Rbs_Versions_B
Where
Rbs_Header_Id = P_Rbs_Header_Id
And Status_Code = 'WORKING'
For Update Of Status_Code NoWait;
Select
Max(Rbs_Version_Id)
From
Pa_Rbs_Versions_B
Where
Rbs_Header_Id = P_Rbs_Header_Id
And Status_Code <> 'WORKING';
Select
Effective_From_Date
From
Pa_Rbs_Headers_B
Where
Rbs_Header_Id = P_Hdr_Id;
Select
rbs_element_name_id
From
pa_rbs_elements
Where
rbs_version_id = l_Rbs_Version_Id
and outline_number = '0';
/* To get the details of selected version*/
Open GetDetails(l_Rbs_Version_From_Id);
-- Delete the current working rbs version and its element/node records
Pa_Debug.G_Stage := 'Call Pa_Rbs_Versions_Pvt.Delete_Working_Version() procedure.';
Pa_Rbs_Versions_Pvt.Delete_Working_Version(
P_Mode => 'COPYING_FROZEN_VERSION',
P_Rbs_Version_Id => l_Rbs_Version_Id,
P_Record_Version_Number => P_Record_Version_Number,
X_Error_Msg_Data => X_Error_Msg_Data);
Pa_Debug.G_Stage := 'Delete the record in pa_rbs_element_names table corresponding to that ' ||
'of top most element in pa_rbs_elements table.';
Delete
From
Pa_Rbs_Element_names_tl
Where
Rbs_Element_Name_Id = l_Rbs_Element_Name_Id;
Delete
From
Pa_Rbs_Element_names_b
Where
Rbs_Element_Name_Id = l_Rbs_Element_Name_Id;
Select
Nvl(Max(Version_Number),0) + 1
Into
l_Version_Number
From
Pa_Rbs_Versions_B
Where
Rbs_Header_Id = P_Rbs_Header_Id;
Select
Name
Into
l_Name
From
Pa_Rbs_Headers_TL
Where
Rbs_Header_Id = P_Rbs_Header_Id
--MLS Changes
--And Source_lang = UserEnv('LANG');
Pa_Debug.G_Stage := 'Call Pa_Rbs_Versions_Pkg.Insert_Row() procedure from existing .';
Pa_Debug.G_Stage := 'Call Pa_Rbs_Versions_Pkg.Insert_Row() procedure brand new.';
X_Error_Msg_Data := 'PA_RECORD_ALREADY_UPDATED';
Pa_Debug.G_Stage := 'Delete all records from Pa_Rbs_Elements_Temp.';
Delete
From Pa_Rbs_Elements_Temp;
Pa_Debug.G_Stage := 'Insert into Pa_Rbs_Elements_Temp that are to be copied.';
* Desc - while inserting into Pa_Rbs_Elements_Temp elements that
* are to be copied, we should only select elements where
* user_created flag = 'Y'.
******************************************************/
Insert Into Pa_Rbs_Elements_Temp(
New_Element_Id,
Old_Element_Id,
Old_Parent_Element_Id,
New_Parent_Element_Id )
(Select
Pa_Rbs_Elements_S.NextVal,
Rbs_Element_Id,
Parent_Element_Id,
Null
From
Pa_Rbs_Elements
Where
Rbs_Version_Id = P_Rbs_Version_From_Id
and user_created_flag = 'Y' );
Pa_Debug.G_Stage := 'Update Pa_Rbs_Elements_Temp records with the correct parent element id.';
Update Pa_Rbs_Elements_Temp Tmp1
Set New_Parent_Element_Id =
(Select
New_Element_Id
From
Pa_Rbs_Elements_Temp Tmp2
Where
Tmp1.Old_Parent_Element_Id = Tmp2.Old_Element_Id);
Pa_Debug.G_Stage := 'Insert into Pa_Rbs_Elements new records.';
/*Bug 4377886 : Included explicitly the column names in the INSERT statement
to remove the GSCC Warning File.Sql.33 */
Insert Into Pa_Rbs_Elements
(
RBS_ELEMENT_ID,
RBS_ELEMENT_NAME_ID,
RBS_VERSION_ID,
OUTLINE_NUMBER,
ORDER_NUMBER,
RESOURCE_TYPE_ID,
RESOURCE_SOURCE_ID,
PERSON_ID,
JOB_ID,
ORGANIZATION_ID,
EXPENDITURE_TYPE_ID,
EVENT_TYPE_ID,
EXPENDITURE_CATEGORY_ID,
REVENUE_CATEGORY_ID,
inventory_item_id,
item_category_id,
bom_labor_id,
bom_equipment_id,
non_labor_resource_id,
role_id,
person_type_id,
resource_class_id,
supplier_id,
rule_flag,
PARENT_ELEMENT_ID,
rbs_level,
element_identifier,
user_defined_custom1_id,
user_defined_custom2_id,
user_defined_custom3_id,
user_defined_custom4_id,
user_defined_custom5_id,
USER_CREATED_FLAG,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
RECORD_VERSION_NUMBER
)
--For perf bug 4045542
Select /*+ ORDERED */
Tmp.New_Element_Id,
Rbs_Elements.Rbs_Element_Name_Id,
P_Rbs_Version_To_Id,
Rbs_Elements.Outline_Number,
Rbs_Elements.Order_Number,
Rbs_Elements.Resource_Type_Id,
Rbs_Elements.Resource_Source_Id,
Rbs_Elements.Person_Id,
Rbs_Elements.Job_Id,
Rbs_Elements.Organization_Id,
Rbs_Elements.Expenditure_Type_Id,
Rbs_Elements.Event_Type_Id,
Rbs_Elements.Expenditure_Category_Id,
Rbs_Elements.Revenue_Category_Id,
Rbs_Elements.Inventory_Item_Id,
Rbs_Elements.Item_Category_Id,
Rbs_Elements.Bom_Labor_Id,
Rbs_Elements.Bom_Equipment_Id,
Rbs_Elements.Non_Labor_Resource_Id,
Rbs_Elements.Role_Id,
Rbs_Elements.Person_Type_Id,
Rbs_Elements.Resource_Class_Id,
Rbs_Elements.Supplier_Id,
Rbs_Elements.Rule_Flag,
Tmp.New_Parent_Element_Id,
Rbs_Elements.Rbs_Level,
Rbs_Elements.Element_Identifier,
Rbs_Elements.User_Defined_Custom1_Id,
Rbs_Elements.User_Defined_Custom2_Id,
Rbs_Elements.User_Defined_Custom3_Id,
Rbs_Elements.User_Defined_Custom4_Id,
Rbs_Elements.User_Defined_Custom5_Id,
Rbs_Elements.User_Created_Flag,
Pa_Rbs_Versions_Pvt.G_Last_Update_Date,
Pa_Rbs_Versions_Pvt.G_Last_Updated_By,
Pa_Rbs_Versions_Pvt.G_Creation_Date,
Pa_Rbs_Versions_Pvt.G_Created_By,
Pa_Rbs_Versions_Pvt.G_Last_Update_Login,
1
From
Pa_Rbs_Elements_Temp Tmp,
Pa_Rbs_Elements Rbs_Elements
Where
Tmp.Old_Element_Id = Rbs_Elements.Rbs_Element_Id;
Update Pa_Rbs_Elements
Set Rbs_Element_Name_Id = l_New_Element_Name_Id,
Resource_Source_Id = P_Rbs_Version_To_Id
Where Rbs_Version_Id = P_Rbs_Version_To_Id
And Resource_Type_Id = -1
And Rbs_Level = 1;
* First select the header_id that corresponds to this version.
* If multiple or no rows found then just set the x_return_status
* as Unexpected error and return.
******************************************************/
Begin
Select
Rbs_Header_Id
Into
l_Rbs_Header_Id
From
Pa_Rbs_Versions_B
Where
Rbs_Version_Id = P_Rbs_Version_Id;
Update
Pa_Rbs_Versions_B
Set
Current_Reporting_Flag = Null
Where
Rbs_Header_Id = l_Rbs_Header_Id;
Update
Pa_Rbs_Versions_B
Set
Current_Reporting_Flag = 'Y'
Where
Rbs_Header_Id = l_Rbs_Header_Id
And Rbs_Version_Id = P_Rbs_Version_Id;