1 PACKAGE BODY hri_apl_time AS
2 /* $Header: hriatime.pkb 115.3 2002/11/25 10:39:03 jtitmas noship $ */
3
4 /******************************************************************************/
5 /* TIME BANDS SECTION */
6 /* */
7 /* The time bands table collects together various different sets of time */
8 /* bands. Each set of time bands has a unique identifier "TYPE". For any */
9 /* given TYPE there must exist a complete set of time bands in the table. A */
10 /* complete set of bands covers the entire range of possible values (0 and */
11 /* above). Each band contains values including the minimum but excluding the */
12 /* maximum. A band with a NULL maximum value contains all values from the */
13 /* minimum up. */
14 /******************************************************************************/
15
16 /******************************************************************************/
17 /* Converts a band composite of days, weeks, months and years to the band min */
18 /* using the grain passed in of days, months or years */
19 /******************************************************************************/
20 FUNCTION convert_band_to_min(p_grain IN VARCHAR2,
21 p_years IN NUMBER,
22 p_months IN NUMBER,
23 p_weeks IN NUMBER,
24 p_days IN NUMBER)
25 RETURN NUMBER IS
26
27 /* Defines the number of days in a months */
28 l_days_in_month NUMBER := 30.4375;
29
30 BEGIN
31
32 IF (p_grain = 'DAY') THEN
33 RETURN ROUND( p_days + (7 * p_weeks) +
34 (l_days_in_month * (p_months + (12 * p_years))), 2 );
35 ELSIF (p_grain = 'MONTH') THEN
36 RETURN ROUND( ((p_days + (7 * p_weeks)) / l_days_in_month) +
37 p_months + (12 * p_years), 2 );
38 ELSIF (p_grain = 'YEAR') THEN
39 RETURN ROUND( ((p_days + (7 * p_weeks)) / (12 * l_days_in_month)) +
40 (p_months / 12) + p_years, 2 );
41 END IF;
42
43 RETURN to_number(null);
44
45 END convert_band_to_min;
46
47
48 /******************************************************************************/
49 /* Inserts a band given its minimum value. This function assumes the band */
50 /* type already has a complete set of bands. If there is already a band with */
51 /* the given minimum value then no action is taken. Otherwise, the existing */
52 /* band which the given minumum value falls into is split into two bands. */
53 /******************************************************************************/
54 PROCEDURE insert_time_band(p_type IN VARCHAR2,
55 p_band_min_day_comp IN NUMBER,
56 p_band_min_week_comp IN NUMBER,
57 p_band_min_month_comp IN NUMBER,
58 p_band_min_year_comp IN NUMBER) IS
59
60 l_band_min_value NUMBER; -- minimum value of band to insert
61 l_band_to_split hri_time_bands%rowtype; -- band to split
62
63 l_max_value NUMBER;
64 l_max_day NUMBER;
65 l_max_week NUMBER;
66 l_max_month NUMBER;
67 l_max_year NUMBER;
68 l_grain VARCHAR2(10);
69
70 /* Picks out the band to split (the band which the given minimum value */
71 /* falls into) */
72 CURSOR split_cur(v_band_min_value IN NUMBER) IS
73 SELECT *
74 FROM hri_time_bands
75 WHERE type = p_type
76 AND v_band_min_value > band_min_value
77 AND (v_band_min_value < band_max_value
78 OR band_max_value IS NULL);
79
80 BEGIN
81
82 /* This section defines the grain - either year, month or day */
83 IF (p_type = 'AGE') THEN
84 l_grain := 'YEAR';
85 ELSIF (p_type = 'LOW') THEN
86 l_grain := 'MONTH';
87 ELSIF (p_type = 'VACANCY' OR
88 p_type = 'APL_STAGE') THEN
89 l_grain := 'DAY';
90 END IF;
91
92 /* Find given minimum value */
93 l_band_min_value := convert_band_to_min(p_grain => l_grain,
94 p_years => p_band_min_year_comp,
95 p_months => p_band_min_month_comp,
96 p_weeks => p_band_min_week_comp,
97 p_days => p_band_min_day_comp);
98
99 /* Find the existing band which this minimum value falls into */
100 OPEN split_cur(l_band_min_value);
101 FETCH split_cur INTO l_band_to_split;
102 /* If the minimum value doesn't fall into an existing band do nothing */
103 IF (split_cur%NOTFOUND OR split_cur%NOTFOUND IS NULL) THEN
104 CLOSE split_cur;
105 /* Otherwise split the existing band */
106 ELSE
107 /* Increment the band sequence for higher bands */
108 UPDATE hri_time_bands
109 SET band_sequence = band_sequence + 1
110 WHERE band_sequence > l_band_to_split.band_sequence
111 AND type = p_type;
112
113 /* Insert a new band with the new minimum and the existing maximum */
114 INSERT INTO hri_time_bands
115 (type
116 ,band_min_value
117 ,band_max_value
118 ,band_sequence
119 ,band_min_day_comp
120 ,band_min_week_comp
121 ,band_min_month_comp
122 ,band_min_year_comp
123 ,band_max_day_comp
124 ,band_max_week_comp
125 ,band_max_month_comp
126 ,band_max_year_comp)
127 VALUES
128 (p_type
129 ,l_band_min_value
130 ,l_band_to_split.band_max_value
131 ,l_band_to_split.band_sequence + 1
132 ,NVL(p_band_min_day_comp,0)
133 ,NVL(p_band_min_week_comp,0)
134 ,NVL(p_band_min_month_comp,0)
135 ,NVL(p_band_min_year_comp,0)
136 ,l_band_to_split.band_max_day_comp
137 ,l_band_to_split.band_max_week_comp
138 ,l_band_to_split.band_max_month_comp
139 ,l_band_to_split.band_max_year_comp);
140
141 /* Update the band with the existing minimum to end at the new minimum */
142 UPDATE hri_time_bands
143 SET band_max_value = l_band_min_value,
144 band_max_year_comp = p_band_min_year_comp,
145 band_max_month_comp = p_band_min_month_comp,
146 band_max_week_comp = p_band_min_week_comp,
147 band_max_day_comp = p_band_min_day_comp
148 WHERE band_min_value = l_band_to_split.band_min_value
149 AND type = p_type;
150
151 END IF;
152
153 END insert_time_band;
154
155
156 /******************************************************************************/
157 /* Removes a band given its minimum value. This function assumes the band */
158 /* type already has a complete set of bands. If the band to delete is found */
159 /* then it is removed and the previous band extended to cover the same values */
160 /* otherwise no action is taken. */
161 /******************************************************************************/
162 PROCEDURE remove_time_band(p_type IN VARCHAR2,
163 p_band_min_day_comp IN NUMBER,
164 p_band_min_week_comp IN NUMBER,
165 p_band_min_month_comp IN NUMBER,
166 p_band_min_year_comp IN NUMBER) IS
167
168 l_band_min_value NUMBER; -- minimum value of band to remove
169 l_band_to_delete hri_time_bands%rowtype; -- band to remove
170 l_band_to_grow hri_time_bands%rowtype; -- band to remove
171
172 /* Selects information from band to be deleted */
173 CURSOR delete_cur IS
174 SELECT *
175 FROM hri_time_bands
176 WHERE type = p_type
177 AND band_min_day_comp = p_band_min_day_comp
178 AND band_min_week_comp = p_band_min_week_comp
179 AND band_min_month_comp = p_band_min_month_comp
180 AND band_min_year_comp = p_band_min_year_comp;
181
182 /* Selects information from band to be extended */
183 CURSOR grow_cur IS
184 SELECT *
185 FROM hri_time_bands
186 WHERE type = p_type
187 AND band_max_day_comp = p_band_min_day_comp
188 AND band_max_week_comp = p_band_min_week_comp
189 AND band_max_month_comp = p_band_min_month_comp
190 AND band_max_year_comp = p_band_min_year_comp;
191
192 BEGIN
193
194 /* The band starting at 0 cannot be deleted as it is the first one */
195 IF (NVL(p_band_min_day_comp, 0) +
196 NVL(p_band_min_week_comp, 0) +
197 NVL(p_band_min_month_comp,0) +
198 NVL(p_band_min_year_comp, 0) > 0) THEN
199
200 /* Get the information about the band to be deleted */
201 OPEN delete_cur;
202 FETCH delete_cur INTO l_band_to_delete;
203 CLOSE delete_cur;
204
205 /* Get the information about the band immediately preceeding it */
206 OPEN grow_cur;
207 FETCH grow_cur INTO l_band_to_grow;
208 CLOSE grow_cur;
209
210 /* Check the bands are consecutive */
211 IF (l_band_to_delete.band_min_value = l_band_to_grow.band_max_value) THEN
212
213 /* Delete the given band */
214 DELETE FROM hri_time_bands
215 WHERE band_min_value = l_band_to_delete.band_min_value
216 AND type = p_type;
217
218 /* Decrement the sequence number of higher bands */
219 UPDATE hri_time_bands
220 SET band_sequence = band_sequence - 1
221 WHERE band_sequence > l_band_to_delete.band_sequence
222 AND type = p_type;
223
224 /* Set the maximum value of the band preceeding the deleted band */
225 /* to the maximum value of the deleted band */
226 UPDATE hri_time_bands
227 SET band_max_value = l_band_to_delete.band_max_value,
228 band_max_year_comp = l_band_to_delete.band_max_year_comp,
229 band_max_month_comp = l_band_to_delete.band_max_month_comp,
230 band_max_week_comp = l_band_to_delete.band_max_week_comp,
231 band_max_day_comp = l_band_to_delete.band_max_day_comp
232 WHERE band_max_value = l_band_to_grow.band_max_value
233 AND type = p_type;
234
235 END IF;
236
237 END IF;
238
239 EXCEPTION WHEN OTHERS THEN
240
241 RETURN;
242
243 END remove_time_band;
244
245
246 /******************************************************************************/
247 /* Overloaded version of remove time band to remove all bands for a type */
248 /******************************************************************************/
249 PROCEDURE remove_time_band(p_type IN VARCHAR2) IS
250
251 CURSOR remove_all_rows IS
252 SELECT
253 band_min_day_comp
254 ,band_min_week_comp
255 ,band_min_month_comp
256 ,band_min_year_comp
257 FROM hri_time_bands
258 WHERE type = p_type
259 AND band_min_value > 0;
260
261 BEGIN
262
263 FOR row_to_remove IN remove_all_rows LOOP
264
265 remove_time_band
266 (p_type => p_type,
267 p_band_min_day_comp => row_to_remove.band_min_day_comp,
268 p_band_min_week_comp => row_to_remove.band_min_week_comp,
269 p_band_min_month_comp => row_to_remove.band_min_month_comp,
270 p_band_min_year_comp => row_to_remove.band_min_year_comp);
271
272 END LOOP;
273
274 END remove_time_band;
275
276 /******************************************************************************/
277 /* Inserts a row into the table. If the row already exists then the row is */
278 /* updated. Called from UPLOAD part of FNDLOAD. */
279 /******************************************************************************/
280 PROCEDURE load_time_band_row(p_type IN VARCHAR2,
281 p_band_min IN NUMBER,
282 p_band_max IN NUMBER,
283 p_band_sequence IN NUMBER,
284 p_band_min_day_comp IN NUMBER,
285 p_band_min_week_comp IN NUMBER,
286 p_band_min_month_comp IN NUMBER,
287 p_band_min_year_comp IN NUMBER,
288 p_band_max_day_comp IN NUMBER,
289 p_band_max_week_comp IN NUMBER,
290 p_band_max_month_comp IN NUMBER,
291 p_band_max_year_comp IN NUMBER,
292 p_owner IN VARCHAR2)
293 IS
294
295 l_rows_customized NUMBER; -- How many rows have been customized
296
297 /* Standard WHO columns */
298 l_last_update_date DATE := SYSDATE;
299 l_last_updated_by NUMBER := 0;
300 l_last_update_login NUMBER := 0;
301 l_created_by NUMBER := 0;
302 l_creation_date DATE := SYSDATE;
303
304 /* Selects the number of bands for the given type that have been customized */
305 CURSOR customized_bands_csr IS
306 SELECT count(*)
307 FROM hri_time_bands
308 WHERE type = p_type
309 AND fnd_load_util.owner_name(last_updated_by) <> 'ORACLE';
310
311 BEGIN
312
313 l_created_by := fnd_load_util.owner_id(p_name => p_owner);
314 l_last_updated_by := fnd_load_util.owner_id(p_name => p_owner);
315
316 /* Find whether the given type has been customized */
317 OPEN customized_bands_csr;
318 FETCH customized_bands_csr INTO l_rows_customized;
319 CLOSE customized_bands_csr;
320
321 /* If the banding type has not been customized, process the seeded type */
322 IF (l_rows_customized = 0) THEN
323
324 /* Delete all overlapping bands */
325 DELETE FROM hri_time_bands
326 WHERE type = p_type
327 AND ((p_band_min <= band_min_value AND
328 (band_min_value < p_band_max OR p_band_max IS NULL))
329 OR (band_min_value <= p_band_min AND
330 (p_band_min < band_max_value OR band_max_value IS NULL))
331 OR band_sequence = p_band_sequence);
332
333 /* Insert seeded band */
334 INSERT INTO hri_time_bands
335 ( type
336 , band_min_value
337 , band_max_value
338 , band_sequence
339 , band_min_day_comp
340 , band_min_week_comp
341 , band_min_month_comp
342 , band_min_year_comp
343 , band_max_day_comp
344 , band_max_week_comp
345 , band_max_month_comp
346 , band_max_year_comp
347 , last_update_date
348 , last_update_login
349 , last_updated_by
350 , created_by
351 , creation_date )
352 VALUES
353 ( p_type
354 , p_band_min
355 , p_band_max
356 , p_band_sequence
357 , p_band_min_day_comp
358 , p_band_min_week_comp
359 , p_band_min_month_comp
360 , p_band_min_year_comp
361 , p_band_max_day_comp
362 , p_band_max_week_comp
363 , p_band_max_month_comp
364 , p_band_max_year_comp
365 , l_last_update_date
366 , l_last_update_login
367 , l_last_updated_by
368 , l_created_by
369 , l_creation_date );
370
371 END IF;
372
373 EXCEPTION
374 WHEN OTHERS THEN
375
376 CLOSE customized_bands_csr;
377 RAISE;
378
379 END load_time_band_row;
380
381 END hri_apl_time;
[Home] [Help]