[Home] [Help]
65: l_days_to_month NUMBER; -- Holds constant
66:
67: CURSOR ratio_cur IS
68: SELECT days_to_month
69: FROM hri_service_bands
70: WHERE days_to_month IS NOT NULL;
71:
72: BEGIN
73:
86: IS
87:
88: BEGIN
89:
90: UPDATE hri_service_bands
91: SET days_to_month = p_days_to_month
92: WHERE days_to_month IS NOT NULL;
93:
94: END set_days_to_months;
93:
94: END set_days_to_months;
95:
96: /******************************************************************************/
97: /* This procedure inserts a service band into the hri_service_bands table. */
98: /* The PK is the minimum year, month, week and day for the service band. */
99: /* There will always be a row with all of these zero since this cannot be */
100: /* removed by the remove_service_band API) and there will always be (possibly */
101: /* the same row) a row with null maximum year, month, week and day values */
145: , band_max_total_years
146: , band_max_total_months
147: , band_max_total_weeks
148: , band_max_total_days
149: FROM hri_service_bands
150: WHERE (normalize_band( band_max_total_years
151: , band_max_total_months
152: , band_max_total_weeks
153: , band_max_total_days
184: /* Service Band already exists */
185: CLOSE split_cur;
186: ELSE
187: /* Create new service band using maximum of band to split */
188: INSERT INTO hri_service_bands
189: (band_min_total_years,
190: band_min_total_months,
191: band_min_total_weeks,
192: band_min_total_days,
204: l_band_to_split_max_weeks,
205: l_band_to_split_max_days );
206:
207: /* Update the maximum of band to split with the input */
208: UPDATE hri_service_bands
209: SET band_max_total_years = p_service_min_years,
210: band_max_total_months = p_service_min_months,
211: band_max_total_weeks = p_service_min_weeks,
212: band_max_total_days = p_service_min_days
243: band_max_total_years
244: ,band_max_total_months
245: ,band_max_total_weeks
246: ,band_max_total_days
247: FROM hri_service_bands
248: WHERE band_min_total_years = p_service_min_years
249: AND band_min_total_months = p_service_min_months
250: AND band_min_total_weeks = p_service_min_weeks
251: AND band_min_total_days = p_service_min_days;
262: /* Age Band doesn't exist */
263: CLOSE grow_cur;
264: ELSE
265: /* Remove the band */
266: DELETE FROM hri_service_bands
267: WHERE band_min_total_years = p_service_min_years
268: AND band_min_total_months = p_service_min_months
269: AND band_min_total_weeks = p_service_min_weeks
270: AND band_min_total_days = p_service_min_days;
270: AND band_min_total_days = p_service_min_days;
271:
272: /* Update the previous band, which can be identified by its maximum */
273: /* being the minimum of the band removed */
274: UPDATE hri_service_bands
275: SET band_max_total_years = l_band_to_grow_max_years,
276: band_max_total_months = l_band_to_grow_max_months,
277: band_max_total_weeks = l_band_to_grow_max_weeks,
278: band_max_total_days = l_band_to_grow_max_days
310: l_creation_date DATE;
311:
312: CURSOR row_exists_cur IS
313: SELECT 1
314: FROM hri_service_bands
315: WHERE (band_min_total_years = p_band_min_yrs
316: AND band_min_total_months = p_band_min_mths
317: AND band_min_total_weeks = p_band_min_wks
318: AND band_min_total_days = p_band_min_days)
332: OPEN row_exists_cur;
333: FETCH row_exists_cur INTO l_row_exists;
334: IF (row_exists_cur%NOTFOUND OR row_exists_cur%NOTFOUND IS NULL) THEN
335: CLOSE row_exists_cur;
336: INSERT INTO hri_service_bands
337: ( band_min_total_years
338: , band_min_total_months
339: , band_min_total_weeks
340: , band_min_total_days
364: , l_created_by
365: , l_creation_date );
366: ELSE
367: CLOSE row_exists_cur;
368: UPDATE hri_service_bands
369: SET
370: band_max_total_years = p_band_max_yrs
371: ,band_max_total_months = p_band_max_mths
372: ,band_max_total_weeks = p_band_max_wks