1 PACKAGE BODY PA_ALLOC_UTILS AS
2 /* $Header: PAXALUTB.pls 120.1.12010000.3 2009/11/30 12:30:37 rrambati ship $ */
3
4 ------------------------------------------------------------------------
5 --- is_resource_in_rules
6 -----This function returns 'Y' if a resource list member is used in allocations
7 ------------------------------------------------------------------------
8 FUNCTION Is_resource_in_rules(p_resource_list_member_id IN NUMBER)
9 RETURN VARCHAR2
10 IS
11
12 CURSOR C_resource_list_member IS
13 SELECT '1'
14 FROM dual
15 WHERE EXISTS (SELECT 'Y'
16 FROM PA_ALLOC_RESOURCES RE
17 WHERE RE.RESOURCE_LIST_MEMBER_ID=P_resource_list_member_id
18 )
19 OR EXISTS
20 (SELECT 'Y'
21 FROM PA_ALLOC_RUN_SOURCE_DET SRC
22 WHERE SRC.RESOURCE_LIST_MEMBER_ID=P_resource_list_member_id
23 )
24 OR EXISTS
25 (SELECT 'Y'
26 FROM PA_ALLOC_RUN_BASIS_DET BASIS
27 WHERE BASIS.RESOURCE_LIST_MEMBER_ID=P_resource_list_member_id
28 );
29 v_ret_code varchar2(1) ;
30 v_dummy varchar2(1);
31
32 BEGIN
33 v_ret_code := 'N';
34
35 OPEN C_resource_list_member ;
36 FETCH C_resource_list_member INTO v_dummy;
37 IF C_resource_list_member%FOUND THEN
38 v_ret_code := 'Y' ;
39 END IF;
40 CLOSE C_resource_list_member;
41 RETURN v_ret_code;
42
43 EXCEPTION
44 WHEN NO_DATA_FOUND THEN
45 v_ret_code := 'N' ;
46 Return v_ret_code ;
47 WHEN OTHERS THEN
48 RAISE;
49 END is_resource_in_rules ;
50
51 ------------------------------------------------------------------------
52 --- is_resource_list_in_rules
53 ---- This function returns 'Y' if a resouce list is used in allocations
54 ------------------------------------------------------------------------
55 FUNCTION is_resource_list_in_rules(p_resource_list_id IN NUMBER)
56 RETURN varchar2 IS
57 CURSOR C_resource_list IS
58 SELECT '1'
59 FROM dual
60 WHERE EXISTS (SELECT 'Y'
61 FROM PA_ALLOC_RULES_ALL PAL /* Bug 4185336 Changed PA_ALLOC_RULES to PA_ALLOC_RULES_ALL */
62 WHERE PAL.BASIS_RESOURCE_LIST_ID=P_resource_list_id
63 OR PAL.ALLOC_RESOURCE_LIST_ID=P_resource_list_id
64 )
65 OR EXISTS
66 (SELECT 'Y'
67 FROM PA_ALLOC_RUNS_ALL PAR
68 WHERE PAR.BASIS_RESOURCE_LIST_ID=P_resource_list_id
69 OR PAR.ALLOC_RESOURCE_LIST_ID=P_resource_list_id
70 );
71 v_ret_code varchar2(1) ;
72 v_dummy varchar2(1);
73
74 BEGIN
75 v_ret_code := 'N';
76
77 OPEN C_resource_list ;
78 FETCH C_resource_list INTO v_dummy;
79 IF C_resource_list%FOUND THEN
80 v_ret_code := 'Y' ;
81 END IF;
82 CLOSE C_resource_list;
83 RETURN v_ret_code;
84
85 EXCEPTION
86 WHEN NO_DATA_FOUND THEN
87 v_ret_code := 'N' ;
88 Return v_ret_code ;
89 WHEN OTHERS THEN
90 RAISE;
91 END is_resource_list_in_rules ;
92
93
94 ------------------------------------------------------------------------
95 --- is_project_in_allocations
96 ---- This function returns 'Y' if a project is used in allocations
97 ------------------------------------------------------------------------
98 FUNCTION Is_project_in_allocations(p_project_id IN NUMBER)
99 RETURN VARCHAR2
100 IS
101
102 CURSOR C_project_in_allocations is
103 SELECT '1'
104 FROM dual
105 WHERE EXISTS (SELECT 'Y'
106 FROM PA_ALLOC_TXN_DETAILS TXN
107 WHERE TXN.project_id=p_project_id)
108 OR EXISTS
109 (SELECT 'Y'
110 FROM PA_ALLOC_RULES_ALL RULES
111 WHERE RULES.offset_project_id=p_project_id)
112 OR EXISTS
113 (SELECT 'Y'
114 FROM PA_ALLOC_SOURCE_LINES SRCL
115 WHERE SRCL.project_id=p_project_id)
116 OR EXISTS
117 ( SELECT 'Y'
118 FROM PA_ALLOC_TARGET_LINES TGTL
119 WHERE TGTL.project_id=p_project_id)
120 OR EXISTS
121 (SELECT 'Y'
122 FROM PA_ALLOC_RUN_SOURCES RSRC
123 WHERE RSRC.project_id=p_project_id)
124 OR EXISTS
125 (SELECT 'Y'
126 FROM PA_ALLOC_RUN_TARGETS RTGT
127 WHERE RTGT.project_id=p_project_id);
128
129 v_ret_code varchar2(1) ;
130 v_dummy varchar2(1);
131
132 BEGIN
133
134 v_ret_code := 'N';
135
136 OPEN C_project_in_allocations;
137 FETCH C_project_in_allocations INTO v_dummy;
138 IF C_project_in_allocations%FOUND THEN
139 v_ret_code := 'Y' ;
140 END IF;
141 CLOSE C_project_in_allocations;
142 RETURN v_ret_code;
143
144 EXCEPTION
145 WHEN NO_DATA_FOUND THEN
146 v_ret_code := 'N' ;
147 Return v_ret_code ;
148 WHEN OTHERS THEN
149 RAISE;
150 END is_project_in_allocations;
151
152
153
154 ------------------------------------------------------------------------
155 --- is_task_in_allocations
156 ---- This function returns 'Y' if a task is used in allocations
157 ------------------------------------------------------------------------
158 FUNCTION Is_task_in_allocations(p_task_id IN NUMBER)
159 RETURN VARCHAR2
160 IS
161 /***
162 CURSOR C_subtasks is
163 select task_id
164 from pa_tasks
165 CONNECT BY PRIOR TASK_ID = PARENT_TASK_ID
166 START WITH TASK_ID = p_task_id;
167 ***/
168
169 CURSOR C_task_in_alloc_rule(x_task_id IN NUMBER) is
170 SELECT 'Y'
171 FROM PA_ALLOC_RULES_ALL RULES
172 WHERE RULES.offset_task_id=x_task_id;
173 /* Commented for bug 8929749
174 CURSOR C_task_in_alloc_src_line(x_task_id IN NUMBER) is
175 SELECT 'Y'
176 FROM PA_ALLOC_SOURCE_LINES SRCL
177 WHERE SRCL.task_id=x_task_id;
178 */
179 CURSOR C_task_in_alloc_tgt_line(x_task_id IN NUMBER) is
180 SELECT 'Y'
181 FROM PA_ALLOC_TARGET_LINES TGTL
182 WHERE TGTL.task_id=x_task_id;
183 CURSOR C_task_in_alloc_txn(x_task_id IN NUMBER) is
184 SELECT 'Y'
185 FROM PA_ALLOC_TXN_DETAILS TXN
186 WHERE TXN.task_id=x_task_id;
187 /* Commented for bug 8929749
188 CURSOR C_task_in_alloc_run_src(x_task_id IN NUMBER) is
189 SELECT 'Y'
190 FROM PA_ALLOC_RUN_SOURCES RSRC
191 WHERE RSRC.task_id=x_task_id;
192 */
193 CURSOR C_task_in_alloc_run_tgt(x_task_id IN NUMBER) is
194 SELECT 'Y'
195 FROM PA_ALLOC_RUN_TARGETS RTGT
196 WHERE RTGT.task_id=x_task_id;
197
198 v_ret_code varchar2(1) := 'N';
199
200 BEGIN
201
202 /**
203 v_ret_code := 'N';
204 For subtasks_rec in C_subtasks LOOP
205 OPEN C_task_in_allocations(p_task_id);
206 FETCH C_task_in_allocations INTO v_dummy;
207 IF C_task_in_allocations%FOUND THEN
208 v_ret_code := 'Y' ;
209 close C_task_in_allocations;
210 return v_ret_code;
211 END IF;
212 CLOSE C_task_in_allocations;
213 END LOOP;
214 **/
215 open C_task_in_alloc_rule(p_task_id);
216 fetch C_task_in_alloc_rule into v_ret_code;
217 close C_task_in_alloc_rule;
218 /* Commented for bug 8929749
219 if (v_ret_code = 'N') then
220 open C_task_in_alloc_src_line(p_task_id);
221 fetch C_task_in_alloc_src_line into v_ret_code;
222 close C_task_in_alloc_src_line;
223 else
224 RETURN v_ret_code;
225 end if;
226 */
227 if (v_ret_code = 'N') then
228 open C_task_in_alloc_tgt_line(p_task_id);
229 fetch C_task_in_alloc_tgt_line into v_ret_code;
230 close C_task_in_alloc_tgt_line;
231 else
232 RETURN v_ret_code;
233 end if;
234
235 if (v_ret_code = 'N') then
236 open C_task_in_alloc_txn(p_task_id);
237 fetch C_task_in_alloc_txn into v_ret_code;
238 close C_task_in_alloc_txn;
239 else
240 RETURN v_ret_code;
241 end if;
242 /* Commented for bug 8929749
243 if (v_ret_code = 'N') then
244 open C_task_in_alloc_run_src(p_task_id);
245 fetch C_task_in_alloc_run_src into v_ret_code;
246 close C_task_in_alloc_run_src;
247 else
248 RETURN v_ret_code;
249 end if;
250 */
251 if (v_ret_code = 'N') then
252 open C_task_in_alloc_run_tgt(p_task_id);
253 fetch C_task_in_alloc_run_tgt into v_ret_code;
254 close C_task_in_alloc_run_tgt;
255 else
256 RETURN v_ret_code;
257 end if;
258
259 RETURN v_ret_code;
260
261 EXCEPTION
262 WHEN NO_DATA_FOUND THEN
263 v_ret_code := 'N' ;
264 Return v_ret_code ;
265 WHEN OTHERS THEN
266 RAISE;
267 END is_task_in_allocations;
268
269 ------------------------------------------------------------------------
270 --- is_task_lowest_in_allocations
271 --- This function returns 'Y' if a task is used as target or offset, or if
272 ---- a task is a non top level task and used as source in allocations
273 ------------------------------------------------------------------------
274 -- All target tasks and offset tasks should be lowest level tasks, subtasks are not
275 --allowed to be created for them. For a given task, if it exists in any target or
276 --offset related table,no subtask is allowed to be created for this task.
277 --Source tasks can be top level tasks or lowest level tasks.For a given task,
278 --if it exists in any source related table and it is not a top level task, no subtask is allowed
279 --to be created for this task.
280
281
282 FUNCTION Is_task_lowest_in_allocations(p_task_id IN NUMBER)
283 RETURN VARCHAR2
284 IS
285
286 CURSOR C_task_in_targets_offsets IS
287 SELECT '1'
288 FROM dual
289 WHERE EXISTS (SELECT '1'
290 FROM PA_ALLOC_TXN_DETAILS TXN
291 WHERE TXN.task_id=p_task_id)
292 OR EXISTS
293 ( SELECT '1'
294 FROM PA_ALLOC_RULES_ALL RULES
295 WHERE RULES.offset_task_id=p_task_id)
296 OR EXISTS
297 (SELECT '1'
298 FROM PA_ALLOC_TARGET_LINES TGTL
299 WHERE TGTL.task_id=p_task_id)
300 OR EXISTS
301 (SELECT '1'
302 FROM PA_ALLOC_RUN_TARGETS RTGT
303 WHERE RTGT.task_id=p_task_id);
304
305 CURSOR C_task_in_sources IS
306 SELECT '1'
307 FROM dual
308 WHERE EXISTS (SELECT '1'
309 FROM PA_ALLOC_SOURCE_LINES SRCL
310 WHERE SRCL.task_id=p_task_id)
311 OR EXISTS
312 ( SELECT '1'
313 FROM PA_ALLOC_RUN_SOURCES RSRC
314 WHERE RSRC.task_id=p_task_id);
315
316 CURSOR C_top_task(p_tsk_id IN NUMBER) IS
317 SELECT top_task_id
318 FROM pa_tasks
319 WHERE task_id=p_tsk_id;
320
321
322 v_ret_code varchar2(1) ;
323 v_dummy varchar2(1);
324 v_top_task_id number;
325
326
327 BEGIN
328
329 v_ret_code := 'N';
330 OPEN C_top_task(p_task_id);
331 FETCH C_top_task INTO v_top_task_id;
332 CLOSE C_top_task;
333
334 OPEN C_task_in_targets_offsets;
335 FETCH C_task_in_targets_offsets INTO v_dummy;
336 IF C_task_in_targets_offsets%FOUND THEN
337 v_ret_code := 'Y';
338 END IF;
339
340 /* Bug# 8834708
341 This check is not required while creating a sub task for a task.
342 Though we have data in alloc run sources, it does not cause any issue
343 unless there is expenditure for the task being passed to this procedure.
344 Since we are checking the existance of expenditure even before calling this
345 API, it is fine to relax this validation and allow creating sub task though
346 the task referred in the sources table.
347 ELSE
348 OPEN C_task_in_sources;
349 FETCH C_task_in_sources INTO v_dummy;
350 IF C_task_in_sources%FOUND THEN
351 IF (v_top_task_id <> p_task_id) THEN
352 v_ret_code := 'Y';
353 END IF;
354 END IF;
355 CLOSE C_task_in_sources;
356 END IF; */
357 CLOSE C_task_in_targets_offsets;
358
359 RETURN v_ret_code;
360
361 EXCEPTION
362 WHEN NO_DATA_FOUND THEN
363 v_ret_code := 'N' ;
364 RETURN v_ret_code ;
365 WHEN OTHERS THEN
366 RAISE;
367 END is_task_lowest_in_allocations;
368
369
370
371 ------------------------------------------------------------------------
372 --- Is_Budget_Type_In_allocations
373 ---- This function returns 'Y' if a budget_type is used in allocations
374 ------------------------------------------------------------------------
375 FUNCTION Is_Budget_Type_In_allocations(p_budget_type_code IN varchar2)
376 RETURN varchar2
377 is
378 CURSOR C_budget_type_in_allocations is
379 SELECT '1'
380 FROM dual
381 WHERE EXISTS (SELECT 'Y'
382 FROM PA_ALLOC_RULES_ALL
383 WHERE basis_budget_type_code=p_budget_type_code)
384 OR EXISTS
385 (SELECT 'Y'
386 FROM PA_ALLOC_RUNS_ALL
387 WHERE basis_budget_type_code=p_budget_type_code);
388
389 v_ret_code varchar2(1) ;
390 v_dummy varchar2(1);
391
392 BEGIN
393
394 v_ret_code := 'N';
395
396 OPEN C_budget_type_in_allocations;
397 FETCH C_budget_type_in_allocations INTO v_dummy;
398 IF C_budget_type_in_allocations%FOUND THEN
399 v_ret_code := 'Y' ;
400 END IF;
401 CLOSE C_budget_type_in_allocations;
402 RETURN v_ret_code;
403
404 EXCEPTION
405 WHEN NO_DATA_FOUND THEN
406 v_ret_code := 'N' ;
407 Return v_ret_code ;
408 WHEN OTHERS THEN
409 RAISE;
410 end Is_Budget_Type_In_allocations;
411
412 /*
413 ------------------------------------------------------------------------
414 --- Is_Bem_In_allocations
415 ---- This function returns 'Y' if a budget entry method is used in allocations
416 ------------------------------------------------------------------------
417 FUNCTION Is_Bem_In_allocations(p_bem_code IN varchar2)
418 RETURN varchar2
419 is
420
421 CURSOR C_Bem_in_allocations is
422 SELECT '1'
423 FROM dual
424 WHERE EXISTS (SELECT 'Y'
425 FROM PA_ALLOC_RULES_ALL
426 WHERE basis_budget_entry_method_code=p_bem_code)
427 OR EXISTS
428 (SELECT 'Y'
429 FROM PA_ALLOC_RUNS_ALL
430 WHERE basis_budget_entry_method_code=p_bem_code);
431
432 v_ret_code varchar2(1) ;
433 v_dummy varchar2(1);
434
435 BEGIN
436
437 v_ret_code := 'N';
438
439 open C_Bem_in_allocations;
440 FETCH C_Bem_in_allocations into v_dummy;
441 IF C_Bem_in_allocations%FOUND THEN
442 v_ret_code := 'Y' ;
443 END IF;
444 CLOSE C_Bem_in_allocations;
445 RETURN v_ret_code;
446
447 EXCEPTION
448 WHEN NO_DATA_FOUND THEN
449 v_ret_code := 'N' ;
450 Return v_ret_code ;
451 WHEN OTHERS THEN
452 RAISE;
453 end Is_Bem_In_allocations;*/
454
455
456 /*
457 API Name : Is_RBS_In_Rules
458 API Desc : Return 'Y' if RBS is used in Allocations.
459 API Created Date : 19-Mar-04
460 API Created By : Vthakkar
461 */
462 FUNCTION Is_RBS_In_Rules ( P_RBS_ID IN pa_rbs_headers_v.RBS_HEADER_ID%Type ) RETURN VARCHAR2
463 IS
464 l_exists Varchar2(1) := 'N';
465 BEGIN
466 /* Checking PA_ALLOC_RULES_ALL */
467 Begin
468 Select 'Y' into l_exists
469 from pa_alloc_rules_all /* Bug 4185336 Changed from pa_alloc_rules to pa_alloc_rules_all */
470 where
471 (
472 ( ALLOC_RESOURCE_STRUCT_TYPE = 'RBS'
473 and
474 Alloc_resource_list_id = P_RBS_ID
475 )
476 or
477 (
478 BASIS_RESOURCE_STRUCT_TYPE = 'RBS'
479 and
480 BASIS_resource_list_id = P_RBS_ID
481 )
482 )
483 and rownum = 1;
484 Return l_exists ;
485 Exception
486 When No_Data_Found Then
487 l_exists := 'N';
488 End;
489 /* Checking PA_ALLOC_RUNS_ALL */
490 Begin
491 Select 'Y' into l_exists
492 from pa_alloc_runs_all /* Bug 4185336 Changed from pa_alloc_rules to pa_alloc_rules_all */
493 where (
494 ( ALLOC_RESOURCE_STRUCT_TYPE = 'RBS'
495 and
496 Alloc_resource_list_id = P_RBS_ID
497 )
498 or
499 (
500 BASIS_RESOURCE_STRUCT_TYPE = 'RBS'
501 and
502 BASIS_resource_list_id = P_RBS_ID
503 )
504 )
505 and rownum = 1;
506 Return l_exists ;
507 Exception
508 When No_Data_Found Then
509 l_exists := 'N';
510 End;
511 Return l_exists;
512 END Is_RBS_In_Rules ;
513 /*
514 API Name : Is_RBS_In_Rules
515
516 API Desc : Return 'Y' if RBS Element is used in Allocations.
517 API Created Date : 19-Mar-04
518 API Created By : Vthakkar
519 */
520 FUNCTION Is_RBS_Element_In_Rules ( P_RBS_ELEMENT_ID IN pa_rbs_elements.RBS_ELEMENT_ID%type ) RETURN
521 VARCHAR2
522
523 IS
524 l_exists Varchar2(1) := 'N';
525 BEGIN
526 Begin
527 Select 'Y' into l_exists
528 From PA_ALLOC_RULES_ALL par, PA_ALLOC_RESOURCES pr
529 Where par.rule_id = pr.rule_id
530 and pr.RESOURCE_LIST_MEMBER_ID = P_RBS_ELEMENT_ID
531 and decode (pr.member_type , 'S' , par.ALLOC_RESOURCE_STRUCT_TYPE ,
532 'B' , par.BASIS_RESOURCE_STRUCT_TYPE
533 ) = 'RBS'
534 and rownum = 1;
535 Return l_exists ;
536 Exception
537 When No_Data_Found Then
538 l_exists := 'N';
539
540 End;
541 Begin
542 Select 'Y' into l_exists
543 From pa_alloc_runs_all par, PA_ALLOC_RUN_SOURCE_DET pars
544 Where par.run_id = pars.run_id
545 and par.rule_id = pars.rule_id
546 and RESOURCE_LIST_MEMBER_ID = P_RBS_ELEMENT_ID
547 and par.ALLOC_RESOURCE_STRUCT_TYPE = 'RBS'
548 and rownum = 1;
549 Return l_exists ;
550 Exception
551 When No_Data_Found Then
552 l_exists := 'N';
553 End;
554 Begin
555 Select 'Y' into l_exists
556 From pa_alloc_runs_all par, PA_ALLOC_RUN_BASIS_DET pars
557 Where par.run_id = pars.run_id
558 and par.rule_id = pars.rule_id
559 and RESOURCE_LIST_MEMBER_ID = P_RBS_ELEMENT_ID
560 and par.BASIS_RESOURCE_STRUCT_TYPE = 'RBS'
561 and rownum = 1;
562 Return l_exists ;
563
564 Exception
565 When No_Data_Found Then
566 l_exists := 'N';
567 End;
568 Return l_exists ;
569 END Is_RBS_Element_In_Rules;
570 /*
571 API Name : Resource_Name
572 API Desc : This function will be return the name of the resource id depending upon the Allocation T
573 ype and
574
575 If Resource ID is member of Resource List or RBS Structure.
576 API Created Date : 19-Mar-04
577 API Created By : Vthakkar
578 */
579 Function Resource_Name (
580 p_alloc_type IN Varchar2 ,
581 p_resource_id IN pa_rbs_elements.RBS_ELEMENT_ID%type ,
582 p_rule_id IN pa_alloc_rules.rule_id%type
583 ) Return Varchar2
584 IS
585 l_source_res_struct_type pa_alloc_rules_all.ALLOC_RESOURCE_STRUCT_TYPE%type;
586 l_basis_res_struct_type pa_alloc_rules_all.BASIS_RESOURCE_STRUCT_TYPE%type;
587
588 x_name varchar2(4000);
589 BEGIN
590 If p_resource_id Is Null Then
591 Return Null;
592 End If;
593 Select ALLOC_RESOURCE_STRUCT_TYPE ,
594 BASIS_RESOURCE_STRUCT_TYPE
595 Into l_source_res_struct_type ,
596 l_basis_res_struct_type
597 From pa_alloc_rules_all
598 Where Rule_ID = p_Rule_Id;
599 If p_alloc_type In ('SOURCE' , 'S') Then
600 If l_source_res_struct_type = 'RL' Then
601 select prlm.alias
602 Into X_name
603 from pa_resource_list_members prlm
604 where prlm.resource_list_member_id = p_resource_id;
605 Elsif l_source_res_struct_type = 'RBS' Then
606 Select pa_alloc_utils.Get_Resource_Name_TL (RBS_ELEMENT_NAME_ID)
607 Into X_name
608 From pa_rbs_elements
609 Where rbs_element_id = p_resource_id
610 And rownum = 1;
611
612 End If;
613 ElsIf p_alloc_type In ('BASIS', 'B' ) Then
614 If l_basis_res_struct_type = 'RL' Then
615 select prlm.alias
616 Into X_name
617 from pa_resource_list_members prlm
618 where prlm.resource_list_member_id = p_resource_id;
619 Elsif l_basis_res_struct_type = 'RBS' Then
620 select pa_alloc_utils.Get_Resource_Name_TL (RBS_ELEMENT_NAME_ID)
621 Into X_name
622 from pa_rbs_elements
623 where rbs_element_id = p_resource_id
624 and rownum = 1;
625 End If;
626 End If;
627 Return X_name;
628 END Resource_Name ;
629 /*
630 API Name : ASSOCIATE_RBS_TO_ALLOC_RULE
631 API Desc : This procedure will be update the new element id to the allocation rules's source resource list member's id
632 and basis resource list member's id when new version of RBS is created
633 API Created Date : 02-Apr-04
634 API Created By : Vthakkar
635
636 History :
637
638 07-JAN-2005 VTHAKKAR Changed the API for bug 4107924
639
640 */
641 Procedure ASSOCIATE_RBS_TO_ALLOC_RULE (
642 p_rbs_header_id IN NUMBER ,
643 p_rbs_version_id IN NUMBER ,
644 x_return_status OUT NOCOPY VARCHAR2 ,
645 x_error_code OUT NOCOPY VARCHAR2
646 )
647 IS
648 CURSOR LV_ALLOC_CUR IS SELECT * FROM PA_ALLOC_RULES_ALL
649 WHERE alloc_resource_list_id = p_rbs_header_id
650 OR basis_resource_list_id = p_rbs_header_id;
651 BEGIN
652
653 x_return_status := FND_API.G_RET_STS_SUCCESS;
654
655 /* Changed the API for Bug 4107924 */
656 FOR ALLOC_CUR IN LV_ALLOC_CUR
657 LOOP
658
659 /* Source Update */
660
661 IF NVL(ALLOC_CUR.alloc_resource_struct_type,'RL') = 'RBS' and ALLOC_CUR.alloc_resource_list_id = p_rbs_header_id
662 and NVL(ALLOC_CUR.ALLOC_RBS_VERSION,0) < p_rbs_version_id Then
663
664 Update pa_alloc_rules_all
665 Set alloc_rbs_version = p_rbs_version_id
666 Where RULE_ID = ALLOC_CUR.RULE_ID;
667
668 Update pa_alloc_resources ARS
669 Set resource_list_member_id = (
670 select new.rbs_element_id
671 from pa_rbs_elements new
672 ,pa_rbs_elements old
673 where new.element_identifier = old.element_identifier
674 and old.rbs_version_Id = ALLOC_CUR.alloc_rbs_version
675 and new.rbs_version_Id = p_rbs_version_id
676 and old.rbs_element_id = ars.resource_list_member_id
677 and new.user_created_flag = 'N'
678 )
679 Where ARS.RULE_ID = ALLOC_CUR.RULE_ID
680 AND ARS.MEMBER_TYPE = 'S';
681
682
683 END IF;
684
685 /* Basis Update */
686
687 IF NVL(ALLOC_CUR.basis_resource_struct_type,'RL') = 'RBS' and ALLOC_CUR.basis_resource_list_id = p_rbs_header_id
688 and NVL(ALLOC_CUR.BASIS_RBS_VERSION,0) < p_rbs_version_id Then
689
690 Update pa_alloc_rules_all
691 Set basis_rbs_version = p_rbs_version_id
692 Where RULE_ID = ALLOC_CUR.RULE_ID;
693
694 Update pa_alloc_resources ARS
695 Set resource_list_member_id = (
696 select new.rbs_element_id
697 from pa_rbs_elements new
698 ,pa_rbs_elements old
699 where new.element_identifier = old.element_identifier
700 and old.rbs_version_Id = ALLOC_CUR.basis_rbs_version
701 and new.rbs_version_Id = p_rbs_version_id
702 and old.rbs_element_id = ars.resource_list_member_id
703 and new.user_created_flag = 'N'
704 )
705 Where ARS.RULE_ID = ALLOC_CUR.RULE_ID
706 AND ARS.MEMBER_TYPE = 'B';
707
708 END IF;
709
710 END LOOP;
711
712 /* Commenting out the code for bug 4107924
713 Update pa_alloc_resources ARS
714 Set resource_list_member_id = (
715 select new.rbs_element_id
716 from pa_rbs_elements new
717 ,pa_rbs_elements old
718 ,pa_alloc_rules_all ar
719 where ar.alloc_resource_list_id = p_rbs_header_id
720 and decode ( ars.member_type ,
721 'S' , nvl(ar.alloc_resource_struct_type,'RL') ,
722 'B' , nvl(ar.basis_resource_struct_type,'RL')
723 ) = 'RBS'
724 and ar.rule_id = ars.rule_id
725 and new.element_identifier = old.element_identifier
726 and old.rbs_version_Id = ar.alloc_rbs_version
727 and new.rbs_version_Id = p_rbs_version_id
728 and old.rbs_element_id = ars.resource_list_member_id
729 )
730 Where Exists (
731 select 1
732 From pa_alloc_rules_all arl
733 Where arl.Rule_Id = Ars.Rule_Id
734 ANd decode ( ars.member_type ,
735 'S' , nvl(arl.alloc_resource_struct_type,'RL') ,
736 'B' , nvl(arl.basis_resource_struct_type,'RL')
737 ) = 'RBS'
738 );
739 Update pa_alloc_rules_all
740 Set alloc_rbs_version = p_rbs_version_id
741 Where nvl(alloc_resource_struct_type,'RL') = 'RBS'
742 And alloc_resource_list_id = p_rbs_header_id;
743 Update pa_alloc_rules_all
744 Set basis_rbs_version = p_rbs_version_id
745 Where nvl(basis_resource_struct_type,'RL') = 'RBS'
746 And basis_resource_list_id = p_rbs_header_id;
747
748 End of commeting code for bug 4107924
749
750 */
751
752 EXCEPTION
753 WHEN OTHERS THEN
754 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
755 x_error_code := to_char(sqlcode);
756 END ASSOCIATE_RBS_TO_ALLOC_RULE;
757 /*
758 API Name : RESOURCE_LIST_NAME
759 API Desc : This function will return name of Resource List or Resource Breakdown Structure Header N
760 ame depending Upon
761
762 Rule contains Resource List or Resource Structure.
763 API Created Date : 06-Apr-04
764 API Created By : Vthakkar
765 */
766 Function RESOURCE_LIST_NAME (
767 p_resource_list_id In Number ,
768 p_resource_struct_type in Varchar2
769 ) Return Varchar2
770 Is
771 X_Resource_List_Name Varchar2(4000);
772 Begin
773 If Nvl(p_Resource_Struct_Type,'RL') = 'RL' Then
774 Select Name
775
776 Into X_Resource_List_Name
777 From pa_resource_lists_v
778 Where RESOURCE_LIST_ID = p_resource_list_id;
779 Elsif Nvl(p_Resource_Struct_Type,'RL') = 'RBS' Then
780 Select Name
781 Into X_Resource_List_Name
782 From pa_rbs_headers_v
783 Where RBS_HEADER_ID = p_resource_list_id;
784 End If;
785 Return X_Resource_List_Name;
786 Exception
787 When Others Then
788 Return Null;
789 End;
790 /*
791 API Name : GET_CONCATENATED_NAME
792 API Desc : This function will return name of Resource List Member attached with parent member name
793 like e.g self.parent
794
795 API Created Date : 03-May-04
796 API Created By : Vthakkar
797 */
798 Function GET_CONCATENATED_NAME (p_resource_id in Number , p_struct_type in Varchar2 ) Return
799
800 Varchar2 Is
801 X_Resource_Name Varchar2(10000);
802 l_self_name pa_resource_list_members.alias%type;
803 l_parent_name pa_resource_list_members.alias%type;
804 l_element_name Varchar2(240);
805 l_count Number;
806 Cursor C_Member_Name
807 Is Select slf.alias , prt.alias
808 from pa_resource_list_members slf , pa_resource_list_members prt
809 Where prt.resource_list_member_id (+) = slf.parent_member_id
810 and slf.resource_list_member_id = p_resource_id;
811 Cursor c_element_name
812 IS SELECT pa_alloc_utils.Get_Resource_Name_TL(ele.rbs_element_name_id) Resource_Name
813 FROM pa_rbs_elements ele
814 CONNECT BY PRIOR ele.parent_element_id = ele.rbs_element_id
815 START WITH ele.rbs_element_id = p_resource_id
816 ORDER BY ele.rbs_level DESC;
817 Begin
818 If p_resource_id is Null Then
819 Return Null;
820 End If;
821 If p_struct_type = 'RL' Then
822 Open C_Member_Name;
823
824 Fetch C_Member_Name into l_self_name , l_parent_name;
825 If C_Member_Name%Found Then
826 X_Resource_Name := l_self_name;
827 If l_parent_name Is Not Null Then
828 X_Resource_Name := X_Resource_Name || '.' || l_parent_name;
829 End If;
830 End If;
831 Close C_Member_Name;
832 ElsIf p_struct_type = 'RBS' Then
833 OPEN c_element_name;
834 LOOP
835 FETCH c_element_name INTO l_element_name;
836 EXIT WHEN c_element_name%NOTFOUND;
837 l_count := c_element_name%ROWCOUNT;
838 /*********************************************
839 * If Count is 1 just assing the l_element_name to the
840 * X_Resource_Name.
841 ***************************************************/
842 IF l_count = 1 THEN
843 X_Resource_Name := l_element_name;
844 ELSE
845 /*********************************************
846 * If Count > 1 just assing the l_element_name to the
847
848 * X_Resource_Name.
849 ***************************************************/
850 X_Resource_Name := X_Resource_Name ||'.'|| l_element_name;
851 END IF;
852 END LOOP;
853 CLOSE c_element_name;
854 End If;
855 Return X_Resource_Name;
856 Exception
857 When Others Then
858 Return Null;
859 End GET_CONCATENATED_NAME;
860 /*
861 API Name : Get_Rbs_Version_Name
862 API Desc : This function will return name of RBS Version Name provided rbs_version_id
863 API Created Date : 11-May-2004
864 API Created By : Vthakkar
865 */
866 Function Get_Rbs_Version_Name (p_rbs_ver_id in Number) Return Varchar2
867 Is
868 X_Rbs_Ver_Name pa_rbs_versions_v.NAME%TYPE;
869 Begin
870 If p_rbs_ver_id Is Null Then
871
872 Return Null;
873 End If;
874 Select Name
875 Into X_Rbs_Ver_Name
876 From pa_rbs_versions_v
877 Where Rbs_Version_Id = P_Rbs_Ver_Id;
878 Return (X_Rbs_Ver_Name);
879 Exception
880 When Others Then
881 Return Null;
882 End Get_Rbs_Version_Name ;
883 /*
884 API Name : Get_Resource_Name_TL
885 API Desc : This function will return name of RBS Name translated in the respective language
886 API Created Date : 13-May-2004
887 API Created By : Vthakkar
888 */
889 Function Get_Resource_Name_TL ( p_rbs_element_name_id in Number ) Return Varchar2
890 Is
891 x_resource_name Varchar2(240);
892 Begin
893 If p_rbs_element_name_id Is Null Then
894 Return Null;
895
896 End If;
897 Select resource_name
898 Into x_resource_name
899 From pa_rbs_element_names_tl
900 Where rbs_element_name_id = p_rbs_element_name_id
901 and language = USERENV('LANG'); /* Added for bug 3960634 */
902 Return x_resource_name;
903 Exception
904 When Others Then
905 Return Null;
906 End Get_Resource_Name_TL;
907
908
909 END pa_alloc_utils ;
[Home] [Help]