1 Package Body Pa_Rbs_Header_Pvt As
2 --$Header: PARBSHVB.pls 120.1.12020000.2 2013/03/27 17:59:38 bpottipa ship $
3
4 /*==========================================================================
5 This api creates RBS Header. It also creates a working version of this RBS
6 ============================================================================*/
7
8 -- Procedure : INSERT_HEADER
9 -- Type : Private Procedure
10 -- Purpose : This API will be used to create new RBS headers.
11 -- : This API will be called from following package:
12 -- : 1.PA_RBS_HEADER_PUB package,Insert_Header procedure
13
14 -- Note : This API will make a call to PA_RBS_HEADER_PKG.Insert_Row procedure which
15 -- : inserts a record into PA_RBS_HEADERS_B and PA_RBS_HEADERS_TL table.
16
17 -- Assumptions :
18
19 -- Parameters Type Required Description and Purpose
20 -- --------------------------- ------ -------- --------------------------------------------------------
21 -- p_name VARCHAR2 Yes The value contain the name of the Rbs header
22 -- p_description VARCHAR2 NO The description of the Rbs header
23 -- p_effectiveFrom DATE YES The start date of the RBS
24 -- p_effectiveTo DATE NO The end date of the Rbs.
25 -- x_rbsHeaderId NUMBER Yes The value will contain the Rbs Header id which is the unique identifier.
26
27 Procedure Insert_Header(
28 P_Name IN Varchar2 ,
29 P_Description IN Varchar2,
30 P_EffectiveFrom IN Date,
31 P_EffectiveTo IN Date,
32 P_Use_For_Alloc_Flag IN Varchar2,
33 P_Cbs_Enabled IN Varchar2 Default NULL, --bug#15834912
34 P_ACTIVE_FLAG IN Varchar2 Default NULL, --bug#15834912
35 X_RbsHeaderId OUT NOCOPY Number,
36 x_return_status OUT NOCOPY Varchar2,
37 x_msg_data OUT NOCOPY Varchar2,
38 x_msg_count OUT NOCOPY Number )
39
40 Is
41
42 l_Business_Group_Id Number := Null;
43
44 --This cursor selects the next value for rbs header id from pa_rbs_headers_s sequence
45 Cursor c_Rbs_Id_Seq Is
46 Select
47 Pa_Rbs_Headers_S.NextVal
48 From
49 Sys.Dual;
50
51
52 Begin
53
54 x_return_status := FND_API.G_RET_STS_SUCCESS;
55 x_msg_data := NULL;
56 x_msg_count := 0;
57
58 Pa_Debug.G_Stage := 'Entering Insert_Header() Pvt.';
59 Pa_Debug.TrackPath('ADD','Insert_Header Pvt');
60
61 Pa_Debug.G_Stage := 'Get next available value for the rbs header id.';
62 Open c_Rbs_Id_Seq;
63 Fetch c_Rbs_Id_Seq Into X_RbsHeaderId;
64 Close c_Rbs_Id_Seq;
65
66 /* MOAC changes - get the BG ID from the HR Profile */
67 /*
68 Pa_Debug.G_Stage := 'Get the business group id from pa_implementations.';
69 Select
70 Business_Group_Id
71 Into
72 l_Business_Group_Id
73 From
74 Pa_Implementations ;
75 */
76 Pa_Debug.G_Stage := 'Get the BG ID from HR Profile PER_BUSINESS_GROUP_ID';
77
78 l_Business_Group_Id := fnd_profile.value('PER_BUSINESS_GROUP_ID');
79
80 Pa_Debug.G_Stage := 'Calls the table handler which inserts the rbs header record into the Pa_Rbs_Header table.';
81 Pa_Rbs_Headers_Pkg.Insert_Row(
82 P_RbsHeaderId => X_RbsHeaderId,
83 P_Name => P_Name,
84 P_Description => P_Description,
85 P_EffectiveFrom => P_EffectiveFrom,
86 P_EffectiveTo => P_EffectiveTo,
87 P_Use_For_Alloc_Flag => P_Use_For_Alloc_Flag,
88 P_Cbs_Enabled => P_Cbs_Enabled, --bug#15834912
89 P_ACTIVE_FLAG => P_ACTIVE_FLAG, --bug#15834912
90 P_BusinessGroupId => l_Business_Group_Id );
91
92 Pa_Debug.G_Stage := 'Leaving Insert_Header() Pvt.';
93 Pa_Debug.TrackPath('STRIP','Insert_Header Pvt');
94
95 Exception
96 When Others Then
97 X_Return_Status := 'U';
98 X_Msg_Data := SqlErrm;
99 X_Msg_Count := 1;
100 Raise;
101
102 End Insert_Header;
103
104
105 /*==========================================================================
106 This api creates Working Version for the RBS Header.
107 ============================================================================*/
108
109
110 -- Procedure : INSERT_VERSIONS
111 -- Type : Private Procedure
112 -- Purpose : This API will be used to create working version for the RBS header.
113 -- : This API will be called from following package:
114 -- : 1.PA_RBS_HEADER_PUB package,Insert_Header procedure
115
116 -- Note : This API will insert a record into PA_RBS_VERSIONS_B and PA_RBS_VERSIONS_TL table which is the working version.
117
118 -- Assumptions :
119
120 -- Parameters Type Required Description and Purpose
121 -- --------------------------- ------ -------- --------------------------------------------------------
122 -- p_rbsHeaderId NUMBER Yes The value will contain the Rbs Header id which is the unique identifier.
123 -- p_name VARCHAR2 Yes The value contain the name of the Rbs header
124 -- p_effectiveFrom DATE YES The start date of the RBS
125
126
127 Procedure Insert_Versions(
128 P_RbsHeaderId IN Number,
129 P_Name IN Varchar2,
130 P_Description IN Varchar2 Default Null,
131 P_EffectiveFrom IN Date,
132 X_Rbs_Version_Id OUT NOCOPY Number,
133 X_Return_Status OUT NOCOPY Varchar2,
134 X_Msg_Data OUT NOCOPY Varchar2,
135 X_Msg_Count OUT NOCOPY Number )
136
137
138 Is
139
140 l_Created_By Number := Fnd_Global.User_Id;
141 l_Creation_Date Date := SysDate;
142 l_Last_Update_Date Date := SysDate;
143 l_Last_Updated_By Number := Fnd_Global.User_Id;
144 l_Last_Update_Login Number := Fnd_Global.Login_Id;
145
146 --This cursor sets the value for rbs_version_id from pa_rbs_versions_s sequence
147 Cursor c_Rbs_Version_Id_Seq Is
148 Select
149 Pa_Rbs_Versions_S.NextVal
150 From
151 Sys.Dual;
152
153 Begin
154 x_return_status := FND_API.G_RET_STS_SUCCESS;
155 x_msg_data := NULL;
156 x_msg_count := 0;
157
158
159 Pa_Debug.G_Stage := 'Entering Insert_Versions() Pvt.';
160 Pa_Debug.TrackPath('ADD','Insert_Versions Pvt');
161
162 Pa_Debug.G_Stage := 'Get the next available sequence for the rbs version table.';
163 Open c_Rbs_Version_Id_Seq;
164 Fetch c_Rbs_Version_Id_Seq Into X_Rbs_Version_Id;
165 Close c_Rbs_Version_Id_Seq;
166
167 Pa_Debug.G_Stage := 'Insert a working version into Pa_Rbs_Versions_B table directly here.';
168 Insert Into Pa_Rbs_Versions_B(
169 Rbs_Header_Id,
170 Rbs_Version_Id,
171 Version_Number,
172 Version_Start_Date,
173 Status_Code,
174 Rule_Based_Flag,
175 Validated_Flag,
176 Creation_Date,
177 Created_By,
178 Last_Update_Date,
179 Last_Updated_By,
180 Last_Update_Login,
181 Record_Version_Number )
182 Values(
183 P_RbsHeaderId,
184 X_Rbs_Version_Id,
185 1,
186 P_EffectiveFrom,
187 'WORKING',
188 'N',
189 'N',
190 l_Creation_Date,
191 l_Created_By,
192 l_Last_Update_Date,
193 l_Last_Updated_By,
194 l_Last_Update_Login,
195 1 );
196
197 Pa_Debug.G_Stage := 'Insert working versions into Pa_Rbs_Versions_TL table directly here.';
198 Insert Into Pa_Rbs_Versions_TL(
199 Rbs_Version_Id,
200 Name,
201 Description,
202 Language,
203 Last_Update_Date,
204 Last_Updated_By,
205 Creation_Date,
206 Created_By,
207 Last_Update_Login,
208 Source_Lang )
209 Select
210 X_Rbs_Version_Id,
211 P_Name,
212 P_Description,
213 L.Language_Code ,
214 l_Last_Update_Date,
215 l_Last_Updated_By,
216 l_Creation_Date,
217 l_Created_By,
218 l_Last_Update_Login,
219 UserEnv('LANG')
220 From
221 Fnd_Languages L
222
223 Where
224 L.Installed_Flag in ('I', 'B')
225 And Not Exists
226 (Select
227 Null
228 From
229 Pa_Rbs_Versions_TL T
230 Where
231 T.Rbs_Version_Id=X_Rbs_Version_Id
232 And
233 T.Language=L.Language_Code);
234
235 Pa_Debug.G_Stage := 'Entering Insert_Versions() Pvt.';
236 Pa_Debug.TrackPath('STRIP','Insert_Versions Pvt');
237
238 Exception
239 When Others Then
240 X_Return_Status := 'U';
241 X_Msg_Data := SqlErrm;
242 X_Msg_Count := 1;
243 Raise;
244
245 End Insert_Versions;
246
247 Procedure Insert_Structure_Element(
248 P_Rbs_Version_Id IN Number,
249 X_Rbs_Element_Id OUT NOCOPY Number,
250 X_Return_Status OUT NOCOPY Varchar2,
251 X_Error_Msg_Data OUT NOCOPY Varchar2,
252 X_Msg_Count OUT NOCOPY Number )
253
254 Is
255
256 l_Rbs_Element_Id Number(15) := Null;
257 NO_RBS_ELEMENT_NAME Exception;
258 l_Rbs_Element_Name_Id Number(15) := Null;
259 l_Error_Status Varchar2(1) := Null;
260
261 Cursor c1 Is
262 Select
263 Pa_Rbs_Elements_S.NextVal
264 From
265 Sys.Dual;
266
267 Begin
268 x_return_status := FND_API.G_RET_STS_SUCCESS;
269 X_Error_Msg_Data := NULL;
270 x_msg_count := 0;
271
272 Pa_Debug.G_Stage := 'Entering Insert_Structure_Element() Pvt.';
273 Pa_Debug.TrackPath('ADD','Insert_Structure_Element Pvt');
274
275 -- pass in the version name to create rbs_element_name.
276 Pa_Debug.G_Stage := 'Get the element_name_id by calling Pa_Rbs_Utils.Populate_Rbs_Element_Name() procedure.';
277 Pa_Rbs_Utils.Populate_Rbs_Element_Name(
278 P_Resource_Source_Id => P_Rbs_Version_Id,
279 P_Resource_Type_Id => -1,
280 X_Rbs_Element_Name_Id => l_Rbs_Element_Name_Id,
281 X_Return_Status => l_Error_Status);
282
283 Pa_Debug.G_Stage := 'Check if the calling to Pa_Rbs_Utils.Populate_Rbs_Element_Name() procedure returned status of U.';
284 If l_Error_Status = 'U' Then
285
286 Pa_Debug.G_Stage := 'The call to Pa_Rbs_Utils.Populate_Rbs_Element_Name() procedure returned status Unexpected Error.';
287 Raise NO_RBS_ELEMENT_NAME;
288
289 End If;
290
291 Pa_Debug.G_Stage := 'Get the next available sequence for tabpel pa_rbs_elements.';
292 Open c1;
293 Fetch c1 Into l_Rbs_Element_Id;
294 Close c1;
295
296 Pa_Debug.G_Stage := 'Insert record directly into table pa_rbs_elements,';
297 Insert Into Pa_Rbs_Elements(
298 RBS_Element_Id,
299 Rbs_Element_Name_Id,
300 RBS_Version_Id,
301 Outline_Number,
302 Order_Number,
303 Resource_Type_Id,
304 Resource_Source_Id,
305 Person_Id,
306 Job_Id,
307 Organization_Id,
308 Expenditure_Type_Id,
309 Event_Type_Id,
310 Expenditure_Category_Id,
311 Revenue_Category_Id,
312 Inventory_Item_Id,
313 Item_Category_Id,
314 BOM_Labor_Id,
315 BOM_Equipment_Id,
316 Non_Labor_Resource_Id,
317 Role_Id,
318 Person_Type_Id,
319 Resource_Class_Id,
320 Supplier_Id,
321 Rule_Flag,
322 Parent_Element_Id,
323 Rbs_Level,
324 Element_Identifier,
325 User_Created_Flag,
326 User_Defined_Custom1_Id,
327 User_Defined_Custom2_Id,
328 User_Defined_Custom3_Id,
329 User_Defined_Custom4_Id,
330 User_Defined_Custom5_Id,
331 Last_Update_Date,
332 Last_Updated_By,
333 Creation_Date,
334 Created_By,
335 Last_Update_Login,
336 Record_Version_Number )
337 Values (
338 l_Rbs_Element_Id,
339 l_Rbs_Element_Name_Id,
340 P_RBS_Version_Id,
341 '0',
342 0,
343 -1,
344 P_RBS_Version_Id,
345 Null,
346 Null,
347 Null,
348 Null,
349 Null,
350 Null,
351 Null,
352 Null,
353 Null,
354 Null,
355 Null,
356 Null,
357 Null,
358 Null,
359 Null,
360 Null,
361 'N',
362 Null,
363 1,
364 Pa_Rbs_Element_Identifier_S.NextVal,
365 'Y',
366 Null,
367 Null,
368 Null,
369 Null,
370 Null,
371 SysDate,
372 Fnd_Global.User_Id,
373 SysDate,
374 Fnd_Global.User_Id,
375 Fnd_Global.Login_Id,
376 1);
377
378 X_Rbs_Element_Id := l_Rbs_Element_Id;
379
380 Pa_Debug.G_Stage := 'Leaving Insert_Structure_Element() Pvt.';
381 Pa_Debug.TrackPath('STRIP','Insert_Structure_Element Pvt');
382
383 Exception
384 When Others Then
385 X_Return_Status := 'U';
386 X_Error_Msg_Data := sqlerrm;
387 X_Msg_Count := 1;
388 Raise;
389
390 End Insert_Structure_Element;
391
392
393 /*==========================================================================
394 This api updates RBS Header.
395 ============================================================================*/
396
397
398
399 -- Procedure : UPDATE_HEADER
400 -- Type : Private Procedure
401 -- Purpose : This API will be used to update RBS headers.
402 -- : This API will be called from following package:
403 -- : 1.PA_RBS_HEADER_PUB package,Update_Header procedure
404
405 -- Note : This API will make a call to PA_RBS_HEADER_PKG.Update_Row procedure which
406 -- : Updates record into PA_RBS_HEADERS_B and PA_RBS_HEADERS_TL table.
407
408 -- Assumptions :
409
410 -- Parameters Type Required Description and Purpose
411 -- --------------------------- ------ -------- --------------------------------------------------------
412 -- p_rbsHeaderId NUMBER Yes The value will contain the Rbs Header id which is the unique identifier.
413 -- p_name VARCHAR2 Yes The value contain the name of the Rbs header
414 -- p_description VARCHAR2 NO The description of the Rbs header
415 -- p_effectiveFrom DATE YES The start date of the RBS
416 -- p_effectiveTo DATE NO The end date of the Rbs.
417
418
419 Procedure Update_Header(
420 P_RbsHeaderId IN Number,
421 P_Name IN Varchar2 ,
422 P_Description IN Varchar2 ,
423 P_EffectiveFrom IN Date ,
424 P_EffectiveTo IN Date,
425 P_Use_For_Alloc_Flag IN Varchar2,
426 P_ACTIVE_FLAG IN Varchar2 Default NULL, --bug#15834912
427 X_return_Status OUT NOCOPY Varchar2,
428 X_Msg_Data OUT NOCOPY Varchar2,
429 X_Msg_Count OUT NOCOPY Number)
430
431 Is
432
433 Begin
434 x_return_status := FND_API.G_RET_STS_SUCCESS;
435 x_msg_data := NULL;
436 x_msg_count := 0;
437
438 Pa_Debug.G_Stage := 'Entering Update_Header() Pvt.';
439 Pa_Debug.TrackPath('ADD','Update_Header Pvt');
440
441 --Updates Rbs header information
442 Pa_Debug.G_Stage := 'Call the table handler procedure Pa_Rbs_Headers_Pkg.Update_Row to update the header record.';
443 Pa_Rbs_Headers_Pkg.Update_Row(
444 P_RbsHeaderId => P_RbsHeaderId,
445 P_Name => P_Name,
446 P_Description => P_Description,
447 P_EffectiveFrom => P_EffectiveFrom,
448 P_Use_For_Alloc_Flag => P_Use_For_Alloc_Flag,
449 P_ACTIVE_FLAG => P_ACTIVE_FLAG, --bug#15834912
450 P_EffectiveTo => P_EffectiveTo);
451
452 Pa_Debug.G_Stage := 'Leaving Update_Header() Pvt.';
453 Pa_Debug.TrackPath('STRIP','Update_Header Pvt');
454
455 Exception
456 When Others Then
457 X_Return_Status := 'U';
458 X_Msg_Data := sqlerrm;
459 X_Msg_Count := 1;
460 Raise;
461
462 End Update_Header;
463
464
465 /*==========================================================================
466 This api updates Working Version for the RBS Header.
467 ============================================================================*/
468
469
470 -- Procedure : UPDATE_VERSIONS
471 -- Type : Private Procedure
472 -- Purpose : This API will be used to update working version for the RBS header.
473 -- : This API will be called from following package:
474 -- : 1.PA_RBS_HEADER_PUB package,Update_Header procedure
475
476 -- Note : This API will Updates working version for Rbs header in PA_RBS_VERSIONS_B and PA_RBS_VERSIONS_TL table.
477
478 -- Assumptions :
479
480 -- Parameters Type Required Description and Purpose
481 -- --------------------------- ------ -------- --------------------------------------------------------
482 -- p_rbsHeaderId NUMBER Yes The value will contain the Rbs Header id which is the unique identifier.
483 -- p_name VARCHAR2 Yes The value contain the name of the Rbs header
484 -- p_effectiveFrom DATE YES The start date of the RBS
485
486
487 Procedure Update_Versions(
488 P_RbsHeaderId IN Number,
489 P_RbsVersionId IN Number Default Null,
490 P_Name IN Varchar2,
491 P_Description IN Varchar2 Default Null,
492 P_EffectiveFrom IN Date,
493 P_Rec_Version_Num IN Number Default Null,
494 X_Return_Status OUT NOCOPY Varchar2,
495 X_msg_Data OUT NOCOPY Varchar2,
496 X_Msg_Count OUT NOCOPY Number )
497
498 Is
499
500 l_Last_Update_Date Date := SysDate;
501 l_Last_Updated_By Number := Fnd_Global.User_Id;
502 l_Last_Update_Login Number := Fnd_Global.Login_Id;
503 l_Rbs_Version_Id Number := Null;
504
505 BEGIN
506
507 x_return_status := FND_API.G_RET_STS_SUCCESS;
508 x_msg_data := NULL;
509 x_msg_count := 0;
510
511 Pa_Debug.G_Stage := 'Leaving Update_Versions() Pvt.';
512 Pa_Debug.TrackPath('ADD','Update_Versions Pvt');
513
514 Pa_Debug.G_Stage := 'Check if parameter P_RbsVersionId is null.';
515 If P_RbsVersionId is Null Then
516
517 Pa_Debug.G_Stage := 'Since we do not have the rbs version id we need to get it.';
518 Select
519 Rbs_Version_Id
520 Into
521 l_Rbs_Version_Id
522 From
523 Pa_Rbs_Versions_B
524 Where
525 Rbs_Header_Id = P_RbsHeaderId
526 And Status_Code = 'WORKING';
527
528 Else
529
530 Pa_Debug.G_Stage := 'We have the rbs version id via the parameters passed in assign to local variable for use.';
531 l_Rbs_Version_Id := P_RbsVersionId;
532
533 End If;
534
535 Pa_Debug.G_Stage := 'Directly update the rbs working version.';
536 Update Pa_Rbs_Versions_B
537 Set
538 Version_Start_Date = P_EffectiveFrom,
539 Last_Update_Date = l_Last_Update_Date,
540 Last_Updated_By = l_Last_Updated_By,
541 Last_Update_Login = l_Last_Update_Login,
542 Record_Version_Number = Record_Version_Number + 1
543 Where
544 Rbs_Version_Id = l_Rbs_Version_Id
545 And Status_Code = 'WORKING'
546 And Record_Version_Number = Nvl(P_Rec_Version_Num,Record_Version_Number);
547
548
549 Pa_Debug.G_Stage := 'Check if the update took place.';
550 If Sql%NotFound Then
551
552 Pa_Debug.G_Stage := 'Unable to update the rbs version because already updated. Raising error.';
553 Raise No_Data_Found;
554
555 End If;
556
557 Pa_Debug.G_Stage := 'Directly update the pa_rbs_versions_tl table.';
558 Update Pa_Rbs_Versions_TL
559 Set
560 Name = P_Name,
561 Description = Nvl(Description,P_Description),
562 Last_Update_Date = l_Last_Update_Date,
563 Last_Updated_By = l_Last_Updated_By,
564 Last_Update_Login = l_Last_Update_Login
565 Where
566 Rbs_Version_Id = l_Rbs_Version_Id;
567
568 Exception
569 When Others Then
570 X_Return_Status := 'U';
571 X_Msg_Data := SqlErrm;
572 X_Msg_Count := 1;
573 Raise;
574
575 END Update_Versions;
576
577
578 /*==========================================================================
579 This Function checks if Rbs has rules as its elements or not.
580 ============================================================================*/
581
582
583 -- Function : Validate_Rbs_For_Allocations
584 -- Type : Private Function
585 -- Purpose : This Function is used to check if RBS has rules as its elements or not.
586 -- : This API will be called from following package:
587 -- : 1.PA_RBS_HEADER_PUB package,Update_Header procedure
588
589 -- Note : This Function is called to check for Rbs having rules as its elements.
590 -- : This function is called whenever user modifies use_for_alloc_flag(i.e when sets to Y from N)
591 -- : This change is allowed only when RBS has no rules as its elements.
592
593 -- Assumptions :
594
595 -- Parameters Type Required Description and Purpose
596 -- --------------------------- ------ -------- --------------------------------------------------------
597 -- P_Rbs_ID NUMBER Yes The value will contain the Rbs Header id which is the unique identifier.
598
599 -- Returns either
600 -- 'Y' -When Rbs has rules as its elements.
601 -- 'N' -When Rbs has no elements which are rules.
602
603
604
605 FUNCTION Validate_Rbs_For_Allocations( P_RBS_ID IN pa_rbs_headers_v.RBS_HEADER_ID%Type ) RETURN VARCHAR2
606 IS
607 l_exists Varchar2(1) := 'N';
608 l_count Number ;
609 BEGIN
610
611 Pa_Debug.G_Stage := 'Inside Validate_Rbs_For_Allocations().';
612 Pa_Debug.TrackPath('ADD','Validate_Rbs_For_Allocations Pvt');
613
614 /* Checking PA_RBS_ELEMENTS */
615
616 Select count(*) INTO l_count
617 From
618 Pa_Rbs_Versions_b Verb,
619 Pa_Rbs_Elements ele
620 Where
621 Verb.rbs_header_id=P_RBS_ID
622 And
623 verb.Rbs_Version_Id=Ele.Rbs_Version_Id
624 And
625 Ele.resource_source_id=-1;
626
627 Pa_Debug.G_Stage := 'Check if rbs has rules as its elements. If so return Y';
628
629 If l_count<>0 Then
630 l_exists := 'Y';
631 Else
632 l_exists := 'N';
633 End If;
634
635 Pa_Debug.G_Stage := 'Leaving Validate_Rbs_For_Allocations().';
636
637 Return l_exists ;
638
639 END Validate_Rbs_For_Allocations;
640
641 End Pa_Rbs_Header_Pvt;
[Home] [Help]