1 PACKAGE BODY pa_perf_status_client_extn AS
2 /* $Header: PAPESCLB.pls 120.0 2005/05/30 12:00:02 appldev noship $ */
3
4 /*======================Beginning of code====================================+
5
6 The following code demonstrate how you can use the client extension
7 to customize the logic to obtain the overall project performance status
8
9 It implements the default logic MIN, which returns the most severe indicator
10 for a given project.
11 +============================================================================*/
12
13 function get_performance_status
14 (
15 p_object_type in VARCHAR2 --- PA_PROJECTS
16 , p_object_id in NUMBER --- PROJECT_ID
17 , p_kpa_summary in pa_exception_engine_pkg.summary_table
18 )RETURN varchar2
19 IS
20 --- cursor to get the severity level of a indicator code
21 --- the severity level is from 1 to 5, 1 being the most severe
22
23 CURSOR get_severity(indicator_code IN VARCHAR2)
24 IS
25 SELECT To_number(predefined_flag) FROM pa_lookups
26 WHERE lookup_type = 'PA_PERF_INDICATORS'
27 AND lookup_code = indicator_code;
28
29 l_lowest_severity NUMBER := 100;
30
31 l_ind VARCHAR2(30);
32
33 l_severity NUMBER := NULL ;
34
35 BEGIN
36
37 --- the default logic for getting the overall performance status
38 --- is to do a MIN
39 --- MIN is taking the worst case scenario. The color indicator associated with
40 --- lowest severity number (which means the highest severity) will be returned.
41
42 --- when there is no color indicator passed to the function, we will return NULL
43 IF p_kpa_summary IS NULL THEN
44 RETURN NULL;
45
46 END IF;
47
48 --- loop through all indicators and find the most severe one
49 FOR i IN p_kpa_summary.first..p_kpa_summary.last LOOP
50
51 OPEN get_severity(p_kpa_summary(i).indicator_code);
52 FETCH get_severity INTO l_severity;
53
54 IF (get_severity%found) then
55
56 IF (l_severity IS NOT NULL AND l_severity < l_lowest_severity) THEN
57 --- find a more severe indicator, save it to the local variable
58 l_lowest_severity := l_severity;
59 l_ind := p_kpa_summary(i).indicator_code;
60 END IF;
61
62 END IF;
63
64 CLOSE get_severity;
65
66 END LOOP;
67
68 IF l_lowest_severity = 100 THEN
69 --- if the MIN logic has not found any indicator, return NULL
70 RETURN NULL;
71 ELSE
72 --- return the most severe indicator
73 RETURN l_ind;
74 END IF;
75
76
77
78
79 EXCEPTION
80
81 WHEN NO_DATA_FOUND THEN
82
83 RETURN NULL;
84
85 WHEN OTHERS THEN
86 RETURN NULL;
87
88 END;
89
90
91 END pa_perf_status_client_extn;
[Home] [Help]