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