[Home] [Help]
PACKAGE BODY: APPS.PQP_NL_PENSION_TEMPLATE
Source
1 Package Body pqp_nl_pension_template As
2 /* $Header: pqpnlped.pkb 120.0.12000000.2 2007/03/02 06:39:27 niljain noship $ */
3
4 g_proc_name varchar2(80) := ' pqp_nl_pension_template.';
5
6 -- ---------------------------------------------------------------------
7 -- |-----------------------< Compile_Formula >--------------------------|
8 -- ---------------------------------------------------------------------
9 procedure Compile_Formula
10 (p_element_type_id in number
11 ,p_effective_start_date in date
12 ,p_scheme_prefix in varchar2
13 ,p_business_group_id in number
14 ,p_request_id out nocopy number
15 ) is
16 -- --------------------------------------------------------
17 -- Cursor to get the formula details necessary to compile
18 -- --------------------------------------------------------
19 cursor csr_fra(c_element_type_id number) is
20 select
21 fra.formula_id,
22 fra.formula_name,
23 fty.formula_type_id,
24 fty.formula_type_name
25 from ff_formulas_f fra,
26 ff_formula_types fty,
27 pay_status_processing_rules_f spr
28 where fty.formula_type_id = fra.formula_type_id
29 and fra.formula_id = spr.formula_id
30 and spr.assignment_status_type_id is null
31 and spr.element_type_id = c_element_type_id
32 and p_effective_start_date between fra.effective_start_date
33 and fra.effective_end_date
34 and p_effective_start_date between spr.effective_start_date
35 and spr.effective_end_date;
36
37 l_request_id number;
38 l_er_request_id number;
39 l_proc_name Varchar2(80) := g_proc_name || 'compile_formula';
40 begin
41 hr_utility.set_location('Entering: '||l_proc_name, 10);
42 -- ------------------------------------------------------------
43 -- Query formula info (ie. the formula attached to this
44 -- element's Standard status proc rule.
45 -- ------------------------------------------------------------
46 for fra_rec in csr_fra (c_element_type_id => p_element_type_id)
47 loop
48 hr_utility.set_location('..FF Name :'||fra_rec.formula_name,15);
49 hr_utility.set_location('..FF Type Name :'||fra_rec.formula_type_name,20);
50 -- ----------------------------------------------
51 -- Submit the request to compile the formula
52 -- ----------------------------------------------
53 l_request_id := fnd_request.submit_request
54 (application => 'FF'
55 ,program => 'SINGLECOMPILE'
56 ,argument1 => fra_rec.formula_type_name --Oracle Payroll
57 ,argument2 => fra_rec.formula_name); --formula name
58 p_request_id := l_request_id;
59 hr_utility.set_location('..Request Id :'||p_request_id, 25);
60 end loop;
61 hr_utility.set_location('Leaving: '||l_proc_name, 30);
62 exception
63 when others then
64 hr_utility.set_location('..Entering exception when others ', 80);
65 hr_utility.set_location('Leaving: '||l_proc_name, 90);
66 p_request_id := null; raise;
67 end Compile_Formula;
68
69 -- ----------------------------------------------------------------------------
70 -- |------------------------< chk_scheme_prefix >-----------------------------|
71 -- ----------------------------------------------------------------------------
72 Procedure chk_scheme_prefix
73 (p_scheme_prefix_in in varchar2
74 ) IS
75
76 element_name varchar2(100) := p_scheme_prefix_in;
77 l_output varchar2(100);
78 l_rgeflg varchar2(100);
79
80 begin
81
82 hr_chkfmt.checkformat
83 (
84 value => element_name,
85 format => 'PAY_NAME',
86 output => l_output,
87 minimum => NULL,
88 maximum => NULL,
89 nullok => 'N',
90 rgeflg => l_rgeflg,
91 curcode => NULL
92 );
93
94 EXCEPTION
95
96 WHEN OTHERS THEN
97 fnd_message.set_name('PQP', 'PQP_230923_SCHEME_PREFIX_ERR');
98 fnd_message.raise_error;
99
100 END chk_scheme_prefix;
101
102 -- ---------------------------------------------------------------------
103 -- |------------------------< Get_Object_ID >--------------------------|
104 -- ---------------------------------------------------------------------
105 function Get_Object_ID (p_object_type in Varchar2,
106 p_object_name in Varchar2,
107 p_business_group_id in Number,
108 p_template_id in Number)
109 return Number is
110 --
111 l_object_id Number := null;
112 l_proc_name Varchar2(72) := g_proc_name || 'get_object_id';
113 --
114 cursor c2 (c_object_name varchar2) is
115 select element_type_id
116 from pay_element_types_f
117 where element_name = c_object_name
118 and business_group_id = p_business_group_id;
119 --
120 cursor c3 (c_object_name in Varchar2) is
121 select ptco.core_object_id
122 from pay_shadow_balance_types psbt,
123 pay_template_core_objects ptco
124 where psbt.template_id = p_template_id
125 and psbt.balance_name = c_object_name
126 and ptco.template_id = psbt.template_id
127 and ptco.shadow_object_id = psbt.balance_type_id;
128 --
129 begin
130 hr_utility.set_location('Entering: '||l_proc_name, 10);
131 --
132 if p_object_type = 'ELE' then
133 for c2_rec in c2 (p_object_name) loop
134 l_object_id := c2_rec.element_type_id; -- element id
135 end loop;
136 elsif p_object_type = 'BAL' then
137 for c3_rec in c3 (p_object_name) loop
138 l_object_id := c3_rec.core_object_id; -- balance id
139 end loop;
140 end if;
141 --
142 hr_utility.set_location('Leaving: '||l_proc_name, 20);
143 --
144 return l_object_id;
145 end Get_Object_ID;
146
147 -- ---------------------------------------------------------------------
148 -- |------------------------< Get_Formula_Id >--------------------------|
149 -- ---------------------------------------------------------------------
150 function Get_Formula_Id (p_formula_name IN VARCHAR2
151 ,p_business_group_id IN NUMBER)
152 return Number is
153
154 cursor csr_get_formula_id is
155 select formula_id
156 from pay_shadow_formulas
157 where formula_name = p_formula_name
158 and business_group_id = p_business_group_id
159 and template_type = 'U';
160
161 l_proc_name Varchar2(72) := g_proc_name || 'get_formula_id';
162 l_formula_id Number;
163 begin
164 --
165 hr_utility.set_location ('Entering '||l_proc_name, 10);
166 --
167 open csr_get_formula_id;
168 fetch csr_get_formula_id into l_formula_id;
169 close csr_get_formula_id;
170 --
171 hr_utility.set_location ('Leaving '||l_proc_name, 20);
172 --
173 return l_formula_id;
174 end Get_Formula_ID;
175
176 -- ---------------------------------------------------------------------
177 -- |---------------------< Update_Ipval_Defval >------------------------|
178 -- ---------------------------------------------------------------------
179 procedure Update_Ipval_Defval(p_ele_name in Varchar2
180 ,p_ip_name in Varchar2
181 ,p_def_value in Varchar2
182 ,p_business_group_id IN Number
183 )
184 is
185
186 cursor csr_getinput(c_ele_name varchar2
187 ,c_iv_name varchar2) is
188 select input_value_id
189 ,piv.name
190 ,piv.element_type_id
191 from pay_input_values_f piv
192 ,pay_element_types_f pet
193 where element_name = c_ele_name
194 and piv.element_type_id = pet.element_type_id
195 and (piv.business_group_id = p_business_group_id or
196 piv.business_group_id is null)
197 and piv.name = c_iv_name
198 and (piv.legislation_code = 'NL' or
199 piv.legislation_code is null);
200
201 cursor csr_updinput(c_ip_id number
202 ,c_element_type_id number) is
203 select rowid
204 from pay_input_values_f
205 where input_value_id = c_ip_id
206 and element_type_id = c_element_type_id
207 for update nowait;
208
209 csr_getinput_rec csr_getinput%rowtype;
210 csr_updinput_rec csr_updinput%rowtype;
211
212 l_proc_name Varchar2(72) := g_proc_name ||
213 'update_ipval_defval';
214 --
215 begin
216 --
217 hr_utility.set_location ('Entering '||l_proc_name, 10);
218 --
219 open csr_getinput(p_ele_name ,p_ip_name);
220 loop
221 fetch csr_getinput into csr_getinput_rec;
222 exit when csr_getinput%notfound;
223 --
224 hr_utility.set_location (l_proc_name, 20);
225 --
226 open csr_updinput(csr_getinput_rec.input_value_id
227 ,csr_getinput_rec.element_type_id);
228 loop
229 fetch csr_updinput into csr_updinput_rec;
230 exit when csr_updinput%notfound;
231 --
232 hr_utility.set_location (l_proc_name, 30);
233 --
234 update pay_input_values_f
235 set default_value = p_def_value
236 where rowid = csr_updinput_rec.rowid;
237 end loop;
238 close csr_updinput;
239 end loop;
240 close csr_getinput;
241 --
242 hr_utility.set_location ('Leaving '||l_proc_name, 40);
243 --
244 end Update_Ipval_Defval;
245
246 -- ---------------------------------------------------------------------
247 -- |--------------------< Create_User_Template >------------------------|
248 -- ---------------------------------------------------------------------
249 function Create_User_Template
250 (p_pension_category in Varchar2
251 ,p_eligibility_model in Varchar2
252 ,p_pension_provider_id in Number
253 ,p_pension_type_id in Number
254 ,p_pension_plan_id in Number
255 ,p_deduction_method in Varchar2
256 ,p_arrearage_flag in Varchar2
257 ,p_partial_deductions_flag in Varchar2
258 ,p_employer_component in Varchar2
259 ,p_scheme_prefix in Varchar2
260 ,p_reporting_name in Varchar2
261 ,p_scheme_description in Varchar2
262 ,p_termination_rule in Varchar2
263 ,p_standard_link in Varchar2
264 ,p_effective_start_date in Date
265 ,p_effective_end_date in Date
266 ,p_security_group_id in Number
267 ,p_business_group_id in Number
268 )
269 return Number is
270 --
271 l_template_id pay_shadow_element_types.template_id%type;
272 l_base_element_type_id pay_template_core_objects.core_object_id%type;
273 l_er_base_element_type_id pay_template_core_objects.core_object_id%type;
274 l_source_template_id pay_element_templates.template_id%type;
275 l_object_version_number pay_element_types_f.object_version_number%type;
276 l_proc_name Varchar2(80) := g_proc_name || 'create_user_template';
277 l_element_type_id Number;
278 l_balance_type_id Number;
279 l_eei_element_type_id Number;
280 l_ele_obj_ver_number Number;
281 l_bal_obj_ver_number Number;
282 i Number;
283 l_eei_info_id Number;
284 l_ovn_eei Number;
285 l_formula_name pay_shadow_formulas.formula_name%type;
286 l_formula_id Number;
287 l_formula_id1 Number;
288 y Number := 0;
289 l_exists Varchar2(1);
290 l_count Number := 0;
291 l_shad_formula_id Number;
292 l_shad_formula_id1 Number;
293 l_prem_replace_string varchar2(5000) := ' ' ;
294 l_std_link_flag varchar2(10) := 'N';
295 l_scheme_prefix varchar2(50) := p_scheme_prefix;
296
297 type shadow_ele_rec is record
298 (element_type_id pay_shadow_element_types.element_type_id%type
299 ,object_version_number pay_shadow_element_types.object_version_number%type
300 ,reporting_name pay_shadow_element_types.reporting_name%type
301 ,description pay_shadow_element_types.description%type
302 );
303 type t_shadow_ele_info is table of shadow_ele_rec
304 index by Binary_Integer;
305 l_shadow_element t_shadow_ele_info;
306
307 type t_ele_name is table of pay_element_types_f.element_name%type
308 index by BINARY_INTEGER;
309 l_ele_name t_ele_name;
310 l_ele_new_name t_ele_name;
311 l_main_ele_name t_ele_name;
312 l_retro_ele_name t_ele_name;
313
314 type t_bal_name is table of pay_balance_types.balance_name%type
315 index by BINARY_INTEGER;
316 l_bal_name t_bal_name;
317 l_bal_new_name t_bal_name;
318
319 type t_ele_reporting_name is table of pay_element_types_f.reporting_name%type
320 index by BINARY_INTEGER;
321 l_ele_reporting_name t_ele_reporting_name;
322
323 type t_ele_description is table of pay_element_types_f.description%type
324 index by BINARY_INTEGER;
325 l_ele_description t_ele_description;
326
327 type t_ele_pp is table of pay_element_types_f.processing_priority%type
328 index by BINARY_INTEGER;
329 l_ele_pp t_ele_pp;
330
331 type t_eei_info is table of pay_element_type_extra_info.eei_information19%type
332 index by BINARY_INTEGER;
333 l_main_eei_info19 t_eei_info;
334 l_retro_eei_info19 t_eei_info;
335
336 l_ele_core_id pay_template_core_objects.core_object_id%type:= -1;
337
338 -- Extra Information variables
339 l_eei_information11 pay_element_type_extra_info.eei_information9%type;
340 l_eei_information12 pay_element_type_extra_info.eei_information10%type;
341 l_eei_information20 pay_element_type_extra_info.eei_information18%type;
342 l_configuration_information4 VARCHAR2(10) := 'N' ;
343 l_configuration_information5 VARCHAR2(10) := 'N' ;
344 l_configuration_information6 VARCHAR2(10) := 'N' ;
345 l_configuration_information7 VARCHAR2(10) := 'N' ;
346 l_configuration_information9 VARCHAR2(10) := 'Y' ;
347 l_configuration_information10 VARCHAR2(10) := 'N' ;
348 l_configuration_information11 VARCHAR2(10) := 'N' ;
349 l_configuration_information12 VARCHAR2(10) := 'N' ;
350 l_configuration_information13 VARCHAR2(10) := 'N' ;
351 l_configuration_information14 VARCHAR2(10) := 'N' ;
352 l_configuration_information15 VARCHAR2(10) := 'N' ;
353 l_configuration_information16 VARCHAR2(10) := 'N' ;
354 l_configuration_information17 VARCHAR2(10) := 'N' ;
355
356 l_ee_contribution_bal_type_id pqp_pension_types_f.ee_contribution_bal_type_id%type;
357 l_er_contribution_bal_type_id pqp_pension_types_f.er_contribution_bal_type_id%type;
358 l_pen_sal_bal_type_id pqp_pension_types_f.pension_salary_balance%type := -1;
359 l_balance_feed_Id pay_balance_feeds_f.balance_feed_id%type;
360 l_row_id rowid;
361 l_request_id Number;
362 l_er_request_id Number;
363 l_formula_text varchar2(32767);
364 l_formula_text1 varchar2(32767);
365 l_tax_si_text varchar2(32767);
366 l_oht_text varchar2(32767);
367 l_dbi_user_name ff_database_items.user_name%TYPE;
368 l_balance_name pay_balance_types.balance_name%TYPE;
369 l_balance_dbi_name ff_database_items.user_name%TYPE;
370
371 --
372 cursor csr_get_ele_info (c_ele_name varchar2) is
373 select element_type_id
374 ,object_version_number
375 from pay_shadow_element_types
376 where template_id = l_template_id
377 and element_name = c_ele_name;
378 --
379 cursor csr_get_bal_info (c_bal_name varchar2) is
380 select balance_type_id
381 ,object_version_number
382 from pay_shadow_balance_types
383 where template_id = l_template_id
384 and balance_name = c_bal_name;
385 --
386 cursor csr_shd_ele (c_shd_elename varchar2) is
387 select element_type_id, object_version_number
388 from pay_shadow_element_types
389 where template_id = l_template_id
390 and element_name = c_shd_elename;
391 --
392 cursor csr_ipv (c_ele_typeid number
393 ,c_effective_date date) is
394 select input_value_id
395 from pay_input_values_f
396 where element_type_id = c_ele_typeid
397 and business_group_id = p_business_group_id
398 and name = 'Pay Value'
399 and c_effective_date between effective_start_date
400 and effective_end_date;
401 --
402 cursor csr_pty (c_pension_type_id number
403 ,c_effective_date date) is
404 select ee_contribution_bal_type_id
405 from pqp_pension_types_f
406 where pension_type_id = c_pension_type_id
407 and business_group_id = p_business_group_id
408 and c_effective_date between effective_start_date
409 and effective_end_date;
410
411 cursor csr_pty1 (c_pension_type_id number
412 ,c_effective_date date) is
413 select *
414 from pqp_pension_types_f
415 where pension_type_id = c_pension_type_id
416 and business_group_id = p_business_group_id
417 and c_effective_date between effective_start_date
418 and effective_end_date;
419
420 cursor csr_pty2 (c_pension_type_id number
421 ,c_effective_date date) is
422 select er_contribution_bal_type_id
423 from pqp_pension_types_f
424 where pension_type_id = c_pension_type_id
425 and business_group_id = p_business_group_id
426 and c_effective_date between effective_start_date
427 and effective_end_date;
428
429 -- cursor added to query the pension_sal_bal_id
430 cursor csr_pty3 (c_pension_type_id number
431 ,c_effective_date date) is
432 select pension_salary_balance
433 from pqp_pension_types_f
434 where pension_type_id = c_pension_type_id
435 and business_group_id = p_business_group_id
436 and c_effective_date between effective_start_date
437 and effective_end_date;
438
439 r_pty_rec pqp_pension_types_f%ROWTYPE;
440
441
442 cursor csr_get_formula_txt (c_formula_id number) is
443 select formula_text
444 from pay_shadow_formulas
445 where formula_id = c_formula_id
446 and template_type = 'U';
447
448 cursor csr_get_dbi_user_name (c_bal_type_id NUMBER) IS
449 select user_name
450 from ff_database_items dbi
451 ,ff_route_parameter_values rpv
452 ,ff_route_parameters rp
453 ,pay_balance_dimensions pbd
454 ,pay_defined_balances pdb
455 where dbi.user_entity_id = rpv.user_entity_id
456 and rpv.route_parameter_id = rp.route_parameter_id
457 and rp.route_id = pbd.route_id
458 AND pbd.database_item_suffix = '_PER_YTD'
459 and pdb.BALANCE_DIMENSION_ID = pbd.BALANCE_DIMENSION_ID
460 and pdb.balance_type_id = to_char(c_bal_type_id)
461 and pbd.legislation_code = 'NL'
462 AND rpv.value = pdb.DEFINED_BALANCE_ID;
463
464 -- cursor added to find the dbi name for the Pension Salary Balance for ABP
465
466 cursor csr_get_pen_sal_bal_dbi_name (c_bal_type_id NUMBER) IS
467 select user_name
468 from ff_database_items dbi
469 ,ff_route_parameter_values rpv
470 ,ff_route_parameters rp
471 ,pay_balance_dimensions pbd
472 ,pay_defined_balances pdb
473 where dbi.user_entity_id = rpv.user_entity_id
474 and rpv.route_parameter_id = rp.route_parameter_id
475 and rp.route_id = pbd.route_id
476 AND pbd.database_item_suffix = '_ASG_RUN'
477 and pdb.BALANCE_DIMENSION_ID = pbd.BALANCE_DIMENSION_ID
478 and pdb.balance_type_id = to_char(c_bal_type_id)
479 and pbd.legislation_code = 'NL'
480 AND rpv.value = pdb.DEFINED_BALANCE_ID ;
481
482 -- cursor added to find the balance name for the Pension Salary Balance for
483 cursor csr_get_pen_sal_bal_name (c_bal_type_id NUMBER) IS
484 select balance_name
485 from pay_balance_types
486 where balance_type_id = c_bal_type_id
487 and (business_group_id = p_business_group_id
488 OR business_group_id is null
489 OR legislation_code = 'NL');
490
491
492 CURSOR chk_pension_scheme_name_cur IS
493 SELECT 'x'
494 FROM pay_element_type_extra_info
495 WHERE eei_information_category = 'PQP_NL_PRE_TAX_DEDUCTIONS'
496 AND eei_information1 = p_scheme_description
497 AND rownum = 1;
498
499 l_scheme_dummy varchar2(10);
500 -- ---------------------------------------------------------------------
501 -- |------------------------< Get_Template_ID >-------------------------|
502 -- ---------------------------------------------------------------------
503 function Get_Template_ID (p_legislation_code in Varchar2)
504 return Number is
505 --
506 l_template_name Varchar2(80);
507 l_proc_name Varchar2(72) := g_proc_name || 'get_template_id';
508 --
509 cursor csr_get_temp_id is
510 select template_id
511 from pay_element_templates
512 where template_name = l_template_name
513 and legislation_code = p_legislation_code
514 and template_type = 'T'
515 and business_group_id is null;
516 --
517 begin
518 --
519 hr_utility.set_location('Entering: '||l_proc_name, 10);
520 --
521 l_template_name := 'Dutch Pension Deduction';
522 --
523 hr_utility.set_location(l_proc_name, 20);
524 --
525 for csr_get_temp_id_rec in csr_get_temp_id loop
526 l_template_id := csr_get_temp_id_rec.template_id;
527 end loop;
528 --
529 hr_utility.set_location('Leaving: '||l_proc_name, 30);
530 --
531 return l_template_id;
532 --
533 end Get_Template_ID;
534
535 -- ---------------------------------------------------------------------
536 -- |-----------------------< Create_Pen_Sal_Bal_Feeds >-----------------|
537 -- ---------------------------------------------------------------------
538 procedure Create_Pen_Sal_Bal_Feeds is
539 --
540 l_row_id rowid;
541 l_balance_feed_Id pay_balance_feeds_f.balance_feed_id%type;
542 l_proc_name Varchar2(80) := g_proc_name ||
543 'Create_Pen_Sal_Bal_Feeds ';
544 --
545 cursor c1_get_reg_earn_feeds is
546 select bc.classification_id, pbf.input_value_id,
547 pbf.scale, pbf.element_type_id
548 from pay_balance_feeds_v pbf,
549 pay_balance_classifications bc,
550 pay_element_classifications pec,
551 pay_element_classifications_tl pect,
552 pay_balance_types_tl pbtl
553 where nvl(pbf.balance_initialization_flag,'N') = 'N'
554 and nvl(pbf.business_group_id,
555 p_business_group_id) = p_business_group_id
556 and nvl(pbf.legislation_code, 'NL') = 'NL'
557 and pbtl.balance_name = 'Gross Salary'
558 and pbtl.language = 'US'
559 and pbtl.balance_type_id = pbf.balance_type_id
560 and bc.balance_type_id = pbf.balance_type_id
561 and pec.classification_id = pect.classification_id
562 and bc.classification_id = pec.classification_id
563 and pect.classification_name = 'Earnings'
564 and pect.language = 'US'
565 and nvl(pec.legislation_code, 'NL') = 'NL'
566 order by pbf.element_name;
567
568 --
569 cursor c2_balance_type is
570 select balance_type_id
571 from pay_balance_types
572 where business_group_id = p_business_group_id
573 and balance_name in (p_scheme_prefix||' Pension Salary');
574 begin
575 hr_utility.set_location('Entering: '||l_proc_name, 10);
576 for c1_rec in c1_get_reg_earn_feeds loop
577 for c2_rec in c2_balance_type loop
578 Pay_Balance_Feeds_f_pkg.Insert_Row
579 (X_Rowid => l_row_id,
580 X_Balance_Feed_Id => l_Balance_Feed_Id,
581 X_Effective_Start_Date => p_effective_start_date,
582 X_Effective_End_Date => hr_api.g_eot,
583 X_Business_Group_Id => p_business_group_id,
584 X_Legislation_Code => null,
585 X_Balance_Type_Id => c2_rec.balance_type_id,
586 X_Input_Value_Id => c1_rec.input_value_id,
587 X_Scale => c1_rec.scale,
588 X_Legislation_Subgroup => null,
589 X_Initial_Balance_Feed => false );
590
591 l_Balance_Feed_Id := Null;
592 l_row_id := Null;
593 end loop;
594 end loop;
595 hr_utility.set_location('Leaving: '||l_proc_name, 70);
596 end Create_Pen_Sal_Bal_Feeds ;
597
598 begin
599 -- ---------------------------------------------------------------------
600 -- |-------------< Main Function : Create_User_Template Body >----------|
601 -- ---------------------------------------------------------------------
602 hr_utility.set_location('Entering : '||l_proc_name, 10);
603
604 chk_scheme_prefix(p_scheme_prefix);
605
606 hr_utility.set_location('Check unique scheme name : '||l_proc_name, 11);
607 OPEN chk_pension_scheme_name_cur;
608 FETCH chk_pension_scheme_name_cur INTO l_scheme_dummy;
609 IF chk_pension_scheme_name_cur%FOUND THEN
610 CLOSE chk_pension_scheme_name_cur;
611 fnd_message.set_name('PQP', 'PQP_230924_SCHEME_NAME_ERR');
612 fnd_message.raise_error;
613 ELSE
614 CLOSE chk_pension_scheme_name_cur;
615 END IF;
616
617 -- ---------------------------------------------------------------------
618 -- Set session date
619 -- ---------------------------------------------------------------------
620 pay_db_pay_setup.set_session_date(nvl(p_effective_start_date, sysdate));
621 --
622 hr_utility.set_location('..Setting the Session Date', 15);
623 -- ---------------------------------------------------------------------
624 -- Get Source Template ID
625 -- ---------------------------------------------------------------------
626 l_source_template_id := get_template_id
627 (p_legislation_code => g_template_leg_code);
628 -- ---------------------------------------------------------------------
629 -- Exclusion rules
630 -- ---------------------------------------------------------------------
631 hr_utility.set_location('..Checking all the Exclusion Rules', 20);
632
633 -- Define the exclusion_rule based on the salary calculation method.
634
635 OPEN csr_pty1 (c_pension_type_id => p_pension_type_id
636 ,c_effective_date => p_effective_start_date);
637 FETCH csr_pty1 INTO r_pty_rec;
638
639 IF csr_pty1%notfound THEN
640 fnd_message.set_name('PQP', 'PQP_230805_INV_PENSIONID');
641 fnd_message.raise_error;
642 CLOSE csr_pty1;
643 ELSE
644
645 -- Fixed premium amount exclusion rules
646 IF p_deduction_method = 'PE' AND
647 r_pty_rec.salary_calculation_method = '3' THEN
648 l_configuration_information4 := 'Y';
649 l_configuration_information5 := 'Y';
650 ELSE
651 l_configuration_information4 := 'N';
652 l_configuration_information5 := 'N';
653 END IF;
654
655 -- Exclusion rule to make sure that both inputs are not
656 -- created when the sl_calc_mthd = 3
657 IF p_employer_component = 'Y' AND
658 r_pty_rec.salary_calculation_method = '3' THEN
659 l_configuration_information6 := 'N';
660 l_configuration_information7 := 'N';
661 ELSE
662 IF p_employer_component = 'Y' AND p_deduction_method = 'PE' THEN
663 l_configuration_information6 := 'N';
664 l_configuration_information7 := 'Y';
665 ELSIF p_employer_component = 'Y'AND p_deduction_method = 'FA' THEN
666 l_configuration_information6 := 'Y';
667 l_configuration_information7 := 'N';
668 END IF;
669 END IF;
670
671 -- added for setting up exclusion rule for pension salary balance
672
673 OPEN csr_pty3 (c_pension_type_id => p_pension_type_id
674 ,c_effective_date => p_effective_start_date);
675 FETCH csr_pty3 INTO l_pen_sal_bal_type_id;
676
677 IF csr_pty3%notfound THEN
678 CLOSE csr_pty3;
679 fnd_message.set_name('PQP', 'PQP_230805_INV_PENSIONID');
680 fnd_message.raise_error;
681 ELSE
682 IF l_pen_sal_bal_type_id is not null then
683 l_configuration_information9 := 'N';
684 ELSE
685 l_configuration_information9 := 'Y';
686 END IF;
687 CLOSE csr_pty3;
688 END IF;
689
690 CLOSE csr_pty1;
691
692 END IF;
693
694 -- setup exclusion rules for formula results to the SI Gross Taxation Balances
695 IF r_pty_rec.sig_sal_spl_tax_reduction IS NOT NULL THEN
696 l_configuration_information11 := 'Y';
697 END IF;
698
699 IF r_pty_rec.sig_sal_non_tax_reduction IS NOT NULL THEN
700 l_configuration_information12 := 'Y';
701 END IF;
702
703 IF r_pty_rec.sig_sal_std_tax_reduction IS NOT NULL THEN
704 l_configuration_information10 := 'Y';
705 END IF;
706
707 IF r_pty_rec.std_tax_reduction IS NOT NULL THEN
708 l_configuration_information13 := 'Y';
709 END IF;
710
711 IF r_pty_rec.spl_tax_reduction IS NOT NULL THEN
712 l_configuration_information14 := 'Y';
713 END IF;
714
715 IF r_pty_rec.sii_std_tax_reduction IS NOT NULL THEN
716 l_configuration_information15 := 'Y';
717 END IF;
718
719 IF r_pty_rec.sii_spl_tax_reduction IS NOT NULL THEN
720 l_configuration_information16 := 'Y';
721 END IF;
722
723 IF r_pty_rec.sii_non_tax_reduction IS NOT NULL THEN
724 l_configuration_information17 := 'Y';
725 END IF;
726
727
728
729 -- ---------------------------------------------------------------------
730 -- Create user structure from the template
731 -- ---------------------------------------------------------------------
732 hr_utility.set_location('..Creating template User structure', 25);
733 pay_element_template_api.create_user_structure
734 (p_validate => false
735 ,p_effective_date => p_effective_start_date
736 ,p_business_group_id => p_business_group_id
737 ,p_source_template_id => l_source_template_id
738 ,p_base_name => p_scheme_prefix
739 ,p_configuration_information1 => p_deduction_method
740 ,p_configuration_information2 => p_deduction_method
741 ,p_configuration_information3 => p_employer_component
742 ,p_configuration_information4 => l_configuration_information4
743 ,p_configuration_information5 => l_configuration_information5
744 ,p_configuration_information6 => l_configuration_information6
745 ,p_configuration_information7 => l_configuration_information7
746 ,p_configuration_information8 => p_arrearage_flag
747 ,p_configuration_information9 => l_configuration_information9
748 ,p_configuration_information10 => l_configuration_information10
749 ,p_configuration_information11 => l_configuration_information11
750 ,p_configuration_information12 => l_configuration_information12
751 ,p_configuration_information13 => l_configuration_information13
752 ,p_configuration_information14 => l_configuration_information14
753 ,p_configuration_information15 => l_configuration_information15
754 ,p_configuration_information16 => l_configuration_information16
755 ,p_configuration_information17 => l_configuration_information17
756 ,p_template_id => l_template_id
757 ,p_object_version_number => l_object_version_number
758 );
759 -- ---------------------------------------------------------------------
760 -- |-------------------< Update Shadow Structure >----------------------|
761 -- ---------------------------------------------------------------------
762 -- Get Element Type id and update user-specified Classification,
763 -- Category, Processing Type and Standard Link on Base Element
764 -- as well as other element created for the Scheme
765 -- ---------------------------------------------------------------------
766 -- 1. <BASE NAME> Special Inputs
767 for csr_rec in csr_shd_ele (p_scheme_prefix||' Special Inputs')
768 loop
769 l_count := l_count + 1;
770 l_shadow_element(l_count).element_type_id := csr_rec.element_type_id;
771 l_shadow_element(l_count).object_version_number := csr_rec.object_version_number;
772 l_shadow_element(l_count).reporting_name := nvl(p_reporting_name,p_scheme_prefix)
773 ||' SI';
774 l_shadow_element(l_count).description := 'Element for '||p_scheme_prefix
775 ||' Special Inputs';
776 end loop;
777 -- 2. <BASE NAME> Pension Deduction
778 for csr_rec in csr_shd_ele (p_scheme_prefix||' Pension Deduction')
779 loop
780 l_count := l_count + 1;
781 l_shadow_element(l_count).element_type_id := csr_rec.element_type_id;
782 l_shadow_element(l_count).object_version_number := csr_rec.object_version_number;
783 l_shadow_element(l_count).reporting_name := nvl(p_reporting_name,p_scheme_prefix);
784 l_shadow_element(l_count).description := 'Element for '||p_scheme_prefix
785 ||' Pension Deduction';
786 end loop;
787 -- 3. <BASE NAME> SI Gross Standard Adjustment
788 for csr_rec in csr_shd_ele (p_scheme_prefix||' SI Gross Standard Adjustment')
789 loop
790 l_count := l_count +1;
791 l_shadow_element(l_count).element_type_id := csr_rec.element_type_id;
792 l_shadow_element(l_count).object_version_number := csr_rec.object_version_number;
793 l_shadow_element(l_count).reporting_name := nvl(p_reporting_name,p_scheme_prefix)
794 ||' SI Gross Std. Adj.';
795 l_shadow_element(l_count).description := 'Element for '||p_scheme_prefix
796 ||' SI Gross Standard Adjustment';
797 end loop;
798 -- 4. <BASE NAME> Standard Tax Adjustment
799 for csr_rec in csr_shd_ele (p_scheme_prefix||' Standard Tax Adjustment')
800 loop
801 l_count := l_count + 1;
802 l_shadow_element(l_count).element_type_id := csr_rec.element_type_id;
803 l_shadow_element(l_count).object_version_number := csr_rec.object_version_number;
804 l_shadow_element(l_count).reporting_name := nvl(p_reporting_name,p_scheme_prefix)
805 ||' Std. Tax Adj.';
806 l_shadow_element(l_count).description := 'Element for '||p_scheme_prefix
807 ||' Standard Tax Adjustment';
808 end loop;
809 -- 5. <BASE NAME> SI Income Standard Adjustment
810 for csr_rec in csr_shd_ele (p_scheme_prefix||' SI Income Standard Adjustment')
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 Income Std. Adj.';
817 l_shadow_element(l_count).description := 'Element for '||p_scheme_prefix
818 ||' SI Income Standard Adjustment';
819 end loop;
820 -- 6. <BASE NAME> SI Gross Special Adjustment
821 for csr_rec in csr_shd_ele (p_scheme_prefix||' SI Gross Special Adjustment')
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 ||' SI Gross Spl. Adj.';
828 l_shadow_element(l_count).description := 'Element for '||p_scheme_prefix
829 ||' SI Gross Special Adjustment';
830 end loop;
831 -- 7. <BASE NAME> Special Tax Adjustment
832 for csr_rec in csr_shd_ele (p_scheme_prefix||' Special Tax Adjustment')
833 loop
834 l_count := l_count + 1 ;
835 l_shadow_element(l_count).element_type_id := csr_rec.element_type_id;
836 l_shadow_element(l_count).object_version_number := csr_rec.object_version_number;
837 l_shadow_element(l_count).reporting_name := nvl(p_reporting_name,p_scheme_prefix)
838 ||' Spl. Tax Adj.';
839 l_shadow_element(l_count).description := 'Element for '||p_scheme_prefix
840 ||' Special Tax Adjustment';
841 end loop;
842 -- 8. <BASE NAME> SI Income Special Adjustment
843 for csr_rec in csr_shd_ele (p_scheme_prefix||' SI Income Special Adjustment')
844 loop
845 l_count := l_count + 1 ;
846 l_shadow_element(l_count).element_type_id := csr_rec.element_type_id;
847 l_shadow_element(l_count).object_version_number := csr_rec.object_version_number;
848 l_shadow_element(l_count).reporting_name := nvl(p_reporting_name,p_scheme_prefix)
849 ||' SI Income Spl. Adj';
850 l_shadow_element(l_count).description := 'Element for '||p_scheme_prefix
851 ||' SI Income Special Adjustment';
852 end loop;
853 -- 9. <BASE NAME> SI Gross Non Tax Adjustment
854 for csr_rec in csr_shd_ele (p_scheme_prefix||' SI Gross Non Tax Adjustment')
855 loop
856 l_count := l_count + 1 ;
857 l_shadow_element(l_count).element_type_id := csr_rec.element_type_id;
858 l_shadow_element(l_count).object_version_number := csr_rec.object_version_number;
859 l_shadow_element(l_count).reporting_name := nvl(p_reporting_name,p_scheme_prefix)
860 ||' SI Gross Non Tax Adj.';
861 l_shadow_element(l_count).description := 'Element for '||p_scheme_prefix
862 ||' SI Gross Non Tax Adjustment';
863 end loop;
864 -- 10. <BASE NAME> SI Income Non Tax Adjustment
865 for csr_rec in csr_shd_ele (p_scheme_prefix||' SI Income Non Tax Adjustment')
866 loop
867 l_count := l_count + 1 ;
868 l_shadow_element(l_count).element_type_id := csr_rec.element_type_id;
869 l_shadow_element(l_count).object_version_number := csr_rec.object_version_number;
870 l_shadow_element(l_count).reporting_name := nvl(p_reporting_name,p_scheme_prefix)
871 ||' SI Income Non Tax Adj.';
872 l_shadow_element(l_count).description := 'Element for '||p_scheme_prefix
873 ||' SI Income Non Tax Adjustment';
874 end loop;
875 -- 11. <BASE NAME> Special Features
876 for csr_rec in csr_shd_ele (p_scheme_prefix||' Special Features')
877 loop
878 l_count := l_count + 1;
879 l_shadow_element(l_count).element_type_id := csr_rec.element_type_id;
880 l_shadow_element(l_count).object_version_number := csr_rec.object_version_number;
881 l_shadow_element(l_count).reporting_name := nvl(p_reporting_name,p_scheme_prefix)
882 ||' SF';
883 l_shadow_element(l_count).description := 'Element for '||p_scheme_prefix
884 ||' Special Features';
885 end loop;
886
887 -- 12. <BASE NAME> Employer Pension Contribution
888 IF p_employer_component = 'Y' THEN
889 for csr_rec in csr_shd_ele (p_scheme_prefix||' Employer Pension Contribution')
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 ||' ER Pension Contribution';
896 l_shadow_element(l_count).description := 'Element for '||p_scheme_prefix
897 ||' Employer Pension Contribution';
898 end loop;
899 END IF;
900
901 -- 13. <BASE NAME> Tax SI Adjustment
902
903 FOR csr_rec IN csr_shd_ele (p_scheme_prefix||' Tax SI Adjustment')
904 LOOP
905 l_count := l_count + 1;
906 l_shadow_element(l_count).element_type_id
907 := csr_rec.element_type_id;
908 l_shadow_element(l_count).object_version_NUMBER
909 := csr_rec.object_version_NUMBER;
910 l_shadow_element(l_count).reporting_name
911 := NVL(p_reporting_name,p_scheme_prefix)||' Tax SI Adjustment';
912 l_shadow_element(l_count).description
913 := 'Element for '||p_scheme_prefix||' Tax SI Adjustment';
914 END LOOP;
915
916
917 hr_utility.set_location('..Updating the scheme shadow elements', 30);
918 for i in 1..l_count
919 loop
920 -- Set the standard link flag only for EE and ER elements
921 -- if the standard link is Y
922 IF UPPER(p_standard_link) = 'Y' THEN
923 IF l_shadow_element(i).description LIKE '%Pension Deduction' OR
924 l_shadow_element(i).description LIKE '%Employer Pension Contribution' THEN
925 l_std_link_flag := 'Y';
926 ELSE
927 l_std_link_flag := 'N';
928 END IF;
929 END IF;
930
931 pay_shadow_element_api.update_shadow_element
932 (p_validate => false
933 ,p_effective_date => p_effective_start_date
934 ,p_element_type_id => l_shadow_element(i).element_type_id
935 ,p_description => l_shadow_element(i).description
936 ,p_reporting_name => l_shadow_element(i).reporting_name
937 ,p_post_termination_rule => p_termination_rule
938 ,p_standard_link_flag => nvl(l_std_link_flag, hr_api.g_varchar2)
939 ,p_object_version_number => l_shadow_element(i).object_version_number
940 );
941
942 -- Reset the value for standard link flag.
943 l_std_link_flag := 'N';
944
945 end loop;
946 hr_utility.set_location('..After Updating the scheme shadow elements', 50);
947
948 -- Replace the spaces in the prefix with underscores. The formula name
949 -- has underscores if the prefix name has spaces in it .
950 l_scheme_prefix := upper(replace(l_scheme_prefix,' ','_'));
951
952
953 -- Update Shadow formula
954
955 l_shad_formula_id := Get_Formula_Id(l_scheme_prefix||'_PENSION_DEDUCTION'
956 ,p_business_group_id);
957
958
959 OPEN csr_pty (c_pension_type_id => p_pension_type_id
960 ,c_effective_date => p_effective_start_date);
961 FETCH csr_pty INTO l_ee_contribution_bal_type_id;
962
963 IF csr_pty%notfound THEN
964 fnd_message.set_name('PQP', 'PQP_230805_INV_PENSIONID');
965 fnd_message.raise_error;
966 CLOSE csr_pty;
967 ELSE
968
969 FOR temp_rec IN csr_get_formula_txt(l_shad_formula_id)
970 LOOP
971 l_formula_text := temp_rec.formula_text;
972 END LOOP;
973
974 FOR temp_rec IN csr_get_dbi_user_name(l_ee_contribution_bal_type_id)
975 LOOP
976 l_dbi_user_name := temp_rec.user_name;
977 l_formula_text := replace(l_formula_text,'REPLACE_PT_EE_BAL_PER_YTD',
978 l_dbi_user_name);
979
980 update pay_shadow_formulas
981 set formula_text = l_formula_text
982 where formula_id = l_shad_formula_id
983 and business_group_id = p_business_group_id;
984
985 END LOOP;
986 END IF;
987
988 CLOSE csr_pty;
989
990 -- added to replace the salary balance name , DBI in the formula text
991
992 IF l_pen_sal_bal_type_id is not null then -- a balance already exists at the PT level
993 IF l_pen_sal_bal_type_id <> -1 then
994 FOR temp_rec IN csr_get_formula_txt(l_shad_formula_id)
995 LOOP
996 l_formula_text := temp_rec.formula_text;
997 END LOOP;
998
999 -- query up the balance name and replace it in formula text
1000 FOR temp_rec IN csr_get_pen_sal_bal_name(l_pen_sal_bal_type_id)
1001 LOOP
1002 l_balance_name := temp_rec.balance_name;
1003 l_formula_text := replace(l_formula_text,'REPLACE_PENSION_SALARY_BAL_NAME',
1004 l_balance_name);
1005
1006 update pay_shadow_formulas
1007 set formula_text = l_formula_text
1008 where formula_id = l_shad_formula_id
1009 and business_group_id = p_business_group_id;
1010
1011 END LOOP;
1012
1013 -- query up the dbi user name and replace it in formula text
1014 FOR temp_rec IN csr_get_pen_sal_bal_dbi_name(l_pen_sal_bal_type_id)
1015 LOOP
1016 l_balance_dbi_name := temp_rec.user_name;
1017 l_formula_text := replace(l_formula_text,'REPLACE_PENSION_SALARY_BAL_DBI',
1018 l_balance_dbi_name);
1019
1020 update pay_shadow_formulas
1021 set formula_text = l_formula_text
1022 where formula_id = l_shad_formula_id
1023 and business_group_id = p_business_group_id;
1024
1025 END LOOP;
1026 END IF;
1027
1028 ELSE -- a new balance has been created from the template (l_pen_sal_bal_type_id is null)
1029 FOR temp_rec IN csr_get_formula_txt(l_shad_formula_id)
1030 LOOP
1031 l_formula_text := temp_rec.formula_text;
1032 END LOOP;
1033 l_formula_text := replace(l_formula_text,'REPLACE_PENSION_SALARY_BAL_DBI',
1034 l_scheme_prefix||'_PENSION_SALARY_ASG_RUN');
1035
1036 l_formula_text := replace(l_formula_text,'REPLACE_PENSION_SALARY_BAL_NAME',
1037 p_scheme_prefix||' Pension Salary');
1038
1039 update pay_shadow_formulas
1040 set formula_text = l_formula_text
1041 where formula_id = l_shad_formula_id
1042 and business_group_id = p_business_group_id;
1043
1044 END IF;
1045
1046 -- replace the taxation and social insurance balance reduction text in the --formula
1047 pqp_pension_functions.gen_dynamic_formula(p_pension_type_id => p_pension_type_id
1048 ,p_effective_date => p_effective_start_date
1049 ,p_formula_string => l_tax_si_text);
1050
1051 FOR temp_rec IN csr_get_formula_txt(l_shad_formula_id)
1052 LOOP
1053 l_formula_text := temp_rec.formula_text;
1054 END LOOP;
1055 l_formula_text := replace(l_formula_text,'REPLACE_TAX_SI_TEXT',
1056 l_tax_si_text);
1057
1058 --
1059 -- Update the formula to reflect the OHT Changes in pension salary
1060 -- This is to be done only if the pension sub category is ANW
1061 --
1062
1063 OPEN csr_pty1 (c_pension_type_id => p_pension_type_id
1064 ,c_effective_date => p_effective_start_date);
1065 FETCH csr_pty1 INTO r_pty_rec;
1066 IF csr_pty1%FOUND THEN
1067 IF r_pty_rec.pension_sub_category = 'C_ANW' THEN
1068 l_oht_text :=
1069
1070 ' l_ret_val = PQP_PRORATE_AMOUNT
1071 ( l_oht_max
1072 ,''Y''
1073 ,l_work_pattern
1074 ,l_tresh_conv_rule
1075 ,l_oht_max_pp
1076 ,l_error_message
1077 ,l_proc_period_name
1078 ,Override_Pension_Days)
1079
1080 IF l_ret_val = 1 THEN
1081 (
1082 error_mesg = l_error_message
1083 return error_mesg
1084 )
1085
1086 /* Apply OHT to the pension salary */
1087 l_pension_salary_oht = l_pension_salary/l_oht_percent
1088
1089 /* Calculate the difference to compare with the pay period limit */
1090 l_oht_comp_val = l_pension_salary - l_pension_salary_oht
1091
1092 /* Amend pension salary with applicable OHT */
1093 IF l_oht_comp_val <= l_oht_max_pp THEN
1094 (
1095 l_pension_salary = l_pension_salary_oht
1096 )
1097 ELSE IF l_oht_comp_val > l_oht_max_pp THEN
1098 (
1099 l_pension_salary = l_pension_salary - l_oht_max_pp
1100 )';
1101 ELSE
1102 l_oht_text := ' ';
1103 END IF;
1104 ELSE
1105 l_oht_text := ' ';
1106 END IF;
1107
1108 CLOSE csr_pty1;
1109
1110 l_formula_text := replace(l_formula_text,
1111 'REPLACE_OHT_TEXT',
1112 l_oht_text);
1113
1114 update pay_shadow_formulas
1115 set formula_text = l_formula_text
1116 where formula_id = l_shad_formula_id
1117 and business_group_id = p_business_group_id;
1118
1119 IF p_employer_component = 'Y' AND l_configuration_information6 = 'N'
1120 AND l_configuration_information7 = 'N' THEN
1121
1122 l_prem_replace_string := '
1123 ELSE IF Percentage WAS DEFAULTED
1124 AND Amount WAS DEFAULTED THEN
1125 /* Percentage of fixed premium amount calculation */
1126 ( ';
1127 l_prem_replace_string := l_prem_replace_string ||'
1128
1129 l_ee_pen_dedn_prem_amt =
1130 '|| l_scheme_prefix ||'_PENSION_DEDUCTION_FIXED_PREMIUM_AMOUNT_ENTRY_VALUE'
1131 ||'
1132
1133 l_annual_prem_amt_char = '' ''
1134 l_ret_val = PQP_GET_PENSION_TYPE_DETAILS( Pension_Type_Id
1135 ,'' ''
1136 ,''ANNUAL_PREMIUM_AMOUNT''
1137 ,l_annual_prem_amt_char
1138 ,l_error_message)
1139
1140 IF l_ret_val = 1 THEN
1141 (
1142 error_mesg = l_error_message
1143 return error_mesg
1144 )
1145 ELSE
1146 (
1147 /* Fixed premium amount is the least of the value
1148 entered on the pension type and the value entered in the
1149 input Fixed Premium Amount
1150 */
1151 l_ee_pen_dedn_prem_amt = LEAST(l_ee_pen_dedn_prem_amt,TO_NUMBER(l_annual_prem_amt_char))
1152 )
1153
1154 l_ee_pen_dedn_percent =
1155 '|| l_scheme_prefix ||'_PENSION_DEDUCTION_PERCENTAGE_ENTRY_VALUE
1156 l_er_prem_amt = l_ee_pen_dedn_prem_amt - l_ee_pen_dedn_prem_amt * (l_ee_pen_dedn_percent/100)
1157 l_fixed_prem_flag = ''Y''
1158
1159 l_ret_val = PQP_PRORATE_AMOUNT ( l_er_prem_amt
1160 ,''Y''
1161 ,l_work_pattern
1162 ,l_contrib_conv_rule
1163 ,dedn_amt
1164 ,l_error_message
1165 ,l_proc_period_name
1166 ,Override_Pension_Days)
1167
1168 IF l_ret_val = 1 THEN
1169 (
1170 error_mesg = l_error_message
1171 return error_mesg
1172 )
1173
1174 IF (l_ret_val = 2 AND l_tmp_decimal_realdays <> 1 )THEN
1175 (
1176 l_tmp_decimal_realdays = 1
1177 mesg = mesg || '''|| l_scheme_prefix || ' Employer Pension Contribution : ''
1178 mesg = mesg||''Real SI Days value rounded as it is to be a whole number .''
1179 )
1180 IF (l_ret_val = 3) THEN
1181 (
1182 dedn_amt = 0
1183 mesg = '''||l_scheme_prefix||' Employer Pension Contribution : ''
1184 mesg = mesg||'' Deduction amount cannot be calculated since ''
1185 mesg = mesg||''no workpattern is attached to the assignment.''
1186 return dedn_amt,mesg
1187 )
1188 ELSE IF(l_ret_val = 4 AND l_avg_ws1 <> 1) THEN
1189 (
1190 l_avg_ws1 = 1
1191 mesg = mesg||'''||l_scheme_prefix||' Employer Pension Contribution: ''
1192 mesg = mesg||''Average Days have been used in the proration instead ''
1193 mesg = mesg||''of Average Days with Work Schedules since no workpattern ''
1194 mesg = mesg||'' is attached to the assignment. ''
1195 )
1196
1197 )';
1198
1199 ELSE
1200
1201 l_prem_replace_string := ' ' ;
1202
1203 END IF;
1204
1205
1206 IF p_employer_component = 'Y' THEN
1207
1208 l_shad_formula_id1 := Get_Formula_Id(l_scheme_prefix||'_EMPLOYER_PENSION_CONTRIBUTION'
1209 ,p_business_group_id);
1210
1211 OPEN csr_pty2 (c_pension_type_id => p_pension_type_id
1212 ,c_effective_date => p_effective_start_date);
1213 FETCH csr_pty2 INTO l_er_contribution_bal_type_id;
1214
1215 IF csr_pty2%notfound THEN
1216 fnd_message.set_name('PQP', 'PQP_230805_INV_PENSIONID');
1217 fnd_message.raise_error;
1218 CLOSE csr_pty2;
1219 ELSE
1220
1221 FOR temp_rec IN csr_get_formula_txt(l_shad_formula_id1)
1222 LOOP
1223 l_formula_text1 := temp_rec.formula_text;
1224 END LOOP;
1225
1226 FOR temp_rec IN csr_get_dbi_user_name(l_er_contribution_bal_type_id)
1227 LOOP
1228 l_dbi_user_name := temp_rec.user_name;
1229 l_formula_text1 := replace(l_formula_text1,'REPLACE_PT_ER_BAL_PER_YTD',
1230 l_dbi_user_name);
1231 l_formula_text1 := replace(l_formula_text1,'REPLACE_PREM_AMT_FORMULA_TEXT',
1232 l_prem_replace_string);
1233
1234
1235 update pay_shadow_formulas
1236 set formula_text = l_formula_text1
1237 where formula_id = l_shad_formula_id1
1238 and business_group_id = p_business_group_id;
1239
1240 END LOOP;
1241 END IF;
1242
1243 CLOSE csr_pty2;
1244 -- to replace the salary balance name , DBI in the formula text
1245
1246 IF l_pen_sal_bal_type_id is not null then -- a balance already exists at the PT level
1247 IF l_pen_sal_bal_type_id <> -1 then
1248 FOR temp_rec IN csr_get_formula_txt(l_shad_formula_id1)
1249 LOOP
1250 l_formula_text1 := temp_rec.formula_text;
1251 END LOOP;
1252
1253 -- query up the balance name and replace it in formula text
1254 FOR temp_rec IN csr_get_pen_sal_bal_name(l_pen_sal_bal_type_id)
1255 LOOP
1256 l_balance_name := temp_rec.balance_name;
1257 l_formula_text1 := replace(l_formula_text1,'REPLACE_PENSION_SALARY_BAL_NAME',
1258 l_balance_name);
1259
1260 update pay_shadow_formulas
1261 set formula_text = l_formula_text1
1262 where formula_id = l_shad_formula_id1
1263 and business_group_id = p_business_group_id;
1264
1265 END LOOP;
1266
1267 -- query up the dbi user name and replace it in formula text
1268 FOR temp_rec IN csr_get_pen_sal_bal_dbi_name(l_pen_sal_bal_type_id)
1269 LOOP
1270 l_balance_dbi_name := temp_rec.user_name;
1271 l_formula_text1 := replace(l_formula_text1,'REPLACE_PENSION_SALARY_BAL_DBI',
1272 l_balance_dbi_name);
1273
1274 update pay_shadow_formulas
1275 set formula_text = l_formula_text1
1276 where formula_id = l_shad_formula_id1
1277 and business_group_id = p_business_group_id;
1278
1279 END LOOP;
1280 END IF;
1281
1282 ELSE -- a new balance has been created from the template (l_pen_sal_bal_type_id is null)
1283 FOR temp_rec IN csr_get_formula_txt(l_shad_formula_id1)
1284 LOOP
1285 l_formula_text1 := temp_rec.formula_text;
1286 END LOOP;
1287 l_formula_text1 := replace(l_formula_text1,'REPLACE_PENSION_SALARY_BAL_DBI',
1288 l_scheme_prefix||'_PENSION_SALARY_ASG_RUN');
1289
1290 l_formula_text1 := replace(l_formula_text1,'REPLACE_PENSION_SALARY_BAL_NAME',
1291 p_scheme_prefix||' Pension Salary');
1292
1293 update pay_shadow_formulas
1294 set formula_text = l_formula_text1
1295 where formula_id = l_shad_formula_id1
1296 and business_group_id = p_business_group_id;
1297
1298 --
1299 -- Update the formula to reflect the OHT Changes in pension salary
1300 -- This is to be done only if the pension sub category is ANW
1301 --
1302
1303 OPEN csr_pty1 (c_pension_type_id => p_pension_type_id
1304 ,c_effective_date => p_effective_start_date);
1305 FETCH csr_pty1 INTO r_pty_rec;
1306 IF csr_pty1%FOUND THEN
1307 IF r_pty_rec.pension_sub_category = 'C_ANW' THEN
1308 l_oht_text :=
1309
1310 ' l_ret_val = PQP_PRORATE_AMOUNT
1311 ( l_oht_max
1312 ,''Y''
1313 ,l_work_pattern
1314 ,l_tresh_conv_rule
1315 ,l_oht_max_pp
1316 ,l_error_message
1317 ,l_proc_period_name
1318 ,Override_Pension_Days)
1319
1320 IF l_ret_val = 1 THEN
1321 (
1322 error_mesg = l_error_message
1323 return error_mesg
1324 )
1325
1326 /* Apply OHT to the pension salary */
1327 l_pension_salary_oht = l_pension_salary/l_oht_percent
1328
1329 /* Calculate the difference to compare with the pay period limit */
1330 l_oht_comp_val = l_pension_salary - l_pension_salary_oht
1331
1332 /* Amend pension salary with applicable OHT */
1333 IF l_oht_comp_val <= l_oht_max_pp THEN
1334 (
1335 l_pension_salary = l_pension_salary_oht
1336 )
1337 ELSE IF l_oht_comp_val > l_oht_max_pp THEN
1338 (
1339 l_pension_salary = l_pension_salary - l_oht_max_pp
1340 )';
1341 ELSE
1342 l_oht_text := ' ';
1343 END IF;
1344 ELSE
1345 l_oht_text := ' ';
1346 END IF;
1347
1348 CLOSE csr_pty1;
1349
1350 l_formula_text1 := replace(l_formula_text1,
1351 'REPLACE_OHT_TEXT',
1352 l_oht_text);
1353
1354 update pay_shadow_formulas
1355 set formula_text = l_formula_text1
1356 where formula_id = l_shad_formula_id1
1357 and business_group_id = p_business_group_id;
1358
1359 END IF;
1360
1361 END IF;
1362
1363
1364
1365 -- ---------------------------------------------------------------------
1366 -- |-------------------< Generate Core Objects >------------------------|
1367 -- ---------------------------------------------------------------------
1368 pay_element_template_api.generate_part1
1369 (p_validate => false
1370 ,p_effective_date => p_effective_start_date
1371 ,p_hr_only => false
1372 ,p_hr_to_payroll => false
1373 ,p_template_id => l_template_id);
1374 --
1375 hr_utility.set_location('..After Generating Core objects : Part - 1', 50);
1376 --
1377 pay_element_template_api.generate_part2
1378 (p_validate => false
1379 ,p_effective_date => p_effective_start_date
1380 ,p_template_id => l_template_id);
1381 --
1382 hr_utility.set_location('..After Generating Core objects : Part - 2', 50);
1383
1384 -- Update some of the input values on the main element
1385
1386 Update_Ipval_Defval( p_scheme_prefix||' Pension Deduction'
1387 ,'Pension Type Id'
1388 ,to_char(p_pension_type_id)
1389 ,p_business_group_id);
1390
1391 -- Update some of the input values on the ER element
1392 IF p_employer_component = 'Y' THEN
1393 Update_Ipval_Defval( p_scheme_prefix||' Employer Pension Contribution'
1394 ,'Pension Type Id'
1395 ,to_char(p_pension_type_id)
1396 ,p_business_group_id);
1397 END IF;
1398
1399
1400 OPEN csr_pty1 (c_pension_type_id => p_pension_type_id
1401 ,c_effective_date => p_effective_start_date);
1402 FETCH csr_pty1 INTO r_pty_rec;
1403
1404 IF csr_pty1%notfound THEN
1405 fnd_message.set_name('PQP', 'PQP_230805_INV_PENSIONID');
1406 fnd_message.raise_error;
1407 CLOSE csr_pty1;
1408 ELSE
1409 IF p_deduction_method = 'PE'
1410 AND r_pty_rec.salary_calculation_method = '3' THEN
1411 IF NVL(r_pty_rec.annual_premium_amount,0) > 0 THEN
1412 Update_Ipval_Defval( p_scheme_prefix||' Pension Deduction'
1413 ,'Percentage'
1414 ,fnd_number.number_to_canonical(r_pty_rec.ee_contribution_percent)
1415 ,p_business_group_id);
1416 Update_Ipval_Defval( p_scheme_prefix||' Pension Deduction'
1417 ,'Fixed Premium Amount'
1418 ,fnd_number.number_to_canonical(r_pty_rec.annual_premium_amount)
1419 ,p_business_group_id);
1420 END IF;
1421 ELSIF p_deduction_method = 'PE' THEN
1422 IF NVL(r_pty_rec.ee_contribution_percent,0) > 0 THEN
1423 Update_Ipval_Defval( p_scheme_prefix||' Pension Deduction'
1424 ,'Percentage'
1425 ,fnd_number.number_to_canonical(r_pty_rec.ee_contribution_percent)
1426 ,p_business_group_id);
1427 END IF;
1428
1429 IF (NVL(r_pty_rec.er_contribution_percent,0) > 0
1430 AND p_employer_component = 'Y'
1431 AND l_configuration_information7 = 'Y' ) THEN
1432 Update_Ipval_Defval( p_scheme_prefix||' Employer Pension Contribution'
1433 ,'Percentage'
1434 ,fnd_number.number_to_canonical(r_pty_rec.er_contribution_percent)
1435 ,p_business_group_id);
1436 END IF;
1437 END IF;
1438 CLOSE csr_pty1;
1439 END IF;
1440
1441 -- ------------------------------------------------------------------------
1442 -- Create a row in pay_element_extra_info with all the element information
1443 -- ------------------------------------------------------------------------
1444 l_base_element_type_id := get_object_id ('ELE',
1445 p_scheme_prefix||' Pension Deduction',
1446 p_business_group_id,
1447 l_template_id);
1448
1449 IF p_employer_component = 'Y' THEN
1450
1451 l_er_base_element_type_id := get_object_id ('ELE',
1452 p_scheme_prefix||' Employer Pension Contribution',
1453 p_business_group_id,
1454 l_template_id);
1455
1456 END IF;
1457
1458 pay_element_extra_info_api.create_element_extra_info
1459 (p_element_type_id => l_base_element_type_id
1460 ,p_information_type => 'PQP_NL_PRE_TAX_DEDUCTIONS'
1461 ,p_eei_information_category => 'PQP_NL_PRE_TAX_DEDUCTIONS'
1462 ,p_eei_information1 => p_scheme_description
1463 ,p_eei_information2 => to_char(p_pension_type_id)
1464 ,p_eei_information3 => to_char(p_pension_provider_id)
1465 ,p_eei_information4 => p_pension_category
1466 ,p_eei_information5 => p_deduction_method
1467 ,p_eei_information6 => p_employer_component
1468 ,p_eei_information7 => p_arrearage_flag
1469 ,p_eei_information8 => p_partial_deductions_flag
1470 ,p_eei_information9 => to_char(p_pension_plan_id)
1471 ,p_eei_information10 => p_scheme_prefix
1472 ,p_eei_information11 => null
1473 ,p_eei_information12 => null
1474 ,p_eei_information13 => null
1475 ,p_eei_information14 => null
1476 ,p_eei_information15 => null
1477 ,p_eei_information16 => null
1478 ,p_eei_information17 => null
1479 ,p_eei_information18 => null
1480 ,p_eei_information19 => null
1481 ,p_eei_information20 => null
1482 ,p_element_type_extra_info_id => l_eei_info_id
1483 ,p_object_version_number => l_ovn_eei);
1484
1485 hr_utility.set_location('..After Creating element extra information', 50);
1486
1487 -- ---------------------------------------------------------------------
1488 -- The base element's Pay Value should feed the EE Contribution balance
1489 -- for the pension scheme created.
1490 -- ---------------------------------------------------------------------
1491 for ipv_rec in csr_ipv
1492 (c_ele_typeid => l_base_element_type_id
1493 ,c_effective_date => p_effective_start_date )
1494 loop
1495 open csr_pty (c_pension_type_id => p_pension_type_id
1496 ,c_effective_date => p_effective_start_date);
1497 fetch csr_pty into l_ee_contribution_bal_type_id;
1498 if csr_pty%notfound then
1499 fnd_message.set_name('PQP', 'PQP_230805_INV_PENSIONID');
1500 fnd_message.raise_error;
1501 close csr_pty;
1502 elsif l_ee_contribution_bal_type_id is null then
1503 fnd_message.set_name('PQP', 'PQP_230805_BAL_NOTFOUND');
1504 fnd_message.raise_error;
1505 close csr_pty;
1506 end if;
1507 close csr_pty;
1508 Pay_Balance_Feeds_f_pkg.Insert_Row(
1509 X_Rowid => l_row_id,
1510 X_Balance_Feed_Id => l_Balance_Feed_Id,
1511 X_Effective_Start_Date => p_effective_start_date,
1512 X_Effective_End_Date => hr_api.g_eot,
1513 X_Business_Group_Id => p_business_group_id,
1514 X_Legislation_Code => null,
1515 X_Balance_Type_Id => l_ee_contribution_bal_type_id,
1516 X_Input_Value_Id => ipv_rec.input_value_id,
1517 X_Scale => '1',
1518 X_Legislation_Subgroup => null,
1519 X_Initial_Balance_Feed => false );
1520
1521 l_Balance_Feed_Id := null;
1522 l_row_id := null;
1523
1524 end loop;
1525
1526 hr_utility.set_location('..After creating the balance feed for the base, Pay Value', 50);
1527
1528 -- ---------------------------------------------------------------------
1529 -- The ER base element's Pay Value should feed the ER Contribution balance
1530 -- for the pension scheme created.
1531 -- ---------------------------------------------------------------------
1532 IF p_employer_component = 'Y' THEN
1533 for ipv_rec in csr_ipv
1534 (c_ele_typeid => l_er_base_element_type_id
1535 ,c_effective_date => p_effective_start_date )
1536 loop
1537 open csr_pty2 (c_pension_type_id => p_pension_type_id
1538 ,c_effective_date => p_effective_start_date);
1539 fetch csr_pty2 into l_er_contribution_bal_type_id;
1540 if csr_pty2%notfound then
1541 fnd_message.set_name('PQP', 'PQP_230805_INV_PENSIONID');
1542 fnd_message.raise_error;
1543 close csr_pty2;
1544 elsif l_er_contribution_bal_type_id is null then
1545 fnd_message.set_name('PQP', 'PQP_230805_BAL_NOTFOUND');
1546 fnd_message.raise_error;
1547 close csr_pty2;
1548 end if;
1549 close csr_pty2;
1550 Pay_Balance_Feeds_f_pkg.Insert_Row(
1551 X_Rowid => l_row_id,
1552 X_Balance_Feed_Id => l_Balance_Feed_Id,
1553 X_Effective_Start_Date => p_effective_start_date,
1554 X_Effective_End_Date => hr_api.g_eot,
1555 X_Business_Group_Id => p_business_group_id,
1556 X_Legislation_Code => null,
1557 X_Balance_Type_Id => l_er_contribution_bal_type_id,
1558 X_Input_Value_Id => ipv_rec.input_value_id,
1559 X_Scale => '1',
1560 X_Legislation_Subgroup => null,
1561 X_Initial_Balance_Feed => false );
1562
1563 l_Balance_Feed_Id := null;
1564 l_row_id := null;
1565 end loop;
1566
1567 END IF;
1568
1569 hr_utility.set_location('..After creating the balance feed for the ER base, Pay Value', 51);
1570
1571 -- ---------------------------------------------------------------------
1572 -- Create the Balance feeds for the eligible comp balance
1573 -- ---------------------------------------------------------------------
1574 Create_Pen_Sal_Bal_Feeds ;
1575 -- ---------------------------------------------------------------------
1576 -- Compile the base element's standard formula
1577 -- ---------------------------------------------------------------------
1578
1579
1580 Compile_Formula
1581 (p_element_type_id => l_base_element_type_id
1582 ,p_effective_start_date => p_effective_start_date
1583 ,p_scheme_prefix => l_scheme_prefix
1584 ,p_business_group_id => p_business_group_id
1585 ,p_request_id => l_request_id
1586 );
1587
1588 IF p_employer_component = 'Y' THEN
1589
1590 Compile_Formula
1591 (p_element_type_id => l_er_base_element_type_id
1592 ,p_effective_start_date => p_effective_start_date
1593 ,p_scheme_prefix => l_scheme_prefix
1594 ,p_business_group_id => p_business_group_id
1595 ,p_request_id => l_er_request_id
1596 );
1597
1598 END IF;
1599
1600
1601 hr_utility.set_location('Leaving :'||l_proc_name, 190);
1602
1603 return l_base_element_type_id;
1604
1605 end Create_User_Template;
1606
1607
1608 -- ---------------------------------------------------------------------
1609 -- |--------------------< Create_User_Template_Swi >------------------------|
1610 -- ---------------------------------------------------------------------
1611
1612 function Create_User_Template_Swi
1613 (p_pension_category in Varchar2
1614 ,p_eligibility_model in Varchar2
1615 ,p_pension_provider_id in Number
1616 ,p_pension_type_id in Number
1617 ,p_pension_plan_id in Number
1618 ,p_deduction_method in Varchar2
1619 ,p_arrearage_flag in Varchar2
1620 ,p_partial_deductions_flag in Varchar2
1621 ,p_employer_component in Varchar2
1622 ,p_scheme_prefix in Varchar2
1623 ,p_reporting_name in Varchar2
1624 ,p_scheme_description in Varchar2
1625 ,p_termination_rule in Varchar2
1626 ,p_standard_link in Varchar2
1627 ,p_effective_start_date in Date
1628 ,p_effective_end_date in Date
1629 ,p_security_group_id in Number
1630 ,p_business_group_id in Number
1631 )
1632 return Number is
1633 --
1634 -- Variables for API Boolean parameters
1635 l_validate boolean;
1636 --
1637 -- Variables for IN/OUT parameters
1638 l_element_type_id number;
1639 --
1640 -- Other variables
1641 l_return_status varchar2(1);
1642 l_proc varchar2(72) := 'Create_User_Template_Swi';
1643 Begin
1644 hr_utility.set_location(' Entering:' || l_proc,10);
1645 l_element_type_id := -1;
1646 --
1647 -- Issue a savepoint
1648 --
1649 savepoint Create_User_Template_Swi;
1650 --
1651 -- Initialise Multiple Message Detection
1652 --
1653 hr_multi_message.enable_message_list;
1654 --
1655 -- Remember IN OUT parameter IN values
1656 --
1657 --
1658 -- Convert constant values to their corresponding boolean value
1659 --
1660 l_validate :=
1661 hr_api.constant_to_boolean
1662 (p_constant_value => hr_api.g_false_num);
1663 --
1664 -- Register Surrogate ID or user key values
1665 --
1666 --
1667 -- Call API
1668 --
1669 l_element_type_id := Create_User_Template
1670 (p_pension_category => p_pension_category
1671 ,p_eligibility_model => p_eligibility_model
1672 ,p_pension_provider_id => p_pension_provider_id
1673 ,p_pension_type_id => p_pension_type_id
1674 ,p_pension_plan_id => p_pension_plan_id
1675 ,p_deduction_method => p_deduction_method
1676 ,p_arrearage_flag => p_arrearage_flag
1677 ,p_partial_deductions_flag => p_partial_deductions_flag
1678 ,p_employer_component => p_employer_component
1679 ,p_scheme_prefix => p_scheme_prefix
1680 ,p_reporting_name => p_reporting_name
1681 ,p_scheme_description => p_scheme_description
1682 ,p_termination_rule => p_termination_rule
1683 ,p_standard_link => p_standard_link
1684 ,p_effective_start_date => p_effective_start_date
1685 ,p_effective_end_date => p_effective_end_date
1686 ,p_security_group_id => p_security_group_id
1687 ,p_business_group_id => p_business_group_id
1688 );
1689 --
1690 -- Convert API warning boolean parameter values to specific
1691 -- messages and add them to Multiple Message List
1692 --
1693 --
1694 -- Convert API non-warning boolean parameter values
1695 --
1696 --
1697 -- Derive the API return status value based on whether
1698 -- messages of any type exist in the Multiple Message List.
1699 -- Also disable Multiple Message Detection.
1700 --
1701 l_return_status := hr_multi_message.get_return_status_disable;
1702 hr_utility.set_location(' Leaving:' || l_proc,20);
1703 return l_element_type_id;
1704
1705 --
1706 exception
1707 when hr_multi_message.error_message_exist then
1708 --
1709 -- Catch the Multiple Message List exception which
1710 -- indicates API processing has been aborted because
1711 -- at least one message exists in the list.
1712 --
1713 rollback to Create_User_Template_Swi;
1714 --
1715 -- Reset IN OUT parameters and set OUT parameters
1716 --
1717 return l_element_type_id;
1718 hr_utility.set_location(' Leaving:' || l_proc, 30);
1719
1720 when others then
1721 --
1722 -- When Multiple Message Detection is enabled catch
1723 -- any Application specific or other unexpected
1724 -- exceptions. Adding appropriate details to the
1725 -- Multiple Message List. Otherwise re-raise the
1726 -- error.
1727 --
1728 rollback to Create_User_Template_Swi;
1729 if hr_multi_message.unexpected_error_add(l_proc) then
1730 hr_utility.set_location(' Leaving:' || l_proc,40);
1731 raise;
1732 end if;
1733 --
1734 -- Reset IN OUT and set OUT parameters
1735 --
1736 l_return_status := hr_multi_message.get_return_status_disable;
1737 return l_element_type_id;
1738 hr_utility.set_location(' Leaving:' || l_proc,50);
1739
1740
1741 END create_user_template_swi;
1742
1743
1744
1745 -- ---------------------------------------------------------------------
1746 -- |--------------------< Delete_User_Template >------------------------|
1747 -- ---------------------------------------------------------------------
1748 procedure Delete_User_Template
1749 (p_pension_plan_id in Number
1750 ,p_business_group_id in Number
1751 ,p_pension_dedn_ele_name in Varchar2
1752 ,p_pension_dedn_ele_type_id in Number
1753 ,p_security_group_id in Number
1754 ,p_effective_date in Date
1755 ) is
1756 --
1757 cursor c1 is
1758 select template_id
1759 from pay_element_templates
1760 where base_name||' Pension Deduction' = p_pension_dedn_ele_name
1761 and business_group_id = p_business_group_id
1762 and template_type = 'U';
1763
1764 CURSOR csr_ele_extra_info IS
1765 SELECT element_type_extra_info_id
1766 ,object_version_number
1767 FROM pay_element_type_extra_info
1768 WHERE eei_information_category = 'PQP_NL_PRE_TAX_DEDUCTIONS'
1769 AND element_type_id = p_pension_dedn_ele_type_id;
1770
1771 l_template_id Number(9);
1772 l_proc Varchar2(60) := g_proc_name||'Delete_User_Template';
1773
1774 begin
1775 hr_utility.set_location('Entering :'||l_proc, 10);
1776 --
1777 for c1_rec in c1 loop
1778 l_template_id := c1_rec.template_id;
1779 end loop;
1780 --
1781 pay_element_template_api.delete_user_structure
1782 (p_validate => false
1783 ,p_drop_formula_packages => true
1784 ,p_template_id => l_template_id);
1785 --
1786
1787 --
1788 -- Delete the rows in pay_element_type_extra_info
1789 --
1790
1791 FOR temp_rec IN csr_ele_extra_info
1792 LOOP
1793 pay_element_extra_info_api.delete_element_extra_info
1794 (p_element_type_extra_info_id => temp_rec.element_type_extra_info_id
1795 ,p_object_version_number => temp_rec.object_version_number);
1796 END LOOP;
1797
1798 hr_utility.set_location('Leaving :'||l_proc, 50);
1799
1800 end Delete_User_Template;
1801 --
1802
1803 -- ---------------------------------------------------------------------
1804 -- |------------------< Delete_User_Template_Swi >----------------------|
1805 -- ---------------------------------------------------------------------
1806
1807 procedure Delete_User_Template_Swi
1808 (p_pension_plan_id in Number
1809 ,p_business_group_id in Number
1810 ,p_pension_dedn_ele_name in Varchar2
1811 ,p_pension_dedn_ele_type_id in Number
1812 ,p_security_group_id in Number
1813 ,p_effective_date in Date
1814 ) is
1815
1816 --
1817 -- Variables for API Boolean parameters
1818 l_validate boolean;
1819 --
1820 -- Variables for IN/OUT parameters
1821 --
1822 -- Other variables
1823 l_return_status varchar2(1);
1824 l_proc varchar2(72) := 'Delete_User_Template_Swi';
1825 Begin
1826 hr_utility.set_location(' Entering:' || l_proc,10);
1827 --
1828 -- Issue a savepoint
1829 --
1830 savepoint Delete_User_Template_Swi;
1831 --
1832 -- Initialise Multiple Message Detection
1833 --
1834 hr_multi_message.enable_message_list;
1835 --
1836 -- Remember IN OUT parameter IN values
1837 --
1838 --
1839 -- Convert constant values to their corresponding boolean value
1840 --
1841 l_validate :=
1842 hr_api.constant_to_boolean
1843 (p_constant_value => hr_api.g_false_num);
1844 --
1845 -- Register Surrogate ID or user key values
1846 --
1847 --
1848 -- Call API
1849 --
1850 Delete_User_Template
1851 (p_pension_plan_id => p_pension_plan_id
1852 ,p_business_group_id => p_business_group_id
1853 ,p_pension_dedn_ele_name => p_pension_dedn_ele_name
1854 ,p_pension_dedn_ele_type_id => p_pension_dedn_ele_type_id
1855 ,p_security_group_id => p_security_group_id
1856 ,p_effective_date => p_effective_date
1857 );
1858 --
1859 -- Convert API warning boolean parameter values to specific
1860 -- messages and add them to Multiple Message List
1861 --
1862 --
1863 -- Convert API non-warning boolean parameter values
1864 --
1865 --
1866 -- Derive the API return status value based on whether
1867 -- messages of any type exist in the Multiple Message List.
1868 -- Also disable Multiple Message Detection.
1869 --
1870 l_return_status := hr_multi_message.get_return_status_disable;
1871 hr_utility.set_location(' Leaving:' || l_proc,20);
1872
1873 --
1874 exception
1875 when hr_multi_message.error_message_exist then
1876 --
1877 -- Catch the Multiple Message List exception which
1878 -- indicates API processing has been aborted because
1879 -- at least one message exists in the list.
1880 --
1881 rollback to Delete_User_Template_Swi;
1882 --
1883 -- Reset IN OUT parameters and set OUT parameters
1884 --
1885 hr_utility.set_location(' Leaving:' || l_proc, 30);
1886
1887 when others then
1888 --
1889 -- When Multiple Message Detection is enabled catch
1890 -- any Application specific or other unexpected
1891 -- exceptions. Adding appropriate details to the
1892 -- Multiple Message List. Otherwise re-raise the
1893 -- error.
1894 --
1895 rollback to Delete_User_Template_Swi;
1896 if hr_multi_message.unexpected_error_add(l_proc) then
1897 hr_utility.set_location(' Leaving:' || l_proc,40);
1898 raise;
1899 end if;
1900 --
1901 -- Reset IN OUT and set OUT parameters
1902 --
1903 l_return_status := hr_multi_message.get_return_status_disable;
1904 hr_utility.set_location(' Leaving:' || l_proc,50);
1905
1906 END delete_user_template_swi;
1907
1908 --
1909
1910 end pqp_nl_pension_template;
1911