[Home] [Help]
PACKAGE BODY: APPS.PER_POS_BUS
Source
1 Package Body per_pos_bus as
2 /* $Header: peposrhi.pkb 115.26 2003/11/26 15:25:03 hsajja ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' per_pos_bus.'; -- Global package name
9
10 -- Added for Bug fix 892165
11 -- -----------------------------------------------------------------
12 -- |-----------------------< chk_non_updateable_args >--------------|
13 -- -----------------------------------------------------------------
14 --
15 Procedure chk_non_updateable_args
16 (p_rec in per_pos_shd.g_rec_type
17 ) is
18 --
19 l_proc varchar2(72) := g_package||'chk_non_updateable_args';
20 l_error exception;
21 l_argument varchar2(30);
22 --
23 Begin
24 hr_utility.set_location('Entering:'||l_proc, 10);
25 --
26 -- Only proceed with validation if a row exists for
27 -- the current record in the HR Schema
28 --
29 IF not per_pos_shd.api_updating
30 (p_position_id => p_rec.position_id
31 ,p_object_version_number => p_rec.object_version_number)
32 THEN
33 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
34 hr_utility.set_message_token('PROCEDURE', l_proc);
35 hr_utility.set_message_token('STEP', '20');
36 END IF;
37 hr_utility.set_location(l_proc, 30);
38 --
39 if nvl(p_rec.business_group_id, hr_api.g_number) <>
40 nvl(per_pos_shd.g_old_rec.business_group_id
41 ,hr_api.g_number
42 ) then
43 l_argument := 'business_group_id';
44 raise l_error;
45 end if;
46 --
47 --
48 if nvl(p_rec.job_id, hr_api.g_number) <>
49 nvl(per_pos_shd.g_old_rec.job_id
50 ,hr_api.g_number
51 ) then
52 l_argument := 'job_id';
53 raise l_error;
54 end if;
55 --
56 --
57 if nvl(p_rec.organization_id, hr_api.g_number) <>
58 nvl(per_pos_shd.g_old_rec.organization_id
59 ,hr_api.g_number
60 ) then
61 l_argument := 'organization_id';
62 raise l_error;
63 end if;
64 --
65 hr_utility.set_location(l_proc, 40);
66 exception
67 when l_error then
68 hr_api.argument_changed_error
69 (p_api_name => l_proc
70 ,p_argument => l_argument
71 );
72 when others then
73 raise;
74 hr_utility.set_location(' Leaving:'||l_proc, 50);
75 end chk_non_updateable_args;
76 --
77 --
78 -- ---------------------------------------------------------------------------
79 -- |----------------------< set_security_group_id >------------------------|
80 -- ---------------------------------------------------------------------------
81 --
82 --
83 procedure set_security_group_id
84 (
85 p_position_id in per_positions.position_id%TYPE
86 ) is
87 --
88 -- Declare cursor
89 --
90 cursor csr_sec_grp is
91 select inf.org_information14
92 from hr_organization_information inf
93 , per_positions pos
94 where pos.position_id = p_position_id
95 and inf.organization_id = pos.business_group_id
96 and inf.org_information_context || '' = 'Business Group Information';
97 --
98 -- Local variables
99 --
100 l_security_group_id number;
101 l_proc varchar2(72) := g_package||'set_security_group_id';
102 --
103 begin
104 hr_utility.set_location('Entering:'|| l_proc, 10);
105 --
106 -- Ensure that all the mandatory parameter are not null
107 --
108 hr_api.mandatory_arg_error(p_api_name => l_proc,
109 p_argument => 'position_id',
110 p_argument_value => p_position_id);
111 --
112 open csr_sec_grp;
113 fetch csr_sec_grp into l_security_group_id;
114 if csr_sec_grp%notfound then
115 close csr_sec_grp;
116 --
117 -- The primary key is invalid therefore we must error
118 --
119 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
120 hr_utility.raise_error;
121 end if;
122 close csr_sec_grp;
123 --
124 -- Set the security_group_id in CLIENT_INFO
125 --
126 hr_api.set_security_group_id
127 (p_security_group_id => l_security_group_id
128 );
129 --
130 hr_utility.set_location(' Leaving:'|| l_proc, 20);
131 --
132 end set_security_group_id;
133 --
134 -- ----------------------------------------------------------------------------
135 -- |--------------------------< chk_job_id >--------------------------------|
136 -- ----------------------------------------------------------------------------
137 --
138 -- Desciption :
139 --
140 -- Validates that JOB_ID is not null
141 --
142 -- Validates that values entered for this column exist in the PER_JOBS
143 -- table.
144 --
145 -- Validates that PER_POSITIONS.DATE_EFFECTIVE cannot be less than the
146 -- DATE_FROM value for the JOB record on PER_JOBS.
147 --
148 -- Pre-conditions :
149 -- Format for p_date_effective must be correct
150 --
151 -- In Arguments :
152 -- p_job_id
153 -- p_date_effective
154 --
155 -- Post Success :
156 -- If a row exists in per_jobs for the job id and the date conditions
157 -- are met, processing continues
158 --
159 -- Post Failure :
160 -- If a row does not exist in per_jobs for the job id or if date conditions
161 -- are not met, an application error will be raised and processing is
162 -- terminated
163 --
164 -- Access Status :
165 -- Internal Table Handler Use only.
166 --
167 -- {End of Comments}
168 --
169 -- ---------------------------------------------------------------------------
170 procedure chk_job_id
171 (p_job_id in number
172 ,p_date_effective in date
173 ,p_business_group_id in number
174 ) is
175 --
176 l_exists varchar2(1);
177 l_proc varchar2(72) := g_package||'chk_job_id';
178 --
179 cursor csr_valid_job_id is
180 select 'x'
181 from per_jobs job
182 where job.job_id = p_job_id
183 and job.business_group_id + 0 = p_business_group_id;
184 --
185 cursor csr_valid_job_dates is
186 select 'x'
187 from per_jobs job
188 where job.job_id = p_job_id
189 and p_date_effective between job.date_from
190 and nvl(job.date_to,hr_api.g_eot);
191 --
192 begin
193 hr_utility.set_location('Entering:'||l_proc, 1);
194 --
195 -- Check mandatory parameters have been set
196 --
197 hr_api.mandatory_arg_error
198 (p_api_name => l_proc
199 ,p_argument => 'job_id'
200 ,p_argument_value => p_job_id
201 );
202 hr_utility.set_location(l_proc, 2);
203 --
204 -- Check for valid job id
205 --
206 open csr_valid_job_id;
207 fetch csr_valid_job_id into l_exists;
208 if csr_valid_job_id%notfound then
209 close csr_valid_job_id;
210 hr_utility.set_message(801,'HR_51090_JOB_NOT_EXIST');
211 hr_utility.raise_error;
212 else
213 hr_utility.set_location(l_proc, 3);
214 --
215 -- Check p_date_effective between job date_from and date_to
216 --
217 close csr_valid_job_id;
218 open csr_valid_job_dates;
219 fetch csr_valid_job_dates into l_exists;
220 if csr_valid_job_dates%notfound then
221 close csr_valid_job_dates;
222 hr_utility.set_message(801,'HR_51358_POS_JOB_INVALID_DATE');
223 hr_utility.raise_error;
224 end if;
225 close csr_valid_job_dates;
226 end if;
227 --
228 hr_utility.set_location(' Leaving:'||l_proc, 4);
229 end chk_job_id;
230 --
231 --
232 -- ---------------------------------------------------------------------------
233 -- | -------------------< chk_organization_id >----------------------------|
234 -- ---------------------------------------------------------------------------
235 --
236 -- Desciption :
237 --
238 -- Validates that ORGANIZATION_ID is not null
239 --
240 -- Validates that values entered for this column exist in the
241 -- HR_ORGANIZATION_UNITS table. (I)
242 --
243 -- Validates that PER_POSITIONS.DATE_EFFECTIVE cannot be less than the
244 -- DATE_FROM value for the ORGANIZATION record on HR_ORGANIZATION_UNITS.
245 --
246 -- Pre-conditions:
247 -- Format for p_date_effective must be correct
248 --
249 -- In Arguments :
250 -- p_organization_id
251 -- p_date_effective
252 --
253 -- Post Success :
254 --
255 -- If a row exists in hr_organization_units for the organization id and the
256 -- date conditions are met then processing continues
257 --
258 -- Post Failure :
259 --
260 -- If a row does not exist in hr_organization_units for the organization id
261 -- or the date conditions are not met then an application error will be
262 -- raised and processing is terminated
263 --
264 -- Access Status :
265 -- Internal Table Handler Use only.
266 --
267 -- {End of Comments}
268 --
269 -- ----------------------------------------------------------------------------
270 procedure chk_organization_id
271 (p_organization_id in number
272 ,p_date_effective in date
273 ,p_business_group_id in number
274 ) is
275 --
276 l_exists varchar2(1);
277 l_proc varchar2(72) := g_package||'chk_organization_id';
278 --
279 cursor csr_valid_organization_id is
280 select 'x'
281 from per_organization_units oru
282 where oru.organization_id = p_organization_id
283 and oru.business_group_id + 0 = p_business_group_id
284 and oru.internal_external_flag = 'INT';
285 --
286 cursor csr_valid_organization_dates is
287 select 'x'
288 from hr_organization_units oru
289 where oru.organization_id = p_organization_id
290 and p_date_effective between oru.date_from
291 and nvl(oru.date_to,hr_api.g_eot);
292 --
293 begin
294 hr_utility.set_location('Entering:'||l_proc, 1);
295 --
296 -- Check mandatory parameters have been set
297 --
298 hr_api.mandatory_arg_error
299 (p_api_name => l_proc
300 ,p_argument => 'organization_id'
301 ,p_argument_value => p_organization_id
302 );
303 hr_utility.set_location(l_proc, 2);
304 --
305 -- Check for valid organization id
306 --
307 open csr_valid_organization_id;
308 fetch csr_valid_organization_id into l_exists;
309 if csr_valid_organization_id%notfound then
310 close csr_valid_organization_id;
311 hr_utility.set_message(801,'HR_51371_POS_ORG_NOT_EXIST');
312 hr_utility.raise_error;
313 else
314 hr_utility.set_location(l_proc, 3);
315 --
316 -- Check p_date_effective between org date_from and date_to
317 --
318 close csr_valid_organization_id;
319 open csr_valid_organization_dates;
320 fetch csr_valid_organization_dates into l_exists;
321 if csr_valid_organization_dates%notfound then
322 close csr_valid_organization_dates;
323 hr_utility.set_message(801,'HR_51359_POS_ORG_INVAL_W_DATE');
324 hr_utility.raise_error;
325 end if;
326 close csr_valid_organization_dates;
327 end if;
328 --
329 hr_utility.set_location(' Leaving:'||l_proc, 4);
330 end chk_organization_id;
331 --
332 -- ---------------------------------------------------------------------------
333 -- |--------------------< chk_successor_position_id >----------------------|
334 -- ---------------------------------------------------------------------------
335 --
336 -- Desciption:
337 --
338 -- Validates that if SUCCESSOR_POSITION_ID exists, it must be a valid
339 -- position for the business group and the successor DATE_END is on or after
340 -- the DATE_EFFECTIVE of the position.
341 --
342 -- Pre-conditions:
343 -- Format for p_date_effective must be correct
344 --
345 -- In Arguments :
346 -- p_position_id
347 -- p_business_group_id
348 -- p_successor_position_id
349 -- p_date_effective
350 -- p_object_version_number
351 --
352 -- Post Success :
353 -- If the above business rules are satisfied, processing continues
354 --
355 -- Post Failure :
356 -- If the above business rules are violated, an application error
357 -- is raised and processing terminates
358 --
359 -- Access Status :
360 -- Internal Table Handler Use only.
361 --
362 -- {End of Comments}
363 --
364 -- ----------------------------------------------------------------------------
365 procedure chk_successor_position_id
366 (p_business_group_id in number
367 ,p_position_id in number default null
368 ,p_successor_position_id in number
369 ,p_date_effective in date
370 ,p_object_version_number in number default null
371 ) is
372 --
373 l_exists varchar2(1);
374 l_proc varchar2(72) := g_package||'chk_successor_position_id';
375 l_api_updating boolean;
376 --
377 cursor csr_valid_successor_position is
378 select 'x'
379 from per_positions pos
380 where pos.position_id = p_successor_position_id
381 and pos.business_group_id + 0 = p_business_group_id
382 and nvl(pos.date_end,hr_api.g_eot) >= p_date_effective ;
383 --
384 begin
385 hr_utility.set_location('Entering:'||l_proc, 1);
386 --
387 -- Only proceed with validation if :
388 -- a) The current g_old_rec is current and
389 -- b) The successor_position_id value has changed
390 --
391 if p_successor_position_id is not null then
392 --
393 hr_api.mandatory_arg_error
394 (p_api_name => l_proc
395 ,p_argument => 'date effective'
396 ,p_argument_value => p_date_effective);
397 --
398 l_api_updating := per_pos_shd.api_updating
399 (p_position_id => p_position_id
400 ,p_object_version_number => p_object_version_number);
401 --
402 -- Check for valid successor position id
403 --
404 if ((l_api_updating and
405 per_pos_shd.g_old_rec.successor_position_id <>
406 p_successor_position_id) or
407 (NOT l_api_updating)) then
408 --
409 hr_utility.set_location(l_proc, 2);
410 --
411 open csr_valid_successor_position;
412 fetch csr_valid_successor_position into l_exists;
413 if csr_valid_successor_position%notfound then
414 close csr_valid_successor_position;
415 fnd_message.set_name('PER','PER_52979_POS_SUCC_NOT_EXIST');
416 fnd_message.raise_error;
417 else
418 close csr_valid_successor_position;
419 if(l_api_updating and p_position_id = p_successor_position_id) then
420 hr_utility.set_message(801,'HR_51360_POS_SUCCESSOR_EQ_POS');
421 hr_utility.raise_error;
422 end if;
423 end if;
424 end if;
425 end if;
426 --
427 hr_utility.set_location(' Leaving:'||l_proc, 3);
428 end chk_successor_position_id;
429 --
430 -- ---------------------------------------------------------------------------
431 -- |--------------------< chk_relief_position_id >-------------------------|
432 -- ---------------------------------------------------------------------------
433 --
434 -- Desciption :
435 --
439 --
436 -- Validates that if RELIEF_POSITION_ID exists, it must be a valid
437 -- position for the business group and the relief DATE_END is on or after
438 -- the DATE_EFFECTIVE of the position.
440 -- Pre-conditions:
441 -- Format for p_date_effective must be correct
442 --
443 -- In Arguments :
444 -- p_business_group_id
445 -- p_position_id
446 -- p_relief_position_id
447 -- p_date_effective
448 -- p_object_version_number
449 --
450 -- Post Success :
451 -- If the above business rules are satisfied, processing continues
452 --
453 -- Post Failure :
454 -- If the above business rules are violated, an application error
455 -- is raised and processing terminates
456 --
457 -- Access Status :
458 -- Internal Table Handler Use only.
459 --
460 -- ---------------------------------------------------------------------------
461 procedure chk_relief_position_id
462 (p_business_group_id in number
463 ,p_position_id in number default null
464 ,p_relief_position_id in number
465 ,p_date_effective in date
466 ,p_object_version_number in number default null
467 ) is
468 --
469 l_exists varchar2(1);
470 l_proc varchar2(72) := g_package||'chk_relief_position_id';
471 l_api_updating boolean;
472 --
473 --
474 cursor csr_valid_relief_position is
475 select 'x'
476 from per_positions pos
477 where pos.position_id = p_relief_position_id
478 and pos.business_group_id + 0 = p_business_group_id
479 and nvl(pos.date_end,hr_api.g_eot) >= p_date_effective ;
480 --
481 begin
482 hr_utility.set_location('Entering:'||l_proc, 1);
483 --
484 -- Only proceed with validation if :
485 -- a) The current g_old_rec is current and
486 -- b) The successor_position_id value has changed
487 --
488 if p_relief_position_id is not null then
489 --
490 hr_api.mandatory_arg_error
491 (p_api_name => l_proc
492 ,p_argument => 'date effective'
493 ,p_argument_value => p_date_effective);
494 --
495 l_api_updating := per_pos_shd.api_updating
496 (p_position_id => p_position_id
497 ,p_object_version_number => p_object_version_number);
498 --
499 -- Check for valid relief position id
500 --
501 if ((l_api_updating and
502 per_pos_shd.g_old_rec.relief_position_id <>
503 p_relief_position_id) or
504 (NOT l_api_updating)) then
505 --
506 hr_utility.set_location(l_proc, 2);
507 --
508 open csr_valid_relief_position;
509 fetch csr_valid_relief_position into l_exists;
510 if csr_valid_relief_position%notfound then
511 close csr_valid_relief_position;
512 fnd_message.set_name('PER','PER_52980_POS_RELF_NOT_EXIST');
513 fnd_message.raise_error;
514 else
515 close csr_valid_relief_position;
516 if(l_api_updating and p_position_id = p_relief_position_id) then
517 hr_utility.set_message(801,'HR_51361_POS_RELIEF_EQ_POS');
518 hr_utility.raise_error;
519 end if;
520 end if;
521 end if;
522 end if;
523 --
524 hr_utility.set_location(' Leaving:'||l_proc, 3);
525 end chk_relief_position_id;
526 --
527 --
528 -- ---------------------------------------------------------------------------
529 -- |-------------------------< chk_location_id >---------------------------|
530 -- ----------------------------------------------------------------------------
531 --
532 -- Desciption :
533 --
534 -- Selects the value for LOCATION_ID from HR_ORGANIZATION_UNITS for the
535 -- position's ORGANIZATION_ID when p_location_id is null. When the
536 -- organization's LOCATION_ID is null the value for the business group is
537 -- selected.
538 --
539 -- Validates that values entered for this column exist in the
540 -- HR_LOCATIONS table and are active for the PER_POSITIONS.DATE_EFFECTIVE
541 -- i.e. HR_LOCATIONS.INACTIVE_DATE must be null or greater than
542 -- PER_POSITIONS.DATE_EFFECTIVE
543 --
544 -- Pre-conditions:
545 -- Format for p_date_effective must be correct
546 --
547 -- In Arguments :
548 -- p_business_group_id
549 -- p_organization_id
550 -- p_position_id
551 -- p_location_id
552 -- p_date_effective
553 -- p_object_version_number
554 --
555 -- Post Success :
556 -- If the above business rules are satisfied, processing continues
557 --
558 -- Post Failure :
559 -- If the above business rules are violated, an application error
560 -- is raised and processing terminates
561 --
562 -- Access Status :
563 -- Internal Table Handler Use only.
564 --
565 -- {End of Comments}
566 --
567 -- ---------------------------------------------------------------------------
568 procedure chk_location_id
569 (p_position_id in number default null
570 ,p_location_id in number
571 ,p_date_effective in date
572 ,p_object_version_number in number default null) is
573 --
574 l_exists varchar2(1);
575 l_proc varchar2(72) := g_package||'chk_location_id';
576 l_location_id number;
577 l_api_updating boolean;
581 from hr_locations loc
578 --
579 cursor csr_valid_location is
580 select 'x'
582 where loc.location_id = p_location_id
583 and p_date_effective < nvl(loc.inactive_date,
584 hr_api.g_eot);
585 --
586 begin
587 hr_utility.set_location('Entering:'||l_proc, 1);
588 --
589 -- Only proceed with validation if :
590 -- a) The current g_old_rec is current and
591 -- b) The location_id value has changed
592 --
593 l_api_updating := per_pos_shd.api_updating
594 (p_position_id => p_position_id
595 ,p_object_version_number => p_object_version_number);
596 --
597 if ((l_api_updating and
598 nvl(per_pos_shd.g_old_rec.location_id,hr_api.g_number) <>
599 nvl(p_location_id,hr_api.g_number)) or
600 (NOT l_api_updating)) then
601 --
602 -- Validate that location id is valid for p_date_effective
603 --
604 hr_utility.set_location(l_proc, 4);
605 --
606 if p_location_id is not null then
607 open csr_valid_location;
608 fetch csr_valid_location into l_exists;
609 if csr_valid_location%notfound then
610 close csr_valid_location;
611 hr_utility.set_message(801,'HR_51357_POS_LOC_NOT_EXIST');
612 hr_utility.raise_error;
613 end if;
614 close csr_valid_location;
615 end if;
616 end if;
617 --
618 hr_utility.set_location(' Leaving:'||l_proc, 5);
619 end chk_location_id;
620 --
621 -- ---------------------------------------------------------------------------
622 -- |--------------------< chk_position_definition_id >---------------------|
623 -- ---------------------------------------------------------------------------
624 --
625 -- Desciption :
626 --
627 -- Validates that POSITION_DEFINITION_ID is not null
628 --
629 --
630 -- Pre-conditions:
631 --
632 -- In Arguments :
633 -- p_position_definition_id
634 --
635 -- Post Success :
636 --
637 -- Post Failure :
638 --
639 -- Access Status :
640 -- Internal Table Handler Use only.
641 --
642 -- {End of Comments}
643 --
644 -- -----------------------------------------------------------------------
645 procedure chk_position_definition_id
646 (p_position_definition_id in number,
647 p_position_id in number default null,
648 p_object_version_number in number default null
649 ) is
650 --
651 l_proc varchar2(72) := g_package||'chk_position_definition_id';
652 l_exists varchar2(1);
653 l_api_updating boolean;
654 --
655 cursor csr_pos_def is
656 select 'x'
657 from per_position_definitions
658 where position_definition_id = p_position_definition_id;
659 --
660 begin
661 hr_utility.set_location('Entering:'||l_proc, 1);
662 --
663 -- Check mandatory parameters have been set
664 --
665 hr_api.mandatory_arg_error
666 (p_api_name => l_proc
667 ,p_argument => 'position_definition_id'
668 ,p_argument_value => p_position_definition_id
669 );
670 --
671 hr_utility.set_location(l_proc, 2);
672 --
673 l_api_updating := per_pos_shd.api_updating
674 (p_position_id => p_position_id
675 ,p_object_version_number => p_object_version_number);
676 --
677 hr_utility.set_location(l_proc, 3);
678 --
679 if ((l_api_updating and
680 (per_pos_shd.g_old_rec.position_definition_id <>
681 p_position_definition_id)) or
682 (NOT l_api_updating)) then
683 --
684 hr_utility.set_location(l_proc, 4);
685 --
686 open csr_pos_def;
687 fetch csr_pos_def into l_exists;
688 if csr_pos_def%notfound then
689 hr_utility.set_message(801,'HR_51369_POS_DEF_NOT_EXIST');
690 hr_utility.raise_error;
691 end if;
692 close csr_pos_def;
693 --
694 end if;
695 hr_utility.set_location('Leaving '||l_proc, 5);
696 --
697 end chk_position_definition_id;
698 --
699 -- ---------------------------------------------------------------------------
700 -- |---------------------------< chk_dates >--------------------------------|
701 -- ---------------------------------------------------------------------------
702 --
703 -- Desciption :
704 --
705 -- Validates DATE_EFFECTIVE is not null
706 --
707 -- Validates that DATE_EFFECTIVE is less than or equal to the value for
708 -- DATE_END on the same POSITION record
709 --
710 -- Pre-conditions:
711 -- Format of p_date_effective must be correct
712 --
713 -- In Arguments :
714 -- p_position_id
715 -- p_date_effective
716 -- p_date_end
717 -- p_object_version_number
718 --
719 -- Post Success :
720 -- If the above business rules are satisfied, processing continues
721 --
722 -- Post Failure :
723 -- If the above business rules are violated, an application error
724 -- is raised and processing terminates
725 --
726 -- Access Status :
727 -- Internal Table Handler Use only.
728 --
729 -- {End of Comments}
730 --
731 -- ---------------------------------------------------------------------------
732 procedure chk_dates
733 (p_position_id in number default null
737 --
734 ,p_date_effective in date
735 ,p_date_end in date
736 ,p_object_version_number in number default null) is
738 l_proc varchar2(72) := g_package||'chk_dates';
739 l_api_updating boolean;
740 --
741 begin
742 hr_utility.set_location('Entering:'||l_proc, 1);
743 --
744 -- Check mandatory parameters have been set
745 --
746 hr_api.mandatory_arg_error
747 (p_api_name => l_proc
748 ,p_argument => 'date_effective'
749 ,p_argument_value => p_date_effective
750 );
751 hr_utility.set_location(l_proc, 2);
752 --
753 -- Only proceed with validation if :
754 -- a) The current g_old_rec is current and
755 -- b) The date_end value has changed
756 --
757 l_api_updating := per_pos_shd.api_updating
758 (p_position_id => p_position_id
759 ,p_object_version_number => p_object_version_number);
760 --
761 if (((l_api_updating and
762 (per_pos_shd.g_old_rec.date_end <> p_date_end) or
763 (per_pos_shd.g_old_rec.date_effective <> p_date_effective)) or
764 (NOT l_api_updating))) then
765 --
766 -- Check that date_effective <= date_end
767 --
768 hr_utility.set_location(l_proc, 3);
769 --
770 if p_date_effective > nvl(p_date_end,hr_api.g_eot) then
771 hr_utility.set_message(801,'HR_51362_POS_INVAL_EFF_DATE');
772 hr_utility.raise_error;
773 end if;
774 --
775 end if;
776 --
777 hr_utility.set_location(' Leaving:'||l_proc, 4);
778 end chk_dates;
779 --
780 -- ---------------------------------------------------------------------------
781 -- |-------------------------< chk_hrs_frequency >-------------------------|
782 -- ---------------------------------------------------------------------------
783 --
784 -- Desciption :
785 --
786 -- Validate that if the values for WORKING_HOURS and FREQUENCY are null that
787 -- the values are defaulted from HR_ORGANIZATION_UNITS for the position's
788 -- ORGANIZATION_ID. When organization defaults are not maintained, the
789 -- default values from the business group are used.
790 --
791 -- Validate that if FREQUENCY is null and WORKING_HOURS is not null
792 -- or if WORKING_HOURS is null and FREQUENCY is not null an error
793 -- is raised
794 --
795 -- Validate the FREQUENCY value against the table
796 -- FND_COMMON_LOOKUPS where the LOOKUP_TYPE is 'FREQUENCY'. (I,U)
797 --
798 -- Validate that if the value for WORKING_HOURS is NOT NULL,
799 -- that the FREQUENCY value is valid for the WORKING_HOURS value.
800 --
801 --
802 -- Pre-conditions:
803 -- None
804 --
805 -- In Arguments :
806 -- p_business_group_id
807 -- p_organization_id
808 -- p_position_id
809 -- p_working_hours
810 -- p_frequency
811 -- p_object_version_number
812 --
813 -- Post Success :
814 -- If the above business rules are satisfied, processing continues
815 --
816 -- Post Failure :
817 -- If the above business rules are violated, an application error
818 -- is raised and processing terminates
819 --
820 -- Access Status :
821 -- Internal Table Handler Use only.
822 --
823 -- {End of Comments}
824 --
825 -- ---------------------------------------------------------------------------
826 procedure chk_hrs_frequency
827 (p_position_id in number default null
828 ,p_working_hours in number
829 ,p_frequency in varchar2
830 ,p_object_version_number in number default null) is
831 --
832 l_proc varchar2(72) := g_package||'chk_hrs_frequency';
833 l_exists varchar2(1);
834 l_working_hours number;
835 l_frequency varchar2(30);
836 l_api_updating boolean;
837 --
838 cursor csr_valid_freq is
839 select 'x'
840 from fnd_common_lookups
841 where lookup_type = 'FREQUENCY'
842 and lookup_code = p_frequency
843 and enabled_flag = 'Y';
844 --
845 --
846 begin
847 hr_utility.set_location('Entering:'||l_proc, 1);
848 --
849 -- Only proceed with validation if :
850 -- a) The current g_old_rec is current and
851 -- b) The working hours value has changed or
852 -- c) The frequency value has changed
853 --
854 --
855 l_api_updating := per_pos_shd.api_updating
856 (p_position_id => p_position_id
857 ,p_object_version_number => p_object_version_number);
858 --
859 if ((l_api_updating and
860 (nvl(per_pos_shd.g_old_rec.working_hours,hr_api.g_number) <>
861 nvl(p_working_hours,hr_api.g_number) or
862 (nvl(per_pos_shd.g_old_rec.frequency,hr_api.g_varchar2) <>
863 nvl(p_frequency,hr_api.g_varchar2)))) or
864 (NOT l_api_updating)) then
865 --
866 -- Check for values consistency
867 --
868 hr_utility.set_location(l_proc, 5);
869 --
870 if ((p_working_hours is null and p_frequency is not null) or
871 (p_working_hours is not null and p_frequency is null)) then
872 fnd_message.set_name('PER','PER_52981_POS_WORK_FREQ_NULL');
873 fnd_message.raise_error;
874 end if;
875 --
876 -- Check for valid frequency against fnd_common_lookups
877 --
881
878 hr_utility.set_location(l_proc, 6);
879 --
880 if p_frequency is not null then
882 open csr_valid_freq;
883 fetch csr_valid_freq into l_exists;
884 if csr_valid_freq%notfound then
885 hr_utility.set_message(801,'HR_51363_POS_INVAL_FREQUENCY');
886 hr_utility.raise_error;
887 end if;
888 --
889 -- Validate combinations of working_hours and frequency
890 --
891 hr_utility.set_location(l_proc, 7);
892 --
893 if ((p_working_hours > 24 AND p_frequency = 'D') or
894 ((p_working_hours > 168)
895 and (p_frequency = 'W')) or
896 ((p_working_hours > 744)
897 and (p_frequency = 'M')) or
898 ((p_working_hours > 8784)
899 and (p_frequency = 'Y'))) then
900 hr_utility.set_message(800,'HR_POS_2_MANY_HOURS');
901 hr_utility.raise_error;
902 end if;
903 --
904 end if;
905 --
906 end if;
907 hr_utility.set_location(' Leaving:'||l_proc, 8);
908 end chk_hrs_frequency;
909 --
910 -- ---------------------------------------------------------------------------
911 -- |---------------------< chk_probation_info >---------------------------|
912 -- ---------------------------------------------------------------------------
913 --
914 -- Desciption :
915 --
916 -- Validate that if the PROBATION_PERIOD is null and PROBATION_PERIOD_UNITS
917 -- is not null or if PROBATION_PERIOD is not null and PROBATION_PERIOS_UNITS
918 -- is null then an error is raised
919 --
920 -- Validate the value for PROBATION_PERIOD_UNITS against the table
921 -- FND_COMMON_LOOKUPS where the LOOKUP_TYPE is 'QUALIFYING_UNITS'.
922 --
923 -- Pre-conditions:
924 -- None
925 --
926 -- In Arguments :
927 -- p_position_id
928 -- p_probation_period
929 -- p_probation_period_units
930 -- p_object_version_number
931 --
932 -- Post Success :
933 -- If the above business rules are satisfied, processing continues
934 --
935 -- Post Failure :
936 -- If the above business rules are violated, an application error
937 -- is raised and processing terminates
938 --
939 -- Access Status :
940 -- Internal Table Handler Use only.
941 --
942 -- {End of Comments}
943 --
944 -- ---------------------------------------------------------------------------
945 procedure chk_probation_info
946 (p_position_id in number default null
947 ,p_probation_period in number
948 ,p_probation_period_units in varchar2
949 ,p_object_version_number in number default null) is
950 --
951 l_proc varchar2(72) := g_package||'chk_probation_info';
952 l_api_updating boolean;
953 l_exists varchar2(1);
954 --
955 cursor csr_valid_unit is
956 select 'x'
957 from fnd_common_lookups
958 where lookup_type = 'QUALIFYING_UNITS'
959 and lookup_code = p_probation_period_units;
960 --
961 begin
962 hr_utility.set_location('Entering:'||l_proc, 1);
963 --
964 -- Only proceed with validation if :
965 -- a) The current g_old_rec is current and
966 -- b) The probation_period value has changed
967 -- c) The probation_period_units value has changed
968 --
969 l_api_updating := per_pos_shd.api_updating
970 (p_position_id => p_position_id
971 ,p_object_version_number => p_object_version_number);
972 --
973 if ((l_api_updating and
974 (nvl(per_pos_shd.g_old_rec.probation_period,hr_api.g_number) <>
975 nvl(p_probation_period,hr_api.g_number)) or
976 (nvl(per_pos_shd.g_old_rec.probation_period_units,hr_api.g_varchar2) <>
977 nvl(p_probation_period_units,hr_api.g_varchar2))) or
978 (NOT l_api_updating)) then
979 --
980 -- Check for values consistency
981 --
982 hr_utility.set_location(l_proc, 2);
983 --
984 if (p_probation_period is null and
985 p_probation_period_units is not null) or
986 (p_probation_period is not null and
987 p_probation_period_units is null) then
988 hr_utility.set_message(801,'HR_51365_POS_PROB_UNITS_REQ');
989 hr_utility.raise_error;
990 else
991 --
992 -- Validate probation_period_units against fnd_common_lookups
993 --
994 hr_utility.set_location(l_proc, 3);
995 --
996 if p_probation_period is not null
997 and p_probation_period_units is not null then
998 open csr_valid_unit;
999 fetch csr_valid_unit into l_exists;
1000 if csr_valid_unit%notfound then
1001 hr_utility.set_message(801,'HR_51366_POS_PROB_UNITS_INV');
1002 hr_utility.raise_error;
1003 end if;
1004 end if;
1005 end if;
1006 end if;
1007 --
1008 hr_utility.set_location(' Leaving:'||l_proc, 4);
1009 end chk_probation_info;
1010 --
1011 -- ---------------------------------------------------------------------------
1012 -- |--------------< chk_replacement_required_flag >-------------------------|
1013 -- ---------------------------------------------------------------------------
1014 --
1015 -- Desciption :
1016 --
1017 -- Validate that allowable values are ('Y','N') against
1018 -- FND_COMMON_LOOKUPS where lookup_type = 'YES_NO'
1019 --
1023 -- Pre-conditions:
1020 -- Validate that on insert, REPLACEMENT_REQUIRED_FLAG must be defaulted to
1021 -- 'N' when null
1022 --
1024 -- None
1025 --
1026 -- In Arguments :
1027 -- p_position_id
1028 -- p_replacement_required_flag
1029 -- p_object_version_number
1030 --
1031 -- Post Success :
1032 -- If the above business rules are satisfied, processing continues
1033 --
1034 -- Post Failure :
1035 -- If the above business rules are violated, an application error
1036 -- is raised and processing terminates
1037 --
1038 -- Access Status :
1039 -- Internal Table Handler Use only.
1040 --
1041 -- {End of Comments}
1042 -- ---------------------------------------------------------------------------
1043 procedure chk_replacement_flag
1044 (p_position_id in number default null
1045 ,p_replacement_required_flag in varchar2
1046 ,p_object_version_number in number default null) is
1047 --
1048 l_exists varchar2(1);
1049 l_proc varchar2(72) := g_package||'chk_replacement_flag';
1050 l_api_updating boolean;
1051 --
1052 cursor csr_valid_flag is
1053 select 'x'
1054 from fnd_common_lookups
1055 where lookup_type = 'YES_NO'
1056 and lookup_code = p_replacement_required_flag;
1057 --
1058 begin
1059 hr_utility.set_location('Entering:'||l_proc, 1);
1060 --
1061 -- Only proceed with validation if :
1062 -- a) The current g_old_rec is current and
1063 -- b) The replacement_required_flag value has changed
1064 --
1065 if p_replacement_required_flag is not null then
1066 l_api_updating := per_pos_shd.api_updating
1067 (p_position_id => p_position_id
1068 ,p_object_version_number => p_object_version_number);
1069 --
1070 if ((l_api_updating and
1071 (nvl(per_pos_shd.g_old_rec.replacement_required_flag,hr_api.g_varchar2) <>
1072 nvl(p_replacement_required_flag,hr_api.g_varchar2))) or
1073 (NOT l_api_updating)) then
1074 --
1075 -- Validate flag replacement_required_flag against fnd_common_lookups
1076 --
1077 hr_utility.set_location(l_proc, 3);
1078 --
1079 open csr_valid_flag;
1080 fetch csr_valid_flag into l_exists;
1081 if csr_valid_flag%notfound then
1082 hr_utility.set_message(801,'HR_51370_POS_REPL_REQ_FLAG');
1083 hr_utility.raise_error;
1084 end if;
1085 --
1086 end if;
1087 end if;
1088 --
1089 hr_utility.set_location(' Leaving:'||l_proc, 4);
1090 end chk_replacement_flag;
1091 --
1092 --
1093 -- ---------------------------------------------------------------------------
1094 -- |------------------< chk_time_start_finish >---------------------------|
1095 -- ---------------------------------------------------------------------------
1096 --
1097 -- Desciption :
1098 --
1099 -- Validate that TIME_NORMAL_FINISH is not before TIME_NORMAL_START.
1100 --
1101 -- Selects TIME_NORMAL_START and TIME_NORMAL_FINISH from the corresponding
1102 -- values on HR_ORGANIZATION_UNITS for the position's ORGANIZATION_ID when
1103 -- the values are null. When organization defaults are not maintained, the
1104 -- default values from the business group are used.
1105 --
1106 -- Pre-conditions:
1107 -- None
1108 --
1109 -- In Arguments :
1110 -- p_business_group_id
1111 -- p_organization_id
1112 -- p_position_id
1113 -- p_time_normal_start
1114 -- p_time_normal_finish
1115 -- p_object_version_number
1116 --
1117 -- Post Success :
1118 -- If the above business rules are satisfied, processing continues
1119 --
1120 -- Post Failure :
1121 -- If the above business rules are violated, an application error
1122 -- is raised and processing terminates
1123 --
1124 -- Access Status :
1125 -- Internal Table Handler Use only.
1126 --
1127 -- {End of Comments}
1128 --
1129 -- ---------------------------------------------------------------------------
1130 procedure chk_time_start_finish
1131 (p_position_id in number default null
1132 ,p_time_normal_start in varchar2
1133 ,p_time_normal_finish in varchar2
1134 ,p_object_version_number in number default null) is
1135 --
1136 l_exists varchar2(1);
1137 l_proc varchar2(72) := g_package||'chk_time_start_finish';
1138 l_time_normal_start varchar2(5);
1139 l_time_normal_finish varchar2(5);
1140 l_api_updating boolean;
1141 --
1142 begin
1143 hr_utility.set_location('Entering:'||l_proc, 1);
1144 --
1145 -- Only proceed with validation if :
1146 -- a) The current g_old_rec is current and
1147 -- b) The time_normal_start value has changed
1148 -- c) The time_normal_finish value has changed
1149 --
1150 l_api_updating := per_pos_shd.api_updating
1151 (p_position_id => p_position_id
1152 ,p_object_version_number => p_object_version_number);
1153 --
1154 if ((l_api_updating and
1155 (nvl(per_pos_shd.g_old_rec.time_normal_start,hr_api.g_varchar2) <>
1156 nvl(p_time_normal_start,hr_api.g_varchar2) or
1157 (nvl(per_pos_shd.g_old_rec.time_normal_finish,hr_api.g_varchar2) <>
1158 nvl(p_time_normal_finish,hr_api.g_varchar2)))) or
1159 (NOT l_api_updating)) then
1160 --
1161 -- Check for values consistency
1162 --
1163 hr_utility.set_location(l_proc, 4);
1164 --
1168 hr_utility.raise_error;
1165 if (p_time_normal_start is not null and p_time_normal_finish is null) or
1166 (p_time_normal_start is null and p_time_normal_finish is not null) then
1167 hr_utility.set_message(801,'HR_51367_POS_TIMES_REQ');
1169 --
1170 elsif not (substr(p_time_normal_start,1,2) between '00' and '24'
1171 and substr(p_time_normal_start,4,2) between '00' and '59'
1172 and substr(p_time_normal_start,3,1) = ':') then
1173 hr_utility.set_message(801,'HR_51154_INVAL_TIME_FORMAT');
1174 hr_utility.raise_error;
1175 --
1176 elsif not (substr(p_time_normal_finish,1,2) between '00' and '24'
1177 and substr(p_time_normal_finish,4,2) between '00' and '59'
1178 and substr(p_time_normal_finish,3,1) = ':') then
1179 hr_utility.set_message(801,'HR_51154_INVAL_TIME_FORMAT');
1180 hr_utility.raise_error;
1181 end if;
1182 --
1183 -- Check that time_normal_start <= time_normal_finish
1184 --
1185 hr_utility.set_location(l_proc, 5);
1186 --
1187 /*
1188 if p_time_normal_finish < p_time_normal_start then
1189 hr_utility.set_message(801,'HR_51368_POS_FIN_GT_START');
1190 hr_utility.raise_error;
1191 end if;
1192 */
1193 --
1194 end if;
1195 --
1196 hr_utility.set_location(' Leaving:'||l_proc, 6);
1197 end chk_time_start_finish;
1198 --
1199 -- ---------------------------------------------------------------------------
1200 -- |-------------------------< chk_status >--------------------------------|
1201 -- ---------------------------------------------------------------------------
1202 --
1203 -- Desciption :
1204 --
1205 -- Validate the STATUS value against the table
1206 -- FND_COMMON_LOOKUPS where the LOOKUP_TYPE is 'POSITION_STATUS'. (I,U)
1207 --
1208 --
1209 -- Pre-conditions:
1210 -- None
1211 --
1212 -- In Arguments :
1213 -- p_position_id
1214 -- p_date_effective
1215 -- p_status
1216 -- p_object_version_number
1217 --
1218 -- Post Success :
1219 -- If the above business rules are satisfied, processing continues
1220 --
1221 -- Post Failure :
1222 -- If the above business rules are violated, an application error
1223 -- is raised and processing terminates
1224 --
1225 -- Access Status :
1226 -- Internal Table Handler Use only.
1227 --
1228 -- {End of Comments}
1229 --
1230 -- ---------------------------------------------------------------------------
1231 procedure chk_status
1232 (p_position_id in number default null
1233 ,p_date_effective in date
1234 ,p_status in varchar2
1235 ,p_object_version_number in number default null) is
1236 --
1237 l_proc varchar2(72) := g_package||'chk_status';
1238 l_exists varchar2(1);
1239 l_date_effective date;
1240 l_status varchar2(30);
1241 l_api_updating boolean;
1242 --
1243 begin
1244 hr_utility.set_location('Entering:'||l_proc, 1);
1245 --
1246 -- Only proceed with validation if :
1247 -- a) The current g_old_rec is current and
1248 -- b) The status value has changed or
1249 -- c) Inserting
1250 --
1251 l_api_updating := per_pos_shd.api_updating
1252 (p_position_id => p_position_id
1253 ,p_object_version_number => p_object_version_number);
1254 --
1255 if ((l_api_updating and
1256 (nvl(per_pos_shd.g_old_rec.status,hr_api.g_varchar2) <>
1257 nvl(p_status,hr_api.g_varchar2))) or
1258 (NOT l_api_updating)) then
1259 --
1260 -- Check for valid status against fnd_common_lookups
1261 --
1262 hr_utility.set_location(l_proc, 2);
1263 --
1264 if p_status is not null and
1265 hr_api.not_exists_in_hr_lookups
1266 (p_effective_date => p_date_effective
1267 ,p_lookup_type => 'POSITION_STATUS'
1268 ,p_lookup_code => p_status
1269 )
1270 then
1271 hr_utility.set_message(801,'PER_51870_POS_STATUS_INV');
1272 hr_utility.raise_error;
1273 end if;
1274 --
1275 end if;
1276 --
1277 hr_utility.set_location(' Leaving:'||l_proc, 3);
1278 end chk_status;
1279 -- -----------------------------------------------------------------------
1280 -- |------------------------------< chk_df >-----------------------------|
1281 -- -----------------------------------------------------------------------
1282 --
1283 -- Description:
1284 -- Validates the all Descriptive Flexfield values.
1285 --
1286 -- Pre-conditions:
1287 -- All other columns have been validated. Must be called as the
1288 -- last step from insert_validate and update_validate.
1289 --
1290 -- In Arguments:
1291 -- p_rec
1292 --
1293 -- Post Success:
1294 -- If the Descriptive Flexfield structure column and data values are
1295 -- all valid this procedure will end normally and processing will
1296 -- continue.
1297 --
1298 -- Post Failure:
1299 -- If the Descriptive Flexfield structure column value or any of
1300 -- the data values are invalid then an application error is raised as
1301 -- a PL/SQL exception.
1302 --
1303 -- Access Status:
1304 -- Internal Row Handler Use Only.
1305 --
1306 procedure chk_df
1307 (p_rec in per_pos_shd.g_rec_type) is
1308 --
1312 hr_utility.set_location('Entering:'||l_proc, 10);
1309 l_proc varchar2(72) := g_package||'chk_df';
1310 --
1311 begin
1313 --
1314 if ((p_rec.position_id is not null) and (
1315 nvl(per_pos_shd.g_old_rec.attribute_category, hr_api.g_varchar2) <>
1316 nvl(p_rec.attribute_category, hr_api.g_varchar2) or
1317 nvl(per_pos_shd.g_old_rec.attribute1, hr_api.g_varchar2) <>
1318 nvl(p_rec.attribute1, hr_api.g_varchar2) or
1319 nvl(per_pos_shd.g_old_rec.attribute2, hr_api.g_varchar2) <>
1320 nvl(p_rec.attribute2, hr_api.g_varchar2) or
1321 nvl(per_pos_shd.g_old_rec.attribute3, hr_api.g_varchar2) <>
1322 nvl(p_rec.attribute3, hr_api.g_varchar2) or
1323 nvl(per_pos_shd.g_old_rec.attribute4, hr_api.g_varchar2) <>
1324 nvl(p_rec.attribute4, hr_api.g_varchar2) or
1325 nvl(per_pos_shd.g_old_rec.attribute5, hr_api.g_varchar2) <>
1326 nvl(p_rec.attribute5, hr_api.g_varchar2) or
1327 nvl(per_pos_shd.g_old_rec.attribute6, hr_api.g_varchar2) <>
1328 nvl(p_rec.attribute6, hr_api.g_varchar2) or
1329 nvl(per_pos_shd.g_old_rec.attribute7, hr_api.g_varchar2) <>
1330 nvl(p_rec.attribute7, hr_api.g_varchar2) or
1331 nvl(per_pos_shd.g_old_rec.attribute8, hr_api.g_varchar2) <>
1332 nvl(p_rec.attribute8, hr_api.g_varchar2) or
1333 nvl(per_pos_shd.g_old_rec.attribute9, hr_api.g_varchar2) <>
1334 nvl(p_rec.attribute9, hr_api.g_varchar2) or
1335 nvl(per_pos_shd.g_old_rec.attribute10, hr_api.g_varchar2) <>
1336 nvl(p_rec.attribute10, hr_api.g_varchar2) or
1337 nvl(per_pos_shd.g_old_rec.attribute11, hr_api.g_varchar2) <>
1338 nvl(p_rec.attribute11, hr_api.g_varchar2) or
1339 nvl(per_pos_shd.g_old_rec.attribute12, hr_api.g_varchar2) <>
1340 nvl(p_rec.attribute12, hr_api.g_varchar2) or
1341 nvl(per_pos_shd.g_old_rec.attribute13, hr_api.g_varchar2) <>
1342 nvl(p_rec.attribute13, hr_api.g_varchar2) or
1343 nvl(per_pos_shd.g_old_rec.attribute14, hr_api.g_varchar2) <>
1344 nvl(p_rec.attribute14, hr_api.g_varchar2) or
1345 nvl(per_pos_shd.g_old_rec.attribute15, hr_api.g_varchar2) <>
1346 nvl(p_rec.attribute15, hr_api.g_varchar2) or
1347 nvl(per_pos_shd.g_old_rec.attribute16, hr_api.g_varchar2) <>
1348 nvl(p_rec.attribute16, hr_api.g_varchar2) or
1349 nvl(per_pos_shd.g_old_rec.attribute17, hr_api.g_varchar2) <>
1350 nvl(p_rec.attribute17, hr_api.g_varchar2) or
1351 nvl(per_pos_shd.g_old_rec.attribute18, hr_api.g_varchar2) <>
1352 nvl(p_rec.attribute18, hr_api.g_varchar2) or
1353 nvl(per_pos_shd.g_old_rec.attribute19, hr_api.g_varchar2) <>
1354 nvl(p_rec.attribute19, hr_api.g_varchar2) or
1355 nvl(per_pos_shd.g_old_rec.attribute20, hr_api.g_varchar2) <>
1356 nvl(p_rec.attribute20, hr_api.g_varchar2)))
1357 or
1358 (p_rec.position_id is null) then
1359 --
1360 -- Only execute the validation if absolutely necessary:
1361 -- a) During update, the structure column value or any
1362 -- of the attribute values have actually changed.
1363 -- b) During insert.
1364 --
1365 hr_dflex_utility.ins_or_upd_descflex_attribs
1366 (p_appl_short_name => 'PER'
1367 ,p_descflex_name => 'PER_POSITIONS'
1368 ,p_attribute_category => p_rec.attribute_category
1369 ,p_attribute1_name => 'ATTRIBUTE1'
1370 ,p_attribute1_value => p_rec.attribute1
1371 ,p_attribute2_name => 'ATTRIBUTE2'
1372 ,p_attribute2_value => p_rec.attribute2
1373 ,p_attribute3_name => 'ATTRIBUTE3'
1374 ,p_attribute3_value => p_rec.attribute3
1375 ,p_attribute4_name => 'ATTRIBUTE4'
1376 ,p_attribute4_value => p_rec.attribute4
1377 ,p_attribute5_name => 'ATTRIBUTE5'
1378 ,p_attribute5_value => p_rec.attribute5
1379 ,p_attribute6_name => 'ATTRIBUTE6'
1380 ,p_attribute6_value => p_rec.attribute6
1381 ,p_attribute7_name => 'ATTRIBUTE7'
1382 ,p_attribute7_value => p_rec.attribute7
1383 ,p_attribute8_name => 'ATTRIBUTE8'
1384 ,p_attribute8_value => p_rec.attribute8
1385 ,p_attribute9_name => 'ATTRIBUTE9'
1386 ,p_attribute9_value => p_rec.attribute9
1387 ,p_attribute10_name => 'ATTRIBUTE10'
1388 ,p_attribute10_value => p_rec.attribute10
1389 ,p_attribute11_name => 'ATTRIBUTE11'
1390 ,p_attribute11_value => p_rec.attribute11
1391 ,p_attribute12_name => 'ATTRIBUTE12'
1392 ,p_attribute12_value => p_rec.attribute12
1393 ,p_attribute13_name => 'ATTRIBUTE13'
1394 ,p_attribute13_value => p_rec.attribute13
1395 ,p_attribute14_name => 'ATTRIBUTE14'
1396 ,p_attribute14_value => p_rec.attribute14
1397 ,p_attribute15_name => 'ATTRIBUTE15'
1398 ,p_attribute15_value => p_rec.attribute15
1399 ,p_attribute16_name => 'ATTRIBUTE16'
1400 ,p_attribute16_value => p_rec.attribute16
1401 ,p_attribute17_name => 'ATTRIBUTE17'
1402 ,p_attribute17_value => p_rec.attribute17
1403 ,p_attribute18_name => 'ATTRIBUTE18'
1404 ,p_attribute18_value => p_rec.attribute18
1405 ,p_attribute19_name => 'ATTRIBUTE19'
1406 ,p_attribute19_value => p_rec.attribute19
1407 ,p_attribute20_name => 'ATTRIBUTE20'
1408 ,p_attribute20_value => p_rec.attribute20
1409 );
1410 end if;
1411 --
1412 hr_utility.set_location(' Leaving:'||l_proc, 20);
1413 end chk_df;
1414 --
1415 --
1416 -- ----------------------------------------------------------------------------
1417 -- |--------------------------< chk_ccid_unique_for_BG >--------------------|
1418 -- ----------------------------------------------------------------------------
1419 --
1423 --
1420 -- PMFLETCH - New uniqueness validation routine
1421 --
1422 -- Desciption :
1424 -- Validates that the POSITION_DEFINITION_ID is unique within a
1425 -- position's BUSINESS GROUP
1426 --
1427 -- Pre-conditions :
1428 --
1429 -- In Arguments :
1430 -- p_business_group_id
1431 -- p_position_id
1432 -- p_position_definition_id
1433 --
1434 -- Post Success :
1435 -- If the POSITION_DEFINITION_ID in PER_ALL_POSITIONS table does not exist
1436 -- for given BUSINESS_GROUP_ID then processing continues
1437 --
1438 -- Post Failure :
1439 -- If the POSITION_DEFINITION_ID does exist in PER_ALL_POSITIONS table for given
1440 -- BUSINESS_GROUP_ID, then an application error will be raised and processing
1441 -- terminated
1442 --
1443 -- Access Status :
1444 -- Internal Table Handler Use only.
1445 --
1446 -- {End of Comments}
1447 --
1448 -- ---------------------------------------------------------------------------
1449 --
1450 procedure chk_ccid_unique_for_BG
1451 (p_business_group_id in number
1452 ,p_position_id in number
1453 ,p_position_definition_id in number
1454 ,p_object_version_number in number
1455 ) is
1456 --
1457 l_api_updating boolean;
1458 l_exists varchar2(1);
1459 l_proc varchar2(72) ;
1460 --
1461 -- Check there are no records in this business group that have the same
1462 -- position definition id - except for the current position
1463 cursor csr_ccid_unique is
1464 SELECT 'x'
1465 from dual
1466 where exists
1467 (select null
1468 from hr_all_positions_f pos
1469 where pos.business_group_id = p_business_group_id
1470 and pos.position_definition_id = p_position_definition_id
1471 and pos.position_id <> nvl(p_position_id, -1)
1472 and hr_general.effective_date
1473 between pos.effective_start_date and pos.effective_end_date
1474 ) ;
1475 --
1476 begin
1477 --if g_debug then
1478 l_proc := g_package||'chk_ccid_unique_for_BG';
1479 hr_utility.set_location('Entering:'||l_proc, 10);
1480 --end if;
1481 --
1482 -- Check mandatory parameters have been set
1483 --
1484 hr_api.mandatory_arg_error
1485 (p_api_name => l_proc
1486 ,p_argument => 'business_group_id'
1487 ,p_argument_value => p_business_group_id
1488 );
1489 --if g_debug then
1490 hr_utility.set_location(l_proc, 20);
1491 --end if;
1492 --
1493 hr_api.mandatory_arg_error
1494 (p_api_name => l_proc
1495 ,p_argument => 'position_definition_id'
1496 ,p_argument_value => p_position_definition_id
1497 );
1498 --if g_debug then
1499 hr_utility.set_location(l_proc, 30);
1500 --end if;
1501 --
1502 l_api_updating := per_pos_shd.api_updating
1503 (p_position_id => p_position_id
1504 ,p_object_version_number => p_object_version_number
1505 );
1506 --if g_debug then
1507 hr_utility.set_location(l_proc, 70);
1508 --end if;
1509 --
1510 if (l_api_updating and
1511 (nvl(per_pos_shd.g_old_rec.position_definition_id, hr_api.g_number)
1512 <> nvl(p_position_definition_id, hr_api.g_number))
1513 )
1514 or NOT l_api_updating
1515 then
1516 --if g_debug then
1517 hr_utility.set_location(l_proc, 80);
1518 --end if;
1519 --
1520 -- Check for unique ccid
1521 --
1522 open csr_ccid_unique;
1523 fetch csr_ccid_unique into l_exists;
1524 if csr_ccid_unique%found then
1525 close csr_ccid_unique;
1526 hr_utility.set_message(801,'PAY_7688_USER_POS_TAB_UNIQUE');
1527 hr_utility.raise_error;
1528 else
1529 close csr_ccid_unique;
1530 --if g_debug then
1531 hr_utility.set_location(l_proc, 90);
1532 --end if;
1533 end if;
1534 --
1535 end if;
1536 --
1537 --if g_debug then
1538 hr_utility.set_location(' Leaving:'||l_proc, 100);
1539 --end if;
1540 --
1541 end chk_ccid_unique_for_BG;
1542 --
1543 --
1544 -- ----------------------------------------------------------------------------
1545 -- |--------------------------< chk_name_unique_for_BG >--------------------|
1546 -- ----------------------------------------------------------------------------
1547 --
1548 -- Desciption :
1549 --
1550 -- Validates that the position NAME is unique within position's BUSINESS GROUP
1551 --
1552 -- Pre-conditions :
1553 --
1554 -- In Arguments :
1555 -- p_business_group_id
1556 -- p_position_id
1557 -- p_name
1558 --
1559 -- Post Success :
1560 -- If the NAME in PER_POSITIONS table does not exist for given BUSINESS_GROUP_ID
1561 -- then processing continues
1562 --
1563 -- Post Failure :
1564 -- If the NAME does exist in PER_POSITIONS table for given BUSINESS_GROUP_ID,
1565 -- then an application error will be raised and processing terminated
1566 --
1567 -- Access Status :
1568 -- Internal Table Handler Use only.
1569 --
1570 -- {End of Comments}
1571 --
1575 (p_business_group_id in number
1572 -- ---------------------------------------------------------------------------
1573 --
1574 procedure chk_name_unique_for_BG
1576 ,p_position_id in number
1577 ,p_name in varchar2
1578 ) is
1579 --
1580 l_exists varchar2(1);
1581 l_proc varchar2(72) := g_package||'chk_name_unique_for_BG';
1582 --
1583 cursor csr_name_unique is
1584 select 'x'
1585 from per_all_positions posn
1586 where posn.name = p_name
1587 and (p_position_id is null or posn.position_id <> p_position_id)
1588 and posn.business_group_id = p_business_group_id;
1589 --
1590 --
1591 Begin
1592 hr_utility.set_location('Entering:'||l_proc, 1);
1593 --
1594 -- Check mandatory parameters have been set
1595 --
1596 hr_api.mandatory_arg_error
1597 (p_api_name => l_proc
1598 ,p_argument => 'business_group_id'
1599 ,p_argument_value => p_business_group_id
1600 );
1601 hr_utility.set_location(l_proc, 2);
1602 --
1603 hr_api.mandatory_arg_error
1604 (p_api_name => l_proc
1605 ,p_argument => 'name'
1606 ,p_argument_value => p_name
1607 );
1608 hr_utility.set_location(l_proc, 3);
1609 --
1610 -- Check for unique name
1611 --
1612 -- Added If statement to ensure an selective open of cursor
1613 -- Bug 892165
1614 -- Amended changed this to p_name
1615
1616 IF ((( p_name IS NOT NULL ) and
1617 NVL(per_pos_shd.g_old_rec.name,hr_api.g_varchar2)
1618 <> NVL(p_name,hr_api.g_varchar2))
1619 OR ( p_name IS NULL)) THEN
1620
1621 open csr_name_unique;
1622 fetch csr_name_unique into l_exists;
1623 if csr_name_unique%found then
1624 close csr_name_unique;
1625 hr_utility.set_message(801,'PAY_7688_USER_POS_TAB_UNIQUE');
1626 hr_utility.raise_error;
1627 else
1628 close csr_name_unique;
1629 end if;
1630
1631 END IF;
1632 --
1633 hr_utility.set_location(' Leaving:'||l_proc, 4);
1634 end chk_name_unique_for_BG;
1635 --
1636 -- ----------------------------------------------------------------------------
1637 -- |---------------------------< insert_validate >----------------------------|
1638 -- ----------------------------------------------------------------------------
1639 Procedure insert_validate(p_rec in per_pos_shd.g_rec_type) is
1640 --
1641 l_proc varchar2(72) := g_package||'insert_validate';
1642 --
1643 Begin
1644 hr_utility.set_location('Entering:'||l_proc, 5);
1645 --
1646 -- Call all supporting business operations
1647 --
1648 -- Validate Business Group
1649 --
1650 hr_api.validate_bus_grp_id(p_rec.business_group_id);
1651 --
1652 hr_utility.set_location(l_proc, 6);
1653 --
1654 --
1655 -- Validate date effective and date_end
1656 --
1657 chk_dates
1658 (p_date_effective => p_rec.date_effective,
1659 p_date_end => p_rec.date_end
1660 );
1661 -- Validate job id
1662 --
1663 chk_job_id
1664 (p_job_id => p_rec.job_id,
1665 p_date_effective => p_rec.date_effective,
1666 p_business_group_id => p_rec.business_group_id
1667 );
1668 --
1669 hr_utility.set_location(l_proc, 7);
1670 --
1671 -- Validate organization id
1672 --
1673 chk_organization_id
1674 (p_organization_id => p_rec.organization_id,
1675 p_date_effective => p_rec.date_effective,
1676 p_business_group_id => p_rec.business_group_id
1677 );
1678 --
1679 hr_utility.set_location(l_proc, 8);
1680 --
1681 -- Validate successor position id
1682 --
1683 chk_successor_position_id
1684 (p_business_group_id => p_rec.business_group_id,
1685 p_successor_position_id => p_rec.successor_position_id,
1686 p_date_effective => p_rec.date_effective
1687 );
1688 --
1689 hr_utility.set_location(l_proc, 9);
1690 --
1691 -- Validate relief position id
1692 --
1693 chk_relief_position_id
1694 (p_business_group_id => p_rec.business_group_id,
1695 p_relief_position_id => p_rec.relief_position_id,
1696 p_date_effective => p_rec.date_effective
1697 );
1698 --
1699 hr_utility.set_location(l_proc, 10);
1700 --
1701 -- Validate location_id
1702 --
1703 chk_location_id
1704 (p_location_id => p_rec.location_id,
1705 p_date_effective => p_rec.date_effective
1706 );
1707 --
1708 hr_utility.set_location(l_proc, 10);
1709 --
1710 -- Validate position definition id
1711 --
1712 chk_position_definition_id
1713 (p_position_definition_id => p_rec.position_definition_id
1714 );
1715 --
1716 hr_utility.set_location(l_proc, 11);
1717 --
1718 -- Validate working_hours and frequency
1719 --
1720 chk_hrs_frequency
1721 (p_working_hours => p_rec.working_hours,
1722 p_frequency => p_rec.frequency
1723 );
1724 --
1725 hr_utility.set_location(l_proc, 15);
1726 --
1727 -- Validate probation period and probation_period_units
1728 --
1729 chk_probation_info
1730 (p_probation_period => p_rec.probation_period,
1731 p_probation_period_units => p_rec.probation_period_units
1732 );
1733 --
1734 hr_utility.set_location(l_proc, 16);
1735 --
1739 (p_time_normal_start => p_rec.time_normal_start,
1736 -- Validate time normal start and time_normal_finish
1737 --
1738 chk_time_start_finish
1740 p_time_normal_finish => p_rec.time_normal_finish
1741 );
1742 --
1743 chk_replacement_flag
1744 (p_replacement_required_flag => p_rec.replacement_required_flag
1745 );
1746 --
1747 -- Validate status
1748 --
1749 chk_status
1750 (p_position_id => p_rec.position_id,
1751 p_date_effective => p_rec.date_effective,
1752 p_status => p_rec.status,
1753 p_object_version_number => p_rec.object_version_number
1754 );
1755 -- Moved the next 11 lines to be before chk_df call
1756 -- Bug 892165
1757 --
1758 hr_utility.set_location(l_proc, 18);
1759 --
1760 -- PMFLETCH ** Not using this uniqueness check anymore **
1761 -- Check position_name is unique for Business_group
1762 --
1763 --chk_name_unique_for_BG
1764 -- (p_business_group_id => p_rec.business_group_id,
1765 -- p_position_id => p_rec.position_id,
1766 -- p_name => p_rec.name
1767 --);
1768 --
1769 -- PMFLETCH Check position_definition_id is unique for business group
1770 --
1771 chk_ccid_unique_for_BG
1772 (p_business_group_id => p_rec.business_group_id
1773 ,p_position_id => p_rec.position_id
1774 ,p_position_definition_id => p_rec.position_definition_id
1775 ,p_object_version_number => p_rec.object_version_number
1776 );
1777 --
1778 --
1779 hr_utility.set_location(l_proc, 19);
1780 --
1781 --
1782 -- Call descriptive flexfield validation routines
1783 --
1784 -- per_pos_bus.chk_df(p_rec => p_rec);
1785 --
1786 hr_utility.set_location(' Leaving:'||l_proc, 20);
1787 --
1788 End insert_validate;
1789
1790 -- ----------------------------------------------------------------------------
1791 -- |---------------------------< update_validate >----------------------------|
1792 -- ----------------------------------------------------------------------------
1793 Procedure update_validate(p_rec in per_pos_shd.g_rec_type) is
1794 --
1795 l_proc varchar2(72) := g_package||'update_validate';
1796 --
1797 Begin
1798 hr_utility.set_location('Entering:'||l_proc, 5);
1799 -- Bug 892165
1800 -- Validate Business Group
1801 --
1802 hr_api.validate_bus_grp_id(p_rec.business_group_id);
1803 --
1804 -- Call to chk_non_updateable_args - Bug 892165
1805 --
1806 hr_utility.set_location(l_proc, 6);
1807 chk_non_updateable_args(p_rec => p_rec);
1808 --
1809 -- Call all supporting business operations
1810 --
1811 -- Validate date effective
1812 --
1813 chk_dates
1814 (p_position_id => p_rec.position_id,
1815 p_date_effective => p_rec.date_effective,
1816 p_date_end => p_rec.date_end,
1817 p_object_version_number => p_rec.object_version_number
1818 );
1819 -- Validate successor position id
1820 --
1821 chk_successor_position_id
1822 (p_business_group_id => p_rec.business_group_id,
1823 p_position_id => p_rec.position_id,
1824 p_successor_position_id => p_rec.successor_position_id,
1825 p_date_effective => p_rec.date_effective,
1826 p_object_version_number => p_rec.object_version_number
1827 );
1828 --
1829 hr_utility.set_location(l_proc, 9);
1830 --
1831 -- Validate relief position id
1832 --
1833 chk_relief_position_id
1834 (p_business_group_id => p_rec.business_group_id,
1835 p_position_id => p_rec.position_id,
1836 p_relief_position_id => p_rec.relief_position_id,
1837 p_date_effective => p_rec.date_effective,
1838 p_object_version_number => p_rec.object_version_number
1839 );
1840 --
1841 hr_utility.set_location(l_proc, 10);
1842 --
1843 -- Validate location_id
1844 --
1845 chk_location_id
1846 (p_position_id => p_rec.position_id,
1847 p_location_id => p_rec.location_id,
1848 p_date_effective => p_rec.date_effective,
1849 p_object_version_number => p_rec.object_version_number
1850 );
1851 --
1852 hr_utility.set_location(l_proc, 12);
1853 --
1854 -- Validate working_hours and frequency
1855 --
1856 chk_hrs_frequency
1857 (p_position_id => p_rec.position_id,
1858 p_working_hours => p_rec.working_hours,
1859 p_frequency => p_rec.frequency,
1860 p_object_version_number => p_rec.object_version_number
1861 );
1862 --
1863 hr_utility.set_location(l_proc, 15);
1864 --
1865 -- Validate probation period and probation_period_units
1866 --
1867 chk_probation_info
1868 (p_position_id => p_rec.position_id,
1869 p_probation_period => p_rec.probation_period,
1870 p_probation_period_units => p_rec.probation_period_units,
1871 p_object_version_number => p_rec.object_version_number
1872 );
1873 --
1874 hr_utility.set_location(l_proc, 16);
1875 --
1876 -- Validate time normal start and time_normal_finish
1877 --
1878 chk_time_start_finish
1879 (p_position_id => p_rec.position_id,
1880 p_time_normal_start => p_rec.time_normal_start,
1881 p_time_normal_finish => p_rec.time_normal_finish,
1882 p_object_version_number => p_rec.object_version_number
1883 );
1884 --
1885 chk_replacement_flag
1889 );
1886 (p_position_id => p_rec.position_id,
1887 p_replacement_required_flag => p_rec.replacement_required_flag,
1888 p_object_version_number => p_rec.object_version_number
1890 --
1891 -- Validate position definition id
1892 --
1893 chk_position_definition_id
1894 (p_position_definition_id => p_rec.position_definition_id,
1895 p_position_id => p_rec.position_id,
1896 p_object_version_number => p_rec.object_version_number
1897 );
1898 --
1899 -- Validate status
1900 --
1901 chk_status
1902 (p_position_id => p_rec.position_id,
1903 p_date_effective => p_rec.date_effective,
1904 p_status => p_rec.status,
1905 p_object_version_number => p_rec.object_version_number
1906 );
1907 hr_utility.set_location(l_proc, 17);
1908 -- Moved this call to be before chk_df
1909 -- Bug 892165
1910 --
1911 -- PMFLETCH ** Not using this uniqueness check anymore **
1912 -- Check position_name is unique for Business_group
1913 --
1914 --chk_name_unique_for_BG
1915 -- (p_business_group_id => p_rec.business_group_id,
1916 -- p_position_id => p_rec.position_id,
1917 -- p_name => p_rec.name
1918 --);
1919 --
1920 -- PMFLETCH Check position_definition_id is unique for business group
1921 --
1922 chk_ccid_unique_for_BG
1923 (p_business_group_id => p_rec.business_group_id
1924 ,p_position_id => p_rec.position_id
1925 ,p_position_definition_id => p_rec.position_definition_id
1926 ,p_object_version_number => p_rec.object_version_number
1927 );
1928 --
1929 --
1930 -- Call descriptive flexfield validation routines
1931 --
1932 --per_pos_bus.chk_df(p_rec => p_rec);
1933 --
1934 hr_utility.set_location(' Leaving:'||l_proc, 18);
1935 End update_validate;
1936 --
1937 -- ----------------------------------------------------------------------------
1938 -- |---------------------------< delete_validate >----------------------------|
1939 -- ----------------------------------------------------------------------------
1940 Procedure delete_validate(p_rec in per_pos_shd.g_rec_type) is
1941 --
1942 l_proc varchar2(72) := g_package||'delete_validate';
1943 --
1944 Begin
1945 hr_utility.set_location('Entering:'||l_proc, 5);
1946 --
1947 -- Call all supporting business operations
1948 --
1949 hr_utility.set_location(' Leaving:'||l_proc, 10);
1950 End delete_validate;
1951 --
1952 --
1953 -- ---------------------------------------------------------------------------
1954 -- |---------------------< return_legislation_code >-------------------------|
1955 -- ---------------------------------------------------------------------------
1956 --
1957 function return_legislation_code
1958 (p_position_id in number
1959 ) return varchar2 is
1960 --
1961 -- Declare cursor
1962 --
1963 cursor csr_leg_code is
1964 select pbg.legislation_code
1965 from per_business_groups pbg
1966 , per_positions pos
1967 where pos.position_id = p_position_id
1968 and pbg.business_group_id = pos.business_group_id;
1969 --
1970 -- Declare local variables
1971 --
1972 l_legislation_code varchar2(150);
1973 l_proc varchar2(72) := g_package||'return_legislation_code';
1974 begin
1975 hr_utility.set_location('Entering:'|| l_proc, 10);
1976 --
1977 -- Ensure that all the mandatory parameter are not null
1978 --
1979 hr_api.mandatory_arg_error(p_api_name => l_proc,
1980 p_argument => 'position_id',
1981 p_argument_value => p_position_id);
1982 --
1983 open csr_leg_code;
1984 fetch csr_leg_code into l_legislation_code;
1985 if csr_leg_code%notfound then
1986 close csr_leg_code;
1987 --
1988 -- The primary key is invalid therefore we must error
1989 --
1990 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
1991 hr_utility.raise_error;
1992 end if;
1993 --
1994 close csr_leg_code;
1995 hr_utility.set_location(' Leaving:'|| l_proc, 20);
1996 --
1997 return l_legislation_code;
1998 end return_legislation_code;
1999 --
2000 end per_pos_bus;