1 PACKAGE BODY Pa_Plan_Res_List_Pub AS
2 /* $Header: PARESLPB.pls 120.3 2006/09/06 23:51:09 ramurthy noship $*/
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 IF (nvl(l_old_resource_list_rec.name,' ') <> nvl(L_Plan_res_list_Rec.p_resource_list_name,' ') OR
1623 nvl(l_old_resource_list_rec.description,' ') <> nvl(L_Plan_res_list_Rec.p_description,' ') OR
1624 nvl(l_old_resource_list_rec.start_date_active,sysdate) <> nvl(L_Plan_res_list_Rec.p_start_date,sysdate) OR
1625 nvl(l_old_resource_list_rec.end_date_active,sysdate) <> nvl(L_Plan_res_list_Rec.p_end_date,sysdate) OR
1626 nvl(l_old_resource_list_rec.job_group_id,-1) <> nvl(L_Plan_res_list_Rec.p_job_group_id,-1) OR
1627 nvl(l_old_resource_list_rec.use_for_wp_flag,' ') <> nvl(L_Plan_res_list_Rec.p_use_for_wp_flag,' ') OR
1628 nvl(l_old_resource_list_rec.control_flag,' ') <> nvl(L_Plan_res_list_Rec.p_control_flag,' ')
1629 )
1630 THEN
1631
1632 -- One of the resource_list attributes has changed which means,
1633 -- the resource_list record needs to be updated.
1634
1635 IF (L_Plan_res_list_Rec.p_record_version_number is null OR
1636 L_Plan_res_list_Rec.p_record_version_number <> l_old_resource_list_rec.record_version_number) THEN
1637 Pa_Utils.Add_Message(
1638 P_App_Short_Name => 'PA',
1639 P_Msg_Name => 'PA_PLN_RES_LIST_REC_VER_INVAL');
1640
1641 Raise l_ERROR;
1642 END IF;
1643
1644
1645 /********************************************************
1646 * Call to API Pa_Create_Resource.Update_Resource_List to update
1647 * the resource_list with the newly passed values. Values are
1648 * passed thro the record struc P_plan_res_list_Rec.
1649 *********************************************************/
1650
1651 Pa_Create_Resource.Update_Resource_List
1652 (p_resource_list_name => L_plan_res_list_Rec.p_resource_list_name,
1653 p_description => L_plan_res_list_Rec.p_description,
1654 p_start_date => L_plan_res_list_Rec.p_start_date,
1655 p_end_date => L_plan_res_list_Rec.p_end_date,
1656 p_job_group_id => L_Plan_Res_List_Rec.p_job_group_id,
1657 p_job_group_name => L_plan_res_list_Rec.p_job_group_name,
1658 p_use_for_wp_flag => L_plan_res_list_Rec.p_use_for_wp_flag,
1659 p_control_flag => L_plan_res_list_Rec.p_control_flag,
1660 --p_migration_code => 'N',
1661 p_record_version_number => L_Plan_res_list_Rec.p_record_version_number,
1662 p_resource_list_id => L_plan_res_list_Rec.p_resource_list_id,
1663 x_msg_count => l_msg_count,
1664 x_return_status => l_return_status,
1665 x_msg_data => l_msg_data);
1666
1667 If l_Return_Status <> Fnd_Api.G_Ret_Sts_Success Then
1668 x_return_status := Fnd_Api.G_Ret_Sts_Error;
1669 x_msg_count := x_msg_count + 1;
1670 x_msg_data := l_msg_data;
1671 Pa_Utils.Add_Message ('PA', x_msg_data);
1672 Rollback ;
1673 Return;
1674
1675 End If;
1676
1677 /************************************************
1678 * Check the Commit flag. if it is true then Commit.
1679 ************************************************/
1680 If l_return_status = Fnd_Api.G_Ret_Sts_Success Then
1681
1682 If Fnd_Api.To_Boolean( p_commit ) Then
1683
1684 Commit;
1685
1686 End If;
1687
1688 End If;
1689 END IF; -- If resource list attributes have changed.
1690 ELSE
1691 -- You need the resource list Id for any processing.
1692 Pa_Utils.Add_Message(
1693 P_App_Short_Name => 'PA',
1694 P_Msg_Name => 'PA_INVALID_RES_LIST_ID');
1695
1696 Raise l_ERROR;
1697 END IF; -- L_plan_res_list_Rec.p_resource_list_id is not null
1698
1699 /***************************************************
1700 * In the case of Resource formats updation is not possible.
1701 * We can either create/delete resource formats.
1702 * Deletion is taken care thro another api. So here we are
1703 * going to create_resource_format using the pl/sql tables
1704 * P_Plan_RL_Format_Tbl and X_Plan_RL_Format_Tbl passed.
1705 *****************************************************/
1706 --SAVEPOINT Update_Resource_List_Pub_A;
1707 IF L_Plan_RL_Format_Tbl.COUNT > 0 THEN
1708 For i IN L_Plan_RL_Format_Tbl.first..L_Plan_RL_Format_Tbl.last
1709 Loop
1710
1711 -- Validate that the P_RES_FORMAT_ID passed in is valid
1712
1713 BEGIN
1714
1715 SELECT 'Y'
1716 INTO l_exists
1717 FROM pa_res_formats_b
1718 WHERE res_format_id = L_Plan_RL_Format_Tbl(i).P_Res_Format_Id;
1719
1720 EXCEPTION
1721 WHEN NO_DATA_FOUND THEN
1722 Pa_Utils.Add_Message(
1723 P_App_Short_Name => 'PA',
1724 P_Msg_Name => 'PA_INVALID_RES_FORMAT_ID');
1725
1726 Raise l_ERROR;
1727
1728 END;
1729
1730 Pa_Plan_RL_Formats_Pvt.Create_Plan_RL_Format(
1731 P_Res_List_Id => L_plan_res_list_Rec.p_resource_list_id,
1732 P_Res_Format_Id => L_Plan_RL_Format_Tbl(i).P_Res_Format_Id,
1733 X_Plan_RL_Format_Id => X_Plan_RL_Format_Tbl(i).X_Plan_RL_Format_Id,
1734 X_Record_Version_Number => X_Plan_RL_Format_Tbl(i).X_Record_Version_Number,
1735 X_Return_Status => X_Return_Status,
1736 X_Msg_Count => l_msg_count,
1737 X_Msg_Data => l_msg_data);
1738
1739 If X_Return_Status <> Fnd_Api.G_Ret_Sts_Success Then
1740
1741 l_return_status := Fnd_Api.G_Ret_Sts_Error;
1742 x_return_status := Fnd_Api.G_Ret_Sts_Error;
1743 x_msg_count := x_msg_count + 1;
1744 x_msg_data := l_msg_data;
1745 Pa_Utils.Add_Message ('PA', x_msg_data);
1746 RollbacK ;
1747 Return;
1748
1749 Else
1750
1751 l_return_status := Fnd_Api.G_Ret_Sts_Success;
1752
1753 End If;
1754
1755 End Loop;
1756
1757 END IF; -- L_Plan_RL_Format_Tbl.COUNT > 0
1758
1759
1760 /************************************************
1761 * Check the Commit flag. if it is true then Commit.
1762 ************************************************/
1763 If l_return_status = Fnd_Api.G_Ret_Sts_Success Then
1764
1765 If Fnd_Api.To_Boolean( p_commit ) Then
1766
1767 Commit;
1768
1769 End If;
1770
1771 End If;
1772
1773
1774 --SAVEPOINT Update_Resource_List_Pub_B;
1775 IF L_planning_resource_in_tbl.COUNT > 0 THEN
1776 For i IN L_planning_resource_in_tbl.first..L_planning_resource_in_tbl.last
1777 Loop
1778
1779 /**********************************************************
1780 * For each of the resource_list_memeber_id in the table.
1781 * We'll first check if an entry is present in the
1782 * pa_resource_list_members table or not.
1783 * If it is present then we'll call the
1784 * Pa_Planning_resource_pvt.Update_Planning_Resource api,
1785 * which would just update the corr record in the table.
1786 * If it is not present then we'll call the
1787 * pa_planning_resource_pvt.Create_Planning_Resource api,
1788 * which would take care of creating the corr record in
1789 * the table. Once a resource list member is succ created/Updated,
1790 * we'll commit it in the db.
1791 ************************************************************/
1792 /*
1793 --commented out for bug 4103909
1794 If L_planning_resource_in_tbl(i).p_resource_list_member_id Is Null Then
1795
1796 IF l_planning_resource_in_tbl(i).p_resource_alias is null THEN
1797
1798 Pa_Utils.Add_Message(
1799 P_App_Short_Name => 'PA',
1800 P_Msg_Name => 'PA_RLM_ALIAS_AND_ID_NULL');
1801
1802 Raise l_ERROR;
1803
1804 END IF;
1805
1806 End If;
1807
1808 IF l_old_resource_list_rec.control_flag = 'Y' THEN
1809
1810 BEGIN
1811 SELECT resource_list_member_id,
1812 record_version_number
1813 INTO L_planning_resource_in_tbl(i).p_resource_list_member_id,
1814 l_rlm_record_version_number
1815 FROM PA_RESOURCE_LIST_MEMBERS
1816 WHERE resource_list_id = l_old_resource_list_rec.resource_list_id
1817 AND ALIAS = L_planning_resource_in_tbl(i).p_resource_alias;
1818
1819 l_check_exist := 'Y';
1820
1821 EXCEPTION
1822 WHEN NO_DATA_FOUND THEN
1823 l_check_exist := 'N';
1824 END;
1825
1826 ELSE
1827 BEGIN
1828 IF l_planning_resource_in_tbl(i).p_project_id is null THEN
1829 l_object_type := 'RESOURCE_LIST';
1830 l_object_id := l_old_resource_list_rec.resource_list_id;
1831 ELSE
1832 l_object_type := 'PROJECT';
1833 l_object_id := l_planning_resource_in_tbl(i).p_project_id;
1834 END IF;
1835
1836 SELECT resource_list_member_id,
1837 record_version_number
1838 INTO l_planning_resource_in_tbl(i).p_resource_list_member_id,
1839 l_rlm_record_version_number
1840 FROM PA_RESOURCE_LIST_MEMBERS
1841 WHERE resource_list_id = l_old_resource_list_rec.resource_list_id
1842 AND ALIAS = L_planning_resource_in_tbl(i).p_resource_alias
1843 AND object_type = l_object_type
1844 AND object_id = l_object_id;
1845
1846 l_check_exist := 'Y';
1847
1848 EXCEPTION
1849 WHEN NO_DATA_FOUND THEN
1850 l_check_exist := 'N';
1851 END;
1852 END IF;
1853
1854 Else
1855 */ --End of changes for bug 4103909
1856
1857 If L_planning_resource_in_tbl(i).p_resource_list_member_id Is not Null Then
1858 Begin
1859 Select record_version_number
1860 Into l_rlm_record_version_number
1861 From pa_resource_list_members
1862 Where resource_list_member_id = L_planning_resource_in_tbl(i).p_resource_list_member_id;
1863
1864 Exception
1865 When No_Data_Found Then
1866 Pa_Utils.Add_Message(
1867 P_App_Short_Name => 'PA',
1868 P_Msg_Name => 'PA_RLM_ID_INVALID');
1869
1870 Raise l_ERROR;
1871
1872 End;
1873
1874 End If;
1875
1876
1877 If L_planning_resource_in_tbl(i).p_resource_list_member_id is null Then --For bug 4103909
1878
1879 -- We need to create the planning resource. First validate the reqd
1880 -- attributes.
1881
1882 IF L_planning_resource_in_tbl(i).p_res_format_id IS null THEN
1883 Pa_Utils.Add_Message(
1884 P_App_Short_Name => 'PA',
1885 P_Msg_Name => 'PA_RLM_FORMAT_NULL');
1886
1887 Raise l_ERROR;
1888 ELSE
1889 BEGIN
1890 -- derive resource_class_flag
1891 SELECT resource_class_flag
1892 INTO l_res_class_flag
1893 FROM pa_res_formats_b
1894 WHERE res_format_id = L_planning_resource_in_tbl(i).p_res_format_id;
1895 EXCEPTION
1896 WHEN NO_DATA_FOUND THEN
1897 Pa_Utils.Add_Message(
1898 P_App_Short_Name => 'PA',
1899 P_Msg_Name => 'PA_RLM_FORMAT_INVALID');
1900
1901 Raise l_ERROR;
1902
1903 END;
1904
1905 END IF;
1906
1907
1908 --For bug 4055082.
1909 --Raise error if invalid project id is passed while creation of planning resource list memeber.
1910 IF (L_planning_resource_in_tbl(i).p_project_id is not null) Then --For bug 4094047
1911
1912 BEGIN
1913
1914 SELECT 'Y'
1915 INTO l_project_exists
1916 FROM pa_projects_all
1917 WHERE project_id = L_planning_resource_in_tbl(i).p_project_id;
1918
1919 EXCEPTION
1920 WHEN NO_DATA_FOUND THEN
1921 Pa_Utils.Add_Message(
1922 P_App_Short_Name => 'PA',
1923 P_Msg_Name => 'PA_AMG_PROJECT_ID_INVALID',
1924 p_token1 => 'PLAN_RES',
1925 p_value1 => Pa_Planning_Resource_Pvt.g_token);
1926
1927 Raise l_ERROR;
1928
1929 END;
1930
1931 END IF;
1932 --End of bug 4103909.
1933
1934 IF (L_planning_resource_in_tbl(i).p_res_format_id is not null) Then --For bug 4103909.
1935
1936 BEGIN
1937
1938 SELECT 'Y'
1939 INTO l_format_exists
1940 FROM pa_plan_rl_formats
1941 WHERE resource_list_id = L_Plan_res_list_Rec.p_resource_list_id
1942 AND res_format_id = L_planning_resource_in_tbl(i).p_res_format_id;
1943
1944 EXCEPTION
1945 WHEN NO_DATA_FOUND THEN
1946 Pa_Utils.Add_Message(
1947 P_App_Short_Name => 'PA',
1948 P_Msg_Name => 'PA_AMG_FORMAT_NOT_EXISTS',
1949 p_token1 => 'PLAN_RES',
1950 p_value1 => Pa_Planning_Resource_Pvt.g_token);
1951
1952 Raise l_ERROR;
1953
1954 END;
1955
1956 END IF;
1957 --End of bug 4103909.
1958
1959 SELECT meaning || ' ' || to_char(i) || ':'
1960 INTO Pa_Planning_Resource_Pvt.g_token
1961 FROM pa_lookups
1962 WHERE lookup_type = 'PA_PLANNING_RESOURCE'
1963 AND lookup_code = 'PLANNING_RESOURCE';
1964
1965 L_one_pln_res_in_tbl(1) := L_planning_resource_in_tbl(i);
1966 -- dbms_output.put_line('--- in create part of upd');
1967 Pa_Plan_Res_List_Pub.Create_Planning_Resource(
1968 p_commit => p_commit,
1969 p_init_msg_list => p_init_msg_list,
1970 p_resource_list_id => L_plan_res_list_Rec.p_resource_list_id,
1971 P_planning_resource_in_tbl => L_one_pln_res_in_tbl,
1972 X_planning_resource_out_tbl => L_one_pln_res_out_tbl,
1973 x_return_status => x_return_status,
1974 x_msg_count => l_msg_count,
1975 x_error_msg_data => l_msg_data);
1976 -- dbms_output.put_line('--- AFTER in create part of upd');
1977
1978 /*
1979 Pa_Planning_Resource_Pvt.Create_Planning_Resource(
1980 p_resource_list_member_id => L_planning_resource_in_tbl(i).p_resource_list_member_id,
1981 p_resource_list_id => L_plan_res_list_Rec.p_resource_list_id,
1982 p_resource_alias => L_planning_resource_in_tbl(i).p_resource_alias,
1983 p_person_id => L_planning_resource_in_tbl(i).p_person_id,
1984 p_person_name => L_planning_resource_in_tbl(i).p_person_name,
1985 p_job_id => L_planning_resource_in_tbl(i).p_job_id,
1986 p_job_name => L_planning_resource_in_tbl(i).p_job_name,
1987 p_organization_id => L_planning_resource_in_tbl(i).p_organization_id,
1988 p_organization_name => L_planning_resource_in_tbl(i).p_organization_name,
1989 p_vendor_id => L_planning_resource_in_tbl(i).p_vendor_id,
1990 p_vendor_name => L_planning_resource_in_tbl(i).p_vendor_name,
1991 p_fin_category_name => L_planning_resource_in_tbl(i).p_fin_category_name,
1992 p_non_labor_resource => L_planning_resource_in_tbl(i).p_non_labor_resource,
1993 p_project_role_id => L_planning_resource_in_tbl(i).p_project_role_id,
1994 p_project_role_name => L_planning_resource_in_tbl(i).p_project_role_name,
1995 p_resource_class_id => L_planning_resource_in_tbl(i).p_resource_class_id,
1996 p_resource_class_code => L_planning_resource_in_tbl(i).p_resource_class_code,
1997 p_res_format_id => L_planning_resource_in_tbl(i).p_res_format_id,
1998 p_spread_curve_id => L_planning_resource_in_tbl(i).p_spread_curve_id,
1999 p_etc_method_code => L_planning_resource_in_tbl(i).p_etc_method_code,
2000 p_mfc_cost_type_id => L_planning_resource_in_tbl(i).p_mfc_cost_type_id,
2001 p_copy_from_rl_flag => Null,
2002 p_resource_class_flag => l_res_class_flag,
2003 p_fc_res_type_code => L_planning_resource_in_tbl(i).p_fc_res_type_code,
2004 p_inventory_item_id => L_planning_resource_in_tbl(i).p_inventory_item_id,
2005 p_inventory_item_name => L_planning_resource_in_tbl(i).p_inventory_item_name,
2006 p_item_category_id => L_planning_resource_in_tbl(i).p_item_category_id,
2007 p_item_category_name => L_planning_resource_in_tbl(i).p_item_category_name,
2008 p_migration_code => 'N',
2009 p_attribute_category => L_planning_resource_in_tbl(i).p_attribute_category,
2010 p_attribute1 => L_planning_resource_in_tbl(i).p_attribute1,
2011 p_attribute2 => L_planning_resource_in_tbl(i).p_attribute2,
2012 p_attribute3 => L_planning_resource_in_tbl(i).p_attribute3,
2013 p_attribute4 => L_planning_resource_in_tbl(i).p_attribute4,
2014 p_attribute5 => L_planning_resource_in_tbl(i).p_attribute5,
2015 p_attribute6 => L_planning_resource_in_tbl(i).p_attribute6,
2016 p_attribute7 => L_planning_resource_in_tbl(i).p_attribute7,
2017 p_attribute8 => L_planning_resource_in_tbl(i).p_attribute8,
2018 p_attribute9 => L_planning_resource_in_tbl(i).p_attribute9,
2019 p_attribute10 => L_planning_resource_in_tbl(i).p_attribute10,
2020 p_attribute11 => L_planning_resource_in_tbl(i).p_attribute11,
2021 p_attribute12 => L_planning_resource_in_tbl(i).p_attribute12,
2022 p_attribute13 => L_planning_resource_in_tbl(i).p_attribute13,
2023 p_attribute14 => L_planning_resource_in_tbl(i).p_attribute14,
2024 p_attribute15 => L_planning_resource_in_tbl(i).p_attribute15,
2025 p_attribute16 => L_planning_resource_in_tbl(i).p_attribute16,
2026 p_attribute17 => L_planning_resource_in_tbl(i).p_attribute17,
2027 p_attribute18 => L_planning_resource_in_tbl(i).p_attribute18,
2028 p_attribute19 => L_planning_resource_in_tbl(i).p_attribute19,
2029 p_attribute20 => L_planning_resource_in_tbl(i).p_attribute20,
2030 p_attribute21 => L_planning_resource_in_tbl(i).p_attribute21,
2031 p_attribute22 => L_planning_resource_in_tbl(i).p_attribute22,
2032 p_attribute23 => L_planning_resource_in_tbl(i).p_attribute23,
2033 p_attribute24 => L_planning_resource_in_tbl(i).p_attribute24,
2034 p_attribute25 => L_planning_resource_in_tbl(i).p_attribute25,
2035 p_attribute26 => L_planning_resource_in_tbl(i).p_attribute26,
2036 p_attribute27 => L_planning_resource_in_tbl(i).p_attribute27,
2037 p_attribute28 => L_planning_resource_in_tbl(i).p_attribute28,
2038 p_attribute29 => L_planning_resource_in_tbl(i).p_attribute29,
2039 p_attribute30 => L_planning_resource_in_tbl(i).p_attribute30,
2040 p_person_type_code => L_planning_resource_in_tbl(i).p_person_type_code,
2041 p_bom_resource_id => L_planning_resource_in_tbl(i).p_bom_resource_id,
2042 p_bom_resource_name => L_planning_resource_in_tbl(i).p_bom_resource_name,
2043 p_team_role => L_planning_resource_in_tbl(i).p_team_role,
2044 p_incur_by_res_code => L_planning_resource_in_tbl(i).p_incur_by_res_code,
2045 p_incur_by_res_type => L_planning_resource_in_tbl(i).p_incur_by_res_type,
2046 p_project_id => L_planning_resource_in_tbl(i).p_project_id,
2047 x_resource_list_member_id => X_planning_resource_out_tbl(i).x_resource_list_member_id,
2048 x_record_version_number => X_planning_resource_out_tbl(i).x_record_version_number,
2049 x_return_status => x_return_status,
2050 x_msg_count => l_msg_count,
2051 x_error_msg_data => l_msg_data);
2052
2053 */
2054
2055 -- dbms_output.put_line('after X_return_status IS : ' || X_return_status);
2056 -- dbms_output.put_line('after l_msg_data IS : ' || l_msg_data);
2057 If X_return_status <> Fnd_Api.G_Ret_Sts_Success THEN
2058
2059 l_return_status := Fnd_Api.G_Ret_Sts_Error;
2060 x_return_status := Fnd_Api.G_Ret_Sts_Error;
2061 x_msg_count := x_msg_count + 1;
2062 x_msg_data := l_msg_data;
2063 Pa_Utils.Add_Message ('PA', x_msg_data);
2064 Rollback ;
2065 Return;
2066
2067 Else
2068
2069 l_return_status := Fnd_Api.G_Ret_Sts_Success;
2070 X_planning_resource_out_tbl(i) := L_one_pln_res_out_tbl(1);
2071
2072 End If;
2073
2074 End If;
2075
2076 If L_planning_resource_in_tbl(i).p_resource_list_member_id is not null Then --For bug 4103909
2077 --This means that the resource list member is already existing and is supposed to be updated.
2078
2079 SELECT meaning || ' ' || to_char(i) || ':'
2080 INTO Pa_Planning_Resource_Pvt.g_token
2081 FROM pa_lookups
2082 WHERE lookup_type = 'PA_PLANNING_RESOURCE'
2083 AND lookup_code = 'PLANNING_RESOURCE';
2084
2085 -- Validate the record version number passed in.
2086 IF L_planning_resource_in_tbl(i).p_record_version_number is null OR
2087 L_planning_resource_in_tbl(i).p_record_version_number <> l_rlm_record_version_number
2088 THEN
2089 Pa_Utils.Add_Message(
2090 P_App_Short_Name => 'PA',
2091 P_Msg_Name => 'PA_RLM_REC_VER_NOT_VALID',
2092 p_token1 => 'PLAN_RES',
2093 p_value1 => Pa_Planning_Resource_Pvt.g_token);
2094
2095 Raise l_ERROR;
2096 END IF;
2097
2098 Pa_Planning_Resource_Pvt.Update_Planning_Resource(
2099 p_resource_list_id => L_plan_res_list_Rec.p_resource_list_id,
2100 p_resource_list_member_id => L_planning_resource_in_tbl(i).p_resource_list_member_id,
2101 p_enabled_flag => L_planning_resource_in_tbl(i).p_enabled_flag,
2102 p_resource_alias => L_planning_resource_in_tbl(i).p_resource_alias,
2103 p_spread_curve_id => L_planning_resource_in_tbl(i).p_spread_curve_id,
2104 p_etc_method_code => L_planning_resource_in_tbl(i).p_etc_method_code,
2105 p_mfc_cost_type_id => L_planning_resource_in_tbl(i).p_mfc_cost_type_id,
2106 p_attribute_category => L_planning_resource_in_tbl(i).p_attribute_category,
2107 p_attribute1 => L_planning_resource_in_tbl(i).p_attribute1,
2108 p_attribute2 => L_planning_resource_in_tbl(i).p_attribute2,
2109 p_attribute3 => L_planning_resource_in_tbl(i).p_attribute3,
2110 p_attribute4 => L_planning_resource_in_tbl(i).p_attribute4,
2111 p_attribute5 => L_planning_resource_in_tbl(i).p_attribute5,
2112 p_attribute6 => L_planning_resource_in_tbl(i).p_attribute6,
2113 p_attribute7 => L_planning_resource_in_tbl(i).p_attribute7,
2114 p_attribute8 => L_planning_resource_in_tbl(i).p_attribute8,
2115 p_attribute9 => L_planning_resource_in_tbl(i).p_attribute9,
2116 p_attribute10 => L_planning_resource_in_tbl(i).p_attribute10,
2117 p_attribute11 => L_planning_resource_in_tbl(i).p_attribute11,
2118 p_attribute12 => L_planning_resource_in_tbl(i).p_attribute12,
2119 p_attribute13 => L_planning_resource_in_tbl(i).p_attribute13,
2120 p_attribute14 => L_planning_resource_in_tbl(i).p_attribute14,
2121 p_attribute15 => L_planning_resource_in_tbl(i).p_attribute15,
2122 p_attribute16 => L_planning_resource_in_tbl(i).p_attribute16,
2123 p_attribute17 => L_planning_resource_in_tbl(i).p_attribute17,
2124 p_attribute18 => L_planning_resource_in_tbl(i).p_attribute18,
2125 p_attribute19 => L_planning_resource_in_tbl(i).p_attribute19,
2126 p_attribute20 => L_planning_resource_in_tbl(i).p_attribute20,
2127 p_attribute21 => L_planning_resource_in_tbl(i).p_attribute21,
2128 p_attribute22 => L_planning_resource_in_tbl(i).p_attribute22,
2129 p_attribute23 => L_planning_resource_in_tbl(i).p_attribute23,
2130 p_attribute24 => L_planning_resource_in_tbl(i).p_attribute24,
2131 p_attribute25 => L_planning_resource_in_tbl(i).p_attribute25,
2132 p_attribute26 => L_planning_resource_in_tbl(i).p_attribute26,
2133 p_attribute27 => L_planning_resource_in_tbl(i).p_attribute27,
2134 p_attribute28 => L_planning_resource_in_tbl(i).p_attribute28,
2135 p_attribute29 => L_planning_resource_in_tbl(i).p_attribute29,
2136 p_attribute30 => L_planning_resource_in_tbl(i).p_attribute30,
2137 p_record_version_number => L_planning_resource_in_tbl(i).p_record_version_number,
2138 x_record_version_number => x_planning_resource_out_tbl(i).x_record_version_number,
2139 x_return_status => x_return_status,
2140 x_msg_count => l_msg_count,
2141 x_error_msg_data => l_msg_data);
2142
2143 If x_return_status <> Fnd_Api.G_Ret_Sts_Success Then
2144
2145 l_return_status := Fnd_Api.G_Ret_Sts_Error;
2146 x_return_status := Fnd_Api.G_Ret_Sts_Error;
2147 x_msg_count := x_msg_count + 1;
2148 x_msg_data := l_msg_data;
2149 Pa_Utils.Add_Message ('PA', x_msg_data);
2150 Rollback ;
2151 Return;
2152
2153 Else
2154
2155 l_return_status := Fnd_Api.G_Ret_Sts_Success;
2156
2157 End If;
2158
2159 End If;
2160 End Loop;
2161 END IF;
2162 -- Commit only if all records are successful.
2163 /************************************************
2164 * Check the Commit flag. if it is true then Commit.
2165 ************************************************/
2166 If l_return_status = Fnd_Api.G_Ret_Sts_Success Then
2167
2168 If Fnd_Api.To_Boolean( p_commit ) Then
2169
2170 Commit;
2171
2172 End If;
2173
2174 End If;
2175
2176
2177 Exception
2178 When l_ERROR Then
2179 l_Msg_Count := Fnd_Msg_Pub.Count_Msg;
2180
2181
2182 If l_Msg_Count = 1 Then
2183
2184 Pa_Interface_Utils_Pub.Get_Messages(
2185 P_Encoded => Fnd_Api.G_False,
2186 P_Msg_Index => 1,
2187 P_Msg_Count => l_Msg_Count,
2188 P_Msg_Data => l_Msg_Data,
2189 P_Data => l_Data,
2190 P_Msg_Index_Out => l_Msg_Index_Out);
2191
2192 X_Msg_Data := l_Data;
2193 X_Msg_Count := l_Msg_Count;
2194
2195 Else
2196
2197 X_Msg_Count := l_Msg_Count;
2198
2199 End If;
2200 X_Return_Status := Fnd_Api.G_Ret_Sts_Error;
2201 Rollback;
2202 When Others Then
2203 x_return_status := Fnd_Api.G_Ret_Sts_UnExp_Error;
2204 -- 4537865
2205 X_Msg_Data := SUBSTRB(SQLERRM,1,240);
2206 X_Msg_Count := X_Msg_Count + 1;
2207 -- 4537865 : End
2208 Rollback ;
2209 Fnd_Msg_Pub.Add_Exc_Msg(
2210 p_pkg_name => G_PKG_NAME ,
2211 p_procedure_name => l_api_name);
2212 Return;
2213
2214 End Update_Resource_List;
2215
2216 /***********************************************************
2217 * Procedure : Delete_Resource_List
2218 * Description : AMG API, used to Delete a resource list
2219 * and its corresponding members and formats.
2220 * The detailed information is in the sepc.
2221 *********************************************************/
2222 PROCEDURE Delete_Resource_List(
2223 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2224 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2225 p_api_version_number IN NUMBER,
2226 P_Res_List_Id IN NUMBER ,
2227 X_Return_Status OUT NOCOPY VARCHAR2,
2228 X_Msg_Count OUT NOCOPY NUMBER,
2229 X_Msg_Data OUT NOCOPY VARCHAR2)
2230 IS
2231 l_api_version_number CONSTANT NUMBER := G_API_VERSION_NUMBER;
2232 l_api_name CONSTANT VARCHAR2(30) := 'Update_Resource_List';
2233 l_return_status Varchar2(30);
2234 l_msg_count Number := 0;
2235 l_msg_data Varchar2(100);
2236 l_check_exists Varchar2(1);
2237 l_exist_res_list Varchar2(1);
2238 l_err_code Number;
2239 l_res_list_member_id_tbl SYSTEM.PA_NUM_TBL_TYPE :=
2240 SYSTEM.PA_NUM_TBL_TYPE();
2241 l_res_format_id_tbl SYSTEM.PA_NUM_TBL_TYPE :=
2242 SYSTEM.PA_NUM_TBL_TYPE();
2243 l_Plan_rl_format_id_tbl SYSTEM.PA_NUM_TBL_TYPE :=
2244 SYSTEM.PA_NUM_TBL_TYPE();
2245 API_ERROR Exception;
2246 BEGIN
2247 Pa_Planning_Resource_Pvt.g_amg_flow := 'Y';
2248 --Initialize the message stack before starting any further processing.
2249 IF FND_API.to_boolean( p_init_msg_list )
2250 THEN
2251 FND_MSG_PUB.initialize;
2252 END IF;
2253
2254 --Initialize the Out Variables.
2255 x_return_status := FND_API.G_RET_STS_SUCCESS;
2256 x_msg_count := 0;
2257
2258 --Set a Savepoint so that if error occurs at any stage we can
2259 -- rollback all the changes.
2260 --SAVEPOINT Delete_Resource_List_Pub;
2261
2262 --Check for the Compatibility of the API Version
2263 --This is a must for AMG API's
2264 --Doubt -- does this have to be done for all the api's/the main one??
2265 IF NOT FND_API.Compatible_API_Call
2266 ( l_api_version_number ,
2267 p_api_version_number ,
2268 l_api_name ,
2269 G_PKG_NAME )
2270 THEN
2271 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2272 END IF;
2273
2274 /* Bug 5490759 - first check whether the list is used so that that
2275 * error message can be shown first, instead of a cryptic downstream
2276 * error when trying to delete a format */
2277 l_err_code := 0;
2278
2279 PA_GET_RESOURCE.delete_resource_list_ok(
2280 p_res_list_id, 'Y', l_err_code, l_msg_data);
2281 IF l_err_code <> 0 THEN
2282 l_return_status := FND_API.G_RET_STS_ERROR;
2283 l_msg_count := l_msg_count + 1;
2284 RAISE API_ERROR;
2285 END IF;
2286
2287 /**************************************************
2288 * Call to the Delete_Planning_Resources procedure
2289 * to Delete all the members from the pa_resource_list_members
2290 * table. We are passing a table of resource_list_member_id's
2291 * to the procedure.
2292 **************************************************/
2293 /*****************************************************
2294 * First Retrieve all the resource list member id's
2295 * belonging to the resource_list_id passed.
2296 * Bulk collect into a PL/SQL table l_res_list_member_id_tbl.
2297 * ******************************************************/
2298 BEGIN
2299 SELECT resource_list_member_id
2300 BULK COLLECT INTO l_res_list_member_id_tbl
2301 FROM Pa_resource_list_members
2302 WHERE resource_list_id = p_res_list_id;
2303 EXCEPTION
2304 WHEN OTHERS THEN
2305 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2306 END;
2307
2308 IF l_res_list_member_id_tbl.COUNT > 0 THEN
2309 FOR i IN l_res_list_member_id_tbl.first.. l_res_list_member_id_tbl.last
2310 LOOP
2311 /*******************************************************
2312 * Call to API Pa_Planning_Resource_Pvt.Delete_Planning_Resource
2313 * passing the pl/sql table od resource_list_member_id's
2314 * This API would take care of deletion.
2315 ********************************************************/
2316 SELECT meaning || ' ' || to_char(i) || ':'
2317 INTO Pa_Planning_Resource_Pvt.g_token
2318 FROM pa_lookups
2319 WHERE lookup_type = 'PA_PLANNING_RESOURCE'
2320 AND lookup_code = 'PLANNING_RESOURCE';
2321
2322 Pa_Planning_Resource_Pvt.Delete_Planning_Resource(
2323 p_resource_list_member_id => l_res_list_member_id_tbl(i),
2324 x_return_status => l_return_status,
2325 x_msg_count => l_msg_count,
2326 x_error_msg_data => l_msg_data);
2327 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2328 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2329 ROLLBACK ;
2330 Return;
2331 END IF;
2332 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
2333 IF FND_API.to_boolean( p_commit )
2334 THEN
2335 COMMIT;
2336 END IF;
2337 END IF;
2338 END LOOP;
2339 END IF;
2340
2341 /*****************************************************
2342 * First Retrieve all the res_format_id's and
2343 * plan_rl_format_id's belonging to the resource_list_id
2344 * passed. Bulk collect into a PL/SQL table
2345 * l_res_format_id_tbl and l_plan_rl_format_id_tbl.
2346 * ******************************************************/
2347 BEGIN
2348 SELECT res_format_id,plan_rl_format_id
2349 BULK COLLECT INTO l_res_format_id_tbl,l_plan_rl_format_id_tbl
2350 FROM Pa_Plan_rl_formats
2351 WHERE resource_list_id = p_res_list_id;
2352 EXCEPTION
2353 WHEN OTHERS THEN
2354 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2355 END;
2356
2357 IF l_res_format_id_tbl.COUNT > 0 THEN
2358 FOR i IN l_res_format_id_tbl.first..l_res_format_id_tbl.last
2359 LOOP
2360 /**************************************************
2361 * Call to the Pa_Plan_RL_Formats_Pvt.Delete_Plan_RL_Format API
2362 * to Delete all the formats from the pa_plan_rl_formats
2363 * table. We are passing table elements
2364 **************************************************/
2365 Pa_Plan_RL_Formats_Pvt.Delete_Plan_RL_Format (
2366 P_Res_List_Id => P_res_list_id,
2367 P_Res_Format_Id => l_res_format_id_tbl(i),
2368 P_Plan_RL_Format_Id => l_plan_rl_format_id_tbl(i),
2369 X_Return_Status => l_return_status,
2370 X_Msg_Count => l_msg_count,
2371 X_Msg_Data => l_msg_data);
2372 --For bug 3810204
2373 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2374 ROLLBACK ;
2375 RAISE API_ERROR;
2376
2377 END IF;--End of bug 3810204
2378
2379 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2380 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2381 ROLLBACK ;
2382 Return;
2383 END IF;
2384
2385 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
2386 IF FND_API.to_boolean( p_commit )
2387 THEN
2388 COMMIT;
2389 END IF;
2390 END IF;
2391 END LOOP;
2392 END IF;
2393
2394 /*****************************************************
2395 * Check if the resource_list_id passed exists in the
2396 * pa_resource_list_members table or in the pa_plan_rl_formats
2397 * table. If it does then ewe cannot delete the record from
2398 * the pa_resource_lists_all_bg table, as corr child members
2399 * are present.
2400 *****************************************************/
2401 BEGIN
2402 SELECT 'Y'
2403 INTO l_exist_res_list
2404 FROM DUAL
2405 WHERE EXISTS
2406 (SELECT 'Y' from pa_resource_list_members
2407 WHERE resource_list_id = P_res_list_id
2408 UNION
2409 SELECT 'Y' from pa_plan_rl_formats
2410 WHERE resource_list_id = P_res_list_id);
2411 EXCEPTION
2412 WHEN NO_DATA_FOUND THEN
2413 l_exist_res_list := 'N';
2414 END;
2415
2416 IF l_exist_res_list = 'Y' THEN
2417 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2418 ROLLBACK ;
2419 Return;
2420 END IF;
2421
2422 IF l_exist_res_list = 'N' THEN
2423 -- Call procedure to delete from base and TL tables
2424 PA_CREATE_RESOURCE.Delete_Plan_Res_List (
2425 p_resource_list_id => P_res_list_id,
2426 X_Return_Status => l_return_status,
2427 X_Msg_Count => l_msg_count,
2428 X_Msg_Data => l_msg_data);
2429
2430 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2431 --For bug 3810204
2432 ROLLBACK ;
2433 RAISE API_ERROR;
2434 --End of bug 3810204
2435 END IF;
2436
2437 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2438 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2439 ROLLBACK ;
2440 Return;
2441 END IF;
2442
2443 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
2444 IF FND_API.to_boolean( p_commit ) THEN
2445 COMMIT;
2446 END IF;
2447 END IF;
2448 END IF;
2449
2450
2451 EXCEPTION
2452 WHEN API_ERROR THEN
2453 x_return_status := l_return_status;
2454 X_Msg_Data := l_msg_data;
2455 x_msg_count := l_msg_count;
2456 Pa_Utils.Add_Message
2457 (P_App_Short_Name => 'PA',
2458 P_Msg_Name => l_msg_data);
2459
2460 WHEN OTHERS THEN
2461 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2462 -- 4537865 : Start
2463 X_Msg_Data := SUBSTRB(SQLERRM,1,240);
2464 x_msg_count := x_msg_count + 1 ;
2465 -- 4537865 : End
2466 ROLLBACK ;
2467 FND_MSG_PUB.Add_Exc_Msg
2468 ( p_pkg_name => G_PKG_NAME ,
2469 p_procedure_name => l_api_name
2470 );
2471 Return;
2472
2473 END Delete_Resource_List;
2474
2475
2476 /************************************************************
2477 * Procedure : Create_Plan_RL_Format
2478 * Description : This procedure is used the pass a Table of
2479 * Record, and call the
2480 * Pa_Plan_RL_Formats_Pvt.Create_Plan_RL_Format
2481 * procedure, which would create the res formats.
2482 * The detailed information is in the spec.
2483 **************************************************************/
2484 Procedure Create_Plan_RL_Format(
2485 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
2486 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
2487 P_Res_List_Id IN Number,
2488 P_Plan_RL_Format_Tbl IN Plan_RL_Format_In_Tbl,
2489 X_Plan_RL_Format_Tbl OUT NOCOPY Plan_RL_Format_Out_Tbl,
2490 X_Return_Status OUT NOCOPY Varchar2,
2491 X_Msg_Count OUT NOCOPY Number,
2492 X_Msg_Data OUT NOCOPY Varchar2)
2493 IS
2494
2495 L_Plan_RL_Format_Tbl Plan_RL_Format_In_Tbl;
2496 INVALID_FMT_ERR Exception;
2497 l_format_exists VARCHAR2(1);
2498
2499 BEGIN
2500
2501 -- First clear the message stack.
2502 IF FND_API.to_boolean( p_init_msg_list )
2503 THEN
2504 FND_MSG_PUB.initialize;
2505 END IF;
2506
2507 x_msg_count := 0;
2508 x_return_status := FND_API.G_RET_STS_SUCCESS;
2509
2510 Convert_Missing_Format_IN_Rec(
2511 P_Plan_RL_Format_Tbl => P_Plan_RL_Format_Tbl,
2512 X_Plan_RL_Format_Tbl => L_Plan_RL_Format_Tbl);
2513
2514 /***************************************************************
2515 * For Loop. To loop through the table of records and
2516 * Validate each one of them and insert accordingly.
2517 ****************************************************************/
2518 FOR i IN 1..P_Plan_RL_Format_Tbl.COUNT
2519 LOOP
2520
2521 --For bug 3675288.
2522 BEGIN
2523
2524 l_format_exists:='N';
2525
2526 --Checks if the format is valid one or not.
2527 -- If 'Y' Then format is valid.
2528
2529 Select 'Y'
2530 Into l_format_exists
2531 From pa_res_formats_b
2532 Where res_format_id=L_Plan_RL_Format_Tbl(i).P_Res_Format_Id;
2533
2534 EXCEPTION
2535 WHEN Others THEN
2536 l_format_exists:='N';
2537
2538 END;
2539
2540 If l_format_exists = 'N' Then
2541
2542 Raise INVALID_FMT_ERR;
2543
2544 End If;
2545 --End of bug 3675288.
2546
2547 Pa_Plan_RL_Formats_Pvt.Create_Plan_RL_Format(
2548 P_Res_List_Id =>P_Res_List_Id,
2549 P_Res_Format_Id =>L_Plan_RL_Format_Tbl(i).P_Res_Format_Id,
2550 X_Plan_RL_Format_Id =>X_Plan_RL_Format_Tbl(i).X_Plan_RL_Format_Id,
2551 X_Record_Version_Number =>
2552 X_Plan_RL_Format_Tbl(i).X_Record_Version_Number,
2553 X_Return_Status =>x_return_status,
2554 X_Msg_Count =>X_Msg_Count,
2555 X_Msg_Data =>X_Msg_Data);
2556
2557 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
2558 THEN
2559 x_return_status := FND_API.G_RET_STS_ERROR;
2560 x_msg_count := x_msg_count + 1;
2561 x_msg_data := x_Msg_Data;
2562 Rollback;
2563 Return;
2564 END IF;
2565 END LOOP;
2566 /************************************************
2567 * Check the Commit flag. if it is true then Commit.
2568 ************************************************/
2569 IF FND_API.to_boolean( p_commit )
2570 THEN
2571 COMMIT;
2572 END IF;
2573 EXCEPTION
2574 WHEN INVALID_FMT_ERR THEN
2575
2576 x_return_status := FND_API.G_RET_STS_ERROR;
2577 x_msg_count := x_msg_count + 1;
2578 x_msg_data := 'PA_AMG_INVALID_FMT_ID';
2579 Pa_Utils.Add_Message
2580 (P_App_Short_Name => 'PA',
2581 P_Msg_Name => 'PA_AMG_INVALID_FMT_ID');
2582 WHEN OTHERS THEN
2583 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2584 x_msg_count := x_msg_count + 1;
2585 -- 4537865
2586 x_msg_data := SUBSTRB(SQLERRM,1,240);
2587 Fnd_Msg_Pub.Add_Exc_Msg(
2588 P_Pkg_Name => 'Pa_Plan_Res_List_Pub',
2589 P_Procedure_Name => 'Create_Plan_RL_Format',
2590 P_error_text => x_msg_data);
2591 END Create_Plan_RL_Format;
2592 /***************************/
2593
2594 /************************************************************
2595 * Procedure : Delete_Plan_RL_Format
2596 * Description : This procedure is used the pass a Table of
2597 * Record, and call the
2598 * Pa_Plan_RL_Formats_Pvt.Delete_Plan_RL_Format
2599 * procedure, which would Delete the res formats.
2600 * The detailed information is in the spec.
2601 **************************************************************/
2602 Procedure Delete_Plan_RL_Format (
2603 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
2604 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
2605 P_Res_List_Id IN NUMBER DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
2606 P_Plan_RL_Format_Tbl IN Plan_RL_Format_In_Tbl ,
2607 X_Return_Status OUT NOCOPY VARCHAR2,
2608 X_Msg_Count OUT NOCOPY NUMBER,
2609 X_Msg_Data OUT NOCOPY VARCHAR2)
2610 IS
2611
2612 L_Plan_RL_Format_Tbl Plan_RL_Format_In_Tbl;
2613
2614 BEGIN
2615 -- First clear the message stack.
2616 IF FND_API.to_boolean( p_init_msg_list )
2617 THEN
2618 FND_MSG_PUB.initialize;
2619 END IF;
2620
2621 Convert_Missing_Format_IN_Rec(
2622 P_Plan_RL_Format_Tbl => P_Plan_RL_Format_Tbl,
2623 X_Plan_RL_Format_Tbl => L_Plan_RL_Format_Tbl);
2624
2625 /***************************************************************
2626 * For Loop. To loop through the table of records and
2627 * Validate each one of them and Update accordingly.
2628 ****************************************************************/
2629 FOR i IN 1..P_Plan_RL_Format_Tbl.COUNT
2630 LOOP
2631
2632 Pa_Plan_RL_Formats_pvt.Delete_Plan_RL_Format (
2633 P_Res_List_Id =>P_Res_List_Id,
2634 P_Res_Format_Id =>L_Plan_RL_Format_Tbl(i).P_Res_Format_Id,
2635 P_Plan_RL_Format_Id =>Null,
2636 X_Return_Status =>X_Return_Status,
2637 X_Msg_Count =>X_Msg_Count,
2638 X_Msg_Data =>X_Msg_Data);
2639
2640 END LOOP;
2641 /************************************************
2642 * Check the Commit flag. if it is true then Commit.
2643 ************************************************/
2644 IF FND_API.to_boolean( p_commit )
2645 THEN
2646 COMMIT;
2647 END IF;
2648 END Delete_Plan_RL_Format;
2649 /***************************/
2650
2651 /**************************************************************
2652 * Procedure : Create_Planning_Resource
2653 * Description : The purpose of this procedure is to Validate
2654 * and create a new planning resource for a
2655 * resource list.
2656 * The detailed information is in the spec.
2657 ****************************************************************/
2658 Procedure Create_Planning_Resource(
2659 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
2660 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
2661 p_resource_list_id IN VARCHAR2,
2662 P_planning_resource_in_tbl IN Planning_Resource_In_Tbl,
2663 X_planning_resource_out_tbl OUT NOCOPY Planning_Resource_Out_Tbl,
2664 x_return_status OUT NOCOPY VARCHAR2,
2665 x_msg_count OUT NOCOPY NUMBER,
2666 x_error_msg_data OUT NOCOPY VARCHAR2 )
2667 Is
2668
2669 l_res_class_flag Varchar2(1) := Null;
2670 L_Planning_resource_in_tbl Planning_Resource_In_Tbl;
2671 l_mode Number :=0;
2672 l_resource Varchar2(100);
2673 l_financial Varchar2(100);
2674 l_organization Varchar2(100);
2675 l_supplier Varchar2(100);
2676 l_role Varchar2(100);
2677 l_incurred Varchar2(100);
2678 FMT_NULL_ERR Exception;
2679 RESOURCE_CLASS_FMT_ERR Exception;
2680 RES_CLASS_ID_AND_CODE_NULL Exception;
2681 FIN_NULL_ERR Exception;
2682 FIN_NOT_NULL_ERR Exception;
2683 BOM_LAB_N_EQUIP_NULL_ERR Exception;
2684 NAMED_PER_NULL_ERR Exception;
2685 ITEM_CAT_NULL_ERR Exception;
2686 INVEN_ITEM_NULL_ERR Exception;
2687 JOB_NULL_ERR Exception;
2688 PERSON_TYPE_NULL_ERR Exception;
2689 NON_LABOR_RES_NULL_ERR Exception;
2690 ORG_NULL_ERR Exception;
2691 ORG_NOT_NULL_ERR Exception;
2692 SUPP_NULL_ERR Exception;
2693 SUPP_NOT_NULL_ERR Exception;
2694 ROLE_NULL_ERR Exception;
2695 ROLE_NOT_NULL_ERR Exception;
2696 INC_NULL_ERR Exception;
2697 INC_NOT_NULL_ERR Exception;
2698 RES_NOT_NULL_ERR Exception;
2699 TOO_MANY_PMT_FOR_INCUR Exception;
2700 RES_CODE_INVALID_ERR Exception;
2701 RES_ID_INVALID_ERR Exception;
2702 INVALID_PROJECT_ID Exception;
2703 RES_FORMAT_ID_ERR Exception;
2704 l_project_exists Varchar2(1);
2705 l_format_exists Varchar2(1);
2706 l_validate_resource_id Number;
2707 l_validate_resource_code Number;
2708 P_Plan_res_list_Rec Plan_Res_List_IN_Rec;
2709
2710 l_count Number;
2711 Begin
2712
2713 Pa_Planning_Resource_Pvt.g_amg_flow := 'Y';
2714 -- First clear the message stack.
2715 If Fnd_Api.To_Boolean( P_Init_Msg_List ) Then
2716
2717 Fnd_Msg_Pub.Initialize;
2718
2719 End If;
2720
2721
2722 Convert_Missing_Member_IN_Rec(
2723 P_Planning_resource_in_tbl => P_Planning_resource_in_tbl,
2724 P_Plan_res_list_Rec => P_Plan_res_list_Rec,
2725 X_Planning_resource_in_tbl => L_Planning_resource_in_tbl,
2726 P_Mode => l_mode);
2727
2728
2729 /***************************************************************
2730 * For Loop. To loop through the table of records and
2731 * Validate each one of them and insert accordingly.
2732 **************************************************************/
2733 For i IN 1..L_Planning_Resource_In_Tbl.Count
2734 Loop
2735
2736 SELECT meaning || ' ' || to_char(i) || ':'
2737 INTO Pa_Planning_Resource_Pvt.g_token
2738 FROM pa_lookups
2739 WHERE lookup_type = 'PA_PLANNING_RESOURCE'
2740 AND lookup_code = 'PLANNING_RESOURCE';
2741
2742 savepoint planning_resource_create;
2743
2744 /*************************************************
2745 * Assigning Initial values for some of the elements.
2746 *************************************************/
2747 x_msg_count := 0;
2748 x_return_status := FND_API.G_RET_STS_SUCCESS;
2749
2750 --For bug 3675288.
2751 IF L_Planning_resource_in_tbl(i).P_Res_Format_Id is NULL THEN
2752
2753 RAISE FMT_NULL_ERR;
2754
2755 END IF;
2756
2757 IF L_Planning_resource_in_tbl(i).P_Resource_Class_Code is NULL THEN
2758 IF L_Planning_resource_in_tbl(i).P_Resource_Class_id is NULL THEN
2759
2760 RAISE RES_CLASS_ID_AND_CODE_NULL;
2761
2762 END IF;
2763 END IF;
2764
2765 --For bug 3810000
2766 IF L_Planning_resource_in_tbl(i).P_Resource_Class_Code is not NULL Then
2767 select count(*) INTO l_validate_resource_code
2768 FROM pa_resource_classes_b
2769 WHERE resource_class_code=L_Planning_resource_in_tbl(i).P_Resource_Class_Code;
2770 IF l_validate_resource_code <> 0 Then
2771 Null;
2772 Else
2773 RAISE RES_CODE_INVALID_ERR;
2774 END IF;
2775 END IF;
2776
2777 IF L_Planning_resource_in_tbl(i).P_Resource_Class_id is not NULL Then
2778 select count(*) INTO l_validate_resource_id
2779 FROM pa_resource_classes_b
2780 WHERE resource_class_id=L_Planning_resource_in_tbl(i).P_Resource_Class_id;
2781 IF l_validate_resource_id <> 0 Then
2782 Null;
2783 Else
2784 RAISE RES_ID_INVALID_ERR;
2785 END IF;
2786 END IF;
2787 --End of bug 3810000
2788
2789 BEGIN
2790
2791 SELECT decode(f.RES_TYPE_ENABLED_FLAG, 'Y', t.res_type_code, NULL) ,
2792 decode(f.FIN_CAT_ENABLED_FLAG, 'Y', 'Financial Category', NULL) ,
2793 decode(f.ORGN_ENABLED_FLAG, 'Y', 'Organization', NULL) ,
2794 decode(f.SUPPLIER_ENABLED_FLAG, 'Y', 'Supplier', NULL) ,
2795 decode(f.ROLE_ENABLED_FLAG, 'Y', 'Role', NULL) ,
2796 decode(f.INCURRED_BY_ENABLED_FLAG, 'Y', 'Incurred By', NULL)
2797 INTO
2798 l_resource,
2799 l_financial,
2800 l_organization,
2801 l_supplier,
2802 l_role,
2803 l_incurred
2804 FROM pa_res_formats_b f, pa_res_types_b t
2805 WHERE f.res_format_id = L_Planning_resource_in_tbl(i).P_Res_Format_Id
2806 AND f.RES_TYPE_ID = t.res_type_id(+);
2807
2808 EXCEPTION WHEN OTHERS THEN
2809 x_return_status := Fnd_Api.G_Ret_Sts_Error;
2810 x_msg_count:=1;
2811 x_error_msg_data := 'PA_PLN_RL_FORMAT_BAD_FMT_ID';
2812 Pa_Utils.Add_Message
2813 (P_App_Short_Name => 'PA',
2814 P_Msg_Name => 'PA_PLN_RL_FORMAT_BAD_FMT_ID',
2815 p_token1 => 'PLAN_RES',
2816 p_value1 => Pa_Planning_Resource_Pvt.g_token);
2817 RETURN;
2818 END;
2819
2820 IF (L_planning_resource_in_tbl(i).p_res_format_id is not null) Then --For bug 4103909.
2821
2822 BEGIN
2823
2824 SELECT 'Y'
2825 INTO l_format_exists
2826 FROM pa_plan_rl_formats
2827 WHERE resource_list_id = p_resource_list_id
2828 AND res_format_id = L_planning_resource_in_tbl(i).p_res_format_id;
2829
2830 EXCEPTION
2831 WHEN NO_DATA_FOUND THEN
2832
2833 Raise RES_FORMAT_ID_ERR;
2834
2835 END;
2836
2837 END IF;
2838 --End of bug 4103909.
2839
2840
2841 IF l_resource is not null THEN
2842
2843 IF l_resource IN ('BOM_LABOR' , 'BOM_EQUIPMENT') THEN
2844 If L_Planning_resource_in_tbl(i).p_bom_resource_id is Null Then
2845 IF L_Planning_resource_in_tbl(i).p_bom_resource_name is Null Then
2846 Raise BOM_LAB_N_EQUIP_NULL_ERR;
2847 END IF;
2848 END IF;
2849 END IF;
2850
2851 IF l_resource = 'NAMED_PERSON' THEN
2852 If L_Planning_resource_in_tbl(i).p_person_id is Null Then
2853 IF L_Planning_resource_in_tbl(i).p_person_name is NULL THEN
2854 Raise NAMED_PER_NULL_ERR;
2855 End IF;
2856 End IF;
2857 End IF;
2858
2859
2860 If l_resource = 'ITEM_CATEGORY' THEN
2861 If L_Planning_resource_in_tbl(i).p_item_category_id is NULL THEN
2862 IF L_Planning_resource_in_tbl(i).p_item_category_name is NULL THEN
2863 Raise ITEM_CAT_NULL_ERR;
2864 END IF;
2865 END IF;
2866 END IF;
2867
2868 If l_resource = 'INVENTORY_ITEM' Then
2869 If L_Planning_resource_in_tbl(i).p_inventory_item_id is NULL THEN
2870 IF L_Planning_resource_in_tbl(i).p_inventory_item_name is NULL THEN
2871 Raise INVEN_ITEM_NULL_ERR;
2872 END IF;
2873 END IF;
2874 END IF;
2875
2876 If l_resource = 'JOB' THEN
2877 If L_Planning_resource_in_tbl(i).p_job_id is NULL THEN
2878 IF L_Planning_resource_in_tbl(i).p_job_name is NULL THEN
2879 Raise JOB_NULL_ERR;
2880 END IF;
2881 END IF;
2882 END IF;
2883
2884 If l_resource = 'PERSON_TYPE' THEN
2885 If L_Planning_resource_in_tbl(i).p_person_type_code is NULL THEN
2886 Raise PERSON_TYPE_NULL_ERR;
2887 END IF;
2888 END IF;
2889
2890 If l_resource = 'NON_LABOR_RESOURCE' THEN
2891 If L_Planning_resource_in_tbl(i).p_non_labor_resource is NULL THEN
2892 Raise NON_LABOR_RES_NULL_ERR;
2893 END IF;
2894 END IF;
2895
2896 If l_resource = 'RESOURCE_CLASS' THEN
2897 Raise RESOURCE_CLASS_FMT_ERR;
2898 END IF;
2899 ELSE -- If no Resource segment, make sure no value is passed in
2900 IF (L_Planning_resource_in_tbl(i).p_bom_resource_id is NOT Null OR
2901 L_Planning_resource_in_tbl(i).p_bom_resource_name is NOT Null OR
2902 L_Planning_resource_in_tbl(i).p_non_labor_resource is NOT Null OR
2903 L_Planning_resource_in_tbl(i).p_item_category_id is NOT Null OR
2904 L_Planning_resource_in_tbl(i).p_item_category_name is NOT Null OR
2905 L_Planning_resource_in_tbl(i).p_inventory_item_id is NOT Null OR
2906 L_Planning_resource_in_tbl(i).p_inventory_item_name is NOT Null OR
2907 L_Planning_resource_in_tbl(i).p_person_id is NOT Null OR
2908 L_Planning_resource_in_tbl(i).p_person_name is NOT Null OR
2909 L_Planning_resource_in_tbl(i).p_job_id is NOT Null OR
2910 L_Planning_resource_in_tbl(i).p_job_name is NOT Null OR
2911 L_Planning_resource_in_tbl(i).p_person_type_code is NOT Null)
2912 THEN
2913 Raise RES_NOT_NULL_ERR;
2914 END IF;
2915 END IF;
2916
2917 -- Check Incurred By
2918 IF l_incurred is not NULL THEN
2919 IF (L_Planning_resource_in_tbl(i).p_incur_by_res_code is Null OR
2920 L_Planning_resource_in_tbl(i).p_incur_by_res_type is Null) THEN
2921 Raise INC_NULL_ERR;
2922 END IF;
2923 ELSE -- If no Inc By segment, make sure no value is passed in
2924 IF (L_Planning_resource_in_tbl(i).p_incur_by_res_code is NOT Null OR
2925 L_Planning_resource_in_tbl(i).p_incur_by_res_type is NOT Null)
2926 THEN
2927 Raise INC_NOT_NULL_ERR;
2928 END IF;
2929 END IF;
2930
2931 IF l_financial is not null THEN
2932 IF (L_Planning_resource_in_tbl(i).p_fc_res_type_code is Null OR
2933 L_Planning_resource_in_tbl(i).p_fin_category_name is Null) Then
2934 Raise FIN_NULL_ERR;
2935 END IF;
2936 ELSE -- If no Fin Cat segment, make sure no value is passed in
2937 IF (L_Planning_resource_in_tbl(i).p_fc_res_type_code is NOT Null OR
2938 L_Planning_resource_in_tbl(i).p_fin_category_name is NOT Null)
2939 THEN
2940 Raise FIN_NOT_NULL_ERR;
2941 END IF;
2942 END IF;
2943
2944 IF l_organization is not null THEN
2945 IF L_Planning_resource_in_tbl(i).p_organization_id is Null Then
2946 IF L_Planning_resource_in_tbl(i).p_organization_name is Null Then
2947 Raise ORG_NULL_ERR;
2948 End IF;
2949 END IF;
2950 ELSE -- If no Org segment, make sure no value is passed in
2951 IF (L_Planning_resource_in_tbl(i).p_organization_id is NOT Null OR
2952 L_Planning_resource_in_tbl(i).p_organization_name is NOT Null)
2953 THEN
2954 Raise ORG_NOT_NULL_ERR;
2955 END IF;
2956 END IF;
2957
2958 IF l_supplier is not null THEN
2959 IF L_Planning_resource_in_tbl(i).p_vendor_id is null THEN
2960 IF L_Planning_resource_in_tbl(i).p_vendor_name is null Then
2961 Raise SUPP_NULL_ERR;
2962 End If;
2963 END IF;
2964 ELSE -- If no Supplier segment, make sure no value is passed in
2965 IF (L_Planning_resource_in_tbl(i).p_vendor_id is NOT Null OR
2966 L_Planning_resource_in_tbl(i).p_vendor_name is NOT Null)
2967 THEN
2968 Raise SUPP_NOT_NULL_ERR;
2969 END IF;
2970 END IF;
2971
2972 IF l_role is not null THEN
2973 IF L_Planning_resource_in_tbl(i).p_project_role_id is NULL THEN
2974 IF L_Planning_resource_in_tbl(i).p_project_role_name is Null THEN
2975 Raise ROLE_NULL_ERR;
2976 END IF;
2977 END IF;
2978 ELSE -- If no Role segment, make sure no value is passed in
2979 IF (L_Planning_resource_in_tbl(i).p_project_role_id is NOT Null OR
2980 L_Planning_resource_in_tbl(i).p_project_role_name is NOT Null OR
2981 L_Planning_resource_in_tbl(i).p_team_role is NOT Null)
2982 THEN
2983 Raise ROLE_NOT_NULL_ERR;
2984 END IF;
2985 END IF;
2986 --End of bug 3675288.
2987
2988 --For bug 4055082.
2989 --Raise error if invalid project id is passed while creation of planning resource list memeber.
2990
2991 IF (L_planning_resource_in_tbl(i).p_project_id is not null) Then --For bug 4094047
2992
2993 BEGIN
2994
2995 SELECT 'Y'
2996 INTO l_project_exists
2997 FROM pa_projects_all
2998 WHERE project_id = L_planning_resource_in_tbl(i).p_project_id;
2999
3000 EXCEPTION
3001 WHEN NO_DATA_FOUND THEN
3002 RAISE INVALID_PROJECT_ID;
3003
3004 END;
3005
3006 END IF;
3007 --End of bug 4055082.
3008
3009 /******************************************************
3010 * Call to pa_planning_resource_pvt.create_planning_resource
3011 * which would take care of the validation and creation
3012 * of the resource list members. The table elements are being passed as
3013 * parameters.
3014 ******************************************************/
3015
3016
3017 -- derive resource_class_flag
3018 Select resource_class_flag
3019 Into l_res_class_flag
3020 From pa_res_formats_b
3021 Where res_format_id = L_planning_resource_in_tbl(i).p_res_format_id;
3022
3023 Pa_Planning_Resource_Pvt.Create_Planning_Resource(
3024 p_resource_list_id => p_resource_list_id,
3025 p_resource_list_member_id => L_planning_resource_in_tbl(i).p_resource_list_member_id,
3026 p_resource_alias => L_planning_resource_in_tbl(i).p_resource_alias,
3027 p_person_id => L_planning_resource_in_tbl(i).p_person_id,
3028 p_person_name => L_planning_resource_in_tbl(i).p_person_name,
3029 p_job_id => L_planning_resource_in_tbl(i).p_job_id,
3030 p_job_name => L_planning_resource_in_tbl(i).p_job_name,
3031 p_organization_id => L_planning_resource_in_tbl(i).p_organization_id,
3032 p_organization_name => L_planning_resource_in_tbl(i).p_organization_name,
3033 p_vendor_id => L_planning_resource_in_tbl(i).p_vendor_id,
3034 p_vendor_name => L_planning_resource_in_tbl(i).p_vendor_name,
3035 p_fin_category_name => L_planning_resource_in_tbl(i).p_fin_category_name,
3036 p_non_labor_resource => L_planning_resource_in_tbl(i).p_non_labor_resource,
3037 p_project_role_id => L_planning_resource_in_tbl(i).p_project_role_id,
3038 p_project_role_name => L_planning_resource_in_tbl(i).p_project_role_name,
3039 p_resource_class_id => L_planning_resource_in_tbl(i).p_resource_class_id,
3040 p_resource_class_code => L_planning_resource_in_tbl(i).p_resource_class_code,
3041 p_res_format_id => L_planning_resource_in_tbl(i).p_res_format_id,
3042 p_spread_curve_id => L_planning_resource_in_tbl(i).p_spread_curve_id,
3043 p_etc_method_code => L_planning_resource_in_tbl(i).p_etc_method_code,
3044 p_mfc_cost_type_id => L_planning_resource_in_tbl(i).p_mfc_cost_type_id,
3045 p_copy_from_rl_flag => Null,
3046 p_resource_class_flag => l_res_class_flag,
3047 p_fc_res_type_code => L_planning_resource_in_tbl(i).p_fc_res_type_code,
3048 p_inventory_item_id => L_planning_resource_in_tbl(i).p_inventory_item_id,
3049 p_inventory_item_name => L_planning_resource_in_tbl(i).p_inventory_item_name,
3050 p_item_category_id => L_planning_resource_in_tbl(i).p_item_category_id,
3051 p_item_category_name => L_planning_resource_in_tbl(i).p_item_category_name,
3052 p_migration_code => 'N',
3053 p_attribute_category => L_planning_resource_in_tbl(i).p_attribute_category,
3054 p_attribute1 => L_planning_resource_in_tbl(i).p_attribute1,
3055 p_attribute2 => L_planning_resource_in_tbl(i).p_attribute2,
3056 p_attribute3 => L_planning_resource_in_tbl(i).p_attribute3,
3057 p_attribute4 => L_planning_resource_in_tbl(i).p_attribute4,
3058 p_attribute5 => L_planning_resource_in_tbl(i).p_attribute5,
3059 p_attribute6 => L_planning_resource_in_tbl(i).p_attribute6,
3060 p_attribute7 => L_planning_resource_in_tbl(i).p_attribute7,
3061 p_attribute8 => L_planning_resource_in_tbl(i).p_attribute8,
3062 p_attribute9 => L_planning_resource_in_tbl(i).p_attribute9,
3063 p_attribute10 => L_planning_resource_in_tbl(i).p_attribute10,
3064 p_attribute11 => L_planning_resource_in_tbl(i).p_attribute11,
3065 p_attribute12 => L_planning_resource_in_tbl(i).p_attribute12,
3066 p_attribute13 => L_planning_resource_in_tbl(i).p_attribute13,
3067 p_attribute14 => L_planning_resource_in_tbl(i).p_attribute14,
3068 p_attribute15 => L_planning_resource_in_tbl(i).p_attribute15,
3069 p_attribute16 => L_planning_resource_in_tbl(i).p_attribute16,
3070 p_attribute17 => L_planning_resource_in_tbl(i).p_attribute17,
3071 p_attribute18 => L_planning_resource_in_tbl(i).p_attribute18,
3072 p_attribute19 => L_planning_resource_in_tbl(i).p_attribute19,
3073 p_attribute20 => L_planning_resource_in_tbl(i).p_attribute20,
3074 p_attribute21 => L_planning_resource_in_tbl(i).p_attribute21,
3075 p_attribute22 => L_planning_resource_in_tbl(i).p_attribute22,
3076 p_attribute23 => L_planning_resource_in_tbl(i).p_attribute23,
3077 p_attribute24 => L_planning_resource_in_tbl(i).p_attribute24,
3078 p_attribute25 => L_planning_resource_in_tbl(i).p_attribute25,
3079 p_attribute26 => L_planning_resource_in_tbl(i).p_attribute26,
3080 p_attribute27 => L_planning_resource_in_tbl(i).p_attribute27,
3081 p_attribute28 => L_planning_resource_in_tbl(i).p_attribute28,
3082 p_attribute29 => L_planning_resource_in_tbl(i).p_attribute29,
3083 p_attribute30 => L_planning_resource_in_tbl(i).p_attribute30,
3084 p_person_type_code => L_planning_resource_in_tbl(i).p_person_type_code,
3085 p_bom_resource_id => L_planning_resource_in_tbl(i).p_bom_resource_id,
3086 p_bom_resource_name => L_planning_resource_in_tbl(i).p_bom_resource_name,
3087 p_team_role => L_planning_resource_in_tbl(i).p_team_role,
3088 p_incur_by_res_code => L_planning_resource_in_tbl(i).p_incur_by_res_code,
3089 p_incur_by_res_type => L_planning_resource_in_tbl(i).p_incur_by_res_type,
3090 p_project_id => L_planning_resource_in_tbl(i).p_project_id,
3091 x_resource_list_member_id => x_planning_resource_out_tbl(i).x_resource_list_member_id,
3092 x_record_version_number => x_planning_resource_out_tbl(i).x_record_version_number,
3093 x_return_status => x_return_status,
3094 x_msg_count => x_msg_count ,
3095 x_error_msg_data => x_error_msg_data);
3096
3097
3098 If x_return_status <> Fnd_Api.G_Ret_Sts_Success Then
3099
3100 RollBack to planning_resource_create;
3101 RETURN;
3102
3103 End If;
3104 End Loop;
3105
3106 -- Commit only if all records are successful
3107 /************************************************
3108 * Check the Commit flag. if it is true then Commit.
3109 ***********************************************/
3110 If Fnd_Api.To_Boolean( P_Commit ) Then
3111
3112 Commit;
3113
3114 End If;
3115 x_return_status := Fnd_Api.G_Ret_Sts_Success;
3116 x_msg_count := Fnd_Msg_Pub.Count_Msg;
3117
3118
3119 Exception
3120 When FMT_NULL_ERR Then
3121 x_return_status := Fnd_Api.G_Ret_Sts_Error;
3122 x_msg_count:=1;
3123 x_error_msg_data := 'PA_AMG_FORMAT_ID_NULL';
3124 Pa_Utils.Add_Message
3125 (P_App_Short_Name => 'PA',
3126 P_Msg_Name => 'PA_AMG_FORMAT_ID_NULL',
3127 p_token1 => 'PLAN_RES',
3128 p_value1 => Pa_Planning_Resource_Pvt.g_token);
3129 When RES_FORMAT_ID_ERR Then
3130 x_return_status := Fnd_Api.G_Ret_Sts_Error;
3131 x_msg_count:=1;
3132 x_error_msg_data := 'PA_AMG_FORMAT_NOT_EXISTS';
3133 Pa_Utils.Add_Message(
3134 P_App_Short_Name => 'PA',
3135 P_Msg_Name => 'PA_AMG_FORMAT_NOT_EXISTS',
3136 p_token1 => 'PLAN_RES',
3137 p_value1 => Pa_Planning_Resource_Pvt.g_token);
3138 When RES_CLASS_ID_AND_CODE_NULL Then
3139 x_return_status := Fnd_Api.G_Ret_Sts_Error;
3140 x_msg_count:=1;
3141 x_error_msg_data := 'PA_AMG_RESCLS_ID_CODE_NULL';
3142 Pa_Utils.Add_Message
3143 (P_App_Short_Name => 'PA',
3144 P_Msg_Name => 'PA_AMG_RESCLS_ID_CODE_NULL',
3145 p_token1 => 'PLAN_RES',
3146 p_value1 => Pa_Planning_Resource_Pvt.g_token);
3147 When RES_CODE_INVALID_ERR Then
3148 x_return_status := Fnd_Api.G_Ret_Sts_Error;
3149 x_msg_count:=1;
3150 x_error_msg_data := 'PA_AMG_RESOURCE_CODE_INVALID';
3151 Pa_Utils.Add_Message
3152 (P_App_Short_Name => 'PA',
3153 P_Msg_Name => 'PA_AMG_RESOURCE_CODE_INVALID',
3154 p_token1 => 'PLAN_RES',
3155 p_value1 => Pa_Planning_Resource_Pvt.g_token);
3156 When RES_ID_INVALID_ERR Then
3157 x_return_status := Fnd_Api.G_Ret_Sts_Error;
3158 x_msg_count:=1;
3159 x_error_msg_data := 'PA_AMG_RESOURCE_ID_INVALID';
3160 Pa_Utils.Add_Message
3161 (P_App_Short_Name => 'PA',
3162 P_Msg_Name => 'PA_AMG_RESOURCE_ID_INVALID',
3163 p_token1 => 'PLAN_RES',
3164 p_value1 => Pa_Planning_Resource_Pvt.g_token);
3165 When INVALID_PROJECT_ID Then
3166 x_return_status := Fnd_Api.G_Ret_Sts_Error;
3167 x_msg_count:=1;
3168 x_error_msg_data := 'PA_AMG_PROJECT_ID_INVALID';
3169 Pa_Utils.Add_Message
3170 (P_App_Short_Name => 'PA',
3171 P_Msg_Name => 'PA_AMG_PROJECT_ID_INVALID',
3172 p_token1 => 'PLAN_RES',
3173 p_value1 => Pa_Planning_Resource_Pvt.g_token);
3174 When NAMED_PER_NULL_ERR Then
3175 x_return_status := Fnd_Api.G_Ret_Sts_Error;
3176 x_msg_count:=1;
3177 x_error_msg_data := 'PA_AMG_NAMED_PER_NULL';
3178 Pa_Utils.Add_Message
3179 (P_App_Short_Name => 'PA',
3180 P_Msg_Name => 'PA_AMG_NAMED_PER_NULL',
3181 p_token1 => 'PLAN_RES',
3182 p_value1 => Pa_Planning_Resource_Pvt.g_token);
3183 When BOM_LAB_N_EQUIP_NULL_ERR Then
3184 x_return_status := Fnd_Api.G_Ret_Sts_Error;
3185 x_msg_count:=1;
3186 x_error_msg_data := 'PA_AMG_BOM_NULL';
3187 Pa_Utils.Add_Message
3188 (P_App_Short_Name => 'PA',
3189 P_Msg_Name => 'PA_AMG_BOM_NULL',
3190 p_token1 => 'PLAN_RES',
3191 p_value1 => Pa_Planning_Resource_Pvt.g_token);
3192 When ITEM_CAT_NULL_ERR Then
3193 x_return_status := Fnd_Api.G_Ret_Sts_Error;
3194 x_msg_count:=1;
3195 x_error_msg_data := 'PA_AMG_ITEM_CAT_NULL';
3196 Pa_Utils.Add_Message
3197 (P_App_Short_Name => 'PA',
3198 P_Msg_Name => 'PA_AMG_ITEM_CAT_NULL',
3199 p_token1 => 'PLAN_RES',
3200 p_value1 => Pa_Planning_Resource_Pvt.g_token);
3201 When INVEN_ITEM_NULL_ERR Then
3202 x_return_status := Fnd_Api.G_Ret_Sts_Error;
3203 x_msg_count:=1;
3204 x_error_msg_data := 'PA_AMG_INV_ITEM_NULL';
3205 Pa_Utils.Add_Message
3206 (P_App_Short_Name => 'PA',
3207 P_Msg_Name => 'PA_AMG_INV_ITEM_NULL',
3208 p_token1 => 'PLAN_RES',
3209 p_value1 => Pa_Planning_Resource_Pvt.g_token);
3210 When JOB_NULL_ERR Then
3211 x_return_status := Fnd_Api.G_Ret_Sts_Error;
3212 x_msg_count:=1;
3213 x_error_msg_data := 'PA_AMG_JOB_NULL';
3214 Pa_Utils.Add_Message
3215 (P_App_Short_Name => 'PA',
3216 P_Msg_Name => 'PA_AMG_JOB_NULL',
3217 p_token1 => 'PLAN_RES',
3218 p_value1 => Pa_Planning_Resource_Pvt.g_token);
3219 When PERSON_TYPE_NULL_ERR Then
3220 x_return_status := Fnd_Api.G_Ret_Sts_Error;
3221 x_msg_count:=1;
3222 x_error_msg_data := 'PA_AMG_PERSON_TYPE_NULL';
3223 Pa_Utils.Add_Message
3224 (P_App_Short_Name => 'PA',
3225 P_Msg_Name => 'PA_AMG_PERSON_TYPE_NULL',
3226 p_token1 => 'PLAN_RES',
3227 p_value1 => Pa_Planning_Resource_Pvt.g_token);
3228 When NON_LABOR_RES_NULL_ERR Then
3229 x_return_status := Fnd_Api.G_Ret_Sts_Error;
3230 x_msg_count:=1;
3231 x_error_msg_data := 'PA_AMG_NON_LABOR_NULL';
3232 Pa_Utils.Add_Message
3233 (P_App_Short_Name => 'PA',
3234 P_Msg_Name => 'PA_AMG_NON_LABOR_NULL',
3235 p_token1 => 'PLAN_RES',
3236 p_value1 => Pa_Planning_Resource_Pvt.g_token);
3237 When FIN_NULL_ERR Then
3238 x_return_status := Fnd_Api.G_Ret_Sts_Error;
3239 x_msg_count:=1;
3240 x_error_msg_data := 'PA_AMG_FIN_NULL';
3241 Pa_Utils.Add_Message
3242 (P_App_Short_Name => 'PA',
3243 P_Msg_Name => 'PA_AMG_FIN_NULL',
3244 p_token1 => 'PLAN_RES',
3245 p_value1 => Pa_Planning_Resource_Pvt.g_token);
3246 When FIN_NOT_NULL_ERR Then
3247 x_return_status := Fnd_Api.G_Ret_Sts_Error;
3248 x_msg_count:=1;
3249 x_error_msg_data := 'PA_AMG_FIN_NOT_NULL';
3250 Pa_Utils.Add_Message
3251 (P_App_Short_Name => 'PA',
3252 P_Msg_Name => 'PA_AMG_FIN_NOT_NULL',
3253 p_token1 => 'PLAN_RES',
3254 p_value1 => Pa_Planning_Resource_Pvt.g_token);
3255 When ORG_NULL_ERR Then
3256 x_return_status := Fnd_Api.G_Ret_Sts_Error;
3257 x_msg_count:=1;
3258 x_error_msg_data := 'PA_AMG_ORG_NULL';
3259 Pa_Utils.Add_Message
3260 (P_App_Short_Name => 'PA',
3261 P_Msg_Name => 'PA_AMG_ORG_NULL',
3262 p_token1 => 'PLAN_RES',
3263 p_value1 => Pa_Planning_Resource_Pvt.g_token);
3264 When ORG_NOT_NULL_ERR Then
3265 x_return_status := Fnd_Api.G_Ret_Sts_Error;
3266 x_msg_count:=1;
3267 x_error_msg_data := 'PA_AMG_ORG_NOT_NULL';
3268 Pa_Utils.Add_Message
3269 (P_App_Short_Name => 'PA',
3270 P_Msg_Name => 'PA_AMG_ORG_NOT_NULL',
3271 p_token1 => 'PLAN_RES',
3272 p_value1 => Pa_Planning_Resource_Pvt.g_token);
3273 When SUPP_NULL_ERR Then
3274 x_return_status := Fnd_Api.G_Ret_Sts_Error;
3275 x_msg_count:=1;
3276 x_error_msg_data := 'PA_AMG_SUPP_NULL';
3277 Pa_Utils.Add_Message
3278 (P_App_Short_Name => 'PA',
3279 P_Msg_Name => 'PA_AMG_SUPP_NULL',
3280 p_token1 => 'PLAN_RES',
3281 p_value1 => Pa_Planning_Resource_Pvt.g_token);
3282 When SUPP_NOT_NULL_ERR Then
3283 x_return_status := Fnd_Api.G_Ret_Sts_Error;
3284 x_msg_count:=1;
3285 x_error_msg_data := 'PA_AMG_SUPP_NOT_NULL';
3286 Pa_Utils.Add_Message
3287 (P_App_Short_Name => 'PA',
3288 P_Msg_Name => 'PA_AMG_SUPP_NOT_NULL',
3289 p_token1 => 'PLAN_RES',
3290 p_value1 => Pa_Planning_Resource_Pvt.g_token);
3291 When ROLE_NULL_ERR Then
3292 x_return_status := Fnd_Api.G_Ret_Sts_Error;
3293 x_msg_count:=1;
3294 x_error_msg_data := 'PA_AMG_ROLE_NULL';
3295 Pa_Utils.Add_Message
3296 (P_App_Short_Name => 'PA',
3297 P_Msg_Name => 'PA_AMG_ROLE_NULL',
3298 p_token1 => 'PLAN_RES',
3299 p_value1 => Pa_Planning_Resource_Pvt.g_token);
3300 When ROLE_NOT_NULL_ERR Then
3301 x_return_status := Fnd_Api.G_Ret_Sts_Error;
3302 x_msg_count:=1;
3303 x_error_msg_data := 'PA_AMG_ROLE_NOT_NULL';
3304 Pa_Utils.Add_Message
3305 (P_App_Short_Name => 'PA',
3306 P_Msg_Name => 'PA_AMG_ROLE_NOT_NULL',
3307 p_token1 => 'PLAN_RES',
3308 p_value1 => Pa_Planning_Resource_Pvt.g_token);
3309 When INC_NULL_ERR Then
3310 x_return_status := Fnd_Api.G_Ret_Sts_Error;
3311 x_msg_count:=1;
3312 x_error_msg_data := 'PA_AMG_INC_NULL';
3313 Pa_Utils.Add_Message
3314 (P_App_Short_Name => 'PA',
3315 P_Msg_Name => 'PA_AMG_INC_NULL',
3316 p_token1 => 'PLAN_RES',
3317 p_value1 => Pa_Planning_Resource_Pvt.g_token);
3318 When INC_NOT_NULL_ERR Then
3319 x_return_status := Fnd_Api.G_Ret_Sts_Error;
3320 x_msg_count:=1;
3321 x_error_msg_data := 'PA_AMG_INC_NOT_NULL';
3322 Pa_Utils.Add_Message
3323 (P_App_Short_Name => 'PA',
3324 P_Msg_Name => 'PA_AMG_INC_NOT_NULL',
3325 p_token1 => 'PLAN_RES',
3326 p_value1 => Pa_Planning_Resource_Pvt.g_token);
3327 When RES_NOT_NULL_ERR Then
3328 x_return_status := Fnd_Api.G_Ret_Sts_Error;
3329 x_msg_count:=1;
3330 x_error_msg_data := 'PA_AMG_RES_NOT_NULL';
3331 Pa_Utils.Add_Message
3332 (P_App_Short_Name => 'PA',
3333 P_Msg_Name => 'PA_AMG_RES_NOT_NULL',
3334 p_token1 => 'PLAN_RES',
3335 p_value1 => Pa_Planning_Resource_Pvt.g_token);
3336 When RESOURCE_CLASS_FMT_ERR Then
3337 x_return_status := Fnd_Api.G_Ret_Sts_Error;
3338 x_msg_count:=1;
3339 x_error_msg_data := 'PA_AMG_RES_CLS_FMT';
3340 Pa_Utils.Add_Message
3341 (P_App_Short_Name => 'PA',
3342 P_Msg_Name => 'PA_AMG_RES_CLS_FMT',
3343 p_token1 => 'PLAN_RES',
3344 p_value1 => Pa_Planning_Resource_Pvt.g_token);
3345 When Others Then
3346 Rollback;
3347 x_return_status := Fnd_Api.G_Ret_Sts_UnExp_Error;
3348
3349 End Create_Planning_Resource;
3350 /*****************************************************/
3351
3352 /***************************************************
3353 * Procedure : Update_Planning_Resource
3354 * Description : The purpose of this procedure is to
3355 * Validate and update attributes on an existing
3356 * planning resource for a resource list.
3357 * The detailed information is in the spec.
3358 ******************************************************/
3359 PROCEDURE Update_Planning_Resource
3360 (p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
3361 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
3362 p_resource_list_id IN NUMBER,
3363 p_enabled_flag IN VARCHAR2,
3364 P_planning_resource_in_tbl IN Planning_Resource_In_Tbl,
3365 X_planning_resource_out_tbl OUT NOCOPY Planning_Resource_Out_Tbl,
3366 x_return_status OUT NOCOPY VARCHAR2,
3367 x_msg_count OUT NOCOPY NUMBER,
3368 x_error_msg_data OUT NOCOPY VARCHAR2 )
3369
3370 IS
3371
3372 -- If need to get the resource_list_member_id using the alias then
3373 -- If control_flag <> 'N' from pa_resource_lists_all_bg this means centralized and only need the
3374 -- predicates to resource_list_id and alias to get the resource_list_member_id
3375 -- If the control_flag = 'N' then we need to add predicates for object_type and object_id.
3376 -- The object_type = 'PROJECT' and the object_id is the project_id.
3377 Cursor c1(P_Alias IN Varchar2,
3378 P_Res_List_Id IN Number,
3379 P_Prj_Id IN Number) Is
3380 Select
3381 rlm.Resource_List_Member_Id
3382 From
3383 Pa_Resource_List_Members rlm,
3384 Pa_Resource_Lists_All_BG rl
3385 Where
3386 rlm.Alias = P_Alias
3387 And rlm.Resource_List_Id = P_Res_List_Id
3388 And rlm.Resource_List_Id = rl.Resource_List_Id
3389 And ( rl.Control_Flag <> 'N' Or
3390 ( rl.Control_Flag = 'N' And
3391 rlm.Object_Type = 'PROJECT' And
3392 rlm.Object_Id = P_Prj_Id) );
3393
3394 Cursor c2(P_rlm_Id IN Number) Is
3395 Select
3396 Record_Version_Number
3397 From
3398 Pa_Resource_List_Members
3399 Where
3400 Resource_List_Member_Id = P_rlm_Id;
3401
3402 l_rlm_id Number := Null;
3403 l_rec_ver_num Number := Null;
3404
3405 EXC_NULL_INVALID_DATA EXCEPTION;
3406
3407 BEGIN
3408
3409 Pa_Planning_Resource_Pvt.g_amg_flow := 'Y';
3410 -- First clear the message stack.
3411 If Fnd_Api.To_Boolean( p_init_msg_list ) Then
3412
3413 Fnd_Msg_Pub.Initialize;
3414
3415 End If;
3416
3417 /* Eugene
3418 * Add code here to check if resource list id
3419 * is passed. If it is not passed, thro an error and return status of ERROR
3420 */
3421 If p_resource_list_id is Null Then
3422
3423 X_Return_Status := Fnd_Api.G_Ret_Sts_Error;
3424 X_Error_Msg_Data := 'PA_PLN_RES_LIST_ID_IS_NULL';
3425 x_msg_count := x_msg_count + 1;
3426 Pa_Utils.Add_Message ('PA', X_Error_Msg_Data);
3427 Rollback;
3428 Return;
3429
3430 End If;
3431
3432 For i IN 1..P_Planning_Resource_In_Tbl.Count
3433 Loop
3434
3435 Begin
3436 SELECT meaning || ' ' || to_char(i) || ':'
3437 INTO Pa_Planning_Resource_Pvt.g_token
3438 FROM pa_lookups
3439 WHERE lookup_type = 'PA_PLANNING_RESOURCE'
3440 AND lookup_code = 'PLANNING_RESOURCE';
3441
3442
3443 savepoint planning_res_list_update;
3444
3445 x_msg_count := 0;
3446 x_return_status := Fnd_Api.G_Ret_Sts_Success;
3447
3448 /* Eugene
3449 * Add code here to check if resource list member id is passed. If it is not
3450 * passed, derive it from the alias, if alias is passed. If both are not
3451 * passed, set the record to status of "ERROR and set the message for the
3452 * error. Continue with the loop.
3453 * To get the resource list member id from the alias, you will need to check
3454 * if the resource list is proj specific.
3455 */
3456
3457 If p_planning_resource_in_tbl(i).p_resource_list_member_id Is Null Then
3458
3459 If P_planning_resource_in_tbl(i).p_resource_alias Is Null Then
3460
3461 x_return_status := Fnd_Api.G_Ret_Sts_Error;
3462 Pa_Utils.Add_Message ('PA', 'PA_PLN_RES_LIST_ID_ALIAS_NULL',
3463 'PLAN_RES', Pa_Planning_Resource_Pvt.g_token);
3464 Raise EXC_NULL_INVALID_DATA;
3465
3466 Else -- P_planning_resource_in_tbl(i).p_resource_alias Is Not Null
3467
3468 Open c1(P_Alias => P_Planning_Resource_In_Tbl(i).P_Resource_Alias,
3469 P_Res_List_Id => P_Resource_List_Id,
3470 P_Prj_Id => P_Planning_Resource_In_Tbl(i).P_Project_Id);
3471 Fetch c1 Into l_rlm_Id;
3472
3473 If c1%NotFound Then
3474
3475 Close c1;
3476 x_return_status := Fnd_Api.G_Ret_Sts_Error;
3477 Pa_Utils.Add_Message ('PA', 'PA_PLN_RES_LIST_ALIAS_INVAL',
3478 'PLAN_RES', Pa_Planning_Resource_Pvt.g_token);
3479 RAISE EXC_NULL_INVALID_DATA;
3480
3481 Else
3482
3483 Close c1;
3484
3485 End If;
3486
3487 End If;
3488
3489 Else
3490
3491 l_rlm_Id := p_planning_resource_in_tbl(i).p_resource_list_member_id;
3492
3493 End If;
3494
3495 If p_planning_resource_in_tbl(i).p_record_version_number is Null Then
3496
3497 x_return_status := Fnd_Api.G_Ret_Sts_Error;
3498 Pa_Utils.Add_Message ('PA', 'PA_PLN_RESLISTMEM_RECVER_NULL',
3499 'PLAN_RES', Pa_Planning_Resource_Pvt.g_token);
3500 RAISE EXC_NULL_INVALID_DATA;
3501
3502
3503 Else
3504
3505 Open c2(l_rlm_id);
3506 Fetch c2 Into l_rec_ver_num;
3507
3508 If c2%NotFound Then
3509
3510 Close c2;
3511 x_return_status := Fnd_Api.G_Ret_Sts_Error;
3512 Pa_Utils.Add_Message ('PA', 'PA_PLN_RESLISTMEM_ID_INVAL',
3513 'PLAN_RES', Pa_Planning_Resource_Pvt.g_token);
3514 RAISE EXC_NULL_INVALID_DATA;
3515
3516 Else
3517
3518 Close c2;
3519 If l_rec_ver_num <> p_planning_resource_in_tbl(i).p_record_version_number then
3520
3521 x_return_status := Fnd_Api.G_Ret_Sts_Error;
3522 Pa_Utils.Add_Message('PA', 'PA_PLN_RESLISTMEM_ALREADY_UPD',
3523 'PLAN_RES', Pa_Planning_Resource_Pvt.g_token);
3524 RAISE EXC_NULL_INVALID_DATA;
3525
3526 End If;
3527
3528 End If;
3529
3530 End If;
3531
3532 pa_planning_resource_pvt.Update_Planning_Resource(
3533 p_resource_list_id => p_resource_list_id,
3534 p_resource_list_member_id => l_rlm_id,
3535 p_enabled_flag => p_enabled_flag,
3536 p_resource_alias => P_planning_resource_in_tbl(i).p_resource_alias,
3537 p_spread_curve_id => P_planning_resource_in_tbl(i).p_spread_curve_id,
3538 p_etc_method_code => P_planning_resource_in_tbl(i).p_etc_method_code,
3539 p_mfc_cost_type_id => P_planning_resource_in_tbl(i).p_mfc_cost_type_id,
3540 p_attribute_category => P_planning_resource_in_tbl(i).p_attribute_category,
3541 p_attribute1 => P_planning_resource_in_tbl(i).p_attribute1,
3542 p_attribute2 => P_planning_resource_in_tbl(i).p_attribute2,
3543 p_attribute3 => P_planning_resource_in_tbl(i).p_attribute3,
3544 p_attribute4 => P_planning_resource_in_tbl(i).p_attribute4,
3545 p_attribute5 => P_planning_resource_in_tbl(i).p_attribute5,
3546 p_attribute6 => P_planning_resource_in_tbl(i).p_attribute6,
3547 p_attribute7 => P_planning_resource_in_tbl(i).p_attribute7,
3548 p_attribute8 => P_planning_resource_in_tbl(i).p_attribute8,
3549 p_attribute9 => P_planning_resource_in_tbl(i).p_attribute9,
3550 p_attribute10 => P_planning_resource_in_tbl(i).p_attribute10,
3551 p_attribute11 => P_planning_resource_in_tbl(i).p_attribute11,
3552 p_attribute12 => P_planning_resource_in_tbl(i).p_attribute12,
3553 p_attribute13 => P_planning_resource_in_tbl(i).p_attribute13,
3554 p_attribute14 => P_planning_resource_in_tbl(i).p_attribute14,
3555 p_attribute15 => P_planning_resource_in_tbl(i).p_attribute15,
3556 p_attribute16 => P_planning_resource_in_tbl(i).p_attribute16,
3557 p_attribute17 => P_planning_resource_in_tbl(i).p_attribute17,
3558 p_attribute18 => P_planning_resource_in_tbl(i).p_attribute18,
3559 p_attribute19 => P_planning_resource_in_tbl(i).p_attribute19,
3560 p_attribute20 => P_planning_resource_in_tbl(i).p_attribute20,
3561 p_attribute21 => P_planning_resource_in_tbl(i).p_attribute21,
3562 p_attribute22 => P_planning_resource_in_tbl(i).p_attribute22,
3563 p_attribute23 => P_planning_resource_in_tbl(i).p_attribute23,
3564 p_attribute24 => P_planning_resource_in_tbl(i).p_attribute24,
3565 p_attribute25 => P_planning_resource_in_tbl(i).p_attribute25,
3566 p_attribute26 => P_planning_resource_in_tbl(i).p_attribute26,
3567 p_attribute27 => P_planning_resource_in_tbl(i).p_attribute27,
3568 p_attribute28 => P_planning_resource_in_tbl(i).p_attribute28,
3569 p_attribute29 => P_planning_resource_in_tbl(i).p_attribute29,
3570 p_attribute30 => P_planning_resource_in_tbl(i).p_attribute30,
3571 p_record_version_number => p_planning_resource_in_tbl(i).p_record_version_number,
3572 x_record_version_number => x_planning_resource_out_tbl(i).x_record_version_number,
3573 x_return_status => x_return_status,
3574 x_msg_count => x_msg_count ,
3575 x_error_msg_data => x_error_msg_data);
3576
3577 If x_return_status <> Fnd_Api.G_Ret_Sts_Success Then
3578
3579 RollBack to planning_res_list_update;
3580 Return;
3581
3582 End If;
3583
3584 Exception
3585 When EXC_NULL_INVALID_DATA Then
3586 x_return_status := Fnd_Api.G_Ret_Sts_Error;
3587 x_msg_count := x_msg_count + 1;
3588 RollBack to planning_res_list_update;
3589 Return;
3590 When Others Then
3591 Raise; -- stop processing
3592
3593 End;
3594 End Loop;
3595 /************************************************
3596 * Check the Commit flag. if it is true then Commit.
3597 ***********************************************/
3598 If Fnd_Api.To_Boolean( p_commit ) Then
3599
3600 Commit;
3601
3602 End If;
3603
3604 x_return_status := Fnd_Api.G_Ret_Sts_Success;
3605 x_msg_count := Fnd_Msg_Pub.Count_Msg;
3606
3607 Exception
3608 When Others Then
3609 Rollback;
3610 x_return_status := Fnd_Api.G_Ret_Sts_UnExp_Error;
3611
3612 End Update_Planning_Resource;
3613 /************************************/
3614
3615 /*************************************************
3616 * Procedure : Delete_Planning_Resource
3617 * Description : The purpose of this procedure is to
3618 * delete a planning resource if it is not
3619 * being used, else disable it.
3620 * Further details in the Body.
3621 * The detailed information is in the spec.
3622 ***************************************************/
3623 PROCEDURE Delete_Planning_Resource(
3624 p_resource_list_member_id IN SYSTEM.PA_NUM_TBL_TYPE,
3625 p_commit IN VARCHAR2,
3626 p_init_msg_list IN VARCHAR2,
3627 x_return_status OUT NOCOPY VARCHAR2,
3628 x_msg_count OUT NOCOPY NUMBER,
3629 x_error_msg_data OUT NOCOPY VARCHAR2)
3630
3631 IS
3632 BEGIN
3633 Pa_Planning_Resource_Pvt.g_amg_flow := 'Y';
3634 -- First clear the message stack.
3635 IF FND_API.to_boolean( p_init_msg_list )
3636 THEN
3637 FND_MSG_PUB.initialize;
3638 END IF;
3639 /********************************************
3640 * To Check if resource_list member is currently being
3641 * used in a planning transaction.
3642 * We are checking from pa_resource_assignments table.
3643 *************************************************/
3644 FOR i in 1..p_resource_list_member_id.COUNT
3645 LOOP
3646 SELECT meaning || ' ' || to_char(i) || ':'
3647 INTO Pa_Planning_Resource_Pvt.g_token
3648 FROM pa_lookups
3649 WHERE lookup_type = 'PA_PLANNING_RESOURCE'
3650 AND lookup_code = 'PLANNING_RESOURCE';
3651
3652 pa_planning_resource_pvt.delete_planning_resource
3653 (p_resource_list_member_id =>p_resource_list_member_id(i),
3654 x_return_status =>x_return_status,
3655 x_msg_count =>x_msg_count,
3656 x_error_msg_data =>x_error_msg_data);
3657
3658
3659 END LOOP;
3660 /************************************************
3661 * Check the Commit flag. if it is true then Commit.
3662 ***********************************************/
3663 IF FND_API.to_boolean( p_commit )
3664 THEN
3665 COMMIT;
3666 END IF;
3667 /***************/
3668 END Delete_Planning_Resource;
3669
3670 /******************************************************
3671 * Procedure : Init_Create_Resource_List
3672 * Description : This procedure initializes the global
3673 * temporary tables for the resource list,
3674 * resource formats and,
3675 * resoure list members.
3676 * The detailed information is in the spec.
3677 ******************************************************/
3678 PROCEDURE Init_Create_Resource_List
3679 IS
3680 l_api_name CONSTANT VARCHAR2(30) := 'Init_Create_Resource_List';
3681 BEGIN
3682 FND_MSG_PUB.initialize;
3683
3684 G_Plan_Res_List_IN_Rec := G_Res_List_empty_rec;
3685 G_Plan_Res_List_Out_Rec := G_Res_List_empty_out_rec;
3686
3687 G_Plan_RL_format_In_Tbl.DELETE;
3688 G_Plan_RL_format_Out_Tbl.DELETE;
3689
3690 G_Planning_resource_In_tbl.DELETE;
3691 G_Planning_resource_Out_tbl.DELETE;
3692
3693
3694 G_Plan_RL_Format_tbl_count := 0;
3695 G_Plan_Resource_tbl_count := 0;
3696
3697 EXCEPTION
3698 WHEN OTHERS THEN
3699 FND_MSG_PUB.Add_Exc_Msg
3700 ( p_pkg_name => G_PKG_NAME ,
3701 p_procedure_name => l_api_name
3702 );
3703
3704 END Init_Create_Resource_List;
3705 /*****************************/
3706
3707 /******************************************************
3708 * Procedure : Init_Update_Resource_List
3709 * Description : This procedure initializes the global
3710 * temporary tables for the resource list,
3711 * resource formats and,
3712 * resoure list members.
3713 * The detailed information is in the spec.
3714 ******************************************************/
3715 PROCEDURE Init_Update_Resource_List
3716 IS
3717 l_api_name CONSTANT VARCHAR2(30) := 'Init_Update_Resource_List';
3718 BEGIN
3719 FND_MSG_PUB.initialize;
3720
3721 G_Plan_Res_List_IN_Rec := G_Res_List_empty_rec;
3722 G_Plan_Res_List_Out_Rec := G_Res_List_empty_out_rec;
3723
3724 G_Plan_RL_format_In_Tbl.DELETE;
3725 G_Plan_RL_format_Out_Tbl.DELETE;
3726
3727 G_Planning_resource_In_tbl.DELETE;
3728 G_Planning_resource_Out_tbl.DELETE;
3729
3730
3731 G_Plan_RL_Format_tbl_count := 0;
3732 G_Plan_Resource_tbl_count := 0;
3733
3734 EXCEPTION
3735 WHEN OTHERS THEN
3736 FND_MSG_PUB.Add_Exc_Msg
3737 ( p_pkg_name => G_PKG_NAME ,
3738 p_procedure_name => l_api_name
3739 );
3740 END Init_Update_Resource_List;
3741 /*****************************/
3742 /*************************************************
3743 * Procedure : Load_Resource_List
3744 * Description : This procedure loads the resource
3745 * list globals.
3746 * The detailed information is in the spec.
3747 ***********************************************/
3748 PROCEDURE Load_Resource_List
3749 (p_api_version_number IN NUMBER,
3750 p_resource_list_id IN NUMBER DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
3751 p_resource_list_name IN VARCHAR2,
3752 p_description IN VARCHAR2,
3753 p_start_date IN DATE DEFAULT SYSDATE,
3754 p_end_date IN DATE DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE,
3755 p_job_group_id IN NUMBER,
3756 p_job_group_name IN VARCHAR2 DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
3757 p_use_for_wp_flag IN VARCHAR2 DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
3758 p_control_flag IN VARCHAR2 DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
3759 p_record_version_number IN NUMBER DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
3760 x_return_status OUT NOCOPY Varchar2)
3761 IS
3762 l_api_version_number CONSTANT NUMBER := G_API_VERSION_NUMBER;
3763 l_api_name CONSTANT VARCHAR2(30) := 'Load_Resource_List';
3764 BEGIN
3765 x_return_status := FND_API.g_ret_sts_success;
3766
3767 --Standard API Compatibility Call.
3768 IF NOT FND_API.Compatible_API_Call ( l_api_version_number ,
3769 p_api_version_number ,
3770 l_api_name ,
3771 G_PKG_NAME )
3772 THEN
3773 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3774 END IF;
3775
3776 G_Plan_Res_List_IN_Rec.p_resource_list_id := p_resource_list_id;
3777 G_Plan_Res_List_IN_Rec.p_resource_list_name := p_resource_list_name;
3778 G_Plan_Res_List_IN_Rec.p_description := p_description;
3779 G_Plan_Res_List_IN_Rec.p_start_date := p_start_date;
3780 G_Plan_Res_List_IN_Rec.p_end_date := p_end_date;
3781 G_Plan_Res_List_IN_Rec.p_job_group_id := p_job_group_id;
3782 G_Plan_Res_List_IN_Rec.p_job_group_name := p_job_group_name;
3783 G_Plan_Res_List_IN_Rec.p_use_for_wp_flag := p_use_for_wp_flag;
3784 G_Plan_Res_List_IN_Rec.p_record_version_number :=
3785 p_record_version_number;
3786
3787 EXCEPTION
3788 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
3789 THEN
3790 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3791 WHEN OTHERS THEN
3792 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3793 FND_MSG_PUB.Add_Exc_Msg
3794 ( p_pkg_name => G_PKG_NAME ,
3795 p_procedure_name => l_api_name
3796 );
3797 END Load_Resource_List;
3798
3799
3800 /*************************************************
3801 * Procedure : Load_Resource_Format
3802 * Description : This procedure loads the resource
3803 * Format globals.
3804 * The detailed information is in the spec.
3805 ***********************************************/
3806 PROCEDURE Load_Resource_Format
3807 (p_api_version_number IN Number,
3808 P_Res_Format_Id IN NUMBER DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
3809 x_return_status OUT NOCOPY Varchar2)
3810 IS
3811 l_api_version_number CONSTANT NUMBER := G_API_VERSION_NUMBER;
3812 l_api_name CONSTANT VARCHAR2(30) := 'Load_Resource_Format';
3813 BEGIN
3814 x_return_status := FND_API.g_ret_sts_success;
3815
3816 --Standard API Compatibility Call.
3817 IF NOT FND_API.Compatible_API_Call ( l_api_version_number ,
3818 p_api_version_number ,
3819 l_api_name ,
3820 G_PKG_NAME )
3821 THEN
3822 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3823 END IF;
3824
3825 G_Plan_RL_Format_tbl_count := G_Plan_RL_Format_tbl_count + 1;
3826
3827 G_Plan_RL_format_In_Tbl(G_Plan_RL_Format_tbl_count).P_Res_Format_Id
3828 := P_Res_Format_Id;
3829
3830 EXCEPTION
3831 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
3832 THEN
3833 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3834 WHEN OTHERS THEN
3835 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3836 FND_MSG_PUB.Add_Exc_Msg
3837 ( p_pkg_name => G_PKG_NAME ,
3838 p_procedure_name => l_api_name
3839 );
3840 END Load_Resource_Format;
3841
3842 /*************************************************
3843 * Procedure : Load_Planning_Resource
3844 * Description : This procedure loads the resource
3845 * list members globals.
3846 * The detailed information is in the spec.
3847 ***********************************************/
3848 PROCEDURE Load_Planning_Resource
3849 (p_api_version_number IN Number,
3850 p_resource_list_member_id IN NUMBER DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
3851 p_resource_alias IN VARCHAR2 DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
3852 p_person_id IN NUMBER DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
3853 p_person_name IN VARCHAR2 DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
3854 p_job_id IN NUMBER DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
3855 p_job_name IN VARCHAR2 DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
3856 p_organization_id IN NUMBER DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
3857 p_organization_name IN VARCHAR2 DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
3858 p_vendor_id IN NUMBER DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
3859 p_vendor_name IN VARCHAR2 DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
3860 p_fin_category_name IN VARCHAR2 DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
3861 p_non_labor_resource IN VARCHAR2 DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
3862 p_project_role_id IN NUMBER DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
3863 p_project_role_name IN VARCHAR2 DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
3864 p_resource_class_id IN NUMBER DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
3865 p_resource_class_code IN VARCHAR2 DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
3866 p_res_format_id IN NUMBER ,
3867 p_spread_curve_id IN NUMBER DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
3868 p_etc_method_code IN VARCHAR2 DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
3869 p_mfc_cost_type_id IN NUMBER DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
3870 p_fc_res_type_code IN VARCHAR2 DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
3871 p_inventory_item_id IN NUMBER DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
3872 p_inventory_item_name IN VARCHAR2 DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
3873 p_item_category_id IN NUMBER DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
3874 p_item_category_name IN VARCHAR2 DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
3875 p_attribute_category IN VARCHAR2 DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
3876 p_attribute1 IN VARCHAR2 DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
3877 p_attribute2 IN VARCHAR2 DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
3878 p_attribute3 IN VARCHAR2 DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
3879 p_attribute4 IN VARCHAR2 DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
3880 p_attribute5 IN VARCHAR2 DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
3881 p_attribute6 IN VARCHAR2 DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
3882 p_attribute7 IN VARCHAR2 DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
3883 p_attribute8 IN VARCHAR2 DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
3884 p_attribute9 IN VARCHAR2 DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
3885 p_attribute10 IN VARCHAR2 DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
3886 p_attribute11 IN VARCHAR2 DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
3887 p_attribute12 IN VARCHAR2 DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
3888 p_attribute13 IN VARCHAR2 DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
3889 p_attribute14 IN VARCHAR2 DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
3890 p_attribute15 IN VARCHAR2 DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
3891 p_attribute16 IN VARCHAR2 DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
3892 p_attribute17 IN VARCHAR2 DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
3893 p_attribute18 IN VARCHAR2 DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
3894 p_attribute19 IN VARCHAR2 DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
3895 p_attribute20 IN VARCHAR2 DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
3896 p_attribute21 IN VARCHAR2 DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
3897 p_attribute22 IN VARCHAR2 DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
3898 p_attribute23 IN VARCHAR2 DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
3899 p_attribute24 IN VARCHAR2 DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
3900 p_attribute25 IN VARCHAR2 DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
3901 p_attribute26 IN VARCHAR2 DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
3902 p_attribute27 IN VARCHAR2 DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
3903 p_attribute28 IN VARCHAR2 DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
3904 p_attribute29 IN VARCHAR2 DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
3905 p_attribute30 IN VARCHAR2 DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
3906 p_person_type_code IN VARCHAR2 DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
3907 p_bom_resource_id IN NUMBER DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
3908 p_bom_resource_name IN VARCHAR2 DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
3909 p_team_role IN VARCHAR2 DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
3910 p_incur_by_res_code IN VARCHAR2 DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
3911 p_incur_by_res_type IN VARCHAR2 DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
3912 p_record_version_number IN NUMBER,
3913 p_project_id IN NUMBER ,
3914 p_enabled_flag IN Varchar2 DEFAULT PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
3915 x_return_status OUT NOCOPY Varchar2)
3916 IS
3917 l_api_version_number CONSTANT NUMBER := G_API_VERSION_NUMBER;
3918 l_api_name CONSTANT VARCHAR2(30) := 'Load_Planning_Resource';
3919 BEGIN
3920 x_return_status := FND_API.g_ret_sts_success;
3921
3922 --Standard API Compatibility Call.
3923 IF NOT FND_API.Compatible_API_Call ( l_api_version_number ,
3924 p_api_version_number ,
3925 l_api_name ,
3926 G_PKG_NAME )
3927 THEN
3928 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3929 END IF;
3930
3931 G_Plan_Resource_tbl_count := G_Plan_Resource_tbl_count + 1;
3932
3933 G_Planning_resource_in_tbl(G_Plan_Resource_tbl_count).p_resource_list_member_id := p_resource_list_member_id;
3934 G_Planning_resource_in_tbl(G_Plan_Resource_tbl_count).p_resource_alias
3935 := p_resource_alias;
3936 G_Planning_resource_in_tbl(G_Plan_Resource_tbl_count).p_person_id
3937 := p_person_id;
3938 G_Planning_resource_in_tbl(G_Plan_Resource_tbl_count).p_person_name
3939 := p_person_name;
3940 G_Planning_resource_in_tbl(G_Plan_Resource_tbl_count).p_job_id
3941 := p_job_id;
3942 G_Planning_resource_in_tbl(G_Plan_Resource_tbl_count).p_job_name
3943 := p_job_name;
3944 G_Planning_resource_in_tbl(G_Plan_Resource_tbl_count).p_organization_id
3945 := p_organization_id;
3946 G_Planning_resource_in_tbl(G_Plan_Resource_tbl_count).p_organization_name
3947 := p_organization_name;
3948 G_Planning_resource_in_tbl(G_Plan_Resource_tbl_count).p_vendor_id
3949 := p_vendor_id;
3950 G_Planning_resource_in_tbl(G_Plan_Resource_tbl_count).p_vendor_name
3951 := p_vendor_name;
3952 G_Planning_resource_in_tbl(G_Plan_Resource_tbl_count).p_fin_category_name
3953 := p_fin_category_name;
3954 G_Planning_resource_in_tbl(G_Plan_Resource_tbl_count).p_non_labor_resource
3955 := p_non_labor_resource;
3956 G_Planning_resource_in_tbl(G_Plan_Resource_tbl_count).p_project_role_id
3957 := p_project_role_id;
3958 G_Planning_resource_in_tbl(G_Plan_Resource_tbl_count).p_project_role_name
3959 := p_project_role_name;
3960 G_Planning_resource_in_tbl(G_Plan_Resource_tbl_count).p_resource_class_id
3961 := p_resource_class_id;
3962 G_Planning_resource_in_tbl(G_Plan_Resource_tbl_count).p_resource_class_code := p_resource_class_code;
3963 G_Planning_resource_in_tbl(G_Plan_Resource_tbl_count).p_res_format_id
3964 := p_res_format_id;
3965 G_Planning_resource_in_tbl(G_Plan_Resource_tbl_count).p_spread_curve_id
3966 := p_spread_curve_id;
3967 G_Planning_resource_in_tbl(G_Plan_Resource_tbl_count).p_etc_method_code
3968 := p_etc_method_code;
3969 G_Planning_resource_in_tbl(G_Plan_Resource_tbl_count).p_mfc_cost_type_id
3970 := p_mfc_cost_type_id;
3971 G_Planning_resource_in_tbl(G_Plan_Resource_tbl_count).p_fc_res_type_code
3972 := p_fc_res_type_code;
3973 G_Planning_resource_in_tbl(G_Plan_Resource_tbl_count).p_inventory_item_id
3974 := p_inventory_item_id;
3975 G_Planning_resource_in_tbl(G_Plan_Resource_tbl_count).p_inventory_item_name
3976 := p_inventory_item_name;
3977 G_Planning_resource_in_tbl(G_Plan_Resource_tbl_count).p_item_category_id
3978 := p_item_category_id;
3979 G_Planning_resource_in_tbl(G_Plan_Resource_tbl_count).p_item_category_name
3980 := p_item_category_name;
3981 G_Planning_resource_in_tbl(G_Plan_Resource_tbl_count).p_attribute_category
3982 := p_attribute_category;
3983 G_Planning_resource_in_tbl(G_Plan_Resource_tbl_count).p_attribute1
3984 := p_attribute1;
3985 G_Planning_resource_in_tbl(G_Plan_Resource_tbl_count).p_attribute2
3986 := p_attribute2;
3987 G_Planning_resource_in_tbl(G_Plan_Resource_tbl_count).p_attribute3
3988 := p_attribute3;
3989 G_Planning_resource_in_tbl(G_Plan_Resource_tbl_count).p_attribute4
3990 := p_attribute4;
3991 G_Planning_resource_in_tbl(G_Plan_Resource_tbl_count).p_attribute5
3992 := p_attribute5;
3993 G_Planning_resource_in_tbl(G_Plan_Resource_tbl_count).p_attribute6
3994 := p_attribute6;
3995 G_Planning_resource_in_tbl(G_Plan_Resource_tbl_count).p_attribute7
3996 := p_attribute7;
3997 G_Planning_resource_in_tbl(G_Plan_Resource_tbl_count).p_attribute8
3998 := p_attribute8;
3999 G_Planning_resource_in_tbl(G_Plan_Resource_tbl_count).p_attribute9
4000 := p_attribute9;
4001 G_Planning_resource_in_tbl(G_Plan_Resource_tbl_count).p_attribute10
4002 := p_attribute10;
4003 G_Planning_resource_in_tbl(G_Plan_Resource_tbl_count).p_attribute11
4004 := p_attribute11;
4005 G_Planning_resource_in_tbl(G_Plan_Resource_tbl_count).p_attribute12
4006 := p_attribute12;
4007 G_Planning_resource_in_tbl(G_Plan_Resource_tbl_count).p_attribute13
4008 := p_attribute13;
4009 G_Planning_resource_in_tbl(G_Plan_Resource_tbl_count).p_attribute14
4010 := p_attribute14;
4011 G_Planning_resource_in_tbl(G_Plan_Resource_tbl_count).p_attribute15
4012 := p_attribute15;
4013 G_Planning_resource_in_tbl(G_Plan_Resource_tbl_count).p_attribute16
4014 := p_attribute16;
4015 G_Planning_resource_in_tbl(G_Plan_Resource_tbl_count).p_attribute17
4016 := p_attribute17;
4017 G_Planning_resource_in_tbl(G_Plan_Resource_tbl_count).p_attribute18
4018 := p_attribute18;
4019 G_Planning_resource_in_tbl(G_Plan_Resource_tbl_count).p_attribute19
4020 := p_attribute19;
4021 G_Planning_resource_in_tbl(G_Plan_Resource_tbl_count).p_attribute20
4022 := p_attribute20;
4023 G_Planning_resource_in_tbl(G_Plan_Resource_tbl_count).p_attribute21
4024 := p_attribute21;
4025 G_Planning_resource_in_tbl(G_Plan_Resource_tbl_count).p_attribute22
4026 := p_attribute22;
4027 G_Planning_resource_in_tbl(G_Plan_Resource_tbl_count).p_attribute23
4028 := p_attribute23;
4029 G_Planning_resource_in_tbl(G_Plan_Resource_tbl_count).p_attribute24
4030 := p_attribute24;
4031 G_Planning_resource_in_tbl(G_Plan_Resource_tbl_count).p_attribute25
4032 := p_attribute25;
4033 G_Planning_resource_in_tbl(G_Plan_Resource_tbl_count).p_attribute26
4034 := p_attribute26;
4035 G_Planning_resource_in_tbl(G_Plan_Resource_tbl_count).p_attribute27
4036 := p_attribute27;
4037 G_Planning_resource_in_tbl(G_Plan_Resource_tbl_count).p_attribute28
4038 := p_attribute28;
4039 G_Planning_resource_in_tbl(G_Plan_Resource_tbl_count).p_attribute29
4040 := p_attribute29;
4041 G_Planning_resource_in_tbl(G_Plan_Resource_tbl_count).p_attribute30
4042 := p_attribute30;
4043 G_Planning_resource_in_tbl(G_Plan_Resource_tbl_count).p_person_type_code
4044 := p_person_type_code;
4045 G_Planning_resource_in_tbl(G_Plan_Resource_tbl_count).p_bom_resource_id
4046 := p_bom_resource_id;
4047 G_Planning_resource_in_tbl(G_Plan_Resource_tbl_count).p_bom_resource_name
4048 := p_bom_resource_name;
4049 G_Planning_resource_in_tbl(G_Plan_Resource_tbl_count).p_team_role
4050 := p_team_role;
4051 G_Planning_resource_in_tbl(G_Plan_Resource_tbl_count).p_incur_by_res_code
4052 := p_incur_by_res_code;
4053 G_Planning_resource_in_tbl(G_Plan_Resource_tbl_count).p_incur_by_res_type
4054 := p_incur_by_res_type;
4055 G_Planning_resource_in_tbl(G_Plan_Resource_tbl_count).p_record_version_number
4056 := p_record_version_number;
4057 G_Planning_resource_in_tbl(G_Plan_Resource_tbl_count).p_project_id
4058 := p_project_id;
4059 G_Planning_resource_in_tbl(G_Plan_Resource_tbl_count).p_enabled_flag
4060 := p_enabled_flag;
4061
4062 EXCEPTION
4063 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
4064 THEN
4065 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4066 WHEN OTHERS THEN
4067 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4068 FND_MSG_PUB.Add_Exc_Msg
4069 ( p_pkg_name => G_PKG_NAME ,
4070 p_procedure_name => l_api_name
4071 );
4072 END Load_Planning_Resource;
4073
4074 /**************************************************
4075 * Procedure : Exec_Create_Resource_List
4076 * Description : This procedure passes the PL/SQL
4077 * globals to the Create_Resource_List API.
4078 * The API is typically used with the
4079 * load-execute-fetch model.
4080 * The detailed information is in spec.
4081 ***************************************************/
4082 PROCEDURE Exec_Create_Resource_List
4083 (p_commit IN VARCHAR2 := FND_API.G_FALSE,
4084 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
4085 p_api_version_number IN NUMBER,
4086 x_return_status OUT NOCOPY VARCHAR2,
4087 x_msg_count OUT NOCOPY NUMBER,
4088 x_msg_data OUT NOCOPY VARCHAR2 )
4089 IS
4090 l_api_version_number CONSTANT NUMBER := G_API_VERSION_NUMBER;
4091 l_api_name CONSTANT VARCHAR2(30) := 'Exec_Create_Resource_List';
4092 l_message_count NUMBER;
4093 BEGIN
4094 x_return_status := FND_API.g_ret_sts_success;
4095
4096 IF NOT FND_API.Compatible_API_Call ( l_api_version_number ,
4097 p_api_version_number ,
4098 l_api_name ,
4099 G_PKG_NAME )
4100 THEN
4101 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4102 END IF;
4103
4104 Create_Resource_List
4105 (p_commit => p_commit,
4106 p_init_msg_list => p_init_msg_list,
4107 p_api_version_number => p_api_version_number,
4108 P_plan_res_list_Rec => G_Plan_Res_List_IN_Rec,
4109 X_plan_res_list_Rec => G_Plan_Res_List_Out_Rec,
4110 P_Plan_RL_Format_Tbl => G_Plan_RL_format_In_Tbl,
4111 X_Plan_RL_Format_Tbl => G_Plan_RL_format_Out_Tbl,
4112 P_planning_resource_in_tbl => G_Planning_resource_in_tbl,
4113 X_planning_resource_out_tbl => G_Planning_resource_out_tbl,
4114 X_Return_Status => x_return_status ,
4115 X_Msg_Count => x_msg_count,
4116 X_Msg_Data => x_msg_data);
4117
4118 EXCEPTION
4119
4120 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4121 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4122 WHEN OTHERS THEN
4123 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4124 -- 4537865
4125 x_msg_data := SUBSTRB(SQLERRM,1,240);
4126 x_msg_count := 1 ;
4127 -- 4537865
4128 FND_MSG_PUB.Add_Exc_Msg
4129 ( p_pkg_name => G_PKG_NAME ,
4130 p_procedure_name => l_api_name
4131 );
4132 END Exec_Create_Resource_List;
4133 /*****************************/
4134 /**************************************************
4135 * Procedure : Exec_Update_Resource_List
4136 * Description : This procedure passes the PL/SQL
4137 * globals to the Update_Resource_List API.
4138 * The API is typically used with the
4139 * load-execute-fetch model.
4140 * The detailed information is in spec.
4141 ***************************************************/
4142 PROCEDURE Exec_Update_Resource_List
4143 (p_commit IN VARCHAR2 := FND_API.G_FALSE,
4144 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
4145 p_api_version_number IN NUMBER,
4146 x_return_status OUT NOCOPY VARCHAR2,
4147 x_msg_count OUT NOCOPY NUMBER,
4148 x_msg_data OUT NOCOPY VARCHAR2
4149 )
4150 IS
4151 l_api_version_number CONSTANT NUMBER := G_API_VERSION_NUMBER;
4152 l_api_name CONSTANT VARCHAR2(30) := 'Exec_Update_Resource_List';
4153 l_message_count NUMBER;
4154 BEGIN
4155 x_return_status := FND_API.g_ret_sts_success;
4156
4157 IF NOT FND_API.Compatible_API_Call ( l_api_version_number ,
4158 p_api_version_number ,
4159 l_api_name ,
4160 G_PKG_NAME )
4161 THEN
4162 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4163 END IF;
4164
4165 Update_Resource_List
4166 (p_commit => p_commit,
4167 p_init_msg_list => p_init_msg_list,
4168 p_api_version_number => p_api_version_number,
4169 P_plan_res_list_Rec => G_Plan_Res_List_IN_Rec,
4170 X_plan_res_list_Rec => G_Plan_Res_List_Out_Rec,
4171 P_Plan_RL_Format_Tbl => G_Plan_RL_format_In_Tbl,
4172 X_Plan_RL_Format_Tbl => G_Plan_RL_format_Out_Tbl,
4173 P_planning_resource_in_tbl => G_Planning_resource_in_tbl,
4174 X_planning_resource_out_tbl => G_Planning_resource_out_tbl,
4175 X_Return_Status => x_return_status,
4176 X_Msg_Count => x_msg_count,
4177 X_Msg_Data => x_msg_data);
4178 -- 4537865 : Included Exception Block
4179 EXCEPTION
4180
4181 WHEN OTHERS THEN
4182 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4183 x_msg_data := SUBSTRB(SQLERRM,1,240);
4184 x_msg_count := 1 ;
4185 FND_MSG_PUB.Add_Exc_Msg
4186 ( p_pkg_name => G_PKG_NAME ,
4187 p_procedure_name => l_api_name ,
4188 p_error_text => x_msg_data
4189 );
4190 END Exec_Update_Resource_List;
4191 /*****************************/
4192 /******************************************************
4193 * Procedure : Fetch_Resource_List
4194 * Description : This procedure returns the return status
4195 * and the newly created resource_list_id
4196 * if any, from a load-execute-fetch cycle.
4197 * The detailed information is in the spec.
4198 *******************************************************/
4199 PROCEDURE Fetch_Resource_List
4200 (
4201 p_api_version_number IN NUMBER,
4202 x_return_status OUT NOCOPY VARCHAR2,
4203 x_resource_list_id OUT NOCOPY NUMBER)
4204 IS
4205 l_api_version_number CONSTANT NUMBER := G_API_VERSION_NUMBER;
4206 l_api_name CONSTANT VARCHAR2(30):= 'Fetch_Resource_List';
4207 l_msg_count INTEGER :=0;
4208 BEGIN
4209 x_return_status := FND_API.G_RET_STS_SUCCESS;
4210
4211 IF NOT FND_API.Compatible_API_Call ( l_api_version_number ,
4212 p_api_version_number ,
4213 l_api_name ,
4214 G_PKG_NAME )
4215 THEN
4216 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4217 END IF;
4218
4219 x_resource_list_id := G_Plan_Res_List_Out_Rec.X_resource_list_id;
4220
4221 EXCEPTION
4222 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4223 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4224 -- 4537865 : RESETTING x_resource_list_id also
4225 x_resource_list_id := NULL ;
4226 WHEN OTHERS THEN
4227 -- 4537865 : RESETTING x_resource_list_id also
4228 x_resource_list_id := NULL ;
4229
4230 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4231 FND_MSG_PUB.Add_Exc_Msg
4232 ( p_pkg_name => G_PKG_NAME ,
4233 p_procedure_name => l_api_name
4234 );
4235 END Fetch_Resource_List;
4236
4237 /******************************************************
4238 * Procedure : Fetch_Plan_Format
4239 * Description : This procedure returns the return status
4240 * and the newly created Plan_rl_format_id
4241 * if any, from a load-execute-fetch cycle.
4242 * The detailed information is in the spec.
4243 *******************************************************/
4244 PROCEDURE Fetch_Plan_Format
4245 ( p_api_version_number IN NUMBER,
4246 p_format_index IN NUMBER
4247 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
4248 x_return_status OUT NOCOPY VARCHAR2,
4249 X_Plan_RL_Format_Id OUT NOCOPY NUMBER)
4250 IS
4251 l_api_version_number CONSTANT NUMBER := G_API_VERSION_NUMBER;
4252 l_api_name CONSTANT VARCHAR2(30):= 'Fetch_Plan_Format';
4253 l_index NUMBER;
4254 BEGIN
4255 x_return_status := FND_API.G_RET_STS_SUCCESS;
4256 IF NOT FND_API.Compatible_API_Call ( l_api_version_number ,
4257 p_api_version_number ,
4258 l_api_name ,
4259 G_PKG_NAME )
4260 THEN
4261 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4262 END IF;
4263
4264 IF p_format_index = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM THEN
4265 l_index := 1;
4266 ELSE
4267 l_index := p_format_index ;
4268 END IF;
4269
4270 IF NOT G_Plan_RL_format_In_Tbl.EXISTS(l_index) THEN
4271 X_Plan_RL_Format_Id := Null;
4272 ELSE
4273 X_Plan_RL_Format_Id :=
4274 G_Plan_RL_format_Out_Tbl(l_index).X_Plan_RL_Format_Id;
4275 END IF;
4276
4277 EXCEPTION
4278 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4279 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4280 -- 4537865 : RESETTING X_Plan_RL_Format_Id also
4281 X_Plan_RL_Format_Id := NULL ;
4282
4283 WHEN OTHERS THEN
4284 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4285 -- 4537865 : RESETTING X_Plan_RL_Format_Id also
4286 X_Plan_RL_Format_Id := NULL ;
4287 FND_MSG_PUB.Add_Exc_Msg
4288 ( p_pkg_name => G_PKG_NAME ,
4289 p_procedure_name => l_api_name
4290 );
4291 END Fetch_Plan_Format;
4292
4293 /******************************************************
4294 * Procedure : Fetch_Resource_List_Member
4295 * Description : This procedure returns the return status
4296 * and the newly created resource_list_id
4297 * if any, from a load-execute-fetch cycle.
4298 * The detailed information is in spec.
4299 *******************************************************/
4300 PROCEDURE Fetch_Resource_List_Member
4301 ( p_api_version_number IN NUMBER,
4302 p_member_index IN NUMBER
4303 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
4304 x_return_status OUT NOCOPY VARCHAR2,
4305 x_resource_list_member_id OUT NOCOPY NUMBER)
4306 IS
4307 l_api_version_number CONSTANT NUMBER := G_API_VERSION_NUMBER;
4308 l_api_name CONSTANT VARCHAR2(30):= 'Fetch_Resource_List_Member';
4309 l_index NUMBER;
4310 BEGIN
4311 x_return_status := FND_API.G_RET_STS_SUCCESS;
4312 IF NOT FND_API.Compatible_API_Call ( l_api_version_number ,
4313 p_api_version_number ,
4314 l_api_name ,
4315 G_PKG_NAME )
4316 THEN
4317 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4318 END IF;
4319
4320 IF p_member_index = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM THEN
4321 l_index := 1;
4322 ELSE
4323 l_index := p_member_index ;
4324 END IF;
4325
4326 IF NOT G_Planning_resource_in_tbl.EXISTS(l_index) THEN
4327 x_resource_list_member_id := NULL;
4328 ELSE
4329 x_resource_list_member_id :=
4330 G_Planning_resource_out_tbl(l_index).x_resource_list_member_id;
4331 END IF;
4332 -- 4537865
4333 EXCEPTION
4334 WHEN OTHERS THEN
4335 x_resource_list_member_id := NULL ;
4336 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4337 FND_MSG_PUB.Add_Exc_Msg
4338 ( p_pkg_name => G_PKG_NAME ,
4339 p_procedure_name => l_api_name
4340 );
4341 -- Not RAISING because other similar APIs dont have RAISE
4342 END Fetch_Resource_List_Member;
4343
4344 END Pa_Plan_Res_List_Pub;
[Home] [Help]