[Home] [Help]
PACKAGE BODY: APPS.HR_BEN_BENEFIT_CONTRIBUTIONS
Source
1 PACKAGE BODY hr_ben_benefit_contributions AS
2 /* $Header: pebenpbc.pkb 115.1 99/07/17 18:46:40 porting ship $ */
3 --
4 --
5 -- Procedure/Function Definitions
6 --
7 -- ****************************************************
8 -- * hr_ben_chk_duplicate_cont *
9 -- ****************************************************
10 --
11 -- Checks that no duplicate contributions exists
12 --
13 PROCEDURE hr_ben_chk_duplicate_cont ( p_benefit_contribution_id NUMBER,
14 p_element_type_id NUMBER,
15 p_coverage_type VARCHAR2,
16 p_effective_start_date DATE,
17 p_effective_end_date DATE,
18 p_business_group_id NUMBER ) IS
19 -- declare local variables
20 --
21 l_contribution_exists VARCHAR2(1) := 'N';
22 --
23 -- declare cursor for check
24 --
25 CURSOR chk_duplicate_cont IS
26 SELECT 'Y'
27 FROM ben_benefit_contributions_f bc
28 WHERE ( bc.benefit_contribution_id <> p_benefit_contribution_id
29 OR p_benefit_contribution_id IS NULL )
30 AND bc.element_type_id = p_element_type_id
31 AND bc.business_group_id + 0 = p_business_group_id
32 AND bc.coverage_type = p_coverage_type
33 AND ( p_effective_start_date BETWEEN
34 bc.effective_start_date AND bc.effective_end_date
35 OR
36 p_effective_end_date BETWEEN
37 bc.effective_start_date AND bc.effective_end_date
38 );
39 --
40 BEGIN
41 --
42 -- execute cursor
43 --
44 OPEN chk_duplicate_cont;
45 FETCH chk_duplicate_cont INTO l_contribution_exists;
46 CLOSE chk_duplicate_cont;
47 --
48 -- chk to see if duplicate contributions exist
49 --
50 IF (l_contribution_exists = 'Y')
51 THEN
52 -- set message and raise exception
53 --
54 hr_utility.set_message(801, 'HR_13107_BEN_DUPLICATE_CONT');
55 hr_utility.raise_error;
56 --
57 END IF;
58 --
59 END hr_ben_chk_duplicate_cont;
60 --
61 -- ******************************************
62 -- * hr_ben_benefit_contribution_id *
63 -- ******************************************
64 --
65 -- gets surrogate key value from sequence
66 --
67 PROCEDURE hr_ben_benefit_contribution_id ( p_benefit_contribution_id IN OUT NUMBER) IS
68 --
69 -- declare cursor
70 --
71 CURSOR get_bc_id IS
72 SELECT ben_benefit_contributions_s.nextval
73 FROM sys.dual;
74 --
75 BEGIN
76 --
77 -- check to see if id already retrieved from previous
78 -- call
79 IF (p_benefit_contribution_id IS NULL)
80 THEN
81 -- execute cursor
82 --
83 OPEN get_bc_id;
84 FETCH get_bc_id INTO p_benefit_contribution_id;
85 CLOSE get_bc_id;
86 --
87 END IF;
88 --
89 END hr_ben_benefit_contribution_id;
90 --
91 -- ****************************************
92 -- * hr_ben_chk_future_conts *
93 -- ****************************************
94 --
95 -- checks that there are future contributions for the current
96 -- contribution being deleted
97 --
98 PROCEDURE hr_ben_chk_future_conts ( p_business_group_id NUMBER,
99 p_benefit_contribution_id NUMBER,
100 p_effective_end_date DATE ) IS
101 -- declare local variables
102 --
103 l_contributions_exist varchar2(1) := 'N';
104 --
105 -- declare cursor for contribution check
106 --
107 CURSOR chk_future_contribution IS
108 SELECT 'Y'
109 FROM ben_benefit_contributions_f bc
110 WHERE bc.benefit_contribution_id = p_benefit_contribution_id
111 AND bc.business_group_id + 0 = p_business_group_id
112 AND bc.effective_end_date > p_effective_end_date;
113 --
114 BEGIN
115 --
116 hr_utility.set_location('hr_ben_chk_future_conts', 0);
117 --
118 --
119 -- execute cursor
120 --
121 OPEN chk_future_contribution;
122 --
123 hr_utility.set_location('hr_ben_chk_future_conts', 1);
124 --
125 FETCH chk_future_contribution INTO l_contributions_exist;
126 --
127 hr_utility.set_location('hr_ben_chk_future_conts', 2);
128 --
129 CLOSE chk_future_contribution;
130 --
131 -- chk to see if future contributions exist
132 --
133 IF(l_contributions_exist = 'N')
134 THEN
135 --
136 hr_utility.set_location('hr_ben_chk_future_conts', 3);
137 --
138 -- abort the delete - raise error
139 --
140 hr_utility.set_message(801, 'HR_13108_BEN_NO_FUTURE_CHNGE');
141 hr_utility.raise_error;
142 --
143 END IF;
144 --
145 hr_utility.set_location('hr_ben_chk_future_conts', 5);
146 --
147 --
148 END hr_ben_chk_future_conts;
149 --
150 --
151 --
152 -- Name hr_ben_ref_chk
153 --
154 -- Purpose
155 --
156 -- referential integrity change
157 --
158 -- Arguments
159 --
160 -- p_element_type_id NUMBER
161 -- p_iv_er_id NUMBER
162 -- p_session_date DATE
163 -- p_coverage_type VARCHAR2
164 -- p_dt_delete_mode VARCHAR2
165 -- p_validation_start_date DATE
166 -- p_validation_end_date DATE
167 -- p_element_effective_start_date DATE )
168 --
169 PROCEDURE hr_ben_ref_chk ( p_element_type_id NUMBER,
170 p_iv_er_id NUMBER,
171 p_session_date DATE,
172 p_coverage_type VARCHAR2,
173 p_dt_delete_mode VARCHAR2,
174 p_validation_start_date DATE,
175 p_validation_end_date DATE,
176 p_element_effective_start_date DATE ) IS
177 --
178 -- declare local variables
179 --
180 l_element_exists VARCHAR2(1) := 'N';
181 l_iv_cov_id NUMBER(9);
182 l_element_entries_exist VARCHAR2(1) := 'N';
183 l_element_links_exist VARCHAR2(1) := 'N';
184 --
185 -- declare cursors
186 --
187 -- check to see if future changes exist to benefit element
188 --
189 CURSOR get_element IS
190 SELECT 'Y'
191 FROM pay_element_types_f et
192 WHERE et.element_type_id = p_element_type_id
193 AND et.effective_end_date = to_date('31-12-4712','DD-MM-YYYY')
194 AND p_session_date BETWEEN
195 et.effective_start_date and et.effective_end_date;
196 --
197 -- retrieve the input value id for the COVERAGE input for the element
198 --
199 CURSOR get_coverage_input_value_id IS
200 SELECT
201 iv_cov.input_value_id iv_cov
202 FROM
203 pay_input_values_f iv_cov,
204 pay_element_types_f et
205 WHERE
206 et.element_type_id = p_element_type_id
207 AND
208 iv_cov.element_type_id = et.element_type_id AND
209 UPPER(iv_cov.name) = 'COVERAGE';
210 --
211 -- check to see if ANY element entries exist for the contribution record
212 -- being deleted
213 --
214 CURSOR get_element_entries IS
215 SELECT
216 'Y'
217 FROM
218 dual
219 WHERE EXISTS (
220 SELECT
221 'x'
222 FROM
223 pay_element_entry_values_f eev_cov,
224 pay_element_entry_values_f eev,
225 pay_element_entries_f ee,
226 pay_element_links_f el
227 WHERE
228 el.element_type_id = p_element_type_id AND
229 p_validation_start_date
230 BETWEEN el.effective_start_date AND
231 el.effective_end_date
232 AND
233 ee.element_link_id = el.element_link_id AND
234 (ee.effective_start_date
235 BETWEEN p_validation_start_date AND
236 p_validation_end_date OR
237 ee.effective_end_date
238 BETWEEN p_validation_start_date AND
239 p_validation_end_date
240 )
241
242 -- p_validation_start_date
243 -- BETWEEN ee.effective_start_date AND
244 -- ee.effective_end_date
245 AND
246 eev_cov.element_entry_id = ee.element_entry_id AND
247 eev_cov.input_value_id = l_iv_cov_id AND
248 eev_cov.screen_entry_value = p_coverage_type AND
249 (eev_cov.effective_start_date
250 BETWEEN p_validation_start_date AND
251 p_validation_end_date OR
252 eev_cov.effective_end_date
253 BETWEEN p_validation_start_date AND
254 p_validation_end_date
255 )
256
257 -- p_validation_start_date
258 -- BETWEEN eev_cov.effective_start_date AND
259 -- eev_cov.effective_end_date
260 AND
261 eev.element_entry_id = ee.element_entry_id AND
262 eev.input_value_id = p_iv_er_id AND
263 (eev.effective_start_date
264 BETWEEN p_validation_start_date AND
265 p_validation_end_date OR
266 eev.effective_end_date
267 BETWEEN p_validation_start_date AND
268 p_validation_end_date
269 ) AND
270
271 -- p_validation_start_date
272 -- BETWEEN eev.effective_start_date AND
273 -- eev.effective_end_date AND
274
275 eev.screen_entry_value IS NULL
276 );
277 --
278 -- check if ANY element entries exist for ZAP
279 --
280 CURSOR get_any_element_entries IS
281 SELECT
282 'Y'
283 FROM
284 dual
285 WHERE EXISTS (
286 SELECT
287 'x'
288 FROM
289 pay_element_entry_values_f eev_cov,
290 pay_element_entry_values_f eev,
291 pay_element_entries_f ee,
292 pay_element_links_f el
293 WHERE
294 el.element_type_id = p_element_type_id
295 AND
296 ee.element_link_id = el.element_link_id
297 AND
298 eev_cov.element_entry_id = ee.element_entry_id AND
299 eev_cov.input_value_id = l_iv_cov_id AND
300 eev_cov.screen_entry_value = p_coverage_type
301 AND
302 eev.element_entry_id = ee.element_entry_id AND
303 eev.input_value_id = p_iv_er_id AND
304 eev.screen_entry_value IS NULL
305 );
306 --
307 BEGIN
308 --
309 hr_utility.set_location('hr_ben_ref_chk', 0);
310 --
311 --
312 -- check no elements exist
313 --
314 OPEN get_element;
315 FETCH get_element INTO l_element_exists;
316 CLOSE get_element;
317 --
318 hr_utility.set_location('hr_ben_ref_chk', 1);
319 --
320 --
321 -- chk flag
322 --
323 IF (l_element_exists = 'N')
324 THEN
325 --
326 hr_utility.set_location('hr_ben_ref_chk', 2);
327 --
328 --
329 -- error
330 --
331 hr_utility.set_message(801, 'HR_13109_BEN_CHANGE_EXISTS');
332 hr_utility.raise_error;
333 --
334 END IF;
335 --
336 -- get iv_cov_id
337 --
338 --
339 hr_utility.set_location('hr_ben_ref_chk', 3);
340 --
341 OPEN get_coverage_input_value_id;
342 FETCH get_coverage_input_value_id INTO l_iv_cov_id;
343 CLOSE get_coverage_input_value_id;
344 --
345 hr_utility.set_location('hr_ben_ref_chk', 4);
346 --
347 --
348 -- Examine DT delete mode to determine whether checking for ANY ee's
349 --
350 IF (p_dt_delete_mode = 'ZAP')
351 THEN
352 --
353 -- check if referenced by ANY element entries
354 --
355 --
356 hr_utility.set_location('hr_ben_ref_chk', 5);
357 --
358 OPEN get_any_element_entries;
359 FETCH get_any_element_entries INTO l_element_entries_exist;
360 CLOSE get_any_element_entries;
361 --
362 ELSE
363 --
364 -- check if referenced by specific element entries
365 --
366 --
367 hr_utility.set_location('hr_ben_ref_chk', 6);
368 --
369 OPEN get_element_entries;
370 FETCH get_element_entries INTO l_element_entries_exist;
371 CLOSE get_element_entries;
372 --
373 END IF;
374 --
375 --
376 hr_utility.set_location('hr_ben_ref_chk', 7);
377 --
378 IF( l_element_entries_exist = 'Y' )
379 THEN
380 --
381 hr_utility.set_location('hr_ben_ref_chk', 8);
382 --
383 hr_utility.set_message(801, 'HR_7326_BEN_ELE_ENTRIES_EXIST');
384 hr_utility.raise_error;
385 END IF;
386 --
387 hr_utility.set_location('hr_ben_ref_chk', 9);
388 --
389 --
390 END hr_ben_ref_chk;
391 --
392 --
393 --
394 END hr_ben_benefit_contributions;