[Home] [Help]
PACKAGE BODY: APPS.HR_GENERATE_PRETAX
Source
1 PACKAGE BODY hr_generate_pretax AS
2 /* $Header: pygenptx.pkb 115.16 2002/12/28 01:15:16 meshah ship $ */
3
4 /*
5 +======================================================================+
6 | Copyright (c) 1993 Oracle Corporation |
7 | Redwood Shores, California, USA |
8 | All rights reserved. |
9 +======================================================================+
10
11 Name : hr_generate_pretax
12 Filename : pygenptx.pkb
13
14 Change List
15 -----------
16 Date Name Vers Bug No Description
17 ----------- ---- ---- ------ -----------
18 07-JUN-96 H.Parichabutr 40.0 Created.
19 22-JUL-1996 HParicha 40.1 373543. Removed comment from exit...
20 25-JUL-1996 hparicha 40.2 Changes required during SWAMP QA.
21 Added category feeds from calculator
22 element pay value.
23 40.3 Changed handling of call to spr_exists.
24 13-AUG-1996 hparicha 40.4 No longer sets ben class id on
25 Calculator ele...affects link screen.
26 14-AUG-1996 hparicha 40.5 Added new variable for l_upgrade_mode.
27 Upgrade mode is implied by existence
28 of the base element.
29 Now calls new API to create
30 link_input_values,
31 element_entry_values,
32 and run_result_values if template
33 is being upgraded.
34 Also, added feeds from Cancel Calc Amount
35 to same feeds as base pay value - ie. cat feeds.
36 Also, Coverage iv for benefits is no longer mandatory.
37 ..-SEP-1996 hparicha 40.6 373543 - again...rework of solution
38 such that 2 separate element entries
39 are not required for processing of
40 pre-tax deductions.
41
42 3rd Oct 1996 hparicha 40.7 398791 - Passing new parameter
43 to hr_template_existence functions.
44 which now uses start date of element
45 in existence comparisons.
46
47 4th Nov 1996 hparicha 40.8 Fixes to base input values creation.
48 Names and l_num_base_ivs not set
49 correctly for start rule inputs.
50 Found and corrected during
51 customer upgrade of ptx dedns
52 in Megapatch 9.
53
54 6th Nov 1996 hparicha 40.9 413211. Minor fixes concerning
55 deletion of ptx dedns...namely,
56 ensuring legislation code on
57 formula record is null (not US).
58 14 Nov 1996 hparicha 40.10 419766 Withholding element is nonrecurring
59 and needed std link flag set to No,
60 instead of using param for std link
61 for base element.
62 04 Jan 1997 tzlacey 40.11 Removed line between create package
63 and header.
64 21 Jan 1997 hparicha 40.12 Making changes for new pretax
65 configuration (M9 cleanup).
66 10 Jul 1997 mmukherj 40.15 502307 Updated do_defined_balances
67 procedure.Included business_group_id
68 in where condition while checking
69 pay_defined_balnce has already exist
70 for that balance_name for that busines_
71 group_id or not
72 21 Jul 1997 mmukherj 40.16 Added some comments in do_defined_balances
73 procedure related to Bug no 502307. No
74 other change in the code. Changed the
75 select statement of the same proceduer
76 to avoid using index on
77 business_group_id.
78 30 Apr 1998 pmadore 40.17 Added additonal input values, formula
79 result rules, elements, and balances to
80 support the Employer match components
81 of a pretax deduction in category of
82 Deferred Comp 401k.
83 The logic to create these objects
84 depends upon the values of a new
85 parameter to the main package function:
86 p_ele_er_match
87 10 Mar 1999 ahanda 40.19 Changed GRE_ITD to ASG_ITD as GRE_ITD
88 is already there in suffixes(26) and
89 ASG_ITD is missing in the
90 pretax_deduction_template. Bug 820068
91 16-jun-1999 achauhan 110.10 Replaced dbms_output with hr_utility
92 09-jul-1999 vmehta 110.11 Added check for legislation_code
93 while retrieving classification for
94 employer match BUG 912994
95 27-oct-1999 dscully Added check for legislation_code while
96 looking up skip rules
97 12-Jul-2000 kthirmiy 110.14 Added ELEMENT_INFORMATION_CATEGORY=
98 'US_EMPLOYER LIABILITIES'
99 while updating PAY_ELEMENT_TYPES_F for
100 pretax ER element to show the desc flex
101 field in the element description screen
102 for pretax ER element
103 *******************************************************************************
104 22-JAN-2002 ahanda 115.13 Added call to create defined bal for
105 Assignment Payments.
106 23-DEC-2002 tclewis 115.15 11.5.9 performance fixes and inspected
107 file to add nocopy directive. I found
108 no procedures requireing it.
109 27-DEC-2002 meshah 115.16 fixed gscc warnings/errors.
110 *******************************************************************************/
111
112 /*
113 This package contains calls to core API used to insert records comprising an
114 entire pretax deduction template. Migration to published (ie. supported) api
115 from core is an essential move when these become available.
116
117 The procedures responsible for creating
118 appropriate records based on data entered on the Deductions form
119 must perform simple logic to determine the exact attributes required for the
120 deductions template. We do this to keep extraneous information
121 to a minimum - especially regarding input values and formula
122 result rules. Attributes (and their determining factors) are:
123
124 - skip rules (Classification)
125 - status processing rules (Calculation Method)
126 - input values (Classification/Category, Calculation Method)
127 - formula result rules (Calculation Method)
128 */
129
130 --
131 ------------------------- upgrade_deduction_template ------------------------
132 --
133
134 FUNCTION pretax_deduction_template (
135 p_ele_name in varchar2,
136 p_ele_reporting_name in varchar2,
137 p_ele_description in varchar2 default NULL,
138 p_ele_classification in varchar2,
139 p_ben_class_id in number,
140 p_ele_category in varchar2 default NULL,
141 p_ele_processing_type in varchar2,
142 p_ele_priority in number default NULL,
143 p_ele_standard_link in varchar2 default 'N',
144 p_ele_proc_runtype in varchar2,
145 p_ele_start_rule in varchar2,
146 p_ele_stop_rule in varchar2,
147 p_ele_ee_bond in varchar2 default 'N',
148 p_ele_amount_rule in varchar2,
149 p_ele_paytab_name in varchar2 default NULL,
150 p_ele_paytab_col in varchar2 default NULL,
151 p_ele_paytab_row_type in varchar2 default NULL,
152 p_ele_arrearage in varchar2 default 'N',
153 p_ele_partial_dedn in varchar2 default 'N',
154 p_mix_flag in varchar2 default NULL,
155 p_ele_er_match in varchar2 default 'N',
156 p_ele_eff_start_date in date default NULL,
157 p_ele_eff_end_date in date default NULL,
158 p_bg_id in number) RETURN NUMBER IS
159
160 -- global vars
161
162 TYPE text_table IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
163 TYPE num_table IS TABLE OF NUMBER(9) INDEX BY BINARY_INTEGER;
164
165 g_eff_start_date date;
166 g_eff_end_date date;
167
168 g_invol_class_name varchar2(80) := 'INVOLUNTARY DEDUCTIONS';
169 g_arrears_contr_inpval_id NUMBER(9);
170 g_adj_arrears_inpval_id NUMBER(9);
171 g_to_tot_inpval_id NUMBER(9);
172 g_topurch_inpval_id NUMBER(9);
173 g_ele_info_cat VARCHAR2(30);
174
175 dedn_iv_seq number;
176 dedn_base_seq number;
177 dedn_wh_seq number;
178 dedn_er_seq number;
179
180
181 dedn_ele_names text_table;
182 dedn_ele_repnames text_table;
183 dedn_ele_class text_table;
184 dedn_ele_cat text_table;
185 dedn_ele_proc_type text_table;
186 dedn_ele_desc text_table;
187 dedn_ele_priority num_table;
188 dedn_indirect_only text_table;
189 dedn_ele_start_rule text_table;
190 dedn_mix_category text_table;
191 dedn_ot_base text_table;
192 dedn_std_link text_table;
193 dedn_pay_formula text_table;
194 dedn_skip_formula text_table;
195 dedn_ele_ids num_table;
196 dedn_statproc_rule_id num_table;
197 dedn_third_party_pay text_table;
198 dedn_payval_id num_table;
199
200 dedn_wh_frr_name text_table;
201 dedn_wh_frr_type text_table;
202 dedn_wh_frr_ele_id num_table;
203 dedn_wh_frr_iv_id num_table;
204 dedn_wh_frr_severity text_table;
205
206 dedn_calc_frr_name text_table;
207 dedn_calc_frr_type text_table;
208 dedn_calc_frr_ele_id num_table;
209 dedn_calc_frr_iv_id num_table;
210 dedn_calc_frr_severity text_table;
211
212 dedn_base_feed_iv_id num_table;
213 dedn_base_feed_bal_id num_table;
214
215 dedn_si_feed_iv_id num_table;
216 dedn_si_feed_bal_id num_table;
217
218 dedn_sf_feed_iv_id num_table;
219 dedn_sf_feed_bal_id num_table;
220
221 l_num_wh_resrules number;
222 l_num_calc_resrules number;
223
224 l_num_base_feeds number;
225 l_num_si_feeds number;
226 l_num_sf_feeds number;
227 l_num_er_feeds number;
228
229
230 dedn_assoc_bal_names text_table;
231 dedn_assoc_bal_rep_names text_table;
232 dedn_assoc_bal_uom text_table;
233 dedn_assoc_bal_ids num_table;
234
235 dedn_base_iv_names text_table;
236 dedn_base_iv_seq num_table;
237 dedn_base_iv_mand text_table;
238 dedn_base_iv_uom text_table;
239 dedn_base_iv_dbi text_table;
240 dedn_base_iv_lkp text_table;
241 dedn_base_iv_dflt text_table;
242 dedn_base_iv_ids num_table;
243
244 dedn_wh_iv_names text_table;
245 dedn_wh_iv_seq num_table;
246 dedn_wh_iv_mand text_table;
247 dedn_wh_iv_uom text_table;
248 dedn_wh_iv_dbi text_table;
249 dedn_wh_iv_lkp text_table;
250 dedn_wh_iv_dflt text_table;
251 dedn_wh_iv_ids num_table;
252
253 dedn_si_iv_names text_table;
254 dedn_si_iv_seq num_table;
255 dedn_si_iv_mand text_table;
256 dedn_si_iv_uom text_table;
257 dedn_si_iv_dbi text_table;
258 dedn_si_iv_lkp text_table;
259 dedn_si_iv_dflt text_table;
260 dedn_si_iv_ids num_table;
261
262 dedn_sf_iv_names text_table;
263 dedn_sf_iv_seq num_table;
264 dedn_sf_iv_mand text_table;
265 dedn_sf_iv_uom text_table;
266 dedn_sf_iv_dbi text_table;
267 dedn_sf_iv_lkp text_table;
268 dedn_sf_iv_dflt text_table;
269 dedn_sf_iv_ids num_table;
270
271 dedn_er_iv_names text_table;
272 dedn_er_iv_seq num_table;
273 dedn_er_iv_mand text_table;
274 dedn_er_iv_uom text_table;
275 dedn_er_iv_dbi text_table;
276 dedn_er_iv_lkp text_table;
277 dedn_er_iv_dflt text_table;
278 dedn_er_iv_ids num_table;
279
280
281 l_num_eles number;
282
283 l_num_assoc_bals number;
284
285 l_num_base_ivs number;
286 l_num_wh_ivs number;
287 l_num_si_ivs number;
288 l_num_sf_ivs number;
289 l_num_er_ivs number;
290
291
292 h number;
293 x number;
294 i number;
295 k number;
296 m number;
297 n number;
298 o number;
299 p number;
300 q number;
301 r number;
302 s number;
303 t number;
304 c number;
305 vf number;
306 sif number;
307 scf number;
308 sf number;
309 siv number;
310 sfv number;
311 l number;
312
313
314 already_exists number;
315
316 -- local constants
317
318 c_end_of_time CONSTANT DATE := TO_DATE('31/12/4712','DD/MM/YYYY');
319
320 -- local vars
321
322 v_bg_name VARCHAR2(60); -- Get from bg short name passed in.
323 v_ele_type_id NUMBER(9); -- insertion of element type.
324 v_primary_class_id NUMBER(9);
325 v_class_lo_priority NUMBER(9);
326 v_class_hi_priority NUMBER(9);
327 v_shadow_ele_type_id NUMBER(9); -- Populated by insertion of element type.
328 v_shadow_ele_name VARCHAR2(80); -- Name of shadow element type.
329 v_inputs_ele_type_id NUMBER(9); -- Populated by insertion of element type.
330 v_inputs_ele_name VARCHAR2(80); -- Name of shadow element type.
331 v_ele_repname VARCHAR2(30);
332 v_bal_type_id NUMBER(9); -- Pop'd by insertion of balance type.
333 v_dedn_bal_uom VARCHAR2(30) := 'M';
334 v_balance_name VARCHAR2(80); -- Additional balances req'd by dedn.
335 v_bal_rpt_name VARCHAR2(30);
336 v_bal_dim VARCHAR2(80);
337 v_inpval_id NUMBER(9);
338 v_payval_id NUMBER(9); -- ID of payval for bal feed insert.
339 v_payval_name VARCHAR2(80); -- Name of payval.
340 v_pay_value_name VARCHAR2(80); -- Name of payval for this legislation.
341 v_shadow_info_payval_id NUMBER(9);
342 v_inputs_info_payval_id NUMBER(9);
343 v_payval_formula_id NUMBER(9); -- ID of formula for payvalue validation.
344 v_totowed_bal_type_id NUMBER(9);
345 v_eepurch_bal_type_id NUMBER(9);
346 v_arrears_bal_type_id NUMBER(9);
347 v_notaken_bal_type_id NUMBER(9);
348 v_able_bal_type_id NUMBER(9);
349 v_sect125_bal_type_id NUMBER(9);
350 v_401k_bal_type_id NUMBER(9);
351 v_topurch_eletype_id NUMBER(9);
352 v_er_charge_eletype_id NUMBER(9);
353 v_er_charge_baltype_id NUMBER(9);
354 v_er_charge_payval_id NUMBER(9); -- inpval id of ER charge PAY VALUE.
355 v_topurch_ele_name VARCHAR2(80);
356 v_er_charge_ele_name VARCHAR2(80);
357 v_skip_formula_id NUMBER(9);
358
359 l_invol_dflt_prio number(9);
360 l_wh_ele_priority number(9);
361 v_emp_liab_dflt_prio number(9);
362
363 l_iv_defaults_ff_text varchar2(32000);
364 l_calc_dedn_ff_text varchar2(32000);
365 l_placeholder_ele_name varchar2(80);
366
367 l_upgrade_mode varchar2(1) := 'N';
368
369 l_reg_earn_classification_id number(9);
370 l_reg_earn_business_group_id number(15);
371 l_reg_earn_legislation_code varchar2(30);
372 l_reg_earn_balance_type_id number(9);
373 l_reg_earn_input_value_id number(9);
374 l_reg_earn_scale number(5);
375 l_reg_earn_element_type_id number(9);
376
377 cursor get_reg_earn_feeds(p_bg_id number) is
378 SELECT /*+ no_merge(pbf) */
379 bc.CLASSIFICATION_ID, pbf.BUSINESS_GROUP_ID,
380 pbf.LEGISLATION_CODE, pbf.BALANCE_TYPE_ID,
381 pbf.INPUT_VALUE_ID, pbf.SCALE, pbf.ELEMENT_TYPE_ID
382 FROM PAY_BALANCE_FEEDS_V pbf,
383 pay_balance_classifications bc
384 WHERE NVL(pbf.BALANCE_INITIALIZATION_FLAG,'N') = 'N'
385 AND ((pbf.BUSINESS_GROUP_ID IS NULL OR pbf.BUSINESS_GROUP_ID = p_bg_id)
386 AND (pbf.LEGISLATION_CODE IS NULL OR pbf.LEGISLATION_CODE = 'US'))
387 and (pbf.BALANCE_NAME = 'Regular Earnings')
388 and bc.balance_type_id = pbf.balance_type_id
389 order by pbf.element_name;
390
391 PROCEDURE do_defined_balances ( p_bal_name IN VARCHAR2,
392 p_bg_name IN VARCHAR2,
393 p_no_payments IN BOOLEAN default FALSE) IS
394
395 -- local vars
396
397 TYPE text_table IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
398
399 suffixes text_table;
400 dim_id number(9);
401 dim_name varchar2(80);
402 num_suffixes number;
403
404 already_exists number;
405 v_business_group_id number;
406
407 BEGIN
408
409 hr_utility.set_location('hr_generate_pretax.do_defined_balances ',10);
410
411 suffixes(1) := '_ASG_RUN';
412 suffixes(2) := '_ASG_PTD';
413 suffixes(3) := '_ASG_MONTH';
414 suffixes(4) := '_ASG_QTD';
415 suffixes(5) := '_ASG_YTD';
416 suffixes(6) := '_ASG_GRE_RUN';
417 suffixes(7) := '_ASG_GRE_PTD';
418 suffixes(8) := '_ASG_GRE_MONTH';
419 suffixes(9) := '_ASG_GRE_QTD';
420 suffixes(10) := '_ASG_GRE_YTD';
421
422 suffixes(11) := '_PER_RUN';
423 suffixes(12) := '_PER_MONTH';
424 suffixes(13) := '_PER_QTD';
425 suffixes(14) := '_PER_YTD';
426 suffixes(15) := '_PER_GRE_RUN';
427 suffixes(16) := '_PER_GRE_MONTH';
428 suffixes(17) := '_PER_GRE_QTD';
429 suffixes(18) := '_PER_GRE_YTD';
430
431 suffixes(19) := '_PAYMENTS';
432
433 suffixes(20) := '_ASG_GRE_LTD';
434 suffixes(21) := '_ASG_LTD';
435
436 suffixes(22) := '_PER_GRE_LTD';
437 suffixes(23) := '_PER_LTD';
438
439 /* WWBug 133133 start */
440
441 /* Add defbals required for company level, summary reporting. */
442
443 suffixes(24) := '_GRE_RUN';
444 suffixes(25) := '_GRE_YTD';
445 suffixes(26) := '_GRE_ITD';
446
447 /* WWBug 350540 start */
448 /* Need defbals on arrears bal for ASG_GRE_ITD and GRE_ITD. */
449
450 suffixes(27) := '_ASG_GRE_ITD';
451
452 /* Changed GRE_ITD to ASG_ITD as GRE_ITD is already there in
453 suffixes(26) and ASG_ITD is missing. Bug 820068 */
454
455 suffixes(28) := '_ASG_ITD';
456 suffixes(29) := '_ASG_PAYMENTS';
457
458 num_suffixes := 29;
459
460 select business_group_id
461 into v_business_group_id
462 from per_business_groups
463 where upper(name) = upper(p_bg_name);
464
465 /* WWBug 133133, 350540 finish */
466
467 for i in 1..num_suffixes loop
468
469 hr_utility.set_location('hr_generate_pretax.do_defined_balances ',20);
470
471 select dimension_name, balance_dimension_id
472 into dim_name, dim_id
473 from pay_balance_dimensions
474 where database_item_suffix = suffixes(i)
475 and legislation_code = g_template_leg_code
476 and business_group_id IS NULL;
477
478 hr_utility.set_location('hr_generate_pretax.do_defined_balances ',30);
479
480 /* added line to include business_group_id in the where clause of the select
481 statement below. So that it checkes the existence of data for a the given
482 business_group_id Bug No: 502307.
483 */
484 SELECT count(0)
485 INTO already_exists
486 FROM pay_defined_balances db,
487 pay_balance_types bt
488 WHERE db.balance_type_id = bt.balance_type_id
489 AND upper(bt.balance_name) = upper(p_bal_name)
490 AND bt.business_group_id + 0 = v_business_group_id
491 AND db.balance_dimension_id = dim_id;
492
493 if (already_exists = 0) then
494
495 IF p_no_payments = TRUE and suffixes(i) = '_PAYMENTS' THEN
496
497 hr_utility.set_location('hr_generate_pretax.do_defined_balances ',40);
498
499 NULL;
500
501 ELSE
502
503 hr_utility.set_location('hr_generate_pretax.do_defined_balances ',50);
504
505 pay_db_pay_setup.create_defined_balance(
506 p_balance_name => p_bal_name,
507 p_balance_dimension => dim_name,
508 p_business_group_name => p_bg_name,
509 p_legislation_code => NULL);
510
511 END IF;
512
513 end if;
514
515 end loop;
516
517 hr_utility.set_location('hr_generate_pretax.do_defined_balances ',60);
518
519 END do_defined_balances;
520
521 --
522 ---------------------------- ins_dedn_ele_type -------------------------------
523 --
524
525 FUNCTION ins_dedn_ele_type ( p_ele_name in varchar2,
526 p_ele_reporting_name in varchar2,
527 p_ele_description in varchar2,
528 p_ele_class in varchar2,
529 p_ele_category in varchar2,
530 p_ele_start_rule in varchar2,
531 p_ele_processing_type in varchar2,
532 p_ele_priority in number,
533 p_ele_standard_link in varchar2,
534 p_skip_formula_id in number default NULL,
535 p_ind_only_flag in varchar2,
536 p_ele_eff_start_date in date,
537 p_ele_eff_end_date in date,
538 p_bg_name in varchar2,
539 p_bg_id in number) RETURN number IS
540
541 -- local vars
542
543
544 ret NUMBER;
545 v_mult_entries_allowed VARCHAR2(1) := 'N';
546 v_third_ppm VARCHAR2(30) := 'N';
547
548 already_exists number;
549
550 BEGIN
551
552 hr_utility.set_location('hr_generate_pretax.ins_dedn_ele_type',10);
553
554 IF p_ele_processing_type = 'N' THEN
555
556 hr_utility.set_location('hr_generate_pretax.ins_dedn_ele_type',20);
557
558 v_mult_entries_allowed := 'Y';
559
560 END IF;
561
562 hr_utility.set_location('hr_generate_pretax.ins_dedn_ele_type',50);
563
564 already_exists := hr_template_existence.ele_exists(
565 p_ele_name => p_ele_name,
566 p_bg_id => p_bg_id,
567 p_eff_date => p_ele_eff_start_date);
568
569 if already_exists = 0 then
570
571 hr_utility.set_location('hr_generate_pretax.ins_dedn_ele_type',55);
572
573 ret := pay_db_pay_setup.create_element(
574 p_element_name => p_ele_name,
575 p_description => p_ele_description,
576 p_classification_name => p_ele_class,
577 p_post_termination_rule => 'Final Close',
578 p_reporting_name => p_ele_reporting_name,
579 p_processing_type => p_ele_processing_type,
580 p_mult_entries_allowed => v_mult_entries_allowed,
581 p_indirect_only_flag => p_ind_only_flag,
582 p_formula_id => p_skip_formula_id,
583 p_processing_priority => p_ele_priority,
584 p_standard_link_flag => p_ele_standard_link,
585 p_business_group_name => p_bg_name,
586 p_effective_start_date => p_ele_eff_start_date,
587 p_effective_end_date => p_ele_eff_end_date,
588 p_legislation_code => NULL,
589 p_third_party_pay_only => v_third_ppm);
590
591 hr_utility.set_location('hr_generate_pretax.ins_dedn_ele_type',80);
592
593 else
594
595 hr_utility.set_location('hr_generate_pretax.ins_dedn_ele_type',90);
596
597 if p_ele_name = dedn_ele_names(1) then
598
599 -- Base element already exists, this MUST be called via upgrade mechanism.
600 -- Set upgrade mode flag for addition of input values, link input values, entry values,
601 -- and run result values.
602
603 l_upgrade_mode := 'Y';
604
605 end if;
606
607 ret := already_exists;
608
609 end if;
610
611 hr_utility.set_location('hr_generate_pretax.ins_dedn_ele_type',100);
612
613 RETURN ret;
614
615 EXCEPTION WHEN NO_DATA_FOUND THEN
616
617 hr_utility.set_location('hr_generate_pretax.ins_dedn_ele_type',999);
618
619 RETURN ret;
620
621 END ins_dedn_ele_type;
622
623
624 --
625 ------------------------- ins_base_formula -----------------------
626 --
627 FUNCTION ins_base_formula ( p_ff_ele_name in varchar2,
628 p_spr_ele_name in varchar2,
629 p_ff_desc in varchar2,
630 p_ff_bg_id in number)
631 RETURN NUMBER IS
632
633 -- Note, the ff_ele_name is used for ele name placeholder substitution in formula text...
634 -- ie. the base element name...while the spr ele name is the element this formula
635 -- will be attached to...
636
637 v_formula_id number; -- Return var.
638 v_skeleton_formula_text VARCHAR2(32000);
639 v_skeleton_formula_type_id NUMBER(9);
640 v_orig_ele_formula_id NUMBER(9);
641 v_orig_ele_formula_name VARCHAR2(80);
642 v_orig_ele_formula_text varchar2(32000);
643 v_new_ele_formula_id NUMBER(9);
644 v_new_ele_formula_name VARCHAR2(80);
645 v_new_ele_formula_text VARCHAR2(32000);
646 v_ele_name VARCHAR2(80);
647 v_new_ele_name varchar2(80);
648
649 l_placehold_ele_name varchar2(80);
650 l_count_already number;
651 already_exists number;
652
653 BEGIN
654
655 hr_utility.set_location('hr_generate_pretax.ins_base_formula',10);
656
657 SELECT FF.formula_text,
658 FF.formula_type_id
659 INTO v_skeleton_formula_text,
660 v_skeleton_formula_type_id
661 FROM ff_formulas_f FF
662 WHERE FF.formula_name = 'PRETAX_WITHHOLDING_FORMULA'
663 AND FF.business_group_id IS NULL
664 AND FF.legislation_code = 'US'
665 AND g_eff_start_date >= FF.effective_start_date
666 AND g_eff_start_date <= FF.effective_end_date;
667
668 -- Replace element name placeholders with current element name:
669
670 hr_utility.set_location('hr_generate_pretax.ins_base_formula',15);
671
672 l_placehold_ele_name := REPLACE(LTRIM(RTRIM(UPPER(p_ff_ele_name))),' ','_');
673 v_new_ele_formula_name := REPLACE(LTRIM(RTRIM(UPPER(p_ff_ele_name))),' ','_');
674 v_new_ele_formula_text := REPLACE( v_skeleton_formula_text,
675 '<ELE_NAME>',
676 v_new_ele_formula_name);
677
678 v_new_ele_formula_name := v_new_ele_formula_name || '_WITHHOLDING';
679 v_new_ele_formula_name := SUBSTR(v_new_ele_formula_name, 1, 80);
680
681 -- Call function to check existence of formula to get id.
682 -- Get original formula id, name, and text for this element currently,
683 -- ie. before putting in new ff text.
684
685 hr_utility.set_location('hr_generate_pretax.ins_base_formula',20);
686
687 already_exists := hr_template_existence.ele_ff_exists(
688 p_ele_name => p_spr_ele_name,
689 p_bg_id => p_ff_bg_id,
690 p_ff_name => v_orig_ele_formula_name,
691 p_ff_text => v_orig_ele_formula_text,
692 p_eff_date => g_eff_start_date);
693
694 if already_exists = 0 then
695
696 -- Insert the new formula text into current business group since
697 -- there is no formula for this element currently.
698 --
699 -- Get new id for formula
700
701 hr_utility.set_location('hr_generate_pretax.ins_base_formula',30);
702
703 SELECT ff_formulas_s.nextval
704 INTO v_new_ele_formula_id
705 FROM sys.dual;
706
707 hr_utility.set_location('hr_generate_pretax.ins_base_formula',40);
708
709 -- hr_utility.trace('Inserting ff '||v_new_ele_formula_name||' for ele '||p_ff_ele_name);
710
711 INSERT INTO ff_formulas_f ( FORMULA_ID,
712 EFFECTIVE_START_DATE,
713 EFFECTIVE_END_DATE,
714 BUSINESS_GROUP_ID,
715 LEGISLATION_CODE,
716 FORMULA_TYPE_ID,
717 FORMULA_NAME,
718 DESCRIPTION,
719 FORMULA_TEXT,
720 STICKY_FLAG,
721 LAST_UPDATE_DATE,
722 LAST_UPDATED_BY,
723 LAST_UPDATE_LOGIN,
724 CREATED_BY,
725 CREATION_DATE)
726 values (
727 v_new_ele_formula_id,
728 g_eff_start_date,
729 g_eff_end_date,
730 p_ff_bg_id,
731 NULL,
732 v_skeleton_formula_type_id,
733 v_new_ele_formula_name,
734 p_ff_desc,
735 v_new_ele_formula_text,
736 'N',
737 NULL,
738 NULL,
739 NULL,
740 -1,
741 g_eff_start_date);
742
743 else
744
745 -- Element already has formula attached via stat proc rule...
746 -- original formula name and text have been populated as outputs
747 -- from check for existence.
748
749 hr_utility.set_location('hr_generate_pretax.ins_base_formula',50);
750
751 v_new_ele_formula_id := already_exists;
752
753 -- Update existing formula with new ff name and text.
754
755 -- hr_utility.trace('existing FF '||v_new_ele_formula_id||' being updated');
756 -- hr_utility.trace(v_new_ele_formula_name);
757
758 hr_utility.set_location('hr_generate_pretax.ins_base_formula',70);
759
760 /*
761 UPDATE ff_formulas_f
762 SET formula_name = v_new_ele_formula_name,
763 formula_text = v_new_ele_formula_text
764 WHERE formula_id = v_new_ele_formula_id
765 AND business_group_id = p_ff_bg_id
766 AND g_eff_start_date BETWEEN effective_start_date
767 AND effective_end_date;
768 */
769 UPDATE ff_formulas_f
770 SET formula_text = v_new_ele_formula_text
771 WHERE formula_id = v_new_ele_formula_id
772 AND business_group_id = p_ff_bg_id
773 AND g_eff_start_date BETWEEN effective_start_date
774 AND effective_end_date;
775
776 --
777 -- Insert the original formula into current business group to preserve customer mods.
778 --
779 -- hr_utility.trace('FF '||v_orig_ele_formula_name||' already exists for ele '||p_ff_ele_name);
780
781 select count(0)
782 into l_count_already
783 from ff_formulas_f
784 where upper(formula_name) like upper('%'||l_placehold_ele_name||'%');
785
786
787 hr_utility.set_location('hr_generate_pretax.ins_base_formula',35);
788
789 -- hr_utility.trace('Preserving text for '||v_orig_ele_formula_name);
790
791 v_orig_ele_formula_name := 'OLD'||l_count_already||'_'||v_orig_ele_formula_name;
792 v_orig_ele_formula_name := substr(v_orig_ele_formula_name,1,80);
793
794 -- hr_utility.trace('Renamed ff name is '||v_orig_ele_formula_name);
795
796 hr_utility.set_location('hr_generate_pretax.ins_base_formula',30);
797
798 SELECT ff_formulas_s.nextval
799 INTO v_orig_ele_formula_id
800 FROM sys.dual;
801
802 hr_utility.set_location('hr_generate_pretax.ins_base_formula',40);
803
804 INSERT INTO ff_formulas_f ( FORMULA_ID,
805 EFFECTIVE_START_DATE,
806 EFFECTIVE_END_DATE,
807 BUSINESS_GROUP_ID,
808 LEGISLATION_CODE,
809 FORMULA_TYPE_ID,
810 FORMULA_NAME,
811 DESCRIPTION,
812 FORMULA_TEXT,
813 STICKY_FLAG,
814 LAST_UPDATE_DATE,
815 LAST_UPDATED_BY,
816 LAST_UPDATE_LOGIN,
817 CREATED_BY,
818 CREATION_DATE)
819 values (
820 v_orig_ele_formula_id,
821 g_eff_start_date,
822 g_eff_end_date,
823 p_ff_bg_id,
824 NULL,
825 v_skeleton_formula_type_id,
826 v_orig_ele_formula_name,
827 p_ff_desc,
828 v_orig_ele_formula_text,
829 'N',
830 NULL,
831 NULL,
832 NULL,
833 -1,
834 g_eff_start_date);
835
836 end if;
837
838 return v_new_ele_formula_id;
839
840 EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
841
842 END ins_base_formula;
843
844
845
846 --
847 ------------------------- ins_calc_formula -----------------------
848 --
849
850 FUNCTION ins_calc_formula (
851 p_ff_ele_name in varchar2,
852 p_spr_ele_name in varchar2,
853 p_ff_suffix in varchar2,
854 p_ff_desc in varchar2,
855 p_ff_bg_id in number,
856 p_amt_rule in varchar2 default NULL,
857 p_row_type in varchar2 default NULL,
858 p_iv_dflts_text in varchar2 default ' ',
859 p_calc_dedn_text in varchar2)
860 RETURN number IS
861
862 -- Note, the ff_ele_name is used for ele name placeholder substitution in formula text...
863 -- ie. the base element name...while the spr ele name is the element this formula
864 -- will be attached to...
865
866 -- local vars
867
868 v_formula_id number; -- Return var.
869 v_skeleton_formula_text VARCHAR2(32000);
870 v_skeleton_formula_type_id NUMBER(9);
871 v_orig_ele_formula_text VARCHAR2(32000);
872 v_new_ele_formula_text VARCHAR2(32000);
873 v_orig_ele_formula_name VARCHAR2(80);
874 v_new_ele_formula_name VARCHAR2(80);
875 v_orig_ele_formula_id NUMBER(9);
876 v_new_ele_formula_id NUMBER(9);
877 v_ele_name VARCHAR2(80);
878
879 l_placehold_ele_name varchar2(80);
880 l_count_already number;
881 already_exists number;
882
883 BEGIN
884
885 hr_utility.set_location('hr_generate_pretax.ins_calc_formula',10);
886
887 SELECT FF.formula_text,
888 FF.formula_type_id
889 INTO v_skeleton_formula_text,
890 v_skeleton_formula_type_id
891 FROM ff_formulas_f FF
892 WHERE FF.formula_name = 'PRETAX_CALCULATION_FORMULA'
893 AND FF.business_group_id IS NULL
894 AND FF.legislation_code = 'US'
895 AND g_eff_start_date >= FF.effective_start_date
896 AND g_eff_start_date <= FF.effective_end_date;
897
898 -- Replace element name placeholders with current element name:
899
900 hr_utility.set_location('hr_generate_pretax.ins_calc_formula',15);
901
902 l_placehold_ele_name := REPLACE(LTRIM(RTRIM(UPPER(p_ff_ele_name))),' ','_');
903 v_new_ele_formula_name := REPLACE(LTRIM(RTRIM(UPPER(p_ff_ele_name))),' ','_');
904 v_new_ele_formula_text := REPLACE( v_skeleton_formula_text,
905 '<ELE_NAME>',
906 v_new_ele_formula_name);
907
908 /* No longer required with 40.6 :
909 -- Make replacements for input entry value defaults
910 v_new_ele_formula_text := REPLACE( v_new_ele_formula_text,
911 '<IV_ENTRY_VALUE_DEFAULTS_SECTION>',
912 p_iv_dflts_text);
913 */
914
915 -- Make replacement for deduction calculation section
916 v_new_ele_formula_text := REPLACE( v_new_ele_formula_text,
917 '<CALC_DEDN_AMOUNT_FF_TEXT>',
918 p_calc_dedn_text);
919
920 --
921 -- Make <ROW_TYPE> replacements if necessary.
922 --
923
924 IF p_amt_rule = 'PT' THEN
925
926 IF p_row_type NOT IN ('Salary Range', 'Age Range') THEN
927
928 hr_utility.set_location('hr_generate_pretax.ins_calc_formula',17);
929
930 v_new_ele_formula_text := REPLACE(v_new_ele_formula_text,
931 '<ROW_TYPE>',
932 REPLACE(LTRIM(RTRIM(p_row_type)),' ','_'));
933
934 hr_utility.set_location('hr_generate_pretax.ins_calc_formula',19);
935
936 v_new_ele_formula_text := REPLACE(v_new_ele_formula_text,
937 '<DEFAULT_ROW_TYPE_LINE>',
938 'default for ' || REPLACE(LTRIM(RTRIM(p_row_type)),' ','_') || ' (text) is ''NOT ENTERED''');
939
940 hr_utility.set_location('hr_generate_pretax.ins_calc_formula',21);
941
942 v_new_ele_formula_text := REPLACE(v_new_ele_formula_text,
943 '<ROW_TYPE_INPUTS_ARE>',
944 ',' || REPLACE(LTRIM(RTRIM(p_row_type)),' ','_') || ' (text)');
945
946 ELSE
947
948 hr_utility.set_location('hr_generate_pretax.ins_calc_formula',20);
949
950 v_new_ele_formula_text := REPLACE(v_new_ele_formula_text,
951 '<ROW_TYPE>',
952 'To_Char(PER_AGE)');
953
954 hr_utility.set_location('hr_generate_pretax.ins_calc_formula',22);
955
956 v_new_ele_formula_text := REPLACE(v_new_ele_formula_text,
957 '<DEFAULT_ROW_TYPE_LINE>',
958 ' ');
959
960 hr_utility.set_location('hr_generate_pretax.ins_calc_formula',24);
961
962 v_new_ele_formula_text := REPLACE(v_new_ele_formula_text,
963 '<ROW_TYPE_INPUTS_ARE>',
964 ' ');
965
966 END IF;
967
968 --
969 -- "Zero" benefits
970 --
971 hr_utility.set_location('hr_user_init_dedn.ins_formula',23);
972 v_new_ele_formula_text := REPLACE( v_new_ele_formula_text,
973 l_placehold_ele_name || '_BEN_EE_CONTR_VALUE',
974 '0');
975
976 v_new_ele_formula_text := REPLACE( v_new_ele_formula_text,
977 l_placehold_ele_name || '_BEN_ER_CONTR_VALUE',
978 '0');
979
980 v_new_ele_formula_text := REPLACE( v_new_ele_formula_text,
981 '<DEFAULT_BEN_EE_LINE>',
982 ' ');
983
984 v_new_ele_formula_text := REPLACE( v_new_ele_formula_text,
985 '<DEFAULT_BEN_ER_LINE>',
986 ' ');
987
988 ELSIF p_amt_rule = 'BT' THEN
989
990 --
991 -- Using benefits, <ELE_NAME>_BEN_EE_CONTR_VALUE is already taken care of.
992 --
993 hr_utility.set_location('hr_user_init_dedn.ins_formula',25);
994 v_new_ele_formula_text := REPLACE( v_new_ele_formula_text,
995 '<DEFAULT_BEN_EE_LINE>',
996 'default for ' || l_placehold_ele_name || '_BEN_EE_CONTR_VALUE is 0');
997
998 v_new_ele_formula_text := REPLACE( v_new_ele_formula_text,
999 '<DEFAULT_BEN_ER_LINE>',
1000 'default for ' || l_placehold_ele_name || '_BEN_ER_CONTR_VALUE is 0');
1001
1002 -- Clear out <ROW_TYPE>
1003 v_new_ele_formula_text := REPLACE( v_new_ele_formula_text,
1004 '<ROW_TYPE>',
1005 '''NOT ENTERED''');
1006
1007 v_new_ele_formula_text := REPLACE( v_new_ele_formula_text,
1008 '<DEFAULT_ROW_TYPE_LINE>',
1009 ' ');
1010
1011 v_new_ele_formula_text := REPLACE( v_new_ele_formula_text,
1012 '<ROW_TYPE_INPUTS_ARE>',
1013 ' ');
1014
1015 ELSE
1016
1017 --
1018 -- Clear out everything!
1019 -- Clear out <ROW_TYPE>
1020 hr_utility.set_location('hr_user_init_dedn.ins_formula',27);
1021 v_new_ele_formula_text := REPLACE( v_new_ele_formula_text,
1022 '<ROW_TYPE>',
1023 '''NOT ENTERED''');
1024
1025 v_new_ele_formula_text := REPLACE( v_new_ele_formula_text,
1026 '<DEFAULT_ROW_TYPE_LINE>',
1027 ' ');
1028
1029 v_new_ele_formula_text := REPLACE( v_new_ele_formula_text,
1030 '<ROW_TYPE_INPUTS_ARE>',
1031 ' ');
1032
1033 --
1034 -- "Zero" benefits
1035 --
1036 v_new_ele_formula_text := REPLACE( v_new_ele_formula_text,
1037 l_placehold_ele_name || '_BEN_EE_CONTR_VALUE',
1038 '0');
1039
1040 v_new_ele_formula_text := REPLACE( v_new_ele_formula_text,
1041 l_placehold_ele_name || '_BEN_ER_CONTR_VALUE',
1042 '0');
1043
1044 v_new_ele_formula_text := REPLACE( v_new_ele_formula_text,
1045 '<DEFAULT_BEN_EE_LINE>',
1046 ' ');
1047
1048 v_new_ele_formula_text := REPLACE( v_new_ele_formula_text,
1049 '<DEFAULT_BEN_ER_LINE>',
1050 ' ');
1051
1052 END IF;
1053
1054 v_new_ele_formula_name := v_new_ele_formula_name || UPPER(p_ff_suffix);
1055 v_new_ele_formula_name := SUBSTR(v_new_ele_formula_name, 1, 80);
1056
1057 -- Call function to check existence of formula to get id.
1058 -- Get original formula id, name, and text for this element currently,
1059 -- ie. before putting in new ff text.
1060
1061 hr_utility.set_location('hr_generate_pretax.ins_calc_formula',20);
1062
1063 already_exists := hr_template_existence.ele_ff_exists(
1064 p_ele_name => p_spr_ele_name,
1065 p_bg_id => p_ff_bg_id,
1066 p_ff_name => v_orig_ele_formula_name,
1067 p_ff_text => v_orig_ele_formula_text,
1068 p_eff_date => g_eff_start_date);
1069
1070 if already_exists = 0 then
1071
1072 -- Insert the new formula text into current business group since
1073 -- there is no formula for this element currently.
1074 --
1075 -- Get new id for formula
1076
1077 hr_utility.set_location('hr_generate_pretax.ins_calc_formula',30);
1078
1079 SELECT ff_formulas_s.nextval
1080 INTO v_new_ele_formula_id
1081 FROM sys.dual;
1082
1083 hr_utility.set_location('hr_generate_pretax.ins_calc_formula',40);
1084
1085 INSERT INTO ff_formulas_f ( FORMULA_ID,
1086 EFFECTIVE_START_DATE,
1087 EFFECTIVE_END_DATE,
1088 BUSINESS_GROUP_ID,
1089 LEGISLATION_CODE,
1090 FORMULA_TYPE_ID,
1091 FORMULA_NAME,
1092 DESCRIPTION,
1093 FORMULA_TEXT,
1094 STICKY_FLAG,
1095 LAST_UPDATE_DATE,
1096 LAST_UPDATED_BY,
1097 LAST_UPDATE_LOGIN,
1098 CREATED_BY,
1099 CREATION_DATE)
1100 values (
1101 v_new_ele_formula_id,
1102 g_eff_start_date,
1103 g_eff_end_date,
1104 p_ff_bg_id,
1105 NULL,
1106 v_skeleton_formula_type_id,
1107 v_new_ele_formula_name,
1108 p_ff_desc,
1109 v_new_ele_formula_text,
1110 'N',
1111 NULL,
1112 NULL,
1113 NULL,
1114 -1,
1115 g_eff_start_date);
1116
1117 else
1118
1119 -- Element already has formula attached via stat proc rule...
1120 -- original formula name and text have been populated as outputs
1121 -- from check for existence.
1122
1123 hr_utility.set_location('hr_generate_pretax.ins_calc_formula',50);
1124
1125 v_new_ele_formula_id := already_exists;
1126
1127 -- Update existing formula with new ff name and text.
1128
1129 hr_utility.set_location('hr_generate_pretax.ins_calc_formula',70);
1130
1131 -- hr_utility.trace('existing FF '||v_new_ele_formula_id||' being updated');
1132 -- hr_utility.trace(v_new_ele_formula_name);
1133
1134 /* Hitting constraint error FF_FORMULAS_F_UK2 with...
1135 UPDATE ff_formulas_f
1136 SET formula_name = v_new_ele_formula_name,
1137 formula_text = v_new_ele_formula_text
1138 WHERE formula_id = v_new_ele_formula_id
1139 AND business_group_id = p_ff_bg_id
1140 AND g_eff_start_date BETWEEN effective_start_date
1141 AND effective_end_date;
1142 */
1143 -- So trying without updating ff name...
1144 UPDATE ff_formulas_f
1145 SET formula_text = v_new_ele_formula_text
1146 WHERE formula_id = v_new_ele_formula_id
1147 AND business_group_id = p_ff_bg_id
1148 AND g_eff_start_date BETWEEN effective_start_date
1149 AND effective_end_date;
1150
1151 --
1152 -- Insert the original formula into current business group to preserve customer mods.
1153 --
1154 -- hr_utility.trace('FF '||v_orig_ele_formula_name||' already exists for ele '||p_ff_ele_name);
1155
1156 select count(0)
1157 into l_count_already
1158 from ff_formulas_f
1159 where upper(formula_name) like upper('%'||l_placehold_ele_name||'%');
1160
1161 -- hr_utility.trace('Preserving text for '||v_orig_ele_formula_name);
1162
1163 hr_utility.set_location('hr_generate_pretax.ins_calc_formula',80);
1164
1165 v_orig_ele_formula_name := 'OLD'||l_count_already||'_'||v_orig_ele_formula_name;
1166 v_orig_ele_formula_name := substr(v_orig_ele_formula_name,1,80);
1167
1168 -- hr_utility.trace('Original formula now in ff called '||v_orig_ele_formula_name);
1169
1170 hr_utility.set_location('hr_generate_pretax.ins_calc_formula',90);
1171
1172 SELECT ff_formulas_s.nextval
1173 INTO v_orig_ele_formula_id
1174 FROM sys.dual;
1175
1176 hr_utility.set_location('hr_generate_pretax.ins_calc_formula',100);
1177
1178 INSERT INTO ff_formulas_f ( FORMULA_ID,
1179 EFFECTIVE_START_DATE,
1180 EFFECTIVE_END_DATE,
1181 BUSINESS_GROUP_ID,
1182 LEGISLATION_CODE,
1183 FORMULA_TYPE_ID,
1184 FORMULA_NAME,
1185 DESCRIPTION,
1186 FORMULA_TEXT,
1187 STICKY_FLAG,
1188 LAST_UPDATE_DATE,
1189 LAST_UPDATED_BY,
1190 LAST_UPDATE_LOGIN,
1191 CREATED_BY,
1192 CREATION_DATE)
1193 values (
1194 v_orig_ele_formula_id,
1195 g_eff_start_date,
1196 g_eff_end_date,
1197 p_ff_bg_id,
1198 NULL,
1199 v_skeleton_formula_type_id,
1200 v_orig_ele_formula_name,
1201 p_ff_desc,
1202 v_orig_ele_formula_text,
1203 'N',
1204 NULL,
1205 NULL,
1206 NULL,
1207 -1,
1208 g_eff_start_date);
1209
1210 end if;
1211
1212 RETURN v_new_ele_formula_id;
1213
1214 EXCEPTION WHEN NO_DATA_FOUND THEN
1215
1216 NULL;
1217
1218 END ins_calc_formula;
1219
1220
1221 --
1222 ------------------------- ins_dedn_formula_processing -----------------------
1223 --
1224
1225 PROCEDURE ins_dedn_formula_processing (
1226 p_ele_name in varchar2,
1227 p_primary_class_id in number,
1228 p_ele_class_name in varchar2,
1229 p_ele_cat in varchar2,
1230 p_ele_proc_type in varchar2,
1231 p_amount_rule in varchar2,
1232 p_proc_runtype in varchar2 default 'R',
1233 p_start_rule in varchar2 default NULL,
1234 p_stop_rule in varchar2 default NULL,
1235 p_ee_bond in varchar2 default 'N',
1236 p_paytab_name in varchar2 default NULL,
1237 p_paytab_col in varchar2 default NULL,
1238 p_paytab_row_type in varchar2 default NULL,
1239 p_arrearage in varchar2 default 'N',
1240 p_partial_dedn in varchar2 default 'N',
1241 p_er_charge_eletype_id in number default NULL,
1242 p_er_charge_payval_id in number default NULL,
1243 p_bg_id in number,
1244 p_mix_category in varchar2 default NULL,
1245 p_eff_start_date in date default NULL,
1246 p_eff_end_date in date default NULL,
1247 p_bg_name in varchar2) IS
1248
1249 -- local vars
1250
1251 v_fname VARCHAR2(80);
1252 v_ftype_id NUMBER(9);
1253 v_fdesc VARCHAR2(240);
1254 v_ftext VARCHAR2(32000); -- "Safe" max length of varchar2.
1255 v_sticky_flag VARCHAR2(1);
1256 v_asst_status_type_id NUMBER(9) := NULL;
1257 v_stat_proc_rule_id NUMBER(9);
1258 v_fres_rule_id NUMBER(9);
1259 v_proc_rule VARCHAR2(1) := 'P'; -- Provide "Process" proc rule.
1260 v_calc_rule_formula_id NUMBER(9);
1261 v_wh_formula_id NUMBER(9);
1262 v_spr_formula_id NUMBER(9);
1263 v_er_contr_inpval_id NUMBER(9); -- inpval id of ER Contr to feed ER chrg.
1264 v_er_payval_id NUMBER(9); -- paybal id of ER Contr (if not passed in).
1265 v_bondrefund_inpval_id NUMBER(9); -- inpval id "Bond Refund" to feed DirPay.
1266 v_to_owed_inpval_id NUMBER(9); -- inpval id for Tot Reached stop rule
1267 v_to_arrears_inpval_id NUMBER(9); -- inpval id for To Arrears rule
1268 v_notaken_inpval_id NUMBER(9); -- inpval id for Not Taken (arrears = 'Y')
1269 v_inpval_id NUMBER(9);
1270 v_inpval_name VARCHAR2(80);
1271 v_inpval_uom VARCHAR2(80);
1272 v_ele_sev_level VARCHAR2(1);
1273 v_gen_dbi VARCHAR2(1);
1274 v_dflt_value VARCHAR2(60);
1275 v_amt_rule_formula VARCHAR2(80);
1276 v_lkp_type VARCHAR2(30);
1277 v_val_formula_id NUMBER(9);
1278 v_class_name VARCHAR2(80);
1279 v_paytab_id NUMBER(9);
1280 v_row_code VARCHAR2(30);
1281 v_age_code VARCHAR2(30);
1282 v_sal_code VARCHAR2(30);
1283 v_cre_row_inpval VARCHAR2(1);
1284 v_user_row_title VARCHAR2(80);
1285
1286 l_ff_suffix varchar2(30);
1287 l_ff_desc varchar2(80);
1288
1289 /*
1290 This procedure performs the following:
1291 . Manually update DDF segments on various associated element types.
1292
1293 Make direct calls to CORE_API packaged procedures to:
1294 . Insert status proc rule of 'PROCESS' for Asst status type 'ACTIVE_ASSIGN'
1295 and appropriate formula according to calculation method
1296 . Setup calculation formula and status proc rule on calculator element.
1297 . Setup withholding formula and status proc rule on base (withholding) element.
1298 . Insert input values according to calculation method
1299 . Insert formula result rules as appropriate for formula and amount rule.
1300
1301 -- In the case of deductions elements, the formulae are fully defined in advance
1302 -- based on calculation rule only. These pre-packaged formulae are seeded
1303 -- as startup data - such that bg_id is NULL, in the appropriate legislation.
1304 -- The formula_name will closely resemble the calc rule.
1305 -- For deductions, formula is "pieced together" according to calc_rule
1306 -- and other attributes.
1307 -- To copy a formula from seed data to the customer business group, we can
1308 -- select the formula_text LONG field into a VARCHAR2; the LONG field
1309 -- in the table can then accept the VARCHAR2 formula text as long as it
1310 -- does not exceed 32767 bytes (varchar2 will be 32000 to be safe).
1311 */
1312
1313 BEGIN
1314
1315 -- Check for percentage amount rule...
1316 IF UPPER(p_amount_rule) = 'PE' THEN
1317
1318 -- Set DDF segments
1319
1320 hr_utility.set_location('hr_generate_pretax.ins_dedn_formula_processing',05);
1321
1322 -- For CALCULATOR ele, set mix flag and ben class if approp...
1323 UPDATE pay_element_types_f
1324 SET element_information_category = g_ele_info_cat,
1325 element_information1 = p_ele_cat,
1326 element_information2 = p_partial_dedn,
1327 element_information3 = p_proc_runtype,
1328 element_information9 = p_mix_category,
1329 benefit_classification_id = p_ben_class_id
1330 WHERE element_type_id = dedn_ele_ids(1)
1331 AND business_group_id + 0 = p_bg_id;
1332
1333 -- For WITHHOLDING element, do not set mix flag or ben class...
1334 UPDATE pay_element_types_f
1335 SET element_information_category = g_ele_info_cat,
1336 element_information1 = p_ele_cat,
1337 element_information2 = p_partial_dedn,
1338 element_information3 = p_proc_runtype,
1339 benefit_classification_id = NULL
1340 WHERE element_type_id = dedn_ele_ids(2)
1341 AND business_group_id + 0 = p_bg_id;
1342
1343 hr_utility.set_location('hr_generate_pretax.ins_dedn_formula_processing',05);
1344
1345 UPDATE pay_element_types_f
1346 SET element_information_category = g_ele_info_cat,
1347 element_information1 = p_ele_cat,
1348 element_information2 = p_partial_dedn,
1349 element_information3 = p_proc_runtype
1350 WHERE element_type_id = dedn_ele_ids(4)
1351 AND business_group_id + 0 = p_bg_id;
1352
1353 hr_utility.set_location('hr_generate_pretax.ins_dedn_formula_processing',05);
1354
1355 UPDATE pay_element_types_f
1356 SET element_information_category = g_ele_info_cat,
1357 element_information1 = p_ele_cat,
1358 element_information2 = p_partial_dedn,
1359 element_information3 = p_proc_runtype
1360 WHERE element_type_id = dedn_ele_ids(3)
1361 AND business_group_id + 0 = p_bg_id;
1362
1363 hr_utility.set_location('hr_generate_pretax.ins_dedn_formula_processing',15);
1364
1365 -- Check for payroll table amount rule...
1366 ELSIF UPPER(p_amount_rule) = 'PT' THEN
1367
1368 -- Find table id
1369
1370 hr_utility.set_location('hr_generate_pretax.ins_dedn_formula_processing',53);
1371
1372 SELECT user_table_id
1373 INTO v_paytab_id
1374 FROM pay_user_tables
1375 WHERE UPPER(user_table_name) = UPPER(p_paytab_name)
1376 AND NVL(business_group_id, p_bg_id) = p_bg_id;
1377
1378 -- Find row code
1379
1380 hr_utility.set_location('hr_generate_pretax.ins_dedn_formula_processing',55);
1381
1382 SELECT lookup_code
1383 INTO v_row_code
1384 FROM hr_lookups
1385 WHERE UPPER(meaning) = UPPER(p_paytab_row_type)
1386 AND lookup_type = 'US_TABLE_ROW_TYPES';
1387
1388 hr_utility.set_location('hr_generate_pretax.ins_dedn_formula_processing',57);
1389
1390 -- For CALCULATOR, set mix flag and ben class...
1391 UPDATE pay_element_types_f
1392 SET element_information_category = g_ele_info_cat,
1393 element_information1 = p_ele_cat,
1394 element_information2 = p_partial_dedn,
1395 element_information3 = p_proc_runtype,
1396 element_information6 = p_paytab_name,
1397 element_information7 = v_row_code,
1398 element_information9 = p_mix_category,
1399 benefit_classification_id = p_ben_class_id
1400 WHERE element_type_id = dedn_ele_ids(1)
1401 AND business_group_id + 0 = p_bg_id;
1402
1403 -- For WITHHOLDING element, do not set mix flag or ben class...
1404 UPDATE pay_element_types_f
1405 SET element_information_category = g_ele_info_cat,
1406 element_information1 = p_ele_cat,
1407 element_information2 = p_partial_dedn,
1408 element_information3 = p_proc_runtype,
1409 element_information6 = p_paytab_name,
1410 element_information7 = v_row_code,
1411 benefit_classification_id = NULL
1412 WHERE element_type_id = dedn_ele_ids(2)
1413 AND business_group_id + 0 = p_bg_id;
1414
1415 hr_utility.set_location('hr_generate_pretax.ins_dedn_formula_processing',57);
1416
1417
1418 UPDATE pay_element_types_f
1419 SET element_information_category = g_ele_info_cat,
1420 element_information1 = p_ele_cat,
1421 element_information2 = p_partial_dedn,
1422 element_information3 = p_proc_runtype,
1423 element_information6 = p_paytab_name,
1424 element_information7 = v_row_code
1425 WHERE element_type_id = dedn_ele_ids(4)
1426 AND business_group_id + 0 = p_bg_id;
1427
1428 hr_utility.set_location('hr_generate_pretax.ins_dedn_formula_processing',57);
1429
1430
1431 UPDATE pay_element_types_f
1432 SET element_information_category = g_ele_info_cat,
1433 element_information1 = p_ele_cat,
1434 element_information2 = p_partial_dedn,
1435 element_information3 = p_proc_runtype,
1436 element_information6 = p_paytab_name,
1437 element_information7 = v_row_code
1438 WHERE element_type_id = dedn_ele_ids(3)
1439 AND business_group_id + 0 = p_bg_id;
1440
1441 hr_utility.set_location('hr_generate_pretax.ins_dedn_formula_processing',59);
1442
1443
1444 -- Check for Benefits Table amount rule...
1445 ELSIF UPPER(p_amount_rule) = 'BT' THEN
1446
1447 -- Set mix flag and ben class on CALCULATOR element...
1448 UPDATE pay_element_types_f
1449 SET element_information_category = g_ele_info_cat,
1450 element_information1 = p_ele_cat,
1451 element_information2 = p_partial_dedn,
1452 element_information3 = p_proc_runtype,
1453 element_information9 = p_mix_category,
1454 benefit_classification_id = p_ben_class_id
1455 WHERE element_type_id = dedn_ele_ids(1)
1456 AND business_group_id + 0 = p_bg_id;
1457
1458 -- For WITHHOLDING element, do not set mix flag or ben class...
1459 UPDATE pay_element_types_f
1460 SET element_information_category = g_ele_info_cat,
1461 element_information1 = p_ele_cat,
1462 element_information2 = p_partial_dedn,
1463 element_information3 = p_proc_runtype,
1464 benefit_classification_id = NULL
1465 WHERE element_type_id = dedn_ele_ids(2)
1466 AND business_group_id + 0 = p_bg_id;
1467
1468
1469 hr_utility.set_location('hr_generate_pretax.ins_dedn_formula_processing',83);
1470
1471
1472 UPDATE pay_element_types_f
1473 SET element_information_category = g_ele_info_cat,
1474 element_information1 = p_ele_cat,
1475 element_information2 = p_partial_dedn,
1476 element_information3 = p_proc_runtype
1477 WHERE element_type_id = dedn_ele_ids(4)
1478 AND business_group_id + 0 = p_bg_id;
1479
1480
1481 hr_utility.set_location('hr_generate_pretax.ins_dedn_formula_processing',83);
1482
1483 UPDATE pay_element_types_f
1484 SET element_information_category = g_ele_info_cat,
1485 element_information1 = p_ele_cat,
1486 element_information2 = p_partial_dedn,
1487 element_information3 = p_proc_runtype
1488 WHERE element_type_id = dedn_ele_ids(3)
1489 AND business_group_id + 0 = p_bg_id;
1490
1491
1492 hr_utility.set_location('hr_generate_pretax.ins_dedn_formula_processing',85);
1493
1494 ELSE
1495 --
1496 -- Default to Flat Amount processing of deduction.
1497 -- Set DDF Segment values:
1498 --
1499
1500 hr_utility.set_location('hr_generate_pretax.ins_dedn_formula_processing',81);
1501
1502 -- Set mix flag and ben class on CALCULATOR element...
1503 UPDATE pay_element_types_f
1504 SET element_information_category = g_ele_info_cat,
1505 element_information1 = p_ele_cat,
1506 element_information2 = p_partial_dedn,
1507 element_information3 = p_proc_runtype,
1508 element_information9 = p_mix_category,
1509 benefit_classification_id = p_ben_class_id
1510 WHERE element_type_id = dedn_ele_ids(1)
1511 AND business_group_id + 0 = p_bg_id;
1512
1513 -- Do not set mix flag or ben class on WITHHOLDING element...
1514 UPDATE pay_element_types_f
1515 SET element_information_category = g_ele_info_cat,
1516 element_information1 = p_ele_cat,
1517 element_information2 = p_partial_dedn,
1518 element_information3 = p_proc_runtype,
1519 benefit_classification_id = NULL
1520 WHERE element_type_id = dedn_ele_ids(2)
1521 AND business_group_id + 0 = p_bg_id;
1522
1523
1524 hr_utility.set_location('hr_generate_pretax.ins_dedn_formula_processing',81);
1525
1526
1527 UPDATE pay_element_types_f
1528 SET element_information_category = g_ele_info_cat,
1529 element_information1 = p_ele_cat,
1530 element_information2 = p_partial_dedn,
1531 element_information3 = p_proc_runtype
1532 WHERE element_type_id = dedn_ele_ids(4)
1533 AND business_group_id + 0 = p_bg_id;
1534
1535 hr_utility.set_location('hr_generate_pretax.ins_dedn_formula_processing',81);
1536
1537
1538 UPDATE pay_element_types_f
1539 SET element_information_category = g_ele_info_cat,
1540 element_information1 = p_ele_cat,
1541 element_information2 = p_partial_dedn,
1542 element_information3 = p_proc_runtype
1543 WHERE element_type_id = dedn_ele_ids(3)
1544 AND business_group_id + 0 = p_bg_id;
1545
1546 hr_utility.set_location('hr_generate_pretax.ins_dedn_formula_processing',15);
1547
1548 END IF; -- Amount rule checks for formula insertion...
1549
1550
1551 /*
1552 Now create calc formula for element by selecting "skeleton" calculation formula
1553 and performing string substitutions for element name in proper placeholders.
1554 The formula is then inserted into the current business group.
1555 Other placeholders will be substituted based on other attributes (ie.
1556 balances and arrears). When finished, the formula can be compiled.
1557 */
1558
1559 l_placeholder_ele_name := REPLACE(LTRIM(RTRIM(UPPER(p_ele_name))),' ','_');
1560
1561 IF UPPER(p_amount_rule) = 'PE' THEN
1562
1563 l_iv_defaults_ff_text := ' default for '||l_placeholder_ele_name||'_PERCENTAGE_ENTRY_VALUE is 0';
1564
1565 select ff.formula_text
1566 into l_calc_dedn_ff_text
1567 from ff_formulas_f ff
1568 where ff.formula_name = 'PRETAX_PERCENTAGE_FF_TEXT'
1569 and ff.business_group_id is null
1570 and ff.legislation_code = 'US'
1571 and sysdate between ff.effective_start_date
1572 and ff.effective_end_date;
1573
1574 l_calc_dedn_ff_text := REPLACE( l_calc_dedn_ff_text,
1575 '<ELE_NAME>',
1576 l_placeholder_ele_name);
1577
1578 l_ff_suffix := '_PERCENTAGE_PTX';
1579 l_ff_desc := 'Percentage calculation for deductions.';
1580
1581 hr_utility.set_location('hr_generate_pretax.ins_dedn_formula_processing',17);
1582
1583 ELSIF UPPER(p_amount_rule) = 'PT' THEN
1584
1585 l_iv_defaults_ff_text := ' default for '||l_placeholder_ele_name||'_TABLE_COLUMN_ENTRY_VALUE IS ''NOT ENTERED'' ';
1586
1587 IF p_paytab_row_type NOT IN ('Salary Range', 'Age Range') THEN
1588
1589 hr_utility.set_location('hr_generate_pretax.ins_dedn_formula_processing',75);
1590 l_iv_defaults_ff_text := l_iv_defaults_ff_text||' default for ' || REPLACE(LTRIM(RTRIM(p_paytab_row_type)),' ','_') || ' (text) is ''NOT ENTERED'' ';
1591
1592 END IF;
1593
1594 select ff.formula_text
1595 into l_calc_dedn_ff_text
1596 from ff_formulas_f ff
1597 where ff.formula_name = 'PRETAX_PAYROLL_TABLE_FF_TEXT'
1598 and ff.business_group_id is null
1599 and ff.legislation_code = 'US'
1600 and sysdate between ff.effective_start_date
1601 and ff.effective_end_date;
1602
1603 l_calc_dedn_ff_text := REPLACE( l_calc_dedn_ff_text,
1604 '<ELE_NAME>',
1605 l_placeholder_ele_name);
1606
1607 l_ff_suffix := '_PAYROLL_PTX';
1608 l_ff_desc := 'Payroll table calculation for deductions.';
1609
1610 ELSIF UPPER(p_amount_rule) = 'BT' THEN
1611
1612 l_iv_defaults_ff_text := ' default for '||l_placeholder_ele_name||'_EE_CONTR_ENTRY_VALUE IS 0 ';
1613 l_iv_defaults_ff_text := l_iv_defaults_ff_text||' default for '||l_placeholder_ele_name||'_ER_CONTR_ENTRY_VALUE IS 0 ';
1614 l_iv_defaults_ff_text := l_iv_defaults_ff_text||' default for '||l_placeholder_ele_name||'_COVERAGE_ENTRY_VALUE IS ''NOT ENTERED'' ';
1615
1616 select ff.formula_text
1617 into l_calc_dedn_ff_text
1618 from ff_formulas_f ff
1619 where ff.formula_name = 'PRETAX_BENEFIT_FF_TEXT'
1620 and ff.business_group_id is null
1621 and ff.legislation_code = 'US'
1622 and sysdate between ff.effective_start_date
1623 and ff.effective_end_date;
1624
1625 l_calc_dedn_ff_text := REPLACE( l_calc_dedn_ff_text,
1626 '<ELE_NAME>',
1627 l_placeholder_ele_name);
1628
1629 l_ff_suffix := '_BENEFIT_PTX';
1630 l_ff_desc := 'Benefit table calculation for deductions.';
1631
1632
1633 ELSE /* Flat Amount calc rule */
1634
1635 l_iv_defaults_ff_text := ' default for '||l_placeholder_ele_name||'_AMOUNT_ENTRY_VALUE IS 0';
1636
1637 select ff.formula_text
1638 into l_calc_dedn_ff_text
1639 from ff_formulas_f ff
1640 where ff.formula_name = 'PRETAX_FLAT_AMOUNT_FF_TEXT'
1641 and ff.business_group_id is null
1642 and ff.legislation_code = 'US'
1643 and sysdate between ff.effective_start_date
1644 and ff.effective_end_date;
1645
1646 l_calc_dedn_ff_text := REPLACE( l_calc_dedn_ff_text,
1647 '<ELE_NAME>',
1648 l_placeholder_ele_name);
1649
1650 l_ff_suffix := '_FLAT_PTX';
1651 l_ff_desc := 'Flat Amount calculation for deductions.';
1652
1653 hr_utility.set_location('hr_generate_pretax.ins_dedn_formula_processing',119);
1654
1655 END IF;
1656
1657
1658 IF UPPER(p_ele_stop_rule) = 'TOTAL REACHED' THEN
1659
1660 l_iv_defaults_ff_text := l_iv_defaults_ff_text||' default for '||l_placeholder_ele_name||'_TOTAL_OWED_ENTRY_VALUE IS 0 ';
1661 l_iv_defaults_ff_text := l_iv_defaults_ff_text||' default for '||l_placeholder_ele_name||'_TOWARDS_OWED_ENTRY_VALUE IS ''NOT ENTERED'' ';
1662
1663 END IF;
1664
1665 --
1666 -- Calculation formula goes on CALCULATION element - ie. base ele.
1667 --
1668 v_calc_rule_formula_id := ins_calc_formula (
1669 p_ff_ele_name => dedn_ele_names(1),
1670 p_spr_ele_name => dedn_ele_names(1),
1671 p_ff_suffix => l_ff_suffix,
1672 p_ff_desc => l_ff_desc,
1673 p_ff_bg_id => p_bg_id,
1674 p_amt_rule => p_amount_rule,
1675 p_row_type => NULL,
1676 p_iv_dflts_text => l_iv_defaults_ff_text,
1677 p_calc_dedn_text => l_calc_dedn_ff_text );
1678
1679 hr_utility.set_location('hr_generate_pretax.ins_dedn_formula_processing',87);
1680
1681 --
1682 -- Now setup withholding formula on WITHHOLDING element...
1683 --
1684 v_wh_formula_id := ins_base_formula (
1685 p_ff_ele_name => dedn_ele_names(1),
1686 p_spr_ele_name => dedn_ele_names(2),
1687 p_ff_desc => 'Pretax withholding formula.',
1688 p_ff_bg_id => p_bg_id);
1689
1690 --
1691 -- check for existence of status proc rule...function returns id if it does...
1692 --
1693 hr_utility.set_location('hr_generate_pretax.ins_dedn_formula_processing',119);
1694
1695 -- hr_utility.trace('spr exists checking...');
1696 -- hr_utility.trace(dedn_ele_ids(1)||' '||v_calc_rule_formula_id);
1697 -- hr_utility.trace(p_bg_id||' '||p_eff_start_date);
1698 -- Calculator status proc rule...
1699 already_exists := hr_template_existence.spr_exists (
1700 p_ele_id => dedn_ele_ids(1),
1701 p_ff_id => v_spr_formula_id,
1702 p_bg_id => p_bg_id,
1703 p_val_date => p_eff_start_date);
1704
1705 if already_exists = 0 then
1706
1707 hr_utility.set_location('hr_generate_pretax.ins_dedn_formula_processing',121);
1708 v_stat_proc_rule_id :=
1709 pay_formula_results.ins_stat_proc_rule (
1710 p_business_group_id => p_bg_id,
1711 p_legislation_code => NULL,
1712 p_legislation_subgroup => g_template_leg_subgroup,
1713 p_effective_start_date => p_eff_start_date,
1714 p_effective_end_date => p_eff_end_date,
1715 p_element_type_id => dedn_ele_ids(1),
1716 p_assignment_status_type_id => v_asst_status_type_id,
1717 p_formula_id => v_calc_rule_formula_id,
1718 p_processing_rule => v_proc_rule);
1719
1720 dedn_statproc_rule_id(1) := v_stat_proc_rule_id;
1721
1722 else
1723
1724 -- Statproc rule already exists for calculator element.
1725
1726 hr_utility.set_location('hr_generate_pretax.ins_dedn_formula_processing',123);
1727
1728 v_stat_proc_rule_id := already_exists;
1729 dedn_statproc_rule_id(1) := v_stat_proc_rule_id;
1730
1731 IF v_calc_rule_formula_id = v_spr_formula_id THEN
1732
1733 NULL;
1734
1735 ELSE
1736
1737 UPDATE pay_status_processing_rules_f
1738 SET formula_id = v_calc_rule_formula_id
1739 WHERE status_processing_rule_id = already_exists
1740 AND p_eff_start_date BETWEEN effective_start_date
1741 AND effective_end_date;
1742
1743 END IF;
1744
1745 end if;
1746
1747 -- hr_utility.trace('spr exists checking...');
1748 -- hr_utility.trace(dedn_ele_ids(2)||' '||v_wh_formula_id);
1749 -- hr_utility.trace(p_bg_id||' '||p_eff_start_date);
1750 -- Check for base status proc rule existence...
1751 already_exists := hr_template_existence.spr_exists (
1752 p_ele_id => dedn_ele_ids(2),
1753 p_ff_id => v_spr_formula_id,
1754 p_bg_id => p_bg_id,
1755 p_val_date => p_eff_start_date);
1756
1757 if already_exists = 0 then
1758
1759 hr_utility.set_location('hr_generate_pretax.ins_dedn_formula_processing',125);
1760 v_stat_proc_rule_id :=
1761 pay_formula_results.ins_stat_proc_rule (
1762 p_business_group_id => p_bg_id,
1763 p_legislation_code => NULL,
1764 p_legislation_subgroup => g_template_leg_subgroup,
1765 p_effective_start_date => p_eff_start_date,
1766 p_effective_end_date => p_eff_end_date,
1767 p_element_type_id => dedn_ele_ids(2),
1768 p_assignment_status_type_id => v_asst_status_type_id,
1769 p_formula_id => v_wh_formula_id,
1770 p_processing_rule => v_proc_rule);
1771
1772 dedn_statproc_rule_id(2) := v_stat_proc_rule_id;
1773
1774 else
1775
1776 -- Statproc rule already exists for calculator element.
1777
1778 hr_utility.set_location('hr_generate_pretax.ins_dedn_formula_processing',127);
1779
1780 v_stat_proc_rule_id := already_exists;
1781 dedn_statproc_rule_id(2) := v_stat_proc_rule_id;
1782
1783 IF v_wh_formula_id = v_spr_formula_id THEN
1784
1785 NULL;
1786
1787 ELSE
1788
1789 UPDATE pay_status_processing_rules_f
1790 SET formula_id = v_wh_formula_id
1791 WHERE status_processing_rule_id = already_exists
1792 AND p_eff_start_date BETWEEN effective_start_date
1793 AND effective_end_date;
1794
1795 END IF;
1796
1797 end if;
1798
1799
1800 -- Create Input Values for elements.
1801 -- These are the input values that are dependent on calc rule or other attributes
1802 -- selected on the Deductions screen...ie. arrears, ee bond, stop/start rules...
1803 -- We may want to put the generic input values in here too - ie the ivs common to
1804 -- all deduction templates.
1805 -- This will give us the ability to refer to ivs by number, especially for creating
1806 -- formula result rules...
1807
1808 IF UPPER(p_amount_rule) = 'PE' THEN
1809
1810 hr_utility.set_location('hr_generate_pretax.ins_dedn_formula_processing',65);
1811
1812 dedn_base_iv_names(1) := 'Percentage';
1813 dedn_base_iv_uom(1) := 'Number';
1814 dedn_base_iv_mand(1) := 'N';
1815 dedn_base_iv_dbi(1) := 'Y';
1816 dedn_base_iv_lkp(1) := NULL;
1817 dedn_base_iv_dflt(1) := NULL;
1818
1819 dedn_base_iv_names(2) := NULL;
1820 dedn_base_iv_names(3) := NULL;
1821
1822 l_num_base_ivs := 3;
1823
1824 ELSIF UPPER(p_amount_rule) = 'PT' THEN
1825
1826 -- Insert input vals;
1827 -- "Table Column" (default to p_ele_paytab_col)
1828 -- Also requires input value for "Table Row" if value in
1829 -- p_ele_paytab_row is NOT a database item. If it IS a dbi_name,
1830 -- then we do not create inpval for it, the value is stored on
1831 -- the SCL and formula picks it up from there. This will amount
1832 -- to an input value required when the user enters a value OTHER
1833 -- then "Salary Range" or "Age Range" in the Row Type field.
1834
1835 hr_utility.set_location('hr_generate_pretax.ins_dedn_formula_processing',65);
1836
1837 dedn_base_iv_names(1) := 'Table Column';
1838 dedn_base_iv_uom(1) := 'Character';
1839 dedn_base_iv_mand(1) := 'N';
1840 dedn_base_iv_dbi(1) := 'N';
1841 dedn_base_iv_lkp(1) := NULL;
1842 dedn_base_iv_dflt(1) := p_paytab_col;
1843
1844 dedn_base_iv_names(2) := NULL;
1845 dedn_base_iv_names(3) := NULL;
1846
1847 l_num_base_ivs := 3;
1848
1849 hr_utility.set_location('hr_generate_pretax.ins_dedn_formula_processing',65);
1850
1851 -- Place logic determining to create or not create additional input value here:
1852 -- 1) If p_paytab_row_type = 'Age Range' or 'Salary Range' then DO NOT create
1853 -- addl inpval;
1854 -- 2) Compare p_paytab_row_type with database item names:
1855 -- If p_paytab_row_type = dbi.name then DO NOT create addl inpval;
1856 -- Else create addl inpval where name = PAY_USER_TABLES.USER_ROW_TITLE
1857 -- (and user_table_name = p_paytab_name)
1858 --
1859
1860 IF p_paytab_row_type NOT IN ('Salary Range', 'Age Range') THEN
1861
1862 hr_utility.set_location('hr_generate_pretax.ins_dedn_formula_processing',75);
1863
1864 dedn_base_iv_names(2) := p_paytab_row_type;
1865 dedn_base_iv_uom(2) := 'Character';
1866 dedn_base_iv_mand(2) := 'N';
1867 dedn_base_iv_dbi(2) := 'Y';
1868 dedn_base_iv_lkp(2) := NULL;
1869 dedn_base_iv_dflt(2) := NULL;
1870
1871 dedn_base_iv_names(3) := NULL;
1872
1873 l_num_base_ivs := 3;
1874
1875 END IF; -- rowtype = dbi name check.
1876
1877 ELSIF UPPER(p_amount_rule) = 'BT' THEN
1878
1879 hr_utility.set_location('hr_generate_pretax.ins_dedn_formula_processing',65);
1880
1881 dedn_base_iv_names(1) := 'Coverage';
1882 dedn_base_iv_uom(1) := 'Character';
1883 dedn_base_iv_mand(1) := 'N';
1884 dedn_base_iv_dbi(1) := 'Y';
1885 dedn_base_iv_lkp(1) := 'US_BENEFIT_COVERAGE';
1886 dedn_base_iv_dflt(1) := 'EMP ONLY';
1887
1888 dedn_base_iv_names(2) := 'ER Contr';
1889 dedn_base_iv_uom(2) := 'Money';
1890 dedn_base_iv_mand(2) := 'N';
1891 dedn_base_iv_dbi(2) := 'Y';
1892 dedn_base_iv_lkp(2) := NULL;
1893 dedn_base_iv_dflt(2) := NULL;
1894
1895 dedn_base_iv_names(3) := 'EE Contr';
1896 dedn_base_iv_uom(3) := 'Money';
1897 dedn_base_iv_mand(3) := 'N';
1898 dedn_base_iv_dbi(3) := 'Y';
1899 dedn_base_iv_lkp(3) := NULL;
1900 dedn_base_iv_dflt(3) := NULL;
1901
1902 l_num_base_ivs := 3;
1903
1904 ELSE
1905
1906 -- Flat Amount calc rule...
1907
1908 dedn_base_iv_names(1) := 'Amount';
1909 dedn_base_iv_uom(1) := 'Money';
1910 dedn_base_iv_mand(1) := 'N';
1911 dedn_base_iv_dbi(1) := 'Y';
1912 dedn_base_iv_lkp(1) := NULL;
1913 dedn_base_iv_dflt(1) := NULL;
1914
1915 dedn_base_iv_names(2) := NULL;
1916 dedn_base_iv_names(3) := NULL;
1917
1918 l_num_base_ivs := 3;
1919
1920 END IF; -- Amount rule checks for input value creation...
1921
1922 -- More input values are required for particular functionality...
1923 /*
1924 Start Rule input values are
1925 dedn_base_iv_names(4)
1926 dedn_base_iv_names(5)
1927 */
1928
1929 IF p_ele_start_rule = 'ET' THEN
1930
1931 dedn_base_iv_names(4) := 'Threshold Amount';
1932 dedn_base_iv_uom(4) := 'Money';
1933 dedn_base_iv_mand(4) := 'N';
1934 dedn_base_iv_dbi(4) := 'Y';
1935 dedn_base_iv_lkp(4) := NULL;
1936 dedn_base_iv_dflt(4) := NULL;
1937
1938 /* 40.8 set iv name(5) to null. */
1939 dedn_base_iv_names(5) := NULL;
1940 l_num_base_ivs := 5;
1941
1942 ELSIF p_ele_start_rule = 'CHAINED' THEN
1943
1944 /* 40.8 set iv name(4) to null. */
1945 dedn_base_iv_names(4) := NULL;
1946
1947 dedn_base_iv_names(5) := 'Chained To';
1948 dedn_base_iv_uom(5) := 'Character';
1949 dedn_base_iv_mand(5) := 'N';
1950 dedn_base_iv_dbi(5) := 'Y';
1951 dedn_base_iv_lkp(5) := NULL;
1952 dedn_base_iv_dflt(5) := NULL;
1953
1954 l_num_base_ivs := 5;
1955
1956 ELSE
1957
1958 dedn_base_iv_names(4) := NULL;
1959 dedn_base_iv_names(5) := NULL;
1960
1961 l_num_base_ivs := 5;
1962
1963 END IF; -- Start Rule checks for input value creation...
1964
1965 /*
1966 Stop Rule input values are
1967 dedn_base_iv_names(6)
1968 dedn_base_iv_names(7)
1969 */
1970
1971 IF UPPER(p_ele_stop_rule) = 'TOTAL REACHED' THEN
1972
1973 dedn_base_iv_names(6) := 'Total Owed';
1974 dedn_base_iv_uom(6) := 'Money';
1975 dedn_base_iv_mand(6) := 'N';
1976 dedn_base_iv_dbi(6) := 'N';
1977 dedn_base_iv_lkp(6) := NULL;
1978 dedn_base_iv_dflt(6) := NULL;
1979
1980 dedn_base_iv_names(7) := 'Towards Owed';
1981 dedn_base_iv_uom(7) := 'Character';
1982 dedn_base_iv_mand(7) := 'N';
1983 dedn_base_iv_dbi(7) := 'Y';
1984 dedn_base_iv_lkp(7) := 'YES_NO';
1985 dedn_base_iv_dflt(7) := 'Y';
1986
1987 l_num_base_ivs := 7;
1988
1989 dedn_sf_iv_names(1) := 'Accrued';
1990 dedn_sf_iv_uom(1) := 'Money';
1991 dedn_sf_iv_mand(1) := 'N';
1992 dedn_sf_iv_dbi(1) := 'N';
1993 dedn_sf_iv_lkp(1) := NULL;
1994 dedn_sf_iv_dflt(1) := NULL;
1995
1996 l_num_sf_ivs := 1;
1997
1998 ELSE
1999
2000 dedn_base_iv_names(6) := NULL;
2001 dedn_base_iv_names(7) := NULL;
2002
2003 l_num_base_ivs := 7;
2004
2005 dedn_sf_iv_names(1) := NULL;
2006 l_num_sf_ivs := 1;
2007
2008 END IF; -- Stop Rule checks for creation of input values.
2009
2010 /*
2011 Arrearage input values are
2012 dedn_base_iv_names(8)
2013 */
2014 IF p_arrearage = 'Y' THEN
2015
2016 -- create input values for:
2017 -- (*) "Clear Arrears" (on base ele)
2018 -- (*) "Arrears Contr" (on Special Features ele Feeds "Arrears" balance)
2019
2020 dedn_base_iv_names(8) := 'Clear Arrears';
2021 dedn_base_iv_uom(8) := 'Character';
2022 dedn_base_iv_mand(8) := 'N';
2023 dedn_base_iv_dbi(8) := 'N';
2024 dedn_base_iv_lkp(8) := 'YES_NO';
2025 dedn_base_iv_dflt(8) := 'N';
2026
2027 l_num_base_ivs := 8;
2028
2029 dedn_sf_iv_names(2) := 'Arrears Contr';
2030 dedn_sf_iv_uom(2) := 'Money';
2031 dedn_sf_iv_mand(2) := 'N';
2032 dedn_sf_iv_dbi(2) := 'N';
2033 dedn_sf_iv_lkp(2) := NULL;
2034 dedn_sf_iv_dflt(2) := NULL;
2035
2036 l_num_sf_ivs := 2;
2037
2038 ELSE
2039
2040 dedn_base_iv_names(8) := NULL;
2041 l_num_base_ivs := 8;
2042
2043 dedn_sf_iv_names(2) := NULL;
2044 l_num_sf_ivs := 2;
2045
2046 END IF; -- Arrears input value creation...
2047
2048 /*
2049 Input values passed from calculator to withholding element are
2050 dedn_wh_iv_names(1) = Calc Dedn Amt
2051 W/H FF INPUTS:
2052 Calc_Dedn_Amt
2053 , Total_Owed
2054 , Towards_Owed (text)
2055 , Clear_Arrears (text)
2056 , Arrears_Bal
2057 , Accrued_Bal
2058 , Partial_Dedns (text)
2059
2060 */
2061
2062 dedn_wh_iv_names(1) := 'Calc Dedn Amt';
2063 dedn_wh_iv_uom(1) := 'Money';
2064 dedn_wh_iv_mand(1) := 'N';
2065 dedn_wh_iv_dbi(1) := 'N';
2066 dedn_wh_iv_lkp(1) := NULL;
2067 dedn_wh_iv_dflt(1) := NULL;
2068
2069 dedn_wh_iv_names(2) := 'Total Owed';
2070 dedn_wh_iv_uom(2) := 'Money';
2071 dedn_wh_iv_mand(2) := 'N';
2072 dedn_wh_iv_dbi(2) := 'N';
2073 dedn_wh_iv_lkp(2) := NULL;
2074 dedn_wh_iv_dflt(2) := NULL;
2075
2076 dedn_wh_iv_names(3) := 'Towards Owed';
2077 dedn_wh_iv_uom(3) := 'Character';
2078 dedn_wh_iv_mand(3) := 'N';
2079 dedn_wh_iv_dbi(3) := 'N';
2080 dedn_wh_iv_lkp(3) := 'YES_NO';
2081 dedn_wh_iv_dflt(3) := 'Y';
2082
2083 dedn_wh_iv_names(4) := 'Clear Arrears';
2084 dedn_wh_iv_uom(4) := 'Character';
2085 dedn_wh_iv_mand(4) := 'N';
2086 dedn_wh_iv_dbi(4) := 'N';
2087 dedn_wh_iv_lkp(4) := 'YES_NO';
2088 dedn_wh_iv_dflt(4) := 'N';
2089
2090 dedn_wh_iv_names(5) := 'Arrears Bal';
2091 dedn_wh_iv_uom(5) := 'Money';
2092 dedn_wh_iv_mand(5) := 'N';
2093 dedn_wh_iv_dbi(5) := 'N';
2094 dedn_wh_iv_lkp(5) := NULL;
2095 dedn_wh_iv_dflt(5) := NULL;
2096
2097 dedn_wh_iv_names(6) := 'Accrued Bal';
2098 dedn_wh_iv_uom(6) := 'Money';
2099 dedn_wh_iv_mand(6) := 'N';
2100 dedn_wh_iv_dbi(6) := 'N';
2101 dedn_wh_iv_lkp(6) := NULL;
2102 dedn_wh_iv_dflt(6) := NULL;
2103
2104 dedn_wh_iv_names(7) := 'Partial Dedns';
2105 dedn_wh_iv_uom(7) := 'Character';
2106 dedn_wh_iv_mand(7) := 'N';
2107 dedn_wh_iv_dbi(7) := 'N';
2108 dedn_wh_iv_lkp(7) := 'YES_NO';
2109 dedn_wh_iv_dflt(7) := 'N';
2110
2111 dedn_wh_iv_names(8) := 'Pass To Aftertax';
2112 dedn_wh_iv_uom(8) := 'Money';
2113 dedn_wh_iv_mand(8) := 'X';
2114 dedn_wh_iv_dbi(8) := 'N';
2115 dedn_wh_iv_lkp(8) := NULL;
2116 dedn_wh_iv_dflt(8) := NULL;
2117
2118 l_num_wh_ivs := 8;
2119
2120
2121
2122 -- Create input values on base element...
2123 FOR h in 1..l_num_base_ivs LOOP
2124
2125 IF dedn_base_iv_names(h) IS NOT NULL THEN
2126
2127 hr_utility.set_location('hr_upgrade_earnings.ins_uie_formula_processing',300);
2128
2129 already_exists := hr_template_existence.iv_name_exists(
2130 p_ele_id => dedn_ele_ids(1),
2131 p_bg_id => p_bg_id,
2132 p_iv_name => dedn_base_iv_names(h),
2133 p_eff_date => g_eff_start_date);
2134
2135 if already_exists = 0 then
2136
2137 hr_utility.set_location('hr_upgrade_earnings.ins_uie_formula_processing',310);
2138
2139 select max(display_sequence)
2140 into dedn_base_seq
2141 from pay_input_values_f
2142 where element_type_id = dedn_ele_ids(1)
2143 and g_eff_start_date between effective_start_date
2144 and effective_end_date;
2145
2146 dedn_base_seq := dedn_base_seq + 1;
2147
2148 /* 40.4 : Call new API to add input value over life of element if
2149 upgrade mode = Yes
2150 */
2151
2152 IF l_upgrade_mode = 'N' THEN
2153
2154 v_inpval_id := pay_db_pay_setup.create_input_value (
2155 p_element_name => dedn_ele_names(1),
2156 p_name => dedn_base_iv_names(h),
2157 p_uom => dedn_base_iv_uom(h),
2158 p_uom_code => NULL,
2159 p_mandatory_flag => dedn_base_iv_mand(h),
2160 p_generate_db_item_flag => dedn_base_iv_dbi(h),
2161 p_default_value => dedn_base_iv_dflt(h),
2162 p_min_value => NULL,
2163 p_max_value => NULL,
2164 p_warning_or_error => NULL,
2165 p_lookup_type => dedn_base_iv_lkp(h),
2166 p_formula_id => NULL,
2167 p_hot_default_flag => 'N',
2168 p_display_sequence => dedn_base_seq,
2169 p_business_group_name => p_bg_name,
2170 p_effective_start_date => g_eff_start_date,
2171 p_effective_end_date => g_eff_end_date);
2172
2173 dedn_base_iv_ids(h) := v_inpval_id;
2174
2175 hr_utility.set_location('hr_upgrade_earnings.ins_uie_formula_processing',320);
2176
2177 hr_input_values.chk_input_value(
2178 p_element_type_id => dedn_ele_ids(1),
2179 p_legislation_code => g_template_leg_code,
2180 p_val_start_date => g_eff_start_date,
2181 p_val_end_date => g_eff_end_date,
2182 p_insert_update_flag => 'UPDATE',
2183 p_input_value_id => dedn_base_iv_ids(h),
2184 p_rowid => NULL,
2185 p_recurring_flag => 'N',
2186 p_mandatory_flag => dedn_base_iv_mand(h),
2187 p_hot_default_flag => 'N',
2188 p_standard_link_flag => 'N',
2189 p_classification_type => 'N',
2190 p_name => dedn_base_iv_names(h),
2191 p_uom => dedn_base_iv_uom(h),
2192 p_min_value => NULL,
2193 p_max_value => NULL,
2194 p_default_value => dedn_base_iv_dflt(h),
2195 p_lookup_type => dedn_base_iv_lkp(h),
2196 p_formula_id => NULL,
2197 p_generate_db_items_flag => dedn_base_iv_dbi(h),
2198 p_warning_or_error => NULL);
2199
2200 hr_utility.set_location('hr_upgrade_earnings.ins_uie_formula_processing',330);
2201
2202 hr_input_values.ins_3p_input_values(
2203 p_val_start_date => g_eff_start_date,
2204 p_val_end_date => g_eff_end_date,
2205 p_element_type_id => dedn_ele_ids(1),
2206 p_primary_classification_id => p_primary_class_id,
2207 p_input_value_id => dedn_base_iv_ids(h),
2208 p_default_value => dedn_base_iv_dflt(h),
2209 p_max_value => NULL,
2210 p_min_value => NULL,
2211 p_warning_or_error_flag => NULL,
2212 p_input_value_name => dedn_base_iv_names(h),
2213 p_db_items_flag => dedn_base_iv_dbi(h),
2214 p_costable_type => NULL,
2215 p_hot_default_flag => 'N',
2216 p_business_group_id => p_bg_id,
2217 p_legislation_code => NULL,
2218 p_startup_mode => NULL);
2219
2220 ELSE
2221
2222 v_inpval_id := pay_db_pay_setup.create_input_value (
2223 p_element_name => dedn_ele_names(1),
2224 p_name => dedn_base_iv_names(h),
2225 p_uom => dedn_base_iv_uom(h),
2226 p_uom_code => NULL,
2227 p_mandatory_flag => dedn_base_iv_mand(h),
2228 p_generate_db_item_flag => dedn_base_iv_dbi(h),
2229 p_default_value => dedn_base_iv_dflt(h),
2230 p_min_value => NULL,
2231 p_max_value => NULL,
2232 p_warning_or_error => NULL,
2233 p_lookup_type => dedn_base_iv_lkp(h),
2234 p_formula_id => NULL,
2235 p_hot_default_flag => 'N',
2236 p_display_sequence => dedn_base_seq,
2237 p_business_group_name => p_bg_name,
2238 p_effective_start_date => g_eff_start_date,
2239 p_effective_end_date => g_eff_end_date);
2240
2241 dedn_base_iv_ids(h) := v_inpval_id;
2242
2243 hr_utility.set_location('hr_upgrade_earnings.ins_uie_formula_processing',320);
2244
2245 pay_template_ivs.chk_input_value(
2246 p_element_type_id => dedn_ele_ids(1),
2247 p_legislation_code => g_template_leg_code,
2248 p_val_start_date => g_eff_start_date,
2249 p_val_end_date => g_eff_end_date,
2250 p_insert_update_flag => 'UPDATE',
2251 p_input_value_id => dedn_base_iv_ids(h),
2252 p_rowid => NULL,
2253 p_recurring_flag => 'N',
2254 p_mandatory_flag => dedn_base_iv_mand(h),
2255 p_hot_default_flag => 'N',
2256 p_standard_link_flag => 'N',
2257 p_classification_type => 'N',
2258 p_name => dedn_base_iv_names(h),
2259 p_uom => dedn_base_iv_uom(h),
2260 p_min_value => NULL,
2261 p_max_value => NULL,
2262 p_default_value => dedn_base_iv_dflt(h),
2263 p_lookup_type => dedn_base_iv_lkp(h),
2264 p_formula_id => NULL,
2265 p_generate_db_items_flag => dedn_base_iv_dbi(h),
2266 p_warning_or_error => NULL);
2267
2268 hr_utility.set_location('hr_upgrade_earnings.ins_uie_formula_processing',330);
2269
2270 pay_template_ivs.ins_3p_input_values(
2271 p_val_start_date => g_eff_start_date,
2272 p_val_end_date => g_eff_end_date,
2273 p_element_type_id => dedn_ele_ids(1),
2274 p_primary_classification_id => p_primary_class_id,
2275 p_input_value_id => dedn_base_iv_ids(h),
2276 p_default_value => dedn_base_iv_dflt(h),
2277 p_max_value => NULL,
2278 p_min_value => NULL,
2279 p_warning_or_error_flag => NULL,
2280 p_input_value_name => dedn_base_iv_names(h),
2281 p_db_items_flag => dedn_base_iv_dbi(h),
2282 p_costable_type => NULL,
2283 p_hot_default_flag => 'N',
2284 p_business_group_id => p_bg_id,
2285 p_legislation_code => NULL,
2286 p_startup_mode => NULL);
2287
2288 pay_template_ivs.new_input_value (
2289 p_element_type_id => dedn_ele_ids(1),
2290 p_input_value_id => dedn_base_iv_ids(h),
2291 p_costed_flag => 'N',
2292 p_default_value => dedn_base_iv_dflt(h),
2293 p_max_value => NULL,
2294 p_min_value => NULL,
2295 p_warning_or_error => NULL);
2296
2297 END IF;
2298
2299 else
2300
2301 hr_utility.set_location('hr_upgrade_earnings.ins_uie_formula_processing',340);
2302
2303 v_inpval_id := already_exists;
2304 dedn_base_iv_ids(h) := v_inpval_id;
2305
2306 end if;
2307
2308 ELSE
2309
2310 -- BASE IV name is null...do not need to create...
2311
2312 NULL;
2313
2314 END IF;
2315
2316 END LOOP;
2317
2318 FOR k in 1..l_num_wh_ivs LOOP
2319
2320 IF dedn_wh_iv_names(k) IS NOT NULL THEN
2321
2322 hr_utility.set_location('hr_upgrade_earnings.ins_uie_formula_processing',300);
2323
2324 already_exists := hr_template_existence.iv_name_exists(
2325 p_ele_id => dedn_ele_ids(2),
2326 p_bg_id => p_bg_id,
2327 p_iv_name => dedn_wh_iv_names(k),
2328 p_eff_date => g_eff_start_date);
2329
2330 if already_exists = 0 then
2331
2332 hr_utility.set_location('hr_upgrade_earnings.ins_uie_formula_processing',310);
2333
2334 select max(display_sequence)
2335 into dedn_wh_seq
2336 from pay_input_values_f
2337 where element_type_id = dedn_ele_ids(2)
2338 and g_eff_start_date between effective_start_date
2339 and effective_end_date;
2340
2341 dedn_wh_seq := dedn_wh_seq + 1;
2342
2343 -- 40.4 : Call new API to add input value over life of element if
2344 -- upgrade mode = Yes
2345
2346
2347 IF l_upgrade_mode = 'N' THEN
2348
2349 v_inpval_id := pay_db_pay_setup.create_input_value (
2350 p_element_name => dedn_ele_names(2),
2351 p_name => dedn_wh_iv_names(k),
2352 p_uom => dedn_wh_iv_uom(k),
2353 p_uom_code => NULL,
2354 p_mandatory_flag => dedn_wh_iv_mand(k),
2355 p_generate_db_item_flag => dedn_wh_iv_dbi(k),
2356 p_default_value => dedn_wh_iv_dflt(k),
2357 p_min_value => NULL,
2358 p_max_value => NULL,
2359 p_warning_or_error => NULL,
2360 p_lookup_type => dedn_wh_iv_lkp(k),
2361 p_formula_id => NULL,
2362 p_hot_default_flag => 'N',
2363 p_display_sequence => dedn_wh_seq,
2364 p_business_group_name => p_bg_name,
2365 p_effective_start_date => g_eff_start_date,
2366 p_effective_end_date => g_eff_end_date);
2367
2368 dedn_wh_iv_ids(k) := v_inpval_id;
2369
2370 hr_utility.set_location('hr_upgrade_earnings.ins_uie_formula_processing',320);
2371
2372 hr_input_values.chk_input_value(
2373 p_element_type_id => dedn_ele_ids(2),
2374 p_legislation_code => g_template_leg_code,
2375 p_val_start_date => g_eff_start_date,
2376 p_val_end_date => g_eff_end_date,
2377 p_insert_update_flag => 'UPDATE',
2378 p_input_value_id => dedn_wh_iv_ids(k),
2379 p_rowid => NULL,
2380 p_recurring_flag => 'N',
2381 p_mandatory_flag => dedn_wh_iv_mand(k),
2382 p_hot_default_flag => 'N',
2383 p_standard_link_flag => 'N',
2384 p_classification_type => 'N',
2385 p_name => dedn_wh_iv_names(k),
2386 p_uom => dedn_wh_iv_uom(k),
2387 p_min_value => NULL,
2388 p_max_value => NULL,
2389 p_default_value => dedn_wh_iv_dflt(k),
2390 p_lookup_type => dedn_wh_iv_lkp(k),
2391 p_formula_id => NULL,
2392 p_generate_db_items_flag => dedn_wh_iv_dbi(k),
2393 p_warning_or_error => NULL);
2394
2395 hr_utility.set_location('hr_upgrade_earnings.ins_uie_formula_processing',330);
2396
2397 hr_input_values.ins_3p_input_values(
2398 p_val_start_date => g_eff_start_date,
2399 p_val_end_date => g_eff_end_date,
2400 p_element_type_id => dedn_ele_ids(2),
2401 p_primary_classification_id => p_primary_class_id,
2402 p_input_value_id => dedn_wh_iv_ids(k),
2403 p_default_value => dedn_wh_iv_dflt(k),
2404 p_max_value => NULL,
2405 p_min_value => NULL,
2406 p_warning_or_error_flag => NULL,
2407 p_input_value_name => dedn_wh_iv_names(k),
2408 p_db_items_flag => dedn_wh_iv_dbi(k),
2409 p_costable_type => NULL,
2410 p_hot_default_flag => 'N',
2411 p_business_group_id => p_bg_id,
2412 p_legislation_code => NULL,
2413 p_startup_mode => NULL);
2414
2415 ELSE
2416
2417 v_inpval_id := pay_db_pay_setup.create_input_value (
2418 p_element_name => dedn_ele_names(2),
2419 p_name => dedn_wh_iv_names(k),
2420 p_uom => dedn_wh_iv_uom(k),
2421 p_uom_code => NULL,
2422 p_mandatory_flag => dedn_wh_iv_mand(k),
2423 p_generate_db_item_flag => dedn_wh_iv_dbi(k),
2424 p_default_value => dedn_wh_iv_dflt(k),
2425 p_min_value => NULL,
2426 p_max_value => NULL,
2427 p_warning_or_error => NULL,
2428 p_lookup_type => dedn_wh_iv_lkp(k),
2429 p_formula_id => NULL,
2430 p_hot_default_flag => 'N',
2431 p_display_sequence => dedn_wh_seq,
2432 p_business_group_name => p_bg_name,
2433 p_effective_start_date => g_eff_start_date,
2434 p_effective_end_date => g_eff_end_date);
2435
2436 dedn_wh_iv_ids(k) := v_inpval_id;
2437
2438 hr_utility.set_location('hr_upgrade_earnings.ins_uie_formula_processing',320);
2439
2440 pay_template_ivs.chk_input_value(
2441 p_element_type_id => dedn_ele_ids(2),
2442 p_legislation_code => g_template_leg_code,
2443 p_val_start_date => g_eff_start_date,
2444 p_val_end_date => g_eff_end_date,
2445 p_insert_update_flag => 'UPDATE',
2446 p_input_value_id => dedn_wh_iv_ids(k),
2447 p_rowid => NULL,
2448 p_recurring_flag => 'N',
2449 p_mandatory_flag => dedn_wh_iv_mand(k),
2450 p_hot_default_flag => 'N',
2451 p_standard_link_flag => 'N',
2452 p_classification_type => 'N',
2453 p_name => dedn_wh_iv_names(k),
2454 p_uom => dedn_wh_iv_uom(k),
2455 p_min_value => NULL,
2456 p_max_value => NULL,
2457 p_default_value => dedn_wh_iv_dflt(k),
2458 p_lookup_type => dedn_wh_iv_lkp(k),
2459 p_formula_id => NULL,
2460 p_generate_db_items_flag => dedn_wh_iv_dbi(k),
2461 p_warning_or_error => NULL);
2462
2463 hr_utility.set_location('hr_upgrade_earnings.ins_uie_formula_processing',330);
2464
2465 pay_template_ivs.ins_3p_input_values(
2466 p_val_start_date => g_eff_start_date,
2467 p_val_end_date => g_eff_end_date,
2468 p_element_type_id => dedn_ele_ids(2),
2469 p_primary_classification_id => p_primary_class_id,
2470 p_input_value_id => dedn_wh_iv_ids(k),
2471 p_default_value => dedn_wh_iv_dflt(k),
2472 p_max_value => NULL,
2473 p_min_value => NULL,
2474 p_warning_or_error_flag => NULL,
2475 p_input_value_name => dedn_wh_iv_names(k),
2476 p_db_items_flag => dedn_wh_iv_dbi(k),
2477 p_costable_type => NULL,
2478 p_hot_default_flag => 'N',
2479 p_business_group_id => p_bg_id,
2480 p_legislation_code => NULL,
2481 p_startup_mode => NULL);
2482
2483 pay_template_ivs.new_input_value (
2484 p_element_type_id => dedn_ele_ids(2),
2485 p_input_value_id => dedn_wh_iv_ids(k),
2486 p_costed_flag => 'N',
2487 p_default_value => dedn_wh_iv_dflt(k),
2488 p_max_value => NULL,
2489 p_min_value => NULL,
2490 p_warning_or_error => NULL);
2491
2492 END IF;
2493
2494 else
2495
2496 hr_utility.set_location('hr_upgrade_earnings.ins_uie_formula_processing',340);
2497
2498 v_inpval_id := already_exists;
2499 dedn_wh_iv_ids(k) := v_inpval_id;
2500
2501 end if;
2502
2503 ELSE
2504
2505 -- WITHHOLDING IV name is null...do not need to create...
2506
2507 NULL;
2508
2509 END IF;
2510
2511 END LOOP;
2512
2513 --
2514 -- Create inpvals for "Special Inputs"
2515 --
2516
2517 hr_utility.set_location('hr_upgrade_earnings.ins_uie_input_values ',110);
2518
2519 dedn_si_iv_names(1) := 'Replace Amt';
2520 dedn_si_iv_uom(1) := 'Money';
2521 dedn_si_iv_mand(1) := 'N';
2522 dedn_si_iv_dbi(1) := 'N';
2523 dedn_si_iv_lkp(1) := NULL;
2524 dedn_si_iv_dflt(1) := NULL;
2525
2526 dedn_si_iv_names(2) := 'Addl Amt';
2527 dedn_si_iv_uom(2) := 'Money';
2528 dedn_si_iv_mand(2) := 'N';
2529 dedn_si_iv_dbi(2) := 'N';
2530 dedn_si_iv_lkp(2) := NULL;
2531 dedn_si_iv_dflt(2) := NULL;
2532
2533 dedn_si_iv_names(3) := 'Adjust Arrears';
2534 dedn_si_iv_uom(3) := 'Money';
2535 dedn_si_iv_mand(3) := 'N';
2536 dedn_si_iv_dbi(3) := 'N';
2537 dedn_si_iv_lkp(3) := NULL;
2538 dedn_si_iv_dflt(3) := NULL;
2539
2540 l_num_si_ivs := 3;
2541
2542 --
2543 -- Now create all special inputs element input values.
2544 --
2545 FOR siv in 1..l_num_si_ivs LOOP
2546
2547 hr_utility.set_location('hr_upgrade_earnings.ins_uie_input_values ',120);
2548
2549 already_exists := hr_template_existence.iv_name_exists(
2550 p_ele_id => dedn_ele_ids(3),
2551 p_bg_id => p_bg_id,
2552 p_iv_name => dedn_si_iv_names(siv),
2553 p_eff_date => g_eff_start_date);
2554
2555 if already_exists = 0 then
2556
2557 hr_utility.set_location('hr_upgrade_earnings.ins_uie_input_values ',130);
2558
2559 select max(display_sequence)
2560 into dedn_iv_seq
2561 from pay_input_values_f
2562 where element_type_id = dedn_ele_ids(3)
2563 and g_eff_start_date between effective_start_date
2564 and effective_end_date;
2565
2566 dedn_iv_seq := dedn_iv_seq + 1;
2567
2568 hr_utility.set_location('hr_upgrade_earnings.ins_uie_input_values ',140);
2569
2570 /* 40.4 : Call new API to add input value over life of element if
2571 upgrade mode = Yes
2572 */
2573
2574 IF l_upgrade_mode = 'N' THEN
2575
2576 v_inpval_id := pay_db_pay_setup.create_input_value (
2577 p_element_name => dedn_ele_names(3),
2578 p_name => dedn_si_iv_names(siv),
2579 p_uom => dedn_si_iv_uom(siv),
2580 p_uom_code => NULL,
2581 p_mandatory_flag => dedn_si_iv_mand(siv),
2582 p_generate_db_item_flag => dedn_si_iv_dbi(siv),
2583 p_default_value => dedn_si_iv_dflt(siv),
2584 p_min_value => NULL,
2585 p_max_value => NULL,
2586 p_warning_or_error => NULL,
2587 p_lookup_type => dedn_si_iv_lkp(siv),
2588 p_formula_id => NULL,
2589 p_hot_default_flag => 'N',
2590 p_display_sequence => dedn_iv_seq,
2591 p_business_group_name => v_bg_name,
2592 p_effective_start_date => g_eff_start_date,
2593 p_effective_end_date => g_eff_end_date);
2594
2595 dedn_si_iv_ids(siv) := v_inpval_id;
2596
2597 hr_utility.set_location('hr_upgrade_earnings.ins_uie_input_values ',150);
2598
2599 hr_input_values.chk_input_value(
2600 p_element_type_id => dedn_ele_ids(3),
2601 p_legislation_code => g_template_leg_code,
2602 p_val_start_date => g_eff_start_date,
2603 p_val_end_date => g_eff_end_date,
2604 p_insert_update_flag => 'UPDATE',
2605 p_input_value_id => dedn_si_iv_ids(siv),
2606 p_rowid => NULL,
2607 p_recurring_flag => 'N',
2608 p_mandatory_flag => dedn_si_iv_mand(siv),
2609 p_hot_default_flag => 'N',
2610 p_standard_link_flag => 'N',
2611 p_classification_type => 'N',
2612 p_name => dedn_si_iv_names(siv),
2613 p_uom => dedn_si_iv_uom(siv),
2614 p_min_value => NULL,
2615 p_max_value => NULL,
2616 p_default_value => dedn_si_iv_dflt(siv),
2617 p_lookup_type => dedn_si_iv_lkp(siv),
2618 p_formula_id => NULL,
2619 p_generate_db_items_flag => dedn_si_iv_dbi(siv),
2620 p_warning_or_error => NULL);
2621
2622 hr_utility.set_location('hr_upgrade_earnings.ins_uie_input_values ',160);
2623
2624 hr_input_values.ins_3p_input_values(
2625 p_val_start_date => g_eff_start_date,
2626 p_val_end_date => g_eff_end_date,
2627 p_element_type_id => dedn_ele_ids(3),
2628 p_primary_classification_id => p_primary_class_id,
2629 p_input_value_id => dedn_si_iv_ids(siv),
2630 p_default_value => dedn_si_iv_dflt(siv),
2631 p_max_value => NULL,
2632 p_min_value => NULL,
2633 p_warning_or_error_flag => NULL,
2634 p_input_value_name => dedn_si_iv_names(siv),
2635 p_db_items_flag => dedn_si_iv_dbi(siv),
2636 p_costable_type => NULL,
2637 p_hot_default_flag => 'N',
2638 p_business_group_id => p_bg_id,
2639 p_legislation_code => NULL,
2640 p_startup_mode => NULL);
2641
2642 ELSE
2643
2644 v_inpval_id := pay_db_pay_setup.create_input_value (
2645 p_element_name => dedn_ele_names(3),
2646 p_name => dedn_si_iv_names(siv),
2647 p_uom => dedn_si_iv_uom(siv),
2648 p_uom_code => NULL,
2649 p_mandatory_flag => dedn_si_iv_mand(siv),
2650 p_generate_db_item_flag => dedn_si_iv_dbi(siv),
2651 p_default_value => dedn_si_iv_dflt(siv),
2652 p_min_value => NULL,
2653 p_max_value => NULL,
2654 p_warning_or_error => NULL,
2655 p_lookup_type => dedn_si_iv_lkp(siv),
2656 p_formula_id => NULL,
2657 p_hot_default_flag => 'N',
2658 p_display_sequence => dedn_iv_seq,
2659 p_business_group_name => v_bg_name,
2660 p_effective_start_date => g_eff_start_date,
2661 p_effective_end_date => g_eff_end_date);
2662
2663 dedn_si_iv_ids(siv) := v_inpval_id;
2664
2665 hr_utility.set_location('hr_upgrade_earnings.ins_uie_input_values ',150);
2666
2667 pay_template_ivs.chk_input_value(
2668 p_element_type_id => dedn_ele_ids(3),
2669 p_legislation_code => g_template_leg_code,
2670 p_val_start_date => g_eff_start_date,
2671 p_val_end_date => g_eff_end_date,
2672 p_insert_update_flag => 'UPDATE',
2673 p_input_value_id => dedn_si_iv_ids(siv),
2674 p_rowid => NULL,
2675 p_recurring_flag => 'N',
2676 p_mandatory_flag => dedn_si_iv_mand(siv),
2677 p_hot_default_flag => 'N',
2678 p_standard_link_flag => 'N',
2679 p_classification_type => 'N',
2680 p_name => dedn_si_iv_names(siv),
2681 p_uom => dedn_si_iv_uom(siv),
2682 p_min_value => NULL,
2683 p_max_value => NULL,
2684 p_default_value => dedn_si_iv_dflt(siv),
2685 p_lookup_type => dedn_si_iv_lkp(siv),
2686 p_formula_id => NULL,
2687 p_generate_db_items_flag => dedn_si_iv_dbi(siv),
2688 p_warning_or_error => NULL);
2689
2690 hr_utility.set_location('hr_upgrade_earnings.ins_uie_input_values ',160);
2691
2692 pay_template_ivs.ins_3p_input_values(
2693 p_val_start_date => g_eff_start_date,
2694 p_val_end_date => g_eff_end_date,
2695 p_element_type_id => dedn_ele_ids(3),
2696 p_primary_classification_id => p_primary_class_id,
2697 p_input_value_id => dedn_si_iv_ids(siv),
2698 p_default_value => dedn_si_iv_dflt(siv),
2699 p_max_value => NULL,
2700 p_min_value => NULL,
2701 p_warning_or_error_flag => NULL,
2702 p_input_value_name => dedn_si_iv_names(siv),
2703 p_db_items_flag => dedn_si_iv_dbi(siv),
2704 p_costable_type => NULL,
2705 p_hot_default_flag => 'N',
2706 p_business_group_id => p_bg_id,
2707 p_legislation_code => NULL,
2708 p_startup_mode => NULL);
2709
2710 pay_template_ivs.new_input_value (
2711 p_element_type_id => dedn_ele_ids(3),
2712 p_input_value_id => dedn_si_iv_ids(siv),
2713 p_costed_flag => 'N',
2714 p_default_value => dedn_si_iv_dflt(siv),
2715 p_max_value => NULL,
2716 p_min_value => NULL,
2717 p_warning_or_error => NULL);
2718
2719 END IF;
2720
2721 else
2722
2723 hr_utility.set_location('hr_upgrade_earnings.ins_uie_input_values ',170);
2724
2725 v_inpval_id := already_exists;
2726
2727 dedn_si_iv_ids(siv) := v_inpval_id;
2728
2729 end if;
2730
2731 END LOOP;
2732
2733 --
2734 -- Create inpvals for "Special Features"
2735 --
2736
2737 hr_utility.set_location('hr_upgrade_earnings.ins_uie_input_values ',180);
2738
2739 dedn_sf_iv_names(3) := 'Replacement Amt';
2740 dedn_sf_iv_uom(3) := 'Money';
2741 dedn_sf_iv_mand(3) := 'N';
2742 dedn_sf_iv_dbi(3) := 'N';
2743 dedn_sf_iv_lkp(3) := NULL;
2744 dedn_sf_iv_dflt(3) := NULL;
2745
2746 dedn_sf_iv_names(4) := 'Additional Amt';
2747 dedn_sf_iv_uom(4) := 'Money';
2748 dedn_sf_iv_mand(4) := 'N';
2749 dedn_sf_iv_dbi(4) := 'N';
2750 dedn_sf_iv_lkp(4) := NULL;
2751 dedn_sf_iv_dflt(4) := NULL;
2752
2753 dedn_sf_iv_names(5) := 'Not Taken';
2754 dedn_sf_iv_uom(5) := 'Money';
2755 dedn_sf_iv_mand(5) := 'N';
2756 dedn_sf_iv_dbi(5) := 'N';
2757 dedn_sf_iv_lkp(5) := NULL;
2758 dedn_sf_iv_dflt(5) := NULL;
2759
2760 dedn_sf_iv_names(6) := 'Cancel Ptx Amt';
2761 dedn_sf_iv_uom(6) := 'Money';
2762 dedn_sf_iv_mand(6) := 'N';
2763 dedn_sf_iv_dbi(6) := 'N';
2764 dedn_sf_iv_lkp(6) := NULL;
2765 dedn_sf_iv_dflt(6) := NULL;
2766
2767 dedn_sf_iv_names(7) := 'Ptx Amt';
2768 dedn_sf_iv_uom(7) := 'Money';
2769 dedn_sf_iv_mand(7) := 'N';
2770 dedn_sf_iv_dbi(7) := 'N';
2771 dedn_sf_iv_lkp(7) := NULL;
2772 dedn_sf_iv_dflt(7) := NULL;
2773
2774 l_num_sf_ivs := 7;
2775
2776 --
2777 -- Now create all special features element input values.
2778 --
2779 FOR sfv in 1..l_num_sf_ivs LOOP
2780
2781 IF dedn_sf_iv_names(sfv) IS NOT NULL THEN
2782
2783 hr_utility.set_location('hr_upgrade_earnings.ins_uie_input_values ',190);
2784
2785 already_exists := hr_template_existence.iv_name_exists(
2786 p_ele_id => dedn_ele_ids(4),
2787 p_bg_id => p_bg_id,
2788 p_iv_name => dedn_sf_iv_names(sfv),
2789 p_eff_date => g_eff_start_date);
2790
2791 if already_exists = 0 then
2792
2793 hr_utility.set_location('hr_upgrade_earnings.ins_uie_input_values ',200);
2794
2795 select max(display_sequence)
2796 into dedn_iv_seq
2797 from pay_input_values_f
2798 where element_type_id = dedn_ele_ids(4)
2799 and g_eff_start_date between effective_start_date
2800 and effective_end_date;
2801
2802 dedn_iv_seq := dedn_iv_seq + 1;
2803
2804 hr_utility.set_location('hr_upgrade_earnings.ins_uie_input_values ',210);
2805
2806 /* 40.4 : Call new API to add input value over life of element if
2807 upgrade mode = Yes
2808 */
2809
2810 IF l_upgrade_mode = 'N' THEN
2811
2812 v_inpval_id := pay_db_pay_setup.create_input_value (
2813 p_element_name => dedn_ele_names(4),
2814 p_name => dedn_sf_iv_names(sfv),
2815 p_uom => dedn_sf_iv_uom(sfv),
2816 p_uom_code => NULL,
2817 p_mandatory_flag => dedn_sf_iv_mand(sfv),
2818 p_generate_db_item_flag => dedn_sf_iv_dbi(sfv),
2819 p_default_value => dedn_sf_iv_dflt(sfv),
2820 p_min_value => NULL,
2821 p_max_value => NULL,
2822 p_warning_or_error => NULL,
2823 p_lookup_type => dedn_sf_iv_lkp(sfv),
2824 p_formula_id => NULL,
2825 p_hot_default_flag => 'N',
2826 p_display_sequence => dedn_iv_seq,
2827 p_business_group_name => v_bg_name,
2828 p_effective_start_date => g_eff_start_date,
2829 p_effective_end_date => g_eff_end_date);
2830
2831 dedn_sf_iv_ids(sfv) := v_inpval_id;
2832
2833 hr_utility.set_location('hr_upgrade_earnings.ins_uie_input_values ',220);
2834
2835 hr_input_values.chk_input_value(
2836 p_element_type_id => dedn_ele_ids(4),
2837 p_legislation_code => g_template_leg_code,
2838 p_val_start_date => g_eff_start_date,
2839 p_val_end_date => g_eff_end_date,
2840 p_insert_update_flag => 'UPDATE',
2841 p_input_value_id => dedn_sf_iv_ids(sfv),
2842 p_rowid => NULL,
2843 p_recurring_flag => 'N',
2844 p_mandatory_flag => dedn_sf_iv_mand(sfv),
2845 p_hot_default_flag => 'N',
2846 p_standard_link_flag => 'N',
2847 p_classification_type => 'N',
2848 p_name => dedn_sf_iv_names(sfv),
2849 p_uom => dedn_sf_iv_uom(sfv),
2850 p_min_value => NULL,
2851 p_max_value => NULL,
2852 p_default_value => dedn_sf_iv_dflt(sfv),
2853 p_lookup_type => dedn_sf_iv_lkp(sfv),
2854 p_formula_id => NULL,
2855 p_generate_db_items_flag => dedn_sf_iv_dbi(sfv),
2856 p_warning_or_error => NULL);
2857
2858 hr_utility.set_location('hr_upgrade_earnings.ins_uie_input_values ',230);
2859
2860 hr_input_values.ins_3p_input_values(
2861 p_val_start_date => g_eff_start_date,
2862 p_val_end_date => g_eff_end_date,
2863 p_element_type_id => dedn_ele_ids(4),
2864 p_primary_classification_id => p_primary_class_id,
2865 p_input_value_id => dedn_sf_iv_ids(sfv),
2866 p_default_value => dedn_sf_iv_dflt(sfv),
2867 p_max_value => NULL,
2868 p_min_value => NULL,
2869 p_warning_or_error_flag => NULL,
2870 p_input_value_name => dedn_sf_iv_names(sfv),
2871 p_db_items_flag => dedn_sf_iv_dbi(sfv),
2872 p_costable_type => NULL,
2873 p_hot_default_flag => 'N',
2874 p_business_group_id => p_bg_id,
2875 p_legislation_code => NULL,
2876 p_startup_mode => NULL);
2877
2878 ELSE
2879
2880 v_inpval_id := pay_db_pay_setup.create_input_value (
2881 p_element_name => dedn_ele_names(4),
2882 p_name => dedn_sf_iv_names(sfv),
2883 p_uom => dedn_sf_iv_uom(sfv),
2884 p_uom_code => NULL,
2885 p_mandatory_flag => dedn_sf_iv_mand(sfv),
2886 p_generate_db_item_flag => dedn_sf_iv_dbi(sfv),
2887 p_default_value => dedn_sf_iv_dflt(sfv),
2888 p_min_value => NULL,
2889 p_max_value => NULL,
2890 p_warning_or_error => NULL,
2891 p_lookup_type => dedn_sf_iv_lkp(sfv),
2892 p_formula_id => NULL,
2893 p_hot_default_flag => 'N',
2894 p_display_sequence => dedn_iv_seq,
2895 p_business_group_name => v_bg_name,
2896 p_effective_start_date => g_eff_start_date,
2897 p_effective_end_date => g_eff_end_date);
2898
2899 dedn_sf_iv_ids(sfv) := v_inpval_id;
2900
2901 hr_utility.set_location('hr_upgrade_earnings.ins_uie_input_values ',220);
2902
2903 pay_template_ivs.chk_input_value(
2904 p_element_type_id => dedn_ele_ids(4),
2905 p_legislation_code => g_template_leg_code,
2906 p_val_start_date => g_eff_start_date,
2907 p_val_end_date => g_eff_end_date,
2908 p_insert_update_flag => 'UPDATE',
2909 p_input_value_id => dedn_sf_iv_ids(sfv),
2910 p_rowid => NULL,
2911 p_recurring_flag => 'N',
2912 p_mandatory_flag => dedn_sf_iv_mand(sfv),
2913 p_hot_default_flag => 'N',
2914 p_standard_link_flag => 'N',
2915 p_classification_type => 'N',
2916 p_name => dedn_sf_iv_names(sfv),
2917 p_uom => dedn_sf_iv_uom(sfv),
2918 p_min_value => NULL,
2919 p_max_value => NULL,
2920 p_default_value => dedn_sf_iv_dflt(sfv),
2921 p_lookup_type => dedn_sf_iv_lkp(sfv),
2922 p_formula_id => NULL,
2923 p_generate_db_items_flag => dedn_sf_iv_dbi(sfv),
2924 p_warning_or_error => NULL);
2925
2926 hr_utility.set_location('hr_upgrade_earnings.ins_uie_input_values ',230);
2927
2928 pay_template_ivs.ins_3p_input_values(
2929 p_val_start_date => g_eff_start_date,
2930 p_val_end_date => g_eff_end_date,
2931 p_element_type_id => dedn_ele_ids(4),
2932 p_primary_classification_id => p_primary_class_id,
2933 p_input_value_id => dedn_sf_iv_ids(sfv),
2934 p_default_value => dedn_sf_iv_dflt(sfv),
2935 p_max_value => NULL,
2936 p_min_value => NULL,
2937 p_warning_or_error_flag => NULL,
2938 p_input_value_name => dedn_sf_iv_names(sfv),
2939 p_db_items_flag => dedn_sf_iv_dbi(sfv),
2940 p_costable_type => NULL,
2941 p_hot_default_flag => 'N',
2942 p_business_group_id => p_bg_id,
2943 p_legislation_code => NULL,
2944 p_startup_mode => NULL);
2945
2946 pay_template_ivs.new_input_value (
2947 p_element_type_id => dedn_ele_ids(4),
2948 p_input_value_id => dedn_sf_iv_ids(sfv),
2949 p_costed_flag => 'N',
2950 p_default_value => dedn_sf_iv_dflt(sfv),
2951 p_max_value => NULL,
2952 p_min_value => NULL,
2953 p_warning_or_error => NULL);
2954
2955 END IF;
2956
2957 else
2958
2959 hr_utility.set_location('hr_upgrade_earnings.ins_uie_input_values ',240);
2960
2961 v_inpval_id := already_exists;
2962 dedn_sf_iv_ids(sfv) := v_inpval_id;
2963
2964 end if;
2965
2966 ELSE
2967
2968 -- SF IV NAME is null, no need to create.
2969
2970 NULL;
2971
2972 END IF;
2973
2974 END LOOP;
2975
2976
2977 /*
2978 Create Input values passed from withholding element to ER element are
2979 dedn_er_iv_names(1) =
2980 ER Match FF INPUTS:
2981 Deduction Actually Taken
2982
2983 */
2984
2985 IF p_ele_er_match = 'Y' THEN
2986 dedn_er_iv_names(1) := 'Deduction Actually Taken';
2987 dedn_er_iv_uom(1) := 'Money';
2988 dedn_er_iv_mand(1) := 'X';
2989 dedn_er_iv_dbi(1) := 'N';
2990 dedn_er_iv_lkp(1) := NULL;
2991 dedn_er_iv_dflt(1) := NULL;
2992 ELSE
2993 dedn_er_iv_names(1) := NULL;
2994 END IF;
2995 l_num_er_ivs := 1;
2996
2997 FOR m in 1..l_num_er_ivs LOOP
2998
2999 IF dedn_er_iv_names(m)IS NOT NULL THEN
3000
3001 hr_utility.set_location('hr_upgrade_earnings.ins_uie_formula_processing',400);
3002
3003 already_exists := hr_template_existence.iv_name_exists(
3004 p_ele_id => dedn_ele_ids(5),
3005 p_bg_id => p_bg_id,
3006 p_iv_name => dedn_er_iv_names(m),
3007 p_eff_date => g_eff_start_date);
3008
3009 if already_exists = 0 then
3010
3011 hr_utility.set_location('hr_upgrade_earnings.ins_uie_formula_processing',410);
3012
3013 select max(display_sequence)
3014 into dedn_er_seq
3015 from pay_input_values_f
3016 where element_type_id = dedn_ele_ids(5)
3017 and g_eff_start_date between effective_start_date
3018 and effective_end_date;
3019
3020 dedn_er_seq := dedn_er_seq + 1;
3021
3022 -- 40.4 : Call new API to add input value over life of element if
3023 -- upgrade mode = Yes
3024
3025
3026 IF l_upgrade_mode = 'N' THEN
3027
3028 v_inpval_id := pay_db_pay_setup.create_input_value (
3029 p_element_name => dedn_ele_names(5),
3030 p_name => dedn_er_iv_names(m),
3031 p_uom => dedn_er_iv_uom(m),
3032 p_uom_code => NULL,
3033 p_mandatory_flag => dedn_er_iv_mand(m),
3034 p_generate_db_item_flag => dedn_er_iv_dbi(m),
3035 p_default_value => dedn_er_iv_dflt(m),
3036 p_min_value => NULL,
3037 p_max_value => NULL,
3038 p_warning_or_error => NULL,
3039 p_lookup_type => dedn_er_iv_lkp(m),
3040 p_formula_id => NULL,
3041 p_hot_default_flag => 'N',
3042 p_display_sequence => dedn_er_seq,
3043 p_business_group_name => p_bg_name,
3044 p_effective_start_date => g_eff_start_date,
3045 p_effective_end_date => g_eff_end_date);
3046
3047 dedn_er_iv_ids(m) := v_inpval_id;
3048
3049 hr_utility.set_location('hr_upgrade_earnings.ins_uie_formula_processing',420);
3050
3051 hr_input_values.chk_input_value(
3052 p_element_type_id => dedn_ele_ids(5),
3053 p_legislation_code => g_template_leg_code,
3054 p_val_start_date => g_eff_start_date,
3055 p_val_end_date => g_eff_end_date,
3056 p_insert_update_flag => 'UPDATE',
3057 p_input_value_id => dedn_er_iv_ids(m),
3058 p_rowid => NULL,
3059 p_recurring_flag => 'N',
3060 p_mandatory_flag => dedn_er_iv_mand(m),
3061 p_hot_default_flag => 'N',
3062 p_standard_link_flag => 'N',
3063 p_classification_type => 'N',
3064 p_name => dedn_er_iv_names(m),
3065 p_uom => dedn_er_iv_uom(m),
3066 p_min_value => NULL,
3067 p_max_value => NULL,
3068 p_default_value => dedn_er_iv_dflt(m),
3069 p_lookup_type => dedn_er_iv_lkp(m),
3070 p_formula_id => NULL,
3071 p_generate_db_items_flag => dedn_er_iv_dbi(m),
3072 p_warning_or_error => NULL);
3073
3074 hr_utility.set_location('hr_upgrade_earnings.ins_uie_formula_processing',430);
3075
3076 hr_input_values.ins_3p_input_values(
3077 p_val_start_date => g_eff_start_date,
3078 p_val_end_date => g_eff_end_date,
3079 p_element_type_id => dedn_ele_ids(5),
3080 p_primary_classification_id => p_primary_class_id,
3081 p_input_value_id => dedn_er_iv_ids(m),
3082 p_default_value => dedn_er_iv_dflt(m),
3083 p_max_value => NULL,
3084 p_min_value => NULL,
3085 p_warning_or_error_flag => NULL,
3086 p_input_value_name => dedn_er_iv_names(m),
3087 p_db_items_flag => dedn_er_iv_dbi(m),
3088 p_costable_type => NULL,
3089 p_hot_default_flag => 'N',
3090 p_business_group_id => p_bg_id,
3091 p_legislation_code => NULL,
3092 p_startup_mode => NULL);
3093
3094 ELSE
3095
3096 v_inpval_id := pay_db_pay_setup.create_input_value (
3097 p_element_name => dedn_ele_names(5),
3098 p_name => dedn_er_iv_names(m),
3099 p_uom => dedn_er_iv_uom(m),
3100 p_uom_code => NULL,
3101 p_mandatory_flag => dedn_er_iv_mand(m),
3102 p_generate_db_item_flag => dedn_er_iv_dbi(m),
3103 p_default_value => dedn_er_iv_dflt(m),
3104 p_min_value => NULL,
3105 p_max_value => NULL,
3106 p_warning_or_error => NULL,
3107 p_lookup_type => dedn_er_iv_lkp(m),
3108 p_formula_id => NULL,
3109 p_hot_default_flag => 'N',
3110 p_display_sequence => dedn_er_seq,
3111 p_business_group_name => p_bg_name,
3112 p_effective_start_date => g_eff_start_date,
3113 p_effective_end_date => g_eff_end_date);
3114
3115 dedn_er_iv_ids(m) := v_inpval_id;
3116
3117 hr_utility.set_location('hr_upgrade_earnings.ins_uie_formula_processing',420);
3118
3119 pay_template_ivs.chk_input_value(
3120 p_element_type_id => dedn_ele_ids(5),
3121 p_legislation_code => g_template_leg_code,
3122 p_val_start_date => g_eff_start_date,
3123 p_val_end_date => g_eff_end_date,
3124 p_insert_update_flag => 'UPDATE',
3125 p_input_value_id => dedn_er_iv_ids(m),
3126 p_rowid => NULL,
3127 p_recurring_flag => 'N',
3128 p_mandatory_flag => dedn_er_iv_mand(m),
3129 p_hot_default_flag => 'N',
3130 p_standard_link_flag => 'N',
3131 p_classification_type => 'N',
3132 p_name => dedn_er_iv_names(m),
3133 p_uom => dedn_er_iv_uom(m),
3134 p_min_value => NULL,
3135 p_max_value => NULL,
3136 p_default_value => dedn_er_iv_dflt(m),
3137 p_lookup_type => dedn_er_iv_lkp(m),
3138 p_formula_id => NULL,
3139 p_generate_db_items_flag => dedn_er_iv_dbi(m),
3140 p_warning_or_error => NULL);
3141
3142 hr_utility.set_location('hr_upgrade_earnings.ins_uie_formula_processing',430);
3143
3144 pay_template_ivs.ins_3p_input_values(
3145 p_val_start_date => g_eff_start_date,
3146 p_val_end_date => g_eff_end_date,
3147 p_element_type_id => dedn_ele_ids(5),
3148 p_primary_classification_id => p_primary_class_id,
3149 p_input_value_id => dedn_er_iv_ids(m),
3150 p_default_value => dedn_er_iv_dflt(m),
3151 p_max_value => NULL,
3152 p_min_value => NULL,
3153 p_warning_or_error_flag => NULL,
3154 p_input_value_name => dedn_er_iv_names(m),
3155 p_db_items_flag => dedn_er_iv_dbi(m),
3156 p_costable_type => NULL,
3157 p_hot_default_flag => 'N',
3158 p_business_group_id => p_bg_id,
3159 p_legislation_code => NULL,
3160 p_startup_mode => NULL);
3161
3162 pay_template_ivs.new_input_value (
3163 p_element_type_id => dedn_ele_ids(5),
3164 p_input_value_id => dedn_er_iv_ids(m),
3165 p_costed_flag => 'N',
3166 p_default_value => dedn_er_iv_dflt(m),
3167 p_max_value => NULL,
3168 p_min_value => NULL,
3169 p_warning_or_error => NULL);
3170
3171 END IF;
3172
3173 else
3174
3175 hr_utility.set_location('hr_upgrade_earnings.ins_uie_formula_processing',440);
3176
3177 v_inpval_id := already_exists;
3178 dedn_er_iv_ids(m) := v_inpval_id;
3179
3180 end if;
3181
3182 ELSE
3183
3184 -- ER IV name is null...do not need to create...
3185
3186 NULL;
3187
3188 END IF;
3189
3190 END LOOP;
3191 --
3192 -- Now insert appropriate formula_result_rules for elements
3193 --
3194 /*
3195 PRETAX CALCULATION FORMULA return section
3196 RETURNS: dedn_amt
3197 , pretax_calc_amount
3198 , bene_er_contr
3199 , clear_repl_amt
3200 , clear_addl_amt
3201 , accrued_balance
3202 , arrears_balance
3203 , clear_arrears_flag
3204 , partial_dedns_flag
3205 , total_owed_amt
3206 , towards_owed_flag
3207 , aftertax_calc_amount
3208 */
3209
3210 dedn_calc_frr_name(1) := 'DEDN_AMT';
3211 dedn_calc_frr_type(1) := 'I';
3212 dedn_calc_frr_ele_id(1) := dedn_ele_ids(2);
3213 dedn_calc_frr_iv_id(1) := dedn_wh_iv_ids(1);
3214 dedn_calc_frr_severity(1) := NULL;
3215
3216 dedn_calc_frr_name(2) := 'PRETAX_CALC_AMOUNT';
3217 dedn_calc_frr_type(2) := 'I';
3218 dedn_calc_frr_ele_id(2) := dedn_ele_ids(4);
3219 dedn_calc_frr_iv_id(2) := dedn_sf_iv_ids(7);
3220 dedn_calc_frr_severity(2) := NULL;
3221
3222 dedn_calc_frr_name(3) := 'MESG';
3223 dedn_calc_frr_type(3) := 'M';
3224 dedn_calc_frr_ele_id(3) := NULL;
3225 dedn_calc_frr_iv_id(3) := NULL;
3226 dedn_calc_frr_severity(3) := 'W';
3227
3228 l_num_calc_resrules := 3;
3229
3230 hr_utility.set_location('hr_generate_pretax.ins_dedn_formula_processing',47);
3231
3232 IF UPPER(p_amount_rule) = 'BT' THEN
3233
3234 --
3235 -- In order to create indirect result feeding Employer Charge element for
3236 -- this benefit, we must find the input_value_id for the pay_value of the
3237 -- employer charge element.
3238 --
3239
3240 hr_utility.set_location('hr_generate_pretax.ins_dedn_formula_processing',115);
3241
3242 dedn_calc_frr_name(4) := 'BENE_ER_CONTR';
3243 dedn_calc_frr_type(4) := 'I';
3244 dedn_calc_frr_ele_id(4) := dedn_ele_ids(5);
3245 dedn_calc_frr_iv_id(4) := dedn_payval_id(5);
3246 dedn_calc_frr_severity(4) := NULL;
3247
3248 l_num_calc_resrules := 4;
3249
3250 ELSE
3251
3252 dedn_calc_frr_name(4) := NULL;
3253
3254 l_num_calc_resrules := 4;
3255
3256 END IF;
3257
3258 IF p_ele_proc_type = 'R' THEN
3259
3260 hr_utility.set_location('hr_generate_pretax.ins_uie_inp_values',90);
3261
3262 dedn_calc_frr_name(5) := 'CLEAR_REPL_AMT';
3263 dedn_calc_frr_type(5) := 'I';
3264 dedn_calc_frr_ele_id(5) := dedn_ele_ids(4);
3265 dedn_calc_frr_iv_id(5) := dedn_sf_iv_ids(3);
3266 dedn_calc_frr_severity(5) := NULL;
3267
3268 dedn_calc_frr_name(6) := 'CLEAR_ADDL_AMT';
3269 dedn_calc_frr_type(6) := 'I';
3270 dedn_calc_frr_ele_id(6) := dedn_ele_ids(4);
3271 dedn_calc_frr_iv_id(6) := dedn_sf_iv_ids(4);
3272 dedn_calc_frr_severity(6) := NULL;
3273
3274 l_num_calc_resrules := 6;
3275
3276 ELSE
3277
3278 dedn_calc_frr_name(5) := NULL;
3279 dedn_calc_frr_name(6) := NULL;
3280
3281 l_num_calc_resrules := 6;
3282
3283 END IF;
3284
3285 dedn_calc_frr_name(7) := 'ACCRUED_BALANCE';
3286 dedn_calc_frr_type(7) := 'I';
3287 dedn_calc_frr_ele_id(7) := dedn_ele_ids(2);
3288 dedn_calc_frr_iv_id(7) := dedn_wh_iv_ids(6);
3289 dedn_calc_frr_severity(7) := NULL;
3290
3291 dedn_calc_frr_name(8) := 'ARREARS_BALANCE';
3292 dedn_calc_frr_type(8) := 'I';
3293 dedn_calc_frr_ele_id(8) := dedn_ele_ids(2);
3294 dedn_calc_frr_iv_id(8) := dedn_wh_iv_ids(5);
3295 dedn_calc_frr_severity(8) := NULL;
3296
3297 dedn_calc_frr_name(9) := 'CLEAR_ARREARS_FLAG';
3298 dedn_calc_frr_type(9) := 'I';
3299 dedn_calc_frr_ele_id(9) := dedn_ele_ids(2);
3300 dedn_calc_frr_iv_id(9) := dedn_wh_iv_ids(4);
3301 dedn_calc_frr_severity(9) := NULL;
3302
3303 dedn_calc_frr_name(10) := 'PARTIAL_DEDNS_FLAG';
3304 dedn_calc_frr_type(10) := 'I';
3305 dedn_calc_frr_ele_id(10) := dedn_ele_ids(2);
3306 dedn_calc_frr_iv_id(10) := dedn_wh_iv_ids(7);
3307 dedn_calc_frr_severity(10) := NULL;
3308
3309 dedn_calc_frr_name(11) := 'TOTAL_OWED_AMT';
3310 dedn_calc_frr_type(11) := 'I';
3311 dedn_calc_frr_ele_id(11) := dedn_ele_ids(2);
3312 dedn_calc_frr_iv_id(11) := dedn_wh_iv_ids(2);
3313 dedn_calc_frr_severity(11) := NULL;
3314
3315 dedn_calc_frr_name(12) := 'TOWARDS_OWED_FLAG';
3316 dedn_calc_frr_type(12) := 'I';
3317 dedn_calc_frr_ele_id(12) := dedn_ele_ids(2);
3318 dedn_calc_frr_iv_id(12) := dedn_wh_iv_ids(3);
3319 dedn_calc_frr_severity(12) := NULL;
3320
3321 dedn_calc_frr_name(13) := 'AFTERTAX_CALC_AMOUNT';
3322 dedn_calc_frr_type(13) := 'I';
3323 dedn_calc_frr_ele_id(13) := dedn_ele_ids(2);
3324 dedn_calc_frr_iv_id(13) := dedn_wh_iv_ids(8);
3325 dedn_calc_frr_severity(13) := NULL;
3326
3327 l_num_calc_resrules := 13;
3328
3329 /*
3330 WITHHOLDING formula return section
3331 RETURNS:* dedn_amt
3332 *, cancel_calc_amt
3333 *, not_taken
3334 *, to_arrears
3335 *, to_total_owed
3336 *, STOP_ENTRY
3337 *, stop_calc_entry
3338 *, set_clear
3339 *, mesg
3340 */
3341
3342 dedn_wh_frr_name(1) := 'DEDN_AMT';
3343 dedn_wh_frr_type(1) := 'I';
3344 dedn_wh_frr_ele_id(1) := dedn_ele_ids(1);
3345 dedn_wh_frr_iv_id(1) := dedn_payval_id(1);
3346 dedn_wh_frr_severity(1) := NULL;
3347
3348 dedn_wh_frr_name(2) := 'CANCEL_CALC_AMT';
3349 dedn_wh_frr_type(2) := 'I';
3350 dedn_wh_frr_ele_id(2) := dedn_ele_ids(4);
3351 dedn_wh_frr_iv_id(2) := dedn_sf_iv_ids(6);
3352 dedn_wh_frr_severity(2) := NULL;
3353
3354 dedn_wh_frr_name(3) := 'NOT_TAKEN';
3355 dedn_wh_frr_type(3) := 'I';
3356 dedn_wh_frr_ele_id(3) := dedn_ele_ids(4);
3357 dedn_wh_frr_iv_id(3) := dedn_sf_iv_ids(5);
3358 dedn_wh_frr_severity(3) := NULL;
3359
3360 /* WE MIGHT WANT TO CONDITIONALLY CREATE THESE RESULT RULES...
3361 LOGIC IS BELOW...
3362 */
3363 dedn_wh_frr_name(4) := NULL; -- No longer need stop entry for WH ele.
3364 dedn_wh_frr_type(4) := 'S';
3365 dedn_wh_frr_ele_id(4) := dedn_ele_ids(2);
3366 dedn_wh_frr_iv_id(4) := dedn_payval_id(2);
3367 dedn_wh_frr_severity(4) := NULL;
3368
3369 IF p_ele_proc_type = 'R' THEN
3370
3371 dedn_wh_frr_name(5) := 'STOP_CALC_ENTRY';
3372 dedn_wh_frr_type(5) := 'S';
3373 dedn_wh_frr_ele_id(5) := dedn_ele_ids(1);
3374 dedn_wh_frr_iv_id(5) := dedn_payval_id(1);
3375 dedn_wh_frr_severity(5) := NULL;
3376
3377 ELSE
3378
3379 dedn_wh_frr_name(5) := NULL;
3380
3381 END IF;
3382
3383 dedn_wh_frr_name(6) := 'MESG';
3384 dedn_wh_frr_type(6) := 'M';
3385 dedn_wh_frr_ele_id(6) := NULL;
3386 dedn_wh_frr_iv_id(6) := NULL;
3387 dedn_wh_frr_severity(6) := 'W';
3388
3389 dedn_wh_frr_name(7) := 'ERROR_MESG';
3390 dedn_wh_frr_type(7) := 'M';
3391 dedn_wh_frr_ele_id(7) := NULL;
3392 dedn_wh_frr_iv_id(7) := NULL;
3393 dedn_wh_frr_severity(7) := 'F';
3394
3395 l_num_wh_resrules := 7;
3396
3397 -- Stop Rule checks:
3398
3399 IF UPPER(p_ele_stop_rule) = 'TOTAL REACHED' THEN
3400
3401 -- Note this indirect result feeds "Accrued" inpval on
3402 -- "<ELE_NAME> Special Features" ele.
3403
3404 dedn_wh_frr_name(8) := 'TO_TOTAL_OWED';
3405 dedn_wh_frr_type(8) := 'I';
3406 dedn_wh_frr_ele_id(8) := dedn_ele_ids(4);
3407 dedn_wh_frr_iv_id(8) := dedn_sf_iv_ids(1);
3408 dedn_wh_frr_severity(8) := NULL;
3409
3410 l_num_wh_resrules := 8;
3411
3412 ELSE
3413
3414 dedn_wh_frr_name(8) := NULL;
3415 l_num_wh_resrules := 8;
3416
3417 END IF; -- Stop Rule checks
3418
3419 IF p_arrearage = 'Y' THEN
3420
3421 -- create formula result rule for:
3422 -- (*) to_arrears --> Indirect to <ELE_NAME>.ARREARS_CONTR
3423
3424 hr_utility.set_location('hr_generate_pretax.ins_dedn_formula_processing',450);
3425
3426 dedn_wh_frr_name(9) := 'TO_ARREARS';
3427 dedn_wh_frr_type(9) := 'I';
3428 dedn_wh_frr_ele_id(9) := dedn_ele_ids(4);
3429 dedn_wh_frr_iv_id(9) := dedn_sf_iv_ids(2);
3430 dedn_wh_frr_severity(9) := NULL;
3431
3432 dedn_wh_frr_name(10) := 'SET_CLEAR';
3433 dedn_wh_frr_type(10) := 'U';
3434 dedn_wh_frr_ele_id(10) := dedn_ele_ids(1);
3435 dedn_wh_frr_iv_id(10) := dedn_base_iv_ids(8);
3436 dedn_wh_frr_severity(10) := NULL;
3437
3438 l_num_wh_resrules := 10;
3439
3440 ELSE
3441
3442 dedn_wh_frr_name(9) := NULL;
3443 dedn_wh_frr_name(10) := NULL;
3444 l_num_wh_resrules := 10;
3445
3446 END IF;
3447
3448 IF p_ele_er_match = 'Y' THEN
3449 dedn_wh_frr_name(11) := 'DEDN_AMT';
3450 dedn_wh_frr_type(11) := 'I';
3451 dedn_wh_frr_ele_id(11) := dedn_ele_ids(5);
3452 dedn_wh_frr_iv_id(11) := dedn_er_iv_ids(1);
3453 dedn_wh_frr_severity(11) := NULL;
3454 l_num_wh_resrules := 11;
3455 ELSE
3456 dedn_wh_frr_name(11) := NULL;
3457 l_num_wh_resrules := 11;
3458 END IF;
3459
3460
3461 -- Create formula result rules for WITHHOLDING element.
3462 FOR n in 1..l_num_wh_resrules LOOP
3463
3464 IF dedn_wh_frr_name(n) IS NOT NULL THEN
3465
3466 hr_utility.set_location('hr_upgrade_earnings.ins_uie_input_values ',270);
3467
3468 already_exists := hr_template_existence.result_rule_exists(
3469 p_spr_id => dedn_statproc_rule_id(2),
3470 p_frr_name => dedn_wh_frr_name(n),
3471 p_iv_id => dedn_wh_frr_iv_id(n),
3472 p_bg_id => p_bg_id,
3473 p_ele_id => dedn_wh_frr_ele_id(n),
3474 p_eff_date => g_eff_start_date);
3475
3476 if already_exists = 0 then
3477
3478 hr_utility.set_location('hr_upgrade_earnings.ins_uie_input_values ',280);
3479
3480 v_fres_rule_id := pay_formula_results.ins_form_res_rule (
3481 p_business_group_id => p_bg_id,
3482 p_legislation_code => NULL,
3483 p_legislation_subgroup => g_template_leg_subgroup,
3484 p_effective_start_date => g_eff_start_date,
3485 p_effective_end_date => g_eff_end_date,
3486 p_status_processing_rule_id => dedn_statproc_rule_id(2),
3487 p_input_value_id => dedn_wh_frr_iv_id(n),
3488 p_result_name => dedn_wh_frr_name(n),
3489 p_result_rule_type => dedn_wh_frr_type(n),
3490 p_severity_level => dedn_wh_frr_severity(n),
3491 p_element_type_id => dedn_wh_frr_ele_id(n));
3492
3493 else
3494
3495 hr_utility.set_location('hr_upgrade_earnings.ins_uie_input_values ',290);
3496 v_fres_rule_id := already_exists;
3497
3498 end if;
3499
3500 ELSE
3501
3502 NULL; -- base resrule name is null, no need to create.
3503
3504 END IF;
3505
3506 END LOOP;
3507
3508
3509
3510 -- Create formula result rules for calc element.
3511 FOR n in 1..l_num_calc_resrules LOOP
3512
3513 IF dedn_calc_frr_name(n) IS NOT NULL THEN
3514
3515 hr_utility.set_location('hr_upgrade_earnings.ins_uie_input_values ',270);
3516
3517 already_exists := hr_template_existence.result_rule_exists(
3518 p_spr_id => dedn_statproc_rule_id(1),
3519 p_frr_name => dedn_calc_frr_name(n),
3520 p_iv_id => dedn_calc_frr_iv_id(n),
3521 p_bg_id => p_bg_id,
3522 p_ele_id => dedn_calc_frr_ele_id(n),
3523 p_eff_date => g_eff_start_date);
3524
3525 if already_exists = 0 then
3526
3527 hr_utility.set_location('hr_upgrade_earnings.ins_uie_input_values ',280);
3528
3529 v_fres_rule_id := pay_formula_results.ins_form_res_rule (
3530 p_business_group_id => p_bg_id,
3531 p_legislation_code => NULL,
3532 p_legislation_subgroup => g_template_leg_subgroup,
3533 p_effective_start_date => g_eff_start_date,
3534 p_effective_end_date => g_eff_end_date,
3535 p_status_processing_rule_id => dedn_statproc_rule_id(1),
3536 p_input_value_id => dedn_calc_frr_iv_id(n),
3537 p_result_name => dedn_calc_frr_name(n),
3538 p_result_rule_type => dedn_calc_frr_type(n),
3539 p_severity_level => dedn_calc_frr_severity(n),
3540 p_element_type_id => dedn_calc_frr_ele_id(n));
3541
3542 else
3543
3544 hr_utility.set_location('hr_upgrade_earnings.ins_uie_input_values ',290);
3545 v_fres_rule_id := already_exists;
3546
3547 end if;
3548
3549 ELSE
3550
3551 NULL; -- Calc resrule name is null, no need to create.
3552
3553 END IF;
3554
3555 END LOOP;
3556
3557 END ins_dedn_formula_processing;
3558
3559
3560 PROCEDURE create_pretax_class_feeds ( p_busgrp_id in number,
3561 p_inpval_id in number,
3562 p_eff_start_date in date) IS
3563
3564 -- This procedure creates feeds to all pretax classification balances
3565 -- in STU and the current business group - except to Startup balances
3566 -- Net and Payments. The usage of this procedure is for cancelling
3567 -- out the calculated pretax deduction amount...which does not feed
3568 -- Net or Payments, but does feed all other pretax class bals.
3569
3570 l_bal_id number(9);
3571
3572 already_exists number;
3573
3574 CURSOR get_pretax_bals IS
3575 SELECT pbc.balance_type_id
3576 FROM pay_balance_classifications pbc,
3577 pay_element_classifications pec,
3578 pay_balance_types pbt
3579 WHERE nvl(pbc.business_group_id, p_busgrp_id) = p_busgrp_id
3580 AND nvl(pbc.legislation_code, 'US') = 'US'
3581 AND pbc.classification_id = pec.classification_id
3582 AND pec.classification_name = 'Pre-Tax Deductions'
3583 AND pec.business_group_id is null
3584 AND pec.legislation_code = 'US'
3585 AND pbc.balance_type_id = pbt.balance_type_id
3586 AND pbt.balance_name not in ('Net', 'Payments');
3587
3588 BEGIN
3589
3590 open get_pretax_bals;
3591
3592 loop
3593
3594 fetch get_pretax_bals into l_bal_id;
3595 exit when get_pretax_bals%notfound;
3596
3597 already_exists := hr_template_existence.bal_feed_exists (
3598 p_bal_id => l_bal_id,
3599 p_bg_id => p_busgrp_id,
3600 p_iv_id => p_inpval_id,
3601 p_eff_date => p_eff_start_date);
3602
3603 IF already_exists = 0 THEN
3604
3605 hr_balances.ins_balance_feed(
3606 p_option => 'INS_MANUAL_FEED',
3607 p_input_value_id => p_inpval_id,
3608 p_element_type_id => NULL,
3609 p_primary_classification_id => NULL,
3610 p_sub_classification_id => NULL,
3611 p_sub_classification_rule_id => NULL,
3612 p_balance_type_id => l_bal_id,
3613 p_scale => '1',
3614 p_session_date => p_eff_start_date,
3615 p_business_group => p_busgrp_id,
3616 p_legislation_code => NULL,
3617 p_mode => 'USER');
3618
3619 END IF;
3620
3621 end loop;
3622
3623 close get_pretax_bals;
3624
3625 END create_pretax_class_feeds;
3626
3627
3628 PROCEDURE create_pretax_cat_feeds ( p_busgrp_id in number,
3629 p_src_iv_id in number,
3630 p_inpval_id in number,
3631 p_eff_start_date in date) IS
3632
3633 -- This procedure creates feeds to all pretax CATEGORY balances
3634 -- in STU and the current business group - except to Startup balances
3635 -- Net and Payments. The usage of this procedure is for cancelling
3636 -- out the calculated pretax deduction amount...which does not feed
3637 -- Net or Payments, but does feed all other pretax cat bals.
3638 -- This will be accomplished by copying all feeds that exist for the
3639 -- Pay Value of the Base element.
3640
3641 l_bal_id number(9);
3642
3643 already_exists number;
3644
3645 CURSOR get_pretax_catbals IS
3646 SELECT pbf.balance_type_id
3647 FROM pay_balance_feeds_f pbf,
3648 pay_balance_types pbt
3649 WHERE pbf.input_value_id = p_src_iv_id
3650 AND nvl(pbf.business_group_id, p_busgrp_id) = p_busgrp_id
3651 AND nvl(pbf.legislation_code, 'US') = 'US'
3652 AND pbt.balance_type_id = pbf.balance_type_id
3653 AND pbt.balance_name not in ('Net', 'Payments');
3654
3655 BEGIN
3656
3657 open get_pretax_catbals;
3658
3659 loop
3660
3661 fetch get_pretax_catbals into l_bal_id;
3662 exit when get_pretax_catbals%notfound;
3663
3664 already_exists := hr_template_existence.bal_feed_exists (
3665 p_bal_id => l_bal_id,
3666 p_bg_id => p_busgrp_id,
3667 p_iv_id => p_inpval_id,
3668 p_eff_date => p_eff_start_date);
3669
3670 IF already_exists = 0 THEN
3671
3672 hr_balances.ins_balance_feed(
3673 p_option => 'INS_MANUAL_FEED',
3674 p_input_value_id => p_inpval_id,
3675 p_element_type_id => NULL,
3676 p_primary_classification_id => NULL,
3677 p_sub_classification_id => NULL,
3678 p_sub_classification_rule_id => NULL,
3679 p_balance_type_id => l_bal_id,
3680 p_scale => '1',
3681 p_session_date => p_eff_start_date,
3682 p_business_group => p_busgrp_id,
3683 p_legislation_code => NULL,
3684 p_mode => 'USER');
3685
3686 END IF;
3687
3688 end loop;
3689
3690 close get_pretax_catbals;
3691
3692 END create_pretax_cat_feeds;
3693
3694
3695 ----------------------- ins_deduction_template Main ------------------------
3696 --
3697 -- Main Procedure
3698 --
3699
3700 BEGIN
3701
3702 --
3703 -- Set session date
3704
3705 hr_utility.set_location('hr_generate_pretax.ins_deduction_template',10);
3706
3707 pay_db_pay_setup.set_session_date(nvl(p_ele_eff_start_date, sysdate));
3708 g_eff_start_date := nvl(p_ele_eff_start_date, sysdate);
3709 g_eff_end_date := nvl(p_ele_eff_end_date, c_end_of_time);
3710
3711 -- Set "globals": v_bg_name
3712
3713 hr_utility.set_location('hr_generate_pretax.ins_deduction_template',20);
3714 select name
3715 into v_bg_name
3716 from per_business_groups
3717 where business_group_id = p_bg_id;
3718
3719 --------------------- Create Balances Types and Defined Balances --------------
3720
3721 /*
3722 dedn_assoc_bal_ids(1) := Primary Balance;
3723 dedn_assoc_bal_ids(2) := Additional Balance;
3724 dedn_assoc_bal_ids(3) := Replacement Balance;
3725 dedn_assoc_bal_ids(4) := Not Taken Balance;
3726 dedn_assoc_bal_ids(5) := Accrued Balance;
3727 dedn_assoc_bal_ids(6) := Arrears Balance;
3728 dedn_assoc_bal_ids(7) := Pretax-Able Balance;
3729 dedn_assoc_bal_ids(8) := Eligible Comp Balance;
3730
3731 */
3732
3733
3734 --
3735 -- Create associated balances for deductions.
3736 --
3737 dedn_assoc_bal_names(1) := p_ele_name;
3738 dedn_assoc_bal_rep_names(1) := p_ele_name;
3739 dedn_assoc_bal_uom(1) := 'Money';
3740
3741 dedn_assoc_bal_names(2) := SUBSTR(p_ele_name, 1, 67)||' Additional';
3742 dedn_assoc_bal_uom(2) := 'Money';
3743 v_bal_rpt_name := SUBSTR(p_ele_name, 1, 17)||' Additional';
3744 dedn_assoc_bal_rep_names(2) := v_bal_rpt_name;
3745
3746 dedn_assoc_bal_names(3) := p_ele_name||' Replacement';
3747 dedn_assoc_bal_uom(3) := 'Money';
3748 v_bal_rpt_name := SUBSTR(p_ele_name, 1, 17)||' Replacement';
3749 dedn_assoc_bal_rep_names(3) := v_bal_rpt_name;
3750
3751 dedn_assoc_bal_names(4) := SUBSTR(p_ele_name, 1, 67)||' Not Taken';
3752 dedn_assoc_bal_uom(4) := 'Money';
3753 v_bal_rpt_name := SUBSTR(p_ele_name, 1, 17)||' Not Taken';
3754 dedn_assoc_bal_rep_names(4) := v_bal_rpt_name;
3755
3756 dedn_assoc_bal_names(5) := substr(p_ele_name, 1, 71)||' Accrued';
3757 dedn_assoc_bal_uom(5) := 'Money';
3758 v_bal_rpt_name := substr(p_ele_name, 1, 21)||' Accrued';
3759 dedn_assoc_bal_rep_names(5) := v_bal_rpt_name;
3760
3761 dedn_assoc_bal_names(6) := substr(p_ele_name, 1, 71)||' Arrears';
3762 dedn_assoc_bal_uom(6) := 'Money';
3763 v_bal_rpt_name := substr(p_ele_name, 1, 21)||' Arrears';
3764 dedn_assoc_bal_rep_names(6) := v_bal_rpt_name;
3765
3766 dedn_assoc_bal_names(7) := substr(p_ele_name, 1, 71)||' Able';
3767 dedn_assoc_bal_uom(7) := 'Money';
3768 v_bal_rpt_name := substr(p_ele_name, 1, 24)||' Able';
3769 dedn_assoc_bal_rep_names(7) := v_bal_rpt_name;
3770
3771 -- Begin new balance for Eligible Compensations
3772 dedn_assoc_bal_names(8) := substr(p_ele_name, 1, 25)||' Eligible Comp';
3773 dedn_assoc_bal_uom(8) := 'Money';
3774 v_bal_rpt_name := substr(p_ele_name, 1, 16)||' Eligible Comp';
3775 dedn_assoc_bal_rep_names(8) := v_bal_rpt_name;
3776
3777
3778 l_num_assoc_bals := 8;
3779
3780 -- Create associated balance types.
3781 FOR i in 1..l_num_assoc_bals LOOP
3782
3783 -- Check for existence before creating baltype.
3784 -- If already exists, set dedn_assoc_bal_id(i) appropriately for future reference.
3785
3786 already_exists := hr_template_existence.bal_exists(
3787 p_bg_id => p_bg_id,
3788 p_bal_name => dedn_assoc_bal_names(i),
3789 p_eff_date => g_eff_start_date);
3790
3791 if already_exists = 0 then
3792
3793 hr_utility.set_location('hr_upgrade_earnings.upgrade_template',50);
3794
3795 -- ARE WE ABSOLUTELY COMFORTABLE WITH REPORTING NAME = BAL NAME?!
3796 -- DESC PAY_BALANCE_TYPES TO SEE COL LENGTHS.
3797
3798 -- Check balance name is unique to balances within this BG.
3799 pay_balance_types_pkg.chk_balance_type(
3800 p_row_id => NULL,
3801 p_business_group_id => p_bg_id,
3802 p_legislation_code => NULL,
3803 p_balance_name => dedn_assoc_bal_names(i),
3804 p_reporting_name => dedn_assoc_bal_rep_names(i),
3805 p_assignment_remuneration_flag => 'N');
3806
3807 hr_utility.set_location('hr_upgrade_earnings.upgrade_template',60);
3808
3809 -- Also check balance name is unique to Startup data balances.
3810 pay_balance_types_pkg.chk_balance_type(
3811 p_row_id => NULL,
3812 p_business_group_id => NULL,
3813 p_legislation_code => 'US',
3814 p_balance_name => dedn_assoc_bal_names(i),
3815 p_reporting_name => dedn_assoc_bal_rep_names(i),
3816 p_assignment_remuneration_flag => 'N');
3817
3818 hr_utility.set_location('hr_upgrade_earnings.upgrade_template',70);
3819
3820 v_bal_type_id := pay_db_pay_setup.create_balance_type(
3821 p_balance_name => dedn_assoc_bal_names(i),
3822 p_uom => dedn_assoc_bal_uom(i),
3823 p_reporting_name => dedn_assoc_bal_rep_names(i),
3824 p_business_group_name => v_bg_name,
3825 p_legislation_code => NULL,
3826 p_legislation_subgroup => NULL);
3827
3828 hr_utility.set_location('hr_upgrade_earnings.upgrade_template',80);
3829
3830 dedn_assoc_bal_ids(i) := v_bal_type_id;
3831
3832 else
3833
3834 hr_utility.set_location('hr_upgrade_earnings.upgrade_template',90);
3835
3836 dedn_assoc_bal_ids(i) := already_exists;
3837
3838 end if;
3839
3840 --
3841 -- Defined Balances (ie. balance type associated with a dimension)
3842 --
3843 hr_utility.set_location('hr_upgrade_earnings.upgrade_template',100);
3844
3845 do_defined_balances( p_bal_name => dedn_assoc_bal_names(i),
3846 p_bg_name => v_bg_name);
3847
3848 hr_utility.set_location('hr_upgrade_earnings.upgrade_template',110);
3849
3850 if dedn_assoc_bal_rep_names(i) like '%Eligible%' then
3851
3852 open get_reg_earn_feeds(p_bg_id);
3853 loop
3854 FETCH get_reg_earn_feeds INTO l_reg_earn_classification_id,
3855 l_reg_earn_business_group_id, l_reg_earn_legislation_code,
3856 l_reg_earn_balance_type_id, l_reg_earn_input_value_id,
3857 l_reg_earn_scale, l_reg_earn_element_type_id;
3858 EXIT WHEN get_reg_earn_feeds%NOTFOUND;
3859
3860 hr_balances.ins_balance_feed(
3861 p_option => 'INS_MANUAL_FEED',
3862 p_input_value_id => l_reg_earn_input_value_id,
3863 p_element_type_id => l_reg_earn_element_type_id,
3864 p_primary_classification_id => l_reg_earn_classification_id,
3865 p_sub_classification_id => NULL,
3866 p_sub_classification_rule_id => NULL,
3867 p_balance_type_id => dedn_assoc_bal_ids(i),
3868 p_scale => l_reg_earn_scale,
3869 p_session_date => g_eff_start_date,
3870 p_business_group => p_bg_id,
3871 p_legislation_code => NULL,
3872 p_mode => 'USER');
3873
3874 end loop;
3875 close get_reg_earn_feeds;
3876
3877 end if;
3878
3879 END LOOP;
3880
3881 --
3882 ----------------------- Create Element Type -----------------------------
3883 --
3884
3885 --
3886 -- Need to determine and get skip rule formula id and pass it
3887 -- create_element.
3888 --
3889
3890 hr_utility.set_location('hr_generate_pretax.upgrade_template',10);
3891
3892 IF UPPER(p_ele_start_rule) = 'CHAINED' THEN
3893
3894 hr_utility.set_location('hr_generate_pretax.upgrade_template',15);
3895
3896 SELECT FF.formula_id
3897 INTO v_skip_formula_id
3898 FROM ff_formulas_f FF
3899 WHERE FF.formula_name = 'CHAINED_SKIP_FORMULA'
3900 AND FF.business_group_id IS NULL
3901 AND FF.legislation_code = g_template_leg_code
3902 AND p_ele_eff_start_date >= FF.effective_start_date
3903 AND p_ele_eff_start_date <= FF.effective_end_date;
3904
3905 ELSIF UPPER(p_ele_start_rule) = 'ET' THEN
3906
3907 hr_utility.set_location('hr_generate_pretax.upgrade_template',20);
3908 SELECT FF.formula_id
3909 INTO v_skip_formula_id
3910 FROM ff_formulas_f FF
3911 WHERE FF.formula_name = 'THRESHOLD_SKIP_FORMULA'
3912 AND FF.business_group_id IS NULL
3913 AND FF.legislation_code = g_template_leg_code
3914 AND p_ele_eff_start_date >= FF.effective_start_date
3915 AND p_ele_eff_start_date <= FF.effective_end_date;
3916
3917 ELSE -- Just check skip rule and separate check flag.
3918
3919 hr_utility.set_location('hr_generate_pretax.upgrade_template',25);
3920
3921 SELECT FF.formula_id
3922 INTO v_skip_formula_id
3923 FROM ff_formulas_f FF
3924 WHERE FF.formula_name = 'FREQ_RULE_SKIP_FORMULA'
3925 AND FF.business_group_id IS NULL
3926 AND FF.legislation_code = g_template_leg_code
3927 AND p_ele_eff_start_date >= FF.effective_start_date
3928 AND p_ele_eff_start_date <= FF.effective_end_date;
3929
3930 END IF;
3931
3932 --
3933 -- Find what ele info category will be for SCL.
3934 --
3935
3936 IF UPPER(p_ele_classification) = 'PRE-TAX DEDUCTIONS' THEN
3937
3938 g_ele_info_cat := 'US_PRE-TAX DEDUCTIONS';
3939
3940 ELSE
3941
3942 g_ele_info_cat := NULL;
3943
3944 END IF;
3945 --
3946
3947 -- Need to find PRIMARY_CLASSIFICATION_ID of element type.
3948 -- For future calls to various API.
3949 --
3950
3951 hr_utility.set_location('hr_generate_pretax.ins_deduction_template',53);
3952
3953 begin
3954
3955 select distinct(classification_id)
3956 into v_primary_class_id
3957 from pay_element_classifications
3958 where upper(classification_name) = upper(p_ele_classification)
3959 and business_group_id is null
3960 and legislation_code = 'US';
3961
3962 exception when no_data_found then
3963
3964 hr_utility.set_location('hr_upgrade_earnings.upgrade_template',999);
3965 hr_utility.set_location('*** Error: Element Classification NOT FOUND. ***',999);
3966
3967
3968 end;
3969
3970 hr_utility.set_location('pyusuiet',130);
3971
3972 begin
3973
3974 SELECT default_low_priority,
3975 default_high_priority
3976 INTO v_class_lo_priority,
3977 v_class_hi_priority
3978 FROM pay_element_classifications
3979 WHERE classification_id = v_primary_class_id
3980 AND business_group_id is null
3981 AND legislation_code = 'US';
3982
3983 exception when no_data_found then
3984
3985 hr_utility.set_location('hr_upgrade_earnings.upgrade_template',999);
3986 hr_utility.set_location('*** Error: Classification priorities NOT FOUND. ***',999);
3987
3988 end;
3989
3990 -- Find default priority for Involuntary Deductions classification
3991 select default_high_priority
3992 into l_invol_dflt_prio
3993 from pay_element_classifications
3994 where UPPER(classification_name) = UPPER(g_invol_class_name)
3995 and business_group_id is null
3996 and legislation_code = 'US';
3997
3998 /*
3999 dedn_ele_ids(1) = Base
4000 dedn_ele_ids(2) = Withholding
4001 dedn_ele_ids(3) = Special Inputs
4002 dedn_ele_ids(4) = Special Features
4003 dedn_ele_ids(5) = Employer portion of Benefit dedn (ER)
4004
4005 Note, with this configuration we will lost ability in calculator to
4006 find _BEN_EE_CONTR AND _BEN_ER_CONTR database items for benefit dedn...
4007 ie. b/c the dbi are based on the element type on which Coverage is entered.
4008 I think the best way to handle this is to create new database items
4009 for the Calculator to use which can check to see which element type has
4010 the calculator element type as element_information20 - ie. the base
4011 element which has this calculator associated to it...and go from there...
4012 Should be a minor modification to the existing dbi for ben ee and er contr.
4013 */
4014
4015 -- Element and payroll formula parameter settings:
4016
4017 dedn_ele_names(1) := p_ele_name;
4018 dedn_ele_repnames(1) := p_ele_reporting_name;
4019 dedn_ele_class(1) := p_ele_classification;
4020 dedn_ele_cat(1) := p_ele_category;
4021 dedn_ele_proc_type(1) := p_ele_processing_type;
4022 dedn_ele_desc(1) := p_ele_description;
4023 dedn_ele_priority(1) := p_ele_priority;
4024 dedn_ele_start_rule(1) := p_ele_start_rule;
4025 dedn_indirect_only(1) := 'N';
4026 dedn_mix_category(1) := p_mix_flag; -- Make sure to set ddf somewhere...
4027 dedn_skip_formula(1) := v_skip_formula_id;
4028 dedn_std_link(1) := p_ele_standard_link;
4029
4030 -- This pretax withholding element should be one more than
4031 -- the priority on all generated
4032 -- involuntary deductions so they will always process
4033 -- in the correct order, ie. AFTER any wage attachments.
4034
4035 l_wh_ele_priority := l_invol_dflt_prio;
4036 -- This is the best we can do to ensure pretax dedns process after
4037 -- involuntary dedns (wage attachments)...that is to make the
4038 -- processing priority of the withholding element the highest (last
4039 -- processed) priority available for involuntary deductions.
4040
4041 dedn_ele_names(2) := p_ele_name||' Withholding';
4042 dedn_ele_repnames(2) := p_ele_reporting_name||' WH';
4043 dedn_ele_class(2) := p_ele_classification;
4044 dedn_ele_cat(2) := p_ele_category;
4045 dedn_ele_proc_type(2) := 'N';
4046 dedn_ele_desc(2) := p_ele_description;
4047 dedn_ele_priority(2) := l_wh_ele_priority;
4048 dedn_ele_start_rule(2) := p_ele_start_rule;
4049 dedn_indirect_only(2) := 'Y'; -- Does it have to be Y?
4050 dedn_mix_category(2) := p_mix_flag; -- Make sure to set ddf somewhere...
4051 dedn_skip_formula(2) := v_skip_formula_id;
4052 dedn_std_link(2) := 'N';
4053
4054 dedn_ele_names(3) := SUBSTR(p_ele_name, 1, 61)||' Special Inputs';
4055 dedn_ele_repnames(3) := SUBSTR(p_ele_reporting_name, 1, 27)||' SI';
4056 dedn_ele_class(3) := p_ele_classification;
4057 dedn_ele_cat(3) := p_ele_category;
4058 dedn_ele_proc_type(3) := 'N';
4059 dedn_ele_desc(3) := 'Generated adjustments element for '||p_ele_name;
4060 dedn_ele_priority(3) := v_class_lo_priority;
4061 dedn_ele_start_rule(3) := 'OE';
4062 dedn_indirect_only(3) := 'N';
4063 dedn_mix_category(3) := p_mix_flag; -- Make sure to set ddf somewhere...
4064 dedn_skip_formula(3) := NULL;
4065 dedn_std_link(3) := 'N';
4066
4067 dedn_ele_names(4) := SUBSTR(p_ele_name, 1, 61)||' Special Features';
4068 dedn_ele_repnames(4) := SUBSTR(p_ele_reporting_name, 1, 27)||' SF';
4069 dedn_ele_class(4) := p_ele_classification;
4070 dedn_ele_cat(4) := p_ele_category;
4071 dedn_ele_proc_type(4) := 'N';
4072 dedn_ele_desc(4) := 'Generated results element for '||p_ele_name;
4073 dedn_ele_priority(4) := v_class_hi_priority;
4074 dedn_ele_start_rule(4) := 'OE';
4075 dedn_indirect_only(4) := 'Y';
4076 dedn_mix_category(4) := NULL;
4077 dedn_skip_formula(4) := NULL;
4078 dedn_std_link(4) := 'N';
4079
4080 l_num_eles := 4;
4081
4082 IF p_ele_amount_rule = 'BT' or p_ele_er_match = 'Y' THEN
4083
4084 hr_utility.set_location('hr_generate_pretax.ins_deduction_template',55);
4085
4086 select default_priority
4087 into v_emp_liab_dflt_prio
4088 from pay_element_classifications
4089 where classification_name = 'Employer Liabilities'
4090 /* added check for legislation_code BUG 912994 */
4091 and legislation_code = g_template_leg_code;
4092
4093 dedn_ele_names(5) := SUBSTR(p_ele_name, 1, 77)||' ER';
4094 dedn_ele_repnames(5) := SUBSTR(p_ele_name, 1, 27)||' ER';
4095 dedn_ele_class(5) := 'Employer Liabilities';
4096 dedn_ele_cat(5) := 'Benefits';
4097 dedn_ele_proc_type(5) := 'N';
4098 dedn_ele_desc(5) := 'Employer portion of benefit.';
4099 dedn_ele_priority(5) := v_emp_liab_dflt_prio;
4100 dedn_ele_start_rule(5) := NULL;
4101 dedn_indirect_only(5) := 'N';
4102 dedn_mix_category(5) := NULL;
4103 dedn_skip_formula(5) := NULL;
4104 dedn_std_link(5) := 'N';
4105
4106 l_num_eles := 5;
4107
4108 ELSE
4109
4110 dedn_ele_names(5) := null;
4111 l_num_eles := 5;
4112
4113 END IF; -- BENE ER Ele
4114
4115 -- Create all pretax configuration elements.
4116 for x in 1..l_num_eles LOOP
4117
4118 IF dedn_ele_names(x) IS NOT NULL THEN
4119
4120 hr_utility.set_location('hr_generate_pretax.ins_deduction_template',51);
4121
4122 v_ele_type_id := ins_dedn_ele_type ( dedn_ele_names(x),
4123 dedn_ele_repnames(x),
4124 dedn_ele_desc(x),
4125 dedn_ele_class(x),
4126 dedn_ele_cat(x),
4127 dedn_ele_start_rule(x),
4128 dedn_ele_proc_type(x),
4129 dedn_ele_priority(x),
4130 dedn_std_link(x),
4131 dedn_skip_formula(x),
4132 dedn_indirect_only(x),
4133 g_eff_start_date,
4134 g_eff_end_date,
4135 v_bg_name,
4136 p_bg_id);
4137
4138 dedn_ele_ids(x) := v_ele_type_id;
4139
4140 -- Make pay value non enterable.
4141
4142 v_pay_value_name := hr_input_values.get_pay_value_name(g_template_leg_code);
4143
4144 hr_utility.set_location('hr_generate_pretax.ins_deduction_template',53);
4145
4146 -- hr_utility.trace('Updating '||v_pay_value_name||' for '||dedn_ele_names(x));
4147
4148 UPDATE pay_input_values_f
4149 SET mandatory_flag = 'X'
4150 WHERE element_type_id = v_ele_type_id
4151 AND name = v_pay_value_name;
4152
4153 SELECT input_value_id
4154 INTO v_payval_id
4155 FROM pay_input_values_f
4156 WHERE element_type_id = v_ele_type_id
4157 AND name = v_pay_value_name;
4158
4159 hr_utility.set_location('hr_upgrade_earnings. upgrade_template',157);
4160
4161 dedn_payval_id(x) := v_payval_id;
4162
4163 ELSE
4164
4165 NULL;
4166
4167 END IF;
4168
4169 END LOOP;
4170
4171 hr_utility.set_location('hr_generate_pretax.ins_deduction_template',57);
4172
4173 IF p_ele_amount_rule = 'BT' or p_ele_er_match = 'Y' THEN
4174 --
4175 -- Create "Primary" balance for ER Liab and "associate" appropriately.
4176 -- Is there any way to table these optional objects that go with optional elements?
4177 --
4178
4179 hr_utility.set_location('hr_generate_pretax.ins_deduction_template',58);
4180
4181 v_balance_name := dedn_ele_names(5);
4182 v_bal_rpt_name := dedn_ele_repnames(5);
4183
4184 -- Check for existence before creating...
4185
4186 already_exists := hr_template_existence.bal_exists(
4187 p_bg_id => p_bg_id,
4188 p_bal_name => v_balance_name,
4189 p_eff_date => g_eff_start_date);
4190
4191 if already_exists = 0 then
4192
4193 v_er_charge_baltype_id := pay_db_pay_setup.create_balance_type(
4194 p_balance_name => v_balance_name,
4195 p_uom => 'Money',
4196 p_reporting_name => v_bal_rpt_name,
4197 p_business_group_name => v_bg_name,
4198 p_legislation_code => NULL,
4199 p_legislation_subgroup => NULL);
4200
4201 else
4202
4203 v_er_charge_baltype_id := already_exists;
4204
4205 end if;
4206
4207 do_defined_balances( p_bal_name => v_balance_name,
4208 p_bg_name => v_bg_name);
4209
4210 -- Associate primary balance...need to look at emp liability ddf first!!!
4211 -- added element_information_Category 12-JUL-00
4212 update pay_element_types_f
4213 set ELEMENT_INFORMATION_CATEGORY='US_EMPLOYER LIABILITIES' ,
4214 element_information10 = v_er_charge_baltype_id
4215 where element_type_id = dedn_ele_ids(5)
4216 and g_eff_start_date between effective_start_date and effective_end_date;
4217
4218 --
4219 -- Primary balance feeds
4220 --
4221
4222 already_exists := hr_template_existence.bal_feed_exists (
4223 p_bal_id => v_er_charge_baltype_id,
4224 p_bg_id => p_bg_id,
4225 p_iv_id => dedn_payval_id(5),
4226 p_eff_date => g_eff_start_date );
4227
4228 if ALREADY_EXISTS = 0 then
4229
4230 hr_utility.set_location('hr_generate_pretax.ins_deduction_template',90);
4231
4232 hr_balances.ins_balance_feed(
4233 p_option => 'INS_MANUAL_FEED',
4234 p_input_value_id => dedn_payval_id(5),
4235 p_element_type_id => NULL,
4236 p_primary_classification_id => NULL,
4237 p_sub_classification_id => NULL,
4238 p_sub_classification_rule_id => NULL,
4239 p_balance_type_id => v_er_charge_baltype_id,
4240 p_scale => '1',
4241 p_session_date => g_eff_start_date,
4242 p_business_group => p_bg_id,
4243 p_legislation_code => NULL,
4244 p_mode => 'USER');
4245
4246 end if; -- feed exists.
4247
4248 END IF; -- Benefit
4249
4250 --
4251 -------------------------- Insert Formula Processing records -------------
4252 --
4253
4254 hr_utility.set_location('hr_generate_pretax.ins_deduction_template',68);
4255
4256 ins_dedn_formula_processing ( dedn_ele_names(1),
4257 v_primary_class_id,
4258 p_ele_classification,
4259 p_ele_category,
4260 p_ele_processing_type,
4261 p_ele_amount_rule,
4262 p_ele_proc_runtype,
4263 p_ele_start_rule,
4264 p_ele_stop_rule,
4265 p_ele_ee_bond,
4266 p_ele_paytab_name,
4267 p_ele_paytab_col,
4268 p_ele_paytab_row_type,
4269 p_ele_arrearage,
4270 p_ele_partial_dedn,
4271 v_er_charge_eletype_id,
4272 v_er_charge_payval_id,
4273 p_bg_id,
4274 p_mix_flag,
4275 g_eff_start_date,
4276 g_eff_end_date,
4277 v_bg_name);
4278
4279 hr_utility.set_location('hr_generate_pretax.ins_deduction_template',69);
4280
4281 --
4282 ------------------------ Insert Balance Feeds -------------------------
4283 --
4284
4285 -- First, call the "category feeder" API which creates manual pay value feeds
4286 -- to pre-existing balances depending on the element classn/category.
4287 -- (Added by ALLEE - 5-MAY-1995) Pass 'g_ele_eff_start_date' to
4288 -- create_category_feeds in order for datetrack to work.
4289 -- This call presumably feeds the Section 125 and 401k balances
4290 -- when the deduction Classification/Category = PreTax/125 or Deferred Comp
4291 -- as appropriate.
4292
4293
4294 pay_us_ctgy_feeds_pkg.create_category_feeds(
4295 p_element_type_id => dedn_ele_ids(2),
4296 p_date => g_eff_start_date);
4297
4298 pay_us_ctgy_feeds_pkg.create_category_feeds(
4299 p_element_type_id => dedn_ele_ids(1),
4300 p_date => g_eff_start_date);
4301
4302
4303 hr_utility.set_location('hr_generate_pretax.ins_deduction_template',70);
4304
4305 /* *** WITHHOLDING AND CALCULATOR FEEDS SECTION BEGIN *** */
4306
4307 dedn_base_feed_iv_id(1) := dedn_payval_id(1); -- Base Payval to
4308 dedn_base_feed_bal_id(1) := dedn_assoc_bal_ids(1); -- Primary Bal
4309
4310 l_num_base_feeds := 1;
4311
4312 /* *** WITHHOLDING AND CALCULATOR FEEDS SECTION END *** */
4313
4314 dedn_si_feed_iv_id(1) := dedn_si_iv_ids(1); -- Repl amount
4315 dedn_si_feed_bal_id(1) := dedn_assoc_bal_ids(3); -- Repl bal
4316
4317 dedn_si_feed_iv_id(2) := dedn_si_iv_ids(2); -- Addl amount
4318 dedn_si_feed_bal_id(2) := dedn_assoc_bal_ids(2); -- Addl bal
4319
4320 l_num_si_feeds := 2;
4321
4322
4323 dedn_sf_feed_iv_id(1) := dedn_sf_iv_ids(3); -- Repl amount
4324 dedn_sf_feed_bal_id(1) := dedn_assoc_bal_ids(3); -- Repl bal
4325
4326 dedn_sf_feed_iv_id(2) := dedn_sf_iv_ids(4); -- Addl amount
4327 dedn_sf_feed_bal_id(2) := dedn_assoc_bal_ids(2); -- Addl bal
4328
4329 dedn_sf_feed_iv_id(3) := dedn_sf_iv_ids(5); -- Not Taken
4330 dedn_sf_feed_bal_id(3) := dedn_assoc_bal_ids(4); -- Not Taken bal
4331
4332 l_num_sf_feeds := 3;
4333
4334 IF p_ele_arrearage = 'Y' THEN
4335
4336 dedn_sf_feed_iv_id(4) := dedn_sf_iv_ids(2); -- Arrears Contr
4337 dedn_sf_feed_bal_id(4) := dedn_assoc_bal_ids(6); -- Arrears bal
4338
4339 l_num_sf_feeds := 4;
4340
4341 dedn_si_feed_iv_id(3) := dedn_si_iv_ids(3); -- Adjust arrears
4342 dedn_si_feed_bal_id(3) := dedn_assoc_bal_ids(6); -- Arrears bal
4343
4344 l_num_si_feeds := 3;
4345
4346 --
4347 -- Total Reached bal feeds (stop rule)
4348 -- Needs to be checked within arrearage check because this is also
4349 -- an optional feed...
4350 --
4351 IF UPPER(p_ele_stop_rule) = 'TOTAL REACHED' THEN
4352
4353 dedn_sf_feed_iv_id(5) := dedn_sf_iv_ids(1); -- Accrued iv to
4354 dedn_sf_feed_bal_id(5) := dedn_assoc_bal_ids(5); -- Accrued Bal
4355
4356 l_num_sf_feeds := 5;
4357
4358 END IF;
4359
4360 --
4361 -- Total Reached bal feeds (stop rule)
4362 --
4363 ELSIF UPPER(p_ele_stop_rule) = 'TOTAL REACHED' THEN
4364
4365 dedn_sf_feed_iv_id(4) := dedn_sf_iv_ids(1); -- Accrued iv to
4366 dedn_sf_feed_bal_id(4) := dedn_assoc_bal_ids(5); -- Accrued Bal
4367
4368 l_num_sf_feeds := 4;
4369
4370 END IF;
4371
4372
4373 /* Also need to create special features feeds from Ptx Amt and
4374 Cancel Ptx Amt ivs to all pretax class bals except net and
4375 payments...write a function.
4376 Also need to feed this input value to appropriate category balances
4377 - ie. do same as create_category_feeds procedure...we'll do this
4378 by a procedure that copies feeds from Base ele pay value...
4379 */
4380
4381 -- Feeds from Ptx Amt...
4382 create_pretax_class_feeds ( p_busgrp_id => p_bg_id,
4383 p_inpval_id => dedn_sf_iv_ids(7),
4384 p_eff_start_date => g_eff_start_date);
4385
4386 create_pretax_cat_feeds ( p_busgrp_id => p_bg_id,
4387 p_src_iv_id => dedn_payval_id(1),
4388 p_inpval_id => dedn_sf_iv_ids(7),
4389 p_eff_start_date => g_eff_start_date);
4390
4391 -- Feeds from Cancel Ptx Amt...
4392 create_pretax_class_feeds ( p_busgrp_id => p_bg_id,
4393 p_inpval_id => dedn_sf_iv_ids(6),
4394 p_eff_start_date => g_eff_start_date);
4395
4396 create_pretax_cat_feeds ( p_busgrp_id => p_bg_id,
4397 p_src_iv_id => dedn_payval_id(1),
4398 p_inpval_id => dedn_sf_iv_ids(6),
4399 p_eff_start_date => g_eff_start_date);
4400
4401 for y in 1..l_num_base_feeds LOOP
4402
4403 already_exists := hr_template_existence.bal_feed_exists (
4404 p_bal_id => dedn_base_feed_bal_id(y),
4405 p_bg_id => p_bg_id,
4406 p_iv_id => dedn_base_feed_iv_id(y),
4407 p_eff_date => g_eff_start_date );
4408
4409 if ALREADY_EXISTS = 0 then
4410
4411 hr_utility.set_location('hr_generate_pretax.ins_deduction_template',90);
4412
4413 hr_balances.ins_balance_feed(
4414 p_option => 'INS_MANUAL_FEED',
4415 p_input_value_id => dedn_base_feed_iv_id(y),
4416 p_element_type_id => NULL,
4417 p_primary_classification_id => NULL,
4418 p_sub_classification_id => NULL,
4419 p_sub_classification_rule_id => NULL,
4420 p_balance_type_id => dedn_base_feed_bal_id(y),
4421 p_scale => '1',
4422 p_session_date => g_eff_start_date,
4423 p_business_group => p_bg_id,
4424 p_legislation_code => NULL,
4425 p_mode => 'USER');
4426
4427 end if; -- feed exists.
4428
4429 END LOOP;
4430
4431
4432 FOR sif in 1..l_num_si_feeds LOOP
4433
4434 already_exists := hr_template_existence.bal_feed_exists (
4435 p_bal_id => dedn_si_feed_bal_id(sif),
4436 p_bg_id => p_bg_id,
4437 p_iv_id => dedn_si_feed_iv_id(sif),
4438 p_eff_date => g_eff_start_date);
4439
4440 if ALREADY_EXISTS = 0 then
4441
4442 hr_balances.ins_balance_feed(
4443 p_option => 'INS_MANUAL_FEED',
4444 p_input_value_id => dedn_si_feed_iv_id(sif),
4445 p_element_type_id => NULL,
4446 p_primary_classification_id => NULL,
4447 p_sub_classification_id => NULL,
4448 p_sub_classification_rule_id => NULL,
4449 p_balance_type_id => dedn_si_feed_bal_id(sif),
4450 p_scale => '1',
4451 p_session_date => g_eff_start_date,
4452 p_business_group => p_bg_id,
4453 p_legislation_code => NULL,
4454 p_mode => 'USER');
4455
4456 end if;
4457
4458 END LOOP;
4459
4460
4461 FOR sf in 1..l_num_sf_feeds LOOP
4462
4463 already_exists := hr_template_existence.bal_feed_exists (
4464 p_bal_id => dedn_sf_feed_bal_id(sf),
4465 p_bg_id => p_bg_id,
4466 p_iv_id => dedn_sf_feed_iv_id(sf),
4467 p_eff_date => g_eff_start_date);
4468
4469 if ALREADY_EXISTS = 0 then
4470
4471 hr_balances.ins_balance_feed(
4472 p_option => 'INS_MANUAL_FEED',
4473 p_input_value_id => dedn_sf_feed_iv_id(sf),
4474 p_element_type_id => NULL,
4475 p_primary_classification_id => NULL,
4476 p_sub_classification_id => NULL,
4477 p_sub_classification_rule_id => NULL,
4478 p_balance_type_id => dedn_sf_feed_bal_id(sf),
4479 p_scale => '1',
4480 p_session_date => g_eff_start_date,
4481 p_business_group => p_bg_id,
4482 p_legislation_code => NULL,
4483 p_mode => 'USER');
4484
4485 end if;
4486
4487 END LOOP;
4488
4489
4490
4491 -- Associate balances and elements to base element type:
4492 /*
4493 dedn_assoc_bal_ids(1) := Primary Balance;
4494 dedn_assoc_bal_ids(2) := Additional Balance;
4495 dedn_assoc_bal_ids(3) := Replacement Balance;
4496 dedn_assoc_bal_ids(4) := Not Taken Balance;
4497 dedn_assoc_bal_ids(5) := Accrued Balance;
4498 dedn_assoc_bal_ids(6) := Arrears Balance;
4499 dedn_assoc_bal_ids(7) := Pretax-Able Balance;
4500 dedn_assoc_bal_ids(8) := Eligible Comp Balance;
4501 */
4502
4503 UPDATE pay_element_types_f
4504 SET element_information10 = dedn_assoc_bal_ids(1), -- primary bal
4505 element_information11 = dedn_assoc_bal_ids(5), -- accrued bal
4506 element_information12 = dedn_assoc_bal_ids(6), -- arrears bal
4507 element_information13 = dedn_assoc_bal_ids(4), -- not taken bal
4508 element_information15 = dedn_assoc_bal_ids(7), -- able amount bal
4509 element_information16 = dedn_assoc_bal_ids(2), -- addl amount bal
4510 element_information17 = dedn_assoc_bal_ids(3), -- repl amount bal
4511 element_information18 = dedn_ele_ids(3), -- Special Inputs
4512 element_information19 = dedn_ele_ids(4), -- Special Features
4513 element_information20 = dedn_ele_ids(2) -- Withholding ele
4514 WHERE element_type_id = dedn_ele_ids(1)
4515 AND business_group_id + 0 = p_bg_id;
4516
4517
4518 ----------------------------- Conclude Main -----------------------------
4519
4520 RETURN dedn_ele_ids(1);
4521
4522 END pretax_deduction_template;
4523
4524 END hr_generate_pretax;