[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Procedure Update_Working_Version(
P_Commit IN Varchar2 Default Fnd_Api.G_False,
P_Init_Msg_List IN Varchar2 Default Fnd_Api.G_True,
P_API_Version_Number IN Number,
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,
P_Init_Debugging_Flag IN Varchar2 Default 'Y',
X_Record_Version_Number OUT NOCOPY Number,
X_Return_Status OUT NOCOPY Varchar2,
X_Msg_Count OUT NOCOPY Number,
X_Error_Msg_Data OUT NOCOPY Varchar2)
Is
l_Api_Name Varchar2(30) := 'Update_Working_Version';
Select
Status_Code,
Job_Group_Id
From
Pa_Rbs_Versions_B
Where
Rbs_Version_Id = P_Rbs_Version_Id
And Status_Code = 'WORKING'
And Record_Version_Number = P_Rec_Num
For Update Of Version_Start_Date NoWait;
Select
H.Effective_From_Date,
H.Effective_To_Date,
H.Rbs_Header_Id,
TL.Name
From
Pa_Rbs_Headers_B H,
Pa_Rbs_Headers_TL TL,
Pa_Rbs_Versions_B V
Where
TL.Rbs_Header_Id = H.Rbs_Header_Id
And UserEnv('LANG') in (TL.Language, TL.Source_Lang)
And H.Rbs_Header_Id = V.Rbs_Header_Id
And V.Rbs_Version_Id = P_Rbs_Ver_Id;
Select
Max(Rbs_Version_Id)
From
Pa_Rbs_Versions_B
Where
Rbs_Header_Id = P_Id
And Status_Code <> 'WORKING';
Select
Version_Start_Date,
Version_End_Date
From
Pa_Rbs_Versions_B
Where
Rbs_Version_Id = P_Id;
Select
Count(*)
From
Pa_Rbs_Elements
Where
Job_Id Is Not Null
And User_Created_Flag = 'Y'
And Rbs_Version_Id = P_Rbs_Ver_Id;
Pa_Debug.G_Stage := 'Entering Update_Working_Version() Pub.';
Pa_Debug.TrackPath('ADD','Update_Working_Version Pub');
Pa_Debug.G_Stage := 'Check able to lock version rec for update.';
Pa_Debug.G_Stage := 'Unable to lock version record for update. ' ||
'Add error message to stack.';
P_Msg_Name => 'PA_RECORD_ALREADY_UPDATED');
Pa_Debug.G_Stage := 'Call Pa_Rbs_Versions_Pvt.Update_Working_Version() procedure.';
Pa_Rbs_Versions_Pvt.Update_Working_Version(
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,
X_Record_Version_Number => X_Record_Version_Number,
X_Error_Msg_Data => X_Error_Msg_Data);
Pa_Debug.G_Stage := 'Leaving Update_Working_Version() Pub procedure.';
Pa_Debug.TrackPath('STRIP','Update_Working_Version Pub');
Pa_Debug.G_Stage := 'Leaving Update_Working_Version() Pub procedure.';
Pa_Debug.TrackPath('STRIP','Update_Working_Version Pub');
End Update_Working_Version;
Procedure Delete_Working_Version(
P_Commit IN Varchar2 Default Fnd_Api.G_False,
P_Init_Msg_List IN Varchar2 Default Fnd_Api.G_True,
P_Api_Version_Number IN Number,
P_RBS_Version_Id IN Number,
P_Record_Version_Number IN Number,
X_Return_Status OUT NOCOPY Varchar2,
X_Msg_Count OUT NOCOPY Number,
X_Error_Msg_Data OUT NOCOPY Varchar2)
Is
l_Api_Name Varchar2(30) := 'Delete_Working_Version';
Pa_Debug.G_Stage := 'Entering Delete_Working_Version() Pub.';
Pa_Debug.TrackPath('ADD','Delete_Working_Version Pub');
Pa_Debug.G_Stage := 'Call Pa_Rbs_Versions_Pvt.Delete_Working_Version() procedure.';
Pa_Rbs_Versions_Pvt.Delete_Working_Version(
P_Rbs_Version_Id => P_RBS_Version_Id,
P_Record_Version_Number => P_Record_Version_Number,
P_Mode => Null,
X_Error_Msg_Data => X_Error_Msg_Data);
Pa_Debug.G_Stage := 'Leaving Delete_Working_Version() Pub procedure.';
Pa_Debug.TrackPath('STRIP','Delete_Working_Version Pub');
End Delete_Working_Version;
Select
Max(Rbs_Version_Id)
From
Pa_Rbs_Versions_B
Where
Rbs_Header_Id = l_Rbs_Header_Id
And Status_Code <> 'WORKING';
Select
Version_Start_Date,
Nvl(Version_End_Date,SysDate) Version_End_Date
From
Pa_Rbs_Versions_B
Where
Rbs_Version_Id = P_Rbs_Ver_Id;
Select
Record_Version_Number
From
Pa_Rbs_Versions_B
Where
Rbs_Version_Id = P_Rbs_Version_Id
For Update Of Version_Start_Date NoWait;
Select
Version_Start_Date,
Rbs_Header_Id
Into
l_Version_Start_Date,
l_Rbs_Header_Id
From
Pa_Rbs_Versions_B
Where
Rbs_Version_Id = P_Rbs_Version_Id;
--SELECT
-- USE_FOR_ALLOC_FLAG
--INTO
-- l_use_for_alloc_flag
--FROM
-- Pa_Rbs_Headers_b
--Where
-- Rbs_Header_Id=l_Rbs_Header_Id;
Update pa_rbs_elements
Set
Element_Identifier = Pa_Rbs_Element_Identifier_S.NextVal
Where
Rbs_Element_Id IN (
Select
Distinct a.Rbs_Element_Id
From
Pa_Rbs_Elements a,
Pa_Rbs_Elements b
Where
a.Element_Identifier = b.Element_Identifier
And a.Rbs_Version_Id = P_Rbs_Version_Id
And b.Rbs_Version_Id = l_Latest_Freezed_Version_Id
And a.Resource_Type_Id <> -1
And b.Resource_Type_id <> -1
And ( a.Resource_Source_Id <> b.Resource_Source_Id Or
a.Resource_Type_Id <> b.Resource_Type_Id) );
Pa_Debug.G_Stage:= 'Delete All Records From Pa_Rbs_Elements_Temp';
Delete
From Pa_Rbs_Elements_Temp;
Pa_Debug.G_Stage:= 'Insert Pa_Rbs_Elements_Temp table with new element_ids';
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_Id
and user_created_flag = 'Y' );
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 all records with user_created_flag=N with new element ids updated with new parent element ids';
/*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)
SELECT
Tmp.New_Element_Id,
Ele.rbs_element_name_id,
P_Rbs_Version_Id,
Ele.outline_number,
Ele.order_Number,
Ele.resource_type_id,
Ele.resource_source_id,
Ele.person_id,
Ele.job_id,
Ele.organization_id,
Ele.Expenditure_Type_Id,
Ele.Event_Type_Id,
Ele.expenditure_category_id,
Ele.revenue_category_id,
Ele.inventory_item_id,
Ele.item_category_id,
Ele.bom_labor_id,
Ele.bom_equipment_id,
Ele.non_labor_resource_id,
Ele.role_id,
Ele.person_type_id,
Ele.resource_class_id,
Ele.supplier_id,
Ele.rule_flag,
Tmp.New_parent_element_id,
Ele.rbs_level,
Ele.element_identifier,
Ele.user_defined_custom1_id,
Ele.user_defined_custom2_id,
Ele.user_defined_custom3_id,
Ele.user_defined_custom4_id,
Ele.user_defined_custom5_id,
'N',
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id,
1
FROM
Pa_Rbs_Elements Ele,
Pa_Rbs_Elements_Temp Tmp
WHERE
Tmp.Old_Element_Id=Ele.Rbs_Element_Id;
Update Pa_Rbs_Versions_B
Set
Status_code = 'FROZEN'
Where
Rbs_Version_Id = P_Rbs_Version_Id;
Update Pa_Rbs_Versions_B
Set
Version_End_Date = l_end_date
Where
Rbs_Version_Id = l_Latest_Freezed_Version_Id;
SELECT count(*)
INTO l_count
FROM pa_rbs_versions_b
where rbs_header_id = l_rbs_header_id;
Update pa_rbs_versions_b
Set current_reporting_flag = 'Y'
where rbs_version_id = p_rbs_version_id;
SELECT count(*)
INTO l_assoc_count
FROM pa_rbs_prj_assignments
WHERE rbs_header_id = l_rbs_header_id;
UPDATE pa_rbs_versions_b
SET current_reporting_flag = 'Y'
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
AND rbs_version_id <> p_rbs_version_id;