1 package body pe_get_val_pkg as
2 /* $Header: pepyppgr.pkb 120.2 2005/12/01 01:04:45 ggnanagu noship $ */
3 --
4 -- define private global vars
5 --
6 g_grade_id pay_grade_rules_f.grade_or_spinal_point_id%TYPE;
7 g_rate_id pay_grade_rules_f.rate_id%TYPE;
8 g_change_date DATE;
9 g_min_val pay_grade_rules_f.MINIMUM%TYPE;
10 g_mid_val pay_grade_rules_f.mid_value%TYPE;
11 g_max_val pay_grade_rules_f.maximum%TYPE;
12
13 --
14 --
15 -- define private global cursor
16 -- Cursor to get the min,max and mid value from pay_grade_rules_f
17 --
18 CURSOR g_csr_get_grade_values
19 IS
20 SELECT g.MINIMUM, g.mid_value, g.maximum
21 FROM pay_grade_rules_f g
22 WHERE g.grade_or_spinal_point_id = g_grade_id
23 AND g.rate_id = g_rate_id
24 AND g_change_date BETWEEN g.effective_start_date AND g.effective_end_date;
25
26 --
27 -- Description
28 -- This procedure returns the the minmum, mid_value or maximum value
29 -- for a given grade.
30 -- This function is used in hrv_salary_proposal
31 --
32 FUNCTION get_grade_value (
33 p_grade_id NUMBER,
34 p_rate_id NUMBER,
35 p_change_date DATE,
36 p_which_value VARCHAR2
37 )
38 RETURN VARCHAR2
39 IS
40 BEGIN
41 IF ( p_grade_id IS NULL
42 OR p_rate_id IS NULL
43 OR p_change_date IS NULL
44 OR p_which_value IS NULL
45 )
46 THEN
47 RETURN NULL;
48 END IF;
49 IF g_grade_id = p_grade_id
50 AND p_change_date = g_change_date
51 AND g_rate_id = g_rate_id
52 THEN
53 -- No need to execute the Cursor
54 NULL;
55 ELSE
56 -- set the global vars used by the cursor
57 g_grade_id := p_grade_id;
58 g_rate_id := p_rate_id;
59 g_change_date := p_change_date;
60
61 --execute the cursor
62 OPEN g_csr_get_grade_values;
63
64 FETCH g_csr_get_grade_values
65 INTO g_min_val, g_mid_val, g_max_val;
66
67 CLOSE g_csr_get_grade_values;
68 END IF;
69 IF p_which_value = 'MIN'
70 THEN
71 RETURN (g_min_val);
72 ELSIF p_which_value = 'MID'
73 THEN
74 RETURN (g_mid_val);
75 ELSIF p_which_value = 'MAX'
76 THEN
77 RETURN (g_max_val);
78 ELSE
79 RETURN (NULL);
80 END IF;
81 EXCEPTION
82 WHEN OTHERS THEN
83 -- close the g_csr_get_grade_values if its open
84 IF g_csr_get_grade_values%ISOPEN THEN
85 CLOSE g_csr_get_grade_values;
86 END IF;
87 -- set the g_min_val, g_mid_val, g_max_val global vars to NULL
88 g_min_val := NULL;
89 g_mid_val := NULL;
90 g_max_val := NULL;
91 -- as an exception occurred, return NULL
92 RETURN(NULL);
93 END get_grade_value;
94 --
95 END pe_get_val_pkg;
[Home] [Help]