1 Package Body per_vgr_bus as
2 /* $Header: pevgrrhi.pkb 120.0.12010000.2 2008/08/06 09:39:05 ubhat ship $ */
3 --
4 --
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
437 -- None
434 -- that for the valid grade.
435 --
436 -- Pre-conditions:
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) is -- Effective_date added for Bug# 1760707
460 --
461 l_exists varchar2(1);
462 l_proc varchar2(72) := g_package||'chk_position_id';
463 l_business_group_id number(15);
464 --
465 --
466 -- Changed 12-Oct-99 SCNair (per_positions to hr_positions) Date tracked position req
467 -- Changed 22-APR-02.hr_positions is replaced with hr_positions_f and added the
468 -- effective_date condition. Bug 1760707
469 cursor csr_valid_pos is
470 select pos.business_group_id
471 from hr_positions_f pos
472 where pos.position_id = p_position_id
473 and p_effective_date between pos.effective_start_date and pos.effective_end_date;
474 --
475 cursor csr_chk_pos_grd_comb is
476 select null
477 from per_valid_grades vgr
478 where vgr.position_id = p_position_id
479 and vgr.grade_id = p_grade_id;
480 --
481 begin
482 hr_utility.set_location('Entering: '|| l_proc, 1);
483 --
484 --
485 hr_api.mandatory_arg_error
486 (p_api_name => l_proc,
487 p_argument => 'grade_id',
488 p_argument_value => p_grade_id
489 );
490 --
491 hr_api.mandatory_arg_error
492 (p_api_name => l_proc,
493 p_argument => 'business_group_id',
494 p_argument_value => p_business_group_iD
495 );
496 --
497 -- Check that the position ID , if it is not null, is linked to a valid
498 -- position on hr_positions_f
499 --
500 if p_position_id is not null then
501 open csr_valid_pos;
502 fetch csr_valid_pos into l_business_group_id;
503 if csr_valid_pos%notfound then
504 close csr_valid_pos;
505 hr_utility.set_message(801, 'HR_51093_POS_NOT_EXIST');
506 hr_utility.raise_error;
507 end if;
508 close csr_valid_pos;
509 --
510 hr_utility.set_location(l_proc, 2);
511 --
512 if l_business_group_id <> p_business_group_id then
513 hr_utility.set_message(801, 'HR_51094_POS_INVALID_BG');
514 hr_utility.raise_error;
515 end if;
516 --
517 hr_utility.set_location(l_proc, 3);
518 --
519 open csr_chk_pos_grd_comb;
520 fetch csr_chk_pos_grd_comb into l_exists;
521 if csr_chk_pos_grd_comb%found then
522 CLose csr_chk_pos_grd_comb;
523 hr_utility.set_message(801, 'HR_51095_VGR_POS_GRD_COMBO');
524 hr_utility.raise_error;
525 end if;
526 close csr_chk_pos_grd_comb;
527 --
528 end if;
529 hr_utility.set_location(' Leaving: '|| l_proc, 10);
530 end chk_position_id;
531 --
532 -- ----------------------------------------------------------------------------
533 -- |---------------------------< chk_date_to >----------------------------|
534 -- ----------------------------------------------------------------------------
535 --
536 -- Description :
537 -- Validates that date_to is greater than or equal to date from
538 --
539 -- Validates that date to must be within the range specified by date_from
540 -- and date_to on per_grades for the grade_id.
541 --
542 -- Validates that date to is equal to or earlier than the effective_date of
543 -- hr_positions_f for the position_id if it is not null.
544 --
545 -- Validates that date to is equal to or earlier than the date_to of
546 -- per_jobs_v for the job_id if it is not null.
547 --
548 -- Pre-conditions:
549 -- Format of p_date_from and p_date_to must be correct
550 --
551 -- In Arguments :
552 -- p_grade_id
553 -- p_date_to
554 -- p_date_from
555 -- p_job_id
556 -- p_position_id
557 -- p_object_version_number
558 --
559 -- Post Success :
560 -- If the above business rules are satisfied then procesing continues.
561 --
562 -- Post Failure :
563 -- If the above business rules are violated then
564 -- an application error will be raised and processing is terminated
565 --
566 -- Access Status :
567 -- Internal Table Handler Use only.
568 --
569 -- {End of Comments}
570 -- ---------------------------------------------------------------------------
571 procedure chk_date_to
572 (p_valid_grade_id in number
573 ,p_grade_id in number
574 ,p_date_from in date
575 ,p_date_to in date
576 ,p_job_id in number
577 ,p_position_id in number
581 --
578 ,p_object_version_number in number
579 ,p_effective_date in date) --Added for Bug#1760707
580 is
582 l_exists varchar2(1);
583 l_proc varchar2(72) := g_package||'chk_date_to';
584 l_api_updating boolean;
585 --
586 cursor csr_chk_gra_dates is
587 select null
588 from per_grades gra
589 where gra.grade_id = p_grade_id
590 and nvl(p_date_to, hr_api.g_eot) between gra.date_from
591 and nvl(gra.date_to, hr_api.g_eot);
592 --
593 cursor csr_chk_job_dates is
594 select null
595 from per_jobs_v job
596 where job.job_id = p_job_id
597 and nvl(p_date_to, hr_api.g_eot) <= nvl(job.date_to, hr_api.g_eot);
598 --
599 -- Changes 12-Oct-99 SCNair (per_postions to hr_positions) date tracked position req.
600 -- replaced hr_positions with hr_position_f.Added effective_date condition. Bug 1760707
601 --
602 cursor csr_chk_pos_dates is
603 select null
604 from hr_positions_f pos
605 where pos.position_id = p_position_id
606 and nvl(p_date_to, hr_api.g_eot) <= nvl(hr_general.get_position_date_end(p_position_id), hr_api.g_eot)
607 and p_effective_date between pos.effective_start_date and pos.effective_end_date;
608 --
609 begin
610 hr_utility.set_location('Entering:'|| l_proc, 1);
611 --
612 -- Check mandatory parameters havu been set
613 --
614 hr_api.mandatory_arg_error
615 (p_api_name => l_proc
616 ,p_argument => 'grade_id'
617 ,p_argument_value => p_grade_id
618 );
619 --
620 hr_api.mandatory_arg_error
621 (p_api_name => l_proc
622 ,p_argument => 'date_from'
623 ,p_argument_value => p_date_from
624 );
625 --
626 -- Only proceed with validation if :
627 -- a) The current g_old_rec is current and
628 -- b) The date_to value has changed
629 --
630 l_api_updating := per_vgr_shd.api_updating
631 (p_valid_grade_id => p_valid_grade_id
632 ,p_object_version_number => p_object_version_number);
633 --
634 if ((l_api_updating and
635 nvl(per_vgr_shd.g_old_rec.date_to, hr_api.g_eot) <>
636 nvl(p_date_to, hr_api.g_eot)) or
637 (NOT l_api_updating)) then
638 hr_utility.set_location(l_proc, 2);
639 --
640 -- Check that the date_from value is greater than or equal to the date_to
641 -- value for the current record
642 --
643 if p_date_from > nvl(p_date_to, hr_api.g_eot) then
644 hr_utility.set_message(801, 'HR_51096_VGR_DATE_GREATER');
645 hr_utility.raise_error;
646 end if;
647 hr_utility.set_location(l_proc, 3);
648 --
649 -- Check that date_to is within the range of the date_from and date_to
650 -- on per_grades for p_grade_id
651 --
652 open csr_chk_gra_dates;
653 fetch csr_chk_gra_dates into l_exists;
654 if csr_chk_gra_dates%notfound then
655 close csr_chk_gra_dates;
656 hr_utility.set_message(801, 'HR_51097_VGR_END_DATE_INVALID');
657 hr_utility.raise_error;
658 end if;
659 close csr_chk_gra_dates;
660 hr_utility.set_location(l_proc, 4);
661 --
662 -- Check that date_to is on or earlier than the date_to on per_jobs_v for
663 -- p_job_id
664 --
665 if p_job_id is not null then
666 open csr_chk_job_dates;
667 fetch csr_chk_job_dates into l_exists;
668 if csr_chk_job_dates%notfound then
669 close csr_chk_job_dates;
670 hr_utility.set_message(801, 'HR_51098_VGR_END_DATE_JOB');
671 hr_utility.raise_error;
672 end if;
673 close csr_chk_job_dates;
674 end if;
675 hr_utility.set_location(l_proc, 5);
676 --
677 -- Check that date_to is on or later than the end_date on
678 -- hr_positions_f p_position_id
679 --
680 if p_position_id is not null then
681 open csr_chk_pos_dates;
682 fetch csr_chk_pos_dates into l_exists;
683 if csr_chk_pos_dates%notfound then
684 close csr_chk_pos_dates;
685 hr_utility.set_message(801, 'HR_51099_VGR_END_DATE_POS');
686 hr_utility.raise_error;
687 end if;
688 close csr_chk_pos_dates;
689 end if;
690 end if;
691 --
692 hr_utility.set_location(' Leaving:'|| l_proc, 6);
693 end chk_date_to;
694 -- -----------------------------------------------------------------------
695 -- |------------------------------< chk_df >-----------------------------|
696 -- -----------------------------------------------------------------------
697 --
698 -- Description:
699 -- Validates the all Descriptive Flexfield values.
700 --
701 -- Pre-conditions:
702 -- All other columns have been validated. Must be called as the
703 -- last step from insert_validate and update_validate.
704 --
705 -- In Arguments:
706 -- p_rec
707 --
708 -- Post Success:
709 -- If the Descriptive Flexfield structure column and data values are
710 -- all valid this procedure will end normally and processing will
711 -- continue.
712 --
713 -- Post Failure:
714 -- If the Descriptive Flexfield structure column value or any of
718 -- Access Status:
715 -- the data values are invalid then an application error is raised as
716 -- a PL/SQL exception.
717 --
719 -- Internal Row Handler Use Only.
720 --
721 procedure chk_df
722 (p_rec in per_vgr_shd.g_rec_type) is
723 --
724 l_proc varchar2(72) := g_package||'chk_df';
725 --
726 begin
727 hr_utility.set_location('Entering:'||l_proc, 10);
728 --
729 if ((p_rec.valid_grade_id is not null) and (
730 nvl(per_vgr_shd.g_old_rec.attribute_category, hr_api.g_varchar2) <>
731 nvl(p_rec.attribute_category, hr_api.g_varchar2) or
732 nvl(per_vgr_shd.g_old_rec.attribute1, hr_api.g_varchar2) <>
733 nvl(p_rec.attribute1, hr_api.g_varchar2) or
734 nvl(per_vgr_shd.g_old_rec.attribute2, hr_api.g_varchar2) <>
735 nvl(p_rec.attribute2, hr_api.g_varchar2) or
736 nvl(per_vgr_shd.g_old_rec.attribute3, hr_api.g_varchar2) <>
737 nvl(p_rec.attribute3, hr_api.g_varchar2) or
738 nvl(per_vgr_shd.g_old_rec.attribute4, hr_api.g_varchar2) <>
739 nvl(p_rec.attribute4, hr_api.g_varchar2) or
740 nvl(per_vgr_shd.g_old_rec.attribute5, hr_api.g_varchar2) <>
741 nvl(p_rec.attribute5, hr_api.g_varchar2) or
742 nvl(per_vgr_shd.g_old_rec.attribute6, hr_api.g_varchar2) <>
743 nvl(p_rec.attribute6, hr_api.g_varchar2) or
744 nvl(per_vgr_shd.g_old_rec.attribute7, hr_api.g_varchar2) <>
745 nvl(p_rec.attribute7, hr_api.g_varchar2) or
746 nvl(per_vgr_shd.g_old_rec.attribute8, hr_api.g_varchar2) <>
747 nvl(p_rec.attribute8, hr_api.g_varchar2) or
748 nvl(per_vgr_shd.g_old_rec.attribute9, hr_api.g_varchar2) <>
749 nvl(p_rec.attribute9, hr_api.g_varchar2) or
750 nvl(per_vgr_shd.g_old_rec.attribute10, hr_api.g_varchar2) <>
751 nvl(p_rec.attribute10, hr_api.g_varchar2) or
752 nvl(per_vgr_shd.g_old_rec.attribute11, hr_api.g_varchar2) <>
753 nvl(p_rec.attribute11, hr_api.g_varchar2) or
754 nvl(per_vgr_shd.g_old_rec.attribute12, hr_api.g_varchar2) <>
755 nvl(p_rec.attribute12, hr_api.g_varchar2) or
756 nvl(per_vgr_shd.g_old_rec.attribute13, hr_api.g_varchar2) <>
757 nvl(p_rec.attribute13, hr_api.g_varchar2) or
758 nvl(per_vgr_shd.g_old_rec.attribute14, hr_api.g_varchar2) <>
759 nvl(p_rec.attribute14, hr_api.g_varchar2) or
760 nvl(per_vgr_shd.g_old_rec.attribute15, hr_api.g_varchar2) <>
761 nvl(p_rec.attribute15, hr_api.g_varchar2) or
762 nvl(per_vgr_shd.g_old_rec.attribute16, hr_api.g_varchar2) <>
763 nvl(p_rec.attribute16, hr_api.g_varchar2) or
764 nvl(per_vgr_shd.g_old_rec.attribute17, hr_api.g_varchar2) <>
765 nvl(p_rec.attribute17, hr_api.g_varchar2) or
766 nvl(per_vgr_shd.g_old_rec.attribute18, hr_api.g_varchar2) <>
767 nvl(p_rec.attribute18, hr_api.g_varchar2) or
768 nvl(per_vgr_shd.g_old_rec.attribute19, hr_api.g_varchar2) <>
769 nvl(p_rec.attribute19, hr_api.g_varchar2) or
770 nvl(per_vgr_shd.g_old_rec.attribute20, hr_api.g_varchar2) <>
771 nvl(p_rec.attribute20, hr_api.g_varchar2)))
772 or
773 (p_rec.valid_grade_id is null) then
774 --
775 -- Only execute the validation if absolutely necessary:
776 -- a) During update, the structure column value or any
777 -- of the attribute values have actually changed.
778 -- b) During insert.
779 --
780 hr_dflex_utility.ins_or_upd_descflex_attribs
781 (p_appl_short_name => 'PER'
782 ,p_descflex_name => 'PER_VALID_GRADES'
783 ,p_attribute_category => p_rec.attribute_category
784 ,p_attribute1_name => 'ATTRIBUTE1'
785 ,p_attribute1_value => p_rec.attribute1
786 ,p_attribute2_name => 'ATTRIBUTE2'
787 ,p_attribute2_value => p_rec.attribute2
788 ,p_attribute3_name => 'ATTRIBUTE3'
789 ,p_attribute3_value => p_rec.attribute3
790 ,p_attribute4_name => 'ATTRIBUTE4'
791 ,p_attribute4_value => p_rec.attribute4
792 ,p_attribute5_name => 'ATTRIBUTE5'
793 ,p_attribute5_value => p_rec.attribute5
794 ,p_attribute6_name => 'ATTRIBUTE6'
795 ,p_attribute6_value => p_rec.attribute6
796 ,p_attribute7_name => 'ATTRIBUTE7'
797 ,p_attribute7_value => p_rec.attribute7
798 ,p_attribute8_name => 'ATTRIBUTE8'
799 ,p_attribute8_value => p_rec.attribute8
800 ,p_attribute9_name => 'ATTRIBUTE9'
801 ,p_attribute9_value => p_rec.attribute9
802 ,p_attribute10_name => 'ATTRIBUTE10'
803 ,p_attribute10_value => p_rec.attribute10
804 ,p_attribute11_name => 'ATTRIBUTE11'
805 ,p_attribute11_value => p_rec.attribute11
806 ,p_attribute12_name => 'ATTRIBUTE12'
807 ,p_attribute12_value => p_rec.attribute12
808 ,p_attribute13_name => 'ATTRIBUTE13'
809 ,p_attribute13_value => p_rec.attribute13
810 ,p_attribute14_name => 'ATTRIBUTE14'
811 ,p_attribute14_value => p_rec.attribute14
812 ,p_attribute15_name => 'ATTRIBUTE15'
813 ,p_attribute15_value => p_rec.attribute15
814 ,p_attribute16_name => 'ATTRIBUTE16'
815 ,p_attribute16_value => p_rec.attribute16
816 ,p_attribute17_name => 'ATTRIBUTE17'
817 ,p_attribute17_value => p_rec.attribute17
818 ,p_attribute18_name => 'ATTRIBUTE18'
819 ,p_attribute18_value => p_rec.attribute18
820 ,p_attribute19_name => 'ATTRIBUTE19'
821 ,p_attribute19_value => p_rec.attribute19
822 ,p_attribute20_name => 'ATTRIBUTE20'
826 --
823 ,p_attribute20_value => p_rec.attribute20
824 );
825 end if;
827 hr_utility.set_location(' Leaving:'||l_proc, 20);
828
829 end chk_df;
830 --
831 -- ----------------------------------------------------------------------------
832 -- |---------------------------< insert_validate >----------------------------|
833 -- ----------------------------------------------------------------------------
834 Procedure insert_validate(p_rec in per_vgr_shd.g_rec_type,
835 p_effective_date in date) is -- Added for Bug# 1760707
836 --
837 l_proc varchar2(72) := g_package||'insert_validate';
838 --
839 Begin
840 hr_utility.set_location('Entering:'||l_proc, 5);
841 --
842 -- Call all supporting business operations. Mapping to the appropriate
843 -- Business Rules in pervga.bru is provided.
844 --
845 --
846 -- Validate Business Group id
847 --
848 hr_api.validate_bus_grp_id(p_rec.business_group_id);
849 --
850 hr_utility.set_location(l_proc, 10);
851 --
852 -- Validate Grade id
853 --
854 chk_grade_id (p_grade_id => p_rec.grade_id
855 ,p_business_group_id => p_rec.business_group_id );
856 --
857 hr_utility.set_location(l_proc, 15);
858 --
859 -- Validate that either Position or Job Ids are set
860 --
861 chk_job_or_position_rule
862 (p_job_id => p_rec.job_id
863 ,p_position_id => p_rec.position_id);
864 --
865 hr_utility.set_location(l_proc, 20);
866 --
867 -- Validate Job id
868 --
869 chk_job_id (p_job_id => p_rec.job_id
870 ,p_business_group_id => p_rec.business_group_id
871 ,p_grade_id => p_rec.grade_id
872 ,p_date_from => p_rec.date_from -- Added For Bug # 6983587
873 ,p_date_to => p_rec.date_to); -- Added For Bug 6983587
874 --
875 hr_utility.set_location(l_proc, 25);
876 --
877 -- Validate Position id
878 --
879 chk_position_id (p_position_id => p_rec.position_id
880 ,p_business_group_id => p_rec.business_group_id
881 ,p_grade_id => p_rec.grade_id
882 ,p_effective_date => p_effective_date); -- Added for Bug# 1760707
883 --
884 hr_utility.set_location(l_proc, 30);
885 --
886 -- Validate Date From
887 --
888 chk_date_from
889 (p_valid_grade_id => p_rec.valid_grade_id
890 ,p_grade_id => p_rec.grade_id
891 ,p_date_from => p_rec.date_from
892 ,p_date_to => p_rec.date_to
893 ,p_job_id => p_rec.job_id
894 ,p_position_id => p_rec.position_id
895 ,p_object_version_number => p_rec.object_version_number
896 ,p_effective_date => p_effective_date); -- Added for Bug# 1760707
897 --
898 hr_utility.set_location(l_proc, 35);
899 --
900 -- Validate Date To
901 --
902 chk_date_to
903 (p_valid_grade_id => p_rec.valid_grade_id
904 ,p_grade_id => p_rec.grade_id
905 ,p_date_from => p_rec.date_from
906 ,p_date_to => p_rec.date_to
907 ,p_job_id => p_rec.job_id
908 ,p_position_id => p_rec.position_id
909 ,p_object_version_number => p_rec.object_version_number
910 ,p_effective_date => p_effective_date); -- Added for Bug# 1760707
911 --
912 hr_utility.set_location(' Leaving:'||l_proc, 40);
913 --
914 -- Call descriptive flexfield validation routines
915 --
916 per_vgr_bus.chk_df(p_rec => p_rec);
917 --
918 hr_utility.set_location(' Leaving:'||l_proc, 45);
919
920 End insert_validate;
921 --
922 -- ----------------------------------------------------------------------------
923 -- |---------------------------< update_validate >----------------------------|
924 -- ----------------------------------------------------------------------------
925 Procedure update_validate(p_rec in per_vgr_shd.g_rec_type,
926 p_effective_date in date) is
927 --
928 l_proc varchar2(72) := g_package||'update_validate';
929 --
930 Begin
931 hr_utility.set_location('Entering:'||l_proc, 5);
932 --
933 -- Call all supporting business operations. Mapping to the
934 -- appropriate Business Rules in per_vgr.bru is provided
935 --
936 hr_utility.set_location(l_proc, 6);
937 --
938 -- Validate Business Group id
939 --
940 hr_api.validate_bus_grp_id(p_rec.business_group_id);
941 --
942 -- Validate date from
943 --
944 chk_date_from
945 (p_valid_grade_id => p_rec.valid_grade_id
946 ,p_grade_id => p_rec.grade_id
947 ,p_date_from => p_rec.date_from
948 ,p_date_to => p_rec.date_to
949 ,p_job_id => p_rec.job_id
950 ,p_position_id => p_rec.position_id
951 ,p_object_version_number => p_rec.object_version_number
952 ,p_effective_date => p_effective_date); -- Added for Bug# 1760707
953 --
954 hr_utility.set_location(l_proc, 7);
955 --
956 -- Validate Date To
957 --
958 chk_date_to
959 (p_valid_grade_id => p_rec.valid_grade_id
960 ,p_grade_id => p_rec.grade_id
961 ,p_date_from => p_rec.date_from
962 ,p_date_to => p_rec.date_to
963 ,p_job_id => p_rec.job_id
964 ,p_position_id => p_rec.position_id
965 ,p_object_version_number => p_rec.object_version_number
966 ,p_effective_date => p_effective_date ); --Added for Bug#1760707
967 --
968 --
969 hr_utility.set_location(' Leaving:'||l_proc, 10);
970 --
971 -- Call descriptive flexfield validation routines
972 --
973 per_vgr_bus.chk_df(p_rec => p_rec);
974 --
975 hr_utility.set_location(' Leaving:'||l_proc, 15);
976 End update_validate;
977 --
978 -- ----------------------------------------------------------------------------
979 -- |---------------------------< delete_validate >----------------------------|
980 -- ----------------------------------------------------------------------------
981 Procedure delete_validate(p_rec in per_vgr_shd.g_rec_type) is
982 --
983 l_proc varchar2(72) := g_package||'delete_validate';
984 --
985 Begin
986 hr_utility.set_location('Entering:'||l_proc, 5);
987 --
988 -- Call all supporting business operations
989 --
990 hr_utility.set_location(' Leaving:'||l_proc, 10);
991 End delete_validate;
992 --
993 -- ---------------------------------------------------------------------------
994 -- |---------------------< return_legislation_code >-------------------------|
995 -- ---------------------------------------------------------------------------
996 --
997 function return_legislation_code
998 (p_valid_grade_id in per_valid_grades.valid_grade_id%TYPE
999 ) return varchar2 is
1000 --
1001 -- Cursor to find legislation code
1002 --
1003 cursor csr_leg_code is
1004 select pbg.legislation_code
1005 from per_business_groups pbg
1006 , per_valid_grades pvg
1007 where pvg.valid_grade_id = p_valid_grade_id
1008 and pbg.business_group_id = pvg.business_group_id;
1009 --
1010 -- Declare local variables
1011 --
1012 l_legislation_code varchar2(150);
1013 l_proc varchar2(72) := g_package||'return_legislation_code';
1014 begin
1015 hr_utility.set_location('Entering:'|| l_proc, 10);
1016 --
1017 -- Ensure that all the mandatory parameter are not null
1018 --
1019 hr_api.mandatory_arg_error(p_api_name => l_proc,
1020 p_argument => 'valid_grade_id',
1021 p_argument_value => p_valid_grade_id);
1022 --
1023 if nvl(g_valid_grade_id, hr_api.g_number) = p_valid_grade_id then
1024 --
1025 -- The legislation has already been found with a previous
1026 -- call to this function. Just return the value in the global
1027 -- variable.
1028 --
1029 l_legislation_code := g_legislation_code;
1030 hr_utility.set_location(l_proc, 20);
1031 else
1032 --
1033 -- The ID is different to the last call to this function
1034 -- or this is the first call to this function.
1035 --
1036 open csr_leg_code;
1037 fetch csr_leg_code into l_legislation_code;
1038 if csr_leg_code%notfound then
1039 --
1040 -- The primary key is invalid therefore we must error
1041 --
1042 close csr_leg_code;
1043 fnd_message.set_name('PAY', 'HR_7220_INVALID_PRIMARY_KEY');
1044 fnd_message.raise_error;
1045 end if;
1046 --
1047 hr_utility.set_location(' Leaving:'|| l_proc, 30);
1048 --
1049 -- Set the global variables so the vlaues are
1050 -- available for the next call to this function
1051 --
1052 close csr_leg_code;
1053 g_valid_grade_id := p_valid_grade_id;
1054 g_legislation_code := l_legislation_code;
1055 end if;
1056 hr_utility.set_location('Entering:'|| l_proc, 40);
1057 --
1058 return l_legislation_code;
1059 end return_legislation_code;
1060 --
1061 end per_vgr_bus;
[Home] [Help]