[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
* and selects the value from the database if the value being input is null,
* so that there wouldn't be any change to the data when null is passed as input.
* The conversions are done for the Rbs_Version_Rec_Typ .
* Attributes :
* INPUT VALUES :
* P_Header_Rec : The record which hold's the rbs header's record .
* This contains the input record.
* P_Mode : The mode in which the procedure is called.Update or create
* 1 means update. 0 means create.
* OUTPUT VALUES :
*
* X_Header_Rec : The record which hold's the rbs header record with the
* changed values to the fields of the record.
* X_Error_Msg : The parameter will hold a message if there is an
* error in this API.
* There can be two modes in which this procedure can be called
* Update and create
* P_Mode = 1 means update
* P_Mode = 0 means create
* In Update
* if Update then rbs header id can be present.
* if rbs header id is not present then rbs header name would be present .
* you can retreive the rbs header id from the rbs header name .
* And then convert all GMiss to the db values.
*
* In create the rbs name would be present
* Convert all missing values to null.
*
* ****************************************************************************************
*/
PROCEDURE Convert_Missing_Rbs_Header
(P_Header_Rec IN Rbs_Header_Rec_Typ,
X_Header_Rec OUT NOCOPY Rbs_Header_Rec_Typ, -- 4537865 Added the nocopy hint
P_Mode IN Number,
X_Error_Msg OUT NOCOPY VARCHAR2)
IS
Cursor C_Rbs_Header_Details(P_Rbs_Header_Id IN Number) IS
Select a.rbs_header_id,
b.name,
b.Description,
a.Effective_From_Date,
a.Effective_To_Date,
a.Record_Version_Number
From pa_rbs_headers_b a,
pa_rbs_headers_tl b
Where a.rbs_header_id=P_Rbs_Header_Id
and b.language = userenv('LANG')
and a.rbs_header_id=b.rbs_header_id;
Select rbs_header_id
into l_rbs_header_id
from pa_rbs_headers_tl
where name= l_rbs_header_name
AND language = userenv('LANG');
Select rbs_header_id
into l_rbs_header_id
from pa_rbs_headers_b
where rbs_header_id = P_Header_Rec.Rbs_Header_Id ;
* and selects the value from the database if the value being input is null,
* so that there wouldn't be any change to the data when null is passed as input.
* The conversions are done for the Rbs_Version_Rec_Typ .
* Attributes :
* INPUT VALUES :
* P_Version_Rec : The record which hold's the rbs version's record .
* This contains the input record.
*
* OUTPUT VALUES :
*
* X_Version_Rec : The record which hold's the rbs version record with the
* changed values to the fields of the record.
* X_Error_Msg : The parameter will hold a message if there is an
* error in this API.
*
* There can be two modes in which this procedure can be called
* Update and create
* P_Mode = 1 means update
* P_Mode = 0 means create
* In Update
* if Update then rbs version id can be present.
* if rbs version id is not present then rbs version name would be present .
* you can retreive the rbs header id from the name .
* And then convert all GMiss to the db values.
*
* In create the rbs name would be present
* Convert all missing values to null.
* ****************************************************************************************
*/
PROCEDURE Convert_Missing_Rbs_Version
(P_Version_Rec IN Rbs_Version_Rec_Typ,
X_Version_Rec OUT NOCOPY Rbs_Version_Rec_Typ, -- 4537865
P_Mode IN Number,
X_Error_Msg OUT NOCOPY VARCHAR2)
IS
Cursor C_Rbs_Version_Details(P_Rbs_Version_Id IN Number) IS
Select a.rbs_version_id, b.name, b.Description,a.Version_Start_Date ,a.Job_Group_Id ,a.Record_Version_Number
from pa_rbs_versions_b a, pa_rbs_versions_tl b
Where a.rbs_version_id=P_Rbs_version_Id
and a.rbs_version_id=b.rbs_version_id;
Select rbs_version_id
Into l_rbs_version_id
From pa_rbs_versions_tl
Where name= l_rbs_version_name ;
* and selects the value from the database if the value being input is null,
* so that there wouldn't be any change to the data when null is passed as input.
* The conversions are done for the Rbs_Elements_Rec_Typ .
* Attributes :
* INPUT VALUES :
* P_Elements_Tbl : The table which hold's the rbs element's records .
* This contains the input records.
*
* OUTPUT VALUES :
*
* X_Elements_Tbl : The table which hold's the rbs element's records with the
* changed values
* X_Error_Msg : The parameter will hold a message if there is an
* error in this API.
*
* ****************************************************************************************
*/
PROCEDURE Convert_Missing_Rbs_Elements
(P_Elements_Tbl IN Rbs_Elements_Tbl_Typ,
X_Elements_Tbl OUT NOCOPY Rbs_Elements_Tbl_Typ, -- 4537865
P_Mode IN Number,
X_Error_Msg OUT NOCOPY VARCHAR2)
IS
Cursor C_Rbs_Elements_Details(P_Rbs_Element_Id IN Number) IS
Select
a.Rbs_Version_Id,
a.rbs_Element_Id ,
a.Parent_Element_Id ,
a.Resource_Type_Id,
a.Resource_Source_Id ,
b.resource_name Resource_Source_Code,
a.Rbs_Level,
a.Record_Version_Number,
a.Order_Number
From pa_rbs_elements a ,
pa_rbs_element_map b
Where rbs_element_id=P_Rbs_Element_Id
and a.resource_source_id =b.resource_id(+) ;
G_Rbs_Elements_Tbl.Delete;
Select
Max(Rbs_Level)
From
Pa_Rbs_Nodes_Temp;
Select
Rbs_Version_Id,
Rbs_Element_Id,
Parent_Element_Id,
Resource_Type_Id,
Resource_Source_Id,
Resource_Source_Code,
Order_Number,
Process_Type,
Rbs_Level,
Record_Version_Number,
Parent_Ref_Element_Id,
Rbs_Ref_Element_Id,
Record_Index
From
Pa_Rbs_Nodes_Temp
Where
Rbs_Level = P_Rbs_Level
Order By
Rbs_Ref_Element_Id;
Select
Count(*)
From
Pa_Rbs_Nodes_Temp
Where
Rbs_Level = 1;
Pa_Debug.G_Stage := 'Create Header Record by calling Pa_Rbs_Header_Pvt.Insert_Header() procedure.';
Pa_Rbs_Header_Pub.Insert_Header(
P_Commit => Fnd_Api.G_False,
P_Init_Msg_List => Fnd_Api.G_False,
P_API_Version_Number => P_API_Version_Number,
P_Name => l_Header_Rec.Name,
P_Description => Nvl(l_Header_Rec.Description,l_Header_Rec.Name),
P_EffectiveFrom => l_Header_Rec.Effective_From_Date,
P_EffectiveTo => l_Header_Rec.Effective_To_Date,
X_Rbs_Header_Id => l_Rbs_Header_Id,
X_Rbs_Version_Id => l_Rbs_Version_Id,
X_Rbs_Element_Id => l_Rbs_Element_Id,
X_Return_Status => X_Return_Status,
X_Msg_Data => X_Error_Msg_Data,
X_Msg_Count => l_Msg_Count);
Pa_Debug.G_Stage := 'The Pa_Rbs_Header_Pub.Insert_Header() procedure returned errror.';
Pa_Debug.G_Stage := 'Calling Pa_Rbs_Versions_Pub.Update_Working_Version() API.';
Pa_Rbs_Versions_Pub.Update_Working_Version(
P_Commit => Fnd_Api.G_False,
P_Init_Msg_List => Fnd_Api.G_False,
P_API_Version_Number => P_Api_Version_Number,
P_RBS_Version_Id => l_Rbs_Version_Id,
P_Name => Nvl(l_Version_Rec.Name,l_Header_Rec.Name),
P_Description => Nvl(l_Version_Rec.Description,Nvl(l_Header_Rec.Description,l_Header_Rec.Name)),
P_Version_Start_Date => Nvl(l_Version_Rec.Version_Start_Date,l_Header_Rec.Effective_From_Date),
P_Job_Group_Id => l_Version_Rec.Job_Group_Id,
P_Record_Version_Number => 1,
P_Init_Debugging_Flag => 'N',
X_Record_Version_Number => l_Record_Version_Number,
X_Return_Status => X_Return_Status,
X_Msg_Count => l_Msg_Count,
X_Error_Msg_Data => X_Error_Msg_Data);
Pa_Debug.G_Stage := 'The Pa_Rbs_Versions_Pub.Update_Working_Version() procedure returned errror. ' ||
'Add error message to stack.';
Pa_Debug.G_Stage := 'Insert record into pa_rbs_nodes_temp table for further processing.';
Insert into Pa_Rbs_Nodes_Temp (
Rbs_Version_Id,
Rbs_Element_Id,
Parent_Element_Id,
Resource_Type_Id,
Resource_Source_Id,
Resource_Source_Code,
Order_Number,
Process_Type,
Rbs_Level,
Record_Version_Number,
Parent_Ref_Element_Id,
Rbs_Ref_Element_Id,
Record_Index )
Values (
l_Rbs_Version_Id,
Decode(l_Elements_Tbl(i).Rbs_Level,1,l_Rbs_Element_Id,Null),
Decode(l_Elements_Tbl(i).Rbs_Level,2,l_Rbs_Element_Id,Null),
Decode(l_Elements_Tbl(i).Rbs_Level,1,-1,l_Elements_Tbl(i).Resource_Type_Id),
Decode(l_Elements_Tbl(i).Rbs_Level,1,l_Rbs_Version_Id,l_Elements_Tbl(i).Resource_Source_Id),
l_Elements_Tbl(i).Resource_Source_Code,
l_Elements_Tbl(i).Order_Number,
Decode(l_Elements_Tbl(i).Rbs_Level,1,'R',l_Elements_Tbl(i).Process_Type),
l_Elements_Tbl(i).Rbs_Level,
l_Elements_Tbl(i).Record_Version_Number,
l_Elements_Tbl(i).Parent_Ref_Element_Id,
l_Elements_Tbl(i).Rbs_Ref_Element_Id,
i );
Pa_Debug.G_Stage := 'Update the rbs_element_id in the pa_rbs_nodes_temp table.';
Update Pa_Rbs_Nodes_Temp
Set
Rbs_Element_Id = l_Rbs_Element_Id
Where
Rbs_Ref_Element_Id = Element_Rec.Rbs_Ref_Element_Id;
Pa_Debug.G_Stage := 'Update the parent_element_id in the pa_rbs_nodes_temp table where needed.';
Update Pa_Rbs_Nodes_Temp
Set
Parent_Element_Id = l_Rbs_Element_Id
Where
Parent_Ref_Element_Id = Element_Rec.Rbs_Ref_Element_Id;
G_Rbs_Elements_Out_Tbl.Delete;
G_Rbs_Elements_Out_Tbl.Delete;
* API Name: Update_Rbs()
* Description:
* This API can be used to update the RBS header, RBS Version, or the
* RBS Element/Node records or a combination of them.
*
* ********************************************************************************
*/
Procedure Update_Rbs(
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_Header_Rec IN Pa_Rbs_Pub.Rbs_Header_Rec_Typ,
P_Version_Rec IN Pa_Rbs_Pub.Rbs_Version_Rec_Typ,
P_Elements_Tbl IN Rbs_Elements_Tbl_Typ,
X_Elements_Tbl OUT NOCOPY Rbs_Elements_Tbl_Typ,
X_Return_Status OUT NOCOPY Varchar2,
X_Msg_Count OUT NOCOPY Number,
X_Error_Msg_Data OUT NOCOPY Varchar2)
Is
i Number := Null;
l_Api_Name Varchar2(30) := 'Update_Rbs';
l_process_type_index Number := 0; -- 1=D delete , 2=U update , 3=A add
Select
Max(Rbs_Level)
From
Pa_Rbs_Nodes_Temp;
Select
Rbs_Version_Id,
Rbs_Element_Id,
Parent_Element_Id,
Resource_Type_Id,
Resource_Source_Id,
Resource_Source_Code,
Order_Number,
Process_Type,
Rbs_Level,
Record_Version_Number,
Parent_Ref_Element_Id,
Rbs_Ref_Element_Id,
Record_Index
From
Pa_Rbs_Nodes_Temp
Where
Rbs_Level = P_Rbs_Level
And Process_Type = P_Process_Type
Order By
Rbs_Ref_Element_Id;
Select
Count(*)
From
Pa_Rbs_Nodes_Temp
Where
Rbs_Level = 1;
Select
tl.Name,
tl.Description,
b.Effective_From_Date,
b.Effective_To_Date,
b.record_version_number
From
Pa_Rbs_Headers_B b,
Pa_Rbs_Headers_TL tl
Where
b.Rbs_Header_Id = p_header_id
And b.Rbs_Header_Id = tl.Rbs_Header_Id
AND tl.language = userenv('LANG');
Select
tl.Name,
tl.Description,
b.Version_Start_Date,
b.Version_End_Date,
b.Job_Group_Id,
b.status_code,
b.record_version_number
From
Pa_Rbs_Versions_B b,
Pa_Rbs_Versions_TL tl
Where
b.Rbs_Version_Id = p_version_id
And b.Rbs_Version_Id = tl.Rbs_Version_Id;
Pa_Debug.G_Stage := 'Entering Update_Rbs().';
Pa_Debug.TrackPath('ADD','Update_Rbs');
Pa_Debug.G_Stage := 'Update Header Record by calling Pa_Rbs_Header_Pub.Update_Header() procedure.';
Pa_Rbs_Header_Pub.Update_Header(
P_Commit => Fnd_Api.G_False,
P_Init_Msg_List => Fnd_Api.G_False,
P_API_Version_Number => P_Api_Version_Number,
P_RbsHeaderId => l_Header_Rec.Rbs_Header_Id,
P_Name => l_Header_Rec.Name,
P_Description => l_Header_Rec.Description,
P_EffectiveFrom => l_Header_Rec.Effective_From_Date,
P_EffectiveTo => l_Header_Rec.Effective_To_Date,
P_RecordVersionNumber => l_Header_Rec.Record_Version_Number,
P_Process_Version => Fnd_Api.G_False,
X_Return_Status => X_Return_Status,
X_Msg_Data => X_Error_Msg_Data,
X_Msg_Count => l_Msg_Count);
Pa_Debug.G_Stage := 'The Pa_Rbs_Header_Pub.Update_Header() procedure returned error.';
Pa_Debug.G_Stage := 'Check if need to update the Version Record.';
Pa_Debug.G_Stage := 'Version is frozen.Cannot update any field.';
Pa_Debug.G_Stage := 'Update Version Record by calling Pa_Rbs_Versions_Pub.Update_Working_Version() procedure.';
Pa_Rbs_Versions_Pub.Update_Working_Version(
P_Commit => Fnd_Api.G_False,
P_Init_Msg_List => Fnd_Api.G_False,
P_API_Version_Number => P_Api_Version_Number,
P_RBS_Version_Id => l_Version_Rec.Rbs_Version_Id,
P_Name => l_Version_Rec.Name,
P_Description => l_Version_Rec.Description,
P_Version_Start_Date => l_Version_Rec.Version_Start_Date,
P_Job_Group_Id => l_Version_Rec.Job_Group_Id,
P_Record_Version_Number => l_Version_Rec.Record_Version_Number,
P_Init_Debugging_Flag => 'N',
X_Record_Version_Number => l_Record_Version_Number,
X_Return_Status => X_Return_Status,
X_Msg_Count => l_Msg_Count,
X_Error_Msg_Data => X_Error_Msg_Data);
Pa_Debug.G_Stage := 'The Pa_Rbs_Versions_Pub.Update_Working_Version() procedure returned status of error.';
SELECT status_code
INTO l_status_code
FROM PA_RBS_VERSIONS_B
WHERE rbs_version_id = l_Elements_Tbl(i).Rbs_Version_Id;
SELECT 'Y'
INTO l_validate
FROM pa_rbs_elements
WHERE rbs_version_id = l_Elements_Tbl(i).Rbs_Version_Id
AND rbs_element_id = l_Elements_Tbl(i).Rbs_Element_Id;
SELECT 'Y'
INTO l_validate
FROM pa_rbs_elements
WHERE rbs_version_id = l_Elements_Tbl(i).Rbs_Version_Id
AND rbs_element_id = l_Elements_Tbl(i).Parent_Element_Id;
SELECT 'Y'
INTO l_validate
FROM pa_rbs_elements
WHERE parent_element_id = l_Elements_Tbl(i).Parent_Element_Id
AND rbs_element_id = l_Elements_Tbl(i).Rbs_Element_Id;
Pa_Debug.G_Stage := 'Insert record into pa_rbs_nodes_temp table for further processing.';
Insert into Pa_Rbs_Nodes_Temp (
Rbs_Version_Id,
Rbs_Element_Id,
Parent_Element_Id,
Resource_Type_Id,
Resource_Source_Id,
Resource_Source_Code,
Order_Number,
Process_Type,
Rbs_Level,
Record_Version_Number,
Parent_Ref_Element_Id,
Rbs_Ref_Element_Id,
Record_Index )
Values (
l_Elements_Tbl(i).Rbs_Version_Id,
l_Elements_Tbl(i).Rbs_Element_Id,
l_Elements_Tbl(i).Parent_Element_Id,
Decode(l_Elements_Tbl(i).Rbs_Level,1,-1,l_Elements_Tbl(i).Resource_Type_Id),
Decode(l_Elements_Tbl(i).Rbs_Level,
1, l_Elements_Tbl(i).Rbs_Version_Id,
l_Elements_Tbl(i).Resource_Source_Id),
l_Elements_Tbl(i).Resource_Source_Code,
l_Elements_Tbl(i).Order_Number,
Decode(l_Elements_Tbl(i).Rbs_Level,1,'R',l_Elements_Tbl(i).Process_Type),
l_Elements_Tbl(i).Rbs_Level,
l_Elements_Tbl(i).Record_Version_Number,
l_Elements_Tbl(i).Parent_Ref_Element_Id,
l_Elements_Tbl(i).Rbs_Ref_Element_Id,
i );
Pa_Debug.G_Stage := 'Check if process type is Add to as to do needed updates on temp table.';
Pa_Debug.G_Stage := 'Update rbs_element_id based on temp rbs element id.';
Update Pa_Rbs_Nodes_Temp
Set
Rbs_Element_Id = l_Rbs_Element_Id
Where
Rbs_Ref_Element_Id = Element_Rec.Rbs_Ref_Element_Id;
Pa_Debug.G_Stage := 'Update parent_element_id based on temp parent id.';
Update Pa_Rbs_Nodes_Temp
Set
Parent_Element_Id = l_Rbs_Element_Id
Where
Parent_Ref_Element_Id = Element_Rec.Rbs_Ref_Element_Id
And Parent_Element_Id is Null;
Pa_Debug.G_Stage := 'Update the global elements out table with rbs_element_id and status.';
Pa_Debug.G_Stage := 'Update the out parameter x_element_tbl with rbs_element_id and parent id.';
Pa_Debug.G_Stage := 'Leaving Update_Rbs() procedure.';
Pa_Debug.TrackPath('STRIP','Update_Rbs');
G_Rbs_Elements_Out_Tbl.Delete;
G_Rbs_Elements_Out_Tbl.Delete;
End Update_Rbs;
* API Name: Exec_Update_Rbs
* Public/Private: Public
* Procedure/Function: Procedure
* Description:
* This API uses the data that was loaded via the load_rbs_header(),
* load_rbs_version(), and load_rbs_elements() API's to call the
* Update_Rbs() API.
* *************************************************************************************
*/
Procedure Exec_Update_Rbs(
P_Commit IN Varchar2 := Fnd_Api.G_False,
P_Init_Msg_List IN Varchar2 := Fnd_Api.G_True,
P_Api_Version_Number IN Number,
X_Return_Status OUT NOCOPY Varchar2,
X_Msg_Count OUT NOCOPY Number,
X_Msg_Data OUT NOCOPY Varchar2)
Is
l_Api_Version_Number CONSTANT NUMBER := G_Api_Version_Number;
l_Api_Name CONSTANT VARCHAR2(30) := 'Exec_Update_Rbs';
Pa_Rbs_Pub.Update_Rbs(
P_Commit => P_Commit,
P_Init_Msg_List => P_Init_Msg_List,
P_API_Version_Number => P_Api_Version_Number,
P_Header_Rec => G_Rbs_Hdr_Rec,
P_Version_Rec => G_Rbs_Ver_Rec,
P_Elements_Tbl => G_Rbs_Elements_Tbl,
X_Elements_Tbl => l_Dummy_Elements_Tbl,
X_Return_Status => X_Return_Status,
X_Msg_Count => X_Msg_Count,
X_Error_Msg_Data => X_Msg_Data);
End Exec_Update_Rbs;
* P_Rbs_Header_Name - the rbs header name of version selected to make a copy
* P_Rbs_Version_Number - the rbs versions version number
*****************************************************************************************************
*/
Procedure Copy_Rbs_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 Default PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
P_Rbs_Header_Id IN Number Default PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
P_Rbs_Header_Name IN Varchar2 Default PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
P_Rbs_Version_Number IN Number Default PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
P_Rec_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) := 'Copy_Rbs_Working_Version';
Select
rbs_version_id
From
Pa_Rbs_Versions_b
Where
rbs_header_id=P_Header_Id
And
version_number=P_Version_Number;
Select
rbs_version_id
From
Pa_Rbs_Versions_b ver, Pa_Rbs_Headers_tl Hdr
Where
hdr.name=P_Header_Name
And
hdr.language = userenv('LANG')
And
hdr.rbs_header_id=ver.rbs_header_id
And
ver.version_number=P_Version_Number;
Select
rbs_header_id
From
Pa_Rbs_Versions_b
Where
rbs_version_id=P_RBS_Version_Id;
Select
Rbs_Version_Id
From
Pa_Rbs_Versions_b
Where
rbs_header_id=P_Header_Id
And
status_code='WORKING';
Select
Rbs_Version_Id
From
Pa_Rbs_Versions_b ver, Pa_Rbs_Headers_tl Hdr
Where
Hdr.name=P_Header_Name
And
hdr.language = userenv('LANG')
And
Hdr.rbs_header_id=Ver.rbs_header_id
And
Ver.status_code='WORKING';
Select rbs_version_id
From Pa_Rbs_Versions_b
Where rbs_header_id = P_Header_Id
And current_reporting_flag = 'Y'; -- Added
Select rbs_version_id
From Pa_Rbs_Versions_b ver,
Pa_Rbs_Headers_tl Hdr
Where hdr.name = P_Header_Name
And hdr.language = userenv('LANG')
And hdr.rbs_header_id = ver.rbs_header_id
And ver.current_reporting_flag = 'Y'; -- Added
SELECT nvl(current_reporting_flag, 'N')
INTO l_current_flag
FROM pa_rbs_versions_b
WHERE Rbs_Version_Id = l_Rbs_Version_Id;
Select
Meaning
From
Pa_Lookups
Where
Lookup_Type = 'PA_RBS_API_ERR_TOKENS'
And Lookup_code = P_Lookup_Code;
Select
Outline_Number
From
Pa_Rbs_Elements
Where
Rbs_Element_Id = P_Id;