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