[Home] [Help]
PACKAGE BODY: APPS.IRC_CMR_BUS
Source
1 Package Body irc_cmr_bus as
2 /* $Header: ircmrrhi.pkb 120.1 2008/04/14 14:51:14 amikukum noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' irc_cmr_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_communication_recipient_id number default null;
15 --
16 -- ---------------------------------------------------------------------------
17 -- |----------------------< set_security_group_id >--------------------------|
18 -- ---------------------------------------------------------------------------
19 --
20 Procedure set_security_group_id
21 (p_communication_recipient_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_comm_recipients 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_comm_recipients cmr
37 -- , EDIT_HERE table_name(s) 333
38 where cmr.communication_recipient_id = p_communication_recipient_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 => 'communication_recipient_id'
56 ,p_argument_value => p_communication_recipient_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,'COMMUNICATION_RECIPIENT_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_communication_recipient_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_comm_recipients 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_comm_recipients cmr
113 -- , EDIT_HERE table_name(s) 333
114 where cmr.communication_recipient_id = p_communication_recipient_id;
115 -- and pbg.business_group_id = EDIT_HERE 333.business_group_id;
116 --
117 -- Declare local variables
118 --
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 => 'communication_recipient_id'
131 ,p_argument_value => p_communication_recipient_id
132 );
133 --
134 if ( nvl(irc_cmr_bus.g_communication_recipient_id, hr_api.g_number)
135 = p_communication_recipient_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_cmr_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_cmr_bus.g_communication_recipient_id := p_communication_recipient_id;
166 irc_cmr_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_effective_date in date
201 ,p_rec in irc_cmr_shd.g_rec_type
202 ) IS
203 --
204 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
205 --
206 Begin
207 --
208 -- Only proceed with the validation if a row exists for the current
209 -- record in the HR Schema.
210 --
211 IF NOT irc_cmr_shd.api_updating
212 (p_communication_recipient_id => p_rec.communication_recipient_id
213 ,p_object_version_number => p_rec.object_version_number
214 ) THEN
215 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
216 fnd_message.set_token('PROCEDURE ', l_proc);
217 fnd_message.set_token('STEP ', '5');
218 fnd_message.raise_error;
219 END IF;
220 --
221 -- EDIT_HERE: Add checks to ensure non-updateable args have
222 -- not been updated.
223 if p_rec.communication_object_type <> irc_cmr_shd.g_old_rec.communication_object_type
224 then
225 hr_api.argument_changed_error
226 (p_api_name => l_proc
227 ,p_argument => 'COMMUNICATION_OBJECT_TYPE'
228 ,p_base_table => irc_cmr_shd.g_tab_nam
229 );
230 end if;
231 --
232 if p_rec.communication_object_id <> irc_cmr_shd.g_old_rec.communication_object_id
233 then
234 hr_api.argument_changed_error
235 (p_api_name => l_proc
236 ,p_argument => 'COMMUNICATION_OBJECT_ID'
237 ,p_base_table => irc_cmr_shd.g_tab_nam
238 );
239 end if;
240 if p_rec.recipient_type <> irc_cmr_shd.g_old_rec.recipient_type
241 then
242 hr_api.argument_changed_error
243 (p_api_name => l_proc
244 ,p_argument => 'RECIPIENT_TYPE'
245 ,p_base_table => irc_cmr_shd.g_tab_nam
246 );
247 end if;
248 --
249 if p_rec.recipient_id <> irc_cmr_shd.g_old_rec.recipient_id
250 then
251 hr_api.argument_changed_error
252 (p_api_name => l_proc
253 ,p_argument => 'RECIPIENT_ID'
254 ,p_base_table => irc_cmr_shd.g_tab_nam
255 );
256 end if;
257 --
258 End chk_non_updateable_args;
259 --
260 -- ----------------------------------------------------------------------------
261 -- |------------------------------< chk_communication_object >------------------------------|
262 -- ----------------------------------------------------------------------------
263 -- {Start Of Comments}
264 --
265 -- Description:
266 --
267 -- In Arguments:
268 --
269 -- Post Success:
270 --
271 -- Post Failure:
272 --
273 -- {End Of Comments}
274 -- ----------------------------------------------------------------------------
275 Procedure chk_communication_object
276 (p_communication_object_type in irc_comm_recipients.communication_object_type%type ,
277 p_communication_object_id in irc_comm_recipients.communication_object_id%type
278 ) is
279 --
280 l_proc varchar2(72) := g_package||'chk_communication_object';
281 l_comm_object_id irc_comm_topics.communication_topic_id%type;
282 --
283 cursor csr_comm_topic is
284 select communication_topic_id
285 from irc_comm_topics
286 where communication_topic_id = p_communication_object_id;
287 --
288 Begin
289 --
290 hr_utility.set_location(' Entering:'||l_proc,10);
291 --
292 hr_api.mandatory_arg_error
293 (p_api_name => l_proc
294 ,p_argument => 'COMMUNICATION_OBJECT_TYPE'
295 ,p_argument_value => p_communication_object_type
296 );
297 --
298 hr_utility.set_location(' Entering:'||l_proc,20);
299 --
300 hr_api.mandatory_arg_error
301 (p_api_name => l_proc
302 ,p_argument => 'COMMUNICATION_OBJECT_ID'
303 ,p_argument_value => p_communication_object_id
304 );
305 --
306 if p_communication_object_type = 'TOPIC' then
307 open csr_comm_topic;
308 fetch csr_comm_topic into l_comm_object_id;
309 --
310 hr_utility.set_location(l_proc,30);
311 --
312 if csr_comm_topic%notfound then
313 close csr_comm_topic;
314 fnd_message.set_name('PER','IRC_412393_BAD_COMM_OBJ_ID');
315 fnd_message.raise_error;
316 end if;
317 --
318 hr_utility.set_location(l_proc,40);
319 --
320 close csr_comm_topic;
321 end if;
322 exception
323 when app_exception.application_exception then
324 if hr_multi_message.exception_add
325 (p_associated_column1 => 'IRC_COMM_RECIPIENTS.COMMUNICATION_OBJECT_TYPE'
326 ,p_associated_column2 => 'IRC_COMM_RECIPIENTS.COMMUNICATION_OBJECT_ID'
327 ) then
328 --
329 hr_utility.set_location(' Leaving:'||l_proc,50);
330 --
331 raise;
332 end if;
333 --
334 hr_utility.set_location(' Leaving:'||l_proc,60);
335 --
336 End chk_communication_object;
337 --
338 -- ----------------------------------------------------------------------------
339 -- |------------------------------< chk_recipient >------------------------------|
340 -- ----------------------------------------------------------------------------
341 -- {Start Of Comments}
342 --
343 -- Description:
344 --
345 -- In Arguments:
346 --
347 -- Post Success:
348 --
349 -- Post Failure:
350 --
351 -- {End Of Comments}
352 -- ----------------------------------------------------------------------------
353 Procedure chk_recipient
354 (p_recipient_type in irc_comm_recipients.recipient_type%type ,
355 p_recipient_id in irc_comm_recipients.recipient_id%type ,
356 p_communication_object_type in irc_comm_recipients.communication_object_type%type,
357 p_communication_object_id in irc_comm_recipients.communication_object_id%type
358 ) is
359 --
360 l_proc varchar2(72) := g_package||'chk_recipient';
361 l_recipient_id per_all_people_f.person_id%type;
362 --
363 cursor csr_recipient_person is
364 select person_id
365 from per_all_people_f
366 where person_id = p_recipient_id;
367 --
368 cursor csr_recipient_agency is
369 select vendor_id
370 from po_vendors
371 where vendor_id = p_recipient_id;
372 --
373 cursor csr_recipient is
374 select communication_recipient_id
375 from irc_comm_recipients
376 where communication_object_type = p_communication_object_type
377 and communication_object_id = p_communication_object_id
378 and recipient_type = p_recipient_type
379 and recipient_id = p_recipient_id;
380 --
381 Begin
382 --
383 hr_utility.set_location(' Entering:'||l_proc,10);
384 --
385 hr_api.mandatory_arg_error
386 (p_api_name => l_proc
387 ,p_argument => 'RECIPIENT_TYPE'
388 ,p_argument_value => p_recipient_type
389 );
390 --
391 hr_utility.set_location(' Entering:'||l_proc,20);
392 --
393 hr_api.mandatory_arg_error
394 (p_api_name => l_proc
395 ,p_argument => 'RECIPIENT_ID'
396 ,p_argument_value => p_recipient_id
397 );
398 --
399 if p_recipient_type = 'PERSON' then
400 open csr_recipient_person;
401 fetch csr_recipient_person into l_recipient_id;
402 --
403 hr_utility.set_location(l_proc,30);
404 --
405 if csr_recipient_person%notfound then
406 close csr_recipient_person;
407 fnd_message.set_name('PER','IRC_412394_BAD_RECIPIENT_ID');
408 fnd_message.raise_error;
409 end if;
410 --
411 hr_utility.set_location(l_proc,40);
412 --
413 close csr_recipient_person;
414 end if;
415 if p_recipient_type = 'AGENCY' then
416 open csr_recipient_agency;
417 fetch csr_recipient_agency into l_recipient_id;
418 --
419 hr_utility.set_location(l_proc,50);
420 --
421 if csr_recipient_agency%notfound then
422 close csr_recipient_agency;
423 fnd_message.set_name('PER','IRC_412395_BAD_RECIPIENT_ID');
424 fnd_message.raise_error;
425 end if;
426 --
427 hr_utility.set_location(l_proc,60);
428 --
429 close csr_recipient_agency;
430 end if;
431 open csr_recipient;
432 fetch csr_recipient into l_recipient_id;
433 --
434 hr_utility.set_location(l_proc,70);
435 --
436 if csr_recipient%found then
437 close csr_recipient;
438 fnd_message.set_name('PER','IRC_412396_ALREADY_RECIPIENT');
439 fnd_message.raise_error;
440 end if;
441 --
442 hr_utility.set_location(l_proc,80);
443 --
444 close csr_recipient;
445 --
446 exception
447 when app_exception.application_exception then
448 if hr_multi_message.exception_add
449 (p_associated_column1 => 'IRC_COMM_RECIPIENTS.RECIPIENT_TYPE'
450 ,p_associated_column2 => 'IRC_COMM_RECIPIENTS.RECIPIENT_ID'
451 ) then
452 --
453 hr_utility.set_location(' Leaving:'||l_proc,90);
454 --
455 raise;
456 end if;
457 --
458 hr_utility.set_location(' Leaving:'||l_proc,100);
459 --
460 End chk_recipient;
461 --
462 -- ----------------------------------------------------------------------------
463 -- |-------------------------< chk_active_dates >-------------------------|
464 -- ----------------------------------------------------------------------------
465 -- {Start Of Comments}
466 --
467 -- Description:
468 --
469 -- In Arguments:
470 --
471 -- Post Success:
472 --
473 -- Post Failure:
474 --
475 -- {End Of Comments}
476 -- ----------------------------------------------------------------------------
477 Procedure chk_active_dates
478 (p_start_date_active in irc_comm_recipients.start_date_active%type
479 ,p_end_date_active in irc_comm_recipients.end_date_active%type
480 ) is
481 --
482 l_proc varchar2(72) := g_package||'chk_active_dates';
483 --
484 --
485 Begin
486 --
487 hr_utility.set_location(' Entering:'||l_proc,10);
488 --
489 hr_api.mandatory_arg_error
490 (p_api_name => l_proc
491 ,p_argument => 'MESSAGE_POST_DATE'
492 ,p_argument_value => p_start_date_active
493 );
494 --
495 if p_start_date_active > nvl(p_end_date_active,p_start_date_active) then
496 fnd_message.set_name('PER','IRC_412397_START_DATE_BEFORE_END');
497 fnd_message.raise_error;
498 end if;
499 --
500 exception
501 when app_exception.application_exception then
502 if hr_multi_message.exception_add
503 (p_associated_column1 => 'IRC_COMM_RECIPIENTS.START_DATE_ACTIVE'
504 ,p_associated_column2 => 'IRC_COMM_RECIPIENTS.END_DATE_ACTIVE'
505 ) then
506 --
507 hr_utility.set_location(' Leaving:'||l_proc,20);
508 --
509 raise;
510 end if;
511 --
512 hr_utility.set_location(' Leaving:'||l_proc,30);
513 --
514 End chk_active_dates;
515 --
516 -- ----------------------------------------------------------------------------
517 -- |---------------------------< chk_primary_flag >---------------------------|
518 -- ----------------------------------------------------------------------------
519 Procedure chk_primary_flag
520 (p_primary_flag in irc_comm_recipients.primary_flag%type
521 ) is
522 --
523 l_proc varchar2(72) := g_package||'chk_primary_flag';
524 --
525 --
526 Begin
527 --
528 hr_utility.set_location(' Entering:'||l_proc,10);
529 --
530 if p_primary_flag <> 'Y' and p_primary_flag <> 'N' then
531 --
532 -- raise error
533 --
534 hr_utility.set_location('Leaving: '|| l_proc, 20);
535 hr_utility.set_message(800,'IRC_412398_INVALID_PRIMARY_FLAG');
536 hr_utility.raise_error;
537 end if;
538 --
539 exception
540 when app_exception.application_exception then
541 if hr_multi_message.exception_add
542 (p_associated_column1 => 'IRC_COMM_RECIPIENTS.PRIMARY_FLAG'
543 ) then
544 --
545 hr_utility.set_location(' Leaving:'||l_proc,30);
546 --
547 raise;
548 end if;
549 --
550 hr_utility.set_location(' Leaving:'||l_proc,40);
551 --
552 End chk_primary_flag;
553 --
554 -- ----------------------------------------------------------------------------
555 -- |---------------------------< insert_validate >----------------------------|
556 -- ----------------------------------------------------------------------------
557 Procedure insert_validate
558 (p_effective_date in date
559 ,p_rec in irc_cmr_shd.g_rec_type
560 ) is
561 --
562 l_proc varchar2(72) := g_package||'insert_validate';
563 --
564 Begin
565 hr_utility.set_location('Entering:'||l_proc, 5);
566 --
567 -- Call all supporting business operations
568 --
569 --
570 -- EDIT_HERE: As this table does not have a mandatory business_group_id
571 -- column, ensure client_info is populated by calling a suitable
572 -- ???_???_bus.set_security_group_id procedure, or add one of the following
573 -- comments:
574 -- "-- No business group context. HR_STANDARD_LOOKUPS used for validation."
575 -- "-- CLIENT_INFO not set. No lookup validation or joins to HR_LOOKUPS."
576 --
577 -- Validate Dependent Attributes
578 --
579 chk_communication_object
580 (p_communication_object_type => p_rec.communication_object_type
581 ,p_communication_object_id => p_rec.communication_object_id);
582 --
583 hr_utility.set_location(' Leaving:'||l_proc, 10);
584 --
585 chk_recipient
586 (p_recipient_type => p_rec.recipient_type
587 ,p_recipient_id => p_rec.recipient_id
588 ,p_communication_object_type => p_rec.communication_object_type
589 ,p_communication_object_id => p_rec.communication_object_id);
590 --
591 hr_utility.set_location(' Leaving:'||l_proc, 20);
592 --
593 chk_active_dates
594 (p_start_date_active => p_rec.start_date_active
595 ,p_end_date_active => p_rec.end_date_active);
596 --
597 hr_utility.set_location(' Leaving:'||l_proc, 30);
598 --
599 chk_primary_flag(p_rec.primary_flag);
600 --
601 hr_utility.set_location(' Leaving:'||l_proc, 40);
602 --
603 End insert_validate;
604 --
605 -- ----------------------------------------------------------------------------
606 -- |---------------------------< update_validate >----------------------------|
607 -- ----------------------------------------------------------------------------
608 Procedure update_validate
609 (p_effective_date in date
610 ,p_rec in irc_cmr_shd.g_rec_type
611 ) is
612 --
613 l_proc varchar2(72) := g_package||'update_validate';
614 --
615 Begin
616 hr_utility.set_location('Entering:'||l_proc, 5);
617 --
618 -- Call all supporting business operations
619 --
620 --
621 -- EDIT_HERE: As this table does not have a mandatory business_group_id
622 -- column, ensure client_info is populated by calling a suitable
623 -- ???_???_bus.set_security_group_id procedure, or add one of the following
624 -- comments:
625 -- "-- No business group context. HR_STANDARD_LOOKUPS used for validation."
626 -- "-- CLIENT_INFO not set. No lookup validation or joins to HR_LOOKUPS."
627 --
628 -- Validate Dependent Attributes
629 --
630 chk_non_updateable_args
631 (p_effective_date => p_effective_date
632 ,p_rec => p_rec
633 );
634 --
635 hr_utility.set_location(' Leaving:'||l_proc, 10);
636 --
637 chk_communication_object
638 (p_communication_object_type => p_rec.communication_object_type
639 ,p_communication_object_id => p_rec.communication_object_id);
640 --
641 hr_utility.set_location(' Leaving:'||l_proc, 20);
642 --
643 chk_recipient
644 (p_recipient_type => p_rec.recipient_type
645 ,p_recipient_id => p_rec.recipient_id
646 ,p_communication_object_type => p_rec.communication_object_type
647 ,p_communication_object_id => p_rec.communication_object_id);
648 --
649 hr_utility.set_location(' Leaving:'||l_proc, 30);
650 --
651 chk_active_dates
652 (p_start_date_active => p_rec.start_date_active
653 ,p_end_date_active => p_rec.end_date_active);
654 --
655 hr_utility.set_location(' Leaving:'||l_proc, 40);
656 --
657 chk_primary_flag(p_rec.primary_flag);
658 --
659 hr_utility.set_location(' Leaving:'||l_proc, 50);
660 --
661 End update_validate;
662 --
663 -- ----------------------------------------------------------------------------
664 -- |---------------------------< delete_validate >----------------------------|
665 -- ----------------------------------------------------------------------------
666 Procedure delete_validate
667 (p_rec in irc_cmr_shd.g_rec_type
668 ) is
669 --
670 l_proc varchar2(72) := g_package||'delete_validate';
671 --
672 Begin
673 hr_utility.set_location('Entering:'||l_proc, 5);
674 --
675 -- Call all supporting business operations
676 --
677 hr_utility.set_location(' Leaving:'||l_proc, 10);
678 End delete_validate;
679 --
680 end irc_cmr_bus;