[Home] [Help]
PACKAGE BODY: APPS.PQP_GB_PENSIONS_TEMPLATE
Source
1 PACKAGE BODY pqp_gb_pensions_template AS
2 /* $Header: pqgbpatp.pkb 120.1 2005/05/30 00:12:07 rvishwan noship $ */
3
4 /*========================================================================
5 * CREATE_USER_TEMPLATE
6 *=======================================================================*/
7 FUNCTION create_user_template
8 (p_ele_name in varchar2
9 ,p_ele_reporting_name in varchar2
10 ,p_ele_description in varchar2 default NULL
11 ,p_ele_classification in varchar2
12 ,p_sch_type in varchar2
13 ,p_emp_cont_method in varchar2
14 ,p_emp_contribution in number default NULL
15 ,p_adl_contribution in varchar2
16 ,p_eer_contribution in varchar2
17 ,p_eer_type in varchar2
18 ,p_eer_rate in number default NULL
19 ,p_ept_contribution in varchar2
20 ,p_byb_added_years in varchar2
21 ,p_fmwd_benefit in varchar2
22 ,p_avc_percentage in varchar2
23 ,p_avc_per_provider in varchar2
24 ,p_avc_fixed_rate in varchar2
25 ,p_avc_fxdrt_provider in varchar2
26 ,p_life_assurance in varchar2
27 ,p_life_asr_provider in varchar2
28 ,p_ele_eff_start_date in date default NULL
29 ,p_ele_eff_end_date in date default NULL
30 ,p_bg_id in number
31 )
32 RETURN NUMBER IS
33 --
34
35
36 /*--------------------------------------------------------------------
37 The input values are explained below : V-varchar2, D-Date, N-number
38 Input-Name Type Valid Values/Explaination
39 ---------- ---- --------------------------------------
40 p_ele_name (V) - User i/p Element name
41 p_ele_reporting_name (V) - User i/p reporting name
42 p_ele_description (V) - User i/p Description
43 p_ele_classification (V) - 'Pre-Tax Deductions'
44 p_sch_type (V) - User i/p
45 p_emp_cont_method (V) - 'P'/'F'
46 p_emp_contribution (N) - User i/p
47 p_adl_contribution (V) - 'Y'/'N'
48 p_eer_contribution (V) - 'Y'/'N'
49 p_eer_type (V) - 'P'/'F'
50 p_eer_rate (N) - User i/p
51 p_ept_contribution (V) - 'Y'/'N'
52 p_byb_added_years (V) - 'Y'/'N'
53 p_fmwd_benefit (V) - 'Y'/'N'
54 p_avc_percentage (V) - 'Y'/'N'
55 p_avc_per_provider (V) - User i/p
56 p_avc_fixed_rate (V) - 'Y'/'N'
57 p_avc_fxdrt_provider (V) - User i/p
58 p_life_assurance (V) - 'Y'/'N'
59 p_life_asr_provider (V) - User i/p
60 p_ele_eff_start_date (D) - Trunc(start date)
61 p_ele_eff_end_date (D) - Trunc(end date)
62 p_bg_id (N) - Business group id
63 ----------------------------------------------------------------------*/
64 --
65 l_template_id pay_shadow_element_types.template_id%TYPE;
66 l_base_element_type_id pay_template_core_objects.core_object_id%TYPE;
67 l_source_template_id pay_element_templates.template_id%TYPE;
68 l_object_version_number NUMBER(9);
69 l_proc VARCHAR2(80) :=
70 'pqp_gb_pensions_template.create_user_template';
71 l_flat_rate VARCHAR2(3);
72 l_per_contribution VARCHAR2(3);
73 l_adl_contribution VARCHAR2(3);
74 l_eer_contribution VARCHAR2(3);
75 l_eer_per_type VARCHAR2(3);
76 l_eer_fac_type VARCHAR2(3);
77 l_ept_contribution VARCHAR2(3);
78 l_byb_added_years VARCHAR2(3);
79 l_fmwd_benefit VARCHAR2(3);
80 l_avc_percentage VARCHAR2(3);
81 l_avc_fixed_rate VARCHAR2(3);
82 l_life_assurance VARCHAR2(3);
83 l_stk_hld_pension VARCHAR2(3);
84 l_eei_info_id NUMBER;
85 l_ovn_eei NUMBER;
86 l_element_type_id NUMBER;
87 l_avcren VARCHAR2(80);
88 l_ele_obj_ver_number NUMBER; --
89 l_ele_name pay_element_types_f.element_name%TYPE;
90 l_name pay_input_values_f.name%TYPE;
91 i NUMBER;
92
93 -- Extra Information variables
94 l_eei_information2 pay_element_type_extra_info.eei_information2%TYPE;
95 l_eei_information3 pay_element_type_extra_info.eei_information3%TYPE;
96 l_eei_information4 pay_element_type_extra_info.eei_information4%TYPE;
97 l_eei_information5 pay_element_type_extra_info.eei_information5%TYPE;
98 l_eei_information6 pay_element_type_extra_info.eei_information6%TYPE;
99 l_eei_information7 pay_element_type_extra_info.eei_information7%TYPE;
100 l_eei_information8 pay_element_type_extra_info.eei_information8%TYPE;
101 l_eei_information9 pay_element_type_extra_info.eei_information9%TYPE;
102 l_eei_information10 pay_element_type_extra_info.eei_information10%TYPE;
103 l_eei_information11 pay_element_type_extra_info.eei_information11%TYPE;
104 l_eei_information12 pay_element_type_extra_info.eei_information12%TYPE;
105 --
106 TYPE t_avc_prov IS TABLE OF VARCHAR2(80)
107 INDEX BY BINARY_INTEGER;
108
109 l_avc t_avc_prov;
110
111 TYPE t_avc_temp IS TABLE OF VARCHAR2(80)
112 INDEX BY BINARY_INTEGER;
113
114 l_avctemp t_avc_temp;
115
116 TYPE t_sub_ele IS TABLE OF VARCHAR2(80)
117 INDEX BY BINARY_INTEGER;
118
119 l_sub t_sub_ele;
120
121 CURSOR c1 (c_ele_name varchar2) is
122 SELECT element_type_id, object_version_number
123 FROM pay_shadow_element_types
124 WHERE template_id = l_template_id
125 AND element_name = c_ele_name;
126 --
127 -- cursor to fetch the core element id
128 --
129 CURSOR c5 (c_element_name in varchar2) is
130 SELECT ptco.core_object_id
131 FROM pay_shadow_element_types psbt,
132 pay_template_core_objects ptco
133 WHERE psbt.template_id = l_template_id
134 AND psbt.element_name = c_element_name
135 AND ptco.template_id = psbt.template_id
136 AND ptco.shadow_object_id = psbt.element_type_id
137 AND ptco.core_object_type = 'ET';
138 --
139 --======================================================================
140 -- FUNCTION GET_TEMPLATE_ID
141 --======================================================================
142 FUNCTION get_template_id (p_legislation_code in varchar2 )
143 RETURN number IS
144 --
145 -- l_template_id NUMBER(9);
146 l_template_name VARCHAR2(80);
147 l_proc varchar2(60) := 'pqp_gb_pensions_template.get_template_id';
148 --
149 CURSOR c4 is
150 SELECT template_id
151 FROM pay_element_templates
152 WHERE template_name = l_template_name
153 AND legislation_code = p_legislation_code
154 AND template_type = 'T'
155 AND business_group_id is NULL;
156 --
157 BEGIN
158 --
159 hr_utility.set_location('Entering: '||l_proc, 10);
160 --
161 l_template_name := 'PQP PENSION AND AVCS';
162 --
163 hr_utility.set_location(l_proc, 30);
164 --
165 for c4_rec in c4 loop
166 l_template_id := c4_rec.template_id;
167 end loop;
168 --
169 hr_utility.set_location('Leaving: '||l_proc, 100);
170 --
171 RETURN l_template_id;
172 --
173 END get_template_id;
174
175 -----------------------------------------------------------------------------
176 --- Procedure Update Element Type formula
177 -----------------------------------------------------------------------------
178 PROCEDURE update_eletyp_for(l_ele_name IN VARCHAR2
179 ,l_formula_name IN VARCHAR2)
180 IS
181
182 CURSOR c1_getfor(lc_formula_name varchar2) IS
183 SELECT formula_id
184 FROM ff_formulas_f
185 WHERE formula_name = lc_formula_name
186 AND (business_group_id = p_bg_id OR business_group_id IS NULL)
187 AND (legislation_code = 'GB' OR legislation_code IS NULL);
188
189 CURSOR c2_getele(lc_ele_name varchar2) IS
190 SELECT element_type_id
191 FROM pay_element_types_f
192 WHERE element_name = lc_ele_name
193 AND (business_group_id = p_bg_id OR business_group_id IS NULL)
194 AND (legislation_code = 'GB' OR legislation_code IS NULL);
195
196 CURSOR c3_updele(lc_ele_type_id number) IS
197 SELECT rowid
198 FROM pay_element_types_f
199 WHERE element_type_id = lc_ele_type_id
200 FOR UPDATE NOWAIT;
201
202 c1_rec c1_getfor%ROWTYPE;
203 c2_rec c2_getele%ROWTYPE;
204 c3_rec c3_updele%ROWTYPE;
205
206 BEGIN
207
208 OPEN c1_getfor(l_formula_name);
209 LOOP
210
211 FETCH c1_getfor INTO c1_rec;
212 EXIT WHEN c1_getfor%NOTFOUND;
213
214 OPEN c2_getele(l_ele_name);
215 LOOP
216
217 FETCH c2_getele INTO c2_rec;
218 EXIT WHEN c2_getele%NOTFOUND;
219
220 OPEN c3_updele(c2_rec.element_type_id);
221 LOOP
222
223 FETCH c3_updele INTO c3_rec;
224 EXIT WHEN c3_updele%NOTFOUND;
225
226 UPDATE pay_element_types_f
227 SET formula_id = c1_rec.formula_id
228 WHERE rowid = c3_rec.rowid;
229
230 END LOOP;
231 CLOSE c3_updele;
232
233 END LOOP;
234 CLOSE c2_getele;
235
236 END LOOP;
237 CLOSE c1_getfor;
238
239 END update_eletyp_for;
240
241 -----------------------------------------------------------------------------
242 --- Procedure Update Input Value default value
243 -----------------------------------------------------------------------------
244 PROCEDURE update_ipval_defval(l_ele_name IN VARCHAR2
245 ,l_name IN VARCHAR2
246 ,l_value IN NUMBER)
247 IS
248
249 CURSOR c1_getinput(lc_ele_name Varchar2,lc_name varchar2) IS
250 SELECT input_value_id,
251 piv.name,
252 piv.element_type_id
253 FROM pay_input_values_f piv,
254 pay_element_types_f pet
255 WHERE element_name= lc_ele_name
256 AND piv.element_type_id=pet.element_type_id
257 AND (piv.business_group_id =p_bg_id OR piv.business_group_id IS NULL)
258 AND piv.name =lc_name
259 AND (piv.legislation_code='GB' OR piv.legislation_code IS NULL);
260
261 CURSOR c2_updinput(lc_ip_id number
262 ,lc_element_type_id number) IS
263 SELECT rowid
264 FROM pay_input_values_f
265 WHERE input_value_id = lc_ip_id
266 AND element_type_id = lc_element_type_id
267 FOR UPDATE NOWAIT;
268
269 c1_rec c1_getinput%rowtype;
270 c2_rec c2_updinput%rowtype;
271
272 BEGIN
273
274 OPEN c1_getinput(l_ele_name
275 ,l_name);
276 LOOP
277
278 FETCH c1_getinput INTO c1_rec;
279 EXIT WHEN c1_getinput%NOTFOUND;
280
281 OPEN c2_updinput(c1_rec.input_value_id
282 ,c1_rec.element_type_id);
283 LOOP
284
285 FETCH c2_updinput INTO c2_rec;
286 EXIT WHEN c2_updinput%NOTFOUND;
287
288 UPDATE pay_input_values_f
289 SET default_value = l_value
290 WHERE rowid = c2_rec.rowid;
291
292 END LOOP;
293 CLOSE c2_updinput;
294
295 END LOOP;
296 CLOSE c1_getinput;
297
298 END update_ipval_defval;
299
300 --
301 --=======================================================================
302 -- FUNCTION GET_OBJECT_ID
303 --=======================================================================
304 FUNCTION get_object_id (p_object_type in varchar2,
305 p_object_name in varchar2)
306 RETURN NUMBER is
307 --
308 l_object_id NUMBER := NULL;
309 l_proc varchar2(60) := 'pqp_gb_pensions_template.get_object_id';
310 --
311 CURSOR c2 (c_object_name varchar2) is
312 SELECT element_type_id
313 FROM pay_element_types_f
314 WHERE element_name = c_object_name
315 AND business_group_id = p_bg_id;
316 --
317 CURSOR c3 (c_object_name in varchar2) is
318 SELECT ptco.core_object_id
319 FROM pay_shadow_balance_types psbt,
320 pay_template_core_objects ptco
321 WHERE psbt.template_id = l_template_id
322 AND psbt.balance_name = c_object_name
323 AND ptco.template_id = psbt.template_id
324 AND ptco.shadow_object_id = psbt.balance_type_id;
325 --
326 BEGIN
327 hr_utility.set_location('Entering: '||l_proc, 10);
328 --
329 if p_object_type = 'ELE' then
330 for c2_rec in c2 (p_object_name) loop
331 l_object_id := c2_rec.element_type_id; -- element id
332 end loop;
333 elsif p_object_type = 'BAL' then
334 for c3_rec in c3 (p_object_name) loop
335 l_object_id := c3_rec.core_object_id; -- balance id
336 end loop;
337 end if;
338 --
339 hr_utility.set_location('Leaving: '||l_proc, 50);
340 --
341 RETURN l_object_id;
342 --
343 END get_object_id;
344 --
345 --===============================================================================
346 -- MAIN FUNCTION
347 --===============================================================================
348 BEGIN
349 hr_utility.set_location('Entering : '||l_proc, 10);
350 ---------------------
351 -- Set session date
352 ---------------------
353
354 pay_db_pay_setup.set_session_date(nvl(p_ele_eff_start_date, sysdate));
355 --
356 hr_utility.set_location(l_proc, 20);
357 ---------------------------
358 -- Get Source Template ID
359 ---------------------------
360 l_source_template_id := get_template_id
361 (p_legislation_code => 'GB'
362 );
363 hr_utility.set_location(l_proc, 30);
364 --
365 /*--------------------------------------------------------------------------
366 Create the user Structure
367 The Configuration Flex segments for the Exclusion Rules are as follows:
368 ---------------------------------------------------------------------------
369 Config1 --
370 Config2 --
371 ---------------------------------------------------------------------------*/
372
373 l_flat_rate := 'N';
374 l_per_contribution := 'N';
375 l_adl_contribution := 'N';
376 l_eer_contribution := 'N';
377 l_eer_per_type := 'N';
378 l_eer_fac_type := 'N';
379 l_ept_contribution := 'N';
380 l_byb_added_years := 'N';
381 l_fmwd_benefit := 'N';
382 l_avc_percentage := 'N';
383 l_avc_fixed_rate := 'N';
384 l_life_assurance := 'N';
385 l_stk_hld_pension := 'N';
386 i := 0;
387
388 -- Intialize all Extra Infornation type variables
389 l_eei_information2 := NULL;
390 l_eei_information3 := 'N';
391 l_eei_information4 := 'N';
392 l_eei_information5 := NULL;
393 l_eei_information6 := NULL;
394 l_eei_information7 := 'N';
395 l_eei_information8 := 'N';
396 l_eei_information9 := NULL;
397 l_eei_information10 := NULL;
398 l_eei_information11 := NULL;
399 l_eei_information12 := NULL;
400
401 IF p_emp_cont_method = 'F' THEN
402 l_flat_rate := NULL;
403 l_eei_information2 := 'F';
404 ELSIF p_emp_cont_method = 'P' THEN
405 l_per_contribution := NULL;
406 l_eei_information2 := 'P';
407 END IF; -- End if of contribution type check...
408
409 IF p_eer_contribution = 'Y' THEN
410 l_eer_contribution := NULL;
411 i := i + 1;
412 l_sub(i) := ' Employer Contribution';
413
414 IF p_eer_type = 'P' THEN
415 l_eer_per_type := NULL;
416 l_eei_information6 := 'P';
417 ELSIF p_eer_type = 'F' THEN
418 l_eer_fac_type := NULL;
419 l_eei_information6 := 'F';
420 END IF; -- End if of employer contribution type check...
421
422 END IF; -- End if of employer contribution check...
423
424 IF p_adl_contribution = 'Y' THEN
425 l_adl_contribution := NULL;
426 i := i + 1;
427 l_sub(i) := ' Additional Contribution';
428 i := i + 1;
429 l_sub(i) := ' Exceptional Adjustment';
430 l_eei_information3 := 'Y';
431 END IF; -- End if of adl contribution check...
432
433 IF p_ept_contribution = 'Y' THEN
434 l_ept_contribution := NULL;
435 i := i + 1;
436 l_sub(i) := ' Exceptional Contribution';
437 l_eei_information4 := 'Y';
438 END IF; -- End if of ept contribution check...
439
440 IF p_byb_added_years = 'Y' THEN
441 l_byb_added_years := NULL;
442 i := i + 1;
443 l_sub(i) := ' BuyBack Added Yrs';
444 l_eei_information7 := 'Y';
445 END IF; -- End if of byb addyrs check...
446
447 IF p_fmwd_benefit = 'Y' THEN
448 l_fmwd_benefit := NULL;
449 i := i + 1;
450 l_sub(i) := ' Family Widower';
451 l_eei_information8 := 'Y';
452 END IF; -- End if of fmwd benefit check...
453
454 IF p_avc_percentage = 'Y' THEN
455 l_avc_percentage := NULL;
456 i := i + 1;
457 l_sub(i) := ' AVC Percentage ';
458 l_eei_information9 := p_avc_per_provider;
459 END IF; -- End if of avc percentage check...
460
461 IF p_avc_fixed_rate = 'Y' THEN
462 l_avc_fixed_rate := NULL;
463 i := i + 1;
464 l_sub(i) := ' AVC Fixed Rate ';
465 l_eei_information10 := p_avc_fxdrt_provider;
466 END IF; -- End if of avc fixed rate check...
467
468 IF p_life_assurance = 'Y' THEN
469 l_life_assurance := NULL;
470 i := i + 1;
471 l_sub(i) := ' Life Assurance ';
472 l_eei_information11 := p_life_asr_provider;
473 END IF; -- End if of life assurance check...
474
475 --
476 -- create user structure from the template
477 --
478 pay_element_template_api.create_user_structure
479 (p_validate => false
480 ,p_effective_date => p_ele_eff_start_date
481 ,p_business_group_id => p_bg_id
482 ,p_source_template_id => l_source_template_id
483 ,p_base_name => p_ele_name
484 ,p_configuration_information1 => l_flat_rate
485 ,p_configuration_information2 => l_per_contribution
486 ,p_configuration_information3 => l_eer_per_type
487 ,p_configuration_information4 => l_eer_fac_type
488 ,p_configuration_information5 => l_eer_contribution
489 ,p_configuration_information6 => l_adl_contribution
490 ,p_configuration_information7 => l_ept_contribution
491 ,p_configuration_information8 => l_byb_added_years
492 ,p_configuration_information9 => l_fmwd_benefit
493 ,p_configuration_information10 => l_avc_percentage
494 ,p_configuration_information11 => l_avc_fixed_rate
495 ,p_configuration_information12 => l_life_assurance
496 ,p_template_id => l_template_id
497 ,p_object_version_number => l_object_version_number
498 );
499 --
500
501 hr_utility.set_location(l_proc, 80);
502 ---------------------------------------------------------------------------
503 ---------------------------- Update Shadow Structure ----------------------
504 --
505
506 OPEN c1(p_ele_name);
507 LOOP
508 FETCH c1 INTO l_element_type_id,l_ele_obj_ver_number;
509 EXIT WHEN c1%NOTFOUND;
510
511 pay_shadow_element_api.update_shadow_element
512 (p_validate => false
513 ,p_effective_date => p_ele_eff_start_date
514 ,p_element_type_id => l_element_type_id
515 ,p_element_name => p_ele_name
516 ,p_reporting_name => p_ele_reporting_name
517 ,P_DESCRIPTION => p_ele_description
518 ,p_object_version_number => l_ele_obj_ver_number
519 );
520
521 END LOOP;
522 CLOSE c1;
523
524
525 FOR i in 1..l_sub.count
526 LOOP
527
528 OPEN c1(p_ele_name||l_sub(i));
529 LOOP
530 FETCH c1 INTO l_element_type_id,l_ele_obj_ver_number;
531 EXIT WHEN c1%NOTFOUND;
532
533 pay_shadow_element_api.update_shadow_element
534 (p_validate => false
535 ,p_effective_date => p_ele_eff_start_date
536 ,p_element_type_id => l_element_type_id
537 ,p_element_name => p_ele_name || l_sub(i)
538 ,P_DESCRIPTION => p_ele_description
539 ,p_object_version_number => l_ele_obj_ver_number
540 );
541
542 END LOOP;
543 CLOSE c1;
544
545 END LOOP;
546
547
548
549 i := 0;
550 IF p_avc_percentage = 'Y' THEN
551 i := i + 1;
552 l_avc(i) := ' AVC Percentage ';
553 l_avctemp(i) := l_avc(i) || substr(p_avc_per_provider,1,5);
554 END IF; -- End if of avc percentage check...
555
556 IF p_avc_fixed_rate = 'Y' THEN
557 i := i + 1;
558 l_avc(i) := ' AVC Fixed Rate ';
559 l_avctemp(i) := l_avc(i) || substr(p_avc_fxdrt_provider,1,5);
560 END IF; -- End if of avc fixed rate check...
561
562 IF p_life_assurance = 'Y' THEN
563 i := i + 1 ;
564 l_avc(i) := ' Life Assurance ';
565 l_avctemp(i) := l_avc(i) || substr(p_life_asr_provider,1,5);
566 END IF; -- End if of life assurance check...
567
568 FOR i in 1..l_avc.count
569 LOOP
570 OPEN c1(p_ele_name||l_avc(i));
571 LOOP
572
573 FETCH c1 INTO l_element_type_id,l_ele_obj_ver_number;
574 EXIT WHEN c1%NOTFOUND;
575
576 l_avcren := p_ele_name || l_avctemp(i);
577
578 pay_shadow_element_api.update_shadow_element
579 (p_validate => false
580 ,p_effective_date => p_ele_eff_start_date
581 ,p_element_type_id => l_element_type_id
582 ,p_element_name => l_avcren
583 ,p_object_version_number => l_ele_obj_ver_number
584 );
585
586 END LOOP;
587 CLOSE c1;
588 END LOOP;
589
590
591
592 -------------------------------------------------------------------------
593 --
594 --
595 hr_utility.set_location(l_proc, 110);
596 ---------------------------------------------------------------------------
597 ---------------------------- Generate Core Objects ------------------------
598 ---------------------------------------------------------------------------
599
600 pay_element_template_api.generate_part1
601 (p_validate => false
602 ,p_effective_date => p_ele_eff_start_date
603 ,p_hr_only => false
604 ,p_hr_to_payroll => false
605 ,p_template_id => l_template_id);
606 --
607 hr_utility.set_location(l_proc, 120);
608 --
609 pay_element_template_api.generate_part2
610 (p_validate => false
611 ,p_effective_date => p_ele_eff_start_date
612 ,p_template_id => l_template_id);
613 --
614
615 hr_utility.set_location(l_proc, 130);
616
617 --
618 -- Update base element formula if contribution type is flat rate
619 --
620
621 IF p_emp_cont_method = 'F' THEN
622 update_eletyp_for(p_ele_name
623 ,'ONCE_EACH_PERIOD');
624 END IF; -- End if of contribution method check...
625
626 hr_utility.set_location(l_proc, 140);
627
628 --
629 -- Update Input Values Default Values
630 --
631
632 IF p_emp_contribution IS NOT NULL THEN
633
634 l_name := NULL;
635 IF p_emp_cont_method = 'F' THEN
636 l_name := 'Flat Rate Contribution';
637 ELSIF p_emp_cont_method = 'P' THEN
638 l_name := 'Percentage Contribution';
639 END IF; -- End if of contribution method check...
640
641 l_eei_information12 := TO_CHAR(p_emp_contribution);
642 update_ipval_defval(p_ele_name
643 ,l_name
644 ,p_emp_contribution);
645 END IF; -- End if of emp_contribution value check...
646
647 hr_utility.set_location(l_proc, 150);
648
649 IF p_eer_contribution = 'Y' AND
650 p_eer_rate IS NOT NULL THEN
651
652 l_name := NULL;
653 IF p_eer_type = 'P' THEN
654 l_name := 'Employers Percentage Cont';
655 ELSIF p_eer_type = 'F' THEN
656 l_name := 'Employers Factor Cont';
657 END IF; -- End if of employer contribution type check...
658
659 l_eei_information5 := TO_CHAR(p_eer_rate);
660 update_ipval_defval(p_ele_name
661 ,l_name
662 ,p_eer_rate);
663 END IF; -- End if of employer contribution check...
664
665 hr_utility.set_location(l_proc, 160);
666
667 l_base_element_type_id := get_object_id ('ELE', p_ele_name);
668
669 hr_utility.set_location(l_proc, 170);
670
671 -- Create a row in pay_element_extra_info with all the element information
672
673 pay_element_extra_info_api.create_element_extra_info
674 (p_element_type_id => l_base_element_type_id
675 ,p_information_type => 'PQP_GB_PENSION_INFORMATION'
676 ,P_EEI_INFORMATION_CATEGORY => 'PQP_GB_PENSION_INFORMATION'
677 ,p_eei_information1 => p_sch_type
678 ,p_eei_information2 => l_eei_information2
679 ,p_eei_information3 => l_eei_information3
680 ,p_eei_information4 => l_eei_information4
681 ,p_eei_information5 => l_eei_information5
682 ,p_eei_information6 => l_eei_information6
683 ,p_eei_information7 => l_eei_information7
684 ,p_eei_information8 => l_eei_information8
685 ,p_eei_information9 => l_eei_information9
686 ,p_eei_information10 => l_eei_information10
687 ,p_eei_information11 => l_eei_information11
688 ,p_eei_information12 => l_eei_information12
689 ,p_element_type_extra_info_id => l_eei_info_id
690 ,p_object_version_number => l_ovn_eei);
691
692 hr_utility.set_location('Leaving :'||l_proc, 180);
693
694 RETURN l_base_element_type_id;
695
696 --
697 END create_user_template;
698 --
699 --
700 --==========================================================================
701 -- Deletion procedure
702 --==========================================================================
703 --
704 PROCEDURE delete_user_template
705 (p_business_group_id in number
706 ,p_ele_type_id in number
707 ,p_ele_name in varchar2
708 ,p_effective_date in date
709 ) IS
710 --
711 l_template_id NUMBER(9);
712 l_proc varchar2(60) :='pqp_gb_pensions_template.delete_user_template';
713 l_eei_info_id number;
714 l_ovn_eei number;
715 --
716 CURSOR eei is
717 SELECT element_type_extra_info_id
718 FROM pay_element_type_extra_info petei
719 WHERE element_type_id=p_ele_type_id ;
720
721
722 CURSOR c1 is
723 SELECT template_id
724 FROM pay_element_templates
725 WHERE base_name = p_ele_name
726 AND business_group_id = p_business_group_id
727 AND template_type = 'U';
728 --
729 BEGIN
730 --
731 hr_utility.set_location('Entering :'||l_proc, 10);
732 --
733 OPEN eei;
734 LOOP
735 FETCH eei INTO l_eei_info_id ;
736 EXIT WHEN eei%NOTFOUND;
737
738
739 pay_element_extra_info_api.delete_element_extra_info
740 (p_validate => FALSE
741 ,p_element_type_extra_info_id => l_eei_info_id
742 ,p_object_version_number => l_ovn_eei);
743
744
745 END LOOP;
746 CLOSE eei;
747
748 FOR c1_rec in c1 loop
749 l_template_id := c1_rec.template_id;
750 END LOOP;
751 --
752
753 pay_element_template_api.delete_user_structure
754 (p_validate => false
755 ,p_drop_formula_packages => true
756 ,p_template_id => l_template_id);
757 --
758
759 hr_utility.set_location('Leaving :'||l_proc, 50);
760 --
761 END delete_user_template;
762 --
763 END pqp_gb_pensions_template;
764