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