[Home] [Help]
PACKAGE BODY: APPS.PA_RBS_ELEMENTS_PUB
Source
1 Package Body Pa_Rbs_Elements_Pub AS
2 /* $Header: PARELEPB.pls 120.0 2005/05/30 20:47:47 appldev noship $*/
3
4 /* -------------------------------------------------------------------------------
5 * Procedure: Process_RBS_Elements
6 * Function: Overall Point of entry to insert/update/delete elements/nodes
7 * This procedure is used by SS clients.
8 * ------------------------------------------------------------------------------- */
9
10 Procedure Process_Rbs_Elements (
11 P_Calling_Page IN Varchar2,
12 P_Commit IN Varchar2 Default Fnd_Api.G_False,
13 P_Init_Msg_List IN Varchar2 Default Fnd_Api.G_True,
14 P_API_Version_Number IN Number,
15 P_RBS_Version_Id IN Number,
16 P_Rbs_Version_Rec_Num IN Number,
17 P_Parent_Element_Id_Tbl IN System.Pa_Num_Tbl_Type,
18 P_Element_Id_Tbl IN System.Pa_Num_Tbl_Type,
19 P_Resource_Type_Id_Tbl IN System.Pa_Num_Tbl_Type,
20 P_Resource_Source_Id_Tbl IN System.Pa_Num_Tbl_Type,
21 P_Resource_Source_Code_Tbl IN System.Pa_Varchar2_240_Tbl_Type,
22 P_Order_Number_Tbl IN System.Pa_Num_Tbl_Type,
23 P_Process_Type_Tbl IN System.Pa_Varchar2_1_Tbl_Type,
24 X_Return_Status OUT NOCOPY Varchar2,
25 X_Msg_Count OUT NOCOPY Number,
26 X_Error_Msg_Data OUT NOCOPY Varchar2)
27
28 Is
29
30 i Number := Null;
31 l_Api_Name Varchar2(30) := 'Process_Rbs_Elements';
32 l_Outline_Number_Tbl System.Pa_Varchar2_240_Tbl_Type := Null;
33 l_Error_Msg_Data_Tbl System.Pa_Varchar2_30_Tbl_Type := Null;
34 l_Resource_Source_Id Number := Null;
35 l_Dummy Varchar2(30) := Null;
36 l_rbs_element_id NUMBER;
37
38 locked_version_rec Exception;
39
40 Cursor cLockVersionRec(P_Id IN Number) is
41 Select
42 Status_Code
43 From
44 Pa_Rbs_Versions_B
45 Where
46 Status_Code = 'WORKING'
47 And Rbs_Version_Id = P_Id
48 For Update Of Status_code NoWait;
49
50 Begin
51
52 Pa_Debug.G_Path := ' ';
53
54 Pa_Debug.G_Stage := 'Entering Process_Rbs_Elements() Pub.';
55 Pa_Debug.TrackPath('ADD','Process_Rbs_Elements Pub');
56
57 Pa_Debug.G_Stage := 'Call Compatibility API.';
58 If Not Fnd_Api.Compatible_API_Call (
59 Pa_Rbs_Elements_Pub.G_Api_Version_Number,
60 P_Api_Version_Number,
61 l_Api_Name,
62 Pa_Rbs_Elements_Pub.G_Pkg_Name) Then
63
64 Raise Fnd_Api.G_Exc_Unexpected_Error;
65
66 End If;
67
68 Pa_Debug.G_Stage := 'Check if need to initialize the message stack(T-True,F-False) - ' || P_Init_Msg_List;
69 If Fnd_Api.To_Boolean(nvl(P_Init_Msg_List,Fnd_Api.G_True)) Then
70
71 Fnd_Msg_Pub.Initialize;
72
73 End If;
74
75 Pa_Debug.G_Stage := 'Initialize error handling variables.';
76 X_Msg_Count := 0;
77 X_Error_Msg_Data := Null;
78 X_Return_Status := Fnd_Api.G_Ret_Sts_Success;
79
80 -- Lock the rbs_version record.
81 Pa_Debug.G_Stage := 'Opening cursor which locks the Rbs Version.';
82 Open cLockVersionRec(P_Id => P_RBS_Version_Id);
83 Fetch cLockVersionRec Into l_dummy;
84
85 If cLockVersionRec%NotFound Then
86 Close cLockVersionRec;
87 Raise locked_version_rec;
88 End If;
89
90 -- first time thru the loop for deleted records.
91 Pa_Debug.G_Stage := 'Beginning Loop thru to process DELETED records.';
92 For i in P_Process_Type_Tbl.First .. P_Process_Type_Tbl.Last
93 Loop
94
95 IF P_Process_Type_Tbl(i) = 'D'
96 THEN
97
98 Pa_Debug.G_Stage := 'Call Process_Rbs_Elements() Pvt - 1.';
99 -- When it is a delete, the resource source ID is not passed in
100 -- Need to get it for the error message token
101 Select Resource_Source_Id
102 Into l_Resource_Source_Id
103 From pa_rbs_elements
104 Where rbs_element_id = P_Element_Id_Tbl(i);
105
106 Pa_Rbs_Elements_Pvt.Process_Rbs_Element(
107 P_RBS_Version_Id => P_RBS_Version_Id,
108 P_Parent_Element_Id => P_Parent_Element_Id_Tbl(i),
109 P_Element_Id => P_Element_Id_Tbl(i),
110 P_Resource_Type_Id => P_Resource_Type_Id_Tbl(i),
111 P_Resource_Source_Id => l_Resource_Source_Id,
112 P_Order_Number => P_Order_Number_Tbl(i),
113 P_Process_Type => P_Process_Type_Tbl(i),
114 X_RBS_Element_id => l_rbs_element_id,
115 X_Error_Msg_Data => X_Error_Msg_Data );
116
117 If X_Error_Msg_Data is not null Then
118
119 Pa_Debug.G_Stage := 'Assign error message - 1.';
120 Pa_Rbs_Elements_Pub.PopulateErrorStack(
121 P_Calling_Page => P_Calling_Page,
122 P_Element_Id => P_Element_Id_Tbl(i),
123 P_Resource_Type_Id => P_Resource_Type_Id_Tbl(i),
124 P_Resource_Source_Id => l_Resource_Source_Id,
125 P_Error_Msg_Data => X_Error_Msg_Data);
126
127 X_Msg_Count := X_Msg_Count + 1;
128 X_Return_Status := Fnd_Api.G_Ret_Sts_Error;
129
130 End If;
131
132 END IF; -- process type is DELETE
133
134 END LOOP; -- first time thru the loop for deleted records.
135
136 -- Second run thru the loop for updated records.
137 Pa_Debug.G_Stage := 'Beginning Loop thru to process UPDATE records.';
138 For i in P_Process_Type_Tbl.First .. P_Process_Type_Tbl.Last
139 Loop
140
141 If P_Process_Type_Tbl(i) = 'U'
142 Then
143
144 If P_Resource_Source_Id_Tbl(i) is Null Then
145
146 Pa_Debug.G_Stage := 'Get Resource Source Id using code - 2.';
147 Pa_Rbs_Elements_Utils.GetResSourceId(
148 P_Resource_Type_Id => P_Resource_Type_Id_Tbl(i),
149 P_Resource_Source_Code => P_Resource_Source_Code_Tbl(i),
150 X_Resource_Source_Id => l_Resource_Source_Id);
151
152 Else
153
154 Pa_Debug.G_Stage := 'Assign resource source id - 2.';
155 l_Resource_Source_Id := P_Resource_Source_Id_Tbl(i);
156
157 End If;
158
159 Pa_Debug.G_Stage := 'Call Process_Rbs_Elements() Pvt - 2.';
160
161 Pa_Rbs_Elements_Pvt.Process_Rbs_Element(
162 P_RBS_Version_Id => P_RBS_Version_Id,
163 P_Parent_Element_Id => P_Parent_Element_Id_Tbl(i),
164 P_Element_Id => P_Element_Id_Tbl(i),
165 P_Resource_Type_Id => P_Resource_Type_Id_Tbl(i),
166 P_Resource_Source_Id => l_Resource_Source_Id,
167 P_Order_Number => P_Order_Number_Tbl(i),
168 P_Process_Type => P_Process_Type_Tbl(i),
169 X_RBS_Element_id => l_rbs_element_id,
170 X_Error_Msg_Data => X_Error_Msg_Data );
171
172 If X_Error_Msg_Data is not null Then
173
174 Pa_Debug.G_Stage := 'Assign error message - 2.';
175 Pa_Rbs_Elements_Pub.PopulateErrorStack(
176 P_Calling_Page => P_Calling_Page,
177 P_Element_Id => P_Element_Id_Tbl(i),
178 P_Resource_Type_Id => P_Resource_Type_Id_Tbl(i),
179 P_Resource_Source_Id => l_Resource_Source_Id,
180 P_Error_Msg_Data => X_Error_Msg_Data);
181
182 X_Msg_Count := X_Msg_Count + 1;
183 X_Return_Status := Fnd_Api.G_Ret_Sts_Error;
184
185 End If;
186
187 End If; -- process type is UPDATE
188
189 End Loop; -- Second run thru the loop for updated records.
190
191 -- third time thru the the loop for added records.
192 Pa_Debug.G_Stage := 'Beginning Loop thru to process ADD records.';
193 For i in P_Process_Type_Tbl.First .. P_Process_Type_Tbl.Last
194 Loop
195
196 If P_Process_Type_Tbl(i) = 'A' Then
197
198 If P_Resource_Source_Id_Tbl(i) is Null Then
199
200 Pa_Debug.G_Stage := 'Get Resource Source Id using code - 3.';
201
202 Pa_Rbs_Elements_Utils.GetResSourceId(
203 P_Resource_Type_Id => P_Resource_Type_Id_Tbl(i),
204 P_Resource_Source_Code => P_Resource_Source_Code_Tbl(i),
205 X_Resource_Source_Id => l_Resource_Source_Id);
206
207 Else
208
209 Pa_Debug.G_Stage := 'Assign resource source id - 3.';
210 l_Resource_Source_Id := P_Resource_Source_Id_Tbl(i);
211
212 End If;
213
214 Pa_Debug.G_Stage := 'Call Process_Rbs_Elements() Pvt - 3.';
215 Pa_Rbs_Elements_Pvt.Process_Rbs_Element(
216 P_RBS_Version_Id => P_RBS_Version_Id,
217 P_Parent_Element_Id => P_Parent_Element_Id_Tbl(i),
218 P_Element_Id => P_Element_Id_Tbl(i),
219 P_Resource_Type_Id => P_Resource_Type_Id_Tbl(i),
220 P_Resource_Source_Id => l_Resource_Source_Id,
221 P_Order_Number => P_Order_Number_Tbl(i),
222 P_Process_Type => P_Process_Type_Tbl(i),
223 X_RBS_Element_id => l_rbs_element_id,
224 X_Error_Msg_Data => X_Error_Msg_Data );
225
226 If X_Error_Msg_Data is not null Then
227
228 Pa_Debug.G_Stage := 'Assign error message - 3.';
229 Pa_Rbs_Elements_Pub.PopulateErrorStack(
230 P_Calling_Page => P_Calling_Page,
231 P_Element_Id => P_Element_Id_Tbl(i),
232 P_Resource_Type_Id => P_Resource_Type_Id_Tbl(i),
233 P_Resource_Source_Id => l_Resource_Source_Id,
234 P_Error_Msg_Data => X_Error_Msg_Data);
235
236 X_Msg_Count := X_Msg_Count + 1;
237 X_Return_Status := Fnd_Api.G_Ret_Sts_Error;
238 return;
239
240 End If;
241
242 End If; -- process type is ADD
243
244 End Loop; -- third time thru the the loop for added records.
245
246 -- A Few assumptions are being made here.
247 -- 1) The order number of a parent can't have changed at the same time as it's child.
248 -- 2) There will always be a parent id
249 -- 3) During the update/add loops what values exist will be stamped in because the outline number
250 -- and the order number columns are not null columns in the table.
251 -- 4) If there is no value for the order number the assigned -1
252 -- 5) If there is no value for the outline number already then assigned 'NONE'
253
254 If X_Error_Msg_Data is not null Then
255
256 Pa_Debug.G_Stage := 'Assign error message - 4.';
257 Pa_Rbs_Elements_Pub.PopulateErrorStack(
258 P_Calling_Page => P_Calling_Page,
259 P_Element_Id => P_Element_Id_Tbl(i),
260 P_Resource_Type_Id => P_Resource_Type_Id_Tbl(i),
261 P_Resource_Source_Id => l_Resource_Source_Id,
262 P_Error_Msg_Data => X_Error_Msg_Data);
263 X_Msg_Count := X_Msg_Count + 1;
264 X_Return_Status := Fnd_Api.G_Ret_Sts_UnExp_Error;
265
266 End If;
267
268 Pa_Debug.G_Stage := 'Check to do commit(T-True,F-False) - ' || P_Commit;
269 If Fnd_Api.To_Boolean(Nvl(P_Commit,Fnd_Api.G_False)) Then
270
271 Commit;
272
273 End If;
274
275 Pa_Debug.G_Stage := 'Closing cursor which locked the Rbs Version.';
276 Close cLockVersionRec;
277
278 Pa_Debug.G_Stage := 'Leaving Process_Rbs_Elements() Pub procedure.';
279 Pa_Debug.TrackPath('STRIP','Process_Rbs_Elements Pub');
280
281 Exception
282 When locked_version_rec Then
283 X_Return_Status := 'E';
284 X_Msg_Count := 1;
285 X_Error_Msg_Data := 'Unable to lock the Rbs Version to process its elements. This means that the ' ||
286 'Rbs Version has been frozen by someone else, an incorrect values has been passed in, ' ||
287 'or is currently locked by someone else.';
288 When Others Then
289 X_Return_Status := 'E';
290 X_Msg_Count := 1;
291 X_Error_Msg_Data := Pa_Debug.G_Path || '::' || Pa_Debug.G_Stage || ':' || SqlErrm;
292 Rollback;
293
294 End Process_Rbs_Elements;
295
296 /* -------------------------------------------------------------------------------
297 * Procedure: Process_RBS_Elements
298 * Function: Overall Point of entry to insert/update/delete elements/nodes
299 * This procedure is used by AMG.
300 * ------------------------------------------------------------------------------- */
301
302 Procedure Process_Rbs_Elements(
303 P_Commit IN Varchar2 Default Fnd_Api.G_False,
304 P_Init_Msg_List IN Varchar2 Default Fnd_Api.G_True,
305 P_API_Version_Number IN Number,
306 P_RBS_Version_Id IN Number,
307 P_Rbs_Version_Rec_Num IN Number,
308 P_Rbs_Elements_Tbl IN Pa_Rbs_Elements_Pub.Rbs_Elements_Tbl_Typ,
309 X_Return_Status OUT NOCOPY Varchar2,
310 X_Msg_Count OUT NOCOPY Number,
311 X_Error_Msg_Data OUT NOCOPY Varchar2 )
312
313 Is
314
315 i Number := Null;
316 l_Api_Name Varchar2(30) := 'Process_Rbs_Elements';
317 l_Parent_Element_Id_Tbl System.Pa_Num_Tbl_Type := Null;
318 l_Outline_Number_Tbl System.Pa_Varchar2_240_Tbl_Type := Null;
319 l_Error_Msg_Data_Tbl System.Pa_Varchar2_30_Tbl_Type := Null;
320 l_Resource_Source_Id Number := Null;
321 l_Dummy Varchar2(1) := Null;
322 l_rbs_element_id Number;
323
324 locked_version_rec Exception;
325
326 Cursor cLockVersionRec(P_Id IN Number) is
327 Select
328 'Y'
329 From
330 Pa_Rbs_Versions_B
331 Where
332 Status_Code = 'WORKING'
333 And Rbs_Version_Id = P_Id
334 For Update of Status_code NoWait;
335
336 Begin
337
338 Pa_Debug.G_Path := ' ';
339
340 Pa_Debug.G_Stage := 'Entering Process_Rbs_Elements() Pub.';
341 Pa_Debug.TrackPath('ADD','Process_Rbs_Elements Pub-AMG');
342
343 Pa_Debug.G_Stage := 'Call Compatibility API.';
344
345 If Not Fnd_Api.Compatible_API_Call (
346 Pa_Rbs_Elements_Pub.G_Api_Version_Number,
347 P_Api_Version_Number,
348 l_Api_Name,
349 Pa_Rbs_Elements_Pub.G_Pkg_Name)
350 THEN
351 Raise Fnd_Api.G_Exc_Unexpected_Error;
352 END IF;
353
354 IF Fnd_Api.To_Boolean(nvl(P_Init_Msg_List,Fnd_Api.G_True))
355 THEN
356 Fnd_Msg_Pub.Initialize;
357 END IF;
358
359 Pa_Debug.G_Stage := 'Initialize error handling variables.';
360 X_Msg_Count := 0;
361 X_Error_Msg_Data := Null;
362 X_Return_Status := Fnd_Api.G_Ret_Sts_Success;
363
364 -- Lock the rbs_version record.
365 Pa_Debug.G_Stage := 'Opening cursor which locks the Rbs Version.';
366 Open cLockVersionRec(P_Id => P_RBS_Version_Id);
367 Fetch cLockVersionRec Into l_dummy;
368
369 If cLockVersionRec%NotFound Then
370 Close cLockVersionRec;
371 Raise locked_version_rec;
372 End If;
373
374 -- first time thru the loop for deleted records.
375 Pa_Debug.G_Stage := 'Beginning Loop thru to process DELETED records.';
376 For i in P_Rbs_Elements_Tbl.First .. P_Rbs_Elements_Tbl.Last
377 Loop
378
379 If P_Rbs_Elements_Tbl(i).Process_Type = 'D' Then
380
381 If P_Rbs_Elements_Tbl(i).Resource_Source_Id is Null Then
382
383 Pa_Debug.G_Stage := 'Get Resource Source Id using code - 1.';
384 Pa_Rbs_Elements_Utils.GetResSourceId(
385 P_Resource_Type_Id => P_Rbs_Elements_Tbl(i).Resource_Type_Id,
386 P_Resource_Source_Code => P_Rbs_Elements_Tbl(i).Resource_Source_Code,
387 X_Resource_Source_Id => l_Resource_Source_Id);
388
389 Else
390
391 Pa_Debug.G_Stage := 'Assign resource source id 1.';
392 l_Resource_Source_Id := P_Rbs_Elements_Tbl(i).Resource_Source_Id;
393
394 End If;
395
396 Pa_Debug.G_Stage := 'Call Process_Rbs_Elements() Pvt - 1.';
397 Pa_Rbs_Elements_Pvt.Process_Rbs_Element(
398 P_RBS_Version_Id => P_RBS_Version_Id,
399 P_Parent_Element_Id => P_Rbs_Elements_Tbl(i).Parent_Element_Id,
400 P_Element_Id => P_Rbs_Elements_Tbl(i).Rbs_Element_Id,
401 P_Resource_Type_Id => P_Rbs_Elements_Tbl(i).Resource_Type_Id,
402 P_Resource_Source_Id => l_Resource_Source_Id,
403 P_Order_Number => P_Rbs_Elements_Tbl(i).Order_Number,
404 P_Process_Type => P_Rbs_Elements_Tbl(i).Process_Type,
405 X_RBS_Element_id => l_rbs_element_id,
406 X_Error_Msg_Data => X_Error_Msg_Data );
407
408 If X_Error_Msg_Data is not null Then
409
410 Pa_Debug.G_Stage := 'Assign error message - 1.';
411 Pa_Rbs_Elements_Pub.PopulateErrorStack(
412 P_Element_Id => P_Rbs_Elements_Tbl(i).Rbs_Element_Id,
413 P_Resource_Type_Id => P_Rbs_Elements_Tbl(i).Resource_Type_Id,
414 P_Resource_Source_Id => l_Resource_Source_Id,
415 P_Error_Msg_Data => X_Error_Msg_Data);
416 X_Msg_Count := X_Msg_Count + 1;
417 X_Return_Status := Fnd_Api.G_Ret_Sts_UnExp_Error;
418
419 End If;
420
421 End If; -- procedure type id DELETE
422
423 End Loop; -- first time thru the loop for deleted records.
424
425 -- second time thru the loop for updated records.
426 Pa_Debug.G_Stage := 'Beginning Loop thru to process UPDATE records.';
427 For i in P_Rbs_Elements_Tbl.First .. P_Rbs_Elements_Tbl.Last
428 Loop
429
430 If P_Rbs_Elements_Tbl(i).Process_Type = 'U' Then
431
432 If P_Rbs_Elements_Tbl(i).Resource_Source_Id is Null Then
433
434 Pa_Debug.G_Stage := 'Get Resource Source Id using code - 2.';
435 Pa_Rbs_Elements_Utils.GetResSourceId(
436 P_Resource_Type_Id => P_Rbs_Elements_Tbl(i).Resource_Type_Id,
437 P_Resource_Source_Code => P_Rbs_Elements_Tbl(i).Resource_Source_Code,
438 X_Resource_Source_Id => l_Resource_Source_Id);
439
440 Else
441
442 Pa_Debug.G_Stage := 'Assign resource source id 2.';
443 l_Resource_Source_Id := P_Rbs_Elements_Tbl(i).Resource_Source_Id;
444
445 End If;
446
447 Pa_Debug.G_Stage := 'Call Process_Rbs_Element() Pvt - 2.';
448 Pa_Rbs_Elements_Pvt.Process_Rbs_Element(
449 P_RBS_Version_Id => P_RBS_Version_Id,
450 P_Parent_Element_Id => P_Rbs_Elements_Tbl(i).Parent_Element_Id,
451 P_Element_Id => P_Rbs_Elements_Tbl(i).Rbs_Element_Id,
452 P_Resource_Type_Id => P_Rbs_Elements_Tbl(i).Resource_Type_Id,
453 P_Resource_Source_Id => l_Resource_Source_Id,
454 P_Order_Number => P_Rbs_Elements_Tbl(i).Order_Number,
455 P_Process_Type => P_Rbs_Elements_Tbl(i).Process_Type,
456 X_RBS_Element_id => l_rbs_element_id,
457 X_Error_Msg_Data => X_Error_Msg_Data );
458
459 If X_Error_Msg_Data is not null Then
460
461 Pa_Debug.G_Stage := 'Assign error message - 2.';
462 Pa_Rbs_Elements_Pub.PopulateErrorStack(
463 P_Element_Id => P_Rbs_Elements_Tbl(i).Rbs_Element_Id,
464 P_Resource_Type_Id => P_Rbs_Elements_Tbl(i).Resource_Type_Id,
465 P_Resource_Source_Id => l_Resource_Source_Id,
466 P_Error_Msg_Data => X_Error_Msg_Data);
467 X_Msg_Count := X_Msg_Count + 1;
468 X_Return_Status := Fnd_Api.G_Ret_Sts_UnExp_Error;
469
470 End If;
471
472 End If; -- process type is UPDATE
473
474 End Loop; -- second time thru the loop for updated records.
475
476 -- third time thru the loop for add records.
477 Pa_Debug.G_Stage := 'Beginning Loop thru to process ADD records.';
478 For i in P_Rbs_Elements_Tbl.First .. P_Rbs_Elements_Tbl.Last
479 Loop
480
481 If P_Rbs_Elements_Tbl(i).Process_Type = 'A' Then
482
483 If P_Rbs_Elements_Tbl(i).Resource_Source_Id is Null Then
484
485 Pa_Debug.G_Stage := 'Get Resource Source Id using code - 3.';
486 Pa_Rbs_Elements_Utils.GetResSourceId(
487 P_Resource_Type_Id => P_Rbs_Elements_Tbl(i).Resource_Type_Id,
488 P_Resource_Source_Code => P_Rbs_Elements_Tbl(i).Resource_Source_Code,
489 X_Resource_Source_Id => l_Resource_Source_Id);
490
491 Else
492
493 Pa_Debug.G_Stage := 'Assign resource source id 3.';
494 l_Resource_Source_Id := P_Rbs_Elements_Tbl(i).Resource_Source_Id;
495
496 End If;
497
498 Pa_Debug.G_Stage := 'Call Process_Rbs_Element() Pvt - 3.';
499 Pa_Rbs_Elements_Pvt.Process_Rbs_Element(
500 P_RBS_Version_Id => P_RBS_Version_Id,
501 P_Parent_Element_Id => P_Rbs_Elements_Tbl(i).Parent_Element_Id,
502 P_Element_Id => P_Rbs_Elements_Tbl(i).Rbs_Element_Id,
503 P_Resource_Type_Id => P_Rbs_Elements_Tbl(i).Resource_Type_Id,
504 P_Resource_Source_Id => l_Resource_Source_Id,
505 P_Order_Number => P_Rbs_Elements_Tbl(i).Order_Number,
506 P_Process_Type => P_Rbs_Elements_Tbl(i).Process_Type,
507 X_RBS_Element_id => l_rbs_element_id,
508 X_Error_Msg_Data => X_Error_Msg_Data );
509
510 If X_Error_Msg_Data is not null Then
511
512 Pa_Debug.G_Stage := 'Assign error message - 3.';
513 Pa_Rbs_Elements_Pub.PopulateErrorStack(
514 P_Element_Id => P_Rbs_Elements_Tbl(i).Rbs_Element_Id,
515 P_Resource_Type_Id => P_Rbs_Elements_Tbl(i).Resource_Type_Id,
516 P_Resource_Source_Id => l_Resource_Source_Id,
517 P_Error_Msg_Data => X_Error_Msg_Data);
518 X_Msg_Count := X_Msg_Count + 1;
519 X_Return_Status := Fnd_Api.G_Ret_Sts_UnExp_Error;
520
521 End If;
522
523 End If; -- process type is ADD
524
525 l_Parent_Element_Id_Tbl(i) := P_Rbs_Elements_Tbl(i).Parent_Element_Id;
526
527 End Loop; -- third time thru the loop for add records.
528
529 Pa_Debug.G_Stage := 'Check to do commit(T-True,F-False) - '|| P_Commit;
530 If Fnd_Api.To_Boolean(Nvl(P_Commit,Fnd_Api.G_False)) Then
531
532 Commit;
533
534 End If;
535
536 Pa_Debug.G_Stage := 'Closing cursor which locked the Rbs Version.';
537 Close cLockVersionRec;
538
539 Pa_Debug.G_Stage := 'Leaving Process_Rbs_Elements() Pub procedure.';
540 Pa_Debug.TrackPath('STRIP','Process_Rbs_Elements Pub-AMG');
541
542 Exception
543 When locked_version_rec Then
544 X_Return_Status := 'E';
545 X_Msg_Count := 1;
546 X_Error_Msg_Data := 'Unable to lock the Rbs Version to process its elements.';
547 When Others Then
548 X_Return_Status := 'E';
549 X_Msg_Count := 1;
550 X_Error_Msg_Data := Pa_Debug.G_Path || '::' || Pa_Debug.G_Stage || ':' || SqlErrm;
551 Rollback;
552
553 End Process_Rbs_Elements;
554
555 -- =======================================================================
556 -- Start of Comments
557 -- API Name : PopulateErrorStack
558 -- Type : Private
559 -- Pre-Reqs : None
560 -- Type : Procedure
561 -- Function : This procedure is used to build the error message.
562 -- This means determining the token value that will
563 -- will be passed in with the message. The token
564 -- value is dynamic and must consider translation.
565 -- Need traslated values for parent,child, resource type, and resource
566 --
567 -- Parameters:
568 --
569 -- IN
570 -- P_Calling_Page - VARCHAR2(10) Values: VERSION_ELEMENTS or CHILD_ELEMENTS
571 -- P_Element_Id - Number
572 -- P_Resource_Type_Id - Number
573 -- P_Resource_Source_Id - Number
574 -- P_Error_Msg_Data - VARACHAR2(30)
575 --
576 /*-------------------------------------------------------------------------*/
577
578 Procedure PopulateErrorStack(
579 P_Calling_Page IN Varchar2 Default 'VERSION_ELEMENTS',
580 P_Element_Id IN Number,
581 P_Resource_Type_Id IN Number,
582 P_Resource_Source_Id IN Number,
583 P_Error_Msg_Data IN Varchar2)
584
585 Is
586 l_Outline_Number Varchar2(240) := Null;
587 l_Outline Varchar2(80) := Null;
588 l_Resource_Type Varchar2(240) := Null;
589 l_Resource Varchar2(240) := Null;
590 l_Msg_Token_Value Varchar2(1000) := Null;
591 l_temp_res_type Varchar2(80) := Null;
592 l_temp_res Varchar2(80) := Null;
593 l_res_type_name Varchar2(240) := Null;
594 l_res_name Varchar2(240) := Null;
595
596 Cursor c1(P_Lookup_Code IN Varchar2) Is
597 Select
598 Meaning
599 From
600 Pa_Lookups
601 Where
602 Lookup_Type = 'PA_RBS_API_ERR_TOKENS'
603 And Lookup_code = P_Lookup_Code;
604
605 Cursor c2(P_Id IN Number) Is
606 Select
607 Outline_Number
608 From
609 Pa_Rbs_Elements
610 Where
611 Rbs_Element_Id = P_Id;
612
613 Cursor c3(P_Id IN Number) Is
614 Select
615 Name
616 From
617 Pa_Res_Types_TL
618 Where
619 Res_Type_Id = P_Id
620 And Language = UserEnv('LANG');
621
622 Begin
623
624 Pa_Debug.G_Stage := 'Entering PopulateErrorStack() procedure.';
625 Pa_Debug.TrackPath('ADD','PopulateErrorStack');
626
627 Pa_Debug.G_Stage := 'Get translated meaning for Resource Type.';
628 Open c1('RESOURCE_TYPE');
629 Fetch c1 Into l_Temp_Res_Type;
630 Close c1;
631
632 Pa_Debug.G_Stage := 'Get translated meaning for Resource.';
633 Open c1('RESOURCE');
634 Fetch c1 Into l_Temp_Res;
635 Close c1;
636
637 -- Get the Resource Type Name
638 Open c3(P_Resource_Type_Id);
639 Fetch c3 Into l_Res_Type_Name;
640 Close c3;
641
642 --hr_utility.trace_on(null, 'RMDEL');
643 --hr_utility.trace('START');
644 --hr_utility.trace('P_Resource_Source_Id IS : ' || P_Resource_Source_Id);
645 If P_Resource_Source_Id <> -1 Then
646
647 l_res_name := Pa_Rbs_Utils.Get_Element_Name(
648 P_Resource_Source_Id => P_Resource_Source_Id,
649 P_Resource_Type_Code => Pa_Rbs_Elements_Utils.GetResTypeCode(P_Resource_Type_Id));
650 --hr_utility.trace('In If l_res_name IS : ' || l_res_name);
651
652 Else
653
654 -- Get the Resource Type Name
655 Open c1('ANY_USED_RESOURCE');
656 Fetch c1 Into l_Res_Name;
657 Close c1;
658
659 End If;
660
661 If P_Calling_Page = 'VERSION_ELEMENTS' Then
662
663 Pa_Debug.G_Stage := 'Get translated meaning for Parent.';
664 Open c1('OUTLINE_NUMBER');
665 Fetch c1 Into l_Outline;
666 Close c1;
667
668 Pa_Debug.G_Stage := 'Get element outline number.';
669 Open c2(P_Element_Id);
670 Fetch c2 Into l_Outline_Number;
671 Close c2;
672
673 Pa_Debug.G_Stage := 'Building token for Version Elements format.';
674 -- Format for messages:
675 -- Outline: <outline number>: Message Text
676 -- Build string with translated values for outline
677
678 l_Msg_Token_Value := l_Outline || ': ' || l_Outline_Number || ': ';
679
680 Else
681
682 Pa_Debug.G_Stage := 'Building token for Child Elements format.';
683 -- Format for messages:
684 -- Resource Type: <RT> Resource : <res>: Message Text
685 -- Build string with translated values for resource type, resource
686 l_Msg_Token_Value := l_temp_res_type || ': ' || l_res_type_name || ' ' || l_temp_res || ': ' ||
687 l_res_name || ': ';
688
689 End If;
690
691 Pa_Debug.G_Stage := 'Calling Pa_Utils.Add_Message() procedure.';
692 Pa_Utils.Add_Message
693 (P_App_Short_Name => 'PA',
694 P_Msg_Name => P_Error_Msg_Data,
695 P_Token1 => 'MSG_TOKEN',
696 P_Value1 => l_Msg_Token_value);
697
698 Pa_Debug.G_Stage := 'Leaving PopulateErrorStack() procedure.';
699 Pa_Debug.TrackPath('STRIP','PopulateErrorStack');
700
701 Exception
702 When Others Then
703 Raise;
704
705 End PopulateErrorStack;
706
707 End Pa_Rbs_Elements_Pub;