[Home] [Help]
9: l_days_to_month NUMBER; -- Holds constant
10:
11: CURSOR ratio_cur IS
12: SELECT days_to_month
13: FROM hri_service_bands
14: WHERE days_to_month IS NOT NULL;
15:
16: BEGIN
17:
30: IS
31:
32: BEGIN
33:
34: UPDATE hri_service_bands
35: SET days_to_month = p_days_to_month
36: WHERE days_to_month IS NOT NULL;
37:
38: END set_days_to_months;
59:
60: END normalize_band;
61:
62: /******************************************************************************/
63: /* This procedure inserts a service band into the hri_service_bands table. */
64: /* The PK is the minimum year, month, week and day for the service band. */
65: /* There will always be a row with all of these zero since this cannot be */
66: /* removed by the remove_service_band API) and there will always be (possibly */
67: /* the same row) a row with null maximum year, month, week and day values */
111: ,band_max_total_years
112: ,band_max_total_months
113: ,band_max_total_weeks
114: ,band_max_total_days
115: FROM hri_service_bands
116: WHERE (normalize_band( band_max_total_years
117: , band_max_total_months
118: , band_max_total_weeks
119: , band_max_total_days
150: /* Service Band already exists */
151: CLOSE split_cur;
152: ELSE
153: /* Create new service band using maximum of band to split */
154: INSERT INTO hri_service_bands
155: (band_min_total_years,
156: band_min_total_months,
157: band_min_total_weeks,
158: band_min_total_days,
170: l_band_to_split_max_weeks,
171: l_band_to_split_max_days );
172:
173: /* Update the maximum of band to split with the input */
174: UPDATE hri_service_bands
175: SET band_max_total_years = p_service_min_years,
176: band_max_total_months = p_service_min_months,
177: band_max_total_weeks = p_service_min_weeks,
178: band_max_total_days = p_service_min_days
208: band_max_total_years
209: ,band_max_total_months
210: ,band_max_total_weeks
211: ,band_max_total_days
212: FROM hri_service_bands
213: WHERE band_min_total_years = p_service_min_years
214: AND band_min_total_months = p_service_min_months
215: AND band_min_total_weeks = p_service_min_weeks
216: AND band_min_total_days = p_service_min_days;
227: /* Age Band doesn't exist */
228: CLOSE grow_cur;
229: ELSE
230: /* Remove the band */
231: DELETE FROM hri_service_bands
232: WHERE band_min_total_years = p_service_min_years
233: AND band_min_total_months = p_service_min_months
234: AND band_min_total_weeks = p_service_min_weeks
235: AND band_min_total_days = p_service_min_days;
235: AND band_min_total_days = p_service_min_days;
236:
237: /* Update the previous band, which can be identified by its maximum */
238: /* being the minimum of the band removed */
239: UPDATE hri_service_bands
240: SET band_max_total_years = l_band_to_grow_max_years,
241: band_max_total_months = l_band_to_grow_max_months,
242: band_max_total_weeks = l_band_to_grow_max_weeks,
243: band_max_total_days = l_band_to_grow_max_days
275: l_creation_date DATE := SYSDATE;
276:
277: CURSOR row_exists_cur IS
278: SELECT 1
279: FROM hri_service_bands
280: WHERE (band_min_total_years = p_band_min_yrs
281: AND band_min_total_months = p_band_min_mths
282: AND band_min_total_weeks = p_band_min_wks
283: AND band_min_total_days = p_band_min_days)
291: OPEN row_exists_cur;
292: FETCH row_exists_cur INTO l_row_exists;
293: IF (row_exists_cur%NOTFOUND OR row_exists_cur%NOTFOUND IS NULL) THEN
294: CLOSE row_exists_cur;
295: INSERT INTO hri_service_bands
296: ( band_min_total_years
297: , band_min_total_months
298: , band_min_total_weeks
299: , band_min_total_days
323: , l_created_by
324: , l_creation_date );
325: ELSE
326: CLOSE row_exists_cur;
327: UPDATE hri_service_bands
328: SET
329: band_max_total_years = p_band_max_yrs
330: ,band_max_total_months = p_band_max_mths
331: ,band_max_total_weeks = p_band_max_wks