1 package body Pa_Rbs_Versions_Pub as
2 --$Header: PARBSVPB.pls 120.3 2011/12/28 11:17:00 djambhek ship $
3
4 g_module_name VARCHAR2(100) := 'pa.plsql.Freeze_Working_Version';
5
6 -- =======================================================================
7 -- Start of Comments
8 -- API Name : Update_Working_Version
9 -- Type : Public
10 -- Pre-Reqs : None
11 -- Type : Procedure
12 -- Function : This procedure allows for the update of the current working version for a rbs header.
13 --
14 -- Parameters:
15 --
16 -- IN
17 -- P_Commit - Varchar2 Default Fnd_Api.G_False
18 -- P_Init_Msg_List - Varchar2 Default Fnd_Api.G_True
19 -- P_Api_Version_Number - Number
20 -- P_RBS_Version_Id - Number
21 -- P_Name - Varchar2(240)
22 -- P_Description - Varchar2(2000)
23 -- P_Version_Start_Date - Date
24 -- P_Job_Group_Id - Number
25 -- P_Record_Version_Number - Number
26 -- P_Init_Debugging_Flag - Varchar2 Default 'Y'
27 -- OUT
28 -- X_Record_Version_Number - Number
29 -- X_Return_Status - Varchar2(1)
30 -- X_Msg_Count - Number
31 -- X_Error_Msg_Data - Varchar2(30)
32 /*-------------------------------------------------------------------------*/
33
34 Procedure Update_Working_Version(
35 P_Commit IN Varchar2 Default Fnd_Api.G_False,
36 P_Init_Msg_List IN Varchar2 Default Fnd_Api.G_True,
37 P_API_Version_Number IN Number,
38 P_RBS_Version_Id IN Number,
39 P_Name IN Varchar2,
40 P_Description IN Varchar2,
41 P_Version_Start_Date IN Date,
42 P_Job_Group_Id IN Number,
43 P_Record_Version_Number IN Number,
44 P_Res_Class_Flag IN Varchar2 Default 'Y', --Added for CBS::13535688
45 P_Init_Debugging_Flag IN Varchar2 Default 'Y',
46 X_Record_Version_Number OUT NOCOPY Number,
47 X_Return_Status OUT NOCOPY Varchar2,
48 X_Msg_Count OUT NOCOPY Number,
49 X_Error_Msg_Data OUT NOCOPY Varchar2)
50
51 Is
52
53 l_Api_Name Varchar2(30) := 'Update_Working_Version';
54 l_Error Exception;
55
56 Cursor c1 (P_Rbs_Ver_Id IN Number,
57 P_Rec_Num IN Number) Is
58 Select
59 Status_Code,
60 Job_Group_Id
61 From
62 Pa_Rbs_Versions_B
63 Where
64 Rbs_Version_Id = P_Rbs_Version_Id
65 And Status_Code = 'WORKING'
66 And Record_Version_Number = P_Rec_Num
67 For Update Of Version_Start_Date NoWait;
68
69 l_Ver_Rec c1%RowType;
70
71 Cursor c2 (P_Rbs_Ver_Id IN Number) Is
72 Select
73 H.Effective_From_Date,
74 H.Effective_To_Date,
75 H.Rbs_Header_Id,
76 TL.Name
77 From
78 Pa_Rbs_Headers_B H,
79 Pa_Rbs_Headers_TL TL,
80 Pa_Rbs_Versions_B V
81 Where
82 TL.Rbs_Header_Id = H.Rbs_Header_Id
83 And UserEnv('LANG') in (TL.Language, TL.Source_Lang)
84 And H.Rbs_Header_Id = V.Rbs_Header_Id
85 And V.Rbs_Version_Id = P_Rbs_Ver_Id;
86
87 l_Hdr_Rec c2%RowType;
88
89 Cursor GetLatestFrozenRbsVersionId(P_Id IN Number) Is
90 Select
91 Max(Rbs_Version_Id)
92 From
93 Pa_Rbs_Versions_B
94 Where
95 Rbs_Header_Id = P_Id
96 And Status_Code <> 'WORKING';
97
98 Cursor GetVersionEndDate(P_Id IN Number) Is
99 Select
100 Version_Start_Date,
101 Version_End_Date
102 From
103 Pa_Rbs_Versions_B
104 Where
105 Rbs_Version_Id = P_Id;
106
107 l_Prior_Rbs_Ver_Id Number(15) := Null;
108 l_Prior_Rbs_Ver_Rec GetVersionEndDate%RowType;
109
110 Cursor CheckJobsExist(P_Rbs_Ver_Id IN Number) Is
111 Select
112 Count(*)
113 From
114 Pa_Rbs_Elements
115 Where
116 Job_Id Is Not Null
117 And User_Created_Flag = 'Y'
118 And Rbs_Version_Id = P_Rbs_Ver_Id;
119
120 l_Job_Count Number := 0;
121
122 Begin
123
124 If P_Init_Debugging_Flag = 'Y' Then
125
126 Pa_Debug.G_Path := ' ';
127
128 End If;
129
130 Pa_Debug.G_Stage := 'Entering Update_Working_Version() Pub.';
131 Pa_Debug.TrackPath('ADD','Update_Working_Version Pub');
132
133 Pa_Debug.G_Stage := 'Call Compatibility API.';
134 If Not Fnd_Api.Compatible_API_Call (
135 Pa_Rbs_Versions_Pub.G_Api_Version_Number,
136 P_Api_Version_Number,
137 l_Api_Name,
138 Pa_Rbs_Versions_Pub.G_Pkg_Name) Then
139
140 Raise Fnd_Api.G_Exc_Unexpected_Error;
141
142 End If;
143
144 Pa_Debug.G_Stage := 'Check if need to initialize message stack.';
145 If Fnd_Api.To_Boolean(nvl(P_Init_Msg_List,Fnd_Api.G_True)) Then
146
147 Pa_Debug.G_Stage := 'Initialize Message Stack.';
148 Fnd_Msg_Pub.Initialize;
149
150 End If;
151
152 Pa_Debug.G_Stage := 'Initialize error handling variables.';
153 X_Msg_Count := 0;
154 X_Error_Msg_Data := Null;
155 X_Return_Status := Fnd_Api.G_Ret_Sts_Success;
156
157 Pa_Debug.G_Stage := 'Lock the version record.';
158 Open c1(P_Rbs_Ver_Id => P_Rbs_Version_Id, P_Rec_Num => P_Record_Version_Number);
159 Fetch c1 Into l_Ver_Rec;
160
161 Pa_Debug.G_Stage := 'Check able to lock version rec for update.';
162 If c1%NotFound Then
163
164 Pa_Debug.G_Stage := 'Unable to lock version record for update. ' ||
165 'Add error message to stack.';
166 Pa_Utils.Add_Message(
167 P_App_Short_Name => 'PA',
168 P_Msg_Name => 'PA_RECORD_ALREADY_UPDATED');
169 Close c1;
170 Raise l_Error;
171
172 End If;
173
174 Open c2(P_Rbs_Ver_Id => P_Rbs_Version_Id);
175 Fetch c2 Into l_Hdr_Rec;
176 Close c2;
177
178 Pa_Debug.G_Stage := 'Check if the version start date is not null.';
179 If P_Version_Start_Date is not null Then
180
181 Pa_Debug.G_Stage := 'The version start date is Null.';
182 If P_Version_Start_Date < l_Hdr_Rec.Effective_From_Date Then
183
184 Pa_Debug.G_Stage := 'The version start date is before the header from date.';
185 Pa_Utils.Add_Message(
186 P_App_Short_Name => 'PA',
187 P_Msg_Name => 'PA_VER_START_<_HDR_FROM_DATE',
188 P_Token1 => 'RBSNAME',
189 P_Value1 => l_Hdr_Rec.Name);
190
191 Raise l_Error;
192
193 End If;
194
195 Else
196
197 Pa_Debug.G_Stage := 'The version start date is null. Add error message to stack.';
198 Pa_Utils.Add_Message(
199 P_App_Short_Name => 'PA',
200 P_Msg_Name => 'PA_VER_START_DATE_IS_NULL',
201 P_Token1 => 'RBSNAME',
202 P_Value1 => l_Hdr_Rec.Name);
203
204 Raise l_Error;
205
206 End If;
207
208 -- Per bug 3602821 don't need to check the end date of the header for anything.
209 -- Pa_Debug.G_Stage := 'Check and see if the version start date is after the header end date.';
210 -- If P_Version_Start_Date > Nvl(l_Hdr_Rec.Effective_To_Date,P_Version_Start_Date) Then
211
212 -- Pa_Debug.G_Stage := 'The version start date is after the header end date.';
213 -- Pa_Utils.Add_Message(
214 -- P_App_Short_Name => 'PA',
215 -- P_Msg_Name => 'PA_VER_START_>_HDR_TO_DATE',
216 -- P_Token1 => 'RBSNAME',
217 -- P_Value1 => l_Hdr_Rec.Name);
218
219 -- Raise l_Error;
220
221 -- End If;
222
223 Pa_Debug.G_Stage := 'Get the prior working version rbs version id if exists.';
224 Open GetLatestFrozenRbsVersionId(P_Id => l_Hdr_Rec.Rbs_Header_Id);
225 Fetch GetLatestFrozenRbsVersionId Into l_Prior_Rbs_Ver_Id;
226
227 If GetLatestFrozenRbsVersionId%NotFound Then
228
229 Close GetLatestFrozenRbsVersionId;
230
231 Else
232
233 Close GetLatestFrozenRbsVersionId;
234
235 Pa_Debug.G_Stage := 'Get the prior working version end date.';
236 Open GetVersionEndDate(P_Id => l_Prior_Rbs_Ver_Id);
237 Fetch GetVersionEndDate Into l_Prior_Rbs_Ver_Rec;
238 Close GetVersionEndDate;
239
240 If P_Version_Start_Date < l_Prior_Rbs_Ver_Rec.Version_Start_Date Then
241
242 Pa_Debug.G_Stage := 'Version Start Date less than prior version. Add error message to stack.';
243 Pa_Utils.Add_Message(
244 P_App_Short_Name => 'PA',
245 P_Msg_Name => 'PA_RBS_VER_DATES_OVERLAP');
246
247 Raise l_Error;
248
249 End If;
250
251 End If;
252
253 -- Check If the job_group has changed and if it has then check to see if there are job assigned to any of the
254 -- elements/nodes for the rbs. If there are job elements then raise an error. You can't change the job_group
255 -- when there are element/nodes with jobs assigned to them.
256
257 Pa_Debug.G_Stage := 'Check if the current working record job group is populated.';
258 If l_Ver_Rec.Job_Group_Id Is Not Null Then
259
260 Pa_Debug.G_Stage := 'Check if the current parameter job group is populated.';
261 If P_Job_Group_Id Is Not Null Then
262
263 If P_Job_Group_Id <> l_Ver_Rec.Job_Group_Id Then
264
265 Open CheckJobsExist(P_Rbs_Ver_Id => P_Rbs_Version_Id);
266 Fetch CheckJobsExist Into l_Job_Count;
267 Close CheckJobsExist;
268
269 If l_Job_Count > 0 Then
270
271 Pa_Debug.G_Stage := 'There are elements with jobs. Cannot change job group. Raise error.';
272 Pa_Utils.Add_Message(
273 P_App_Short_Name => 'PA',
274 P_Msg_Name => 'PA_RBS_CANT_CHANGE_JOB_GROUP');
275
276 Raise l_Error;
277
278 End If;
279
280 End If;
281
282 Else
283
284 Open CheckJobsExist(P_Rbs_Ver_Id => P_Rbs_Version_Id);
285 Fetch CheckJobsExist Into l_Job_Count;
286 Close CheckJobsExist;
287
288 If l_Job_Count > 0 Then
289
290 Pa_Debug.G_Stage := 'There are element with jobs. Cannot change job group. Raise error.';
291 Pa_Utils.Add_Message(
292 P_App_Short_Name => 'PA',
293 P_Msg_Name => 'PA_RBS_CANT_CHANGE_JOB_GROUP');
294
295 Raise l_Error;
296
297 End If;
298
299 End If;
300
301 End If;
302
303 Pa_Debug.G_Stage := 'Call Pa_Rbs_Versions_Pvt.Update_Working_Version() procedure.';
304 Pa_Rbs_Versions_Pvt.Update_Working_Version(
305 P_Rbs_Version_Id => P_Rbs_Version_Id,
306 P_Name => P_Name,
307 P_Description => P_Description,
308 P_Version_Start_Date => P_Version_Start_Date,
309 P_Job_Group_Id => P_Job_Group_Id,
310 P_Record_Version_Number => P_Record_Version_Number,
311 P_Res_Class_Flag => P_Res_Class_Flag, --Added for CBS::13535688
312 X_Record_Version_Number => X_Record_Version_Number,
313 X_Error_Msg_Data => X_Error_Msg_Data);
314
315 If X_Error_Msg_Data Is Not Null Then
316
317 Raise l_Error;
318
319 End If;
320
321 Pa_Debug.G_Stage := 'Check to do commit(T-True,F-False) - ' || P_Commit;
322 If Fnd_Api.To_Boolean(Nvl(P_Commit,Fnd_Api.G_False)) Then
323
324 Commit;
325
326 End If;
327
328 Pa_Debug.G_Stage := 'Leaving Update_Working_Version() Pub procedure.';
329 Pa_Debug.TrackPath('STRIP','Update_Working_Version Pub');
330
331 Exception
332 When l_Error Then
333 X_Return_Status := 'E';
334 X_Msg_Count := 1;
335 Pa_Debug.G_Stage := 'Leaving Update_Working_Version() Pub procedure.';
336 Pa_Debug.TrackPath('STRIP','Update_Working_Version Pub');
337 When Others Then
338 X_Return_Status := 'U';
339 X_Msg_Count := 1;
340 X_Error_Msg_Data := Pa_Rbs_Versions_Pub.G_Pkg_Name || ':::' || Pa_Debug.G_Path || '::' || Pa_Debug.G_Stage ||
341 ':' || SqlErrm;
342 If P_Init_Debugging_Flag = 'Y' Then
343 Rollback;
344 Else
345 Raise;
346 End If;
347
348 End Update_Working_Version;
349
350
351 -- =======================================================================
352 -- Start of Comments
353 -- API Name : Delete_Working_Version
354 -- Type : Public
355 -- Pre-Reqs : None
356 -- Type : Procedure
357 -- Function : This procedure deletes the working rbs version as well as it elements/nodes.
358 --
359 -- Parameters:
360 --
361 -- IN
362 -- P_Commit - Varchar2 Default Fnd_Api.G_False
363 -- P_Init_Msg_List - Varchar2 Default Fnd_Api.G_True
364 -- P_Api_Version_Number - Number
365 -- P_RBS_Version_Id - Number(15)
366 -- P_Record_Version_Number - Number(15)
367 -- OUT
368 -- X_Return_Status - Varchar2(1)
369 -- X_Msg_Count - Number
370 -- X_Error_Msg_Data - Varchar2(30)
371 --
372 /*-------------------------------------------------------------------------*/
373
374 Procedure Delete_Working_Version(
375 P_Commit IN Varchar2 Default Fnd_Api.G_False,
376 P_Init_Msg_List IN Varchar2 Default Fnd_Api.G_True,
377 P_Api_Version_Number IN Number,
378 P_RBS_Version_Id IN Number,
379 P_Record_Version_Number IN Number,
380 X_Return_Status OUT NOCOPY Varchar2,
381 X_Msg_Count OUT NOCOPY Number,
382 X_Error_Msg_Data OUT NOCOPY Varchar2)
383
384 Is
385
386 l_Api_Name Varchar2(30) := 'Delete_Working_Version';
387 l_Error Exception;
388
389 Begin
390
391
392 Pa_Debug.G_Path := ' ';
393
394 Pa_Debug.G_Stage := 'Entering Delete_Working_Version() Pub.';
395 Pa_Debug.TrackPath('ADD','Delete_Working_Version Pub');
396
397 Pa_Debug.G_Stage := 'Call Compatibility API.';
398 If Not Fnd_Api.Compatible_API_Call (
399 Pa_Rbs_Versions_Pub.G_Api_Version_Number,
400 P_Api_Version_Number,
401 l_Api_Name,
402 Pa_Rbs_Versions_Pub.G_Pkg_Name) Then
403
404 Raise Fnd_Api.G_Exc_Unexpected_Error;
405
406 End If;
407
408 Pa_Debug.G_Stage := 'Check if need to initialize message stack.';
409 If Fnd_Api.To_Boolean(nvl(P_Init_Msg_List,Fnd_Api.G_True)) Then
410
411 Pa_Debug.G_Stage := 'Initialize Message Stack.';
412 Fnd_Msg_Pub.Initialize;
413
414 End If;
415
416 Pa_Debug.G_Stage := 'Initialize error handling variables.';
417 X_Msg_Count := 0;
418 X_Error_Msg_Data := Null;
419 X_Return_Status := Fnd_Api.G_Ret_Sts_Success;
420
421 Pa_Debug.G_Stage := 'Call Pa_Rbs_Versions_Pvt.Delete_Working_Version() procedure.';
422 Pa_Rbs_Versions_Pvt.Delete_Working_Version(
423 P_Rbs_Version_Id => P_RBS_Version_Id,
424 P_Record_Version_Number => P_Record_Version_Number,
425 P_Mode => Null,
426 X_Error_Msg_Data => X_Error_Msg_Data);
427
428 Pa_Debug.G_Stage := 'Check if error message data is populated.';
429 If X_Error_Msg_Data Is Not Null Then
430
431 Pa_Debug.G_Stage := 'Raise user defined error due to error msg data parameter being populated.';
432 Raise l_error;
433
434 End If;
435
436 Pa_Debug.G_Stage := 'Check to do commit(T-True,F-False) - ' || P_Commit;
437 If Fnd_Api.To_Boolean(Nvl(P_Commit,Fnd_Api.G_False)) Then
438
439 Commit;
440
441 End If;
442
443 Pa_Debug.G_Stage := 'Leaving Delete_Working_Version() Pub procedure.';
444 Pa_Debug.TrackPath('STRIP','Delete_Working_Version Pub');
445
446 Exception
447 When l_Error Then
448 X_Return_Status := 'E';
449 X_Msg_Count := 1;
450 When Others Then
451 X_Return_Status := 'U';
452 X_Msg_Count := 1;
453 X_Error_Msg_Data := Pa_Rbs_Versions_Pub.G_Pkg_Name || ':::' || Pa_Debug.G_Path || '::' || Pa_Debug.G_Stage ||
454 ':' || SqlErrm;
455 Rollback;
456
457 End Delete_Working_Version;
458
459
460 -- =======================================================================
461 -- Start of Comments
462 -- API Name : Create_Working_Version
463 -- Type : Public
464 -- Pre-Reqs : None
465 -- Type : Procedure
466 -- Function : This procedure create a new working rbs version based on a previously frozen rbs version.
467 --
468 -- Parameters:
469 --
470 -- IN
471 -- P_Commit - Varchar2 Default Fnd_Api.G_False,
472 -- P_Init_Msg_List - Varchar2 Default Fnd_Api.G_True,
473 -- P_Api_Version_Number - Number
474 -- P_RBS_Version_Id - Number(15) is the frozen version id to copy from(should not be null copying)
475 -- P_Rbs_Header_Id - Number(15) is Header for the frozen and the working version
476 -- P_Rec_Version_Number - Number(15) is for the current working version
477 -- OUT
478 -- X_Return_Status - Varchar2(1)
479 -- X_Msg_Count - Number
480 -- X_Error_Msg_Data - Varchar2(30)
481 --
482 /*-------------------------------------------------------------------------*/
483
484 Procedure Create_Working_Version (
485 P_Commit IN Varchar2 Default Fnd_Api.G_False,
486 P_Init_Msg_List IN Varchar2 Default Fnd_Api.G_True,
487 P_Api_Version_Number IN Number,
488 P_RBS_Version_Id IN Number Default Null,
489 P_Rbs_Header_Id IN Number,
490 P_Rec_Version_Number IN Number Default Null,
491 P_Init_Debugging_Flag IN Varchar2 Default 'Y',
492 X_Return_Status OUT NOCOPY Varchar2,
493 X_Msg_Count OUT NOCOPY Number,
494 X_Error_Msg_Data OUT NOCOPY Varchar2 )
495
496 Is
497
498 l_Api_Name Varchar2(30) := 'Create_Working_Version';
499 l_Error Exception;
500
501 Begin
502
503 If P_Init_Debugging_Flag = 'Y' Then
504 Pa_Debug.G_Path := ' ';
505 End If;
506
507 Pa_Debug.G_Stage := 'Entering Create_Working_Version().';
508 Pa_Debug.TrackPath('ADD','Create_Working_Version');
509
510 Pa_Debug.G_Stage := 'Call Compatibility API.';
511 If Not Fnd_Api.Compatible_API_Call (
512 Pa_Rbs_Versions_Pub.G_Api_Version_Number,
513 P_Api_Version_Number,
514 l_Api_Name,
515 Pa_Rbs_Versions_Pub.G_Pkg_Name) Then
516
517 Raise Fnd_Api.G_Exc_Unexpected_Error;
518
519 End If;
520
521 Pa_Debug.G_Stage := 'Check if need to initialize message stack.';
522 If Fnd_Api.To_Boolean(nvl(P_Init_Msg_List,Fnd_Api.G_True)) Then
523
524 Pa_Debug.G_Stage := 'Initialize Message Stack.';
525 Fnd_Msg_Pub.Initialize;
526
527 End If;
528
529 Pa_Debug.G_Stage := 'Initialize error handling variables.';
530 X_Msg_Count := 0;
531 X_Error_Msg_Data := Null;
532 X_Return_Status := Fnd_Api.G_Ret_Sts_Success;
533
534 Pa_Debug.G_Stage := 'Call Pa_Rbs_Versions_Pvt.Create_New_Working_Version() procedure.';
535 Pa_Rbs_Versions_Pvt.Create_New_Working_Version(
536 P_RBS_Version_Id => P_RBS_Version_Id,
537 P_Rbs_Header_Id => P_Rbs_Header_Id,
538 P_Record_Version_Number => P_Rec_Version_Number,
539 X_Error_Msg_Data => X_Error_Msg_Data);
540
541 Pa_Debug.G_Stage := 'Check if error message data is populated.';
542 If X_Error_Msg_Data Is Not Null Then
543
544 Pa_Debug.G_Stage := 'Raise user defined error due to error msg data parameter being populated.';
545 Raise l_error;
546
547 End If;
548
549 Pa_Debug.G_Stage := 'Check to do commit(T-True,F-False) - ' || P_Commit;
550 If Fnd_Api.To_Boolean(Nvl(P_Commit,Fnd_Api.G_False)) Then
551
552 Commit;
553
554 End If;
555
556 Pa_Debug.G_Stage := 'Leaving Create_Working_Version() procedure.';
557 Pa_Debug.TrackPath('STRIP','Create_Working_Version');
558
559 Exception
560 When l_Error Then
561 X_Return_Status := 'E';
562 X_Msg_Count := 1;
563 When Others Then
564 X_Return_Status := 'U';
565 X_Msg_Count := 1;
566 X_Error_Msg_Data := Pa_Rbs_Versions_Pub.G_Pkg_Name || ':::' || Pa_Debug.G_Path || '::' || Pa_Debug.G_Stage ||
567 ':' || SqlErrm;
568 If P_Init_Debugging_Flag = 'Y' Then
569 Rollback;
570 Else
571 Raise;
572 End If;
573
574 End Create_Working_Version;
575
576
577 -- =======================================================================
578 -- Start of Comments
579 -- API Name : Freeze_Working_Version
580 -- Type : Public
581 -- Pre-Reqs : None
582 -- Type : Procedure
583 -- Function : This procedure freezes working version and creates a new working version based on this frozen rbs version.
584 -- If the RBS has no rules as its element then a copy of the version hierarchy is created with user_created_flag=N
585 -- for RBS usage in allocation rules.
586 --
587 -- Parameters:
588 --
589 -- IN
590 -- P_Commit - Varchar2 Default Fnd_Api.G_False,
591 -- P_Init_Msg_List - Varchar2 Default Fnd_Api.G_True,
592 -- P_Rbs_Version_Id - Number(15) is the version id of the version to be freezed
593 -- P_Rbs_Version_Record_Ver_Num - Number(15) is record version number of the version to be freezed
594 -- P_Init_Debugging_Flag - Vachar2(1)
595 -- OUT
596 -- X_Return_Status - Varchar2(1)
597 -- X_Msg_Count - Number
598 -- X_Error_Msg_Data - Varchar2(30)
599 --
600
601 PROCEDURE Freeze_Working_Version(
602 P_Commit IN Varchar2 Default Fnd_Api.G_False,
603 P_Init_Msg_List IN Varchar2 Default Fnd_Api.G_True,
604 P_Rbs_Version_Id IN Number,
605 P_Rbs_Version_Record_Ver_Num IN Number Default Null,
606 P_Init_Debugging_Flag IN Varchar2 Default 'Y',
607 X_Return_Status OUT NOCOPY Varchar2,
608 X_Msg_Count OUT NOCOPY Number,
609 X_Error_Msg_Data OUT NOCOPY Varchar2)
610 IS
611
612 l_version_start_date Date;
613 l_end_date Date := Null;
614 l_latest_freezed_version_id Number;
615 l_Error Exception;
616 l_Rbs_Header_Id Number := Null;
617 l_Rec_Version_Number Number := Null;
618
619 l_msg_count Number := 0;
620 l_data Varchar2(2000);
621 l_msg_data Varchar2(2000);
622 l_msg_index_out Number;
623 l_old_rbs_version_id Number := Null;
624 l_new_rbs_version_id Number := Null;
625 l_project_id Number := Null;
626 l_count Number := Null;
627 l_assoc_count Number := Null;
628 --This cursor gets the latest frozen versions Rbs_version_id
629 Cursor GetLatestFrozenRbsVersionId(l_Rbs_Header_Id IN Number) Is
630 Select
631 Max(Rbs_Version_Id)
632 From
633 Pa_Rbs_Versions_B
634 Where
635 Rbs_Header_Id = l_Rbs_Header_Id
636 And Status_Code <> 'WORKING';
637
638 Cursor c_GetVersionDates(P_Rbs_Ver_Id IN Number) Is
639 Select
640 Version_Start_Date,
641 Nvl(Version_End_Date,SysDate) Version_End_Date
642 From
643 Pa_Rbs_Versions_B
644 Where
645 Rbs_Version_Id = P_Rbs_Ver_Id;
646
647 l_Ver_Dates_Rec c_GetVersionDates%RowType;
648
649 Cursor c_Record_Ver_No is
650 Select
651 Record_Version_Number
652 From
653 Pa_Rbs_Versions_B
654 Where
655 Rbs_Version_Id = P_Rbs_Version_Id
656 For Update Of Version_Start_Date NoWait;
657
658 RecInfo c_Record_Ver_No%RowType;
659
660 l_Api_Name Varchar2(30) := 'Freeze_Working_Version';
661 l_Api_Version_Number Number := Null;
662 --l_use_for_alloc_flag VARCHAR2(1);
663
664 BEGIN
665
666 If P_Init_Debugging_Flag = 'Y' Then
667 Pa_Debug.G_Path := ' ';
668 End If;
669
670 Pa_Debug.G_Stage := 'Entering Freeze_Working_Version().';
671 Pa_Debug.TrackPath('ADD','Freeze_Working_Version');
672
673 --Initialize the message stack if not initialized
674 Pa_Debug.G_Stage := 'Check if need to initialize the error message stack.';
675 If Fnd_Api.To_Boolean(nvl(P_Init_Msg_List,Fnd_Api.G_True)) Then
676
677 Pa_Debug.G_Stage := 'Initialize the error message stack by calling Fnd_Msg_Pub.Initialize().';
678 Fnd_Msg_Pub.Initialize;
679
680 End If;
681
682 Pa_Debug.G_Stage := 'Initialize error handling variables.';
683 X_Msg_Count := 0;
684 X_Error_Msg_Data := Null;
685 X_Return_Status := Fnd_Api.G_Ret_Sts_Success;
686
687
688 Pa_Debug.G_Stage := 'Check if the version record is locked.';
689 Open c_record_ver_no;
690 Fetch c_record_ver_no Into recinfo;
691
692 If (c_Record_Ver_No%NotFound) Then
693 Close c_Record_Ver_No;
694 End If;
695
696 Close c_record_ver_no;
697
698 Pa_Debug.G_Stage := 'Check if Rbs Version record_version_number matches.';
699 IF RecInfo.Record_Version_Number = P_Rbs_Version_Record_Ver_Num Then
700
701 Null;
702
703 Else
704
705 Pa_Debug.G_Stage := 'Rbs Version record_version_number does not match. Add error to message stack.';
706 x_return_status := Fnd_Api.G_Ret_Sts_Error;
707
708 Pa_Utils.Add_Message(
709 P_App_Short_Name => 'FND', /* Bug 3819654: Changed short name from PA to FND */
710 P_Msg_Name => 'FND_LOCK_RECORD_ERROR');
711
712 l_msg_count := Fnd_Msg_Pub.Count_Msg;
713 If l_Msg_Count = 1 Then
714
715 Pa_Interface_Utils_Pub.Get_Messages(
716 P_Encoded => Fnd_Api.G_True,
717 P_Msg_Index => 1,
718 P_Msg_Count => l_Msg_Count,
719 P_Msg_Data => l_Msg_Data,
720 P_Data => l_Data,
721 P_Msg_Index_Out => l_Msg_Index_Out);
722
723 X_Error_Msg_Data := l_Data;
724 X_Msg_Count := l_Msg_Count;
725
726 Else
727
728 X_Msg_Count := l_Msg_Count;
729
730 End If;
731
732 Raise l_error;
733
734 END IF;
735
736 Pa_Debug.G_Stage := 'Get the Version_Start_Date and Rbs_Header_Id of working version.';
737 Select
738 Version_Start_Date,
739 Rbs_Header_Id
740 Into
741 l_Version_Start_Date,
742 l_Rbs_Header_Id
743 From
744 Pa_Rbs_Versions_B
745 Where
746 Rbs_Version_Id = P_Rbs_Version_Id;
747
748 --commented out since checking of this flag is not needed to copy hierarchy of elements when we freeze a version
749 -- For bug 3659078
750 --SELECT
751 -- USE_FOR_ALLOC_FLAG
752 --INTO
753 -- l_use_for_alloc_flag
754 --FROM
755 -- Pa_Rbs_Headers_b
756 --Where
757 -- Rbs_Header_Id=l_Rbs_Header_Id;
758
759 Pa_Debug.G_Stage:= 'Getting version id of the latest freezed version';
760 Open GetLatestFrozenRbsVersionId(l_Rbs_Header_Id);
761 Fetch GetLatestFrozenRbsVersionId Into l_latest_freezed_version_id;
762 Close GetLatestFrozenRbsVersionId;
763
764 --If either resource type id or resource source id is different for each element of working version compared to latest,
765 --freezed version,give them new element identifiers.
766
767 Pa_Debug.G_Stage:= 'Updating element identifiers of the rbs elements for the working version which do not match latest freeze.';
768 -- Bug 3635614 changed the update statement by removing the start with and connect by prior clause and used where clause instead
769 Update pa_rbs_elements
770 Set
771 Element_Identifier = Pa_Rbs_Element_Identifier_S.NextVal
772 Where
773 Rbs_Element_Id IN (
774 Select
775 Distinct a.Rbs_Element_Id
776 From
777 Pa_Rbs_Elements a,
778 Pa_Rbs_Elements b
779 Where
780 a.Element_Identifier = b.Element_Identifier
781 And a.Rbs_Version_Id = P_Rbs_Version_Id
782 And b.Rbs_Version_Id = l_Latest_Freezed_Version_Id
783 And a.Resource_Type_Id <> -1
784 And b.Resource_Type_id <> -1
785 And ( a.Resource_Source_Id <> b.Resource_Source_Id Or
786 a.Resource_Type_Id <> b.Resource_Type_Id) );
787
788 -- For Bug 3659078
789 --Description
790 --When the RBS is frozen, we will create a copy of that
791 --hierarchy with "user_created_flag = 'N' if use_for_alloc_flag is set to Y.
792
793 --The above logic is changed. We copy hierarchy only when we freeze a RBS having no rules as its elements
794 --irrespective of the use_for_alloc_flag value.
795
796 IF PA_RBS_HEADER_PVT.Validate_Rbs_For_Allocations(p_rbs_id=>l_Rbs_Header_Id)='N' Then --When RBS has no rules as its element
797 --If l_use_for_alloc_flag='Y' Then
798
799 Pa_Debug.G_Stage:= 'Copying All Elements With user_created_Flag = N';
800
801 Pa_Debug.G_Stage:= 'Delete All Records From Pa_Rbs_Elements_Temp';
802 Begin
803
804 Delete
805 From Pa_Rbs_Elements_Temp;
806
807 Exception
808 When No_Data_Found Then
809 null;
810
811 End;
812
813 Pa_Debug.G_Stage:= 'Insert Pa_Rbs_Elements_Temp table with new element_ids';
814
815 Insert Into Pa_Rbs_Elements_Temp(
816 New_Element_Id,
817 Old_Element_Id,
818 Old_Parent_Element_Id,
819 New_Parent_Element_Id )
820 (Select
821 Pa_Rbs_Elements_S.NextVal,
822 Rbs_Element_Id,
823 Parent_Element_Id,
824 Null
825 From
826 Pa_Rbs_Elements
827 Where
828 Rbs_Version_Id = P_Rbs_Version_Id
829 and user_created_flag = 'Y' );
830
831 Update Pa_Rbs_Elements_Temp Tmp1
832 Set New_Parent_Element_Id =
833 (Select
834 New_Element_Id
835 From
836 Pa_Rbs_Elements_Temp Tmp2
837 Where
838 Tmp1.Old_Parent_Element_Id = Tmp2.Old_Element_Id);
839
840 Pa_Debug.G_Stage:= 'Insert all records with user_created_flag=N with new element ids updated with new parent element ids';
841
842 /*Bug 4377886 : Included explicitly the column names in the INSERT statement
843 to remove the GSCC Warning File.Sql.33 */
844 Insert Into pa_rbs_elements
845 (
846 RBS_ELEMENT_ID,
847 RBS_ELEMENT_NAME_ID,
848 RBS_VERSION_ID,
849 OUTLINE_NUMBER,
850 ORDER_NUMBER,
851 RESOURCE_TYPE_ID,
852 RESOURCE_SOURCE_ID,
853 PERSON_ID,
854 JOB_ID,
855 ORGANIZATION_ID,
856 EXPENDITURE_TYPE_ID,
857 EVENT_TYPE_ID,
858 EXPENDITURE_CATEGORY_ID,
859 REVENUE_CATEGORY_ID,
860 inventory_item_id,
861 item_category_id,
862 bom_labor_id,
863 bom_equipment_id,
864 non_labor_resource_id,
865 role_id,
866 person_type_id,
867 resource_class_id,
868 supplier_id,
869 rule_flag,
870 PARENT_ELEMENT_ID,
871 rbs_level,
872 element_identifier,
873 user_defined_custom1_id,
874 user_defined_custom2_id,
875 user_defined_custom3_id,
876 user_defined_custom4_id,
877 user_defined_custom5_id,
878 USER_CREATED_FLAG,
879 LAST_UPDATE_DATE,
880 LAST_UPDATED_BY,
881 CREATION_DATE,
882 CREATED_BY,
883 LAST_UPDATE_LOGIN,
884 RECORD_VERSION_NUMBER)
885 SELECT
886 Tmp.New_Element_Id,
887 Ele.rbs_element_name_id,
888 P_Rbs_Version_Id,
889 Ele.outline_number,
890 Ele.order_Number,
891 Ele.resource_type_id,
892 Ele.resource_source_id,
893 Ele.person_id,
894 Ele.job_id,
895 Ele.organization_id,
896 Ele.Expenditure_Type_Id,
897 Ele.Event_Type_Id,
898 Ele.expenditure_category_id,
899 Ele.revenue_category_id,
900 Ele.inventory_item_id,
901 Ele.item_category_id,
902 Ele.bom_labor_id,
903 Ele.bom_equipment_id,
904 Ele.non_labor_resource_id,
905 Ele.role_id,
906 Ele.person_type_id,
907 Ele.resource_class_id,
908 Ele.supplier_id,
909 Ele.rule_flag,
910 Tmp.New_parent_element_id,
911 Ele.rbs_level,
912 Ele.element_identifier,
913 Ele.user_defined_custom1_id,
914 Ele.user_defined_custom2_id,
915 Ele.user_defined_custom3_id,
916 Ele.user_defined_custom4_id,
917 Ele.user_defined_custom5_id,
918 'N',
919 sysdate,
920 fnd_global.user_id,
921 sysdate,
922 fnd_global.user_id,
923 fnd_global.login_id,
924 1
925 FROM
926 Pa_Rbs_Elements Ele,
927 Pa_Rbs_Elements_Temp Tmp
928 WHERE
929 Tmp.Old_Element_Id=Ele.Rbs_Element_Id;
930
931 End IF; --End of changes made for bug 3659078. modified for bug 3703364
932
933 Pa_Debug.G_Stage := 'Mark the working version as frozen.';
934 Update Pa_Rbs_Versions_B
935 Set
936 Status_code = 'FROZEN'
937 Where
938 Rbs_Version_Id = P_Rbs_Version_Id;
939
940
941 Open c_GetVersionDates(P_Rbs_Ver_Id => l_Latest_Freezed_Version_Id);
942 Fetch c_GetVersionDates Into l_Ver_Dates_Rec;
943 Close c_GetVersionDates;
944
945
946 Pa_Debug.G_Stage := 'Set the version end date of latest freezed version to 1 minus start date of version being freezed,';
947 Pa_Debug.G_Stage := 'if they are not equal';
948
949 Pa_Debug.G_Stage := 'Set the version end date of latest freezed version to start date of version being freezed,';
950 Pa_Debug.G_Stage := 'if they are equal';
951
952 If ((l_version_start_date-1)<(l_ver_dates_rec.Version_start_date)) Then
953
954 l_end_date := l_version_start_date;
955 Else
956
957 l_end_date := l_version_start_date-1;
958
959 End If;
960
961
962 Update Pa_Rbs_Versions_B
963 Set
964 Version_End_Date = l_end_date
965 Where
966 Rbs_Version_Id = l_Latest_Freezed_Version_Id;
967
968 /***********************************************************
969 * Bug - 3617050
970 * Desc - we are getting the count of the versions associated
971 * with the header id.
972 * If the count is one, only then we will set the current
973 * reporting flag as Null.
974 * because more than 1 version for a header cannot have
975 * current_reporting_flag set. so we are only setting it
976 * for the 1st frozen version.
977 ******************************************************************/
978 BEGIN
979 SELECT count(*)
980 INTO l_count
981 FROM pa_rbs_versions_b
982 where rbs_header_id = l_rbs_header_id;
983 EXCEPTION
984 WHEN OTHERS THEN
985 l_count := 0;
986 END;
987
988 IF l_count = 1 THEN
989 Update pa_rbs_versions_b
990 Set current_reporting_flag = 'Y'
991 where rbs_version_id = p_rbs_version_id;
992 END IF;
993
994 -- Bug 3987478 - If RBS is not associated to any projects, the
995 -- PJI push will not pick it up. We still need to set the current
996 -- reporting flag as Yes for the latest frozen version.
997
998 -- Check any associations exist for the RBS Header.
999 SELECT count(*)
1000 INTO l_assoc_count
1001 FROM pa_rbs_prj_assignments
1002 WHERE rbs_header_id = l_rbs_header_id;
1003
1004 IF l_assoc_count = 0 THEN
1005 UPDATE pa_rbs_versions_b
1006 SET current_reporting_flag = 'Y'
1007 WHERE rbs_version_id = p_rbs_version_id;
1008
1009 UPDATE pa_rbs_versions_b
1010 SET current_reporting_flag = NULL
1011 WHERE rbs_header_id = l_rbs_header_id
1012 AND rbs_version_id <> p_rbs_version_id;
1013
1014 -- Similarly, if an RBS is used in allocations but not projects,
1015 -- need to update the latest version to allocations.
1016 PA_ALLOC_UTILS.ASSOCIATE_RBS_TO_ALLOC_RULE(
1017 p_rbs_header_id => l_rbs_header_id,
1018 p_rbs_version_id => p_rbs_version_id,
1019 x_return_status => x_return_status,
1020 x_error_code => x_error_msg_data);
1021
1022 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1023 pa_debug.G_Stage := 'Error in API PA_ALLOC_UTILS.ASSOCIATE_RBS_TO_ALLOC_RULE';
1024 Raise l_error;
1025 END IF;
1026
1027 END IF; /* End of Bug 3987478 changes */
1028
1029 Pa_Debug.G_Stage := 'Check if we have a error message populated.';
1030 If X_Error_Msg_Data Is Not Null Then
1031
1032 Pa_Debug.G_Stage := 'We have a error message populated. Raise error.';
1033 Raise l_error;
1034
1035 Else
1036
1037 Pa_Debug.G_Stage := 'Prepare to call Pji_FM_Xbs_Accum_Maint.Rbs_Push() procedure.';
1038 l_Old_Rbs_Version_Id := l_latest_freezed_version_id;
1039 l_New_Rbs_Version_Id := P_Rbs_Version_Id;
1040
1041 Pa_Debug.G_Stage := 'Call Pji_FM_Xbs_Accum_Maint.Rbs_Push() procedure.';
1042 Pji_FM_Xbs_Accum_Maint.Rbs_Push(
1043 P_Old_Rbs_Version_Id => l_Old_Rbs_Version_Id,
1044 P_New_Rbs_Version_Id => l_New_Rbs_Version_Id,
1045 P_Project_Id => l_Project_Id,
1046 X_Return_Status => X_Return_Status,
1047 X_Msg_Code => X_Error_Msg_Data);
1048
1049 End If;
1050
1051 --For Bug 3678165
1052 If X_Error_Msg_Data Is Not Null Then
1053 Pa_Debug.G_Stage := 'We have a error message populated. Raise error.';
1054 Raise l_error;
1055
1056 Else
1057
1058 Pa_Debug.G_Stage := 'Prepare to call pa_rbs_mapping.create_mapping_rules() procedure.';
1059 Pa_Rbs_Mapping.Create_Mapping_Rules(
1060 P_Rbs_Version_Id => P_Rbs_Version_Id,
1061 X_Return_Status => X_Return_Status,
1062 X_Msg_Count => X_Msg_Count,
1063 X_Msg_Data => X_Error_Msg_Data);
1064 End If;
1065
1066 l_Api_Version_Number := 1;
1067
1068 Pa_Debug.G_Stage := 'Create a copy of the version being freezed which will be the working version for this header.';
1069 Pa_Rbs_Versions_Pub.Create_Working_Version(
1070 P_Commit => Fnd_Api.G_False,
1071 P_Init_Msg_List => Fnd_Api.G_False,
1072 P_Api_Version_Number => l_Api_Version_Number,
1073 P_RBS_Version_Id => P_RBS_Version_Id,
1074 P_Rbs_Header_Id => l_Rbs_Header_Id,
1075 P_Rec_Version_Number => l_Rec_Version_Number,
1076 P_Init_Debugging_Flag => 'N',
1077 X_Return_Status => X_Return_Status,
1078 X_Msg_Count => X_Msg_Count,
1079 X_Error_Msg_Data => X_Error_Msg_Data);
1080
1081 Pa_Debug.G_Stage := 'Check if Pa_Rbs_Versions_Pub.Create_Working_Version() procedure return error.';
1082 IF X_Error_Msg_Data Is Not Null Then
1083
1084 Pa_Debug.G_Stage := 'Check if Pa_Rbs_Versions_Pub.Create_Working_Version() procedure return error.';
1085 Raise l_error;
1086
1087 END IF;
1088
1089 Pa_Debug.G_Stage := 'Check if need to do commit.';
1090 If Fnd_Api.To_Boolean(Nvl(P_Commit,Fnd_Api.G_False)) Then
1091
1092 Pa_Debug.G_Stage := 'Commit changes to the db.';
1093 Commit;
1094
1095 End If;
1096
1097 Pa_Debug.G_Stage := 'Leaving Freeze_Working_Version() procedure.';
1098 Pa_Debug.TrackPath('STRIP','Freeze_Working_Version');
1099
1100 Exception
1101 When l_Error Then
1102 X_Return_Status := 'E';
1103 X_Msg_Count := 1;
1104
1105 When Others Then
1106 X_Return_Status := 'E';
1107 X_Msg_Count := 1;
1108 X_Error_Msg_Data := Pa_Rbs_Versions_Pub.G_Pkg_Name || ':::' || Pa_Debug.G_Path || '::' || Pa_Debug.G_Stage ||
1109 ':' || SqlErrm;
1110 If P_Init_Debugging_Flag = 'Y' Then
1111 Rollback;
1112 Else
1113 Raise;
1114 End If;
1115
1116 END Freeze_Working_Version;
1117
1118 End Pa_Rbs_Versions_Pub;
[Home] [Help]