[Home] [Help]
PACKAGE BODY: APPS.PA_GET_RESOURCE
Source
1 PACKAGE BODY PA_GET_RESOURCE AS
2 /* $Header: PAGTRESB.pls 120.4 2006/05/10 16:33:20 ramurthy noship $*/
3
4 Procedure Get_Resource_group (p_resource_list_id In Number,
5 p_resource_group In Varchar2,
6 p_resource_list_member_id Out NOCOPY Number, --File.Sql.39 bug 4440895
7 p_resource_id Out NOCOPY Number, --File.Sql.39 bug 4440895
8 p_track_as_labor_flag Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
9 p_err_code Out NOCOPY Number, --File.Sql.39 bug 4440895
10 p_err_stage In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
11 p_err_stack In Out NOCOPY Varchar2) IS --File.Sql.39 bug 4440895
12 l_resource_type_id NUMBER;
13 l_resource_id NUMBER;
14 l_org_id NUMBER := NULL;
15
16 CURSOR c_resource_lists_csr IS
17 SELECT
18 group_resource_type_id
19 FROM
20 pa_resource_lists
21 WHERE resource_list_id = p_resource_list_id;
22
23 CURSOR c_resource_types_csr IS
24 SELECT
25 resource_type_code
26 FROM
27 pa_resource_types
28 WHERE resource_type_id = l_resource_type_id;
29
30 CURSOR c_resource_list_member_csr IS
31 SELECT
32 resource_list_member_id,
33 track_as_labor_flag
34 FROM
35 pa_resource_list_members
36 WHERE resource_list_id = p_resource_list_id
37 AND parent_member_id IS NULL
38 AND resource_id = l_resource_id;
39
40 CURSOR c_revenue_category_csr IS
41 SELECT
42 revenue_category_m
43 FROM
44 pa_revenue_categories_v
45 WHERE
46 revenue_category_code = p_resource_group;
47
48 CURSOR c_org_csr IS
49 SELECT
50 organization_name
51 FROM
52 pa_organizations_res_v
53 WHERE
54 organization_id = l_org_id ;
55
56 l_err_code NUMBER := 0;
57 l_resource_list_member_id NUMBER ;
58 l_old_stack VARCHAR2(2000);
59 l_resource_type_code VARCHAR2(30);
60 l_expenditure_category VARCHAR2(80):= NULL;
61 l_revenue_category VARCHAR2(80):= NULL;
62 l_resource_name VARCHAR2(80);
63
64 BEGIN
65 l_old_stack := p_err_stack;
66 p_err_code := 0;
67 p_err_stack := p_err_stack ||'->PA_GET_RESOURCE.Get_resource_group';
68 p_err_stage := ' Select group_resource_type_id from pa_resource_lists';
69
70 --- Get the group_resource_type_id of the resource_list
71 --- from pa_resource_lists using
72 --- x_resource_list_id.
73
74 OPEN c_resource_lists_csr;
75 FETCH c_resource_lists_csr INTO l_resource_type_id;
76 IF c_resource_lists_csr%NOTFOUND THEN
77 p_err_code := 10;
78 p_err_stage := 'PA_RL_INVALID';
79 CLOSE c_resource_lists_csr;
80 RETURN;
81 ELSE
82 CLOSE c_resource_lists_csr;
83 END IF;
84
85 -- If group_resource_type_id is 0 , then
86 -- the resource list has been grouped by None.Hence, do not proceed
87 IF l_resource_type_id = 0 THEN
88 p_resource_list_member_id := NULL;
89 p_track_as_labor_flag := NULL;
90 RETURN;
91 END IF;
92
93 p_err_stage := 'Select resource_type_code from pa_resource_types ';
94
95 --- Get the resource_type_code of the resource_type from
96 --- pa_resource_types using the resource_type_id.
97
98 OPEN c_resource_types_csr;
99 FETCH c_resource_types_csr INTO l_resource_type_code;
100 IF c_resource_types_csr%NOTFOUND THEN
101 p_err_code := 11;
102 p_err_stage := 'PA_RT_INVALID';
103 CLOSE c_resource_types_csr;
104 RETURN;
105 ELSE
106 CLOSE c_resource_types_csr;
107 END IF;
108 IF l_resource_type_code = 'EXPENDITURE_CATEGORY' THEN
109 l_expenditure_category := p_resource_group;
110 l_resource_name := p_resource_group;
111 ELSIF l_resource_type_code = 'REVENUE_CATEGORY' THEN
112 l_revenue_category := p_resource_group;
113 p_err_stage :=
114 ' Select revenue_category_m from pa_revenue_categories_v';
115 -- Need to get the revenue_category_m (Meaning) since what is passed
116 -- is the revenue_category_code
117 OPEN c_revenue_category_csr;
118 FETCH c_revenue_category_csr INTO l_resource_name;
119 IF c_revenue_category_csr%NOTFOUND THEN
120 p_err_code := 12;
121 p_err_stage := 'PA_INVALID_REV_CATEG';
122 CLOSE c_revenue_category_csr;
123 RETURN;
124 ELSE
125 CLOSE c_revenue_category_csr;
126 END IF;
127 ELSIF l_resource_type_code = 'ORGANIZATION' THEN
128 l_org_id := TO_NUMBER(p_resource_group);
129 p_err_stage :=
130 ' Select organization_name from pa_organizations_res_v';
131 -- Need to get the organization_name since what is passed
132 -- is the organization id
133 OPEN c_org_csr;
134 FETCH c_org_csr INTO l_resource_name;
135 IF c_org_csr%NOTFOUND THEN
136 p_err_code := 13;
137 p_err_stage := 'PA_INVALID_ORGANIZATION';
138 CLOSE c_org_csr;
139 RETURN;
140 ELSE
141 CLOSE c_org_csr;
142 END IF;
143 END IF;
144
145 --- To get the resource_list_member_id , we need the resource_id. hence
146 --- Check whether the resource_group has already been created as
147 --- a resource in PA_RESOURCE table and get the resource_id.
148 --- Hence, call Get_resource
149 PA_GET_RESOURCE.Get_Resource
150 (p_resource_name => l_resource_name,
151 p_resource_type_Code => l_resource_type_code,
152 p_person_id => NULL,
153 p_job_id => NULL,
154 p_proj_organization_id => l_org_id,
155 p_vendor_id => NULL,
156 p_expenditure_type => NULL,
157 p_event_type => NULL,
158 p_expenditure_category => l_expenditure_category,
159 p_revenue_category_code => l_revenue_category,
160 p_non_labor_resource => NULL,
161 p_system_linkage => NULL,
162 p_project_role_id => NULL,
163 p_resource_id => l_resource_id,
164 p_err_code => l_err_code,
165 p_err_stage => p_err_stage,
166 p_err_stack => p_err_stack );
167
168
169 IF l_err_code <> 0 THEN
170 p_err_code := l_err_code;
171 RETURN;
172 END IF;
173 --- If the resource_group has not been created as a resource yet,then
174 --- it means, it could not have been created as a resource_group yet.
175 --- l_Resource_id would be null in this case.
176 --- Hence return at this stage with p_resource_list_member_id as null.
177 IF l_resource_id IS NULL THEN
178 p_err_stack := l_old_stack;
179 p_resource_list_member_id := NULL;
180 p_resource_id := NULL;
181 p_track_as_labor_flag := NULL;
182 RETURN;
183 END IF;
184
185 p_err_stage :=
186 'Select resource_list_member_id from pa_resource_list_members';
187 OPEN c_resource_list_member_csr;
188 FETCH c_resource_list_member_csr INTO
189 l_resource_list_member_id,
190 p_track_as_labor_flag;
191 IF c_resource_list_member_csr%NOTFOUND THEN
192 p_resource_list_member_id := NULL;
193 p_track_as_labor_flag := NULL;
194 END IF;
195 CLOSE c_resource_list_member_csr;
196 p_resource_list_member_id := l_resource_list_member_id;
197 p_resource_id := l_resource_id;
198 p_err_stack := l_old_stack;
199
200 EXCEPTION
201 WHEN OTHERS THEN
202 p_err_code := SQLCODE;
203
204 -- 4537865 : Start : RESET other OUT PARAMS also
205 p_resource_list_member_id := NULL ;
206 p_resource_id := NULL ;
207 p_track_as_labor_flag := NULL ;
208
209 -- Dont reset p_err_stage as it will already be populated to correct value
210
211 p_err_stack := p_err_stack || ' : ' || SUBSTRB(SQLERRM,1,100);
212 Fnd_Msg_Pub.add_exc_msg
213 ( p_pkg_name => 'PA_GET_RESOURCE'
214 , p_procedure_name => 'Get_Resource_group'
215 , p_error_text => p_err_stack);
216 -- 4537865 : End
217
218 RAISE;
219 End Get_Resource_group;
220
221 --Name: Get_Resource_list_member
222 --Type: Procedure
223 --Description: This procedure retrieves the resource_list_member_id for a given
224 -- set of transaction attributes...
225 --
226 --Called subprograms: ?
227 --
228 --History:
229 -- xx-xxx-xxxx rkrishna Created
230 --
231 -- 16-MAR-2001 jwhite Bug 1685015: Forecast/Bgt Integration
232 -- 1. New IN-parameter, p_project_role_id, required.
233 --
234 Procedure Get_Resource_list_member (p_resource_list_id In Number,
235 p_resource_name In Varchar2,
236 p_resource_type_Code In Varchar2,
237 p_group_resource_type_id In Number,
238 p_person_id In Number,
239 p_job_id In Number,
240 p_proj_organization_id In Number,
241 p_vendor_id In Number,
242 p_expenditure_type In Varchar2,
243 p_event_type In Varchar2,
244 p_expenditure_category In Varchar2,
245 p_revenue_category_code In Varchar2,
246 p_non_labor_resource In Varchar2,
247 p_system_linkage In Varchar2,
248 p_parent_member_id In Number,
249 p_project_role_id IN NUMBER,
250 p_resource_id Out NOCOPY Number, --File.Sql.39 bug 4440895
251 p_resource_list_member_id Out NOCOPY Number, --File.Sql.39 bug 4440895
252 p_track_as_labor_flag Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
253 p_err_code Out NOCOPY Number, --File.Sql.39 bug 4440895
254 p_err_stage In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
255 p_err_stack In Out NOCOPY Varchar2) --File.Sql.39 bug 4440895
256 IS
257 l_old_stack VARCHAR2(2000);
258 l_err_code NUMBER := 0;
259 l_resource_id NUMBER;
260
261 CURSOR c_resource_list_member_csr_1 IS
262 SELECT
263 resource_list_member_id,
264 track_as_labor_flag
265 FROM
266 pa_resource_list_members
267 WHERE resource_list_id = p_resource_list_id
268 AND parent_member_id IS NULL
269 AND resource_id = l_resource_id;
270
271 CURSOR c_resource_list_member_csr_2 IS
272 SELECT
273 resource_list_member_id,
274 track_as_labor_flag
275 FROM
276 pa_resource_list_members
277 WHERE resource_list_id = p_resource_list_id
278 AND parent_member_id = p_parent_member_id
279 AND resource_id = l_resource_id;
280 BEGIN
281 l_old_stack := p_err_stack;
282 p_err_code := 0;
283 p_err_stack := p_err_stack ||'->PA_GET_RESOURCE.Get_Resource_list_member';
284 IF p_group_resource_type_id = 0 AND
285 p_parent_member_id IS NOT NULL THEN
286 -- This means the resource list has not been grouped.
287 ---Hence,parent_member_id should be null
288 p_err_code := 10;
289 p_err_stage := 'PA_RL_NOT_GROUPED';
290 RETURN;
291 ELSIF p_group_resource_type_id <> 0 AND
292 p_parent_member_id IS NULL THEN
293 -- This means the resource list has been grouped.
294 ---Hence,parent_member_id should not be null
295 p_err_code := 11;
296 p_err_stage := 'PA_RL_GROUPED';
297 RETURN;
298 END IF;
299 -- First need to get the resource_id of the input resource
300 -- Hence,call Get_resource
301
302 PA_GET_RESOURCE.Get_Resource
303 (p_resource_name => p_resource_name,
304 p_resource_type_Code => p_resource_type_code,
305 p_person_id => p_person_id,
306 p_job_id => p_job_id,
307 p_proj_organization_id => p_proj_organization_id,
308 p_vendor_id => p_vendor_id,
309 p_expenditure_type => p_expenditure_type,
310 p_event_type => p_event_type,
311 p_expenditure_category => p_expenditure_category,
312 p_revenue_category_code => p_revenue_category_code,
313 p_non_labor_resource => p_non_labor_resource,
314 p_system_linkage => p_system_linkage,
315 p_project_role_id => p_project_role_id,
316 p_resource_id => l_resource_id,
317 p_err_code => l_err_code,
318 p_err_stage => p_err_stage,
319 p_err_stack => p_err_stack );
320
321 IF l_err_code <> 0 THEN
322 p_err_code := l_err_code;
323 RETURN;
324 END IF;
325 --If l_resource_id is null, then the resource itself is yet to be created.
326 -- Hence,return resource_list_member_id and track_as_labor_flag as null
327
328 IF l_resource_id IS NULL THEN
329 p_err_stack := l_old_stack;
330 p_resource_list_member_id := NULL;
331 p_resource_id := NULL;
332 p_track_as_labor_flag := NULL;
336 p_err_stage :=
333 RETURN;
334 END IF;
335
337 'Select resource_list_member_id from pa_resource_list_members';
338 IF p_parent_member_id IS NULL THEN
339 OPEN c_resource_list_member_csr_1;
340 FETCH c_resource_list_member_csr_1 INTO
341 p_resource_list_member_id,
342 p_track_as_labor_flag;
343 IF c_resource_list_member_csr_1%NOTFOUND THEN
344 p_resource_list_member_id := NULL;
345 p_track_as_labor_flag := NULL;
346 END IF;
347 CLOSE c_resource_list_member_csr_1;
348 ELSIF p_parent_member_id IS NOT NULL THEN
349 OPEN c_resource_list_member_csr_2;
350 FETCH c_resource_list_member_csr_2 INTO
351 p_resource_list_member_id,
352 p_track_as_labor_flag;
353 IF c_resource_list_member_csr_2%NOTFOUND THEN
354 p_resource_list_member_id := NULL;
355 p_track_as_labor_flag := NULL;
356 END IF;
357 CLOSE c_resource_list_member_csr_2;
358 END IF;
359 p_resource_id := l_resource_id;
360 p_err_stack := l_old_stack;
361
362 EXCEPTION
363 WHEN OTHERS THEN
364 p_err_code := SQLCODE;
365
366 -- 4537865 : Start : RESET other OUT PARAMS also
367 p_resource_list_member_id := NULL ;
368 p_resource_id := NULL ;
369 p_track_as_labor_flag := NULL ;
370
371 -- Dont reset p_err_stage as it will already be populated to correct value
372
373 p_err_stack := p_err_stack || ' : ' || SUBSTRB(SQLERRM,1,100);
374 Fnd_Msg_Pub.add_exc_msg
375 ( p_pkg_name => 'PA_GET_RESOURCE'
376 , p_procedure_name => 'Get_Resource_list_member'
377 , p_error_text => p_err_stack);
378 -- 4537865 : End
379
380 RAISE;
381
382 END Get_Resource_list_member;
383
384
385 --Name: Get_Resource
386 --Type: Procedure
387 --Description: This procedure...
388 --
389 --Called subprograms: ?
390 --
391 -- History
392 --
393 -- xx-xxx-97 rkrishna - Created.
394 --
395 -- 22-APR-98 jwhite - For the Get_Resource procedure,
396 -- Converted the Dynamic SQL to hardcoded cursors
397 -- to address performance issues related to bug
398 -- #606398.
399 --
400 -- 16-MAR-2001 jwhite Bug 1685015: Forecast/Bgt Integration
401 -- 1. New IN-parameter, p_project_role_id, required.
402 -- 2. New cursor, resource_project_role_csr, required.
403 -- 3. New p_resource_type_code validation and
404 -- new error message.
405 -- 4. New fetch for new cursor.
406 --
407 --
408
409 Procedure Get_Resource (p_resource_name In Varchar2,
410 p_resource_type_Code In Varchar2,
411 p_person_id In Number,
412 p_job_id In Number,
413 p_proj_organization_id In Number,
414 p_vendor_id In Number,
415 p_expenditure_type In Varchar2,
416 p_event_type In Varchar2,
417 p_expenditure_category In Varchar2,
418 p_revenue_category_code In Varchar2,
419 p_non_labor_resource In Varchar2,
420 p_system_linkage In Varchar2,
421 p_project_role_id IN NUMBER,
422 p_resource_id Out NOCOPY Number, --File.Sql.39 bug 4440895
423 p_err_code Out NOCOPY Number, --File.Sql.39 bug 4440895
424 p_err_stage In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
425 p_err_stack In Out NOCOPY Varchar2) --File.Sql.39 bug 4440895
426 IS
427
428 CURSOR c_resource_types_csr IS
429 SELECT
430 resource_type_id
431 FROM
432 pa_resource_types
433 WHERE resource_type_code = p_resource_type_code;
434
435 --
436 -- 22-APR-98 ------------------------------------------------
437 -- Replaced dynamic SQL with hardcoded
438 -- cursors to enhance performance.
439
440 CURSOR resource_employee_csr (p_person_id NUMBER, l_resource_type_id NUMBER)
441 IS
442 SELECT a.resource_id
443 FROM pa_resources a, pa_resource_txn_attributes b
444 WHERE a.resource_type_id = l_resource_type_id
445 AND a.resource_id = b.resource_id
446 AND b.person_id = p_person_id;
447
448 CURSOR resource_job_csr (p_job_id NUMBER, l_resource_type_id NUMBER)
449 IS
450 SELECT a.resource_id
451 FROM pa_resources a, pa_resource_txn_attributes b
452 WHERE a.resource_type_id = l_resource_type_id
453 AND a.resource_id = b.resource_id
454 AND b.job_id = p_job_id ;
455
456 CURSOR resource_org_csr (p_proj_organization_id NUMBER, l_resource_type_id NUMBER)
457 IS
461 AND a.resource_id = b.resource_id
458 SELECT a.resource_id
459 FROM pa_resources a, pa_resource_txn_attributes b
460 WHERE a.resource_type_id = l_resource_type_id
462 AND b.organization_id = p_proj_organization_id;
463
464 CURSOR resource_vendor_csr (p_vendor_id NUMBER, l_resource_type_id NUMBER)
465 IS
466 SELECT a.resource_id
467 FROM pa_resources a, pa_resource_txn_attributes b
468 WHERE a.resource_type_id = l_resource_type_id
469 AND a.resource_id = b.resource_id
470 AND b.vendor_id = p_vendor_id;
471
472 CURSOR resource_exp_type_csr (p_expenditure_type VARCHAR2
473 , l_resource_type_id NUMBER)
474 IS
475 SELECT a.resource_id
476 FROM pa_resources a, pa_resource_txn_attributes b
477 WHERE a.resource_type_id = l_resource_type_id
478 AND a.resource_id = b.resource_id
479 AND b.expenditure_type = p_expenditure_type;
480
481 CURSOR resource_event_type_csr (p_event_type VARCHAR2, l_resource_type_id NUMBER)
482 IS
483 SELECT a.resource_id
484 FROM pa_resources a, pa_resource_txn_attributes b
485 WHERE a.resource_type_id = l_resource_type_id
486 AND a.resource_id = b.resource_id
487 AND b.event_type = p_event_type;
488
489 CURSOR resource_exp_cat_csr (p_expenditure_category VARCHAR2
490 , l_resource_type_id NUMBER)
491 IS
492 SELECT a.resource_id
493 FROM pa_resources a, pa_resource_txn_attributes b
494 WHERE a.resource_type_id = l_resource_type_id
495 AND a.resource_id = b.resource_id
496 AND b.expenditure_category = p_expenditure_category;
497
498
499 CURSOR resource_rev_cat_csr (p_revenue_category_code VARCHAR2
500 , l_resource_type_id NUMBER)
501 IS
502 SELECT a.resource_id
503 FROM pa_resources a, pa_resource_txn_attributes b
504 WHERE a.resource_type_id = l_resource_type_id
505 AND a.resource_id = b.resource_id
506 AND b.revenue_category = p_revenue_category_code;
507
508 CURSOR resource_project_role_csr (p_project_role_id NUMBER
509 , l_resource_type_id NUMBER)
510 IS
511 SELECT a.resource_id
512 FROM pa_resources a, pa_resource_txn_attributes b
513 WHERE a.resource_type_id = l_resource_type_id
514 AND a.resource_id = b.resource_id
515 AND b.project_role_id = p_project_role_id ;
516
517
518 -- -----------------------------------------------------------------
519
520 l_old_stack VARCHAR2(2000);
521 l_resource_type_id NUMBER;
522 l_resource_id NUMBER;
523
524
525 BEGIN
526 l_old_stack := p_err_stack;
527 p_err_code := 0;
528 p_err_stack := p_err_stack ||'->PA_GET_RESOURCE.Get_Resource';
529
530 -- Based on the Resource_type_code Ensure that the corresponding
531 -- attribute has a valid value.
532
533 IF (p_resource_type_code = 'EMPLOYEE' AND
534 p_person_id IS NULL) THEN
535 p_err_code := 10;
536 p_err_stage := 'PA_NO_PERSON_ID';
537 RETURN;
538 ELSIF (p_resource_type_code = 'JOB' AND
539 p_job_id IS NULL) THEN
540 p_err_code := 10;
541 p_err_stage := 'PA_NO_JOB_ID';
542 RETURN;
543 ELSIF (p_resource_type_code = 'ORGANIZATION' AND
544 p_proj_organization_id IS NULL) THEN
545 p_err_code := 10;
546 p_err_stage := 'PA_NO_PROJ_ORG_ID';
547 RETURN;
548 ELSIF (p_resource_type_code = 'VENDOR' AND
549 p_vendor_id IS NULL) THEN
550 p_err_code := 10;
551 p_err_stage := 'PA_NO_VENDOR_ID';
552 RETURN;
553 ELSIF (p_resource_type_code = 'EXPENDITURE_TYPE' AND
554 p_expenditure_type IS NULL) THEN
555 p_err_code := 10;
556 p_err_stage := 'PA_NO_EXPENDITURE_TYPE';
557 RETURN;
558 ELSIF (p_resource_type_code = 'EVENT_TYPE' AND
559 p_event_type IS NULL) THEN
560 p_err_code := 10;
561 p_err_stage := 'PA_NO_EVENT_TYPE';
562 RETURN;
563 ELSIF (p_resource_type_code = 'EXPENDITURE_CATEGORY' AND
564 p_expenditure_category IS NULL) THEN
565 p_err_code := 10;
566 p_err_stage := 'PA_NO_EXPENDITURE_CATEGORY';
567 RETURN;
568 ELSIF (p_resource_type_code = 'REVENUE_CATEGORY' AND
569 p_revenue_category_code IS NULL) THEN
570 p_err_code := 10;
571 p_err_stage := 'PA_NO_REVENUE_CATEGORY';
572 RETURN;
573 ELSIF (p_resource_type_code = 'PROJECT_ROLE' AND
574 p_project_role_id IS NULL) THEN
575 p_err_code := 10;
576 p_err_stage := 'PA_NO_PROJECT_ROLE_ID';
577 RETURN;
578 END IF;
579 p_err_stage := 'Select resource_type_id from pa_resource_types ';
580
581 --- Get the resource_type_id of the resource_type from
582 --- pa_resource_types using the resource_type_code.
583
584 OPEN c_resource_types_csr;
585 FETCH c_resource_types_csr INTO l_resource_type_id;
586 IF c_resource_types_csr%NOTFOUND THEN
587 p_err_code := 11;
588 p_err_stage := 'PA_RT_INVALID';
592 CLOSE c_resource_types_csr;
589 CLOSE c_resource_types_csr;
590 RETURN;
591 ELSE
593 END IF;
594
595 -- 22-APR-97, jwhite -----------------------------------------
596 -- Changed code to FETCH hardcoded cursors.
597 --
598
599 p_err_stage := 'Select resource_id from pa_resource_txn_attributes ';
600
601 IF (p_resource_type_code = 'EMPLOYEE') THEN
602 OPEN resource_employee_csr (p_person_id, l_resource_type_id);
603 FETCH resource_employee_csr INTO l_resource_id;
604 CLOSE resource_employee_csr;
605 ELSIF p_resource_type_code = 'JOB' THEN
606 OPEN resource_job_csr(p_job_id, l_resource_type_id);
607 FETCH resource_job_csr INTO l_resource_id;
608 CLOSE resource_job_csr;
609 ELSIF p_resource_type_code = 'ORGANIZATION' THEN
610 OPEN resource_org_csr(p_proj_organization_id, l_resource_type_id);
611 FETCH resource_org_csr INTO l_resource_id;
612 CLOSE resource_org_csr;
613 ELSIF p_resource_type_code = 'VENDOR' THEN
614 OPEN resource_vendor_csr(p_vendor_id, l_resource_type_id);
615 FETCH resource_vendor_csr INTO l_resource_id;
616 CLOSE resource_vendor_csr;
617 ELSIF p_resource_type_code = 'EXPENDITURE_TYPE' THEN
618 OPEN resource_exp_type_csr(p_expenditure_type, l_resource_type_id);
619 FETCH resource_exp_type_csr INTO l_resource_id;
620 CLOSE resource_exp_type_csr;
621 ELSIF p_resource_type_code = 'EVENT_TYPE' THEN
622 OPEN resource_event_type_csr(p_event_type, l_resource_type_id );
623 FETCH resource_event_type_csr INTO l_resource_id;
624 CLOSE resource_event_type_csr;
625 ELSIF p_resource_type_code = 'EXPENDITURE_CATEGORY' THEN
626 OPEN resource_exp_cat_csr(p_expenditure_category , l_resource_type_id );
627 FETCH resource_exp_cat_csr INTO l_resource_id;
628 CLOSE resource_exp_cat_csr;
629 ELSIF p_resource_type_code = 'REVENUE_CATEGORY' THEN
630 OPEN resource_rev_cat_csr(p_revenue_category_code, l_resource_type_id );
631 FETCH resource_rev_cat_csr INTO l_resource_id;
632 CLOSE resource_rev_cat_csr;
633 ELSIF p_resource_type_code = 'PROJECT_ROLE' THEN
634 OPEN resource_project_role_csr(p_project_role_id, l_resource_type_id);
635 FETCH resource_project_role_csr INTO l_resource_id;
636 CLOSE resource_project_role_csr;
637 END IF;
638 -- ----------------------------------------------------------
639
640 p_resource_id := l_resource_id;
641 p_err_stack := l_old_stack;
642
643 EXCEPTION
644 WHEN OTHERS THEN
645 p_err_code := SQLCODE;
646
647 -- 4537865
648 p_resource_id := NULL ;
649 p_err_stack := p_err_stack || ' ' || SUBSTRB(SQLERRM,1,100) ;
650
651 Fnd_Msg_Pub.add_exc_msg
652 ( p_pkg_name => 'PA_GET_RESOURCE'
653 , p_procedure_name => 'Get_Resource'
654 , p_error_text => p_err_stack);
655 -- 4537865 : dont reset p_err_stage as it will be already properly populated.
656
657 RAISE;
658 END Get_Resource;
659
660 --Name: Get_Resource_Information
661 --Type: Procedure
662 --Description: This procedure ...
663 --
664 --Called subprograms: ?
665 --
666 --History:
667 -- xx-xxx-xxxx rkrishna Created
668 --
669 -- 16-MAR-2001 jwhite Bug 1685015: Forecast/Bgt Integration
670 -- 1. New p_resource_type_code assignment.
671 -- 2. Error messaging for NO_DATA_FOUND
672 --
673 --
674 Procedure Get_Resource_Information (p_resource_type_Code In Varchar2,
675 p_resource_attr_value In Varchar2,
676 p_unit_of_measure Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
677 p_Rollup_quantity_flag Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
678 p_track_as_labor_flag Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
679 p_err_code Out NOCOPY Number, --File.Sql.39 bug 4440895
680 p_err_stage In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
681 p_err_stack In Out NOCOPY Varchar2) --File.Sql.39 bug 4440895
682 IS
683 l_old_stack VARCHAR2(2000);
684 l_resource_type_id NUMBER;
685 l_resource_id NUMBER;
686 l_cursor INTEGER;
687 l_statement VARCHAR2(2000);
688 l_rows INTEGER;
689 l_person_id NUMBER;
690 l_job_id NUMBER;
691 l_organization_id NUMBER;
692 l_vendor_id NUMBER;
693 l_uom VARCHAR2(30);
694 l_rollup_qty_flag VARCHAR2(1);
695 l_track_as_labor_flag VARCHAR2(1);
696 l_project_role_id NUMBER := NULL;
697
698 BEGIN
699 l_old_stack := p_err_stack;
700 p_err_code := 0;
701 p_err_stack := p_err_stack ||'->PA_GET_RESOURCE.Get_Resource_Information';
702
703 -- Based on the Resource_type_code form the dynamic SQL statement
704 -- to fetch from the appropriate views
705 -- Since all the Id fields like person_id,job_id etc are number fields
706 -- it is better to convert the value of p_resource_attr_value to
710 -- which would affect performance
707 -- number in case p_resource_type_code is 'EMPLOYEE','JOB' etc
708 -- and store in appropriate variables.This is to ensure
709 -- that the parser does not do an implicit conversion at runtime
711 -- For eg : if we use ' where person_id = p_resource_attr_value'
712 -- it is likely that the parser would interpret it as
713 -- ' where to_char(person_id) = p_resource_attr_value '
714 -- which might impact the performance
715
716 l_cursor := dbms_sql.open_cursor;
717
718 l_statement := 'Select unit_of_measure,rollup_quantity_flag ,'||
719 ' track_as_labor_flag from ';
720
721 IF (p_resource_type_code = 'EMPLOYEE') THEN
722 l_person_id := to_number (p_resource_attr_value);
723 l_statement :=
724 l_statement ||'pa_employees_res_v where person_id = :person_id ';
725 ELSIF p_resource_type_code = 'JOB' THEN
726 l_job_id := to_number (p_resource_attr_value);
727 l_statement :=
728 l_statement ||'pa_jobs_res_v where job_id = :job_id ';
729 ELSIF p_resource_type_code = 'ORGANIZATION' THEN
730 l_organization_id := to_number (p_resource_attr_value);
731 l_statement :=
732 l_statement ||'pa_organizations_res_v ' ||
733 ' where organization_id = :organization_id ';
734 ELSIF p_resource_type_code = 'VENDOR' THEN
735 l_vendor_id := to_number (p_resource_attr_value);
736 l_statement :=
737 l_statement ||'pa_vendors_res_v where vendor_id = :vendor_id ';
738 ELSIF p_resource_type_code = 'EXPENDITURE_TYPE' THEN
739 l_statement :=
740 l_statement ||'pa_expenditure_types_res_v ' ||
741 ' where expenditure_type = :expenditure_type ';
742 ELSIF p_resource_type_code = 'EVENT_TYPE' THEN
743 l_statement :=
744 l_statement ||'pa_event_types_res_v ' ||
745 ' where event_type = :event_type ';
746 ELSIF p_resource_type_code = 'EXPENDITURE_CATEGORY' THEN
747 l_statement :=
748 l_statement ||'pa_expend_categories_res_v ' ||
749 ' where expenditure_category = :expenditure_category ';
750 ELSIF p_resource_type_code = 'REVENUE_CATEGORY' THEN
751 l_statement :=
752 l_statement ||'pa_revenue_categories_res_v ' ||
753 ' where revenue_category_code = :revenue_category_code ';
754 ELSIF p_resource_type_code = 'PROJECT_ROLE' THEN
755 l_project_role_id := to_number(p_resource_attr_value);
756 l_statement :=
757 l_statement ||'pa_project_roles_res_v where project_role_id = :project_role_id ';
758 END IF;
759
760 dbms_sql.parse(l_cursor, l_statement, dbms_sql.native);
761
762 IF (p_resource_type_code = 'EMPLOYEE') THEN
763 dbms_sql.bind_variable
764 (l_cursor, 'person_id', l_person_id );
765 ELSIF p_resource_type_code = 'JOB' THEN
766 dbms_sql.bind_variable
767 (l_cursor, 'job_id', l_job_id );
768 ELSIF p_resource_type_code = 'ORGANIZATION' THEN
769 dbms_sql.bind_variable
770 (l_cursor, 'organization_id', l_organization_id );
771 ELSIF p_resource_type_code = 'VENDOR' THEN
772 dbms_sql.bind_variable
773 (l_cursor, 'vendor_id', l_vendor_id );
774 ELSIF p_resource_type_code = 'EXPENDITURE_TYPE' THEN
775 dbms_sql.bind_variable
776 (l_cursor, 'expenditure_type', p_resource_attr_value );
777 ELSIF p_resource_type_code = 'EVENT_TYPE' THEN
778 dbms_sql.bind_variable
779 (l_cursor, 'event_type', p_resource_attr_value );
780 ELSIF p_resource_type_code = 'EXPENDITURE_CATEGORY' THEN
781 dbms_sql.bind_variable
782 (l_cursor, 'expenditure_category', p_resource_attr_value );
783 ELSIF p_resource_type_code = 'REVENUE_CATEGORY' THEN
784 dbms_sql.bind_variable
785 (l_cursor, 'revenue_category_code', p_resource_attr_value );
786 ELSIF p_resource_type_code = 'PROJECT_ROLE' THEN
787 dbms_sql.bind_variable
788 (l_cursor, 'project_role_id', l_project_role_id );
789 END IF;
790 p_err_stage := 'Select unit_of_measure...from ... ';
791 dbms_sql.define_column (l_cursor, 1, l_uom,30);
792 dbms_sql.define_column (l_cursor, 2, l_rollup_qty_flag,1);
793 dbms_sql.define_column (l_cursor, 3, l_track_as_labor_flag,1);
794
795 l_rows := dbms_sql.execute(l_cursor);
796 IF dbms_sql.fetch_rows( l_cursor ) > 0 THEN
797 dbms_sql.column_value (l_cursor, 1, l_uom);
798 dbms_sql.column_value (l_cursor, 2, l_rollup_qty_flag);
799 dbms_sql.column_value (l_cursor, 3, l_track_as_labor_flag);
800 p_unit_of_measure := l_uom;
801 p_rollup_quantity_flag := l_rollup_qty_flag;
802 p_track_as_labor_flag := l_track_as_labor_flag;
803 ELSE -- if no rows were returned then the input is not a valid
804 -- resource for that resource type.Hence, we need to raise
805 -- error
806 p_unit_of_measure := NULL;
807 p_rollup_quantity_flag := NULL;
808 p_track_as_labor_flag := NULL;
809 IF (p_resource_type_code = 'EMPLOYEE') THEN
813 p_err_code := 10;
810 p_err_code := 10;
811 p_err_stage := 'PA_INVALID_EMPLOYEE';
812 ELSIF p_resource_type_code = 'JOB' THEN
814 p_err_stage := 'PA_INVALID_JOB';
815 ELSIF p_resource_type_code = 'ORGANIZATION' THEN
816 p_err_code := 10;
817 p_err_stage := 'PA_INVALID_ORGANIZATION';
818 ELSIF p_resource_type_code = 'VENDOR' THEN
819 p_err_code := 10;
820 p_err_stage := 'PA_INVALID_VENDOR';
821 ELSIF p_resource_type_code = 'EXPENDITURE_TYPE' THEN
822 p_err_code := 10;
823 p_err_stage := 'PA_INVALID_EXPENDITURE_TYPE';
824 ELSIF p_resource_type_code = 'EVENT_TYPE' THEN
825 p_err_code := 10;
826 p_err_stage := 'PA_INVALID_EVENT_TYPE';
827 ELSIF p_resource_type_code = 'EXPENDITURE_CATEGORY' THEN
828 p_err_code := 10;
829 p_err_stage := 'PA_INVALID_EXP_CATEGORY';
830 ELSIF p_resource_type_code = 'REVENUE_CATEGORY' THEN
831 p_err_code := 10;
832 p_err_stage := 'PA_INVALID_REV_CATEG';
833 ELSIF p_resource_type_code = 'PROJECT_ROLE' THEN
834 p_err_code := 10;
835 p_err_stage := 'PA_INVALID_PROJECT_ROLE';
836 END IF;
837 RETURN;
838 END IF;
839 IF dbms_sql.is_open (l_cursor) THEN
840 dbms_sql.close_cursor (l_cursor);
841 END IF;
842 p_err_stack := l_old_stack;
843
844 -- 4537865 Included Exception Handling - WHEN OTHERS Block
845 EXCEPTION
846 WHEN OTHERS THEN
847 p_unit_of_measure := NULL ;
848 p_Rollup_quantity_flag:= NULL ;
849 p_track_as_labor_flag := NULL ;
850 p_err_code := SQLCODE;
851 p_err_stack := p_err_stack || ' : ' || SUBSTRB(SQLERRM,1,100);
852
853 Fnd_Msg_Pub.add_exc_msg
854 ( p_pkg_name => 'PA_GET_RESOURCE'
855 , p_procedure_name => 'Get_Resource_Information'
856 , p_error_text => p_err_stack);
857 RAISE;
858 End Get_Resource_Information;
859
860 Procedure Get_Uncateg_Resource_Info (p_resource_list_id Out NOCOPY Number, --File.Sql.39 bug 4440895
861 p_resource_list_member_id Out NOCOPY Number, --File.Sql.39 bug 4440895
862 p_resource_id Out NOCOPY Number, --File.Sql.39 bug 4440895
863 p_track_as_labor_flag Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
864 p_err_code Out NOCOPY Number, --File.Sql.39 bug 4440895
865 p_err_stage In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
866 p_err_stack In Out NOCOPY Varchar2) --File.Sql.39 bug 4440895
867
868 IS
869 l_old_stack VARCHAR2(2000);
870
871 CURSOR resource_list_uncateg_csr IS
872 SELECT
873 rl.resource_list_id,
874 rlm.resource_list_member_id,
875 rlm.resource_id,
876 rlm.track_as_labor_flag
877 FROM
878 pa_resource_lists rl,
879 pa_resource_list_members rlm
880 WHERE rl.uncategorized_flag = 'Y'
881 AND rlm.resource_class_code = 'FINANCIAL_ELEMENTS' -- shelly
882 AND rlm.resource_class_flag = 'Y' --shelly
883 AND rl.resource_list_id = rlm.resource_list_id
884 AND rl.business_group_id = pa_utils.business_group_id; /* Added for Bug 2373165 */
885
886 BEGIN
887 l_old_stack := p_err_stack;
888 p_err_code := 0;
889 p_err_stack := p_err_stack ||'->PA_GET_RESOURCE.Get_Uncateg_Resource_Info';
890
891 OPEN resource_list_uncateg_csr;
892 FETCH resource_list_uncateg_csr INTO
893 p_resource_list_id,
894 p_resource_list_member_id,
895 p_resource_id,
896 p_track_as_labor_flag;
897 IF resource_list_uncateg_csr%NOTFOUND THEN
898 CLOSE resource_list_uncateg_csr;
899 RAISE NO_DATA_FOUND;
900 ELSE
901 CLOSE resource_list_uncateg_csr;
902 END IF;
903 p_err_stack := l_old_stack;
904
905 EXCEPTION
906 WHEN OTHERS THEN
907 p_err_code := SQLCODE;
908 -- 4537865 : RESET other out params also.
909
910 p_resource_list_member_id := NULL ;
911 p_resource_id := NULL ;
912 p_track_as_labor_flag := NULL ;
913
914 p_err_stack := p_err_stack || ' : ' || SUBSTRB(SQLERRM,1,100);
915 Fnd_Msg_Pub.add_exc_msg
916 ( p_pkg_name => 'PA_GET_RESOURCE'
917 , p_procedure_name => 'Get_Uncateg_Resource_Info'
918 , p_error_text => p_err_stack);
919 -- 4537865 : End
920 RAISE;
921
922 END Get_Uncateg_Resource_Info;
923
924 Procedure Get_Unclassified_Member (p_resource_list_id In Number,
925 p_parent_member_id In Number,
926 p_unclassified_resource_id In Number,
927 p_resource_list_member_id Out NOCOPY Number, --File.Sql.39 bug 4440895
928 p_track_as_labor_flag Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
932
929 p_err_code Out NOCOPY Number, --File.Sql.39 bug 4440895
930 p_err_stage In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
931 p_err_stack In Out NOCOPY Varchar2) --File.Sql.39 bug 4440895
933 IS
934 l_old_stack varchar2(2000);
935
936 CURSOR res_list_member_csr_1 IS
937 SELECT
938 resource_list_member_id,
939 track_as_labor_flag
940 FROM
941 pa_resource_list_members
942 WHERE resource_list_id = p_resource_list_id
943 AND parent_member_id IS NULL
944 AND resource_id = p_unclassified_resource_id;
945
946 CURSOR res_list_member_csr_2 IS
947 SELECT
948 resource_list_member_id,
949 track_as_labor_flag
950 FROM
951 pa_resource_list_members
952 WHERE resource_list_id = p_resource_list_id
953 AND parent_member_id = p_parent_member_id
954 AND resource_id = p_unclassified_resource_id;
955
956 BEGIN
957 l_old_stack := p_err_stack;
958 p_err_code := 0;
959 p_err_stack := p_err_stack ||'->PA_GET_RESOURCE.Get_Unclassified_Member';
960
961 ---If parent_member_id is Null, then need to return the
962 --- resource_list_member_id of the Unclassified resource at the Resource
963 --- list level;
964 ---else
965 --- return the resource_list_member_id of the unclassified resource
966 --- at the resource_group level
967
968 IF p_parent_member_id IS NULL THEN
969 OPEN res_list_member_csr_1;
970 FETCH res_list_member_csr_1 INTO
971 p_resource_list_member_id,
972 p_track_as_labor_flag;
973 IF res_list_member_csr_1%NOTFOUND THEN
974 CLOSE res_list_member_csr_1;
975 RAISE NO_DATA_FOUND;
976 ELSE
977 CLOSE res_list_member_csr_1;
978 END IF;
979 ELSE
980 OPEN res_list_member_csr_2;
981 FETCH res_list_member_csr_2 INTO
982 p_resource_list_member_id,
983 p_track_as_labor_flag;
984 IF res_list_member_csr_2%NOTFOUND THEN
985 p_resource_list_member_id := NULL;
986 p_track_as_labor_flag := NULL;
987 END IF;
988 CLOSE res_list_member_csr_2;
989 END IF;
990
991 p_err_stack := l_old_stack;
992
993 EXCEPTION
994 WHEN OTHERS THEN
995 p_err_code := SQLCODE;
996 -- 4537865 : Start
997 p_resource_list_member_id := NULL;
998 p_track_as_labor_flag := NULL;
999 p_err_stack := p_err_stack || ': ' || SUBSTRB(SQLERRM,1,100);
1000
1001 Fnd_Msg_Pub.add_exc_msg
1002 ( p_pkg_name => 'PA_GET_RESOURCE'
1003 , p_procedure_name => 'Get_Unclassified_Member'
1004 , p_error_text => p_err_stack);
1005 -- 4537865 : ENd
1006 RAISE;
1007
1008 END Get_Unclassified_Member;
1009
1010 Procedure Get_Unclassified_Resource (p_resource_id Out NOCOPY Number, --File.Sql.39 bug 4440895
1011 p_resource_name Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
1012 p_track_as_labor_flag Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
1013 p_unit_of_measure Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
1014 p_rollup_quantity_flag Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
1015 p_err_code Out NOCOPY Number, --File.Sql.39 bug 4440895
1016 p_err_stage In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
1017 p_err_stack In Out NOCOPY Varchar2) --File.Sql.39 bug 4440895
1018
1019 IS
1020 CURSOR unclassified_res_csr IS
1021 SELECT par.resource_id,
1022 par.name,
1023 par.track_as_labor_flag,
1024 par.unit_of_measure,
1025 par.rollup_quantity_flag
1026 FROM pa_resources par,
1027 pa_resource_types part
1028 WHERE part.resource_type_code = 'UNCLASSIFIED'
1029 AND part.resource_type_id = par.resource_type_id;
1030
1031 l_old_stack varchar2(2000);
1032 BEGIN
1033 l_old_stack := p_err_stack;
1034 p_err_code := 0;
1035 p_err_stack := p_err_stack ||'->PA_GET_RESOURCE.Get_Unclassified_Resource';
1036
1037 OPEN unclassified_res_csr;
1038 FETCH unclassified_res_csr INTO
1039 p_resource_id,
1040 p_resource_name,
1041 p_track_as_labor_flag,
1042 p_unit_of_measure,
1043 p_rollup_quantity_flag;
1044 IF unclassified_res_csr%NOTFOUND THEN
1045 CLOSE unclassified_res_csr;
1046 RAISE NO_DATA_FOUND;
1047 END IF;
1048 CLOSE unclassified_res_csr;
1049 p_err_stack := l_old_stack;
1050 EXCEPTION
1051 WHEN OTHERS THEN
1052 p_err_code := SQLCODE;
1053 -- 4537865 : Start
1054 p_resource_id := NULL ;
1055 p_resource_name := NULL ;
1056 p_track_as_labor_flag := NULL ;
1057 p_unit_of_measure := NULL ;
1058 p_rollup_quantity_flag := NULL ;
1062 , p_procedure_name => 'Get_Unclassified_Resource'
1059 p_err_stack := p_err_stack || ' : ' || SUBSTRB(SQLERRM,1,100);
1060 Fnd_Msg_Pub.add_exc_msg
1061 ( p_pkg_name => 'PA_GET_RESOURCE'
1063 , p_error_text => p_err_stack);
1064 -- 4537865 : End
1065
1066 RAISE;
1067 END Get_Unclassified_Resource;
1068
1069 FUNCTION Include_Inactive_Resources RETURN VARCHAR2 IS
1070 -- This function returns the value in the Package variable
1071 -- G_include_inactive_res_flag. It returns 'Y' or 'N'
1072 -- which serves as the basis for some resource views
1073 -- to determine whether to return inactive resources or not
1074
1075 BEGIN
1076 RETURN G_include_inactive_res_flag;
1077 END Include_Inactive_Resources;
1078
1079 PROCEDURE Set_Inactive_Resources_Flag (p_set_flag IN VARCHAR2) IS
1080
1081 BEGIN
1082 G_include_inactive_res_flag := p_Set_Flag;
1083
1084 END Set_Inactive_Resources_Flag;
1085
1086
1087
1088 FUNCTION Child_resource_exists
1089 -- This function checks existence of child level resource member
1090 -- for a resource member . It is using pa_project_accum_headers
1091 -- to ensure that for the specified project and task that resource
1092 -- was used for accumulation .This is done because this function is
1093 -- called from project status inquiry
1094 (p_resource_id number ,
1095 p_task_id number,
1096 p_project_id number
1097 )
1098 RETURN VARCHAR2 is
1099 rv varchar2(1) ;
1100 temp number;
1101 begin
1102 begin
1103 select 1
1104 into temp
1105 from sys.dual where
1106 exists ( select 1 from pa_resource_list_members p,pa_project_accum_headers h
1107 where p.resource_list_member_id = h.resource_list_member_id
1108 and p.parent_member_id = p_resource_id
1109 and h.project_id = p_project_id
1110 and h.task_id = p_task_id );
1111 rv := 'Y';
1112 exception
1113 when NO_DATA_FOUND then
1114 rv := 'N';
1115 end;
1116 return rv;
1117 End Child_resource_exists;
1118
1119 -- added by jayashree on sept 24' 98
1120 Procedure delete_resource_list_ok(l_resource_list_id NUMBER,
1121 p_is_plan_res_list IN VARCHAR2 default 'N',
1122 x_err_code IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
1123 x_err_stage IN OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
1124
1125 l_dummy VARCHAR2(1);
1126
1127 --Check for Resource List Members OTHER THAN UNclassified
1128 CURSOR l_members_csr (l_resource_list_id NUMBER)
1129 IS
1130 SELECT 'x'
1131 FROM dual
1132 WHERE exists
1133 (select 'x'
1134 from pa_resource_list_members rlm
1135 , pa_resources r
1136 , pa_resource_types rt
1137 where
1138 rlm.resource_list_id = l_resource_list_id
1139 and rlm.resource_id = r.resource_id
1140 and r.resource_type_id = rt.resource_type_id
1141 and rt.resource_type_code <> 'UNCLASSIFIED');
1142
1143
1144 -- Check for Resource List in Resource List Assignments
1145 CURSOR l_list_assignments_csr (l_resource_list_id NUMBER)
1146 IS
1147 SELECT 'x'
1148 FROM dual
1149 WHERE exists
1150 (select 'x'
1151 from pa_resource_list_assignments rla
1152 where rla.resource_list_id = l_resource_list_id);
1153
1154 -- Check for Resource List in Project Types All
1155
1156 -- Modified for perf bug 4887375
1157 Cursor check_resource_list_csr (l_resource_list_id NUMBER)
1158 IS
1159 Select 'X'
1160 From dual
1161 Where exists
1162 (Select null
1163 From pa_project_types_all pa
1164 Where pa.DEFAULT_RESOURCE_LIST_ID = l_resource_list_id
1165 Or pa.COST_BUDGET_RESOURCE_LIST_ID = l_resource_list_id
1166 Or pa.REV_BUDGET_RESOURCE_LIST_ID = l_resource_list_id);
1167
1168 -- Check for Resource List in Budget Version
1169
1170 -- Modified for perf bug 4887375
1171 Cursor check_resource_budget_list (l_resource_list_id NUMBER)
1172 IS
1173 Select 'X'
1174 From dual
1175 Where exists
1176 (Select null
1177 From pa_budget_versions
1178 Where resource_list_id = l_resource_list_id);
1179
1180 -- Check for Resource list in Proj_Fp_Options
1181
1182 Cursor check_resource_proj_fp_list (l_resource_list_id NUMBER)
1183 IS
1184 Select 'X'
1185 From pa_proj_fp_options
1186 Where all_resource_list_id = l_resource_list_id
1187 OR cost_resource_list_id = l_resource_list_id
1188 OR revenue_resource_list_id = l_resource_list_id;
1189
1190 l_resource_list_pa varchar2(1);
1191
1192 BEGIN
1193
1194 x_err_code := 0;
1195
1196 -- VALIDATION LAYER ---------------------------------------------------------
1197
1198 -- Check for Resource List Members OTHER THAN UNclassified
1199
1200 IF p_is_plan_res_list <> 'Y' THEN
1201 OPEN l_members_csr (l_resource_list_id);
1202 FETCH l_members_csr INTO l_dummy;
1203 IF l_members_csr%FOUND THEN
1204 x_err_code := 10;
1205 x_err_stage := 'PA_RSRC_LIST_HAS_MEMBERS';
1206 return;
1207 END IF;
1208 CLOSE l_members_csr;
1209 END IF;
1210
1214 FETCH l_list_assignments_csr INTO l_dummy;
1211 -- Check for Resource List in Resource List Assignements
1212
1213 OPEN l_list_assignments_csr (l_resource_list_id);
1215 IF l_list_assignments_csr%FOUND THEN
1216 x_err_code := 20;
1217 x_err_stage := 'PA_RSRC_LIST_USED_ASSIGNMENTS';
1218 return;
1219 END IF;
1220 CLOSE l_list_assignments_csr;
1221
1222 -- Check for Resource List in Project Type All
1223
1224 Open check_resource_list_csr (l_resource_list_id);
1225 Fetch check_resource_list_csr INTO l_dummy;
1226
1227 if check_resource_list_csr%FOUND then
1228 x_err_code := 30;
1229 x_err_stage := 'PA_RL_PT_USED';
1230 return;
1231 end if;
1232 Close check_resource_list_csr;
1233
1234 -- Check for Resource List in Budget Version
1235
1236 Open check_resource_budget_list (l_resource_list_id);
1237 Fetch check_resource_budget_list INTO l_dummy;
1238
1239 if check_resource_budget_list%FOUND then
1240 x_err_code := 40;
1241 x_err_stage := 'PA_RL_BUDGET_USED';
1242 return;
1243 end if;
1244 Close check_resource_budget_list;
1245
1246 -- Check for Resource List in Proj_FP_Options
1247
1248 Open check_resource_proj_fp_list (l_resource_list_id);
1249 Fetch check_resource_proj_fp_list INTO l_dummy;
1250
1251 if check_resource_proj_fp_list%FOUND then
1252 x_err_code := 45;
1253 x_err_stage := 'PA_RL_PROJ_FP_USED';
1254 return;
1255 end if;
1256 Close check_resource_proj_fp_list;
1257
1258 --Check for resource list in project allocations
1259 l_resource_list_pa := PA_ALLOC_UTILS.is_resource_list_in_rules(l_resource_list_id);
1260 If l_resource_list_pa = 'Y' Then
1261 x_err_code := 50;
1262 x_err_stage := 'PA_RES_LIST_EXISTS_PROJ_ALLOC';
1263 return;
1264 End If;
1265 Exception
1266 when others then
1267 x_err_code := SQLCODE;
1268 x_err_stage := SQLERRM;
1269 rollback;
1270 return;
1271
1272 END delete_resource_list_ok;
1273
1274 Procedure delete_resource_list_member_ok(l_resource_list_id NUMBER,l_resource_list_member_id NUMBER,x_err_code IN OUT NOCOPY NUMBER,x_err_stage IN OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
1275
1276 l_dummy VARCHAR2(1);
1277
1278 -- Check for Resource List Member Id in Project Accum Headers
1279
1280 CURSOR l_accum_headers_csr (l_resource_list_id NUMBER,
1281 l_resource_list_member_id NUMBER)
1282 IS
1283 SELECT 'x'
1284 FROM dual
1285 WHERE exists
1286 (select 'x'
1287 from pa_resource_list_assignments rla, pa_project_accum_headers pah
1288 where rla.resource_list_id = l_resource_list_id
1289 and rla.project_id = pah.project_id
1290 and rla.resource_list_id = pah.resource_list_id
1291 and pah.resource_list_member_id = l_resource_list_member_id);
1292
1293 -- Check for Resource List Member Id in Budgets
1294
1295 CURSOR l_budgets_csr (l_resource_list_id NUMBER,
1296 l_resource_list_member_id NUMBER)
1297 IS
1298 /* Modified for perf bug 4887375
1299 SELECT 'x'
1300 FROM dual
1301 WHERE exists
1302 (select 'x'
1303 from pa_resource_list_assignments rla
1304 , pa_budget_versions bv
1305 , pa_resource_assignments ra
1306 where rla.resource_list_id = l_resource_list_id
1307 and rla.project_id = bv.project_id
1308 and rla.resource_list_id = bv.resource_list_id
1309 and bv.budget_version_id = ra.budget_version_id
1310 and bv.project_id = ra.project_id
1311 and ra.resource_list_member_id = l_resource_list_member_id);*/
1312 SELECT 'x'
1313 FROM dual
1314 WHERE exists
1315 (select 'x'
1316 from pa_resource_assignments ra
1317 where ra.resource_list_member_id = l_resource_list_member_id);
1321 -- Bug 5199763 - pa_fp_elements is obsolete in R12. Hence, commenting out.
1318
1319 -- Check for Resource List Member Id in Pa_Fp_Elements
1320
1322 -- CURSOR l_elements_csr
1323 -- IS
1324 -- SELECT 'x'
1325 -- FROM dual
1326 -- WHERE exists
1327 -- (select 'x'
1328 -- from pa_fp_elements pfe
1329 -- where pfe.resource_list_member_id = l_resource_list_member_id);
1330
1331 l_resource_pa varchar2(1);
1332
1333 BEGIN
1334
1335 x_err_code := 0;
1336
1337 -- VALIDATION LAYER ----------------------------------------------------------
1338 ----
1339
1340
1341 -- Check for Resource List Member in Project_Accum_Headers
1342
1343 OPEN l_accum_headers_csr (l_resource_list_id,l_resource_list_member_id );
1344
1345 FETCH l_accum_headers_csr INTO l_dummy;
1346 IF l_accum_headers_csr %FOUND THEN
1347 x_err_code := 10;
1348 x_err_stage := 'PA_RLM_USED_IN_ACCUM';
1349 return;
1350 END IF;
1351 CLOSE l_accum_headers_csr;
1352
1353 -- Check for Resource List Member in pa_fp_elements
1354 -- Bug 5199763 - pa_fp_elements is obsolete in R12. Hence, commenting out.
1355
1356 -- OPEN l_elements_csr;
1357 -- FETCH l_elements_csr INTO l_dummy;
1358 -- IF l_elements_csr %FOUND THEN
1359 -- x_err_code := 15;
1360 -- x_err_stage := 'PA_RLM_USED_IN_FP_OPTIONS';
1361 -- return;
1362 -- END IF;
1363 -- CLOSE l_elements_csr;
1364
1365 -- Check for Resource List Member in Budgets
1366
1367 OPEN l_budgets_csr (l_resource_list_id,l_resource_list_member_id );
1368
1369 FETCH l_budgets_csr INTO l_dummy;
1370 IF l_budgets_csr %FOUND THEN
1371 x_err_code := 20;
1372 x_err_stage := 'PA_RLM_USED_IN_BUDGETS';
1373 return;
1374 END IF;
1375 CLOSE l_budgets_csr;
1376
1377 --Check resource list member in project allocations
1378
1379 l_resource_pa := PA_ALLOC_UTILS.is_resource_in_rules(l_resource_list_member_id);
1380 If l_resource_pa = 'Y' Then
1381 x_err_code := 30;
1382 x_err_stage := 'PA_RES_EXISTS_PROJ_ALLOC';
1383 return;
1384 End If;
1385
1386 EXCEPTION
1387 when others then
1388 x_err_code := SQLCODE;
1389 x_err_stage := SQLERRM;
1390 rollback;
1391 return;
1392
1393 END delete_resource_list_member_ok;
1394
1395 END PA_GET_RESOURCE;