[Home] [Help]
PACKAGE BODY: APPS.PAY_NL_LIFE_SAVINGS_TEMPLATE
Source
1 PACKAGE BODY PAY_NL_LIFE_SAVINGS_TEMPLATE AS
2 /* $Header: pynllssd.pkb 120.1 2007/04/11 04:29:59 rlingama noship $ */
3
4 g_proc_name VARCHAR2(80) := ' pay_nl_life_savings_template.';
5 g_debug BOOLEAN := hr_utility.debug_enabled;
6
7
8
9 -- ---------------------------------------------------------------------
10 -- |--------------------< Create_User_Template >------------------------|
11 -- ---------------------------------------------------------------------
12 FUNCTION Create_User_Template
13 (p_pension_category IN VARCHAR2
14 ,p_pension_provider_id IN NUMBER
15 ,p_pension_type_id IN NUMBER
16 ,p_scheme_prefix IN VARCHAR2
17 ,p_reporting_name IN VARCHAR2
18 ,p_scheme_description IN VARCHAR2
19 ,p_termination_rule IN VARCHAR2
20 ,p_er_component IN VARCHAR2
21 ,p_arrearage_flag IN VARCHAR2
22 ,p_ee_deduction_method IN VARCHAR2
23 ,p_er_deduction_method IN VARCHAR2
24 ,p_saving_scheme_type IN VARCHAR2
25 ,p_zvw_std_tax_chk IN VARCHAR2
26 ,p_zvw_spl_tax_chk IN VARCHAR2
27 ,p_standard_link IN VARCHAR2
28 ,p_effective_start_date IN DATE DEFAULT NULL
29 ,p_effective_end_date IN DATE DEFAULT NULL
30 ,p_security_group_id IN NUMBER DEFAULT NULL
31 ,p_business_group_id IN NUMBER
32 )
33 RETURN NUMBER IS
34 --
35 TYPE shadow_ele_rec IS RECORD
36 (element_type_id pay_shadow_element_types.element_type_id%TYPE
37 ,object_version_number
38 pay_shadow_element_types.object_version_NUMBER%TYPE
39 ,reporting_name pay_shadow_element_types.reporting_name%TYPE
40 ,description pay_shadow_element_types.description%TYPE
41 );
42 TYPE t_shadow_ele_info IS TABLE OF shadow_ele_rec
43 INDEX BY BINARY_INTEGER;
44
45 l_shadow_element t_shadow_ele_info;
46
47 TYPE t_ele_name IS TABLE OF pay_element_types_f.element_name%TYPE
48 INDEX BY BINARY_INTEGER;
49
50 l_ele_name t_ele_name;
51 l_ele_new_name t_ele_name;
52 l_main_ele_name t_ele_name;
53 l_retro_ele_name t_ele_name;
54
55 TYPE t_bal_name IS TABLE OF pay_balance_types.balance_name%TYPE
56 INDEX BY BINARY_INTEGER;
57 l_bal_name t_bal_name;
58 l_bal_new_name t_bal_name;
59
60 TYPE t_ele_reporting_name IS TABLE OF
61 pay_element_types_f.reporting_name%TYPE
62 INDEX BY BINARY_INTEGER;
63 l_ele_reporting_name t_ele_reporting_name;
64
65 TYPE t_ele_description IS TABLE OF
66 pay_element_types_f.description%TYPE
67 INDEX BY BINARY_INTEGER;
68 l_ele_description t_ele_description;
69
70 TYPE t_ele_pp IS TABLE OF
71 pay_element_types_f.processing_priority%TYPE
72 INDEX BY BINARY_INTEGER;
73 l_ele_pp t_ele_pp;
74
75 TYPE t_eei_info IS TABLE OF
76 pay_element_type_extra_info.eei_information19%TYPE
77 INDEX BY BINARY_INTEGER;
78
79 TYPE t_retro_ele IS TABLE OF
80 pay_element_types_f.element_type_id%TYPE
81 INDEX BY BINARY_INTEGER;
82
83 l_retro_element_lst t_retro_ele;
84
85 l_main_eei_info19 t_eei_info;
86 l_retro_eei_info19 t_eei_info;
87 l_ele_core_id pay_template_core_objects.core_object_id%TYPE:= -1;
88
89 --
90 -- Extra Information variables
91 --
92 l_eei_information11 pay_element_type_extra_info.eei_information9%TYPE;
93 l_eei_information12 pay_element_type_extra_info.eei_information10%TYPE;
94 l_eei_information20 pay_element_type_extra_info.eei_information18%TYPE;
95
96 --For Exclusion rules
97 l_configuration_information1 VARCHAR2(10) := 'N' ;
98 l_configuration_information2 VARCHAR2(10) := 'N' ;
99 l_configuration_information3 VARCHAR2(10) := 'Y' ;
100 l_configuration_information4 VARCHAR2(10) := 'N' ;
101 l_configuration_information5 VARCHAR2(10) := 'N' ;
102 l_configuration_information6 VARCHAR2(10) := 'N' ;
103 l_configuration_information7 VARCHAR2(10) := 'N' ;
104 l_configuration_information8 VARCHAR2(10) := 'N' ;
105 l_configuration_information9 VARCHAR2(10) := 'N' ;
106 l_configuration_information10 VARCHAR2(10) := 'N' ;
107
108 l_ee_contribution_bal_type_id
109 pqp_pension_types_f.ee_contribution_bal_type_id%TYPE;
110
111 l_er_contribution_bal_type_id
112 pqp_pension_types_f.er_contribution_bal_type_id%TYPE;
113
114 l_ee_retro_bal_id pay_balance_types.balance_type_id%TYPE;
115 l_er_retro_bal_id pay_balance_types.balance_type_id%TYPE;
116 l_pen_sal_bal_type_id
117 pqp_pension_types_f.pension_salary_balance%TYPE := -1;
118
119 l_balance_feed_Id
120 pay_balance_feeds_f.balance_feed_id%TYPE;
121
122
123 l_row_id ROWID;
124 l_request_id NUMBER;
125 l_er_request_id NUMBER;
126 l_formula_text VARCHAR2(32767);
127 l_formula_text1 VARCHAR2(32767);
128 l_tax_si_text VARCHAR2(32767);
129 l_abs_text VARCHAR2(32767);
130 l_dbi_user_name ff_database_items.user_name%TYPE;
131 l_balance_name pay_balance_types.balance_name%TYPE;
132 l_balance_dbi_name ff_database_items.user_name%TYPE;
133 l_template_id pay_shadow_element_types.template_id%TYPE;
134 l_base_element_type_id pay_template_core_objects.core_object_id%TYPE;
135 l_er_base_element_type_id pay_template_core_objects.core_object_id%TYPE;
136 l_cy_retro_element_type_id pay_template_core_objects.core_object_id%TYPE;
137 l_cy_er_retro_element_type_id pay_template_core_objects.core_object_id%TYPE;
138 l_py_retro_element_type_id pay_template_core_objects.core_object_id%TYPE;
139 l_py_er_retro_element_type_id pay_template_core_objects.core_object_id%TYPE;
140 l_xtr_element_type_id pay_template_core_objects.core_object_id%TYPE;
141 l_source_template_id pay_element_templates.template_id%TYPE;
142 l_object_version_NUMBER pay_element_types_f.object_version_NUMBER%TYPE;
143 l_proc_name VARCHAR2(80)
144 := g_proc_name || 'create_user_template';
145 l_element_type_id NUMBER;
146 l_balance_type_id NUMBER;
147 l_eei_element_type_id NUMBER;
148 l_ele_obj_ver_NUMBER NUMBER;
149 l_bal_obj_ver_NUMBER NUMBER;
150 i NUMBER;
151 li NUMBER;
152 l_eei_info_id NUMBER;
153 l_ovn_eei NUMBER;
154 l_formula_name pay_shadow_formulas.formula_name%TYPE;
155 l_formula_id NUMBER;
156 l_formula_id1 NUMBER;
157 y NUMBER := 0;
158 l_exists VARCHAR2(1);
159 l_count NUMBER := 0;
160 l_retro_count NUMBER := 0;
161 l_shad_formula_id NUMBER;
162 l_shad_formula_id1 NUMBER;
163 l_retr_1 NUMBER;
164 l_retr_2 NUMBER;
165 l_retr_3 NUMBER;
166 l_retr_4 NUMBER;
167 l_retr_5 NUMBER;
168 l_retr_6 NUMBER;
169 l_retr_7 NUMBER;
170 l_retr_8 NUMBER;
171 l_retr_9 NUMBER;
172 l_retr_10 NUMBER;
173 l_retr_11 NUMBER;
174 l_retr_12 NUMBER;
175 l_retr_13 NUMBER;
176 l_retr_14 NUMBER;
177 l_retr_15 NUMBER;
178 l_retr_16 NUMBER;
179 l_retr_17 NUMBER;
180 l_retr_18 NUMBER;
181 l_retr_19 NUMBER;
182 l_retr_20 NUMBER;
183 l_retr_21 NUMBER;
184 l_retr_22 NUMBER;
185 l_prem_replace_string VARCHAR2(5000) := ' ' ;
186 l_std_link_flag VARCHAR2(10) := 'N';
187 l_scheme_prefix VARCHAR2(50) := p_scheme_prefix;
188 l_pension_sub_category pqp_pension_types_f.pension_sub_category%TYPE;
189 l_subcat VARCHAR2(30);
190 l_conversion_rule pqp_pension_types_f.threshold_conversion_rule%TYPE;
191 l_basis_method pqp_pension_types_f.pension_basis_calc_method%TYPE;
192 l_zvw_text VARCHAR2(5000):= ' ' ;
193
194
195 --
196 CURSOR csr_get_ele_info (c_ele_name VARCHAR2) IS
197 SELECT element_type_id
198 ,object_version_NUMBER
199 FROM pay_shadow_element_types
200 WHERE template_id = l_template_id
201 AND element_name = c_ele_name;
202 --
203 CURSOR csr_get_bal_info (c_bal_name VARCHAR2) IS
204 SELECT balance_type_id
205 ,object_version_NUMBER
206 FROM pay_shadow_balance_types
207 WHERE template_id = l_template_id
208 AND balance_name = c_bal_name;
209 --
210 CURSOR csr_shd_ele (c_shd_elename VARCHAR2) IS
211 SELECT element_type_id, object_version_NUMBER
212 FROM pay_shadow_element_types
213 WHERE template_id = l_template_id
214 AND element_name = c_shd_elename;
215 --
216 CURSOR csr_ipv (c_ele_typeid NUMBER
217 ,c_effective_date DATE) IS
218 SELECT input_value_id
219 FROM pay_input_values_f
220 WHERE element_type_id = c_ele_typeid
221 AND business_group_id = p_business_group_id
222 AND NAME = 'Pay Value'
223 AND c_effective_date BETWEEN effective_start_date
224 AND effective_end_date;
225 --
226 CURSOR csr_pty1 (c_pension_type_id NUMBER
227 ,c_effective_date DATE) IS
228 SELECT *
229 FROM pqp_pension_types_f
230 WHERE pension_type_id = c_pension_type_id
231 AND business_group_id = p_business_group_id
232 AND c_effective_date BETWEEN effective_start_date
233 AND effective_end_date;
234
235 r_pty_rec pqp_pension_types_f%ROWTYPE;
236
237 CURSOR csr_get_formula_txt (c_formula_id NUMBER) IS
238 SELECT formula_text
239 FROM pay_shadow_formulas
240 WHERE formula_id = c_formula_id
241 AND template_type = 'U';
242
243 CURSOR csr_get_dbi_user_name (c_bal_type_id NUMBER) IS
244 SELECT user_name
245 FROM ff_database_items dbi
246 ,ff_route_parameter_values rpv
247 ,ff_route_parameters rp
248 ,pay_balance_dimensions pbd
249 ,pay_defined_balances pdb
250 WHERE dbi.user_entity_id = rpv.user_entity_id
251 AND rpv.route_parameter_id = rp.route_parameter_id
252 AND rp.route_id = pbd.route_id
253 AND pbd.database_item_suffix = '_PER_YTD' -- nilesh
254 and pdb.BALANCE_DIMENSION_ID = pbd.BALANCE_DIMENSION_ID
255 AND pbd.legislation_code = 'NL'
256 and pdb.balance_type_id = to_char(c_bal_type_id)
257 AND rpv.value = pdb.DEFINED_BALANCE_ID;
258
259 CURSOR csr_get_dbi_asg_user_name (c_bal_type_id NUMBER) IS
260 SELECT user_name
261 FROM ff_database_items dbi
262 ,ff_route_parameter_values rpv
263 ,ff_route_parameters rp
264 ,pay_balance_dimensions pbd
265 ,pay_defined_balances pdb
266 WHERE dbi.user_entity_id = rpv.user_entity_id -- 4472436
267 AND rpv.route_parameter_id = rp.route_parameter_id -- 1999
268 AND rp.route_id = pbd.route_id
269 AND pbd.database_item_suffix = '_ASG_RUN' -- nilesh
270 AND pdb.BALANCE_DIMENSION_ID = pbd.BALANCE_DIMENSION_ID
271 AND pbd.legislation_code = 'NL'
272 AND pdb.balance_type_id = to_char(c_bal_type_id)
273 AND rpv.value = pdb.DEFINED_BALANCE_ID ;
274
275 CURSOR chk_pension_scheme_name_cur IS
276 SELECT 'x'
277 FROM pay_element_type_extra_info
278 WHERE eei_information_category = 'PQP_NL_SAVINGS_DEDUCTIONS'
279 AND eei_information1 = p_scheme_description
280 AND rownum = 1;
281
282 CURSOR c_get_retro_bal_id(c_subcat IN VARCHAR2
283 ,c_ee_er IN VARCHAR2) IS
284 SELECT balance_type_id
285 FROM pay_balance_types_tl
286 WHERE balance_name = 'Retro '||c_subcat||' '
287 ||c_ee_er||' Contribution'
288 AND LANGUAGE = 'US';
289
290 l_scheme_dummy VARCHAR2(10);
291
292
293
294
295 -- ---------------------------------------------------------------------
296 -- |----------------------< Create_Retro_Usages >-------------------------|
297 -- ---------------------------------------------------------------------
298
299 PROCEDURE Create_Retro_Usages
300 (p_creator_name VARCHAR2,
301 p_creator_type VARCHAR2,
302 p_retro_component_priority BINARY_INTEGER,
303 p_default_component VARCHAR2,
304 p_reprocess_type VARCHAR2,
305 p_retro_element_name VARCHAR2 DEFAULT NULL,
306 p_start_time_def_name VARCHAR2 DEFAULT 'Start of Time',
307 p_end_time_def_name VARCHAR2 DEFAULT 'End of Time',
308 p_business_group_id NUMBER)
309 IS
310 l_creator_id NUMBER;
311 l_comp_name pay_retro_components.component_name%TYPE;
312 l_comp_id pay_retro_components.retro_component_id%TYPE;
313 l_comp_type pay_retro_components.retro_type%TYPE;
314 l_rc_usage_id pay_retro_component_usages.Retro_Component_Usage_Id%TYPE;
315 l_retro_ele_id pay_element_types_f.element_type_id%TYPE;
316 l_time_span_id pay_time_spans.time_span_id%TYPE;
317 l_es_usage_id pay_element_span_usages.element_span_usage_id%TYPE;
318 l_proc_name VARCHAR2(80);
319 --
320 --
321 --
322 BEGIN
323 l_proc_name := g_proc_name||'Create_Retro_Usages';
324
325 IF g_debug THEN
326 hr_utility.set_location('Entering: '||l_proc_name, 10);
327 END IF;
328 --
329 IF g_creator.name = p_creator_name AND
330 g_creator.type = p_creator_type
331 THEN
332 l_creator_id := g_creator.id;
333 ELSE
334 --
335 -- Prime creator cache
336 --
337 IF p_creator_type = 'ET' THEN
338 SELECT DISTINCT element_type_id
339 INTO l_creator_id
340 FROM pay_element_types_f
341 WHERE element_name = p_creator_name
342 AND business_group_id = p_business_group_id;
343 ELSIF p_creator_type = 'EC' THEN
344 SELECT classification_id
345 INTO l_creator_id
346 FROM pay_element_classifications
347 WHERE classification_name = p_creator_name
348 AND business_group_id = p_business_group_id;
349 ELSE
350 RAISE no_data_found;
351 END IF;
352
353 g_creator.name := p_creator_name;
354 g_creator.type := p_creator_type;
355 g_creator.id := l_creator_id;
356
357 END IF;
358 --
359 IF g_component.EXISTS(p_retro_component_priority) THEN
360 l_comp_name := g_component(p_retro_component_priority).NAME;
361 l_comp_type := g_component(p_retro_component_priority).TYPE;
362 l_comp_id := g_component(p_retro_component_priority).id;
363 ELSE
364 -- prime component cache
365 SELECT rc.retro_component_id,rc.component_name, rc.retro_type
366 INTO l_comp_id, l_comp_name, l_comp_type
367 FROM pay_retro_definitions rd,
368 pay_retro_defn_components rdc,
369 pay_retro_components rc
370 WHERE rdc.retro_component_id = rc.retro_component_id
371 AND rc.legislation_code = g_legislation_code
372 AND rdc.priority = p_retro_component_priority
373 AND rd.retro_definition_id = rdc.retro_definition_id
374 AND rd.legislation_code = g_legislation_code
375 AND rd.definition_name = g_retro_def_name;
376 --
377 g_component(p_retro_component_priority).NAME := l_comp_name;
378 g_component(p_retro_component_priority).TYPE := l_comp_type;
379 g_component(p_retro_component_priority).id := l_comp_id;
380 END IF;
381 --
382 IF l_comp_type = 'F' AND p_reprocess_type <> 'R' THEN
383 RAISE no_data_found;
384 END IF;
385 --
386 BEGIN
387 SELECT Retro_Component_Usage_Id
388 INTO l_rc_usage_id
389 FROM pay_retro_component_usages
390 WHERE retro_component_id = l_comp_id
391 AND creator_id = l_creator_id
392 AND creator_type = p_creator_type;
393 EXCEPTION WHEN no_data_found THEN
394 SELECT pay_retro_component_usages_s.NEXTVAL
395 INTO l_rc_usage_id
396 FROM dual;
397 --
398 IF g_debug THEN
399 hr_utility.set_location('Insert Retro Comp Usgs '||l_proc_name, 20);
400 END IF;
401
402 INSERT INTO pay_retro_component_usages(
403 RETRO_COMPONENT_USAGE_ID,
404 RETRO_COMPONENT_ID,
405 CREATOR_ID,
406 CREATOR_TYPE,
407 DEFAULT_COMPONENT,
408 REPROCESS_TYPE,
409 BUSINESS_GROUP_ID,
410 LEGISLATION_CODE,
411 CREATION_DATE,
412 CREATED_BY,
413 LAST_UPDATE_DATE,
414 LAST_UPDATED_BY,
415 LAST_UPDATE_LOGIN,
416 OBJECT_VERSION_NUMBER)
417 VALUES(l_rc_usage_id
418 ,l_comp_id
419 ,l_creator_id
420 ,p_creator_type
421 ,p_default_component
422 ,p_reprocess_type
423 ,p_business_group_id
424 ,NULL
425 ,SYSDATE
426 ,-1
427 ,SYSDATE
428 ,-1
429 ,-1
430 ,1);
431 END;
432 --
433 IF p_retro_element_name IS NOT NULL AND p_creator_type='ET' THEN
434 IF g_component(p_retro_component_priority).start_time_def_name
435 = p_start_time_def_name
436 AND g_component(p_retro_component_priority).end_time_def_name
437 = p_end_time_def_name
438 THEN
439 l_time_span_id := g_component(p_retro_component_priority).time_span_id;
440 ELSE
441 -- Prime cache
442 SELECT ts.time_span_id
443 INTO l_time_span_id
444 FROM pay_time_definitions s,
445 pay_time_definitions e,
446 pay_time_spans ts
447 WHERE ts.creator_id = l_comp_id
448 AND ts.creator_type = 'RC'
449 AND ts.start_time_def_id = s.time_definition_id
450 AND ts.end_time_def_id = e.time_definition_id
451 AND s.legislation_code = 'NL'
452 AND s.definition_name = p_start_time_def_name
453 AND e.legislation_code = 'NL'
454 AND e.definition_name = p_end_time_def_name;
455
456 g_component(p_retro_component_priority).time_span_id := l_time_span_id;
457 g_component(p_retro_component_priority).start_time_def_name
458 := p_start_time_def_name;
459 g_component(p_retro_component_priority).end_time_def_name
460 := p_end_time_def_name;
461 END IF;
462 --
463 SELECT DISTINCT element_type_id
464 INTO l_retro_ele_id
465 FROM pay_element_types_f
466 WHERE element_name = p_retro_element_name
467 AND business_group_id = p_business_group_id;
468 --
469 BEGIN
470 SELECT element_span_usage_id
471 INTO l_es_usage_id
472 FROM pay_element_span_usages
473 WHERE time_span_id = l_time_span_id
474 AND retro_component_usage_id = l_rc_usage_id
475 AND adjustment_type IS NULL;
476
477 EXCEPTION WHEN no_data_found THEN
478 SELECT pay_element_span_usages_s.NEXTVAL
479 INTO l_es_usage_id
480 FROM dual;
481
482 IF g_debug THEN
483 hr_utility.set_location('Insert Element Span Usgs '||l_proc_name, 30);
484 END IF;
485
486 INSERT INTO pay_element_span_usages(
487 ELEMENT_SPAN_USAGE_ID,
488 BUSINESS_GROUP_ID,
489 LEGISLATION_CODE,
490 TIME_SPAN_ID,
491 RETRO_COMPONENT_USAGE_ID,
492 ADJUSTMENT_TYPE,
493 RETRO_ELEMENT_TYPE_ID,
494 CREATION_DATE,
495 CREATED_BY,
496 LAST_UPDATE_DATE,
497 LAST_UPDATED_BY,
498 LAST_UPDATE_LOGIN,
499 OBJECT_VERSION_NUMBER)
500 VALUES(l_es_usage_id
501 ,p_business_group_id
502 ,NULL
503 ,l_time_span_id
504 ,l_rc_usage_id
505 ,NULL
506 ,l_retro_ele_id
507 ,SYSDATE
508 ,-1
509 ,SYSDATE
510 ,-1
511 ,-1
512 ,1);
513 END;
514 END IF;
515
516 IF g_debug THEN
517 hr_utility.set_location('Leaving '||l_proc_name, 40);
518 END IF;
519 --
520 EXCEPTION WHEN no_data_found THEN
521 NULL;
522 END Create_Retro_Usages;
523
524
525 -- ---------------------------------------------------------------------
526 -- |----------------------< Update_Event_Group >-------------------------|
527 -- ---------------------------------------------------------------------
528
529 PROCEDURE Update_Event_Group
530 (p_element_name VARCHAR2,
531 p_business_group_id NUMBER)
532 IS
533
534 CURSOR c_get_retro_evg_id IS
535 SELECT event_group_id
536 FROM pay_event_groups
537 WHERE event_group_name = 'PQP_NL_RETRO_EVG'
538 AND legislation_code = 'NL';
539
540 l_retro_evg_id NUMBER;
541 l_proc_name VARCHAR2(80);
542 --
543 --
544 --
545 BEGIN
546 l_proc_name := g_proc_name||'Update_Event_Group';
547
548 IF g_debug THEN
549 hr_utility.set_location('Entering: '||l_proc_name, 10);
550 END IF;
551
552 --Query up the retro event group id
553 OPEN c_get_retro_evg_id;
554 FETCH c_get_retro_evg_id INTO l_retro_evg_id;
555 IF c_get_retro_evg_id%FOUND THEN
556 hr_utility.set_location('Retro EVG id found: '||l_retro_evg_id,20);
557 CLOSE c_get_retro_evg_id;
558
559 --now update the elements with this recalc event grp id
560 UPDATE pay_element_types_f
561 SET recalc_event_group_id = l_retro_evg_id
562 WHERE element_name = p_element_name
563 AND business_group_id = p_business_group_id;
564 ELSE
565 --evg id was not found
566 hr_utility.set_location('Retro EVG id not found',30);
567 CLOSE c_get_retro_evg_id;
568 END IF;
569
570 IF g_debug THEN
571 hr_utility.set_location('Leaving: '||l_proc_name, 40);
572 END IF;
573
574 EXCEPTION WHEN OTHERS THEN
575 NULL;
576
577 END Update_Event_Group;
578
579
580 -- ---------------------------------------------------------------------
581 -- |------------------------< Get_Template_ID >-------------------------|
582 -- ---------------------------------------------------------------------
583 FUNCTION Get_Template_Id (p_legislation_code IN VARCHAR2)
584 RETURN NUMBER IS
585 --
586 l_template_name VARCHAR2(80);
587 l_proc_name VARCHAR2(72) := g_proc_name || 'get_template_id';
588 --
589 CURSOR csr_get_temp_id IS
590 SELECT template_id
591 FROM pay_element_templates
592 WHERE template_name = l_template_name
593 AND legislation_code = p_legislation_code
594 AND template_type = 'T'
595 AND business_group_id IS NULL;
596 --
597 BEGIN
598 --
599 IF g_debug THEN
600 hr_utility.set_location('Entering: '||l_proc_name, 10);
601 END IF;
602 --
603 l_template_name := 'Dutch Life Savings Scheme Deduction';
604 --
605 IF g_debug THEN
606 hr_utility.set_location(l_proc_name, 20);
607 END IF;
608 --
609 FOR csr_get_temp_id_rec IN csr_get_temp_id LOOP
610 l_template_id := csr_get_temp_id_rec.template_id;
611 END LOOP;
612 --
613 IF g_debug THEN
614 hr_utility.set_location('Leaving: '||l_proc_name, 30);
615 END IF;
616 --
617 RETURN l_template_id;
618 --
619 END Get_Template_ID;
620
621 -- ---------------------------------------------------------------------
622 -- |------------------------< Update_Input_Vaue_Col >-------------------------|
623 -- ---------------------------------------------------------------------
624 PROCEDURE Update_Input_Vaue_Col ( c_element_type_id IN NUMBER,
625 c_iv_name IN VARCHAR2,
626 c_business_group_id IN NUMBER,
627 c_mandatory_flag IN CHAR
628 )
629 IS
630
631 l_proc_name VARCHAR2(72) := g_proc_name || 'Update_Input_Vaue_Col';
632
633 BEGIN
634
635 hr_utility.set_location('Entering : '||l_proc_name, 10);
636
637 --
638 update pay_input_values_f
639 set mandatory_flag = c_mandatory_flag
640 where element_type_id =c_element_type_id
641 and name = c_iv_name
642 and business_group_id = c_business_group_id;
643
644 hr_utility.set_location('Leaving : '||l_proc_name, 50);
645
646 END Update_Input_Vaue_Col;
647
648 BEGIN
649 -- ---------------------------------------------------------------------
650 -- |-------------< Main Function : Create_User_Template Body >----------|
651 -- ---------------------------------------------------------------------
652 IF g_debug THEN
653 hr_utility.set_location('Entering : '||l_proc_name, 10);
654 END IF;
655
656 IF LENGTH(p_scheme_prefix) > 8 THEN
657
658 -- Limit the prefix length due to DBI's
659 -- generated for the element names. Max of 8 chars is allowed.
660 --
661 fnd_message.set_name('PQP','PQP_230300_SAV_PREFIX_LEN_ERR');
662 fnd_message.raise_error;
663 END IF;
664
665 --
666 -- Check the format of the prefix name entered.
667 --
668 pqp_nl_pension_template.chk_scheme_prefix(p_scheme_prefix);
669
670 IF g_debug THEN
671 hr_utility.set_location('Check unique scheme name : '||l_proc_name, 11);
672 END IF;
673
674 --
675 -- Check if the scheme being created is already in use.
676 --
677 OPEN chk_pension_scheme_name_cur;
678 FETCH chk_pension_scheme_name_cur INTO l_scheme_dummy;
679 IF chk_pension_scheme_name_cur%FOUND THEN
680 CLOSE chk_pension_scheme_name_cur;
681 fnd_message.set_name('PQP','PQP_230924_SCHEME_NAME_ERR');
682 fnd_message.raise_error;
683 ELSE
684 CLOSE chk_pension_scheme_name_cur;
685 END IF;
686
687 --
688 -- Fetch all pension type details
689 --
690 IF g_debug THEN
691 hr_utility.set_location('Fetching PT Details : '||l_proc_name, 12);
692 END IF;
693
694 OPEN csr_pty1 (c_pension_type_id => p_pension_type_id
695 ,c_effective_date => p_effective_start_date);
696 FETCH csr_pty1 INTO r_pty_rec;
697 --
698 IF csr_pty1%NOTFOUND THEN
699 fnd_message.set_name('PQP', 'PQP_230805_INV_PENSIONID');
700 fnd_message.raise_error;
701 END IF;
702 --
703 CLOSE csr_pty1;
704
705 l_pension_sub_category := r_pty_rec.pension_sub_category;
706 l_conversion_rule := r_pty_rec.threshold_conversion_rule;
707 l_basis_method := r_pty_rec.pension_basis_calc_method;
708
709
710 -- ---------------------------------------------------------------------
711 -- Set session date to the start date of the Life Savings Scheme
712 -- ---------------------------------------------------------------------
713 pay_db_pay_setup.set_session_date(NVL(p_effective_start_date, SYSDATE));
714 --
715 IF g_debug THEN
716 hr_utility.set_location('..Setting the Session Date', 15);
717 END IF;
718 -- ---------------------------------------------------------------------
719 -- Get Source Template Id for the PGGM template
720 -- ---------------------------------------------------------------------
721 l_source_template_id := get_template_id
722 (p_legislation_code => g_template_leg_code);
723 IF g_debug THEN
724 hr_utility.set_location('Derived the Src Template id', 15);
725 END IF;
726 -- ---------------------------------------------------------------------
727 -- Exclusion rules
728 -- ---------------------------------------------------------------------
729 IF g_debug THEN
730 hr_utility.set_location('..Checking all the Exclusion Rules', 20);
731 END IF;
732
733 -- Define the exclusion rules
734
735 IF p_er_component ='Y' THEN
736 l_configuration_information1 := 'Y';
737 END IF;
738
739 IF l_basis_method = '13' THEN
740 l_configuration_information2 := 'Y';
741 ELSE
742 l_configuration_information3 := 'N';
743 END IF;
744
745 IF p_arrearage_flag = 'Y' THEN
746 l_configuration_information4 := 'Y';
747 END IF;
748
749 IF l_basis_method = '13' AND p_ee_deduction_method ='PE' THEN
750 l_configuration_information5 := 'Y';
751 END IF;
752
753 IF r_pty_rec.std_tax_reduction IS NOT NULL THEN
754 l_configuration_information6 := 'Y';
755 END IF;
756
757 IF r_pty_rec.spl_tax_reduction IS NOT NULL THEN
758 l_configuration_information7 := 'Y';
759 END IF;
760
761
762 IF p_zvw_std_tax_chk ='Y' THEN
763 l_configuration_information9 := 'Y';
764 END IF;
765
766 IF p_zvw_std_tax_chk ='Y' THEN
767 l_configuration_information10 := 'Y';
768 END IF;
769
770
771 -- ---------------------------------------------------------------------
772 -- Create user structure from the template
773 -- ---------------------------------------------------------------------
774 IF g_debug THEN
775 hr_utility.set_location('..Creating template User structure', 25);
776 END IF;
777
778 pay_element_template_api.create_user_structure
779 (p_validate => FALSE
780 ,p_effective_date => p_effective_start_date
781 ,p_business_group_id => p_business_group_id
782 ,p_source_template_id => l_source_template_id
783 ,p_base_name => p_scheme_prefix
784 ,p_configuration_information1 => l_configuration_information1
785 ,p_configuration_information2 => l_configuration_information2
786 ,p_configuration_information3 => l_configuration_information3
787 ,p_configuration_information4 => l_configuration_information4
788 ,p_configuration_information5 => l_configuration_information5
789 ,p_configuration_information6 => l_configuration_information6
790 ,p_configuration_information7 => l_configuration_information7
791 ,p_configuration_information8 => l_configuration_information8
792 ,p_configuration_information9 => l_configuration_information9
793 ,p_configuration_information10 => l_configuration_information10
794 ,p_template_id => l_template_id
795 ,p_object_version_number => l_object_version_number
796 );
797
798 IF g_debug THEN
799 hr_utility.set_location('Done Creating User structure', 26);
800 hr_utility.set_location('Deriving element typ ids', 27);
801 END IF;
802
803 -- ---------------------------------------------------------------------
804 -- |-------------------< Update Shadow Structure >----------------------|
805 -- ---------------------------------------------------------------------
806 -- Get Element Type id and update user-specified Classification,
807 -- Category, Processing Type and Standard Link on Base Element
808 -- as well as other element created for the Scheme
809 -- ---------------------------------------------------------------------
810
811 -- <BASE NAME> Employee Savings Deduction
812
813 FOR csr_rec IN csr_shd_ele (p_scheme_prefix||' Employee Savings Deduction')
814 LOOP
815 l_count := l_count + 1;
816 l_shadow_element(l_count).element_type_id
817 := csr_rec.element_type_id;
818 l_shadow_element(l_count).object_version_number
819 := csr_rec.object_version_number;
820 l_shadow_element(l_count).reporting_name
821 := NVL(p_reporting_name,p_scheme_prefix)||' Life Savings EE';
822 l_shadow_element(l_count).description
823 := 'Element for '||p_scheme_prefix||' Employee Savings Deduction';
824 END LOOP;
825
826 -- <BASE NAME> Retro Employee Savings Deduction Current Year
827
828 FOR csr_rec IN csr_shd_ele (p_scheme_prefix
829 ||' Retro Employee Savings Deduction Current Year')
830 LOOP
831 l_count := l_count + 1;
832 l_shadow_element(l_count).element_type_id
833 := csr_rec.element_type_id;
834 l_shadow_element(l_count).object_version_NUMBER
835 := csr_rec.object_version_NUMBER;
836 l_shadow_element(l_count).reporting_name
837 := NVL(p_reporting_name,p_scheme_prefix)||' Retro Employee Savings CY';
838 l_shadow_element(l_count).description
839 := 'Element for '||p_scheme_prefix
840 ||' Retro Employee Savings Deduction Current Year';
841 END LOOP;
842
843 -- <BASE NAME> Retro Employee Savings Deduction Previous Year
844
845 FOR csr_rec IN csr_shd_ele (p_scheme_prefix
846 ||' Retro Employee Savings Deduction Previous Year')
847 LOOP
848 l_count := l_count + 1;
849 l_shadow_element(l_count).element_type_id
850 := csr_rec.element_type_id;
851 l_shadow_element(l_count).object_version_NUMBER
852 := csr_rec.object_version_NUMBER;
853 l_shadow_element(l_count).reporting_name
854 := NVL(p_reporting_name,p_scheme_prefix)
855 ||' Retro Life Savings PY';
856 l_shadow_element(l_count).description
857 := 'Element for '||p_scheme_prefix
858 ||' Retro Employee Savings Deduction Previous Year';
859 END LOOP;
860
861 -- <BASE NAME> Employer Savings Contribution
862
863 IF p_er_component = 'Y' THEN
864 for csr_rec in csr_shd_ele (p_scheme_prefix||' Employer Savings Contribution')
865 loop
866 l_count := l_count + 1;
867 l_shadow_element(l_count).element_type_id := csr_rec.element_type_id;
868 l_shadow_element(l_count).object_version_number := csr_rec.object_version_number;
869 l_shadow_element(l_count).reporting_name := nvl(p_reporting_name,p_scheme_prefix)
870 ||' ER Savings Contribution';
871 l_shadow_element(l_count).description := 'Element for '||p_scheme_prefix
872 ||' Employer Savings Contribution';
873 end loop;
874 END IF;
875
876 -- <BASE NAME> Retro Employer Savings Contribution Current Year
877
878 IF p_er_component = 'Y' THEN
879 for csr_rec in csr_shd_ele (p_scheme_prefix||' Retro Employer Savings Contribution Current Year')
880 loop
881 l_count := l_count + 1;
882 l_shadow_element(l_count).element_type_id := csr_rec.element_type_id;
883 l_shadow_element(l_count).object_version_number := csr_rec.object_version_number;
884 l_shadow_element(l_count).reporting_name := nvl(p_reporting_name,p_scheme_prefix)
885 ||' Retro ER Savings Contribution Current Year';
886 l_shadow_element(l_count).description := 'Element for '||p_scheme_prefix
887 ||' Retro Employer Savings Contribution Current Year';
888 end loop;
889 END IF;
890
891 -- <BASE NAME> Retro Employer Savings Contribution Previous Year
892
893 IF p_er_component = 'Y' THEN
894 for csr_rec in csr_shd_ele (p_scheme_prefix||' Retro Employer Savings Contribution Previous Year')
895 loop
896 l_count := l_count + 1;
897 l_shadow_element(l_count).element_type_id := csr_rec.element_type_id;
898 l_shadow_element(l_count).object_version_number := csr_rec.object_version_number;
899 l_shadow_element(l_count).reporting_name := nvl(p_reporting_name,p_scheme_prefix)
900 ||' Retro ER Savings Contribution Previous Year';
901 l_shadow_element(l_count).description := 'Element for '||p_scheme_prefix
902 ||' Retro Employer Savings Contribution Previous Year';
903 end loop;
904 END IF;
905
906 -- <BASE NAME> Special Inputs
907
908 FOR csr_rec IN csr_shd_ele (p_scheme_prefix
909 ||' Special Inputs')
910 LOOP
911 l_count := l_count + 1;
912 l_shadow_element(l_count).element_type_id
913 := csr_rec.element_type_id;
914 l_shadow_element(l_count).object_version_NUMBER
915 := csr_rec.object_version_NUMBER;
916 l_shadow_element(l_count).reporting_name
917 := NVL(p_reporting_name,p_scheme_prefix)
918 ||' Special Inputs';
919 l_shadow_element(l_count).description
920 := 'Element for '||p_scheme_prefix
921 ||' Special Inputs';
922 END LOOP;
923
924
925 -- <BASE NAME> Special Features
926
927 FOR csr_rec IN csr_shd_ele (p_scheme_prefix
928 ||' Special Features')
929 LOOP
930 l_count := l_count + 1;
931 l_shadow_element(l_count).element_type_id
932 := csr_rec.element_type_id;
933 l_shadow_element(l_count).object_version_NUMBER
934 := csr_rec.object_version_NUMBER;
935 l_shadow_element(l_count).reporting_name
936 := NVL(p_reporting_name,p_scheme_prefix)
937 ||' Special Features';
938 l_shadow_element(l_count).description
939 := 'Element for '||p_scheme_prefix
940 ||' Special Features';
941 END LOOP;
942
943 -- <BASE NAME> Tax SI Adjustment
944
945 FOR csr_rec IN csr_shd_ele (p_scheme_prefix||' Tax SI Adjustment')
946 LOOP
947 l_count := l_count + 1;
948 l_shadow_element(l_count).element_type_id
949 := csr_rec.element_type_id;
950 l_shadow_element(l_count).object_version_NUMBER
951 := csr_rec.object_version_NUMBER;
952 l_shadow_element(l_count).reporting_name
953 := NVL(p_reporting_name,p_scheme_prefix)||' Tax SI Adjustment';
954 l_shadow_element(l_count).description
955 := 'Element for '||p_scheme_prefix||' Tax SI Adjustment';
956 END LOOP;
957
958 -- <BASE NAME> Standard Tax Adjustment
959
960 FOR csr_rec IN csr_shd_ele (p_scheme_prefix||' Standard Tax Adjustment')
961 LOOP
962 l_count := l_count + 1;
963 l_shadow_element(l_count).element_type_id
964 := csr_rec.element_type_id;
965 l_shadow_element(l_count).object_version_NUMBER
966 := csr_rec.object_version_NUMBER;
967 l_shadow_element(l_count).reporting_name
968 := NVL(p_reporting_name,p_scheme_prefix)||' Std. Tax Adj.';
969 l_shadow_element(l_count).description
970 := 'Element for '||p_scheme_prefix||' Standard Tax Adjustment';
971 END LOOP;
972
973 -- <BASE NAME> Retro Standard Tax Adjustment Current Year
974
975 FOR csr_rec IN csr_shd_ele (p_scheme_prefix
976 ||' Retro Standard Tax Adjustment Current Year')
977 LOOP
978 l_count := l_count + 1;
979 l_shadow_element(l_count).element_type_id
980 := csr_rec.element_type_id;
981 l_shadow_element(l_count).object_version_NUMBER
982 := csr_rec.object_version_NUMBER;
983 l_shadow_element(l_count).reporting_name
984 := NVL(p_reporting_name,p_scheme_prefix)
985 ||' Retro Std. Tax Adj CY';
986 l_shadow_element(l_count).description
987 := 'Element for '||p_scheme_prefix
988 ||' Retro Standard Tax Adjustment Current Year';
989 END LOOP;
990
991
992 -- <BASE NAME> Retro Standard Tax Adjustment Previous Year
993
994 FOR csr_rec IN csr_shd_ele (p_scheme_prefix
995 ||' Retro Standard Tax Adjustment Previous Year')
996 LOOP
997 l_count := l_count + 1;
998 l_shadow_element(l_count).element_type_id
999 := csr_rec.element_type_id;
1000 l_shadow_element(l_count).object_version_NUMBER
1001 := csr_rec.object_version_NUMBER;
1002 l_shadow_element(l_count).reporting_name
1003 := NVL(p_reporting_name,p_scheme_prefix)
1004 ||' Retro Std. Tax Adj PY';
1005 l_shadow_element(l_count).description
1006 := 'Element for '||p_scheme_prefix
1007 ||' Retro Standard Tax Adjustment Previous Year';
1008 END LOOP;
1009
1010 -- <BASE NAME> Special Tax Adjustment
1011
1012 FOR csr_rec IN csr_shd_ele (p_scheme_prefix||' Special Tax Adjustment')
1013 LOOP
1014 l_count := l_count + 1 ;
1015 l_shadow_element(l_count).element_type_id
1016 := csr_rec.element_type_id;
1017 l_shadow_element(l_count).object_version_NUMBER
1018 := csr_rec.object_version_NUMBER;
1019 l_shadow_element(l_count).reporting_name
1020 := NVL(p_reporting_name,p_scheme_prefix)||' Spl. Tax Adj.';
1021 l_shadow_element(l_count).description
1022 := 'Element for '||p_scheme_prefix||' Special Tax Adjustment';
1023 END LOOP;
1024
1025 -- <BASE NAME> Retro Special Tax Adjustment Current Year
1026
1027 FOR csr_rec IN csr_shd_ele (p_scheme_prefix
1028 ||' Retro Special Tax Adjustment Current Year')
1029 LOOP
1030 l_count := l_count + 1;
1031 l_shadow_element(l_count).element_type_id
1032 := csr_rec.element_type_id;
1033 l_shadow_element(l_count).object_version_NUMBER
1034 := csr_rec.object_version_NUMBER;
1035 l_shadow_element(l_count).reporting_name
1036 := NVL(p_reporting_name,p_scheme_prefix)
1037 ||' Retro Spl. Tax Adj CY';
1038 l_shadow_element(l_count).description
1039 := 'Element for '||p_scheme_prefix
1040 ||' Retro Special Tax Adjustment Current Year';
1041 END LOOP;
1042
1043 -- <BASE NAME> Retro Special Tax Adjustment Previous Year
1044
1045 FOR csr_rec IN csr_shd_ele (p_scheme_prefix
1046 ||' Retro Special Tax Adjustment Previous Year')
1047 LOOP
1048 l_count := l_count + 1;
1049 l_shadow_element(l_count).element_type_id
1050 := csr_rec.element_type_id;
1051 l_shadow_element(l_count).object_version_NUMBER
1052 := csr_rec.object_version_NUMBER;
1053 l_shadow_element(l_count).reporting_name
1054 := NVL(p_reporting_name,p_scheme_prefix)
1055 ||' Retro Spl. Tax Adj PY';
1056 l_shadow_element(l_count).description
1057 := 'Element for '||p_scheme_prefix
1058 ||' Retro Special Tax Adjustment Previous Year';
1059 END LOOP;
1060
1061 -- <BASE NAME> ZVW Income Standard Tax Adjustment
1062
1063 FOR csr_rec IN csr_shd_ele (p_scheme_prefix||' ZVW Income Standard Tax Adjustment')
1064 LOOP
1065 l_count := l_count + 1;
1066 l_shadow_element(l_count).element_type_id
1067 := csr_rec.element_type_id;
1068 l_shadow_element(l_count).object_version_NUMBER
1069 := csr_rec.object_version_NUMBER;
1070 l_shadow_element(l_count).reporting_name
1071 := NVL(p_reporting_name,p_scheme_prefix)||' ZVW Income Std. Tax Adj.';
1072 l_shadow_element(l_count).description
1073 := 'Element for '||p_scheme_prefix||' ZVW Income Standard Tax Adjustment';
1074 END LOOP;
1075
1076 -- <BASE NAME> Retro ZVW Income Standard Tax Adjustment Current Year
1077
1078 FOR csr_rec IN csr_shd_ele (p_scheme_prefix||' Retro ZVW Income Standard Tax Adjustment Current Year')
1079 LOOP
1080 l_count := l_count + 1;
1081 l_shadow_element(l_count).element_type_id
1082 := csr_rec.element_type_id;
1083 l_shadow_element(l_count).object_version_NUMBER
1084 := csr_rec.object_version_NUMBER;
1085 l_shadow_element(l_count).reporting_name
1086 := NVL(p_reporting_name,p_scheme_prefix)||' Retro ZVW Income Std. Tax Adj. CY';
1087 l_shadow_element(l_count).description
1088 := 'Element for '||p_scheme_prefix||' Retro ZVW Income Standard Tax Adjustment Current Year';
1089 END LOOP;
1090
1091 -- <BASE NAME> Retro ZVW Income Standard Tax Adjustment Previous Year
1092
1093 FOR csr_rec IN csr_shd_ele (p_scheme_prefix||' Retro ZVW Income Standard Tax Adjustment Previous Year')
1094 LOOP
1095 l_count := l_count + 1;
1096 l_shadow_element(l_count).element_type_id
1097 := csr_rec.element_type_id;
1098 l_shadow_element(l_count).object_version_NUMBER
1099 := csr_rec.object_version_NUMBER;
1100 l_shadow_element(l_count).reporting_name
1101 := NVL(p_reporting_name,p_scheme_prefix)||' Retro ZVW Income Std. Tax Adj. PY';
1102 l_shadow_element(l_count).description
1103 := 'Element for '||p_scheme_prefix||' ZVW Income Standard Tax Adjustment Previous Year';
1104 END LOOP;
1105
1106 -- <BASE NAME> ZVW Income Special Tax Adjustment
1107
1108 FOR csr_rec IN csr_shd_ele (p_scheme_prefix||' ZVW Income Special Tax Adjustment')
1109 LOOP
1110 l_count := l_count + 1;
1111 l_shadow_element(l_count).element_type_id
1112 := csr_rec.element_type_id;
1113 l_shadow_element(l_count).object_version_NUMBER
1114 := csr_rec.object_version_NUMBER;
1115 l_shadow_element(l_count).reporting_name
1116 := NVL(p_reporting_name,p_scheme_prefix)||' ZVW Income Spl. Tax Adj.';
1117 l_shadow_element(l_count).description
1118 := 'Element for '||p_scheme_prefix||' ZVW Income Special Tax Adjustment';
1119 END LOOP;
1120
1121 -- <BASE NAME> Retro ZVW Income Special Tax Adjustment Current Year
1122
1123 FOR csr_rec IN csr_shd_ele (p_scheme_prefix||' Retro ZVW Income Special Tax Adjustment Current Year')
1124 LOOP
1125 l_count := l_count + 1;
1126 l_shadow_element(l_count).element_type_id
1127 := csr_rec.element_type_id;
1128 l_shadow_element(l_count).object_version_NUMBER
1129 := csr_rec.object_version_NUMBER;
1130 l_shadow_element(l_count).reporting_name
1131 := NVL(p_reporting_name,p_scheme_prefix)||' Retro ZVW Income Spl. Tax Adj. CY';
1132 l_shadow_element(l_count).description
1133 := 'Element for '||p_scheme_prefix||' Retro ZVW Income Special Tax Adjustment Current Year';
1134 END LOOP;
1135
1136 -- <BASE NAME> Retro ZVW Income Special Tax Adjustment Previous Year
1137
1138 FOR csr_rec IN csr_shd_ele (p_scheme_prefix||' Retro ZVW Income Special Tax Adjustment Previous Year')
1139 LOOP
1140 l_count := l_count + 1;
1141 l_shadow_element(l_count).element_type_id
1142 := csr_rec.element_type_id;
1143 l_shadow_element(l_count).object_version_NUMBER
1144 := csr_rec.object_version_NUMBER;
1145 l_shadow_element(l_count).reporting_name
1146 := NVL(p_reporting_name,p_scheme_prefix)||' Retro ZVW Income Spl. Tax Adj. PY';
1147 l_shadow_element(l_count).description
1148 := 'Element for '||p_scheme_prefix||' ZVW Income Special Tax Adjustment Previous Year';
1149 END LOOP;
1150
1151
1152 IF g_debug THEN
1153 hr_utility.set_location('Finished deriving element typ ids', 28);
1154 hr_utility.set_location('..Updating the scheme shadow elements', 30);
1155 END IF;
1156
1157 FOR i IN 1..l_count
1158 LOOP
1159 pay_shadow_element_api.update_shadow_element
1160 (p_validate => FALSE
1161 ,p_effective_date => p_effective_start_date
1162 ,p_element_type_id => l_shadow_element(i).element_type_id
1163 ,p_description => l_shadow_element(i).description
1164 ,p_reporting_name => l_shadow_element(i).reporting_name
1165 ,p_post_termination_rule => p_termination_rule
1166 ,p_object_version_number => l_shadow_element(i).object_version_number
1167 );
1168 END LOOP;
1169
1170 IF g_debug THEN
1171 hr_utility.set_location('..After Updating the scheme shadow elements', 50);
1172 END IF;
1173 --
1174 -- Replace the spaces in the prefix with underscores. The formula name
1175 -- has underscores if the prefix name has spaces in it .
1176 --
1177 l_scheme_prefix := UPPER(REPLACE(l_scheme_prefix,' ','_'));
1178
1179 --
1180 -- Update Shadow formula
1181 --
1182 l_shad_formula_id := pqp_nl_pension_template.Get_Formula_Id
1183 (l_scheme_prefix||'_EMPLOYEE_LIFE_SAVINGS_DEDUCTION'
1184 ,p_business_group_id);
1185
1186 IF g_debug THEN
1187 hr_utility.set_location('Replacing Balance Name in the formula', 51);
1188 END IF;
1189
1190 IF r_pty_rec.ee_contribution_bal_type_id IS NOT NULL THEN
1191
1192 FOR temp_rec IN csr_get_formula_txt(l_shad_formula_id)
1193 LOOP
1194 l_formula_text := temp_rec.formula_text;
1195 END LOOP;
1196
1197 FOR temp_rec IN
1198 csr_get_dbi_asg_user_name(r_pty_rec.ee_contribution_bal_type_id)
1199 LOOP
1200 l_dbi_user_name := temp_rec.user_name;
1201 l_formula_text := REPLACE(l_formula_text,
1202 'REPLACE_PT_EE_CUSTOM_BAL_ASG_RUN',
1203 l_dbi_user_name);
1204
1205 UPDATE pay_shadow_formulas
1206 SET formula_text = l_formula_text
1207 WHERE formula_id = l_shad_formula_id
1208 AND business_group_id = p_business_group_id;
1209
1210 END LOOP;
1211 ELSE
1212
1213 --If balance information does not exist then replace text with null string
1214 FOR temp_rec IN csr_get_formula_txt(l_shad_formula_id)
1215 LOOP
1216 l_formula_text := temp_rec.formula_text;
1217 END LOOP;
1218
1219 l_formula_text := REPLACE(l_formula_text,
1220 'REPLACE_PT_EE_CUSTOM_BAL_ASG_RUN',
1221 '0');
1222
1223 UPDATE pay_shadow_formulas
1224 SET formula_text = l_formula_text
1225 WHERE formula_id = l_shad_formula_id
1226 AND business_group_id = p_business_group_id;
1227
1228
1229
1230
1231 END IF;
1232
1233 --
1234 -- Replace the taxation and social insurance
1235 -- balance reduction text in the formula
1236 --
1237 IF g_debug THEN
1238 hr_utility.set_location('Replacing Tax SI string in the formula', 51);
1239 END IF;
1240
1241 pqp_pension_functions.gen_dynamic_formula
1242 (p_pension_type_id => p_pension_type_id
1243 ,p_effective_date => p_effective_start_date
1244 ,p_formula_string => l_tax_si_text);
1245
1246 FOR temp_rec IN csr_get_formula_txt(l_shad_formula_id)
1247 LOOP
1248 l_formula_text := temp_rec.formula_text;
1249 END LOOP;
1250 l_formula_text := REPLACE(l_formula_text,'REPLACE_TAX_ZVW_TEXT',
1251 l_tax_si_text);
1252
1253 UPDATE pay_shadow_formulas
1254 SET formula_text = l_formula_text
1255 WHERE formula_id = l_shad_formula_id
1256 AND business_group_id = p_business_group_id;
1257
1258
1259 --Employer Formula
1260 l_shad_formula_id1 :=
1261 pqp_nl_pension_template.Get_Formula_Id
1262 (l_scheme_prefix||
1263 '_EMPLOYER_LIFE_SAVINGS_CONTRIBUTION'
1264 ,p_business_group_id);
1265
1266 FOR temp_rec IN csr_get_formula_txt(l_shad_formula_id1)
1267 LOOP
1268 l_formula_text := temp_rec.formula_text;
1269
1270 l_formula_text := REPLACE(l_formula_text,'REPLACE_TAX_ZVW_TEXT',
1271 l_tax_si_text);
1272
1273 UPDATE pay_shadow_formulas
1274 SET formula_text = l_formula_text
1275 WHERE formula_id = l_shad_formula_id1
1276 AND business_group_id = p_business_group_id;
1277
1278 END LOOP;
1279
1280
1281
1282 IF r_pty_rec.er_contribution_bal_type_id IS NOT NULL THEN
1283
1284 FOR temp_rec IN csr_get_formula_txt(l_shad_formula_id1)
1285 LOOP
1286 l_formula_text := temp_rec.formula_text;
1287 END LOOP;
1288
1289 FOR temp_rec IN
1290 csr_get_dbi_asg_user_name(r_pty_rec.er_contribution_bal_type_id)
1291 LOOP
1292 l_dbi_user_name := temp_rec.user_name;
1293 l_formula_text := REPLACE(l_formula_text,
1294 'REPLACE_PT_ER_CUSTOM_BAL_ASG_RUN',
1295 l_dbi_user_name);
1296
1297 UPDATE pay_shadow_formulas
1298 SET formula_text = l_formula_text
1299 WHERE formula_id = l_shad_formula_id1
1300 AND business_group_id = p_business_group_id;
1301
1302 END LOOP;
1303 ELSE
1304
1305 --If balance information does not exist then replace text with null string
1306 FOR temp_rec IN csr_get_formula_txt(l_shad_formula_id1)
1307 LOOP
1308 l_formula_text := temp_rec.formula_text;
1309
1310 l_formula_text := REPLACE(l_formula_text,
1311 'REPLACE_PT_ER_CUSTOM_BAL_ASG_RUN',
1312 '0');
1313
1314 UPDATE pay_shadow_formulas
1315 SET formula_text = l_formula_text
1316 WHERE formula_id = l_shad_formula_id1
1317 AND business_group_id = p_business_group_id;
1318 END LOOP;
1319
1320 END IF;
1321 -----------------------------------------------------
1322 ---Replacing ZVW String from the EE formula
1323 -----------------------------------------------------
1324
1325 IF p_zvw_std_tax_chk = 'Y' and p_zvw_spl_tax_chk = 'N' THEN
1326
1327 --
1328 -- Reduce just the ZVW std tax balance
1329 --
1330 l_zvw_text := '
1331 IF dedn_amt <= l_zvw_std_tax_bal THEN
1332 feed_to_zvw_std_tax = dedn_amt
1333 ELSE IF dedn_amt > l_zvw_std_tax_bal THEN
1334 (
1335 feed_to_zvw_std_tax = l_zvw_std_tax_bal
1336 ) ';
1337 ELSIF p_zvw_std_tax_chk = 'N' and p_zvw_spl_tax_chk = 'Y' THEN
1338
1339 --
1340 -- Reduce just the ZVW spl tax balance
1341 --
1342 l_zvw_text := '
1343 IF dedn_amt <= l_zvw_spl_tax_bal THEN
1344 feed_to_zvw_spl_tax = dedn_amt
1345 ELSE IF dedn_amt > l_zvw_spl_tax_bal THEN
1346 (
1347 feed_to_zvw_spl_tax = l_zvw_spl_tax_bal
1348 ) ';
1349
1350 ELSIF p_zvw_std_tax_chk = 'Y' and p_zvw_spl_tax_chk = 'Y' THEN
1351
1352 --
1353 -- Reduce the ZVW std tax balance first and then
1354 -- reduce the ZVW spl tax balance
1355 --
1356 l_zvw_text := '
1357
1358 IF dedn_amt <= l_zvw_std_tax_bal THEN
1359 feed_to_zvw_std_tax = dedn_amt
1360 ELSE IF dedn_amt > l_zvw_std_tax_bal THEN
1361 (
1362 feed_to_zvw_std_tax = l_zvw_std_tax_bal
1363 dedn_amt_temp = dedn_amt - feed_to_zvw_std_tax
1364 feed_to_zvw_spl_tax = LEAST(dedn_amt_temp,l_zvw_spl_tax_bal)
1365 ) ';
1366
1367 ELSIF ( p_zvw_std_tax_chk ='N' and p_zvw_spl_tax_chk ='N') THEN
1368 l_zvw_text:=' ';
1369 END IF;
1370
1371
1372 FOR temp_rec IN csr_get_formula_txt(l_shad_formula_id)
1373 LOOP
1374 l_formula_text := temp_rec.formula_text;
1375 END LOOP;
1376 l_formula_text := REPLACE(l_formula_text,'REPLACE_ZVW_TEXT',
1377 l_zvw_text);
1378
1379 UPDATE pay_shadow_formulas
1380 SET formula_text = l_formula_text
1381 WHERE formula_id = l_shad_formula_id
1382 AND business_group_id = p_business_group_id;
1383
1384 FOR temp_rec IN csr_get_formula_txt(l_shad_formula_id1)
1385 LOOP
1386 l_formula_text := temp_rec.formula_text;
1387
1388 l_formula_text := REPLACE(l_formula_text,'REPLACE_ZVW_TEXT',
1389 l_zvw_text);
1390
1391 UPDATE pay_shadow_formulas
1392 SET formula_text = l_formula_text
1393 WHERE formula_id = l_shad_formula_id1
1394 AND business_group_id = p_business_group_id;
1395 END LOOP;
1396
1397
1398 IF g_debug THEN
1399 hr_utility.set_location('Done replacing Tax SI string in the formula', 51);
1400 hr_utility.set_location('Generating Core objects : Part - 1', 50);
1401 END IF;
1402 -- ---------------------------------------------------------------------
1403 -- |-------------------< Generate Core Objects >------------------------|
1404 -- ---------------------------------------------------------------------
1405 pay_element_template_api.generate_part1
1406 (p_validate => FALSE
1407 ,p_effective_date => p_effective_start_date
1408 ,p_hr_only => FALSE
1409 ,p_hr_to_payroll => FALSE
1410 ,p_template_id => l_template_id);
1411 --
1412 IF g_debug THEN
1413 hr_utility.set_location('..After Generating Core objects : Part - 1', 50);
1414 hr_utility.set_location('..After Generating Core objects : Part - 2', 50);
1415 END IF;
1416
1417 pay_element_template_api.generate_part2
1418 (p_validate => FALSE
1419 ,p_effective_date => p_effective_start_date
1420 ,p_template_id => l_template_id);
1421 --
1422 IF g_debug THEN
1423 hr_utility.set_location('..After Generating Core objects : Part - 2', 50);
1424 hr_utility.set_location('Updating Input Values..', 50);
1425 END IF;
1426
1427 -- ------------------------------------------------------------------------
1428 -- Create a row in pay_element_extra_info with all the element information
1429 -- ------------------------------------------------------------------------
1430 IF g_debug THEN
1431 hr_utility.set_location('..Creating element extra information', 50);
1432 END IF;
1433
1434 l_base_element_type_id := pqp_nl_pension_template.Get_Object_ID ('ELE',
1435 p_scheme_prefix||' Employee Savings Deduction',
1436 p_business_group_id,
1437 l_template_id);
1438
1439
1440 IF p_er_component = 'Y' THEN
1441
1442 l_er_base_element_type_id := pqp_nl_pension_template.Get_Object_ID ('ELE',
1443 p_scheme_prefix||' Employer Savings Contribution',
1444 p_business_group_id,
1445 l_template_id);
1446
1447 END IF;
1448
1449 --
1450 -- Update some the input values for default values
1451 --
1452 pqp_nl_pension_template.Update_Ipval_Defval(
1453 p_scheme_prefix||' Employee Savings Deduction'
1454 ,'Pension Type Id'
1455 ,TO_CHAR(p_pension_type_id)
1456 ,p_business_group_id);
1457
1458
1459 pqp_nl_pension_template.Update_Ipval_Defval(
1460 p_scheme_prefix||' Employee Savings Deduction'
1461 ,'Contribution Type'
1462 ,p_ee_deduction_method
1463 ,p_business_group_id);
1464
1465 pqp_nl_pension_template.Update_Ipval_Defval(
1466 p_scheme_prefix||' Employee Savings Deduction'
1467 ,'Enable Maximum Deduction'
1468 ,'N'
1469 ,p_business_group_id);
1470
1471
1472 pqp_nl_pension_template.Update_Ipval_Defval(
1473 p_scheme_prefix||' Employee Savings Deduction'
1474 ,'Enable Limit Check'
1475 ,'Y'
1476 ,p_business_group_id);
1477
1478 pqp_nl_pension_template.Update_Ipval_Defval(
1479 p_scheme_prefix||' Employer Savings Contribution'
1480 ,'Pension Type Id'
1481 ,TO_CHAR(p_pension_type_id)
1482 ,p_business_group_id);
1483
1484
1485 pqp_nl_pension_template.Update_Ipval_Defval(
1486 p_scheme_prefix||' Employer Savings Contribution'
1487 ,'Contribution Type'
1488 ,p_er_deduction_method
1489 ,p_business_group_id);
1490
1491
1492
1493
1494 IF g_debug THEN
1495 hr_utility.set_location('Done Updating Input Values..', 50);
1496 -- hr_utility.set_location('Deriving Element Type Ids..', 50);
1497 END IF;
1498
1499
1500 -------------------------------------------------------------------------------
1501 --Make Type as readonly for Flat Amount Schemes.In this case user cannot switch from
1502 --FA to PE
1503
1504 IF p_ee_deduction_method = 'FA' THEN
1505
1506 Update_Input_Vaue_Col(l_base_element_type_id,
1507 'Contribution Type' ,
1508 p_business_group_id ,
1509 'X');
1510
1511 END IF;
1512
1513 IF p_er_component = 'Y' and p_er_deduction_method = 'FA' THEN
1514
1515 Update_Input_Vaue_Col(l_er_base_element_type_id,
1516 'Contribution Type' ,
1517 p_business_group_id ,
1518 'X');
1519 END IF;
1520
1521 -------------------------------------------------------------------------------------
1522
1523
1524
1525 pay_element_extra_info_api.create_element_extra_info
1526 (p_element_type_id => l_base_element_type_id
1527 ,p_information_type => 'PQP_NL_SAVINGS_DEDUCTIONS'
1528 ,p_eei_information_category => 'PQP_NL_SAVINGS_DEDUCTIONS'
1529 ,p_eei_information1 => p_scheme_description
1530 ,p_eei_information2 => to_char(p_pension_type_id)
1531 ,p_eei_information3 => to_char(p_pension_provider_id)
1532 ,p_eei_information4 => p_pension_category
1533 ,p_eei_information5 => p_ee_deduction_method
1534 ,p_eei_information6 => p_er_component
1535 ,p_eei_information7 => p_arrearage_flag
1536 ,p_eei_information8 => null
1537 ,p_eei_information9 => p_er_deduction_method
1538 ,p_eei_information10 => p_scheme_prefix
1539 ,p_eei_information11 => p_saving_scheme_type
1540 ,p_eei_information12 => null
1541 ,p_eei_information13 => null
1542 ,p_eei_information14 => null
1543 ,p_eei_information15 => null
1544 ,p_eei_information16 => null
1545 ,p_eei_information17 => null
1546 ,p_eei_information18 => null
1547 ,p_eei_information19 => null
1548 ,p_eei_information20 => null
1549 ,p_element_type_extra_info_id => l_eei_info_id
1550 ,p_object_version_number => l_ovn_eei);
1551
1552
1553
1554 IF g_debug THEN
1555 hr_utility.set_location('..After Creating element extra information', 50);
1556
1557 -- ---------------------------------------------------------------------
1558 -- Create the Retro Component usage associations between the retro and
1559 -- pension deduction elements
1560 -- ---------------------------------------------------------------------
1561 hr_utility.set_location('Creating Retro Comp Usgs', 50);
1562 END IF;
1563
1564 Create_Retro_Usages
1565 (p_creator_name => p_scheme_prefix||' Employee Savings Deduction'
1566 ,p_creator_type => 'ET'
1567 ,p_retro_component_priority => 10
1568 ,p_default_component => 'Y'
1569 ,p_reprocess_type => 'R'
1570 ,p_retro_element_name
1571 => p_scheme_prefix||' Retro Employee Savings Deduction Current Year'
1572 ,p_start_time_def_name => 'Start of Current Year'
1573 ,p_end_time_def_name => 'End of Time'
1574 ,p_business_group_id => p_business_group_id);
1575
1576 -- commented out because retro changes to the prev year are not brought
1577 -- forward to the current year.
1578 -- Create_Retro_Usages
1579 -- (p_creator_name => p_scheme_prefix||' Employee Savings Deduction'
1580 -- ,p_creator_type => 'ET'
1581 -- ,p_retro_component_priority => 10
1582 -- ,p_default_component => 'Y'
1583 -- ,p_reprocess_type => 'R'
1584 -- ,p_retro_element_name
1585 -- => p_scheme_prefix||' Retro Employee Savings Deduction Previous Year'
1586 -- ,p_start_time_def_name => 'Start of Time'
1587 -- ,p_end_time_def_name => 'End of Previous Year'
1588 -- ,p_business_group_id => p_business_group_id);
1589
1590 Create_Retro_Usages
1591 (p_creator_name
1592 => p_scheme_prefix||' Employer Savings Contribution'
1593 ,p_creator_type => 'ET'
1594 ,p_retro_component_priority => 10
1595 ,p_default_component => 'Y'
1596 ,p_reprocess_type => 'R'
1597 ,p_retro_element_name
1598 => p_scheme_prefix||' Retro Employer Savings Contribution Current Year'
1599 ,p_start_time_def_name => 'Start of Current Year'
1600 ,p_end_time_def_name => 'End of Time'
1601 ,p_business_group_id => p_business_group_id);
1602
1603 -- commented out because retro changes to the prev year are not brought
1604 -- forward to the current year.
1605 -- Create_Retro_Usages
1606 -- (p_creator_name
1607 -- => p_scheme_prefix||' Employer Savings Contribution'
1608 -- ,p_creator_type => 'ET'
1609 -- ,p_retro_component_priority => 10
1610 -- ,p_default_component => 'Y'
1611 -- ,p_reprocess_type => 'R'
1612 -- ,p_retro_element_name
1613 -- => p_scheme_prefix||' Retro Employer Savings Contribution Previous Year'
1614 -- ,p_start_time_def_name => 'Start of Time'
1615 -- ,p_end_time_def_name => 'End of Previous Year'
1616 -- ,p_business_group_id => p_business_group_id);
1617
1618 -- Create_Retro_Usages (p_creator_name
1619 -- => p_scheme_prefix||' Standard Tax Adjustment'
1620 -- ,p_creator_type => 'ET'
1621 -- ,p_retro_component_priority => 10
1622 -- ,p_default_component => 'Y'
1623 -- ,p_reprocess_type => 'R'
1624 -- ,p_retro_element_name
1625 -- => p_scheme_prefix||' Retro Standard Tax Adjustment Current Year'
1626 -- ,p_start_time_def_name => 'Start of Current Year'
1627 -- ,p_end_time_def_name => 'End of Time'
1628 -- ,p_business_group_id => p_business_group_id);
1629
1630 -- Create_Retro_Usages
1631 -- (p_creator_name
1632 -- => p_scheme_prefix||' Standard Tax Adjustment'
1633 -- ,p_creator_type => 'ET'
1634 -- ,p_retro_component_priority => 10
1635 -- ,p_default_component => 'Y'
1636 -- ,p_reprocess_type => 'R'
1637 -- ,p_retro_element_name
1638 -- => p_scheme_prefix||' Retro Standard Tax Adjustment Previous Year'
1639 -- ,p_start_time_def_name => 'Start of Time'
1640 -- ,p_end_time_def_name => 'End of Previous Year'
1641 -- ,p_business_group_id => p_business_group_id);
1642
1643 -- Create_Retro_Usages (p_creator_name
1644 -- => p_scheme_prefix||' Special Tax Adjustment'
1645 -- ,p_creator_type => 'ET'
1646 -- ,p_retro_component_priority => 10
1647 -- ,p_default_component => 'Y'
1648 -- ,p_reprocess_type => 'R'
1649 -- ,p_retro_element_name
1650 -- => p_scheme_prefix||' Retro Special Tax Adjustment Current Year'
1651 -- ,p_start_time_def_name => 'Start of Current Year'
1652 -- ,p_end_time_def_name => 'End of Time'
1653 -- ,p_business_group_id => p_business_group_id);
1654
1655 -- Create_Retro_Usages
1656 -- (p_creator_name
1657 -- => p_scheme_prefix||' Special Tax Adjustment'
1658 -- ,p_creator_type => 'ET'
1659 -- ,p_retro_component_priority => 10
1660 -- ,p_default_component => 'Y'
1661 -- ,p_reprocess_type => 'R'
1662 -- ,p_retro_element_name
1663 -- => p_scheme_prefix||' Retro Special Tax Adjustment Previous Year'
1664 -- ,p_start_time_def_name => 'Start of Time'
1665 -- ,p_end_time_def_name => 'End of Previous Year'
1666 -- ,p_business_group_id => p_business_group_id);
1667
1668 -- Create_Retro_Usages (p_creator_name
1669 -- => p_scheme_prefix||' ZVW Income Standard Tax Adjustment'
1670 -- ,p_creator_type => 'ET'
1671 -- ,p_retro_component_priority => 10
1672 -- ,p_default_component => 'Y'
1673 -- ,p_reprocess_type => 'R'
1674 -- ,p_retro_element_name
1675 -- => p_scheme_prefix||' Retro ZVW Income Standard Tax Adjustment Current Year'
1676 -- ,p_start_time_def_name => 'Start of Current Year'
1677 -- ,p_end_time_def_name => 'End of Time'
1678 -- ,p_business_group_id => p_business_group_id);
1679
1680 -- Create_Retro_Usages
1681 -- (p_creator_name
1682 -- => p_scheme_prefix||' ZVW Income Standard Tax Adjustment'
1683 -- ,p_creator_type => 'ET'
1684 -- ,p_retro_component_priority => 10
1685 -- ,p_default_component => 'Y'
1686 -- ,p_reprocess_type => 'R'
1687 -- ,p_retro_element_name
1688 -- => p_scheme_prefix||' Retro ZVW Income Standard Tax Adjustment Previous Year'
1689 -- ,p_start_time_def_name => 'Start of Time'
1690 -- ,p_end_time_def_name => 'End of Previous Year'
1691 -- ,p_business_group_id => p_business_group_id);
1692
1693 -- Create_Retro_Usages (p_creator_name
1694 -- => p_scheme_prefix||' ZVW Income Special Tax Adjustment'
1695 -- ,p_creator_type => 'ET'
1696 -- ,p_retro_component_priority => 10
1697 -- ,p_default_component => 'Y'
1698 -- ,p_reprocess_type => 'R'
1699 -- ,p_retro_element_name
1700 -- => p_scheme_prefix||' Retro ZVW Income Special Tax Adjustment Current Year'
1701 -- ,p_start_time_def_name => 'Start of Current Year'
1702 -- ,p_end_time_def_name => 'End of Time'
1703 -- ,p_business_group_id => p_business_group_id);
1704
1705 -- Create_Retro_Usages
1706 -- (p_creator_name
1707 -- => p_scheme_prefix||' ZVW Income Special Tax Adjustment'
1708 -- ,p_creator_type => 'ET'
1709 -- ,p_retro_component_priority => 10
1710 -- ,p_default_component => 'Y'
1711 -- ,p_reprocess_type => 'R'
1712 -- ,p_retro_element_name
1713 -- => p_scheme_prefix||' Retro ZVW Income Special Tax Adjustment Previous Year'
1714 -- ,p_start_time_def_name => 'Start of Time'
1715 -- ,p_end_time_def_name => 'End of Previous Year'
1716 -- ,p_business_group_id => p_business_group_id);
1717
1718
1719
1720 IF g_debug THEN
1721 hr_utility.set_location('Done Creating Retro Comp Usgs', 50);
1722 END IF;
1723
1724 IF g_debug THEN
1725 hr_utility.set_location('Adding Event Group',60);
1726 END IF;
1727
1728 Update_Event_Group
1729 (p_element_name => p_scheme_prefix||' Employee Savings Deduction'
1730 ,p_business_group_id => p_business_group_id);
1731
1732 Update_Event_Group
1733 (p_element_name => p_scheme_prefix||' Employer Savings Contribution'
1734 ,p_business_group_id => p_business_group_id);
1735
1736
1737
1738 IF g_debug THEN
1739 hr_utility.set_location('Done Adding the Event Group', 60);
1740 END IF;
1741
1742 -- ---------------------------------------------------------------------
1743 -- Compile the base element's standard formula
1744 -- ---------------------------------------------------------------------
1745 hr_utility.set_location('Compile EE Formula', 50);
1746
1747 pqp_nl_pension_template.Compile_Formula
1748 (p_element_type_id => l_base_element_type_id
1749 ,p_effective_start_date => p_effective_start_date
1750 ,p_scheme_prefix => l_scheme_prefix
1751 ,p_business_group_id => p_business_group_id
1752 ,p_request_id => l_request_id
1753 );
1754
1755 IF g_debug THEN
1756 hr_utility.set_location('Done Compile EE Formula', 50);
1757 hr_utility.set_location('Compile ER Formula', 50);
1758 END IF;
1759
1760 pqp_nl_pension_template.Compile_Formula
1761 (p_element_type_id => l_er_base_element_type_id
1762 ,p_effective_start_date => p_effective_start_date
1763 ,p_scheme_prefix => l_scheme_prefix
1764 ,p_business_group_id => p_business_group_id
1765 ,p_request_id => l_er_request_id
1766 );
1767
1768 IF g_debug THEN
1769 hr_utility.set_location('Done Compile ER Formula', 50);
1770 hr_utility.set_location('Leaving :'||l_proc_name, 190);
1771 END IF;
1772
1773 RETURN l_base_element_type_id;
1774
1775 END Create_User_Template;
1776 -- ---------------------------------------------------------------------
1777 -- |--------------------< Create_User_Template_Swi >------------------------|
1778 -- ---------------------------------------------------------------------
1779 FUNCTION Create_User_Template_Swi
1780 (p_pension_category IN VARCHAR2
1781 ,p_pension_provider_id IN NUMBER
1782 ,p_pension_type_id IN NUMBER
1783 ,p_scheme_prefix IN VARCHAR2
1784 ,p_reporting_name IN VARCHAR2
1785 ,p_scheme_description IN VARCHAR2
1786 ,p_termination_rule IN VARCHAR2
1787 ,p_er_component IN VARCHAR2
1788 ,p_arrearage_flag IN VARCHAR2
1789 ,p_ee_deduction_method IN VARCHAR2
1790 ,p_er_deduction_method IN VARCHAR2
1791 ,p_saving_scheme_type IN VARCHAR2
1792 ,p_zvw_std_tax_chk IN VARCHAR2
1793 ,p_zvw_spl_tax_chk IN VARCHAR2
1794 ,p_standard_link IN VARCHAR2
1795 ,p_effective_start_date IN DATE DEFAULT NULL
1796 ,p_effective_end_date IN DATE DEFAULT NULL
1797 ,p_security_group_id IN NUMBER DEFAULT NULL
1798 ,p_business_group_id IN NUMBER
1799 )
1800 RETURN NUMBER IS
1801 --
1802 -- Variables for API Boolean parameters
1803 l_validate BOOLEAN;
1804 --
1805 -- Variables for IN/OUT parameters
1806 l_element_type_id NUMBER;
1807 --
1808 -- Other variables
1809 l_return_status VARCHAR2(1);
1810 l_proc VARCHAR2(72) := 'Create_User_Template_Swi';
1811 BEGIN
1812
1813 IF g_debug THEN
1814 hr_utility.set_location(' Entering:' || l_proc,10);
1815 END IF;
1816
1817 l_element_type_id := -1;
1818 --
1819 -- Issue a savepoint
1820 --
1821 SAVEPOINT Create_User_Template_Swi;
1822 --
1823 -- Initialise Multiple Message Detection
1824 --
1825 hr_multi_message.enable_message_list;
1826 --
1827 -- Remember IN OUT parameter IN values
1828 --
1829 --
1830 -- Convert constant values to their corresponding boolean value
1831 --
1832 l_validate :=
1833 hr_api.constant_to_boolean
1834 (p_constant_value => hr_api.g_false_num);
1835 --
1836 -- Register Surrogate ID or user key values
1837 --
1838 --
1839 -- Call API
1840 --
1841 l_element_type_id := Create_User_Template
1842 (p_pension_category => p_pension_category
1843 ,p_pension_provider_id => p_pension_provider_id
1844 ,p_pension_type_id => p_pension_type_id
1845 ,p_scheme_prefix => p_scheme_prefix
1846 ,p_reporting_name => p_reporting_name
1847 ,p_scheme_description => p_scheme_description
1848 ,p_termination_rule => p_termination_rule
1849 ,p_ee_deduction_method => p_ee_deduction_method
1850 ,p_er_deduction_method => p_er_deduction_method
1851 ,p_er_component => p_er_component
1852 ,p_arrearage_flag => p_arrearage_flag
1853 ,p_zvw_std_tax_chk => p_zvw_std_tax_chk
1854 ,p_zvw_spl_tax_chk => p_zvw_spl_tax_chk
1855 ,p_saving_scheme_type => p_saving_scheme_type
1856 ,p_standard_link => p_standard_link
1857 ,p_effective_start_date => p_effective_start_date
1858 ,p_effective_end_date => p_effective_end_date
1859 ,p_security_group_id => p_security_group_id
1860 ,p_business_group_id => p_business_group_id
1861 );
1862
1863 --
1864 -- Convert API warning boolean parameter values to specific
1865 -- messages and add them to Multiple Message List
1866 --
1867 --
1868 -- Convert API non-warning boolean parameter values
1869 --
1870 --
1871 -- Derive the API return status value based on whether
1872 -- messages of any type exist in the Multiple Message List.
1873 -- Also disable Multiple Message Detection.
1874 --
1875 l_return_status := hr_multi_message.get_return_status_disable;
1876
1877 IF g_debug THEN
1878 hr_utility.set_location(' Leaving:' || l_proc,20);
1879 END IF;
1880
1881 RETURN l_element_type_id;
1882
1883 --
1884 EXCEPTION
1885 WHEN hr_multi_message.error_message_exist THEN
1886 --
1887 -- Catch the Multiple Message List exception which
1888 -- indicates API processing has been aborted because
1889 -- at least one message exists in the list.
1890 --
1891 ROLLBACK TO Create_User_Template_Swi;
1892 --
1893 -- Reset IN OUT parameters and set OUT parameters
1894 --
1895 RETURN l_element_type_id;
1896
1897 IF g_debug THEN
1898 hr_utility.set_location(' Leaving:' || l_proc, 30);
1899 END IF;
1900
1901 WHEN others THEN
1902 --
1903 -- When Multiple Message Detection is enabled catch
1904 -- any Application specific or other unexpected
1905 -- exceptions. Adding appropriate details to the
1906 -- Multiple Message List. Otherwise re-raise the
1907 -- error.
1908 --
1909 ROLLBACK TO Create_User_Template_Swi;
1910 IF hr_multi_message.unexpected_error_add(l_proc) THEN
1911 IF g_debug THEN
1912 hr_utility.set_location(' Leaving:' || l_proc,40);
1913 END IF;
1914 RAISE;
1915 END IF;
1916 --
1917 -- Reset IN OUT and set OUT parameters
1918 --
1919 l_return_status := hr_multi_message.get_return_status_disable;
1920 RETURN l_element_type_id;
1921
1922 IF g_debug THEN
1923 hr_utility.set_location(' Leaving:' || l_proc,50);
1924 END IF;
1925
1926 END Create_User_Template_Swi;
1927
1928 -- ---------------------------------------------------------------------
1929 -- |--------------------< Delete_User_Template >------------------------|
1930 -- ---------------------------------------------------------------------
1931 procedure Delete_User_Template
1932 (p_savings_plan_id in Number
1933 ,p_business_group_id in Number
1934 ,p_savings_dedn_ele_name in Varchar2
1935 ,p_savings_dedn_ele_type_id in Number
1936 ,p_security_group_id in Number
1937 ,p_effective_date in Date
1938 ) is
1939 --
1940 cursor c1 is
1941 select template_id
1942 from pay_element_templates
1943 where base_name||' Employee Savings Deduction' = p_savings_dedn_ele_name
1944 and business_group_id = p_business_group_id
1945 and template_type = 'U';
1946
1947 CURSOR csr_ele_extra_info IS
1948 SELECT element_type_extra_info_id
1949 ,object_version_number
1950 FROM pay_element_type_extra_info
1951 WHERE eei_information_category = 'PQP_NL_SAVINGS_DEDUCTIONS'
1952 AND element_type_id = p_savings_dedn_ele_type_id;
1953
1954 l_template_id Number(9);
1955 l_proc Varchar2(60) := g_proc_name||'Delete_User_Template';
1956
1957 begin
1958 hr_utility.set_location('Entering :'||l_proc, 10);
1959 --
1960 for c1_rec in c1 loop
1961 l_template_id := c1_rec.template_id;
1962 end loop;
1963 --
1964 pay_element_template_api.delete_user_structure
1965 (p_validate => false
1966 ,p_drop_formula_packages => true
1967 ,p_template_id => l_template_id);
1968 --
1969
1970 --
1971 -- Delete the rows in pay_element_type_extra_info
1972 --
1973
1974 FOR temp_rec IN csr_ele_extra_info
1975 LOOP
1976 pay_element_extra_info_api.delete_element_extra_info
1977 (p_element_type_extra_info_id => temp_rec.element_type_extra_info_id
1978 ,p_object_version_number => temp_rec.object_version_number);
1979 END LOOP;
1980
1981 hr_utility.set_location('Leaving :'||l_proc, 50);
1982 end Delete_User_Template;
1983
1984
1985 -- ---------------------------------------------------------------------
1986 -- |--------------------< Delete_User_Template_Swi >------------------------|
1987 -- ---------------------------------------------------------------------
1988
1989 procedure Delete_User_Template_Swi
1990 (p_savings_plan_id in Number
1991 ,p_business_group_id in Number
1992 ,p_savings_dedn_ele_name in Varchar2
1993 ,p_savings_dedn_ele_type_id in Number
1994 ,p_security_group_id in Number
1995 ,p_effective_date in Date
1996 ) is
1997
1998 --
1999 -- Variables for API Boolean parameters
2000 l_validate boolean;
2001 --
2002 -- Variables for IN/OUT parameters
2003 --
2004 -- Other variables
2005 l_return_status varchar2(1);
2006 l_proc varchar2(72) := 'Delete_User_Template_Swi';
2007
2008 Begin
2009
2010 hr_utility.set_location(' Entering:' || l_proc,10);
2011 --
2012 -- Issue a savepoint
2013 --
2014 savepoint Delete_User_Template_Swi;
2015 --
2016 -- Initialise Multiple Message Detection
2017 --
2018 hr_multi_message.enable_message_list;
2019 --
2020 -- Remember IN OUT parameter IN values
2021 --
2022 --
2023 -- Convert constant values to their corresponding boolean value
2024 --
2025 l_validate :=
2026 hr_api.constant_to_boolean
2027 (p_constant_value => hr_api.g_false_num);
2028 --
2029 -- Register Surrogate ID or user key values
2030 --
2031 --
2032 -- Call API
2033 --
2034 Delete_User_Template
2035 (p_savings_plan_id => p_savings_plan_id
2036 ,p_business_group_id => p_business_group_id
2037 ,p_savings_dedn_ele_name => p_savings_dedn_ele_name
2038 ,p_savings_dedn_ele_type_id => p_savings_dedn_ele_type_id
2039 ,p_security_group_id => p_security_group_id
2040 ,p_effective_date => p_effective_date
2041 );
2042 --
2043 -- Convert API warning boolean parameter values to specific
2044 -- messages and add them to Multiple Message List
2045 --
2046 --
2047 -- Convert API non-warning boolean parameter values
2048 --
2049 --
2050 -- Derive the API return status value based on whether
2051 -- messages of any type exist in the Multiple Message List.
2052 -- Also disable Multiple Message Detection.
2053 --
2054 l_return_status := hr_multi_message.get_return_status_disable;
2055 hr_utility.set_location(' Leaving:' || l_proc,20);
2056
2057 --
2058 exception
2059 when hr_multi_message.error_message_exist then
2060 --
2061 -- Catch the Multiple Message List exception which
2062 -- indicates API processing has been aborted because
2063 -- at least one message exists in the list.
2064 --
2065 rollback to Delete_User_Template_Swi;
2066 --
2067 -- Reset IN OUT parameters and set OUT parameters
2068 --
2069 hr_utility.set_location(' Leaving:' || l_proc, 30);
2070
2071 when others then
2072 --
2073 -- When Multiple Message Detection is enabled catch
2074 -- any Application specific or other unexpected
2075 -- exceptions. Adding appropriate details to the
2076 -- Multiple Message List. Otherwise re-raise the
2077 -- error.
2078 --
2079 rollback to Delete_User_Template_Swi;
2080 if hr_multi_message.unexpected_error_add(l_proc) then
2081 hr_utility.set_location(' Leaving:' || l_proc,40);
2082 raise;
2083 end if;
2084 --
2085 -- Reset IN OUT and set OUT parameters
2086 --
2087 l_return_status := hr_multi_message.get_return_status_disable;
2088 hr_utility.set_location(' Leaving:' || l_proc,50);
2089
2090 END delete_user_template_swi;
2091
2092 End pay_nl_life_savings_template;
2093
2094