[Home] [Help]
PACKAGE BODY: APPS.PA_RBS_VERSIONS_PVT
Source
1 Package Body Pa_Rbs_Versions_Pvt as
2 --$Header: PARBSVVB.pls 120.5 2011/12/28 11:18:48 djambhek ship $
3
4 Procedure Create_Working_Version_Record(
5 P_Mode IN Varchar2 Default Null,
6 P_Version_Number IN Number,
7 P_Rbs_Header_Id IN Number,
8 P_Record_Version_Number IN Number,
9 P_Name IN Varchar2,
10 P_Description IN Varchar2,
11 P_Version_Start_Date IN Date,
12 P_Version_End_Date IN Date,
13 P_Job_Group_Id IN Number,
14 P_Resource_Class_Flag IN Varchar2 DEFAULT 'Y', --Added for CBS::13535688
15 P_Rule_Based_Flag IN Varchar2,
16 P_Validated_Flag IN Varchar2,
17 P_Status_Code IN Varchar2,
18 X_Record_Version_Number OUT NOCOPY Number,
19 X_RBS_Version_Id OUT NOCOPY Number,
20 X_Error_Msg_Data OUT NOCOPY Varchar2 )
21
22 Is
23
24 Begin
25
26 Pa_Debug.G_Stage := 'Entering Create_Working_Version_Record().';
27 Pa_Debug.TrackPath('ADD','Create_Working_Version_Record');
28
29 Pa_Debug.G_Stage := 'Call Pa_Rbs_Versions_Pkg.Insert_Row() procedure.';
30 Pa_Rbs_Versions_Pkg.Insert_Row(
31 P_Version_Number => P_Version_Number,
32 P_Rbs_Header_Id => P_Rbs_Header_Id,
33 P_Record_Version_Number => P_Record_Version_Number,
34 P_Name => P_Name,
35 P_Description => P_Description,
36 P_Version_Start_Date => P_Version_Start_Date,
37 P_Version_End_Date => P_Version_End_Date,
38 P_Job_Group_Id => P_Job_Group_Id,
39 P_Resource_Class_Flag => P_Resource_Class_Flag, --Added for CBS::13535688
40 P_Rule_Based_Flag => P_Rule_Based_Flag,
41 P_Validated_Flag => P_Validated_Flag,
42 P_Status_Code => P_Status_Code,
43 P_Creation_Date => Pa_Rbs_Versions_Pvt.G_Creation_Date,
44 P_Created_By => Pa_Rbs_Versions_Pvt.G_Created_By,
45 P_Last_Update_Date => Pa_Rbs_Versions_Pvt.G_Last_Update_Date,
46 P_Last_Updated_By => Pa_Rbs_Versions_Pvt.G_Last_Updated_By,
47 P_Last_Update_Login => Pa_Rbs_Versions_Pvt.G_Last_Update_Login,
48 X_Record_Version_Number => X_Record_Version_Number,
49 X_Rbs_Version_Id => X_RBS_Version_Id,
50 X_Error_Msg_Data => X_Error_Msg_Data);
51
52 Pa_Debug.G_Stage := 'Check if Pa_Rbs_Versions_Pkg.Insert_Row() procedure returned error msg.';
53 If X_Error_Msg_Data is Not Null And P_Mode is Null Then
54
55 Pa_Debug.G_Stage := 'Add Message to message stack.';
56 Pa_Utils.Add_Message
57 (P_App_Short_Name => 'PA',
58 P_Msg_Name => X_Error_Msg_Data);
59
60 End If;
61
62 Pa_Debug.G_Stage := 'Leaving Create_Working_Version_Record()procedure.';
63 Pa_Debug.TrackPath('STRIP','Create_Working_Version_Record');
64
65 Exception
66 When Others Then
67 Raise;
68
69 End Create_Working_Version_Record;
70
71 Procedure Update_Working_Version(
72 P_RBS_Version_Id IN Number,
73 P_Name IN Varchar2,
74 P_Description IN Varchar2,
75 P_Version_Start_Date IN Date ,
76 P_Job_Group_Id IN Number,
77 P_Record_Version_Number IN Number,
78 P_Res_Class_Flag IN Varchar2 DEFAULT 'Y', --Added for CBS::13535688
79 X_Record_Version_Number OUT NOCOPY Number,
80 X_Error_Msg_Data OUT NOCOPY Varchar2 )
81
82 Is
83
84 Begin
85
86 Pa_Debug.G_Stage := 'Entering Update_Working_Version() Pvt.';
87 Pa_Debug.TrackPath('ADD','Update_Working_Version Pvt');
88
89 Pa_Debug.G_Stage := 'Call Pa_Rbs_Versions_Pkg.Update_Row() procedure.';
90 Pa_Rbs_Versions_Pkg.Update_Row(
91 P_RBS_Version_Id => P_RBS_Version_Id,
92 P_Name => P_Name,
93 P_Description => P_Description,
94 P_Version_Start_Date => P_Version_Start_Date,
95 P_Job_Group_Id => P_Job_Group_Id,
96 P_Record_Version_Number => P_Record_Version_Number,
97 P_Res_Class_Flag => P_Res_Class_Flag , --Added for CBS::13535688
98 P_Last_Update_Date => Pa_Rbs_Versions_Pvt.G_Last_Update_Date,
99 P_Last_Updated_By => Pa_Rbs_Versions_Pvt.G_Last_Updated_By,
100 P_Last_Update_Login => Pa_Rbs_Versions_Pvt.G_Last_Update_Login,
101 X_Record_Version_Number => X_Record_Version_Number,
102 X_Error_Msg_Data => X_Error_Msg_Data);
103
104 Pa_Debug.G_Stage := 'Check if Pa_Rbs_Versions_Pkg.Update_Row() procedure returned error msg.';
105 If X_Error_Msg_Data is Not Null Then
106
107 Pa_Debug.G_Stage := 'Add Message to message stack.';
108 Pa_Utils.Add_Message
109 (P_App_Short_Name => 'PA',
110 P_Msg_Name => X_Error_Msg_Data);
111
112 End If;
113
114 Pa_Debug.G_Stage := 'Leaving Update_Working_Version() Pvt procedure.';
115 Pa_Debug.TrackPath('STRIP','Update_Working_Version Pvt');
116
117 Exception
118 When Others Then
119 Raise;
120
121 End Update_Working_Version;
122
123 Procedure Delete_Working_Version(
124 P_Mode IN Varchar2 Default Null,
125 P_RBS_Version_Id IN Number,
126 P_Record_Version_Number IN Number,
127 X_Error_Msg_Data OUT NOCOPY Varchar2)
128
129 Is
130
131
132 Begin
133
134 Pa_Debug.G_Stage := 'Entering Delete_Working_Version() Pvt.';
135 Pa_Debug.TrackPath('ADD','Delete_Working_Version Pvt');
136
137 Pa_Debug.G_Stage := 'Call Pa_Rbs_Versions_Pkg.Delete_Row() procedure.';
138 Pa_Rbs_Versions_Pkg.Delete_Row(
139 P_RBS_Version_Id => P_RBS_Version_Id,
140 P_Record_Version_Number => P_Record_Version_Number,
141 X_Error_Msg_Data => X_Error_Msg_Data);
142
143 Pa_Debug.G_Stage := 'Check if Pa_Rbs_Versions_Pkg.Delete_Row() procedure returned error msg.';
144 If X_Error_Msg_Data is Not Null and P_Mode is Null Then
145
146 Pa_Debug.G_Stage := 'Add Message to message stack.';
147 Pa_Utils.Add_Message
148 (P_App_Short_Name => 'PA',
149 P_Msg_Name => X_Error_Msg_Data);
150
151 End If;
152
153 If X_Error_Msg_Data is Null Then
154
155
156 Pa_Debug.G_Stage := 'Delete the working version element/nodes.';
157 Begin
158 Delete
159 From
160 Pa_Rbs_Elements
161 Where
162 Rbs_Version_Id = P_Rbs_Version_Id;
163
164 Exception
165 When No_Data_Found Then
166 Null;
167 When Others Then
168 Raise;
169
170 End;
171
172 End If;
173
174
175 Pa_Debug.G_Stage := 'Leaving Delete_Working_Version() Pvt procedure.';
176 Pa_Debug.TrackPath('STRIP','Delete_Working_Version Pvt');
177
178 Exception
179 When Others Then
180 Raise;
181
182 End Delete_Working_Version;
183
184 Procedure Create_New_Working_Version(
185 P_Rbs_Version_Id IN Number,
186 P_Rbs_Header_Id IN Number,
187 P_Record_Version_Number IN Number,
188 X_Error_Msg_Data OUT NOCOPY Varchar2)
189 Is
190
191 l_Rbs_Version_Id Number(15) := Null;
192 l_Error EXCEPTION;
193
194 Begin
195
196 Pa_Debug.G_Stage := 'Entering Create_New_Working_Version().';
197 Pa_Debug.TrackPath('ADD','Create_New_Working_Version');
198
199
200 Pa_Debug.G_Stage := 'Call Pa_Rbs_Versions_Pvt.Copy_Frozen_Rbs_Version() procedure.';
201 Pa_Rbs_Versions_Pvt.Copy_Frozen_Rbs_Version(
202 P_Rbs_Version_Id => P_Rbs_Version_Id, -- the frozen version being copied
203 P_Rbs_Header_Id => P_Rbs_Header_Id,
204 P_Record_Version_Number => P_Record_Version_Number, -- This is the record_version_number of the working version
205 X_Rbs_Version_Id => l_Rbs_Version_Id, -- The working version being copied to
206 X_Error_Msg_Data => X_Error_Msg_Data);
207
208 Pa_Debug.G_Stage := 'Check if Pa_Rbs_Versions_Pkg.Copy_Frozen_Rbs_Version() procedure returned error msg.';
209 If X_Error_Msg_Data is Not Null Then
210
211 Pa_Debug.G_Stage := 'Add Message to message stack.';
212 Pa_Utils.Add_Message
213 (P_App_Short_Name => 'PA',
214 P_Msg_Name => X_Error_Msg_Data);
215
216 Raise l_Error;
217
218 End If;
219
220 Pa_Debug.G_Stage := 'Call Pa_Rbs_Versions_Pvt.Copy_Frozen_Rbs_Elements() procedure.';
221
222
223 Pa_Rbs_Versions_Pvt.Copy_Frozen_Rbs_Elements(
224 P_Rbs_Version_From_Id => P_Rbs_Version_Id, -- this is the frozen version being copied from
225 P_Rbs_Version_To_Id => l_Rbs_Version_Id, -- this is the working version being copied to
226 X_Error_Msg_Data => X_Error_Msg_Data);
227
228
229 Pa_Debug.G_Stage := 'Check if Pa_Rbs_Versions_Pkg.Copy_Frozen_Rbs_Elements() procedure returned error msg.';
230 If X_Error_Msg_Data is Not Null Then
231
232 Pa_Debug.G_Stage := 'Add Message to message stack.';
233 Pa_Utils.Add_Message
234 (P_App_Short_Name => 'PA',
235 P_Msg_Name => X_Error_Msg_Data);
236
237 End If;
238
239 Pa_Debug.G_Stage := 'Leaving Create_New_Working_Version() procedure.';
240 Pa_Debug.TrackPath('STRIP','Create_New_Working_Version');
241
242
243
244 Exception
245 When l_Error Then
246 Null;
247
248 When Others Then
249 Raise;
250
251 End Create_New_Working_Version;
252
253 Procedure Copy_Frozen_Rbs_Version(
254 P_Rbs_Version_Id IN Number,
255 P_Rbs_Header_Id IN Number,
256 P_Record_Version_Number IN Number,
257 X_Rbs_Version_Id OUT NOCOPY Number,
258 X_Error_Msg_Data OUT NOCOPY Varchar2)
259
260 Is
261
262 l_Version_Number Number(15);
263 l_Name Varchar2(240);
264 l_Description Varchar2(2000);
265 l_Version_Start_Date Date;
266 l_Job_Group_Id Number;
267 l_Rule_Based_Flag Varchar2(1);
268 l_Validated_Flag Varchar2(1);
269 l_Created_By Number := Fnd_Global.User_Id;
270 l_Creation_date Date := SysDate;
271 l_Last_Update_Date Date := SysDate;
272 l_Last_Updated_By Number := Fnd_Global.User_Id;
273 l_Last_Update_Login Number := Fnd_Global.Login_Id;
274 l_Working Varchar2(1);
275 l_Record_Locked Exception;
276 l_Msg_Data Varchar2(2000) := Null;
277 l_Msg_Count Number := 0;
278 l_Data Varchar2(2000) := Null;
279 l_Msg_Index_Out Number;
280 l_Version_Id Number(15);
281 l_Rec_Version_Number Number := Null;
282 --Bug: 4537865
283 l_new_Rec_Version_Number NUMBER := Null;
284 --Bug: 4537865
285 l_Error_Msg_Data Varchar2(30) := Null;
286
287 Cursor GetDetails(P_Id IN Number) IS
288 Select
289 Job_Group_Id,
290 Rule_Based_Flag,
291 Version_End_Date,
292 RESOURCE_CLASS_FLAG --Added for CBS::13535688
293 From
294 Pa_Rbs_Versions_B
295 Where
296 Rbs_Version_Id = P_Id;
297
298 Cursor GetDetails2(P_Id IN Number) Is
299 Select
300 Description
301 From
302 Pa_Rbs_Versions_TL
303 Where
304 Rbs_Version_Id = P_Id
305 --MLS changes.
306 --And Source_Lang = UserEnv('LANG');
307 And Language = UserEnv('LANG');
308
309 GetDetails_Rec GetDetails%RowType;
310 GetDetails_Rec2 GetDetails2%RowType;
311
312 Cursor C is
313 Select
314 Record_Version_Number,
315 Rbs_Version_Id
316 From
317 Pa_Rbs_Versions_B
318 Where
319 Rbs_Header_Id = P_Rbs_Header_Id
320 And Status_Code = 'WORKING'
321 For Update Of Status_Code NoWait;
322
323 Cursor GetMaxFrozenRbsVersionId(P_Id IN Number) Is
324 Select
325 Max(Rbs_Version_Id)
326 From
327 Pa_Rbs_Versions_B
328 Where
329 Rbs_Header_Id = P_Rbs_Header_Id
330 And Status_Code <> 'WORKING';
331
332 Cursor c_GetHdrFromDate(P_Hdr_Id IN Number) Is
333 Select
334 Effective_From_Date
335 From
336 Pa_Rbs_Headers_B
337 Where
338 Rbs_Header_Id = P_Hdr_Id;
339
340 Cursor C_GetRbsElementNameId(l_Rbs_Version_Id IN Number) is
341 Select
342 rbs_element_name_id
343 From
344 pa_rbs_elements
345 Where
346 rbs_version_id = l_Rbs_Version_Id
347 and outline_number = '0';
348
349
350 l_RVN Number(15) := Null;
351 l_Rbs_Version_Id Number(15) := Null;
352 l_Rbs_Version_From_Id Number(15) := Null;
353 l_Frozen_Rbs_Ver_Exists BOOLEAN := False;
354 l_Hdr_From_Date Date;
355 l_Rbs_Element_Name_Id Number(15) := Null;
356
357 BEGIN
358
359 Pa_Debug.G_Stage := 'Entering Copy_Frozen_Rbs_Version() procedure.';
360 Pa_Debug.TrackPath('ADD','Copy_Frozen_Rbs_Version');
361
362 Pa_Debug.G_Stage := 'Check if the rbs_version_id to copy from parameter is populated.';
363 If P_Rbs_Version_Id is Null Then
364
365 Pa_Debug.G_Stage := 'Get the max rbs version id for the rbs header as the copy from rbs version.';
366 Open GetMaxFrozenRbsVersionId(P_Rbs_Header_Id);
367 Fetch GetMaxFrozenRbsVersionId Into l_Rbs_Version_From_Id;
368 Close GetMaxFrozenRbsVersionId;
369
370 Else
371
372 Pa_Debug.G_Stage := 'Using the rbs version id passed in as the copy from rbs version.';
373 l_Rbs_Version_From_Id := P_Rbs_Version_Id;
374
375 End If;
376
377 Pa_Debug.G_Stage := 'Get details from record in pa_rbs_versions_b to copy from.';
378 /* To get the details of selected version*/
379 Open GetDetails(l_Rbs_Version_From_Id);
380 Fetch GetDetails Into GetDetails_Rec;
381 If GetDetails%NotFound Then
382
383 Pa_Debug.G_Stage := 'No frozen version record found.';
384 l_Frozen_Rbs_Ver_Exists := False;
385 Close GetDetails;
386
387 Else
388
389 l_Frozen_Rbs_Ver_Exists := True;
390 Close GetDetails;
391
392 Pa_Debug.G_Stage := 'Get details from record in pa_rbs_versions_tl to copy from.';
393 Open GetDetails2(l_Rbs_Version_From_Id);
394 Fetch GetDetails2 Into GetDetails_Rec2;
395 Close GetDetails2;
396
397 End If;
398
399 Pa_Debug.G_Stage := 'Get working version record data from pa_rbs_versions_b.';
400 Open C;
401 Fetch C Into l_rvn, l_Rbs_Version_Id;
402 Close C;
403
404 Open C_GetRbsElementNameId(l_Rbs_Version_Id);
405 Fetch C_GetRbsElementNameId Into l_Rbs_Element_Name_Id;
406 Close C_GetRbsElementNameId;
407
408 Pa_Debug.G_Stage := 'Check if there is a working version record or not.';
409 If l_RVN <> 0 or l_RVN is Not Null Then
410
411
412 Pa_Debug.G_Stage := 'Check if the record version parameter matches with the record ' ||
413 'version number from working version..';
414 If l_Rvn = P_Record_Version_Number Then
415
416
417 -- Delete the current working rbs version and its element/node records
418 Pa_Debug.G_Stage := 'Call Pa_Rbs_Versions_Pvt.Delete_Working_Version() procedure.';
419 Pa_Rbs_Versions_Pvt.Delete_Working_Version(
420 P_Mode => 'COPYING_FROZEN_VERSION',
421 P_Rbs_Version_Id => l_Rbs_Version_Id,
422 P_Record_Version_Number => P_Record_Version_Number,
423 X_Error_Msg_Data => X_Error_Msg_Data);
424
425 Pa_Debug.G_Stage := 'Delete the record in pa_rbs_element_names table corresponding to that ' ||
426 'of top most element in pa_rbs_elements table.';
427 If X_Error_Msg_Data is null then
428
429 Begin
430
431 Delete
432 From
433 Pa_Rbs_Element_names_tl
434 Where
435 Rbs_Element_Name_Id = l_Rbs_Element_Name_Id;
436
437 Delete
438 From
439 Pa_Rbs_Element_names_b
440 Where
441 Rbs_Element_Name_Id = l_Rbs_Element_Name_Id;
442
443 Exception
444 When No_Data_Found Then
445 Null;
446 When Others Then
447 Raise;
448 End;
449 Else
450
451 Pa_Debug.G_Stage := 'Add Message to message stack.';
452 Pa_Utils.Add_Message
453 (P_App_Short_Name => 'PA',
454 P_Msg_Name => X_Error_Msg_Data);
455 End If;
456
457 Else
458
459 Pa_Debug.G_Stage := 'Unable to lock record. Raise user defined error.';
460 Raise l_Record_Locked;
461
462 End If;
463
464 End If ; -- end of if l_rvn <>0
465
466 Pa_Debug.G_Stage := 'Increment the record version number based on the value passed in.';
467 l_Rec_Version_Number := Nvl(P_Record_Version_number,0) + 1;
468
469 Pa_Debug.G_Stage := 'Derived next available version number for use in creating new working version record.';
470 Select
471 Nvl(Max(Version_Number),0) + 1
472 Into
473 l_Version_Number
474 From
475 Pa_Rbs_Versions_B
476 Where
477 Rbs_Header_Id = P_Rbs_Header_Id;
478
479 Pa_Debug.G_Stage := 'Get the Rbs Name from pa_rbs_headers_tl.';
480 Select
481 Name
482 Into
483 l_Name
484 From
485 Pa_Rbs_Headers_TL
486 Where
487 Rbs_Header_Id = P_Rbs_Header_Id
488 --MLS Changes
489 --And Source_lang = UserEnv('LANG');
490 And Language = UserEnv('LANG');
491
492 Pa_Debug.G_Stage := 'Create new version name based on the header name and avaliable version number.';
493 l_name := l_name || ' ' || to_char(l_Version_Number);
494
495 If l_Frozen_Rbs_Ver_Exists Then
496
497 Pa_Debug.G_Stage := 'Use the version end date of last frozen version to derive new start date adding 1 to it.';
498 GetDetails_Rec.Version_End_Date := GetDetails_Rec.Version_End_Date + 1;
499
500 Pa_Debug.G_Stage := 'Call Pa_Rbs_Versions_Pkg.Insert_Row() procedure from existing .';
501 Pa_Rbs_Versions_Pvt.Create_Working_Version_Record(
502 P_Mode => 'COPYING_FROZEN_VERSION',
503 P_Version_Number => l_Version_Number,
504 P_Rbs_Header_Id => P_Rbs_Header_Id,
505 P_Record_Version_Number => l_Rec_Version_Number,
506 P_Name => l_Name,
507 P_Description => GetDetails_Rec2.Description,
508 P_Version_Start_Date => GetDetails_Rec.Version_End_Date,
509 P_Version_End_Date => Null,
510 P_Job_Group_Id => GetDetails_Rec.Job_Group_Id,
511 P_Resource_Class_Flag => GetDetails_Rec.Resource_Class_Flag, --Added for CBS::13535688
512 P_Rule_Based_Flag => GetDetails_Rec.Rule_Based_Flag,
513 P_Validated_Flag => 'N',
514 P_Status_Code =>'WORKING',
515 --X_Record_Version_Number => l_Rec_Version_Number, --Bug: 4537865
516 X_Record_Version_Number => l_new_Rec_Version_Number, --Bug: 4537865
517 X_RBS_Version_Id => X_Rbs_Version_Id,
518 X_Error_Msg_Data => X_Error_Msg_Data);
519 --Bug: 4537865
520 l_Rec_Version_Number := l_new_Rec_Version_Number;
521 --Bug: 4537865
522
523 Else
524
525 Pa_Debug.G_Stage := 'This is the first version and the start date must match the header from date, so get from header.';
526 Open c_GetHdrFromDate(P_Hdr_Id => P_Rbs_Header_Id);
527 Fetch c_GetHdrFromDate Into l_Hdr_From_Date;
528 Close c_GetHdrFromDate;
529
530 Pa_Debug.G_Stage := 'Call Pa_Rbs_Versions_Pkg.Insert_Row() procedure brand new.';
531 Pa_Rbs_Versions_Pvt.Create_Working_Version_Record(
532 P_Mode => 'COPYING_FROZEN_VERSION',
533 P_Version_Number => l_Version_Number,
534 P_Rbs_Header_Id => P_Rbs_Header_Id,
535 P_Record_Version_Number => l_Rec_Version_Number,
536 P_Name => l_Name,
537 P_Description => Null,
538 P_Version_Start_Date => l_Hdr_From_Date,
539 P_Version_End_Date => Null,
540 P_Job_Group_Id => Null,
541 P_Resource_Class_Flag => Null,
542 P_Rule_Based_Flag => 'N',
543 P_Validated_Flag => 'N',
544 P_Status_Code =>'WORKING',
545 --X_Record_Version_Number => l_Rec_Version_Number, --Bug: 4537865
546 X_Record_Version_Number => l_new_Rec_Version_Number, --Bug: 4537865
547 X_RBS_Version_Id => X_Rbs_Version_Id,
548 X_Error_Msg_Data => X_Error_Msg_Data);
549 -- Bug: 4537865
550 l_Rec_Version_Number := l_new_Rec_Version_Number;
551 -- Bug: 4537865
552
553 End If;
554
555 Pa_Debug.G_Stage := 'Leaving Copy_Frozen_Rbs_Version() procedure.';
556 Pa_Debug.TrackPath('STRIP','Copy_Frozen_Rbs_Version');
557
558 Exception
559 When l_Record_Locked Then
560 X_Error_Msg_Data := 'PA_RECORD_ALREADY_UPDATED';
561
562 When Others Then
563 Raise;
564
565 End Copy_Frozen_Rbs_Version;
566
567
568 Procedure Copy_Frozen_Rbs_Elements(
569 P_Rbs_Version_From_Id IN Number,
570 P_Rbs_Version_To_Id IN Number,
571 X_Error_Msg_Data OUT NOCOPY Varchar2)
572
573 Is
574
575 --Bug 3592145
576 l_new_element_name_id Number;
577 l_dummy_error_status Varchar2(10);
578 l_Error Exception;
579
580 Begin
581
582 Pa_Debug.G_Stage := 'Entering Copy_Frozen_Rbs_Elements() procedure.';
583 Pa_Debug.TrackPath('ADD','Copy_Frozen_Rbs_Elements');
584
585 Pa_Debug.G_Stage := 'Delete all records from Pa_Rbs_Elements_Temp.';
586 Begin
587
588 Delete
589 From Pa_Rbs_Elements_Temp;
590
591 Exception
592 When No_Data_Found Then
593 null;
594
595 End;
596
597 Pa_Debug.G_Stage := 'Insert into Pa_Rbs_Elements_Temp that are to be copied.';
598 /*******************************************************
599 * Bug - 3591534
600 * Desc - while inserting into Pa_Rbs_Elements_Temp elements that
601 * are to be copied, we should only select elements where
602 * user_created flag = 'Y'.
603 ******************************************************/
604 Insert Into Pa_Rbs_Elements_Temp(
605 New_Element_Id,
606 Old_Element_Id,
607 Old_Parent_Element_Id,
608 New_Parent_Element_Id )
609 (Select
610 Pa_Rbs_Elements_S.NextVal,
611 Rbs_Element_Id,
612 Parent_Element_Id,
613 Null
614 From
615 Pa_Rbs_Elements
616 Where
617 Rbs_Version_Id = P_Rbs_Version_From_Id
618 and user_created_flag = 'Y' );
619
620 Pa_Debug.G_Stage := 'Update Pa_Rbs_Elements_Temp records with the correct parent element id.';
621 Update Pa_Rbs_Elements_Temp Tmp1
622 Set New_Parent_Element_Id =
623 (Select
624 New_Element_Id
625 From
626 Pa_Rbs_Elements_Temp Tmp2
627 Where
628 Tmp1.Old_Parent_Element_Id = Tmp2.Old_Element_Id);
629
630 Pa_Debug.G_Stage := 'Insert into Pa_Rbs_Elements new records.';
631
632 /*Bug 4377886 : Included explicitly the column names in the INSERT statement
633 to remove the GSCC Warning File.Sql.33 */
634 Insert Into Pa_Rbs_Elements
635 (
636 RBS_ELEMENT_ID,
637 RBS_ELEMENT_NAME_ID,
638 RBS_VERSION_ID,
639 OUTLINE_NUMBER,
640 ORDER_NUMBER,
641 RESOURCE_TYPE_ID,
642 RESOURCE_SOURCE_ID,
643 PERSON_ID,
644 JOB_ID,
645 ORGANIZATION_ID,
646 EXPENDITURE_TYPE_ID,
647 EVENT_TYPE_ID,
648 EXPENDITURE_CATEGORY_ID,
649 REVENUE_CATEGORY_ID,
650 inventory_item_id,
651 item_category_id,
652 bom_labor_id,
653 bom_equipment_id,
654 non_labor_resource_id,
655 role_id,
656 person_type_id,
657 resource_class_id,
658 supplier_id,
659 rule_flag,
660 PARENT_ELEMENT_ID,
661 rbs_level,
662 element_identifier,
663 user_defined_custom1_id,
664 user_defined_custom2_id,
665 user_defined_custom3_id,
666 user_defined_custom4_id,
667 user_defined_custom5_id,
668 USER_CREATED_FLAG,
669 LAST_UPDATE_DATE,
670 LAST_UPDATED_BY,
671 CREATION_DATE,
672 CREATED_BY,
673 LAST_UPDATE_LOGIN,
674 RECORD_VERSION_NUMBER
675 )
676 --For perf bug 4045542
677 Select /*+ ORDERED */
678 Tmp.New_Element_Id,
679 Rbs_Elements.Rbs_Element_Name_Id,
680 P_Rbs_Version_To_Id,
681 Rbs_Elements.Outline_Number,
682 Rbs_Elements.Order_Number,
683 Rbs_Elements.Resource_Type_Id,
684 Rbs_Elements.Resource_Source_Id,
685 Rbs_Elements.Person_Id,
686 Rbs_Elements.Job_Id,
687 Rbs_Elements.Organization_Id,
688 Rbs_Elements.Expenditure_Type_Id,
689 Rbs_Elements.Event_Type_Id,
690 Rbs_Elements.Expenditure_Category_Id,
691 Rbs_Elements.Revenue_Category_Id,
692 Rbs_Elements.Inventory_Item_Id,
693 Rbs_Elements.Item_Category_Id,
694 Rbs_Elements.Bom_Labor_Id,
695 Rbs_Elements.Bom_Equipment_Id,
696 Rbs_Elements.Non_Labor_Resource_Id,
697 Rbs_Elements.Role_Id,
698 Rbs_Elements.Person_Type_Id,
699 Rbs_Elements.Resource_Class_Id,
700 Rbs_Elements.Supplier_Id,
701 Rbs_Elements.Rule_Flag,
702 Tmp.New_Parent_Element_Id,
703 Rbs_Elements.Rbs_Level,
704 Rbs_Elements.Element_Identifier,
705 Rbs_Elements.User_Defined_Custom1_Id,
706 Rbs_Elements.User_Defined_Custom2_Id,
707 Rbs_Elements.User_Defined_Custom3_Id,
708 Rbs_Elements.User_Defined_Custom4_Id,
709 Rbs_Elements.User_Defined_Custom5_Id,
710 Rbs_Elements.User_Created_Flag,
711 Pa_Rbs_Versions_Pvt.G_Last_Update_Date,
712 Pa_Rbs_Versions_Pvt.G_Last_Updated_By,
713 Pa_Rbs_Versions_Pvt.G_Creation_Date,
714 Pa_Rbs_Versions_Pvt.G_Created_By,
715 Pa_Rbs_Versions_Pvt.G_Last_Update_Login,
716 1
717 From
718 Pa_Rbs_Elements_Temp Tmp,
719 Pa_Rbs_Elements Rbs_Elements
720 Where
721 Tmp.Old_Element_Id = Rbs_Elements.Rbs_Element_Id;
722
723 --Bug 3592145
724 --Deriving the element_name_id
725 Pa_Debug.G_Stage := 'Now getting a new rbs_element_name_id for the root element since it is based on rbs_version_id.';
726 Pa_Rbs_Utils.Populate_RBS_Element_Name (
727 P_Resource_Source_Id => P_Rbs_Version_To_Id,
728 P_Resource_Type_Id => -1,
729 X_Rbs_Element_Name_Id => l_new_element_name_id,
730 X_Return_Status => l_Dummy_Error_Status);
731
732 Pa_Debug.G_Stage := 'Check Pa_Rbs_Utils.Populate_RBS_Element_Name() returns error or not for getting ' ||
733 'root element element_name_id.';
734 If l_Dummy_Error_Status = Fnd_Api.G_Ret_Sts_Success Then
735
736 Pa_Debug.G_Stage := 'Updating the root element rbs record with the new element_name_id.';
737 Update Pa_Rbs_Elements
738 Set Rbs_Element_Name_Id = l_New_Element_Name_Id,
739 Resource_Source_Id = P_Rbs_Version_To_Id
740 Where Rbs_Version_Id = P_Rbs_Version_To_Id
741 And Resource_Type_Id = -1
742 And Rbs_Level = 1;
743
744 Else
745
746 Raise l_Error;
747
748 End If;
749
750 Pa_Debug.G_Stage := 'Leaving Copy_Frozen_Rbs_Elements() procedure.';
751 Pa_Debug.TrackPath('STRIP','Copy_Frozen_Rbs_Elements');
752
753 Exception
754 When Others Then
755 Raise;
756
757 End Copy_Frozen_Rbs_Elements;
758
759
760 /***************************************************
761 * Procedure : Set_Reporting_Flag
762 * Description : This procedure is used to set the
763 * current reporting flag for the version
764 * passed in as 'Y'. All other versions
765 * belonging to the same header will then
766 * have the current reporting flag set to
767 * Null.
768 ***************************************************/
769 Procedure Set_Reporting_Flag(
770 p_rbs_version_id IN Number,
771 x_return_status OUT NOCOPY Varchar2)
772 Is
773
774 l_rbs_header_id Number;
775
776 Begin
777
778
779 x_Return_Status := Fnd_Api.G_Ret_Sts_Success;
780 /*******************************************************
781 * First select the header_id that corresponds to this version.
782 * If multiple or no rows found then just set the x_return_status
783 * as Unexpected error and return.
784 ******************************************************/
785
786 Begin
787
788 Select
789 Rbs_Header_Id
790 Into
791 l_Rbs_Header_Id
792 From
793 Pa_Rbs_Versions_B
794 Where
795 Rbs_Version_Id = P_Rbs_Version_Id;
796
797 Exception
798 When Others Then
799 x_Return_Status := Fnd_Api.G_Ret_Sts_UnExp_Error;
800 Return;
801 End;
802
803 Update
804 Pa_Rbs_Versions_B
805 Set
806 Current_Reporting_Flag = Null
807 Where
808 Rbs_Header_Id = l_Rbs_Header_Id;
809
810 Update
811 Pa_Rbs_Versions_B
812 Set
813 Current_Reporting_Flag = 'Y'
814 Where
815 Rbs_Header_Id = l_Rbs_Header_Id
816 And Rbs_Version_Id = P_Rbs_Version_Id;
817
818 Exception
819 When Others Then
820 X_Return_Status := Fnd_Api.G_Ret_Sts_UnExp_Error;
821 Return;
822
823 End Set_Reporting_Flag;
824
825
826 END Pa_Rbs_Versions_Pvt;