[Home] [Help]
PACKAGE BODY: APPS.PQP_PENSION_TYPES_API
Source
1 Package Body PQP_Pension_Types_api as
2 /* $Header: pqptyapi.pkb 120.8 2011/06/15 10:10:38 abraghun ship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(50) := ' pqp_pension_types_api.';
7 g_bg_grp_id per_business_groups.business_group_id%TYPE;
8 g_bg_grp_name per_business_groups.name%TYPE;
9 g_leg_code per_business_groups.legislation_code%TYPE;
10 g_currency_code per_business_groups.currency_code%TYPE;
11
12
13 -- ---------------------------------------------------------------------------
14 -- |------------------------< Create_EE_Balance >-------------------------|
15 -- ---------------------------------------------------------------------------
16 Procedure Create_EE_Balance
17 (p_effective_date in date
18 ,p_business_group_id in number
19 ,p_legislation_code in varchar2
20 ,p_pension_type_name in varchar2
21 ,p_pension_category in varchar2
22 ,p_ee_balance_typeid out nocopy number
23 ) Is
24
25 Cursor csr_bg Is
26 Select currency_code,legislation_code,name
27 from per_business_groups_perf
28 where business_group_id = p_business_group_id;
29
30 Cursor csr_bal_name(c_balance_name in varchar2) Is
31 Select 'x'
32 from pay_balance_types
33 where balance_name = c_balance_name
34 and business_group_id = p_business_group_id;
35
36 Cursor csr_bal_dim ( c_legislation_code in varchar2 ) Is
37 Select balance_dimension_id
38 from pay_balance_dimensions
39 where legislation_code = c_legislation_code
40 and database_item_suffix in ('_ASG_ITD', '_ASG_QTD',
41 '_ASG_MONTH', '_ASG_PTD',
42 '_ASG_YTD', '_ASG_RUN',
43 '_PER_ITD', '_PER_PTD',
44 '_PER_QTD', '_PER_YTD'
45 );
46
47 Cursor csr_get_balance_cat_id(c_category_name in varchar2
48 ,c_legislation_code in varchar2
49 ,c_effective_date in date) Is
50 Select balance_category_id
51 from pay_balance_categories_f
52 where category_name = c_category_name
53 and legislation_code = c_legislation_code
54 and c_effective_date between effective_start_date and effective_end_date;
55
56 l_proc varchar2(150) := g_package||'Create_EE_Balances';
57 l_bal_name_exists varchar2(3);
58 l_row_id rowid;
59 l_balance_type_id pay_balance_types.balance_type_id%TYPE;
60 l_currency_code pay_balance_types.currency_code%TYPE;
61 l_legislation_code per_business_groups.legislation_code%TYPE;
62 l_balance_name pay_balance_types.balance_name%TYPE;
63 l_reporting_name pay_balance_types.reporting_name%TYPE;
64 l_defined_balance_id pay_defined_balances.defined_balance_id%TYPE;
65 l_balance_dimension_id pay_balance_dimensions.balance_dimension_id%TYPE;
66 l_balance_cat_id pay_balance_categories_f.balance_category_id%TYPE := Null;
67 l_category_name pay_balance_categories_f.category_name%TYPE;
68
69 Begin
70 hr_utility.set_location('Entering:'|| l_proc, 10);
71 --
72 -- Check if the EE Contribution
73 -- balances already exist for the BG
74 --
75 Open csr_bal_name(c_balance_name => p_pension_type_name ||' EE Contribution');
76 Fetch csr_bal_name Into l_bal_name_exists;
77 If csr_bal_name%FOUND Then
78 close csr_bal_name;
79 fnd_message.set_name('PQP', 'PQP_230805_EE_BAL_NAME_EXISTS');
80 fnd_message.raise_error;
81 End If;
82 --
83 -- Get the currency code for the business group or leg code
84 --
85 If p_business_group_id Is Not Null Then
86 Open csr_bg;
87 fetch csr_bg Into l_currency_code,l_legislation_code,g_bg_grp_name;
88 Close csr_bg;
89 hr_utility.set_location(' Fetched the currency code :'||l_currency_code,15);
90 ElsIf p_legislation_code Is Not Null Then
91 l_currency_code := hr_general.default_currency_code(p_legislation_code);
92 l_legislation_code := p_legislation_code;
93 Else
94 fnd_message.set_name('PQP', 'PQP_230807_INV_BUSGRP_LEGCODE');
95 fnd_message.raise_error;
96 End If;
97
98 If (l_legislation_code is Not Null and l_legislation_code = 'NL') Then
99 l_balance_cat_id := Null;
100 IF p_pension_category = 'P' THEN
101 l_category_name := 'Pension Contribution';
102 ELSIF p_pension_category = 'S' THEN
103 l_category_name := 'Savings Contribution';
104 END IF;
105
106 OPEN csr_get_balance_cat_id(c_category_name => l_category_name
107 ,c_legislation_code => 'NL'
108 ,c_effective_date => p_effective_date);
109 FETCH csr_get_balance_cat_id INTO l_balance_cat_id;
110 CLOSE csr_get_balance_cat_id;
111 hr_utility.set_location('Fetched the category id :'||l_balance_cat_id,17);
112 End If;
113
114 --
115 -- These global variables would be used by other procedures
116 --
117 g_leg_code := l_legislation_code;
118 g_bg_grp_id := p_business_group_id;
119 g_currency_code := l_currency_code;
120
121 --
122 -- Create the EE Contribution balances
123 --
124
125 l_balance_name := p_pension_type_name||' EE Contribution';
126 l_reporting_name := l_balance_name;
127
128 hr_utility.set_location(' Creating balance : '||l_balance_name,20);
129 Pay_Balance_Types_pkg.Insert_Row
130 (x_Rowid => l_row_id
131 ,x_Balance_Type_Id => l_balance_type_id
132 ,x_Business_Group_Id => p_business_group_id
133 ,x_Legislation_Code => p_legislation_code
134 ,x_Currency_Code => l_currency_code
135 ,x_Assignment_Remuneration_Flag => 'N'
136 ,x_Balance_Name => l_balance_name
137 ,x_Base_Balance_Name => l_balance_name
138 ,x_Balance_Uom => 'M'
139 ,x_Comments => Null
140 ,x_Legislation_Subgroup => Null
141 ,x_Reporting_Name => l_reporting_name
142 ,x_Attribute_Category => Null
143 ,X_Attribute1 => Null
144 ,x_Attribute2 => Null
145 ,x_Attribute3 => Null
146 ,x_Attribute4 => Null
147 ,x_Attribute5 => Null
148 ,x_Attribute6 => Null
149 ,x_Attribute7 => Null
150 ,x_Attribute8 => Null
151 ,x_Attribute9 => Null
152 ,x_Attribute10 => Null
153 ,x_Attribute11 => Null
154 ,x_Attribute12 => Null
155 ,x_Attribute13 => Null
156 ,x_Attribute14 => Null
157 ,x_Attribute15 => Null
158 ,x_Attribute16 => Null
159 ,x_Attribute17 => Null
160 ,x_Attribute18 => Null
161 ,x_Attribute19 => Null
162 ,x_Attribute20 => Null
163 ,x_balance_category_id => l_balance_cat_id
164 ,x_base_balance_type_id => Null
165 ,x_input_value_id => Null
166 );
167 hr_utility.set_location(' Created balance :'||l_balance_name,22);
168
169 p_ee_balance_typeid := l_balance_type_id;
170
171 l_balance_type_id := Null;
172 l_row_id := null;
173
174 --
175 -- Create the dimensions for the balances.
176 --
177 hr_utility.set_location(' Creating Defined balances', 25);
178
179
180 l_balance_type_id := p_ee_balance_typeid;
181
182 For csr_dim in csr_bal_dim
183 (c_legislation_code => l_legislation_code )
184 Loop
185 Pay_Defined_Balances_pkg.Insert_Row
186 (x_rowid => l_row_id
187 ,x_defined_balance_id => l_defined_balance_id
188 ,x_business_group_id => p_business_group_id
189 ,x_legislation_code => p_legislation_code
190 ,x_balance_type_id => l_balance_type_id
191 ,x_balance_dimension_id => csr_dim.balance_dimension_id
192 ,x_force_latest_balance_flag => 'N'
193 ,x_legislation_subgroup => null
194 ,x_grossup_allowed_flag => 'N'
195 );
196 l_row_id := null; l_defined_balance_id := null;
197 End Loop;
198
199
200 Exception
201 When Others Then
202 hr_utility.set_location('Exception in Create_EE_Balance',150);
203 raise;
204
205 End Create_EE_Balance;
206
207
208 -- ---------------------------------------------------------------------------
209 -- |------------------------< Create_ER_Balance >-------------------------|
210 -- ---------------------------------------------------------------------------
211 Procedure Create_ER_Balance
212 (p_effective_date in date
213 ,p_business_group_id in number
214 ,p_legislation_code in varchar2
215 ,p_pension_type_name in varchar2
216 ,p_pension_category in varchar2
217 ,p_er_balance_typeid out nocopy number
218 ) Is
219
220 Cursor csr_bg Is
221 Select currency_code,legislation_code,name
222 from per_business_groups_perf
223 where business_group_id = p_business_group_id;
224
225 Cursor csr_bal_name(c_balance_name in varchar2) Is
226 Select 'x'
227 from pay_balance_types
228 where balance_name = c_balance_name
229 and business_group_id = p_business_group_id;
230
231 Cursor csr_bal_dim ( c_legislation_code in varchar2 ) Is
232 Select balance_dimension_id
233 from pay_balance_dimensions
234 where legislation_code = c_legislation_code
235 and database_item_suffix in ('_ASG_ITD', '_ASG_QTD',
236 '_ASG_MONTH', '_ASG_PTD',
237 '_ASG_YTD', '_ASG_RUN',
238 '_PER_ITD', '_PER_PTD',
239 '_PER_QTD', '_PER_YTD'
240 );
241
242 Cursor csr_get_balance_cat_id(c_category_name in varchar2
243 ,c_legislation_code in varchar2
244 ,c_effective_date in date) Is
245 Select balance_category_id
246 from pay_balance_categories_f
247 where category_name = c_category_name
248 and legislation_code = c_legislation_code
249 and c_effective_date between effective_start_date and effective_end_date;
250
251 l_proc varchar2(150) := g_package||'Create_ER_Balances';
252 l_bal_name_exists varchar2(3);
253 l_row_id rowid;
254 l_balance_type_id pay_balance_types.balance_type_id%TYPE;
255 l_currency_code pay_balance_types.currency_code%TYPE;
256 l_legislation_code per_business_groups.legislation_code%TYPE;
257 l_balance_name pay_balance_types.balance_name%TYPE;
258 l_reporting_name pay_balance_types.reporting_name%TYPE;
259 l_defined_balance_id pay_defined_balances.defined_balance_id%TYPE;
260 l_balance_dimension_id pay_balance_dimensions.balance_dimension_id%TYPE;
261 l_balance_cat_id pay_balance_categories_f.balance_category_id%TYPE := Null;
262 l_category_name pay_balance_categories_f.category_name%TYPE;
263
264 Begin
265 hr_utility.set_location('Entering:'|| l_proc, 10);
266 --
267 -- Check if the ER Contribution
268 -- balances already exist for the BG
269 --
270 Open csr_bal_name(c_balance_name => p_pension_type_name ||' ER Contribution');
271 Fetch csr_bal_name Into l_bal_name_exists;
272 If csr_bal_name%FOUND Then
273 close csr_bal_name;
274 fnd_message.set_name('PQP', 'PQP_230805_EE_BAL_NAME_EXISTS');
275 fnd_message.raise_error;
276 End If;
277 --
278 -- Get the currency code for the business group or leg code
279 --
280 If p_business_group_id Is Not Null Then
281 Open csr_bg;
282 fetch csr_bg Into l_currency_code,l_legislation_code,g_bg_grp_name;
283 Close csr_bg;
284 hr_utility.set_location(' Fetched the currency code :'||l_currency_code,15);
285 ElsIf p_legislation_code Is Not Null Then
286 l_currency_code := hr_general.default_currency_code(p_legislation_code);
287 l_legislation_code := p_legislation_code;
288 Else
289 fnd_message.set_name('PQP', 'PQP_230807_INV_BUSGRP_LEGCODE');
290 fnd_message.raise_error;
291 End If;
292
293 If (l_legislation_code is Not Null and l_legislation_code = 'NL') Then
294 l_balance_cat_id := Null;
295 IF p_pension_category = 'P' THEN
296 l_category_name := 'Pension Contribution';
297 ELSIF p_pension_category = 'S' THEN
298 l_category_name := 'Savings Contribution';
299 END IF;
300
301 OPEN csr_get_balance_cat_id(c_category_name => l_category_name
302 ,c_legislation_code => 'NL'
303 ,c_effective_date => p_effective_date);
304 FETCH csr_get_balance_cat_id INTO l_balance_cat_id;
305 CLOSE csr_get_balance_cat_id;
306 hr_utility.set_location('Fetched the category id :'||l_balance_cat_id,17);
307 End If;
308
309 --
310 -- These global variables would be used by other procedures
311 --
312 g_leg_code := l_legislation_code;
313 g_bg_grp_id := p_business_group_id;
314 g_currency_code := l_currency_code;
315
316 --
317 -- Create the EE Contribution balances
318 --
319
320 l_balance_name := p_pension_type_name||' ER Contribution';
321 l_reporting_name := l_balance_name;
322
323 hr_utility.set_location(' Creating balance : '||l_balance_name,20);
324 Pay_Balance_Types_pkg.Insert_Row
325 (x_Rowid => l_row_id
326 ,x_Balance_Type_Id => l_balance_type_id
327 ,x_Business_Group_Id => p_business_group_id
328 ,x_Legislation_Code => p_legislation_code
329 ,x_Currency_Code => l_currency_code
330 ,x_Assignment_Remuneration_Flag => 'N'
331 ,x_Balance_Name => l_balance_name
332 ,x_Base_Balance_Name => l_balance_name
333 ,x_Balance_Uom => 'M'
334 ,x_Comments => Null
335 ,x_Legislation_Subgroup => Null
336 ,x_Reporting_Name => l_reporting_name
337 ,x_Attribute_Category => Null
338 ,X_Attribute1 => Null
339 ,x_Attribute2 => Null
340 ,x_Attribute3 => Null
341 ,x_Attribute4 => Null
342 ,x_Attribute5 => Null
343 ,x_Attribute6 => Null
344 ,x_Attribute7 => Null
345 ,x_Attribute8 => Null
346 ,x_Attribute9 => Null
347 ,x_Attribute10 => Null
348 ,x_Attribute11 => Null
349 ,x_Attribute12 => Null
350 ,x_Attribute13 => Null
351 ,x_Attribute14 => Null
352 ,x_Attribute15 => Null
353 ,x_Attribute16 => Null
354 ,x_Attribute17 => Null
355 ,x_Attribute18 => Null
356 ,x_Attribute19 => Null
357 ,x_Attribute20 => Null
358 ,x_balance_category_id => l_balance_cat_id
359 ,x_base_balance_type_id => Null
360 ,x_input_value_id => Null
361 );
362 hr_utility.set_location(' Created balance :'||l_balance_name,22);
363
364 p_er_balance_typeid := l_balance_type_id;
365
366 l_balance_type_id := Null;
367 l_row_id := null;
368
369 --
370 -- Create the dimensions for the balances.
371 --
372 hr_utility.set_location(' Creating Defined balances', 25);
373
374
375 l_balance_type_id := p_er_balance_typeid;
376
377 For csr_dim in csr_bal_dim
378 (c_legislation_code => l_legislation_code )
379 Loop
380 Pay_Defined_Balances_pkg.Insert_Row
381 (x_rowid => l_row_id
382 ,x_defined_balance_id => l_defined_balance_id
383 ,x_business_group_id => p_business_group_id
384 ,x_legislation_code => p_legislation_code
385 ,x_balance_type_id => l_balance_type_id
386 ,x_balance_dimension_id => csr_dim.balance_dimension_id
387 ,x_force_latest_balance_flag => 'N'
388 ,x_legislation_subgroup => null
389 ,x_grossup_allowed_flag => 'N'
390 );
391 l_row_id := null; l_defined_balance_id := null;
392 End Loop;
393
394
395 Exception
396 When Others Then
397 hr_utility.set_location('Exception in Create_ER_Balance',150);
398 raise;
399
400 End Create_ER_Balance;
401 --
402 -- ---------------------------------------------------------------------------
403 -- |------------------------< Create_EE_ER_Balances >-------------------------|
404 -- ---------------------------------------------------------------------------
405 Procedure Create_EE_ER_Balances
406 (p_effective_date in date
407 ,p_business_group_id in number
408 ,p_legislation_code in varchar2
409 ,p_pension_type_name in varchar2
410 ,p_pension_category in varchar2
411 ,p_ee_balance_typeid out nocopy number
412 ,p_er_balance_typeid out nocopy number
413 ) Is
414
415 Cursor csr_bg Is
416 Select currency_code,legislation_code,name
417 from per_business_groups_perf
418 where business_group_id = p_business_group_id;
419
420 Cursor csr_bal_name(c_balance_name in varchar2) Is
421 Select 'x'
422 from pay_balance_types
423 where balance_name = c_balance_name
424 and business_group_id = p_business_group_id;
425
426 Cursor csr_bal_dim ( c_legislation_code in varchar2 ) Is
427 Select balance_dimension_id
428 from pay_balance_dimensions
429 where legislation_code = c_legislation_code
430 and database_item_suffix in ('_ASG_ITD', '_ASG_QTD',
431 '_ASG_MONTH', '_ASG_PTD',
432 '_ASG_YTD', '_ASG_RUN',
433 '_PER_ITD', '_PER_PTD',
434 '_PER_QTD', '_PER_YTD'
435 );
436
437 Cursor csr_get_balance_cat_id(c_category_name in varchar2
438 ,c_legislation_code in varchar2
439 ,c_effective_date in date) Is
440 Select balance_category_id
441 from pay_balance_categories_f
442 where category_name = c_category_name
443 and legislation_code = c_legislation_code
444 and c_effective_date between effective_start_date and effective_end_date;
445
446 l_proc varchar2(150) := g_package||'Create_EE_ER_Balances';
447 l_bal_name_exists varchar2(3);
448 l_row_id rowid;
449 l_balance_type_id pay_balance_types.balance_type_id%TYPE;
450 l_currency_code pay_balance_types.currency_code%TYPE;
451 l_legislation_code per_business_groups.legislation_code%TYPE;
452 l_balance_name pay_balance_types.balance_name%TYPE;
453 l_reporting_name pay_balance_types.reporting_name%TYPE;
454 l_defined_balance_id pay_defined_balances.defined_balance_id%TYPE;
455 l_balance_dimension_id pay_balance_dimensions.balance_dimension_id%TYPE;
456 l_balance_cat_id pay_balance_categories_f.balance_category_id%TYPE := Null;
457 l_category_name pay_balance_categories_f.category_name%TYPE;
458
459 Begin
460 hr_utility.set_location('Entering:'|| l_proc, 10);
461 --
462 -- Check if the EE Contribution and ER Contribution
463 -- balances already exist for the BG
464 --
465 Open csr_bal_name(c_balance_name => p_pension_type_name ||' EE Contribution');
466 Fetch csr_bal_name Into l_bal_name_exists;
467 If csr_bal_name%FOUND Then
468 close csr_bal_name;
469 fnd_message.set_name('PQP', 'PQP_230805_EE_BAL_NAME_EXISTS');
470 fnd_message.raise_error;
471 Else
472 close csr_bal_name;
473 Open csr_bal_name(c_balance_name => p_pension_type_name ||' ER Contribution');
474 Fetch csr_bal_name Into l_bal_name_exists;
475 If csr_bal_name%FOUND Then
476 close csr_bal_name;
477 fnd_message.set_name('PQP', 'PQP_230806_ER_BAL_NAME_EXISTS');
478 fnd_message.raise_error;
479 End If;
480 close csr_bal_name;
481 End If;
482 --
483 -- Get the currency code for the business group or leg code
484 --
485 If p_business_group_id Is Not Null Then
486 Open csr_bg;
487 fetch csr_bg Into l_currency_code,l_legislation_code,g_bg_grp_name;
488 Close csr_bg;
489 hr_utility.set_location(' Fetched the currency code :'||l_currency_code,15);
490 ElsIf p_legislation_code Is Not Null Then
491 l_currency_code := hr_general.default_currency_code(p_legislation_code);
492 l_legislation_code := p_legislation_code;
493 Else
494 fnd_message.set_name('PQP', 'PQP_230807_INV_BUSGRP_LEGCODE');
495 fnd_message.raise_error;
496 End If;
497
498 --
499 --Get the balance category id for 'Other Deductions' category (UK pensions only)
500 --
501 If (l_legislation_code is Not Null and l_legislation_code = 'GB') Then
502 Open csr_get_balance_cat_id(c_category_name => 'Other Deductions'
503 ,c_legislation_code => 'GB'
504 ,c_effective_date => p_effective_date);
505 Fetch csr_get_balance_cat_id into l_balance_cat_id;
506 close csr_get_balance_cat_id;
507 hr_utility.set_location('Fetched the category id :'||l_balance_cat_id,17);
508 ElsIf (l_legislation_code is Not Null and l_legislation_code = 'NL') Then
509 l_balance_cat_id := Null;
510 IF p_pension_category = 'P' THEN
511 l_category_name := 'Pension Contribution';
512 ELSIF p_pension_category = 'S' THEN
513 l_category_name := 'Savings Contribution';
514 END IF;
515
516 OPEN csr_get_balance_cat_id(c_category_name => l_category_name
517 ,c_legislation_code => 'NL'
518 ,c_effective_date => p_effective_date);
519 FETCH csr_get_balance_cat_id INTO l_balance_cat_id;
520 CLOSE csr_get_balance_cat_id;
521 hr_utility.set_location('Fetched the category id :'||l_balance_cat_id,17);
522
523 ElsIf (l_legislation_code is Not Null and l_legislation_code = 'IE') Then
524 l_balance_cat_id := Null;
525
526 Open csr_get_balance_cat_id(c_category_name => 'Other Deductions'
527 ,c_legislation_code => 'IE'
528 ,c_effective_date => p_effective_date);
529 Fetch csr_get_balance_cat_id into l_balance_cat_id;
530 close csr_get_balance_cat_id;
531 hr_utility.set_location('Fetched the category id :'||l_balance_cat_id,17);
532
533 End If;
534
535 --
536 -- These global variables would be used by other procedures
537 --
538 g_leg_code := l_legislation_code;
539 g_bg_grp_id := p_business_group_id;
540 g_currency_code := l_currency_code;
541 --
542 -- Create the EE and ER Contribution balances
543 --
544 For i in 1..2 Loop
545 If i = 1 Then
546 l_balance_name := p_pension_type_name||' EE Contribution';
547 l_reporting_name := l_balance_name;
548 Else
549 l_balance_name := p_pension_type_name||' ER Contribution';
550 l_reporting_name := l_balance_name;
551 End if;
552 hr_utility.set_location(' Creating balance : '||l_balance_name,20);
553 Pay_Balance_Types_pkg.Insert_Row
554 (x_Rowid => l_row_id
555 ,x_Balance_Type_Id => l_balance_type_id
556 ,x_Business_Group_Id => p_business_group_id
557 ,x_Legislation_Code => p_legislation_code
558 ,x_Currency_Code => l_currency_code
559 ,x_Assignment_Remuneration_Flag => 'N'
560 ,x_Balance_Name => l_balance_name
561 ,x_Base_Balance_Name => l_balance_name
562 ,x_Balance_Uom => 'M'
563 ,x_Comments => Null
564 ,x_Legislation_Subgroup => Null
565 ,x_Reporting_Name => l_reporting_name
566 ,x_Attribute_Category => Null
567 ,X_Attribute1 => Null
568 ,x_Attribute2 => Null
569 ,x_Attribute3 => Null
570 ,x_Attribute4 => Null
571 ,x_Attribute5 => Null
572 ,x_Attribute6 => Null
573 ,x_Attribute7 => Null
574 ,x_Attribute8 => Null
575 ,x_Attribute9 => Null
576 ,x_Attribute10 => Null
577 ,x_Attribute11 => Null
578 ,x_Attribute12 => Null
579 ,x_Attribute13 => Null
580 ,x_Attribute14 => Null
581 ,x_Attribute15 => Null
582 ,x_Attribute16 => Null
583 ,x_Attribute17 => Null
584 ,x_Attribute18 => Null
585 ,x_Attribute19 => Null
586 ,x_Attribute20 => Null
587 ,x_balance_category_id => l_balance_cat_id
588 ,x_base_balance_type_id => Null
589 ,x_input_value_id => Null
590 );
591 hr_utility.set_location(' Created balance :'||l_balance_name,22);
592 If i = 1 Then
593 p_ee_balance_typeid := l_balance_type_id;
594 Else
595 p_er_balance_typeid := l_balance_type_id;
596 End If;
597 l_balance_type_id := Null;
598 l_row_id := null;
599 End Loop;
600 --
601 -- Create the dimensions for the balances.
602 --
603 hr_utility.set_location(' Creating Defined balances', 25);
604 For i in 1..2 Loop
605 If i = 1 Then
606 l_balance_type_id := p_ee_balance_typeid;
607 Else
608 l_balance_type_id := p_er_balance_typeid;
609 End if;
610 For csr_dim in csr_bal_dim
611 (c_legislation_code => l_legislation_code )
612 Loop
613 Pay_Defined_Balances_pkg.Insert_Row
614 (x_rowid => l_row_id
615 ,x_defined_balance_id => l_defined_balance_id
616 ,x_business_group_id => p_business_group_id
617 ,x_legislation_code => p_legislation_code
618 ,x_balance_type_id => l_balance_type_id
619 ,x_balance_dimension_id => csr_dim.balance_dimension_id
620 ,x_force_latest_balance_flag => 'N'
621 ,x_legislation_subgroup => null
622 ,x_grossup_allowed_flag => 'N'
623 );
624 l_row_id := null; l_defined_balance_id := null;
625 End Loop;
626 End Loop;
627
628 Exception
629 When Others Then
630 hr_utility.set_location('Exception in Create_EE_ER_Balances',150);
631 raise;
632
633 End Create_EE_ER_Balances;
634
635 --
636 -- ---------------------------------------------------------------------------
637 -- |------------------------< Create_ABP_EE_ER_Balances >---------------------|
638 -- ---------------------------------------------------------------------------
639 Procedure Create_ABP_EE_ER_Balances
640 (p_effective_date in date
641 ,p_business_group_id in number
642 ,p_legislation_code in varchar2
643 ,p_pension_sub_cat in varchar2
644 ,p_ee_balance_typeid out nocopy number
645 ,p_er_balance_typeid out nocopy number
646 ) Is
647
648 Cursor csr_bg Is
649 Select currency_code,legislation_code,name
650 from per_business_groups_perf
651 where business_group_id = p_business_group_id;
652
653 Cursor csr_bal_typ_id(c_balance_name in varchar2) Is
654 Select balance_type_id
655 from pay_balance_types
656 where balance_name = c_balance_name
657 and business_group_id = p_business_group_id;
658
659 Cursor csr_bal_dim ( c_legislation_code in varchar2 ) Is
660 Select balance_dimension_id
661 from pay_balance_dimensions
662 where legislation_code = c_legislation_code
663 and database_item_suffix in ('_ASG_ITD', '_ASG_QTD',
664 '_ASG_MONTH', '_ASG_PTD',
665 '_ASG_YTD', '_ASG_RUN',
666 '_PER_ITD', '_PER_PTD',
667 '_PER_QTD', '_PER_YTD'
668 );
669
670 CURSOR csr_get_balance_cat_id(c_category_name IN varchar2
671 ,c_legislation_code IN varchar2
672 ,c_effective_date IN DATE) IS
673 SELECT balance_category_id
674 FROM pay_balance_categories_f
675 WHERE category_name = c_category_name
676 AND legislation_code = c_legislation_code
677 AND c_effective_date BETWEEN effective_start_date AND effective_end_date;
678
679 l_proc varchar2(150) := g_package||'Create_ABP_EE_ER_Balances';
680 l_row_id rowid;
681 l_balance_type_id pay_balance_types.balance_type_id%TYPE;
682 l_currency_code pay_balance_types.currency_code%TYPE;
683 l_legislation_code per_business_groups.legislation_code%TYPE;
684 l_balance_name pay_balance_types.balance_name%TYPE;
685 l_reporting_name pay_balance_types.reporting_name%TYPE;
686 l_defined_balance_id pay_defined_balances.defined_balance_id%TYPE;
687 l_balance_dimension_id pay_balance_dimensions.balance_dimension_id%TYPE;
688 l_pension_sub_cat varchar2(80) := '';
689 l_balance_cat_id pay_balance_categories_f.balance_category_id%TYPE;
690
691 Begin
692 hr_utility.set_location('Entering:'|| l_proc, 10);
693
694 -- First construct the EE and ER sub category level balance names
695 If p_pension_sub_cat IS NOT NULL THEN
696
697 Select DECODE(p_pension_sub_cat
698 ,'OPNP','OPNP'
699 ,'OPNP_65','OPNP65'
700 ,'OPNP_AOW','OPNPAOW'
701 ,'OPNP_W25','OPNPW25'
702 ,'OPNP_W50','OPNPW50'
703 ,'FPU_E','FPU Extra'
704 ,'FPU_R','FPU Raise'
705 ,'FPU_S','FPU Standard'
706 ,'FPU_T','FPU Total'
707 ,'FUR_S','FUR Standard'
708 ,'IPAP','IPAP'
709 ,'IPBW_H','IPBW High'
710 ,'IPBW_L','IPBW Low'
711 ,'VSG','VSG'
712 ,'FPU_B','FPU Base'
713 ,'FPU_C','FPU Composition'
714 ,'PPP','Partner Plus Pension'
715 ,'FPB','FP Basis'
716 ,'AAOP','ABP Disability'
717 ,l_pension_sub_cat)
718 INTO l_pension_sub_cat
719 From dual;
720 --
721 -- Get the currency code for the business group or leg code
722 --
723 If p_business_group_id Is Not Null Then
724 Open csr_bg;
725 fetch csr_bg Into l_currency_code,l_legislation_code,g_bg_grp_name;
726 Close csr_bg;
727 hr_utility.set_location(' Fetched the currency code :'||l_currency_code,15);
728 ElsIf p_legislation_code Is Not Null Then
729 l_currency_code := hr_general.default_currency_code(p_legislation_code);
730 l_legislation_code := p_legislation_code;
731 Else
732 fnd_message.set_name('PQP', 'PQP_230807_INV_BUSGRP_LEGCODE');
733 fnd_message.raise_error;
734 End If;
735
736 --
737 -- These global variables would be used by other procedures
738 --
739 g_leg_code := l_legislation_code;
740 g_bg_grp_id := p_business_group_id;
741 g_currency_code := l_currency_code;
742
743 -- only for NL Pensions the balances need to be created
744 If l_legislation_code is Not Null AND l_legislation_code = 'NL' THEN
745
746 OPEN csr_get_balance_cat_id(c_category_name => 'ABP Contribution'
747 ,c_legislation_code => 'NL'
748 ,c_effective_date => p_effective_date);
749 FETCH csr_get_balance_cat_id INTO l_balance_cat_id;
750 CLOSE csr_get_balance_cat_id;
751 hr_utility.set_location('Fetched the category id :'||l_balance_cat_id,17);
752
753 --
754 -- Create the EE and ER Contribution balances
755 --
756 For i in 1..2 Loop
757 If i = 1 Then
758 l_balance_name := l_pension_sub_cat||' EE Contribution';
759 l_reporting_name := l_balance_name;
760 Else
761 l_balance_name := l_pension_sub_cat||' ER Contribution';
762 l_reporting_name := l_balance_name;
763 End if;
764
765 -- if the balance already exists, dont create it, return the bal type id
766 Open csr_bal_typ_id(l_balance_name);
767 Fetch csr_bal_typ_id INTO l_balance_type_id;
768
769 If csr_bal_typ_id%FOUND THEN
770 Close csr_bal_typ_Id;
771 If i = 1 Then
772
773 p_ee_balance_typeid := l_balance_type_id;
774
775 Else
776
777 p_er_balance_typeid := l_balance_type_id;
778
779 End If;
780
781 Else
782 Close csr_bal_typ_id;
783
784 hr_utility.set_location(' Creating balance : '||l_balance_name,20);
785 Pay_Balance_Types_pkg.Insert_Row
786 (x_Rowid => l_row_id
787 ,x_Balance_Type_Id => l_balance_type_id
788 ,x_Business_Group_Id => p_business_group_id
789 ,x_Legislation_Code => p_legislation_code
790 ,x_Currency_Code => l_currency_code
791 ,x_Assignment_Remuneration_Flag => 'N'
792 ,x_Balance_Name => l_balance_name
793 ,x_Base_Balance_Name => l_balance_name
794 ,x_Balance_Uom => 'M'
795 ,x_Comments => Null
796 ,x_Legislation_Subgroup => Null
797 ,x_Reporting_Name => l_reporting_name
798 ,x_Attribute_Category => Null
799 ,X_Attribute1 => Null
800 ,x_Attribute2 => Null
801 ,x_Attribute3 => Null
802 ,x_Attribute4 => Null
803 ,x_Attribute5 => Null
804 ,x_Attribute6 => Null
805 ,x_Attribute7 => Null
806 ,x_Attribute8 => Null
807 ,x_Attribute9 => Null
808 ,x_Attribute10 => Null
809 ,x_Attribute11 => Null
810 ,x_Attribute12 => Null
811 ,x_Attribute13 => Null
812 ,x_Attribute14 => Null
813 ,x_Attribute15 => Null
814 ,x_Attribute16 => Null
815 ,x_Attribute17 => Null
816 ,x_Attribute18 => Null
817 ,x_Attribute19 => Null
818 ,x_Attribute20 => Null
819 ,x_balance_category_id => l_balance_cat_id
820 ,x_base_balance_type_id => Null
821 ,x_input_value_id => Null
822 );
823 hr_utility.set_location(' Created balance :'||l_balance_name,22);
824 If i = 1 Then
825 p_ee_balance_typeid := l_balance_type_id;
826 Else
827 p_er_balance_typeid := l_balance_type_id;
828 End If;
829
830 --
831 -- Create the dimensions for the balances.
832 --
833 hr_utility.set_location(' Creating Defined balances', 25);
834 If i = 1 Then
835 l_balance_type_id := p_ee_balance_typeid;
836 Else
837 l_balance_type_id := p_er_balance_typeid;
838 End if;
839 For csr_dim in csr_bal_dim
840 (c_legislation_code => l_legislation_code )
841 Loop
842 Pay_Defined_Balances_pkg.Insert_Row
843 (x_rowid => l_row_id
844 ,x_defined_balance_id => l_defined_balance_id
845 ,x_business_group_id => p_business_group_id
846 ,x_legislation_code => p_legislation_code
847 ,x_balance_type_id => l_balance_type_id
848 ,x_balance_dimension_id => csr_dim.balance_dimension_id
849 ,x_force_latest_balance_flag => 'N'
850 ,x_legislation_subgroup => null
851 ,x_grossup_allowed_flag => 'N'
852 );
853 l_row_id := null; l_defined_balance_id := null;
854 End Loop;
855
856 End If;
857 l_balance_type_id := Null;
858 l_row_id := null;
859
860 End Loop;
861
862 End If; /* end of chk for NL*/
863 End If; /* end of chk for non null sub category*/
864 Exception
865 When Others Then
866 hr_utility.set_location('Exception in Create_ABP_EE_ER_Balances',150);
867 raise;
868
869 End Create_ABP_EE_ER_Balances;
870
871 -- ----------------------------------------------------------------------------
872 -- |------------------------< Create_Balance_Init_Ele >------------------------|
873 -- ----------------------------------------------------------------------------
874 Procedure Create_Balance_Init_Ele
875 (p_effective_date in date
876 ,p_business_group_id in number
877 ,p_legislation_code in varchar2
878 ,p_pension_type_name in varchar2
879 ,p_pension_sub_cat in varchar2
880 ,p_balance_init_element_type_id out nocopy number
881 ) Is
882
883 Cursor csr_bal_type (c_balance_name in varchar2) Is
884 Select balance_type_id
885 from pay_balance_types
886 where balance_name = c_balance_name
887 and (business_group_id = p_business_group_id
888 or
889 legislation_code = g_leg_code);
890
891 Type Bal_feeds_Rec Is Record
892 (balance_type_id Number
893 ,input_value_id Number
894 );
895 Type Bal_feeds_info Is Table Of Bal_feeds_Rec
896 Index By Binary_Integer;
897
898 l_balfeeds_tab Bal_feeds_info;
899 l_row_id rowid;
900 l_count number :=0;
901 l_proc varchar2(150) := g_package||'Create_Balance_Init_Ele';
902 l_balance_feed_Id pay_balance_feeds_f.balance_feed_id%TYPE;
903 l_ipv_ee_contr pay_input_values_f.input_value_id%TYPE;
904 l_ipv_er_contr pay_input_values_f.input_value_id%TYPE;
905 l_business_group_name per_business_groups.name%TYPE;
906 l_pension_sub_cat_mean varchar2(80) := '';
907
908 Begin
909 hr_utility.set_location(' Entering:'|| l_proc, 10);
910 l_business_group_name := g_bg_grp_name;
911
912 hr_utility.set_location(' Setting the session date to :'||p_effective_date,11);
913 pay_db_pay_setup.set_session_date(nvl(p_effective_date, trunc(sysdate)));
914
915 hr_utility.set_location(' Creating the balance initialization element', 15);
916 p_balance_init_element_type_id :=
917 pay_db_pay_setup.create_element
918 ( p_element_name => 'Setup '||p_pension_type_name ||' Contribution Element'
919 ,p_description => 'Element for initializing '||p_pension_type_name||' balances'
920 ,p_classification_name => 'Balance Initialization'
921 ,p_post_termination_rule => 'Last Standard Process'
922 ,p_mult_entries_allowed => 'Y'
923 ,p_adjustment_only_flag => 'Y'
924 ,p_third_party_pay_only => NULL
925 ,p_processing_type => 'N'
926 ,p_processing_priority => 0
927 ,p_standard_link_flag => 'N'
928 ,p_business_group_name => l_business_group_name
929 ,p_legislation_code => p_legislation_code
930 ,p_effective_start_date => p_effective_date
931 ,p_effective_end_date => hr_api.g_eot
932 );
933
934 hr_utility.set_location(' Creating input value EE Contribution', 20);
935 l_ipv_ee_contr := pay_db_pay_setup.create_input_value
936 (
937 p_element_name => 'Setup '||p_pension_type_name ||' Contribution Element'
938 ,p_name => 'EE Contribution'
939 ,p_uom_code => 'M'
940 ,p_mandatory_flag => 'N'
941 ,p_display_sequence => 1
942 ,p_business_group_name => l_business_group_name
943 ,p_effective_start_date => p_effective_date
944 ,p_effective_end_date => hr_api.g_eot
945 ,p_legislation_code => p_legislation_code
946 );
947 hr_utility.set_location(' Creating input value ER Contribution', 25);
948 l_ipv_er_contr := pay_db_pay_setup.create_input_value
949 (
950 p_element_name => 'Setup '||p_pension_type_name ||' Contribution Element'
951 ,p_name => 'ER Contribution'
952 ,p_uom_code => 'M'
953 ,p_mandatory_flag => 'N'
954 ,p_display_sequence => 2
955 ,p_business_group_name => l_business_group_name
956 ,p_effective_start_date => p_effective_date
957 ,p_effective_end_date => hr_api.g_eot
958 ,p_legislation_code => p_legislation_code
959 );
960
961 If p_pension_sub_cat IS NOT NULL THEN
962 Select DECODE(p_pension_sub_cat
963 ,'OPNP','OPNP'
964 ,'OPNP_65','OPNP65'
965 ,'OPNP_AOW','OPNPAOW'
966 ,'OPNP_W25','OPNPW25'
967 ,'OPNP_W50','OPNPW50'
968 ,'FPU_E','FPU Extra'
969 ,'FPU_R','FPU Raise'
970 ,'FPU_S','FPU Standard'
971 ,'FPU_T','FPU Total'
972 ,'FUR_S','FUR Standard'
973 ,'IPAP','IPAP'
974 ,'IPBW_H','IPBW High'
975 ,'IPBW_L','IPBW Low'
976 ,'VSG','VSG'
977 ,'FPU_B','FPU Base'
978 ,'FPU_C','FPU Composition'
979 ,'PPP','Partner Plus Pension'
980 ,'FPB','FP Basis'
981 ,l_pension_sub_cat_mean)
982 INTO l_pension_sub_cat_mean
983 From dual;
984 End If;
985
986 If p_pension_sub_cat IS NULL THEN
987 For c_rec in csr_bal_type
988 (c_balance_name => p_pension_type_name ||' ER Contribution')
989 Loop
990 l_count := l_count + 1 ;
991 l_balfeeds_tab(l_count).balance_type_id := c_rec.balance_type_id;
992 l_balfeeds_tab(l_count).input_value_id := l_ipv_er_contr;
993 End Loop;
994 Else
995 For c_rec in csr_bal_type
996 (c_balance_name => l_pension_sub_cat_mean ||' ER Contribution')
997 Loop
998 l_count := l_count + 1 ;
999 l_balfeeds_tab(l_count).balance_type_id := c_rec.balance_type_id;
1000 l_balfeeds_tab(l_count).input_value_id := l_ipv_er_contr;
1001 End Loop;
1002 End If;
1003
1004 For c_rec in csr_bal_type
1005 (c_balance_name => 'Employer Pension Contribution')
1006 Loop
1007 l_count := l_count + 1 ;
1008 l_balfeeds_tab(l_count).balance_type_id := c_rec.balance_type_id;
1009 l_balfeeds_tab(l_count).input_value_id := l_ipv_er_contr;
1010 hr_utility.set_location(' Found seeded balance ER Pension Contribution',30);
1011 End Loop;
1012
1013 If p_pension_sub_cat IS NULL THEN
1014 For c_rec in csr_bal_type
1015 (c_balance_name => p_pension_type_name ||' EE Contribution')
1016 Loop
1017 l_count := l_count + 1 ;
1018 l_balfeeds_tab(l_count).balance_type_id := c_rec.balance_type_id;
1019 l_balfeeds_tab(l_count).input_value_id := l_ipv_ee_contr;
1020 End Loop;
1021 Else
1022 For c_rec in csr_bal_type
1023 (c_balance_name => l_pension_sub_cat_mean ||' EE Contribution')
1024 Loop
1025 l_count := l_count + 1 ;
1026 l_balfeeds_tab(l_count).balance_type_id := c_rec.balance_type_id;
1027 l_balfeeds_tab(l_count).input_value_id := l_ipv_ee_contr;
1028 End Loop;
1029 End If;
1030
1031 For c_rec in csr_bal_type
1032 (c_balance_name => 'Employee Pension Contribution')
1033 Loop
1034 l_count := l_count + 1 ;
1035 l_balfeeds_tab(l_count).balance_type_id := c_rec.balance_type_id;
1036 l_balfeeds_tab(l_count).input_value_id := l_ipv_ee_contr;
1037 hr_utility.set_location(' Found seeded balance EE Pension Contribution',35);
1038 End Loop;
1039
1040 hr_utility.set_location(' Creating input value ER,EE Contribution, balance feeds', 40);
1041 For i in 1..l_count Loop
1042 Pay_Balance_Feeds_f_pkg.Insert_Row(
1043 X_Rowid => l_row_id,
1044 X_Balance_Feed_Id => l_Balance_Feed_Id,
1045 X_Effective_Start_Date => p_effective_date,
1046 X_Effective_End_Date => hr_api.g_eot,
1047 X_Business_Group_Id => p_business_group_id,
1048 X_Legislation_Code => p_legislation_code,
1049 X_Balance_Type_Id => l_balfeeds_tab(i).balance_type_id,
1050 X_Input_Value_Id => l_balfeeds_tab(i).input_value_id,
1051 X_Scale => '1',
1052 X_Legislation_Subgroup => Null,
1053 X_Initial_Balance_Feed => false );
1054
1055 l_Balance_Feed_Id := Null;
1056 l_row_id := Null;
1057 End Loop;
1058 hr_utility.set_location(' Leaving:'|| l_proc, 80);
1059
1060 Exception
1061 When Others Then
1062 hr_utility.set_location('Exception in Create_Balance_Init_Ele',150);
1063 raise;
1064
1065 End Create_Balance_Init_Ele;
1066
1067 -- ----------------------------------------------------------------------------
1068 -- |------------------------< chk_pension_type_name >------------------------|
1069 -- ----------------------------------------------------------------------------
1070 Procedure chk_pension_type_name
1071 (p_pension_type_name_in in varchar2
1072 ) IS
1073
1074 element_name varchar2(100) := p_pension_type_name_in;
1075 l_output varchar2(100);
1076 l_rgeflg varchar2(100);
1077
1078 begin
1079
1080 -- Check if the pension type name has any special chars
1081 hr_chkfmt.checkformat
1082 (
1083 value => element_name,
1084 format => 'PAY_NAME',
1085 output => l_output,
1086 minimum => NULL,
1087 maximum => NULL,
1088 nullok => 'N',
1089 rgeflg => l_rgeflg,
1090 curcode => NULL
1091 );
1092
1093 EXCEPTION
1094
1095 WHEN OTHERS THEN
1096 fnd_message.set_name('PQP', 'PQP_230922_PEN_TYPE_NAME_ERR');
1097 fnd_message.raise_error;
1098
1099 END chk_pension_type_name;
1100
1101 -- ----------------------------------------------------------------------------
1102 -- |------------------------< chk_dup_pension_type >--------------------------|
1103 -- ----------------------------------------------------------------------------
1104 Procedure chk_dup_pension_type
1105 (p_pension_type_name_in in varchar2
1106 ,p_bg_id in number
1107 ) IS
1108
1109 -- Cursor to check if the pension type already exists
1110 CURSOR csr_pension_type IS
1111 SELECT 'x'
1112 FROM pqp_pension_types_f
1113 WHERE pension_type_name = p_pension_type_name_in
1114 AND business_group_id = p_bg_id
1115 AND rownum = 1;
1116
1117 l_dummy varchar2(1);
1118
1119 begin
1120
1121 -- Check if the pension type already exists
1122 OPEN csr_pension_type;
1123 FETCH csr_pension_type INTO l_dummy;
1124 IF csr_pension_type%FOUND THEN
1125 CLOSE csr_pension_type;
1126 fnd_message.set_name('PQP', 'PQP_230813_PEN_TYPE_EXISTS');
1127 fnd_message.raise_error;
1128 ELSE
1129 CLOSE csr_pension_type;
1130 END IF;
1131
1132 END chk_dup_pension_type;
1133
1134 -- ----------------------------------------------------------------------------
1135 -- |--------------------------< Create_Pension_Type >--------------------------|
1136 -- ----------------------------------------------------------------------------
1137 --
1138 Procedure Create_Pension_Type
1139 (p_validate in Boolean
1140 ,p_effective_date in date
1141 ,p_pension_type_name in varchar2
1142 ,p_pension_category in varchar2
1143 ,p_pension_provider_type in varchar2
1144 ,p_salary_calculation_method in varchar2
1145 ,p_threshold_conversion_rule in varchar2
1146 ,p_contribution_conversion_rule in varchar2
1147 ,p_er_annual_limit in number
1148 ,p_ee_annual_limit in number
1149 ,p_er_annual_salary_threshold in number
1150 ,p_ee_annual_salary_threshold in number
1151 ,p_business_group_id in number
1152 ,p_legislation_code in varchar2
1153 ,p_description in varchar2
1154 ,p_minimum_age in number
1155 ,p_ee_contribution_percent in number
1156 ,p_maximum_age in number
1157 ,p_er_contribution_percent in number
1158 ,p_ee_annual_contribution in number
1159 ,p_er_annual_contribution in number
1160 ,p_annual_premium_amount in number
1161 ,p_ee_contribution_bal_type_id in number
1162 ,p_er_contribution_bal_type_id in number
1163 ,p_balance_init_element_type_id in number
1164 ,p_ee_contribution_fixed_rate in number --added for UK
1165 ,p_er_contribution_fixed_rate in number --added for UK
1166 ,p_pty_attribute_category in varchar2
1167 ,p_pty_attribute1 in varchar2
1168 ,p_pty_attribute2 in varchar2
1169 ,p_pty_attribute3 in varchar2
1170 ,p_pty_attribute4 in varchar2
1171 ,p_pty_attribute5 in varchar2
1172 ,p_pty_attribute6 in varchar2
1173 ,p_pty_attribute7 in varchar2
1174 ,p_pty_attribute8 in varchar2
1175 ,p_pty_attribute9 in varchar2
1176 ,p_pty_attribute10 in varchar2
1177 ,p_pty_attribute11 in varchar2
1178 ,p_pty_attribute12 in varchar2
1179 ,p_pty_attribute13 in varchar2
1180 ,p_pty_attribute14 in varchar2
1181 ,p_pty_attribute15 in varchar2
1182 ,p_pty_attribute16 in varchar2
1183 ,p_pty_attribute17 in varchar2
1184 ,p_pty_attribute18 in varchar2
1185 ,p_pty_attribute19 in varchar2
1186 ,p_pty_attribute20 in varchar2
1187 ,p_pty_information_category in varchar2
1188 ,p_pty_information1 in varchar2
1189 ,p_pty_information2 in varchar2
1190 ,p_pty_information3 in varchar2
1191 ,p_pty_information4 in varchar2
1192 ,p_pty_information5 in varchar2
1193 ,p_pty_information6 in varchar2
1194 ,p_pty_information7 in varchar2
1195 ,p_pty_information8 in varchar2
1196 ,p_pty_information9 in varchar2
1197 ,p_pty_information10 in varchar2
1198 ,p_pty_information11 in varchar2
1199 ,p_pty_information12 in varchar2
1200 ,p_pty_information13 in varchar2
1201 ,p_pty_information14 in varchar2
1202 ,p_pty_information15 in varchar2
1203 ,p_pty_information16 in varchar2
1204 ,p_pty_information17 in varchar2
1205 ,p_pty_information18 in varchar2
1206 ,p_pty_information19 in varchar2
1207 ,p_pty_information20 in varchar2
1208 ,p_special_pension_type_code in varchar2 -- added for NL Phase 2B
1209 ,p_pension_sub_category in varchar2 -- added for NL Phase 2B
1210 ,p_pension_basis_calc_method in varchar2 -- added for NL Phase 2B
1211 ,p_pension_salary_balance in number -- added for NL Phase 2B
1212 ,p_recurring_bonus_percent in number -- added for NL Phase 2B
1213 ,p_non_recurring_bonus_percent in number -- added for NL Phase 2B
1214 ,p_recurring_bonus_balance in number -- added for NL Phase 2B
1215 ,p_non_recurring_bonus_balance in number -- added for NL Phase 2B
1216 ,p_std_tax_reduction in varchar2 -- added for NL Phase 2B
1217 ,p_spl_tax_reduction in varchar2 -- added for NL Phase 2B
1218 ,p_sig_sal_spl_tax_reduction in varchar2 -- added for NL Phase 2B
1219 ,p_sig_sal_non_tax_reduction in varchar2 -- added for NL Phase 2B
1220 ,p_sig_sal_std_tax_reduction in varchar2 -- added for NL Phase 2B
1221 ,p_sii_std_tax_reduction in varchar2 -- added for NL Phase 2B
1222 ,p_sii_spl_tax_reduction in varchar2 -- added for NL Phase 2B
1223 ,p_sii_non_tax_reduction in varchar2 -- added for NL Phase 2B
1224 ,p_previous_year_bonus_included in varchar2 -- added for NL Phase 2B
1225 ,p_recurring_bonus_period in varchar2 -- added for NL Phase 2B
1226 ,p_non_recurring_bonus_period in varchar2 -- added for NL Phase 2B
1227 ,p_ee_age_threshold in varchar2 -- added for ABP TAR fixes
1228 ,p_er_age_threshold in varchar2 -- added for ABP TAR fixes
1229 ,p_ee_age_contribution in varchar2 -- added for ABP TAR fixes
1230 ,p_er_age_contribution in varchar2 -- added for ABP TAR fixes
1231 ,p_pension_type_id out nocopy number
1232 ,p_object_version_number out nocopy number
1233 ,p_effective_start_date out nocopy date
1234 ,p_effective_end_date out nocopy date
1235 ,p_api_warning out nocopy varchar2
1236 ) Is
1237
1238 --
1239 -- Declare cursors and local variables
1240 --
1241
1242 CURSOR c_get_leg_code IS
1243 SELECT legislation_code
1244 FROM per_business_groups
1245 WHERE business_group_id = p_business_group_id;
1246
1247 l_proc varchar2(150) := g_package||'Create_Pension_Type';
1248 l_effective_date date;
1249 l_pension_type_id pqp_pension_types_f.pension_type_id%TYPE;
1250 l_object_version_number pqp_pension_types_f.object_version_number%TYPE;
1251 l_ee_contribution_bal_type_id pay_balance_types.balance_type_id%TYPE;
1252 l_er_contribution_bal_type_id pay_balance_types.balance_type_id%TYPE;
1253 l_balance_init_element_type_id pay_element_types_f.element_type_id%TYPE;
1254 l_effective_start_date date;
1255 l_effective_end_date date;
1256 l_api_warning varchar2(250);
1257 l_sum_percent number(7,4);
1258 l_er_annual_limit number;
1259 l_ee_annual_limit number;
1260 l_er_annual_salary_threshold number;
1261 l_ee_annual_salary_threshold number;
1262 l_leg_code varchar2(10);
1263
1264 begin
1265 hr_utility.set_location('Entering:'|| l_proc, 10);
1266 -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1267 -- Issue a savepoint
1268 -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1269 savepoint Create_Pension_Type;
1270
1271 chk_pension_type_name(p_pension_type_name_in => p_pension_type_name);
1272
1273 chk_dup_pension_type(p_pension_type_name_in => p_pension_type_name
1274 ,p_bg_id => p_business_group_id);
1275
1276
1277 -- Check if the EE and ER percentages sum up to 100 for NL legislation
1278 if(p_salary_calculation_method = '3') then
1279 l_sum_percent := p_ee_contribution_percent + p_er_contribution_percent;
1280
1281 if l_sum_percent Is Null then
1282 fnd_message.set_name('PQP', 'PQP_230892_PEN_TYPE_PERCNT_INV');
1283 fnd_message.raise_error;
1284 end if;
1285
1286 if((l_sum_percent > 100) or (l_sum_percent < 100)) then
1287 fnd_message.set_name('PQP', 'PQP_230892_PEN_TYPE_PERCNT_INV');
1288 fnd_message.raise_error;
1289 end if;
1290
1291 end if;
1292
1293 -- check to see if the annual limits have been entered for Savings Pension Type
1294 -- when the salary calculation method is null
1295
1296 if (p_salary_calculation_method is Null and p_pension_category = 'S') then
1297
1298 if(p_er_annual_limit is Null or p_ee_annual_limit is Null) then
1299
1300 fnd_message.set_name('PQP', 'PQP_230990_SAV_NULL_ANN_LIMITS');
1301 fnd_message.raise_error;
1302
1303 end if;
1304
1305 end if;
1306
1307 -- Check the defaults for EE and ER limits/thresholds
1308 if(p_salary_calculation_method = '3') then
1309 l_er_annual_limit := NULL;
1310 l_ee_annual_limit := NULL;
1311 l_er_annual_salary_threshold := NULL;
1312 l_ee_annual_salary_threshold := NULL;
1313 else
1314 l_er_annual_limit := p_er_annual_limit;
1315 l_ee_annual_limit := p_ee_annual_limit;
1316 l_er_annual_salary_threshold := p_er_annual_salary_threshold;
1317 l_ee_annual_salary_threshold := p_ee_annual_salary_threshold;
1318 end if;
1319
1320 --
1321 -- check that the sub category is not null for ABP and PGGM pension types
1322 --
1323 if p_special_pension_type_code IS NOT NULL THEN
1324 if p_special_pension_type_code IN('ABP','PGGM') THEN
1325 if p_pension_sub_category IS NULL THEN
1326 fnd_message.set_name('PQP','PQP_230069_PEN_SUB_CAT_REQ');
1327 fnd_message.raise_error;
1328 end if;
1329 end if;
1330 end if;
1331
1332 --
1333 -- check that the total contribution percentage is atleast equal to the
1334 -- employee contribution percentage
1335 --
1336 IF p_special_pension_type_code IS NOT NULL THEN
1337 IF p_special_pension_type_code = 'PGGM' THEN
1338 IF nvl(p_er_contribution_percent,0) <
1339 nvl(p_ee_contribution_percent,0) THEN
1340 fnd_message.set_name('PQP','PQP_230221_PGGM_INV_PERCENTAGE');
1341 fnd_message.raise_error;
1342 END IF;
1343 END IF;
1344 END IF;
1345
1346 -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1347 -- Truncate the time portion from all IN date parameters
1348 -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1349 l_effective_date := Trunc(p_effective_date);
1350
1351 -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1352 -- Create the EE, ER Contribution balances(only for NL,GB)
1353 -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1354 hr_utility.set_location('Creating EE, ER Balances ',14);
1355 OPEN c_get_leg_code;
1356 FETCH c_get_leg_code INTO l_leg_code;
1357 CLOSE c_get_leg_code;
1358
1359 If l_leg_code <> 'HU' THEN
1360 If (
1361 (p_special_pension_type_code IS NULL and p_pension_category <> 'S')
1362 )
1363 THEN
1364 Create_EE_ER_Balances
1365 (p_effective_date => l_effective_date
1366 ,p_business_group_id => p_business_group_id
1367 ,p_legislation_code => p_legislation_code
1368 ,p_pension_type_name => p_pension_type_name
1369 ,p_ee_balance_typeid => l_ee_contribution_bal_type_id
1370 ,p_er_balance_typeid => l_er_contribution_bal_type_id
1371 ,p_pension_category => p_pension_category
1372 );
1373
1374 Elsif p_special_pension_type_code = 'ABP' THEN
1375 Create_ABP_EE_ER_Balances
1376 (p_effective_date => l_effective_date
1377 ,p_business_group_id => p_business_group_id
1378 ,p_legislation_code => p_legislation_code
1379 ,p_pension_sub_cat => p_pension_sub_category
1380 ,p_ee_balance_typeid => l_ee_contribution_bal_type_id
1381 ,p_er_balance_typeid => l_er_contribution_bal_type_id
1382 );
1383
1384 End If;
1385 hr_utility.set_location('Created EE, ER Balances ',15);
1386 -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1387 -- Create Balance Initialization Element
1388 -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1389 IF (NVL(p_special_pension_type_code,'X') <> 'PGGM' and p_pension_category = 'P') THEN
1390 Create_Balance_Init_Ele
1391 (p_effective_date => l_effective_date
1392 ,p_business_group_id => p_business_group_id
1393 ,p_legislation_code => p_legislation_code
1394 ,p_pension_type_name => p_pension_type_name
1395 ,p_pension_sub_cat => p_pension_sub_category
1396 ,p_balance_init_element_type_id => l_balance_init_element_type_id
1397 );
1398 END IF;
1399
1400 -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1401 -- For Savings create EE/ER balances for basis=User Defined Balance
1402 -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1403 IF p_pension_basis_calc_method = '10' and p_pension_category = 'S' THEN
1404 --Check for Employee Contribution balance
1405 IF p_ee_contribution_bal_type_id = '0' THEN
1406 Create_EE_Balance(
1407 p_effective_date => l_effective_date
1408 ,p_business_group_id => p_business_group_id
1409 ,p_legislation_code => p_legislation_code
1410 ,p_pension_type_name => p_pension_type_name
1411 ,p_ee_balance_typeid => l_ee_contribution_bal_type_id
1412 ,p_pension_category => p_pension_category
1413 );
1414
1415 Else
1416 l_ee_contribution_bal_type_id:=p_ee_contribution_bal_type_id;
1417 END IF;
1418
1419 --Check for Employer Contribution balance
1420 IF p_er_contribution_bal_type_id = '0' THEN
1421 Create_ER_Balance(
1422 p_effective_date => l_effective_date
1423 ,p_business_group_id => p_business_group_id
1424 ,p_legislation_code => p_legislation_code
1425 ,p_pension_type_name => p_pension_type_name
1426 ,p_er_balance_typeid => l_er_contribution_bal_type_id
1427 ,p_pension_category => p_pension_category
1428 );
1429
1430 Else
1431 l_er_contribution_bal_type_id:=p_er_contribution_bal_type_id;
1432 END IF;
1433
1434 ELSIF p_pension_basis_calc_method <> '10' and p_pension_category = 'S' THEN
1435
1436 Create_EE_ER_Balances
1437 (p_effective_date => l_effective_date
1438 ,p_business_group_id => p_business_group_id
1439 ,p_legislation_code => p_legislation_code
1440 ,p_pension_type_name => p_pension_type_name
1441 ,p_ee_balance_typeid => l_ee_contribution_bal_type_id
1442 ,p_er_balance_typeid => l_er_contribution_bal_type_id
1443 ,p_pension_category => p_pension_category
1444 );
1445
1446 END IF;
1447
1448 END IF;
1449 -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1450 -- Call Before Process User Hook
1451 -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1452
1453 begin
1454 hr_utility.set_location('Before Calling User Hook Create_PensionType_b',20);
1455 PQP_Pension_Types_BK1.Create_Pension_Type_b
1456 (p_validate => p_validate
1457 ,p_effective_date => l_effective_date
1458 ,p_pension_type_name => p_pension_type_name
1459 ,p_pension_category => p_pension_category
1460 ,p_pension_provider_type => p_pension_provider_type
1461 ,p_salary_calculation_method => p_salary_calculation_method
1462 ,p_threshold_conversion_rule => p_threshold_conversion_rule
1463 ,p_contribution_conversion_rule => p_contribution_conversion_rule
1464 ,p_er_annual_limit => l_er_annual_limit
1465 ,p_ee_annual_limit => l_ee_annual_limit
1466 ,p_er_annual_salary_threshold => l_er_annual_salary_threshold
1467 ,p_ee_annual_salary_threshold => l_ee_annual_salary_threshold
1468 ,p_business_group_id => p_business_group_id
1469 ,p_legislation_code => p_legislation_code
1470 ,p_description => p_description
1471 ,p_minimum_age => p_minimum_age
1472 ,p_ee_contribution_percent => p_ee_contribution_percent
1473 ,p_maximum_age => p_maximum_age
1474 ,p_er_contribution_percent => p_er_contribution_percent
1475 ,p_ee_annual_contribution => p_ee_annual_contribution
1476 ,p_er_annual_contribution => p_er_annual_contribution
1477 ,p_annual_premium_amount => p_annual_premium_amount
1478 ,p_ee_contribution_bal_type_id => p_ee_contribution_bal_type_id
1479 ,p_er_contribution_bal_type_id => p_er_contribution_bal_type_id
1480 ,p_balance_init_element_type_id => p_balance_init_element_type_id
1481 ,p_ee_contribution_fixed_rate => p_ee_contribution_fixed_rate
1482 ,p_er_contribution_fixed_rate => p_er_contribution_fixed_rate
1483 ,p_pty_attribute_category => p_pty_attribute_category
1484 ,p_pty_attribute1 => p_pty_attribute1
1485 ,p_pty_attribute2 => p_pty_attribute2
1486 ,p_pty_attribute3 => p_pty_attribute3
1487 ,p_pty_attribute4 => p_pty_attribute4
1488 ,p_pty_attribute5 => p_pty_attribute5
1489 ,p_pty_attribute6 => p_pty_attribute6
1490 ,p_pty_attribute7 => p_pty_attribute7
1491 ,p_pty_attribute8 => p_pty_attribute8
1492 ,p_pty_attribute9 => p_pty_attribute9
1493 ,p_pty_attribute10 => p_pty_attribute10
1494 ,p_pty_attribute11 => p_pty_attribute11
1495 ,p_pty_attribute12 => p_pty_attribute12
1496 ,p_pty_attribute13 => p_pty_attribute13
1497 ,p_pty_attribute14 => p_pty_attribute14
1498 ,p_pty_attribute15 => p_pty_attribute15
1499 ,p_pty_attribute16 => p_pty_attribute16
1500 ,p_pty_attribute17 => p_pty_attribute17
1501 ,p_pty_attribute18 => p_pty_attribute18
1502 ,p_pty_attribute19 => p_pty_attribute19
1503 ,p_pty_attribute20 => p_pty_attribute20
1504 ,p_pty_information_category => p_pty_information_category
1505 ,p_pty_information1 => p_pty_information1
1506 ,p_pty_information2 => p_pty_information2
1507 ,p_pty_information3 => p_pty_information3
1508 ,p_pty_information4 => p_pty_information4
1509 ,p_pty_information5 => p_pty_information5
1510 ,p_pty_information6 => p_pty_information6
1511 ,p_pty_information7 => p_pty_information7
1512 ,p_pty_information8 => p_pty_information8
1513 ,p_pty_information9 => p_pty_information9
1514 ,p_pty_information10 => p_pty_information10
1515 ,p_pty_information11 => p_pty_information11
1516 ,p_pty_information12 => p_pty_information12
1517 ,p_pty_information13 => p_pty_information13
1518 ,p_pty_information14 => p_pty_information14
1519 ,p_pty_information15 => p_pty_information15
1520 ,p_pty_information16 => p_pty_information16
1521 ,p_pty_information17 => p_pty_information17
1522 ,p_pty_information18 => p_pty_information18
1523 ,p_pty_information19 => p_pty_information19
1524 ,p_pty_information20 => p_pty_information20
1525 ,p_special_pension_type_code => p_special_pension_type_code -- added for NL Phase 2B
1526 ,p_pension_sub_category => p_pension_sub_category -- added for NL Phase 2B
1527 ,p_pension_basis_calc_method => p_pension_basis_calc_method -- added for NL Phase 2B
1528 ,p_pension_salary_balance => p_pension_salary_balance -- added for NL Phase 2B
1529 ,p_recurring_bonus_percent => p_recurring_bonus_percent -- added for NL Phase 2B
1530 ,p_non_recurring_bonus_percent => p_non_recurring_bonus_percent -- added for NL Phase 2B
1531 ,p_recurring_bonus_balance => p_recurring_bonus_balance -- added for NL Phase 2B
1532 ,p_non_recurring_bonus_balance => p_non_recurring_bonus_balance -- added for NL Phase 2B
1533 ,p_std_tax_reduction => p_std_tax_reduction -- added for NL Phase 2B
1534 ,p_spl_tax_reduction => p_spl_tax_reduction -- added for NL Phase 2B
1535 ,p_sig_sal_spl_tax_reduction => p_sig_sal_spl_tax_reduction -- added for NL Phase 2B
1536 ,p_sig_sal_non_tax_reduction => p_sig_sal_non_tax_reduction -- added for NL Phase 2B
1537 ,p_sig_sal_std_tax_reduction => p_sig_sal_std_tax_reduction -- added for NL Phase 2B
1538 ,p_sii_std_tax_reduction => p_sii_std_tax_reduction -- added for NL Phase 2B
1539 ,p_sii_spl_tax_reduction => p_sii_spl_tax_reduction -- added for NL Phase 2B
1540 ,p_sii_non_tax_reduction => p_sii_non_tax_reduction -- added for NL Phase 2B
1541 ,p_previous_year_bonus_included => p_previous_year_bonus_included -- added for NL Phase 2B
1542 ,p_recurring_bonus_period => p_recurring_bonus_period -- added for NL Phase 2B
1543 ,p_non_recurring_bonus_period => p_non_recurring_bonus_period -- added for NL Phase 2B
1544 ,p_ee_age_threshold => p_ee_age_threshold -- added for ABP TAR fixes
1545 ,p_er_age_threshold => p_er_age_threshold -- added for ABP TAR fixes
1546 ,p_ee_age_contribution => p_ee_age_contribution -- added for ABP TAR fixes
1547 ,p_er_age_contribution => p_er_age_contribution -- added for ABP TAR fixes
1548 );
1549 hr_utility.set_location('After Calling User Hook Create_PensionType_b',20);
1550 exception
1551 when hr_api.cannot_find_prog_unit then
1552 hr_utility.set_location('Exception in User Hook Create_PensionType_b',25);
1553 hr_api.cannot_find_prog_unit_error
1554 (p_module_name => 'Create_Pension_Type'
1555 ,p_hook_type => 'BP'
1556 );
1557 end;
1558
1559 -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1560 -- Validation in addition to Row Handlers
1561 -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1562
1563
1564
1565 -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1566 -- Process Logic - Call the row-handler ins procedure
1567 -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1568 hr_utility.set_location('Before calling row-handler pqp_pty_ins.ins',30);
1569 pqp_pty_ins.ins
1570 (p_effective_date => l_effective_date
1571 ,p_pension_type_name => p_pension_type_name
1572 ,p_pension_category => p_pension_category
1573 ,p_pension_provider_type => p_pension_provider_type
1574 ,p_salary_calculation_method => p_salary_calculation_method
1575 ,p_threshold_conversion_rule => p_threshold_conversion_rule
1576 ,p_contribution_conversion_rule => p_contribution_conversion_rule
1577 ,p_er_annual_limit => l_er_annual_limit
1578 ,p_ee_annual_limit => l_ee_annual_limit
1579 ,p_er_annual_salary_threshold => l_er_annual_salary_threshold
1580 ,p_ee_annual_salary_threshold => l_ee_annual_salary_threshold
1581 ,p_business_group_id => p_business_group_id
1582 ,p_legislation_code => p_legislation_code
1583 ,p_description => p_description
1584 ,p_minimum_age => p_minimum_age
1585 ,p_ee_contribution_percent => p_ee_contribution_percent
1586 ,p_maximum_age => p_maximum_age
1587 ,p_er_contribution_percent => p_er_contribution_percent
1588 ,p_ee_annual_contribution => p_ee_annual_contribution
1589 ,p_er_annual_contribution => p_er_annual_contribution
1590 ,p_annual_premium_amount => p_annual_premium_amount
1591 ,p_ee_contribution_bal_type_id => l_ee_contribution_bal_type_id
1592 ,p_er_contribution_bal_type_id => l_er_contribution_bal_type_id
1593 ,p_balance_init_element_type_id => l_balance_init_element_type_id
1594 ,p_ee_contribution_fixed_rate => p_ee_contribution_fixed_rate --added for UK
1595 ,p_er_contribution_fixed_rate => p_er_contribution_fixed_rate --added for UK
1596 ,p_pty_attribute_category => p_pty_attribute_category
1597 ,p_pty_attribute1 => p_pty_attribute1
1598 ,p_pty_attribute2 => p_pty_attribute2
1599 ,p_pty_attribute3 => p_pty_attribute3
1600 ,p_pty_attribute4 => p_pty_attribute4
1601 ,p_pty_attribute5 => p_pty_attribute5
1602 ,p_pty_attribute6 => p_pty_attribute6
1603 ,p_pty_attribute7 => p_pty_attribute7
1604 ,p_pty_attribute8 => p_pty_attribute8
1605 ,p_pty_attribute9 => p_pty_attribute9
1606 ,p_pty_attribute10 => p_pty_attribute10
1607 ,p_pty_attribute11 => p_pty_attribute11
1608 ,p_pty_attribute12 => p_pty_attribute12
1609 ,p_pty_attribute13 => p_pty_attribute13
1610 ,p_pty_attribute14 => p_pty_attribute14
1611 ,p_pty_attribute15 => p_pty_attribute15
1612 ,p_pty_attribute16 => p_pty_attribute16
1613 ,p_pty_attribute17 => p_pty_attribute17
1614 ,p_pty_attribute18 => p_pty_attribute18
1615 ,p_pty_attribute19 => p_pty_attribute19
1616 ,p_pty_attribute20 => p_pty_attribute20
1617 ,p_pty_information_category => p_pty_information_category
1618 ,p_pty_information1 => p_pty_information1
1619 ,p_pty_information2 => p_pty_information2
1620 ,p_pty_information3 => p_pty_information3
1621 ,p_pty_information4 => p_pty_information4
1622 ,p_pty_information5 => p_pty_information5
1623 ,p_pty_information6 => p_pty_information6
1624 ,p_pty_information7 => p_pty_information7
1625 ,p_pty_information8 => p_pty_information8
1626 ,p_pty_information9 => p_pty_information9
1627 ,p_pty_information10 => p_pty_information10
1628 ,p_pty_information11 => p_pty_information11
1629 ,p_pty_information12 => p_pty_information12
1630 ,p_pty_information13 => p_pty_information13
1631 ,p_pty_information14 => p_pty_information14
1632 ,p_pty_information15 => p_pty_information15
1633 ,p_pty_information16 => p_pty_information16
1634 ,p_pty_information17 => p_pty_information17
1635 ,p_pty_information18 => p_pty_information18
1636 ,p_pty_information19 => p_pty_information19
1637 ,p_pty_information20 => p_pty_information20
1638 ,p_special_pension_type_code => p_special_pension_type_code -- added for NL Phase 2B
1639 ,p_pension_sub_category => p_pension_sub_category -- added for NL Phase 2B
1640 ,p_pension_basis_calc_method => p_pension_basis_calc_method -- added for NL Phase 2B
1641 ,p_pension_salary_balance => p_pension_salary_balance -- added for NL Phase 2B
1642 ,p_recurring_bonus_percent => p_recurring_bonus_percent -- added for NL Phase 2B
1643 ,p_non_recurring_bonus_percent => p_non_recurring_bonus_percent -- added for NL Phase 2B
1644 ,p_recurring_bonus_balance => p_recurring_bonus_balance -- added for NL Phase 2B
1645 ,p_non_recurring_bonus_balance => p_non_recurring_bonus_balance -- added for NL Phase 2B
1646 ,p_std_tax_reduction => p_std_tax_reduction -- added for NL Phase 2B
1647 ,p_spl_tax_reduction => p_spl_tax_reduction -- added for NL Phase 2B
1648 ,p_sig_sal_spl_tax_reduction => p_sig_sal_spl_tax_reduction -- added for NL Phase 2B
1649 ,p_sig_sal_non_tax_reduction => p_sig_sal_non_tax_reduction -- added for NL Phase 2B
1650 ,p_sig_sal_std_tax_reduction => p_sig_sal_std_tax_reduction -- added for NL Phase 2B
1651 ,p_sii_std_tax_reduction => p_sii_std_tax_reduction -- added for NL Phase 2B
1652 ,p_sii_spl_tax_reduction => p_sii_spl_tax_reduction -- added for NL Phase 2B
1653 ,p_sii_non_tax_reduction => p_sii_non_tax_reduction -- added for NL Phase 2B
1654 ,p_previous_year_bonus_included => p_previous_year_bonus_included -- added for NL Phase 2B
1655 ,p_recurring_bonus_period => p_recurring_bonus_period -- added for NL Phase 2B
1656 ,p_non_recurring_bonus_period => p_non_recurring_bonus_period -- added for NL Phase 2B
1657 ,p_ee_age_threshold => p_ee_age_threshold -- added for ABP TAR fixes
1658 ,p_er_age_threshold => p_er_age_threshold -- added for ABP TAR fixes
1659 ,p_ee_age_contribution => p_ee_age_contribution -- added for ABP TAR fixes
1660 ,p_er_age_contribution => p_er_age_contribution -- added for ABP TAR fixes
1661 ,p_pension_type_id => l_pension_type_id
1662 ,p_object_version_number => l_object_version_number
1663 ,p_effective_start_date => l_effective_start_date
1664 ,p_effective_end_date => l_effective_end_date
1665 );
1666
1667 -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1668 -- Call After Process User Hook
1669 -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1670
1671 begin
1672 PQP_Pension_Types_BK1.Create_Pension_Type_a
1673 (p_validate => p_validate
1674 ,p_effective_date => l_effective_date
1675 ,p_pension_type_name => p_pension_type_name
1676 ,p_pension_category => p_pension_category
1677 ,p_pension_provider_type => p_pension_provider_type
1678 ,p_salary_calculation_method => p_salary_calculation_method
1679 ,p_threshold_conversion_rule => p_threshold_conversion_rule
1680 ,p_contribution_conversion_rule => p_contribution_conversion_rule
1681 ,p_er_annual_limit => l_er_annual_limit
1682 ,p_ee_annual_limit => l_ee_annual_limit
1683 ,p_er_annual_salary_threshold => l_er_annual_salary_threshold
1684 ,p_ee_annual_salary_threshold => l_ee_annual_salary_threshold
1685 ,p_business_group_id => p_business_group_id
1686 ,p_legislation_code => p_legislation_code
1687 ,p_description => p_description
1688 ,p_minimum_age => p_minimum_age
1689 ,p_ee_contribution_percent => p_ee_contribution_percent
1690 ,p_maximum_age => p_maximum_age
1691 ,p_er_contribution_percent => p_er_contribution_percent
1692 ,p_ee_annual_contribution => p_ee_annual_contribution
1693 ,p_er_annual_contribution => p_er_annual_contribution
1694 ,p_annual_premium_amount => p_annual_premium_amount
1695 ,p_ee_contribution_bal_type_id => p_ee_contribution_bal_type_id
1696 ,p_er_contribution_bal_type_id => p_er_contribution_bal_type_id
1697 ,p_balance_init_element_type_id => p_balance_init_element_type_id
1698 ,p_ee_contribution_fixed_rate => p_ee_contribution_fixed_rate
1699 ,p_er_contribution_fixed_rate => p_er_contribution_fixed_rate
1700 ,p_pty_attribute_category => p_pty_attribute_category
1701 ,p_pty_attribute1 => p_pty_attribute1
1702 ,p_pty_attribute2 => p_pty_attribute2
1703 ,p_pty_attribute3 => p_pty_attribute3
1704 ,p_pty_attribute4 => p_pty_attribute4
1705 ,p_pty_attribute5 => p_pty_attribute5
1706 ,p_pty_attribute6 => p_pty_attribute6
1707 ,p_pty_attribute7 => p_pty_attribute7
1708 ,p_pty_attribute8 => p_pty_attribute8
1709 ,p_pty_attribute9 => p_pty_attribute9
1710 ,p_pty_attribute10 => p_pty_attribute10
1711 ,p_pty_attribute11 => p_pty_attribute11
1712 ,p_pty_attribute12 => p_pty_attribute12
1713 ,p_pty_attribute13 => p_pty_attribute13
1714 ,p_pty_attribute14 => p_pty_attribute14
1715 ,p_pty_attribute15 => p_pty_attribute15
1716 ,p_pty_attribute16 => p_pty_attribute16
1717 ,p_pty_attribute17 => p_pty_attribute17
1718 ,p_pty_attribute18 => p_pty_attribute18
1719 ,p_pty_attribute19 => p_pty_attribute19
1720 ,p_pty_attribute20 => p_pty_attribute20
1721 ,p_pty_information_category => p_pty_information_category
1722 ,p_pty_information1 => p_pty_information1
1723 ,p_pty_information2 => p_pty_information2
1724 ,p_pty_information3 => p_pty_information3
1725 ,p_pty_information4 => p_pty_information4
1726 ,p_pty_information5 => p_pty_information5
1727 ,p_pty_information6 => p_pty_information6
1728 ,p_pty_information7 => p_pty_information7
1729 ,p_pty_information8 => p_pty_information8
1730 ,p_pty_information9 => p_pty_information9
1731 ,p_pty_information10 => p_pty_information10
1732 ,p_pty_information11 => p_pty_information11
1733 ,p_pty_information12 => p_pty_information12
1734 ,p_pty_information13 => p_pty_information13
1735 ,p_pty_information14 => p_pty_information14
1736 ,p_pty_information15 => p_pty_information15
1737 ,p_pty_information16 => p_pty_information16
1738 ,p_pty_information17 => p_pty_information17
1739 ,p_pty_information18 => p_pty_information18
1740 ,p_pty_information19 => p_pty_information19
1741 ,p_pty_information20 => p_pty_information20
1742 ,p_special_pension_type_code => p_special_pension_type_code -- added for NL Phase 2B
1743 ,p_pension_sub_category => p_pension_sub_category -- added for NL Phase 2B
1744 ,p_pension_basis_calc_method => p_pension_basis_calc_method -- added for NL Phase 2B
1745 ,p_pension_salary_balance => p_pension_salary_balance -- added for NL Phase 2B
1746 ,p_recurring_bonus_percent => p_recurring_bonus_percent -- added for NL Phase 2B
1747 ,p_non_recurring_bonus_percent => p_non_recurring_bonus_percent -- added for NL Phase 2B
1748 ,p_recurring_bonus_balance => p_recurring_bonus_balance -- added for NL Phase 2B
1749 ,p_non_recurring_bonus_balance => p_non_recurring_bonus_balance -- added for NL Phase 2B
1750 ,p_std_tax_reduction => p_std_tax_reduction -- added for NL Phase 2B
1751 ,p_spl_tax_reduction => p_spl_tax_reduction -- added for NL Phase 2B
1752 ,p_sig_sal_spl_tax_reduction => p_sig_sal_spl_tax_reduction -- added for NL Phase 2B
1753 ,p_sig_sal_non_tax_reduction => p_sig_sal_non_tax_reduction -- added for NL Phase 2B
1754 ,p_sig_sal_std_tax_reduction => p_sig_sal_std_tax_reduction -- added for NL Phase 2B
1755 ,p_sii_std_tax_reduction => p_sii_std_tax_reduction -- added for NL Phase 2B
1756 ,p_sii_spl_tax_reduction => p_sii_spl_tax_reduction -- added for NL Phase 2B
1757 ,p_sii_non_tax_reduction => p_sii_non_tax_reduction -- added for NL Phase 2B
1758 ,p_previous_year_bonus_included => p_previous_year_bonus_included -- added for NL Phase 2B
1759 ,p_recurring_bonus_period => p_recurring_bonus_period -- added for NL Phase 2B
1760 ,p_non_recurring_bonus_period => p_non_recurring_bonus_period -- added for NL Phase 2B
1761 ,p_ee_age_threshold => p_ee_age_threshold -- added for ABP TAR fixes
1762 ,p_er_age_threshold => p_er_age_threshold -- added for ABP TAR fixes
1763 ,p_ee_age_contribution => p_ee_age_contribution -- added for ABP TAR fixes
1764 ,p_er_age_contribution => p_er_age_contribution -- added for ABP TAR fixes
1765 ,p_pension_type_id => l_pension_type_id
1766 ,p_object_version_number => l_object_version_number
1767 ,p_effective_start_date => l_effective_start_date
1768 ,p_effective_end_date => l_effective_end_date
1769 ,p_api_warning => l_api_warning
1770
1771 );
1772 exception
1773 when hr_api.cannot_find_prog_unit then
1774 hr_api.cannot_find_prog_unit_error
1775 (p_module_name => 'Create_Pension_Type'
1776 ,p_hook_type => 'AP'
1777 );
1778 end;
1779
1780 -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1781 -- When in validation only mode raise the Validate_Enabled exception
1782 -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1783 If p_validate Then
1784 raise hr_api.validate_enabled;
1785 End If;
1786 -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1787 -- Set all output arguments
1788 -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1789 p_pension_type_id := l_pension_type_id;
1790 p_object_version_number := l_object_version_number;
1791 p_api_warning := l_api_warning;
1792 p_effective_start_date := l_effective_start_date;
1793 p_effective_end_date := l_effective_end_date;
1794
1795 --
1796 hr_utility.set_location(' Leaving:'||l_proc, 70);
1797 Exception
1798 When hr_api.validate_enabled Then
1799 --
1800 -- As the Validate_Enabled exception has been raised
1801 -- we must rollback to the savepoint
1802 --
1803 Rollback To Create_Pension_Type;
1804 --
1805 -- Only set output warning arguments
1806 -- (Any key or derived arguments must be set to null
1807 -- when validation only mode is being used.)
1808 --
1809 l_pension_type_id := Null;
1810 p_object_version_number := Null;
1811 p_effective_start_date := Null;
1812 p_effective_end_date := Null;
1813 p_api_warning := l_api_warning;
1814 hr_utility.set_location(' Leaving:'||l_proc, 80);
1815 When Others Then
1816 --
1817 -- A validation or unexpected error has occured
1818 --
1819 Rollback to Create_Pension_Type;
1820 hr_utility.set_location('error is : '||SQLERRM,85);
1821 hr_utility.set_location(' Leaving:'||l_proc, 90);
1822 Raise;
1823 End Create_Pension_Type;
1824 --
1825 -- ----------------------------------------------------------------------------
1826 -- |--------------------------< Update_Pension_Type >--------------------------|
1827 -- ----------------------------------------------------------------------------
1828 Procedure Update_Pension_Type
1829 (p_validate in boolean
1830 ,p_effective_date in date
1831 ,p_datetrack_mode in varchar2
1832 ,p_pension_type_id in number
1833 ,p_object_version_number in out nocopy number
1834 ,p_pension_type_name in varchar2 --x
1835 ,p_pension_category in varchar2 --x
1836 ,p_pension_provider_type in varchar2
1837 ,p_salary_calculation_method in varchar2
1838 ,p_threshold_conversion_rule in varchar2
1839 ,p_contribution_conversion_rule in varchar2
1840 ,p_er_annual_limit in number
1841 ,p_ee_annual_limit in number
1842 ,p_er_annual_salary_threshold in number
1843 ,p_ee_annual_salary_threshold in number
1844 ,p_business_group_id in number --x
1845 ,p_legislation_code in varchar2 --x
1846 ,p_description in varchar2
1847 ,p_minimum_age in number
1848 ,p_ee_contribution_percent in number
1849 ,p_maximum_age in number
1850 ,p_er_contribution_percent in number
1851 ,p_ee_annual_contribution in number
1852 ,p_er_annual_contribution in number
1853 ,p_annual_premium_amount in number
1854 ,p_ee_contribution_bal_type_id in number --x
1855 ,p_er_contribution_bal_type_id in number --x
1856 ,p_balance_init_element_type_id in number --x
1857 ,p_ee_contribution_fixed_rate in number --added for UK
1858 ,p_er_contribution_fixed_rate in number --added for UK
1859 ,p_pty_attribute_category in varchar2
1860 ,p_pty_attribute1 in varchar2
1861 ,p_pty_attribute2 in varchar2
1862 ,p_pty_attribute3 in varchar2
1863 ,p_pty_attribute4 in varchar2
1864 ,p_pty_attribute5 in varchar2
1865 ,p_pty_attribute6 in varchar2
1866 ,p_pty_attribute7 in varchar2
1867 ,p_pty_attribute8 in varchar2
1868 ,p_pty_attribute9 in varchar2
1869 ,p_pty_attribute10 in varchar2
1870 ,p_pty_attribute11 in varchar2
1871 ,p_pty_attribute12 in varchar2
1872 ,p_pty_attribute13 in varchar2
1873 ,p_pty_attribute14 in varchar2
1874 ,p_pty_attribute15 in varchar2
1875 ,p_pty_attribute16 in varchar2
1876 ,p_pty_attribute17 in varchar2
1877 ,p_pty_attribute18 in varchar2
1878 ,p_pty_attribute19 in varchar2
1879 ,p_pty_attribute20 in varchar2
1880 ,p_pty_information_category in varchar2
1881 ,p_pty_information1 in varchar2
1882 ,p_pty_information2 in varchar2
1883 ,p_pty_information3 in varchar2
1884 ,p_pty_information4 in varchar2
1885 ,p_pty_information5 in varchar2
1886 ,p_pty_information6 in varchar2
1887 ,p_pty_information7 in varchar2
1888 ,p_pty_information8 in varchar2
1889 ,p_pty_information9 in varchar2
1890 ,p_pty_information10 in varchar2
1891 ,p_pty_information11 in varchar2
1892 ,p_pty_information12 in varchar2
1893 ,p_pty_information13 in varchar2
1894 ,p_pty_information14 in varchar2
1895 ,p_pty_information15 in varchar2
1896 ,p_pty_information16 in varchar2
1897 ,p_pty_information17 in varchar2
1898 ,p_pty_information18 in varchar2
1899 ,p_pty_information19 in varchar2
1900 ,p_pty_information20 in varchar2
1901 ,p_special_pension_type_code in varchar2 -- added for NL Phase 2B
1902 ,p_pension_sub_category in varchar2 -- added for NL Phase 2B
1903 ,p_pension_basis_calc_method in varchar2 -- added for NL Phase 2B
1904 ,p_pension_salary_balance in number -- added for NL Phase 2B
1905 ,p_recurring_bonus_percent in number -- added for NL Phase 2B
1906 ,p_non_recurring_bonus_percent in number -- added for NL Phase 2B
1907 ,p_recurring_bonus_balance in number -- added for NL Phase 2B
1908 ,p_non_recurring_bonus_balance in number -- added for NL Phase 2B
1909 ,p_std_tax_reduction in varchar2 -- added for NL Phase 2B
1910 ,p_spl_tax_reduction in varchar2 -- added for NL Phase 2B
1911 ,p_sig_sal_spl_tax_reduction in varchar2 -- added for NL Phase 2B
1912 ,p_sig_sal_non_tax_reduction in varchar2 -- added for NL Phase 2B
1913 ,p_sig_sal_std_tax_reduction in varchar2 -- added for NL Phase 2B
1914 ,p_sii_std_tax_reduction in varchar2 -- added for NL Phase 2B
1915 ,p_sii_spl_tax_reduction in varchar2 -- added for NL Phase 2B
1916 ,p_sii_non_tax_reduction in varchar2 -- added for NL Phase 2B
1917 ,p_previous_year_bonus_included in varchar2 -- added for NL Phase 2B
1918 ,p_recurring_bonus_period in varchar2 -- added for NL Phase 2B
1919 ,p_non_recurring_bonus_period in varchar2 -- added for NL Phase 2B
1920 ,p_ee_age_threshold in varchar2 -- added for ABP TAR fixes
1921 ,p_er_age_threshold in varchar2 -- added for ABP TAR fixes
1922 ,p_ee_age_contribution in varchar2 -- added for ABP TAR fixes
1923 ,p_er_age_contribution in varchar2 -- added for ABP TAR fixes
1924 ,p_effective_start_date out nocopy date
1925 ,p_effective_end_date out nocopy date
1926 ,p_api_warning out nocopy varchar2
1927 ) Is
1928 l_proc varchar2(150) := g_package||'Update_Pension_Type';
1929 l_object_version_number pqp_pension_types_f.object_version_number%TYPE;
1930 l_effective_date date;
1931 l_effective_start_date date;
1932 l_effective_end_date date;
1933 l_sum_percent number(7,4);
1934 l_er_annual_limit number;
1935 l_ee_annual_limit number;
1936 l_er_annual_salary_threshold number;
1937 l_ee_annual_salary_threshold number;
1938 l_category varchar2(30);
1939 l_schm_exists number;
1940 l_api_warning number := hr_api.g_number;
1941
1942 CURSOR c_chk_scheme IS
1943 SELECT 1
1944 FROM pay_element_type_extra_info
1945 WHERE information_type = 'HU_PENSION_SCHEME_INFO'
1946 AND eei_information_category = 'HU_PENSION_SCHEME_INFO'
1947 AND eei_information3 = fnd_number.number_to_canonical(p_pension_type_id);
1948
1949 CURSOR c_get_category IS
1950 SELECT pension_category
1951 FROM pqp_pension_types_f
1952 WHERE pension_type_id = p_pension_type_id
1953 AND p_effective_date BETWEEN effective_start_date
1954 AND effective_end_date;
1955
1956 Begin
1957 p_api_warning := hr_api.g_varchar2;
1958 hr_utility.set_location('Entering:'|| l_proc, 10);
1959 -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1960 -- Issue a savepoint
1961 -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1962 savepoint Update_Pension_Type;
1963
1964 if(p_salary_calculation_method = '3') then
1965 l_sum_percent := p_ee_contribution_percent + p_er_contribution_percent;
1966
1967 if l_sum_percent Is Null then
1968 fnd_message.set_name('PQP', 'PQP_230892_PEN_TYPE_PERCNT_INV');
1969 fnd_message.raise_error;
1970 end if;
1971
1972 if((l_sum_percent > 100) or (l_sum_percent < 100)) then
1973 fnd_message.set_name('PQP', 'PQP_230892_PEN_TYPE_PERCNT_INV');
1974 fnd_message.raise_error;
1975 end if;
1976 end if;
1977
1978 -- check to see if the annual limits have been entered for Savings Pension Type
1979 -- when the salary calculation method is null
1980
1981 if (p_salary_calculation_method is Null and p_pension_category = 'S') then
1982
1983 if(p_er_annual_limit is Null or p_ee_annual_limit is Null) then
1984
1985 fnd_message.set_name('PQP', 'PQP_230990_SAV_NULL_ANN_LIMITS');
1986 fnd_message.raise_error;
1987
1988 end if;
1989
1990 end if;
1991
1992 -- Check the defaults for EE and ER limits/thresholds
1993 if(p_salary_calculation_method = '3') then
1994 l_er_annual_limit := NULL;
1995 l_ee_annual_limit := NULL;
1996 l_er_annual_salary_threshold := NULL;
1997 l_ee_annual_salary_threshold := NULL;
1998 else
1999 l_er_annual_limit := p_er_annual_limit;
2000 l_ee_annual_limit := p_ee_annual_limit;
2001 l_er_annual_salary_threshold := p_er_annual_salary_threshold;
2002 l_ee_annual_salary_threshold := p_ee_annual_salary_threshold;
2003 end if;
2004
2005 --
2006 -- check that the total contribution percentage is atleast equal to the
2007 -- employee contribution percentage
2008 --
2009 IF p_special_pension_type_code IS NOT NULL THEN
2010 IF p_special_pension_type_code = 'PGGM' THEN
2011 IF nvl(p_er_contribution_percent,0) <
2012 nvl(p_ee_contribution_percent,0) THEN
2013 fnd_message.set_name('PQP','PQP_230221_PGGM_INV_PERCENTAGE');
2014 fnd_message.raise_error;
2015 END IF;
2016 END IF;
2017 END IF;
2018
2019 --validation added for HU (allow change of category only if no scheme exists)
2020 OPEN c_get_category;
2021 FETCH c_get_category INTO l_category;
2022 CLOSE c_get_category;
2023
2024 IF l_category <> p_pension_category THEN
2025 OPEN c_chk_scheme;
2026 FETCH c_chk_scheme INTO l_schm_exists;
2027 IF c_chk_scheme%FOUND THEN
2028 CLOSE c_chk_scheme;
2029 fnd_message.set_name('PQP','PQP_230125_PT_UPD_NOT_ALLOWED');
2030 fnd_message.raise_error;
2031 ELSE
2032 CLOSE c_chk_scheme;
2033 END IF;
2034 END IF;
2035
2036 -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
2037 -- Truncate the time portion from all IN date parameters
2038 -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
2039 l_effective_date := Trunc(p_effective_date);
2040 l_object_version_number := p_object_version_number;
2041 -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
2042 -- Call Before Process User Hook
2043 -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
2044
2045 begin
2046 hr_utility.set_location('Before Calling User Hook Update_Pension_Type_b',20);
2047 PQP_Pension_Types_BK2.Update_Pension_Type_b
2048 (p_validate => p_validate
2049 ,p_effective_date => p_effective_date
2050 ,p_datetrack_mode => p_datetrack_mode
2051 ,p_pension_type_id => p_pension_type_id
2052 ,p_object_version_number => l_object_version_number
2053 ,p_pension_type_name => p_pension_type_name
2054 ,p_pension_category => p_pension_category
2055 ,p_pension_provider_type => p_pension_provider_type
2056 ,p_salary_calculation_method => p_salary_calculation_method
2057 ,p_threshold_conversion_rule => p_threshold_conversion_rule
2058 ,p_contribution_conversion_rule => p_contribution_conversion_rule
2059 ,p_er_annual_limit => l_er_annual_limit
2060 ,p_ee_annual_limit => l_ee_annual_limit
2061 ,p_er_annual_salary_threshold => l_er_annual_salary_threshold
2062 ,p_ee_annual_salary_threshold => l_ee_annual_salary_threshold
2063 ,p_business_group_id => p_business_group_id
2064 ,p_legislation_code => p_legislation_code
2065 ,p_description => p_description
2066 ,p_minimum_age => p_minimum_age
2067 ,p_ee_contribution_percent => p_ee_contribution_percent
2068 ,p_maximum_age => p_maximum_age
2069 ,p_er_contribution_percent => p_er_contribution_percent
2070 ,p_ee_annual_contribution => p_ee_annual_contribution
2071 ,p_er_annual_contribution => p_er_annual_contribution
2072 ,p_annual_premium_amount => p_annual_premium_amount
2073 ,p_ee_contribution_bal_type_id => p_ee_contribution_bal_type_id
2074 ,p_er_contribution_bal_type_id => p_er_contribution_bal_type_id
2075 ,p_balance_init_element_type_id => p_balance_init_element_type_id
2076 ,p_ee_contribution_fixed_rate => p_ee_contribution_fixed_rate --added for UK
2077 ,p_er_contribution_fixed_rate => p_er_contribution_fixed_rate --added for UK
2078 ,p_pty_attribute_category => p_pty_attribute_category
2079 ,p_pty_attribute1 => p_pty_attribute1
2080 ,p_pty_attribute2 => p_pty_attribute2
2081 ,p_pty_attribute3 => p_pty_attribute3
2082 ,p_pty_attribute4 => p_pty_attribute4
2083 ,p_pty_attribute5 => p_pty_attribute5
2084 ,p_pty_attribute6 => p_pty_attribute6
2085 ,p_pty_attribute7 => p_pty_attribute7
2086 ,p_pty_attribute8 => p_pty_attribute8
2087 ,p_pty_attribute9 => p_pty_attribute9
2088 ,p_pty_attribute10 => p_pty_attribute10
2089 ,p_pty_attribute11 => p_pty_attribute11
2090 ,p_pty_attribute12 => p_pty_attribute12
2091 ,p_pty_attribute13 => p_pty_attribute13
2092 ,p_pty_attribute14 => p_pty_attribute14
2093 ,p_pty_attribute15 => p_pty_attribute15
2094 ,p_pty_attribute16 => p_pty_attribute16
2095 ,p_pty_attribute17 => p_pty_attribute17
2096 ,p_pty_attribute18 => p_pty_attribute18
2097 ,p_pty_attribute19 => p_pty_attribute19
2098 ,p_pty_attribute20 => p_pty_attribute20
2099 ,p_pty_information_category => p_pty_information_category
2100 ,p_pty_information1 => p_pty_information1
2101 ,p_pty_information2 => p_pty_information2
2102 ,p_pty_information3 => p_pty_information3
2103 ,p_pty_information4 => p_pty_information4
2104 ,p_pty_information5 => p_pty_information5
2105 ,p_pty_information6 => p_pty_information6
2106 ,p_pty_information7 => p_pty_information7
2107 ,p_pty_information8 => p_pty_information8
2108 ,p_pty_information9 => p_pty_information9
2109 ,p_pty_information10 => p_pty_information10
2110 ,p_pty_information11 => p_pty_information11
2111 ,p_pty_information12 => p_pty_information12
2112 ,p_pty_information13 => p_pty_information13
2113 ,p_pty_information14 => p_pty_information14
2114 ,p_pty_information15 => p_pty_information15
2115 ,p_pty_information16 => p_pty_information16
2116 ,p_pty_information17 => p_pty_information17
2117 ,p_pty_information18 => p_pty_information18
2118 ,p_pty_information19 => p_pty_information19
2119 ,p_pty_information20 => p_pty_information20
2120 ,p_special_pension_type_code => p_special_pension_type_code -- added for NL Phase 2B
2121 ,p_pension_sub_category => p_pension_sub_category -- added for NL Phase 2B
2122 ,p_pension_basis_calc_method => p_pension_basis_calc_method -- added for NL Phase 2B
2123 ,p_pension_salary_balance => p_pension_salary_balance -- added for NL Phase 2B
2124 ,p_recurring_bonus_percent => p_recurring_bonus_percent -- added for NL Phase 2B
2125 ,p_non_recurring_bonus_percent => p_non_recurring_bonus_percent -- added for NL Phase 2B
2126 ,p_recurring_bonus_balance => p_recurring_bonus_balance -- added for NL Phase 2B
2127 ,p_non_recurring_bonus_balance => p_non_recurring_bonus_balance -- added for NL Phase 2B
2128 ,p_std_tax_reduction => p_std_tax_reduction -- added for NL Phase 2B
2129 ,p_spl_tax_reduction => p_spl_tax_reduction -- added for NL Phase 2B
2130 ,p_sig_sal_spl_tax_reduction => p_sig_sal_spl_tax_reduction -- added for NL Phase 2B
2131 ,p_sig_sal_non_tax_reduction => p_sig_sal_non_tax_reduction -- added for NL Phase 2B
2132 ,p_sig_sal_std_tax_reduction => p_sig_sal_std_tax_reduction -- added for NL Phase 2B
2133 ,p_sii_std_tax_reduction => p_sii_std_tax_reduction -- added for NL Phase 2B
2134 ,p_sii_spl_tax_reduction => p_sii_spl_tax_reduction -- added for NL Phase 2B
2135 ,p_sii_non_tax_reduction => p_sii_non_tax_reduction -- added for NL Phase 2B
2136 ,p_previous_year_bonus_included => p_previous_year_bonus_included -- added for NL Phase 2B
2137 ,p_recurring_bonus_period => p_recurring_bonus_period -- added for NL Phase 2B
2138 ,p_non_recurring_bonus_period => p_non_recurring_bonus_period -- added for NL Phase 2B
2139 ,p_ee_age_threshold => p_ee_age_threshold -- added for ABP TAR fixes
2140 ,p_er_age_threshold => p_er_age_threshold -- added for ABP TAR fixes
2141 ,p_ee_age_contribution => p_ee_age_contribution -- added for ABP TAR fixes
2142 ,p_er_age_contribution => p_er_age_contribution -- added for ABP TAR fixes
2143 );
2144 hr_utility.set_location('After Calling User Hook Update_Pension_Type_b',20);
2145 exception
2146 when hr_api.cannot_find_prog_unit then
2147 hr_utility.set_location('Exception in User Hook Update_Pension_Type_b',25);
2148 hr_api.cannot_find_prog_unit_error
2149 (p_module_name => 'Update_Pension_Type'
2150 ,p_hook_type => 'BP'
2151 );
2152 end;
2153 -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
2154 -- Validation in addition to Row Handlers
2155 -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
2156
2157
2158 -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
2159 -- Process Logic - Call the row-handler ins procedure
2160 -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
2161 hr_utility.set_location('Before calling row-handler pqp_pty_upd.upd',30);
2162 pqp_pty_upd.upd
2163 (p_effective_date => l_effective_date
2164 ,p_datetrack_mode => p_datetrack_mode
2165 ,p_pension_type_id => p_pension_type_id
2166 ,p_object_version_number => l_object_version_number
2167 --,p_pension_type_name => p_pension_type_name
2168 ,p_pension_category => p_pension_category
2169 ,p_pension_provider_type => p_pension_provider_type
2170 ,p_salary_calculation_method => p_salary_calculation_method
2171 ,p_threshold_conversion_rule => p_threshold_conversion_rule
2172 ,p_contribution_conversion_rule => p_contribution_conversion_rule
2173 ,p_er_annual_limit => l_er_annual_limit
2174 ,p_ee_annual_limit => l_ee_annual_limit
2175 ,p_er_annual_salary_threshold => l_er_annual_salary_threshold
2176 ,p_ee_annual_salary_threshold => l_ee_annual_salary_threshold
2177 ,p_business_group_id => p_business_group_id
2178 ,p_legislation_code => p_legislation_code
2179 ,p_description => p_description
2180 ,p_minimum_age => p_minimum_age
2181 ,p_ee_contribution_percent => p_ee_contribution_percent
2182 ,p_maximum_age => p_maximum_age
2183 ,p_er_contribution_percent => p_er_contribution_percent
2184 ,p_ee_annual_contribution => p_ee_annual_contribution
2185 ,p_er_annual_contribution => p_er_annual_contribution
2186 ,p_annual_premium_amount => p_annual_premium_amount
2187 --,p_ee_contribution_bal_type_id => p_ee_contribution_bal_type_id
2188 --,p_er_contribution_bal_type_id => p_er_contribution_bal_type_id
2189 --,p_balance_init_element_type_id => p_balance_init_element_type_id
2190 ,p_ee_contribution_fixed_rate => p_ee_contribution_fixed_rate --added for UK
2191 ,p_er_contribution_fixed_rate => p_er_contribution_fixed_rate --added for UK
2192 ,p_pty_attribute_category => p_pty_attribute_category
2193 ,p_pty_attribute1 => p_pty_attribute1
2194 ,p_pty_attribute2 => p_pty_attribute2
2195 ,p_pty_attribute3 => p_pty_attribute3
2196 ,p_pty_attribute4 => p_pty_attribute4
2197 ,p_pty_attribute5 => p_pty_attribute5
2198 ,p_pty_attribute6 => p_pty_attribute6
2199 ,p_pty_attribute7 => p_pty_attribute7
2200 ,p_pty_attribute8 => p_pty_attribute8
2201 ,p_pty_attribute9 => p_pty_attribute9
2202 ,p_pty_attribute10 => p_pty_attribute10
2203 ,p_pty_attribute11 => p_pty_attribute11
2204 ,p_pty_attribute12 => p_pty_attribute12
2205 ,p_pty_attribute13 => p_pty_attribute13
2206 ,p_pty_attribute14 => p_pty_attribute14
2207 ,p_pty_attribute15 => p_pty_attribute15
2208 ,p_pty_attribute16 => p_pty_attribute16
2209 ,p_pty_attribute17 => p_pty_attribute17
2210 ,p_pty_attribute18 => p_pty_attribute18
2211 ,p_pty_attribute19 => p_pty_attribute19
2212 ,p_pty_attribute20 => p_pty_attribute20
2213 ,p_pty_information_category => p_pty_information_category
2214 ,p_pty_information1 => p_pty_information1
2215 ,p_pty_information2 => p_pty_information2
2216 ,p_pty_information3 => p_pty_information3
2217 ,p_pty_information4 => p_pty_information4
2218 ,p_pty_information5 => p_pty_information5
2219 ,p_pty_information6 => p_pty_information6
2220 ,p_pty_information7 => p_pty_information7
2221 ,p_pty_information8 => p_pty_information8
2222 ,p_pty_information9 => p_pty_information9
2223 ,p_pty_information10 => p_pty_information10
2224 ,p_pty_information11 => p_pty_information11
2225 ,p_pty_information12 => p_pty_information12
2226 ,p_pty_information13 => p_pty_information13
2227 ,p_pty_information14 => p_pty_information14
2228 ,p_pty_information15 => p_pty_information15
2229 ,p_pty_information16 => p_pty_information16
2230 ,p_pty_information17 => p_pty_information17
2231 ,p_pty_information18 => p_pty_information18
2232 ,p_pty_information19 => p_pty_information19
2233 ,p_pty_information20 => p_pty_information20
2234 ,p_special_pension_type_code => p_special_pension_type_code -- added for NL Phase 2B
2235 ,p_pension_sub_category => p_pension_sub_category -- added for NL Phase 2B
2236 ,p_pension_basis_calc_method => p_pension_basis_calc_method -- added for NL Phase 2B
2237 ,p_pension_salary_balance => p_pension_salary_balance -- added for NL Phase 2B
2238 ,p_recurring_bonus_percent => p_recurring_bonus_percent -- added for NL Phase 2B
2239 ,p_non_recurring_bonus_percent => p_non_recurring_bonus_percent -- added for NL Phase 2B
2240 ,p_recurring_bonus_balance => p_recurring_bonus_balance -- added for NL Phase 2B
2241 ,p_non_recurring_bonus_balance => p_non_recurring_bonus_balance -- added for NL Phase 2B
2242 ,p_std_tax_reduction => p_std_tax_reduction -- added for NL Phase 2B
2243 ,p_spl_tax_reduction => p_spl_tax_reduction -- added for NL Phase 2B
2244 ,p_sig_sal_spl_tax_reduction => p_sig_sal_spl_tax_reduction -- added for NL Phase 2B
2245 ,p_sig_sal_non_tax_reduction => p_sig_sal_non_tax_reduction -- added for NL Phase 2B
2246 ,p_sig_sal_std_tax_reduction => p_sig_sal_std_tax_reduction -- added for NL Phase 2B
2247 ,p_sii_std_tax_reduction => p_sii_std_tax_reduction -- added for NL Phase 2B
2248 ,p_sii_spl_tax_reduction => p_sii_spl_tax_reduction -- added for NL Phase 2B
2249 ,p_sii_non_tax_reduction => p_sii_non_tax_reduction -- added for NL Phase 2B
2250 ,p_previous_year_bonus_included => p_previous_year_bonus_included -- added for NL Phase 2B
2251 ,p_recurring_bonus_period => p_recurring_bonus_period -- added for NL Phase 2B
2252 ,p_non_recurring_bonus_period => p_non_recurring_bonus_period -- added for NL Phase 2B
2253 ,p_ee_age_threshold => p_ee_age_threshold -- added for ABP TAR fixes
2254 ,p_er_age_threshold => p_er_age_threshold -- added for ABP TAR fixes
2255 ,p_ee_age_contribution => p_ee_age_contribution -- added for ABP TAR fixes
2256 ,p_er_age_contribution => p_er_age_contribution -- added for ABP TAR fixes
2257 ,p_effective_start_date => l_effective_start_date
2258 ,p_effective_end_date => l_effective_end_date
2259 );
2260 -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
2261 -- Call After Process User Hook
2262 -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
2263 begin
2264 hr_utility.set_location(' Before Calling User Hook : Update_Pension_Type_a',20);
2265 PQP_Pension_Types_BK2.Update_Pension_Type_a
2266 (p_validate => p_validate
2267 ,p_effective_date => p_effective_date
2268 ,p_datetrack_mode => p_datetrack_mode
2269 ,p_pension_type_id => p_pension_type_id
2270 ,p_object_version_number => l_object_version_number
2271 ,p_pension_type_name => p_pension_type_name
2272 ,p_pension_category => p_pension_category
2273 ,p_pension_provider_type => p_pension_provider_type
2274 ,p_salary_calculation_method => p_salary_calculation_method
2275 ,p_threshold_conversion_rule => p_threshold_conversion_rule
2276 ,p_contribution_conversion_rule => p_contribution_conversion_rule
2277 ,p_er_annual_limit => l_er_annual_limit
2278 ,p_ee_annual_limit => l_ee_annual_limit
2279 ,p_er_annual_salary_threshold => l_er_annual_salary_threshold
2280 ,p_ee_annual_salary_threshold => l_ee_annual_salary_threshold
2281 ,p_business_group_id => p_business_group_id
2282 ,p_legislation_code => p_legislation_code
2283 ,p_description => p_description
2284 ,p_minimum_age => p_minimum_age
2285 ,p_ee_contribution_percent => p_ee_contribution_percent
2286 ,p_maximum_age => p_maximum_age
2287 ,p_er_contribution_percent => p_er_contribution_percent
2288 ,p_ee_annual_contribution => p_ee_annual_contribution
2289 ,p_er_annual_contribution => p_er_annual_contribution
2290 ,p_annual_premium_amount => p_annual_premium_amount
2291 ,p_ee_contribution_bal_type_id => p_ee_contribution_bal_type_id
2292 ,p_er_contribution_bal_type_id => p_er_contribution_bal_type_id
2293 ,p_balance_init_element_type_id => p_balance_init_element_type_id
2294 ,p_ee_contribution_fixed_rate => p_ee_contribution_fixed_rate --added for UK
2295 ,p_er_contribution_fixed_rate => p_er_contribution_fixed_rate --added for UK
2296 ,p_pty_attribute_category => p_pty_attribute_category
2297 ,p_pty_attribute1 => p_pty_attribute1
2298 ,p_pty_attribute2 => p_pty_attribute2
2299 ,p_pty_attribute3 => p_pty_attribute3
2300 ,p_pty_attribute4 => p_pty_attribute4
2301 ,p_pty_attribute5 => p_pty_attribute5
2302 ,p_pty_attribute6 => p_pty_attribute6
2303 ,p_pty_attribute7 => p_pty_attribute7
2304 ,p_pty_attribute8 => p_pty_attribute8
2305 ,p_pty_attribute9 => p_pty_attribute9
2306 ,p_pty_attribute10 => p_pty_attribute10
2307 ,p_pty_attribute11 => p_pty_attribute11
2308 ,p_pty_attribute12 => p_pty_attribute12
2309 ,p_pty_attribute13 => p_pty_attribute13
2310 ,p_pty_attribute14 => p_pty_attribute14
2311 ,p_pty_attribute15 => p_pty_attribute15
2312 ,p_pty_attribute16 => p_pty_attribute16
2313 ,p_pty_attribute17 => p_pty_attribute17
2314 ,p_pty_attribute18 => p_pty_attribute18
2315 ,p_pty_attribute19 => p_pty_attribute19
2316 ,p_pty_attribute20 => p_pty_attribute20
2317 ,p_pty_information_category => p_pty_information_category
2318 ,p_pty_information1 => p_pty_information1
2319 ,p_pty_information2 => p_pty_information2
2320 ,p_pty_information3 => p_pty_information3
2321 ,p_pty_information4 => p_pty_information4
2322 ,p_pty_information5 => p_pty_information5
2323 ,p_pty_information6 => p_pty_information6
2324 ,p_pty_information7 => p_pty_information7
2325 ,p_pty_information8 => p_pty_information8
2326 ,p_pty_information9 => p_pty_information9
2327 ,p_pty_information10 => p_pty_information10
2328 ,p_pty_information11 => p_pty_information11
2329 ,p_pty_information12 => p_pty_information12
2330 ,p_pty_information13 => p_pty_information13
2331 ,p_pty_information14 => p_pty_information14
2332 ,p_pty_information15 => p_pty_information15
2333 ,p_pty_information16 => p_pty_information16
2334 ,p_pty_information17 => p_pty_information17
2335 ,p_pty_information18 => p_pty_information18
2336 ,p_pty_information19 => p_pty_information19
2337 ,p_pty_information20 => p_pty_information20
2338 ,p_special_pension_type_code => p_special_pension_type_code -- added for NL Phase 2B
2339 ,p_pension_sub_category => p_pension_sub_category -- added for NL Phase 2B
2340 ,p_pension_basis_calc_method => p_pension_basis_calc_method -- added for NL Phase 2B
2341 ,p_pension_salary_balance => p_pension_salary_balance -- added for NL Phase 2B
2342 ,p_recurring_bonus_percent => p_recurring_bonus_percent -- added for NL Phase 2B
2343 ,p_non_recurring_bonus_percent => p_non_recurring_bonus_percent -- added for NL Phase 2B
2344 ,p_recurring_bonus_balance => p_recurring_bonus_balance -- added for NL Phase 2B
2345 ,p_non_recurring_bonus_balance => p_non_recurring_bonus_balance -- added for NL Phase 2B
2346 ,p_std_tax_reduction => p_std_tax_reduction -- added for NL Phase 2B
2347 ,p_spl_tax_reduction => p_spl_tax_reduction -- added for NL Phase 2B
2348 ,p_sig_sal_spl_tax_reduction => p_sig_sal_spl_tax_reduction -- added for NL Phase 2B
2349 ,p_sig_sal_non_tax_reduction => p_sig_sal_non_tax_reduction -- added for NL Phase 2B
2350 ,p_sig_sal_std_tax_reduction => p_sig_sal_std_tax_reduction -- added for NL Phase 2B
2351 ,p_sii_std_tax_reduction => p_sii_std_tax_reduction -- added for NL Phase 2B
2352 ,p_sii_spl_tax_reduction => p_sii_spl_tax_reduction -- added for NL Phase 2B
2353 ,p_sii_non_tax_reduction => p_sii_non_tax_reduction -- added for NL Phase 2B
2354 ,p_previous_year_bonus_included => p_previous_year_bonus_included -- added for NL Phase 2B
2355 ,p_recurring_bonus_period => p_recurring_bonus_period -- added for NL Phase 2B
2356 ,p_non_recurring_bonus_period => p_non_recurring_bonus_period -- added for NL Phase 2B
2357 ,p_ee_age_threshold => p_ee_age_threshold -- added for ABP TAR fixes
2358 ,p_er_age_threshold => p_er_age_threshold -- added for ABP TAR fixes
2359 ,p_ee_age_contribution => p_ee_age_contribution -- added for ABP TAR fixes
2360 ,p_er_age_contribution => p_er_age_contribution -- added for ABP TAR fixes
2361 ,p_effective_start_date => l_effective_start_date
2362 ,p_effective_end_date => l_effective_end_date
2363 );
2364 hr_utility.set_location(' After Calling User Hook : Update_Pension_Type_a',20);
2365 Exception
2366 When hr_api.cannot_find_prog_unit Then
2367 hr_utility.set_location('Exception in User Hook : Update_Pension_Type_a',25);
2368 hr_api.cannot_find_prog_unit_error
2369 (p_module_name => 'Update_Pension_Type'
2370 ,p_hook_type => 'AP'
2371 );
2372 End;
2373 -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
2374 -- When in validation only mode raise the Validate_Enabled exception
2375 -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
2376 If p_validate Then
2377 raise hr_api.validate_enabled;
2378 End If;
2379
2380 -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
2381 -- Set all output arguments
2382 -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
2383 p_effective_start_date := l_effective_start_date;
2384 p_effective_end_date := l_effective_end_date;
2385 p_object_version_number := l_object_version_number;
2386 --p_api_warning := l_api_warning;
2387
2388 --
2389 hr_utility.set_location(' Leaving:'||l_proc, 70);
2390 Exception
2391 When hr_api.validate_enabled Then
2392 --
2393 -- As the Validate_Enabled exception has been raised
2394 -- we must rollback to the savepoint
2395 --
2396 Rollback To Update_Pension_Type;
2397 --
2398 -- Only set output warning arguments
2399 -- (Any key or derived arguments must be set to null
2400 -- when validation only mode is being used.)
2401 --
2402 p_effective_start_date := Null;
2403 p_effective_end_date := Null;
2404 --p_api_warning := l_api_warning;
2405 hr_utility.set_location(' Leaving:'||l_proc, 80);
2406 When Others Then
2407 --
2408 -- A validation or unexpected error has occured
2409 --
2410 Rollback to Update_Pension_Type;
2411 hr_utility.set_location(' Leaving:'||l_proc, 90);
2412 Raise;
2413
2414 End Update_Pension_Type;
2415
2416 -- ----------------------------------------------------------------------------
2417 -- |--------------------------< get_pty_bal_ele_info >------------------------|
2418 -- ----------------------------------------------------------------------------
2419 Procedure Get_Pty_Bal_Ele_Info
2420 (p_pension_type_id in number
2421 ,p_effective_date in date
2422 ,p_ee_contribution_bal_type_id out nocopy number
2423 ,p_er_contribution_bal_type_id out nocopy number
2424 ,p_balance_init_ele_type_id out nocopy number
2425 )
2426 Is
2427 --
2428 Cursor csr_get_pty_bal_ele_info
2429 Is
2430 Select ee_contribution_bal_type_id
2431 ,er_contribution_bal_type_id
2432 ,balance_init_element_type_id
2433 From pqp_pension_types_f
2434 Where pension_type_id = p_pension_type_id
2435 And p_effective_date Between effective_start_date
2436 And effective_end_date;
2437
2438 l_proc varchar2(80) := g_package || 'Get_Pty_Bal_Ele_Info';
2439 l_pty_rec csr_get_pty_bal_ele_info%ROWTYPE;
2440 --
2441 Begin
2442 --
2443 hr_utility.set_location ('Entering: '||l_proc, 10);
2444
2445 OPEN csr_get_pty_bal_ele_info;
2446 FETCH csr_get_pty_bal_ele_info INTO l_pty_rec;
2447 -- No need to check for cursor found as this
2448 -- will be handled in the row handler
2449 CLOSE csr_get_pty_bal_ele_info;
2450
2451 p_ee_contribution_bal_type_id := l_pty_rec.ee_contribution_bal_type_id;
2452 p_er_contribution_bal_type_id := l_pty_rec.er_contribution_bal_type_id;
2453 p_balance_init_ele_type_id := l_pty_rec.balance_init_element_type_id;
2454
2455 --
2456 hr_utility.set_location ('Leaving: '||l_proc, 20);
2457 --
2458 Exception
2459
2460 When Others Then
2461 hr_utility.set_location ('Others Exception occurred in '||l_proc, 30);
2462 p_ee_contribution_bal_type_id := NULL;
2463 p_er_contribution_bal_type_id := NULL;
2464 p_balance_init_ele_type_id := NULL;
2465 RAISE;
2466
2467 End Get_Pty_Bal_Ele_Info;
2468 --
2469
2470 -- ----------------------------------------------------------------------------
2471 -- |--------------------------< Delete_Balance_Init_Ele >---------------------|
2472 -- ----------------------------------------------------------------------------
2473 Procedure Delete_Balance_Init_Ele
2474 (p_balance_init_ele_type_id in number
2475 ,p_validate in boolean
2476 ,p_effective_date in date
2477 ,p_datetrack_mode in varchar2
2478 )
2479 Is
2480 --
2481 -- Cursor to retrieve input value information
2482 Cursor csr_get_ipv_info
2483 Is
2484 Select input_value_id
2485 ,object_version_number
2486 From pay_input_values_f
2487 Where element_type_id = p_balance_init_ele_type_id
2488 And p_effective_date Between effective_start_date
2489 And effective_end_date;
2490
2491 -- Cursor to retrieve element ovn
2492 Cursor csr_get_ele_ovn
2493 Is
2494 Select object_version_number
2495 From pay_element_types_f
2496 Where element_type_id = p_balance_init_ele_type_id
2497 And p_effective_date Between effective_start_date
2498 And effective_end_date;
2499
2500 l_proc varchar2(80) := g_package || 'Delete_Balance_Init_Ele';
2501 l_bal_feed_warn boolean;
2502 l_prs_rule_warn boolean;
2503 l_ele_ovn number;
2504 l_eff_start_date date;
2505 l_eff_end_date date;
2506
2507 --
2508 Begin
2509 --
2510 hr_utility.set_location ('Entering: '||l_proc, 10);
2511 --
2512
2513 -- Delete the input values for this element first
2514 -- Only do this if datetrack mode is not 'DELETE'
2515
2516 IF p_datetrack_mode <> 'DELETE' THEN
2517
2518 FOR csr_get_ipv_rec IN csr_get_ipv_info
2519 LOOP
2520
2521 -- Call the api to delete input values
2522 hr_utility.set_location (l_proc, 20);
2523 --
2524 pay_input_value_api.delete_input_value
2525 (p_validate => p_validate
2526 ,p_effective_date => p_effective_date
2527 ,p_datetrack_delete_mode => p_datetrack_mode
2528 ,p_input_value_id => csr_get_ipv_rec.input_value_id
2529 ,p_object_version_number => csr_get_ipv_rec.object_version_number
2530 ,p_effective_start_date => l_eff_start_date
2531 ,p_effective_end_date => l_eff_end_date
2532 ,p_balance_feeds_warning => l_bal_feed_warn
2533 );
2534
2535 END LOOP;
2536
2537 END IF; -- End if of date track mode not delete check ...
2538
2539 -- Delete the element now
2540 hr_utility.set_location (l_proc, 30);
2541
2542 -- Get ele ovn
2543 OPEN csr_get_ele_ovn;
2544 FETCH csr_get_ele_ovn INTO l_ele_ovn;
2545
2546 IF csr_get_ele_ovn%FOUND THEN
2547 -- Call API to delete element types
2548 pay_element_types_api.delete_element_type
2549 (p_validate => p_validate
2550 ,p_effective_date => p_effective_date
2551 ,p_datetrack_delete_mode => p_datetrack_mode
2552 ,p_element_type_id => p_balance_init_ele_type_id
2553 ,p_object_version_number => l_ele_ovn
2554 ,p_effective_start_date => l_eff_start_date
2555 ,p_effective_end_date => l_eff_end_date
2556 ,p_balance_feeds_warning => l_bal_feed_warn
2557 ,p_processing_rules_warning => l_prs_rule_warn
2558 );
2559
2560 END IF; -- End if of check element exists check ...
2561
2562 CLOSE csr_get_ele_ovn;
2563 --
2564 hr_utility.set_location ('Leaving: '||l_proc, 40);
2565 --
2566 End Delete_Balance_Init_Ele;
2567 --
2568
2569 -- ----------------------------------------------------------------------------
2570 -- |--------------------------< Delete_EE_ER_Balances >-----------------------|
2571 -- ----------------------------------------------------------------------------
2572 Procedure Delete_EE_ER_Balances
2573 (p_ee_contribution_bal_type_id in number
2574 ,p_er_contribution_bal_type_id in number
2575 )
2576 Is
2577 --
2578 -- Cursor to retrieve the rowid for balances
2579 Cursor csr_get_bal_rowid (c_balance_type_id number)
2580 Is
2581 Select rowid
2582 From pay_balance_types
2583 Where balance_type_id = c_balance_type_id;
2584
2585 l_proc varchar2(80) := g_package || 'Delete_EE_ER_Balances';
2586 l_rowid ROWID;
2587 i NUMBER;
2588
2589 TYPE t_number IS TABLE OF NUMBER
2590 INDEX BY BINARY_INTEGER;
2591
2592 l_bal_type_id t_number;
2593 --
2594 Begin
2595 --
2596 hr_utility.set_location ('Entering: '||l_proc, 10);
2597 --
2598 i := 0;
2599 i := i + 1 ;
2600 l_bal_type_id (i) := p_ee_contribution_bal_type_id;
2601
2602 i := i + 1;
2603 l_bal_type_id (i) := p_er_contribution_bal_type_id;
2604
2605 FOR i IN 1..l_bal_type_id.COUNT LOOP
2606
2607 -- Get the row id information for the balance
2608 OPEN csr_get_bal_rowid (l_bal_type_id (i));
2609 FETCH csr_get_bal_rowid INTO l_rowid;
2610
2611 IF csr_get_bal_rowid%FOUND THEN
2612
2613 -- Call api to delete balances
2614 -- This api does a delete cascade
2615 -- so no need to delete feeds / dimensions
2616 -- separately
2617
2618 pay_balance_types_pkg.delete_row
2619 (x_rowid => l_rowid
2620 ,x_balance_type_id => l_bal_type_id (i)
2621 );
2622 END IF; -- End if of balance row found check ...
2623 CLOSE csr_get_bal_rowid;
2624
2625 END LOOP;
2626 --
2627 hr_utility.set_location ('Leaving: '||l_proc, 20);
2628 --
2629 End Delete_EE_ER_Balances;
2630 --
2631
2632 -- ---------------------------------------------------------------------
2633 --|-----------------------<end_date_org_info>---------------------------|
2634 -- ---------------------------------------------------------------------
2635 --
2636 PROCEDURE end_date_org_info (
2637 p_pension_type_id in pqp_pension_types_f.pension_type_id%TYPE
2638 ,p_effective_end_date in date
2639 ) IS
2640
2641 CURSOR c_get_org_info IS
2642 SELECT rowid,
2643 org_information_id,
2644 organization_id,
2645 org_information1,
2646 nvl(org_information2,'31/12/4712') org_information2,
2647 org_information3
2648 FROM hr_organization_information
2649 WHERE org_information_context = 'PQP_NL_ABP_PT'
2650 AND org_information3 = to_char(p_pension_type_id)
2651 AND trunc(to_date(nvl(org_information2,'31/12/4712'),'DD/MM/RRRR')) > p_effective_end_date;
2652
2653 BEGIN
2654
2655 SAVEPOINT end_date_org_info;
2656 hr_utility.set_location('In end date org info'||p_pension_type_id,10);
2657 -- fetch all the org information rows in the org information EIT
2658 -- where the pension type is the same as the current pension type
2659 -- and the end date is greater than the effective end date
2660 FOR temp_rec in c_get_org_info
2661 LOOP
2662 hr_utility.set_location('In end date org info',20);
2663 -- for each of these rows call the hr_org_information_pkg.update_row proc
2664 hr_org_information_pkg.update_row
2665 (x_rowid => temp_rec.rowid
2666 ,x_org_information_id => temp_rec.org_information_id
2667 ,x_org_information_context => 'PQP_NL_ABP_PT'
2668 ,x_organization_id => temp_rec.organization_id
2669 ,x_org_information1 => temp_rec.org_information1
2670 ,x_org_information2 => to_char(p_effective_end_date,'DD-MON-RR')
2671 ,x_org_information3 => temp_rec.org_information3
2672 ,x_org_information4 => null
2673 ,x_org_information5 => null
2674 ,x_org_information6 => null
2675 ,x_org_information7 => null
2676 ,x_org_information8 => null
2677 ,x_org_information9 => null
2678 ,x_org_information10 => null
2679 ,x_org_information11 => null
2680 ,x_org_information12 => null
2681 ,x_org_information13 => null
2682 ,x_org_information14 => null
2683 ,x_org_information15 => null
2684 ,x_org_information16 => null
2685 ,x_org_information17 => null
2686 ,x_org_information18 => null
2687 ,x_org_information19 => null
2688 ,x_org_information20 => null
2689 ,x_attribute_category => null
2690 ,x_attribute1 => null
2691 ,x_attribute2 => null
2692 ,x_attribute3 => null
2693 ,x_attribute4 => null
2694 ,x_attribute5 => null
2695 ,x_attribute6 => null
2696 ,x_attribute7 => null
2697 ,x_attribute8 => null
2698 ,x_attribute9 => null
2699 ,x_attribute10 => null
2700 ,x_attribute11 => null
2701 ,x_attribute12 => null
2702 ,x_attribute13 => null
2703 ,x_attribute14 => null
2704 ,x_attribute15 => null
2705 ,x_attribute16 => null
2706 ,x_attribute17 => null
2707 ,x_attribute18 => null
2708 ,x_attribute19 => null
2709 ,x_attribute20 => null
2710 );
2711
2712 END LOOP;
2713
2714 EXCEPTION
2715
2716 WHEN Others THEN
2717
2718 rollback to end_date_org_info;
2719 RAISE;
2720
2721 END end_date_org_info;
2722
2723
2724 -- ----------------------------------------------------------------------------
2725 -- |--------------------------< Delete_Pension_Type >--------------------------|
2726 -- ----------------------------------------------------------------------------
2727 Procedure Delete_Pension_Type
2728 (p_validate in Boolean
2729 ,p_effective_date in date
2730 ,p_datetrack_mode in varchar2
2731 ,p_pension_type_id in number
2732 ,p_object_version_number in out nocopy number
2733 ,p_effective_start_date out nocopy date
2734 ,p_effective_end_date out nocopy date
2735 ,p_api_warning out nocopy varchar2
2736 )Is
2737
2738 l_proc varchar2(150) := g_package||'Delete_Pension_Type';
2739 l_object_version_number pqp_pension_types_f.object_version_number%TYPE;
2740 l_effective_date date;
2741 l_effective_start_date date;
2742 l_effective_end_date date;
2743 l_ee_contribution_bal_type_id number;
2744 l_er_contribution_bal_type_id number;
2745 l_balance_init_ele_type_id number;
2746 l_chk_abp_pt number;
2747
2748 --Cursor to check that the pension type is a ABP Pension Type
2749 CURSOR c_chk_abp_pt IS
2750 SELECT 1
2751 FROM pqp_pension_types_f
2752 WHERE pension_type_id = p_pension_type_id
2753 AND special_pension_type_code = 'ABP';
2754
2755 Begin
2756 -- hr_utility.trace_on(null,'rkpipe');
2757 -- if the start date is earlier than the least start date of the PT,raise error
2758 hr_utility.set_location('Entering:'|| l_proc, 10);
2759 -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
2760 -- Issue a savepoint
2761 -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
2762 savepoint Delete_Pension_Type;
2763 -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
2764 -- Truncate the time portion from all IN date parameters
2765 -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
2766 l_effective_date := Trunc(p_effective_date);
2767 l_object_version_number := p_object_version_number;
2768
2769 OPEN c_chk_abp_pt;
2770 FETCH c_chk_abp_pt INTO l_chk_abp_pt;
2771 IF c_chk_abp_pt%FOUND THEN
2772 -- if the pension type is a NL ABP PT,do not delete the EE/ER balances
2773 CLOSE c_chk_abp_pt;
2774 l_chk_abp_pt := 1;
2775 ELSE
2776 CLOSE c_chk_abp_pt;
2777 l_chk_abp_pt := 0;
2778 END IF;
2779
2780 -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
2781 -- Call Before Process User Hook
2782 -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
2783 Begin
2784 p_api_warning := hr_api.g_varchar2;
2785 hr_utility.set_location('Before Calling User Hook Delete_Pension_Type_b',20);
2786 PQP_Pension_Types_BK3.Delete_Pension_Type_b
2787 (p_validate => p_validate
2788 ,p_effective_date => p_effective_date
2789 ,p_datetrack_mode => p_datetrack_mode
2790 ,p_pension_type_id => p_pension_type_id
2791 ,p_object_version_number => l_object_version_number
2792 );
2793 hr_utility.set_location('After Calling User Hook Delete_Pension_Type_b',20);
2794 Exception
2795 When hr_api.cannot_find_prog_unit Then
2796 hr_utility.set_location('Exception in User Hook Delete_Pension_Type_b',25);
2797 hr_api.cannot_find_prog_unit_error
2798 (p_module_name => 'Delete_Pension_Type'
2799 ,p_hook_type => 'BP'
2800 );
2801 End;
2802
2803 -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
2804 -- Validation in addition to Row Handlers
2805 -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
2806 --
2807 -- Added code to delete EE and ER balances plus the intialization
2808 -- Element created by the pension types API before deleting
2809 -- pension type in ver 115.17
2810 -- We do not want to delete these objects now but only after deleting
2811 -- pension types, as there may be validations preventing
2812 -- deleting a pension type, so we want to validate this information
2813 -- first. But we will have to get the balance and element information
2814 -- associated with this pension type as this will be lost after
2815 -- calling del rhi procedure
2816
2817 -- Call function get_pty_bal_ele_info
2818 hr_utility.set_location (l_proc, 25);
2819 get_pty_bal_ele_info
2820 (p_pension_type_id => p_pension_type_id
2821 ,p_effective_date => p_effective_date
2822 ,p_ee_contribution_bal_type_id => l_ee_contribution_bal_type_id
2823 ,p_er_contribution_bal_type_id => l_er_contribution_bal_type_id
2824 ,p_balance_init_ele_type_id => l_balance_init_ele_type_id
2825 );
2826
2827 -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
2828 -- Process Logic - Call the row-handler del procedure
2829 -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
2830 hr_utility.set_location('Before calling row-handler pqp_pty_del.del',30);
2831 pqp_pty_del.del
2832 (p_effective_date => p_effective_date
2833 ,p_datetrack_mode => p_datetrack_mode
2834 ,p_pension_type_id => p_pension_type_id
2835 ,p_object_version_number => l_object_version_number
2836 ,p_effective_start_date => l_effective_start_date
2837 ,p_effective_end_date => l_effective_end_date
2838 );
2839
2840 -- Delete the EE and ER balances and Balance Init Element
2841 -- First delete the element
2842
2843 hr_utility.set_location (l_proc, 35);
2844 delete_balance_init_ele
2845 (p_balance_init_ele_type_id => l_balance_init_ele_type_id
2846 ,p_validate => p_validate
2847 ,p_effective_date => p_effective_date
2848 ,p_datetrack_mode => p_datetrack_mode
2849 );
2850
2851 -- Delete balance only if date track mode is ZAP
2852 hr_utility.set_location (l_proc, 36);
2853
2854 IF p_datetrack_mode = hr_api.g_zap THEN
2855 --only if the PT is not a ABP PT, delete the EE/ER balances
2856 IF l_chk_abp_pt = 0 THEN
2857 delete_ee_er_balances
2858 (p_ee_contribution_bal_type_id => l_ee_contribution_bal_type_id
2859 ,p_er_contribution_bal_type_id => l_er_contribution_bal_type_id
2860 );
2861 END IF;
2862
2863 END IF; -- End if of date track mode is ZAP check ...
2864
2865 -- End of changes for deleting balance and init elements
2866
2867 -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
2868 -- Call After Process User Hook
2869 -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
2870 Begin
2871 hr_utility.set_location('Before Calling User Hook Delete_Pension_Type_a',20);
2872 PQP_Pension_Types_BK3.Delete_Pension_Type_a
2873 (p_validate => p_validate
2874 ,p_effective_date => p_effective_date
2875 ,p_datetrack_mode => p_datetrack_mode
2876 ,p_pension_type_id => p_pension_type_id
2877 ,p_object_version_number => l_object_version_number
2878 ,p_effective_start_date => l_effective_start_date
2879 ,p_effective_end_date => l_effective_end_date
2880 );
2881 hr_utility.set_location('After Calling User Hook Delete_Pension_Type_a',20);
2882 Exception
2883 When hr_api.cannot_find_prog_unit Then
2884 hr_utility.set_location('Exception in User Hook Delete_Pension_Type_a',25);
2885 hr_api.cannot_find_prog_unit_error
2886 (p_module_name => 'Delete_Pension_Type'
2887 ,p_hook_type => 'AP'
2888 );
2889 End;
2890
2891 -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
2892 -- When in validation only mode raise the Validate_Enabled exception
2893 -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
2894 If p_validate Then
2895 raise hr_api.validate_enabled;
2896 End If;
2897 -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
2898 -- Set all output arguments
2899 -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
2900 p_effective_start_date := l_effective_start_date;
2901 p_effective_end_date := l_effective_end_date;
2902 p_object_version_number := l_object_version_number;
2903 --
2904 hr_utility.set_location(' Leaving:'||l_proc, 70);
2905 -- hr_utility.trace_off;
2906 Exception
2907 When hr_api.validate_enabled Then
2908 --
2909 -- As the Validate_Enabled exception has been raised
2910 -- we must rollback to the savepoint
2911 --
2912 Rollback To Delete_Pension_Type;
2913 --
2914 -- Only set output warning arguments
2915 -- (Any key or derived arguments must be set to null
2916 -- when validation only mode is being used.)
2917 --
2918 p_effective_start_date := Null;
2919 p_effective_end_date := Null;
2920 hr_utility.set_location(' Leaving:'||l_proc, 80);
2921 When Others Then
2922 --
2923 -- A validation or unexpected error has occured
2924 --
2925 Rollback to Delete_Pension_Type;
2926 hr_utility.set_location(' Leaving:'||l_proc, 90);
2927 Raise;
2928
2929 End Delete_Pension_Type;
2930
2931 End PQP_Pension_Types_api;