1 Package Body hr_ori_bus as
2 /* $Header: hrorirhi.pkb 120.3.12010000.2 2008/08/06 08:45:57 ubhat ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' hr_ori_bus.'; -- Global package name
9 --
10 -- The following two global variables are only to be
11 -- used by the return_legislation_code function.
12 --
13 g_legislation_code varchar2(150) default null;
14 g_org_information_id number default null;
15 --
16 -- ---------------------------------------------------------------------------
17 -- |----------------------< set_security_group_id >--------------------------|
18 -- ---------------------------------------------------------------------------
19 --
20 Procedure set_security_group_id
21 (p_org_information_id in number
22 ) is
23 --
24 -- Declare cursor
25 --
26 -- EDIT_HERE In the following cursor statement add join(s) between
27 -- hr_organization_information and PER_BUSINESS_GROUPS
28 -- so that the security_group_id for
29 -- the current business group context can be derived.
30 -- Remove this comment when the edit has been completed.
31 cursor csr_sec_grp is
32 select pbg.security_group_id
33 from per_business_groups pbg
34 , hr_organization_information ori
35 -- , EDIT_HERE table_name(s) 333
36 where ori.org_information_id = p_org_information_id;
37 -- and pbg.business_group_id = EDIT_HERE 333.business_group_id;
38 --
39 -- Declare local variables
40 --
41 l_security_group_id number;
42 l_proc varchar2(72) := g_package||'set_security_group_id';
43 --
44 begin
45 --
46 hr_utility.set_location('Entering:'|| l_proc, 10);
47 --
48 -- Ensure that all the mandatory parameter are not null
49 --
50 hr_api.mandatory_arg_error
51 (p_api_name => l_proc
52 ,p_argument => 'org_information_id'
53 ,p_argument_value => p_org_information_id
54 );
55 --
56 open csr_sec_grp;
57 fetch csr_sec_grp into l_security_group_id;
58 --
59 if csr_sec_grp%notfound then
60 --
61 close csr_sec_grp;
62 --
63 -- The primary key is invalid therefore we must error
64 --
65 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
66 fnd_message.raise_error;
67 --
68 end if;
69 close csr_sec_grp;
70 --
71 -- Set the security_group_id in CLIENT_INFO
72 --
73 hr_api.set_security_group_id
74 (p_security_group_id => l_security_group_id
75 );
76 --
77 hr_utility.set_location(' Leaving:'|| l_proc, 20);
78 --
79 end set_security_group_id;
80 --
81 -- ----------------------------------------------------------------------------
82 -- |-------------------------< chk_cost_center_gap >--------------------------|
83 -- ----------------------------------------------------------------------------
84 --
85 -- This procedure checks that the organization information being passed
86 -- in does not result in a gap in existing cost center managers.
87 -- This function returns a boolean value of true if a gap has occured.
88 --
89 -- Access Status:
90 -- Internal Development Use Only.
91 --
92 -- {End Of Comments}
93 --
94 FUNCTION chk_cost_center_gap
95 (p_organization_id IN number,
96 p_org_information_context IN varchar2,
97 p_org_information_id IN number,
98 p_start_date IN date,
99 p_end_date IN date) return boolean is
100 --
101 l_proc varchar2(72) := g_package||'chk_cost_center_gap';
102 --
103 cursor c1 is
104 select max(fnd_date.canonical_to_date(org_information4)) max_end_date
105 from hr_organization_information
106 where organization_id = p_organization_id
107 and org_information_context = 'Organization Name Alias'
108 and org_information_id <> nvl(p_org_information_id,-1)
109 and fnd_date.canonical_to_date(org_information4) < p_start_date;
110 --
111 cursor c2 is
112 select min(fnd_date.canonical_to_date(org_information3)) min_start_date
113 from hr_organization_information
114 where organization_id = p_organization_id
115 and org_information_context = 'Organization Name Alias'
116 and org_information_id <> nvl(p_org_information_id,-1)
117 and fnd_date.canonical_to_date(org_information3)
118 > nvl(p_end_date,hr_api.g_eot);
119 --
120 l_c2 c2%rowtype;
121 l_c1 c1%rowtype;
122 --
123 begin
124 --
125 hr_utility.set_location(' Entering:'|| l_proc, 20);
126 --
127 -- Rules are as follows
128 -- If record being inserted or updated is before the earliest start
129 -- date then compare end date to previous earliest start date.
130 -- If record is after earliest start date then grab latest end date where
131 -- end date is less than newly created or updated start date and compare.
132 -- Additionally check end date compared to next start date that is greater
133 -- than end date. Don't bother checking overlaps as they are found elsewhere.
134 --
135 -- This is only relevant for Organization Name Alias context
136 --
137 if p_org_information_context <> 'Organization Name Alias' then
138 --
139 hr_utility.set_location(' Leaving:'|| l_proc, 11);
140 return false;
141 --
142 end if;
143 --
144 -- Get max end date before new start date first.
145 --
146 open c1;
147 --
148 fetch c1 into l_c1;
149 if c1%found then
150 --
151 -- Check if the date is one day before the row we are inserting/updating
152 --
153 if l_c1.max_end_date+1 < p_start_date then
154 --
155 -- This results in a gap, if its an overlap then it will be picked
156 -- up by the overlap business rule.
157 --
158 close c1;
159 hr_utility.set_location(' Leaving:'|| l_proc, 12);
160 return true;
161 --
162 end if;
163 --
164 end if;
165 --
166 close c1;
167 --
168 -- Get min start date after end date
169 --
170 open c2;
171 --
172 fetch c2 into l_c2;
173 if c2%found then
174 --
175 -- Check if the date is one day before the row we are inserting/updating
176 --
177 if l_c2.min_start_date-1 > p_end_date then
178 --
179 -- This results in a gap, if its an overlap then it will be picked
180 -- up by the overlap business rule.
181 --
182 close c2;
183 hr_utility.set_location(p_end_date|| l_proc, 13);
184 hr_utility.set_location(l_c2.min_start_date|| l_proc, 13);
185 hr_utility.set_location(' Leaving:'|| l_proc, 13);
186 return true;
187 --
188 end if;
189 --
190 end if;
191 --
192 close c2;
193 --
194 hr_utility.set_location(' Leaving:'|| l_proc, 20);
195 --
196 return false;
197 --
198 end chk_cost_center_gap;
199 -- ----------------------------------------------------------------------------
200 -- |-------------------------< chk_cost_center_start_date >-------------------|
201 -- ----------------------------------------------------------------------------
202 --
203 -- This procedure checks that the start date is entered for the context
204 -- of "Organization Name Alias" and thats its before the end date.
205 --
206 -- Access Status:
207 -- Internal Development Use Only.
208 --
209 -- {End Of Comments}
210 --
211 procedure chk_cost_center_start_date
212 (p_org_information_context in varchar2,
213 p_org_information2 in varchar2,
214 p_org_information3 in varchar2,
215 p_org_information4 in varchar2) is
216 --
217 l_proc varchar2(72) := g_package||'chk_cost_center_start_date';
218 l_start_date date;
219 l_end_date date;
220 --
221 begin
222 --
223 hr_utility.set_location(' Entering:'|| l_proc, 20);
224 --
225 -- Rules are as follows
226 -- Any record being inserted or updated must have a Start Date and if
227 -- entered the End Date must be on or after the Start Date.
228 --
229 if p_org_information_context <> 'Organization Name Alias' then
230 --
231 hr_utility.set_location(' Leaving:'|| l_proc, 11);
232 return;
233 --
234 end if;
235 --
236 -- Check if start date has been populated. It may have been updated to null
237 -- though which is valid providing all the fields are null.
238 --
239 if p_org_information3 is null and (p_org_information2 is not null or
240 p_org_information4 is not null) then
241 --
242 fnd_message.set_name('PER','PER_289693_START_DATE_NULL');
243 fnd_message.raise_error;
244 --
245 end if;
246 --
247 if p_org_information3 is not null and
248 p_org_information2 is null then
249 --
250 fnd_message.set_name('PER','PER_289694_NO_MANAGER');
251 fnd_message.raise_error;
252 --
253 end if;
254 --
255 if p_org_information2 is null and
256 p_org_information3 is null and
257 p_org_information4 is null then
258 --
259 return;
260 --
261 end if;
262 --
263 -- Check if start date is less than end date if end date has been
264 -- populated.
265 --
266 begin
267 --
268 -- Since we are checking the format prior to flex we need to make sure
269 -- its all good and valid.
270 --
271 l_start_date := fnd_date.canonical_to_date(p_org_information3);
272 --
273 exception
274 --
275 when others then
276 --
277 fnd_message.set_name('PER','PER_289695_START_DATE_FORMAT');
278 fnd_message.raise_error;
279 --
280 end;
281 --
282 if p_org_information4 is not null then
283 --
284 begin
285 --
286 -- Since we are checking the format prior to flex we need to make sure
287 -- its all good and valid.
288 --
289 l_end_date := fnd_date.canonical_to_date(p_org_information4);
290 --
291 exception
292 --
293 when others then
294 --
295 fnd_message.set_name('PER','PER_289696_END_DATE_FORMAT');
296 fnd_message.raise_error;
297 --
298 end;
299 --
300 if l_start_date > l_end_date then
301 --
302 fnd_message.set_name('PER','PER_289697_START_BEFORE_END');
303 fnd_message.raise_error;
304 --
305 end if;
306 --
307 end if;
308 --
309 hr_utility.set_location(' Leaving:'|| l_proc, 20);
310 --
311 end chk_cost_center_start_date;
312 -- ----------------------------------------------------------------------------
313 -- |-------------------------< chk_cost_center_start_end_date >---------------|
314 -- ----------------------------------------------------------------------------
315 --
316 -- This procedure checks that the start date is not before the employees hire
317 -- date and that the end date is not after the employees termination date.
318 -- This is only application for the context "Organization Name Alias".
319 --
320 -- Access Status:
321 -- Internal Development Use Only.
322 --
323 -- {End Of Comments}
324 --
325 procedure chk_cost_center_start_end_date
326 (p_org_information_context in varchar2,
327 p_org_information2 in varchar2,
328 p_org_information3 in varchar2,
329 p_org_information4 in varchar2) is
330 --
331 l_proc varchar2(72) := g_package||'chk_cost_center_start_end_date';
332 l_person_id number;
333 l_start_date date;
334 l_end_date date;
335 --
336 cursor c1 is
337 select period_of_service_id worker_id,
338 date_start
339 from per_periods_of_service
340 where l_start_date
341 between date_start
342 and nvl(actual_termination_date,hr_api.g_eot)
343 and person_id = l_person_id
344 union
345 select period_of_placement_id worker_id,
346 date_start
347 from per_periods_of_placement
348 where l_start_date
349 between date_start
350 and nvl(actual_termination_date,hr_api.g_eot)
351 and person_id = l_person_id;
352 --
353 -- They need to share the same period of service id.
354 --
355 -- WWBUG 2358813.
356 --
357 cursor c2(p_worker_id number) is
358 select actual_termination_date
359 from per_periods_of_service
360 where nvl(l_end_date,hr_api.g_eot)
361 between date_start
362 and nvl(actual_termination_date,hr_api.g_eot)
363 and period_of_service_id = p_worker_id
364 and person_id = l_person_id
365 union
366 select actual_termination_date
367 from per_periods_of_placement
368 where nvl(l_end_date,hr_api.g_eot)
369 between date_start
370 and nvl(actual_termination_date,hr_api.g_eot)
371 and period_of_placement_id = p_worker_id
372 and person_id = l_person_id;
373 --
374 l_c1 c1%rowtype;
375 l_c2 c2%rowtype;
376 --
377 begin
378 --
379 hr_utility.set_location(' Entering:'|| l_proc, 20);
380 --
381 -- Rules are as follows
382 -- Any Cost Center Manager being inserted or updated must have a Hire
383 -- Date that is on or before the Start Date and a Termination Date that
384 -- is on or after the End Date.
385 --
386 -- This is only relevant for Organization Name Alias context
387 --
388 if p_org_information_context <> 'Organization Name Alias' then
389 --
390 hr_utility.set_location(' Leaving:'|| l_proc, 11);
391 return;
392 --
393 end if;
394 --
395 -- Check if person has been assigned to the cost center. If not return
396 --
397 if p_org_information2 is null then
398 --
399 return;
400 --
401 end if;
402 --
403 -- Get value of person id.
404 --
405 begin
406 --
407 -- Since we are checking the format prior to flex we need to make sure
408 -- its all good and valid.
409 --
410 l_person_id := to_number(p_org_information2);
411 --
412 exception
413 --
414 when others then
415 --
416 fnd_message.set_name('PER','PER_289698_PERSON_ID_INVALID');
417 fnd_message.raise_error;
418 --
419 end;
420 --
421 -- Get value of Start Date.
422 --
423 begin
424 --
425 -- Since we are checking the format prior to flex we need to make sure
426 -- its all good and valid.
427 --
428 l_start_date := fnd_date.canonical_to_date(p_org_information3);
429 --
430 exception
431 --
432 when others then
433 --
434 fnd_message.set_name('PER','PER_289695_START_DATE_FORMAT');
435 fnd_message.raise_error;
436 --
437 end;
438 --
439 open c1;
440 --
441 fetch c1 into l_c1;
442 if c1%notfound then
443 --
444 fnd_message.set_name('PER','PER_289699_START_DATE_BFR_HIRE');
445 fnd_message.raise_error;
446 --
447 end if;
448 --
449 close c1;
450 --
451 -- Get value of end date
452 --
453 begin
454 --
455 -- Since we are checking the format prior to flex we need to make sure
456 -- its all good and valid.
457 --
458 l_end_date := fnd_date.canonical_to_date(p_org_information4);
459 --
460 exception
461 --
462 when others then
463 --
464 fnd_message.set_name('PER','PER_289696_END_DATE_FORMAT');
465 fnd_message.raise_error;
466 --
467 end;
468 --
469 open c2(l_c1.worker_id);
470 --
471 fetch c2 into l_c2;
472 if c2%notfound then
473 --
474 fnd_message.set_name('PER','PER_289700_END_DATE_AFTER_TERM');
475 fnd_message.raise_error;
476 --
477 end if;
478 --
479 close c2;
480 --
481 hr_utility.set_location(' Leaving:'|| l_proc, 20);
482 --
483 end chk_cost_center_start_end_date;
484 -- ----------------------------------------------------------------------------
485 -- |-------------------------< chk_cost_center_valid >------------------------|
486 -- ----------------------------------------------------------------------------
487 --
488 -- This procedure checks that the Cost Center being updated can be seen by the
489 -- user.
490 --
491 -- Access Status:
492 -- Internal Development Use Only.
493 --
494 -- {End Of Comments}
495 --
496 procedure chk_cost_center_valid
497 (p_organization_id in number,
498 p_org_information_context in VARCHAR2) is
499 --
500 l_proc varchar2(72) := g_package||'chk_cost_center_valid';
501 --
502 cursor c1 is
503 select null
504 from dual
505 where exists
506 (select null
507 from hr_organization_units org,
508 hr_organization_information org2,
509 hr_org_info_types_by_class oitbc
510 where org.organization_id = p_organization_id
511 and org.organization_id = org2.organization_id
512 and org2.org_information_context = 'CLASS'
513 and org2.org_information2 = 'Y'
514 and oitbc.org_classification = org2.org_information1
515 and oitbc.org_information_type = 'Organization Name Alias');
516 --
517 l_c1 c1%rowtype;
518 --
519 begin
520 --
521 hr_utility.set_location(' Entering:'|| l_proc, 20);
522 --
523 -- Rules are as follows
524 -- Any Cost Center that is being updated must be visible to the user
525 -- through the users security profile.
526 --
527 -- This is only relevant for Organization Name Alias context
528 --
529 if p_org_information_context <> 'Organization Name Alias' then
530 --
531 hr_utility.set_location(' Leaving:'|| l_proc, 11);
532 return;
533 --
534 end if;
535 --
536 -- Check if user can see the organization in question.
537 --
538 open c1;
539 --
540 fetch c1 into l_c1;
541 if c1%notfound then
542 --
543 fnd_message.set_name('PER','PER_289701_INVALID_COST_CENTER');
544 fnd_message.raise_error;
545 --
546 end if;
547 --
548 close c1;
549 --
550 hr_utility.set_location(' Leaving:'|| l_proc, 20);
551 --
552 end chk_cost_center_valid;
553 -- ----------------------------------------------------------------------------
554 -- |-------------------------< chk_cost_center_manager_valid >----------------|
555 -- ----------------------------------------------------------------------------
556 --
557 -- This procedure checks that the Cost Center manager being updated can be
558 -- seen by the user.
559 --
560 -- Access Status:
561 -- Internal Development Use Only.
562 --
563 -- {End Of Comments}
564 --
565 procedure chk_cost_center_manager_valid
566 (p_org_information_context in VARCHAR2,
567 p_org_information2 in VARCHAR2,
568 p_effective_date in DATE) is
569 --
570 l_proc varchar2(72) := g_package||'chk_cost_center_manager_valid';
571 l_person_id number;
572 --
573 cursor c1 is
574 select null
575 from per_people_f
576 where person_id = l_person_id
577 and p_effective_date
578 between effective_start_date
579 and effective_end_date;
580 --
581 l_c1 c1%rowtype;
582 --
583 begin
584 --
585 hr_utility.set_location(' Entering:'|| l_proc, 20);
586 --
587 -- Rules are as follows
588 -- Any Cost Center Manager that is being updated must be visible to the user
589 -- through the users security profile.
590 --
591 -- This is only relevant for Organization Name Alias context
592 --
593 if p_org_information_context <> 'Organization Name Alias' then
594 --
595 hr_utility.set_location(' Leaving:'|| l_proc, 11);
596 return;
597 --
598 end if;
599 --
600 -- Person can be nulled out if they are terminated for example.
601 --
602 if p_org_information2 is null then
603 --
604 hr_utility.set_location(' Leaving:'|| l_proc, 12);
605 return;
606 --
607 end if;
608 --
609 -- Get value of person id.
610 --
611 begin
612 --
613 -- Since we are checking the format prior to flex we need to make sure
614 -- its all good and valid.
615 --
616 l_person_id := to_number(p_org_information2);
617 --
618 exception
619 --
620 when others then
621 --
622 fnd_message.set_name('PER','PER_289698_PERSON_ID_INVALID');
623 fnd_message.raise_error;
624 --
625 end;
626 --
627 -- Check if user can see the manager in question.
628 --
629 open c1;
630 --
631 fetch c1 into l_c1;
632 if c1%notfound then
633 --
634 fnd_message.set_name('PER','PER_289702_INVALID_MANAGER');
635 fnd_message.raise_error;
636 --
637 end if;
638 --
639 close c1;
640 --
641 hr_utility.set_location(' Leaving:'|| l_proc, 20);
642 --
643 end chk_cost_center_manager_valid;
644 -- ----------------------------------------------------------------------------
645 -- |-------------------------< chk_cost_center_man_overlap >------------------|
646 -- ----------------------------------------------------------------------------
647 --
648 -- This procedure checks that the Cost Center Manager Relationship being
649 -- updated does not overlap an existing Cost Center Manager Relationship.
650 --
651 -- Access Status:
652 -- Internal Development Use Only.
653 --
654 -- {End Of Comments}
655 --
656 procedure chk_cost_center_man_overlap
657 (p_organization_id in NUMBER,
658 p_org_information_id in NUMBER,
659 p_org_information_context in VARCHAR2,
660 p_org_information3 in VARCHAR2,
661 p_org_information4 in VARCHAR2) is
662 --
663 l_proc varchar2(72) := g_package||'chk_cost_center_man_overlap';
664 l_start_date date;
665 l_end_date date;
666 --
667 cursor c1 is
668 select null
669 from hr_organization_information
670 where organization_id = p_organization_id
671 and org_information_context = 'Organization Name Alias'
672 and (l_start_date
673 between fnd_date.canonical_to_date(org_information3)
674 and nvl(fnd_date.canonical_to_date(org_information4),hr_api.g_eot)
675 or
676 nvl(l_end_date,hr_api.g_eot)
677 between fnd_date.canonical_to_date(org_information3)
678 and nvl(fnd_date.canonical_to_date(org_information4),hr_api.g_eot)
679 or fnd_date.canonical_to_date(org_information3)
680 between l_start_date
681 and nvl(l_end_date,hr_api.g_eot)
682 or nvl(fnd_date.canonical_to_date(org_information4),hr_api.g_eot)
683 between l_start_date
684 and nvl(l_end_date,hr_api.g_eot))
685 and org_information_id <> nvl(p_org_information_id,-1)
686 and org_information3 is not null;
687 --
688 l_c1 c1%rowtype;
689 --
690 cursor c2 is
691 select date_from,
692 date_to
693 from hr_organization_units
694 where organization_id = p_organization_id;
695 --
696 l_c2 c2%rowtype;
697 --
698 begin
699 --
700 hr_utility.set_location(' Entering:'|| l_proc, 20);
701 --
702 -- Rules are as follows
703 -- Any Cost Center Manager Relationship that is being updated must not
704 -- overlap another Cost Center Manager Relationship for the same
705 -- Cost Center.
706 --
707 -- This is only relevant for Organization Name Alias context
708 --
709 if p_org_information_context <> 'Organization Name Alias' then
710 --
711 hr_utility.set_location(' Leaving:'|| l_proc, 11);
712 return;
713 --
714 end if;
715 --
716 -- The row is being updated to null so no need to check for overlap.
717 --
718 if p_org_information3 is null then
719 --
720 return;
721 --
722 end if;
723 --
724 -- Get value of Start Date.
725 --
726 begin
727 --
728 -- Since we are checking the format prior to flex we need to make sure
729 -- its all good and valid.
730 --
731 l_start_date := fnd_date.canonical_to_date(p_org_information3);
732 --
733 exception
734 --
735 when others then
736 --
737 fnd_message.set_name('PER','PER_289695_START_DATE_FORMAT');
738 fnd_message.raise_error;
739 --
740 end;
741 --
742 -- Get value of End Date.
743 --
744 begin
745 --
746 -- Since we are checking the format prior to flex we need to make sure
747 -- its all good and valid.
748 --
749 l_end_date := fnd_date.canonical_to_date(p_org_information4);
750 --
751 exception
752 --
753 when others then
754 --
755 fnd_message.set_name('PER','PER_289696_END_DATE_FORMAT');
756 fnd_message.raise_error;
757 --
758 end;
759 --
760 hr_utility.set_location('Start Date '||l_start_date,10);
761 hr_utility.set_location('End Date '||l_end_date,10);
762 hr_utility.set_location('Organization_id '||p_organization_id,10);
763 hr_utility.set_location('Org_information_id '||p_org_information_id,10);
764 --
765 open c1;
766 --
767 fetch c1 into l_c1;
768 if c1%found then
769 --
770 fnd_message.set_name('PER','PER_289703_CCM_OVERLAP');
771 fnd_message.raise_error;
772 --
773 end if;
774 --
775 close c1;
776 --
777 open c2;
778 --
779 fetch c2 into l_c2;
780 --
781 hr_utility.set_location('Start Date '||l_start_date,10);
782 hr_utility.set_location('End Date '||l_end_date,10);
783 hr_utility.set_location('Org Start Date '||l_c2.date_from,10);
784 hr_utility.set_location('Org End Date '||l_c2.date_to,10);
785 --
786 -- Removed the check for end date for fix of #3137148.
787 --
788 if l_start_date < l_c2.date_from then
789 --
790 hr_utility.set_location(' Rel. ship start date is before Org start date '|| l_proc, 15);
791 close c2;
792 --
793 fnd_message.set_name('PER','PER_449079_CCM_BEFORE_ORG');
794 fnd_message.raise_error;
795 --
796 end if;
797 --
798 close c2;
799 --
800 hr_utility.set_location(' Leaving:'|| l_proc, 20);
801 --
802 end chk_cost_center_man_overlap;
803 -- ---------------------------------------------------------------------------
804 -- |---------------------< return_legislation_code >-------------------------|
805 -- ---------------------------------------------------------------------------
806 --
807 Function return_legislation_code
808 (p_org_information_id in number
809 )
810 Return Varchar2 Is
811 --
812 -- Declare cursor
813 --
814 -- EDIT_HERE In the following cursor statement add join(s) between
815 -- hr_organization_information and PER_BUSINESS_GROUPS
816 -- so that the legislation_code for
817 -- the current business group context can be derived.
818 -- Remove this comment when the edit has been completed.
819 -- JOIN COMPLETED
820 cursor csr_leg_code is
821 select pbg.legislation_code
822 from per_business_groups pbg
823 , hr_organization_information ori
824 , hr_organization_units org
825 where ori.org_information_id = p_org_information_id
826 and org.organization_id = ori.organization_id
827 and pbg.business_group_id = org.business_group_id; -- AT 27/9/01
828 --
829 -- Declare local variables
830 --
831 l_legislation_code varchar2(150);
832 l_proc varchar2(72) := g_package||'return_legislation_code';
833 --
834 Begin
835 --
836 hr_utility.set_location('Entering:'|| l_proc, 10);
837 --
838 -- Ensure that all the mandatory parameter are not null
839 --
840 hr_api.mandatory_arg_error
841 (p_api_name => l_proc
842 ,p_argument => 'org_information_id'
843 ,p_argument_value => p_org_information_id
844 );
845 --
846 if ( nvl(hr_ori_bus.g_org_information_id, hr_api.g_number)
847 = p_org_information_id) then
848 --
849 -- The legislation code has already been found with a previous
850 -- call to this function. Just return the value in the global
851 -- variable.
852 --
853 l_legislation_code := hr_ori_bus.g_legislation_code;
854 hr_utility.set_location(l_proc, 20);
855 else
856 --
857 -- The ID is different to the last call to this function
858 -- or this is the first call to this function.
859 --
860 open csr_leg_code;
861 fetch csr_leg_code into l_legislation_code;
862 --
863 if csr_leg_code%notfound then
864 --
865 -- The primary key is invalid therefore we must error
866 --
867 close csr_leg_code;
868 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
869 fnd_message.raise_error;
870 end if;
871 hr_utility.set_location(l_proc,30);
872 --
873 -- Set the global variables so the values are
874 -- available for the next call to this function.
875 --
876 close csr_leg_code;
877 hr_ori_bus.g_org_information_id:= p_org_information_id;
878 hr_ori_bus.g_legislation_code := l_legislation_code;
879 end if;
880 hr_utility.set_location(' Leaving:'|| l_proc, 40);
881 return l_legislation_code;
882 end return_legislation_code;
883 -- ----------------------------------------------------------------------------
884 -- |-------------------------< chk_name >-------------------------------------|
885 -- ----------------------------------------------------------------------------
886 --
887 -- if the record being updated or inserted is a business group classification
888 -- then this procedure checks that the business group name is unique
889 --
890 -- Access Status:
891 -- Internal Development Use Only.
892 --
893 -- {End Of Comments}
894 --
895 PROCEDURE chk_name
896 (p_org_information1 IN hr_organization_information.org_information1%TYPE,
897 p_org_information_context IN hr_organization_information.org_information_context%TYPE,
898 p_organization_id IN number, --default null, -- R115.21
899 p_org_information2 IN hr_organization_information.org_information2%TYPE
900 )
901 IS
902 l_proc VARCHAR2(72) := g_package||'chk_name';
903 l_name hr_all_organization_units.name%TYPE;
904 l_exists number;
905 BEGIN
906 --
907 hr_utility.set_location('Entering:'|| l_proc, 10);
908 --
909 --
910 --
911 hr_utility.set_location(l_proc, 20);
912 --
913 --
914 -- Check that the business group name is unique if we are adding or updating to
915 -- a business group classification
916 --
917 select name into l_name from hr_all_organization_units where organization_id = p_organization_id;
918
919 if p_org_information1 = 'HR_BG'
920 and p_org_information_context = 'CLASS'
921 and p_org_information2 = 'Y' then
922
923 select count(*)
924 into l_exists
925 from hr_organization_information i, hr_all_organization_units u
926 where i.organization_id <> p_organization_id
927 and i.organization_id = u.organization_id
928 and i.org_information1='HR_BG'
929 and i.org_information_context='CLASS'
930 and i.org_information2 ='Y'
931 and u.name = l_name;
932 if l_exists >0 then
933 hr_utility.set_message(800, 'HR_289381_DUPLICATE_BG');
934 hr_utility.raise_error;
935 end if;
936 end if;
937 exception
938 when no_data_found then
939 hr_utility.set_message(800, 'HR_289002_INV_ORG_ID');
940 hr_utility.raise_error;
941
942 end chk_name;
943
944 -- ----------------------------------------------------------------------------
945 -- |-----------------------------< chk_ddf >----------------------------------|
946 -- ----------------------------------------------------------------------------
947 --
948 -- Description:
949 -- Validates all the Developer Descriptive Flexfield values.
950 --
951 -- Prerequisites:
952 -- All other columns have been validated. Must be called as the
953 -- last step from insert_validate and update_validate.
954 --
955 -- In Arguments:
956 -- p_rec
957 --
958 -- Post Success:
959 -- If the Developer Descriptive Flexfield structure column and data values
960 -- are all valid this procedure will end normally and processing will
961 -- continue.
962 --
963 -- Post Failure:
964 -- If the Developer Descriptive Flexfield structure column value or any of
965 -- the data values are invalid then an application error is raised as
966 -- a PL/SQL exception.
967 --
968 -- Access Status:
969 -- Internal Row Handler Use Only.
970 --
971 -- ----------------------------------------------------------------------------
972 procedure chk_ddf
973 (p_rec in hr_ori_shd.g_rec_type
974 ) is
975 --
976 l_proc varchar2(72) := g_package || 'chk_ddf';
977 --
978 begin
979 hr_utility.set_location('Entering:'||l_proc,10);
980 --
981 if ((p_rec.org_information_id is not null) and (
982 nvl(hr_ori_shd.g_old_rec.org_information_id, hr_api.g_number) <>
983 nvl(p_rec.org_information_id, hr_api.g_number) or
984 nvl(hr_ori_shd.g_old_rec.org_information_context, hr_api.g_varchar2) <>
985 nvl(p_rec.org_information_context, hr_api.g_varchar2) or
986 nvl(hr_ori_shd.g_old_rec.org_information1, hr_api.g_varchar2) <>
987 nvl(p_rec.org_information1, hr_api.g_varchar2) or
988 nvl(hr_ori_shd.g_old_rec.org_information10, hr_api.g_varchar2) <>
989 nvl(p_rec.org_information10, hr_api.g_varchar2) or
990 nvl(hr_ori_shd.g_old_rec.org_information11, hr_api.g_varchar2) <>
991 nvl(p_rec.org_information11, hr_api.g_varchar2) or
992 nvl(hr_ori_shd.g_old_rec.org_information12, hr_api.g_varchar2) <>
993 nvl(p_rec.org_information12, hr_api.g_varchar2) or
994 nvl(hr_ori_shd.g_old_rec.org_information13, hr_api.g_varchar2) <>
995 nvl(p_rec.org_information13, hr_api.g_varchar2) or
996 nvl(hr_ori_shd.g_old_rec.org_information14, hr_api.g_varchar2) <>
997 nvl(p_rec.org_information14, hr_api.g_varchar2) or
998 nvl(hr_ori_shd.g_old_rec.org_information15, hr_api.g_varchar2) <>
999 nvl(p_rec.org_information15, hr_api.g_varchar2) or
1000 nvl(hr_ori_shd.g_old_rec.org_information16, hr_api.g_varchar2) <>
1001 nvl(p_rec.org_information16, hr_api.g_varchar2) or
1002 nvl(hr_ori_shd.g_old_rec.org_information17, hr_api.g_varchar2) <>
1003 nvl(p_rec.org_information17, hr_api.g_varchar2) or
1004 nvl(hr_ori_shd.g_old_rec.org_information18, hr_api.g_varchar2) <>
1005 nvl(p_rec.org_information18, hr_api.g_varchar2) or
1006 nvl(hr_ori_shd.g_old_rec.org_information19, hr_api.g_varchar2) <>
1007 nvl(p_rec.org_information19, hr_api.g_varchar2) or
1008 nvl(hr_ori_shd.g_old_rec.org_information2, hr_api.g_varchar2) <>
1009 nvl(p_rec.org_information2, hr_api.g_varchar2) or
1010 nvl(hr_ori_shd.g_old_rec.org_information20, hr_api.g_varchar2) <>
1011 nvl(p_rec.org_information20, hr_api.g_varchar2) or
1012 nvl(hr_ori_shd.g_old_rec.org_information3, hr_api.g_varchar2) <>
1013 nvl(p_rec.org_information3, hr_api.g_varchar2) or
1014 nvl(hr_ori_shd.g_old_rec.org_information4, hr_api.g_varchar2) <>
1015 nvl(p_rec.org_information4, hr_api.g_varchar2) or
1016 nvl(hr_ori_shd.g_old_rec.org_information5, hr_api.g_varchar2) <>
1017 nvl(p_rec.org_information5, hr_api.g_varchar2) or
1018 nvl(hr_ori_shd.g_old_rec.org_information6, hr_api.g_varchar2) <>
1019 nvl(p_rec.org_information6, hr_api.g_varchar2) or
1020 nvl(hr_ori_shd.g_old_rec.org_information7, hr_api.g_varchar2) <>
1021 nvl(p_rec.org_information7, hr_api.g_varchar2) or
1022 nvl(hr_ori_shd.g_old_rec.org_information8, hr_api.g_varchar2) <>
1023 nvl(p_rec.org_information8, hr_api.g_varchar2) or
1024 nvl(hr_ori_shd.g_old_rec.org_information9, hr_api.g_varchar2) <>
1025 nvl(p_rec.org_information9, hr_api.g_varchar2) ))
1026 or (p_rec.org_information_id is null) then
1027 --
1028 -- Only execute the validation if absolutely necessary:
1029 -- a) During update, the structure column value or any
1030 -- of the attribute values have actually changed.
1031 -- b) During insert.
1032 --
1033 hr_utility.set_location('context = '||p_rec.org_information_context,20);
1034 hr_utility.set_location('org_information1 = '||p_rec.org_information1,30);
1035 hr_utility.set_location('org_information2 = '||p_rec.org_information2,40);
1036 hr_utility.set_location('org_information3 = '||p_rec.org_information3,50);
1037 hr_utility.set_location('org_information4 = '||p_rec.org_information4,60);
1038 hr_utility.set_location('org_information5 = '||p_rec.org_information5,70);
1039
1040 /*
1041 ** Some valuesets used by this flexfield require additional information
1042 ** this will be passed using profile options. We will create these and
1043 ** set these on-the-fly now and then we'll call the flex code.
1044 */
1045 fnd_profile.put('PER_ORGANIZATION_ID',p_rec.organization_id);
1046 fnd_profile.put('PER_ORG_INFORMATION_ID',
1047 nvl(to_number(p_rec.org_information_id),-1));
1048 hr_utility.set_location('PER_ORG_INFORMATION_ID'||
1049 fnd_profile.value('PER_ORG_INFORMATION_ID'),80);
1050 hr_utility.set_location('PER_ORGANIZATION_ID'||
1051 fnd_profile.value('PER_ORGANIZATION_ID'),80);
1052
1053 hr_dflex_utility.ins_or_upd_descflex_attribs
1054 (p_appl_short_name => 'PER'
1055 ,p_descflex_name => 'Org Developer DF'
1056 ,p_attribute_category => p_rec.org_information_context
1057 ,p_attribute1_name => 'ORG_INFORMATION1'
1058 ,p_attribute1_value => p_rec.org_information1
1059 ,p_attribute2_name => 'ORG_INFORMATION2'
1060 ,p_attribute2_value => p_rec.org_information2
1061 ,p_attribute3_name => 'ORG_INFORMATION3'
1062 ,p_attribute3_value => p_rec.org_information3
1063 ,p_attribute4_name => 'ORG_INFORMATION4'
1064 ,p_attribute4_value => p_rec.org_information4
1065 ,p_attribute5_name => 'ORG_INFORMATION5'
1066 ,p_attribute5_value => p_rec.org_information5
1067 ,p_attribute6_name => 'ORG_INFORMATION6'
1068 ,p_attribute6_value => p_rec.org_information6
1069 ,p_attribute7_name => 'ORG_INFORMATION7'
1070 ,p_attribute7_value => p_rec.org_information7
1071 ,p_attribute8_name => 'ORG_INFORMATION8'
1072 ,p_attribute8_value => p_rec.org_information8
1073 ,p_attribute9_name => 'ORG_INFORMATION9'
1074 ,p_attribute9_value => p_rec.org_information9
1075 ,p_attribute10_name => 'ORG_INFORMATION10'
1076 ,p_attribute10_value => p_rec.org_information10
1077 ,p_attribute11_name => 'ORG_INFORMATION11'
1078 ,p_attribute11_value => p_rec.org_information11
1079 ,p_attribute12_name => 'ORG_INFORMATION12'
1080 ,p_attribute12_value => p_rec.org_information12
1081 ,p_attribute13_name => 'ORG_INFORMATION13'
1082 ,p_attribute13_value => p_rec.org_information13
1083 ,p_attribute14_name => 'ORG_INFORMATION14'
1084 ,p_attribute14_value => p_rec.org_information14
1085 ,p_attribute15_name => 'ORG_INFORMATION15'
1086 ,p_attribute15_value => p_rec.org_information15
1087 ,p_attribute16_name => 'ORG_INFORMATION16'
1088 ,p_attribute16_value => p_rec.org_information16
1089 ,p_attribute17_name => 'ORG_INFORMATION17'
1090 ,p_attribute17_value => p_rec.org_information17
1091 ,p_attribute18_name => 'ORG_INFORMATION18'
1092 ,p_attribute18_value => p_rec.org_information18
1093 ,p_attribute19_name => 'ORG_INFORMATION19'
1094 ,p_attribute19_value => p_rec.org_information19
1095 ,p_attribute20_name => 'ORG_INFORMATION20'
1096 ,p_attribute20_value => p_rec.org_information20
1097 );
1098 end if;
1099 --
1100 hr_utility.set_location(' Leaving:'||l_proc,100);
1101 end chk_ddf;
1102 --
1103 -- ----------------------------------------------------------------------------
1104 -- |------------------------------< chk_df >----------------------------------|
1105 -- ----------------------------------------------------------------------------
1106 --
1107 -- Description:
1108 -- Validates all the Descriptive Flexfield values.
1109 --
1110 -- Prerequisites:
1111 -- All other columns have been validated. Must be called as the
1112 -- last step from insert_validate and update_validate.
1113 --
1114 -- In Arguments:
1115 -- p_rec
1116 --
1117 -- Post Success:
1118 -- If the Descriptive Flexfield structure column and data values are
1119 -- all valid this procedure will end normally and processing will
1120 -- continue.
1121 --
1122 -- Post Failure:
1123 -- If the Descriptive Flexfield structure column value or any of
1124 -- the data values are invalid then an application error is raised as
1125 -- a PL/SQL exception.
1126 --
1127 -- Access Status:
1128 -- Internal Row Handler Use Only.
1129 --
1130 -- ----------------------------------------------------------------------------
1131 procedure chk_df
1132 (p_rec in hr_ori_shd.g_rec_type
1133 ) is
1134 --
1135 l_proc varchar2(72) := g_package || 'chk_df';
1136 --
1137 begin
1138 hr_utility.set_location('Entering:'||l_proc,10);
1139 --
1140 if ((p_rec.org_information_id is not null) and (
1141 nvl(hr_ori_shd.g_old_rec.attribute_category, hr_api.g_varchar2) <>
1142 nvl(p_rec.attribute_category, hr_api.g_varchar2) or
1143 nvl(hr_ori_shd.g_old_rec.attribute1, hr_api.g_varchar2) <>
1144 nvl(p_rec.attribute1, hr_api.g_varchar2) or
1145 nvl(hr_ori_shd.g_old_rec.attribute2, hr_api.g_varchar2) <>
1146 nvl(p_rec.attribute2, hr_api.g_varchar2) or
1147 nvl(hr_ori_shd.g_old_rec.attribute3, hr_api.g_varchar2) <>
1148 nvl(p_rec.attribute3, hr_api.g_varchar2) or
1149 nvl(hr_ori_shd.g_old_rec.attribute4, hr_api.g_varchar2) <>
1150 nvl(p_rec.attribute4, hr_api.g_varchar2) or
1151 nvl(hr_ori_shd.g_old_rec.attribute5, hr_api.g_varchar2) <>
1152 nvl(p_rec.attribute5, hr_api.g_varchar2) or
1153 nvl(hr_ori_shd.g_old_rec.attribute6, hr_api.g_varchar2) <>
1154 nvl(p_rec.attribute6, hr_api.g_varchar2) or
1155 nvl(hr_ori_shd.g_old_rec.attribute7, hr_api.g_varchar2) <>
1156 nvl(p_rec.attribute7, hr_api.g_varchar2) or
1157 nvl(hr_ori_shd.g_old_rec.attribute8, hr_api.g_varchar2) <>
1158 nvl(p_rec.attribute8, hr_api.g_varchar2) or
1159 nvl(hr_ori_shd.g_old_rec.attribute9, hr_api.g_varchar2) <>
1160 nvl(p_rec.attribute9, hr_api.g_varchar2) or
1161 nvl(hr_ori_shd.g_old_rec.attribute10, hr_api.g_varchar2) <>
1162 nvl(p_rec.attribute10, hr_api.g_varchar2) or
1163 nvl(hr_ori_shd.g_old_rec.attribute11, hr_api.g_varchar2) <>
1164 nvl(p_rec.attribute11, hr_api.g_varchar2) or
1165 nvl(hr_ori_shd.g_old_rec.attribute12, hr_api.g_varchar2) <>
1166 nvl(p_rec.attribute12, hr_api.g_varchar2) or
1167 nvl(hr_ori_shd.g_old_rec.attribute13, hr_api.g_varchar2) <>
1168 nvl(p_rec.attribute13, hr_api.g_varchar2) or
1169 nvl(hr_ori_shd.g_old_rec.attribute14, hr_api.g_varchar2) <>
1170 nvl(p_rec.attribute14, hr_api.g_varchar2) or
1171 nvl(hr_ori_shd.g_old_rec.attribute15, hr_api.g_varchar2) <>
1172 nvl(p_rec.attribute15, hr_api.g_varchar2) or
1173 nvl(hr_ori_shd.g_old_rec.attribute16, hr_api.g_varchar2) <>
1174 nvl(p_rec.attribute16, hr_api.g_varchar2) or
1175 nvl(hr_ori_shd.g_old_rec.attribute17, hr_api.g_varchar2) <>
1176 nvl(p_rec.attribute17, hr_api.g_varchar2) or
1177 nvl(hr_ori_shd.g_old_rec.attribute18, hr_api.g_varchar2) <>
1178 nvl(p_rec.attribute18, hr_api.g_varchar2) or
1179 nvl(hr_ori_shd.g_old_rec.attribute19, hr_api.g_varchar2) <>
1180 nvl(p_rec.attribute19, hr_api.g_varchar2) or
1181 nvl(hr_ori_shd.g_old_rec.attribute20, hr_api.g_varchar2) <>
1182 nvl(p_rec.attribute20, hr_api.g_varchar2) ))
1183 or (p_rec.org_information_id is null) then
1184 --
1185 -- Only execute the validation if absolutely necessary:
1186 -- a) During update, the structure column value or any
1187 -- of the attribute values have actually changed.
1188 -- b) During insert.
1189 --
1190 hr_dflex_utility.ins_or_upd_descflex_attribs
1191 (p_appl_short_name => 'PER'
1192 ,p_descflex_name => 'HR_ORGANIZATION_INFORMATION'
1193 ,p_attribute_category =>p_rec.attribute_category
1194 ,p_attribute1_name => 'ATTRIBUTE1'
1195 ,p_attribute1_value => p_rec.attribute1
1196 ,p_attribute2_name => 'ATTRIBUTE2'
1197 ,p_attribute2_value => p_rec.attribute2
1198 ,p_attribute3_name => 'ATTRIBUTE3'
1199 ,p_attribute3_value => p_rec.attribute3
1200 ,p_attribute4_name => 'ATTRIBUTE4'
1201 ,p_attribute4_value => p_rec.attribute4
1202 ,p_attribute5_name => 'ATTRIBUTE5'
1203 ,p_attribute5_value => p_rec.attribute5
1204 ,p_attribute6_name => 'ATTRIBUTE6'
1205 ,p_attribute6_value => p_rec.attribute6
1206 ,p_attribute7_name => 'ATTRIBUTE7'
1207 ,p_attribute7_value => p_rec.attribute7
1208 ,p_attribute8_name => 'ATTRIBUTE8'
1209 ,p_attribute8_value => p_rec.attribute8
1210 ,p_attribute9_name => 'ATTRIBUTE9'
1211 ,p_attribute9_value => p_rec.attribute9
1212 ,p_attribute10_name => 'ATTRIBUTE10'
1213 ,p_attribute10_value => p_rec.attribute10
1214 ,p_attribute11_name => 'ATTRIBUTE11'
1215 ,p_attribute11_value => p_rec.attribute11
1216 ,p_attribute12_name => 'ATTRIBUTE12'
1217 ,p_attribute12_value => p_rec.attribute12
1218 ,p_attribute13_name => 'ATTRIBUTE13'
1219 ,p_attribute13_value => p_rec.attribute13
1220 ,p_attribute14_name => 'ATTRIBUTE14'
1221 ,p_attribute14_value => p_rec.attribute14
1222 ,p_attribute15_name => 'ATTRIBUTE15'
1223 ,p_attribute15_value => p_rec.attribute15
1224 ,p_attribute16_name => 'ATTRIBUTE16'
1225 ,p_attribute16_value => p_rec.attribute16
1226 ,p_attribute17_name => 'ATTRIBUTE17'
1227 ,p_attribute17_value => p_rec.attribute17
1228 ,p_attribute18_name => 'ATTRIBUTE18'
1229 ,p_attribute18_value => p_rec.attribute18
1230 ,p_attribute19_name => 'ATTRIBUTE19'
1231 ,p_attribute19_value => p_rec.attribute19
1232 ,p_attribute20_name => 'ATTRIBUTE20'
1233 ,p_attribute20_value => p_rec.attribute20
1234 );
1235 end if;
1236 --
1237 hr_utility.set_location(' Leaving:'||l_proc,20);
1238 end chk_df;
1239 --
1240 -- ----------------------------------------------------------------------------
1241 -- |-----------------------< chk_non_updateable_args >------------------------|
1242 -- ----------------------------------------------------------------------------
1243 -- {Start Of Comments}
1244 --
1245 -- Description:
1246 -- This procedure is used to ensure that non updateable attributes have
1247 -- not been updated. If an attribute has been updated an error is generated.
1248 --
1249 -- Pre Conditions:
1250 -- g_old_rec has been populated with details of the values currently in
1251 -- the database.
1252 --
1253 -- In Arguments:
1254 -- p_rec has been populated with the updated values the user would like the
1255 -- record set to.
1256 --
1257 -- Post Success:
1258 -- Processing continues if all the non updateable attributes have not
1259 -- changed.
1260 --
1261 -- Post Failure:
1262 -- An application error is raised if any of the non updatable attributes
1263 -- have been altered.
1264 --
1265 -- {End Of Comments}
1266 -- ----------------------------------------------------------------------------
1267 Procedure chk_non_updateable_args
1268 (p_effective_date in date
1269 ,p_rec in hr_ori_shd.g_rec_type
1270 ) IS
1271 --
1272 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
1273 l_error EXCEPTION;
1274 l_argument varchar2(30);
1275 --
1276 Begin
1277 --
1278 -- Only proceed with the validation if a row exists for the current
1279 -- record in the HR Schema.
1280 --
1281 IF NOT hr_ori_shd.api_updating
1282 (p_org_information_id => p_rec.org_information_id
1283 ,p_object_version_number => p_rec.object_version_number
1284 ) THEN
1285 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
1286 fnd_message.set_token('PROCEDURE ', l_proc);
1287 fnd_message.set_token('STEP ', '5');
1288 fnd_message.raise_error;
1289 END IF;
1290 --
1291 -- EDIT_HERE: Add checks to ensure non-updateable args have
1292 -- not been updated.
1293 --
1294 IF nvl(p_rec.org_information_context, hr_api.g_varchar2) <>
1295 nvl(hr_ori_shd.g_old_rec.org_information_context, hr_api.g_varchar2) THEN
1296 l_argument := 'ORG_INFORMATION_CONTEXT';
1297 RAISE l_error;
1298 END IF;
1299 --
1300 EXCEPTION
1301 WHEN l_error THEN
1302 hr_api.argument_changed_error
1303 (p_api_name => l_proc
1304 ,p_argument => l_argument);
1305 WHEN OTHERS THEN
1306 RAISE;
1307 End chk_non_updateable_args;
1308 --
1309 --
1310 -- ----------------------------------------------------------------------------
1311 -- |-------------------------< chk_organization_id >--------------------------|
1312 -- ----------------------------------------------------------------------------
1313 --
1314 -- Description:
1315 -- Validates that organization_id of organization unit is present in
1316 -- HR_ALL_ORGANIZATION_UNITS table and valid.
1317 --
1318 -- Pre-conditions:
1319 -- None.
1320 --
1321 -- In Arguments:
1322 -- p_organization_id
1323 -- p_effective_date
1324 --
1325 -- Post Success:
1326 -- If the organization_id attribute is valid then
1327 -- normal processing continues
1328 --
1329 -- Post Failure:
1330 -- If the organization_id attribute is invalid then an application
1331 -- error will be raised and processing is terminated.
1332 --
1333 -- Developer/Implementation Notes:
1334 -- Duplicate validation exists on form, so any changes made here
1335 -- or on form must be dual-maintained.
1336 --
1337 -- Access Status:
1338 -- Internal Row Table Handler Use Only.
1339 --
1340 -- {End Of Comments}
1341 --
1342 PROCEDURE chk_organization_id
1343 ( p_organization_id IN hr_organization_information.organization_id%TYPE,
1344 p_effective_date IN DATE)
1345 IS
1346 l_proc VARCHAR2(72) := g_package||'chk_organization_id';
1347 l_exists VARCHAR2(1) := 'N';
1348 --
1349 BEGIN
1350 --
1351 hr_utility.set_location('Entering:'|| l_proc, 10);
1352 --
1353 --
1354 -- Check organization_id presence
1355 --
1356 BEGIN
1357 SELECT 'Y'
1358 INTO l_exists
1359 FROM sys.dual
1360 WHERE EXISTS
1361 (SELECT null
1362 FROM hr_all_organization_units
1363 WHERE organization_id = p_organization_id);
1364 EXCEPTION
1365 WHEN NO_DATA_FOUND THEN NULL;
1366 END;
1367 --
1368 hr_utility.set_location(l_proc, 20);
1369 --
1370 IF l_exists = 'N' THEN
1371 hr_utility.set_message(800, 'HR_289002_INV_ORG_ID');
1372 hr_utility.raise_error;
1373 END IF;
1374 --
1375 --
1376 hr_utility.set_location('Leaving:'||l_proc, 30);
1377 --
1378 END chk_organization_id;
1379 --
1380 -- ----------------------------------------------------------------------------
1381 -- |-------------------------< chk_cls_valid >--------------------------------|
1382 -- ----------------------------------------------------------------------------
1383 --
1384 -- Description:
1385 -- Validates that ORG_INFORMATION1 for ORG_INFORMATION_CONTEXT 'CLASS' is
1386 -- present in HR_LOKUPS table and valid.
1387 --
1388 -- Pre-conditions:
1389 -- None.
1390 --
1391 -- In Arguments:
1392 -- p_org_information_context
1393 -- p_org_information1
1394 -- p_effective_date
1395 --
1396 -- Post Success:
1397 -- If ORG_INFORMATION1 is present and valid then
1398 -- normal processing continues
1399 --
1400 -- Post Failure:
1401 -- If ORG_INFORMATION1 is present and invalid then an application
1402 -- error will be raised and processing is terminated.
1403 --
1404 -- Developer/Implementation Notes:
1405 -- Duplicate validation exists on form, so any changes made here
1406 -- or on form must be dual-maintained.
1407 --
1408 -- Access Status:
1409 -- Internal Row Table Handler Use Only.
1410 --
1411 -- {End Of Comments}
1412 --
1413 PROCEDURE chk_cls_valid
1414 ( p_org_information_context IN hr_organization_information.org_information_context%TYPE,
1415 p_org_information1 IN hr_organization_information.org_information1%TYPE,
1416 p_effective_date IN DATE)
1417 IS
1418 l_proc VARCHAR2(72) := g_package||'chk_cls_valid';
1419 l_exists VARCHAR2(1) := 'N';
1420 --
1421 cursor csr_cls_valid IS
1422 SELECT 'Y'
1423 FROM hr_lookups
1424 WHERE lookup_type = 'ORG_CLASS'
1425 AND lookup_code = p_org_information1
1426 AND enabled_flag = 'Y'
1427 AND p_effective_date BETWEEN nvl(start_date_active,p_effective_date)
1428 AND nvl(end_date_active,p_effective_date);
1429 --
1430 BEGIN
1431 --
1432 hr_utility.set_location('Entering:'|| l_proc, 10);
1433 --
1434 --
1435 -- Check classification
1436 --
1437 IF p_org_information_context = 'CLASS' THEN
1438 IF p_org_information1 IS null THEN
1439 hr_utility.set_message(800, 'HR_52760_NULL_CLSF');
1440 hr_utility.raise_error;
1441 ELSE
1442 OPEN csr_cls_valid;
1443 FETCH csr_cls_valid INTO l_exists;
1444 --
1445 hr_utility.set_location(l_proc, 20);
1446 --
1447 IF csr_cls_valid%notfound THEN
1448 CLOSE csr_cls_valid;
1449 hr_utility.set_message(800, 'HR_52759_INV_CLSF');
1450 hr_utility.raise_error;
1451 ELSE
1452 CLOSE csr_cls_valid;
1453 END IF;
1454 END IF;
1455 END IF;
1456 --
1457 --
1458 hr_utility.set_location('Leaving:'||l_proc, 30);
1459 --
1460 END chk_cls_valid;
1461 --
1462 -- ----------------------------------------------------------------------------
1463 -- |-------------------------< chk_cls_unique >-------------------------------|
1464 -- ----------------------------------------------------------------------------
1465 --
1466 -- Description:
1467 -- Validates that classification of organization unit is not present in
1468 -- HR_ORGANIZATION_INFORMATION table for ORGANIZATION_ID.
1469 --
1470 -- Pre-conditions:
1471 -- None.
1472 --
1473 -- In Arguments:
1474 -- p_organization_id
1475 -- p_org_information1
1476 --
1477 -- Post Success:
1478 -- If classification is not present then
1479 -- normal processing continues
1480 --
1481 -- Post Failure:
1482 -- If classification is already present then an application
1483 -- error will be raised and processing is terminated.
1484 --
1485 -- Developer/Implementation Notes:
1486 -- Duplicate validation exists on form, so any changes made here
1487 -- or on form must be dual-maintained.
1488 --
1489 -- Access Status:
1490 -- Internal Row Table Handler Use Only.
1491 --
1492 -- {End Of Comments}
1493 --
1494 PROCEDURE chk_cls_unique
1495 ( p_organization_id IN hr_organization_information.organization_id%TYPE,
1496 p_org_information1 IN hr_organization_information.org_information1%TYPE)
1497 IS
1498 l_proc VARCHAR2(72) := g_package||'chk_cls_unique';
1499 l_exists VARCHAR2(1) := 'N';
1500 --
1501 BEGIN
1502 --
1503 hr_utility.set_location('Entering:'|| l_proc, 10);
1504 --
1505 --
1506 -- Check classification presence
1507 --
1508 BEGIN
1509 SELECT 'Y'
1510 INTO l_exists
1511 FROM sys.dual
1512 WHERE EXISTS
1513 (SELECT null
1514 FROM hr_organization_information
1515 WHERE organization_id = p_organization_id
1516 AND org_information_context = 'CLASS'
1517 AND org_information1 = p_org_information1);
1518 EXCEPTION
1519 WHEN NO_DATA_FOUND THEN null;
1520 END;
1521 --
1522 hr_utility.set_location(l_proc, 20);
1523 --
1524 IF l_exists = 'Y' THEN
1525 hr_utility.set_message(800, 'HR_52761_ORG_CLSF_EXISTS');
1526 hr_utility.raise_error;
1527 END IF;
1528 --
1529 --
1530 hr_utility.set_location('Leaving:'||l_proc, 30);
1531 --
1532 END chk_cls_unique;
1533 --
1534 --
1535 -- ----------------------------------------------------------------------------
1536 -- |-------------------------< chk_info_type_valid >--------------------------|
1537 -- ----------------------------------------------------------------------------
1538 --
1539 -- Description:
1540 -- Validates that info type is present in the list of info types
1541 -- for all current enabled classifications in
1542 -- HR_ORGANIZATION_INFORMATION table for ORGANIZATION_ID.
1543 --
1544 -- Pre-conditions:
1545 -- None.
1546 --
1547 -- In Arguments:
1548 -- p_organization_id
1549 -- p_org_information_context
1550 --
1551 -- Post Success:
1552 -- If info type is present in the list then
1553 -- normal processing continues
1554 --
1555 -- Post Failure:
1556 -- If info type is not present in the list then an application
1557 -- error will be raised and processing is terminated.
1558 --
1559 -- Developer/Implementation Notes:
1560 -- Duplicate validation exists on form, so any changes made here
1561 -- or on form must be dual-maintained.
1562 --
1563 -- Access Status:
1564 -- Internal Row Table Handler Use Only.
1565 --
1566 -- {End Of Comments}
1567 --
1568 PROCEDURE chk_info_type_valid
1569 ( p_organization_id IN hr_organization_information.organization_id%TYPE,
1570 p_org_information_context IN hr_organization_information.org_information_context%TYPE)
1571 IS
1572 l_proc VARCHAR2(72) := g_package||'chk_info_type_valid';
1573 l_exists VARCHAR2(1) := 'N';
1574 --
1575 BEGIN
1576 --
1577 hr_utility.set_location('Entering:'|| l_proc, 10);
1578 --
1579 --
1580 -- Check info type presence
1581 --
1582 -- Added nav method 'LOC' AT 27/9/01
1583 --
1584 BEGIN
1585 SELECT 'Y'
1586 INTO l_exists
1587 FROM sys.dual
1588 WHERE EXISTS
1589 (SELECT hoit.org_information_type
1590 FROM hr_org_information_types hoit
1591 WHERE hoit.org_information_type = p_org_information_context
1592 AND (hoit.navigation_method = 'GS' OR hoit.navigation_method = 'GM' OR hoit.navigation_method = 'LOC')
1593 AND EXISTS
1594 (SELECT null
1595 FROM hr_org_info_types_by_class hitbc
1596 ,hr_organization_information hoi
1597 WHERE hitbc.org_information_type = hoit.org_information_type
1598 AND hitbc.org_classification = hoi.org_information1
1599 AND hoi.org_information2 = 'Y'
1600 AND hoi.org_information_context = 'CLASS'
1601 AND hoi.organization_id = p_organization_id)
1602 );
1603 EXCEPTION
1604 WHEN NO_DATA_FOUND THEN null;
1605 END;
1606 -- VT maybe we still need it later modified and added to the
1607 -- select statement above
1608 --
1609 -- AND hoit.legislation_code =
1610 -- (SELECT pbg.legislation_code
1611 -- FROM per_business_groups pbg
1612 -- ,hr_all_organization_units haou
1613 -- WHERE haou.organization_id = p_organization_id
1614 -- AND haou.business_group_id = pbg.business_group_id)
1615 --
1616 hr_utility.set_location(l_proc, 20);
1617 --
1618 IF l_exists = 'N' THEN
1619 hr_utility.set_message(800, 'HR_289003_INV_INFO_TYPE');
1620 hr_utility.raise_error;
1621 END IF;
1622 --
1623 --
1624 hr_utility.set_location('Leaving:'||l_proc, 30);
1625 --
1626 END chk_info_type_valid;
1627 --
1628 --
1629 -- ----------------------------------------------------------------------------
1630 -- |-------------------------< chk_info_type_unique >-------------------------|
1631 -- ----------------------------------------------------------------------------
1632 --
1633 -- Description:
1634 -- Validates that info type is present in the
1635 -- HR_ORGANIZATION_INFORMATION table for ORGANIZATION_ID.
1636 --
1637 -- Pre-conditions:
1638 -- None.
1639 --
1640 -- In Arguments:
1641 -- p_organization_id
1642 -- p_org_information_context
1643 --
1644 -- Post Success:
1645 -- If info type is not present then
1646 -- normal processing continues
1647 --
1648 -- Post Failure:
1649 -- If info type is present in then an application
1650 -- error will be raised and processing is terminated.
1651 --
1652 -- Developer/Implementation Notes:
1653 -- Duplicate validation exists on form, so any changes made here
1654 -- or on form must be dual-maintained.
1655 --
1656 -- Access Status:
1657 -- Internal Row Table Handler Use Only.
1658 --
1659 -- {End Of Comments}
1660 --
1661 PROCEDURE chk_info_type_unique
1662 ( p_organization_id IN hr_organization_information.organization_id%TYPE,
1663 p_org_information_context IN hr_organization_information.org_information_context%TYPE)
1664 IS
1665 l_proc VARCHAR2(72) := g_package||'chk_info_type_unique';
1666 l_exists VARCHAR2(1) := 'N';
1667 --
1668 BEGIN
1669 --
1670 hr_utility.set_location('Entering:'|| l_proc, 10);
1671 --
1672 --
1673 -- Check info type presence
1674 --
1675 BEGIN
1676 SELECT 'Y'
1677 INTO l_exists
1678 FROM sys.dual
1679 WHERE EXISTS
1680 (SELECT null
1681 FROM hr_organization_information hoi
1682 ,hr_org_information_types hoit
1683 WHERE hoi.org_information_context = p_org_information_context
1684 AND hoi.organization_id = p_organization_id
1685 AND hoit.org_information_type = p_org_information_context
1686 AND hoit.navigation_method = 'GS');
1687 EXCEPTION
1688 WHEN NO_DATA_FOUND THEN null;
1689 END;
1690 --
1691 hr_utility.set_location(l_proc, 20);
1692 --
1693 IF l_exists = 'Y' THEN
1694 hr_utility.set_message(800, 'HR_289004_INFO_TYPE_EXISTS');
1695 hr_utility.raise_error;
1696 END IF;
1697 --
1698 --
1699 hr_utility.set_location('Leaving:'||l_proc, 30);
1700 --
1701 END chk_info_type_unique;
1702 --
1703 -- Bug 3456540 Start
1704 procedure chk_location(
1705 p_organization_id IN hr_organization_information.organization_id%TYPE
1706 ) is
1707 --
1708 cursor csr_location is
1709 select location_id
1710 from hr_all_organization_units
1711 where organization_id = p_organization_id;
1712 --
1713 l_location_id number;
1714 --
1715 begin
1716 --
1717 open csr_location;
1718 fetch csr_location into l_location_id;
1719 close csr_location;
1720 --
1721 if l_location_id is null then
1722 hr_utility.set_message(800, 'HR_6612_ORG_LEGAL_NO_LOCATION');
1723 hr_utility.raise_error;
1724 end if;
1725 --
1726 end chk_location;
1727 --
1728 --
1729 -- Bug 3456540 End
1730 -- Start of Bug No 2586522
1731 -- ----------------------------------------------------------------------------
1732 -- |---------------------------< check_state_tax_rules >----------------------|
1733 -- ----------------------------------------------------------------------------
1734 --
1735 -- Description:
1736 -- US specific validation to check that if the structure being updated is
1737 -- 'State Tax Rules' and the segment being updated is WC Carrier then
1738 -- check that a WC rate for this carrier is not being referenced by
1739 -- an assignment's 'WC Override Code' on the SCL 'GREs and other data'
1740 --
1741 -- Pre-conditions:
1742 -- When Org_Information_context = 'State Tax Rules'.
1743 --
1744 -- In Arguments:
1745 -- X_Org_Information_ID
1746 -- X_org_information1
1747 -- X_org_information8
1748 --
1749 -- Post Success:
1750 -- Normal processing continues
1751 --
1752 -- Post Failure:
1753 -- Error will be raised and processing is terminated.
1754 --
1755 --
1756 -- Access Status:
1757 -- Internal Row Table Handler Use Only.
1758 --
1759 -- {End Of Comments}
1760 --
1761 Procedure check_state_tax_rules
1762 (X_Org_Information_ID in hr_organization_information.organization_id%TYPE,
1763 X_org_information1 in hr_organization_information.org_information1%TYPE,
1764 X_org_information8 in hr_organization_information.org_information8%TYPE )
1765 as
1766 --
1767 -- declare local variables
1768 --
1769 l_dummy VARCHAR2(1);
1770 l_state_code VARCHAR2(2);
1771 l_carrier_id VARCHAR2(17);
1772 l_proc VARCHAR2(72) := g_package||'check_state_tax_rules';
1773
1774 --
1775 -- declare cursors
1776 --
1777
1778 CURSOR get_orig_values IS
1779 select
1780 org_information1,
1781 org_information8
1782 from
1783 hr_organization_information
1784 where
1785 org_information_id = X_org_information_id;
1786 --
1787
1788 CURSOR check_override IS
1789 SELECT /*+ STAR_TRANSFORMATION */
1790 'x'
1791 FROM
1792 pay_wc_rates wcr,
1793 pay_wc_funds wcf
1794 WHERE
1795 wcf.carrier_id = l_carrier_id AND
1796 wcf.state_code = l_state_code AND
1797 wcr.fund_id = wcf.fund_id
1798 AND EXISTS
1799 ( SELECT 'code referenced in override'
1800 FROM per_assignments_f a,
1801 fnd_id_flex_structures_vl ifs,
1802 hr_soft_coding_keyflex sck
1803 WHERE sck.segment1 = to_char(X_org_information_id) -- #1683897
1804 AND segment8 = to_char(wcr.wc_code)
1805 AND ifs.id_flex_structure_name = 'GREs and other data'
1806 AND sck.id_flex_num = ifs.id_flex_num
1807 AND a.assignment_type = 'E'
1808 AND a.soft_coding_keyflex_id = sck.soft_coding_keyflex_id );
1809
1810 Begin
1811 hr_utility.set_location('Entering:'||l_proc, 10);
1812
1813 --
1814 -- get original values
1815 --
1816 OPEN get_orig_values;
1817 FETCH get_orig_values into l_state_code, l_carrier_id;
1818 CLOSE get_orig_values;
1819 --
1820 -- check if values have changed
1821 --
1822 IF ((l_state_code <> X_org_information1) OR
1823 (NVL(l_carrier_id, X_org_information8) <> X_org_information8) OR
1824 X_org_information8 IS NULL)
1825 THEN
1826 hr_utility.set_location('Entering:'||l_proc, 20);
1827 OPEN check_override;
1828 FETCH check_override into l_dummy;
1829 IF check_override%FOUND
1830 THEN
1831 hr_utility.set_location(l_proc, 30);
1832 hr_utility.set_message(800,'HR_51039_ORG_WC_OVRRD_RATE_REF');
1833 hr_utility.raise_error;
1834 END IF;
1835 CLOSE check_override;
1836 hr_utility.set_location(l_proc, 40);
1837 END IF;
1838 hr_utility.set_location('Leaving :'||l_proc, 50);
1839 END check_state_tax_rules;
1840 --
1841 -- End of bug No 2586522
1842 --
1843 --
1844 -- Start of fix for bug 3679256
1845 --
1846 -- ----------------------------------------------------------------------------
1847 -- |----------------------------< Chk_Bus_grp >------------------------------|
1848 -- ----------------------------------------------------------------------------
1849 --
1850 -- Description:
1851 -- Retrives Business group id based on Org id passed and sets Context values
1852 -- 'Legislation code' and 'Security profile id', which is further used by
1853 -- by Lookup 'HR_LOOKUP'.
1854 --
1855 -- Pre-conditions:
1856 -- Valid organization id is passed.
1857 --
1858 -- In Arguments:
1859 -- p_organization_id
1860 --
1861 --
1862 -- Post Success:
1863 -- Set Context values for LEG CODE and Security Group Id.
1864 --
1865 --
1866 --
1867 -- Access Status:
1868 -- Internal Row Table Handler Use Only.
1869 --
1870 -- {End Of Comments}
1871 --
1872 Procedure chk_bus_grp
1873 (p_organization_id in number
1874 )is
1875
1876
1877 l_proc varchar2(72) := g_package||'chk_business_grp';
1878 l_bg_id number ;
1879 -- Fetch BG Id from Org id.
1880 cursor C_BG is
1881 select business_group_id
1882 from hr_all_organization_units
1883 where organization_id = p_organization_id;
1884
1885 Begin
1886 Open c_bg;
1887 Fetch c_bg into l_bg_id;
1888 hr_api.validate_bus_grp_id(l_bg_id); -- Validate Bus Grp
1889 Close c_bg;
1890
1891
1892 End chk_bus_grp;
1893 --
1894 -- End of fix for bug 3679256
1895 --
1896 -- ----------------------------------------------------------------------------
1897 -- |---------------------------< insert_validate >----------------------------|
1898 -- ----------------------------------------------------------------------------
1899 Procedure insert_validate
1900 (p_effective_date in date
1901 ,p_rec in hr_ori_shd.g_rec_type
1902 ) is
1903 --
1904 l_proc varchar2(72) := g_package||'insert_validate';
1905 l_bg_id number;
1906 --
1907 Begin
1908 hr_utility.set_location('Entering:'||l_proc, 5);
1909 --
1910 -- Call all supporting business operations
1911
1912 hr_api.mandatory_arg_error
1913 (p_api_name => l_proc
1914 ,p_argument => 'ORGANIZATION_ID'
1915 ,p_argument_value => p_rec.organization_id
1916 );
1917 --
1918 --
1919 hr_api.mandatory_arg_error
1920 (p_api_name => l_proc
1921 ,p_argument => 'ORG_INFORMATION_CONTEXT'
1922 ,p_argument_value => p_rec.org_information_context
1923 );
1924 --
1925 -- Validate organization_id
1926 --=========================
1927 chk_organization_id(
1928 p_organization_id => p_rec.organization_id,
1929 p_effective_date => p_effective_date);
1930
1931
1932 -- Check unique business group name
1933 -- ================================
1934 chk_name(
1935 p_org_information1 => p_rec.org_information1,
1936 p_org_information_context => p_rec.org_information_context,
1937 p_organization_id => p_rec.organization_id,
1938 p_org_information2 => p_rec.org_information2);
1939 --
1940 IF p_rec.org_information_context = 'CLASS' THEN
1941
1942 --
1943 -- Start of fix for bug 3679256
1944 --
1945 -- Validate Business Group.
1946 --=========================
1947 If p_rec.org_information1 <> 'HR_BG' then
1948 chk_bus_grp(
1949 p_organization_id => p_rec.organization_id);
1950 End if;
1951 --
1952 -- End of fix for bug 3679256
1953 --
1954
1955 -- Validate classification
1956 --=========================
1957 chk_cls_valid(
1958 p_org_information_context => p_rec.org_information_context,
1959 p_org_information1 => p_rec.org_information1,
1960 p_effective_date => p_effective_date);
1961 --
1962 -- Validate org classification unique
1963 --=========================
1964 chk_cls_unique(
1965 p_organization_id => p_rec.organization_id,
1966 p_org_information1 => p_rec.org_information1);
1967
1968 -- Bug 3456540 Start
1969 -- Validation for legal entity.
1970 -- Check if the organization has a location attached to it
1971 if p_rec.org_information1 = 'HR_LEGAL' and p_rec.org_information2 = 'Y' then
1972 chk_location( p_organization_id => p_rec.organization_id );
1973 end if;
1974 -- Bug 3456540 End
1975 --
1976 ELSE
1977 -- Validate info type
1978 --=========================
1979 chk_info_type_valid(
1980 p_organization_id => p_rec.organization_id,
1981 p_org_information_context => p_rec.org_information_context);
1982 --
1983 -- Validate info type unique
1984 --=========================
1985 chk_info_type_unique(
1986 p_organization_id => p_rec.organization_id,
1987 p_org_information_context => p_rec.org_information_context);
1988 --
1989 END IF;
1990 --
1991 if p_rec.org_information_context = 'Organization Name Alias' then
1992 --
1993 chk_cost_center_start_date
1994 (p_org_information_context => p_rec.org_information_context,
1995 p_org_information2 => p_rec.org_information2,
1996 p_org_information3 => p_rec.org_information3,
1997 p_org_information4 => p_rec.org_information4);
1998 --
1999 chk_cost_center_start_end_date
2000 (p_org_information_context => p_rec.org_information_context,
2001 p_org_information2 => p_rec.org_information2,
2002 p_org_information3 => p_rec.org_information3,
2003 p_org_information4 => p_rec.org_information4);
2004 --
2005 chk_cost_center_valid
2006 (p_organization_id => p_rec.organization_id,
2007 p_org_information_context => p_rec.org_information_context);
2008 --
2009 chk_cost_center_manager_valid
2010 (p_org_information_context => p_rec.org_information_context,
2011 p_org_information2 => p_rec.org_information2,
2012 p_effective_date => p_effective_date);
2013 --
2014 chk_cost_center_man_overlap
2015 (p_organization_id => p_rec.organization_id,
2016 p_org_information_id => p_rec.org_information_id,
2017 p_org_information_context => p_rec.org_information_context,
2018 p_org_information3 => p_rec.org_information3,
2019 p_org_information4 => p_rec.org_information4);
2020 --
2021 end if;
2022 --
2023 --
2024 -- start of fix for Bug #2586522
2025 --
2026 if p_rec.org_information_context = 'FR_ESTAB_INFO' then
2027 HR_ORG_INFORMATION_PKG.Validate_SIRET(X_SIRET => p_rec.org_information2);
2028 elsif p_rec.org_information_context = 'FR_ESTAB_PREV_INFO' then
2029 HR_ORG_INFORMATION_PKG.Validate_SIRET(X_SIRET => p_rec.org_information1);
2030 end if;
2031
2032 if p_rec.org_information_context = 'FR_COMP_INFO' then
2033 HR_ORG_INFORMATION_PKG.Validate_SIREN(X_SIREN => p_rec.org_information1);
2034 elsif p_rec.org_information_context = 'FR_COMP_PREV_INFO' then
2035 HR_ORG_INFORMATION_PKG.Validate_SIREN(X_SIREN => p_rec.org_information1);
2036 end if;
2037
2038 HR_ORG_INFORMATION_PKG.validate_business_group_name
2039 (p_organization_id => p_rec.Organization_Id
2040 ,p_org_information_context => p_rec.Org_Information_Context
2041 ,p_org_information1 => p_rec.Org_Information1
2042 ,p_org_information2 => p_rec.Org_Information2
2043 );
2044
2045 --
2046 -- End of fix for Bug #2586522
2047 --
2048 --
2049 -- Added fix for bug #4745845. Do not validate ddf for not usable OU.
2050 if p_rec.org_information_context = 'Operating Unit Information' and
2051 p_rec.org_information6 = 'N' then
2052 null;
2053 else
2054 hr_ori_bus.chk_ddf(p_rec);
2055 end if;
2056 --
2057 -- Descriptive Flexfield is context dependent HR_ORGANIZATION_INFORMATION
2058 --
2059 --fix for bug 6376908.
2060 --Added the if condition before calling the procedure chk_df.
2061 --
2062 if(p_rec.org_information_context<>'CLASS') then
2063 hr_ori_bus.chk_df(p_rec);
2064 end if;
2065 --
2066 hr_utility.set_location(' Leaving:'||l_proc, 10);
2067 End insert_validate;
2068 --
2069 -- ----------------------------------------------------------------------------
2070 -- |---------------------------< update_validate >----------------------------|
2071 -- ----------------------------------------------------------------------------
2072 Procedure update_validate
2073 (p_effective_date in date
2074 ,p_rec in hr_ori_shd.g_rec_type
2075 ) is
2076 --
2077 cursor c_date (p_id number) is
2078 select min(effective_start_date), max(effective_end_date)
2079 from per_all_people_f
2080 where current_employee_flag='Y'
2081 and person_id=p_id;
2082 l_proc varchar2(72) := g_package||'update_validate';
2083 l_startdate date;
2084 l_enddate date;
2085 --
2086 Begin
2087 hr_utility.set_location('Entering:'||l_proc, 5);
2088 --
2089 -- Call all supporting business operations
2090 --
2091 --
2092 --
2093 hr_api.mandatory_arg_error
2094 (p_api_name => l_proc
2095 ,p_argument => 'ORG_INFORMATION_CONTEXT'
2096 ,p_argument_value => p_rec.org_information_context
2097 );
2098 --
2099 --
2100 -- EDIT_HERE: As this table does not have a mandatory business_group_id
2101 -- column, ensure client_info is populated by calling a suitable
2102 -- ???_???_bus.set_security_group_id procedure, or add one of the following
2103 -- comments:
2104 -- "-- No business group context. HR_STANDARD_LOOKUPS used for validation."
2105 -- "-- CLIENT_INFO not set. No lookup validation or joins to HR_LOOKUPS."
2106 --
2107 -- Check unique business group name
2108 -- ================================
2109 chk_name(
2110 p_org_information1 => p_rec.org_information1,
2111 p_org_information_context => p_rec.org_information_context,
2112 p_organization_id => p_rec.organization_id,
2113 p_org_information2 => p_rec.org_information2
2114 );
2115 --
2116 chk_non_updateable_args
2117 (p_effective_date => p_effective_date
2118 ,p_rec => p_rec
2119 );
2120 --
2121 -- 3456540 Start
2122 if p_rec.org_information_context = 'CLASS' then
2123 -- Validation for legal entity.
2124 -- Check if the organization has a location attached to it
2125 if p_rec.org_information1 = 'HR_LEGAL' and p_rec.org_information2 = 'Y' then
2126 chk_location( p_organization_id => p_rec.organization_id );
2127 end if;
2128 end if;
2129 -- 3456540 end
2130 if p_rec.org_information_context = 'Organization Name Alias' then
2131 --
2132 chk_cost_center_start_date
2133 (p_org_information_context => p_rec.org_information_context,
2134 p_org_information2 => p_rec.org_information2,
2135 p_org_information3 => p_rec.org_information3,
2136 p_org_information4 => p_rec.org_information4);
2137 --
2138 chk_cost_center_start_end_date
2139 (p_org_information_context => p_rec.org_information_context,
2140 p_org_information2 => p_rec.org_information2,
2141 p_org_information3 => p_rec.org_information3,
2142 p_org_information4 => p_rec.org_information4);
2143 --
2144 chk_cost_center_valid
2145 (p_organization_id => p_rec.organization_id,
2146 p_org_information_context => p_rec.org_information_context);
2147 --
2148 chk_cost_center_manager_valid
2149 (p_org_information_context => p_rec.org_information_context,
2150 p_org_information2 => p_rec.org_information2,
2151 p_effective_date => p_effective_date);
2152 --
2153 chk_cost_center_man_overlap
2154 (p_organization_id => p_rec.organization_id,
2155 p_org_information_id => p_rec.org_information_id,
2156 p_org_information_context => p_rec.org_information_context,
2157 p_org_information3 => p_rec.org_information3,
2158 p_org_information4 => p_rec.org_information4);
2159 --
2160 end if;
2161 --
2162 --
2163 -- start of fix for Bug #2586522
2164 --
2165 IF (p_rec.org_information_context = 'State Tax Rules')
2166 THEN
2167 check_state_tax_rules
2168 (X_Org_Information_ID => p_rec.org_information_id,
2169 X_Org_information1 => p_rec.org_information1,
2170 X_Org_information8 => p_rec.org_information8);
2171 END IF;
2172
2173 if p_rec.org_information_context = 'FR_ESTAB_INFO' then
2174 HR_ORG_INFORMATION_PKG.Validate_SIRET(X_SIRET => p_rec.org_information2);
2175 elsif p_rec.org_information_context = 'FR_ESTAB_PREV_INFO' then
2176 HR_ORG_INFORMATION_PKG.Validate_SIRET(X_SIRET => p_rec.org_information1);
2177 end if;
2178
2179 if p_rec.org_information_context = 'FR_COMP_INFO' then
2180 HR_ORG_INFORMATION_PKG.Validate_SIREN(X_SIREN => p_rec.org_information1);
2181 elsif p_rec.org_information_context = 'FR_COMP_PREV_INFO' then
2182 HR_ORG_INFORMATION_PKG.Validate_SIREN(X_SIREN => p_rec.org_information1);
2183 end if;
2184
2185 HR_ORG_INFORMATION_PKG.validate_business_group_name
2186 (p_organization_id => p_rec.Organization_Id
2187 ,p_org_information_context => p_rec.Org_Information_Context
2188 ,p_org_information1 => p_rec.Org_Information1
2189 ,p_org_information2 => p_rec.Org_Information2
2190 );
2191 /* To check uniqueness of identifier segment in extra information type
2192 of 'grouping unit information' for FR Public Section'*/
2193 /*
2194 if p_rec.Org_Information_Context = 'FR_PQH_GROUPING_UNIT_INFO' then
2195 pqh_fr_org_validate_pkg.check_unique_identifier
2196 ( p_org_information_context => p_rec.Org_Information_Context,
2197 p_org_information_id => NULL,
2198 p_org_information6 => p_rec.Org_Information6);
2199 end if;
2200 */
2201 --
2202 -- Added by FS
2203 -- End of fix for Bug #2586522
2204 --
2205
2206 --Added for 3034234.Start of fix.
2207 IF (p_rec.org_information_context = 'Organization Name Alias') then
2208 hr_utility.set_location('The person:'|| p_rec.org_information2,333);
2209 open c_date(to_number(p_rec.org_information2));
2210 fetch c_date into l_startdate,l_enddate;
2211 hr_utility.set_location('Start:'||l_startdate||' Enddate :'||l_enddate,555);
2212 if c_date%notfound then
2213 close c_date;
2214 if(fnd_date.canonical_to_date(p_rec.org_information4) > fnd_date.date_to_canonical(l_enddate)) then
2215
2216 hr_ori_bus.chk_ddf(p_rec);
2217 --
2218 end if;
2219 close c_date;
2220 end if;
2221
2222 else
2223
2224 hr_ori_bus.chk_ddf(p_rec);
2225 --
2226 end if;
2227 -- End of fix for 3034234
2228 --
2229 hr_ori_bus.chk_df(p_rec);
2230 hr_utility.set_location(' Leaving:'||l_proc, 10);
2231 --
2232 End update_validate;
2233 --
2234 -- ----------------------------------------------------------------------------
2235 -- |---------------------------< delete_validate >----------------------------|
2236 -- ----------------------------------------------------------------------------
2237 Procedure delete_validate
2238 (p_rec in hr_ori_shd.g_rec_type
2239 ) is
2240 --
2241 l_proc varchar2(72) := g_package||'delete_validate';
2242 --
2243 Begin
2244 hr_utility.set_location('Entering:'||l_proc, 5);
2245 --
2246 -- Call all supporting business operations
2247 --
2248 hr_utility.set_location(' Leaving:'||l_proc, 10);
2249 End delete_validate;
2250 --
2251 end hr_ori_bus;
[Home] [Help]