4 --
1 Package Body per_vgr_bus as
2 /* $Header: pevgrrhi.pkb 120.0.12010000.3 2008/11/17 13:51:24 varanjan ship $ */
3 --
5 -- ----------------------------------------------------------------------------
6 -- | Private Global Definitions |
7 -- ----------------------------------------------------------------------------
8 --
9 g_package varchar2(33) := ' per_vgr_bus.'; -- Global package name
10 --
11 --
12 -- The following two global variables are only to be used by the
13 -- return_legislation_code function.
14 --
15 g_valid_grade_id number default null;
16 g_legislation_code varchar2(150) default null;
17 -- ----------------------------------------------------------------------------
18 -- |---------------------------< chk_grade_id >----------------------------|
19 -- ----------------------------------------------------------------------------
20 --
21 -- Description :
22 -- Validates that a grade id exists in the table per_grades
23 --
24 -- Validates that the business group id for the grade id is the same
25 -- as that for the valid grade.
26 -- Pre-conditions:
27 -- None
28 --
29 -- In Arguments :
30 -- p_grade_id
31 --
32 -- Post Success :
33 -- If a row does exist in per_grades for the given grade id then
34 -- processing continues
35 --
36 -- Post Failure :
37 -- If a row does not exist in per_grades for the given grade id then
38 -- an application error will be raised and processing is terminated
39 --
40 -- Access Status :
41 -- Internal Table Handler Use only.
42 --
43 -- {End of Comments}
44 -- ---------------------------------------------------------------------------
45 procedure chk_grade_id
46 (p_grade_id in number,
47 p_business_group_id in number) is
48 --
49 l_exists varchar2(1);
50 l_proc varchar2(72) := g_package||'chk_grade_id';
51 l_business_group_id number(15);
52 --
53 cursor csr_valid_gra is
54 select gra.business_group_id
55 from per_grades gra
56 where gra.grade_id = p_grade_id;
57 --
58 begin
59 hr_utility.set_location('Entering: '|| l_proc, 1);
60 --
61 -- Check mandatory parameters have been set
62 --
63 hr_api.mandatory_arg_error
64 (p_api_name => l_proc,
65 p_argument => 'grade_id',
66 p_argument_value => p_grade_id
67 );
68 --
69 hr_api.mandatory_arg_error
70 (p_api_name => l_proc,
71 p_argument => 'business_group_id',
72 p_argument_value => p_business_group_id
73 );
74 hr_utility.set_location(l_proc, 2);
75 --
76 -- Check that the grade ID is linked to a valid grade on per_grades
77 --
78 open csr_valid_gra;
79 fetch csr_valid_gra into l_business_group_id;
80 if csr_valid_gra%notfound then
81 close csr_valid_gra;
82 hr_utility.set_message(801, 'HR_51082_GRADE_NOT_EXIST');
83 hr_utility.raise_error;
84 end if;
85 close csr_valid_gra;
86 --
87 hr_utility.set_location(l_proc, 3);
88 --
89 if l_business_group_id <> p_business_group_id then
90 hr_utility.set_message(801, 'HR_51083_GRADE_INVALID_BG');
91 hr_utility.raise_error;
92 end if;
93 hr_utility.set_location(' Leaving: '|| l_proc, 4);
94 end chk_grade_id;
95 --
96 -- ----------------------------------------------------------------------------
97 -- |---------------------------< chk_date_from >----------------------------|
98 -- ----------------------------------------------------------------------------
99 --
100 -- Description :
101 -- Validates that date_from is less than or equal to date to (may be null)
102 --
103 -- Validates that date from must be within the range specified by date_from
104 -- and date_to on per_grades for the grade_id.
105 --
106 -- Validates that date from is equal to or later than the effective_date of
107 -- hr_positions_f for the position_id if it is not null.
108 --
109 -- Validates that date from is equal to or later than the date_from of
110 -- per_jobs_v for the job_id if it is not null.
111 --
112 -- Pre-conditions:
113 -- Format of p_date_from and p_date_to must be correct
114 --
115 -- In Arguments :
116 -- p_grade_id
117 -- p_date_from
118 -- p_date_to
119 -- p_job_id
120 -- p_position_id
121 -- p_object_version_number
122 -- p_effective_date Added for Bug# 1760707
123 --
124 -- Post Success :
125 -- If the above business rules are satisfied then procesing continues.
126 --
127 -- Post Failure :
128 -- If the above business rules are violated then
129 -- an application error will be raised and processing is terminated
130 --
131 -- Access Status :
132 -- Internal Table Handler Use only.
133 --
134 -- {End of Comments}
135 -- ---------------------------------------------------------------------------
136 procedure chk_date_from
137 (p_valid_grade_id in number
138 ,p_grade_id in number
139 ,p_date_from in date
140 ,p_date_to in date
141 ,p_job_id in number
142 ,p_position_id in number
143 ,p_object_version_number in number
144 ,p_effective_date in date) -- Added for Bug# 1760707
145 is
146 --
147 l_exists varchar2(1);
148 l_proc varchar2(72) := g_package||'chk_date_from';
149 l_api_updating boolean;
150 --
151 cursor csr_chk_gra_dates is
152 select null
153 from per_grades gra
154 where gra.grade_id = p_grade_id
155 and p_date_from between gra.date_from
156 and nvl(gra.date_to, hr_api.g_eot);
157 --
158 cursor csr_chk_job_dates is
159 select null
160 from per_jobs_v job
161 where job.job_id = p_job_id
162 and p_date_from >= job.date_from;
163 --
164 -- Changes 12-Oct-99 SCNair (per_positions to hr_positions) Date tracked position req.
165 -- Changes 22-APR-02. replaced hr_positions with hr_positions_f and added effective_date
166 -- condition
167 cursor csr_chk_pos_dates is
168 select null
169 from hr_positions_f pos
170 where pos.position_id = p_position_id
171 and p_date_from >= pos.date_effective
172 and p_effective_date between pos.effective_start_date and pos.effective_end_date;
173 --
174 begin
175 hr_utility.set_location('Entering:'|| l_proc, 1);
176 --
177 -- Check mandatory parameters have been set
178 --
179 hr_api.mandatory_arg_error
180 (p_api_name => l_proc
181 ,p_argument =>'grade_id'
182 ,p_argument_value => p_grade_id
183 );
184 --
185 hr_api.mandatory_arg_error
186 (p_api_name => l_proc
187 ,p_argument => 'date_from'
188 ,p_argument_value => p_date_from
189 );
190 --
191 -- Only proceed with validation if :
192 -- a) The current g_old_rec is current and
193 -- b) The date_from value has changed
194 --
195 l_api_updating := per_vgr_shd.api_updating
196 (p_valid_grade_id => p_valid_grade_id
197 ,p_object_version_number => p_object_version_number);
198 --
199 if ((l_api_updating and per_vgr_shd.g_old_rec.date_from <> p_date_from) or
200 (NOT l_api_updating)) then
201 hr_utility.set_location(l_proc, 2);
202 --
203 -- Check that the date_from value is less than or equal to the date_to
204 -- value for the current record
205 --
206 if p_date_from > nvl(p_date_to, hr_api.g_eot) then
207 hr_utility.set_message(801, 'HR_51084_VGR_DATE_LESS');
208 hr_utility.raise_error;
209 end if;
210 hr_utility.set_location(l_proc, 3);
211 --
212 -- Check that date_from is within the range of the date_from and date_to
213 -- on per_grades for p_grade_id
214 --
215 open csr_chk_gra_dates;
216 fetch csr_chk_gra_dates into l_exists;
217 if csr_chk_gra_dates%notfound then
218 close csr_chk_gra_dates;
219 hr_utility.set_message(801, 'HR_51085_VGR_DATE_GRADE');
220 hr_utility.raise_error;
221 end if;
222 close csr_chk_gra_dates;
223 hr_utility.set_location(l_proc, 4);
224 --
225 -- Check that date_from is on or later than the date_from on per_jobs_v for
226 -- p_job_id
227 --
228 if p_job_id is not null then
229 open csr_chk_job_dates;
230 fetch csr_chk_job_dates into l_exists;
231 if csr_chk_job_dates%notfound then
232 close csr_chk_job_dates;
233 hr_utility.set_message(801, 'HR_51086_VGR_DATE_JOB');
234 hr_utility.raise_error;
235 end if;
236 close csr_chk_job_dates;
237 end if;
238 hr_utility.set_location(l_proc, 5);
239 --
240 -- Check that date_from is on or later than the effective_date on
241 -- hr_positions_f p_position_id
242 --
243 if p_position_id is not null then
244 open csr_chk_pos_dates;
245 fetch csr_chk_pos_dates into l_exists;
246 if csr_chk_pos_dates%notfound then
247 close csr_chk_pos_dates;
248 hr_utility.set_message(801, 'HR_51087_VGR_DATE_POS');
249 hr_utility.raise_error;
250 end if;
251 close csr_chk_pos_dates;
252 end if;
253 end if;
254 --
255 hr_utility.set_location(' Leaving:'|| l_proc, 6);
256 end chk_date_from;
257 --
258 -- ----------------------------------------------------------------------------
259 -- |---------------------------< chk_job_or_position_rule >-------------|
260 -- ----------------------------------------------------------------------------
261 --
262 -- Description :
263 -- Validates that if job_id is not null that position_id is null
264 -- or that if job_id is null that position_id is not null
265 --
266 -- Pre-conditions:
267 -- None
268 --
269 -- In Arguments :
270 -- p_job_id
271 -- p_position_id
272 --
273 -- Post Success :
274 -- If job_id is not null and position_id is null or
275 -- job_id is null and position_id is not null then
276 -- processing continues
277 --
278 -- Post Failure :
279 -- if job_id is not null and position_id is not null then
280 -- an application error will be raised and processing is terminated
281 --
282 -- Access Status :
283 -- Internal Table Handler Use only.
284 --
285 -- {End of Comments}
286 -- ---------------------------------------------------------------------------
287 procedure chk_job_or_position_rule
288 (p_job_id in number
289 ,p_position_id in number
290 ) is
291 --
292 l_exists varchar2(1);
293 l_proc varchar2(72) := g_package||'chk_job_or_position_rule';
294 --
295 --
296 begin
297 hr_utility.set_location('Entering: '|| l_proc, 1);
298 --
299 if p_job_id is not null and p_position_id is not null then
300 hr_utility.set_message(801, 'HR_51088_VGR_JOB_OR_POS');
301 hr_utility.raise_error;
302 elsif p_job_id is null and p_position_id is null then
303 hr_utility.set_message(801, 'HR_51089_VGR_INV_JOB_OR_POS');
304 hr_utility.raise_error;
305 end if;
306 --
307 hr_utility.set_location('Leaving: '|| l_proc, 1);
308 end chk_job_or_position_rule;
309 --
310 -- ----------------------------------------------------------------------------
311 -- |---------------------------< chk_job_id >----------------------------|
312 -- ----------------------------------------------------------------------------
313 --
314 -- Description :
315 -- Validates that a job id exists in the view per_jobs_v
316 --
317 -- Validates that the business group id for the job is the same as that
318 -- for the valid grade.
319 --
320 -- Validates that the combination of grade_id and job_id does not already
321 -- exist on per_valid_grades.
322 --
323 -- Pre-conditions:
324 -- None
325 --
326 -- In Arguments :
327 -- p_job_id
328 --
329 -- Post Success :
330 -- If a row does exist in per_jobs_v for the given job id then
331 -- processing continues
332 --
333 -- Post Failure :
334 -- if a row does not exist in per_jobs_v for the given job_id then
335 -- an application error will be raised and processing is terminated
336 --
337 -- Access Status :
338 -- Internal Table Handler Use only.
339 --
340 -- {End of Comments}
341 -- ---------------------------------------------------------------------------
342 procedure chk_job_id
343 (p_job_id in number,
344 p_business_group_id in number,
345 p_grade_id in number,
346 p_date_from in date, -- Added For Bug # 6983587
347 p_date_to in date) is -- Added For Bug # 6983587
348 --
349 l_exists varchar2(1);
350 l_proc varchar2(72) := g_package||'chk_job_id';
351 l_business_group_id number(15);
352 --
353 cursor csr_valid_job is
354 select job.business_group_id
355 from per_jobs_v job
356 where job.job_id = p_job_id;
357 --
358 cursor csr_chk_job_grd_comb is
359 select null
360 from per_valid_grades vgr
361 where vgr.job_id = p_job_id
362 and vgr.grade_id = p_grade_id
363 -- Fix For Bug # 6983587 Starts
364 and
365 (
366 (p_date_from between vgr.date_from and nvl(vgr.date_to,hr_api.g_eot)
367 or
368 p_date_to between vgr.date_from and nvl(vgr.date_to,hr_api.g_eot)
369 )
370 or
371 (p_date_from < vgr.date_from and p_date_to > nvl(vgr.date_to,hr_api.g_eot))
372 );
373 -- Fix For Bug # 6983587 Ends
374 --
375 begin
376 hr_utility.set_location('Entering: '|| l_proc, 1);
377 --
378 --
379 -- Check that the job ID, if it is not null, is linked to a valid job on
380 -- per_jobs_v
381 --
382 hr_api.mandatory_arg_error
383 (p_api_name => l_proc,
384 p_argument => 'grade_id',
385 p_argument_value => p_grade_id
386 );
387 --
388 hr_api.mandatory_arg_error
389 (p_api_name => l_proc,
390 p_argument => 'business_group_id',
391 p_argument_value => p_business_group_iD
392 );
393 --
394 if p_job_id is not null then
395 open csr_valid_job;
396 fetch csr_valid_job into l_business_group_id;
397 if csr_valid_job %notfound then
398 close csr_valid_job;
399 hr_utility.set_message(801, 'HR_51090_JOB_NOT_EXIST');
400 hr_utility.raise_error;
401 end if;
402 close csr_valid_job;
403 --
404 hr_utility.set_location(l_proc, 2);
405 --
406 if l_business_group_id <> p_business_group_id then
407 hr_utility.set_message(801, 'HR_51091_JOB_INVALID_BG');
408 hr_utility.raise_error;
409 end if;
410 --
411 hr_utility.set_location(l_proc, 4);
412 --
413 open csr_chk_job_grd_comb;
414 fetch csr_chk_job_grd_comb into l_exists;
415 if csr_chk_job_grd_comb%found then
416 close csr_chk_job_grd_comb;
417 hr_utility.set_message(801, 'HR_51092_VGR_JOB_GRD_COMBO');
418 hr_utility.raise_error;
419 end if;
420 close csr_chk_job_grd_comb;
421 --
422 end if;
423 hr_utility.set_location(' Leaving: '|| l_proc, 10);
424 end chk_job_id;
425 --
426 -- ----------------------------------------------------------------------------
427 -- |---------------------------< chk_position_id >----------------------------|
428 -- ----------------------------------------------------------------------------
429 --
430 -- Description :
431 -- Validates that a position id exists in the table hr_positions_f
432 --
433 -- Validates that the business group id for the position is the same as
434 -- that for the valid grade.
435 --
436 -- Pre-conditions:
437 -- None
438 --
439 -- In Arguments :
440 -- p_position_id
441 --
442 -- Post Success :
443 -- If a row does exist in hr_positions_f for the given position id then
444 -- processing continues
445 --
446 -- Post Failure :
447 -- If a row does not exist in hr_positions_f for the given position id then
448 -- 0an application error will be raised and processing is terminatet
449 --
450 -- Access Status :
451 -- Internal Table Handler Use only.
452 --
453 -- {End of Comments}
454 -- ---------------------------------------------------------------------------
455 procedure chk_position_id
456 (p_position_id in number,
457 p_business_group_id in number,
458 p_grade_id in number,
459 p_effective_date in date, -- Effective_date added for Bug# 1760707
460 p_date_from in date, -- Added For Bug # 7516458
461 p_date_to in date) is -- Added For Bug # 7516458
462
463 --
464 l_exists varchar2(1);
465 l_proc varchar2(72) := g_package||'chk_position_id';
466 l_business_group_id number(15);
467 --
468 --
469 -- Changed 12-Oct-99 SCNair (per_positions to hr_positions) Date tracked position req
470 -- Changed 22-APR-02.hr_positions is replaced with hr_positions_f and added the
471 -- effective_date condition. Bug 1760707
472 cursor csr_valid_pos is
473 select pos.business_group_id
474 from hr_positions_f pos
475 where pos.position_id = p_position_id
476 and p_effective_date between pos.effective_start_date and pos.effective_end_date;
477 --
478 cursor csr_chk_pos_grd_comb is
479 select null
480 from per_valid_grades vgr
481 where vgr.position_id = p_position_id
482 and vgr.grade_id = p_grade_id
483 -- Fix For Bug # 7516458 Starts
484 and
485 (
486 ( p_date_from between vgr.date_from and nvl(vgr.date_to,hr_api.g_eot)
487 or
488 p_date_to between vgr.date_from and nvl(vgr.date_to,hr_api.g_eot))
489 or
490 (p_date_from < vgr.date_from and p_date_to > nvl(vgr.date_to,hr_api.g_eot))
491 );
492 -- Fix For Bug # 7516458 Ends
493
494 --
495 begin
496 hr_utility.set_location('Entering: '|| l_proc, 1);
497 --
498 --
499 hr_api.mandatory_arg_error
500 (p_api_name => l_proc,
501 p_argument => 'grade_id',
502 p_argument_value => p_grade_id
503 );
504 --
505 hr_api.mandatory_arg_error
506 (p_api_name => l_proc,
507 p_argument => 'business_group_id',
508 p_argument_value => p_business_group_iD
509 );
510 --
511 -- Check that the position ID , if it is not null, is linked to a valid
512 -- position on hr_positions_f
513 --
514 if p_position_id is not null then
515 open csr_valid_pos;
516 fetch csr_valid_pos into l_business_group_id;
517 if csr_valid_pos%notfound then
518 close csr_valid_pos;
519 hr_utility.set_message(801, 'HR_51093_POS_NOT_EXIST');
520 hr_utility.raise_error;
521 end if;
522 close csr_valid_pos;
523 --
524 hr_utility.set_location(l_proc, 2);
525 --
526 if l_business_group_id <> p_business_group_id then
527 hr_utility.set_message(801, 'HR_51094_POS_INVALID_BG');
528 hr_utility.raise_error;
529 end if;
530 --
531 hr_utility.set_location(l_proc, 3);
532 --
533 open csr_chk_pos_grd_comb;
534 fetch csr_chk_pos_grd_comb into l_exists;
535 if csr_chk_pos_grd_comb%found then
536 CLose csr_chk_pos_grd_comb;
537 hr_utility.set_message(801, 'HR_51095_VGR_POS_GRD_COMBO');
538 hr_utility.raise_error;
539 end if;
540 close csr_chk_pos_grd_comb;
541 --
542 end if;
543 hr_utility.set_location(' Leaving: '|| l_proc, 10);
544 end chk_position_id;
545 --
546 -- ----------------------------------------------------------------------------
547 -- |---------------------------< chk_date_to >----------------------------|
548 -- ----------------------------------------------------------------------------
549 --
550 -- Description :
551 -- Validates that date_to is greater than or equal to date from
552 --
553 -- Validates that date to must be within the range specified by date_from
554 -- and date_to on per_grades for the grade_id.
555 --
556 -- Validates that date to is equal to or earlier than the effective_date of
557 -- hr_positions_f for the position_id if it is not null.
558 --
559 -- Validates that date to is equal to or earlier than the date_to of
560 -- per_jobs_v for the job_id if it is not null.
561 --
562 -- Pre-conditions:
563 -- Format of p_date_from and p_date_to must be correct
564 --
565 -- In Arguments :
566 -- p_grade_id
567 -- p_date_to
568 -- p_date_from
569 -- p_job_id
570 -- p_position_id
571 -- p_object_version_number
572 --
573 -- Post Success :
574 -- If the above business rules are satisfied then procesing continues.
575 --
576 -- Post Failure :
577 -- If the above business rules are violated then
578 -- an application error will be raised and processing is terminated
579 --
580 -- Access Status :
581 -- Internal Table Handler Use only.
582 --
583 -- {End of Comments}
584 -- ---------------------------------------------------------------------------
585 procedure chk_date_to
586 (p_valid_grade_id in number
587 ,p_grade_id in number
588 ,p_date_from in date
589 ,p_date_to in date
590 ,p_job_id in number
591 ,p_position_id in number
592 ,p_object_version_number in number
593 ,p_effective_date in date) --Added for Bug#1760707
594 is
595 --
596 l_exists varchar2(1);
597 l_proc varchar2(72) := g_package||'chk_date_to';
598 l_api_updating boolean;
599 --
600 cursor csr_chk_gra_dates is
601 select null
602 from per_grades gra
603 where gra.grade_id = p_grade_id
604 and nvl(p_date_to, hr_api.g_eot) between gra.date_from
605 and nvl(gra.date_to, hr_api.g_eot);
606 --
607 cursor csr_chk_job_dates is
608 select null
609 from per_jobs_v job
610 where job.job_id = p_job_id
611 and nvl(p_date_to, hr_api.g_eot) <= nvl(job.date_to, hr_api.g_eot);
612 --
613 -- Changes 12-Oct-99 SCNair (per_postions to hr_positions) date tracked position req.
614 -- replaced hr_positions with hr_position_f.Added effective_date condition. Bug 1760707
615 --
616 cursor csr_chk_pos_dates is
617 select null
618 from hr_positions_f pos
619 where pos.position_id = p_position_id
620 and nvl(p_date_to, hr_api.g_eot) <= nvl(hr_general.get_position_date_end(p_position_id), hr_api.g_eot)
621 and p_effective_date between pos.effective_start_date and pos.effective_end_date;
622 --
623 begin
624 hr_utility.set_location('Entering:'|| l_proc, 1);
625 --
626 -- Check mandatory parameters havu been set
627 --
628 hr_api.mandatory_arg_error
629 (p_api_name => l_proc
630 ,p_argument => 'grade_id'
631 ,p_argument_value => p_grade_id
632 );
633 --
634 hr_api.mandatory_arg_error
635 (p_api_name => l_proc
636 ,p_argument => 'date_from'
637 ,p_argument_value => p_date_from
638 );
639 --
640 -- Only proceed with validation if :
641 -- a) The current g_old_rec is current and
642 -- b) The date_to value has changed
643 --
644 l_api_updating := per_vgr_shd.api_updating
645 (p_valid_grade_id => p_valid_grade_id
646 ,p_object_version_number => p_object_version_number);
647 --
648 if ((l_api_updating and
649 nvl(per_vgr_shd.g_old_rec.date_to, hr_api.g_eot) <>
650 nvl(p_date_to, hr_api.g_eot)) or
651 (NOT l_api_updating)) then
652 hr_utility.set_location(l_proc, 2);
653 --
654 -- Check that the date_from value is greater than or equal to the date_to
655 -- value for the current record
656 --
657 if p_date_from > nvl(p_date_to, hr_api.g_eot) then
658 hr_utility.set_message(801, 'HR_51096_VGR_DATE_GREATER');
659 hr_utility.raise_error;
660 end if;
661 hr_utility.set_location(l_proc, 3);
662 --
663 -- Check that date_to is within the range of the date_from and date_to
664 -- on per_grades for p_grade_id
665 --
666 open csr_chk_gra_dates;
667 fetch csr_chk_gra_dates into l_exists;
668 if csr_chk_gra_dates%notfound then
669 close csr_chk_gra_dates;
670 hr_utility.set_message(801, 'HR_51097_VGR_END_DATE_INVALID');
671 hr_utility.raise_error;
672 end if;
673 close csr_chk_gra_dates;
674 hr_utility.set_location(l_proc, 4);
675 --
676 -- Check that date_to is on or earlier than the date_to on per_jobs_v for
677 -- p_job_id
678 --
679 if p_job_id is not null then
680 open csr_chk_job_dates;
681 fetch csr_chk_job_dates into l_exists;
682 if csr_chk_job_dates%notfound then
683 close csr_chk_job_dates;
684 hr_utility.set_message(801, 'HR_51098_VGR_END_DATE_JOB');
685 hr_utility.raise_error;
686 end if;
687 close csr_chk_job_dates;
688 end if;
689 hr_utility.set_location(l_proc, 5);
690 --
691 -- Check that date_to is on or later than the end_date on
692 -- hr_positions_f p_position_id
693 --
694 if p_position_id is not null then
695 open csr_chk_pos_dates;
696 fetch csr_chk_pos_dates into l_exists;
697 if csr_chk_pos_dates%notfound then
698 close csr_chk_pos_dates;
699 hr_utility.set_message(801, 'HR_51099_VGR_END_DATE_POS');
700 hr_utility.raise_error;
701 end if;
702 close csr_chk_pos_dates;
703 end if;
704 end if;
705 --
706 hr_utility.set_location(' Leaving:'|| l_proc, 6);
707 end chk_date_to;
708 -- -----------------------------------------------------------------------
709 -- |------------------------------< chk_df >-----------------------------|
710 -- -----------------------------------------------------------------------
711 --
712 -- Description:
713 -- Validates the all Descriptive Flexfield values.
714 --
715 -- Pre-conditions:
716 -- All other columns have been validated. Must be called as the
717 -- last step from insert_validate and update_validate.
718 --
719 -- In Arguments:
720 -- p_rec
721 --
722 -- Post Success:
723 -- If the Descriptive Flexfield structure column and data values are
724 -- all valid this procedure will end normally and processing will
725 -- continue.
726 --
727 -- Post Failure:
728 -- If the Descriptive Flexfield structure column value or any of
729 -- the data values are invalid then an application error is raised as
730 -- a PL/SQL exception.
731 --
732 -- Access Status:
733 -- Internal Row Handler Use Only.
734 --
735 procedure chk_df
736 (p_rec in per_vgr_shd.g_rec_type) is
737 --
738 l_proc varchar2(72) := g_package||'chk_df';
739 --
740 begin
741 hr_utility.set_location('Entering:'||l_proc, 10);
742 --
743 if ((p_rec.valid_grade_id is not null) and (
744 nvl(per_vgr_shd.g_old_rec.attribute_category, hr_api.g_varchar2) <>
745 nvl(p_rec.attribute_category, hr_api.g_varchar2) or
746 nvl(per_vgr_shd.g_old_rec.attribute1, hr_api.g_varchar2) <>
747 nvl(p_rec.attribute1, hr_api.g_varchar2) or
748 nvl(per_vgr_shd.g_old_rec.attribute2, hr_api.g_varchar2) <>
749 nvl(p_rec.attribute2, hr_api.g_varchar2) or
750 nvl(per_vgr_shd.g_old_rec.attribute3, hr_api.g_varchar2) <>
751 nvl(p_rec.attribute3, hr_api.g_varchar2) or
752 nvl(per_vgr_shd.g_old_rec.attribute4, hr_api.g_varchar2) <>
753 nvl(p_rec.attribute4, hr_api.g_varchar2) or
754 nvl(per_vgr_shd.g_old_rec.attribute5, hr_api.g_varchar2) <>
755 nvl(p_rec.attribute5, hr_api.g_varchar2) or
756 nvl(per_vgr_shd.g_old_rec.attribute6, hr_api.g_varchar2) <>
757 nvl(p_rec.attribute6, hr_api.g_varchar2) or
758 nvl(per_vgr_shd.g_old_rec.attribute7, hr_api.g_varchar2) <>
759 nvl(p_rec.attribute7, hr_api.g_varchar2) or
760 nvl(per_vgr_shd.g_old_rec.attribute8, hr_api.g_varchar2) <>
761 nvl(p_rec.attribute8, hr_api.g_varchar2) or
762 nvl(per_vgr_shd.g_old_rec.attribute9, hr_api.g_varchar2) <>
763 nvl(p_rec.attribute9, hr_api.g_varchar2) or
764 nvl(per_vgr_shd.g_old_rec.attribute10, hr_api.g_varchar2) <>
765 nvl(p_rec.attribute10, hr_api.g_varchar2) or
766 nvl(per_vgr_shd.g_old_rec.attribute11, hr_api.g_varchar2) <>
767 nvl(p_rec.attribute11, hr_api.g_varchar2) or
768 nvl(per_vgr_shd.g_old_rec.attribute12, hr_api.g_varchar2) <>
769 nvl(p_rec.attribute12, hr_api.g_varchar2) or
770 nvl(per_vgr_shd.g_old_rec.attribute13, hr_api.g_varchar2) <>
771 nvl(p_rec.attribute13, hr_api.g_varchar2) or
772 nvl(per_vgr_shd.g_old_rec.attribute14, hr_api.g_varchar2) <>
773 nvl(p_rec.attribute14, hr_api.g_varchar2) or
774 nvl(per_vgr_shd.g_old_rec.attribute15, hr_api.g_varchar2) <>
775 nvl(p_rec.attribute15, hr_api.g_varchar2) or
776 nvl(per_vgr_shd.g_old_rec.attribute16, hr_api.g_varchar2) <>
777 nvl(p_rec.attribute16, hr_api.g_varchar2) or
778 nvl(per_vgr_shd.g_old_rec.attribute17, hr_api.g_varchar2) <>
779 nvl(p_rec.attribute17, hr_api.g_varchar2) or
780 nvl(per_vgr_shd.g_old_rec.attribute18, hr_api.g_varchar2) <>
781 nvl(p_rec.attribute18, hr_api.g_varchar2) or
782 nvl(per_vgr_shd.g_old_rec.attribute19, hr_api.g_varchar2) <>
783 nvl(p_rec.attribute19, hr_api.g_varchar2) or
784 nvl(per_vgr_shd.g_old_rec.attribute20, hr_api.g_varchar2) <>
785 nvl(p_rec.attribute20, hr_api.g_varchar2)))
786 or
787 (p_rec.valid_grade_id is null) then
788 --
789 -- Only execute the validation if absolutely necessary:
790 -- a) During update, the structure column value or any
791 -- of the attribute values have actually changed.
792 -- b) During insert.
793 --
794 hr_dflex_utility.ins_or_upd_descflex_attribs
795 (p_appl_short_name => 'PER'
796 ,p_descflex_name => 'PER_VALID_GRADES'
797 ,p_attribute_category => p_rec.attribute_category
798 ,p_attribute1_name => 'ATTRIBUTE1'
799 ,p_attribute1_value => p_rec.attribute1
800 ,p_attribute2_name => 'ATTRIBUTE2'
801 ,p_attribute2_value => p_rec.attribute2
802 ,p_attribute3_name => 'ATTRIBUTE3'
803 ,p_attribute3_value => p_rec.attribute3
804 ,p_attribute4_name => 'ATTRIBUTE4'
805 ,p_attribute4_value => p_rec.attribute4
806 ,p_attribute5_name => 'ATTRIBUTE5'
807 ,p_attribute5_value => p_rec.attribute5
808 ,p_attribute6_name => 'ATTRIBUTE6'
809 ,p_attribute6_value => p_rec.attribute6
810 ,p_attribute7_name => 'ATTRIBUTE7'
811 ,p_attribute7_value => p_rec.attribute7
812 ,p_attribute8_name => 'ATTRIBUTE8'
813 ,p_attribute8_value => p_rec.attribute8
814 ,p_attribute9_name => 'ATTRIBUTE9'
815 ,p_attribute9_value => p_rec.attribute9
816 ,p_attribute10_name => 'ATTRIBUTE10'
817 ,p_attribute10_value => p_rec.attribute10
818 ,p_attribute11_name => 'ATTRIBUTE11'
819 ,p_attribute11_value => p_rec.attribute11
820 ,p_attribute12_name => 'ATTRIBUTE12'
821 ,p_attribute12_value => p_rec.attribute12
822 ,p_attribute13_name => 'ATTRIBUTE13'
823 ,p_attribute13_value => p_rec.attribute13
824 ,p_attribute14_name => 'ATTRIBUTE14'
825 ,p_attribute14_value => p_rec.attribute14
826 ,p_attribute15_name => 'ATTRIBUTE15'
827 ,p_attribute15_value => p_rec.attribute15
828 ,p_attribute16_name => 'ATTRIBUTE16'
829 ,p_attribute16_value => p_rec.attribute16
830 ,p_attribute17_name => 'ATTRIBUTE17'
831 ,p_attribute17_value => p_rec.attribute17
832 ,p_attribute18_name => 'ATTRIBUTE18'
833 ,p_attribute18_value => p_rec.attribute18
834 ,p_attribute19_name => 'ATTRIBUTE19'
835 ,p_attribute19_value => p_rec.attribute19
836 ,p_attribute20_name => 'ATTRIBUTE20'
837 ,p_attribute20_value => p_rec.attribute20
838 );
839 end if;
840 --
841 hr_utility.set_location(' Leaving:'||l_proc, 20);
842
843 end chk_df;
844 --
845 -- ----------------------------------------------------------------------------
846 -- |---------------------------< insert_validate >----------------------------|
847 -- ----------------------------------------------------------------------------
848 Procedure insert_validate(p_rec in per_vgr_shd.g_rec_type,
849 p_effective_date in date) is -- Added for Bug# 1760707
850 --
851 l_proc varchar2(72) := g_package||'insert_validate';
852 --
853 Begin
854 hr_utility.set_location('Entering:'||l_proc, 5);
855 --
856 -- Call all supporting business operations. Mapping to the appropriate
857 -- Business Rules in pervga.bru is provided.
858 --
859 --
860 -- Validate Business Group id
861 --
862 hr_api.validate_bus_grp_id(p_rec.business_group_id);
863 --
864 hr_utility.set_location(l_proc, 10);
865 --
866 -- Validate Grade id
867 --
868 chk_grade_id (p_grade_id => p_rec.grade_id
869 ,p_business_group_id => p_rec.business_group_id );
870 --
871 hr_utility.set_location(l_proc, 15);
872 --
873 -- Validate that either Position or Job Ids are set
874 --
875 chk_job_or_position_rule
876 (p_job_id => p_rec.job_id
877 ,p_position_id => p_rec.position_id);
878 --
879 hr_utility.set_location(l_proc, 20);
880 --
881 -- Validate Job id
882 --
883 chk_job_id (p_job_id => p_rec.job_id
884 ,p_business_group_id => p_rec.business_group_id
885 ,p_grade_id => p_rec.grade_id
886 ,p_date_from => p_rec.date_from -- Added For Bug # 6983587
887 ,p_date_to => p_rec.date_to); -- Added For Bug 6983587
888 --
889 hr_utility.set_location(l_proc, 25);
890 --
891 -- Validate Position id
892 --
893 chk_position_id (p_position_id => p_rec.position_id
894 ,p_business_group_id => p_rec.business_group_id
895 ,p_grade_id => p_rec.grade_id
896 ,p_effective_date => p_effective_date -- Added for Bug# 1760707
897 ,p_date_from => p_rec.date_from -- Added For Bug # 7516458
898 ,p_date_to => p_rec.date_to); -- Added For Bug 7516458
899
900 --
901 hr_utility.set_location(l_proc, 30);
902 --
903 -- Validate Date From
904 --
905 chk_date_from
906 (p_valid_grade_id => p_rec.valid_grade_id
907 ,p_grade_id => p_rec.grade_id
908 ,p_date_from => p_rec.date_from
909 ,p_date_to => p_rec.date_to
910 ,p_job_id => p_rec.job_id
911 ,p_position_id => p_rec.position_id
912 ,p_object_version_number => p_rec.object_version_number
913 ,p_effective_date => p_effective_date); -- Added for Bug# 1760707
914 --
915 hr_utility.set_location(l_proc, 35);
916 --
917 -- Validate Date To
918 --
919 chk_date_to
920 (p_valid_grade_id => p_rec.valid_grade_id
921 ,p_grade_id => p_rec.grade_id
922 ,p_date_from => p_rec.date_from
923 ,p_date_to => p_rec.date_to
924 ,p_job_id => p_rec.job_id
925 ,p_position_id => p_rec.position_id
926 ,p_object_version_number => p_rec.object_version_number
927 ,p_effective_date => p_effective_date); -- Added for Bug# 1760707
928 --
929 hr_utility.set_location(' Leaving:'||l_proc, 40);
930 --
931 -- Call descriptive flexfield validation routines
932 --
933 per_vgr_bus.chk_df(p_rec => p_rec);
934 --
935 hr_utility.set_location(' Leaving:'||l_proc, 45);
936
937 End insert_validate;
938 --
939 -- ----------------------------------------------------------------------------
940 -- |---------------------------< update_validate >----------------------------|
941 -- ----------------------------------------------------------------------------
942 Procedure update_validate(p_rec in per_vgr_shd.g_rec_type,
943 p_effective_date in date) is
944 --
945 l_proc varchar2(72) := g_package||'update_validate';
946 --
947 Begin
948 hr_utility.set_location('Entering:'||l_proc, 5);
949 --
950 -- Call all supporting business operations. Mapping to the
951 -- appropriate Business Rules in per_vgr.bru is provided
952 --
953 hr_utility.set_location(l_proc, 6);
954 --
955 -- Validate Business Group id
956 --
957 hr_api.validate_bus_grp_id(p_rec.business_group_id);
958 --
959 -- Validate date from
960 --
961 chk_date_from
962 (p_valid_grade_id => p_rec.valid_grade_id
963 ,p_grade_id => p_rec.grade_id
964 ,p_date_from => p_rec.date_from
965 ,p_date_to => p_rec.date_to
966 ,p_job_id => p_rec.job_id
967 ,p_position_id => p_rec.position_id
968 ,p_object_version_number => p_rec.object_version_number
969 ,p_effective_date => p_effective_date); -- Added for Bug# 1760707
970 --
971 hr_utility.set_location(l_proc, 7);
972 --
973 -- Validate Date To
974 --
975 chk_date_to
976 (p_valid_grade_id => p_rec.valid_grade_id
977 ,p_grade_id => p_rec.grade_id
978 ,p_date_from => p_rec.date_from
979 ,p_date_to => p_rec.date_to
980 ,p_job_id => p_rec.job_id
981 ,p_position_id => p_rec.position_id
982 ,p_object_version_number => p_rec.object_version_number
983 ,p_effective_date => p_effective_date ); --Added for Bug#1760707
984 --
985 --
986 hr_utility.set_location(' Leaving:'||l_proc, 10);
987 --
988 -- Call descriptive flexfield validation routines
989 --
990 per_vgr_bus.chk_df(p_rec => p_rec);
991 --
992 hr_utility.set_location(' Leaving:'||l_proc, 15);
993 End update_validate;
994 --
995 -- ----------------------------------------------------------------------------
996 -- |---------------------------< delete_validate >----------------------------|
997 -- ----------------------------------------------------------------------------
998 Procedure delete_validate(p_rec in per_vgr_shd.g_rec_type) is
999 --
1000 l_proc varchar2(72) := g_package||'delete_validate';
1001 --
1002 Begin
1003 hr_utility.set_location('Entering:'||l_proc, 5);
1004 --
1005 -- Call all supporting business operations
1006 --
1007 hr_utility.set_location(' Leaving:'||l_proc, 10);
1008 End delete_validate;
1009 --
1010 -- ---------------------------------------------------------------------------
1011 -- |---------------------< return_legislation_code >-------------------------|
1012 -- ---------------------------------------------------------------------------
1013 --
1014 function return_legislation_code
1015 (p_valid_grade_id in per_valid_grades.valid_grade_id%TYPE
1016 ) return varchar2 is
1017 --
1018 -- Cursor to find legislation code
1019 --
1020 cursor csr_leg_code is
1021 select pbg.legislation_code
1022 from per_business_groups pbg
1023 , per_valid_grades pvg
1024 where pvg.valid_grade_id = p_valid_grade_id
1025 and pbg.business_group_id = pvg.business_group_id;
1026 --
1027 -- Declare local variables
1028 --
1029 l_legislation_code varchar2(150);
1030 l_proc varchar2(72) := g_package||'return_legislation_code';
1031 begin
1032 hr_utility.set_location('Entering:'|| l_proc, 10);
1033 --
1034 -- Ensure that all the mandatory parameter are not null
1035 --
1036 hr_api.mandatory_arg_error(p_api_name => l_proc,
1037 p_argument => 'valid_grade_id',
1038 p_argument_value => p_valid_grade_id);
1039 --
1040 if nvl(g_valid_grade_id, hr_api.g_number) = p_valid_grade_id then
1041 --
1042 -- The legislation has already been found with a previous
1043 -- call to this function. Just return the value in the global
1044 -- variable.
1045 --
1046 l_legislation_code := g_legislation_code;
1047 hr_utility.set_location(l_proc, 20);
1048 else
1049 --
1050 -- The ID is different to the last call to this function
1051 -- or this is the first call to this function.
1052 --
1053 open csr_leg_code;
1054 fetch csr_leg_code into l_legislation_code;
1055 if csr_leg_code%notfound then
1056 --
1057 -- The primary key is invalid therefore we must error
1058 --
1059 close csr_leg_code;
1060 fnd_message.set_name('PAY', 'HR_7220_INVALID_PRIMARY_KEY');
1061 fnd_message.raise_error;
1062 end if;
1063 --
1064 hr_utility.set_location(' Leaving:'|| l_proc, 30);
1065 --
1066 -- Set the global variables so the vlaues are
1067 -- available for the next call to this function
1068 --
1069 close csr_leg_code;
1070 g_valid_grade_id := p_valid_grade_id;
1071 g_legislation_code := l_legislation_code;
1072 end if;
1073 hr_utility.set_location('Entering:'|| l_proc, 40);
1074 --
1075 return l_legislation_code;
1076 end return_legislation_code;
1077 --
1078 end per_vgr_bus;
[Home] [Help]