[Home] [Help]
PACKAGE BODY: APPS.BEN_PBB_BUS
Source
1 Package Body ben_pbb_bus as
2 /* $Header: bepbbrhi.pkb 115.14 2004/02/03 10:38:48 pbodla ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ben_pbb_bus.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------< chk_per_bnfts_bal_id >------|
12 -- ----------------------------------------------------------------------------
13 --
14 -- Description
15 -- This procedure is used to check that the primary key for the table
16 -- is created properly. It should be null on insert and
17 -- should not be able to be updated.
18 --
19 -- Pre Conditions
20 -- None.
21 --
22 -- In Parameters
23 -- per_bnfts_bal_id PK of record being inserted or updated.
24 -- effective_date Effective Date of session
25 -- object_version_number Object version number of record being
26 -- inserted or updated.
27 --
28 -- Post Success
29 -- Processing continues
30 --
31 -- Post Failure
32 -- Errors handled by the procedure
33 --
34 -- Access Status
35 -- Internal table handler use only.
36 --
37 Procedure chk_per_bnfts_bal_id(p_per_bnfts_bal_id in number,
38 p_effective_date in date,
39 p_object_version_number in number) is
40 --
41 l_proc varchar2(72) := g_package||'chk_per_bnfts_bal_id';
42 l_api_updating boolean;
43 --
44 Begin
45 --
46 hr_utility.set_location('Entering:'||l_proc, 5);
47 --
48 l_api_updating := ben_pbb_shd.api_updating
49 (p_effective_date => p_effective_date,
50 p_per_bnfts_bal_id => p_per_bnfts_bal_id,
51 p_object_version_number => p_object_version_number);
52 --
53 if (l_api_updating
54 and nvl(p_per_bnfts_bal_id,hr_api.g_number)
55 <> ben_pbb_shd.g_old_rec.per_bnfts_bal_id) then
56 --
57 -- raise error as PK has changed
58 --
59 ben_pbb_shd.constraint_error('BEN_PER_BNFTS_BAL_PK');
60 --
61 elsif not l_api_updating then
62 --
63 -- check if PK is null
64 --
65 if p_per_bnfts_bal_id is not null then
66 --
67 -- raise error as PK is not null
68 --
69 ben_pbb_shd.constraint_error('BEN_PER_BNFTS_BAL_PK');
70 --
71 end if;
72 --
73 end if;
74 --
75 hr_utility.set_location('Leaving:'||l_proc, 10);
76 --
77 End chk_per_bnfts_bal_id;
78 --
79 -- ----------------------------------------------------------------------------
80 -- |------< chk_per_bnft_bal_unique >------|
81 -- ----------------------------------------------------------------------------
82 --
83 -- Description
84 -- This procedure is used to check that the combination of Person_id
85 -- and BNFTS_BAL_ID are unique within business group
86 --
87 -- Pre Conditions
88 -- None.
89 --
90 -- In Parameters
91 -- p_per_bnfts_bal_id PK of record being inserted or updated.
92 -- p_person_id person_id or record being inserted or updated
93 -- p_bnfts_bal_id bnfts_bal_id or record being inserted or updated
94 -- p_business_group_id business_group_id version number of record being
95 -- inserted or updated.
96 --
97 -- Post Success
98 -- Processing continues
99 --
100 -- Post Failure
101 -- Errors handled by the procedure
102 --
103 -- Access Status
104 -- Internal table handler use only.
105 --
106 Procedure chk_per_bnft_bal_unique(p_per_bnfts_bal_id in number,
107 p_person_id in number,
108 p_bnfts_bal_id in number,
109 p_business_group_id in number,
110 p_validation_start_date in date,
111 p_validation_end_date in date,
112 p_effective_date in date ) is
113 --
114 l_proc varchar2(72) := g_package||'chk_per_bnft_bal_unique';
115 v_dummy varchar2(1);
116 cursor c1 is select null
117 from ben_per_bnfts_bal_f
118 where per_bnfts_bal_id <> nvl(p_per_bnfts_bal_id, -1)
119 and (person_id = p_person_id
120 and
121 bnfts_bal_id = p_bnfts_bal_id)
122 and business_group_id = p_business_group_id
123 and ((p_effective_date between effective_start_date
124 and effective_end_date) or
125 (effective_start_date between p_validation_start_date
126 and p_validation_end_date) or
127 (effective_end_date between p_validation_start_date
128 and p_validation_end_date));
129 --
130 begin
131 --
132 hr_utility.set_location('Entering:'||l_proc, 5);
133 --
134 open c1;
135 fetch c1 into v_dummy;
136 --
137 if c1%found then
138 close c1;
139 fnd_message.set_name('BEN','BEN_91697_PER_ID_BNFT_BAL_ID_U');
140 fnd_message.raise_error;
141 end if;
142 --
143 close c1;
144 --
145 hr_utility.set_location('Leaving:'||l_proc, 10);
146 --
147 End chk_per_bnft_bal_unique;
148 --
149 -- ----------------------------------------------------------------------------
150 -- |--------------------------< dt_update_validate >--------------------------|
151 -- ----------------------------------------------------------------------------
152 -- {Start Of Comments}
153 --
154 -- Description:
155 -- This procedure is used for referential integrity of datetracked
156 -- parent entities when a datetrack update operation is taking place
157 -- and where there is no cascading of update defined for this entity.
158 --
159 -- Prerequisites:
160 -- This procedure is called from the update_validate.
161 --
162 -- In Parameters:
163 --
164 -- Post Success:
165 -- Processing continues.
166 --
167 -- Post Failure:
168 --
169 -- Developer Implementation Notes:
170 -- This procedure should not need maintenance unless the HR Schema model
171 -- changes.
172 --
173 -- Access Status:
174 -- Internal Row Handler Use Only.
175 --
176 -- {End Of Comments}
177 -- ----------------------------------------------------------------------------
178 Procedure dt_update_validate
179 (p_bnfts_bal_id in number default hr_api.g_number,
180 p_datetrack_mode in varchar2,
181 p_validation_start_date in date,
182 p_validation_end_date in date) Is
183 --
184 l_proc varchar2(72) := g_package||'dt_update_validate';
185 l_integrity_error Exception;
186 l_table_name all_tables.table_name%TYPE;
187 --
188 Begin
189 hr_utility.set_location('Entering:'||l_proc, 5);
190 --
191 -- Ensure that the p_datetrack_mode argument is not null
192 --
193 hr_api.mandatory_arg_error
194 (p_api_name => l_proc,
195 p_argument => 'datetrack_mode',
196 p_argument_value => p_datetrack_mode);
197 --
198 -- Only perform the validation if the datetrack update mode is valid
199 --
200 If (dt_api.validate_dt_upd_mode(p_datetrack_mode => p_datetrack_mode)) then
201 --
202 --
203 -- Ensure the arguments are not null
204 --
205 hr_api.mandatory_arg_error
206 (p_api_name => l_proc,
207 p_argument => 'validation_start_date',
208 p_argument_value => p_validation_start_date);
209 --
210 hr_api.mandatory_arg_error
211 (p_api_name => l_proc,
212 p_argument => 'validation_end_date',
213 p_argument_value => p_validation_end_date);
214 --
215 If ((nvl(p_bnfts_bal_id, hr_api.g_number) <> hr_api.g_number) and
216 NOT (dt_api.check_min_max_dates
217 (p_base_table_name => 'ben_bnfts_bal_f',
218 p_base_key_column => 'bnfts_bal_id',
219 p_base_key_value => p_bnfts_bal_id,
220 p_from_date => p_validation_start_date,
221 p_to_date => p_validation_end_date))) Then
222 l_table_name := 'ben_bnfts_bal_f';
223 Raise l_integrity_error;
224 End If;
225 --
226 End If;
227 --
228 hr_utility.set_location(' Leaving:'||l_proc, 10);
229 Exception
230 When l_integrity_error Then
231 --
232 -- A referential integrity check was violated therefore
233 -- we must error
234 --
235 ben_utility.parent_integrity_error(p_table_name => l_table_name);
236 When Others Then
237 --
238 -- An unhandled or unexpected error has occurred which
239 -- we must report
240 --
241 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
242 fnd_message.set_token('PROCEDURE', l_proc);
243 fnd_message.set_token('STEP','15');
244 fnd_message.raise_error;
245 End dt_update_validate;
246 --
247 -- ----------------------------------------------------------------------------
248 -- |--------------------------< dt_delete_validate >--------------------------|
249 -- ----------------------------------------------------------------------------
250 -- {Start Of Comments}
251 --
252 -- Description:
253 -- This procedure is used for referential integrity of datetracked
254 -- child entities when either a datetrack DELETE or ZAP is in operation
255 -- and where there is no cascading of delete defined for this entity.
256 -- For the datetrack mode of DELETE or ZAP we must ensure that no
257 -- datetracked child rows exist between the validation start and end
258 -- dates.
259 --
260 -- Prerequisites:
261 -- This procedure is called from the delete_validate.
262 --
263 -- In Parameters:
264 --
265 -- Post Success:
266 -- Processing continues.
267 --
268 -- Post Failure:
269 -- If a row exists by determining the returning Boolean value from the
270 -- generic dt_api.rows_exist function then we must supply an error via
271 -- the use of the local exception handler l_rows_exist.
272 --
273 -- Developer Implementation Notes:
274 -- This procedure should not need maintenance unless the HR Schema model
275 -- changes.
276 --
277 -- Access Status:
278 -- Internal Row Handler Use Only.
279 --
280 -- {End Of Comments}
281 -- ----------------------------------------------------------------------------
282 Procedure dt_delete_validate
283 (p_per_bnfts_bal_id in number,
284 p_datetrack_mode in varchar2,
285 p_validation_start_date in date,
286 p_validation_end_date in date) Is
287 --
288 l_proc varchar2(72) := g_package||'dt_delete_validate';
289 l_rows_exist Exception;
290 l_table_name all_tables.table_name%TYPE;
291 --
292 Begin
293 hr_utility.set_location('Entering:'||l_proc, 5);
294 --
295 -- Ensure that the p_datetrack_mode argument is not null
296 --
297 hr_api.mandatory_arg_error
298 (p_api_name => l_proc,
299 p_argument => 'datetrack_mode',
300 p_argument_value => p_datetrack_mode);
301 --
302 -- Only perform the validation if the datetrack mode is either
303 -- DELETE or ZAP
304 --
305 If (p_datetrack_mode = 'DELETE' or
306 p_datetrack_mode = 'ZAP') then
307 --
308 --
309 -- Ensure the arguments are not null
310 --
311 hr_api.mandatory_arg_error
312 (p_api_name => l_proc,
313 p_argument => 'validation_start_date',
314 p_argument_value => p_validation_start_date);
315 --
316 hr_api.mandatory_arg_error
317 (p_api_name => l_proc,
318 p_argument => 'validation_end_date',
319 p_argument_value => p_validation_end_date);
320 --
321 hr_api.mandatory_arg_error
322 (p_api_name => l_proc,
323 p_argument => 'per_bnfts_bal_id',
324 p_argument_value => p_per_bnfts_bal_id);
325 --
326 --
327 --
328 End If;
329 --
330 hr_utility.set_location(' Leaving:'||l_proc, 10);
331 Exception
332 When l_rows_exist Then
333 --
334 -- A referential integrity check was violated therefore
335 -- we must error
336 --
337 ben_utility.child_exists_error(p_table_name => l_table_name);
338 --
339 When Others Then
340 --
341 -- An unhandled or unexpected error has occurred which
342 -- we must report
343 --
344 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
345 fnd_message.set_token('PROCEDURE', l_proc);
346 fnd_message.set_token('STEP','15');
347 fnd_message.raise_error;
348 End dt_delete_validate;
349 --
350 -- ----------------------------------------------------------------------------
351 -- |---------------------------< insert_validate >----------------------------|
352 -- ----------------------------------------------------------------------------
353 Procedure insert_validate
354 (p_rec in ben_pbb_shd.g_rec_type,
355 p_effective_date in date,
356 p_datetrack_mode in varchar2,
357 p_validation_start_date in date,
358 p_validation_end_date in date) is
359 --
360 l_proc varchar2(72) := g_package||'insert_validate';
361 --
362 Begin
363 hr_utility.set_location('Entering:'||l_proc, 5);
364 --
365 -- Call all supporting business operations
366 --
367 --
368 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
369 --
370 chk_per_bnfts_bal_id
371 (p_per_bnfts_bal_id => p_rec.per_bnfts_bal_id,
372 p_effective_date => p_effective_date,
373 p_object_version_number => p_rec.object_version_number);
374 --
375 chk_per_bnft_bal_unique
376 (p_per_bnfts_bal_id => p_rec.per_bnfts_bal_id,
377 p_person_id => p_rec.person_id,
378 p_bnfts_bal_id => p_rec.bnfts_bal_id,
379 p_business_group_id => p_rec.business_group_id,
380 p_validation_start_date => p_validation_start_date,
381 p_validation_end_date => p_validation_end_date,
382 p_effective_date => p_effective_date);
383 --
384 hr_utility.set_location(' Leaving:'||l_proc, 10);
385 End insert_validate;
386 --
387 -- ----------------------------------------------------------------------------
388 -- |---------------------------< update_validate >----------------------------|
389 -- ----------------------------------------------------------------------------
390 Procedure update_validate
391 (p_rec in ben_pbb_shd.g_rec_type,
392 p_effective_date in date,
393 p_datetrack_mode in varchar2,
394 p_validation_start_date in date,
395 p_validation_end_date in date) is
396 --
397 l_proc varchar2(72) := g_package||'update_validate';
398 --
399 Begin
400 hr_utility.set_location('Entering:'||l_proc, 5);
401 --
402 -- Call all supporting business operations
403 --
404 --
405 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
406 --
407 chk_per_bnfts_bal_id
408 (p_per_bnfts_bal_id => p_rec.per_bnfts_bal_id,
409 p_effective_date => p_effective_date,
410 p_object_version_number => p_rec.object_version_number);
411 --
412 -- Call the datetrack update integrity operation
413 --
414 dt_update_validate
415 (p_bnfts_bal_id => p_rec.bnfts_bal_id,
416 p_datetrack_mode => p_datetrack_mode,
417 p_validation_start_date => p_validation_start_date,
418 p_validation_end_date => p_validation_end_date);
419 --
420 chk_per_bnft_bal_unique
421 (p_per_bnfts_bal_id => p_rec.per_bnfts_bal_id,
422 p_person_id => p_rec.person_id,
423 p_bnfts_bal_id => p_rec.bnfts_bal_id,
424 p_business_group_id => p_rec.business_group_id,
425 p_validation_start_date => p_validation_start_date,
426 p_validation_end_date => p_validation_end_date,
427 p_effective_date => p_effective_date);
428 --
429 hr_utility.set_location(' Leaving:'||l_proc, 10);
430 End update_validate;
431 --
432 -- ----------------------------------------------------------------------------
433 -- |---------------------------< delete_validate >----------------------------|
434 -- ----------------------------------------------------------------------------
435 Procedure delete_validate
436 (p_rec in ben_pbb_shd.g_rec_type,
437 p_effective_date in date,
438 p_datetrack_mode in varchar2,
439 p_validation_start_date in date,
440 p_validation_end_date in date) is
441 --
442 l_proc varchar2(72) := g_package||'delete_validate';
443 --
444 Begin
445 hr_utility.set_location('Entering:'||l_proc, 5);
446 --
447 -- Call all supporting business operations
448 --
449 dt_delete_validate
450 (p_datetrack_mode => p_datetrack_mode,
451 p_validation_start_date => p_validation_start_date,
452 p_validation_end_date => p_validation_end_date,
453 p_per_bnfts_bal_id => p_rec.per_bnfts_bal_id);
454 --
455 hr_utility.set_location(' Leaving:'||l_proc, 10);
456 End delete_validate;
457 --
458 --
459 -- ---------------------------------------------------------------------------
460 -- |---------------------< return_legislation_code >-------------------------|
461 -- ---------------------------------------------------------------------------
462 --
463 function return_legislation_code
464 (p_per_bnfts_bal_id in number) return varchar2 is
465 --
466 -- Declare cursor
467 --
468 cursor csr_leg_code is
469 select a.legislation_code
470 from per_business_groups a,
471 ben_per_bnfts_bal_f b
472 where b.per_bnfts_bal_id = p_per_bnfts_bal_id
473 and a.business_group_id = b.business_group_id;
474 --
475 -- Declare local variables
476 --
477 l_legislation_code varchar2(150);
478 l_proc varchar2(72) := g_package||'return_legislation_code';
479 --
480 begin
481 --
482 hr_utility.set_location('Entering:'|| l_proc, 10);
483 --
484 -- Ensure that all the mandatory parameter are not null
485 --
486 hr_api.mandatory_arg_error(p_api_name => l_proc,
487 p_argument => 'per_bnfts_bal_id',
488 p_argument_value => p_per_bnfts_bal_id);
489 --
490 open csr_leg_code;
491 --
492 fetch csr_leg_code into l_legislation_code;
493 --
494 if csr_leg_code%notfound then
495 --
496 close csr_leg_code;
497 --
498 -- The primary key is invalid therefore we must error
499 --
500 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
501 fnd_message.raise_error;
502 --
503 end if;
504 --
505 close csr_leg_code;
506 --
507 hr_utility.set_location(' Leaving:'|| l_proc, 20);
508 --
509 return l_legislation_code;
510 --
511 end return_legislation_code;
512 --
513 end ben_pbb_bus;