[Home] [Help]
PACKAGE BODY: APPS.BEN_CQR_BUS
Source
1 Package Body ben_cqr_bus as
2 /* $Header: becqrrhi.pkb 120.2 2006/03/30 23:57:08 gsehgal noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ben_cqr_bus.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------< chk_cbr_quald_bnf_rt_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 -- cbr_quald_bnf_rt_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_cbr_quald_bnf_rt_id(p_cbr_quald_bnf_rt_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_cbr_quald_bnf_rt_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_cqr_shd.api_updating
49 (p_effective_date => p_effective_date,
50 p_cbr_quald_bnf_rt_id => p_cbr_quald_bnf_rt_id,
51 p_object_version_number => p_object_version_number);
52 --
53 if (l_api_updating
54 and nvl(p_cbr_quald_bnf_rt_id,hr_api.g_number)
55 <> ben_cqr_shd.g_old_rec.cbr_quald_bnf_rt_id) then
56 --
57 -- raise error as PK has changed
58 --
59 ben_cqr_shd.constraint_error('BEN_CBR_QUALD_BNF_RT_F_PK');
60 --
61 elsif not l_api_updating then
62 --
63 -- check if PK is null
64 --
65 if p_cbr_quald_bnf_rt_id is not null then
66 --
67 -- raise error as PK is not null
68 --
69 ben_cqr_shd.constraint_error('BEN_CBR_QUALD_BNF_RT_F_PK');
70 --
71 end if;
72 --
73 end if;
74 --
75 hr_utility.set_location('Leaving:'||l_proc, 10);
76 --
77 End chk_cbr_quald_bnf_rt_id;
78
79 --
80 -- ----------------------------------------------------------------------------
81 -- |------< chk_dup_record >------|
82 -- ----------------------------------------------------------------------------
83 --
84 -- Description
85 -- This procedure is used to check that there is no duplicate record
86 --
87 -- Pre Conditions
88 -- None.
89 --
90 -- In Parameters
91 -- <> PK of record being inserted or updated.
92 -- <> Value of lookup code.
93 -- p_vrbl_rt_prfl_id FK of the record
94 -- p_effective_date effective date
95 -- p_object_version_number Object version number of record being
96 -- inserted or updated.
97 -- p_business_group_id business_group_id of the record
98 -- p_validation_start_date validation_start_date of record
99 -- p_validation_end_date validation_end_date of record
100 --
101 -- Post Success
102 -- Processing continues
103 --
104 -- Post Failure
105 -- Error handled by procedure
106 --
107 -- Access Status
108 -- Internal table handler use only.
109 --
110 Procedure chk_dup_record
111 (p_cbr_quald_bnf_rt_id in number,
112 p_ptip_id in number,
113 p_pgm_id in number,
114 p_quald_bnf_flag in varchar2,
115 p_vrbl_rt_prfl_id in number,
116 p_effective_date in date,
117 p_object_version_number in number,
118 p_business_group_id in number,
119 p_validation_start_date in date,
120 p_validation_end_date in date )
121 is
122 --
123 l_proc varchar2(72) := g_package||'chk_dup_record';
124 l_api_updating boolean;
125 l_exists varchar2(1);
126 --
127 cursor c_dup is
128 select null
129 from ben_cbr_quald_bnf_rt_f
130 where quald_bnf_flag = p_quald_bnf_flag
131 and (pgm_id = nvl(p_pgm_id,hr_api.g_number)
132 or
133 ptip_id = nvl(p_ptip_id,hr_api.g_number)
134 )
135 and vrbl_rt_prfl_id = p_vrbl_rt_prfl_id
136 and cbr_quald_bnf_rt_id <> nvl(p_cbr_quald_bnf_rt_id,hr_api.g_number)
137 and business_group_id + 0 = p_business_group_id
138 and p_validation_start_date <= effective_end_date
139 and p_validation_end_date >= effective_start_date;
140 --
141 BEGIN
142 --
143 hr_utility.set_location('Entering:'||l_proc, 5);
144 --
145 l_api_updating := ben_cqr_shd.api_updating
146 (p_cbr_quald_bnf_rt_id => p_cbr_quald_bnf_rt_id,
147 p_effective_date => p_effective_date,
148 p_object_version_number => p_object_version_number);
149 --
150 if (l_api_updating
151 and
152 (p_quald_bnf_flag <> nvl(ben_cqr_shd.g_old_rec.quald_bnf_flag,hr_api.g_varchar2) or
153 (nvl(p_pgm_id,hr_api.g_number) <> nvl(ben_cqr_shd.g_old_rec.pgm_id,hr_api.g_number)
154 or
155 nvl(p_ptip_id,hr_api.g_number) <> nvl(ben_cqr_shd.g_old_rec.ptip_id,hr_api.g_number)
156 )
157 )
158 or not l_api_updating) then
159
160 open c_dup;
161 fetch c_dup into l_exists;
162 if c_dup%found then
163 close c_dup;
164 --
165 -- raise error as this < > criteria already exists for this profile
166 --
167 fnd_message.set_name('BEN', 'BEN_92992_DUPS_ROW');
168 fnd_message.set_token('VAR1','Cobra qualified beneficiary criteria',TRUE);
169 fnd_message.set_token('VAR2','Variable Rate Profile',TRUE);
170 fnd_message.raise_error;
171 --
172 end if;
173 close c_dup;
174 --
175 end if;
176 hr_utility.set_location('Leaving:'||l_proc,10);
177 --
178 END chk_dup_record;
179
180
181 --
182 -- ----------------------------------------------------------------------------
183 -- |---------------------< chk_pgm_id >--------------------------------|
184 -- ----------------------------------------------------------------------------
185 --
186 -- Description
187 -- This procedure checks that a referenced foreign key actually exists
188 -- in the referenced table.
189 --
190 -- Pre-Conditions
191 -- None.
192 --
193 -- In Parameters
194 -- p_cbr_quald_bnf_rt_id PK
195 -- p_pgm_id ID of FK column
196 -- p_object_version_number object version number
197 --
198 -- Post Success
199 -- Processing continues
200 --
201 -- Post Failure
202 -- Error raised.
203 --
204 -- Access Status
205 -- Internal table handler use only.
206 --
207 Procedure chk_pgm_id (p_cbr_quald_bnf_rt_id in number,
208 p_pgm_id in number,
209 p_effective_date in date,
210 p_object_version_number in number) is
211 --
212 l_proc varchar2(72) := g_package||'chk_pgm_id';
213 l_api_updating boolean;
214 l_dummy varchar2(1);
215 --
216 cursor c1 is
217 select null
218 from ben_pgm_f a
219 where a.pgm_id = p_pgm_id;
220 --
221 Begin
222 --
223 hr_utility.set_location('Entering:'||l_proc,5);
224 --
225 l_api_updating := ben_cqr_shd.api_updating
226 (p_effective_date => p_effective_date,
227 p_cbr_quald_bnf_rt_id => p_cbr_quald_bnf_rt_id,
228 p_object_version_number => p_object_version_number);
229 --
230 if (l_api_updating
231 and nvl(p_pgm_id,hr_api.g_number)
232 <> nvl(ben_cqr_shd.g_old_rec.pgm_id,hr_api.g_number)
233 or not l_api_updating) and p_pgm_id is not null then
234 --
235 -- check if pgm_id value exists in ben_pgm_f table
236 --
237 open c1;
238 --
239 fetch c1 into l_dummy;
240 if c1%notfound then
241 --
242 close c1;
243 --
244 -- raise error as FK does not relate to PK in ben_pgm_f
245 -- table.
246 --
247 ben_cqr_shd.constraint_error('BEN_CBR_QUALD_BNF_RT_F_DT3');
248 --
249 end if;
250 --
251 close c1;
252 --
253 end if;
254 --
255 hr_utility.set_location('Leaving:'||l_proc,10);
256 --
257 End chk_pgm_id;
258 --
259 -- ----------------------------------------------------------------------------
260 -- |---------------------< chk_ptip_id >--------------------------------|
261 -- ----------------------------------------------------------------------------
262 --
263 -- Description
264 -- This procedure checks that a referenced foreign key actually exists
265 -- in the referenced table.
266 --
267 -- Pre-Conditions
268 -- None.
269 --
270 -- In Parameters
271 -- p_cbr_quald_bnf_rt_id PK
272 -- p_ptip_id ID of FK column
273 -- p_object_version_number object version number
274 --
275 -- Post Success
276 -- Processing continues
277 --
278 -- Post Failure
279 -- Error raised.
280 --
281 -- Access Status
282 -- Internal table handler use only.
283 --
284 Procedure chk_ptip_id(p_cbr_quald_bnf_rt_id in number,
285 p_ptip_id in number,
286 p_effective_date in date,
287 p_object_version_number in number) is
288 --
289 l_proc varchar2(72) := g_package||'chk_ptip_id';
290 l_api_updating boolean;
291 l_dummy varchar2(1);
292 --
293 cursor c1 is
294 select null
295 from ben_ptip_f a
296 where a.ptip_id = p_ptip_id;
297 --
298 Begin
299 --
300 hr_utility.set_location('Entering:'||l_proc,5);
301 --
302 l_api_updating := ben_cqr_shd.api_updating
303 (p_effective_date => p_effective_date,
304 p_cbr_quald_bnf_rt_id => p_cbr_quald_bnf_rt_id,
305 p_object_version_number => p_object_version_number);
306 --
307 if (l_api_updating
308 and nvl(p_ptip_id,hr_api.g_number)
309 <> nvl(ben_cqr_shd.g_old_rec.ptip_id,hr_api.g_number)
310 or not l_api_updating) and p_ptip_id is not null then
311 --
312 -- check if ptip_id value exists in ben_ptip_f table
313 --
314 open c1;
315 --
316 fetch c1 into l_dummy;
317 if c1%notfound then
318 --
319 close c1;
320 --
321 -- raise error as FK does not relate to PK in ben_ptip_f
322 -- table.
323 --
324 ben_cqr_shd.constraint_error('BEN_CBR_QUALD_BNF_RT_F_DT2');
325 --
326 end if;
327 --
328 close c1;
329 --
330 end if;
331 --
332 hr_utility.set_location('Leaving:'||l_proc,10);
333 --
334 End chk_ptip_id;
335 --
336 -- ----------------------------------------------------------------------------
337 -- |------< chk_quald_bnf_flag >------|
338 -- ----------------------------------------------------------------------------
339 --
340 -- Description
341 -- This procedure is used to check that the lookup value is valid.
342 --
343 -- Pre Conditions
344 -- None.
345 --
346 -- In Parameters
347 -- cbr_quald_bnf_rt_id PK of record being inserted or updated.
348 -- quald_bnf_flag Value of lookup code.
349 -- effective_date effective date
350 -- object_version_number Object version number of record being
351 -- inserted or updated.
352 --
353 -- Post Success
354 -- Processing continues
355 --
356 -- Post Failure
357 -- Error handled by procedure
358 --
359 -- Access Status
360 -- Internal table handler use only.
361 --
362 Procedure chk_quald_bnf_flag(p_cbr_quald_bnf_rt_id in number,
363 p_quald_bnf_flag in varchar2,
364 p_effective_date in date,
365 p_object_version_number in number) is
366 --
367 l_proc varchar2(72) := g_package||'chk_quald_bnf_flag';
368 l_api_updating boolean;
369 --
370 Begin
371 --
372 hr_utility.set_location('Entering:'||l_proc, 5);
373 --
374 l_api_updating := ben_cqr_shd.api_updating
375 (p_cbr_quald_bnf_rt_id => p_cbr_quald_bnf_rt_id,
376 p_effective_date => p_effective_date,
377 p_object_version_number => p_object_version_number);
378 --
379 if (l_api_updating
380 and p_quald_bnf_flag
381 <> nvl(ben_cqr_shd.g_old_rec.quald_bnf_flag,hr_api.g_varchar2)
382 or not l_api_updating) then
383 --
384 -- check if value of lookup falls within lookup type.
385 --
386 --
387 if hr_api.not_exists_in_hr_lookups
388 (p_lookup_type => 'YES_NO',
389 p_lookup_code => p_quald_bnf_flag,
390 p_effective_date => p_effective_date) then
391 --
392 -- raise error as does not exist as lookup
393 --
394 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
395 fnd_message.set_token('FIELD','p_quald_bnf_flag');
396 fnd_message.set_token('TYPE','YES_NO');
397 fnd_message.raise_error;
398 --
399 end if;
400 --
401 end if;
402 --
403 hr_utility.set_location('Leaving:'||l_proc,10);
404 --
405 end chk_quald_bnf_flag;
406 --
407 -- added for Bug 5078478 .. add this procedure to check the duplicate seq no
408 -- |--------------------< chk_duplicate_ordr_num >----------------------------|
409 -- ----------------------------------------------------------------------------
410 --
411 -- Description
412 --
413 -- Pre Conditions
414 -- None.
415 --
416 -- In Parameters
417 -- p_cbr_quald_bnf_rt_id
418 -- p_vrbl_rt_prfl_id
419 -- p_ordr_num
420 -- p_effective_date
421 -- p_business_group_id
422 --
423 -- Post Success
424 -- Processing continues
425 --
426 -- Post Failure
427 -- Errors handled by the procedure
428 --
429 -- Access Status
430 -- Internal table handler use only.
431 --
432 -- ----------------------------------------------------------------------------
433
434
435 procedure chk_duplicate_ordr_num
436 (p_vrbl_rt_prfl_id in number
437 ,p_cbr_quald_bnf_rt_id in number
438 ,p_ordr_num in number
439 ,p_validation_start_date in date
440 ,p_validation_end_date in date
441 ,p_business_group_id in number)
442 is
443 l_proc varchar2(72) := g_package||' chk_duplicate_ordr_num ';
444 l_dummy char(1);
445 cursor c1 is select null
446 from ben_cbr_quald_bnf_rt_f
447 where vrbl_rt_prfl_id = p_vrbl_rt_prfl_id
448 and cbr_quald_bnf_rt_id <> nvl(p_cbr_quald_bnf_rt_id ,-1)
449 --and p_effective_date between effective_start_date
450 -- and effective_end_date
451 and p_validation_start_date <= effective_end_date
452 and p_validation_end_date >= effective_start_date
453 and business_group_id + 0 = p_business_group_id
454 and ordr_num = p_ordr_num;
455 --
456 Begin
457 hr_utility.set_location('Entering:'||l_proc, 5);
458
459 --
460 open c1;
461 fetch c1 into l_dummy;
462 --
463 if c1%found then
464 fnd_message.set_name('BEN','BEN_91001_SEQ_NOT_UNIQUE');
465 fnd_message.raise_error;
466 end if;
467 close c1;
468 --
469 hr_utility.set_location('Leaving:'||l_proc, 15);
470 End chk_duplicate_ordr_num;
471
472 --
473 -- ----------------------------------------------------------------------------
474 -- |--------------------------< dt_update_validate >--------------------------|
475 -- ----------------------------------------------------------------------------
476 -- {Start Of Comments}
477 --
478 -- Description:
479 -- This procedure is used for referential integrity of datetracked
480 -- parent entities when a datetrack update operation is taking place
481 -- and where there is no cascading of update defined for this entity.
482 --
483 -- Prerequisites:
484 -- This procedure is called from the update_validate.
485 --
486 -- In Parameters:
487 --
488 -- Post Success:
489 -- Processing continues.
490 --
491 -- Post Failure:
492 --
493 -- Developer Implementation Notes:
494 -- This procedure should not need maintenance unless the HR Schema model
495 -- changes.
496 --
497 -- Access Status:
498 -- Internal Row Handler Use Only.
499 --
500 -- {End Of Comments}
501 -- ----------------------------------------------------------------------------
502 Procedure dt_update_validate
503 (p_vrbl_rt_prfl_id in number default hr_api.g_number,
504 p_pgm_id in number default hr_api.g_number,
505 p_ptip_id in number default hr_api.g_number,
506 p_datetrack_mode in varchar2,
507 p_validation_start_date in date,
508 p_validation_end_date in date) Is
509 --
510 l_proc varchar2(72) := g_package||'dt_update_validate';
511 l_integrity_error Exception;
512 l_table_name all_tables.table_name%TYPE;
513 --
514 Begin
515 hr_utility.set_location('Entering:'||l_proc, 5);
516 --
517 -- Ensure that the p_datetrack_mode argument is not null
518 --
519 hr_api.mandatory_arg_error
520 (p_api_name => l_proc,
521 p_argument => 'datetrack_mode',
522 p_argument_value => p_datetrack_mode);
523 --
524 -- Only perform the validation if the datetrack update mode is valid
525 --
526 If (dt_api.validate_dt_upd_mode(p_datetrack_mode => p_datetrack_mode)) then
527 --
528 --
529 -- Ensure the arguments are not null
530 --
531 hr_api.mandatory_arg_error
532 (p_api_name => l_proc,
533 p_argument => 'validation_start_date',
534 p_argument_value => p_validation_start_date);
535 --
536 hr_api.mandatory_arg_error
537 (p_api_name => l_proc,
538 p_argument => 'validation_end_date',
539 p_argument_value => p_validation_end_date);
540 --
541 If ((nvl(p_vrbl_rt_prfl_id, hr_api.g_number) <> hr_api.g_number) and
542 NOT (dt_api.check_min_max_dates
543 (p_base_table_name => 'ben_vrbl_rt_prfl_f',
544 p_base_key_column => 'vrbl_rt_prfl_id',
545 p_base_key_value => p_vrbl_rt_prfl_id,
546 p_from_date => p_validation_start_date,
547 p_to_date => p_validation_end_date))) Then
548 l_table_name := 'ben_vrbl_rt_prfl_f';
549 Raise l_integrity_error;
550 End If;
551 If ((nvl(p_pgm_id, hr_api.g_number) <> hr_api.g_number) and
552 NOT (dt_api.check_min_max_dates
553 (p_base_table_name => 'ben_pgm_f',
554 p_base_key_column => 'pgm_id',
555 p_base_key_value => p_pgm_id,
556 p_from_date => p_validation_start_date,
557 p_to_date => p_validation_end_date))) Then
558 l_table_name := 'ben_pgm_f';
559 Raise l_integrity_error;
560 End If;
561 If ((nvl(p_ptip_id, hr_api.g_number) <> hr_api.g_number) and
562 NOT (dt_api.check_min_max_dates
563 (p_base_table_name => 'ben_ptip_f',
564 p_base_key_column => 'ptip_id',
565 p_base_key_value => p_ptip_id,
566 p_from_date => p_validation_start_date,
567 p_to_date => p_validation_end_date))) Then
568 l_table_name := 'ben_ptip_f';
569 Raise l_integrity_error;
570 End If;
571 --
572 End If;
573 --
574 hr_utility.set_location(' Leaving:'||l_proc, 10);
575 Exception
576 When l_integrity_error Then
577 --
578 -- A referential integrity check was violated therefore
579 -- we must error
580 --
581 ben_utility.parent_integrity_error(p_table_name => l_table_name);
582 --
583 When Others Then
584 --
585 -- An unhandled or unexpected error has occurred which
586 -- we must report
587 --
588 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
589 fnd_message.set_token('PROCEDURE', l_proc);
590 fnd_message.set_token('STEP','15');
591 fnd_message.raise_error;
592 End dt_update_validate;
593 --
594 -- ----------------------------------------------------------------------------
595 -- |--------------------------< dt_delete_validate >--------------------------|
596 -- ----------------------------------------------------------------------------
597 -- {Start Of Comments}
598 --
599 -- Description:
600 -- This procedure is used for referential integrity of datetracked
601 -- child entities when either a datetrack DELETE or ZAP is in operation
602 -- and where there is no cascading of delete defined for this entity.
603 -- For the datetrack mode of DELETE or ZAP we must ensure that no
604 -- datetracked child rows exist between the validation start and end
605 -- dates.
606 --
607 -- Prerequisites:
608 -- This procedure is called from the delete_validate.
609 --
610 -- In Parameters:
611 --
612 -- Post Success:
613 -- Processing continues.
614 --
615 -- Post Failure:
616 -- If a row exists by determining the returning Boolean value from the
617 -- generic dt_api.rows_exist function then we must supply an error via
618 -- the use of the local exception handler l_rows_exist.
619 --
620 -- Developer Implementation Notes:
621 -- This procedure should not need maintenance unless the HR Schema model
622 -- changes.
623 --
624 -- Access Status:
625 -- Internal Row Handler Use Only.
626 --
627 -- {End Of Comments}
628 -- ----------------------------------------------------------------------------
629 Procedure dt_delete_validate
630 (p_cbr_quald_bnf_rt_id in number,
631 p_datetrack_mode in varchar2,
632 p_validation_start_date in date,
633 p_validation_end_date in date) Is
634 --
635 l_proc varchar2(72) := g_package||'dt_delete_validate';
636 l_rows_exist Exception;
637 l_table_name all_tables.table_name%TYPE;
638 --
639 Begin
640 hr_utility.set_location('Entering:'||l_proc, 5);
641 --
642 -- Ensure that the p_datetrack_mode argument is not null
643 --
644 hr_api.mandatory_arg_error
645 (p_api_name => l_proc,
646 p_argument => 'datetrack_mode',
647 p_argument_value => p_datetrack_mode);
648 --
649 -- Only perform the validation if the datetrack mode is either
650 -- DELETE or ZAP
651 --
652 If (p_datetrack_mode = 'DELETE' or
653 p_datetrack_mode = 'ZAP') then
654 --
655 --
656 -- Ensure the arguments are not null
657 --
658 hr_api.mandatory_arg_error
659 (p_api_name => l_proc,
660 p_argument => 'validation_start_date',
661 p_argument_value => p_validation_start_date);
662 --
663 hr_api.mandatory_arg_error
664 (p_api_name => l_proc,
665 p_argument => 'validation_end_date',
666 p_argument_value => p_validation_end_date);
667 --
668 hr_api.mandatory_arg_error
669 (p_api_name => l_proc,
670 p_argument => 'cbr_quald_bnf_rt_id',
671 p_argument_value => p_cbr_quald_bnf_rt_id);
672 --
673 --
674 --
675 End If;
676 --
677 hr_utility.set_location(' Leaving:'||l_proc, 10);
678 Exception
679 When l_rows_exist Then
680 --
681 -- A referential integrity check was violated therefore
682 -- we must error
683 --
684 fnd_message.set_name('PAY', 'HR_7215_DT_CHILD_EXISTS');
685 fnd_message.set_token('TABLE_NAME', l_table_name);
686 fnd_message.raise_error;
687 When Others Then
688 --
689 -- An unhandled or unexpected error has occurred which
690 -- we must report
691 --
692 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
693 fnd_message.set_token('PROCEDURE', l_proc);
694 fnd_message.set_token('STEP','15');
695 fnd_message.raise_error;
696 End dt_delete_validate;
697 --
698 -- ----------------------------------------------------------------------------
699 -- |---------------------------< insert_validate >----------------------------|
700 -- ----------------------------------------------------------------------------
701 Procedure insert_validate
702 (p_rec in ben_cqr_shd.g_rec_type,
703 p_effective_date in date,
704 p_datetrack_mode in varchar2,
705 p_validation_start_date in date,
706 p_validation_end_date in date) is
707 --
708 l_proc varchar2(72) := g_package||'insert_validate';
709 --
710 Begin
711 hr_utility.set_location('Entering:'||l_proc, 5);
712 --
713 -- Call all supporting business operations
714 --
715 --
716 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
717 --
718 chk_cbr_quald_bnf_rt_id
719 (p_cbr_quald_bnf_rt_id => p_rec.cbr_quald_bnf_rt_id,
720 p_effective_date => p_effective_date,
721 p_object_version_number => p_rec.object_version_number);
722 --
723 chk_pgm_id
724 (p_cbr_quald_bnf_rt_id => p_rec.cbr_quald_bnf_rt_id,
725 p_pgm_id => p_rec.pgm_id,
726 p_effective_date => p_effective_date,
727 p_object_version_number => p_rec.object_version_number);
728 --
729 chk_ptip_id
730 (p_cbr_quald_bnf_rt_id => p_rec.cbr_quald_bnf_rt_id,
731 p_ptip_id => p_rec.ptip_id,
732 p_effective_date => p_effective_date,
733 p_object_version_number => p_rec.object_version_number);
734 --
735 chk_quald_bnf_flag
736 (p_cbr_quald_bnf_rt_id => p_rec.cbr_quald_bnf_rt_id,
737 p_quald_bnf_flag => p_rec.quald_bnf_flag,
738 p_effective_date => p_effective_date,
739 p_object_version_number => p_rec.object_version_number);
740
741 chk_dup_record
742 (p_cbr_quald_bnf_rt_id => p_rec.cbr_quald_bnf_rt_id,
743 p_quald_bnf_flag => p_rec.quald_bnf_flag,
744 p_ptip_id => p_rec.ptip_id,
745 p_pgm_id => p_rec.pgm_id,
746 p_vrbl_rt_prfl_id => p_rec.vrbl_rt_prfl_id,
747 p_effective_date => p_effective_date,
748 p_object_version_number => p_rec.object_version_number,
749 p_business_group_id => p_rec.business_group_id,
750 p_validation_start_date => p_validation_start_date,
751 p_validation_end_date => p_validation_end_date);
752 --
753 -- added for Bug 5078478 .. add this procedure to check the duplicate seq no
754 chk_duplicate_ordr_num
755 (p_vrbl_rt_prfl_id => p_rec.vrbl_rt_prfl_id
756 ,p_cbr_quald_bnf_rt_id => p_rec.cbr_quald_bnf_rt_id
757 ,p_ordr_num => p_rec.ordr_num
758 ,p_validation_start_date => p_validation_start_date
759 ,p_validation_end_date => p_validation_end_date
760 ,p_business_group_id => p_rec.business_group_id);
761
762 --
763 hr_utility.set_location(' Leaving:'||l_proc, 10);
764 End insert_validate;
765 --
766 -- ----------------------------------------------------------------------------
767 -- |---------------------------< update_validate >----------------------------|
768 -- ----------------------------------------------------------------------------
769 Procedure update_validate
770 (p_rec in ben_cqr_shd.g_rec_type,
771 p_effective_date in date,
772 p_datetrack_mode in varchar2,
773 p_validation_start_date in date,
774 p_validation_end_date in date) is
775 --
776 l_proc varchar2(72) := g_package||'update_validate';
777 --
778 Begin
779 hr_utility.set_location('Entering:'||l_proc, 5);
780 --
781 -- Call all supporting business operations
782 --
783 --
784 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
785 --
786 chk_cbr_quald_bnf_rt_id
787 (p_cbr_quald_bnf_rt_id => p_rec.cbr_quald_bnf_rt_id,
788 p_effective_date => p_effective_date,
789 p_object_version_number => p_rec.object_version_number);
790 --
791 chk_pgm_id
792 (p_cbr_quald_bnf_rt_id => p_rec.cbr_quald_bnf_rt_id,
793 p_pgm_id => p_rec.pgm_id,
794 p_effective_date => p_effective_date,
795 p_object_version_number => p_rec.object_version_number);
796 --
797 chk_ptip_id
798 (p_cbr_quald_bnf_rt_id => p_rec.cbr_quald_bnf_rt_id,
799 p_ptip_id => p_rec.ptip_id,
800 p_effective_date => p_effective_date,
801 p_object_version_number => p_rec.object_version_number);
802 --
803 chk_quald_bnf_flag
804 (p_cbr_quald_bnf_rt_id => p_rec.cbr_quald_bnf_rt_id,
805 p_quald_bnf_flag => p_rec.quald_bnf_flag,
806 p_effective_date => p_effective_date,
807 p_object_version_number => p_rec.object_version_number);
808
809 chk_dup_record
810 (p_cbr_quald_bnf_rt_id => p_rec.cbr_quald_bnf_rt_id,
811 p_quald_bnf_flag => p_rec.quald_bnf_flag,
812 p_ptip_id => p_rec.ptip_id,
813 p_pgm_id => p_rec.pgm_id,
814 p_vrbl_rt_prfl_id => p_rec.vrbl_rt_prfl_id,
815 p_effective_date => p_effective_date,
816 p_object_version_number => p_rec.object_version_number,
817 p_business_group_id => p_rec.business_group_id,
818 p_validation_start_date => p_validation_start_date,
819 p_validation_end_date => p_validation_end_date);
820 --
821 -- added for Bug 5078478 .. add this procedure to check the duplicate seq no
822 chk_duplicate_ordr_num
823 (p_vrbl_rt_prfl_id => p_rec.vrbl_rt_prfl_id
824 ,p_cbr_quald_bnf_rt_id => p_rec.cbr_quald_bnf_rt_id
825 ,p_ordr_num => p_rec.ordr_num
826 ,p_validation_start_date => p_validation_start_date
827 ,p_validation_end_date => p_validation_end_date
828 ,p_business_group_id => p_rec.business_group_id);
829 --
830 -- Call the datetrack update integrity operation
831 --
832 dt_update_validate
833 (p_vrbl_rt_prfl_id => p_rec.vrbl_rt_prfl_id,
834 p_pgm_id => p_rec.pgm_id,
835 p_ptip_id => p_rec.ptip_id,
836 p_datetrack_mode => p_datetrack_mode,
837 p_validation_start_date => p_validation_start_date,
838 p_validation_end_date => p_validation_end_date);
839 --
840 hr_utility.set_location(' Leaving:'||l_proc, 10);
841 End update_validate;
842 --
843 -- ----------------------------------------------------------------------------
844 -- |---------------------------< delete_validate >----------------------------|
845 -- ----------------------------------------------------------------------------
846 Procedure delete_validate
847 (p_rec in ben_cqr_shd.g_rec_type,
848 p_effective_date in date,
849 p_datetrack_mode in varchar2,
850 p_validation_start_date in date,
851 p_validation_end_date in date) is
852 --
853 l_proc varchar2(72) := g_package||'delete_validate';
854 --
855 Begin
856 hr_utility.set_location('Entering:'||l_proc, 5);
857 --
858 -- Call all supporting business operations
859 --
860 dt_delete_validate
861 (p_datetrack_mode => p_datetrack_mode,
862 p_validation_start_date => p_validation_start_date,
863 p_validation_end_date => p_validation_end_date,
864 p_cbr_quald_bnf_rt_id => p_rec.cbr_quald_bnf_rt_id);
865 --
866 hr_utility.set_location(' Leaving:'||l_proc, 10);
867 End delete_validate;
868 --
869 --
870 -- ---------------------------------------------------------------------------
871 -- |---------------------< return_legislation_code >-------------------------|
872 -- ---------------------------------------------------------------------------
873 --
874 function return_legislation_code
875 (p_cbr_quald_bnf_rt_id in number) return varchar2 is
876 --
877 -- Declare cursor
878 --
879 cursor csr_leg_code is
880 select a.legislation_code
881 from per_business_groups a,
882 ben_cbr_quald_bnf_rt_f b
883 where b.cbr_quald_bnf_rt_id = p_cbr_quald_bnf_rt_id
884 and a.business_group_id = b.business_group_id;
885 --
886 -- Declare local variables
887 --
888 l_legislation_code varchar2(150);
889 l_proc varchar2(72) := g_package||'return_legislation_code';
890 --
891 begin
892 --
893 hr_utility.set_location('Entering:'|| l_proc, 10);
894 --
895 -- Ensure that all the mandatory parameter are not null
896 --
897 hr_api.mandatory_arg_error(p_api_name => l_proc,
898 p_argument => 'cbr_quald_bnf_rt_id',
899 p_argument_value => p_cbr_quald_bnf_rt_id);
900 --
901 open csr_leg_code;
902 --
903 fetch csr_leg_code into l_legislation_code;
904 --
905 if csr_leg_code%notfound then
906 --
907 close csr_leg_code;
908 --
909 -- The primary key is invalid therefore we must error
910 --
911 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
912 fnd_message.raise_error;
913 --
914 end if;
915 --
916 close csr_leg_code;
917 --
918 hr_utility.set_location(' Leaving:'|| l_proc, 20);
919 --
920 return l_legislation_code;
921 --
922 end return_legislation_code;
923 --
924 end ben_cqr_bus;