1 Package Body irc_ios_bus as
2 /* $Header: iriosrhi.pkb 120.3.12010000.2 2008/11/09 06:41:05 vmummidi ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' irc_ios_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_offer_status_history_id number default null;
15 --
16 -- ---------------------------------------------------------------------------
17 -- |---------------------< return_legislation_code >-------------------------|
18 -- ---------------------------------------------------------------------------
19 --
20 Function return_legislation_code
21 (p_offer_status_history_id in number
22 )
23 Return Varchar2 Is
24 --
25 -- Declare cursor
26 --
27 cursor csr_leg_code is
28 select pbg.legislation_code
29 from per_business_groups_perf pbg
30 , irc_offer_status_history ios
31 , per_all_vacancies vac
32 where ios.offer_status_history_id = p_offer_status_history_id
33 and pbg.business_group_id = vac.business_group_id;
34 --
35 -- Declare local variables
36 --
37 l_legislation_code varchar2(150);
38 l_proc varchar2(72) := g_package||'return_legislation_code';
39 --
40 Begin
41 --
42 hr_utility.set_location('Entering:'|| l_proc, 10);
43 --
44 -- Ensure that all the mandatory parameter are not null
45 --
46 hr_api.mandatory_arg_error
47 (p_api_name => l_proc
48 ,p_argument => 'offer_status_history_id'
49 ,p_argument_value => p_offer_status_history_id
50 );
51 --
52 if ( nvl(irc_ios_bus.g_offer_status_history_id, hr_api.g_number)
53 = p_offer_status_history_id) then
54 --
55 -- The legislation code has already been found with a previous
56 -- call to this function. Just return the value in the global
57 -- variable.
58 --
59 l_legislation_code := irc_ios_bus.g_legislation_code;
60 hr_utility.set_location(l_proc, 20);
61 else
62 --
63 -- The ID is different to the last call to this function
64 -- or this is the first call to this function.
65 --
66 open csr_leg_code;
67 fetch csr_leg_code into l_legislation_code;
68 --
69 if csr_leg_code%notfound then
70 --
71 -- The primary key is invalid therefore we must error
72 --
73 close csr_leg_code;
74 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
75 fnd_message.raise_error;
76 end if;
77 hr_utility.set_location(l_proc,30);
78 --
79 -- Set the global variables so the values are
80 -- available for the next call to this function.
81 --
82 close csr_leg_code;
83 irc_ios_bus.g_offer_status_history_id := p_offer_status_history_id;
84 irc_ios_bus.g_legislation_code := l_legislation_code;
85 end if;
86 hr_utility.set_location(' Leaving:'|| l_proc, 40);
87 return l_legislation_code;
88 end return_legislation_code;
89 --
90 -- ----------------------------------------------------------------------------
91 -- |-----------------------< chk_non_updateable_args >------------------------|
92 -- ----------------------------------------------------------------------------
93 -- {Start Of Comments}
94 --
95 -- Description:
96 -- This procedure is used to ensure that non updateable attributes have
97 -- not been updated. If an attribute has been updated an error is generated.
98 --
99 -- Pre Conditions:
100 -- g_old_rec has been populated with details of the values currently in
101 -- the database.
102 --
106 --
103 -- In Arguments:
104 -- p_rec has been populated with the updated values the user would like the
105 -- record set to.
107 -- Post Success:
108 -- Processing continues if all the non updateable attributes have not
109 -- changed.
110 --
111 -- Post Failure:
112 -- An application error is raised if any of the non updatable attributes
113 -- have been altered.
114 --
115 -- {End Of Comments}
116 -- ----------------------------------------------------------------------------
117 Procedure chk_non_updateable_args
118 (p_effective_date in date
119 ,p_rec in irc_ios_shd.g_rec_type
120 ) IS
121 --
122 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
123 --
124 Begin
125 --
126 -- Only proceed with the validation if a row exists for the current
127 -- record in the HR Schema.
128 --
129 IF NOT irc_ios_shd.api_updating
130 (p_offer_status_history_id => p_rec.offer_status_history_id
131 ,p_object_version_number => p_rec.object_version_number
132 ) THEN
133 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
134 fnd_message.set_token('PROCEDURE ', l_proc);
135 fnd_message.set_token('STEP ', '5');
136 fnd_message.raise_error;
137 END IF;
138 --
139 if irc_ios_shd.g_old_rec.offer_status_history_id <>
140 p_rec.offer_status_history_id
141 then
142 hr_api.argument_changed_error
143 (p_api_name => l_proc,
144 p_argument => 'offer_status_history_id',
145 p_base_table => irc_ios_shd.g_tab_name
146 );
147 end if;
148 --
149 if irc_ios_shd.g_old_rec.offer_id <> p_rec.offer_id
150 then
151 hr_api.argument_changed_error
152 (p_api_name => l_proc,
153 p_argument => 'offer_id',
154 p_base_table => irc_ios_shd.g_tab_name
155 );
156 end if;
157 --
158 if irc_ios_shd.g_old_rec.offer_status <> p_rec.offer_status
159 then
160 hr_api.argument_changed_error
161 (p_api_name => l_proc,
162 p_argument => 'offer_status',
163 p_base_table => irc_ios_shd.g_tab_name
164 );
165 end if;
166 --
167 if irc_ios_shd.g_old_rec.change_reason <> p_rec.change_reason
168 then
169 hr_api.argument_changed_error
170 (p_api_name => l_proc,
171 p_argument => 'change_reason',
172 p_base_table => irc_ios_shd.g_tab_name
173 );
174 end if;
175 --
176 if irc_ios_shd.g_old_rec.decline_reason <> p_rec.decline_reason
177 then
178 hr_api.argument_changed_error
179 (p_api_name => l_proc,
180 p_argument => 'decline_reason',
181 p_base_table => irc_ios_shd.g_tab_name
182 );
183 end if;
184 --
185 End chk_non_updateable_args;
186 --
187 -- ----------------------------------------------------------------------------
188 -- |-----------------------------< chk_offer_id >-----------------------------|
189 -- ----------------------------------------------------------------------------
190 -- {Start Of Comments}
191 --
192 -- Description:
193 -- This procedure is used to ensure that offer Id exists
194 -- in table irc_offers.
195 --
196 -- Pre Conditions:
197 -- offer Id should exist in the table.
198 --
199 -- In Arguments:
200 -- offer_id is passed by the user.
201 --
202 -- Post Success:
203 -- Processing continues if Offer Id exists.
204 --
205 -- Post Failure:
206 -- An error is raised if Offer Id does not exist.
207 --
208 -- {End Of Comments}
209 -- ----------------------------------------------------------------------------
210 Procedure chk_offer_id
211 (p_offer_id in irc_offer_status_history.offer_id%TYPE
212 ) is
213 --
214 l_proc varchar2(72) := g_package || 'chk_offer_id';
215 --
216 l_offer_id irc_offer_status_history.offer_id%TYPE;
217 --
218 cursor csr_offer is
219 select 1
220 from IRC_OFFERS
221 where offer_id = p_offer_id;
222 --
223 begin
224 --
225 hr_utility.set_location('Entering:'|| l_proc, 10);
226 hr_api.mandatory_arg_error
227 (p_api_name => l_proc
228 ,p_argument => 'OFFER_ID'
229 ,p_argument_value => p_offer_id
230 );
231 --
232 open csr_offer;
233 fetch csr_offer into l_offer_id;
234 hr_utility.set_location(l_proc,20);
235 if csr_offer%NOTFOUND then
236 close csr_offer;
237 fnd_message.set_name ('PER','IRC_412322_INVALID_OFFER_ID');
238 fnd_message.raise_error;
239 end if;
240 close csr_offer;
241 --
242 hr_utility.set_location(' Leaving:'|| l_proc, 30);
243 --
244 exception
245 when app_exception.application_exception then
246 if hr_multi_message.exception_add
247 (p_associated_column1=>
248 'IRC_OFFER_STATUS_HISTORY.OFFER_ID'
249 ) then
250 hr_utility.set_location(' Leaving:'|| l_proc, 40);
251 raise;
252 end if;
253 hr_utility.set_location(' Leaving:'|| l_proc, 50);
254 end chk_offer_id;
255 --
256 -- ----------------------------------------------------------------------------
257 -- |---------------------------< chk_offer_status >---------------------------|
258 -- ----------------------------------------------------------------------------
259 -- {Start Of Comments}
260 --
261 -- Description:
262 -- This procedure is used to ensure that
266 -- Pre Conditions:
263 -- a) Offer Status is not null in the table irc_offer_status_history
264 -- b) Offer Status is a valid value from IRC_OFFER_STATUSES lookup
265 --
267 -- offer status should exist in the table.
268 -- offer status is a valid value from the lookup
269 --
270 -- In Arguments:
271 -- offer_status is passed by the user.
272 --
273 -- Post Success:
274 -- Processing continues if Offer status exists.
275 --
276 -- Post Failure:
277 -- An error is raised if Offer status does not exist.
278 --
279 -- {End Of Comments}
280 -- ----------------------------------------------------------------------------
281 Procedure chk_offer_status
282 (p_offer_status in irc_offer_status_history.offer_status%TYPE,
283 p_effective_date in date
284 ) is
285 --
286 l_proc varchar2(72) := g_package || 'chk_offer_status';
287 --
288 l_offer_status irc_offer_status_history.offer_status%TYPE;
289
290 --
291 begin
292 --
293 hr_utility.set_location('Entering:'|| l_proc, 10);
294 --
295 -- Check mandatory parameters have been set
296 --
297 hr_api.mandatory_arg_error
298 (p_api_name => l_proc
299 ,p_argument => 'EFFECTIVE_DATE'
300 ,p_argument_value => p_effective_date
301 );
302 hr_api.mandatory_arg_error
303 (p_api_name => l_proc
304 ,p_argument => 'OFFER_STATUS'
305 ,p_argument_value => p_offer_status
306 );
307 --
308
309 --
310 hr_utility.set_location(l_proc,20);
311 if hr_api.not_exists_in_hr_lookups
312 (p_effective_date => p_effective_date,
313 p_lookup_type => 'IRC_OFFER_STATUSES',
314 p_lookup_code => p_offer_status
315 ) then
316
317 hr_utility.set_message(800, 'IRC_412323_INV_OFFER_STATUS');
318 hr_utility.raise_error;
319 end if;
320 --
321 hr_utility.set_location(' Leaving:' || l_proc, 30);
322
323 --
324 exception
325 when app_exception.application_exception then
326 if hr_multi_message.exception_add
327 (p_associated_column1=>
328 'IRC_OFFER_STATUS_HISTORY.OFFER_STATUS'
329 ) then
330 hr_utility.set_location(' Leaving:'|| l_proc, 40);
331 raise;
332 end if;
333 hr_utility.set_location(' Leaving:'|| l_proc, 50);
334 end chk_offer_status;
335 --
336 -- ----------------------------------------------------------------------------
337 -- |--------------------------< chk_change_reason >---------------------------|
338 -- ----------------------------------------------------------------------------
339 -- {Start Of Comments}
340 --
341 -- Description:
342 -- This procedure is used to ensure that
343 -- a) Change Reason must be a valid value from
344 -- IRC_OFFER_STATUS_CHANGE_REASON lookup
345 -- b) Should not get updated in irc_offer_status_history
346 --
347 -- Pre Conditions:
348 -- Change Reason should exist in the lookup.
349 --
350 -- In Arguments:
351 -- change_reason is passed by the user.
352 -- effective_date is passed by the user.
353 --
354 -- Post Success:
355 -- Processing continues if change reason exists in the lookup.
356 --
357 -- Post Failure:
358 -- An error is raised if change reason does not exist.
359 --
360 -- {End Of Comments}
361 -- ----------------------------------------------------------------------------
362 Procedure chk_change_reason
363 (p_change_reason in irc_offer_status_history.change_reason%TYPE,
364 p_effective_date in date
365 ) is
366 --
367 l_proc varchar2(72) := g_package || 'chk_change_reason';
368 --
369 l_change_reason irc_offer_status_history.change_reason%TYPE;
370
371 --
372 begin
373 --
374 hr_utility.set_location('Entering:'|| l_proc, 10);
375 --
376 if (p_change_reason is not null)
377 then
378 hr_utility.set_location(l_proc,20);
379 if hr_api.not_exists_in_hr_lookups
380 (p_effective_date => p_effective_date,
381 p_lookup_type => 'IRC_OFFER_STATUS_CHANGE_REASON',
382 p_lookup_code => p_change_reason
383 ) then
384
385 hr_utility.set_message(800, 'IRC_412347_INV_CHANGE_REASON');
386 hr_utility.raise_error;
387 end if;
388 end if;
389 --
390 hr_utility.set_location(' Leaving:' || l_proc, 30);
391
392 --
393 exception
394 when app_exception.application_exception then
395 if hr_multi_message.exception_add
396 (p_associated_column1=>
397 'IRC_OFFER_STATUS_HISTORY.CHANGE_REASON'
398 ) then
399 hr_utility.set_location(' Leaving:'|| l_proc, 40);
400 raise;
401 end if;
402 hr_utility.set_location(' Leaving:'|| l_proc, 50);
403 end chk_change_reason;
404 --
405 -- ----------------------------------------------------------------------------
406 -- |--------------------------< chk_decline_reason >--------------------------|
407 -- ----------------------------------------------------------------------------
408 -- {Start Of Comments}
409 --
410 -- Description:
411 -- This procedure is used to ensure that
412 -- a) decline Reason must be a valid value from
413 -- IRC_OFFER_STATUS_DECLINE_REASON lookup
414 -- b) Should not get updated in irc_offer_status_history
415 --
416 -- Pre Conditions:
417 -- decline Reason should exist in the lookup.
418 --
419 -- In Arguments:
420 -- decline_reason is passed by the user.
424 -- Processing continues if decline reason exists in the lookup.
421 -- effective_date is passed by the user.
422 --
423 -- Post Success:
425 --
426 -- Post Failure:
427 -- An error is raised if decline reason does not exist.
428 --
429 -- {End Of Comments}
430 -- ----------------------------------------------------------------------------
431 Procedure chk_decline_reason
432 (p_decline_reason in irc_offer_status_history.decline_reason%TYPE,
433 p_effective_date in date
434 ) is
435 --
436 l_proc varchar2(72) := g_package || 'chk_decline_reason';
437 --
438 l_decline_reason irc_offer_status_history.decline_reason%TYPE;
439
440 --
441 begin
442 --
443 hr_utility.set_location('Entering:'|| l_proc, 10);
444 --
445 -- Check mandatory parameters have been set
446 --
447 hr_api.mandatory_arg_error
448 (p_api_name => l_proc
449 ,p_argument => 'EFFECTIVE_DATE'
450 ,p_argument_value => p_effective_date
451 );
452 hr_api.mandatory_arg_error
453 (p_api_name => l_proc
454 ,p_argument => 'DECLINE_REASON'
455 ,p_argument_value => p_decline_reason
456 );
457 --
458
459 --
460 if (p_decline_reason is not null)
461 then
462 hr_utility.set_location(l_proc,20);
463 if hr_api.not_exists_in_hr_lookups
464 (p_effective_date => p_effective_date,
465 p_lookup_type => 'IRC_OFFER_DECLINE_REASON',
466 p_lookup_code => p_decline_reason
467 ) then
468
469 hr_utility.set_message(800, 'IRC_412324_INV_DECLINE_REASON');
470 hr_utility.raise_error;
471 end if;
472 end if;
473 --
474 hr_utility.set_location(' Leaving:' || l_proc, 30);
475
476 --
477 exception
478 when app_exception.application_exception then
479 if hr_multi_message.exception_add
480 (p_associated_column1=>
481 'IRC_OFFER_STATUS_HISTORY.DECLINE_REASON'
482 ) then
483 hr_utility.set_location(' Leaving:'|| l_proc, 40);
484 raise;
485 end if;
486 hr_utility.set_location(' Leaving:'|| l_proc, 50);
487 end chk_decline_reason;
488 --
489 -- ----------------------------------------------------------------------------
490 -- |--------------------------< chk_withdraw_reason >--------------------------|
491 -- ----------------------------------------------------------------------------
492 -- {Start Of Comments}
493 --
494 -- Description:
495 -- This procedure is used to ensure that
496 -- a) decline Reason must be a valid value from
497 -- IRC_OFFER_WITHDRAW_REASON lookup
498 -- b) Should not get updated in irc_offer_status_history
499 --
500 -- Pre Conditions:
501 -- decline Reason should exist in the lookup.
502 --
503 -- In Arguments:
504 -- decline_reason is passed by the user.
505 -- effective_date is passed by the user.
506 --
507 -- Post Success:
508 -- Processing continues if decline reason exists in the lookup.
509 --
510 -- Post Failure:
511 -- An error is raised if decline reason does not exist.
512 --
513 -- {End Of Comments}
514 -- ----------------------------------------------------------------------------
515 Procedure chk_withdraw_reason
516 (p_withdraw_reason in irc_offer_status_history.decline_reason%TYPE,
517 p_effective_date in date
518 ) is
519 --
520 l_proc varchar2(72) := g_package || 'chk_withdraw_reason';
521 --
522 l_decline_reason irc_offer_status_history.decline_reason%TYPE;
523
524 --
525 begin
526 --
527 hr_utility.set_location('Entering:'|| l_proc, 10);
528 --
529 -- Check mandatory parameters have been set
530 --
531 hr_api.mandatory_arg_error
532 (p_api_name => l_proc
533 ,p_argument => 'EFFECTIVE_DATE'
534 ,p_argument_value => p_effective_date
535 );
536 hr_api.mandatory_arg_error
537 (p_api_name => l_proc
538 ,p_argument => 'DECLINE_REASON'
539 ,p_argument_value => p_withdraw_reason
540 );
541 --
542
543 --
544 if (p_withdraw_reason is not null)
545 then
546 hr_utility.set_location(l_proc,20);
547 if hr_api.not_exists_in_hr_lookups
548 (p_effective_date => p_effective_date,
549 p_lookup_type => 'IRC_OFFER_WITHDRAWAL_REASON',
550 p_lookup_code => p_withdraw_reason
551 ) then
552
553 hr_utility.set_message(800, 'IRC_412551_INV_WITHDRAW_REASON');
554 hr_utility.raise_error;
555 end if;
556 end if;
557 --
558 hr_utility.set_location(' Leaving:' || l_proc, 30);
559
560 --
561 exception
562 when app_exception.application_exception then
563 if hr_multi_message.exception_add
564 (p_associated_column1=>
565 'IRC_OFFER_STATUS_HISTORY.DECLINE_REASON'
566 ) then
567 hr_utility.set_location(' Leaving:'|| l_proc, 40);
568 raise;
569 end if;
570 hr_utility.set_location(' Leaving:'|| l_proc, 50);
571 end chk_withdraw_reason;
572 --
573 -- ----------------------------------------------------------------------------
574 -- |---------------------------< insert_validate >----------------------------|
575 -- ----------------------------------------------------------------------------
576 Procedure insert_validate
577 (p_effective_date in date
578 ,p_rec in irc_ios_shd.g_rec_type
579 ) is
580 --
581 l_proc varchar2(72) := g_package||'insert_validate';
582 --
583 Begin
584 hr_utility.set_location(' Leaving:'||l_proc, 10);
585 --
586 -- Call all supporting business operations
587 --
588
589 --
590 hr_utility.set_location(l_proc, 20);
591 --
592 chk_offer_id
593 (p_offer_id => p_rec.offer_id
594 );
595 --
596 hr_utility.set_location(l_proc, 30);
597 --
598 chk_offer_status
599 (p_offer_status => p_rec.offer_status,
600 p_effective_date => p_effective_date
601 );
602 --
603 hr_utility.set_location(l_proc, 40);
604 --
605 if (p_rec.change_reason is not null) then
606 chk_change_reason
607 (p_change_reason => p_rec.change_reason,
608 p_effective_date => p_effective_date
609 );
610 end if;
611 --
612 hr_utility.set_location(l_proc, 50);
613 --
614 if (p_rec.decline_reason is not null) then
615 if(p_rec.change_reason='MGR_WITHDRAW') then
616 chk_withdraw_reason
617 (p_withdraw_reason => p_rec.decline_reason,
618 p_effective_date => p_effective_date
619 );
620 else
621 chk_decline_reason
622 (p_decline_reason => p_rec.decline_reason,
623 p_effective_date => p_effective_date
624 );
625 end if;
626 end if;
627 --
628 hr_utility.set_location(l_proc, 60);
629 --
630 End insert_validate;
631 --
632 -- ----------------------------------------------------------------------------
633 -- |---------------------------< update_validate >----------------------------|
634 -- ----------------------------------------------------------------------------
635 Procedure update_validate
636 (p_effective_date in date
637 ,p_rec in irc_ios_shd.g_rec_type
638 ) is
639 --
640 l_proc varchar2(72) := g_package||'update_validate';
641 --
642 Begin
643 hr_utility.set_location('Entering:'||l_proc, 10);
644 --
645 -- Call all supporting business operations
646 --
647 chk_non_updateable_args
648 (p_effective_date => p_effective_date
649 ,p_rec => p_rec
650 );
651 hr_utility.set_location(l_proc, 20);
652 --
653 if (p_rec.change_reason is not null) then
654 chk_change_reason
655 (p_change_reason => p_rec.change_reason,
656 p_effective_date => p_effective_date
657 );
658 end if;
659 --
660 hr_utility.set_location(l_proc, 30);
661 --
662 if (p_rec.decline_reason is not null) then
663 if(p_rec.change_reason='MGR_WITHDRAW') then
664 chk_withdraw_reason
665 (p_withdraw_reason => p_rec.decline_reason,
666 p_effective_date => p_effective_date
667 );
668 else
669 chk_decline_reason
670 (p_decline_reason => p_rec.decline_reason,
671 p_effective_date => p_effective_date
672 );
673 end if;
674 end if;
675 --
676 hr_utility.set_location(' Leaving:'||l_proc, 40);
677 End update_validate;
678 --
679 -- ----------------------------------------------------------------------------
680 -- |---------------------------< delete_validate >----------------------------|
681 -- ----------------------------------------------------------------------------
682 Procedure delete_validate
683 (p_rec in irc_ios_shd.g_rec_type
684 ) is
685 --
686 l_proc varchar2(72) := g_package||'delete_validate';
687 --
688 Begin
689 hr_utility.set_location('Entering:'||l_proc, 5);
690 --
691 -- Call all supporting business operations
692 --
693 hr_utility.set_location(' Leaving:'||l_proc, 10);
694 End delete_validate;
695 --
696 end irc_ios_bus;
[Home] [Help]