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