[Home] [Help]
PACKAGE BODY: APPS.BEN_PEA_BUS
Source
1 Package Body ben_pea_bus as
2 /* $Header: bepearhi.pkb 120.3 2011/09/02 10:52:24 pvelvano ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ben_pea_bus.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------< chk_prtt_enrt_actn_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 -- prtt_enrt_actn_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_prtt_enrt_actn_id(p_prtt_enrt_actn_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_prtt_enrt_actn_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_pea_shd.api_updating
49 (p_effective_date => p_effective_date,
50 p_prtt_enrt_actn_id => p_prtt_enrt_actn_id,
51 p_object_version_number => p_object_version_number);
52 --
53 if (l_api_updating
54 and nvl(p_prtt_enrt_actn_id,hr_api.g_number)
55 <> ben_pea_shd.g_old_rec.prtt_enrt_actn_id) then
56 --
57 -- raise error as PK has changed
58 --
59 ben_pea_shd.constraint_error('BEN_PRTT_ENRT_ACTN_PK');
60 --
61 elsif not l_api_updating then
62 --
63 -- check if PK is null
64 --
65 if p_prtt_enrt_actn_id is not null then
66 --
67 -- raise error as PK is not null
68 --
69 ben_pea_shd.constraint_error('BEN_PRTT_ENRT_ACTN_PK');
70 --
71 end if;
72 --
73 end if;
74 --
75 hr_utility.set_location('Leaving:'||l_proc, 10);
76 --
77 End chk_prtt_enrt_actn_id;
78 --
79 -- ----------------------------------------------------------------------------
80 -- |------< chk_actn_typ_id >------|
81 -- ----------------------------------------------------------------------------
82 --
83 -- Description
84 -- This procedure checks that a referenced foreign key actually exists
85 -- in the referenced table.
86 --
87 -- Pre-Conditions
88 -- None.
89 --
90 -- In Parameters
91 -- p_prtt_enrt_actn_id PK
92 -- p_actn_typ_id ID of FK column
93 -- p_effective_date session date
94 -- p_object_version_number object version number
95 --
96 -- Post Success
97 -- Processing continues
98 --
99 -- Post Failure
100 -- Error raised.
101 --
102 -- Access Status
103 -- Internal table handler use only.
104 --
105 Procedure chk_actn_typ_id (p_prtt_enrt_actn_id in number,
106 p_actn_typ_id in number,
107 p_effective_date in date,
108 p_object_version_number in number) is
109 --
110 l_proc varchar2(72) := g_package||'chk_actn_typ_id';
111 l_api_updating boolean;
112 l_dummy varchar2(1);
113 --
114 cursor c1 is
115 select null
116 from ben_actn_typ a
117 where a.actn_typ_id = p_actn_typ_id;
118 --
119 Begin
120 --
121 hr_utility.set_location('Entering:'||l_proc,5);
122 --
123 l_api_updating := ben_pea_shd.api_updating
124 (p_prtt_enrt_actn_id => p_prtt_enrt_actn_id,
125 p_effective_date => p_effective_date,
126 p_object_version_number => p_object_version_number);
127 --
128 if (l_api_updating
129 and nvl(p_actn_typ_id,hr_api.g_number)
130 <> nvl(ben_pea_shd.g_old_rec.actn_typ_id,hr_api.g_number)
131 or not l_api_updating) then
132 --
133 -- check if actn_typ_id value exists in ben_actn_typ table
134 --
135 open c1;
136 --
137 fetch c1 into l_dummy;
138 if c1%notfound then
139 --
140 close c1;
141 --
142 -- raise error as FK does not relate to PK in ben_actn_typ
143 -- table.
144 --
145 ben_pea_shd.constraint_error('BEN_PRTT_ENRT_ACTN_FK2');
146 --
147 end if;
148 --
149 close c1;
150 --
151 end if;
152 --
153 hr_utility.set_location('Leaving:'||l_proc,10);
154 --
155 End chk_actn_typ_id;
156 --
157 -- ----------------------------------------------------------------------------
158 -- |--------------------------< dt_update_validate >--------------------------|
159 -- ----------------------------------------------------------------------------
160 -- {Start Of Comments}
161 --
162 -- Description:
163 -- This procedure is used for referential integrity of datetracked
164 -- parent entities when a datetrack update operation is taking place
165 -- and where there is no cascading of update defined for this entity.
166 --
167 -- Prerequisites:
168 -- This procedure is called from the update_validate.
169 --
170 -- In Parameters:
171 --
172 -- Post Success:
173 -- Processing continues.
174 --
175 -- Post Failure:
176 --
177 -- Developer Implementation Notes:
178 -- This procedure should not need maintenance unless the HR Schema model
179 -- changes.
180 --
181 -- Access Status:
182 -- Internal Row Handler Use Only.
183 --
184 -- {End Of Comments}
185 -- ----------------------------------------------------------------------------
186 Procedure dt_update_validate
187 (p_prtt_enrt_rslt_id in number default hr_api.g_number,
188 p_datetrack_mode in varchar2,
189 p_validation_start_date in date,
190 p_validation_end_date in date) Is
191 --
192 l_proc varchar2(72) := g_package||'dt_update_validate';
193 l_integrity_error Exception;
194 l_table_name all_tables.table_name%TYPE;
195 --
196 Begin
197 hr_utility.set_location('Entering:'||l_proc, 5);
198 --
199 -- Ensure that the p_datetrack_mode argument is not null
200 --
201 hr_api.mandatory_arg_error
202 (p_api_name => l_proc,
203 p_argument => 'datetrack_mode',
204 p_argument_value => p_datetrack_mode);
205 --
206 -- Only perform the validation if the datetrack update mode is valid
207 --
208 If (dt_api.validate_dt_upd_mode(p_datetrack_mode => p_datetrack_mode)) then
209 --
210 --
211 -- Ensure the arguments are not null
212 --
213 hr_api.mandatory_arg_error
214 (p_api_name => l_proc,
215 p_argument => 'validation_start_date',
216 p_argument_value => p_validation_start_date);
217 --
218 hr_api.mandatory_arg_error
219 (p_api_name => l_proc,
220 p_argument => 'validation_end_date',
221 p_argument_value => p_validation_end_date);
222 --
223 If ((nvl(p_prtt_enrt_rslt_id, hr_api.g_number) <> hr_api.g_number) and
224 NOT (dt_api.check_min_max_dates
225 (p_base_table_name => 'ben_prtt_enrt_rslt_f',
226 p_base_key_column => 'prtt_enrt_rslt_id',
227 p_base_key_value => p_prtt_enrt_rslt_id,
228 p_from_date => p_validation_start_date,
229 p_to_date => p_validation_end_date))) Then
230 l_table_name := 'ben_prtt_enrt_rslt_f';
231 Raise l_integrity_error;
232 End If;
233 --
234 End If;
235 --
236 hr_utility.set_location(' Leaving:'||l_proc, 10);
237 Exception
238 When l_integrity_error Then
239 --
240 -- A referential integrity check was violated therefore
241 -- we must error
242 --
243 hr_utility.set_message(801, 'HR_7216_DT_UPD_INTEGRITY_ERR');
244 hr_utility.set_message_token('TABLE_NAME', l_table_name);
245 hr_utility.raise_error;
246 When Others Then
247 --
248 -- An unhandled or unexpected error has occurred which
249 -- we must report
250 --
251 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
252 hr_utility.set_message_token('PROCEDURE', l_proc);
253 hr_utility.set_message_token('STEP','15');
254 hr_utility.raise_error;
255 End dt_update_validate;
256 --
257 -- ----------------------------------------------------------------------------
258 -- |--------------------------< dt_delete_validate >--------------------------|
259 -- ----------------------------------------------------------------------------
260 -- {Start Of Comments}
261 --
262 -- Description:
263 -- This procedure is used for referential integrity of datetracked
264 -- child entities when either a datetrack DELETE or ZAP is in operation
265 -- and where there is no cascading of delete defined for this entity.
266 -- For the datetrack mode of DELETE or ZAP we must ensure that no
267 -- datetracked child rows exist between the validation start and end
268 -- dates.
269 --
270 -- Prerequisites:
271 -- This procedure is called from the delete_validate.
272 --
273 -- In Parameters:
274 --
275 -- Post Success:
276 -- Processing continues.
277 --
278 -- Post Failure:
279 -- If a row exists by determining the returning Boolean value from the
280 -- generic dt_api.rows_exist function then we must supply an error via
281 -- the use of the local exception handler l_rows_exist.
282 --
283 -- Developer Implementation Notes:
284 -- This procedure should not need maintenance unless the HR Schema model
285 -- changes.
286 --
287 -- Access Status:
288 -- Internal Row Handler Use Only.
289 --
290 -- {End Of Comments}
291 -- ----------------------------------------------------------------------------
292 Procedure dt_delete_validate
293 (p_prtt_enrt_actn_id in number,
294 p_datetrack_mode in varchar2,
295 p_validation_start_date in date,
296 p_validation_end_date in date) Is
297 --
298 l_proc varchar2(72) := g_package||'dt_delete_validate';
299 l_rows_exist Exception;
300 l_table_name all_tables.table_name%TYPE;
301 --
302 Begin
303 hr_utility.set_location('Entering:'||l_proc, 5);
304 --
305 -- Ensure that the p_datetrack_mode argument is not null
306 --
307 hr_api.mandatory_arg_error
308 (p_api_name => l_proc,
309 p_argument => 'datetrack_mode',
310 p_argument_value => p_datetrack_mode);
311 --
312 -- Only perform the validation if the datetrack mode is either
313 -- DELETE or ZAP
314 --
315 If (p_datetrack_mode = 'DELETE' or
316 p_datetrack_mode = 'ZAP') then
317 --
318 --
319 -- Ensure the arguments are not null
320 --
321 hr_api.mandatory_arg_error
322 (p_api_name => l_proc,
323 p_argument => 'validation_start_date',
324 p_argument_value => p_validation_start_date);
325 --
326 hr_api.mandatory_arg_error
327 (p_api_name => l_proc,
328 p_argument => 'validation_end_date',
329 p_argument_value => p_validation_end_date);
330 --
331 hr_api.mandatory_arg_error
332 (p_api_name => l_proc,
333 p_argument => 'prtt_enrt_actn_id',
334 p_argument_value => p_prtt_enrt_actn_id);
335 --
336 If (dt_api.rows_exist
337 (p_base_table_name => 'ben_cvrd_dpnt_ctfn_prvdd_f',
338 p_base_key_column => 'prtt_enrt_actn_id',
339 p_base_key_value => p_prtt_enrt_actn_id,
340 p_from_date => p_validation_start_date,
341 p_to_date => p_validation_end_date)) Then
342 l_table_name := 'ben_cvrd_dpnt_ctfn_prvdd_f';
343 Raise l_rows_exist;
344 End If;
345 If (dt_api.rows_exist
346 (p_base_table_name => 'ben_pl_bnf_ctfn_prvdd_f',
347 p_base_key_column => 'prtt_enrt_actn_id',
348 p_base_key_value => p_prtt_enrt_actn_id,
349 p_from_date => p_validation_start_date,
350 p_to_date => p_validation_end_date)) Then
351 l_table_name := 'ben_pl_bnf_ctfn_prvdd_f';
352 Raise l_rows_exist;
353 End If;
354 If (dt_api.rows_exist
355 (p_base_table_name => 'ben_prtt_enrt_ctfn_prvdd_f',
356 p_base_key_column => 'prtt_enrt_actn_id',
357 p_base_key_value => p_prtt_enrt_actn_id,
358 p_from_date => p_validation_start_date,
359 p_to_date => p_validation_end_date)) Then
360 l_table_name := 'ben_prtt_enrt_ctfn_prvdd_f';
361 Raise l_rows_exist;
362 End If;
363 --
364 End If;
365 --
366 hr_utility.set_location(' Leaving:'||l_proc, 10);
367 Exception
368 When l_rows_exist Then
369 --
370 -- A referential integrity check was violated therefore
371 -- we must error
372 --
373 hr_utility.set_message(801, 'HR_7215_DT_CHILD_EXISTS');
374 hr_utility.set_message_token('TABLE_NAME', l_table_name);
375 hr_utility.raise_error;
376 When Others Then
377 --
378 -- An unhandled or unexpected error has occurred which
379 -- we must report
380 --
381 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
382 hr_utility.set_message_token('PROCEDURE', l_proc);
383 hr_utility.set_message_token('STEP','15');
384 hr_utility.raise_error;
385 End dt_delete_validate;
386 --
387 -- ----------------------------------------------------------------------------
388 -- |---------------------------< insert_validate >----------------------------|
389 -- ----------------------------------------------------------------------------
390 Procedure insert_validate
391 (p_rec in ben_pea_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||'insert_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_prtt_enrt_actn_id
408 (p_prtt_enrt_actn_id => p_rec.prtt_enrt_actn_id,
409 p_effective_date => p_effective_date,
410 p_object_version_number => p_rec.object_version_number);
411 --
412 chk_actn_typ_id
413 (p_prtt_enrt_actn_id => p_rec.prtt_enrt_actn_id,
414 p_actn_typ_id => p_rec.actn_typ_id,
415 p_effective_date => p_effective_date,
416 p_object_version_number => p_rec.object_version_number);
417 --
418 hr_utility.set_location(' Leaving:'||l_proc, 10);
419 End insert_validate;
420 --
421 -- ----------------------------------------------------------------------------
422 -- |---------------------------< update_validate >----------------------------|
423 -- ----------------------------------------------------------------------------
424 Procedure update_validate
425 (p_rec in ben_pea_shd.g_rec_type,
426 p_effective_date in date,
427 p_datetrack_mode in varchar2,
428 p_validation_start_date in date,
429 p_validation_end_date in date) is
430 --
431 l_proc varchar2(72) := g_package||'update_validate';
432 --
433 Begin
434 hr_utility.set_location('Entering:'||l_proc, 5);
435 --
436 -- Call all supporting business operations
437 --
438 --
439 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
440 --
441 chk_prtt_enrt_actn_id
442 (p_prtt_enrt_actn_id => p_rec.prtt_enrt_actn_id,
443 p_effective_date => p_effective_date,
444 p_object_version_number => p_rec.object_version_number);
445 --
446 chk_actn_typ_id
447 (p_prtt_enrt_actn_id => p_rec.prtt_enrt_actn_id,
448 p_actn_typ_id => p_rec.actn_typ_id,
449 p_effective_date => p_effective_date,
450 p_object_version_number => p_rec.object_version_number);
451 --
452 -- Call the datetrack update integrity operation
453 --
454 dt_update_validate
455 (p_prtt_enrt_rslt_id => p_rec.prtt_enrt_rslt_id,
456 p_datetrack_mode => p_datetrack_mode,
457 p_validation_start_date => p_validation_start_date,
458 p_validation_end_date => p_validation_end_date);
459 --
460 hr_utility.set_location(' Leaving:'||l_proc, 10);
461 End update_validate;
462 --
463 -- ----------------------------------------------------------------------------
464 -- |---------------------------< delete_validate >----------------------------|
465 -- ----------------------------------------------------------------------------
466 Procedure delete_validate
467 (p_rec in ben_pea_shd.g_rec_type,
468 p_effective_date in date,
469 p_datetrack_mode in varchar2,
470 p_validation_start_date in date,
471 p_validation_end_date in date) is
472 --
473 l_proc varchar2(72) := g_package||'delete_validate';
474 --
475 Begin
476 hr_utility.set_location('Entering:'||l_proc, 5);
477 --
478 -- Call all supporting business operations
479 --
480 dt_delete_validate
481 (p_datetrack_mode => p_datetrack_mode,
482 p_validation_start_date => p_validation_start_date,
483 p_validation_end_date => p_validation_end_date,
484 p_prtt_enrt_actn_id => p_rec.prtt_enrt_actn_id);
485 --
486 hr_utility.set_location(' Leaving:'||l_proc, 10);
487 End delete_validate;
488 --
489 --
490 -- ---------------------------------------------------------------------------
491 -- |---------------------< return_legislation_code >-------------------------|
492 -- ---------------------------------------------------------------------------
493 --
494 function return_legislation_code
495 (p_prtt_enrt_actn_id in number) return varchar2 is
496 --
497 -- Declare cursor
498 --
499 cursor csr_leg_code is
500 select a.legislation_code
501 from per_business_groups a,
502 ben_prtt_enrt_actn_f b
503 where b.prtt_enrt_actn_id = p_prtt_enrt_actn_id
504 and a.business_group_id = b.business_group_id;
505 --
506 -- Declare local variables
507 --
508 l_legislation_code varchar2(150);
509 l_proc varchar2(72) := g_package||'return_legislation_code';
510 --
511 begin
512 --
513 hr_utility.set_location('Entering:'|| l_proc, 10);
514 --
515 -- Ensure that all the mandatory parameter are not null
516 --
517 hr_api.mandatory_arg_error(p_api_name => l_proc,
518 p_argument => 'prtt_enrt_actn_id',
519 p_argument_value => p_prtt_enrt_actn_id);
520 --
521 open csr_leg_code;
522 --
523 fetch csr_leg_code into l_legislation_code;
524 --
525 if csr_leg_code%notfound then
526 --
527 close csr_leg_code;
528 --
529 -- The primary key is invalid therefore we must error
530 --
531 hr_utility.set_message(801,'HR_7220_INVALID_PRIMARY_KEY');
532 hr_utility.raise_error;
533 --
534 end if;
535 --
536 close csr_leg_code;
537 --
538 hr_utility.set_location(' Leaving:'|| l_proc, 20);
539 --
540 return l_legislation_code;
541 --
542 end return_legislation_code;
543 --
544 end ben_pea_bus;