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