1 Package Body Pa_Rbs_Header_Pub as
2 --$Header: PARBSHPB.pls 120.1 2006/01/04 17:33:47 appldev noship $
3
4 /*==============================================================================
5 This api Updates RBS Header. It also updates the working version of this RBS
6 =============================================================================*/
7
8 -- Procedure : UPDATE_HEADER
9 -- Type : Public Procedure
10 -- Purpose : This API will be used to update the RBS header for a particular rbs header id.
11 -- : This API will be called from following page:
12 -- : 1.Rbs Header Page
13
14 -- Note : This API will does all the business validations.
15 -- : -- If no errors are encounterd it updates the pa_rbs_headers table.
16 -- : -- The validations made are
17 -- : -- Rbs Header name should be unique
18 -- : -- Rbs From date cannot be null
19 -- : -- Rbs To date cannot be less than Rbs from date
20 -- : -- Rbs From date cannot be updated if there atleast one published version
21 -- : -- Rbs To date cannot be less than its versions
22 -- : -- If a Rbs is not having any published version then update is allowed on both header and versions for
23 -- : -- every attribute.
24 -- : -- If a Rbs has atleast one published version then update is not allowed on the versions table.
25 -- : -- If a Rbs has rules as its elements then user cant check(setting it to 'Y') use_for_alloc_flag.
26 -- : -- If a Rbs has alteast one freezed version and is used in allocation rule then user cant uncheck
27 -- : -- Use_For_Alloc_Flag ( i.e setting it to 'N')
28
29 -- Assumptions :
30
31 -- Parameters Type Required Description and Purpose
32 -- --------------------------- ------ -------- --------------------------------------------------------
33 -- P_Commit Varchar2 No
34 -- P_Init_Msg_List Varchar2 No
35 -- P_API_Version_Number Varchar2 Yes
36 -- P_RbsHeaderId NUMBER Yes The value will contain the Rbs Header id which is the
37 -- unique identifier.
38 -- P_Name VARCHAR2 Yes The name of the Rbs Header.
39 -- P_Description VARCHAR2 NO The description of the Rbs header
40 -- P_EffectiveFrom DATE YES The start date of the RBS
41 -- P_EffectiveTo DATE NO The end date of the Rbs.
42 -- P_Use_For_Alloc_Flag VARCHAR2 NO This determine whether a Rbs can be used in allocation rule or not.
43 -- P_RecordVersionNumber NUMBER Yes The record version number of the rbs header which is
44 -- used to ensure syncronization.
45
46 Procedure Update_Header(
47 P_Commit IN Varchar2 Default Fnd_Api.G_False,
48 P_Init_Msg_List IN Varchar2 Default Fnd_Api.G_True,
49 P_API_Version_Number IN Number,
50 P_RbsHeaderId IN Number,
51 P_Name IN Varchar2,
52 P_Description IN Varchar2,
53 P_EffectiveFrom IN Date,
54 P_EffectiveTo IN Date,
55 P_Use_For_Alloc_Flag IN Varchar2 Default 'N',
56 P_RecordVersionNumber IN Number,
57 P_Process_Version IN Varchar2 Default Fnd_Api.G_True,
58 X_Return_status OUT NOCOPY Varchar2,
59 X_Msg_Data OUT NOCOPY Varchar2,
60 X_Msg_Count OUT NOCOPY Number )
61
62 IS
63
64 -- This cursor selects the record version number for the header which needs to be updated
65 Cursor c_Record_Ver_No Is
66 Select
67 record_version_number
68 From
69 pa_rbs_headers_b
70 Where
71 rbs_header_id=p_rbsHeaderId
72 For Update Of Effective_From_Date NoWait;
73
74 RecInfo c_Record_Ver_No%RowType;
75
76 l_Msg_Count Number := 0;
77 l_Msg_Data Varchar2(2000) := Null;
78 l_Data Varchar2(2000) := Null;
79 l_Msg_Index_Out Number;
80 l_Debug_Mode Varchar2(1) := Null;
81 l_Return_Status Varchar2(1) := Null;
82 l_Count Number;
83 l_Name Varchar2(240) := Null;
84 l_Error Exception;
85 l_UnExp_Error Exception;
86 l_Error_Raised Varchar2(1) := Null;
87 l_Effect_To_Date Date;
88 l_EffectiveFromDate Date;
89 l_Check Number := 0;
90 l_use_for_alloc_flag Varchar2(1);
91
92 l_Api_Name Varchar2(30) := 'Update_Header';
93
94 Begin
95
96 If P_Process_Version = 'Y' Then
97
98 Pa_Debug.G_Path := ' ';
99
100 End If;
101
102 Pa_Debug.G_Stage := 'Entering Update_Header().';
103 Pa_Debug.TrackPath('ADD','Update_Header');
104
105 Pa_Debug.G_Stage := 'Check API Version compatibility by calling Fnd_Api.Compatible_API_Call() procedure.';
106 If Not Fnd_Api.Compatible_API_Call (
107 Pa_Rbs_Header_Pub.G_Api_Version_Number,
108 P_Api_Version_Number,
109 l_Api_Name,
110 Pa_Rbs_Header_Pub.G_Pkg_Name) Then
111
112 Pa_Debug.G_Stage := 'API Version compatibility failure.';
113 Raise Fnd_Api.G_Exc_Unexpected_Error;
114
115 End If;
116
117 Pa_Debug.G_Stage := 'Check if need to initialize the error message stack.';
118 If Fnd_Api.To_Boolean(nvl(P_Init_Msg_List,Fnd_Api.G_True)) Then
119
120 Pa_Debug.G_Stage := 'Initialize the error message stack by callling Fnd_Msg_Pub.Initialize() procedure.';
121 Fnd_Msg_Pub.Initialize;
122
123 End If;
124
125 X_Return_Status := Fnd_Api.G_Ret_Sts_Success;
126 X_Msg_Count := 0;
127
128 /* validation: Rbs name cannot be null*/
129 Pa_Debug.G_Stage := 'Check if the paramter p_name is null.';
130 If P_Name Is Null Then
131
132 Pa_Debug.G_Stage := 'Rbs Name is null. Add message to error stage.';
133 Pa_Utils.Add_MessagE(
134 P_App_Short_Name => 'PA',
135 P_Msg_Name => 'PA_RBS_NAME_NULL');
136
137 Raise l_Error;
138
139 End If;
140
141 /* validation: Rbs Effective From date cannot be null*/
142 Pa_Debug.G_Stage := 'Check if the Effective From Date for the RBS Header is null.';
143 If P_EffectiveFrom Is Null Then
144
145 Pa_Debug.G_Stage := 'The Effective From Date for the RBS Header is null.';
146 Pa_Utils.Add_Message(
147 P_App_Short_Name => 'PA',
148 P_Msg_Name => 'PA_RBS_FROM_NULL',
149 P_Token1 => 'RBSNAME',
150 P_Value1 => P_Name);
151
152 l_Error_Raised := 'Y';
153
154 End If;
155
156 /* validation: Rbs effective From should always be less than effective to date */
157 Pa_Debug.G_Stage := 'Check if the RBS Header Effective From Date is > Effective To Date.';
158 If P_EffectiveFrom > P_EffectiveTo Then
159
160 Pa_Debug.G_Stage := 'The Rbs Header Effective From Date is > Effective To Date.';
161 Pa_Utils.Add_Message(
162 P_App_Short_Name => 'PA',
163 P_Msg_Name => 'PA_RBS_TO_LESS_FROM',
164 P_Token1 => 'RBSNAME',
165 P_value1 => P_Name);
166
167 l_Error_Raised := 'Y';
168
169 End If;
170
171 /* to check the uniqueness of the RBS name*/
172 Pa_Debug.G_Stage := 'Get the Rbs Name using the rbs header id.';
173 Select
174 Name
175 Into
176 l_Name
177 From
178 Pa_Rbs_Headers_TL
179 Where
180 Rbs_Header_Id = P_RbsHeaderId
181 And language = USERENV('LANG');
182
183 Pa_Debug.G_Stage := 'Check if the p_name passed in matches the name already stored in the db for the rbs header id.';
184 If l_Name <> P_Name Then
185
186 Pa_Debug.G_Stage := 'Get the number of rbs headers that use the name passed in p_name parameter.';
187 Select
188 Count(*)
189 Into
190 l_Count
191 From
192 Pa_Rbs_Headers_TL
193 Where
194 Name = P_Name
195 And language = USERENV('LANG');
196
197 Pa_Debug.G_Stage := 'Check if the number of rbs headers using the name.';
198 If l_Count <> 0 Then
199
200 Pa_Debug.G_Stage := 'The p_name parameter for a new rbs name is already used. Add message to message stack.';
201 Pa_Utils.Add_Message(
202 P_App_Short_Name => 'PA',
203 P_Msg_Name => 'PA_RBS_NOT_UNIQUE',
204 P_Token1 => 'RBSNAME',
205 P_Value1 => P_Name);
206
207 l_Error_Raised := 'Y';
208
209 End If;
210
211 End If;
212
213 /* check for locking */
214 Pa_Debug.G_Stage := 'Use cursor to lock the header record for update.';
215 Open c_Record_Ver_No;
216 Fetch c_Record_Ver_No Into RecInfo;
217
218 Pa_Debug.G_Stage := 'Check if header record to lock.';
219 If c_Record_Ver_No%NotFound Then
220
221 Close c_Record_Ver_No;
222 End If;
223
224 Close c_Record_Ver_No;
225
226 Pa_Debug.G_Stage := 'Check if the record version number retrieved matches the parameter value passed in.';
227 If RecInfo.Record_Version_Number = P_RecordVersionNumber Then
228
229 Pa_Debug.G_Stage := 'The record version number matches up.';
230 Null;
231
232 Else
233
234 Pa_Debug.G_Stage := 'The record version number does not match up. The record has already been update ' ||
235 'other others. Add message to error stack.';
236 Pa_Utils.Add_Message(
237 P_App_Short_Name => 'PA',
238 P_Msg_Name => 'PA_RBS_HEADER_CHANGED');
239
240 l_Error_Raised := 'Y';
241
242 End If;
243
244 Pa_Debug.G_Stage := 'Check if any error were found.';
245 If l_Error_Raised = 'Y' Then
246
247 Pa_Debug.G_Stage := 'We have validation error so raising.';
248 Raise l_Error;
249
250 End If;
251
252
253 --Check if Rbs has rules as its elements when use_for_alloc_flag is checked by the user.
254
255 SELECT use_for_Alloc_Flag
256 INTO l_Use_For_Alloc_Flag
257 FROM pa_rbs_headers_b
258 WHERE rbs_header_id=P_RbsHeaderId;
259
260
261 IF(l_use_for_alloc_flag='N' and p_use_for_alloc_flag='Y') Then
262
263 IF PA_RBS_HEADER_PVT.Validate_Rbs_For_Allocations(p_rbs_id=> P_RbsHeaderId)='Y'
264 THEN
265 Pa_Utils.Add_Message(
266 P_App_Short_Name => 'PA',
267 P_Msg_Name => 'PA_RBS_HAS_RULES');
268
269 Pa_Debug.G_Stage := 'We have rules as elements so raising error.';
270 Raise l_Error;
271
272 End If;
273 End IF;
274
275 Pa_Debug.G_Stage := 'See if there is a frozen rbs version for this header.';
276 Begin
277
278 l_Check := 0;
279
280 --Checks if there is at least one published versions for the rbs.
281 --If yes then l_check has not null value
282 --Otherwise it is set to null
283
284 Select
285 Max(Rbs_Header_Id),
286 Max(Version_End_Date)
287 Into
288 l_Check,
289 l_Effect_To_Date
290 From
291 Pa_Rbs_Versions_B
292 Where
293 Rbs_Header_Id = P_RbsHeaderId
294 And Status_Code <> 'WORKING';
295
296 Exception
297 When Others Then
298 l_Check := 0;
299
300 End;
301
302 Pa_Debug.G_Stage := 'Check if a version record was found.';
303 If l_Check Is Null Or l_Check = 0 Then
304
305
306 Pa_Debug.G_Stage := 'No frozen version record was found. Update the header information by calling ' ||
307 'the Pa_Rbs_Header_Pvt.Update_Header() procedure.';
308
309 --When user checks use_for_alloc_flag there is no need to check its usage in allocation rule.
310 --The RBS has no freezed version and hence cant be used in allocation rule.
311
312 Pa_Rbs_Header_Pvt.Update_Header(
313 P_RbsHeaderId => P_RbsHeaderId,
314 P_Name => P_Name,
315 P_Description => P_Description,
316 P_EffectiveFrom => P_EFfectiveFrom,
317 P_EffectiveTo => P_EffectiveTo,
318 P_Use_For_Alloc_Flag => P_Use_For_Alloc_Flag,
319 X_Return_Status => X_Return_Status,
320 X_Msg_Data => X_Msg_Data,
321 X_Msg_Count => X_Msg_Count);
322
323 Pa_Debug.G_Stage := 'Check if return status from call to Pa_Rbs_Header_Pvt.Update_Header() procedure is U.';
324 If X_Return_Status = 'U' Then
325
326 Pa_Debug.G_Stage := 'Call to Pa_Rbs_Header_Pvt.Update_Header() procedure returned Unexpected error. Raise.';
327 Raise l_UnExp_Error;
328
329 End If;
330
331 Pa_Debug.G_Stage := 'No frozen version record was found. Check if allow to update the version record in this module.';
332 If P_Process_Version = Fnd_Api.G_True Then
333
334 Pa_Debug.G_Stage := 'No frozen version record was found. Update the working version record for ' ||
335 'the Rbs header by calling the Pa_Rbs_Header_Pvt.Update_Versions procedure.';
336 Pa_Rbs_Header_Pvt.Update_Versions(
337 P_RbsHeaderId => P_RbsHeaderId,
338 P_Name => P_Name,
339 P_EffectiveFrom => P_EffectiveFrom,
340 X_Return_Status => X_Return_Status,
341 X_Msg_Data => X_Msg_Data,
342 X_Msg_Count => X_Msg_Count);
343
344 Pa_Debug.G_Stage := 'Check if return status from call to Pa_Rbs_Header_Pvt.Update_Versions() procedure is U.';
345 If X_Return_Status = 'U' Then
346
347 Pa_Debug.G_Stage := 'Call to Pa_Rbs_Header_Pvt.Update_Versions() procedure returned ' ||
348 'Unexpected error. Raise.';
349 Raise l_UnExp_Error;
350
351 End If;
352
353 End If;
354
355 Else
356
357 -- name can be updated but should not be reflected in versions. Effective to can be updated but validated such that
358 -- it cannot be less than effective to date of its versions. effective from cannot be updated
359 -- Also if use_for_alloc_flag is unchecked test for its usage in allocation rules. If yes raise appropriate error.
360
361 /* validation: effective from cannot be updated*/
362 Pa_Debug.G_Stage := 'Found frozen versions for the rbs. Get the effective from date in the header.';
363 Select
364 Effective_From_Date
365 Into
366 l_EffectiveFromDate
367 From
368 Pa_Rbs_Headers_B
369 Where
370 Rbs_Header_Id = P_RbsHeaderId;
371
372 Pa_Debug.G_Stage := 'Check if trying to change the effective from date of the header.';
373 If l_EffectiveFromDate <> P_EffectiveFrom Then
374
375 Pa_Debug.G_Stage := 'Trying to change the effective from date of the header when have frozen ' ||
376 'rbs versions. Add message to error stack.';
377 Pa_Utils.Add_Message(
378 P_App_Short_Name => 'PA',
379 P_Msg_Name => 'PA_RBS_FROM_CHANGED',
380 P_Token1 => 'RBSNAME',
381 P_Value1 => P_Name);
382
383 l_Error_Raised := 'Y';
384
385 End If;
386
387 -- Per bug 3602821 we don't need to check the header end_date to the version end_dates
388 -- VALIDATION: Effective to if being updated to a not null value for the rbs header must not be
389 -- less than effective to of rbs frozen versions*/
390 -- Pa_Debug.G_Stage := 'Check if the effective_to_date is being changed. Check If not null then if < max ' ||
391 -- 'version_end_date of frozen versions.';
392 -- If P_EffectiveTo Is Not Null And P_EffectiveTo < l_Effect_To_Date Then
393
394 -- Pa_Debug.G_Stage := 'Trying to change effective to date of header and it is < that ' ||
395 -- 'max version_end_date of frozen versions. Add msg to error stack.';
396 -- Pa_Utils.Add_MessagE(
397 -- P_App_Short_Name => 'PA',
398 -- P_Msg_Name => 'PA_RBS_TO_LESS_THAN_VERSIONS',
399 -- P_Token1 => 'RBSNAME',
400 -- P_Value1 => P_Name);
401
402 -- l_Error_Raised := 'Y';
403
404 -- End If;
405
406 Pa_Debug.G_Stage := 'Check 2 if validation errors were found.';
407 If l_Error_Raised ='Y' Then
408
409 Pa_Debug.G_Stage := 'Validation errors were found - 2. Raise l_Error.';
410 Raise l_Error;
411
412 End If;
413
414 --Logic for Use For alloc Flag
415
416 Pa_Debug.G_Stage := 'Check if use_for_alloc_flag is changed from Y to N. If so then check for its ' ||
417 'usage in allocation rules.';
418
419
420 IF(l_use_for_alloc_flag='Y' and p_use_for_alloc_flag='N') Then
421 --Check if Rbs is used for allocation
422 IF PA_ALLOC_UTILS.IS_RBS_IN_RULES(p_rbs_id=> P_RbsHeaderId) = 'Y'
423 THEN
424 Pa_Debug.G_Stage := 'Trying to change use_for_alloc_flag to N for Rbs used in allocation rule' ||
425 'Add msg to error stack.';
426
427 Pa_Utils.Add_Message(
428 P_App_Short_Name => 'PA',
429 P_Msg_Name => 'PA_RBS_USED_IN_ALLOC',
430 P_Token1 => 'RBSNAME',
431 P_Value1 => P_Name);
432
433 Pa_Debug.G_Stage := 'We have used in allocation error so raising.';
434 Raise l_Error;
435
436 End If;
437 End IF;
438
439 Pa_Debug.G_Stage := 'Check 3 if allocation usage error is found.';
440 If l_Error_Raised ='Y' Then
441
442 Pa_Debug.G_Stage := 'Allcation error found - 3. Raise l_Error.';
443 Raise l_Error;
444
445 End If;
446
447 --Updates the header information for the Rbs
448 Pa_Debug.G_Stage := 'Update the rbs header record - 2 by calling the Pa_Rbs_Header_Pvt.Update_Header() procedure.';
449 Pa_Rbs_Header_Pvt.Update_Header(
450 p_rbsHeaderId,
451 p_name,
452 p_description,
453 p_effectiveFrom,
454 p_effectiveTo,
455 p_use_for_alloc_flag,
456 x_return_status,
457 x_msg_data,
458 x_msg_count);
459
460 Pa_Debug.G_Stage := 'Check if return status from call to Pa_Rbs_Header_Pvt.Update_Header() procedure is U.';
461 If X_Return_Status = 'U' Then
462
463 Pa_Debug.G_Stage := 'Call to Pa_Rbs_Header_Pvt.Update_Header() procedure returned Unexpected error. Raise.';
464 Raise l_UnExp_Error;
465
466 End If;
467
468 End If;
469
470 Pa_Debug.G_Stage := 'Check if need to commit data.';
471 If Fnd_Api.To_Boolean(Nvl(P_Commit,Fnd_Api.G_False)) Then
472
473 Pa_Debug.G_Stage := 'Commit inserts to db.';
474 Commit;
475
476 End If;
477
478 Pa_Debug.G_Stage := 'Leaving Update_Header() procedure.';
479 Pa_Debug.TrackPath('STRIP','Update_Header');
480
481 Exception
482
483 When l_Error Then
484 X_Return_Status := Fnd_Api.G_Ret_Sts_Error;
485 l_Msg_Count := Fnd_Msg_Pub.Count_Msg;
486 If l_msg_count = 1 Then
487 Pa_Interface_Utils_Pub.Get_Messages(
488 p_encoded => Fnd_Api.G_True,
489 p_msg_index => 1,
490 p_msg_count => l_msg_count,
491 p_msg_data => l_msg_data,
492 p_data => l_data,
493 p_msg_index_out => l_msg_index_out);
494
495 x_msg_data := l_data;
496 x_msg_count := l_msg_count;
497 Else
498 x_msg_count := l_msg_count;
499 End If;
500
501 When l_UnExp_Error Then
502 x_return_status := 'U';
503 x_msg_data := Pa_Rbs_Header_Pub.G_Pkg_Name || ':::' || Pa_Debug.G_Path || '::' || Pa_Debug.G_Stage || ':' || X_Msg_Data;
504 x_msg_count := 1;
505 Rollback;
506
507 When Others Then
508 x_return_status := 'U';
509 x_msg_data := Pa_Rbs_Header_Pub.G_Pkg_Name || ':::' || Pa_Debug.G_Path || '::' || Pa_Debug.G_Stage || ':' || SqlErrm;
510 x_msg_count := 1;
511 Rollback;
512
513 END Update_Header;
514
515
516 /*==========================================================================
517 This api creates RBS Header. It also creates a working version of this RBS
518 ============================================================================*/
519
520
521
522 -- Procedure : INSERT_HEADER
523 -- Type : Public Procedure
524 -- Purpose : This API will be used to create new RBS headers.
525 -- : This API will be called from following page:
526 -- : 1.Rbs Header Page
527
528 -- Note : This API will does all the business validations.
529 -- : -- If no errors are encounterd it inserts the Rbs header into pa_rbs_headers_b and pa_rbs_headers_tl table.
530 -- : -- The validations made are
531 -- : -- Rbs Header name should be unique
532 -- : -- Rbs Header name should not be null
533 -- : -- Rbs From date cannot be null
534 -- : -- Rbs To date cannot be less than Rbs from date
535 -- : -- Use_For_Alloc_Flag is set to either Y or N and no check is made coz this RBS when created will
536 -- : -- have no freezed version to associate to allocation rule and will have no elements to check for rules
537 -- : -- as its elements.
538
539 -- Assumptions :
540
541 -- Parameters Type Required Description and Purpose
542 -- --------------------------- ------ -------- --------------------------------------------------------
543 -- P_Commit Varchar2 No
544 -- P_Init_Msg_List Varchar2 No
545 -- P_API_Version_Number Varchar2 Yes
546 -- p_name NUMBER Yes The name of the Rbs Header id which is unique.
547 -- p_description VARCHAR2 NO The description of the Rbs header
548 -- p_effectiveFrom DATE YES The start date of the RBS
549 -- p_effectiveTo DATE NO The end date of the Rbs.
550 -- P_Use_For_alloc_Flag VARCHAR2 NO The field which determines Rbs usage in allocation rule
551
552
553
554
555 PROCEDURE Insert_Header(
556 P_Commit IN Varchar2 Default Fnd_Api.G_False,
557 P_Init_Msg_List IN Varchar2 Default Fnd_Api.G_True,
558 P_API_Version_Number IN Number,
559 P_Name IN Varchar2,
560 P_Description IN Varchar2,
561 P_EffectiveFrom IN Date,
562 P_EffectiveTo IN Date,
563 P_Use_For_Alloc_Flag IN Varchar2 Default 'N',
564 X_Rbs_Header_Id OUT NOCOPY Number,
565 X_Rbs_Version_Id OUT NOCOPY Number,
566 X_Rbs_Element_Id OUT NOCOPY Number,
567 X_Return_Status OUT NOCOPY Varchar2,
568 X_Msg_Data OUT NOCOPY Varchar2,
569 X_Msg_Count OUT NOCOPY Number )
570
571 Is
572
573 l_Rbs_Header_Id Number(15) := Null;
574 l_Rbs_Version_Id Number(15) := Null;
575 l_Rbs_Element_Id Number(15) := Null;
576
577 l_Api_Name Varchar2(30) := 'Insert_Header';
578
579 l_msg_count Number := 0;
580 l_msg_data Varchar2(2000) := Null;
581 l_data Varchar2(2000) := Null;
582 l_msg_index_out Number;
583 l_return_status Varchar2(1) := Null;
584 l_count Number;
585 l_error_raised Varchar2(1) := Null;
586 l_Error Exception;
587 l_UnExp_Error Exception;
588
589 BEGIN
590
591 Pa_Debug.G_Stage := 'Entering Insert_Header().';
592 Pa_Debug.TrackPath('ADD','Insert_Header');
593
594 Pa_Debug.G_Stage := 'Check API Version compatibility by calling Fnd_Api.Compatible_API_Call() procedure.';
595 If Not Fnd_Api.Compatible_API_Call (
596 Pa_Rbs_Elements_Pub.G_Api_Version_Number,
597 P_Api_Version_Number,
598 l_Api_Name,
599 Pa_Rbs_Elements_Pub.G_Pkg_Name) Then
600
601 Pa_Debug.G_Stage := 'API Version compatibility failure.';
602 Raise Fnd_Api.G_Exc_Unexpected_Error;
603
604 End If;
605
606 Pa_Debug.G_Stage := 'Check if need to initialize the message error stack.';
607 If Fnd_Api.To_Boolean(nvl(P_Init_Msg_List,Fnd_Api.G_True)) Then
608
609 Pa_Debug.G_Stage := 'Initilize the message error stack by calling the Fnd_Msg_Pub.Initialize() procedure.';
610 Fnd_Msg_Pub.Initialize;
611
612 End If;
613
614 X_Return_Status := Fnd_Api.G_Ret_Sts_Success;
615 X_Msg_Count := 0;
616
617 /* validation: Rbs name cannot be null */
618 Pa_Debug.G_Stage := 'Check if the paramter p_name is null.';
619 If P_Name Is Null Then
620
621 Pa_Debug.G_Stage := 'Rbs Name is null. Add message to error stage.';
622 Pa_Utils.Add_Message(
623 P_App_Short_Name => 'PA',
624 P_Msg_Name => 'PA_RBS_NAME_NULL');
625
626 Raise l_Error;
627
628 End If;
629
630 /* validation:Rbs effective From date cannot be null */
631 Pa_Debug.G_Stage := 'Check if the effective from date is null.';
632 If P_EffectiveFrom Is Null Then
633
634 Pa_Debug.G_Stage := 'The Effective from date is null. Add message to error stack.';
635 Pa_Utils.Add_Message(
636 P_App_Short_Name => 'PA',
637 P_Msg_Name => 'PA_RBS_FROM_NULL',
638 P_Token1 => 'RBSNAME',
639 P_Value1 => P_Name);
640
641 l_Error_Raised := 'Y';
642
643 Else
644
645 /* validation: Rbs effective From should always be less than Rbs effective to */
646 Pa_Debug.G_Stage := 'Check if the rbs header effective from date > effective to date.';
647 If P_EffectiveFrom > Nvl(P_EffectiveTo,P_EffectiveFrom) Then
648
649 Pa_Debug.G_Stage := 'The rbs header effective from date > effective to date. Add message to error stack.';
650 Pa_Utils.Add_Message(
651 P_App_Short_Name => 'PA',
652 P_Msg_Name => 'PA_RBS_TO_LESS_FROM',
653 P_Token1 => 'RBSNAME',
654 P_Value1 => P_Name);
655
656 l_Error_Raised := 'Y';
657
658 End If;
659
660 End If;
661
662 /* validation: Rbs name should be unique*/
663 Pa_Debug.G_Stage := 'Get a count of the number of rbs headers using the p_name parameter passed in.';
664 Select
665 Count(*)
666 Into
667 l_Count
668 From
669 Pa_Rbs_Headers_TL
670 Where
671 Name = P_Name
672 And language = USERENV('LANG');
673
674 Pa_Debug.G_Stage := 'Check if the count of rbs headers using the p_name parameter is <> 0.';
675 IF l_count <> 0 THEN
676
677 Pa_Debug.G_Stage := 'When create rbs the header name must be unique. Add message to error stack.';
678 Pa_Utils.Add_Message(
679 P_App_Short_Name => 'PA',
680 P_Msg_Name => 'PA_RBS_NOT_UNIQUE',
681 P_Token1 => 'RBSNAME',
682 P_Value1 => P_Name);
683
684 l_Error_Raised := 'Y';
685
686 End If;
687
688 Pa_Debug.G_Stage := 'Check if any validation error occured.';
689 If l_Error_Raised = 'Y' Then
690
691 Pa_Debug.G_Stage := 'Validation errors occured. Raise l_Error.';
692 Raise l_Error;
693
694 End If;
695
696 --Inserts into pa_rbs_header table.
697 Pa_Debug.G_Stage := 'Insert header record by calling the Pa_Rbs_Header_Pvt.Insert_Header() procedure.';
698 Pa_Rbs_Header_Pvt.Insert_Header(
699 P_Name => P_Name,
700 P_Description => P_Description,
701 P_EffectiveFrom => P_EffectiveFrom,
702 P_EffectiveTo => P_EffectiveTo,
703 P_Use_For_Alloc_Flag => P_Use_For_Alloc_Flag,
704 X_RbsHeaderId => X_Rbs_Header_Id,
705 X_Return_Status => X_Return_Status,
706 X_Msg_Data => X_Msg_Data,
707 X_Msg_Count => X_Msg_Count);
708
709 If X_Return_Status ='U' Then
710
711 Pa_Debug.G_Stage := 'Calling to Pa_Rbs_Header_Pvt.Insert_Header() procedure returned error. Raise.';
712 Raise l_UnExp_Error;
713
714 End If;
715
716 Pa_Debug.G_Stage := 'Insert the version record by calling the Pa_Rbs_Header_Pvt.Insert_Versions() procedure.';
717 Pa_Rbs_Header_Pvt.Insert_Versions(
718 P_RbsHeaderId => X_Rbs_Header_Id,
719 P_Name => P_Name,
720 P_Description => P_Description,
721 P_EffectiveFrom => P_EffectiveFrom,
722 X_Rbs_Version_Id => X_Rbs_Version_Id,
723 X_Return_Status => X_Return_Status,
724 X_Msg_Data => X_Msg_Data,
725 X_Msg_Count => X_Msg_Count );
726
727 If X_Return_Status ='U' Then
728
729 Pa_Debug.G_Stage := 'Calling to Pa_Rbs_Header_Pvt.Insert_Versions() procedure returned error. Raise.';
730 Raise l_UnExp_Error;
731
732 End If;
733
734 Pa_Debug.G_Stage := 'Insert the root element node for the rbs by callling the Pa_Rbs_Header_Pvt.Insert_Structure_Element() procedure.';
735 Pa_Rbs_Header_Pvt.Insert_Structure_Element(
736 P_Rbs_Version_Id => X_Rbs_Version_Id,
737 X_Rbs_Element_Id => X_Rbs_Element_Id,
738 X_Return_Status => X_Return_Status,
739 X_Error_Msg_Data => X_Msg_Data,
740 X_Msg_Count => X_Msg_Count);
741
742 If X_Return_Status ='U' Then
743
744 Pa_Debug.G_Stage := 'Calling to Pa_Rbs_Header_Pvt.Insert_Structure_Element() procedure returned error. Raise.';
745 Raise l_UnExp_Error;
746
747 End If;
748
749 Pa_Debug.G_Stage := 'Check if need to commit data.';
750 If Fnd_Api.To_Boolean(Nvl(P_Commit,Fnd_Api.G_False)) Then
751
752 Pa_Debug.G_Stage := 'Commit inserts to db.';
753 Commit;
754
755 End If;
756
757 Pa_Debug.G_Stage := 'Leaving Insert_Header() procedure.';
758 Pa_Debug.TrackPath('STRIP','Insert_Header');
759
760 Exception
761 When l_Error Then
762 X_Return_Status := Fnd_Api.G_Ret_Sts_Error;
763 l_Msg_Count := Fnd_Msg_Pub.Count_Msg;
764 If l_msg_count = 1 Then
765 Pa_Interface_Utils_Pub.Get_Messages(
766 p_encoded => Fnd_Api.G_True,
767 p_msg_index => 1,
768 p_msg_count => l_msg_count,
769 p_msg_data => l_msg_data,
770 p_data => l_data,
771 p_msg_index_out => l_msg_index_out);
772
773 x_msg_data := l_data;
774 x_msg_count := l_msg_count;
775 Else
776 x_msg_count := l_msg_count;
777 End If;
778
779 When l_UnExp_Error Then
780 X_Return_Status := 'U';
781 X_Msg_Data := Pa_Rbs_Header_Pub.G_Pkg_Name || ':::' || Pa_Debug.G_Path || '::' || Pa_Debug.G_Stage || ':' || X_Msg_Data;
782 X_Msg_Count := 1;
783 Rollback;
784
785 When Others Then
786 X_Return_Status := 'U';
787 X_Msg_Data := Pa_Rbs_Header_Pub.G_Pkg_Name || ':::' || Pa_Debug.G_Path || '::' || Pa_Debug.G_Stage || ':' || SqlErrm;
788 X_Msg_Count := 1;
789 Rollback;
790
791 END Insert_Header;
792
793 END Pa_Rbs_Header_Pub;
[Home] [Help]