[Home] [Help]
PACKAGE BODY: APPS.BEN_SHR_BUS
Source
1 Package Body ben_shr_bus as
2 /* $Header: beshrrhi.pkb 120.0.12010000.2 2008/08/05 15:27:41 ubhat ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ben_shr_bus.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------< chk_schedd_hrs_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 -- schedd_hrs_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_schedd_hrs_rt_id(p_schedd_hrs_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_schedd_hrs_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_shr_shd.api_updating
49 (p_effective_date => p_effective_date,
50 p_schedd_hrs_rt_id => p_schedd_hrs_rt_id,
51 p_object_version_number => p_object_version_number);
52 --
53 if (l_api_updating
54 and nvl(p_schedd_hrs_rt_id,hr_api.g_number)
55 <> ben_shr_shd.g_old_rec.schedd_hrs_rt_id) then
56 --
57 -- raise error as PK has changed
58 --
59 ben_shr_shd.constraint_error('BEN_SCHEDD_HRS_RT_PK');
60 --
61 elsif not l_api_updating then
62 --
63 -- check if PK is null
64 --
65 if p_schedd_hrs_rt_id is not null then
66 --
67 -- raise error as PK is not null
68 --
69 ben_shr_shd.constraint_error('BEN_SCHEDD_HRS_RT_PK');
70 --
71 end if;
72 --
73 end if;
74 --
75 hr_utility.set_location('Leaving:'||l_proc, 10);
76 --
77 End chk_schedd_hrs_rt_id;
78 --
79 -- ----------------------------------------------------------------------------
80 -- |------< chk_freq_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 -- schedd_hrs_rt_id PK of record being inserted or updated.
91 -- freq_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_freq_cd(p_schedd_hrs_rt_id in number,
106 p_freq_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_freq_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_shr_shd.api_updating
118 (p_schedd_hrs_rt_id => p_schedd_hrs_rt_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_freq_cd
124 <> nvl(ben_shr_shd.g_old_rec.freq_cd,hr_api.g_varchar2)
125 or not l_api_updating)
126 and p_freq_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 => 'FREQUENCY',
132 p_lookup_code => p_freq_cd,
133 p_effective_date => p_effective_date) then
134 --
135 -- raise error as does not exist as lookup
136 --
137 hr_utility.set_message(801,'HR_LOOKUP_DOES_NOT_EXIST');
138 hr_utility.raise_error;
139 --
140 end if;
141 --
142 end if;
143 --
144 hr_utility.set_location('Leaving:'||l_proc,10);
145 --
146 end chk_freq_cd;
147
148
149
150
151 -- ---------------------------------------------------------------------------
152 -- |-----------------------< chk_dup_ordr_num >---------------------------|
153 -- ---------------------------------------------------------------------------
154 --
155 -- Description
156 -- Ensure that the Sequence Number is unique
157 -- within business_group
158 --
159 -- Pre Conditions
160 -- None.
161 --
162 -- In Parameters
163 -- p_schedd_hrs_rt_id
164 -- p_vrbl_rt_prfl_id
165 -- p_ordr_num Sequence Number
166 -- p_business_group_id
167 --
168 -- Post Success
169 -- Processing continues
170 --
171 -- Post Failure
172 -- Errors handled by the procedure
173 --
174 -- Access Status
175 -- Internal table handler use only
176 --
177 -- ----------------------------------------------------------------------------
178 Procedure chk_duplicate_ordr_num
179 ( p_schedd_hrs_rt_id in number
180 ,p_vrbl_rt_prfl_id in number
181 ,p_ordr_num in number
182 ,p_business_group_id in number)
183 is
184 l_proc varchar2(72) := g_package||'chk_duplicate_ordr_num';
185 l_dummy char(1);
186 cursor c1 is
187 select null
188 from ben_schedd_hrs_rt_f
189 where schedd_hrs_rt_id <> nvl(p_schedd_hrs_rt_id,hr_api.g_number)
190 and vrbl_rt_prfl_id = p_vrbl_rt_prfl_id
191 and ordr_num = p_ordr_num
192 and business_group_id = p_business_group_id;
193 --
194 Begin
195 hr_utility.set_location('Entering:'||l_proc, 5);
196 --
197 open c1;
198 fetch c1 into l_dummy;
199 if c1%found then
200 close c1;
201 fnd_message.set_name('BEN','BEN_91001_SEQ_NOT_UNIQUE');
202 fnd_message.raise_error;
203 end if;
204 close c1;
205 --
206 hr_utility.set_location('Leaving:'||l_proc, 15);
207 End chk_duplicate_ordr_num;
208 --
209
210 --
211 -- ----------------------------------------------------------------------------
212 -- |------< chk_dup_record >------|
213 -- ----------------------------------------------------------------------------
214 --
215 -- Description
216 -- This procedure is used to check that there is no duplicate record
217 --
218 -- Pre Conditions
219 -- None.
220 --
221 -- In Parameters
222 -- p_schedd_hrs_rt_id PK of record being inserted or updated.
223 -- p_freq_cd Value of lookup
224 -- p_vrbl_rt_prfl_id FK of the record
225 -- p_effective_date effective date
226 -- p_object_version_number Object version number of record being
227 -- inserted or updated.
228 -- p_business_group_id business_group_id of the record
229 -- p_validation_start_date validation_start_date of record
230 -- p_validation_end_date validation_end_date of record
231 --
232 -- Post Success
233 -- Processing continues
234 --
235 -- Post Failure
236 -- Error handled by procedure
237 --
238 -- Access Status
239 -- Internal table handler use only.
240 --
241 Procedure chk_dup_record
242 (p_schedd_hrs_rt_id in number,
243 p_hrs_num in number,
244 p_freq_cd in varchar2,
245 p_max_hrs_num in number,
246 p_schedd_hrs_rl in number,
247 p_vrbl_rt_prfl_id in number,
248 p_effective_date in date,
249 p_object_version_number in number,
250 p_business_group_id in number,
251 p_validation_start_date in date,
252 p_validation_end_date in date )
253 is
254 --
255 l_proc varchar2(72) := g_package||'chk_dup_record';
256 l_api_updating boolean;
257 l_exists varchar2(1);
258 --
259 cursor c_dup is
260 select null
261 from ben_schedd_hrs_rt_f
262 where nvl(hrs_num,-99999999999) = nvl(p_hrs_num,-99999999999)
263 and nvl(freq_cd,'freq_cd') = nvl(p_freq_cd,'freq_cd')
264 and nvl(max_hrs_num,-99999999999) = nvl(p_max_hrs_num,-99999999999)
265 and nvl(schedd_hrs_rl,-99999999999) = nvl(p_schedd_hrs_rl,-99999999999)
266 and vrbl_rt_prfl_id = p_vrbl_rt_prfl_id
267 and schedd_hrs_rt_id <> nvl(p_schedd_hrs_rt_id,hr_api.g_number)
268 and business_group_id + 0 = p_business_group_id
269 and p_validation_start_date <= effective_end_date
270 and p_validation_end_date >= effective_start_date;
271 --
272 BEGIN
273 --
274 hr_utility.set_location('Entering:'||l_proc, 5);
275 --
276 l_api_updating := ben_shr_shd.api_updating
277 (p_schedd_hrs_rt_id => p_schedd_hrs_rt_id,
278 p_effective_date => p_effective_date,
279 p_object_version_number => p_object_version_number);
280 --
281 if (l_api_updating
282 and ( p_hrs_num <> nvl(ben_shr_shd.g_old_rec.hrs_num,hr_api.g_number)
283 or p_max_hrs_num <> nvl(ben_shr_shd.g_old_rec.max_hrs_num,hr_api.g_number)
284 or p_freq_cd <> nvl(ben_shr_shd.g_old_rec.freq_cd,hr_api.g_varchar2)
285 or p_schedd_hrs_rl <> nvl(ben_shr_shd.g_old_rec.schedd_hrs_rl,hr_api.g_number) )
286 or not l_api_updating) then
287
288 open c_dup;
289 fetch c_dup into l_exists;
290 if c_dup%found then
291 close c_dup;
292 --
293 -- raise error as this Scheduled Hours criteria already exists for this profile
294 --
295 fnd_message.set_name('BEN', 'BEN_92992_DUPS_ROW');
296 fnd_message.set_token('VAR1','Scheduled Hours',TRUE);
297 fnd_message.set_token('VAR2','Variable Rate Profile',TRUE);
298 fnd_message.raise_error;
299 --
300 end if;
301 close c_dup;
302 --
303 end if;
304 hr_utility.set_location('Leaving:'||l_proc,10);
305 --
306 END chk_dup_record;
307 --
308
309 -- ----------------------------------------------------------------------------
310 -- |------< chk_excld_flag >------|
311 -- ----------------------------------------------------------------------------
312 --
313 -- Description
314 -- This procedure is used to check that the lookup value is valid.
315 --
316 -- Pre Conditions
317 -- None.
318 --
319 -- In Parameters
320 -- schedd_hrs_rt_id PK of record being inserted or updated.
321 -- excld_flag Value of lookup code.
322 -- effective_date effective date
323 -- object_version_number Object version number of record being
324 -- inserted or updated.
325 --
326 -- Post Success
327 -- Processing continues
328 --
329 -- Post Failure
330 -- Error handled by procedure
331 --
332 -- Access Status
333 -- Internal table handler use only.
334 --
335 Procedure chk_excld_flag(p_schedd_hrs_rt_id in number,
336 p_excld_flag in varchar2,
337 p_effective_date in date,
338 p_object_version_number in number) is
339 --
340 l_proc varchar2(72) := g_package||'chk_excld_flag';
341 l_api_updating boolean;
342 --
343 Begin
344 --
345 hr_utility.set_location('Entering:'||l_proc, 5);
346 --
347 l_api_updating := ben_shr_shd.api_updating
348 (p_schedd_hrs_rt_id => p_schedd_hrs_rt_id,
349 p_effective_date => p_effective_date,
350 p_object_version_number => p_object_version_number);
351 --
352 if (l_api_updating
353 and p_excld_flag
354 <> nvl(ben_shr_shd.g_old_rec.excld_flag,hr_api.g_varchar2)
355 or not l_api_updating) then
356 --
357 -- check if value of lookup falls within lookup type.
358 --
359 --
360 if hr_api.not_exists_in_hr_lookups
361 (p_lookup_type => 'YES_NO',
362 p_lookup_code => p_excld_flag,
363 p_effective_date => p_effective_date) then
364 --
365 -- raise error as does not exist as lookup
366 --
367 hr_utility.set_message(801,'HR_LOOKUP_DOES_NOT_EXIST');
368 hr_utility.raise_error;
369 --
370 end if;
371 --
372 end if;
373 --
374 hr_utility.set_location('Leaving:'||l_proc,10);
375 --
376 end chk_excld_flag;
377 --
378 -- ----------------------------------------------------------------------------
379 -- |--------------------------< dt_update_validate >--------------------------|
380 -- ----------------------------------------------------------------------------
381 -- {Start Of Comments}
382 --
383 -- Description:
384 -- This procedure is used for referential integrity of datetracked
385 -- parent entities when a datetrack update operation is taking place
386 -- and where there is no cascading of update defined for this entity.
387 --
388 -- Prerequisites:
389 -- This procedure is called from the update_validate.
390 --
391 -- In Parameters:
392 --
393 -- Post Success:
394 -- Processing continues.
395 --
396 -- Post Failure:
397 --
398 -- Developer Implementation Notes:
399 -- This procedure should not need maintenance unless the HR Schema model
400 -- changes.
401 --
402 -- Access Status:
403 -- Internal Row Handler Use Only.
404 --
405 -- {End Of Comments}
406 -- ----------------------------------------------------------------------------
407 Procedure dt_update_validate
408 (p_vrbl_rt_prfl_id in number default hr_api.g_number,
409 p_datetrack_mode in varchar2,
410 p_validation_start_date in date,
411 p_validation_end_date in date) Is
412 --
413 l_proc varchar2(72) := g_package||'dt_update_validate';
414 l_integrity_error Exception;
415 l_table_name all_tables.table_name%TYPE;
416 --
417 Begin
418 hr_utility.set_location('Entering:'||l_proc, 5);
419 --
420 -- Ensure that the p_datetrack_mode argument is not null
421 --
422 hr_api.mandatory_arg_error
423 (p_api_name => l_proc,
424 p_argument => 'datetrack_mode',
425 p_argument_value => p_datetrack_mode);
426 --
427 -- Only perform the validation if the datetrack update mode is valid
428 --
429 If (dt_api.validate_dt_upd_mode(p_datetrack_mode => p_datetrack_mode)) then
430 --
431 --
432 -- Ensure the arguments are not null
433 --
434 hr_api.mandatory_arg_error
435 (p_api_name => l_proc,
436 p_argument => 'validation_start_date',
437 p_argument_value => p_validation_start_date);
438 --
439 hr_api.mandatory_arg_error
440 (p_api_name => l_proc,
441 p_argument => 'validation_end_date',
442 p_argument_value => p_validation_end_date);
443 --
444 If ((nvl(p_vrbl_rt_prfl_id, hr_api.g_number) <> hr_api.g_number) and
445 NOT (dt_api.check_min_max_dates
446 (p_base_table_name => 'ben_vrbl_rt_prfl_f',
447 p_base_key_column => 'vrbl_rt_prfl_id',
448 p_base_key_value => p_vrbl_rt_prfl_id,
449 p_from_date => p_validation_start_date,
450 p_to_date => p_validation_end_date))) Then
451 l_table_name := 'ben_vrbl_rt_prfl_f';
452 Raise l_integrity_error;
453 End If;
454 --
455 End If;
456 --
457 hr_utility.set_location(' Leaving:'||l_proc, 10);
458 Exception
459 When l_integrity_error Then
460 --
461 -- A referential integrity check was violated therefore
462 -- we must error
463 --
464 hr_utility.set_message(801, 'HR_7216_DT_UPD_INTEGRITY_ERR');
465 hr_utility.set_message_token('TABLE_NAME', l_table_name);
466 hr_utility.raise_error;
467 When Others Then
468 --
469 -- An unhandled or unexpected error has occurred which
470 -- we must report
471 --
472 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
473 hr_utility.set_message_token('PROCEDURE', l_proc);
474 hr_utility.set_message_token('STEP','15');
475 hr_utility.raise_error;
476 End dt_update_validate;
477 --
478 -- ----------------------------------------------------------------------------
479 -- |--------------------------< dt_delete_validate >--------------------------|
480 -- ----------------------------------------------------------------------------
481 -- {Start Of Comments}
482 --
483 -- Description:
484 -- This procedure is used for referential integrity of datetracked
485 -- child entities when either a datetrack DELETE or ZAP is in operation
486 -- and where there is no cascading of delete defined for this entity.
487 -- For the datetrack mode of DELETE or ZAP we must ensure that no
488 -- datetracked child rows exist between the validation start and end
489 -- dates.
490 --
491 -- Prerequisites:
492 -- This procedure is called from the delete_validate.
493 --
494 -- In Parameters:
495 --
496 -- Post Success:
497 -- Processing continues.
498 --
499 -- Post Failure:
500 -- If a row exists by determining the returning Boolean value from the
501 -- generic dt_api.rows_exist function then we must supply an error via
502 -- the use of the local exception handler l_rows_exist.
503 --
504 -- Developer Implementation Notes:
505 -- This procedure should not need maintenance unless the HR Schema model
506 -- changes.
507 --
508 -- Access Status:
509 -- Internal Row Handler Use Only.
510 --
511 -- {End Of Comments}
512 -- ----------------------------------------------------------------------------
513 Procedure dt_delete_validate
514 (p_schedd_hrs_rt_id in number,
515 p_datetrack_mode in varchar2,
516 p_validation_start_date in date,
517 p_validation_end_date in date) Is
518 --
519 l_proc varchar2(72) := g_package||'dt_delete_validate';
520 l_rows_exist Exception;
521 l_table_name all_tables.table_name%TYPE;
522 --
523 Begin
524 hr_utility.set_location('Entering:'||l_proc, 5);
525 --
526 -- Ensure that the p_datetrack_mode argument is not null
527 --
528 hr_api.mandatory_arg_error
529 (p_api_name => l_proc,
530 p_argument => 'datetrack_mode',
531 p_argument_value => p_datetrack_mode);
532 --
533 -- Only perform the validation if the datetrack mode is either
534 -- DELETE or ZAP
535 --
536 If (p_datetrack_mode = 'DELETE' or
537 p_datetrack_mode = 'ZAP') then
538 --
539 --
540 -- Ensure the arguments are not null
541 --
542 hr_api.mandatory_arg_error
543 (p_api_name => l_proc,
544 p_argument => 'validation_start_date',
545 p_argument_value => p_validation_start_date);
546 --
547 hr_api.mandatory_arg_error
548 (p_api_name => l_proc,
549 p_argument => 'validation_end_date',
550 p_argument_value => p_validation_end_date);
551 --
552 hr_api.mandatory_arg_error
553 (p_api_name => l_proc,
554 p_argument => 'schedd_hrs_rt_id',
555 p_argument_value => p_schedd_hrs_rt_id);
556 --
557 --
558 --
559 End If;
560 --
561 hr_utility.set_location(' Leaving:'||l_proc, 10);
562 Exception
563 When l_rows_exist Then
564 --
565 -- A referential integrity check was violated therefore
566 -- we must error
567 --
568 hr_utility.set_message(801, 'HR_7215_DT_CHILD_EXISTS');
569 hr_utility.set_message_token('TABLE_NAME', l_table_name);
570 hr_utility.raise_error;
571 When Others Then
572 --
573 -- An unhandled or unexpected error has occurred which
574 -- we must report
575 --
576 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
577 hr_utility.set_message_token('PROCEDURE', l_proc);
578 hr_utility.set_message_token('STEP','15');
579 hr_utility.raise_error;
580 End dt_delete_validate;
581
582 -- Enhancements done to include new fields
583 -- ----------------------------------------------------------------------------
584 -- |---------------------------< check_min_or_max >-------------------------|
585 -- ----------------------------------------------------------------------------
586 -- {Start Of Comments}
587 --
588 -- Description:
589 -- To check that both Min Hours and Max Hours are not null.unless a rule is specified
590 -- Prerequisites:
591 -- None
592 -- In Parameters:
593 -- p_num_hrs - Min hours entered.
594 -- p_max_num_hrs - Max hours entered.
595 -- p_schedd_rl - 'Range of Scheduled Hours' Formula selected.
596 -- Post Success:
597 -- Processing continues
598 -- Post Failure:
599 -- Failure is handled by procedure.
600 -- Developer Implementation Notes:
601 -- If (either p_num_hrs or p_max_num_hrs is not null and p_schedd_rl is not null)
602 -- or (both p_num_hrs and p_max_num_hrs are null and p_schedd_rl is also null)
603 -- then raise an error.
604 -- Access Status:
605 -- Internal table handler use only.
606 -- {End Of Comments}
607 -- ----------------------------------------------------------------------------
608
609 Procedure check_min_or_max
610 (p_hrs_num in number,
611 p_max_hrs_num in number,
612 p_schedd_hrs_rl in number,
613 p_freq_cd in varchar2,
614 p_schedd_hrs_rt_id in number,
615 p_effective_date in date,
616 p_object_version_number in number)
617 is
618 l_proc varchar2(72) := g_package||'check_min_or_max';
619 l_api_updating boolean;
620 --
621 Begin
622 hr_utility.set_location('Entering:'||l_proc, 5);
623
624 l_api_updating := ben_shr_shd.api_updating
625 (p_schedd_hrs_rt_id => p_schedd_hrs_rt_id,
626 p_effective_date => p_effective_date,
627 p_object_version_number => p_object_version_number);
628 --
629 if (l_api_updating
630 and ( nvl(p_hrs_num,hr_api.g_number) <> nvl(ben_shr_shd.g_old_rec.hrs_num,hr_api.g_number)
631 or nvl(p_max_hrs_num,hr_api.g_number) <> nvl(ben_shr_shd.g_old_rec.max_hrs_num,hr_api.g_number)
632 or nvl(p_freq_cd,hr_api.g_varchar2) <> nvl(ben_shr_shd.g_old_rec.freq_cd,hr_api.g_varchar2)
633 or nvl(p_schedd_hrs_rl,hr_api.g_number) <> nvl(ben_shr_shd.g_old_rec.schedd_hrs_rl,hr_api.g_number) )
634 or not l_api_updating) then
635 --
636 if (p_schedd_hrs_rl is not null) then
637 if (p_hrs_num is not null) or (p_max_hrs_num is not null) then
638 fnd_message.set_name('BEN', 'BEN_93220_MIN_MAX_HRS_OR_RULE');
639 fnd_message.raise_error;
640 end if;
641 else
642 if (p_hrs_num is null) and (p_max_hrs_num is null) then
643 if (p_freq_cd is not null) then
644 fnd_message.set_name('BEN','BEN_93222_MIN_MAX_HRS_NOT_NULL');
645 fnd_message.raise_error;
646 else
647 fnd_message.set_name('BEN', 'BEN_93220_MIN_MAX_HRS_OR_RULE');
648 fnd_message.raise_error;
649 end if;
650 elsif (nvl(p_hrs_num,-99999999999) > nvl(p_max_hrs_num,9999999999999999999999)) then
651 fnd_message.set_name('BEN','BEN_91069_INVALID_MIN_MAX');
652 fnd_message.raise_error;
653 end if;
654 end if;
655 end if;
656 --
657 hr_utility.set_location(' Leaving:'||l_proc, 10);
658 End check_min_or_max;
659
660 -- ----------------------------------------------------------------------------
661 -- |---------------------------< match_formula_type >-------------------------|
662 -- ----------------------------------------------------------------------------
663 -- {Start Of Comments}
664 --
665 -- Description:
666 -- To check that the rule is of the correct type
667 -- Prerequisites:
668 -- None
669 -- In Parameters:
670 -- p_formula_id - formula_number of the corresponding rule
671 -- p_formula_type_id - formula_type required
672 -- p_effective_date - effective date
673 -- Returns:
674 -- boolean (true -> if the formula_type_id matches with the DB values
675 -- false -> otherwise)
676 -- Post Success:
677 -- processing continues.
678 -- Post Failure:
679 -- error handled by procedure
680 -- Developer Implementation Notes:
681 -- Select the rule type from DB based on the number provided.
682 -- if the rule type matches return true, else false
683 -- Access Status:
684 -- Internal table handler use only.
685 -- {End Of Comments}
686 -- ----------------------------------------------------------------------------
687
688 Function match_formula_type
689 (p_rl number,
690 p_formula_type_id number,
691 p_business_group_id number,
692 p_effective_date date)
693 Return Boolean
694 is
695 l_proc varchar2(72) := g_package||'match_formula_type';
696 l_formula_type_id ff_formula_types.formula_type_id%type;
697
698 cursor c_rule is
699 select ff.formula_type_id formula_type_id
700 from ff_formulas_f ff
701 where ff.formula_id = p_rl
702 and p_effective_date between ff.effective_start_date and ff.effective_end_date
703 and ff.business_group_id = p_business_group_id
704 and ff.formula_type_id = p_formula_type_id;
705 --
706 Begin
707 hr_utility.set_location(' Entering:'||l_proc, 10);
708 --
709 If (p_rl is null) then
710 return false;
711 End if;
712 --
713 Open c_rule;
714 Fetch c_rule into l_formula_type_id;
715 If (c_rule%notfound) then
716 Close c_rule;
717 return false;
718 End If;
719 Close c_rule;
720 --
721 hr_utility.set_location(' Leaving:'||l_proc, 10);
722 return true;
723 End match_formula_type;
724 --
725 -- ----------------------------------------------------------------------------
726 -- |---------------------------< check_schedd_hrs_rl >-------------------------|
727 -- ----------------------------------------------------------------------------
728 -- {Start Of Comments}
729 --
730 -- Description:
731 -- To check that the rule exists and is of the correct type
732 -- Prerequisites:
733 -- None
734 -- In Parameters:
735 -- p_schedd_hrs_rl - formula_number of the corresponding rule
736 -- p_effective_date - effective date
737 -- Post Success:
738 -- processing continues.
739 -- Post Failure:
740 -- error handled by procedure
741 -- Developer Implementation Notes:
742 -- Select the rule type from DB based on the number provided. If it is not -548 (hardcoded for 'Range of Scheduled Hours')
743 -- raise an error
744 -- Access Status:
745 -- Internal table handler use only.
746 -- {End Of Comments}
747 -- ----------------------------------------------------------------------------
748
749 Procedure check_schedd_hrs_rl
750 (p_schedd_hrs_rl in number,
751 p_schedd_hrs_rt_id in number,
752 p_business_group_id in number,
753 p_effective_date in date,
754 p_object_version_number in number) is
755
756 l_proc varchar2(72) := g_package||'check_schedd_hrs_rl';
757 l_rule_type ff_formula_types.formula_type_id%type;
758 l_api_updating boolean;
759
760 --
761 Begin
762 hr_utility.set_location('Entering:'||l_proc, 5);
763 --
764 l_api_updating := ben_shr_shd.api_updating
765 (p_schedd_hrs_rt_id => p_schedd_hrs_rt_id,
766 p_effective_date => p_effective_date,
767 p_object_version_number => p_object_version_number);
768 --
769 if (l_api_updating
770 and nvl(p_schedd_hrs_rl,hr_api.g_number) <> nvl(ben_shr_shd.g_old_rec.schedd_hrs_rl,hr_api.g_number)
771 or not l_api_updating) then
772 --
773 If (p_schedd_hrs_rl is not null) then
774 If (not match_formula_type
775 (p_rl => p_schedd_hrs_rl,
776 p_formula_type_id => -548,
777 p_business_group_id => p_business_group_id,
778 p_effective_date => p_effective_date) ) then /*HARDCODE THE RULE-TYPE HERE */
779 fnd_message.set_name('BEN', 'BEN_93221_SCH_HRS_RL_INVALID');
780 fnd_message.raise_error;
781 End if;
782 End If;
783 --
784 End If;
785 --
786 hr_utility.set_location(' Leaving:'||l_proc, 10);
787 End check_schedd_hrs_rl;
788 --
789 -- ----------------------------------------------------------------------------
790 -- |---------------------------< check_determination_cd >-------------------------|
791 -- ----------------------------------------------------------------------------
792 -- {Start Of Comments}
793 --
794 -- Description:
795 -- To check that the code is either null or is of the correct type
796 -- Prerequisites:
797 -- None
798 -- In Parameters:
799 -- p_determination_cd - Determination code entered
800 -- p_effective_date - Effective date
801 -- Post Success:
802 --
803 -- Post Failure:
804 --
805 -- Developer Implementation Notes:
806 --
807 -- Access Status:
808 -- Internal table handler use only.
809 -- {End Of Comments}
810 -- ----------------------------------------------------------------------------
811
812 Procedure check_determination_cd
813 (p_determination_cd varchar2,
814 p_schedd_hrs_rt_id in number,
815 p_effective_date in date,
816 p_object_version_number in number) is
817
818 l_proc varchar2(72) := g_package||'check_determination_cd';
819 l_api_updating boolean;
820
821 --
822 Begin
823 hr_utility.set_location('Entering:'||l_proc, 5);
824 --
825 l_api_updating := ben_shr_shd.api_updating
826 (p_schedd_hrs_rt_id => p_schedd_hrs_rt_id,
827 p_effective_date => p_effective_date,
828 p_object_version_number => p_object_version_number);
829 --
830 if (l_api_updating
831 and nvl(p_determination_cd,hr_api.g_varchar2) <> nvl(ben_shr_shd.g_old_rec.determination_cd,hr_api.g_varchar2)
832 or not l_api_updating) then
833 --
834 if (p_determination_cd is not null) then
835 if hr_api.not_exists_in_hr_lookups
836 (p_lookup_type => 'BEN_LOS_DET',
837 p_lookup_code => p_determination_cd,
838 p_effective_date => p_effective_date) then
839 --
840 -- raise error as does not exist as lookup
841 --
842 fnd_message.set_name('PAY','HR_LOOKUP_DOES_NOT_EXIST');
843 fnd_message.raise_error;
844 end if;
845 end if;
846 end if;
847
848 hr_utility.set_location(' Leaving:'||l_proc, 10);
849 End check_determination_cd;
850 --
851 -- ----------------------------------------------------------------------------
852 -- |---------------------------< check_determination_rl >-------------------------|
853 -- ----------------------------------------------------------------------------
854 -- {Start Of Comments}
855 --
856 -- Description:
857 -- To check that the rule is specified only when determination_cd
858 -- is 'RL' and that the Rule exists and is of the correct type
859 -- Prerequisites:
860 -- None
861 -- In Parameters:
862 -- p_determination_cd
863 -- p_determination_rl
864 -- p_effective_date
865 -- Post Success:
866 -- processing continues
867 -- Post Failure:
868 -- error handled by procedure
869 -- Developer Implementation Notes:
870 --
871 -- Access Status:
872 -- Internal table handler use only.
873 -- {End Of Comments}
874 -- ----------------------------------------------------------------------------
875
876 Procedure check_determination_rl
877 (p_determination_rl number,
878 p_determination_cd varchar2,
879 p_schedd_hrs_rt_id in number,
880 p_business_group_id in number,
881 p_effective_date in date,
882 p_object_version_number in number) is
883
884 l_proc varchar2(72) := g_package||'check_determination_rl';
885 l_api_updating boolean;
886
887 --
888 Begin
889 hr_utility.set_location('Entering:'||l_proc, 5);
890
891 l_api_updating := ben_shr_shd.api_updating
892 (p_schedd_hrs_rt_id => p_schedd_hrs_rt_id,
893 p_effective_date => p_effective_date,
894 p_object_version_number => p_object_version_number);
895 --
896 if (l_api_updating
897 and (nvl(p_determination_rl,hr_api.g_number) <> nvl(ben_shr_shd.g_old_rec.determination_rl,hr_api.g_number)
898 or nvl(p_determination_cd,hr_api.g_varchar2) <> nvl(ben_shr_shd.g_old_rec.determination_cd,hr_api.g_varchar2) )
899 or not l_api_updating) then
900 --
901 If (p_determination_cd = 'RL') then
902 If (p_determination_rl is null) then
903 fnd_message.set_name('BEN','BEN_91098_LOS_DET_RL_NULL');
904 fnd_message.raise_error;
905 elsif (not match_formula_type
906 (p_rl => p_determination_rl,
907 p_formula_type_id => -170,
908 p_business_group_id => p_business_group_id,
909 p_effective_date => p_effective_date) ) then /*HARDCODE THE RULE-TYPE HERE */
910 fnd_message.set_name('BEN','BEN_91066_INVLD_LOS_DET_RL');
911 fnd_message.raise_error;
912 End if;
913 Else
914 if (p_determination_rl is not null) then
915 fnd_message.set_name('BEN','BEN_91071_LOS_DET_RL_NOT_NULL');
916 fnd_message.raise_error;
917 end if;
918 End if;
919 End if;
920 hr_utility.set_location(' Leaving:'||l_proc, 10);
921 End check_determination_rl;
922 --
923 -- ----------------------------------------------------------------------------
924 -- |---------------------------< check_rounding_cd >-------------------------|
925 -- ----------------------------------------------------------------------------
926 -- {Start Of Comments}
927 --
928 -- Description:
929 -- To check that the code is either null or is of the correct type
930 -- Prerequisites:
931 -- None
932 -- In Parameters:
933 -- p_rounding_cd - Determination code entered
934 -- p_rounding_date - Effective date
935 -- Post Success:
936 -- Processing continues
937 -- Post Failure:
938 -- Error handled by procedure
939 -- Developer Implementation Notes:
940 --
941 -- Access Status:
942 -- Internal table handler use only.
943 -- {End Of Comments}
944 -- ----------------------------------------------------------------------------
945
946 Procedure check_rounding_cd
947 (p_rounding_cd varchar2,
948 p_schedd_hrs_rt_id in number,
949 p_effective_date in date,
950 p_object_version_number in number) is
951
952 l_proc varchar2(72) := g_package||'check_rounding_cd';
953 l_api_updating boolean;
954
955 --
956 Begin
957 hr_utility.set_location('Entering:'||l_proc, 5);
958 --
959 l_api_updating := ben_shr_shd.api_updating
960 (p_schedd_hrs_rt_id => p_schedd_hrs_rt_id,
961 p_effective_date => p_effective_date,
962 p_object_version_number => p_object_version_number);
963 --
964 if (l_api_updating
965 and nvl(p_rounding_cd,hr_api.g_varchar2) <> nvl(ben_shr_shd.g_old_rec.rounding_cd,hr_api.g_varchar2)
966 or not l_api_updating) then
967
968 if (p_rounding_cd is not null) then
969 if hr_api.not_exists_in_hr_lookups
970 (p_lookup_type => 'BEN_RNDG',
971 p_lookup_code => p_rounding_cd,
972 p_effective_date => p_effective_date) then
973 --
974 -- raise error as does not exist as lookup
975 --
976 fnd_message.set_name('PAY','HR_LOOKUP_DOES_NOT_EXIST');
977 fnd_message.raise_error;
978 end if;
979 end if;
980 End if;
981
982 hr_utility.set_location(' Leaving:'||l_proc, 10);
983
984 End check_rounding_cd;
985 --
986 -- ----------------------------------------------------------------------------
987 -- |---------------------------< check_rounding_rl >-------------------------|
988 -- ----------------------------------------------------------------------------
989 -- {Start Of Comments}
990 --
991 -- Description:
992 -- To check that the rule is specified only when rounding_cd
993 -- is 'RL' and that the Rule exists and is of the correct type
994 -- Prerequisites:
995 -- None
996 -- In Parameters:
997 -- p_rounding_cd
998 -- p_rounding_rl
999 -- p_effective_date
1000 -- Post Success:
1001 -- processing continues
1002 -- Post Failure:
1003 -- error handled by procedure
1004 -- Developer Implementation Notes:
1005 --
1006 -- Access Status:
1007 -- Internal table handler use only.
1008 -- {End Of Comments}
1009 -- ----------------------------------------------------------------------------
1010 Procedure check_rounding_rl
1011 (p_rounding_rl number,
1012 p_rounding_cd varchar2,
1013 p_schedd_hrs_rt_id in number,
1014 p_business_group_id in number,
1015 p_effective_date in date,
1016 p_object_version_number in number) is
1017 --
1018 l_proc varchar2(72) := g_package||'check_rounding_rl';
1019 l_api_updating boolean;
1020
1021 Begin
1022 hr_utility.set_location('Entering:'||l_proc, 5);
1023 --
1024 l_api_updating := ben_shr_shd.api_updating
1025 (p_schedd_hrs_rt_id => p_schedd_hrs_rt_id,
1026 p_effective_date => p_effective_date,
1027 p_object_version_number => p_object_version_number);
1028 --
1029 if (l_api_updating
1030 and (nvl(p_rounding_rl,hr_api.g_number) <> nvl(ben_shr_shd.g_old_rec.rounding_rl,hr_api.g_number)
1031 or nvl(p_rounding_cd,hr_api.g_varchar2) <> nvl(ben_shr_shd.g_old_rec.rounding_cd,hr_api.g_varchar2) )
1032 or not l_api_updating) then
1033
1034 If (p_rounding_cd = 'RL') then
1035 If (p_rounding_rl is null) then
1036 fnd_message.set_name('BEN','BEN_91733_RNDG_RULE');
1037 fnd_message.raise_error;
1038 Elsif (not match_formula_type
1039 (p_rl => p_rounding_rl,
1040 p_formula_type_id => -169,
1041 p_business_group_id => p_business_group_id,
1042 p_effective_date => p_effective_date) ) then /*HARDCODE THE RULE-TYPE HERE */
1043 fnd_message.set_name('BEN','BEN_91042_INVALID_RNDG_RL');
1044 fnd_message.raise_error;
1045 End if;
1046 Else
1047 if (p_rounding_rl is not null) then
1048 fnd_message.set_name('BEN','BEN_91043_RNDG_RL_NOT_NULL');
1049 fnd_message.raise_error;
1050 end if;
1051 End if;
1052 End if;
1053 --
1054 hr_utility.set_location(' Leaving:'||l_proc, 10);
1055 End check_rounding_rl;
1056 --
1057 -- Enhancements done to include new fields
1058 --
1059 -- ----------------------------------------------------------------------------
1060 -- |---------------------------< insert_validate >----------------------------|
1061 -- ----------------------------------------------------------------------------
1062 Procedure insert_validate
1063 (p_rec in ben_shr_shd.g_rec_type,
1064 p_effective_date in date,
1065 p_datetrack_mode in varchar2,
1066 p_validation_start_date in date,
1067 p_validation_end_date in date) is
1068 --
1069 l_proc varchar2(72) := g_package||'insert_validate';
1070 l_api_updating boolean;
1071
1072 --
1073 Begin
1074 hr_utility.set_location('Entering:'||l_proc, 5);
1075 --
1076 -- Call all supporting business operations
1077 --
1078 --
1079 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
1080 --
1081 chk_schedd_hrs_rt_id
1082 (p_schedd_hrs_rt_id => p_rec.schedd_hrs_rt_id,
1083 p_effective_date => p_effective_date,
1084 p_object_version_number => p_rec.object_version_number);
1085 --
1086 chk_freq_cd
1087 (p_schedd_hrs_rt_id => p_rec.schedd_hrs_rt_id,
1088 p_freq_cd => p_rec.freq_cd,
1089 p_effective_date => p_effective_date,
1090 p_object_version_number => p_rec.object_version_number);
1091 --
1092 chk_duplicate_ordr_num
1093 ( p_schedd_hrs_rt_id => p_rec.schedd_hrs_rt_id,
1094 p_vrbl_rt_prfl_id => p_rec.vrbl_rt_prfl_id,
1095 p_ordr_num => p_rec.ordr_num,
1096 p_business_group_id => p_rec.business_group_id);
1097
1098 --
1099 chk_dup_record
1100 (p_schedd_hrs_rt_id => p_rec.schedd_hrs_rt_id,
1101 p_hrs_num => p_rec.hrs_num,
1102 p_freq_cd => p_rec.freq_cd,
1103 p_max_hrs_num => p_rec.max_hrs_num,
1104 p_schedd_hrs_rl => p_rec.schedd_hrs_rl,
1105 p_vrbl_rt_prfl_id => p_rec.vrbl_rt_prfl_id,
1106 p_effective_date => p_effective_date,
1107 p_object_version_number => p_rec.object_version_number,
1108 p_business_group_id => p_rec.business_group_id,
1109 p_validation_start_date => p_validation_start_date,
1110 p_validation_end_date => p_validation_end_date);
1111 --
1112 chk_excld_flag
1113 (p_schedd_hrs_rt_id => p_rec.schedd_hrs_rt_id,
1114 p_excld_flag => p_rec.excld_flag,
1115 p_effective_date => p_effective_date,
1116 p_object_version_number => p_rec.object_version_number);
1117
1118 --
1119 check_min_or_max
1120 (p_hrs_num => p_rec.hrs_num,
1121 p_max_hrs_num => p_rec.max_hrs_num,
1122 p_schedd_hrs_rl => p_rec.schedd_hrs_rl,
1123 p_freq_cd => p_rec.freq_cd,
1124 p_schedd_hrs_rt_id => p_rec.schedd_hrs_rt_id,
1125 p_effective_date => p_effective_date,
1126 p_object_version_number => p_rec.object_version_number);
1127
1128
1129 --
1130 check_schedd_hrs_rl
1131 (p_schedd_hrs_rl => p_rec.schedd_hrs_rl,
1132 p_schedd_hrs_rt_id => p_rec.schedd_hrs_rt_id,
1133 p_business_group_id => p_rec.business_group_id,
1134 p_effective_date => p_effective_date,
1135 p_object_version_number => p_rec.object_version_number);
1136
1137 --
1138 check_determination_cd
1139 (p_determination_cd => p_rec.determination_cd,
1140 p_schedd_hrs_rt_id => p_rec.schedd_hrs_rt_id,
1141 p_effective_date => p_effective_date,
1142 p_object_version_number => p_rec.object_version_number);
1143 --
1144 check_determination_rl
1145 (p_determination_rl => p_rec.determination_rl,
1146 p_determination_cd => p_rec.determination_cd,
1147 p_schedd_hrs_rt_id => p_rec.schedd_hrs_rt_id,
1148 p_business_group_id => p_rec.business_group_id,
1149 p_effective_date => p_effective_date,
1150 p_object_version_number => p_rec.object_version_number);
1151
1152 --
1153 check_rounding_cd
1154 (p_rounding_cd => p_rec.rounding_cd,
1155 p_schedd_hrs_rt_id => p_rec.schedd_hrs_rt_id,
1156 p_effective_date => p_effective_date,
1157 p_object_version_number => p_rec.object_version_number);
1158 --
1159 check_rounding_rl
1160 (p_rounding_rl => p_rec.rounding_rl,
1161 p_rounding_cd => p_rec.rounding_cd,
1162 p_schedd_hrs_rt_id => p_rec.schedd_hrs_rt_id,
1163 p_business_group_id => p_rec.business_group_id,
1164 p_effective_date => p_effective_date,
1165 p_object_version_number => p_rec.object_version_number);
1166
1167
1168 --
1169 hr_utility.set_location(' Leaving:'||l_proc, 10);
1170
1171 End insert_validate;
1172 --
1173 -- ----------------------------------------------------------------------------
1174 -- |---------------------------< update_validate >----------------------------|
1175 -- ----------------------------------------------------------------------------
1176 Procedure update_validate
1177 (p_rec in ben_shr_shd.g_rec_type,
1178 p_effective_date in date,
1179 p_datetrack_mode in varchar2,
1180 p_validation_start_date in date,
1181 p_validation_end_date in date) is
1182 --
1183 l_proc varchar2(72) := g_package||'update_validate';
1184 --
1185 Begin
1186 hr_utility.set_location('Entering:'||l_proc, 5);
1187 --
1188 -- Call all supporting business operations
1189 --
1190 --
1191 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
1192 --
1193 chk_schedd_hrs_rt_id
1194 (p_schedd_hrs_rt_id => p_rec.schedd_hrs_rt_id,
1195 p_effective_date => p_effective_date,
1196 p_object_version_number => p_rec.object_version_number);
1197 --
1198 chk_freq_cd
1199 (p_schedd_hrs_rt_id => p_rec.schedd_hrs_rt_id,
1200 p_freq_cd => p_rec.freq_cd,
1201 p_effective_date => p_effective_date,
1202 p_object_version_number => p_rec.object_version_number);
1203 --
1204 chk_duplicate_ordr_num
1205 ( p_schedd_hrs_rt_id => p_rec.schedd_hrs_rt_id,
1206 p_vrbl_rt_prfl_id => p_rec.vrbl_rt_prfl_id,
1207 p_ordr_num => p_rec.ordr_num,
1208 p_business_group_id => p_rec.business_group_id);
1209 --
1210 chk_dup_record
1211 (p_schedd_hrs_rt_id => p_rec.schedd_hrs_rt_id,
1212 p_hrs_num => p_rec.hrs_num,
1213 p_freq_cd => p_rec.freq_cd,
1214 p_max_hrs_num => p_rec.max_hrs_num,
1215 p_schedd_hrs_rl => p_rec.schedd_hrs_rl,
1216 p_vrbl_rt_prfl_id => p_rec.vrbl_rt_prfl_id,
1217 p_effective_date => p_effective_date,
1218 p_object_version_number => p_rec.object_version_number,
1219 p_business_group_id => p_rec.business_group_id,
1220 p_validation_start_date => p_validation_start_date,
1221 p_validation_end_date => p_validation_end_date);
1222 --
1223 chk_excld_flag
1224 (p_schedd_hrs_rt_id => p_rec.schedd_hrs_rt_id,
1225 p_excld_flag => p_rec.excld_flag,
1226 p_effective_date => p_effective_date,
1227 p_object_version_number => p_rec.object_version_number);
1228 --
1229 --
1230 check_min_or_max
1231 (p_hrs_num => p_rec.hrs_num,
1232 p_max_hrs_num => p_rec.max_hrs_num,
1233 p_schedd_hrs_rl => p_rec.schedd_hrs_rl,
1234 p_freq_cd => p_rec.freq_cd,
1235 p_schedd_hrs_rt_id => p_rec.schedd_hrs_rt_id,
1236 p_effective_date => p_effective_date,
1237 p_object_version_number => p_rec.object_version_number);
1238
1239 --
1240 check_schedd_hrs_rl
1241 (p_schedd_hrs_rl => p_rec.schedd_hrs_rl,
1242 p_schedd_hrs_rt_id => p_rec.schedd_hrs_rt_id,
1243 p_business_group_id => p_rec.business_group_id,
1244 p_effective_date => p_effective_date,
1245 p_object_version_number => p_rec.object_version_number);
1246
1247 --
1248 check_determination_cd
1249 (p_determination_cd => p_rec.determination_cd,
1250 p_schedd_hrs_rt_id => p_rec.schedd_hrs_rt_id,
1251 p_effective_date => p_effective_date,
1252 p_object_version_number => p_rec.object_version_number);
1253 --
1254 check_determination_rl
1255 (p_determination_rl => p_rec.determination_rl,
1256 p_determination_cd => p_rec.determination_cd,
1257 p_schedd_hrs_rt_id => p_rec.schedd_hrs_rt_id,
1258 p_business_group_id => p_rec.business_group_id,
1259 p_effective_date => p_effective_date,
1260 p_object_version_number => p_rec.object_version_number);
1261 --
1262 check_rounding_cd
1263 (p_rounding_cd => p_rec.rounding_cd,
1264 p_schedd_hrs_rt_id => p_rec.schedd_hrs_rt_id,
1265 p_effective_date => p_effective_date,
1266 p_object_version_number => p_rec.object_version_number);
1267 --
1268 check_rounding_rl
1269 (p_rounding_rl => p_rec.rounding_rl,
1270 p_rounding_cd => p_rec.rounding_cd,
1271 p_schedd_hrs_rt_id => p_rec.schedd_hrs_rt_id,
1272 p_business_group_id => p_rec.business_group_id,
1273 p_effective_date => p_effective_date,
1274 p_object_version_number => p_rec.object_version_number);
1275 --
1276
1277 -- Call the datetrack update integrity operation
1278 --
1279 dt_update_validate
1280 (p_vrbl_rt_prfl_id => p_rec.vrbl_rt_prfl_id,
1281 p_datetrack_mode => p_datetrack_mode,
1282 p_validation_start_date => p_validation_start_date,
1283 p_validation_end_date => p_validation_end_date);
1284 --
1285
1286 hr_utility.set_location(' Leaving:'||l_proc, 10);
1287 End update_validate;
1288 --
1289 -- ----------------------------------------------------------------------------
1290 -- |---------------------------< delete_validate >----------------------------|
1291 -- ----------------------------------------------------------------------------
1292 Procedure delete_validate
1293 (p_rec in ben_shr_shd.g_rec_type,
1294 p_effective_date in date,
1295 p_datetrack_mode in varchar2,
1296 p_validation_start_date in date,
1297 p_validation_end_date in date) is
1298 --
1299 l_proc varchar2(72) := g_package||'delete_validate';
1300 --
1301 Begin
1302 hr_utility.set_location('Entering:'||l_proc, 5);
1303 --
1304 -- Call all supporting business operations
1305 --
1306 dt_delete_validate
1307 (p_datetrack_mode => p_datetrack_mode,
1308 p_validation_start_date => p_validation_start_date,
1309 p_validation_end_date => p_validation_end_date,
1310 p_schedd_hrs_rt_id => p_rec.schedd_hrs_rt_id);
1311 --
1312 hr_utility.set_location(' Leaving:'||l_proc, 10);
1313 End delete_validate;
1314 --
1315 --
1316 -- ---------------------------------------------------------------------------
1317 -- |---------------------< return_legislation_code >-------------------------|
1318 -- ---------------------------------------------------------------------------
1319 --
1320 function return_legislation_code
1321 (p_schedd_hrs_rt_id in number) return varchar2 is
1322 --
1323 -- Declare cursor
1324 --
1325 cursor csr_leg_code is
1326 select a.legislation_code
1327 from per_business_groups a,
1328 ben_schedd_hrs_rt_f b
1329 where b.schedd_hrs_rt_id = p_schedd_hrs_rt_id
1330 and a.business_group_id = b.business_group_id;
1331 --
1332 -- Declare local variables
1333 --
1334 l_legislation_code varchar2(150);
1335 l_proc varchar2(72) := g_package||'return_legislation_code';
1336 --
1337 begin
1338 --
1339 hr_utility.set_location('Entering:'|| l_proc, 10);
1340 --
1341 -- Ensure that all the mandatory parameter are not null
1342 --
1343 hr_api.mandatory_arg_error(p_api_name => l_proc,
1344 p_argument => 'schedd_hrs_rt_id',
1345 p_argument_value => p_schedd_hrs_rt_id);
1346 --
1347 open csr_leg_code;
1348 --
1349 fetch csr_leg_code into l_legislation_code;
1350 --
1351 if csr_leg_code%notfound then
1352 --
1353 close csr_leg_code;
1354 --
1355 -- The primary key is invalid therefore we must error
1356 --
1357 hr_utility.set_message(801,'HR_7220_INVALID_PRIMARY_KEY');
1358 hr_utility.raise_error;
1359 --
1360 end if;
1361 --
1362 close csr_leg_code;
1363 --
1364 hr_utility.set_location(' Leaving:'|| l_proc, 20);
1365 --
1366 return l_legislation_code;
1367 --
1368 end return_legislation_code;
1369 --
1370 end ben_shr_bus;