1 Package Body Pa_Rbs_Pub AS
2 /* $Header: PARBSAPB.pls 120.1 2005/08/19 04:23:04 avaithia noship $*/
3 /*
4 * ***************************************************************************************
5 * API Name: Convert_Missing_Rbs_Header
6 * Public/Private : Private
7 * Procedure/Function : Procedure
8 * Description:
9 * This procedure converts the input values to null if its a G _MISS_VALUE
10 * and selects the value from the database if the value being input is null,
11 * so that there wouldn't be any change to the data when null is passed as input.
12 * The conversions are done for the Rbs_Version_Rec_Typ .
13 * Attributes :
14 * INPUT VALUES :
15 * P_Header_Rec : The record which hold's the rbs header's record .
16 * This contains the input record.
17 * P_Mode : The mode in which the procedure is called.Update or create
18 * 1 means update. 0 means create.
19 * OUTPUT VALUES :
20 *
21 * X_Header_Rec : The record which hold's the rbs header record with the
22 * changed values to the fields of the record.
23 * X_Error_Msg : The parameter will hold a message if there is an
24 * error in this API.
25 * There can be two modes in which this procedure can be called
26 * Update and create
27 * P_Mode = 1 means update
28 * P_Mode = 0 means create
29 * In Update
30 * if Update then rbs header id can be present.
31 * if rbs header id is not present then rbs header name would be present .
32 * you can retreive the rbs header id from the rbs header name .
33 * And then convert all GMiss to the db values.
34 *
35 * In create the rbs name would be present
36 * Convert all missing values to null.
37 *
38 * ****************************************************************************************
39 */
40
41 PROCEDURE Convert_Missing_Rbs_Header
42 (P_Header_Rec IN Rbs_Header_Rec_Typ,
43 X_Header_Rec OUT NOCOPY Rbs_Header_Rec_Typ, -- 4537865 Added the nocopy hint
44 P_Mode IN Number,
45 X_Error_Msg OUT NOCOPY VARCHAR2)
46 IS
47
48
49
50 Cursor C_Rbs_Header_Details(P_Rbs_Header_Id IN Number) IS
51
52 Select a.rbs_header_id,
53 b.name,
54 b.Description,
55 a.Effective_From_Date,
56 a.Effective_To_Date,
57 a.Record_Version_Number
58 From pa_rbs_headers_b a,
59 pa_rbs_headers_tl b
60 Where a.rbs_header_id=P_Rbs_Header_Id
61 and b.language = userenv('LANG')
62 and a.rbs_header_id=b.rbs_header_id;
63
64 Rec_Details C_Rbs_Header_Details%RowType;
65
66 l_rbs_header_id NUMBER;
67 l_rbs_header_name VARCHAR2(200);
68 l_ERROR Exception;
69
70
71 BEGIN
72
73 X_Header_Rec :=P_Header_Rec;
74
75 l_rbs_header_id :=P_Header_Rec.Rbs_Header_Id;
76
77 If l_rbs_header_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM Then
78 l_rbs_header_id := null;
79 End if;
80
81 l_rbs_header_name := P_Header_Rec.Name ;
82
83
84
85
86 IF P_Mode = 1 Then
87 IF l_rbs_header_id is null Then
88 -- Get the header id from the name if the name is not null
89 IF l_rbs_header_name is not null and l_rbs_header_name <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
90 BEGIN
91 Select rbs_header_id
92 into l_rbs_header_id
93 from pa_rbs_headers_tl
94 where name= l_rbs_header_name
95 AND language = userenv('LANG');
96 EXCEPTION
97 WHEN OTHERS THEN
98 Pa_Debug.G_Stage := 'No Rbs Element id was provided. Add error message to stack.';
99 Pa_Utils.Add_Message(
100 P_App_Short_Name => 'PA',
101 P_Msg_Name => 'PA_RBS_HEADER_NAME_INVALID');
102
103 Raise l_ERROR;
104
105 END;
106 ELSE
107 BEGIN
108 Select rbs_header_id
109 into l_rbs_header_id
110 from pa_rbs_headers_b
111 where rbs_header_id = P_Header_Rec.Rbs_Header_Id ;
112 EXCEPTION
113 WHEN OTHERS THEN
114 Pa_Debug.G_Stage := 'No Rbs Element id was provided. Add error message to stack.';
115 Pa_Utils.Add_Message(
116 P_App_Short_Name => 'PA',
117 P_Msg_Name => 'PA_RBS_HEADER_ID_INVALID');
118
119 Raise l_ERROR;
120
121 END;
122 END IF;
123 End if;
124
125
126 IF l_rbs_header_id <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM and l_rbs_header_id is not null Then
127 OPEN C_Rbs_Header_Details(P_Rbs_Header_Id => l_rbs_header_id);
128 FETCH C_Rbs_Header_Details INTO Rec_Details;
129 CLOSE C_Rbs_Header_Details;
130
131 If P_Header_Rec.Rbs_Header_Id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM Then
132 X_Header_Rec.Rbs_Header_Id := Rec_Details.Rbs_Header_Id;
133
134 End If;
135
136 If P_Header_Rec.Name = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
137 X_Header_Rec.Name := Rec_Details.Name;
138
139 End If;
140
141 If P_Header_Rec.Description = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
142 X_Header_Rec.Description := Rec_Details.Description;
143
144 End If;
145
146 If P_Header_Rec.Effective_From_Date = PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE Then
147 X_Header_Rec.Effective_From_Date := Rec_Details.Effective_From_Date;
148
149 End If;
150
151 If P_Header_Rec.Effective_To_Date = PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE Then
152 X_Header_Rec.Effective_To_Date := Rec_Details.Effective_To_Date;
153
154 End If;
155
156 If P_Header_Rec.Record_Version_Number = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM Then
157 X_Header_Rec.Record_Version_Number := null;
158
159 End If;
160
161
162 End If;
163 ElsIf P_Mode = 0 Then
164 If P_Header_Rec.Rbs_Header_Id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM Then
165 X_Header_Rec.Rbs_Header_Id := null;
166 End If;
167 If P_Header_Rec.Name = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
168 X_Header_Rec.Name := null;
169
170 End If;
171
172 If P_Header_Rec.Description = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
173 X_Header_Rec.Description := null;
174
175 End If;
176
177 If P_Header_Rec.Effective_From_Date = PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE Then
178 X_Header_Rec.Effective_From_Date := null;
179
180 End If;
181
182 If P_Header_Rec.Effective_To_Date = PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE Then
183 X_Header_Rec.Effective_To_Date := null;
184
185 End If;
186
187 If P_Header_Rec.Record_Version_Number = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM Then
188 X_Header_Rec.Record_Version_Number := null;
189
190 End If;
191
192
193 End if;
194
195 Exception
196 When Others Then
197 Null ;
198 -- just leave it, let the exception be handled by the table handler.
199 End Convert_Missing_Rbs_Header;
200
201
202 /*
203 * ***************************************************************************************
204 * API Name: Convert_Missing_Rbs_Version
205 * Public/Private : Private
206 * Procedure/Function : Procedure
207 * Description:
208 * This procedure converts the input values to null if its a G _MISS_VALUE
209 * and selects the value from the database if the value being input is null,
210 * so that there wouldn't be any change to the data when null is passed as input.
211 * The conversions are done for the Rbs_Version_Rec_Typ .
212 * Attributes :
213 * INPUT VALUES :
214 * P_Version_Rec : The record which hold's the rbs version's record .
215 * This contains the input record.
216 *
217 * OUTPUT VALUES :
218 *
219 * X_Version_Rec : The record which hold's the rbs version record with the
220 * changed values to the fields of the record.
221 * X_Error_Msg : The parameter will hold a message if there is an
222 * error in this API.
223 *
224 * There can be two modes in which this procedure can be called
225 * Update and create
226 * P_Mode = 1 means update
227 * P_Mode = 0 means create
228 * In Update
229 * if Update then rbs version id can be present.
230 * if rbs version id is not present then rbs version name would be present .
231 * you can retreive the rbs header id from the name .
232 * And then convert all GMiss to the db values.
233 *
234 * In create the rbs name would be present
235 * Convert all missing values to null.
236 * ****************************************************************************************
237 */
238 PROCEDURE Convert_Missing_Rbs_Version
239 (P_Version_Rec IN Rbs_Version_Rec_Typ,
240 X_Version_Rec OUT NOCOPY Rbs_Version_Rec_Typ, -- 4537865
241 P_Mode IN Number,
242 X_Error_Msg OUT NOCOPY VARCHAR2)
243 IS
244
245 Cursor C_Rbs_Version_Details(P_Rbs_Version_Id IN Number) IS
246 Select a.rbs_version_id, b.name, b.Description,a.Version_Start_Date ,a.Job_Group_Id ,a.Record_Version_Number
247 from pa_rbs_versions_b a, pa_rbs_versions_tl b
248 Where a.rbs_version_id=P_Rbs_version_Id
249 and a.rbs_version_id=b.rbs_version_id;
250
251 Rec_Details C_Rbs_Version_Details%RowType;
252
253 l_rbs_version_id NUMBER;
254 l_rbs_version_name VARCHAR2(200) ;
255 BEGIN
256
257 X_Version_Rec :=P_Version_Rec;
258
259 l_rbs_version_id :=P_Version_Rec.Rbs_Version_Id;
260 If l_rbs_version_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM Then
261 l_rbs_version_id := null;
262 End if;
263 l_rbs_version_name := P_Version_Rec.Name ;
264
265
266
267
268 IF P_Mode = 1 Then
269 IF l_rbs_version_id is null Then
270 -- Get the header id from the name if the name is not null
271 IF l_rbs_version_name is not null and l_rbs_version_name <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
272 Select rbs_version_id
273 Into l_rbs_version_id
274 From pa_rbs_versions_tl
275 Where name= l_rbs_version_name ;
276 End if ;
277 End if;
278
279 IF l_rbs_version_id <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM and l_rbs_version_id is not null Then
280 OPEN C_Rbs_Version_Details(P_Rbs_Version_Id => l_rbs_version_id);
281 FETCH C_Rbs_Version_Details INTO Rec_Details;
282 CLOSE C_Rbs_Version_Details;
283
284 If P_Version_Rec.Rbs_Version_Id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM Then
285 X_Version_Rec.Rbs_Version_Id := Rec_Details.Rbs_Version_Id;
286
287 End If;
288 If P_Version_Rec.Name = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
289 X_Version_Rec.Name := Rec_Details.Name;
290
291 End If;
292
293 If P_Version_Rec.Description = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
294 X_Version_Rec.Description := Rec_Details.Description;
295
296 End If;
297
298 If P_Version_Rec.Version_Start_Date = PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE Then
299 X_Version_Rec.Version_Start_Date := Rec_Details.Version_Start_Date;
300
301 End If;
302
303 If P_Version_Rec.Job_Group_Id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM Then
304 X_Version_Rec.Job_Group_Id := Rec_Details.Job_Group_Id;
305
306 End If;
307
308 If P_Version_Rec.Record_Version_Number = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM Then
309 X_Version_Rec.Record_Version_Number := Rec_Details.Record_Version_Number;
310
311 End If;
312 End If;
313 Elsif P_Mode = 0 Then
314 If P_Version_Rec.Rbs_Version_Id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM Then
315 X_Version_Rec.Rbs_Version_Id := null;
316 End If;
317
318 If P_Version_Rec.Name = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
319 X_Version_Rec.Name := null;
320
321 End If;
322
323 If P_Version_Rec.Description = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
324 X_Version_Rec.Description := null;
325
326 End If;
327
328 If P_Version_Rec.Version_Start_Date = PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE Then
329 X_Version_Rec.Version_Start_Date := null;
330
331 End If;
332
333 If P_Version_Rec.Job_Group_Id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM Then
334 X_Version_Rec.Job_Group_Id := null;
335
336 End If;
337
338 If P_Version_Rec.Record_Version_Number = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM Then
339 X_Version_Rec.Record_Version_Number := null;
340
341 End If;
342
343
344 End if;
345 Exception
346 When Others Then
347 Null;
348 -- just leave it, let the exception be handled by the table handler.
349
350 End Convert_Missing_Rbs_Version;
351
352
353 /*
354 * ***************************************************************************************
355 * API Name: Convert_Missing_Rbs_Elements
356 * Public/Private : Private
357 * Procedure/Function : Procedure
358 * Description:
359 * This procedure converts the input values to null if its a G _MISS_VALUE
360 * and selects the value from the database if the value being input is null,
361 * so that there wouldn't be any change to the data when null is passed as input.
362 * The conversions are done for the Rbs_Elements_Rec_Typ .
363 * Attributes :
364 * INPUT VALUES :
365 * P_Elements_Tbl : The table which hold's the rbs element's records .
366 * This contains the input records.
367 *
368 * OUTPUT VALUES :
369 *
370 * X_Elements_Tbl : The table which hold's the rbs element's records with the
371 * changed values
372 * X_Error_Msg : The parameter will hold a message if there is an
373 * error in this API.
374 *
375 * ****************************************************************************************
376 */
377 PROCEDURE Convert_Missing_Rbs_Elements
378 (P_Elements_Tbl IN Rbs_Elements_Tbl_Typ,
379 X_Elements_Tbl OUT NOCOPY Rbs_Elements_Tbl_Typ, -- 4537865
380 P_Mode IN Number,
381 X_Error_Msg OUT NOCOPY VARCHAR2)
382 IS
383
384 Cursor C_Rbs_Elements_Details(P_Rbs_Element_Id IN Number) IS
385
386 Select
387 a.Rbs_Version_Id,
388 a.rbs_Element_Id ,
389 a.Parent_Element_Id ,
390 a.Resource_Type_Id,
391 a.Resource_Source_Id ,
392 b.resource_name Resource_Source_Code,
393 a.Rbs_Level,
394 a.Record_Version_Number,
395 a.Order_Number
396 From pa_rbs_elements a ,
397 pa_rbs_element_map b
398 Where rbs_element_id=P_Rbs_Element_Id
399 and a.resource_source_id =b.resource_id(+) ;
400
401
402
403 /*Rbs_Version_Id Number(15) Default PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
404 Rbs_Element_Id Number(15) Default PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
405 Parent_Element_Id Number(15) Default PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
406 Resource_Type_Id Number(15),
407 Resource_Source_Id Number(15) Default PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
408 Resource_Source_Code Varchar2(240) Default PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
409 Order_Number Number(15) Default PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
410 Process_Type Varchar2(1),
411 Rbs_Level Number(15),
412 Record_Version_Number Number(15) Default PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
413 Parent_Ref_Element_Id Number(15) Default PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
414 Rbs_Ref_Element_Id Number(15) Default PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM);
415 */
416 Rec_Details C_Rbs_Elements_Details%RowType;
417
418 l_rbs_version_id NUMBER;
419 l_rbs_element_id NUMBER;
420
421 BEGIN
422
423 X_Elements_Tbl :=P_Elements_Tbl;
424
425 /* get the rbs version id from the first record of the table */
426
427 IF P_Elements_Tbl.count >0 then
428
429 IF P_Mode = 1 Then
430
431 For i in P_Elements_Tbl.First .. P_Elements_Tbl.Last
432 Loop
433 l_rbs_element_id := P_Elements_Tbl(i).Rbs_Element_Id ;
434 OPEN C_Rbs_Elements_Details(P_Rbs_Element_Id => l_rbs_element_id);
435
436 IF C_Rbs_Elements_Details%NOTFOUND Then
437
438 CLOSE C_Rbs_Elements_Details;
439 Else
440 FETCH C_Rbs_Elements_Details INTO Rec_Details;
441 CLOSE C_Rbs_Elements_Details;
442
443 If P_Elements_Tbl(i).Rbs_Version_Id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM Then
444 X_Elements_Tbl(i).Rbs_Version_Id := Rec_Details.Rbs_Version_Id;
445
446 End If;
447
448 If P_Elements_Tbl(i).Parent_Element_Id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM Then
449 X_Elements_Tbl(i).Parent_Element_Id := Rec_Details.Parent_Element_Id;
450
451 End If;
452
453 If P_Elements_Tbl(i).Resource_Source_Id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM Then
454 X_Elements_Tbl(i).Resource_Source_Id := Rec_Details.Resource_Source_Id;
455
456 End If;
457
458
459
460 If P_Elements_Tbl(i).Resource_Source_Code = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
461 X_Elements_Tbl(i).Resource_Source_Code := Rec_Details.Resource_Source_Code;
462
463 End If;
464
465
466
467 If P_Elements_Tbl(i).Order_Number = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM Then
468 X_Elements_Tbl(i).Order_Number := Rec_Details.Order_Number;
469
470 End If;
471
472
473
474 If P_Elements_Tbl(i).Record_Version_Number = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM Then
475 X_Elements_Tbl(i).Record_Version_Number := Rec_Details.Record_Version_Number;
476
477 End If;
478
479 If P_Elements_Tbl(i).Process_Type = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
480 X_Elements_Tbl(i).Process_Type := null;
481 End if;
482
483
484 If P_Elements_Tbl(i).Rbs_Level = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM Then
485 X_Elements_Tbl(i).Rbs_Level := Rec_Details.Rbs_Level;
486
487 End If;
488
489 If P_Elements_Tbl(i).Resource_Type_Id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM Then
490 X_Elements_Tbl(i).Resource_Type_Id := Rec_Details.Resource_Type_Id;
491
492 End If;
493
494
495 If P_Elements_Tbl(i).Parent_Ref_Element_Id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM Then
496 X_Elements_Tbl(i).Parent_Ref_Element_Id := null;
497
498 End If;
499
500 If P_Elements_Tbl(i).Rbs_Ref_Element_Id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM Then
501 X_Elements_Tbl(i).Rbs_Ref_Element_Id := null;
502
503 End If;
504 End if ;
505 End Loop;
506
507 ElsIf P_Mode =0 Then
508 For i in P_Elements_Tbl.First .. P_Elements_Tbl.Last
509 Loop
510 If P_Elements_Tbl(i).Rbs_Version_Id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM Then
511 X_Elements_Tbl(i).Rbs_Version_Id := null;
512
513 End If;
514
515 If P_Elements_Tbl(i).Rbs_Element_Id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM Then
516 X_Elements_Tbl(i).Rbs_Element_Id := null;
517
518 End If;
519
520
521
522 If P_Elements_Tbl(i).Parent_Element_Id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM Then
523 X_Elements_Tbl(i).Parent_Element_Id := null;
524
525 End If;
526
527
528
529 If P_Elements_Tbl(i).Resource_Source_Id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM Then
530 X_Elements_Tbl(i).Resource_Source_Id := null;
531
532 End If;
533
534
535
536 If P_Elements_Tbl(i).Resource_Source_Code = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
537 X_Elements_Tbl(i).Resource_Source_Code := null;
538
539 End If;
540
541
542
543 If P_Elements_Tbl(i).Order_Number = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM Then
544 X_Elements_Tbl(i).Order_Number := null;
545
546 End If;
547
548
549
550 If P_Elements_Tbl(i).Record_Version_Number = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM Then
551 X_Elements_Tbl(i).Record_Version_Number := null;
552
553 End If;
554
555 If P_Elements_Tbl(i).Process_Type = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
556 X_Elements_Tbl(i).Process_Type := null;
557
558 End If;
559
560
561 If P_Elements_Tbl(i).Rbs_Level = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM Then
562 X_Elements_Tbl(i).Rbs_Level := null;
563
564 End If;
565
566 If P_Elements_Tbl(i).Resource_Type_Id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM Then
567 X_Elements_Tbl(i).Resource_Type_Id := null;
568
569 End If;
570
571 If P_Elements_Tbl(i).Parent_Ref_Element_Id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM Then
572 X_Elements_Tbl(i).Parent_Ref_Element_Id := null;
573
574 End If;
575
576 If P_Elements_Tbl(i).Rbs_Ref_Element_Id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM Then
577 X_Elements_Tbl(i).Rbs_Ref_Element_Id := null;
578
579 End If;
580 End Loop;
581 End If;
582 End If;
583 End Convert_Missing_Rbs_Elements;
584
585 /*
586 ****************************************************************************************
587 * API Name: Init_Rbs_Processing
588 * Description:
589 * This procedure initialize global pl/sql records and tables and
590 * other variables. It is use solely in conjunction with option 2
591 * identified in the package description on how to use.
592 * ****************************************************************************************
593 */
594 Procedure Init_Rbs_Processing IS
595
596 l_Api_Name CONSTANT Varchar2(30) := 'Init_Rbs_Processing';
597
598 Begin
599
600 Fnd_Msg_Pub.initialize;
601
602 G_Rbs_Hdr_Rec := G_Empty_Rbs_Hdr_Rec;
603 G_Rbs_Hdr_Out_Rec := G_Empty_Rbs_Hdr_Out_Rec;
604 G_Rbs_Ver_Rec := G_Empty_Rbs_Ver_Rec;
605 G_Rbs_Ver_Out_Rec := G_Empty_Rbs_Ver_Out_Rec;
606 G_Rbs_Elements_Tbl.Delete;
607 G_Rbs_Elements_Count := 0;
608
609 Exception
610 When Others Then
611
612 If Fnd_Msg_Pub.Check_Msg_Level(Fnd_Msg_Pub.G_Msg_Lvl_UnExp_Error) Then
613 Fnd_Msg_Pub.Add_Exc_Msg
614 ( P_Pkg_Name => G_Pkg_Name ,
615 P_Procedure_Name => l_Api_Name );
616
617 End If;
618 Rollback;
619 END Init_Rbs_Processing;
620
621 /*
622 ********************************************************************************************
623 * API Name: Create_Rbs()
624 * Description:
625 * At a minimum this API will create the header, version and root
626 * node/element records for the Resource Breakdown Structure.
627 * Otherwise, this API will create a complete Resource Breakdown
628 * Structure based on the data passed in.
629 *
630 *
631 * *******************************************************************************************
632 */
633 Procedure Create_Rbs (
634 P_Commit IN Varchar2 Default Fnd_Api.G_False,
635 P_Init_Msg_List IN Varchar2 Default Fnd_Api.G_True,
636 P_API_Version_Number IN Number,
637 P_Header_Rec IN Pa_Rbs_Pub.Rbs_Header_Rec_Typ,
638 P_Version_Rec IN Pa_Rbs_Pub.Rbs_Version_Rec_Typ Default G_Empty_Rbs_Ver_Rec,
639 P_Elements_Tbl IN Rbs_Elements_Tbl_Typ Default G_Empty_Rbs_Elements_Tbl,
640 X_Rbs_Header_Id OUT NOCOPY Number,
641 X_Rbs_Version_Id OUT NOCOPY Number,
642 X_Elements_Tbl OUT NOCOPY Rbs_Elements_Tbl_Typ,
643 X_Return_Status OUT NOCOPY Varchar2,
644 X_Msg_Count OUT NOCOPY Number,
645 X_Error_Msg_Data OUT NOCOPY Varchar2)
646
647 Is
648
649 i Number := Null;
650 l_Api_Name Varchar2(30) := 'Create_Rbs';
651 l_Resource_Source_Id Number := Null;
652 l_Dummy Varchar2(30) := Null;
653 l_Record_Version_Number Number := Null;
654
655 l_Rbs_Header_Id Number(15) := Null;
656 l_Rbs_Version_Id Number(15) := Null;
657 l_Rbs_Element_Id Number(15) := Null;
658 l_Msg_Count Number := 0;
659 l_ERROR Exception;
660
661 l_msg_data Varchar2(2000) := Null;
662 l_data Varchar2(2000) := Null;
663 l_msg_index_out Number;
664 l_count Number;
665 l_Max_Rbs_Level Number := 0;
666 l_Root_Count Number := 0;
667
668 l_Header_Rec Rbs_Header_Rec_Typ;
669 l_Version_Rec Rbs_Version_Rec_Typ;
670 l_Elements_Tbl Rbs_Elements_Tbl_Typ;
671 l_Mode Number := 0;
672
673 Cursor c1 Is
674 Select
675 Max(Rbs_Level)
676 From
677 Pa_Rbs_Nodes_Temp;
678
679 Cursor c2 (P_Rbs_Level IN Number) Is
680 Select
681 Rbs_Version_Id,
682 Rbs_Element_Id,
683 Parent_Element_Id,
684 Resource_Type_Id,
685 Resource_Source_Id,
686 Resource_Source_Code,
687 Order_Number,
688 Process_Type,
689 Rbs_Level,
690 Record_Version_Number,
691 Parent_Ref_Element_Id,
692 Rbs_Ref_Element_Id,
693 Record_Index
694 From
695 Pa_Rbs_Nodes_Temp
696 Where
697 Rbs_Level = P_Rbs_Level
698 Order By
699 Rbs_Ref_Element_Id;
700
701 Element_Rec c2%RowType;
702
703 Cursor c3 Is
704 Select
705 Count(*)
706 From
707 Pa_Rbs_Nodes_Temp
708 Where
709 Rbs_Level = 1;
710
711 Begin
712
713 Pa_Debug.G_Path := ' ';
714
715 Pa_Debug.G_Stage := 'Entering Create_Rbs().';
716 Pa_Debug.TrackPath('ADD','Create_Rbs');
717
718 Pa_Debug.G_Stage := 'Call Compatibility API.';
719 If Not Fnd_Api.Compatible_API_Call (
720 Pa_Rbs_Pub.G_Api_Version_Number,
721 P_Api_Version_Number,
722 l_Api_Name,
723 Pa_Rbs_Pub.G_Pkg_Name) Then
724
725 Raise Fnd_Api.G_Exc_Unexpected_Error;
726
727 End If;
728
729 Pa_Debug.G_Stage := 'Check if need to initialize the message stack(T-True,F-False) - ' || P_Init_Msg_List;
730 If Fnd_Api.To_Boolean(nvl(P_Init_Msg_List,Fnd_Api.G_True)) Then
731
732 Pa_Debug.G_Stage := 'Initializing message stack by calling Fnd_Msg_Pub.Initialize().';
733 Fnd_Msg_Pub.Initialize;
734
735 End If;
736
737 Pa_Debug.G_Stage := 'Initialize error handling variables.';
738 X_Error_Msg_Data := Null;
739 X_Msg_Count := 0;
740 X_Return_Status := Fnd_Api.G_Ret_Sts_Success;
741
742 /***********************************
743 call to covert missing
744 *************************************/
745 Pa_Rbs_Pub.Convert_Missing_Rbs_Header(
746 P_Header_Rec => P_Header_Rec,
747 X_Header_Rec => l_Header_Rec,
748 P_Mode => l_Mode ,
749 X_Error_Msg => X_Error_Msg_Data);
750
751 Pa_Rbs_Pub.Convert_Missing_Rbs_Version(
752 P_Version_Rec => P_Version_Rec,
753 X_Version_Rec => l_Version_Rec,
754 P_Mode => l_Mode ,
755 X_Error_Msg => X_Error_Msg_Data);
756
757 Pa_Rbs_Pub.Convert_Missing_Rbs_Elements(
758 P_Elements_Tbl => P_Elements_Tbl,
759 X_Elements_Tbl => l_Elements_Tbl,
760 P_Mode => l_Mode ,
761 X_Error_Msg => X_Error_Msg_Data);
762
763
764
765
766
767 /***********************************
768 Create Header,Version,Root Element
769 ***********************************/
770
771 Pa_Debug.G_Stage := 'Create Header Record by calling Pa_Rbs_Header_Pvt.Insert_Header() procedure.';
772 Pa_Rbs_Header_Pub.Insert_Header(
773 P_Commit => Fnd_Api.G_False,
774 P_Init_Msg_List => Fnd_Api.G_False,
775 P_API_Version_Number => P_API_Version_Number,
776 P_Name => l_Header_Rec.Name,
777 P_Description => Nvl(l_Header_Rec.Description,l_Header_Rec.Name),
778 P_EffectiveFrom => l_Header_Rec.Effective_From_Date,
779 P_EffectiveTo => l_Header_Rec.Effective_To_Date,
780 X_Rbs_Header_Id => l_Rbs_Header_Id,
781 X_Rbs_Version_Id => l_Rbs_Version_Id,
782 X_Rbs_Element_Id => l_Rbs_Element_Id,
783 X_Return_Status => X_Return_Status,
784 X_Msg_Data => X_Error_Msg_Data,
785 X_Msg_Count => l_Msg_Count);
786
787 If X_Error_Msg_Data is Not Null Then
788
789 Pa_Debug.G_Stage := 'The Pa_Rbs_Header_Pub.Insert_Header() procedure returned errror.';
790 Raise l_ERROR;
791
792 Else
793
794 G_Rbs_Hdr_Out_Rec.Rbs_Header_Id := l_Rbs_Header_Id;
795 X_Rbs_Header_Id := l_Rbs_Header_Id;
796 -- G_Rbs_Hdr_Out_Rec.Return_Status := Fnd_Api.G_Ret_Sts_Success;
797
798 G_Rbs_Ver_Out_Rec.Rbs_Version_Id := l_Rbs_Version_Id;
799 X_Rbs_Version_Id := l_Rbs_Version_Id;
800 -- G_Rbs_Ver_Out_Rec.Return_Status := Fnd_Api.G_Ret_Sts_Success;
801
802 End If;
803
804 /***************************
805 Process the Version Rec
806 ***************************/
807
808 -- Since the Version Record is already created we want to check and see if we need to update the record or not.
809 -- That is determined by if there is any data passed into the global pl/sql version table.
810 Pa_Debug.G_Stage := 'Check if the version record is not null.';
811 If l_Version_Rec.Name is Not Null or
812 l_Version_Rec.Version_Start_Date is Not Null or
813 l_Version_Rec.Job_Group_Id is Not Null or
814 l_Version_Rec.Description is Not Null Then
815
816 Pa_Debug.G_Stage := 'Calling Pa_Rbs_Versions_Pub.Update_Working_Version() API.';
817 Pa_Rbs_Versions_Pub.Update_Working_Version(
818 P_Commit => Fnd_Api.G_False,
819 P_Init_Msg_List => Fnd_Api.G_False,
820 P_API_Version_Number => P_Api_Version_Number,
821 P_RBS_Version_Id => l_Rbs_Version_Id,
822 P_Name => Nvl(l_Version_Rec.Name,l_Header_Rec.Name),
823 P_Description => Nvl(l_Version_Rec.Description,Nvl(l_Header_Rec.Description,l_Header_Rec.Name)),
824 P_Version_Start_Date => Nvl(l_Version_Rec.Version_Start_Date,l_Header_Rec.Effective_From_Date),
825 P_Job_Group_Id => l_Version_Rec.Job_Group_Id,
826 P_Record_Version_Number => 1,
827 P_Init_Debugging_Flag => 'N',
828 X_Record_Version_Number => l_Record_Version_Number,
829 X_Return_Status => X_Return_Status,
830 X_Msg_Count => l_Msg_Count,
831 X_Error_Msg_Data => X_Error_Msg_Data);
832
833 If X_Error_Msg_Data is Not Null Then
834
835 Pa_Debug.G_Stage := 'The Pa_Rbs_Versions_Pub.Update_Working_Version() procedure returned errror. ' ||
836 'Add error message to stack.';
837 Raise l_ERROR;
838
839 End If;
840
841 End If;
842
843 /*****************************
844 Process the Elements/Nodes
845 *****************************/
846
847 Pa_Debug.G_Stage := 'Do we have element/nodes to process.';
848 If l_Elements_Tbl.Count > 0 Then
849
850 Pa_Debug.G_Stage := 'Copy elements pl/sql table to out parameter.';
851 X_Elements_Tbl := l_Elements_Tbl;
852
853 -- Put the pl/sql table into a temp table, checking for problems before inserting.
854 Pa_Debug.G_Stage := 'Start loop thru pl/sql table for elements/nodes processing.';
855 For i in l_Elements_Tbl.First .. l_Elements_Tbl.Last
856 Loop
857
858 Pa_Debug.G_Stage := 'Check if we have a rbs reference element id to work with.';
859 If l_Elements_Tbl(i).Rbs_Ref_Element_Id is Null Then
860
861 Pa_Debug.G_Stage := 'No Rbs Element id was provided. Add error message to stack.';
862 Pa_Utils.Add_Message(
863 P_App_Short_Name => 'PA',
864 P_Msg_Name => 'PA_RBS_REF_ELEMENT_ID_REQ');
865
866 Raise l_ERROR;
867
868 End If;
869
870 Pa_Debug.G_Stage := 'Check if we have a parent reference element id to work with.';
871 If l_Elements_Tbl(i).Parent_Ref_Element_Id Is Null And
872 l_Elements_Tbl(i).Rbs_Level not in (1,2) Then
873
874 Pa_Debug.G_Stage := 'No parent reference element id was provided. Add error message to stack.';
875 Pa_Utils.Add_Message(
876 P_App_Short_Name => 'PA',
877 P_Msg_Name => 'PA_RBS_REF_PARENT_ID_REQ');
878
879 Raise l_ERROR;
880
881 End If;
882
883 Pa_Debug.G_Stage := 'Check if we have a rbs level to work with.';
884 If l_Elements_Tbl(i).Rbs_Level is Null Then
885
886 Pa_Debug.G_Stage := 'Rbs level is null. Add error message to stack.';
887 Pa_Utils.Add_Message(
888 P_App_Short_Name => 'PA',
889 P_Msg_Name => 'PA_RBS_RBS_LEVEL_REQ');
890
891 Raise l_ERROR;
892
893 End If;
894
895 Pa_Debug.G_Stage := 'Check if we have a process type that we can work with.';
896 If l_Elements_Tbl(i).Process_Type is Null or
897 l_Elements_Tbl(i).Process_Type <> 'A' Then
898
899 Pa_Debug.G_Stage := 'Process Type is null or not A. Add error message to stack.';
900 Pa_Utils.Add_Message(
901 P_App_Short_Name => 'PA',
902 P_Msg_Name => 'PA_RBS_PRC_TYPE_INVALID');
903
904 Raise l_ERROR;
905
906 End If;
907
908 Pa_Debug.G_Stage := 'Insert record into pa_rbs_nodes_temp table for further processing.';
909 Insert into Pa_Rbs_Nodes_Temp (
910 Rbs_Version_Id,
911 Rbs_Element_Id,
912 Parent_Element_Id,
913 Resource_Type_Id,
914 Resource_Source_Id,
915 Resource_Source_Code,
916 Order_Number,
917 Process_Type,
918 Rbs_Level,
919 Record_Version_Number,
920 Parent_Ref_Element_Id,
921 Rbs_Ref_Element_Id,
922 Record_Index )
923 Values (
924 l_Rbs_Version_Id,
925 Decode(l_Elements_Tbl(i).Rbs_Level,1,l_Rbs_Element_Id,Null),
926 Decode(l_Elements_Tbl(i).Rbs_Level,2,l_Rbs_Element_Id,Null),
927 Decode(l_Elements_Tbl(i).Rbs_Level,1,-1,l_Elements_Tbl(i).Resource_Type_Id),
928 Decode(l_Elements_Tbl(i).Rbs_Level,1,l_Rbs_Version_Id,l_Elements_Tbl(i).Resource_Source_Id),
929 l_Elements_Tbl(i).Resource_Source_Code,
930 l_Elements_Tbl(i).Order_Number,
931 Decode(l_Elements_Tbl(i).Rbs_Level,1,'R',l_Elements_Tbl(i).Process_Type),
932 l_Elements_Tbl(i).Rbs_Level,
933 l_Elements_Tbl(i).Record_Version_Number,
934 l_Elements_Tbl(i).Parent_Ref_Element_Id,
935 l_Elements_Tbl(i).Rbs_Ref_Element_Id,
936 i );
937
938 If l_Elements_Tbl(i).Rbs_Level = 1 Then
939
940 -- This is needed by the Fetch_Rbs_Element() procedure.
941 -- The root element/node is not process beyond this point.
942 -- It is created once for a RBS and is never touched again.
943 -- Default values are used for the element/node and is already created.
944 G_Rbs_Elements_Out_Tbl(i).Rbs_Element_Id := l_Rbs_Element_Id;
945 -- G_Rbs_Elements_Out_Tbl(i).Return_Status := Fnd_Api.G_Ret_Sts_Success;
946
947 X_Elements_Tbl(i).Rbs_Element_Id := l_Rbs_Element_Id;
948 X_Elements_Tbl(i).Resource_Type_Id := -1;
949 X_Elements_Tbl(i).Resource_Source_Id := l_Rbs_Version_Id;
950 X_Elements_Tbl(i).Resource_Source_Code := Null;
951
952 End If;
953
954 End Loop;
955
956 -- Check to see if have a root node passed in the pl/sql table. This is required
957 -- even though they have minimul control over it values.
958 -- Root element/node is always level one.
959 Pa_Debug.G_Stage := 'Open c3 to get count of rbs level 1 records.';
960 Open c3;
961 Fetch c3 Into l_Root_Count;
962 Close c3;
963
964 If l_Root_Count = 0 Then
965
966 Pa_Debug.G_Stage := 'No root element/node provided. Add error message to stack.';
967 Pa_Utils.Add_Message(
968 P_App_Short_Name => 'PA',
969 P_Msg_Name => 'PA_RBS_NO_ROOT_ELEMENT');
970
971 Raise l_ERROR;
972
973 End If;
974
975 -- Check to see if provide more than a single root element. This is not allowed.
976 If l_Root_Count > 1 Then
977
978 Pa_Debug.G_Stage := 'Multiple root elements/nodes provided. Add error message to stack.';
979 Pa_Utils.Add_Message(
980 P_App_Short_Name => 'PA',
981 P_Msg_Name => 'PA_RBS_MULTI_ROOT_ELEMENTS');
982
983 Raise l_ERROR;
984
985 End If;
986
987 Pa_Debug.G_Stage := 'Open c1 cursor to get max rbs level.';
988 Open c1;
989 Fetch c1 Into l_Max_Rbs_Level;
990 Close c1;
991
992 Pa_Debug.G_Stage := 'Start rbs level loop.';
993 For i in 2 .. l_Max_Rbs_Level
994 Loop
995
996 Pa_Debug.G_Stage := 'Open c2 cursor for current rbs level elements/nodes to process.';
997 Open c2(P_Rbs_Level => i);
998
999 Pa_Debug.G_Stage := 'Start loop to process the current rbs level elements/nodes.';
1000 Loop
1001
1002 Pa_Debug.G_Stage := 'Fetch c2 record.';
1003 Fetch c2 Into Element_Rec;
1004 Exit When c2%NotFound;
1005
1006 Pa_Debug.G_Stage := 'Check if resource source id is null.';
1007 If Element_Rec.Resource_Source_Id is Null Then
1008
1009 Pa_Debug.G_Stage := 'Get Resource Source Id using source code.';
1010 Pa_Rbs_Elements_Utils.GetResSourceId(
1011 P_Resource_Type_Id => Element_Rec.Resource_Type_Id,
1012 P_Resource_Source_Code => Element_Rec.Resource_Source_Code,
1013 X_Resource_Source_Id => l_Resource_Source_Id);
1014
1015 IF l_Resource_Source_Id IS NULL THEN
1016 Pa_Utils.Add_Message(P_App_Short_Name => 'PA',
1017 P_Msg_Name => 'PA_RBS_NO_RESOURCE_SOURCE_ID');
1018
1019 Raise l_ERROR;
1020 END IF;
1021 Else
1022
1023 Pa_Debug.G_Stage := 'Assign resource source id.';
1024 l_Resource_Source_Id := Element_Rec.Resource_Source_Id;
1025
1026 End If;
1027
1028 Pa_Debug.G_Stage := 'Call Process_Rbs_Elements() procedure.';
1029 Pa_Rbs_Elements_Pvt.Process_Rbs_Element(
1030 P_RBS_Version_Id => Element_Rec.Rbs_Version_Id,
1031 P_Parent_Element_Id => Element_Rec.Parent_Element_Id,
1032 P_Element_Id => Element_Rec.Rbs_Element_Id,
1033 P_Resource_Type_Id => Element_Rec.Resource_Type_Id,
1034 P_Resource_Source_Id => l_Resource_Source_Id,
1035 P_Order_Number => Element_Rec.Order_Number,
1036 P_Process_Type => Element_Rec.Process_Type,
1037 X_Rbs_Element_Id => l_Rbs_Element_Id,
1038 X_Error_Msg_Data => X_Error_Msg_Data );
1039
1040 If X_Error_Msg_Data is not null Then
1041
1042 Pa_Debug.G_Stage := 'The Process_Rbs_Elements() procedure returned error. ' ||
1043 'Assign error message to the stack.';
1044 Pa_Rbs_Pub.PopulateErrorStack(
1045 P_Ref_Element_Id => Element_Rec.Rbs_Ref_Element_Id,
1046 P_Element_Id => Element_Rec.Rbs_Element_Id,
1047 P_Process_Type => Element_Rec.Process_Type,
1048 P_Error_Msg_Data => X_Error_Msg_Data);
1049
1050 Raise l_ERROR;
1051
1052 End If;
1053
1054 -- now need to update the temp recs with the element id just created
1055 Pa_Debug.G_Stage := 'Update the rbs_element_id in the pa_rbs_nodes_temp table.';
1056 Update Pa_Rbs_Nodes_Temp
1057 Set
1058 Rbs_Element_Id = l_Rbs_Element_Id
1059 Where
1060 Rbs_Ref_Element_Id = Element_Rec.Rbs_Ref_Element_Id;
1061
1062 Pa_Debug.G_Stage := 'Update the parent_element_id in the pa_rbs_nodes_temp table where needed.';
1063 Update Pa_Rbs_Nodes_Temp
1064 Set
1065 Parent_Element_Id = l_Rbs_Element_Id
1066 Where
1067 Parent_Ref_Element_Id = Element_Rec.Rbs_Ref_Element_Id;
1068
1069 -- The Fetch_Rbs_Element() procedure needs this done.
1070 Pa_Debug.G_Stage := 'Assign the rbs_element_id and status to global elements out table.';
1071 G_Rbs_Elements_Out_Tbl(Element_Rec.Record_Index).Rbs_Element_Id := l_Rbs_Element_Id;
1072 -- G_Rbs_Elements_Out_Tbl(Element_Rec.Record_Index).Return_Status := Fnd_Api.G_Ret_Sts_Success;
1073
1074 Pa_Debug.G_Stage := 'Assign the rbs_element_id and parent_id to the x_element_tbl out parameter.';
1075 X_Elements_Tbl(Element_Rec.Record_Index).Rbs_Element_Id := l_Rbs_Element_Id;
1076 X_Elements_Tbl(Element_Rec.Record_Index).Parent_Element_Id := Element_Rec.Parent_Element_Id;
1077
1078 End Loop;
1079 Close c2;
1080
1081 End Loop;
1082
1083 End If; -- l_Elements_Tbl.Count > 0
1084
1085 Pa_Debug.G_Stage := 'Check to do commit(T-True,F-False) - ' || P_Commit;
1086 If Fnd_Api.To_Boolean(Nvl(P_Commit,Fnd_Api.G_True)) Then
1087
1088 Commit;
1089
1090 End If;
1091
1092 Pa_Debug.G_Stage := 'Leaving Create_Rbs() procedure.';
1093 Pa_Debug.TrackPath('STRIP','Create_Rbs');
1094
1095 Exception
1096 When l_ERROR Then
1097 l_Msg_Count := Fnd_Msg_Pub.Count_Msg;
1098 If l_Msg_Count = 1 Then
1099 Pa_Interface_Utils_Pub.Get_Messages(
1100 P_Encoded => Fnd_Api.G_True,
1101 P_Msg_Index => 1,
1102 P_Msg_Count => l_Msg_Count,
1103 P_Msg_Data => l_Msg_Data,
1104 P_Data => l_Data,
1105 P_Msg_Index_Out => l_Msg_Index_Out);
1106 X_Error_Msg_Data := l_Data;
1107 X_Msg_Count := l_Msg_Count;
1108 Else
1109 X_Msg_Count := l_Msg_Count;
1110 End If;
1111 X_Return_Status := Fnd_Api.G_Ret_Sts_Error;
1112 G_Rbs_Ver_Out_Rec := G_Empty_Rbs_Ver_Out_Rec;
1113 G_Rbs_Elements_Out_Tbl.Delete;
1114 G_Rbs_Hdr_Out_Rec.Rbs_Header_Id := l_Rbs_Header_Id;
1115 -- G_Rbs_Hdr_Out_Rec.Return_Status := Fnd_Api.G_Ret_Sts_Error;
1116 G_Rbs_Elements_Count := 0;
1117 Rollback;
1118 When Others Then
1119 X_Return_Status := Fnd_Api.G_Ret_Sts_UnExp_Error;
1120 X_Msg_Count := 1;
1121 X_Error_Msg_Data := Pa_Debug.G_Path || '::' || Pa_Debug.G_Stage || ':' || SqlErrm;
1122 G_Rbs_Ver_Out_Rec := G_Empty_Rbs_Ver_Out_Rec;
1123 G_Rbs_Elements_Out_Tbl.Delete;
1124 G_Rbs_Hdr_Out_Rec.Rbs_Header_Id := l_Rbs_Header_Id;
1125 -- G_Rbs_Hdr_Out_Rec.Return_Status := Fnd_Api.G_Ret_Sts_UnExp_Error;
1126 G_Rbs_Elements_Count := 0;
1127 Rollback;
1128
1129 End Create_Rbs;
1130
1131 /*
1132 *********************************************************************************
1133 * API Name: Update_Rbs()
1134 * Description:
1135 * This API can be used to update the RBS header, RBS Version, or the
1136 * RBS Element/Node records or a combination of them.
1137 *
1138 * ********************************************************************************
1139 */
1140 Procedure Update_Rbs(
1141 P_Commit IN Varchar2 Default Fnd_Api.G_False,
1142 P_Init_Msg_List IN Varchar2 Default Fnd_Api.G_True,
1143 P_API_Version_Number IN Number,
1144 P_Header_Rec IN Pa_Rbs_Pub.Rbs_Header_Rec_Typ,
1145 P_Version_Rec IN Pa_Rbs_Pub.Rbs_Version_Rec_Typ,
1146 P_Elements_Tbl IN Rbs_Elements_Tbl_Typ,
1147 X_Elements_Tbl OUT NOCOPY Rbs_Elements_Tbl_Typ,
1148 X_Return_Status OUT NOCOPY Varchar2,
1149 X_Msg_Count OUT NOCOPY Number,
1150 X_Error_Msg_Data OUT NOCOPY Varchar2)
1151
1152 Is
1153
1154 i Number := Null;
1155 l_Api_Name Varchar2(30) := 'Update_Rbs';
1156 l_Resource_Source_Id Number := Null;
1157 l_Dummy Varchar2(30) := Null;
1158 l_Record_Version_Number Number := Null;
1159
1160 l_Rbs_Header_Id Number(15) := Null;
1161 l_Rbs_Element_Id Number(15) := Null;
1162 l_Msg_Count Number := 0;
1163 l_ERROR Exception;
1164
1165 l_msg_data Varchar2(2000) := Null;
1166 l_data Varchar2(2000) := Null;
1167 l_msg_index_out Number;
1168 l_count Number;
1169 l_Max_Rbs_Level Number := 0;
1170 l_process_type_index Number := 0; -- 1=D delete , 2=U update , 3=A add
1171 l_Process_Type Varchar2(1) := Null;
1172 l_Root_Count Number := 0;
1173 l_Header_Rec Rbs_Header_Rec_Typ;
1174 l_Version_Rec Rbs_Version_Rec_Typ;
1175 l_Elements_Tbl Rbs_Elements_Tbl_Typ;
1176 l_Mode Number := 1;
1177
1178 l_status_code Varchar2(30);
1179 l_validate Varchar2(1);
1180
1181 Cursor c1 Is
1182 Select
1183 Max(Rbs_Level)
1184 From
1185 Pa_Rbs_Nodes_Temp;
1186
1187 Cursor c2 (P_Rbs_Level IN Number,
1188 P_Process_Type IN Varchar2) Is
1189 Select
1190 Rbs_Version_Id,
1191 Rbs_Element_Id,
1192 Parent_Element_Id,
1193 Resource_Type_Id,
1194 Resource_Source_Id,
1195 Resource_Source_Code,
1196 Order_Number,
1197 Process_Type,
1198 Rbs_Level,
1199 Record_Version_Number,
1200 Parent_Ref_Element_Id,
1201 Rbs_Ref_Element_Id,
1202 Record_Index
1203 From
1204 Pa_Rbs_Nodes_Temp
1205 Where
1206 Rbs_Level = P_Rbs_Level
1207 And Process_Type = P_Process_Type
1208 Order By
1209 Rbs_Ref_Element_Id;
1210
1211 Element_Rec c2%RowType;
1212
1213 Cursor c3 Is
1214 Select
1215 Count(*)
1216 From
1217 Pa_Rbs_Nodes_Temp
1218 Where
1219 Rbs_Level = 1;
1220
1221 Cursor rbs_header_cursor (P_header_id In Number) Is
1222 Select
1223 tl.Name,
1224 tl.Description,
1225 b.Effective_From_Date,
1226 b.Effective_To_Date,
1227 b.record_version_number
1228 From
1229 Pa_Rbs_Headers_B b,
1230 Pa_Rbs_Headers_TL tl
1231 Where
1232 b.Rbs_Header_Id = p_header_id
1233 And b.Rbs_Header_Id = tl.Rbs_Header_Id
1234 AND tl.language = userenv('LANG');
1235
1236 l_old_header_rec rbs_header_cursor%RowType;
1237
1238 Cursor rbs_version_cursor(P_Version_Id IN Number) Is
1239 Select
1240 tl.Name,
1241 tl.Description,
1242 b.Version_Start_Date,
1243 b.Version_End_Date,
1244 b.Job_Group_Id,
1245 b.status_code,
1246 b.record_version_number
1247 From
1248 Pa_Rbs_Versions_B b,
1249 Pa_Rbs_Versions_TL tl
1250 Where
1251 b.Rbs_Version_Id = p_version_id
1252 And b.Rbs_Version_Id = tl.Rbs_Version_Id;
1253
1254 l_old_version_rec rbs_version_cursor%RowType;
1255
1256 Begin
1257
1258 Pa_Debug.G_Path := ' ';
1259
1260 Pa_Debug.G_Stage := 'Entering Update_Rbs().';
1261 Pa_Debug.TrackPath('ADD','Update_Rbs');
1262
1263 Pa_Debug.G_Stage := 'Call Compatibility API.';
1264 If Not Fnd_Api.Compatible_API_Call (
1265 Pa_Rbs_Pub.G_Api_Version_Number,
1266 P_Api_Version_Number,
1267 l_Api_Name,
1268 Pa_Rbs_Pub.G_Pkg_Name) Then
1269
1270 Raise Fnd_Api.G_Exc_Unexpected_Error;
1271
1272 End If;
1273
1274 Pa_Debug.G_Stage := 'Check if need to initialize the message stack(T-True,F-False) - ' || P_Init_Msg_List;
1275 If Fnd_Api.To_Boolean(nvl(P_Init_Msg_List,Fnd_Api.G_True)) Then
1276
1277 Pa_Debug.G_Stage := 'Initializing message stack by calling Fnd_Msg_Pub.Initialize().';
1278 Fnd_Msg_Pub.Initialize;
1279
1280 End If;
1281
1282 Pa_Debug.G_Stage := 'Initialize error handling variables.';
1283 X_Error_Msg_Data := Null;
1284 X_Return_Status := Fnd_Api.G_Ret_Sts_Success;
1285
1286
1287 /***********************************
1288 call to covert missing
1289
1290 *************************************/
1291 Pa_Rbs_Pub.Convert_Missing_Rbs_Header(
1292 P_Header_Rec => P_Header_Rec,
1293 X_Header_Rec => l_Header_Rec,
1294 P_Mode => l_Mode ,
1295 X_Error_Msg => X_Error_Msg_Data);
1296
1297 Pa_Rbs_Pub.Convert_Missing_Rbs_Elements(
1298 P_Elements_Tbl => P_Elements_Tbl,
1299 X_Elements_Tbl => l_Elements_Tbl,
1300 P_Mode => l_Mode ,
1301 X_Error_Msg => X_Error_Msg_Data);
1302
1303
1304
1305
1306
1307 /***************************
1308 Process the Header Rec
1309 ***************************/
1310
1311 -- Query the header from the DB and check if any fields are changed.
1312
1313 IF l_header_rec.rbs_header_id is not null THEN
1314 Open rbs_header_cursor(p_header_id => l_header_rec.rbs_header_id);
1315
1316 Fetch rbs_header_cursor into l_old_header_rec;
1317
1318 If rbs_header_cursor%NotFound Then
1319
1320 Pa_Debug.G_Stage := 'Rbs header Id is invalid.';
1321 Close rbs_header_cursor;
1322 Pa_Utils.Add_Message(
1323 P_App_Short_Name => 'PA',
1324 P_Msg_Name => 'PA_INVALID_RBS_HEADER_ID');
1325
1326 Raise l_ERROR;
1327
1328 Else
1329
1330 Close rbs_header_cursor;
1331
1332 End If;
1333
1334 IF (nvl(l_header_rec.name,' ') <> nvl(l_old_header_rec.name,' ') OR
1335 nvl(l_header_rec.description,' ') <> nvl(l_old_header_rec.description,' ') OR
1336 nvl(l_header_rec.effective_from_date,sysdate) <> nvl(l_old_header_rec.effective_from_date,sysdate) OR
1337 nvl(l_header_rec.Effective_To_Date,sysdate) <> nvl(l_old_header_rec.Effective_To_Date,sysdate)) THEN
1338
1339
1340 -- One of the header attributes has changed which means, the header record needs to be updated.
1341
1342 IF l_header_rec.record_version_number <> l_old_header_rec.record_version_number THEN
1343 Pa_Utils.Add_Message(
1344 P_App_Short_Name => 'PA',
1345 P_Msg_Name => 'PA_RBS_HDR_INCORRECT');
1346
1347 Raise l_ERROR;
1348 END IF;
1349
1350 Pa_Debug.G_Stage := 'Update Header Record by calling Pa_Rbs_Header_Pub.Update_Header() procedure.';
1351 Pa_Rbs_Header_Pub.Update_Header(
1352 P_Commit => Fnd_Api.G_False,
1353 P_Init_Msg_List => Fnd_Api.G_False,
1354 P_API_Version_Number => P_Api_Version_Number,
1355 P_RbsHeaderId => l_Header_Rec.Rbs_Header_Id,
1356 P_Name => l_Header_Rec.Name,
1357 P_Description => l_Header_Rec.Description,
1358 P_EffectiveFrom => l_Header_Rec.Effective_From_Date,
1359 P_EffectiveTo => l_Header_Rec.Effective_To_Date,
1360 P_RecordVersionNumber => l_Header_Rec.Record_Version_Number,
1361 P_Process_Version => Fnd_Api.G_False,
1362 X_Return_Status => X_Return_Status,
1363 X_Msg_Data => X_Error_Msg_Data,
1364 X_Msg_Count => l_Msg_Count);
1365
1366 If X_Error_Msg_Data is Not Null Then
1367
1368 Pa_Debug.G_Stage := 'The Pa_Rbs_Header_Pub.Update_Header() procedure returned error.';
1369 Raise l_ERROR;
1370
1371 Else
1372
1373 G_Rbs_Hdr_Out_Rec.Rbs_Header_Id := l_Header_Rec.Rbs_Header_Id;
1374 -- G_Rbs_Hdr_Out_Rec.Return_Status := Fnd_Api.G_Ret_Sts_Success;
1375
1376 End If;
1377
1378 End If; -- Has the header record been passed in.
1379 END IF; -- l_header_rec.rbs_header_id is not null
1380
1381 /***************************
1382 Process the Version Rec
1383 ***************************/
1384
1385 Pa_Rbs_Pub.Convert_Missing_Rbs_Version(
1386 P_Version_Rec => P_Version_Rec,
1387 X_Version_Rec => l_Version_Rec,
1388 P_Mode => l_Mode ,
1389 X_Error_Msg => X_Error_Msg_Data);
1390
1391 IF l_Version_Rec.rbs_version_id is not null THEN
1392 -- Query the version from the DB and check if any fields are changed.
1393
1394 Open rbs_version_cursor(p_version_id => l_version_rec.rbs_version_id);
1395
1396 Fetch rbs_version_cursor into l_old_version_rec;
1397
1398 If rbs_version_cursor%NotFound Then
1399
1400 Pa_Debug.G_Stage := 'Rbs header Id is invalid.';
1401 Close rbs_version_cursor;
1402 Pa_Utils.Add_Message(
1403 P_App_Short_Name => 'PA',
1404 P_Msg_Name => 'PA_INVALID_RBS_VERSION_ID');
1405
1406 Raise l_ERROR;
1407
1408 Else
1409
1410 Close rbs_version_cursor;
1411
1412 End If;
1413
1414 Pa_Debug.G_Stage := 'Check if need to update the Version Record.';
1415
1416 IF (nvl(l_Version_Rec.Name, ' ') <> nvl(l_old_version_rec.Name, ' ') OR
1417 nvl(l_Version_Rec.Description, ' ') <> nvl(l_old_version_rec.Description, ' ') OR
1418 nvl(l_Version_Rec.Version_Start_Date,sysdate) <> nvl(l_old_version_rec.Version_Start_Date,sysdate) OR
1419 nvl(l_Version_Rec.Job_Group_Id,-1) <> nvl(l_old_version_rec.Job_Group_Id,-1)) THEN
1420
1421
1422 -- Check to see if the Version's record version number matches
1423 -- the database record version number. If not, error out.
1424
1425 IF nvl(l_version_rec.record_version_number,-1) <> l_old_version_rec.record_version_number
1426 THEN
1427 Pa_Debug.G_Stage := 'Incorred Record Version number passed in';
1428 Pa_Utils.Add_Message(
1429 P_App_Short_Name => 'PA',
1430 P_Msg_Name => 'PA_RBS_VERSION_INCORRECT');
1431
1432 Raise l_ERROR;
1433 END IF;
1434
1435 IF l_old_version_rec.status_code ='FROZEN' THEN
1436 Pa_Debug.G_Stage := 'Version is frozen.Cannot update any field.';
1437 Pa_Utils.Add_Message(
1438 P_App_Short_Name => 'PA',
1439 P_Msg_Name => 'PA_RBS_VERSION_FROZEN');
1440
1441 Raise l_ERROR;
1442 END IF;
1443
1444 Pa_Debug.G_Stage := 'Update Version Record by calling Pa_Rbs_Versions_Pub.Update_Working_Version() procedure.';
1445 Pa_Rbs_Versions_Pub.Update_Working_Version(
1446 P_Commit => Fnd_Api.G_False,
1447 P_Init_Msg_List => Fnd_Api.G_False,
1448 P_API_Version_Number => P_Api_Version_Number,
1449 P_RBS_Version_Id => l_Version_Rec.Rbs_Version_Id,
1450 P_Name => l_Version_Rec.Name,
1451 P_Description => l_Version_Rec.Description,
1452 P_Version_Start_Date => l_Version_Rec.Version_Start_Date,
1453 P_Job_Group_Id => l_Version_Rec.Job_Group_Id,
1454 P_Record_Version_Number => l_Version_Rec.Record_Version_Number,
1455 P_Init_Debugging_Flag => 'N',
1456 X_Record_Version_Number => l_Record_Version_Number,
1457 X_Return_Status => X_Return_Status,
1458 X_Msg_Count => l_Msg_Count,
1459 X_Error_Msg_Data => X_Error_Msg_Data);
1460
1461 If X_Return_Status <> Fnd_Api.G_Ret_Sts_Success Then
1462
1463 Pa_Debug.G_Stage := 'The Pa_Rbs_Versions_Pub.Update_Working_Version() procedure returned status of error.';
1464 Raise l_ERROR;
1465
1466 Else
1467
1468 G_Rbs_Ver_Out_Rec.Rbs_Version_Id := l_Version_Rec.Rbs_Version_Id;
1469 -- G_Rbs_Ver_Out_Rec.Return_Status := Fnd_Api.G_Ret_Sts_Success;
1470
1471 End If;
1472
1473 End If;
1474 END IF; -- l_Version_Rec.rbs_version_id is not null
1475
1476 /*****************************
1477 Process the Elements/Nodes
1478 *****************************/
1479
1480 If l_Elements_Tbl.Count > 0 Then
1481
1482 -- Put the pl/sql table into a temp table, checking for problems before inserting.
1483 Pa_Debug.G_Stage := 'Start loop thru the element/nodes pl/sql table.';
1484 For i in l_Elements_Tbl.First .. l_Elements_Tbl.Last
1485 Loop
1486
1487 Pa_Debug.G_Stage := 'Check if missing rbs level id.';
1488 If l_Elements_Tbl(i).Rbs_Level is Null Then
1489
1490 Pa_Debug.G_Stage := 'The rbs level cannot be null. Add error message to stack.';
1491 Pa_Utils.Add_Message(
1492 P_App_Short_Name => 'PA',
1493 P_Msg_Name => 'PA_RBS_RBS_LEVEL_REQ');
1494
1495 Raise l_ERROR;
1496
1497 End If;
1498
1499 Pa_Debug.G_Stage := 'Check if we have a process type to work with.';
1500 If l_Elements_Tbl(i).Process_Type is Null or
1501 l_Elements_Tbl(i).Process_Type Not In ('A','D','U') Then
1502
1503 Pa_Debug.G_Stage := 'Process Type is null or invalid. Add error message to stack.';
1504 Pa_Utils.Add_Message(
1505 P_App_Short_Name => 'PA',
1506 P_Msg_Name => 'PA_RBS_PRC_TYPE_INVALID');
1507
1508 Raise l_ERROR;
1509
1510 End If;
1511
1512 Pa_Debug.G_Stage := 'Check that have rbs element id.';
1513 If l_Elements_Tbl(i).Rbs_Ref_Element_Id is Null And
1514 l_Elements_Tbl(i).Process_Type = 'A' Then
1515
1516 Pa_Debug.G_Stage := 'Missing reference rbs element id. Add error message to stack.';
1517 Pa_Utils.Add_Message(
1518 P_App_Short_Name => 'PA',
1519 P_Msg_Name => 'PA_RBS_REF_ELEMENT_ID_REQ');
1520
1521 Raise l_ERROR;
1522
1523 End If;
1524
1525 Pa_Debug.G_Stage := 'Check f missing rbs (reference) element id.';
1526 If l_Elements_Tbl(i).Rbs_Element_Id is Null And
1527 l_Elements_Tbl(i).Process_Type in ('U','D') Then
1528
1529 Pa_Debug.G_Stage := 'Missing rbs element id. Add error message to stack.';
1530 Pa_Utils.Add_Message(
1531 P_App_Short_Name => 'PA',
1532 P_Msg_Name => 'PA_RBS_ELEMENT_ID_REQ');
1533
1534 Raise l_ERROR;
1535
1536 End If;
1537
1538 Pa_Debug.G_Stage := 'Check if missing parent element id.';
1539 If ( l_Elements_Tbl(i).Parent_Ref_Element_Id Is Null And
1540 l_Elements_Tbl(i).Parent_Element_Id is Null ) And
1541 l_Elements_Tbl(i).Process_Type = 'A' And
1542 l_Elements_Tbl(i).Rbs_Level <> 1 Then
1543
1544 Pa_Debug.G_Stage := 'Missing parent element id. Add error message to stack.';
1545 Pa_Utils.Add_Message(
1546 P_App_Short_Name => 'PA',
1547 P_Msg_Name => 'PA_RBS_REF_PARENT_ID_REQ');
1548
1549 Raise l_ERROR;
1550
1551 End If;
1552
1553 Pa_Debug.G_Stage := 'Check if missing parent (reference) element id.';
1554 If l_Elements_Tbl(i).Parent_Element_Id is Null And
1555 l_Elements_Tbl(i).Process_Type in ('U','D') And
1556 l_Elements_Tbl(i).Rbs_Level <> 1 Then
1557
1558 Pa_Debug.G_Stage := 'Missing parent element id. Add error message to stack.';
1559 Pa_Utils.Add_Message(
1560 P_App_Short_Name => 'PA',
1561 P_Msg_Name => 'PA_RBS_PARENT_ID_REQ');
1562
1563 Raise l_ERROR;
1564
1565 End If;
1566
1567 -- Validate the RBS Version. Only elements of a working
1568 -- version can be updated.
1569
1570 IF (l_Elements_Tbl(i).Rbs_Version_Id is null OR
1571 l_Elements_Tbl(i).Rbs_Version_Id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM)
1572 THEN
1573 Pa_Debug.G_Stage := 'Version Identifier Invalid.';
1574 Pa_Utils.Add_Message(
1575 P_App_Short_Name => 'PA',
1576 P_Msg_Name => 'PA_RBS_VERSION_IS_MISSING');
1577
1578 Raise l_ERROR;
1579 ELSE
1580 BEGIN
1581 SELECT status_code
1582 INTO l_status_code
1583 FROM PA_RBS_VERSIONS_B
1584 WHERE rbs_version_id = l_Elements_Tbl(i).Rbs_Version_Id;
1585
1586 IF l_status_code = 'FROZEN' THEN
1587
1588 Pa_Debug.G_Stage := 'Version Identifier Invalid.';
1589 Pa_Utils.Add_Message(
1590 P_App_Short_Name => 'PA',
1591 P_Msg_Name => 'PA_RBS_VERSION_FROZEN');
1592
1593 Raise l_ERROR;
1594 END IF;
1595 EXCEPTION
1596 WHEN NO_DATA_FOUND THEN
1597 Pa_Debug.G_Stage := 'Version Identifier Invalid.';
1598 Pa_Utils.Add_Message(
1599 P_App_Short_Name => 'PA',
1600 P_Msg_Name => 'PA_INVALID_RBS_VERSION_ID');
1601
1602 Raise l_ERROR;
1603 END;
1604
1605 END IF;
1606
1607 --For bug 3964469.
1608 --Validate if the Element Id passed belongs to the version being updated.
1609 If l_Elements_Tbl(i).Rbs_Element_Id IS NOT NULL THEN
1610
1611 BEGIN
1612
1613 SELECT 'Y'
1614 INTO l_validate
1615 FROM pa_rbs_elements
1616 WHERE rbs_version_id = l_Elements_Tbl(i).Rbs_Version_Id
1617 AND rbs_element_id = l_Elements_Tbl(i).Rbs_Element_Id;
1618
1619 If l_validate = 'Y' Then
1620 Pa_Debug.G_Stage := 'Element Identifier in sync with Version Identifier.';
1621 END IF;
1622
1623 EXCEPTION
1624 WHEN NO_DATA_FOUND Then
1625 l_validate := 'N';
1626 Pa_Debug.G_Stage := 'Element Identifier not sync with Version Identifier.';
1627 Pa_Utils.Add_Message(
1628 P_App_Short_Name => 'PA',
1629 P_Msg_Name => 'PA_RBS_ELE_NSYNC_VER');
1630
1631 Raise l_ERROR;
1632
1633 END;
1634
1635 END IF;
1636
1637 --Also, same validation is done for the parent_element_id:
1638 If l_Elements_Tbl(i).Parent_Element_Id IS NOT NULL THEN
1639
1640 BEGIN
1641
1642 SELECT 'Y'
1643 INTO l_validate
1644 FROM pa_rbs_elements
1645 WHERE rbs_version_id = l_Elements_Tbl(i).Rbs_Version_Id
1646 AND rbs_element_id = l_Elements_Tbl(i).Parent_Element_Id;
1647
1648 If l_validate = 'Y' Then
1649 Pa_Debug.G_Stage := 'Parent Element Identifier in sync with Element id of the given Version Identifier.';
1650 END IF;
1651
1652 EXCEPTION
1653 WHEN NO_DATA_FOUND Then
1654 l_validate := 'N';
1655 Pa_Debug.G_Stage := 'Parent Element Identifier not sync with Element id of the given Version Identifier.';
1656 Pa_Utils.Add_Message(
1657 P_App_Short_Name => 'PA',
1658 P_Msg_Name => 'PA_PAR_ELE_ID_INVALID');
1659
1660 Raise l_ERROR;
1661
1662 END;
1663
1664 END IF;
1665
1666 --Validate Parent_element_id. Check whether the parent_element_id is the elements's parent_element_id
1667 IF l_Elements_Tbl(i).Rbs_Element_Id IS NOT NULL AND
1668 l_Elements_Tbl(i).Parent_Element_Id IS NOT NULL THEN
1669
1670 BEGIN
1671
1672 SELECT 'Y'
1673 INTO l_validate
1674 FROM pa_rbs_elements
1675 WHERE parent_element_id = l_Elements_Tbl(i).Parent_Element_Id
1676 AND rbs_element_id = l_Elements_Tbl(i).Rbs_Element_Id;
1677
1678 If l_validate = 'Y' Then
1679 Pa_Debug.G_Stage := 'Parent Element Identifier passed is the correct parent_element_id';
1680 END IF;
1681
1682 EXCEPTION
1683 WHEN NO_DATA_FOUND Then
1684 Pa_Debug.G_Stage := 'Parent Element Identifier passed is not the correct parent_element_id.';
1685 Pa_Utils.Add_Message(
1686 P_App_Short_Name => 'PA',
1687 P_Msg_Name => 'PA_PAR_ELE_NSYNC_ELE');
1688
1689 Raise l_ERROR;
1690
1691 END;
1692
1693 END IF;
1694 --End of Bug 3964469.
1695
1696 Pa_Debug.G_Stage := 'Insert record into pa_rbs_nodes_temp table for further processing.';
1697 Insert into Pa_Rbs_Nodes_Temp (
1698 Rbs_Version_Id,
1699 Rbs_Element_Id,
1700 Parent_Element_Id,
1701 Resource_Type_Id,
1702 Resource_Source_Id,
1703 Resource_Source_Code,
1704 Order_Number,
1705 Process_Type,
1706 Rbs_Level,
1707 Record_Version_Number,
1708 Parent_Ref_Element_Id,
1709 Rbs_Ref_Element_Id,
1710 Record_Index )
1711 Values (
1712 l_Elements_Tbl(i).Rbs_Version_Id,
1713 l_Elements_Tbl(i).Rbs_Element_Id,
1714 l_Elements_Tbl(i).Parent_Element_Id,
1715 Decode(l_Elements_Tbl(i).Rbs_Level,1,-1,l_Elements_Tbl(i).Resource_Type_Id),
1716 Decode(l_Elements_Tbl(i).Rbs_Level,
1717 1, l_Elements_Tbl(i).Rbs_Version_Id,
1718 l_Elements_Tbl(i).Resource_Source_Id),
1719 l_Elements_Tbl(i).Resource_Source_Code,
1720 l_Elements_Tbl(i).Order_Number,
1721 Decode(l_Elements_Tbl(i).Rbs_Level,1,'R',l_Elements_Tbl(i).Process_Type),
1722 l_Elements_Tbl(i).Rbs_Level,
1723 l_Elements_Tbl(i).Record_Version_Number,
1724 l_Elements_Tbl(i).Parent_Ref_Element_Id,
1725 l_Elements_Tbl(i).Rbs_Ref_Element_Id,
1726 i );
1727
1728 If l_Elements_Tbl(i).Rbs_Level = 1 Then
1729
1730 -- The Fetch_Rbs_Element() procedure needs this done.
1731 -- Rbs Level 1 is the root element/node for the structure.
1732 -- It is not processed but is created with a default structure,
1733 -- so it is not looked at further into the process elements part of the proedure.
1734 G_Rbs_Elements_Out_Tbl(i).Rbs_Element_Id := l_Elements_Tbl(i).Rbs_Element_Id;
1735 --G_Rbs_Elements_Out_Tbl(i).Return_Status := Fnd_Api.G_Ret_Sts_Success;
1736
1737 End If;
1738
1739 End Loop;
1740
1741 -- Check to see if have a root node passed in the pl/sql table. This is required
1742 -- even though they have minimul control over it values.
1743 -- Root element/node is always level one.
1744 Pa_Debug.G_Stage := 'Open c3 to get count of rbs level 1 records.';
1745 Open c3;
1746 Fetch c3 Into l_Root_Count;
1747 Close c3;
1748
1749 If l_Root_Count = 0 Then
1750
1751 Pa_Debug.G_Stage := 'No root element/node provided. Add error message to stack.';
1752 Pa_Utils.Add_Message(
1753 P_App_Short_Name => 'PA',
1754 P_Msg_Name => 'PA_RBS_NO_ROOT_ELEMENT');
1755
1756 Raise l_ERROR;
1757
1758 End If;
1759
1760 -- Check to see if provide more than a single root element. This is not allowed.
1761 If l_Root_Count > 1 Then
1762
1763 Pa_Debug.G_Stage := 'Multiple root elements/nodes provided. Add error message to stack.';
1764 Pa_Utils.Add_Message(
1765 P_App_Short_Name => 'PA',
1766 P_Msg_Name => 'PA_RBS_MULTI_ROOT_ELEMENTS');
1767
1768 Raise l_ERROR;
1769
1770 End If;
1771
1772 Pa_Debug.G_Stage := 'Open cursor c1 to get the max rbs level defined.';
1773 Open c1;
1774 Fetch c1 Into l_Max_Rbs_Level;
1775 Close c1;
1776
1777 -- This for loop is to enforce the business rule of what data is processed in what order
1778 -- We want to process elements/nodes in the following order: Delete, Update, Add
1779 Pa_Debug.G_Stage := 'For next loop to restrict by process type.';
1780 For j in 1 .. 3
1781 Loop
1782
1783 If j = 1 Then
1784
1785 l_Process_Type := 'D';
1786
1787 ElsIf j = 2 Then
1788
1789 l_Process_Type := 'U';
1790
1791 ElsIf j = 3 Then
1792
1793 l_Process_Type := 'A';
1794
1795 End If;
1796
1797 -- This loop is to enforce the business rule of processing the elements in order of rbs_level because
1798 -- we need to always have the actual parent_element_id determined before its child can be processed
1799 -- Level 1 is the root for the RBS structure and is only created and never updated or deleted
1800 Pa_Debug.G_Stage := 'For next loop to restrict by rbs_level.';
1801 For i in 2 .. l_Max_Rbs_Level
1802 Loop
1803
1804 Pa_Debug.G_Stage := 'Open c2 cursor to get next set of records to process.';
1805 Open c2( P_Rbs_Level => i,
1806 P_Process_Type => l_Process_Type);
1807
1808 -- This is the loop where the needed elements will be process based on the to parent
1809 -- loops criteria be applied to
1810 -- the cursor c2, that is the process type and the rbs_level we want to work with.
1811 Pa_Debug.G_Stage := 'Start loop to process the elements/nodes for the process type/rbs level.';
1812 Loop
1813
1814 Pa_Debug.G_Stage := 'Fetch c2 cursor record.';
1815 Fetch c2 Into Element_Rec;
1816 Exit When c2%NotFound;
1817
1818 Pa_Debug.G_Stage := 'Check if resource source id is null.';
1819 If Element_Rec.Resource_Source_Id is Null Then
1820
1821 Pa_Debug.G_Stage := 'Get Resource Source Id using source code.';
1822 Pa_Rbs_Elements_Utils.GetResSourceId(
1823 P_Resource_Type_Id => Element_Rec.Resource_Type_Id,
1824 P_Resource_Source_Code => Element_Rec.Resource_Source_Code,
1825 X_Resource_Source_Id => l_Resource_Source_Id);
1826
1827 IF l_Resource_Source_Id IS NULL THEN
1828 Pa_Utils.Add_Message(P_App_Short_Name => 'PA',
1829 P_Msg_Name => 'PA_RBS_NO_RESOURCE_SOURCE_ID');
1830
1831 Raise l_ERROR;
1832 END IF;
1833 Else
1834
1835 Pa_Debug.G_Stage := 'Assign resource source id.';
1836 l_Resource_Source_Id := Element_Rec.Resource_Source_Id;
1837
1838 End If;
1839
1840 Pa_Debug.G_Stage := 'Call Process_Rbs_Elements() procedure.';
1841 Pa_Rbs_Elements_Pvt.Process_Rbs_Element(
1842 P_RBS_Version_Id => Element_Rec.Rbs_Version_Id,
1843 P_Parent_Element_Id => Element_Rec.Parent_Element_Id,
1844 P_Element_Id => Element_Rec.Rbs_Element_Id,
1845 P_Resource_Type_Id => Element_Rec.Resource_Type_Id,
1846 P_Resource_Source_Id => l_Resource_Source_Id,
1847 P_Order_Number => Element_Rec.Order_Number,
1848 P_Process_Type => Element_Rec.Process_Type,
1849 X_Rbs_Element_Id => l_Rbs_Element_Id,
1850 X_Error_Msg_Data => X_Error_Msg_Data );
1851
1852 If X_Error_Msg_Data is not null Then
1853
1854 Pa_Debug.G_Stage := 'The Process_Rbs_Elements() procedure returned error. ' ||
1855 'Add error message to stack.';
1856 Pa_Rbs_Pub.PopulateErrorStack(
1857 P_Ref_Element_Id => Element_Rec.Rbs_Ref_Element_Id,
1858 P_Element_Id => Element_Rec.Rbs_Element_Id,
1859 P_Process_Type => Element_Rec.Process_Type,
1860 P_Error_Msg_Data => X_Error_Msg_Data);
1861
1862 Raise l_ERROR;
1863
1864 End If;
1865
1866 -- Now need to update the temp recs with the element id just created.
1867 -- This is because we have process one rbs level at a time to populate needed data
1868 -- However, these updates are only needed when adding elements/nodes.
1869 Pa_Debug.G_Stage := 'Check if process type is Add to as to do needed updates on temp table.';
1870 If l_Process_Type = 'A' Then
1871
1872 -- This should always update at least one record
1873 Pa_Debug.G_Stage := 'Update rbs_element_id based on temp rbs element id.';
1874 Update Pa_Rbs_Nodes_Temp
1875 Set
1876 Rbs_Element_Id = l_Rbs_Element_Id
1877 Where
1878 Rbs_Ref_Element_Id = Element_Rec.Rbs_Ref_Element_Id;
1879
1880 -- This may or may not update records
1881 Pa_Debug.G_Stage := 'Update parent_element_id based on temp parent id.';
1882 Update Pa_Rbs_Nodes_Temp
1883 Set
1884 Parent_Element_Id = l_Rbs_Element_Id
1885 Where
1886 Parent_Ref_Element_Id = Element_Rec.Rbs_Ref_Element_Id
1887 And Parent_Element_Id is Null;
1888
1889 End If;
1890
1891 Pa_Debug.G_Stage := 'Added parent Id to pl/sql array.';
1892
1893 -- The Fetch_Rbs_Element() procedure needs this done.
1894 Pa_Debug.G_Stage := 'Update the global elements out table with rbs_element_id and status.';
1895 If Element_Rec.Process_Type = 'A' Then
1896 G_Rbs_Elements_Out_Tbl(Element_Rec.Record_Index).Rbs_Element_Id := l_Rbs_Element_Id;
1897 Else
1898 G_Rbs_Elements_Out_Tbl(Element_Rec.Record_Index).Rbs_Element_Id := Element_Rec.Rbs_Element_Id;
1899 End If;
1900 -- G_Rbs_Elements_Out_Tbl(Element_Rec.Record_Index).Return_Status := Fnd_Api.G_Ret_Sts_Success;
1901
1902 Pa_Debug.G_Stage := 'Update the out parameter x_element_tbl with rbs_element_id and parent id.';
1903 If Element_Rec.Process_Type = 'A' Then
1904
1905 X_Elements_Tbl(Element_Rec.Record_Index).Rbs_Element_Id := l_Rbs_Element_Id;
1906
1907 Else
1908
1909 X_Elements_Tbl(Element_Rec.Record_Index).Rbs_Element_Id := Element_Rec.Rbs_Element_Id;
1910
1911 End If;
1912 X_Elements_Tbl(Element_Rec.Record_Index).Parent_Element_Id := Element_Rec.Parent_Element_Id;
1913
1914 End Loop;
1915 Close c2;
1916
1917 End Loop; -- to handle the rbs_level order properly
1918
1919 End Loop; -- to handle the process type order properly
1920
1921 End If; -- l_Elements_Tbl.Count > 0
1922
1923 Pa_Debug.G_Stage := 'Check to do commit(T-True,F-False) - ' || P_Commit;
1924 If Fnd_Api.To_Boolean(Nvl(P_Commit,Fnd_Api.G_True)) Then
1925
1926 Commit;
1927
1928 End If;
1929
1930 Pa_Debug.G_Stage := 'Leaving Update_Rbs() procedure.';
1931 Pa_Debug.TrackPath('STRIP','Update_Rbs');
1932
1933 Exception
1934 When l_ERROR Then
1935 l_Msg_Count := Fnd_Msg_Pub.Count_Msg;
1936
1937 If l_Msg_Count = 1 Then
1938
1939 Pa_Interface_Utils_Pub.Get_Messages(
1940 P_Encoded => Fnd_Api.G_True,
1941 P_Msg_Index => 1,
1942 P_Msg_Count => l_Msg_Count,
1943 P_Msg_Data => l_Msg_Data,
1944 P_Data => l_Data,
1945 P_Msg_Index_Out => l_Msg_Index_Out);
1946
1947 X_Error_Msg_Data := l_Data;
1948 X_Msg_Count := l_Msg_Count;
1949
1950 Else
1951
1952 X_Msg_Count := l_Msg_Count;
1953
1954 End If;
1955 X_Return_Status := Fnd_Api.G_Ret_Sts_Error;
1956 G_Rbs_Ver_Out_Rec := G_Empty_Rbs_Ver_Out_Rec;
1957 G_Rbs_Elements_Out_Tbl.Delete;
1958 G_Rbs_Hdr_Out_Rec.Rbs_Header_Id := l_Rbs_Header_Id;
1959 -- G_Rbs_Hdr_Out_Rec.Return_Status := Fnd_Api.G_Ret_Sts_Error;
1960 G_Rbs_Elements_Count := 0;
1961 Rollback;
1962
1963 When Others Then
1964 X_Return_Status := Fnd_Api.G_Ret_Sts_UnExp_Error;
1965 X_Msg_Count := 1;
1966 X_Error_Msg_Data := Pa_Debug.G_Path || '::' || Pa_Debug.G_Stage || ':' || SqlErrm;
1967 G_Rbs_Ver_Out_Rec := G_Empty_Rbs_Ver_Out_Rec;
1968 G_Rbs_Elements_Out_Tbl.Delete;
1969 G_Rbs_Hdr_Out_Rec.Rbs_Header_Id := l_Rbs_Header_Id;
1970 -- G_Rbs_Hdr_Out_Rec.Return_Status := Fnd_Api.G_Ret_Sts_UnExp_Error;
1971 G_Rbs_Elements_Count := 0;
1972 Rollback;
1973
1974 End Update_Rbs;
1975
1976 /*
1977 *****************************************************************************************
1978 * API Name: Load_Rbs_Header
1979 * Description:
1980 * This API allows the user to load the RBS header record data.
1981 * It is required to be executed with you want to create a new RBS.
1982 *
1983 * See package specification of the rbs header table structure to see
1984 *******************************************************************************************
1985 */
1986 Procedure Load_Rbs_Header(
1987 P_Api_Version_Number IN Number,
1988 P_Rbs_Header_Id IN Number Default PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
1989 P_Name IN Varchar2 Default PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
1990 P_Description IN Varchar2 Default PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
1991 P_Effective_From_Date IN Date Default PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE,
1992 P_Effective_To_Date IN Date Default PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE,
1993 P_Record_Version_Number IN Number Default PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
1994 X_return_status OUT NOCOPY Varchar2)
1995
1996 Is
1997
1998 l_Api_Version_Number CONSTANT Number := G_Api_Version_Number;
1999 l_Api_Name CONSTANT Varchar2(30) := 'Load_Rbs_Header';
2000
2001 BEGIN
2002
2003 X_Return_Status := Fnd_Api.G_Ret_Sts_Success;
2004
2005 -- Standard Api compatibility call
2006 If Not Fnd_Api.Compatible_API_Call ( l_Api_Version_Number ,
2007 P_Api_Version_Number ,
2008 l_Api_Name ,
2009 G_Pkg_Name ) Then
2010
2011 Raise Fnd_Api.G_Exc_UnExpected_Error;
2012
2013 End If;
2014
2015 G_Rbs_Hdr_Rec.Rbs_Header_Id := P_Rbs_Header_Id;
2016 G_Rbs_Hdr_Rec.Name := P_Name;
2017 G_Rbs_Hdr_Rec.Description := P_Description;
2018 G_Rbs_Hdr_Rec.Effective_From_Date := P_Effective_From_Date;
2019 G_Rbs_Hdr_Rec.Effective_To_Date := P_Effective_To_Date;
2020 G_Rbs_Hdr_Rec.Record_Version_Number := P_Record_Version_Number;
2021
2022 Exception
2023 When Fnd_Api.G_Exc_UnExpected_Error Then
2024 X_Return_Status := Fnd_Api.G_Ret_Sts_UnExp_Error ;
2025 Rollback;
2026 When Others Then
2027 X_Return_Status := Fnd_Api.G_Ret_Sts_UnExp_Error ;
2028
2029 If Fnd_Msg_Pub.Check_Msg_Level(Fnd_Msg_Pub.G_Msg_Lvl_UnExp_Error) Then
2030
2031 Fnd_Msg_Pub.Add_Exc_Msg(
2032 P_Pkg_Name => G_Pkg_Name,
2033 P_Procedure_Name => l_api_name);
2034
2035 END IF;
2036 Rollback;
2037 End Load_Rbs_Header;
2038
2039 /*
2040 ************************************************************************************
2041 * API Name: Load_Rbs_Version
2042 *
2043 * Description:
2044 * This API allows the user to load the RBS version record data.
2045 * It is never required to be executed.
2046 *
2047 * **********************************************************************************
2048 */
2049 Procedure Load_Rbs_Version(
2050 P_Api_Version_Number IN Number,
2051 P_Rbs_Version_Id IN Number Default PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
2052 P_Name IN Varchar2 Default PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
2053 P_Description IN Varchar2 Default PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
2054 P_Version_Start_Date IN Date Default PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE,
2055 P_Job_Group_Id IN Number Default PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
2056 P_Record_Version_Number IN Number Default PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
2057 X_Return_Status OUT NOCOPY VARCHAR2)
2058
2059 Is
2060
2061 l_Api_Version_Number CONSTANT Number := G_Api_Version_Number;
2062 l_Api_Name CONSTANT Varchar2(30) := 'Load_Rbs_Version';
2063
2064 BEGIN
2065
2066 X_Return_Status := Fnd_Api.G_Ret_Sts_Success;
2067
2068 -- Standard Api compatibility call
2069 If Not Fnd_Api.Compatible_API_Call ( l_Api_Version_Number ,
2070 P_Api_Version_Number ,
2071 l_Api_Name ,
2072 G_Pkg_Name ) Then
2073
2074 Raise Fnd_Api.G_Exc_UnExpected_Error;
2075
2076 End If;
2077
2078 G_Rbs_Ver_Rec.Rbs_Version_Id := P_Rbs_Version_Id;
2079 G_Rbs_Ver_Rec.Name := P_Name;
2080 G_Rbs_Ver_Rec.Job_Group_Id := P_Job_Group_Id;
2081 G_Rbs_Ver_Rec.Description := P_Description;
2082 G_Rbs_Ver_Rec.Version_Start_Date := P_Version_Start_Date;
2083 G_Rbs_Ver_Rec.Record_Version_Number := P_Record_Version_Number;
2084
2085 Exception
2086 When Fnd_Api.G_Exc_UnExpected_Error Then
2087 X_Return_Status := Fnd_Api.G_Ret_Sts_UnExp_Error;
2088 Rollback;
2089 When Others Then
2090 X_Return_Status := Fnd_Api.G_Ret_Sts_UnExp_Error;
2091
2092 If Fnd_Msg_Pub.Check_Msg_Level(Fnd_Msg_Pub.G_Msg_Lvl_UnExp_Error) Then
2093
2094 Fnd_Msg_Pub.Add_Exc_Msg(
2095 P_Pkg_Name => G_Pkg_Name,
2096 P_Procedure_Name => l_api_name);
2097
2098 End If;
2099 Rollback;
2100 End Load_Rbs_Version;
2101
2102 /*
2103 **********************************************************************************************
2104 * API Name: Load_Rbs_Elements
2105 * Description:
2106 * This API allows the user to load the RBS element records data.
2107 * It is never required to be executed.
2108 *************************************************************************************************
2109 */
2110 Procedure Load_Rbs_Elements(
2111 P_Api_Version_Number IN Number,
2112 P_Rbs_Version_Id IN Number Default PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
2113 P_Rbs_Element_Id IN Number Default PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
2114 P_Parent_Element_Id IN Number Default PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
2115 P_Resource_Type_Id IN Number,
2116 P_Resource_Source_Id IN Number Default PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
2117 P_Resource_Source_Code IN Varchar2 Default PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
2118 P_Order_Number IN Number Default PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
2119 P_Process_Type IN Varchar2,
2120 P_Rbs_Level IN Number,
2121 P_Record_Version_Number IN Number Default PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
2122 P_Parent_Ref_Element_Id IN Number Default PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
2123 P_Rbs_Ref_Element_Id IN Number Default PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
2124 X_Return_Status OUT NOCOPY Varchar2)
2125
2126 Is
2127
2128 l_Api_Version_Number CONSTANT Number := G_Api_Version_Number;
2129 l_Api_Name CONSTANT Varchar2(30) := 'Load_Rbs_Elements';
2130
2131 BEGIN
2132
2133 X_Return_Status := Fnd_Api.G_Ret_Sts_Success;
2134
2135 -- Standard Api compatibility call
2136 If Not Fnd_Api.Compatible_API_Call ( l_Api_Version_Number ,
2137 P_Api_Version_Number ,
2138 l_Api_Name ,
2139 G_Pkg_Name ) Then
2140
2141 Raise Fnd_Api.G_Exc_UnExpected_Error;
2142
2143 End If;
2144
2145 G_Rbs_Elements_Count := G_Rbs_Elements_Count + 1;
2146
2147 G_Rbs_Elements_Tbl(G_Rbs_Elements_Count).Rbs_Version_Id := P_Rbs_Version_Id;
2148 G_Rbs_Elements_Tbl(G_Rbs_Elements_Count).Rbs_Element_Id := P_Rbs_Element_Id;
2149 G_Rbs_Elements_Tbl(G_Rbs_Elements_Count).Parent_Element_Id := P_Parent_Element_Id;
2150 G_Rbs_Elements_Tbl(G_Rbs_Elements_Count).Resource_Type_Id := P_Resource_Type_Id;
2151 G_Rbs_Elements_Tbl(G_Rbs_Elements_Count).Resource_Source_Id := P_Resource_Source_Id;
2152 G_Rbs_Elements_Tbl(G_Rbs_Elements_Count).Resource_Source_Code := P_Resource_Source_Code;
2153 G_Rbs_Elements_Tbl(G_Rbs_Elements_Count).Order_Number := P_Order_Number;
2154 G_Rbs_Elements_Tbl(G_Rbs_Elements_Count).Process_Type := P_Process_Type;
2155 G_Rbs_Elements_Tbl(G_Rbs_Elements_Count).Rbs_Level := P_Rbs_Level;
2156 G_Rbs_Elements_Tbl(G_Rbs_Elements_Count).Parent_Ref_Element_Id := P_Parent_Ref_Element_Id;
2157 G_Rbs_Elements_Tbl(G_Rbs_Elements_Count).Rbs_Ref_Element_Id := P_Rbs_Ref_Element_Id;
2158 G_Rbs_Elements_Tbl(G_Rbs_Elements_Count).Record_Version_Number := P_Record_Version_Number;
2159
2160 Exception
2161 When Fnd_Api.G_Exc_UnExpected_Error Then
2162 X_Return_Status := Fnd_Api.G_Ret_Sts_UnExp_Error;
2163 Rollback;
2164 When Others Then
2165 X_Return_Status := Fnd_Api.G_Ret_Sts_UnExp_Error;
2166
2167 If Fnd_Msg_Pub.Check_Msg_Level(Fnd_Msg_Pub.G_Msg_Lvl_UnExp_Error) Then
2168
2169 Fnd_Msg_Pub.Add_Exc_Msg(
2170 P_Pkg_Name => G_Pkg_Name,
2171 P_Procedure_Name => l_Api_Name);
2172
2173 End If;
2174 Rollback;
2175 End Load_Rbs_Elements;
2176
2177 /*
2178 *****************************************************************************
2179 * API Name: Fetch_Rbs_Header
2180 * Public/Private: Public
2181 * Procedure/Function: Procedure
2182 * Description:
2183 * This API returns the internal identifier and status of the Rbs Header
2184 * record.
2185 *
2186 * There are 3 status that can be returned:
2187 * S - Success
2188 * E - Error; caused when fails validation
2189 * U - Unexpected Error; system error and unhandle issue like ORA errors
2190 ****************************************************************************
2191 */
2192 Procedure Fetch_Rbs_Header(
2193 P_Api_Version_Number IN Number,
2194 X_Rbs_Header_Id OUT NOCOPY Number,
2195 X_Return_Status OUT NOCOPY Varchar2)
2196
2197 Is
2198
2199 l_Api_Version_Number CONSTANT Number := G_Api_Version_Number;
2200 l_Api_Name CONSTANT Varchar2(30) := 'Fetch_Rbs_Header';
2201
2202 Begin
2203
2204 X_Return_Status := Fnd_Api.G_Ret_Sts_Success;
2205
2206 If Not Fnd_Api.Compatible_Api_Call(
2207 l_Api_Version_Number,
2208 P_Api_Version_Number,
2209 l_Api_Name,
2210 G_Pkg_Name) Then
2211
2212 Raise Fnd_Api.G_Exc_UnExpected_Error;
2213
2214 End If;
2215
2216 X_Rbs_Header_Id := G_Rbs_Hdr_Out_Rec.Rbs_Header_Id;
2217 -- X_Rbs_Hdr_Return_Status := G_Rbs_Hdr_Out_Rec.Return_Status;
2218
2219 Exception
2220 When Fnd_Api.G_Exc_UnExpected_Error Then
2221
2222 X_return_status := Fnd_Api.G_Ret_Sts_UnExp_Error ;
2223 Rollback;
2224 When Others Then
2225 X_Return_Status := Fnd_Api.G_Ret_Sts_UnExp_Error ;
2226
2227 If Fnd_Msg_Pub.Check_Msg_Level(Fnd_Msg_Pub.G_Msg_Lvl_UnExp_Error) Then
2228
2229 Fnd_Msg_Pub.Add_Exc_Msg(
2230 P_Pkg_Name => G_Pkg_Name,
2231 P_Procedure_Name => l_Api_Name);
2232
2233 End If;
2234 Rollback;
2235
2236 End Fetch_Rbs_Header;
2237
2238 /*
2239 *****************************************************************************
2240 * API Name: Fetch_Rbs_Version
2241 * Public/Private: Public
2242 * Procedure/Function: Procedure
2243 * Description:
2244 * This API returns the internal identifier and status of the Rbs version
2245 * record.
2246 *
2247 * There are 3 status that can be returned:
2248 * S - Success
2249 * E - Error; caused when fails validation
2250 * U - Unexpected Error; system error and unhandle issue like ORA errors
2251 * ***************************************************************************
2252 */
2253 Procedure Fetch_Rbs_Version(
2254 P_Api_Version_Number IN Number,
2255 X_Rbs_Version_Id OUT NOCOPY Number,
2256 X_Return_Status OUT NOCOPY Varchar2)
2257
2258 Is
2259
2260 l_Api_Version_Number CONSTANT Number := G_Api_Version_Number;
2261 l_Api_Name CONSTANT Varchar2(30) := 'Fetch_Rbs_Version';
2262
2263 Begin
2264
2265 X_Return_Status := Fnd_Api.G_Ret_Sts_Success;
2266
2267 If Not Fnd_Api.Compatible_Api_Call(
2268 l_Api_Version_Number,
2269 P_Api_Version_Number,
2270 l_Api_Name,
2271 G_Pkg_Name) Then
2272
2273 Raise Fnd_Api.G_Exc_UnExpected_Error;
2274
2275 End If;
2276
2277 X_Rbs_Version_Id := G_Rbs_Ver_Out_Rec.Rbs_Version_Id;
2278 -- X_Rbs_Ver_Return_Status := G_Rbs_Ver_Out_Rec.Return_Status;
2279
2280 Exception
2281 When Fnd_Api.G_Exc_UnExpected_Error Then
2282
2283 X_return_status := Fnd_Api.G_Ret_Sts_UnExp_Error ;
2284 Rollback;
2285 When Others Then
2286 X_Return_Status := Fnd_Api.G_Ret_Sts_UnExp_Error ;
2287
2288 If Fnd_Msg_Pub.Check_Msg_Level(Fnd_Msg_Pub.G_Msg_Lvl_UnExp_Error) Then
2289
2290 Fnd_Msg_Pub.Add_Exc_Msg(
2291 P_Pkg_Name => G_Pkg_Name,
2292 P_Procedure_Name => l_Api_Name);
2293
2294 End If;
2295 Rollback;
2296 End Fetch_Rbs_Version;
2297
2298 /*
2299 *****************************************************************************
2300 * API Name: Fetch_Rbs_Element
2301 * Public/Private: Public
2302 * Procedure/Function: Procedure
2303 * Description:
2304 * This API returns the internal identifier and status of the Rbs element/node
2305 * record. If no records were loaded using load_rbs_elements then there
2306 * will no records to fetch.
2307 *
2308 * There are 3 status that can be returned:
2309 * S - Success
2310 * E - Error; caused when fails validation
2311 * U - Unexpected Error; system error and unhandle issue like ORA errors
2312 *
2313 * The p_rbs_element_index in parameter is the order in which you called
2314 * load_rbs_elements() API. So you will need to track that when
2315 * when using the load_rbs_elements() API in your calling routine.
2316 ****************************************************************************
2317 */
2318 Procedure Fetch_Rbs_Element(
2319 P_Api_Version_Number IN Number,
2320 P_Rbs_Element_Index IN Number,
2321 X_Rbs_Element_Id OUT NOCOPY Number,
2322 X_Return_Status OUT NOCOPY Varchar2)
2323
2324 Is
2325
2326 l_Api_Version_Number CONSTANT Number := G_Api_Version_Number;
2327 l_Api_Name CONSTANT Varchar2(30) := 'Fetch_Rbs_Elements';
2328 l_Index NUMBER;
2329
2330 Begin
2331
2332 X_Return_Status := Fnd_Api.G_Ret_Sts_Success;
2333
2334 If Not Fnd_Api.Compatible_Api_Call(
2335 l_Api_Version_Number,
2336 P_Api_Version_Number,
2337 l_Api_Name,
2338 G_Pkg_Name) Then
2339
2340 Raise Fnd_Api.G_Exc_UnExpected_Error;
2341
2342 End If;
2343
2344 -- Check Line index value
2345 If P_Rbs_Element_Index Is Null Then
2346
2347 l_Index := 1;
2348
2349 Else
2350
2351 l_Index := P_Rbs_Element_Index;
2352
2353 End If;
2354
2355 -- Check whether an entry exists in the G_Rbs_Elements_Tbl or not.
2356 -- If there is no entry with that index , then do nothing
2357 If Not G_Rbs_Elements_Tbl.Exists(l_Index) Then
2358
2359 -- X_Rbs_Element_Return_Status := NULL;
2360 X_Rbs_Element_Id := NULL;
2361
2362 Else
2363
2364 X_Rbs_Element_Id := G_Rbs_Elements_Out_Tbl(l_index).Rbs_Element_Id;
2365 -- X_Rbs_Element_Return_Status := G_Rbs_Elements_Out_Tbl(l_index).Return_Status;
2366
2367 End If;
2368
2369 Exception
2370 When Fnd_Api.G_Exc_UnExpected_Error Then
2371
2372 X_Return_Status := Fnd_Api.G_Ret_Sts_UnExp_Error ;
2373 Rollback;
2374 When Others Then
2375 X_Return_Status := Fnd_Api.G_Ret_Sts_UnExp_Error ;
2376
2377 If Fnd_Msg_Pub.Check_Msg_Level(Fnd_Msg_Pub.G_Msg_Lvl_UnExp_Error) Then
2378
2379 Fnd_Msg_Pub.Add_Exc_Msg(
2380 P_Pkg_Name => G_Pkg_Name,
2381 P_Procedure_Name => l_Api_Name);
2382
2383 End If;
2384 Rollback;
2385 End Fetch_Rbs_Element;
2386
2387 /*
2388 *****************************************************************************
2389 * API Name: Exec_Create_Rbs
2390 * Public/Private: Public
2391 * Procedure/Function: Procedure
2392 * Description:
2393 * This API uses the data that was loaded via the load_rbs_header(),
2394 * load_rbs_version(), and load_rbs_elements() API's to call the
2395 * Create_Rbs() API.
2396 ****************************************************************************
2397 */
2398 Procedure Exec_Create_Rbs(
2399 P_Commit IN Varchar2 := Fnd_Api.G_False,
2400 P_Init_Msg_List IN Varchar2 := Fnd_Api.G_True,
2401 P_Api_Version_Number IN Number,
2402 X_Return_Status OUT NOCOPY Varchar2,
2403 X_Msg_Count OUT NOCOPY Number,
2404 X_Msg_Data OUT NOCOPY Varchar2)
2405
2406 Is
2407
2408 l_Api_Version_Number CONSTANT NUMBER := G_Api_Version_Number;
2409 l_Api_Name CONSTANT VARCHAR2(30) := 'Exec_Create_Rbs';
2410 l_Message_Count NUMBER;
2411
2412 l_Rbs_Header_Id Number(15) := Null;
2413 l_Rbs_Version_Id Number(15) := Null;
2414 l_Dummy_Elements_Tbl Rbs_Elements_Tbl_Typ;
2415
2416 BEGIN
2417
2418 X_Return_Status := Fnd_Api.G_Ret_Sts_Success;
2419
2420 -- Standard Api compatibility call
2421 If Not Fnd_Api.Compatible_Api_Call(
2422 l_Api_Version_Number,
2423 P_Api_Version_Number,
2424 l_Api_Name,
2425 G_Pkg_Name) Then
2426
2427 Raise Fnd_Api.G_Exc_UnExpected_Error;
2428
2429 End If;
2430
2431 Pa_Rbs_Pub.Create_Rbs(
2432 P_Commit => P_Commit,
2433 P_Init_Msg_List => P_Init_Msg_List,
2434 P_API_Version_Number => P_Api_Version_Number,
2435 P_Header_Rec => G_Rbs_Hdr_Rec,
2436 P_Version_Rec => G_Rbs_Ver_Rec,
2437 P_Elements_Tbl => G_Rbs_Elements_Tbl,
2438 X_Elements_Tbl => l_Dummy_Elements_Tbl,
2439 X_Rbs_Header_Id => l_Rbs_Header_Id,
2440 X_Rbs_Version_Id => l_Rbs_Version_Id,
2441 X_Return_Status => X_Return_Status,
2442 X_Msg_Count => X_Msg_Count,
2443 X_Error_Msg_Data => X_Msg_Data);
2444
2445 Exception
2446 When Fnd_Api.G_Exc_UnExpected_Error Then
2447 X_Return_Status := Fnd_Api.G_Ret_Sts_UnExp_Error;
2448 Rollback;
2449 When Others Then
2450 X_return_status := Fnd_Api.G_Ret_Sts_UnExp_Error;
2451
2452 If Fnd_Msg_Pub.Check_Msg_Level(Fnd_Msg_Pub.G_Msg_Lvl_UnExp_Error) Then
2453 Fnd_Msg_Pub.Add_Exc_Msg(
2454 P_Pkg_Name => G_Pkg_Name,
2455 P_Procedure_Name => l_Api_Name);
2456
2457 End If;
2458
2459 Fnd_Msg_Pub.Count_And_Get(
2460 P_Count => X_Msg_Count,
2461 P_Data => X_Msg_Data );
2462 Rollback;
2463
2464 End Exec_Create_Rbs;
2465
2466 /*
2467 ************************************************************************************
2468 * API Name: Exec_Update_Rbs
2469 * Public/Private: Public
2470 * Procedure/Function: Procedure
2471 * Description:
2472 * This API uses the data that was loaded via the load_rbs_header(),
2473 * load_rbs_version(), and load_rbs_elements() API's to call the
2474 * Update_Rbs() API.
2475 * *************************************************************************************
2476 */
2477 Procedure Exec_Update_Rbs(
2478 P_Commit IN Varchar2 := Fnd_Api.G_False,
2479 P_Init_Msg_List IN Varchar2 := Fnd_Api.G_True,
2480 P_Api_Version_Number IN Number,
2481 X_Return_Status OUT NOCOPY Varchar2,
2482 X_Msg_Count OUT NOCOPY Number,
2483 X_Msg_Data OUT NOCOPY Varchar2)
2484
2485 Is
2486
2487 l_Api_Version_Number CONSTANT NUMBER := G_Api_Version_Number;
2488 l_Api_Name CONSTANT VARCHAR2(30) := 'Exec_Update_Rbs';
2489 l_Message_Count NUMBER;
2490 l_Dummy_Elements_Tbl Rbs_Elements_Tbl_Typ;
2491
2492 BEGIN
2493
2494 X_Return_Status := Fnd_Api.G_Ret_Sts_Success;
2495
2496 -- Standard Api compatibility call
2497 If Not Fnd_Api.Compatible_Api_Call(
2498 l_Api_Version_Number,
2499 P_Api_Version_Number,
2500 l_Api_Name,
2501 G_Pkg_Name) Then
2502
2503 Raise Fnd_Api.G_Exc_UnExpected_Error;
2504
2505 End If;
2506
2507 Pa_Rbs_Pub.Update_Rbs(
2508 P_Commit => P_Commit,
2509 P_Init_Msg_List => P_Init_Msg_List,
2510 P_API_Version_Number => P_Api_Version_Number,
2511 P_Header_Rec => G_Rbs_Hdr_Rec,
2512 P_Version_Rec => G_Rbs_Ver_Rec,
2513 P_Elements_Tbl => G_Rbs_Elements_Tbl,
2514 X_Elements_Tbl => l_Dummy_Elements_Tbl,
2515 X_Return_Status => X_Return_Status,
2516 X_Msg_Count => X_Msg_Count,
2517 X_Error_Msg_Data => X_Msg_Data);
2518
2519 Exception
2520 When Fnd_Api.G_Exc_UnExpected_Error Then
2521 X_Return_Status := Fnd_Api.G_Ret_Sts_UnExp_Error;
2522
2523 When Others Then
2524 X_return_status := Fnd_Api.G_Ret_Sts_UnExp_Error;
2525
2526 If Fnd_Msg_Pub.Check_Msg_Level(Fnd_Msg_Pub.G_Msg_Lvl_UnExp_Error) Then
2527 Fnd_Msg_Pub.Add_Exc_Msg(
2528 P_Pkg_Name => G_Pkg_Name,
2529 P_Procedure_Name => l_Api_Name);
2530
2531 End If;
2532
2533 Fnd_Msg_Pub.Count_And_Get(
2534 P_Count => X_Msg_Count,
2535 P_Data => X_Msg_Data );
2536
2537 End Exec_Update_Rbs;
2538
2539 /*
2540 **************************************************************************************************
2541 * API Name: Copy_Rbs_Working_Version
2542 * Description:
2543 * This API is used to create a working version from an existing frozen version.
2544 * P_RBS_Version_Id - the frozen version id to copy from
2545 * P_Rbs_Header_Id - Header for the frozen and the working version
2546 * P_Rec_Version_Number - for the current working version
2547 * P_Rbs_Header_Name - the rbs header name of version selected to make a copy
2548 * P_Rbs_Version_Number - the rbs versions version number
2549 *****************************************************************************************************
2550 */
2551 Procedure Copy_Rbs_Working_Version(
2552 P_Commit IN Varchar2 Default Fnd_Api.G_False,
2553 P_Init_Msg_List IN Varchar2 Default Fnd_Api.G_True,
2554 P_Api_Version_Number IN Number,
2555 P_RBS_Version_Id IN Number Default PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
2556 P_Rbs_Header_Id IN Number Default PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
2557 P_Rbs_Header_Name IN Varchar2 Default PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
2558 P_Rbs_Version_Number IN Number Default PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
2559 P_Rec_Version_Number IN Number,
2560 X_Return_Status OUT NOCOPY Varchar2,
2561 X_Msg_Count OUT NOCOPY Number,
2562 X_Error_Msg_Data OUT NOCOPY Varchar2 )
2563
2564 Is
2565
2566 l_Api_Name Varchar2(30) := 'Copy_Rbs_Working_Version';
2567 l_Error Exception;
2568 l_rbs_header_id Number;
2569 l_rbs_version_id Number;
2570
2571 --Retrieves rbs_version_id provided both rbs_header_id and rbs_version_number
2572
2573 Cursor C_GetVersionId1(P_Header_Id IN Number, P_Version_Number IN Number) Is
2574 Select
2575 rbs_version_id
2576 From
2577 Pa_Rbs_Versions_b
2578 Where
2579 rbs_header_id=P_Header_Id
2580 And
2581 version_number=P_Version_Number;
2582
2583 --Retrieves rbs_version_id provided both rbs name and rbs_version_number
2584
2585 Cursor C_GetVersionId2(P_Header_Name IN Varchar2, P_Version_Number IN Number) Is
2586 Select
2587 rbs_version_id
2588 From
2589 Pa_Rbs_Versions_b ver, Pa_Rbs_Headers_tl Hdr
2590 Where
2591 hdr.name=P_Header_Name
2592 And
2593 hdr.language = userenv('LANG')
2594 And
2595 hdr.rbs_header_id=ver.rbs_header_id
2596 And
2597 ver.version_number=P_Version_Number;
2598
2599 --Retrieves rbs_header_id from rbs_version_id
2600
2601 Cursor C_GetHeaderId Is
2602 Select
2603 rbs_header_id
2604 From
2605 Pa_Rbs_Versions_b
2606 Where
2607 rbs_version_id=P_RBS_Version_Id;
2608
2609 Begin
2610
2611 Pa_Debug.G_Path := ' ';
2612
2613 Pa_Debug.G_Stage := 'Entering Copy_Rbs_Working_Version().';
2614 Pa_Debug.TrackPath('ADD','Copy_Rbs_Working_Version');
2615
2616 Pa_Debug.G_Stage := 'Call Compatibility API.';
2617 If Not Fnd_Api.Compatible_API_Call (
2618 Pa_Rbs_Pub.G_Api_Version_Number,
2619 P_Api_Version_Number,
2620 l_Api_Name,
2621 Pa_Rbs_Pub.G_Pkg_Name) Then
2622
2623 Raise Fnd_Api.G_Exc_Unexpected_Error;
2624
2625 End If;
2626
2627
2628 Pa_Debug.G_Stage := 'Check if need to initialize the message stack(T-True,F-False) - ' || P_Init_Msg_List;
2629 If Fnd_Api.To_Boolean(nvl(P_Init_Msg_List,Fnd_Api.G_True)) Then
2630
2631 Pa_Debug.G_Stage := 'Initializing message stack by calling Fnd_Msg_Pub.Initialize().';
2632 Fnd_Msg_Pub.Initialize;
2633
2634 End If;
2635
2636 Pa_Debug.G_Stage := 'Initialize error handling variables.';
2637 X_Error_Msg_Data := Null;
2638 X_Msg_Count := 0;
2639 X_Return_Status := Fnd_Api.G_Ret_Sts_Success;
2640
2641
2642 l_Rbs_Version_Id:=P_Rbs_Version_Id;
2643
2644 Pa_Debug.G_Stage := 'Checks if P_Rbs_Version_Id is null';
2645 If P_Rbs_Version_Id is null or P_Rbs_Version_Id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM Then
2646
2647 If P_Rbs_Header_Id is not null and P_Rbs_Header_Id <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM and P_Rbs_Version_Number <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM and P_Rbs_Version_Number is not null then
2648
2649 OPEN C_GetVersionId1(P_Header_Id=>P_Rbs_Header_Id,P_Version_Number=>P_Rbs_Version_Number);
2650 FETCH C_GetVersionId1 INTO l_Rbs_Version_Id;
2651 CLOSE C_GetVersionId1;
2652
2653 ElsIf P_Rbs_Header_Name is not null and P_Rbs_Header_Name <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM and P_Rbs_Version_Number <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM and P_Rbs_Version_Number is not null then
2654
2655 OPEN C_GetVersionId2(P_Header_Name=>P_Rbs_Header_Name,P_Version_Number=>P_Rbs_Version_Number);
2656 FETCH C_GetVersionId2 INTO l_Rbs_Version_Id;
2657 CLOSE C_GetVersionId2;
2658 Else
2659 Pa_Debug.G_Stage := 'Raise Error: Not able to derive rbs_version_id';
2660 X_Error_Msg_Data:='PA_RBS_VERSION_ID_NOT_PASSED_AMG';
2661 Pa_Utils.Add_Message
2662 (P_App_Short_Name => 'PA',
2663 P_Msg_Name => 'PA_RBS_VERSION_ID_NOT_PASSED_AMG');
2664 Raise l_error;
2665 End If;
2666
2667 End IF;
2668
2669 l_Rbs_Header_Id:=P_Rbs_Header_Id;
2670
2671 Pa_Debug.G_Stage := 'Checks if P_Rbs_Header_Id is null';
2672 If P_Rbs_Header_Id is null or P_Rbs_Header_Id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM Then
2673
2674 OPEN C_GetHeaderId;
2675 FETCH C_GetHeaderId Into l_Rbs_Header_Id;
2676 CLOSE C_GetHeaderId;
2677
2678 End If;
2679
2680
2681 Pa_Debug.G_Stage := 'Call Pa_Rbs_Versions_Pvt.Create_New_Working_Version() procedure.';
2682 Pa_Rbs_Versions_Pub.Create_Working_Version(
2683 P_Commit => P_Commit,
2684 P_Init_Msg_List => P_Init_Msg_List,
2685 P_Api_Version_Number => P_Api_Version_Number,
2686 P_RBS_Version_Id => l_Rbs_Version_Id,
2687 P_Rbs_Header_Id => l_Rbs_Header_Id,
2688 P_Rec_Version_Number => P_Rec_Version_Number,
2689 P_Init_Debugging_Flag => 'N',
2690 X_Return_Status => X_Return_Status,
2691 X_Msg_Count => X_Msg_Count,
2692 X_Error_Msg_Data => X_Error_Msg_Data );
2693
2694 Pa_Debug.G_Stage := 'Check if error message data is populated.';
2695 If X_Error_Msg_Data Is Not Null Then
2696
2697 Pa_Debug.G_Stage := 'Raise user defined error due to error msg data parameter being populated.';
2698 Raise l_error;
2699
2700 End If;
2701
2702 Pa_Debug.G_Stage := 'Leaving Copy_Rbs_Working_Version() procedure.';
2703 Pa_Debug.TrackPath('STRIP','Copy_Rbs_Working_Version');
2704
2705 Exception
2706 When l_Error Then
2707 X_Return_Status := 'E';
2708 X_Msg_Count := 1;
2709 Rollback;
2710 Return;
2711 When Others Then
2712 X_Return_Status := 'U';
2713 X_Msg_Count := 1;
2714 X_Error_Msg_Data := Pa_Rbs_Pub.G_Pkg_Name || ':::' || Pa_Debug.G_Path || '::' || Pa_Debug.G_Stage || ':' || SqlErrm;
2715
2716 --For bug 4061935.
2717 If Fnd_Msg_Pub.Check_Msg_Level(Fnd_Msg_Pub.G_Msg_Lvl_UnExp_Error) Then
2718 Fnd_Msg_Pub.Add_Exc_Msg(
2719 P_Pkg_Name => G_Pkg_Name,
2720 P_Procedure_Name => l_Api_Name);
2721 End If;
2722
2723 Rollback;
2724 Return;
2725
2726 End Copy_Rbs_Working_Version;
2727
2728 /*
2729 ***************************************************************************************************
2730 * API Name: Freeze_Rbs_Version
2731 * Description:
2732 * This API to freeze the current working version for the RBS and create and new
2733 * working version.
2734 ****************************************************************************************************
2735 */
2736 PROCEDURE Freeze_Rbs_Version(
2737 P_Commit IN Varchar2 Default Fnd_Api.G_False,
2738 P_Init_Msg_List IN Varchar2 Default Fnd_Api.G_True,
2739 P_API_Version_Number IN Number,
2740 P_Rbs_Version_Id IN Number Default PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
2741 P_Rbs_Header_Name IN Varchar2 Default PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
2742 P_Rbs_Header_Id IN Number Default PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
2743 P_Rbs_Version_Record_Ver_Num IN Number Default PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
2744 X_Return_Status OUT NOCOPY Varchar2,
2745 X_Msg_Count OUT NOCOPY Number,
2746 X_Error_Msg_Data OUT NOCOPY Varchar2)
2747 IS
2748
2749 l_Error Exception;
2750 l_Api_Name Varchar2(30) := 'Freeze_Rbs_Version';
2751 l_rbs_version_id Number;
2752
2753 Cursor C_GetVersionId1(P_Header_Id IN Number) Is
2754 Select
2755 Rbs_Version_Id
2756 From
2757 Pa_Rbs_Versions_b
2758 Where
2759 rbs_header_id=P_Header_Id
2760 And
2761 status_code='WORKING';
2762
2763 Cursor C_GetVersionId2(P_Header_Name IN Varchar2) Is
2764 Select
2765 Rbs_Version_Id
2766 From
2767 Pa_Rbs_Versions_b ver, Pa_Rbs_Headers_tl Hdr
2768 Where
2769 Hdr.name=P_Header_Name
2770 And
2771 hdr.language = userenv('LANG')
2772 And
2773 Hdr.rbs_header_id=Ver.rbs_header_id
2774 And
2775 Ver.status_code='WORKING';
2776
2777 BEGIN
2778
2779 Pa_Debug.G_Path := ' ';
2780
2781 Pa_Debug.G_Stage := 'Entering Freeze_Rbs_Version().';
2782 Pa_Debug.TrackPath('ADD','Freeze_Rbs_Version');
2783
2784 Pa_Debug.G_Stage := 'Call Compatibility API.';
2785 If Not Fnd_Api.Compatible_API_Call (
2786 Pa_Rbs_Pub.G_Api_Version_Number,
2787 P_Api_Version_Number,
2788 l_Api_Name,
2789 Pa_Rbs_Pub.G_Pkg_Name) Then
2790
2791 Raise Fnd_Api.G_Exc_Unexpected_Error;
2792
2793 End If;
2794
2795 --For bug 4061935.
2796 Pa_Debug.G_Stage := 'Check if need to initialize the message stack(T-True,F-False) - ' || P_Init_Msg_List;
2797 If Fnd_Api.To_Boolean(nvl(P_Init_Msg_List,Fnd_Api.G_True)) Then
2798
2799 Pa_Debug.G_Stage := 'Initializing message stack by calling Fnd_Msg_Pub.Initialize().';
2800 Fnd_Msg_Pub.Initialize;
2801
2802 End If;
2803
2804 Pa_Debug.G_Stage := 'Initialize error handling variables.';
2805 X_Error_Msg_Data := Null;
2806 X_Msg_Count := 0;
2807 X_Return_Status := Fnd_Api.G_Ret_Sts_Success;
2808
2809
2810 l_Rbs_Version_Id:=P_Rbs_Version_Id;
2811
2812 Pa_Debug.G_Stage := 'Checks if P_Rbs_Version_Id is null';
2813 If P_Rbs_Version_Id is null or P_Rbs_Version_Id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM Then
2814
2815 If P_Rbs_Header_Id is not null and P_Rbs_Header_Id <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM Then
2816
2817 OPEN C_GetVersionId1(P_Header_Id=>P_Rbs_Header_Id);
2818 FETCH C_GetVersionId1 INTO l_Rbs_Version_Id;
2819 CLOSE C_GetVersionId1;
2820
2821 ElsIf P_Rbs_Header_Name is not null and P_Rbs_Header_Name <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
2822
2823 OPEN C_GetVersionId2(P_Header_Name=>P_Rbs_Header_Name);
2824 FETCH C_GetVersionId2 INTO l_Rbs_Version_Id;
2825 CLOSE C_GetVersionId2;
2826 Else
2827 Pa_Debug.G_Stage := 'Raise Error:Not able to derive rbs_version_id';
2828 X_Error_Msg_Data := 'PA_RBS_VERSION_ID_NOT_PASSED_AMG';
2829 Pa_Utils.Add_Message
2830 (P_App_Short_Name => 'PA',
2831 P_Msg_Name => 'PA_RBS_VERSION_ID_NOT_PASSED_AMG');
2832 Raise l_error;
2833 End If;
2834
2835 End If;
2836
2837
2838 Pa_Debug.G_Stage := 'Create a copy of the version being freezed which will be the working version for this header.';
2839 Pa_Rbs_Versions_Pub.Freeze_Working_Version(
2840 P_Commit => P_Commit,
2841 P_Init_Msg_List => P_Init_Msg_List,
2842 P_Rbs_Version_Id => l_Rbs_Version_Id,
2843 P_Rbs_Version_Record_Ver_Num => P_Rbs_Version_Record_Ver_Num,
2844 P_Init_Debugging_Flag => 'N',
2845 X_Return_Status => X_Return_Status,
2846 X_Msg_Count => X_Msg_Count,
2847 X_Error_Msg_Data => X_Error_Msg_Data);
2848
2849 Pa_Debug.G_Stage := 'Check if Pa_Rbs_Versions_Pub.Create_Working_Version() procedure return error.';
2850 If X_Error_Msg_Data Is Not Null Then
2851
2852 Pa_Debug.G_Stage := 'Check if Pa_Rbs_Versions_Pub.Create_Working_Version() procedure return error.';
2853 Raise l_error;
2854
2855 End If;
2856
2857 Pa_Debug.G_Stage := 'Leaving Freeze_Rbs_Version() procedure.';
2858 Pa_Debug.TrackPath('STRIP','Freeze_Rbs_Version');
2859
2860 Exception
2861 When l_Error Then
2862 X_Return_Status := 'E';
2863 X_Msg_Count := 1;
2864 Rollback;
2865 Return;
2866 When Others Then
2867 X_Return_Status := 'U';
2868 X_Msg_Count := 1;
2869 X_Error_Msg_Data := Pa_Rbs_Pub.G_Pkg_Name || ':::' || Pa_Debug.G_Path || '::' || Pa_Debug.G_Stage || ':' || SqlErrm;
2870
2871 If Fnd_Msg_Pub.Check_Msg_Level(Fnd_Msg_Pub.G_Msg_Lvl_UnExp_Error) Then
2872 Fnd_Msg_Pub.Add_Exc_Msg(
2873 P_Pkg_Name => G_Pkg_Name,
2874 P_Procedure_Name => l_Api_Name);
2875 End If;
2876
2877 Rollback;
2878 Return;
2879
2880 END Freeze_Rbs_Version;
2881
2882 /*
2883 *******************************************************************************
2884 * API Name: Assign_Rbs_To_Project
2885 *
2886 * Description:
2887 * This API will assign the RBS to a project.
2888 * You must provide the Rbs Header Id and the Project Id as in parameters
2889 * The rest have default values. The RBS will always have a usage of Reporting.
2890 * Note: Parameter P_Rbs_Version_Id is not used in the procedure. It is
2891 * retained for the time being.
2892
2893 *******************************************************************************
2894 */
2895 Procedure Assign_Rbs_To_Project(
2896 P_Commit IN Varchar2 Default Fnd_Api.G_False,
2897 P_Init_Msg_List IN Varchar2 Default Fnd_Api.G_True,
2898 P_API_Version_Number IN Number,
2899 P_Rbs_Header_Id IN Number Default PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
2900 P_Rbs_Version_Id IN Number Default PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM, --Not used
2901 P_Project_Id IN Number Default PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
2902 P_Pm_project_Reference IN Varchar2 Default PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
2903 P_Rbs_Header_Name IN Varchar2 Default PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
2904 P_Rbs_Version_Number IN Number Default PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
2905 P_Prog_Rep_Usage_Flag IN Varchar2 Default 'N',
2906 P_Primary_Rep_Flag IN Varchar2 Default 'N',
2907 X_Return_Status OUT NOCOPY Varchar2,
2908 X_Msg_Count OUT NOCOPY Number,
2909 X_Error_Msg_Data OUT NOCOPY Varchar2)
2910
2911 Is
2912
2913 l_Api_Name Varchar2(30) := 'Assign_Rbs_To_Project';
2914 l_Error Exception;
2915 l_rbs_header_id Number;
2916 l_rbs_version_id Number;
2917 l_project_id Number;
2918
2919 Cursor C_GetVersionId1(P_Header_Id IN Number) IS
2920 Select rbs_version_id
2921 From Pa_Rbs_Versions_b
2922 Where rbs_header_id = P_Header_Id
2923 And current_reporting_flag = 'Y'; -- Added
2924 --And version_number=P_Version_Number;
2925
2926 Cursor C_GetVersionId2(P_Header_Name IN Varchar2) IS
2927 Select rbs_version_id
2928 From Pa_Rbs_Versions_b ver,
2929 Pa_Rbs_Headers_tl Hdr
2930 Where hdr.name = P_Header_Name
2931 And hdr.language = userenv('LANG')
2932 And hdr.rbs_header_id = ver.rbs_header_id
2933 And ver.current_reporting_flag = 'Y'; -- Added
2934 --And ver.version_number=P_Version_Number;
2935
2936 --Cursor C_GetHeaderId Is
2937 --Select rbs_header_id
2938 --From Pa_Rbs_Versions_b
2939 --Where rbs_version_id = P_RBS_Version_Id;
2940
2941 l_current_flag VARCHAR2(1);
2942
2943 Begin
2944
2945 Pa_Debug.G_Path := ' ';
2946
2947 Pa_Debug.G_Stage := 'Entering Assign_Rbs_To_Project().';
2948 Pa_Debug.TrackPath('ADD','Assign_Rbs_To_Project');
2949
2950 Pa_Debug.G_Stage := 'Call Compatibility API.';
2951 If Not Fnd_Api.Compatible_API_Call (
2952 Pa_Rbs_Pub.G_Api_Version_Number,
2953 P_Api_Version_Number,
2954 l_Api_Name,
2955 Pa_Rbs_Pub.G_Pkg_Name) Then
2956
2957 Raise Fnd_Api.G_Exc_Unexpected_Error;
2958
2959 End If;
2960
2961 --For bug 4061935.
2962 Pa_Debug.G_Stage := 'Check if need to initialize the message stack(T-True,F-False) - ' || P_Init_Msg_List;
2963 If Fnd_Api.To_Boolean(nvl(P_Init_Msg_List,Fnd_Api.G_True)) Then
2964
2965 Pa_Debug.G_Stage := 'Initializing message stack by calling Fnd_Msg_Pub.Initialize().';
2966 Fnd_Msg_Pub.Initialize;
2967
2968 End If;
2969
2970 Pa_Debug.G_Stage := 'Initialize error handling variables.';
2971 X_Error_Msg_Data := Null;
2972 X_Msg_Count := 0;
2973 X_Return_Status := Fnd_Api.G_Ret_Sts_Success;
2974
2975
2976 l_project_id := P_Project_Id;
2977
2978 Pa_Debug.G_Stage := 'Check if P_Project_Id is null';
2979 If P_Project_Id is null or
2980 P_Project_Id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM Then
2981
2982 Pa_Debug.G_Stage := 'Get the project id from Pa_Project_Pvt.Convert_Pm_Projref_to_id';
2983
2984 Pa_Project_Pvt.Convert_Pm_Projref_to_id(
2985 P_Pm_Project_Reference => P_Pm_Project_Reference,
2986 P_pa_project_id => P_Project_Id,
2987 P_out_project_id => l_project_id,
2988 P_Return_Status => X_Return_Status);
2989
2990 If X_Return_Status <> 'S' Then
2991 X_Error_Msg_Data := Pa_Rbs_Pub.G_Pkg_Name || ':::' || Pa_Debug.G_Path || '::' || Pa_Debug.G_Stage || ':' || SqlErrm;
2992 Raise l_error;
2993 End If;
2994
2995 End If;
2996
2997
2998 -- l_Rbs_Version_Id:=P_Rbs_Version_Id;
2999
3000 Pa_Debug.G_Stage := 'Check if P_Rbs_Version_Id is null';
3001
3002 -- If P_Rbs_Version_Id is null or P_Rbs_Version_Id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM Then
3003
3004 IF P_Rbs_Header_Id is not null AND
3005 P_Rbs_Header_Id <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM THEN
3006
3007 OPEN C_GetVersionId1(P_Header_Id => P_Rbs_Header_Id);
3008 FETCH C_GetVersionId1 INTO l_Rbs_Version_Id;
3009 CLOSE C_GetVersionId1;
3010
3011 ELSIF P_Rbs_Header_Name is not null AND
3012 P_Rbs_Header_Name <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM THEN
3013
3014 OPEN C_GetVersionId2(P_Header_Name => P_Rbs_Header_Name);
3015 FETCH C_GetVersionId2 INTO l_Rbs_Version_Id;
3016 CLOSE C_GetVersionId2;
3017
3018 ELSE
3019 Pa_Debug.G_Stage := 'Raise Error: Not able to derive rbs_version_id';
3020 X_Error_Msg_Data := 'PA_INVALID_HEADER_ID';
3021 Pa_Utils.Add_Message --For bug 4061935.
3022 (P_App_Short_Name => 'PA',
3023 P_Msg_Name => 'PA_INVALID_HEADER_ID');
3024 Raise l_error;
3025 -- End If;
3026 END IF;
3027
3028
3029 l_Rbs_Header_Id := P_Rbs_Header_Id;
3030
3031 /*
3032 Pa_Debug.G_Stage := 'Check if P_Rbs_Header_Id is null';
3033 If P_Rbs_Header_Id is null or
3034 P_Rbs_Header_Id =PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM Then
3035
3036 OPEN C_GetHeaderId;
3037 FETCH C_GetHeaderId Into l_Rbs_Header_Id;
3038 CLOSE C_GetHeaderId;
3039
3040 End If;
3041
3042 -- Add check to ensure that the rbs version ID being passed
3043 -- to API has current_reporting_flag as yes.
3044 l_current_flag := 'N';
3045 BEGIN
3046 SELECT nvl(current_reporting_flag, 'N')
3047 INTO l_current_flag
3048 FROM pa_rbs_versions_b
3049 WHERE Rbs_Version_Id = l_Rbs_Version_Id;
3050
3051 EXCEPTION WHEN NO_DATA_FOUND THEN
3052 l_current_flag := 'N';
3053 END;
3054
3055 IF l_current_flag = 'N' THEN
3056 Pa_Debug.G_Stage := 'Raise Error: RBS version is not the latest';
3057 X_Error_Msg_Data := 'PA_RBS_VERSION_NOT_CURRENT';
3058 Raise l_error;
3059 END IF;
3060 */
3061
3062 IF l_Rbs_Version_Id IS NULL THEN
3063 Pa_Debug.G_Stage := 'Raise Error: RBS version does not exist.';
3064 X_Error_Msg_Data := 'PA_AMG_VERSION_ID_NOT_PASSED';
3065 Pa_Utils.Add_Message
3066 (P_App_Short_Name => 'PA',
3067 P_Msg_Name => 'PA_AMG_VERSION_ID_NOT_PASSED');
3068 Raise l_error;
3069 END IF;
3070
3071 Pa_Debug.G_Stage := 'Call Pa_Rbs_Asgmt_Pub.Create_RBS_Assignment() procedure.';
3072 Pa_Rbs_Asgmt_Pub.Create_RBS_Assignment(
3073 P_Commit => Fnd_Api.G_False,
3074 P_Init_Msg_List => Fnd_Api.G_False,
3075 P_Rbs_Header_Id => l_Rbs_Header_Id,
3076 P_Rbs_Version_Id => l_Rbs_Version_Id,
3077 P_Project_Id => l_Project_Id,
3078 P_Prog_Rep_Usage_Flag => P_Prog_Rep_Usage_Flag,
3079 P_Primary_Rep_Flag => P_Primary_Rep_Flag,
3080 X_Return_Status => X_Return_Status,
3081 X_Msg_Count => X_Msg_Count,
3082 X_Error_Msg_Data => X_Error_Msg_Data);
3083
3084 Pa_Debug.G_Stage := 'Check if Pa_Rbs_Asgmt_Pub.Create_RBS_Assignment() procedure return error.';
3085 If X_Error_Msg_Data Is Not Null Then
3086
3087 Pa_Debug.G_Stage := 'Check if Pa_Rbs_Asgmt_Pub.Create_RBS_Assignment() procedure return error.';
3088 Raise l_error;
3089
3090 End If;
3091
3092 Pa_Debug.G_Stage := 'Leaving Assign_Rbs_To_Project() procedure.';
3093 Pa_Debug.TrackPath('STRIP','Assign_Rbs_To_Project');
3094
3095 Exception
3096 When l_Error Then
3097 X_Return_Status := 'E';
3098 X_Msg_Count := 1;
3099 Rollback;
3100 Return;
3101 When Others Then
3102 X_Return_Status := 'U';
3103 X_Msg_Count := 1;
3104 X_Error_Msg_Data := Pa_Rbs_Pub.G_Pkg_Name || ':::' || Pa_Debug.G_Path || '::' || Pa_Debug.G_Stage || ':' || SqlErrm;
3105
3106 If Fnd_Msg_Pub.Check_Msg_Level(Fnd_Msg_Pub.G_Msg_Lvl_UnExp_Error) Then
3107 Fnd_Msg_Pub.Add_Exc_Msg(
3108 P_Pkg_Name => G_Pkg_Name,
3109 P_Procedure_Name => l_Api_Name);
3110 End If;
3111
3112 Rollback;
3113 Return;
3114
3115 End Assign_Rbs_To_Project;
3116
3117 /*
3118 *****************************************************************************
3119 * API Name: PopulateErrorStack
3120 * Description:
3121 * This API is used to generate a usable message when processing the
3122 * rbs elements. If is for internal use only and should not be called
3123 * externally.
3124 ****************************************************************************
3125 */
3126 Procedure PopulateErrorStack(
3127 P_Ref_Element_Id IN Number,
3128 P_Element_Id IN Number,
3129 P_Process_Type IN Varchar,
3130 P_Error_Msg_Data IN Varchar2)
3131
3132 Is
3133
3134 l_Msg_Token_Value Varchar2(2000) := Null;
3135 l_Prefix_Value Varchar2(80) := Null;
3136 l_Outline Varchar2(240) := Null;
3137
3138 Cursor c1(P_Lookup_Code IN Varchar2) Is
3139 Select
3140 Meaning
3141 From
3142 Pa_Lookups
3143 Where
3144 Lookup_Type = 'PA_RBS_API_ERR_TOKENS'
3145 And Lookup_code = P_Lookup_Code;
3146
3147 Cursor c2(P_Id IN Number) Is
3148 Select
3149 Outline_Number
3150 From
3151 Pa_Rbs_Elements
3152 Where
3153 Rbs_Element_Id = P_Id;
3154
3155 Begin
3156
3157 Pa_Debug.G_Stage := 'Entering PopulateErrorStack() procedure.';
3158 Pa_Debug.TrackPath('ADD','PopulateErrorStack');
3159
3160 If P_Process_Type = 'A' Then
3161
3162 Open c1(P_Lookup_Code => 'REFERENCE_ELEMENT_ID');
3163 Fetch c1 Into l_Prefix_Value;
3164 Close c1;
3165
3166 l_Msg_Token_Value := l_Prefix_Value || ': ' || to_char(P_Ref_Element_Id) || ': ' ;
3167
3168 Else
3169
3170 Open c1(P_Lookup_Code => 'OUTLINE_NUMBER');
3171 Fetch c1 Into l_Prefix_Value;
3172 Close c1;
3173
3174 Open c2(P_Id => P_Element_Id);
3175 Fetch c2 Into l_Outline;
3176 Close c2;
3177
3178 l_Msg_Token_Value := l_Prefix_Value || ': ' || l_Outline || ': ' ;
3179
3180 End If;
3181
3182 Pa_Debug.G_Stage := 'Calling Pa_Utils.Add_Message() procedure.';
3183 Pa_Utils.Add_Message
3184 (P_App_Short_Name => 'PA',
3185 P_Msg_Name => P_Error_Msg_Data,
3186 P_Token1 => 'MSG_TOKEN',
3187 P_Value1 => l_Msg_Token_value);
3188
3189 Pa_Debug.G_Stage := 'Leaving PopulateErrorStack() procedure.';
3190 Pa_Debug.TrackPath('STRIP','PopulateErrorStack');
3191
3192 Exception
3193 When Others Then
3194 Raise;
3195
3196 End PopulateErrorStack;
3197
3198 End Pa_Rbs_Pub;
[Home] [Help]