1 PACKAGE BODY Pa_Plan_Res_List_Pub AS
2 /* $Header: PARESLPB.pls 120.4 2012/01/25 04:23:13 djambhek ship $*/
3
4
5 /*******************************************************************************************
6 * API Name: Convert_Missing_List_In_Rec
7 * Public/Private : Private
8 * Procedure/Function : Procedure
9 * Description:
10 * This procedure converts the input values to null if its a G_MISS_VALUE
11 * and selects the value from the database if the value being input is
12 * null, so that there wouldn't be any change to the data when null is passed
13 * as input.
14 * The conversions are done for the Plan_Res_List_IN_Rec.
15 * Attributes :
16 * INPUT VALUES :
17 * P_Plan_res_list_Rec : The record which hold's the resource list details.
18 * This contains the input record.
19 * P_Mode : The mode in which this procedure is called.
20 * It can be called in either Update or create mode
21 * 1 means update. 0 means create.
22 * OUTPUT VALUES :
23 *
24 * X_Plan_res_list_Rec : The record which hold's the resource list details with the
25 * changed values to the fields of the record.
26 * X_Error_Msg : The parameter will hold a message if there is an
27 * error in this API.
28 * There can be two modes in which this procedure can be called
29 * Update and create
30 * P_Mode = 1 means update
31 * P_Mode = 0 means create
32 * In Update
33 * if Update then Resource list id can be present.
34 * if Resource lsit id is not present then resource list name would be present .
35 * You can retreive the Resource list id from the resource list name.
36 * And then convert all GMISS to the database values.
37 *
38 * In create mode the Resource list name would be present
39 * Convert all missing values to null.
40 *
41 *
42 *******************************************************************************************/
43 PROCEDURE Convert_Missing_List_IN_Rec
44 (P_Plan_res_list_Rec IN Plan_Res_List_IN_Rec,
45 X_Plan_res_list_Rec OUT NOCOPY Plan_res_list_IN_Rec, -- 4537865
46 P_Mode IN NUMBER)
47 IS
48
49 Cursor C_Res_List_Details(P_Res_List_Id IN Number) IS
50 Select *
51 From Pa_Resource_Lists_All_Bg
52 Where resource_list_id=P_Res_List_Id;
53
54 Rec_Details C_Res_List_Details%RowType;
55
56 l_resource_list_id NUMBER;
57 l_resource_list_name Varchar2(1000);
58
59 BEGIN
60
61 X_Plan_res_list_Rec :=P_Plan_res_list_Rec;
62
63 l_resource_list_id:=P_Plan_res_list_Rec.P_Resource_list_id;
64 l_resource_list_name := P_Plan_res_list_Rec.P_resource_list_Name;
65
66 IF P_Mode = 1 Then
67 IF l_resource_list_id =PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM Then
68 l_resource_list_id := Null;
69 End If;
70
71 IF l_resource_list_id is Null Then
72 -- Get the Resource list id from the Resource list name if the name is not defaulted to null.
73 IF l_resource_list_name is not null and l_resource_list_name <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
74 SELECT resource_list_id
75 INTO l_resource_list_id
76 FROM pa_resource_lists_all_bg
77 WHERE name=l_resource_list_name;
78 END IF;
79 END IF;
80
81 IF l_resource_list_id <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM and
82 l_resource_list_id is not null Then
83
84 X_Plan_res_list_Rec.p_resource_list_ID := l_resource_list_id;
85
86 OPEN C_Res_List_Details(P_Res_List_Id => l_resource_list_id);
87 If C_Res_List_Details%NOTFOUND Then
88 CLOSE C_Res_List_Details;
89 Else
90 FETCH C_Res_List_Details INTO Rec_Details;
91 CLOSE C_Res_List_Details;
92
93 If P_Plan_res_list_Rec.p_resource_list_name = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
94 X_Plan_res_list_Rec.p_resource_list_name := Rec_Details.name;
95 End If;
96
97 If P_Plan_res_list_Rec.P_description = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
98 X_Plan_res_list_Rec.P_description := Rec_Details.description;
99 End If;
100
101
102 If P_Plan_res_list_Rec.P_Start_date = PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE Then
103 X_Plan_res_list_Rec.P_Start_date := Rec_Details.start_date_active;
104 End If;
105
106 If P_Plan_res_list_Rec.P_End_date = PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE Then
107 X_Plan_res_list_Rec.P_End_date := Rec_Details.end_date_active;
108 End If;
109
110 If P_Plan_res_list_Rec.P_Job_Group_Id= PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM Then
111 X_Plan_res_list_Rec.P_Job_Group_Id := Rec_Details.job_group_id;
112 End If;
113
114 If P_Plan_res_list_Rec.P_Job_Group_Name= PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
115 X_Plan_res_list_Rec.P_Job_Group_name:= Null;
116 End If;
117
118 If P_Plan_res_list_Rec.P_Use_For_Wp_Flag= PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
119 X_Plan_res_list_Rec.P_Use_For_Wp_Flag:= Rec_Details.Use_For_Wp_Flag;
120 End If;
121
122 If P_Plan_res_list_Rec.P_Control_Flag= PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
123 X_Plan_res_list_Rec.P_Control_Flag := Rec_Details.control_flag;
124 End If;
125
126 If P_Plan_res_list_Rec.P_Record_Version_Number= PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM Then
127 X_Plan_res_list_Rec.P_Record_Version_Number:= Rec_Details.record_version_number; --For bug 4101579
128 End If;
129
130 End If;
131
132 End If;
133
134 End If;
135
136 IF P_Mode=0 Then
137
138 If P_Plan_res_list_Rec.p_resource_list_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM Then
139 X_Plan_res_list_Rec.p_resource_list_id := Null;
140 End If;
141
142
143 If P_Plan_res_list_Rec.p_resource_list_name = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
144 X_Plan_res_list_Rec.p_resource_list_name := null;
145 End If;
146
147 If
148 P_Plan_res_list_Rec.P_description = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
149 X_Plan_res_list_Rec.P_description := Null;
150 End If;
151
152
153 If
154 P_Plan_res_list_Rec.P_Start_date = PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE Then
155 X_Plan_res_list_Rec.P_Start_date := sysdate;
156 End If;
157
158 If
159 P_Plan_res_list_Rec.P_End_date = PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE Then
160 X_Plan_res_list_Rec.P_End_date := Null;
161 End If;
162
163 If
164 P_Plan_res_list_Rec.P_Job_Group_Id= PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM Then
165 X_Plan_res_list_Rec.P_Job_Group_Id := Null;
166 End If;
167
168
169 If
170 P_Plan_res_list_Rec.P_Job_Group_Name= PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
171 X_Plan_res_list_Rec.P_Job_Group_name:= Null;
172 End If;
173
174 If
175 P_Plan_res_list_Rec.P_Use_For_Wp_Flag= PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
176 X_Plan_res_list_Rec.P_Use_For_Wp_Flag:= Null;
177 End If;
178
179 If
180 P_Plan_res_list_Rec.P_Control_Flag= PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
181 X_Plan_res_list_Rec.P_Control_Flag := Null;
182 End If;
183
184 If
185 P_Plan_res_list_Rec.P_Record_Version_Number= PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM Then
186 X_Plan_res_list_Rec.P_Record_Version_Number:= Null;
187 End If;
188
189 End If;
190 -- 4537865 : Havent included EXCEPTION block per mail from Ranjana
191
192 End Convert_Missing_List_IN_Rec;
193
194
195 /*******************************************************************************************
196 * API Name: Convert_Missing_Format_In_Rec
197 * Public/Private : Private
198 * Procedure/Function : Procedure
199 * Description:
200 * This procedure converts the input values to null if its a G_MISS_VALUE
201 * The conversions are done for the Plan_RL_Format_In_Tbl.
202 * Attributes :
203 * INPUT VALUES :
204 * P_Plan_RL_Format_Tbl : The record which hold's the resource list format details.
205 * This contains the format identifier.
206 * OUTPUT VALUES :
207 *
208 * X_Plan_RL_Format_Tbl : The record which hold's the resource list identifiers
209 * of the newly created resource formats.
210 * In Update
211 * This API can be called in both update and create mode. In both the cases
212 * the resource format identifier is checked against GMiss value. If it is Gmiss value
213 * the then identifier is converted into null.
214 *
215 *
216 *******************************************************************************************/
217
218 PROCEDURE Convert_Missing_Format_IN_Rec
219 (P_Plan_RL_Format_Tbl IN Plan_RL_Format_In_Tbl,
220 X_Plan_RL_Format_Tbl OUT NOCOPY Plan_RL_Format_In_Tbl) -- 4537865
221 IS
222
223 l_resource_format_id NUMBER;
224
225 BEGIN
226
227 X_Plan_RL_Format_Tbl:= P_Plan_RL_Format_Tbl;
228
229
230 IF P_Plan_RL_Format_Tbl.count >0 then
231
232 For i in P_Plan_RL_Format_Tbl.First .. P_Plan_RL_Format_Tbl.Last
233 Loop
234 l_resource_format_id := P_Plan_RL_Format_Tbl(i).P_Res_Format_Id;
235
236 If l_resource_format_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM Then
237
238 X_Plan_RL_Format_Tbl(i).P_Res_Format_Id:=Null;
239
240 End If;
241
242 End Loop;
243
244 End If;
245 -- 4537865 : Havent included EXCEPTION block per mail from Ranjana
246 End Convert_Missing_Format_IN_Rec;
247
248
249 /*******************************************************************************************
250 * API Name: Convert_Missing_Members_In_Rec
251 * Public/Private : Private
252 * Procedure/Function : Procedure
253 * Description:
254 * This procedure converts the input values to null if its a G_MISS_VALUE
255 * and selects the value from the database if the value being input is
256 * null, so that there wouldn't be any change to the data when null is passed
257 * as input.
258 * The conversions are done for the Planning_Resource_In_Tbl.
259 * Attributes :
260 * INPUT VALUES :
261 * P_Plan_res_list_Rec : The record which hold's the resource list
262 * members details.
263 * This contains the input record.
264 * P_Mode : The mode in which this procedure is called.
265 * It can be called in either Update or create mode
266 * 1 means update. 0 means create.
267 * OUTPUT VALUES :
268 *
269 * X_Plan_res_list_Rec : The record which hold's the resource list members
270 * details with the
271 * changed values to the fields of the record.
272 * There can be two modes in which this procedure can be called
273 * Update and create
274 * P_Mode = 1 means update
275 * P_Mode = 0 means create
276 * In Update
277 * if Update then Resource list member id can be present.
278 * Convert all GMISS to the database values.
279 *
280 * In create mode,
281 * Convert all missing values to null.
282 *
283 *
284 *******************************************************************************************/
285 PROCEDURE Convert_Missing_Member_IN_Rec
286 (P_planning_resource_in_tbl IN Planning_Resource_In_Tbl,
287 P_Plan_res_list_Rec IN Plan_Res_List_IN_Rec,
288 X_planning_resource_in_tbl OUT NOCOPY Planning_Resource_In_Tbl, -- 4537865
289 P_Mode IN Number)
290 IS
291
292 Cursor C_Member_Details(P_Member_Id IN Number) Is
293 Select *
294 From Pa_resource_list_members
295 Where
296 resource_list_member_id=P_Member_Id;
297
298 member_rec_details C_Member_Details%RowType;
299
300 l_member_id NUMBER;
301 l_object_id NUMBER;
302 l_object_type VARCHAR2(30);
303
304 BEGIN
305
306 X_planning_resource_in_tbl:= P_planning_resource_in_tbl;
307
308
309 IF P_Mode = 1 Then
310
311 IF P_planning_resource_in_tbl.count >0 then
312
313 For i in P_planning_resource_in_tbl.First .. P_planning_resource_in_tbl.Last
314 Loop
315
316 l_member_id := P_planning_resource_in_tbl(i).p_resource_list_member_id;
317 -- try to derive the member id if its null or gmiss from the alias
318 --added for bug 3947158
319 IF l_member_id IN (PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,Null) Then
320
321 IF ( P_planning_resource_in_tbl(i).P_resource_alias IS NOT Null)
322 and ( P_planning_resource_in_tbl(i).P_resource_alias <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR) THEN
323
324 -- For bug 4103909
325 IF P_Plan_res_list_Rec.p_control_flag = 'Y' THEN
326
327 BEGIN
328 SELECT resource_list_member_id
329 INTO l_member_id
330 FROM pa_resource_list_members
331 WHERE alias = p_planning_resource_in_tbl(i).P_resource_alias
332 and resource_list_id = P_Plan_res_list_Rec.p_resource_list_id;
333 EXCEPTION
334 WHEN NO_DATA_FOUND THEN
335 l_member_id :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM ;
336 END;
337
338 ELSE
339
340 BEGIN
341 IF p_planning_resource_in_tbl(i).p_project_id is null THEN
342 l_object_type := 'RESOURCE_LIST';
343 l_object_id := P_Plan_res_list_Rec.p_resource_list_id;
344 ELSE
345 l_object_type := 'PROJECT';
346 l_object_id := p_planning_resource_in_tbl(i).p_project_id;
347 END IF;
348
349 SELECT resource_list_member_id
350 INTO l_member_id
351 FROM PA_RESOURCE_LIST_MEMBERS
352 WHERE resource_list_id = P_Plan_res_list_Rec.p_resource_list_id
353 AND ALIAS = p_planning_resource_in_tbl(i).p_resource_alias
354 AND object_type = l_object_type
355 AND object_id = l_object_id;
356
357 EXCEPTION
358 WHEN NO_DATA_FOUND THEN
359 l_member_id :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM ;
360 END;
361 --End of bug 4103909
362
363 END IF;
364
365 X_planning_resource_in_tbl(i).p_resource_list_member_id:= l_member_id;
366
367 END IF;
368
369 END IF;
370 --For bug 3815348.
371
372 If l_member_id IN (PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,Null) Then
373
374 X_planning_resource_in_tbl(i).p_resource_list_member_id:=Null;
375
376
377 If P_planning_resource_in_tbl(i).P_resource_alias = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
378 X_planning_resource_in_tbl(i).P_resource_alias := Null;
379 End If;
380
381 If P_planning_resource_in_tbl(i).p_person_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM Then
382 X_planning_resource_in_tbl(i).p_person_id := Null;
383 End If;
384
385 If P_planning_resource_in_tbl(i).p_person_name = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
386 X_planning_resource_in_tbl(i).p_person_name := Null;
387 End If;
388
389 If P_planning_resource_in_tbl(i).p_job_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM Then
390 X_planning_resource_in_tbl(i).p_job_id := Null;
391 End If;
392
393 If P_planning_resource_in_tbl(i).p_job_name = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
394 X_planning_resource_in_tbl(i).p_job_name := Null;
395 End If;
396
397 If P_planning_resource_in_tbl(i).p_organization_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM Then
398 X_planning_resource_in_tbl(i).p_organization_id := Null;
399 End If;
400
401 If P_planning_resource_in_tbl(i).p_organization_name = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
402 X_planning_resource_in_tbl(i).p_organization_name := null;
403 End If;
404
405 If P_planning_resource_in_tbl(i).p_vendor_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM Then
406 X_planning_resource_in_tbl(i).p_vendor_id := Null;
407 End If;
408
409 If P_planning_resource_in_tbl(i).p_vendor_name = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
410 X_planning_resource_in_tbl(i).p_vendor_name := null;
411 End If;
412
413 If P_planning_resource_in_tbl(i).p_fin_category_name = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
414 X_planning_resource_in_tbl(i).p_fin_category_name := Null;
415 End If;
416
417 If P_planning_resource_in_tbl(i).p_non_labor_resource = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
418 X_planning_resource_in_tbl(i).p_non_labor_resource:= Null;
419 End If;
420
421 If P_planning_resource_in_tbl(i).p_project_role_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM Then
422 X_planning_resource_in_tbl(i).p_project_role_id:= Null;
423 End If;
424
425 If P_planning_resource_in_tbl(i).p_project_role_name = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
426 X_planning_resource_in_tbl(i).p_project_role_name:=Null;
427 End If;
428
429 If P_planning_resource_in_tbl(i).p_resource_class_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM Then
430 X_planning_resource_in_tbl(i).p_resource_class_id:=Null;
431 End If;
432
433 If P_planning_resource_in_tbl(i).p_resource_class_code = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
434 X_planning_resource_in_tbl(i).p_resource_class_code :=Null;
435 End If;
436
437 If P_planning_resource_in_tbl(i).p_res_format_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM Then
438 X_planning_resource_in_tbl(i).p_res_format_id := Null;
439 End If;
440
441 If P_planning_resource_in_tbl(i).p_spread_curve_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM Then
442 X_planning_resource_in_tbl(i).p_spread_curve_id:= Null;
443 End If;
444
445 If P_planning_resource_in_tbl(i).p_etc_method_code = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
446 X_planning_resource_in_tbl(i).p_etc_method_code := Null;
447 End If;
448
449 If P_planning_resource_in_tbl(i).p_mfc_cost_type_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM Then
450 X_planning_resource_in_tbl(i).p_mfc_cost_type_id := Null;
451 End If;
452
453 If P_planning_resource_in_tbl(i).p_fc_res_type_code = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
454 X_planning_resource_in_tbl(i).p_fc_res_type_code := Null;
455 End If;
456
457 If P_planning_resource_in_tbl(i).p_inventory_item_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM Then
458 X_planning_resource_in_tbl(i).p_inventory_item_id := Null;
459 End If;
460
461 If P_planning_resource_in_tbl(i).p_inventory_item_name = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
462 X_planning_resource_in_tbl(i).p_inventory_item_name := Null;
463 End If;
464
465 If P_planning_resource_in_tbl(i).p_item_category_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM Then
466 X_planning_resource_in_tbl(i).p_item_category_id := Null;
467 End If;
468
469 If P_planning_resource_in_tbl(i).p_item_category_name = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
470 X_planning_resource_in_tbl(i).p_item_category_name := Null;
471 End If;
472
473 If P_planning_resource_in_tbl(i).p_attribute_category = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
474 X_planning_resource_in_tbl(i).p_attribute_category := Null;
475 End If;
476
477 If P_planning_resource_in_tbl(i).p_attribute1 = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
478 X_planning_resource_in_tbl(i).p_attribute1 := Null;
479 End If;
480
481 If P_planning_resource_in_tbl(i).p_attribute2 = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
482 X_planning_resource_in_tbl(i).p_attribute2 := Null;
483 End If;
484
485 If P_planning_resource_in_tbl(i).p_attribute3 = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
486 X_planning_resource_in_tbl(i).p_attribute3 := Null;
487 End If;
488
489 If P_planning_resource_in_tbl(i).p_attribute4 = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
490 X_planning_resource_in_tbl(i).p_attribute4 := Null;
491 End If;
492
493 If P_planning_resource_in_tbl(i).p_attribute5 = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
494 X_planning_resource_in_tbl(i).p_attribute5 := Null;
495 End If;
496
497 If P_planning_resource_in_tbl(i).p_attribute6 = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
498 X_planning_resource_in_tbl(i).p_attribute6 := Null;
499 End If;
500
501 If P_planning_resource_in_tbl(i).p_attribute7 = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
502 X_planning_resource_in_tbl(i).p_attribute7 := Null;
503 End If;
504
505 If P_planning_resource_in_tbl(i).p_attribute8 = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
506 X_planning_resource_in_tbl(i).p_attribute8 := Null;
507 End If;
508
509 If P_planning_resource_in_tbl(i).p_attribute9 = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
510 X_planning_resource_in_tbl(i).p_attribute9 := Null;
511 End If;
512
513 If P_planning_resource_in_tbl(i).p_attribute10 = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
514 X_planning_resource_in_tbl(i).p_attribute10:= Null;
515 End If;
516
517 If P_planning_resource_in_tbl(i).p_attribute11 = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
518 X_planning_resource_in_tbl(i).p_attribute11:= Null;
519 End If;
520
521 If P_planning_resource_in_tbl(i).p_attribute12= PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
522 X_planning_resource_in_tbl(i).p_attribute12:= Null;
523 End If;
524
525 If P_planning_resource_in_tbl(i).p_attribute13= PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
526 X_planning_resource_in_tbl(i).p_attribute13:= Null;
527 End If;
528
529 If P_planning_resource_in_tbl(i).p_attribute14 = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
530 X_planning_resource_in_tbl(i).p_attribute14 := Null;
531 End If;
532
533 If P_planning_resource_in_tbl(i).p_attribute15 = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
534 X_planning_resource_in_tbl(i).p_attribute15 := Null;
535 End If;
536
537 If P_planning_resource_in_tbl(i).p_attribute16= PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
538 X_planning_resource_in_tbl(i).p_attribute16:= Null;
539 End If;
540
541 If P_planning_resource_in_tbl(i).p_attribute17= PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
542 X_planning_resource_in_tbl(i).p_attribute17:= null;
543 End If;
544
545 If P_planning_resource_in_tbl(i).p_attribute18= PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
546 X_planning_resource_in_tbl(i).p_attribute18:= Null;
547 End If;
548
549 If P_planning_resource_in_tbl(i).p_attribute19= PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
550 X_planning_resource_in_tbl(i).p_attribute19:= Null;
551 End If;
552
553 If P_planning_resource_in_tbl(i).p_attribute20= PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
554 X_planning_resource_in_tbl(i).p_attribute20:= Null;
555 End If;
556
557 If P_planning_resource_in_tbl(i).p_attribute21= PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
558 X_planning_resource_in_tbl(i).p_attribute21:= Null;
559 End If;
560
561 If P_planning_resource_in_tbl(i).p_attribute22= PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
562 X_planning_resource_in_tbl(i).p_attribute22:= Null;
563 End If;
564
565 If P_planning_resource_in_tbl(i).p_attribute23= PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
566 X_planning_resource_in_tbl(i).p_attribute23:= null;
567 End If;
568
569 If P_planning_resource_in_tbl(i).p_attribute24= PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
570 X_planning_resource_in_tbl(i).p_attribute24:= Null;
571 End If;
572
573 If P_planning_resource_in_tbl(i).p_attribute25= PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
574 X_planning_resource_in_tbl(i).p_attribute25:= Null;
575 End If;
576
577 If P_planning_resource_in_tbl(i).p_attribute26= PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
578 X_planning_resource_in_tbl(i).p_attribute26:= Null;
579 End If;
580
581 If P_planning_resource_in_tbl(i).p_attribute27= PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
582 X_planning_resource_in_tbl(i).p_attribute27:= Null;
583 End If;
584
585 If P_planning_resource_in_tbl(i).p_attribute28= PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
586 X_planning_resource_in_tbl(i).p_attribute28 := Null;
587 End If;
588
589 If P_planning_resource_in_tbl(i).p_attribute29= PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
590 X_planning_resource_in_tbl(i).p_attribute29:= Null;
591 End If;
592
593 If P_planning_resource_in_tbl(i).p_attribute30= PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
594 X_planning_resource_in_tbl(i).p_attribute30:= Null;
595 End If;
596
597 If P_planning_resource_in_tbl(i).p_person_type_code = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
598 X_planning_resource_in_tbl(i).p_person_type_code := Null;
599 End If;
600
601 If P_planning_resource_in_tbl(i).p_bom_resource_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM Then
602 X_planning_resource_in_tbl(i).p_bom_resource_id := Null;
603 End If;
604
605 If P_planning_resource_in_tbl(i).p_bom_resource_name = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
606 X_planning_resource_in_tbl(i).p_bom_resource_name := Null;
607 End If;
608
609 If P_planning_resource_in_tbl(i).p_team_role = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
610 X_planning_resource_in_tbl(i).p_team_role := Null;
611 End If;
612
613 If P_planning_resource_in_tbl(i).p_incur_by_res_code = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
614 X_planning_resource_in_tbl(i).p_incur_by_res_code := Null;
615 End If;
616
617 If P_planning_resource_in_tbl(i).p_incur_by_res_type = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
618 X_planning_resource_in_tbl(i).p_incur_by_res_type := Null;
619 End If;
620
621 If P_planning_resource_in_tbl(i).p_record_version_number = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM Then
622 X_planning_resource_in_tbl(i).p_record_version_number := Null;
623 End If;
624
625 If P_planning_resource_in_tbl(i).p_project_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM Then
626 X_planning_resource_in_tbl(i).p_project_id := Null;
627 End If;
628
629 If P_planning_resource_in_tbl(i).p_enabled_flag = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
630 X_planning_resource_in_tbl(i).p_enabled_flag := Null;
631 End If;
632
633 End IF; --End of bug 3815348
634
635
636 If l_member_id is NOT Null and l_member_id <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM Then
637
638 OPEN C_Member_Details(P_Member_Id => l_member_id);
639 If C_Member_Details%NOTFOUND Then
640 CLOSE C_Member_Details;
641 Else
642 FETCH C_Member_Details INTO Member_Rec_Details;
643 CLOSE C_Member_Details;
644
645 If P_planning_resource_in_tbl(i).p_resource_alias = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
646 X_planning_resource_in_tbl(i).p_resource_alias := Member_Rec_Details.alias;
647 End If;
648
649 If P_planning_resource_in_tbl(i).p_person_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM Then
650 X_planning_resource_in_tbl(i).p_person_id := Member_Rec_Details.person_id;
651 End If;
652
653 If P_planning_resource_in_tbl(i).p_person_name = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
654 X_planning_resource_in_tbl(i).p_person_name := Null;
655 End If;
656
657 If P_planning_resource_in_tbl(i).p_job_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM Then
658 X_planning_resource_in_tbl(i).p_job_id := Member_Rec_Details.job_id;
659 End If;
660
661 If P_planning_resource_in_tbl(i).p_job_name = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
662 X_planning_resource_in_tbl(i).p_job_name := Null;
663 End If;
664
665 If P_planning_resource_in_tbl(i).p_organization_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM Then
666 X_planning_resource_in_tbl(i).p_organization_id := Member_Rec_Details.organization_id;
667 End If;
668
669 If P_planning_resource_in_tbl(i).p_organization_name = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
670 X_planning_resource_in_tbl(i).p_organization_name := null;
671 End If;
672
673 If P_planning_resource_in_tbl(i).p_vendor_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM Then
674 X_planning_resource_in_tbl(i).p_vendor_id :=Member_Rec_Details.vendor_id;
675 End If;
676
677 If P_planning_resource_in_tbl(i).p_vendor_name = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
678 X_planning_resource_in_tbl(i).p_vendor_name := null;
679 End If;
680
681 If P_planning_resource_in_tbl(i).p_fin_category_name = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
682 X_planning_resource_in_tbl(i).p_fin_category_name := Null;
683 End If;
684
685 If P_planning_resource_in_tbl(i).p_non_labor_resource = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
686 X_planning_resource_in_tbl(i).p_non_labor_resource:= Member_Rec_Details.non_labor_resource;
687 End If;
688
689 If P_planning_resource_in_tbl(i).p_project_role_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM Then
690 X_planning_resource_in_tbl(i).p_project_role_id:= Member_Rec_Details.project_role_id;
691 End If;
692
693 If P_planning_resource_in_tbl(i).p_project_role_name = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
694 X_planning_resource_in_tbl(i).p_project_role_name:=Null;
695 End If;
696
697 If P_planning_resource_in_tbl(i).p_resource_class_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM Then
698 X_planning_resource_in_tbl(i).p_resource_class_id:=Member_Rec_Details.resource_class_id;
699 End If;
700
701 If P_planning_resource_in_tbl(i).p_resource_class_code = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
702 X_planning_resource_in_tbl(i).p_resource_class_code :=Member_Rec_Details.resource_class_code;
703 End If;
704
705 If P_planning_resource_in_tbl(i).p_res_format_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM Then
706 X_planning_resource_in_tbl(i).p_res_format_id := Member_Rec_Details.res_format_id;
707 End If;
708
709 If P_planning_resource_in_tbl(i).p_spread_curve_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM Then
710 X_planning_resource_in_tbl(i).p_spread_curve_id:= Member_Rec_Details.spread_curve_id;
711 End If;
712
713 If P_planning_resource_in_tbl(i).p_etc_method_code = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
714 X_planning_resource_in_tbl(i).p_etc_method_code := Member_Rec_Details.etc_method_code;
715 End If;
716
717 If P_planning_resource_in_tbl(i).p_mfc_cost_type_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM Then
718 X_planning_resource_in_tbl(i).p_mfc_cost_type_id := Member_Rec_Details.mfc_cost_type_id;
719 End If;
720
721 If P_planning_resource_in_tbl(i).p_fc_res_type_code = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
722 X_planning_resource_in_tbl(i).p_fc_res_type_code := Member_Rec_Details.fc_res_type_code;
723 End If;
724
725 If P_planning_resource_in_tbl(i).p_inventory_item_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM Then
726 X_planning_resource_in_tbl(i).p_inventory_item_id := Member_Rec_Details.inventory_item_id;
727 End If;
728
729 If P_planning_resource_in_tbl(i).p_inventory_item_name = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
730 X_planning_resource_in_tbl(i).p_inventory_item_name := Null;
731 End If;
732
733 If P_planning_resource_in_tbl(i).p_item_category_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM Then
734 X_planning_resource_in_tbl(i).p_item_category_id := Member_Rec_Details.item_category_id;
735 End If;
736
737 If P_planning_resource_in_tbl(i).p_item_category_name = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
738 X_planning_resource_in_tbl(i).p_item_category_name := Null;
739 End If;
740
741 If P_planning_resource_in_tbl(i).p_attribute_category = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
742 X_planning_resource_in_tbl(i).p_attribute_category := Member_Rec_Details.attribute_category;
743 End If;
744
745 If P_planning_resource_in_tbl(i).p_attribute1 = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
746 X_planning_resource_in_tbl(i).p_attribute1 := Member_Rec_Details.attribute1;
747 End If;
748
749 If P_planning_resource_in_tbl(i).p_attribute2 = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
750 X_planning_resource_in_tbl(i).p_attribute2 := Member_Rec_Details.attribute2;
751 End If;
752
753 If P_planning_resource_in_tbl(i).p_attribute3 = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
754 X_planning_resource_in_tbl(i).p_attribute3 := Member_Rec_Details.attribute3;
755 End If;
756
757 If P_planning_resource_in_tbl(i).p_attribute4 = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
758 X_planning_resource_in_tbl(i).p_attribute4 := Member_Rec_Details.attribute4;
759 End If;
760
761 If P_planning_resource_in_tbl(i).p_attribute5 = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
762 X_planning_resource_in_tbl(i).p_attribute5 := Member_Rec_Details.attribute5;
763 End If;
764
765 If P_planning_resource_in_tbl(i).p_attribute6 = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
766 X_planning_resource_in_tbl(i).p_attribute6 := Member_Rec_Details.attribute6;
767 End If;
768
769 If P_planning_resource_in_tbl(i).p_attribute7 = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
770 X_planning_resource_in_tbl(i).p_attribute7 := Member_Rec_Details.attribute7;
771 End If;
772
773 If P_planning_resource_in_tbl(i).p_attribute8 = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
774 X_planning_resource_in_tbl(i).p_attribute8 := Member_Rec_Details.attribute8;
775 End If;
776
777 If P_planning_resource_in_tbl(i).p_attribute9 = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
778 X_planning_resource_in_tbl(i).p_attribute9 := Member_Rec_Details.attribute9;
779 End If;
780
781 If P_planning_resource_in_tbl(i).p_attribute10 = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
782 X_planning_resource_in_tbl(i).p_attribute10:= Member_Rec_Details.attribute10;
783 End If;
784
785 If P_planning_resource_in_tbl(i).p_attribute11 = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
786 X_planning_resource_in_tbl(i).p_attribute11:= Member_Rec_Details.attribute11;
787 End If;
788
789 If P_planning_resource_in_tbl(i).p_attribute12= PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
790 X_planning_resource_in_tbl(i).p_attribute12:= Member_Rec_Details.attribute12;
791 End If;
792
793 If P_planning_resource_in_tbl(i).p_attribute13= PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
794 X_planning_resource_in_tbl(i).p_attribute13:= Member_Rec_Details.attribute13;
795 End If;
796
797 If P_planning_resource_in_tbl(i).p_attribute14 = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
798 X_planning_resource_in_tbl(i).p_attribute14 := Member_Rec_Details.attribute14;
799 End If;
800
801 If P_planning_resource_in_tbl(i).p_attribute15 = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
802 X_planning_resource_in_tbl(i).p_attribute15 := Member_Rec_Details.attribute15;
803 End If;
804
805 If P_planning_resource_in_tbl(i).p_attribute16= PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
806 X_planning_resource_in_tbl(i).p_attribute16:= Member_Rec_Details.attribute16;
807 End If;
808
809 If P_planning_resource_in_tbl(i).p_attribute17= PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
810 X_planning_resource_in_tbl(i).p_attribute17:= Member_Rec_Details.attribute17;
811 End If;
812
813 If P_planning_resource_in_tbl(i).p_attribute18= PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
814 X_planning_resource_in_tbl(i).p_attribute18:= Member_Rec_Details.attribute18;
815 End If;
816
817 If P_planning_resource_in_tbl(i).p_attribute19= PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
818 X_planning_resource_in_tbl(i).p_attribute19:= Member_Rec_Details.attribute19;
819 End If;
820
821 If P_planning_resource_in_tbl(i).p_attribute20= PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
822 X_planning_resource_in_tbl(i).p_attribute20:= Member_Rec_Details.attribute20;
823 End If;
824
825 If P_planning_resource_in_tbl(i).p_attribute21= PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
826 X_planning_resource_in_tbl(i).p_attribute21:= Member_Rec_Details.attribute21;
827 End If;
828
829 If P_planning_resource_in_tbl(i).p_attribute22= PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
830 X_planning_resource_in_tbl(i).p_attribute22:= Member_Rec_Details.attribute22;
831 End If;
832
833 If P_planning_resource_in_tbl(i).p_attribute23= PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
834 X_planning_resource_in_tbl(i).p_attribute23:= Member_Rec_Details.attribute23;
835 End If;
836
837 If P_planning_resource_in_tbl(i).p_attribute24= PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
838 X_planning_resource_in_tbl(i).p_attribute24:= Member_Rec_Details.attribute24;
839 End If;
840
841 If P_planning_resource_in_tbl(i).p_attribute25= PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
842 X_planning_resource_in_tbl(i).p_attribute25:= Member_Rec_Details.attribute25;
843 End If;
844
845 If P_planning_resource_in_tbl(i).p_attribute26= PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
846 X_planning_resource_in_tbl(i).p_attribute26:= Member_Rec_Details.attribute26;
847 End If;
848
849 If P_planning_resource_in_tbl(i).p_attribute27= PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
850 X_planning_resource_in_tbl(i).p_attribute27:= Member_Rec_Details.attribute27;
851 End If;
852
853 If P_planning_resource_in_tbl(i).p_attribute28= PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
854 X_planning_resource_in_tbl(i).p_attribute28 := Member_Rec_Details.attribute28;
855 End If;
856
857 If P_planning_resource_in_tbl(i).p_attribute29= PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
858 X_planning_resource_in_tbl(i).p_attribute29:= Member_Rec_Details.attribute29;
859 End If;
860
861 If P_planning_resource_in_tbl(i).p_attribute30= PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
862 X_planning_resource_in_tbl(i).p_attribute30:= Member_Rec_Details.attribute30;
863 End If;
864
865 If P_planning_resource_in_tbl(i).p_person_type_code = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
866 X_planning_resource_in_tbl(i).p_person_type_code := Member_Rec_Details.person_type_code;
867 End If;
868
869 If P_planning_resource_in_tbl(i).p_bom_resource_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM Then
870 X_planning_resource_in_tbl(i).p_bom_resource_id := Member_Rec_Details.bom_resource_id;
871 End If;
872
873 If P_planning_resource_in_tbl(i).p_bom_resource_name = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
874 X_planning_resource_in_tbl(i).p_bom_resource_name := Null;
875 End If;
876
877 If P_planning_resource_in_tbl(i).p_team_role = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
878 X_planning_resource_in_tbl(i).p_team_role := Member_Rec_Details.team_role;
879 End If;
880
881 If P_planning_resource_in_tbl(i).p_incur_by_res_code = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
882 X_planning_resource_in_tbl(i).p_incur_by_res_code := Null;
883 End If;
884
885 If P_planning_resource_in_tbl(i).p_incur_by_res_type = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
886 X_planning_resource_in_tbl(i).p_incur_by_res_type := Null;
887 End If;
888
889 If P_planning_resource_in_tbl(i).p_record_version_number = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM Then
890 X_planning_resource_in_tbl(i).p_record_version_number := Member_Rec_Details.record_version_number;
891 End If;
892
893 If P_planning_resource_in_tbl(i).p_project_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM Then
894 X_planning_resource_in_tbl(i).p_project_id := Null;
895 End If;
896
897 If P_planning_resource_in_tbl(i).p_enabled_flag = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
898 X_planning_resource_in_tbl(i).p_enabled_flag := Member_Rec_Details.enabled_flag;
899 End If;
900
901 End If;
902
903 End If;
904
905 End Loop;
906
907 End If;
908
909 End IF;
910
911 If P_Mode = 0 Then
912
913 IF P_planning_resource_in_tbl.count >0 then
914
915 For i in P_planning_resource_in_tbl.First .. P_planning_resource_in_tbl.Last
916 Loop
917
918 If P_planning_resource_in_tbl(i).P_resource_alias = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
919 X_planning_resource_in_tbl(i).P_resource_alias := Null;
920 End If;
921
922 If P_planning_resource_in_tbl(i).p_resource_list_member_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM Then
923 X_planning_resource_in_tbl(i).p_resource_list_member_id := Null;
924 End If;
925
926 If P_planning_resource_in_tbl(i).p_person_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM Then
927 X_planning_resource_in_tbl(i).p_person_id := Null;
928 End If;
929
930 If P_planning_resource_in_tbl(i).p_person_name = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
931 X_planning_resource_in_tbl(i).p_person_name := Null;
932 End If;
933
934 If P_planning_resource_in_tbl(i).p_job_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM Then
935 X_planning_resource_in_tbl(i).p_job_id := Null;
936 End If;
937
938 If P_planning_resource_in_tbl(i).p_job_name = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
939 X_planning_resource_in_tbl(i).p_job_name := Null;
940 End If;
941
942 If P_planning_resource_in_tbl(i).p_organization_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM Then
943 X_planning_resource_in_tbl(i).p_organization_id := Null;
944 End If;
945
946 If P_planning_resource_in_tbl(i).p_organization_name = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
947 X_planning_resource_in_tbl(i).p_organization_name := null;
948 End If;
949
950 If P_planning_resource_in_tbl(i).p_vendor_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM Then
951 X_planning_resource_in_tbl(i).p_vendor_id := Null;
952 End If;
953
954 If P_planning_resource_in_tbl(i).p_vendor_name = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
955 X_planning_resource_in_tbl(i).p_vendor_name := null;
956 End If;
957
958 If P_planning_resource_in_tbl(i).p_fin_category_name = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
959 X_planning_resource_in_tbl(i).p_fin_category_name := Null;
960 End If;
961
962 If P_planning_resource_in_tbl(i).p_non_labor_resource = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
963 X_planning_resource_in_tbl(i).p_non_labor_resource:= Null;
964 End If;
965
966 If P_planning_resource_in_tbl(i).p_project_role_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM Then
967 X_planning_resource_in_tbl(i).p_project_role_id:= Null;
968 End If;
969
970 If P_planning_resource_in_tbl(i).p_project_role_name = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
971 X_planning_resource_in_tbl(i).p_project_role_name:=Null;
972 End If;
973
974 If P_planning_resource_in_tbl(i).p_resource_class_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM Then
975 X_planning_resource_in_tbl(i).p_resource_class_id:=Null;
976 End If;
977
978 If P_planning_resource_in_tbl(i).p_resource_class_code = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
979 X_planning_resource_in_tbl(i).p_resource_class_code :=Null;
980 End If;
981
982 If P_planning_resource_in_tbl(i).p_res_format_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM Then
983 X_planning_resource_in_tbl(i).p_res_format_id := Null;
984 End If;
985
986 If P_planning_resource_in_tbl(i).p_spread_curve_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM Then
987 X_planning_resource_in_tbl(i).p_spread_curve_id:= Null;
988 End If;
989
990 If P_planning_resource_in_tbl(i).p_etc_method_code = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
991 X_planning_resource_in_tbl(i).p_etc_method_code := Null;
992 End If;
993
994 If P_planning_resource_in_tbl(i).p_mfc_cost_type_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM Then
995 X_planning_resource_in_tbl(i).p_mfc_cost_type_id := Null;
996 End If;
997
998 If P_planning_resource_in_tbl(i).p_fc_res_type_code = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
999 X_planning_resource_in_tbl(i).p_fc_res_type_code := Null;
1000 End If;
1001
1002 If P_planning_resource_in_tbl(i).p_inventory_item_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM Then
1003 X_planning_resource_in_tbl(i).p_inventory_item_id := Null;
1004 End If;
1005
1006 If P_planning_resource_in_tbl(i).p_inventory_item_name = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
1007 X_planning_resource_in_tbl(i).p_inventory_item_name := Null;
1008 End If;
1009
1010 If P_planning_resource_in_tbl(i).p_item_category_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM Then
1011 X_planning_resource_in_tbl(i).p_item_category_id := Null;
1012 End If;
1013
1014 If P_planning_resource_in_tbl(i).p_item_category_name = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
1015 X_planning_resource_in_tbl(i).p_item_category_name := Null;
1016 End If;
1017
1018 If P_planning_resource_in_tbl(i).p_attribute_category = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
1019 X_planning_resource_in_tbl(i).p_attribute_category := Null;
1020 End If;
1021
1022 If P_planning_resource_in_tbl(i).p_attribute1 = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
1023 X_planning_resource_in_tbl(i).p_attribute1 := Null;
1024 End If;
1025
1026 If P_planning_resource_in_tbl(i).p_attribute2 = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
1027 X_planning_resource_in_tbl(i).p_attribute2 := Null;
1028 End If;
1029
1030 If P_planning_resource_in_tbl(i).p_attribute3 = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
1031 X_planning_resource_in_tbl(i).p_attribute3 := Null;
1032 End If;
1033
1034 If P_planning_resource_in_tbl(i).p_attribute4 = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
1035 X_planning_resource_in_tbl(i).p_attribute4 := Null;
1036 End If;
1037
1038 If P_planning_resource_in_tbl(i).p_attribute5 = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
1039 X_planning_resource_in_tbl(i).p_attribute5 := Null;
1040 End If;
1041
1042 If P_planning_resource_in_tbl(i).p_attribute6 = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
1043 X_planning_resource_in_tbl(i).p_attribute6 := Null;
1044 End If;
1045
1046 If P_planning_resource_in_tbl(i).p_attribute7 = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
1047 X_planning_resource_in_tbl(i).p_attribute7 := Null;
1048 End If;
1049
1050 If P_planning_resource_in_tbl(i).p_attribute8 = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
1051 X_planning_resource_in_tbl(i).p_attribute8 := Null;
1052 End If;
1053
1054 If P_planning_resource_in_tbl(i).p_attribute9 = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
1055 X_planning_resource_in_tbl(i).p_attribute9 := Null;
1056 End If;
1057
1058 If P_planning_resource_in_tbl(i).p_attribute10 = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
1059 X_planning_resource_in_tbl(i).p_attribute10:= Null;
1060 End If;
1061
1062 If P_planning_resource_in_tbl(i).p_attribute11 = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
1063 X_planning_resource_in_tbl(i).p_attribute11:= Null;
1064 End If;
1065
1066 If P_planning_resource_in_tbl(i).p_attribute12= PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
1067 X_planning_resource_in_tbl(i).p_attribute12:= Null;
1068 End If;
1069
1070 If P_planning_resource_in_tbl(i).p_attribute13= PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
1071 X_planning_resource_in_tbl(i).p_attribute13:= Null;
1072 End If;
1073
1074 If P_planning_resource_in_tbl(i).p_attribute14 = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
1075 X_planning_resource_in_tbl(i).p_attribute14 := Null;
1076 End If;
1077
1078 If P_planning_resource_in_tbl(i).p_attribute15 = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
1079 X_planning_resource_in_tbl(i).p_attribute15 := Null;
1080 End If;
1081
1082 If P_planning_resource_in_tbl(i).p_attribute16= PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
1083 X_planning_resource_in_tbl(i).p_attribute16:= Null;
1084 End If;
1085
1086 If P_planning_resource_in_tbl(i).p_attribute17= PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
1087 X_planning_resource_in_tbl(i).p_attribute17:= null;
1088 End If;
1089
1090 If P_planning_resource_in_tbl(i).p_attribute18= PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
1091 X_planning_resource_in_tbl(i).p_attribute18:= Null;
1092 End If;
1093
1094 If P_planning_resource_in_tbl(i).p_attribute19= PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
1095 X_planning_resource_in_tbl(i).p_attribute19:= Null;
1096 End If;
1097
1098 If P_planning_resource_in_tbl(i).p_attribute20= PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
1099 X_planning_resource_in_tbl(i).p_attribute20:= Null;
1100 End If;
1101
1102 If P_planning_resource_in_tbl(i).p_attribute21= PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
1103 X_planning_resource_in_tbl(i).p_attribute21:= Null;
1104 End If;
1105
1106 If P_planning_resource_in_tbl(i).p_attribute22= PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
1107 X_planning_resource_in_tbl(i).p_attribute22:= Null;
1108 End If;
1109
1110 If P_planning_resource_in_tbl(i).p_attribute23= PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
1111 X_planning_resource_in_tbl(i).p_attribute23:= null;
1112 End If;
1113
1114 If P_planning_resource_in_tbl(i).p_attribute24= PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
1115 X_planning_resource_in_tbl(i).p_attribute24:= Null;
1116 End If;
1117
1118 If P_planning_resource_in_tbl(i).p_attribute25= PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
1119 X_planning_resource_in_tbl(i).p_attribute25:= Null;
1120 End If;
1121
1122 If P_planning_resource_in_tbl(i).p_attribute26= PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
1123 X_planning_resource_in_tbl(i).p_attribute26:= Null;
1124 End If;
1125
1126 If P_planning_resource_in_tbl(i).p_attribute27= PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
1127 X_planning_resource_in_tbl(i).p_attribute27:= Null;
1128 End If;
1129
1130 If P_planning_resource_in_tbl(i).p_attribute28= PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
1131 X_planning_resource_in_tbl(i).p_attribute28 := Null;
1132 End If;
1133
1134 If P_planning_resource_in_tbl(i).p_attribute29= PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
1135 X_planning_resource_in_tbl(i).p_attribute29:= Null;
1136 End If;
1137
1138 If P_planning_resource_in_tbl(i).p_attribute30= PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
1139 X_planning_resource_in_tbl(i).p_attribute30:= Null;
1140 End If;
1141
1142 If P_planning_resource_in_tbl(i).p_person_type_code = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
1143 X_planning_resource_in_tbl(i).p_person_type_code := Null;
1144 End If;
1145
1146 If P_planning_resource_in_tbl(i).p_bom_resource_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM Then
1147 X_planning_resource_in_tbl(i).p_bom_resource_id := Null;
1148 End If;
1149
1150 If P_planning_resource_in_tbl(i).p_bom_resource_name = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
1151 X_planning_resource_in_tbl(i).p_bom_resource_name := Null;
1152 End If;
1153
1154 If P_planning_resource_in_tbl(i).p_team_role = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
1155 X_planning_resource_in_tbl(i).p_team_role := Null;
1156 End If;
1157
1158 If P_planning_resource_in_tbl(i).p_incur_by_res_code = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
1159 X_planning_resource_in_tbl(i).p_incur_by_res_code := Null;
1160 End If;
1161
1162 If P_planning_resource_in_tbl(i).p_incur_by_res_type = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
1163 X_planning_resource_in_tbl(i).p_incur_by_res_type := Null;
1164 End If;
1165
1166 If P_planning_resource_in_tbl(i).p_record_version_number = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM Then
1167 X_planning_resource_in_tbl(i).p_record_version_number := Null;
1168 End If;
1169
1170 If P_planning_resource_in_tbl(i).p_project_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM Then
1171 X_planning_resource_in_tbl(i).p_project_id := Null;
1172 End If;
1173
1174 If P_planning_resource_in_tbl(i).p_enabled_flag = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
1175 X_planning_resource_in_tbl(i).p_enabled_flag := Null;
1176 End If;
1177
1178 End Loop;
1179
1180 End If;
1181
1182 End If;--p=0
1183 -- 4537865 : Havent included EXCEPTION block per mail from Ranjana
1184 End Convert_Missing_Member_IN_Rec;
1185
1186
1187 /****************************************************************************************
1188 * Procedure : Create_Plan_RL_Format
1189 * Public/Private : Public
1190 * Procedure/Function: Procedure
1191 * Description : This API adds planning resources to a resource list. Called
1192 * from CREATE_RESOURCE_LIST or UPDATE_RESOURCE_LIST API in this
1193 * package.
1194 ****************************************************************************************/
1195
1196 Procedure Create_Plan_RL_Format(
1197 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1198 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1199 P_Res_List_id IN NUMBER,
1200 P_Plan_RL_Format_Tbl IN Plan_RL_Format_In_Tbl,
1201 X_Plan_RL_Format_Tbl OUT NOCOPY Plan_RL_Format_Out_Tbl,
1202 X_Return_Status OUT NOCOPY VARCHAR2,
1203 X_Msg_Count OUT NOCOPY NUMBER,
1204 X_Msg_Data OUT NOCOPY VARCHAR2);
1205
1206
1207
1208 /******************************************************************************************
1209 * Procedure : Create_Planning_Resource
1210 * Public/Private : Public
1211 * Procedure/Function: Procedure
1212 * Description : The purpose of this procedure is to Validate
1213 * and create new planning resources for the
1214 * given resource list.
1215 ********************************************************************************************/
1216 PROCEDURE Create_Planning_Resource(
1217 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1218 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1219 p_resource_list_id IN VARCHAR2,
1220 P_planning_resource_in_tbl IN Planning_Resource_In_Tbl,
1221 X_planning_resource_out_tbl OUT NOCOPY Planning_Resource_Out_Tbl,
1222 x_return_status OUT NOCOPY VARCHAR2,
1223 x_msg_count OUT NOCOPY NUMBER,
1224 x_error_msg_data OUT NOCOPY VARCHAR2 );
1225
1226 /***********************************************************
1227 * Procedure : Create_Resource_List
1228 * Description : AMG API, used to create a resource list
1229 * and its corresponding members and formats.
1230 * The detailed information is in spec.
1231 **********************************************************/
1232 PROCEDURE Create_Resource_List
1233 (p_commit IN VARCHAR2 := FND_API.G_FALSE,
1234 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1235 p_api_version_number IN NUMBER,
1236 P_plan_res_list_Rec IN Plan_Res_List_IN_Rec,
1237 X_plan_res_list_Rec OUT NOCOPY Plan_Res_List_OUT_Rec,
1238 P_Plan_RL_Format_Tbl IN Plan_RL_Format_In_Tbl,
1239 X_Plan_RL_Format_Tbl OUT NOCOPY Plan_RL_Format_Out_Tbl,
1240 P_planning_resource_in_tbl IN Planning_Resource_In_Tbl,
1241 X_planning_resource_out_tbl OUT NOCOPY Planning_Resource_Out_Tbl,
1242 X_Return_Status OUT NOCOPY VARCHAR2,
1243 X_Msg_Count OUT NOCOPY NUMBER,
1244 X_Msg_Data OUT NOCOPY VARCHAR2)
1245 IS
1246 l_api_version_number CONSTANT NUMBER := G_API_VERSION_NUMBER;
1247 l_api_name CONSTANT VARCHAR2(30) := 'Create_Resource_List';
1248 l_resource_list_id Number;
1249 l_error_code NUMBER := 0;
1250 l_error_stage VARCHAR2(2000);
1251 l_error_stack VARCHAR2(2000);
1252 l_return_status Varchar2(30);
1253 l_msg_count Number;
1254 l_msg_data Varchar2(100);
1255 l_business_group_id Number := null;
1256 l_Plan_Res_List_Rec Plan_Res_List_IN_Rec;
1257 L_Plan_RL_Format_Tbl Plan_RL_Format_In_Tbl;
1258 L_Planning_resource_in_tbl Planning_Resource_In_Tbl;
1259 l_mode Number;
1260 NAME_NULL_ERR Exception;
1261 NOFORMAT_EXISTS_ERR Exception;
1262
1263 BEGIN
1264
1265 Pa_Planning_Resource_Pvt.g_amg_flow := 'Y';
1266
1267 --Initialize the message stack before starting any further processing.
1268 IF FND_API.to_boolean( p_init_msg_list )
1269 THEN
1270 FND_MSG_PUB.initialize;
1271 END IF;
1272
1273 --Initialize the Out Variables.
1274 x_return_status := FND_API.G_RET_STS_SUCCESS;
1275 x_msg_count := 0;
1276 l_mode:=0;
1277
1278 --Set a Savepoint so that if error occurs at any stage we can
1279 -- rollback all the changes.
1280 --SAVEPOINT Create_Resource_List_Pub;
1281
1282 --Check for the Compatibility of the API Version
1283 --This is a must for AMG API's
1284 --Doubt -- does this have to be done for all the api's/the main one??
1285 IF NOT FND_API.Compatible_API_Call
1286 ( l_api_version_number ,
1287 p_api_version_number ,
1288 l_api_name ,
1289 G_PKG_NAME )
1290 THEN
1291
1292 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1293 END IF;
1294 Convert_Missing_List_IN_Rec(
1295 P_Plan_res_list_Rec => P_Plan_res_list_Rec,
1296 X_Plan_res_list_Rec => L_Plan_res_list_Rec,
1297 P_Mode => l_mode);
1298
1299 Convert_Missing_Format_IN_Rec(
1300 P_Plan_RL_Format_Tbl => P_Plan_RL_Format_Tbl,
1301 X_Plan_RL_Format_Tbl => L_Plan_RL_Format_Tbl);
1302
1303
1304 Convert_Missing_Member_IN_Rec(
1305 P_Planning_resource_in_tbl => P_Planning_resource_in_tbl,
1306 P_Plan_res_list_Rec => L_Plan_res_list_Rec,
1307 X_Planning_resource_in_tbl => L_Planning_resource_in_tbl,
1308 P_Mode => l_mode);
1309
1310
1311 -- derive business_group_id to pass to Pa_Create_Resource.create_resource_list()
1312 -- procedure. The approach assumes that the user executing this procedure has
1313 -- already set the operating unit via dbms_application_info.set_client_info().
1314 -- Otherwise, a value of null will be passed to the called procedure.
1315 Select
1316 business_group_id
1317 Into
1318 l_business_group_id
1319 From
1320 Pa_Implementations;
1321
1322 -- For bug 3675288.
1323 If L_plan_res_list_Rec.p_resource_list_name is NULL Then
1324
1325 RAISE NAME_NULL_ERR;
1326
1327 End If;
1328
1329
1330 /***************************************************************
1331 * Call to the Pa_Create_Resource.Create_Resource_List API
1332 * which will create a resource list, when we pass the
1333 * the corr values for resource_list_name, description etc
1334 * Its all a part of the P_plan_res_list_Rec record structure.
1335 * It will create a resource list and pass back the resource_list_id
1336 * value as an out parameter into l_resource_list_id variable.
1337 * This resource list ID value will then be passed while creating
1338 * resource formats and resource list members.
1339 ***************************************************************/
1340 Pa_Create_Resource.Create_Resource_List
1341 (p_resource_list_name => L_plan_res_list_Rec.p_resource_list_name,
1342 p_description => L_plan_res_list_Rec.p_description,
1343 p_public_flag => 'Y',
1344 p_group_resource_type => NULL,
1345 p_start_date => L_plan_res_list_Rec.p_start_date,
1346 p_end_date => L_plan_res_list_Rec.p_end_date,
1347 p_business_group_id => l_business_group_id,
1348 p_job_group_id => L_plan_res_list_Rec.p_job_group_id,
1349 p_job_group_name => L_plan_res_list_Rec.p_job_group_name ,
1350 p_use_for_wp_flag => L_plan_res_list_Rec.p_use_for_wp_flag ,
1351 p_control_flag => L_plan_res_list_Rec.p_control_flag ,
1352 p_migration_code => 'N',
1353 p_record_version_number =>
1354 L_plan_res_list_Rec.p_record_version_number ,
1355 p_resource_list_id => x_plan_res_list_rec.x_resource_list_id,
1356 p_err_code => l_error_code,
1357 p_err_stage => l_error_stage ,
1358 p_err_stack => l_error_stack );
1359
1360
1361 IF l_error_code > 0 THEN
1362
1363 RAISE FND_API.G_EXC_ERROR;
1364 END IF;
1365 IF l_error_code < 0 THEN
1366
1367 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1368 END IF;
1369
1370 /************************************************
1371 * Check the Commit flag. if it is true then Commit.
1372 ************************************************/
1373 IF l_error_code = 0 THEN
1374 IF FND_API.to_boolean( p_commit )
1375 THEN
1376 COMMIT;
1377 END IF;
1378 END IF;
1379 l_resource_list_id := x_plan_res_list_rec.x_resource_list_id;
1380 /**************************************************
1381 * Call to the Pa_Plan_Res_List_Pub.Create_Plan_RL_Format
1382 * API which will add the resource formats passed in as
1383 * table of recs. We are also passing the resource list ID
1384 * which was created.
1385 **************************************************/
1386
1387 --For Bug 3675288.
1388 IF (L_Plan_RL_Format_Tbl.Count = 0 AND L_planning_resource_in_tbl.Count <> 0) THEN
1389
1390 RAISE NOFORMAT_EXISTS_ERR;
1391
1392 END IF;
1393
1394 IF L_Plan_RL_Format_Tbl.Count <> 0 Then
1395 Pa_Plan_Res_List_Pub.Create_Plan_RL_Format(
1396 p_commit => p_commit,
1397 p_init_msg_list => p_init_msg_list,
1398 P_Res_List_Id => l_resource_list_id,
1399 P_Plan_RL_Format_Tbl => L_Plan_RL_Format_Tbl,
1400 X_Plan_RL_Format_Tbl => X_Plan_RL_Format_Tbl,
1401 X_Return_Status => l_return_status,
1402 X_Msg_Count => l_msg_count,
1403 X_Msg_Data => l_msg_data);
1404
1405
1406 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1407 x_return_status := FND_API.G_RET_STS_ERROR;
1408 x_msg_count := x_msg_count + 1;
1409 x_msg_data := l_msg_data;
1410 PA_UTILS.Add_Message ('PA', x_msg_data);
1411 Rollback;
1412 Return;
1413 END IF;
1414
1415 END IF;
1416
1417 /**************************************************
1418 * Call to the Pa_Plan_Res_List_Pub.Create_Planning_Resource
1419 * API which will add the resource members passed in as
1420 * table of recs. We are also passing the resource list ID
1421 * which was created.
1422 **************************************************/
1423 --SAVEPOINT Create_Resource_List_Pub_B;
1424
1425 Pa_Plan_Res_List_Pub.Create_Planning_Resource(
1426 p_commit => p_commit,
1427 p_init_msg_list => p_init_msg_list,
1428 p_resource_list_id => l_resource_list_id,
1429 P_planning_resource_in_tbl => L_planning_resource_in_tbl,
1430 X_planning_resource_out_tbl => X_planning_resource_out_tbl,
1431 x_return_status => l_return_status,
1432 x_msg_count => l_msg_count,
1433 x_error_msg_data => l_msg_data );
1434
1435 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1436 x_return_status := FND_API.G_RET_STS_ERROR;
1437 x_msg_count := x_msg_count + 1;
1438 x_msg_data := l_msg_data;
1439 PA_UTILS.Add_Message ('PA', x_msg_data);
1440 ROLLBACK ;
1441 Return;
1442 END IF;
1443
1444 EXCEPTION
1445 WHEN NOFORMAT_EXISTS_ERR THEN
1446 x_return_status := FND_API.G_RET_STS_ERROR ;
1447 X_Msg_Data := 'PA_AMG_CANNOT_CRT_MEMBERS';
1448 x_msg_count := x_msg_count + 1;
1449 Pa_Utils.Add_Message
1450 (P_App_Short_Name => 'PA',
1451 P_Msg_Name => 'PA_AMG_CANNOT_CRT_MEMBERS');
1452 WHEN NAME_NULL_ERR THEN
1453 x_return_status := FND_API.G_RET_STS_ERROR ;
1454 X_Msg_Data := 'PA_AMG_LIST_NAME_NULL';
1455 x_msg_count := x_msg_count + 1;
1456 Pa_Utils.Add_Message
1457 (P_App_Short_Name => 'PA',
1458 P_Msg_Name => 'PA_AMG_LIST_NAME_NULL');
1459 ROLLBACK ;
1460 Return;
1461 WHEN FND_API.G_EXC_ERROR THEN
1462 x_return_status := FND_API.G_RET_STS_ERROR ;
1463 X_Msg_Data := l_error_stack;
1464 x_msg_count := x_msg_count + 1;
1465 ROLLBACK ;
1466 Return;
1467 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1468 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1469 x_msg_count := x_msg_count + 1;
1470 X_Msg_Data := SUBSTRB(SQLERRM,1,240); -- 4537865
1471 ROLLBACK ;
1472 -- 4537865
1473 FND_MSG_PUB.Add_Exc_Msg
1474 ( p_pkg_name => G_PKG_NAME ,
1475 p_procedure_name => l_api_name,
1476 p_error_text => X_Msg_Data
1477 );
1478 Return;
1479
1480 WHEN OTHERS THEN
1481 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1482 x_msg_count := x_msg_count + 1; -- 4537865
1483 X_Msg_Data := SUBSTRB(SQLERRM,1,240); -- 4537865
1484 ROLLBACK;
1485 FND_MSG_PUB.Add_Exc_Msg
1486 ( p_pkg_name => G_PKG_NAME ,
1487 p_procedure_name => l_api_name,
1488 p_error_text => x_msg_data -- 4537865
1489 );
1490 Return;
1491 END Create_Resource_List;
1492
1493 /***********************************************************
1494 * Procedure : Update_Resource_List
1495 * Description : AMG API, used to Update a resource list
1496 * and its corresponding members and formats.
1497 * The detailed information is in the spec.
1498 *********************************************************/
1499 PROCEDURE Update_Resource_List(
1500 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1501 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1502 p_api_version_number IN NUMBER,
1503 P_plan_res_list_Rec IN Plan_Res_List_IN_Rec,
1504 X_plan_res_list_Rec OUT NOCOPY Plan_Res_List_OUT_Rec,
1505 P_Plan_RL_Format_Tbl IN Plan_RL_Format_In_Tbl,
1506 X_Plan_RL_Format_Tbl OUT NOCOPY Plan_RL_Format_Out_Tbl,
1507 P_planning_resource_in_tbl IN Planning_Resource_In_Tbl,
1508 X_planning_resource_out_tbl OUT NOCOPY Planning_Resource_Out_Tbl,
1509 X_Return_Status OUT NOCOPY VARCHAR2,
1510 X_Msg_Count OUT NOCOPY NUMBER,
1511 X_Msg_Data OUT NOCOPY VARCHAR2)
1512
1513 IS
1514
1515 l_api_version_number CONSTANT NUMBER := G_API_VERSION_NUMBER;
1516 l_api_name CONSTANT VARCHAR2(30) := 'Update_Resource_List';
1517 l_return_status Varchar2(30);
1518 l_msg_count Number;
1519 l_msg_data Varchar2(100);
1520 l_check_exist Varchar2(1);
1521 l_rec_ver_number Number;
1522 l_res_class_flag Varchar2(1) := Null;
1523 L_Plan_res_list_Rec Plan_Res_List_IN_Rec;
1524 L_Plan_RL_Format_Tbl Plan_RL_Format_In_Tbl;
1525 L_planning_resource_in_tbl Planning_Resource_In_Tbl;
1526 L_one_pln_res_in_tbl Planning_Resource_In_Tbl;
1527 L_one_pln_res_out_tbl Planning_Resource_Out_Tbl;
1528 l_mode Number := 1;
1529 l_ERROR Exception;
1530 l_msg_index_out Number;
1531 l_data Varchar2(2000) := Null;
1532 l_exists Varchar2(1);
1533 l_object_type Varchar2(30);
1534 l_object_id Number;
1535 l_rlm_record_version_number Number;
1536 l_project_exists Varchar2(1);
1537 l_format_exists Varchar2(1);
1538
1539 CURSOR resource_list_cursor (p_resource_list_id in number) IS
1540 SELECT resource_list_id,
1541 control_flag,
1542 record_version_number,
1543 name,
1544 description,
1545 start_date_active,
1546 end_date_active,
1547 job_group_id,
1548 use_for_wp_flag
1549 FROM PA_RESOURCE_LISTS_ALL_BG
1550 WHERE resource_list_id = p_resource_list_id;
1551
1552 l_old_resource_list_rec resource_list_cursor%RowType;
1553
1554 BEGIN
1555
1556 Pa_Planning_Resource_Pvt.g_amg_flow := 'Y';
1557 --Initialize the message stack before starting any further processing.
1558 If Fnd_Api.To_Boolean( P_Init_Msg_List ) Then
1559
1560 Fnd_Msg_Pub.Initialize;
1561
1562 End If;
1563
1564 --Initialize the Out Variables.
1565 x_return_status := Fnd_Api.G_Ret_Sts_Success;
1566 x_msg_count := 0;
1567
1568
1569 --Set a Savepoint so that if error occurs at any stage we can
1570 -- rollback all the changes.
1571 -- SAVEPOINT Update_Resource_List_Pub;
1572
1573 --Check for the Compatibility of the API Version
1574 --This is a must for AMG API's
1575 If Not Fnd_Api.Compatible_API_Call
1576 ( l_api_version_number ,
1577 p_api_version_number ,
1578 l_api_name ,
1579 G_Pkg_Name ) Then
1580
1581 Raise Fnd_Api.G_Exc_UnExpected_Error;
1582
1583 End If;
1584
1585
1586 Convert_Missing_List_IN_Rec(
1587 P_Plan_res_list_Rec => P_Plan_res_list_Rec,
1588 X_Plan_res_list_Rec => L_Plan_res_list_Rec,
1589 P_Mode => l_mode);
1590
1591 Convert_Missing_Format_IN_Rec(
1592 P_Plan_RL_Format_Tbl => P_Plan_RL_Format_Tbl,
1593 X_Plan_RL_Format_Tbl => L_Plan_RL_Format_Tbl);
1594
1595
1596 Convert_Missing_Member_IN_Rec(
1597 P_Planning_resource_in_tbl => P_Planning_resource_in_tbl,
1598 P_Plan_res_list_Rec => L_Plan_res_list_Rec,
1599 X_Planning_resource_in_tbl => L_Planning_resource_in_tbl,
1600 P_Mode => l_mode);
1601
1602 IF L_plan_res_list_Rec.p_resource_list_id is not null THEN
1603 Open resource_list_cursor(p_resource_list_id => L_plan_res_list_Rec.p_resource_list_id);
1604
1605 Fetch resource_list_cursor into l_old_resource_list_rec;
1606
1607 If resource_list_cursor%NotFound Then
1608 Pa_Debug.G_Stage := 'Resource List Id is invalid.';
1609 Close resource_list_cursor;
1610 Pa_Utils.Add_Message(
1611 P_App_Short_Name => 'PA',
1612 P_Msg_Name => 'PA_INVALID_RESOURCE_LIST_ID');
1613
1614 Raise l_ERROR;
1615
1616 Else
1617
1618 Close resource_list_cursor;
1619
1620 End If;
1621
1622 /* Added for CBS changes */
1623 IF PA_FP_GEN_AMOUNT_UTILS.get_trg_resource_class_flag(L_plan_res_list_Rec.p_resource_list_id) = 'N' THEN
1624 x_return_status := FND_API.G_RET_STS_ERROR;
1625 x_msg_count := x_msg_count + 1;
1626 x_msg_data := 'PA_CNT_USE_AMG_FR_NON_ENHCD_PRL';
1627 Pa_Utils.Add_Message
1628 (P_App_Short_Name => 'PA',
1629 P_Msg_Name => 'PA_CNT_USE_AMG_FR_NON_ENHCD_PRL');
1630
1631 RETURN;
1632 END IF;
1633
1634 /* End of CBS changes */
1635
1636
1637 IF (nvl(l_old_resource_list_rec.name,' ') <> nvl(L_Plan_res_list_Rec.p_resource_list_name,' ') OR
1638 nvl(l_old_resource_list_rec.description,' ') <> nvl(L_Plan_res_list_Rec.p_description,' ') OR
1639 nvl(l_old_resource_list_rec.start_date_active,sysdate) <> nvl(L_Plan_res_list_Rec.p_start_date,sysdate) OR
1640 nvl(l_old_resource_list_rec.end_date_active,sysdate) <> nvl(L_Plan_res_list_Rec.p_end_date,sysdate) OR
1641 nvl(l_old_resource_list_rec.job_group_id,-1) <> nvl(L_Plan_res_list_Rec.p_job_group_id,-1) OR
1642 nvl(l_old_resource_list_rec.use_for_wp_flag,' ') <> nvl(L_Plan_res_list_Rec.p_use_for_wp_flag,' ') OR
1643 nvl(l_old_resource_list_rec.control_flag,' ') <> nvl(L_Plan_res_list_Rec.p_control_flag,' ')
1644 )
1645 THEN
1646
1647 -- One of the resource_list attributes has changed which means,
1648 -- the resource_list record needs to be updated.
1649
1650 IF (L_Plan_res_list_Rec.p_record_version_number is null OR
1651 L_Plan_res_list_Rec.p_record_version_number <> l_old_resource_list_rec.record_version_number) THEN
1652 Pa_Utils.Add_Message(
1653 P_App_Short_Name => 'PA',
1654 P_Msg_Name => 'PA_PLN_RES_LIST_REC_VER_INVAL');
1655
1656 Raise l_ERROR;
1657 END IF;
1658
1659
1660 /********************************************************
1661 * Call to API Pa_Create_Resource.Update_Resource_List to update
1662 * the resource_list with the newly passed values. Values are
1663 * passed thro the record struc P_plan_res_list_Rec.
1664 *********************************************************/
1665
1666 Pa_Create_Resource.Update_Resource_List
1667 (p_resource_list_name => L_plan_res_list_Rec.p_resource_list_name,
1668 p_description => L_plan_res_list_Rec.p_description,
1669 p_start_date => L_plan_res_list_Rec.p_start_date,
1670 p_end_date => L_plan_res_list_Rec.p_end_date,
1671 p_job_group_id => L_Plan_Res_List_Rec.p_job_group_id,
1672 p_job_group_name => L_plan_res_list_Rec.p_job_group_name,
1673 p_use_for_wp_flag => L_plan_res_list_Rec.p_use_for_wp_flag,
1674 p_control_flag => L_plan_res_list_Rec.p_control_flag,
1675 --p_migration_code => 'N',
1676 p_record_version_number => L_Plan_res_list_Rec.p_record_version_number,
1677 p_resource_list_id => L_plan_res_list_Rec.p_resource_list_id,
1678 x_msg_count => l_msg_count,
1679 x_return_status => l_return_status,
1680 x_msg_data => l_msg_data);
1681
1682 If l_Return_Status <> Fnd_Api.G_Ret_Sts_Success Then
1683 x_return_status := Fnd_Api.G_Ret_Sts_Error;
1684 x_msg_count := x_msg_count + 1;
1685 x_msg_data := l_msg_data;
1686 Pa_Utils.Add_Message ('PA', x_msg_data);
1687 Rollback ;
1688 Return;
1689
1690 End If;
1691
1692 /************************************************
1693 * Check the Commit flag. if it is true then Commit.
1694 ************************************************/
1695 If l_return_status = Fnd_Api.G_Ret_Sts_Success Then
1696
1697 If Fnd_Api.To_Boolean( p_commit ) Then
1698
1699 Commit;
1700
1701 End If;
1702
1703 End If;
1704 END IF; -- If resource list attributes have changed.
1705 ELSE
1706 -- You need the resource list Id for any processing.
1707 Pa_Utils.Add_Message(
1708 P_App_Short_Name => 'PA',
1709 P_Msg_Name => 'PA_INVALID_RES_LIST_ID');
1710
1711 Raise l_ERROR;
1712 END IF; -- L_plan_res_list_Rec.p_resource_list_id is not null
1713
1714 /***************************************************
1715 * In the case of Resource formats updation is not possible.
1716 * We can either create/delete resource formats.
1717 * Deletion is taken care thro another api. So here we are
1718 * going to create_resource_format using the pl/sql tables
1719 * P_Plan_RL_Format_Tbl and X_Plan_RL_Format_Tbl passed.
1720 *****************************************************/
1721 --SAVEPOINT Update_Resource_List_Pub_A;
1722 IF L_Plan_RL_Format_Tbl.COUNT > 0 THEN
1723 For i IN L_Plan_RL_Format_Tbl.first..L_Plan_RL_Format_Tbl.last
1724 Loop
1725
1726 -- Validate that the P_RES_FORMAT_ID passed in is valid
1727
1728 BEGIN
1729
1730 SELECT 'Y'
1731 INTO l_exists
1732 FROM pa_res_formats_b
1733 WHERE res_format_id = L_Plan_RL_Format_Tbl(i).P_Res_Format_Id;
1734
1735 EXCEPTION
1736 WHEN NO_DATA_FOUND THEN
1737 Pa_Utils.Add_Message(
1738 P_App_Short_Name => 'PA',
1739 P_Msg_Name => 'PA_INVALID_RES_FORMAT_ID');
1740
1741 Raise l_ERROR;
1742
1743 END;
1744
1745 Pa_Plan_RL_Formats_Pvt.Create_Plan_RL_Format(
1746 P_Res_List_Id => L_plan_res_list_Rec.p_resource_list_id,
1747 P_Res_Format_Id => L_Plan_RL_Format_Tbl(i).P_Res_Format_Id,
1748 X_Plan_RL_Format_Id => X_Plan_RL_Format_Tbl(i).X_Plan_RL_Format_Id,
1749 X_Record_Version_Number => X_Plan_RL_Format_Tbl(i).X_Record_Version_Number,
1750 X_Return_Status => X_Return_Status,
1751 X_Msg_Count => l_msg_count,
1752 X_Msg_Data => l_msg_data);
1753
1754 If X_Return_Status <> Fnd_Api.G_Ret_Sts_Success Then
1755
1756 l_return_status := Fnd_Api.G_Ret_Sts_Error;
1757 x_return_status := Fnd_Api.G_Ret_Sts_Error;
1758 x_msg_count := x_msg_count + 1;
1759 x_msg_data := l_msg_data;
1760 Pa_Utils.Add_Message ('PA', x_msg_data);
1761 RollbacK ;
1762 Return;
1763
1764 Else
1765
1766 l_return_status := Fnd_Api.G_Ret_Sts_Success;
1767
1768 End If;
1769
1770 End Loop;
1771
1772 END IF; -- L_Plan_RL_Format_Tbl.COUNT > 0
1773
1774
1775 /************************************************
1776 * Check the Commit flag. if it is true then Commit.
1777 ************************************************/
1778 If l_return_status = Fnd_Api.G_Ret_Sts_Success Then
1779
1780 If Fnd_Api.To_Boolean( p_commit ) Then
1781
1782 Commit;
1783
1784 End If;
1785
1786 End If;
1787
1788
1789 --SAVEPOINT Update_Resource_List_Pub_B;
1790 IF L_planning_resource_in_tbl.COUNT > 0 THEN
1791 For i IN L_planning_resource_in_tbl.first..L_planning_resource_in_tbl.last
1792 Loop
1793
1794 /**********************************************************
1795 * For each of the resource_list_memeber_id in the table.
1796 * We'll first check if an entry is present in the
1797 * pa_resource_list_members table or not.
1798 * If it is present then we'll call the
1799 * Pa_Planning_resource_pvt.Update_Planning_Resource api,
1800 * which would just update the corr record in the table.
1801 * If it is not present then we'll call the
1802 * pa_planning_resource_pvt.Create_Planning_Resource api,
1803 * which would take care of creating the corr record in
1804 * the table. Once a resource list member is succ created/Updated,
1805 * we'll commit it in the db.
1806 ************************************************************/
1807 /*
1808 --commented out for bug 4103909
1809 If L_planning_resource_in_tbl(i).p_resource_list_member_id Is Null Then
1810
1811 IF l_planning_resource_in_tbl(i).p_resource_alias is null THEN
1812
1813 Pa_Utils.Add_Message(
1814 P_App_Short_Name => 'PA',
1815 P_Msg_Name => 'PA_RLM_ALIAS_AND_ID_NULL');
1816
1817 Raise l_ERROR;
1818
1819 END IF;
1820
1821 End If;
1822
1823 IF l_old_resource_list_rec.control_flag = 'Y' THEN
1824
1825 BEGIN
1826 SELECT resource_list_member_id,
1827 record_version_number
1828 INTO L_planning_resource_in_tbl(i).p_resource_list_member_id,
1829 l_rlm_record_version_number
1830 FROM PA_RESOURCE_LIST_MEMBERS
1831 WHERE resource_list_id = l_old_resource_list_rec.resource_list_id
1832 AND ALIAS = L_planning_resource_in_tbl(i).p_resource_alias;
1833
1834 l_check_exist := 'Y';
1835
1836 EXCEPTION
1837 WHEN NO_DATA_FOUND THEN
1838 l_check_exist := 'N';
1839 END;
1840
1841 ELSE
1842 BEGIN
1843 IF l_planning_resource_in_tbl(i).p_project_id is null THEN
1844 l_object_type := 'RESOURCE_LIST';
1845 l_object_id := l_old_resource_list_rec.resource_list_id;
1846 ELSE
1847 l_object_type := 'PROJECT';
1848 l_object_id := l_planning_resource_in_tbl(i).p_project_id;
1849 END IF;
1850
1851 SELECT resource_list_member_id,
1852 record_version_number
1853 INTO l_planning_resource_in_tbl(i).p_resource_list_member_id,
1854 l_rlm_record_version_number
1855 FROM PA_RESOURCE_LIST_MEMBERS
1856 WHERE resource_list_id = l_old_resource_list_rec.resource_list_id
1857 AND ALIAS = L_planning_resource_in_tbl(i).p_resource_alias
1858 AND object_type = l_object_type
1859 AND object_id = l_object_id;
1860
1861 l_check_exist := 'Y';
1862
1863 EXCEPTION
1864 WHEN NO_DATA_FOUND THEN
1865 l_check_exist := 'N';
1866 END;
1867 END IF;
1868
1869 Else
1870 */ --End of changes for bug 4103909
1871
1872 If L_planning_resource_in_tbl(i).p_resource_list_member_id Is not Null Then
1873 Begin
1874 Select record_version_number
1875 Into l_rlm_record_version_number
1876 From pa_resource_list_members
1877 Where resource_list_member_id = L_planning_resource_in_tbl(i).p_resource_list_member_id;
1878
1879 Exception
1880 When No_Data_Found Then
1881 Pa_Utils.Add_Message(
1882 P_App_Short_Name => 'PA',
1883 P_Msg_Name => 'PA_RLM_ID_INVALID');
1884
1885 Raise l_ERROR;
1886
1887 End;
1888
1889 End If;
1890
1891
1892 If L_planning_resource_in_tbl(i).p_resource_list_member_id is null Then --For bug 4103909
1893
1894 -- We need to create the planning resource. First validate the reqd
1895 -- attributes.
1896
1897 IF L_planning_resource_in_tbl(i).p_res_format_id IS null THEN
1898 Pa_Utils.Add_Message(
1899 P_App_Short_Name => 'PA',
1900 P_Msg_Name => 'PA_RLM_FORMAT_NULL');
1901
1902 Raise l_ERROR;
1903 ELSE
1904 BEGIN
1905 -- derive resource_class_flag
1906 SELECT resource_class_flag
1907 INTO l_res_class_flag
1908 FROM pa_res_formats_b
1909 WHERE res_format_id = L_planning_resource_in_tbl(i).p_res_format_id;
1910 EXCEPTION
1911 WHEN NO_DATA_FOUND THEN
1912 Pa_Utils.Add_Message(
1913 P_App_Short_Name => 'PA',
1914 P_Msg_Name => 'PA_RLM_FORMAT_INVALID');
1915
1916 Raise l_ERROR;
1917
1918 END;
1919
1920 END IF;
1921
1922
1923 --For bug 4055082.
1924 --Raise error if invalid project id is passed while creation of planning resource list memeber.
1925 IF (L_planning_resource_in_tbl(i).p_project_id is not null) Then --For bug 4094047
1926
1927 BEGIN
1928
1929 SELECT 'Y'
1930 INTO l_project_exists
1931 FROM pa_projects_all
1932 WHERE project_id = L_planning_resource_in_tbl(i).p_project_id;
1933
1934 EXCEPTION
1935 WHEN NO_DATA_FOUND THEN
1936 Pa_Utils.Add_Message(
1937 P_App_Short_Name => 'PA',
1938 P_Msg_Name => 'PA_AMG_PROJECT_ID_INVALID',
1939 p_token1 => 'PLAN_RES',
1940 p_value1 => Pa_Planning_Resource_Pvt.g_token);
1941
1942 Raise l_ERROR;
1943
1944 END;
1945
1946 END IF;
1947 --End of bug 4103909.
1948
1949 IF (L_planning_resource_in_tbl(i).p_res_format_id is not null) Then --For bug 4103909.
1950
1951 BEGIN
1952
1953 SELECT 'Y'
1954 INTO l_format_exists
1955 FROM pa_plan_rl_formats
1956 WHERE resource_list_id = L_Plan_res_list_Rec.p_resource_list_id
1957 AND res_format_id = L_planning_resource_in_tbl(i).p_res_format_id;
1958
1959 EXCEPTION
1960 WHEN NO_DATA_FOUND THEN
1961 Pa_Utils.Add_Message(
1962 P_App_Short_Name => 'PA',
1963 P_Msg_Name => 'PA_AMG_FORMAT_NOT_EXISTS',
1964 p_token1 => 'PLAN_RES',
1965 p_value1 => Pa_Planning_Resource_Pvt.g_token);
1966
1967 Raise l_ERROR;
1968
1969 END;
1970
1971 END IF;
1972 --End of bug 4103909.
1973
1974 SELECT meaning || ' ' || to_char(i) || ':'
1975 INTO Pa_Planning_Resource_Pvt.g_token
1976 FROM pa_lookups
1977 WHERE lookup_type = 'PA_PLANNING_RESOURCE'
1978 AND lookup_code = 'PLANNING_RESOURCE';
1979
1980 L_one_pln_res_in_tbl(1) := L_planning_resource_in_tbl(i);
1981 -- dbms_output.put_line('--- in create part of upd');
1982 Pa_Plan_Res_List_Pub.Create_Planning_Resource(
1983 p_commit => p_commit,
1984 p_init_msg_list => p_init_msg_list,
1985 p_resource_list_id => L_plan_res_list_Rec.p_resource_list_id,
1986 P_planning_resource_in_tbl => L_one_pln_res_in_tbl,
1987 X_planning_resource_out_tbl => L_one_pln_res_out_tbl,
1988 x_return_status => x_return_status,
1989 x_msg_count => l_msg_count,
1990 x_error_msg_data => l_msg_data);
1991 -- dbms_output.put_line('--- AFTER in create part of upd');
1992
1993 /*
1994 Pa_Planning_Resource_Pvt.Create_Planning_Resource(
1995 p_resource_list_member_id => L_planning_resource_in_tbl(i).p_resource_list_member_id,
1996 p_resource_list_id => L_plan_res_list_Rec.p_resource_list_id,
1997 p_resource_alias => L_planning_resource_in_tbl(i).p_resource_alias,
1998 p_person_id => L_planning_resource_in_tbl(i).p_person_id,
1999 p_person_name => L_planning_resource_in_tbl(i).p_person_name,
2000 p_job_id => L_planning_resource_in_tbl(i).p_job_id,
2001 p_job_name => L_planning_resource_in_tbl(i).p_job_name,
2002 p_organization_id => L_planning_resource_in_tbl(i).p_organization_id,
2003 p_organization_name => L_planning_resource_in_tbl(i).p_organization_name,
2004 p_vendor_id => L_planning_resource_in_tbl(i).p_vendor_id,
2005 p_vendor_name => L_planning_resource_in_tbl(i).p_vendor_name,
2006 p_fin_category_name => L_planning_resource_in_tbl(i).p_fin_category_name,
2007 p_non_labor_resource => L_planning_resource_in_tbl(i).p_non_labor_resource,
2008 p_project_role_id => L_planning_resource_in_tbl(i).p_project_role_id,
2009 p_project_role_name => L_planning_resource_in_tbl(i).p_project_role_name,
2010 p_resource_class_id => L_planning_resource_in_tbl(i).p_resource_class_id,
2011 p_resource_class_code => L_planning_resource_in_tbl(i).p_resource_class_code,
2012 p_res_format_id => L_planning_resource_in_tbl(i).p_res_format_id,
2013 p_spread_curve_id => L_planning_resource_in_tbl(i).p_spread_curve_id,
2014 p_etc_method_code => L_planning_resource_in_tbl(i).p_etc_method_code,
2015 p_mfc_cost_type_id => L_planning_resource_in_tbl(i).p_mfc_cost_type_id,
2016 p_copy_from_rl_flag => Null,
2017 p_resource_class_flag => l_res_class_flag,
2018 p_fc_res_type_code => L_planning_resource_in_tbl(i).p_fc_res_type_code,
2019 p_inventory_item_id => L_planning_resource_in_tbl(i).p_inventory_item_id,
2020 p_inventory_item_name => L_planning_resource_in_tbl(i).p_inventory_item_name,
2021 p_item_category_id => L_planning_resource_in_tbl(i).p_item_category_id,
2022 p_item_category_name => L_planning_resource_in_tbl(i).p_item_category_name,
2023 p_migration_code => 'N',
2024 p_attribute_category => L_planning_resource_in_tbl(i).p_attribute_category,
2025 p_attribute1 => L_planning_resource_in_tbl(i).p_attribute1,
2026 p_attribute2 => L_planning_resource_in_tbl(i).p_attribute2,
2027 p_attribute3 => L_planning_resource_in_tbl(i).p_attribute3,
2028 p_attribute4 => L_planning_resource_in_tbl(i).p_attribute4,
2029 p_attribute5 => L_planning_resource_in_tbl(i).p_attribute5,
2030 p_attribute6 => L_planning_resource_in_tbl(i).p_attribute6,
2031 p_attribute7 => L_planning_resource_in_tbl(i).p_attribute7,
2032 p_attribute8 => L_planning_resource_in_tbl(i).p_attribute8,
2033 p_attribute9 => L_planning_resource_in_tbl(i).p_attribute9,
2034 p_attribute10 => L_planning_resource_in_tbl(i).p_attribute10,
2035 p_attribute11 => L_planning_resource_in_tbl(i).p_attribute11,
2036 p_attribute12 => L_planning_resource_in_tbl(i).p_attribute12,
2037 p_attribute13 => L_planning_resource_in_tbl(i).p_attribute13,
2038 p_attribute14 => L_planning_resource_in_tbl(i).p_attribute14,
2039 p_attribute15 => L_planning_resource_in_tbl(i).p_attribute15,
2040 p_attribute16 => L_planning_resource_in_tbl(i).p_attribute16,
2041 p_attribute17 => L_planning_resource_in_tbl(i).p_attribute17,
2042 p_attribute18 => L_planning_resource_in_tbl(i).p_attribute18,
2043 p_attribute19 => L_planning_resource_in_tbl(i).p_attribute19,
2044 p_attribute20 => L_planning_resource_in_tbl(i).p_attribute20,
2045 p_attribute21 => L_planning_resource_in_tbl(i).p_attribute21,
2046 p_attribute22 => L_planning_resource_in_tbl(i).p_attribute22,
2047 p_attribute23 => L_planning_resource_in_tbl(i).p_attribute23,
2048 p_attribute24 => L_planning_resource_in_tbl(i).p_attribute24,
2049 p_attribute25 => L_planning_resource_in_tbl(i).p_attribute25,
2050 p_attribute26 => L_planning_resource_in_tbl(i).p_attribute26,
2051 p_attribute27 => L_planning_resource_in_tbl(i).p_attribute27,
2052 p_attribute28 => L_planning_resource_in_tbl(i).p_attribute28,
2053 p_attribute29 => L_planning_resource_in_tbl(i).p_attribute29,
2054 p_attribute30 => L_planning_resource_in_tbl(i).p_attribute30,
2055 p_person_type_code => L_planning_resource_in_tbl(i).p_person_type_code,
2056 p_bom_resource_id => L_planning_resource_in_tbl(i).p_bom_resource_id,
2057 p_bom_resource_name => L_planning_resource_in_tbl(i).p_bom_resource_name,
2058 p_team_role => L_planning_resource_in_tbl(i).p_team_role,
2059 p_incur_by_res_code => L_planning_resource_in_tbl(i).p_incur_by_res_code,
2060 p_incur_by_res_type => L_planning_resource_in_tbl(i).p_incur_by_res_type,
2061 p_project_id => L_planning_resource_in_tbl(i).p_project_id,
2062 x_resource_list_member_id => X_planning_resource_out_tbl(i).x_resource_list_member_id,
2063 x_record_version_number => X_planning_resource_out_tbl(i).x_record_version_number,
2064 x_return_status => x_return_status,
2065 x_msg_count => l_msg_count,
2066 x_error_msg_data => l_msg_data);
2067
2068 */
2069
2070 -- dbms_output.put_line('after X_return_status IS : ' || X_return_status);
2071 -- dbms_output.put_line('after l_msg_data IS : ' || l_msg_data);
2072 If X_return_status <> Fnd_Api.G_Ret_Sts_Success THEN
2073
2074 l_return_status := Fnd_Api.G_Ret_Sts_Error;
2075 x_return_status := Fnd_Api.G_Ret_Sts_Error;
2076 x_msg_count := x_msg_count + 1;
2077 x_msg_data := l_msg_data;
2078 Pa_Utils.Add_Message ('PA', x_msg_data);
2079 Rollback ;
2080 Return;
2081
2082 Else
2083
2084 l_return_status := Fnd_Api.G_Ret_Sts_Success;
2085 X_planning_resource_out_tbl(i) := L_one_pln_res_out_tbl(1);
2086
2087 End If;
2088
2089 End If;
2090
2091 If L_planning_resource_in_tbl(i).p_resource_list_member_id is not null Then --For bug 4103909
2092 --This means that the resource list member is already existing and is supposed to be updated.
2093
2094 SELECT meaning || ' ' || to_char(i) || ':'
2095 INTO Pa_Planning_Resource_Pvt.g_token
2096 FROM pa_lookups
2097 WHERE lookup_type = 'PA_PLANNING_RESOURCE'
2098 AND lookup_code = 'PLANNING_RESOURCE';
2099
2100 -- Validate the record version number passed in.
2101 IF L_planning_resource_in_tbl(i).p_record_version_number is null OR
2102 L_planning_resource_in_tbl(i).p_record_version_number <> l_rlm_record_version_number
2103 THEN
2104 Pa_Utils.Add_Message(
2105 P_App_Short_Name => 'PA',
2106 P_Msg_Name => 'PA_RLM_REC_VER_NOT_VALID',
2107 p_token1 => 'PLAN_RES',
2108 p_value1 => Pa_Planning_Resource_Pvt.g_token);
2109
2110 Raise l_ERROR;
2111 END IF;
2112
2113 Pa_Planning_Resource_Pvt.Update_Planning_Resource(
2114 p_resource_list_id => L_plan_res_list_Rec.p_resource_list_id,
2115 p_resource_list_member_id => L_planning_resource_in_tbl(i).p_resource_list_member_id,
2116 p_enabled_flag => L_planning_resource_in_tbl(i).p_enabled_flag,
2117 p_resource_alias => L_planning_resource_in_tbl(i).p_resource_alias,
2118 p_spread_curve_id => L_planning_resource_in_tbl(i).p_spread_curve_id,
2119 p_etc_method_code => L_planning_resource_in_tbl(i).p_etc_method_code,
2120 p_mfc_cost_type_id => L_planning_resource_in_tbl(i).p_mfc_cost_type_id,
2121 p_attribute_category => L_planning_resource_in_tbl(i).p_attribute_category,
2122 p_attribute1 => L_planning_resource_in_tbl(i).p_attribute1,
2123 p_attribute2 => L_planning_resource_in_tbl(i).p_attribute2,
2124 p_attribute3 => L_planning_resource_in_tbl(i).p_attribute3,
2125 p_attribute4 => L_planning_resource_in_tbl(i).p_attribute4,
2126 p_attribute5 => L_planning_resource_in_tbl(i).p_attribute5,
2127 p_attribute6 => L_planning_resource_in_tbl(i).p_attribute6,
2128 p_attribute7 => L_planning_resource_in_tbl(i).p_attribute7,
2129 p_attribute8 => L_planning_resource_in_tbl(i).p_attribute8,
2130 p_attribute9 => L_planning_resource_in_tbl(i).p_attribute9,
2131 p_attribute10 => L_planning_resource_in_tbl(i).p_attribute10,
2132 p_attribute11 => L_planning_resource_in_tbl(i).p_attribute11,
2133 p_attribute12 => L_planning_resource_in_tbl(i).p_attribute12,
2134 p_attribute13 => L_planning_resource_in_tbl(i).p_attribute13,
2135 p_attribute14 => L_planning_resource_in_tbl(i).p_attribute14,
2136 p_attribute15 => L_planning_resource_in_tbl(i).p_attribute15,
2137 p_attribute16 => L_planning_resource_in_tbl(i).p_attribute16,
2138 p_attribute17 => L_planning_resource_in_tbl(i).p_attribute17,
2139 p_attribute18 => L_planning_resource_in_tbl(i).p_attribute18,
2140 p_attribute19 => L_planning_resource_in_tbl(i).p_attribute19,
2141 p_attribute20 => L_planning_resource_in_tbl(i).p_attribute20,
2142 p_attribute21 => L_planning_resource_in_tbl(i).p_attribute21,
2143 p_attribute22 => L_planning_resource_in_tbl(i).p_attribute22,
2144 p_attribute23 => L_planning_resource_in_tbl(i).p_attribute23,
2145 p_attribute24 => L_planning_resource_in_tbl(i).p_attribute24,
2146 p_attribute25 => L_planning_resource_in_tbl(i).p_attribute25,
2147 p_attribute26 => L_planning_resource_in_tbl(i).p_attribute26,
2148 p_attribute27 => L_planning_resource_in_tbl(i).p_attribute27,
2149 p_attribute28 => L_planning_resource_in_tbl(i).p_attribute28,
2150 p_attribute29 => L_planning_resource_in_tbl(i).p_attribute29,
2151 p_attribute30 => L_planning_resource_in_tbl(i).p_attribute30,
2152 p_record_version_number => L_planning_resource_in_tbl(i).p_record_version_number,
2153 x_record_version_number => x_planning_resource_out_tbl(i).x_record_version_number,
2154 x_return_status => x_return_status,
2155 x_msg_count => l_msg_count,
2156 x_error_msg_data => l_msg_data);
2157
2158 If x_return_status <> Fnd_Api.G_Ret_Sts_Success Then
2159
2160 l_return_status := Fnd_Api.G_Ret_Sts_Error;
2161 x_return_status := Fnd_Api.G_Ret_Sts_Error;
2162 x_msg_count := x_msg_count + 1;
2163 x_msg_data := l_msg_data;
2164 Pa_Utils.Add_Message ('PA', x_msg_data);
2165 Rollback ;
2166 Return;
2167
2168 Else
2169
2170 l_return_status := Fnd_Api.G_Ret_Sts_Success;
2171
2172 End If;
2173
2174 End If;
2175 End Loop;
2176 END IF;
2177 -- Commit only if all records are successful.
2178 /************************************************
2179 * Check the Commit flag. if it is true then Commit.
2180 ************************************************/
2181 If l_return_status = Fnd_Api.G_Ret_Sts_Success Then
2182
2183 If Fnd_Api.To_Boolean( p_commit ) Then
2184
2185 Commit;
2186
2187 End If;
2188
2189 End If;
2190
2191
2192 Exception
2193 When l_ERROR Then
2194 l_Msg_Count := Fnd_Msg_Pub.Count_Msg;
2195
2196
2197 If l_Msg_Count = 1 Then
2198
2199 Pa_Interface_Utils_Pub.Get_Messages(
2200 P_Encoded => Fnd_Api.G_False,
2201 P_Msg_Index => 1,
2202 P_Msg_Count => l_Msg_Count,
2203 P_Msg_Data => l_Msg_Data,
2204 P_Data => l_Data,
2205 P_Msg_Index_Out => l_Msg_Index_Out);
2206
2207 X_Msg_Data := l_Data;
2208 X_Msg_Count := l_Msg_Count;
2209
2210 Else
2211
2212 X_Msg_Count := l_Msg_Count;
2213
2214 End If;
2215 X_Return_Status := Fnd_Api.G_Ret_Sts_Error;
2216 Rollback;
2217 When Others Then
2218 x_return_status := Fnd_Api.G_Ret_Sts_UnExp_Error;
2219 -- 4537865
2220 X_Msg_Data := SUBSTRB(SQLERRM,1,240);
2221 X_Msg_Count := X_Msg_Count + 1;
2222 -- 4537865 : End
2223 Rollback ;
2224 Fnd_Msg_Pub.Add_Exc_Msg(
2225 p_pkg_name => G_PKG_NAME ,
2226 p_procedure_name => l_api_name);
2227 Return;
2228
2229 End Update_Resource_List;
2230
2231 /***********************************************************
2232 * Procedure : Delete_Resource_List
2233 * Description : AMG API, used to Delete a resource list
2234 * and its corresponding members and formats.
2235 * The detailed information is in the sepc.
2236 *********************************************************/
2237 PROCEDURE Delete_Resource_List(
2238 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2239 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2240 p_api_version_number IN NUMBER,
2241 P_Res_List_Id IN NUMBER ,
2242 X_Return_Status OUT NOCOPY VARCHAR2,
2243 X_Msg_Count OUT NOCOPY NUMBER,
2244 X_Msg_Data OUT NOCOPY VARCHAR2)
2245 IS
2246 l_api_version_number CONSTANT NUMBER := G_API_VERSION_NUMBER;
2247 l_api_name CONSTANT VARCHAR2(30) := 'Update_Resource_List';
2248 l_return_status Varchar2(30);
2249 l_msg_count Number := 0;
2250 l_msg_data Varchar2(100);
2251 l_check_exists Varchar2(1);
2252 l_exist_res_list Varchar2(1);
2253 l_err_code Number;
2254 l_res_list_member_id_tbl SYSTEM.PA_NUM_TBL_TYPE :=
2255 SYSTEM.PA_NUM_TBL_TYPE();
2256 l_res_format_id_tbl SYSTEM.PA_NUM_TBL_TYPE :=
2257 SYSTEM.PA_NUM_TBL_TYPE();
2258 l_Plan_rl_format_id_tbl SYSTEM.PA_NUM_TBL_TYPE :=
2259 SYSTEM.PA_NUM_TBL_TYPE();
2260 API_ERROR Exception;
2261 BEGIN
2262 Pa_Planning_Resource_Pvt.g_amg_flow := 'Y';
2263 --Initialize the message stack before starting any further processing.
2264 IF FND_API.to_boolean( p_init_msg_list )
2265 THEN
2266 FND_MSG_PUB.initialize;
2267 END IF;
2268
2269 --Initialize the Out Variables.
2270 x_return_status := FND_API.G_RET_STS_SUCCESS;
2271 x_msg_count := 0;
2272
2273 --Set a Savepoint so that if error occurs at any stage we can
2274 -- rollback all the changes.
2275 --SAVEPOINT Delete_Resource_List_Pub;
2276
2277 --Check for the Compatibility of the API Version
2278 --This is a must for AMG API's
2279 --Doubt -- does this have to be done for all the api's/the main one??
2280 IF NOT FND_API.Compatible_API_Call
2281 ( l_api_version_number ,
2282 p_api_version_number ,
2283 l_api_name ,
2284 G_PKG_NAME )
2285 THEN
2286 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2287 END IF;
2288
2289 /* Bug 5490759 - first check whether the list is used so that that
2290 * error message can be shown first, instead of a cryptic downstream
2291 * error when trying to delete a format */
2292 l_err_code := 0;
2293
2294 PA_GET_RESOURCE.delete_resource_list_ok(
2295 p_res_list_id, 'Y', l_err_code, l_msg_data);
2296 IF l_err_code <> 0 THEN
2297 l_return_status := FND_API.G_RET_STS_ERROR;
2298 l_msg_count := l_msg_count + 1;
2299 RAISE API_ERROR;
2300 END IF;
2301
2302 /**************************************************
2303 * Call to the Delete_Planning_Resources procedure
2304 * to Delete all the members from the pa_resource_list_members
2305 * table. We are passing a table of resource_list_member_id's
2306 * to the procedure.
2307 **************************************************/
2308 /*****************************************************
2309 * First Retrieve all the resource list member id's
2310 * belonging to the resource_list_id passed.
2311 * Bulk collect into a PL/SQL table l_res_list_member_id_tbl.
2312 * ******************************************************/
2313 BEGIN
2314 SELECT resource_list_member_id
2315 BULK COLLECT INTO l_res_list_member_id_tbl
2316 FROM Pa_resource_list_members
2317 WHERE resource_list_id = p_res_list_id;
2318 EXCEPTION
2319 WHEN OTHERS THEN
2320 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2321 END;
2322
2323 IF l_res_list_member_id_tbl.COUNT > 0 THEN
2324 FOR i IN l_res_list_member_id_tbl.first.. l_res_list_member_id_tbl.last
2325 LOOP
2326 /*******************************************************
2327 * Call to API Pa_Planning_Resource_Pvt.Delete_Planning_Resource
2328 * passing the pl/sql table od resource_list_member_id's
2329 * This API would take care of deletion.
2330 ********************************************************/
2331 SELECT meaning || ' ' || to_char(i) || ':'
2332 INTO Pa_Planning_Resource_Pvt.g_token
2333 FROM pa_lookups
2334 WHERE lookup_type = 'PA_PLANNING_RESOURCE'
2335 AND lookup_code = 'PLANNING_RESOURCE';
2336
2337 Pa_Planning_Resource_Pvt.Delete_Planning_Resource(
2338 p_resource_list_member_id => l_res_list_member_id_tbl(i),
2339 x_return_status => l_return_status,
2340 x_msg_count => l_msg_count,
2341 x_error_msg_data => l_msg_data);
2342 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2343 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2344 ROLLBACK ;
2345 Return;
2346 END IF;
2347 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
2348 IF FND_API.to_boolean( p_commit )
2349 THEN
2350 COMMIT;
2351 END IF;
2352 END IF;
2353 END LOOP;
2354 END IF;
2355
2356 /*****************************************************
2357 * First Retrieve all the res_format_id's and
2358 * plan_rl_format_id's belonging to the resource_list_id
2359 * passed. Bulk collect into a PL/SQL table
2360 * l_res_format_id_tbl and l_plan_rl_format_id_tbl.
2361 * ******************************************************/
2362 BEGIN
2363 SELECT res_format_id,plan_rl_format_id
2364 BULK COLLECT INTO l_res_format_id_tbl,l_plan_rl_format_id_tbl
2365 FROM Pa_Plan_rl_formats
2366 WHERE resource_list_id = p_res_list_id;
2367 EXCEPTION
2368 WHEN OTHERS THEN
2369 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2370 END;
2371
2372 IF l_res_format_id_tbl.COUNT > 0 THEN
2373 FOR i IN l_res_format_id_tbl.first..l_res_format_id_tbl.last
2374 LOOP
2375 /**************************************************
2376 * Call to the Pa_Plan_RL_Formats_Pvt.Delete_Plan_RL_Format API
2377 * to Delete all the formats from the pa_plan_rl_formats
2378 * table. We are passing table elements
2379 **************************************************/
2380 Pa_Plan_RL_Formats_Pvt.Delete_Plan_RL_Format (
2381 P_Res_List_Id => P_res_list_id,
2382 P_Res_Format_Id => l_res_format_id_tbl(i),
2383 P_Plan_RL_Format_Id => l_plan_rl_format_id_tbl(i),
2384 X_Return_Status => l_return_status,
2385 X_Msg_Count => l_msg_count,
2386 X_Msg_Data => l_msg_data);
2387 --For bug 3810204
2388 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2389 ROLLBACK ;
2390 RAISE API_ERROR;
2391
2392 END IF;--End of bug 3810204
2393
2394 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2395 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2396 ROLLBACK ;
2397 Return;
2398 END IF;
2399
2400 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
2401 IF FND_API.to_boolean( p_commit )
2402 THEN
2403 COMMIT;
2404 END IF;
2405 END IF;
2406 END LOOP;
2407 END IF;
2408
2409 /*****************************************************
2410 * Check if the resource_list_id passed exists in the
2411 * pa_resource_list_members table or in the pa_plan_rl_formats
2412 * table. If it does then ewe cannot delete the record from
2413 * the pa_resource_lists_all_bg table, as corr child members
2414 * are present.
2415 *****************************************************/
2416 BEGIN
2417 SELECT 'Y'
2418 INTO l_exist_res_list
2419 FROM DUAL
2420 WHERE EXISTS
2421 (SELECT 'Y' from pa_resource_list_members
2422 WHERE resource_list_id = P_res_list_id
2423 UNION
2424 SELECT 'Y' from pa_plan_rl_formats
2425 WHERE resource_list_id = P_res_list_id);
2426 EXCEPTION
2427 WHEN NO_DATA_FOUND THEN
2428 l_exist_res_list := 'N';
2429 END;
2430
2431 IF l_exist_res_list = 'Y' THEN
2432 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2433 ROLLBACK ;
2434 Return;
2435 END IF;
2436
2437 IF l_exist_res_list = 'N' THEN
2438 -- Call procedure to delete from base and TL tables
2439 PA_CREATE_RESOURCE.Delete_Plan_Res_List (
2440 p_resource_list_id => P_res_list_id,
2441 X_Return_Status => l_return_status,
2442 X_Msg_Count => l_msg_count,
2443 X_Msg_Data => l_msg_data);
2444
2445 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2446 --For bug 3810204
2447 ROLLBACK ;
2448 RAISE API_ERROR;
2449 --End of bug 3810204
2450 END IF;
2451
2452 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2453 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2454 ROLLBACK ;
2455 Return;
2456 END IF;
2457
2458 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
2459 IF FND_API.to_boolean( p_commit ) THEN
2460 COMMIT;
2461 END IF;
2462 END IF;
2463 END IF;
2464
2465
2466 EXCEPTION
2467 WHEN API_ERROR THEN
2468 x_return_status := l_return_status;
2469 X_Msg_Data := l_msg_data;
2470 x_msg_count := l_msg_count;
2471 Pa_Utils.Add_Message
2472 (P_App_Short_Name => 'PA',
2473 P_Msg_Name => l_msg_data);
2474
2475 WHEN OTHERS THEN
2476 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2477 -- 4537865 : Start
2478 X_Msg_Data := SUBSTRB(SQLERRM,1,240);
2479 x_msg_count := x_msg_count + 1 ;
2480 -- 4537865 : End
2481 ROLLBACK ;
2482 FND_MSG_PUB.Add_Exc_Msg
2483 ( p_pkg_name => G_PKG_NAME ,
2484 p_procedure_name => l_api_name
2485 );
2486 Return;
2487
2488 END Delete_Resource_List;
2489
2490
2491 /************************************************************
2492 * Procedure : Create_Plan_RL_Format
2493 * Description : This procedure is used the pass a Table of
2494 * Record, and call the
2495 * Pa_Plan_RL_Formats_Pvt.Create_Plan_RL_Format
2496 * procedure, which would create the res formats.
2497 * The detailed information is in the spec.
2498 **************************************************************/
2499 Procedure Create_Plan_RL_Format(
2500 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
2501 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
2502 P_Res_List_Id IN Number,
2503 P_Plan_RL_Format_Tbl IN Plan_RL_Format_In_Tbl,
2504 X_Plan_RL_Format_Tbl OUT NOCOPY Plan_RL_Format_Out_Tbl,
2505 X_Return_Status OUT NOCOPY Varchar2,
2506 X_Msg_Count OUT NOCOPY Number,
2507 X_Msg_Data OUT NOCOPY Varchar2)
2508 IS
2509
2510 L_Plan_RL_Format_Tbl Plan_RL_Format_In_Tbl;
2511 INVALID_FMT_ERR Exception;
2512 l_format_exists VARCHAR2(1);
2513
2514 BEGIN
2515
2516 -- First clear the message stack.
2517 IF FND_API.to_boolean( p_init_msg_list )
2518 THEN
2519 FND_MSG_PUB.initialize;
2520 END IF;
2521
2522
2523
2524 x_msg_count := 0;
2525 x_return_status := FND_API.G_RET_STS_SUCCESS;
2526
2527 Convert_Missing_Format_IN_Rec(
2528 P_Plan_RL_Format_Tbl => P_Plan_RL_Format_Tbl,
2529 X_Plan_RL_Format_Tbl => L_Plan_RL_Format_Tbl);
2530
2531 /***************************************************************
2532 * For Loop. To loop through the table of records and
2533 * Validate each one of them and insert accordingly.
2534 ****************************************************************/
2535 FOR i IN 1..P_Plan_RL_Format_Tbl.COUNT
2536 LOOP
2537
2538 --For bug 3675288.
2539 BEGIN
2540
2541 l_format_exists:='N';
2542
2543 --Checks if the format is valid one or not.
2544 -- If 'Y' Then format is valid.
2545
2546 Select 'Y'
2547 Into l_format_exists
2548 From pa_res_formats_b
2549 Where res_format_id=L_Plan_RL_Format_Tbl(i).P_Res_Format_Id;
2550
2551 EXCEPTION
2552 WHEN Others THEN
2553 l_format_exists:='N';
2554
2555 END;
2556
2557 If l_format_exists = 'N' Then
2558
2559 Raise INVALID_FMT_ERR;
2560
2561 End If;
2562 --End of bug 3675288.
2563
2564 Pa_Plan_RL_Formats_Pvt.Create_Plan_RL_Format(
2565 P_Res_List_Id =>P_Res_List_Id,
2566 P_Res_Format_Id =>L_Plan_RL_Format_Tbl(i).P_Res_Format_Id,
2567 X_Plan_RL_Format_Id =>X_Plan_RL_Format_Tbl(i).X_Plan_RL_Format_Id,
2568 X_Record_Version_Number =>
2569 X_Plan_RL_Format_Tbl(i).X_Record_Version_Number,
2570 X_Return_Status =>x_return_status,
2571 X_Msg_Count =>X_Msg_Count,
2572 X_Msg_Data =>X_Msg_Data);
2573
2574 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
2575 THEN
2576 x_return_status := FND_API.G_RET_STS_ERROR;
2577 x_msg_count := x_msg_count + 1;
2578 x_msg_data := x_Msg_Data;
2579 Rollback;
2580 Return;
2581 END IF;
2582 END LOOP;
2583 /************************************************
2584 * Check the Commit flag. if it is true then Commit.
2585 ************************************************/
2586 IF FND_API.to_boolean( p_commit )
2587 THEN
2588 COMMIT;
2589 END IF;
2590 EXCEPTION
2591 WHEN INVALID_FMT_ERR THEN
2592
2593 x_return_status := FND_API.G_RET_STS_ERROR;
2594 x_msg_count := x_msg_count + 1;
2595 x_msg_data := 'PA_AMG_INVALID_FMT_ID';
2596 Pa_Utils.Add_Message
2597 (P_App_Short_Name => 'PA',
2598 P_Msg_Name => 'PA_AMG_INVALID_FMT_ID');
2599
2600
2601
2602 WHEN OTHERS THEN
2603 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2604 x_msg_count := x_msg_count + 1;
2605 -- 4537865
2606 x_msg_data := SUBSTRB(SQLERRM,1,240);
2607 Fnd_Msg_Pub.Add_Exc_Msg(
2608 P_Pkg_Name => 'Pa_Plan_Res_List_Pub',
2609 P_Procedure_Name => 'Create_Plan_RL_Format',
2610 P_error_text => x_msg_data);
2611 END Create_Plan_RL_Format;
2612 /***************************/
2613
2614 /************************************************************
2615 * Procedure : Delete_Plan_RL_Format
2616 * Description : This procedure is used the pass a Table of
2617 * Record, and call the
2618 * Pa_Plan_RL_Formats_Pvt.Delete_Plan_RL_Format
2619 * procedure, which would Delete the res formats.
2620 * The detailed information is in the spec.
2621 **************************************************************/
2622 Procedure Delete_Plan_RL_Format (
2623 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
2624 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
2625 P_Res_List_Id IN NUMBER DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
2626 P_Plan_RL_Format_Tbl IN Plan_RL_Format_In_Tbl ,
2627 X_Return_Status OUT NOCOPY VARCHAR2,
2628 X_Msg_Count OUT NOCOPY NUMBER,
2629 X_Msg_Data OUT NOCOPY VARCHAR2)
2630 IS
2631
2632 L_Plan_RL_Format_Tbl Plan_RL_Format_In_Tbl;
2633
2634 BEGIN
2635 -- First clear the message stack.
2636 IF FND_API.to_boolean( p_init_msg_list )
2637 THEN
2638 FND_MSG_PUB.initialize;
2639 END IF;
2640
2641 Convert_Missing_Format_IN_Rec(
2642 P_Plan_RL_Format_Tbl => P_Plan_RL_Format_Tbl,
2643 X_Plan_RL_Format_Tbl => L_Plan_RL_Format_Tbl);
2644
2645 /***************************************************************
2646 * For Loop. To loop through the table of records and
2647 * Validate each one of them and Update accordingly.
2648 ****************************************************************/
2649 FOR i IN 1..P_Plan_RL_Format_Tbl.COUNT
2650 LOOP
2651
2652 Pa_Plan_RL_Formats_pvt.Delete_Plan_RL_Format (
2653 P_Res_List_Id =>P_Res_List_Id,
2654 P_Res_Format_Id =>L_Plan_RL_Format_Tbl(i).P_Res_Format_Id,
2655 P_Plan_RL_Format_Id =>Null,
2656 X_Return_Status =>X_Return_Status,
2657 X_Msg_Count =>X_Msg_Count,
2658 X_Msg_Data =>X_Msg_Data);
2659
2660 END LOOP;
2661 /************************************************
2662 * Check the Commit flag. if it is true then Commit.
2663 ************************************************/
2664 IF FND_API.to_boolean( p_commit )
2665 THEN
2666 COMMIT;
2667 END IF;
2668 END Delete_Plan_RL_Format;
2669 /***************************/
2670
2671 /**************************************************************
2672 * Procedure : Create_Planning_Resource
2673 * Description : The purpose of this procedure is to Validate
2674 * and create a new planning resource for a
2675 * resource list.
2676 * The detailed information is in the spec.
2677 ****************************************************************/
2678 Procedure Create_Planning_Resource(
2679 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
2680 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
2681 p_resource_list_id IN VARCHAR2,
2682 P_planning_resource_in_tbl IN Planning_Resource_In_Tbl,
2683 X_planning_resource_out_tbl OUT NOCOPY Planning_Resource_Out_Tbl,
2684 x_return_status OUT NOCOPY VARCHAR2,
2685 x_msg_count OUT NOCOPY NUMBER,
2686 x_error_msg_data OUT NOCOPY VARCHAR2 )
2687 Is
2688
2689 l_res_class_flag Varchar2(1) := Null;
2690 L_Planning_resource_in_tbl Planning_Resource_In_Tbl;
2691 l_mode Number :=0;
2692 l_resource Varchar2(100);
2693 l_financial Varchar2(100);
2694 l_organization Varchar2(100);
2695 l_supplier Varchar2(100);
2696 l_role Varchar2(100);
2697 l_incurred Varchar2(100);
2698 FMT_NULL_ERR Exception;
2699 RESOURCE_CLASS_FMT_ERR Exception;
2700 RES_CLASS_ID_AND_CODE_NULL Exception;
2701 FIN_NULL_ERR Exception;
2702 FIN_NOT_NULL_ERR Exception;
2703 BOM_LAB_N_EQUIP_NULL_ERR Exception;
2704 NAMED_PER_NULL_ERR Exception;
2705 ITEM_CAT_NULL_ERR Exception;
2706 INVEN_ITEM_NULL_ERR Exception;
2707 JOB_NULL_ERR Exception;
2708 PERSON_TYPE_NULL_ERR Exception;
2709 NON_LABOR_RES_NULL_ERR Exception;
2710 ORG_NULL_ERR Exception;
2711 ORG_NOT_NULL_ERR Exception;
2712 SUPP_NULL_ERR Exception;
2713 SUPP_NOT_NULL_ERR Exception;
2714 ROLE_NULL_ERR Exception;
2715 ROLE_NOT_NULL_ERR Exception;
2716 INC_NULL_ERR Exception;
2717 INC_NOT_NULL_ERR Exception;
2718 RES_NOT_NULL_ERR Exception;
2719 TOO_MANY_PMT_FOR_INCUR Exception;
2720 RES_CODE_INVALID_ERR Exception;
2721 RES_ID_INVALID_ERR Exception;
2722 INVALID_PROJECT_ID Exception;
2723 RES_FORMAT_ID_ERR Exception;
2724 l_project_exists Varchar2(1);
2725 l_format_exists Varchar2(1);
2726 l_validate_resource_id Number;
2727 l_validate_resource_code Number;
2728 P_Plan_res_list_Rec Plan_Res_List_IN_Rec;
2729
2730 l_count Number;
2731 Begin
2732
2733 Pa_Planning_Resource_Pvt.g_amg_flow := 'Y';
2734 -- First clear the message stack.
2735 If Fnd_Api.To_Boolean( P_Init_Msg_List ) Then
2736
2737 Fnd_Msg_Pub.Initialize;
2738
2739 End If;
2740
2741 /* Added for CBS changes */
2742 IF PA_FP_GEN_AMOUNT_UTILS.get_trg_resource_class_flag(p_resource_list_id) = 'N' THEN
2743
2744 x_return_status := FND_API.G_RET_STS_ERROR;
2745 x_msg_count := x_msg_count + 1;
2746 x_error_msg_data := 'PA_CNT_USE_CRT_AMG_FR_NON_ENHCD_PRL';
2747 Pa_Utils.Add_Message
2748 (P_App_Short_Name => 'PA',
2749 P_Msg_Name => 'PA_CNT_USE_CRT_AMG_FR_NON_ENHCD_PRL');
2750 RETURN;
2751 END IF;
2752 /* End of CBS changes */
2753
2754 Convert_Missing_Member_IN_Rec(
2755 P_Planning_resource_in_tbl => P_Planning_resource_in_tbl,
2756 P_Plan_res_list_Rec => P_Plan_res_list_Rec,
2757 X_Planning_resource_in_tbl => L_Planning_resource_in_tbl,
2758 P_Mode => l_mode);
2759
2760
2761 /***************************************************************
2762 * For Loop. To loop through the table of records and
2763 * Validate each one of them and insert accordingly.
2764 **************************************************************/
2765 For i IN 1..L_Planning_Resource_In_Tbl.Count
2766 Loop
2767
2768 SELECT meaning || ' ' || to_char(i) || ':'
2769 INTO Pa_Planning_Resource_Pvt.g_token
2770 FROM pa_lookups
2771 WHERE lookup_type = 'PA_PLANNING_RESOURCE'
2772 AND lookup_code = 'PLANNING_RESOURCE';
2773
2774 savepoint planning_resource_create;
2775
2776 /*************************************************
2777 * Assigning Initial values for some of the elements.
2778 *************************************************/
2779 x_msg_count := 0;
2780 x_return_status := FND_API.G_RET_STS_SUCCESS;
2781
2782 --For bug 3675288.
2783 IF L_Planning_resource_in_tbl(i).P_Res_Format_Id is NULL THEN
2784
2785 RAISE FMT_NULL_ERR;
2786
2787 END IF;
2788
2789 IF L_Planning_resource_in_tbl(i).P_Resource_Class_Code is NULL THEN
2790 IF L_Planning_resource_in_tbl(i).P_Resource_Class_id is NULL THEN
2791
2792 RAISE RES_CLASS_ID_AND_CODE_NULL;
2793
2794 END IF;
2795 END IF;
2796
2797 --For bug 3810000
2798 IF L_Planning_resource_in_tbl(i).P_Resource_Class_Code is not NULL Then
2799 select count(*) INTO l_validate_resource_code
2800 FROM pa_resource_classes_b
2801 WHERE resource_class_code=L_Planning_resource_in_tbl(i).P_Resource_Class_Code;
2802 IF l_validate_resource_code <> 0 Then
2803 Null;
2804 Else
2805 RAISE RES_CODE_INVALID_ERR;
2806 END IF;
2807 END IF;
2808
2809 IF L_Planning_resource_in_tbl(i).P_Resource_Class_id is not NULL Then
2810 select count(*) INTO l_validate_resource_id
2811 FROM pa_resource_classes_b
2812 WHERE resource_class_id=L_Planning_resource_in_tbl(i).P_Resource_Class_id;
2813 IF l_validate_resource_id <> 0 Then
2814 Null;
2815 Else
2816 RAISE RES_ID_INVALID_ERR;
2817 END IF;
2818 END IF;
2819 --End of bug 3810000
2820
2821 BEGIN
2822
2823 SELECT decode(f.RES_TYPE_ENABLED_FLAG, 'Y', t.res_type_code, NULL) ,
2824 decode(f.FIN_CAT_ENABLED_FLAG, 'Y', 'Financial Category', NULL) ,
2825 decode(f.ORGN_ENABLED_FLAG, 'Y', 'Organization', NULL) ,
2826 decode(f.SUPPLIER_ENABLED_FLAG, 'Y', 'Supplier', NULL) ,
2827 decode(f.ROLE_ENABLED_FLAG, 'Y', 'Role', NULL) ,
2828 decode(f.INCURRED_BY_ENABLED_FLAG, 'Y', 'Incurred By', NULL)
2829 INTO
2830 l_resource,
2831 l_financial,
2832 l_organization,
2833 l_supplier,
2834 l_role,
2835 l_incurred
2836 FROM pa_res_formats_b f, pa_res_types_b t
2837 WHERE f.res_format_id = L_Planning_resource_in_tbl(i).P_Res_Format_Id
2838 AND f.RES_TYPE_ID = t.res_type_id(+);
2839
2840 EXCEPTION WHEN OTHERS THEN
2841 x_return_status := Fnd_Api.G_Ret_Sts_Error;
2842 x_msg_count:=1;
2843 x_error_msg_data := 'PA_PLN_RL_FORMAT_BAD_FMT_ID';
2844 Pa_Utils.Add_Message
2845 (P_App_Short_Name => 'PA',
2846 P_Msg_Name => 'PA_PLN_RL_FORMAT_BAD_FMT_ID',
2847 p_token1 => 'PLAN_RES',
2848 p_value1 => Pa_Planning_Resource_Pvt.g_token);
2849 RETURN;
2850 END;
2851
2852 IF (L_planning_resource_in_tbl(i).p_res_format_id is not null) Then --For bug 4103909.
2853
2854 BEGIN
2855
2856 SELECT 'Y'
2857 INTO l_format_exists
2858 FROM pa_plan_rl_formats
2859 WHERE resource_list_id = p_resource_list_id
2860 AND res_format_id = L_planning_resource_in_tbl(i).p_res_format_id;
2861
2862 EXCEPTION
2863 WHEN NO_DATA_FOUND THEN
2864
2865 Raise RES_FORMAT_ID_ERR;
2866
2867 END;
2868
2869 END IF;
2870 --End of bug 4103909.
2871
2872
2873 IF l_resource is not null THEN
2874
2875 IF l_resource IN ('BOM_LABOR' , 'BOM_EQUIPMENT') THEN
2876 If L_Planning_resource_in_tbl(i).p_bom_resource_id is Null Then
2877 IF L_Planning_resource_in_tbl(i).p_bom_resource_name is Null Then
2878 Raise BOM_LAB_N_EQUIP_NULL_ERR;
2879 END IF;
2880 END IF;
2881 END IF;
2882
2883 IF l_resource = 'NAMED_PERSON' THEN
2884 If L_Planning_resource_in_tbl(i).p_person_id is Null Then
2885 IF L_Planning_resource_in_tbl(i).p_person_name is NULL THEN
2886 Raise NAMED_PER_NULL_ERR;
2887 End IF;
2888 End IF;
2889 End IF;
2890
2891
2892 If l_resource = 'ITEM_CATEGORY' THEN
2893 If L_Planning_resource_in_tbl(i).p_item_category_id is NULL THEN
2894 IF L_Planning_resource_in_tbl(i).p_item_category_name is NULL THEN
2895 Raise ITEM_CAT_NULL_ERR;
2896 END IF;
2897 END IF;
2898 END IF;
2899
2900 If l_resource = 'INVENTORY_ITEM' Then
2901 If L_Planning_resource_in_tbl(i).p_inventory_item_id is NULL THEN
2902 IF L_Planning_resource_in_tbl(i).p_inventory_item_name is NULL THEN
2903 Raise INVEN_ITEM_NULL_ERR;
2904 END IF;
2905 END IF;
2906 END IF;
2907
2908 If l_resource = 'JOB' THEN
2909 If L_Planning_resource_in_tbl(i).p_job_id is NULL THEN
2910 IF L_Planning_resource_in_tbl(i).p_job_name is NULL THEN
2911 Raise JOB_NULL_ERR;
2912 END IF;
2913 END IF;
2914 END IF;
2915
2916 If l_resource = 'PERSON_TYPE' THEN
2917 If L_Planning_resource_in_tbl(i).p_person_type_code is NULL THEN
2918 Raise PERSON_TYPE_NULL_ERR;
2919 END IF;
2920 END IF;
2921
2922 If l_resource = 'NON_LABOR_RESOURCE' THEN
2923 If L_Planning_resource_in_tbl(i).p_non_labor_resource is NULL THEN
2924 Raise NON_LABOR_RES_NULL_ERR;
2925 END IF;
2926 END IF;
2927
2928 If l_resource = 'RESOURCE_CLASS' THEN
2929 Raise RESOURCE_CLASS_FMT_ERR;
2930 END IF;
2931 ELSE -- If no Resource segment, make sure no value is passed in
2932 IF (L_Planning_resource_in_tbl(i).p_bom_resource_id is NOT Null OR
2933 L_Planning_resource_in_tbl(i).p_bom_resource_name is NOT Null OR
2934 L_Planning_resource_in_tbl(i).p_non_labor_resource is NOT Null OR
2935 L_Planning_resource_in_tbl(i).p_item_category_id is NOT Null OR
2936 L_Planning_resource_in_tbl(i).p_item_category_name is NOT Null OR
2937 L_Planning_resource_in_tbl(i).p_inventory_item_id is NOT Null OR
2938 L_Planning_resource_in_tbl(i).p_inventory_item_name is NOT Null OR
2939 L_Planning_resource_in_tbl(i).p_person_id is NOT Null OR
2940 L_Planning_resource_in_tbl(i).p_person_name is NOT Null OR
2941 L_Planning_resource_in_tbl(i).p_job_id is NOT Null OR
2942 L_Planning_resource_in_tbl(i).p_job_name is NOT Null OR
2943 L_Planning_resource_in_tbl(i).p_person_type_code is NOT Null)
2944 THEN
2945 Raise RES_NOT_NULL_ERR;
2946 END IF;
2947 END IF;
2948
2949 -- Check Incurred By
2950 IF l_incurred is not NULL THEN
2951 IF (L_Planning_resource_in_tbl(i).p_incur_by_res_code is Null OR
2952 L_Planning_resource_in_tbl(i).p_incur_by_res_type is Null) THEN
2953 Raise INC_NULL_ERR;
2954 END IF;
2955 ELSE -- If no Inc By segment, make sure no value is passed in
2956 IF (L_Planning_resource_in_tbl(i).p_incur_by_res_code is NOT Null OR
2957 L_Planning_resource_in_tbl(i).p_incur_by_res_type is NOT Null)
2958 THEN
2959 Raise INC_NOT_NULL_ERR;
2960 END IF;
2961 END IF;
2962
2963 IF l_financial is not null THEN
2964 IF (L_Planning_resource_in_tbl(i).p_fc_res_type_code is Null OR
2965 L_Planning_resource_in_tbl(i).p_fin_category_name is Null) Then
2966 Raise FIN_NULL_ERR;
2967 END IF;
2968 ELSE -- If no Fin Cat segment, make sure no value is passed in
2969 IF (L_Planning_resource_in_tbl(i).p_fc_res_type_code is NOT Null OR
2970 L_Planning_resource_in_tbl(i).p_fin_category_name is NOT Null)
2971 THEN
2972 Raise FIN_NOT_NULL_ERR;
2973 END IF;
2974 END IF;
2975
2976 IF l_organization is not null THEN
2977 IF L_Planning_resource_in_tbl(i).p_organization_id is Null Then
2978 IF L_Planning_resource_in_tbl(i).p_organization_name is Null Then
2979 Raise ORG_NULL_ERR;
2980 End IF;
2981 END IF;
2982 ELSE -- If no Org segment, make sure no value is passed in
2983 IF (L_Planning_resource_in_tbl(i).p_organization_id is NOT Null OR
2984 L_Planning_resource_in_tbl(i).p_organization_name is NOT Null)
2985 THEN
2986 Raise ORG_NOT_NULL_ERR;
2987 END IF;
2988 END IF;
2989
2990 IF l_supplier is not null THEN
2991 IF L_Planning_resource_in_tbl(i).p_vendor_id is null THEN
2992 IF L_Planning_resource_in_tbl(i).p_vendor_name is null Then
2993 Raise SUPP_NULL_ERR;
2994 End If;
2995 END IF;
2996 ELSE -- If no Supplier segment, make sure no value is passed in
2997 IF (L_Planning_resource_in_tbl(i).p_vendor_id is NOT Null OR
2998 L_Planning_resource_in_tbl(i).p_vendor_name is NOT Null)
2999 THEN
3000 Raise SUPP_NOT_NULL_ERR;
3001 END IF;
3002 END IF;
3003
3004 IF l_role is not null THEN
3005 IF L_Planning_resource_in_tbl(i).p_project_role_id is NULL THEN
3006 IF L_Planning_resource_in_tbl(i).p_project_role_name is Null THEN
3007 Raise ROLE_NULL_ERR;
3008 END IF;
3009 END IF;
3010 ELSE -- If no Role segment, make sure no value is passed in
3011 IF (L_Planning_resource_in_tbl(i).p_project_role_id is NOT Null OR
3012 L_Planning_resource_in_tbl(i).p_project_role_name is NOT Null OR
3013 L_Planning_resource_in_tbl(i).p_team_role is NOT Null)
3014 THEN
3015 Raise ROLE_NOT_NULL_ERR;
3016 END IF;
3017 END IF;
3018 --End of bug 3675288.
3019
3020 --For bug 4055082.
3021 --Raise error if invalid project id is passed while creation of planning resource list memeber.
3022
3023 IF (L_planning_resource_in_tbl(i).p_project_id is not null) Then --For bug 4094047
3024
3025 BEGIN
3026
3027 SELECT 'Y'
3028 INTO l_project_exists
3029 FROM pa_projects_all
3030 WHERE project_id = L_planning_resource_in_tbl(i).p_project_id;
3031
3032 EXCEPTION
3033 WHEN NO_DATA_FOUND THEN
3034 RAISE INVALID_PROJECT_ID;
3035
3036 END;
3037
3038 END IF;
3039 --End of bug 4055082.
3040
3041 /******************************************************
3042 * Call to pa_planning_resource_pvt.create_planning_resource
3043 * which would take care of the validation and creation
3044 * of the resource list members. The table elements are being passed as
3045 * parameters.
3046 ******************************************************/
3047
3048
3049 -- derive resource_class_flag
3050 Select resource_class_flag
3051 Into l_res_class_flag
3052 From pa_res_formats_b
3053 Where res_format_id = L_planning_resource_in_tbl(i).p_res_format_id;
3054
3055 Pa_Planning_Resource_Pvt.Create_Planning_Resource(
3056 p_resource_list_id => p_resource_list_id,
3057 p_resource_list_member_id => L_planning_resource_in_tbl(i).p_resource_list_member_id,
3058 p_resource_alias => L_planning_resource_in_tbl(i).p_resource_alias,
3059 p_person_id => L_planning_resource_in_tbl(i).p_person_id,
3060 p_person_name => L_planning_resource_in_tbl(i).p_person_name,
3061 p_job_id => L_planning_resource_in_tbl(i).p_job_id,
3062 p_job_name => L_planning_resource_in_tbl(i).p_job_name,
3063 p_organization_id => L_planning_resource_in_tbl(i).p_organization_id,
3064 p_organization_name => L_planning_resource_in_tbl(i).p_organization_name,
3065 p_vendor_id => L_planning_resource_in_tbl(i).p_vendor_id,
3066 p_vendor_name => L_planning_resource_in_tbl(i).p_vendor_name,
3067 p_fin_category_name => L_planning_resource_in_tbl(i).p_fin_category_name,
3068 p_non_labor_resource => L_planning_resource_in_tbl(i).p_non_labor_resource,
3069 p_project_role_id => L_planning_resource_in_tbl(i).p_project_role_id,
3070 p_project_role_name => L_planning_resource_in_tbl(i).p_project_role_name,
3071 p_resource_class_id => L_planning_resource_in_tbl(i).p_resource_class_id,
3072 p_resource_class_code => L_planning_resource_in_tbl(i).p_resource_class_code,
3073 p_res_format_id => L_planning_resource_in_tbl(i).p_res_format_id,
3074 p_spread_curve_id => L_planning_resource_in_tbl(i).p_spread_curve_id,
3075 p_etc_method_code => L_planning_resource_in_tbl(i).p_etc_method_code,
3076 p_mfc_cost_type_id => L_planning_resource_in_tbl(i).p_mfc_cost_type_id,
3077 p_copy_from_rl_flag => Null,
3078 p_resource_class_flag => l_res_class_flag,
3079 p_fc_res_type_code => L_planning_resource_in_tbl(i).p_fc_res_type_code,
3080 p_inventory_item_id => L_planning_resource_in_tbl(i).p_inventory_item_id,
3081 p_inventory_item_name => L_planning_resource_in_tbl(i).p_inventory_item_name,
3082 p_item_category_id => L_planning_resource_in_tbl(i).p_item_category_id,
3083 p_item_category_name => L_planning_resource_in_tbl(i).p_item_category_name,
3084 p_migration_code => 'N',
3085 p_attribute_category => L_planning_resource_in_tbl(i).p_attribute_category,
3086 p_attribute1 => L_planning_resource_in_tbl(i).p_attribute1,
3087 p_attribute2 => L_planning_resource_in_tbl(i).p_attribute2,
3088 p_attribute3 => L_planning_resource_in_tbl(i).p_attribute3,
3089 p_attribute4 => L_planning_resource_in_tbl(i).p_attribute4,
3090 p_attribute5 => L_planning_resource_in_tbl(i).p_attribute5,
3091 p_attribute6 => L_planning_resource_in_tbl(i).p_attribute6,
3092 p_attribute7 => L_planning_resource_in_tbl(i).p_attribute7,
3093 p_attribute8 => L_planning_resource_in_tbl(i).p_attribute8,
3094 p_attribute9 => L_planning_resource_in_tbl(i).p_attribute9,
3095 p_attribute10 => L_planning_resource_in_tbl(i).p_attribute10,
3096 p_attribute11 => L_planning_resource_in_tbl(i).p_attribute11,
3097 p_attribute12 => L_planning_resource_in_tbl(i).p_attribute12,
3098 p_attribute13 => L_planning_resource_in_tbl(i).p_attribute13,
3099 p_attribute14 => L_planning_resource_in_tbl(i).p_attribute14,
3100 p_attribute15 => L_planning_resource_in_tbl(i).p_attribute15,
3101 p_attribute16 => L_planning_resource_in_tbl(i).p_attribute16,
3102 p_attribute17 => L_planning_resource_in_tbl(i).p_attribute17,
3103 p_attribute18 => L_planning_resource_in_tbl(i).p_attribute18,
3104 p_attribute19 => L_planning_resource_in_tbl(i).p_attribute19,
3105 p_attribute20 => L_planning_resource_in_tbl(i).p_attribute20,
3106 p_attribute21 => L_planning_resource_in_tbl(i).p_attribute21,
3107 p_attribute22 => L_planning_resource_in_tbl(i).p_attribute22,
3108 p_attribute23 => L_planning_resource_in_tbl(i).p_attribute23,
3109 p_attribute24 => L_planning_resource_in_tbl(i).p_attribute24,
3110 p_attribute25 => L_planning_resource_in_tbl(i).p_attribute25,
3111 p_attribute26 => L_planning_resource_in_tbl(i).p_attribute26,
3112 p_attribute27 => L_planning_resource_in_tbl(i).p_attribute27,
3113 p_attribute28 => L_planning_resource_in_tbl(i).p_attribute28,
3114 p_attribute29 => L_planning_resource_in_tbl(i).p_attribute29,
3115 p_attribute30 => L_planning_resource_in_tbl(i).p_attribute30,
3116 p_person_type_code => L_planning_resource_in_tbl(i).p_person_type_code,
3117 p_bom_resource_id => L_planning_resource_in_tbl(i).p_bom_resource_id,
3118 p_bom_resource_name => L_planning_resource_in_tbl(i).p_bom_resource_name,
3119 p_team_role => L_planning_resource_in_tbl(i).p_team_role,
3120 p_incur_by_res_code => L_planning_resource_in_tbl(i).p_incur_by_res_code,
3121 p_incur_by_res_type => L_planning_resource_in_tbl(i).p_incur_by_res_type,
3122 p_project_id => L_planning_resource_in_tbl(i).p_project_id,
3123 x_resource_list_member_id => x_planning_resource_out_tbl(i).x_resource_list_member_id,
3124 x_record_version_number => x_planning_resource_out_tbl(i).x_record_version_number,
3125 x_return_status => x_return_status,
3126 x_msg_count => x_msg_count ,
3127 x_error_msg_data => x_error_msg_data);
3128
3129
3130 If x_return_status <> Fnd_Api.G_Ret_Sts_Success Then
3131
3132 RollBack to planning_resource_create;
3133 RETURN;
3134
3135 End If;
3136 End Loop;
3137
3138 -- Commit only if all records are successful
3139 /************************************************
3140 * Check the Commit flag. if it is true then Commit.
3141 ***********************************************/
3142 If Fnd_Api.To_Boolean( P_Commit ) Then
3143
3144 Commit;
3145
3146 End If;
3147 x_return_status := Fnd_Api.G_Ret_Sts_Success;
3148 x_msg_count := Fnd_Msg_Pub.Count_Msg;
3149
3150
3151 Exception
3152 When FMT_NULL_ERR Then
3153 x_return_status := Fnd_Api.G_Ret_Sts_Error;
3154 x_msg_count:=1;
3155 x_error_msg_data := 'PA_AMG_FORMAT_ID_NULL';
3156 Pa_Utils.Add_Message
3157 (P_App_Short_Name => 'PA',
3158 P_Msg_Name => 'PA_AMG_FORMAT_ID_NULL',
3159 p_token1 => 'PLAN_RES',
3160 p_value1 => Pa_Planning_Resource_Pvt.g_token);
3161 When RES_FORMAT_ID_ERR Then
3162 x_return_status := Fnd_Api.G_Ret_Sts_Error;
3163 x_msg_count:=1;
3164 x_error_msg_data := 'PA_AMG_FORMAT_NOT_EXISTS';
3165 Pa_Utils.Add_Message(
3166 P_App_Short_Name => 'PA',
3167 P_Msg_Name => 'PA_AMG_FORMAT_NOT_EXISTS',
3168 p_token1 => 'PLAN_RES',
3169 p_value1 => Pa_Planning_Resource_Pvt.g_token);
3170 When RES_CLASS_ID_AND_CODE_NULL Then
3171 x_return_status := Fnd_Api.G_Ret_Sts_Error;
3172 x_msg_count:=1;
3173 x_error_msg_data := 'PA_AMG_RESCLS_ID_CODE_NULL';
3174 Pa_Utils.Add_Message
3175 (P_App_Short_Name => 'PA',
3176 P_Msg_Name => 'PA_AMG_RESCLS_ID_CODE_NULL',
3177 p_token1 => 'PLAN_RES',
3178 p_value1 => Pa_Planning_Resource_Pvt.g_token);
3179 When RES_CODE_INVALID_ERR Then
3180 x_return_status := Fnd_Api.G_Ret_Sts_Error;
3181 x_msg_count:=1;
3182 x_error_msg_data := 'PA_AMG_RESOURCE_CODE_INVALID';
3183 Pa_Utils.Add_Message
3184 (P_App_Short_Name => 'PA',
3185 P_Msg_Name => 'PA_AMG_RESOURCE_CODE_INVALID',
3186 p_token1 => 'PLAN_RES',
3187 p_value1 => Pa_Planning_Resource_Pvt.g_token);
3188 When RES_ID_INVALID_ERR Then
3189 x_return_status := Fnd_Api.G_Ret_Sts_Error;
3190 x_msg_count:=1;
3191 x_error_msg_data := 'PA_AMG_RESOURCE_ID_INVALID';
3192 Pa_Utils.Add_Message
3193 (P_App_Short_Name => 'PA',
3194 P_Msg_Name => 'PA_AMG_RESOURCE_ID_INVALID',
3195 p_token1 => 'PLAN_RES',
3196 p_value1 => Pa_Planning_Resource_Pvt.g_token);
3197 When INVALID_PROJECT_ID Then
3198 x_return_status := Fnd_Api.G_Ret_Sts_Error;
3199 x_msg_count:=1;
3200 x_error_msg_data := 'PA_AMG_PROJECT_ID_INVALID';
3201 Pa_Utils.Add_Message
3202 (P_App_Short_Name => 'PA',
3203 P_Msg_Name => 'PA_AMG_PROJECT_ID_INVALID',
3204 p_token1 => 'PLAN_RES',
3205 p_value1 => Pa_Planning_Resource_Pvt.g_token);
3206 When NAMED_PER_NULL_ERR Then
3207 x_return_status := Fnd_Api.G_Ret_Sts_Error;
3208 x_msg_count:=1;
3209 x_error_msg_data := 'PA_AMG_NAMED_PER_NULL';
3210 Pa_Utils.Add_Message
3211 (P_App_Short_Name => 'PA',
3212 P_Msg_Name => 'PA_AMG_NAMED_PER_NULL',
3213 p_token1 => 'PLAN_RES',
3214 p_value1 => Pa_Planning_Resource_Pvt.g_token);
3215 When BOM_LAB_N_EQUIP_NULL_ERR Then
3216 x_return_status := Fnd_Api.G_Ret_Sts_Error;
3217 x_msg_count:=1;
3218 x_error_msg_data := 'PA_AMG_BOM_NULL';
3219 Pa_Utils.Add_Message
3220 (P_App_Short_Name => 'PA',
3221 P_Msg_Name => 'PA_AMG_BOM_NULL',
3222 p_token1 => 'PLAN_RES',
3223 p_value1 => Pa_Planning_Resource_Pvt.g_token);
3224 When ITEM_CAT_NULL_ERR Then
3225 x_return_status := Fnd_Api.G_Ret_Sts_Error;
3226 x_msg_count:=1;
3227 x_error_msg_data := 'PA_AMG_ITEM_CAT_NULL';
3228 Pa_Utils.Add_Message
3229 (P_App_Short_Name => 'PA',
3230 P_Msg_Name => 'PA_AMG_ITEM_CAT_NULL',
3231 p_token1 => 'PLAN_RES',
3232 p_value1 => Pa_Planning_Resource_Pvt.g_token);
3233 When INVEN_ITEM_NULL_ERR Then
3234 x_return_status := Fnd_Api.G_Ret_Sts_Error;
3235 x_msg_count:=1;
3236 x_error_msg_data := 'PA_AMG_INV_ITEM_NULL';
3237 Pa_Utils.Add_Message
3238 (P_App_Short_Name => 'PA',
3239 P_Msg_Name => 'PA_AMG_INV_ITEM_NULL',
3240 p_token1 => 'PLAN_RES',
3241 p_value1 => Pa_Planning_Resource_Pvt.g_token);
3242 When JOB_NULL_ERR Then
3243 x_return_status := Fnd_Api.G_Ret_Sts_Error;
3244 x_msg_count:=1;
3245 x_error_msg_data := 'PA_AMG_JOB_NULL';
3246 Pa_Utils.Add_Message
3247 (P_App_Short_Name => 'PA',
3248 P_Msg_Name => 'PA_AMG_JOB_NULL',
3249 p_token1 => 'PLAN_RES',
3250 p_value1 => Pa_Planning_Resource_Pvt.g_token);
3251 When PERSON_TYPE_NULL_ERR Then
3252 x_return_status := Fnd_Api.G_Ret_Sts_Error;
3253 x_msg_count:=1;
3254 x_error_msg_data := 'PA_AMG_PERSON_TYPE_NULL';
3255 Pa_Utils.Add_Message
3256 (P_App_Short_Name => 'PA',
3257 P_Msg_Name => 'PA_AMG_PERSON_TYPE_NULL',
3258 p_token1 => 'PLAN_RES',
3259 p_value1 => Pa_Planning_Resource_Pvt.g_token);
3260 When NON_LABOR_RES_NULL_ERR Then
3261 x_return_status := Fnd_Api.G_Ret_Sts_Error;
3262 x_msg_count:=1;
3263 x_error_msg_data := 'PA_AMG_NON_LABOR_NULL';
3264 Pa_Utils.Add_Message
3265 (P_App_Short_Name => 'PA',
3266 P_Msg_Name => 'PA_AMG_NON_LABOR_NULL',
3267 p_token1 => 'PLAN_RES',
3268 p_value1 => Pa_Planning_Resource_Pvt.g_token);
3269 When FIN_NULL_ERR Then
3270 x_return_status := Fnd_Api.G_Ret_Sts_Error;
3271 x_msg_count:=1;
3272 x_error_msg_data := 'PA_AMG_FIN_NULL';
3273 Pa_Utils.Add_Message
3274 (P_App_Short_Name => 'PA',
3275 P_Msg_Name => 'PA_AMG_FIN_NULL',
3276 p_token1 => 'PLAN_RES',
3277 p_value1 => Pa_Planning_Resource_Pvt.g_token);
3278 When FIN_NOT_NULL_ERR Then
3279 x_return_status := Fnd_Api.G_Ret_Sts_Error;
3280 x_msg_count:=1;
3281 x_error_msg_data := 'PA_AMG_FIN_NOT_NULL';
3282 Pa_Utils.Add_Message
3283 (P_App_Short_Name => 'PA',
3284 P_Msg_Name => 'PA_AMG_FIN_NOT_NULL',
3285 p_token1 => 'PLAN_RES',
3286 p_value1 => Pa_Planning_Resource_Pvt.g_token);
3287 When ORG_NULL_ERR Then
3288 x_return_status := Fnd_Api.G_Ret_Sts_Error;
3289 x_msg_count:=1;
3290 x_error_msg_data := 'PA_AMG_ORG_NULL';
3291 Pa_Utils.Add_Message
3292 (P_App_Short_Name => 'PA',
3293 P_Msg_Name => 'PA_AMG_ORG_NULL',
3294 p_token1 => 'PLAN_RES',
3295 p_value1 => Pa_Planning_Resource_Pvt.g_token);
3296 When ORG_NOT_NULL_ERR Then
3297 x_return_status := Fnd_Api.G_Ret_Sts_Error;
3298 x_msg_count:=1;
3299 x_error_msg_data := 'PA_AMG_ORG_NOT_NULL';
3300 Pa_Utils.Add_Message
3301 (P_App_Short_Name => 'PA',
3302 P_Msg_Name => 'PA_AMG_ORG_NOT_NULL',
3303 p_token1 => 'PLAN_RES',
3304 p_value1 => Pa_Planning_Resource_Pvt.g_token);
3305 When SUPP_NULL_ERR Then
3306 x_return_status := Fnd_Api.G_Ret_Sts_Error;
3307 x_msg_count:=1;
3308 x_error_msg_data := 'PA_AMG_SUPP_NULL';
3309 Pa_Utils.Add_Message
3310 (P_App_Short_Name => 'PA',
3311 P_Msg_Name => 'PA_AMG_SUPP_NULL',
3312 p_token1 => 'PLAN_RES',
3313 p_value1 => Pa_Planning_Resource_Pvt.g_token);
3314 When SUPP_NOT_NULL_ERR Then
3315 x_return_status := Fnd_Api.G_Ret_Sts_Error;
3316 x_msg_count:=1;
3317 x_error_msg_data := 'PA_AMG_SUPP_NOT_NULL';
3318 Pa_Utils.Add_Message
3319 (P_App_Short_Name => 'PA',
3320 P_Msg_Name => 'PA_AMG_SUPP_NOT_NULL',
3321 p_token1 => 'PLAN_RES',
3322 p_value1 => Pa_Planning_Resource_Pvt.g_token);
3323 When ROLE_NULL_ERR Then
3324 x_return_status := Fnd_Api.G_Ret_Sts_Error;
3325 x_msg_count:=1;
3326 x_error_msg_data := 'PA_AMG_ROLE_NULL';
3327 Pa_Utils.Add_Message
3328 (P_App_Short_Name => 'PA',
3329 P_Msg_Name => 'PA_AMG_ROLE_NULL',
3330 p_token1 => 'PLAN_RES',
3331 p_value1 => Pa_Planning_Resource_Pvt.g_token);
3332 When ROLE_NOT_NULL_ERR Then
3333 x_return_status := Fnd_Api.G_Ret_Sts_Error;
3334 x_msg_count:=1;
3335 x_error_msg_data := 'PA_AMG_ROLE_NOT_NULL';
3336 Pa_Utils.Add_Message
3337 (P_App_Short_Name => 'PA',
3338 P_Msg_Name => 'PA_AMG_ROLE_NOT_NULL',
3339 p_token1 => 'PLAN_RES',
3340 p_value1 => Pa_Planning_Resource_Pvt.g_token);
3341 When INC_NULL_ERR Then
3342 x_return_status := Fnd_Api.G_Ret_Sts_Error;
3343 x_msg_count:=1;
3344 x_error_msg_data := 'PA_AMG_INC_NULL';
3345 Pa_Utils.Add_Message
3346 (P_App_Short_Name => 'PA',
3347 P_Msg_Name => 'PA_AMG_INC_NULL',
3348 p_token1 => 'PLAN_RES',
3349 p_value1 => Pa_Planning_Resource_Pvt.g_token);
3350 When INC_NOT_NULL_ERR Then
3351 x_return_status := Fnd_Api.G_Ret_Sts_Error;
3352 x_msg_count:=1;
3353 x_error_msg_data := 'PA_AMG_INC_NOT_NULL';
3354 Pa_Utils.Add_Message
3355 (P_App_Short_Name => 'PA',
3356 P_Msg_Name => 'PA_AMG_INC_NOT_NULL',
3357 p_token1 => 'PLAN_RES',
3358 p_value1 => Pa_Planning_Resource_Pvt.g_token);
3359 When RES_NOT_NULL_ERR Then
3360 x_return_status := Fnd_Api.G_Ret_Sts_Error;
3361 x_msg_count:=1;
3362 x_error_msg_data := 'PA_AMG_RES_NOT_NULL';
3363 Pa_Utils.Add_Message
3364 (P_App_Short_Name => 'PA',
3365 P_Msg_Name => 'PA_AMG_RES_NOT_NULL',
3366 p_token1 => 'PLAN_RES',
3367 p_value1 => Pa_Planning_Resource_Pvt.g_token);
3368 When RESOURCE_CLASS_FMT_ERR Then
3369 x_return_status := Fnd_Api.G_Ret_Sts_Error;
3370 x_msg_count:=1;
3371 x_error_msg_data := 'PA_AMG_RES_CLS_FMT';
3372 Pa_Utils.Add_Message
3373 (P_App_Short_Name => 'PA',
3374 P_Msg_Name => 'PA_AMG_RES_CLS_FMT',
3375 p_token1 => 'PLAN_RES',
3376 p_value1 => Pa_Planning_Resource_Pvt.g_token);
3377 When Others Then
3378 Rollback;
3379 x_return_status := Fnd_Api.G_Ret_Sts_UnExp_Error;
3380
3381 End Create_Planning_Resource;
3382 /*****************************************************/
3383
3384 /***************************************************
3385 * Procedure : Update_Planning_Resource
3386 * Description : The purpose of this procedure is to
3387 * Validate and update attributes on an existing
3388 * planning resource for a resource list.
3389 * The detailed information is in the spec.
3390 ******************************************************/
3391 PROCEDURE Update_Planning_Resource
3392 (p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
3393 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
3394 p_resource_list_id IN NUMBER,
3395 p_enabled_flag IN VARCHAR2,
3396 P_planning_resource_in_tbl IN Planning_Resource_In_Tbl,
3397 X_planning_resource_out_tbl OUT NOCOPY Planning_Resource_Out_Tbl,
3398 x_return_status OUT NOCOPY VARCHAR2,
3399 x_msg_count OUT NOCOPY NUMBER,
3400 x_error_msg_data OUT NOCOPY VARCHAR2 )
3401
3402 IS
3403
3404 -- If need to get the resource_list_member_id using the alias then
3405 -- If control_flag <> 'N' from pa_resource_lists_all_bg this means centralized and only need the
3406 -- predicates to resource_list_id and alias to get the resource_list_member_id
3407 -- If the control_flag = 'N' then we need to add predicates for object_type and object_id.
3408 -- The object_type = 'PROJECT' and the object_id is the project_id.
3409 Cursor c1(P_Alias IN Varchar2,
3410 P_Res_List_Id IN Number,
3411 P_Prj_Id IN Number) Is
3412 Select
3413 rlm.Resource_List_Member_Id
3414 From
3415 Pa_Resource_List_Members rlm,
3416 Pa_Resource_Lists_All_BG rl
3417 Where
3418 rlm.Alias = P_Alias
3419 And rlm.Resource_List_Id = P_Res_List_Id
3420 And rlm.Resource_List_Id = rl.Resource_List_Id
3421 And ( rl.Control_Flag <> 'N' Or
3422 ( rl.Control_Flag = 'N' And
3423 rlm.Object_Type = 'PROJECT' And
3424 rlm.Object_Id = P_Prj_Id) );
3425
3426 Cursor c2(P_rlm_Id IN Number) Is
3427 Select
3428 Record_Version_Number
3429 From
3430 Pa_Resource_List_Members
3431 Where
3432 Resource_List_Member_Id = P_rlm_Id;
3433
3434 l_rlm_id Number := Null;
3435 l_rec_ver_num Number := Null;
3436
3437 EXC_NULL_INVALID_DATA EXCEPTION;
3438
3439 BEGIN
3440
3441 Pa_Planning_Resource_Pvt.g_amg_flow := 'Y';
3442 -- First clear the message stack.
3443 If Fnd_Api.To_Boolean( p_init_msg_list ) Then
3444
3445 Fnd_Msg_Pub.Initialize;
3446
3447 End If;
3448
3449 /* Eugene
3450 * Add code here to check if resource list id
3451 * is passed. If it is not passed, thro an error and return status of ERROR
3452 */
3453 If p_resource_list_id is Null Then
3454
3455 X_Return_Status := Fnd_Api.G_Ret_Sts_Error;
3456 X_Error_Msg_Data := 'PA_PLN_RES_LIST_ID_IS_NULL';
3457 x_msg_count := x_msg_count + 1;
3458 Pa_Utils.Add_Message ('PA', X_Error_Msg_Data);
3459 Rollback;
3460 Return;
3461
3462 End If;
3463
3464 /* Added for CBS changes */
3465 IF PA_FP_GEN_AMOUNT_UTILS.get_trg_resource_class_flag(p_resource_list_id) = 'N' THEN
3466 x_return_status := FND_API.G_RET_STS_ERROR;
3467 x_msg_count := x_msg_count + 1;
3468 X_Error_Msg_Data := 'PA_CNT_USE_UPD_AMG_FR_NON_ENHCD_PRL';
3469 Pa_Utils.Add_Message
3470 (P_App_Short_Name => 'PA',
3471 P_Msg_Name => 'PA_CNT_USE_UPD_AMG_FR_NON_ENHCD_PRL');
3472
3473 RETURN;
3474 END IF;
3475
3476 /* End of CBS changes */
3477
3478
3479 For i IN 1..P_Planning_Resource_In_Tbl.Count
3480 Loop
3481
3482 Begin
3483 SELECT meaning || ' ' || to_char(i) || ':'
3484 INTO Pa_Planning_Resource_Pvt.g_token
3485 FROM pa_lookups
3486 WHERE lookup_type = 'PA_PLANNING_RESOURCE'
3487 AND lookup_code = 'PLANNING_RESOURCE';
3488
3489
3490 savepoint planning_res_list_update;
3491
3492 x_msg_count := 0;
3493 x_return_status := Fnd_Api.G_Ret_Sts_Success;
3494
3495 /* Eugene
3496 * Add code here to check if resource list member id is passed. If it is not
3497 * passed, derive it from the alias, if alias is passed. If both are not
3498 * passed, set the record to status of "ERROR and set the message for the
3499 * error. Continue with the loop.
3500 * To get the resource list member id from the alias, you will need to check
3501 * if the resource list is proj specific.
3502 */
3503
3504 If p_planning_resource_in_tbl(i).p_resource_list_member_id Is Null Then
3505
3506 If P_planning_resource_in_tbl(i).p_resource_alias Is Null Then
3507
3508 x_return_status := Fnd_Api.G_Ret_Sts_Error;
3509 Pa_Utils.Add_Message ('PA', 'PA_PLN_RES_LIST_ID_ALIAS_NULL',
3510 'PLAN_RES', Pa_Planning_Resource_Pvt.g_token);
3511 Raise EXC_NULL_INVALID_DATA;
3512
3513 Else -- P_planning_resource_in_tbl(i).p_resource_alias Is Not Null
3514
3515 Open c1(P_Alias => P_Planning_Resource_In_Tbl(i).P_Resource_Alias,
3516 P_Res_List_Id => P_Resource_List_Id,
3517 P_Prj_Id => P_Planning_Resource_In_Tbl(i).P_Project_Id);
3518 Fetch c1 Into l_rlm_Id;
3519
3520 If c1%NotFound Then
3521
3522 Close c1;
3523 x_return_status := Fnd_Api.G_Ret_Sts_Error;
3524 Pa_Utils.Add_Message ('PA', 'PA_PLN_RES_LIST_ALIAS_INVAL',
3525 'PLAN_RES', Pa_Planning_Resource_Pvt.g_token);
3526 RAISE EXC_NULL_INVALID_DATA;
3527
3528 Else
3529
3530 Close c1;
3531
3532 End If;
3533
3534 End If;
3535
3536 Else
3537
3538 l_rlm_Id := p_planning_resource_in_tbl(i).p_resource_list_member_id;
3539
3540 End If;
3541
3542 If p_planning_resource_in_tbl(i).p_record_version_number is Null Then
3543
3544 x_return_status := Fnd_Api.G_Ret_Sts_Error;
3545 Pa_Utils.Add_Message ('PA', 'PA_PLN_RESLISTMEM_RECVER_NULL',
3546 'PLAN_RES', Pa_Planning_Resource_Pvt.g_token);
3547 RAISE EXC_NULL_INVALID_DATA;
3548
3549
3550 Else
3551
3552 Open c2(l_rlm_id);
3553 Fetch c2 Into l_rec_ver_num;
3554
3555 If c2%NotFound Then
3556
3557 Close c2;
3558 x_return_status := Fnd_Api.G_Ret_Sts_Error;
3559 Pa_Utils.Add_Message ('PA', 'PA_PLN_RESLISTMEM_ID_INVAL',
3560 'PLAN_RES', Pa_Planning_Resource_Pvt.g_token);
3561 RAISE EXC_NULL_INVALID_DATA;
3562
3563 Else
3564
3565 Close c2;
3566 If l_rec_ver_num <> p_planning_resource_in_tbl(i).p_record_version_number then
3567
3568 x_return_status := Fnd_Api.G_Ret_Sts_Error;
3569 Pa_Utils.Add_Message('PA', 'PA_PLN_RESLISTMEM_ALREADY_UPD',
3570 'PLAN_RES', Pa_Planning_Resource_Pvt.g_token);
3571 RAISE EXC_NULL_INVALID_DATA;
3572
3573 End If;
3574
3575 End If;
3576
3577 End If;
3578
3579 pa_planning_resource_pvt.Update_Planning_Resource(
3580 p_resource_list_id => p_resource_list_id,
3581 p_resource_list_member_id => l_rlm_id,
3582 p_enabled_flag => p_enabled_flag,
3583 p_resource_alias => P_planning_resource_in_tbl(i).p_resource_alias,
3584 p_spread_curve_id => P_planning_resource_in_tbl(i).p_spread_curve_id,
3585 p_etc_method_code => P_planning_resource_in_tbl(i).p_etc_method_code,
3586 p_mfc_cost_type_id => P_planning_resource_in_tbl(i).p_mfc_cost_type_id,
3587 p_attribute_category => P_planning_resource_in_tbl(i).p_attribute_category,
3588 p_attribute1 => P_planning_resource_in_tbl(i).p_attribute1,
3589 p_attribute2 => P_planning_resource_in_tbl(i).p_attribute2,
3590 p_attribute3 => P_planning_resource_in_tbl(i).p_attribute3,
3591 p_attribute4 => P_planning_resource_in_tbl(i).p_attribute4,
3592 p_attribute5 => P_planning_resource_in_tbl(i).p_attribute5,
3593 p_attribute6 => P_planning_resource_in_tbl(i).p_attribute6,
3594 p_attribute7 => P_planning_resource_in_tbl(i).p_attribute7,
3595 p_attribute8 => P_planning_resource_in_tbl(i).p_attribute8,
3596 p_attribute9 => P_planning_resource_in_tbl(i).p_attribute9,
3597 p_attribute10 => P_planning_resource_in_tbl(i).p_attribute10,
3598 p_attribute11 => P_planning_resource_in_tbl(i).p_attribute11,
3599 p_attribute12 => P_planning_resource_in_tbl(i).p_attribute12,
3600 p_attribute13 => P_planning_resource_in_tbl(i).p_attribute13,
3601 p_attribute14 => P_planning_resource_in_tbl(i).p_attribute14,
3602 p_attribute15 => P_planning_resource_in_tbl(i).p_attribute15,
3603 p_attribute16 => P_planning_resource_in_tbl(i).p_attribute16,
3604 p_attribute17 => P_planning_resource_in_tbl(i).p_attribute17,
3605 p_attribute18 => P_planning_resource_in_tbl(i).p_attribute18,
3606 p_attribute19 => P_planning_resource_in_tbl(i).p_attribute19,
3607 p_attribute20 => P_planning_resource_in_tbl(i).p_attribute20,
3608 p_attribute21 => P_planning_resource_in_tbl(i).p_attribute21,
3609 p_attribute22 => P_planning_resource_in_tbl(i).p_attribute22,
3610 p_attribute23 => P_planning_resource_in_tbl(i).p_attribute23,
3611 p_attribute24 => P_planning_resource_in_tbl(i).p_attribute24,
3612 p_attribute25 => P_planning_resource_in_tbl(i).p_attribute25,
3613 p_attribute26 => P_planning_resource_in_tbl(i).p_attribute26,
3614 p_attribute27 => P_planning_resource_in_tbl(i).p_attribute27,
3615 p_attribute28 => P_planning_resource_in_tbl(i).p_attribute28,
3616 p_attribute29 => P_planning_resource_in_tbl(i).p_attribute29,
3617 p_attribute30 => P_planning_resource_in_tbl(i).p_attribute30,
3618 p_record_version_number => p_planning_resource_in_tbl(i).p_record_version_number,
3619 x_record_version_number => x_planning_resource_out_tbl(i).x_record_version_number,
3620 x_return_status => x_return_status,
3621 x_msg_count => x_msg_count ,
3622 x_error_msg_data => x_error_msg_data);
3623
3624 If x_return_status <> Fnd_Api.G_Ret_Sts_Success Then
3625
3626 RollBack to planning_res_list_update;
3627 Return;
3628
3629 End If;
3630
3631 Exception
3632 When EXC_NULL_INVALID_DATA Then
3633 x_return_status := Fnd_Api.G_Ret_Sts_Error;
3634 x_msg_count := x_msg_count + 1;
3635 RollBack to planning_res_list_update;
3636 Return;
3637 When Others Then
3638 Raise; -- stop processing
3639
3640 End;
3641 End Loop;
3642 /************************************************
3643 * Check the Commit flag. if it is true then Commit.
3644 ***********************************************/
3645 If Fnd_Api.To_Boolean( p_commit ) Then
3646
3647 Commit;
3648
3649 End If;
3650
3651 x_return_status := Fnd_Api.G_Ret_Sts_Success;
3652 x_msg_count := Fnd_Msg_Pub.Count_Msg;
3653
3654 Exception
3655 When Others Then
3656 Rollback;
3657 x_return_status := Fnd_Api.G_Ret_Sts_UnExp_Error;
3658
3659 End Update_Planning_Resource;
3660 /************************************/
3661
3662 /*************************************************
3663 * Procedure : Delete_Planning_Resource
3664 * Description : The purpose of this procedure is to
3665 * delete a planning resource if it is not
3666 * being used, else disable it.
3667 * Further details in the Body.
3668 * The detailed information is in the spec.
3669 ***************************************************/
3670 PROCEDURE Delete_Planning_Resource(
3671 p_resource_list_member_id IN SYSTEM.PA_NUM_TBL_TYPE,
3672 p_commit IN VARCHAR2,
3673 p_init_msg_list IN VARCHAR2,
3674 x_return_status OUT NOCOPY VARCHAR2,
3675 x_msg_count OUT NOCOPY NUMBER,
3676 x_error_msg_data OUT NOCOPY VARCHAR2)
3677
3678 IS
3679 BEGIN
3680 Pa_Planning_Resource_Pvt.g_amg_flow := 'Y';
3681 -- First clear the message stack.
3682 IF FND_API.to_boolean( p_init_msg_list )
3683 THEN
3684 FND_MSG_PUB.initialize;
3685 END IF;
3686 /********************************************
3687 * To Check if resource_list member is currently being
3688 * used in a planning transaction.
3689 * We are checking from pa_resource_assignments table.
3690 *************************************************/
3691 FOR i in 1..p_resource_list_member_id.COUNT
3692 LOOP
3693 SELECT meaning || ' ' || to_char(i) || ':'
3694 INTO Pa_Planning_Resource_Pvt.g_token
3695 FROM pa_lookups
3696 WHERE lookup_type = 'PA_PLANNING_RESOURCE'
3697 AND lookup_code = 'PLANNING_RESOURCE';
3698
3699 pa_planning_resource_pvt.delete_planning_resource
3700 (p_resource_list_member_id =>p_resource_list_member_id(i),
3701 x_return_status =>x_return_status,
3702 x_msg_count =>x_msg_count,
3703 x_error_msg_data =>x_error_msg_data);
3704
3705
3706 END LOOP;
3707 /************************************************
3708 * Check the Commit flag. if it is true then Commit.
3709 ***********************************************/
3710 IF FND_API.to_boolean( p_commit )
3711 THEN
3712 COMMIT;
3713 END IF;
3714 /***************/
3715 END Delete_Planning_Resource;
3716
3717 /******************************************************
3718 * Procedure : Init_Create_Resource_List
3719 * Description : This procedure initializes the global
3720 * temporary tables for the resource list,
3721 * resource formats and,
3722 * resoure list members.
3723 * The detailed information is in the spec.
3724 ******************************************************/
3725 PROCEDURE Init_Create_Resource_List
3726 IS
3727 l_api_name CONSTANT VARCHAR2(30) := 'Init_Create_Resource_List';
3728 BEGIN
3729 FND_MSG_PUB.initialize;
3730
3731 G_Plan_Res_List_IN_Rec := G_Res_List_empty_rec;
3732 G_Plan_Res_List_Out_Rec := G_Res_List_empty_out_rec;
3733
3734 G_Plan_RL_format_In_Tbl.DELETE;
3735 G_Plan_RL_format_Out_Tbl.DELETE;
3736
3737 G_Planning_resource_In_tbl.DELETE;
3738 G_Planning_resource_Out_tbl.DELETE;
3739
3740
3741 G_Plan_RL_Format_tbl_count := 0;
3742 G_Plan_Resource_tbl_count := 0;
3743
3744 EXCEPTION
3745 WHEN OTHERS THEN
3746 FND_MSG_PUB.Add_Exc_Msg
3747 ( p_pkg_name => G_PKG_NAME ,
3748 p_procedure_name => l_api_name
3749 );
3750
3751 END Init_Create_Resource_List;
3752 /*****************************/
3753
3754 /******************************************************
3755 * Procedure : Init_Update_Resource_List
3756 * Description : This procedure initializes the global
3757 * temporary tables for the resource list,
3758 * resource formats and,
3759 * resoure list members.
3760 * The detailed information is in the spec.
3761 ******************************************************/
3762 PROCEDURE Init_Update_Resource_List
3763 IS
3764 l_api_name CONSTANT VARCHAR2(30) := 'Init_Update_Resource_List';
3765 BEGIN
3766 FND_MSG_PUB.initialize;
3767
3768 G_Plan_Res_List_IN_Rec := G_Res_List_empty_rec;
3769 G_Plan_Res_List_Out_Rec := G_Res_List_empty_out_rec;
3770
3771 G_Plan_RL_format_In_Tbl.DELETE;
3772 G_Plan_RL_format_Out_Tbl.DELETE;
3773
3774 G_Planning_resource_In_tbl.DELETE;
3775 G_Planning_resource_Out_tbl.DELETE;
3776
3777
3778 G_Plan_RL_Format_tbl_count := 0;
3779 G_Plan_Resource_tbl_count := 0;
3780
3781 EXCEPTION
3782 WHEN OTHERS THEN
3783 FND_MSG_PUB.Add_Exc_Msg
3784 ( p_pkg_name => G_PKG_NAME ,
3785 p_procedure_name => l_api_name
3786 );
3787 END Init_Update_Resource_List;
3788 /*****************************/
3789 /*************************************************
3790 * Procedure : Load_Resource_List
3791 * Description : This procedure loads the resource
3792 * list globals.
3793 * The detailed information is in the spec.
3794 ***********************************************/
3795 PROCEDURE Load_Resource_List
3796 (p_api_version_number IN NUMBER,
3797 p_resource_list_id IN NUMBER DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
3798 p_resource_list_name IN VARCHAR2,
3799 p_description IN VARCHAR2,
3800 p_start_date IN DATE DEFAULT SYSDATE,
3801 p_end_date IN DATE DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE,
3802 p_job_group_id IN NUMBER,
3803 p_job_group_name IN VARCHAR2 DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
3804 p_use_for_wp_flag IN VARCHAR2 DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
3805 p_control_flag IN VARCHAR2 DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
3806 p_record_version_number IN NUMBER DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
3807 x_return_status OUT NOCOPY Varchar2)
3808 IS
3809 l_api_version_number CONSTANT NUMBER := G_API_VERSION_NUMBER;
3810 l_api_name CONSTANT VARCHAR2(30) := 'Load_Resource_List';
3811 BEGIN
3812 x_return_status := FND_API.g_ret_sts_success;
3813
3814 --Standard API Compatibility Call.
3815 IF NOT FND_API.Compatible_API_Call ( l_api_version_number ,
3816 p_api_version_number ,
3817 l_api_name ,
3818 G_PKG_NAME )
3819 THEN
3820 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3821 END IF;
3822
3823 G_Plan_Res_List_IN_Rec.p_resource_list_id := p_resource_list_id;
3824 G_Plan_Res_List_IN_Rec.p_resource_list_name := p_resource_list_name;
3825 G_Plan_Res_List_IN_Rec.p_description := p_description;
3826 G_Plan_Res_List_IN_Rec.p_start_date := p_start_date;
3827 G_Plan_Res_List_IN_Rec.p_end_date := p_end_date;
3828 G_Plan_Res_List_IN_Rec.p_job_group_id := p_job_group_id;
3829 G_Plan_Res_List_IN_Rec.p_job_group_name := p_job_group_name;
3830 G_Plan_Res_List_IN_Rec.p_use_for_wp_flag := p_use_for_wp_flag;
3831 G_Plan_Res_List_IN_Rec.p_record_version_number :=
3832 p_record_version_number;
3833
3834 EXCEPTION
3835 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
3836 THEN
3837 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3838 WHEN OTHERS THEN
3839 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3840 FND_MSG_PUB.Add_Exc_Msg
3841 ( p_pkg_name => G_PKG_NAME ,
3842 p_procedure_name => l_api_name
3843 );
3844 END Load_Resource_List;
3845
3846
3847 /*************************************************
3848 * Procedure : Load_Resource_Format
3849 * Description : This procedure loads the resource
3850 * Format globals.
3851 * The detailed information is in the spec.
3852 ***********************************************/
3853 PROCEDURE Load_Resource_Format
3854 (p_api_version_number IN Number,
3855 P_Res_Format_Id IN NUMBER DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
3856 x_return_status OUT NOCOPY Varchar2)
3857 IS
3858 l_api_version_number CONSTANT NUMBER := G_API_VERSION_NUMBER;
3859 l_api_name CONSTANT VARCHAR2(30) := 'Load_Resource_Format';
3860 BEGIN
3861 x_return_status := FND_API.g_ret_sts_success;
3862
3863 --Standard API Compatibility Call.
3864 IF NOT FND_API.Compatible_API_Call ( l_api_version_number ,
3865 p_api_version_number ,
3866 l_api_name ,
3867 G_PKG_NAME )
3868 THEN
3869 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3870 END IF;
3871
3872 G_Plan_RL_Format_tbl_count := G_Plan_RL_Format_tbl_count + 1;
3873
3874 G_Plan_RL_format_In_Tbl(G_Plan_RL_Format_tbl_count).P_Res_Format_Id
3875 := P_Res_Format_Id;
3876
3877 EXCEPTION
3878 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
3879 THEN
3880 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3881 WHEN OTHERS THEN
3882 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3883 FND_MSG_PUB.Add_Exc_Msg
3884 ( p_pkg_name => G_PKG_NAME ,
3885 p_procedure_name => l_api_name
3886 );
3887 END Load_Resource_Format;
3888
3889 /*************************************************
3890 * Procedure : Load_Planning_Resource
3891 * Description : This procedure loads the resource
3892 * list members globals.
3893 * The detailed information is in the spec.
3894 ***********************************************/
3895 PROCEDURE Load_Planning_Resource
3896 (p_api_version_number IN Number,
3897 p_resource_list_member_id IN NUMBER DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
3898 p_resource_alias IN VARCHAR2 DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
3899 p_person_id IN NUMBER DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
3900 p_person_name IN VARCHAR2 DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
3901 p_job_id IN NUMBER DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
3902 p_job_name IN VARCHAR2 DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
3903 p_organization_id IN NUMBER DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
3904 p_organization_name IN VARCHAR2 DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
3905 p_vendor_id IN NUMBER DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
3906 p_vendor_name IN VARCHAR2 DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
3907 p_fin_category_name IN VARCHAR2 DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
3908 p_non_labor_resource IN VARCHAR2 DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
3909 p_project_role_id IN NUMBER DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
3910 p_project_role_name IN VARCHAR2 DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
3911 p_resource_class_id IN NUMBER DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
3912 p_resource_class_code IN VARCHAR2 DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
3913 p_res_format_id IN NUMBER ,
3914 p_spread_curve_id IN NUMBER DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
3915 p_etc_method_code IN VARCHAR2 DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
3916 p_mfc_cost_type_id IN NUMBER DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
3917 p_fc_res_type_code IN VARCHAR2 DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
3918 p_inventory_item_id IN NUMBER DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
3919 p_inventory_item_name IN VARCHAR2 DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
3920 p_item_category_id IN NUMBER DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
3921 p_item_category_name IN VARCHAR2 DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
3922 p_attribute_category IN VARCHAR2 DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
3923 p_attribute1 IN VARCHAR2 DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
3924 p_attribute2 IN VARCHAR2 DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
3925 p_attribute3 IN VARCHAR2 DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
3926 p_attribute4 IN VARCHAR2 DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
3927 p_attribute5 IN VARCHAR2 DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
3928 p_attribute6 IN VARCHAR2 DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
3929 p_attribute7 IN VARCHAR2 DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
3930 p_attribute8 IN VARCHAR2 DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
3931 p_attribute9 IN VARCHAR2 DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
3932 p_attribute10 IN VARCHAR2 DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
3933 p_attribute11 IN VARCHAR2 DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
3934 p_attribute12 IN VARCHAR2 DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
3935 p_attribute13 IN VARCHAR2 DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
3936 p_attribute14 IN VARCHAR2 DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
3937 p_attribute15 IN VARCHAR2 DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
3938 p_attribute16 IN VARCHAR2 DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
3939 p_attribute17 IN VARCHAR2 DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
3940 p_attribute18 IN VARCHAR2 DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
3941 p_attribute19 IN VARCHAR2 DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
3942 p_attribute20 IN VARCHAR2 DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
3943 p_attribute21 IN VARCHAR2 DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
3944 p_attribute22 IN VARCHAR2 DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
3945 p_attribute23 IN VARCHAR2 DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
3946 p_attribute24 IN VARCHAR2 DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
3947 p_attribute25 IN VARCHAR2 DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
3948 p_attribute26 IN VARCHAR2 DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
3949 p_attribute27 IN VARCHAR2 DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
3950 p_attribute28 IN VARCHAR2 DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
3951 p_attribute29 IN VARCHAR2 DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
3952 p_attribute30 IN VARCHAR2 DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
3953 p_person_type_code IN VARCHAR2 DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
3954 p_bom_resource_id IN NUMBER DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
3955 p_bom_resource_name IN VARCHAR2 DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
3956 p_team_role IN VARCHAR2 DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
3957 p_incur_by_res_code IN VARCHAR2 DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
3958 p_incur_by_res_type IN VARCHAR2 DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
3959 p_record_version_number IN NUMBER,
3960 p_project_id IN NUMBER ,
3961 p_enabled_flag IN Varchar2 DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
3962 x_return_status OUT NOCOPY Varchar2)
3963 IS
3964 l_api_version_number CONSTANT NUMBER := G_API_VERSION_NUMBER;
3965 l_api_name CONSTANT VARCHAR2(30) := 'Load_Planning_Resource';
3966 BEGIN
3967 x_return_status := FND_API.g_ret_sts_success;
3968
3969 --Standard API Compatibility Call.
3970 IF NOT FND_API.Compatible_API_Call ( l_api_version_number ,
3971 p_api_version_number ,
3972 l_api_name ,
3973 G_PKG_NAME )
3974 THEN
3975 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3976 END IF;
3977
3978 G_Plan_Resource_tbl_count := G_Plan_Resource_tbl_count + 1;
3979
3980 G_Planning_resource_in_tbl(G_Plan_Resource_tbl_count).p_resource_list_member_id := p_resource_list_member_id;
3981 G_Planning_resource_in_tbl(G_Plan_Resource_tbl_count).p_resource_alias
3982 := p_resource_alias;
3983 G_Planning_resource_in_tbl(G_Plan_Resource_tbl_count).p_person_id
3984 := p_person_id;
3985 G_Planning_resource_in_tbl(G_Plan_Resource_tbl_count).p_person_name
3986 := p_person_name;
3987 G_Planning_resource_in_tbl(G_Plan_Resource_tbl_count).p_job_id
3988 := p_job_id;
3989 G_Planning_resource_in_tbl(G_Plan_Resource_tbl_count).p_job_name
3990 := p_job_name;
3991 G_Planning_resource_in_tbl(G_Plan_Resource_tbl_count).p_organization_id
3992 := p_organization_id;
3993 G_Planning_resource_in_tbl(G_Plan_Resource_tbl_count).p_organization_name
3994 := p_organization_name;
3995 G_Planning_resource_in_tbl(G_Plan_Resource_tbl_count).p_vendor_id
3996 := p_vendor_id;
3997 G_Planning_resource_in_tbl(G_Plan_Resource_tbl_count).p_vendor_name
3998 := p_vendor_name;
3999 G_Planning_resource_in_tbl(G_Plan_Resource_tbl_count).p_fin_category_name
4000 := p_fin_category_name;
4001 G_Planning_resource_in_tbl(G_Plan_Resource_tbl_count).p_non_labor_resource
4002 := p_non_labor_resource;
4003 G_Planning_resource_in_tbl(G_Plan_Resource_tbl_count).p_project_role_id
4004 := p_project_role_id;
4005 G_Planning_resource_in_tbl(G_Plan_Resource_tbl_count).p_project_role_name
4006 := p_project_role_name;
4007 G_Planning_resource_in_tbl(G_Plan_Resource_tbl_count).p_resource_class_id
4008 := p_resource_class_id;
4009 G_Planning_resource_in_tbl(G_Plan_Resource_tbl_count).p_resource_class_code := p_resource_class_code;
4010 G_Planning_resource_in_tbl(G_Plan_Resource_tbl_count).p_res_format_id
4011 := p_res_format_id;
4012 G_Planning_resource_in_tbl(G_Plan_Resource_tbl_count).p_spread_curve_id
4013 := p_spread_curve_id;
4014 G_Planning_resource_in_tbl(G_Plan_Resource_tbl_count).p_etc_method_code
4015 := p_etc_method_code;
4016 G_Planning_resource_in_tbl(G_Plan_Resource_tbl_count).p_mfc_cost_type_id
4017 := p_mfc_cost_type_id;
4018 G_Planning_resource_in_tbl(G_Plan_Resource_tbl_count).p_fc_res_type_code
4019 := p_fc_res_type_code;
4020 G_Planning_resource_in_tbl(G_Plan_Resource_tbl_count).p_inventory_item_id
4021 := p_inventory_item_id;
4022 G_Planning_resource_in_tbl(G_Plan_Resource_tbl_count).p_inventory_item_name
4023 := p_inventory_item_name;
4024 G_Planning_resource_in_tbl(G_Plan_Resource_tbl_count).p_item_category_id
4025 := p_item_category_id;
4026 G_Planning_resource_in_tbl(G_Plan_Resource_tbl_count).p_item_category_name
4027 := p_item_category_name;
4028 G_Planning_resource_in_tbl(G_Plan_Resource_tbl_count).p_attribute_category
4029 := p_attribute_category;
4030 G_Planning_resource_in_tbl(G_Plan_Resource_tbl_count).p_attribute1
4031 := p_attribute1;
4032 G_Planning_resource_in_tbl(G_Plan_Resource_tbl_count).p_attribute2
4033 := p_attribute2;
4034 G_Planning_resource_in_tbl(G_Plan_Resource_tbl_count).p_attribute3
4035 := p_attribute3;
4036 G_Planning_resource_in_tbl(G_Plan_Resource_tbl_count).p_attribute4
4037 := p_attribute4;
4038 G_Planning_resource_in_tbl(G_Plan_Resource_tbl_count).p_attribute5
4039 := p_attribute5;
4040 G_Planning_resource_in_tbl(G_Plan_Resource_tbl_count).p_attribute6
4041 := p_attribute6;
4042 G_Planning_resource_in_tbl(G_Plan_Resource_tbl_count).p_attribute7
4043 := p_attribute7;
4044 G_Planning_resource_in_tbl(G_Plan_Resource_tbl_count).p_attribute8
4045 := p_attribute8;
4046 G_Planning_resource_in_tbl(G_Plan_Resource_tbl_count).p_attribute9
4047 := p_attribute9;
4048 G_Planning_resource_in_tbl(G_Plan_Resource_tbl_count).p_attribute10
4049 := p_attribute10;
4050 G_Planning_resource_in_tbl(G_Plan_Resource_tbl_count).p_attribute11
4051 := p_attribute11;
4052 G_Planning_resource_in_tbl(G_Plan_Resource_tbl_count).p_attribute12
4053 := p_attribute12;
4054 G_Planning_resource_in_tbl(G_Plan_Resource_tbl_count).p_attribute13
4055 := p_attribute13;
4056 G_Planning_resource_in_tbl(G_Plan_Resource_tbl_count).p_attribute14
4057 := p_attribute14;
4058 G_Planning_resource_in_tbl(G_Plan_Resource_tbl_count).p_attribute15
4059 := p_attribute15;
4060 G_Planning_resource_in_tbl(G_Plan_Resource_tbl_count).p_attribute16
4061 := p_attribute16;
4062 G_Planning_resource_in_tbl(G_Plan_Resource_tbl_count).p_attribute17
4063 := p_attribute17;
4064 G_Planning_resource_in_tbl(G_Plan_Resource_tbl_count).p_attribute18
4065 := p_attribute18;
4066 G_Planning_resource_in_tbl(G_Plan_Resource_tbl_count).p_attribute19
4067 := p_attribute19;
4068 G_Planning_resource_in_tbl(G_Plan_Resource_tbl_count).p_attribute20
4069 := p_attribute20;
4070 G_Planning_resource_in_tbl(G_Plan_Resource_tbl_count).p_attribute21
4071 := p_attribute21;
4072 G_Planning_resource_in_tbl(G_Plan_Resource_tbl_count).p_attribute22
4073 := p_attribute22;
4074 G_Planning_resource_in_tbl(G_Plan_Resource_tbl_count).p_attribute23
4075 := p_attribute23;
4076 G_Planning_resource_in_tbl(G_Plan_Resource_tbl_count).p_attribute24
4077 := p_attribute24;
4078 G_Planning_resource_in_tbl(G_Plan_Resource_tbl_count).p_attribute25
4079 := p_attribute25;
4080 G_Planning_resource_in_tbl(G_Plan_Resource_tbl_count).p_attribute26
4081 := p_attribute26;
4082 G_Planning_resource_in_tbl(G_Plan_Resource_tbl_count).p_attribute27
4083 := p_attribute27;
4084 G_Planning_resource_in_tbl(G_Plan_Resource_tbl_count).p_attribute28
4085 := p_attribute28;
4086 G_Planning_resource_in_tbl(G_Plan_Resource_tbl_count).p_attribute29
4087 := p_attribute29;
4088 G_Planning_resource_in_tbl(G_Plan_Resource_tbl_count).p_attribute30
4089 := p_attribute30;
4090 G_Planning_resource_in_tbl(G_Plan_Resource_tbl_count).p_person_type_code
4091 := p_person_type_code;
4092 G_Planning_resource_in_tbl(G_Plan_Resource_tbl_count).p_bom_resource_id
4093 := p_bom_resource_id;
4094 G_Planning_resource_in_tbl(G_Plan_Resource_tbl_count).p_bom_resource_name
4095 := p_bom_resource_name;
4096 G_Planning_resource_in_tbl(G_Plan_Resource_tbl_count).p_team_role
4097 := p_team_role;
4098 G_Planning_resource_in_tbl(G_Plan_Resource_tbl_count).p_incur_by_res_code
4099 := p_incur_by_res_code;
4100 G_Planning_resource_in_tbl(G_Plan_Resource_tbl_count).p_incur_by_res_type
4101 := p_incur_by_res_type;
4102 G_Planning_resource_in_tbl(G_Plan_Resource_tbl_count).p_record_version_number
4103 := p_record_version_number;
4104 G_Planning_resource_in_tbl(G_Plan_Resource_tbl_count).p_project_id
4105 := p_project_id;
4106 G_Planning_resource_in_tbl(G_Plan_Resource_tbl_count).p_enabled_flag
4107 := p_enabled_flag;
4108
4109 EXCEPTION
4110 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
4111 THEN
4112 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4113 WHEN OTHERS THEN
4114 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4115 FND_MSG_PUB.Add_Exc_Msg
4116 ( p_pkg_name => G_PKG_NAME ,
4117 p_procedure_name => l_api_name
4118 );
4119 END Load_Planning_Resource;
4120
4121 /**************************************************
4122 * Procedure : Exec_Create_Resource_List
4123 * Description : This procedure passes the PL/SQL
4124 * globals to the Create_Resource_List API.
4125 * The API is typically used with the
4126 * load-execute-fetch model.
4127 * The detailed information is in spec.
4128 ***************************************************/
4129 PROCEDURE Exec_Create_Resource_List
4130 (p_commit IN VARCHAR2 := FND_API.G_FALSE,
4131 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
4132 p_api_version_number IN NUMBER,
4133 x_return_status OUT NOCOPY VARCHAR2,
4134 x_msg_count OUT NOCOPY NUMBER,
4135 x_msg_data OUT NOCOPY VARCHAR2 )
4136 IS
4137 l_api_version_number CONSTANT NUMBER := G_API_VERSION_NUMBER;
4138 l_api_name CONSTANT VARCHAR2(30) := 'Exec_Create_Resource_List';
4139 l_message_count NUMBER;
4140 BEGIN
4141 x_return_status := FND_API.g_ret_sts_success;
4142
4143 IF NOT FND_API.Compatible_API_Call ( l_api_version_number ,
4144 p_api_version_number ,
4145 l_api_name ,
4146 G_PKG_NAME )
4147 THEN
4148 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4149 END IF;
4150
4151 Create_Resource_List
4152 (p_commit => p_commit,
4153 p_init_msg_list => p_init_msg_list,
4154 p_api_version_number => p_api_version_number,
4155 P_plan_res_list_Rec => G_Plan_Res_List_IN_Rec,
4156 X_plan_res_list_Rec => G_Plan_Res_List_Out_Rec,
4157 P_Plan_RL_Format_Tbl => G_Plan_RL_format_In_Tbl,
4158 X_Plan_RL_Format_Tbl => G_Plan_RL_format_Out_Tbl,
4159 P_planning_resource_in_tbl => G_Planning_resource_in_tbl,
4160 X_planning_resource_out_tbl => G_Planning_resource_out_tbl,
4161 X_Return_Status => x_return_status ,
4162 X_Msg_Count => x_msg_count,
4163 X_Msg_Data => x_msg_data);
4164
4165 EXCEPTION
4166
4167 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4168 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4169 WHEN OTHERS THEN
4170 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4171 -- 4537865
4172 x_msg_data := SUBSTRB(SQLERRM,1,240);
4173 x_msg_count := 1 ;
4174 -- 4537865
4175 FND_MSG_PUB.Add_Exc_Msg
4176 ( p_pkg_name => G_PKG_NAME ,
4177 p_procedure_name => l_api_name
4178 );
4179 END Exec_Create_Resource_List;
4180 /*****************************/
4181 /**************************************************
4182 * Procedure : Exec_Update_Resource_List
4183 * Description : This procedure passes the PL/SQL
4184 * globals to the Update_Resource_List API.
4185 * The API is typically used with the
4186 * load-execute-fetch model.
4187 * The detailed information is in spec.
4188 ***************************************************/
4189 PROCEDURE Exec_Update_Resource_List
4190 (p_commit IN VARCHAR2 := FND_API.G_FALSE,
4191 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
4192 p_api_version_number IN NUMBER,
4193 x_return_status OUT NOCOPY VARCHAR2,
4194 x_msg_count OUT NOCOPY NUMBER,
4195 x_msg_data OUT NOCOPY VARCHAR2
4196 )
4197 IS
4198 l_api_version_number CONSTANT NUMBER := G_API_VERSION_NUMBER;
4199 l_api_name CONSTANT VARCHAR2(30) := 'Exec_Update_Resource_List';
4200 l_message_count NUMBER;
4201 BEGIN
4202 x_return_status := FND_API.g_ret_sts_success;
4203
4204 IF NOT FND_API.Compatible_API_Call ( l_api_version_number ,
4205 p_api_version_number ,
4206 l_api_name ,
4207 G_PKG_NAME )
4208 THEN
4209 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4210 END IF;
4211
4212 Update_Resource_List
4213 (p_commit => p_commit,
4214 p_init_msg_list => p_init_msg_list,
4215 p_api_version_number => p_api_version_number,
4216 P_plan_res_list_Rec => G_Plan_Res_List_IN_Rec,
4217 X_plan_res_list_Rec => G_Plan_Res_List_Out_Rec,
4218 P_Plan_RL_Format_Tbl => G_Plan_RL_format_In_Tbl,
4219 X_Plan_RL_Format_Tbl => G_Plan_RL_format_Out_Tbl,
4220 P_planning_resource_in_tbl => G_Planning_resource_in_tbl,
4221 X_planning_resource_out_tbl => G_Planning_resource_out_tbl,
4222 X_Return_Status => x_return_status,
4223 X_Msg_Count => x_msg_count,
4224 X_Msg_Data => x_msg_data);
4225 -- 4537865 : Included Exception Block
4226 EXCEPTION
4227
4228 WHEN OTHERS THEN
4229 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4230 x_msg_data := SUBSTRB(SQLERRM,1,240);
4231 x_msg_count := 1 ;
4232 FND_MSG_PUB.Add_Exc_Msg
4233 ( p_pkg_name => G_PKG_NAME ,
4234 p_procedure_name => l_api_name ,
4235 p_error_text => x_msg_data
4236 );
4237 END Exec_Update_Resource_List;
4238 /*****************************/
4239 /******************************************************
4240 * Procedure : Fetch_Resource_List
4241 * Description : This procedure returns the return status
4242 * and the newly created resource_list_id
4243 * if any, from a load-execute-fetch cycle.
4244 * The detailed information is in the spec.
4245 *******************************************************/
4246 PROCEDURE Fetch_Resource_List
4247 (
4248 p_api_version_number IN NUMBER,
4249 x_return_status OUT NOCOPY VARCHAR2,
4250 x_resource_list_id OUT NOCOPY NUMBER)
4251 IS
4252 l_api_version_number CONSTANT NUMBER := G_API_VERSION_NUMBER;
4253 l_api_name CONSTANT VARCHAR2(30):= 'Fetch_Resource_List';
4254 l_msg_count INTEGER :=0;
4255 BEGIN
4256 x_return_status := FND_API.G_RET_STS_SUCCESS;
4257
4258 IF NOT FND_API.Compatible_API_Call ( l_api_version_number ,
4259 p_api_version_number ,
4260 l_api_name ,
4261 G_PKG_NAME )
4262 THEN
4263 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4264 END IF;
4265
4266 x_resource_list_id := G_Plan_Res_List_Out_Rec.X_resource_list_id;
4267
4268 EXCEPTION
4269 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4270 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4271 -- 4537865 : RESETTING x_resource_list_id also
4272 x_resource_list_id := NULL ;
4273 WHEN OTHERS THEN
4274 -- 4537865 : RESETTING x_resource_list_id also
4275 x_resource_list_id := NULL ;
4276
4277 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4278 FND_MSG_PUB.Add_Exc_Msg
4279 ( p_pkg_name => G_PKG_NAME ,
4280 p_procedure_name => l_api_name
4281 );
4282 END Fetch_Resource_List;
4283
4284 /******************************************************
4285 * Procedure : Fetch_Plan_Format
4286 * Description : This procedure returns the return status
4287 * and the newly created Plan_rl_format_id
4288 * if any, from a load-execute-fetch cycle.
4289 * The detailed information is in the spec.
4290 *******************************************************/
4291 PROCEDURE Fetch_Plan_Format
4292 ( p_api_version_number IN NUMBER,
4293 p_format_index IN NUMBER
4294 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
4295 x_return_status OUT NOCOPY VARCHAR2,
4296 X_Plan_RL_Format_Id OUT NOCOPY NUMBER)
4297 IS
4298 l_api_version_number CONSTANT NUMBER := G_API_VERSION_NUMBER;
4299 l_api_name CONSTANT VARCHAR2(30):= 'Fetch_Plan_Format';
4300 l_index NUMBER;
4301 BEGIN
4302 x_return_status := FND_API.G_RET_STS_SUCCESS;
4303 IF NOT FND_API.Compatible_API_Call ( l_api_version_number ,
4304 p_api_version_number ,
4305 l_api_name ,
4306 G_PKG_NAME )
4307 THEN
4308 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4309 END IF;
4310
4311 IF p_format_index = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM THEN
4312 l_index := 1;
4313 ELSE
4314 l_index := p_format_index ;
4315 END IF;
4316
4317 IF NOT G_Plan_RL_format_In_Tbl.EXISTS(l_index) THEN
4318 X_Plan_RL_Format_Id := Null;
4319 ELSE
4320 X_Plan_RL_Format_Id :=
4321 G_Plan_RL_format_Out_Tbl(l_index).X_Plan_RL_Format_Id;
4322 END IF;
4323
4324 EXCEPTION
4325 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4326 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4327 -- 4537865 : RESETTING X_Plan_RL_Format_Id also
4328 X_Plan_RL_Format_Id := NULL ;
4329
4330 WHEN OTHERS THEN
4331 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4332 -- 4537865 : RESETTING X_Plan_RL_Format_Id also
4333 X_Plan_RL_Format_Id := NULL ;
4334 FND_MSG_PUB.Add_Exc_Msg
4335 ( p_pkg_name => G_PKG_NAME ,
4336 p_procedure_name => l_api_name
4337 );
4338 END Fetch_Plan_Format;
4339
4340 /******************************************************
4341 * Procedure : Fetch_Resource_List_Member
4342 * Description : This procedure returns the return status
4343 * and the newly created resource_list_id
4344 * if any, from a load-execute-fetch cycle.
4345 * The detailed information is in spec.
4346 *******************************************************/
4347 PROCEDURE Fetch_Resource_List_Member
4348 ( p_api_version_number IN NUMBER,
4349 p_member_index IN NUMBER
4350 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
4351 x_return_status OUT NOCOPY VARCHAR2,
4352 x_resource_list_member_id OUT NOCOPY NUMBER)
4353 IS
4354 l_api_version_number CONSTANT NUMBER := G_API_VERSION_NUMBER;
4355 l_api_name CONSTANT VARCHAR2(30):= 'Fetch_Resource_List_Member';
4356 l_index NUMBER;
4357 BEGIN
4358 x_return_status := FND_API.G_RET_STS_SUCCESS;
4359 IF NOT FND_API.Compatible_API_Call ( l_api_version_number ,
4360 p_api_version_number ,
4361 l_api_name ,
4362 G_PKG_NAME )
4363 THEN
4364 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4365 END IF;
4366
4367 IF p_member_index = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM THEN
4368 l_index := 1;
4369 ELSE
4370 l_index := p_member_index ;
4371 END IF;
4372
4373 IF NOT G_Planning_resource_in_tbl.EXISTS(l_index) THEN
4374 x_resource_list_member_id := NULL;
4375 ELSE
4376 x_resource_list_member_id :=
4377 G_Planning_resource_out_tbl(l_index).x_resource_list_member_id;
4378 END IF;
4379 -- 4537865
4380 EXCEPTION
4381 WHEN OTHERS THEN
4382 x_resource_list_member_id := NULL ;
4383 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4384 FND_MSG_PUB.Add_Exc_Msg
4385 ( p_pkg_name => G_PKG_NAME ,
4386 p_procedure_name => l_api_name
4387 );
4388 -- Not RAISING because other similar APIs dont have RAISE
4389 END Fetch_Resource_List_Member;
4390
4391 END Pa_Plan_Res_List_Pub;
[Home] [Help]