[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Pa_Debug.G_Stage := 'Call DeleteRbsElement() procedure.';
SELECT rbs_header_id
INTO l_rbs_header_id
FROM pa_rbs_versions_b
WHERE rbs_version_id = p_rbs_version_id;
Pa_Rbs_Elements_Pvt.DeleteRbsElement(
P_RBS_Version_Id => P_RBS_Version_Id,
P_Element_Id => P_Element_Id,
X_Error_Msg_Data => X_Error_Msg_Data);
Pa_Debug.G_Stage := 'Call UpdateExistingRbsElement() procedure.';
Pa_Rbs_Elements_Pvt.UpdateExisingRbsElement(
P_Rbs_Version_Id => P_Rbs_Version_Id,
P_Parent_Element_Id => P_Parent_Element_Id,
P_Rbs_Element_Id => P_Element_Id,
P_Resource_Type_Id => P_Resource_Type_Id,
P_Resource_Source_Id => P_Resource_Source_Id,
P_Order_Number => P_Order_Number,
P_Level_code => P_Level_code,--15834912
P_Cost_code => P_Cost_code,--15834912
P_Cost_code_Name => P_Cost_code_Name,--16430696
X_Error_Msg_Data => X_Error_Msg_Data);
PROCEDURE DeleteRbsElement(
P_RBS_Version_Id IN Number,
P_Element_Id IN Number,
X_Error_Msg_Data OUT NOCOPY Varchar2)
IS
CURSOR c1 (P_Rbs_Elem_Id IN Number) IS
SELECT rbs_element_id
FROM pa_rbs_elements
START WITH rbs_element_Id = p_rbs_elem_id
CONNECT BY prior rbs_element_id = parent_element_Id;
SELECT rbs_element_id, outline_number
FROM pa_rbs_elements
WHERE rbs_version_id = p_rbs_version_id
AND parent_element_Id = p_parent_id
AND to_number(replace(outline_number, '.')) >
to_number(replace(p_del_outline_number, '.'))
ORDER BY to_number(replace(outline_number, '.'));
SELECT rbs_element_id
FROM pa_rbs_elements
START WITH rbs_element_Id = p_rbs_elem_id
CONNECT BY prior rbs_element_id = parent_element_Id;
SELECT parent_element_Id, outline_number
INTO l_parent_id, l_del_outline_number
FROM pa_rbs_elements
WHERE rbs_element_id = P_Element_Id;
SELECT outline_number
INTO l_parent_outline_number
FROM pa_rbs_elements
WHERE rbs_element_id = l_parent_id;
Pa_Debug.G_Stage := 'Entering DeleteRbsElement().';
Pa_Debug.TrackPath('ADD','DeleteRbsElement');
Pa_Debug.G_Stage := 'Call DeleteRbsElement() procedure.';
Pa_Debug.G_Stage := 'Delete the children elements/nodes using cursor and loop.';
Pa_Debug.G_Stage := 'Delete child element/node by calling table handler.';
Pa_Rbs_Elements_Pkg.Delete_Row(P_Rbs_Element_Id => l_id);
Pa_Debug.G_Stage := 'Could not find the element/node to delete.';
UPDATE pa_rbs_elements
SET outline_number = l_new_outline
WHERE rbs_element_id = l_upd_rbs_id;
UPDATE pa_rbs_elements
SET outline_number = replace(substr(outline_number, 1,
length(l_upd_outline_number) + 1),
l_upd_outline_number || '.',
l_new_outline || '.') ||
substr(outline_number, length(l_upd_outline_number) + 2)
WHERE rbs_element_Id = l_child_id;
Pa_Debug.G_Stage := 'Leaving DeleteRbsElement() procedure.';
Pa_Debug.TrackPath('STRIP','DeleteRbsElement');
END DeleteRbsElement;
PROCEDURE UpdateExisingRbsElement(
P_Rbs_Version_Id IN Number,
P_Parent_Element_Id IN Number,
P_Rbs_Element_Id IN Number,
P_Resource_Type_Id IN Number,
P_Resource_Source_Id IN Number,
P_Order_Number IN Number,
P_Level_Code IN VARCHAR2 default null,--15834912
P_COST_CODE IN VARCHAR2 default null,--15834912
P_COST_CODE_NAME IN VARCHAR2 default null,--16430696
X_Error_Msg_Data OUT NOCOPY Varchar2)
IS
l_Person_Id Number := Null;
Select Rbs_Element_Id
From Pa_Rbs_Elements
Where Rule_Flag = 'Y'
Start With Parent_Element_Id = P_Rbs_Element_Id
Connect By Prior Rbs_Element_Id = Parent_Element_Id;
Pa_Debug.G_Stage := 'Entering UpdateExisingRbsElement().';
Pa_Debug.TrackPath('ADD','UpdateExisingRbsElement');
-- call the table handler to update the record.
Pa_Debug.G_Stage := 'Call Pa_Rbs_Elements_Pkg.Update_Row() procedure.';
Pa_Rbs_Elements_Pkg.Update_Row(
P_Rbs_Element_Id => P_Rbs_Element_Id,
P_Rbs_Element_Name_Id => l_Rbs_Element_Name_Id,
P_Rbs_Version_Id => P_Rbs_Version_Id,
P_Outline_Number => l_outline_number,
P_Order_Number => l_Order_Number,
P_Resource_Type_Id => P_Resource_Type_Id,
P_Resource_Source_Id => P_Resource_Source_Id,
P_Person_Id => l_Person_Id,
P_Job_Id => l_Job_Id,
P_Organization_Id => l_Organization_Id,
P_Expenditure_Type_Id => l_Exp_Type_Id,
P_Event_Type_Id => l_Event_Type_Id,
P_Expenditure_Category_Id => l_Exp_Cat_Id,
P_Revenue_Category_Id => l_Rev_Cat_Id,
P_Inventory_Item_Id => l_Inv_Item_Id,
P_Item_Category_Id => l_Item_Cat_Id,
P_BOM_Labor_Id => l_BOM_Labor_Id,
P_BOM_Equipment_Id => l_BOM_Equip_Id,
P_Non_Labor_Resource_Id => l_Non_Labor_Res_Id,
P_Role_Id => l_Role_Id,
P_Person_Type_ID => l_Person_Type_Id,
P_Resource_Class_Id => l_Res_Class_Id,
P_Supplier_Id => l_Supplier_Id,
P_Rule_Flag => l_Rule_Flag,
P_Parent_Element_Id => P_Parent_Element_Id,
P_Rbs_Level => l_Rbs_Level,
P_Element_Identifier => l_Element_Identifier,
P_User_Created_Flag => 'Y',
P_User_Defined_Custom1_Id => l_User_Def_Custom1_Id,
P_User_Defined_Custom2_Id => l_User_Def_Custom2_Id,
P_User_Defined_Custom3_Id => l_User_Def_Custom3_Id,
P_User_Defined_Custom4_Id => l_User_Def_Custom4_Id,
P_User_Defined_Custom5_Id => l_User_Def_Custom5_Id,
P_LEVEL_CODE =>P_LEVEL_CODE,--15834912
P_COST_CODE =>P_COST_CODE,--15834912
P_COST_CODE_NAME =>P_COST_CODE_NAME,--16430696
P_Last_Update_Date => Pa_Rbs_Elements_Pvt.G_Last_Update_Date,
P_Last_Updated_By => Pa_Rbs_Elements_Pvt.G_Last_Updated_By,
P_Last_Update_Login => Pa_Rbs_Elements_Pvt.G_Last_Update_Login,
X_Error_Msg_Data => X_Error_Msg_Data);
--We need to update the value of rule flag for all Child nodes below the current node.
--If rule is changed to instance then all child nodes below it should have
--rule flag = 'N'.
--If instance is changed to rule and it is not below any other instance in the
--hierarchy then all rule nodes below it (that are not under any other instance node)
--should have rule flag = 'Y'.
If P_Resource_Source_Id <> -1 Then
--For bug 4047578:perf fix
OPEN Read_Element_Id_c;
Update Pa_Rbs_Elements
Set Rule_Flag = 'N'
Where Rbs_Element_Id =l_Element_Id;
Update Pa_Rbs_Elements
Set Rule_Flag = 'Y'
Where Rbs_Element_Id In ( Select Rbs_Element_Id
From Pa_Rbs_Elements
Start With Rbs_Element_Id = P_Rbs_Element_Id
Connect By Prior Rbs_Element_Id = Parent_Element_Id
And Resource_Source_Id = -1 );
PA_Debug.G_Stage := 'Call Pa_Rbs_Elements_Pvt.Update_Children_Data() procedure.';
Pa_Rbs_Elements_Pvt.Update_Children_Data(
P_Rbs_Element_Id => P_Rbs_Element_Id,
X_Error_Msg_Data => X_Error_Msg_Data);
Pa_Debug.G_Stage := 'Leaving UpdateExisingRbsElement() procedure.';
Pa_Debug.TrackPath('STRIP','UpdateExisingRbsElement');
END UpdateExisingRbsElement;
-- call the table handler to update the record.
Pa_Debug.G_Stage := 'Call Pa_Rbs_Elements_Pkg.Insert_Row() procedure.';
Pa_Rbs_Elements_Pkg.Insert_Row(
P_Rbs_Element_Name_Id => l_Rbs_Element_Name_Id,
P_Rbs_Version_Id => P_Rbs_Version_Id,
P_Outline_Number => l_outline_number,
P_Order_Number => l_Order_Number,
P_Resource_Type_Id => P_Resource_Type_Id,
P_Resource_Source_Id => P_Resource_Source_Id,
P_Person_Id => l_Person_Id,
P_Job_Id => l_Job_Id,
P_Organization_Id => l_Organization_Id,
P_Expenditure_Type_Id => l_Exp_Type_Id,
P_Event_Type_Id => l_Event_Type_Id,
P_Expenditure_Category_Id => l_Exp_Cat_Id,
P_Revenue_Category_Id => l_Rev_Cat_Id,
P_Inventory_Item_Id => l_Inv_Item_Id,
P_Item_Category_Id => l_Item_Cat_Id,
P_BOM_Labor_Id => l_BOM_Labor_Id,
P_BOM_Equipment_Id => l_BOM_Equip_Id,
P_Non_Labor_Resource_Id => l_Non_Labor_Res_Id,
P_Role_Id => l_Role_Id,
P_Person_Type_Id => l_Person_Type_Id,
P_Resource_Class_Id => l_Res_Class_Id,
P_Supplier_Id => l_Supplier_Id,
P_Rule_Flag => l_Rule_Flag,
P_Parent_Element_Id => P_Parent_Element_Id,
P_Rbs_Level => l_Rbs_Level,
P_Element_Identifier => l_Element_Identifier,
P_User_Created_Flag => 'Y',
P_User_Defined_Custom1_Id => l_User_Def_Custom1_Id,
P_User_Defined_Custom2_Id => l_User_Def_Custom2_Id,
P_User_Defined_Custom3_Id => l_User_Def_Custom3_Id,
P_User_Defined_Custom4_Id => l_User_Def_Custom4_Id,
P_User_Defined_Custom5_Id => l_User_Def_Custom5_Id,
P_Last_Update_Date => Pa_Rbs_Elements_Pvt.G_Last_Update_Date,
P_Last_Updated_By => Pa_Rbs_Elements_Pvt.G_Last_Updated_By,
P_Last_Update_Login => Pa_Rbs_Elements_Pvt.G_Last_Update_Login,
P_Creation_Date => Pa_Rbs_Elements_Pvt.G_Creation_Date,
P_Created_By => Pa_Rbs_Elements_Pvt.G_Created_By,
P_LEVEL_CODE =>P_LEVEL_CODE,--15834912
P_COST_CODE =>P_COST_CODE,--15834912
P_COST_CODE_NAME =>P_COST_CODE_NAME,--16430696
X_Rbs_Element_Id => X_Rbs_Element_Id,
X_Error_Msg_Data => X_Error_Msg_Data);
SELECT 'Y', rbs_element_id
FROM pa_rbs_elements
WHERE resource_type_id = p_resource_type_id
AND resource_source_id = p_resource_source_id
AND Rbs_Version_Id = P_Rbs_Version_Id
AND rbs_element_id <> nvl(p_rbs_element_id, -99)
AND rbs_level = P_rbs_level;
SELECT 'Y'
FROM pa_rbs_elements
WHERE resource_type_id = p_resource_type_id
AND resource_source_id = p_resource_source_id
AND Rbs_Version_Id = P_Rbs_Version_Id
AND rbs_element_id <> nvl(p_rbs_element_id, -99);
SELECT resource_type_id,
resource_source_id
INTO l_old_resource_type_id,
l_old_resource_source_id
FROM PA_RBS_ELEMENTS
WHERE rbs_element_id = p_rbs_element_id;
Select
Decode(r.parent_element_id,null,Decode(P_Resource_Source_Id,-1,'Y','N'),
Decode(r.rule_flag,'Y',Decode(P_Resource_Source_Id,-1,'Y','N'),'N'))
Into
X_Rule_Based_Flag
From
pa_rbs_elements r
Where
r.rbs_element_id = P_Parent_Element_Id; --End of bug fix 3736374.
SELECT 'N'
INTO l_unique_branch
FROM pa_rbs_elements
WHERE rbs_element_id = l_exists_element_id
AND nvl(person_id, -99) = nvl(x_person_id, -99)
AND nvl(organization_id, -99) = nvl(x_organization_id, -99)
AND nvl(job_id, -99) = nvl(x_job_id, -99)
AND nvl(supplier_id, -99) = nvl(x_supplier_id, -99)
AND nvl(expenditure_type_id, -99) = nvl(x_exp_type_id, -99)
AND nvl(event_type_id, -99) = nvl(x_event_type_id, -99)
AND nvl(revenue_category_id, -99) = nvl(x_rev_cat_id, -99)
AND nvl(inventory_item_id, -99) = nvl(x_inv_item_id, -99)
AND nvl(item_category_id, -99) = nvl(x_item_cat_id, -99)
AND nvl(bom_labor_id, -99) = nvl(x_bom_labor_id, -99)
AND nvl(bom_equipment_id, -99) = nvl(x_bom_equip_id, -99)
AND nvl(person_type_id, -99) = nvl(x_person_type_id, -99)
AND nvl(resource_class_id, -99) = nvl(x_res_class_id, -99)
AND nvl(role_id, -99) = nvl(x_role_id, -99)
AND nvl(non_labor_resource_id, -99) = nvl(x_non_labor_res_id, -99)
AND nvl(expenditure_category_id, -99) = nvl(x_exp_cat_id,-99)
AND nvl(User_Defined_Custom1_Id, -99) = nvl(X_User_Def_Custom1_Id, -99)
AND nvl(User_Defined_Custom2_Id, -99) = nvl(X_User_Def_Custom2_Id, -99)
AND nvl(User_Defined_Custom3_Id, -99) = nvl(X_User_Def_Custom3_Id, -99)
AND nvl(User_Defined_Custom4_Id, -99) = nvl(X_User_Def_Custom4_Id, -99)
AND nvl(User_Defined_Custom5_Id, -99) = nvl(X_User_Def_Custom5_Id, -99);
SELECT 'N'
INTO l_unique_branch
FROM pa_rbs_elements
WHERE rbs_element_id <> nvl(P_Rbs_Element_Id,-99)
AND rbs_version_id = P_Rbs_Version_Id
AND rbs_level = X_Rbs_level
AND nvl(person_id, -99) = nvl(x_person_id, -99)
AND nvl(organization_id, -99) = nvl(x_organization_id, -99)
AND nvl(job_id, -99) = nvl(x_job_id, -99)
AND nvl(supplier_id, -99) = nvl(x_supplier_id, -99)
AND nvl(expenditure_type_id, -99) = nvl(x_exp_type_id, -99)
AND nvl(event_type_id, -99) = nvl(x_event_type_id, -99)
AND nvl(revenue_category_id, -99) = nvl(x_rev_cat_id, -99)
AND nvl(inventory_item_id, -99) = nvl(x_inv_item_id, -99)
AND nvl(item_category_id, -99) = nvl(x_item_cat_id, -99)
AND nvl(bom_labor_id, -99) = nvl(x_bom_labor_id, -99)
AND nvl(bom_equipment_id, -99) = nvl(x_bom_equip_id, -99)
AND nvl(person_type_id, -99) = nvl(x_person_type_id, -99)
AND nvl(resource_class_id, -99) = nvl(x_res_class_id, -99)
AND nvl(role_id, -99) = nvl(x_role_id, -99)
AND nvl(non_labor_resource_id, -99) = nvl(x_non_labor_res_id, -99)
AND nvl(expenditure_category_id, -99) = nvl(x_exp_cat_id,-99)
AND nvl(User_Defined_Custom1_Id, -99) = nvl(X_User_Def_Custom1_Id, -99)
AND nvl(User_Defined_Custom2_Id, -99) = nvl(X_User_Def_Custom2_Id, -99)
AND nvl(User_Defined_Custom3_Id, -99) = nvl(X_User_Def_Custom3_Id, -99)
AND nvl(User_Defined_Custom4_Id, -99) = nvl(X_User_Def_Custom4_Id, -99)
AND nvl(User_Defined_Custom5_Id, -99) = nvl(X_User_Def_Custom5_Id, -99);
Select
use_for_alloc_flag
Into
l_use_for_alloc_flag
From
pa_rbs_headers_vl h,
pa_rbs_versions_vl v
where
v.rbs_version_id=P_Rbs_Version_Id
And
h.rbs_header_id=v.rbs_header_id;
Pa_Debug.G_Stage := 'Check if update or add to determine element identifier value.';
Select Pa_Rbs_Element_Identifier_S.NextVal
Into X_Element_Identifier
From Dual;
Pa_Debug.G_Stage := 'Retrieve the element identifier value from pa_rbs_elements for update.';
Select Element_Identifier
Into X_Element_Identifier
From Pa_Rbs_Elements
Where Rbs_Element_Id = P_Rbs_Element_Id;
SELECT count(*)
INTO l_number_of_peers
FROM PA_RBS_ELEMENTS
WHERE parent_element_id = p_parent_element_id
AND USER_CREATED_FLAG = 'Y';
SELECT outline_number
INTO x_outline_number
FROM PA_RBS_ELEMENTS
WHERE RBS_ELEMENT_ID = P_RBS_ELEMENT_ID;
SELECT Count(*)
FROM PA_RBS_ELEMENTS
WHERE Rbs_Version_Id = P_RbsVersionId
AND Resource_Type_Id = P_ResTypeId
AND Resource_Source_Id = P_ResSourceId
AND parent_element_id = p_parentId;
Select Count(*)
From Pa_Rbs_Elements
Where Resource_Type_Id = P_Res_Type_Id
And Resource_Source_Id = P_Res_Srce_Id
Start With Rbs_Element_Id = P_Parent_Elem_Id
Connect By Prior Parent_Element_Id = Rbs_Element_Id;
Select Count(*)
From Pa_Rbs_Elements
Where Resource_Type_Id = P_Res_Type_Id
And Resource_Source_Id = P_Res_Srce_Id
Start With Parent_Element_Id = P_Rbs_Elem_Id
Connect By Prior Rbs_Element_Id = Parent_Element_Id;
SELECT rbs_level + 1
FROM Pa_Rbs_Elements
WHERE Rbs_Element_Id = P_Parent_Id;
SELECT Count(*)
FROM Pa_Rbs_Elements
WHERE Resource_Type_Id = P_Res_Type_Id
AND Rbs_Level not in (P_Rbs_Level, P_Rbs_Level - 1, P_Rbs_Level + 1)
AND Rbs_Version_Id = P_Version_Id;
SELECT rbs_element_id , resource_type_id, resource_source_id
FROM pa_rbs_elements
WHERE parent_element_id = P_Rbs_Element_Id;
SELECT NVL(X.CBS_ENABLED,'N')
FROM PA_RBS_HEADERS_VL X , PA_RBS_VERSIONS_VL Y
WHERE Y.RBS_HEADER_ID = X.RBS_HEADER_ID
AND Y.RBS_VERSION_ID = P_Rbs_Version_Id;
SELECT resource_type_id, resource_source_id
INTO l_parent_resource_type_id, l_parent_resource_source_id
FROM PA_RBS_ELEMENTS
WHERE rbs_element_id = P_Parent_Element_Id;
SELECT count(*)
INTO l_dummy_count
FROM PA_RBS_ELEMENTS
WHERE RESOURCE_TYPE_ID = P_Resource_Type_Id
START WITH rbs_element_id = p_parent_element_id
CONNECT BY PRIOR parent_element_id = rbs_element_id;
SELECT count(*)
INTO l_dummy_count
FROM PA_RBS_ELEMENTS
WHERE RESOURCE_TYPE_ID = P_Resource_Type_Id
START WITH parent_element_id = c1.rbs_element_id
CONNECT BY PRIOR rbs_element_id = parent_element_id;
select count(*)
INTO l_dummy_count
FROM PA_RBS_ELEMENTS
WHERE PARENT_ELEMENT_ID = p_rbs_element_id
AND RESOURCE_TYPE_ID = p_old_resource_type_id;
Select
Resource_Name
From
Pa_Rbs_Element_Map
Where
Resource_Type_Id = P_Res_Type_Id
And Resource_Id = P_Res_Srce_Id;
Select
'Y'
From
Pa_Event_Types
Where
Event_Type_Id = P_Id
And Event_Type_Classification IN ('AUTOMATIC','MANUAL','WRITE OFF','WRITE ON');
Select
'Y'
From
Pa_Expenditure_Types
Where
Expenditure_Type_Id = P_Id;
Select
'Y'
From
Pa_Lookups
Where
Lookup_Code = P_Code
And Lookup_Type = 'REVENUE CATEGORY';
Select
'Y'
From
Per_People_X
Where
Person_Id = P_Id
And ( (Pa_Cross_Business_Grp.IsCrossBGProfile = 'N' AND
Fnd_Profile.Value('PER_BUSINESS_GROUP_ID') = Per_People_X.Business_Group_Id)
OR Pa_Cross_Business_Grp.IsCrossBGProfile = 'Y');
Select
'Y'
From
Per_Jobs
Where
Job_Id = P_Id
And ( (Pa_Cross_Business_Grp.IsCrossBGProfile = 'N' AND
Fnd_Profile.Value('PER_BUSINESS_GROUP_ID') = Per_Jobs.Business_Group_Id )
OR Pa_Cross_Business_Grp.IsCrossBGProfile = 'Y');
Select
'Y'
From
Bom_Resources
Where
Resource_Id = P_BOM_Res_Id;
Select
'Y'
From
Mtl_Categories_tl
Where
Language = USERENV('LANG')
And Category_Id = P_Id;
Select
'Y'
From
Mtl_System_Items_tl
Where
Language = USERENV('LANG')
And Inventory_Item_Id = P_Id;
Select
'Y'
From
Pa_Resource_Classes_Vl
Where
Resource_Class_Id = P_Id;
Select
'Y'
From
Pa_Project_Role_Types_B
Where
Project_Role_Id = P_Id;
Select
'Y'
From
Hr_All_Organization_Units
Where
Organization_Id = P_Id;
Select
'Y'
From
Po_Vendors
Where
Vendor_Id = P_Id;
Select
'Y'
From
Per_Person_Types
Where
Person_Type_Id = P_Id
And Business_Group_Id = 0;*/
Select
'Y'
From
Pa_Lookups
Where
Lookup_Code = P_Code
And Lookup_Type = 'PA_PERSON_TYPE';
Select
'Y'
From
Pa_Non_Labor_Resources
Where
Non_Labor_Resource_Id = P_Id;
Select
'Y'
From
Pa_Expenditure_Categories
Where
Expenditure_Category_Id = P_Id;
Select
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,
User_Defined_Custom1_Id,
User_Defined_Custom2_Id,
User_Defined_Custom3_id,
User_Defined_Custom4_Id,
User_Defined_Custom5_Id,
Resource_Class_Id,
Supplier_Id,
Rbs_Level,
Outline_number
Into
X_Person_Id,
X_Job_Id,
X_Organization_Id,
X_Exp_Type_Id,
X_Event_Type_Id,
X_Exp_Cat_Id,
X_Rev_Cat_Id,
X_Inv_Item_Id,
X_Item_Cat_Id,
X_BOM_Labor_Id,
X_BOM_Equip_Id,
X_Non_Labor_Res_Id,
X_Role_Id,
X_Person_Type_Id,
X_User_Def_Custom1_Id,
X_User_Def_Custom2_Id,
X_User_Def_Custom3_Id,
X_User_Def_Custom4_Id,
X_User_Def_Custom5_Id,
X_Res_Class_Id,
X_Supplier_Id,
X_Rbs_Level,
X_Outline_Number
From
Pa_Rbs_Elements
Where
Rbs_Element_Id = P_Parent_Element_Id;
Procedure UpdateOrderOutlineNumber(
P_Parent_Element_Id_Tbl IN System.Pa_Num_Tbl_Type,
X_Error_Msg_Data OUT NOCOPY Varchar2 )
Is
i number := null;
Select
Max(Order_Number)
From
Pa_Rbs_Elements
Where
Parent_Element_Id = P_Par_Element_Id;
Select
Outline_Number
From
Pa_Rbs_Elements
Where
Rbs_Element_Id = P_Par_Element_Id;
-- Gets all the children of the parent for update.
Cursor c3(P_Par_Element_Id IN Number) Is
Select
Rbs_Element_Id,
Order_Number
From
Pa_Rbs_Elements
Where
Parent_Element_Id = P_Par_Element_Id
For Update of Outline_Number NoWait;
Pa_Debug.G_Stage := 'Entering UpdateOrderOutlineNumber().';
Pa_Debug.TrackPath('ADD','UpdateOrderOutlineNumber');
-- Get all the children for the parent for update
Pa_Debug.G_Stage := 'Open cursor to get all child elements for the current ' ||
'parent element id.';
Pa_Debug.G_Stage := 'Update the child rbs element record with new ' ||
'order number and outline number - update 1.';
Update pa_rbs_elements
Set
Order_Number = l_Order_Number,
Outline_Number = decode(l_Par_Outline_Number,'0',
to_char(l_Order_Number),
l_Par_Outline_Number || '.' || to_char(l_Order_Number))
Where
Rbs_Element_Id = l_Rbs_Rec.Rbs_Element_id;
Pa_Debug.G_Stage := 'Update the child rbs element record with the ' ||
'outline number - update 2 .';
Update Pa_Rbs_Elements
Set
Outline_Number = decode(l_Par_Outline_Number,'0',
to_char(l_Rbs_Rec.Order_Number),
l_Par_Outline_Number || '.' || to_char(l_Rbs_Rec.Order_Number))
Where
Rbs_Element_Id = l_Rbs_Rec.Rbs_Element_id;
Pa_Debug.G_Stage := 'Leaving UpdateOrderOutlineNumber() procedure.';
Pa_Debug.TrackPath('STRIP','UpdateOrderOutlineNumber');
End UpdateOrderOutlineNumber;
Procedure Update_Children_Data(
P_Rbs_Element_Id IN Number,
X_Error_Msg_Data OUT NOCOPY Varchar2)
Is
Cursor c1 (P_Rbs_Elem_Id IN Number) IS
Select
Rbs_Element_Id,
Parent_Element_Id,
Resource_Type_Id,
Resource_Source_Id
From
Pa_Rbs_Elements
Where
User_Created_Flag = 'Y'
Start With
Parent_Element_Id = P_Rbs_Elem_Id
Connect By Prior
Rbs_Element_Id = Parent_Element_Id
Order by
Rbs_Level;
Pa_Debug.G_Stage := 'Entering Update_Children_Data().';
Pa_Debug.TrackPath('ADD','Update_Children_Data');
Pa_Debug.G_Stage := 'Open c1 cursor to get rbs elements to update.';
Pa_Debug.G_Stage := 'Update the child element record.';
Update Pa_Rbs_Elements
Set
Person_Id = l_Person_Id,
Job_Id = l_Job_Id,
Organization_Id = l_Organization_Id,
Expenditure_Type_Id = l_Exp_Type_Id,
Event_Type_Id = l_Event_Type_Id,
Expenditure_Category_Id = l_Exp_Cat_Id,
Revenue_Category_Id = l_Rev_Cat_Id,
Inventory_Item_Id = l_Inv_Item_Id,
Item_Category_Id = l_Item_Cat_Id,
BOM_Labor_Id = l_BOM_Labor_Id,
BOM_Equipment_Id = l_BOM_Equip_Id,
Non_Labor_Resource_Id = l_Non_Labor_Res_Id,
Role_Id = l_Role_Id,
Person_Type_Id = l_Person_Type_Id,
User_Defined_Custom1_Id = l_User_Def_Custom1_Id,
User_Defined_Custom2_Id = l_User_Def_Custom2_Id,
User_Defined_Custom3_Id = l_User_Def_Custom3_Id,
User_Defined_Custom4_Id = l_User_Def_Custom4_Id,
User_Defined_Custom5_Id = l_User_Def_Custom5_Id,
Resource_Class_Id = l_Res_Class_Id,
Supplier_Id = l_Supplier_Id
Where Rbs_Element_Id = l_Child_Rec.Rbs_Element_Id;
Pa_Debug.G_Stage := 'Leaving Update_Children_Data() procedure.';
Pa_Debug.TrackPath('STRIP','Update_Children_Data');
End Update_Children_Data;
UPDATE PA_RBS_ELEMENTS SET ENABLE_FLAG='N'
WHERE RBS_VERSION_ID=P_RBS_VERSION_ID
AND RBS_ELEMENT_ID = P_Element_Id_Tbl(i);
UPDATE PA_RBS_ELEMENTS SET ENABLE_FLAG='N'
WHERE RBS_VERSION_ID=P_RBS_VERSION_ID
AND PARENT_ELEMENT_ID = P_Element_Id_Tbl(i);