1 PACKAGE BODY hri_edw_dim_service AS
2 /* $Header: hriedlwb.pkb 120.0 2005/05/29 07:08:39 appldev noship $ */
3
4 /******************************************************************************/
5 /* Retrieves constant converting days to months */
6 /******************************************************************************/
7 FUNCTION get_days_to_month RETURN NUMBER IS
8
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
18 OPEN ratio_cur;
19 FETCH ratio_cur INTO l_days_to_month;
20 CLOSE ratio_cur;
21
22 RETURN l_days_to_month;
23
24 END get_days_to_month;
25
26 /******************************************************************************/
27 /* Retrieves constant converting days to months */
28 /******************************************************************************/
29 PROCEDURE set_days_to_months( p_days_to_month NUMBER)
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;
39
40 /******************************************************************************/
41 /* Takes values in years, months, weeks and days, and the constant for how */
42 /* many days in a months, and returns the equivalent value in months */
43 /******************************************************************************/
44 FUNCTION normalize_band( p_band_years NUMBER,
45 p_band_months NUMBER,
46 p_band_weeks NUMBER,
47 p_band_days NUMBER,
48 p_days_to_month NUMBER)
49 RETURN NUMBER IS
50
51 l_return_value NUMBER; -- Holds value to return
52
53 BEGIN
54
55 l_return_value := (((p_band_weeks * 7) + p_band_days) / p_days_to_month)
56 + ((p_band_years * 12) + p_band_months);
57
58 RETURN l_return_value;
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 */
68 /* since inserting a row always works by picking the band that the new */
69 /* service length falls into, and splitting it into two */
70 /* */
71 /* If a service length is given that already exists, nothing will happen. */
72 /* */
73 /* E.g. if the following bands exist (Years, Months, Weeks, Days): */
74 /* (0,0,0,0) - (0,3,0,0) */
75 /* (0,3,0,0) - (0,6,0,0) */
76 /* (0,6,0,0) - (0,9,0,0) */
77 /* (0,9,0,0) - (,,,) */
78 /* */
79 /* Then insert_service_band(1,0,0,0) would give the new set of bands as: */
80 /* (0,0,0,0) - (0,3,0,0) */
81 /* (0,3,0,0) - (0,6,0,0) */
82 /* (0,6,0,0) - (0,9,0,0) */
83 /* (0,9,0,0) - (1,0,0,0) */
84 /* (1,0,0,0) - (,,,) */
85 /******************************************************************************/
86 PROCEDURE insert_service_band( p_service_min_years NUMBER,
87 p_service_min_months NUMBER,
88 p_service_min_weeks NUMBER,
89 p_service_min_days NUMBER)
90 IS
91
92 l_total_min_service_months NUMBER; -- Holds the service length in months
93
94 l_band_to_split_min NUMBER; -- Service (in months) of band to split
95 l_band_to_split_max_years NUMBER; -- Max years of band to split
96 l_band_to_split_max_months NUMBER; -- Max months of band to split
97 l_band_to_split_max_weeks NUMBER; -- Max weeks of band to split
98 l_band_to_split_max_days NUMBER; -- Max days of band to split
99
100 l_days_to_month NUMBER; -- Constant converting days to months
101
102 /* Get details of band to split */
103 CURSOR split_cur
104 (v_total_min_service_months NUMBER,
105 v_days_to_month NUMBER) IS
106 SELECT normalize_band( band_min_total_years
107 , band_min_total_months
108 , band_min_total_weeks
109 , band_min_total_days
110 , v_days_to_month) band_months
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
120 , v_days_to_month) > v_total_min_service_months
121 OR ( band_max_total_years IS NULL AND band_max_total_months IS NULL
122 AND band_max_total_weeks IS NULL AND band_max_total_days IS NULL))
123 AND normalize_band( band_min_total_years
124 , band_min_total_months
125 , band_min_total_weeks
126 , band_min_total_days
127 , v_days_to_month) < v_total_min_service_months;
128
129 BEGIN
130
131 /* Retrive constant */
132 l_days_to_month := get_days_to_month;
133
134 /* Convert input to months */
135 l_total_min_service_months := normalize_band( p_service_min_years
136 , p_service_min_months
137 , p_service_min_weeks
138 , p_service_min_days
139 , l_days_to_month );
140
141 /* Find which service band contains input service length */
142 OPEN split_cur(l_total_min_service_months, l_days_to_month);
143 FETCH split_cur INTO l_band_to_split_min,
144 l_band_to_split_max_years,
145 l_band_to_split_max_months,
146 l_band_to_split_max_weeks,
147 l_band_to_split_max_days;
148 IF (split_cur%NOTFOUND OR split_cur%NOTFOUND IS NULL) THEN
149
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,
159 band_max_total_years,
160 band_max_total_months,
161 band_max_total_weeks,
162 band_max_total_days)
163 VALUES
164 ( p_service_min_years,
165 p_service_min_months,
166 p_service_min_weeks,
167 p_service_min_days,
168 l_band_to_split_max_years,
169 l_band_to_split_max_months,
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
179 WHERE normalize_band( band_min_total_years,
180 band_min_total_months,
181 band_min_total_weeks,
182 band_min_total_days,
183 l_days_to_month ) = l_band_to_split_min;
184 END IF;
185
186 END insert_service_band;
187
188 /******************************************************************************/
189 /* Removes a service band, if it exists. The maximum of the band preceding */
190 /* the removed band is updated with the maximim of the removed band. */
191 /******************************************************************************/
192 PROCEDURE remove_service_band( p_service_min_years NUMBER,
193 p_service_min_months NUMBER,
194 p_service_min_weeks NUMBER,
195 p_service_min_days NUMBER)
196 IS
197
198 l_total_min_service_months NUMBER; -- Service length of band to remove
199
200 l_band_to_grow_max_years NUMBER; -- Max years of band to remove
201 l_band_to_grow_max_months NUMBER; -- Max months of band to remove
202 l_band_to_grow_max_weeks NUMBER; -- Max weeks of band to remove
203 l_band_to_grow_max_days NUMBER; -- Max days of band to remove
204
205 /* Get maximum service length of band to remove */
206 CURSOR grow_cur IS
207 SELECT
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;
217
218 BEGIN
219
220 /* Populate variables with maximum service length of band to remove */
221 OPEN grow_cur;
222 FETCH grow_cur INTO l_band_to_grow_max_years,
223 l_band_to_grow_max_months,
224 l_band_to_grow_max_weeks,
225 l_band_to_grow_max_days;
226 IF (grow_cur%NOTFOUND OR grow_cur%NOTFOUND IS NULL) THEN
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;
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
244 WHERE band_max_total_years = p_service_min_years
245 AND band_max_total_months = p_service_min_months
246 AND band_max_total_weeks = p_service_min_weeks
247 AND band_max_total_days = p_service_min_days;
248 END IF;
249
250 END remove_service_band;
251
252 /******************************************************************************/
253 /* Inserts row into table, or updates it if the row already exists. Called */
254 /* from the UPLOAD section of FNDLOAD. */
255 /******************************************************************************/
256 PROCEDURE load_row( p_band_min_yrs IN NUMBER,
257 p_band_min_mths IN NUMBER,
258 p_band_min_wks IN NUMBER,
259 p_band_min_days IN NUMBER,
260 p_band_max_yrs IN NUMBER,
261 p_band_max_mths IN NUMBER,
262 p_band_max_wks IN NUMBER,
263 p_band_max_days IN NUMBER,
264 p_days_to_month IN NUMBER,
265 p_owner IN VARCHAR2 )
266 IS
267
268 l_row_exists NUMBER;
269
270 /* Standard WHO columns */
271 l_last_update_date DATE := SYSDATE;
272 l_last_updated_by NUMBER := 0;
273 l_last_update_login NUMBER := 0;
274 l_created_by NUMBER := 0;
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)
284 OR (band_min_total_years IS NULL AND p_band_min_yrs IS NULL
285 AND band_min_total_months IS NULL AND p_band_min_mths IS NULL
286 AND band_min_total_weeks IS NULL AND p_band_min_wks IS NULL
287 AND band_min_total_days IS NULL AND p_band_min_days IS NULL);
288
289 BEGIN
290
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
300 , band_max_total_years
301 , band_max_total_months
302 , band_max_total_weeks
303 , band_max_total_days
304 , days_to_month
305 , last_update_date
306 , last_update_login
307 , last_updated_by
308 , created_by
309 , creation_date )
310 VALUES
311 ( p_band_min_yrs
312 , p_band_min_mths
313 , p_band_min_wks
314 , p_band_min_days
315 , p_band_max_yrs
316 , p_band_max_mths
317 , p_band_max_wks
318 , p_band_max_days
319 , p_days_to_month
320 , l_last_update_date
321 , l_last_update_login
322 , l_last_updated_by
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
332 ,band_max_total_days = p_band_max_days
333 ,days_to_month = p_days_to_month
334 ,last_update_date = l_last_update_date
335 ,last_update_login = l_last_update_login
336 ,last_updated_by = l_last_updated_by
337 WHERE (band_min_total_years = p_band_min_yrs
338 AND band_min_total_months = p_band_min_mths
339 AND band_min_total_weeks = p_band_min_wks
340 AND band_min_total_days = p_band_min_days)
341 OR (band_min_total_years IS NULL AND p_band_min_yrs IS NULL
342 AND band_min_total_months IS NULL AND p_band_min_mths IS NULL
343 AND band_min_total_weeks IS NULL AND p_band_min_wks IS NULL
344 AND band_min_total_days IS NULL AND p_band_min_days IS NULL);
345 END IF;
346
347 END load_row;
348
349 END hri_edw_dim_service;
[Home] [Help]