[Home] [Help]
20: /******************************************************************************/
21:
22:
23: /******************************************************************************/
24: /* This procedure inserts an age band into the hri_age_bands table. The PK is */
25: /* the minimum age for the age band. There will always be a row with minimum */
26: /* age zero (since this cannot be removed by the delete_age_band API) and */
27: /* there will always be (possibly the same row) a row with a null maximum age */
28: /* since inserting a row always works by picking the age band that the new */
66: /* which corresponds exactly to an age minimum on an existing age band */
67: CURSOR split_cur
68: (v_total_min_age_months NUMBER) IS
69: SELECT band_min_total_months, band_max_total_months
70: FROM hri_age_bands
71: WHERE v_total_min_age_months < NVL(band_max_total_months, l_total_min_age_months + 1)
72: AND v_total_min_age_months > NVL(band_min_total_months, l_total_min_age_months - 1)
73: ;
74:
83: /* Age Band already exists */
84: CLOSE split_cur;
85: ELSE
86: /* Create age band with the new age min and the age max of the band it fell into */
87: INSERT INTO hri_age_bands
88: (band_min_total_months
89: ,band_max_total_months)
90: VALUES
91: (l_total_min_age_months, l_age_band_to_split_max);
90: VALUES
91: (l_total_min_age_months, l_age_band_to_split_max);
92:
93: /* Update the age max of the above band to the new age min above */
94: UPDATE hri_age_bands
95: SET band_max_total_months = l_total_min_age_months
96: WHERE band_min_total_months = l_age_band_to_split_min;
97: END IF;
98:
99: END insert_age_band;
100:
101:
102: /******************************************************************************/
103: /* This procedure removes an age band from the hri_age_bands table. The PK is */
104: /* the minimum age for the age band. There will always be a row with minimum */
105: /* age zero (since this cannot be removed by the this procedure and there */
106: /* will always be (possibly the same row) a row with a null maximum age since */
107: /* inserting a row always works by picking the age band that the new minimum */
135:
136: CURSOR grow_cur
137: (v_remove_band_min NUMBER) IS
138: SELECT band_max_total_months
139: FROM hri_age_bands
140: WHERE band_min_total_months = v_remove_band_min;
141:
142: BEGIN
143:
149: IF (grow_cur%NOTFOUND OR grow_cur%NOTFOUND IS NULL) THEN
150: /* Age Band doesn't exist */
151: CLOSE grow_cur;
152: ELSE
153: DELETE FROM hri_age_bands
154: WHERE band_min_total_months = l_total_min_age_months;
155:
156: UPDATE hri_age_bands
157: SET band_max_total_months = l_total_max_age_months
152: ELSE
153: DELETE FROM hri_age_bands
154: WHERE band_min_total_months = l_total_min_age_months;
155:
156: UPDATE hri_age_bands
157: SET band_max_total_months = l_total_max_age_months
158: WHERE band_max_total_months = l_total_min_age_months;
159: END IF;
160: END IF;
180: l_creation_date DATE := SYSDATE;
181:
182: CURSOR row_exists_cur IS
183: SELECT 1
184: FROM hri_age_bands
185: WHERE band_min_total_months = p_band_min;
186:
187: BEGIN
188:
194: OPEN row_exists_cur;
195: FETCH row_exists_cur INTO l_row_exists;
196: IF (row_exists_cur%NOTFOUND OR row_exists_cur%NOTFOUND IS NULL) THEN
197: CLOSE row_exists_cur;
198: INSERT INTO hri_age_bands
199: ( band_min_total_months
200: , band_max_total_months
201: , last_update_date
202: , last_update_login
212: , l_created_by
213: , l_creation_date );
214: ELSE
215: CLOSE row_exists_cur;
216: UPDATE hri_age_bands
217: SET
218: band_max_total_months = p_band_max
219: ,last_update_date = l_last_update_date
220: ,last_update_login = l_last_update_login