[Home] [Help]
PACKAGE BODY: APPS.PAY_IE_PENSION_TEMPLATE
Source
1 PACKAGE BODY PAY_IE_PENSION_TEMPLATE As
2 /* $Header: pyiepend.pkb 120.5 2012/06/07 09:02:11 rsahai ship $ */
3
4 g_proc_name varchar2(80) := ' pay_ie_pension_template.';
5
6 -- ---------------------------------------------------------------------
7 -- |-----------------------< Compile_Formula >--------------------------|
8 -- ---------------------------------------------------------------------
9 procedure Compile_Formula
10 (p_element_type_id in number
11 ,p_effective_start_date in date
12 ,p_scheme_prefix in varchar2
13 ,p_business_group_id in number
14 ,p_request_id out nocopy number
15 ) is
16 -- --------------------------------------------------------
17 -- Cursor to get the formula details necessary to compile
18 -- --------------------------------------------------------
19 cursor csr_fra(c_element_type_id number) is
20 select
21 fra.formula_id,
22 fra.formula_name,
23 fty.formula_type_id,
24 fty.formula_type_name
25 from ff_formulas_f fra,
26 ff_formula_types fty,
27 pay_status_processing_rules_f spr
28 where fty.formula_type_id = fra.formula_type_id
29 and fra.formula_id = spr.formula_id
30 and spr.assignment_status_type_id is null
31 and spr.element_type_id = c_element_type_id
32 and p_effective_start_date between fra.effective_start_date
33 and fra.effective_end_date
34 and p_effective_start_date between spr.effective_start_date
35 and spr.effective_end_date;
36
37 l_request_id number;
38 l_er_request_id number;
39 l_proc_name Varchar2(80) := g_proc_name || 'compile_formula';
40 l_user_id number;
41 l_resp_id number;
42 begin
43 hr_utility.set_location('Entering: '||l_proc_name, 10);
44 -- ------------------------------------------------------------
45 -- Query formula info (ie. the formula attached to this
46 -- element's Standard status proc rule.
47 -- ------------------------------------------------------------
48 for fra_rec in csr_fra (c_element_type_id => p_element_type_id)
49 loop
50 hr_utility.set_location('..FF Name :'||fra_rec.formula_name,15);
51 hr_utility.set_location('..FF Type Name :'||fra_rec.formula_type_name,20);
52 -- ----------------------------------------------
53 -- Submit the request to compile the formula
54 -- ----------------------------------------------
55 --fnd_profile.get('USER_ID', l_user_id);
56 --fnd_profile.get('RESP_ID', l_resp_id);
57 --hr_utility.set_location('..User Id :'||l_user_id, 25);
58 --hr_utility.set_location('..Responsibility Id :'||l_resp_id, 25);
59 --fnd_global.apps_initialize(l_user_id,l_resp_id,800);
60
61 --13843294
62 /* l_request_id := fnd_request.submit_request
63 (application => 'FF'
64 ,program => 'SINGLECOMPILE'
65 ,argument1 => fra_rec.formula_type_name --Oracle Payroll
66 ,argument2 => fra_rec.formula_name); --formula name
67 */
68
69 l_request_id := ff_formula_webui_pkg.submit_singlecompile
70 (p_formula_type => fra_rec.formula_type_name --Oracle Payroll
71 ,p_formula_name => fra_rec.formula_name); --formula name
72
73 --13843294
74 p_request_id := l_request_id;
75 hr_utility.set_location('..Request Id :'||p_request_id, 25);
76 end loop;
77 hr_utility.set_location('Leaving: '||l_proc_name, 30);
78 exception
79 when others then
80 hr_utility.set_location('..Entering exception when others ', 80);
81 hr_utility.set_location('Leaving: '||l_proc_name, 90);
82 p_request_id := null; raise;
83 end Compile_Formula;
84
85 -- ----------------------------------------------------------------------------
86 -- |------------------------< chk_scheme_prefix >-----------------------------|
87 -- ----------------------------------------------------------------------------
88 Procedure chk_scheme_prefix
89 (p_scheme_prefix_in in varchar2
90 ) IS
91
92 element_name varchar2(100) := p_scheme_prefix_in;
93 l_output varchar2(100);
94 l_rgeflg varchar2(100);
95
96 begin
97
98 hr_chkfmt.checkformat
99 (
100 value => element_name,
101 format => 'PAY_NAME',
102 output => l_output,
103 minimum => NULL,
104 maximum => NULL,
105 nullok => 'N',
106 rgeflg => l_rgeflg,
107 curcode => NULL
108 );
109
110 EXCEPTION
111
112 WHEN OTHERS THEN
113 fnd_message.set_name('PQP', 'PQP_230923_SCHEME_PREFIX_ERR');
114 fnd_message.raise_error;
115
116 END chk_scheme_prefix;
117
118 -- ---------------------------------------------------------------------
119 -- |------------------------< Get_Object_ID >--------------------------|
120 -- ---------------------------------------------------------------------
121 function Get_Object_ID (p_object_type in Varchar2,
122 p_object_name in Varchar2,
123 p_business_group_id in Number,
124 p_template_id in Number)
125 return Number is
126 --
127 l_object_id Number := null;
128 l_proc_name Varchar2(72) := g_proc_name || 'get_object_id';
129 --
130 cursor c2 (c_object_name varchar2) is
131 select element_type_id
132 from pay_element_types_f
133 where element_name = c_object_name
134 and business_group_id = p_business_group_id;
135 --
136 cursor c3 (c_object_name in Varchar2) is
137 select ptco.core_object_id
138 from pay_shadow_balance_types psbt,
139 pay_template_core_objects ptco
140 where psbt.template_id = p_template_id
141 and psbt.balance_name = c_object_name
142 and ptco.template_id = psbt.template_id
143 and ptco.shadow_object_id = psbt.balance_type_id;
144 --
145 begin
146 hr_utility.set_location('Entering: '||l_proc_name, 10);
147 --
148 if p_object_type = 'ELE' then
149 for c2_rec in c2 (p_object_name) loop
150 l_object_id := c2_rec.element_type_id; -- element id
151 end loop;
152 elsif p_object_type = 'BAL' then
153 for c3_rec in c3 (p_object_name) loop
154 l_object_id := c3_rec.core_object_id; -- balance id
155 end loop;
156 end if;
157 --
158 hr_utility.set_location('Leaving: '||l_proc_name, 20);
159 --
160 return l_object_id;
161 end Get_Object_ID;
162
163 -- ---------------------------------------------------------------------
164 -- |------------------------< Get_Formula_Id >--------------------------|
165 -- ---------------------------------------------------------------------
166 function Get_Formula_Id (p_formula_name IN VARCHAR2
167 ,p_business_group_id IN NUMBER)
168 return Number is
169
170 cursor csr_get_formula_id is
171 select formula_id
172 from pay_shadow_formulas
173 where formula_name = p_formula_name
174 and business_group_id = p_business_group_id
175 and template_type = 'U';
176
177 l_proc_name Varchar2(72) := g_proc_name || 'get_formula_id';
178 l_formula_id Number;
179 begin
180 --
181 hr_utility.set_location ('Entering '||l_proc_name, 10);
182 --
183 open csr_get_formula_id;
184 fetch csr_get_formula_id into l_formula_id;
185 close csr_get_formula_id;
186 --
187 hr_utility.set_location ('Leaving '||l_proc_name, 20);
188 --
189 return l_formula_id;
190 end Get_Formula_ID;
191
192 -- ---------------------------------------------------------------------
193 -- |---------------------< Create_Formula_Results >--------------------|
194 -- ---------------------------------------------------------------------
195 PROCEDURE Create_Formula_Results (p_scheme_prefix IN VARCHAR2
196 ,p_pension_category IN VARCHAR2
197 ,p_business_group_id IN NUMBER
198 ,p_scheme_start_date IN DATE
199 ,p_scheme_end_date IN DATE)
200 IS
201 --This procedure creates the formula result rules for the
202 --Employer Contribution element in the case of PRSA and RAC Contributions
203 --for feeding the seeded BIK balances.
204
205 l_er_formula_id NUMBER;
206 l_rowid ROWID;
207 l_er_status_proc_rule_id NUMBER;
208 l_bik_er_ele_id NUMBER;
209 l_er_ele_id NUMBER;
210 l_bik_er_iv NUMBER;
211 l_formula_result_rule_id NUMBER;
212
213 CURSOR c_formula_id (c_name IN VARCHAR2)
214 IS
215 SELECT formula_id
216 FROM ff_formulas_f
217 WHERE formula_name=upper(c_name)
218 AND business_group_id=p_business_group_id
219 AND trunc(p_scheme_start_date) BETWEEN
220 effective_start_date AND effective_end_date;
221
222
223 CURSOR c_ele_id (c_name IN VARCHAR2)
224 IS
225 SELECT element_type_id
226 FROM pay_element_types_f
227 WHERE element_name = c_name
228 AND trunc(p_scheme_start_date) BETWEEN
229 effective_start_date AND effective_end_date;
230
231 CURSOR c_ip_val
232 ( c_element_type_id IN NUMBER
233 ,c_name IN VARCHAR2) IS
234 SELECT input_value_id
235 FROM pay_input_values_f
236 WHERE element_type_id = c_element_type_id
237 AND trunc(p_scheme_start_date) BETWEEN
238 effective_start_date AND effective_end_date
239 AND name = c_name;
240
241 CURSOR c_status_proc_id
242 ( c_element_type_id IN NUMBER
243 ,c_formula_id IN NUMBER ) IS
244 SELECT status_processing_rule_id
245 FROM pay_status_processing_rules_f
246 WHERE element_type_id = c_element_type_id
247 AND formula_id = c_formula_id
248 AND trunc(p_scheme_start_date) BETWEEN
249 effective_start_date AND effective_end_date;
250
251 BEGIN
252
253 hr_utility.set_location('..In Create_Formula_Results', 51);
254
255 OPEN c_formula_id (replace(p_scheme_prefix,' ','_')||'_ER_CONTRIBUTION');
256 FETCH c_formula_id INTO l_er_formula_id;
257 IF c_formula_id%NOTFOUND THEN
258 CLOSE c_formula_id;
259 fnd_message.raise_error;
260 ELSE
261 CLOSE c_formula_id;
262 END IF;
263 hr_utility.set_location('..Fetched Formula ID', 51);
264 OPEN c_ele_id (p_scheme_prefix||' ER Contribution');
265 FETCH c_ele_id INTO l_er_ele_id;
266 IF c_ele_id%NOTFOUND THEN
267 CLOSE c_ele_id;
268 fnd_message.raise_error;
269 ELSE
270 CLOSE c_ele_id;
271 END IF;
272 hr_utility.set_location('..Fetched Element ID', 51);
273 OPEN c_status_proc_id (l_er_ele_id, l_er_formula_id);
274 FETCH c_status_proc_id INTO l_er_status_proc_rule_id;
275 IF c_status_proc_id%NOTFOUND THEN
276 CLOSE c_status_proc_id;
277 fnd_message.raise_error;
278 ELSE
279 CLOSE c_status_proc_id;
280 END IF;
281
282 hr_utility.set_location('..Creating Formula Result Rules for BIK', 51);
283 IF p_pension_category='PRSA' THEN
284 OPEN c_ele_id ('IE BIK PRSA ER Contribution');
285 FETCH c_ele_id INTO l_bik_er_ele_id;
286 IF c_ele_id%NOTFOUND THEN
287 CLOSE c_ele_id;
288 fnd_message.raise_error;
289 ELSE
290 CLOSE c_ele_id;
291 END IF;
292
293 OPEN c_ip_val (l_bik_er_ele_id, 'Contribution Amount');
294 FETCH c_ip_val INTO l_bik_er_iv;
295 IF c_ip_val%NOTFOUND THEN
296 CLOSE c_ip_val;
297 fnd_message.raise_error;
298 ELSE
299 CLOSE c_ip_val;
300 END IF;
301
302 SELECT pay_formula_result_rules_s.nextval
303 INTO l_formula_result_rule_id
304 FROM dual;
305 pay_formula_result_rules_pkg.insert_row
306 (p_rowid => l_rowid
307 ,p_formula_result_rule_id => l_formula_result_rule_id
308 ,p_effective_start_date => trunc(p_scheme_start_date)
309 ,p_effective_end_date => trunc(p_scheme_end_date)
310 ,p_business_group_id => p_business_group_id
311 ,p_legislation_code => NULL
312 ,p_element_type_id => l_bik_er_ele_id
313 ,p_status_processing_rule_id => l_er_status_proc_rule_id
314 ,p_result_name => 'DEDUCTION_AMT'
315 ,p_result_rule_type => 'I'
316 ,p_legislation_subgroup => NULL
317 ,p_severity_level => NULL
318 ,p_input_value_id => l_bik_er_iv
319 ,p_session_date => p_scheme_start_date
320 ,p_created_by => -1
321 );
322 END IF;
323
324 IF p_pension_category='RAC' THEN
325 OPEN c_ele_id ('IE BIK RAC ER Contribution');
326 FETCH c_ele_id INTO l_bik_er_ele_id;
327 IF c_ele_id%NOTFOUND THEN
328 CLOSE c_ele_id;
329 fnd_message.raise_error;
330 ELSE
331 CLOSE c_ele_id;
332 END IF;
333 OPEN c_ip_val (l_bik_er_ele_id, 'Contribution Amount');
334 FETCH c_ip_val INTO l_bik_er_iv;
335 IF c_ip_val%NOTFOUND THEN
336 CLOSE c_ip_val;
337 fnd_message.raise_error;
338 ELSE
339 CLOSE c_ip_val;
340 END IF;
341
342 SELECT pay_formula_result_rules_s.nextval
343 INTO l_formula_result_rule_id
344 FROM dual;
345 pay_formula_result_rules_pkg.insert_row
346 (p_rowid => l_rowid
347 ,p_formula_result_rule_id => l_formula_result_rule_id
348 ,p_effective_start_date => trunc(p_scheme_start_date)
349 ,p_effective_end_date => trunc(p_scheme_end_date)
350 ,p_business_group_id => p_business_group_id
351 ,p_legislation_code => NULL
352 ,p_element_type_id => l_bik_er_ele_id
353 ,p_status_processing_rule_id => l_er_status_proc_rule_id
354 ,p_result_name => 'DEDUCTION_AMT'
355 ,p_result_rule_type => 'I'
356 ,p_legislation_subgroup => NULL
357 ,p_severity_level => NULL
358 ,p_input_value_id => l_bik_er_iv
359 ,p_session_date => p_scheme_start_date
360 ,p_created_by => -1
361 );
362 END IF;
363
364 END Create_Formula_Results;
365
366 -- ---------------------------------------------------------------------
367 -- |---------------------< Update_Ipval_Defval >------------------------|
368 -- ---------------------------------------------------------------------
369 procedure Update_Ipval_Defval(p_ele_name in Varchar2
370 ,p_ip_name in Varchar2
371 ,p_def_value in Varchar2
372 ,p_business_group_id IN Number
373 )
374 is
375
376 cursor csr_getinput(c_ele_name varchar2
377 ,c_iv_name varchar2) is
378 select input_value_id
379 ,piv.name
380 ,piv.element_type_id
381 from pay_input_values_f piv
382 ,pay_element_types_f pet
383 where element_name = c_ele_name
384 and piv.element_type_id = pet.element_type_id
385 and (piv.business_group_id = p_business_group_id or
386 piv.business_group_id is null)
387 and piv.name = c_iv_name
388 and (piv.legislation_code = 'IE' or
389 piv.legislation_code is null);
390
391 cursor csr_updinput(c_ip_id number
392 ,c_element_type_id number) is
393 select rowid
394 from pay_input_values_f
395 where input_value_id = c_ip_id
396 and element_type_id = c_element_type_id
397 for update nowait;
398
399 csr_getinput_rec csr_getinput%rowtype;
400 csr_updinput_rec csr_updinput%rowtype;
401
402 l_proc_name Varchar2(72) := g_proc_name ||
403 'update_ipval_defval';
404 --
405 begin
406 --
407 hr_utility.set_location ('Entering '||l_proc_name, 10);
408 --
409 open csr_getinput(p_ele_name ,p_ip_name);
410 loop
411 fetch csr_getinput into csr_getinput_rec;
412 exit when csr_getinput%notfound;
413 --
414 hr_utility.set_location (l_proc_name, 20);
415 --
416 open csr_updinput(csr_getinput_rec.input_value_id
417 ,csr_getinput_rec.element_type_id);
418 loop
419 fetch csr_updinput into csr_updinput_rec;
420 exit when csr_updinput%notfound;
421 --
422 hr_utility.set_location (l_proc_name, 30);
423 --
424 update pay_input_values_f
425 set default_value = p_def_value
426 where rowid = csr_updinput_rec.rowid;
427 end loop;
428 close csr_updinput;
429 end loop;
430 close csr_getinput;
431 --
432 hr_utility.set_location ('Leaving '||l_proc_name, 40);
433 --
434 end Update_Ipval_Defval;
435
436 -- ---------------------------------------------------------------------
437 -- |--------------------< Create_User_Template >------------------------|
438 -- ---------------------------------------------------------------------
439 function Create_User_Template (
440 p_pension_provider_id In Number
441 ,p_pension_type_id In Number
442 ,p_scheme_prefix In Varchar2
443 ,p_reporting_name In Varchar2
444 ,p_prsa2_certificate In Varchar2
445 ,p_third_party In Varchar2
446 ,p_termination_rule In Varchar2
447 ,p_effective_start_date In Date Default Null
448 ,p_effective_end_date In Date Default Null
449 ,p_security_group_id In Number Default Null
450 ,p_business_group_id In Number
451 )
452 return Number is
453 --
454 l_template_id pay_shadow_element_types.template_id%type;
455 l_base_element_type_id pay_template_core_objects.core_object_id%type;
456 l_setup_element_type_id pay_template_core_objects.core_object_id%type;
457 l_er_base_element_type_id pay_template_core_objects.core_object_id%type;
458 l_ee_tax_element_type_id pay_template_core_objects.core_object_id%type;
459 l_source_template_id pay_element_templates.template_id%type;
460 l_object_version_number pay_element_types_f.object_version_number%type;
461 l_proc_name Varchar2(80) := g_proc_name || 'create_user_template';
462 l_element_type_id Number;
463 l_balance_type_id Number;
464 l_eei_element_type_id Number;
465 l_ele_obj_ver_number Number;
466 l_bal_obj_ver_number Number;
467 i Number;
468 l_eei_info_id Number;
469 l_ovn_eei Number;
470 l_formula_name pay_shadow_formulas.formula_name%type;
471 l_formula_id Number;
472 l_formula_id1 Number;
473 y Number := 0;
474 l_exists Varchar2(1);
475 l_count Number := 0;
476 l_shad_formula_id Number;
477 l_shad_formula_id1 Number;
478 l_prem_replace_string varchar2(5000) := ' ' ;
479 l_std_link_flag varchar2(10) := 'N';
480 l_scheme_prefix varchar2(50) := p_scheme_prefix;
481 l_pension_category varchar2(30);
482 l_seed_ee_bal_type_id Number;
483 l_seed_er_bal_type_id Number;
484 l_seed_ee_tax_bal_type_id Number;
485 l_seed_arrear_type_id Number;
486 l_bal_name1 varchar2(80);
487 l_bal_name2 varchar2(80);
488 l_bal_name3 varchar2(80);
489 l_bal_name4 varchar2(80);
490 l_configuration_information1 Varchar2(10) := 'N';
491
492 type shadow_ele_rec is record
493 (element_type_id pay_shadow_element_types.element_type_id%type
494 ,object_version_number pay_shadow_element_types.object_version_number%type
495 ,reporting_name pay_shadow_element_types.reporting_name%type
496 ,description pay_shadow_element_types.description%type
497 ,priority pay_shadow_element_types.relative_processing_priority%type
498 ,third_party_pay_only_flag pay_shadow_element_types.third_party_pay_only_flag%type
499 ,classification_name pay_shadow_element_types.classification_name%type
500 );
501 type t_shadow_ele_info is table of shadow_ele_rec
502 index by Binary_Integer;
503 l_shadow_element t_shadow_ele_info;
504
505 type t_ele_name is table of pay_element_types_f.element_name%type
506 index by BINARY_INTEGER;
507 l_ele_name t_ele_name;
508 l_ele_new_name t_ele_name;
509 l_main_ele_name t_ele_name;
510 l_retro_ele_name t_ele_name;
511
512 type t_bal_name is table of pay_balance_types.balance_name%type
513 index by BINARY_INTEGER;
514 l_bal_name t_bal_name;
515 l_bal_new_name t_bal_name;
516
517 type t_ele_reporting_name is table of pay_element_types_f.reporting_name%type
518 index by BINARY_INTEGER;
519 l_ele_reporting_name t_ele_reporting_name;
520
521 type t_ele_description is table of pay_element_types_f.description%type
522 index by BINARY_INTEGER;
523 l_ele_description t_ele_description;
524
525 type t_ele_pp is table of pay_element_types_f.processing_priority%type
526 index by BINARY_INTEGER;
527 l_ele_pp t_ele_pp;
528
529 type t_eei_info is table of pay_element_type_extra_info.eei_information1%type
530 index by BINARY_INTEGER;
531 l_main_eei_info1 t_eei_info;
532 l_retro_eei_info1 t_eei_info;
533
534 l_ele_core_id pay_template_core_objects.core_object_id%type:= -1;
535
536 -- Extra Information variables
537 l_eei_information1 pay_element_type_extra_info.eei_information1%type;
538 l_eei_information2 pay_element_type_extra_info.eei_information2%type;
539 l_ee_contribution_bal_type_id pqp_pension_types_f.ee_contribution_bal_type_id%type;
540 l_er_contribution_bal_type_id pqp_pension_types_f.er_contribution_bal_type_id%type;
541 l_balance_feed_Id pay_balance_feeds_f.balance_feed_id%type;
542 l_row_id rowid;
543 l_request_id Number;
544 l_er_request_id Number;
545 l_dbi_user_name ff_database_items.user_name%TYPE;
546 l_balance_name pay_balance_types.balance_name%TYPE;
547 l_balance_dbi_name ff_database_items.user_name%TYPE;
548
549 --
550 cursor csr_get_category (c_pen_type_id number,
551 c_effective_date date) is
552 select pension_category
553 from pqp_pension_types_f
554 where pension_type_id = c_pen_type_id
555 and c_effective_date between effective_start_date and effective_end_date;
556
557 cursor csr_get_ee_bal_info (c_bal_name varchar2) is
558 select balance_type_id
559 from pay_balance_types
560 where balance_name = c_bal_name
561 and legislation_code='IE'
562 and business_group_id is null;
563
564 cursor csr_get_ele_info (c_ele_name varchar2) is
565 select element_type_id
566 ,object_version_number
567 from pay_shadow_element_types
568 where template_id = l_template_id
569 and element_name = c_ele_name;
570 --
571 cursor csr_get_bal_info (c_bal_name varchar2) is
572 select balance_type_id
573 ,object_version_number
574 from pay_shadow_balance_types
575 where template_id = l_template_id
576 and balance_name = c_bal_name;
577 --
578 cursor csr_shd_ele (c_shd_elename varchar2) is
579 select element_type_id, object_version_number, relative_processing_priority, third_party_pay_only_flag, classification_name
580 from pay_shadow_element_types
581 where template_id = l_template_id
582 and element_name = c_shd_elename;
583 --
584 cursor csr_ipv (c_ele_typeid number
585 ,c_effective_date date) is
586 select input_value_id
587 from pay_input_values_f
588 where element_type_id = c_ele_typeid
589 and business_group_id = p_business_group_id
590 and name = 'Pay Value'
591 and c_effective_date between effective_start_date
592 and effective_end_date;
593 --
594 cursor csr_ipv1 (c_ele_typeid number
595 ,c_ipv_name varchar2
596 ,c_effective_date date) is
597 select input_value_id
598 from pay_input_values_f
599 where element_type_id = c_ele_typeid
600 and business_group_id = p_business_group_id
601 and name = c_ipv_name
602 and c_effective_date between effective_start_date
603 and effective_end_date;
604 --
605 cursor csr_pty (c_pension_type_id number
606 ,c_effective_date date) is
607 select ee_contribution_bal_type_id
608 from pqp_pension_types_f
609 where pension_type_id = c_pension_type_id
610 and business_group_id = p_business_group_id
611 and c_effective_date between effective_start_date
612 and effective_end_date;
613
614 cursor csr_pty1 (c_pension_type_id number
615 ,c_effective_date date) is
616 select *
617 from pqp_pension_types_f
618 where pension_type_id = c_pension_type_id
619 and business_group_id = p_business_group_id
620 and c_effective_date between effective_start_date
621 and effective_end_date;
622
623 cursor csr_pty2 (c_pension_type_id number
624 ,c_effective_date date) is
625 select er_contribution_bal_type_id
626 from pqp_pension_types_f
627 where pension_type_id = c_pension_type_id
628 and business_group_id = p_business_group_id
629 and c_effective_date between effective_start_date
630 and effective_end_date;
631
632 r_pty_rec pqp_pension_types_f%ROWTYPE;
633
634
635 l_scheme_dummy varchar2(10);
636 -- ---------------------------------------------------------------------
637 -- |------------------------< Get_Template_ID >-------------------------|
638 -- ---------------------------------------------------------------------
639 function Get_Template_ID (p_legislation_code in Varchar2)
640 return Number is
641 --
642 l_template_name Varchar2(80);
643 l_proc_name Varchar2(72) := g_proc_name || 'get_template_id';
644 --
645 cursor csr_get_temp_id is
646 select template_id
647 from pay_element_templates
648 where template_name = l_template_name
649 and legislation_code = p_legislation_code
650 and template_type = 'T'
651 and business_group_id is null;
652
653 --
654 begin
655 --
656 hr_utility.set_location('Entering: '||l_proc_name, 10);
657 --
658 l_template_name := 'Ireland Pension Deduction';
659 --
660 hr_utility.set_location(l_proc_name, 20);
661 --
662 for csr_get_temp_id_rec in csr_get_temp_id loop
663 l_template_id := csr_get_temp_id_rec.template_id;
664 end loop;
665 --
666 hr_utility.set_location('Leaving: '||l_proc_name, 30);
667 hr_utility.set_location('Template_id: '||l_template_id , 30);
668 --
669 return l_template_id;
670 --
671 end Get_Template_ID;
672
673 begin
674 --hr_utility.trace_on('Y', 'PENSIONIE');
675 -- ---------------------------------------------------------------------
676 -- |-------------< Main Function : Create_User_Template Body >----------|
677 -- ---------------------------------------------------------------------
678 hr_utility.set_location('Entering : '||l_proc_name, 10);
679
680 chk_scheme_prefix(p_scheme_prefix);
681
682 -- ---------------------------------------------------------------------
683 -- Set session date
684 -- ---------------------------------------------------------------------
685 pay_db_pay_setup.set_session_date(nvl(p_effective_start_date, sysdate));
686 --
687 hr_utility.set_location('..Setting the Session Date', 15);
688 -- ---------------------------------------------------------------------
689 -- Get Source Template ID
690 -- ---------------------------------------------------------------------
691 l_source_template_id := get_template_id
692 (p_legislation_code => g_template_leg_code);
693 OPEN csr_get_category (p_pension_type_id, p_effective_start_date);
694 FETCH csr_get_category INTO l_pension_category;
695 IF csr_get_category%NOTFOUND THEN
696 fnd_message.set_name('PQP', 'PQP_230805_INV_PENSIONID');
697 fnd_message.raise_error;
698 END IF;
699 CLOSE csr_get_category;
700 -- ---------------------------------------------------------------------
701 -- Exclusion rules
702 -- ---------------------------------------------------------------------
703 hr_utility.set_location('..Checking all the Exclusion Rules', 20);
704
705 -- Define the exclusion_rule for Employer Component
706 IF l_pension_category <> 'RBSAVC' AND l_pension_category <> 'PRSAAVC' THEN
707 l_configuration_information1 := 'Y';
708 ELSE
709 l_configuration_information1 := 'N';
710 END IF;
711
712 OPEN csr_pty1 (c_pension_type_id => p_pension_type_id
713 ,c_effective_date => p_effective_start_date);
714 FETCH csr_pty1 INTO r_pty_rec;
715
716 IF csr_pty1%notfound THEN
717 fnd_message.set_name('PQP', 'PQP_230805_INV_PENSIONID');
718 fnd_message.raise_error;
719 CLOSE csr_pty1;
720 ELSE
721
722 -- ---------------------------------------------------------------------
723 -- Create user structure from the template
724 -- ---------------------------------------------------------------------
725 hr_utility.set_location('..Creating template User structure', 25);
726 pay_element_template_api.create_user_structure
727 (p_validate => false
728 ,p_effective_date => p_effective_start_date
729 ,p_business_group_id => p_business_group_id
730 ,p_source_template_id => l_source_template_id
731 ,p_base_name => p_scheme_prefix
732 ,p_configuration_information1 => l_configuration_information1
733 ,p_template_id => l_template_id
734 ,p_object_version_number => l_object_version_number
735 );
736 hr_utility.set_location('..Created template User structure', 25);
737 -- ---------------------------------------------------------------------
738 -- |-------------------< Update Shadow Structure >----------------------|
739 -- ---------------------------------------------------------------------
740 -- Get Element Type id and update user-specified Classification,
741 -- Category, Processing Type and Standard Link on Base Element
742 -- as well as other element created for the Scheme
743 -- ---------------------------------------------------------------------
744 -- 1. <BASE NAME> Pension Deduction
745 for csr_rec in csr_shd_ele (p_scheme_prefix||' Pension Deduction')
746 loop
747 l_count := l_count + 1;
748 l_shadow_element(l_count).element_type_id := csr_rec.element_type_id;
749 l_shadow_element(l_count).object_version_number := csr_rec.object_version_number;
750 l_shadow_element(l_count).reporting_name := nvl(p_reporting_name,p_scheme_prefix||' Pension Deduction');
751 l_shadow_element(l_count).description := 'Element for '||p_scheme_prefix
752 ||' Pension Deduction';
753 l_shadow_element(l_count).third_party_pay_only_flag := p_third_party;
754 l_shadow_element(l_count).classification_name := csr_rec.classification_name;
755 if l_pension_category = 'RBS' then
756 l_shadow_element(l_count).priority := 8300;
757 end if;
758 if l_pension_category = 'RBSAVC' then
759 l_shadow_element(l_count).priority := 8301;
760 end if;
761 if l_pension_category = 'PRSA' then
762 l_shadow_element(l_count).priority := 8302;
763 end if;
764 if l_pension_category = 'PRSAAVC' then
765 l_shadow_element(l_count).priority := 8303;
766 end if;
767 if l_pension_category = 'RAC' then
768 l_shadow_element(l_count).priority := 8304;
769 end if;
770 end loop;
771
772 IF l_configuration_information1 = 'Y' THEN
773 -- 2. <BASE NAME> Employer Contribution
774 for csr_rec in csr_shd_ele (p_scheme_prefix||' ER Contribution')
775 loop
776 l_count := l_count + 1;
777 l_shadow_element(l_count).element_type_id := csr_rec.element_type_id;
778 l_shadow_element(l_count).object_version_number := csr_rec.object_version_number;
779 l_shadow_element(l_count).reporting_name := nvl(p_reporting_name,p_scheme_prefix || ' Employer Contribution');
780 l_shadow_element(l_count).description := 'Element for '||p_scheme_prefix
781 ||' Employer Contribution';
782 if l_pension_category <> 'PRSA' and l_pension_category <> 'RAC' then
783 l_shadow_element(l_count).classification_name := csr_rec.classification_name;
784 l_shadow_element(l_count).priority := csr_rec.relative_processing_priority;
785 else
786 l_shadow_element(l_count).classification_name := 'Information';
787 l_shadow_element(l_count).priority := 500;
788 end if;
789 l_shadow_element(l_count).third_party_pay_only_flag := p_third_party;
790 end loop;
791 END IF;
792 -- 3. <BASE NAME> EE Taxable Contribution
793 for csr_rec in csr_shd_ele (p_scheme_prefix||' EE Taxable Contribution')
794 loop
795 l_count := l_count +1;
796 l_shadow_element(l_count).element_type_id := csr_rec.element_type_id;
797 l_shadow_element(l_count).object_version_number := csr_rec.object_version_number;
798 l_shadow_element(l_count).reporting_name := nvl(p_reporting_name,p_scheme_prefix || ' EE Taxable Contribution');
799 l_shadow_element(l_count).description := 'Element for '||p_scheme_prefix
800 ||' EE Taxable Contribution';
801 l_shadow_element(l_count).priority := csr_rec.relative_processing_priority;
802 l_shadow_element(l_count).third_party_pay_only_flag := p_third_party;
803 l_shadow_element(l_count).classification_name := csr_rec.classification_name;
804 end loop;
805 -- 4. <BASE NAME> Setup
806 for csr_rec in csr_shd_ele (p_scheme_prefix||' Setup')
807 loop
808 l_count := l_count +1;
809 l_shadow_element(l_count).element_type_id := csr_rec.element_type_id;
810 l_shadow_element(l_count).object_version_number := csr_rec.object_version_number;
811 l_shadow_element(l_count).reporting_name := nvl(p_reporting_name,p_scheme_prefix || ' Setup');
812 l_shadow_element(l_count).description := 'Element for '||p_scheme_prefix
813 ||' Setup';
814 l_shadow_element(l_count).priority := csr_rec.relative_processing_priority;
815 l_shadow_element(l_count).third_party_pay_only_flag := csr_rec.third_party_pay_only_flag;
816 l_shadow_element(l_count).classification_name := csr_rec.classification_name;
817 end loop;
818 hr_utility.set_location('..Updating the scheme shadow elements', 30);
819 for i in 1..l_count
820 loop
821 pay_shadow_element_api.update_shadow_element
822 (p_validate => false
823 ,p_effective_date => p_effective_start_date
824 ,p_element_type_id => l_shadow_element(i).element_type_id
825 ,p_description => l_shadow_element(i).description
826 ,p_reporting_name => l_shadow_element(i).reporting_name
827 ,p_post_termination_rule => p_termination_rule
828 ,p_standard_link_flag => l_std_link_flag
829 ,p_relative_processing_priority => l_shadow_element(i).priority
830 ,p_object_version_number => l_shadow_element(i).object_version_number
831 ,p_third_party_pay_only_flag => l_shadow_element(i).third_party_pay_only_flag
832 ,p_classification_name => l_shadow_element(i).classification_name
833 );
834 end loop;
835 hr_utility.set_location('..After Updating the scheme shadow elements', 50);
836
837 -- Replace the spaces in the prefix with underscores. The formula name
838 -- has underscores if the prefix name has spaces in it .
839 l_scheme_prefix := upper(replace(l_scheme_prefix,' ','_'));
840
841
842 hr_utility.set_location('..Updated Shadow element', 25);
843
844 -- ---------------------------------------------------------------------
845 -- |-------------------< Generate Core Objects >------------------------|
846 -- ---------------------------------------------------------------------
847 pay_element_template_api.generate_part1
848 (p_validate => false
849 ,p_effective_date => p_effective_start_date
850 ,p_hr_only => false
851 ,p_hr_to_payroll => false
852 ,p_template_id => l_template_id);
853 --
854 hr_utility.set_location('After Generating Core objects : Part - 1', 50);
855 --
856 pay_element_template_api.generate_part2
857 (p_validate => false
858 ,p_effective_date => p_effective_start_date
859 ,p_template_id => l_template_id);
860 --
861 hr_utility.set_location('After Generating Core objects : Part - 2', 50);
862
863 -- Update some of the input values on the main element
864
865 Update_Ipval_Defval( p_scheme_prefix||' Pension Deduction'
866 ,'Pension Category'
867 ,l_pension_category
868 ,p_business_group_id);
869
870
871 Update_Ipval_Defval( p_scheme_prefix||' Pension Deduction'
872 ,'PRSA2 Certificate'
873 ,p_prsa2_certificate
874 ,p_business_group_id);
875
876 Update_Ipval_Defval( p_scheme_prefix||' Pension Deduction'
877 ,'Pension Type ID'
878 ,p_pension_type_id
879 ,p_business_group_id);
880
881 Update_Ipval_Defval( p_scheme_prefix||' EE Taxable Contribution'
882 ,'Contribution Amount'
883 ,fnd_number.number_to_canonical(0)
884 ,p_business_group_id);
885 Update_Ipval_Defval( p_scheme_prefix||' EE Taxable Contribution'
886 ,'Excess Contribution Amount'
887 ,fnd_number.number_to_canonical(0)
888 ,p_business_group_id);
889 Update_Ipval_Defval( p_scheme_prefix||' Setup'
890 ,'EE Pension Deduction'
891 ,fnd_number.number_to_canonical(0)
892 ,p_business_group_id);
893 IF l_configuration_information1 = 'Y' THEN
894 Update_Ipval_Defval( p_scheme_prefix||' ER Contribution'
895 ,'Pension Type ID'
896 ,p_pension_type_id
897 ,p_business_group_id);
898 Update_Ipval_Defval( p_scheme_prefix||' Setup'
899 ,'ER Contribution'
900 ,fnd_number.number_to_canonical(0)
901 ,p_business_group_id);
902 END IF;
903 Update_Ipval_Defval( p_scheme_prefix||' Setup'
904 ,'EE Taxable Contribution'
905 ,fnd_number.number_to_canonical(0)
906 ,p_business_group_id);
907 Update_Ipval_Defval( p_scheme_prefix||' Setup'
908 ,'EE Arrears'
909 ,fnd_number.number_to_canonical(0)
910 ,p_business_group_id);
911
912 -- ------------------------------------------------------------------------
913 -- Create a row in pay_element_extra_info with all the element information
914 -- ------------------------------------------------------------------------
915 l_base_element_type_id := get_object_id ('ELE',
916 p_scheme_prefix||' Pension Deduction',
917 p_business_group_id,
918 l_template_id);
919 IF l_configuration_information1 = 'Y' THEN
920 l_er_base_element_type_id := get_object_id ('ELE',
921 p_scheme_prefix||' ER Contribution',
922 p_business_group_id,
923 l_template_id);
924 END IF;
925 l_ee_tax_element_type_id := get_object_id ('ELE',
926 p_scheme_prefix||' EE Taxable Contribution',
927 p_business_group_id,
928 l_template_id);
929 l_setup_element_type_id := get_object_id ('ELE',
930 p_scheme_prefix||' Setup',
931 p_business_group_id,
932 l_template_id);
933 pay_element_extra_info_api.create_element_extra_info
934 (p_element_type_id => l_base_element_type_id
935 ,p_information_type => 'IE_PENSION_SCHEME_INFO'
936 ,p_eei_information_category => 'IE_PENSION_SCHEME_INFO'
937 ,p_eei_information1 => to_char(p_pension_type_id)
938 ,p_eei_information2 => to_char(p_pension_provider_id)
939 ,p_eei_information3 => p_scheme_prefix
940 ,p_eei_information4 => p_reporting_name
941 ,p_eei_information5 => fnd_date.date_to_canonical(p_effective_start_date)
942 ,p_eei_information6 => p_prsa2_certificate
943 ,p_eei_information7 => p_termination_rule
944 ,p_eei_information8 => p_third_party
945 ,p_eei_information9 => null
946 ,p_eei_information10 => null
947 ,p_eei_information11 => null
948 ,p_eei_information12 => null
949 ,p_eei_information13 => null
950 ,p_eei_information14 => null
951 ,p_eei_information15 => null
952 ,p_eei_information16 => null
953 ,p_eei_information17 => null
954 ,p_eei_information18 => null
955 ,p_eei_information19 => null
956 ,p_eei_information20 => null
957 ,p_element_type_extra_info_id => l_eei_info_id
958 ,p_object_version_number => l_ovn_eei);
959 IF l_configuration_information1 = 'Y' THEN
960 pay_element_extra_info_api.create_element_extra_info
961 (p_element_type_id => l_er_base_element_type_id
962 ,p_information_type => 'IE_PENSION_SCHEME_INFO'
963 ,p_eei_information_category => 'IE_PENSION_SCHEME_INFO'
964 ,p_eei_information1 => to_char(p_pension_type_id)
965 ,p_eei_information2 => to_char(p_pension_provider_id)
966 ,p_eei_information3 => p_scheme_prefix
967 ,p_eei_information4 => p_reporting_name
968 ,p_eei_information5 => fnd_date.date_to_canonical(p_effective_start_date)
969 ,p_eei_information6 => p_prsa2_certificate
970 ,p_eei_information7 => p_termination_rule
971 ,p_eei_information8 => p_third_party
972 ,p_eei_information9 => null
973 ,p_eei_information10 => null
974 ,p_eei_information11 => null
975 ,p_eei_information12 => null
976 ,p_eei_information13 => null
977 ,p_eei_information14 => null
978 ,p_eei_information15 => null
979 ,p_eei_information16 => null
980 ,p_eei_information17 => null
981 ,p_eei_information18 => null
982 ,p_eei_information19 => null
983 ,p_eei_information20 => null
984 ,p_element_type_extra_info_id => l_eei_info_id
985 ,p_object_version_number => l_ovn_eei);
986 END IF;
987 pay_element_extra_info_api.create_element_extra_info
988 (p_element_type_id => l_ee_tax_element_type_id
989 ,p_information_type => 'IE_PENSION_SCHEME_INFO'
990 ,p_eei_information_category => 'IE_PENSION_SCHEME_INFO'
991 ,p_eei_information1 => to_char(p_pension_type_id)
992 ,p_eei_information2 => to_char(p_pension_provider_id)
993 ,p_eei_information3 => p_scheme_prefix
994 ,p_eei_information4 => p_reporting_name
995 ,p_eei_information5 => fnd_date.date_to_canonical(p_effective_start_date)
996 ,p_eei_information6 => p_prsa2_certificate
997 ,p_eei_information7 => p_termination_rule
998 ,p_eei_information8 => p_third_party
999 ,p_eei_information9 => null
1000 ,p_eei_information10 => null
1001 ,p_eei_information11 => null
1002 ,p_eei_information12 => null
1003 ,p_eei_information13 => null
1004 ,p_eei_information14 => null
1005 ,p_eei_information15 => null
1006 ,p_eei_information16 => null
1007 ,p_eei_information17 => null
1008 ,p_eei_information18 => null
1009 ,p_eei_information19 => null
1010 ,p_eei_information20 => null
1011 ,p_element_type_extra_info_id => l_eei_info_id
1012 ,p_object_version_number => l_ovn_eei);
1013
1014 hr_utility.set_location('..After Creating element extra information', 50);
1015
1016 -- ---------------------------------------------------------------------
1017 -- The base element's Pay Value should feed the EE Contribution balance
1018 -- for the pension scheme created.
1019 -- ---------------------------------------------------------------------
1020 IF l_pension_category='RBS' THEN
1021 l_bal_name1 := 'IE RBS EE Contribution';
1022 l_bal_name2 := 'IE RBS ER Contribution';
1023 END IF;
1024 IF l_pension_category='PRSA' THEN
1025 l_bal_name1 := 'IE PRSA EE Contribution';
1026 l_bal_name2 := 'IE PRSA ER Contribution';
1027 END IF;
1028 IF l_pension_category='RAC' THEN
1029 l_bal_name1 := 'IE RAC EE Contribution';
1030 l_bal_name2 := 'IE RAC ER Contribution';
1031 END IF;
1032 IF l_pension_category='RBSAVC' THEN
1033 l_bal_name1 := 'IE RBS EE AVC Contribution';
1034 l_bal_name2 := ' ';
1035 END IF;
1036 IF l_pension_category='PRSAAVC' THEN
1037 l_bal_name1 := 'IE PRSA EE AVC Contribution';
1038 l_bal_name2 := ' ';
1039 END IF;
1040
1041 OPEN csr_get_ee_bal_info (l_bal_name1);
1042 FETCH csr_get_ee_bal_info INTO l_seed_ee_bal_type_id;
1043 IF csr_get_ee_bal_info%NOTFOUND THEN
1044 fnd_message.set_name('PQP', 'PQP_230805_BAL_NOTFOUND');
1045 fnd_message.raise_error;
1046 CLOSE csr_get_ee_bal_info;
1047 END IF;
1048 CLOSE csr_get_ee_bal_info;
1049 IF l_pension_category <> 'RBSAVC' AND l_pension_category <> 'PRSAAVC' THEN
1050 OPEN csr_get_ee_bal_info (l_bal_name2);
1051 FETCH csr_get_ee_bal_info INTO l_seed_er_bal_type_id;
1052 IF csr_get_ee_bal_info%NOTFOUND THEN
1053 fnd_message.set_name('PQP', 'PQP_230805_BAL_NOTFOUND');
1054 fnd_message.raise_error;
1055 CLOSE csr_get_ee_bal_info;
1056 END IF;
1057 CLOSE csr_get_ee_bal_info;
1058 END IF;
1059
1060 for ipv_rec in csr_ipv
1061 (c_ele_typeid => l_base_element_type_id
1062 ,c_effective_date => p_effective_start_date )
1063 loop
1064
1065 Pay_Balance_Feeds_f_pkg.Insert_Row(
1066 X_Rowid => l_row_id,
1067 X_Balance_Feed_Id => l_Balance_Feed_Id,
1068 X_Effective_Start_Date => p_effective_start_date,
1069 X_Effective_End_Date => hr_api.g_eot,
1070 X_Business_Group_Id => p_business_group_id,
1071 X_Legislation_Code => null,
1072 X_Balance_Type_Id => l_seed_ee_bal_type_id,
1073 X_Input_Value_Id => ipv_rec.input_value_id,
1074 X_Scale => '1',
1075 X_Legislation_Subgroup => null,
1076 X_Initial_Balance_Feed => false );
1077
1078 l_Balance_Feed_Id := null;
1079 l_row_id := null;
1080
1081 end loop;
1082 for ipv_rec in csr_ipv1
1083 (c_ele_typeid => l_setup_element_type_id
1084 ,c_ipv_name => 'EE Pension Deduction'
1085 ,c_effective_date => p_effective_start_date )
1086 loop
1087
1088 Pay_Balance_Feeds_f_pkg.Insert_Row(
1089 X_Rowid => l_row_id,
1090 X_Balance_Feed_Id => l_Balance_Feed_Id,
1091 X_Effective_Start_Date => p_effective_start_date,
1092 X_Effective_End_Date => hr_api.g_eot,
1093 X_Business_Group_Id => p_business_group_id,
1094 X_Legislation_Code => null,
1095 X_Balance_Type_Id => l_seed_ee_bal_type_id,
1096 X_Input_Value_Id => ipv_rec.input_value_id,
1097 X_Scale => '1',
1098 X_Legislation_Subgroup => null,
1099 X_Initial_Balance_Feed => false );
1100
1101 l_Balance_Feed_Id := null;
1102 l_row_id := null;
1103
1104 end loop;
1105
1106 hr_utility.set_location('..After creating the balance feed for the base, Pay Value', 50);
1107
1108 -- ---------------------------------------------------------------------
1109 -- The ER base element's Pay Value should feed the ER Contribution balance
1110 -- for the pension scheme created.
1111 -- ---------------------------------------------------------------------
1112 IF l_pension_category <> 'RBSAVC' AND l_pension_category <> 'PRSAAVC' THEN
1113 for ipv_rec in csr_ipv
1114 (c_ele_typeid => l_er_base_element_type_id
1115 ,c_effective_date => p_effective_start_date )
1116 loop
1117 Pay_Balance_Feeds_f_pkg.Insert_Row(
1118 X_Rowid => l_row_id,
1119 X_Balance_Feed_Id => l_Balance_Feed_Id,
1120 X_Effective_Start_Date => p_effective_start_date,
1121 X_Effective_End_Date => hr_api.g_eot,
1122 X_Business_Group_Id => p_business_group_id,
1123 X_Legislation_Code => null,
1124 X_Balance_Type_Id => l_seed_er_bal_type_id,
1125 X_Input_Value_Id => ipv_rec.input_value_id,
1126 X_Scale => '1',
1127 X_Legislation_Subgroup => null,
1128 X_Initial_Balance_Feed => false );
1129
1130 l_Balance_Feed_Id := null;
1131 l_row_id := null;
1132 end loop;
1133
1134 for ipv_rec in csr_ipv1
1135 (c_ele_typeid => l_setup_element_type_id
1136 ,c_ipv_name => 'ER Contribution'
1137 ,c_effective_date => p_effective_start_date )
1138 loop
1139 Pay_Balance_Feeds_f_pkg.Insert_Row(
1140 X_Rowid => l_row_id,
1141 X_Balance_Feed_Id => l_Balance_Feed_Id,
1142 X_Effective_Start_Date => p_effective_start_date,
1143 X_Effective_End_Date => hr_api.g_eot,
1144 X_Business_Group_Id => p_business_group_id,
1145 X_Legislation_Code => null,
1146 X_Balance_Type_Id => l_seed_er_bal_type_id,
1147 X_Input_Value_Id => ipv_rec.input_value_id,
1148 X_Scale => '1',
1149 X_Legislation_Subgroup => null,
1150 X_Initial_Balance_Feed => false );
1151
1152 l_Balance_Feed_Id := null;
1153 l_row_id := null;
1154 end loop;
1155 END IF;
1156 hr_utility.set_location('..After creating the balance feed for the ER base, Pay Value', 51);
1157
1158 -- ---------------------------------------------------------------------
1159 -- Compile the base element's standard formula
1160 -- ---------------------------------------------------------------------
1161
1162 Compile_Formula
1163 (p_element_type_id => l_base_element_type_id
1164 ,p_effective_start_date => p_effective_start_date
1165 ,p_scheme_prefix => l_scheme_prefix
1166 ,p_business_group_id => p_business_group_id
1167 ,p_request_id => l_request_id
1168 );
1169 Compile_Formula
1170 (p_element_type_id => l_ee_tax_element_type_id
1171 ,p_effective_start_date => p_effective_start_date
1172 ,p_scheme_prefix => l_scheme_prefix
1173 ,p_business_group_id => p_business_group_id
1174 ,p_request_id => l_request_id
1175 );
1176
1177 IF l_pension_category <> 'RBSAVC' AND l_pension_category <> 'PRSAAVC' THEN
1178
1179 Compile_Formula
1180 (p_element_type_id => l_er_base_element_type_id
1181 ,p_effective_start_date => p_effective_start_date
1182 ,p_scheme_prefix => l_scheme_prefix
1183 ,p_business_group_id => p_business_group_id
1184 ,p_request_id => l_er_request_id
1185 );
1186
1187 END IF;
1188
1189 IF l_pension_category='PRSA' OR l_pension_category='RAC' THEN
1190 Create_Formula_Results (p_scheme_prefix
1191 ,l_pension_category
1192 ,p_business_group_id
1193 ,p_effective_start_date
1194 ,p_effective_end_date);
1195 END IF;
1196
1197 hr_utility.set_location('..After creating the formula result rules', 51);
1198 END IF;
1199
1200
1201 return l_base_element_type_id;
1202
1203 end Create_User_Template;
1204
1205
1206 -- ---------------------------------------------------------------------
1207 -- |--------------------< Create_User_Template_Swi >------------------------|
1208 -- ---------------------------------------------------------------------
1209
1210 function Create_User_Template_Swi
1211 (p_pension_provider_id In Number
1212 ,p_pension_type_id In Number
1213 ,p_scheme_prefix In Varchar2
1214 ,p_reporting_name In Varchar2
1215 ,p_prsa2_certificate In Varchar2
1216 ,p_third_party In Varchar2
1217 ,p_termination_rule In Varchar2
1218 ,p_effective_start_date In Date Default Null
1219 ,p_effective_end_date In Date Default Null
1220 ,p_security_group_id In Number Default Null
1221 ,p_business_group_id In Number
1222 )
1223 return Number is
1224 --
1225 -- Variables for API Boolean parameters
1226 l_validate boolean;
1227 --
1228 -- Variables for IN/OUT parameters
1229 l_element_type_id number;
1230 --
1231 -- Other variables
1232 l_return_status varchar2(1);
1233 l_proc varchar2(72) := 'Create_User_Template_Swi';
1234 Begin
1235 hr_utility.set_location(' Entering:' || l_proc,10);
1236 l_element_type_id := -1;
1237 --
1238 -- Issue a savepoint
1239 --
1240 savepoint Create_User_Template_Swi;
1241 --
1242 -- Initialise Multiple Message Detection
1243 --
1244 hr_multi_message.enable_message_list;
1245 --
1246 -- Remember IN OUT parameter IN values
1247 --
1248 --
1249 -- Convert constant values to their corresponding boolean value
1250 --
1251 l_validate :=
1252 hr_api.constant_to_boolean
1253 (p_constant_value => hr_api.g_false_num);
1254 --
1255 -- Register Surrogate ID or user key values
1256 --
1257 --
1258 -- Call API
1259 --
1260 l_element_type_id := Create_User_Template
1261 (p_pension_provider_id =>p_pension_provider_id
1262 ,p_pension_type_id =>p_pension_type_id
1263 ,p_scheme_prefix =>p_scheme_prefix
1264 ,p_reporting_name =>p_reporting_name
1265 ,p_prsa2_certificate =>p_prsa2_certificate
1266 ,p_third_party =>p_third_party
1267 ,p_termination_rule =>p_termination_rule
1268 ,p_effective_start_date =>p_effective_start_date
1269 ,p_effective_end_date =>p_effective_end_date
1270 ,p_security_group_id =>p_security_group_id
1271 ,p_business_group_id =>p_business_group_id
1272 );
1273 --
1274 -- Convert API warning boolean parameter values to specific
1275 -- messages and add them to Multiple Message List
1276 --
1277 --
1278 -- Convert API non-warning boolean parameter values
1279 --
1280 --
1281 -- Derive the API return status value based on whether
1282 -- messages of any type exist in the Multiple Message List.
1283 -- Also disable Multiple Message Detection.
1284 --
1285 l_return_status := hr_multi_message.get_return_status_disable;
1286 hr_utility.set_location(' Leaving:' || l_proc,20);
1287 return l_element_type_id;
1288
1289 --
1290 exception
1291 when hr_multi_message.error_message_exist then
1292 --
1293 -- Catch the Multiple Message List exception which
1294 -- indicates API processing has been aborted because
1295 -- at least one message exists in the list.
1296 --
1297 rollback to Create_User_Template_Swi;
1298 --
1299 -- Reset IN OUT parameters and set OUT parameters
1300 --
1301 return l_element_type_id;
1302 hr_utility.set_location(' Leaving:' || l_proc, 30);
1303
1304 when others then
1305 --
1306 -- When Multiple Message Detection is enabled catch
1307 -- any Application specific or other unexpected
1308 -- exceptions. Adding appropriate details to the
1309 -- Multiple Message List. Otherwise re-raise the
1310 -- error.
1311 --
1312 rollback to Create_User_Template_Swi;
1313 if hr_multi_message.unexpected_error_add(l_proc) then
1314 hr_utility.set_location(' Leaving:' || l_proc,40);
1315 raise;
1316 end if;
1317 --
1318 -- Reset IN OUT and set OUT parameters
1319 --
1320 l_return_status := hr_multi_message.get_return_status_disable;
1321 return l_element_type_id;
1322 hr_utility.set_location(' Leaving:' || l_proc,50);
1323
1324
1325 END create_user_template_swi;
1326
1327
1328 -- ---------------------------------------------------------------------
1329 -- |--------------------< Delete_User_Template >------------------------|
1330 -- ---------------------------------------------------------------------
1331 procedure Delete_User_Template
1332 (p_business_group_id In Number
1333 ,p_pension_dedn_ele_name In Varchar2
1334 ,p_pension_dedn_ele_type_id In Number
1335 ,p_security_group_id In Number
1336 ,p_effective_date In Date
1337 ) is
1338 --
1339 cursor c1 is
1340 select template_id
1341 from pay_element_templates
1342 where base_name||' Pension Deduction' = p_pension_dedn_ele_name
1343 and business_group_id = p_business_group_id
1344 and template_type = 'U';
1345
1346 CURSOR csr_ele_extra_info IS
1347 SELECT element_type_extra_info_id
1348 ,object_version_number
1349 FROM pay_element_type_extra_info
1350 WHERE eei_information_category = 'IE_PENSION_SCHEME_INFO'
1351 AND element_type_id = p_pension_dedn_ele_type_id;
1352
1353 l_template_id Number(9);
1354 l_proc Varchar2(60) := g_proc_name||'Delete_User_Template';
1355
1356 begin
1357 hr_utility.set_location('Entering :'||l_proc, 10);
1358 --
1359 for c1_rec in c1 loop
1360 l_template_id := c1_rec.template_id;
1361 end loop;
1362 --
1363 pay_element_template_api.delete_user_structure
1364 (p_validate => false
1365 ,p_drop_formula_packages => true
1366 ,p_template_id => l_template_id);
1367 --
1368
1369 --
1370 -- Delete the rows in pay_element_type_extra_info
1371 --
1372
1373 FOR temp_rec IN csr_ele_extra_info
1374 LOOP
1375 pay_element_extra_info_api.delete_element_extra_info
1376 (p_element_type_extra_info_id => temp_rec.element_type_extra_info_id
1377 ,p_object_version_number => temp_rec.object_version_number);
1378 END LOOP;
1379
1380 hr_utility.set_location('Leaving :'||l_proc, 50);
1381
1382 end Delete_User_Template;
1383 --
1384
1385 -- ---------------------------------------------------------------------
1386 -- |------------------< Delete_User_Template_Swi >----------------------|
1387 -- ---------------------------------------------------------------------
1388
1389 procedure Delete_User_Template_Swi
1390 (p_business_group_id In Number
1391 ,p_pension_dedn_ele_name In Varchar2
1392 ,p_pension_dedn_ele_type_id In Number
1393 ,p_security_group_id In Number
1394 ,p_effective_date In Date
1395 ) is
1396
1397 --
1398 -- Variables for API Boolean parameters
1399 l_validate boolean;
1400 --
1401 -- Variables for IN/OUT parameters
1402 --
1403 -- Other variables
1404 l_return_status varchar2(1);
1405 l_proc varchar2(72) := 'Delete_User_Template_Swi';
1406 Begin
1407 hr_utility.set_location(' Entering:' || l_proc,10);
1408 --
1409 -- Issue a savepoint
1410 --
1411 savepoint Delete_User_Template_Swi;
1412 --
1413 -- Initialise Multiple Message Detection
1414 --
1415 hr_multi_message.enable_message_list;
1416 --
1417 -- Remember IN OUT parameter IN values
1418 --
1419 --
1420 -- Convert constant values to their corresponding boolean value
1421 --
1422 l_validate :=
1423 hr_api.constant_to_boolean
1424 (p_constant_value => hr_api.g_false_num);
1425 --
1426 -- Register Surrogate ID or user key values
1427 --
1428 --
1429 -- Call API
1430 --
1431 Delete_User_Template
1432 (p_business_group_id => p_business_group_id
1433 ,p_pension_dedn_ele_name => p_pension_dedn_ele_name
1434 ,p_pension_dedn_ele_type_id => p_pension_dedn_ele_type_id
1435 ,p_security_group_id => p_security_group_id
1436 ,p_effective_date => p_effective_date
1437 );
1438 --
1439 -- Convert API warning boolean parameter values to specific
1440 -- messages and add them to Multiple Message List
1441 --
1442 --
1443 -- Convert API non-warning boolean parameter values
1444 --
1445 --
1446 -- Derive the API return status value based on whether
1447 -- messages of any type exist in the Multiple Message List.
1448 -- Also disable Multiple Message Detection.
1449 --
1450 l_return_status := hr_multi_message.get_return_status_disable;
1451 hr_utility.set_location(' Leaving:' || l_proc,20);
1452
1453 --
1454 exception
1455 when hr_multi_message.error_message_exist then
1456 --
1457 -- Catch the Multiple Message List exception which
1458 -- indicates API processing has been aborted because
1459 -- at least one message exists in the list.
1460 --
1461 rollback to Delete_User_Template_Swi;
1462 --
1463 -- Reset IN OUT parameters and set OUT parameters
1464 --
1465 hr_utility.set_location(' Leaving:' || l_proc, 30);
1466
1467 when others then
1468 --
1469 -- When Multiple Message Detection is enabled catch
1470 -- any Application specific or other unexpected
1471 -- exceptions. Adding appropriate details to the
1472 -- Multiple Message List. Otherwise re-raise the
1473 -- error.
1474 --
1475 rollback to Delete_User_Template_Swi;
1476 if hr_multi_message.unexpected_error_add(l_proc) then
1477 hr_utility.set_location(' Leaving:' || l_proc,40);
1478 raise;
1479 end if;
1480 --
1481 -- Reset IN OUT and set OUT parameters
1482 --
1483 l_return_status := hr_multi_message.get_return_status_disable;
1484 hr_utility.set_location(' Leaving:' || l_proc,50);
1485
1486 END delete_user_template_swi;
1487
1488 --
1489
1490 end pay_ie_pension_template;