[Home] [Help]
PACKAGE BODY: APPS.BEN_RRB_BUS
Source
1 Package Body ben_rrb_bus as
2 /* $Header: berrbrhi.pkb 120.0.12010000.2 2008/08/05 15:26:44 ubhat ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ben_rrb_bus.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------< chk_regn_for_regy_body_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 -- regn_for_regy_body_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_regn_for_regy_body_id(p_regn_for_regy_body_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_regn_for_regy_body_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_rrb_shd.api_updating
49 (p_effective_date => p_effective_date,
50 p_regn_for_regy_body_id => p_regn_for_regy_body_id,
51 p_object_version_number => p_object_version_number);
52 --
53 if (l_api_updating
54 and nvl(p_regn_for_regy_body_id,hr_api.g_number)
55 <> ben_rrb_shd.g_old_rec.regn_for_regy_body_id) then
56 --
57 -- raise error as PK has changed
58 --
59 ben_rrb_shd.constraint_error('BEN_REGN_FOR_REGY_BODY_PK');
60 --
61 elsif not l_api_updating then
62 --
63 -- check if PK is null
64 --
65 if p_regn_for_regy_body_id is not null then
66 --
67 -- raise error as PK is not null
68 --
69 ben_rrb_shd.constraint_error('BEN_REGN_FOR_REGY_BODY_PK');
70 --
71 end if;
72 --
73 end if;
74 --
75 hr_utility.set_location('Leaving:'||l_proc, 10);
76 --
77 End chk_regn_for_regy_body_id;
78 --
79 -- ----------------------------------------------------------------------------
80 -- |------< chk_regn_admin_cd >------|
81 -- ----------------------------------------------------------------------------
82 --
83 -- Description
84 -- This procedure is used to check that the lookup value is valid.
85 --
86 -- Pre Conditions
87 -- None.
88 --
89 -- In Parameters
90 -- regn_for_regy_body_id PK of record being inserted or updated.
91 -- regn_admin_cd Value of lookup code.
92 -- effective_date effective date
93 -- object_version_number Object version number of record being
94 -- inserted or updated.
95 --
96 -- Post Success
97 -- Processing continues
98 --
99 -- Post Failure
100 -- Error handled by procedure
101 --
102 -- Access Status
103 -- Internal table handler use only.
104 --
105 Procedure chk_regn_admin_cd(p_regn_for_regy_body_id in number,
106 p_regn_admin_cd in varchar2,
107 p_effective_date in date,
108 p_object_version_number in number) is
109 --
110 l_proc varchar2(72) := g_package||'chk_regn_admin_cd';
111 l_api_updating boolean;
112 --
113 Begin
114 --
115 hr_utility.set_location('Entering:'||l_proc, 5);
116 --
117 l_api_updating := ben_rrb_shd.api_updating
118 (p_regn_for_regy_body_id => p_regn_for_regy_body_id,
119 p_effective_date => p_effective_date,
120 p_object_version_number => p_object_version_number);
121 --
122 if (l_api_updating
123 and p_regn_admin_cd
124 <> nvl(ben_rrb_shd.g_old_rec.regn_admin_cd,hr_api.g_varchar2)
125 or not l_api_updating)
126 and p_regn_admin_cd is not null then
127 --
128 -- check if value of lookup falls within lookup type.
129 --
130 if hr_api.not_exists_in_hr_lookups
131 (p_lookup_type => 'BEN_REGN_ADMN',
132 p_lookup_code => p_regn_admin_cd,
133 p_effective_date => p_effective_date) then
134 --
135 -- raise error as does not exist as lookup
136 --
137 fnd_message.set_name('PAY','HR_LOOKUP_DOES_NOT_EXIST');
138 fnd_message.raise_error;
139 --
140 end if;
141 --
142 end if;
143 --
144 hr_utility.set_location('Leaving:'||l_proc,10);
145 --
146 end chk_regn_admin_cd;
147 --
148 -- ----------------------------------------------------------------------------
149 -- |------< chk_organization_id >------|
150 -- ----------------------------------------------------------------------------
151 --
152 -- Description
153 -- This procedure is used to check that the organization_id field (for Regy Body)
154 -- is unique within Regulation on insert and on update.
155 --
156 -- Pre Conditions
157 -- None.
158 --
159 -- In Parameters
160 -- regn_for_regy_body_id PK of record being inserted or updated.
161 -- regn_id of record being inserted or updated.
162 -- organization_id that is beeing inserted ot updated to.
163 -- effective_date Effective Date of session
164 -- business group ID
165 -- object_version_number Object version number of record being
166 -- inserted or updated.
167 --
168 -- Post Success
169 -- Processing continues
170 --
171 -- Post Failure
172 -- Errors handled by the procedure
173 --
174 -- Access Status
175 -- HR Development Internal use only.
176 --
177 Procedure chk_organization_id(
178 p_regn_for_regy_body_id in number,
179 p_regn_id in number,
180 p_organization_id in number,
181 p_effective_date in date,
182 p_validation_start_date in date,
183 p_validation_end_date in date,
184 p_business_group_id in number,
185 p_object_version_number in number) is
186 --
187 l_proc varchar2(72) := g_package||'chk_organization_id';
188 l_api_updating boolean;
189 l_exists varchar2(1);
190 --
191 --
192 cursor csr_organization_id is
193 select null
194 from ben_regn_for_regy_body_f
195 where organization_id = p_organization_id
196 and regn_id = p_regn_id
197 and regn_for_regy_body_id <> nvl(p_regn_for_regy_body_id,hr_api.g_number)
198 and business_group_id + 0 = p_business_group_id
199 and p_validation_start_date <= effective_end_date
200 and p_validation_end_date >= effective_start_date
201 ;
202 --
203 Begin
204 --
205 hr_utility.set_location('Entering:'||l_proc, 5);
206 --
207 l_api_updating := ben_rrb_shd.api_updating
208 (p_effective_date => p_effective_date,
209 p_regn_for_regy_body_id => p_regn_for_regy_body_id,
210 p_object_version_number => p_object_version_number);
211 --
212 if (l_api_updating
213 and p_organization_id <> ben_rrb_shd.g_old_rec.organization_id) or
214 not l_api_updating then
215 --
216 --
217 hr_utility.set_location('Entering:'||l_proc, 10);
218 --
219 -- check if this regy body already exists for this regulation
220 --
221 open csr_organization_id;
222 fetch csr_organization_id into l_exists;
223 if csr_organization_id%found then
224 close csr_organization_id;
225 --
226 -- raise error as this reg body already exists for this regulation
227 --
228 fnd_message.set_name('BEN', 'BEN_91009_NAME_NOT_UNIQUE');
229 fnd_message.raise_error;
230 --
231 end if;
232 --
233 end if;
234 --
235 hr_utility.set_location('Leaving:'||l_proc, 20);
236 --
237 End chk_organization_id;
238 --
239 -- ----------------------------------------------------------------------------
240 -- |--------------------------< dt_update_validate >--------------------------|
241 -- ----------------------------------------------------------------------------
242 -- {Start Of Comments}
243 --
244 -- Description:
245 -- This procedure is used for referential integrity of datetracked
246 -- parent entities when a datetrack update operation is taking place
247 -- and where there is no cascading of update defined for this entity.
248 --
249 -- Prerequisites:
250 -- This procedure is called from the update_validate.
251 --
252 -- In Parameters:
253 --
254 -- Post Success:
255 -- Processing continues.
256 --
257 -- Post Failure:
258 --
259 -- Developer Implementation Notes:
260 -- This procedure should not need maintenance unless the HR Schema model
261 -- changes.
262 --
263 -- Access Status:
264 -- Internal Row Handler Use Only.
265 --
266 -- {End Of Comments}
267 -- ----------------------------------------------------------------------------
268 Procedure dt_update_validate
269 (p_regn_id in number default hr_api.g_number,
270 p_datetrack_mode in varchar2,
271 p_validation_start_date in date,
272 p_validation_end_date in date) Is
273 --
274 l_proc varchar2(72) := g_package||'dt_update_validate';
275 l_integrity_error Exception;
276 l_table_name all_tables.table_name%TYPE;
277 --
278 Begin
279 hr_utility.set_location('Entering:'||l_proc, 5);
280 --
281 -- Ensure that the p_datetrack_mode argument is not null
282 --
283 hr_api.mandatory_arg_error
284 (p_api_name => l_proc,
285 p_argument => 'datetrack_mode',
286 p_argument_value => p_datetrack_mode);
287 --
288 -- Only perform the validation if the datetrack update mode is valid
289 --
290 If (dt_api.validate_dt_upd_mode(p_datetrack_mode => p_datetrack_mode)) then
291 --
292 --
293 -- Ensure the arguments are not null
294 --
295 hr_api.mandatory_arg_error
296 (p_api_name => l_proc,
297 p_argument => 'validation_start_date',
298 p_argument_value => p_validation_start_date);
299 --
300 hr_api.mandatory_arg_error
301 (p_api_name => l_proc,
302 p_argument => 'validation_end_date',
303 p_argument_value => p_validation_end_date);
304 --
305 If ((nvl(p_regn_id, hr_api.g_number) <> hr_api.g_number) and
306 NOT (dt_api.check_min_max_dates
307 (p_base_table_name => 'ben_regn_f',
308 p_base_key_column => 'regn_id',
309 p_base_key_value => p_regn_id,
310 p_from_date => p_validation_start_date,
311 p_to_date => p_validation_end_date))) Then
312 l_table_name := 'ben_regn_f';
313 Raise l_integrity_error;
314 End If;
315 --
316 End If;
317 --
318 hr_utility.set_location(' Leaving:'||l_proc, 10);
319 Exception
320 When l_integrity_error Then
321 --
322 -- A referential integrity check was violated therefore
323 -- we must error
324 --
325 fnd_message.set_name('PAY', 'HR_7216_DT_UPD_INTEGRITY_ERR');
326 fnd_message.set_token('TABLE_NAME', l_table_name);
327 fnd_message.raise_error;
328 When Others Then
329 --
330 -- An unhandled or unexpected error has occurred which
331 -- we must report
332 --
333 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
334 fnd_message.set_token('PROCEDURE', l_proc);
335 fnd_message.set_token('STEP','15');
336 fnd_message.raise_error;
337 End dt_update_validate;
338 --
339 -- ----------------------------------------------------------------------------
340 -- |--------------------------< dt_delete_validate >--------------------------|
341 -- ----------------------------------------------------------------------------
342 -- {Start Of Comments}
343 --
344 -- Description:
345 -- This procedure is used for referential integrity of datetracked
346 -- child entities when either a datetrack DELETE or ZAP is in operation
347 -- and where there is no cascading of delete defined for this entity.
348 -- For the datetrack mode of DELETE or ZAP we must ensure that no
349 -- datetracked child rows exist between the validation start and end
350 -- dates.
351 --
352 -- Prerequisites:
353 -- This procedure is called from the delete_validate.
354 --
355 -- In Parameters:
356 --
357 -- Post Success:
358 -- Processing continues.
359 --
360 -- Post Failure:
361 -- If a row exists by determining the returning Boolean value from the
362 -- generic dt_api.rows_exist function then we must supply an error via
363 -- the use of the local exception handler l_rows_exist.
364 --
365 -- Developer Implementation Notes:
366 -- This procedure should not need maintenance unless the HR Schema model
367 -- changes.
368 --
369 -- Access Status:
370 -- Internal Row Handler Use Only.
371 --
372 -- {End Of Comments}
373 -- ----------------------------------------------------------------------------
374 Procedure dt_delete_validate
375 (p_regn_for_regy_body_id in number,
376 p_datetrack_mode in varchar2,
377 p_validation_start_date in date,
378 p_validation_end_date in date) Is
379 --
380 l_proc varchar2(72) := g_package||'dt_delete_validate';
381 l_rows_exist Exception;
382 l_table_name all_tables.table_name%TYPE;
383 --
384 Begin
385 hr_utility.set_location('Entering:'||l_proc, 5);
386 --
387 -- Ensure that the p_datetrack_mode argument is not null
388 --
389 hr_api.mandatory_arg_error
390 (p_api_name => l_proc,
391 p_argument => 'datetrack_mode',
392 p_argument_value => p_datetrack_mode);
393 --
394 -- Only perform the validation if the datetrack mode is either
395 -- DELETE or ZAP
396 --
397 If (p_datetrack_mode = 'DELETE' or
398 p_datetrack_mode = 'ZAP') then
399 --
400 --
401 -- Ensure the arguments are not null
402 --
403 hr_api.mandatory_arg_error
404 (p_api_name => l_proc,
405 p_argument => 'validation_start_date',
406 p_argument_value => p_validation_start_date);
407 --
408 hr_api.mandatory_arg_error
409 (p_api_name => l_proc,
410 p_argument => 'validation_end_date',
411 p_argument_value => p_validation_end_date);
412 --
413 hr_api.mandatory_arg_error
414 (p_api_name => l_proc,
415 p_argument => 'regn_for_regy_body_id',
416 p_argument_value => p_regn_for_regy_body_id);
417 --
418 --
419 --
420 End If;
421 --
422 hr_utility.set_location(' Leaving:'||l_proc, 10);
423 Exception
424 When l_rows_exist Then
425 --
426 -- A referential integrity check was violated therefore
430 fnd_message.set_token('TABLE_NAME', l_table_name);
427 -- we must error
428 --
429 fnd_message.set_name('PAY', 'HR_7215_DT_CHILD_EXISTS');
431 fnd_message.raise_error;
432 When Others Then
433 --
434 -- An unhandled or unexpected error has occurred which
435 -- we must report
436 --
437 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
438 fnd_message.set_token('PROCEDURE', l_proc);
439 fnd_message.set_token('STEP','15');
440 fnd_message.raise_error;
441 End dt_delete_validate;
442 --
443 -- ----------------------------------------------------------------------------
444 -- |---------------------------< insert_validate >----------------------------|
445 -- ----------------------------------------------------------------------------
446 Procedure insert_validate
447 (p_rec in ben_rrb_shd.g_rec_type,
448 p_effective_date in date,
449 p_datetrack_mode in varchar2,
450 p_validation_start_date in date,
451 p_validation_end_date in date) is
452 --
453 l_proc varchar2(72) := g_package||'insert_validate';
454 --
455 Begin
456 hr_utility.set_location('Entering:'||l_proc, 5);
457 --
458 -- Call all supporting business operations
459 --
460 --
461 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
462 --
463 chk_regn_for_regy_body_id
464 (p_regn_for_regy_body_id => p_rec.regn_for_regy_body_id,
465 p_effective_date => p_effective_date,
466 p_object_version_number => p_rec.object_version_number);
467 --
468 chk_regn_admin_cd
469 (p_regn_for_regy_body_id => p_rec.regn_for_regy_body_id,
470 p_regn_admin_cd => p_rec.regn_admin_cd,
471 p_effective_date => p_effective_date,
472 p_object_version_number => p_rec.object_version_number);
473 --
474 chk_organization_id(
475 p_regn_for_regy_body_id => p_rec.regn_for_regy_body_id,
476 p_regn_id => p_rec.regn_id,
477 p_organization_id => p_rec.organization_id,
478 p_effective_date => p_effective_date,
479 p_validation_start_date => p_validation_start_date,
480 p_validation_end_date => p_validation_end_date,
481 p_business_group_id => p_rec.business_group_id,
482 p_object_version_number => p_rec.object_version_number);
483 --
484 hr_utility.set_location(' Leaving:'||l_proc, 10);
485 End insert_validate;
486 --
487 -- ----------------------------------------------------------------------------
488 -- |---------------------------< update_validate >----------------------------|
489 -- ----------------------------------------------------------------------------
490 Procedure update_validate
491 (p_rec in ben_rrb_shd.g_rec_type,
492 p_effective_date in date,
493 p_datetrack_mode in varchar2,
494 p_validation_start_date in date,
495 p_validation_end_date in date) is
496 --
497 l_proc varchar2(72) := g_package||'update_validate';
498 --
499 Begin
500 hr_utility.set_location('Entering:'||l_proc, 5);
501 --
502 -- Call all supporting business operations
503 --
504 --
505 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
506 --
507 chk_regn_for_regy_body_id
508 (p_regn_for_regy_body_id => p_rec.regn_for_regy_body_id,
509 p_effective_date => p_effective_date,
510 p_object_version_number => p_rec.object_version_number);
511 --
512 chk_regn_admin_cd
513 (p_regn_for_regy_body_id => p_rec.regn_for_regy_body_id,
514 p_regn_admin_cd => p_rec.regn_admin_cd,
515 p_effective_date => p_effective_date,
516 p_object_version_number => p_rec.object_version_number);
517 --
518 --
519 chk_organization_id(
520 p_regn_for_regy_body_id => p_rec.regn_for_regy_body_id,
521 p_regn_id => p_rec.regn_id,
522 p_organization_id => p_rec.organization_id,
523 p_effective_date => p_effective_date,
524 p_validation_start_date => p_validation_start_date,
525 p_validation_end_date => p_validation_end_date,
526 p_business_group_id => p_rec.business_group_id,
527 p_object_version_number => p_rec.object_version_number);
528 --
529 -- Call the datetrack update integrity operation
530 --
531 dt_update_validate
532 (p_regn_id => p_rec.regn_id,
533 p_datetrack_mode => p_datetrack_mode,
534 p_validation_start_date => p_validation_start_date,
535 p_validation_end_date => p_validation_end_date);
536 --
537 hr_utility.set_location(' Leaving:'||l_proc, 10);
538 End update_validate;
539 --
540 -- ----------------------------------------------------------------------------
541 -- |---------------------------< delete_validate >----------------------------|
542 -- ----------------------------------------------------------------------------
543 Procedure delete_validate
544 (p_rec in ben_rrb_shd.g_rec_type,
545 p_effective_date in date,
546 p_datetrack_mode in varchar2,
550 l_proc varchar2(72) := g_package||'delete_validate';
547 p_validation_start_date in date,
548 p_validation_end_date in date) is
549 --
551 --
552 Begin
553 hr_utility.set_location('Entering:'||l_proc, 5);
554 --
555 -- Call all supporting business operations
556 --
557 dt_delete_validate
558 (p_datetrack_mode => p_datetrack_mode,
559 p_validation_start_date => p_validation_start_date,
560 p_validation_end_date => p_validation_end_date,
561 p_regn_for_regy_body_id => p_rec.regn_for_regy_body_id);
562 --
563 hr_utility.set_location(' Leaving:'||l_proc, 10);
564 End delete_validate;
565 --
566 --
567 -- ---------------------------------------------------------------------------
568 -- |---------------------< return_legislation_code >-------------------------|
569 -- ---------------------------------------------------------------------------
570 --
571 function return_legislation_code
572 (p_regn_for_regy_body_id in number) return varchar2 is
573 --
574 -- Declare cursor
575 --
576 cursor csr_leg_code is
577 select a.legislation_code
578 from per_business_groups a,
579 ben_regn_for_regy_body_f b
580 where b.regn_for_regy_body_id = p_regn_for_regy_body_id
581 and a.business_group_id = b.business_group_id;
582 --
583 -- Declare local variables
584 --
585 l_legislation_code varchar2(150);
586 l_proc varchar2(72) := g_package||'return_legislation_code';
587 --
588 begin
589 --
590 hr_utility.set_location('Entering:'|| l_proc, 10);
591 --
592 -- Ensure that all the mandatory parameter are not null
593 --
594 hr_api.mandatory_arg_error(p_api_name => l_proc,
595 p_argument => 'regn_for_regy_body_id',
596 p_argument_value => p_regn_for_regy_body_id);
597 --
598 open csr_leg_code;
599 --
600 fetch csr_leg_code into l_legislation_code;
601 --
602 if csr_leg_code%notfound then
603 --
604 close csr_leg_code;
605 --
606 -- The primary key is invalid therefore we must error
607 --
608 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
609 fnd_message.raise_error;
610 --
611 end if;
612 --
613 close csr_leg_code;
614 --
615 hr_utility.set_location(' Leaving:'|| l_proc, 20);
616 --
617 return l_legislation_code;
618 --
619 end return_legislation_code;
620 --
621 end ben_rrb_bus;