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,
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 P_Level_Code IN VARCHAR2 default null,--15834912
13 P_COST_CODE IN VARCHAR2 default null,--15834912
14 P_COST_CODE_NAME IN VARCHAR2 default null,--16430696
15 X_RBS_Element_id OUT NOCOPY Number,
16 X_Error_Msg_Data OUT NOCOPY Varchar2)
17
18 Is
19
20 l_rbs_header_id NUMBER;
21
22 Begin
23
24 Pa_Debug.G_Stage := 'Entering Process_Rbs_Elements() Pvt.';
25 Pa_Debug.TrackPath('ADD','Process_Rbs_Elements Pvt');
26
27 If P_Process_Type = 'D' Then
28
29 Pa_Debug.G_Stage := 'Call DeleteRbsElement() procedure.';
30
31 -- We can delete an element in an RBS from the working version
32 -- only if none of the previous RBS versions are used in any
33 -- allocations rules. If the version is used, we prevent the
34 -- delete.
35 -- IMP : We do not check at element level to check whether
36 -- it's used for allocations. We prevent any and every deletion,
37 -- if the any element of the previous RBS version is used.
38
39 SELECT rbs_header_id
40 INTO l_rbs_header_id
41 FROM pa_rbs_versions_b
42 WHERE rbs_version_id = p_rbs_version_id;
43
44 IF PA_ALLOC_UTILS.IS_RBS_IN_RULES
45 (p_rbs_id => l_rbs_header_id) = 'Y'
46 THEN
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
56 X_RBS_Element_id := null;
57
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,
67 P_Resource_Source_Id => P_Resource_Source_Id,
68 P_Order_Number => P_Order_Number,
69 P_Level_code => P_Level_code,--15834912
70 P_Cost_code => P_Cost_code,--15834912
71 P_Cost_code_Name => P_Cost_code_Name,--16430696
72 X_Error_Msg_Data => X_Error_Msg_Data);
73
74
75 X_RBS_Element_id := P_Parent_Element_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,
85 P_Resource_Source_Id => P_Resource_Source_Id,
86 P_Order_Number => P_Order_Number,
87 P_Level_code => P_Level_code, --15834912
88 P_Cost_code => P_Cost_code,--15834912
89 P_Cost_code_Name => P_Cost_code_Name,--16430696
90 X_RBS_Element_id => X_RBS_Element_id,
91 X_Error_Msg_Data => X_Error_Msg_Data);
92
93 End If;
94
95 Pa_Debug.G_Stage := 'Leaving Process_Rbs_Elements() Pvt procedure.';
96 Pa_Debug.TrackPath('STRIP','Process_Rbs_Elements Pvt');
97
98
99 Exception
100 When Others Then
101 Raise;
102
103 End Process_RBS_Element;
104
105 PROCEDURE DeleteRbsElement(
106 P_RBS_Version_Id IN Number,
107 P_Element_Id IN Number,
108 X_Error_Msg_Data OUT NOCOPY Varchar2)
109
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,
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;
136 -- Bug 4146317 changes - make sure outline number is still in sync
137 l_parent_id NUMBER := NULL;
138 l_parent_outline_number VARCHAR2(240);
139 l_upd_rbs_id NUMBER := NULL;
140 l_upd_outline_number VARCHAR2(240);
141 l_del_outline_number VARCHAR2(240);
142
143 l_new_last NUMBER := NULL;
144 l_new_outline VARCHAR2(240);
145 l_child_id NUMBER := NULL;
146
147 BEGIN
148
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
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');
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
170
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
180 Close c1;
181
182 Else -- The element does not exist
183
184 Pa_Debug.G_Stage := 'Could not find the element/node to delete.';
185 Raise No_Data_Found;
186
187 END IF;
188
189 -- After the element is deleted, update outline numbers which need
190 -- to be updated.
191 -- Select all the siblings which have outline number "greater"
192 -- than the element being deleted.
193
194 OPEN get_later_sibs(p_rbs_version_id => p_rbs_version_id,
195 p_del_outline_number => l_del_outline_number,
196 p_parent_id => l_parent_id);
197 LOOP
198
199 FETCH get_later_sibs INTO l_upd_rbs_id, l_upd_outline_number;
200 EXIT WHEN get_later_sibs%NOTFOUND;
201 -- decrement the last digit of the outline number by 1
202 IF l_parent_outline_number <> '0' THEN
203 l_new_last := to_number(replace(l_upd_outline_number,
204 l_parent_outline_number || '.')) - 1;
205 -- create the new outline number using the parent's
206 l_new_outline := l_parent_outline_number || '.' || to_char(l_new_last);
207 ELSE
208 l_new_last := to_number(l_upd_outline_number) - 1;
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
218 -- with the new prefix.
219 OPEN get_sib_children(P_Rbs_Elem_Id => l_upd_rbs_id);
220 LOOP
221
222 FETCH get_sib_children INTO l_child_id;
223 EXIT WHEN get_sib_children%NOTFOUND;
224
225 -- this update is complicated because say you delete outline number 2
226 -- then you need to update 3 to 2 and replace 3 in all the children
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 || '.') ||
236 substr(outline_number, length(l_upd_outline_number) + 2)
237 WHERE rbs_element_Id = l_child_id;
238 END LOOP;
239 CLOSE get_sib_children;
240
241 END LOOP;
242
243 CLOSE get_later_sibs;
244
245 Pa_Debug.G_Stage := 'Leaving DeleteRbsElement() procedure.';
246 Pa_Debug.TrackPath('STRIP','DeleteRbsElement');
247
248 EXCEPTION
249 When Others Then
250 Raise;
251
252 END DeleteRbsElement;
253
254 PROCEDURE UpdateExisingRbsElement(
255 P_Rbs_Version_Id IN Number,
256 P_Parent_Element_Id IN Number,
257 P_Rbs_Element_Id IN Number,
258 P_Resource_Type_Id IN Number,
259 P_Resource_Source_Id IN Number,
260 P_Order_Number IN Number,
261 P_Level_Code IN VARCHAR2 default null,--15834912
262 P_COST_CODE IN VARCHAR2 default null,--15834912
263 P_COST_CODE_NAME IN VARCHAR2 default null,--16430696
264 X_Error_Msg_Data OUT NOCOPY Varchar2)
265
266 IS
267
268 l_Person_Id Number := Null;
269 l_Job_Id Number := Null;
270 l_Organization_Id Number := Null;
271 l_Exp_Type_Id Number := Null;
272 l_Event_Type_Id Number := Null;
273 l_Exp_Cat_Id Number := Null;
274 l_Rev_Cat_Id Number := Null;
275 l_Inv_Item_Id Number := Null;
276 l_Item_Cat_Id Number := Null;
277 l_BOM_Labor_Id Number := Null;
278 l_BOM_Equip_Id Number := Null;
279 l_Non_Labor_Res_Id Number := Null;
280 l_Role_Id Number := Null;
281 l_Person_Type_Id Number := Null;
282 l_Res_Class_Id Number := Null;
283 l_Supplier_Id Number := Null;
284 l_Rbs_Level Number := Null;
285 l_Rule_Flag Varchar2(1) := Null;
286 l_Order_Number Number := Null;
287 l_Rbs_Element_Name_Id Number := Null;
288 l_Element_Identifier Number := Null;
289 l_Outline_Number Varchar2(240) := Null;
290 l_User_Def_Custom1_Id Number := Null;
291 l_User_Def_Custom2_Id Number := Null;
292 l_User_Def_Custom3_Id Number := Null;
293 l_User_Def_Custom4_Id Number := Null;
294 l_User_Def_Custom5_Id Number := Null;
295 l_Mode Varchar2(1) := 'U';
296 l_Element_Id Number;
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
306
307 Pa_Debug.G_Stage := 'Entering UpdateExisingRbsElement().';
308 Pa_Debug.TrackPath('ADD','UpdateExisingRbsElement');
309
310
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
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,
325 P_Resource_Type_Id => P_Resource_Type_Id,
326 P_Resource_Source_Id => P_Resource_Source_Id,
327 P_Order_Number => P_Order_Number,
328 X_Person_Id => l_Person_Id,
329 X_Job_Id => l_Job_Id,
330 X_Organization_Id => l_Organization_Id,
331 X_Exp_Type_Id => l_Exp_Type_Id,
332 X_Event_Type_Id => l_Event_Type_Id,
333 X_Exp_Cat_Id => l_Exp_Cat_Id,
334 X_Rev_Cat_Id => l_Rev_Cat_Id,
335 X_Inv_Item_Id => l_Inv_Item_Id,
336 X_Item_Cat_Id => l_Item_Cat_Id,
337 X_BOM_Labor_Id => l_BOM_Labor_Id,
338 X_BOM_Equip_Id => l_BOM_Equip_Id,
339 X_Non_Labor_Res_Id => l_Non_Labor_Res_Id,
340 X_Role_Id => l_Role_Id,
341 X_Person_Type_Id => l_Person_Type_Id,
342 X_User_Def_Custom1_Id => l_User_Def_Custom1_Id,
343 X_User_Def_Custom2_Id => l_User_Def_Custom2_Id,
344 X_User_Def_Custom3_Id => l_User_Def_Custom3_Id,
345 X_User_Def_Custom4_Id => l_User_Def_Custom4_Id,
346 X_User_Def_Custom5_Id => l_User_Def_Custom5_Id,
347 X_Res_Class_Id => l_Res_Class_Id,
348 X_Supplier_Id => l_Supplier_Id,
349 X_Rbs_Level => l_Rbs_Level,
350 X_Rule_Based_Flag => l_Rule_Flag,
351 X_Rbs_Element_Name_Id => l_Rbs_Element_Name_Id,
352 X_Order_Number => l_Order_Number,
353 X_Element_Identifier => l_Element_Identifier,
354 X_Outline_Number => l_outline_number,
355 X_Error_Msg_Data => X_Error_Msg_Data);
356
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,
366 P_Rbs_Version_Id => P_Rbs_Version_Id,
367 P_Outline_Number => l_outline_number,
368 P_Order_Number => l_Order_Number,
369 P_Resource_Type_Id => P_Resource_Type_Id,
370 P_Resource_Source_Id => P_Resource_Source_Id,
371 P_Person_Id => l_Person_Id,
372 P_Job_Id => l_Job_Id,
373 P_Organization_Id => l_Organization_Id,
374 P_Expenditure_Type_Id => l_Exp_Type_Id,
375 P_Event_Type_Id => l_Event_Type_Id,
376 P_Expenditure_Category_Id => l_Exp_Cat_Id,
377 P_Revenue_Category_Id => l_Rev_Cat_Id,
378 P_Inventory_Item_Id => l_Inv_Item_Id,
379 P_Item_Category_Id => l_Item_Cat_Id,
380 P_BOM_Labor_Id => l_BOM_Labor_Id,
381 P_BOM_Equipment_Id => l_BOM_Equip_Id,
382 P_Non_Labor_Resource_Id => l_Non_Labor_Res_Id,
383 P_Role_Id => l_Role_Id,
384 P_Person_Type_ID => l_Person_Type_Id,
385 P_Resource_Class_Id => l_Res_Class_Id,
386 P_Supplier_Id => l_Supplier_Id,
387 P_Rule_Flag => l_Rule_Flag,
388 P_Parent_Element_Id => P_Parent_Element_Id,
389 P_Rbs_Level => l_Rbs_Level,
390 P_Element_Identifier => l_Element_Identifier,
391 P_User_Created_Flag => 'Y',
392 P_User_Defined_Custom1_Id => l_User_Def_Custom1_Id,
393 P_User_Defined_Custom2_Id => l_User_Def_Custom2_Id,
394 P_User_Defined_Custom3_Id => l_User_Def_Custom3_Id,
395 P_User_Defined_Custom4_Id => l_User_Def_Custom4_Id,
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
405
406 If X_Error_Msg_Data Is Null Then
407
408 --Added for Bug fix 3736374
409 --We need to update the value of rule flag for all Child nodes below the current node.
410 --If rule is changed to instance then all child nodes below it should have
411 --rule flag = 'N'.
412 --If instance is changed to rule and it is not below any other instance in the
413 --hierarchy then all rule nodes below it (that are not under any other instance node)
414 --should have rule flag = 'Y'.
415 If P_Resource_Source_Id <> -1 Then
416
417 --For bug 4047578:perf fix
418 OPEN Read_Element_Id_c;
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;
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
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
448 End If;
449
450
451 End If; -- error returned from ResourceNameCheck() procedure
452
453 Else -- The parent element does not exist
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
463
464 EXCEPTION
465 WHEN OTHERS THEN
466 Raise;
467
468 END UpdateExisingRbsElement;
469
470 Procedure CreateNewRbsElement(
471 P_Rbs_Version_Id IN Number,
472 P_Parent_Element_Id IN Number,
473 P_Rbs_Element_Id IN Number,
474 P_Resource_Type_Id IN Number,
475 P_Resource_Source_Id IN Number,
476 P_Order_Number IN Number,
477 P_Level_Code IN VARCHAR2 default null,--15834912
478 P_COST_CODE IN VARCHAR2 default null,--15834912
479 P_COST_CODE_NAME IN VARCHAR2 default null,--16430696
480 X_RBS_Element_id OUT NOCOPY Number,
481 X_Error_Msg_Data OUT NOCOPY Varchar2)
482
483 Is
484
485 l_Person_Id Number := Null;
486 l_Job_Id Number := Null;
487 l_Organization_Id Number := Null;
488 l_Exp_Type_Id Number := Null;
489 l_Event_Type_Id Number := Null;
490 l_Exp_Cat_Id Number := Null;
491 l_Rev_Cat_Id Number := Null;
492 l_Inv_Item_Id Number := Null;
493 l_Item_Cat_Id Number := Null;
494 l_BOM_Labor_Id Number := Null;
495 l_BOM_Equip_Id Number := Null;
496 l_Non_Labor_Res_Id Number := Null;
497 l_Role_Id Number := Null;
498 l_Person_Type_Id Number := Null;
499 l_Res_Class_Id Number := Null;
500 l_Supplier_Id Number := Null;
501 l_Rule_Flag Varchar2(1) := Null;
502 l_Order_Number Number := Null;
503 l_Rbs_Element_Name_Id Number := Null;
504 l_Rbs_Element_Id Number := Null;
505 l_Rbs_Level Number := Null;
506 l_Element_Identifier Number := Null;
507 l_Outline_Number Varchar2(240) := Null;
508 l_User_Def_Custom1_Id Number := Null;
509 l_User_Def_Custom2_Id Number := Null;
510 l_User_Def_Custom3_Id Number := Null;
511 l_User_Def_Custom4_Id Number := Null;
512 l_User_Def_Custom5_Id Number := Null;
513 l_Mode Varchar2(1) := 'A';
514
515 Begin
516
517 Pa_Debug.G_Stage := 'Entering CreateNewRbsElement().';
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,
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,
530 P_Resource_Type_Id => P_Resource_Type_Id,
531 P_Resource_Source_Id => P_Resource_Source_Id,
532 P_Order_Number => P_Order_Number,
533 X_Person_Id => l_Person_Id,
534 X_Job_Id => l_Job_Id,
535 X_Organization_Id => l_Organization_Id,
536 X_Exp_Type_Id => l_Exp_Type_Id,
537 X_Event_Type_Id => l_Event_Type_Id,
538 X_Exp_Cat_Id => l_Exp_Cat_Id,
539 X_Rev_Cat_Id => l_Rev_Cat_Id,
540 X_Inv_Item_Id => l_Inv_Item_Id,
541 X_Item_Cat_Id => l_Item_Cat_Id,
542 X_BOM_Labor_Id => l_BOM_Labor_Id,
543 X_BOM_Equip_Id => l_BOM_Equip_Id,
544 X_Non_Labor_Res_Id => l_Non_Labor_Res_Id,
545 X_Role_Id => l_Role_Id,
546 X_Person_Type_Id => l_Person_Type_Id,
547 X_User_Def_Custom1_Id => l_User_Def_Custom1_Id,
548 X_User_Def_Custom2_Id => l_User_Def_Custom2_Id,
549 X_User_Def_Custom3_Id => l_User_Def_Custom3_Id,
550 X_User_Def_Custom4_Id => l_User_Def_Custom4_Id,
551 X_User_Def_Custom5_Id => l_User_Def_Custom5_Id,
552 X_Res_Class_Id => l_Res_Class_Id,
553 X_Supplier_Id => l_Supplier_Id,
554 X_Rbs_Level => l_Rbs_Level,
555 X_Rule_Based_Flag => l_Rule_Flag,
556 X_Rbs_Element_Name_Id => l_Rbs_Element_Name_Id,
557 X_Order_Number => l_Order_Number,
558 X_Element_Identifier => l_Element_Identifier,
559 X_Outline_Number => l_Outline_Number,
560 X_Error_Msg_Data => X_Error_Msg_Data);
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,
570 P_Order_Number => l_Order_Number,
571 P_Resource_Type_Id => P_Resource_Type_Id,
572 P_Resource_Source_Id => P_Resource_Source_Id,
573 P_Person_Id => l_Person_Id,
574 P_Job_Id => l_Job_Id,
575 P_Organization_Id => l_Organization_Id,
576 P_Expenditure_Type_Id => l_Exp_Type_Id,
577 P_Event_Type_Id => l_Event_Type_Id,
578 P_Expenditure_Category_Id => l_Exp_Cat_Id,
579 P_Revenue_Category_Id => l_Rev_Cat_Id,
580 P_Inventory_Item_Id => l_Inv_Item_Id,
581 P_Item_Category_Id => l_Item_Cat_Id,
582 P_BOM_Labor_Id => l_BOM_Labor_Id,
583 P_BOM_Equipment_Id => l_BOM_Equip_Id,
584 P_Non_Labor_Resource_Id => l_Non_Labor_Res_Id,
585 P_Role_Id => l_Role_Id,
586 P_Person_Type_Id => l_Person_Type_Id,
587 P_Resource_Class_Id => l_Res_Class_Id,
588 P_Supplier_Id => l_Supplier_Id,
589 P_Rule_Flag => l_Rule_Flag,
590 P_Parent_Element_Id => P_Parent_Element_Id,
591 P_Rbs_Level => l_Rbs_Level,
592 P_Element_Identifier => l_Element_Identifier,
593 P_User_Created_Flag => 'Y',
594 P_User_Defined_Custom1_Id => l_User_Def_Custom1_Id,
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,
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,
608 X_Error_Msg_Data => X_Error_Msg_Data);
609
610 End If; -- error returned from ResourceNameCheck() procedure
611
612 Else -- The parent element does not exist
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
622 Exception
623 When Others Then
624 Raise;
625
626 End CreateNewRbsElement;
627
628 Procedure ValidateAndBuildElement(
629 P_Mode IN Varchar2,
630 P_Rbs_Version_Id IN Number,
631 P_Parent_Element_Id IN Number,
632 P_Rbs_Element_Id IN Number,
633 P_Resource_Type_Id IN Number,
634 P_Resource_Source_Id IN Number,
635 P_Order_Number IN Number,
636 X_Person_Id OUT NOCOPY Number,
637 X_Job_Id OUT NOCOPY Number,
638 X_Organization_Id OUT NOCOPY Number,
639 X_Exp_Type_Id OUT NOCOPY Number,
640 X_Event_Type_Id OUT NOCOPY Number,
641 X_Exp_Cat_Id OUT NOCOPY Number,
642 X_Rev_Cat_Id OUT NOCOPY Number,
643 X_Inv_Item_Id OUT NOCOPY Number,
644 X_Item_Cat_Id OUT NOCOPY Number,
645 X_BOM_Labor_Id OUT NOCOPY Number,
646 X_BOM_Equip_Id OUT NOCOPY Number,
647 X_Non_Labor_Res_Id OUT NOCOPY Number,
648 X_Role_Id OUT NOCOPY Number,
649 X_Person_Type_Id OUT NOCOPY Number,
650 X_User_Def_Custom1_Id OUT NOCOPY Number,
651 X_User_Def_Custom2_Id OUT NOCOPY Number,
652 X_User_Def_Custom3_Id OUT NOCOPY Number,
653 X_User_Def_Custom4_Id OUT NOCOPY Number,
654 X_User_Def_Custom5_Id OUT NOCOPY Number,
655 X_Res_Class_Id OUT NOCOPY Number,
656 X_Supplier_Id OUT NOCOPY Number,
657 X_Rbs_Level OUT NOCOPY Number,
658 X_Rule_Based_Flag OUT NOCOPY Varchar2,
659 X_Rbs_Element_Name_Id OUT NOCOPY Number,
660 X_Order_Number OUT NOCOPY Number,
661 X_Element_Identifier OUT NOCOPY Number,
662 X_Outline_Number OUT NOCOPY Varchar2,
663 X_Error_Msg_Data OUT NOCOPY Varchar2)
664
665 Is
666
667 CURSOR chk_element_exists(p_resource_type_id IN NUMBER,
668 p_resource_source_id IN NUMBER,
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)
678 AND rbs_level = P_rbs_level;
679
680 CURSOR chk_element_diff_level(p_resource_type_id IN NUMBER,
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);
690
691 l_ele_exists VARCHAR2(1) := 'N';
692 l_unique_branch VARCHAR2(1) := 'N';
693 l_exists_element_id NUMBER := NULL;
694
695 l_Resource_Type Varchar2(30) := Null;
696 MAX_USER_DEF_RES_IDS Exception;
697 MAX_RBS_LEVELS Exception;
698 NON_UNIQUE_BRANCH Exception;
699 GET_ELEMENT_NAME_ID_FAILED Exception;
700 CANNOT_CREATE_RULES Exception;
701 l_Dummy_Error_Status Varchar2(1) := Null;
702 l_Dummy_Error_Count Number := Null;
703 l_number_of_peers NUMBER;
704 l_o_number NUMBER;
705 l_old_resource_type_id NUMBER := null;
706 l_old_resource_source_id NUMBER := null;
707 l_use_for_alloc_flag VARCHAR2(1) := Null; --Bug 3725965
708
709 Begin
710 -- hr_utility.trace_on(NULL, 'RMRBS');
711 -- hr_utility.trace('******** START ******* ');
712 --dbms_output.put_line('******** START *******');
713
714 Pa_Debug.G_Stage := 'Entering ValidateAndBuildElement().';
715 Pa_Debug.TrackPath('ADD','ValidateAndBuildElement');
716
717 IF p_mode = 'U' THEN
718 -- Get the old Resource Type Id and Resource Source Id for
719 -- the element.
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.';
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,
735 P_Old_Resource_Type_Id => l_Old_Resource_Type_Id,
736 P_Old_Resource_Source_Id => l_Old_Resource_Source_Id,
737 P_Resource_Type_Id => P_Resource_Type_Id,
738 P_Resource_Source_Id => P_Resource_Source_Id,
739 X_Resource_Type => l_Resource_Type,
740 X_Error_Msg_Data => X_Error_Msg_Data);
741
742 --dbms_output.put_line('after ValidateRbsElement');
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,
752 X_Exp_Type_Id => X_Exp_Type_Id,
753 X_Event_Type_Id => X_Event_Type_Id,
754 X_Exp_Cat_Id => X_Exp_Cat_Id,
755 X_Rev_Cat_Id => X_Rev_Cat_Id,
756 X_Inv_Item_Id => X_Inv_Item_Id,
757 X_Item_Cat_Id => X_Item_Cat_Id,
758 X_BOM_Labor_Id => X_BOM_Labor_Id,
759 X_BOM_Equip_Id => X_BOM_Equip_Id,
760 X_Non_Labor_Res_Id => X_Non_Labor_Res_Id,
761 X_Role_Id => X_Role_Id,
762 X_Person_Type_Id => X_Person_Type_Id,
763 X_User_Def_Custom1_Id => X_User_Def_Custom1_Id,
764 X_User_Def_Custom2_Id => X_User_Def_Custom2_Id,
765 X_User_Def_Custom3_Id => X_User_Def_Custom3_Id,
766 X_User_Def_Custom4_Id => X_User_Def_Custom4_Id,
767 X_User_Def_Custom5_Id => X_User_Def_Custom5_Id,
768 X_Res_Class_Id => X_Res_Class_Id,
769 X_Supplier_Id => X_Supplier_Id,
770 X_Rbs_Level => X_Rbs_Level,
771 X_Outline_Number => X_Outline_Number);
772 --dbms_output.put_line('after GetParentRbsData');
773
774 If l_Resource_Type = 'BOM_LABOR' Then
775
776 Pa_Debug.G_Stage := 'Assign the resource source id to BOM Labor.';
777 X_BOM_Labor_Id := P_Resource_Source_Id;
778
779 Elsif l_Resource_Type = 'BOM_EQUIPMENT' Then
780
781 Pa_Debug.G_Stage := 'Assign the resource source id to BOM Equipment.';
782 X_BOM_Equip_Id := P_Resource_Source_Id;
783
784 Elsif l_Resource_Type = 'NAMED_PERSON' Then
785
786 Pa_Debug.G_Stage := 'Assign the resource source id to Named Person.';
787 X_Person_Id := P_Resource_Source_Id;
788
789 Elsif l_Resource_Type = 'EVENT_TYPE' Then
790
791 Pa_Debug.G_Stage := 'Assign the resource source id to Event Type.';
792 X_Event_Type_Id := P_Resource_Source_Id;
793
794 Elsif l_Resource_Type = 'EXPENDITURE_CATEGORY' Then
795
796 Pa_Debug.G_Stage := 'Assign the resource source id to Expenditure Category.';
797 X_Exp_Cat_Id := P_Resource_Source_Id;
798
799 Elsif l_Resource_Type = 'EXPENDITURE_TYPE' Then
800
801 Pa_Debug.G_Stage := 'Assign the resource source id to Expenditure Type.';
802 X_Exp_Type_Id := P_Resource_Source_Id;
803
804 Elsif l_Resource_Type = 'ITEM_CATEGORY' Then
805
806 Pa_Debug.G_Stage := 'Assign the resource source id to Item Category.';
807 X_Item_Cat_Id := P_Resource_Source_Id;
808
809 Elsif l_Resource_Type = 'INVENTORY_ITEM' Then
810
811 Pa_Debug.G_Stage := 'Assign the resource source id to Inventory Item.';
812 X_Inv_Item_Id := P_Resource_Source_Id;
813
814 Elsif l_Resource_Type = 'JOB' Then
815
816 Pa_Debug.G_Stage := 'Assign the resource source id to Job.';
817 X_Job_Id := P_Resource_Source_Id;
818
819 Elsif l_Resource_Type = 'ORGANIZATION' Then
820
821 Pa_Debug.G_Stage := 'Assign the resource source id to Organization.';
822 X_Organization_Id := P_Resource_Source_Id;
823
824 Elsif l_Resource_Type = 'PERSON_TYPE' Then
825
826 Pa_Debug.G_Stage := 'Assign the resource source id to Person Type.';
827 X_Person_Type_Id := P_Resource_Source_Id;
828
829 Elsif l_Resource_Type = 'NON_LABOR_RESOURCE' Then
830
831 Pa_Debug.G_Stage := 'Assign the resource source id to Non Labor Resource.';
832 X_Non_Labor_Res_Id := P_Resource_Source_Id;
833
834 Elsif l_Resource_Type = 'RESOURCE_CLASS' Then
835
836 Pa_Debug.G_Stage := 'Assign the resource source id to Resource Class.';
837 X_Res_Class_Id := P_Resource_Source_Id;
838
839 Elsif l_Resource_Type = 'REVENUE_CATEGORY' Then
840
841 Pa_Debug.G_Stage := 'Assign the resource source id to Revenue Category.';
842 X_Rev_Cat_Id := P_Resource_Source_Id;
843
844 Elsif l_Resource_Type = 'ROLE' Then
845
846 Pa_Debug.G_Stage := 'Assign the resource source id to Role.';
847 X_Role_Id := P_Resource_Source_Id;
848
849 Elsif l_Resource_Type = 'SUPPLIER' Then
850
851 Pa_Debug.G_Stage := 'Assign the resource source id to Supplier.';
852 X_Supplier_Id := P_Resource_Source_Id;
853
854 Elsif l_Resource_Type = 'USER_DEFINED' Then
855
856 Pa_Debug.G_Stage := 'Assign the resource source id to User Defined.';
857 If X_User_Def_Custom1_Id Is Null Then
858
859 Pa_Debug.G_Stage := 'Assign the resource source id to User Defined 1.';
860 X_User_Def_Custom1_Id := P_Resource_Source_Id;
861
862 ElsIf X_User_Def_Custom2_Id Is Null Then
863
864 Pa_Debug.G_Stage := 'Assign the resource source id to User Defined 2.';
865 X_User_Def_Custom2_Id := P_Resource_Source_Id;
866
867 ElsIf X_User_Def_Custom3_Id Is Null Then
868
869 Pa_Debug.G_Stage := 'Assign the resource source id to User Defined 3.';
870 X_User_Def_Custom3_Id := P_Resource_Source_Id;
871
872 ElsIf X_User_Def_Custom4_Id Is Null Then
873
874 Pa_Debug.G_Stage := 'Assign the resource source id to User Defined 4.';
875 X_User_Def_Custom4_Id := P_Resource_Source_Id;
876
877 ElsIf X_User_Def_Custom5_Id Is Null Then
878
879 Pa_Debug.G_Stage := 'Assign the resource source id to User Defined 5.';
880 X_User_Def_Custom5_Id := P_Resource_Source_Id;
881
882 Else
883
884 Pa_Debug.G_Stage := 'All user defined resources field populated.';
885 Raise MAX_USER_DEF_RES_IDS;
886
887 End If;
888
889 End If;
890
891 -- set the value for the rule flag
892 --Modified for bug fix 3736374.
893 --Rule flag is set 'N' for an instance based node and all RBS nodes
894 --(both rule and instance-based) below it.
895 --Top most node of an RBS has rule flag = 'N'.
896 Pa_Debug.G_Stage := 'Determine the rule based flag.';
897 --dbms_output.put_line('get rule based flag');
898 Select
899 Decode(r.parent_element_id,null,Decode(P_Resource_Source_Id,-1,'Y','N'),
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');
909 -- Need to increment the Rbs Level for the child we got
910 -- the value from the parent.
911 Pa_Debug.G_Stage := 'Increment the rbs level based on the parent level.';
912 X_Rbs_level := X_Rbs_Level + 1;
913
914 IF X_Rbs_Level > 10 THEN
915 -- We do not allow more then 10 Levels. So error out.
916 RAISE MAX_RBS_LEVELS;
917 END IF;
918
919 PA_DEBUG.G_Stage := 'Check if element is variation of same combination in different combination - Job-Org vs Org-Job.';
920 -- Fix bugs 3909551 and 3882731 by checking to see if the new/upd
921 -- element already exists in the RBS; if so, then check the entire
922 -- branch (except UDR) and ensure they are not the same. This will
923 -- prevent creation of DBA-Cons East and Cons East-DBA, and the
924 -- creation of the same element under different UDR nodes.
925
926 -- First, check whether element exists already:
927 -- hr_utility.trace('P_Resource_Type_Id IS : ' || P_Resource_Type_Id);
928 -- hr_utility.trace('P_Resource_Source_Id IS : ' || P_Resource_Source_Id);
929 -- hr_utility.trace('P_Rbs_Element_Id IS : ' || P_Rbs_Element_Id);
930 --dbms_output.put_line('before chk_element_exists');
931 l_unique_branch := 'Y';
932 OPEN chk_element_exists(P_Resource_Type_Id => P_Resource_Type_Id,
933 P_Resource_Source_Id => P_Resource_Source_Id,
934 P_Rbs_Element_Id => P_Rbs_Element_Id,
935 P_rbs_level => X_Rbs_level);
936
937 LOOP
938 FETCH chk_element_exists Into l_ele_exists, l_exists_element_id;
939 -- hr_utility.trace('IN LOOP');
940 -- hr_utility.trace('l_ele_exists IS : ' || l_ele_exists);
941 -- hr_utility.trace('l_exists_element_id IS : ' || l_exists_element_id);
942 l_unique_branch := 'Y';
943 EXIT WHEN chk_element_exists%NOTFOUND OR
944 l_unique_branch = 'N';
945
946 -- If element exists, then check uniqueness on branch:
947 PA_DEBUG.G_Stage := 'Check if element is unique combination - validation check 10.';
948
949 IF l_ele_exists = 'Y' THEN
950 -- hr_utility.trace('ELE EXISTS');
951 BEGIN
952 -- hr_utility.trace('x_organization_id IS : ' || x_organization_id);
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)
962 AND nvl(supplier_id, -99) = nvl(x_supplier_id, -99)
963 AND nvl(expenditure_type_id, -99) = nvl(x_exp_type_id, -99)
964 AND nvl(event_type_id, -99) = nvl(x_event_type_id, -99)
965 AND nvl(revenue_category_id, -99) = nvl(x_rev_cat_id, -99)
966 AND nvl(inventory_item_id, -99) = nvl(x_inv_item_id, -99)
967 AND nvl(item_category_id, -99) = nvl(x_item_cat_id, -99)
968 AND nvl(bom_labor_id, -99) = nvl(x_bom_labor_id, -99)
969 AND nvl(bom_equipment_id, -99) = nvl(x_bom_equip_id, -99)
970 AND nvl(person_type_id, -99) = nvl(x_person_type_id, -99)
971 AND nvl(resource_class_id, -99) = nvl(x_res_class_id, -99)
972 AND nvl(role_id, -99) = nvl(x_role_id, -99)
973 AND nvl(non_labor_resource_id, -99) = nvl(x_non_labor_res_id, -99)
974 AND nvl(expenditure_category_id, -99) = nvl(x_exp_cat_id,-99)
975 AND nvl(User_Defined_Custom1_Id, -99) = nvl(X_User_Def_Custom1_Id, -99)
976 AND nvl(User_Defined_Custom2_Id, -99) = nvl(X_User_Def_Custom2_Id, -99)
977 AND nvl(User_Defined_Custom3_Id, -99) = nvl(X_User_Def_Custom3_Id, -99)
978 AND nvl(User_Defined_Custom4_Id, -99) = nvl(X_User_Def_Custom4_Id, -99)
979 AND nvl(User_Defined_Custom5_Id, -99) = nvl(X_User_Def_Custom5_Id, -99);
980 EXCEPTION
981 WHEN NO_DATA_FOUND THEN
982 l_unique_branch := 'Y';
983 WHEN OTHERS THEN
984 l_unique_branch := 'N';
985 END;
986
987 -- hr_utility.trace('l_unique_branch IS : ' || l_unique_branch);
988 IF l_unique_branch = 'N' THEN
989 -- hr_utility.trace('RAISE ERROR');
990 RAISE NON_UNIQUE_BRANCH;
991 END IF;
992
993 END IF;
994 END LOOP;
995 CLOSE chk_element_exists;
996
997 -- Bug - Prevent creation of identical branches.
998 -- e.g. disallow DBA-CW and CW-DBA - the actual nodes
999 -- are different but the branches are the same.
1000 -- First check if the node being added exists in RBS - then
1001 -- only does the possiblity of the same branch arise.
1002
1003 --dbms_output.put_line('before chk_element_diff_level');
1004 l_unique_branch := 'Y';
1005 OPEN chk_element_diff_level(P_Resource_Type_Id => P_Resource_Type_Id,
1006 P_Resource_Source_Id => P_Resource_Source_Id,
1007 P_Rbs_Element_Id => P_Rbs_Element_Id);
1008
1009 FETCH chk_element_diff_level Into l_ele_exists;
1010 -- hr_utility.trace('IN Identical branch check flow');
1011 -- hr_utility.trace('l_ele_exists IS : ' || l_ele_exists);
1012 -- hr_utility.trace('l_exists_element_id IS : ' || l_exists_element_id);
1013 IF chk_element_diff_level%FOUND THEN
1014 l_ele_exists := 'Y';
1015 ELSE
1016 l_ele_exists := 'N';
1017 END IF;
1018
1019 CLOSE chk_element_diff_level;
1020
1021 -- Check whether identical branch exists
1022 --dbms_output.put_line('l_ele_exists IS ' || l_ele_exists);
1023 IF l_ele_exists = 'Y' THEN
1024 -- hr_utility.trace('identical Branch exists Check');
1025 BEGIN
1026 -- hr_utility.trace('x_organization_id IS : ' || x_organization_id);
1027 -- hr_utility.trace('x_job_id IS : ' || x_job_id);
1028 -- hr_utility.trace('x_person_id IS : ' || x_person_id);
1029 --dbms_output.put_line('before iden branch check');
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)
1039 AND nvl(organization_id, -99) = nvl(x_organization_id, -99)
1040 AND nvl(job_id, -99) = nvl(x_job_id, -99)
1041 AND nvl(supplier_id, -99) = nvl(x_supplier_id, -99)
1042 AND nvl(expenditure_type_id, -99) = nvl(x_exp_type_id, -99)
1043 AND nvl(event_type_id, -99) = nvl(x_event_type_id, -99)
1044 AND nvl(revenue_category_id, -99) = nvl(x_rev_cat_id, -99)
1045 AND nvl(inventory_item_id, -99) = nvl(x_inv_item_id, -99)
1046 AND nvl(item_category_id, -99) = nvl(x_item_cat_id, -99)
1047 AND nvl(bom_labor_id, -99) = nvl(x_bom_labor_id, -99)
1048 AND nvl(bom_equipment_id, -99) = nvl(x_bom_equip_id, -99)
1049 AND nvl(person_type_id, -99) = nvl(x_person_type_id, -99)
1050 AND nvl(resource_class_id, -99) = nvl(x_res_class_id, -99)
1051 AND nvl(role_id, -99) = nvl(x_role_id, -99)
1052 AND nvl(non_labor_resource_id, -99) = nvl(x_non_labor_res_id, -99)
1053 AND nvl(expenditure_category_id, -99) = nvl(x_exp_cat_id,-99)
1054 AND nvl(User_Defined_Custom1_Id, -99) = nvl(X_User_Def_Custom1_Id, -99)
1055 AND nvl(User_Defined_Custom2_Id, -99) = nvl(X_User_Def_Custom2_Id, -99)
1056 AND nvl(User_Defined_Custom3_Id, -99) = nvl(X_User_Def_Custom3_Id, -99)
1057 AND nvl(User_Defined_Custom4_Id, -99) = nvl(X_User_Def_Custom4_Id, -99)
1058 AND nvl(User_Defined_Custom5_Id, -99) = nvl(X_User_Def_Custom5_Id, -99);
1059 EXCEPTION
1060 WHEN NO_DATA_FOUND THEN
1061 l_unique_branch := 'Y';
1062 WHEN OTHERS THEN
1063 l_unique_branch := 'N';
1064 END;
1065 --dbms_output.put_line('after iden branch check');
1066 --dbms_output.put_line('l_unique_branch IS ' || l_unique_branch);
1067
1068 -- hr_utility.trace('l_unique_branch IS : ' || l_unique_branch);
1069 IF l_unique_branch = 'N' THEN
1070 -- hr_utility.trace('RAISE ERROR');
1071 RAISE NON_UNIQUE_BRANCH;
1072 END IF;
1073
1074 END IF;
1075 --Added for bug fix 3725965.
1076
1077 Select
1078 use_for_alloc_flag
1079 Into
1080 l_use_for_alloc_flag
1081 From
1082 pa_rbs_headers_vl h,
1083 pa_rbs_versions_vl v
1084 where
1085 v.rbs_version_id=P_Rbs_Version_Id
1086 And
1087 h.rbs_header_id=v.rbs_header_id;
1088
1089
1090 If l_use_for_alloc_flag='Y' Then
1091 If P_RESOURCE_SOURCE_ID = -1 Then --For bug 3803213.
1092 --If X_Rule_Based_Flag ='Y' Then --Commented for bug fix 3803213.
1093 Raise CANNOT_CREATE_RULES;
1094 End If;
1095 End If; --End of bug fix 3725965
1096
1097 IF P_Order_Number = FND_API.G_MISS_NUM
1098 THEN
1099 X_Order_Number := null;
1100 ELSE
1101 X_Order_Number := P_Order_Number;
1102 END IF;
1103
1104 IF X_Order_Number = -1 THEN
1105 X_Order_Number := null;
1106 END IF;
1107
1108 -- The procedure Pa_Rbs_Utils.Populate_RBS_Element_Name() handles
1109 -- returning the rbs_element_name_id and creating rbs element name
1110 -- records if needed.
1111 Pa_Debug.G_Stage := 'Call Pa_Rbs_Utils.Populate_RBS_Element_Name() procedure.';
1112
1113
1114 Pa_Rbs_Utils.Populate_RBS_Element_Name (
1115 P_Resource_Source_Id => P_Resource_Source_Id,
1116 P_Resource_Type_Id => P_Resource_Type_Id,
1117 X_Rbs_Element_Name_Id => X_Rbs_Element_Name_Id,
1118 X_Return_Status => l_Dummy_Error_Status);
1119
1120
1121 If l_Dummy_Error_Status <> FND_API.G_RET_STS_SUCCESS
1122 Then
1123 Pa_Debug.G_Stage := 'Call to Pa_Rbs_Utils.Populate_RBS_Element_Name() procedure failed.';
1124 Raise GET_ELEMENT_NAME_ID_FAILED;
1125 End If;
1126
1127 -- Get the Element Identifier
1128 Pa_Debug.G_Stage := 'Check if update or add to determine element identifier value.';
1129 IF P_Mode = 'A' THEN
1130
1131 Pa_Debug.G_Stage := 'Get the next available element identifier sequence value for add.';
1132 -- Get the next value in the sequence
1133 Select Pa_Rbs_Element_Identifier_S.NextVal
1134 Into X_Element_Identifier
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
1144 Where Rbs_Element_Id = P_Rbs_Element_Id;
1145
1146 END IF;
1147
1148 l_number_of_peers := 0;
1149
1150
1151 IF p_mode = 'A' THEN
1152
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
1162 l_o_number := 1;
1163 ELSE
1164 l_o_number := l_number_of_peers + 1;
1165 END IF;
1166
1167 IF X_Outline_Number = '0' THEN
1168 X_Outline_Number := l_o_number;
1169 ELSE
1170 X_Outline_Number := X_Outline_Number || '.' || l_o_number;
1171 END IF;
1172 ELSE
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
1182
1183 END IF;
1184
1185 Pa_Debug.G_Stage := 'Leaving ValidateAndBuildElement() procedure.';
1186 Pa_Debug.TrackPath('STRIP','ValidateAndBuildElement');
1187
1188 Exception
1189 When MAX_USER_DEF_RES_IDS Then
1190 X_Error_Msg_Data := 'PA_MAX_USER_DEF_RES_IDS';
1191 When MAX_RBS_LEVELS Then
1192 X_Error_Msg_Data := 'PA_MAX_RBS_LEVELS';
1193 When NON_UNIQUE_BRANCH Then
1194 X_Error_Msg_Data := 'PA_NON_UNIQUE_BRANCH';
1195 When CANNOT_CREATE_RULES Then
1196 X_Error_Msg_Data := 'PA_RBS_CANNOT_CREATE_RULES';
1197 When Others Then
1198 Raise;
1199
1200 End ValidateAndBuildElement;
1201
1202
1203 -- A
1204 -- | |
1205 -- B C
1206 -- | | |
1207 -- E G F
1208
1209 Procedure ValidateRbsElement(
1210 P_Mode IN Varchar2,
1211 P_Rbs_Version_Id IN Number,
1212 P_Parent_Element_Id IN Number,
1213 P_Rbs_Element_Id IN Number,
1214 P_Old_Resource_Type_Id IN Number,
1215 P_Old_Resource_Source_Id IN Number,
1216 P_Resource_Type_Id IN Number,
1217 P_Resource_Source_Id IN Number,
1218 X_Resource_Type OUT NOCOPY Varchar2,
1219 X_Error_Msg_Data OUT NOCOPY Varchar2)
1220
1221 IS
1222
1223 CURSOR c_CheckDupSiblings
1224 (P_RbsVersionId IN Number,
1225 P_ParentId IN Number,
1226 P_ResSourceId IN Number,
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;
1236
1237 l_dummy_count Number := 0;
1238
1239 CURSOR c_CheckExistParentMatch
1240 (P_Res_Type_Id IN Number,
1241 P_Res_Srce_Id IN Number,
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;
1251
1252 CURSOR c_CheckExistChildMatch
1253 (P_Res_Type_Id IN Number,
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;
1263
1264 DUP_SIBLING_RES_RES_TYPE Exception;
1265 INVAL_RES_TYPE Exception;
1266 ELEMENT_USER_DEF_RULE Exception;
1267 INVALID_RESOURCE Exception;
1268 DUP_PARENT_RES_RES_TYPE Exception;
1269 DUP_CHILD_RES_RES_TYPE Exception;
1270 RES_TYPE_RES_NOT_CON_LVLS Exception;
1271 CONS_RES_TYPES_NOT_INSTANCES Exception;
1272 l_Current_level Number;
1273 l_grand_parent_element_id Number;
1274 l_parent_resource_type_id Number;
1275 l_parent_resource_source_id Number;
1276
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
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
1303 SELECT NVL(X.CBS_ENABLED,'N')
1304 FROM PA_RBS_HEADERS_VL X , PA_RBS_VERSIONS_VL Y
1305 WHERE Y.RBS_HEADER_ID = X.RBS_HEADER_ID
1306 AND Y.RBS_VERSION_ID = P_Rbs_Version_Id;
1307
1308 L_CBS_FLAG VARCHAR2(1) :='N';
1309
1310
1311 BEGIN
1312
1313 Pa_Debug.G_Stage := 'Entering ValidateRbsElement().';
1314 Pa_Debug.TrackPath('ADD','ValidateRbsElement');
1315
1316 -- 1. Validate the resource_type
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'
1326 THEN
1327
1328 Pa_Debug.G_Stage := 'Invalid resource type id passed in.';
1329 RAISE INVAL_RES_TYPE;
1330 END IF;
1331
1332 -- If the resource type or the resource source id has not changed from
1333 -- the old values, we do not need to proceeed further.
1334
1335 IF p_old_resource_type_id = p_resource_type_id AND
1336 P_old_Resource_Source_Id = P_Resource_Source_Id THEN
1337 Return;
1338 END IF;
1339
1340
1341 -- 2. If the element is rule based, and resource_type is user-defined, it's an error.
1342 Pa_Debug.G_Stage := 'Determine if rule and user-defined which is not allowed.';
1343 If P_Resource_Source_Id = -1 and X_Resource_Type = 'USER_DEFINED' Then
1344
1345 Pa_Debug.G_Stage := 'Rule and user defined raise user defined error.';
1346 Raise ELEMENT_USER_DEF_RULE;
1347
1348 End If;
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);
1358
1359 If X_Error_Msg_Data is Not Null Then
1360
1361 Pa_Debug.G_Stage := 'The Resource is invalid. Raise user defined error.';
1362 Raise INVALID_RESOURCE;
1363
1364 End If;
1365
1366 -- 4. Validate that the element is not the same as it's siblings.
1367 Pa_Debug.G_Stage := 'Open c_CheckDupSiblings cursor - validation check 4.';
1368 Open c_CheckDupSiblings(P_Rbs_Version_Id,
1369 P_Parent_Element_Id,
1370 P_Resource_Source_Id,
1371 P_Resource_Type_Id);
1372
1373
1374 Fetch c_CheckDupSiblings Into l_dummy_count;
1375 Close c_CheckDupSiblings;
1376
1377 IF l_dummy_count = 0
1378 THEN
1379 Null;
1380 ELSE
1381 Pa_Debug.G_Stage := 'Records found! Close c_CheckDupSiblings cursor. ' ||
1382 'Raise user defined error - validation check 4.';
1383 RAISE DUP_SIBLING_RES_RES_TYPE;
1384 END IF;
1385
1386 -- 5. Validate that the element is not the same as it's one of it's parents.
1387 Open c_CheckExistParentMatch(P_Resource_Type_Id,
1388 P_Resource_Source_Id,
1389 P_Parent_Element_Id);
1390
1391 Fetch c_CheckExistParentMatch Into l_Dummy_Count;
1392 Close c_CheckExistParentMatch;
1393
1394
1395 IF l_dummy_count = 0 THEN
1396 Null;
1397 ELSE
1398 -- Added code for CBS phase 2 16282618
1399 OPEN GET_RBS_HEADER_INFO;
1400 FETCH GET_RBS_HEADER_INFO INTO L_CBS_FLAG;
1401 CLOSE GET_RBS_HEADER_INFO;
1402 IF(L_CBS_FLAG = 'N') THEN --Skipping this validation for CBS
1403 Pa_Debug.G_Stage := 'Raise user defined error - validation check 5.';
1404 Raise DUP_PARENT_RES_RES_TYPE;
1405 END IF;
1406 END IF;
1407
1408 -- 6. Validate that the element is not repeated in the branches below it.
1409 -- This check does not need to be done when adding a element/node. It won't have children
1410 -- to worry about.
1411 Pa_Debug.G_Stage := 'Check if validation is for updating an element - validation check 6.';
1412 If P_Mode = 'U' THEN
1413
1414 Open c_CheckExistChildMatch(P_Resource_Type_Id,P_Resource_Source_Id,P_Rbs_Element_Id);
1415
1416 Fetch c_CheckExistChildMatch Into l_Dummy_Count;
1417 Close c_CheckExistChildMatch;
1418
1419 IF l_dummy_count = 0 THEN
1420 Null;
1421 ELSE
1422 Pa_Debug.G_Stage := 'Raise user defined error - validation check 6.';
1423 Raise DUP_CHILD_RES_RES_TYPE;
1424 END IF;
1425
1426 END IF;
1427
1428 PA_DEBUG.G_Stage := 'Check if element/node is rule based - validation check 7.';
1429
1430 -- 7. Resources of Same Resource Type can be repeated in a branch only
1431 -- in consecutive generations and if both the elements are instance
1432 -- based (not rule based)
1433
1434 /*
1435 Check the Parent's resource type.
1436 i. Check if the parent resource type is same as the current element's
1437 resource type.
1438 a. If resource types are same, check if the current element is
1439 rule based. If yes, error out since when resource type is
1440 repeated, the elements must be instances and not rule.
1441 b. If the resource types are different, check if the current
1442 element's resource type is same as any of it's grandparents.
1443 If yes, error out, since resource type can be repeated only
1444 in consecutive generations.
1445 */
1446
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;
1456 END;
1457
1458 IF l_parent_resource_type_id = p_resource_type_id
1459 THEN
1460 IF p_resource_source_id = -1 OR
1461 l_parent_resource_source_id = -1 THEN
1462 RAISE CONS_RES_TYPES_NOT_INSTANCES;
1463 END IF;
1464
1465 ELSE
1466
1467 -- We need to check for grand parent's resource types only if the
1468 -- parent's resource type is different from the element's resource type.
1469
1470
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
1480 IF l_dummy_count > 0 THEN
1481 RAISE RES_TYPE_RES_NOT_CON_LVLS;
1482 ELSE
1483 -- There are no grand parents with the same
1484 -- resource type.
1485 null;
1486 END IF;
1487
1488 END IF;
1489
1490
1491 -- Check resource type with the children
1492
1493 IF p_mode = 'U' THEN
1494 FOR c1 in children_elements(P_Rbs_Element_Id) LOOP
1495
1496 IF P_Resource_Type_Id = c1.resource_type_id THEN
1497
1498 IF p_resource_source_id = -1 OR
1499 c1.resource_source_id = -1 THEN
1500 RAISE CONS_RES_TYPES_NOT_INSTANCES;
1501 END IF;
1502
1503 ELSE
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
1513
1514 IF l_dummy_count > 0 THEN
1515 RAISE RES_TYPE_RES_NOT_CON_LVLS;
1516 ELSE
1517 -- There are no grand children with the
1518 -- same resource type
1519 null;
1520 END IF;
1521 END IF;
1522 END LOOP;
1523
1524 -- If the element is updated, and the old resource type happened
1525 -- to be same as parent resource type, then we need to check if
1526 -- any child exists with the same resource type. If it does,
1527 -- then we need to error out, since change the resource type of the
1528 -- element will create a break in consecutive resource types.
1529
1530 IF (l_parent_resource_type_id = P_Old_Resource_Type_Id
1531 AND p_resource_type_id <> p_old_resource_type_id)
1532 THEN
1533
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
1543 RAISE RES_TYPE_RES_NOT_CON_LVLS;
1544 ELSE
1545 -- There are no children with the same resource type,
1546 -- so changing the resource type will not create
1547 -- any discontinuity
1548 null;
1549 END IF;
1550
1551 END IF;
1552
1553 END IF; /* p_mode = 'U' */
1554 Pa_Debug.G_Stage := 'Leaving ValidateRbsElement() procedure.';
1555 Pa_Debug.TrackPath('STRIP','ValidateRbsElement');
1556
1557 EXCEPTION
1558 WHEN INVAL_RES_TYPE THEN
1559 X_Error_Msg_Data := 'PA_RBS_INVAL_RES_TYPE';
1560 WHEN ELEMENT_USER_DEF_RULE THEN
1561 X_Error_Msg_Data := 'PA_RBS_ELE_USER_DEF_RULE';
1562 WHEN INVALID_RESOURCE THEN
1563 Null;
1564 WHEN DUP_SIBLING_RES_RES_TYPE THEN
1565 X_Error_Msg_Data := 'PA_RBS_DUP_SIB_RES_RES_TYPE';
1566 WHEN DUP_PARENT_RES_RES_TYPE THEN
1567 X_Error_Msg_Data := 'PA_RBS_DUP_PAR_RES_RES_TYPE';
1568 WHEN DUP_CHILD_RES_RES_TYPE THEN
1569 X_Error_Msg_Data := 'PA_RBS_DUP_CHD_RES_RES_TYPE';
1570 WHEN RES_TYPE_RES_NOT_CON_LVLS THEN
1571 X_Error_Msg_Data := 'PA_RES_TYPE_RES_NOT_CON_LVLS';
1572 WHEN CONS_RES_TYPES_NOT_INSTANCES THEN
1573 X_Error_Msg_Data := 'PA_CONS_RBS_ELE_NOT_INS';
1574 WHEN Others THEN
1575 Raise;
1576
1577 END ValidateRbsElement;
1578
1579 Procedure ValidateResource(
1580 P_Resource_Type_Id IN Number,
1581 P_Resource_Source_Id IN Number,
1582 P_Resource_Type IN Varchar2,
1583 X_Error_Msg_Data OUT NOCOPY Varchar2)
1584
1585 Is
1586
1587 l_Rev_Code Varchar2(30) := Null;
1588 l_Person_Type_Code Varchar2(30) := Null; --Added for Bug 3780201
1589 l_Named_Role Varchar2(30) := Null;
1590 l_dummy Varchar2(1) := Null;
1591
1592 Cursor c_GetResCode(P_Res_Type_Id IN Number,
1593 P_Res_Srce_Id IN Number) Is
1594 Select
1595 Resource_Name
1596 From
1597 Pa_Rbs_Element_Map
1598 Where
1599 Resource_Type_Id = P_Res_Type_Id
1600 And Resource_Id = P_Res_Srce_Id;
1601
1602 Cursor c_EventType ( P_Id IN Number ) Is
1603 Select
1604 'Y'
1605 From
1606 Pa_Event_Types
1607 Where
1608 Event_Type_Id = P_Id
1609 And Event_Type_Classification IN ('AUTOMATIC','MANUAL','WRITE OFF','WRITE ON');
1610
1611 Cursor c_ExpType ( P_Id IN Number) Is
1612 Select
1613 'Y'
1614 From
1615 Pa_Expenditure_Types
1616 Where
1617 Expenditure_Type_Id = P_Id;
1618
1619 Cursor c_RevCat ( P_Code IN Varchar2) Is
1620 Select
1621 'Y'
1622 From
1623 Pa_Lookups
1624 Where
1625 Lookup_Code = P_Code
1626 And Lookup_Type = 'REVENUE CATEGORY';
1627
1628 Cursor c_People (P_Id IN Number) is
1629 Select
1630 'Y'
1631 From
1632 Per_People_X
1633 Where
1634 Person_Id = P_Id
1635 And ( (Pa_Cross_Business_Grp.IsCrossBGProfile = 'N' AND
1636 Fnd_Profile.Value('PER_BUSINESS_GROUP_ID') = Per_People_X.Business_Group_Id)
1637 OR Pa_Cross_Business_Grp.IsCrossBGProfile = 'Y');
1638
1639 Cursor c_Job (P_Id In Number) Is
1640 Select
1641 'Y'
1642 From
1643 Per_Jobs
1644 Where
1645 Job_Id = P_Id
1646 And ( (Pa_Cross_Business_Grp.IsCrossBGProfile = 'N' AND
1647 Fnd_Profile.Value('PER_BUSINESS_GROUP_ID') = Per_Jobs.Business_Group_Id )
1648 OR Pa_Cross_Business_Grp.IsCrossBGProfile = 'Y');
1649
1650 Cursor c_BOM (P_BOM_Res_Id IN Number) Is
1651 Select
1652 'Y'
1653 From
1654 Bom_Resources
1655 Where
1656 Resource_Id = P_BOM_Res_Id;
1657
1658 Cursor c_ItemCat ( P_Id IN Number ) Is
1659 Select
1660 'Y'
1661 From
1662 Mtl_Categories_tl
1663 Where
1664 Language = USERENV('LANG')
1665 And Category_Id = P_Id;
1666
1667 Cursor c_InvenItem (P_Id IN Number ) Is
1668 Select
1669 'Y'
1670 From
1671 Mtl_System_Items_tl
1672 Where
1673 Language = USERENV('LANG')
1674 And Inventory_Item_Id = P_Id;
1675
1676 Cursor c_ResClass (P_Id IN Number) Is
1677 Select
1678 'Y'
1679 From
1680 Pa_Resource_Classes_Vl
1681 Where
1682 Resource_Class_Id = P_Id;
1683
1684 Cursor c_PrjRoles (P_Id IN Number) Is
1685 Select
1686 'Y'
1687 From
1688 Pa_Project_Role_Types_B
1689 Where
1690 Project_Role_Id = P_Id;
1691
1692 Cursor c_Org(P_Id IN Number) Is
1693 Select
1694 'Y'
1695 From
1696 Hr_All_Organization_Units
1697 Where
1698 Organization_Id = P_Id;
1699
1700 Cursor c_Supplier (P_Id IN Number) Is
1701 Select
1702 'Y'
1703 From
1704 Po_Vendors
1705 Where
1706 Vendor_Id = P_Id;
1707
1708 --Commented for Bug 3780201
1709 /* Cursor c_PerTypes (P_Id IN Number) Is
1710 Select
1711 'Y'
1712 From
1713 Per_Person_Types
1714 Where
1715 Person_Type_Id = P_Id
1716 And Business_Group_Id = 0;*/
1717 --Added for Bug 3780201
1718 Cursor c_PerTypes ( P_Code IN Varchar2) Is
1719 Select
1720 'Y'
1721 From
1722 Pa_Lookups
1723 Where
1724 Lookup_Code = P_Code
1725 And Lookup_Type = 'PA_PERSON_TYPE';
1726 --Changes for Bug 3780201 end
1727
1728 Cursor c_NLRs(P_Id IN Number) Is
1729 Select
1730 'Y'
1731 From
1732 Pa_Non_Labor_Resources
1733 Where
1734 Non_Labor_Resource_Id = P_Id;
1735
1736 Cursor c_ExpCat(P_Id IN Number) Is
1737 Select
1738 'Y'
1739 From
1740 Pa_Expenditure_Categories
1741 Where
1742 Expenditure_Category_Id = P_Id;
1743
1744 Begin
1745
1746
1747 Pa_Debug.G_Stage := 'Entering ValidateResource().';
1748 Pa_Debug.TrackPath('ADD','ValidateResource');
1749
1750 Pa_Debug.G_Stage := 'Check what the resource type is to determine how to validate the resource.';
1751
1752 If P_Resource_Type IN ('BOM_LABOR','BOM_EQUIPMENT') And
1753 P_Resource_Source_Id <> -1 Then
1754
1755 Pa_Debug.G_Stage := 'Validating BOM Labor or BOM Equipment resource.';
1756 Open c_BOM(P_Resource_Source_Id);
1757 Fetch c_BOM Into l_Dummy;
1758
1759 If c_BOM%NotFound Then
1760
1761 X_Error_Msg_Data := 'PA_RBS_ELE_INVALID_RESOURCE';
1762
1763 End If;
1764 Close c_BOM;
1765
1766 Elsif P_Resource_Type = 'NAMED_PERSON' And
1767 P_Resource_Source_Id <> -1 Then
1768
1769 Pa_Debug.G_Stage := 'Validating Name Person resource.';
1770 Open c_People(P_Resource_Source_Id);
1771 Fetch c_People Into l_Dummy;
1772
1773 If c_People%NotFound Then
1774
1775 X_Error_Msg_Data := 'PA_RBS_ELE_NVALID_RESOURCE';
1776
1777 End If;
1778 Close c_People;
1779
1780 Elsif P_Resource_Type = 'EVENT_TYPE' And
1781 P_Resource_Source_Id <> -1 Then
1782
1783 Pa_Debug.G_Stage := 'Validating Event Type resource.';
1784 Open c_EventType(P_Resource_Source_Id);
1785 Fetch c_EventType Into l_Dummy;
1786
1787 If c_EventType%NotFound Then
1788
1789 X_Error_Msg_Data := 'PA_RBS_ELE_INVALID_RESOURCE';
1790
1791 End If;
1792 Close c_EventType;
1793
1794 Elsif P_Resource_Type = 'EXPENDITURE_CATEGORY' And
1795 P_Resource_Source_Id <> -1 Then
1796
1797 Pa_Debug.G_Stage := 'Validating Expenditure Category resource.';
1798 Open c_ExpCat(P_Resource_Source_Id);
1799 Fetch c_ExpCat Into l_Dummy;
1800
1801 If c_ExpCat%NotFound Then
1802
1803 X_Error_Msg_Data := 'PA_RBS_ELE_INVALID_RESOURCE';
1804
1805 End If;
1806 Close c_ExpCat;
1807
1808 Elsif P_Resource_Type = 'EXPENDITURE_TYPE' And
1809 P_Resource_Source_Id <> -1 Then
1810
1811 Pa_Debug.G_Stage := 'Validating Expenditure Type resource.';
1812 Open c_ExpType(P_Resource_Source_Id);
1813 Fetch c_ExpType Into l_Dummy;
1814
1815 If c_ExpType%NotFound Then
1816
1817 X_Error_Msg_Data := 'PA_RBS_ELE_INVALID_RESOURCE';
1818
1819 End If;
1820 Close c_ExpType;
1821
1822 Elsif P_Resource_Type = 'ITEM_CATEGORY' And
1823 P_Resource_Source_Id <> -1 Then
1824
1825 Pa_Debug.G_Stage := 'Validating Item Category resource.';
1826 Open c_ItemCat(P_Resource_Source_Id);
1827 Fetch c_ItemCat Into l_Dummy;
1828
1829 If c_ItemCat%NotFound Then
1830
1831 X_Error_Msg_Data := 'PA_RBS_ELE_INVALID_RESOURCE';
1832
1833 End If;
1834 Close c_ItemCat;
1835
1836 Elsif P_Resource_Type = 'INVENTORY_ITEM' And
1837 P_Resource_Source_Id <> -1 Then
1838
1839 Pa_Debug.G_Stage := 'Validating Inventory Item resource.';
1840 Open c_InvenItem(P_Resource_Source_Id);
1841 Fetch c_InvenItem Into l_Dummy;
1842
1843 If c_InvenItem%NotFound Then
1844
1845 X_Error_Msg_Data := 'PA_RBS_ELE_INVALID_RESOURCE';
1846
1847 End If;
1848 Close c_InvenItem;
1849
1850 Elsif P_Resource_Type = 'JOB' And
1851 P_Resource_Source_Id <> -1 Then
1852
1853 Pa_Debug.G_Stage := 'Validating Job resource.';
1854 Open c_Job(P_Resource_Source_Id);
1855 Fetch c_Job Into l_Dummy;
1856
1857 If c_Job%NotFound Then
1858
1859 X_Error_Msg_Data := 'PA_RBS_ELE_INVALID_RESOURCE';
1860
1861 End If;
1862 Close c_Job;
1863
1864 Elsif P_Resource_Type = 'ORGANIZATION' And
1865 P_Resource_Source_Id <> -1 Then
1866
1867 Pa_Debug.G_Stage := 'Validating Organization resource.';
1868 Open c_Org(P_Resource_Source_Id);
1869 Fetch c_Org Into l_Dummy;
1870
1871 If c_Org%NotFound Then
1872
1873 X_Error_Msg_Data := 'PA_RBS_ELE_INVALID_RESOURCE';
1874
1875 End If;
1876 Close c_Org;
1877
1878 Elsif P_Resource_Type = 'PERSON_TYPE' And
1879 P_Resource_Source_Id <> -1 Then
1880
1881 Pa_Debug.G_Stage := 'Validating Person Type resource.';
1882 --Commented for Bug 3780201
1883 /* Open c_PerTypes(P_Resource_Source_Id);
1884 Fetch c_PerTypes Into l_Dummy;
1885
1886 If c_PerTypes%NotFound Then
1887
1888 X_Error_Msg_Data := 'PA_RBS_ELE_INVALID_RESOURCE';
1889
1890 End If;
1891 Close c_PerTypes;*/
1892 --Added for Bug 3780201
1893 Open c_GetResCode(P_Resource_Type_Id,P_Resource_Source_Id);
1894 Fetch c_GetResCode Into l_Person_Type_Code;
1895 Close c_GetResCode;
1896
1897 If l_Person_Type_Code Is Not Null Then
1898
1899 Open c_PerTypes(l_Person_Type_Code);
1900 Fetch c_PerTypes Into l_Dummy;
1901
1902 If c_PerTypes%NotFound Then
1903
1904 X_Error_Msg_Data := 'PA_RBS_ELE_INVALID_RESOURCE';
1905
1906 End If;
1907 Close c_PerTypes;
1908
1909 Else
1910
1911 X_Error_Msg_Data := 'PA_RBS_ELE_INVALID_RESOURCE';
1912
1913 End If;
1914 --Changes for Bug 3780201 end
1915
1916 Elsif P_Resource_Type = 'NON_LABOR_RESOURCE' And
1917 P_Resource_Source_Id <> -1 Then
1918
1919 Pa_Debug.G_Stage := 'Validating Non Labor Resource resource.';
1920 Open c_NLRs(P_Resource_Source_Id);
1921 Fetch c_NLRs Into l_Dummy;
1922
1923 If c_NLRs%NotFound Then
1924
1925 X_Error_Msg_Data := 'PA_RBS_ELE_INVALID_RESOURCE';
1926
1927 End If;
1928 Close c_NLRs;
1929
1930 Elsif P_Resource_Type = 'RESOURCE_CLASS' And
1931 P_Resource_Source_Id <> -1 Then
1932
1933 Pa_Debug.G_Stage := 'Validating Resource Class resource.';
1934 Open c_ResClass(P_Resource_Source_Id);
1935 Fetch c_ResClass Into l_Dummy;
1936
1937 If c_ResClass%NotFound Then
1938
1939 X_Error_Msg_Data := 'PA_RBS_ELE_INVALID_RESOURCE';
1940
1941 End If;
1942 Close c_ResClass;
1943
1944 Elsif P_Resource_Type = 'REVENUE_CATEGORY' And
1945 P_Resource_Source_Id <> -1 Then
1946
1947 Pa_Debug.G_Stage := 'Validating Revenue Category resource.';
1948 Open c_GetResCode(P_Resource_Type_Id,P_Resource_Source_Id);
1949 Fetch c_GetResCode Into l_Rev_Code;
1950 Close c_GetResCode;
1951
1952 If l_Rev_Code Is Not Null Then
1953
1954 Open c_RevCat(l_Rev_Code);
1955 Fetch c_RevCat Into l_Dummy;
1956
1957 If c_RevCat%NotFound Then
1958
1959 X_Error_Msg_Data := 'PA_RBS_ELE_INVALID_RESOURCE';
1960
1961 End If;
1962 Close c_RevCat;
1963
1964 Else
1965
1966 X_Error_Msg_Data := 'PA_RBS_ELE_INVALID_RESOURCE';
1967
1968 End If;
1969
1970 Elsif P_Resource_Type = 'ROLE' And
1971 P_Resource_Source_Id <> -1 Then
1972
1973 Pa_Debug.G_Stage := 'Validating Role resource.';
1974 Open c_PrjRoles(P_Resource_Source_Id);
1975 Fetch c_PrjRoles Into l_Dummy;
1976
1977 If c_PrjRoles%NotFound Then
1978
1979 X_Error_Msg_Data := 'PA_RBS_ELE_INVALID_RESOURCE';
1980
1981 End If;
1982 Close c_PrjRoles;
1983
1984 Elsif P_Resource_Type = 'SUPPLIER' And
1985 P_Resource_Source_Id <> -1 Then
1986
1987 Pa_Debug.G_Stage := 'Validating Supplier resource.';
1988 Open c_Supplier(P_Resource_Source_Id);
1989 Fetch c_Supplier Into l_Dummy;
1990 If c_Supplier%NotFound Then
1991
1992 X_Error_Msg_Data := 'PA_RBS_ELE_INVALID_RESOURCE';
1993
1994 End If;
1995 Close c_Supplier;
1996
1997 End If;
1998
1999 Pa_Debug.G_Stage := 'Leaving ValidateResource() procedure.';
2000 Pa_Debug.TrackPath('STRIP','ValidateResource');
2001
2002 Exception
2003 When Others Then
2004 Raise;
2005
2006 End ValidateResource;
2007
2008 Procedure GetParentRbsData(
2009 P_Parent_Element_Id IN Number,
2010 X_Person_Id OUT NOCOPY Number,
2011 X_Job_Id OUT NOCOPY Number,
2012 X_Organization_Id OUT NOCOPY Number,
2013 X_Exp_Type_Id OUT NOCOPY Number,
2014 X_Event_Type_Id OUT NOCOPY Number,
2015 X_Exp_Cat_Id OUT NOCOPY Number,
2016 X_Rev_Cat_Id OUT NOCOPY Number,
2017 X_Inv_Item_Id OUT NOCOPY Number,
2018 X_Item_Cat_Id OUT NOCOPY Number,
2019 X_BOM_Labor_Id OUT NOCOPY Number,
2020 X_BOM_Equip_Id OUT NOCOPY Number,
2021 X_Non_Labor_Res_Id OUT NOCOPY Number,
2022 X_Role_Id OUT NOCOPY Number,
2023 X_Person_Type_Id OUT NOCOPY Number,
2024 X_User_Def_Custom1_Id OUT NOCOPY Number,
2025 X_User_Def_Custom2_Id OUT NOCOPY Number,
2026 X_User_Def_Custom3_Id OUT NOCOPY Number,
2027 X_User_Def_Custom4_Id OUT NOCOPY Number,
2028 X_User_Def_Custom5_Id OUT NOCOPY Number,
2029 X_Res_Class_Id OUT NOCOPY Number,
2030 X_Supplier_Id OUT NOCOPY Number,
2031 X_Rbs_Level OUT NOCOPY Number,
2032 X_outline_number OUT NOCOPY VARCHAR2)
2033
2034 Is
2035
2036 Begin
2037
2038 Pa_Debug.G_Stage := 'Entering GetParentRbsData().';
2039 Pa_Debug.TrackPath('ADD','GetParentRbsData');
2040
2041 Pa_Debug.G_Stage := 'Retrieve the parent rbs element data for use in child element/node.';
2042 Select
2043 Person_Id,
2044 Job_Id,
2045 Organization_Id,
2046 Expenditure_Type_Id,
2047 Event_Type_Id,
2048 Expenditure_Category_Id,
2049 Revenue_Category_Id,
2050 Inventory_Item_Id,
2051 Item_Category_Id,
2052 BOM_Labor_Id,
2053 BOM_Equipment_Id,
2054 Non_Labor_Resource_Id,
2055 Role_Id,
2056 Person_Type_Id,
2057 User_Defined_Custom1_Id,
2058 User_Defined_Custom2_Id,
2059 User_Defined_Custom3_id,
2060 User_Defined_Custom4_Id,
2061 User_Defined_Custom5_Id,
2062 Resource_Class_Id,
2063 Supplier_Id,
2064 Rbs_Level,
2065 Outline_number
2066 Into
2067 X_Person_Id,
2068 X_Job_Id,
2069 X_Organization_Id,
2070 X_Exp_Type_Id,
2071 X_Event_Type_Id,
2072 X_Exp_Cat_Id,
2073 X_Rev_Cat_Id,
2074 X_Inv_Item_Id,
2075 X_Item_Cat_Id,
2076 X_BOM_Labor_Id,
2077 X_BOM_Equip_Id,
2078 X_Non_Labor_Res_Id,
2079 X_Role_Id,
2080 X_Person_Type_Id,
2081 X_User_Def_Custom1_Id,
2082 X_User_Def_Custom2_Id,
2083 X_User_Def_Custom3_Id,
2084 X_User_Def_Custom4_Id,
2085 X_User_Def_Custom5_Id,
2086 X_Res_Class_Id,
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
2096 Pa_Debug.G_Stage := 'Leaving GetParentRbsData() procedure.';
2097 Pa_Debug.TrackPath('STRIP','GetParentRbsData');
2098
2099 Exception
2100 When No_Data_Found Then
2101 Null;
2102 When Others Then
2103 Raise;
2104
2105 End GetParentRbsData;
2106
2107 -- This procedure is only really neccessary for the Update/Create Child Elements page
2108 -- but will always be called.
2109 -- Since we can't control the order of the records in the pl/sql table it may
2110 -- occur that children are process more than once.
2111 Procedure UpdateOrderOutlineNumber(
2112 P_Parent_Element_Id_Tbl IN System.Pa_Num_Tbl_Type,
2113 X_Error_Msg_Data OUT NOCOPY Varchar2 )
2114
2115 Is
2116
2117 i number := null;
2118 l_par_element_id number := -1;
2119 l_par_outline_number varchar2(240) := Null;
2120 l_order_number number := Null;
2121
2122 -- Gets the max order number for the the level
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.
2141 Cursor c3(P_Par_Element_Id IN Number) Is
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
2151 l_rbs_rec c3%RowType;
2152
2153 Begin
2154
2155 Pa_Debug.G_Stage := 'Entering UpdateOrderOutlineNumber().';
2156 Pa_Debug.TrackPath('ADD','UpdateOrderOutlineNumber');
2157
2158 Pa_Debug.G_Stage := 'Begin loop thru the parent array table to process the elements/nodes for the parents.';
2159 For i in P_Parent_Element_Id_Tbl.First .. P_Parent_Element_Id_Tbl.Last
2160 Loop
2161
2162 Pa_Debug.G_Stage := 'Check if working with new parent element/node.';
2163 If l_par_element_id <> P_Parent_Element_Id_Tbl(i) Then
2164
2165 Pa_Debug.G_Stage := 'Assign parent element/node id to local variable for if check.';
2166 l_par_element_id := P_Parent_Element_Id_Tbl(i);
2167
2168 -- Get the max order number for the children of the current parent
2169 Pa_Debug.G_Stage := 'Get the max order number for all the children of ' ||
2170 'the current parent element id being processed.';
2171 open c1(P_Parent_Element_Id_Tbl(i));
2172 Fetch c1 into l_order_number;
2173 Close c1;
2174
2175 -- Get the parent outline number
2176 Pa_Debug.G_Stage := 'Get parent outline number to use to append the child ' ||
2177 'order number to for the new child outline number.';
2178 open c2(P_Parent_Element_Id_Tbl(i));
2179 Fetch c2 into l_par_outline_number;
2180 Close c2;
2181
2182 -- Get all the children for the parent for update
2183 Pa_Debug.G_Stage := 'Open cursor to get all child elements for the current ' ||
2184 'parent element id.';
2185 Open c3(P_Parent_Element_Id_Tbl(i));
2186 Loop
2187
2188 Pa_Debug.G_Stage := 'Fetch record from cursor with all the child ' ||
2189 'elements of the current parent element being processed.';
2190 Fetch c3 into l_Rbs_Rec;
2191 Exit When c3%NotFound;
2192
2193 Pa_Debug.G_Stage := 'Check if the child element/node has an order number = -1.';
2194 If l_Rbs_Rec.Order_Number = -1 Then
2195
2196 Pa_Debug.G_Stage := 'Assign order number to a child that does ' ||
2197 'not currently have one.';
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),
2207 l_Par_Outline_Number || '.' || to_char(l_Order_Number))
2208 Where
2209 Rbs_Element_Id = l_Rbs_Rec.Rbs_Element_id;
2210
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))
2220 Where
2221 Rbs_Element_Id = l_Rbs_Rec.Rbs_Element_id;
2222
2223 End If;
2224
2225 End Loop;
2226
2227 End If;
2228
2229 End Loop;
2230
2231 Pa_Debug.G_Stage := 'Leaving UpdateOrderOutlineNumber() procedure.';
2232 Pa_Debug.TrackPath('STRIP','UpdateOrderOutlineNumber');
2233
2234 Exception
2235 When Others Then
2236 Raise;
2237
2238 End UpdateOrderOutlineNumber;
2239
2240 Procedure Update_Children_Data(
2241 P_Rbs_Element_Id IN Number,
2242 X_Error_Msg_Data OUT NOCOPY Varchar2)
2243
2244 Is
2245
2246 Cursor c1 (P_Rbs_Elem_Id IN Number) IS
2247 Select
2248 Rbs_Element_Id,
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
2258 Connect By Prior
2259 Rbs_Element_Id = Parent_Element_Id
2260 Order by
2261 Rbs_Level;
2262
2263 l_Child_Rec c1%RowType;
2264
2265 l_Person_Id Number(15) := Null;
2266 l_Job_Id Number(15) := Null;
2267 l_Organization_Id Number(15) := Null;
2268 l_Exp_Type_Id Number(15) := Null;
2269 l_Event_Type_Id Number(15) := Null;
2270 l_Exp_Cat_Id Number(15) := Null;
2271 l_Rev_Cat_Id Number(15) := Null;
2272 l_Inv_Item_Id Number(15) := Null;
2273 l_Item_Cat_Id Number(15) := Null;
2274 l_BOM_Labor_Id Number(15) := Null;
2275 l_BOM_Equip_Id Number(15) := Null;
2276 l_Non_Labor_Res_Id Number(15) := Null;
2277 l_Role_Id Number(15) := Null;
2278 l_Person_Type_Id Number(15) := Null;
2279 l_User_Def_Custom1_Id Number(15) := Null;
2280 l_User_Def_Custom2_Id Number(15) := Null;
2281 l_User_Def_Custom3_Id Number(15) := Null;
2282 l_User_Def_Custom4_Id Number(15) := Null;
2283 l_User_Def_Custom5_Id Number(15) := Null;
2284 l_Res_Class_Id Number(15) := Null;
2285 l_Supplier_Id Number(15) := Null;
2286 l_Dummy_Rbs_Level Number(15) := Null;
2287 l_Dummy_Outline_Number Varchar2(240) := Null;
2288 l_Resource_Type Varchar2(30) := Null;
2289
2290 MAX_USER_DEF_RES_IDS Exception;
2291
2292 Begin
2293
2294 Pa_Debug.G_Stage := 'Entering Update_Children_Data().';
2295 Pa_Debug.TrackPath('ADD','Update_Children_Data');
2296
2297 Pa_Debug.G_Stage := 'Open c1 cursor to get rbs elements to update.';
2298 Open c1(P_Rbs_Elem_Id => P_Rbs_Element_Id);
2299
2300 Loop
2301
2302 Pa_Debug.G_Stage := 'Fetch the current child record.';
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,
2312 X_Exp_Type_Id => l_Exp_Type_Id,
2313 X_Event_Type_Id => l_Event_Type_Id,
2314 X_Exp_Cat_Id => l_Exp_Cat_Id,
2315 X_Rev_Cat_Id => l_Rev_Cat_Id,
2316 X_Inv_Item_Id => l_Inv_Item_Id,
2317 X_Item_Cat_Id => l_Item_Cat_Id,
2318 X_BOM_Labor_Id => l_BOM_Labor_Id,
2319 X_BOM_Equip_Id => l_BOM_Equip_Id,
2320 X_Non_Labor_Res_Id => l_Non_Labor_Res_Id,
2321 X_Role_Id => l_Role_Id,
2322 X_Person_Type_Id => l_Person_Type_Id,
2323 X_User_Def_Custom1_Id => l_User_Def_Custom1_Id,
2324 X_User_Def_Custom2_Id => l_User_Def_Custom2_Id,
2325 X_User_Def_Custom3_Id => l_User_Def_Custom3_Id,
2326 X_User_Def_Custom4_Id => l_User_Def_Custom4_Id,
2327 X_User_Def_Custom5_Id => l_User_Def_Custom5_Id,
2328 X_Res_Class_Id => l_Res_Class_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
2338
2339 Pa_Debug.G_Stage := 'Assign the resource source id to BOM Labor.';
2340 l_BOM_Labor_Id := l_Child_Rec.Resource_Source_Id;
2341
2342 Elsif l_Resource_Type = 'BOM_EQUIPMENT' Then
2343
2344 Pa_Debug.G_Stage := 'Assign the resource source id to BOM Equipment.';
2345 l_BOM_Equip_Id := l_Child_Rec.Resource_Source_Id;
2346
2347 Elsif l_Resource_Type = 'NAMED_PERSON' Then
2348
2349 Pa_Debug.G_Stage := 'Assign the resource source id to Named Person.';
2350 l_Person_Id := l_Child_Rec.Resource_Source_Id;
2351
2352 Elsif l_Resource_Type = 'EVENT_TYPE' Then
2353
2354 Pa_Debug.G_Stage := 'Assign the resource source id to Event Type.';
2355 l_Event_Type_Id := l_Child_Rec.Resource_Source_Id;
2356
2357 Elsif l_Resource_Type = 'EXPENDITURE_CATEGORY' Then
2358
2359 Pa_Debug.G_Stage := 'Assign the resource source id to Expenditure Category.';
2360 l_Exp_Cat_Id := l_Child_Rec.Resource_Source_Id;
2361
2362 Elsif l_Resource_Type = 'EXPENDITURE_TYPE' Then
2363
2364 Pa_Debug.G_Stage := 'Assign the resource source id to Expenditure Type.';
2365 l_Exp_Type_Id := l_Child_Rec.Resource_Source_Id;
2366
2367 Elsif l_Resource_Type = 'ITEM_CATEGORY' Then
2368
2369 Pa_Debug.G_Stage := 'Assign the resource source id to Item Category.';
2370 l_Item_Cat_Id := l_Child_Rec.Resource_Source_Id;
2371
2372 Elsif l_Resource_Type = 'INVENTORY_ITEM' Then
2373
2374 Pa_Debug.G_Stage := 'Assign the resource source id to Inventory Item.';
2375 l_Inv_Item_Id := l_Child_Rec.Resource_Source_Id;
2376
2377 Elsif l_Resource_Type = 'JOB' Then
2378
2379 Pa_Debug.G_Stage := 'Assign the resource source id to Job.';
2380 l_Job_Id := l_Child_Rec.Resource_Source_Id;
2381
2382 Elsif l_Resource_Type = 'ORGANIZATION' Then
2383
2384 Pa_Debug.G_Stage := 'Assign the resource source id to Organization.';
2385 l_Organization_Id := l_Child_Rec.Resource_Source_Id;
2386
2387 Elsif l_Resource_Type = 'PERSON_TYPE' Then
2388
2389 Pa_Debug.G_Stage := 'Assign the resource source id to Person Type.';
2390 l_Person_Type_Id := l_Child_Rec.Resource_Source_Id;
2391
2392 Elsif l_Resource_Type = 'NON_LABOR_RESOURCE' Then
2393
2394 Pa_Debug.G_Stage := 'Assign the resource source id to Non Labor Resource.';
2395 l_Non_Labor_Res_Id := l_Child_Rec.Resource_Source_Id;
2396
2397 Elsif l_Resource_Type = 'RESOURCE_CLASS' Then
2398
2399 Pa_Debug.G_Stage := 'Assign the resource source id to Resource Class.';
2400 l_Res_Class_Id := l_Child_Rec.Resource_Source_Id;
2401
2402 Elsif l_Resource_Type = 'REVENUE_CATEGORY' Then
2403
2404 Pa_Debug.G_Stage := 'Assign the resource source id to Revenue Category.';
2405 l_Rev_Cat_Id := l_Child_Rec.Resource_Source_Id;
2406
2407 Elsif l_Resource_Type = 'ROLE' Then
2408
2409 Pa_Debug.G_Stage := 'Assign the resource source id to Role.';
2410 l_Role_Id := l_Child_Rec.Resource_Source_Id;
2411
2412 Elsif l_Resource_Type = 'SUPPLIER' Then
2413
2414 Pa_Debug.G_Stage := 'Assign the resource source id to Supplier.';
2415 l_Supplier_Id := l_Child_Rec.Resource_Source_Id;
2416
2417 Elsif l_Resource_Type = 'USER_DEFINED' Then
2418
2419 Pa_Debug.G_Stage := 'Assign the resource source id to User Defined.';
2420 If l_User_Def_Custom1_Id Is Null Then
2421
2422 Pa_Debug.G_Stage := 'Assign the resource source id to User Defined 1.';
2423 l_User_Def_Custom1_Id := l_Child_Rec.Resource_Source_Id;
2424
2425 ElsIf l_User_Def_Custom2_Id Is Null Then
2426
2427 Pa_Debug.G_Stage := 'Assign the resource source id to User Defined 2.';
2428 l_User_Def_Custom2_Id := l_Child_Rec.Resource_Source_Id;
2429
2430 ElsIf l_User_Def_Custom3_Id Is Null Then
2431
2432 Pa_Debug.G_Stage := 'Assign the resource source id to User Defined 3.';
2433 l_User_Def_Custom3_Id := l_Child_Rec.Resource_Source_Id;
2434
2435 ElsIf l_User_Def_Custom4_Id Is Null Then
2436
2437 Pa_Debug.G_Stage := 'Assign the resource source id to User Defined 4.';
2438 l_User_Def_Custom4_Id := l_Child_Rec.Resource_Source_Id;
2439
2440 ElsIf l_User_Def_Custom5_Id Is Null Then
2441
2442 Pa_Debug.G_Stage := 'Assign the resource source id to User Defined 5.';
2443 l_User_Def_Custom5_Id := l_Child_Rec.Resource_Source_Id;
2444
2445 Else
2446
2447 Pa_Debug.G_Stage := 'All user defined resources field populated.';
2448 Raise MAX_USER_DEF_RES_IDS;
2449
2450 End If;
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,
2460 Expenditure_Type_Id = l_Exp_Type_Id,
2461 Event_Type_Id = l_Event_Type_Id,
2462 Expenditure_Category_Id = l_Exp_Cat_Id,
2463 Revenue_Category_Id = l_Rev_Cat_Id,
2464 Inventory_Item_Id = l_Inv_Item_Id,
2465 Item_Category_Id = l_Item_Cat_Id,
2466 BOM_Labor_Id = l_BOM_Labor_Id,
2467 BOM_Equipment_Id = l_BOM_Equip_Id,
2468 Non_Labor_Resource_Id = l_Non_Labor_Res_Id,
2469 Role_Id = l_Role_Id,
2470 Person_Type_Id = l_Person_Type_Id,
2471 User_Defined_Custom1_Id = l_User_Def_Custom1_Id,
2472 User_Defined_Custom2_Id = l_User_Def_Custom2_Id,
2473 User_Defined_Custom3_Id = l_User_Def_Custom3_Id,
2474 User_Defined_Custom4_Id = l_User_Def_Custom4_Id,
2475 User_Defined_Custom5_Id = l_User_Def_Custom5_Id,
2476 Resource_Class_Id = l_Res_Class_Id,
2477 Supplier_Id = l_Supplier_Id
2478 Where Rbs_Element_Id = l_Child_Rec.Rbs_Element_Id;
2479
2480 End Loop;
2481
2482 Pa_Debug.G_Stage := 'Close the primary cursor c1.';
2483 Close c1;
2484
2485 Pa_Debug.G_Stage := 'Leaving Update_Children_Data() procedure.';
2486 Pa_Debug.TrackPath('STRIP','Update_Children_Data');
2487
2488 Exception
2489 When MAX_USER_DEF_RES_IDS Then
2490 X_Error_Msg_Data := 'PA_MAX_USER_DEF_RES_IDS';
2491 When Others Then
2492 Raise;
2493
2494 End Update_Children_Data;
2495
2496 /*
2497 This procedure disable the cbs cost attributes
2498 Bug15834912 : CBS phase 2 changes.
2499 */
2500 procedure disableCostAttributes(P_RBS_Version_Id IN Number,
2501 P_Element_Id_Tbl IN System.Pa_Num_Tbl_Type,
2502 X_Return_Status OUT NOCOPY Varchar2,
2503 X_Msg_Count OUT NOCOPY Number,
2504 X_Error_Msg_Data OUT NOCOPY Varchar2)
2505 IS
2506
2507
2508 BEGIN
2509
2510 X_Msg_Count := 0;
2511 X_Error_Msg_Data := Null;
2512 X_Return_Status := Fnd_Api.G_Ret_Sts_Success;
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'
2522 WHERE RBS_VERSION_ID=P_RBS_VERSION_ID
2523 AND PARENT_ELEMENT_ID = P_Element_Id_Tbl(i);
2524
2525 END Loop;
2526 Exception
2527 When Others Then
2528 X_Return_Status := 'E';
2529 X_Msg_Count := 1;
2530 X_Error_Msg_Data := 'Unable to disable cost attributes';
2531
2532 END disableCostAttributes;
2533
2534 End Pa_Rbs_Elements_Pvt;
[Home] [Help]