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