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