[Home] [Help]
PACKAGE BODY: APPS.PA_CREATE_RESOURCE
Source
1 PACKAGE BODY PA_CREATE_RESOURCE AS
2 /* $Header: PACRRESB.pls 120.6.12000000.6 2007/07/05 06:49:22 sugupta ship $*/
3
4 FUNCTION chk_plan_rl_unique (p_resource_list_name IN VARCHAR2,
5 p_resource_list_id IN NUMBER) return BOOLEAN;
6
7 PROCEDURE Create_Resource_group
8 (p_resource_list_id IN NUMBER,
9 p_resource_group IN VARCHAR2,
10 p_resource_name IN VARCHAR2,
11 p_alias IN VARCHAR2,
12 p_sort_order IN NUMBER,
13 p_display_flag IN VARCHAR2,
14 p_enabled_flag IN VARCHAR2,
15 p_track_as_labor_flag OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
16 p_resource_id OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
17 p_resource_list_member_id OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
18 p_err_code OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
19 p_err_stage IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
20 p_err_stack IN OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
21 IS
22 l_sort_order NUMBER := 0;
23 l_resource_type_id NUMBER := 0;
24 l_org_id NUMBER := NULL;
25
26 /*bug 1889671 : Resource Mapping Enhancement -- Code changes starts */
27 l_person_id pa_resource_txn_attributes.person_id%TYPE;
28 l_job_id pa_resource_txn_attributes.job_id%TYPE;
29 l_organization_id pa_resource_txn_attributes.organization_id%TYPE;
30 l_vendor_id pa_resource_txn_attributes.vendor_id%TYPE;
31 l_project_role_id pa_resource_txn_attributes.project_role_id%TYPE;
32 l_expenditure_type pa_resource_txn_attributes.expenditure_type%TYPE;
33 l_event_type pa_resource_txn_attributes.event_type%TYPE;
34 l_expenditure_category pa_resource_txn_attributes.expenditure_category%TYPE;
35 l_revenue_category pa_resource_txn_attributes.revenue_category%TYPE;
36 l_nlr_resource pa_resource_txn_attributes.non_labor_resource%TYPE;
37 l_nlr_res_org_id pa_resource_txn_attributes.non_labor_resource_org_id%TYPE;
38 l_event_type_cls pa_resource_txn_attributes.event_type_classification%TYPE;
39 l_system_link_function pa_resource_txn_attributes.system_linkage_function%TYPE;
40 l_resource_format_id pa_resource_txn_attributes.resource_format_id%TYPE;
41 l_res_type_code pa_resource_types.resource_type_code%TYPE;
42
43 CURSOR Cur_TXn_Attributes(p_resource_id PA_RESOURCES.RESOURCE_ID%TYPE) IS
44 SELECT prta.person_id,
45 prta.job_id,
46 prta.organization_id,
47 prta.vendor_id,
48 prta.project_role_id,
49 prta.expenditure_type,
50 prta.event_type,
51 prta.expenditure_category,
52 prta.revenue_category,
53 prta.non_labor_resource,
54 prta.non_labor_resource_org_id,
55 prta.event_type_classification,
56 prta.system_linkage_function,
57 prta.resource_format_id,
58 prt.resource_type_id,
59 prt.resource_type_code
60 FROM PA_RESOURCE_TXN_ATTRIBUTES PRTA,
61 PA_RESOURCES PR,
62 PA_RESOURCE_TYPES PRT
63 WHERE prta.resource_id = pr.resource_id
64 AND pr.resource_id =P_RESOURCE_ID
65 AND pr.resource_type_id= prt.resource_type_id;
66
67 /*changes end for 1889671 */
68
69 CURSOR c_res_list_csr IS
70 SELECT
71 group_resource_type_id
72 FROM
73 pa_resource_lists_all_bg
74 WHERE resource_list_id = p_resource_list_id;
75
76 CURSOR c_res_list_member_csr_1 IS
77 SELECT 'x'
78 FROM
79 pa_resource_list_members
80 WHERE resource_list_id = p_resource_list_id
81 AND parent_member_id IS NULL
82 AND sort_order = p_sort_order;
83
84 CURSOR c_res_list_member_csr_2 IS
85 SELECT
86 NVL(MAX(sort_order),0)+10
87 FROM
88 pa_resource_list_members
89 WHERE resource_list_id = p_resource_list_id
90 AND parent_member_id IS NULL
91 AND sort_order < 999999;
92
93 CURSOR c_res_list_member_csr_3 IS
94 SELECT 'x'
95 FROM
96 pa_resource_list_members
97 WHERE resource_list_id = p_resource_list_id
98 AND parent_member_id IS NULL
99 AND alias = p_alias;
100
101 CURSOR c_resource_types_csr IS
102 SELECT
103 resource_type_code
104 FROM
105 pa_resource_types_active_v
106 WHERE resource_type_id = l_resource_type_id;
107
108 CURSOR c_revenue_categ_csr IS -- changed for perf bug 4887375
109 /*SELECT
110 description
111 FROM
112 pa_revenue_categories_res_v
113 WHERE
114 revenue_category_code = p_resource_group;*/
115 SELECT
116 tmp.description
117 FROM (
118 SELECT
119 REVENUE_CATEGORY_CODE
120 ,REVENUE_CATEGORY_M description
121 FROM PA_REVENUE_CATEGORIES_V RC
122 WHERE DECODE(PA_GET_RESOURCE.INCLUDE_INACTIVE_RESOURCES, 'Y', START_DATE_ACTIVE,TRUNC(SYSDATE)) BETWEEN START_DATE_ACTIVE AND NVL(END_DATE_ACTIVE,TRUNC(SYSDATE))
123 ) tmp
124 WHERE
125 tmp.revenue_category_code = p_resource_group;
126
127 CURSOR c_org_csr IS
128 SELECT
129 organization_name
130 FROM
131 pa_organizations_res_v
132 WHERE
133 organization_id = l_org_id ;
134
135 CURSOR c_res_list_member_seq_csr IS
136 SELECT
137 pa_resource_list_members_s.NEXTVAL
138 FROM SYS.DUAL;
139
140 l_err_code NUMBER := 0;
141 l_old_stack VARCHAR2(2000);
142 l_dummy VARCHAR2(1);
143 l_get_new_sort_order VARCHAR2(10) := 'FALSE';
144 l_resource_name VARCHAR2(80);
145 l_alias VARCHAR2(30);
146 l_Uom VARCHAR2(30);
147 l_track_as_labor_flag VARCHAR2(1);
148 l_rollup_qty_flag VARCHAR2(1);
149 l_resource_id NUMBER := NULL;
150 l_resource_list_member_id NUMBER := NULL;
151 l_resource_type_code VARCHAR2(100);
152
153
154 BEGIN
155 l_old_stack := p_err_stack;
156 p_err_code := 0;
157 p_err_stack := p_err_stack ||'->PA_CREATE_RESOURCE.create_resource_group';
158 p_err_stage := ' Select group_resource_type_id from pa_resource_lists';
159
160 -- Get Resource List Id ,Group_resource_type_id from
161 -- PA_RESOURCE_LISTS with the
162 -- X_Resource_list_id.
163 OPEN c_res_list_csr;
164 FETCH c_res_list_csr INTO
165 l_resource_type_id;
166 IF c_res_list_csr%NOTFOUND THEN
167 p_err_code := 10;
168 p_err_stage := 'PA_RL_INVALID';
169 CLOSE c_res_list_csr;
170 RETURN;
171 END IF;
172
173 CLOSE c_res_list_csr;
174 -- If group_resource_type_id is 0 , then
175 -- the resource list has not been grouped.Hence,cannot create
176 -- a resource group
177
178 IF l_resource_type_id = 0 THEN
179 p_err_code := 11;
180 p_err_stage := 'PA_RL_NOT_GROUPED';
181 RETURN;
182 END IF;
183 IF (p_sort_order IS NULL OR p_sort_order = 0) THEN
184 l_get_new_sort_order := 'TRUE';
185 END IF;
186
187 p_err_stage := ' Select resource_type_code from pa_resource_types';
188 OPEN c_resource_types_csr;
189 FETCH c_resource_types_csr INTO
190 l_resource_type_code;
191 IF c_resource_types_csr%NOTFOUND THEN
192 p_err_code := 12;
193 p_err_stage := 'PA_RT_INVALID';
194 CLOSE c_resource_types_csr;
195 RETURN;
196 END IF;
197 CLOSE c_resource_types_csr;
198
199
200 p_err_stage := ' Select x from pa_resource_list_members';
201
202 -- Check whether sort_order is unique
203 IF (p_sort_order IS NOT NULL AND p_sort_order > 0 ) THEN
204 OPEN c_res_list_member_csr_1;
205 FETCH c_res_list_member_csr_1 INTO
206 l_dummy;
207 IF c_res_list_member_csr_1%FOUND THEN
208 l_get_new_sort_order := 'TRUE';
209 ELSE
210 l_sort_order := p_sort_order;
211 END IF;
212 CLOSE c_res_list_member_csr_1;
213 END IF;
214
215 IF l_get_new_sort_order = 'TRUE' THEN
216 p_err_stage := ' Select max(sort_order) from pa_resource_list_members';
217 OPEN c_res_list_member_csr_2;
218 FETCH c_res_list_member_csr_2 INTO
219 l_sort_order;
220 CLOSE c_res_list_member_csr_2;
221 END IF;
222
223
224 -- In the case of revenue category,need to get the
225 -- revenue_category_name also,since
226 -- what is passed is revenue_category_code
227
228 p_err_stage := 'Select description from pa_revenue_categories_res_v';
229 IF l_resource_type_code = 'REVENUE_CATEGORY' THEN
230 OPEN c_revenue_categ_csr;
231 FETCH c_revenue_categ_csr INTO
232 l_resource_name;
233 IF c_revenue_categ_csr%NOTFOUND THEN
234 p_err_code := 13;
235 p_err_stage := 'PA_INVALID_REV_CATEG';
236 CLOSE c_revenue_categ_csr;
237 RETURN;
238 ELSE
239 CLOSE c_revenue_categ_csr;
240 END IF;
241 ELSIF l_resource_type_code = 'EXPENDITURE_CATEGORY' THEN
242 l_resource_name := p_resource_group;
243 ELSIF l_resource_type_code = 'ORGANIZATION' THEN
244 l_org_id := TO_NUMBER(p_resource_group);
245 p_err_stage :=
246 ' Select organization_name from pa_organizations_res_v';
247 -- Need to get the organization_name since what is passed
248 -- is the organization id
249 OPEN c_org_csr;
250 FETCH c_org_csr INTO l_resource_name;
251 IF c_org_csr%NOTFOUND THEN
252 p_err_code := 14;
253 p_err_stage := 'PA_INVALID_ORGANIZATION';
254 CLOSE c_org_csr;
255 RETURN;
256 ELSE
257 CLOSE c_org_csr;
258 END IF;
259 END IF;
260
261 IF LENGTH(p_alias) > 0 THEN
262 l_alias := SUBSTR(p_alias,1,30);
263 ELSE
264 l_alias := p_alias;
265 END IF;
266
267 -- Check whether alias is unique
268 IF (p_alias IS NOT NULL ) THEN
269 OPEN c_res_list_member_csr_3;
270 FETCH c_res_list_member_csr_3 INTO
271 l_dummy;
272 IF c_res_list_member_csr_3%FOUND THEN
273 IF l_resource_type_code = 'EXPENDITURE_CATEGORY' THEN
274 l_alias := SUBSTR(p_resource_group,1,30);
275 ELSIF l_resource_type_code = 'REVENUE_CATEGORY' THEN
276 l_alias := SUBSTR(l_resource_name,1,30);
277 END IF;
278 END IF;
279 CLOSE c_res_list_member_csr_3; -- Bug 5347514 - added closing of csr
280 END IF;
281
282
283 IF p_alias IS NULL THEN
284 IF l_resource_type_code = 'EXPENDITURE_CATEGORY' THEN
285 l_alias := SUBSTR(p_resource_group,1,30);
286 ELSIF l_resource_type_code IN ('REVENUE_CATEGORY','ORGANIZATION')
287 THEN
288 l_alias := SUBSTR(l_resource_name,1,30);
289 END IF;
290 END IF;
291
292 IF l_resource_type_code = 'EXPENDITURE_CATEGORY' THEN
293 l_expenditure_category := p_resource_group;
294 l_revenue_category := NULL;
295 l_org_id := NULL;
296 ELSIF l_resource_type_code = 'REVENUE_CATEGORY' THEN
297 l_revenue_category := p_resource_group;
298 l_expenditure_category := NULL;
299 l_org_id := NULL;
300 ELSIF l_resource_type_code = 'ORGANIZATION' THEN
301 l_revenue_category := NULL;
302 l_expenditure_category := NULL;
303 l_org_id := TO_NUMBER(p_resource_group);
304
305 END IF;
306
307
308 -- Check whether the resource_group has already been created as
309 -- a resource in PA_RESOURCE table and get the resource_id.
310
311 PA_GET_RESOURCE.Get_Resource
312 (p_resource_name => l_resource_name,
313 p_resource_type_Code => l_resource_type_code,
314 p_person_id => NULL,
315 p_job_id => NULL,
316 p_proj_organization_id => l_org_id,
317 p_vendor_id => NULL,
318 p_expenditure_type => NULL,
319 p_event_type => NULL,
320 p_expenditure_category => l_expenditure_category,
321 p_revenue_category_code => l_revenue_category,
322 p_non_labor_resource => NULL,
323 p_system_linkage => NULL,
324 p_project_role_id => NULL,
325 p_resource_id => l_resource_id,
326 p_err_code => l_err_code,
330 IF l_err_code <> 0 THEN
327 p_err_stage => p_err_stage,
328 p_err_stack => p_err_stack );
329
331 p_err_code := l_err_code;
332 RETURN;
333 END IF;
334
335 /* For bug # 818076 fix moved this code outside the if condition */
336 PA_GET_RESOURCE.Get_Resource_Information
337 (p_resource_type_Code => l_resource_type_code,
338 p_resource_attr_value => p_resource_group,
339 p_unit_of_measure => l_uom,
340 p_Rollup_quantity_flag => l_rollup_qty_flag,
341 p_track_as_labor_flag => l_track_as_labor_flag,
342 p_err_code => l_err_code,
343 p_err_stage => p_err_stage,
344 p_err_stack => p_err_stack);
345
346 IF l_err_code <> 0 THEN
347 p_err_code := l_err_code;
348 RETURN;
349 END IF;
350
351 /* End of bug # 818076 fix */
352
353
354 IF l_resource_id IS NULL THEN
355
356 -- If the resource_group has not been created as a resource yet,then
357 -- need to create the resource.Hence,get the necessary information
358 -- from base views
359
360 /* For bug # 818076 fix moved this code outside the if condition
361 as track_as_labor flag should be assigned for resource_groups
362 being inserted into resource_member_list table */
363 /* Comment starts ********************
364
365 PA_GET_RESOURCE.Get_Resource_Information
366 (p_resource_type_Code => l_resource_type_code,
367 p_resource_attr_value => p_resource_group,
368 p_unit_of_measure => l_uom,
369 p_Rollup_quantity_flag => l_rollup_qty_flag,
370 p_track_as_labor_flag => l_track_as_labor_flag,
371 p_err_code => l_err_code,
372 p_err_stage => p_err_stage,
373 p_err_stack => p_err_stack);
374
375 IF l_err_code <> 0 THEN
376 p_err_code := l_err_code;
377 RETURN;
378 END IF;
379 *********** Comment ends, # 818076 */
380
381 Create_Resource
382 (p_resource_name => l_resource_name,
383 p_resource_type_Code => l_resource_type_code,
384 p_description => l_resource_name,
385 p_unit_of_measure => l_uom,
386 p_rollup_quantity_flag => l_rollup_qty_flag,
387 p_track_as_labor_flag => l_track_as_labor_flag,
388 p_start_date => SYSDATE,
389 p_end_date => NULL,
390 p_person_id => NULL,
391 p_job_id => NULL,
392 p_proj_organization_id => l_org_id,
393 p_vendor_id => NULL,
394 p_expenditure_type => NULL,
395 p_event_type => NULL,
396 p_expenditure_category => l_expenditure_category,
397 p_revenue_category_code => l_revenue_category,
398 p_non_labor_resource => NULL,
399 p_system_linkage => NULL,
400 p_project_role_id => NULL,
401 p_resource_id => l_resource_id,
402 p_err_code => l_err_code,
403 p_err_stage => p_err_stage,
404 p_err_stack => p_err_stack );
405
406 IF l_err_code <> 0 THEN
407 p_err_code := l_err_code;
408 RETURN;
409 END IF;
410 END IF; -- (IF l_resource_id IS NULL )
411
412 OPEN c_res_list_member_seq_csr;
413 FETCH c_res_list_member_seq_csr INTO
414 l_resource_list_member_id;
415 IF c_res_list_member_seq_csr%NOTFOUND THEN
416 CLOSE c_res_list_member_seq_csr;
417 RAISE NO_DATA_FOUND;
418 ELSE
419 CLOSE c_res_list_member_seq_csr;
420 END IF;
421
422 /*Changes done for Resource Mapping Enhancements */
423
424 OPEN Cur_Txn_Attributes(l_resource_id);
425 FETCH Cur_Txn_Attributes
426 INTO l_person_id,
427 l_job_id,
428 l_organization_id,
429 l_vendor_id,
430 l_project_role_id,
431 l_expenditure_type,
432 l_event_type,
433 l_expenditure_category,
434 l_revenue_category,
435 l_nlr_resource,
436 l_nlr_res_org_id,
437 l_event_type_cls,
438 l_system_link_function,
439 l_resource_format_id,
440 l_resource_type_id,
441 l_res_type_code;
442 CLOSE Cur_Txn_Attributes;
443
444
445 INSERT INTO pa_resource_list_members
446 (resource_list_id,
447 resource_list_member_id,
448 resource_id,
449 alias,
450 parent_member_id,
451 sort_order,
452 member_level,
453 display_flag,
457 last_update_date,
454 enabled_flag,
455 track_as_labor_flag,
456 last_updated_by,
458 creation_date,
459 created_by,
460 last_update_login,
461 PERSON_ID,
462 JOB_ID,
463 ORGANIZATION_ID,
464 VENDOR_ID,
465 PROJECT_ROLE_ID,
466 EXPENDITURE_TYPE,
467 EVENT_TYPE,
468 EXPENDITURE_CATEGORY,
469 REVENUE_CATEGORY,
470 NON_LABOR_RESOURCE,
471 NON_LABOR_RESOURCE_ORG_ID,
472 EVENT_TYPE_CLASSIFICATION,
473 SYSTEM_LINKAGE_FUNCTION,
474 RESOURCE_FORMAT_ID,
475 RESOURCE_TYPE_ID,
476 RESOURCE_TYPE_CODE
477 )
478
479 SELECT
480 p_resource_list_id,
481 l_resource_list_member_id,
482 l_resource_id,
483 l_alias,
484 NULL,
485 l_sort_order,
486 1,
487 NVL(p_display_flag,'Y'),
488 NVL(p_enabled_flag,'Y'),
489 l_track_as_labor_flag,
490 g_last_updated_by,
491 g_last_update_date,
492 g_creation_date,
493 g_created_by,
494 g_last_update_login,
495 l_person_id,
496 l_job_id,
497 l_organization_id,
498 l_vendor_id,
499 l_project_role_id,
500 l_expenditure_type,
501 l_event_type,
502 l_expenditure_category,
503 l_revenue_category,
504 l_nlr_resource,
505 l_nlr_res_org_id,
506 l_event_type_cls,
507 l_system_link_function,
508 l_resource_format_id,
509 l_resource_type_id,
510 l_res_type_code
511 FROM
512 sys.dual
513 WHERE NOT EXISTS
514 (SELECT 'x' FROM PA_RESOURCE_LIST_MEMBERS
515 WHERE resource_list_id = p_resource_list_id
516 AND resource_id = l_resource_id
517 AND parent_member_id IS NULL );
518
519 p_resource_list_member_id := l_resource_list_member_id;
520 p_track_as_labor_flag := l_track_as_labor_flag;
521 p_resource_id := l_resource_id;
522
523 p_err_stack := l_old_stack;
524
525 EXCEPTION
526 WHEN OTHERS THEN
527 p_err_code := SQLCODE;
528 RAISE;
529 END Create_Resource_group;
530
531 --
532 -- sachin Bug 2486405. Added a New parameter p_job_group_id to the procedure
533 --
534 PROCEDURE Create_Resource_List
535 (p_resource_list_name IN VARCHAR2,
536 p_description IN VARCHAR2,
537 p_public_flag IN VARCHAR2, -- DEFAULT 'Y',
538 p_group_resource_type IN VARCHAR2,
539 p_start_date IN DATE, -- DEFAULT SYSDATE,
540 p_end_date IN DATE, -- DEFAULT NULL,
541 p_business_group_id IN NUMBER, -- DEFAULT NULL,
542 p_job_group_id IN NUMBER, --Added for Bug 2486405.
543 p_job_group_name IN VARCHAR2 DEFAULT NULL,
544 p_use_for_wp_flag IN VARCHAR2 DEFAULT NULL,
545 p_control_flag IN VARCHAR2 DEFAULT NULL,
546 p_migration_code IN VARCHAR2 DEFAULT NULL,
547 p_record_version_number IN NUMBER DEFAULT NULL,
548 p_resource_list_id OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
549 p_err_code OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
550 p_err_stage IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
551 p_err_stack IN OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
552
553 IS
554 l_err_code NUMBER := 0;
555 l_old_stack VARCHAR2(2000);
556 l_dummy VARCHAR2(1);
557 l_resource_type_id NUMBER := NULL;
558 l_resource_list_id NUMBER;
559 l_resource_name VARCHAR2(80);
560 l_Uom VARCHAR2(30);
561 l_track_as_labor_flag VARCHAR2(1);
562 l_rollup_qty_flag VARCHAR2(1);
563 l_resource_id NUMBER := NULL;
564 l_resource_type_code pa_resource_types.resource_type_code%TYPE;
565 l_job_group_id NUMBER := NULL; --Added for Bug 2486405.
566 l_msg_count NUMBER;
567 l_record_version_number NUMBER := NULL;
568 l_return_status VARCHAR2(1);
569 l_format_id NUMBER;
570 l_res_class_id NUMBER;
571 l_res_class_code VARCHAR2(30);
572 l_etc_method_code VARCHAR2(30);
573 l_spread_curve_id NUMBER;
574 --l_cost_type_id NUMBER;
575 l_plan_rl_format_id NUMBER;
576 --Bug 3501039
577 l_resource_list_name Varchar2(60);
578
579 CURSOR c_res_list_csr IS
580 SELECT 'x' FROM
581 pa_resource_lists
582 WHERE NAME = p_resource_list_name;
583
584 CURSOR c_res_list_member_seq_csr IS
585 SELECT
586 pa_resource_list_members_s.NEXTVAL
587 FROM SYS.DUAL;
588
589 CURSOR csr_get_formats IS
590 SELECT fmt.res_format_id, fmt.resource_class_id, cls.resource_class_code
591 FROM pa_res_formats_b fmt, pa_resource_classes_b cls
595 CURSOR csr_get_class_def(p_resource_class_id NUMBER) IS
592 WHERE fmt.resource_class_flag = 'Y'
593 AND fmt.resource_class_id = cls.resource_class_id;
594
596 SELECT def.spread_curve_id, def.etc_method_code --, def.mfc_cost_type_id
597 FROM pa_plan_res_defaults def
598 WHERE def.resource_class_id = p_resource_class_id
599 AND def.object_type = 'CLASS';
600
601 CURSOR c_resource_groups_csr IS
602 SELECT
603 group_resource_type_id
604 FROM
605 pa_resource_groups_valid_v
606 WHERE resource_group = p_group_resource_type;
607
608 CURSOR c_res_list_seq_csr IS
609 SELECT pa_resource_lists_s.NEXTVAL
610 FROM
611 SYS.DUAL;
612
613 -- Added for Bug 2486405.
614 CURSOR c_job_group_csr IS
615 SELECT 1
616 FROM pa_jobs_v
617 WHERE job_group_id = p_job_group_id
618 AND ROWNUM = 1;
619
620 -- Following block of code is added for the resolution of bug 1889671
621 -- Same logic is used as it is done in PA_GET_RESOURCE.Get_Unclassified_Resource
622 -- Start of change
623
624 CURSOR Cur_Unclassified_Resource_List IS
625 SELECT prt.resource_type_id,prt.resource_type_code
626 FROM pa_resources pr, pa_resource_types prt
627 WHERE prt.resource_type_code='UNCLASSIFIED'
628 AND pr.resource_type_id = prt.resource_type_id;
629
630
631 BEGIN
632
633 l_old_stack := p_err_stack;
634 p_err_code := 0;
635 p_err_stack := p_err_stack ||'->PA_CREATE_RESOURCE.create_resource_list';
636 p_err_stage := 'Select x from pa_resource_lists ';
637
638 -- First clear the message stack if called from html.
639 IF p_migration_code IS NOT NULL THEN
640 FND_MSG_PUB.initialize;
641 p_err_stack := FND_API.G_RET_STS_SUCCESS;
642 END IF;
643
644 IF p_migration_code IS NULL THEN -- Added by RM
645 OPEN c_res_list_csr;
646 FETCH c_res_list_csr INTO
647 l_dummy;
648 IF c_res_list_csr%FOUND THEN
649 p_err_code := 10;
650 p_err_stage := 'PA_RL_FOUND' ;
651 CLOSE c_res_list_csr;
652 RETURN;
653 END IF;
654 -- Added by RM
655 ELSE
656 IF (chk_plan_rl_unique(p_resource_list_name,
657 p_resource_list_id) = FALSE) THEN
658 p_err_code := p_err_code + 1;
659 p_err_stage := FND_API.G_RET_STS_ERROR;
660 p_err_stack := 'PA_RL_FOUND' ;
661 pa_utils.add_message(P_App_Short_Name => 'PA',
662 P_Msg_Name => 'PA_RL_FOUND');
663 RETURN;
664 END IF;
665
666 END IF;
667
668 -- Validate Dates
669 -- Start Date is required -- ERROR MESSAGE NEEDS TO BE DONE
670 IF (p_start_date is NULL AND p_migration_code = 'N') THEN
671 p_err_code := p_err_code + 1;
672 p_err_stage := FND_API.G_RET_STS_ERROR;
673 p_err_stack := 'PA_IRS_START_NOT_NULL' ;
674 pa_utils.add_message(p_app_short_name => 'PA'
675 ,p_msg_name => 'PA_IRS_START_NOT_NULL');
676 RETURN;
677 END IF;
678
679 IF (p_start_date IS NOT NULL and p_end_date IS NOT NULL
680 and p_start_date >= p_end_date) THEN
681 p_err_code := p_err_code + 1;
682 p_err_stage := FND_API.G_RET_STS_ERROR;
683 p_err_stack := 'PA_PR_INVALID_OR_DATES' ;
684 pa_utils.add_message(p_app_short_name => 'PA'
685 ,p_msg_name => 'PA_PR_INVALID_OR_DATES');
686 RETURN;
687 END IF;
688
689 p_err_stage :=
690 'Select group_resource_type_id from pa_resource_groups_valid_v ';
691
692 IF p_migration_code IS NULL THEN -- Added by RM
693 OPEN c_resource_groups_csr;
694 FETCH c_resource_groups_csr INTO
695 l_resource_type_id;
696 IF c_resource_groups_csr%NOTFOUND THEN
697 p_err_code := 11;
698 p_err_stage := 'PA_GROUPED_RT_INVALID';
699 CLOSE c_resource_groups_csr;
700 RETURN;
701 ELSE
702 CLOSE c_resource_groups_csr;
703 END IF;
704 END IF; -- Added by RM
705
706 p_err_stage := 'Select pa_resource_lists_s.nextval from dual ';
707 OPEN c_res_list_seq_csr;
708 FETCH c_res_list_seq_csr INTO
709 l_resource_list_id;
710 IF c_res_list_seq_csr%NOTFOUND THEN
711 CLOSE c_res_list_seq_csr;
712 RAISE NO_DATA_FOUND;
713 END IF;
714
715 -----------------------Bug 2486405--------------------------
716 p_err_stage := 'Select 1 from pa_jobs_v ';
717
718 If (p_job_group_id IS NULL OR
719 p_job_group_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM) THEN
720 l_job_group_id := NULL;
721 ELSE
722 IF p_migration_code IS NULL THEN
723 OPEN c_job_group_csr;
724 FETCH c_job_group_csr INTO l_job_group_id;
725 IF c_job_group_csr%NOTFOUND THEN
726 p_err_code := 11;
727 p_err_stage := 'PA_JOB_GROUP_INVALID'; -- New Error ->The specified Job Group is invalid.
728 CLOSE c_job_group_csr;
732 CLOSE c_job_group_csr;
729 RETURN;
730 ELSE
731 l_job_group_id := p_job_group_id;
733 END IF;
734 END IF;
735 End If;
736 -----------------------Bug 2486405--------------------------
737
738 p_err_stage := 'Insert into pa_resource_lists ';
739
740 IF (p_job_group_id IS NOT NULL ) OR (p_job_group_name IS NOT NULL) THEN
741 pa_job_utils.Check_Job_GroupName_Or_Id(
742 p_job_group_id => p_job_group_id,
743 p_job_group_name => p_job_group_name,
744 p_check_id_flag => PA_STARTUP.G_Check_ID_Flag,
745 x_job_group_id => l_job_group_id,
746 x_return_status => p_err_stage,
747 x_error_message_code => p_err_stack);
748 IF p_err_stage = FND_API.G_RET_STS_ERROR THEN
749 p_err_code := p_err_code + 1;
750 PA_UTILS.Add_Message(p_app_short_name => 'PA'
751 ,p_msg_name => p_err_stack );
752 RETURN;
753 END IF;
754 END IF;
755 /**************************************************
756 * Bug - 3501039
757 * Desc - taking a substr of the p_resource_list_name
758 * before inserting into the pa_resource_lists_all_bg
759 * table.
760 ************************************************/
761 l_resource_list_name := substr(p_resource_list_name,0,58);
762 INSERT INTO pa_resource_lists_all_bg (
763 resource_list_id,
764 name,
765 business_group_id,
766 description,
767 public_flag,
768 group_resource_type_id,
769 start_date_active,
770 end_date_active,
771 uncategorized_flag,
772 job_group_id, --Added for Bug 2486405.
773 last_updated_by,
774 last_update_date,
775 creation_date,
776 created_by,
777 last_update_login,
778 control_flag, -- Added by RM
779 use_for_wp_flag, -- Added by RM
780 migration_code, -- Added by RM
781 record_version_number -- Added by RM
782 )
783 VALUES
784 (l_resource_list_id ,
785 --p_resource_list_name,
786 l_resource_list_name, --Bug 3501039
787 NVL(p_business_group_id,fnd_profile.value('PER_BUSINESS_GROUP_ID')), -- MOAC Changes - get from HR profile
788 --NVL(p_description,p_resource_list_name),
789 NVL(p_description,l_resource_list_name), --Bug 3501039
790 NVL(p_public_flag,'Y'),
791 l_resource_type_id,
792 NVL(p_start_date,SYSDATE),
793 p_end_date,
794 'N',
795 l_job_group_id, --Added for Bug 2486405.
796 g_last_updated_by,
797 g_last_update_date,
798 g_creation_date,
799 g_created_by,
800 g_last_update_login,
801 p_control_flag, -- Added by RM
802 p_use_for_wp_flag, -- Added by RM
803 p_migration_code, -- Added by RM
804 1 -- Added by RM
805 );
806
807 /* commented for bug 6079140 IF p_migration_code = 'N' THEN -- Added by RM */
808 -- New lists - insert into TL table
809 insert into pa_resource_lists_tl (
810 LAST_UPDATE_LOGIN,
811 CREATION_DATE,
812 CREATED_BY,
813 LAST_UPDATE_DATE,
814 LAST_UPDATED_BY,
815 RESOURCE_LIST_ID,
816 NAME,
817 DESCRIPTION,
818 LANGUAGE,
819 SOURCE_LANG
820 ) select
821 g_last_update_login,
822 g_creation_date,
823 g_created_by,
824 g_last_update_date,
825 g_last_updated_by,
826 L_RESOURCE_LIST_ID,
827 p_resource_list_name,
828 NVL(p_description,p_resource_list_name),
829 L.LANGUAGE_CODE,
830 userenv('LANG')
831 from FND_LANGUAGES L
832 where L.INSTALLED_FLAG in ('I', 'B')
833 and not exists
834 (select NULL
835 from pa_resource_lists_tl T
836 where T.RESOURCE_LIST_ID = L_RESOURCE_LIST_ID
837 and T.LANGUAGE = L.LANGUAGE_CODE);
838
839 /* commented for bug 6079140 END IF; -- Adding to TL */
840
841 IF p_migration_code IS NULL THEN -- Added by RM
842 -- Need to create one Unclassified Resource for the resource list
843 PA_GET_RESOURCE.Get_Unclassified_Resource
844 (p_resource_id => l_resource_id,
845 p_resource_name => l_resource_name,
846 p_track_as_labor_flag => l_track_as_labor_flag,
847 p_unit_of_measure => l_uom,
848 p_rollup_quantity_flag => l_rollup_qty_flag,
849 p_err_code => l_err_code,
850 p_err_stage => p_err_stage,
851 p_err_stack => p_err_stack );
852
853 IF l_err_code <> 0 THEN
854 p_err_code := l_err_code;
855 RETURN;
856 END IF;
857
858 p_err_stage := 'Insert into pa_resource_list_members ';
859
860 -- Following block of code is added for the resolution of bug 1889671
861
862 OPEN Cur_Unclassified_Resource_List;
866 INSERT INTO pa_resource_list_members
863 FETCH Cur_Unclassified_Resource_List INTO l_resource_type_id , l_resource_type_code;
864 CLOSE Cur_Unclassified_Resource_List;
865
867 (resource_list_id,
868 resource_list_member_id,
869 resource_id,
870 alias,
871 parent_member_id,
872 sort_order,
873 member_level,
874 display_flag,
875 enabled_flag,
876 track_as_labor_flag,
877 resource_type_id,
878 resource_type_code,
879 last_updated_by,
880 last_update_date,
881 creation_date,
882 created_by,
883 last_update_login )
884 VALUES (
885 l_resource_list_id,
886 pa_resource_list_members_s.NEXTVAL,
887 l_resource_id,
888 l_resource_name,
889 NULL,
890 9999999,
891 1,
892 'N',
893 'Y',
894 l_track_as_labor_flag,
895 l_resource_type_id,
896 l_resource_type_code,
897 g_last_updated_by,
898 g_last_update_date,
899 g_creation_date,
900 g_created_by,
901 g_last_update_login );
902
903 p_resource_list_id := l_resource_list_id;
904
905 p_err_stack := l_old_stack;
906 ELSE -- New Planning Resource Lists
907 -- Add the four seeded class formats:
908 open csr_get_formats;
909 LOOP
910 fetch csr_get_formats into l_format_id, l_res_class_id,
911 l_res_class_code;
912 exit when csr_get_formats%NOTFOUND;
913 pa_plan_rl_formats_pvt.Create_Plan_RL_Format(
914 P_Res_List_Id => l_resource_list_id,
915 P_Res_Format_Id => l_format_id,
916 X_Plan_RL_Format_Id => l_plan_rl_format_id,
917 X_Record_Version_Number => l_record_version_number,
918 X_Return_Status => p_err_stage,
919 X_Msg_Count => p_err_code,
920 X_Msg_Data => p_err_stack);
921
922 IF p_err_stage <> FND_API.G_RET_STS_SUCCESS THEN
923 RETURN;
924 END IF;
925 open csr_get_class_def(l_res_class_id);
926 fetch csr_get_class_def into l_spread_curve_id,
927 l_etc_method_code;
928 --l_cost_type_id;
929 close csr_get_class_def;
930
931 -- New Planning Resource Lists
932 -- Add four resources - one for each class.
933 /***********************************************
934 * Defaulting the record version_number to 1
935 * and Migration_code = 'N' while doing this insert.
936 * *********************************************/
937 /*********************************************
938 * Bug : 3476765
939 * Desc : Defaulting the value of incurred_by_res_flag
940 * to 'N' while doing the insert.
941 *********************************************/
942 /**********************************************
943 * Bug - 3591751
944 * Desc - Defaulting the value of wp_eligible_flag
945 * to 'Y' while doing the insert.
946 ***********************************************/
947 INSERT INTO pa_resource_list_members
948 (resource_list_id,
949 resource_list_member_id,
950 resource_id,
951 alias,
952 display_flag,
953 enabled_flag,
954 track_as_labor_flag,
955 last_updated_by,
956 last_update_date,
957 creation_date,
958 created_by,
959 last_update_login,
960 spread_curve_id,
961 etc_method_code,
962 mfc_cost_type_id,
963 object_type,
964 object_id,
965 res_format_id,
966 resource_class_flag,
967 resource_class_id,
968 resource_class_code,
969 Migration_code,
970 incurred_by_res_flag,
971 Record_version_number,
972 wp_eligible_flag,
973 --Bug 3636856
974 unit_of_measure
975 )
976 VALUES (
977 l_resource_list_id,
978 pa_resource_list_members_s.NEXTVAL,
979 -99,
980 initcap(replace(l_res_class_code, '_', ' ')),
981 'Y',
982 'Y',
983 decode(l_res_class_code, 'PEOPLE', 'Y', 'N'),
984 g_last_updated_by,
985 g_last_update_date,
986 g_creation_date,
987 g_created_by,
988 g_last_update_login,
989 l_spread_curve_id,
990 l_etc_method_code,
991 NULL,
992 'RESOURCE_LIST',
993 l_resource_list_id,
994 l_format_id,
995 'Y',
996 l_res_class_id,
997 l_res_class_code,
998 'N',
999 'N',
1000 1,
1001 'Y',
1002 --Bug 3636856
1003 DECODE(l_res_class_code,'PEOPLE','HOURS','EQUIPMENT','HOURS',
1004 'MATERIAL_ITEMS','DOLLARS','FINANCIAL_ELEMENTS','DOLLARS'));
1005
1006 END LOOP;
1007 END IF;
1008
1009 p_resource_list_id := l_resource_list_id;
1010
1011 EXCEPTION
1012 WHEN OTHERS THEN
1013 p_err_code := SQLCODE;
1014 RAISE;
1015
1019 --Type: Procedure
1016 END Create_Resource_list;
1017
1018 --Name: Update_Resource_List
1020 --Description: This procedure updates header information for a
1021 -- planning resource list
1022
1023 PROCEDURE Update_Resource_List
1024 (p_resource_list_name IN VARCHAR2 DEFAULT NULL,
1025 p_description IN VARCHAR2 DEFAULT NULL,
1026 p_start_date IN DATE DEFAULT NULL,
1027 p_end_date IN DATE DEFAULT NULL,
1028 p_job_group_id IN OUT NOCOPY NUMBER,
1029 p_job_group_name IN VARCHAR2 DEFAULT NULL,
1030 p_use_for_wp_flag IN VARCHAR2 DEFAULT NULL,
1031 p_control_flag IN VARCHAR2 DEFAULT NULL,
1032 p_migration_code IN VARCHAR2 DEFAULT NULL,
1033 p_record_version_number IN OUT NOCOPY NUMBER,
1034 p_resource_list_id IN NUMBER,
1035 x_msg_count OUT NOCOPY NUMBER,
1036 x_return_status OUT NOCOPY VARCHAR2,
1037 x_msg_data OUT NOCOPY VARCHAR2) IS
1038 /*************************************************************
1039 * Bug : 3473679
1040 * Description : Modified the below cursor to only pick up
1041 * those records where the res_type_code
1042 * is NAMED_PERSON, INVENTORY_ITEM, BOM_LABOR,
1043 * BOM_EQUIPMENT, NON_LABOR_RESOURCE
1044 * and the count is more than 1.
1045 * Earlier we were not allowing the user to set the
1046 * enabled flag to 'Y' if it was already being used
1047 * irrespective of the format.
1048 ***********************************************************/
1049 CURSOR chk_wp_change_allowed IS
1050 select count(typ.res_type_code)-- , typ.res_type_code
1051 from pa_plan_rl_formats prl,
1052 pa_res_formats_b fmt,
1053 pa_res_types_b typ
1054 where prl.resource_list_id = p_resource_list_id
1055 and prl.res_format_id = fmt.res_format_id
1056 and fmt.res_type_id = typ.res_type_id
1057 and typ.res_type_code in ('NAMED_PERSON', 'INVENTORY_ITEM', 'BOM_LABOR',
1058 'BOM_EQUIPMENT', 'NON_LABOR_RESOURCE')
1059 group by typ.res_type_code
1060 having count(typ.res_type_code) > 1;
1061
1062 --Bug 3605602
1063 --Using pa_resource_list_assignments_v instead of
1064 --pa_resource_list_assignments.
1065 CURSOR chk_wp_disable IS
1066 SELECT 'N'
1067 FROM pa_resource_lists_all_bg rl
1068 WHERE rl.resource_list_id = p_resource_list_id
1069 AND rl.use_for_wp_flag <> p_use_for_wp_flag
1070 --AND exists (select 'Y' from pa_resource_list_assignments rla
1071 AND exists (select 'Y' from pa_resource_list_assignments_v rla
1072 where rla.resource_list_id = rl.resource_list_id
1073 and rla.use_for_wp_flag = 'Y');
1074
1075 --Bug 3605602
1076 --Using pa_resource_list_assignments_v instead of
1077 --pa_resource_list_assignments.
1078 CURSOR chk_ctrl_changed IS
1079 SELECT 'N'
1080 FROM pa_resource_lists_all_bg rl
1081 WHERE rl.resource_list_id = p_resource_list_id
1082 AND rl.control_flag <> p_control_flag
1083 --AND exists (select 'Y' from pa_resource_list_assignments rla
1084 AND exists (select 'Y' from pa_resource_list_assignments_v rla
1085 where rla.resource_list_id = rl.resource_list_id);
1086
1087 CURSOR chk_job_group_allow IS
1088 SELECT 'N'
1089 FROM pa_resource_list_members
1090 WHERE resource_list_id = p_resource_list_id
1091 AND job_id IS NOT NULL;
1092
1093 CURSOR get_job_group_id IS
1094 SELECT job_group_id
1095 FROM pa_resource_lists_all_bg
1096 WHERE resource_list_id = p_resource_list_id;
1097
1098 /********************************************
1099 * Bug : 3473679
1100 * Desc : This cursor is being used to get the value of
1101 * the enabled flag for the resource list in
1102 * the database.
1103 ********************************************/
1104 CURSOR get_wp_flag IS
1105 SELECT use_for_wp_flag
1106 FROM pa_resource_lists_all_bg
1107 WHERE resource_list_id = p_resource_list_id;
1108
1109 CURSOR chk_migrated_list IS
1110 SELECT 'N'
1111 FROM pa_resource_lists_all_bg rl
1112 WHERE rl.resource_list_id = p_resource_list_id
1113 AND rl.control_flag <> p_control_flag
1114 AND rl.migration_code = 'M';
1115
1116
1117 l_wp_type_count NUMBER := 0;
1118 l_wp_flag VARCHAR2(1) := NULL;
1119 l_wp_disable VARCHAR2(1) := 'Y';
1120 l_ctrl_allowed VARCHAR2(1) := 'Y';
1121 l_job_allowed VARCHAR2(1) := 'Y';
1122 l_job_group_id NUMBER;
1123 -- added for bug: 4537865
1124 l_new_job_group_id NUMBER;
1125 -- added for bug: 4537865
1126 BEGIN
1127 -- First clear the message stack.
1128 FND_MSG_PUB.initialize;
1129
1130 x_msg_count := 0;
1131 x_return_status := FND_API.G_RET_STS_SUCCESS;
1132
1133 -- Check Name uniqueness
1134
1135 IF (chk_plan_rl_unique(p_resource_list_name,
1136 p_resource_list_id) = FALSE) THEN
1137
1138 x_msg_count := x_msg_count + 1;
1139 x_return_status := FND_API.G_RET_STS_ERROR;
1140 x_msg_data := 'PA_RL_FOUND' ;
1141 pa_utils.add_message(P_App_Short_Name => 'PA',
1145
1142 P_Msg_Name => 'PA_RL_FOUND');
1143 RETURN;
1144 END IF;
1146 -- Validate Dates
1147 -- Start Date is required -- ERROR MESSAGE NEEDS TO BE DONE
1148 IF p_start_date is NULL THEN
1149 x_msg_count := x_msg_count + 1;
1150 x_return_status := FND_API.G_RET_STS_ERROR;
1151 x_msg_data := 'PA_IRS_START_NOT_NULL' ;
1152 pa_utils.add_message(p_app_short_name => 'PA'
1153 ,p_msg_name => 'PA_IRS_START_NOT_NULL');
1154 RETURN;
1155 END IF;
1156
1157 IF (p_start_date IS NOT NULL and p_end_date IS NOT NULL
1158 and p_start_date >= p_end_date) THEN
1159 x_msg_count := x_msg_count + 1;
1160 x_return_status := FND_API.G_RET_STS_ERROR;
1161 x_msg_data := 'PA_PR_INVALID_OR_DATES' ;
1162 pa_utils.add_message(p_app_short_name => 'PA'
1163 ,p_msg_name => 'PA_PR_INVALID_OR_DATES');
1164 RETURN;
1165 END IF;
1166
1167 /**************************************************
1168 * This cursor is used to get the enabled_flag for
1169 * the resource_list from the Database.
1170 * This will be used in determining if the Value
1171 * is being changed ot not.
1172 * *********************************************/
1173 open get_wp_flag;
1174 fetch get_wp_flag into l_wp_flag;
1175 close get_wp_flag;
1176
1177 -- Check if Enable for WP has changed to 'Y'
1178 IF p_use_for_wp_flag = 'Y' and p_use_for_wp_flag <> l_wp_flag THEN
1179 open chk_wp_change_allowed;
1180 fetch chk_wp_change_allowed into l_wp_type_count;
1181 IF chk_wp_change_allowed%FOUND THEN
1182 x_msg_count := x_msg_count + 1;
1183 x_return_status := FND_API.G_RET_STS_ERROR;
1184 x_msg_data := 'PA_WP_ENABLE_ERR';
1185 pa_utils.add_message(p_app_short_name => 'PA'
1186 ,p_msg_name => 'PA_WP_ENABLE_ERR');
1187 close chk_wp_change_allowed;
1188 RETURN;
1189 END IF;
1190 close chk_wp_change_allowed;
1191
1192 -- Check if Enable for WP has changed to 'N'
1193 ELSIF p_use_for_wp_flag = 'N' THEN
1194 open chk_wp_disable;
1195 fetch chk_wp_disable into l_wp_disable;
1196 IF chk_wp_disable%FOUND THEN
1197 x_msg_count := x_msg_count + 1;
1198 x_return_status := FND_API.G_RET_STS_ERROR;
1199 x_msg_data := 'PA_WP_DISABLE_ERR';
1200 pa_utils.add_message(p_app_short_name => 'PA'
1201 ,p_msg_name => 'PA_WP_DISABLE_ERR');
1202 close chk_wp_disable;
1203 RETURN;
1204 END IF;
1205 close chk_wp_disable;
1206 END IF;
1207
1208 -- Check if control flag has changed to 'Y'
1209 --Bug 3605602
1210 --We should not do the below check just when the flag is changed to
1211 --'Y' but for all cases.
1212 --IF p_control_flag = 'Y' THEN
1213
1214 open chk_ctrl_changed;
1215 fetch chk_ctrl_changed into l_ctrl_allowed;
1216 IF chk_ctrl_changed%FOUND THEN
1217 x_msg_count := x_msg_count + 1;
1218 x_return_status := FND_API.G_RET_STS_ERROR;
1219 x_msg_data := 'PA_CTRL_FLG_ERR';
1220 pa_utils.add_message(p_app_short_name => 'PA'
1221 ,p_msg_name => 'PA_CTRL_FLG_ERR');
1222 close chk_ctrl_changed;
1223 RETURN;
1224 END IF;
1225 close chk_ctrl_changed;
1226
1227 -- begin bug 3695571
1228 open chk_migrated_list;
1229 fetch chk_migrated_list into l_ctrl_allowed;
1230 If chk_migrated_list%NotFound Then
1231 Null;
1232 Else
1233 x_msg_count := x_msg_count + 1;
1234 x_return_status := FND_API.G_RET_STS_ERROR;
1235 x_msg_data := 'PA_CTRL_FLG_MIG_ERR';
1236 pa_utils.add_message(p_app_short_name => 'PA'
1237 ,p_msg_name => 'PA_CTRL_FLG_MIG_ERR');
1238 close chk_migrated_list;
1239 Return;
1240 End If;
1241 close chk_migrated_list;
1242 -- end bug 3695571
1243
1244 --END IF;
1245
1246 -- Validate job group ID and name - convert to ID to synch them up
1247 -- hr_utility.trace_on(NULL, 'RMJOB');
1248 -- hr_utility.trace('before job group id check');
1249 IF (p_job_group_id IS NOT NULL ) OR (p_job_group_name IS NOT NULL) THEN
1250 -- hr_utility.trace('inside job group id check');
1251 pa_job_utils.Check_Job_GroupName_Or_Id(
1252 p_job_group_id => p_job_group_id,
1253 p_job_group_name => p_job_group_name,
1254 p_check_id_flag => PA_STARTUP.G_Check_ID_Flag,
1255 -- x_job_group_id => p_job_group_id, * commented for bug: 4537865
1256 x_job_group_id => l_new_job_group_id, --added for bug : 4537865
1257 x_return_status => x_return_status,
1258 x_error_message_code => x_msg_data);
1259
1260 --added fopr bug : 4537865
1261 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
1262 p_job_group_id := l_new_job_group_id;
1263 END IF;
1264 --added fopr bug : 4537865
1265
1266 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1267 x_msg_count := x_msg_count + 1;
1268 PA_UTILS.Add_Message(p_app_short_name => 'PA'
1269 ,p_msg_name => x_msg_data);
1270 RETURN;
1271 END IF;
1272 END IF;
1276 -- hr_utility.trace('get job group id ');
1273 -- check whether any planning resources with jobs exist on the list
1274 -- if they do, cannot change job group
1275
1277 open get_job_group_id;
1278 fetch get_job_group_id into l_job_group_id;
1279 close get_job_group_id;
1280 -- hr_utility.trace('l_job_group_id is : ' || l_job_group_id);
1281 -- hr_utility.trace('p_job_group_id is : ' || p_job_group_id);
1282 IF (l_job_group_id IS NOT NULL) AND
1283 ((l_job_group_id <> p_job_group_id) OR (p_job_group_id IS NULL) OR
1284 (p_job_group_id = FND_API.G_MISS_NUM)) THEN
1285 open chk_job_group_allow;
1286 fetch chk_job_group_allow into l_job_allowed;
1287 -- hr_utility.trace('l_job_allowed is : ' || l_job_allowed);
1288 IF chk_job_group_allow%FOUND THEN
1289 x_msg_count := x_msg_count + 1;
1290 x_return_status := FND_API.G_RET_STS_ERROR;
1291 x_msg_data := 'PA_JOB_GROUP_ERR';
1292 pa_utils.add_message(p_app_short_name => 'PA'
1293 ,p_msg_name => 'PA_JOB_GROUP_ERR');
1294 close chk_job_group_allow;
1295 RETURN;
1296 END IF;
1297 close chk_job_group_allow;
1298
1299 END IF;
1300 --Bug 3501039
1301 update pa_resource_lists_all_bg
1302 set name = substr(nvl(p_resource_list_name, name),0,58),
1303 description = p_description,
1304 job_group_id = p_job_group_id,
1305 start_date_active = nvl(p_start_date, start_date_active),
1306 end_date_active = p_end_date, --Removed nvl for bug 3787913
1307 last_updated_by = g_last_updated_by,
1308 last_update_date = g_last_update_date,
1309 last_update_login = g_last_update_login,
1310 control_flag = p_control_flag,
1311 use_for_wp_flag = p_use_for_wp_flag,
1312 record_version_number = record_version_number + 1
1313 where resource_list_id = p_resource_list_id
1314 and nvl(record_version_number, 0) = nvl(p_record_version_number, 0);
1315
1316 IF (SQL%NOTFOUND) THEN
1317 PA_UTILS.Add_message(p_app_short_name => 'PA'
1318 ,p_msg_name => 'PA_XC_RECORD_CHANGED');
1319 x_msg_count := x_msg_count + 1;
1320 x_return_status := FND_API.G_RET_STS_ERROR;
1321 x_msg_data := 'PA_XC_RECORD_CHANGED';
1322 RETURN;
1323 END IF;
1324
1325 p_record_version_number := p_record_version_number + 1;
1326
1327 update pa_resource_lists_tl set
1328 NAME = nvl(p_resource_list_name, name),
1329 DESCRIPTION = P_DESCRIPTION,
1330 LAST_UPDATE_DATE = g_last_update_date,
1331 LAST_UPDATED_BY = g_last_updated_by,
1332 LAST_UPDATE_LOGIN = g_last_update_login,
1333 SOURCE_LANG = userenv('LANG')
1334 where resource_list_id = p_resource_list_id
1335 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
1336
1337 if (sql%notfound) then
1338 raise no_data_found;
1339 end if;
1340
1341 END Update_Resource_List;
1342
1343 FUNCTION chk_plan_rl_unique (p_resource_list_name IN VARCHAR2,
1344 p_resource_list_id IN NUMBER) return BOOLEAN
1345 IS
1346 CURSOR check_plan_rl_unique IS
1347 SELECT 'N' FROM
1348 pa_resource_lists_tl
1349 WHERE NAME = p_resource_list_name
1350 AND LANGUAGE = userenv('LANG')
1351 AND ((resource_list_id <> p_resource_list_id
1352 AND p_resource_list_id IS NOT NULL)
1353 OR p_resource_list_id IS NULL);
1354
1355 CURSOR check_old_lists IS
1356 SELECT 'N' FROM
1357 pa_resource_lists_all_bg
1358 WHERE NAME = p_resource_list_name
1359 AND ((resource_list_id <> p_resource_list_id
1360 AND p_resource_list_id IS NOT NULL)
1361 OR p_resource_list_id IS NULL);
1362 l_return BOOLEAN := TRUE;
1363 l_dummy VARCHAR2(1) := 'Y';
1364 BEGIN
1365 OPEN check_plan_rl_unique;
1366 FETCH check_plan_rl_unique into l_dummy;
1367
1368 IF check_plan_rl_unique%FOUND THEN
1369 l_return := FALSE;
1370 ELSE
1371 -- check against existing old forms created lists
1372 OPEN check_old_lists;
1373 FETCH check_old_lists into l_dummy;
1374
1375 IF check_old_lists%FOUND THEN
1376 l_return := FALSE;
1377 ELSE
1378 l_return := TRUE;
1379 END IF;
1380 CLOSE check_old_lists;
1381 END IF;
1382
1383 CLOSE check_plan_rl_unique;
1384
1385 RETURN l_return;
1386
1387 END chk_plan_rl_unique;
1388 --Name: Create_Resource_txn_Attribute
1389 --Type: Procedure
1390 --Description: This procedure inserts rows into pa_resource_txn_attributes...
1391 --
1392 --Called subprograms: ?
1393 --
1394 --History:
1395 -- xx-xxx-xxxx rkrishna Created
1396 --
1397 -- 16-MAR-2001 jwhite Bug 1685015: Forecast/Bgt Integration
1398 -- 1. New IN-parameter, p_project_role_id, required.
1399 -- 2. Add project_role_id_flag join to c_res_format_csr
1400 -- 3. new p_resource_type_code assignment
1401 -- 4. modify insert for new project_role_id_flag
1402 --
1403 PROCEDURE Create_Resource_txn_Attribute
1404 ( p_resource_id IN NUMBER,
1405 p_resource_type_Code IN VARCHAR2,
1409 p_vendor_id IN NUMBER,
1406 p_person_id IN NUMBER,
1407 p_job_id IN NUMBER,
1408 p_proj_organization_id IN NUMBER,
1410 p_expenditure_type IN VARCHAR2,
1411 p_event_type IN VARCHAR2,
1412 p_expenditure_category IN VARCHAR2,
1413 p_revenue_category_code IN VARCHAR2,
1414 p_non_labor_resource IN VARCHAR2,
1415 p_system_linkage IN VARCHAR2,
1416 p_project_role_id IN NUMBER,
1417 p_resource_txn_attribute_id OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
1418 p_err_code OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
1419 p_err_stage IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1420 p_err_stack IN OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
1421 IS
1422 l_err_code NUMBER := 0;
1423 l_old_stack VARCHAR2(2000);
1424
1425 l_person_id_flag VARCHAR2(1) := 'N';
1426 l_job_id_flag VARCHAR2(1) := 'N';
1427 l_proj_organization_id_flag VARCHAR2(1) := 'N';
1428 l_vendor_id_flag VARCHAR2(1) := 'N';
1429 l_expenditure_type_flag VARCHAR2(1) := 'N';
1430 l_event_type_flag VARCHAR2(1) := 'N';
1431 l_expenditure_category_flag VARCHAR2(1) := 'N';
1432 l_revenue_category_code_flag VARCHAR2(1) := 'N';
1433
1434 /* Bug # 932398 Fix : Added flags for non-labor resources */
1435 l_non_labor_resource_flag VARCHAR2(1) := 'N';
1436 l_non_labor_res_org_id_flag VARCHAR2(1) := 'N';
1437
1438 -- Forecast/Bgt Integration
1439 l_project_role_id_flag VARCHAR2(1) := 'N';
1440 l_project_role_id NUMBER := NULL;
1441
1442 l_resource_txn_attribute_id NUMBER;
1443 l_resource_format_id NUMBER;
1444 l_resource_class_code VARCHAR2(30);
1445 l_person_id NUMBER := NULL;
1446 l_job_id NUMBER := NULL;
1447 l_proj_organization_id NUMBER := NULL;
1448 l_vendor_id NUMBER := NULL;
1449 l_expenditure_type VARCHAR2(80) := NULL;
1450 l_event_type VARCHAR2(80) := NULL;
1451 l_expenditure_category VARCHAR2(80) := NULL;
1452 l_revenue_category_code VARCHAR2(80) := NULL;
1453
1454 CURSOR c_res_types_csr IS
1455 SELECT
1456 resource_class_code
1457 FROM
1458 pa_resource_types_active_v
1459 WHERE
1460 resource_type_code = p_resource_type_code;
1461
1462 CURSOR c_res_format_csr IS
1463 SELECT
1464 resource_format_id
1465 FROM
1466 pa_resource_formats
1467 WHERE person_id_flag = l_person_id_flag
1468 AND job_id_flag = l_job_id_flag
1469 AND organization_id_flag = l_proj_organization_id_flag
1470 AND vendor_id_flag = l_vendor_id_flag
1471 AND expenditure_type_flag = l_expenditure_type_flag
1472 AND event_type_flag = l_event_type_flag
1473 AND expenditure_category_flag = l_expenditure_category_flag
1474 AND revenue_category_flag = l_revenue_category_code_flag
1475 AND non_labor_resource_flag = l_non_labor_resource_flag
1476 AND non_labor_resource_org_id_flag = l_non_labor_res_org_id_flag
1477 AND project_role_id_flag = l_project_role_id_flag;
1478
1479 /* Bug # 932398 Fix : Added flags for non-labor resources in the above cursor */
1480
1481 CURSOR c_res_txn_attr_seq_csr IS
1482 SELECT pa_resource_txn_attributes_s.NEXTVAL
1483 FROM SYS.DUAL;
1484
1485 BEGIN
1486 l_old_stack := p_err_stack;
1487 p_err_code := 0;
1488 p_err_stack :=
1489 p_err_stack ||'->PA_CREATE_RESOURCE.Create_Resource_txn_Attribute';
1490
1491
1492 p_err_stage := 'Select resource_class_code from pa_resource_types_active_v';
1493
1494 OPEN c_res_types_csr;
1495 FETCH c_res_types_csr INTO l_resource_class_code;
1496 IF c_res_types_csr%NOTFOUND THEN
1497 p_err_code := 10;
1498 p_err_stage := 'PA_RT_INVALID';
1499 CLOSE c_res_types_csr;
1500 RETURN;
1501 END IF;
1502 CLOSE c_res_types_csr;
1503
1504 IF l_resource_class_code = 'USER_DEFINED' THEN
1505 l_person_id := p_person_id;
1506 l_job_id := p_job_id;
1507 l_proj_organization_id := p_proj_organization_id;
1508 l_vendor_id := p_vendor_id;
1509 l_expenditure_type := p_expenditure_type;
1510 l_event_type := p_event_type;
1511 l_expenditure_category := p_expenditure_category;
1512 l_revenue_category_code := p_revenue_category_code;
1513 l_project_role_id := p_project_role_id;
1514 ELSIF
1515 l_resource_class_code = 'PRE_DEFINED' THEN
1516 -- Need to get the resource_format_id from pa_resource_formats
1517 IF p_resource_type_code = 'EMPLOYEE' THEN
1518 l_person_id_flag := 'Y';
1519 l_person_id := p_person_id;
1523 ELSIF p_resource_type_code = 'ORGANIZATION' THEN
1520 ELSIF p_resource_type_code = 'JOB' THEN
1521 l_job_id_flag := 'Y';
1522 l_job_id := p_job_id;
1524 l_proj_organization_id_flag := 'Y';
1525 l_proj_organization_id := p_proj_organization_id;
1526 ELSIF p_resource_type_code = 'VENDOR' THEN
1527 l_vendor_id_flag := 'Y';
1528 l_vendor_id := p_vendor_id;
1529 ELSIF p_resource_type_code = 'EXPENDITURE_TYPE' THEN
1530 l_expenditure_type_flag := 'Y';
1531 l_expenditure_type := p_expenditure_type;
1532 ELSIF p_resource_type_code = 'EVENT_TYPE' THEN
1533 l_event_type_flag := 'Y';
1534 l_event_type := p_event_type;
1535 ELSIF p_resource_type_code = 'EXPENDITURE_CATEGORY' THEN
1536 l_expenditure_category_flag := 'Y';
1537 l_expenditure_category := p_expenditure_category;
1538 ELSIF p_resource_type_code = 'REVENUE_CATEGORY' THEN
1539 l_revenue_category_code_flag := 'Y';
1540 l_revenue_category_code := p_revenue_category_code;
1541 ELSIF p_resource_type_code = 'PROJECT_ROLE' THEN
1542 l_project_role_id_flag := 'Y';
1543 l_project_role_id := p_project_role_id;
1544 END IF;
1545 END IF;
1546
1547 p_err_stage := 'Select resource_format_id from pa_resource_formats ';
1548
1549 OPEN c_res_format_csr;
1550 FETCH c_res_format_csr INTO
1551 l_resource_format_id;
1552 IF c_res_format_csr%NOTFOUND THEN
1553 p_err_code := 11;
1554 p_err_stage := 'PA_RES_FORMAT_INVALID';
1555 CLOSE c_res_format_csr;
1556 RETURN;
1557 ELSE
1558 CLOSE c_res_format_csr;
1559 END IF;
1560
1561 p_err_stage := 'Select pa_resource_txn_attributes_s.nextval from sys.dual ';
1562
1563 OPEN c_res_txn_attr_seq_csr;
1564 FETCH c_res_txn_attr_seq_csr INTO
1565 l_resource_txn_attribute_id;
1566 IF c_res_txn_attr_seq_csr%NOTFOUND THEN
1567 CLOSE c_res_txn_attr_seq_csr;
1568 RAISE NO_DATA_FOUND;
1569 ELSE
1570 CLOSE c_res_txn_attr_seq_csr;
1571 END IF;
1572
1573 p_err_stage := 'Insert into pa_resource_txn_attributes ';
1574
1575 INSERT INTO pa_resource_txn_attributes
1576 (
1577 resource_txn_attribute_id,
1578 resource_id ,
1579 person_id,
1580 job_id ,
1581 organization_id,
1582 vendor_id,
1583 expenditure_type,
1584 event_type,
1585 non_labor_resource ,
1586 expenditure_category,
1587 revenue_category ,
1588 non_labor_resource_org_id ,
1589 event_type_classification,
1590 system_linkage_function ,
1591 resource_format_id ,
1592 last_updated_by ,
1593 last_update_date,
1594 creation_date,
1595 created_by,
1596 last_update_login,
1597 project_role_id
1598 )
1599 VALUES
1600 (l_resource_txn_attribute_id,
1601 p_resource_id,
1602 l_person_id,
1603 l_job_id,
1604 l_proj_organization_id,
1605 l_vendor_id,
1606 l_expenditure_type,
1607 l_event_type,
1608 p_non_labor_resource,
1609 l_expenditure_category,
1610 l_revenue_category_code,
1611 NULL,
1612 NULL,
1613 p_system_linkage,
1614 l_resource_format_id,
1615 g_last_updated_by,
1616 g_last_update_date,
1617 g_creation_date,
1618 g_created_by,
1619 g_last_update_login,
1620 l_project_role_id
1621 );
1622
1623 p_resource_txn_attribute_id := l_resource_txn_attribute_id;
1624
1625 p_err_stack := l_old_stack;
1626
1627 EXCEPTION
1628 WHEN OTHERS THEN
1629 p_err_code := SQLCODE;
1630 RAISE;
1631
1632 END Create_Resource_txn_Attribute ;
1633
1634 --Name: Create_Resource_list_member
1635 --Type: Procedure
1636 --Description: This procedure creates resource lists...
1637 --
1638 --Called subprograms: ?
1639 --
1640 --History:
1641 -- xx-xxx-xxxx rkrishna Created
1642 --
1643 -- 16-MAR-2001 jwhite Bug 1685015: Forecast/Bgt Integration
1644 -- 1. New IN-parameter, p_project_role_id, required.
1645 -- 2. New p_resource_type_code validation and
1646 -- new error message.
1647 --
1648 -- 28-JAN-2003 sacgupta Bug 2486405. Resource List Enhancement.
1649 -- 1. new IN parameter p_job_group_id
1650 --
1651 --
1652
1653 PROCEDURE Create_Resource_list_member
1654 (p_resource_list_id IN NUMBER,
1655 p_resource_name IN VARCHAR2,
1656 p_resource_type_Code IN VARCHAR2,
1657 p_alias IN VARCHAR2,
1658 p_sort_order IN NUMBER,
1659 p_display_flag IN VARCHAR2,
1660 p_enabled_flag IN VARCHAR2,
1661 p_person_id IN NUMBER,
1665 p_expenditure_type IN VARCHAR2,
1662 p_job_id IN NUMBER,
1663 p_proj_organization_id IN NUMBER,
1664 p_vendor_id IN NUMBER,
1666 p_event_type IN VARCHAR2,
1667 p_expenditure_category IN VARCHAR2,
1668 p_revenue_category_code IN VARCHAR2,
1669 p_non_labor_resource IN VARCHAR2,
1670 p_system_linkage IN VARCHAR2,
1671 p_project_role_id IN NUMBER,
1672 p_job_group_id IN NUMBER, --- Added for Bug 2486405.
1673 p_parent_member_id OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
1674 p_resource_list_member_id OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
1675 p_track_as_labor_flag OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1676 p_err_code OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
1677 p_err_stage IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1678 p_err_stack IN OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
1679 IS
1680
1681 l_err_code NUMBER := 0;
1682 l_old_stack VARCHAR2(2000);
1683 l_grouped_resource_type_id NUMBER;
1684 l_grouped_resource_type_code VARCHAR2(30);
1685 l_resource_id NUMBER;
1686 l_resource_list_member_id NUMBER;
1687 l_track_as_labor_flag VARCHAR2(1);
1688 l_group_res_list_member_id NUMBER;
1689 l_group_track_as_labor_flag VARCHAR2(1);
1690 l_resource_group VARCHAR2(80);
1691 l_revenue_category_code VARCHAR2(80);
1692 l_resource_group_name VARCHAR2(80);
1693 l_org_id NUMBER := NULL;
1694 l_job_exist VARCHAR2(1); -- Added for bug 2486405.
1695
1696 CURSOR c_resource_lists_csr IS
1697 SELECT
1698 group_resource_type_id
1699 FROM
1700 pa_resource_lists_all_bg
1701 WHERE resource_list_id = p_resource_list_id;
1702
1703 CURSOR c_resource_types_csr IS
1704 SELECT
1705 resource_type_code
1706 FROM
1707 pa_resource_types_active_v
1708 WHERE resource_type_id = l_grouped_resource_type_id;
1709
1710 CURSOR c_rev_category_csr IS -- changed for perf bug 4887375
1711 /*SELECT
1712 description
1713 FROM
1714 pa_revenue_categories_res_v
1715 WHERE
1716 revenue_category_code = l_revenue_category_code;*/
1717 SELECT
1718 tmp.description
1719 FROM (
1720 SELECT
1721 REVENUE_CATEGORY_CODE
1722 ,REVENUE_CATEGORY_M description
1723 FROM PA_REVENUE_CATEGORIES_V RC
1724 WHERE DECODE(PA_GET_RESOURCE.INCLUDE_INACTIVE_RESOURCES, 'Y', START_DATE_ACTIVE,TRUNC(SYSDATE)) BETWEEN START_DATE_ACTIVE AND NVL(END_DATE_ACTIVE,TRUNC(SYSDATE))
1725 ) tmp
1726 WHERE
1727 tmp.revenue_category_code = l_revenue_category_code;
1728
1729 CURSOR c_org_csr IS
1730 SELECT
1731 organization_name
1732 FROM
1733 pa_organizations_res_v
1734 WHERE
1735 organization_id = l_org_id ;
1736
1737 -- Added for Bug 2486405.
1738 CURSOR c_job_csr IS
1739 SELECT
1740 'X'
1741 FROM
1742 pa_jobs_v
1743 WHERE job_group_id = p_job_group_id
1744 AND job_id = p_job_id;
1745
1746 BEGIN
1747 l_old_stack := p_err_stack;
1748 p_err_code := 0;
1749 p_err_stack :=
1750 p_err_stack ||'->PA_CREATE_RESOURCE.Create_Resource_list_member';
1751
1752 -- Based on the Resource_type_code Ensure that the corresponding
1753 -- attribute has a valid value.
1754
1755 IF (p_resource_type_code = 'EMPLOYEE' AND
1756 p_person_id IS NULL) THEN
1757 p_err_code := 10;
1758 p_err_stage := 'PA_NO_PERSON_ID';
1759 RETURN;
1760 ----------- Changes done for Bug 2486405----------------------
1761 /* ELSIF (p_resource_type_code = 'JOB' AND
1762 p_job_id IS NULL) THEN
1763 p_err_code := 10;
1764 p_err_stage := 'PA_NO_JOB_ID';
1765 RETURN; */
1766 ELSIF p_resource_type_code = 'JOB' THEN
1767 IF ( p_job_group_id IS NULL OR
1768 p_job_group_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM) THEN
1769 p_err_code := 10;
1770 p_err_stage := 'PA_NO_JOB_GROUP_ID'; -- New Error -> A valid job Group id is required.
1771 RETURN;
1772 ELSIF
1773 p_job_id IS NULL THEN
1774 p_err_code := 10;
1775 p_err_stage := 'PA_NO_JOB_ID';
1776 RETURN;
1777 ELSE
1778 OPEN c_job_csr;
1779 FETCH c_job_csr INTO l_job_exist;
1780 IF c_job_csr%NOTFOUND THEN
1781 p_err_code := 11;
1782 p_err_stage := 'PA_INVALID_JOB_RELATION';
1783 CLOSE c_job_csr;
1784 RETURN;
1785 ELSE
1786 CLOSE c_job_csr;
1787 END IF;
1788 END IF;
1789 ----------------Bug 2486405-----------------------------------
1790 ELSIF (p_resource_type_code = 'ORGANIZATION' AND
1794 RETURN;
1791 p_proj_organization_id IS NULL) THEN
1792 p_err_code := 10;
1793 p_err_stage := 'PA_NO_PROJ_ORG_ID';
1795 ELSIF (p_resource_type_code = 'VENDOR' AND
1796 p_vendor_id IS NULL) THEN
1797 p_err_code := 10;
1798 p_err_stage := 'PA_NO_VENDOR_ID';
1799 RETURN;
1800 ELSIF (p_resource_type_code = 'EXPENDITURE_TYPE' AND
1801 p_expenditure_type IS NULL) THEN
1802 p_err_code := 10;
1803 p_err_stage := 'PA_NO_EXPENDITURE_TYPE';
1804 RETURN;
1805 ELSIF (p_resource_type_code = 'EVENT_TYPE' AND
1806 p_event_type IS NULL) THEN
1807 p_err_code := 10;
1808 p_err_stage := 'PA_NO_EVENT_TYPE';
1809 RETURN;
1810 ELSIF (p_resource_type_code = 'EXPENDITURE_CATEGORY' AND
1811 p_expenditure_category IS NULL) THEN
1812 p_err_code := 10;
1813 p_err_stage := 'PA_NO_EXPENDITURE_CATEGORY';
1814 RETURN;
1815 ELSIF (p_resource_type_code = 'REVENUE_CATEGORY' AND
1816 p_revenue_category_code IS NULL) THEN
1817 p_err_code := 10;
1818 p_err_stage := 'PA_NO_REVENUE_CATEGORY';
1819 RETURN;
1820 ELSIF (p_resource_type_code = 'PROJECT_ROLE' AND
1821 p_project_role_id IS NULL) THEN
1822 p_err_code := 10;
1823 p_err_stage := 'PA_NO_PROJECT_ROLE_ID';
1824 RETURN;
1825 END IF;
1826 p_err_stage := 'Select group_resource_type_id from pa_resource_lists ';
1827 OPEN c_resource_lists_csr;
1828 FETCH c_resource_lists_csr INTO
1829 l_grouped_resource_type_id;
1830 IF c_resource_lists_csr%NOTFOUND THEN
1831 p_err_code := 11;
1832 p_err_stage := 'PA_RL_INVALID';
1833 CLOSE c_resource_lists_csr;
1834 RETURN;
1835 ELSE
1836 CLOSE c_resource_lists_csr;
1837 END IF;
1838 IF l_grouped_resource_type_id <> 0 THEN
1839 -- the resource list has been grouped
1840 -- get the resource_type_code into grouped_by_resource_type_code
1841 -- from pa_resource_types using group_resource_type_id
1842
1843 p_err_stage := 'Select resource_type_code from pa_resource_types ';
1844 OPEN c_resource_types_csr;
1845 FETCH c_resource_types_csr INTO
1846 l_grouped_resource_type_code;
1847 IF c_resource_types_csr%NOTFOUND THEN
1848 p_err_code := 12;
1849 p_err_stage := 'PA_GROUPED_RT_INVALID';
1850 CLOSE c_resource_types_csr;
1851 RETURN;
1852 ELSE
1853 CLOSE c_resource_types_csr;
1854 END IF;
1855 END IF;
1856 IF l_grouped_resource_type_id = 0 THEN
1857 -- since the resource list is not grouped,need only to return
1858 -- the resource_list_member_id of the input resource.Hence,first
1859 -- call Get_Resource_list_member to get the resource list member id.
1860
1861 PA_GET_RESOURCE.Get_Resource_list_member
1862 (p_resource_list_id => p_resource_list_id,
1863 p_resource_name => p_resource_name,
1864 p_resource_type_Code => p_resource_type_code,
1865 p_group_resource_type_id => l_grouped_resource_type_id,
1866 p_person_id => p_person_id,
1867 p_job_id => p_job_id,
1868 p_proj_organization_id => p_proj_organization_id,
1869 p_vendor_id => p_vendor_id,
1870 p_expenditure_type => p_expenditure_type,
1871 p_event_type => p_event_type,
1872 p_expenditure_category => p_expenditure_category,
1873 p_revenue_category_code => p_revenue_category_code,
1874 p_non_labor_resource => p_non_labor_resource,
1875 p_system_linkage => p_system_linkage,
1876 p_parent_member_id => NULL,
1877 p_project_role_id => p_project_role_id,
1878 p_resource_id => l_resource_id,
1879 p_resource_list_member_id => l_resource_list_member_id,
1880 p_track_as_labor_flag => l_track_as_labor_flag,
1881 p_err_code => l_err_code,
1882 p_err_stage => p_err_stage,
1883 p_err_stack => p_err_stack);
1884
1885 IF l_err_code <> 0 THEN
1886 p_err_code := l_err_code;
1887 RETURN;
1888 END IF;
1889
1890 IF l_resource_list_member_id IS NOT NULL THEN
1891 -- This means the resource has already been created as a
1892 -- resource list member. Hence, return the appropriate
1893 -- values
1894 p_resource_list_member_id := l_resource_list_member_id;
1895 p_track_as_labor_flag := l_track_as_labor_flag;
1896 p_err_stack := l_old_stack;
1897 RETURN;
1898 ELSE
1899 -- If the resource_list_member_id returned by
1900 -- Get_Resource_list_member is null
1901 -- then need to create the member;
1902 -- Hence call Add_resource_list_member
1903 Add_Resouce_List_Member (
1904 p_resource_list_id => p_resource_list_id,
1905 p_resource_name => p_resource_name,
1909 p_display_flag => p_display_flag,
1906 p_resource_type_Code => p_resource_type_Code,
1907 p_alias => p_alias,
1908 p_sort_order => p_sort_order,
1910 p_enabled_flag => p_enabled_flag,
1911 p_person_id => p_person_id,
1912 p_job_id => p_job_id,
1913 p_proj_organization_id => p_proj_organization_id,
1914 p_vendor_id => p_vendor_id,
1915 p_expenditure_type => p_expenditure_type,
1916 p_event_type => p_event_type,
1917 p_expenditure_category => p_expenditure_category,
1918 p_revenue_category_code => p_revenue_category_code,
1919 p_non_labor_resource => p_non_labor_resource,
1920 p_system_linkage => p_system_linkage,
1921 p_parent_member_id => NULL,
1922 p_project_role_id => p_project_role_id,
1923 p_track_as_labor_flag => l_track_as_labor_flag,
1924 p_resource_id => l_resource_id,
1925 p_resource_list_member_id => l_resource_list_member_id,
1926 p_err_code => l_err_code,
1927 p_err_stage => p_err_stage,
1928 p_err_stack => p_err_stack );
1929
1930 IF l_err_code <> 0 THEN
1931 p_err_code := l_err_code;
1932 RETURN;
1933 END IF;
1934 p_resource_list_member_id := l_resource_list_member_id;
1935 p_track_as_labor_flag := l_track_as_labor_flag;
1936 p_err_stack := l_old_stack;
1937 RETURN;
1938 END IF; --- IF l_resource_list_member_id IS NOT NULL
1939
1940 ELSE -- (IF l_grouped_resource_type_id is not 0 )
1941 -- If the resource list had been grouped
1942 -- the grouped_resource_type_code would determine how the
1943 -- resource_list had been grouped by . Need to check whether
1944 -- we have the right inputs
1945
1946 IF (l_grouped_resource_type_code = 'EXPENDITURE_CATEGORY'
1947 AND p_expenditure_category IS NULL) THEN
1948 p_err_code := 13;
1949 p_err_stage := 'PA_EXP_CATEG_REQD';
1950 RETURN;
1951 ELSIF (l_grouped_resource_type_code = 'REVENUE_CATEGORY'
1952 AND p_revenue_category_code IS NULL ) THEN
1953 p_err_code := 13;
1954 p_err_stage := 'PA_REV_CATEG_REQD';
1955 RETURN;
1956 ELSIF (l_grouped_resource_type_code = 'ORGANIZATION'
1957 AND p_proj_organization_id IS NULL ) THEN
1958 p_err_code := 13;
1959 p_err_stage := 'PA_ORG_ID_REQD';
1960 RETURN;
1961
1962 END IF;
1963 IF l_grouped_resource_type_code = 'EXPENDITURE_CATEGORY' THEN
1964 l_resource_group := p_expenditure_category;
1965 ELSIF
1966 l_grouped_resource_type_code = 'REVENUE_CATEGORY' THEN
1967 l_resource_group := p_revenue_category_code;
1968 ELSIF
1969 l_grouped_resource_type_code = 'ORGANIZATION' THEN
1970 l_resource_group := TO_CHAR(p_proj_organization_id);
1971 END IF;
1972
1973 -- If l_grouped_resource_type_code = input p_resource_type_code
1974 -- this means this is a resource group. In that case, we
1975 -- need to return the resource_list_member_id and
1976 -- track_as_labor_flag.Parent_member_id would be null in this
1977 -- case. It is possible that the resource group has already
1978 -- been created. Hence we call Get_resource_group first
1979 IF l_grouped_resource_type_code = p_resource_type_code THEN
1980 PA_GET_RESOURCE.Get_Resource_group
1981 (p_resource_list_id => p_resource_list_id,
1982 p_resource_group => l_resource_group,
1983 p_resource_list_member_id => l_group_res_list_member_id,
1984 p_resource_id => l_resource_id,
1985 p_track_as_labor_flag => l_group_track_as_labor_flag,
1986 p_err_code => l_err_code,
1987 p_err_stage => p_err_stage,
1988 p_err_stack => p_err_stack );
1989 IF l_err_code <> 0 THEN
1990 p_err_code := l_err_code;
1991 RETURN;
1992 END IF;
1993 IF l_group_res_list_member_id IS NOT NULL THEN
1994 p_resource_list_member_id := l_group_res_list_member_id;
1995 p_track_as_labor_flag := l_group_track_as_labor_flag;
1996 p_err_stack := l_old_stack;
1997 RETURN;
1998 ELSE -- need to create the resource_group
1999 Create_Resource_group (
2000 p_resource_list_id => p_resource_list_id,
2001 p_resource_group => l_resource_group,
2002 p_resource_name => p_resource_name,
2003 p_alias => p_alias,
2007 p_track_as_labor_flag => l_group_track_as_labor_flag,
2004 p_sort_order => p_sort_order,
2005 p_display_flag => p_display_flag,
2006 p_enabled_flag => p_enabled_flag,
2008 p_resource_id => l_resource_id,
2009 p_resource_list_member_id => l_group_res_list_member_id,
2010 p_err_code => l_err_code,
2011 p_err_stage => p_err_stage,
2012 p_err_stack => p_err_stack );
2013 IF l_err_code <> 0 THEN
2014 p_err_code := l_err_code;
2015 RETURN;
2016 END IF;
2017 p_resource_list_member_id := l_group_res_list_member_id;
2018 p_track_as_labor_flag := l_group_track_as_labor_flag;
2019 p_err_stack := l_old_stack;
2020 RETURN;
2021 END IF;
2022 ELSE --i.e l_grouped_resource_type_code is <> p_resource_type_code
2023 --this means we need to return the resource_list_member_id,
2024 -- parent_member_id (resource_list_member_id of the
2025 -- resource_group) and the track_as_labor_flag of the
2026 -- child_resource
2027 -- These are the possibilities
2028 -- a) The resource group itself has not yet been created hence
2029 -- need to create the resource group as well as the
2030 -- child resource
2031 -- b) The resource_group has been created , hence need to create
2032 -- only the child_resource
2033 -- c) Both the resource_group and child_resource have been created
2034 -- , hence need to just return the information pertaining to
2035 -- the child_resource
2036 --Hence,
2037 -- to determine whether the child resource has been created
2038 -- we need the parent_member_id (the resource_list_member_id of
2039 -- the resource_group);So,call Get_resource_group first
2040 PA_GET_RESOURCE.Get_Resource_group
2041 (p_resource_list_id => p_resource_list_id,
2042 p_resource_group => l_resource_group,
2043 p_resource_list_member_id => l_group_res_list_member_id,
2044 p_resource_id => l_resource_id,
2045 p_track_as_labor_flag => l_group_track_as_labor_flag,
2046 p_err_code => l_err_code,
2047 p_err_stage => p_err_stage,
2048 p_err_stack => p_err_stack );
2049 --- This would return the resource_list_member_id of
2050 --- the resource group (Expenditure or revenue category)
2051 --- IF the resource_list_member_id returned by
2052 --- Get_Resource_Group
2053 --- is null then the resource_group as well as
2054 --- the child resource need to be created.Hence,
2055 --- call Create_Resource_group first
2056
2057
2058 IF l_group_res_list_member_id IS NULL THEN
2059 IF l_grouped_resource_type_code = 'REVENUE_CATEGORY' THEN
2060 --- If creating Revenue_category as a group, then need the
2061 --- revenue_category name ,since what is available is
2062 --- the revenue_category_code.Hence,Get the
2063 --- revenue_category_name from pa_revenue_categories_res_v
2064 --- end if;
2065 l_revenue_category_code := l_resource_group ;
2066 OPEN c_rev_category_csr;
2067 FETCH c_rev_category_csr INTO
2068 l_resource_group_name;
2069 IF c_rev_category_csr%NOTFOUND THEN
2070 p_err_code := 14;
2071 p_err_stage := 'PA_INVALID_REV_CATEG';
2072 CLOSE c_rev_category_csr;
2073 RETURN;
2074 ELSE
2075 CLOSE c_rev_category_csr;
2076 END IF;
2077 ELSIF l_grouped_resource_type_code =
2078 'EXPENDITURE_CATEGORY' THEN
2079 l_resource_group_name := l_resource_group;
2080 ELSIF l_grouped_resource_type_code = 'ORGANIZATION' THEN
2081 l_org_id := p_proj_organization_id;
2082 p_err_stage :=
2083 ' Select organization_name from pa_organizations_res_v';
2084 -- Need to get the organization_name since what is passed
2085 -- is the organization id
2086 OPEN c_org_csr;
2087 FETCH c_org_csr INTO l_resource_group_name;
2088 IF c_org_csr%NOTFOUND THEN
2089 p_err_code := 14;
2090 p_err_stage := 'PA_INVALID_ORGANIZATION';
2091 CLOSE c_org_csr;
2092 RETURN;
2093 ELSE
2094 CLOSE c_org_csr;
2095 END IF;
2096 END IF;
2097 Create_Resource_group (
2098 p_resource_list_id =>p_resource_list_id,
2102 1,30),
2099 p_resource_group =>l_resource_group,
2100 p_resource_name =>l_resource_group_name,
2101 p_alias =>SUBSTR(l_resource_group_name,
2103 p_sort_order =>NULL,
2104 p_display_flag =>'Y',
2105 p_enabled_flag =>'Y',
2106 p_track_as_labor_flag =>l_group_track_as_labor_flag,
2107 p_resource_id =>l_resource_id,
2108 p_resource_list_member_id =>l_group_res_list_member_id,
2109 p_err_code =>l_err_code,
2110 p_err_stage =>p_err_stage,
2111 p_err_stack =>p_err_stack );
2112 IF l_err_code <> 0 THEN
2113 p_err_code := l_err_code;
2114 RETURN;
2115 END IF;
2116 ---Now create the child resource,by calling
2117 ---add_resource_list_member
2118 Add_Resouce_List_Member (
2119 p_resource_list_id => p_resource_list_id,
2120 p_resource_name => p_resource_name,
2121 p_resource_type_Code => p_resource_type_Code,
2122 p_alias => p_alias,
2123 p_sort_order => p_sort_order,
2124 p_display_flag => p_display_flag,
2125 p_enabled_flag => p_enabled_flag,
2126 p_person_id => p_person_id,
2127 p_job_id => p_job_id,
2128 p_proj_organization_id => p_proj_organization_id,
2129 p_vendor_id => p_vendor_id,
2130 p_expenditure_type => p_expenditure_type,
2131 p_event_type => p_event_type,
2132 p_expenditure_category => p_expenditure_category,
2133 p_revenue_category_code => p_revenue_category_code,
2134 p_non_labor_resource => p_non_labor_resource,
2135 p_system_linkage => p_system_linkage,
2136 p_parent_member_id => l_group_res_list_member_id,
2137 p_project_role_id => p_project_role_id,
2138 p_track_as_labor_flag => l_track_as_labor_flag,
2139 p_resource_id => l_resource_id,
2140 p_resource_list_member_id => l_resource_list_member_id,
2141 p_err_code => l_err_code,
2142 p_err_stage => p_err_stage,
2143 p_err_stack => p_err_stack );
2144 IF l_err_code <> 0 THEN
2145 p_err_code := l_err_code;
2146 RETURN;
2147 END IF;
2148 p_parent_member_id := l_group_res_list_member_id;
2149 p_resource_list_member_id := l_resource_list_member_id;
2150 p_track_as_labor_flag := l_track_as_labor_flag;
2151 p_err_stack := l_old_stack;
2152 RETURN;
2153 ELSE -- If resource_list_member_id returned by
2154 -- Get_Resource_Group is not null then
2155 -- call Get_Resource_list_member to get
2156 -- the resource list member
2157 PA_GET_RESOURCE.Get_Resource_list_member
2158 (p_resource_list_id => p_resource_list_id,
2159 p_resource_name => p_resource_name,
2160 p_resource_type_Code => p_resource_type_code,
2161 p_group_resource_type_id => l_grouped_resource_type_id,
2162 p_person_id => p_person_id,
2163 p_job_id => p_job_id,
2164 p_proj_organization_id => p_proj_organization_id,
2165 p_vendor_id => p_vendor_id,
2166 p_expenditure_type => p_expenditure_type,
2167 p_event_type => p_event_type,
2168 p_expenditure_category => p_expenditure_category,
2169 p_revenue_category_code => p_revenue_category_code,
2170 p_non_labor_resource => p_non_labor_resource,
2171 p_system_linkage => p_system_linkage,
2172 p_parent_member_id => l_group_res_list_member_id,
2173 p_project_role_id => p_project_role_id,
2174 p_resource_id => l_resource_id,
2175 p_resource_list_member_id => l_resource_list_member_id,
2176 p_track_as_labor_flag => l_track_as_labor_flag,
2177 p_err_code => l_err_code,
2178 p_err_stage => p_err_stage,
2179 p_err_stack => p_err_stack);
2180
2181 IF l_err_code <> 0 THEN
2182 p_err_code := l_err_code;
2183 RETURN;
2184 END IF;
2185
2186 IF l_resource_list_member_id IS NOT NULL THEN
2190 p_parent_member_id := l_group_res_list_member_id;
2187 -- This means the resource has already been created as a
2188 -- resource list member. Hence, return the appropriate
2189 -- values
2191 p_resource_list_member_id := l_resource_list_member_id;
2192 p_track_as_labor_flag := l_track_as_labor_flag;
2193 p_err_stack := l_old_stack;
2194 RETURN;
2195 ELSE -- If the resource_list_member_id returned by
2196 -- Get_Resource_list_member is null
2197 -- then need to create the member;
2198 -- Hence call Add_resource_list_member
2199
2200 Add_Resouce_List_Member (
2201 p_resource_list_id => p_resource_list_id,
2202 p_resource_name => p_resource_name,
2203 p_resource_type_Code => p_resource_type_Code,
2204 p_alias => p_alias,
2205 p_sort_order => p_sort_order,
2206 p_display_flag => p_display_flag,
2207 p_enabled_flag => p_enabled_flag,
2208 p_person_id => p_person_id,
2209 p_job_id => p_job_id,
2210 p_proj_organization_id => p_proj_organization_id,
2211 p_vendor_id => p_vendor_id,
2212 p_expenditure_type => p_expenditure_type,
2213 p_event_type => p_event_type,
2214 p_expenditure_category => p_expenditure_category,
2215 p_revenue_category_code => p_revenue_category_code,
2216 p_non_labor_resource => p_non_labor_resource,
2217 p_system_linkage => p_system_linkage,
2218 p_parent_member_id => l_group_res_list_member_id,
2219 p_project_role_id => p_project_role_id,
2220 p_track_as_labor_flag => l_track_as_labor_flag,
2221 p_resource_id => l_resource_id,
2222 p_resource_list_member_id => l_resource_list_member_id,
2223 p_err_code => l_err_code,
2224 p_err_stage => p_err_stage,
2225 p_err_stack => p_err_stack );
2226 IF l_err_code <> 0 THEN
2227 p_err_code := l_err_code;
2228 RETURN;
2229 END IF;
2230 p_parent_member_id := l_group_res_list_member_id;
2231 p_resource_list_member_id := l_resource_list_member_id;
2232 p_track_as_labor_flag := l_track_as_labor_flag;
2233 p_err_stack := l_old_stack;
2234 RETURN;
2235 END IF;--end if for l_resource_list_member_id is not null
2236 END IF; -- end if for l_group_res_list_member_id IS NULL
2237 END IF;--end if for l_grouped_resource_type_code = p_resource_type_code
2238 END IF; -- end if for l_grouped_resource_type_id = 0
2239
2240 p_err_stack := l_old_stack;
2241
2242 EXCEPTION
2243 WHEN VALUE_ERROR THEN
2244 p_err_code := SQLCODE;
2245 RAISE;
2246 WHEN OTHERS THEN
2247 p_err_code := SQLCODE;
2248 RAISE;
2249 END Create_Resource_list_member;
2250
2251 PROCEDURE Create_Resource (p_resource_name IN VARCHAR2,
2252 p_resource_type_Code IN VARCHAR2,
2253 p_description IN VARCHAR2,
2254 p_unit_of_measure IN VARCHAR2,
2255 p_rollup_quantity_flag IN VARCHAR2,
2256 p_track_as_labor_flag IN VARCHAR2,
2257 p_start_date IN DATE,
2258 p_end_date IN DATE,
2259 p_person_id IN NUMBER,
2260 p_job_id IN NUMBER,
2261 p_proj_organization_id IN NUMBER,
2262 p_vendor_id IN NUMBER,
2263 p_expenditure_type IN VARCHAR2,
2264 p_event_type IN VARCHAR2,
2265 p_expenditure_category IN VARCHAR2,
2266 p_revenue_category_code IN VARCHAR2,
2267 p_non_labor_resource IN VARCHAR2,
2268 p_system_linkage IN VARCHAR2,
2269 p_project_role_id IN NUMBER,
2270 p_resource_id OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
2271 p_err_code OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
2272 p_err_stage IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2273 p_err_stack IN OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
2277 SELECT
2274 IS
2275
2276 CURSOR c_resource_types_csr IS
2278 resource_type_id
2279 FROM
2280 pa_resource_types_active_v
2281 WHERE resource_type_code = p_resource_type_code;
2282
2283 CURSOR c_resource_seq_csr IS
2284 SELECT
2285 pa_resources_s.NEXTVAL
2286 FROM
2287 SYS.DUAL;
2288
2289
2290 l_err_code NUMBER := 0;
2291 l_old_stack VARCHAR2(2000);
2292 l_resource_type_id NUMBER;
2293 l_resource_id NUMBER;
2294 l_resource_txn_attribute_id NUMBER;
2295 BEGIN
2296
2297 l_old_stack := p_err_stack;
2298 p_err_code := 0;
2299 p_err_stack := p_err_stack ||'->PA_CREATE_RESOURCE.Create_Resource';
2300
2301 IF p_resource_type_code IS NULL THEN
2302 p_err_code := 10;
2303 p_err_stage := 'PA_RL_RES_TYPE_CODE_REQD';
2304 RETURN;
2305 END IF;
2306
2307 p_err_stage := 'Select resource_type_id from pa_resource_types_active_v ';
2308
2309 OPEN c_resource_types_csr;
2310 FETCH c_resource_types_csr INTO
2311 l_resource_type_id;
2312 IF c_resource_types_csr%NOTFOUND THEN
2313 p_err_code := 11;
2314 p_err_stage := 'PA_RT_INVALID';
2315 CLOSE c_resource_types_csr;
2316 RETURN;
2317 END IF;
2318 CLOSE c_resource_types_csr;
2319
2320 IF (p_resource_type_code = 'EMPLOYEE' AND
2321 p_person_id IS NULL) THEN
2322 p_err_code := 12;
2323 p_err_stage := 'PA_NO_PERSON_ID';
2324 RETURN;
2325 ELSIF (p_resource_type_code = 'JOB' AND
2326 p_job_id IS NULL) THEN
2327 p_err_code := 12;
2328 p_err_stage := 'PA_NO_JOB_ID';
2329 RETURN;
2330 ELSIF (p_resource_type_code = 'ORGANIZATION' AND
2331 p_proj_organization_id IS NULL) THEN
2332 p_err_code := 12;
2333 p_err_stage := 'PA_NO_PROJ_ORG_ID';
2334 RETURN;
2335 ELSIF (p_resource_type_code = 'VENDOR' AND
2336 p_vendor_id IS NULL) THEN
2337 p_err_code := 12;
2338 p_err_stage := 'PA_NO_VENDOR_ID';
2339 RETURN;
2340 ELSIF (p_resource_type_code = 'EXPENDITURE_TYPE' AND
2341 p_expenditure_type IS NULL) THEN
2342 p_err_code := 12;
2343 p_err_stage := 'PA_NO_EXPENDITURE_TYPE';
2344 RETURN;
2345 ELSIF (p_resource_type_code = 'EVENT_TYPE' AND
2346 p_event_type IS NULL) THEN
2347 p_err_code := 12;
2348 p_err_stage := 'PA_NO_EVENT_TYPE';
2349 RETURN;
2350 ELSIF (p_resource_type_code = 'EXPENDITURE_CATEGORY' AND
2351 p_expenditure_category IS NULL) THEN
2352 p_err_code := 12;
2353 p_err_stage := 'PA_NO_EXPENDITURE_CATEGORY';
2354 RETURN;
2355 ELSIF (p_resource_type_code = 'REVENUE_CATEGORY' AND
2356 p_revenue_category_code IS NULL) THEN
2357 p_err_code := 12;
2358 p_err_stage := 'PA_NO_REVENUE_CATEGORY';
2359 RETURN;
2360 ELSIF (p_resource_type_code = 'PROJECT_ROLE' AND
2361 p_project_role_id IS NULL) THEN
2362 p_err_code := 12;
2363 p_err_stage := 'PA_NO_PROJECT_ROLE_ID';
2364 RETURN;
2365 END IF;
2366
2367 OPEN c_resource_seq_csr;
2368 FETCH c_resource_seq_csr INTO
2369 l_resource_id;
2370 CLOSE c_resource_seq_csr;
2371
2372 p_err_stage := 'Insert into pa_resources ';
2373
2374 INSERT INTO pa_resources
2375 (resource_id,
2376 name,
2377 description,
2378 resource_type_id,
2379 unit_of_measure,
2380 rollup_quantity_flag,
2381 track_as_labor_flag,
2382 start_date_active,
2383 end_date_active,
2384 last_updated_by,
2385 last_update_date,
2386 creation_date,
2387 created_by,
2388 last_update_login )
2389 VALUES
2390 (l_resource_id,
2391 p_resource_name,
2392 p_description,
2393 l_resource_type_id,
2394 p_unit_of_measure,
2395 p_rollup_quantity_flag,
2396 p_track_as_labor_flag,
2397 NVL(p_start_date,SYSDATE),
2398 p_end_date,
2399 g_last_updated_by,
2400 g_last_update_date,
2401 g_creation_date,
2402 g_created_by,
2403 g_last_update_login );
2404 -- Need to create resource txn attributes
2405
2406 Create_Resource_txn_Attribute
2407 (p_resource_id => l_resource_id,
2408 p_resource_type_Code => p_resource_type_code,
2409 p_person_id => p_person_id,
2410 p_job_id => p_job_id,
2411 p_proj_organization_id => p_proj_organization_id,
2412 p_vendor_id => p_vendor_id,
2413 p_expenditure_type => p_expenditure_type,
2414 p_event_type => p_event_type,
2415 p_expenditure_category => p_expenditure_category,
2416 p_revenue_category_code => p_revenue_category_code,
2417 p_non_labor_resource => p_non_labor_resource,
2418 p_system_linkage => p_system_linkage,
2419 p_project_role_id => p_project_role_id,
2423 p_err_stack => p_err_stack);
2420 p_resource_txn_attribute_id => l_resource_txn_attribute_id,
2421 p_err_code => l_err_code,
2422 p_err_stage => p_err_stage,
2424
2425 IF l_err_code <> 0 THEN
2426 p_err_code := l_err_code;
2427 RETURN;
2428 END IF;
2429 p_resource_id := l_resource_id;
2430 p_err_stack := l_old_stack;
2431
2432 EXCEPTION
2433 WHEN OTHERS THEN
2434 p_err_code := SQLCODE;
2435 RAISE;
2436
2437 END ;
2438
2439 --Name: Add_Resouce_list_member (sic)
2440 --Type: Procedure
2441 --Description: This procedure creates a resource list member...
2442 --
2443 --Called subprograms: ?
2444 --
2445 --History:
2446 -- xx-xxx-xxxx rkrishna Created
2447 --
2448 -- 16-MAR-2001 jwhite Bug 1685015: Forecast/Bgt Integration
2449 -- 1. New IN-parameter, p_project_role_id, required.
2450 -- 2. New p_resource_type_code assigment
2451 --
2452
2453 PROCEDURE Add_Resouce_List_Member
2454 (p_resource_list_id IN NUMBER,
2455 p_resource_name IN VARCHAR2,
2456 p_resource_type_Code IN VARCHAR2,
2457 p_alias IN VARCHAR2,
2458 p_sort_order IN NUMBER,
2459 p_display_flag IN VARCHAR2,
2460 p_enabled_flag IN VARCHAR2,
2461 p_person_id IN NUMBER,
2462 p_job_id IN NUMBER,
2463 p_proj_organization_id IN NUMBER,
2464 p_vendor_id IN NUMBER,
2465 p_expenditure_type IN VARCHAR2,
2466 p_event_type IN VARCHAR2,
2467 p_expenditure_category IN VARCHAR2,
2468 p_revenue_category_code IN VARCHAR2,
2469 p_non_labor_resource IN VARCHAR2,
2470 p_system_linkage IN VARCHAR2,
2471 p_parent_member_id IN NUMBER,
2472 p_project_role_id IN NUMBER,
2473 p_track_as_labor_flag OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2474 p_resource_id OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
2475 p_resource_list_member_id OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
2476 p_err_code OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
2477 p_err_stage IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2478 p_err_stack IN OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
2479 IS
2480 l_err_code NUMBER := 0;
2481 l_sort_order NUMBER := 0;
2482 l_resource_id NUMBER := 0;
2483 l_resource_list_member_id NUMBER := 0;
2484 l_old_stack VARCHAR2(2000);
2485 l_grouped_resource_type_id NUMBER := 0;
2486 l_grouped_res_type_code VARCHAR2(80);
2487 l_attr_value VARCHAR2(80);
2488 l_exp_category VARCHAR2(80);
2489 l_exp_type VARCHAR2(80);
2490 l_revenue_category_code VARCHAR2(80);
2491 l_parent_track_as_labor_flag VARCHAR2(1);
2492 l_track_as_labor_flag VARCHAR2(1);
2493 l_dummy VARCHAR2(1);
2494 l_Uom VARCHAR2(30);
2495 l_rollup_qty_flag VARCHAR2(1);
2496 l_get_new_sort_order VARCHAR2(10) := 'FALSE';
2497 l_resource_type_code VARCHAR2(30);
2498 l_resource_name VARCHAR2(80);
2499 l_alias VARCHAR2(30);
2500 l_new_track_as_labor_flag VARCHAR2(1);
2501 l_org_id NUMBER := NULL;
2502 l_resource_type_id pa_resource_types.resource_type_id%TYPE;
2503 l_person_id pa_resource_txn_attributes.person_id%TYPE;
2504 l_job_id pa_resource_txn_attributes.job_id%TYPE;
2505 l_organization_id pa_resource_txn_attributes.organization_id%TYPE;
2506 l_vendor_id pa_resource_txn_attributes.vendor_id%TYPE;
2507 l_project_role_id pa_resource_txn_attributes.project_role_id%TYPE;
2508 l_expenditure_type pa_resource_txn_attributes.expenditure_type%TYPE;
2509 l_event_type pa_resource_txn_attributes.event_type%TYPE;
2510 l_expenditure_category pa_resource_txn_attributes.expenditure_category%TYPE;
2511 l_revenue_category pa_resource_txn_attributes.revenue_category%TYPE;
2512 l_nlr_resource pa_resource_txn_attributes.non_labor_resource%TYPE;
2513 l_nlr_res_org_id pa_resource_txn_attributes.non_labor_resource_org_id%TYPE;
2514 l_event_type_cls pa_resource_txn_attributes.event_type_classification%TYPE;
2515 l_system_link_function pa_resource_txn_attributes.system_linkage_function%TYPE;
2516 l_resource_format_id pa_resource_txn_attributes.resource_format_id%TYPE;
2517 l_res_type_code pa_resource_types.resource_type_code%TYPE;
2518
2519
2523 FROM
2520 CURSOR c_res_list_csr IS
2521 SELECT
2522 group_resource_type_id
2524 pa_resource_lists_all_bg
2525 WHERE resource_list_id = p_resource_list_id;
2526
2527 CURSOR c_resource_types_csr IS
2528 SELECT
2529 resource_type_code
2530 FROM
2531 pa_resource_types
2532 WHERE resource_type_id = l_grouped_resource_type_id;
2533
2534 CURSOR c_exp_categ_csr IS
2535 SELECT
2536 rta.expenditure_category
2537 FROM
2538 pa_resource_list_members rlm,
2539 pa_resources re,
2540 pa_resource_txn_attributes rta
2541 WHERE rlm.resource_list_member_id = p_parent_member_id
2542 AND rlm.resource_id = re.resource_id
2543 AND re.resource_id = rta.resource_id;
2544
2545 CURSOR c_rev_categ_csr IS
2546 SELECT
2547 rta.revenue_category
2548 FROM
2549 pa_resource_list_members rlm,
2550 pa_resources re,
2551 pa_resource_txn_attributes rta
2552 WHERE rlm.resource_list_member_id = p_parent_member_id
2553 AND rlm.resource_id = re.resource_id
2554 AND re.resource_id = rta.resource_id;
2555
2556 CURSOR c_exp_types_csr_1 IS
2557 SELECT
2558 expenditure_type
2559 FROM
2560 pa_expenditure_types_res_v
2561 WHERE expenditure_type = p_expenditure_type
2562 AND expenditure_category = l_exp_category;
2563
2564
2565 CURSOR c_exp_types_csr_2 IS
2566 SELECT
2567 expenditure_type
2568 FROM
2569 pa_expenditure_types_res_v
2570 WHERE expenditure_type = p_expenditure_type
2571 AND revenue_category_code = l_revenue_category_code;
2572
2573 CURSOR c_event_types_csr IS
2574 SELECT
2575 event_type
2576 FROM
2577 pa_event_types_res_v
2578 WHERE event_type = p_event_type
2579 AND revenue_category_code = l_revenue_category_code;
2580
2581
2582 CURSOR c_res_list_member_csr_1 IS
2583 SELECT 'x'
2584 FROM
2585 pa_resource_list_members
2586 WHERE resource_list_id = p_resource_list_id
2587 AND parent_member_id = p_parent_member_id
2588 AND sort_order = p_sort_order;
2589
2590 CURSOR c_res_list_member_csr_1a IS
2591 SELECT 'x'
2592 FROM
2593 pa_resource_list_members
2594 WHERE resource_list_id = p_resource_list_id
2595 AND parent_member_id IS NULL
2596 AND sort_order = p_sort_order;
2597
2598 CURSOR c_res_list_member_csr_2 IS
2599 SELECT
2600 NVL(MAX(sort_order),0)+10
2601 FROM
2602 pa_resource_list_members
2603 WHERE resource_list_id = p_resource_list_id
2604 AND parent_member_id = p_parent_member_id
2605 AND sort_order < 999999;
2606
2607 CURSOR c_res_list_member_csr_2a IS
2608 SELECT
2609 NVL(MAX(sort_order),0)+10
2610 FROM
2611 pa_resource_list_members
2612 WHERE resource_list_id = p_resource_list_id
2613 AND parent_member_id IS NULL
2614 AND sort_order < 999999;
2615
2616 CURSOR c_res_list_member_csr_3 IS
2617 SELECT 'x'
2618 FROM
2619 pa_resource_list_members
2620 WHERE resource_list_id = p_resource_list_id
2621 AND parent_member_id = p_parent_member_id
2622 AND alias = p_alias;
2623
2624 CURSOR c_res_list_member_csr_3a IS
2625 SELECT 'x'
2626 FROM
2627 pa_resource_list_members
2628 WHERE resource_list_id = p_resource_list_id
2629 AND parent_member_id IS NULL
2630 AND alias = p_alias;
2631
2632 CURSOR c_res_list_member_csr_4 IS
2633 SELECT track_as_labor_flag
2634 FROM
2635 pa_resource_list_members
2636 WHERE resource_list_id = p_resource_list_id
2637 AND resource_list_member_id = p_parent_member_id;
2638
2639 CURSOR c_res_list_member_seq_csr IS
2640 SELECT
2641 pa_resource_list_members_s.NEXTVAL
2642 FROM SYS.DUAL;
2643
2644 --Cursor added for resolution of bug 1889671
2645
2646 CURSOR Cur_TXn_Attributes(p_resource_id PA_RESOURCES.RESOURCE_ID%TYPE) IS
2647 SELECT prta.person_id,
2648 prta.job_id,
2649 prta.organization_id,
2650 prta.vendor_id,
2651 prta.project_role_id,
2652 prta.expenditure_type,
2653 prta.event_type,
2654 prta.expenditure_category,
2655 prta.revenue_category,
2656 prta.non_labor_resource,
2657 prta.non_labor_resource_org_id,
2658 prta.event_type_classification,
2659 prta.system_linkage_function,
2660 prta.resource_format_id,
2661 prt.resource_type_id,
2662 prt.resource_type_code
2663 FROM PA_RESOURCE_TXN_ATTRIBUTES PRTA,
2664 PA_RESOURCES PR,
2665 PA_RESOURCE_TYPES PRT
2666 WHERE prta.resource_id = pr.resource_id
2667 AND pr.resource_id =P_RESOURCE_ID
2668 AND pr.resource_type_id= prt.resource_type_id;
2669
2670 --Outer Join is removed from above cursor as this code is not used to create unclassified
2671 --resource
2672
2673 -- Following block of code is added for the resolution of bug 1889671
2674 -- Same logic is used as it is done in PA_GET_RESOURCE.Get_Unclassified_Resource
2675 -- Start of change
2676
2677 CURSOR Cur_Unclassified_Resource_List IS
2678 SELECT prt.resource_type_id,prt.resource_type_code
2679 FROM pa_resources pr, pa_resource_types prt
2680 WHERE prt.resource_type_code='UNCLASSIFIED'
2684 l_old_stack := p_err_stack;
2681 AND pr.resource_type_id = prt.resource_type_id;
2682
2683 BEGIN
2685 p_err_code := 0;
2686 p_err_stack := p_err_stack||'->PA_CREATE_RESOURCE.add_resource_list_member';
2687 p_err_stage := ' Select group_resource_type_id from pa_resource_lists';
2688
2689 -- Get Resource List Id ,Group_resource_type_id from
2690 -- PA_RESOURCE_LISTS with the
2691 -- X_Resource_list_id.
2692 OPEN c_res_list_csr;
2693 FETCH c_res_list_csr INTO
2694 l_grouped_resource_type_id;
2695 IF c_res_list_csr%NOTFOUND THEN
2696 p_err_code := 10;
2697 p_err_stage := 'PA_RL_INVALID';
2698 CLOSE c_res_list_csr;
2699 RETURN;
2700 END IF;
2701
2702 CLOSE c_res_list_csr;
2703 --- If grouped_resource_type_id = 0,that means the resource list
2704 --- is not grouped.In that case,parent member id should be null
2705 --- else , parent_member_id should not be null
2706
2707 IF l_grouped_resource_type_id = 0 AND
2708 p_parent_member_id IS NOT NULL THEN
2709 p_err_code := 11;
2710 p_err_stage := 'PA_RL_NOT_GROUPED';
2711 RETURN;
2712 ELSIF
2713 l_grouped_resource_type_id <> 0 AND
2714 p_parent_member_id IS NULL THEN
2715 p_err_code := 12;
2716 p_err_stage := 'PA_RL_GROUPED';
2717 RETURN;
2718 END IF;
2719
2720 IF l_grouped_resource_type_id <> 0 THEN
2721 p_err_stage := ' Select resource_type_code from pa_resource_types';
2722 OPEN c_resource_types_csr;
2723 FETCH c_resource_types_csr INTO
2724 l_grouped_res_type_code;
2725 IF c_resource_types_csr%NOTFOUND THEN
2726 p_err_code := 13;
2727 p_err_stage := 'PA_RT_INVALID';
2728 CLOSE c_resource_types_csr;
2729 RETURN;
2730 END IF;
2731 CLOSE c_resource_types_csr;
2732 END IF;
2733 --- If parent_member_id is not null then
2734 --- Based on the resource_type_code get the Expenditure_category or
2735 --- Revenue_Category_code of the parent
2736 --- end if;
2737 IF p_parent_member_id IS NOT NULL THEN
2738 IF l_grouped_res_type_code = 'EXPENDITURE_CATEGORY' THEN
2739 p_err_stage := 'Select expenditure_category from ....';
2740 OPEN c_exp_categ_csr;
2741 FETCH c_exp_categ_csr INTO
2742 l_exp_category;
2743 CLOSE c_exp_categ_csr;
2744 ELSIF l_grouped_res_type_code = 'REVENUE_CATEGORY' THEN
2745 p_err_stage := 'Select revenue_category_code from ....';
2746 OPEN c_rev_categ_csr;
2747 FETCH c_rev_categ_csr INTO
2748 l_revenue_category_code;
2749 CLOSE c_rev_categ_csr;
2750 END IF;
2751 END IF;
2752
2753 ---If the resource_list had been grouped by Expenditure_Category or
2754 ---Revenue_category
2755 --- If the p_resource_type_code = 'EXPENDITURE_TYPE'
2756 --- then ensure that the input resource (the resource which is
2757 --- sought to be created as a resource list member )
2758 --- is valid under that Expenditure_Category or Revenue_Category
2759 --- If not then
2760 --- RAISE_ERROR;
2761 --- end if;
2762 --- end if;
2763 ---End if;
2764 --- Eg : An Expenditure_Type of 'Professional' is valid under
2765 --- Resource_Group 'Labor' but an Expenditure_Type of 'Air Travel'
2766 --- is invalid. This is because, the Expenditure_Type of 'Air Travel'
2767 --- does not have an expenditure_Category of 'Labor' and hence
2768 --- cannot be specified under the Resource_Group of 'Labor'
2769
2770 --- If the resource list had been grouped by Revenue Category
2771 --- If the p_resource_type_code = 'EVENT_TYPE'
2772 --- then ensure that the input resource (the resource which is
2773 --- is valid under that Revenue_Category
2774 --- If not then
2775 --- RAISE_ERROR;
2776 --- end if;
2777 --- end if;
2778 --- Eg: An Event_Type of 'Surcharge' is valid under
2779 --- Resource Group 'Fee' but an event Type of 'Bonus'
2780 --- is invalid. This is because, the Event type of 'Bonus'
2781 --- does not have a Revenue category of 'Fee' and hence
2782 --- cannot be specified under the Resource_Group of 'Fee'
2783
2784 IF p_parent_member_id IS NOT NULL THEN
2785 p_err_stage :=
2786 'Select expenditure_type from pa_expenditure_types_res_v';
2787 IF l_grouped_res_type_code = 'EXPENDITURE_CATEGORY' AND
2788 p_resource_type_code = 'EXPENDITURE_TYPE' THEN
2789 OPEN c_exp_types_csr_1;
2790 FETCH c_exp_types_csr_1 INTO l_exp_type;
2791 IF c_exp_types_csr_1%NOTFOUND THEN
2792 p_err_code := 14;
2793 p_err_stage := 'PA_ET_INV_FOR_EXP_CATEG';
2794 CLOSE c_exp_types_csr_1;
2795 RETURN;
2796 ELSE
2797 CLOSE c_exp_types_csr_1;
2798 END IF;
2799 ELSIF l_grouped_res_type_code = 'REVENUE_CATEGORY' AND
2800 p_resource_type_code = 'EXPENDITURE_TYPE' THEN
2801 OPEN c_exp_types_csr_2;
2802 FETCH c_exp_types_csr_2 INTO
2803 l_exp_type;
2807 CLOSE c_exp_types_csr_2;
2804 IF c_exp_types_csr_2%NOTFOUND THEN
2805 p_err_code := 14;
2806 p_err_stage := 'PA_ET_INV_FOR_REV_CATEG';
2808 RETURN;
2809 ELSE
2810 CLOSE c_exp_types_csr_2;
2811 END IF;
2812 ELSIF l_grouped_res_type_code = 'REVENUE_CATEGORY' AND
2813 p_resource_type_code = 'EVENT_TYPE' THEN
2814 p_err_stage :=
2815 'Select event_type from pa_event_types_res_v';
2816 OPEN c_event_types_csr;
2817 FETCH c_event_types_csr INTO
2818 l_event_type;
2819 IF c_event_types_csr%NOTFOUND THEN
2820 p_err_code := 14;
2821 p_err_stage := 'PA_EVENT_INV_FOR_REV_CATEG';
2822 CLOSE c_event_types_csr;
2823 RETURN;
2824 ELSE
2825 CLOSE c_event_types_csr;
2826 END IF;
2827 END IF;
2828 END IF;
2829
2830 p_err_stage := ' Select x from pa_resource_list_members';
2831
2832 IF (p_sort_order IS NULL OR p_sort_order = 0) THEN
2833 l_get_new_sort_order := 'TRUE';
2834 END IF;
2835
2836 -- Check whether sort_order is unique
2837 IF (p_sort_order IS NOT NULL AND p_sort_order > 0 ) THEN
2838 IF p_parent_member_id IS NULL THEN
2839 OPEN c_res_list_member_csr_1a;
2840 FETCH c_res_list_member_csr_1a INTO
2841 l_dummy;
2842 IF c_res_list_member_csr_1a%FOUND THEN
2843 l_get_new_sort_order := 'TRUE';
2844 ELSE
2845 l_sort_order := p_sort_order;
2846 END IF;
2847 CLOSE c_res_list_member_csr_1a;
2848 ELSE
2849 OPEN c_res_list_member_csr_1;
2850 FETCH c_res_list_member_csr_1 INTO
2851 l_dummy;
2852 IF c_res_list_member_csr_1%FOUND THEN
2853 l_get_new_sort_order := 'TRUE';
2854 ELSE
2855 l_sort_order := p_sort_order;
2856 END IF;
2857 CLOSE c_res_list_member_csr_1;
2858 END IF;
2859 END IF;
2860
2861 IF l_get_new_sort_order = 'TRUE' THEN
2862 p_err_stage := ' Select max(sort_order) from pa_resource_list_members';
2863 IF p_parent_member_id IS NULL THEN
2864 OPEN c_res_list_member_csr_2a;
2865 FETCH c_res_list_member_csr_2a INTO
2866 l_sort_order;
2867 CLOSE c_res_list_member_csr_2a;
2868 ELSE
2869 OPEN c_res_list_member_csr_2;
2870 FETCH c_res_list_member_csr_2 INTO
2871 l_sort_order;
2872 CLOSE c_res_list_member_csr_2;
2873 END IF;
2874 END IF;
2875
2876 IF LENGTH(p_alias) > 0 THEN
2877 l_alias := SUBSTR(p_alias,1,30);
2878 ELSE
2879 l_alias := p_alias;
2880 END IF;
2881 -- Check whether alias is unique
2882
2883 IF (p_alias IS NOT NULL ) THEN
2884 p_err_stage := ' Select x from pa_resource_list_members - alias';
2885 IF p_parent_member_id IS NULL THEN
2886 OPEN c_res_list_member_csr_3a;
2887 FETCH c_res_list_member_csr_3a INTO
2888 l_dummy;
2889 IF c_res_list_member_csr_3a%FOUND THEN
2890 l_alias := SUBSTR(p_resource_name,1,30);
2891 END IF;
2892 CLOSE c_res_list_member_csr_3a;
2893 ELSE
2894 OPEN c_res_list_member_csr_3;
2895 FETCH c_res_list_member_csr_3 INTO
2896 l_dummy;
2897 IF c_res_list_member_csr_3%FOUND THEN
2898 l_alias := SUBSTR(p_resource_name,1,30);
2899 END IF;
2900 CLOSE c_res_list_member_csr_3;
2901 END IF;
2902 ELSE
2903 l_alias := SUBSTR(p_resource_name,1,30);
2904 END IF;
2905
2906 -- Track_as_labor_flag of the child resource is dependent on the parent's
2907 -- track_as_labor_flag. Hence, need to get the parent's
2908 -- track_as_labor_flag;
2909
2910 IF p_parent_member_id IS NOT NULL THEN
2911 p_err_stage :=
2912 'Select track_as_labor_flag from pa_resource_list_members';
2913 OPEN c_res_list_member_csr_4;
2914 FETCH c_res_list_member_csr_4 INTO
2915 l_parent_track_as_labor_flag;
2916 IF c_res_list_member_csr_4%NOTFOUND THEN
2917 CLOSE c_res_list_member_csr_4;
2918 RAISE NO_DATA_FOUND;
2919 END IF;
2920 END IF;
2921
2922 --- Check whether the child resource has already been created as
2923 --- a resource in PA_RESOURCES table and get the resource_id.
2924
2925 PA_GET_RESOURCE.Get_Resource
2926 (p_resource_name => p_resource_name,
2927 p_resource_type_Code => p_resource_type_code,
2928 p_person_id => p_person_id,
2929 p_job_id => p_job_id,
2930 p_proj_organization_id => p_proj_organization_id,
2931 p_vendor_id => p_vendor_id,
2932 p_expenditure_type => p_expenditure_type,
2933 p_event_type => p_event_type,
2934 p_expenditure_category => p_expenditure_category,
2935 p_revenue_category_code => p_revenue_category_code,
2939 p_resource_id => l_resource_id,
2936 p_non_labor_resource => p_non_labor_resource,
2937 p_system_linkage => p_system_linkage,
2938 p_project_role_id => p_project_role_id,
2940 p_err_code => l_err_code,
2941 p_err_stage => p_err_stage,
2942 p_err_stack => p_err_stack );
2943
2944 IF l_err_code <> 0 THEN
2945 p_err_code := l_err_code;
2946 RETURN;
2947 END IF;
2948
2949 /* For bug # 818076 fix moved this code outside the if condition */
2950
2951 IF p_resource_type_code = 'EMPLOYEE' THEN
2952 l_attr_value := TO_CHAR(p_person_id );
2953 ELSIF p_resource_type_code = 'JOB' THEN
2954 l_attr_value := TO_CHAR(p_job_id) ;
2955 ELSIF p_resource_type_code = 'ORGANIZATION' THEN
2956 l_attr_value := TO_CHAR(p_proj_organization_id) ;
2957 ELSIF p_resource_type_code = 'VENDOR' THEN
2958 l_attr_value := TO_CHAR(p_vendor_id) ;
2959 ELSIF p_resource_type_code = 'EXPENDITURE_TYPE' THEN
2960 l_attr_value := p_expenditure_type ;
2961 ELSIF p_resource_type_code = 'EVENT_TYPE' THEN
2962 l_attr_value := p_event_type ;
2963 ELSIF p_resource_type_code = 'EXPENDITURE_CATEGORY' THEN
2964 l_attr_value := p_expenditure_category ;
2965 ELSIF p_resource_type_code = 'REVENUE_CATEGORY' THEN
2966 l_attr_value := p_revenue_category_code ;
2967 ELSIF p_resource_type_code = 'PROJECT_ROLE' THEN
2968 l_attr_value := TO_CHAR(p_project_role_id) ;
2969 END IF;
2970
2971 PA_GET_RESOURCE.Get_Resource_Information
2972 (p_resource_type_Code => p_resource_type_code,
2973 p_resource_attr_value => l_attr_value,
2974 p_unit_of_measure => l_uom,
2975 p_Rollup_quantity_flag => l_rollup_qty_flag,
2976 p_track_as_labor_flag => l_track_as_labor_flag,
2977 p_err_code => l_err_code,
2978 p_err_stage => p_err_stage,
2979 p_err_stack => p_err_stack);
2980
2981 IF l_err_code <> 0 THEN
2982 p_err_code := l_err_code;
2983 RETURN;
2984 END IF;
2985 /* End of bug # 818076 fix */
2986
2987 IF l_resource_id IS NULL THEN
2988
2989 -- If the child resource has not been created as a resource yet,then
2990 -- need to create the resource.Hence,get the necessary information
2991 -- from base views. Based on the resource_type_code,need to pass the
2992 -- person_id or job_id etc. Hence,
2993
2994 /* For bug # 818076 fix moved this code outside the if condition
2995 as track_as_labor flag should be assigned for resource_groups
2996 being inserted into resource_member_list table */
2997 /* Comment starts ********************
2998
2999 IF p_resource_type_code = 'EMPLOYEE' THEN
3000 l_attr_value := to_char(p_person_id );
3001 ELSIF p_resource_type_code = 'JOB' THEN
3002 l_attr_value := to_char(p_job_id) ;
3003 ELSIF p_resource_type_code = 'ORGANIZATION' THEN
3004 l_attr_value := to_char(p_proj_organization_id) ;
3005 ELSIF p_resource_type_code = 'VENDOR' THEN
3006 l_attr_value := to_char(p_vendor_id) ;
3007 ELSIF p_resource_type_code = 'EXPENDITURE_TYPE' THEN
3008 l_attr_value := p_expenditure_type ;
3009 ELSIF p_resource_type_code = 'EVENT_TYPE' THEN
3010 l_attr_value := p_event_type ;
3011 ELSIF p_resource_type_code = 'EXPENDITURE_CATEGORY' THEN
3012 l_attr_value := p_expenditure_category ;
3013 ELSIF p_resource_type_code = 'REVENUE_CATEGORY' THEN
3014 l_attr_value := p_revenue_category_code ;
3015 END IF;
3016
3017 PA_GET_RESOURCE.Get_Resource_Information
3018 (p_resource_type_Code => p_resource_type_code,
3019 p_resource_attr_value => l_attr_value,
3020 p_unit_of_measure => l_uom,
3021 p_Rollup_quantity_flag => l_rollup_qty_flag,
3022 p_track_as_labor_flag => l_track_as_labor_flag,
3023 p_err_code => l_err_code,
3024 p_err_stage => p_err_stage,
3025 p_err_stack => p_err_stack);
3026
3027 IF l_err_code <> 0 THEN
3028 p_err_code := l_err_code;
3029 RETURN;
3030 END IF;
3031 *********** Comment ends, # 818076 */
3032
3033 Create_Resource
3034 (p_resource_name => p_resource_name,
3035 p_resource_type_Code => p_resource_type_code,
3036 p_description => p_resource_name,
3037 p_unit_of_measure => l_uom,
3038 p_rollup_quantity_flag => l_rollup_qty_flag,
3039 p_track_as_labor_flag => l_track_as_labor_flag,
3040 p_start_date => SYSDATE,
3041 p_end_date => NULL,
3042 p_person_id => p_person_id,
3043 p_job_id => p_job_id,
3044 p_proj_organization_id => p_proj_organization_id,
3045 p_vendor_id => p_vendor_id,
3049 p_revenue_category_code => p_revenue_category_code,
3046 p_expenditure_type => p_expenditure_type,
3047 p_event_type => p_event_type,
3048 p_expenditure_category => p_expenditure_category,
3050 p_non_labor_resource => p_non_labor_resource,
3051 p_system_linkage => p_system_linkage,
3052 p_project_role_id => p_project_role_id,
3053 p_resource_id => l_resource_id,
3054 p_err_code => l_err_code,
3055 p_err_stage => p_err_stage,
3056 p_err_stack => p_err_stack );
3057
3058 IF l_err_code <> 0 THEN
3059 p_err_code := l_err_code;
3060 RETURN;
3061 END IF;
3062 END IF; -- (IF l_resource_id IS NULL )
3063 -- Tracking a Resource as Labor within the context of a Resource List
3064 -- is treated as follows.
3065 -- If resource's track_as_labor_flag = 'Y' then
3066 -- if parent_member_id is not null then
3067 -- if parent_track_as_labor_flag = 'Y'
3068 -- then Resource List Member Track_As_Labor_Flag = 'Y'
3069 -- else
3070 -- Resource List Member Track_As_Labor_Flag = 'N'
3071 -- end if;
3072 -- else
3073 -- Resource List Member Track_As_Labor_Flag = 'Y'
3074 -- end if;
3075 -- else
3076 -- Resource List Member Track_As_Labor_Flag = 'N'
3077 -- end if ;
3078
3079 IF l_track_as_labor_flag = 'Y' THEN
3080 IF p_parent_member_id IS NOT NULL THEN
3081 IF l_parent_track_as_labor_flag = 'Y' THEN
3082 l_new_track_as_labor_flag := 'Y';
3083 ELSE
3084 l_new_track_as_labor_flag := 'N';
3085 END IF;
3086 ELSE -- (if parent member id is null)
3087 l_new_track_as_labor_flag := 'Y';
3088 END IF;
3089 ELSE -- (if l_track_as_labor_flag = 'N')
3090 l_new_track_as_labor_flag := 'N';
3091 END IF;
3092
3093 -- Need to generate the resource_list_member_id
3094 p_err_stage := 'Select pa_resource_list_members_s.nextval ';
3095
3096 OPEN c_res_list_member_seq_csr;
3097 FETCH c_res_list_member_seq_csr INTO
3098 l_resource_list_member_id;
3099 IF c_res_list_member_seq_csr%NOTFOUND THEN
3100 CLOSE c_res_list_member_seq_csr;
3101 RAISE NO_DATA_FOUND;
3102 ELSE
3103 CLOSE c_res_list_member_seq_csr;
3104 END IF;
3105
3106 p_err_stage := 'Insert into pa_resource_list_members ';
3107
3108 /*Changes done for Resource Mapping Enhancements */
3109
3110 OPEN Cur_Txn_Attributes(l_resource_id);
3111 FETCH Cur_Txn_Attributes
3112 INTO l_person_id,
3113 l_job_id,
3114 l_organization_id,
3115 l_vendor_id,
3116 l_project_role_id,
3117 l_expenditure_type,
3118 l_event_type,
3119 l_expenditure_category,
3120 l_revenue_category,
3121 l_nlr_resource,
3122 l_nlr_res_org_id,
3123 l_event_type_cls,
3124 l_system_link_function,
3125 l_resource_format_id,
3126 l_resource_type_id,
3127 l_res_type_code;
3128 CLOSE Cur_Txn_Attributes;
3129
3130
3131 INSERT INTO pa_resource_list_members
3132 (resource_list_id,
3133 resource_list_member_id,
3134 resource_id,
3135 alias,
3136 parent_member_id,
3137 sort_order,
3138 member_level,
3139 display_flag,
3140 enabled_flag,
3141 track_as_labor_flag,
3142 last_updated_by,
3143 last_update_date,
3144 creation_date,
3145 created_by,
3146 last_update_login,
3147 PERSON_ID,
3148 JOB_ID,
3149 ORGANIZATION_ID,
3150 VENDOR_ID,
3151 PROJECT_ROLE_ID,
3152 EXPENDITURE_TYPE,
3153 EVENT_TYPE,
3154 EXPENDITURE_CATEGORY,
3155 REVENUE_CATEGORY,
3156 NON_LABOR_RESOURCE,
3157 NON_LABOR_RESOURCE_ORG_ID,
3158 EVENT_TYPE_CLASSIFICATION,
3159 SYSTEM_LINKAGE_FUNCTION,
3160 RESOURCE_FORMAT_ID,
3161 RESOURCE_TYPE_ID,
3162 RESOURCE_TYPE_CODE
3163 )
3164 VALUES (
3165 p_resource_list_id,
3166 l_resource_list_member_id,
3167 l_resource_id,
3168 l_alias,
3169 p_parent_member_id,
3170 l_sort_order,
3171 DECODE(p_parent_member_id,NULL,1,2),
3172 NVL(p_display_flag,'Y'),
3173 NVL(p_enabled_flag,'Y'),
3174 l_new_track_as_labor_flag,
3175 g_last_updated_by,
3176 g_last_update_date,
3177 g_creation_date,
3178 g_created_by,
3179 g_last_update_login,
3180 l_person_id,
3181 l_job_id,
3182 l_organization_id,
3183 l_vendor_id,
3184 l_project_role_id,
3185 l_expenditure_type,
3186 l_event_type,
3187 l_expenditure_category,
3188 l_revenue_category,
3192 l_system_link_function,
3189 l_nlr_resource,
3190 l_nlr_res_org_id,
3191 l_event_type_cls,
3193 l_resource_format_id,
3194 l_resource_type_id,
3195 l_res_type_code
3196 );
3197
3198 p_resource_list_member_id := l_resource_list_member_id;
3199 p_track_as_labor_flag := l_new_track_as_labor_flag;
3200 p_resource_id := l_resource_id;
3201
3202
3203 -- Each resource_group needs to have at least one unclassified
3204 -- resource as a child resource. However, this is true only if
3205 -- the resource_group has at least one child resource. Hence
3206 -- need to check whether the unclassified resource has already
3207 -- been created.
3208 IF p_parent_member_id IS NOT NULL THEN
3209 PA_GET_RESOURCE.Get_Unclassified_Resource
3210 (p_resource_id => l_resource_id,
3211 p_resource_name => l_resource_name,
3212 p_track_as_labor_flag => l_track_as_labor_flag,
3213 p_unit_of_measure => l_uom,
3214 p_rollup_quantity_flag => l_rollup_qty_flag,
3215 p_err_code => l_err_code,
3216 p_err_stage => p_err_stage,
3217 p_err_stack => p_err_stack );
3218
3219 IF l_err_code <> 0 THEN
3220 p_err_code := l_err_code;
3221 RETURN;
3222 END IF;
3223 PA_GET_RESOURCE.Get_Unclassified_Member
3224 (p_resource_list_id => p_resource_list_id,
3225 p_parent_member_id => p_parent_member_id,
3226 p_unclassified_resource_id => l_resource_id,
3227 p_resource_list_member_id => l_resource_list_member_id,
3228 p_track_as_labor_flag => l_new_track_as_labor_flag,
3229 p_err_code => l_err_code,
3230 p_err_stage => p_err_stage,
3231 p_err_stack => p_err_stack );
3232
3233 IF l_err_code <> 0 THEN
3234 p_err_code := l_err_code;
3235 RETURN;
3236 END IF;
3237
3238 IF l_resource_list_member_id IS NULL THEN
3239 p_err_stage := 'Insert into pa_resource_list_members ';
3240
3241 -- Following block of code is added for the resolution of bug 1889671
3242
3243 OPEN Cur_Unclassified_Resource_List;
3244 FETCH Cur_Unclassified_Resource_List INTO l_resource_type_id , l_resource_type_code;
3245 CLOSE Cur_Unclassified_Resource_List;
3246
3247 INSERT INTO pa_resource_list_members
3248 (resource_list_id,
3249 resource_list_member_id,
3250 resource_id,
3251 alias,
3252 parent_member_id,
3253 sort_order,
3254 member_level,
3255 display_flag,
3256 enabled_flag,
3257 track_as_labor_flag,
3258 resource_type_id,
3259 resource_type_code,
3260 last_updated_by,
3261 last_update_date,
3262 creation_date,
3263 created_by,
3264 last_update_login )
3265
3266 VALUES (
3267 p_resource_list_id,
3268 pa_resource_list_members_s.NEXTVAL,
3269 l_resource_id,
3270 l_resource_name,
3271 p_parent_member_id,
3272 999999,
3273 2,
3274 'N',
3275 'Y',
3276 l_track_as_labor_flag,
3277 l_resource_type_id,
3278 l_resource_type_code,
3279 g_last_updated_by,
3280 g_last_update_date,
3281 g_creation_date,
3282 g_created_by,
3283 g_last_update_login );
3284 END IF;
3285
3286 END IF;
3287
3288 p_err_stack := l_old_stack;
3289
3290 EXCEPTION
3291 WHEN OTHERS THEN
3292 p_err_code := SQLCODE;
3293 RAISE;
3294 END Add_Resouce_List_Member;
3295
3296 --##
3297 -- PROCEDURE Create_Default_Res_List
3298 --
3299 -- This procedure creates default Resource Lists for a specified business
3300 -- group. If a value is not passed for the X_BUSINESS_GROUP_ID parameter,
3301 -- then the business group used is the business group defined in the
3302 -- implementation options for the current operating unit.
3303 --
3304 -- PA seeds default Resource Lists upon install with a dummy
3305 -- BUSINESS_GROUP_ID of -3113. Whenever a new operating unit is
3306 -- implemented in PA (ie, a new record is created in PA_IMPLEMENTATIONS),
3307 -- this procedure is called to copy the seeded resource list data to the
3308 -- business group.
3309 --
3310 -- Arguments:
3311 -- X_business_group_id Identifier of the business group specified for
3312 -- the Operating Unit.
3313 --
3314 -- History:
3315 -- 16-AUG-96 Z. Connors Created.
3316 --
3320 -- by Ramesh:
3317 -- 17-OCT-02 jwhite Bug 2619122
3318 -- Rewrote logic to populate the following new
3319 -- columns from the source tables as directed
3321 -- - RESOURCE_TYPE_ID NUMBER(15)
3322 -- - RESOURCE_TYPE_CODE VARCHAR2(30)
3323 -- 23-Nov-04 smullapp Rewrote logic to populate the foll columns
3324 -- migration_code,use_for_wp_flag,control_flag,
3325 -- record_version_number(see bug: 4025330).
3326
3327
3328 PROCEDURE Create_Default_Res_List ( X_business_group_id IN NUMBER
3329 , X_err_code OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
3330 , X_err_stage IN OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
3331 , X_err_stack IN OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
3332 IS
3333 V_old_stack VARCHAR2(2000);
3334 X_resource_list_id NUMBER(15);
3335 X_user_id NUMBER(15);
3336 X_login_id NUMBER(15);
3337
3338 CURSOR Seeded_RLs
3339 IS
3340 SELECT resource_list_id,
3341 name, description, public_flag, group_resource_type_id,
3342 start_date_active, end_date_active, uncategorized_flag,
3343 control_flag,migration_code,use_for_wp_flag,
3344 last_updated_by, last_update_date, creation_date,
3345 created_by, last_update_login
3346 FROM pa_resource_lists_all_bg
3347 WHERE business_group_id = -3113;
3348
3349 BEGIN
3350
3351 X_user_id := NVL( fnd_global.user_id, 1 );
3352 X_login_id := NVL( fnd_global.login_id, 0 );
3353
3354 V_old_stack := X_err_stack;
3355 X_err_code := 0;
3356 X_err_stack :=
3357 X_err_stack ||'->PA_CREATE_RESOURCE.Create_Default_Res_List';
3358
3359 X_err_stage := 'Select seeded_resource_list From PA_RESOURCE_LISTS';
3360
3361 FOR eachRL IN Seeded_RLs LOOP
3362
3363 X_err_stage := 'Select pa_resource_lists_s.NEXTVAL From Dual';
3364
3365 SELECT pa_resource_lists_s.NEXTVAL
3366 INTO X_resource_list_id
3367 FROM sys.dual;
3368
3369 X_err_stage := 'Insert Into PA_RESOURCE_LISTS';
3370
3371 INSERT INTO pa_resource_lists_all_bg (
3372 resource_list_id
3373 , name
3374 , business_group_id
3375 , description
3376 , public_flag
3377 , group_resource_type_id
3378 , start_date_active
3379 , end_date_active
3380 , uncategorized_flag
3381 , control_flag
3382 , use_for_wp_flag
3383 , migration_code
3384 , last_updated_by
3385 , last_update_date
3386 , creation_date
3387 , created_by
3388 , last_update_login
3389 , record_version_number )
3390 SELECT
3391 X_resource_list_id
3392 , eachRL.name
3393 , NVL(X_business_group_id, fnd_profile.value('PER_BUSINESS_GROUP_ID'))
3394 , eachRL.description
3395 , eachRL.public_flag
3396 , eachRL.group_resource_type_id
3397 , eachRL.start_date_active
3398 , eachRL.end_date_active
3399 , eachRL.uncategorized_flag
3400 , eachRL.control_flag
3401 , eachRL.use_for_wp_flag
3402 , eachRL.migration_code
3403 , X_user_id
3404 , SYSDATE
3405 , SYSDATE
3406 , X_user_id
3407 , X_login_id
3408 , 1 -- record version number
3409 FROM
3410 sys.dual
3411 WHERE NOT EXISTS (
3412 SELECT NULL
3413 FROM pa_resource_lists rl
3414 WHERE business_group_id =
3415 NVL(X_business_group_id,
3416 fnd_profile.value('PER_BUSINESS_GROUP_ID'))
3417 AND rl.name = eachRL.name );
3418
3419 --Adding to TL
3420 INSERT into pa_resource_lists_tl (
3421 last_update_login,
3422 creation_date,
3423 created_by,
3424 last_update_date,
3425 last_updated_by,
3426 resource_list_id,
3427 name,
3428 description,
3429 language,
3430 source_lang
3431 ) SELECT
3432 x_login_id,
3433 sysdate,
3434 x_user_id,
3435 sysdate,
3436 X_user_id,
3437 x_resource_list_id,
3438 eachRL.name,
3439 NVL(eachRL.description,eachRL.name),
3440 L.LANGUAGE_CODE,
3441 userenv('LANG')
3442 FROM FND_LANGUAGES L
3443 WHERE L.INSTALLED_FLAG in ('I', 'B')
3444 and not exists
3445 (select NULL
3449 X_err_stage := 'Insert Into PA_RESOURCE_LIST_MEMBERS';
3446 from pa_resource_lists_tl T
3447 where T.RESOURCE_LIST_ID = X_RESOURCE_LIST_ID);
3448
3450
3451 INSERT INTO pa_resource_list_members (
3452 resource_list_member_id
3453 , resource_list_id
3454 , resource_id
3455 , alias
3456 , parent_member_id
3457 , sort_order
3458 , member_level
3459 , display_flag
3460 , enabled_flag
3461 , track_as_labor_flag
3462 , last_updated_by
3463 , last_update_date
3464 , creation_date
3465 , created_by
3466 , last_update_login
3467 , RESOURCE_TYPE_ID
3468 , RESOURCE_TYPE_CODE
3469 , object_type
3470 , object_id
3471 , RESOURCE_CLASS_ID
3472 , RES_FORMAT_ID
3473 , SPREAD_CURVE_ID
3474 , ETC_METHOD_CODE
3475 , RES_TYPE_CODE
3476 , RESOURCE_CLASS_CODE
3477 , RESOURCE_CLASS_FLAG
3478 , MIGRATION_CODE
3479 , RECORD_VERSION_NUMBER
3480 , INCURRED_BY_RES_FLAG
3481 , WP_ELIGIBLE_FLAG
3482 , UNIT_OF_MEASURE
3483 )
3484 SELECT pa_resource_list_members_s.NEXTVAL
3485 , X_resource_list_id
3486 , rlm.resource_id
3487 , rlm.alias
3488 , rlm.parent_member_id
3489 , rlm.sort_order
3490 , rlm.member_level
3491 , rlm.display_flag
3492 , rlm.enabled_flag
3493 , rlm.track_as_labor_flag
3494 , X_user_id
3495 , SYSDATE
3496 , SYSDATE
3497 , X_user_id
3498 , X_login_id
3499 --For bug 4025330
3500 , decode(rlm.resource_id, -99, null,rlm.resource_type_id)
3501 , decode(rlm.resource_id, -99, null,rlm.resource_type_code)
3502 , rlm.object_type
3503 , X_resource_list_id -- object ID has to be the new resource list ID - that's why we can't copy over the object ID.
3504 , rlm.RESOURCE_CLASS_ID
3505 , rlm.RES_FORMAT_ID
3506 , rlm.SPREAD_CURVE_ID
3507 , rlm.ETC_METHOD_CODE
3508 , rlm.RES_TYPE_CODE
3509 , rlm.RESOURCE_CLASS_CODE
3510 , rlm.RESOURCE_CLASS_FLAG
3511 , rlm.MIGRATION_CODE
3512 , 1 -- record version number
3513 , rlm.INCURRED_BY_RES_FLAG
3514 , rlm.WP_ELIGIBLE_FLAG
3515 , rlm.UNIT_OF_MEASURE
3516 FROM pa_resource_list_members rlm
3517 WHERE rlm.resource_list_id = eachRL.resource_list_id
3518 --begin:bug:5925973:Implementing the logic to restrict the creation of resource list member records when new OU ('Implementation Options' form of the OU)is created for an existing Business Group.
3519 --The Check:check whether the 'X_RESOURCE_LIST_ID' exists in the 'pa_resource_lists_all_bg' table before inserting records into 'pa_resource_list_members' table
3520 AND EXISTS
3521 (SELECT NULL
3522 FROM pa_resource_lists_all_bg T1
3523 WHERE T1.RESOURCE_LIST_ID = X_RESOURCE_LIST_ID);
3524 --end:bug:5925973
3525 END LOOP;
3526
3527 X_err_stack := V_old_stack;
3528
3529 EXCEPTION
3530 WHEN NO_DATA_FOUND THEN
3531 X_err_code := -100;
3532 X_err_stack := SQLERRM(SQLCODE);
3533 WHEN OTHERS THEN
3534 X_err_code := SQLCODE;
3535 X_err_stack := SQLERRM(SQLCODE);
3536
3537 END Create_Default_Res_List;
3538
3539 -- This procedure deletes a planning resource list, provided that it
3540 -- is not being used anywhere. It deletes the associated formats and
3541 -- planning resources of the list before deleting the list itself.
3542
3543 PROCEDURE Delete_Plan_Res_List (p_resource_list_id IN NUMBER,
3544 x_return_status OUT NOCOPY VARCHAR2,
3545 x_msg_count OUT NOCOPY NUMBER,
3546 x_msg_data OUT NOCOPY VARCHAR2) IS
3547
3548 CURSOR get_members (p_res_list_id in NUMBER) IS
3549 SELECT resource_list_member_id
3550 FROM pa_resource_list_members
3551 WHERE resource_list_id = p_res_list_id;
3552
3553 l_res_list_member_id NUMBER;
3554 l_err_code NUMBER;
3555
3556 BEGIN
3557
3558 x_msg_count := 0;
3559 x_return_status := FND_API.G_RET_STS_SUCCESS;
3560
3561 -- Check for Planning Resource List used anywhere
3562
3563 l_err_code := 0;
3564
3565 PA_GET_RESOURCE.delete_resource_list_ok(
3566 p_resource_list_id,
3567 'Y',
3568 l_err_code,
3569 x_msg_data);
3570 IF l_err_code <> 0 THEN
3571 x_msg_count := x_msg_count + 1;
3572 x_return_status := FND_API.G_RET_STS_ERROR;
3573 pa_utils.add_message('PA', x_msg_data);
3574 RETURN;
3575 --FND_MESSAGE.SET_NAME('PA', x_msg_data);
3576 --FND_MSG_PUB.ADD;
3577 END IF;
3578
3579 /*
3580 -- Check for Planning Resources used anywhere
3581 OPEN get_members(p_resource_list_id);
3582 LOOP
3583 FETCH get_members into l_res_list_member_id;
3584 EXIT WHEN get_members%NOTFOUND;
3585
3586 l_err_code := 0;
3587
3588 PA_GET_RESOURCE.delete_resource_list_member_ok(
3592 x_msg_data);
3589 p_resource_list_id,
3590 l_res_list_member_id,
3591 l_err_code,
3593
3594 IF l_err_code <> 0 THEN
3595 IF FND_MSG_PUB.Check_Msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3596 x_msg_count := x_msg_count + 1;
3597 x_return_status := FND_API.G_RET_STS_ERROR;
3598 FND_MESSAGE.SET_NAME('PA',x_msg_data);
3599 FND_MSG_PUB.ADD;
3600 END IF;
3601 RAISE FND_API.G_EXC_ERROR;
3602 END IF;
3603 END LOOP;
3604 */
3605
3606 -- Delete Planning resources from the list
3607 delete from pa_resource_list_members
3608 where resource_list_id = p_resource_list_id;
3609
3610 -- Delete resource formats from the list
3611 delete from pa_plan_rl_formats
3612 where resource_list_id = p_resource_list_id;
3613
3614 -- Delete the planning resource list - TL
3615 delete from pa_resource_lists_tl
3616 where resource_list_id = p_resource_list_id;
3617
3618 -- Delete the planning resource list
3619 delete from pa_resource_lists_all_bg
3620 where resource_list_id = p_resource_list_id;
3621
3622 END Delete_Plan_Res_List;
3623
3624 --The Below Code has been added by Archana
3625 /*************************************************************
3626 * Function : Check_pl_alias_unique
3627 * Description : The purpose of this function is to determine
3628 * the uniqueness of the resource alias if it is not null.
3629 * While inserting when we call this function then if 'N'
3630 * is returned then proceed else throw an error.
3631 *************************************************************/
3632 FUNCTION Check_pl_alias_unique(
3633 p_resource_list_id IN VARCHAR2,
3634 p_resource_alias IN VARCHAR2,
3635 p_resource_list_member_id IN VARCHAR2)
3636 RETURN VARCHAR2
3637 IS
3638 l_check_unique_res varchar2(30) := 'Y';
3639 BEGIN
3640 BEGIN
3641 SELECT 'N'
3642 INTO l_check_unique_res
3643 FROM pa_resource_list_members
3644 WHERE resource_list_id = p_resource_list_id
3645 AND alias = p_resource_alias
3646 AND resource_list_member_id <>
3647 nvl(p_resource_list_member_id,-99);
3648 EXCEPTION
3649 WHEN NO_DATA_FOUND THEN
3650 l_check_unique_res := 'Y';
3651 END;
3652 return l_check_unique_res;
3653 END Check_pl_alias_unique;
3654 /***********************************/
3655 /**********************************************
3656 * Procedure : Add_Language
3657 **********************************************/
3658 procedure ADD_LANGUAGE
3659 is
3660 begin
3661 delete from pa_resource_lists_tl T
3662 where not exists
3663 (select NULL
3664 from PA_RESOURCE_LISTS_ALL_BG B
3665 where B.RESOURCE_LIST_ID = T.resource_list_id
3666 );
3667
3668 update pa_resource_lists_tl T set (
3669 NAME,
3670 DESCRIPTION
3671 ) = (select
3672 B.NAME,
3673 B.DESCRIPTION
3674 from pa_resource_lists_tl b
3675 where B.RESOURCE_LIST_ID = T.RESOURCE_LIST_ID
3676 and B.LANGUAGE = T.SOURCE_LANG)
3677 where (
3678 T.RESOURCE_LIST_ID,
3679 T.LANGUAGE
3680 ) in (select
3681 SUBT.RESOURCE_LIST_ID,
3682 SUBT.LANGUAGE
3683 from pa_resource_lists_tl SUBB, pa_resource_lists_tl SUBT
3684 where SUBB.RESOURCE_LIST_ID = SUBT.RESOURCE_LIST_ID
3685 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
3686 and (SUBB.NAME <> SUBT.NAME
3687 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
3688 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
3689 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
3690 ));
3691
3692 insert into pa_resource_lists_tl (
3693 LAST_UPDATE_LOGIN,
3694 CREATION_DATE,
3695 CREATED_BY,
3696 LAST_UPDATE_DATE,
3697 LAST_UPDATED_BY,
3698 RESOURCE_LIST_ID,
3699 NAME,
3700 DESCRIPTION,
3701 LANGUAGE,
3702 SOURCE_LANG
3703 ) select
3704 B.LAST_UPDATE_LOGIN,
3705 B.CREATION_DATE,
3706 B.CREATED_BY,
3707 B.LAST_UPDATE_DATE,
3708 B.LAST_UPDATED_BY,
3709 B.RESOURCE_LIST_ID,
3710 B.NAME,
3711 B.DESCRIPTION,
3712 L.LANGUAGE_CODE,
3713 B.SOURCE_LANG
3714 from pa_resource_lists_tl B, FND_LANGUAGES L
3715 where L.INSTALLED_FLAG in ('I', 'B')
3716 and B.LANGUAGE = userenv('LANG')
3717 and not exists
3718 (select NULL
3719 from pa_resource_lists_tl T
3720 where T.RESOURCE_LIST_ID = B.RESOURCE_LIST_ID
3721 and T.LANGUAGE = L.LANGUAGE_CODE);
3722 end ADD_LANGUAGE;
3723 /***************************/
3724 /*******************************************************
3725 * Procedure : Create_Proj_Resource_List
3726 * Description : This procedure is used to create resource
3727 * list members, whenever we create a project
3728 * specific resource list(ie when a resource
3729 * list is associated to a project).
3730 * We are copying the resource members
3731 * from the existing members for the same
3732 * resource list.
3733 *******************************************************/
3734 PROCEDURE Create_Proj_Resource_List
3735 (p_resource_list_id IN VARCHAR2,
3739 x_msg_count OUT NOCOPY Number)
3736 p_project_id IN NUMBER,
3737 x_return_status OUT NOCOPY VARCHAR2,
3738 x_error_msg_data OUT NOCOPY Varchar2,
3740 IS
3741 l_exist_record Varchar2(30);
3742 l_central_control Varchar2(30);
3743 l_error_msg_data Varchar2(30);
3744 BEGIN
3745 x_return_status := FND_API.G_RET_STS_SUCCESS;
3746 x_msg_count := 0;
3747 x_error_msg_data := NULL;
3748 /*********************************************
3749 * The below select would check if the resource
3750 * list is centrally controlled or not.
3751 * If it is Centrally Controlled then we cannot associate
3752 * it to a project.
3753 ************************************************/
3754 /**************************************************
3755 * If the Project ID is passed in as NULL then Raise an
3756 * Unexpected error and Return.
3757 ***************************************************/
3758 IF p_project_id IS NULL THEN
3759 X_Return_Status := Fnd_Api.G_Ret_Sts_UnExp_Error;
3760 x_msg_count := x_msg_count + 1;
3761 x_error_msg_data := Null;
3762 Fnd_Msg_Pub.Add_Exc_Msg(
3763 P_Pkg_Name => 'PA_CREATE_RESOURCE',
3764 P_Procedure_Name => 'Create_Proj_Resource_List');
3765
3766 Return;
3767
3768 END IF;
3769
3770 BEGIN
3771 SELECT Control_flag
3772 INTO l_central_control
3773 FROM pa_resource_lists_all_bg
3774 where resource_list_id = p_resource_list_id;
3775 EXCEPTION
3776 WHEN OTHERS THEN
3777 X_Return_Status := Fnd_Api.G_Ret_Sts_UnExp_Error;
3778 x_msg_count := x_msg_count + 1;
3779 x_error_msg_data := Null;
3780 Fnd_Msg_Pub.Add_Exc_Msg(
3781 P_Pkg_Name => 'PA_CREATE_RESOURCE',
3782 P_Procedure_Name => 'Create_Proj_Resource_List');
3783
3784 Return;
3785 END;
3786
3787 IF l_central_control = 'Y' THEN
3788 x_return_status := FND_API.G_RET_STS_SUCCESS;
3789 RETURN;
3790 END IF;
3791
3792 /******************************************************
3793 * This select would check for the existance of recource
3794 * members in the pa_resource_list_members table which have the same
3795 * resource_list_id and project_id.
3796 * *****************************************************/
3797 BEGIN
3798 SELECT 'Y'
3799 INTO l_exist_record
3800 FROM dual --For perf bug 4067435
3801 WHERE EXISTS (SELECT resource_list_id,object_id
3802 FROM pa_resource_list_members
3803 WHERE object_id = p_project_id
3804 AND object_type = 'PROJECT'
3805 AND resource_list_id = p_resource_list_id)
3806 AND ROWNUM = 1;
3807 EXCEPTION
3808 WHEN NO_DATA_FOUND THEN
3809 l_exist_record := 'N';
3810 WHEN OTHERS THEN
3811 l_exist_record := 'Y';
3812 END;
3813
3814 IF l_exist_record = 'Y' THEN
3815 x_return_status := FND_API.G_RET_STS_SUCCESS;
3816 RETURN;
3817 END IF;
3818 /******************************************************
3819 * Copying into the Pa_Resource_list_members table based
3820 * on existing values for the same resource_list_id.
3821 ******************************************************/
3822 /*****************************************************
3823 * Bug - 3591751
3824 * Desc - While inserting into the Pa_resource_list_members
3825 * insert value into the wp_eligible_flag as well.
3826 *****************************************************/
3827 /**********************************************************************
3828 * Bug - 3597011
3829 * Desc - While inserting we need to check for enabled_flag <> N
3830 ***********************************************************************/
3831 INSERT INTO Pa_Resource_List_Members
3832 ( RESOURCE_LIST_MEMBER_ID,
3833 RESOURCE_LIST_ID,
3834 RESOURCE_ID,
3835 ALIAS,
3836 DISPLAY_FLAG,
3837 ENABLED_FLAG,
3838 TRACK_AS_LABOR_FLAG,
3839 PERSON_ID,
3840 JOB_ID,
3841 ORGANIZATION_ID,
3842 VENDOR_ID,
3843 EXPENDITURE_TYPE,
3844 EVENT_TYPE,
3845 NON_LABOR_RESOURCE,
3846 EXPENDITURE_CATEGORY,
3847 REVENUE_CATEGORY,
3848 PROJECT_ROLE_ID,
3849 OBJECT_TYPE,
3850 OBJECT_ID,
3851 RESOURCE_CLASS_ID,
3852 RESOURCE_CLASS_CODE,
3853 RES_FORMAT_ID,
3854 SPREAD_CURVE_ID,
3855 ETC_METHOD_CODE,
3856 MFC_COST_TYPE_ID,
3857 COPY_FROM_RL_FLAG,
3858 RESOURCE_CLASS_FLAG,
3859 FC_RES_TYPE_CODE,
3860 INVENTORY_ITEM_ID,
3861 ITEM_CATEGORY_ID,
3862 MIGRATION_CODE,
3863 ATTRIBUTE_CATEGORY,
3864 ATTRIBUTE1,
3865 ATTRIBUTE2,
3866 ATTRIBUTE3 ,
3867 ATTRIBUTE4 ,
3868 ATTRIBUTE5 ,
3869 ATTRIBUTE6 ,
3870 ATTRIBUTE7 ,
3871 ATTRIBUTE8 ,
3872 ATTRIBUTE9 ,
3873 ATTRIBUTE10 ,
3874 ATTRIBUTE11 ,
3878 ATTRIBUTE15 ,
3875 ATTRIBUTE12 ,
3876 ATTRIBUTE13 ,
3877 ATTRIBUTE14 ,
3879 ATTRIBUTE16 ,
3880 ATTRIBUTE17 ,
3881 ATTRIBUTE18 ,
3882 ATTRIBUTE19 ,
3883 ATTRIBUTE20 ,
3884 ATTRIBUTE21 ,
3885 ATTRIBUTE22 ,
3886 ATTRIBUTE23 ,
3887 ATTRIBUTE24 ,
3888 ATTRIBUTE25 ,
3889 ATTRIBUTE26 ,
3890 ATTRIBUTE27 ,
3891 ATTRIBUTE28 ,
3892 ATTRIBUTE29 ,
3893 ATTRIBUTE30 ,
3894 RECORD_VERSION_NUMBER,
3895 PERSON_TYPE_CODE,
3896 BOM_RESOURCE_ID,
3897 TEAM_ROLE,
3898 INCURRED_BY_RES_FLAG,
3899 INCUR_BY_RES_CLASS_CODE,
3900 INCUR_BY_ROLE_ID,
3901 --3591751
3902 WP_ELIGIBLE_FLAG,
3903 --Bug 3637045
3904 UNIT_OF_MEASURE,
3905 LAST_UPDATED_BY,
3906 LAST_UPDATE_DATE,
3907 CREATION_DATE,
3908 CREATED_BY,
3909 LAST_UPDATE_LOGIN)
3910 SELECT
3911 pa_resource_list_members_s.NEXTVAL,
3912 a.RESOURCE_LIST_ID,
3913 a.RESOURCE_ID,
3914 a.ALIAS,
3915 a.DISPLAY_FLAG,
3916 a.ENABLED_FLAG,
3917 a.TRACK_AS_LABOR_FLAG,
3918 a.PERSON_ID,
3919 a.JOB_ID,
3920 a.ORGANIZATION_ID,
3921 a.VENDOR_ID,
3922 a.EXPENDITURE_TYPE,
3923 a.EVENT_TYPE,
3924 a.NON_LABOR_RESOURCE,
3925 a.EXPENDITURE_CATEGORY,
3926 a.REVENUE_CATEGORY,
3927 a.PROJECT_ROLE_ID,
3928 'PROJECT',
3929 p_project_id,
3930 a.RESOURCE_CLASS_ID,
3931 a.RESOURCE_CLASS_CODE,
3932 a.RES_FORMAT_ID,
3933 a.SPREAD_CURVE_ID,
3934 a.ETC_METHOD_CODE,
3935 a.MFC_COST_TYPE_ID,
3936 a.COPY_FROM_RL_FLAG,
3937 a.RESOURCE_CLASS_FLAG,
3938 a.FC_RES_TYPE_CODE,
3939 a.INVENTORY_ITEM_ID,
3940 a.ITEM_CATEGORY_ID,
3941 a.MIGRATION_CODE,
3942 a.ATTRIBUTE_CATEGORY,
3943 a.ATTRIBUTE1,
3944 a.ATTRIBUTE2,
3945 a.ATTRIBUTE3 ,
3946 a.ATTRIBUTE4 ,
3947 a.ATTRIBUTE5 ,
3948 a.ATTRIBUTE6 ,
3949 a.ATTRIBUTE7 ,
3950 a.ATTRIBUTE8 ,
3951 a.ATTRIBUTE9 ,
3952 a.ATTRIBUTE10 ,
3953 a.ATTRIBUTE11 ,
3954 a.ATTRIBUTE12 ,
3955 a.ATTRIBUTE13 ,
3956 a.ATTRIBUTE14 ,
3957 a.ATTRIBUTE15 ,
3958 a.ATTRIBUTE16 ,
3959 a.ATTRIBUTE17 ,
3960 a.ATTRIBUTE18 ,
3961 a.ATTRIBUTE19 ,
3962 a.ATTRIBUTE20 ,
3963 a.ATTRIBUTE21 ,
3964 a.ATTRIBUTE22 ,
3965 a.ATTRIBUTE23 ,
3966 a.ATTRIBUTE24 ,
3967 a.ATTRIBUTE25 ,
3968 a.ATTRIBUTE26 ,
3969 a.ATTRIBUTE27 ,
3970 a.ATTRIBUTE28 ,
3971 a.ATTRIBUTE29 ,
3972 a.ATTRIBUTE30 ,
3973 a.RECORD_VERSION_NUMBER,
3974 a.PERSON_TYPE_CODE,
3975 a.BOM_RESOURCE_ID,
3976 a.TEAM_ROLE,
3977 a.INCURRED_BY_RES_FLAG,
3978 a.INCUR_BY_RES_CLASS_CODE,
3979 a.INCUR_BY_ROLE_ID,
3980 --3591751
3981 a.wp_eligible_flag,
3982 --Bug 3637045
3983 a.unit_of_measure,
3984 FND_GLOBAL.USER_ID,
3985 SYSDATE,
3986 SYSDATE,
3987 FND_GLOBAL.USER_ID,
3988 FND_GLOBAL.LOGIN_ID
3989 FROM pa_resource_list_members a
3990 WHERE a.resource_list_id = p_resource_list_id
3991 AND a.object_id = p_resource_list_id
3992 AND a.object_type = 'RESOURCE_LIST'
3993 -- 3597011
3994 and a.enabled_flag <> 'N';
3995
3996 EXCEPTION
3997 WHEN OTHERS THEN
3998 X_Return_Status := Fnd_Api.G_Ret_Sts_UnExp_Error;
3999 x_msg_count := x_msg_count + 1;
4000 x_error_msg_data := Null;
4001 Fnd_Msg_Pub.Add_Exc_Msg(
4002 P_Pkg_Name => 'PA_CREATE_RESOURCE',
4003 P_Procedure_Name => 'Create_Proj_Resource_List');
4004
4005 Return;
4006 END Create_Proj_Resource_List;
4007 /**********************************/
4008
4009
4010 -- History:
4011 --
4012 -- 16-MAR-2004 smullapp created
4013 /*=========================================================================================
4014 This api creates a new resource list and copies its elements from the parent resource list
4015 ===========================================================================================*/
4016
4017 -- Procedure : COPY_RESOURCE_LIST
4018 -- Type : Public Procedure
4019 -- Purpose : This API will be used to create new resource list which will be the copy of existing resource list.
4020 -- : This API will be called from following page:
4021 -- : 1.Copy Planning Resource List Page
4022 -- : This API does business validations
4023 -- : 1: The resource list names should be unique
4024 -- : 2: The start date active of resource cannot be null
4025 -- : 3: The start date active cannot be more than end date active
4029 -- Note : This API will create a new resource list.
4026 -- : If no errors are encountered it will call the table handler which creates
4027 -- the new resource list.
4028
4030
4031 -- Assumptions :
4032
4033 -- Parameters Type Required Description and Purpose
4034 -- --------------------------- ------ -------- --------------------------------------------------------
4035 -- p_parent_resource_list_id NUMBER Yes The value will contain the Resource list id of the parent resource list
4036 -- p_name VARCHAR2 Yes The value will contain the name of the resource list
4037 -- p_description VARCHAR2 No The value will contain the description of the resource list
4038 -- p_start_date_active DATE Yes The value will contain the start date of the resource
4039 -- p_end_date_active DATE No The value will contain the end date of the resource
4040 -- p_job_group_id NUMBER No The value will contain the job group id of the resource list
4041 -- p_control_flag VARCHAR2 No The value will contain the control flag of the resource
4042 -- p_use_for_wp_flag VARCHAR2 No The value will contain the use for workplan flag
4043
4044
4045 PROCEDURE COPY_RESOURCE_LIST(
4046 P_Commit IN Varchar2 Default Fnd_Api.G_False,
4047 P_Init_Msg_List IN Varchar2 Default Fnd_Api.G_True,
4048 P_API_Version_Number IN Number,
4049 p_parent_resource_list_id IN PA_RESOURCE_LISTS_ALL_BG.resource_list_id%TYPE,
4050 p_name IN PA_RESOURCE_LISTS_ALL_BG.name%TYPE,
4051 p_description IN PA_RESOURCE_LISTS_ALL_BG.description%TYPE,
4052 p_start_date_active IN PA_RESOURCE_LISTS_ALL_BG.START_DATE_ACTIVE%TYPE,
4053 p_end_date_active IN PA_RESOURCE_LISTS_ALL_BG.END_DATE_ACTIVE%TYPE,
4054 p_job_group_id IN PA_RESOURCE_LISTS_ALL_BG.JOB_GROUP_ID%TYPE,
4055 p_control_flag IN PA_RESOURCE_LISTS_ALL_BG.CONTROL_FLAG%TYPE,
4056 p_use_for_wp_flag IN PA_RESOURCE_LISTS_ALL_BG.USE_FOR_WP_FLAG%TYPE,
4057 x_return_status OUT NOCOPY Varchar2,
4058 x_msg_data OUT NOCOPY Varchar2,
4059 x_msg_count OUT NOCOPY NUMBER
4060 )
4061 IS
4062 p_public_flag PA_RESOURCE_LISTS_ALL_BG.public_flag%TYPE;
4063 p_group_resource_type_id PA_RESOURCE_LISTS_ALL_BG.group_resource_type_id%TYPE;
4064 p_uncategorized_flag PA_RESOURCE_LISTS_ALL_BG.uncategorized_flag%TYPE;
4065 p_business_group_id PA_RESOURCE_LISTS_ALL_BG.business_group_id%TYPE;
4066 p_adw_notify_flag PA_RESOURCE_LISTS_ALL_BG.adw_notify_flag%TYPE;
4067 p_resource_list_type PA_RESOURCE_LISTS_ALL_BG.resource_list_type%TYPE;
4068 p_migration_code PA_RESOURCE_LISTS_ALL_BG.migration_code%TYPE;
4069 l_resource_list_member_id PA_RESOURCE_LIST_MEMBERS.RESOURCE_LIST_MEMBER_ID%TYPE;
4070 x_resource_list_id PA_RESOURCE_LISTS_ALL_BG.resource_list_id%TYPE;
4071 l_resource_list_id NUMBER:=NULL;
4072 --3596702
4073 l_res_list_member_id Number;
4074
4075
4076 l_msg_count NUMBER:=0;
4077 l_msg_data VARCHAR2(2000):=NULL;
4078 l_data VARCHAR2(2000):=NULL;
4079 l_msg_index_out NUMBER;
4080 l_error_raised VARCHAR2(1):=NULL;
4081 l_error Exception;
4082
4083 l_Api_Name Varchar2(30) := 'COPY_RESOURCE_LIST';
4084 l_api_version NUMBER:=1.0;
4085
4086 BEGIN
4087
4088 -- hr_utility.trace_on(NULL, 'RMCOPY');
4089 -- hr_utility.trace('start');
4090 --Check for API compatibility
4091 If Not Fnd_Api.Compatible_API_Call (
4092 l_Api_Version,
4093 P_Api_Version_Number,
4094 l_Api_Name,
4095 'PA_CREATE_RESOURCE') Then
4096
4097 Raise Fnd_Api.G_Exc_Unexpected_Error;
4098
4099 End If;
4100
4101
4102 --Initialize the message stack if not initialized
4103 If Fnd_Api.To_Boolean(nvl(P_Init_Msg_List,Fnd_Api.G_True)) Then
4104
4105 Fnd_Msg_Pub.Initialize;
4106
4107 End If;
4108
4109 --Initialize error handling variables
4110 X_Msg_Count := 0;
4111 X_Msg_Data := Null;
4112 X_Return_Status := Fnd_Api.G_Ret_Sts_Success;
4113
4114 -- hr_utility.trace('Initialize');
4115
4116 --Check for the uniquness of resource list names
4117 IF(chk_plan_rl_unique(p_name,
4118 l_resource_list_id) = FALSE) THEN
4119 x_return_status := FND_API.G_RET_STS_ERROR;
4120 pa_utils.add_message(P_App_Short_Name => 'PA',
4121 P_Msg_Name => 'PA_RL_FOUND');
4122 l_msg_count := FND_MSG_PUB.count_msg;
4123 IF l_msg_count = 1 THEN
4124 PA_INTERFACE_UTILS_PUB.get_messages
4125 (p_encoded => FND_API.G_TRUE,
4126 p_msg_index => 1,
4127 p_msg_count => l_msg_count,
4128 p_msg_data => l_msg_data,
4129 p_data => l_data,
4130 p_msg_index_out => l_msg_index_out);
4131 x_msg_data := l_data;
4135 x_msg_count := l_msg_count;
4132 x_msg_count := l_msg_count;
4133
4134 ELSE
4136 END IF;
4137
4138 pa_debug.reset_curr_function;
4139 -- hr_utility.trace('l_error_raised');
4140 l_error_raised:='Y';
4141 END IF;
4142
4143
4144
4145 -- Validate Dates
4146 -- Start Date is required
4147 -- hr_utility.trace('Validate Dates');
4148 IF (p_start_date_active is NULL) THEN
4149 x_return_status := FND_API.G_RET_STS_ERROR;
4150 pa_utils.add_message(p_app_short_name => 'PA'
4151 ,p_msg_name => 'PA_IRS_START_NOT_NULL');
4152
4153 l_msg_count := FND_MSG_PUB.count_msg;
4154 IF l_msg_count = 1 THEN
4155 PA_INTERFACE_UTILS_PUB.get_messages
4156 (p_encoded => FND_API.G_TRUE,
4157 p_msg_index => 1,
4158 p_msg_count => l_msg_count,
4159 p_msg_data => l_msg_data,
4160 p_data => l_data,
4161 p_msg_index_out => l_msg_index_out);
4162 x_msg_data := l_data;
4163 x_msg_count := l_msg_count;
4164
4165 ELSE
4166 x_msg_count := l_msg_count;
4167 END IF;
4168
4169 pa_debug.reset_curr_function;
4170 l_error_raised:='Y';
4171 END IF;
4172
4173 -- hr_utility.trace('Validate Dates 2');
4174 --Validation:Start date cannot be greater than end date
4175 IF (p_start_date_active IS NOT NULL and p_end_date_active IS NOT NULL
4176 and p_start_date_active >= p_end_date_active) THEN
4177
4178 x_return_status := FND_API.G_RET_STS_ERROR;
4179
4180 pa_utils.add_message(p_app_short_name => 'PA'
4181 ,p_msg_name => 'PA_PR_INVALID_OR_DATES');
4182
4183 l_msg_count := FND_MSG_PUB.count_msg;
4184 IF l_msg_count = 1 THEN
4185 PA_INTERFACE_UTILS_PUB.get_messages
4186 (p_encoded => FND_API.G_TRUE,
4187 p_msg_index => 1,
4188 p_msg_count => l_msg_count,
4189 p_msg_data => l_msg_data,
4190 p_data => l_data,
4191 p_msg_index_out => l_msg_index_out);
4192 x_msg_data := l_data;
4193 x_msg_count := l_msg_count;
4194
4195 ELSE
4196 x_msg_count := l_msg_count;
4197 END IF;
4198
4199 pa_debug.reset_curr_function;
4200 l_error_raised:='Y';
4201 END IF;
4202
4203
4204 IF(l_error_raised ='Y') THEN
4205 -- hr_utility.trace('l_error_raised =Y');
4206 Raise l_error;
4207 END IF;
4208
4209 --Get needed data of parent resource list from pa_resource_lists_all_bg
4210 SELECT
4211 public_flag,
4212 group_resource_type_id,
4213 uncategorized_flag,
4214 business_group_id,
4215 adw_notify_flag,
4216 resource_list_type,
4217 --'N' --Bug 3695679
4218 migration_code --Bug 3710189
4219 INTO
4220 p_public_flag,
4221 p_group_resource_type_id,
4222 p_uncategorized_flag,
4223 p_business_group_id,
4224 p_adw_notify_flag,
4225 p_resource_list_type,
4226 p_migration_code
4227 FROM
4228 pa_resource_lists_all_bg
4229 WHERE
4230 resource_list_id=p_parent_resource_list_id;
4231
4232 -- hr_utility.trace('BG insert');
4233
4234 --Call Insert_row which inserts a row into PA_RESOURCE_LISTS_ALL_BG table
4235 PA_Resource_List_tbl_Pkg.Insert_Row(
4236 p_name,
4237 p_description,
4238 p_public_flag,
4239 p_group_resource_type_id,
4240 p_start_date_active,
4241 p_end_date_active,
4242 p_uncategorized_flag,
4243 p_business_group_id,
4244 p_adw_notify_flag,
4245 p_job_group_id,
4246 p_resource_list_type,
4247 p_control_flag,
4248 p_use_for_wp_flag,
4249 p_migration_code,
4250 x_resource_list_id,
4251 x_return_status,
4252 x_msg_data
4253 );
4254
4255 -- hr_utility.trace('member insert');
4256
4257
4258 --Adding to TL
4259 INSERT into pa_resource_lists_tl (
4260 last_update_login,
4261 creation_date,
4262 created_by,
4263 last_update_date,
4264 last_updated_by,
4268 language,
4265 resource_list_id,
4266 name,
4267 description,
4269 source_lang
4270 ) SELECT
4271 fnd_global.login_id,
4272 sysdate,
4273 fnd_global.user_id,
4274 sysdate,
4275 fnd_global.user_id,
4276 x_resource_list_id,
4277 p_name,
4278 NVL(p_description,p_name),
4279 L.LANGUAGE_CODE,
4280 userenv('LANG')
4281 FROM FND_LANGUAGES L
4282 WHERE L.INSTALLED_FLAG in ('I', 'B')
4283 and not exists
4284 (select NULL
4285 from pa_resource_lists_tl T
4286 where T.RESOURCE_LIST_ID = X_RESOURCE_LIST_ID
4287 and T.LANGUAGE = L.LANGUAGE_CODE);
4288
4289
4290 -- hr_utility.trace('TL insert');
4291 INSERT INTO pa_plan_rl_formats
4292 (SELECT
4293 Pa_Plan_RL_Formats_S.nextval,
4294 X_Resource_List_Id,
4295 res_format_id,
4296 1,
4297 sysdate,
4298 fnd_global.user_id,
4299 sysdate,
4300 fnd_global.user_id,
4301 fnd_global.login_id
4302 FROM
4303 pa_plan_rl_formats
4304 WHERE
4305 resource_list_id=p_parent_resource_list_id
4306 AND p_migration_code IN ('N','M')); --Bug 3710189
4307
4308 -- hr_utility.trace('format insert');
4309
4310 --Start:bug 3710189
4311
4312 Begin
4313
4314 Delete
4315 From Pa_Rbs_Elements_Temp;
4316
4317 Exception
4318 When No_Data_Found Then
4319 null;
4320
4321 End;
4322
4323
4324 Insert Into Pa_Rbs_Elements_Temp(
4325 New_Element_Id,
4326 Old_Element_Id,
4327 Old_Parent_Element_Id,
4328 New_Parent_Element_Id )
4329 (Select
4330 Pa_resource_list_members_S.NextVal,
4331 resource_list_member_id,
4332 Parent_member_Id,
4333 Null
4334 From Pa_resource_list_members
4335 Where resource_list_id = p_parent_resource_list_id
4336 and (object_type = 'RESOURCE_LIST' OR object_type is NULL)
4337 --don't want to copy proj specific resources
4338 and enabled_flag <> 'N' );
4339
4340 --Update the parent member ID for the new child elements:
4341
4342 Update Pa_Rbs_Elements_Temp Tmp1
4343 Set New_Parent_Element_Id =
4344 (Select New_Element_Id
4345 From Pa_Rbs_Elements_Temp Tmp2
4346 Where Tmp1.Old_Parent_Element_Id = Tmp2.Old_Element_Id);
4347
4348
4349 --Copy all the elements of parent_resource_list to the newly created resource list
4350 /**********************************************************************
4351 * Bug - 3597011
4352 * Desc - While inserting we need to check for enabled_flag <> N
4353 ***********************************************************************/
4354
4355 -- hr_utility.trace('before copy members insert');
4356 -- hr_utility.trace('p_parent_resource_list_id is : ' || p_parent_resource_list_id);
4357 INSERT INTO pa_resource_list_members
4358 ( resource_list_member_id,
4359 RESOURCE_LIST_ID,
4360 RESOURCE_ID,
4361 ALIAS ,
4362 PARENT_MEMBER_ID,
4363 SORT_ORDER ,
4364 MEMBER_LEVEL,
4365 DISPLAY_FLAG ,
4366 ENABLED_FLAG ,
4367 TRACK_AS_LABOR_FLAG,
4368 last_updated_by,
4369 last_update_date,
4370 creation_date,
4371 created_by,
4372 last_update_login,
4373 ADW_NOTIFY_FLAG,
4374 FUNDS_CONTROL_LEVEL_CODE,
4375 PERSON_ID,
4376 JOB_ID,
4377 ORGANIZATION_ID,
4378 VENDOR_ID,
4379 EXPENDITURE_TYPE,
4380 EVENT_TYPE,
4381 NON_LABOR_RESOURCE,
4382 EXPENDITURE_CATEGORY,
4383 REVENUE_CATEGORY,
4384 NON_LABOR_RESOURCE_ORG_ID,
4385 EVENT_TYPE_CLASSIFICATION,
4386 SYSTEM_LINKAGE_FUNCTION,
4387 PROJECT_ROLE_ID,
4388 RESOURCE_FORMAT_ID,
4389 RESOURCE_TYPE_ID,
4390 RESOURCE_TYPE_CODE,
4391 OBJECT_TYPE,
4392 --3596702
4393 object_id,
4394 RES_FORMAT_ID,
4395 SPREAD_CURVE_ID,
4396 ETC_METHOD_CODE,
4397 MFC_COST_TYPE_ID,
4398 PERSON_TYPE_CODE,
4399 RES_TYPE_CODE,
4400 RESOURCE_CLASS_CODE,
4401 RESOURCE_CLASS_ID,
4402 RESOURCE_CLASS_FLAG,
4403 FC_RES_TYPE_CODE,
4404 BOM_RESOURCE_ID,
4405 INVENTORY_ITEM_ID,
4406 ITEM_CATEGORY_ID,
4407 TEAM_ROLE,
4408 MIGRATION_CODE,
4409 ATTRIBUTE_CATEGORY,
4410 ATTRIBUTE1,
4411 ATTRIBUTE2,
4412 ATTRIBUTE3,
4413 ATTRIBUTE4,
4414 ATTRIBUTE5,
4415 ATTRIBUTE6,
4416 ATTRIBUTE7,
4417 ATTRIBUTE8,
4418 ATTRIBUTE9,
4419 ATTRIBUTE10,
4420 ATTRIBUTE11,
4421 ATTRIBUTE12,
4422 ATTRIBUTE13,
4423 ATTRIBUTE14,
4424 ATTRIBUTE15,
4425 ATTRIBUTE16,
4426 ATTRIBUTE17,
4427 ATTRIBUTE18,
4428 ATTRIBUTE19,
4429 ATTRIBUTE20,
4430 ATTRIBUTE21,
4434 ATTRIBUTE25,
4431 ATTRIBUTE22,
4432 ATTRIBUTE23,
4433 ATTRIBUTE24,
4435 ATTRIBUTE26,
4436 ATTRIBUTE27,
4437 ATTRIBUTE28,
4438 ATTRIBUTE29,
4439 ATTRIBUTE30,
4440 record_version_number,
4441 INCURRED_BY_RES_FLAG,
4442 INCUR_BY_RES_CLASS_CODE,
4443 INCUR_BY_ROLE_ID,
4444 COPY_FROM_RL_FLAG,
4445 WP_ELIGIBLE_FLAG,
4446 --Bug 3636926
4447 UNIT_OF_MEASURE)
4448 --MIGRATED_RBS_ELEMENT_ID)
4449 SELECT /*+ use_nl (tmp, a) */ --For perf bug 4067435
4450 Tmp.New_Element_Id,
4451 X_RESOURCE_LIST_ID,
4452 a.RESOURCE_ID,
4453 a.ALIAS ,
4454 Tmp.New_Parent_Element_Id,
4455 a.SORT_ORDER ,
4456 a.MEMBER_LEVEL,
4457 a.DISPLAY_FLAG ,
4458 a.ENABLED_FLAG ,
4459 a.TRACK_AS_LABOR_FLAG,
4460 FND_GLOBAL.USER_ID,
4461 SYSDATE,
4462 SYSDATE,
4463 FND_GLOBAL.USER_ID,
4464 FND_GLOBAL.LOGIN_ID,
4465 a.ADW_NOTIFY_FLAG,
4466 a.FUNDS_CONTROL_LEVEL_CODE,
4467 a.PERSON_ID,
4468 a.JOB_ID,
4469 a.ORGANIZATION_ID,
4470 a.VENDOR_ID,
4471 a.EXPENDITURE_TYPE,
4472 a.EVENT_TYPE,
4473 a.NON_LABOR_RESOURCE,
4474 a.EXPENDITURE_CATEGORY,
4475 a.REVENUE_CATEGORY,
4476 a.NON_LABOR_RESOURCE_ORG_ID,
4477 a.EVENT_TYPE_CLASSIFICATION,
4478 a.SYSTEM_LINKAGE_FUNCTION,
4479 a.PROJECT_ROLE_ID,
4480 a.RESOURCE_FORMAT_ID,
4481 a.RESOURCE_TYPE_ID,
4482 a.RESOURCE_TYPE_CODE,
4483 a.OBJECT_TYPE,
4484 --3596702
4485 --X_RESOURCE_LIST_ID,
4486 decode(a.object_type, 'RESOURCE_LIST', X_RESOURCE_LIST_ID, NULL),
4487 a.RES_FORMAT_ID,
4488 a.SPREAD_CURVE_ID,
4489 a.ETC_METHOD_CODE,
4490 a.MFC_COST_TYPE_ID,
4491 a.PERSON_TYPE_CODE,
4492 a.RES_TYPE_CODE,
4493 a.RESOURCE_CLASS_CODE,
4494 a.RESOURCE_CLASS_ID,
4495 a.RESOURCE_CLASS_FLAG,
4496 a.FC_RES_TYPE_CODE,
4497 a.BOM_RESOURCE_ID,
4498 a.INVENTORY_ITEM_ID,
4499 a.ITEM_CATEGORY_ID,
4500 a.TEAM_ROLE,
4501 --'N',--Bug 3695679
4502 a.MIGRATION_CODE,
4503 a.ATTRIBUTE_CATEGORY,
4504 a.ATTRIBUTE1,
4505 a.ATTRIBUTE2,
4506 a.ATTRIBUTE3,
4507 a.ATTRIBUTE4,
4508 a.ATTRIBUTE5,
4509 a.ATTRIBUTE6,
4510 a.ATTRIBUTE7,
4511 a.ATTRIBUTE8,
4512 a.ATTRIBUTE9,
4513 a.ATTRIBUTE10,
4514 a.ATTRIBUTE11,
4515 a.ATTRIBUTE12,
4516 a.ATTRIBUTE13,
4517 a.ATTRIBUTE14,
4518 a.ATTRIBUTE15,
4519 a.ATTRIBUTE16,
4520 a.ATTRIBUTE17,
4521 a.ATTRIBUTE18,
4522 a.ATTRIBUTE19,
4523 a.ATTRIBUTE20,
4524 a.ATTRIBUTE21,
4525 a.ATTRIBUTE22,
4526 a.ATTRIBUTE23,
4527 a.ATTRIBUTE24,
4528 a.ATTRIBUTE25,
4529 a.ATTRIBUTE26,
4530 a.ATTRIBUTE27,
4531 a.ATTRIBUTE28,
4532 a.ATTRIBUTE29,
4533 a.ATTRIBUTE30,
4534 1,
4535 a.INCURRED_BY_RES_FLAG,
4536 a.INCUR_BY_RES_CLASS_CODE,
4537 a.INCUR_BY_ROLE_ID,
4538 a.COPY_FROM_RL_FLAG,
4539 a.WP_ELIGIBLE_FLAG,
4540 -- Bug 3636926
4541 a.UNIT_OF_MEASURE
4542 --a.MIGRATED_RBS_ELEMENT_ID
4543 From Pa_resource_list_members a, Pa_Rbs_Elements_Temp Tmp
4544 Where Tmp.Old_Element_Id = a.resource_list_member_id;
4545
4546 --End: Bug 3710189
4547 /*
4548 FROM
4549 pa_resource_list_members a
4550 WHERE
4551 a.resource_list_id=p_parent_resource_list_id
4552 AND
4553 a.object_type='RESOURCE_LIST'
4554 -- 3597011
4555 and a.enabled_flag <> 'N';
4556
4557 */
4558
4559 -- hr_utility.trace('after copy members insert');
4560
4561 IF Fnd_Api.To_Boolean(Nvl(P_Commit,Fnd_Api.G_False)) Then
4562
4563 Commit;
4564
4565 END IF;
4566
4567 EXCEPTION
4568
4569 When l_error THEN
4570 -- hr_utility.trace('when others l_error');
4571 null;
4572
4573 WHEN OTHERS THEN
4574 -- hr_utility.trace('when others unexp');
4575 x_return_status :='U';
4576 x_msg_data :=sqlerrm;
4577 x_msg_count :=1;
4578
4579 END COPY_RESOURCE_LIST;
4580
4581 /******************************************************
4582 * Procedure : Copy_Resource_Lists
4583 * Description : This API is used to copy all the
4584 * Resource list members for the resource_list_id's
4585 * associated to the source project -->
4586 * into the destination project.
4587 * If the resource_list is Centrally controlled.
4588 * Then do nothing. If it is not centrally controlled
4589 * then do the copy operation.
4590 *******************************************************/
4591 PROCEDURE Copy_Resource_Lists
4592 (p_source_project_id IN Number,
4593 p_destination_project_id IN Number,
4594 x_return_status OUT NOCOPY Varchar2)
4595 IS
4596 /***********************************************
4597 * Cursor to get all the resource_list_ID's
4598 * associated to the source project_id.
4599 **********************************************/
4600 --Bug 3494461
4604 SELECT resource_list_id
4601 -- Changed Pa_resource_list_assignments to pa_resource_list_assignments_v
4602 Cursor c_get_resource_list
4603 IS
4605 FROM pa_resource_list_assignments_v
4606 WHERE project_id = p_source_project_id;
4607
4608 l_resource_list_id pa_resource_list_members.resource_list_id%TYPE;
4609 l_control_flag Varchar2(1);
4610
4611 BEGIN
4612 X_Return_Status := Fnd_Api.G_Ret_Sts_Success;
4613 /*************************************
4614 * Open Cursor and fetch values
4615 * ************************************/
4616 OPEN c_get_resource_list;
4617 LOOP
4618 FETCH c_get_resource_list INTO l_resource_list_id;
4619 /*************************************************
4620 * If no values are returned from the cursor, that
4621 * is no resource lists are found associated to the
4622 * source project id then
4623 * raise no unexp error and return.
4624 ************************************************/
4625 IF c_get_resource_list%ROWCOUNT = 0 THEN
4626 /*******************************************************
4627 * Bug - 3595659
4628 * Desc - If no resource list found in the source project, then
4629 * just close the cursor and return. No UNEXP error needs
4630 * to be raised.
4631 ***********************************************************/
4632 -- X_Return_Status := Fnd_Api.G_Ret_Sts_UnExp_Error;
4633 Close c_get_resource_list;
4634 Return;
4635 END IF;
4636 EXIT WHEN c_get_resource_list%NOTFOUND;
4637 /***********************************************
4638 * Check to see if the resource list is centrally
4639 * controlled. If it is then do nothing.
4640 * Else do the COPY.
4641 ***************************************************/
4642 BEGIN
4643 SELECT control_flag
4644 INTO l_control_flag
4645 FROM pa_resource_lists_all_bg
4646 WHERE resource_list_id = l_resource_list_id;
4647 EXCEPTION
4648 WHEN OTHERS THEN
4649 X_Return_Status := Fnd_Api.G_Ret_Sts_UnExp_Error;
4650 Close c_get_resource_list;
4651 Return;
4652 END;
4653 /******************************************
4654 * If the resource list is not centrally controlled
4655 * then do the copy operation from the source
4656 * project to the destination project.
4657 **********************************************/
4658 IF l_control_flag <> 'Y' THEN
4659 BEGIN
4660 /************************************************
4661 * Insert resource list members into the
4662 * pa_resource_list_members table as those that
4663 * exist for the p_source_project_id.
4664 * The project_id should be the destination project id
4665 * and the resource_list_member_id should be the one from
4666 * the sequence.
4667 * *****************************************************/
4668 /******************************************************
4669 * Bug - 3591751
4670 * Desc - Inserting the wp_eligible_flag as in the source
4671 * resource_list.
4672 **********************************************************/
4673 /**********************************************************************
4674 * Bug - 3597011
4675 * Desc - While inserting we need to check for enabled_flag <> N
4676 ***********************************************************************/
4677 INSERT INTO PA_RESOURCE_LIST_MEMBERS
4678 ( RESOURCE_LIST_MEMBER_ID,
4679 RESOURCE_LIST_ID,
4680 RESOURCE_ID,
4681 ALIAS,
4682 DISPLAY_FLAG,
4683 ENABLED_FLAG,
4684 TRACK_AS_LABOR_FLAG,
4685 PERSON_ID,
4686 JOB_ID,
4687 ORGANIZATION_ID,
4688 VENDOR_ID,
4689 EXPENDITURE_TYPE,
4690 EVENT_TYPE,
4691 NON_LABOR_RESOURCE,
4692 EXPENDITURE_CATEGORY,
4693 REVENUE_CATEGORY,
4694 PROJECT_ROLE_ID,
4695 OBJECT_TYPE,
4696 OBJECT_ID,
4697 RESOURCE_CLASS_ID,
4698 RESOURCE_CLASS_CODE,
4699 RES_FORMAT_ID,
4700 SPREAD_CURVE_ID,
4701 ETC_METHOD_CODE,
4702 MFC_COST_TYPE_ID,
4703 COPY_FROM_RL_FLAG,
4704 RESOURCE_CLASS_FLAG,
4705 FC_RES_TYPE_CODE,
4706 INVENTORY_ITEM_ID,
4707 ITEM_CATEGORY_ID,
4708 MIGRATION_CODE,
4709 ATTRIBUTE_CATEGORY,
4710 ATTRIBUTE1,
4711 ATTRIBUTE2,
4712 ATTRIBUTE3 ,
4713 ATTRIBUTE4 ,
4714 ATTRIBUTE5 ,
4715 ATTRIBUTE6 ,
4716 ATTRIBUTE7 ,
4720 ATTRIBUTE11 ,
4717 ATTRIBUTE8 ,
4718 ATTRIBUTE9 ,
4719 ATTRIBUTE10 ,
4721 ATTRIBUTE12 ,
4722 ATTRIBUTE13 ,
4723 ATTRIBUTE14 ,
4724 ATTRIBUTE15 ,
4725 ATTRIBUTE16 ,
4726 ATTRIBUTE17 ,
4727 ATTRIBUTE18 ,
4728 ATTRIBUTE19 ,
4729 ATTRIBUTE20 ,
4730 ATTRIBUTE21 ,
4731 ATTRIBUTE22 ,
4732 ATTRIBUTE23 ,
4733 ATTRIBUTE24 ,
4734 ATTRIBUTE25 ,
4735 ATTRIBUTE26 ,
4736 ATTRIBUTE27 ,
4737 ATTRIBUTE28 ,
4738 ATTRIBUTE29 ,
4739 ATTRIBUTE30 ,
4740 RECORD_VERSION_NUMBER,
4741 PERSON_TYPE_CODE,
4742 BOM_RESOURCE_ID,
4743 TEAM_ROLE,
4744 INCURRED_BY_RES_FLAG,
4745 INCUR_BY_RES_CLASS_CODE,
4746 INCUR_BY_ROLE_ID,
4747 --3591751
4748 wp_eligible_flag,
4749 --Bug 3636926
4750 unit_of_measure,
4751 LAST_UPDATED_BY,
4752 LAST_UPDATE_DATE,
4753 CREATION_DATE,
4754 CREATED_BY,
4755 LAST_UPDATE_LOGIN)
4756 SELECT
4757 pa_resource_list_members_s.NEXTVAL,
4758 l_resource_list_id,
4759 a.RESOURCE_ID,
4760 a.ALIAS,
4761 a.DISPLAY_FLAG,
4762 a.ENABLED_FLAG,
4763 a.TRACK_AS_LABOR_FLAG,
4764 a.PERSON_ID,
4765 a.JOB_ID,
4766 a.ORGANIZATION_ID,
4767 a.VENDOR_ID,
4768 a.EXPENDITURE_TYPE,
4769 a.EVENT_TYPE,
4770 a.NON_LABOR_RESOURCE,
4771 a.EXPENDITURE_CATEGORY,
4772 a.REVENUE_CATEGORY,
4773 a.PROJECT_ROLE_ID,
4774 'PROJECT',
4775 p_destination_project_id,
4776 a.RESOURCE_CLASS_ID,
4777 a.RESOURCE_CLASS_CODE,
4778 a.RES_FORMAT_ID,
4779 a.SPREAD_CURVE_ID,
4780 a.ETC_METHOD_CODE,
4781 a.MFC_COST_TYPE_ID,
4782 a.COPY_FROM_RL_FLAG,
4783 a.RESOURCE_CLASS_FLAG,
4784 a.FC_RES_TYPE_CODE,
4785 a.INVENTORY_ITEM_ID,
4786 a.ITEM_CATEGORY_ID,
4787 a.MIGRATION_CODE,
4788 a.ATTRIBUTE_CATEGORY,
4789 a.ATTRIBUTE1,
4790 a.ATTRIBUTE2,
4791 a.ATTRIBUTE3 ,
4792 a.ATTRIBUTE4 ,
4793 a.ATTRIBUTE5 ,
4794 a.ATTRIBUTE6 ,
4795 a.ATTRIBUTE7 ,
4796 a.ATTRIBUTE8 ,
4797 a.ATTRIBUTE9 ,
4798 a.ATTRIBUTE10 ,
4799 a.ATTRIBUTE11 ,
4800 a.ATTRIBUTE12 ,
4801 a.ATTRIBUTE13 ,
4802 a.ATTRIBUTE14 ,
4803 a.ATTRIBUTE15 ,
4804 a.ATTRIBUTE16 ,
4805 a.ATTRIBUTE17 ,
4806 a.ATTRIBUTE18 ,
4807 a.ATTRIBUTE19 ,
4808 a.ATTRIBUTE20 ,
4809 a.ATTRIBUTE21 ,
4810 a.ATTRIBUTE22 ,
4811 a.ATTRIBUTE23 ,
4812 a.ATTRIBUTE24 ,
4813 a.ATTRIBUTE25 ,
4814 a.ATTRIBUTE26 ,
4815 a.ATTRIBUTE27 ,
4816 a.ATTRIBUTE28 ,
4817 a.ATTRIBUTE29 ,
4818 a.ATTRIBUTE30 ,
4819 a.RECORD_VERSION_NUMBER,
4820 a.PERSON_TYPE_CODE,
4821 a.BOM_RESOURCE_ID,
4822 a.TEAM_ROLE,
4823 a.INCURRED_BY_RES_FLAG,
4824 a.INCUR_BY_RES_CLASS_CODE,
4825 a.INCUR_BY_ROLE_ID,
4826 --3591751
4827 a.wp_eligible_flag,
4828 --Bug 3636926
4829 a.unit_of_measure,
4830 FND_GLOBAL.USER_ID,
4831 SYSDATE,
4832 SYSDATE,
4833 FND_GLOBAL.USER_ID,
4834 FND_GLOBAL.LOGIN_ID
4835 FROM pa_resource_list_members a
4836 WHERE a.resource_list_id = l_resource_list_id
4837 AND a.object_id = p_source_project_id
4838 AND a.object_type = 'PROJECT'
4839 -- 3597011
4840 and a.enabled_flag <> 'N'
4841 AND
4842 (a.resource_id,a.res_format_id,NVL(a.alias,'XXX'))
4843 IN
4847 AND object_id = p_source_project_id
4844 (SELECT resource_id,res_format_id,NVL(alias,'XXX')
4845 FROM pa_resource_list_members
4846 WHERE resource_list_id = l_resource_list_id
4848 AND object_type = 'PROJECT'
4849 MINUS
4850 SELECT resource_id,res_format_id,NVL(alias,'XXX')
4851 FROM pa_resource_list_members
4852 WHERE resource_list_id = l_resource_list_id
4853 AND object_id = p_destination_project_id
4854 AND object_type = 'PROJECT');
4855
4856 EXCEPTION
4857 WHEN OTHERS THEN
4858 Null;
4859 END;
4860 END IF;--L_central_control = Y
4861 END LOOP;--Res_list_ID's
4862 END Copy_Resource_Lists;
4863
4864
4865 -- History:
4866 --
4867 -- 03-FEB-2005 smullapp created
4868 -------------------------------------------------------------------
4869 --For bug 4139144
4870
4871 /******************************************************
4872 * Procedure : TRANSLATE_ROW
4873 * Description : This API is used to tranlslate all
4874 * translatable colmuns os pa_resource_lits_tl
4875 * table. This is called from the lct file.
4876 * **************************************************/
4877 procedure TRANSLATE_ROW(
4878 P_RESOURCE_LIST_ID in NUMBER ,
4879 P_OWNER in VARCHAR2 ,
4880 P_NAME in VARCHAR2 ,
4881 P_DESCRIPTION in VARCHAR2
4882 ) is
4883 begin
4884
4885 update pa_resource_lists_tl set
4886 NAME = P_NAME,
4887 DESCRIPTION = P_DESCRIPTION,
4888 LAST_UPDATE_DATE = sysdate,
4889 LAST_UPDATED_BY = decode(P_OWNER, 'SEED', 1, 0),
4890 LAST_UPDATE_LOGIN = 0,
4891 SOURCE_LANG = userenv('LANG')
4892 where resource_list_id = P_RESOURCE_LIST_ID
4893 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
4894
4895 if (sql%notfound) then
4896 raise no_data_found;
4897 end if;
4898
4899 end TRANSLATE_ROW;
4900
4901
4902 /******************************************************
4903 * Procedure : LOAD_ROW
4904 * Description : This API is used to update or insert rows
4905 * into table pa_resource_lists_bg and
4906 * pa_resource_lits_tl table. This procedure
4907 * is called from the lct file.
4908 * **************************************************/
4909 procedure LOAD_ROW(
4910 P_RESOURCE_LIST_ID in NUMBER,
4911 P_NAME in VARCHAR2,
4912 P_DESCRIPTION in VARCHAR2,
4913 P_PUBLIC_FLAG in VARCHAR2,
4914 P_GROUP_RESOURCE_TYPE_ID in NUMBER,
4915 P_START_DATE_ACTIVE in DATE,
4916 P_END_DATE_ACTIVE in DATE,
4917 P_UNCATEGORIZED_FLAG in VARCHAR2,
4918 P_BUSINESS_GROUP_ID in NUMBER,
4919 P_JOB_GROUP_ID in NUMBER,
4920 P_RESOURCE_LIST_TYPE in VARCHAR2,
4921 P_OWNER in VARCHAR2)
4922 IS
4923 user_id NUMBER;
4924 l_row_id VARCHAR2(64);
4925 l_resource_list_id NUMBER;
4926
4927 --Bug 4202015: Added this cursor
4928 CURSOR RES_CUR IS
4929 Select
4930 Rowid
4931 from
4932 PA_RESOURCE_LISTS_ALL_BG
4933 Where Resource_List_Id = P_Resource_List_Id;
4934
4935 BEGIN
4936
4937 IF(P_OWNER = 'SEED') THEN
4938 user_id := 1;
4939 else
4940 user_id :=0;
4941 END IF;
4942
4943 --Commented the following two Selects For Bug#5094347. These are not used anywhere in the code.
4944 /*SELECT ROWID
4945 INTO l_row_id
4946 FROM pa_resource_lists_all_bg
4947 WHERE resource_list_id = P_RESOURCE_LIST_ID;
4948
4949 SELECT nvl(p_resource_list_id,pa_resource_lists_s.NEXTVAL)
4950 INTO l_resource_list_id
4951 FROM dual;
4952 */
4953 --End of Commenting for Bug#5094347
4954
4955 /*Bug 4202015 - Changes Start*/
4956 --If we call PA_Resource_List_tbl_Pkg.Update_Row and then call PA_Resource_List_tbl_Pkg.Insert_Row
4957 --in case no_data_found exception is returned by previous API, then we get
4958 --unique constraint (PA.PA_RESOURCE_LISTS_U2) violation error in case table pa_resource_lists_tl
4959 --is empty. This is due to the fact that although no_data_found has been raised while updating
4960 --record in table pa_resource_lists_tl, we try to insert the same record in table
4961 --PA_RESOURCE_LISTS_ALL_BG in call to PA_Resource_List_tbl_Pkg.Insert_Row.
4962 --Hence we have coded this API to update the records directly in _BG and _TL tables
4963 --and in case of no_data_found exception we insert records in respective tables.
4964
4965 Update PA_RESOURCE_LISTS_ALL_BG
4966 SET
4967 NAME = P_NAME ,
4968 DESCRIPTION = P_DESCRIPTION ,
4969 PUBLIC_FLAG = P_PUBLIC_FLAG ,
4970 GROUP_RESOURCE_TYPE_ID = P_GROUP_RESOURCE_TYPE_ID ,
4971 START_DATE_ACTIVE = P_START_DATE_ACTIVE ,
4972 END_DATE_ACTIVE = P_END_DATE_ACTIVE ,
4973 UNCATEGORIZED_FLAG = P_UNCATEGORIZED_FLAG ,
4974 BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID ,
4975 JOB_GROUP_ID = P_JOB_GROUP_ID ,
4979 LAST_UPDATE_LOGIN = 0
4976 RESOURCE_LIST_TYPE = P_RESOURCE_LIST_TYPE ,
4977 LAST_UPDATED_BY = user_id ,
4978 LAST_UPDATE_DATE = sysdate ,
4980 WHERE RESOURCE_LIST_ID = P_RESOURCE_LIST_ID;
4981
4982 If SQL%NOTFOUND Then
4983 Insert Into PA_RESOURCE_LISTS_ALL_BG
4984 (
4985 RESOURCE_LIST_ID,
4986 NAME ,
4987 DESCRIPTION ,
4988 PUBLIC_FLAG ,
4989 GROUP_RESOURCE_TYPE_ID ,
4990 START_DATE_ACTIVE ,
4991 END_DATE_ACTIVE ,
4992 UNCATEGORIZED_FLAG ,
4993 BUSINESS_GROUP_ID ,
4994 JOB_GROUP_ID ,
4995 RESOURCE_LIST_TYPE,
4996 LAST_UPDATED_BY ,
4997 LAST_UPDATE_DATE,
4998 CREATION_DATE ,
4999 CREATED_BY ,
5000 LAST_UPDATE_LOGIN,
5001 CONTROL_FLAG,
5002 USE_FOR_WP_FLAG,
5003 MIGRATION_CODE
5004 )
5005 VALUES
5006 (
5007 P_RESOURCE_LIST_ID,
5008 P_NAME ,
5009 P_DESCRIPTION ,
5010 P_PUBLIC_FLAG ,
5011 P_GROUP_RESOURCE_TYPE_ID ,
5012 P_START_DATE_ACTIVE ,
5013 P_END_DATE_ACTIVE ,
5014 P_UNCATEGORIZED_FLAG ,
5015 P_BUSINESS_GROUP_ID ,
5016 P_JOB_GROUP_ID ,
5017 P_RESOURCE_LIST_TYPE,
5018 user_id ,
5019 sysdate,
5020 sysdate ,
5021 user_id ,
5022 0,
5023 'Y',
5024 'N', -- open issue
5025 NULL);
5026 end if;
5027
5028
5029 update pa_resource_lists_tl
5030 set
5031 NAME = P_NAME,
5032 DESCRIPTION = P_DESCRIPTION,
5033 LAST_UPDATE_DATE = sysdate,
5034 LAST_UPDATED_BY = fnd_global.user_id,
5035 LAST_UPDATE_LOGIN = fnd_global.login_id,
5036 SOURCE_LANG = userenv('LANG')
5037 where resource_list_id = P_RESOURCE_LIST_ID
5038 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
5039
5040 if (sql%notfound) then
5041 insert into pa_resource_lists_tl (
5042 LAST_UPDATE_LOGIN,
5043 CREATION_DATE,
5044 CREATED_BY,
5045 LAST_UPDATE_DATE,
5046 LAST_UPDATED_BY,
5047 RESOURCE_LIST_ID,
5048 NAME,
5049 DESCRIPTION,
5050 LANGUAGE,
5051 SOURCE_LANG)
5052 select
5053 FND_GLOBAL.LOGIN_ID,
5054 sysdate,
5055 FND_GLOBAL.USER_ID,
5056 sysdate,
5057 FND_GLOBAL.USER_ID,
5058 P_RESOURCE_LIST_ID,
5059 P_NAME,
5060 NVL(P_DESCRIPTION,P_NAME),
5061 L.LANGUAGE_CODE,
5062 userenv('LANG')
5063 from FND_LANGUAGES L
5064 where L.INSTALLED_FLAG in ('I', 'B')
5065 and not exists
5066 (select NULL
5067 from pa_resource_lists_tl T
5068 where T.RESOURCE_LIST_ID = P_RESOURCE_LIST_ID
5069 and T.LANGUAGE = L.LANGUAGE_CODE);
5070 end if;
5071
5072 Open Res_Cur;
5073 Fetch Res_Cur Into l_row_id;
5074 If (Res_Cur%NOTFOUND) then
5075 Close Res_Cur;
5076 Raise NO_DATA_FOUND;
5077 End If;
5078 Close Res_Cur;
5079
5080 /*Bug 4202015 - Changes End*/
5081
5082 --Commented the following code for Bug 4202015
5083 /*PA_Resource_List_tbl_Pkg.Update_Row(
5084 X_ROW_ID => l_row_id ,
5085 X_RESOURCE_LIST_ID => P_RESOURCE_LIST_ID ,
5086 X_NAME => P_NAME ,
5087 X_DESCRIPTION => P_DESCRIPTION ,
5088 X_PUBLIC_FLAG => P_PUBLIC_FLAG ,
5089 X_GROUP_RESOURCE_TYPE_ID => P_GROUP_RESOURCE_TYPE_ID,
5090 X_START_DATE_ACTIVE => P_START_DATE_ACTIVE ,
5091 X_END_DATE_ACTIVE => P_END_DATE_ACTIVE ,
5092 X_UNCATEGORIZED_FLAG => P_UNCATEGORIZED_FLAG ,
5093 X_BUSINESS_GROUP_ID => P_BUSINESS_GROUP_ID ,
5094 X_JOB_GROUP_ID => P_JOB_GROUP_ID ,
5095 X_RESOURCE_LIST_TYPE => P_RESOURCE_LIST_TYPE ,
5096 X_LAST_UPDATED_BY => user_id ,
5097 X_LAST_UPDATE_DATE => sysdate ,
5098 X_LAST_UPDATE_LOGIN => 0 );
5099
5100 EXCEPTION
5101 WHEN no_data_found then
5102 PA_Resource_List_tbl_Pkg.Insert_row(
5103 X_ROW_ID => l_row_id ,
5104 X_RESOURCE_LIST_ID => L_RESOURCE_LIST_ID ,
5105 X_NAME => P_NAME ,
5106 X_DESCRIPTION => P_DESCRIPTION ,
5107 X_PUBLIC_FLAG => P_PUBLIC_FLAG ,
5108 X_GROUP_RESOURCE_TYPE_ID => P_GROUP_RESOURCE_TYPE_ID ,
5109 X_START_DATE_ACTIVE => P_START_DATE_ACTIVE ,
5110 X_END_DATE_ACTIVE => P_END_DATE_ACTIVE ,
5111 X_UNCATEGORIZED_FLAG => P_UNCATEGORIZED_FLAG ,
5112 X_BUSINESS_GROUP_ID => P_BUSINESS_GROUP_ID ,
5113 X_JOB_GROUP_ID => P_JOB_GROUP_ID ,
5114 X_RESOURCE_LIST_TYPE => P_RESOURCE_LIST_TYPE ,
5115 X_LAST_UPDATED_BY => user_id ,
5116 X_LAST_UPDATE_DATE => sysdate ,
5117 X_CREATION_DATE => sysdate ,
5118 X_CREATED_BY => user_id ,
5119 X_LAST_UPDATE_LOGIN => 0 );*/
5120
5121 END LOAD_ROW;
5122
5123 END PA_CREATE_RESOURCE;