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