[Home] [Help]
PACKAGE BODY: APPS.PQP_GB_PENSION_SCHEME_TEMPLATE
Source
1 PACKAGE BODY pqp_gb_pension_scheme_template
2 /* $Header: pqpgbped.pkb 120.6 2012/02/08 14:34:45 asnell ship $ */
3 AS
4 g_proc_name VARCHAR2 (80)
5 := 'pqp_gb_pension_scheme_template.';
6
7 TYPE r_pension_types IS RECORD (
8 pension_type_id pqp_pension_types_f.pension_type_id%TYPE
9 ,pension_type_name pqp_pension_types_f.pension_type_name%TYPE
10 ,effective_start_date pqp_pension_types_f.effective_start_date%TYPE
11 ,effective_end_date pqp_pension_types_f.effective_end_date%TYPE
12 ,pension_category pqp_pension_types_f.pension_category%TYPE
13 ,ee_contribution_percent pqp_pension_types_f.ee_contribution_percent%TYPE
14 ,er_contribution_percent pqp_pension_types_f.er_contribution_percent%TYPE
15 ,ee_contribution_fixed_rate pqp_pension_types_f.ee_contribution_fixed_rate%TYPE
16 ,er_contribution_fixed_rate pqp_pension_types_f.er_contribution_fixed_rate%TYPE
17 ,ee_contribution_bal_type_id pqp_pension_types_f.ee_contribution_bal_type_id%TYPE
18 ,er_contribution_bal_type_id pqp_pension_types_f.er_contribution_bal_type_id%TYPE);
19
20 TYPE t_pension_types IS TABLE OF r_pension_types
21 INDEX BY BINARY_INTEGER;
22
23 g_tab_pension_types_info t_pension_types;
24
25 TYPE t_number IS TABLE OF NUMBER
26 INDEX BY BINARY_INTEGER;
27
28 g_tab_formula_ids t_number;
29
30
31 /*========================================================================
32 * CREATE_USER_TEMPLATE_LOW
33 *=======================================================================*/
34 FUNCTION create_user_template_low (
35 p_pension_scheme_name IN VARCHAR2
36 ,p_pension_year_start_dt IN DATE
37 ,p_pension_category IN VARCHAR2
38 ,p_pension_provider_id IN NUMBER
39 ,p_pension_type_id IN NUMBER
40 ,p_emp_deduction_method IN VARCHAR2
41 ,p_ele_base_name IN VARCHAR2
42 ,p_effective_start_date IN DATE
43 ,p_ele_reporting_name IN VARCHAR2
44 ,p_ele_classification_id IN NUMBER
45 ,p_business_group_id IN NUMBER
46 ,p_eer_deduction_method IN VARCHAR2
47 ,p_scon_number IN VARCHAR2
48 ,p_econ_number IN VARCHAR2 -- Bug 4108320
49 ,p_additional_contribution IN VARCHAR2
50 ,p_added_years IN VARCHAR2
51 ,p_family_widower IN VARCHAR2
52 ,p_fwc_added_years IN VARCHAR2
53 ,p_scheme_reference_no IN VARCHAR2
54 ,p_employer_reference_no IN VARCHAR2
55 ,p_associated_ocp_ele_id IN NUMBER
56 ,p_ele_description IN VARCHAR2
57 ,p_pension_scheme_type IN VARCHAR2
58 ,p_pensionable_sal_bal_id IN NUMBER
59 ,p_third_party_only_flag IN VARCHAR2
60 ,p_iterative_processing IN VARCHAR2
61 ,p_arrearage_allowed IN VARCHAR2
62 ,p_partial_deduction IN VARCHAR2
63 ,p_termination_rule IN VARCHAR2
64 ,p_standard_link IN VARCHAR2
65 ,p_validate IN BOOLEAN
66 )
67 RETURN NUMBER
68 IS
69 --
70
71
72 /*---------------------------------------------------------------------------
73 The input values are explained below : V-varchar2, D-Date, N-number
74 Input-Name Type Valid Values/Explaination
75 ---------- ----
76 --------------------------------------------------------------------------
77 p_pension_scheme_name (V) - User i/p Scheme Name
78 p_pension_year_start_dt (D) - User i/p Date
79 p_pension_category (V) - LOV based i/p (OCP/AVC/SHP/FSAVC/PEP)
80 p_pension_provider_ip (N) - LOV based i/p
81 p_pension_type_id (N) - LOV based i/p
82 p_emp_deduction_method (V) - LOV based i/p (PE/FR/PEFR)
83 p_ele_base_name (V) - User i/p Base Name
84 p_effective_start_date (D) - User i/p Date
85 p_ele_reporting_name (V) - User i/p Reporting Name
86 p_ele_classification_id (N) - LOV based i/p
87 p_business_group_id (N) - User i/p Business Group
88 p_eer_deduction_method (V) - LOV based i/p (PE/FR/PEFR)
89 p_scon_number (V) - User i/p SCON
90 p_econ_number (V) - User i/p ECON
91 p_additional_contribution (V) - LOV based i/p (PE/FR/PEFR)
92 p_added_years (V) - LOV based i/p (PE/FR/PEFR)
93 p_family_widower (V) - LOV based i/p (PE/FR/PEFR)
94 p_fwc_added_years (V) - LOV based i/p (PE/FR/PEFR)
95 p_scheme_reference_no (V) - User i/p Scheme Reference Number
96 p_employer_reference_no (V) - User i/p Employer Reference Number
97 p_associated_ocp_ele_id (N) - LOV based i/p
98 p_ele_description (V) - User i/p Element Description
99 p_pension_scheme_type (V) - LOV based i/p (COSR/COMP)
100 p_pensionable_sal_bal_id (N) - LOV based i/p
101 p_third_party_only_flag (V) - Check box based i/p (Y/N) Default N
102 p_iterative_processing (V) - Check box based i/p (Y/N) Default N
103 p_arrearage_allowed (V) - Check box based i/p (Y/N) Default N
104 p_partial_deduction (V) - Check box based i/p (Y/N) Default N
105 p_termination_rule (V) - Radio button based i/p (A/F/L) Default L
106 p_standard_link (V) - Check box based i/p (Y/N) Default N
107 p_validate (B) - TRUE or FALSE
108
109 -----------------------------------------------------------------------------*/
110 --
111 l_template_id pay_shadow_element_types.template_id%TYPE;
112 l_base_element_type_id pay_template_core_objects.core_object_id%TYPE;
113 l_base_fwc_element_type_id pay_template_core_objects.core_object_id%TYPE;
114 l_base_fwc_element_type_id_fix pay_template_core_objects.core_object_id%TYPE;
115 l_source_template_id pay_element_templates.template_id%TYPE;
116 l_object_version_number pay_element_types_f.object_version_number%TYPE;
117 l_proc_name VARCHAR2 (80)
118 := g_proc_name
119 || 'create_user_template_low';
120 l_element_type_id NUMBER;
121 l_fwc_element_type_id NUMBER;
122 l_fwc_element_type_id_fixed NUMBER;
123 l_balance_type_id NUMBER;
124 l_eei_element_type_id NUMBER;
125 l_ele_obj_ver_number NUMBER;
126 l_bal_obj_ver_number NUMBER;
127 i NUMBER;
128 j NUMBER;
129 l_eei_info_id NUMBER;
130 l_ovn_eei NUMBER;
131 l_exists VARCHAR2 (1);
132
133 TYPE t_ele_name IS TABLE OF pay_element_types_f.element_name%TYPE
134 INDEX BY BINARY_INTEGER;
135
136 l_ele_name t_ele_name;
137 l_ele_new_name t_ele_name;
138 l_bal_name pay_balance_types.balance_name%TYPE;
139 l_bal_new_name pay_balance_types.balance_name%TYPE;
140 l_ele_class_name pay_element_classifications.classification_name%TYPE;
141 l_exc_ocp_rule_id VARCHAR2 (1);
142 l_exc_ers_cent_rule_id VARCHAR2 (1);
143 l_exc_ers_fxd_rule_id VARCHAR2 (1);
144 l_exc_adl_cent_rule_id VARCHAR2 (1);
145 l_exc_adl_fxd_rule_id VARCHAR2 (1);
146 l_exc_ayr_cent_rule_id VARCHAR2 (1);
147 l_exc_ayr_fxd_rule_id VARCHAR2 (1);
148
149 -- for buy back added years for family widower
150 l_exc_bb_fwc_cent_rule_id VARCHAR2 (1);
151 l_exc_bb_fwc_fxd_rule_id VARCHAR2 (1);
152
153 l_exc_fwd_cent_rule_id VARCHAR2 (1);
154 l_exc_fwd_fxd_rule_id VARCHAR2 (1);
155 l_exc_avc_rule_id VARCHAR2 (1);
156 l_exc_shp_rule_id VARCHAR2 (1);
157 l_exc_fsavc_rule_id VARCHAR2 (1);
158 l_exc_pep_rule_id VARCHAR2 (1);
159 l_exc_pre_tax_rule_id VARCHAR2 (1);
160 l_exc_ees_cram_rule_id VARCHAR2 (1);
161 l_exc_ees_cent_rule_id VARCHAR2 (1);
162 l_exc_ers_rule_id VARCHAR2 (1);
163 l_exc_adl_rule_id VARCHAR2 (1);
164 l_exc_ayr_rule_id VARCHAR2 (1);
165
166 -- for buy back added years for family widower
167 l_exc_bb_fwc_rule_id VARCHAR2 (1);
168
169 l_exc_fwd_rule_id VARCHAR2 (1);
170 l_exc_ssal_rule_id VARCHAR2 (1);
171 l_exc_vol_con_rule_id VARCHAR2 (1);
172 l_exc_fsavc_eer_rule_id VARCHAR2 (1);
173 l_cont_iv_name pay_input_values_f.NAME%TYPE;
174 l_skip_formula ff_formulas_f.formula_name%TYPE;
175 l_iv_default_value pay_input_values_f.default_value%TYPE;
176 l_base_processing_priority pay_element_types_f.processing_priority%TYPE;
177 l_iterative_flag pay_element_types_f.iterative_flag%TYPE;
178 l_iterative_priority pay_element_types_f.iterative_priority%TYPE;
179 l_iterative_formula ff_formulas_f.formula_name%TYPE;
180 l_sub_class_name pay_element_classifications.classification_name%TYPE;
181 l_pensionable_sal_bal_id NUMBER;
182 l_pensionable_sal_bal_name pay_balance_types.balance_name%TYPE;
183 l_arrearage_allowed VARCHAR2 (1)
184 := NVL (p_arrearage_allowed, 'N');
185 l_partial_deduction VARCHAR2 (1)
186 := NVL (p_partial_deduction, 'N');
187 l_standard_link VARCHAR2 (1) := NVL (
188 p_standard_link
189 ,'N'
190 );
191 l_format_base_name pay_element_templates.base_name%TYPE
192 := UPPER (TRANSLATE (TRIM (p_ele_base_name), ' ', '_'));
193 l_ees_cont_formula ff_formulas_f.formula_name%TYPE
194 := l_format_base_name
195 || '_EES_'
196 || p_pension_category
197 || '_CONTRIBUTION_FORMULA';
198 l_ees_cont_formula_id ff_formulas_f.formula_id%TYPE;
199 l_ers_cont_formula ff_formulas_f.formula_name%TYPE
200 := l_format_base_name
201 || '_ERS_PENSION_CONTRIBUTION_FORMULA';
202 l_ers_cont_formula_id ff_formulas_f.formula_id%TYPE;
203 l_adl_cont_formula ff_formulas_f.formula_name%TYPE
204 := l_format_base_name
205 || '_ADDITIONAL_CONTRIBUTION_FORMULA';
206 l_adl_cont_formula_id ff_formulas_f.formula_id%TYPE;
207 l_ayr_cont_formula ff_formulas_f.formula_name%TYPE
208 := l_format_base_name
209 || '_ADDED_YEARS_CONTRIBUTION_FORMULA';
210 l_ayr_cont_formula_id ff_formulas_f.formula_id%TYPE;
211
212 l_fwd_cont_formula ff_formulas_f.formula_name%TYPE
213 := l_format_base_name
214 || '_FAMILY_WIDOWER_CONTRIBUTION_FORMULA';
215 l_fwd_cont_formula_id ff_formulas_f.formula_id%TYPE;
216
217 l_bb_fwc_cont_formula ff_formulas_f.formula_name%TYPE
218 := l_format_base_name
219 || '_EES_BUY_BACK_FWC_CONTRIBUTION_FORMULA';
220 l_bb_fwc_cont_formula_id ff_formulas_f.formula_id%TYPE;
221
222
223 l_search_string VARCHAR2 (2000);
224 l_replace_string VARCHAR2 (2000);
225 l_associated_ocp_base_name VARCHAR2 (100);
226 -- Iterative rule variables
227
228 l_iterative_rule_id NUMBER;
229 l_ovn_itr NUMBER;
230 l_itr_effective_start_dt DATE;
231 l_itr_effective_end_dt DATE;
232 l_itr_result_name pay_iterative_rules_f.result_name%TYPE
233 := 'L_STOPPER';
234 l_itr_rule_type pay_iterative_rules_f.iterative_rule_type%TYPE
235 := 'S';
236 l_exc_itr_rule_id VARCHAR2 (1);
237 l_emp_deduction_method hr_lookups.lookup_code%TYPE
238 := p_emp_deduction_method;
239
240 --
241
242 -- Cursor to retrieve the shadow element information
243 CURSOR csr_get_shadow_ele_info (c_ele_name VARCHAR2)
244 IS
245 SELECT element_type_id, object_version_number
246 FROM pay_shadow_element_types
247 WHERE template_id = l_template_id AND element_name = c_ele_name;
248
249 -- Cursor to retrieve the shadow balance information
250 CURSOR csr_get_shadow_bal_info (c_bal_name VARCHAR2)
251 IS
252 SELECT balance_type_id, object_version_number
253 FROM pay_shadow_balance_types
254 WHERE template_id = l_template_id AND balance_name = c_bal_name;
255
256 -- Cursor to check unique base name
257 CURSOR csr_chk_uniq_base_name
258 IS
259 SELECT 'X'
260 FROM pay_element_templates
261 WHERE template_name = g_template_name
262 AND business_group_id = p_business_group_id
263 AND template_type = 'U'
264 AND UPPER (base_name) = UPPER (p_ele_base_name);
265
266
267 --
268 --======================================================================
269 --|-------------------------< get_template_id >------------------------|
270 --======================================================================
271 FUNCTION get_template_id (p_legislation_code IN VARCHAR2)
272 RETURN NUMBER
273 IS
274 --
275
276 l_template_id NUMBER;
277 l_template_name VARCHAR2 (80);
278 l_proc_name VARCHAR2 (72) := g_proc_name
279 || 'get_template_id';
280
281 --
282 CURSOR csr_get_temp_id
283 IS
284 SELECT template_id
285 FROM pay_element_templates
286 WHERE template_name = g_template_name
287 AND legislation_code = p_legislation_code
288 AND template_type = 'T'
289 AND business_group_id IS NULL;
290 --
291 BEGIN
292 --
293 hr_utility.set_location ( 'Entering: '
294 || l_proc_name, 10);
295 --
296
297 OPEN csr_get_temp_id;
298 FETCH csr_get_temp_id INTO l_template_id;
299 CLOSE csr_get_temp_id;
300 --
301 hr_utility.set_location ( 'l_template_id: '||l_template_id, 25);
302
303 hr_utility.set_location ( 'Leaving: '
304 || l_proc_name, 30);
305 --
306 RETURN l_template_id;
307 --
308 END get_template_id;
309
310
311 --
312 --=======================================================================
313 --|----------------------------< get_object_id >------------------------|
314 --=======================================================================
315 FUNCTION get_object_id (
316 p_object_type IN VARCHAR2
317 ,p_object_name IN VARCHAR2
318 )
319 RETURN NUMBER
320 IS
321 --
322 l_object_id NUMBER := NULL;
323 l_proc_name VARCHAR2 (72) := g_proc_name
324 || 'get_object_id';
325
326 --
327 CURSOR csr_get_ele_id
328 IS
329 SELECT element_type_id
330 FROM pay_element_types_f
331 WHERE element_name = p_object_name
332 AND business_group_id = p_business_group_id
333 AND p_effective_start_date BETWEEN effective_start_date
334 AND effective_end_date;
335
336 --
337 CURSOR csr_get_bal_id
338 IS
339 SELECT ptco.core_object_id
340 FROM pay_shadow_balance_types psbt
341 ,pay_template_core_objects ptco
342 WHERE psbt.template_id = l_template_id
343 AND psbt.balance_name = p_object_name
344 AND ptco.template_id = psbt.template_id
345 AND ptco.shadow_object_id = psbt.balance_type_id;
346 --
347 BEGIN
348 hr_utility.set_location ( 'Entering: '
349 || l_proc_name, 10);
350
351 --
352 IF p_object_type = 'ELE'
353 THEN
354 OPEN csr_get_ele_id;
355 FETCH csr_get_ele_id INTO l_object_id;
356
357 IF csr_get_ele_id%NOTFOUND
358 THEN
359 CLOSE csr_get_ele_id;
360 fnd_message.set_name ('PQP', 'PQP_230933_ELE_TYPE_NOTFOUND');
361 fnd_message.set_token ('ELEMENT_TYPE', p_object_name);
362 fnd_message.raise_error;
363 END IF; -- End if of csr ele id row not found check ...
364 hr_utility.set_location ( 'l_object_id (ELE) : '||l_object_id, 15);
365
366 CLOSE csr_get_ele_id;
367 ELSIF p_object_type = 'BAL'
368 THEN
369 OPEN csr_get_bal_id;
370 FETCH csr_get_bal_id INTO l_object_id;
371
372 IF csr_get_bal_id%NOTFOUND
373 THEN
374 CLOSE csr_get_bal_id;
375 fnd_message.set_name ('PQP', 'PQP_230932_BAL_TYPE_NOTFOUND');
376 fnd_message.set_token ('BALANCE_TYPE', p_object_name);
377 fnd_message.raise_error;
378 END IF; -- End if of csr bal id row not found check ...
379 hr_utility.set_location ( 'l_object_id (BAL) : '||l_object_id, 15);
380
381 CLOSE csr_get_bal_id;
382 END IF; -- End if of object type = ele check ...
383
384 --
385 hr_utility.set_location ( 'Leaving: '
386 || l_proc_name, 20);
387 --
388 RETURN l_object_id;
389 --
390 END get_object_id;
391
392
393 --
394 --=======================================================================
395 --|-------------------------< update_ipval_defval >---------------------|
396 --=======================================================================
397 PROCEDURE update_ipval_defval (
398 p_ele_name IN VARCHAR2
399 ,p_ip_name IN VARCHAR2
400 ,p_def_value IN VARCHAR2
401 )
402 IS
403 CURSOR csr_getinput (c_ele_name VARCHAR2, c_iv_name VARCHAR2)
404 IS
405 SELECT piv.input_value_id, piv.NAME, piv.element_type_id
406 FROM pay_input_values_f piv, pay_element_types_f pet
407 WHERE element_name = c_ele_name
408 AND piv.element_type_id = pet.element_type_id
409 AND piv.business_group_id = p_business_group_id
410 AND piv.NAME = c_iv_name;
411
412 CURSOR csr_updinput (c_ip_id NUMBER, c_element_type_id NUMBER)
413 IS
414 SELECT ROWID
415 FROM pay_input_values_f
416 WHERE input_value_id = c_ip_id
417 AND element_type_id = c_element_type_id
418 FOR UPDATE NOWAIT;
419
420 csr_getinput_rec csr_getinput%ROWTYPE;
421 csr_updinput_rec csr_updinput%ROWTYPE;
422 l_proc_name VARCHAR2 (72)
423 := g_proc_name
424 || 'update_ipval_defval';
425 --
426 BEGIN
427 --
428
429 --
430 hr_utility.set_location ( 'Entering '
431 || l_proc_name, 10);
432 --
433 OPEN csr_getinput (p_ele_name, p_ip_name);
434
435 LOOP
436 FETCH csr_getinput INTO csr_getinput_rec;
437 EXIT WHEN csr_getinput%NOTFOUND;
438 --
439 hr_utility.set_location (l_proc_name, 20);
440 --
441
442 OPEN csr_updinput (
443 csr_getinput_rec.input_value_id
444 ,csr_getinput_rec.element_type_id
445 );
446
447 LOOP
448 FETCH csr_updinput INTO csr_updinput_rec;
449 EXIT WHEN csr_updinput%NOTFOUND;
450 --
451 hr_utility.set_location (l_proc_name, 30);
452
453 --
454
455 UPDATE pay_input_values_f
456 SET default_value = p_def_value
457 WHERE ROWID = csr_updinput_rec.ROWID;
458 END LOOP;
459
460 CLOSE csr_updinput;
461 END LOOP;
462
463 CLOSE csr_getinput;
464 --
465 hr_utility.set_location ( 'Leaving '
466 || l_proc_name, 40);
467 --
468
469 END update_ipval_defval;
470
471 --
472
473 -- ---------------------------------------------------------------------
474 -- |-----------------------< Compile_Formula >--------------------------|
475 -- ---------------------------------------------------------------------
476 PROCEDURE compile_formula (p_element_type_id IN NUMBER)
477 IS
478
479 -- --------------------------------------------------------
480 -- Cursor to get the formula details necessary to compile
481 -- --------------------------------------------------------
482 CURSOR csr_get_ff_id (c_element_type_id NUMBER)
483 IS
484 SELECT fra.formula_id, fra.formula_name, fty.formula_type_id
485 ,fty.formula_type_name
486 FROM ff_formulas_f fra
487 ,ff_formula_types fty
488 ,pay_status_processing_rules_f spr
489 WHERE fty.formula_type_id = fra.formula_type_id
490 AND fra.formula_id = spr.formula_id
491 AND spr.assignment_status_type_id IS NULL
492 AND spr.element_type_id = c_element_type_id
493 AND p_effective_start_date BETWEEN fra.effective_start_date
494 AND fra.effective_end_date
495 AND p_effective_start_date BETWEEN spr.effective_start_date
496 AND spr.effective_end_date;
497
498 l_request_id NUMBER;
499 l_er_request_id NUMBER;
500 l_proc_name VARCHAR2 (80) := g_proc_name
501 || 'compile_formula';
502 BEGIN
503 hr_utility.set_location ( 'Entering: '
504 || l_proc_name, 10);
505
506
507 -- ------------------------------------------------------------
508 -- Query formula info (ie. the formula attached to this
509 -- element's Standard status proc rule.
510 -- ------------------------------------------------------------
511 FOR fra_rec IN csr_get_ff_id (
512 c_element_type_id => p_element_type_id
513 )
514 LOOP
515 hr_utility.set_location (
516 '..FF Name :'
517 || fra_rec.formula_name
518 ,15
519 );
520 hr_utility.set_location (
521 '..FF Type Name :'
522 || fra_rec.formula_type_name
523 ,20
524 );
525
526
527 -- ----------------------------------------------
528 -- Submit the request to compile the formula
529 -- ----------------------------------------------
530
531 -- Check whether the formula id is in the collection
532 -- if so do not submit a request as the compiled info
533 -- should exist
534 IF NOT g_tab_formula_ids.EXISTS (fra_rec.formula_id)
535 THEN
536 l_request_id := ff_formula_webui_pkg.submit_singlecompile
537 (p_formula_type => fra_rec.formula_type_name --Oracle Payroll
538 ,p_formula_name => fra_rec.formula_name); --formula name
539 hr_utility.set_location (
540 '..Request Id :'
541 || l_request_id
542 ,25
543 );
544 -- store it in the collection
545 g_tab_formula_ids (fra_rec.formula_id) := fra_rec.formula_id;
546 END IF; -- End if of formula id exists in collection check ...
547 END LOOP;
548
549 hr_utility.set_location ( 'Leaving: '
550 || l_proc_name, 30);
551 END compile_formula;
552
553
554 -- ----------------------------------------------------------------------------
555 -- |------------------------< chk_scheme_prefix >-----------------------------|
556 -- ----------------------------------------------------------------------------
557 PROCEDURE chk_scheme_prefix (p_scheme_prefix IN VARCHAR2)
558 IS
559 l_element_name VARCHAR2 (100) := p_scheme_prefix;
560 l_output VARCHAR2 (100);
561 l_rgeflg VARCHAR2 (100);
562 BEGIN
563 hr_utility.set_location ( 'Entering : chk_scheme_prefix ', 10);
564
565 hr_chkfmt.checkformat (
566 VALUE => l_element_name
567 ,format => 'PAY_NAME'
568 ,output => l_output
569 ,MINIMUM => NULL
570 ,maximum => NULL
571 ,nullok => 'N'
572 ,rgeflg => l_rgeflg
573 ,curcode => NULL
574 );
575
576 hr_utility.set_location ( 'Exiting : chk_scheme_prefix ', 20);
577
578 EXCEPTION
579 WHEN OTHERS
580 THEN
581 fnd_message.set_name ('PQP', 'PQP_230923_SCHEME_PREFIX_ERR');
582 fnd_message.raise_error;
583 END chk_scheme_prefix;
584
585 --
586
587 --
588 --==============================================================================
589 --|-----------------------------< get_balance_info >---------------------------|
590 --==============================================================================
591 FUNCTION get_balance_info (p_balance_type_id IN NUMBER)
592 RETURN VARCHAR2
593 IS
594 --
595 -- Cursor to retrieve the balance information
596 CURSOR csr_get_bal_info
597 IS
598 SELECT balance_name
599 FROM pay_balance_types
600 WHERE balance_type_id = p_balance_type_id
601 AND ( ( business_group_id IS NOT NULL
602 AND business_group_id = p_business_group_id
603 )
604 OR ( legislation_code IS NOT NULL
605 AND legislation_code = g_template_leg_code
606 )
607 OR (business_group_id IS NULL AND legislation_code IS NULL)
608 );
609
610 l_proc_name VARCHAR2 (80) := g_proc_name
611 || 'get_balance_info';
612 l_balance_name pay_balance_types.balance_name%TYPE;
613 --
614
615 BEGIN
616 --
617 hr_utility.set_location ( 'Entering: '
618 || l_proc_name, 10);
619 --
620
621 OPEN csr_get_bal_info;
622 FETCH csr_get_bal_info INTO l_balance_name;
623
624 IF csr_get_bal_info%NOTFOUND
625 THEN
626 CLOSE csr_get_bal_info;
627 fnd_message.set_name ('PQP', 'PQP_230549_BAL_TYPE_NOT_FOUND');
628 fnd_message.raise_error;
629 END IF; -- End if of row found check ...
630
631 hr_utility.set_location ( 'l_balance_name : '|| l_balance_name, 20);
632
633 CLOSE csr_get_bal_info;
634 --
635 hr_utility.set_location ( 'Leaving: '
636 || l_proc_name, 20);
637 RETURN l_balance_name;
638 --
639 END get_balance_info;
640
641 --
642
643 --
644 --==============================================================================
645 --|-----------------------< get_ele_classification_info >----------------------|
646 --==============================================================================
647 PROCEDURE get_ele_classification_info (
648 p_classification_id IN NUMBER
649 ,p_classification_name OUT NOCOPY VARCHAR2
650 ,p_default_priority OUT NOCOPY NUMBER
651 )
652 IS
653 --
654 CURSOR csr_get_ele_class_info
655 IS
656 SELECT classification_name, default_priority
657 FROM pay_element_classifications
658 WHERE classification_id = p_classification_id;
659
660 l_proc_name VARCHAR2 (80)
661 := g_proc_name
662 || 'get_ele_classification_info';
663 l_ele_class_info csr_get_ele_class_info%ROWTYPE;
664 --
665
666 BEGIN
667 --
668 hr_utility.set_location ( 'Entering: '
669 || l_proc_name, 10);
670 --
671
672 OPEN csr_get_ele_class_info;
673 FETCH csr_get_ele_class_info INTO l_ele_class_info;
674
675 IF csr_get_ele_class_info%FOUND
676 THEN
677 p_classification_name :=
678 l_ele_class_info.classification_name;
679 p_default_priority := l_ele_class_info.default_priority;
680 END IF; -- End if of row found check ...
681
682 hr_utility.set_location ( 'l_ele_class_info : '|| l_ele_class_info.classification_name, 20);
683
684 CLOSE csr_get_ele_class_info;
685 --
686 hr_utility.set_location ( 'Leaving: '
687 || l_proc_name, 20);
688 --
689
690 EXCEPTION
691 WHEN OTHERS
692 THEN
693 hr_utility.set_location (
694 'Others Exception: '
695 || l_proc_name
696 ,30
697 );
698 hr_utility.set_location ( 'Leaving: '
699 || l_proc_name, 40);
700 p_classification_name := NULL;
701 p_default_priority := NULL;
702 RAISE;
703 END get_ele_classification_info;
704
705
706 --
707 --
708 --==============================================================================
709 --|--------------------------< get_iterative_priority >------------------------|
710 --==============================================================================
711 FUNCTION get_iterative_priority (p_element_type_id IN NUMBER)
712 RETURN NUMBER
713 IS
714 --
715 CURSOR csr_get_prs_priority
716 IS
717 SELECT relative_processing_priority
718 FROM pay_shadow_element_types
719 WHERE element_type_id = p_element_type_id;
720
721 l_proc_name VARCHAR2 (80)
722 := g_proc_name
723 || 'get_iterative_priority';
724 l_processing_priority NUMBER;
725 l_iterative_priority NUMBER;
726 --
727 BEGIN
728 --
729 hr_utility.set_location ( 'Entering: '
730 || l_proc_name, 10);
731 --
732 OPEN csr_get_prs_priority;
733 FETCH csr_get_prs_priority INTO l_processing_priority;
734 CLOSE csr_get_prs_priority;
735 l_iterative_priority := 400
736 - l_processing_priority;
737 --
738 hr_utility.set_location ( 'Leaving: '
739 || l_proc_name, 20);
740 --
741
742 RETURN l_iterative_priority;
743 --
744 END get_iterative_priority;
745
746 --
747 --
748 --==============================================================================
749 --|---------------------------< get_pension_type_info >------------------------|
750 --==============================================================================
751 FUNCTION get_pension_type_info
752 RETURN t_pension_types
753 IS
754 --
755 CURSOR csr_get_pension_type_info
756 IS
757 SELECT pension_type_id, pension_type_name, effective_start_date
758 ,effective_end_date, pension_category
759 ,ee_contribution_percent, er_contribution_percent
760 ,ee_contribution_fixed_rate, er_contribution_fixed_rate
761 ,ee_contribution_bal_type_id, er_contribution_bal_type_id
762 FROM pqp_pension_types_f
763 WHERE pension_type_id = p_pension_type_id
764 AND p_effective_start_date BETWEEN effective_start_date
765 AND effective_end_date
766 AND ( ( business_group_id IS NOT NULL
767 AND business_group_id = p_business_group_id
768 )
769 OR ( legislation_code IS NOT NULL
770 AND legislation_code = g_template_leg_code
771 )
772 OR (business_group_id IS NULL AND legislation_code IS NULL)
773 );
774
775 l_proc_name VARCHAR2 (80)
776 := g_proc_name
777 || 'get_pension_type';
778 l_pension_type_info csr_get_pension_type_info%ROWTYPE;
779 l_tab_pension_types t_pension_types;
780 --
781 BEGIN
782 --
783 hr_utility.set_location ( 'Entering: '
784 || l_proc_name, 10);
785 --
786
787 OPEN csr_get_pension_type_info;
788 FETCH csr_get_pension_type_info INTO l_pension_type_info;
789
790 IF csr_get_pension_type_info%NOTFOUND
791 THEN
792 CLOSE csr_get_pension_type_info;
793 fnd_message.set_name ('PQP', 'PQP_230934_PEN_TYPE_ID_INVALID');
794 fnd_message.raise_error;
795 END IF; -- End if of pension type row found check ...
796
797 CLOSE csr_get_pension_type_info;
798
799 hr_utility.set_location ( 'l_pension_type_info : '|| l_pension_type_info.pension_type_name, 20);
800
801
802 l_tab_pension_types (p_pension_type_id).pension_type_id :=
803 l_pension_type_info.pension_type_id;
804 l_tab_pension_types (p_pension_type_id).pension_type_name :=
805 l_pension_type_info.pension_type_name;
806 l_tab_pension_types (p_pension_type_id).effective_start_date :=
807 l_pension_type_info.effective_start_date;
808 l_tab_pension_types (p_pension_type_id).effective_end_date :=
809 l_pension_type_info.effective_end_date;
810 l_tab_pension_types (p_pension_type_id).pension_category :=
811 l_pension_type_info.pension_category;
812 l_tab_pension_types (p_pension_type_id).ee_contribution_percent :=
813 l_pension_type_info.ee_contribution_percent;
814 l_tab_pension_types (p_pension_type_id).er_contribution_percent :=
815 l_pension_type_info.er_contribution_percent;
816 l_tab_pension_types (p_pension_type_id).ee_contribution_fixed_rate :=
817 l_pension_type_info.ee_contribution_fixed_rate;
818 l_tab_pension_types (p_pension_type_id).er_contribution_fixed_rate :=
819 l_pension_type_info.er_contribution_fixed_rate;
820 l_tab_pension_types (p_pension_type_id).ee_contribution_bal_type_id :=
821 l_pension_type_info.ee_contribution_bal_type_id;
822 l_tab_pension_types (p_pension_type_id).er_contribution_bal_type_id :=
823 l_pension_type_info.er_contribution_bal_type_id;
824 --
825 hr_utility.set_location ( 'Leaving: '
826 || l_proc_name, 20);
827 --
828
829 RETURN l_tab_pension_types;
830 END get_pension_type_info;
831
832
833 --
834 --
835 --==============================================================================
836 --|---------------------------< insert_validate >------------------------------|
837 --==============================================================================
838 PROCEDURE insert_validate
839 IS
840 --
841 -- Cursor to check whether provider exists
842
843 CURSOR csr_chk_provider_exists
844 IS
845 SELECT 'X'
846 FROM hr_all_organization_units hou
847 ,hr_organization_information hoi
848 WHERE hou.organization_id = p_pension_provider_id
849 AND ( hou.business_group_id = p_business_group_id
850 OR hou.business_group_id IS NULL
851 )
852 AND p_effective_start_date BETWEEN date_from
853 AND NVL (
854 date_to
855 ,p_effective_start_date
856 )
857 AND hoi.organization_id = hou.organization_id
858 AND hoi.org_information_context = 'CLASS'
859 AND hoi.org_information1 = 'FR_PENSION'
860 AND hoi.org_information2 = 'Y';
861
862 -- Cursor to check whether provider supports this pension type
863
864 CURSOR csr_chk_pens_type_in_prov
865 IS
866 SELECT 'X'
867 FROM hr_organization_information
868 WHERE organization_id = p_pension_provider_id
869 AND org_information_context = 'PQP_GB_PENSION_TYPES_INFO'
870 AND org_information1 = p_pension_type_id;
871
872 -- Cursor to check the associated OCP element validity
873
874 CURSOR csr_chk_ocp_ele_info (c_element_type_id NUMBER)
875 IS
876 SELECT 'X'
877 FROM pay_element_type_extra_info eeit, pay_element_types_f pet
878 WHERE pet.element_type_id = c_element_type_id
879 AND ( ( pet.business_group_id IS NOT NULL
880 AND pet.business_group_id = p_business_group_id
881 )
882 OR ( pet.legislation_code IS NOT NULL
883 AND pet.legislation_code = g_template_leg_code
884 )
885 OR ( pet.business_group_id IS NULL
886 AND pet.legislation_code IS NULL
887 )
888 )
889 AND p_effective_start_date BETWEEN pet.effective_start_date
890 AND pet.effective_end_date
891 AND eeit.element_type_id = pet.element_type_id
892 AND eeit.information_type = 'PQP_GB_PENSION_SCHEME_INFO'
893 AND eeit.eei_information4 = 'OCP'
894 AND eeit.eei_information12 IS NULL;
895
896 -- Cursor to get ECON number
897 CURSOR csr_get_econ
898 IS
899 SELECT org_information7
900 FROM hr_organization_information
901 WHERE organization_id = p_business_group_id
902 AND org_information_context = 'Tax Details References';
903
904 -- BUG 4108320
905 -- Cursor to get translated value of SCON / ECON
906 CURSOR csr_translate_escon (c_number VARCHAR2)
907 IS
908 SELECT TRANSLATE (
909 UPPER (c_number)
910 ,'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789'
911 ,'AAAAAAAAAAAAAAAAAAAAAAAAAA9999999999'
912 )
913 FROM DUAL;
914
915
916 l_proc_name VARCHAR2 (80) := g_proc_name
917 || 'insert_validate';
918 l_exists VARCHAR2 (1);
919 l_econ_number hr_organization_information.org_information7%TYPE;
920 l_scon_format VARCHAR2 (9) := 'A9999999A';
921 l_econ_format VARCHAR2 (9) := 'E9999999A';
922 l_scon_number pay_element_type_extra_info.eei_information1%TYPE;
923 --
924 BEGIN
925 --
926 hr_utility.set_location ( 'Entering: '
927 || l_proc_name, 10);
928 --
929 -- Check mandatory arguments first
930 -- Pension Scheme Name
931 hr_utility.set_location('Pension Scheme Name',15);
932
933 hr_api.mandatory_arg_error (
934 p_api_name => l_proc_name
935 ,p_argument => 'Pension Scheme Name'
936 ,p_argument_value => p_pension_scheme_name
937 );
938
939 -- Pension Year Start Date
940 hr_utility.set_location('Pension Year Start Date',15);
941 hr_api.mandatory_arg_error (
942 p_api_name => l_proc_name
943 ,p_argument => 'Pension Year Start Date'
944 ,p_argument_value => p_pension_year_start_dt
945 );
946
947 -- Effective Start Date
948 hr_utility.set_location('Effective Start Date',15);
949 hr_api.mandatory_arg_error (
950 p_api_name => l_proc_name
951 ,p_argument => 'Effective Start Date'
952 ,p_argument_value => p_effective_start_date
953 );
954
955 hr_utility.set_location('Pension Category',15);
956 -- Pension Category
957 hr_api.mandatory_arg_error (
958 p_api_name => l_proc_name
959 ,p_argument => 'Pension Category'
960 ,p_argument_value => p_pension_category
961 );
962
963 hr_utility.set_location('Pension Provider ID',15);
964 -- Pension Provider
965 hr_api.mandatory_arg_error (
966 p_api_name => l_proc_name
967 ,p_argument => 'Pension Provider ID'
968 ,p_argument_value => p_pension_provider_id
969 );
970
971 hr_utility.set_location('Pension Type ID',15);
972 -- Pension Type
973 hr_api.mandatory_arg_error (
974 p_api_name => l_proc_name
975 ,p_argument => 'Pension Type ID'
976 ,p_argument_value => p_pension_type_id
977 );
978
979 hr_utility.set_location('Employee Deduction Method',15);
980 -- Employee Deduction Method
981 hr_api.mandatory_arg_error (
982 p_api_name => l_proc_name
983 ,p_argument => 'Employee Deduction Method'
984 ,p_argument_value => l_emp_deduction_method
985 );
986
987 hr_utility.set_location('Scheme Prefix',15);
988 -- Element Base Name
989 hr_api.mandatory_arg_error (
990 p_api_name => l_proc_name
991 ,p_argument => 'Scheme Prefix'
992 ,p_argument_value => p_ele_base_name
993 );
994
995 hr_utility.set_location('Reporting Name',15);
996 -- Reporting Name
997 hr_api.mandatory_arg_error (
998 p_api_name => l_proc_name
999 ,p_argument => 'Reporting Name'
1000 ,p_argument_value => p_ele_reporting_name
1001 );
1002
1003 hr_utility.set_location('Classification',15);
1004 -- Element Classification
1005 hr_api.mandatory_arg_error (
1006 p_api_name => l_proc_name
1007 ,p_argument => 'Classification'
1008 ,p_argument_value => p_ele_classification_id
1009 );
1010
1011 -- For AVC p_associated_ocp_ele_id is mandatory
1012 IF p_pension_category = 'AVC'
1013 THEN
1014 hr_api.mandatory_arg_error (
1015 p_api_name => l_proc_name
1016 ,p_argument => 'Associated OCP Scheme'
1017 ,p_argument_value => p_associated_ocp_ele_id
1018 );
1019 END IF; -- End if of pension category is AVC check ...
1020
1021 --
1022 hr_utility.set_location (l_proc_name, 20);
1023
1024 --
1025 -- Check pension category lookup code
1026 --
1027
1028 IF hr_api.not_exists_in_hr_lookups (
1029 p_effective_date => p_effective_start_date
1030 ,p_lookup_type => 'PQP_PENSION_CATEGORY'
1031 ,p_lookup_code => p_pension_category
1032 )
1033 THEN
1034 -- Invalid Pension Category
1035 fnd_message.set_name ('PAY', 'HR_52966_INVALID_LOOKUP');
1036 fnd_message.set_token ('COLUMN', 'Pension Category');
1037 fnd_message.set_token ('LOOKUP_TYPE', 'PQP_PENSION_CATEGORY');
1038
1039 hr_utility.set_location (l_proc_name, 25);
1040
1041 fnd_message.raise_error;
1042 END IF; -- End if of not exists in lookup check ...
1043
1044 -- Check Pension Provider exists for this BG
1045 --
1046 hr_utility.set_location (l_proc_name, 30);
1047 --
1048
1049 OPEN csr_chk_provider_exists;
1050 FETCH csr_chk_provider_exists INTO l_exists;
1051
1052 IF csr_chk_provider_exists%NOTFOUND
1053 THEN
1054 CLOSE csr_chk_provider_exists;
1055 fnd_message.set_name ('PQP', 'PQP_230936_PEN_PROV_ID_INVALID');
1056 fnd_message.raise_error;
1057 END IF; -- End if of provider exists row not found check ...
1058
1059 CLOSE csr_chk_provider_exists;
1060 -- Get Pension Type Info
1061 --
1062 hr_utility.set_location (l_proc_name, 30);
1063
1064 --
1065
1066 IF NOT g_tab_pension_types_info.EXISTS (p_pension_type_id)
1067 OR -- Check the effectiveness as this is DT table
1068 ( g_tab_pension_types_info.EXISTS (p_pension_type_id)
1069 AND NOT (p_effective_start_date
1070 BETWEEN g_tab_pension_types_info (
1071 p_pension_type_id
1072 ).effective_start_date
1073 AND g_tab_pension_types_info (
1074 p_pension_type_id
1075 ).effective_end_date
1076 )
1077 )
1078 THEN
1079 g_tab_pension_types_info := get_pension_type_info;
1080 END IF; -- End if of pension type info exists
1081
1082 -- Validate whether the pension type supports the pension category
1083 IF g_tab_pension_types_info (p_pension_type_id).pension_category <>
1084 p_pension_category
1085 THEN
1086 fnd_message.set_name ('PQP', 'PQP_230938_PEN_TYP_CAT_NOTSUP');
1087 fnd_message.set_token (
1088 'PENSION_CATEGORY'
1089 ,hr_general.decode_lookup (
1090 'PQP_PENSION_CATEGORY'
1091 ,p_pension_category
1092 )
1093 );
1094 fnd_message.raise_error;
1095 END IF; -- End if of pension category in pension type matches with parameter check ...
1096
1097 -- Check pension type is supported by this pension provider
1098 --
1099 hr_utility.set_location (l_proc_name, 40);
1100 --
1101
1102 OPEN csr_chk_pens_type_in_prov;
1103 FETCH csr_chk_pens_type_in_prov INTO l_exists;
1104
1105 IF csr_chk_pens_type_in_prov%NOTFOUND
1106 THEN
1107 CLOSE csr_chk_pens_type_in_prov;
1108 fnd_message.set_name ('PQP', 'PQP_230937_PEN_TYP_NOTIN_PROV');
1109 fnd_message.raise_error;
1110 END IF; -- End if of pension type in provider not found check ...
1111
1112 CLOSE csr_chk_pens_type_in_prov;
1113 -- Check employee deduction method in lookup
1114 --
1115 hr_utility.set_location (l_proc_name, 50);
1116
1117 --
1118 IF hr_api.not_exists_in_hr_lookups (
1119 p_effective_date => p_effective_start_date
1120 ,p_lookup_type => 'PQP_PENSION_DEDUCTION_METHOD'
1121 ,p_lookup_code => l_emp_deduction_method
1122 )
1123 THEN
1124 -- Invalid Deduction Method
1125 fnd_message.set_name ('PAY', 'HR_52966_INVALID_LOOKUP');
1126 fnd_message.set_token ('COLUMN', 'Employee Deduction Method');
1127 fnd_message.set_token (
1128 'LOOKUP_TYPE'
1129 ,'PQP_PENSION_DEDUCTION_METHOD'
1130 );
1131 fnd_message.raise_error;
1132 END IF; -- End if of not exists in lookup check ...
1133
1134 -- Check employer deduction method in lookup
1135 --
1136 hr_utility.set_location (l_proc_name, 60);
1137
1138 --
1139 IF p_eer_deduction_method IS NOT NULL
1140 THEN
1141 IF hr_api.not_exists_in_hr_lookups (
1142 p_effective_date => p_effective_start_date
1143 ,p_lookup_type => 'PQP_PENSION_DEDUCTION_METHOD'
1144 ,p_lookup_code => p_eer_deduction_method
1145 )
1146 THEN
1147 -- Invalid Deduction Method
1148 fnd_message.set_name ('PAY', 'HR_52966_INVALID_LOOKUP');
1149 fnd_message.set_token ('COLUMN', 'Employer Deduction Method');
1150 fnd_message.set_token (
1151 'LOOKUP_TYPE'
1152 ,'PQP_PENSION_DEDUCTION_METHOD'
1153 );
1154 fnd_message.raise_error;
1155 END IF; -- End if of not exists in lookup check ...
1156 END IF; -- End if of employer deduction method specified check ...
1157
1158 -- NOT required now
1159 -- Check scon if pension category is OCP
1160 --
1161 -- hr_utility.set_location (l_proc_name, 70);
1162 --
1163 -- --
1164 --
1165 -- IF p_pension_category = 'OCP'
1166 -- THEN
1167 -- -- SCON
1168 -- hr_api.mandatory_arg_error (
1169 -- p_api_name => l_proc_name
1170 -- ,p_argument => 'SCON'
1171 -- ,p_argument_value => p_scon_number
1172 -- );
1173 -- END IF; -- End if of pension category is OCP check ...
1174
1175 -- Check deduction method codes for Additional contributions
1176 --
1177 hr_utility.set_location (l_proc_name, 80);
1178
1179 --
1180
1181 IF p_additional_contribution IS NOT NULL
1182 THEN
1183 IF hr_api.not_exists_in_hr_lookups (
1184 p_effective_date => p_effective_start_date
1185 ,p_lookup_type => 'PQP_PENSION_DEDUCTION_METHOD'
1186 ,p_lookup_code => p_additional_contribution
1187 )
1188 THEN
1189 -- Invalid Deduction Method
1190 fnd_message.set_name ('PAY', 'HR_52966_INVALID_LOOKUP');
1191 fnd_message.set_token (
1192 'COLUMN'
1193 ,'Additional Contribution Deduction Method'
1194 );
1195 fnd_message.set_token (
1196 'LOOKUP_TYPE'
1197 ,'PQP_PENSION_DEDUCTION_METHOD'
1198 );
1199 fnd_message.raise_error;
1200 END IF; -- End if of not exists in lookup check ...
1201 END IF; -- End if of additional contribution deduction method specified check ...
1202
1203 -- Check deduction method codes for Addded Years contributions
1204 --
1205 hr_utility.set_location (l_proc_name, 90);
1206
1207 --
1208
1209 IF p_added_years IS NOT NULL
1210 THEN
1211 IF hr_api.not_exists_in_hr_lookups (
1212 p_effective_date => p_effective_start_date
1213 ,p_lookup_type => 'PQP_PENSION_DEDUCTION_METHOD'
1214 ,p_lookup_code => p_added_years
1215 )
1216 THEN
1217 -- Invalid Deduction Method
1218 fnd_message.set_name ('PAY', 'HR_52966_INVALID_LOOKUP');
1219 fnd_message.set_token (
1220 'COLUMN'
1221 ,'Added Years Deduction Method'
1222 );
1223 fnd_message.set_token (
1224 'LOOKUP_TYPE'
1225 ,'PQP_PENSION_DEDUCTION_METHOD'
1226 );
1227 fnd_message.raise_error;
1228 END IF; -- End if of not exists in lookup check ...
1229 END IF; -- End if of added years deduction method specified check ...
1230
1231 -- Check deduction method codes for Family Widower
1232 --
1233 hr_utility.set_location (l_proc_name, 100);
1234
1235 --
1236
1237 IF p_family_widower IS NOT NULL
1238 THEN
1239 IF hr_api.not_exists_in_hr_lookups (
1240 p_effective_date => p_effective_start_date
1241 ,p_lookup_type => 'PQP_PENSION_DEDUCTION_METHOD'
1242 ,p_lookup_code => p_family_widower
1243 )
1244 THEN
1245 -- Invalid Deduction Method
1246 fnd_message.set_name ('PAY', 'HR_52966_INVALID_LOOKUP');
1247 fnd_message.set_token (
1248 'COLUMN'
1249 ,'Family Widower Deduction Method'
1250 );
1251 fnd_message.set_token (
1252 'LOOKUP_TYPE'
1253 ,'PQP_PENSION_DEDUCTION_METHOD'
1254 );
1255 fnd_message.raise_error;
1256 END IF; -- End if of not exists in lookup check ...
1257 END IF; -- End if of family widower contribution deduction method specified check ...
1258
1259
1260 -- Check deduction method codes for Family Widower Addded Years contributions
1261 --
1262 hr_utility.set_location (l_proc_name, 90);
1263
1264 --
1265
1266 IF p_fwc_added_years IS NOT NULL
1267 THEN
1268 IF hr_api.not_exists_in_hr_lookups (
1269 p_effective_date => p_effective_start_date
1270 ,p_lookup_type => 'PQP_PENSION_DEDUCTION_METHOD'
1271 ,p_lookup_code => p_fwc_added_years
1272 )
1273 THEN
1274 -- Invalid Deduction Method
1275 fnd_message.set_name ('PAY', 'HR_52966_INVALID_LOOKUP');
1276 fnd_message.set_token (
1277 'COLUMN'
1278 ,'Family Widower Added Years Deduction Method'
1279 );
1280 fnd_message.set_token (
1281 'LOOKUP_TYPE'
1282 ,'PQP_PENSION_DEDUCTION_METHOD'
1283 );
1284 fnd_message.raise_error;
1285 END IF; -- End if of not exists in lookup check ...
1286 END IF; -- End if of added years deduction method specified check ...
1287
1288
1289
1290 -- Check pension scheme type
1291 --
1292 hr_utility.set_location (l_proc_name, 110);
1293
1294 --
1295
1296 IF p_pension_scheme_type IS NOT NULL
1297 THEN
1298 IF hr_api.not_exists_in_hr_lookups (
1299 p_effective_date => p_effective_start_date
1300 ,p_lookup_type => 'PQP_PENSION_SCHEME_TYPE'
1301 ,p_lookup_code => p_pension_scheme_type
1302 )
1303 THEN
1304 -- Invalid Pension Scheme Type
1305 fnd_message.set_name ('PAY', 'HR_52966_INVALID_LOOKUP');
1306 fnd_message.set_token ('COLUMN', 'Pension Scheme Type');
1307 fnd_message.set_token (
1308 'LOOKUP_TYPE'
1309 ,'PQP_PENSION_SCHEME_TYPE'
1310 );
1311 fnd_message.raise_error;
1312 END IF; -- End if of not exists in lookup check ...
1313
1314 -- Check whether ECON and SCON values are entered
1315 -- Ideally for a scheme of this type should have these
1316 -- values
1317 -- BUG 4108320
1318 -- Remove mandatory validation for ECON and SCON
1319
1320 -- Get ECON number first
1321 l_econ_number := NULL;
1322 -- OPEN csr_get_econ;
1323 -- FETCH csr_get_econ INTO l_econ_number;
1324 -- CLOSE csr_get_econ;
1325
1326 -- IF p_scon_number IS NULL
1327 -- OR l_econ_number IS NULL
1328 -- THEN
1329 -- -- Raise an error
1330 -- fnd_message.set_name ('PQP', 'PQP_230983_PEN_SCON_ECON_NULL');
1331 -- hr_multi_message.add
1332 -- (p_associated_column4
1333 -- => 'PQP_GB_PENSION_SCHEMES_V.SCON'
1334 -- );
1335 -- END IF; -- End if of scon or econ is null check ...
1336 END IF; -- End if of pension scheme type specified check ...
1337
1338 --
1339 hr_utility.set_location (l_proc_name, 111);
1340
1341 --
1342
1343 -- Added validation for SCON format
1344 IF p_scon_number IS NOT NULL
1345 THEN
1346 --
1347 -- Get the translated value
1348 OPEN csr_translate_escon(p_scon_number);
1349 FETCH csr_translate_escon INTO l_scon_number;
1350 CLOSE csr_translate_escon;
1351
1352 IF l_scon_number <> l_scon_format
1353 THEN
1354 -- Raise an error
1355 fnd_message.set_name ('PQP', 'PQP_230984_SCON_INVALID_FORMAT');
1356 fnd_message.raise_error;
1357 END IF; -- End if of scon number in invalid check ...
1358 END IF; -- End if of scon number entered check ...
1359
1360 --
1361 hr_utility.set_location (l_proc_name, 112);
1362
1363 -- Added validation for ECON format
1364 IF p_econ_number IS NOT NULL
1365 THEN
1366 --
1367 -- Get the translated value
1368 l_econ_number := SUBSTR(p_econ_number,2);
1369 OPEN csr_translate_escon(l_econ_number);
1370 FETCH csr_translate_escon INTO l_econ_number;
1371 CLOSE csr_translate_escon;
1372
1373 l_econ_number := SUBSTR(p_econ_number,1,1)||l_econ_number;
1374
1375 IF l_econ_number <> l_econ_format
1376 THEN
1377 -- Raise an error
1378 fnd_message.set_name ('PQP', 'PQP_230172_ECON_INVALID_FORMAT');
1379 fnd_message.raise_error;
1380 END IF; -- End if of econ number in invalid check ...
1381 END IF; -- End if of econ number entered check ...
1382
1383 --
1384 hr_utility.set_location (l_proc_name, 115);
1385
1386 --
1387
1388 -- Check associated OCP element id validity
1389 IF p_associated_ocp_ele_id IS NOT NULL
1390 THEN
1391 OPEN csr_chk_ocp_ele_info (p_associated_ocp_ele_id);
1392 FETCH csr_chk_ocp_ele_info INTO l_exists;
1393
1394 IF csr_chk_ocp_ele_info%NOTFOUND
1395 THEN
1396 -- Raise error
1397 CLOSE csr_chk_ocp_ele_info;
1398 fnd_message.set_name ('PQP', 'PQP_230944_PEN_OCP_SCH_INVALID');
1399 fnd_message.raise_error;
1400 END IF; -- End if of ocp ele not found check ...
1401
1402 CLOSE csr_chk_ocp_ele_info;
1403 END IF; -- End if of associated ocp ele id not null check ...
1404
1405 -- Check post termination rule
1406 --
1407 hr_utility.set_location (l_proc_name, 120);
1408
1409 --
1410
1411 IF hr_api.not_exists_in_hr_lookups (
1412 p_effective_date => p_effective_start_date
1413 ,p_lookup_type => 'TERMINATION_RULE'
1414 ,p_lookup_code => NVL (
1415 p_termination_rule
1416 ,hr_api.g_varchar2
1417 )
1418 )
1419 THEN
1420 -- Invalid Termination Rule
1421 fnd_message.set_name ('PAY', 'HR_52966_INVALID_LOOKUP');
1422 fnd_message.set_token ('COLUMN', 'Termination Rule');
1423 fnd_message.set_token ('LOOKUP_TYPE', 'TERMINATION_RULE');
1424 fnd_message.raise_error;
1425 END IF; -- End if of not exists in lookup check ...
1426
1427 --
1428 hr_utility.set_location ( 'Leaving: '
1429 || l_proc_name, 130);
1430 --
1431 END insert_validate;
1432
1433 --
1434
1435
1436 --
1437 --==============================================================================
1438 --|---------------------------< get_shadow_formula_id >------------------------|
1439 --==============================================================================
1440 FUNCTION get_shadow_formula_id (p_formula_name IN VARCHAR2)
1441 RETURN NUMBER
1442 IS
1443 --
1444 -- Cursor to retrieve the formula information
1445 CURSOR csr_get_formula_info
1446 IS
1447 SELECT formula_id
1448 FROM pay_shadow_formulas
1449 WHERE formula_name = p_formula_name
1450 AND business_group_id = p_business_group_id
1451 AND template_type = 'U';
1452
1453 l_proc_name VARCHAR2 (80)
1454 := g_proc_name
1455 || 'get_shadow_formula_id';
1456 l_formula_id NUMBER;
1457 --
1458 BEGIN
1459 --
1460 hr_utility.set_location ( 'Entering: '
1461 || l_proc_name, 10);
1462 --
1463 OPEN csr_get_formula_info;
1464 FETCH csr_get_formula_info INTO l_formula_id;
1465
1466 IF csr_get_formula_info%NOTFOUND
1467 THEN
1468 CLOSE csr_get_formula_info;
1469 fnd_message.set_name ('PER', 'HR_289263_FORMULA_ID_INVALID');
1470 fnd_message.raise_error;
1471 END IF; -- End if of csr row not found check ...
1472
1473 CLOSE csr_get_formula_info;
1474 --
1475 hr_utility.set_location ( 'Leaving: '
1476 || l_proc_name, 20);
1477 --
1478 RETURN l_formula_id;
1479 END get_shadow_formula_id;
1480
1481
1482 --
1483 --==============================================================================
1484 --|---------------------------< update_shadow_formula >------------------------|
1485 --==============================================================================
1486 PROCEDURE update_shadow_formula (
1487 p_formula_id IN NUMBER
1488 ,p_search_string IN VARCHAR2
1489 ,p_replace_string IN VARCHAR2
1490 )
1491 IS
1492 --
1493 -- Cursor to retrieve the formula information
1494 CURSOR csr_get_formula_info
1495 IS
1496 SELECT formula_text
1497 FROM pay_shadow_formulas
1498 WHERE formula_id = p_formula_id;
1499
1500 l_proc_name VARCHAR2 (80)
1501 := g_proc_name
1502 || 'update_shadow_formula';
1503 /* Bug No:13520124
1504 l_formula_text LONG;*/
1505 l_formula_text pay_shadow_formulas.formula_text%type;
1506 --
1507 BEGIN
1508 --
1509 hr_utility.set_location ( 'Entering: '
1510 || l_proc_name, 10);
1511 --
1512 OPEN csr_get_formula_info;
1513 FETCH csr_get_formula_info INTO l_formula_text;
1514
1515 IF csr_get_formula_info%NOTFOUND
1516 THEN
1517 CLOSE csr_get_formula_info;
1518 fnd_message.set_name ('PER', 'HR_289263_FORMULA_ID_INVALID');
1519 fnd_message.raise_error;
1520 END IF; -- End if of csr row not found check ...
1521
1522 CLOSE csr_get_formula_info;
1523 l_formula_text :=
1524 REPLACE (l_formula_text, p_search_string, p_replace_string);
1525 --
1526 hr_utility.set_location (l_proc_name, 20);
1527
1528 --
1529 UPDATE pay_shadow_formulas
1530 SET formula_text = l_formula_text
1531 WHERE formula_id = p_formula_id;
1532
1533 --
1534 hr_utility.set_location ( 'Leaving: '
1535 || l_proc_name, 30);
1536
1537 --
1538 END update_shadow_formula;
1539
1540
1541 --
1542 --
1543 --==============================================================================
1544 --|---------------------------< get_input_value_id >---------------------------|
1545 --==============================================================================
1546 FUNCTION get_input_value_id (
1547 p_input_value_name IN VARCHAR2
1548 ,p_element_type_id IN NUMBER
1549 ,p_element_name IN VARCHAR2
1550 )
1551 RETURN NUMBER
1552 IS
1553 --
1554 -- Cursor to retrieve the input value information
1555 CURSOR csr_get_ipv_info (c_element_type_id NUMBER)
1556 IS
1557 SELECT input_value_id
1558 FROM pay_input_values_f
1559 WHERE NAME = p_input_value_name
1560 AND element_type_id = c_element_type_id
1561 AND p_effective_start_date BETWEEN effective_start_date
1562 AND effective_end_date;
1563
1564 l_proc_name VARCHAR2 (80)
1565 := g_proc_name
1566 || 'get_input_value_id';
1567 l_input_value_id NUMBER;
1568 l_element_type_id NUMBER;
1569 --
1570 BEGIN
1571 --
1572 hr_utility.set_location ( 'Entering: '
1573 || l_proc_name, 10);
1574
1575 IF p_element_type_id IS NULL
1576 THEN
1577 --
1578 -- Get the element type id for the element name
1579 l_element_type_id :=
1580 get_object_id (
1581 p_object_type => 'ELE'
1582 ,p_object_name => p_element_name
1583 );
1584 ELSE -- p_element_type_id is not null
1585 l_element_type_id := p_element_type_id;
1586 END IF; -- End if of p_element_type_id is null check ...
1587
1588 --
1589 hr_utility.set_location (l_proc_name, 20);
1590 --
1591 OPEN csr_get_ipv_info (l_element_type_id);
1592 FETCH csr_get_ipv_info INTO l_input_value_id;
1593
1594 IF csr_get_ipv_info%NOTFOUND
1595 THEN
1596 CLOSE csr_get_ipv_info;
1597 fnd_message.set_name ('PQP', 'PQP_230935_INPUT_VAL_NOTFOUND');
1598 fnd_message.set_token ('INPUT_VALUE', p_input_value_name);
1599 fnd_message.raise_error;
1600 END IF; -- End if of csr row not found check ...
1601
1602 CLOSE csr_get_ipv_info;
1603
1604 --
1605 hr_utility.set_location ( 'Leaving: '
1606 || l_proc_name, 20);
1607 --
1608 RETURN l_input_value_id;
1609 END get_input_value_id;
1610
1611 --
1612
1613 --
1614 --==============================================================================
1615 --|---------------------------< update_ipv_mandatory_flag >--------------------|
1616 --==============================================================================
1617 PROCEDURE update_ipv_mandatory_flag (
1618 p_input_value_name IN VARCHAR2
1619 ,p_element_type_id IN NUMBER
1620 ,p_mandatory_flag IN VARCHAR2
1621 )
1622 IS
1623 --
1624 l_proc_name VARCHAR2 (80)
1625 := g_proc_name
1626 || 'update_ipv_mandatory_flag';
1627 l_input_value_id NUMBER;
1628 --
1629 BEGIN
1630 --
1631 hr_utility.set_location ( 'Entering: '
1632 || l_proc_name, 10);
1633 --
1634
1635 -- Get input value id
1636 l_input_value_id :=
1637 get_input_value_id (
1638 p_input_value_name => p_input_value_name
1639 ,p_element_type_id => p_element_type_id
1640 ,p_element_name => NULL
1641 );
1642 --
1643 hr_utility.set_location (l_proc_name, 20);
1644
1645 --
1646 UPDATE pay_input_values_f
1647 SET mandatory_flag = p_mandatory_flag
1648 WHERE input_value_id = l_input_value_id
1649 AND p_effective_start_date BETWEEN effective_start_date
1650 AND effective_end_date;
1651
1652 --
1653 hr_utility.set_location ( 'Leaving: '
1654 || l_proc_name, 30);
1655 --
1656 END update_ipv_mandatory_flag;
1657
1658
1659 --
1660 --
1661 --==============================================================================
1662 --|---------------------------< create_balance_feeds >-------------------------|
1663 --==============================================================================
1664 PROCEDURE create_balance_feeds (
1665 p_balance_type_id IN NUMBER
1666 ,p_element_name IN VARCHAR2
1667 ,p_input_value_name IN VARCHAR2
1668 ,p_scale IN NUMBER
1669 )
1670 IS
1671 --
1672 l_proc_name VARCHAR2 (80)
1673 := g_proc_name
1674 || 'create_balance_feeds';
1675 l_element_type_id NUMBER;
1676 l_input_value_id NUMBER;
1677 l_row_id ROWID;
1678 l_balance_feed_id NUMBER;
1679 --
1680 BEGIN
1681 --
1682 hr_utility.set_location ( 'Entering: '
1683 || l_proc_name, 10);
1684 --
1685 -- Get input value id for the input value name
1686
1687 l_input_value_id :=
1688 get_input_value_id (
1689 p_input_value_name => p_input_value_name
1690 ,p_element_name => p_element_name
1691 ,p_element_type_id => NULL
1692 );
1693 --
1694 hr_utility.set_location (l_proc_name, 20);
1695 --
1696 -- Create Balance Feed
1697 pay_balance_feeds_f_pkg.insert_row (
1698 x_rowid => l_row_id
1699 ,x_balance_feed_id => l_balance_feed_id
1700 ,x_effective_start_date => p_effective_start_date
1701 ,x_effective_end_date => hr_api.g_eot
1702 ,x_business_group_id => p_business_group_id
1703 ,x_legislation_code => NULL
1704 ,x_balance_type_id => p_balance_type_id
1705 ,x_input_value_id => l_input_value_id
1706 ,x_scale => p_scale
1707 ,x_legislation_subgroup => NULL
1708 );
1709 --
1710 hr_utility.set_location ( 'Leaving: '
1711 || l_proc_name, 30);
1712 --
1713 END create_balance_feeds;
1714
1715
1716 --
1717 --
1718 --==============================================================================
1719 --|----------------------------< get_scheme_prefix >---------------------------|
1720 --==============================================================================
1721 FUNCTION get_scheme_prefix (p_element_type_id IN NUMBER)
1722 RETURN VARCHAR2
1723 IS
1724 --
1725 CURSOR csr_get_scheme_prefix
1726 IS
1727 SELECT eei_information18
1728 FROM pay_element_type_extra_info
1729 WHERE element_type_id = p_element_type_id
1730 AND information_type = 'PQP_GB_PENSION_SCHEME_INFO';
1731
1732 l_proc_name VARCHAR2 (80)
1733 := g_proc_name
1734 || 'get_scheme_prefix';
1735 l_scheme_prefix pay_element_type_extra_info.eei_information18%TYPE;
1736 --
1737 BEGIN
1738 --
1739 hr_utility.set_location ( 'Entering: '
1740 || l_proc_name, 10);
1741 --
1742 OPEN csr_get_scheme_prefix;
1743 FETCH csr_get_scheme_prefix INTO l_scheme_prefix;
1744
1745 IF csr_get_scheme_prefix%NOTFOUND
1746 THEN
1747 CLOSE csr_get_scheme_prefix;
1748 fnd_message.set_name ('PQP', 'PQP_230944_PEN_OCP_SCH_INVALID');
1749 fnd_message.raise_error;
1750 END IF; -- End if of row not found check ...
1751
1752 CLOSE csr_get_scheme_prefix;
1753 --
1754 hr_utility.set_location ( 'Leaving: '
1755 || l_proc_name, 20);
1756 --
1757
1758 RETURN l_scheme_prefix;
1759 --
1760 END get_scheme_prefix;
1761
1762 --
1763
1764
1765 --
1766 --==============================================================================
1767 --|----------------------------< create_sub_class_rules >----------------------|
1768 --==============================================================================
1769 PROCEDURE create_sub_class_rules (
1770 p_classification_name IN VARCHAR2
1771 ,p_element_type_id IN NUMBER
1772 )
1773 IS
1774 --
1775 CURSOR csr_get_class_id
1776 IS
1777 SELECT classification_id
1778 FROM pay_element_classifications
1779 WHERE classification_name = p_classification_name
1780 AND legislation_code = g_template_leg_code
1781 AND business_group_id IS NULL;
1782
1783 l_proc_name VARCHAR2 (80)
1784 := g_proc_name
1785 || 'create_sub_class_rules';
1786 l_rowid ROWID;
1787 l_sub_class_rule_id NUMBER;
1788 l_classification_id NUMBER;
1789 l_user_id NUMBER := fnd_global.user_id;
1790 l_login_id NUMBER := fnd_global.login_id;
1791 --
1792 BEGIN
1793 --
1794 hr_utility.set_location ( 'Entering: '
1795 || l_proc_name, 10);
1796 --
1797
1798 -- Get classification id
1799 OPEN csr_get_class_id;
1800 FETCH csr_get_class_id INTO l_classification_id;
1801
1802 IF csr_get_class_id%NOTFOUND
1803 THEN
1804 CLOSE csr_get_class_id;
1805 fnd_message.set_name ('PAY', 'PAY_50060_ETM_BAD_ELE_CLASS');
1806 fnd_message.set_token ('CLASSIFICATION', p_classification_name);
1807 fnd_message.raise_error;
1808 END IF; -- End if of csr class id not found check ...
1809
1810 CLOSE csr_get_class_id;
1811 -- Insert sub classification rule
1812 --
1813 hr_utility.set_location (l_proc_name, 20);
1814 --
1815
1816 pay_sub_class_rules_pkg.insert_row (
1817 p_rowid => l_rowid
1818 ,p_sub_classification_rule_id => l_sub_class_rule_id
1819 ,p_effective_start_date => p_effective_start_date
1820 ,p_effective_end_date => hr_api.g_eot
1821 ,p_element_type_id => p_element_type_id
1822 ,p_classification_id => l_classification_id
1823 ,p_business_group_id => p_business_group_id
1824 ,p_legislation_code => NULL
1825 ,p_creation_date => SYSDATE
1826 ,p_created_by => l_user_id
1827 ,p_last_update_date => SYSDATE
1828 ,p_last_updated_by => l_user_id
1829 ,p_last_update_login => l_login_id
1830 );
1831 --
1832 hr_utility.set_location ( 'Leaving: '
1833 || l_proc_name, 30);
1834 --
1835 END create_sub_class_rules;
1836
1837 --
1838 --
1839 --==============================================================================
1840 --|------------------------------< Main Function >-----------------------------|
1841 --==============================================================================
1842
1843 BEGIN
1844 --
1845 hr_utility.set_location ( 'Entering : '
1846 || l_proc_name, 10);
1847 --
1848
1849 -- Check scheme prefix
1850 chk_scheme_prefix (p_scheme_prefix => p_ele_base_name);
1851 --
1852 hr_utility.set_location (l_proc_name, 20);
1853 --
1854
1855 OPEN csr_chk_uniq_base_name;
1856 FETCH csr_chk_uniq_base_name INTO l_exists;
1857
1858 IF csr_chk_uniq_base_name%FOUND
1859 THEN
1860 -- Raise error
1861 fnd_message.set_name ('PAY', 'PAY_50076_ETM_BASE_NAME_EXISTS');
1862 fnd_message.set_token ('BASE_NAME', p_ele_base_name);
1863 CLOSE csr_chk_uniq_base_name;
1864 hr_multi_message.add
1865 (p_associated_column4
1866 => 'PQP_GB_PENSION_SCHEMES_V.SCHEME_PREFIX'
1867 );
1868 END IF; -- End if of base name row found check...
1869
1870 IF csr_chk_uniq_base_name%ISOPEN THEN
1871 CLOSE csr_chk_uniq_base_name;
1872 END IF; -- Cursor is open check ...
1873
1874 -- Validate all the parameters before processing
1875 --
1876 hr_utility.set_location (l_proc_name, 25);
1877
1878 --
1879
1880 IF p_validate
1881 THEN
1882 insert_validate;
1883 END IF; -- End if of p_validate check ...
1884
1885 -- Initialize all exclusion variables first
1886
1887 l_exc_ocp_rule_id := 'N';
1888 l_exc_ers_cent_rule_id := 'N';
1889 l_exc_ers_fxd_rule_id := 'N';
1890 l_exc_adl_cent_rule_id := 'N';
1891 l_exc_adl_fxd_rule_id := 'N';
1892 l_exc_ayr_cent_rule_id := 'N';
1893 l_exc_ayr_fxd_rule_id := 'N';
1894
1895 l_exc_bb_fwc_cent_rule_id := 'N';
1896 l_exc_bb_fwc_fxd_rule_id := 'N';
1897
1898 l_exc_fwd_cent_rule_id := 'N';
1899 l_exc_fwd_fxd_rule_id := 'N';
1900 l_exc_avc_rule_id := 'N';
1901 l_exc_shp_rule_id := 'N';
1902 l_exc_fsavc_rule_id := 'N';
1903 l_exc_pep_rule_id := 'N';
1904 l_exc_pre_tax_rule_id := 'N';
1905 l_exc_ees_cram_rule_id := 'N';
1906 l_exc_ees_cent_rule_id := 'N';
1907 l_exc_ers_rule_id := 'N';
1908 l_exc_adl_rule_id := 'N';
1909 l_exc_ayr_rule_id := 'N';
1910 l_exc_fwd_rule_id := 'N';
1911 l_exc_ssal_rule_id := 'N';
1912 l_exc_vol_con_rule_id := 'N';
1913 l_exc_itr_rule_id := 'N';
1914 l_exc_fsavc_eer_rule_id := 'N';
1915
1916 l_exc_bb_fwc_rule_id := 'N';
1917 --
1918 -- Set exclusion rule
1919 --
1920 -- Set employees contribution exclusion rule based
1921 -- on pension category
1922
1923 --
1924 hr_utility.set_location (l_proc_name, 50);
1925
1926 --
1927
1928 IF p_pension_category = 'OCP'
1929 THEN
1930 l_exc_ocp_rule_id := NULL;
1931 ELSIF p_pension_category = 'AVC'
1932 THEN
1933 l_exc_avc_rule_id := NULL;
1934 ELSIF p_pension_category = 'SHP'
1935 THEN
1936 l_exc_shp_rule_id := NULL;
1937 ELSIF p_pension_category = 'FSAVC'
1938 THEN
1939 l_exc_fsavc_rule_id := NULL;
1940 ELSIF p_pension_category = 'PEP'
1941 THEN
1942 l_exc_pep_rule_id := NULL;
1943 END IF; -- End if of pension category check ...
1944
1945 -- Set employees input value exclusion rule based
1946 -- on employees deduction method
1947
1948 --
1949 hr_utility.set_location (l_proc_name, 60);
1950 --
1951
1952 -- Check whether the pension type supports the employee
1953 -- and employer deduction method chosen
1954
1955 l_cont_iv_name := NULL;
1956 l_skip_formula := NULL;
1957 l_iv_default_value := NULL;
1958
1959 IF l_emp_deduction_method = 'PE'
1960 OR l_emp_deduction_method = 'PEFR'
1961 THEN
1962 IF g_tab_pension_types_info (p_pension_type_id).ee_contribution_percent IS NULL
1963 THEN
1964 fnd_message.set_name ('PQP', 'PQP_230931_PEN_INVALID_DED_MTH');
1965 fnd_message.set_token ('DEDUCTION_METHOD', 'Employee Percentage');
1966 fnd_message.set_token (
1967 'PENSION_TYPE'
1968 ,g_tab_pension_types_info (p_pension_type_id).pension_type_name
1969 );
1970 IF l_emp_deduction_method = 'PEFR'
1971 THEN
1972 IF g_tab_pension_types_info (p_pension_type_id).ee_contribution_fixed_rate IS NULL
1973 THEN
1974 fnd_message.set_name ('PQP', 'PQP_230931_PEN_INVALID_DED_MTH');
1975 fnd_message.set_token (
1976 'DEDUCTION_METHOD'
1977 ,'Employee Percentage and Fixed Rate'
1978 );
1979 fnd_message.set_token (
1980 'PENSION_TYPE'
1981 ,g_tab_pension_types_info (p_pension_type_id).pension_type_name
1982 );
1983 END IF; -- End if of pension type support FR deduction method check ...
1984 END IF; -- End if of emp deduction method is PEFR check ...
1985 hr_multi_message.add
1986 (p_associated_column2
1987 => 'EMPLOYEE_DEDUCTION_METHOD'
1988 );
1989 END IF; -- End if of pension type support % deduction method check ...
1990
1991 -- Check whether deduction method FR is supported by this
1992 -- pension type
1993 IF l_emp_deduction_method = 'PEFR'
1994 THEN
1995 IF g_tab_pension_types_info (p_pension_type_id).ee_contribution_fixed_rate IS NULL
1996 THEN
1997 fnd_message.set_name ('PQP', 'PQP_230931_PEN_INVALID_DED_MTH');
1998 fnd_message.set_token (
1999 'DEDUCTION_METHOD'
2000 ,'Employee Fixed Rate'
2001 );
2002 fnd_message.set_token (
2003 'PENSION_TYPE'
2004 ,g_tab_pension_types_info (p_pension_type_id).pension_type_name
2005 );
2006 hr_multi_message.add
2007 (p_associated_column2
2008 => 'PQP_GB_PENSION_SCHEMES_V.EMPLOYEE_DEDUCTION_METHOD'
2009 );
2010 END IF; -- End if of pension type support FR deduction method check ...
2011
2012 l_emp_deduction_method := 'PE';
2013 END IF; -- End if of emp deduction method is PEFR check ...
2014
2015 l_exc_ees_cent_rule_id := NULL;
2016 l_skip_formula := NULL;
2017 l_cont_iv_name := 'Contribution Percent';
2018 l_iv_default_value :=
2019 g_tab_pension_types_info (p_pension_type_id).ee_contribution_percent;
2020 ELSIF l_emp_deduction_method = 'FR'
2021 THEN
2022 IF g_tab_pension_types_info (p_pension_type_id).ee_contribution_fixed_rate IS NULL
2023 THEN
2024 fnd_message.set_name ('PQP', 'PQP_230931_PEN_INVALID_DED_MTH');
2025 fnd_message.set_token ('DEDUCTION_METHOD', 'Employee Fixed Rate');
2026 fnd_message.set_token (
2027 'PENSION_TYPE'
2028 ,g_tab_pension_types_info (p_pension_type_id).pension_type_name
2029 );
2030 hr_multi_message.add
2031 (p_associated_column2
2032 => 'PQP_GB_PENSION_SCHEMES_V.EMPLOYEE_DEDUCTION_METHOD'
2033 );
2034 END IF; -- End if of pension type support FR deduction method check ...
2035
2036 l_exc_ees_cram_rule_id := NULL;
2037 l_skip_formula := 'ONCE_EACH_PERIOD';
2038 l_cont_iv_name := 'Contribution Amount';
2039 l_iv_default_value :=
2040 g_tab_pension_types_info (p_pension_type_id).ee_contribution_fixed_rate;
2041 END IF; -- End if of emp deduction method check ...
2042
2043 -- Set employers contribution exclusion rule based
2044 -- on employer deduction method
2045
2046 --
2047 hr_utility.set_location (l_proc_name, 70);
2048
2049 --
2050
2051 IF p_eer_deduction_method = 'PE'
2052 THEN
2053 IF g_tab_pension_types_info (p_pension_type_id).er_contribution_percent IS NULL
2054 THEN
2055 fnd_message.set_name ('PQP', 'PQP_230931_PEN_INVALID_DED_MTH');
2056 fnd_message.set_token ('DEDUCTION_METHOD', 'Employer Percentage');
2057 fnd_message.set_token (
2058 'PENSION_TYPE'
2059 ,g_tab_pension_types_info (p_pension_type_id).pension_type_name
2060 );
2061 hr_multi_message.add
2062 (p_associated_column3
2063 => 'PQP_GB_PENSION_SCHEMES_V.EMPLOYER_DEDUCTION_METHOD'
2064 );
2065
2066 END IF; -- End if of pension type support % deduction method check ...
2067
2068 l_exc_ers_cent_rule_id := NULL;
2069 l_exc_ers_rule_id := NULL;
2070 ELSIF p_eer_deduction_method = 'FR'
2071 THEN
2072 IF g_tab_pension_types_info (p_pension_type_id).er_contribution_fixed_rate IS NULL
2073 THEN
2074 fnd_message.set_name ('PQP', 'PQP_230931_PEN_INVALID_DED_MTH');
2075 fnd_message.set_token ('DEDUCTION_METHOD', 'Employer Fixed Rate');
2076 fnd_message.set_token (
2077 'PENSION_TYPE'
2078 ,g_tab_pension_types_info (p_pension_type_id).pension_type_name
2079 );
2080 hr_multi_message.add
2081 (p_associated_column3
2082 => 'PQP_GB_PENSION_SCHEMES_V.EMPLOYER_DEDUCTION_METHOD'
2083 );
2084 END IF; -- End if of pension type support FR deduction method check ...
2085
2086 l_exc_ers_fxd_rule_id := NULL;
2087 l_exc_ers_rule_id := NULL;
2088 ELSIF p_eer_deduction_method = 'PEFR'
2089 THEN
2090 IF g_tab_pension_types_info (p_pension_type_id).er_contribution_percent IS NULL
2091 AND g_tab_pension_types_info (p_pension_type_id).er_contribution_fixed_rate IS NULL
2092 THEN
2093 fnd_message.set_name ('PQP', 'PQP_230931_PEN_INVALID_DED_MTH');
2094 fnd_message.set_token (
2095 'DEDUCTION_METHOD'
2096 ,'Employer Percentage and Fixed Rate'
2097 );
2098 fnd_message.set_token (
2099 'PENSION_TYPE'
2100 ,g_tab_pension_types_info (p_pension_type_id).pension_type_name
2101 );
2102 hr_multi_message.add
2103 (p_associated_column3
2104 => 'PQP_GB_PENSION_SCHEMES_V.EMPLOYER_DEDUCTION_METHOD'
2105 );
2106 END IF; -- End if of pension type support % and FR deduction method check ...
2107
2108 IF g_tab_pension_types_info (p_pension_type_id).er_contribution_fixed_rate IS NULL
2109 THEN
2110 fnd_message.set_name ('PQP', 'PQP_230931_PEN_INVALID_DED_MTH');
2111 fnd_message.set_token ('DEDUCTION_METHOD', 'Employer Fixed Rate');
2112 fnd_message.set_token (
2113 'PENSION_TYPE'
2114 ,g_tab_pension_types_info (p_pension_type_id).pension_type_name
2115 );
2116 hr_multi_message.add
2117 (p_associated_column3
2118 => 'PQP_GB_PENSION_SCHEMES_V.EMPLOYER_DEDUCTION_METHOD'
2119 );
2120 END IF; -- End if of fixed rate is null check ...
2121
2122 IF g_tab_pension_types_info (p_pension_type_id).er_contribution_percent IS NULL
2123 THEN
2124 fnd_message.set_name ('PQP', 'PQP_230931_PEN_INVALID_DED_MTH');
2125 fnd_message.set_token ('DEDUCTION_METHOD', 'Employer Percentage');
2126 fnd_message.set_token (
2127 'PENSION_TYPE'
2128 ,g_tab_pension_types_info (p_pension_type_id).pension_type_name
2129 );
2130 hr_multi_message.add
2131 (p_associated_column3
2132 => 'PQP_GB_PENSION_SCHEMES_V.EMPLOYER_DEDUCTION_METHOD'
2133 );
2134 END IF; -- End if of percentage is null check ...
2135
2136 l_exc_ers_cent_rule_id := NULL;
2137 l_exc_ers_fxd_rule_id := NULL;
2138 l_exc_ers_rule_id := NULL;
2139 END IF; -- End if of eer deduction method check ...
2140
2141 hr_multi_message.end_validation_set;
2142
2143 -- Set exclusion rules for additional, added and family widower
2144 -- contributions
2145
2146 --
2147 hr_utility.set_location (l_proc_name, 80);
2148
2149 --
2150
2151 IF p_additional_contribution = 'PE'
2152 THEN
2153 l_exc_adl_cent_rule_id := NULL;
2154 l_exc_adl_rule_id := NULL;
2155 ELSIF p_additional_contribution = 'FR'
2156 THEN
2157 l_exc_adl_fxd_rule_id := NULL;
2158 l_exc_adl_rule_id := NULL;
2159 ELSIF p_additional_contribution = 'PEFR'
2160 THEN
2161 l_exc_adl_cent_rule_id := NULL;
2162 l_exc_adl_fxd_rule_id := NULL;
2163 l_exc_adl_rule_id := NULL;
2164 END IF; -- End if of additional contribution check ...
2165
2166 IF p_added_years = 'PE'
2167 THEN
2168 l_exc_ayr_cent_rule_id := NULL;
2169 l_exc_ayr_rule_id := NULL;
2170 ELSIF p_added_years = 'FR'
2171 THEN
2172 l_exc_ayr_fxd_rule_id := NULL;
2173 l_exc_ayr_rule_id := NULL;
2174 ELSIF p_added_years = 'PEFR'
2175 THEN
2176 l_exc_ayr_cent_rule_id := NULL;
2177 l_exc_ayr_fxd_rule_id := NULL;
2178 l_exc_ayr_rule_id := NULL;
2179 END IF; -- End if of added years check ...
2180
2181 IF p_family_widower = 'PE'
2182 THEN
2183 l_exc_fwd_cent_rule_id := NULL;
2184 l_exc_fwd_rule_id := NULL;
2185 ELSIF p_family_widower = 'FR'
2186 THEN
2187 l_exc_fwd_fxd_rule_id := NULL;
2188 l_exc_fwd_rule_id := NULL;
2189 ELSIF p_family_widower = 'PEFR'
2190 THEN
2191 l_exc_fwd_cent_rule_id := NULL;
2192 l_exc_fwd_fxd_rule_id := NULL;
2193 l_exc_fwd_rule_id := NULL;
2194 END IF; -- End if of family widower check ...
2195
2196
2197 IF p_fwc_added_years = 'PE'
2198 THEN
2199 l_exc_bb_fwc_cent_rule_id := NULL;
2200 l_exc_bb_fwc_rule_id := NULL;
2201 ELSIF p_fwc_added_years = 'FR'
2202 THEN
2203 l_exc_bb_fwc_fxd_rule_id := NULL;
2204 l_exc_bb_fwc_rule_id := NULL;
2205 ELSIF p_fwc_added_years = 'PEFR'
2206 THEN
2207 l_exc_bb_fwc_cent_rule_id := NULL;
2208 l_exc_bb_fwc_fxd_rule_id := NULL;
2209 l_exc_bb_fwc_rule_id := NULL;
2210 END IF; -- End if of added years check ...
2211 -- Get element classification name
2212 --
2213 hr_utility.set_location (l_proc_name, 90);
2214 --
2215
2216 get_ele_classification_info (
2217 p_classification_id => p_ele_classification_id
2218 ,p_classification_name => l_ele_class_name
2219 ,p_default_priority => l_base_processing_priority
2220 );
2221
2222 IF l_base_processing_priority = 4500
2223 AND l_ele_class_name = 'Pre Tax Deductions'
2224 THEN
2225 l_base_processing_priority := 4100;
2226 END IF; -- End if of base processing priority check ...
2227
2228 -- Set exclusion rule for pre-tax and vol cont
2229 -- based on classification name
2230
2231 IF l_ele_class_name = 'Pre Tax Deductions'
2232 THEN
2233 l_exc_pre_tax_rule_id := NULL;
2234
2235 -- Set exclusion rule for FSAVC employer contribution
2236 -- balance feed
2237 IF l_exc_fsavc_rule_id IS NULL
2238 THEN
2239 l_exc_fsavc_eer_rule_id := NULL;
2240 END IF; -- End if of fsavc check ...
2241 ELSIF l_ele_class_name = 'Voluntary Deductions'
2242 THEN
2243 l_exc_vol_con_rule_id := NULL;
2244 END IF; -- End if of ele class name check ...
2245
2246 -- Set exclusion rule for superannuable salary balance
2247 -- based on pensionable salary information
2248 --
2249 hr_utility.set_location (l_proc_name, 100);
2250
2251 --
2252
2253 IF p_pensionable_sal_bal_id IS NULL
2254 THEN
2255 l_exc_ssal_rule_id := NULL;
2256 END IF; -- End if of pensionable sal bal check ...
2257
2258 -- Set exclusion rule for iterative rules
2259 -- Check iterative processing is chosen only for classification
2260 -- pre tax deduction
2261
2262 l_iterative_flag := 'N';
2263
2264 IF l_ele_class_name = 'Pre Tax Deductions'
2265 THEN
2266 IF p_iterative_processing = 'Y'
2267 THEN
2268 l_iterative_flag := 'Y';
2269 l_exc_itr_rule_id := NULL;
2270 END IF; -- End if of iterative processing is enabled check ...
2271 END IF; -- End if of classification is pre tax check ...
2272
2273 -- Get Source Template ID
2274
2275 --
2276 hr_utility.set_location (l_proc_name, 110);
2277 --
2278
2279 l_source_template_id :=
2280 get_template_id (p_legislation_code => g_template_leg_code);
2281 /*--------------------------------------------------------------------------
2282 Create the user Structure
2283 The Configuration Flex segments for the Exclusion Rules are as follows:
2284 ---------------------------------------------------------------------------
2285 Config1 -- l_exc_ocp_rule_id
2286 Config2 -- l_exc_pre_tax_rule_id
2287 Config3 -- l_exc_ees_cram_rule_id
2288 Config4 -- l_exc_ees_cent_rule_id
2289 Config5 -- l_exc_ers_cent_rule_id
2290 Config6 -- l_exc_ers_fxd_rule_id
2291 Config7 -- l_exc_adl_cent_rule_id
2292 Config8 -- l_exc_adl_fxd_rule_id
2293 Config9 -- l_exc_ayr_cent_rule_id
2294 Config10 -- l_exc_ayr_fxd_rule_id
2295 Config11 -- l_exc_fwd_cent_rule_id
2296 Config12 -- l_exc_fwd_fxd_rule_id
2297 Config13 -- l_exc_avc_rule_id
2298 Config14 -- l_exc_shp_rule_id
2299 Config15 -- l_exc_fsavc_rule_id
2300 Config16 -- l_exc_pep_rule_id
2301 Config17 -- l_exc_ers_rule_id
2302 Config18 -- l_exc_adl_rule_id
2303 Config19 -- l_exc_ayr_rule_id
2304 Config20 -- l_exc_fwd_rule_id
2305 Config21 -- l_exc_ssal_rule_id
2306 Config22 -- l_exc_vol_con_rule_id
2307 Config23 -- l_exc_itr_rule_id
2308 Config24 -- l_exc_fsavc_eer_rule_id
2309
2310 ---------------------------------------------------------------------------*/
2311 --
2312 hr_utility.set_location (l_proc_name, 120);
2313 --
2314
2315 --
2316 -- create user structure from the template
2317 --
2318 pay_element_template_api.create_user_structure (
2319 p_validate => FALSE
2320 ,p_effective_date => p_effective_start_date
2321 ,p_business_group_id => p_business_group_id
2322 ,p_source_template_id => l_source_template_id
2323 ,p_base_name => p_ele_base_name
2324 ,p_base_processing_priority => l_base_processing_priority
2325 ,p_configuration_information1 => l_exc_ocp_rule_id
2326 ,p_configuration_information2 => l_exc_pre_tax_rule_id
2327 ,p_configuration_information3 => l_exc_ees_cram_rule_id
2328 ,p_configuration_information4 => l_exc_ees_cent_rule_id
2329 ,p_configuration_information5 => l_exc_ers_cent_rule_id
2330 ,p_configuration_information6 => l_exc_ers_fxd_rule_id
2331 ,p_configuration_information7 => l_exc_adl_cent_rule_id
2332 ,p_configuration_information8 => l_exc_adl_fxd_rule_id
2333 ,p_configuration_information9 => l_exc_ayr_cent_rule_id
2334 ,p_configuration_information10 => l_exc_ayr_fxd_rule_id
2335 ,p_configuration_information11 => l_exc_fwd_cent_rule_id
2336 ,p_configuration_information12 => l_exc_fwd_fxd_rule_id
2337 ,p_configuration_information13 => l_exc_avc_rule_id
2338 ,p_configuration_information14 => l_exc_shp_rule_id
2339 ,p_configuration_information15 => l_exc_fsavc_rule_id
2340 ,p_configuration_information16 => l_exc_pep_rule_id
2341 ,p_configuration_information17 => l_exc_ers_rule_id
2342 ,p_configuration_information18 => l_exc_adl_rule_id
2343 ,p_configuration_information19 => l_exc_ayr_rule_id
2344 ,p_configuration_information20 => l_exc_fwd_rule_id
2345 ,p_configuration_information21 => l_exc_ssal_rule_id
2346 ,p_configuration_information22 => l_exc_vol_con_rule_id
2347 ,p_configuration_information23 => l_exc_itr_rule_id
2348 ,p_configuration_information24 => l_exc_fsavc_eer_rule_id
2349 ,p_configuration_information25 => l_exc_bb_fwc_cent_rule_id
2350 ,p_configuration_information26 => l_exc_bb_fwc_fxd_rule_id
2351 ,p_configuration_information27 => l_exc_bb_fwc_rule_id
2352 ,p_template_id => l_template_id
2353 ,p_object_version_number => l_object_version_number
2354 ,p_allow_base_name_reuse => TRUE
2355 );
2356 --
2357 hr_utility.set_location (l_proc_name, 130);
2358 --
2359 ---------------------------- Update Shadow Structure ----------------------
2360 --
2361
2362 -- Employee Contribution element
2363 i := 0;
2364 i := i
2365 + 1;
2366 l_ele_name (i) :=
2367 p_ele_base_name
2368 || ' EES '
2369 || p_pension_category
2370 || ' Contribution';
2371 OPEN csr_get_shadow_ele_info (l_ele_name (i));
2372 FETCH csr_get_shadow_ele_info INTO l_element_type_id
2373 ,l_ele_obj_ver_number;
2374 CLOSE csr_get_shadow_ele_info;
2375 -- Get iterative priority and formula only if
2376 -- iterative flag is set
2377
2378 l_iterative_priority := NULL;
2379 l_iterative_formula := NULL;
2380
2381 IF l_iterative_flag = 'Y'
2382 THEN
2383 l_iterative_priority :=
2384 get_iterative_priority (p_element_type_id => l_element_type_id);
2385 l_iterative_formula := 'PQP_GB_ITERATIVE_PRETAX';
2386 END IF; -- End if of iterative flag = Y check ...
2387
2388 --
2389 hr_utility.set_location (l_proc_name, 140);
2390 --
2391
2392 pay_shadow_element_api.update_shadow_element (
2393 p_validate => FALSE
2394 ,p_effective_date => p_effective_start_date
2395 ,p_element_type_id => l_element_type_id
2396 ,p_element_name => l_ele_name (i)
2397 ,p_reporting_name => p_ele_reporting_name
2398 ,p_description => p_ele_description
2399 ,p_classification_name => l_ele_class_name
2400 ,p_skip_formula => l_skip_formula
2401 ,p_third_party_pay_only_flag => p_third_party_only_flag
2402 ,p_iterative_flag => l_iterative_flag
2403 ,p_iterative_priority => l_iterative_priority
2404 ,p_iterative_formula_name => l_iterative_formula
2405 ,p_standard_link_flag => l_standard_link
2406 ,p_post_termination_rule => p_termination_rule
2407 ,p_object_version_number => l_ele_obj_ver_number
2408 );
2409 --
2410 hr_utility.set_location (l_proc_name, 150);
2411
2412 --
2413
2414 -- Employer Pension Element
2415
2416 IF l_exc_ers_cent_rule_id IS NULL
2417 THEN
2418 i := i
2419 + 1;
2420 l_ele_name (i) :=
2421 p_ele_base_name
2422 || ' ERS Contribution';
2423 l_ele_new_name (i) := p_ele_base_name
2424 || ' ERS '
2425 || p_pension_category
2426 || ' Contribution';
2427 END IF; -- End if of ers cent rule null check ...
2428
2429 IF l_exc_ers_fxd_rule_id IS NULL
2430 THEN
2431 i := i
2432 + 1;
2433 l_ele_name (i) :=
2434 p_ele_base_name
2435 || ' ERS Contribution Fixed';
2436 l_ele_new_name (i) := p_ele_base_name
2437 || ' ERS '
2438 || p_pension_category
2439 || ' Contribution Fixed';
2440 END IF; -- End if of ers fr rule null check ...
2441
2442 -- Start from 2 as the first one will always be a base element
2443
2444 IF i > 1
2445 THEN
2446 FOR i IN 2 .. l_ele_name.LAST
2447 LOOP
2448 OPEN csr_get_shadow_ele_info (l_ele_name (i));
2449
2450 LOOP
2451 FETCH csr_get_shadow_ele_info INTO l_element_type_id
2452 ,l_ele_obj_ver_number;
2453 EXIT WHEN csr_get_shadow_ele_info%NOTFOUND;
2454 --
2455 hr_utility.set_location (l_proc_name, 170);
2456
2457 --
2458
2459 pay_shadow_element_api.update_shadow_element (
2460 p_validate => FALSE
2461 ,p_effective_date => p_effective_start_date
2462 ,p_element_type_id => l_element_type_id
2463 ,p_element_name => l_ele_new_name (i)
2464 ,p_third_party_pay_only_flag => p_third_party_only_flag
2465 ,p_standard_link_flag => l_standard_link
2466 ,p_post_termination_rule => p_termination_rule
2467 ,p_object_version_number => l_ele_obj_ver_number
2468 );
2469 -- Move the ele new name to the ele name
2470 l_ele_name (i) := l_ele_new_name (i);
2471 END LOOP;
2472
2473 CLOSE csr_get_shadow_ele_info;
2474 END LOOP;
2475 END IF; -- End if of i > 1 check ...
2476
2477 --
2478 hr_utility.set_location (l_proc_name, 180);
2479 --
2480
2481 -- set counter initial value
2482 j := l_ele_name.LAST;
2483
2484 -- Additional Contribution Element
2485
2486 IF l_exc_adl_cent_rule_id IS NULL
2487 THEN
2488 i := i
2489 + 1;
2490 l_ele_name (i) :=
2491 p_ele_base_name
2492 || ' Additional Contribution';
2493 END IF; -- End if of ers cent rule null check ...
2494
2495 IF l_exc_adl_fxd_rule_id IS NULL
2496 THEN
2497 i := i
2498 + 1;
2499 l_ele_name (i) :=
2500 p_ele_base_name
2501 || ' Additional Contribution Fixed';
2502 END IF; -- End if of ers fr rule null check ...
2503
2504 -- Added Years Element
2505
2506 IF l_exc_ayr_cent_rule_id IS NULL
2507 THEN
2508 i := i
2509 + 1;
2510 l_ele_name (i) := p_ele_base_name
2511 || ' Added Years';
2512 END IF; -- End if of ers cent rule null check ...
2513
2514 IF l_exc_ayr_fxd_rule_id IS NULL
2515 THEN
2516 i := i
2517 + 1;
2518 l_ele_name (i) :=
2519 p_ele_base_name
2520 || ' Added Years Fixed';
2521 END IF; -- End if of ers fr rule null check ...
2522
2523 -- Family Widower Element
2524
2525 IF l_exc_fwd_cent_rule_id IS NULL
2526 THEN
2527 i := i
2528 + 1;
2529 l_ele_name (i) := p_ele_base_name
2530 || ' Family Widower';
2531 END IF; -- End if of ers cent rule null check ...
2532
2533 IF l_exc_fwd_fxd_rule_id IS NULL
2534 THEN
2535 i := i
2536 + 1;
2537 l_ele_name (i) :=
2538 p_ele_base_name
2539 || ' Family Widower Fixed';
2540 END IF; -- End if of ers fr rule null check ...
2541
2542
2543 -- Family Widower Added Years Element
2544
2545 IF l_exc_bb_fwc_cent_rule_id IS NULL
2546 THEN
2547 i := i
2548 + 1;
2549 l_ele_name (i) := p_ele_base_name
2550 || ' Buy Back FWC';
2551 END IF; -- End if of ers cent rule null check ...
2552
2553 IF l_exc_bb_fwc_fxd_rule_id IS NULL
2554 THEN
2555 i := i
2556 + 1;
2557 l_ele_name (i) :=
2558 p_ele_base_name
2559 || ' Buy Back FWC Fixed';
2560 END IF; -- End if of ers fr rule null check ...
2561
2562
2563
2564 -- Start after j
2565 i := l_ele_name.NEXT (j);
2566
2567 WHILE i IS NOT NULL
2568 LOOP
2569 OPEN csr_get_shadow_ele_info (l_ele_name (i));
2570
2571 LOOP
2572 FETCH csr_get_shadow_ele_info INTO l_element_type_id
2573 ,l_ele_obj_ver_number;
2574 EXIT WHEN csr_get_shadow_ele_info%NOTFOUND;
2575
2576 IF l_iterative_flag = 'Y'
2577 THEN
2578 -- Get iterative priority for this element
2579 l_iterative_priority :=
2580 get_iterative_priority (
2581 p_element_type_id => l_element_type_id
2582 );
2583 END IF; -- End if of iterative flag = Y check ...
2584
2585 --
2586 hr_utility.set_location (l_proc_name, 190);
2587 --
2588
2589 pay_shadow_element_api.update_shadow_element (
2590 p_validate => FALSE
2591 ,p_effective_date => p_effective_start_date
2592 ,p_element_type_id => l_element_type_id
2593 ,p_element_name => l_ele_name (i)
2594 ,p_classification_name => l_ele_class_name
2595 ,p_third_party_pay_only_flag => p_third_party_only_flag
2596 ,p_iterative_flag => l_iterative_flag
2597 ,p_iterative_priority => l_iterative_priority
2598 ,p_iterative_formula_name => l_iterative_formula
2599 ,p_standard_link_flag => l_standard_link
2600 ,p_post_termination_rule => p_termination_rule
2601 ,p_object_version_number => l_ele_obj_ver_number
2602 );
2603 END LOOP;
2604
2605 CLOSE csr_get_shadow_ele_info;
2606 i := l_ele_name.NEXT (i);
2607 END LOOP;
2608
2609 -- Update shadow structure for Balances
2610
2611 --
2612 hr_utility.set_location (l_proc_name, 200);
2613
2614 --
2615
2616 -- Employer Pension Balance
2617
2618 IF l_exc_ers_rule_id IS NULL
2619 THEN
2620 l_bal_name :=
2621 p_ele_base_name
2622 || ' ERS Contribution';
2623 l_bal_new_name := p_ele_base_name
2624 || ' '
2625 || p_pension_category
2626 || ' ERS Contribution';
2627 OPEN csr_get_shadow_bal_info (l_bal_name);
2628
2629 LOOP
2630 FETCH csr_get_shadow_bal_info INTO l_balance_type_id
2631 ,l_bal_obj_ver_number;
2632 EXIT WHEN csr_get_shadow_bal_info%NOTFOUND;
2633 pay_sbt_upd.upd (
2634 p_effective_date => p_effective_start_date
2635 ,p_balance_type_id => l_balance_type_id
2636 ,p_balance_name => l_bal_new_name
2637 ,p_object_version_number => l_bal_obj_ver_number
2638 );
2639 END LOOP;
2640
2641 CLOSE csr_get_shadow_bal_info;
2642 END IF; -- End if of ers exc rule is null check ...
2643
2644 -- Update shadow formula with OCP base name information
2645 -- for AVC employee contribution
2646 IF l_exc_avc_rule_id IS NULL
2647 THEN
2648 -- Get the base name for associated ocp element
2649 l_associated_ocp_base_name :=
2650 get_scheme_prefix (p_element_type_id => p_associated_ocp_ele_id);
2651 l_associated_ocp_base_name :=
2652 UPPER (TRANSLATE (TRIM (l_associated_ocp_base_name), ' ', '_'));
2653 hr_utility.set_location (l_proc_name, 185);
2654 --
2655 l_ees_cont_formula_id :=
2656 get_shadow_formula_id (p_formula_name => l_ees_cont_formula);
2657 --
2658 hr_utility.set_location (l_proc_name, 186);
2659 --
2660 l_search_string := '<OCP NAME>';
2661 l_replace_string := l_associated_ocp_base_name;
2662 update_shadow_formula (
2663 p_formula_id => l_ees_cont_formula_id
2664 ,p_search_string => l_search_string
2665 ,p_replace_string => l_replace_string
2666 );
2667 END IF; -- End if of avc pension category check ...
2668
2669 -- Update shadow formula with pension category information
2670 -- for employer contribution
2671 IF l_exc_ers_rule_id IS NULL
2672 THEN
2673 -- Update the employer contribution formula
2674 --
2675 hr_utility.set_location (l_proc_name, 191);
2676 --
2677 l_ers_cont_formula_id :=
2678 get_shadow_formula_id (p_formula_name => l_ers_cont_formula);
2679 --
2680 hr_utility.set_location (l_proc_name, 192);
2681 --
2682 l_search_string := '<PENSION CATEGORY>';
2683 l_replace_string := p_pension_category;
2684 update_shadow_formula (
2685 p_formula_id => l_ers_cont_formula_id
2686 ,p_search_string => l_search_string
2687 ,p_replace_string => l_replace_string
2688 );
2689 -- Check whether the pension category is AVC
2690 --
2691 hr_utility.set_location (l_proc_name, 193);
2692
2693 --
2694 IF l_exc_avc_rule_id IS NULL
2695 THEN
2696 l_search_string := '/* OCP_Opt_Out_Date Alias */';
2697 l_replace_string :=
2698 'ALIAS '
2699 || l_associated_ocp_base_name
2700 || '_EES_OCP_CONTRIBUTION_OPT_OUT_DATE_ENTRY_VALUE
2701 AS OCP_Opt_Out_Date';
2702 update_shadow_formula (
2703 p_formula_id => l_ers_cont_formula_id
2704 ,p_search_string => l_search_string
2705 ,p_replace_string => l_replace_string
2706 );
2707 l_search_string := '/* OCP_Opt_Out_Date Default */';
2708 l_replace_string :=
2709 'Default for OCP_Opt_Out_Date IS ''4712/12/31 00:00:00'' (DATE)';
2710 update_shadow_formula (
2711 p_formula_id => l_ers_cont_formula_id
2712 ,p_search_string => l_search_string
2713 ,p_replace_string => l_replace_string
2714 );
2715 l_search_string := '/* OCP_Opt_Out_Date Logic */';
2716 l_replace_string :=
2717 '
2718 /* OCP_Opt_Out_Date Logic */
2719 IF OCP_Opt_Out_Date WAS NOT DEFAULTED THEN
2720 (
2721 /* Check whether the date entered is lesser than the payroll
2722 period end date. */
2723
2724 IF OCP_Opt_Out_Date <= PAY_PROC_PERIOD_END_DATE THEN
2725 (
2726 /* If lesser, then issue a warning message and stop rule */
2727 l_stop_entry = ''Y''
2728 l_warning2 = l_element_name
2729 +
2730 ''Employee opted out from the associated ''
2731 +
2732 ''occupational pension scheme.''
2733 )
2734 )';
2735 update_shadow_formula (
2736 p_formula_id => l_ers_cont_formula_id
2737 ,p_search_string => l_search_string
2738 ,p_replace_string => l_replace_string
2739 );
2740 END IF; -- End if of pension category is AVC check ...
2741 END IF; -- End if of ers rule id is null check ...
2742
2743 -- Update shadow formulas with the pensionable salary information
2744 -- First get the pensionable salary name, update only if is not created
2745
2746 IF p_pensionable_sal_bal_id IS NOT NULL
2747 THEN
2748 --
2749 hr_utility.set_location (l_proc_name, 201);
2750 --
2751 -- Get the balance name
2752 l_pensionable_sal_bal_name :=
2753 get_balance_info (p_balance_type_id => p_pensionable_sal_bal_id);
2754 -- Update the employee contribution formula
2755 --
2756 hr_utility.set_location (l_proc_name, 202);
2757 --
2758 l_ees_cont_formula_id :=
2759 get_shadow_formula_id (p_formula_name => l_ees_cont_formula);
2760 --
2761 hr_utility.set_location (l_proc_name, 203);
2762 --
2763 l_search_string :=
2764 l_format_base_name
2765 || '_SUPERANNUABLE_SALARY';
2766 l_replace_string :=
2767 UPPER (TRANSLATE (TRIM (l_pensionable_sal_bal_name), ' ', '_'));
2768 update_shadow_formula (
2769 p_formula_id => l_ees_cont_formula_id
2770 ,p_search_string => l_search_string
2771 ,p_replace_string => l_replace_string
2772 );
2773
2774 IF l_exc_ers_rule_id IS NULL
2775 THEN
2776 -- Update the employer contribution formula
2777 --
2778 hr_utility.set_location (l_proc_name, 204);
2779 --
2780 l_ers_cont_formula_id :=
2781 get_shadow_formula_id (p_formula_name => l_ers_cont_formula);
2782 --
2783 hr_utility.set_location (l_proc_name, 205);
2784 --
2785 update_shadow_formula (
2786 p_formula_id => l_ers_cont_formula_id
2787 ,p_search_string => l_search_string
2788 ,p_replace_string => l_replace_string
2789 );
2790 END IF; -- End if of ers rule id is null check ...
2791
2792 IF l_exc_adl_rule_id IS NULL
2793 THEN
2794 -- Update the additional contribution formula
2795 --
2796 hr_utility.set_location (l_proc_name, 204);
2797 --
2798 l_adl_cont_formula_id :=
2799 get_shadow_formula_id (p_formula_name => l_adl_cont_formula);
2800 --
2801 hr_utility.set_location (l_proc_name, 205);
2802 --
2803 update_shadow_formula (
2804 p_formula_id => l_adl_cont_formula_id
2805 ,p_search_string => l_search_string
2806 ,p_replace_string => l_replace_string
2807 );
2808 END IF; -- End if of adl rule id is null check ...
2809
2810 IF l_exc_ayr_rule_id IS NULL
2811 THEN
2812 -- Update the added years contribution formula
2813 --
2814 hr_utility.set_location (l_proc_name, 206);
2815 --
2816 l_ayr_cont_formula_id :=
2817 get_shadow_formula_id (p_formula_name => l_ayr_cont_formula);
2818 --
2819 hr_utility.set_location (l_proc_name, 207);
2820 --
2821 update_shadow_formula (
2822 p_formula_id => l_ayr_cont_formula_id
2823 ,p_search_string => l_search_string
2824 ,p_replace_string => l_replace_string
2825 );
2826 END IF; -- End if of ayr rule id is null check ...
2827
2828 IF l_exc_fwd_rule_id IS NULL
2829 THEN
2830 -- Update the family widower contribution formula
2831 --
2832 hr_utility.set_location (l_proc_name, 208);
2833 --
2834 l_fwd_cont_formula_id :=
2835 get_shadow_formula_id (p_formula_name => l_fwd_cont_formula);
2836 --
2837 hr_utility.set_location (l_proc_name, 209);
2838 --
2839 update_shadow_formula (
2840 p_formula_id => l_fwd_cont_formula_id
2841 ,p_search_string => l_search_string
2842 ,p_replace_string => l_replace_string
2843 );
2844 END IF; -- End if of fwd rule id is null check ...
2845
2846
2847 IF l_exc_bb_fwc_rule_id IS NULL
2848 THEN
2849 -- Update the added years family widower contribution formula
2850 --
2851 hr_utility.set_location (l_proc_name, 206);
2852 --
2853 l_bb_fwc_cont_formula_id :=
2854 get_shadow_formula_id (p_formula_name => l_bb_fwc_cont_formula);
2855 --
2856 hr_utility.set_location (l_proc_name, 207);
2857 --
2858 update_shadow_formula (
2859 p_formula_id => l_bb_fwc_cont_formula_id
2860 ,p_search_string => l_search_string
2861 ,p_replace_string => l_replace_string
2862 );
2863 END IF; -- End if of ayr rule id is null check ...
2864
2865
2866 END IF; -- End if of pensionable salary bal specified check ...
2867
2868
2869
2870 -------------------------------------------------------------------------
2871 --
2872 --
2873 hr_utility.set_location (l_proc_name, 210);
2874
2875 ---------------------------------------------------------------------------
2876 ---------------------------- Generate Core Objects ------------------------
2877 ---------------------------------------------------------------------------
2878
2879 pay_element_template_api.generate_part1 (
2880 p_validate => FALSE
2881 ,p_effective_date => p_effective_start_date
2882 ,p_hr_only => FALSE
2883 ,p_hr_to_payroll => FALSE
2884 ,p_template_id => l_template_id
2885 );
2886 --
2887 hr_utility.set_location (l_proc_name, 220);
2888 --
2889
2890 pay_element_template_api.generate_part2 (
2891 p_validate => FALSE
2892 ,p_effective_date => p_effective_start_date
2893 ,p_template_id => l_template_id
2894 );
2895 --
2896
2897 -- Update the default contribution value for base element
2898 -- or Employee Contribution Element
2899 -- Remember l_cont_iv_name and iv_default_value are already
2900 -- stored for employee contribution element
2901
2902 --
2903 hr_utility.set_location (l_proc_name, 230);
2904 --
2905 i := 0;
2906 i := i
2907 + 1;
2908 update_ipval_defval (
2909 p_ele_name => l_ele_name (i)
2910 , -- base element name
2911 p_ip_name => l_cont_iv_name
2912 ,p_def_value => l_iv_default_value
2913 );
2914
2915 -- Create balance feeds for <pension type> EE Contribution balance
2916 IF g_tab_pension_types_info (p_pension_type_id).ee_contribution_bal_type_id IS NULL
2917 THEN
2918 fnd_message.set_name ('PQP', 'PQP_230932_BAL_TYPE_NOTFOUND');
2919 fnd_message.set_token (
2920 'BALANCE_TYPE'
2921 , g_tab_pension_types_info (p_pension_type_id).pension_type_name
2922 || ' EE Contribution'
2923 );
2924 fnd_message.raise_error;
2925 END IF; -- End if of ee contribution balance is null check ...
2926
2927 --
2928 hr_utility.set_location (l_proc_name, 240);
2929 --
2930
2931 create_balance_feeds (
2932 p_balance_type_id => g_tab_pension_types_info (
2933 p_pension_type_id
2934 ).ee_contribution_bal_type_id
2935 ,p_element_name => l_ele_name (i)
2936 ,p_input_value_name => 'Pay Value'
2937 ,p_scale => 1
2938 );
2939 -- update the default contribution value for employer contribution
2940 -- element
2941 -- Remember there may be two elements created for employer contribution
2942 -- so check them before you update the default values
2943 -- safe assumption would be to start checking the exclusion rule
2944
2945 --
2946 hr_utility.set_location (l_proc_name, 250);
2947
2948 --
2949
2950 IF l_exc_ers_cent_rule_id IS NULL
2951 THEN
2952 i := i
2953 + 1;
2954 -- Contribution Percent for Employer is present
2955 update_ipval_defval (
2956 p_ele_name => l_ele_name (
2957 i
2958 ) -- employer contribution %
2959 ,p_ip_name => 'Contribution Percent'
2960 ,p_def_value => g_tab_pension_types_info (
2961 p_pension_type_id
2962 ).er_contribution_percent
2963 );
2964
2965 -- Create balance feeds for <pension type> ER Contribution balance
2966 IF g_tab_pension_types_info (p_pension_type_id).er_contribution_bal_type_id IS NULL
2967 THEN
2968 fnd_message.set_name ('PQP', 'PQP_230932_BAL_TYPE_NOTFOUND');
2969 fnd_message.set_token (
2970 'BALANCE_TYPE'
2971 , g_tab_pension_types_info (p_pension_type_id).pension_type_name
2972 || ' ER Contribution'
2973 );
2974 fnd_message.raise_error;
2975 END IF; -- End if of er contribution balance is null check ...
2976
2977 --
2978 hr_utility.set_location (l_proc_name, 260);
2979 --
2980
2981 create_balance_feeds (
2982 p_balance_type_id => g_tab_pension_types_info (
2983 p_pension_type_id
2984 ).er_contribution_bal_type_id
2985 ,p_element_name => l_ele_name (i)
2986 ,p_input_value_name => 'Pay Value'
2987 ,p_scale => 1
2988 );
2989 END IF; -- End if of employer percent rule is null check ...
2990
2991 --
2992 hr_utility.set_location (l_proc_name, 270);
2993
2994 --
2995
2996 IF l_exc_ers_fxd_rule_id IS NULL
2997 THEN
2998 i := i
2999 + 1;
3000 -- Contribution Fixed Rate for Employer is present
3001 update_ipval_defval (
3002 p_ele_name => l_ele_name (
3003 i
3004 ) -- employer contribution FR
3005 ,p_ip_name => 'Contribution Amount'
3006 ,p_def_value => g_tab_pension_types_info (
3007 p_pension_type_id
3008 ).er_contribution_fixed_rate
3009 );
3010
3011 -- Create balance feeds for <pension type> ER Contribution balance
3012 IF g_tab_pension_types_info (p_pension_type_id).er_contribution_bal_type_id IS NULL
3013 THEN
3014 fnd_message.set_name ('PQP', 'PQP_230932_BAL_TYPE_NOTFOUND');
3015 fnd_message.set_token (
3016 'BALANCE_TYPE'
3017 , g_tab_pension_types_info (p_pension_type_id).pension_type_name
3018 || ' ER Contribution'
3019 );
3020 fnd_message.raise_error;
3021 END IF; -- End if of er contribution balance is null check ...
3022
3023 --
3024 hr_utility.set_location (l_proc_name, 280);
3025 --
3026
3027 create_balance_feeds (
3028 p_balance_type_id => g_tab_pension_types_info (
3029 p_pension_type_id
3030 ).er_contribution_bal_type_id
3031 ,p_element_name => l_ele_name (i)
3032 ,p_input_value_name => 'Pay Value'
3033 ,p_scale => 1
3034 );
3035 END IF; -- End if of employer fixed rate rule is null check ...
3036
3037 --
3038 hr_utility.set_location (l_proc_name, 290);
3039 --
3040
3041 l_base_element_type_id := get_object_id ('ELE', l_ele_name (1));
3042 --
3043 hr_utility.set_location (l_proc_name, 295);
3044 --
3045 -- Get pensionable salary details
3046 l_pensionable_sal_bal_id := NULL;
3047
3048 IF p_pensionable_sal_bal_id IS NULL
3049 THEN
3050 l_pensionable_sal_bal_id :=
3051 get_object_id (
3052 'BAL'
3053 , p_ele_base_name
3054 || ' Superannuable Salary'
3055 );
3056 ELSE -- pensionable sal present
3057 l_pensionable_sal_bal_id := p_pensionable_sal_bal_id;
3058 END IF; -- End if of pensionable sal is null check ...
3059
3060 IF l_pensionable_sal_bal_id IS NULL
3061 THEN
3062 fnd_message.set_name ('PQP', 'PQP_230932_BAL_TYPE_NOTFOUND');
3063 fnd_message.set_token ('BALANCE_TYPE', 'Superannuable Salary');
3064 fnd_message.raise_error;
3065 END IF; -- End if of local pensionable sal id is null check ...
3066
3067 -- Create sub classification rule for the company pension base element
3068 -- create only if it is pre tax and scheme type is comp/cosr
3069 -- and pension category is OCP
3070 IF l_ele_class_name = 'Pre Tax Deductions'
3071 AND p_pension_category = 'OCP'
3072 AND NVL (p_pension_scheme_type, hr_api.g_varchar2) IN
3073 ('COSR', 'COMP')
3074 THEN
3075 l_sub_class_name :=
3076 'Pre Tax Employee Pension '
3077 || p_pension_scheme_type;
3078 --
3079 hr_utility.set_location (l_proc_name, 300);
3080 --
3081
3082 create_sub_class_rules (
3083 p_classification_name => l_sub_class_name
3084 ,p_element_type_id => l_base_element_type_id
3085 );
3086 END IF; -- End if of class name = pre tax check ...
3087
3088 -- Set EEIT with Arrears information type "PQP_GB_ARREARAGE_INFO" for
3089 -- base element and additional elements
3090 -- PS EER contribution element should not have an arrear information set
3091 -- Also within the same loop we could create EEIT rows with the relevant
3092 -- information for pension scheme information type "PQP_GB_PENSION_SCHEME_INFO"
3093
3094 -- Create EEIT for the base element as the values are different
3095
3096 --
3097 hr_utility.set_location (l_proc_name, 310);
3098 --
3099 -- Create a row in pay_element_extra_info with all the element information
3100
3101 pay_element_extra_info_api.create_element_extra_info (
3102 p_element_type_id => l_base_element_type_id
3103 ,p_information_type => 'PQP_GB_PENSION_SCHEME_INFO'
3104 ,p_eei_information_category => 'PQP_GB_PENSION_SCHEME_INFO'
3105 ,p_eei_information1 => p_pension_scheme_name -- pension scheme name
3106 ,p_eei_information2 => fnd_number.number_to_canonical (
3107 p_pension_provider_id
3108 ) -- pension provider
3109 ,p_eei_information3 => fnd_number.number_to_canonical (
3110 p_pension_type_id
3111 ) -- pension type
3112 ,p_eei_information4 => p_pension_category -- pension category
3113 ,p_eei_information5 => fnd_date.date_to_canonical (
3114 p_pension_year_start_dt
3115 ) -- pension year start date
3116 ,p_eei_information6 => l_emp_deduction_method -- employee deduction method
3117 ,p_eei_information7 => p_eer_deduction_method -- employer deduction method
3118 ,p_eei_information8 => p_pension_scheme_type -- scheme type
3119 ,p_eei_information9 => p_scon_number -- SCON
3120 ,p_eei_information10 => p_scheme_reference_no -- Scheme Number
3121 ,p_eei_information11 => p_employer_reference_no -- Employer Reference Number
3122 ,p_eei_information12 => NULL -- Base pension scheme
3123 ,p_eei_information13 => p_additional_contribution -- Additional Contributions
3124 ,p_eei_information14 => p_added_years -- Added Years
3125 ,p_eei_information15 => p_family_widower -- Family or Widower Benefit
3126 ,p_eei_information16 => fnd_number.number_to_canonical (
3127 p_associated_ocp_ele_id
3128 ) -- Associated OCP Scheme
3129 ,p_eei_information17 => fnd_number.number_to_canonical (
3130 l_pensionable_sal_bal_id
3131 ) -- Pensionable Salary Balance
3132 ,p_eei_information18 => p_ele_base_name -- Scheme Prefix
3133 ,p_eei_information19 => p_econ_number -- ECON ( BUG 4108320 )
3134 ,p_eei_information20 => p_fwc_added_years -- Family Widower Added Years
3135 ,p_element_type_extra_info_id => l_eei_info_id
3136 ,p_object_version_number => l_ovn_eei
3137 );
3138 --
3139 hr_utility.set_location (l_proc_name, 320);
3140 --
3141
3142 -- Create a row in pay_element_extra_info with arrearage information
3143 pay_element_extra_info_api.create_element_extra_info (
3144 p_element_type_id => l_base_element_type_id
3145 ,p_information_type => 'PQP_GB_ARREARAGE_INFO'
3146 ,p_eei_information_category => 'PQP_GB_ARREARAGE_INFO'
3147 ,p_eei_information1 => l_arrearage_allowed
3148 , -- Arrears Allowed
3149 p_eei_information2 => l_partial_deduction
3150 , -- Partial Deduction Allowed
3151 p_element_type_extra_info_id => l_eei_info_id
3152 ,p_object_version_number => l_ovn_eei
3153 );
3154 -- Delete the collection that holds the formula ids before requesting
3155 -- compilation
3156 g_tab_formula_ids.DELETE;
3157 -- Compile formula attached with this base element
3158 --
3159 hr_utility.set_location (l_proc_name, 330);
3160 --
3161 compile_formula (p_element_type_id => l_base_element_type_id);
3162
3163 FOR i IN 2 .. l_ele_name.COUNT
3164 LOOP
3165 hr_utility.set_location (l_proc_name, 340);
3166 l_eei_element_type_id := get_object_id ('ELE', l_ele_name (i));
3167 --
3168 hr_utility.set_location (l_proc_name, 350);
3169 --
3170
3171
3172 --- bug fix : 5128634
3173 -- if this is a Family Widower element, store the element id
3174 IF l_ele_name (i) IN ( p_ele_base_name
3175 || ' Family Widower'
3176 )
3177 THEN
3178 l_fwc_element_type_id := l_eei_element_type_id;
3179 END IF;
3180
3181 -- if this is a Family Widower fixed element, store the element id
3182 IF l_ele_name (i) IN ( p_ele_base_name
3183 || ' Family Widower Fixed'
3184 )
3185 THEN
3186 l_fwc_element_type_id_fixed := l_eei_element_type_id;
3187 END IF;
3188
3189 -- if this is a Family Widower Added years element, store the element id of FWC main element(s)
3190 IF l_ele_name (i) IN ( p_ele_base_name
3191 || ' Buy Back FWC'
3192 , p_ele_base_name
3193 || ' Buy Back FWC Fixed'
3194 )
3195 THEN
3196
3197 -- if PEFR, the store FW_id in 21, and FW_fixed_id in 22
3198 IF l_fwc_element_type_id IS NOT NULL
3199 AND
3200 l_fwc_element_type_id_fixed IS NOT NULL
3201 THEN
3202 l_base_fwc_element_type_id := l_fwc_element_type_id;
3203 l_base_fwc_element_type_id_fix := l_fwc_element_type_id_fixed;
3204 ELSIF l_fwc_element_type_id IS NOT NULL -- this is PE, hence store FW_id in both
3205 THEN
3206 l_base_fwc_element_type_id := l_fwc_element_type_id;
3207 l_base_fwc_element_type_id_fix := NULL;
3208 ELSIF l_fwc_element_type_id_fixed IS NOT NULL -- -- this is FR, hence store FW_fixed_id in both
3209 THEN
3210 l_base_fwc_element_type_id := NULL;
3211 l_base_fwc_element_type_id_fix := l_fwc_element_type_id_fixed;
3212 ELSE -- ideally this shd not arise, as it is PE/FR/PEFR
3213 l_base_fwc_element_type_id := NULL;
3214 l_base_fwc_element_type_id_fix := NULL;
3215 END IF;
3216
3217 ELSE -- not a FWC added_years element, hence null
3218 l_base_fwc_element_type_id := NULL;
3219 l_base_fwc_element_type_id_fix := NULL;
3220 END IF;
3221
3222
3223
3224 --
3225 -- Create a row in pay_element_extra_info with all the element information
3226
3227 pay_element_extra_info_api.create_element_extra_info (
3228 p_element_type_id => l_eei_element_type_id
3229 ,p_information_type => 'PQP_GB_PENSION_SCHEME_INFO'
3230 ,p_eei_information_category => 'PQP_GB_PENSION_SCHEME_INFO'
3231 ,p_eei_information1 => p_pension_scheme_name -- pension scheme name
3232 ,p_eei_information2 => fnd_number.number_to_canonical (
3233 p_pension_provider_id
3234 ) -- pension provider
3235 ,p_eei_information3 => fnd_number.number_to_canonical (
3236 p_pension_type_id
3237 ) -- pension type
3238 ,p_eei_information4 => p_pension_category -- pension category
3239 ,p_eei_information5 => fnd_date.date_to_canonical (
3240 p_pension_year_start_dt
3241 ) -- pension year start date
3242 ,p_eei_information6 => l_emp_deduction_method -- employee deduction method
3243 ,p_eei_information7 => p_eer_deduction_method -- employer deduction method
3244 ,p_eei_information9 => p_scon_number -- SCON
3245 ,p_eei_information10 => p_scheme_reference_no -- Scheme Number
3246 ,p_eei_information11 => p_employer_reference_no -- Employer Reference Number
3247 ,p_eei_information12 => fnd_number.number_to_canonical (
3248 l_base_element_type_id
3249 ) -- Base pension scheme
3250 ,p_eei_information16 => fnd_number.number_to_canonical (
3251 p_associated_ocp_ele_id
3252 ) -- Associated OCP Scheme
3253 ,p_eei_information17 => fnd_number.number_to_canonical (
3254 l_pensionable_sal_bal_id
3255 ) -- Pensionable Salary Balance
3256 ,p_eei_information18 => p_ele_base_name -- Scheme Prefix
3257 ,p_eei_information19 => p_econ_number -- ECON ( BUG 4108320 )
3258 ,p_eei_information20 => p_fwc_added_years -- Family Widower Added Years
3259 ,p_eei_information21 => fnd_number.number_to_canonical (
3260 l_base_fwc_element_type_id
3261 ) -- Family Widower base element type_id :5128634
3262 ,p_eei_information22 => fnd_number.number_to_canonical (
3263 l_base_fwc_element_type_id_fix
3264 ) -- Family Widower base element type_id :5128634
3265
3266 ,p_element_type_extra_info_id => l_eei_info_id
3267 ,p_object_version_number => l_ovn_eei
3268 );
3269 --
3270 hr_utility.set_location (l_proc_name, 360);
3271
3272 --
3273
3274 -- Do not create arrear info for ers contribution..
3275 IF l_ele_name (i) NOT IN ( p_ele_base_name
3276 || ' ERS '
3277 || p_pension_category
3278 || ' Contribution'
3279 , p_ele_base_name
3280 || ' ERS '
3281 || p_pension_category
3282 || ' Contribution Fixed'
3283 )
3284 THEN
3285 -- Create a row in pay_element_extra_info with arrearage information
3286 pay_element_extra_info_api.create_element_extra_info (
3287 p_element_type_id => l_eei_element_type_id
3288 ,p_information_type => 'PQP_GB_ARREARAGE_INFO'
3289 ,p_eei_information_category => 'PQP_GB_ARREARAGE_INFO'
3290 ,p_eei_information1 => l_arrearage_allowed
3291 , -- Arrears Allowed
3292 p_eei_information2 => l_partial_deduction
3293 , -- Partial Deduction Allowed
3294 p_element_type_extra_info_id => l_eei_info_id
3295 ,p_object_version_number => l_ovn_eei
3296 );
3297 END IF; -- End if of ele name not in ERS cont check ...
3298
3299 -- Compile formula attached with this base element
3300 --
3301 hr_utility.set_location (l_proc_name, 370);
3302 --
3303 compile_formula (p_element_type_id => l_eei_element_type_id);
3304 END LOOP;
3305
3306 hr_utility.set_location ( 'Leaving :'
3307 || l_proc_name, 380);
3308 RETURN l_base_element_type_id;
3309 --
3310 END create_user_template_low;
3311
3312
3313 --
3314 /*========================================================================
3315 * CREATE_USER_TEMPLATE
3316 *=======================================================================*/
3317 FUNCTION create_user_template (
3318 p_pension_scheme_name IN VARCHAR2
3319 ,p_pension_year_start_dt IN DATE
3320 ,p_pension_category IN VARCHAR2
3321 ,p_pension_provider_id IN NUMBER
3322 ,p_pension_type_id IN NUMBER
3323 ,p_emp_deduction_method IN VARCHAR2
3324 ,p_ele_base_name IN VARCHAR2
3325 ,p_effective_start_date IN DATE
3326 ,p_ele_reporting_name IN VARCHAR2
3327 ,p_ele_classification_id IN NUMBER
3328 ,p_business_group_id IN NUMBER
3329 ,p_eer_deduction_method IN VARCHAR2
3330 ,p_scon_number IN VARCHAR2
3331 ,p_econ_number IN VARCHAR2 -- BUG 4108320
3332 ,p_additional_contribution IN VARCHAR2
3333 ,p_added_years IN VARCHAR2
3334 ,p_family_widower IN VARCHAR2
3335 ,p_fwc_added_years IN VARCHAR2
3336 ,p_scheme_reference_no IN VARCHAR2
3337 ,p_employer_reference_no IN VARCHAR2
3338 ,p_associated_ocp_ele_id IN NUMBER
3339 ,p_ele_description IN VARCHAR2
3340 ,p_pension_scheme_type IN VARCHAR2
3341 ,p_pensionable_sal_bal_id IN NUMBER
3342 ,p_third_party_only_flag IN VARCHAR2
3343 ,p_iterative_processing IN VARCHAR2
3344 ,p_arrearage_allowed IN VARCHAR2
3345 ,p_partial_deduction IN VARCHAR2
3346 ,p_termination_rule IN VARCHAR2
3347 ,p_standard_link IN VARCHAR2
3348 )
3349 RETURN NUMBER
3350 IS
3351 --
3352
3353
3354 /*---------------------------------------------------------------------------
3355 The input values are explained below : V-varchar2, D-Date, N-number
3356 Input-Name Type Valid Values/Explaination
3357 ---------- ----
3358 --------------------------------------------------------------------------
3359 p_pension_scheme_name (V) - User i/p Scheme Name
3360 p_pension_year_start_dt (D) - User i/p Date
3361 p_pension_category (V) - LOV based i/p (OCP/AVC/SHP/FSAVC/PEP)
3362 p_pension_provider_ip (N) - LOV based i/p
3363 p_pension_type_id (N) - LOV based i/p
3364 p_emp_deduction_method (V) - LOV based i/p (PE/FR/PEFR)
3365 p_ele_base_name (V) - User i/p Base Name
3366 p_effective_start_date (D) - User i/p Date
3367 p_ele_reporting_name (V) - User i/p Reporting Name
3368 p_ele_classification_id (N) - LOV based i/p
3369 p_business_group_id (N) - User i/p Business Group
3370 p_eer_deduction_method (V) - LOV based i/p (PE/FR/PEFR)
3371 p_scon_number (V) - User i/p SCON
3372 p_additional_contribution (V) - LOV based i/p (PE/FR/PEFR)
3373 p_added_years (V) - LOV based i/p (PE/FR/PEFR)
3374 p_family_widower (V) - LOV based i/p (PE/FR/PEFR)
3375 p_fwc_added_years (V) - LOV based i/p (PE/FR/PEFR)
3376 p_scheme_reference_no (V) - User i/p Scheme Reference Number
3377 p_employer_reference_no (V) - User i/p Employer Reference Number
3378 p_associated_ocp_ele_id (N) - LOV based i/p
3379 p_ele_description (V) - User i/p Element Description
3380 p_pension_scheme_type (V) - LOV based i/p (COSR/COMP)
3381 p_pensionable_sal_bal_id (N) - LOV based i/p
3382 p_third_party_only_flag (V) - Check box based i/p (Y/N) Default N
3383 p_iterative_processing (V) - Check box based i/p (Y/N) Default N
3384 p_arrearage_allowed (V) - Check box based i/p (Y/N) Default N
3385 p_partial_deduction (V) - Check box based i/p (Y/N) Default N
3386 p_termination_rule (V) - Radio button based i/p (A/F/L) Default L
3387 p_standard_link (V) - Check box based i/p (Y/N) Default N
3388
3389 -----------------------------------------------------------------------------*/
3390 --
3391 l_element_type_id NUMBER;
3392 l_proc_name VARCHAR2 (80)
3393 := g_proc_name
3394 || 'create_user_template';
3395 l_effective_start_date DATE := TRUNC (
3396 p_effective_start_date
3397 );
3398
3399 -- Cursor to get pensionable salary balance information
3400 CURSOR csr_get_pens_bal_id (c_element_type_id NUMBER)
3401 IS
3402 SELECT fnd_number.canonical_to_number (eei_information17)
3403 FROM pay_element_type_extra_info
3404 WHERE element_type_id = c_element_type_id
3405 AND information_type = 'PQP_GB_PENSION_SCHEME_INFO';
3406
3407 l_pensionable_sal_bal_id NUMBER;
3408
3409
3410 --
3411 --==============================================================================
3412 --|--------------------------< chk_scheme_name >-------------------------------|
3413 --==============================================================================
3414 PROCEDURE chk_scheme_name
3415 IS
3416 --
3417 CURSOR csr_chk_uniq_sch_name
3418 IS
3419 SELECT 'X'
3420 FROM DUAL
3421 WHERE EXISTS ( SELECT 1
3422 FROM pay_element_type_extra_info eei
3423 ,pay_element_types_f pet
3424 WHERE pet.element_type_id = eei.element_type_id
3425 AND pet.business_group_id =
3426 p_business_group_id
3427 AND eei.information_type =
3428 'PQP_GB_PENSION_SCHEME_INFO'
3429 AND UPPER (eei.eei_information1) =
3430 UPPER (p_pension_scheme_name)
3431 AND eei.eei_information12 IS NULL);
3432
3433 l_proc_name VARCHAR2 (80) := g_proc_name
3434 || 'chk_scheme_name';
3435 l_exists VARCHAR2 (1);
3436 --
3437 BEGIN
3438 --
3439 hr_utility.set_location ( 'Entering: '
3440 || l_proc_name, 10);
3441 --
3442
3443 OPEN csr_chk_uniq_sch_name;
3444 FETCH csr_chk_uniq_sch_name INTO l_exists;
3445
3446 IF csr_chk_uniq_sch_name%FOUND
3447 THEN
3448 CLOSE csr_chk_uniq_sch_name;
3449 fnd_message.set_name ('PQP', 'PQP_230924_SCHEME_NAME_ERR');
3450 hr_multi_message.add
3451 (p_associated_column1
3452 => 'PQP_GB_PENSION_SCHEMES_V.PENSION_SCHEME_NAME'
3453 );
3454 END IF; -- End if of csr uniq row found check ...
3455
3456 IF csr_chk_uniq_sch_name%ISOPEN THEN
3457 CLOSE csr_chk_uniq_sch_name;
3458 END IF; -- Cursor is open check ...
3459 --
3460 hr_utility.set_location ( 'Leaving: '
3461 || l_proc_name, 20);
3462 --
3463 END chk_scheme_name;
3464
3465 --
3466 --
3467 --==============================================================================
3468 --|------------------------------< Main Function >-----------------------------|
3469 --==============================================================================
3470
3471 BEGIN
3472 --
3473 hr_utility.set_location ( 'Entering : '
3474 || l_proc_name, 10);
3475 --
3476
3477 ---------------------
3478 -- Set session date
3479 ---------------------
3480
3481 pay_db_pay_setup.set_session_date (
3482 NVL (l_effective_start_date, SYSDATE)
3483 );
3484 --
3485 hr_utility.set_location (l_proc_name, 20);
3486
3487 --
3488
3489 IF (hr_utility.chk_product_install (
3490 'Oracle Payroll'
3491 ,g_template_leg_code
3492 )
3493 )
3494 THEN
3495 -- Check scheme name for its uniqueness
3496 chk_scheme_name;
3497 --
3498 hr_utility.set_location (l_proc_name, 25);
3499 -- Delete the pension type collection
3500 g_tab_pension_types_info.DELETE;
3501
3502
3503 --
3504
3505 -- Check employee deduction method
3506 IF p_emp_deduction_method = 'PEFR'
3507 THEN
3508 -- Set the same base name but pass percentage first
3509 --
3510 hr_utility.set_location (l_proc_name, 30);
3511 --
3512 l_element_type_id :=
3513 create_user_template_low (
3514 p_pension_scheme_name => p_pension_scheme_name
3515 ,p_pension_year_start_dt => p_pension_year_start_dt
3516 ,p_pension_category => p_pension_category
3517 ,p_pension_provider_id => p_pension_provider_id
3518 ,p_pension_type_id => p_pension_type_id
3519 ,p_emp_deduction_method => 'PEFR'
3520 ,p_ele_base_name => p_ele_base_name
3521 ,p_effective_start_date => l_effective_start_date
3522 ,p_ele_reporting_name => p_ele_reporting_name
3523 ,p_ele_classification_id => p_ele_classification_id
3524 ,p_business_group_id => p_business_group_id
3525 ,p_eer_deduction_method => p_eer_deduction_method
3526 ,p_scon_number => p_scon_number
3527 ,p_econ_number => p_econ_number -- BUG 4108320
3528 ,p_additional_contribution => p_additional_contribution
3529 ,p_added_years => p_added_years
3530 ,p_family_widower => p_family_widower
3531 ,p_fwc_added_years => p_fwc_added_years
3532 ,p_scheme_reference_no => p_scheme_reference_no
3533 ,p_employer_reference_no => p_employer_reference_no
3534 ,p_associated_ocp_ele_id => p_associated_ocp_ele_id
3535 ,p_ele_description => p_ele_description
3536 ,p_pension_scheme_type => p_pension_scheme_type
3537 ,p_pensionable_sal_bal_id => p_pensionable_sal_bal_id
3538 ,p_third_party_only_flag => p_third_party_only_flag
3539 ,p_iterative_processing => p_iterative_processing
3540 ,p_arrearage_allowed => p_arrearage_allowed
3541 ,p_partial_deduction => p_partial_deduction
3542 ,p_termination_rule => p_termination_rule
3543 ,p_standard_link => p_standard_link
3544 ,p_validate => TRUE
3545 );
3546 -- Get the pensionable salary balance if a new one is created above
3547 -- so that the same information is used for the fixed rate element
3548
3549 --
3550 hr_utility.set_location (l_proc_name, 35);
3551
3552 --
3553
3554 IF p_pensionable_sal_bal_id IS NULL
3555 THEN
3556 -- Get the balance id
3557 OPEN csr_get_pens_bal_id (l_element_type_id);
3558 FETCH csr_get_pens_bal_id INTO l_pensionable_sal_bal_id;
3559 CLOSE csr_get_pens_bal_id;
3560 ELSE -- pensionable_sal_bal_id entered
3561 l_pensionable_sal_bal_id := p_pensionable_sal_bal_id;
3562 END IF; -- End if of pensionable sal bal id null check ...
3563
3564 -- Change the base name and pass FR now
3565 --
3566 hr_utility.set_location (l_proc_name, 40);
3567 --
3568 l_element_type_id :=
3569 create_user_template_low (
3570 p_pension_scheme_name => p_pension_scheme_name
3571 ,p_pension_year_start_dt => p_pension_year_start_dt
3572 ,p_pension_category => p_pension_category
3573 ,p_pension_provider_id => p_pension_provider_id
3574 ,p_pension_type_id => p_pension_type_id
3575 ,p_emp_deduction_method => 'FR'
3576 ,p_ele_base_name => p_ele_base_name
3577 || ' Fixed'
3578 ,p_effective_start_date => l_effective_start_date
3579 ,p_ele_reporting_name => p_ele_reporting_name
3580 ,p_ele_classification_id => p_ele_classification_id
3581 ,p_business_group_id => p_business_group_id
3582 ,p_eer_deduction_method => NULL
3583 ,p_scon_number => p_scon_number
3584 ,p_econ_number => p_econ_number -- BUG 4108320
3585 ,p_additional_contribution => NULL
3586 ,p_added_years => NULL
3587 ,p_family_widower => NULL
3588 ,p_fwc_added_years => NULL
3589 ,p_scheme_reference_no => p_scheme_reference_no
3590 ,p_employer_reference_no => p_employer_reference_no
3591 ,p_associated_ocp_ele_id => p_associated_ocp_ele_id
3592 ,p_ele_description => p_ele_description
3593 ,p_pension_scheme_type => p_pension_scheme_type
3594 ,p_pensionable_sal_bal_id => l_pensionable_sal_bal_id
3595 ,p_third_party_only_flag => p_third_party_only_flag
3596 ,p_iterative_processing => p_iterative_processing
3597 ,p_arrearage_allowed => p_arrearage_allowed
3598 ,p_partial_deduction => p_partial_deduction
3599 ,p_termination_rule => p_termination_rule
3600 ,p_standard_link => p_standard_link
3601 ,p_validate => FALSE
3602 );
3603 ELSE -- not PEFR
3604 -- Call the low level function with the same parameters
3605 --
3606 hr_utility.set_location (l_proc_name, 50);
3607 --
3608 l_element_type_id :=
3609 create_user_template_low (
3610 p_pension_scheme_name => p_pension_scheme_name
3611 ,p_pension_year_start_dt => p_pension_year_start_dt
3612 ,p_pension_category => p_pension_category
3613 ,p_pension_provider_id => p_pension_provider_id
3614 ,p_pension_type_id => p_pension_type_id
3615 ,p_emp_deduction_method => p_emp_deduction_method
3616 ,p_ele_base_name => p_ele_base_name
3617 ,p_effective_start_date => l_effective_start_date
3618 ,p_ele_reporting_name => p_ele_reporting_name
3619 ,p_ele_classification_id => p_ele_classification_id
3620 ,p_business_group_id => p_business_group_id
3621 ,p_eer_deduction_method => p_eer_deduction_method
3622 ,p_scon_number => p_scon_number
3623 ,p_econ_number => p_econ_number -- BUG 4108320
3624 ,p_additional_contribution => p_additional_contribution
3625 ,p_added_years => p_added_years
3626 ,p_family_widower => p_family_widower
3627 ,p_fwc_added_years => p_fwc_added_years
3628 ,p_scheme_reference_no => p_scheme_reference_no
3629 ,p_employer_reference_no => p_employer_reference_no
3630 ,p_associated_ocp_ele_id => p_associated_ocp_ele_id
3631 ,p_ele_description => p_ele_description
3632 ,p_pension_scheme_type => p_pension_scheme_type
3633 ,p_pensionable_sal_bal_id => p_pensionable_sal_bal_id
3634 ,p_third_party_only_flag => p_third_party_only_flag
3635 ,p_iterative_processing => p_iterative_processing
3636 ,p_arrearage_allowed => p_arrearage_allowed
3637 ,p_partial_deduction => p_partial_deduction
3638 ,p_termination_rule => p_termination_rule
3639 ,p_standard_link => p_standard_link
3640 ,p_validate => TRUE
3641 );
3642 END IF; -- End if of emp deduction method = PEFR check ...
3643 ELSE
3644 hr_utility.set_message (8303, 'PQP_230535_GBORAPAY_NOT_FOUND');
3645 hr_utility.raise_error;
3646 END IF; -- IF chk_product_install('Oracle Payroll',g_template_leg_code))
3647
3648 --
3649 hr_utility.set_location ( 'Leaving: '
3650 || l_proc_name, 60);
3651 --
3652
3653 RETURN l_element_type_id;
3654 --
3655 END create_user_template;
3656
3657 --
3658
3659 /*========================================================================
3660 * CREATE_USER_TEMPLATE_SWI
3661 *=======================================================================*/
3662 FUNCTION create_user_template_swi (
3663 p_pension_scheme_name IN VARCHAR2
3664 ,p_pension_year_start_dt IN DATE
3665 ,p_pension_category IN VARCHAR2
3666 ,p_pension_provider_id IN NUMBER
3667 ,p_pension_type_id IN NUMBER
3668 ,p_emp_deduction_method IN VARCHAR2
3669 ,p_ele_base_name IN VARCHAR2
3670 ,p_effective_start_date IN DATE
3671 ,p_ele_reporting_name IN VARCHAR2
3672 ,p_ele_classification_id IN NUMBER
3673 ,p_business_group_id IN NUMBER
3674 ,p_eer_deduction_method IN VARCHAR2
3675 ,p_scon_number IN VARCHAR2
3676 ,p_econ_number IN VARCHAR2 -- BUG 4108320
3677 ,p_additional_contribution IN VARCHAR2
3678 ,p_added_years IN VARCHAR2
3679 ,p_family_widower IN VARCHAR2
3680 ,p_fwc_added_years IN VARCHAR2
3681 ,p_scheme_reference_no IN VARCHAR2
3682 ,p_employer_reference_no IN VARCHAR2
3683 ,p_associated_ocp_ele_id IN NUMBER
3684 ,p_ele_description IN VARCHAR2
3685 ,p_pension_scheme_type IN VARCHAR2
3686 ,p_pensionable_sal_bal_id IN NUMBER
3687 ,p_third_party_only_flag IN VARCHAR2
3688 ,p_iterative_processing IN VARCHAR2
3689 ,p_arrearage_allowed IN VARCHAR2
3690 ,p_partial_deduction IN VARCHAR2
3691 ,p_termination_rule IN VARCHAR2
3692 ,p_standard_link IN VARCHAR2
3693 )
3694 RETURN NUMBER
3695 IS
3696 --
3697 -- Variables for API Boolean parameters
3698 l_validate BOOLEAN;
3699 --
3700 -- Variables for IN/OUT parameters
3701 l_element_type_id NUMBER;
3702 --
3703 -- Other variables
3704 l_return_status VARCHAR2 (1);
3705 l_proc_name VARCHAR2 (80)
3706 := g_proc_name
3707 || 'create_user_template_swi';
3708 BEGIN
3709
3710 --*********************************
3711 -- AG IMP !!
3712 -- hr_utility.trace_on(NULL, 'ag_pension');
3713
3714 hr_utility.set_location('p_pension_scheme_name :' ||p_pension_scheme_name ,5);
3715 hr_utility.set_location('p_pension_year_start_dt :' ||p_pension_year_start_dt ,5);
3716 hr_utility.set_location(' p_pension_category :' ||p_pension_category ,5);
3717 hr_utility.set_location(' p_pension_provider_id :' ||p_pension_provider_id ,5);
3718 hr_utility.set_location(' p_pension_type_id :' ||p_pension_type_id ,5);
3719 hr_utility.set_location(' p_emp_deduction_method :' ||p_emp_deduction_method ,5);
3720 hr_utility.set_location(' p_ele_base_name :' ||p_ele_base_name ,5);
3721 hr_utility.set_location(' p_effective_start_date :' ||p_effective_start_date ,5);
3722 hr_utility.set_location(' p_ele_reporting_name :' ||p_ele_reporting_name ,5);
3723 hr_utility.set_location(' p_ele_classification_id :' ||p_ele_classification_id ,5);
3724 hr_utility.set_location(' p_business_group_id :' ||p_business_group_id ,5);
3725 hr_utility.set_location(' p_eer_deduction_method :' ||p_eer_deduction_method ,5);
3726 hr_utility.set_location(' p_scon_number :' ||p_scon_number ,5);
3727 hr_utility.set_location(' p_econ_number :' ||p_econ_number ,5);
3728 hr_utility.set_location(' p_additional_contribution:' ||p_additional_contribution ,5);
3729 hr_utility.set_location(' p_added_years :' ||p_added_years ,5);
3730 hr_utility.set_location(' p_family_widower :' ||p_family_widower ,5);
3731 hr_utility.set_location(' p_fwc_added_years :' ||p_fwc_added_years ,5);
3732 hr_utility.set_location(' p_scheme_reference_no :' ||p_scheme_reference_no ,5);
3733 hr_utility.set_location(' p_employer_reference_no :' ||p_employer_reference_no ,5);
3734 hr_utility.set_location(' p_associated_ocp_ele_id :' ||p_associated_ocp_ele_id ,5);
3735 hr_utility.set_location(' p_ele_description :' ||p_ele_description ,5);
3736 hr_utility.set_location(' p_pension_scheme_type :' ||p_pension_scheme_type ,5);
3737 hr_utility.set_location(' p_pensionable_sal_bal_id :' ||p_pensionable_sal_bal_id ,5);
3738 hr_utility.set_location(' p_third_party_only_flag :' ||p_third_party_only_flag ,5);
3739 hr_utility.set_location(' p_iterative_processing :' ||p_iterative_processing ,5);
3740 hr_utility.set_location(' p_arrearage_allowed :' ||p_arrearage_allowed ,5);
3741 hr_utility.set_location(' p_partial_deduction :' ||p_partial_deduction ,5);
3742 hr_utility.set_location(' p_termination_rule :' ||p_termination_rule ,5);
3743 hr_utility.set_location(' p_standard_link :' ||p_standard_link ,5);
3744
3745 --**********************************
3746
3747 hr_utility.set_location ( ' Entering:'
3748 || l_proc_name, 10);
3749 l_element_type_id := -1;
3750 --
3751 -- Issue a savepoint
3752 --
3753 SAVEPOINT create_user_template_swi;
3754 --
3755 -- Initialise Multiple Message Detection
3756 --
3757 hr_multi_message.enable_message_list;
3758 --
3759 -- Remember IN OUT parameter IN values
3760 --
3761 --
3762 -- Convert constant values to their corresponding boolean value
3763 --
3764 l_validate :=
3765 hr_api.constant_to_boolean (p_constant_value => hr_api.g_false_num);
3766 --
3767 -- Register Surrogate ID or user key values
3768 --
3769 --
3770 -- Call API
3771 --
3772 --
3773 hr_utility.set_location (l_proc_name, 20);
3774 --
3775 l_element_type_id :=
3776 create_user_template (
3777 p_pension_scheme_name => p_pension_scheme_name
3778 ,p_pension_year_start_dt => p_pension_year_start_dt
3779 ,p_pension_category => p_pension_category
3780 ,p_pension_provider_id => p_pension_provider_id
3781 ,p_pension_type_id => p_pension_type_id
3782 ,p_emp_deduction_method => p_emp_deduction_method
3783 ,p_ele_base_name => p_ele_base_name
3784 ,p_effective_start_date => p_effective_start_date
3785 ,p_ele_reporting_name => p_ele_reporting_name
3786 ,p_ele_classification_id => p_ele_classification_id
3787 ,p_business_group_id => p_business_group_id
3788 ,p_eer_deduction_method => p_eer_deduction_method
3789 ,p_scon_number => p_scon_number
3790 ,p_econ_number => p_econ_number
3791 ,p_additional_contribution => p_additional_contribution
3792 ,p_added_years => p_added_years
3793 ,p_family_widower => p_family_widower
3794 ,p_fwc_added_years => p_fwc_added_years
3795 ,p_scheme_reference_no => p_scheme_reference_no
3796 ,p_employer_reference_no => p_employer_reference_no
3797 ,p_associated_ocp_ele_id => p_associated_ocp_ele_id
3798 ,p_ele_description => p_ele_description
3799 ,p_pension_scheme_type => p_pension_scheme_type
3800 ,p_pensionable_sal_bal_id => p_pensionable_sal_bal_id
3801 ,p_third_party_only_flag => p_third_party_only_flag
3802 ,p_iterative_processing => p_iterative_processing
3803 ,p_arrearage_allowed => p_arrearage_allowed
3804 ,p_partial_deduction => p_partial_deduction
3805 ,p_termination_rule => p_termination_rule
3806 ,p_standard_link => p_standard_link
3807 );
3808
3809 hr_utility.set_location ( 'l_element_type_id: ' || l_element_type_id, 25);
3810 --
3811 -- Convert API warning boolean parameter values to specific
3812 -- messages and add them to Multiple Message List
3813 --
3814 --
3815 -- Convert API non-warning boolean parameter values
3816 --
3817 --
3818 -- Derive the API return status value based on whether
3819 -- messages of any type exist in the Multiple Message List.
3820 -- Also disable Multiple Message Detection.
3821 --
3822 l_return_status :=
3823 hr_multi_message.get_return_status_disable;
3824 hr_utility.set_location ( ' Leaving:'
3825 || l_proc_name, 30);
3826 RETURN l_element_type_id;
3827 --
3828 EXCEPTION
3829 WHEN hr_multi_message.error_message_exist
3830 THEN
3831 --
3832 -- Catch the Multiple Message List exception which
3833 -- indicates API processing has been aborted because
3834 -- at least one message exists in the list.
3835 --
3836 ROLLBACK TO create_user_template_swi;
3837 --
3838 -- Reset IN OUT parameters and set OUT parameters
3839 --
3840 RETURN l_element_type_id;
3841 hr_utility.set_location ( ' Leaving:'
3842 || l_proc_name, 40);
3843 WHEN OTHERS
3844 THEN
3845 --
3846 -- When Multiple Message Detection is enabled catch
3847 -- any Application specific or other unexpected
3848 -- exceptions. Adding appropriate details to the
3849 -- Multiple Message List. Otherwise re-raise the
3850 -- error.
3851 --
3852 ROLLBACK TO create_user_template_swi;
3853
3854 IF hr_multi_message.unexpected_error_add (l_proc_name)
3855 THEN
3856 hr_utility.set_location ( ' Leaving:'
3857 || l_proc_name, 50);
3858 RAISE;
3859 END IF;
3860
3861 --
3862 -- Reset IN OUT and set OUT parameters
3863 --
3864 l_return_status :=
3865 hr_multi_message.get_return_status_disable;
3866 RETURN l_element_type_id;
3867 hr_utility.set_location ( ' Leaving:'
3868 || l_proc_name, 60);
3869 END create_user_template_swi;
3870
3871
3872 --
3873 --
3874 --==========================================================================
3875 -- Deletion procedure
3876 --==========================================================================
3877 --
3878 PROCEDURE delete_user_template (
3879 p_business_group_id IN NUMBER
3880 ,p_ele_base_name IN VARCHAR2
3881 ,p_element_type_id IN NUMBER
3882 ,p_effective_date IN DATE
3883 )
3884 IS
3885 --
3886 l_template_id NUMBER;
3887 l_proc_name VARCHAR2 (72)
3888 := g_proc_name
3889 || 'delete_user_template';
3890 l_eei_info_id NUMBER;
3891 l_ovn_eei NUMBER;
3892 l_exists VARCHAR2 (1);
3893 l_element_type_id pay_element_types_f.element_type_id%TYPE;
3894
3895 -- Cursor to get template id
3896 CURSOR csr_get_template_id
3897 IS
3898 SELECT template_id
3899 FROM pay_element_templates
3900 WHERE base_name = p_ele_base_name
3901 AND template_name = g_template_name
3902 AND business_group_id = p_business_group_id
3903 AND template_type = 'U';
3904
3905 -- Cursor to retrieve core element type id for this
3906 -- template
3907 CURSOR csr_get_ele_type_id (c_template_id NUMBER)
3908 IS
3909 SELECT element_type_id
3910 FROM pay_template_core_objects pet, pay_element_types_f petf
3911 WHERE pet.template_id = c_template_id
3912 AND petf.element_type_id = pet.core_object_id
3913 AND pet.core_object_type = 'ET';
3914
3915 -- Cursor to retrieve element extra info for this
3916 -- element type id
3917 CURSOR csr_get_eei_info (c_element_type_id NUMBER)
3918 IS
3919 SELECT element_type_extra_info_id
3920 FROM pay_element_type_extra_info petei
3921 WHERE element_type_id = c_element_type_id;
3922
3923 -- Cursor to check whether an sub classification row
3924 -- exists for the base element type id
3925 CURSOR csr_chk_sub_class_exists
3926 IS
3927 SELECT sub.ROWID, sub.sub_classification_rule_id
3928 FROM pay_sub_classification_rules_f sub
3929 ,pay_element_classifications pec
3930 WHERE element_type_id = p_element_type_id
3931 AND sub.classification_id = pec.classification_id
3932 AND pec.classification_name IN
3933 ('Pre Tax Employee Pension COSR'
3934 ,'Pre Tax Employee Pension COMP'
3935 )
3936 AND pec.legislation_code = g_template_leg_code;
3937
3938 -- Cursor to check whether an iterative rule exists
3939 -- for this element type
3940 CURSOR csr_get_itr_info (c_element_type_id NUMBER)
3941 IS
3942 SELECT iterative_rule_id, object_version_number
3943 FROM pay_iterative_rules_f
3944 WHERE element_type_id = c_element_type_id;
3945
3946 -- Cursor to get pension category
3947 CURSOR csr_get_pens_cat
3948 IS
3949 SELECT eei_information4, eei_information6
3950 FROM pay_element_type_extra_info
3951 WHERE element_type_id = p_element_type_id
3952 AND information_type = 'PQP_GB_PENSION_SCHEME_INFO';
3953
3954 -- Cursor to check whether this is an OCP scheme
3955 -- and whether any AVC scheme uses this OCP scheme
3956 -- to ensure that we do not delete an OCP scheme
3957 -- attached to an AVC scheme
3958
3959 CURSOR csr_chk_ocp_asoc_avc
3960 IS
3961 SELECT 'X'
3962 FROM DUAL
3963 WHERE EXISTS ( SELECT 1
3964 FROM pqp_gb_pension_schemes_v
3965 WHERE ( associated_ocp_ele_type_id IS NOT NULL
3966 AND associated_ocp_ele_type_id =
3967 p_element_type_id
3968 )
3969 AND business_group_id = p_business_group_id);
3970
3971 -- Check whether a fixed rate emp deduction method exists
3972 -- for this pension scheme
3973 CURSOR csr_chk_fr_exists
3974 IS
3975 SELECT 'X'
3976 FROM DUAL
3977 WHERE EXISTS ( SELECT 1
3978 FROM pay_element_templates
3979 WHERE base_name = p_ele_base_name
3980 || ' Fixed'
3981 AND template_name = g_template_name
3982 AND business_group_id = p_business_group_id
3983 AND template_type = 'U');
3984
3985 l_sub_classification_rule_id NUMBER;
3986 l_rowid ROWID;
3987 l_ovn_itr NUMBER;
3988 l_itr_effective_start_dt DATE;
3989 l_itr_effective_end_dt DATE;
3990 l_pension_category hr_lookups.lookup_code%TYPE;
3991 l_emp_deduction_method hr_lookups.lookup_code%TYPE;
3992
3993 --
3994 BEGIN
3995 --
3996 hr_utility.set_location ( 'Entering :'
3997 || l_proc_name, 10);
3998 -- Get the pension category
3999 OPEN csr_get_pens_cat;
4000 FETCH csr_get_pens_cat INTO l_pension_category, l_emp_deduction_method;
4001 CLOSE csr_get_pens_cat;
4002
4003 IF l_emp_deduction_method = 'PE'
4004 THEN
4005 -- Check whether this pension scheme has
4006 -- an FR deduction method
4007 OPEN csr_chk_fr_exists;
4008 FETCH csr_chk_fr_exists INTO l_exists;
4009
4010 IF csr_chk_fr_exists%FOUND
4011 THEN
4012 CLOSE csr_chk_fr_exists;
4013 fnd_message.set_name ('PQP', 'PQP_230981_PEN_FR_DED_EXISTS');
4014 hr_multi_message.add
4015 (p_associated_column1
4016 => 'PQP_GB_PENSION_SCHEMES_V.EMPLOYEE_DEDUCTION_METHOD'
4017 );
4018 END IF; -- End if of fr deduction method exist check ...
4019
4020 IF csr_chk_fr_exists%ISOPEN THEN
4021 CLOSE csr_chk_fr_exists;
4022 END IF; -- Cursor is open check ...
4023 END IF; -- End if of emp deduction method is percentage check ...
4024
4025 IF l_pension_category = 'OCP'
4026 THEN
4027 -- Check whether any AVC uses this OCP
4028 OPEN csr_chk_ocp_asoc_avc;
4029 FETCH csr_chk_ocp_asoc_avc INTO l_exists;
4030
4031 IF csr_chk_ocp_asoc_avc%FOUND
4032 THEN
4033 CLOSE csr_chk_ocp_asoc_avc;
4034 fnd_message.set_name ('PQP', 'PQP_230945_PEN_AVC_CHILD_EXIST');
4035 hr_multi_message.add
4036 (p_associated_column2
4037 => 'PQP_GB_PENSION_SCHEMES_V.ELEMENT_TYPE_ID'
4038 );
4039 END IF; -- End if of ocp associated with AVC check ...
4040
4041 IF csr_chk_ocp_asoc_avc%ISOPEN THEN
4042 CLOSE csr_chk_ocp_asoc_avc;
4043 END IF; -- cursor is open check ...
4044 END IF; -- End if of pension category is OCP check ...
4045
4046 hr_multi_message.end_validation_set;
4047 --
4048
4049 FOR csr_get_template_id_rec IN csr_get_template_id
4050 LOOP
4051 l_template_id := csr_get_template_id_rec.template_id;
4052 END LOOP;
4053
4054 hr_utility.set_location (l_proc_name, 20);
4055 OPEN csr_get_ele_type_id (l_template_id);
4056
4057 LOOP
4058 FETCH csr_get_ele_type_id INTO l_element_type_id;
4059 EXIT WHEN csr_get_ele_type_id%NOTFOUND;
4060 -- Get Element extra info id for this element type id
4061
4062 OPEN csr_get_eei_info (l_element_type_id);
4063
4064 LOOP
4065 FETCH csr_get_eei_info INTO l_eei_info_id;
4066 EXIT WHEN csr_get_eei_info%NOTFOUND;
4067 -- Delete the EEI row
4068 --
4069 hr_utility.set_location (l_proc_name, 30);
4070 --
4071 pay_element_extra_info_api.delete_element_extra_info (
4072 p_validate => FALSE
4073 ,p_element_type_extra_info_id => l_eei_info_id
4074 ,p_object_version_number => l_ovn_eei
4075 );
4076 END LOOP; -- EEIT loop
4077
4078 CLOSE csr_get_eei_info;
4079 END LOOP; -- Element type id LOOP
4080
4081 CLOSE csr_get_ele_type_id;
4082 --
4083 hr_utility.set_location (l_proc_name, 40);
4084 --
4085
4086 -- Delete sub classification rules if one exist
4087 OPEN csr_chk_sub_class_exists;
4088 FETCH csr_chk_sub_class_exists INTO l_rowid, l_sub_classification_rule_id;
4089
4090 IF csr_chk_sub_class_exists%FOUND
4091 THEN
4092 -- Delete the sub classification row
4093 pay_sub_class_rules_pkg.delete_row (
4094 p_rowid => l_rowid
4095 ,p_sub_classification_rule_id => l_sub_classification_rule_id
4096 ,p_delete_mode => 'ZAP'
4097 ,p_validation_start_date => p_effective_date
4098 ,p_validation_end_date => p_effective_date
4099 );
4100 END IF; -- End if of sub class row exists check ...
4101
4102 CLOSE csr_chk_sub_class_exists;
4103 --
4104 hr_utility.set_location (l_proc_name, 50);
4105 --
4106
4107 pay_element_template_api.delete_user_structure (
4108 p_validate => FALSE
4109 ,p_drop_formula_packages => TRUE
4110 ,p_template_id => l_template_id
4111 );
4112 --
4113 hr_utility.set_location ( 'Leaving :'
4114 || l_proc_name, 60);
4115 --
4116
4117 END delete_user_template;
4118
4119
4120 -- ---------------------------------------------------------------------
4121 -- |--------------------< delete_user_template_swi >-------------------|
4122 -- ---------------------------------------------------------------------
4123
4124 PROCEDURE delete_user_template_swi (
4125 p_business_group_id IN NUMBER
4126 ,p_ele_base_name IN VARCHAR2
4127 ,p_element_type_id IN NUMBER
4128 ,p_effective_date IN DATE
4129 )
4130 IS
4131 --
4132 -- Variables for API Boolean parameters
4133 l_validate BOOLEAN;
4134 --
4135 -- Variables for IN/OUT parameters
4136 --
4137 -- Other variables
4138 l_return_status VARCHAR2 (1);
4139 l_proc_name VARCHAR2 (80)
4140 := g_proc_name
4141 || 'delete_user_template_swi';
4142 BEGIN
4143 hr_utility.set_location ( ' Entering:'
4144 || l_proc_name, 10);
4145 --
4146 -- Issue a savepoint
4147 --
4148 SAVEPOINT delete_user_template_swi;
4149 --
4150 -- Initialise Multiple Message Detection
4151 --
4152 hr_multi_message.enable_message_list;
4153 --
4154 -- Remember IN OUT parameter IN values
4155 --
4156 --
4157 -- Convert constant values to their corresponding boolean value
4158 --
4159 l_validate :=
4160 hr_api.constant_to_boolean (p_constant_value => hr_api.g_false_num);
4161 --
4162 -- Register Surrogate ID or user key values
4163 --
4164 --
4165 -- Call API
4166 --
4167 delete_user_template (
4168 p_business_group_id => p_business_group_id
4169 ,p_ele_base_name => p_ele_base_name
4170 ,p_element_type_id => p_element_type_id
4171 ,p_effective_date => p_effective_date
4172 );
4173 --
4174 -- Convert API warning boolean parameter values to specific
4175 -- messages and add them to Multiple Message List
4176 --
4177 --
4178 -- Convert API non-warning boolean parameter values
4179 --
4180 --
4181 -- Derive the API return status value based on whether
4182 -- messages of any type exist in the Multiple Message List.
4183 -- Also disable Multiple Message Detection.
4184 --
4185 l_return_status :=
4186 hr_multi_message.get_return_status_disable;
4187 hr_utility.set_location ( ' Leaving:'
4188 || l_proc_name, 20);
4189 --
4190 EXCEPTION
4191 WHEN hr_multi_message.error_message_exist
4192 THEN
4193 --
4194 -- Catch the Multiple Message List exception which
4195 -- indicates API processing has been aborted because
4196 -- at least one message exists in the list.
4197 --
4198 ROLLBACK TO delete_user_template_swi;
4199 --
4200 -- Reset IN OUT parameters and set OUT parameters
4201 --
4202 hr_utility.set_location ( ' Leaving:'
4203 || l_proc_name, 30);
4204 WHEN OTHERS
4205 THEN
4206 --
4207 -- When Multiple Message Detection is enabled catch
4208 -- any Application specific or other unexpected
4209 -- exceptions. Adding appropriate details to the
4210 -- Multiple Message List. Otherwise re-raise the
4211 -- error.
4212 --
4213 ROLLBACK TO delete_user_template_swi;
4214
4215 IF hr_multi_message.unexpected_error_add (l_proc_name)
4216 THEN
4217 hr_utility.set_location ( ' Leaving:'
4218 || l_proc_name, 40);
4219 RAISE;
4220 END IF;
4221
4222 --
4223 -- Reset IN OUT and set OUT parameters
4224 --
4225 l_return_status :=
4226 hr_multi_message.get_return_status_disable;
4227 hr_utility.set_location ( ' Leaving:'
4228 || l_proc_name, 50);
4229 END delete_user_template_swi;
4230 --
4231 END pqp_gb_pension_scheme_template;