1 Package Body pqp_pty_bus as
2 /* $Header: pqptyrhi.pkb 120.0.12000000.1 2007/01/16 04:29:01 appldev noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pqp_pty_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_pension_type_id number default null;
15 --
16 -- ----------------------------------------------------------------------------
17 -- |---------------------< chk_contribution_conv_rule >------------------------|
18 -- ----------------------------------------------------------------------------
19 --
20 procedure chk_contribution_conv_rule
21 (p_pension_type_id in pqp_pension_types_f.pension_type_id%TYPE
22 ,p_contribution_conversion_rule in pqp_pension_types_f.contribution_conversion_rule%TYPE
23 ,p_effective_date in date
24 ,p_validation_start_date in date
25 ,p_validation_end_date in date
26 ) is
27 --
28 l_proc varchar2(72) := g_package||'chk_contribution_conv_rule';
29 l_api_updating boolean;
30 l_lookup_type varchar2(50); -- added for UK
31 --
32 begin
33 hr_utility.set_location('Entering:'|| l_proc, 10);
34 --
35 -- Check mandatory parameters have been set
36 --
37 hr_api.mandatory_arg_error
38 (p_api_name => l_proc
39 ,p_argument => 'effective date'
40 ,p_argument_value => p_effective_date
41 );
42 --
43 -- Only proceed with validation if:
44 -- a) During update, the value has actually changed to
45 -- another not null value.
46 -- b) During insert, the type_id is not null.
47 --
48 if (((p_pension_type_id is not null) and
49 nvl(pqp_pty_shd.g_old_rec.contribution_conversion_rule,
50 hr_api.g_varchar2) <> nvl(p_contribution_conversion_rule,
51 hr_api.g_varchar2))
52 or
53 (p_pension_type_id is null)) then
54
55 -- If contribution_conversion_rule is not null then
56 -- Check if the contribution_conversion_rule value exists in hr_lookups
57 -- where the lookup_type is 'PQP_NL_CONVERSION_RULE'
58 --
59 if p_contribution_conversion_rule is not null then
60 if hr_api.not_exists_in_dt_hrstanlookups
61 (p_effective_date => p_effective_date
62 ,p_validation_start_date => p_validation_start_date
63 ,p_validation_end_date => p_validation_end_date
64 ,p_lookup_type => 'PQP_NL_CONVERSION_RULE'
65 ,p_lookup_code => p_contribution_conversion_rule
66 ) then
67 -- Error: Invalid Contribution Conversion Rule
68 fnd_message.set_name('PQP', 'PQP_230808_INVALID_CONTR_RULE');
69 fnd_message.raise_error;
70 end if;
71 end if;
72 end if;
73 hr_utility.set_location(' Leaving:'|| l_proc, 20);
74 exception
75 when app_exception.application_exception then
76 if hr_multi_message.exception_add
77 (p_associated_column1 => 'PQP_PENSION_TYPES_F.CONTRIBUTION_CONVERSION_RULE'
78 ) then
79 hr_utility.set_location(' Leaving:'|| l_proc, 30);
80 raise;
81 end if;
82 hr_utility.set_location(' Leaving:'|| l_proc, 40);
83 end chk_contribution_conv_rule;
84
85 --
86 -- ---------------------------------------------------------------------------------
87 -- |------------------------< chk_threshold_conv_rule >-----------------------------|
88 -- ---------------------------------------------------------------------------------
89 --
90 procedure chk_threshold_conv_rule
91 (p_pension_type_id in pqp_pension_types_f.pension_type_id%TYPE
92 ,p_threshold_conversion_rule in pqp_pension_types_f.threshold_conversion_rule%TYPE
93 ,p_effective_date in date
94 ,p_validation_start_date in date
95 ,p_validation_end_date in date
96 ) is
97 --
98 l_proc varchar2(72) := g_package||'chk_threshold_conv_rule';
99 l_api_updating boolean;
100 --
101 begin
102 hr_utility.set_location('Entering:'|| l_proc, 10);
103 --
104 -- Check mandatory parameters have been set
105 --
106 hr_api.mandatory_arg_error
107 (p_api_name => l_proc
108 ,p_argument => 'effective date'
109 ,p_argument_value => p_effective_date
110 );
111 --
112 -- Only proceed with validation if:
113 -- a) During update, the value has actually changed to
114 -- another not null value.
115 -- b) During insert, the type_id is not null.
116 --
117 if (((p_pension_type_id is not null) and
118 nvl(pqp_pty_shd.g_old_rec.threshold_conversion_rule,
119 hr_api.g_varchar2) <> nvl(p_threshold_conversion_rule,
120 hr_api.g_varchar2))
121 or
122 (p_pension_type_id is null)) then
123 --
124 -- If threshold_conversion_rule is not null then
125 -- Check if the threshold_conversion_rule value exists in hr_lookups
126 -- where the lookup_type is 'PQP_NL_CONVERSION_RULE'
127 --
128 if p_threshold_conversion_rule is not null then
129 if hr_api.not_exists_in_dt_hrstanlookups
130 (p_effective_date => p_effective_date
131 ,p_validation_start_date => p_validation_start_date
132 ,p_validation_end_date => p_validation_end_date
133 ,p_lookup_type => 'PQP_NL_CONVERSION_RULE'
134 ,p_lookup_code => p_threshold_conversion_rule
135 ) then
136 -- Error: Invalid Threshold Conversion Rule
137 fnd_message.set_name('PQP', 'PQP_230809_INVALID_THRES_RULE');
138 fnd_message.raise_error;
139 end if;
140 end if;
141 end if;
142 hr_utility.set_location(' Leaving:'|| l_proc, 20);
143 exception
144 when app_exception.application_exception then
145 if hr_multi_message.exception_add
146 (p_associated_column1 => 'PQP_PENSION_TYPES_F.THRESHOLD_CONVERSION_RULE'
147 ) then
148 hr_utility.set_location(' Leaving:'|| l_proc, 30);
149 raise;
150 end if;
151 hr_utility.set_location(' Leaving:'|| l_proc, 40);
152 end chk_threshold_conv_rule;
153
154 --
155 -- ----------------------------------------------------------------------------
156 -- |-----------------------< chk_pension_category >----------------------------|
157 -- ----------------------------------------------------------------------------
158 --
159 procedure chk_pension_category
160 (p_pension_type_id in pqp_pension_types_f.pension_type_id%TYPE
161 ,p_pension_category in pqp_pension_types_f.pension_category%TYPE
162 ,p_effective_date in date
163 ,p_validation_start_date in date
164 ,p_validation_end_date in date
165 ) is
166 --
167 l_proc varchar2(72) := g_package||'chk_pension_category';
168 l_api_updating boolean;
169
170 --
171 begin
172 hr_utility.set_location('Entering:'|| l_proc, 10);
173 --
174 -- Check mandatory parameters have been set
175 --
176 hr_api.mandatory_arg_error
177 (p_api_name => l_proc
178 ,p_argument => 'effective date'
179 ,p_argument_value => p_effective_date
180 );
181 --
182 -- Only proceed with validation if:
183 -- a) During update, the value has actually changed to
184 -- another not null value.
185 -- b) During insert, the type_id is not null.
186 --
187 if (((p_pension_type_id is not null) and
188 nvl(pqp_pty_shd.g_old_rec.pension_category,
189 hr_api.g_varchar2) <> nvl(p_pension_category,
190 hr_api.g_varchar2))
191 or
192 (p_pension_type_id is null)) then
193
194 --
195 -- If pension_category is not null then
196 -- Check if the pension_category value exists in hr_lookups
197 -- where the lookup_type is 'PQP_PENSION_CATEGORY'
198 --
199 if p_pension_category is not null then
200 if hr_api.not_exists_in_dt_hrstanlookups
201 (p_effective_date => p_effective_date
202 ,p_validation_start_date => p_validation_start_date
203 ,p_validation_end_date => p_validation_end_date
204 ,p_lookup_type => 'PQP_PENSION_CATEGORY'
205 ,p_lookup_code => p_pension_category
206 ) then
207 -- Error: Invalid Pension Category
208 fnd_message.set_name('PQP', 'PQP_230810_INVALID_PEN_CAT');
209 fnd_message.raise_error;
210 end if;
211 end if;
212
213 end if;
214 hr_utility.set_location(' Leaving:'|| l_proc, 20);
215 exception
216 when app_exception.application_exception then
217 if hr_multi_message.exception_add
218 (p_associated_column1 => 'PQP_PENSION_TYPES_F.PENSION_CATEGORY'
219 ) then
220 hr_utility.set_location(' Leaving:'|| l_proc, 30);
221 raise;
222 end if;
223 hr_utility.set_location(' Leaving:'|| l_proc, 40);
224 end chk_pension_category;
225
226 --
227 -- ------------------------------------------------------------------------------
228 -- |-------------------------< chk_pension_provider >----------------------------|
229 -- ------------------------------------------------------------------------------
230 --
231 procedure chk_pension_provider
232 (p_pension_type_id in pqp_pension_types_f.pension_type_id%TYPE
233 ,p_pension_provider_type in pqp_pension_types_f.pension_provider_type%TYPE
234 ,p_effective_date in date
235 ,p_validation_start_date in date
236 ,p_validation_end_date in date
237 ) is
238 --
239 l_proc varchar2(72) := g_package||'chk_pension_provider';
240 l_api_updating boolean;
241 --
242 begin
243 hr_utility.set_location('Entering:'|| l_proc, 10);
244 --
245 -- Check mandatory parameters have been set
246 --
247 hr_api.mandatory_arg_error
248 (p_api_name => l_proc
249 ,p_argument => 'effective date'
250 ,p_argument_value => p_effective_date
251 );
252 --
253 -- Only proceed with validation if:
254 -- a) During update, the value has actually changed to
255 -- another not null value.
256 -- b) During insert, the type_id is not null.
257 --
258 if (((p_pension_type_id is not null) and
259 nvl(pqp_pty_shd.g_old_rec.pension_provider_type,
260 hr_api.g_varchar2) <> nvl(p_pension_provider_type,
261 hr_api.g_varchar2))
262 or
263 (p_pension_type_id is null)) then
264 --
265 -- If pension_provider_type is not null then
266 -- Check if the pension_provider_type value exists in hr_lookups
267 -- where the lookup_type is 'PQP_NL_PENSION_PROVIDER_TYPE'
268 --
269 if p_pension_provider_type is not null then
270 if hr_api.not_exists_in_dt_hrstanlookups
271 (p_effective_date => p_effective_date
272 ,p_validation_start_date => p_validation_start_date
273 ,p_validation_end_date => p_validation_end_date
274 ,p_lookup_type => 'PQP_NL_PENSION_PROVIDER_TYPE'
275 ,p_lookup_code => p_pension_provider_type
276 ) then
277 -- Error: Invalid Pension Provider Type
278 fnd_message.set_name('PQP', 'PQP_230811_INV_PRVDR_TYPE');
279 fnd_message.raise_error;
280 end if;
281 end if;
282 end if;
283 hr_utility.set_location(' Leaving:'|| l_proc, 20);
284 exception
285 when app_exception.application_exception then
286 if hr_multi_message.exception_add
287 (p_associated_column1 => 'PQP_PENSION_TYPES_F.PENSION_PROVIDER_TYPE'
288 ) then
289 hr_utility.set_location(' Leaving:'|| l_proc, 30);
290 raise;
291 end if;
292 hr_utility.set_location(' Leaving:'|| l_proc, 40);
293 end chk_pension_provider;
294 --
295 -- ------------------------------------------------------------------------------
296 -- |-------------------------< chk_salary_calc_method >--------------------------|
297 -- ------------------------------------------------------------------------------
298 --
299 procedure chk_salary_calc_method
300 (p_pension_type_id in pqp_pension_types_f.pension_type_id%TYPE
301 ,p_salary_calculation_method in pqp_pension_types_f.salary_calculation_method%TYPE
302 ,p_effective_date in date
303 ,p_validation_start_date in date
304 ,p_validation_end_date in date
305 ) is
306 --
307 l_proc varchar2(72) := g_package||'chk_salary_calc_method';
308 l_api_updating boolean;
309 --
310 begin
311 hr_utility.set_location('Entering:'|| l_proc, 10);
312 --
313 -- Check mandatory parameters have been set
314 --
315 hr_api.mandatory_arg_error
316 (p_api_name => l_proc
317 ,p_argument => 'effective date'
318 ,p_argument_value => p_effective_date
319 );
320 --
321 -- Only proceed with validation if:
322 -- a) During update, the value has actually changed to
323 -- another not null value.
324 -- b) During insert, the type_id is not null.
325 --
326 if (((p_pension_type_id is not null) and
327 nvl(pqp_pty_shd.g_old_rec.salary_calculation_method,
328 hr_api.g_varchar2) <> nvl(p_salary_calculation_method,
329 hr_api.g_varchar2))
330 or
331 (p_pension_type_id is null)) then
332 --
333 -- If salary_calculation_method is not null then
334 -- Check if the salary_calculation_method value exists in hr_lookups
335 -- where the lookup_type is 'PQP_NL_SALARY_CALC_METHOD'
336 --
337 if p_salary_calculation_method is not null then
338 if hr_api.not_exists_in_dt_hrstanlookups
339 (p_effective_date => p_effective_date
340 ,p_validation_start_date => p_validation_start_date
341 ,p_validation_end_date => p_validation_end_date
342 ,p_lookup_type => 'PQP_NL_SALARY_CALC_METHOD'
343 ,p_lookup_code => p_salary_calculation_method
344 ) then
345 -- Error: Invalid Salary Calculation Method
346 fnd_message.set_name('PQP', 'PQP_230812_SALARY_CALC_METHOD');
347 fnd_message.raise_error;
348 end if;
349 end if;
350 end if;
351 hr_utility.set_location(' Leaving:'|| l_proc, 20);
352 exception
353 when app_exception.application_exception then
354 if hr_multi_message.exception_add
355 (p_associated_column1 => 'PQP_PENSION_TYPES_F.SALARY_CALCULATION_METHOD'
356 ) then
357 hr_utility.set_location(' Leaving:'|| l_proc, 30);
358 raise;
359 end if;
360 hr_utility.set_location(' Leaving:'|| l_proc, 40);
361 end chk_salary_calc_method;
362 --
363 -- ------------------------------------------------------------------------------
364 -- |-------------------------< chk_pension_type_name >---------------------------|
365 -- ------------------------------------------------------------------------------
366 --
367 procedure chk_pension_type_name
368 (p_pension_type_id in pqp_pension_types_f.pension_type_id%TYPE
369 ,p_pension_type_name in pqp_pension_types_f.pension_type_name%TYPE
370 ,p_effective_date in date
371 ,p_business_group_id in pqp_pension_types_f.business_group_id%TYPE
372 ,p_legislation_code in pqp_pension_types_f.legislation_code%TYPE
373 ,p_validation_start_date in date
374 ,p_validation_end_date in date
375 ) is
376 --
377 Cursor csr_pension_type
378 (c_pension_type_name in pqp_pension_types_f.pension_type_name%TYPE,
379 c_business_group_id in pqp_pension_types_f.business_group_id%TYPE,
380 c_legislation_code in pqp_pension_types_f.legislation_code%TYPE,
381 c_effective_date in date,
382 c_validation_start_date in date,
383 c_validation_end_date in date
384 ) Is
385 select pension_type_name,
386 business_group_id,
387 legislation_code,
388 effective_start_date,
389 effective_start_date,
390 effective_end_date
391 from pqp_pension_types_f
392 where upper(pension_type_name) = upper(c_pension_type_name)
393 and
394 (( business_group_id IS NOT NULL
395 AND business_group_id = c_business_group_id
396 )
397 OR ( legislation_code IS NOT NULL AND legislation_code = c_legislation_code)
398 OR (business_group_id IS NULL AND legislation_code IS NULL)
399 )
400 and c_effective_date between effective_start_date and effective_end_date;
401
402 l_proc varchar2(72) := g_package||'chk_pension_type_name';
403 l_api_updating boolean;
404 l_pension_type_name pqp_pension_types_f.pension_type_name%TYPE;
405 l_business_group_id pqp_pension_types_f.business_group_id%TYPE;
406 l_legislation_code pqp_pension_types_f.legislation_code%TYPE;
407 l_effective_date date;
408 l_validation_start_date date;
409 l_validation_end_date date;
410 --
411 begin
412 hr_utility.set_location('Entering:'|| l_proc, 10);
413
414 --
415 -- During insert check to see if the pension type name exists
416 -- already in the pqp_pension_types_f table.
417 --
418 if p_pension_type_id is null then
419 open csr_pension_type
420 (
421 c_pension_type_name => p_pension_type_name,
422 c_business_group_id => p_business_group_id,
423 c_legislation_code => p_legislation_code,
424 c_effective_date => p_effective_date,
425 c_validation_start_date => p_validation_start_date,
426 c_validation_end_date => p_validation_end_date
427 );
428
429
430 fetch csr_pension_type Into
431 l_pension_type_name,
432 l_business_group_id,
433 l_legislation_code,
434 l_effective_date,
435 l_validation_start_date,
436 l_validation_end_date;
437
438 if csr_pension_type%FOUND then
439 close csr_pension_type;
440 hr_utility.set_location(' Pension Type Name already exists in table',15);
441 -- Error: Pension Name already exists in the table
442 fnd_message.set_name('PQP', 'PQP_230813_PEN_TYPE_EXISTS');
443 fnd_message.raise_error;
444 else
445 close csr_pension_type;
446 end if;
447
448 end if;
449 hr_utility.set_location(' Leaving:'|| l_proc, 20);
450 exception
451 when app_exception.application_exception then
452 if hr_multi_message.exception_add
453 (p_associated_column1 => 'PQP_PENSION_TYPES_F.PENSION_TYPE_NAME'
454 ) then
455 hr_utility.set_location(' Leaving:'|| l_proc, 30);
456 raise;
457 end if;
458 hr_utility.set_location(' Leaving:'|| l_proc, 40);
459 end chk_pension_type_name;
460 --
461 -- ------------------------------------------------------------------------------
462 -- |-------------------------< chk_provider_assignment >-------------------------|
463 -- ------------------------------------------------------------------------------
464 --
465 procedure chk_provider_assignment
466 (p_pension_type_id in pqp_pension_types_f.pension_type_id%TYPE
467 ,p_effective_date in date
468 ,p_leg_code in pqp_pension_types_f.legislation_code%TYPE
469 ) is
470 --
471 Cursor csr_org_info_nl
472 (c_pension_type_id in pqp_pension_types_f.pension_type_id%TYPE) Is
473 select 'x'
474 from hr_organization_information
475 where org_information_context = 'PQP_NL_PENSION_TYPES'
476 and org_information1 = to_char(c_pension_type_id);
477
478 -- added for UK
479 Cursor csr_org_info_gb
480 (c_pension_type_id in pqp_pension_types_f.pension_type_id%TYPE) Is
481 select 'x'
482 from hr_organization_information
483 where org_information_context = 'PQP_GB_PENSION_TYPES_INFO'
484 and org_information1 = to_char(c_pension_type_id);
485
486 -- added for HU
487 Cursor csr_org_info_hu
488 (c_pension_type_id in pqp_pension_types_f.pension_type_id%TYPE) Is
489 select 'x'
490 from hr_organization_information
491 where org_information_context = 'HU_PENSION_TYPES_INFO'
492 and org_information1 = to_char(c_pension_type_id);
493
494 l_proc varchar2(72) := g_package||'chk_provider_assignment';
495 l_api_updating boolean;
496 l_org_info_exits varchar2(5);
497 --
498 begin
499 hr_utility.set_location('Entering:'|| l_proc, 10);
500 --
501 -- During insert check to see if the pension type name exists
502 -- already in the pqp_pension_types_f table.
503 --
504
505 if(p_leg_code = 'NL') then
506 open csr_org_info_nl (c_pension_type_id => p_pension_type_id);
507 fetch csr_org_info_nl Into l_org_info_exits;
508 if csr_org_info_nl%FOUND then
509 close csr_org_info_nl;
510 -- Error: Pension Name has been assigned to a provider
511 fnd_message.set_name('PQP', 'PQP_230814_PEN_TYPE_ASGED');
512 fnd_message.raise_error;
513 else
514 close csr_org_info_nl;
515 end if;
516 end if;
517
518 if(p_leg_code = 'GB') then
519 open csr_org_info_gb (c_pension_type_id => p_pension_type_id);
520 fetch csr_org_info_gb Into l_org_info_exits;
521 if csr_org_info_gb%FOUND then
522 close csr_org_info_gb;
523 -- Error: Pension Name has been assigned to a provider
524 fnd_message.set_name('PQP', 'PQP_230814_PEN_TYPE_ASGED');
525 fnd_message.raise_error;
526 else
527 close csr_org_info_gb;
528 end if;
529 end if;
530
531 if(p_leg_code = 'HU') then
532 open csr_org_info_hu (c_pension_type_id => p_pension_type_id);
533 fetch csr_org_info_hu Into l_org_info_exits;
534 if csr_org_info_hu%FOUND then
535 close csr_org_info_hu;
536 -- Error: Pension Name has been assigned to a provider
537 fnd_message.set_name('PQP', 'PQP_230814_PEN_TYPE_ASGED');
538 fnd_message.raise_error;
539 else
540 close csr_org_info_hu;
541 end if;
542 end if;
543
544 hr_utility.set_location(' Leaving:'|| l_proc, 20);
545 exception
546 when app_exception.application_exception then
547 if hr_multi_message.exception_add
548 (p_associated_column1 => 'PQP_PENSION_TYPES_F.PENSION_TYPE_ID'
549 ) then
550 hr_utility.set_location(' Leaving:'|| l_proc, 30);
551 raise;
552 end if;
553 hr_utility.set_location(' Leaving:'|| l_proc, 40);
554 end chk_provider_assignment;
555
556 --
557 -- ------------------------------------------------------------------------------
558 -- |----------------------< chk_special_pension_type_code >-------------------|
559 -- ------------------------------------------------------------------------------
560 --
561 procedure chk_special_pension_type_code
562 (p_pension_type_id in pqp_pension_types_f.pension_type_id%TYPE
563 ,p_special_pension_type_code in pqp_pension_types_f.special_pension_type_code%TYPE
564 ,p_effective_date in date
565 ,p_validation_start_date in date
566 ,p_validation_end_date in date
567 ) is
568 --
569 l_proc varchar2(72) := g_package||'chk_special_pension_type_code';
570 l_api_updating boolean;
571 --
572 begin
573 hr_utility.set_location('Entering:'|| l_proc, 10);
574 --
575 -- Check mandatory parameters have been set
576 --
577 hr_api.mandatory_arg_error
578 (p_api_name => l_proc
579 ,p_argument => 'effective date'
580 ,p_argument_value => p_effective_date
581 );
582 --
583 -- Only proceed with validation if:
584 -- a) During update, the value has actually changed to
585 -- another not null value.
586 -- b) During insert, the type_id is not null.
587 --
588 if (((p_pension_type_id is not null) and
589 nvl(pqp_pty_shd.g_old_rec.special_pension_type_code,
590 hr_api.g_varchar2) <> nvl(p_special_pension_type_code,
591 hr_api.g_varchar2))
592 or
593 (p_pension_type_id is null)) then
594 --
595 -- If special_pension_type_code is not null then
596 -- Check if the special_pension_type_code value exists in hr_lookups
597 -- where the lookup_type is 'PQP_SPECIAL_PENSION_TYPE_CODE'
598 --
599 if p_special_pension_type_code is not null then
600 if hr_api.not_exists_in_dt_hrstanlookups
601 (p_effective_date => p_effective_date
602 ,p_validation_start_date => p_validation_start_date
603 ,p_validation_end_date => p_validation_end_date
604 ,p_lookup_type => 'PQP_SPECIAL_PENSION_TYPE_CODE'
605 ,p_lookup_code => p_special_pension_type_code
606 ) then
607 -- Error: Invalid Special Pension Type Code
608 fnd_message.set_name('PQP', 'PQP_230022_INV_SPL_PEN_TYPE');
609 fnd_message.raise_error;
610 end if;
611 end if;
612 end if;
613 hr_utility.set_location(' Leaving:'|| l_proc, 20);
614 exception
615 when app_exception.application_exception then
616 if hr_multi_message.exception_add
617 (p_associated_column1 => 'PQP_PENSION_TYPES_F.SPECIAL_PENSION_TYPE_CODE'
618 ) then
619 hr_utility.set_location(' Leaving:'|| l_proc, 30);
620 raise;
621 end if;
622 hr_utility.set_location(' Leaving:'|| l_proc, 40);
623 end chk_special_pension_type_code;
624
625 --
626 -- ------------------------------------------------------------------------------
627 -- |-----------------------< chk_pension_sub_category >------------------------|
628 -- ------------------------------------------------------------------------------
629 --
630 procedure chk_pension_sub_category
631 (p_pension_type_id in pqp_pension_types_f.pension_type_id%TYPE
632 ,p_pension_sub_category in pqp_pension_types_f.pension_sub_category%TYPE
633 ,p_effective_date in date
634 ,p_validation_start_date in date
635 ,p_validation_end_date in date
636 ) is
637 --
638 l_proc varchar2(72) := g_package||'chk_pension_sub_category';
639 l_api_updating boolean;
640 --
641 begin
642 hr_utility.set_location('Entering:'|| l_proc, 10);
643 --
644 -- Check mandatory parameters have been set
645 --
646 hr_api.mandatory_arg_error
647 (p_api_name => l_proc
648 ,p_argument => 'effective date'
649 ,p_argument_value => p_effective_date
650 );
651 --
652 -- Only proceed with validation if:
653 -- a) During update, the value has actually changed to
654 -- another not null value.
655 -- b) During insert, the type_id is not null.
656 --
657 if (((p_pension_type_id is not null) and
658 nvl(pqp_pty_shd.g_old_rec.pension_sub_category,
659 hr_api.g_varchar2) <> nvl(p_pension_sub_category,
660 hr_api.g_varchar2))
661 or
662 (p_pension_type_id is null)) then
663 --
664 -- If pension_sub_category is not null then
665 -- Check if the pension_sub_category value exists in hr_lookups
666 -- where the lookup_type is 'PQP_PENSION_SUB_CATEGORY'
667 --
668 if p_pension_sub_category is not null then
669 if hr_api.not_exists_in_dt_hrstanlookups
670 (p_effective_date => p_effective_date
671 ,p_validation_start_date => p_validation_start_date
672 ,p_validation_end_date => p_validation_end_date
673 ,p_lookup_type => 'PQP_PENSION_SUB_CATEGORY'
674 ,p_lookup_code => p_pension_sub_category
675 ) then
676 -- Error: Invalid Pension Sub Category
677 fnd_message.set_name('PQP', 'PQP_230023_INV_PEN_SUB_CAT');
678 fnd_message.raise_error;
679 end if;
680 end if;
681 end if;
682 hr_utility.set_location(' Leaving:'|| l_proc, 20);
683 exception
684 when app_exception.application_exception then
685 if hr_multi_message.exception_add
686 (p_associated_column1 => 'PQP_PENSION_TYPES_F.PENSION_SUB_CATEGORY'
687 ) then
688 hr_utility.set_location(' Leaving:'|| l_proc, 30);
689 raise;
690 end if;
691 hr_utility.set_location(' Leaving:'|| l_proc, 40);
692 end chk_pension_sub_category;
693
694 --
695 -- ------------------------------------------------------------------------------
696 -- |-----------------------< chk_pension_basis_calc_method >-------------------|
697 -- ------------------------------------------------------------------------------
698 --
699 procedure chk_pension_basis_calc_method
700 (p_pension_type_id in pqp_pension_types_f.pension_type_id%TYPE
701 ,p_pension_basis_calc_method in pqp_pension_types_f.pension_basis_calc_method%TYPE
702 ,p_effective_date in date
703 ,p_validation_start_date in date
704 ,p_validation_end_date in date
705 ) is
706 --
707 l_proc varchar2(72) := g_package||'chk_pension_basis_calc_method';
708 l_api_updating boolean;
709 --
710 begin
711 hr_utility.set_location('Entering:'|| l_proc, 10);
712 --
713 -- Check mandatory parameters have been set
714 --
715 hr_api.mandatory_arg_error
716 (p_api_name => l_proc
717 ,p_argument => 'effective date'
718 ,p_argument_value => p_effective_date
719 );
720 --
721 -- Only proceed with validation if:
722 -- a) During update, the value has actually changed to
723 -- another not null value.
724 -- b) During insert, the type_id is not null.
725 --
726 if (((p_pension_type_id is not null) and
727 nvl(pqp_pty_shd.g_old_rec.pension_basis_calc_method,
728 hr_api.g_varchar2) <> nvl(p_pension_basis_calc_method,
729 hr_api.g_varchar2))
730 or
731 (p_pension_type_id is null)) then
732 --
733 -- If pension_basis_calc_method is not null then
734 -- Check if the pension_basis_calc_method value exists in hr_lookups
735 -- where the lookup_type is 'PQP_PENSION_BASIS_CALC_MTHD'
736 --
737 if p_pension_basis_calc_method is not null then
738 if hr_api.not_exists_in_dt_hrstanlookups
739 (p_effective_date => p_effective_date
740 ,p_validation_start_date => p_validation_start_date
741 ,p_validation_end_date => p_validation_end_date
742 ,p_lookup_type => 'PQP_PENSION_BASIS_CALC_MTHD'
743 ,p_lookup_code => p_pension_basis_calc_method
744 ) then
745 -- Error: Invalid Pension Basis Calculation Method
746 fnd_message.set_name('PQP', 'PQP_230024_INV_PEN_BASIS_CALC');
747 fnd_message.raise_error;
748 end if;
749 end if;
750 end if;
751 hr_utility.set_location(' Leaving:'|| l_proc, 20);
752 exception
753 when app_exception.application_exception then
754 if hr_multi_message.exception_add
755 (p_associated_column1 => 'PQP_PENSION_TYPES_F.PENSION_BASIS_CALC_METHOD'
756 ) then
757 hr_utility.set_location(' Leaving:'|| l_proc, 30);
758 raise;
759 end if;
760 hr_utility.set_location(' Leaving:'|| l_proc, 40);
761 end chk_pension_basis_calc_method;
762
763 --
764 -- ------------------------------------------------------------------------------
765 -- |----------------------< chk_prev_year_bonus_include >---------------------|
766 -- ------------------------------------------------------------------------------
767 --
768 procedure chk_prev_year_bonus_include
769 (p_pension_type_id in pqp_pension_types_f.pension_type_id%TYPE
770 ,p_previous_year_bonus_included in pqp_pension_types_f.previous_year_bonus_included%TYPE
771 ,p_effective_date in date
772 ,p_validation_start_date in date
773 ,p_validation_end_date in date
774 ) is
775 --
776 l_proc varchar2(72) := g_package||'chk_prev_year_bonus_include';
777 l_api_updating boolean;
778 --
779 begin
780 hr_utility.set_location('Entering:'|| l_proc, 10);
781 --
782 -- Check mandatory parameters have been set
783 --
784 hr_api.mandatory_arg_error
785 (p_api_name => l_proc
786 ,p_argument => 'effective date'
787 ,p_argument_value => p_effective_date
788 );
789 --
790 -- Only proceed with validation if:
791 -- a) During update, the value has actually changed to
792 -- another not null value.
793 -- b) During insert, the type_id is not null.
794 --
795 if (((p_pension_type_id is not null) and
796 nvl(pqp_pty_shd.g_old_rec.previous_year_bonus_included,
797 hr_api.g_varchar2) <> nvl(p_previous_year_bonus_included,
798 hr_api.g_varchar2))
799 or
800 (p_pension_type_id is null)) then
801 --
802 -- If previous_year_bonus_included is not null then
803 -- Check if the previous_year_bonus_included value exists in hr_lookups
804 -- where the lookup_type is 'PQP_YES_NO'
805 --
806 if p_previous_year_bonus_included is not null then
807 if hr_api.not_exists_in_dt_hrstanlookups
808 (p_effective_date => p_effective_date
809 ,p_validation_start_date => p_validation_start_date
810 ,p_validation_end_date => p_validation_end_date
811 ,p_lookup_type => 'PQP_YES_NO'
812 ,p_lookup_code => p_previous_year_bonus_included
813 ) then
814 -- Error: Invalid Previous Year Bonus Included
815 fnd_message.set_name('PQP', 'PQP_230025_PREV_YR_BONUS_INCL');
816 fnd_message.raise_error;
817 end if;
818 end if;
819 end if;
820 hr_utility.set_location(' Leaving:'|| l_proc, 20);
821 exception
822 when app_exception.application_exception then
823 if hr_multi_message.exception_add
824 (p_associated_column1 => 'PQP_PENSION_TYPES_F.PREVIOUS_YEAR_BONUS_INCLUDED'
825 ) then
826 hr_utility.set_location(' Leaving:'|| l_proc, 30);
827 raise;
828 end if;
829 hr_utility.set_location(' Leaving:'|| l_proc, 40);
830 end chk_prev_year_bonus_include;
831
832 --
833 -- ------------------------------------------------------------------------------
834 -- |-----------------------< chk_recurring_bonus_period >---------------------|
835 -- ------------------------------------------------------------------------------
836 --
837 procedure chk_recurring_bonus_period
838 (p_pension_type_id in pqp_pension_types_f.pension_type_id%TYPE
839 ,p_recurring_bonus_period in pqp_pension_types_f.recurring_bonus_period%TYPE
840 ,p_effective_date in date
841 ,p_validation_start_date in date
842 ,p_validation_end_date in date
843 ) is
844 --
845 l_proc varchar2(72) := g_package||'chk_recurring_bonus_period';
846 l_api_updating boolean;
847 --
848 begin
849 hr_utility.set_location('Entering:'|| l_proc, 10);
850 --
851 -- Check mandatory parameters have been set
852 --
853 hr_api.mandatory_arg_error
854 (p_api_name => l_proc
855 ,p_argument => 'effective date'
856 ,p_argument_value => p_effective_date
857 );
858 --
859 -- Only proceed with validation if:
860 -- a) During update, the value has actually changed to
861 -- another not null value.
862 -- b) During insert, the type_id is not null.
863 --
864 if (((p_pension_type_id is not null) and
865 nvl(pqp_pty_shd.g_old_rec.recurring_bonus_period,
866 hr_api.g_varchar2) <> nvl(p_recurring_bonus_period,
867 hr_api.g_varchar2))
868 or
869 (p_pension_type_id is null)) then
870 --
871 -- If recurring_bonus_period is not null then
872 -- Check if the recurring_bonus_period value exists in hr_lookups
873 -- where the lookup_type is 'PQP_BONUS_PERIOD'
874 --
875 if p_recurring_bonus_period is not null then
876 if hr_api.not_exists_in_dt_hrstanlookups
877 (p_effective_date => p_effective_date
878 ,p_validation_start_date => p_validation_start_date
879 ,p_validation_end_date => p_validation_end_date
880 ,p_lookup_type => 'PQP_BONUS_PERIOD'
881 ,p_lookup_code => p_recurring_bonus_period
882 ) then
883 -- Error: Invalid Recurring Bonus Period
884 fnd_message.set_name('PQP', 'PQP_230026_INV_RECUR_BONUS_PER');
885 fnd_message.raise_error;
886 end if;
887 end if;
888 end if;
889 hr_utility.set_location(' Leaving:'|| l_proc, 20);
890 exception
891 when app_exception.application_exception then
892 if hr_multi_message.exception_add
893 (p_associated_column1 => 'PQP_PENSION_TYPES_F.RECURRING_BONUS_PERIOD'
894 ) then
895 hr_utility.set_location(' Leaving:'|| l_proc, 30);
896 raise;
897 end if;
898 hr_utility.set_location(' Leaving:'|| l_proc, 40);
899 end chk_recurring_bonus_period;
900
901 --
902 -- ------------------------------------------------------------------------------
903 -- |--------------------< chk_non_recurring_bonus_period >---------------------|
904 -- ------------------------------------------------------------------------------
905 --
906 procedure chk_non_recurring_bonus_period
907 (p_pension_type_id in pqp_pension_types_f.pension_type_id%TYPE
908 ,p_non_recurring_bonus_period in pqp_pension_types_f.non_recurring_bonus_period%TYPE
909 ,p_effective_date in date
910 ,p_validation_start_date in date
911 ,p_validation_end_date in date
912 ) is
913 --
914 l_proc varchar2(72) := g_package||'chk_non_recurring_bonus_period';
915 l_api_updating boolean;
916 --
917 begin
918 hr_utility.set_location('Entering:'|| l_proc, 10);
919 --
920 -- Check mandatory parameters have been set
921 --
922 hr_api.mandatory_arg_error
923 (p_api_name => l_proc
924 ,p_argument => 'effective date'
925 ,p_argument_value => p_effective_date
926 );
927 --
928 -- Only proceed with validation if:
929 -- a) During update, the value has actually changed to
930 -- another not null value.
931 -- b) During insert, the type_id is not null.
932 --
933 if (((p_pension_type_id is not null) and
934 nvl(pqp_pty_shd.g_old_rec.non_recurring_bonus_period,
935 hr_api.g_varchar2) <> nvl(p_non_recurring_bonus_period,
936 hr_api.g_varchar2))
937 or
938 (p_pension_type_id is null)) then
939 --
940 -- If non_recurring_bonus_period is not null then
941 -- Check if the non_recurring_bonus_period value exists in hr_lookups
942 -- where the lookup_type is 'PQP_BONUS_PERIOD'
943 --
944 if p_non_recurring_bonus_period is not null then
945 if hr_api.not_exists_in_dt_hrstanlookups
946 (p_effective_date => p_effective_date
947 ,p_validation_start_date => p_validation_start_date
948 ,p_validation_end_date => p_validation_end_date
949 ,p_lookup_type => 'PQP_BONUS_PERIOD'
950 ,p_lookup_code => p_non_recurring_bonus_period
951 ) then
952 -- Error: Invalid Non Recurring Bonus Period
953 fnd_message.set_name('PQP', 'PQP_230027_INV_NON_RECUR_BONUS');
954 fnd_message.raise_error;
955 end if;
956 end if;
957 end if;
958 hr_utility.set_location(' Leaving:'|| l_proc, 20);
959 exception
960 when app_exception.application_exception then
961 if hr_multi_message.exception_add
962 (p_associated_column1 => 'PQP_PENSION_TYPES_F.NON_RECURRING_BONUS_PERIOD'
963 ) then
964 hr_utility.set_location(' Leaving:'|| l_proc, 30);
965 raise;
966 end if;
967 hr_utility.set_location(' Leaving:'|| l_proc, 40);
968 end chk_non_recurring_bonus_period;
969
970 --
971 -- ------------------------------------------------------------------------------
972 -- |---------------------< chk_pension_salary_balance >----------------------|
973 ------------------------------------------------------------------------------
974 --
975 procedure chk_pension_salary_balance
976 (p_pension_type_id in pqp_pension_types_f.pension_type_id%TYPE
977 ,p_pension_salary_balance in pqp_pension_types_f.pension_salary_balance%TYPE
978 ,p_business_group_id in pqp_pension_types_f.business_group_id%TYPE
979 ,p_legislation_code in pqp_pension_types_f.legislation_code%TYPE
980 ,p_effective_date in date
981 ,p_validation_start_date in date
982 ,p_validation_end_date in date
983 ) is
984 --
985
986 Cursor csr_chk_for_dimension
987 (c_balance_type_id in pqp_pension_types_f.pension_salary_balance%TYPE
988 ,c_business_group_id in pqp_pension_types_f.business_group_id%TYPE
989 ,c_legislation_code in pqp_pension_types_f.legislation_code%TYPE)
990 Is
991 Select 1 from pay_defined_balances
992 where balance_dimension_id =
993 (select balance_dimension_id from pay_balance_dimensions
994 where database_item_suffix = '_ASG_RUN'
995 and (( business_group_id IS NOT NULL
996 AND business_group_id = c_business_group_id
997 )
998 OR (legislation_code IS NOT NULL AND legislation_code = c_legislation_code)
999 OR (business_group_id IS NULL AND legislation_code IS NULL)
1000 )
1001 )
1002 and balance_type_id = c_balance_type_id
1003 and (( business_group_id IS NOT NULL
1004 AND business_group_id = c_business_group_id
1005 )
1006 OR ( legislation_code IS NOT NULL AND legislation_code = c_legislation_code)
1007 OR (business_group_id IS NULL AND legislation_code IS NULL)
1008 );
1009
1010 l_proc varchar2(72) := g_package||'chk_pension_salary_balance';
1011 l_api_updating boolean;
1012 l_dim_exists number;
1013 --
1014 begin
1015 hr_utility.set_location('Entering:'|| l_proc, 10);
1016 --
1017 -- Check mandatory parameters have been set
1018 --
1019 hr_api.mandatory_arg_error
1020 (p_api_name => l_proc
1021 ,p_argument => 'effective date'
1022 ,p_argument_value => p_effective_date
1023 );
1024 --
1025 -- Only proceed with validation if:
1026 -- a) During update, the value has actually changed to
1027 -- another not null value.
1028 -- b) During insert, the type_id is not null.
1029 --
1030 if (((p_pension_type_id is not null) and
1031 nvl(pqp_pty_shd.g_old_rec.pension_salary_balance,
1032 hr_api.g_number) <> nvl(p_pension_salary_balance,
1033 hr_api.g_number))
1034 or
1035 (p_pension_type_id is null)) then
1036 --
1037 -- If pension_salary_balance is not null and the legislation is NL then
1038 -- Check if the pension_salary_balance refers to a valid balance
1039 -- which has a _ASG_RUN dimension defined in defined balances
1040 --
1041 if p_pension_salary_balance is not null AND
1042 (p_legislation_code is not null and p_legislation_code = 'NL')then
1043
1044 hr_utility.set_location('Entering:'|| l_proc||' bal '||p_pension_salary_balance, 15);
1045 Open csr_chk_for_dimension(c_balance_type_id => p_pension_salary_balance
1046 ,c_business_group_id => p_business_group_id
1047 ,c_legislation_code => p_legislation_code);
1048
1049 Fetch csr_chk_for_dimension into l_dim_exists;
1050
1051 if csr_chk_for_dimension%NOTFOUND then
1052 -- Error: Invalid Pension Salary Balance
1053 close csr_chk_for_dimension;
1054 fnd_message.set_name('PQP', 'PQP_230028_INV_PEN_SAL_BALANCE');
1055 fnd_message.raise_error;
1056 else
1057 close csr_chk_for_dimension;
1058 end if;
1059 end if;
1060 end if;
1061 hr_utility.set_location(' Leaving:'|| l_proc, 20);
1062 exception
1063 when app_exception.application_exception then
1064 if hr_multi_message.exception_add
1065 (p_associated_column1 => 'PQP_PENSION_TYPES_F.PENSION_SALARY_BALANCE'
1066 ) then
1067 hr_utility.set_location(' Leaving:'|| l_proc, 30);
1068 raise;
1069 end if;
1070 hr_utility.set_location(' Leaving:'|| l_proc, 40);
1071 end chk_pension_salary_balance;
1072
1073 --
1074 -- ------------------------------------------------------------------------------
1075 -- |-------------------------< chk_ee_age_threshold >---------------------------|
1076 -- ------------------------------------------------------------------------------
1077 --
1078 procedure chk_ee_age_threshold
1079 (p_pension_type_id in pqp_pension_types_f.pension_type_id%TYPE
1080 ,p_ee_age_threshold in pqp_pension_types_f.ee_age_threshold%TYPE
1081 ,p_effective_date in date
1082 ,p_validation_start_date in date
1083 ,p_validation_end_date in date
1084 ) is
1085 --
1086 l_proc varchar2(72) := g_package||'chk_ee_age_threshold';
1087 l_api_updating boolean;
1088 --
1089 begin
1090 hr_utility.set_location('Entering:'|| l_proc, 10);
1091 --
1092 -- Check mandatory parameters have been set
1093 --
1094 hr_api.mandatory_arg_error
1095 (p_api_name => l_proc
1096 ,p_argument => 'effective date'
1097 ,p_argument_value => p_effective_date
1098 );
1099 --
1100 -- Only proceed with validation if:
1101 -- a) During update, the value has actually changed to
1102 -- another not null value.
1103 -- b) During insert, the type_id is not null.
1104 --
1105 if (((p_pension_type_id is not null) and
1106 nvl(pqp_pty_shd.g_old_rec.ee_age_threshold,
1107 hr_api.g_varchar2) <> nvl(p_ee_age_threshold,
1108 hr_api.g_varchar2))
1109 or
1110 (p_pension_type_id is null)) then
1111 --
1112 -- If ee_age_threshold is not null then
1113 -- Check if the ee_age_threshold value exists in hr_lookups
1114 -- where the lookup_type is 'PQP_YES_NO'
1115 --
1116 if p_ee_age_threshold is not null then
1117 if hr_api.not_exists_in_dt_hrstanlookups
1118 (p_effective_date => p_effective_date
1119 ,p_validation_start_date => p_validation_start_date
1120 ,p_validation_end_date => p_validation_end_date
1121 ,p_lookup_type => 'PQP_YES_NO'
1122 ,p_lookup_code => p_ee_age_threshold
1123 ) then
1124 -- Error: Invalid Age Dependant Employee Threshold
1125 fnd_message.set_name('PQP', 'PQP_230130_INV_EE_AGE_THRESH');
1126 fnd_message.raise_error;
1127 end if;
1128 end if;
1129 end if;
1130 hr_utility.set_location(' Leaving:'|| l_proc, 20);
1131 exception
1132 when app_exception.application_exception then
1133 if hr_multi_message.exception_add
1134 (p_associated_column1 => 'PQP_PENSION_TYPES_F.EE_AGE_THRESHOLD'
1135 ) then
1136 hr_utility.set_location(' Leaving:'|| l_proc, 30);
1137 raise;
1138 end if;
1139 hr_utility.set_location(' Leaving:'|| l_proc, 40);
1140 end chk_ee_age_threshold;
1141
1142 --
1143 -- ------------------------------------------------------------------------------
1144 -- |-------------------------< chk_er_age_threshold >---------------------------|
1145 -- ------------------------------------------------------------------------------
1146 --
1147 procedure chk_er_age_threshold
1148 (p_pension_type_id in pqp_pension_types_f.pension_type_id%TYPE
1149 ,p_er_age_threshold in pqp_pension_types_f.ee_age_threshold%TYPE
1150 ,p_effective_date in date
1151 ,p_validation_start_date in date
1152 ,p_validation_end_date in date
1153 ) is
1154 --
1155 l_proc varchar2(72) := g_package||'chk_er_age_threshold';
1156 l_api_updating boolean;
1157 --
1158 begin
1159 hr_utility.set_location('Entering:'|| l_proc, 10);
1160 --
1161 -- Check mandatory parameters have been set
1162 --
1163 hr_api.mandatory_arg_error
1164 (p_api_name => l_proc
1165 ,p_argument => 'effective date'
1166 ,p_argument_value => p_effective_date
1167 );
1168 --
1169 -- Only proceed with validation if:
1170 -- a) During update, the value has actually changed to
1171 -- another not null value.
1172 -- b) During insert, the type_id is not null.
1173 --
1174 if (((p_pension_type_id is not null) and
1175 nvl(pqp_pty_shd.g_old_rec.er_age_threshold,
1176 hr_api.g_varchar2) <> nvl(p_er_age_threshold,
1177 hr_api.g_varchar2))
1178 or
1179 (p_pension_type_id is null)) then
1180 --
1181 -- If er_age_threshold is not null then
1182 -- Check if the er_age_threshold value exists in hr_lookups
1183 -- where the lookup_type is 'PQP_YES_NO'
1184 --
1185 if p_er_age_threshold is not null then
1186 if hr_api.not_exists_in_dt_hrstanlookups
1187 (p_effective_date => p_effective_date
1188 ,p_validation_start_date => p_validation_start_date
1189 ,p_validation_end_date => p_validation_end_date
1190 ,p_lookup_type => 'PQP_YES_NO'
1191 ,p_lookup_code => p_er_age_threshold
1192 ) then
1193 -- Error: Invalid Age Dependant Employer Threshold
1194 fnd_message.set_name('PQP', 'PQP_230131_INV_ER_AGE_THRESH');
1195 fnd_message.raise_error;
1196 end if;
1197 end if;
1198 end if;
1199 hr_utility.set_location(' Leaving:'|| l_proc, 20);
1200 exception
1201 when app_exception.application_exception then
1202 if hr_multi_message.exception_add
1203 (p_associated_column1 => 'PQP_PENSION_TYPES_F.ER_AGE_THRESHOLD'
1204 ) then
1205 hr_utility.set_location(' Leaving:'|| l_proc, 30);
1206 raise;
1207 end if;
1208 hr_utility.set_location(' Leaving:'|| l_proc, 40);
1209 end chk_er_age_threshold;
1210
1211 --
1212 -- ------------------------------------------------------------------------------
1213 -- |-----------------------< chk_ee_age_contribution >--------------------------|
1214 -- ------------------------------------------------------------------------------
1215 --
1216 procedure chk_ee_age_contribution
1217 (p_pension_type_id in pqp_pension_types_f.pension_type_id%TYPE
1218 ,p_ee_age_contribution in pqp_pension_types_f.ee_age_threshold%TYPE
1219 ,p_effective_date in date
1220 ,p_validation_start_date in date
1221 ,p_validation_end_date in date
1222 ) is
1223 --
1224 l_proc varchar2(72) := g_package||'chk_ee_age_contribution';
1225 l_api_updating boolean;
1226 --
1227 begin
1228 hr_utility.set_location('Entering:'|| l_proc, 10);
1229 --
1230 -- Check mandatory parameters have been set
1231 --
1232 hr_api.mandatory_arg_error
1233 (p_api_name => l_proc
1234 ,p_argument => 'effective date'
1235 ,p_argument_value => p_effective_date
1236 );
1237 --
1238 -- Only proceed with validation if:
1239 -- a) During update, the value has actually changed to
1240 -- another not null value.
1241 -- b) During insert, the type_id is not null.
1242 --
1243 if (((p_pension_type_id is not null) and
1244 nvl(pqp_pty_shd.g_old_rec.ee_age_contribution,
1245 hr_api.g_varchar2) <> nvl(p_ee_age_contribution,
1246 hr_api.g_varchar2))
1247 or
1248 (p_pension_type_id is null)) then
1249 --
1250 -- If ee_age_contribution is not null then
1251 -- Check if the ee_age_contribution value exists in hr_lookups
1252 -- where the lookup_type is 'PQP_YES_NO'
1253 --
1254 if p_ee_age_contribution is not null then
1255 if hr_api.not_exists_in_dt_hrstanlookups
1256 (p_effective_date => p_effective_date
1257 ,p_validation_start_date => p_validation_start_date
1258 ,p_validation_end_date => p_validation_end_date
1259 ,p_lookup_type => 'PQP_YES_NO'
1260 ,p_lookup_code => p_ee_age_contribution
1261 ) then
1262 -- Error: Invalid Age Dependant Employee Contribution
1263 fnd_message.set_name('PQP', 'PQP_230132_INV_EE_AGE_CONTRIB');
1264 fnd_message.raise_error;
1265 end if;
1266 end if;
1267 end if;
1268 hr_utility.set_location(' Leaving:'|| l_proc, 20);
1269 exception
1270 when app_exception.application_exception then
1271 if hr_multi_message.exception_add
1272 (p_associated_column1 => 'PQP_PENSION_TYPES_F.EE_AGE_CONTRIBUTION'
1273 ) then
1274 hr_utility.set_location(' Leaving:'|| l_proc, 30);
1275 raise;
1276 end if;
1277 hr_utility.set_location(' Leaving:'|| l_proc, 40);
1278 end chk_ee_age_contribution;
1279
1280 --
1281 -- ------------------------------------------------------------------------------
1282 -- |------------------------< chk_er_age_contribution >--------------------------|
1283 -- ------------------------------------------------------------------------------
1284 --
1285 procedure chk_er_age_contribution
1286 (p_pension_type_id in pqp_pension_types_f.pension_type_id%TYPE
1287 ,p_er_age_contribution in pqp_pension_types_f.ee_age_threshold%TYPE
1288 ,p_effective_date in date
1289 ,p_validation_start_date in date
1290 ,p_validation_end_date in date
1291 ) is
1292 --
1293 l_proc varchar2(72) := g_package||'chk_er_age_contribution';
1294 l_api_updating boolean;
1295 --
1296 begin
1297 hr_utility.set_location('Entering:'|| l_proc, 10);
1298 --
1299 -- Check mandatory parameters have been set
1300 --
1301 hr_api.mandatory_arg_error
1302 (p_api_name => l_proc
1303 ,p_argument => 'effective date'
1304 ,p_argument_value => p_effective_date
1305 );
1306 --
1307 -- Only proceed with validation if:
1308 -- a) During update, the value has actually changed to
1309 -- another not null value.
1310 -- b) During insert, the type_id is not null.
1311 --
1312 if (((p_pension_type_id is not null) and
1313 nvl(pqp_pty_shd.g_old_rec.er_age_contribution,
1314 hr_api.g_varchar2) <> nvl(p_er_age_contribution,
1315 hr_api.g_varchar2))
1316 or
1317 (p_pension_type_id is null)) then
1318 --
1319 -- If er_age_contribution is not null then
1320 -- Check if the er_age_contribution value exists in hr_lookups
1321 -- where the lookup_type is 'PQP_YES_NO'
1322 --
1323 if p_er_age_contribution is not null then
1324 if hr_api.not_exists_in_dt_hrstanlookups
1325 (p_effective_date => p_effective_date
1326 ,p_validation_start_date => p_validation_start_date
1327 ,p_validation_end_date => p_validation_end_date
1328 ,p_lookup_type => 'PQP_YES_NO'
1329 ,p_lookup_code => p_er_age_contribution
1330 ) then
1331 -- Error: Invalid Age Dependant Employer Contribution
1332 fnd_message.set_name('PQP', 'PQP_230133_INV_ER_AGE_CONTRIB');
1333 fnd_message.raise_error;
1334 end if;
1335 end if;
1336 end if;
1337 hr_utility.set_location(' Leaving:'|| l_proc, 20);
1338 exception
1339 when app_exception.application_exception then
1340 if hr_multi_message.exception_add
1341 (p_associated_column1 => 'PQP_PENSION_TYPES_F.ER_AGE_CONTRIBUTION'
1342 ) then
1343 hr_utility.set_location(' Leaving:'|| l_proc, 30);
1344 raise;
1345 end if;
1346 hr_utility.set_location(' Leaving:'|| l_proc, 40);
1347 end chk_er_age_contribution;
1348
1349 --
1350 -- ---------------------------------------------------------------------------
1351 -- |----------------------< set_end_date_fpus >--------------------------|
1352 -- ---------------------------------------------------------------------------
1353 --
1354
1355 Procedure set_end_date_fpus
1356 (
1357 p_pension_type_id in pqp_pension_types_f.pension_type_id%TYPE
1358 ,p_pension_sub_category in pqp_pension_types_f.pension_sub_category%TYPE
1359 ,p_effective_date in date
1360 ,p_validation_start_date in date
1361 ,p_validation_end_date out nocopy date
1362 ) IS
1363
1364 cursor c_get_end_date is
1365 select nvl(end_date_active,null)
1366 from fnd_lookup_values
1367 where lookup_type = 'PQP_PENSION_SUB_CATEGORY'
1368 and lookup_code = p_pension_sub_category
1369 and p_effective_date between start_date_active
1370 and nvl(end_date_active,hr_api.g_eot)
1371 and nvl(enabled_flag,'N') = 'Y';
1372
1373
1374 l_end_date_active date;
1375 l_proc varchar2(50) := g_package||'set_end_date_fpus';
1376
1377 Begin
1378
1379 hr_utility.set_location(' entering :'|| l_proc, 10);
1380
1381 open c_get_end_date;
1382 fetch c_get_end_date into l_end_date_active;
1383 hr_utility.set_location(' l_end_date_active :'|| l_end_date_active, 20);
1384 if (c_get_end_date%FOUND and l_end_date_active is not null) then
1385 close c_get_end_date;
1386 hr_utility.set_location(' l_end_date_active :'|| l_end_date_active, 30);
1387 p_validation_end_date := l_end_date_active;
1388 hr_utility.set_location(' p_validation_end_date :'|| p_validation_end_date, 40);
1389 else
1390 close c_get_end_date;
1391 end if;
1392
1393 End set_end_date_fpus;
1394
1395
1396
1397 --
1398 -- ---------------------------------------------------------------------------
1399 -- |----------------------< set_security_group_id >--------------------------|
1400 -- ---------------------------------------------------------------------------
1401 --
1402 Procedure set_security_group_id
1403 (p_pension_type_id in number
1404 ,p_associated_column1 in varchar2 default null
1405 ) is
1406 --
1407 -- Declare cursor
1408 --
1409 cursor csr_sec_grp is
1410 select pbg.security_group_id,
1411 pbg.legislation_code
1412 from per_business_groups_perf pbg
1413 , pqp_pension_types_f pty
1414 where pty.pension_type_id = p_pension_type_id
1415 and pbg.business_group_id = pty.business_group_id;
1416 --
1417 -- Declare local variables
1418 --
1419 l_security_group_id number;
1420 l_proc varchar2(72) := g_package||'set_security_group_id';
1421 l_legislation_code varchar2(150);
1422 --
1423 begin
1424 --
1425 hr_utility.set_location('Entering:'|| l_proc, 10);
1426 --
1427 -- Ensure that all the mandatory parameter are not null
1428 --
1429 hr_api.mandatory_arg_error
1430 (p_api_name => l_proc
1431 ,p_argument => 'pension_type_id'
1432 ,p_argument_value => p_pension_type_id
1433 );
1434 --
1435 open csr_sec_grp;
1436 fetch csr_sec_grp into l_security_group_id
1437 , l_legislation_code;
1438 --
1439 if csr_sec_grp%notfound then
1440 --
1441 close csr_sec_grp;
1442 --
1443 -- The primary key is invalid therefore we must error
1444 --
1445 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
1446 hr_multi_message.add
1447 (p_associated_column1
1448 => nvl(p_associated_column1,'PENSION_TYPE_ID')
1449 );
1450 --
1451 else
1452 close csr_sec_grp;
1453 --
1454 -- Set the security_group_id in CLIENT_INFO
1455 --
1456 hr_api.set_security_group_id
1457 (p_security_group_id => l_security_group_id
1458 );
1459 --
1460 -- Set the sessions legislation context in HR_SESSION_DATA
1461 --
1462 hr_api.set_legislation_context(l_legislation_code);
1463 end if;
1464 --
1465 hr_utility.set_location(' Leaving:'|| l_proc, 20);
1466 --
1467 end set_security_group_id;
1468 --
1469 -- ---------------------------------------------------------------------------
1470 -- |---------------------< return_legislation_code >-------------------------|
1471 -- ---------------------------------------------------------------------------
1472 --
1473 Function return_legislation_code
1474 (p_pension_type_id in number
1475 )
1476 Return Varchar2 Is
1477 --
1478 -- Declare cursor
1479 --
1480 cursor csr_leg_code is
1481 select pbg.legislation_code
1482 from per_business_groups_perf pbg
1483 , pqp_pension_types_f pty
1484 where pty.pension_type_id = p_pension_type_id
1485 and pbg.business_group_id (+) = pty.business_group_id;
1486 --
1487 -- Declare local variables
1488 --
1489 l_legislation_code varchar2(150);
1490 l_proc varchar2(72) := g_package||'return_legislation_code';
1491 --
1492 Begin
1493 --
1494 hr_utility.set_location('Entering:'|| l_proc, 10);
1495 --
1496 -- Ensure that all the mandatory parameter are not null
1497 --
1498 hr_api.mandatory_arg_error
1499 (p_api_name => l_proc
1500 ,p_argument => 'pension_type_id'
1501 ,p_argument_value => p_pension_type_id
1502 );
1503 --
1504 if ( nvl(pqp_pty_bus.g_pension_type_id, hr_api.g_number)
1505 = p_pension_type_id) then
1506 --
1507 -- The legislation code has already been found with a previous
1508 -- call to this function. Just return the value in the global
1509 -- variable.
1510 --
1511 l_legislation_code := pqp_pty_bus.g_legislation_code;
1512 hr_utility.set_location(l_proc, 20);
1513 else
1514 --
1515 -- The ID is different to the last call to this function
1516 -- or this is the first call to this function.
1517 --
1518 open csr_leg_code;
1519 fetch csr_leg_code into l_legislation_code;
1520 --
1521 if csr_leg_code%notfound then
1522 --
1523 -- The primary key is invalid therefore we must error
1524 --
1525 close csr_leg_code;
1526 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
1527 fnd_message.raise_error;
1528 end if;
1529 hr_utility.set_location(l_proc,30);
1530 --
1531 -- Set the global variables so the values are
1532 -- available for the next call to this function.
1533 --
1534 close csr_leg_code;
1535 pqp_pty_bus.g_pension_type_id := p_pension_type_id;
1536 pqp_pty_bus.g_legislation_code := l_legislation_code;
1537 end if;
1538 hr_utility.set_location(' Leaving:'|| l_proc, 40);
1539 return l_legislation_code;
1540 end return_legislation_code;
1541 --
1542 -- ----------------------------------------------------------------------------
1543 -- |-----------------------------< chk_ddf >----------------------------------|
1544 -- ----------------------------------------------------------------------------
1545 --
1546 -- Description:
1547 -- Validates all the Developer Descriptive Flexfield values.
1548 --
1549 -- Prerequisites:
1550 -- All other columns have been validated. Must be called as the
1551 -- last step from insert_validate and update_validate.
1552 --
1553 -- In Arguments:
1554 -- p_rec
1555 --
1556 -- Post Success:
1557 -- If the Developer Descriptive Flexfield structure column and data values
1558 -- are all valid this procedure will end normally and processing will
1559 -- continue.
1560 --
1561 -- Post Failure:
1562 -- If the Developer Descriptive Flexfield structure column value or any of
1563 -- the data values are invalid then an application error is raised as
1564 -- a PL/SQL exception.
1565 --
1566 -- Access Status:
1567 -- Internal Row Handler Use Only.
1568 --
1569 -- ----------------------------------------------------------------------------
1570 procedure chk_ddf
1571 (p_rec in pqp_pty_shd.g_rec_type
1572 ) is
1573 --
1574 l_proc varchar2(72) := g_package || 'chk_ddf';
1575 --
1576 begin
1577 hr_utility.set_location('Entering:'||l_proc,10);
1578 --
1579 if ((p_rec.pension_type_id is not null) and (
1580 nvl(pqp_pty_shd.g_old_rec.pty_information_category, hr_api.g_varchar2) <>
1581 nvl(p_rec.pty_information_category, hr_api.g_varchar2) or
1582 nvl(pqp_pty_shd.g_old_rec.pty_information1, hr_api.g_varchar2) <>
1583 nvl(p_rec.pty_information1, hr_api.g_varchar2) or
1584 nvl(pqp_pty_shd.g_old_rec.pty_information2, hr_api.g_varchar2) <>
1585 nvl(p_rec.pty_information2, hr_api.g_varchar2) or
1586 nvl(pqp_pty_shd.g_old_rec.pty_information3, hr_api.g_varchar2) <>
1587 nvl(p_rec.pty_information3, hr_api.g_varchar2) or
1588 nvl(pqp_pty_shd.g_old_rec.pty_information4, hr_api.g_varchar2) <>
1589 nvl(p_rec.pty_information4, hr_api.g_varchar2) or
1590 nvl(pqp_pty_shd.g_old_rec.pty_information5, hr_api.g_varchar2) <>
1591 nvl(p_rec.pty_information5, hr_api.g_varchar2) or
1592 nvl(pqp_pty_shd.g_old_rec.pty_information6, hr_api.g_varchar2) <>
1593 nvl(p_rec.pty_information6, hr_api.g_varchar2) or
1594 nvl(pqp_pty_shd.g_old_rec.pty_information7, hr_api.g_varchar2) <>
1595 nvl(p_rec.pty_information7, hr_api.g_varchar2) or
1596 nvl(pqp_pty_shd.g_old_rec.pty_information8, hr_api.g_varchar2) <>
1597 nvl(p_rec.pty_information8, hr_api.g_varchar2) or
1598 nvl(pqp_pty_shd.g_old_rec.pty_information9, hr_api.g_varchar2) <>
1599 nvl(p_rec.pty_information9, hr_api.g_varchar2) or
1600 nvl(pqp_pty_shd.g_old_rec.pty_information10, hr_api.g_varchar2) <>
1601 nvl(p_rec.pty_information10, hr_api.g_varchar2) or
1602 nvl(pqp_pty_shd.g_old_rec.pty_information11, hr_api.g_varchar2) <>
1603 nvl(p_rec.pty_information11, hr_api.g_varchar2) or
1604 nvl(pqp_pty_shd.g_old_rec.pty_information12, hr_api.g_varchar2) <>
1605 nvl(p_rec.pty_information12, hr_api.g_varchar2) or
1606 nvl(pqp_pty_shd.g_old_rec.pty_information13, hr_api.g_varchar2) <>
1607 nvl(p_rec.pty_information13, hr_api.g_varchar2) or
1608 nvl(pqp_pty_shd.g_old_rec.pty_information14, hr_api.g_varchar2) <>
1609 nvl(p_rec.pty_information14, hr_api.g_varchar2) or
1610 nvl(pqp_pty_shd.g_old_rec.pty_information15, hr_api.g_varchar2) <>
1611 nvl(p_rec.pty_information15, hr_api.g_varchar2) or
1612 nvl(pqp_pty_shd.g_old_rec.pty_information16, hr_api.g_varchar2) <>
1613 nvl(p_rec.pty_information16, hr_api.g_varchar2) or
1614 nvl(pqp_pty_shd.g_old_rec.pty_information17, hr_api.g_varchar2) <>
1615 nvl(p_rec.pty_information17, hr_api.g_varchar2) or
1616 nvl(pqp_pty_shd.g_old_rec.pty_information18, hr_api.g_varchar2) <>
1617 nvl(p_rec.pty_information18, hr_api.g_varchar2) or
1618 nvl(pqp_pty_shd.g_old_rec.pty_information19, hr_api.g_varchar2) <>
1619 nvl(p_rec.pty_information19, hr_api.g_varchar2) or
1620 nvl(pqp_pty_shd.g_old_rec.pty_information20, hr_api.g_varchar2) <>
1621 nvl(p_rec.pty_information20, hr_api.g_varchar2) ))
1622 or (p_rec.pension_type_id is null) then
1623 --
1624 -- Only execute the validation if absolutely necessary:
1625 -- a) During update, the structure column value or any
1626 -- of the attribute values have actually changed.
1627 -- b) During insert.
1628 --
1629 hr_dflex_utility.ins_or_upd_descflex_attribs
1630 (p_appl_short_name => 'PQP'
1631 ,p_descflex_name => 'Pension Type Developer DF'
1632 ,p_attribute_category => p_rec.pty_information_category
1633 ,p_attribute1_name => 'PTY_INFORMATION1'
1634 ,p_attribute1_value => p_rec.pty_information1
1635 ,p_attribute2_name => 'PTY_INFORMATION2'
1636 ,p_attribute2_value => p_rec.pty_information2
1637 ,p_attribute3_name => 'PTY_INFORMATION3'
1638 ,p_attribute3_value => p_rec.pty_information3
1639 ,p_attribute4_name => 'PTY_INFORMATION4'
1640 ,p_attribute4_value => p_rec.pty_information4
1641 ,p_attribute5_name => 'PTY_INFORMATION5'
1642 ,p_attribute5_value => p_rec.pty_information5
1643 ,p_attribute6_name => 'PTY_INFORMATION6'
1644 ,p_attribute6_value => p_rec.pty_information6
1645 ,p_attribute7_name => 'PTY_INFORMATION7'
1646 ,p_attribute7_value => p_rec.pty_information7
1647 ,p_attribute8_name => 'PTY_INFORMATION8'
1648 ,p_attribute8_value => p_rec.pty_information8
1649 ,p_attribute9_name => 'PTY_INFORMATION9'
1650 ,p_attribute9_value => p_rec.pty_information9
1651 ,p_attribute10_name => 'PTY_INFORMATION10'
1652 ,p_attribute10_value => p_rec.pty_information10
1653 ,p_attribute11_name => 'PTY_INFORMATION11'
1654 ,p_attribute11_value => p_rec.pty_information11
1655 ,p_attribute12_name => 'PTY_INFORMATION12'
1656 ,p_attribute12_value => p_rec.pty_information12
1657 ,p_attribute13_name => 'PTY_INFORMATION13'
1658 ,p_attribute13_value => p_rec.pty_information13
1659 ,p_attribute14_name => 'PTY_INFORMATION14'
1660 ,p_attribute14_value => p_rec.pty_information14
1661 ,p_attribute15_name => 'PTY_INFORMATION15'
1662 ,p_attribute15_value => p_rec.pty_information15
1663 ,p_attribute16_name => 'PTY_INFORMATION16'
1664 ,p_attribute16_value => p_rec.pty_information16
1665 ,p_attribute17_name => 'PTY_INFORMATION17'
1666 ,p_attribute17_value => p_rec.pty_information17
1667 ,p_attribute18_name => 'PTY_INFORMATION18'
1668 ,p_attribute18_value => p_rec.pty_information18
1669 ,p_attribute19_name => 'PTY_INFORMATION19'
1670 ,p_attribute19_value => p_rec.pty_information19
1671 ,p_attribute20_name => 'PTY_INFORMATION20'
1672 ,p_attribute20_value => p_rec.pty_information20
1673 );
1674 end if;
1675 --
1676 hr_utility.set_location(' Leaving:'||l_proc,20);
1677 end chk_ddf;
1678 --
1679 -- ----------------------------------------------------------------------------
1680 -- |------------------------------< chk_df >----------------------------------|
1681 -- ----------------------------------------------------------------------------
1682 --
1683 -- Description:
1684 -- Validates all the Descriptive Flexfield values.
1685 --
1686 -- Prerequisites:
1687 -- All other columns have been validated. Must be called as the
1688 -- last step from insert_validate and update_validate.
1689 --
1690 -- In Arguments:
1691 -- p_rec
1692 --
1693 -- Post Success:
1694 -- If the Descriptive Flexfield structure column and data values are
1695 -- all valid this procedure will end normally and processing will
1696 -- continue.
1697 --
1698 -- Post Failure:
1699 -- If the Descriptive Flexfield structure column value or any of
1700 -- the data values are invalid then an application error is raised as
1701 -- a PL/SQL exception.
1702 --
1703 -- Access Status:
1704 -- Internal Row Handler Use Only.
1705 --
1706 -- ----------------------------------------------------------------------------
1707 procedure chk_df
1708 (p_rec in pqp_pty_shd.g_rec_type
1709 ) is
1710 --
1711 l_proc varchar2(72) := g_package || 'chk_df';
1712 --
1713 begin
1714 hr_utility.set_location('Entering:'||l_proc,10);
1715 --
1716 if ((p_rec.pension_type_id is not null) and (
1717 nvl(pqp_pty_shd.g_old_rec.pty_attribute_category, hr_api.g_varchar2) <>
1718 nvl(p_rec.pty_attribute_category, hr_api.g_varchar2) or
1719 nvl(pqp_pty_shd.g_old_rec.pty_attribute1, hr_api.g_varchar2) <>
1720 nvl(p_rec.pty_attribute1, hr_api.g_varchar2) or
1721 nvl(pqp_pty_shd.g_old_rec.pty_attribute2, hr_api.g_varchar2) <>
1722 nvl(p_rec.pty_attribute2, hr_api.g_varchar2) or
1723 nvl(pqp_pty_shd.g_old_rec.pty_attribute3, hr_api.g_varchar2) <>
1724 nvl(p_rec.pty_attribute3, hr_api.g_varchar2) or
1725 nvl(pqp_pty_shd.g_old_rec.pty_attribute4, hr_api.g_varchar2) <>
1726 nvl(p_rec.pty_attribute4, hr_api.g_varchar2) or
1727 nvl(pqp_pty_shd.g_old_rec.pty_attribute5, hr_api.g_varchar2) <>
1728 nvl(p_rec.pty_attribute5, hr_api.g_varchar2) or
1729 nvl(pqp_pty_shd.g_old_rec.pty_attribute6, hr_api.g_varchar2) <>
1730 nvl(p_rec.pty_attribute6, hr_api.g_varchar2) or
1731 nvl(pqp_pty_shd.g_old_rec.pty_attribute7, hr_api.g_varchar2) <>
1732 nvl(p_rec.pty_attribute7, hr_api.g_varchar2) or
1733 nvl(pqp_pty_shd.g_old_rec.pty_attribute8, hr_api.g_varchar2) <>
1734 nvl(p_rec.pty_attribute8, hr_api.g_varchar2) or
1735 nvl(pqp_pty_shd.g_old_rec.pty_attribute9, hr_api.g_varchar2) <>
1736 nvl(p_rec.pty_attribute9, hr_api.g_varchar2) or
1737 nvl(pqp_pty_shd.g_old_rec.pty_attribute10, hr_api.g_varchar2) <>
1738 nvl(p_rec.pty_attribute10, hr_api.g_varchar2) or
1739 nvl(pqp_pty_shd.g_old_rec.pty_attribute11, hr_api.g_varchar2) <>
1740 nvl(p_rec.pty_attribute11, hr_api.g_varchar2) or
1741 nvl(pqp_pty_shd.g_old_rec.pty_attribute12, hr_api.g_varchar2) <>
1742 nvl(p_rec.pty_attribute12, hr_api.g_varchar2) or
1743 nvl(pqp_pty_shd.g_old_rec.pty_attribute13, hr_api.g_varchar2) <>
1744 nvl(p_rec.pty_attribute13, hr_api.g_varchar2) or
1745 nvl(pqp_pty_shd.g_old_rec.pty_attribute14, hr_api.g_varchar2) <>
1746 nvl(p_rec.pty_attribute14, hr_api.g_varchar2) or
1747 nvl(pqp_pty_shd.g_old_rec.pty_attribute15, hr_api.g_varchar2) <>
1748 nvl(p_rec.pty_attribute15, hr_api.g_varchar2) or
1749 nvl(pqp_pty_shd.g_old_rec.pty_attribute16, hr_api.g_varchar2) <>
1750 nvl(p_rec.pty_attribute16, hr_api.g_varchar2) or
1751 nvl(pqp_pty_shd.g_old_rec.pty_attribute17, hr_api.g_varchar2) <>
1752 nvl(p_rec.pty_attribute17, hr_api.g_varchar2) or
1753 nvl(pqp_pty_shd.g_old_rec.pty_attribute18, hr_api.g_varchar2) <>
1754 nvl(p_rec.pty_attribute18, hr_api.g_varchar2) or
1755 nvl(pqp_pty_shd.g_old_rec.pty_attribute19, hr_api.g_varchar2) <>
1756 nvl(p_rec.pty_attribute19, hr_api.g_varchar2) or
1757 nvl(pqp_pty_shd.g_old_rec.pty_attribute20, hr_api.g_varchar2) <>
1758 nvl(p_rec.pty_attribute20, hr_api.g_varchar2) ))
1759 or (p_rec.pension_type_id is null) then
1760 --
1761 -- Only execute the validation if absolutely necessary:
1762 -- a) During update, the structure column value or any
1763 -- of the attribute values have actually changed.
1764 -- b) During insert.
1765 --
1766 hr_dflex_utility.ins_or_upd_descflex_attribs
1767 (p_appl_short_name => 'PQP'
1768 ,p_descflex_name => 'Pension Type DF'
1769 ,p_attribute_category => p_rec.pty_attribute_category
1770 ,p_attribute1_name => 'PTY_ATTRIBUTE1'
1771 ,p_attribute1_value => p_rec.pty_attribute1
1772 ,p_attribute2_name => 'PTY_ATTRIBUTE2'
1773 ,p_attribute2_value => p_rec.pty_attribute2
1774 ,p_attribute3_name => 'PTY_ATTRIBUTE3'
1775 ,p_attribute3_value => p_rec.pty_attribute3
1776 ,p_attribute4_name => 'PTY_ATTRIBUTE4'
1777 ,p_attribute4_value => p_rec.pty_attribute4
1778 ,p_attribute5_name => 'PTY_ATTRIBUTE5'
1779 ,p_attribute5_value => p_rec.pty_attribute5
1780 ,p_attribute6_name => 'PTY_ATTRIBUTE6'
1781 ,p_attribute6_value => p_rec.pty_attribute6
1782 ,p_attribute7_name => 'PTY_ATTRIBUTE7'
1783 ,p_attribute7_value => p_rec.pty_attribute7
1784 ,p_attribute8_name => 'PTY_ATTRIBUTE8'
1785 ,p_attribute8_value => p_rec.pty_attribute8
1786 ,p_attribute9_name => 'PTY_ATTRIBUTE9'
1787 ,p_attribute9_value => p_rec.pty_attribute9
1788 ,p_attribute10_name => 'PTY_ATTRIBUTE10'
1789 ,p_attribute10_value => p_rec.pty_attribute10
1790 ,p_attribute11_name => 'PTY_ATTRIBUTE11'
1791 ,p_attribute11_value => p_rec.pty_attribute11
1792 ,p_attribute12_name => 'PTY_ATTRIBUTE12'
1793 ,p_attribute12_value => p_rec.pty_attribute12
1794 ,p_attribute13_name => 'PTY_ATTRIBUTE13'
1795 ,p_attribute13_value => p_rec.pty_attribute13
1796 ,p_attribute14_name => 'PTY_ATTRIBUTE14'
1797 ,p_attribute14_value => p_rec.pty_attribute14
1798 ,p_attribute15_name => 'PTY_ATTRIBUTE15'
1799 ,p_attribute15_value => p_rec.pty_attribute15
1800 ,p_attribute16_name => 'PTY_ATTRIBUTE16'
1801 ,p_attribute16_value => p_rec.pty_attribute16
1802 ,p_attribute17_name => 'PTY_ATTRIBUTE17'
1803 ,p_attribute17_value => p_rec.pty_attribute17
1804 ,p_attribute18_name => 'PTY_ATTRIBUTE18'
1805 ,p_attribute18_value => p_rec.pty_attribute18
1806 ,p_attribute19_name => 'PTY_ATTRIBUTE19'
1807 ,p_attribute19_value => p_rec.pty_attribute19
1808 ,p_attribute20_name => 'PTY_ATTRIBUTE20'
1809 ,p_attribute20_value => p_rec.pty_attribute20
1810 );
1811 end if;
1812 --
1813 hr_utility.set_location(' Leaving:'||l_proc,20);
1814 end chk_df;
1815 --
1816 -- ----------------------------------------------------------------------------
1817 -- |-----------------------< chk_non_updateable_args >------------------------|
1818 -- ----------------------------------------------------------------------------
1819 -- {Start Of Comments}
1820 --
1821 -- Description:
1822 -- This procedure is used to ensure that non updateable attributes have
1823 -- not been updated. If an attribute has been updated an error is generated.
1824 --
1825 -- Pre Conditions:
1826 -- g_old_rec has been populated with details of the values currently in
1827 -- the database.
1828 --
1829 -- In Arguments:
1830 -- p_rec has been populated with the updated values the user would like the
1831 -- record set to.
1832 --
1833 -- Post Success:
1834 -- Processing continues if all the non updateable attributes have not
1835 -- changed.
1836 --
1837 -- Post Failure:
1838 -- An application error is raised if any of the non updatable attributes
1839 -- have been altered.
1840 --
1841 -- {End Of Comments}
1842 -- ----------------------------------------------------------------------------
1843 Procedure chk_non_updateable_args
1844 (p_effective_date in date
1845 ,p_rec in pqp_pty_shd.g_rec_type
1846 ) IS
1847 --
1848 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
1849 --
1850 Begin
1851 --
1852 -- Only proceed with the validation if a row exists for the current
1853 -- record in the HR Schema.
1854 --
1855 IF NOT pqp_pty_shd.api_updating
1856 (p_pension_type_id => p_rec.pension_type_id
1857 ,p_effective_date => p_effective_date
1858 ,p_object_version_number => p_rec.object_version_number
1859 ) THEN
1860 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
1861 fnd_message.set_token('PROCEDURE ', l_proc);
1862 fnd_message.set_token('STEP ', '5');
1863 fnd_message.raise_error;
1864 END IF;
1865 --
1866 -- EDIT_HERE: Add checks to ensure non-updateable args have
1867 -- not been updated.
1868 --
1869 End chk_non_updateable_args;
1870 --
1871 -- ----------------------------------------------------------------------------
1872 -- |--------------------------< dt_update_validate >--------------------------|
1873 -- ----------------------------------------------------------------------------
1874 -- {Start Of Comments}
1875 --
1876 -- Description:
1877 -- This procedure is used for referential integrity of datetracked
1878 -- parent entities when a datetrack update operation is taking place
1879 -- and where there is no cascading of update defined for this entity.
1880 --
1881 -- Prerequisites:
1882 -- This procedure is called from the update_validate.
1883 --
1884 -- In Parameters:
1885 --
1886 -- Post Success:
1887 -- Processing continues.
1888 --
1889 -- Post Failure:
1890 --
1891 -- Developer Implementation Notes:
1892 -- This procedure should not need maintenance unless the HR Schema model
1893 -- changes.
1894 --
1895 -- Access Status:
1896 -- Internal Row Handler Use Only.
1897 --
1898 -- {End Of Comments}
1899 -- ----------------------------------------------------------------------------
1900 Procedure dt_update_validate
1901 (p_datetrack_mode in varchar2
1902 ,p_validation_start_date in date
1903 ,p_validation_end_date in date
1904 ) Is
1905 --
1906 l_proc varchar2(72) := g_package||'dt_update_validate';
1907 --
1908 Begin
1909 --
1910 -- Ensure that the p_datetrack_mode argument is not null
1911 --
1912 hr_api.mandatory_arg_error
1913 (p_api_name => l_proc
1914 ,p_argument => 'datetrack_mode'
1915 ,p_argument_value => p_datetrack_mode
1916 );
1917 --
1918 -- Mode will be valid, as this is checked at the start of the upd.
1919 --
1920 -- Ensure the arguments are not null
1921 --
1922 hr_api.mandatory_arg_error
1923 (p_api_name => l_proc
1924 ,p_argument => 'validation_start_date'
1925 ,p_argument_value => p_validation_start_date
1926 );
1927 --
1928 hr_api.mandatory_arg_error
1929 (p_api_name => l_proc
1930 ,p_argument => 'validation_end_date'
1931 ,p_argument_value => p_validation_end_date
1932 );
1933 --
1934 --
1935 --
1936 Exception
1937 When Others Then
1938 --
1939 -- An unhandled or unexpected error has occurred which
1940 -- we must report
1941 --
1942 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
1943 fnd_message.set_token('PROCEDURE', l_proc);
1944 fnd_message.set_token('STEP','15');
1945 fnd_message.raise_error;
1946 End dt_update_validate;
1947 --
1948 -- ----------------------------------------------------------------------------
1949 -- |--------------------------< dt_delete_validate >--------------------------|
1950 -- ----------------------------------------------------------------------------
1951 -- {Start Of Comments}
1952 --
1953 -- Description:
1954 -- This procedure is used for referential integrity of datetracked
1955 -- child entities when either a datetrack DELETE or ZAP is in operation
1956 -- and where there is no cascading of delete defined for this entity.
1957 -- For the datetrack mode of DELETE or ZAP we must ensure that no
1958 -- datetracked child rows exist between the validation start and end
1959 -- dates.
1960 --
1961 -- Prerequisites:
1962 -- This procedure is called from the delete_validate.
1963 --
1964 -- In Parameters:
1965 --
1966 -- Post Success:
1967 -- Processing continues.
1968 --
1969 -- Post Failure:
1970 -- If a row exists by determining the returning Boolean value from the
1971 -- generic dt_api.rows_exist function then we must supply an error via
1972 -- the use of the local exception handler l_rows_exist.
1973 --
1974 -- Developer Implementation Notes:
1975 -- This procedure should not need maintenance unless the HR Schema model
1976 -- changes.
1977 --
1978 -- Access Status:
1979 -- Internal Row Handler Use Only.
1980 --
1981 -- {End Of Comments}
1982 -- ----------------------------------------------------------------------------
1983 Procedure dt_delete_validate
1984 (p_pension_type_id in number
1985 ,p_datetrack_mode in varchar2
1986 ,p_validation_start_date in date
1987 ,p_validation_end_date in date
1988 ) Is
1989 --
1990 l_proc varchar2(72) := g_package||'dt_delete_validate';
1991 --
1992 Begin
1993 --
1994 -- Ensure that the p_datetrack_mode argument is not null
1995 --
1996 hr_api.mandatory_arg_error
1997 (p_api_name => l_proc
1998 ,p_argument => 'datetrack_mode'
1999 ,p_argument_value => p_datetrack_mode
2000 );
2001 --
2002 -- Only perform the validation if the datetrack mode is either
2003 -- DELETE or ZAP
2004 --
2005 If (p_datetrack_mode = hr_api.g_delete or
2006 p_datetrack_mode = hr_api.g_zap) then
2007 --
2008 --
2009 -- Ensure the arguments are not null
2010 --
2011 hr_api.mandatory_arg_error
2012 (p_api_name => l_proc
2013 ,p_argument => 'validation_start_date'
2014 ,p_argument_value => p_validation_start_date
2015 );
2016 --
2017 hr_api.mandatory_arg_error
2018 (p_api_name => l_proc
2019 ,p_argument => 'validation_end_date'
2020 ,p_argument_value => p_validation_end_date
2021 );
2022 --
2023 hr_api.mandatory_arg_error
2024 (p_api_name => l_proc
2025 ,p_argument => 'pension_type_id'
2026 ,p_argument_value => p_pension_type_id
2027 );
2028 --
2029 --
2030 --
2031 End If;
2032 --
2033 Exception
2034 When Others Then
2035 --
2036 -- An unhandled or unexpected error has occurred which
2037 -- we must report
2038 --
2039 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
2040 fnd_message.set_token('PROCEDURE', l_proc);
2041 fnd_message.set_token('STEP','15');
2042 fnd_message.raise_error;
2043 --
2044 End dt_delete_validate;
2045 --
2046 -- ----------------------------------------------------------------------------
2047 -- |----------------------< chk_startup_action >------------------------------|
2048 -- ----------------------------------------------------------------------------
2049 --
2050 -- Description:
2051 -- This procedure will check that the current action is allowed according
2052 -- to the current startup mode.
2053 --
2054 -- ----------------------------------------------------------------------------
2055 PROCEDURE chk_startup_action
2056 (p_insert IN boolean
2057 ,p_business_group_id IN number
2058 ,p_legislation_code IN varchar2
2059 ,p_legislation_subgroup IN varchar2 DEFAULT NULL) IS
2060 --
2061 BEGIN
2062 --
2063 -- Call the supporting procedure to check startup mode
2064 -- EDIT_HERE: The following call should be edited if certain types of rows
2065 -- are not permitted.
2066 IF (p_insert) THEN
2067 hr_startup_data_api_support.chk_startup_action
2068 (p_generic_allowed => TRUE
2069 ,p_startup_allowed => TRUE
2070 ,p_user_allowed => TRUE
2071 ,p_business_group_id => p_business_group_id
2072 ,p_legislation_code => p_legislation_code
2073 ,p_legislation_subgroup => p_legislation_subgroup
2074 );
2075 ELSE
2076 hr_startup_data_api_support.chk_upd_del_startup_action
2077 (p_generic_allowed => TRUE
2078 ,p_startup_allowed => TRUE
2079 ,p_user_allowed => TRUE
2080 ,p_business_group_id => p_business_group_id
2081 ,p_legislation_code => p_legislation_code
2082 ,p_legislation_subgroup => p_legislation_subgroup
2083 );
2084 END IF;
2085 --
2086 END chk_startup_action;
2087
2088
2089 -- ----------------------------------------------------------------------------
2090 -- |---------------------------< get_legislation_code >------------------------|
2091 -- ----------------------------------------------------------------------------
2092
2093 FUNCTION get_legislation_code
2094 (p_business_group_id IN NUMBER
2095 ) RETURN VARCHAR2 IS
2096 --declare local variables
2097 l_legislation_code per_business_groups.legislation_code%TYPE;
2098
2099 CURSOR c_get_leg_code IS
2100 SELECT legislation_code
2101 FROM per_business_groups_perf
2102 WHERE business_group_id =p_business_group_id;
2103
2104 BEGIN
2105 OPEN c_get_leg_code;
2106 LOOP
2107 FETCH c_get_leg_code INTO l_legislation_code;
2108 EXIT WHEN c_get_leg_code%NOTFOUND;
2109 END LOOP;
2110 CLOSE c_get_leg_code;
2111 RETURN (l_legislation_code);
2112 EXCEPTION
2113 ---------
2114 WHEN OTHERS THEN
2115 RETURN(NULL);
2116 END;
2117
2118 --
2119 -- ----------------------------------------------------------------------------
2120 -- |---------------------------< insert_validate >----------------------------|
2121 -- ----------------------------------------------------------------------------
2122 Procedure insert_validate
2123 (p_rec in pqp_pty_shd.g_rec_type
2124 ,p_effective_date in date
2125 ,p_datetrack_mode in varchar2
2126 ,p_validation_start_date in date
2127 ,p_validation_end_date in out nocopy date
2128 ) is
2129 --
2130 l_proc varchar2(72) := g_package||'insert_validate';
2131 l_legislation_code varchar2(150);
2132
2133 --
2134 Begin
2135 hr_utility.set_location('Entering:'||l_proc, 5);
2136 --
2137 -- Call all supporting business operations
2138 --
2139 --
2140 chk_startup_action(true
2141 ,p_rec.business_group_id
2142 ,p_rec.legislation_code
2143 );
2144 IF hr_startup_data_api_support.g_startup_mode
2145 NOT IN ('GENERIC','STARTUP') THEN
2146 --
2147 -- Validate Important Attributes
2148 --
2149 hr_api.validate_bus_grp_id
2150 (p_business_group_id => p_rec.business_group_id
2151 ,p_associated_column1 => pqp_pty_shd.g_tab_nam
2152 || '.BUSINESS_GROUP_ID');
2153 --
2154 -- after validating the set of important attributes,
2155 -- if Multiple Message Detection is enabled and at least
2156 -- one error has been found then abort further validation.
2157 --
2158 hr_multi_message.end_validation_set;
2159 END IF;
2160 --
2161
2162 IF p_rec.business_group_id IS NOT NULL THEN
2163 --Get the legislation Code from Business Group Id
2164 l_legislation_code := get_legislation_code(p_rec.business_group_id);
2165 ELSE
2166 l_legislation_code := p_rec.legislation_code;
2167 END IF;
2168
2169
2170 -- Check if the Pension Type Name is Unique within the table
2171 -- pqp_pension_types_f
2172 --
2173 chk_pension_type_name
2174 (p_pension_type_id => p_rec.pension_type_id
2175 ,p_pension_type_name => p_rec.pension_type_name
2176 ,p_effective_date => p_effective_date
2177 ,p_business_group_id => p_rec.business_group_id
2178 ,p_legislation_code => l_legislation_code
2179 ,p_validation_start_date => p_validation_start_date
2180 ,p_validation_end_date => p_validation_end_date
2181 );
2182
2183 --
2184 -- Validate the Pension Category Lookup Code
2185 --
2186 chk_pension_category
2187 (p_pension_type_id => p_rec.pension_type_id
2188 ,p_pension_category => p_rec.pension_category
2189 ,p_effective_date => p_effective_date
2190 ,p_validation_start_date => p_validation_start_date
2191 ,p_validation_end_date => p_validation_end_date
2192 );
2193
2194 if(l_legislation_code = 'NL') then
2195
2196 --
2197 -- Validate the Contribution Conversion Rule Lookup Code
2198 --
2199
2200 chk_contribution_conv_rule
2201 (p_pension_type_id => p_rec.pension_type_id
2202 ,p_contribution_conversion_rule => p_rec.contribution_conversion_rule
2203 ,p_effective_date => p_effective_date
2204 ,p_validation_start_date => p_validation_start_date
2205 ,p_validation_end_date => p_validation_end_date
2206 );
2207
2208
2209 --
2210 -- Validate the Threshold Conversion Rule Lookup Code
2211 --
2212 chk_threshold_conv_rule
2213 (p_pension_type_id => p_rec.pension_type_id
2214 ,p_threshold_conversion_rule => p_rec.threshold_conversion_rule
2215 ,p_effective_date => p_effective_date
2216 ,p_validation_start_date => p_validation_start_date
2217 ,p_validation_end_date => p_validation_end_date
2218 );
2219
2220 --
2221 -- Validate the Pension Provider Type Lookup Code
2222 --
2223 chk_pension_provider
2224 (p_pension_type_id => p_rec.pension_type_id
2225 ,p_pension_provider_type => p_rec.pension_provider_type
2226 ,p_effective_date => p_effective_date
2227 ,p_validation_start_date => p_validation_start_date
2228 ,p_validation_end_date => p_validation_end_date
2229 );
2230
2231 --
2232 -- Validate the Salary Calculation Method Lookup Code
2233 --
2234 chk_salary_calc_method
2235 (p_pension_type_id => p_rec.pension_type_id
2236 ,p_salary_calculation_method => p_rec.salary_calculation_method
2237 ,p_effective_date => p_effective_date
2238 ,p_validation_start_date => p_validation_start_date
2239 ,p_validation_end_date => p_validation_end_date
2240 );
2241
2242 --
2243 -- Validate the Special Pension Type Code
2244 --
2245 chk_special_pension_type_code
2246 (p_pension_type_id => p_rec.pension_type_id
2247 ,p_special_pension_type_code => p_rec.special_pension_type_code
2248 ,p_effective_date => p_effective_date
2249 ,p_validation_start_date => p_validation_start_date
2250 ,p_validation_end_date => p_validation_end_date
2251 );
2252
2253
2254 --
2255 -- Validate the Pension Sub Category
2256 --
2257 chk_pension_sub_category
2258 (p_pension_type_id => p_rec.pension_type_id
2259 ,p_pension_sub_category => p_rec.pension_sub_category
2260 ,p_effective_date => p_effective_date
2261 ,p_validation_start_date => p_validation_start_date
2262 ,p_validation_end_date => p_validation_end_date
2263 );
2264
2265
2266 --
2267 -- Validate the Pension Basis Calculation Method
2268 --
2269 chk_pension_basis_calc_method
2270 (p_pension_type_id => p_rec.pension_type_id
2271 ,p_pension_basis_calc_method => p_rec.pension_basis_calc_method
2272 ,p_effective_date => p_effective_date
2273 ,p_validation_start_date => p_validation_start_date
2274 ,p_validation_end_date => p_validation_end_date
2275 );
2276
2277
2278 --
2279 -- Validate the Previous Year Bonus Included
2280 --
2281 chk_prev_year_bonus_include
2282 (p_pension_type_id => p_rec.pension_type_id
2283 ,p_previous_year_bonus_included => p_rec.previous_year_bonus_included
2284 ,p_effective_date => p_effective_date
2285 ,p_validation_start_date => p_validation_start_date
2286 ,p_validation_end_date => p_validation_end_date
2287 );
2288
2289
2290 --
2291 -- Validate the Recurring Bonus Period
2292 --
2293 chk_recurring_bonus_period
2294 (p_pension_type_id => p_rec.pension_type_id
2295 ,p_recurring_bonus_period => p_rec.recurring_bonus_period
2296 ,p_effective_date => p_effective_date
2297 ,p_validation_start_date => p_validation_start_date
2298 ,p_validation_end_date => p_validation_end_date
2299 );
2300
2301
2302 --
2303 -- Validate the Non Recurring Bonus Period
2304 --
2305 chk_non_recurring_bonus_period
2306 (p_pension_type_id => p_rec.pension_type_id
2307 ,p_non_recurring_bonus_period => p_rec.non_recurring_bonus_period
2308 ,p_effective_date => p_effective_date
2309 ,p_validation_start_date => p_validation_start_date
2310 ,p_validation_end_date => p_validation_end_date
2311 );
2312
2313
2314 --
2315 -- Validate the Pension Salary Balance
2316 --
2317 chk_pension_salary_balance
2318 (p_pension_type_id => p_rec.pension_type_id
2319 ,p_pension_salary_balance => p_rec.pension_salary_balance
2320 ,p_business_group_id => p_rec.business_group_id
2321 ,p_legislation_code => l_legislation_code
2322 ,p_effective_date => p_effective_date
2323 ,p_validation_start_date => p_validation_start_date
2324 ,p_validation_end_date => p_validation_end_date
2325 );
2326
2327 --
2328 -- Validate the Age Dependant Employee Threshold
2329 --
2330 chk_ee_age_threshold
2331 (p_pension_type_id => p_rec.pension_type_id
2332 ,p_ee_age_threshold => p_rec.ee_age_threshold
2333 ,p_effective_date => p_effective_date
2334 ,p_validation_start_date => p_validation_start_date
2335 ,p_validation_end_date => p_validation_end_date
2336 );
2337
2338 --
2339 -- Validate the Age Dependant Employer Threshold
2340 --
2341 chk_er_age_threshold
2342 (p_pension_type_id => p_rec.pension_type_id
2343 ,p_er_age_threshold => p_rec.er_age_threshold
2344 ,p_effective_date => p_effective_date
2345 ,p_validation_start_date => p_validation_start_date
2346 ,p_validation_end_date => p_validation_end_date
2347 );
2348
2349 --
2350 -- Validate the Age Dependant Employee Contribution
2351 --
2352 chk_ee_age_contribution
2353 (p_pension_type_id => p_rec.pension_type_id
2354 ,p_ee_age_contribution => p_rec.ee_age_contribution
2355 ,p_effective_date => p_effective_date
2356 ,p_validation_start_date => p_validation_start_date
2357 ,p_validation_end_date => p_validation_end_date
2358 );
2359
2360 --
2361 -- Validate the Age Dependant Employer Contribution
2362 --
2363 chk_er_age_contribution
2364 (p_pension_type_id => p_rec.pension_type_id
2365 ,p_er_age_contribution => p_rec.er_age_contribution
2366 ,p_effective_date => p_effective_date
2367 ,p_validation_start_date => p_validation_start_date
2368 ,p_validation_end_date => p_validation_end_date
2369 );
2370
2371 if (p_rec.pension_sub_category is not null and
2372 p_rec.pension_sub_category = 'FPU_S') then
2373 --
2374 -- Set End date for FPU_S.
2375 --
2376 set_end_date_fpus
2377 (
2378 p_pension_type_id => p_rec.pension_type_id
2379 ,p_pension_sub_category => p_rec.pension_sub_category
2380 ,p_effective_date => p_effective_date
2381 ,p_validation_start_date => p_validation_start_date
2382 ,p_validation_end_date => p_validation_end_date
2383 );
2384
2385 hr_utility.set_location(' p_validation_end_date :'|| p_validation_end_date, 20);
2386
2387
2388
2389 end if;
2390
2391 end if; -- end of Validations for NL
2392
2393 --
2394 --
2395 -- Validate Dependent Attributes
2396 --
2397 --
2398 pqp_pty_bus.chk_ddf(p_rec);
2399 --
2400 pqp_pty_bus.chk_df(p_rec);
2401 --
2402 hr_utility.set_location(' Leaving:'||l_proc, 10);
2403
2404 exception
2405 when others then
2406 hr_utility.set_location('error occured : '||SQLERRM,15);
2407 raise;
2408 End insert_validate;
2409
2410
2411 --
2412 -- ----------------------------------------------------------------------------
2413 -- |---------------------------< update_validate >----------------------------|
2414 -- ----------------------------------------------------------------------------
2415 Procedure update_validate
2416 (p_rec in pqp_pty_shd.g_rec_type
2417 ,p_effective_date in date
2418 ,p_datetrack_mode in varchar2
2419 ,p_validation_start_date in date
2420 ,p_validation_end_date in out nocopy date
2421 ) is
2422 --
2423 l_proc varchar2(72) := g_package||'update_validate';
2424 l_legislation_code varchar2(150);
2425 --
2426 Begin
2427 hr_utility.set_location('Entering:'||l_proc, 5);
2428 --
2429 -- Call all supporting business operations
2430 --
2431 --
2432 chk_startup_action(false
2433 ,p_rec.business_group_id
2434 ,p_rec.legislation_code
2435 );
2436 IF hr_startup_data_api_support.g_startup_mode
2437 NOT IN ('GENERIC','STARTUP') THEN
2438 --
2439 -- Validate Important Attributes
2440 --
2441 hr_api.validate_bus_grp_id
2442 (p_business_group_id => p_rec.business_group_id
2443 ,p_associated_column1 => pqp_pty_shd.g_tab_nam
2444 || '.BUSINESS_GROUP_ID');
2445 --
2446 -- After validating the set of important attributes,
2447 -- if Multiple Message Detection is enabled and at least
2448 -- one error has been found then abort further validation.
2449 --
2450 hr_multi_message.end_validation_set;
2451 END IF;
2452 --
2453 --
2454 -- Validate Dependent Attributes
2455 --
2456 -- Call the datetrack update integrity operation
2457 --
2458 dt_update_validate
2459 (p_datetrack_mode => p_datetrack_mode
2460 ,p_validation_start_date => p_validation_start_date
2461 ,p_validation_end_date => p_validation_end_date
2462 );
2463 --
2464 chk_non_updateable_args
2465 (p_effective_date => p_effective_date
2466 ,p_rec => p_rec
2467 );
2468
2469 --
2470 -- Validate the Pension Category Lookup Code
2471 --
2472 chk_pension_category
2473 (p_pension_type_id => p_rec.pension_type_id
2474 ,p_pension_category => p_rec.pension_category
2475 ,p_effective_date => p_effective_date
2476 ,p_validation_start_date => p_validation_start_date
2477 ,p_validation_end_date => p_validation_end_date
2478 );
2479
2480 IF p_rec.business_group_id IS NOT NULL THEN
2481 --Get the legislation Code from Business Group Id
2482 l_legislation_code := get_legislation_code(p_rec.business_group_id);
2483 ELSE
2484 l_legislation_code := p_rec.legislation_code;
2485 END IF;
2486
2487
2488 if(l_legislation_code = 'NL') then
2489 --
2490 -- Validate the Contribution Conversion Rule Lookup Code
2491 --
2492 chk_contribution_conv_rule
2493 (p_pension_type_id => p_rec.pension_type_id
2494 ,p_contribution_conversion_rule => p_rec.contribution_conversion_rule
2495 ,p_effective_date => p_effective_date
2496 ,p_validation_start_date => p_validation_start_date
2497 ,p_validation_end_date => p_validation_end_date
2498 );
2499 --
2500 -- Validate the Threshold Conversion Rule Lookup Code
2501 --
2502 chk_threshold_conv_rule
2503 (p_pension_type_id => p_rec.pension_type_id
2504 ,p_threshold_conversion_rule => p_rec.threshold_conversion_rule
2505 ,p_effective_date => p_effective_date
2506 ,p_validation_start_date => p_validation_start_date
2507 ,p_validation_end_date => p_validation_end_date
2508 );
2509
2510 --
2511 -- Validate the Pension Provider Type Lookup Code
2512 --
2513 chk_pension_provider
2514 (p_pension_type_id => p_rec.pension_type_id
2515 ,p_pension_provider_type => p_rec.pension_provider_type
2516 ,p_effective_date => p_effective_date
2517 ,p_validation_start_date => p_validation_start_date
2518 ,p_validation_end_date => p_validation_end_date
2519 );
2520 --
2521 -- Validate the Salary Calculation Method Lookup Code
2522 --
2523 chk_salary_calc_method
2524 (p_pension_type_id => p_rec.pension_type_id
2525 ,p_salary_calculation_method => p_rec.salary_calculation_method
2526 ,p_effective_date => p_effective_date
2527 ,p_validation_start_date => p_validation_start_date
2528 ,p_validation_end_date => p_validation_end_date
2529 );
2530
2531 --
2532 -- Validate the Pension Basis Calculation Method
2533 --
2534 chk_pension_basis_calc_method
2535 (p_pension_type_id => p_rec.pension_type_id
2536 ,p_pension_basis_calc_method => p_rec.pension_basis_calc_method
2537 ,p_effective_date => p_effective_date
2538 ,p_validation_start_date => p_validation_start_date
2539 ,p_validation_end_date => p_validation_end_date
2540 );
2541
2542
2543 --
2544 -- Validate the Previous Year Bonus Included
2545 --
2546 chk_prev_year_bonus_include
2547 (p_pension_type_id => p_rec.pension_type_id
2548 ,p_previous_year_bonus_included => p_rec.previous_year_bonus_included
2549 ,p_effective_date => p_effective_date
2550 ,p_validation_start_date => p_validation_start_date
2551 ,p_validation_end_date => p_validation_end_date
2552 );
2553
2554
2555 --
2556 -- Validate the Recurring Bonus Period
2557 --
2558 chk_recurring_bonus_period
2559 (p_pension_type_id => p_rec.pension_type_id
2560 ,p_recurring_bonus_period => p_rec.recurring_bonus_period
2561 ,p_effective_date => p_effective_date
2562 ,p_validation_start_date => p_validation_start_date
2563 ,p_validation_end_date => p_validation_end_date
2564 );
2565
2566
2567 --
2568 -- Validate the Non Recurring Bonus Period
2569 --
2570 chk_non_recurring_bonus_period
2571 (p_pension_type_id => p_rec.pension_type_id
2572 ,p_non_recurring_bonus_period => p_rec.non_recurring_bonus_period
2573 ,p_effective_date => p_effective_date
2574 ,p_validation_start_date => p_validation_start_date
2575 ,p_validation_end_date => p_validation_end_date
2576 );
2577
2578
2579 --
2580 -- Validate the Pension Salary Balance
2581 --
2582 chk_pension_salary_balance
2583 (p_pension_type_id => p_rec.pension_type_id
2584 ,p_pension_salary_balance => p_rec.pension_salary_balance
2585 ,p_business_group_id => p_rec.business_group_id
2586 ,p_legislation_code => l_legislation_code
2587 ,p_effective_date => p_effective_date
2588 ,p_validation_start_date => p_validation_start_date
2589 ,p_validation_end_date => p_validation_end_date
2590 );
2591
2592 --
2593 -- Validate the Age Dependant Employee Threshold
2594 --
2595 chk_ee_age_threshold
2596 (p_pension_type_id => p_rec.pension_type_id
2597 ,p_ee_age_threshold => p_rec.ee_age_threshold
2598 ,p_effective_date => p_effective_date
2599 ,p_validation_start_date => p_validation_start_date
2600 ,p_validation_end_date => p_validation_end_date
2601 );
2602
2603 --
2604 -- Validate the Age Dependant Employer Threshold
2605 --
2606 chk_er_age_threshold
2607 (p_pension_type_id => p_rec.pension_type_id
2608 ,p_er_age_threshold => p_rec.er_age_threshold
2609 ,p_effective_date => p_effective_date
2610 ,p_validation_start_date => p_validation_start_date
2611 ,p_validation_end_date => p_validation_end_date
2612 );
2613
2614 --
2615 -- Validate the Age Dependant Employee Contribution
2616 --
2617 chk_ee_age_contribution
2618 (p_pension_type_id => p_rec.pension_type_id
2619 ,p_ee_age_contribution => p_rec.ee_age_contribution
2620 ,p_effective_date => p_effective_date
2621 ,p_validation_start_date => p_validation_start_date
2622 ,p_validation_end_date => p_validation_end_date
2623 );
2624
2625 --
2626 -- Validate the Age Dependant Employer Contribution
2627 --
2628 chk_er_age_contribution
2629 (p_pension_type_id => p_rec.pension_type_id
2630 ,p_er_age_contribution => p_rec.er_age_contribution
2631 ,p_effective_date => p_effective_date
2632 ,p_validation_start_date => p_validation_start_date
2633 ,p_validation_end_date => p_validation_end_date
2634 );
2635
2636 hr_utility.set_location(' pension_sub_category :'||
2637 p_rec.pension_sub_category, 10);
2638
2639 if (p_rec.pension_sub_category is not null and
2640 p_rec.pension_sub_category = 'FPU_S') then
2641 --
2642 -- Set End date for FPU_S.
2643 --
2644 set_end_date_fpus
2645 (
2646 p_pension_type_id => p_rec.pension_type_id
2647 ,p_pension_sub_category => p_rec.pension_sub_category
2648 ,p_effective_date => p_effective_date
2649 ,p_validation_start_date => p_validation_start_date
2650 ,p_validation_end_date => p_validation_end_date
2651 );
2652
2653
2654 end if;
2655
2656 end if;
2657
2658 pqp_pty_bus.chk_ddf(p_rec);
2659 --
2660 pqp_pty_bus.chk_df(p_rec);
2661 --
2662 hr_utility.set_location(' Leaving:'||l_proc, 10);
2663 End update_validate;
2664
2665
2666 --
2667 -- ----------------------------------------------------------------------------
2668 -- |---------------------------< delete_validate >----------------------------|
2669 -- ----------------------------------------------------------------------------
2670 Procedure delete_validate
2671 (p_rec in pqp_pty_shd.g_rec_type
2672 ,p_effective_date in date
2673 ,p_datetrack_mode in varchar2
2674 ,p_validation_start_date in date
2675 ,p_validation_end_date in date
2676 ) is
2677 --
2678 l_proc VARCHAR2(72) := g_package||'delete_validate';
2679 l_legislation_code VARCHAR2(30);
2680 l_is_abp_pt NUMBER;
2681 l_is_pggm_pt NUMBER;
2682 l_org_info_exists NUMBER;
2683 l_pggm_org_info_exists NUMBER;
2684 l_pggm_scheme NUMBER;
2685 l_asg_info_exists NUMBER;
2686 l_max_org_end_dt DATE;
2687 l_max_asg_end_dt DATE;
2688
2689 --cursor to check that the pension type is a ABP Pension type
2690 CURSOR c_chk_abp_pt(c_pension_type_id IN pqp_pension_types_f.pension_type_id%TYPE) IS
2691 SELECT 1
2692 FROM pqp_pension_types_f
2693 WHERE pension_type_id = c_pension_type_id
2694 AND special_pension_type_code = 'ABP';
2695
2696 --cursor to check that the pension type is a PGGM Pension type
2697 CURSOR c_chk_pggm_pt(c_pension_type_id IN pqp_pension_types_f.pension_type_id%TYPE) IS
2698 SELECT 1
2699 FROM pqp_pension_types_f
2700 WHERE pension_type_id = c_pension_type_id
2701 AND special_pension_type_code = 'PGGM';
2702
2703 --cursor to check if an org info row exists for the PT
2704 CURSOR c_chk_org_info(c_pension_type_id IN pqp_pension_types_f.pension_type_id%TYPE) IS
2705 SELECT 1
2706 FROM hr_organization_information
2707 WHERE org_information_context = 'PQP_NL_ABP_PT'
2708 AND org_information3 = to_char(c_pension_type_id);
2709
2710 --cursor to check if an org info row exists for the PT
2711 CURSOR c_chk_pggm_org_info(c_pension_type_id IN pqp_pension_types_f.pension_type_id%TYPE) IS
2712 SELECT 1
2713 FROM hr_organization_information
2714 WHERE org_information_context = 'PQP_NL_PGGM_PT'
2715 AND org_information3 = to_char(c_pension_type_id);
2716
2717 --cursor to check if an asg eit row exists for the PT
2718 CURSOR c_chk_asg_info(c_pension_type_id IN pqp_pension_types_f.pension_type_id%TYPE) IS
2719 SELECT 1
2720 FROM per_assignment_extra_info
2721 WHERE aei_information_category = 'NL_ABP_PI'
2722 AND information_type = 'NL_ABP_PI'
2723 AND aei_information3 = to_char(c_pension_type_id);
2724
2725 --cursor to get the end date for the org eit row for this PT
2726 CURSOR c_org_info_end_dt(c_pension_type_id IN pqp_pension_types_f.pension_type_id%TYPE) IS
2727 SELECT fnd_date.canonical_to_date(nvl(org_information2,'4712/12/31')) org_info_end_dt
2728 FROM hr_organization_information
2729 WHERE org_information3 = to_char(c_pension_type_id)
2730 AND org_information_context = 'PQP_NL_ABP_PT';
2731
2732 --cursor to get the end date for the org eit row for this PT
2733 CURSOR c_pggm_org_info_end_dt(c_pension_type_id IN pqp_pension_types_f.pension_type_id%TYPE) IS
2734 SELECT fnd_date.canonical_to_date(nvl(org_information2,'4712/12/31')) org_info_end_dt
2735 FROM hr_organization_information
2736 WHERE org_information3 = to_char(c_pension_type_id)
2737 AND org_information_context = 'PQP_NL_PGGM_PT';
2738
2739 --cursor to get the end date for the asg eit row for this PT
2740 CURSOR c_asg_info_end_dt(c_pension_type_id IN pqp_pension_types_f.pension_type_id%TYPE) IS
2741 SELECT fnd_date.canonical_to_date(nvl(aei_information2,'4712/12/31')) asg_info_end_dt
2742 FROM per_assignment_extra_info
2743 WHERE aei_information3 = to_char(c_pension_type_id)
2744 AND aei_information_category = 'NL_ABP_PI'
2745 AND information_type = 'NL_ABP_PI';
2746
2747 --
2748 -- CURSOR to check if a PGGM Scheme Exists
2749 --
2750 CURSOR c_pggm_scheme (
2751 c_pension_type_id IN pqp_pension_types_f.pension_type_id%TYPE) IS
2752 SELECT 1
2753 FROM pay_element_type_extra_info
2754 WHERE eei_information_category = 'PQP_NL_PGGM_DEDUCTION'
2755 AND eei_information2 = fnd_number.number_to_canonical(c_pension_type_id);
2756
2757 --
2758 Begin
2759 hr_utility.set_location('Entering:'||l_proc, 5);
2760 --
2761 --
2762 chk_startup_action(false
2763 ,pqp_pty_shd.g_old_rec.business_group_id
2764 ,pqp_pty_shd.g_old_rec.legislation_code
2765 );
2766 IF hr_startup_data_api_support.g_startup_mode
2767 NOT IN ('GENERIC','STARTUP') THEN
2768 --
2769 -- Validate Important Attributes
2770 --
2771 --
2772 -- After validating the set of important attributes,
2773 -- if Multiple Message Detection is enabled and at least
2774 -- one error has been found then abort further validation.
2775 --
2776 hr_multi_message.end_validation_set;
2777 END IF;
2778 --
2779 -- Call all supporting business operations
2780 --
2781 dt_delete_validate
2782 (p_datetrack_mode => p_datetrack_mode
2783 ,p_validation_start_date => p_validation_start_date
2784 ,p_validation_end_date => p_validation_end_date
2785 ,p_pension_type_id => p_rec.pension_type_id
2786 );
2787 --
2788 --
2789 -- Check if the pension type has been assigned to a provider
2790 --
2791 If p_datetrack_mode ='ZAP' Then
2792
2793 IF p_rec.business_group_id IS NOT NULL THEN
2794 l_legislation_code := hr_api.return_legislation_code (
2795 p_business_group_id => p_rec.business_group_id
2796 );
2797 ELSE
2798 l_legislation_code := p_rec.legislation_code;
2799 END IF;
2800
2801
2802 chk_provider_assignment
2803 (p_pension_type_id => p_rec.pension_type_id
2804 ,p_effective_date => p_effective_date
2805 ,p_leg_code => l_legislation_code --added for UK
2806 );
2807 End If;
2808
2809 --
2810 -- For NL ABP Pension Types perform extra ORG and ASG EIT Validations
2811 --
2812 OPEN c_chk_abp_pt(p_rec.pension_type_id);
2813 FETCH c_chk_abp_pt INTO l_is_abp_pt;
2814 IF c_chk_abp_pt%FOUND THEN
2815 CLOSE c_chk_abp_pt;
2816 hr_utility.set_location('ABP PT'||l_proc,10);
2817 -- if the mode is ZAP, then dont allow a delete if a row exists
2818 -- in the org or asg EIT for this PT
2819 If p_datetrack_mode = 'ZAP' Then
2820 --check if there exists a row in the ORG EIT for the PT
2821 hr_utility.set_location('Delete Mode ZAP'||l_proc,20);
2822 OPEN c_chk_org_info(p_rec.pension_type_id);
2823 FETCH c_chk_org_info INTO l_org_info_exists;
2824 If c_chk_org_info%FOUND THEN
2825 CLOSE c_chk_org_info;
2826 hr_utility.set_location('Found org info for the PT'||l_proc,30);
2827 fnd_message.set_name('PQP','PQP_230048_ORG_INFO_EXISTS');
2828 hr_multi_message.add();
2829 Else
2830 CLOSE c_chk_org_info;
2831 End If;
2832
2833 --check if there exists a row in the ASG EIT for the PT
2834 OPEN c_chk_asg_info(p_rec.pension_type_id);
2835 FETCH c_chk_asg_info INTO l_asg_info_exists;
2836 If c_chk_asg_info%FOUND THEN
2837 CLOSE c_chk_asg_info;
2838 hr_utility.set_location('Found ASG info for the PT'||l_proc,40);
2839 fnd_message.set_name('PQP','PQP_230049_ASG_INFO_EXISTS');
2840 hr_multi_message.add();
2841 Else
2842 CLOSE c_chk_asg_info;
2843 End If;
2844
2845 -- if the date track mode is DELETE (END DATE)
2846 -- first find the max end date in the ORG/ASG EIT rows and then
2847 -- error out with a message to inform the user to atleast end date
2848 -- on this maximum end date
2849
2850 Elsif p_datetrack_mode = 'DELETE' Then
2851 --find the maximum end date in the ORG/ASG EIT for this PT
2852 hr_utility.set_location('Datetrack mode DELETE'||l_proc,50);
2853 l_max_org_end_dt := p_effective_date;
2854 l_max_asg_end_dt := p_effective_date;
2855 -- loop through all org info rows and fetch the end date greatest than the eff date
2856 FOR temp_rec in c_org_info_end_dt(p_rec.pension_type_id)
2857 LOOP
2858 hr_utility.set_location('in org info loop'||p_effective_date||temp_rec.org_info_end_dt,10);
2859 If temp_rec.org_info_end_dt > l_max_org_end_dt THEN
2860 l_max_org_end_dt := temp_rec.org_info_end_dt;
2861 End If;
2862 END LOOP;
2863
2864 If l_max_org_end_dt <> p_effective_date THEN
2865 hr_utility.set_location('Found future dates org eit rows'||l_proc,60);
2866 fnd_message.set_name('PQP','PQP_230050_FUTURE_ORG_INFO');
2867 fnd_message.set_token('ENDDATE',to_char(l_max_org_end_dt));
2868 hr_multi_message.add();
2869 End If;
2870
2871 -- loop through all asg info rows and fetch the end date greatest than the eff date
2872 FOR temp_rec in c_asg_info_end_dt(p_rec.pension_type_id)
2873 LOOP
2874 If temp_rec.asg_info_end_dt > l_max_asg_end_dt THEN
2875 l_max_asg_end_dt := temp_rec.asg_info_end_dt;
2876 End If;
2877 END LOOP;
2878 -- hr_utility.trace_off;
2879 If l_max_asg_end_dt <> p_effective_date THEN
2880 hr_utility.set_location('found future dates asg eit rows'||l_proc,70);
2881 fnd_message.set_name('PQP','PQP_230051_FUTURE_ASG_INFO');
2882 fnd_message.set_token('ENDDATE',to_char(l_max_asg_end_dt));
2883 hr_multi_message.add();
2884 End If;
2885
2886 End If; -- end of date track mode DELETE/ZAP validations
2887
2888 ELSE
2889 CLOSE c_chk_abp_pt;
2890 END IF;
2891
2892 --
2893 -- For NL PGGM Pension type perform extra validations
2894 -- Check if the pension type has been attached to an org
2895 -- Please Note: This is only for the NL Legislation
2896 --
2897 OPEN c_chk_pggm_pt(p_rec.pension_type_id);
2898 FETCH c_chk_pggm_pt
2899 INTO l_is_pggm_pt;
2900 --
2901 IF c_chk_pggm_pt%FOUND THEN
2902 hr_utility.set_location('PGGM PT'||l_proc,10);
2903 -- Do not allow a delete if a row exists
2904 -- in the org EIT for this PT
2905
2906 --
2907 -- Check if there exists a row in the ORG EIT for the PT
2908 --
2909 hr_utility.set_location('Delete Mode ZAP'||l_proc,20);
2910 OPEN c_chk_pggm_org_info(p_rec.pension_type_id);
2911 FETCH c_chk_pggm_org_info
2912 INTO l_pggm_org_info_exists;
2913 --
2914 IF c_chk_pggm_org_info%FOUND THEN
2915 CLOSE c_chk_pggm_org_info;
2916 hr_utility.set_location('Found org info for the PT'||l_proc,30);
2917 fnd_message.set_name('PQP','PQP_230048_ORG_INFO_EXISTS');
2918 hr_multi_message.add();
2919 ELSE
2920 CLOSE c_chk_pggm_org_info;
2921 END IF;
2922
2923 ELSE
2924
2925 --
2926 -- Check if a PGGM Scheme exists for the PT
2927 --
2928 OPEN c_pggm_scheme(p_rec.pension_type_id);
2929 FETCH c_pggm_scheme
2930 INTO l_pggm_scheme;
2931
2932 IF c_pggm_scheme%FOUND THEN
2933 CLOSE c_pggm_scheme;
2934 hr_utility.set_location('Found scheme info for the PT'||l_proc,30);
2935 fnd_message.set_name('PQP','PQP_2300XX_SCHEME_INFO_EXISTS');
2936 hr_multi_message.add();
2937 ELSE
2938 CLOSE c_pggm_scheme;
2939 END IF;
2940
2941
2942 END IF;
2943
2944 CLOSE c_chk_pggm_pt;
2945
2946 hr_utility.set_location(' Leaving:'||l_proc, 80);
2947
2948 End delete_validate;
2949 --
2950
2951 end pqp_pty_bus;
2952
[Home] [Help]