[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
This api Updates RBS Header. It also updates the working version of this RBS
=============================================================================*/
-- Procedure : UPDATE_HEADER
-- Type : Public Procedure
-- Purpose : This API will be used to update the RBS header for a particular rbs header id.
-- : This API will be called from following page:
-- : 1.Rbs Header Page
-- Note : This API will does all the business validations.
-- : -- If no errors are encounterd it updates the pa_rbs_headers table.
-- : -- The validations made are
-- : -- Rbs Header name should be unique
-- : -- Rbs From date cannot be null
-- : -- Rbs To date cannot be less than Rbs from date
-- : -- Rbs From date cannot be updated if there atleast one published version
-- : -- Rbs To date cannot be less than its versions
-- : -- If a Rbs is not having any published version then update is allowed on both header and versions for
-- : -- every attribute.
-- : -- If a Rbs has atleast one published version then update is not allowed on the versions table.
-- : -- If a Rbs has rules as its elements then user cant check(setting it to 'Y') use_for_alloc_flag.
-- : -- If a Rbs has alteast one freezed version and is used in allocation rule then user cant uncheck
-- : -- Use_For_Alloc_Flag ( i.e setting it to 'N')
-- Assumptions :
-- Parameters Type Required Description and Purpose
-- --------------------------- ------ -------- --------------------------------------------------------
-- P_Commit Varchar2 No
-- P_Init_Msg_List Varchar2 No
-- P_API_Version_Number Varchar2 Yes
-- P_RbsHeaderId NUMBER Yes The value will contain the Rbs Header id which is the
-- unique identifier.
-- P_Name VARCHAR2 Yes 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.
-- P_Use_For_Alloc_Flag VARCHAR2 NO This determine whether a Rbs can be used in allocation rule or not.
-- P_RecordVersionNumber NUMBER Yes The record version number of the rbs header which is
-- used to ensure syncronization.
Procedure Update_Header(
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_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 Default 'N',
P_RecordVersionNumber IN Number,
P_Process_Version IN Varchar2 Default Fnd_Api.G_True,
X_Return_status OUT NOCOPY Varchar2,
X_Msg_Data OUT NOCOPY Varchar2,
X_Msg_Count OUT NOCOPY Number )
IS
-- This cursor selects the record version number for the header which needs to be updated
Cursor c_Record_Ver_No Is
Select
record_version_number
From
pa_rbs_headers_b
Where
rbs_header_id=p_rbsHeaderId
For Update Of Effective_From_Date NoWait;
l_Api_Name Varchar2(30) := 'Update_Header';
Pa_Debug.G_Stage := 'Entering Update_Header().';
Pa_Debug.TrackPath('ADD','Update_Header');
Select
Name
Into
l_Name
From
Pa_Rbs_Headers_TL
Where
Rbs_Header_Id = P_RbsHeaderId
And language = USERENV('LANG');
Select
Count(*)
Into
l_Count
From
Pa_Rbs_Headers_TL
Where
Name = P_Name
And language = USERENV('LANG');
Pa_Debug.G_Stage := 'Use cursor to lock the header record for update.';
Pa_Debug.G_Stage := 'The record version number does not match up. The record has already been update ' ||
'other others. Add message to error stack.';
SELECT use_for_Alloc_Flag
INTO l_Use_For_Alloc_Flag
FROM pa_rbs_headers_b
WHERE rbs_header_id=P_RbsHeaderId;
Select
Max(Rbs_Header_Id),
Max(Version_End_Date)
Into
l_Check,
l_Effect_To_Date
From
Pa_Rbs_Versions_B
Where
Rbs_Header_Id = P_RbsHeaderId
And Status_Code <> 'WORKING';
Pa_Debug.G_Stage := 'No frozen version record was found. Update the header information by calling ' ||
'the Pa_Rbs_Header_Pvt.Update_Header() procedure.';
Pa_Rbs_Header_Pvt.Update_Header(
P_RbsHeaderId => P_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,
X_Return_Status => X_Return_Status,
X_Msg_Data => X_Msg_Data,
X_Msg_Count => X_Msg_Count);
Pa_Debug.G_Stage := 'Check if return status from call to Pa_Rbs_Header_Pvt.Update_Header() procedure is U.';
Pa_Debug.G_Stage := 'Call to Pa_Rbs_Header_Pvt.Update_Header() procedure returned Unexpected error. Raise.';
Pa_Debug.G_Stage := 'No frozen version record was found. Check if allow to update the version record in this module.';
Pa_Debug.G_Stage := 'No frozen version record was found. Update the working version record for ' ||
'the Rbs header by calling the Pa_Rbs_Header_Pvt.Update_Versions procedure.';
Pa_Rbs_Header_Pvt.Update_Versions(
P_RbsHeaderId => P_RbsHeaderId,
P_Name => P_Name,
P_EffectiveFrom => P_EffectiveFrom,
X_Return_Status => X_Return_Status,
X_Msg_Data => X_Msg_Data,
X_Msg_Count => X_Msg_Count);
Pa_Debug.G_Stage := 'Check if return status from call to Pa_Rbs_Header_Pvt.Update_Versions() procedure is U.';
Pa_Debug.G_Stage := 'Call to Pa_Rbs_Header_Pvt.Update_Versions() procedure returned ' ||
'Unexpected error. Raise.';
-- name can be updated but should not be reflected in versions. Effective to can be updated but validated such that
-- it cannot be less than effective to date of its versions. effective from cannot be updated
-- Also if use_for_alloc_flag is unchecked test for its usage in allocation rules. If yes raise appropriate error.
/* validation: effective from cannot be updated*/
Pa_Debug.G_Stage := 'Found frozen versions for the rbs. Get the effective from date in the header.';
Select
Effective_From_Date
Into
l_EffectiveFromDate
From
Pa_Rbs_Headers_B
Where
Rbs_Header_Id = P_RbsHeaderId;
-- VALIDATION: Effective to if being updated to a not null value for the rbs header must not be
-- less than effective to of rbs frozen versions*/
-- Pa_Debug.G_Stage := 'Check if the effective_to_date is being changed. Check If not null then if < max ' ||
-- 'version_end_date of frozen versions.';
--Updates the header information for the Rbs
Pa_Debug.G_Stage := 'Update the rbs header record - 2 by calling the Pa_Rbs_Header_Pvt.Update_Header() procedure.';
Pa_Rbs_Header_Pvt.Update_Header(
p_rbsHeaderId,
p_name,
p_description,
p_effectiveFrom,
p_effectiveTo,
p_use_for_alloc_flag,
x_return_status,
x_msg_data,
x_msg_count);
Pa_Debug.G_Stage := 'Check if return status from call to Pa_Rbs_Header_Pvt.Update_Header() procedure is U.';
Pa_Debug.G_Stage := 'Call to Pa_Rbs_Header_Pvt.Update_Header() procedure returned Unexpected error. Raise.';
Pa_Debug.G_Stage := 'Commit inserts to db.';
Pa_Debug.G_Stage := 'Leaving Update_Header() procedure.';
Pa_Debug.TrackPath('STRIP','Update_Header');
END Update_Header;
PROCEDURE Insert_Header(
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_Name IN Varchar2,
P_Description IN Varchar2,
P_EffectiveFrom IN Date,
P_EffectiveTo IN Date,
P_Use_For_Alloc_Flag IN Varchar2 Default 'N',
X_Rbs_Header_Id OUT NOCOPY Number,
X_Rbs_Version_Id OUT NOCOPY Number,
X_Rbs_Element_Id OUT NOCOPY Number,
X_Return_Status OUT NOCOPY Varchar2,
X_Msg_Data OUT NOCOPY Varchar2,
X_Msg_Count OUT NOCOPY Number )
Is
l_Rbs_Header_Id Number(15) := Null;
l_Api_Name Varchar2(30) := 'Insert_Header';
Pa_Debug.G_Stage := 'Entering Insert_Header().';
Pa_Debug.TrackPath('ADD','Insert_Header');
Select
Count(*)
Into
l_Count
From
Pa_Rbs_Headers_TL
Where
Name = P_Name
And language = USERENV('LANG');
--Inserts into pa_rbs_header table.
Pa_Debug.G_Stage := 'Insert header record by calling the Pa_Rbs_Header_Pvt.Insert_Header() procedure.';
Pa_Rbs_Header_Pvt.Insert_Header(
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,
X_RbsHeaderId => X_Rbs_Header_Id,
X_Return_Status => X_Return_Status,
X_Msg_Data => X_Msg_Data,
X_Msg_Count => X_Msg_Count);
Pa_Debug.G_Stage := 'Calling to Pa_Rbs_Header_Pvt.Insert_Header() procedure returned error. Raise.';
Pa_Debug.G_Stage := 'Insert the version record by calling the Pa_Rbs_Header_Pvt.Insert_Versions() procedure.';
Pa_Rbs_Header_Pvt.Insert_Versions(
P_RbsHeaderId => X_Rbs_Header_Id,
P_Name => P_Name,
P_Description => P_Description,
P_EffectiveFrom => P_EffectiveFrom,
X_Rbs_Version_Id => X_Rbs_Version_Id,
X_Return_Status => X_Return_Status,
X_Msg_Data => X_Msg_Data,
X_Msg_Count => X_Msg_Count );
Pa_Debug.G_Stage := 'Calling to Pa_Rbs_Header_Pvt.Insert_Versions() procedure returned error. Raise.';
Pa_Debug.G_Stage := 'Insert the root element node for the rbs by callling the Pa_Rbs_Header_Pvt.Insert_Structure_Element() procedure.';
Pa_Rbs_Header_Pvt.Insert_Structure_Element(
P_Rbs_Version_Id => X_Rbs_Version_Id,
X_Rbs_Element_Id => X_Rbs_Element_Id,
X_Return_Status => X_Return_Status,
X_Error_Msg_Data => X_Msg_Data,
X_Msg_Count => X_Msg_Count);
Pa_Debug.G_Stage := 'Calling to Pa_Rbs_Header_Pvt.Insert_Structure_Element() procedure returned error. Raise.';
Pa_Debug.G_Stage := 'Commit inserts to db.';
Pa_Debug.G_Stage := 'Leaving Insert_Header() procedure.';
Pa_Debug.TrackPath('STRIP','Insert_Header');
END Insert_Header;