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