1 Package Body hr_ori_bus as
2 /* $Header: hrorirhi.pkb 120.5.12020000.2 2012/09/10 10:32:16 swrajapa 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_all_organization_units org -- Modified for bug # 6794638 to pick from base table
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 cursor csr_leg_code is
830 select ori2.ORG_INFORMATION9 from
831 hr_all_organization_units org,
832 hr_organization_information ori1,
833 hr_organization_information ori2
834 where ori1.ORG_INFORMATION_ID=p_org_information_id
835 and org.ORGANIZATION_ID=ori1.ORGANIZATION_ID
836 and ori2.ORGANIZATION_ID=org.business_group_id
837 and ori2.ORG_INFORMATION_CONTEXT='Business Group Information'; -- bug14506616
838 --
839 -- Declare local variables
840 --
841 l_legislation_code varchar2(150);
842 l_proc varchar2(72) := g_package||'return_legislation_code';
843 --
844 Begin
845 --
846 hr_utility.set_location('Entering:'|| l_proc, 10);
847 --
848 -- Ensure that all the mandatory parameter are not null
849 --
850 hr_api.mandatory_arg_error
851 (p_api_name => l_proc
852 ,p_argument => 'org_information_id'
853 ,p_argument_value => p_org_information_id
854 );
855 --
856 if ( nvl(hr_ori_bus.g_org_information_id, hr_api.g_number)
857 = p_org_information_id) then
858 --
859 -- The legislation code has already been found with a previous
860 -- call to this function. Just return the value in the global
861 -- variable.
862 --
863 l_legislation_code := hr_ori_bus.g_legislation_code;
864 hr_utility.set_location(l_proc, 20);
865 else
866 --
867 -- The ID is different to the last call to this function
868 -- or this is the first call to this function.
869 --
870 open csr_leg_code;
871 fetch csr_leg_code into l_legislation_code;
872 --
873 if csr_leg_code%notfound then
874 --
875 -- The primary key is invalid therefore we must error
876 --
877 close csr_leg_code;
878 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
879 fnd_message.raise_error;
880 end if;
881 hr_utility.set_location(l_proc,30);
882 --
883 -- Set the global variables so the values are
884 -- available for the next call to this function.
885 --
886 close csr_leg_code;
887 hr_ori_bus.g_org_information_id:= p_org_information_id;
888 hr_ori_bus.g_legislation_code := l_legislation_code;
889 end if;
890 hr_utility.set_location(' Leaving:'|| l_proc, 40);
891 return l_legislation_code;
892 end return_legislation_code;
893 -- ----------------------------------------------------------------------------
894 -- |-------------------------< chk_name >-------------------------------------|
895 -- ----------------------------------------------------------------------------
896 --
897 -- if the record being updated or inserted is a business group classification
898 -- then this procedure checks that the business group name is unique
899 --
900 -- Access Status:
901 -- Internal Development Use Only.
902 --
903 -- {End Of Comments}
904 --
905 PROCEDURE chk_name
906 (p_org_information1 IN hr_organization_information.org_information1%TYPE,
907 p_org_information_context IN hr_organization_information.org_information_context%TYPE,
908 p_organization_id IN number, --default null, -- R115.21
909 p_org_information2 IN hr_organization_information.org_information2%TYPE
910 )
911 IS
912 l_proc VARCHAR2(72) := g_package||'chk_name';
913 l_name hr_all_organization_units.name%TYPE;
914 l_exists number;
915 BEGIN
916 --
917 hr_utility.set_location('Entering:'|| l_proc, 10);
918 --
919 --
920 --
921 hr_utility.set_location(l_proc, 20);
922 --
923 --
924 -- Check that the business group name is unique if we are adding or updating to
925 -- a business group classification
926 --
927 select name into l_name from hr_all_organization_units where organization_id = p_organization_id;
928
929 if p_org_information1 = 'HR_BG'
930 and p_org_information_context = 'CLASS'
931 and p_org_information2 = 'Y' then
932
933 select count(*)
934 into l_exists
935 from hr_organization_information i, hr_all_organization_units u
936 where i.organization_id <> p_organization_id
937 and i.organization_id = u.organization_id
938 and i.org_information1='HR_BG'
939 and i.org_information_context='CLASS'
940 and i.org_information2 ='Y'
941 and u.name = l_name;
942 if l_exists >0 then
943 hr_utility.set_message(800, 'HR_289381_DUPLICATE_BG');
944 hr_utility.raise_error;
945 end if;
946 end if;
947 exception
948 when no_data_found then
949 hr_utility.set_message(800, 'HR_289002_INV_ORG_ID');
950 hr_utility.raise_error;
951
952 end chk_name;
953
954 -- ----------------------------------------------------------------------------
955 -- |-----------------------------< chk_ddf >----------------------------------|
956 -- ----------------------------------------------------------------------------
957 --
958 -- Description:
959 -- Validates all the Developer Descriptive Flexfield values.
960 --
961 -- Prerequisites:
962 -- All other columns have been validated. Must be called as the
963 -- last step from insert_validate and update_validate.
964 --
965 -- In Arguments:
966 -- p_rec
967 --
968 -- Post Success:
969 -- If the Developer Descriptive Flexfield structure column and data values
970 -- are all valid this procedure will end normally and processing will
971 -- continue.
972 --
973 -- Post Failure:
974 -- If the Developer Descriptive Flexfield structure column value or any of
975 -- the data values are invalid then an application error is raised as
976 -- a PL/SQL exception.
977 --
978 -- Access Status:
979 -- Internal Row Handler Use Only.
980 --
981 -- ----------------------------------------------------------------------------
982 procedure chk_ddf
983 (p_rec in hr_ori_shd.g_rec_type
984 ) is
985 --
986 l_proc varchar2(72) := g_package || 'chk_ddf';
987 --
988 begin
989 hr_utility.set_location('Entering:'||l_proc,10);
990 --
991 if ((p_rec.org_information_id is not null) and (
992 nvl(hr_ori_shd.g_old_rec.org_information_id, hr_api.g_number) <>
993 nvl(p_rec.org_information_id, hr_api.g_number) or
994 nvl(hr_ori_shd.g_old_rec.org_information_context, hr_api.g_varchar2) <>
995 nvl(p_rec.org_information_context, hr_api.g_varchar2) or
996 nvl(hr_ori_shd.g_old_rec.org_information1, hr_api.g_varchar2) <>
997 nvl(p_rec.org_information1, hr_api.g_varchar2) or
998 nvl(hr_ori_shd.g_old_rec.org_information10, hr_api.g_varchar2) <>
999 nvl(p_rec.org_information10, hr_api.g_varchar2) or
1000 nvl(hr_ori_shd.g_old_rec.org_information11, hr_api.g_varchar2) <>
1001 nvl(p_rec.org_information11, hr_api.g_varchar2) or
1002 nvl(hr_ori_shd.g_old_rec.org_information12, hr_api.g_varchar2) <>
1003 nvl(p_rec.org_information12, hr_api.g_varchar2) or
1004 nvl(hr_ori_shd.g_old_rec.org_information13, hr_api.g_varchar2) <>
1005 nvl(p_rec.org_information13, hr_api.g_varchar2) or
1006 nvl(hr_ori_shd.g_old_rec.org_information14, hr_api.g_varchar2) <>
1007 nvl(p_rec.org_information14, hr_api.g_varchar2) or
1008 nvl(hr_ori_shd.g_old_rec.org_information15, hr_api.g_varchar2) <>
1009 nvl(p_rec.org_information15, hr_api.g_varchar2) or
1010 nvl(hr_ori_shd.g_old_rec.org_information16, hr_api.g_varchar2) <>
1011 nvl(p_rec.org_information16, hr_api.g_varchar2) or
1012 nvl(hr_ori_shd.g_old_rec.org_information17, hr_api.g_varchar2) <>
1013 nvl(p_rec.org_information17, hr_api.g_varchar2) or
1014 nvl(hr_ori_shd.g_old_rec.org_information18, hr_api.g_varchar2) <>
1015 nvl(p_rec.org_information18, hr_api.g_varchar2) or
1016 nvl(hr_ori_shd.g_old_rec.org_information19, hr_api.g_varchar2) <>
1017 nvl(p_rec.org_information19, hr_api.g_varchar2) or
1018 nvl(hr_ori_shd.g_old_rec.org_information2, hr_api.g_varchar2) <>
1019 nvl(p_rec.org_information2, hr_api.g_varchar2) or
1020 nvl(hr_ori_shd.g_old_rec.org_information20, hr_api.g_varchar2) <>
1021 nvl(p_rec.org_information20, hr_api.g_varchar2) or
1022 nvl(hr_ori_shd.g_old_rec.org_information3, hr_api.g_varchar2) <>
1023 nvl(p_rec.org_information3, hr_api.g_varchar2) or
1024 nvl(hr_ori_shd.g_old_rec.org_information4, hr_api.g_varchar2) <>
1025 nvl(p_rec.org_information4, hr_api.g_varchar2) or
1026 nvl(hr_ori_shd.g_old_rec.org_information5, hr_api.g_varchar2) <>
1027 nvl(p_rec.org_information5, hr_api.g_varchar2) or
1028 nvl(hr_ori_shd.g_old_rec.org_information6, hr_api.g_varchar2) <>
1029 nvl(p_rec.org_information6, hr_api.g_varchar2) or
1030 nvl(hr_ori_shd.g_old_rec.org_information7, hr_api.g_varchar2) <>
1031 nvl(p_rec.org_information7, hr_api.g_varchar2) or
1032 nvl(hr_ori_shd.g_old_rec.org_information8, hr_api.g_varchar2) <>
1033 nvl(p_rec.org_information8, hr_api.g_varchar2) or
1034 nvl(hr_ori_shd.g_old_rec.org_information9, hr_api.g_varchar2) <>
1035 nvl(p_rec.org_information9, hr_api.g_varchar2) ))
1036 or (p_rec.org_information_id is null) then
1037 --
1038 -- Only execute the validation if absolutely necessary:
1039 -- a) During update, the structure column value or any
1040 -- of the attribute values have actually changed.
1041 -- b) During insert.
1042 --
1043 hr_utility.set_location('context = '||p_rec.org_information_context,20);
1044 hr_utility.set_location('org_information1 = '||p_rec.org_information1,30);
1045 hr_utility.set_location('org_information2 = '||p_rec.org_information2,40);
1046 hr_utility.set_location('org_information3 = '||p_rec.org_information3,50);
1047 hr_utility.set_location('org_information4 = '||p_rec.org_information4,60);
1048 hr_utility.set_location('org_information5 = '||p_rec.org_information5,70);
1049
1050 /*
1051 ** Some valuesets used by this flexfield require additional information
1052 ** this will be passed using profile options. We will create these and
1053 ** set these on-the-fly now and then we'll call the flex code.
1054 */
1055 fnd_profile.put('PER_ORGANIZATION_ID',p_rec.organization_id);
1056 fnd_profile.put('PER_ORG_INFORMATION_ID',
1057 nvl(to_number(p_rec.org_information_id),-1));
1058 hr_utility.set_location('PER_ORG_INFORMATION_ID'||
1059 fnd_profile.value('PER_ORG_INFORMATION_ID'),80);
1060 hr_utility.set_location('PER_ORGANIZATION_ID'||
1061 fnd_profile.value('PER_ORGANIZATION_ID'),80);
1062
1063 hr_dflex_utility.ins_or_upd_descflex_attribs
1064 (p_appl_short_name => 'PER'
1065 ,p_descflex_name => 'Org Developer DF'
1066 ,p_attribute_category => p_rec.org_information_context
1067 ,p_attribute1_name => 'ORG_INFORMATION1'
1068 ,p_attribute1_value => p_rec.org_information1
1069 ,p_attribute2_name => 'ORG_INFORMATION2'
1070 ,p_attribute2_value => p_rec.org_information2
1071 ,p_attribute3_name => 'ORG_INFORMATION3'
1072 ,p_attribute3_value => p_rec.org_information3
1073 ,p_attribute4_name => 'ORG_INFORMATION4'
1074 ,p_attribute4_value => p_rec.org_information4
1075 ,p_attribute5_name => 'ORG_INFORMATION5'
1076 ,p_attribute5_value => p_rec.org_information5
1077 ,p_attribute6_name => 'ORG_INFORMATION6'
1078 ,p_attribute6_value => p_rec.org_information6
1079 ,p_attribute7_name => 'ORG_INFORMATION7'
1080 ,p_attribute7_value => p_rec.org_information7
1081 ,p_attribute8_name => 'ORG_INFORMATION8'
1082 ,p_attribute8_value => p_rec.org_information8
1083 ,p_attribute9_name => 'ORG_INFORMATION9'
1084 ,p_attribute9_value => p_rec.org_information9
1085 ,p_attribute10_name => 'ORG_INFORMATION10'
1086 ,p_attribute10_value => p_rec.org_information10
1087 ,p_attribute11_name => 'ORG_INFORMATION11'
1088 ,p_attribute11_value => p_rec.org_information11
1089 ,p_attribute12_name => 'ORG_INFORMATION12'
1090 ,p_attribute12_value => p_rec.org_information12
1091 ,p_attribute13_name => 'ORG_INFORMATION13'
1092 ,p_attribute13_value => p_rec.org_information13
1093 ,p_attribute14_name => 'ORG_INFORMATION14'
1094 ,p_attribute14_value => p_rec.org_information14
1095 ,p_attribute15_name => 'ORG_INFORMATION15'
1096 ,p_attribute15_value => p_rec.org_information15
1097 ,p_attribute16_name => 'ORG_INFORMATION16'
1098 ,p_attribute16_value => p_rec.org_information16
1099 ,p_attribute17_name => 'ORG_INFORMATION17'
1100 ,p_attribute17_value => p_rec.org_information17
1101 ,p_attribute18_name => 'ORG_INFORMATION18'
1102 ,p_attribute18_value => p_rec.org_information18
1103 ,p_attribute19_name => 'ORG_INFORMATION19'
1104 ,p_attribute19_value => p_rec.org_information19
1105 ,p_attribute20_name => 'ORG_INFORMATION20'
1106 ,p_attribute20_value => p_rec.org_information20
1107 );
1108 end if;
1109 --
1110 hr_utility.set_location(' Leaving:'||l_proc,100);
1111 end chk_ddf;
1112 --
1113 -- ----------------------------------------------------------------------------
1114 -- |------------------------------< chk_df >----------------------------------|
1115 -- ----------------------------------------------------------------------------
1116 --
1117 -- Description:
1118 -- Validates all the Descriptive Flexfield values.
1119 --
1120 -- Prerequisites:
1121 -- All other columns have been validated. Must be called as the
1122 -- last step from insert_validate and update_validate.
1123 --
1124 -- In Arguments:
1125 -- p_rec
1126 --
1127 -- Post Success:
1128 -- If the Descriptive Flexfield structure column and data values are
1129 -- all valid this procedure will end normally and processing will
1130 -- continue.
1131 --
1132 -- Post Failure:
1133 -- If the Descriptive Flexfield structure column value or any of
1134 -- the data values are invalid then an application error is raised as
1135 -- a PL/SQL exception.
1136 --
1137 -- Access Status:
1138 -- Internal Row Handler Use Only.
1139 --
1140 -- ----------------------------------------------------------------------------
1141 procedure chk_df
1142 (p_rec in hr_ori_shd.g_rec_type
1143 ) is
1144 --
1145 l_proc varchar2(72) := g_package || 'chk_df';
1146 --
1147 begin
1148 hr_utility.set_location('Entering:'||l_proc,10);
1149 --
1150 if ((p_rec.org_information_id is not null) and (
1151 nvl(hr_ori_shd.g_old_rec.attribute_category, hr_api.g_varchar2) <>
1152 nvl(p_rec.attribute_category, hr_api.g_varchar2) or
1153 nvl(hr_ori_shd.g_old_rec.attribute1, hr_api.g_varchar2) <>
1154 nvl(p_rec.attribute1, hr_api.g_varchar2) or
1155 nvl(hr_ori_shd.g_old_rec.attribute2, hr_api.g_varchar2) <>
1156 nvl(p_rec.attribute2, hr_api.g_varchar2) or
1157 nvl(hr_ori_shd.g_old_rec.attribute3, hr_api.g_varchar2) <>
1158 nvl(p_rec.attribute3, hr_api.g_varchar2) or
1159 nvl(hr_ori_shd.g_old_rec.attribute4, hr_api.g_varchar2) <>
1160 nvl(p_rec.attribute4, hr_api.g_varchar2) or
1161 nvl(hr_ori_shd.g_old_rec.attribute5, hr_api.g_varchar2) <>
1162 nvl(p_rec.attribute5, hr_api.g_varchar2) or
1163 nvl(hr_ori_shd.g_old_rec.attribute6, hr_api.g_varchar2) <>
1164 nvl(p_rec.attribute6, hr_api.g_varchar2) or
1165 nvl(hr_ori_shd.g_old_rec.attribute7, hr_api.g_varchar2) <>
1166 nvl(p_rec.attribute7, hr_api.g_varchar2) or
1167 nvl(hr_ori_shd.g_old_rec.attribute8, hr_api.g_varchar2) <>
1168 nvl(p_rec.attribute8, hr_api.g_varchar2) or
1169 nvl(hr_ori_shd.g_old_rec.attribute9, hr_api.g_varchar2) <>
1170 nvl(p_rec.attribute9, hr_api.g_varchar2) or
1171 nvl(hr_ori_shd.g_old_rec.attribute10, hr_api.g_varchar2) <>
1172 nvl(p_rec.attribute10, hr_api.g_varchar2) or
1173 nvl(hr_ori_shd.g_old_rec.attribute11, hr_api.g_varchar2) <>
1174 nvl(p_rec.attribute11, hr_api.g_varchar2) or
1175 nvl(hr_ori_shd.g_old_rec.attribute12, hr_api.g_varchar2) <>
1176 nvl(p_rec.attribute12, hr_api.g_varchar2) or
1177 nvl(hr_ori_shd.g_old_rec.attribute13, hr_api.g_varchar2) <>
1178 nvl(p_rec.attribute13, hr_api.g_varchar2) or
1179 nvl(hr_ori_shd.g_old_rec.attribute14, hr_api.g_varchar2) <>
1180 nvl(p_rec.attribute14, hr_api.g_varchar2) or
1181 nvl(hr_ori_shd.g_old_rec.attribute15, hr_api.g_varchar2) <>
1182 nvl(p_rec.attribute15, hr_api.g_varchar2) or
1183 nvl(hr_ori_shd.g_old_rec.attribute16, hr_api.g_varchar2) <>
1184 nvl(p_rec.attribute16, hr_api.g_varchar2) or
1185 nvl(hr_ori_shd.g_old_rec.attribute17, hr_api.g_varchar2) <>
1186 nvl(p_rec.attribute17, hr_api.g_varchar2) or
1187 nvl(hr_ori_shd.g_old_rec.attribute18, hr_api.g_varchar2) <>
1188 nvl(p_rec.attribute18, hr_api.g_varchar2) or
1189 nvl(hr_ori_shd.g_old_rec.attribute19, hr_api.g_varchar2) <>
1190 nvl(p_rec.attribute19, hr_api.g_varchar2) or
1191 nvl(hr_ori_shd.g_old_rec.attribute20, hr_api.g_varchar2) <>
1192 nvl(p_rec.attribute20, hr_api.g_varchar2) ))
1193 or (p_rec.org_information_id is null) then
1194 --
1195 -- Only execute the validation if absolutely necessary:
1196 -- a) During update, the structure column value or any
1197 -- of the attribute values have actually changed.
1198 -- b) During insert.
1199 --
1200 hr_dflex_utility.ins_or_upd_descflex_attribs
1201 (p_appl_short_name => 'PER'
1202 ,p_descflex_name => 'HR_ORGANIZATION_INFORMATION'
1203 ,p_attribute_category =>p_rec.attribute_category
1204 ,p_attribute1_name => 'ATTRIBUTE1'
1205 ,p_attribute1_value => p_rec.attribute1
1206 ,p_attribute2_name => 'ATTRIBUTE2'
1207 ,p_attribute2_value => p_rec.attribute2
1208 ,p_attribute3_name => 'ATTRIBUTE3'
1209 ,p_attribute3_value => p_rec.attribute3
1210 ,p_attribute4_name => 'ATTRIBUTE4'
1211 ,p_attribute4_value => p_rec.attribute4
1212 ,p_attribute5_name => 'ATTRIBUTE5'
1213 ,p_attribute5_value => p_rec.attribute5
1214 ,p_attribute6_name => 'ATTRIBUTE6'
1215 ,p_attribute6_value => p_rec.attribute6
1216 ,p_attribute7_name => 'ATTRIBUTE7'
1217 ,p_attribute7_value => p_rec.attribute7
1218 ,p_attribute8_name => 'ATTRIBUTE8'
1219 ,p_attribute8_value => p_rec.attribute8
1220 ,p_attribute9_name => 'ATTRIBUTE9'
1221 ,p_attribute9_value => p_rec.attribute9
1222 ,p_attribute10_name => 'ATTRIBUTE10'
1223 ,p_attribute10_value => p_rec.attribute10
1224 ,p_attribute11_name => 'ATTRIBUTE11'
1225 ,p_attribute11_value => p_rec.attribute11
1226 ,p_attribute12_name => 'ATTRIBUTE12'
1227 ,p_attribute12_value => p_rec.attribute12
1228 ,p_attribute13_name => 'ATTRIBUTE13'
1229 ,p_attribute13_value => p_rec.attribute13
1230 ,p_attribute14_name => 'ATTRIBUTE14'
1231 ,p_attribute14_value => p_rec.attribute14
1232 ,p_attribute15_name => 'ATTRIBUTE15'
1233 ,p_attribute15_value => p_rec.attribute15
1234 ,p_attribute16_name => 'ATTRIBUTE16'
1235 ,p_attribute16_value => p_rec.attribute16
1236 ,p_attribute17_name => 'ATTRIBUTE17'
1237 ,p_attribute17_value => p_rec.attribute17
1238 ,p_attribute18_name => 'ATTRIBUTE18'
1239 ,p_attribute18_value => p_rec.attribute18
1240 ,p_attribute19_name => 'ATTRIBUTE19'
1241 ,p_attribute19_value => p_rec.attribute19
1242 ,p_attribute20_name => 'ATTRIBUTE20'
1243 ,p_attribute20_value => p_rec.attribute20
1244 );
1245 end if;
1246 --
1247 hr_utility.set_location(' Leaving:'||l_proc,20);
1248 end chk_df;
1249 --
1250 -- ----------------------------------------------------------------------------
1251 -- |-----------------------< chk_non_updateable_args >------------------------|
1252 -- ----------------------------------------------------------------------------
1253 -- {Start Of Comments}
1254 --
1255 -- Description:
1256 -- This procedure is used to ensure that non updateable attributes have
1257 -- not been updated. If an attribute has been updated an error is generated.
1258 --
1259 -- Pre Conditions:
1260 -- g_old_rec has been populated with details of the values currently in
1261 -- the database.
1262 --
1263 -- In Arguments:
1264 -- p_rec has been populated with the updated values the user would like the
1265 -- record set to.
1266 --
1267 -- Post Success:
1268 -- Processing continues if all the non updateable attributes have not
1269 -- changed.
1270 --
1271 -- Post Failure:
1272 -- An application error is raised if any of the non updatable attributes
1273 -- have been altered.
1274 --
1275 -- {End Of Comments}
1276 -- ----------------------------------------------------------------------------
1277 Procedure chk_non_updateable_args
1278 (p_effective_date in date
1279 ,p_rec in hr_ori_shd.g_rec_type
1280 ) IS
1281 --
1282 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
1283 l_error EXCEPTION;
1284 l_argument varchar2(30);
1285 --
1286 Begin
1287 --
1288 -- Only proceed with the validation if a row exists for the current
1289 -- record in the HR Schema.
1290 --
1291 IF NOT hr_ori_shd.api_updating
1292 (p_org_information_id => p_rec.org_information_id
1293 ,p_object_version_number => p_rec.object_version_number
1294 ) THEN
1295 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
1296 fnd_message.set_token('PROCEDURE ', l_proc);
1297 fnd_message.set_token('STEP ', '5');
1298 fnd_message.raise_error;
1299 END IF;
1300 --
1301 -- EDIT_HERE: Add checks to ensure non-updateable args have
1302 -- not been updated.
1303 --
1304 IF nvl(p_rec.org_information_context, hr_api.g_varchar2) <>
1305 nvl(hr_ori_shd.g_old_rec.org_information_context, hr_api.g_varchar2) THEN
1306 l_argument := 'ORG_INFORMATION_CONTEXT';
1307 RAISE l_error;
1308 END IF;
1309 --
1310 EXCEPTION
1311 WHEN l_error THEN
1312 hr_api.argument_changed_error
1313 (p_api_name => l_proc
1314 ,p_argument => l_argument);
1315 WHEN OTHERS THEN
1316 RAISE;
1317 End chk_non_updateable_args;
1318 --
1319 --
1320 -- ----------------------------------------------------------------------------
1321 -- |-------------------------< chk_organization_id >--------------------------|
1322 -- ----------------------------------------------------------------------------
1323 --
1324 -- Description:
1325 -- Validates that organization_id of organization unit is present in
1326 -- HR_ALL_ORGANIZATION_UNITS table and valid.
1327 --
1328 -- Pre-conditions:
1329 -- None.
1330 --
1331 -- In Arguments:
1332 -- p_organization_id
1333 -- p_effective_date
1334 --
1335 -- Post Success:
1336 -- If the organization_id attribute is valid then
1337 -- normal processing continues
1338 --
1339 -- Post Failure:
1340 -- If the organization_id attribute is invalid then an application
1341 -- error will be raised and processing is terminated.
1342 --
1343 -- Developer/Implementation Notes:
1344 -- Duplicate validation exists on form, so any changes made here
1345 -- or on form must be dual-maintained.
1346 --
1347 -- Access Status:
1348 -- Internal Row Table Handler Use Only.
1349 --
1350 -- {End Of Comments}
1351 --
1352 PROCEDURE chk_organization_id
1353 ( p_organization_id IN hr_organization_information.organization_id%TYPE,
1354 p_effective_date IN DATE)
1355 IS
1356 l_proc VARCHAR2(72) := g_package||'chk_organization_id';
1357 l_exists VARCHAR2(1) := 'N';
1358 --
1359 BEGIN
1360 --
1361 hr_utility.set_location('Entering:'|| l_proc, 10);
1362 --
1363 --
1364 -- Check organization_id presence
1365 --
1366 BEGIN
1367 SELECT 'Y'
1368 INTO l_exists
1369 FROM sys.dual
1370 WHERE EXISTS
1371 (SELECT null
1372 FROM hr_all_organization_units
1373 WHERE organization_id = p_organization_id);
1374 EXCEPTION
1375 WHEN NO_DATA_FOUND THEN NULL;
1376 END;
1377 --
1378 hr_utility.set_location(l_proc, 20);
1379 --
1380 IF l_exists = 'N' THEN
1381 hr_utility.set_message(800, 'HR_289002_INV_ORG_ID');
1382 hr_utility.raise_error;
1383 END IF;
1384 --
1385 --
1386 hr_utility.set_location('Leaving:'||l_proc, 30);
1387 --
1388 END chk_organization_id;
1389 --
1390 -- ----------------------------------------------------------------------------
1391 -- |-------------------------< chk_cls_valid >--------------------------------|
1392 -- ----------------------------------------------------------------------------
1393 --
1394 -- Description:
1395 -- Validates that ORG_INFORMATION1 for ORG_INFORMATION_CONTEXT 'CLASS' is
1396 -- present in HR_LOKUPS table and valid.
1397 --
1398 -- Pre-conditions:
1399 -- None.
1400 --
1401 -- In Arguments:
1402 -- p_org_information_context
1403 -- p_org_information1
1404 -- p_effective_date
1405 --
1406 -- Post Success:
1407 -- If ORG_INFORMATION1 is present and valid then
1408 -- normal processing continues
1409 --
1410 -- Post Failure:
1411 -- If ORG_INFORMATION1 is present and invalid then an application
1412 -- error will be raised and processing is terminated.
1413 --
1414 -- Developer/Implementation Notes:
1415 -- Duplicate validation exists on form, so any changes made here
1416 -- or on form must be dual-maintained.
1417 --
1418 -- Access Status:
1419 -- Internal Row Table Handler Use Only.
1420 --
1421 -- {End Of Comments}
1422 --
1423 PROCEDURE chk_cls_valid
1424 ( p_org_information_context IN hr_organization_information.org_information_context%TYPE,
1425 p_org_information1 IN hr_organization_information.org_information1%TYPE,
1426 p_effective_date IN DATE)
1427 IS
1428 l_proc VARCHAR2(72) := g_package||'chk_cls_valid';
1429 l_exists VARCHAR2(1) := 'N';
1430 --
1431 cursor csr_cls_valid IS
1432 SELECT 'Y'
1433 FROM hr_lookups
1434 WHERE lookup_type = 'ORG_CLASS'
1435 AND lookup_code = p_org_information1
1436 AND enabled_flag = 'Y'
1437 AND p_effective_date BETWEEN nvl(start_date_active,p_effective_date)
1438 AND nvl(end_date_active,p_effective_date);
1439 --
1440 BEGIN
1441 --
1442 hr_utility.set_location('Entering:'|| l_proc, 10);
1443 --
1444 --
1445 -- Check classification
1446 --
1447 IF p_org_information_context = 'CLASS' THEN
1448 IF p_org_information1 IS null THEN
1449 hr_utility.set_message(800, 'HR_52760_NULL_CLSF');
1450 hr_utility.raise_error;
1451 ELSE
1452 OPEN csr_cls_valid;
1453 FETCH csr_cls_valid INTO l_exists;
1454 --
1455 hr_utility.set_location(l_proc, 20);
1456 --
1457 IF csr_cls_valid%notfound THEN
1458 CLOSE csr_cls_valid;
1459 hr_utility.set_message(800, 'HR_52759_INV_CLSF');
1460 hr_utility.raise_error;
1461 ELSE
1462 CLOSE csr_cls_valid;
1463 END IF;
1464 END IF;
1465 END IF;
1466 --
1467 --
1468 hr_utility.set_location('Leaving:'||l_proc, 30);
1469 --
1470 END chk_cls_valid;
1471 --
1472 -- ----------------------------------------------------------------------------
1473 -- |-------------------------< chk_cls_unique >-------------------------------|
1474 -- ----------------------------------------------------------------------------
1475 --
1476 -- Description:
1477 -- Validates that classification of organization unit is not present in
1478 -- HR_ORGANIZATION_INFORMATION table for ORGANIZATION_ID.
1479 --
1480 -- Pre-conditions:
1481 -- None.
1482 --
1483 -- In Arguments:
1484 -- p_organization_id
1485 -- p_org_information1
1486 --
1487 -- Post Success:
1488 -- If classification is not present then
1489 -- normal processing continues
1490 --
1491 -- Post Failure:
1492 -- If classification is already present then an application
1493 -- error will be raised and processing is terminated.
1494 --
1495 -- Developer/Implementation Notes:
1496 -- Duplicate validation exists on form, so any changes made here
1497 -- or on form must be dual-maintained.
1498 --
1499 -- Access Status:
1500 -- Internal Row Table Handler Use Only.
1501 --
1502 -- {End Of Comments}
1503 --
1504 PROCEDURE chk_cls_unique
1505 ( p_organization_id IN hr_organization_information.organization_id%TYPE,
1506 p_org_information1 IN hr_organization_information.org_information1%TYPE)
1507 IS
1508 l_proc VARCHAR2(72) := g_package||'chk_cls_unique';
1509 l_exists VARCHAR2(1) := 'N';
1510 --
1511 BEGIN
1512 --
1513 hr_utility.set_location('Entering:'|| l_proc, 10);
1514 --
1515 --
1516 -- Check classification presence
1517 --
1518 BEGIN
1519 SELECT 'Y'
1520 INTO l_exists
1521 FROM sys.dual
1522 WHERE EXISTS
1523 (SELECT null
1524 FROM hr_organization_information
1525 WHERE organization_id = p_organization_id
1526 AND org_information_context = 'CLASS'
1527 AND org_information1 = p_org_information1);
1528 EXCEPTION
1529 WHEN NO_DATA_FOUND THEN null;
1530 END;
1531 --
1532 hr_utility.set_location(l_proc, 20);
1533 --
1534 IF l_exists = 'Y' THEN
1535 hr_utility.set_message(800, 'HR_52761_ORG_CLSF_EXISTS');
1536 hr_utility.raise_error;
1537 END IF;
1538 --
1539 --
1540 hr_utility.set_location('Leaving:'||l_proc, 30);
1541 --
1542 END chk_cls_unique;
1543 --
1544 --
1545 -- ----------------------------------------------------------------------------
1546 -- |-------------------------< chk_info_type_valid >--------------------------|
1547 -- ----------------------------------------------------------------------------
1548 --
1549 -- Description:
1550 -- Validates that info type is present in the list of info types
1551 -- for all current enabled classifications in
1552 -- HR_ORGANIZATION_INFORMATION table for ORGANIZATION_ID.
1553 --
1554 -- Pre-conditions:
1555 -- None.
1556 --
1557 -- In Arguments:
1558 -- p_organization_id
1559 -- p_org_information_context
1560 --
1561 -- Post Success:
1562 -- If info type is present in the list then
1563 -- normal processing continues
1564 --
1565 -- Post Failure:
1566 -- If info type is not present in the list then an application
1567 -- error will be raised and processing is terminated.
1568 --
1569 -- Developer/Implementation Notes:
1570 -- Duplicate validation exists on form, so any changes made here
1571 -- or on form must be dual-maintained.
1572 --
1573 -- Access Status:
1574 -- Internal Row Table Handler Use Only.
1575 --
1576 -- {End Of Comments}
1577 --
1578 PROCEDURE chk_info_type_valid
1579 ( p_organization_id IN hr_organization_information.organization_id%TYPE,
1580 p_org_information_context IN hr_organization_information.org_information_context%TYPE)
1581 IS
1582 l_proc VARCHAR2(72) := g_package||'chk_info_type_valid';
1583 l_exists VARCHAR2(1) := 'N';
1584 --
1585 BEGIN
1586 --
1587 hr_utility.set_location('Entering:'|| l_proc, 10);
1588 --
1589 --
1590 -- Check info type presence
1591 --
1592 -- Added nav method 'LOC' AT 27/9/01
1593 --
1594 BEGIN
1595 SELECT 'Y'
1596 INTO l_exists
1597 FROM sys.dual
1598 WHERE EXISTS
1599 (SELECT hoit.org_information_type
1600 FROM hr_org_information_types hoit
1601 WHERE hoit.org_information_type = p_org_information_context
1602 AND (hoit.navigation_method = 'GS' OR hoit.navigation_method = 'GM' OR hoit.navigation_method = 'LOC')
1603 AND EXISTS
1604 (SELECT null
1605 FROM hr_org_info_types_by_class hitbc
1606 ,hr_organization_information hoi
1607 WHERE hitbc.org_information_type = hoit.org_information_type
1608 AND hitbc.org_classification = hoi.org_information1
1609 AND hoi.org_information2 = 'Y'
1610 AND hoi.org_information_context = 'CLASS'
1611 AND hoi.organization_id = p_organization_id)
1612 );
1613 EXCEPTION
1614 WHEN NO_DATA_FOUND THEN null;
1615 END;
1616 -- VT maybe we still need it later modified and added to the
1617 -- select statement above
1618 --
1619 -- AND hoit.legislation_code =
1620 -- (SELECT pbg.legislation_code
1621 -- FROM per_business_groups pbg
1622 -- ,hr_all_organization_units haou
1623 -- WHERE haou.organization_id = p_organization_id
1624 -- AND haou.business_group_id = pbg.business_group_id)
1625 --
1626 hr_utility.set_location(l_proc, 20);
1627 --
1628 IF l_exists = 'N' THEN
1629 hr_utility.set_message(800, 'HR_289003_INV_INFO_TYPE');
1630 hr_utility.raise_error;
1631 END IF;
1632 --
1633 --
1634 hr_utility.set_location('Leaving:'||l_proc, 30);
1635 --
1636 END chk_info_type_valid;
1637 --
1638 --
1639 -- ----------------------------------------------------------------------------
1640 -- |-------------------------< chk_info_type_unique >-------------------------|
1641 -- ----------------------------------------------------------------------------
1642 --
1643 -- Description:
1644 -- Validates that info type is present in the
1645 -- HR_ORGANIZATION_INFORMATION table for ORGANIZATION_ID.
1646 --
1647 -- Pre-conditions:
1648 -- None.
1649 --
1650 -- In Arguments:
1651 -- p_organization_id
1652 -- p_org_information_context
1653 --
1654 -- Post Success:
1655 -- If info type is not present then
1656 -- normal processing continues
1657 --
1658 -- Post Failure:
1659 -- If info type is present in then an application
1660 -- error will be raised and processing is terminated.
1661 --
1662 -- Developer/Implementation Notes:
1663 -- Duplicate validation exists on form, so any changes made here
1664 -- or on form must be dual-maintained.
1665 --
1666 -- Access Status:
1667 -- Internal Row Table Handler Use Only.
1668 --
1669 -- {End Of Comments}
1670 --
1671 PROCEDURE chk_info_type_unique
1672 ( p_organization_id IN hr_organization_information.organization_id%TYPE,
1673 p_org_information_context IN hr_organization_information.org_information_context%TYPE)
1674 IS
1675 l_proc VARCHAR2(72) := g_package||'chk_info_type_unique';
1676 l_exists VARCHAR2(1) := 'N';
1677 --
1678 BEGIN
1679 --
1680 hr_utility.set_location('Entering:'|| l_proc, 10);
1681 --
1682 --
1683 -- Check info type presence
1684 --
1685 BEGIN
1686 SELECT 'Y'
1687 INTO l_exists
1688 FROM sys.dual
1689 WHERE EXISTS
1690 (SELECT null
1691 FROM hr_organization_information hoi
1692 ,hr_org_information_types hoit
1693 WHERE hoi.org_information_context = p_org_information_context
1694 AND hoi.organization_id = p_organization_id
1695 AND hoit.org_information_type = p_org_information_context
1696 AND hoit.navigation_method = 'GS');
1697 EXCEPTION
1698 WHEN NO_DATA_FOUND THEN null;
1699 END;
1700 --
1701 hr_utility.set_location(l_proc, 20);
1702 --
1703 IF l_exists = 'Y' THEN
1704 hr_utility.set_message(800, 'HR_289004_INFO_TYPE_EXISTS');
1705 hr_utility.raise_error;
1706 END IF;
1707 --
1708 --
1709 hr_utility.set_location('Leaving:'||l_proc, 30);
1710 --
1711 END chk_info_type_unique;
1712 --
1713 -- Bug 3456540 Start
1714 procedure chk_location(
1715 p_organization_id IN hr_organization_information.organization_id%TYPE
1716 ) is
1717 --
1718 cursor csr_location is
1719 select location_id
1720 from hr_all_organization_units
1721 where organization_id = p_organization_id;
1722 --
1723 l_location_id number;
1724 --
1725 begin
1726 --
1727 open csr_location;
1728 fetch csr_location into l_location_id;
1729 close csr_location;
1730 --
1731 if l_location_id is null then
1732 hr_utility.set_message(800, 'HR_6612_ORG_LEGAL_NO_LOCATION');
1733 hr_utility.raise_error;
1734 end if;
1735 --
1736 end chk_location;
1737 --
1738 --
1739 -- Bug 3456540 End
1740 -- Start of Bug No 2586522
1741 -- ----------------------------------------------------------------------------
1742 -- |---------------------------< check_state_tax_rules >----------------------|
1743 -- ----------------------------------------------------------------------------
1744 --
1745 -- Description:
1746 -- US specific validation to check that if the structure being updated is
1747 -- 'State Tax Rules' and the segment being updated is WC Carrier then
1748 -- check that a WC rate for this carrier is not being referenced by
1749 -- an assignment's 'WC Override Code' on the SCL 'GREs and other data'
1750 --
1751 -- Pre-conditions:
1752 -- When Org_Information_context = 'State Tax Rules'.
1753 --
1754 -- In Arguments:
1755 -- X_Org_Information_ID
1756 -- X_org_information1
1757 -- X_org_information8
1758 --
1759 -- Post Success:
1760 -- Normal processing continues
1761 --
1762 -- Post Failure:
1763 -- Error will be raised and processing is terminated.
1764 --
1765 --
1766 -- Access Status:
1767 -- Internal Row Table Handler Use Only.
1768 --
1769 -- {End Of Comments}
1770 --
1771 Procedure check_state_tax_rules
1772 (X_Org_Information_ID in hr_organization_information.organization_id%TYPE,
1773 X_org_information1 in hr_organization_information.org_information1%TYPE,
1774 X_org_information8 in hr_organization_information.org_information8%TYPE )
1775 as
1776 --
1777 -- declare local variables
1778 --
1779 l_dummy VARCHAR2(1);
1780 l_state_code VARCHAR2(2);
1781 l_carrier_id VARCHAR2(17);
1782 l_proc VARCHAR2(72) := g_package||'check_state_tax_rules';
1783
1784 --
1785 -- declare cursors
1786 --
1787
1788 CURSOR get_orig_values IS
1789 select
1790 org_information1,
1791 org_information8
1792 from
1793 hr_organization_information
1794 where
1795 org_information_id = X_org_information_id;
1796 --
1797
1798 CURSOR check_override IS
1799 SELECT /*+ STAR_TRANSFORMATION */
1800 'x'
1801 FROM
1802 pay_wc_rates wcr,
1803 pay_wc_funds wcf
1804 WHERE
1805 wcf.carrier_id = l_carrier_id AND
1806 wcf.state_code = l_state_code AND
1807 wcr.fund_id = wcf.fund_id
1808 AND EXISTS
1809 ( SELECT 'code referenced in override'
1810 FROM per_assignments_f a,
1811 fnd_id_flex_structures_vl ifs,
1812 hr_soft_coding_keyflex sck
1813 WHERE sck.segment1 = to_char(X_org_information_id) -- #1683897
1814 AND segment8 = to_char(wcr.wc_code)
1815 AND ifs.id_flex_structure_name = 'GREs and other data'
1816 AND sck.id_flex_num = ifs.id_flex_num
1817 AND a.assignment_type = 'E'
1818 AND a.soft_coding_keyflex_id = sck.soft_coding_keyflex_id );
1819
1820 Begin
1821 hr_utility.set_location('Entering:'||l_proc, 10);
1822
1823 --
1824 -- get original values
1825 --
1826 OPEN get_orig_values;
1827 FETCH get_orig_values into l_state_code, l_carrier_id;
1828 CLOSE get_orig_values;
1829 --
1830 -- check if values have changed
1831 --
1832 IF ((l_state_code <> X_org_information1) OR
1833 (NVL(l_carrier_id, X_org_information8) <> X_org_information8) OR
1834 X_org_information8 IS NULL)
1835 THEN
1836 hr_utility.set_location('Entering:'||l_proc, 20);
1837 OPEN check_override;
1838 FETCH check_override into l_dummy;
1839 IF check_override%FOUND
1840 THEN
1841 hr_utility.set_location(l_proc, 30);
1842 hr_utility.set_message(800,'HR_51039_ORG_WC_OVRRD_RATE_REF');
1843 hr_utility.raise_error;
1844 END IF;
1845 CLOSE check_override;
1846 hr_utility.set_location(l_proc, 40);
1847 END IF;
1848 hr_utility.set_location('Leaving :'||l_proc, 50);
1849 END check_state_tax_rules;
1850 --
1851 -- End of bug No 2586522
1852 --
1853 --
1854 -- Start of fix for bug 3679256
1855 --
1856 -- ----------------------------------------------------------------------------
1857 -- |----------------------------< Chk_Bus_grp >------------------------------|
1858 -- ----------------------------------------------------------------------------
1859 --
1860 -- Description:
1861 -- Retrives Business group id based on Org id passed and sets Context values
1862 -- 'Legislation code' and 'Security profile id', which is further used by
1863 -- by Lookup 'HR_LOOKUP'.
1864 --
1865 -- Pre-conditions:
1866 -- Valid organization id is passed.
1867 --
1868 -- In Arguments:
1869 -- p_organization_id
1870 --
1871 --
1872 -- Post Success:
1873 -- Set Context values for LEG CODE and Security Group Id.
1874 --
1875 --
1876 --
1877 -- Access Status:
1878 -- Internal Row Table Handler Use Only.
1879 --
1880 -- {End Of Comments}
1881 --
1882 Procedure chk_bus_grp
1883 (p_organization_id in number
1884 )is
1885
1886
1887 l_proc varchar2(72) := g_package||'chk_business_grp';
1888 l_bg_id number ;
1889 -- Fetch BG Id from Org id.
1890 cursor C_BG is
1891 select business_group_id
1892 from hr_all_organization_units
1893 where organization_id = p_organization_id;
1894
1895 Begin
1896 Open c_bg;
1897 Fetch c_bg into l_bg_id;
1898 hr_api.validate_bus_grp_id(l_bg_id); -- Validate Bus Grp
1899 Close c_bg;
1900
1901
1902 End chk_bus_grp;
1903 --
1904 -- End of fix for bug 3679256
1905 --
1906 -- ----------------------------------------------------------------------------
1907 -- |---------------------------< insert_validate >----------------------------|
1908 -- ----------------------------------------------------------------------------
1909 Procedure insert_validate
1910 (p_effective_date in date
1911 ,p_rec in hr_ori_shd.g_rec_type
1912 ) is
1913 --
1914 l_proc varchar2(72) := g_package||'insert_validate';
1915 l_bg_id number;
1916 --
1917 Begin
1918 hr_utility.set_location('Entering:'||l_proc, 5);
1919 --
1920 -- Call all supporting business operations
1921
1922 hr_api.mandatory_arg_error
1923 (p_api_name => l_proc
1924 ,p_argument => 'ORGANIZATION_ID'
1925 ,p_argument_value => p_rec.organization_id
1926 );
1927 --
1928 --
1929 hr_api.mandatory_arg_error
1930 (p_api_name => l_proc
1931 ,p_argument => 'ORG_INFORMATION_CONTEXT'
1932 ,p_argument_value => p_rec.org_information_context
1933 );
1934 --
1935 -- Validate organization_id
1936 --=========================
1937 chk_organization_id(
1938 p_organization_id => p_rec.organization_id,
1939 p_effective_date => p_effective_date);
1940
1941
1942 -- Check unique business group name
1943 -- ================================
1944 chk_name(
1945 p_org_information1 => p_rec.org_information1,
1946 p_org_information_context => p_rec.org_information_context,
1947 p_organization_id => p_rec.organization_id,
1948 p_org_information2 => p_rec.org_information2);
1949 --
1950 IF p_rec.org_information_context = 'CLASS' THEN
1951
1952 --
1953 -- Start of fix for bug 3679256
1954 --
1955 -- Validate Business Group.
1956 --=========================
1957 If p_rec.org_information1 <> 'HR_BG' then
1958 chk_bus_grp(
1959 p_organization_id => p_rec.organization_id);
1960 End if;
1961 --
1962 -- End of fix for bug 3679256
1963 --
1964
1965 -- Validate classification
1966 --=========================
1967 chk_cls_valid(
1968 p_org_information_context => p_rec.org_information_context,
1969 p_org_information1 => p_rec.org_information1,
1970 p_effective_date => p_effective_date);
1971 --
1972 -- Validate org classification unique
1973 --=========================
1974 chk_cls_unique(
1975 p_organization_id => p_rec.organization_id,
1976 p_org_information1 => p_rec.org_information1);
1977
1978 -- Bug 3456540 Start
1979 -- Validation for legal entity.
1980 -- Check if the organization has a location attached to it
1981 if p_rec.org_information1 = 'HR_LEGAL' and p_rec.org_information2 = 'Y' then
1982 chk_location( p_organization_id => p_rec.organization_id );
1983 end if;
1984 -- Bug 3456540 End
1985 --
1986 ELSE
1987 -- Validate info type
1988 --=========================
1989 chk_info_type_valid(
1990 p_organization_id => p_rec.organization_id,
1991 p_org_information_context => p_rec.org_information_context);
1992 --
1993 -- Validate info type unique
1994 --=========================
1995 chk_info_type_unique(
1996 p_organization_id => p_rec.organization_id,
1997 p_org_information_context => p_rec.org_information_context);
1998 --
1999 END IF;
2000 --
2001 if p_rec.org_information_context = 'Organization Name Alias' then
2002 --
2003 chk_cost_center_start_date
2004 (p_org_information_context => p_rec.org_information_context,
2005 p_org_information2 => p_rec.org_information2,
2006 p_org_information3 => p_rec.org_information3,
2007 p_org_information4 => p_rec.org_information4);
2008 --
2009 chk_cost_center_start_end_date
2010 (p_org_information_context => p_rec.org_information_context,
2011 p_org_information2 => p_rec.org_information2,
2012 p_org_information3 => p_rec.org_information3,
2013 p_org_information4 => p_rec.org_information4);
2014 --
2015 chk_cost_center_valid
2016 (p_organization_id => p_rec.organization_id,
2017 p_org_information_context => p_rec.org_information_context);
2018 --
2019 chk_cost_center_manager_valid
2020 (p_org_information_context => p_rec.org_information_context,
2021 p_org_information2 => p_rec.org_information2,
2022 p_effective_date => p_effective_date);
2023 --
2024 chk_cost_center_man_overlap
2025 (p_organization_id => p_rec.organization_id,
2026 p_org_information_id => p_rec.org_information_id,
2027 p_org_information_context => p_rec.org_information_context,
2028 p_org_information3 => p_rec.org_information3,
2029 p_org_information4 => p_rec.org_information4);
2030 --
2031 end if;
2032 --
2033 --
2034 -- start of fix for Bug #2586522
2035 --
2036 if p_rec.org_information_context = 'FR_ESTAB_INFO' then
2037 HR_ORG_INFORMATION_PKG.Validate_SIRET(X_SIRET => p_rec.org_information2);
2038 elsif p_rec.org_information_context = 'FR_ESTAB_PREV_INFO' then
2039 HR_ORG_INFORMATION_PKG.Validate_SIRET(X_SIRET => p_rec.org_information1);
2040 end if;
2041
2042 if p_rec.org_information_context = 'FR_COMP_INFO' then
2043 HR_ORG_INFORMATION_PKG.Validate_SIREN(X_SIREN => p_rec.org_information1);
2044 elsif p_rec.org_information_context = 'FR_COMP_PREV_INFO' then
2045 HR_ORG_INFORMATION_PKG.Validate_SIREN(X_SIREN => p_rec.org_information1);
2046 end if;
2047
2048 HR_ORG_INFORMATION_PKG.validate_business_group_name
2049 (p_organization_id => p_rec.Organization_Id
2050 ,p_org_information_context => p_rec.Org_Information_Context
2051 ,p_org_information1 => p_rec.Org_Information1
2052 ,p_org_information2 => p_rec.Org_Information2
2053 );
2054
2055 --
2056 -- End of fix for Bug #2586522
2057 --
2058 --
2059 -- Added fix for bug #4745845. Do not validate ddf for not usable OU.
2060 if p_rec.org_information_context = 'Operating Unit Information' and
2061 p_rec.org_information6 = 'N' then
2062 null;
2063 else
2064 hr_ori_bus.chk_ddf(p_rec);
2065 end if;
2066 --
2067 -- Descriptive Flexfield is context dependent HR_ORGANIZATION_INFORMATION
2068 --
2069 --fix for bug 6376908.
2070 --Added the if condition before calling the procedure chk_df.
2071 --
2072 if(p_rec.org_information_context<>'CLASS') then
2073 hr_ori_bus.chk_df(p_rec);
2074 end if;
2075 --
2076 hr_utility.set_location(' Leaving:'||l_proc, 10);
2077 End insert_validate;
2078 --
2079 -- ----------------------------------------------------------------------------
2080 -- |---------------------------< update_validate >----------------------------|
2081 -- ----------------------------------------------------------------------------
2082 Procedure update_validate
2083 (p_effective_date in date
2084 ,p_rec in hr_ori_shd.g_rec_type
2085 ) is
2086 --
2087 cursor c_date (p_id number) is
2088 select min(effective_start_date), max(effective_end_date)
2089 from per_all_people_f
2090 where current_employee_flag='Y'
2091 and person_id=p_id;
2092 l_proc varchar2(72) := g_package||'update_validate';
2093 l_startdate date;
2094 l_enddate date;
2095 --
2096 Begin
2097 hr_utility.set_location('Entering:'||l_proc, 5);
2098 --
2099 -- Call all supporting business operations
2100 --
2101 --
2102 --
2103 hr_api.mandatory_arg_error
2104 (p_api_name => l_proc
2105 ,p_argument => 'ORG_INFORMATION_CONTEXT'
2106 ,p_argument_value => p_rec.org_information_context
2107 );
2108 --
2109 --
2110 -- EDIT_HERE: As this table does not have a mandatory business_group_id
2111 -- column, ensure client_info is populated by calling a suitable
2112 -- ???_???_bus.set_security_group_id procedure, or add one of the following
2113 -- comments:
2114 -- "-- No business group context. HR_STANDARD_LOOKUPS used for validation."
2115 -- "-- CLIENT_INFO not set. No lookup validation or joins to HR_LOOKUPS."
2116 --
2117 -- Check unique business group name
2118 -- ================================
2119 chk_name(
2120 p_org_information1 => p_rec.org_information1,
2121 p_org_information_context => p_rec.org_information_context,
2122 p_organization_id => p_rec.organization_id,
2123 p_org_information2 => p_rec.org_information2
2124 );
2125 --
2126 chk_non_updateable_args
2127 (p_effective_date => p_effective_date
2128 ,p_rec => p_rec
2129 );
2130 --
2131 -- 3456540 Start
2132 if p_rec.org_information_context = 'CLASS' then
2133 -- Validation for legal entity.
2134 -- Check if the organization has a location attached to it
2135 if p_rec.org_information1 = 'HR_LEGAL' and p_rec.org_information2 = 'Y' then
2136 chk_location( p_organization_id => p_rec.organization_id );
2137 end if;
2138 end if;
2139 -- 3456540 end
2140 if p_rec.org_information_context = 'Organization Name Alias' then
2141 --
2142 chk_cost_center_start_date
2143 (p_org_information_context => p_rec.org_information_context,
2144 p_org_information2 => p_rec.org_information2,
2145 p_org_information3 => p_rec.org_information3,
2146 p_org_information4 => p_rec.org_information4);
2147 --
2148 chk_cost_center_start_end_date
2149 (p_org_information_context => p_rec.org_information_context,
2150 p_org_information2 => p_rec.org_information2,
2151 p_org_information3 => p_rec.org_information3,
2152 p_org_information4 => p_rec.org_information4);
2153 --
2154 chk_cost_center_valid
2155 (p_organization_id => p_rec.organization_id,
2156 p_org_information_context => p_rec.org_information_context);
2157 --
2158 chk_cost_center_manager_valid
2159 (p_org_information_context => p_rec.org_information_context,
2160 p_org_information2 => p_rec.org_information2,
2161 p_effective_date => p_effective_date);
2162 --
2163 chk_cost_center_man_overlap
2164 (p_organization_id => p_rec.organization_id,
2165 p_org_information_id => p_rec.org_information_id,
2166 p_org_information_context => p_rec.org_information_context,
2167 p_org_information3 => p_rec.org_information3,
2168 p_org_information4 => p_rec.org_information4);
2169 --
2170 end if;
2171 --
2172 --
2173 -- start of fix for Bug #2586522
2174 --
2175 IF (p_rec.org_information_context = 'State Tax Rules')
2176 THEN
2177 check_state_tax_rules
2178 (X_Org_Information_ID => p_rec.org_information_id,
2179 X_Org_information1 => p_rec.org_information1,
2180 X_Org_information8 => p_rec.org_information8);
2181 END IF;
2182
2183 if p_rec.org_information_context = 'FR_ESTAB_INFO' then
2184 HR_ORG_INFORMATION_PKG.Validate_SIRET(X_SIRET => p_rec.org_information2);
2185 elsif p_rec.org_information_context = 'FR_ESTAB_PREV_INFO' then
2186 HR_ORG_INFORMATION_PKG.Validate_SIRET(X_SIRET => p_rec.org_information1);
2187 end if;
2188
2189 if p_rec.org_information_context = 'FR_COMP_INFO' then
2190 HR_ORG_INFORMATION_PKG.Validate_SIREN(X_SIREN => p_rec.org_information1);
2191 elsif p_rec.org_information_context = 'FR_COMP_PREV_INFO' then
2192 HR_ORG_INFORMATION_PKG.Validate_SIREN(X_SIREN => p_rec.org_information1);
2193 end if;
2194
2195 HR_ORG_INFORMATION_PKG.validate_business_group_name
2196 (p_organization_id => p_rec.Organization_Id
2197 ,p_org_information_context => p_rec.Org_Information_Context
2198 ,p_org_information1 => p_rec.Org_Information1
2199 ,p_org_information2 => p_rec.Org_Information2
2200 );
2201 /* To check uniqueness of identifier segment in extra information type
2202 of 'grouping unit information' for FR Public Section'*/
2203 /*
2204 if p_rec.Org_Information_Context = 'FR_PQH_GROUPING_UNIT_INFO' then
2205 pqh_fr_org_validate_pkg.check_unique_identifier
2206 ( p_org_information_context => p_rec.Org_Information_Context,
2207 p_org_information_id => NULL,
2208 p_org_information6 => p_rec.Org_Information6);
2209 end if;
2210 */
2211 --
2212 -- Added by FS
2213 -- End of fix for Bug #2586522
2214 --
2215
2216 --Added for 3034234.Start of fix.
2217 IF (p_rec.org_information_context = 'Organization Name Alias') then
2218 hr_utility.set_location('The person:'|| p_rec.org_information2,333);
2219 open c_date(to_number(p_rec.org_information2));
2220 fetch c_date into l_startdate,l_enddate;
2221 hr_utility.set_location('Start:'||l_startdate||' Enddate :'||l_enddate,555);
2222 if c_date%notfound then
2223 close c_date;
2224 if(fnd_date.canonical_to_date(p_rec.org_information4) > fnd_date.date_to_canonical(l_enddate)) then
2225
2226 hr_ori_bus.chk_ddf(p_rec);
2227 --
2228 end if;
2229 close c_date;
2230 end if;
2231
2232 else
2233
2234 hr_ori_bus.chk_ddf(p_rec);
2235 --
2236 end if;
2237 -- End of fix for 3034234
2238 --
2239 hr_ori_bus.chk_df(p_rec);
2240 hr_utility.set_location(' Leaving:'||l_proc, 10);
2241 --
2242 End update_validate;
2243 --
2244 -- ----------------------------------------------------------------------------
2245 -- |---------------------------< delete_validate >----------------------------|
2246 -- ----------------------------------------------------------------------------
2247 Procedure delete_validate
2248 (p_rec in hr_ori_shd.g_rec_type
2249 ) is
2250 --
2251 l_proc varchar2(72) := g_package||'delete_validate';
2252 --
2253 Begin
2254 hr_utility.set_location('Entering:'||l_proc, 5);
2255 --
2256 -- Call all supporting business operations
2257 --
2258 hr_utility.set_location(' Leaving:'||l_proc, 10);
2259 End delete_validate;
2260 --
2261 end hr_ori_bus;
[Home] [Help]