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