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