[Home] [Help]
1: Package Body Pa_Rbs_Elements_Pvt As
2: /* $Header: PARELEVB.pls 120.1.12020000.3 2013/03/27 13:14:41 bpottipa ship $*/
3:
4: Procedure Process_RBS_Element (
5: P_RBS_Version_Id IN Number,
47: X_Error_Msg_Data := 'PA_RBS_ELE_USED_IN_ALLOC';
48: Return;
49: END IF;
50:
51: Pa_Rbs_Elements_Pvt.DeleteRbsElement(
52: P_RBS_Version_Id => P_RBS_Version_Id,
53: P_Element_Id => P_Element_Id,
54: X_Error_Msg_Data => X_Error_Msg_Data);
55:
58: ElsIf P_Process_Type = 'U' Then
59:
60: Pa_Debug.G_Stage := 'Call UpdateExistingRbsElement() procedure.';
61:
62: Pa_Rbs_Elements_Pvt.UpdateExisingRbsElement(
63: P_Rbs_Version_Id => P_Rbs_Version_Id,
64: P_Parent_Element_Id => P_Parent_Element_Id,
65: P_Rbs_Element_Id => P_Element_Id,
66: P_Resource_Type_Id => P_Resource_Type_Id,
76:
77: Else
78:
79: Pa_Debug.G_Stage := 'Call CreateNewRbsElement() procedure.';
80: Pa_Rbs_Elements_Pvt.CreateNewRbsElement(
81: P_Rbs_Version_Id => P_Rbs_Version_Id,
82: P_Parent_Element_Id => P_Parent_Element_Id,
83: P_Rbs_Element_Id => P_Element_Id,
84: P_Resource_Type_Id => P_Resource_Type_Id,
110: IS
111:
112: CURSOR c1 (P_Rbs_Elem_Id IN Number) IS
113: SELECT rbs_element_id
114: FROM pa_rbs_elements
115: START WITH rbs_element_Id = p_rbs_elem_id
116: CONNECT BY prior rbs_element_id = parent_element_Id;
117:
118: CURSOR get_later_sibs(p_rbs_version_id IN NUMBER,
118: CURSOR get_later_sibs(p_rbs_version_id IN NUMBER,
119: p_del_outline_number IN VARCHAR2,
120: p_parent_id IN NUMBER) IS
121: SELECT rbs_element_id, outline_number
122: FROM pa_rbs_elements
123: WHERE rbs_version_id = p_rbs_version_id
124: AND parent_element_Id = p_parent_id
125: AND to_number(replace(outline_number, '.')) >
126: to_number(replace(p_del_outline_number, '.'))
127: ORDER BY to_number(replace(outline_number, '.'));
128:
129: CURSOR get_sib_children (P_Rbs_Elem_Id IN Number) IS
130: SELECT rbs_element_id
131: FROM pa_rbs_elements
132: START WITH rbs_element_Id = p_rbs_elem_id
133: CONNECT BY prior rbs_element_id = parent_element_Id;
134:
135: l_Id Number := Null;
149: -- Get the parent ID and parent outline number for the element
150: -- being deleted.
151: SELECT parent_element_Id, outline_number
152: INTO l_parent_id, l_del_outline_number
153: FROM pa_rbs_elements
154: WHERE rbs_element_id = P_Element_Id;
155:
156: SELECT outline_number
157: INTO l_parent_outline_number
154: WHERE rbs_element_id = P_Element_Id;
155:
156: SELECT outline_number
157: INTO l_parent_outline_number
158: FROM pa_rbs_elements
159: WHERE rbs_element_id = l_parent_id;
160:
161: Pa_Debug.G_Stage := 'Entering DeleteRbsElement().';
162: Pa_Debug.TrackPath('ADD','DeleteRbsElement');
161: Pa_Debug.G_Stage := 'Entering DeleteRbsElement().';
162: Pa_Debug.TrackPath('ADD','DeleteRbsElement');
163:
164: Pa_Debug.G_Stage := 'Call DeleteRbsElement() procedure.';
165: IF Pa_Rbs_Elements_Utils.RbsElementExists( P_Element_Id => P_Element_Id ) = 'Y' Then
166:
167: Pa_Debug.G_Stage := 'Delete the children elements/nodes using cursor and loop.';
168: Open c1(P_Element_Id);
169: Loop
171: Fetch c1 Into l_id;
172: Exit When c1%NotFound;
173:
174: Pa_Debug.G_Stage := 'Delete child element/node by calling table handler.';
175: Pa_Rbs_Elements_Pkg.Delete_Row(P_Rbs_Element_Id => l_id);
176:
177:
178: End Loop;
179:
209: l_new_outline := to_char(l_new_last);
210: END IF;
211:
212: -- update sibling outline number
213: UPDATE pa_rbs_elements
214: SET outline_number = l_new_outline
215: WHERE rbs_element_id = l_upd_rbs_id;
216:
217: -- Update all the children's outline numbers by replacing the prefix
227: -- of the old 3 to 2. But a straight replace won't work - it will replace
228: -- 3.3.1 to 2.2.1 instead of 2.3.1. Hence just replace the first
229: -- part which is the length of the outline number plus 1 for the '.'
230: -- and then append the rest - so you get 2. || 3.1
231: UPDATE pa_rbs_elements
232: SET outline_number = replace(substr(outline_number, 1,
233: length(l_upd_outline_number) + 1),
234: l_upd_outline_number || '.',
235: l_new_outline || '.') ||
297:
298: --For bug 4047578:perf fix
299: Cursor Read_Element_Id_c IS
300: Select Rbs_Element_Id
301: From Pa_Rbs_Elements
302: Where Rule_Flag = 'Y'
303: Start With Parent_Element_Id = P_Rbs_Element_Id
304: Connect By Prior Rbs_Element_Id = Parent_Element_Id;
305: BEGIN
311: -- Check if the parent element is valid.
312: Pa_Debug.G_Stage := 'Check to see if parent element/node exists.';
313:
314:
315: If Pa_Rbs_Elements_Utils.RbsElementExists( P_Element_Id => P_Parent_Element_Id ) = 'Y' Then
316:
317:
318: Pa_Debug.G_Stage := 'Call ValidateAndBuildElement() procedure.';
319:
316:
317:
318: Pa_Debug.G_Stage := 'Call ValidateAndBuildElement() procedure.';
319:
320: Pa_Rbs_Elements_Pvt.ValidateAndBuildElement(
321: P_Mode => l_Mode,
322: P_Rbs_Version_Id => P_Rbs_Version_Id,
323: P_Parent_Element_Id => P_Parent_Element_Id,
324: P_Rbs_Element_Id => P_Rbs_Element_Id,
357:
358: If X_Error_Msg_Data is Null Then
359:
360: -- call the table handler to update the record.
361: Pa_Debug.G_Stage := 'Call Pa_Rbs_Elements_Pkg.Update_Row() procedure.';
362:
363: Pa_Rbs_Elements_Pkg.Update_Row(
364: P_Rbs_Element_Id => P_Rbs_Element_Id,
365: P_Rbs_Element_Name_Id => l_Rbs_Element_Name_Id,
359:
360: -- call the table handler to update the record.
361: Pa_Debug.G_Stage := 'Call Pa_Rbs_Elements_Pkg.Update_Row() procedure.';
362:
363: Pa_Rbs_Elements_Pkg.Update_Row(
364: P_Rbs_Element_Id => P_Rbs_Element_Id,
365: P_Rbs_Element_Name_Id => l_Rbs_Element_Name_Id,
366: P_Rbs_Version_Id => P_Rbs_Version_Id,
367: P_Outline_Number => l_outline_number,
396: P_User_Defined_Custom5_Id => l_User_Def_Custom5_Id,
397: P_LEVEL_CODE =>P_LEVEL_CODE,--15834912
398: P_COST_CODE =>P_COST_CODE,--15834912
399: P_COST_CODE_NAME =>P_COST_CODE_NAME,--16430696
400: P_Last_Update_Date => Pa_Rbs_Elements_Pvt.G_Last_Update_Date,
401: P_Last_Updated_By => Pa_Rbs_Elements_Pvt.G_Last_Updated_By,
402: P_Last_Update_Login => Pa_Rbs_Elements_Pvt.G_Last_Update_Login,
403: X_Error_Msg_Data => X_Error_Msg_Data);
404:
397: P_LEVEL_CODE =>P_LEVEL_CODE,--15834912
398: P_COST_CODE =>P_COST_CODE,--15834912
399: P_COST_CODE_NAME =>P_COST_CODE_NAME,--16430696
400: P_Last_Update_Date => Pa_Rbs_Elements_Pvt.G_Last_Update_Date,
401: P_Last_Updated_By => Pa_Rbs_Elements_Pvt.G_Last_Updated_By,
402: P_Last_Update_Login => Pa_Rbs_Elements_Pvt.G_Last_Update_Login,
403: X_Error_Msg_Data => X_Error_Msg_Data);
404:
405:
398: P_COST_CODE =>P_COST_CODE,--15834912
399: P_COST_CODE_NAME =>P_COST_CODE_NAME,--16430696
400: P_Last_Update_Date => Pa_Rbs_Elements_Pvt.G_Last_Update_Date,
401: P_Last_Updated_By => Pa_Rbs_Elements_Pvt.G_Last_Updated_By,
402: P_Last_Update_Login => Pa_Rbs_Elements_Pvt.G_Last_Update_Login,
403: X_Error_Msg_Data => X_Error_Msg_Data);
404:
405:
406: If X_Error_Msg_Data Is Null Then
419: LOOP
420: FETCH Read_Element_Id_c INTO l_Element_Id;
421: EXIT WHEN Read_Element_Id_c%NOTFOUND;
422:
423: Update Pa_Rbs_Elements
424: Set Rule_Flag = 'N'
425: Where Rbs_Element_Id =l_Element_Id;
426:
427: END LOOP;
427: END LOOP;
428: CLOSE Read_Element_Id_c;
429:
430: ElsIf l_Rule_Flag = 'Y' Then
431: Update Pa_Rbs_Elements
432: Set Rule_Flag = 'Y'
433: Where Rbs_Element_Id In ( Select Rbs_Element_Id
434: From Pa_Rbs_Elements
435: Start With Rbs_Element_Id = P_Rbs_Element_Id
430: ElsIf l_Rule_Flag = 'Y' Then
431: Update Pa_Rbs_Elements
432: Set Rule_Flag = 'Y'
433: Where Rbs_Element_Id In ( Select Rbs_Element_Id
434: From Pa_Rbs_Elements
435: Start With Rbs_Element_Id = P_Rbs_Element_Id
436: Connect By Prior Rbs_Element_Id = Parent_Element_Id
437: And Resource_Source_Id = -1 );
438: End If; --End of Bug fix 3736374
439:
440: -- Bug 3636175
441: -- This call is to update the elements below the one just updated with the changes made
442: -- so that the mapping for the rbs does not become broken.
443: PA_Debug.G_Stage := 'Call Pa_Rbs_Elements_Pvt.Update_Children_Data() procedure.';
444: Pa_Rbs_Elements_Pvt.Update_Children_Data(
445: P_Rbs_Element_Id => P_Rbs_Element_Id,
446: X_Error_Msg_Data => X_Error_Msg_Data);
447:
440: -- Bug 3636175
441: -- This call is to update the elements below the one just updated with the changes made
442: -- so that the mapping for the rbs does not become broken.
443: PA_Debug.G_Stage := 'Call Pa_Rbs_Elements_Pvt.Update_Children_Data() procedure.';
444: Pa_Rbs_Elements_Pvt.Update_Children_Data(
445: P_Rbs_Element_Id => P_Rbs_Element_Id,
446: X_Error_Msg_Data => X_Error_Msg_Data);
447:
448: End If;
454:
455: Pa_Debug.G_Stage := 'Parent element/node does not exists.';
456: Raise No_Data_Found;
457:
458: End If; -- Pa_Rbs_Elements_Utils.RbsElementExists()
459:
460: Pa_Debug.G_Stage := 'Leaving UpdateExisingRbsElement() procedure.';
461: Pa_Debug.TrackPath('STRIP','UpdateExisingRbsElement');
462:
518: Pa_Debug.TrackPath('ADD','CreateNewRbsElement');
519:
520: -- Check if the parent element is valid.
521: Pa_Debug.G_Stage := 'Check to see if parent element/node exists.';
522: If Pa_Rbs_Elements_Utils.RbsElementExists( P_Element_Id => P_Parent_Element_Id ) = 'Y' Then
523:
524: Pa_Debug.G_Stage := 'Call ValidateAndBuildElement() procedure.';
525: Pa_Rbs_Elements_Pvt.ValidateAndBuildElement(
526: P_Mode => l_Mode,
521: Pa_Debug.G_Stage := 'Check to see if parent element/node exists.';
522: If Pa_Rbs_Elements_Utils.RbsElementExists( P_Element_Id => P_Parent_Element_Id ) = 'Y' Then
523:
524: Pa_Debug.G_Stage := 'Call ValidateAndBuildElement() procedure.';
525: Pa_Rbs_Elements_Pvt.ValidateAndBuildElement(
526: P_Mode => l_Mode,
527: P_Rbs_Version_Id => P_Rbs_Version_Id,
528: P_Parent_Element_Id => P_Parent_Element_Id,
529: P_Rbs_Element_Id => P_Rbs_Element_Id,
561:
562: If X_Error_Msg_Data is Null Then
563:
564: -- call the table handler to update the record.
565: Pa_Debug.G_Stage := 'Call Pa_Rbs_Elements_Pkg.Insert_Row() procedure.';
566: Pa_Rbs_Elements_Pkg.Insert_Row(
567: P_Rbs_Element_Name_Id => l_Rbs_Element_Name_Id,
568: P_Rbs_Version_Id => P_Rbs_Version_Id,
569: P_Outline_Number => l_outline_number,
562: If X_Error_Msg_Data is Null Then
563:
564: -- call the table handler to update the record.
565: Pa_Debug.G_Stage := 'Call Pa_Rbs_Elements_Pkg.Insert_Row() procedure.';
566: Pa_Rbs_Elements_Pkg.Insert_Row(
567: P_Rbs_Element_Name_Id => l_Rbs_Element_Name_Id,
568: P_Rbs_Version_Id => P_Rbs_Version_Id,
569: P_Outline_Number => l_outline_number,
570: P_Order_Number => l_Order_Number,
595: P_User_Defined_Custom2_Id => l_User_Def_Custom2_Id,
596: P_User_Defined_Custom3_Id => l_User_Def_Custom3_Id,
597: P_User_Defined_Custom4_Id => l_User_Def_Custom4_Id,
598: P_User_Defined_Custom5_Id => l_User_Def_Custom5_Id,
599: P_Last_Update_Date => Pa_Rbs_Elements_Pvt.G_Last_Update_Date,
600: P_Last_Updated_By => Pa_Rbs_Elements_Pvt.G_Last_Updated_By,
601: P_Last_Update_Login => Pa_Rbs_Elements_Pvt.G_Last_Update_Login,
602: P_Creation_Date => Pa_Rbs_Elements_Pvt.G_Creation_Date,
603: P_Created_By => Pa_Rbs_Elements_Pvt.G_Created_By,
596: P_User_Defined_Custom3_Id => l_User_Def_Custom3_Id,
597: P_User_Defined_Custom4_Id => l_User_Def_Custom4_Id,
598: P_User_Defined_Custom5_Id => l_User_Def_Custom5_Id,
599: P_Last_Update_Date => Pa_Rbs_Elements_Pvt.G_Last_Update_Date,
600: P_Last_Updated_By => Pa_Rbs_Elements_Pvt.G_Last_Updated_By,
601: P_Last_Update_Login => Pa_Rbs_Elements_Pvt.G_Last_Update_Login,
602: P_Creation_Date => Pa_Rbs_Elements_Pvt.G_Creation_Date,
603: P_Created_By => Pa_Rbs_Elements_Pvt.G_Created_By,
604: P_LEVEL_CODE =>P_LEVEL_CODE,--15834912
597: P_User_Defined_Custom4_Id => l_User_Def_Custom4_Id,
598: P_User_Defined_Custom5_Id => l_User_Def_Custom5_Id,
599: P_Last_Update_Date => Pa_Rbs_Elements_Pvt.G_Last_Update_Date,
600: P_Last_Updated_By => Pa_Rbs_Elements_Pvt.G_Last_Updated_By,
601: P_Last_Update_Login => Pa_Rbs_Elements_Pvt.G_Last_Update_Login,
602: P_Creation_Date => Pa_Rbs_Elements_Pvt.G_Creation_Date,
603: P_Created_By => Pa_Rbs_Elements_Pvt.G_Created_By,
604: P_LEVEL_CODE =>P_LEVEL_CODE,--15834912
605: P_COST_CODE =>P_COST_CODE,--15834912
598: P_User_Defined_Custom5_Id => l_User_Def_Custom5_Id,
599: P_Last_Update_Date => Pa_Rbs_Elements_Pvt.G_Last_Update_Date,
600: P_Last_Updated_By => Pa_Rbs_Elements_Pvt.G_Last_Updated_By,
601: P_Last_Update_Login => Pa_Rbs_Elements_Pvt.G_Last_Update_Login,
602: P_Creation_Date => Pa_Rbs_Elements_Pvt.G_Creation_Date,
603: P_Created_By => Pa_Rbs_Elements_Pvt.G_Created_By,
604: P_LEVEL_CODE =>P_LEVEL_CODE,--15834912
605: P_COST_CODE =>P_COST_CODE,--15834912
606: P_COST_CODE_NAME =>P_COST_CODE_NAME,--16430696
599: P_Last_Update_Date => Pa_Rbs_Elements_Pvt.G_Last_Update_Date,
600: P_Last_Updated_By => Pa_Rbs_Elements_Pvt.G_Last_Updated_By,
601: P_Last_Update_Login => Pa_Rbs_Elements_Pvt.G_Last_Update_Login,
602: P_Creation_Date => Pa_Rbs_Elements_Pvt.G_Creation_Date,
603: P_Created_By => Pa_Rbs_Elements_Pvt.G_Created_By,
604: P_LEVEL_CODE =>P_LEVEL_CODE,--15834912
605: P_COST_CODE =>P_COST_CODE,--15834912
606: P_COST_CODE_NAME =>P_COST_CODE_NAME,--16430696
607: X_Rbs_Element_Id => X_Rbs_Element_Id,
613:
614: Pa_Debug.G_Stage := 'Parent element/node does not exist.';
615: Raise No_Data_Found;
616:
617: End If; -- Pa_Rbs_Elements_Utils.RbsElementExists()
618:
619: Pa_Debug.G_Stage := 'Leaving CreateNewRbsElement() procedure.';
620: Pa_Debug.TrackPath('STRIP','CreateNewRbsElement');
621:
669: p_rbs_element_id IN NUMBER,
670: P_rbs_level IN NUMBER)
671: IS
672: SELECT 'Y', rbs_element_id
673: FROM pa_rbs_elements
674: WHERE resource_type_id = p_resource_type_id
675: AND resource_source_id = p_resource_source_id
676: AND Rbs_Version_Id = P_Rbs_Version_Id
677: AND rbs_element_id <> nvl(p_rbs_element_id, -99)
681: p_resource_source_id IN NUMBER,
682: p_rbs_element_id IN NUMBER)
683: IS
684: SELECT 'Y'
685: FROM pa_rbs_elements
686: WHERE resource_type_id = p_resource_type_id
687: AND resource_source_id = p_resource_source_id
688: AND Rbs_Version_Id = P_Rbs_Version_Id
689: AND rbs_element_id <> nvl(p_rbs_element_id, -99);
720: SELECT resource_type_id,
721: resource_source_id
722: INTO l_old_resource_type_id,
723: l_old_resource_source_id
724: FROM PA_RBS_ELEMENTS
725: WHERE rbs_element_id = p_rbs_element_id;
726: END IF;
727:
728: Pa_Debug.G_Stage := 'Call ValidateRbsElement() procedure.';
726: END IF;
727:
728: Pa_Debug.G_Stage := 'Call ValidateRbsElement() procedure.';
729: --dbms_output.put_line('before ValidateRbsElement');
730: Pa_Rbs_Elements_Pvt.ValidateRbsElement(
731: P_Mode => P_Mode,
732: P_Rbs_Version_Id => P_Rbs_Version_Id,
733: P_Parent_Element_Id => P_Parent_Element_Id,
734: P_Rbs_Element_Id => P_Rbs_Element_Id,
743: If X_Error_Msg_Data is Null Then
744:
745: Pa_Debug.G_Stage := 'Call GetParentRbsData() procedure.';
746: --dbms_output.put_line('before GetParentRbsData');
747: Pa_Rbs_Elements_Pvt.GetParentRbsData(
748: P_Parent_Element_Id => P_Parent_Element_Id,
749: X_Person_Id => X_Person_Id,
750: X_Job_Id => X_Job_Id,
751: X_Organization_Id => X_Organization_Id,
900: Decode(r.rule_flag,'Y',Decode(P_Resource_Source_Id,-1,'Y','N'),'N'))
901: Into
902: X_Rule_Based_Flag
903: From
904: pa_rbs_elements r
905: Where
906: r.rbs_element_id = P_Parent_Element_Id; --End of bug fix 3736374.
907:
908: --dbms_output.put_line('after get rule based flag');
953: -- hr_utility.trace('x_job_id IS : ' || x_job_id);
954: -- hr_utility.trace('x_person_id IS : ' || x_person_id);
955: SELECT 'N'
956: INTO l_unique_branch
957: FROM pa_rbs_elements
958: WHERE rbs_element_id = l_exists_element_id
959: AND nvl(person_id, -99) = nvl(x_person_id, -99)
960: AND nvl(organization_id, -99) = nvl(x_organization_id, -99)
961: AND nvl(job_id, -99) = nvl(x_job_id, -99)
1030: --dbms_output.put_line('P_Rbs_Element_Id IS ' || P_Rbs_Element_Id);
1031: --dbms_output.put_line('P_Rbs_Version_Id IS ' || P_Rbs_Version_Id);
1032: SELECT 'N'
1033: INTO l_unique_branch
1034: FROM pa_rbs_elements
1035: WHERE rbs_element_id <> nvl(P_Rbs_Element_Id,-99)
1036: AND rbs_version_id = P_Rbs_Version_Id
1037: AND rbs_level = X_Rbs_level
1038: AND nvl(person_id, -99) = nvl(x_person_id, -99)
1135: From Dual;
1136:
1137: ELSE
1138:
1139: Pa_Debug.G_Stage := 'Retrieve the element identifier value from pa_rbs_elements for update.';
1140: -- Get the value from the rbs_elements table.
1141: Select Element_Identifier
1142: Into X_Element_Identifier
1143: From Pa_Rbs_Elements
1139: Pa_Debug.G_Stage := 'Retrieve the element identifier value from pa_rbs_elements for update.';
1140: -- Get the value from the rbs_elements table.
1141: Select Element_Identifier
1142: Into X_Element_Identifier
1143: From Pa_Rbs_Elements
1144: Where Rbs_Element_Id = P_Rbs_Element_Id;
1145:
1146: END IF;
1147:
1153: -- Set the outline number
1154:
1155: SELECT count(*)
1156: INTO l_number_of_peers
1157: FROM PA_RBS_ELEMENTS
1158: WHERE parent_element_id = p_parent_element_id
1159: AND USER_CREATED_FLAG = 'Y';
1160:
1161: IF l_number_of_peers = 0 THEN
1173: -- Mode is update. Get the outline number of the element
1174:
1175: SELECT outline_number
1176: INTO x_outline_number
1177: FROM PA_RBS_ELEMENTS
1178: WHERE RBS_ELEMENT_ID = P_RBS_ELEMENT_ID;
1179:
1180: END IF;
1181:
1227: P_ResTypeId IN Number)
1228: IS
1229:
1230: SELECT Count(*)
1231: FROM PA_RBS_ELEMENTS
1232: WHERE Rbs_Version_Id = P_RbsVersionId
1233: AND Resource_Type_Id = P_ResTypeId
1234: AND Resource_Source_Id = P_ResSourceId
1235: AND parent_element_id = p_parentId;
1242: P_Parent_Elem_Id IN Number)
1243: IS
1244:
1245: Select Count(*)
1246: From Pa_Rbs_Elements
1247: Where Resource_Type_Id = P_Res_Type_Id
1248: And Resource_Source_Id = P_Res_Srce_Id
1249: Start With Rbs_Element_Id = P_Parent_Elem_Id
1250: Connect By Prior Parent_Element_Id = Rbs_Element_Id;
1254: P_Res_Srce_Id IN Number,
1255: P_Rbs_Elem_Id IN Number)
1256: IS
1257: Select Count(*)
1258: From Pa_Rbs_Elements
1259: Where Resource_Type_Id = P_Res_Type_Id
1260: And Resource_Source_Id = P_Res_Srce_Id
1261: Start With Parent_Element_Id = P_Rbs_Elem_Id
1262: Connect By Prior Rbs_Element_Id = Parent_Element_Id;
1277:
1278: CURSOR c_GetCurrentLevel(P_Parent_Id IN Number)
1279: IS
1280: SELECT rbs_level + 1
1281: FROM Pa_Rbs_Elements
1282: WHERE Rbs_Element_Id = P_Parent_Id;
1283:
1284: CURSOR c_CheckResTypeInRbs
1285: (P_Res_Type_Id IN Number,
1286: P_Rbs_Level IN Number,
1287: P_Version_Id IN Number)
1288: IS
1289: SELECT Count(*)
1290: FROM Pa_Rbs_Elements
1291: WHERE Resource_Type_Id = P_Res_Type_Id
1292: AND Rbs_Level not in (P_Rbs_Level, P_Rbs_Level - 1, P_Rbs_Level + 1)
1293: AND Rbs_Version_Id = P_Version_Id;
1294:
1294:
1295: CURSOR children_elements(P_Rbs_Element_Id IN NUMBER)
1296: IS
1297: SELECT rbs_element_id , resource_type_id, resource_source_id
1298: FROM pa_rbs_elements
1299: WHERE parent_element_id = P_Rbs_Element_Id;
1300:
1301: --Added for CBS phase 2 16282618
1302: CURSOR GET_RBS_HEADER_INFO IS
1317: -- We need this validation irrespective of whether the resource type is
1318: -- changed or not. The x_resource_type we get below is used by the
1319: -- calling API to populate the element record in both update and insert mode.
1320:
1321: X_Resource_Type := Pa_Rbs_Elements_Utils.GetResTypeCode
1322: (P_Res_Type_Id => P_Resource_Type_Id);
1323:
1324:
1325: IF X_Resource_Type is Null or X_Resource_Type = 'NAMED_ROLE'
1349:
1350: -- 3. If the element is not rule based, and resource_type is not user-defined,
1351: -- validate the resource_source_id.
1352: Pa_Debug.G_Stage := 'Validate the resource by calling the ValidateResource() procedure.';
1353: Pa_Rbs_Elements_Pvt.ValidateResource(
1354: P_Resource_Type_Id => P_Resource_Type_Id,
1355: P_Resource_Source_Id => P_Resource_Source_Id,
1356: P_Resource_Type => X_Resource_Type,
1357: X_Error_Msg_Data => X_Error_Msg_Data);
1447: -- Get parent's resource type
1448: BEGIN
1449: SELECT resource_type_id, resource_source_id
1450: INTO l_parent_resource_type_id, l_parent_resource_source_id
1451: FROM PA_RBS_ELEMENTS
1452: WHERE rbs_element_id = P_Parent_Element_Id;
1453: EXCEPTION
1454: WHEN OTHERS THEN
1455: null;
1471: l_dummy_count := 0;
1472:
1473: SELECT count(*)
1474: INTO l_dummy_count
1475: FROM PA_RBS_ELEMENTS
1476: WHERE RESOURCE_TYPE_ID = P_Resource_Type_Id
1477: START WITH rbs_element_id = p_parent_element_id
1478: CONNECT BY PRIOR parent_element_id = rbs_element_id;
1479:
1504: l_dummy_count := 0;
1505:
1506: SELECT count(*)
1507: INTO l_dummy_count
1508: FROM PA_RBS_ELEMENTS
1509: WHERE RESOURCE_TYPE_ID = P_Resource_Type_Id
1510: START WITH parent_element_id = c1.rbs_element_id
1511: CONNECT BY PRIOR rbs_element_id = parent_element_id;
1512:
1534: l_dummy_count := 0;
1535:
1536: select count(*)
1537: INTO l_dummy_count
1538: FROM PA_RBS_ELEMENTS
1539: WHERE PARENT_ELEMENT_ID = p_rbs_element_id
1540: AND RESOURCE_TYPE_ID = p_old_resource_type_id;
1541:
1542: IF l_dummy_count > 0 THEN
2087: X_Supplier_Id,
2088: X_Rbs_Level,
2089: X_Outline_Number
2090: From
2091: Pa_Rbs_Elements
2092: Where
2093: Rbs_Element_Id = P_Parent_Element_Id;
2094: -- And Rbs_Level <> 0;
2095:
2123: Cursor c1(P_Par_Element_Id IN Number) is
2124: Select
2125: Max(Order_Number)
2126: From
2127: Pa_Rbs_Elements
2128: Where
2129: Parent_Element_Id = P_Par_Element_Id;
2130:
2131: -- Gets the parent outline number
2132: Cursor c2(P_Par_Element_Id IN Number) Is
2133: Select
2134: Outline_Number
2135: From
2136: Pa_Rbs_Elements
2137: Where
2138: Rbs_Element_Id = P_Par_Element_Id;
2139:
2140: -- Gets all the children of the parent for update.
2142: Select
2143: Rbs_Element_Id,
2144: Order_Number
2145: From
2146: Pa_Rbs_Elements
2147: Where
2148: Parent_Element_Id = P_Par_Element_Id
2149: For Update of Outline_Number NoWait;
2150:
2198: l_order_number := l_order_number + 1;
2199:
2200: Pa_Debug.G_Stage := 'Update the child rbs element record with new ' ||
2201: 'order number and outline number - update 1.';
2202: Update pa_rbs_elements
2203: Set
2204: Order_Number = l_Order_Number,
2205: Outline_Number = decode(l_Par_Outline_Number,'0',
2206: to_char(l_Order_Number),
2211: Else
2212:
2213: Pa_Debug.G_Stage := 'Update the child rbs element record with the ' ||
2214: 'outline number - update 2 .';
2215: Update Pa_Rbs_Elements
2216: Set
2217: Outline_Number = decode(l_Par_Outline_Number,'0',
2218: to_char(l_Rbs_Rec.Order_Number),
2219: l_Par_Outline_Number || '.' || to_char(l_Rbs_Rec.Order_Number))
2249: Parent_Element_Id,
2250: Resource_Type_Id,
2251: Resource_Source_Id
2252: From
2253: Pa_Rbs_Elements
2254: Where
2255: User_Created_Flag = 'Y'
2256: Start With
2257: Parent_Element_Id = P_Rbs_Elem_Id
2303: Fetch c1 Into l_Child_Rec;
2304: Exit When c1%NotFound;
2305:
2306: Pa_Debug.G_Stage := 'Call GetParentRbsData() procedure.';
2307: Pa_Rbs_Elements_Pvt.GetParentRbsData(
2308: P_Parent_Element_Id => l_Child_Rec.Parent_Element_Id,
2309: X_Person_Id => l_Person_Id,
2310: X_Job_Id => l_Job_Id,
2311: X_Organization_Id => l_Organization_Id,
2329: X_Supplier_Id => l_Supplier_Id,
2330: X_Rbs_Level => l_Dummy_Rbs_Level,
2331: X_Outline_Number => l_Dummy_Outline_Number);
2332:
2333: Pa_Debug.G_Stage := 'Get the Resource Type by calling Pa_Rbs_Elements_Utils.GetResTypeCode() function.';
2334: l_Resource_Type := Pa_Rbs_Elements_Utils.GetResTypeCode(P_Res_Type_Id => l_Child_Rec.Resource_Type_Id);
2335:
2336: Pa_Debug.G_Stage := 'Determine which resource type we have for this element to properly assign the resource source id.';
2337: If l_Resource_Type = 'BOM_LABOR' Then
2330: X_Rbs_Level => l_Dummy_Rbs_Level,
2331: X_Outline_Number => l_Dummy_Outline_Number);
2332:
2333: Pa_Debug.G_Stage := 'Get the Resource Type by calling Pa_Rbs_Elements_Utils.GetResTypeCode() function.';
2334: l_Resource_Type := Pa_Rbs_Elements_Utils.GetResTypeCode(P_Res_Type_Id => l_Child_Rec.Resource_Type_Id);
2335:
2336: Pa_Debug.G_Stage := 'Determine which resource type we have for this element to properly assign the resource source id.';
2337: If l_Resource_Type = 'BOM_LABOR' Then
2338:
2451:
2452: End If;
2453:
2454: Pa_Debug.G_Stage := 'Update the child element record.';
2455: Update Pa_Rbs_Elements
2456: Set
2457: Person_Id = l_Person_Id,
2458: Job_Id = l_Job_Id,
2459: Organization_Id = l_Organization_Id,
2513:
2514: For i in P_Element_Id_Tbl.First .. P_Element_Id_Tbl.Last
2515: Loop
2516: --Make the cost attibute as disabled
2517: UPDATE PA_RBS_ELEMENTS SET ENABLE_FLAG='N'
2518: WHERE RBS_VERSION_ID=P_RBS_VERSION_ID
2519: AND RBS_ELEMENT_ID = P_Element_Id_Tbl(i);
2520: --make all its children as disabled
2521: UPDATE PA_RBS_ELEMENTS SET ENABLE_FLAG='N'
2517: UPDATE PA_RBS_ELEMENTS SET ENABLE_FLAG='N'
2518: WHERE RBS_VERSION_ID=P_RBS_VERSION_ID
2519: AND RBS_ELEMENT_ID = P_Element_Id_Tbl(i);
2520: --make all its children as disabled
2521: UPDATE PA_RBS_ELEMENTS SET ENABLE_FLAG='N'
2522: WHERE RBS_VERSION_ID=P_RBS_VERSION_ID
2523: AND PARENT_ELEMENT_ID = P_Element_Id_Tbl(i);
2524:
2525: END Loop;
2530: X_Error_Msg_Data := 'Unable to disable cost attributes';
2531:
2532: END disableCostAttributes;
2533:
2534: End Pa_Rbs_Elements_Pvt;