[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Procedure Insert_Header(
P_Name IN Varchar2 ,
P_Description IN Varchar2,
P_EffectiveFrom IN Date,
P_EffectiveTo IN Date,
P_Use_For_Alloc_Flag IN Varchar2,
X_RbsHeaderId OUT NOCOPY Number,
x_return_status OUT NOCOPY Varchar2,
x_msg_data OUT NOCOPY Varchar2,
x_msg_count OUT NOCOPY Number )
Is
l_Business_Group_Id Number := Null;
Select
Pa_Rbs_Headers_S.NextVal
From
Sys.Dual;
Pa_Debug.G_Stage := 'Entering Insert_Header() Pvt.';
Pa_Debug.TrackPath('ADD','Insert_Header Pvt');
Select
Business_Group_Id
Into
l_Business_Group_Id
From
Pa_Implementations ;
Pa_Debug.G_Stage := 'Calls the table handler which inserts the rbs header record into the Pa_Rbs_Header table.';
Pa_Rbs_Headers_Pkg.Insert_Row(
P_RbsHeaderId => X_RbsHeaderId,
P_Name => P_Name,
P_Description => P_Description,
P_EffectiveFrom => P_EffectiveFrom,
P_EffectiveTo => P_EffectiveTo,
P_Use_For_Alloc_Flag => P_Use_For_Alloc_Flag,
P_BusinessGroupId => l_Business_Group_Id );
Pa_Debug.G_Stage := 'Leaving Insert_Header() Pvt.';
Pa_Debug.TrackPath('STRIP','Insert_Header Pvt');
End Insert_Header;
Procedure Insert_Versions(
P_RbsHeaderId IN Number,
P_Name IN Varchar2,
P_Description IN Varchar2 Default Null,
P_EffectiveFrom IN Date,
X_Rbs_Version_Id OUT NOCOPY Number,
X_Return_Status OUT NOCOPY Varchar2,
X_Msg_Data OUT NOCOPY Varchar2,
X_Msg_Count OUT NOCOPY Number )
Is
l_Created_By Number := Fnd_Global.User_Id;
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
Pa_Rbs_Versions_S.NextVal
From
Sys.Dual;
Pa_Debug.G_Stage := 'Entering Insert_Versions() Pvt.';
Pa_Debug.TrackPath('ADD','Insert_Versions Pvt');
Pa_Debug.G_Stage := 'Insert a working version into Pa_Rbs_Versions_B table directly here.';
Insert Into Pa_Rbs_Versions_B(
Rbs_Header_Id,
Rbs_Version_Id,
Version_Number,
Version_Start_Date,
Status_Code,
Rule_Based_Flag,
Validated_Flag,
Creation_Date,
Created_By,
Last_Update_Date,
Last_Updated_By,
Last_Update_Login,
Record_Version_Number )
Values(
P_RbsHeaderId,
X_Rbs_Version_Id,
1,
P_EffectiveFrom,
'WORKING',
'N',
'N',
l_Creation_Date,
l_Created_By,
l_Last_Update_Date,
l_Last_Updated_By,
l_Last_Update_Login,
1 );
Pa_Debug.G_Stage := 'Insert working versions into Pa_Rbs_Versions_TL table directly here.';
Insert Into Pa_Rbs_Versions_TL(
Rbs_Version_Id,
Name,
Description,
Language,
Last_Update_Date,
Last_Updated_By,
Creation_Date,
Created_By,
Last_Update_Login,
Source_Lang )
Select
X_Rbs_Version_Id,
P_Name,
P_Description,
L.Language_Code ,
l_Last_Update_Date,
l_Last_Updated_By,
l_Creation_Date,
l_Created_By,
l_Last_Update_Login,
UserEnv('LANG')
From
Fnd_Languages L
Where
L.Installed_Flag in ('I', 'B')
And Not Exists
(Select
Null
From
Pa_Rbs_Versions_TL T
Where
T.Rbs_Version_Id=X_Rbs_Version_Id
And
T.Language=L.Language_Code);
Pa_Debug.G_Stage := 'Entering Insert_Versions() Pvt.';
Pa_Debug.TrackPath('STRIP','Insert_Versions Pvt');
End Insert_Versions;
Procedure Insert_Structure_Element(
P_Rbs_Version_Id IN Number,
X_Rbs_Element_Id OUT NOCOPY Number,
X_Return_Status OUT NOCOPY Varchar2,
X_Error_Msg_Data OUT NOCOPY Varchar2,
X_Msg_Count OUT NOCOPY Number )
Is
l_Rbs_Element_Id Number(15) := Null;
Select
Pa_Rbs_Elements_S.NextVal
From
Sys.Dual;
Pa_Debug.G_Stage := 'Entering Insert_Structure_Element() Pvt.';
Pa_Debug.TrackPath('ADD','Insert_Structure_Element Pvt');
Pa_Debug.G_Stage := 'Insert record directly into table pa_rbs_elements,';
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_Created_Flag,
User_Defined_Custom1_Id,
User_Defined_Custom2_Id,
User_Defined_Custom3_Id,
User_Defined_Custom4_Id,
User_Defined_Custom5_Id,
Last_Update_Date,
Last_Updated_By,
Creation_Date,
Created_By,
Last_Update_Login,
Record_Version_Number )
Values (
l_Rbs_Element_Id,
l_Rbs_Element_Name_Id,
P_RBS_Version_Id,
'0',
0,
-1,
P_RBS_Version_Id,
Null,
Null,
Null,
Null,
Null,
Null,
Null,
Null,
Null,
Null,
Null,
Null,
Null,
Null,
Null,
Null,
'N',
Null,
1,
Pa_Rbs_Element_Identifier_S.NextVal,
'Y',
Null,
Null,
Null,
Null,
Null,
SysDate,
Fnd_Global.User_Id,
SysDate,
Fnd_Global.User_Id,
Fnd_Global.Login_Id,
1);
Pa_Debug.G_Stage := 'Leaving Insert_Structure_Element() Pvt.';
Pa_Debug.TrackPath('STRIP','Insert_Structure_Element Pvt');
End Insert_Structure_Element;
This api updates RBS Header.
============================================================================*/
-- Procedure : UPDATE_HEADER
-- Type : Private Procedure
-- Purpose : This API will be used to update RBS headers.
-- : This API will be called from following package:
-- : 1.PA_RBS_HEADER_PUB package,Update_Header procedure
-- Note : This API will make a call to PA_RBS_HEADER_PKG.Update_Row procedure which
-- : Updates record into PA_RBS_HEADERS_B and PA_RBS_HEADERS_TL table.
-- Assumptions :
-- Parameters Type Required Description and Purpose
-- --------------------------- ------ -------- --------------------------------------------------------
-- p_rbsHeaderId NUMBER Yes The value will contain the Rbs Header id which is the unique identifier.
-- p_name VARCHAR2 Yes The value contain the name of the Rbs header
-- p_description VARCHAR2 NO The description of the Rbs header
-- p_effectiveFrom DATE YES The start date of the RBS
-- p_effectiveTo DATE NO The end date of the Rbs.
Procedure Update_Header(
P_RbsHeaderId IN Number,
P_Name IN Varchar2 ,
P_Description IN Varchar2 ,
P_EffectiveFrom IN Date ,
P_EffectiveTo IN Date,
P_Use_For_Alloc_Flag IN Varchar2,
X_return_Status OUT NOCOPY Varchar2,
X_Msg_Data OUT NOCOPY Varchar2,
X_Msg_Count OUT NOCOPY Number)
Is
Begin
x_return_status := FND_API.G_RET_STS_SUCCESS;
Pa_Debug.G_Stage := 'Entering Update_Header() Pvt.';
Pa_Debug.TrackPath('ADD','Update_Header Pvt');
Pa_Debug.G_Stage := 'Call the table handler procedure Pa_Rbs_Headers_Pkg.Update_Row to update the header record.';
Pa_Rbs_Headers_Pkg.Update_Row(
P_RbsHeaderId => P_RbsHeaderId,
P_Name => P_Name,
P_Description => P_Description,
P_EffectiveFrom => P_EffectiveFrom,
P_Use_For_Alloc_Flag => P_Use_For_Alloc_Flag,
P_EffectiveTo => P_EffectiveTo);
Pa_Debug.G_Stage := 'Leaving Update_Header() Pvt.';
Pa_Debug.TrackPath('STRIP','Update_Header Pvt');
End Update_Header;
This api updates Working Version for the RBS Header.
============================================================================*/
-- Procedure : UPDATE_VERSIONS
-- Type : Private Procedure
-- Purpose : This API will be used to update working version for the RBS header.
-- : This API will be called from following package:
-- : 1.PA_RBS_HEADER_PUB package,Update_Header procedure
-- Note : This API will Updates working version for Rbs header in PA_RBS_VERSIONS_B and PA_RBS_VERSIONS_TL table.
-- Assumptions :
-- Parameters Type Required Description and Purpose
-- --------------------------- ------ -------- --------------------------------------------------------
-- p_rbsHeaderId NUMBER Yes The value will contain the Rbs Header id which is the unique identifier.
-- p_name VARCHAR2 Yes The value contain the name of the Rbs header
-- p_effectiveFrom DATE YES The start date of the RBS
Procedure Update_Versions(
P_RbsHeaderId IN Number,
P_RbsVersionId IN Number Default Null,
P_Name IN Varchar2,
P_Description IN Varchar2 Default Null,
P_EffectiveFrom IN Date,
P_Rec_Version_Num IN Number Default Null,
X_Return_Status OUT NOCOPY Varchar2,
X_msg_Data OUT NOCOPY Varchar2,
X_Msg_Count OUT NOCOPY Number )
Is
l_Last_Update_Date Date := SysDate;
l_Last_Updated_By Number := Fnd_Global.User_Id;
l_Last_Update_Login Number := Fnd_Global.Login_Id;
Pa_Debug.G_Stage := 'Leaving Update_Versions() Pvt.';
Pa_Debug.TrackPath('ADD','Update_Versions Pvt');
Select
Rbs_Version_Id
Into
l_Rbs_Version_Id
From
Pa_Rbs_Versions_B
Where
Rbs_Header_Id = P_RbsHeaderId
And Status_Code = 'WORKING';
Pa_Debug.G_Stage := 'Directly update the rbs working version.';
Update Pa_Rbs_Versions_B
Set
Version_Start_Date = P_EffectiveFrom,
Last_Update_Date = l_Last_Update_Date,
Last_Updated_By = l_Last_Updated_By,
Last_Update_Login = l_Last_Update_Login,
Record_Version_Number = Record_Version_Number + 1
Where
Rbs_Version_Id = l_Rbs_Version_Id
And Status_Code = 'WORKING'
And Record_Version_Number = Nvl(P_Rec_Version_Num,Record_Version_Number);
Pa_Debug.G_Stage := 'Check if the update took place.';
Pa_Debug.G_Stage := 'Unable to update the rbs version because already updated. Raising error.';
Pa_Debug.G_Stage := 'Directly update the pa_rbs_versions_tl table.';
Update Pa_Rbs_Versions_TL
Set
Name = P_Name,
Description = Nvl(Description,P_Description),
Last_Update_Date = l_Last_Update_Date,
Last_Updated_By = l_Last_Updated_By,
Last_Update_Login = l_Last_Update_Login
Where
Rbs_Version_Id = l_Rbs_Version_Id;
END Update_Versions;
Select count(*) INTO l_count
From
Pa_Rbs_Versions_b Verb,
Pa_Rbs_Elements ele
Where
Verb.rbs_header_id=P_RBS_ID
And
verb.Rbs_Version_Id=Ele.Rbs_Version_Id
And
Ele.resource_source_id=-1;