1 Package Body Pa_Rbs_Elements_Pvt As
2 /* $Header: PARELEVB.pls 120.1 2008/01/16 20:22:11 amehrotr ship $*/
3
4 Procedure Process_RBS_Element (
5 P_RBS_Version_Id IN Number,
6 P_Parent_Element_Id IN Number,
7 P_Element_Id IN Number,
8 P_Resource_Type_Id IN Number,
9 P_Resource_Source_Id IN Number,
10 P_Order_Number IN Number,
11 P_Process_Type IN Varchar2,
12 X_RBS_Element_id OUT NOCOPY Number,
13 X_Error_Msg_Data OUT NOCOPY Varchar2)
14
15 Is
16
17 l_rbs_header_id NUMBER;
18
19 Begin
20
21 Pa_Debug.G_Stage := 'Entering Process_Rbs_Elements() Pvt.';
22 Pa_Debug.TrackPath('ADD','Process_Rbs_Elements Pvt');
23
24 If P_Process_Type = 'D' Then
25
26 Pa_Debug.G_Stage := 'Call DeleteRbsElement() procedure.';
27
28 -- We can delete an element in an RBS from the working version
29 -- only if none of the previous RBS versions are used in any
30 -- allocations rules. If the version is used, we prevent the
31 -- delete.
32 -- IMP : We do not check at element level to check whether
33 -- it's used for allocations. We prevent any and every deletion,
34 -- if the any element of the previous RBS version is used.
35
36 SELECT rbs_header_id
37 INTO l_rbs_header_id
38 FROM pa_rbs_versions_b
39 WHERE rbs_version_id = p_rbs_version_id;
40
41 IF PA_ALLOC_UTILS.IS_RBS_IN_RULES
42 (p_rbs_id => l_rbs_header_id) = 'Y'
43 THEN
44 X_Error_Msg_Data := 'PA_RBS_ELE_USED_IN_ALLOC';
45 Return;
46 END IF;
47
48 Pa_Rbs_Elements_Pvt.DeleteRbsElement(
49 P_RBS_Version_Id => P_RBS_Version_Id,
50 P_Element_Id => P_Element_Id,
51 X_Error_Msg_Data => X_Error_Msg_Data);
52
53 X_RBS_Element_id := null;
54
55 ElsIf P_Process_Type = 'U' Then
56
57 Pa_Debug.G_Stage := 'Call UpdateExistingRbsElement() procedure.';
58 Pa_Rbs_Elements_Pvt.UpdateExisingRbsElement(
59 P_Rbs_Version_Id => P_Rbs_Version_Id,
60 P_Parent_Element_Id => P_Parent_Element_Id,
61 P_Rbs_Element_Id => P_Element_Id,
62 P_Resource_Type_Id => P_Resource_Type_Id,
63 P_Resource_Source_Id => P_Resource_Source_Id,
64 P_Order_Number => P_Order_Number,
65 X_Error_Msg_Data => X_Error_Msg_Data);
66
67
68 X_RBS_Element_id := P_Parent_Element_Id;
69
70 Else
71
72 Pa_Debug.G_Stage := 'Call CreateNewRbsElement() procedure.';
73 Pa_Rbs_Elements_Pvt.CreateNewRbsElement(
74 P_Rbs_Version_Id => P_Rbs_Version_Id,
75 P_Parent_Element_Id => P_Parent_Element_Id,
76 P_Rbs_Element_Id => P_Element_Id,
77 P_Resource_Type_Id => P_Resource_Type_Id,
78 P_Resource_Source_Id => P_Resource_Source_Id,
79 P_Order_Number => P_Order_Number,
80 X_RBS_Element_id => X_RBS_Element_id,
81 X_Error_Msg_Data => X_Error_Msg_Data);
82
83 End If;
84
85 Pa_Debug.G_Stage := 'Leaving Process_Rbs_Elements() Pvt procedure.';
86 Pa_Debug.TrackPath('STRIP','Process_Rbs_Elements Pvt');
87
88 Exception
89 When Others Then
90 Raise;
91
92 End Process_RBS_Element;
93
94 PROCEDURE DeleteRbsElement(
95 P_RBS_Version_Id IN Number,
96 P_Element_Id IN Number,
97 X_Error_Msg_Data OUT NOCOPY Varchar2)
98
99 IS
100
101 CURSOR c1 (P_Rbs_Elem_Id IN Number) IS
102 SELECT rbs_element_id
103 FROM pa_rbs_elements
104 START WITH rbs_element_Id = p_rbs_elem_id
105 CONNECT BY prior rbs_element_id = parent_element_Id;
106
107 CURSOR get_later_sibs(p_rbs_version_id IN NUMBER,
108 p_del_outline_number IN VARCHAR2,
109 p_parent_id IN NUMBER) IS
110 SELECT rbs_element_id, outline_number
111 FROM pa_rbs_elements
112 WHERE rbs_version_id = p_rbs_version_id
113 AND parent_element_Id = p_parent_id
114 AND to_number(replace(outline_number, '.')) >
115 to_number(replace(p_del_outline_number, '.'))
116 ORDER BY to_number(replace(outline_number, '.'));
117
118 CURSOR get_sib_children (P_Rbs_Elem_Id IN Number) IS
119 SELECT rbs_element_id
120 FROM pa_rbs_elements
121 START WITH rbs_element_Id = p_rbs_elem_id
122 CONNECT BY prior rbs_element_id = parent_element_Id;
123
124 l_Id Number := Null;
125 -- Bug 4146317 changes - make sure outline number is still in sync
126 l_parent_id NUMBER := NULL;
127 l_parent_outline_number VARCHAR2(240);
128 l_upd_rbs_id NUMBER := NULL;
129 l_upd_outline_number VARCHAR2(240);
130 l_del_outline_number VARCHAR2(240);
131
132 l_new_last NUMBER := NULL;
133 l_new_outline VARCHAR2(240);
134 l_child_id NUMBER := NULL;
135
136 BEGIN
137
138 -- Get the parent ID and parent outline number for the element
139 -- being deleted.
140 SELECT parent_element_Id, outline_number
141 INTO l_parent_id, l_del_outline_number
142 FROM pa_rbs_elements
143 WHERE rbs_element_id = P_Element_Id;
144
145 SELECT outline_number
146 INTO l_parent_outline_number
147 FROM pa_rbs_elements
148 WHERE rbs_element_id = l_parent_id;
149
150 Pa_Debug.G_Stage := 'Entering DeleteRbsElement().';
151 Pa_Debug.TrackPath('ADD','DeleteRbsElement');
152
153 Pa_Debug.G_Stage := 'Call DeleteRbsElement() procedure.';
154 IF Pa_Rbs_Elements_Utils.RbsElementExists( P_Element_Id => P_Element_Id ) = 'Y' Then
155
156 Pa_Debug.G_Stage := 'Delete the children elements/nodes using cursor and loop.';
157 Open c1(P_Element_Id);
158 Loop
159
160 Fetch c1 Into l_id;
161 Exit When c1%NotFound;
162
163 Pa_Debug.G_Stage := 'Delete child element/node by calling table handler.';
164 Pa_Rbs_Elements_Pkg.Delete_Row(P_Rbs_Element_Id => l_id);
165
166
167 End Loop;
168
169 Close c1;
170
171 Else -- The element does not exist
172
173 Pa_Debug.G_Stage := 'Could not find the element/node to delete.';
174 Raise No_Data_Found;
175
176 END IF;
177
178 -- After the element is deleted, update outline numbers which need
179 -- to be updated.
180 -- Select all the siblings which have outline number "greater"
181 -- than the element being deleted.
182
183 OPEN get_later_sibs(p_rbs_version_id => p_rbs_version_id,
184 p_del_outline_number => l_del_outline_number,
185 p_parent_id => l_parent_id);
186 LOOP
187
188 FETCH get_later_sibs INTO l_upd_rbs_id, l_upd_outline_number;
189 EXIT WHEN get_later_sibs%NOTFOUND;
190 -- decrement the last digit of the outline number by 1
191 IF l_parent_outline_number <> '0' THEN
192 l_new_last := to_number(replace(l_upd_outline_number,
193 l_parent_outline_number || '.')) - 1;
194 -- create the new outline number using the parent's
195 l_new_outline := l_parent_outline_number || '.' || to_char(l_new_last);
196 ELSE
197 l_new_last := to_number(l_upd_outline_number) - 1;
198 l_new_outline := to_char(l_new_last);
199 END IF;
200
201 -- update sibling outline number
202 UPDATE pa_rbs_elements
203 SET outline_number = l_new_outline
204 WHERE rbs_element_id = l_upd_rbs_id;
205
206 -- Update all the children's outline numbers by replacing the prefix
207 -- with the new prefix.
208 OPEN get_sib_children(P_Rbs_Elem_Id => l_upd_rbs_id);
209 LOOP
210
211 FETCH get_sib_children INTO l_child_id;
212 EXIT WHEN get_sib_children%NOTFOUND;
213
214 -- this update is complicated because say you delete outline number 2
215 -- then you need to update 3 to 2 and replace 3 in all the children
216 -- of the old 3 to 2. But a straight replace won't work - it will replace
217 -- 3.3.1 to 2.2.1 instead of 2.3.1. Hence just replace the first
218 -- part which is the length of the outline number plus 1 for the '.'
219 -- and then append the rest - so you get 2. || 3.1
220 UPDATE pa_rbs_elements
221 SET outline_number = replace(substr(outline_number, 1,
222 length(l_upd_outline_number) + 1),
223 l_upd_outline_number || '.',
224 l_new_outline || '.') ||
225 substr(outline_number, length(l_upd_outline_number) + 2)
226 WHERE rbs_element_Id = l_child_id;
227 END LOOP;
228 CLOSE get_sib_children;
229
230 END LOOP;
231
232 CLOSE get_later_sibs;
233
234 Pa_Debug.G_Stage := 'Leaving DeleteRbsElement() procedure.';
235 Pa_Debug.TrackPath('STRIP','DeleteRbsElement');
236
237 EXCEPTION
238 When Others Then
239 Raise;
240
241 END DeleteRbsElement;
242
243 PROCEDURE UpdateExisingRbsElement(
244 P_Rbs_Version_Id IN Number,
245 P_Parent_Element_Id IN Number,
246 P_Rbs_Element_Id IN Number,
247 P_Resource_Type_Id IN Number,
248 P_Resource_Source_Id IN Number,
249 P_Order_Number IN Number,
250 X_Error_Msg_Data OUT NOCOPY Varchar2)
251
252 IS
253
254 l_Person_Id Number := Null;
255 l_Job_Id Number := Null;
256 l_Organization_Id Number := Null;
257 l_Exp_Type_Id Number := Null;
258 l_Event_Type_Id Number := Null;
259 l_Exp_Cat_Id Number := Null;
260 l_Rev_Cat_Id Number := Null;
261 l_Inv_Item_Id Number := Null;
262 l_Item_Cat_Id Number := Null;
263 l_BOM_Labor_Id Number := Null;
264 l_BOM_Equip_Id Number := Null;
265 l_Non_Labor_Res_Id Number := Null;
266 l_Role_Id Number := Null;
267 l_Person_Type_Id Number := Null;
268 l_Res_Class_Id Number := Null;
269 l_Supplier_Id Number := Null;
270 l_Rbs_Level Number := Null;
271 l_Rule_Flag Varchar2(1) := Null;
272 l_Order_Number Number := Null;
273 l_Rbs_Element_Name_Id Number := Null;
274 l_Element_Identifier Number := Null;
275 l_Outline_Number Varchar2(240) := Null;
276 l_User_Def_Custom1_Id Number := Null;
277 l_User_Def_Custom2_Id Number := Null;
278 l_User_Def_Custom3_Id Number := Null;
279 l_User_Def_Custom4_Id Number := Null;
280 l_User_Def_Custom5_Id Number := Null;
281 l_Mode Varchar2(1) := 'U';
282 l_Element_Id Number;
283
284 --For bug 4047578:perf fix
285 Cursor Read_Element_Id_c IS
286 Select Rbs_Element_Id
287 From Pa_Rbs_Elements
288 Where Rule_Flag = 'Y'
289 Start With Parent_Element_Id = P_Rbs_Element_Id
290 Connect By Prior Rbs_Element_Id = Parent_Element_Id;
291 BEGIN
292
293 Pa_Debug.G_Stage := 'Entering UpdateExisingRbsElement().';
294 Pa_Debug.TrackPath('ADD','UpdateExisingRbsElement');
295
296 -- Check if the parent element is valid.
297 Pa_Debug.G_Stage := 'Check to see if parent element/node exists.';
298
299
300 If Pa_Rbs_Elements_Utils.RbsElementExists( P_Element_Id => P_Parent_Element_Id ) = 'Y' Then
301
302
303 Pa_Debug.G_Stage := 'Call ValidateAndBuildElement() procedure.';
304 Pa_Rbs_Elements_Pvt.ValidateAndBuildElement(
305 P_Mode => l_Mode,
306 P_Rbs_Version_Id => P_Rbs_Version_Id,
307 P_Parent_Element_Id => P_Parent_Element_Id,
308 P_Rbs_Element_Id => P_Rbs_Element_Id,
309 P_Resource_Type_Id => P_Resource_Type_Id,
310 P_Resource_Source_Id => P_Resource_Source_Id,
311 P_Order_Number => P_Order_Number,
312 X_Person_Id => l_Person_Id,
313 X_Job_Id => l_Job_Id,
314 X_Organization_Id => l_Organization_Id,
315 X_Exp_Type_Id => l_Exp_Type_Id,
316 X_Event_Type_Id => l_Event_Type_Id,
317 X_Exp_Cat_Id => l_Exp_Cat_Id,
318 X_Rev_Cat_Id => l_Rev_Cat_Id,
319 X_Inv_Item_Id => l_Inv_Item_Id,
320 X_Item_Cat_Id => l_Item_Cat_Id,
321 X_BOM_Labor_Id => l_BOM_Labor_Id,
322 X_BOM_Equip_Id => l_BOM_Equip_Id,
323 X_Non_Labor_Res_Id => l_Non_Labor_Res_Id,
324 X_Role_Id => l_Role_Id,
325 X_Person_Type_Id => l_Person_Type_Id,
326 X_User_Def_Custom1_Id => l_User_Def_Custom1_Id,
327 X_User_Def_Custom2_Id => l_User_Def_Custom2_Id,
328 X_User_Def_Custom3_Id => l_User_Def_Custom3_Id,
329 X_User_Def_Custom4_Id => l_User_Def_Custom4_Id,
330 X_User_Def_Custom5_Id => l_User_Def_Custom5_Id,
331 X_Res_Class_Id => l_Res_Class_Id,
332 X_Supplier_Id => l_Supplier_Id,
333 X_Rbs_Level => l_Rbs_Level,
334 X_Rule_Based_Flag => l_Rule_Flag,
335 X_Rbs_Element_Name_Id => l_Rbs_Element_Name_Id,
336 X_Order_Number => l_Order_Number,
337 X_Element_Identifier => l_Element_Identifier,
338 X_Outline_Number => l_outline_number,
339 X_Error_Msg_Data => X_Error_Msg_Data);
340
341
342 If X_Error_Msg_Data is Null Then
343
344 -- call the table handler to update the record.
345 Pa_Debug.G_Stage := 'Call Pa_Rbs_Elements_Pkg.Update_Row() procedure.';
346 Pa_Rbs_Elements_Pkg.Update_Row(
347 P_Rbs_Element_Id => P_Rbs_Element_Id,
348 P_Rbs_Element_Name_Id => l_Rbs_Element_Name_Id,
349 P_Rbs_Version_Id => P_Rbs_Version_Id,
350 P_Outline_Number => l_outline_number,
351 P_Order_Number => l_Order_Number,
352 P_Resource_Type_Id => P_Resource_Type_Id,
353 P_Resource_Source_Id => P_Resource_Source_Id,
354 P_Person_Id => l_Person_Id,
355 P_Job_Id => l_Job_Id,
356 P_Organization_Id => l_Organization_Id,
357 P_Expenditure_Type_Id => l_Exp_Type_Id,
358 P_Event_Type_Id => l_Event_Type_Id,
359 P_Expenditure_Category_Id => l_Exp_Cat_Id,
360 P_Revenue_Category_Id => l_Rev_Cat_Id,
361 P_Inventory_Item_Id => l_Inv_Item_Id,
362 P_Item_Category_Id => l_Item_Cat_Id,
363 P_BOM_Labor_Id => l_BOM_Labor_Id,
364 P_BOM_Equipment_Id => l_BOM_Equip_Id,
365 P_Non_Labor_Resource_Id => l_Non_Labor_Res_Id,
366 P_Role_Id => l_Role_Id,
367 P_Person_Type_ID => l_Person_Type_Id,
368 P_Resource_Class_Id => l_Res_Class_Id,
369 P_Supplier_Id => l_Supplier_Id,
370 P_Rule_Flag => l_Rule_Flag,
371 P_Parent_Element_Id => P_Parent_Element_Id,
372 P_Rbs_Level => l_Rbs_Level,
373 P_Element_Identifier => l_Element_Identifier,
374 P_User_Created_Flag => 'Y',
375 P_User_Defined_Custom1_Id => l_User_Def_Custom1_Id,
376 P_User_Defined_Custom2_Id => l_User_Def_Custom2_Id,
377 P_User_Defined_Custom3_Id => l_User_Def_Custom3_Id,
378 P_User_Defined_Custom4_Id => l_User_Def_Custom4_Id,
379 P_User_Defined_Custom5_Id => l_User_Def_Custom5_Id,
380 P_Last_Update_Date => Pa_Rbs_Elements_Pvt.G_Last_Update_Date,
381 P_Last_Updated_By => Pa_Rbs_Elements_Pvt.G_Last_Updated_By,
382 P_Last_Update_Login => Pa_Rbs_Elements_Pvt.G_Last_Update_Login,
383 X_Error_Msg_Data => X_Error_Msg_Data);
384
385
386 If X_Error_Msg_Data Is Null Then
387
388 --Added for Bug fix 3736374
389 --We need to update the value of rule flag for all Child nodes below the current node.
390 --If rule is changed to instance then all child nodes below it should have
391 --rule flag = 'N'.
392 --If instance is changed to rule and it is not below any other instance in the
393 --hierarchy then all rule nodes below it (that are not under any other instance node)
394 --should have rule flag = 'Y'.
395 If P_Resource_Source_Id <> -1 Then
396
397 --For bug 4047578:perf fix
398 OPEN Read_Element_Id_c;
399 LOOP
400 FETCH Read_Element_Id_c INTO l_Element_Id;
401 EXIT WHEN Read_Element_Id_c%NOTFOUND;
402
403 Update Pa_Rbs_Elements
404 Set Rule_Flag = 'N'
405 Where Rbs_Element_Id =l_Element_Id;
406
407 END LOOP;
408 CLOSE Read_Element_Id_c;
409
410 ElsIf l_Rule_Flag = 'Y' Then
411 Update Pa_Rbs_Elements
412 Set Rule_Flag = 'Y'
413 Where Rbs_Element_Id In ( Select Rbs_Element_Id
414 From Pa_Rbs_Elements
415 Start With Rbs_Element_Id = P_Rbs_Element_Id
416 Connect By Prior Rbs_Element_Id = Parent_Element_Id
417 And Resource_Source_Id = -1 );
418 End If; --End of Bug fix 3736374
419
420 -- Bug 3636175
421 -- This call is to update the elements below the one just updated with the changes made
422 -- so that the mapping for the rbs does not become broken.
423 PA_Debug.G_Stage := 'Call Pa_Rbs_Elements_Pvt.Update_Children_Data() procedure.';
424 Pa_Rbs_Elements_Pvt.Update_Children_Data(
425 P_Rbs_Element_Id => P_Rbs_Element_Id,
426 X_Error_Msg_Data => X_Error_Msg_Data);
427
428 End If;
429
430
431 End If; -- error returned from ResourceNameCheck() procedure
432
433 Else -- The parent element does not exist
434
435 Pa_Debug.G_Stage := 'Parent element/node does not exists.';
436 Raise No_Data_Found;
437
438 End If; -- Pa_Rbs_Elements_Utils.RbsElementExists()
439
440 Pa_Debug.G_Stage := 'Leaving UpdateExisingRbsElement() procedure.';
441 Pa_Debug.TrackPath('STRIP','UpdateExisingRbsElement');
442
443
444 EXCEPTION
445 WHEN OTHERS THEN
446 Raise;
447
448 END UpdateExisingRbsElement;
449
450 Procedure CreateNewRbsElement(
451 P_Rbs_Version_Id IN Number,
452 P_Parent_Element_Id IN Number,
453 P_Rbs_Element_Id IN Number,
454 P_Resource_Type_Id IN Number,
455 P_Resource_Source_Id IN Number,
456 P_Order_Number IN Number,
457 X_RBS_Element_id OUT NOCOPY Number,
458 X_Error_Msg_Data OUT NOCOPY Varchar2)
459
460 Is
461
462 l_Person_Id Number := Null;
463 l_Job_Id Number := Null;
464 l_Organization_Id Number := Null;
465 l_Exp_Type_Id Number := Null;
466 l_Event_Type_Id Number := Null;
467 l_Exp_Cat_Id Number := Null;
468 l_Rev_Cat_Id Number := Null;
469 l_Inv_Item_Id Number := Null;
470 l_Item_Cat_Id Number := Null;
471 l_BOM_Labor_Id Number := Null;
472 l_BOM_Equip_Id Number := Null;
473 l_Non_Labor_Res_Id Number := Null;
474 l_Role_Id Number := Null;
475 l_Person_Type_Id Number := Null;
476 l_Res_Class_Id Number := Null;
477 l_Supplier_Id Number := Null;
478 l_Rule_Flag Varchar2(1) := Null;
479 l_Order_Number Number := Null;
480 l_Rbs_Element_Name_Id Number := Null;
481 l_Rbs_Element_Id Number := Null;
482 l_Rbs_Level Number := Null;
483 l_Element_Identifier Number := Null;
484 l_Outline_Number Varchar2(240) := Null;
485 l_User_Def_Custom1_Id Number := Null;
486 l_User_Def_Custom2_Id Number := Null;
487 l_User_Def_Custom3_Id Number := Null;
488 l_User_Def_Custom4_Id Number := Null;
489 l_User_Def_Custom5_Id Number := Null;
490 l_Mode Varchar2(1) := 'A';
491
492 Begin
493
494 Pa_Debug.G_Stage := 'Entering CreateNewRbsElement().';
495 Pa_Debug.TrackPath('ADD','CreateNewRbsElement');
496
497 -- Check if the parent element is valid.
498 Pa_Debug.G_Stage := 'Check to see if parent element/node exists.';
499 If Pa_Rbs_Elements_Utils.RbsElementExists( P_Element_Id => P_Parent_Element_Id ) = 'Y' Then
500
501 Pa_Debug.G_Stage := 'Call ValidateAndBuildElement() procedure.';
502 Pa_Rbs_Elements_Pvt.ValidateAndBuildElement(
503 P_Mode => l_Mode,
504 P_Rbs_Version_Id => P_Rbs_Version_Id,
505 P_Parent_Element_Id => P_Parent_Element_Id,
506 P_Rbs_Element_Id => P_Rbs_Element_Id,
507 P_Resource_Type_Id => P_Resource_Type_Id,
508 P_Resource_Source_Id => P_Resource_Source_Id,
509 P_Order_Number => P_Order_Number,
510 X_Person_Id => l_Person_Id,
511 X_Job_Id => l_Job_Id,
512 X_Organization_Id => l_Organization_Id,
513 X_Exp_Type_Id => l_Exp_Type_Id,
514 X_Event_Type_Id => l_Event_Type_Id,
515 X_Exp_Cat_Id => l_Exp_Cat_Id,
516 X_Rev_Cat_Id => l_Rev_Cat_Id,
517 X_Inv_Item_Id => l_Inv_Item_Id,
518 X_Item_Cat_Id => l_Item_Cat_Id,
519 X_BOM_Labor_Id => l_BOM_Labor_Id,
520 X_BOM_Equip_Id => l_BOM_Equip_Id,
521 X_Non_Labor_Res_Id => l_Non_Labor_Res_Id,
522 X_Role_Id => l_Role_Id,
523 X_Person_Type_Id => l_Person_Type_Id,
524 X_User_Def_Custom1_Id => l_User_Def_Custom1_Id,
525 X_User_Def_Custom2_Id => l_User_Def_Custom2_Id,
526 X_User_Def_Custom3_Id => l_User_Def_Custom3_Id,
527 X_User_Def_Custom4_Id => l_User_Def_Custom4_Id,
528 X_User_Def_Custom5_Id => l_User_Def_Custom5_Id,
529 X_Res_Class_Id => l_Res_Class_Id,
530 X_Supplier_Id => l_Supplier_Id,
531 X_Rbs_Level => l_Rbs_Level,
532 X_Rule_Based_Flag => l_Rule_Flag,
533 X_Rbs_Element_Name_Id => l_Rbs_Element_Name_Id,
534 X_Order_Number => l_Order_Number,
535 X_Element_Identifier => l_Element_Identifier,
536 X_Outline_Number => l_Outline_Number,
537 X_Error_Msg_Data => X_Error_Msg_Data);
538
539 If X_Error_Msg_Data is Null Then
540
541 -- call the table handler to update the record.
542 Pa_Debug.G_Stage := 'Call Pa_Rbs_Elements_Pkg.Insert_Row() procedure.';
543 Pa_Rbs_Elements_Pkg.Insert_Row(
544 P_Rbs_Element_Name_Id => l_Rbs_Element_Name_Id,
545 P_Rbs_Version_Id => P_Rbs_Version_Id,
546 P_Outline_Number => l_outline_number,
547 P_Order_Number => l_Order_Number,
548 P_Resource_Type_Id => P_Resource_Type_Id,
549 P_Resource_Source_Id => P_Resource_Source_Id,
550 P_Person_Id => l_Person_Id,
551 P_Job_Id => l_Job_Id,
552 P_Organization_Id => l_Organization_Id,
553 P_Expenditure_Type_Id => l_Exp_Type_Id,
554 P_Event_Type_Id => l_Event_Type_Id,
555 P_Expenditure_Category_Id => l_Exp_Cat_Id,
556 P_Revenue_Category_Id => l_Rev_Cat_Id,
557 P_Inventory_Item_Id => l_Inv_Item_Id,
558 P_Item_Category_Id => l_Item_Cat_Id,
559 P_BOM_Labor_Id => l_BOM_Labor_Id,
560 P_BOM_Equipment_Id => l_BOM_Equip_Id,
561 P_Non_Labor_Resource_Id => l_Non_Labor_Res_Id,
562 P_Role_Id => l_Role_Id,
563 P_Person_Type_Id => l_Person_Type_Id,
564 P_Resource_Class_Id => l_Res_Class_Id,
565 P_Supplier_Id => l_Supplier_Id,
566 P_Rule_Flag => l_Rule_Flag,
567 P_Parent_Element_Id => P_Parent_Element_Id,
568 P_Rbs_Level => l_Rbs_Level,
569 P_Element_Identifier => l_Element_Identifier,
570 P_User_Created_Flag => 'Y',
571 P_User_Defined_Custom1_Id => l_User_Def_Custom1_Id,
572 P_User_Defined_Custom2_Id => l_User_Def_Custom2_Id,
573 P_User_Defined_Custom3_Id => l_User_Def_Custom3_Id,
574 P_User_Defined_Custom4_Id => l_User_Def_Custom4_Id,
575 P_User_Defined_Custom5_Id => l_User_Def_Custom5_Id,
576 P_Last_Update_Date => Pa_Rbs_Elements_Pvt.G_Last_Update_Date,
577 P_Last_Updated_By => Pa_Rbs_Elements_Pvt.G_Last_Updated_By,
578 P_Last_Update_Login => Pa_Rbs_Elements_Pvt.G_Last_Update_Login,
579 P_Creation_Date => Pa_Rbs_Elements_Pvt.G_Creation_Date,
580 P_Created_By => Pa_Rbs_Elements_Pvt.G_Created_By,
581 X_Rbs_Element_Id => X_Rbs_Element_Id,
582 X_Error_Msg_Data => X_Error_Msg_Data);
583
584 End If; -- error returned from ResourceNameCheck() procedure
585
586 Else -- The parent element does not exist
587
588 Pa_Debug.G_Stage := 'Parent element/node does not exist.';
589 Raise No_Data_Found;
590
591 End If; -- Pa_Rbs_Elements_Utils.RbsElementExists()
592
593 Pa_Debug.G_Stage := 'Leaving CreateNewRbsElement() procedure.';
594 Pa_Debug.TrackPath('STRIP','CreateNewRbsElement');
595
596 Exception
597 When Others Then
598 Raise;
599
600 End CreateNewRbsElement;
601
602 Procedure ValidateAndBuildElement(
603 P_Mode IN Varchar2,
604 P_Rbs_Version_Id IN Number,
605 P_Parent_Element_Id IN Number,
606 P_Rbs_Element_Id IN Number,
607 P_Resource_Type_Id IN Number,
608 P_Resource_Source_Id IN Number,
609 P_Order_Number IN Number,
610 X_Person_Id OUT NOCOPY Number,
611 X_Job_Id OUT NOCOPY Number,
612 X_Organization_Id OUT NOCOPY Number,
613 X_Exp_Type_Id OUT NOCOPY Number,
614 X_Event_Type_Id OUT NOCOPY Number,
615 X_Exp_Cat_Id OUT NOCOPY Number,
616 X_Rev_Cat_Id OUT NOCOPY Number,
617 X_Inv_Item_Id OUT NOCOPY Number,
618 X_Item_Cat_Id OUT NOCOPY Number,
619 X_BOM_Labor_Id OUT NOCOPY Number,
620 X_BOM_Equip_Id OUT NOCOPY Number,
621 X_Non_Labor_Res_Id OUT NOCOPY Number,
622 X_Role_Id OUT NOCOPY Number,
623 X_Person_Type_Id OUT NOCOPY Number,
624 X_User_Def_Custom1_Id OUT NOCOPY Number,
625 X_User_Def_Custom2_Id OUT NOCOPY Number,
626 X_User_Def_Custom3_Id OUT NOCOPY Number,
627 X_User_Def_Custom4_Id OUT NOCOPY Number,
628 X_User_Def_Custom5_Id OUT NOCOPY Number,
629 X_Res_Class_Id OUT NOCOPY Number,
630 X_Supplier_Id OUT NOCOPY Number,
631 X_Rbs_Level OUT NOCOPY Number,
632 X_Rule_Based_Flag OUT NOCOPY Varchar2,
633 X_Rbs_Element_Name_Id OUT NOCOPY Number,
634 X_Order_Number OUT NOCOPY Number,
635 X_Element_Identifier OUT NOCOPY Number,
636 X_Outline_Number OUT NOCOPY Varchar2,
637 X_Error_Msg_Data OUT NOCOPY Varchar2)
638
639 Is
640
641 CURSOR chk_element_exists(p_resource_type_id IN NUMBER,
642 p_resource_source_id IN NUMBER,
643 p_rbs_element_id IN NUMBER,
644 P_rbs_level IN NUMBER)
645 IS
646 SELECT 'Y', rbs_element_id
647 FROM pa_rbs_elements
648 WHERE resource_type_id = p_resource_type_id
649 AND resource_source_id = p_resource_source_id
650 AND Rbs_Version_Id = P_Rbs_Version_Id
651 AND rbs_element_id <> nvl(p_rbs_element_id, -99)
652 AND rbs_level = P_rbs_level;
653
654 CURSOR chk_element_diff_level(p_resource_type_id IN NUMBER,
655 p_resource_source_id IN NUMBER,
656 p_rbs_element_id IN NUMBER)
657 IS
658 SELECT 'Y'
659 FROM pa_rbs_elements
660 WHERE resource_type_id = p_resource_type_id
661 AND resource_source_id = p_resource_source_id
662 AND Rbs_Version_Id = P_Rbs_Version_Id
663 AND rbs_element_id <> nvl(p_rbs_element_id, -99);
664
665 l_ele_exists VARCHAR2(1) := 'N';
666 l_unique_branch VARCHAR2(1) := 'N';
667 l_exists_element_id NUMBER := NULL;
668
669 l_Resource_Type Varchar2(30) := Null;
670 MAX_USER_DEF_RES_IDS Exception;
671 MAX_RBS_LEVELS Exception;
672 NON_UNIQUE_BRANCH Exception;
673 GET_ELEMENT_NAME_ID_FAILED Exception;
674 CANNOT_CREATE_RULES Exception;
675 l_Dummy_Error_Status Varchar2(1) := Null;
676 l_Dummy_Error_Count Number := Null;
677 l_number_of_peers NUMBER;
678 l_o_number NUMBER;
679 l_old_resource_type_id NUMBER := null;
680 l_old_resource_source_id NUMBER := null;
681 l_use_for_alloc_flag VARCHAR2(1) := Null; --Bug 3725965
682
683 Begin
684 -- hr_utility.trace_on(NULL, 'RMRBS');
685 -- hr_utility.trace('******** START ******* ');
686 --dbms_output.put_line('******** START *******');
687
688 Pa_Debug.G_Stage := 'Entering ValidateAndBuildElement().';
689 Pa_Debug.TrackPath('ADD','ValidateAndBuildElement');
690
691 IF p_mode = 'U' THEN
692 -- Get the old Resource Type Id and Resource Source Id for
693 -- the element.
694 SELECT resource_type_id,
695 resource_source_id
696 INTO l_old_resource_type_id,
697 l_old_resource_source_id
698 FROM PA_RBS_ELEMENTS
699 WHERE rbs_element_id = p_rbs_element_id;
700 END IF;
701
702 Pa_Debug.G_Stage := 'Call ValidateRbsElement() procedure.';
703 --dbms_output.put_line('before ValidateRbsElement');
704 Pa_Rbs_Elements_Pvt.ValidateRbsElement(
705 P_Mode => P_Mode,
706 P_Rbs_Version_Id => P_Rbs_Version_Id,
707 P_Parent_Element_Id => P_Parent_Element_Id,
708 P_Rbs_Element_Id => P_Rbs_Element_Id,
709 P_Old_Resource_Type_Id => l_Old_Resource_Type_Id,
710 P_Old_Resource_Source_Id => l_Old_Resource_Source_Id,
711 P_Resource_Type_Id => P_Resource_Type_Id,
712 P_Resource_Source_Id => P_Resource_Source_Id,
713 X_Resource_Type => l_Resource_Type,
714 X_Error_Msg_Data => X_Error_Msg_Data);
715
716 --dbms_output.put_line('after ValidateRbsElement');
717 If X_Error_Msg_Data is Null Then
718
719 Pa_Debug.G_Stage := 'Call GetParentRbsData() procedure.';
720 --dbms_output.put_line('before GetParentRbsData');
721 Pa_Rbs_Elements_Pvt.GetParentRbsData(
722 P_Parent_Element_Id => P_Parent_Element_Id,
723 X_Person_Id => X_Person_Id,
724 X_Job_Id => X_Job_Id,
725 X_Organization_Id => X_Organization_Id,
726 X_Exp_Type_Id => X_Exp_Type_Id,
727 X_Event_Type_Id => X_Event_Type_Id,
728 X_Exp_Cat_Id => X_Exp_Cat_Id,
729 X_Rev_Cat_Id => X_Rev_Cat_Id,
730 X_Inv_Item_Id => X_Inv_Item_Id,
731 X_Item_Cat_Id => X_Item_Cat_Id,
732 X_BOM_Labor_Id => X_BOM_Labor_Id,
733 X_BOM_Equip_Id => X_BOM_Equip_Id,
734 X_Non_Labor_Res_Id => X_Non_Labor_Res_Id,
735 X_Role_Id => X_Role_Id,
736 X_Person_Type_Id => X_Person_Type_Id,
737 X_User_Def_Custom1_Id => X_User_Def_Custom1_Id,
738 X_User_Def_Custom2_Id => X_User_Def_Custom2_Id,
739 X_User_Def_Custom3_Id => X_User_Def_Custom3_Id,
740 X_User_Def_Custom4_Id => X_User_Def_Custom4_Id,
741 X_User_Def_Custom5_Id => X_User_Def_Custom5_Id,
742 X_Res_Class_Id => X_Res_Class_Id,
743 X_Supplier_Id => X_Supplier_Id,
744 X_Rbs_Level => X_Rbs_Level,
745 X_Outline_Number => X_Outline_Number);
746 --dbms_output.put_line('after GetParentRbsData');
747
748 If l_Resource_Type = 'BOM_LABOR' Then
749
750 Pa_Debug.G_Stage := 'Assign the resource source id to BOM Labor.';
751 X_BOM_Labor_Id := P_Resource_Source_Id;
752
753 Elsif l_Resource_Type = 'BOM_EQUIPMENT' Then
754
755 Pa_Debug.G_Stage := 'Assign the resource source id to BOM Equipment.';
756 X_BOM_Equip_Id := P_Resource_Source_Id;
757
758 Elsif l_Resource_Type = 'NAMED_PERSON' Then
759
760 Pa_Debug.G_Stage := 'Assign the resource source id to Named Person.';
761 X_Person_Id := P_Resource_Source_Id;
762
763 Elsif l_Resource_Type = 'EVENT_TYPE' Then
764
765 Pa_Debug.G_Stage := 'Assign the resource source id to Event Type.';
766 X_Event_Type_Id := P_Resource_Source_Id;
767
768 Elsif l_Resource_Type = 'EXPENDITURE_CATEGORY' Then
769
770 Pa_Debug.G_Stage := 'Assign the resource source id to Expenditure Category.';
771 X_Exp_Cat_Id := P_Resource_Source_Id;
772
773 Elsif l_Resource_Type = 'EXPENDITURE_TYPE' Then
774
775 Pa_Debug.G_Stage := 'Assign the resource source id to Expenditure Type.';
776 X_Exp_Type_Id := P_Resource_Source_Id;
777
778 Elsif l_Resource_Type = 'ITEM_CATEGORY' Then
779
780 Pa_Debug.G_Stage := 'Assign the resource source id to Item Category.';
781 X_Item_Cat_Id := P_Resource_Source_Id;
782
783 Elsif l_Resource_Type = 'INVENTORY_ITEM' Then
784
785 Pa_Debug.G_Stage := 'Assign the resource source id to Inventory Item.';
786 X_Inv_Item_Id := P_Resource_Source_Id;
787
788 Elsif l_Resource_Type = 'JOB' Then
789
790 Pa_Debug.G_Stage := 'Assign the resource source id to Job.';
791 X_Job_Id := P_Resource_Source_Id;
792
793 Elsif l_Resource_Type = 'ORGANIZATION' Then
794
795 Pa_Debug.G_Stage := 'Assign the resource source id to Organization.';
796 X_Organization_Id := P_Resource_Source_Id;
797
798 Elsif l_Resource_Type = 'PERSON_TYPE' Then
799
800 Pa_Debug.G_Stage := 'Assign the resource source id to Person Type.';
801 X_Person_Type_Id := P_Resource_Source_Id;
802
803 Elsif l_Resource_Type = 'NON_LABOR_RESOURCE' Then
804
805 Pa_Debug.G_Stage := 'Assign the resource source id to Non Labor Resource.';
806 X_Non_Labor_Res_Id := P_Resource_Source_Id;
807
808 Elsif l_Resource_Type = 'RESOURCE_CLASS' Then
809
810 Pa_Debug.G_Stage := 'Assign the resource source id to Resource Class.';
811 X_Res_Class_Id := P_Resource_Source_Id;
812
813 Elsif l_Resource_Type = 'REVENUE_CATEGORY' Then
814
815 Pa_Debug.G_Stage := 'Assign the resource source id to Revenue Category.';
816 X_Rev_Cat_Id := P_Resource_Source_Id;
817
818 Elsif l_Resource_Type = 'ROLE' Then
819
820 Pa_Debug.G_Stage := 'Assign the resource source id to Role.';
821 X_Role_Id := P_Resource_Source_Id;
822
823 Elsif l_Resource_Type = 'SUPPLIER' Then
824
825 Pa_Debug.G_Stage := 'Assign the resource source id to Supplier.';
826 X_Supplier_Id := P_Resource_Source_Id;
827
828 Elsif l_Resource_Type = 'USER_DEFINED' Then
829
830 Pa_Debug.G_Stage := 'Assign the resource source id to User Defined.';
831 If X_User_Def_Custom1_Id Is Null Then
832
833 Pa_Debug.G_Stage := 'Assign the resource source id to User Defined 1.';
834 X_User_Def_Custom1_Id := P_Resource_Source_Id;
835
836 ElsIf X_User_Def_Custom2_Id Is Null Then
837
838 Pa_Debug.G_Stage := 'Assign the resource source id to User Defined 2.';
839 X_User_Def_Custom2_Id := P_Resource_Source_Id;
840
841 ElsIf X_User_Def_Custom3_Id Is Null Then
842
843 Pa_Debug.G_Stage := 'Assign the resource source id to User Defined 3.';
844 X_User_Def_Custom3_Id := P_Resource_Source_Id;
845
846 ElsIf X_User_Def_Custom4_Id Is Null Then
847
848 Pa_Debug.G_Stage := 'Assign the resource source id to User Defined 4.';
849 X_User_Def_Custom4_Id := P_Resource_Source_Id;
850
851 ElsIf X_User_Def_Custom5_Id Is Null Then
852
853 Pa_Debug.G_Stage := 'Assign the resource source id to User Defined 5.';
854 X_User_Def_Custom5_Id := P_Resource_Source_Id;
855
856 Else
857
858 Pa_Debug.G_Stage := 'All user defined resources field populated.';
859 Raise MAX_USER_DEF_RES_IDS;
860
861 End If;
862
863 End If;
864
865 -- set the value for the rule flag
866 --Modified for bug fix 3736374.
867 --Rule flag is set 'N' for an instance based node and all RBS nodes
868 --(both rule and instance-based) below it.
869 --Top most node of an RBS has rule flag = 'N'.
870 Pa_Debug.G_Stage := 'Determine the rule based flag.';
871 --dbms_output.put_line('get rule based flag');
872 Select
873 Decode(r.parent_element_id,null,Decode(P_Resource_Source_Id,-1,'Y','N'),
874 Decode(r.rule_flag,'Y',Decode(P_Resource_Source_Id,-1,'Y','N'),'N'))
875 Into
876 X_Rule_Based_Flag
877 From
878 pa_rbs_elements r
879 Where
880 r.rbs_element_id = P_Parent_Element_Id; --End of bug fix 3736374.
881
882 --dbms_output.put_line('after get rule based flag');
883 -- Need to increment the Rbs Level for the child we got
884 -- the value from the parent.
885 Pa_Debug.G_Stage := 'Increment the rbs level based on the parent level.';
886 X_Rbs_level := X_Rbs_Level + 1;
887
888 IF X_Rbs_Level > 10 THEN
889 -- We do not allow more then 10 Levels. So error out.
890 RAISE MAX_RBS_LEVELS;
891 END IF;
892
893 PA_DEBUG.G_Stage := 'Check if element is variation of same combination in different combination - Job-Org vs Org-Job.';
894 -- Fix bugs 3909551 and 3882731 by checking to see if the new/upd
895 -- element already exists in the RBS; if so, then check the entire
896 -- branch (except UDR) and ensure they are not the same. This will
897 -- prevent creation of DBA-Cons East and Cons East-DBA, and the
898 -- creation of the same element under different UDR nodes.
899
900 -- First, check whether element exists already:
901 -- hr_utility.trace('P_Resource_Type_Id IS : ' || P_Resource_Type_Id);
902 -- hr_utility.trace('P_Resource_Source_Id IS : ' || P_Resource_Source_Id);
903 -- hr_utility.trace('P_Rbs_Element_Id IS : ' || P_Rbs_Element_Id);
904 --dbms_output.put_line('before chk_element_exists');
905 l_unique_branch := 'Y';
906 OPEN chk_element_exists(P_Resource_Type_Id => P_Resource_Type_Id,
907 P_Resource_Source_Id => P_Resource_Source_Id,
908 P_Rbs_Element_Id => P_Rbs_Element_Id,
909 P_rbs_level => X_Rbs_level);
910
911 LOOP
912 FETCH chk_element_exists Into l_ele_exists, l_exists_element_id;
913 -- hr_utility.trace('IN LOOP');
914 -- hr_utility.trace('l_ele_exists IS : ' || l_ele_exists);
915 -- hr_utility.trace('l_exists_element_id IS : ' || l_exists_element_id);
916 l_unique_branch := 'Y';
917 EXIT WHEN chk_element_exists%NOTFOUND OR
918 l_unique_branch = 'N';
919
920 -- If element exists, then check uniqueness on branch:
921 PA_DEBUG.G_Stage := 'Check if element is unique combination - validation check 10.';
922
923 IF l_ele_exists = 'Y' THEN
924 -- hr_utility.trace('ELE EXISTS');
925 BEGIN
926 -- hr_utility.trace('x_organization_id IS : ' || x_organization_id);
927 -- hr_utility.trace('x_job_id IS : ' || x_job_id);
928 -- hr_utility.trace('x_person_id IS : ' || x_person_id);
929 SELECT 'N'
930 INTO l_unique_branch
931 FROM pa_rbs_elements
932 WHERE rbs_element_id = l_exists_element_id
933 AND nvl(person_id, -99) = nvl(x_person_id, -99)
934 AND nvl(organization_id, -99) = nvl(x_organization_id, -99)
935 AND nvl(job_id, -99) = nvl(x_job_id, -99)
936 AND nvl(supplier_id, -99) = nvl(x_supplier_id, -99)
937 AND nvl(expenditure_type_id, -99) = nvl(x_exp_type_id, -99)
938 AND nvl(event_type_id, -99) = nvl(x_event_type_id, -99)
939 AND nvl(revenue_category_id, -99) = nvl(x_rev_cat_id, -99)
940 AND nvl(inventory_item_id, -99) = nvl(x_inv_item_id, -99)
941 AND nvl(item_category_id, -99) = nvl(x_item_cat_id, -99)
942 AND nvl(bom_labor_id, -99) = nvl(x_bom_labor_id, -99)
943 AND nvl(bom_equipment_id, -99) = nvl(x_bom_equip_id, -99)
944 AND nvl(person_type_id, -99) = nvl(x_person_type_id, -99)
945 AND nvl(resource_class_id, -99) = nvl(x_res_class_id, -99)
946 AND nvl(role_id, -99) = nvl(x_role_id, -99)
947 AND nvl(non_labor_resource_id, -99) = nvl(x_non_labor_res_id, -99)
948 AND nvl(expenditure_category_id, -99) = nvl(x_exp_cat_id,-99)
949 AND nvl(User_Defined_Custom1_Id, -99) = nvl(X_User_Def_Custom1_Id, -99)
950 AND nvl(User_Defined_Custom2_Id, -99) = nvl(X_User_Def_Custom2_Id, -99)
951 AND nvl(User_Defined_Custom3_Id, -99) = nvl(X_User_Def_Custom3_Id, -99)
952 AND nvl(User_Defined_Custom4_Id, -99) = nvl(X_User_Def_Custom4_Id, -99)
953 AND nvl(User_Defined_Custom5_Id, -99) = nvl(X_User_Def_Custom5_Id, -99);
954 EXCEPTION
955 WHEN NO_DATA_FOUND THEN
956 l_unique_branch := 'Y';
957 WHEN OTHERS THEN
958 l_unique_branch := 'N';
959 END;
960
961 -- hr_utility.trace('l_unique_branch IS : ' || l_unique_branch);
962 IF l_unique_branch = 'N' THEN
963 -- hr_utility.trace('RAISE ERROR');
964 RAISE NON_UNIQUE_BRANCH;
965 END IF;
966
967 END IF;
968 END LOOP;
969 CLOSE chk_element_exists;
970
971 -- Bug - Prevent creation of identical branches.
972 -- e.g. disallow DBA-CW and CW-DBA - the actual nodes
973 -- are different but the branches are the same.
974 -- First check if the node being added exists in RBS - then
975 -- only does the possiblity of the same branch arise.
976
977 --dbms_output.put_line('before chk_element_diff_level');
978 l_unique_branch := 'Y';
979 OPEN chk_element_diff_level(P_Resource_Type_Id => P_Resource_Type_Id,
980 P_Resource_Source_Id => P_Resource_Source_Id,
981 P_Rbs_Element_Id => P_Rbs_Element_Id);
982
983 FETCH chk_element_diff_level Into l_ele_exists;
984 -- hr_utility.trace('IN Identical branch check flow');
985 -- hr_utility.trace('l_ele_exists IS : ' || l_ele_exists);
986 -- hr_utility.trace('l_exists_element_id IS : ' || l_exists_element_id);
987 IF chk_element_diff_level%FOUND THEN
988 l_ele_exists := 'Y';
989 ELSE
990 l_ele_exists := 'N';
991 END IF;
992
993 CLOSE chk_element_diff_level;
994
995 -- Check whether identical branch exists
996 --dbms_output.put_line('l_ele_exists IS ' || l_ele_exists);
997 IF l_ele_exists = 'Y' THEN
998 -- hr_utility.trace('identical Branch exists Check');
999 BEGIN
1000 -- hr_utility.trace('x_organization_id IS : ' || x_organization_id);
1001 -- hr_utility.trace('x_job_id IS : ' || x_job_id);
1002 -- hr_utility.trace('x_person_id IS : ' || x_person_id);
1003 --dbms_output.put_line('before iden branch check');
1004 --dbms_output.put_line('P_Rbs_Element_Id IS ' || P_Rbs_Element_Id);
1005 --dbms_output.put_line('P_Rbs_Version_Id IS ' || P_Rbs_Version_Id);
1006 SELECT 'N'
1007 INTO l_unique_branch
1008 FROM pa_rbs_elements
1009 WHERE rbs_element_id <> nvl(P_Rbs_Element_Id,-99)
1010 AND rbs_version_id = P_Rbs_Version_Id
1011 AND rbs_level = X_Rbs_level
1012 AND nvl(person_id, -99) = nvl(x_person_id, -99)
1013 AND nvl(organization_id, -99) = nvl(x_organization_id, -99)
1014 AND nvl(job_id, -99) = nvl(x_job_id, -99)
1015 AND nvl(supplier_id, -99) = nvl(x_supplier_id, -99)
1016 AND nvl(expenditure_type_id, -99) = nvl(x_exp_type_id, -99)
1017 AND nvl(event_type_id, -99) = nvl(x_event_type_id, -99)
1018 AND nvl(revenue_category_id, -99) = nvl(x_rev_cat_id, -99)
1019 AND nvl(inventory_item_id, -99) = nvl(x_inv_item_id, -99)
1020 AND nvl(item_category_id, -99) = nvl(x_item_cat_id, -99)
1021 AND nvl(bom_labor_id, -99) = nvl(x_bom_labor_id, -99)
1022 AND nvl(bom_equipment_id, -99) = nvl(x_bom_equip_id, -99)
1023 AND nvl(person_type_id, -99) = nvl(x_person_type_id, -99)
1024 AND nvl(resource_class_id, -99) = nvl(x_res_class_id, -99)
1025 AND nvl(role_id, -99) = nvl(x_role_id, -99)
1026 AND nvl(non_labor_resource_id, -99) = nvl(x_non_labor_res_id, -99)
1027 AND nvl(expenditure_category_id, -99) = nvl(x_exp_cat_id,-99)
1028 AND nvl(User_Defined_Custom1_Id, -99) = nvl(X_User_Def_Custom1_Id, -99)
1029 AND nvl(User_Defined_Custom2_Id, -99) = nvl(X_User_Def_Custom2_Id, -99)
1030 AND nvl(User_Defined_Custom3_Id, -99) = nvl(X_User_Def_Custom3_Id, -99)
1031 AND nvl(User_Defined_Custom4_Id, -99) = nvl(X_User_Def_Custom4_Id, -99)
1032 AND nvl(User_Defined_Custom5_Id, -99) = nvl(X_User_Def_Custom5_Id, -99);
1033 EXCEPTION
1034 WHEN NO_DATA_FOUND THEN
1035 l_unique_branch := 'Y';
1036 WHEN OTHERS THEN
1037 l_unique_branch := 'N';
1038 END;
1039 --dbms_output.put_line('after iden branch check');
1040 --dbms_output.put_line('l_unique_branch IS ' || l_unique_branch);
1041
1042 -- hr_utility.trace('l_unique_branch IS : ' || l_unique_branch);
1043 IF l_unique_branch = 'N' THEN
1044 -- hr_utility.trace('RAISE ERROR');
1045 RAISE NON_UNIQUE_BRANCH;
1046 END IF;
1047
1048 END IF;
1049 --Added for bug fix 3725965.
1050
1051 Select
1052 use_for_alloc_flag
1053 Into
1054 l_use_for_alloc_flag
1055 From
1056 pa_rbs_headers_vl h,
1057 pa_rbs_versions_vl v
1058 where
1059 v.rbs_version_id=P_Rbs_Version_Id
1060 And
1061 h.rbs_header_id=v.rbs_header_id;
1062
1063
1064 If l_use_for_alloc_flag='Y' Then
1065 If P_RESOURCE_SOURCE_ID = -1 Then --For bug 3803213.
1066 --If X_Rule_Based_Flag ='Y' Then --Commented for bug fix 3803213.
1067 Raise CANNOT_CREATE_RULES;
1068 End If;
1069 End If; --End of bug fix 3725965
1070
1071 IF P_Order_Number = FND_API.G_MISS_NUM
1072 THEN
1073 X_Order_Number := null;
1074 ELSE
1075 X_Order_Number := P_Order_Number;
1076 END IF;
1077
1078 IF X_Order_Number = -1 THEN
1079 X_Order_Number := null;
1080 END IF;
1081
1082 -- The procedure Pa_Rbs_Utils.Populate_RBS_Element_Name() handles
1083 -- returning the rbs_element_name_id and creating rbs element name
1084 -- records if needed.
1085 Pa_Debug.G_Stage := 'Call Pa_Rbs_Utils.Populate_RBS_Element_Name() procedure.';
1086
1087
1088 Pa_Rbs_Utils.Populate_RBS_Element_Name (
1089 P_Resource_Source_Id => P_Resource_Source_Id,
1090 P_Resource_Type_Id => P_Resource_Type_Id,
1091 X_Rbs_Element_Name_Id => X_Rbs_Element_Name_Id,
1092 X_Return_Status => l_Dummy_Error_Status);
1093
1094
1095 If l_Dummy_Error_Status <> FND_API.G_RET_STS_SUCCESS
1096 Then
1097 Pa_Debug.G_Stage := 'Call to Pa_Rbs_Utils.Populate_RBS_Element_Name() procedure failed.';
1098 Raise GET_ELEMENT_NAME_ID_FAILED;
1099 End If;
1100
1101 -- Get the Element Identifier
1102 Pa_Debug.G_Stage := 'Check if update or add to determine element identifier value.';
1103 IF P_Mode = 'A' THEN
1104
1105 Pa_Debug.G_Stage := 'Get the next available element identifier sequence value for add.';
1106 -- Get the next value in the sequence
1107 Select Pa_Rbs_Element_Identifier_S.NextVal
1108 Into X_Element_Identifier
1109 From Dual;
1110
1111 ELSE
1112
1113 Pa_Debug.G_Stage := 'Retrieve the element identifier value from pa_rbs_elements for update.';
1114 -- Get the value from the rbs_elements table.
1115 Select Element_Identifier
1116 Into X_Element_Identifier
1117 From Pa_Rbs_Elements
1118 Where Rbs_Element_Id = P_Rbs_Element_Id;
1119
1120 END IF;
1121
1122 l_number_of_peers := 0;
1123
1124
1125 IF p_mode = 'A' THEN
1126
1127 -- Set the outline number
1128
1129 SELECT count(*)
1130 INTO l_number_of_peers
1131 FROM PA_RBS_ELEMENTS
1132 WHERE parent_element_id = p_parent_element_id
1133 AND USER_CREATED_FLAG = 'Y';
1134
1135 IF l_number_of_peers = 0 THEN
1136 l_o_number := 1;
1137 ELSE
1138 l_o_number := l_number_of_peers + 1;
1139 END IF;
1140
1141 IF X_Outline_Number = '0' THEN
1142 X_Outline_Number := l_o_number;
1143 ELSE
1144 X_Outline_Number := X_Outline_Number || '.' || l_o_number;
1145 END IF;
1146 ELSE
1147 -- Mode is update. Get the outline number of the element
1148
1149 SELECT outline_number
1150 INTO x_outline_number
1151 FROM PA_RBS_ELEMENTS
1152 WHERE RBS_ELEMENT_ID = P_RBS_ELEMENT_ID;
1153
1154 END IF;
1155
1156
1157 END IF;
1158
1159 Pa_Debug.G_Stage := 'Leaving ValidateAndBuildElement() procedure.';
1160 Pa_Debug.TrackPath('STRIP','ValidateAndBuildElement');
1161
1162 Exception
1163 When MAX_USER_DEF_RES_IDS Then
1164 X_Error_Msg_Data := 'PA_MAX_USER_DEF_RES_IDS';
1165 When MAX_RBS_LEVELS Then
1166 X_Error_Msg_Data := 'PA_MAX_RBS_LEVELS';
1167 When NON_UNIQUE_BRANCH Then
1168 X_Error_Msg_Data := 'PA_NON_UNIQUE_BRANCH';
1169 When CANNOT_CREATE_RULES Then
1170 X_Error_Msg_Data := 'PA_RBS_CANNOT_CREATE_RULES';
1171 When Others Then
1172 Raise;
1173
1174 End ValidateAndBuildElement;
1175
1176
1177 -- A
1178 -- | |
1179 -- B C
1180 -- | | |
1181 -- E G F
1182
1183 Procedure ValidateRbsElement(
1184 P_Mode IN Varchar2,
1185 P_Rbs_Version_Id IN Number,
1186 P_Parent_Element_Id IN Number,
1187 P_Rbs_Element_Id IN Number,
1188 P_Old_Resource_Type_Id IN Number,
1189 P_Old_Resource_Source_Id IN Number,
1190 P_Resource_Type_Id IN Number,
1191 P_Resource_Source_Id IN Number,
1192 X_Resource_Type OUT NOCOPY Varchar2,
1193 X_Error_Msg_Data OUT NOCOPY Varchar2)
1194
1195 IS
1196
1197 CURSOR c_CheckDupSiblings
1198 (P_RbsVersionId IN Number,
1199 P_ParentId IN Number,
1200 P_ResSourceId IN Number,
1201 P_ResTypeId IN Number)
1202 IS
1203
1204 SELECT Count(*)
1205 FROM PA_RBS_ELEMENTS
1206 WHERE Rbs_Version_Id = P_RbsVersionId
1207 AND Resource_Type_Id = P_ResTypeId
1208 AND Resource_Source_Id = P_ResSourceId
1209 AND parent_element_id = p_parentId;
1210
1211 l_dummy_count Number := 0;
1212
1213 CURSOR c_CheckExistParentMatch
1214 (P_Res_Type_Id IN Number,
1215 P_Res_Srce_Id IN Number,
1216 P_Parent_Elem_Id IN Number)
1217 IS
1218
1219 Select Count(*)
1220 From Pa_Rbs_Elements
1221 Where Resource_Type_Id = P_Res_Type_Id
1222 And Resource_Source_Id = P_Res_Srce_Id
1223 Start With Rbs_Element_Id = P_Parent_Elem_Id
1224 Connect By Prior Parent_Element_Id = Rbs_Element_Id;
1225
1226 CURSOR c_CheckExistChildMatch
1227 (P_Res_Type_Id IN Number,
1228 P_Res_Srce_Id IN Number,
1229 P_Rbs_Elem_Id IN Number)
1230 IS
1231 Select Count(*)
1232 From Pa_Rbs_Elements
1233 Where Resource_Type_Id = P_Res_Type_Id
1234 And Resource_Source_Id = P_Res_Srce_Id
1235 Start With Parent_Element_Id = P_Rbs_Elem_Id
1236 Connect By Prior Rbs_Element_Id = Parent_Element_Id;
1237
1238 DUP_SIBLING_RES_RES_TYPE Exception;
1239 INVAL_RES_TYPE Exception;
1240 ELEMENT_USER_DEF_RULE Exception;
1241 INVALID_RESOURCE Exception;
1242 DUP_PARENT_RES_RES_TYPE Exception;
1243 DUP_CHILD_RES_RES_TYPE Exception;
1244 RES_TYPE_RES_NOT_CON_LVLS Exception;
1245 CONS_RES_TYPES_NOT_INSTANCES Exception;
1246 l_Current_level Number;
1247 l_grand_parent_element_id Number;
1248 l_parent_resource_type_id Number;
1249 l_parent_resource_source_id Number;
1250
1251
1252 CURSOR c_GetCurrentLevel(P_Parent_Id IN Number)
1253 IS
1254 SELECT rbs_level + 1
1255 FROM Pa_Rbs_Elements
1256 WHERE Rbs_Element_Id = P_Parent_Id;
1257
1258 CURSOR c_CheckResTypeInRbs
1259 (P_Res_Type_Id IN Number,
1260 P_Rbs_Level IN Number,
1261 P_Version_Id IN Number)
1262 IS
1263 SELECT Count(*)
1264 FROM Pa_Rbs_Elements
1265 WHERE Resource_Type_Id = P_Res_Type_Id
1266 AND Rbs_Level not in (P_Rbs_Level, P_Rbs_Level - 1, P_Rbs_Level + 1)
1267 AND Rbs_Version_Id = P_Version_Id;
1268
1269 CURSOR children_elements(P_Rbs_Element_Id IN NUMBER)
1270 IS
1271 SELECT rbs_element_id , resource_type_id, resource_source_id
1272 FROM pa_rbs_elements
1273 WHERE parent_element_id = P_Rbs_Element_Id;
1274
1275 BEGIN
1276
1277 Pa_Debug.G_Stage := 'Entering ValidateRbsElement().';
1278 Pa_Debug.TrackPath('ADD','ValidateRbsElement');
1279
1280 -- 1. Validate the resource_type
1281 -- We need this validation irrespective of whether the resource type is
1282 -- changed or not. The x_resource_type we get below is used by the
1283 -- calling API to populate the element record in both update and insert mode.
1284
1285 X_Resource_Type := Pa_Rbs_Elements_Utils.GetResTypeCode
1286 (P_Res_Type_Id => P_Resource_Type_Id);
1287
1288
1289 IF X_Resource_Type is Null or X_Resource_Type = 'NAMED_ROLE'
1290 THEN
1291
1292 Pa_Debug.G_Stage := 'Invalid resource type id passed in.';
1293 RAISE INVAL_RES_TYPE;
1294 END IF;
1295
1296 -- If the resource type or the resource source id has not changed from
1297 -- the old values, we do not need to proceeed further.
1298
1299 IF p_old_resource_type_id = p_resource_type_id AND
1300 P_old_Resource_Source_Id = P_Resource_Source_Id THEN
1301 Return;
1302 END IF;
1303
1304
1305 -- 2. If the element is rule based, and resource_type is user-defined, it's an error.
1306 Pa_Debug.G_Stage := 'Determine if rule and user-defined which is not allowed.';
1307 If P_Resource_Source_Id = -1 and X_Resource_Type = 'USER_DEFINED' Then
1308
1309 Pa_Debug.G_Stage := 'Rule and user defined raise user defined error.';
1310 Raise ELEMENT_USER_DEF_RULE;
1311
1312 End If;
1313
1314 -- 3. If the element is not rule based, and resource_type is not user-defined,
1315 -- validate the resource_source_id.
1316 Pa_Debug.G_Stage := 'Validate the resource by calling the ValidateResource() procedure.';
1317 Pa_Rbs_Elements_Pvt.ValidateResource(
1318 P_Resource_Type_Id => P_Resource_Type_Id,
1319 P_Resource_Source_Id => P_Resource_Source_Id,
1320 P_Resource_Type => X_Resource_Type,
1321 X_Error_Msg_Data => X_Error_Msg_Data);
1322
1323 If X_Error_Msg_Data is Not Null Then
1324
1325 Pa_Debug.G_Stage := 'The Resource is invalid. Raise user defined error.';
1326 Raise INVALID_RESOURCE;
1327
1328 End If;
1329
1330 -- 4. Validate that the element is not the same as it's siblings.
1331 Pa_Debug.G_Stage := 'Open c_CheckDupSiblings cursor - validation check 4.';
1332 Open c_CheckDupSiblings(P_Rbs_Version_Id,
1333 P_Parent_Element_Id,
1334 P_Resource_Source_Id,
1335 P_Resource_Type_Id);
1336
1337
1338 Fetch c_CheckDupSiblings Into l_dummy_count;
1339 Close c_CheckDupSiblings;
1340
1341 IF l_dummy_count = 0
1342 THEN
1343 Null;
1344 ELSE
1345 Pa_Debug.G_Stage := 'Records found! Close c_CheckDupSiblings cursor. ' ||
1346 'Raise user defined error - validation check 4.';
1347 RAISE DUP_SIBLING_RES_RES_TYPE;
1348 END IF;
1349
1350 -- 5. Validate that the element is not the same as it's one of it's parents.
1351 Open c_CheckExistParentMatch(P_Resource_Type_Id,
1352 P_Resource_Source_Id,
1353 P_Parent_Element_Id);
1354
1355 Fetch c_CheckExistParentMatch Into l_Dummy_Count;
1356 Close c_CheckExistParentMatch;
1357
1358 IF l_dummy_count = 0 THEN
1359 Null;
1360 ELSE
1361 Pa_Debug.G_Stage := 'Raise user defined error - validation check 5.';
1362 Raise DUP_PARENT_RES_RES_TYPE;
1363 END IF;
1364
1365 -- 6. Validate that the element is not repeated in the branches below it.
1366 -- This check does not need to be done when adding a element/node. It won't have children
1367 -- to worry about.
1368 Pa_Debug.G_Stage := 'Check if validation is for updating an element - validation check 6.';
1369 If P_Mode = 'U' THEN
1370
1371 Open c_CheckExistChildMatch(P_Resource_Type_Id,P_Resource_Source_Id,P_Rbs_Element_Id);
1372
1373 Fetch c_CheckExistChildMatch Into l_Dummy_Count;
1374 Close c_CheckExistChildMatch;
1375
1376 IF l_dummy_count = 0 THEN
1377 Null;
1378 ELSE
1379 Pa_Debug.G_Stage := 'Raise user defined error - validation check 6.';
1380 Raise DUP_CHILD_RES_RES_TYPE;
1381 END IF;
1382
1383 END IF;
1384
1385 PA_DEBUG.G_Stage := 'Check if element/node is rule based - validation check 7.';
1386
1387 -- 7. Resources of Same Resource Type can be repeated in a branch only
1388 -- in consecutive generations and if both the elements are instance
1389 -- based (not rule based)
1390
1391 /*
1392 Check the Parent's resource type.
1393 i. Check if the parent resource type is same as the current element's
1394 resource type.
1395 a. If resource types are same, check if the current element is
1396 rule based. If yes, error out since when resource type is
1397 repeated, the elements must be instances and not rule.
1398 b. If the resource types are different, check if the current
1399 element's resource type is same as any of it's grandparents.
1400 If yes, error out, since resource type can be repeated only
1401 in consecutive generations.
1402 */
1403
1404 -- Get parent's resource type
1405 BEGIN
1406 SELECT resource_type_id, resource_source_id
1407 INTO l_parent_resource_type_id, l_parent_resource_source_id
1408 FROM PA_RBS_ELEMENTS
1409 WHERE rbs_element_id = P_Parent_Element_Id;
1410 EXCEPTION
1411 WHEN OTHERS THEN
1412 null;
1413 END;
1414
1415 IF l_parent_resource_type_id = p_resource_type_id
1416 THEN
1417 IF p_resource_source_id = -1 OR
1418 l_parent_resource_source_id = -1 THEN
1419 RAISE CONS_RES_TYPES_NOT_INSTANCES;
1420 END IF;
1421
1422 ELSE
1423
1424 -- We need to check for grand parent's resource types only if the
1425 -- parent's resource type is different from the element's resource type.
1426
1427
1428 l_dummy_count := 0;
1429
1430 SELECT count(*)
1431 INTO l_dummy_count
1432 FROM PA_RBS_ELEMENTS
1433 WHERE RESOURCE_TYPE_ID = P_Resource_Type_Id
1434 START WITH rbs_element_id = p_parent_element_id
1435 CONNECT BY PRIOR parent_element_id = rbs_element_id;
1436
1437 IF l_dummy_count > 0 THEN
1438 RAISE RES_TYPE_RES_NOT_CON_LVLS;
1439 ELSE
1440 -- There are no grand parents with the same
1441 -- resource type.
1442 null;
1443 END IF;
1444
1445 END IF;
1446
1447
1448 -- Check resource type with the children
1449
1450 IF p_mode = 'U' THEN
1451 FOR c1 in children_elements(P_Rbs_Element_Id) LOOP
1452
1453 IF P_Resource_Type_Id = c1.resource_type_id THEN
1454
1455 IF p_resource_source_id = -1 OR
1456 c1.resource_source_id = -1 THEN
1457 RAISE CONS_RES_TYPES_NOT_INSTANCES;
1458 END IF;
1459
1460 ELSE
1461 l_dummy_count := 0;
1462
1463 SELECT count(*)
1464 INTO l_dummy_count
1465 FROM PA_RBS_ELEMENTS
1466 WHERE RESOURCE_TYPE_ID = P_Resource_Type_Id
1467 START WITH parent_element_id = c1.rbs_element_id
1468 CONNECT BY PRIOR rbs_element_id = parent_element_id;
1469
1470
1471 IF l_dummy_count > 0 THEN
1472 RAISE RES_TYPE_RES_NOT_CON_LVLS;
1473 ELSE
1474 -- There are no grand children with the
1475 -- same resource type
1476 null;
1477 END IF;
1478 END IF;
1479 END LOOP;
1480
1481 -- If the element is updated, and the old resource type happened
1482 -- to be same as parent resource type, then we need to check if
1483 -- any child exists with the same resource type. If it does,
1484 -- then we need to error out, since change the resource type of the
1485 -- element will create a break in consecutive resource types.
1486
1487 IF (l_parent_resource_type_id = P_Old_Resource_Type_Id
1488 AND p_resource_type_id <> p_old_resource_type_id)
1489 THEN
1490
1491 l_dummy_count := 0;
1492
1493 select count(*)
1494 INTO l_dummy_count
1495 FROM PA_RBS_ELEMENTS
1496 WHERE PARENT_ELEMENT_ID = p_rbs_element_id
1497 AND RESOURCE_TYPE_ID = p_old_resource_type_id;
1498
1499 IF l_dummy_count > 0 THEN
1500 RAISE RES_TYPE_RES_NOT_CON_LVLS;
1501 ELSE
1502 -- There are no children with the same resource type,
1503 -- so changing the resource type will not create
1504 -- any discontinuity
1505 null;
1506 END IF;
1507
1508 END IF;
1509
1510 END IF; /* p_mode = 'U' */
1511 Pa_Debug.G_Stage := 'Leaving ValidateRbsElement() procedure.';
1512 Pa_Debug.TrackPath('STRIP','ValidateRbsElement');
1513
1514 EXCEPTION
1515 WHEN INVAL_RES_TYPE THEN
1516 X_Error_Msg_Data := 'PA_RBS_INVAL_RES_TYPE';
1517 WHEN ELEMENT_USER_DEF_RULE THEN
1518 X_Error_Msg_Data := 'PA_RBS_ELE_USER_DEF_RULE';
1519 WHEN INVALID_RESOURCE THEN
1520 Null;
1521 WHEN DUP_SIBLING_RES_RES_TYPE THEN
1522 X_Error_Msg_Data := 'PA_RBS_DUP_SIB_RES_RES_TYPE';
1523 WHEN DUP_PARENT_RES_RES_TYPE THEN
1524 X_Error_Msg_Data := 'PA_RBS_DUP_PAR_RES_RES_TYPE';
1525 WHEN DUP_CHILD_RES_RES_TYPE THEN
1526 X_Error_Msg_Data := 'PA_RBS_DUP_CHD_RES_RES_TYPE';
1527 WHEN RES_TYPE_RES_NOT_CON_LVLS THEN
1528 X_Error_Msg_Data := 'PA_RES_TYPE_RES_NOT_CON_LVLS';
1529 WHEN CONS_RES_TYPES_NOT_INSTANCES THEN
1530 X_Error_Msg_Data := 'PA_CONS_RBS_ELE_NOT_INS';
1531 WHEN Others THEN
1532 Raise;
1533
1534 END ValidateRbsElement;
1535
1536 Procedure ValidateResource(
1537 P_Resource_Type_Id IN Number,
1538 P_Resource_Source_Id IN Number,
1539 P_Resource_Type IN Varchar2,
1540 X_Error_Msg_Data OUT NOCOPY Varchar2)
1541
1542 Is
1543
1544 l_Rev_Code Varchar2(30) := Null;
1545 l_Person_Type_Code Varchar2(30) := Null; --Added for Bug 3780201
1546 l_Named_Role Varchar2(30) := Null;
1547 l_dummy Varchar2(1) := Null;
1548
1549 Cursor c_GetResCode(P_Res_Type_Id IN Number,
1550 P_Res_Srce_Id IN Number) Is
1551 Select
1552 Resource_Name
1553 From
1554 Pa_Rbs_Element_Map
1555 Where
1556 Resource_Type_Id = P_Res_Type_Id
1557 And Resource_Id = P_Res_Srce_Id;
1558
1559 Cursor c_EventType ( P_Id IN Number ) Is
1560 Select
1561 'Y'
1562 From
1563 Pa_Event_Types
1564 Where
1565 Event_Type_Id = P_Id
1566 And Event_Type_Classification IN ('AUTOMATIC','MANUAL','WRITE OFF','WRITE ON');
1567
1568 Cursor c_ExpType ( P_Id IN Number) Is
1569 Select
1570 'Y'
1571 From
1572 Pa_Expenditure_Types
1573 Where
1574 Expenditure_Type_Id = P_Id;
1575
1576 Cursor c_RevCat ( P_Code IN Varchar2) Is
1577 Select
1578 'Y'
1579 From
1580 Pa_Lookups
1581 Where
1582 Lookup_Code = P_Code
1583 And Lookup_Type = 'REVENUE CATEGORY';
1584
1585 Cursor c_People (P_Id IN Number) is
1586 Select
1587 'Y'
1588 From
1589 Per_People_X
1590 Where
1591 Person_Id = P_Id
1592 And ( (Pa_Cross_Business_Grp.IsCrossBGProfile = 'N' AND
1593 Fnd_Profile.Value('PER_BUSINESS_GROUP_ID') = Per_People_X.Business_Group_Id)
1594 OR Pa_Cross_Business_Grp.IsCrossBGProfile = 'Y');
1595
1596 Cursor c_Job (P_Id In Number) Is
1597 Select
1598 'Y'
1599 From
1600 Per_Jobs
1601 Where
1602 Job_Id = P_Id
1603 And ( (Pa_Cross_Business_Grp.IsCrossBGProfile = 'N' AND
1604 Fnd_Profile.Value('PER_BUSINESS_GROUP_ID') = Per_Jobs.Business_Group_Id )
1605 OR Pa_Cross_Business_Grp.IsCrossBGProfile = 'Y');
1606
1607 Cursor c_BOM (P_BOM_Res_Id IN Number) Is
1608 Select
1609 'Y'
1610 From
1611 Bom_Resources
1612 Where
1613 Resource_Id = P_BOM_Res_Id;
1614
1615 Cursor c_ItemCat ( P_Id IN Number ) Is
1616 Select
1617 'Y'
1618 From
1619 Mtl_Categories_tl
1620 Where
1621 Language = USERENV('LANG')
1622 And Category_Id = P_Id;
1623
1624 Cursor c_InvenItem (P_Id IN Number ) Is
1625 Select
1626 'Y'
1627 From
1628 Mtl_System_Items_tl
1629 Where
1630 Language = USERENV('LANG')
1631 And Inventory_Item_Id = P_Id;
1632
1633 Cursor c_ResClass (P_Id IN Number) Is
1634 Select
1635 'Y'
1636 From
1637 Pa_Resource_Classes_Vl
1638 Where
1639 Resource_Class_Id = P_Id;
1640
1641 Cursor c_PrjRoles (P_Id IN Number) Is
1642 Select
1643 'Y'
1644 From
1645 Pa_Project_Role_Types_B
1646 Where
1647 Project_Role_Id = P_Id;
1648
1649 Cursor c_Org(P_Id IN Number) Is
1650 Select
1651 'Y'
1652 From
1653 Hr_All_Organization_Units
1654 Where
1655 Organization_Id = P_Id;
1656
1657 Cursor c_Supplier (P_Id IN Number) Is
1658 Select
1659 'Y'
1660 From
1661 Po_Vendors
1662 Where
1663 Vendor_Id = P_Id;
1664
1665 --Commented for Bug 3780201
1666 /* Cursor c_PerTypes (P_Id IN Number) Is
1667 Select
1668 'Y'
1669 From
1670 Per_Person_Types
1671 Where
1672 Person_Type_Id = P_Id
1673 And Business_Group_Id = 0;*/
1674 --Added for Bug 3780201
1675 Cursor c_PerTypes ( P_Code IN Varchar2) Is
1676 Select
1677 'Y'
1678 From
1679 Pa_Lookups
1680 Where
1681 Lookup_Code = P_Code
1682 And Lookup_Type = 'PA_PERSON_TYPE';
1683 --Changes for Bug 3780201 end
1684
1685 Cursor c_NLRs(P_Id IN Number) Is
1686 Select
1687 'Y'
1688 From
1689 Pa_Non_Labor_Resources
1690 Where
1691 Non_Labor_Resource_Id = P_Id;
1692
1693 Cursor c_ExpCat(P_Id IN Number) Is
1694 Select
1695 'Y'
1696 From
1697 Pa_Expenditure_Categories
1698 Where
1699 Expenditure_Category_Id = P_Id;
1700
1701 Begin
1702
1703
1704 Pa_Debug.G_Stage := 'Entering ValidateResource().';
1705 Pa_Debug.TrackPath('ADD','ValidateResource');
1706
1707 Pa_Debug.G_Stage := 'Check what the resource type is to determine how to validate the resource.';
1708
1709 If P_Resource_Type IN ('BOM_LABOR','BOM_EQUIPMENT') And
1710 P_Resource_Source_Id <> -1 Then
1711
1712 Pa_Debug.G_Stage := 'Validating BOM Labor or BOM Equipment resource.';
1713 Open c_BOM(P_Resource_Source_Id);
1714 Fetch c_BOM Into l_Dummy;
1715
1716 If c_BOM%NotFound Then
1717
1718 X_Error_Msg_Data := 'PA_RBS_ELE_INVALID_RESOURCE';
1719
1720 End If;
1721 Close c_BOM;
1722
1723 Elsif P_Resource_Type = 'NAMED_PERSON' And
1724 P_Resource_Source_Id <> -1 Then
1725
1726 Pa_Debug.G_Stage := 'Validating Name Person resource.';
1727 Open c_People(P_Resource_Source_Id);
1728 Fetch c_People Into l_Dummy;
1729
1730 If c_People%NotFound Then
1731
1732 X_Error_Msg_Data := 'PA_RBS_ELE_NVALID_RESOURCE';
1733
1734 End If;
1735 Close c_People;
1736
1737 Elsif P_Resource_Type = 'EVENT_TYPE' And
1738 P_Resource_Source_Id <> -1 Then
1739
1740 Pa_Debug.G_Stage := 'Validating Event Type resource.';
1741 Open c_EventType(P_Resource_Source_Id);
1742 Fetch c_EventType Into l_Dummy;
1743
1744 If c_EventType%NotFound Then
1745
1746 X_Error_Msg_Data := 'PA_RBS_ELE_INVALID_RESOURCE';
1747
1748 End If;
1749 Close c_EventType;
1750
1751 Elsif P_Resource_Type = 'EXPENDITURE_CATEGORY' And
1752 P_Resource_Source_Id <> -1 Then
1753
1754 Pa_Debug.G_Stage := 'Validating Expenditure Category resource.';
1755 Open c_ExpCat(P_Resource_Source_Id);
1756 Fetch c_ExpCat Into l_Dummy;
1757
1758 If c_ExpCat%NotFound Then
1759
1760 X_Error_Msg_Data := 'PA_RBS_ELE_INVALID_RESOURCE';
1761
1762 End If;
1763 Close c_ExpCat;
1764
1765 Elsif P_Resource_Type = 'EXPENDITURE_TYPE' And
1766 P_Resource_Source_Id <> -1 Then
1767
1768 Pa_Debug.G_Stage := 'Validating Expenditure Type resource.';
1769 Open c_ExpType(P_Resource_Source_Id);
1770 Fetch c_ExpType Into l_Dummy;
1771
1772 If c_ExpType%NotFound Then
1773
1774 X_Error_Msg_Data := 'PA_RBS_ELE_INVALID_RESOURCE';
1775
1776 End If;
1777 Close c_ExpType;
1778
1779 Elsif P_Resource_Type = 'ITEM_CATEGORY' And
1780 P_Resource_Source_Id <> -1 Then
1781
1782 Pa_Debug.G_Stage := 'Validating Item Category resource.';
1783 Open c_ItemCat(P_Resource_Source_Id);
1784 Fetch c_ItemCat Into l_Dummy;
1785
1786 If c_ItemCat%NotFound Then
1787
1788 X_Error_Msg_Data := 'PA_RBS_ELE_INVALID_RESOURCE';
1789
1790 End If;
1791 Close c_ItemCat;
1792
1793 Elsif P_Resource_Type = 'INVENTORY_ITEM' And
1794 P_Resource_Source_Id <> -1 Then
1795
1796 Pa_Debug.G_Stage := 'Validating Inventory Item resource.';
1797 Open c_InvenItem(P_Resource_Source_Id);
1798 Fetch c_InvenItem Into l_Dummy;
1799
1800 If c_InvenItem%NotFound Then
1801
1802 X_Error_Msg_Data := 'PA_RBS_ELE_INVALID_RESOURCE';
1803
1804 End If;
1805 Close c_InvenItem;
1806
1807 Elsif P_Resource_Type = 'JOB' And
1808 P_Resource_Source_Id <> -1 Then
1809
1810 Pa_Debug.G_Stage := 'Validating Job resource.';
1811 Open c_Job(P_Resource_Source_Id);
1812 Fetch c_Job Into l_Dummy;
1813
1814 If c_Job%NotFound Then
1815
1816 X_Error_Msg_Data := 'PA_RBS_ELE_INVALID_RESOURCE';
1817
1818 End If;
1819 Close c_Job;
1820
1821 Elsif P_Resource_Type = 'ORGANIZATION' And
1822 P_Resource_Source_Id <> -1 Then
1823
1824 Pa_Debug.G_Stage := 'Validating Organization resource.';
1825 Open c_Org(P_Resource_Source_Id);
1826 Fetch c_Org Into l_Dummy;
1827
1828 If c_Org%NotFound Then
1829
1830 X_Error_Msg_Data := 'PA_RBS_ELE_INVALID_RESOURCE';
1831
1832 End If;
1833 Close c_Org;
1834
1835 Elsif P_Resource_Type = 'PERSON_TYPE' And
1836 P_Resource_Source_Id <> -1 Then
1837
1838 Pa_Debug.G_Stage := 'Validating Person Type resource.';
1839 --Commented for Bug 3780201
1840 /* Open c_PerTypes(P_Resource_Source_Id);
1841 Fetch c_PerTypes Into l_Dummy;
1842
1843 If c_PerTypes%NotFound Then
1844
1845 X_Error_Msg_Data := 'PA_RBS_ELE_INVALID_RESOURCE';
1846
1847 End If;
1848 Close c_PerTypes;*/
1849 --Added for Bug 3780201
1850 Open c_GetResCode(P_Resource_Type_Id,P_Resource_Source_Id);
1851 Fetch c_GetResCode Into l_Person_Type_Code;
1852 Close c_GetResCode;
1853
1854 If l_Person_Type_Code Is Not Null Then
1855
1856 Open c_PerTypes(l_Person_Type_Code);
1857 Fetch c_PerTypes Into l_Dummy;
1858
1859 If c_PerTypes%NotFound Then
1860
1861 X_Error_Msg_Data := 'PA_RBS_ELE_INVALID_RESOURCE';
1862
1863 End If;
1864 Close c_PerTypes;
1865
1866 Else
1867
1868 X_Error_Msg_Data := 'PA_RBS_ELE_INVALID_RESOURCE';
1869
1870 End If;
1871 --Changes for Bug 3780201 end
1872
1873 Elsif P_Resource_Type = 'NON_LABOR_RESOURCE' And
1874 P_Resource_Source_Id <> -1 Then
1875
1876 Pa_Debug.G_Stage := 'Validating Non Labor Resource resource.';
1877 Open c_NLRs(P_Resource_Source_Id);
1878 Fetch c_NLRs Into l_Dummy;
1879
1880 If c_NLRs%NotFound Then
1881
1882 X_Error_Msg_Data := 'PA_RBS_ELE_INVALID_RESOURCE';
1883
1884 End If;
1885 Close c_NLRs;
1886
1887 Elsif P_Resource_Type = 'RESOURCE_CLASS' And
1888 P_Resource_Source_Id <> -1 Then
1889
1890 Pa_Debug.G_Stage := 'Validating Resource Class resource.';
1891 Open c_ResClass(P_Resource_Source_Id);
1892 Fetch c_ResClass Into l_Dummy;
1893
1894 If c_ResClass%NotFound Then
1895
1896 X_Error_Msg_Data := 'PA_RBS_ELE_INVALID_RESOURCE';
1897
1898 End If;
1899 Close c_ResClass;
1900
1901 Elsif P_Resource_Type = 'REVENUE_CATEGORY' And
1902 P_Resource_Source_Id <> -1 Then
1903
1904 Pa_Debug.G_Stage := 'Validating Revenue Category resource.';
1905 Open c_GetResCode(P_Resource_Type_Id,P_Resource_Source_Id);
1906 Fetch c_GetResCode Into l_Rev_Code;
1907 Close c_GetResCode;
1908
1909 If l_Rev_Code Is Not Null Then
1910
1911 Open c_RevCat(l_Rev_Code);
1912 Fetch c_RevCat Into l_Dummy;
1913
1914 If c_RevCat%NotFound Then
1915
1916 X_Error_Msg_Data := 'PA_RBS_ELE_INVALID_RESOURCE';
1917
1918 End If;
1919 Close c_RevCat;
1920
1921 Else
1922
1923 X_Error_Msg_Data := 'PA_RBS_ELE_INVALID_RESOURCE';
1924
1925 End If;
1926
1927 Elsif P_Resource_Type = 'ROLE' And
1928 P_Resource_Source_Id <> -1 Then
1929
1930 Pa_Debug.G_Stage := 'Validating Role resource.';
1931 Open c_PrjRoles(P_Resource_Source_Id);
1932 Fetch c_PrjRoles Into l_Dummy;
1933
1934 If c_PrjRoles%NotFound Then
1935
1936 X_Error_Msg_Data := 'PA_RBS_ELE_INVALID_RESOURCE';
1937
1938 End If;
1939 Close c_PrjRoles;
1940
1941 Elsif P_Resource_Type = 'SUPPLIER' And
1942 P_Resource_Source_Id <> -1 Then
1943
1944 Pa_Debug.G_Stage := 'Validating Supplier resource.';
1945 Open c_Supplier(P_Resource_Source_Id);
1946 Fetch c_Supplier Into l_Dummy;
1947 If c_Supplier%NotFound Then
1948
1949 X_Error_Msg_Data := 'PA_RBS_ELE_INVALID_RESOURCE';
1950
1951 End If;
1952 Close c_Supplier;
1953
1954 End If;
1955
1956 Pa_Debug.G_Stage := 'Leaving ValidateResource() procedure.';
1957 Pa_Debug.TrackPath('STRIP','ValidateResource');
1958
1959 Exception
1960 When Others Then
1961 Raise;
1962
1963 End ValidateResource;
1964
1965 Procedure GetParentRbsData(
1966 P_Parent_Element_Id IN Number,
1967 X_Person_Id OUT NOCOPY Number,
1968 X_Job_Id OUT NOCOPY Number,
1969 X_Organization_Id OUT NOCOPY Number,
1970 X_Exp_Type_Id OUT NOCOPY Number,
1971 X_Event_Type_Id OUT NOCOPY Number,
1972 X_Exp_Cat_Id OUT NOCOPY Number,
1973 X_Rev_Cat_Id OUT NOCOPY Number,
1974 X_Inv_Item_Id OUT NOCOPY Number,
1975 X_Item_Cat_Id OUT NOCOPY Number,
1976 X_BOM_Labor_Id OUT NOCOPY Number,
1977 X_BOM_Equip_Id OUT NOCOPY Number,
1978 X_Non_Labor_Res_Id OUT NOCOPY Number,
1979 X_Role_Id OUT NOCOPY Number,
1980 X_Person_Type_Id OUT NOCOPY Number,
1981 X_User_Def_Custom1_Id OUT NOCOPY Number,
1982 X_User_Def_Custom2_Id OUT NOCOPY Number,
1983 X_User_Def_Custom3_Id OUT NOCOPY Number,
1984 X_User_Def_Custom4_Id OUT NOCOPY Number,
1985 X_User_Def_Custom5_Id OUT NOCOPY Number,
1986 X_Res_Class_Id OUT NOCOPY Number,
1987 X_Supplier_Id OUT NOCOPY Number,
1988 X_Rbs_Level OUT NOCOPY Number,
1989 X_outline_number OUT NOCOPY VARCHAR2)
1990
1991 Is
1992
1993 Begin
1994
1995 Pa_Debug.G_Stage := 'Entering GetParentRbsData().';
1996 Pa_Debug.TrackPath('ADD','GetParentRbsData');
1997
1998 Pa_Debug.G_Stage := 'Retrieve the parent rbs element data for use in child element/node.';
1999 Select
2000 Person_Id,
2001 Job_Id,
2002 Organization_Id,
2003 Expenditure_Type_Id,
2004 Event_Type_Id,
2005 Expenditure_Category_Id,
2006 Revenue_Category_Id,
2007 Inventory_Item_Id,
2008 Item_Category_Id,
2009 BOM_Labor_Id,
2010 BOM_Equipment_Id,
2011 Non_Labor_Resource_Id,
2012 Role_Id,
2013 Person_Type_Id,
2014 User_Defined_Custom1_Id,
2015 User_Defined_Custom2_Id,
2016 User_Defined_Custom3_id,
2017 User_Defined_Custom4_Id,
2018 User_Defined_Custom5_Id,
2019 Resource_Class_Id,
2020 Supplier_Id,
2021 Rbs_Level,
2022 Outline_number
2023 Into
2024 X_Person_Id,
2025 X_Job_Id,
2026 X_Organization_Id,
2027 X_Exp_Type_Id,
2028 X_Event_Type_Id,
2029 X_Exp_Cat_Id,
2030 X_Rev_Cat_Id,
2031 X_Inv_Item_Id,
2032 X_Item_Cat_Id,
2033 X_BOM_Labor_Id,
2034 X_BOM_Equip_Id,
2035 X_Non_Labor_Res_Id,
2036 X_Role_Id,
2037 X_Person_Type_Id,
2038 X_User_Def_Custom1_Id,
2039 X_User_Def_Custom2_Id,
2040 X_User_Def_Custom3_Id,
2041 X_User_Def_Custom4_Id,
2042 X_User_Def_Custom5_Id,
2043 X_Res_Class_Id,
2044 X_Supplier_Id,
2045 X_Rbs_Level,
2046 X_Outline_Number
2047 From
2048 Pa_Rbs_Elements
2049 Where
2050 Rbs_Element_Id = P_Parent_Element_Id;
2051 -- And Rbs_Level <> 0;
2052
2053 Pa_Debug.G_Stage := 'Leaving GetParentRbsData() procedure.';
2054 Pa_Debug.TrackPath('STRIP','GetParentRbsData');
2055
2056 Exception
2057 When No_Data_Found Then
2058 Null;
2059 When Others Then
2060 Raise;
2061
2062 End GetParentRbsData;
2063
2064 -- This procedure is only really neccessary for the Update/Create Child Elements page
2065 -- but will always be called.
2066 -- Since we can't control the order of the records in the pl/sql table it may
2067 -- occur that children are process more than once.
2068 Procedure UpdateOrderOutlineNumber(
2069 P_Parent_Element_Id_Tbl IN System.Pa_Num_Tbl_Type,
2070 X_Error_Msg_Data OUT NOCOPY Varchar2 )
2071
2072 Is
2073
2074 i number := null;
2075 l_par_element_id number := -1;
2076 l_par_outline_number varchar2(240) := Null;
2077 l_order_number number := Null;
2078
2079 -- Gets the max order number for the the level
2080 Cursor c1(P_Par_Element_Id IN Number) is
2081 Select
2082 Max(Order_Number)
2083 From
2084 Pa_Rbs_Elements
2085 Where
2086 Parent_Element_Id = P_Par_Element_Id;
2087
2088 -- Gets the parent outline number
2089 Cursor c2(P_Par_Element_Id IN Number) Is
2090 Select
2091 Outline_Number
2092 From
2093 Pa_Rbs_Elements
2094 Where
2095 Rbs_Element_Id = P_Par_Element_Id;
2096
2097 -- Gets all the children of the parent for update.
2098 Cursor c3(P_Par_Element_Id IN Number) Is
2099 Select
2100 Rbs_Element_Id,
2101 Order_Number
2102 From
2103 Pa_Rbs_Elements
2104 Where
2105 Parent_Element_Id = P_Par_Element_Id
2106 For Update of Outline_Number NoWait;
2107
2108 l_rbs_rec c3%RowType;
2109
2110 Begin
2111
2112 Pa_Debug.G_Stage := 'Entering UpdateOrderOutlineNumber().';
2113 Pa_Debug.TrackPath('ADD','UpdateOrderOutlineNumber');
2114
2115 Pa_Debug.G_Stage := 'Begin loop thru the parent array table to process the elements/nodes for the parents.';
2116 For i in P_Parent_Element_Id_Tbl.First .. P_Parent_Element_Id_Tbl.Last
2117 Loop
2118
2119 Pa_Debug.G_Stage := 'Check if working with new parent element/node.';
2120 If l_par_element_id <> P_Parent_Element_Id_Tbl(i) Then
2121
2122 Pa_Debug.G_Stage := 'Assign parent element/node id to local variable for if check.';
2123 l_par_element_id := P_Parent_Element_Id_Tbl(i);
2124
2125 -- Get the max order number for the children of the current parent
2126 Pa_Debug.G_Stage := 'Get the max order number for all the children of ' ||
2127 'the current parent element id being processed.';
2128 open c1(P_Parent_Element_Id_Tbl(i));
2129 Fetch c1 into l_order_number;
2130 Close c1;
2131
2132 -- Get the parent outline number
2133 Pa_Debug.G_Stage := 'Get parent outline number to use to append the child ' ||
2134 'order number to for the new child outline number.';
2135 open c2(P_Parent_Element_Id_Tbl(i));
2136 Fetch c2 into l_par_outline_number;
2137 Close c2;
2138
2139 -- Get all the children for the parent for update
2140 Pa_Debug.G_Stage := 'Open cursor to get all child elements for the current ' ||
2141 'parent element id.';
2142 Open c3(P_Parent_Element_Id_Tbl(i));
2143 Loop
2144
2145 Pa_Debug.G_Stage := 'Fetch record from cursor with all the child ' ||
2146 'elements of the current parent element being processed.';
2147 Fetch c3 into l_Rbs_Rec;
2148 Exit When c3%NotFound;
2149
2150 Pa_Debug.G_Stage := 'Check if the child element/node has an order number = -1.';
2151 If l_Rbs_Rec.Order_Number = -1 Then
2152
2153 Pa_Debug.G_Stage := 'Assign order number to a child that does ' ||
2154 'not currently have one.';
2155 l_order_number := l_order_number + 1;
2156
2157 Pa_Debug.G_Stage := 'Update the child rbs element record with new ' ||
2158 'order number and outline number - update 1.';
2159 Update pa_rbs_elements
2160 Set
2161 Order_Number = l_Order_Number,
2162 Outline_Number = decode(l_Par_Outline_Number,'0',
2163 to_char(l_Order_Number),
2164 l_Par_Outline_Number || '.' || to_char(l_Order_Number))
2165 Where
2166 Rbs_Element_Id = l_Rbs_Rec.Rbs_Element_id;
2167
2168 Else
2169
2170 Pa_Debug.G_Stage := 'Update the child rbs element record with the ' ||
2171 'outline number - update 2 .';
2172 Update Pa_Rbs_Elements
2173 Set
2174 Outline_Number = decode(l_Par_Outline_Number,'0',
2175 to_char(l_Rbs_Rec.Order_Number),
2176 l_Par_Outline_Number || '.' || to_char(l_Rbs_Rec.Order_Number))
2177 Where
2178 Rbs_Element_Id = l_Rbs_Rec.Rbs_Element_id;
2179
2180 End If;
2181
2182 End Loop;
2183
2184 End If;
2185
2186 End Loop;
2187
2188 Pa_Debug.G_Stage := 'Leaving UpdateOrderOutlineNumber() procedure.';
2189 Pa_Debug.TrackPath('STRIP','UpdateOrderOutlineNumber');
2190
2191 Exception
2192 When Others Then
2193 Raise;
2194
2195 End UpdateOrderOutlineNumber;
2196
2197 Procedure Update_Children_Data(
2198 P_Rbs_Element_Id IN Number,
2199 X_Error_Msg_Data OUT NOCOPY Varchar2)
2200
2201 Is
2202
2203 Cursor c1 (P_Rbs_Elem_Id IN Number) IS
2204 Select
2205 Rbs_Element_Id,
2206 Parent_Element_Id,
2207 Resource_Type_Id,
2208 Resource_Source_Id
2209 From
2210 Pa_Rbs_Elements
2211 Where
2212 User_Created_Flag = 'Y'
2213 Start With
2214 Parent_Element_Id = P_Rbs_Elem_Id
2215 Connect By Prior
2216 Rbs_Element_Id = Parent_Element_Id
2217 Order by
2218 Rbs_Level;
2219
2220 l_Child_Rec c1%RowType;
2221
2222 l_Person_Id Number(15) := Null;
2223 l_Job_Id Number(15) := Null;
2224 l_Organization_Id Number(15) := Null;
2225 l_Exp_Type_Id Number(15) := Null;
2226 l_Event_Type_Id Number(15) := Null;
2227 l_Exp_Cat_Id Number(15) := Null;
2228 l_Rev_Cat_Id Number(15) := Null;
2229 l_Inv_Item_Id Number(15) := Null;
2230 l_Item_Cat_Id Number(15) := Null;
2231 l_BOM_Labor_Id Number(15) := Null;
2232 l_BOM_Equip_Id Number(15) := Null;
2233 l_Non_Labor_Res_Id Number(15) := Null;
2234 l_Role_Id Number(15) := Null;
2235 l_Person_Type_Id Number(15) := Null;
2236 l_User_Def_Custom1_Id Number(15) := Null;
2237 l_User_Def_Custom2_Id Number(15) := Null;
2238 l_User_Def_Custom3_Id Number(15) := Null;
2239 l_User_Def_Custom4_Id Number(15) := Null;
2240 l_User_Def_Custom5_Id Number(15) := Null;
2241 l_Res_Class_Id Number(15) := Null;
2242 l_Supplier_Id Number(15) := Null;
2243 l_Dummy_Rbs_Level Number(15) := Null;
2244 l_Dummy_Outline_Number Varchar2(240) := Null;
2245 l_Resource_Type Varchar2(30) := Null;
2246
2247 MAX_USER_DEF_RES_IDS Exception;
2248
2249 Begin
2250
2251 Pa_Debug.G_Stage := 'Entering Update_Children_Data().';
2252 Pa_Debug.TrackPath('ADD','Update_Children_Data');
2253
2254 Pa_Debug.G_Stage := 'Open c1 cursor to get rbs elements to update.';
2255 Open c1(P_Rbs_Elem_Id => P_Rbs_Element_Id);
2256
2257 Loop
2258
2259 Pa_Debug.G_Stage := 'Fetch the current child record.';
2260 Fetch c1 Into l_Child_Rec;
2261 Exit When c1%NotFound;
2262
2263 Pa_Debug.G_Stage := 'Call GetParentRbsData() procedure.';
2264 Pa_Rbs_Elements_Pvt.GetParentRbsData(
2265 P_Parent_Element_Id => l_Child_Rec.Parent_Element_Id,
2266 X_Person_Id => l_Person_Id,
2267 X_Job_Id => l_Job_Id,
2268 X_Organization_Id => l_Organization_Id,
2269 X_Exp_Type_Id => l_Exp_Type_Id,
2270 X_Event_Type_Id => l_Event_Type_Id,
2271 X_Exp_Cat_Id => l_Exp_Cat_Id,
2272 X_Rev_Cat_Id => l_Rev_Cat_Id,
2273 X_Inv_Item_Id => l_Inv_Item_Id,
2274 X_Item_Cat_Id => l_Item_Cat_Id,
2275 X_BOM_Labor_Id => l_BOM_Labor_Id,
2276 X_BOM_Equip_Id => l_BOM_Equip_Id,
2277 X_Non_Labor_Res_Id => l_Non_Labor_Res_Id,
2278 X_Role_Id => l_Role_Id,
2279 X_Person_Type_Id => l_Person_Type_Id,
2280 X_User_Def_Custom1_Id => l_User_Def_Custom1_Id,
2281 X_User_Def_Custom2_Id => l_User_Def_Custom2_Id,
2282 X_User_Def_Custom3_Id => l_User_Def_Custom3_Id,
2283 X_User_Def_Custom4_Id => l_User_Def_Custom4_Id,
2284 X_User_Def_Custom5_Id => l_User_Def_Custom5_Id,
2285 X_Res_Class_Id => l_Res_Class_Id,
2286 X_Supplier_Id => l_Supplier_Id,
2287 X_Rbs_Level => l_Dummy_Rbs_Level,
2288 X_Outline_Number => l_Dummy_Outline_Number);
2289
2290 Pa_Debug.G_Stage := 'Get the Resource Type by calling Pa_Rbs_Elements_Utils.GetResTypeCode() function.';
2291 l_Resource_Type := Pa_Rbs_Elements_Utils.GetResTypeCode(P_Res_Type_Id => l_Child_Rec.Resource_Type_Id);
2292
2293 Pa_Debug.G_Stage := 'Determine which resource type we have for this element to properly assign the resource source id.';
2294 If l_Resource_Type = 'BOM_LABOR' Then
2295
2296 Pa_Debug.G_Stage := 'Assign the resource source id to BOM Labor.';
2297 l_BOM_Labor_Id := l_Child_Rec.Resource_Source_Id;
2298
2299 Elsif l_Resource_Type = 'BOM_EQUIPMENT' Then
2300
2301 Pa_Debug.G_Stage := 'Assign the resource source id to BOM Equipment.';
2302 l_BOM_Equip_Id := l_Child_Rec.Resource_Source_Id;
2303
2304 Elsif l_Resource_Type = 'NAMED_PERSON' Then
2305
2306 Pa_Debug.G_Stage := 'Assign the resource source id to Named Person.';
2307 l_Person_Id := l_Child_Rec.Resource_Source_Id;
2308
2309 Elsif l_Resource_Type = 'EVENT_TYPE' Then
2310
2311 Pa_Debug.G_Stage := 'Assign the resource source id to Event Type.';
2312 l_Event_Type_Id := l_Child_Rec.Resource_Source_Id;
2313
2314 Elsif l_Resource_Type = 'EXPENDITURE_CATEGORY' Then
2315
2316 Pa_Debug.G_Stage := 'Assign the resource source id to Expenditure Category.';
2317 l_Exp_Cat_Id := l_Child_Rec.Resource_Source_Id;
2318
2319 Elsif l_Resource_Type = 'EXPENDITURE_TYPE' Then
2320
2321 Pa_Debug.G_Stage := 'Assign the resource source id to Expenditure Type.';
2322 l_Exp_Type_Id := l_Child_Rec.Resource_Source_Id;
2323
2324 Elsif l_Resource_Type = 'ITEM_CATEGORY' Then
2325
2326 Pa_Debug.G_Stage := 'Assign the resource source id to Item Category.';
2327 l_Item_Cat_Id := l_Child_Rec.Resource_Source_Id;
2328
2329 Elsif l_Resource_Type = 'INVENTORY_ITEM' Then
2330
2331 Pa_Debug.G_Stage := 'Assign the resource source id to Inventory Item.';
2332 l_Inv_Item_Id := l_Child_Rec.Resource_Source_Id;
2333
2334 Elsif l_Resource_Type = 'JOB' Then
2335
2336 Pa_Debug.G_Stage := 'Assign the resource source id to Job.';
2337 l_Job_Id := l_Child_Rec.Resource_Source_Id;
2338
2339 Elsif l_Resource_Type = 'ORGANIZATION' Then
2340
2341 Pa_Debug.G_Stage := 'Assign the resource source id to Organization.';
2342 l_Organization_Id := l_Child_Rec.Resource_Source_Id;
2343
2344 Elsif l_Resource_Type = 'PERSON_TYPE' Then
2345
2346 Pa_Debug.G_Stage := 'Assign the resource source id to Person Type.';
2347 l_Person_Type_Id := l_Child_Rec.Resource_Source_Id;
2348
2349 Elsif l_Resource_Type = 'NON_LABOR_RESOURCE' Then
2350
2351 Pa_Debug.G_Stage := 'Assign the resource source id to Non Labor Resource.';
2352 l_Non_Labor_Res_Id := l_Child_Rec.Resource_Source_Id;
2353
2354 Elsif l_Resource_Type = 'RESOURCE_CLASS' Then
2355
2356 Pa_Debug.G_Stage := 'Assign the resource source id to Resource Class.';
2357 l_Res_Class_Id := l_Child_Rec.Resource_Source_Id;
2358
2359 Elsif l_Resource_Type = 'REVENUE_CATEGORY' Then
2360
2361 Pa_Debug.G_Stage := 'Assign the resource source id to Revenue Category.';
2362 l_Rev_Cat_Id := l_Child_Rec.Resource_Source_Id;
2363
2364 Elsif l_Resource_Type = 'ROLE' Then
2365
2366 Pa_Debug.G_Stage := 'Assign the resource source id to Role.';
2367 l_Role_Id := l_Child_Rec.Resource_Source_Id;
2368
2369 Elsif l_Resource_Type = 'SUPPLIER' Then
2370
2371 Pa_Debug.G_Stage := 'Assign the resource source id to Supplier.';
2372 l_Supplier_Id := l_Child_Rec.Resource_Source_Id;
2373
2374 Elsif l_Resource_Type = 'USER_DEFINED' Then
2375
2376 Pa_Debug.G_Stage := 'Assign the resource source id to User Defined.';
2377 If l_User_Def_Custom1_Id Is Null Then
2378
2379 Pa_Debug.G_Stage := 'Assign the resource source id to User Defined 1.';
2380 l_User_Def_Custom1_Id := l_Child_Rec.Resource_Source_Id;
2381
2382 ElsIf l_User_Def_Custom2_Id Is Null Then
2383
2384 Pa_Debug.G_Stage := 'Assign the resource source id to User Defined 2.';
2385 l_User_Def_Custom2_Id := l_Child_Rec.Resource_Source_Id;
2386
2387 ElsIf l_User_Def_Custom3_Id Is Null Then
2388
2389 Pa_Debug.G_Stage := 'Assign the resource source id to User Defined 3.';
2390 l_User_Def_Custom3_Id := l_Child_Rec.Resource_Source_Id;
2391
2392 ElsIf l_User_Def_Custom4_Id Is Null Then
2393
2394 Pa_Debug.G_Stage := 'Assign the resource source id to User Defined 4.';
2395 l_User_Def_Custom4_Id := l_Child_Rec.Resource_Source_Id;
2396
2397 ElsIf l_User_Def_Custom5_Id Is Null Then
2398
2399 Pa_Debug.G_Stage := 'Assign the resource source id to User Defined 5.';
2400 l_User_Def_Custom5_Id := l_Child_Rec.Resource_Source_Id;
2401
2402 Else
2403
2404 Pa_Debug.G_Stage := 'All user defined resources field populated.';
2405 Raise MAX_USER_DEF_RES_IDS;
2406
2407 End If;
2408
2409 End If;
2410
2411 Pa_Debug.G_Stage := 'Update the child element record.';
2412 Update Pa_Rbs_Elements
2413 Set
2414 Person_Id = l_Person_Id,
2415 Job_Id = l_Job_Id,
2416 Organization_Id = l_Organization_Id,
2417 Expenditure_Type_Id = l_Exp_Type_Id,
2418 Event_Type_Id = l_Event_Type_Id,
2419 Expenditure_Category_Id = l_Exp_Cat_Id,
2420 Revenue_Category_Id = l_Rev_Cat_Id,
2421 Inventory_Item_Id = l_Inv_Item_Id,
2422 Item_Category_Id = l_Item_Cat_Id,
2423 BOM_Labor_Id = l_BOM_Labor_Id,
2424 BOM_Equipment_Id = l_BOM_Equip_Id,
2425 Non_Labor_Resource_Id = l_Non_Labor_Res_Id,
2426 Role_Id = l_Role_Id,
2427 Person_Type_Id = l_Person_Type_Id,
2428 User_Defined_Custom1_Id = l_User_Def_Custom1_Id,
2429 User_Defined_Custom2_Id = l_User_Def_Custom2_Id,
2430 User_Defined_Custom3_Id = l_User_Def_Custom3_Id,
2431 User_Defined_Custom4_Id = l_User_Def_Custom4_Id,
2432 User_Defined_Custom5_Id = l_User_Def_Custom5_Id,
2433 Resource_Class_Id = l_Res_Class_Id,
2434 Supplier_Id = l_Supplier_Id
2435 Where Rbs_Element_Id = l_Child_Rec.Rbs_Element_Id;
2436
2437 End Loop;
2438
2439 Pa_Debug.G_Stage := 'Close the primary cursor c1.';
2440 Close c1;
2441
2442 Pa_Debug.G_Stage := 'Leaving Update_Children_Data() procedure.';
2443 Pa_Debug.TrackPath('STRIP','Update_Children_Data');
2444
2445 Exception
2446 When MAX_USER_DEF_RES_IDS Then
2447 X_Error_Msg_Data := 'PA_MAX_USER_DEF_RES_IDS';
2448 When Others Then
2449 Raise;
2450
2451 End Update_Children_Data;
2452
2453 End Pa_Rbs_Elements_Pvt;
[Home] [Help]