1 PACKAGE BODY hri_opl_rec_bands AS
2 /* $Header: hriprbnd.pkb 115.2 2002/11/19 11:35:05 jtitmas noship $ */
3
4 TYPE g_band_rec IS RECORD(g_min_value NUMBER,
5 g_max_value NUMBER,
6 g_band_name VARCHAR2(30));
7
8 TYPE g_bands_tabtype IS TABLE OF g_band_rec INDEX BY BINARY_INTEGER;
9
10
11 g_apl_bands_tab g_bands_tabtype;
12 g_vac_bands_tab g_bands_tabtype;
13
14 /* Bug 2669294 - Incremented band min value for the displayed name if it */
15 /* is non-zero */
16 CURSOR g_apl_time_bands_csr IS
17 SELECT
18 band_min_value min_val
19 ,band_max_value max_val
20 ,to_char(DECODE(band_min_value, 0, 0, band_min_value + 1)) ||
21 DECODE(band_max_value, to_number(null), ' +', ' - ' || to_char(band_max_value))
22 band_name
23 ,band_sequence seq_no
24 FROM hri_time_bands
25 WHERE type = 'APL_STAGE';
26
27 /* Bug 2669294 - Incremented band min value for the displayed name if it */
28 /* is non-zero */
29 CURSOR g_vac_time_bands_csr IS
30 SELECT
31 band_min_value min_val
32 ,band_max_value max_val
33 ,to_char(DECODE(band_min_value, 0, 0, band_min_value + 1)) ||
34 DECODE(band_max_value, to_number(null), ' +', ' - ' || to_char(band_max_value))
35 band_name
36 ,band_sequence seq_no
37 FROM hri_time_bands
38 WHERE type = 'VACANCY';
39
40 /******************************************************************************/
41 /* Returns an indicator for whether the supplied value is within the */
42 /* specified applicant time band */
43 /******************************************************************************/
44 FUNCTION is_in_apl_band(p_value IN NUMBER,
45 p_band_sequence IN NUMBER)
46 RETURN NUMBER IS
47
48 BEGIN
49
50 /* Bug 2673387 - Fixed condition */
51 IF ((g_apl_bands_tab(p_band_sequence).g_min_value < p_value OR
52 g_apl_bands_tab(p_band_sequence).g_min_value = 0) AND
53 (p_value <= g_apl_bands_tab(p_band_sequence).g_max_value OR
54 g_apl_bands_tab(p_band_sequence).g_max_value IS NULL)) THEN
55 RETURN 1;
56 ELSE
57 RETURN 0;
58 END IF;
59
60 EXCEPTION
61 WHEN OTHERS THEN
62
63 RETURN to_number(null);
64
65 END is_in_apl_band;
66
67 /******************************************************************************/
68 /* Returns an indicator for whether the supplied value is within the */
69 /* specified vacancy time band */
70 /******************************************************************************/
71 FUNCTION is_in_vac_band(p_value IN NUMBER,
72 p_band_sequence IN NUMBER)
73 RETURN NUMBER IS
74
75 BEGIN
76
77 /* Bug 2673387 - Fixed condition */
78 IF ((g_vac_bands_tab(p_band_sequence).g_min_value < p_value OR
79 g_vac_bands_tab(p_band_sequence).g_min_value = 0) AND
80 (p_value <= g_vac_bands_tab(p_band_sequence).g_max_value OR
81 g_vac_bands_tab(p_band_sequence).g_max_value IS NULL)) THEN
82 RETURN 1;
83 ELSE
84 RETURN 0;
85 END IF;
86
87 EXCEPTION
88 WHEN OTHERS THEN
89
90 RETURN to_number(null);
91
92 END is_in_vac_band;
93
94 /******************************************************************************/
95 /* Function returning a specific band name from the global table */
96 /******************************************************************************/
97 FUNCTION get_apl_time_band_name(p_band_sequence IN NUMBER)
98 RETURN VARCHAR2 IS
99
100 BEGIN
101
102 RETURN g_apl_bands_tab(p_band_sequence).g_band_name;
103
104 EXCEPTION WHEN OTHERS THEN
105
106 RETURN to_char(p_band_sequence);
107
108 END get_apl_time_band_name;
109
110 /******************************************************************************/
111 /* Function returning a specific band name from the global table */
112 /******************************************************************************/
113 FUNCTION get_vac_time_band_name(p_band_sequence IN NUMBER)
114 RETURN VARCHAR2 IS
115
116 BEGIN
117
118 RETURN g_vac_bands_tab(p_band_sequence).g_band_name;
119
120 EXCEPTION WHEN OTHERS THEN
121
122 RETURN to_char(p_band_sequence);
123
124 END get_vac_time_band_name;
125
126 /******************************************************************************/
127 /* INITIALIZATION SECTION */
128 /******************************************************************************/
129 BEGIN
130
131 FOR g_apl_rec IN g_apl_time_bands_csr LOOP
132 g_apl_bands_tab(g_apl_rec.seq_no).g_min_value := g_apl_rec.min_val;
133 g_apl_bands_tab(g_apl_rec.seq_no).g_max_value := g_apl_rec.max_val;
134 g_apl_bands_tab(g_apl_rec.seq_no).g_band_name := g_apl_rec.band_name;
135 END LOOP;
136
137 FOR g_vac_rec IN g_vac_time_bands_csr LOOP
138 g_vac_bands_tab(g_vac_rec.seq_no).g_min_value := g_vac_rec.min_val;
139 g_vac_bands_tab(g_vac_rec.seq_no).g_max_value := g_vac_rec.max_val;
140 g_vac_bands_tab(g_vac_rec.seq_no).g_band_name := g_vac_rec.band_name;
141 END LOOP;
142
143 END hri_opl_rec_bands;
[Home] [Help]