1 PACKAGE BODY hri_oltp_pmv_rank_ctr AS
2 /* $Header: hrioprkc.pkb 120.0 2005/05/29 07:34 appldev noship $ */
3
4 TYPE country_rec_type is RECORD
5 (country_code VARCHAR2(80)
6 ,country_name VARCHAR2(240));
7
8 TYPE country_rec_tab_type is TABLE of country_rec_type INDEX BY BINARY_INTEGER;
9
10 g_countries country_rec_tab_type;
11
12 g_max_no_countries PLS_INTEGER := 4;
13
14 g_supervisor_id NUMBER;
15 g_effective_date DATE;
16
17 g_not_used VARCHAR2(240) := hri_oltp_view_message.get_not_used_msg;
18
19 /* Sets the top countries, ordered by headcount as of the given date */
20 /* Returns the desired number in a PLSQL table */
21 PROCEDURE set_top_countries
22 (p_supervisor_id IN NUMBER,
23 p_effective_date IN DATE,
24 p_no_countries IN PLS_INTEGER,
25 p_country_tab OUT NOCOPY country_tab_type) IS
26
27 -- Cursor to get top countries
28 CURSOR c_get_countries IS
29 SELECT
30 top.country_code
31 ,ctr.value country_name
32 ,top.country_rank
33 FROM
34 hri_dbi_cl_geo_country_v ctr
35 ,(SELECT
36 tab.country_code
37 ,tab.total_headcount
38 ,ROWNUM country_rank
39 FROM
40 (SELECT
41 hc.geo_country_code country_code
42 ,SUM(hc.total_headcount) total_headcount
43 FROM
44 hri_mdp_sup_wrkfc_ctr_mv hc
45 WHERE hc.supervisor_person_id = p_supervisor_id
46 AND p_effective_date BETWEEN effective_start_date AND effective_end_date
47 GROUP BY
48 hc.geo_country_code
49 ORDER BY
50 SUM(hc.total_headcount) DESC
51 ,hc.geo_country_code
52 ) tab
53 WHERE ROWNUM <= g_max_no_countries
54 ) top
55 WHERE top.country_code = ctr.id;
56
57 BEGIN
58
59 IF (g_supervisor_id = p_supervisor_id AND
60 g_effective_date = p_effective_date AND
61 p_no_countries <= g_max_no_countries) THEN
62
63 /* Cache hit */
64 NULL;
65
66 ELSE
67
68 /* Reset globals */
69 g_supervisor_id := p_supervisor_id;
70 g_effective_date := p_effective_date;
71
72 /* Adjust maximum if necessary */
73 IF (p_no_countries > g_max_no_countries) THEN
74 g_max_no_countries := p_no_countries;
75 END IF;
76
77 /* Reset all values in table to "Not Used" */
78 FOR i IN 1..g_max_no_countries LOOP
79 g_countries(i).country_code := g_not_used;
80 g_countries(i).country_name := g_not_used;
81 END LOOP;
82
83 /* Populate table with top countries */
84 FOR ctr_rec IN c_get_countries LOOP
85 g_countries(ctr_rec.country_rank).country_code := ctr_rec.country_code;
86 g_countries(ctr_rec.country_rank).country_name := ctr_rec.country_name;
87 END LOOP;
88
89 END IF;
90
91 FOR i IN 1..p_no_countries LOOP
92 p_country_tab(i) := g_countries(i).country_code;
93 END LOOP;
94
95 END set_top_countries;
96
97 -- Function to return country names by rank
98 FUNCTION get_country_name(p_rank IN NUMBER)
99 RETURN VARCHAR2 IS
100
101 BEGIN
102
103 RETURN g_countries(p_rank).country_name;
104
105 EXCEPTION WHEN OTHERS THEN
106
107 RETURN g_not_used || ' (' || to_char(p_rank) || ')';
108
109 END get_country_name;
110
111 END hri_oltp_pmv_rank_ctr;
[Home] [Help]