[Home] [Help]
PACKAGE BODY: APPS.PQH_STS_BUS
Source
1 Package Body pqh_sts_bus as
2 /* $Header: pqstsrhi.pkb 120.0 2005/05/29 02:43 appldev noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pqh_sts_bus.'; -- Global package name
9 g_debug boolean := hr_utility.debug_enabled;
10
11 --
12 -- The following two global variables are only to be
13 -- used by the return_legislation_code function.
14 --
15 g_legislation_code varchar2(150) default null;
16 g_statutory_situation_id number default null;
17 --
18 -- ---------------------------------------------------------------------------
19 -- |----------------------< set_security_group_id >--------------------------|
20 -- ---------------------------------------------------------------------------
21 --
22 Procedure set_security_group_id
23 (p_statutory_situation_id in number
24 ,p_associated_column1 in varchar2 default null
25 ) is
26 --
27 -- Declare cursor
28 --
29 cursor csr_sec_grp is
30 select pbg.security_group_id,
31 pbg.legislation_code
32 from per_business_groups_perf pbg
33 , pqh_fr_stat_situations sts
34 where sts.statutory_situation_id = p_statutory_situation_id
35 and pbg.business_group_id = sts.business_group_id;
36 --
37 -- Declare local variables
38 --
39 l_security_group_id number;
40 l_proc varchar2(72) := g_package||'set_security_group_id';
41 l_legislation_code varchar2(150);
42 --
43 begin
44 --
45 if g_debug then
46 --
47 hr_utility.set_location('Entering:'|| l_proc, 10);
48 --
49 end if;
50
51 --
52 -- Ensure that all the mandatory parameter are not null
53 --
54 hr_api.mandatory_arg_error
55 (p_api_name => l_proc
56 ,p_argument => 'statutory_situation_id'
57 ,p_argument_value => p_statutory_situation_id
58 );
59 --
60 open csr_sec_grp;
61 fetch csr_sec_grp into l_security_group_id
62 , l_legislation_code;
63 --
64 if csr_sec_grp%notfound then
65 --
66 close csr_sec_grp;
67 --
68 -- The primary key is invalid therefore we must error
69 --
70 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
71 hr_multi_message.add
72 (p_associated_column1
73 => nvl(p_associated_column1,'STATUTORY_SITUATION_ID')
74 );
75 --
76 else
77 close csr_sec_grp;
78 --
79 -- Set the security_group_id in CLIENT_INFO
80 --
81 hr_api.set_security_group_id
82 (p_security_group_id => l_security_group_id
83 );
84 --
85 -- Set the sessions legislation context in HR_SESSION_DATA
86 --
87 hr_api.set_legislation_context(l_legislation_code);
88 end if;
89 --
90 if g_debug then
91 --
92 hr_utility.set_location(' Leaving:'|| l_proc, 20);
93 --
94 end if;
95 --
96 end set_security_group_id;
97 --
98 -- ---------------------------------------------------------------------------
99 -- |---------------------< return_legislation_code >-------------------------|
100 -- ---------------------------------------------------------------------------
101 --
102 Function return_legislation_code
103 (p_statutory_situation_id in number
104 )
105 Return Varchar2 Is
106 --
107 -- Declare cursor
108 --
109 cursor csr_leg_code is
110 select pbg.legislation_code
111 from per_business_groups_perf pbg
112 , pqh_fr_stat_situations sts
113 where sts.statutory_situation_id = p_statutory_situation_id
114 and pbg.business_group_id = sts.business_group_id;
115 --
116 -- Declare local variables
117 --
118 l_legislation_code varchar2(150);
119 l_proc varchar2(72) := g_package||'return_legislation_code';
120 --
121 Begin
122 --
123 if g_debug then
124 --
125 hr_utility.set_location('Entering:'|| l_proc, 10);
126 --
127 end if;
128 --
129 -- Ensure that all the mandatory parameter are not null
130 --
131 hr_api.mandatory_arg_error
132 (p_api_name => l_proc
133 ,p_argument => 'statutory_situation_id'
134 ,p_argument_value => p_statutory_situation_id
135 );
136 --
137 if ( nvl(pqh_sts_bus.g_statutory_situation_id, hr_api.g_number)
138 = p_statutory_situation_id) then
139 --
140 -- The legislation code has already been found with a previous
141 -- call to this function. Just return the value in the global
142 -- variable.
143 --
144 l_legislation_code := pqh_sts_bus.g_legislation_code;
145
146 if g_debug then
147 --
148 hr_utility.set_location(l_proc, 20);
149 --
150 end if;
151 else
152 --
153 -- The ID is different to the last call to this function
154 -- or this is the first call to this function.
155 --
156 open csr_leg_code;
157 fetch csr_leg_code into l_legislation_code;
158 --
159 if csr_leg_code%notfound then
160 --
161 -- The primary key is invalid therefore we must error
162 --
163 close csr_leg_code;
164 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
165 fnd_message.raise_error;
166 end if;
167
168 if g_debug then
169 --
170 hr_utility.set_location(l_proc,30);
171 --
172 end if;
173 --
174 -- Set the global variables so the values are
175 -- available for the next call to this function.
176 --
177 close csr_leg_code;
178 pqh_sts_bus.g_statutory_situation_id := p_statutory_situation_id;
179 pqh_sts_bus.g_legislation_code := l_legislation_code;
180 end if;
181
182 if g_debug then
183 --
184 hr_utility.set_location(' Leaving:'|| l_proc, 40);
185 --
186 end if;
187
188 return l_legislation_code;
189 end return_legislation_code;
190 --
191 -- ----------------------------------------------------------------------------
192 -- |-----------------------< chk_non_updateable_args >------------------------|
193 -- ----------------------------------------------------------------------------
194 -- {Start Of Comments}
195 --
196 -- Description:
197 -- This procedure is used to ensure that non updateable attributes have
198 -- not been updated. If an attribute has been updated an error is generated.
199 --
200 -- Pre Conditions:
201 -- g_old_rec has been populated with details of the values currently in
202 -- the database.
203 --
204 -- In Arguments:
205 -- p_rec has been populated with the updated values the user would like the
206 -- record set to.
207 --
208 -- Post Success:
209 -- Processing continues if all the non updateable attributes have not
210 -- changed.
211 --
212 -- Post Failure:
213 -- An application error is raised if any of the non updatable attributes
214 -- have been altered.
215 --
216 -- {End Of Comments}
217 -- ----------------------------------------------------------------------------
218 Procedure chk_non_updateable_args
219 (p_effective_date in date
220 ,p_rec in pqh_sts_shd.g_rec_type
221 ) IS
222 --
223 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
224 l_status varchar2(10) := null;
225 --
226 Begin
227 --
228 -- Only proceed with the validation if a row exists for the current
229 -- record in the HR Schema.
230 --
231 IF NOT pqh_sts_shd.api_updating
232 (p_statutory_situation_id => p_rec.statutory_situation_id
233 ,p_object_version_number => p_rec.object_version_number
234 ) THEN
235 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
236 fnd_message.set_token('PROCEDURE ', l_proc);
237 fnd_message.set_token('STEP ', '5');
238 fnd_message.raise_error;
239 END IF;
240
241 if nvl(p_rec.business_group_id, hr_api.g_number) <>
242 nvl(pqh_sts_shd.g_old_rec.business_group_id
243 ,hr_api.g_number
244 ) then
245 hr_api.argument_changed_error
246 (p_api_name => l_proc
247 ,p_argument => 'BUSINESS_GROUP_ID'
248 ,p_base_table => pqh_sts_shd.g_tab_nam
249 );
250 end if;
251
252 -- Type_Of_PS
253
254 if nvl(p_rec.type_of_ps, hr_api.g_varchar2) <>
255 nvl(pqh_sts_shd.g_old_rec.type_of_ps
256 ,hr_api.g_varchar2
257 ) then
258 hr_api.argument_changed_error
259 (p_api_name => l_proc
260 ,p_argument => 'TYPE_OF_PS'
261 ,p_base_table => pqh_sts_shd.g_tab_nam
262 );
263 end if;
264
265 -- Situation Type
266
267 if nvl(p_rec.situation_type, hr_api.g_varchar2) <>
268 nvl(pqh_sts_shd.g_old_rec.situation_type
269 ,hr_api.g_varchar2
270 ) then
271 hr_api.argument_changed_error
272 (p_api_name => l_proc
273 ,p_argument => 'SITUATION_TYPE'
274 ,p_base_table => pqh_sts_shd.g_tab_nam
275 );
276 end if;
277
278 -- Sub Type
279 if nvl(p_rec.sub_type, hr_api.g_varchar2) <>
280 nvl(pqh_sts_shd.g_old_rec.sub_type
281 ,hr_api.g_varchar2
282 ) then
283 hr_api.argument_changed_error
284 (p_api_name => l_proc
285 ,p_argument => 'SUB_TYPE'
286 ,p_base_table => pqh_sts_shd.g_tab_nam
287 );
288 end if;
289
290 -- Source
291 if nvl(p_rec.source, hr_api.g_varchar2) <>
292 nvl(pqh_sts_shd.g_old_rec.source
293 ,hr_api.g_varchar2
294 ) then
295 hr_api.argument_changed_error
296 (p_api_name => l_proc
297 ,p_argument => 'SOURCE'
298 ,p_base_table => pqh_sts_shd.g_tab_nam
299 );
300 end if;
301
302 -- Location
303 if nvl(p_rec.location, hr_api.g_varchar2) <>
304 nvl(pqh_sts_shd.g_old_rec.location
305 ,hr_api.g_varchar2
306 ) then
307 hr_api.argument_changed_error
308 (p_api_name => l_proc
309 ,p_argument => 'LOCATION'
310 ,p_base_table => pqh_sts_shd.g_tab_nam
311 );
312 end if;
313
314 -- Reason
315 if nvl(p_rec.reason, hr_api.g_varchar2) <>
316 nvl(pqh_sts_shd.g_old_rec.reason
317 ,hr_api.g_varchar2
318 ) then
319 hr_api.argument_changed_error
320 (p_api_name => l_proc
321 ,p_argument => 'REASON'
322 ,p_base_table => pqh_sts_shd.g_tab_nam
323 );
324 end if;
325
326 --
327 End chk_non_updateable_args;
328 --
329
330 procedure chk_renewable(p_max_no_of_renewals in number,
331 p_max_duration_per_renewal in number,
332 p_renewable_allowed in varchar2)
333 is
334 --
335 l_proc varchar2(72) := g_package||'chk_renewable';
336 l_value varchar2(100);
337 --
338 begin
339 --
340 if ( p_max_no_of_renewals is not null or
341 p_max_duration_per_renewal is not null ) then
342
343 if (p_renewable_allowed = 'N') then
344 --
345
346 fnd_message.set_name('PQH','PQH_FR_VALUE_NOT_ALLOWED');
347 fnd_message.set_token('ATTRIBUTE','MAX_NO_OF_RENEWALS');
348
349 hr_multi_message.add(p_associated_column1=> 'MAX_NO_OF_RENEWALS',
350 p_associated_column2=> 'MAX_DURATION_PER_RENEWAL');
351 end if;
352 end if;
353
354 end chk_renewable;
355 --
356 procedure chk_duration_limits(p_rec in pqh_sts_shd.g_rec_type)
357 is
358 --
359 l_proc varchar2(72) := g_package||'chk_duration_limits';
360 l_value varchar2(100);
361 --
362 begin
363 if ( p_rec.FIRST_PERIOD_MAX_DURATION is not null or
364 p_rec.MIN_DURATION_PER_REQUEST is not null or
365 p_rec.MAX_DURATION_PER_REQUEST is not null or
366 p_rec.MAX_DURATION_WHOLE_CAREER is not null or
367 p_rec.RENEWABLE_ALLOWED = 'Y' or
368 p_rec.MAX_NO_OF_RENEWALS is not null or
369 p_rec.MAX_DURATION_PER_RENEWAL is not null or
370 p_rec.MAX_TOT_CONTINUOUS_DURATION is not null ) then
371 --
372 if (p_rec.FREQUENCY is null ) then
373 --
374 fnd_message.set_name('PQH','PQH_FR_FREQUENCY_MUST_SELECT');
375
376 hr_multi_message.add(p_associated_column1=> 'FREQUENCY_NAME');
377 end if;
378 --
379 end if;
380
381 end chk_duration_limits;
382 --
383 --
384 procedure chk_frequency(p_frequency varchar2)
385 is
386 --
387 l_proc varchar2(72) := g_package||'chk_frequency';
388 l_value varchar2(100);
389 --
390 Cursor csr_frequency IS
391 Select null
392 from hr_lookups
393 Where Lookup_type='PROC_PERIOD_TYPE'
394 and ENABLED_FLAG='Y'
395 and lookup_code = p_frequency;
396 --
397
398 begin
399
400 if (p_frequency is not null) then
401 --
402 Open csr_frequency;
403 --
404 Fetch csr_frequency into l_value;
405
406 if csr_frequency%NOTFOUND then
407 --
408 fnd_message.set_name('PQH','PQH_FR_INVALID_VALUE');
409 fnd_message.set_token('ATTRIBUTE',hr_general.decode_lookup('FR_PQH_FORM_PROMPTS','FREQUENCY'));
410
411 hr_multi_message.add(p_associated_column1=> 'FREQUENCY_NAME');
412 end if;
413 --
414 Close csr_frequency;
415 --
416 end if;
417
418 end chk_frequency;
419 --
420 procedure chk_remuneration_paid(p_pay_share in number , p_pay_periods number ,
421 p_remuneration_paid varchar2 )
422 is
423 --
424 l_proc varchar2(72) := g_package||'chk_remuneration_paid';
425 l_value varchar2(100);
426 --
427
428 --
429
430 begin
431
432 if ( p_pay_share is not null or
433 p_pay_periods is not null) then
434 --
435 if (p_remuneration_paid = 'N') then
436 --
437 fnd_message.set_name('PQH','PQH_FR_VALUE_NOT_ALLOWED');
438 fnd_message.set_token('ATTRIBUTE','PAY_SHARE');
439
440 hr_multi_message.add(p_associated_column1=> 'REMUNERATION_PAID');
441 end if;
442 --
443 end if;
444
445 end chk_remuneration_paid;
446 --
447 procedure chk_date(p_date_from in date, p_date_to in date)
448 is
449 --
450 l_proc varchar2(72) := g_package||'chk_date_check';
451 l_value varchar2(100);
452 --
453
454 --
455
456 begin
457 If (p_date_to is not null) then
458 --
459 if (trunc(p_date_from) > trunc(p_date_to)) then
460 --
461 fnd_message.set_name('PQH','PQH_FR_INVALID_VALUE_DATE_FROM');
462
463 hr_multi_message.add(p_associated_column1=> 'DATE_TO');
464
465 --
466 end if;
467 --
468 End If;
469
470 end chk_date;
471 --
472 procedure chk_reason(p_reason in varchar2, p_situation_type in varchar2)
473 is
474 --
475 l_proc varchar2(72) := g_package||'chk_reason';
476 l_value varchar2(100);
477 --
478
479 Cursor csr_reason IS
480 Select null
481 from hr_lookups
482 where lookup_type ='FR_PQH_STAT_SIT_REASON'
483 and lookup_code like p_situation_type || '%'
484 and lookup_code = p_reason
485 and enabled_flag = 'Y';
486
487 --
488
489 begin
490
491 if (p_reason is not null) then
492 --
493 Open csr_reason;
494 --
495 Fetch csr_reason into l_value;
496
497 if csr_reason%NOTFOUND then
498 --
499 fnd_message.set_name('PQH','PQH_FR_INVALID_VALUE');
500 fnd_message.set_token('ATTRIBUTE',hr_general.decode_lookup('FR_PQH_FORM_PROMPTS','REASON'));
501
502 hr_multi_message.add
503 (p_associated_column1=> 'REASON');
504 end if;
505 --
506 Close csr_reason;
507 --
508 end if;
509
510 end chk_reason;
511 --
512 procedure chk_location(p_location in varchar2)
513 is
514 --
515 l_proc varchar2(72) := g_package||'chk_location';
516 l_value varchar2(100);
517 --
518 Cursor csr_location IS
519 Select null
520 from hr_lookups
521 Where Lookup_type='FR_PQH_STAT_SIT_PLCMENT'
522 and ENABLED_FLAG='Y'
523 and lookup_code = p_location;
524
525 --
526
527 begin
528
529 If (p_location is not null) then
530 --
531 Open csr_location;
532 --
533 Fetch csr_location into l_value;
534
535 if csr_location%NOTFOUND then
536 --
537 fnd_message.set_name('PQH','PQH_FR_INVALID_VALUE');
538 fnd_message.set_token('ATTRIBUTE',hr_general.decode_lookup('FR_PQH_FORM_PROMPTS','LOCATION'));
539
540 hr_multi_message.add
541 (p_associated_column1
542 => 'LOCATION');
543 end if;
544 --
545 Close csr_location;
546 --
547 End If;
548
549 end chk_location;
550 --
551
552 --
553 procedure chk_source(p_source in varchar2)
554 is
555 --
556 l_proc varchar2(72) := g_package||'chk_source';
557 l_value varchar2(100);
558 --
559 Cursor csr_source IS
560 Select null
561 from hr_lookups
562 Where Lookup_type='FR_PQH_STAT_SIT_SOURCE'
563 and ENABLED_FLAG='Y'
564 and lookup_code = p_source;
565
566 --
567
568 begin
569
570 IF (p_source is not null) then
571 --
572 Open csr_source;
573 --
574 Fetch csr_source into l_value;
575
576 if csr_source%NOTFOUND then
577 --
578 fnd_message.set_name('PQH','PQH_FR_INVALID_VALUE');
579 fnd_message.set_token('ATTRIBUTE',hr_general.decode_lookup('FR_PQH_FORM_PROMPTS','SOURCE'));
580
581 hr_multi_message.add
582 (p_associated_column1
583 => 'SOURCE');
584 end if;
585 --
586 Close csr_source;
587 --
588 END If;
589
590 end chk_source;
591 --
592 --
593 procedure chk_sub_type(p_sub_type in varchar2, p_situation_type in varchar2)
594 is
595 --
596 l_proc varchar2(72) := g_package||'chk_sub_type';
597 l_value varchar2(100);
598 --
599 Cursor csr_sub_type IS
600 Select null
601 from hr_lookups
602 where lookup_type ='FR_PQH_STAT_SIT_SUB_TYPE'
603 and lookup_code like p_situation_type || '%'
604 and lookup_code = p_sub_type
605 and enabled_flag = 'Y';
606 --
607
608 begin
609
610
611 if (p_sub_type is not null) then
612 --
613 Open csr_sub_type;
614
615 Fetch csr_sub_type into l_value ;
616
617 If csr_sub_type%NOTFOUND then
618 --
619 fnd_message.set_name('PQH','PQH_FR_INVALID_VALUE');
620 fnd_message.set_token('ATTRIBUTE',hr_general.decode_lookup('FR_PQH_FORM_PROMPTS','SUB_TYPE'));
621 hr_multi_message.add
622 (p_associated_column1
623 => 'SUB_TYPE_NAME');
624 --
625 End If;
626 --
627 End if;
628
629
630 end chk_sub_type;
631 --
632 --
633 procedure chk_pay_share (p_pay_share in number , p_remuneration_paid in varchar2)
634 is
635 --
636 l_proc varchar2(72) := g_package||'chk_pay_share';
637 --
638 begin
639
640 if (p_pay_share < 0 or p_pay_share >100) then
641 --
642 fnd_message.set_name('PQH','PQH_FR_INVALID_VALUE');
643 fnd_message.set_token('ATTRIBUTE',hr_general.decode_lookup('FR_PQH_FORM_PROMPTS','PAY_SHARE'));
644 hr_multi_message.add
645 (p_associated_column1
646 => 'PAY_SHARE');
647 --
648 elsif (p_pay_share >0 and p_remuneration_paid = 'N') then
649 --
650 fnd_message.set_name('PQH','PQH_FR_STAT_CHECK_REMUNERATION');
651 fnd_message.set_token('ATTRIBUTE',hr_general.decode_lookup('FR_PQH_FORM_PROMPTS','PAY_SHARE'));
652 hr_multi_message.add
653 (p_associated_column1=> 'REMUNERATION_PAID');
654
655 hr_multi_message.end_validation_set;
656 --
657 end if;
658
659
660 end chk_pay_share;
661 --
662 -- Pay Period Check
663 --
664 procedure chk_pay_periods (p_pay_periods in number , p_remuneration_paid in varchar2)
665 is
666 --
667 l_proc varchar2(72) := g_package||'chk_pay_periods';
668 --
669 begin
670
671 if (p_pay_periods < 0) then
672 --
673 fnd_message.set_name('PQH','PQH_FR_INVALID_VALUE');
674 fnd_message.set_token('ATTRIBUTE',hr_general.decode_lookup('FR_PQH_FORM_PROMPTS','PAY_PERIODS'));
675 hr_multi_message.add
676 (p_associated_column1
677 => 'PAY_PERIODS');
678 --
679 elsif (p_pay_periods >0 and p_remuneration_paid = 'N') then
680 --
681 fnd_message.set_name('PQH','PQH_FR_STAT_CHECK_REMUNERATION');
682 fnd_message.set_token('ATTRIBUTE',hr_general.decode_lookup('FR_PQH_FORM_PROMPTS','PAY_PERIODS'));
683 hr_multi_message.add
684 (p_associated_column1=> 'REMUNERATION_PAID');
685 --
686 end if;
687 --
688 end chk_pay_periods;
689 --
690 -- first period max duration Check
691 --
692 procedure chk_first_period_max_duration (p_first_period_max_duration in number,
693 p_frequency in varchar2)
694 is
695 --
696 l_proc varchar2(72) := g_package||'chk_first_period_max_duration';
697 --
698 begin
699
700 if (p_first_period_max_duration < 0) then
701 --
702 fnd_message.set_name('PQH','PQH_FR_INVALID_VALUE');
703 fnd_message.set_token('ATTRIBUTE',hr_general.decode_lookup('FR_PQH_FORM_PROMPTS','FIRST_PERIOD_MAX_DURATION'));
704 hr_multi_message.add
705 (p_associated_column1
706 => 'FIRST_PERIOD_MAX_DURATION');
707 --
708 end if;
709 --
710 end chk_first_period_max_duration;
711 --
712
713 procedure chk_min_duration_per_rqst (p_min_duration_per_request in number,
714 p_frequency in varchar2)
715 is
716 --
717 l_proc varchar2(72) := g_package||'chk_min_duration_per_request';
718 --
719 begin
720
721 if (p_min_duration_per_request < 0) then
722 --
723 fnd_message.set_name('PQH','PQH_FR_INVALID_VALUE');
724 fnd_message.set_token('ATTRIBUTE',hr_general.decode_lookup('FR_PQH_FORM_PROMPTS','MIN_DURATION_PER_REQUEST'));
725 hr_multi_message.add
726 (p_associated_column1
727 => 'MIN_DURATION_PER_REQUEST');
728 --
729 end if;
730 --
731 end chk_min_duration_per_rqst;
732 --
733
734 procedure chk_max_duration_per_request (p_max_duration_per_request in number,
735 p_frequency in varchar2)
736 is
737 --
738 l_proc varchar2(72) := g_package||'chk_max_duration_per_request';
739 --
740 begin
741
742 if (p_max_duration_per_request < 0) then
743 --
744 fnd_message.set_name('PQH','PQH_FR_INVALID_VALUE');
745 fnd_message.set_token('ATTRIBUTE',hr_general.decode_lookup('FR_PQH_FORM_PROMPTS','MAX_DURATION_PER_REQUEST'));
746 hr_multi_message.add
747 (p_associated_column1
748 => 'MAX_DURATION_PER_REQUEST');
749 --
750 end if;
751 --
752 end chk_max_duration_per_request;
753 --
754
755 procedure chk_max_duration_whole_crr (p_max_duration_whole_career in number,
756 p_frequency in varchar2)
757 is
758 --
759 l_proc varchar2(72) := g_package||'chk_max_duration_whole_crr';
760 --
761 begin
762
763 if (p_max_duration_whole_career < 0) then
764 --
765 fnd_message.set_name('PQH','PQH_FR_INVALID_VALUE');
766 fnd_message.set_token('ATTRIBUTE',hr_general.decode_lookup('FR_PQH_FORM_PROMPTS','MAX_DURATION_WHOLE_CAREER'));
767 hr_multi_message.add
768 (p_associated_column1
769 => 'MAX_DURATION_WHOLE_CAREER');
770 --
771 end if;
772 --
773 end chk_max_duration_whole_crr;
774 --
775
776
777 procedure chk_max_no_of_renewals (p_max_no_of_renewals in number, p_renewable_allowed in varchar2,
778 p_frequency in varchar2)
779 is
780 --
781 l_proc varchar2(72) := g_package||'chk_max_no_of_renewals';
782 --
783 begin
784
785 if (p_max_no_of_renewals < 0) then
786 --
787 fnd_message.set_name('PQH','PQH_FR_INVALID_VALUE');
788 fnd_message.set_token('ATTRIBUTE',hr_general.decode_lookup('FR_PQH_FORM_PROMPTS','MAX_NO_OF_RENEWALS'));
789 hr_multi_message.add
790 (p_associated_column1
791 => 'MAX_NO_OF_RENEWALS');
792 --
793 elsif (p_max_no_of_renewals > 0 and p_renewable_allowed = 'N' ) then
794 --
795 --
796 fnd_message.set_name('PQH','PQH_FR_STAT_RENEWABLE_ALLOWED');
797 fnd_message.set_token('ATTRIBUTE',hr_general.decode_lookup('FR_PQH_FORM_PROMPTS','MAX_NO_OF_RENEWALS'));
798 hr_multi_message.add
799 (p_associated_column1
800 => 'RENEWABLE_ALLOWED');
801
802 hr_multi_message.end_validation_set;
803 --
804 end if;
805
806 --
807 end chk_max_no_of_renewals;
808 --
809
810
811 procedure chk_max_duration_per_renewal (p_max_duration_per_renewal in number,
812 p_renewable_allowed in varchar2, p_frequency in varchar2)
813 is
814 --
815 l_proc varchar2(72) := g_package||'chk_max_duration_per_renewal';
816 --
817 begin
818
819
820 if (p_max_duration_per_renewal < 0) then
821 --
822 fnd_message.set_name('PQH','PQH_FR_INVALID_VALUE');
823 fnd_message.set_token('ATTRIBUTE',hr_general.decode_lookup('FR_PQH_FORM_PROMPTS','MAX_DURATION_PER_RENEWAL'));
824 hr_multi_message.add
825 (p_associated_column1
826 => 'MAX_DURATION_PER_RENEWAL');
827 --
828 elsif (p_max_duration_per_renewal > 0 and p_renewable_allowed = 'N' ) then
829 --
830 --
831 fnd_message.set_name('PQH','PQH_FR_STAT_RENEWABLE_ALLOWED');
832 fnd_message.set_token('ATTRIBUTE',hr_general.decode_lookup('FR_PQH_FORM_PROMPTS','MAX_DURATION_PER_RENEWAL'));
833 hr_multi_message.add
834 (p_associated_column1
835 => 'RENEWABLE_ALLOWED');
836 --
837 end if;
838
839 --
840 end chk_max_duration_per_renewal;
841
842
843 procedure chk_max_tot_continuous_dur (p_max_tot_continuous_duration in number,p_frequency in varchar2)
844 is
845 --
846 l_proc varchar2(72) := g_package||'chk_max_tot_continuous_dur';
847 --
848 begin
849
850 if (p_max_tot_continuous_duration is not null
851 and p_max_tot_continuous_duration < 0 ) then
852 --
853 fnd_message.set_name('PQH','PQH_FR_INVALID_VALUE');
854 fnd_message.set_token('ATTRIBUTE',hr_general.decode_lookup('FR_PQH_FORM_PROMPTS','MAX_TOT_CONTINUOUS_DURATION'));
855 hr_multi_message.add
856 (p_associated_column1
857 => ' MAX_TOT_CONTINUOUS_DURATION');
858 --
859 end if;
860 --
861 end chk_max_tot_continuous_dur;
862
863 ---
864 -- Default check
865 ---
866 procedure chk_default(p_rec in pqh_sts_shd.g_rec_type)
867 is
868 --
869 l_proc varchar2(72) := g_package||'chk_default';
870 l_value varchar2(1000);
871 --
872 -- Situation Type + Type of PS + BG
873 Cursor csr_chk_default IS
874 Select null
875 from pqh_fr_stat_situations
876 where
877 type_of_ps = p_rec.type_Of_ps
878 and situation_type = p_rec.situation_type
879 and statutory_situation_id <> nvl(p_rec.statutory_situation_id,-1)
880 and business_group_id = p_rec.business_group_id
881 and default_flag = 'Y';
885 --
882 --
883 Begin
884 if g_debug then
886 hr_utility.set_location('Entering:'||l_proc, 7);
887 --
888 end if;
889 if(p_rec.is_default = 'Y') then
890
891 Open csr_chk_default;
892 --
893 Fetch csr_chk_default into l_value;
894
895 if csr_chk_default%FOUND then
896 --
897 fnd_message.set_name('PQH','PQH_FR_STAT_DUP_DEFAULT');
898 hr_multi_message.add
899 (p_associated_column1 => 'DEFAULT_FLAG');
900
901 end if;
902 --
903 Close csr_chk_default;
904 end if;
905 --
906 End chk_default;
907
908 --
909 -- Type of Public Sector Check
910 procedure chk_type_of_ps(p_type_of_ps in varchar2)
911 is
912 --
913 l_proc varchar2(72) := g_package||'chk_type_of_ps';
914 l_value varchar2(1000);
915 --
916 Cursor csr_type_of_ps IS
917 Select null
918 From per_shared_types_vl
919 Where shared_type_id = to_number(p_type_of_ps);
920
921 --
922 Begin
923 if g_debug then
924 --
925 hr_utility.set_location('Entering:'||l_proc, 7);
926 --
927 end if;
928
929 Open csr_type_of_ps;
930 --
931 Fetch csr_type_of_ps into l_value;
932
933 if csr_type_of_ps%NOTFOUND then
934 --
935 fnd_message.set_name('PQH','PQH_FR_INVALID_VALUE');
936 fnd_message.set_token('ATTRIBUTE',hr_general.decode_lookup('FR_PQH_FORM_PROMPTS','TYPE_OF_PS'));
937 hr_multi_message.add
938 (p_associated_column1 => 'TYPE_OF_PS');
939
940 end if;
941 --
942 Close csr_type_of_ps;
943 --
944 End chk_type_of_ps;
945
946 --
947 -- Situation Type Check
948 procedure chk_situation_type(p_situation_type in varchar2)
949 is
950 --
951 l_proc varchar2(72) := g_package||'chk_situation_type';
952 l_value varchar2(1000);
953 --
954 Cursor csr_situation_type IS
955 Select null
956 from hr_lookups
957 Where Lookup_type='FR_PQH_STAT_SIT_TYPE'
958 and ENABLED_FLAG='Y'
959 and lookup_code =p_situation_type;
960 --
961 Begin
962 if g_debug then
963 --
964 hr_utility.set_location('Entering:'||l_proc, 7);
965 --
966 end if;
967
968 Open csr_situation_type;
969 --
970 Fetch csr_situation_type into l_value;
971
972 if csr_situation_type%NOTFOUND then
973 --
974 fnd_message.set_name('PQH','PQH_FR_INVALID_VALUE');
975 fnd_message.set_token('ATTRIBUTE',hr_general.decode_lookup('FR_PQH_FORM_PROMPTS','SITUATION_TYPE'));
976 hr_multi_message.add
977 (p_associated_column1 => 'SITUATION_TYPE');
978
979 end if;
980 --
981 Close csr_situation_type;
982 --
983 End chk_situation_type;
984
985 --
986 procedure chk_unique_sitaution_name (p_rec in pqh_sts_shd.g_rec_type)
987 is
988 --
989 l_proc varchar2(72) := g_package||'chk_unique_sitaution_name';
990 l_value varchar2(1000);
991 --
992 Cursor csr_situation_name IS
993 Select situation_name
994 from pqh_fr_stat_situations
995 where situation_name = p_rec.situation_name
996 and business_group_id = p_rec.business_group_id
997 and statutory_situation_id <> nvl(p_rec.statutory_situation_id,-1);
998 --
999 Begin
1000 if g_debug then
1001 --
1002 hr_utility.set_location('Entering:'||l_proc, 6);
1003 --
1004 End if;
1005
1006
1007 Open csr_situation_name;
1008 --
1009 Fetch csr_situation_name into l_value;
1010
1011 if csr_situation_name%found then
1012 --
1013 fnd_message.set_name('PQH','PQH_FR_UNIQUE_STAT_SIT_NAME');
1014 hr_multi_message.add
1015 (p_associated_column1
1016 => 'SITUATION_NAME');
1017
1018
1019 end if;
1020
1021 Close csr_situation_name;
1022
1023 End chk_unique_sitaution_name;
1024
1025 --
1026 --
1027 --
1028 procedure chk_unique_sitaution (p_rec in pqh_sts_shd.g_rec_type)
1029 is
1030 --
1031 l_proc varchar2(72) := g_package||'chk_unique_sitaution';
1032 l_value varchar2(1000);
1033 --
1034 Cursor csr_unique_situation IS
1035 Select null
1036 from pqh_fr_stat_situations
1037 where
1038 type_of_ps = p_rec.type_Of_ps
1039 and situation_type = p_rec.situation_type
1040 and nvl(sub_type,'-1') = nvl(p_rec.sub_type,'-1')
1041 and nvl(source,'-1') = nvl(p_rec.source,'-1')
1042 and nvl(location,'-1') = nvl(p_rec.location,'-1')
1043 and nvl(reason,'-1') = nvl(p_rec.reason,'-1')
1044 and business_group_id = p_rec.business_group_id
1045 and statutory_situation_id <> nvl(p_rec.statutory_situation_id,-1);
1046 --
1047 Begin
1048 if g_debug then
1049 --
1050 hr_utility.set_location('Entering:'||l_proc, 6);
1051 --
1052 End if;
1053
1054
1055 Open csr_unique_situation;
1056 --
1057 Fetch csr_unique_situation into l_value;
1058
1059 if csr_unique_situation%FOUND then
1060 --
1061 fnd_message.set_name('PQH','PQH_FR_UNIQUE_COMBINATION');
1062 hr_multi_message.add
1063 (p_associated_column1
1064 => 'SITUATION_NAME');
1065
1066 --
1067 end if;
1068
1069 Close csr_unique_situation;
1070
1071 --
1072 End chk_unique_sitaution;
1073 --
1074 --
1075 procedure chk_min_max_duration_rqst(p_min_duration_per_request in number,p_max_duration_per_request in number)
1076 is
1077 --
1078 l_proc varchar2(72) := g_package||'chk_min_max_duration';
1079 --
1080 begin
1081
1082 if (p_min_duration_per_request is not null and p_max_duration_per_request is not null) then
1083 --
1084 if nvl(p_min_duration_per_request,0) >
1085 nvl(p_max_duration_per_request,0) then
1086 --
1087 --
1088 fnd_message.set_name('PQH','PQH_FR_STAT_MIN_MAX_PER_RQST');
1089 hr_multi_message.add
1090 (p_associated_column1=> 'MIN_DURATION_PER_REQUEST');
1091 --
1092 end if;
1093 --
1094 End if;
1095 --
1096 end chk_min_max_duration_rqst;
1097 --
1098 --
1099 procedure chk_max_duration_in_whole(p_FIRST_PERIOD_MAX_DURATION in number, p_MAX_DURATION_WHOLE_CAREER in number)
1100 is
1101 --
1102 l_proc varchar2(72) := g_package||'chk_max_duration_in_whole';
1103 --
1104 begin
1105
1106 if p_max_duration_whole_career is not null and p_first_period_max_duration is not null then
1107 --
1108 if nvl(p_max_duration_whole_career,0) < nvl(p_first_period_max_duration,0) then
1109 --
1110 fnd_message.set_name('PQH','PQH_FR_STAT_DURATION_WHOLE');
1111 hr_multi_message.add
1112 (p_associated_column1=> 'FIRST_PERIOD_MAX_DURATION');
1113 --
1114 end if;
1115 --
1116 end if;
1117 --
1118 end chk_max_duration_in_whole;
1119 --
1120 -- ----------------------------------------------------------------------------
1121 -- |---------------------------< insert_validate >----------------------------|
1122 -- ----------------------------------------------------------------------------
1123 Procedure insert_validate
1124 (p_effective_date in date
1125 ,p_rec in pqh_sts_shd.g_rec_type
1126 ) is
1127 --
1128 l_proc varchar2(72) := g_package||'insert_validate';
1129 --
1130 Begin
1131
1132 if g_debug then
1133 --
1134 hr_utility.set_location('Entering:'||l_proc, 5);
1135 --
1136 End if;
1137 --
1138 -- Call all supporting business operations
1139 --
1140 hr_api.validate_bus_grp_id
1141 (p_business_group_id => p_rec.business_group_id
1142 ,p_associated_column1 => pqh_sts_shd.g_tab_nam
1143 || '.BUSINESS_GROUP_ID');
1144 --
1145 --
1146 hr_multi_message.end_validation_set;
1147
1148 chk_unique_sitaution_name(p_rec);
1149
1150 chk_type_of_ps(p_rec.type_of_ps);
1151
1152 chk_situation_type(p_rec.situation_type);
1153
1154 chk_sub_type(p_rec.sub_type,p_rec.situation_type);
1155
1156 chk_source(p_rec.source);
1157
1158 chk_reason(p_rec.reason, p_rec.situation_type);
1159
1160 chk_default(p_rec);
1161
1162 chk_location(p_rec.location);
1163
1164 chk_frequency (p_rec.frequency);
1165
1166 hr_multi_message.end_validation_set;
1167
1168 chk_unique_sitaution(p_rec);
1169
1170 hr_multi_message.end_validation_set;
1171
1172 chk_duration_limits(p_rec);
1173
1174
1175 hr_multi_message.end_validation_set;
1176
1177 -- Number filed checks
1178 chk_min_max_duration_rqst(p_rec.MIN_DURATION_PER_REQUEST,p_rec.MAX_DURATION_PER_REQUEST);
1179
1180 chk_max_duration_in_whole(p_rec.FIRST_PERIOD_MAX_DURATION,p_rec.MAX_DURATION_WHOLE_CAREER);
1181
1182 hr_multi_message.end_validation_set;
1183
1184 chk_date(p_rec.date_from , p_rec.date_to);
1185
1186 chk_pay_share(p_rec.pay_share,p_rec.remuneration_paid);
1187
1188 chk_pay_periods(p_rec.pay_periods,p_rec.remuneration_paid);
1189
1190 chk_first_period_max_duration(p_rec.first_period_max_duration,p_rec.frequency);
1191
1192 chk_min_duration_per_rqst(p_rec.min_duration_per_request,p_rec.frequency);
1193
1194 chk_max_duration_per_request(p_rec.max_duration_per_request,p_rec.frequency);
1195
1196 chk_max_duration_whole_crr(p_rec.max_duration_whole_career,p_rec.frequency);
1197
1198 chk_max_no_of_renewals(p_rec.max_no_of_renewals,p_rec.renewable_allowed,p_rec.frequency);
1199
1200 chk_max_duration_per_renewal(p_rec.max_duration_per_renewal,p_rec.renewable_allowed,p_rec.frequency);
1201
1202 chk_max_tot_continuous_dur(p_rec.max_tot_continuous_duration,p_rec.frequency);
1203
1204 --
1205 hr_multi_message.end_validation_set;
1206 --
1207 -- Validate Dependent Attributes
1208 --
1209 --
1210 if g_debug then
1211 --
1212 hr_utility.set_location(' Leaving:'||l_proc, 10);
1213 --
1214 end if;
1215 --
1216 End insert_validate;
1217 --
1218 -- ----------------------------------------------------------------------------
1219 -- |---------------------------< update_validate >----------------------------|
1220 -- ----------------------------------------------------------------------------
1221 Procedure update_validate
1222 (p_effective_date in date
1223 ,p_rec in pqh_sts_shd.g_rec_type
1224 ) is
1225 --
1226 l_proc varchar2(72) := g_package||'update_validate';
1227 --
1228 Begin
1229 if g_debug then
1230 --
1231 hr_utility.set_location('Entering:'||l_proc, 5);
1232 --
1233 End if;
1234 --
1235 -- Call all supporting business operations
1236 --
1237 hr_api.validate_bus_grp_id
1238 (p_business_group_id => p_rec.business_group_id
1239 ,p_associated_column1 => pqh_sts_shd.g_tab_nam
1240 || '.BUSINESS_GROUP_ID');
1241 hr_multi_message.end_validation_set;
1242
1243 chk_unique_sitaution_name(p_rec);
1244 chk_default(p_rec);
1245 chk_date(p_rec.date_from , p_rec.date_to);
1246
1247 -- Number filed checks
1248
1249 chk_duration_limits(p_rec);
1250
1251 hr_multi_message.end_validation_set;
1252
1253 chk_min_max_duration_rqst(p_rec.MIN_DURATION_PER_REQUEST,p_rec.MAX_DURATION_PER_REQUEST);
1254
1255 chk_max_duration_in_whole(p_rec.FIRST_PERIOD_MAX_DURATION,p_rec.MAX_DURATION_WHOLE_CAREER);
1256
1257 chk_pay_share(p_rec.pay_share,p_rec.remuneration_paid);
1258
1259 chk_pay_periods(p_rec.pay_periods,p_rec.remuneration_paid);
1260
1261 chk_first_period_max_duration(p_rec.first_period_max_duration,p_rec.frequency);
1262
1263 chk_min_duration_per_rqst(p_rec.min_duration_per_request,p_rec.frequency);
1264
1265 chk_max_duration_per_request(p_rec.max_duration_per_request,p_rec.frequency);
1266
1267 chk_max_duration_whole_crr(p_rec.max_duration_whole_career,p_rec.frequency);
1268
1269 chk_max_no_of_renewals(p_rec.max_no_of_renewals,p_rec.renewable_allowed,p_rec.frequency);
1270
1271 chk_max_duration_per_renewal(p_rec.max_duration_per_renewal,p_rec.renewable_allowed,p_rec.frequency);
1272
1273 chk_max_tot_continuous_dur(p_rec.max_tot_continuous_duration,p_rec.frequency);
1274
1275
1276 --
1277 hr_multi_message.end_validation_set;
1281 chk_non_updateable_args
1278 --
1279 -- Validate Dependent Attributes
1280 --
1282 (p_effective_date => p_effective_date
1283 ,p_rec => p_rec
1284 );
1285 --
1286 hr_multi_message.end_validation_set;
1287 --
1288 if g_debug then
1289 --
1290 hr_utility.set_location(' Leaving:'||l_proc, 10);
1291 --
1292 end if;
1293 End update_validate;
1294 --
1295 -- ----------------------------------------------------------------------------
1296 -- |---------------------------< delete_validate >----------------------------|
1297 -- ----------------------------------------------------------------------------
1298 Procedure delete_validate
1299 (p_rec in pqh_sts_shd.g_rec_type
1300 ) is
1301 --
1302 l_proc varchar2(72) := g_package||'delete_validate';
1303 --
1304 Begin
1305 --
1306
1307 if g_debug then
1308 --
1309 hr_utility.set_location('Entering:'||l_proc, 5);
1310 --
1311
1312 --
1313 -- Call all supporting business operations
1314 --
1315 hr_utility.set_location(' Leaving:'||l_proc, 10);
1316
1317 End if;
1318 End delete_validate;
1319 --
1320 end pqh_sts_bus;