1 PACKAGE BODY hri_bpl_age AS
2 /* $Header: hribage.pkb 115.6 2002/05/10 07:54:59 pkm ship $ */
3
4 /* Set up global type - effectively creates an array */
5 TYPE g_num_array_t IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
6 g_ages g_num_array_t; -- Holds sequence of band min ages
7
8 /* Values for global reference */
9 g_age_band_count NUMBER := 0; -- Keeps a count of the number of age bands
10 g_band_number NUMBER; -- Primary key for temporary band tables
11 g_age_ff_update DATE; -- Last update date of age_band Fast Formula
12
13 /******************************************************************************/
14 /* PRIVATE Procdures and Functions */
15 /******************************************************************************/
16
17
18 /******************************************************************************/
19 /* PUBLIC Procdures and Functions */
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 */
29 /* minimum age falls into, and splitting it out on the new minimum age. */
30 /* */
31 /* If a minimum age is given that already exists, then nothing will happen. */
32 /* */
33 /* E.g. if the following bands exist: */
34 /* 0 - 12 */
35 /* 12 - 24 */
36 /* 24 - 36 */
37 /* 36 - <null> */
38 /* */
39 /* Then insert_age_band(0,12) would do nothing since 12 does not strictly */
40 /* fall into any of the above bands. */
41 /* */
42 /* However, insert_age_band(0,18) [NB - equivalent to insert_age_band(1,6) ] */
43 /* would give the new set of bands as: */
44 /* 0 - 12 */
45 /* 12 - 18 [UPDATEd band] */
46 /* 18 - 24 [INSERTed band] */
47 /* 24 - 36 */
48 /* 36 - <null> */
49 /* */
50 /* The band_min_total_months is the primary key for the table, and each age */
51 /* band is defined as the ages (X) satisfying: */
52 /* band_min_total_months <= X < band_max_total_months */
53 /* */
54 /******************************************************************************/
55 PROCEDURE insert_age_band( p_age_min_years NUMBER,
56 p_age_min_months NUMBER)
57 IS
58
59 l_total_min_age_months NUMBER; --Holds converted age min in months
60
61 l_age_band_to_split_min NUMBER; -- Age minimum of band to split
62 l_age_band_to_split_max NUMBER; -- Age maxumum of band to split
63
64 /* Selects the age band that the new age minimum falls into */
65 /* Since this is strict it will return no rows if an age minimum is passed in */
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
75 BEGIN
76
77 /* Converts parameters to months */
78 l_total_min_age_months := p_age_min_months + (12 * p_age_min_years);
79
80 OPEN split_cur(l_total_min_age_months);
81 FETCH split_cur INTO l_age_band_to_split_min, l_age_band_to_split_max;
82 IF (split_cur%NOTFOUND OR split_cur%NOTFOUND IS NULL) THEN
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);
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 */
108 /* age falls into, and splitting it out on the new minimum age. */
109 /* */
110 /* If a minimum age is given that does not exists, then nothing will happen. */
111 /* */
112 /* E.g. if the following bands exist: */
113 /* 0 - 12 */
114 /* 12 - 24 */
115 /* 24 - 36 */
116 /* 36 - <null> */
117 /* */
118 /* Then remove_age_band(0,18) would do nothing since 18 does not match the */
119 /* minimum age of any of the above bands. */
120 /* */
121 /* However, remove_age_band(0,12) would give the new set of bands as: */
122 /* 0 - 24 [UPDATEd band with maximum age of DELETEd band] */
123 /* 24 - 36 */
124 /* 36 - <null> */
125 /* If the top band is removed, the previous band maximum age will be updated */
126 /* with the null value. */
127 /******************************************************************************/
128 PROCEDURE remove_age_band( p_age_min_years NUMBER,
129 p_age_min_months NUMBER)
130 IS
131
132 l_total_min_age_months NUMBER; -- Minimum age in months of band to remove
133
134 l_total_max_age_months NUMBER; -- Maximum age in months of band to remove
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
144 l_total_min_age_months := p_age_min_months + (12 * p_age_min_years);
145
146 IF (l_total_min_age_months > 0) THEN
147 OPEN grow_cur(l_total_min_age_months);
148 FETCH grow_cur INTO l_total_max_age_months;
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
158 WHERE band_max_total_months = l_total_min_age_months;
159 END IF;
160 END IF;
161
162 END remove_age_band;
163
164 /*******************************************************************************/
165 /* Inserts a row into the table. If the row already exists then the row is */
166 /* updated. Called from UPLOAD part of FNDLOAD. */
167 /******************************************************************************/
168 PROCEDURE load_row( p_band_min IN NUMBER,
169 p_band_max IN NUMBER,
170 p_owner IN VARCHAR2 )
171 IS
172
173 l_row_exists NUMBER; -- Whether a row already exists in table
174
175 /* Standard WHO columns */
176 l_last_update_date DATE := SYSDATE;
177 l_last_updated_by NUMBER := 0;
178 l_last_update_login NUMBER := 0;
179 l_created_by NUMBER := 0;
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
189 IF (p_owner = 'SEED') THEN
190 l_created_by := 1;
191 l_last_updated_by := 1;
192 END IF;
193
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
203 , last_updated_by
204 , created_by
205 , creation_date )
206 VALUES
207 ( p_band_min
208 , p_band_max
209 , l_last_update_date
210 , l_last_update_login
211 , l_last_updated_by
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
221 ,last_updated_by = l_last_updated_by
222 WHERE band_min_total_months = p_band_min;
223 END IF;
224
225 END load_row;
226
227 END hri_bpl_age;
[Home] [Help]