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