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