[Home] [Help]
PACKAGE BODY: APPS.IRC_ITA_BUS
Source
1 Package Body irc_ita_bus as
2 /* $Header: iritarhi.pkb 120.1 2005/10/04 06:26 kthavran noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' irc_ita_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_template_association_id number default null;
15 --
16 -- ---------------------------------------------------------------------------
17 -- |----------------------< set_security_group_id >--------------------------|
18 -- ---------------------------------------------------------------------------
19 --
20 Procedure set_security_group_id
21 (p_template_association_id in number
22 ,p_associated_column1 in varchar2 default null
23 ) is
24 --
25 -- Declare cursor
26 --
27 -- EDIT_HERE In the following cursor statement add join(s) between
28 -- irc_template_associations and PER_BUSINESS_GROUPS_PERF
29 -- so that the security_group_id for
30 -- the current business group context can be derived.
31 -- Remove this comment when the edit has been completed.
32 cursor csr_sec_grp is
33 select pbg.security_group_id,
34 pbg.legislation_code
35 from per_business_groups_perf pbg
36 , irc_template_associations ita
37 -- , EDIT_HERE table_name(s) 333
38 where ita.template_association_id = p_template_association_id;
39 -- and pbg.business_group_id = EDIT_HERE 333.business_group_id;
40 --
41 -- Declare local variables
42 --
43 l_security_group_id number;
44 l_proc varchar2(72) := g_package||'set_security_group_id';
45 l_legislation_code varchar2(150);
46 --
47 begin
48 --
49 hr_utility.set_location('Entering:'|| l_proc, 10);
50 --
51 -- Ensure that all the mandatory parameter are not null
52 --
53 hr_api.mandatory_arg_error
54 (p_api_name => l_proc
55 ,p_argument => 'template_association_id'
56 ,p_argument_value => p_template_association_id
57 );
58 --
59 open csr_sec_grp;
60 fetch csr_sec_grp into l_security_group_id
61 , l_legislation_code;
62 --
63 if csr_sec_grp%notfound then
64 --
65 close csr_sec_grp;
66 --
67 -- The primary key is invalid therefore we must error
68 --
69 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
70 hr_multi_message.add
71 (p_associated_column1
72 => nvl(p_associated_column1,'TEMPLATE_ASSOCIATION_ID')
73 );
74 --
75 else
76 close csr_sec_grp;
77 --
78 -- Set the security_group_id in CLIENT_INFO
79 --
80 hr_api.set_security_group_id
81 (p_security_group_id => l_security_group_id
82 );
83 --
84 -- Set the sessions legislation context in HR_SESSION_DATA
85 --
86 hr_api.set_legislation_context(l_legislation_code);
87 end if;
88 --
89 hr_utility.set_location(' Leaving:'|| l_proc, 20);
90 --
91 end set_security_group_id;
92 --
93 -- ---------------------------------------------------------------------------
94 -- |---------------------< return_legislation_code >-------------------------|
95 -- ---------------------------------------------------------------------------
96 --
97 Function return_legislation_code
98 (p_template_association_id in number
99 )
100 Return Varchar2 Is
101 --
102 -- Declare cursor
103 --
104 -- EDIT_HERE In the following cursor statement add join(s) between
105 -- irc_template_associations and PER_BUSINESS_GROUPS_PERF
106 -- so that the legislation_code for
107 -- the current business group context can be derived.
108 -- Remove this comment when the edit has been completed.
109 cursor csr_leg_code is
110 select pbg.legislation_code
111 from per_business_groups_perf pbg
112 , irc_template_associations ita
113 -- , EDIT_HERE table_name(s) 333
114 where ita.template_association_id = p_template_association_id;
118 --
115 -- and pbg.business_group_id = EDIT_HERE 333.business_group_id;
116 --
117 -- Declare local variables
119 l_legislation_code varchar2(150);
120 l_proc varchar2(72) := g_package||'return_legislation_code';
121 --
122 Begin
123 --
124 hr_utility.set_location('Entering:'|| l_proc, 10);
125 --
126 -- Ensure that all the mandatory parameter are not null
127 --
128 hr_api.mandatory_arg_error
129 (p_api_name => l_proc
130 ,p_argument => 'template_association_id'
131 ,p_argument_value => p_template_association_id
132 );
133 --
134 if ( nvl(irc_ita_bus.g_template_association_id, hr_api.g_number)
135 = p_template_association_id) then
136 --
137 -- The legislation code has already been found with a previous
138 -- call to this function. Just return the value in the global
139 -- variable.
140 --
141 l_legislation_code := irc_ita_bus.g_legislation_code;
142 hr_utility.set_location(l_proc, 20);
143 else
144 --
145 -- The ID is different to the last call to this function
146 -- or this is the first call to this function.
147 --
148 open csr_leg_code;
149 fetch csr_leg_code into l_legislation_code;
150 --
151 if csr_leg_code%notfound then
152 --
153 -- The primary key is invalid therefore we must error
154 --
155 close csr_leg_code;
156 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
157 fnd_message.raise_error;
158 end if;
159 hr_utility.set_location(l_proc,30);
160 --
161 -- Set the global variables so the values are
162 -- available for the next call to this function.
163 --
164 close csr_leg_code;
165 irc_ita_bus.g_template_association_id := p_template_association_id;
166 irc_ita_bus.g_legislation_code := l_legislation_code;
167 end if;
168 hr_utility.set_location(' Leaving:'|| l_proc, 40);
169 return l_legislation_code;
170 end return_legislation_code;
171 --
172 -- ----------------------------------------------------------------------------
173 -- |-----------------------< chk_non_updateable_args >------------------------|
174 -- ----------------------------------------------------------------------------
175 -- {Start Of Comments}
176 --
177 -- Description:
178 -- This procedure is used to ensure that non updateable attributes have
179 -- not been updated. If an attribute has been updated an error is generated.
180 --
181 -- Pre Conditions:
182 -- g_old_rec has been populated with details of the values currently in
183 -- the database.
184 --
185 -- In Arguments:
186 -- p_rec has been populated with the updated values the user would like the
187 -- record set to.
188 --
189 -- Post Success:
190 -- Processing continues if all the non updateable attributes have not
191 -- changed.
192 --
193 -- Post Failure:
194 -- An application error is raised if any of the non updatable attributes
195 -- have been altered.
196 --
197 -- {End Of Comments}
198 -- ----------------------------------------------------------------------------
199 Procedure chk_non_updateable_args
200 (p_rec in irc_ita_shd.g_rec_type
201 ) IS
202 --
203 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
204 l_error exception;
205 l_argument varchar2(30);
206
207 --
208 Begin
209 --
210 -- Only proceed with the validation if a row exists for the current
211 -- record in the HR Schema.
212 --
213 IF NOT irc_ita_shd.api_updating
214 (p_template_association_id => p_rec.template_association_id
215 ,p_object_version_number => p_rec.object_version_number
216 ) THEN
217 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
218 fnd_message.set_token('PROCEDURE ', l_proc);
219 fnd_message.set_token('STEP ', '5');
220 fnd_message.raise_error;
221 END IF;
222 --
223 --
224 if irc_ita_shd.g_old_rec.template_association_id <> p_rec.template_association_id then
225 hr_api.argument_changed_error
226 (p_api_name => l_proc
227 ,p_argument => 'template_association_id'
228 ,p_base_table => irc_ita_shd.g_tab_nam
229 );
230 end if;
231 --
232 exception
233 when l_error then
234 hr_api.argument_changed_error
235 (p_api_name => l_proc
236 ,p_argument => l_argument);
237 when others then
238 raise;
239 End chk_non_updateable_args;
240 --
241 -- ----------------------------------------------------------------------------
242 -- |-------------------------------< chk_template_id >------------------------|
243 -- ----------------------------------------------------------------------------
244 -- {Start Of Comments}
245 --
246 -- Description:
247 -- This procedure ensures a valid template id
248 -- Pre Conditions:
249 -- g_old_rec has been populated with details of the values currently in
250 -- the database.
251 --
252 -- In Arguments:
253 -- p_template_association_id
254 -- p_template_id
255 -- p_object_version_number
256 -- Post Success:
257 -- Processing continues if template id is not null and unique
258 --
259 -- Post Failure:
260 -- An application error is raised if template id is null or exists already
261 --
262 -- {End Of Comments}
263 -- ----------------------------------------------------------------------------
264 Procedure chk_template_id
265 (p_template_association_id in irc_template_associations.template_association_id%TYPE
266 ,p_template_id in irc_template_associations.template_id%TYPE
267 ,p_object_version_number in irc_template_associations.object_version_number%TYPE
268 ) IS
269 --
270 l_proc varchar2(72) := g_package || 'chk_template_id';
271 l_template_id varchar2(1);
272 l_api_updating boolean;
273 --
274 cursor csr_template is
275 select null
276 from xdo_templates_b
277 where template_id = p_template_id;
278 --
279 Begin
280 hr_utility.set_location('Entering:'||l_proc,10);
281 --
282 l_api_updating := irc_ita_shd.api_updating
283 (p_template_association_id => p_template_association_id
284 ,p_object_version_number => p_object_version_number);
285 hr_utility.set_location(l_proc,20);
286 if ((l_api_updating
287 and
288 nvl(irc_ita_shd.g_old_rec.template_id,hr_api.g_number) <>
289 nvl(p_template_id, hr_api.g_number))
290 or
291 (NOT l_api_updating)) then
292 --
293 if(p_template_id is not null)
294 then
295 open csr_template;
296 fetch csr_template into l_template_id;
297 hr_utility.set_location(l_proc,30);
298 if(csr_template%notfound)
299 then
300 close csr_template;
301 fnd_message.set_name('PER','IRC_412326_OFFER_INV_TEMPLT_ID');
302 hr_multi_message.add
303 (p_associated_column1 => 'IRC_TEMPLATE_ASSOCIATIONS.TEMPLATE_ID'
304 );
305 else
306 close csr_template;
307 end if;
308 else
309 fnd_message.set_name('PER','IRC_412327_OFFER_NULL_TMPLT_ID');
310 hr_multi_message.add
311 (p_associated_column1 => 'IRC_TEMPLATE_ASSOCIATIONS.TEMPLATE_ID'
312 );
313
314 end if;
315 end if;
316 --
317 hr_utility.set_location(' Leaving:'||l_proc,40);
318 --
319 exception
320 when app_exception.application_exception then
321 if hr_multi_message.exception_add
322 (p_associated_column1 => 'IRC_TEMPLATE_ASSOCIATIONS.TEMPLATE_ID'
323 ) then
324 hr_utility.set_location(' Leaving:'|| l_proc, 50);
325 raise;
326 end if;
327 hr_utility.set_location(' Leaving:'|| l_proc, 60);
328 End chk_template_id;
329 --
330 -- ---------------------------------------------------------------------------
331 -- |---------------------------< chk_organization_id >--------------------------|
332 -- ---------------------------------------------------------------------------
333 --
334 -- Description:
335 -- - Validates that a organization id exists in table hr_all_organization_units.
336 --
337 -- Pre-conditions:
338 -- None.
339 --
340 -- In Arguments:
341 -- p_organization_id
342 --
343 -- Post Success:
344 -- If a row does exist in hr_all_organization_units for the given organization id then
345 -- processing continues.
346 --
347 -- Post Failure:
348 -- If a row does not exist in hr_all_organization_units for the given organization id then
349 -- an application error will be raised and processing is terminated.
350 --
351 -- Access Status:
352 -- Internal Table Handler Use Only.
353 --
354 -- {End Of Comments}
355 -- ----------------------------------------------------------------------------
356 procedure chk_organization_id
357 (p_organization_id in irc_template_associations.organization_id%TYPE
358 )
359 is
360 --
361 l_proc varchar2(72) := g_package||'chk_organization_id';
362 l_org varchar2(1);
363 cursor csr_org is
364 select null
365 from hr_all_organization_units haou
366 where haou.organization_id = p_organization_id;
367 --
368 --
369 begin
370 hr_utility.set_location('Entering:'|| l_proc, 10);
371 --
372 if hr_multi_message.no_exclusive_error
373 (p_check_column1 => 'IRC_TEMPLATE_ASSOCIATIONS.TEMPLATE_ID'
374 ) then
375 --
376
377 if ((irc_ita_shd.g_old_rec.template_association_id is null and p_organization_id is not null)
378 or (irc_ita_shd.g_old_rec.template_association_id is not null
379 and nvl(irc_ita_shd.g_old_rec.organization_id, hr_api.g_number)
380 <> nvl(p_organization_id, hr_api.g_number))) then
381 --
382 hr_utility.set_location(l_proc, 20);
383 --
384 -- Check that the organization ID is linked to a
385 -- valid organization on HR_ALL_ORGANIZATON_UNITS
386 --
387 open csr_org;
388 fetch csr_org into l_org;
389 if (csr_org%notfound)
390 then
391 close csr_org;
392 fnd_message.set_name('PER','IRC_412091_ORG_NOT_EXIST');
393 fnd_message.raise_error;
394 end if;
395 close csr_org;
396 --
397 hr_utility.set_location(l_proc, 30);
398 --
399 end if;
400 end if; -- no exclusive error
401 --
402 hr_utility.set_location(' Leaving:'|| l_proc, 40);
403 --
404 exception
405 when app_exception.application_exception then
406 if hr_multi_message.exception_add
407 (p_associated_column1 => 'IRC_TEMPLATE_ASSOCIATIONS.ORGANIZATION_ID'
408 ) then
409 hr_utility.set_location(' Leaving:'|| l_proc, 50);
410 raise;
411 end if;
412 hr_utility.set_location(' Leaving:'|| l_proc, 60);
413 end chk_organization_id;
414 --
415 -- ---------------------------------------------------------------------------
416 -- |-----------------------------< chk_job_id >-----------------------------|
417 -- ---------------------------------------------------------------------------
418 --
419 -- Description:
420 -- - Validates that a job id exists in table per_jobs.
421 --
422 -- Pre-conditions:
423 -- None.
424 --
425 -- In Arguments:
426 -- p_job_id
427 --
428 -- Post Success:
429 -- If a row does exist in per_jobs for the given job id then
430 -- processing continues.
431 --
432 -- Post Failure:
433 -- If a row does not exist in per_jobs for the given job id then
434 -- an application error will be raised and processing is terminated.
435 --
436 -- Access Status:
437 -- Internal Table Handler Use Only.
438 --
439 -- {End Of Comments}
440 -- ----------------------------------------------------------------------------
441 procedure chk_job_id
442 (p_job_id in irc_template_associations.job_id%TYPE
443 )
444 is
445 --
446 l_proc varchar2(72) := g_package||'chk_job_id';
447 l_job varchar2(1);
448 cursor csr_job is
449 select null
450 from per_jobs pj
451 where pj.job_id = p_job_id;
452 --
453 begin
454 hr_utility.set_location('Entering:'|| l_proc, 10);
455 --
456 if hr_multi_message.no_exclusive_error
457 (p_check_column1 => 'IRC_TEMPLATE_ASSOCIATIONS.TEMPLATE_ID'
458 ) then
459 --
460
461 if ((irc_ita_shd.g_old_rec.template_association_id is null and p_job_id is not null)
462 or (irc_ita_shd.g_old_rec.template_association_id is not null
463 and nvl(irc_ita_shd.g_old_rec.job_id, hr_api.g_number)
464 <> nvl(p_job_id, hr_api.g_number))) then
465 --
466 hr_utility.set_location(l_proc, 20);
467 --
468 -- Check that the job ID is linked to a
469 -- valid job on per_jobs
470 --
471 open csr_job;
472 fetch csr_job into l_job;
473 --
474 if (csr_job%notfound) then
475 --
476 close csr_job;
477 fnd_message.set_name('PER','IRC_412037_RTM_INV_JOB_ID');
478 fnd_message.raise_error;
479 --
480 end if;
481 --
482 close csr_job;
483 hr_utility.set_location(l_proc, 30);
484 --
485 end if;
486 end if; -- no exclusive error
487 --
488 hr_utility.set_location(' Leaving:'|| l_proc, 40);
489 --
490 exception
491 when app_exception.application_exception then
492 if hr_multi_message.exception_add
493 (p_associated_column1 => 'IRC_TEMPLATE_ASSOCIATIONS.JOB_ID'
494 ) then
495 hr_utility.set_location(' Leaving:'|| l_proc, 50);
496 raise;
497 end if;
498 hr_utility.set_location(' Leaving:'|| l_proc, 60);
499 end chk_job_id;
500 --
501 -- ---------------------------------------------------------------------------
502 -- |---------------------------< chk_position_id >--------------------------|
503 -- ---------------------------------------------------------------------------
504 --
505 -- Description:
506 -- - Validates that a position id exists in table hr_all_positions_f.
507 --
508 -- Pre-conditions:
509 -- None.
510 --
511 -- In Arguments:
512 -- p_position_id
513 --
514 -- Post Success:
515 -- If a row does exist in hr_all_positions_f for the given position id then
516 -- processing continues.
517 --
518 -- Post Failure:
519 -- If a row does not exist in hr_all_positions_f for the given position id then
520 -- an application error will be raised and processing is terminated.
521 --
522 -- Access Status:
523 -- Internal Table Handler Use Only.
524 --
525 -- {End Of Comments}
526 -- ----------------------------------------------------------------------------
527 procedure chk_position_id
528 (p_position_id in irc_template_associations.position_id%TYPE
529 )
530 is
531 --
532 l_proc varchar2(72) := g_package||'chk_position_id';
533 l_pos varchar2(1);
534 cursor csr_pos is
535 select null
536 from hr_all_positions_f hapf
537 where hapf.position_id = p_position_id
538 and trunc(sysdate) between hapf.effective_start_date
539 and hapf.effective_end_date;
540 --
541 begin
542 hr_utility.set_location('Entering:'|| l_proc, 10);
543 --
544 if hr_multi_message.no_exclusive_error
545 (p_check_column1 => 'IRC_TEMPLATE_ASSOCIATIONS.TEMPLATE_ID'
546 ) then
547 --
548
549 if ((irc_ita_shd.g_old_rec.template_association_id is null and p_position_id is not null)
550 or (irc_ita_shd.g_old_rec.template_association_id is not null
551 and nvl(irc_ita_shd.g_old_rec.position_id, hr_api.g_number)
552 <> nvl(p_position_id, hr_api.g_number))) then
553 --
554 hr_utility.set_location(l_proc, 20);
555 --
556 -- Check that the Position ID is linked to a
557 -- valid position on HR_ALL_POSITIONS_F
558 --
559 open csr_pos;
560 fetch csr_pos into l_pos;
561 --
562 if (csr_pos%notfound) then
563 --
564 close csr_pos;
565 fnd_message.set_name('PER','IRC_412092_POS_NOT_EXIST');
566 fnd_message.raise_error;
567 --
568 end if;
569 --
570 close csr_pos;
571 hr_utility.set_location(l_proc, 30);
572 --
573 end if;
574 end if; -- no exclusive error
575 --
576 hr_utility.set_location(' Leaving:'|| l_proc, 40);
577 --
578 exception
579 when app_exception.application_exception then
580 if hr_multi_message.exception_add
581 (p_associated_column1 => 'IRC_TEMPLATE_ASSOCIATIONS.POSITION_ID'
582 ) then
583 hr_utility.set_location(' Leaving:'|| l_proc, 50);
584 raise;
585 end if;
586 hr_utility.set_location(' Leaving:'|| l_proc, 60);
587 end chk_position_id;
588 --
589 -- ---------------------------------------------------------------------------
590 -- |----------------------< chk_job_position_organization >------------------|
591 -- ---------------------------------------------------------------------------
592 --
593 -- Description:
594 -- - Validates that only one of organization id, job id and position id is entered.
595 -- Pre-conditions:
596 -- None.
597 --
598 -- In Arguments:
599 -- p_organization_id
600 -- p_job_id
601 -- p_position_id
602 --
603 -- Post Success:
604 -- If only one of the input parameters is not null then
605 -- processing continues.
606 --
607 -- Post Failure:
608 -- If two or more of the input parameters are not null
609 -- an application error will be raised and processing is terminated.
610 --
611 -- Access Status:
612 -- Internal Table Handler Use Only.
613 --
614 -- {End Of Comments}
615 -- ----------------------------------------------------------------------------
616 procedure chk_job_position_organization
617 (p_job_id in irc_template_associations.job_id%TYPE
618 ,p_organization_id in irc_template_associations.organization_id%TYPE
619 ,p_position_id in irc_template_associations.position_id%TYPE
620 )
621 is
622 --
623 l_proc varchar2(72) := g_package||'chk_job_position_organization';
624 l_flag varchar2(1);
625 --
626
627 begin
628 hr_utility.set_location('Entering:'|| l_proc, 10);
629 --
630 if hr_multi_message.no_exclusive_error
631 (p_check_column1 => 'IRC_TEMPLATE_ASSOCIATIONS.TEMPLATE_ID'
632 ) then
633 --
634
635 l_flag := 0;
636 --
637 if (p_job_id is not null) then
638 l_flag := l_flag + 1;
639 end if;
640 --
641 if (p_position_id is not null) then
642 l_flag := l_flag + 1;
643 end if;
644 --
645 if (p_organization_id is not null) then
646 l_flag := l_flag + 1;
647 end if;
648 --
649 if (l_flag > 1) then
650 fnd_message.set_name('PER','IRC_412090_TOO_MANY_ARGS');
651 fnd_message.raise_error;
652 end if;
653 end if; -- no exclusive error
654 --
655 hr_utility.set_location(' Leaving:'|| l_proc, 40);
656 --
657 exception
658 when app_exception.application_exception then
659 if hr_multi_message.exception_add
660 (p_associated_column1 => 'IRC_TEMPLATE_ASSOCIATIONS.JOB_ID'
661 ,p_associated_column2 => 'IRC_TEMPLATE_ASSOCIATIONS.ORGANIZATION_ID'
662 ,p_associated_column3 => 'IRC_TEMPLATE_ASSOCIATIONS.POSITION_ID'
663 ) then
664 hr_utility.set_location(' Leaving:'|| l_proc, 50);
665 raise;
666 end if;
667 hr_utility.set_location(' Leaving:'|| l_proc, 60);
668 end chk_job_position_organization;
669
670 --
671 -- ---------------------------------------------------------------------------
672 -- |----------------------< chk_duplicate_template_id >---------------------|
673 -- ---------------------------------------------------------------------------
674 --
675 -- Description:
676 -- - Verifies for duplicate template id.
677 -- Pre-conditions:
678 -- None.
679 --
680 -- In Arguments:
681 -- p_template_id
682 -- p_organization_id
683 -- p_job_id
684 -- p_position_id
685 --
686 -- Post Success:
687 -- If duplicate entry is not found then
688 -- processing continues.
689 --
690 -- Post Failure:
691 -- An application error will be raised and processing is terminated.
692 --
693 -- Access Status:
694 -- Internal Table Handler Use Only.
695 --
696 -- {End Of Comments}
697 -- ----------------------------------------------------------------------------
698 procedure chk_duplicate_template_id
699 (p_template_id in irc_template_associations.template_id%TYPE
700 ,p_job_id in irc_template_associations.job_id%TYPE
701 ,p_organization_id in irc_template_associations.organization_id%TYPE
702 ,p_position_id in irc_template_associations.position_id%TYPE
703 )
704 is
705 --
706 l_proc varchar2(72) := g_package||'chk_duplicate_template_id';
707 l_flag varchar2(1);
708 --
709 cursor csr_org is
710 select null
711 from irc_template_associations ita
712 where ita.organization_id = p_organization_id
713 and ita.template_id = p_template_id;
714 --
715 cursor csr_pos is
716 select null
717 from irc_template_associations ita
718 where ita.position_id = p_position_id
719 and ita.template_id = p_template_id;
720 --
721 cursor csr_job is
722 select null
723 from irc_template_associations ita
724 where ita.job_id = p_job_id
725 and ita.template_id = p_template_id;
726 --
727
728 begin
729 hr_utility.set_location('Entering:'|| l_proc, 10);
730 --
731 if hr_multi_message.no_exclusive_error
732 (p_check_column1 => 'IRC_TEMPLATE_ASSOCIATIONS.TEMPLATE_ID'
733 ) then
734 --
735 --
736 if (p_job_id is not null) then
737 --
738 open csr_job;
739 fetch csr_job into l_flag;
740 --
741 if (csr_job%found) then
742 --
743 close csr_job;
744 fnd_message.set_name('PER','IRC_412328_OFR_DUP_TEMPLATE_ID');
745 fnd_message.raise_error;
746 --
747 end if;
748 --
749 end if;
750 --
751 if (p_position_id is not null) then
752 --
753 open csr_pos;
754 fetch csr_pos into l_flag;
755 --
756 if (csr_pos%found) then
757 --
758 close csr_pos;
759 fnd_message.set_name('PER','IRC_412328_OFR_DUP_TEMPLATE_ID');
760 fnd_message.raise_error;
761 --
762 end if;
763 --
764
765 end if;
766 --
767 if (p_organization_id is not null) then
768 --
769 open csr_org;
770 fetch csr_org into l_flag;
771 --
772 if (csr_org%found) then
773 --
774 close csr_org;
775 fnd_message.set_name('PER','IRC_412328_OFR_DUP_TEMPLATE_ID');
776 fnd_message.raise_error;
777 --
778 end if;
779 --
780 end if;
781 --
782 end if; -- no exclusive error
783 --
784 hr_utility.set_location(' Leaving:'|| l_proc, 40);
785 --
786 exception
787 when app_exception.application_exception then
788 if hr_multi_message.exception_add
789 (p_associated_column1 => 'IRC_TEMPLATE_ASSOCIATIONS.JOB_ID'
790 ,p_associated_column2 => 'IRC_TEMPLATE_ASSOCIATIONS.ORGANIZATION_ID'
791 ,p_associated_column3 => 'IRC_TEMPLATE_ASSOCIATIONS.POSITION_ID'
792 ) then
793 hr_utility.set_location(' Leaving:'|| l_proc, 50);
794 raise;
795 end if;
796 hr_utility.set_location(' Leaving:'|| l_proc, 60);
797 end chk_duplicate_template_id;
798
799 --
800 -- ---------------------------------------------------------------------------
801 -- |--------------------< chk_default_association >--------------------------|
802 -- ---------------------------------------------------------------------------
803 --
804 -- Description:
805 -- Validates that default association exists as a lookup code on HR_LOOKUPS
806 --
807 -- Pre-conditions:
808 -- None.
809 --
810 -- In Arguments:
811 -- p_template_association_id
812 -- p_object_version_number
813 -- p_default_association
814 --
815 -- Post Success:
816 -- default association exists as a lookup code in HR_LOOKUPS
817 -- Post Failure:
818 -- An application error is raised and processing is terminated if:
819 -- per information6 and 9 does not exist as a lookup code in
820 -- HR_LOOKUPS
821 --
822 -- Access Status:
823 -- Internal Table Handler Use Only.
824 --
825 -- {End Of Comments}
826 -- ----------------------------------------------------------------------------
827 Procedure chk_default_association
828 ( p_template_association_id in irc_template_associations.template_association_id%TYPE
829 ,p_object_version_number in irc_template_associations.object_version_number%TYPE
830 ,p_creation_date in irc_template_associations.creation_date%TYPE
831 ,p_default_association in irc_template_associations.default_association%TYPE
832 ) IS
833
834 -- Local declarations
835 l_proc VARCHAR2(72) := g_package||'chk_default_association';
836 l_api_updating boolean;
837 Begin
838 --
839 hr_utility.set_location('Entering:'|| l_proc, 10);
840 --
841 if hr_multi_message.no_exclusive_error
842 (p_check_column1 => 'IRC_TEMPLATE_ASSOCIATIONS.TEMPLATE_ID'
843 ) then
844 --
845 l_api_updating := irc_ita_shd.api_updating
846 (p_template_association_id => p_template_association_id
847 ,p_object_version_number => p_object_version_number);
848 --
849 hr_utility.set_location(l_proc,20);
850 --
851 -- Check if the value for default_association is set on insert or has
852 -- changed on update.
853 --
854 if ((nvl(p_default_association,hr_api.g_varchar2) <>
855 nvl(irc_ita_shd.g_old_rec.default_association,hr_api.g_varchar2)
856 and l_api_updating) or
857 (NOT l_api_updating))
858
859 then
860 --
861 -- Check that default_association exists in hr_lookups for the
862 -- lookup type 'YES_NO' with an enabled flag set to 'Y'.
863 --
864 hr_utility.set_location(l_proc,30);
865 if hr_api.not_exists_in_hr_lookups
866 (p_lookup_type => 'YES_NO'
867 ,p_lookup_code => p_default_association
868 ,p_effective_date => p_creation_date
869 )
870 then
871 --
872 hr_utility.set_location(l_proc,40);
873 fnd_message.set_name('PER','IRC_412329_OFR_INV_DEF_TMP_ASS');
874 fnd_message.raise_error;
875 --
876 end if;
877 end if;
878 end if; -- no exclusive error
879 hr_utility.set_location('Leaving: '||l_proc,50);
880 --
881 exception
882 when app_exception.application_exception then
883 if hr_multi_message.exception_add
884 (p_associated_column1 => 'IRC_TEMPLATE_ASSOCIATIONS.DEFAULT_ASSOCIATION'
885 ) then
886 hr_utility.set_location(' Leaving:'|| l_proc, 60);
887 raise;
888 end if;
889 hr_utility.set_location(' Leaving:'|| l_proc, 70);
890 End chk_default_association;
891 --
892 -- ----------------------------------------------------------------------------
893 -- |---------------------------< chk_dates >----------------------------------|
894 -- ----------------------------------------------------------------------------
895 -- {Start Of Comments}
896 --
897 -- Description:
898 -- This procedure ensures valid dates are entered
899 -- Pre Conditions:
900 -- g_old_rec has been populated with details of the values currently in
901 -- the database.
902 --
903 -- In Arguments:
904 -- p_template_association_id
905 -- p_object_version_number
906 -- p_start_date
907 -- p_end_date
908 -- Post Success:
909 -- Processing continues if start and end dates are valid and from date is lesser
910 -- to date
911 --
912 -- Post Failure:
913 -- An application error is raised if dates entered are not valid
914 --
915 -- {End Of Comments}
916 -- ----------------------------------------------------------------------------
917 Procedure chk_dates
918 (p_template_association_id in irc_template_associations.template_association_id%TYPE
919 ,p_object_version_number in irc_template_associations.object_version_number%TYPE
920 ,p_start_date in irc_template_associations.start_date%TYPE
921 ,p_end_date in irc_template_associations.end_date%TYPE
922 ) IS
923 --
924 l_proc varchar2(72) := g_package || 'chk_dates';
925 l_api_updating boolean;
926 --
927 Begin
928 hr_utility.set_location('Entering:'||l_proc,10);
929 --
930 if hr_multi_message.no_exclusive_error
931 (p_check_column1 => 'IRC_TEMPLATE_ASSOCIATIONS.TEMPLATE_ID'
932 ) then
933 --
934 l_api_updating := irc_ita_shd.api_updating
935 (p_template_association_id => p_template_association_id
936 ,p_object_version_number => p_object_version_number);
937 --
938 hr_utility.set_location(l_proc,20);
939 if ((l_api_updating
940 and
941 (nvl(irc_ita_shd.g_old_rec.start_date, hr_api.g_date) <>
942 nvl(p_start_date, hr_api.g_date) or
943 nvl(irc_ita_shd.g_old_rec.end_date, hr_api.g_date) <>
944 nvl(p_end_date, hr_api.g_date)))
945 or
946 (NOT l_api_updating)) then
947 --
948 hr_utility.set_location(l_proc,30);
949 if (p_start_date is not null)
950 then
951 hr_utility.set_location(l_proc,40);
952 --
953 if (p_start_date > nvl(p_end_date,hr_api.g_eot))
954 then
955 fnd_message.set_name('PER','IRC_ALL_DATE_START_END');
956 hr_multi_message.add
957 (p_associated_column1 => 'IRC_TEMPLATE_ASSOCIATIONS.START_DATE'
958 ,p_associated_column2 => 'IRC_TEMPLATE_ASSOCIATIONS.END_DATE'
959 );
960 end if;
961 end if;
962 end if;
963 end if; -- no exclusive error
964 --
965 hr_utility.set_location(' Leaving:'||l_proc,50);
966 --
967 exception
968 when app_exception.application_exception then
969 if hr_multi_message.exception_add
973 hr_utility.set_location(' Leaving:'|| l_proc, 60);
970 (p_associated_column1 => 'IRC_TEMPLATE_ASSOCIATIONS.START_DATE'
971 ,p_associated_column2 => 'IRC_TEMPLATE_ASSOCIATIONS.END_DATE'
972 ) then
974 raise;
975 end if;
976 hr_utility.set_location(' Leaving:'|| l_proc, 70);
977 End chk_dates;
978 --
979 -- ----------------------------------------------------------------------------
980 -- |---------------------------< insert_validate >----------------------------|
981 -- ----------------------------------------------------------------------------
982 Procedure insert_validate
983 (p_effective_date in date
984 ,p_rec in irc_ita_shd.g_rec_type
985 ) is
986 --
987 l_proc varchar2(72) := g_package||'insert_validate';
988 --
989 Begin
990 hr_utility.set_location('Entering:'||l_proc, 5);
991 --
992 -- Call all supporting business operations
993 -- CLIENT_INFO not set. No lookup validation or joins to HR_LOOKUPS.
994
995 --
996 -- Validate CHK_TEMPLATE_ID
997 --
998 irc_ita_bus.chk_template_id
999 (p_template_association_id => p_rec.template_association_id
1000 ,p_template_id => p_rec.template_id
1001 ,p_object_version_number => p_rec.object_version_number
1002 );
1003
1004 --
1005 -- Validate CHK_DEFAULT_ASSOCIATION
1006 --
1007 irc_ita_bus.chk_default_association
1008 (p_template_association_id => p_rec.template_association_id
1009 ,p_object_version_number => p_rec.object_version_number
1010 ,p_creation_date => p_rec.start_date
1011 ,p_default_association => p_rec.default_association
1012 );
1013
1014 --
1015 -- Validate CHK_ORGANIZATION_ID
1016 --
1017 irc_ita_bus.chk_organization_id
1018 (p_organization_id => p_rec.organization_id
1019 );
1020
1021 --
1022 -- Validate CHK_JOB_ID
1023 --
1024 irc_ita_bus.chk_job_id
1025 (p_job_id => p_rec.job_id
1026 );
1027
1028 --
1029 -- Validate CHK_POSITION_ID
1030 --
1031 irc_ita_bus.chk_position_id
1032 (p_position_id => p_rec.position_id
1033 );
1034
1035 --
1036 -- Validate CHK_JOB_POSITION_ORGANIZATION
1037 --
1038 irc_ita_bus.chk_job_position_organization
1039 (p_job_id => p_rec.job_id
1040 ,p_organization_id => p_rec.organization_id
1041 ,p_position_id => p_rec.position_id
1042 );
1043
1044 --
1045 -- Validate CHK_JOB_POSITION_ORGANIZATION
1046 --
1047 irc_ita_bus.chk_duplicate_template_id
1048 (p_template_id => p_rec.template_id
1049 ,p_job_id => p_rec.job_id
1050 ,p_organization_id => p_rec.organization_id
1051 ,p_position_id => p_rec.position_id
1052 );
1053
1054
1055 --
1056 -- Validate CHK_DATES
1057 --
1058 irc_ita_bus.chk_dates
1059 (p_template_association_id => p_rec.template_association_id
1060 ,p_object_version_number => p_rec.object_version_number
1061 ,p_start_date => p_rec.start_date
1062 ,p_end_date => p_rec.end_date
1063 );
1064 hr_utility.set_location(' Leaving:'||l_proc, 10);
1065 End insert_validate;
1066 --
1067 -- ----------------------------------------------------------------------------
1068 -- |---------------------------< update_validate >----------------------------|
1069 -- ----------------------------------------------------------------------------
1070 Procedure update_validate
1071 (p_effective_date in date
1072 ,p_rec in irc_ita_shd.g_rec_type
1073 ) is
1074 --
1075 l_proc varchar2(72) := g_package||'update_validate';
1076 --
1077 Begin
1078 hr_utility.set_location('Entering:'||l_proc, 5);
1079 --
1080 -- Call all supporting business operations
1081 --
1082
1083 -- CLIENT_INFO not set. No lookup validation or joins to HR_LOOKUPS
1084 --
1085 -- Validate Dependent Attributes
1086 --
1087 chk_non_updateable_args
1088 (p_rec => p_rec
1089 );
1090 --
1091 --
1092
1093 --
1094 -- Validate CHK_TEMPLATE_ID
1095 --
1096 irc_ita_bus.chk_template_id
1097 (p_template_association_id => p_rec.template_association_id
1098 ,p_template_id => p_rec.template_id
1099 ,p_object_version_number => p_rec.object_version_number
1100 );
1101
1102 --
1103 -- Validate CHK_DEFAULT_ASSOCIATION
1104 --
1105 irc_ita_bus.chk_default_association
1106 (p_template_association_id => p_rec.template_association_id
1107 ,p_object_version_number => p_rec.object_version_number
1108 ,p_creation_date => p_rec.start_date
1109 ,p_default_association => p_rec.default_association
1110 );
1111
1112 --
1113 -- Validate CHK_ORGANIZATION_ID
1114 --
1115 irc_ita_bus.chk_organization_id
1116 (p_organization_id => p_rec.organization_id
1117 );
1118
1119 --
1120 -- Validate CHK_JOB_ID
1121 --
1122 irc_ita_bus.chk_job_id
1123 (p_job_id => p_rec.job_id
1124 );
1125
1126 --
1127 -- Validate CHK_POSITION_ID
1128 --
1129 irc_ita_bus.chk_position_id
1130 (p_position_id => p_rec.position_id
1131 );
1132
1133 --
1134 -- Validate CHK_JOB_POSITION_ORGANIZATION
1135 --
1136 irc_ita_bus.chk_job_position_organization
1137 (p_job_id => p_rec.job_id
1138 ,p_organization_id => p_rec.organization_id
1139 ,p_position_id => p_rec.position_id
1140 );
1141
1142 --
1143 -- Validate CHK_DATES
1144 --
1145 irc_ita_bus.chk_dates
1146 (p_template_association_id => p_rec.template_association_id
1147 ,p_object_version_number => p_rec.object_version_number
1148 ,p_start_date => p_rec.start_date
1149 ,p_end_date => p_rec.end_date
1150 );
1151
1152
1153 hr_utility.set_location(' Leaving:'||l_proc, 10);
1154 End update_validate;
1155 --
1156 -- ----------------------------------------------------------------------------
1157 -- |---------------------------< delete_validate >----------------------------|
1158 -- ----------------------------------------------------------------------------
1159 Procedure delete_validate
1160 (p_rec in irc_ita_shd.g_rec_type
1161 ) is
1162 --
1163 l_proc varchar2(72) := g_package||'delete_validate';
1164 --
1165 Begin
1166 hr_utility.set_location('Entering:'||l_proc, 5);
1167 --
1168 -- Call all supporting business operations
1169 --
1170 hr_utility.set_location(' Leaving:'||l_proc, 10);
1171 End delete_validate;
1172 --
1173 end irc_ita_bus;