1 PACKAGE BODY hri_oltp_pmv_rank_abs AS
2 /* $Header: hrioprkac.pkb 120.2 2005/10/13 09:19 cbridge noship $ */
3
4 TYPE category_rec_type is RECORD
5 (category_code VARCHAR2(80)
6 ,category_name VARCHAR2(240));
7
8 TYPE abs_category_tab_type is TABLE of category_rec_type INDEX BY BINARY_INTEGER;
9
10 g_categories abs_category_tab_type;
11
12 g_max_no_categories PLS_INTEGER := 4;
13
14 g_supervisor_id NUMBER;
15 g_effective_from_date DATE;
16 g_effective_to_date DATE;
17
18 g_not_used VARCHAR2(240) := hri_oltp_view_message.get_not_used_msg;
19
20 /* Sets the top categories, ordered by abs_drtn as of the given date */
21 /* Returns the desired number in a PLSQL table */
22 PROCEDURE set_top_categories
23 (p_supervisor_id IN NUMBER,
24 p_effective_from_date IN DATE,
25 p_effective_to_date IN DATE,
26 p_no_categories IN PLS_INTEGER,
27 p_category_tab OUT NOCOPY abs_category_tab) IS
28
29 -- Cursor to get top categories
30 CURSOR c_get_categories IS
31 SELECT
32 top.category_code
33 ,cat.value category_name
34 ,top.category_rank
35 FROM
36 hri_cl_absnc_cat_v cat
37 ,(SELECT
38 tab.category_code
39 ,tab.abs_drtn_days
40 ,ROWNUM category_rank
41 FROM
42 (SELECT
43 SUM(fact.abs_drtn_days) abs_drtn_days
44 ,fact.absence_category_code category_code
45 FROM
46 hri_mdp_sup_absnc_cat_mv fact
47 WHERE fact.supervisor_person_id = p_supervisor_id
48 AND fact.effective_date BETWEEN p_effective_from_date AND p_effective_to_date
49 GROUP BY
50 fact.absence_category_code
51 ORDER BY
52 SUM(fact.abs_drtn_days) DESC
53 ,fact.absence_category_code
54 ) tab
55 WHERE ROWNUM <= g_max_no_categories
56 ) top
57 WHERE top.category_code = cat.id;
58
59 BEGIN
60
61 IF (g_supervisor_id = p_supervisor_id AND
62 g_effective_from_date = p_effective_from_date AND
63 g_effective_to_date = p_effective_to_date AND
64 p_no_categories <= g_max_no_categories) THEN
65
66 /* Cache hit */
67 NULL;
68
69 ELSE
70
71 /* Reset globals */
72 g_supervisor_id := p_supervisor_id;
73 g_effective_from_date := p_effective_from_date;
74 g_effective_to_date := p_effective_to_date;
75
76 /* Adjust maximum if necessary */
77 IF (p_no_categories > g_max_no_categories) THEN
78 g_max_no_categories := p_no_categories;
79 END IF;
80
81 /* Reset all values in table to "Not Used" */
82 FOR i IN 1..g_max_no_categories LOOP
83 g_categories(i).category_code := g_not_used;
84 g_categories(i).category_name := g_not_used;
85 END LOOP;
86
87 /* Populate table with top countries */
88 FOR cat_rec IN c_get_categories LOOP
89 g_categories(cat_rec.category_rank).category_code := cat_rec.category_code;
90 g_categories(cat_rec.category_rank).category_name := cat_rec.category_name;
91 END LOOP;
92
93
94 END IF;
95
96 FOR i IN 1..p_no_categories LOOP
97 p_category_tab(i) := g_categories(i).category_code;
98 END LOOP;
99
100 END set_top_categories;
101
102 -- Function to return category names by rank
103 FUNCTION get_category_name(p_rank IN NUMBER)
104 RETURN VARCHAR2 IS
105
106 BEGIN
107
108 RETURN g_categories(p_rank).category_name;
109
110 EXCEPTION WHEN OTHERS THEN
111
112 RETURN g_not_used || ' (' || to_char(p_rank) || ')';
113
114 END get_category_name;
115
116 -- Function to return category code by rank
117 FUNCTION get_category_code(p_rank IN NUMBER)
118 RETURN VARCHAR2 IS
119
120 BEGIN
121
122 RETURN NVL(g_categories(p_rank).category_code,'NA_EDW');
123
124 EXCEPTION WHEN OTHERS THEN
125
126 RETURN g_not_used || ' (' || to_char(p_rank) || ')';
127
128 END get_category_code;
129
130
131
132 END hri_oltp_pmv_rank_abs;
[Home] [Help]