[Home] [Help]
PACKAGE BODY: APPS.PAY_US_USER_INIT_DEDN
Source
1 Package Body PAY_US_User_Init_Dedn As
2 /* $Header: pyusdtmp.pkb 120.11.12000000.2 2007/07/09 06:53:47 sudedas noship $ */
3
4 g_proc constant varchar2(150) := 'PAY_US_User_Init_Dedn';
5
6 -- =============================================================================
7 -- create_user_init_template:
8 -- =============================================================================
9 function create_user_init_template
10 (p_ele_name in varchar2
11 ,p_ele_reporting_name in varchar2
12 ,p_ele_description in varchar2
13 ,p_ele_classification in varchar2
14 ,p_ben_class_id in number
15 ,p_ele_category in varchar2
16 ,p_ele_processing_type in varchar2
17 ,p_ele_priority in number
18 ,p_ele_standard_link in varchar2
19 ,p_ele_proc_runtype in varchar2
20 ,p_ele_calc_rule in varchar2
21 ,p_ele_start_rule in varchar2
22 ,p_ele_stop_rule in varchar2
23 ,p_ele_partial_deduction in varchar2
24 ,p_ele_arrearage in varchar2
25 ,p_ele_eff_start_date in date
26 ,p_ele_eff_end_date in date
27 ,p_employer_match in varchar2
28 ,p_after_tax_component in varchar2
29 ,p_ele_srs_plan_type in varchar2
30 ,p_ele_srs_buy_back in varchar2
31 ,p_roth_contribution in varchar2
32 ,p_userra_contribution in varchar2
33 ,p_bg_id in number
34 ,p_catchup_processing in varchar2
35 ,p_termination_rule in varchar2
36 )
37 return number is
38 --
39 -- =============================================================================
40 -- The input values are explained below : V-varchar2, D-Date, N-number
41 -- =============================================================================
42 -- Input-Name Type Valid Values/Explaination
43 -- ====================== ===== ================================================
44 -- p_ele_name (V) - User i/p Element name
45 -- p_ele_reporting_name (V) - User i/p reporting name
46 -- p_ele_description (V) - User i/p Description
47 -- p_ele_classification (V) - 'Pre-Tax Deductions'
48 -- p_ben_class_id (N) - '' - not used
49 -- p_ele_category (V) - 'E'/'G' (403B/457)
50 -- p_ele_processing_type (V) - 'R'/'N' (Recurring/Non-recurring)
51 -- p_ele_priority (N) - User i/p priority
52 -- p_ele_standard_link (V) - 'Y'/'N' (default N)
53 -- p_ele_proc_runtype (V) - 'REG'/'ALL'
54 -- p_ele_calc_rule (V) - 'FA'/'PE' (Flat amount/Percentage)
55 -- p_ele_start_rule (V) - 'ET'(Earnings threshold),'CHAINED',''
56 -- p_ele_stop_rule (V) - 'OE'(On Entry), 'Total Reached'
57 -- p_ele_partial_deduction(V) - 'Y'/'N'
58 -- p_ele_arrearage (V) - 'Y'/'N'
59 -- p_ele_eff_start_date (D) - Trunc(start date)
60 -- p_ele_eff_end_date (D) - Trunc(end date)
61 -- p_employer_match (D) - 'Y'/'N'
62 -- p_after_tax_component (V) - 'Y'/'N'
63 -- p_ele_srs_plan_type (V) - 'N' (NONE), 'C' (DCP), 'B' (DBP)
64 -- p_ele_srs_buy_back (V) - 'Y'/'N'
65 -- p_bg_id (N) - Business group id
66 -- p_roth_contribution (V) - Y= Creates the Roth AT element
67 -- p_userra_contribution (V) - Y= Creates the USERRA elements
68 -- =============================================================================
69 --
70 l_arrearage_create varchar2(1);
71 l_aftertax_nonrecurring_rule varchar2(1);
72 l_aftertax_si_rule varchar2(1);
73 l_at_er_exclusion_rule varchar2(1);
74 l_cu_si_exclusion_rule varchar2(1);
75 l_accr_bal_id number(9);
76 l_arr_bal_id number(9);
77 l_addl_bal_id number(9);
78 l_at_element_type_id number(9);
79 l_at_sf_element_type_id number(9);
80 l_at_si_element_type_id number(9);
81 l_at_base_element_type_id number(9);
82 l_at_pri_bal_id number(9);
83 l_at_arr_bal_id number(9);
84 l_at_not_taken_bal_id number(9);
85 l_at_addl_bal_id number(9);
86 l_at_repl_bal_id number(9);
87 l_at_accr_bal_id number(9);
88 l_at_si_core_element_type_id number(9);
89 l_at_sf_core_element_type_id number(9);
90 l_base_element_type_id number(9);
91 l_cal_core_element_type_id number(9);
92 l_element_type_id number(9);
93 l_ele_obj_ver_number number(9);
94 l_fee_bal_id number(9);
95 l_not_taken_bal_id number(9);
96 l_object_version_number number(9);
97 l_pri_bal_id number(9);
98 l_repl_bal_id number(9);
99 l_source_template_id number(9);
100 l_sf_element_type_id number(9);
101 l_sf_core_element_type_id number(9);
102 l_sf_ele_obj_ver_number number(9);
103 l_si_element_type_id number(9);
104 l_si_core_element_type_id number(9);
105 l_si_ele_obj_ver_number number(9);
106 l_template_id number(9);
107 l_total_owed_create varchar2(1);
108 l_skip_formula varchar2(30);
109 l_er_element_type_id number(9);
110 l_er_bal_id number(9);
111 l_at_er_element_type_id number(9);
112 l_at_er_bal_id number(9);
113 l_etei_id number(9);
114 l_etei_ovn number(9);
115 l_srs_etei_id number(9);
116 l_srs_etei_ovn number(9);
117 l_cu_element_type_id number(9);
118 l_cu_sf_element_type_id number(9);
119 l_cu_si_element_type_id number(9);
120 l_cur_temp number(9);
121 l_cu_pri_bal_id number(9);
122 l_cu_addl_bal_id number(9);
123 l_cu_repl_bal_id number(9);
124 l_cu_ele_obj_ver_number number(9);
125 l_dummy number(9);
126 l_glb_rowid rowid;
127 l_glb_rowid1 rowid;
128 l_glb_id number(9);
129 l_glb_id1 number(9);
130 l_glb_name1 varchar2(50);
131 l_glb_name varchar2(50);
132 l_cu_accrued_bal_id number(9);
133 l_cu_nottaken_bal_id number(9);
134 l_cu_arrears_bal_id number(9);
135 l_bb_nottaken_bal_id number(9);
136 l_bb_arrears_bal_id number(9);
137 l_jd_core_element_type_id number;
138 l_jd_ele_obj_ver_number number;
139 l_cu_proc_inp_value_id number;
140 l_vol_ded_bal_id number(9);
141 --l_ver_core_element_type_id NUMBER(9);
142 l_dbp_exclusion_rule varchar2(1);
143 l_dcp_exclusion_rule varchar2(1);
144 l_bb_element_type_id number(9);
145 l_bb_sf_element_type_id number(9);
146 l_bb_si_element_type_id number(9);
147 l_bb_pri_bal_id number(9);
148 l_bb_addl_bal_id number(9);
149 l_bb_repl_bal_id number(9);
150 l_bb_ele_obj_ver_number number(9);
151 l_bb_accrued_bal_id number(9);
152 l_er_contr_element_type_id number(9);
153 l_er_contr_pri_bal_id number(9);
154 l_er_contr_ele_obj_ver_number number(9);
155 l_ele_category varchar2(20);
156 -- Added by sdahiya for involuntary deductions
157 l_ca_ele_obj_ver_number number(9);
158 l_pri_ele_obj_ver_number number(9);
159 l_fee_ele_obj_ver_number number(9);
160 l_ver_ele_obj_ver_number number(9);
161 l_ca_element_type_id number(9);
162 l_pri_element_type_id number(9);
163 l_fee_element_type_id number(9);
164 l_ver_element_type_id number(9);
165 l_element_information_category varchar2(30);
166 l_fees_core_element_type_id number(9);
167 l_inv_ded varchar2(22);
168 l_accr_fees_bal_id number(9);
169 l_srs_ercontr_type_id pay_element_types_f.element_type_id%type;
170 l_srs_ercontr_obj_ver_number pay_element_types_f.object_version_number%type;
171 l_srs_ercontr_rep_name pay_element_types_f.reporting_name%type;
172 l_srs_ercontr_desc_name pay_element_types_f.description%type;
173 -- USERRA Changes
174 l_pt_userra_si_rule varchar2(2);
175 l_pt_userra_rule varchar2(2);
176 l_pt_userra_er_rule varchar2(2);
177 l_atr_userra_si_rule varchar2(2);
178 l_atr_userra_rule varchar2(2);
179 l_atr_userra_er_rule varchar2(2);
180 -- Roth Changes
181 l_at_roth_si_rule varchar2(2);
182 l_at_roth_er_rule varchar2(2);
183 l_roth_ele_type_id number(15);
184 l_roth_si_ele_type_id number(15);
185 l_roth_sf_ele_type_id number(15);
186 l_roth_eligiCmp_ipv_id number(15);
187 l_atr_pri_bal_id number(15);
188 l_atr_addl_bal_id number(15);
189 l_atr_repl_bal_id number(15);
190 l_atr_element_type_id number(15);
191 l_atr_er_element_type_id number(15);
192 l_atr_si_element_type_id number(15);
193 l_atr_sf_element_type_id number(15);
194 l_atr_er_bal_id number(15);
195 l_atr_accr_bal_id number(15);
196 l_atr_not_taken_bal_id number(15);
197 l_atr_arr_bal_id number(15);
198 l_roth_er_ele_type_id number(15);
199
200 -- Bug# 4676867: Impact of Roth on Involuntary Deductions
201 l_ele_information1 varchar2(100) ;
202 l_relative_processing_priority number(15) ;
203
204 l_ben_class_name ben_benefit_classifications.benefit_classification_name%type;
205 type r_temp_var is record
206 (sub_name pay_element_types_f.element_name%type);
207 type t_temp_var is table of r_temp_var
208 index by binary_integer;
209 l_temp_var t_temp_var;
210 --
211 l_proc varchar2(80);
212 --
213 -- Get the element type id for a given template id
214 --
215 cursor c1 (c_ele_name varchar2) is
216 select element_type_id, object_version_number
217 from pay_shadow_element_types
218 where template_id = l_template_id
219 and element_name = c_ele_name;
220 --
221 -- Cursor to fetch the core element id
222 --
223 cursor c5 (c_element_name in varchar2) is
224 select ptco.core_object_id
225 from pay_shadow_element_types psbt,
226 pay_template_core_objects ptco
227 where psbt.template_id = l_template_id
228 and psbt.element_name = c_element_name
229 and ptco.template_id = psbt.template_id
230 and ptco.shadow_object_id = psbt.element_type_id
231 and ptco.core_object_type = 'ET';
232 --
233 -- Cursor to get Input Value ID's for the AT Element
234 --
235 cursor c_at_ivn(p_element_type_id in number) is
236 select input_value_id, name
237 from pay_input_values_f
238 where element_type_id = p_element_type_id
239 and name in ('Take Overlimit AT',
240 'AT Processing Order')
241 and business_group_id = p_bg_id ;
242 --
243 -- Cursor to get Input Value ID's for the Roth Element
244 --
245 cursor c_atr_ivn(p_element_type_id in number) is
246 select input_value_id, name
247 from pay_input_values_f
248 where element_type_id = p_element_type_id
249 and name in ('Take Overlimit Roth',
250 'Roth Processing Order')
251 and business_group_id = p_bg_id ;
252
253 --
254 -- Cursor to check if EMPLOYER_MATCH_PCT global value was created.
255 --
256 cursor c_global is
257 select 1
258 from ff_globals_f
259 where business_group_id = p_bg_id
260 and global_name = 'EMPLOYER_MATCH_PCT';
261 --
262 -- Cursor to check if EMPLOYER_MATCH_LIMIT global value was created.
263 --
264 cursor c_global1 is
265 select 1
266 from ff_globals_f
267 where business_group_id = p_bg_id
268 and global_name = 'EMPLOYER_MATCH_LIMIT';
269 --
270 -- Get the benefit classification name
271 --
272 cursor c_ben_class (l_ben_class_id in varchar2) is
273 select benefit_classification_name
274 from ben_benefit_classifications
275 where benefit_classification_id = l_ben_class_id
276 and legislation_code = 'US';
277 --
278 -- Pre Tax Arrearage
279 --
280 cursor c_iter_formula is
281 select formula_id
282 from ff_formulas_f
283 where formula_name = 'US_ITERATIVE_PRETAX'
284 and legislation_code = 'US';
285 --
286 -- Cursor to make Pay Value non user enterable
287 --
288 cursor csr_ele ( c_effective_date in date
289 ,c_ele_prefix in varchar2 ) is
290 select pet.element_name
291 ,pet.element_type_id
292 ,piv.name
293 ,piv.input_value_id
294 ,piv.mandatory_flag
295 from pay_element_types_f pet
296 ,pay_input_values_f piv
297 where (pet.element_name like c_ele_prefix||'% Special Inputs'
298 or
299 pet.element_name like c_ele_prefix||'% SI' )
300 and piv.element_type_id = pet.element_type_id
301 and piv.name ='Pay Value'
302 and pet.business_group_id = p_bg_id
303 and piv.business_group_id = p_bg_id
304 and c_effective_date between pet.effective_start_date
305 and pet.effective_end_date
306 and c_effective_date between piv.effective_start_date
307 and piv.effective_end_date;
308 --
309 -- For the balance architecture changes
310 -- as per US Payroll Team request - 02-APR-03
311 --
312 cursor get_asg_gre_run_dim_id is
313 select balance_dimension_id
314 from pay_balance_dimensions
315 where dimension_name = 'Assignment within Government Reporting Entity Run'
316 and legislation_code = 'US';
317 --
318 l_asg_gre_run_dim_id pay_balance_dimensions.balance_dimension_id%type;
319 l_iter_formula_id ff_formulas_f.formula_id%type;
320 l_iter_priority pay_element_types_f.iterative_priority%type;
321 l_priority_inc number;
322 l_ele_template_priority number;
323 l_template_priority number;
324 --
325 -- =============================================================================
326 -- get_jd_bal_id:
327 -- =============================================================================
328 function get_jd_bal_id
329 (p_jd_bal_name in varchar2
330 ) return number is
331
332 l_bal_id number := null ;
333 l_proc varchar2(200);
334 cursor c_bal is
335 select balance_type_id
336 from pay_balance_types
337 where balance_name = p_jd_bal_name
338 and legislation_code = 'US';
339 begin
340 l_proc := g_proc||'.get_template_id';
341 hr_utility.set_location('Entering: '||l_proc, 5);
342 --
343 for temp_rec in c_bal loop
344 l_bal_id := temp_rec.balance_type_id;
345 end loop;
346 hr_utility.set_location('Leaving: '||l_proc, 10);
347 return l_bal_id;
348 --
349 end;
350 -- =============================================================================
351 -- get_cu_input_value_id:
352 -- =============================================================================
353 function get_cu_input_value_id
354 (p_element_type_id in number
355 ) return number is
356
357 l_input_value_id number := null ;
358 l_proc varchar2(200);
359
360 cursor c_bal is
361 select input_value_id
362 from pay_input_values_f
363 where element_type_id = p_element_type_id
364 and name = 'Catchup Processing'
365 and business_group_id = p_bg_id ;
366
367 begin
368 l_proc := g_proc||'.get_template_id';
369 hr_utility.set_location('Entering: '||l_proc, 5);
370
371 for temp_rec in c_bal loop
372 l_input_value_id := temp_rec.input_value_id;
373 end loop;
374 hr_utility.set_location('Leaving: '||l_proc, 10);
375 return l_input_value_id;
376
377 end;
378 -- =============================================================================
379 -- get_template_id:
380 -- =============================================================================
381 function get_template_id
382 (p_legislation_code in varchar2,
383 p_ele_category in varchar2,
384 p_ele_srs_plan_type in varchar2
385 ) return number is
386
387 l_template_id number(9);
388 l_template_name varchar2(80);
389 l_proc varchar2(60);
390 l_ele_category varchar2(30);
391
392 cursor c4 is
393 select template_id,base_processing_priority
394 from pay_element_templates
395 where template_name = l_template_name
396 and legislation_code = p_legislation_code
397 and template_type = 'T'
398 and business_group_id is null;
399 --
400 begin
401 --
402 l_proc := g_proc||'.get_template_id';
403 hr_utility.set_location('Entering: '||l_proc, 10);
404
405 l_ele_category := p_ele_category;
406 if p_ele_srs_plan_type = 'C' then
407 l_ele_category := 'DCP';
408 elsif p_ele_srs_plan_type = 'B' then
409 l_ele_category := 'DBP';
410 end if;
411 --
412 if l_ele_category = 'E' then
413 l_template_name := '403b Deduction 2002';
414 l_iter_priority := 60;
415 -- Modified for Garnishment rewrite
416 elsif l_ele_category = 'G' and p_ele_classification <> l_inv_ded then
417 l_template_name := '457 Deduction 2002';
418 l_iter_priority := 50;
419
420 elsif l_ele_category = 'D' then
421 l_template_name := '401K Deduction 2002';
422 l_iter_priority := 70;
423
424 elsif l_ele_category = 'DBP' then
425 l_template_name := 'State Retirement Plan';
426 l_iter_priority := 30;
427
428 elsif l_ele_category = 'DCP' then
429 l_template_name := 'State Retirement Plan';
430 l_iter_priority := 20;
431
432 elsif l_ele_category = 'S' then /* Dependent Care 125 */
433 l_template_name := 'Dependent Care'; /*Bug:3452933 */
434 l_iter_priority := 40;
435
436 elsif l_ele_category = 'H' then /* Health Care 125 */
437 l_template_name := '125 Deduction';
438 l_iter_priority := 80;
439
440 elsif l_ele_category in ('AY','CS','SS') then
441 l_template_name := 'Alimony';
442 l_iter_priority := 10;
443
444 elsif l_ele_category = 'BO' then
445 l_template_name := 'Bankruptcy';
446 l_iter_priority := 10;
447
448 elsif l_ele_category in ('CD','G') then
449 l_template_name := 'Credit Debt';
450 l_iter_priority := 10;
451
452 elsif l_ele_category = 'EL' then
453 l_template_name := 'Educational Loan';
454 l_iter_priority := 10;
455
456 elsif l_ele_category = 'ER' then
457 l_template_name := 'Employee Requested';
458 l_iter_priority := 10;
459
460 elsif l_ele_category = 'TL' then
461 l_template_name := 'Tax Levy';
462 l_iter_priority := 10;
463
464 elsif l_ele_category = 'DCIA' then
465 l_template_name := 'DCIA';
466 l_iter_priority := 10;
467
468 else
469 -- added by kumar thirmiya
470 -- only 403b,457,401K and all the userdefined category will be passed this procedure so
471 -- if it is other than the above three use the Other Pretax Deduction template
472 l_template_name := 'Other Pretax Deduction';
473 l_iter_priority := 10;
474
475 end if;
476 --
477 hr_utility.set_location(l_proc, 30);
478 --
479 for c4_rec in c4 loop
480 l_template_id := c4_rec.template_id;
481 l_ele_template_priority := c4_rec.base_processing_priority;
482 end loop;
483 --
484 if l_ele_category = 'S' then
485
486 l_ele_template_priority := l_ele_template_priority + 175;
487
488 elsif l_ele_category = 'DCP' then
489
490 l_ele_template_priority := l_ele_template_priority + 50;
491
492 end if;
493
494 hr_utility.set_location('Leaving: '||l_proc, 50);
495 --
496 return l_template_id;
497 --
498 end get_template_id;
499 --
500 -- =============================================================================
501 -- create_eligible_comp_bal_feeds:
502 -- =============================================================================
503 procedure create_eligible_comp_bal_feeds is
504
505 l_row_id rowid;
506 l_balance_feed_id pay_balance_feeds_f.balance_feed_id%type;
507 l_proc varchar2(160);
508 --
509 -- added hint no_merge(pbf) for bug 5187416
510 cursor c1_get_reg_earn_feeds is
511 -- Commenting this section for Performance Issue
512
513 --select /*+ no_merge(pbf) */ bc.classification_id
514 /* ,pbf.input_value_id
515 ,pbf.scale
516 ,pbf.element_type_id
517 from pay_balance_feeds_v pbf,
518 pay_balance_classifications bc
519 where nvl(pbf.balance_initialization_flag,'N') = 'N'
520 and nvl(pbf.business_group_id,p_bg_id) = p_bg_id
521 and nvl(pbf.legislation_code, 'US') = 'US'
522 and pbf.balance_name = 'Regular Earnings'
523 and bc.balance_type_id = pbf.balance_type_id
524 order by pbf.element_name;
525 */
526 -- Changed the above Cusror, replacing View with Base Tables
527 -- Removed 'Order By' Clause (Bug# 5724902)
528
529 select /*+ no_merge(BF) */
530 bc.classification_id
531 ,bf.input_value_id
532 ,bf.scale
533 ,et.element_type_id
534 from pay_balance_classifications bc
535 , PAY_BALANCE_FEEDS_F BF
536 , PAY_BALANCE_TYPES BT
537 , PAY_INPUT_VALUES_F IV
538 , PAY_ELEMENT_TYPES_F ET
539 , PAY_ELEMENT_CLASSIFICATIONS EC
540 , HR_LOOKUPS HL
541 , HR_LOOKUPS HL2
542 , FND_SESSIONS SES
543 WHERE BT.BALANCE_TYPE_ID = BF.BALANCE_TYPE_ID
544 AND IV.INPUT_VALUE_ID = BF.INPUT_VALUE_ID
545 AND ET.ELEMENT_TYPE_ID = IV.ELEMENT_TYPE_ID
546 AND EC.CLASSIFICATION_ID = ET.CLASSIFICATION_ID
547 AND HL.LOOKUP_TYPE = 'ADD_SUBTRACT'
548 AND HL.LOOKUP_CODE = BF.SCALE
549 AND HL2.LOOKUP_TYPE = 'UNITS'
550 AND HL2.LOOKUP_CODE = IV.UOM
551 AND SES.SESSION_ID = USERENV('SESSIONID')
552 AND SES.EFFECTIVE_DATE BETWEEN BF.EFFECTIVE_START_DATE
553 AND BF.EFFECTIVE_END_DATE
554 AND SES.EFFECTIVE_DATE BETWEEN IV.EFFECTIVE_START_DATE
555 AND IV.EFFECTIVE_END_DATE
556 AND SES.EFFECTIVE_DATE BETWEEN ET.EFFECTIVE_START_DATE
557 AND ET.EFFECTIVE_END_DATE
558 AND BC.BALANCE_TYPE_ID = BF.BALANCE_TYPE_ID
559 AND nvl(EC.BALANCE_INITIALIZATION_FLAG , 'N') = 'N'
560 AND nvl(BF.BUSINESS_GROUP_ID, p_bg_id) = p_bg_id
561 AND nvl(BF.LEGISLATION_CODE, 'US') = 'US'
562 AND BT.BALANCE_NAME = 'Regular Earnings' ;
563
564 -- To get the balance type id
565 cursor c2_balance_type is
566 select balance_type_id
567 from pay_balance_types
568 where business_group_id = p_bg_id
569 and balance_name in (p_ele_name||' Eligible Comp',
570 p_ele_name||' Roth Eligible Comp',
571 p_ele_name||' AT Eligible Comp');
572 begin
573 l_proc := g_proc||'.create_eligible_comp_bal_feeds';
574 hr_utility.set_location('Entering: '||l_proc, 5);
575 for c1_rec in c1_get_reg_earn_feeds loop
576 for c2_rec in c2_balance_type loop
577 pay_balance_feeds_f_pkg.insert_row
578 (x_rowid => l_row_id,
579 x_balance_feed_id => l_balance_feed_id,
580 x_effective_start_date => p_ele_eff_start_date,
581 x_effective_end_date => hr_api.g_eot,
582 x_business_group_id => p_bg_id,
583 x_legislation_code => null,
584 x_balance_type_id => c2_rec.balance_type_id,
585 x_input_value_id => c1_rec.input_value_id,
586 x_scale => c1_rec.scale,
587 x_legislation_subgroup => null,
588 x_initial_balance_feed => false
589 );
590 l_balance_feed_id := null;
591 l_row_id := null;
592 end loop;
593 end loop;
594 hr_utility.set_location('Leaving: '||l_proc, 10);
595 end create_eligible_comp_bal_feeds;
596 --
597 -- =============================================================================
598 -- get_object_id:
599 -- =============================================================================
600 function get_object_id
601 (p_object_type in varchar2,
602 p_object_name in varchar2
603 ) return number is
604 --
605 l_object_id number := null;
606 l_proc varchar2(200);
607 --
608 cursor c2 (c_object_name varchar2) is
609 select element_type_id
610 from pay_element_types_f
611 where element_name = c_object_name
612 and business_group_id = p_bg_id;
613 --
614 cursor c3 (c_object_name in varchar2) is
615 select ptco.core_object_id
616 from pay_shadow_balance_types psbt,
617 pay_template_core_objects ptco
618 where psbt.template_id = l_template_id
619 and psbt.balance_name = c_object_name
620 and ptco.template_id = psbt.template_id
621 and ptco.shadow_object_id = psbt.balance_type_id;
622 --
623 begin
624 l_proc := g_proc||'.get_object_id';
625 hr_utility.set_location('Entering: '||l_proc, 10);
626 --
627 if p_object_type = 'ELE' then
628 for c2_rec in c2 (p_object_name) loop
629 l_object_id := c2_rec.element_type_id; -- element id
630 end loop;
631 elsif p_object_type = 'BAL' then
632 for c3_rec in c3 (p_object_name) loop
633 l_object_id := c3_rec.core_object_id; -- balance id
634 end loop;
635 end if;
636 --
637 hr_utility.set_location('Leaving: '||l_proc, 50);
638 --
639 return l_object_id;
640 --
641 end get_object_id;
642
643 -- =============================================================================
644 -- get_elgicomp_ipv: to get the Eligible comp option Input value Id.
645 -- =============================================================================
646 function get_elgicomp_ipv
647 (p_element_type_id in number
648 ) return number is
649
650 l_input_value_id number := null ;
651 l_proc varchar2(200);
652
653 cursor c_bal is
654 select input_value_id
655 from pay_input_values_f
656 where element_type_id = p_element_type_id
657 and name = 'Eligible Comp Option'
658 and business_group_id = p_bg_id ;
659
660 begin
661
662 l_proc := g_proc||'.get_elgicomp_ipv';
663 hr_utility.set_location('Entering: '||l_proc, 5);
664
665 for temp_rec in c_bal loop
666 l_input_value_id := temp_rec.input_value_id;
667 end loop;
668 hr_utility.set_location('Leaving: '||l_proc, 10);
669 return l_input_value_id;
670
671 end get_elgicomp_ipv;
672 -- =============================================================================
673 -- get_element_and_balance_ids:
674 -- =============================================================================
675 procedure get_element_and_bal_ids is
676 --
677 l_proc varchar2(200);
678 l_eligicmp_ipv_id number(9);
679 --
680 begin
681 l_proc := g_proc||'.get_element_and_bal_ids';
682 hr_utility.set_location('Entering: '||l_proc, 10);
683 --
684 l_base_element_type_id := get_object_id('ELE',p_ele_name);
685 -- Added to check the ER match element is exist for base element.
686 -- If not, then set the default_value to S_EE and lookup_type
687 -- to US_ELIGIBLE_COMP_OPTIONS_EE
688 if nvl(p_employer_match ,'N') = 'N' then
689 --Added to get input value id for eligible Compensation option
690 l_eligiCmp_ipv_id := get_elgiComp_ipv (l_base_element_type_id);
691 update pay_input_values_f
692 set default_value = 'S_EE'
693 ,lookup_type = 'US_ELIGIBLE_COMP_OPTIONS_EE'
694 where input_value_id = l_eligicmp_ipv_id;
695 end if;
696 -- Code to get ids for catchup objects.
697 if nvl(p_catchup_processing ,'NONE') <> 'NONE' then
698 l_cu_element_type_id := get_object_id('ELE',p_ele_name||' Catchup');
699 l_cu_si_element_type_id := get_object_id('ELE',p_ele_name||' Catchup SI');
700 l_cu_sf_element_type_id := get_object_id('ELE',p_ele_name||' Catchup SF');
701 l_cu_pri_bal_id := get_object_id('BAL', p_ele_name||' Catchup');
702 l_cu_addl_bal_id := get_object_id('BAL', p_ele_name||' Catchup Additional Amt');
703 l_cu_repl_bal_id := get_object_id('BAL', p_ele_name||' Catchup Replacement Amt');
704 l_cu_accrued_bal_id := get_object_id('BAL', p_ele_name||' Catchup Accrued');
705 l_cu_nottaken_bal_id := get_object_id('BAL', p_ele_name||' Catchup Not Taken');
706 -- Attach the arrears balance only if Arrearage is selected in the Ded. form.
707 if p_ele_arrearage = 'Y' then
708 l_cu_arrears_bal_id := get_object_id('BAL', p_ele_name||' Catchup Arrears');
709 end if;
710 end if;
711 --
712 -- Code to get ids for SRS objects.
713 --
714 if (p_ele_category in ('DCP', 'DBP')) or
715 (NVL(p_ele_srs_plan_type, 'N') <> 'N') then
716 -- Means that the Category is SRS type.
717 -- Check for the Buy Back Flag and get Id's for the Buy back element
718 if p_ele_srs_buy_back = 'Y' then
719 l_bb_element_type_id := get_object_id('ELE',p_ele_name||' Buy Back');
720 l_bb_si_element_type_id := get_object_id('ELE',p_ele_name||' Buy Back SI');
721 l_bb_sf_element_type_id := get_object_id('ELE',p_ele_name||' Buy Back SF');
722 l_bb_pri_bal_id := get_object_id('BAL',p_ele_name||' Buy Back');
723 l_bb_addl_bal_id := get_object_id('BAL',p_ele_name||' Buy Back Additional Amt');
724 l_bb_repl_bal_id := get_object_id('BAL',p_ele_name||' Buy Back Replacement Amt');
725 l_bb_accrued_bal_id := get_object_id('BAL',p_ele_name||' Accrued Buy Back');
726 l_bb_nottaken_bal_id := get_object_id('BAL',p_ele_name||' Buy Back Not Taken');
727 -- Attach the arrears balance only if Arrearage is selected in the Ded. form.
728 if p_ele_arrearage = 'Y' then
729 l_bb_arrears_bal_id := get_object_id('BAL', p_ele_name||' Buy Back Arrears');
730 end if;
731 end if;
732 -- Get object Id's for ER contribution Element
733 l_er_contr_element_type_id := get_object_id('ELE',p_ele_name||' ER Contribution');
734 l_er_contr_pri_bal_id := get_object_id('BAL',p_ele_name||' ER Contribution');
735 end if;
736 l_si_core_element_type_id := get_object_id('ELE',p_ele_name||' Special Inputs');
737 l_sf_core_element_type_id := get_object_id('ELE',p_ele_name||' Special Features');
738 l_fees_core_element_type_id := get_object_id('ELE',p_ele_name||' Fees');
739 -- Added for Garnishment Rewrite --
740 if p_ele_classification = l_inv_ded then
741 --l_ver_core_element_type_id := get_object_id('ELE',p_ele_name||' Verifier');
742 l_cal_core_element_type_id := get_object_id('ELE',p_ele_name||' Calculator');
743 end if;
744 --
745 -- Get the Id for the JD Element. Modified for Garnishment rewrite
746 --
747 if p_ele_category in ('D','E','G')and
748 p_ele_classification <> l_inv_ded then
749 l_jd_core_element_type_id :=
750 get_object_id('ELE', p_ele_name||' Taxable By JD');
751 end if;
752 l_pri_bal_id := get_object_id('BAL', p_ele_name);
753
754 -- Added for Garnishment rewrite --
755 if p_ele_classification = l_inv_ded then
756 l_addl_bal_id := get_object_id('BAL', p_ele_name||' Additional');
757 l_repl_bal_id := get_object_id('BAL', p_ele_name||' Replacement');
758 l_fee_bal_id := get_object_id('BAL', p_ele_name||' Fees');
759 l_accr_fees_bal_id := get_object_id('BAL',p_ele_name||' Accrued Fees');
760 l_vol_ded_bal_id := get_object_id('BAL', p_ele_name||' Vol Dedns');
761 else
762 l_addl_bal_id := get_object_id('BAL', p_ele_name||' Additional Amount');
763 l_repl_bal_id := get_object_id('BAL', p_ele_name||' Replacement Amount');
764 end if;
765
766 l_accr_bal_id := get_object_id('BAL', p_ele_name||' Accrued');
767 l_not_taken_bal_id := get_object_id('BAL', p_ele_name||' Not Taken');
768
769 if p_ele_arrearage = 'Y' then
770 l_arr_bal_id := get_object_id('BAL', p_ele_name||' Arrears');
771 end if;
772 --
773 -- Get the input value id for Catchup Processing
774 --
775 if nvl(p_catchup_processing ,'NONE') <> 'NONE' then
776 l_cu_proc_inp_value_id
777 := get_cu_input_value_id (l_cu_element_type_id);
778 --
779 -- Update the Default Value for Catchup Processing
780 -- to the one selected in the deductions form.
781 --
782 update pay_input_values_f
783 set default_value = substr(p_catchup_processing,1,1)
784 where input_value_id = l_cu_proc_inp_value_id;
785 end if;
786 --
787 -- Get the id's for the AT components if the After Tax component is Y
788 --
789 if p_after_tax_component = 'Y' then
790 -- element ids
791 l_at_base_element_type_id := get_object_id('ELE', p_ele_name||' AT');
792 l_at_si_core_element_type_id :=
793 get_object_id ('ELE', p_ele_name||' AT Special Inputs');
794 l_at_sf_core_element_type_id :=
795 get_object_id ('ELE', p_ele_name||' AT Special Features');
796 -- balance id's
797 l_at_pri_bal_id := get_object_id('BAL', p_ele_name||' AT');
798 l_at_addl_bal_id := get_object_id('BAL', p_ele_name||' AT Additional Amount');
799 l_at_repl_bal_id := get_object_id('BAL', p_ele_name||' AT Replacement Amount');
800 if p_ele_arrearage = 'Y' then
801 l_at_arr_bal_id :=
802 get_object_id('BAL', p_ele_name||' AT Arrears');
803 l_at_not_taken_bal_id :=
804 get_object_id('BAL', p_ele_name||' AT Not Taken');
805 end if;
806 if p_ele_stop_rule = 'Total Reached' then
807 l_at_accr_bal_id :=
808 get_object_id('BAL', p_ele_name||' AT Accrued');
809 end if;
810 end if;
811 --
812 -- Update the Input value with the correct lookup code if CU is not
813 -- chosen and just AT is chosen.
814 --
815 if p_after_tax_component = 'Y' and
816 p_catchup_processing = 'NONE' then
817 for temp_rec in c_at_ivn(l_at_base_element_type_id)
818 loop
819 if temp_rec.name = 'Take Overlimit AT' then
820 update pay_input_values_f
821 set lookup_type = 'PQP_US_OVERLIMIT_AT1'
822 where input_value_id = temp_rec.input_value_id;
823 elsif temp_rec.name = 'AT Processing Order' then
824 update pay_input_values_f
825 set lookup_type = 'PQP_US_AT_PROCESSING_ORDER1'
826 where input_value_id = temp_rec.input_value_id;
827 end if;
828 end loop;
829 end if;
830 --
831 -- Get the pre-tax ER and After-Tax ER balance
832 --
833 if p_employer_match = 'Y' then
834 -- element id
835 l_er_element_type_id := get_object_id('ELE', p_ele_name||' ER');
836 -- balance id
837 l_er_bal_id := get_object_id('BAL', p_ele_name||' ER');
838 if p_after_tax_component = 'Y' then
839 l_at_er_element_type_id := get_object_id('ELE', p_ele_name||' AT ER');
840 l_at_er_bal_id := get_object_id('BAL', p_ele_name||' AT ER');
841 end if;
842 end if;
843 --
844 -- Check if element has Roth Contribution, option valid for 401k/403b only
845 --
846 if nvl(p_roth_contribution,'N') = 'Y' then
847
848 l_roth_ele_type_id := get_object_id('ELE',p_ele_name||' Roth');
849
850 -- If employer match is not selected then change the lookup type for
851 -- eligible comp option for Roth 401k AT element
852 if nvl(p_employer_match ,'N') = 'N' and
853 p_ele_category in ('D') then
854 l_roth_eligiCmp_ipv_id := get_elgiComp_ipv (l_roth_ele_type_id);
855 if l_roth_eligiCmp_ipv_id is not null then
856 update pay_input_values_f
857 set default_value = 'S_EE'
858 ,lookup_type = 'US_ELIGIBLE_COMP_OPTIONS_EE'
859 where input_value_id = l_roth_eligiCmp_ipv_id;
860 end if;
861 end if;
862
863 -- Get the Roth ER Match element and primary balance
864 if p_employer_match = 'Y' then
865 l_roth_er_ele_type_id := get_object_id('ELE',p_ele_name||' Roth ER');
866 l_atr_er_bal_id := get_object_id('BAL', p_ele_name||' Roth ER');
867 end if;
868 --
869 l_roth_si_ele_type_id := get_object_id('ELE',p_ele_name||' Roth SI');
870 l_roth_sf_ele_type_id := get_object_id('ELE',p_ele_name||' Roth SF');
871
872 -- Get the primary Roth AT element's balance id
873 l_atr_pri_bal_id := get_object_id('BAL', p_ele_name||' Roth');
874 -- If element processing is Recurring the get the Rep. and Add. balance
875 if p_ele_processing_type = 'R' then
876 l_atr_addl_bal_id :=
877 get_object_id('BAL', p_ele_name||' Roth Additional Amount');
878 l_atr_repl_bal_id :=
879 get_object_id('BAL', p_ele_name||' Roth Replacement Amount');
880 end if;
881
882 -- If Arrearage is selected then get the Arrears and Not taken balance
883 if p_ele_arrearage = 'Y' then
884 l_atr_arr_bal_id :=
885 get_object_id('BAL', p_ele_name||' Roth Arrears');
886 end if;
887 --
888 l_atr_not_taken_bal_id
889 := get_object_id('BAL', p_ele_name||' Roth Not Taken');
890 -- If element has stop rule
891 if p_ele_stop_rule = 'Total Reached' then
892 l_atr_accr_bal_id :=
893 get_object_id('BAL', p_ele_name||' Roth Accrued');
894 end if;
895 -- If catch-up processing is selected
896 if p_catchup_processing = 'NONE' then
897 for temp_rec in c_atr_ivn(l_roth_er_ele_type_id)
898 loop
899 if temp_rec.name = 'Take Overlimit Roth' then
900 update pay_input_values_f
901 set lookup_type = 'PQP_US_OVERLIMIT_AT1'
902 where input_value_id = temp_rec.input_value_id;
903 elsif temp_rec.name = 'Roth Processing Order' then
904 update pay_input_values_f
905 set lookup_type = 'PQP_US_AT_PROCESSING_ORDER1'
906 where input_value_id = temp_rec.input_value_id;
907 end if;
908 end loop;
909 end if;
910
911 end if; --if nvl(p_roth_contribution,'N')
912 --
913 hr_utility.set_location('Leaving: '||l_proc, 100);
914 --
915 end get_element_and_bal_ids;
916 --
917 -- =============================================================================
918 -- insert_iterative_rules:
919 -- =============================================================================
920 procedure insert_iterative_rules
921 (p_iter_element_type_id pay_element_types_f.element_type_id%type
922 ) is
923
924 l_proc varchar2(160);
925
926 begin
927 l_proc := g_proc||'.insert_iterative_rules';
928 hr_utility.set_location('Entering: '||l_proc, 5);
929
930 insert into pay_iterative_rules_f
931 (iterative_rule_id
932 ,element_type_id
933 ,effective_start_date
934 ,effective_end_date
935 ,result_name
936 ,iterative_rule_type
937 ,input_value_id
938 ,severity_level
939 ,business_group_id
940 ,legislation_code
941 ,object_version_number
942 ,created_by
943 ,creation_date
944 ,last_update_date
945 ,last_updated_by
946 ,last_update_login
947 )
948 values
949 (pay_iterative_rules_s.nextval
950 ,p_iter_element_type_id
951 ,p_ele_eff_start_date
952 ,to_date('31-12-4712','DD-MM-YYYY')
953 ,'STOPPER'
954 ,'S'
955 ,null
956 ,null
957 ,p_bg_id
958 ,'US'
959 ,1
960 ,-1
961 ,p_ele_eff_start_date
962 ,p_ele_eff_start_date
963 ,-1
964 ,-1
965 );
966 hr_utility.set_location('Leaving: '||l_proc, 10);
967 end insert_iterative_rules;
968
969 -- =============================================================================
970 -- Main Function
971 -- =============================================================================
972 begin
973 --hr_utility.trace_on(null,'tmehra');
974 l_proc := g_proc||'.create_user_init_template';
975 hr_utility.set_location('Entering : '||l_proc, 10);
976 --
977 -- Initialize local variables
978 --
979 l_at_er_exclusion_rule := 'N';
980 l_cu_si_exclusion_rule := 'N';
981 l_glb_name1 := 'EMPLOYER_MATCH_LIMIT';
982 l_glb_name := 'EMPLOYER_MATCH_PCT';
983 l_dbp_exclusion_rule := 'N';
984 l_dcp_exclusion_rule := 'N';
985 l_inv_ded := 'Involuntary Deductions';
986 --
987 -- Set local variable for ele information category
988 --
989 if p_ele_classification = l_inv_ded then
990 l_element_information_category := 'US_INVOLUNTARY DEDUCTIONS';
991 else
992 l_element_information_category := 'US_PRE-TAX DEDUCTIONS';
993 end if;
994 --
995 -- Set session date
996 --
997 pay_db_pay_setup.set_session_date(nvl(p_ele_eff_start_date, sysdate));
998 --
999 hr_utility.set_location(l_proc, 20);
1000 --
1001 -- Get Source Template ID
1002 --
1003 l_source_template_id := get_template_id
1004 (p_legislation_code => 'US'
1005 ,p_ele_category => p_ele_category
1006 ,p_ele_srs_plan_type => NVL(p_ele_srs_plan_type,'NONE')
1007 );
1008 hr_utility.set_location(l_proc, 30);
1009 --
1010 /*
1011 ==============================================================================
1012 create the user structure the Configuration Flex segments for the
1013 Exclusion Rules are as follows:
1014 ==============================================================================
1015 Config1 -- exclude SI and SF elements if ele_processing_type='N'
1016 Config2 -- exclude Arrearage related structures if ele_arrearage='N'
1017 Config3 -- exclude Partial Deductions structures if
1018 -- ele_partial_deduction='N'
1019 Config4 -- exclude Stop rule structures if ele_stop_rule='Total Reached'
1020 -- default is OE -On entry
1021 Config5 -- exclude Start rule structures. Default is '', excludes if
1022 -- ET(Earnings Threshold), Chained is not supported in 403/457
1023 Config6 -- exclude After tax element structures if after_tax_component='N'
1024 Config7 -- exclude Employer Match element structures if employer_match='N'
1025 Config8 -- exclude Non Recurring-After tax structures if
1026 -- after_tax_component='Y' and ele_processing_type='N'
1027 Config9 -- exclude DCP elements
1028 Config10 -- exclude Flat amt calculation structures if ele_calc_rule=FA
1029 Config11 -- exclude Percentage calculation structures if ele_calc_rule=PE
1030 Config12 -- exclude AT-ER element if either employer_match or
1031 -- after_tax_component = 'N'
1032 Config13 -- exclude catchup processing
1033 Config14 -- exclude Buy Back element if p_ele_srs_buy_back = 'N'
1034 Config15 -- exclude DBP elements
1035 Config16 -- exclude the CatchUp SI when base is non-recurring
1036 Config17 -- exclude the After-Tax SI when base is non-recurring
1037 Config18 -- Roth contribution for 401k
1038 Config19 -- exclude After-Tax Roth SI when base is non-recurring
1039 Config20 -- exclude Roth ER match element when ER component is not selected
1040
1041 Config21 -- rule to create Pre-tax USERRA element
1042 Config22 -- rule to create Pre-tax USERRA element
1043 Config23 -- rule to create Pre-tax USERRA element
1044
1045 Config24 -- rule to create After-Tax USERRA element
1046 Config25 -- rule to create After-Tax USERRA element
1047 Config26 -- rule to create After-Tax USERRA element
1048 Config27 -- rule to create balance feeds for W2 Roth 403b
1049 Config28 -- rule to create balance feeds for W2 Roth 401k
1050 =============================================================================
1051 */
1052 --
1053 -- set the aftertax nonrecurring rule(config8)
1054 --
1055 if p_after_tax_component = 'N' then
1056 l_aftertax_nonrecurring_rule := 'N';
1057 elsif p_ele_processing_type = 'N' then
1058 l_aftertax_nonrecurring_rule := 'N';
1059 else
1060 l_aftertax_nonrecurring_rule := 'Y';
1061 end if;
1062 -- AT Special Inputs created only for Recurring processing type
1063 -- Config17: Exclude the After-Tax SI when base is non-recurring
1064 if p_after_tax_component = 'Y' and
1065 p_ele_processing_type = 'R' then
1066 l_aftertax_si_rule := 'Y';
1067 else
1068 l_aftertax_si_rule := 'N';
1069 end if;
1070 -- Config16: The Catch Special Inputs should only be
1071 -- created for recurring base element.
1072 if p_catchup_processing ='NONE' or
1073 p_ele_processing_type='N' then
1074 l_cu_si_exclusion_rule := 'N';
1075 elsif p_catchup_processing <> 'NONE' and
1076 p_ele_processing_type <> 'N' then
1077 l_cu_si_exclusion_rule := 'Y';
1078 end if;
1079 --
1080 -- After-tax ER Match
1081 --
1082 if p_after_tax_component = 'Y' and
1083 p_employer_match = 'Y' then
1084 l_at_er_exclusion_rule := 'Y';
1085 end if;
1086 -- State Retirement, Buy Back rules
1087 if (p_ele_category in ('DCP', 'DBP')) or
1088 (NVL(p_ele_srs_plan_type, 'N') <> 'N') then
1089 if p_ele_srs_plan_type = 'B' then
1090 l_dbp_exclusion_rule := 'N';
1091 l_dcp_exclusion_rule := 'Y';
1092 else
1093 l_dbp_exclusion_rule := 'Y';
1094 l_dcp_exclusion_rule := 'N';
1095 end if;
1096 end if;
1097 -- Config19: If base is recurring then create Roth Contribution SI element
1098 if nvl(p_roth_contribution,'N') = 'Y' and
1099 p_ele_processing_type = 'R' then
1100 l_at_roth_si_rule := 'Y';
1101 else
1102 l_at_roth_si_rule := 'N';
1103 end if;
1104 -- Config20: Create the Roth ER Match element only ER
1105 -- and Roth components are selected
1106 if p_roth_contribution = 'Y' and
1107 p_employer_match = 'Y' then
1108 l_at_roth_er_rule := 'Y';
1109 else
1110 l_at_roth_er_rule := 'N';
1111 end if;
1112
1113 -- Pre-Tax USERRA element, SI and ER
1114 if p_userra_contribution ='Y' and
1115 p_ele_processing_type = 'R' and
1116 p_employer_match = 'Y' then
1117
1118 l_pt_userra_si_rule := 'Y';
1119 l_pt_userra_rule := 'Y';
1120 l_pt_userra_er_rule := 'Y';
1121
1122 elsif p_userra_contribution ='Y' and
1123 p_ele_processing_type <> 'R' and
1124 p_employer_match = 'Y' then
1125
1126 l_pt_userra_si_rule := 'N';
1127 l_pt_userra_rule := 'Y';
1128 l_pt_userra_er_rule := 'Y';
1129
1130 elsif p_userra_contribution ='Y' and
1131 p_ele_processing_type <> 'R' and
1132 p_employer_match <> 'Y' then
1133
1134 l_pt_userra_si_rule := 'N';
1135 l_pt_userra_rule := 'Y';
1136 l_pt_userra_er_rule := 'N';
1137 end if;
1138 -- Roth After-Tax USERRA element, SI and ER 4489655
1139 if p_roth_contribution = 'Y' then
1140 if p_userra_contribution ='Y' and
1141 p_ele_processing_type = 'R' and
1142 p_employer_match = 'Y' then
1143
1144 l_atr_userra_si_rule := 'Y';
1145 l_atr_userra_rule := 'Y';
1146 l_atr_userra_er_rule := 'Y';
1147
1148 elsif p_userra_contribution ='Y' and
1149 p_ele_processing_type <> 'R' and
1150 p_employer_match = 'Y' then
1151
1152 l_atr_userra_si_rule := 'N';
1153 l_atr_userra_rule := 'Y';
1154 l_atr_userra_er_rule := 'Y';
1155
1156 elsif p_userra_contribution ='Y' and
1157 p_ele_processing_type <> 'R' and
1158 p_employer_match <> 'Y' then
1159
1160 l_atr_userra_si_rule := 'N';
1161 l_atr_userra_rule := 'Y';
1162 l_atr_userra_er_rule := 'N';
1163 end if;
1164 end if;
1165
1166 --
1167 -- Set the element processing priority
1168 --
1169 if p_ele_priority = 3750 or
1170 p_ele_classification = 'Involuntary Deductions' then
1171
1172 l_template_priority := l_ele_template_priority;
1173 else
1174 l_template_priority := p_ele_priority;
1175
1176 end if;
1177
1178 pay_element_template_api.create_user_structure
1179 (p_validate => false
1180 ,p_effective_date => p_ele_eff_start_date
1181 ,p_business_group_id => p_bg_id
1182 ,p_source_template_id => l_source_template_id
1183 ,p_base_name => p_ele_name
1184 ,p_base_processing_priority => l_template_priority
1185 ,p_configuration_information1 => p_ele_processing_type
1186 ,p_configuration_information2 => p_ele_arrearage
1187 ,p_configuration_information3 => p_ele_partial_deduction
1188 ,p_configuration_information4 => p_ele_stop_rule
1189 ,p_configuration_information5 => p_ele_start_rule
1190 ,p_configuration_information6 => p_after_tax_component
1191 ,p_configuration_information7 => p_employer_match
1192 ,p_configuration_information8 => l_aftertax_nonrecurring_rule
1193 ,p_configuration_information9 => l_dcp_exclusion_rule
1194 ,p_configuration_information10 => p_ele_calc_rule
1195 ,p_configuration_information11 => p_ele_calc_rule
1196 ,p_configuration_information12 => l_at_er_exclusion_rule
1197 ,p_configuration_information13 => p_catchup_processing
1198 ,p_configuration_information14 => p_ele_srs_buy_back
1199 ,p_configuration_information15 => l_dbp_exclusion_rule
1200 ,p_configuration_information16 => l_cu_si_exclusion_rule
1201 ,p_configuration_information17 => l_aftertax_si_rule
1202 -- Roth Rules
1203 ,p_configuration_information18 => p_roth_contribution
1204 ,p_configuration_information19 => l_at_roth_si_rule
1205 ,p_configuration_information20 => l_at_roth_er_rule
1206 -- Pre-Tax USERRA Rules
1207 ,p_configuration_information21 => l_pt_userra_si_rule
1208 ,p_configuration_information22 => l_pt_userra_rule
1209 ,p_configuration_information23 => l_pt_userra_er_rule
1210 -- After-Tax Roth USERRA Rules
1211 ,p_configuration_information24 => l_atr_userra_si_rule
1212 ,p_configuration_information25 => l_atr_userra_rule
1213 ,p_configuration_information26 => l_atr_userra_er_rule
1214 --
1215 ,p_template_id => l_template_id
1216 ,p_object_version_number => l_object_version_number
1217 );
1218 --
1219 hr_utility.set_location(l_proc, 80);
1220 -- =========================================================================
1221 -- Create Global Values: For 401k ER Match percentage and limit
1222 -- =========================================================================
1223 open c_global;
1224 fetch c_global into l_dummy;
1225 if c_global%notfound then
1226 -- Create Global Value
1227 ff_globals_f_pkg.insert_row(
1228 x_rowid => l_glb_rowid,
1229 x_global_id => l_glb_id,
1230 x_effective_start_date => to_date('01/01/1900','dd/mm/yyyy'),
1231 x_effective_end_date => to_date('31/12/4712','dd/mm/yyyy'),
1232 x_business_group_id => p_bg_id,
1233 x_legislation_code => null,
1234 x_data_type => 'N',
1235 x_global_name => l_glb_name,
1236 x_global_description => 'The rate of the employer match',
1237 x_global_value => .50);
1238 close c_global;
1239 else
1240 close c_global;
1241 end if;
1242
1243 open c_global1;
1244 fetch c_global1 into l_dummy;
1245 if c_global1%notfound then
1246 -- Create Global Value
1247 ff_globals_f_pkg.insert_row(
1248 x_rowid => l_glb_rowid1,
1249 x_global_id => l_glb_id1,
1250 x_effective_start_date => to_date('01/01/1900','dd/mm/yyyy'),
1251 x_effective_end_date => to_date('31/12/4712','dd/mm/yyyy'),
1252 x_business_group_id => p_bg_id,
1253 x_legislation_code => null,
1254 x_data_type => 'N',
1255 x_global_name => l_glb_name1,
1256 x_global_description => 'The rate of the employer match limit',
1257 x_global_value => .06);
1258 close c_global1;
1259 else
1260 close c_global1;
1261 end if;
1262
1263 hr_utility.set_location(l_proc, 85);
1264 -- =========================================================================
1265 -- Update Shadow Structure: Get Element Type id and update user-specified
1266 -- Classification,Category, Processing Type and Standard Link on Base Element
1267 -- =========================================================================
1268 for c1_rec in c1 ( p_ele_name ) loop
1269 l_element_type_id := c1_rec.element_type_id;
1270 l_ele_obj_ver_number := c1_rec.object_version_number;
1271 end loop;
1272 -- Added for Garnishment rewrite --
1273 if p_ele_classification <> l_inv_ded then
1274 if p_ele_start_rule = 'ET' then
1275 l_skip_formula := 'THRESHOLD_SKIP_FORMULA';
1276 else
1277 l_skip_formula := 'FREQ_RULE_SKIP_FORMULA';
1278 end if;
1279 end if;
1280 --
1281 if p_ben_class_id is not null then
1282 for c_rec in c_ben_class(p_ben_class_id) loop
1283 l_ben_class_name := c_rec.benefit_classification_name;
1284 end loop;
1285 end if;
1286 --
1287 pay_shadow_element_api.update_shadow_element
1288 (p_validate => false
1289 ,p_effective_date => p_ele_eff_start_date
1290 ,p_element_type_id => l_element_type_id
1291 ,p_description => p_ele_description
1292 ,p_reporting_name => p_ele_reporting_name
1293 ,p_post_termination_rule => p_termination_rule
1294 ,p_benefit_classification_name => l_ben_class_name
1295 ,p_element_information_category => l_element_information_category
1296 ,p_classification_name => nvl(p_ele_classification, hr_api.g_varchar2)
1297 ,p_processing_type => nvl(p_ele_processing_type, hr_api.g_varchar2)
1298 ,p_standard_link_flag => nvl(p_ele_standard_link, hr_api.g_varchar2)
1299 ,p_skip_formula => l_skip_formula
1300 ,p_element_information1 => nvl(p_ele_category, hr_api.g_varchar2)
1301 ,p_element_information2 => nvl(p_ele_partial_deduction, hr_api.g_varchar2)
1302 ,p_element_information3 => nvl(p_ele_proc_runtype, hr_api.g_varchar2)
1303 ,p_element_information9 => 'D'
1304 ,p_object_version_number => l_ele_obj_ver_number
1305 );
1306 hr_utility.set_location(l_proc, 90);
1307 -- SRS: State Retirement Systems
1308 -- Update Reporting Name and other details on the shadow tables.
1309 --
1310 if (p_ele_category in ('DCP', 'DBP')) or
1311 (nvl(p_ele_srs_plan_type, 'N') <> 'N') then
1312 for i in 1..2
1313 loop
1314 if i = 1 then
1315 -- For the ER Contribution Element
1316 for c1_rec in c1 ( p_ele_name||' ER Contribution' ) loop
1317 l_srs_ERContr_type_id := c1_rec.element_type_id;
1318 l_srs_erContr_obj_ver_number := c1_rec.object_version_number;
1319 l_srs_erContr_rep_name :=
1320 nvl(p_ele_reporting_name,p_ele_name)||' ER Contribution';
1321 l_srs_erContr_desc_name :=
1322 'Generated Element for :'||
1323 nvl(p_ele_reporting_name,p_ele_name)||
1324 ' ER Contribution';
1325 end loop;
1326 else
1327 -- For ER Contribution Special Features
1328 for c1_rec in c1 ( p_ele_name||' ER Contribution SF' ) loop
1329 l_srs_ERContr_type_id := c1_rec.element_type_id;
1330 l_srs_erContr_obj_ver_number := c1_rec.object_version_number;
1331 l_srs_erContr_rep_name := nvl(p_ele_reporting_name,p_ele_name)||
1332 ' ER Contribution SF';
1333 l_srs_erContr_desc_name := 'Generated Element for :'||
1334 nvl(p_ele_reporting_name,p_ele_name)||
1335 ' ER Contribution Special Features';
1336 end loop;
1337 end if;
1338 pay_shadow_element_api.update_shadow_element
1339 (p_validate => false
1340 ,p_effective_date => p_ele_eff_start_date
1341 ,p_element_type_id => l_srs_ERContr_type_id
1342 ,p_description => l_srs_erContr_desc_name
1343 ,p_reporting_name => l_srs_erContr_rep_name
1344 ,p_post_termination_rule => p_termination_rule
1345 ,p_processing_type => nvl(p_ele_processing_type, hr_api.g_varchar2)
1346 ,p_object_version_number => l_srs_erContr_obj_ver_number
1347 );
1348 end loop;
1349 --
1350 -- For Buy Back element
1351 --
1352 if p_ele_srs_buy_back = 'Y' then
1353 -- Means that the category is SRS type. Check for the Buy Back Flag
1354 -- and get Id's for the Buy back element
1355 for c1_rec in c1 ( p_ele_name||' Buy Back' ) loop
1356 l_bb_element_type_id := c1_rec.element_type_id;
1357 l_bb_ele_obj_ver_number := c1_rec.object_version_number;
1358 end loop;
1359 pay_shadow_element_api.update_shadow_element
1360 (p_validate => false
1361 ,p_effective_date => p_ele_eff_start_date
1362 ,p_element_type_id => l_bb_element_type_id
1363 ,p_description => 'Generated Element For:'
1364 ||p_ele_name||' Buy Back'
1365 ,p_reporting_name => nvl(p_ele_reporting_name,p_ele_name)
1366 ||' Buy Back'
1367 ,p_post_termination_rule => p_termination_rule
1368 ,p_classification_name => nvl(p_ele_classification, hr_api.g_varchar2)
1369 ,p_element_information_category => 'US_PRE-TAX DEDUCTIONS'
1370 ,p_skip_formula => l_skip_formula
1371 ,p_processing_type => nvl(p_ele_processing_type, hr_api.g_varchar2)
1372 ,p_standard_link_flag => nvl(p_ele_standard_link, hr_api.g_varchar2)
1373 ,p_element_information1 => nvl(p_ele_category, hr_api.g_varchar2)
1374 ,p_element_information2 => nvl(p_ele_partial_deduction, hr_api.g_varchar2)
1375 ,p_element_information3 => nvl(p_ele_proc_runtype, hr_api.g_varchar2)
1376 ,p_object_version_number => l_bb_ele_obj_ver_number
1377 );
1378 l_temp_var(1).sub_name:=' Buy Back SI';
1379 l_temp_var(2).sub_name:=' Buy Back SF';
1380 for i in 1..2
1381 loop
1382 l_cur_temp:='';
1383 for c1_rec in c1 ( p_ele_name||l_temp_var(i).sub_name )
1384 loop
1385 if l_temp_var(i).sub_name=' Buy Back SI' then
1386 l_bb_si_element_type_id := c1_rec.element_type_id;
1387 l_bb_ele_obj_ver_number := c1_rec.object_version_number;
1388 l_cur_temp := c1_rec.element_type_id;
1389 elsif l_temp_var(i).sub_name=' Buy Back SF' then
1390 l_bb_sf_element_type_id := c1_rec.element_type_id;
1391 l_bb_ele_obj_ver_number := c1_rec.object_version_number;
1392 l_cur_temp := c1_rec.element_type_id;
1393 end if;
1394 pay_shadow_element_api.update_shadow_element
1395 (p_validate => false
1396 ,p_effective_date => p_ele_eff_start_date
1397 ,p_post_termination_rule => p_termination_rule
1398 ,p_element_type_id => l_cur_temp
1399 ,p_description => 'Generated Element For:'
1400 ||p_ele_name||l_temp_var(i).sub_name
1401 ,p_reporting_name => nvl(p_ele_reporting_name,p_ele_name)
1402 ||l_temp_var(i).sub_name
1403 ,p_element_information_category => 'US_PRE-TAX DEDUCTIONS'
1404 ,p_element_information1 => nvl(p_ele_category, hr_api.g_varchar2)
1405 ,p_object_version_number => l_bb_ele_obj_ver_number
1406 );
1407 end loop;
1408 end loop;
1409 hr_utility.set_location(l_proc, 95);
1410 end if; -- p_ele_srs_buy_back = y
1411 end if; -- p_ele_srs_plan_type = Y
1412
1413 hr_utility.set_location(l_proc, 96);
1414
1415 -- Update Reporting Name and other details on the shadow tables.
1416 -- for Catch-Up element if option is selected
1417 if nvl(p_catchup_processing,'NONE') <> 'NONE' then
1418
1419 for c1_rec in c1 ( p_ele_name||' Catchup' )
1420 loop
1421 l_cu_element_type_id := c1_rec.element_type_id;
1422 l_cu_ele_obj_ver_number := c1_rec.object_version_number;
1423 end loop;
1424
1425 pay_shadow_element_api.update_shadow_element
1426 (p_validate => false
1427 ,p_effective_date => p_ele_eff_start_date
1428 ,p_element_type_id => l_cu_element_type_id
1429 ,p_description => 'Generated Element For:'
1430 ||p_ele_name||' Catchup'
1431 ,p_reporting_name => nvl(p_ele_reporting_name,p_ele_name) ||' Catchup'
1432 ,p_post_termination_rule => p_termination_rule
1433 ,p_classification_name => nvl(p_ele_classification, hr_api.g_varchar2)
1434 ,p_element_information_category => 'US_PRE-TAX DEDUCTIONS'
1435 ,p_skip_formula => l_skip_formula
1436 ,p_processing_type => nvl(p_ele_processing_type, hr_api.g_varchar2)
1437 ,p_standard_link_flag => nvl(p_ele_standard_link, hr_api.g_varchar2)
1438 ,p_element_information1 => nvl(p_ele_category||'C', hr_api.g_varchar2)
1439 ,p_element_information2 => nvl(p_ele_partial_deduction, hr_api.g_varchar2)
1440 ,p_element_information3 => nvl(p_ele_proc_runtype, hr_api.g_varchar2)
1441 ,p_object_version_number => l_cu_ele_obj_ver_number
1442 );
1443
1444 l_temp_var(1).sub_name:=' Catchup SI';
1445 l_temp_var(2).sub_name:=' Catchup SF';
1446 for i in 1..2
1447 loop
1448 l_cur_temp:='';
1449 for c1_rec in c1 ( p_ele_name||l_temp_var(i).sub_name )
1450 loop
1451 if l_temp_var(i).sub_name=' Catchup SI' then
1452 l_cu_si_element_type_id := c1_rec.element_type_id;
1453 l_cu_ele_obj_ver_number := c1_rec.object_version_number;
1454 l_cur_temp := c1_rec.element_type_id;
1455 elsif l_temp_var(i).sub_name=' Catchup SF' then
1456 l_cu_sf_element_type_id := c1_rec.element_type_id;
1457 l_cu_ele_obj_ver_number := c1_rec.object_version_number;
1458 l_cur_temp := c1_rec.element_type_id;
1459 end if;
1460 pay_shadow_element_api.update_shadow_element
1461 (p_validate => false
1462 ,p_effective_date => p_ele_eff_start_date
1463 ,p_element_type_id => l_cur_temp
1464 ,p_description => 'Generated Element For:'
1465 ||p_ele_name||l_temp_var(i).sub_name
1466 ,p_reporting_name => nvl(p_ele_reporting_name,p_ele_name)
1467 ||l_temp_var(i).sub_name
1468 ,p_post_termination_rule => p_termination_rule
1469 ,p_element_information_category => 'US_PRE-TAX DEDUCTIONS'
1470 ,p_element_information1 => nvl(p_ele_category||'C', hr_api.g_varchar2)
1471 ,p_object_version_number => l_cu_ele_obj_ver_number
1472 );
1473 end loop;
1474 end loop;
1475 hr_utility.set_location(l_proc, 97);
1476 end if; -- If Catch-Up <> NONE
1477
1478 -- Update Taxable by JD element with relevant Data.
1479 -- Modified for Garnishment rewrite
1480 if p_ele_category in ('D','E','G') and
1481 p_ele_classification <> l_inv_ded then
1482
1483 for c1_rec in c1 ( p_ele_name||' Taxable By JD' )
1484 loop
1485 l_jd_core_element_type_id := c1_rec.element_type_id;
1486 l_jd_ele_obj_ver_number := c1_rec.object_version_number;
1487 end loop;
1488
1489 pay_shadow_element_api.update_shadow_element
1490 (p_validate => false
1491 ,p_effective_date => p_ele_eff_start_date
1492 ,p_element_type_id => l_jd_core_element_type_id
1493 ,p_description => 'Generated Element For:'
1494 ||p_ele_name||' Taxable by JD'
1495 ,p_reporting_name => nvl(p_ele_reporting_name,p_ele_name)
1496 ||' Taxable by JD'
1497 ,p_post_termination_rule => p_termination_rule
1498 ,p_classification_name => nvl(p_ele_classification, hr_api.g_varchar2)
1499 ,p_element_information_category => 'US_PRE-TAX DEDUCTIONS'
1500 ,p_element_information1 => nvl(p_ele_category||'J', hr_api.g_varchar2)
1501 ,p_element_information2 => nvl(p_ele_partial_deduction, hr_api.g_varchar2)
1502 ,p_element_information3 => nvl(p_ele_proc_runtype, hr_api.g_varchar2)
1503 ,p_object_version_number => l_jd_ele_obj_ver_number
1504 );
1505 end if;
1506
1507 hr_utility.set_location(l_proc, 98);
1508
1509 --
1510 -- Update user-specified Classification on Special Features Element.
1511 -- ref. bug 1559726.
1512 if p_ele_classification <> l_inv_ded then
1513 for c1_rec in c1 ( p_ele_name||' Special Features' ) loop
1514 l_sf_element_type_id := c1_rec.element_type_id;
1515 l_sf_ele_obj_ver_number := c1_rec.object_version_number;
1516 end loop;
1517 pay_shadow_element_api.update_shadow_element
1518 (p_validate => false
1519 ,p_effective_date => p_ele_eff_start_date
1520 ,p_element_type_id => l_sf_element_type_id
1521 ,p_description => 'Generated results element for:'
1522 ||p_ele_name
1523 ,p_reporting_name => nvl(p_ele_reporting_name,p_ele_name) ||' Special Features'
1524 ,p_classification_name => nvl(p_ele_classification, hr_api.g_varchar2)
1525 ,p_post_termination_rule => p_termination_rule
1526 ,p_element_information_category => 'US_PRE-TAX DEDUCTIONS'
1527 ,p_element_information1 => nvl(p_ele_category, hr_api.g_varchar2)
1528 ,p_element_information2 => nvl(p_ele_partial_deduction, hr_api.g_varchar2)
1529 ,p_element_information3 => nvl(p_ele_proc_runtype, hr_api.g_varchar2)
1530 ,p_object_version_number => l_sf_ele_obj_ver_number
1531 );
1532 end if;
1533
1534 hr_utility.set_location(l_proc, 99);
1535
1536 --
1537 -- Update user-specified Classification Special Inputs if it exists.
1538 --
1539 if p_ele_processing_type = 'R' then
1540 for c1_rec in c1 ( p_ele_name||' Special Inputs' )
1541 loop
1542 l_si_element_type_id := c1_rec.element_type_id;
1543 l_si_ele_obj_ver_number := c1_rec.object_version_number;
1544 end loop;
1545 pay_shadow_element_api.update_shadow_element
1546 (p_validate => false
1547 ,p_effective_date => p_ele_eff_start_date
1548 ,p_element_type_id => l_si_element_type_id
1549 ,p_description => 'Generated adjustments element for:'
1550 ||p_ele_name
1551 ,p_classification_name => nvl(p_ele_classification, hr_api.g_varchar2)
1552 ,p_reporting_name => nvl(p_ele_reporting_name,p_ele_name)||' Special Inputs'
1553 ,p_post_termination_rule => p_termination_rule
1554 ,p_element_information_category => l_element_information_category
1555 ,p_element_information1 => nvl(p_ele_category, hr_api.g_varchar2)
1556 ,p_element_information2 => nvl(p_ele_partial_deduction, hr_api.g_varchar2)
1557 ,p_element_information3 => nvl(p_ele_proc_runtype, hr_api.g_varchar2)
1558 ,p_object_version_number => l_si_ele_obj_ver_number
1559 );
1560 end if;
1561
1562 hr_utility.set_location(l_proc, 100);
1563
1564 -- Added for Garnishment rewrite
1565 if p_ele_classification = l_inv_ded then
1566 for c1_rec in c1 ( p_ele_name||' Calculator' ) loop
1567 l_ca_element_type_id := c1_rec.element_type_id;
1568 l_ca_ele_obj_ver_number := c1_rec.object_version_number;
1569 end loop;
1570 pay_shadow_element_api.update_shadow_element
1571 (p_validate => false
1572 ,p_effective_date => p_ele_eff_start_date
1573 ,p_element_type_id => l_ca_element_type_id
1574 ,p_description => 'Generated calculation element for '
1575 ||p_ele_name
1576 ,p_classification_name => nvl(p_ele_classification, hr_api.g_varchar2)
1577 ,p_reporting_name => nvl(p_ele_reporting_name,p_ele_name)||' Calculator'
1578 ,p_post_termination_rule => p_termination_rule
1579 ,p_element_information_category => l_element_information_category
1580 ,p_element_information1 => nvl(p_ele_category, hr_api.g_varchar2)
1581 ,p_element_information2 => nvl(p_ele_partial_deduction, hr_api.g_varchar2)
1582 ,p_element_information3 => nvl(p_ele_proc_runtype, hr_api.g_varchar2)
1583 ,p_object_version_number => l_ca_ele_obj_ver_number
1584 );
1585
1586 for c1_rec in c1 ( p_ele_name||' Fees' ) loop
1587 l_fee_element_type_id := c1_rec.element_type_id;
1588 l_fee_ele_obj_ver_number := c1_rec.object_version_number;
1589 end loop;
1590 pay_shadow_element_api.update_shadow_element
1591 (p_validate => false
1592 ,p_effective_date => p_ele_eff_start_date
1593 ,p_element_type_id => l_fee_element_type_id
1594 ,p_description => 'Generated Fee results element for '
1595 ||p_ele_name
1596 ,p_classification_name => nvl(p_ele_classification, hr_api.g_varchar2)
1597 ,p_reporting_name => nvl(p_ele_reporting_name,p_ele_name)||' Fees'
1598 ,p_post_termination_rule => p_termination_rule
1599 ,p_element_information_category => l_element_information_category
1600 ,p_element_information1 => nvl(p_ele_category, hr_api.g_varchar2)
1601 ,p_element_information2 => nvl(p_ele_partial_deduction, hr_api.g_varchar2)
1602 ,p_element_information3 => nvl(p_ele_proc_runtype, hr_api.g_varchar2)
1603 ,p_object_version_number => l_fee_ele_obj_ver_number
1604 );
1605 end if;
1606
1607 hr_utility.set_location(l_proc, 101);
1608
1609 --
1610 -- Update user-specified details on all After-Tax Elements
1611 --
1612 if p_employer_match ='Y' then
1613 l_temp_var(1).sub_name:= ' ER';
1614 l_cur_temp := '';
1615 for c1_rec in c1 ( p_ele_name||l_temp_var(1).sub_name ) loop
1616 l_er_element_type_id := c1_rec.element_type_id;
1617 l_object_version_number := c1_rec.object_version_number;
1618 pay_shadow_element_api.update_shadow_element
1619 (p_validate => false
1620 ,p_effective_date => p_ele_eff_start_date
1621 ,p_element_type_id => l_er_element_type_id
1622 ,p_reporting_name => nvl(p_ele_reporting_name,p_ele_name)||l_temp_var(1).sub_name
1623 ,p_post_termination_rule => p_termination_rule
1624 ,p_description => 'Employer Match element for:'||p_ele_name
1625 ,p_object_version_number => l_object_version_number
1626 );
1627 end loop;
1628 end if;
1629
1630 hr_utility.set_location(l_proc, 102);
1631
1632 if p_after_tax_component = 'Y' then
1633
1634 for c1_rec in c1 ( p_ele_name||' AT' ) loop
1635 l_at_element_type_id := c1_rec.element_type_id;
1636 l_object_version_number := c1_rec.object_version_number;
1637 end loop;
1638
1639 pay_shadow_element_api.update_shadow_element
1640 (p_validate => false
1641 ,p_effective_date => p_ele_eff_start_date
1642 ,p_element_type_id => l_at_element_type_id
1643 ,p_reporting_name => nvl(p_ele_reporting_name,p_ele_name) ||' AT'
1644 ,p_post_termination_rule => p_termination_rule
1645 ,p_description => 'After Tax element for:'||p_ele_name
1646 ,p_element_information_category => 'US_VOLUNTARY DEDUCTIONS'
1647 ,p_skip_formula => l_skip_formula
1648 ,p_processing_type => nvl(p_ele_processing_type, hr_api.g_varchar2)
1649 ,p_element_information2 => nvl(p_ele_partial_deduction, hr_api.g_varchar2)
1650 ,p_element_information3 => nvl(p_ele_proc_runtype, hr_api.g_varchar2)
1651 ,p_object_version_number => l_object_version_number
1652 );
1653
1654 l_temp_var(1).sub_name:=' AT ER';
1655 l_cur_temp:='';
1656
1657 for c1_rec in c1 ( p_ele_name||l_temp_var(1).sub_name )
1658 loop
1659 l_at_er_element_type_id := c1_rec.element_type_id;
1660 l_object_version_number := c1_rec.object_version_number;
1661
1662 pay_shadow_element_api.update_shadow_element
1663 (p_validate => false
1664 ,p_effective_date => p_ele_eff_start_date
1665 ,p_element_type_id => l_at_er_element_type_id
1666 ,p_reporting_name => nvl(p_ele_reporting_name,p_ele_name)||l_temp_var(1).sub_name
1667 ,p_post_termination_rule => p_termination_rule
1668 ,p_description => 'After Tax Employer Match element for:'||p_ele_name
1669 ,p_object_version_number => l_object_version_number
1670 );
1671 end loop;
1672
1673 if p_ele_processing_type = 'R' then
1674 for c1_rec in c1 ( p_ele_name||' AT Special Inputs' ) loop
1675 l_at_si_element_type_id := c1_rec.element_type_id;
1676 l_object_version_number := c1_rec.object_version_number;
1677 end loop;
1678 pay_shadow_element_api.update_shadow_element
1679 (p_validate => false
1680 ,p_effective_date => p_ele_eff_start_date
1681 ,p_element_type_id => l_at_si_element_type_id
1682 ,p_description => 'Generated adjustments AT element for:'
1683 ||p_ele_name
1684 ,p_reporting_name => nvl(p_ele_reporting_name,p_ele_name)||' AT SI'
1685 ,p_post_termination_rule => p_termination_rule
1686 ,p_element_information_category => 'US_VOLUNTARY DEDUCTIONS'
1687 ,p_element_information2 => nvl(p_ele_partial_deduction, hr_api.g_varchar2)
1688 ,p_element_information3 => nvl(p_ele_proc_runtype, hr_api.g_varchar2)
1689 ,p_object_version_number => l_object_version_number
1690 );
1691 end if;
1692
1693 for c1_rec in c1 ( p_ele_name||' AT Special Features' ) loop
1694 l_at_sf_element_type_id := c1_rec.element_type_id;
1695 l_object_version_number := c1_rec.object_version_number;
1696 end loop;
1697 pay_shadow_element_api.update_shadow_element
1698 (p_validate => false
1699 ,p_effective_date => p_ele_eff_start_date
1700 ,p_element_type_id => l_at_sf_element_type_id
1701 ,p_description => 'Generated Special Features AT element for:'
1702 ||p_ele_name
1703 ,p_reporting_name => nvl(p_ele_reporting_name
1704 ,p_ele_name)||': AT SF'
1705 ,p_post_termination_rule => p_termination_rule
1706 ,p_element_information_category => 'US_VOLUNTARY DEDUCTIONS'
1707 ,p_element_information2 => nvl(p_ele_partial_deduction,hr_api.g_varchar2)
1708 ,p_element_information3 => nvl(p_ele_proc_runtype, hr_api.g_varchar2)
1709 ,p_object_version_number => l_object_version_number
1710 );
1711 end if;
1712 hr_utility.set_location(l_proc, 110);
1713 --
1714 -- Update shadow element if Roth Contribution option is selected.
1715 --
1716 if p_roth_contribution = 'Y' then
1717
1718 for c1_rec in c1 ( p_ele_name||' Roth' )
1719 loop
1720 l_atr_element_type_id := c1_rec.element_type_id;
1721 l_object_version_number := c1_rec.object_version_number;
1722 end loop;
1723 -- Bug# 4676867
1724 IF p_ele_category = 'D' THEN
1725 l_relative_processing_priority := 1425 ;
1726 l_ele_information1 := 'R401K' ;
1727 ELSIF p_ele_category = 'E' THEN
1728 l_relative_processing_priority := 1425 ;
1729 l_ele_information1 := 'R403B' ;
1730 END IF ;
1731
1732 hr_utility.set_location(l_proc, 111);
1733
1734 pay_shadow_element_api.update_shadow_element
1735 (p_validate => false
1736 ,p_effective_date => p_ele_eff_start_date
1737 ,p_element_type_id => l_atr_element_type_id
1738 ,p_reporting_name => nvl(p_ele_reporting_name
1739 ,p_ele_name)||' Roth'
1740 ,p_post_termination_rule => p_termination_rule
1741 ,p_description => 'Roth Contribution element for:'||p_ele_name
1742 ,p_element_information_category => 'US_VOLUNTARY DEDUCTIONS'
1743 ,p_skip_formula => l_skip_formula
1744 ,p_processing_type => nvl(p_ele_processing_type, hr_api.g_varchar2)
1745 ,p_relative_processing_priority => l_relative_processing_priority
1746 ,p_element_information1 => l_ele_information1
1747 ,p_element_information2 => nvl(p_ele_partial_deduction, hr_api.g_varchar2)
1748 ,p_element_information3 => nvl(p_ele_proc_runtype, hr_api.g_varchar2)
1749 ,p_object_version_number => l_object_version_number
1750 );
1751
1752 hr_utility.set_location(l_proc, 112);
1753 --
1754 -- Update the Roth AT ER element
1755 --
1756 l_temp_var(1).sub_name := ' Roth ER';
1757 l_cur_temp := '';
1758 for c1_rec in c1 ( p_ele_name||l_temp_var(1).sub_name )
1759 loop
1760 l_atr_er_element_type_id := c1_rec.element_type_id;
1761 l_object_version_number := c1_rec.object_version_number;
1762
1763 pay_shadow_element_api.update_shadow_element
1764 (p_validate => false
1765 ,p_effective_date => p_ele_eff_start_date
1766 ,p_description => 'Generated Roth ER element for:'||p_ele_name
1767 ,p_element_type_id => l_atr_er_element_type_id
1768 ,p_reporting_name => nvl(p_ele_reporting_name
1769 ,p_ele_name)||l_temp_var(1).sub_name
1770 ,p_post_termination_rule => p_termination_rule
1771 ,p_object_version_number => l_object_version_number
1772 );
1773
1774 end loop;
1775 hr_utility.set_location(l_proc, 113);
1776 --
1777 -- Update the Special Inputs element for Roth 401k
1778 --
1779 if p_ele_processing_type = 'R' then
1780 for c1_rec in c1 ( p_ele_name||' Roth SI' )
1781 loop
1782 l_atr_si_element_type_id := c1_rec.element_type_id;
1783 l_object_version_number := c1_rec.object_version_number;
1784 end loop;
1785 -- Bug# 4676867: Impact of Roth on Involuntary Deductions
1786 IF p_ele_category = 'D' THEN
1787 l_relative_processing_priority := 1405 ;
1788 ELSIF p_ele_category = 'E' THEN
1789 l_relative_processing_priority := 1405 ;
1790 END IF ;
1791
1792 pay_shadow_element_api.update_shadow_element
1793 (p_validate => false
1794 ,p_effective_date => p_ele_eff_start_date
1795 ,p_element_type_id => l_atr_si_element_type_id
1796 ,p_description => 'Generated adjustments Roth SI element for:'||p_ele_name
1797 ,p_reporting_name => nvl(p_ele_reporting_name
1798 ,p_ele_name)||' Roth SI'
1799 ,p_post_termination_rule => p_termination_rule
1800 ,p_element_information_category => 'US_VOLUNTARY DEDUCTIONS'
1801 ,p_relative_processing_priority => l_relative_processing_priority
1802 ,p_element_information2 => nvl(p_ele_partial_deduction, hr_api.g_varchar2)
1803 ,p_element_information3 => nvl(p_ele_proc_runtype, hr_api.g_varchar2)
1804 ,p_object_version_number => l_object_version_number
1805 );
1806 end if;
1807 hr_utility.set_location(l_proc, 114);
1808 --
1809 -- Update the Special Features element for Roth 401k
1810 --
1811 for c1_rec in c1 ( p_ele_name||' Roth SF' )
1812 loop
1813 l_atr_sf_element_type_id := c1_rec.element_type_id;
1814 l_object_version_number := c1_rec.object_version_number;
1815 end loop;
1816
1817 -- Bug# 4676867: Impact of Roth on Involuntary Deductions
1818 IF p_ele_category = 'D' THEN
1819 l_relative_processing_priority := 1435 ;
1820 ELSIF p_ele_category = 'E' THEN
1821 l_relative_processing_priority := 1435 ;
1822 END IF ;
1823
1824 pay_shadow_element_api.update_shadow_element
1825 (p_validate => false
1826 ,p_effective_date => p_ele_eff_start_date
1827 ,p_element_type_id => l_atr_sf_element_type_id
1828 ,p_description => 'Generated Special Features Roth SF element for:'
1829 ||p_ele_name
1830 ,p_reporting_name => nvl(p_ele_reporting_name
1831 ,p_ele_name)||' Roth SF'
1832 ,p_post_termination_rule => p_termination_rule
1833 ,p_element_information_category => 'US_VOLUNTARY DEDUCTIONS'
1834 ,p_relative_processing_priority => l_relative_processing_priority
1835 ,p_element_information2 => nvl(p_ele_partial_deduction,hr_api.g_varchar2)
1836 ,p_element_information3 => nvl(p_ele_proc_runtype, hr_api.g_varchar2)
1837 ,p_object_version_number => l_object_version_number
1838 );
1839 hr_utility.set_location(l_proc, 115);
1840 end if; -- if p_roth_contribution = 'Y'
1841
1842 hr_utility.set_location(l_proc, 120);
1843
1844 -- ========================================================================
1845 -- Generate Core Objects
1846 -- ========================================================================
1847 pay_element_template_api.generate_part1
1848 (p_validate => false
1849 ,p_effective_date => p_ele_eff_start_date
1850 ,p_hr_only => false
1851 ,p_hr_to_payroll => false
1852 ,p_template_id => l_template_id);
1853
1854 hr_utility.set_location(l_proc, 121);
1855
1856 -- ========================================================================
1857 -- Add logic to see generate part2 only if payroll is installed
1858 -- ========================================================================
1859 pay_element_template_api.generate_part2
1860 (p_validate => false
1861 ,p_effective_date => p_ele_eff_start_date
1862 ,p_template_id => l_template_id
1863 );
1864
1865 hr_utility.set_location(l_proc, 130);
1866
1867 -- ========================================================================
1868 -- Get Element Type ID and Balance Id's to update the Further Information
1869 -- ========================================================================
1870
1871 get_element_and_bal_ids;
1872
1873 --
1874 -- get the Iterative Formula Id
1875 --
1876 hr_utility.set_location(l_proc, 135);
1877 open c_iter_formula;
1878 fetch c_iter_formula into l_iter_formula_id;
1879 close c_iter_formula;
1880 --
1881 hr_utility.set_location(l_proc, 140);
1882 --
1883 -- Added for Garnishment rewrite --
1884 if p_ele_classification = l_inv_ded then
1885 update pay_element_types_f
1886 set element_information5 = l_cal_core_element_type_id,
1887 element_information8 = l_vol_ded_bal_id,
1888 element_information10 = l_pri_bal_id,
1889 element_information11 = l_accr_bal_id,
1890 --element_information12 = l_arr_bal_id, Commented for Bug 2527761, 980683
1891 --element_information13 = l_not_taken_bal_id,
1892 element_information15 = l_fee_bal_id,
1893 element_information16 = l_addl_bal_id,
1894 element_information17 = l_repl_bal_id,
1895 element_information18 = l_si_core_element_type_id
1896 --element_information19 = l_sf_core_element_type_id,
1897 --element_information20 = l_ver_core_element_type_id,
1898 where element_type_id = l_base_element_type_id
1899 and business_group_id = p_bg_id;
1900
1901 update pay_element_types_f
1902 set element_information10 = l_fee_bal_id,
1903 element_information11 = l_accr_fees_bal_id
1904 where element_type_id = l_fees_core_element_type_id
1905 and business_group_id = p_bg_id;
1906
1907 update pay_input_values_f
1908 set mandatory_flag = 'X'
1909 where name = 'Pay Value'
1910 and element_type_id in (select element_type_id
1911 from pay_element_types_f
1912 where element_name like p_ele_name ||'%');
1913 else
1914 update pay_element_types_f
1915 set element_information10 = l_pri_bal_id,
1916 element_information11 = l_accr_bal_id,
1917 element_information12 = l_arr_bal_id,
1918 element_information13 = l_not_taken_bal_id,
1919 element_information16 = l_addl_bal_id,
1920 element_information17 = l_repl_bal_id,
1921 element_information18 = l_si_core_element_type_id,
1922 element_information19 = l_sf_core_element_type_id,
1923 iterative_flag = 'Y',
1924 iterative_formula_id = l_iter_formula_id,
1925 iterative_priority = l_iter_priority
1926 where element_type_id = l_base_element_type_id
1927 and business_group_id = p_bg_id;
1928 end if;
1929 hr_utility.set_location(l_proc, 150);
1930
1931 insert_iterative_rules(l_base_element_type_id);
1932
1933 -- Get the _ASG_GRE_RUN dimension id
1934 for crec in get_asg_gre_run_dim_id
1935 loop
1936 l_asg_gre_run_dim_id := crec.balance_dimension_id;
1937 end loop;
1938
1939 update pay_defined_balances
1940 set save_run_balance = 'Y'
1941 where balance_type_id = l_pri_bal_id
1942 and balance_dimension_id = l_asg_gre_run_dim_id
1943 and business_group_id = p_bg_id;
1944
1945 hr_utility.set_location(l_proc, 170);
1946 --
1947 if p_after_tax_component = 'Y' then
1948 update pay_element_types_f
1949 set element_information10 = l_at_pri_bal_id,
1950 element_information11 = l_at_accr_bal_id,
1951 element_information12 = l_at_arr_bal_id,
1952 element_information13 = l_at_not_taken_bal_id,
1953 element_information16 = l_at_addl_bal_id,
1954 element_information17 = l_at_repl_bal_id,
1955 element_information18 = l_at_si_core_element_type_id,
1956 element_information19 = l_at_sf_core_element_type_id
1957 where element_type_id = l_at_base_element_type_id
1958 and business_group_id = p_bg_id;
1959
1960 update pay_defined_balances
1961 set save_run_balance = 'Y'
1962 where balance_type_id = l_at_pri_bal_id
1963 and balance_dimension_id = l_asg_gre_run_dim_id
1964 and business_group_id = p_bg_id;
1965 end if;
1966
1967 hr_utility.set_location(l_proc, 175);
1968 -- Update Roth element if Roth contribution is selected
1969 if p_roth_contribution = 'Y' then
1970 update pay_element_types_f
1971 set element_information10 = l_atr_pri_bal_id,
1972 element_information11 = l_atr_accr_bal_id,
1973 element_information12 = l_atr_arr_bal_id,
1974 element_information13 = l_atr_not_taken_bal_id,
1975 element_information16 = l_atr_addl_bal_id,
1976 element_information17 = l_atr_repl_bal_id,
1977 element_information18 = l_roth_si_ele_type_id,
1978 element_information19 = l_roth_sf_ele_type_id
1979 where element_type_id = l_roth_ele_type_id
1980 and business_group_id = p_bg_id;
1981
1982 update pay_defined_balances
1983 set save_run_balance = 'Y'
1984 where balance_type_id = l_atr_pri_bal_id
1985 and balance_dimension_id = l_asg_gre_run_dim_id
1986 and business_group_id = p_bg_id;
1987 -- If ER component is selected then update the Roth ER match element
1988 if p_employer_match ='Y' then
1989 update pay_element_types_f
1990 set element_information1 = 'O',
1991 element_information10 = l_atr_er_bal_id,
1992 element_information_category = 'US_EMPLOYER LIABILITIES'
1993 where element_type_id = l_roth_er_ele_type_id
1994 and business_group_id = p_bg_id;
1995
1996 update pay_defined_balances
1997 set save_run_balance = 'Y'
1998 where balance_type_id = l_atr_er_bal_id
1999 and balance_dimension_id = l_asg_gre_run_dim_id
2000 and business_group_id = p_bg_id;
2001 end if;
2002
2003 end if;
2004
2005 hr_utility.set_location(l_proc, 180);
2006 -- Update the element_types_f table with the primary
2007 -- catchup balance id for the catch-up element
2008 if NVL(p_catchup_processing,'NONE') <> 'NONE' then
2009 update pay_element_types_f
2010 set element_information10 = l_cu_pri_bal_id,
2011 element_information11 = l_cu_accrued_bal_id,
2012 element_information12 = l_cu_arrears_bal_id,
2013 element_information13 = l_cu_nottaken_bal_id,
2014 element_information16 = l_cu_addl_bal_id,
2015 element_information17 = l_cu_repl_bal_id,
2016 element_information18 = l_cu_si_element_type_id,
2017 element_information19 = l_cu_sf_element_type_id,
2018 iterative_flag = 'Y',
2019 iterative_formula_id = l_iter_formula_id,
2020 iterative_priority = (l_iter_priority - 5)
2021 where element_type_id = l_cu_element_type_id
2022 and business_group_id = p_bg_id;
2023
2024 insert_iterative_rules(l_cu_element_type_id);
2025 update pay_defined_balances
2026 set save_run_balance = 'Y'
2027 where balance_type_id = l_cu_pri_bal_id
2028 and balance_dimension_id = l_asg_gre_run_dim_id
2029 and business_group_id = p_bg_id;
2030 end if;
2031 hr_utility.set_location(l_proc, 190);
2032 -- Update the element_types_f table with the primary
2033 -- Buy Back balance id for the Buy-back element
2034 if NVL(p_ele_srs_buy_back,'N') <> 'N' then
2035 update pay_element_types_f
2036 set element_information10 = l_bb_pri_bal_id,
2037 element_information11 = l_bb_accrued_bal_id,
2038 element_information12 = l_bb_arrears_bal_id,
2039 element_information13 = l_bb_nottaken_bal_id,
2040 element_information16 = l_bb_addl_bal_id,
2041 element_information17 = l_bb_repl_bal_id,
2042 element_information18 = l_bb_si_element_type_id,
2043 element_information19 = l_bb_sf_element_type_id,
2044 iterative_flag = 'Y',
2045 iterative_formula_id = l_iter_formula_id,
2046 iterative_priority = (l_iter_priority - 5)
2047 where element_type_id = l_bb_element_type_id
2048 and business_group_id = p_bg_id;
2049
2050 insert_iterative_rules(l_bb_element_type_id);
2051
2052 update pay_defined_balances
2053 set save_run_balance = 'Y'
2054 where balance_type_id = l_bb_pri_bal_id
2055 and balance_dimension_id = l_asg_gre_run_dim_id
2056 and business_group_id = p_bg_id;
2057 end if;
2058 hr_utility.set_location(l_proc, 200);
2059 -- Update the element_types_f table with the primary
2060 -- ER Contribution balance id for the ER Contr element
2061 if nvl(p_ele_srs_plan_type,'N') <> 'N' then
2062 update pay_element_types_f
2063 set element_information10 = l_er_contr_pri_bal_id,
2064 element_information_category = 'US_EMPLOYER LIABILITIES'
2065 where element_type_id = l_er_contr_element_type_id
2066 and business_group_id = p_bg_id;
2067 end if;
2068 hr_utility.set_location(l_proc, 210);
2069 -- For SRS type Base element
2070 if nvl(p_ele_srs_plan_type,'N') <> 'N' then
2071
2072 pay_element_extra_info_api.create_element_extra_info
2073 (p_element_type_id => l_base_element_type_id
2074 ,p_information_type => 'PQP_US_SRS_DEDUCTIONS'
2075 ,p_eei_information_category => 'PQP_US_SRS_DEDUCTIONS'
2076 ,p_eei_information4 => p_ele_srs_plan_type
2077 ,p_eei_information5 => p_ele_srs_buy_back
2078 ,p_element_type_extra_info_id => l_srs_etei_id
2079 ,p_object_version_number => l_srs_etei_ovn
2080 );
2081 end if;
2082 hr_utility.set_location(l_proc, 220);
2083 -- For ER Contribution Element
2084 if nvl(p_ele_srs_plan_type,'N') <> 'N' then
2085
2086 pay_element_extra_info_api.create_element_extra_info
2087 (p_element_type_id => l_er_contr_element_type_id
2088 ,p_information_type => 'PQP_US_SRS_DEDUCTIONS'
2089 ,p_eei_information_category => 'PQP_US_SRS_DEDUCTIONS'
2090 ,p_eei_information4 => p_ele_srs_plan_type
2091 ,p_eei_information5 => p_ele_srs_buy_back
2092 ,p_element_type_extra_info_id => l_srs_etei_id
2093 ,p_object_version_number => l_srs_etei_ovn);
2094 end if;
2095 -- For Buy Back Element
2096 if nvl(p_ele_srs_plan_type,'N') <> 'N' and
2097 p_ele_srs_buy_back = 'Y' then
2098
2099 pay_element_extra_info_api.create_element_extra_info
2100 (p_element_type_id => l_bb_element_type_id
2101 ,p_information_type => 'PQP_US_SRS_DEDUCTIONS'
2102 ,p_eei_information_category => 'PQP_US_SRS_DEDUCTIONS'
2103 ,p_eei_information4 => p_ele_srs_plan_type
2104 ,p_eei_information5 => p_ele_srs_buy_back
2105 ,p_element_type_extra_info_id => l_srs_etei_id
2106 ,p_object_version_number => l_srs_etei_ovn
2107 );
2108 end if;
2109 hr_utility.set_location(l_proc, 220);
2110 --
2111 -- Update further info for the ER and AT ER elements
2112 --
2113 if p_employer_match = 'Y' then
2114 update pay_element_types_f
2115 set element_information1 = 'O',
2116 element_information10 = l_er_bal_id,
2117 element_information_category = 'US_EMPLOYER LIABILITIES'
2118 where element_type_id = l_er_element_type_id
2119 and business_group_id = p_bg_id;
2120 --
2121 if p_after_tax_component = 'Y' then
2122 update pay_element_types_f
2123 set element_information1 = 'O',
2124 element_information10 = l_at_er_bal_id,
2125 element_information_category = 'US_EMPLOYER LIABILITIES'
2126 where element_type_id = l_at_er_element_type_id
2127 and business_group_id = p_bg_id;
2128 end if;
2129 --
2130 update pay_defined_balances
2131 set save_run_balance = 'Y'
2132 where balance_type_id = l_er_bal_id
2133 and balance_dimension_id = l_asg_gre_run_dim_id
2134 and business_group_id = p_bg_id;
2135
2136 update pay_defined_balances
2137 set save_run_balance = 'Y'
2138 where balance_type_id = l_at_er_bal_id
2139 and balance_dimension_id = l_asg_gre_run_dim_id
2140 and business_group_id = p_bg_id;
2141
2142 end if;
2143 hr_utility.set_location(l_proc, 230);
2144 --
2145 -- Create Information Type for Catch-Up Processing
2146 -- If p_catchup_processing is not null then should be checking for NONE
2147 --
2148 if nvl(p_catchup_processing ,'NONE') <> 'NONE' then
2149
2150 pay_element_extra_info_api.create_element_extra_info
2151 (p_element_type_id => l_base_element_type_id
2152 ,p_information_type => 'PQP_US_PRE_TAX_DEDUCTIONS'
2153 ,p_eei_information_category => 'PQP_US_PRE_TAX_DEDUCTIONS'
2154 ,p_eei_information3 => p_catchup_processing
2155 ,p_element_type_extra_info_id => l_etei_id
2156 ,p_object_version_number => l_etei_ovn);
2157
2158 end if;
2159 --
2160 hr_utility.set_location(l_proc, 240);
2161 if nvl(p_roth_contribution ,'N') <> 'N' and
2162 l_roth_ele_type_id is not null then
2163
2164 pay_element_extra_info_api.create_element_extra_info
2165 (p_element_type_id => l_roth_ele_type_id
2166 ,p_information_type => 'PAY_US_ROTH_OPTIONS'
2167 ,p_eei_information_category => 'PAY_US_ROTH_OPTIONS'
2168 ,p_eei_information4 => 'Y'
2169 ,p_eei_information6 => 'N'
2170 ,p_element_type_extra_info_id => l_etei_id
2171 ,p_object_version_number => l_etei_ovn);
2172
2173 end if;
2174 hr_utility.set_location(l_proc, 245);
2175
2176 -- Make Pay Value as non user-enterable field for all the elements
2177 -- created under this pre-tax deduction plan.
2178 for i in csr_ele(c_effective_date => p_ele_eff_start_date
2179 ,c_ele_prefix => p_ele_name )
2180 loop
2181 update pay_input_values_f piv
2182 set piv.mandatory_flag = 'X'
2183 where piv.input_value_id = i.input_value_id
2184 and piv.element_type_id = i.element_type_id
2185 and piv.business_group_id = p_bg_id;
2186 end loop;
2187 -- ======================================================
2188 -- Create the balance feeds for the eligible comp balance
2189 -- ======================================================
2190
2191 create_eligible_comp_bal_feeds;
2192 --
2193 hr_utility.set_location('Leaving: '||l_proc, 250);
2194 return l_base_element_type_id;
2195 --
2196 end create_user_init_template;
2197 --
2198 -- =============================================================================
2199 -- delete_user_init_template: Delete User Created template.
2200 -- =============================================================================
2201 procedure delete_user_init_template
2202 (p_business_group_id in number
2203 ,p_ele_type_id in number
2204 ,p_ele_name in varchar2
2205 ,p_effective_date in date
2206 ) is
2207 --
2208 l_template_id number(9);
2209 l_proc varchar2(200);
2210 --
2211 cursor c1 is
2212 select template_id
2213 from pay_element_templates
2214 where base_name = p_ele_name
2215 and business_group_id = p_business_group_id
2216 and template_type = 'U';
2217 --
2218 begin
2219 --
2220 l_proc := g_proc||'.delete_user_init_template';
2221 hr_utility.set_location('Entering :'||l_proc, 10);
2222 --
2223 for c1_rec in c1 loop
2224 l_template_id := c1_rec.template_id;
2225 end loop;
2226 --
2227 pay_element_template_api.delete_user_structure
2228 (p_validate => false
2229 ,p_drop_formula_packages => true
2230 ,p_template_id => l_template_id
2231 );
2232 --
2233 hr_utility.set_location('Leaving :'||l_proc, 50);
2234 --
2235 end delete_user_init_template;
2236 --
2237 end pay_us_user_init_dedn;