1 Package Body irc_cmc_bus as
2 /* $Header: ircmcrhi.pkb 120.0 2007/11/19 11:04:15 sethanga noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' irc_cmc_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_id number default null;
15 --
16 -- ---------------------------------------------------------------------------
17 -- |----------------------< set_security_group_id >--------------------------|
18 -- ---------------------------------------------------------------------------
19 --
20 Procedure set_security_group_id
21 (p_communication_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_communications 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_communications cmc
37 -- , EDIT_HERE table_name(s) 333
38 where cmc.communication_id = p_communication_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_id'
56 ,p_argument_value => p_communication_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_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_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_communications 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_communications cmc
113 -- , EDIT_HERE table_name(s) 333
114 where cmc.communication_id = p_communication_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_id'
131 ,p_argument_value => p_communication_id
132 );
133 --
134 if ( nvl(irc_cmc_bus.g_communication_id, hr_api.g_number)
135 = p_communication_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_cmc_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_cmc_bus.g_communication_id := p_communication_id;
166 irc_cmc_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_cmc_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_cmc_shd.api_updating
212 (p_communication_id => p_rec.communication_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 --
224 IF p_rec.object_type <>
225 irc_cmc_shd.g_old_rec.object_type then
226 hr_api.argument_changed_error
227 (p_api_name => l_proc
228 ,p_argument => 'OBJECT_TYPE'
229 ,p_base_table => irc_cmc_shd.g_tab_nam
230 );
231 END IF;
232 --
233 IF p_rec.object_id <> irc_cmc_shd.g_old_rec.object_id THEN
234 IF p_rec.object_type = 'APPL' THEN
235 hr_api.argument_changed_error
236 ( p_api_name => l_proc
237 ,p_argument => 'APPLICATION_ID'
238 ,p_base_table => irc_cmc_shd.g_tab_nam
239 );
240 END IF;
241 END IF;
242
243 IF p_rec.communication_property_id <>
244 irc_cmc_shd.g_old_rec.communication_property_id then
245 hr_api.argument_changed_error
246 (p_api_name => l_proc
247 ,p_argument => 'COMMUNICATION_PROPERTY_ID'
248 ,p_base_table => irc_cmc_shd.g_tab_nam
249 );
250 END IF;
251
252 IF p_rec.start_date <>
253 irc_cmc_shd.g_old_rec.start_date then
254 hr_api.argument_changed_error
255 (p_api_name => l_proc
256 ,p_argument => 'START_DATE'
257 ,p_base_table => irc_cmc_shd.g_tab_nam
258 );
259 END IF;
260
261 End chk_non_updateable_args;
262 --
263 -- ---------------------------------------------------------------------------
264 -- |----------------------------< chk_status >--------------------------------|
265 -- ---------------------------------------------------------------------------
266 --
267 -- {Start Of Comments}
268 --
269 -- Description:
270 -- This process validates that 'status' exists in the lookup
271 -- IRC_COMM_STATUS
272 --
273 -- Pre Conditions:
274 -- None.
275 --
276 -- In Parameters:
277 -- status varchar2(50) communication status
278 -- communication_id number(15) PK of irc_communications
279 -- effective_date date date record effective
280 -- object_version_number number(9) version of row
281 --
282 -- Post Success:
283 -- Processing continues.
284 --
285 -- Post Failure:
286 -- An application error will be raised for the following faliure conditions:
287 -- 1: p_status does not exist in lookup IRC_COMM_STATUS
288 --
289 -- Access Status:
290 -- Internal Table Handler Use Only.
291 Procedure chk_status(p_status in varchar2,
292 p_communication_id in number,
293 p_effective_date in date,
294 p_object_version_number in number) is
295 --
296 l_proc varchar2(72) := g_package||'chk_status';
297 l_api_updating boolean;
298 --
299 begin
300 hr_utility.set_location('Entering: '|| l_proc, 10);
301 l_api_updating := irc_cmc_shd.api_updating
302 (p_communication_id => p_communication_id,
303 p_object_version_number => p_object_version_number);
304 --
305 if (l_api_updating
306 and nvl(p_status,hr_api.g_varchar2)
307 <> nvl(irc_cmc_shd.g_old_rec.status,hr_api.g_varchar2)
308 or not l_api_updating) then
309 --
310 -- check if value of type falls within lookup.
311 --
312 if hr_api.not_exists_in_hr_lookups(p_lookup_type => 'IRC_COMM_STATUS',
313 p_lookup_code => p_status,
314 p_effective_date => sysdate)
315 then
316 --
317 -- raise error as does not exist as lookup
318 --
319 hr_utility.set_location('Leaving: '|| l_proc, 20);
320 fnd_message.set_name('PER','IRC_412416_INVALID_COMM_STATUS');
321 fnd_message.raise_error;
322 end if;
323 end if;
324 hr_utility.set_location('Leaving: '|| l_proc, 30);
325 end chk_status;
326 --
327 -- ----------------------------------------------------------------------------
328 -- |-----------------------------< chk_object_id >-----------------------------|
329 -- ----------------------------------------------------------------------------
330 --
331 -- {Start Of Comments}
332 --
333 -- Description:
334 -- This procedure is used to ensure -
335 -- 1) that object_id exists in PER_ALL_ASSIGNMENTS_F
336 -- when the object_type is 'APPL'
337 -- 2) that combination of (object_id,object_type) is
338 -- unique.
339 --
340 -- Pre Conditions:
341 --
342 -- In Arguments:
343 -- p_object_id
344 -- p_object_type
345 --
346 -- Post Success:
347 -- Processing continues if object_id is valid.
348 --
349 -- Post Failure:
350 -- An application error is raised if object_id is invalid.
351 --
352 -- {End Of Comments}
353 -- ----------------------------------------------------------------------------
354 Procedure chk_object_id
355 (p_object_id in irc_communications.object_id%TYPE,
356 p_object_type in irc_communications.object_type%TYPE
357 ) IS
358 --
359 l_proc varchar2(72) := g_package || 'chk_object_id';
360 l_object_id varchar2(1);
361 l_object_type varchar2(1);
362 --
363 cursor csr_object_id is
364 select null
365 from per_all_assignments_f paaf
366 where paaf.assignment_id = p_object_id
367 and assignment_type = 'A';
368 --
369 cursor csr_object_type is
370 select null
371 from irc_communications ic
372 where ic.object_id = p_object_id
373 and ic.object_type = p_object_type;
374 --
375 begin
376 hr_utility.set_location('Entering:'||l_proc,10);
377 -- Check that object_id is not null.
378 hr_api.mandatory_arg_error
379 (p_api_name => l_proc
380 ,p_argument => 'OBJECT_ID'
381 ,p_argument_value => p_object_id
382 );
383
384 -- Check that object_type is not null.
385 hr_api.mandatory_arg_error
386 (p_api_name => l_proc
387 ,p_argument => 'OBJECT_TYPE'
388 ,p_argument_value => p_object_type
389 );
390
391 -- Check that object_id exists in per_all_assignments_f
392 hr_utility.set_location(l_proc,20);
393 open csr_object_id;
394 fetch csr_object_id into l_object_id;
395 hr_utility.set_location(l_proc,30);
396 if csr_object_id%NOTFOUND then
397 close csr_object_id;
398 fnd_message.set_name('PER','IRC_412417_BAD_COMM_OBJ_ID');
399 fnd_message.raise_error;
400 end if;
401 close csr_object_id;
402
403 -- Check that combination of (object_id,object_type) is unique.
404
405 open csr_object_type;
406 fetch csr_object_type into l_object_type;
407 hr_utility.set_location(l_proc,40);
408 if csr_object_type%FOUND then
409 close csr_object_type;
410 fnd_message.set_name('PER','IRC_412418_OBJID_OBJTYP_NOT_UNQ');
411 fnd_message.raise_error;
412 end if;
413 close csr_object_type;
414
415 hr_utility.set_location(' Leaving:'||l_proc,50);
416 exception
417 when app_exception.application_exception then
418 if hr_multi_message.exception_add
419 (p_associated_column1 =>
420 'IRC_COMMUNICATIONS.OBJECT_ID'
421 ) then
422 hr_utility.set_location(' Leaving:'||l_proc,60);
423 raise;
424 end if;
425 hr_utility.set_location(' Leaving:'||l_proc,70);
426 end chk_object_id;
427 --
428 -- ----------------------------------------------------------------------------
429 -- |-----------------------------< chk_object_type >-----------------------------|
430 -- ----------------------------------------------------------------------------
431 --
432 -- {Start Of Comments}
433 --
434 -- Description:
435 -- This procedure is used to ensure that object_type has one of the following
436 -- values :
437 -- 'APPL'
438 --
439 -- Pre Conditions:
440 --
441 -- In Arguments:
442 -- p_object_type
443 --
444 -- Post Success:
445 -- Processing continues if object_type is valid.
446 --
447 -- Post Failure:
448 -- An application error is raised if object_type is invalid.
449 --
450 -- {End Of Comments}
451 -- ----------------------------------------------------------------------------
452 Procedure chk_object_type
453 (p_object_type in irc_communications.object_type%TYPE
454 ) IS
455 --
456 l_proc varchar2(72) := g_package || 'chk_object_type';
457 --
458 begin
459 hr_utility.set_location('Entering:'||l_proc,10);
460 -- Check that object_type is not null.
461 hr_api.mandatory_arg_error
462 (p_api_name => l_proc
463 ,p_argument => 'OBJECT_TYPE'
464 ,p_argument_value => p_object_type
465 );
466
467 if p_object_type <> 'APPL' then
468 fnd_message.set_name('PER','IRC_412419_BAD_OBJECT_TYPE');
469 fnd_message.raise_error;
470 end if;
471 hr_utility.set_location(' Leaving:'||l_proc,20);
472 exception
473 when app_exception.application_exception then
474 if hr_multi_message.exception_add
475 (p_associated_column1 =>
476 'IRC_COMMUNICATIONS.OBJECT_TYPE'
477 ) then
478 hr_utility.set_location(' Leaving:'||l_proc,30);
479 raise;
480 end if;
481 hr_utility.set_location(' Leaving:'||l_proc,40);
482 end chk_object_type;
483 --
484 -- ---------------------------------------------------------------------------
485 -- |----------------------------< chk_start_end_date >------------------------------|
486 -- ---------------------------------------------------------------------------
487 --
488 -- {Start Of Comments}
489 --
490 -- Description:
491 -- This procedure is used to check that the end_date is later than the
492 -- start_date.
493 --
494 -- Pre Conditions:
495 -- None.
496 --
497 -- In Parameters:
498 -- communication_id number(15) PK of irc_communications
499 -- start_date date start date of communication
500 -- end_date date end date of communication
501 -- object_version_number number(9) version of row
502 --
503 -- Post Success:
504 -- Processing continues.
505 --
506 -- Post Failure:
507 -- Errors handled by the procedure.
508 --
509 -- Access Status:
510 -- Internal Table Handler Use Only.
511 --
512 Procedure chk_start_end_date(p_start_date in date,
513 p_end_date in date,
514 p_communication_id in number,
515 p_object_version_number in number) is
516 --
517 l_proc varchar2(72) := g_package||'chk_start_end_date';
518 l_api_updating boolean;
519 --
520 begin
521 hr_utility.set_location('Entering: '|| l_proc, 10);
522 l_api_updating := irc_cmc_shd.api_updating
523 (p_communication_id => p_communication_id,
524 p_object_version_number => p_object_version_number);
525 --
526 if (l_api_updating
527 and (nvl(p_start_date,hr_api.g_date)
528 <> nvl(irc_cmc_shd.g_old_rec.start_date,hr_api.g_date)
529 or nvl(p_end_date,hr_api.g_date)
530 <> nvl(irc_cmc_shd.g_old_rec.end_date,hr_api.g_date))
531 or not l_api_updating) then
532 --
533 -- check if end date is greater than start date
534 --
535 if p_start_date > nvl(p_end_date,hr_api.g_eot) then
536 --
537 -- raise error as start date should be less than or equal to end date.
538 --
539 fnd_message.set_name('PER','IRC_412420_START_DATE_BEFORE_END');
540 fnd_message.raise_error;
541 --
542 end if;
543 --
544 end if;
545 --
546 hr_utility.set_location(' Leaving:'||l_proc, 20);
547 --
548 End chk_start_end_date;
549 --
550 -- ----------------------------------------------------------------------------
551 -- |---------------------------< insert_update_validate >----------------------|
552 -- ----------------------------------------------------------------------------
553 Procedure insert_update_validate
554 (p_effective_date in date
555 ,p_rec in irc_cmc_shd.g_rec_type
556 ) is
557 --
558 l_proc varchar2(72) := g_package||'insert_update_validate';
559 --
560 Begin
561 --
562 irc_cmc_bus.chk_status
563 (
564 p_rec.status,
565 p_rec.communication_id,
566 p_effective_date,
567 p_rec.object_version_number
568 );
569
570 irc_cmc_bus.chk_start_end_date
571 ( p_rec.start_date,
572 p_rec.end_date,
573 p_rec.communication_id,
574 p_rec.object_version_number
575 );
576 --
577 End insert_update_validate;
578 --
579 -- ----------------------------------------------------------------------------
583 (p_effective_date in date
580 -- |---------------------------< insert_validate >----------------------------|
581 -- ----------------------------------------------------------------------------
582 Procedure insert_validate
584 ,p_rec in irc_cmc_shd.g_rec_type
585 ) is
586 --
587 l_proc varchar2(72) := g_package||'insert_validate';
588 --
589 Begin
590 hr_utility.set_location('Entering:'||l_proc, 5);
591 --
592 -- Call all supporting business operations
593 --
594 --
595 -- EDIT_HERE: As this table does not have a mandatory business_group_id
596 -- column, ensure client_info is populated by calling a suitable
597 -- ???_???_bus.set_security_group_id procedure, or add one of the following
598 -- comments:
599 -- "-- No business group context. HR_STANDARD_LOOKUPS used for validation."
600 -- "-- CLIENT_INFO not set. No lookup validation or joins to HR_LOOKUPS."
601 --
602 -- Validate Dependent Attributes
603 --
604 irc_cmc_bus.insert_update_validate(p_effective_date, p_rec);
605 irc_cmc_bus.chk_object_type(p_rec.object_type);
606 irc_cmc_bus.chk_object_id(p_rec.object_id, p_rec.object_type);
607 --
608 hr_utility.set_location(' Leaving:'||l_proc, 10);
609 End insert_validate;
610 --
611 -- ----------------------------------------------------------------------------
612 -- |---------------------------< update_validate >----------------------------|
613 -- ----------------------------------------------------------------------------
614 Procedure update_validate
615 (p_effective_date in date
616 ,p_rec in irc_cmc_shd.g_rec_type
617 ) is
618 --
619 l_proc varchar2(72) := g_package||'update_validate';
620 --
621 Begin
622 hr_utility.set_location('Entering:'||l_proc, 5);
623 --
624 -- Call all supporting business operations
625 --
626 --
627 -- EDIT_HERE: As this table does not have a mandatory business_group_id
628 -- column, ensure client_info is populated by calling a suitable
629 -- ???_???_bus.set_security_group_id procedure, or add one of the following
630 -- comments:
631 -- "-- No business group context. HR_STANDARD_LOOKUPS used for validation."
632 -- "-- CLIENT_INFO not set. No lookup validation or joins to HR_LOOKUPS."
633 --
634 -- Validate Dependent Attributes
635 --
636 chk_non_updateable_args
637 (p_effective_date => p_effective_date
638 ,p_rec => p_rec
639 );
640 --
641 irc_cmc_bus.insert_update_validate(p_effective_date, p_rec);
642 --
643 hr_utility.set_location(' Leaving:'||l_proc, 10);
644 End update_validate;
645 --
646 -- ----------------------------------------------------------------------------
647 -- |---------------------------< delete_validate >----------------------------|
648 -- ----------------------------------------------------------------------------
649 Procedure delete_validate
650 (p_rec in irc_cmc_shd.g_rec_type
651 ) is
652 --
653 l_proc varchar2(72) := g_package||'delete_validate';
654 --
655 Begin
656 hr_utility.set_location('Entering:'||l_proc, 5);
657 --
658 -- Call all supporting business operations
659 --
660 hr_utility.set_location(' Leaving:'||l_proc, 10);
661 End delete_validate;
662 --
663 end irc_cmc_bus;
[Home] [Help]