1 package body FPA_Utils_PVT as
2 /* $Header: FPAVUTIB.pls 120.8 2011/08/09 01:16:21 skkoppul ship $ */
3
4 /**********************************************************************************
5 **********************************************************************************/
6
7 --The procedure Get_Scenario_Wscore_Color is used to compare the scenario weighted
8 --score against the Planning Cycle From and To weighted score targets.
9 --It returns the appropriate color.
10 function Get_Scenario_Wscore_Color(
11 p_pc_id IN number
12 ,p_scenario_id IN number
13 ) return varchar2 is
14
15 l_sce_wscore number;
16 l_pc_wscore_from number;
17 l_pc_wscore_to number;
18
19 l_color varchar2(1);
20
21 begin
22
23 select a.cost_weighted_score
24 ,b.pc_inv_crit_score_target_from
25 ,b.pc_inv_crit_score_target_to
26 into l_sce_wscore
27 ,l_pc_wscore_from
28 ,l_pc_wscore_to
29 from fpa_aw_sce_str_scores_v a
30 ,fpa_aw_pc_inv_criteria_v b
31 where a.investment_criteria = b.investment_criteria
32 and b.investment_criteria = 3
33 and a.planning_cycle = b.planning_cycle
34 and a.planning_cycle = p_pc_id
35 and a.scenario = p_scenario_id;
36
37 if (l_sce_wscore > l_pc_wscore_to) then
38 l_color := 'G';
39 elsif (l_sce_wscore < l_pc_wscore_to) and (l_sce_wscore > l_pc_wscore_from) then
40 l_color := 'Y';
41 else
42 l_color := 'R';
43 end if;
44
45 return l_color;
46
47 EXCEPTION
48 WHEN OTHERS THEN
49 return null;
50
51 end Get_Scenario_Wscore_Color;
52
53 /**********************************************************************************
54 **********************************************************************************/
55
56 --The procedure Get_Scenario_NPV_Color is used to compare the scenario NPV
57 --value against the Planning Cycle NPV From and To targets.
58 --It returns the appropriate color.
59 function Get_Scenario_NPV_Color(
60 p_pc_id IN number
61 ,p_scenario_id IN number
62 ) return varchar2 is
63
64 l_sce_npv number;
65 l_pc_npv_from number;
66 l_pc_npv_to number;
67
68 l_color varchar2(4);
69
70 begin
71
72 select nvl(b.net_present_value,0)
73 ,c.financial_target_from
74 ,c.financial_target_to
75 into l_sce_npv
76 ,l_pc_npv_from
77 ,l_pc_npv_to
78 from fpa_aw_sces_v a
79 ,fpa_aw_sce_npvs_v b
80 ,fpa_aw_pc_financial_targets_v c
81 where a.scenario = b.scenario
82 and b.scenario = p_scenario_id
83 and a.planning_cycle = c.planning_cycle
84 and a.planning_cycle = p_pc_id
85 and c.financial_metrics = 'NPV';
86
87 if (l_sce_npv > l_pc_npv_to) then
88 l_color := 'GNPV';
89 elsif (l_sce_npv < l_pc_npv_to) and (l_sce_npv > l_pc_npv_from) then
90 l_color := 'YNPV';
91 else
92 l_color := 'RNPV';
93 end if;
94
95 return l_color;
96
97 EXCEPTION
98 WHEN OTHERS THEN
99 return null;
100
101 end Get_Scenario_NPV_Color;
102
103 /**********************************************************************************
104 **********************************************************************************/
105 --The procedure Get_Scenario_ROI_Color is used to compare the scenario ROI
106 --value against the Planning Cycle ROI From and To targets.
107 --It returns the appropriate color.
108 function Get_Scenario_ROI_Color(
109 p_pc_id IN number
110 ,p_scenario_id IN number
111 ) return varchar2 is
112
113 l_sce_roi number;
114 l_pc_roi_from number;
115 l_pc_roi_to number;
116
117 l_color varchar2(4);
118
119 begin
120
121 select nvl(b.return_on_inv,0)
122 ,c.financial_target_from
123 ,c.financial_target_to
124 into l_sce_roi
125 ,l_pc_roi_from
126 ,l_pc_roi_to
127 from fpa_aw_sces_v a
128 ,fpa_aw_sce_fin_metrics_v b
129 ,fpa_aw_pc_financial_targets_v c
130 where a.scenario = b.scenario
131 and b.scenario = p_scenario_id
132 and a.planning_cycle = c.planning_cycle
133 and a.planning_cycle = p_pc_id
134 and c.financial_metrics = 'ROI';
135
136 if (l_sce_roi > l_pc_roi_to) then
137 l_color := 'GROI';
138 elsif (l_sce_roi < l_pc_roi_to) and (l_sce_roi > l_pc_roi_from) then
139 l_color := 'YROI';
140 else
141 l_color := 'RROI';
142 end if;
143
144 return l_color;
145
146 EXCEPTION
147 WHEN OTHERS THEN
148 return null;
149
150 end Get_Scenario_ROI_Color;
151
152 /**********************************************************************************
153 **********************************************************************************/
154
155 function Determine_PC_Checklist_status(
156 p_pc_id IN number
157 ,p_lookup_code IN varchar2
158 ) return varchar2 is
159
160 l_sce_count number;
161
162 l_pc_status varchar2(30);
163 l_step_status varchar2(30) := 'NOSTEP';
164
165 begin
166
167 --Select current pc status
168 select pc_status
169 into l_pc_status
170 from fpa_aw_pc_info_v
171 where planning_cycle = p_pc_id;
172
173 CASE
174 when l_pc_status is null then
175 if p_lookup_code = 'REVIEW_CRITERIA' then
176 l_step_status := 'INPROGRESS';
177 elsif p_lookup_code = 'INITIATE_PC' then
178 l_step_status := 'NOTSTARTED';
179 elsif p_lookup_code = 'COLLECT_PROJECTS' then
180 l_step_status := 'NOTSTARTED';
181 elsif p_lookup_code = 'DEVELOP_SCENARIOS' then
182 l_step_status := 'NOTSTARTED';
183 elsif p_lookup_code = 'SUBMIT_PLAN' then
184 l_step_status := 'NOTSTARTED';
185 elsif p_lookup_code = 'APPROVE_PLAN' then
186 l_step_status := 'NOTSTARTED';
187 elsif p_lookup_code = 'CLOSE_PLAN' then
188 l_step_status := 'NOTSTARTED';
189 end if;
190 when l_pc_status = 'CREATED' then
191 if p_lookup_code = 'REVIEW_CRITERIA' then
192 l_step_status := 'INPROGRESS';
193 elsif p_lookup_code = 'INITIATE_PC' then
194 l_step_status := 'INITIATE';
195 elsif p_lookup_code = 'COLLECT_PROJECTS' then
196 l_step_status := 'NOTSTARTED';
197 elsif p_lookup_code = 'DEVELOP_SCENARIOS' then
198 l_step_status := 'NOTSTARTED';
199 elsif p_lookup_code = 'SUBMIT_PLAN' then
200 l_step_status := 'NOTSTARTED';
201 elsif p_lookup_code = 'APPROVE_PLAN' then
202 l_step_status := 'NOTSTARTED';
203 elsif p_lookup_code = 'CLOSE_PLAN' then
204 l_step_status := 'CLOSE';
205 end if;
206 when l_pc_status = 'COLLECTING' then
207 if p_lookup_code = 'REVIEW_CRITERIA' then
208 l_step_status := 'DONE';
209 elsif p_lookup_code = 'INITIATE_PC' then
210 l_step_status := 'DONE';
211 elsif p_lookup_code = 'COLLECT_PROJECTS' then
212 l_step_status := 'INPROGRESS';
213 elsif p_lookup_code = 'DEVELOP_SCENARIOS' then
214 l_step_status := 'NOTSTARTED';
215 elsif p_lookup_code = 'SUBMIT_PLAN' then
216 l_step_status := 'NOTSTARTED';
217 elsif p_lookup_code = 'APPROVE_PLAN' then
218 l_step_status := 'NOTSTARTED';
219 elsif p_lookup_code = 'CLOSE_PLAN' then
220 l_step_status := 'CLOSE';
221 end if;
222 when l_pc_status = 'ANALYSIS' then
223 if p_lookup_code = 'REVIEW_CRITERIA' then
224 l_step_status := 'DONE';
225 elsif p_lookup_code = 'INITIATE_PC' then
226 l_step_status := 'DONE';
227 elsif p_lookup_code = 'COLLECT_PROJECTS' then
228 l_step_status := 'DONE';
229 elsif p_lookup_code = 'DEVELOP_SCENARIOS' then
230 l_step_status := 'INPROGRESS';
231 elsif (p_lookup_code = 'SUBMIT_PLAN') then
232 if (scenarios_recommended(p_pc_id) = 'T') then
233 l_step_status := 'SUBMIT';
234 else
235 l_step_status := 'NOTSTARTED';
236 end if;
237 elsif p_lookup_code = 'APPROVE_PLAN' then
238 l_step_status := 'NOTSTARTED';
239 elsif p_lookup_code = 'CLOSE_PLAN' then
240 l_step_status := 'CLOSE';
241 end if;
242 when l_pc_status = 'SUBMITTED' then
243 if p_lookup_code = 'REVIEW_CRITERIA' then
244 l_step_status := 'DONE';
245 elsif p_lookup_code = 'INITIATE_PC' then
246 l_step_status := 'DONE';
247 elsif p_lookup_code = 'COLLECT_PROJECTS' then
248 l_step_status := 'DONE';
249 elsif p_lookup_code = 'DEVELOP_SCENARIOS' then
250 l_step_status := 'DONE';
251 elsif p_lookup_code = 'SUBMIT_PLAN' then
252 l_step_status := 'DONE';
253 elsif (p_lookup_code = 'APPROVE_PLAN') then
254 if (scenario_approved(p_pc_id) = 'T') then
255 l_step_status := 'APPROVE';
256 else
257 l_step_status := 'NOTSTARTED';
258 end if;
259 elsif p_lookup_code = 'CLOSE_PLAN' then
260 l_step_status := 'CLOSE';
261 end if;
262 when l_pc_status = 'APPROVED' then
263 if p_lookup_code = 'REVIEW_CRITERIA' then
264 l_step_status := 'DONE';
265 elsif p_lookup_code = 'INITIATE_PC' then
266 l_step_status := 'DONE';
267 elsif p_lookup_code = 'COLLECT_PROJECTS' then
268 l_step_status := 'DONE';
269 elsif p_lookup_code = 'DEVELOP_SCENARIOS' then
270 l_step_status := 'DONE';
271 elsif p_lookup_code = 'SUBMIT_PLAN' then
272 l_step_status := 'DONE';
273 elsif p_lookup_code = 'APPROVE_PLAN' then
274 l_step_status := 'DONE';
275 elsif p_lookup_code = 'CLOSE_PLAN' then
276 l_step_status := 'CLOSE';
277 end if;
278 when l_pc_status = 'CLOSED' then
279 if p_lookup_code = 'REVIEW_CRITERIA' then
280 l_step_status := 'DONE';
281 elsif p_lookup_code = 'INITIATE_PC' then
282 l_step_status := 'DONE';
283 elsif p_lookup_code = 'COLLECT_PROJECTS' then
284 l_step_status := 'DONE';
285 elsif p_lookup_code = 'DEVELOP_SCENARIOS' then
286 l_step_status := 'DONE';
287 elsif p_lookup_code = 'SUBMIT_PLAN' then
288 l_step_status := 'DONE';
289 elsif p_lookup_code = 'APPROVE_PLAN' then
290 l_step_status := 'DONE';
291 elsif p_lookup_code = 'CLOSE_PLAN' then
292 l_step_status := 'DONE';
293 end if;
294 end CASE;
295
296 return l_step_status;
297
298 EXCEPTION
299 WHEN OTHERS THEN
300 return null;
301
302 end Determine_PC_Checklist_status;
303
304 /************************************************************************************
305 ************************************************************************************/
306 -- This function determines if an organization is member of the
307 -- Organization hierarchy set in the PJP Profile Option
308 function Is_Org_In_PJP_Org_Hier(
309 p_org_id IN number
310 ) return number is
311
312 l_org_count number := 0;
313
314 begin
315
316 select count(c.org_structure_element_id)
317 into l_org_count
318 from per_org_structure_elements c,
319 per_organization_structures d
320 where d.organization_structure_id = fnd_profile.VALUE('PJP_ORGANIZATION_HIERARCHY')
321 and c.business_group_id = d.business_group_id
322 and c.org_structure_element_id = p_org_id;
323
324 return l_org_count;
325
326 EXCEPTION
327 WHEN OTHERS THEN
328 return null;
329
330 end Is_Org_In_PJP_Org_Hier;
331
332 /************************************************************************************
333 ************************************************************************************/
334 -- This function validates if a Class code can be assigned to a new Portfolio.
335 -- The validation is as follow.
336 -- A class code assigned to a Portfolio with no Organization cannot be used by any
337 -- other portfolio.
338 -- A class code assigned to a Portfolio with an Organization cannot be assigned
339 -- to another portfolio with the same organization
340 -- hierarchy cannot be assigned to a Portfolio with the same Organization or to a
341 -- Portfolio with no Organization.
342 -- A class code assigned to a Portfolio with an Organization in the PJP
343 -- hierarchy cannot be assigned to Portfolio with any Organization under the PJP
344 -- hierarchy or to a Portfolio with no Organization.
345 function Is_Class_Code_Available(
346 p_class_code IN number
347 ,p_org_id IN number
348 ) return varchar2 is
349
350 l_is_available VARCHAR2(1) := 'Y';
351 l_count NUMBER;
352
353 begin
354
355 --1st check
356 -- Can we assign a Class code to a portfolio with no Org? Only if this class code has
357 -- not been assigned yet.
358 if p_org_id is null then
359 select count(portfolio)
360 into l_count
361 from fpa_aw_portf_headers_v
362 where portfolio_class_code = p_class_code;
363 -- if class code already assigned return 'N'.
364 if l_count > 0 then
365 l_is_available := 'N';
366 return l_is_available;
367 end if;
368
369 else
370
371 -- 2nd check.
372 -- Can we assign a class code to a portfolio with an org? Only if the class code has
373 -- been assigned to a portfolio with no Org.
374 select count(portfolio)
375 into l_count
376 from fpa_aw_portf_headers_v
377 where portfolio_class_code = p_class_code
378 and portfolio_organization is null;
379 -- if class code already assigned return 'N'.
380 if l_count > 0 then
381 l_is_available := 'N';
382 return l_is_available;
383 end if;
384
385 -- 3rd check
386 -- Can we assign a class code to a portfolio with an org? Only if the class code has
387 -- been assigned to a portfolio with an Org that does not belong to the Org hierarchy
388 -- specified in the Profile Option.
389 -- to another portfolio with the same organization
390 select count(portfolio)
391 into l_count
392 from fpa_aw_portf_headers_v
393 where portfolio_class_code = p_class_code
394 and portfolio_organization in (
395 SELECT e.organization_id_child
396 FROM per_org_structure_elements e
397 ,PER_ORG_STRUCTURE_VERSIONS v
398 WHERE e.org_structure_version_id = v.ORG_STRUCTURE_VERSION_ID
399 and v.ORGANIZATION_STRUCTURE_ID = FND_PROFILE.VALUE('PJP_ORGANIZATION_HIERARCHY')
400 AND (TRUNC(SYSDATE) BETWEEN TRUNC(v.DATE_FROM) AND TRUNC(nvl(v.DATE_TO, sysdate)))
401 CONNECT BY PRIOR e.organization_id_child = e.organization_id_parent
402 AND PRIOR e.org_structure_version_id = v.ORG_STRUCTURE_VERSION_ID
403 START WITH e.organization_id_child = p_org_id
404 UNION
405 SELECT e.organization_id_parent
406 FROM per_org_structure_elements e
407 ,PER_ORG_STRUCTURE_VERSIONS v
408 WHERE e.org_structure_version_id = v.ORG_STRUCTURE_VERSION_ID
409 and v.ORGANIZATION_STRUCTURE_ID = FND_PROFILE.VALUE('PJP_ORGANIZATION_HIERARCHY')
410 AND (TRUNC(SYSDATE) BETWEEN TRUNC(v.DATE_FROM) AND TRUNC(nvl(v.DATE_TO, sysdate)))
411 CONNECT BY PRIOR e.organization_id_parent = e.organization_id_child
412 AND PRIOR e.org_structure_version_id = v.ORG_STRUCTURE_VERSION_ID
413 START WITH e.organization_id_child = p_org_id
414 UNION
415 SELECT e.organization_id_parent
416 FROM per_org_structure_elements e
417 ,PER_ORG_STRUCTURE_VERSIONS v
418 WHERE e.org_structure_version_id = v.ORG_STRUCTURE_VERSION_ID
419 and v.ORGANIZATION_STRUCTURE_ID = FND_PROFILE.VALUE('PJP_ORGANIZATION_HIERARCHY')
420 AND (TRUNC(SYSDATE) BETWEEN TRUNC(v.DATE_FROM) AND TRUNC(nvl(v.DATE_TO, sysdate)))
421 CONNECT BY PRIOR e.organization_id_parent = e.organization_id_child
422 AND PRIOR e.org_structure_version_id = v.ORG_STRUCTURE_VERSION_ID
423 START WITH e.organization_id_parent = p_org_id
424 );
425
426 -- If there's an Org in the hierarchy with the current class code.
427 if l_count > 0 then
428 l_is_available := 'N';
429 return l_is_available;
430 end if;
431
432 end if;
433
434 return l_is_available;
435
436 EXCEPTION
437 WHEN OTHERS THEN
438 return null;
439
440 end Is_Class_Code_Available;
441
442 /************************************************************************************
443 ************************************************************************************/
444 -- This function validates if an Organization can be assigned to a new Portfolio.
445 function Is_Organization_Available(
446 p_org_id IN number
447 ,p_class_code IN number
448 ) return varchar2 is
449
450 l_is_available VARCHAR2(1) := 'Y';
451 l_count NUMBER;
452
453 begin
454
455 -- 1st check.
456 -- Can we assign the Organization to a portfolio without knowing the Org? Only
457 -- if the org has not been assigned.
458 if p_class_code is null then
459 select count(portfolio)
460 into l_count
461 from fpa_aw_portf_headers_v
462 where portfolio_organization is not null
463 and portfolio_organization = p_org_id;
464 -- if class code already assigned return 'N'.
465 if l_count > 0 then
466 l_is_available := 'N';
467 return l_is_available;
468 end if;
469
470 -- else
471
472 end if;
473
474 return l_is_available;
475
476 EXCEPTION
477 WHEN OTHERS THEN
478 return null;
479
480 end Is_Organization_Available;
481
482 /************************************************************************************
483 ************************************************************************************/
484
485 procedure load_gl_calendar (
486 p_api_version IN NUMBER,
487 p_commit IN VARCHAR2,
488 p_calendar_name IN VARCHAR2,
489 p_period_type IN VARCHAR2,
490 p_cal_period_type IN VARCHAR2,
491 x_return_status OUT NOCOPY VARCHAR2,
492 x_msg_data OUT NOCOPY VARCHAR2,
493 x_msg_count OUT NOCOPY NUMBER) IS
494
495 -- cursor to check if GL Calendar has been loaded for any other planning cycle.
496
497 cursor c_cal_per_type_valid is
498 select 'VALID' from fpa_aw_pc_info_v
499 where calendar_name = p_calendar_name
500 and period_type = p_period_type and pc_status <> 'CREATED' and rownum < 2;
501
502 l_valid varchar2(30);
503 CAL_PARAMETERS_INVALID EXCEPTION;
504 l_str varchar2(2000);
505 BEGIN
506
507 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
508 FND_LOG.String
509 (
510 FND_LOG.LEVEL_PROCEDURE,
511 'fpa.sql.FPA_Utilities_Pvt.load_gl_calendar.begin',
512 'Entering FPA_Utilities_Pvt.load_gl_calendar'
513 );
514 END IF;
515
516 /** Bug 4995505
517 Commeting check below. Calendar period values will always be loaded.
518 **/
519 -- OPEN c_cal_per_type_valid;
520 -- fetch c_cal_per_type_valid into l_valid;
521
522 -- if c_cal_per_type_valid%NOTFOUND then
523 -- If cursor did not fetch any records,
524 -- The calendar periods should be loaded.
525 -- Call the OLAP program : LOAD_CALENDAR_PRG
526 l_str := 'call LOAD_CALENDAR_PRG('''||p_calendar_name||''''||' '||''''||p_period_type||''''||' '||''''||p_cal_period_type || ''')';
527 dbms_aw.execute(l_str);
528 -- end if;
529 -- CLOSE c_cal_per_type_valid;
530
531 IF (p_commit = FND_API.G_TRUE) THEN
532 dbms_aw.execute('UPDATE');
533 COMMIT;
534 END IF;
535
536 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
537 FND_LOG.String
538 (
539 FND_LOG.LEVEL_PROCEDURE,
540 'fpa.sql.FPA_Utilities_Pvt.load_gl_calendar.end',
541 'Entering FPA_Utilities_Pvt.load_gl_calendar'
542 );
543 END IF;
544
545 EXCEPTION
546
547 WHEN OTHERS then
548
549 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
550
551 IF FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_ERROR THEN
552 FND_LOG.String
553 (
554 FND_LOG.LEVEL_ERROR,
555 'fpa.sql.FPA_Utilities_Pvt.load_gl_calendar',
556 SQLERRM
557 );
558 END IF;
559
560 FND_MSG_PUB.count_and_get
561 (
562 p_count => x_msg_count,
563 p_data => x_msg_data
564 );
565 RAISE;
566
567 END;
568
569 function scenarios_recommended(
570 p_planning_cycle_id IN NUMBER
571 ) return varchar2 IS
572 l_scenario_count number;
573 l_scen_recommended varchar2(1);
574 BEGIN
575
576 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
577 FND_LOG.String
578 (
579 FND_LOG.LEVEL_PROCEDURE,
580 'fpa.sql.FPA_Utils_Pvt.scenarios_recommended.start',
581 'Entering FPA_Utils_Pvt.scenarios_recommended'
582 );
583 END IF;
584
585 -- Count scenarios in Recommended status.
586 select count(scenario) into l_scenario_count
587 from fpa_aw_sce_info_v
588 where planning_cycle = p_planning_cycle_id
589 and recommended_flag = 1;
590
591 IF l_scenario_count > 0 then
592 l_scen_recommended := 'T';
593 else
594 l_scen_recommended := 'F';
595 end if;
596
597 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
598 FND_LOG.String
599 (
600 FND_LOG.LEVEL_PROCEDURE,
601 'fpa.sql.FPA_Utils_Pvt.scenarios_recommended.end',
602 'Exiting FPA_Utils_Pvt.scenarios_recommended'
603 );
604 END IF;
605
606
607 return l_scen_recommended;
608
609 EXCEPTION
610
611 WHEN OTHERS then
612 IF FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_ERROR THEN
613 FND_LOG.String
614 (
615 FND_LOG.LEVEL_ERROR,
616 'fpa.sql.FPA_Utils_Pvt.scenarios_recommended',
617 SQLERRM
618 );
619 END IF;
620 return l_scen_recommended;
621
622 end scenarios_recommended;
623
624
625 function scenario_approved(
626 p_planning_cycle_id IN NUMBER
627 ) return varchar2 IS
628 l_scenario_count number;
629 l_scen_approved varchar2(1) := 'F';
630 BEGIN
631
632 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
633 FND_LOG.String
634 (
635 FND_LOG.LEVEL_PROCEDURE,
636 'fpa.sql.FPA_Utils_Pvt.scenario_approved.start',
637 'Entering FPA_Utils_Pvt.scenario_approved'
638 );
639 END IF;
640
641 -- Count scenarios in approved status.
642 select count(scenario) into l_scenario_count
643 from fpa_aw_sce_info_v
644 where planning_cycle = p_planning_cycle_id
645 and approved_flag = 1;
646
647 IF l_scenario_count > 0 then
648 l_scen_approved := 'T';
649 else
650 l_scen_approved := 'F';
651 end if;
652
653 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
654 FND_LOG.String
655 (
656 FND_LOG.LEVEL_PROCEDURE,
657 'fpa.sql.FPA_Utils_Pvt.scenario_approved.end',
658 'Exiting FPA_Utils_Pvt.scenario_approved'
659 );
660 END IF;
661
662
663 return l_scen_approved;
664
665 EXCEPTION
666
667 WHEN OTHERS then
668 IF FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_ERROR THEN
669 FND_LOG.String
670 (
671 FND_LOG.LEVEL_ERROR,
672 'fpa.sql.FPA_Utils_Pvt.scenario_approved',
673 SQLERRM
674 );
675 END IF;
676 return l_scen_approved;
677
678 end scenario_approved;
679
680
681
682 -- Called from Advanced filter region to render category name as the label
683
684 procedure get_inv_category_name(
685 p_planning_cycle_id in number,
686 x_inv_category_name out nocopy varchar2
687 ) is
688 cursor c_inv_cat_name is
689 select class_category from pa_class_categories pac, fpa_aw_pc_info_v pci
690 where pac.class_category_id = pci.pc_category
691 and pci.planning_cycle = p_planning_cycle_id;
692
693 l_inv_cat_name pa_class_categories.class_category%type;
694
695 begin
696 open c_inv_cat_name;
697 fetch c_inv_cat_name into l_inv_cat_name;
698 close c_inv_cat_name;
699
700 x_inv_category_name := l_inv_cat_name;
701 return;
702
703 end;
704
705 procedure get_approver_name(
706 p_portfolio_id in number,
707 x_approver_names out nocopy varchar2
708 ) is
709
710 cursor c_approver_names is
711 select pe.full_name
712 from
713 pa_project_parties ppp, pa_project_role_types par , fnd_menu_entries fndm ,fnd_form_functions fndf,
714 per_all_people_f pe, per_all_assignments_f prd
715 where
716 ppp.object_type = 'PJP_PORTFOLIO' and ppp.object_id = p_portfolio_id
717 and ppp.PROJECT_ROLE_ID = par.project_role_id
718 and par.menu_id = fndm.menu_id
719 and fndm.function_id = fndf.function_id
720 and fndf.function_name = 'FPA_SEC_APPROVE_PC'
721 and ppp.resource_type_id = 101
722 AND ppp.resource_source_id = pe.person_id
723 AND trunc(sysdate) BETWEEN trunc(ppp.start_date_active) AND trunc(NVL(ppp.end_date_active,sysdate))
724 AND trunc(sysdate) BETWEEN trunc(pe.effective_start_date) AND trunc(pe.effective_end_date)
725 AND ppp.resource_source_id = prd.person_id
726 AND prd.primary_flag = 'Y'
727 AND prd.assignment_type = 'E'
728 AND trunc(sysdate) BETWEEN trunc(prd.effective_start_date) AND trunc(prd.effective_end_date)
729 AND par.language = userenv('LANG')
730 AND par.project_role_type = 'PORTFOLIO_APPROVER';
731
732
733 l_approver_name varchar2(240);
734 l_concat_approver_names varchar2(2000);
735
736 begin
737 open c_approver_names;
738 loop
739 fetch c_approver_names into l_approver_name;
740 exit when c_approver_names%notfound;
741 l_concat_approver_names := l_concat_approver_names||l_approver_name||'; ';
742 end loop;
743 close c_approver_names;
744 x_approver_names := rtrim(l_concat_approver_names,'; ');
745 return;
746 exception
747 when others then
748 null;
749 end;
750
751 function Get_Fin_Metric_Unit(
752 p_planning_cycle_id IN NUMBER,
753 p_metric_code IN VARCHAR2
754 ) return VARCHAR2 is
755
756 l_unit varchar2(25);
757
758 BEGIN
759
760 if p_metric_code = 'NPV' then
761 select NVL(currency_code,NULL) into l_unit from fpa_aw_pc_info_v
762 where planning_cycle = p_planning_cycle_id;
763 else
764 select NVL(meaning,NULL) into l_unit from fpa_lookups_V
765 where lookup_type ='FPA_UNITS'
766 AND lookup_code = decode(p_metric_code, 'IRR', 'PERCENT','PAYBACKPERIOD', 'MONTHS');
767 end if;
768
769 return l_unit;
770 EXCEPTION
771 WHEN OTHERS then
772 IF FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_ERROR THEN
773 FND_LOG.String
774 (
775 FND_LOG.LEVEL_ERROR,
776 'fpa.sql.FPA_Utils_Pvt.Get_Fin_Metric_Unit',
777 SQLERRM
778 );
779 END IF;
780 return l_unit;
781 END Get_Fin_Metric_Unit;
782
783 /****END: Section for common API messages, exception handling and logging.******
784 ********************************************************************************/
785 -------------------------------------------------------------------
786 -- Utility Function to Return the Correct Number data
787 -- even if the decimal character is changed.
788 -------------------------------------------------------------------
789 FUNCTION GET_FORMATTED_NUM(P_INPUT_STR IN VARCHAR2 )
790 RETURN NUMBER IS
791
792 L_DECIMAL_MARKER VARCHAR2(1);
793 L_OUTPUT_STR NUMBER;
794
795 BEGIN
796 SELECT SUBSTR(VALUE,1,1)
797 INTO L_DECIMAL_MARKER
798 FROM NLS_SESSION_PARAMETERS
799 WHERE PARAMETER = 'NLS_NUMERIC_CHARACTERS';
800
801 L_OUTPUT_STR := REPLACE( P_INPUT_STR , L_DECIMAL_MARKER,'.');
802 RETURN To_Number(L_OUTPUT_STR);
803
804 EXCEPTION
805 WHEN OTHERS THEN
806 NULL;
807 RETURN L_OUTPUT_STR;
808
809 END GET_FORMATTED_NUM;
810
811 end FPA_Utils_PVT;
[Home] [Help]