1 PACKAGE BODY Pa_Planning_Resource_Pvt AS
2 /* $Header: PAPRESVB.pls 120.6 2006/03/13 04:39:55 avaithia noship $*/
3
4 /*************************************************************
5 * Function : Check_pl_alias_unique
6 * Description : The purpose of this function is to determine
7 * the uniqueness of the resource alias if it is not null.
8 * While inserting when we call this function then if 'N'
9 * is returned then proceed else throw an error.
10 *************************************************************/
11 FUNCTION Check_pl_alias_unique(
12 p_resource_list_id IN VARCHAR2,
13 p_resource_alias IN VARCHAR2,
14 p_resource_list_member_id IN VARCHAR2,
15 p_object_type IN VARCHAR2,
16 p_object_id IN NUMBER)
17 RETURN VARCHAR2
18 IS
19 l_check_unique_res varchar2(30) := 'Y';
20 BEGIN
21
22 BEGIN
23 SELECT 'N'
24 INTO l_check_unique_res
25 FROM pa_resource_list_members
26 WHERE resource_list_id = p_resource_list_id
27 AND alias = p_resource_alias
28 AND object_type = p_object_type
29 AND object_id = p_object_id
30 AND resource_list_member_id <>
31 nvl(p_resource_list_member_id,-99);
32 EXCEPTION
33 WHEN NO_DATA_FOUND THEN
34 l_check_unique_res := 'Y';
35 END;
36 RETURN l_check_unique_res;
37 END Check_pl_alias_unique;
38 /***********************************/
39 /************************************************************
40 * Function : Default_uom
41 * Desc :
42 ************************************************************/
43 FUNCTION Default_uom(
44 p_resource_class_code IN VARCHAR2,
45 p_inventory_item_id IN NUMBER,
46 p_organization_id IN NUMBER,
47 p_expenditure_type IN VARCHAR2)
48 RETURN VARCHAR2
49 IS
50 l_uom VARCHAR2(30);
51 l_currency VARCHAR2(1);
52 l_organization_id NUMBER := p_organization_id;
53 l_master_organization_id NUMBER;
54 BEGIN
55
56 SELECT def.item_master_id
57 INTO l_master_organization_id
58 FROM pa_resource_classes_b cls,
59 pa_plan_res_defaults def
60 WHERE cls.resource_class_code = 'MATERIAL_ITEMS'
61 AND cls.resource_class_id = def.resource_class_id
62 AND def.object_type = 'CLASS';
63
64 IF p_organization_id IS NULL THEN
65 l_organization_id := l_master_organization_id;
66 ELSE
67 l_organization_id := p_organization_id;
68 END IF;
69
70 IF p_resource_class_code IN ('PEOPLE','EQUIPMENT') THEN
71 l_uom := 'HOURS';
72 END IF;
73
74 IF p_resource_class_code = 'MATERIAL_ITEMS' AND l_uom IS NULL AND
75 p_inventory_item_id IS NOT NULL THEN
76 BEGIN
77 SELECT primary_uom_code
78 INTO l_uom
79 FROM mtl_system_items_b items
80 WHERE items.inventory_item_id = p_inventory_item_id
81 AND items.organization_id = l_organization_id
82 AND ROWNUM = 1;
83
84 EXCEPTION WHEN NO_DATA_FOUND THEN
85 l_uom := NULL;
86 END;
87
88 IF (l_uom IS NULL) AND
89 (l_organization_id <> l_master_organization_id) THEN
90 SELECT primary_uom_code
91 INTO l_uom
92 FROM mtl_system_items_b items
93 WHERE items.inventory_item_id = p_inventory_item_id
94 AND items.organization_id = l_master_organization_id;
95 END IF;
96
97 IF l_uom IS NOT NULL THEN
98
99 l_currency := 'N';
100
101 BEGIN
102 SELECT 'Y'
103 INTO l_currency
104 FROM mtl_units_of_measure meas
105 WHERE meas.uom_code = l_uom
106 AND meas.uom_class = 'Currency';
107 EXCEPTION WHEN NO_DATA_FOUND THEN
108 l_currency := 'N';
109
110 END;
111
112 IF l_currency = 'Y' THEN
113 l_uom := 'DOLLARS';
114 END IF;
115 END IF;
116 END IF;
117
118 IF p_resource_class_code IN ('MATERIAL_ITEMS', 'FINANCIAL_ELEMENTS')
119 AND l_uom IS NULL
120 AND p_inventory_item_id IS NULL
121 AND p_expenditure_type IS NOT NULL
122 THEN
123 BEGIN
124 SELECT unit_of_measure
125 INTO l_uom
126 FROM pa_expenditure_types et
127 WHERE et.expenditure_type = p_expenditure_type
128 AND ROWNUM = 1;
129 END;
130 END IF;
131 IF l_uom IS NULL THEN
132 l_uom := 'DOLLARS';
133 END IF;
134 Return l_uom;
135 EXCEPTION
136 WHEN OTHERS THEN
137 l_uom := Null;
138 Return l_uom;
139 END Default_uom;
140
141 /**************************************************************
142 * Procedure : Create_Planning_Resource
143 * Description : The purpose of this procedure is to Validate
144 * and create a new planning resource for a
145 * resource list.
146 * It first checks for the uniqueness of the
147 * p_resource_alias
148 * It gets the appr resource_class_code of it is Null
149 ****************************************************************/
150 PROCEDURE Create_Planning_Resource
151 (p_resource_list_member_id IN NUMBER DEFAULT NULL,
152 p_resource_list_id IN VARCHAR2,
153 p_resource_alias IN VARCHAR2 DEFAULT NULL,
154 p_person_id IN NUMBER DEFAULT NULL,
155 p_person_name IN VARCHAR2 DEFAULT NULL,
156 p_job_id IN NUMBER DEFAULT NULL,
157 p_job_name IN VARCHAR2 DEFAULT NULL,
158 p_organization_id IN NUMBER DEFAULT NULL,
159 p_organization_name IN VARCHAR2 DEFAULT NULL,
160 p_vendor_id IN NUMBER DEFAULT NULL,
161 p_vendor_name IN VARCHAR2 DEFAULT NULL,
162 p_fin_category_name IN VARCHAR2 DEFAULT NULL,
163 p_non_labor_resource IN VARCHAR2 DEFAULT NULL,
164 p_project_role_id IN NUMBER DEFAULT NULL,
165 p_project_role_name IN VARCHAR2 DEFAULT NULL,
166 p_resource_class_id IN NUMBER DEFAULT NULL,
167 p_resource_class_code IN VARCHAR2 DEFAULT NULL,
168 p_res_format_id IN NUMBER ,
169 p_spread_curve_id IN NUMBER DEFAULT NULL,
170 p_etc_method_code IN VARCHAR2 DEFAULT NULL,
171 p_mfc_cost_type_id IN NUMBER DEFAULT NULL,
172 p_copy_from_rl_flag IN VARCHAR2 DEFAULT NULL,
173 p_resource_class_flag IN VARCHAR2 DEFAULT NULL,
174 p_fc_res_type_code IN VARCHAR2 DEFAULT NULL,
175 p_inventory_item_id IN NUMBER DEFAULT NULL,
176 p_inventory_item_name IN VARCHAR2 DEFAULT NULL,
177 p_item_category_id IN NUMBER DEFAULT NULL,
178 p_item_category_name IN VARCHAR2 DEFAULT NULL,
179 p_migration_code IN VARCHAR2 DEFAULT 'N',
180 p_attribute_category IN VARCHAR2 DEFAULT NULL,
181 p_attribute1 IN VARCHAR2 DEFAULT NULL,
182 p_attribute2 IN VARCHAR2 DEFAULT NULL,
183 p_attribute3 IN VARCHAR2 DEFAULT NULL,
184 p_attribute4 IN VARCHAR2 DEFAULT NULL,
185 p_attribute5 IN VARCHAR2 DEFAULT NULL,
186 p_attribute6 IN VARCHAR2 DEFAULT NULL,
187 p_attribute7 IN VARCHAR2 DEFAULT NULL,
188 p_attribute8 IN VARCHAR2 DEFAULT NULL,
189 p_attribute9 IN VARCHAR2 DEFAULT NULL,
190 p_attribute10 IN VARCHAR2 DEFAULT NULL,
191 p_attribute11 IN VARCHAR2 DEFAULT NULL,
192 p_attribute12 IN VARCHAR2 DEFAULT NULL,
193 p_attribute13 IN VARCHAR2 DEFAULT NULL,
194 p_attribute14 IN VARCHAR2 DEFAULT NULL,
195 p_attribute15 IN VARCHAR2 DEFAULT NULL,
196 p_attribute16 IN VARCHAR2 DEFAULT NULL,
197 p_attribute17 IN VARCHAR2 DEFAULT NULL,
198 p_attribute18 IN VARCHAR2 DEFAULT NULL,
199 p_attribute19 IN VARCHAR2 DEFAULT NULL,
200 p_attribute20 IN VARCHAR2 DEFAULT NULL,
201 p_attribute21 IN VARCHAR2 DEFAULT NULL,
202 p_attribute22 IN VARCHAR2 DEFAULT NULL,
203 p_attribute23 IN VARCHAR2 DEFAULT NULL,
204 p_attribute24 IN VARCHAR2 DEFAULT NULL,
205 p_attribute25 IN VARCHAR2 DEFAULT NULL,
206 p_attribute26 IN VARCHAR2 DEFAULT NULL,
207 p_attribute27 IN VARCHAR2 DEFAULT NULL,
208 p_attribute28 IN VARCHAR2 DEFAULT NULL,
209 p_attribute29 IN VARCHAR2 DEFAULT NULL,
210 p_attribute30 IN VARCHAR2 DEFAULT NULL,
211 p_person_type_code IN VARCHAR2 DEFAULT NULL,
212 p_bom_resource_id IN NUMBER DEFAULT NULL,
213 p_bom_resource_name IN VARCHAR2 DEFAULT NULL,
214 -- Team Role changes
215 p_team_role IN VARCHAR2 DEFAULT NULL,
216 --p_named_role IN VARCHAR2 DEFAULT NULL,
217 p_incur_by_res_code IN VARCHAR2 DEFAULT NULL,
218 p_incur_by_res_type IN VARCHAR2 DEFAULT NULL,
219 --Added this new parameter for project specific res.
220 p_project_id IN NUMBER DEFAULT NULL,
221 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE, -- Added for bug#4350589
222 x_resource_list_member_id OUT NOCOPY NUMBER ,
223 x_record_version_number OUT NOCOPY NUMBER ,
224 x_return_status OUT NOCOPY VARCHAR2,
225 x_msg_count OUT NOCOPY NUMBER ,
226 x_error_msg_data OUT NOCOPY VARCHAR2)
227 IS
228 /********************************
229 * Cursor Declaration Section
230 ********************************/
231 Cursor get_class_details IS
232 SELECT resource_class_code
233 FROM pa_resource_classes_b
234 WHERE resource_class_id = p_resource_class_id;
235 Cursor get_fmt_details IS
236 SELECT res_type_id,res_type_enabled_flag,
237 resource_class_flag
238 FROM pa_res_formats_b
239 WHERE res_format_id = p_res_format_id;
240 Cursor get_res_type(p_res_type_id pa_res_types_b.res_type_id%TYPE )
241 IS
242 SELECT res_type_code
243 FROM pa_res_types_b
244 WHERE res_type_id = p_res_type_id;
245 /**************************************
246 * Local Variable Declaration
247 *************************************/
248 l_resource_alias VARCHAR2(80);
249 l_res_combo VARCHAR2(1000);
250 l_res_class_flag VARCHAR2(1);
251 l_incur_by_res_flag VARCHAR2(30) ;
252 l_vendor_id NUMBER := p_vendor_id;
253 l_role_id NUMBER := p_project_role_id;
254 -- Team Role changes
255 --Bug 3604528
256 l_team_role VARCHAR2(80) := p_team_role;
257 l_event_type VARCHAR2(30);
258 l_error_msg_data VARCHAR2(30);
259 l_res_list_member_id NUMBER := p_resource_list_member_id;
260 l_res_class_code VARCHAR2(30);
261 l_res_class_id Number;
262 l_fmt_details get_fmt_details%ROWTYPE;
263 l_res_type VARCHAR2(30);
264 l_person_name per_people_x.full_name%TYPE;
265 l_resource_id pa_resources.resource_id%TYPE;
266 l_unique_res_list VARCHAR2(30) := null;
267 --Local vars for Validate_planning_resource
268 l_resource_code VARCHAR2(30);
269 l_resource_name VARCHAR2(1000);
270 --For OUT
271 l_resource_list_member_id NUMBER;
272 l_person_id NUMBER;
273 l_bom_resource_id NUMBER;
274 l_job_id NUMBER;
275 l_person_type_code VARCHAR2(30);
276 l_non_labor_resource VARCHAR2(20);
277 l_inventory_item_id NUMBER;
278 l_item_category_id NUMBER;
279 l_organization_id NUMBER;
280 l_expenditure_type VARCHAR2(30);
281 l_expenditure_category VARCHAR2(30);
282 l_revenue_category VARCHAR2(30);
283 l_resource_class_id NUMBER;
284 l_incur_by_role_id NUMBER;
285 l_incur_by_res_class_code VARCHAR2(30);
286 l_return_status VARCHAR2(30);
287 l_err_code NUMBER;
288 l_err_stage VARCHAR2(100);
289 l_err_stack VARCHAR2(100);
290 l_msg_data VARCHAR2(100);
291 l_msg_count NUMBER;
292 l_spread_curve_id NUMBER;
293 l_etc_method_code VARCHAR2(30);
294 l_mfc_cost_type_id NUMBER;
295
296 -- used for getting inc by name for token for messages
297 l_inc_person_id NUMBER := NULL;
298 l_inc_job_id NUMBER := NULL;
299 l_inc_role_id NUMBER := NULL;
300 l_inc_person_type VARCHAR2(30) := NULL;
301 l_inc_class_code VARCHAR2(30) := NULL;
302
303 l_fin_cat_name VARCHAR2(80);
304 l_org_name VARCHAR2(80);
305 l_supplier_name VARCHAR2(80);
306 l_role_name VARCHAR2(80);
307 l_inc_by_name VARCHAR2(80);
308
309 l_res_class_valid Varchar2(1);
310
311 --Project specific changes.
312 l_object_type VARCHAR2(30);
313 l_object_id NUMBER;
314 l_wp_eligible_flag Varchar2(1);
315 l_num Number;
316 l_done Varchar2(1);
317 l_length Number;
318 l_uom Varchar2(30);
319 l_res_type_code Varchar2(30);
320 l_dummy_variable Varchar2(30);
321 BEGIN
322 -- FND_MSG_PUB.initialize; -- done in public pacakge
323 -- hr_utility.trace_on(NULL, 'RMDE');
324 -- hr_utility.trace('**** START ****');
325 -- hr_utility.trace('**** g_amg_flow IS ****' || g_amg_flow);
326 IF g_amg_flow = 'N' OR g_amg_flow IS NULL THEN
327 -- hr_utility.trace('**** IN IF g_amg_flow IS ****' || g_amg_flow);
328 g_token := NULL;
329
330 SELECT meaning || ' '
331 INTO g_token
332 FROM pa_lookups
333 WHERE lookup_type = 'PA_PLANNING_RESOURCE'
334 AND lookup_code = 'PLANNING_RESOURCE';
335 END IF;
336
337 --hr_utility.trace('g_token is : ' || g_token);
338 /******************************************************
339 * The below IF Condition is used to check for the
340 * uniqueness of the p_resource_alias. This is done by call
341 * to check_pl_alias_unique. If it returns 'Y' then throw an error
342 * and return, else continue with the validation.
343 * This If condn is only exec if the resource alias is not null.
344 *****************************************************/
345 x_msg_count := 0;
346 x_return_status := FND_API.G_RET_STS_SUCCESS;
347
348 -- Added for bug#4350589, this procedure is directly getting called from AddSingleResourceVORowImpl
349 -- passing p_init_msg_list value explicitly 'T' and message stack will be intialized
350 -- for other flows the default value is 'F' and message stack wont be intialized
351
352 IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_TRUE)) THEN
353 FND_MSG_PUB.initialize;
354 END IF;
355
356 -- bug#4350589 end
357
358 /*******************************************
359 * Assigning the value for l_object_type
360 * and l_object_id based on whether the
361 * Project ID value is passed or not.
362 * *****************************************/
363 IF p_project_id IS NOT NULL
364 THEN
365 l_object_type := 'PROJECT';
366 l_object_id := p_project_id;
367 ELSE
368 l_object_type := 'RESOURCE_LIST';
369 l_object_id := p_resource_list_id;
370 END IF;
371
372 IF p_resource_alias IS NOT NULL THEN
373 IF g_amg_flow = 'N' OR g_amg_flow IS NULL THEN
374 g_token := g_token || p_resource_alias || ':';
375 END IF;
376
377 IF pa_planning_resource_pvt.Check_pl_alias_unique(p_resource_list_id,
378 p_resource_alias,l_res_list_member_id,l_object_type,l_object_id) = 'N'
379 THEN
380 x_return_status := FND_API.G_RET_STS_ERROR;
381 x_msg_count := x_msg_count + 1;
382 x_error_msg_data := 'PA_RES_ALIAS_NOT_UNIQUE';
383 PA_UTILS.Add_Message ('PA', x_error_msg_data,
384 'PLAN_RES', g_token);
385 Return;
386 END IF;
387 END IF;
388 /******************************************************
389 * If the Resource class code is Null then we need
390 * to fetch it from the cursor get_class_details
391 * If the resource class code is Null and the cursor also
392 * doesn't return a value then throw an error and Return.
393 ********************************************************/
394 IF p_resource_class_code IS NULL
395 THEN
396 OPEN get_class_details;
397 FETCH get_class_details INTO l_res_class_code;
398 IF get_class_details%NOTFOUND THEN
399 x_return_status := FND_API.G_RET_STS_ERROR;
400 x_msg_count := x_msg_count + 1;
401 x_error_msg_data := 'PA_RES_NO_CLASS_PROVIDED';
402 PA_UTILS.Add_Message ('PA', x_error_msg_data,
403 'PLAN_RES', g_token);
404 Return;
405 END IF;
406 CLOSE get_class_details;
407 ELSE
408 l_res_class_code := p_resource_class_code;
409 END IF;
410
411 IF p_resource_class_id IS NULL
412 THEN
413 BEGIN
414 SELECT resource_class_id
415 INTO l_res_class_id
416 FROM pa_resource_classes_b
417 WHERE resource_class_code = l_res_class_code;
418 EXCEPTION
419 WHEN OTHERS THEN
420 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
421 x_msg_count := x_msg_count + 1;
422 Return;
423 END;
424 ELSE
425 l_res_class_id := p_resource_class_id;
426 END IF;
427
428 -- If both the class code and ID have been passed in, validate
429 -- that they are a valid pair - this is for AMG flows mostly
430 -- as the page should always pass in values which are in sync.
431 -- Bug 4507065.
432 IF p_resource_class_id IS NOT NULL AND
433 p_resource_class_code IS NOT NULL THEN
434 BEGIN
435 SELECT 'Y'
436 INTO l_res_class_valid
437 FROM pa_resource_classes_b
438 WHERE resource_class_code = p_resource_class_code
439 AND resource_class_id = p_resource_class_id;
440
441 EXCEPTION
442 WHEN OTHERS THEN
443 x_return_status := FND_API.G_RET_STS_ERROR;
444 x_msg_count := x_msg_count + 1;
445 x_error_msg_data := 'PA_RES_CLASS_INVALID';
446 PA_UTILS.Add_Message ('PA', x_error_msg_data,
447 'PLAN_RES', g_token);
448 Return;
449 END;
450
451 END IF;
452
453 /*********************************************************
454 * This If condition checks if the resource format ID is
455 * not null. IF NOT NULL then it needs to get the resource format details
456 * like res_type_id, res_type_enabled_flag and resource_class_flag.
457 * If the cursor does not return anything then we need to throw an error
458 * and Return.
459 *********************************************************/
460 IF p_res_format_id IS NOT NULL THEN
461 OPEN get_fmt_details;
462 FETCH get_fmt_details into l_fmt_details;
463 IF get_fmt_details%NOTFOUND THEN
464 x_return_status := FND_API.G_RET_STS_ERROR;
465 x_error_msg_data := 'PA_PLN_RL_FORMAT_BAD_FMT_ID';
466 PA_UTILS.Add_Message ('PA', x_error_msg_data,
467 'PLAN_RES', g_token);
468 x_record_version_number := null;
469 x_msg_count := x_msg_count + 1;
470 Return;
471 END IF;
472 CLOSE get_fmt_details;
473 END IF;
474 /**********************************************************
475 * This If condition checks if Resource is a part of the format.
476 * It makes use of the Values returned by the Prev cursor. ie
477 * This check is done only if the l_fmt_details.res_type_enabled_flag
478 * = 'Y'. If the cursor doesn't return a value then return.
479 *******************************************************/
480 IF l_fmt_details.res_type_enabled_flag = 'Y' THEN
481 OPEN get_res_type(l_fmt_details.res_type_id);
482 FETCH get_res_type INTO l_res_type;
483 IF get_res_type%NOTFOUND THEN
484 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
485 x_msg_count := x_msg_count + 1;
486 CLOSE get_res_type;
487 Return;
488 END IF;
489 CLOSE get_res_type;
490 ELSE
491 l_res_type := null;
492 END IF;
493 /***************************************************************
494 * Before Call to Validate_Resource_Planning Procedure,
495 * The values for some of the variables being passed need to be set.
496 * This condn needs to be executed only of l_res_type is not NULL.
497 * A value needs to be set for the l_resource_code and
498 * l_resource_name variables, based on the value of l_res_type.
499 **********************************************************/
500
501 IF l_res_type IS NOT NULL THEN
502 IF l_res_type = 'NAMED_PERSON' THEN
503 l_resource_code := p_person_id;
504 l_resource_name := p_person_name;
505 ELSIF l_res_type IN ('BOM_LABOR','BOM_EQUIPMENT') THEN
506 l_resource_code := p_bom_resource_id;
507 l_resource_name := p_bom_resource_name;
508 ELSIF l_res_type = 'NAMED_ROLE' THEN
509 -- Team Role changes
510 l_resource_code := p_team_role;
511 l_resource_name := p_team_role;
512 ELSIF l_res_type = 'JOB' THEN
513 l_resource_code := p_job_id;
514 l_resource_name := p_job_name;
515 ELSIF l_res_type = 'PERSON_TYPE' THEN
516 l_resource_code := p_person_type_code;
517 l_resource_name := p_person_type_code;
518 ELSIF l_res_type = 'NON_LABOR_RESOURCE' THEN
519 l_resource_code := p_non_labor_resource;
520 l_resource_name := p_non_labor_resource;
521 ELSIF l_res_type = 'INVENTORY_ITEM' THEN
522 l_resource_code := p_inventory_item_id;
523 l_resource_name := p_inventory_item_name;
524 ELSIF l_res_type = 'ITEM_CATEGORY' THEN
525 l_resource_code := p_item_category_id;
526 l_resource_name := p_item_category_name;
527 ELSIF l_res_type = 'RESOURCE_CLASS' THEN
528 l_resource_code := p_resource_class_code;
529 l_resource_name := p_resource_class_code;
530 END IF;
531
532 IF p_resource_alias IS NULL AND (g_amg_flow = 'N' OR g_amg_flow IS NULL)
533 THEN
534 --hr_utility.trace('before g_token is : ' || g_token);
535 g_token := g_token || pa_planning_resource_utils.ret_Resource_Name(
536 p_Res_Type_Code => l_res_type,
537 P_Person_Id => p_person_id,
538 P_Bom_Resource_Id => p_bom_resource_id,
539 P_Job_Id => p_job_id,
540 P_Person_Type_Code => l_resource_code,
541 P_Non_Labor_Resource => l_resource_code,
542 P_Inventory_Item_Id => p_inventory_item_id,
543 P_Resource_Class_Id => l_res_class_id,
544 P_Item_Category_Id => p_item_category_id,
545 p_res_assignment_id => NULL);
546 --hr_utility.trace('after g_token is : ' || g_token);
547 END IF;
548 END IF;
549
550 IF p_resource_alias IS NULL AND (g_amg_flow = 'N' OR g_amg_flow IS NULL)
551 THEN
552 IF p_incur_by_res_type IS NOT NULL THEN
553 IF p_incur_by_res_type = 'NAMED_PERSON' THEN
554 l_inc_person_id := p_incur_by_res_code;
555 ELSIF p_incur_by_res_type = 'JOB' THEN
556 l_inc_job_id := p_incur_by_res_code;
557 ELSIF p_incur_by_res_type = 'ROLE' THEN
558 l_inc_role_id := p_incur_by_res_code;
559 ELSIF p_incur_by_res_type = 'PERSON_TYPE' THEN
560 l_inc_person_type := p_incur_by_res_code;
561 ELSIF p_incur_by_res_type = 'RESOURCE_CLASS' THEN
562 l_inc_class_code := p_incur_by_res_code;
563 END IF;
564 END IF;
565 --hr_utility.trace('before all others g_token is : ' || g_token);
566 l_fin_cat_name := pa_planning_resource_utils.Ret_Fin_Category_Name(
567 P_FC_Res_Type_Code => p_fc_res_type_code,
568 P_Expenditure_Type => p_fin_category_name,
569 P_Expenditure_Category => p_fin_category_name,
570 P_Event_Type => p_fin_category_name,
571 P_Revenue_Category_Code => p_fin_category_name);
572 l_org_name := nvl(p_organization_name,
573 pa_planning_resource_utils.ret_Organization_Name(
574 P_Organization_Id => p_organization_id));
575 l_supplier_name := nvl(p_vendor_name,
576 pa_planning_resource_utils.ret_supplier_Name(
577 P_supplier_id => p_vendor_id));
578 l_role_name := p_team_role;
579 l_inc_by_name := pa_planning_resource_utils.Ret_Incur_By_Res_Name(
580 P_Person_Id => l_inc_person_id,
581 P_Job_Id => l_inc_job_id,
582 P_Incur_By_Role_Id => l_inc_role_id,
583 P_Person_Type_Code => l_inc_person_type,
584 P_Inc_By_Res_Class_Code => l_inc_class_code);
585 SELECT g_token ||
586 decode(l_fin_cat_name, NULL, NULL, '-' || l_fin_cat_name) ||
587 decode(l_org_name, NULL, NULL, '-' || l_org_name) ||
588 decode(l_supplier_name, NULL, NULL, '-' || l_supplier_name) ||
589 decode(l_role_name, NULL, NULL, '-' || l_role_name) ||
590 decode(l_inc_by_name, NULL, NULL, '-' || l_inc_by_name)
591 INTO g_token
592 FROM dual;
593 IF l_res_type IS NULL THEN
594 g_token := replace(g_token, ' -', ' ');
595 END IF;
596 g_token := g_token || ':';
597 --hr_utility.trace('after all others g_token is : ' || g_token);
598 END IF;
599 /**************************************************************
600 * Call to the Package Validate_Planning_Resource
601 * which will Validate the planning resource and the
602 * resource elements. If this package returns an error, then
603 * we need to throw an error and return, else proceed.
604 **********************************************************/
605 pa_planning_resource_utils.Validate_Planning_Resource
606 (p_task_name => null,
607 p_task_number => null,
608 p_planning_resource_alias => null,
609 p_resource_list_member_id => null,
610 p_resource_list_id => p_resource_list_id,
611 p_res_format_id => p_res_format_id,
612 p_resource_class_code => l_res_class_code,
613 p_res_type_code => l_res_type,
614 p_resource_code => l_resource_code,
615 p_resource_name => l_resource_name,
616 p_project_role_id => p_project_role_id,
617 p_project_role_name => p_project_role_name,
618 -- Team Role changes
619 p_team_role => p_team_role,
620 p_organization_id => p_organization_id,
621 p_organization_name => p_organization_name,
622 p_fc_res_type_code => p_fc_res_type_code,
623 p_fin_category_name => p_fin_category_name,
624 p_supplier_id => p_vendor_id,
625 p_supplier_name => p_vendor_name,
626 p_incur_by_resource_code => p_incur_by_res_code,
627 p_incur_by_resource_type => p_incur_by_res_type,
628 x_resource_list_member_id => l_resource_list_member_id,
629 x_person_id => l_person_id,
630 x_bom_resource_id => l_bom_resource_id,
631 x_job_id => l_job_id,
632 x_person_type_code => l_person_type_code,
633 x_non_labor_resource => l_non_labor_resource,
634 x_inventory_item_id => l_inventory_item_id,
635 x_item_category_id => l_item_category_id,
636 x_project_role_id => l_role_id,
637 -- Team Role changes
638 x_team_role => l_team_role,
639 x_organization_id => l_organization_id,
640 x_expenditure_type => l_expenditure_type,
641 x_expenditure_category => l_expenditure_category,
642 x_event_type => l_event_type,
643 x_revenue_category_code => l_revenue_category,
644 x_supplier_id => l_vendor_id,
645 x_resource_class_id => l_resource_class_id,
646 x_resource_class_flag => l_res_class_flag,
647 x_incur_by_role_id => l_incur_by_role_id,
648 x_incur_by_res_class_code => l_incur_by_res_class_code,
649 x_incur_by_res_flag => l_incur_by_res_flag,
650 x_return_status => x_return_status,
651 x_msg_data => x_error_msg_data,
652 x_msg_count => x_msg_count);
653 -- dbms_output.put_line('- After Validate_plan_res l_vendor_id IS : '|| l_vendor_id);
654
655
656 /********************************************************
657 * If the Validate package errors out then throw an error and
658 * Return.
659 ********************************************************/
660 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
661 RETURN;
662 END IF;
663
664 /******************************************************
665 * If the l_res_type returned = 'NAMED_PERSON' then
666 * first we need to get the value of person_name into
667 * l_person_name based on l_person_id.
668 * This is obtained from the per_people_x view
669 * Then Insert into pa_resources
670 * followed by insert into pa_resources_txn_attributes
671 ***************************************************/
672
673 IF l_res_type = 'NAMED_PERSON' THEN
674 BEGIN
675 SELECT full_name
676 INTO l_person_name
677 FROM per_people_x
678 WHERE person_id = l_person_id;
679 EXCEPTION
680 WHEN OTHERS THEN
681 l_person_name := null;
682 END;
683 /***********************************************
684 * First check if the resource already exists.
685 * *********************************************/
686 PA_GET_RESOURCE.Get_Resource
687 (p_resource_name => l_person_name,
688 p_resource_type_Code => 'EMPLOYEE',
689 p_person_id => l_person_id,
690 p_job_id => NULL,
691 p_proj_organization_id => NULL,
692 p_vendor_id => NULL,
693 p_expenditure_type => NULL,
694 p_event_type => NULL,
695 p_expenditure_category => NULL,
696 p_revenue_category_code => NULL,
697 p_non_labor_resource => NULL,
698 p_system_linkage => NULL,
699 p_project_role_id => NULL,
700 p_resource_id => l_resource_id,
701 p_err_code => l_err_code,
702 p_err_stage => l_err_stage,
703 p_err_stack => l_err_stack );
704
705 IF l_err_code <> 0 THEN
706 x_return_status := FND_API.G_RET_STS_ERROR;
707 x_msg_count := x_msg_count + 1;
708 x_error_msg_data := l_err_stage;
709 pa_utils.add_message('PA', l_err_stage);
710 RETURN;
711 END IF;
712
713 IF l_resource_id IS NULL THEN
714
715 /***********************************************
716 * Insert into PA_RESOURCES and PA_RESOURCE_TXN_ATTRIBUTES
717 * Table. This is done by a call to
718 * pa_create_resource.Create_Resource procedure.
719 ************************************************/
720 pa_create_resource.Create_Resource
721 (p_resource_name => l_person_name,
722 p_resource_type_Code => 'EMPLOYEE',
723 p_description => l_person_name,
724 p_unit_of_measure => NULL,
725 p_rollup_quantity_flag => NULL,
726 p_track_as_labor_flag => NULL,
727 p_start_date => SYSDATE,
728 p_end_date => NULL,
729 p_person_id => l_person_id,
730 p_job_id => NULL,
731 p_proj_organization_id => NULL,
732 p_vendor_id => NULL,
733 p_expenditure_type => NULL,
734 p_event_type => NULL,
735 p_expenditure_category => NULL,
736 p_revenue_category_code => NULL,
737 p_non_labor_resource => NULL,
738 p_system_linkage => NULL,
739 p_project_role_id => NULL,
740 p_resource_id => l_resource_id,
741 p_err_code => l_err_code,
742 p_err_stage => l_err_stage,
743 p_err_stack => l_err_stack);
744
745 IF l_err_code <> 0 THEN
746 x_return_status := FND_API.G_RET_STS_ERROR;
747 x_msg_count := x_msg_count + 1;
748 x_error_msg_data := l_err_stage;
749 pa_utils.add_message('PA', l_err_stage);
750 RETURN;
751 END IF;
752 END IF;
753 END IF;
754
755 /***********************************************************
756 * Do a Check to determine the uniqueness of the resource
757 * in the Resource list. Only if it is Unique we need
758 * to to the Insert Into PA_RESOURCE_LIST_MEMBERS.
759 * If it is not Unique then we should display an error
760 * saying 'Planning resource already exists in this
761 * Planning resource list'
762 * If its = 'Y' dont insert. If it is = 'N' then insert.
763 ********************************************************/
764 /*******************************************************
765 * Bug : 3486256
766 * Description : This fix has been done to fix the Duplicates issue.
767 * Earlier the NVL for incurred_by_res_flag
768 * used to check for 'B' but while inserting if
769 * the value was Null we were inserting 'N'
770 * Therefore it used to never find the dup record.
771 * We have now added NVL 'N' clause to help solve
772 * the issue.
773 **********************************************************/
774 BEGIN
775 Select 'Y'
776 Into l_unique_res_list
777 From pa_resource_list_members
778 Where resource_list_id = p_resource_list_id
779 And res_format_id = p_res_format_id
780 --Added the below 2 lines to check for the uniqueness
781 -- on a list/proj combination.
782 -- Removed NVL for performance tuning
783 And object_type = nvl(l_object_type,'DUMMY')
784 And object_id = nvl(l_object_id,-99)
785 -- Added resource class ID for performance
786 and resource_class_Id = l_res_class_id
787 And nvl(person_id, -99) = nvl(l_person_id, -99)
788 And nvl(organization_id, -99) = nvl(l_organization_id, -99)
789 And nvl(job_id, -99) = nvl(l_job_id, -99)
790 And nvl(vendor_id, -99) = nvl(l_vendor_id, -99)
791 -- Team Role Changes.
792 --And nvl(PROJECT_ROLE_ID, -99) = nvl(l_role_id, -99)
793 And nvl(inventory_item_id, -99) = nvl(l_inventory_item_id, -99)
794 And nvl(item_category_id, -99) = nvl(l_item_category_id, -99)
795 And nvl(bom_resource_id, -99) = nvl(l_bom_resource_id, -99)
796 And nvl(person_type_code, 'DUMMY') = nvl(l_person_type_code, 'DUMMY')
797 -- Team Role changes
798 And nvl(team_role, 'DUMMY') = nvl(l_team_role, 'DUMMY')
799 And nvl(incurred_by_res_flag, 'N') = nvl(l_incur_by_res_flag, 'N')
800 And nvl(incur_by_res_class_code, 'DUMMY') =
801 nvl(l_incur_by_res_class_code,'DUMMY')
802 And nvl(incur_by_role_id, -99) = nvl(l_incur_by_role_id, -99)
803 And nvl(expenditure_type,'DUMMY') = nvl(l_expenditure_type, 'DUMMY')
804 And nvl(event_type, 'DUMMY') = nvl(l_event_type, 'DUMMY')
805 And nvl(non_labor_resource, 'DUMMY') =
806 nvl(l_non_labor_resource, 'DUMMY')
807 And nvl(expenditure_category, 'DUMMY')
808 = nvl(l_expenditure_category,'DUMMY')
809 And nvl(revenue_category, 'DUMMY') = nvl(l_revenue_category, 'DUMMY');
810 EXCEPTION
811 WHEN NO_DATA_FOUND THEN
812 l_unique_res_list := 'N';
813 WHEN OTHERS THEN
814 l_unique_res_list := 'Y';
815 END;
816
817 IF l_unique_res_list = 'Y' THEN
818 x_msg_count := x_msg_count + 1;
819 x_return_status := FND_API.G_RET_STS_ERROR;
820 x_error_msg_data := 'PA_NOT_UNIQUE_RES_LIST_MEMBER';
821 -- FND_MESSAGE.SET_TOKEN('PLAN_RES', g_token);
822 PA_UTILS.Add_Message ('PA', x_error_msg_data, 'PLAN_RES', g_token);
823 -- PA_UTILS.Add_Message ('PA', x_error_msg_data);
824 Return;
825 END IF;
826
827 /***************************************************
828 * Derive the value which will be passed to
829 * WP_ELIGIBLE_FLAG column, while calling the
830 * Insert_row procedure.
831 * This value is got by call to Validate_Fin_Cat_For_WP
832 * Function, which takes in the p_fc_res_type_code
833 * and returns a 'Y' or 'N'.
834 * ************************************************/
835 l_wp_eligible_flag :=
836 PA_TASK_ASSIGNMENT_UTILS.Validate_Fin_Cat_For_WP(p_fc_res_type_code);
837
838 l_uom := Default_uom(
839 p_resource_class_code => l_res_class_code,
840 p_inventory_item_id => l_inventory_item_id,
841 p_organization_id => l_organization_id,
842 p_expenditure_type => l_expenditure_type);
843
844 /*************************************************
845 * Insert Into Pa_resource_list_members
846 ************************************************/
847 IF l_res_list_member_id IS NULL THEN
848 SELECT pa_resource_list_members_s.NEXTVAL
849 INTO l_res_list_member_id
850 FROM dual;
851 END IF;
852
853 If l_inventory_item_id is Not Null Then
854
855 l_dummy_variable:= Pa_Uom.Get_Uom(P_user_id => Fnd_Global.User_Id,
856 P_uom_code => l_uom);
857
858 End If;
859
860 /********************************************
861 * Call to Pa_Planning_Resource_pkg.insert_row
862 * Procedure, which will insert into the
863 * pa_resource_list_members table.
864 ********************************************/
865
866 pa_res_list_members_pkg.insert_row
867 (p_resource_list_member_id => l_res_list_member_id,
868 p_resource_list_id => p_resource_list_id,
869 p_resource_id => l_resource_id,
870 p_resource_alias => p_resource_alias,
871 p_person_id => l_person_id,
872 p_job_id => l_job_id ,
873 p_organization_id => l_organization_id ,
874 p_vendor_id => l_vendor_id ,
875 p_expenditure_type => l_expenditure_type ,
876 p_event_type => l_event_type ,
877 p_non_labor_resource => l_non_labor_resource ,
878 p_expenditure_category => l_expenditure_category ,
879 p_revenue_category => l_revenue_category ,
880 p_role_id => l_role_id ,
881 p_resource_class_id => l_res_class_id ,
882 p_res_class_code => l_res_class_code ,
883 p_res_format_id => p_res_format_id ,
884 p_spread_curve_id => p_spread_curve_id ,
885 p_etc_method_code => p_etc_method_code ,
886 p_mfc_cost_type_id => p_mfc_cost_type_id ,
887 p_res_class_flag => l_res_class_flag ,
888 p_fc_res_type_code => p_fc_res_type_code ,
889 p_inventory_item_id => l_inventory_item_id ,
890 p_item_category_id => l_item_category_id ,
891 p_attribute_category => p_attribute_category ,
892 p_attribute1 => p_attribute1 ,
893 p_attribute2 => p_attribute2 ,
894 p_attribute3 => p_attribute3 ,
895 p_attribute4 => p_attribute4 ,
896 p_attribute5 => p_attribute5 ,
897 p_attribute6 => p_attribute6 ,
898 p_attribute7 => p_attribute7 ,
899 p_attribute8 => p_attribute8 ,
900 p_attribute9 => p_attribute9 ,
901 p_attribute10 => p_attribute10 ,
902 p_attribute11 => p_attribute11 ,
903 p_attribute12 => p_attribute12 ,
904 p_attribute13 => p_attribute13 ,
905 p_attribute14 => p_attribute14 ,
906 p_attribute15 => p_attribute15 ,
907 p_attribute16 => p_attribute16 ,
908 p_attribute17 => p_attribute17 ,
909 p_attribute18 => p_attribute18 ,
910 p_attribute19 => p_attribute19 ,
911 p_attribute20 => p_attribute20 ,
912 p_attribute21 => p_attribute21 ,
913 p_attribute22 => p_attribute22 ,
914 p_attribute23 => p_attribute23 ,
915 p_attribute24 => p_attribute24 ,
916 p_attribute25 => p_attribute25 ,
917 p_attribute26 => p_attribute26 ,
918 p_attribute27 => p_attribute27 ,
919 p_attribute28 => p_attribute28 ,
920 p_attribute29 => p_attribute29 ,
921 p_attribute30 => p_attribute30 ,
922 p_person_type_code => l_person_type_code,
923 p_bom_resource_id => l_bom_resource_id,
924 p_team_role => l_team_role,
925 p_incur_by_res_class_code => l_incur_by_res_class_code,
926 p_incur_by_role_id => l_incur_by_role_id,
927 p_incur_by_res_flag => l_incur_by_res_flag,
928 p_object_type => l_object_type,
929 p_object_id => l_object_id,
930 p_wp_eligible_flag => l_wp_eligible_flag,
931 p_unit_of_measure => l_uom,
932 x_msg_count => x_msg_count,
933 x_return_status => x_return_status ,
934 x_error_msg_data => x_error_msg_data );
935
936 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
937 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
938 x_msg_count := x_msg_count + 1;
939 RETURN;
940 END IF;
941
942 /**************************************************
943 * Get the default values for the spread_curve_id,
944 * etc_method_code and mfc_cost_type_id, if no values
945 * are passed.
946 *************************************************/
947 /*************************************************
948 * The below select would get the default values for
949 * spread curve id, etc method code and mfc cost type id
950 **************************************************/
951 BEGIN
952 SELECT spread_curve_id,
953 etc_method_code,
954 mfc_cost_type_id
955 INTO l_spread_curve_id,
956 l_etc_method_code,
957 l_mfc_cost_type_id
958 FROM Pa_Plan_Res_Defaults
959 WHERE resource_class_id = l_res_class_id
960 AND object_type = 'CLASS';
961 EXCEPTION
962 WHEN OTHERS THEN
963 l_spread_curve_id := NULL;
964 l_etc_method_code := NULL;
965 l_mfc_cost_type_id := NULL;
966 END;
967 /******************************************************
968 * If the values for spread curve id, etc method code
969 * and mfc cost type id are Not null then retain the same values
970 * else use the derived values(from above).
971 *****************************************************/
972 UPDATE pa_resource_list_members
973 SET spread_curve_id = DECODE(spread_curve_id,NULL,
974 l_spread_curve_id, spread_curve_id),
975 etc_method_code = DECODE(etc_method_code,NULL,
976 l_etc_method_code, etc_method_code)
977 WHERE resource_list_member_id = l_res_list_member_id;
978
979 BEGIN
980 SELECT res.res_type_code
981 INTO l_res_type_code
982 from pa_res_formats_b fmt,pa_res_types_b res
983 where fmt.res_type_id = res.res_type_id
984 and fmt.res_format_id = p_res_format_id;
985 EXCEPTION
986 WHEN NO_DATA_FOUND THEN
987 l_res_type_code := NULL;
988 END;
989
990 IF l_res_type_code IN ('BOM_EQUIPMENT','BOM_LABOR','INVENTORY_ITEM')
991 THEN
992 UPDATE pa_resource_list_members
993 SET mfc_cost_type_id = DECODE(mfc_cost_type_id,NULL,
994 l_mfc_cost_type_id, mfc_cost_type_id)
995 WHERE resource_list_member_id = l_res_list_member_id;
996 ELSE
997 UPDATE pa_resource_list_members
998 SET mfc_cost_type_id = NULL
999 WHERE resource_list_member_id = l_res_list_member_id;
1000 END IF;
1001
1002 /**************************************************
1003 * If the p_resource_alias is Null then
1004 * we need to derive it by call to procedure
1005 * PA_PLANNING_RESOURCE_DEFAULTS. Get_Plan_Res_Combination
1006 * and then we need to update the table pa_resource_list_members
1007 * with the derived value.
1008 ***************************************************/
1009 IF p_resource_alias IS NULL
1010 THEN
1011 /***************************************************
1012 * Get_Plan_Res_Combination
1013 *************************************************/
1014 PA_PLANNING_RESOURCE_UTILS. Get_Plan_Res_Combination(
1015 P_Resource_List_Member_Id => l_res_list_member_id,
1016 X_resource_alias => l_resource_alias,
1017 X_Plan_Res_Combination => l_res_combo,
1018 X_Return_Status => l_return_status,
1019 X_Msg_Count => l_msg_count,
1020 X_Msg_Data => l_error_msg_data);
1021
1022 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1023 -- PA_UTILS.Add_Message ('PA', l_error_msg_data);
1024 Return;
1025 END IF;
1026 /*****************************************************
1027 * Bug - 3509278
1028 * Desc - Generating Unique Alias.
1029 ****************************************************/
1030 l_length := length(l_resource_alias);
1031 IF l_length > 77 THEN
1032 l_length := 77;
1033 END IF;
1034
1035 IF pa_planning_resource_pvt.Check_pl_alias_unique(p_resource_list_id,
1036 l_resource_alias,l_res_list_member_id,l_object_type,l_object_id) = 'N'
1037 THEN
1038 l_num := 1;
1039 l_done := 'N';
1040 LOOP
1041 EXIT when l_done = 'Y';
1042 l_resource_alias :=
1043 substr(l_resource_alias, 1, l_length)|| l_num;
1044 IF pa_planning_resource_pvt.Check_pl_alias_unique(p_resource_list_id,
1045 l_resource_alias,l_res_list_member_id,l_object_type,l_object_id)= 'Y'
1046 THEN
1047 l_done := 'Y';
1048 END IF;
1049 l_num := l_num + 1;
1050 END LOOP;
1051 -- x_return_status := FND_API.G_RET_STS_ERROR;
1052 -- x_msg_count := x_msg_count + 1;
1053 -- x_error_msg_data := 'PA_RES_ALIAS_NOT_UNIQUE';
1054 -- PA_UTILS.Add_Message ('PA', x_error_msg_data);
1055 -- Return;
1056 END IF;
1057
1058 BEGIN
1059 UPDATE pa_resource_list_members
1060 SET alias = l_resource_alias
1061 WHERE resource_list_member_id = l_res_list_member_id;
1062 EXCEPTION
1063 WHEN OTHERS THEN
1064 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1065 x_msg_count := x_msg_count + 1;
1066 Return;
1067 END;
1068
1069 END IF;
1070 --Increment the x_record_version_number after Insert.
1071 x_resource_list_member_id := l_res_list_member_id;
1072 x_record_version_number := 1;
1073 /**************************************************/
1074 END Create_Planning_Resource;
1075 /*************************************/
1076
1077 /***************************************************
1078 * Procedure : Update_Planning_Resource
1079 * Description : The purpose of this procedure is to
1080 * Validate and update attributes on an existing
1081 * planning resource for a resource list.
1082 * It first checks for the Uniqueness of the
1083 * resource list. If it is Unique then it updates
1084 * the table PA_RESOURCE_LIST_MEMBERS
1085 * with the values passed.
1086 ************************************/
1087 PROCEDURE Update_Planning_Resource
1088 (p_resource_list_id IN NUMBER,
1089 p_resource_list_member_id IN NUMBER,
1090 p_enabled_flag IN VARCHAR2,
1091 p_resource_alias IN VARCHAR2 ,
1092 p_spread_curve_id IN NUMBER DEFAULT NULL,
1093 p_etc_method_code IN VARCHAR2 DEFAULT NULL,
1094 p_mfc_cost_type_id IN NUMBER DEFAULT NULL,
1095 p_attribute_category IN VARCHAR2 DEFAULT NULL,
1096 p_attribute1 IN VARCHAR2 DEFAULT NULL,
1097 p_attribute2 IN VARCHAR2 DEFAULT NULL,
1098 p_attribute3 IN VARCHAR2 DEFAULT NULL,
1099 p_attribute4 IN VARCHAR2 DEFAULT NULL,
1100 p_attribute5 IN VARCHAR2 DEFAULT NULL,
1101 p_attribute6 IN VARCHAR2 DEFAULT NULL,
1102 p_attribute7 IN VARCHAR2 DEFAULT NULL,
1103 p_attribute8 IN VARCHAR2 DEFAULT NULL,
1104 p_attribute9 IN VARCHAR2 DEFAULT NULL,
1105 p_attribute10 IN VARCHAR2 DEFAULT NULL,
1106 p_attribute11 IN VARCHAR2 DEFAULT NULL,
1107 p_attribute12 IN VARCHAR2 DEFAULT NULL,
1108 p_attribute13 IN VARCHAR2 DEFAULT NULL,
1109 p_attribute14 IN VARCHAR2 DEFAULT NULL,
1110 p_attribute15 IN VARCHAR2 DEFAULT NULL,
1111 p_attribute16 IN VARCHAR2 DEFAULT NULL,
1112 p_attribute17 IN VARCHAR2 DEFAULT NULL,
1113 p_attribute18 IN VARCHAR2 DEFAULT NULL,
1114 p_attribute19 IN VARCHAR2 DEFAULT NULL,
1115 p_attribute20 IN VARCHAR2 DEFAULT NULL,
1116 p_attribute21 IN VARCHAR2 DEFAULT NULL,
1117 p_attribute22 IN VARCHAR2 DEFAULT NULL,
1118 p_attribute23 IN VARCHAR2 DEFAULT NULL,
1119 p_attribute24 IN VARCHAR2 DEFAULT NULL,
1120 p_attribute25 IN VARCHAR2 DEFAULT NULL,
1121 p_attribute26 IN VARCHAR2 DEFAULT NULL,
1122 p_attribute27 IN VARCHAR2 DEFAULT NULL,
1123 p_attribute28 IN VARCHAR2 DEFAULT NULL,
1124 p_attribute29 IN VARCHAR2 DEFAULT NULL,
1125 p_attribute30 IN VARCHAR2 DEFAULT NULL,
1126 p_record_version_number IN NUMBER,
1127 x_record_version_number OUT NOCOPY NUMBER ,
1128 x_return_status OUT NOCOPY VARCHAR2 ,
1129 x_msg_count OUT NOCOPY NUMBER ,
1130 x_error_msg_data OUT NOCOPY VARCHAR2 )
1131 IS
1132
1133 l_resource_alias VARCHAR2(80);
1134 l_res_combo VARCHAR2(1000);
1135 l_object_id NUMBER;
1136 l_object_type VARCHAR2(30);
1137 l_num NUMBER;
1138 l_done VARCHAR2(1);
1139 l_length NUMBER;
1140 l_allowed VARCHAR2(1) := 'Y';
1141
1142 BEGIN
1143 IF g_amg_flow = 'N' OR g_amg_flow IS NULL THEN
1144 SELECT meaning || ' '
1145 INTO g_token
1146 FROM pa_lookups
1147 WHERE lookup_type = 'PA_PLANNING_RESOURCE'
1148 AND lookup_code = 'PLANNING_RESOURCE';
1149
1150 g_token := g_token || nvl(p_resource_alias,
1151 PA_PLANNING_RESOURCE_UTILS.Get_Plan_Res_Combination(
1152 P_Resource_List_Member_Id => P_Resource_List_Member_Id));
1153 END IF;
1154
1155 FND_MSG_PUB.initialize;
1156 x_msg_count := 0;
1157 x_return_status := FND_API.G_RET_STS_SUCCESS;
1158 BEGIN
1159 SELECT object_type,object_id
1160 INTO l_object_type,l_object_id
1161 FROM pa_resource_list_members
1162 WHERE resource_list_member_id = p_resource_list_member_id;
1163 EXCEPTION
1164 WHEN OTHERS THEN
1165 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1166 x_msg_count := x_msg_count + 1;
1167 RETURN;
1168 END;
1169
1170 /***************************************************
1171 * Check if Resource List member is Unique. Done by
1172 * Call to pa_planning_resource_pvt.Check_pl_alias_unique
1173 * If it returns a value that means it is not unique
1174 * Display an error and return.
1175 **************************************************/
1176
1177 -- Bug 3719859 - when a user has nulled out the alias, we need to
1178 -- rederive it - so treat G_MISS_CHAR as NULL.
1179 --
1180 ----hr_utility.trace_on(NULL, 'RMALIAS');
1181 ----hr_utility.trace('start - before alias check');
1182 ----hr_utility.trace('p_resource_alias is : ' || p_resource_alias);
1183 --IF (p_resource_alias IS NULL OR p_resource_alias = FND_API.G_MISS_CHAR) THEN
1184
1185 IF p_resource_alias IS NULL THEN
1186 ----hr_utility.trace('p_resource_alias is NULL - derive');
1187 -- Derive the default alias and use that.
1188 /**************************************************
1189 * Derive the default Alias and Use that.
1190 ****************************************************/
1191 Pa_Planning_Resource_Utils.Get_Plan_Res_Combination(
1192 P_Resource_List_Member_Id => p_resource_list_member_id,
1193 X_resource_alias => l_resource_alias,
1194 X_Plan_Res_Combination => l_res_combo,
1195 X_Return_Status => x_return_status,
1196 X_Msg_Count => x_msg_count,
1197 X_Msg_Data => x_error_msg_data);
1198
1199 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1200 -- PA_UTILS.Add_Message ('PA', x_error_msg_data);
1201 Return;
1202 END IF;
1203
1204 l_length := length(l_resource_alias);
1205 IF l_length > 77 THEN
1206 l_length := 77;
1207 END IF;
1208 IF pa_planning_resource_pvt.Check_pl_alias_unique(p_resource_list_id,
1209 l_resource_alias,p_resource_list_member_id,
1210 l_object_type,l_object_id) = 'N' THEN
1211 l_num := 1;
1212 l_done := 'N';
1213 LOOP
1214 EXIT when l_done = 'Y';
1215 l_resource_alias :=
1216 substr(l_resource_alias, 1, l_length)|| l_num;
1217 IF pa_planning_resource_pvt.Check_pl_alias_unique(
1218 p_resource_list_id, l_resource_alias,
1219 p_resource_list_member_id,l_object_type,l_object_id)= 'Y' THEN
1220 l_done := 'Y';
1221 END IF;
1222 l_num := l_num + 1;
1223 END LOOP;
1224 END IF;
1225
1226 ----hr_utility.trace('after derivation l_resource_alias is : ' || l_resource_alias);
1227
1228 ELSE
1229 l_resource_alias := p_resource_alias;
1230 ----hr_utility.trace('l_resource_alias is : ' || l_resource_alias);
1231 IF pa_planning_resource_pvt.Check_pl_alias_unique(
1232 p_resource_list_id => p_resource_list_id,
1233 p_resource_alias => l_resource_alias,
1234 p_resource_list_member_id => p_resource_list_member_id,
1235 p_object_type => l_object_type,
1236 p_object_id => l_object_id) = 'N'
1237 THEN
1238 x_return_status := FND_API.G_RET_STS_ERROR;
1239 x_error_msg_data := 'PA_RES_ALIAS_NOT_UNIQUE';
1240 PA_UTILS.Add_Message ('PA', x_error_msg_data, 'PLAN_RES', g_token);
1241 Return;
1242 END IF;
1243 END IF;
1244
1245 -- Check to see if enabling this resource is allowed, if enabled flag = 'Y'
1246 -- Fixes bug 3710822
1247 -- --hr_utility.trace_on(null, 'RMENABLE');
1248 -- --hr_utility.trace('hdjhjdhdkahdkahdk - start');
1249 -- --hr_utility.trace('p_enabled_flag is : ' || p_enabled_flag);
1250 IF p_enabled_flag = 'Y' THEN
1251 l_allowed := pa_planning_resource_utils.check_enable_allowed(
1252 p_resource_list_member_id => p_resource_list_member_id);
1253
1254 -- --hr_utility.trace('l_allowed is : ' || l_allowed);
1255 IF l_allowed = 'N' THEN
1256 x_return_status := FND_API.G_RET_STS_ERROR;
1257 x_error_msg_data := 'PA_ENABLE_NOT_ALLOWED';
1258 PA_UTILS.Add_Message ('PA', x_error_msg_data, 'PLAN_RES', g_token);
1259 -- --hr_utility.trace('x_error_msg_data is : ' || x_error_msg_data);
1260 Return;
1261 END IF;
1262 END IF;
1263
1264 /************************************
1265 * If it is Unique we go ahead with the Update to
1266 * pa_resource_list_members table.
1267 * Update using the values passed.
1268 *****************************************/
1269
1270 pa_res_list_members_pkg.update_row
1271 (p_alias => l_resource_alias,
1272 p_enabled_flag => p_enabled_flag,
1273 p_resource_list_member_id => p_resource_list_member_id,
1274 p_spread_curve_id => p_spread_curve_id,
1275 p_etc_method_code => p_etc_method_code,
1276 p_mfc_cost_type_id => p_mfc_cost_type_id,
1277 p_attribute_category => p_attribute_category,
1278 p_attribute1 => p_attribute1,
1279 p_attribute2 => p_attribute2,
1280 p_attribute3 => p_attribute3,
1281 p_attribute4 => p_attribute4,
1282 p_attribute5 => p_attribute5,
1283 p_attribute6 => p_attribute6,
1284 p_attribute7 => p_attribute7,
1285 p_attribute8 => p_attribute8,
1286 p_attribute9 => p_attribute9,
1287 p_attribute10 => p_attribute10,
1288 p_attribute11 => p_attribute11,
1289 p_attribute12 => p_attribute12,
1290 p_attribute13 => p_attribute13,
1291 p_attribute14 => p_attribute14,
1292 p_attribute15 => p_attribute15,
1293 p_attribute16 => p_attribute16,
1294 p_attribute17 => p_attribute17,
1295 p_attribute18 => p_attribute18,
1296 p_attribute19 => p_attribute19,
1297 p_attribute20 => p_attribute20,
1298 p_attribute21 => p_attribute21,
1299 p_attribute22 => p_attribute22,
1300 p_attribute23 => p_attribute23,
1301 p_attribute24 => p_attribute24,
1302 p_attribute25 => p_attribute25,
1303 p_attribute26 => p_attribute26,
1304 p_attribute27 => p_attribute27,
1305 p_attribute28 => p_attribute28,
1306 p_attribute29 => p_attribute29,
1307 p_attribute30 => p_attribute30,
1308 p_record_version_number => p_record_version_number,
1309 x_return_status => x_return_status,
1310 x_error_msg_data => x_error_msg_data,
1311 x_msg_count => x_msg_count);
1312
1313
1314 x_record_version_number := p_record_version_number;
1315
1316 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1317 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1318 x_msg_count := x_msg_count + 1;
1319 RETURN;
1320 END IF;
1321
1322 END Update_Planning_Resource;
1323 /************************************************/
1324
1325 /*************************************************
1326 * Procedure : Delete_Planning_Resource
1327 * Description : The purpose of this procedure is to
1328 * delete a planning resource if it is not
1329 * being used, else disable it.
1330 ***************************************************/
1331 PROCEDURE Delete_Planning_Resource(
1332 p_resource_list_member_id IN NUMBER,
1333 x_return_status OUT NOCOPY VARCHAR2,
1334 x_msg_count OUT NOCOPY NUMBER,
1335 x_error_msg_data OUT NOCOPY VARCHAR2)
1336 IS
1337 l_exist_res_list VARCHAR2(30) := 'N';
1338 l_resource_list_id NUMBER;
1339 l_migration_code VARCHAR2(30) := NULL;
1340 l_msg_count NUMBER := 0;
1341 BEGIN
1342 IF g_amg_flow = 'N' OR g_amg_flow IS NULL THEN
1343 SELECT meaning || ' '
1344 INTO g_token
1345 FROM pa_lookups
1346 WHERE lookup_type = 'PA_PLANNING_RESOURCE'
1347 AND lookup_code = 'PLANNING_RESOURCE';
1348
1349 g_token := g_token || PA_PLANNING_RESOURCE_UTILS.Get_Plan_Res_Combination(
1350 P_Resource_List_Member_Id => P_Resource_List_Member_Id);
1351 END IF;
1352
1353 x_return_status := FND_API.G_RET_STS_SUCCESS;
1354 x_msg_count := 0;
1355
1356 BEGIN
1357 SELECT resource_list_id, migration_code
1358 INTO l_resource_list_id, l_migration_code
1359 FROM pa_resource_list_members
1360 WHERE resource_list_member_id = p_resource_list_member_id;
1361
1362 EXCEPTION WHEN OTHERS THEN
1363 RETURN;
1364 END;
1365
1366 /********************************************
1367 * To Check if resource_list member is currently being
1368 * used in a planning transaction.
1369 * We are checking from pa_resource_assignments table.
1370 ************************************************/
1371 BEGIN
1372 /*********************************************************
1373 * Bug : 3485415
1374 * Description : Added the extra UNION condition to check from
1375 * the Pa_project_assignments table as well before
1376 * deleting. If the resource list member
1377 * found in either pa_resource_assignments
1378 * or pa_project_assignments we cannot delete
1379 * it. We will only set the enabled_flag = 'Y'.
1380 **********************************************************/
1381 SELECT 'Y'
1382 INTO l_exist_res_list
1383 FROM DUAL
1384 WHERE EXISTS
1385 (SELECT 'Y' from pa_resource_assignments
1386 WHERE resource_list_member_id = p_resource_list_member_id
1387 UNION
1388 SELECT 'Y' from pa_project_assignments
1389 WHERE resource_list_member_id = p_resource_list_member_id );
1390 EXCEPTION
1391 WHEN NO_DATA_FOUND THEN
1392 l_exist_res_list := 'N';
1393 WHEN OTHERS THEN
1394 FND_MSG_PUB.add_exc_msg( p_pkg_name =>
1395 'pa_planning_resource_pvt.delete_planning_resource'
1396 ,p_procedure_name => PA_DEBUG.G_Err_Stack);
1397 l_msg_count := l_msg_count + 1;
1398 x_msg_count := l_msg_count;
1399 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1400 END;
1401
1402 IF l_exist_res_list = 'N' THEN
1403
1404 IF l_migration_code <> 'N' THEN
1405
1406 PA_GET_RESOURCE.delete_resource_list_member_ok(
1407 l_resource_list_id => l_resource_list_id,
1408 l_resource_list_member_id => p_resource_list_member_id,
1409 x_err_code => x_msg_count,
1410 x_err_stage => x_error_msg_data);
1411
1412 IF x_msg_count <> 0 THEN
1413 l_exist_res_list := 'Y';
1414 END IF;
1415 END IF;
1416
1417 END IF;
1418
1419 pa_res_list_members_pkg.Delete_row
1420 (p_resource_list_member_id => p_resource_list_member_id,
1421 p_exist_res_list => l_exist_res_list,
1422 x_msg_count => x_msg_count,
1423 x_return_status => x_return_status);
1424
1425 END Delete_Planning_Resource;
1426 /***************************/
1427 /*************************************************************
1428 * Procedure : Copy_Planning_Resources
1429 * Description : This API is used to copy the resource list
1430 * members passed(as a table) from the source
1431 * resource list ID to the destination resource
1432 * list ID.
1433 * It is called from the Task Assignments code when
1434 * task assignments are copied from an external project
1435 * to the current project - the transactions are also
1436 * copied and so the planning resources also have to be
1437 * copied from the source project's resource list to
1438 * the destination project's resource list - only project
1439 * specific resources will be copied, and the newly
1440 * created resources will have the object_id of the destination
1441 * project. Only resources whose formats are on the
1442 * destination list are copied.
1443 * Steps :-
1444 * - It first gets the format for the resource
1445 * list member passed.
1446 * - It then checks if the same format is being
1447 * used by the destination resource list ID.
1448 * - IF it does use it then check if there
1449 * already exist a planning resource in the
1450 * destination resource list having the same
1451 * combination.
1452 * - If it does then pass it back.
1453 * - If it does not then create it and pass it back.
1454 * - If the res_format_id does not exist
1455 * then pass back a Null resource list member id.
1456 * - Do a final check to see that the out Tbl
1457 * size equals the IN Tbl size.
1458 *************************************************************/
1459
1460 -- Modified the procedure to operate in bulk mode for performance issues
1461 -- as reported in the bug 4102957.
1462
1463 PROCEDURE Copy_Planning_Resources(
1464 p_source_resource_list_id IN Number,
1465 p_destination_resource_list_id IN Number,
1466 p_src_res_list_member_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE,
1467 x_dest_res_list_member_id_tbl OUT NOCOPY SYSTEM.PA_NUM_TBL_TYPE,
1468 p_destination_project_id IN Number DEFAULT NULL)
1469 IS
1470
1471 /**********************************
1472 * Local Variable
1473 **********************************/
1474 l_control_flag Varchar2(1);
1475 l_object_id Number;
1476 l_object_type VARCHAR2(30);
1477 l_exception EXCEPTION;
1478 l_bulk_resource_list_member_id SYSTEM.PA_NUM_TBL_TYPE;
1479 l_bulk_enabled_flag SYSTEM.PA_VARCHAR2_1_TBL_TYPE;
1480 l_old_resource_list_member_id SYSTEM.PA_NUM_TBL_TYPE;
1481 l_new_resource_list_member_id SYSTEM.PA_NUM_TBL_TYPE;
1482 l_last_analyzed all_tables.last_analyzed%TYPE;
1483 l_pa_schema VARCHAR2(30);
1484 BEGIN
1485
1486 --hr_utility.trace_on(NULL, 'RMP1');
1487 --hr_utility.trace('START');
1488 --hr_utility.trace('p_source_resource_list_id IS : ' || p_source_resource_list_id);
1489 --hr_utility.trace('p_destination_resource_list_id IS : ' || p_destination_resource_list_id);
1490 --hr_utility.trace('p_destination_project_id IS : ' || p_destination_project_id);
1491 x_dest_res_list_member_id_tbl := SYSTEM.PA_NUM_TBL_TYPE();
1492
1493 /*******************************************
1494 * If no resource list member ID's passed then
1495 * Just Return without doing anything.
1496 *****************************************/
1497 IF p_src_res_list_member_id_tbl.count = 0 THEN
1498 Return;
1499 END IF;
1500
1501 --hr_utility.trace('count is ' || p_src_res_list_member_id_tbl.count);
1502 -- COUNT is greater than 0 - initialize out table to be same size
1503 x_dest_res_list_member_id_tbl.extend(p_src_res_list_member_id_tbl.count);
1504
1505 -- Bug 3642940
1506 BEGIN
1507 SELECT control_flag
1508 INTO l_control_flag
1509 FROM pa_resource_lists_all_bg
1510 WHERE resource_list_id = p_destination_resource_list_id;
1511 END;
1512
1513 /*********************************************
1514 * The below select would be used to determine the
1515 * uniqueness of the resource within the resource list.
1516 *********************************************/
1517 -- Bug 3642940
1518 IF l_control_flag = 'Y' THEN
1519 l_object_id := p_destination_resource_list_id;
1520 l_object_type := 'RESOURCE_LIST';
1521 ELSE
1522 l_object_id := p_destination_project_id;
1523 l_object_type := 'PROJECT';
1524 END IF;
1525
1526 -- Setting statistics for temp tables:
1527 -- bug 4887312
1528 -- ***** TEMP fix - proper fix will be done later
1529 -- Bug 4887312
1530 /*
1531 FND_STATS.SET_TABLE_STATS('PA',
1532 'PA_RES_MEMBERS_TEMP',
1533 100,
1534 10,
1535 100);
1536
1537 FND_STATS.SET_TABLE_STATS('PA',
1538 'PA_RES_MEMBER_ID_TEMP',
1539 100,
1540 10,
1541 100);
1542 */
1543 -- Proper Fix for 4887312 *** RAMURTHY 03/01/06 02:33 pm ***
1544 -- It solves the issue above wrt commit by the FND_STATS.SET_TABLE_STATS call
1545
1546 PA_TASK_ASSIGNMENT_UTILS.set_table_stats('PA','PA_RES_MEMBERS_TEMP',100,10,100);
1547 PA_TASK_ASSIGNMENT_UTILS.set_table_stats('PA','PA_RES_MEMBER_ID_TEMP',100,10,100);
1548
1549 -- End Bug fix 4887312
1550 /***********************************************
1551 * Deleting from the temp tables in the beginning as well
1552 * to be on the safe side.
1553 ***********************************************/
1554 DELETE FROM pa_res_members_temp;
1555
1556 DELETE FROM pa_res_member_id_temp;
1557 /*************************************************/
1558
1559 -- Looping through the source Resource list member ID table
1560 -- and inserting the values to
1561 -- the temp table pa_res_member_id_temp.
1562
1563 IF p_src_res_list_member_id_tbl.count > 0 THEN
1564
1565 FOR i IN p_src_res_list_member_id_tbl.first ..
1566 p_src_res_list_member_id_tbl.last
1567 LOOP
1568 --hr_utility.trace('p_src_res_list_member_id_tbl(i) IS : ' || p_src_res_list_member_id_tbl(i));
1569 INSERT INTO pa_res_member_id_temp
1570 (resource_list_member_id,
1571 order_id)
1572 VALUES(p_src_res_list_member_id_tbl(i),
1573 i);
1574 END LOOP;
1575
1576 END IF;
1577
1578 -- Inserting Null for ORG_ID. Later ORG_ID will be popluated
1579 -- with values for corresponding
1580 -- RLM's which has a match in the destination list.
1581
1582 --hr_utility.trace('before temp insert');
1583 INSERT INTO pa_res_members_temp
1584 (resource_list_member_id ,
1585 order_id ,
1586 person_id ,
1587 project_role_id ,
1588 organization_id ,
1589 job_id ,
1590 vendor_id ,
1591 inventory_item_id ,
1592 item_category_id ,
1593 bom_resource_id ,
1594 person_type_code ,
1595 -- named_role is holding team role
1596 named_role ,
1597 incurred_by_res_flag ,
1598 incur_by_res_class_code ,
1599 incur_by_role_id ,
1600 expenditure_type ,
1601 Event_type ,
1602 non_labor_resource ,
1603 expenditure_category ,
1604 revenue_category ,
1605 org_id ,
1606 resource_class_id ,
1607 -- Spread curve id is holding format id.
1608 spread_curve_id )
1609 SELECT /*+ ORDERED */
1610 a.resource_list_member_id ,
1611 b.order_id ,
1612 a.person_id ,
1613 a.project_role_id ,
1614 a.organization_id ,
1615 a.job_id ,
1616 a.vendor_id ,
1617 a.inventory_item_id ,
1618 a.item_category_id ,
1619 a.bom_resource_id ,
1620 a.person_type_code ,
1621 a.team_role ,
1622 a.incurred_by_res_flag ,
1623 a.incur_by_res_class_code ,
1624 a.incur_by_role_id ,
1625 a.expenditure_type ,
1626 a.event_type ,
1627 a.non_labor_resource ,
1628 a.expenditure_category ,
1629 a.revenue_category ,
1630 NULL ,
1631 a.resource_class_id ,
1632 a.res_format_id
1633 FROM pa_res_member_id_temp b,
1634 pa_resource_list_members a
1635 WHERE a.resource_list_member_id = b.resource_list_member_id;
1636
1637 -- Updating the ORG ID column to be -1 for those RLM's whose formats
1638 -- don't exist on the destination list:
1639
1640 UPDATE pa_res_members_temp rlmtmp
1641 SET org_id = -1
1642 WHERE NOT EXISTS (
1643 SELECT 'Y'
1644 FROM Pa_Plan_rl_formats
1645 WHERE res_format_id = rlmtmp.spread_curve_id
1646 AND resource_list_id = p_destination_resource_list_id
1647 AND rownum = 1);
1648
1649 -- Now, the temp tables are having all the details for every
1650 -- resource list member in the IN table. The ones with
1651 -- ORG_ID as NULL needs to be processed.
1652
1653 -- Used four PL/SQL tables :
1654 -- l_bulk_resource_list_member_id : Holds the RLM id of the
1655 -- destination list which matches with source RLM id's.
1656 -- l_bulk_enabled_flag : Holds the flag value of
1657 -- enabled_flag of destination RLM id's.
1658 -- l_old_resource_list_member_id : Holds the RLM ids of the
1659 -- source list which has a match with destination RLM id's.
1660 -- l_new_resource_list_member_id : Holds the RLM ids of the
1661 -- newly created members.
1662
1663 SELECT a.resource_list_member_id, -- matching rlm on dest
1664 a.enabled_flag, -- enabled flag of match
1665 b.resource_list_member_id -- matching rlm on source list
1666 BULK COLLECT INTO l_bulk_resource_list_member_id,
1667 l_bulk_enabled_flag,
1668 l_old_resource_list_member_id
1669 FROM pa_resource_list_members a,
1670 pa_res_members_temp b
1671 WHERE a.resource_list_id = p_destination_resource_list_id
1672 -- To process only those RLM which has corr formats as that of source RL.
1673 AND b.org_id IS NULL
1674 AND a.res_format_id = b.spread_curve_id
1675 AND a.object_type = l_object_type
1676 And a.object_id = l_object_id
1677 And a.resource_class_id = b.resource_class_id
1678 And nvl(a.person_id, -99) = nvl(b.person_id, -99)
1679 And nvl(a.organization_id, -99) =
1680 nvl(b.organization_id, -99)
1681 And nvl(a.job_id, -99) = nvl(b.job_id, -99)
1682 And nvl(a.vendor_id, -99) = nvl(b.vendor_id, -99)
1683 And nvl(a.inventory_item_id, -99) =
1684 nvl(b.inventory_item_id, -99)
1685 And nvl(a.item_category_id, -99) =
1686 nvl(b.item_category_id, -99)
1687 And nvl(a.bom_resource_id, -99) =
1688 nvl(b.bom_resource_id, -99)
1689 And nvl(a.person_type_code, 'DUMMY') =
1690 nvl(b.person_type_code, 'DUMMY')
1691 And nvl(a.team_role, 'DUMMY') =
1692 nvl(b.named_role, 'DUMMY')
1693 And nvl(a.incurred_by_res_flag, 'B') =
1694 nvl(b.incurred_by_res_flag, 'B')
1695 And nvl(a.incur_by_res_class_code, 'DUMMY') =
1696 nvl(b.incur_by_res_class_code,'DUMMY')
1697 And nvl(a.incur_by_role_id, -99) =
1698 nvl(b.incur_by_role_id, -99)
1699 And nvl(a.expenditure_type,'DUMMY') =
1700 nvl(b.expenditure_type, 'DUMMY')
1701 And nvl(a.event_type, 'DUMMY') = nvl(b.event_type, 'DUMMY')
1702 And nvl(a.non_labor_resource, 'DUMMY') =
1703 nvl(b.non_labor_resource, 'DUMMY')
1704 And nvl(a.expenditure_category, 'DUMMY') =
1705 nvl(b.expenditure_category,'DUMMY')
1706 And nvl(a.revenue_category, 'DUMMY') =
1707 nvl(b.revenue_category, 'DUMMY');
1708
1709 -- The table l_bulk_resource_list_member_id is having the
1710 -- corresponding RLM's for the source RLM's
1711 -- which have a match on destination.
1712
1713 -- The temp table(ORG_ID) is updated to keep track of the matching
1714 -- resource list member id's.
1715
1716 --hr_utility.trace('l_bulk_resource_list_member_id.count is ' || l_bulk_resource_list_member_id.count);
1717 IF l_bulk_resource_list_member_id.count > 0 THEN
1718
1719 FORALL j IN l_bulk_resource_list_member_id.first ..
1720 l_bulk_resource_list_member_id.last
1721 UPDATE pa_res_members_temp
1722 SET org_id = DECODE(l_bulk_enabled_flag(j), 'Y' ,
1723 l_bulk_resource_list_member_id(j),-1)
1724 WHERE resource_list_member_id = l_old_resource_list_member_id(j);
1725
1726 END IF;
1727
1728 -- So now, in pa_res_members_temp, All the source RLM's which have a match,
1729 -- have ORG_ID NOT NULL - if it is enabled, it is the RLM ID of the
1730 -- destination RLM; if it is not enabled it is -1, a dummy value which
1731 -- is converted to NULL later.
1732
1733 -- There are now records in pa_res_members_temp where ORG_ID
1734 -- is NULL - these are source RLM's which don't have a match.
1735 -- They are created
1736 -- if the list is not centrally controlled.
1737
1738 IF l_control_flag <> 'Y' THEN
1739
1740 -- Getting the source ID's without a match.
1741
1742 l_bulk_resource_list_member_id.delete; -- initializing the table
1743
1744 SELECT DISTINCT resource_list_member_id
1745 BULK COLLECT INTO l_bulk_resource_list_member_id
1746 FROM pa_res_members_temp
1747 WHERE org_id IS NULL;
1748
1749 l_new_resource_list_member_id := SYSTEM.PA_NUM_TBL_TYPE();
1750 l_new_resource_list_member_id.extend(
1751 l_bulk_resource_list_member_id.count);
1752
1753 IF l_bulk_resource_list_member_id.count > 0 THEN
1754
1755 FOR i IN l_bulk_resource_list_member_id.first ..
1756 l_bulk_resource_list_member_id.last
1757 LOOP
1758 SELECT pa_resource_list_members_s.NEXTVAL
1759 INTO l_new_resource_list_member_id(i)
1760 FROM dual;
1761 END LOOP;
1762
1763 END IF;
1764
1765
1766 IF l_bulk_resource_list_member_id.count > 0 THEN
1767
1768 --hr_utility.trace('INSIDE IF l_bulk_resource_list_member_id.count is ' || l_bulk_resource_list_member_id.count);
1769 FORALL k IN l_bulk_resource_list_member_id.first ..
1770 l_bulk_resource_list_member_id.last
1771
1772 INSERT INTO PA_RESOURCE_LIST_MEMBERS
1773 ( RESOURCE_LIST_MEMBER_ID ,
1774 RESOURCE_LIST_ID ,
1775 RESOURCE_ID ,
1776 ALIAS ,
1777 DISPLAY_FLAG ,
1778 ENABLED_FLAG ,
1779 TRACK_AS_LABOR_FLAG ,
1780 PERSON_ID ,
1781 JOB_ID ,
1782 ORGANIZATION_ID ,
1783 VENDOR_ID ,
1784 EXPENDITURE_TYPE ,
1785 EVENT_TYPE ,
1786 NON_LABOR_RESOURCE ,
1787 EXPENDITURE_CATEGORY ,
1788 REVENUE_CATEGORY ,
1789 PROJECT_ROLE_ID ,
1790 OBJECT_TYPE ,
1791 OBJECT_ID ,
1792 RESOURCE_CLASS_ID ,
1793 RESOURCE_CLASS_CODE ,
1794 RES_FORMAT_ID ,
1795 SPREAD_CURVE_ID ,
1796 ETC_METHOD_CODE ,
1797 MFC_COST_TYPE_ID ,
1798 COPY_FROM_RL_FLAG ,
1799 RESOURCE_CLASS_FLAG ,
1800 FC_RES_TYPE_CODE ,
1801 INVENTORY_ITEM_ID ,
1802 ITEM_CATEGORY_ID ,
1803 MIGRATION_CODE ,
1804 ATTRIBUTE_CATEGORY ,
1805 ATTRIBUTE1 ,
1806 ATTRIBUTE2 ,
1807 ATTRIBUTE3 ,
1808 ATTRIBUTE4 ,
1809 ATTRIBUTE5 ,
1810 ATTRIBUTE6 ,
1811 ATTRIBUTE7 ,
1812 ATTRIBUTE8 ,
1813 ATTRIBUTE9 ,
1814 ATTRIBUTE10 ,
1815 ATTRIBUTE11 ,
1816 ATTRIBUTE12 ,
1817 ATTRIBUTE13 ,
1818 ATTRIBUTE14 ,
1819 ATTRIBUTE15 ,
1820 ATTRIBUTE16 ,
1821 ATTRIBUTE17 ,
1822 ATTRIBUTE18 ,
1823 ATTRIBUTE19 ,
1824 ATTRIBUTE20 ,
1825 ATTRIBUTE21 ,
1826 ATTRIBUTE22 ,
1827 ATTRIBUTE23 ,
1828 ATTRIBUTE24 ,
1829 ATTRIBUTE25 ,
1830 ATTRIBUTE26 ,
1831 ATTRIBUTE27 ,
1832 ATTRIBUTE28 ,
1833 ATTRIBUTE29 ,
1834 ATTRIBUTE30 ,
1835 RECORD_VERSION_NUMBER ,
1836 PERSON_TYPE_CODE ,
1837 BOM_RESOURCE_ID ,
1838 TEAM_ROLE ,
1839 INCURRED_BY_RES_FLAG ,
1840 INCUR_BY_RES_CLASS_CODE ,
1841 INCUR_BY_ROLE_ID ,
1842 WP_ELIGIBLE_FLAG ,
1843 UNIT_OF_MEASURE ,
1844 LAST_UPDATED_BY ,
1845 LAST_UPDATE_DATE ,
1846 CREATION_DATE ,
1847 CREATED_BY ,
1848 LAST_UPDATE_LOGIN )
1849 SELECT
1850 l_new_resource_list_member_id(k) ,
1851 p_destination_resource_list_id ,
1852 a.resource_id ,
1853 a.alias ,
1854 a.display_flag ,
1855 a.enabled_flag ,
1856 a.track_as_labor_flag ,
1857 a.person_id ,
1858 a.job_id ,
1859 a.organization_id ,
1860 a.vendor_id ,
1861 a.expenditure_type ,
1862 a.event_type ,
1863 a.non_labor_resource ,
1864 a.expenditure_category ,
1865 a.revenue_category ,
1866 a.project_role_id ,
1867 'PROJECT' ,
1868 p_destination_project_id ,
1869 a.resource_class_id ,
1870 a.resource_class_code ,
1871 a.res_format_id ,
1872 a.spread_curve_id ,
1873 a.etc_method_code ,
1874 a.mfc_cost_type_id ,
1875 a.copy_from_rl_flag ,
1876 a.resource_class_flag ,
1877 a.fc_res_type_code ,
1878 a.inventory_item_id ,
1879 a.item_category_id ,
1880 a.migration_code ,
1881 a.attribute_category ,
1882 a.attribute1 ,
1883 a.attribute2 ,
1884 a.attribute3 ,
1885 a.attribute4 ,
1886 a.attribute5 ,
1887 a.attribute6 ,
1888 a.attribute7 ,
1889 a.attribute8 ,
1890 a.attribute9 ,
1891 a.attribute10 ,
1892 a.attribute11 ,
1893 a.attribute12 ,
1894 a.attribute13 ,
1895 a.attribute14 ,
1896 a.attribute15 ,
1897 a.attribute16 ,
1898 a.attribute17 ,
1899 a.attribute18 ,
1900 a.attribute19 ,
1901 a.attribute20 ,
1902 a.attribute21 ,
1903 a.attribute22 ,
1904 a.attribute23 ,
1905 a.attribute24 ,
1906 a.attribute25 ,
1907 a.attribute26 ,
1908 a.attribute27 ,
1909 a.attribute28 ,
1910 a.attribute29 ,
1911 a.attribute30 ,
1912 a.record_version_number ,
1913 a.person_type_code ,
1914 a.bom_resource_id ,
1915 a.team_role ,
1916 a.incurred_by_res_flag ,
1917 a.incur_by_res_class_code ,
1918 a.incur_by_role_id ,
1919 a.wp_eligible_flag ,
1920 a.unit_of_measure ,
1921 FND_GLOBAL.USER_ID ,
1922 SYSDATE ,
1923 SYSDATE ,
1924 FND_GLOBAL.USER_ID ,
1925 FND_GLOBAL.LOGIN_ID
1926 FROM pa_resource_list_members a
1927 WHERE a.resource_list_id = p_source_resource_list_id
1928 AND a.resource_list_member_id =
1929 l_bulk_resource_list_member_id(k);
1930
1931 END IF;
1932
1933 -- The table l_new_resource_list_member_id has the newly created RLM ID
1934 -- for the source RLM's that didn't have a match, which are in
1935 -- l_bulk_resource_list_member_id.
1936 -- Updating the temp table with this information.
1937
1938 IF l_bulk_resource_list_member_id.count > 0 THEN
1939
1940 FORALL x IN l_bulk_resource_list_member_id.first ..
1941 l_bulk_resource_list_member_id.last
1942 UPDATE pa_res_members_temp
1943 SET org_id = l_new_resource_list_member_id(x)
1944 WHERE resource_list_member_id = l_bulk_resource_list_member_id(x);
1945
1946 END IF;
1947
1948 END IF; -- (l_control_flag <> 'Y')
1949
1950 -- Converting the -1's to NULL
1951
1952 UPDATE pa_res_members_temp
1953 SET org_id = NULL
1954 WHERE org_id = -1;
1955
1956 -- Now, each record in the temp table has the value for ORG_ID that we
1957 -- are passing back in the out table - either a NULL or an RLM ID (new
1958 -- or existing):
1959 -- Populating the out table.
1960
1961 SELECT a.org_id
1962 BULK COLLECT INTO x_dest_res_list_member_id_tbl
1963 FROM pa_res_members_temp a,
1964 pa_res_member_id_temp b
1965 WHERE a.resource_list_member_id = b.resource_list_member_id
1966 AND a.order_id = b.order_id
1967 ORDER BY b.order_id;
1968
1969 IF p_src_res_list_member_id_tbl.count <>
1970 x_dest_res_list_member_id_tbl.count
1971 THEN
1972 RAISE l_exception;
1973 END IF;
1974 -- Clearing Temp tables
1975 DELETE FROM pa_res_members_temp;
1976
1977 DELETE FROM pa_res_member_id_temp;
1978
1979
1980 EXCEPTION
1981 WHEN l_exception THEN
1982 FND_MSG_PUB.add_exc_msg
1983 ( p_pkg_name => 'Pa_Planning_Resource_Pvt'
1984 ,p_procedure_name => 'Copy_Planning_Resources');
1985 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1986 END Copy_Planning_Resources;
1987
1988 /******************************/
1989
1990 END Pa_Planning_Resource_Pvt;
1991 /**************************/
[Home] [Help]