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