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