[Home] [Help]
PACKAGE BODY: APPS.HR_USER_INIT_DEDN
Source
1 PACKAGE BODY hr_user_init_dedn AS
2 /* $Header: pyusuidt.pkb 120.3.12010000.1 2008/07/27 23:58:14 appldev ship $ */
3 -- PACKAGE BODY hr_user_init_dedn IS
4 /*
5 +======================================================================+
6 | Copyright (c) 1993 Oracle Corporation |
7 | Redwood Shores, California, USA |
8 | All rights reserved. |
9 +======================================================================+
10
11 Name : hr_user_init_dedn
12
13 Filename : pyusuidt.pkb
14
15 Change List
16 -----------
17 Date Name Vers Bug No Description
18 ---- ---- ---- ------ -----------
19 29-OCT-93 H.Parichabutr 1.0 First Created.
20 Initial Procedures
21 02-NOV-93 hparicha Completed initial creation.
22 04-NOV-93 hparicha 1.1 Added updates to SCL, inpval
23 defaulting (where applicable),
24 Adding locking and delete
25 procedures.
26 18-JAN-94 hparicha 40.19 p_ele_category is receiving
27 LOOKUP_CODE! Make appropriate
28 comparison for Section 125 and
29 Deferred Compensation.
30 (Been lax on this change list
31 - check the arcs log).
32 21-FEB-94 hparicha 40.24 G571 Changed 'COVERAGE_TYPE' to
33 'US_BENEFIT_COVERAGE'.
34 (Been lax on this change list
35 - check the arcs log).
36 03-JUN-94 hparicha 40.1 G815 Added "MIX Category" param
37 and DDF segment.
38 16-JUN-94 hparicha 40.2 G934
39 03-JUL-94 hparicha 40.3 Tidy up for 10G install.
40 13-JUL-94 hparicha 40.4 G907 New implementation of Earnings
41 and Deductions without use
42 of update recurring fres rules.
43 (aka BETA I freeze)
44 22-AUG-94 hparicha 40.5 G1241 Add new defined balances req'd
45 for view dedns screen.
46 Creating "Not Taken" inpval
47 on shadow whether or not
48 partial flag is Yes or No - ie.
49 we need to report dedns not
50 taken in either case. Same
51 for "Arrears Contr", just for
52 predictability and consistency.
53 Create "Not Taken" balance
54 type for Dedns Not Taken rpt.
55 Feed "Not Taken" bal by
56 Special Features "Not Taken"
57 inpval. Changed name of
58 "Towards Bond Purchase" to
59 "Toward...".
60 Update element type DDF with
61 associated balances.
62 26-SEP-94 hparicha 40.6 G1201 Add "_GRE_YTD" defined bal w/
63 Primary balance type - for
64 summary reporting.
65 Add deletion of "Not Taken" bal
66 and handle name change of
67 "...Towards Bond Purchase" to
68 "...Toward Bond Purchase" in
69 formula and deletion.
70 05-OCT-94 rfine 40.7 Changed calls to DB_PAY_SETUP
71 to PAY_DB_PAY_SETUP.
72 18-OCT-94 spanwar 40.8 Removed "PER_PTD" and "PER_LR"
73 balance dimension from
74 dimension list since it is no
75 longer supported.
76 40.9 changed '-Able' to '_Able' for
77 pretax dedn balance name.
78 21-OCT-94 spanwar 40.10 Removed "PER_LR".
79 24-NOV-94 rfine 40.11 Suppressed index on
80 business_group_id
81 05-DEC-94 hparicha 40.12 G1571 Added 'Payments' defbal for
82 primary balance.
83 21-DEC-94 hparicha 40.13 G1681 Clear/Adjust arrears fn'ality.
84 02-MAR-95 spanwar Added call to category feeder
85 in insert bal feeds section.
86 05-MAY-95 allee Added global session date to
87 the call to category feeder.
88 14-JUN-95 hparicha 40.17 286491 Deletion of all balances via
89 assoc bal ids held in
90 ELEMENT_INFORMATIONxx columns.
91 New params to "do_deletions".
92 16-JUN-95 hparicha 40.18 271622 Deletion of freq rule info.
93 29-JUN-95 hparicha 40.19 289319 Generate "Primary" balance
94 and feeds for "ER Liab" when
95 dedn is a benefit. Remember
96 to delete the bal and feeds!
97 Defined balances are
98 required for "_ASG_GRE_RUN/
99 PTD/MONTH/QTD/YTD" - and
100 also for PER and PER_GRE.
101 30-JUN-95 hparicha 40.20 Added "Court Order" input
102 value to garnishments.
103 Also, set new param to
104 create_element_type for
105 p_third_party_pay_only = Y
106 for garns.
107 30-JUN-95 hparicha 40.21 Add benefit classification id
108 to ele type update NO MATTER
109 WHAT the calculation method is.
110 03-AUG-95 hparicha 40.22 EE Bond Refund primary assoc
111 balance created, defbals and
112 feed also created.
113 ?Ben class may have
114 ?CONTRIBUTIONS_USED = 'N', in
115 ?which case the formula must
116 ?be altered NOT TO USE the
117 ?dbi "...BEN_EE_CONTR_VALUE"
118 ?and "...BEN_ER_CONTR_VALUE".
119 17-OCT-95 hparicha 40.25 315814 Added call to bal type pkg
120 to check for uniqueness of
121 "Primary Associated Balance"
122 name - ie. has same name as
123 element type being generated.
124 09-JAN-96 hparicha 40.26 333133 Added defined balances for "_GRE_RUN",
125 _GRE_YTD, _GRE_ITD to do_defined_balances
126 procedure. This allows for GRE-level
127 reporting of earnings and deductions.
128 09-JAN-96 mswanson 40.27 333133 Add defined bal for arrears deductions with
129 "_GRE_ITD" dimensions.
130 12-JAN-96 mswanson 40.28 Restrict qry on dimension by legislation code.
131 01-APR-96 hparicha 40.29 348658 Added element type id to Indirect formula
132 result rules enabling Formula Results screen
133 to display element name.
134 15-Jul-1996 hparicha 40.30 373543 Changing creation of pretax
135 deductions such that they are
136 calculated before taxes, yet withheld
137 after taxes and wage attachments.
138 Now calls new package to create
139 pretax dedns.
140 96/08/14 lwthomps 40.32 345102 Added the primary balance_id to post tax ER
141 elements.
142 16-Aug-1996 gpaytonm 40.33 Removed call to hr_generate_pretax.pretax_ded
143 uction_template which removes bug fix 373543
144 12-Sep-1996 hparicha 40.34 373543 calls hr_generate_pretax again.
145 05-Nov-1996 hparicha 40.35 413211 updated deletion procedure
146 to handle latest configuration -
147 esp. for pretax dedns...involved
148 addition of params to deletion
149 procedure. Also added DDF
150 associations on voluntary
151 deductions for special inputs
152 and special features elements,
153 additional, replacement and
154 ee bond refund balances.
155 07-Nov-1996 hparicha 40.36 413211 Deletion procedure
156 needs to cleanup OLD formulae
157 created during pretax upgrades.
158 08 Nov 1996 hparicha 40.37 Dimension name for _PER_GRE_RUN
159 reverted in generator code.
160
161 11-Jul-1997 mmukherj 40.38 502307 changed do_defined_balances procedure to check
162 whether record already exists in
163 pay_defined_balances table or not for that
164 business_group.Same changes has been made in
165 pywatgen.pkb(involuntary deduction) and
166 pygenptx.pkb(pretax deduction)
167 18-Feb-1998 mmukherj 40.39 566328 do_deletions procedure is modified to
168 include business_group_id in one a select
169 (without cursor) statement, which was
170 selecting more than one row for a given
171 element_name.
172
173 30-Apr-1998 pmadore 40.40 Added additonal input values, formula result
174 rules, elements, and balances to support the
175 Aftertax components of a pretax deduction in
176 category of Deferred Comp 401k. The logic to
177 create these objects depends upon the values
178 of two new parameters to the main package
179 function:
180 p_ele_er_match AND p_ele_at_component
181 18-Aug-1998 mmukherj 40.42 703234 Changed the procedure get_assoc_ele, set
182 default value of l_val as 'N', so that if
183 csr_sfx does not does not fetch any row
184 l_val passes the value 'N', instead of '',
185 which was creating problem in the form.
186 Because aftertax_component_flag and
187 employer_match_flag were not being set to
188 any value.
189 30 Dec 1998 mmukherj 110.4 787491 Entered business_group_id in all where
190 condition of the select statements of
191 do_deletions procedure.
192 NOTE:
193 Data used for certain inserts depend on the calculation method
194 selected. Calls to these procedures may be bundled in a procedure
195 that will handle putting together a logical set of calls - ie.
196 instead of repeating the same logic in each of the insert procedures,
197 the logic can be performed once and the appropriate calls made
198 immediately. The data involved includes input values, status
199 processing rules, formula result rules, and skip rules.
200 See ins_uie_formula below.
201
202 Also note, *could* make insertion (and validation) procedures
203 externally callable. Consider usefulness of such a design.
204
205 For All Future Bugfixes: Please use business group_id in new DML statements ,
206 whenever necessary, because we are allowing to create deduction with same name
207 for two different business groups - mmukherj.
208
209 01/08/1997 asasthan 110.5 773036 Added legislation code for US/Canada
210 interoperability.
211
212 07/29/1999 Rpotnuru 110.6 The variable v_notaken_bal_type_id was
213 used for Eligible comp and Over limit
214 balances were also created using the same
215 variable and because of this the system
216 is creating wrong balance feeds. So two
217 new variables for eleigible comp and
218 Over limit balance were created and used
219 accordingly. Added ASG_GRE_RUN dimension
220 to Arrears balance.
221
222 01/22/2002 ahanda 115.6 Added _ASG_PAYMENTS dimension
223 25-Mar-2002 ekim 115.8 2276457 Added p_termination_rule to
224 ins_deduction_template and update of
225 pay_element_types_f for
226 termination rule update
227 24-Jul-2002 ekim 115.9 changed v_bg_name to
228 per_business_groups.name%TYPE
229 23-DEC-2002 tclewis 115.10 11.5.9. performance changes.
230 27-DEC-2002 meshah 115.11 fixed gscc warnings.
231 01-APR-2003 ahanda 115.12 Changed the defined balance creation for
232 ASG_GRE_RUN to save run balance for Primary,
233 Not Taken, Arrears and Accrued Bal.
234 26-JUN-2003 ahanda 115.13 Changed call to create_balance_type procedure
235 to pass the balance name as reporting name.
236 Added code to populate 'After-Tax Deductions'
237 category for balances
238 07-JAN-2004 rsethupa 115.15 3349594 11.5.10 performance changes
239 08-JAN-2004 rsethupa 115.16 3349594 Removed extra comment added in 115.15 version
240 18-MAR-2004 kvsankar 115.17 3311781 Changed call to create_balance_type
241 procedure to pass the balance_category value
242 depending upon the classification of the
243 element instead of passing 'After-Tax Deductions'
244 for deduction elements.
245 20-MAY-2004 meshah 115.18 removed the logic not required for
246 Non-Recurring elements. Like creation of
247 additional and replacement defined balances.
248 21-MAY-2004 meshah 115.19 fixed gscc error File.Sql.2 and File.Sql.17
249 21-JUL-2004 schauhan 115.20 3613575 Added a table pay_element_types_f to a query
250 in procedure do_deletions to remove Merge Join Cartesian.
251 22-JUL-2004 schauhan 115.21 3613575 Added rownum<2 condition to the query modified in previous
252 version.
253 18-AUG-2004 sdhole 115.22 3651755 Removed balance category parameter for the
254 Eligible Comp balance.
255 23-JAN-2007 alikhar 115.23 5763867 Added code to populate element_information_category
256 115.24 for Voluntary Deduction ER shadow element with
257 amount type as Benefits Table.
258 21-JAN-2008 sudedas 115.25 6270794 Added Defined Balance for ' Accrued' Balance
259 with Dimension _ENTRY_ITD.
260 */
261
262 /*
263 ---------------------------------------------------------------------
264 This package contains calls to core API used to insert records comprising an
265 entire deduction template.
266
267 The procedures responsible for creating
268 appropriate records based on data entered on the User-Initiated Earnings form
269 must perform simple logic to determine the exact attributes required for the
270 earnings template. Attributes (and their determining factors) are:
271 - skip rules (Class): will be determined during insert of ele type.
272 - calculation formulas (CalcMeth)
273 - status processing rules (CalcMeth)
274 - input values (Class/Cat, Calc Method)
275 - formula result rules (CalcMeth)
276 ---------------------------------------------------------------------
277 */
278
279 -- Controlling procedure that calls all insert procedures according to
280 -- locking ladder. May perform some simple logic. More involved logic
281 -- is handled inside various insertion procedures as required,
282 -- especially ins_uie_formula_processing.
283 --
284 -- If any part of this package body fails, then rollback entire transaction.
285 -- Return to form and alert user of corrections required or to notify
286 -- Oracle HR staff in case of more serious error.
287
288 -- Please Note: PL/SQL v1 does not support explicit naming
289 -- of parameters in procedure/function calls. Which only means
290 -- Forms 4 cannot call server-side PL/SQL using explicitly named parameters.
291
292 --
293 ------------------------------- Insertions ------------------------------
294 --
295 -- Procedures to perform insertions of user-initiated earnings data:
296 -- Move inside ins_deduction_template.
297 --
298 ------------------------- ins_deduction_template ------------------------
299 --
300 -- Move all other insert fns and procedures into here(?).
301 FUNCTION ins_deduction_template (
302 p_ele_name in varchar2,
303 p_ele_reporting_name in varchar2,
304 p_ele_description in varchar2 default NULL,
305 p_ele_classification in varchar2,
306 p_ben_class_id in number,
307 p_ele_category in varchar2 default NULL,
308 p_ele_processing_type in varchar2,
309 p_ele_priority in number default NULL,
310 p_ele_standard_link in varchar2 default 'N',
311 p_ele_proc_runtype in varchar2,
312 p_ele_start_rule in varchar2,
313 p_ele_stop_rule in varchar2,
314 p_ele_ee_bond in varchar2 default 'N',
315 p_ele_amount_rule in varchar2,
316 p_ele_paytab_name in varchar2 default NULL,
317 p_ele_paytab_col in varchar2 default NULL,
318 p_ele_paytab_row_type in varchar2 default NULL,
319 p_ele_arrearage in varchar2 default 'N',
320 p_ele_partial_dedn in varchar2 default 'N',
321 p_mix_flag in varchar2 default NULL,
322 p_ele_er_match in varchar2 default 'N',
323 p_ele_at_component in varchar2 default 'N',
324 p_ele_eff_start_date in date default NULL,
325 p_ele_eff_end_date in date default NULL,
326 p_bg_id in number,
327 p_termination_rule in varchar2 default 'F'
328 ) RETURN NUMBER IS
329
330 -- global vars
331 -- Legislation Subgroup Code for all template elements.
332 g_template_leg_code VARCHAR2(30) := 'US';
333 g_template_leg_subgroup VARCHAR2(30);
334 g_arrears_contr_inpval_id NUMBER(9);
335 g_adj_arrears_inpval_id NUMBER(9);
336 g_to_tot_inpval_id NUMBER(9);
337 g_topurch_inpval_id NUMBER(9);
338 g_ele_info_cat VARCHAR2(30);
339 --
340 g_inpval_disp_seq NUMBER := 0; -- Display seq for input vals.
341 g_shadow_inpval_disp_seq NUMBER := 0; -- Display seq for shadow inpvals.
342 g_inputs_inpval_disp_seq NUMBER := 0; -- Display seq for shadow inpvals.
343 g_er_inpval_disp_seq NUMBER := 0; -- Display seq for ER component inpvals.
344 g_eff_start_date DATE;
345 g_eff_end_date DATE;
346
347 -- local constants
348 c_end_of_time CONSTANT DATE := TO_DATE('31/12/4712','DD/MM/YYYY');
349
350 -- local vars
351 v_bg_name per_business_groups.name%TYPE;
352 -- Get from bg short name passed in.
353 v_ele_type_id NUMBER(9); -- insertion of element type.
354 v_primary_class_id NUMBER(9);
355 v_class_lo_priority NUMBER(9);
356 v_class_hi_priority NUMBER(9);
357 v_shadow_ele_type_id NUMBER(9); -- Populated by insertion of element type.
358 v_shadow_ele_name VARCHAR2(80); -- Name of shadow element type.
359 v_inputs_ele_type_id NUMBER(9); -- Populated by insertion of element type.
360 v_inputs_ele_name VARCHAR2(80); -- Name of shadow element type.
361 v_ele_repname VARCHAR2(30);
362 v_bal_type_id NUMBER(9); -- Pop'd by insertion of balance type.
363 v_earn_bal_uom VARCHAR2(30) := 'M';
364 v_balance_name VARCHAR2(80); -- Additional balances req'd by dedn.
365 --v_bal_rpt_name VARCHAR2(30);
366 v_bal_dim VARCHAR2(80);
367 v_inpval_id NUMBER(9);
368 v_payval_id NUMBER(9); -- ID of payval for bal feed insert.
369 v_payval_name VARCHAR2(80); -- Name of payval.
370 v_shadow_info_payval_id NUMBER(9);
371 v_inputs_info_payval_id NUMBER(9);
372 --
373 v_payval_formula_id NUMBER(9); -- ID of formula for payvalue validation.
374 v_totowed_bal_type_id NUMBER(9);
375 v_eepurch_bal_type_id NUMBER(9);
376 v_arrears_bal_type_id NUMBER(9);
377 v_notaken_bal_type_id NUMBER(9);
378 v_eligiblecomp_bal_type_id NUMBER (9);
379 v_overlimit_bal_type_id NUMBER(9);
380 v_able_bal_type_id NUMBER(9);
381 v_sect125_bal_type_id NUMBER(9);
382 v_401k_bal_type_id NUMBER(9);
383 --
384 v_addl_amt_bal_type_id NUMBER(9); -- Pop'd by insertion of balance type.
385 v_addl_amt_bal_name VARCHAR2(80);
386 v_repl_amt_bal_type_id NUMBER(9); -- Pop'd by insertion of balance type.
387 v_repl_amt_bal_name VARCHAR2(80);
388 g_addl_inpval_id NUMBER(9); -- ID of Addl Amt inpval for bal feed.
389 g_repl_inpval_id NUMBER(9); -- ID of Replacement Amt inpval for bal feed.
390 gi_addl_inpval_id NUMBER(9); -- ID of Addl Amt inpval for bal feed.
391 gi_repl_inpval_id NUMBER(9); -- ID of Replace Amt inpval for bal feed.
392 --
393 g_notaken_inpval_id NUMBER(9); -- ID of Not Taken inpval for bal feed.
394 v_eerefund_eletype_id NUMBER(9);
395 v_eerefund_payval_id NUMBER(9);
396 v_topurch_eletype_id NUMBER(9);
397 v_er_charge_eletype_id NUMBER(9);
398 v_er_charge_baltype_id NUMBER(9);
399 v_er_charge_payval_id NUMBER(9); -- inpval id of ER charge PAY VALUE
400 v_eerefund_ele_name VARCHAR2(80);
401 v_eerefund_baltype_id NUMBER(9);
402 v_topurch_ele_name VARCHAR2(80);
403 v_er_charge_ele_name VARCHAR2(80);
404 v_skip_formula_id NUMBER(9);
405
406 l_reg_earn_classification_id number(9);
407 l_reg_earn_business_group_id number(15);
408 l_reg_earn_legislation_code varchar2(30);
409 l_reg_earn_balance_type_id number(9);
410 l_reg_earn_input_value_id number(9);
411 l_reg_earn_scale number(5);
412 l_reg_earn_element_type_id number(9);
413
414 -- Emp Balance form enhancement Bug 3311781
415 l_balance_category varchar2(80);
416
417 cursor get_reg_earn_feeds(p_bg_id number) is
418 SELECT /*+ no_merge(pbf) */
419 bc.CLASSIFICATION_ID, pbf.BUSINESS_GROUP_ID,
420 pbf.LEGISLATION_CODE, pbf.BALANCE_TYPE_ID,
421 pbf.INPUT_VALUE_ID, pbf.SCALE, pbf.ELEMENT_TYPE_ID
422 FROM PAY_BALANCE_FEEDS_V pbf,
423 pay_balance_classifications bc
424 WHERE NVL(pbf.BALANCE_INITIALIZATION_FLAG,'N') = 'N'
425 AND ((pbf.BUSINESS_GROUP_ID IS NULL OR pbf.BUSINESS_GROUP_ID = p_bg_id)
426 AND (pbf.LEGISLATION_CODE IS NULL OR pbf.LEGISLATION_CODE = 'US'))
427 and (pbf.BALANCE_NAME = 'Regular Earnings')
428 and bc.balance_type_id = pbf.balance_type_id
429 order by pbf.element_name;
430
431 --
432 ---------------------------- ins_dedn_ele_type -------------------------------
433 --
434 FUNCTION ins_dedn_ele_type ( p_ele_name in varchar2,
435 p_ele_reporting_name in varchar2,
436 p_ele_description in varchar2,
437 p_ele_class in varchar2,
438 p_ele_category in varchar2,
439 p_ele_start_rule in varchar2,
440 p_ele_processing_type in varchar2,
441 p_ele_priority in number,
442 p_ele_standard_link in varchar2,
443 p_skip_formula_id in number default NULL,
444 p_ind_only_flag in varchar2,
445 p_ele_eff_start_date in date,
446 p_ele_eff_end_date in date,
447 p_bg_name in varchar2)
448 RETURN number IS
449 -- local vars
450 ret NUMBER;
451 v_pay_value_name VARCHAR2(80);
452 v_mult_entries_allowed VARCHAR2(1);
453 v_third_ppm VARCHAR2(30) := 'N';
454
455 BEGIN
456
457 --
458 -- Unless this function actually has to do anything, we can make call
459 -- to pay_db_pay_setup from ins_deduction_template.
460 --
461
462 hr_utility.set_location('hr_user_init_dedn.ins_dedn_ele_type',10);
463
464 IF p_ele_processing_type = 'N' THEN
465
466 v_mult_entries_allowed := 'Y';
467
468 ELSE
469
470 v_mult_entries_allowed := 'N';
471
472 END IF;
473
474 IF UPPER(p_ele_class) = 'INVOLUNTARY DEDUCTIONS' AND
475 UPPER(p_ele_category) = 'G' AND
476 UPPER(p_ele_name) NOT LIKE '%SPECIAL INPUTS' AND
477 UPPER(p_ele_name) NOT LIKE '%SPECIAL FEATURES' THEN
478
479 v_third_ppm := 'Y';
480
481 END IF;
482
483 hr_utility.set_location('hr_user_init_dedn.ins_dedn_ele_type',30);
484 ret := pay_db_pay_setup.create_element(
485 p_element_name => p_ele_name,
486 p_description => p_ele_description,
487 p_classification_name => p_ele_class,
488 p_post_termination_rule => 'Final Close',
489 p_reporting_name => p_ele_reporting_name,
490 p_processing_type => p_ele_processing_type,
491 p_mult_entries_allowed => v_mult_entries_allowed,
492 p_indirect_only_flag => p_ind_only_flag,
493 p_formula_id => p_skip_formula_id,
494 p_processing_priority => p_ele_priority,
495 p_standard_link_flag => p_ele_standard_link,
496 p_business_group_name => p_bg_name,
497 p_effective_start_date => p_ele_eff_start_date,
498 p_effective_end_date => p_ele_eff_end_date,
499 p_legislation_code => NULL,
500 p_third_party_pay_only => v_third_ppm);
501 --
502 v_pay_value_name := hr_input_values.get_pay_value_name(g_template_leg_code);
503 --
504 UPDATE pay_input_values_f
505 SET mandatory_flag = 'X'
506 WHERE element_type_id = ret
507 AND name = v_pay_value_name;
508 --
509 RETURN ret;
510 --
511 END ins_dedn_ele_type;
512 --
513 ------------------------- ins_formula -----------------------
514 --
515 FUNCTION ins_formula ( p_ff_ele_name in varchar2,
516 p_ff_suffix in varchar2,
517 p_ff_desc in varchar2,
518 p_ff_bg_id in number,
519 p_amt_rule in varchar2 default NULL,
520 p_row_type in varchar2 default NULL,
521 p_ele_processing_type in varchar2)
522 RETURN number IS
523 -- local vars
524 v_formula_id number; -- Return var
525 --
526 v_skeleton_formula_text VARCHAR2(32000);
527 v_skeleton_formula_type_id NUMBER(9);
528 v_ele_formula_text VARCHAR2(32000);
529 v_ele_formula_name VARCHAR2(80);
530 v_ele_formula_id NUMBER(9);
531 v_ele_name VARCHAR2(80);
532
533 BEGIN
534 hr_utility.set_location('hr_user_init_dedn.ins_formula',10);
535
536 if p_ele_processing_type = 'R' then
537
538 SELECT FF.formula_text, FF.formula_type_id
539 INTO v_skeleton_formula_text, v_skeleton_formula_type_id
540 FROM ff_formulas_f FF
541 WHERE FF.formula_name = 'SYSTEM_DEDN_CALC_FORMULA'
542 AND FF.business_group_id IS NULL
543 AND FF.legislation_code = 'US'
544 AND FF.formula_id >= 0 --Bug 3349594
545 AND g_eff_start_date between FF.effective_start_date
546 AND FF.effective_end_date;
547
548 else
549
550 SELECT FF.formula_text, FF.formula_type_id
551 INTO v_skeleton_formula_text, v_skeleton_formula_type_id
552 FROM ff_formulas_f FF
553 WHERE FF.formula_name = 'SYSTEM_DEDN_CALC_NR_FORMULA'
554 AND FF.business_group_id IS NULL
555 AND FF.legislation_code = 'US'
556 AND FF.formula_id >= 0 --Bug 3349594
557 AND g_eff_start_date between FF.effective_start_date
558 AND FF.effective_end_date;
559
560 end if; /* p_ele_processing_type */
561
562 -- Replace element name placeholders with current element name:
563 hr_utility.set_location('hr_user_init_dedn.ins_formula',15);
564 v_ele_name := REPLACE(LTRIM(RTRIM(UPPER(p_ff_ele_name))),' ','_');
565
566 v_ele_formula_text := REPLACE( v_skeleton_formula_text,
567 '<ELE_NAME>',
568 v_ele_name);
569 --
570 -- Make <ROW_TYPE> replacements if necessary.
571 --
572 IF p_amt_rule = 'PT' THEN
573 IF p_row_type NOT IN ('Salary Range', 'Age Range') THEN
574 hr_utility.set_location('hr_user_init_dedn.ins_formula',17);
575 v_ele_formula_text := REPLACE( v_ele_formula_text,
576 '<ROW_TYPE>',
577 REPLACE(LTRIM(RTRIM(p_row_type)),' ','_'));
578
579 hr_utility.set_location('hr_user_init_dedn.ins_formula',19);
580 v_ele_formula_text := REPLACE( v_ele_formula_text,
581 '<DEFAULT_ROW_TYPE_LINE>',
582 'default for ' || REPLACE(LTRIM(RTRIM(p_row_type)),' ','_') || ' (text) is ''NOT ENTERED''');
583
584 v_ele_formula_text := REPLACE( v_ele_formula_text,
585 '<ROW_TYPE_INPUTS_ARE>',
586 ',' || REPLACE(LTRIM(RTRIM(p_row_type)),' ','_') || ' (text)');
587
588 ELSE
589
590 --
591 -- Do we need to handle when row type is Salary Range, ie. use ASS_SALARY dbi?
592 -- Do we also need to create Default For ASS_SALARY or PER_AGE as appropriate?
593 --
594 hr_utility.set_location('hr_user_init_dedn.ins_formula',20);
595 v_ele_formula_text := REPLACE( v_ele_formula_text,
596 '<ROW_TYPE>',
597 'To_Char(PER_AGE)');
598
599 hr_utility.set_location('hr_user_init_dedn.ins_formula',21);
600 v_ele_formula_text := REPLACE( v_ele_formula_text,
601 '<DEFAULT_ROW_TYPE_LINE>',
602 ' ');
603
604 v_ele_formula_text := REPLACE( v_ele_formula_text,
605 '<ROW_TYPE_INPUTS_ARE>',
606 ' ');
607
608 END IF;
609
610 --
611 -- "Zero" benefits
612 --
613 hr_utility.set_location('hr_user_init_dedn.ins_formula',23);
614 v_ele_formula_text := REPLACE( v_ele_formula_text,
615 v_ele_name || '_BEN_EE_CONTR_VALUE',
616 '0');
617
618 v_ele_formula_text := REPLACE( v_ele_formula_text,
619 v_ele_name || '_BEN_ER_CONTR_VALUE',
620 '0');
621
622 v_ele_formula_text := REPLACE( v_ele_formula_text,
623 '<DEFAULT_BEN_EE_LINE>',
624 ' ');
625
626 v_ele_formula_text := REPLACE( v_ele_formula_text,
627 '<DEFAULT_BEN_ER_LINE>',
628 ' ');
629
630 ELSIF p_amt_rule = 'BT' THEN
631
632 --
633 -- Using benefits, <ELE_NAME>_BEN_EE_CONTR_VALUE is already taken care of.
634 --
635 hr_utility.set_location('hr_user_init_dedn.ins_formula',25);
636 v_ele_formula_text := REPLACE( v_ele_formula_text,
637 '<DEFAULT_BEN_EE_LINE>',
638 'default for ' || v_ele_name || '_BEN_EE_CONTR_VALUE is 0');
639
640 v_ele_formula_text := REPLACE( v_ele_formula_text,
641 '<DEFAULT_BEN_ER_LINE>',
642 'default for ' || v_ele_name || '_BEN_ER_CONTR_VALUE is 0');
643
644 -- Clear out <ROW_TYPE>
645 v_ele_formula_text := REPLACE( v_ele_formula_text,
646 '<ROW_TYPE>',
647 '''NOT ENTERED''');
648
649 v_ele_formula_text := REPLACE( v_ele_formula_text,
650 '<DEFAULT_ROW_TYPE_LINE>',
651 ' ');
652
653 v_ele_formula_text := REPLACE( v_ele_formula_text,
654 '<ROW_TYPE_INPUTS_ARE>',
655 ' ');
656
657 ELSE
658
659 --
660 -- Clear out everything!
661 -- Clear out <ROW_TYPE>
662 hr_utility.set_location('hr_user_init_dedn.ins_formula',27);
663 v_ele_formula_text := REPLACE( v_ele_formula_text,
664 '<ROW_TYPE>',
665 '''NOT ENTERED''');
666
667 v_ele_formula_text := REPLACE( v_ele_formula_text,
668 '<DEFAULT_ROW_TYPE_LINE>',
669 ' ');
670
671 v_ele_formula_text := REPLACE( v_ele_formula_text,
672 '<ROW_TYPE_INPUTS_ARE>',
673 ' ');
674
675 --
676 -- "Zero" benefits
677 --
678 v_ele_formula_text := REPLACE( v_ele_formula_text,
679 v_ele_name || '_BEN_EE_CONTR_VALUE',
680 '0');
681
682 v_ele_formula_text := REPLACE( v_ele_formula_text,
683 v_ele_name || '_BEN_ER_CONTR_VALUE',
684 '0');
685
686 v_ele_formula_text := REPLACE( v_ele_formula_text,
687 '<DEFAULT_BEN_EE_LINE>',
688 ' ');
689
690 v_ele_formula_text := REPLACE( v_ele_formula_text,
691 '<DEFAULT_BEN_ER_LINE>',
692 ' ');
693
694 END IF;
695
696 v_ele_formula_name := v_ele_name || UPPER(p_ff_suffix);
697 v_ele_formula_name := SUBSTR(v_ele_formula_name, 1, 80);
698
699 --
700 -- Insert the new formula into current business goup:
701 -- Get new id
702
703 hr_utility.set_location('hr_user_init_dedn.ins_formula',30);
704 SELECT ff_formulas_s.nextval
705 INTO v_formula_id
706 FROM sys.dual;
707
708 hr_utility.set_location('hr_user_init_dedn.ins_formula',40);
709 INSERT INTO ff_formulas_f (
710 FORMULA_ID,
711 EFFECTIVE_START_DATE,
712 EFFECTIVE_END_DATE,
713 BUSINESS_GROUP_ID,
714 LEGISLATION_CODE,
715 FORMULA_TYPE_ID,
716 FORMULA_NAME,
717 DESCRIPTION,
718 FORMULA_TEXT,
719 STICKY_FLAG,
720 LAST_UPDATE_DATE,
721 LAST_UPDATED_BY,
722 LAST_UPDATE_LOGIN,
723 CREATED_BY,
724 CREATION_DATE)
725 values (
726 v_formula_id,
727 g_eff_start_date,
728 g_eff_end_date,
729 p_bg_id,
730 NULL,
731 v_skeleton_formula_type_id,
732 v_ele_formula_name,
733 p_ff_desc,
734 v_ele_formula_text,
735 'N',
736 NULL,
737 NULL,
738 NULL,
739 -1,
740 g_eff_start_date);
741
742 RETURN v_formula_id;
743
744 END ins_formula;
745
746 --
747 ------------------------- ins_dedn_formula_processing -----------------------
748 --
749 PROCEDURE ins_dedn_formula_processing (
750 p_ele_id in number,
751 p_ele_name in varchar2,
752 p_shadow_ele_id in number,
753 p_shadow_ele_name in varchar2,
754 p_inputs_ele_id in number,
755 p_inputs_ele_name in varchar2,
756 p_primary_class_id in number,
757 p_ele_class_name in varchar2,
758 p_ele_cat in varchar2,
759 p_ele_proc_type in varchar2,
760 p_amount_rule in varchar2,
761 p_proc_runtype in varchar2 default 'R',
762 p_start_rule in varchar2 default NULL,
763 p_stop_rule in varchar2 default NULL,
764 p_ee_bond in varchar2 default 'N',
765 p_paytab_name in varchar2 default NULL,
766 p_paytab_col in varchar2 default NULL,
767 p_paytab_row_type in varchar2 default NULL,
768 p_arrearage in varchar2 default 'N',
769 p_partial_dedn in varchar2 default 'N',
770 p_er_charge_eletype_id in number default NULL,
771 p_er_charge_payval_id in number default NULL,
772 p_eerefund_eletype_id in number default NULL,
773 p_bg_id in number,
774 p_mix_category in varchar2 default NULL,
775 p_eff_start_date in date default NULL,
776 p_eff_end_date in date default NULL,
777 p_bg_name in varchar2) IS
778
779 -- local vars
780 v_fname VARCHAR2(80);
781 v_ftype_id NUMBER(9);
782 v_fdesc VARCHAR2(240);
783 v_ftext VARCHAR2(32000); -- "Safe" max length of varchar2
784 v_sticky_flag VARCHAR2(1);
785 v_asst_status_type_id NUMBER(9);
786 v_stat_proc_rule_id NUMBER(9);
787 v_fres_rule_id NUMBER(9);
788 v_proc_rule VARCHAR2(1) := 'P'; -- Provide "Process" proc rule.
789 v_calc_rule_formula_id NUMBER(9);
790 v_er_contr_inpval_id NUMBER(9); -- inpval id of ER Contr to feed ER chrg
791 v_er_payval_id NUMBER(9); -- paybal id of ER Contr (if not passed in)
792 v_bondrefund_inpval_id NUMBER(9); -- inpval id;"Bond Refund" to feedDirPay.
793 v_eerefund_payval_id NUMBER(9); -- inpval id of "EE Bond Refund" ele payval
794 v_to_owed_inpval_id NUMBER(9); -- inpval id for Tot Reached stop rule
795 v_to_arrears_inpval_id NUMBER(9); -- inpval id for To Arrears rule
796 v_notaken_inpval_id NUMBER(9); -- inpval id for Not Taken (arrears = 'Y')
797 v_actdedn_inpval_id NUMBER(9); -- inpval id for "Deduction Actually Taken" amount
798 v_passthru_inpval_id NUMBER(9); -- inpval id for "Take OverLimit AT" amount
799
800 v_inpval_id NUMBER(9);
801 v_inpval_name VARCHAR2(80);
802 v_inpval_uom VARCHAR2(80);
803 v_ele_sev_level VARCHAR2(1);
804 v_gen_dbi VARCHAR2(1);
805 v_dflt_value VARCHAR2(60);
806 v_amt_rule_formula VARCHAR2(80);
807 v_lkp_type VARCHAR2(30);
808 v_val_formula_id NUMBER(9);
809 v_class_name VARCHAR2(80);
810 v_paytab_id NUMBER(9);
811 v_row_code VARCHAR2(30);
812 v_age_code VARCHAR2(30);
813 v_sal_code VARCHAR2(30);
814 v_cre_row_inpval VARCHAR2(1);
815 v_user_row_title VARCHAR2(80);
816 --
817 -- In the case of earnings elements, the formulae are fully defined in advance
818 -- based on calculation rule only. These pre-packaged formulae are seeded
819 -- as startup data - such that bg_id is NULL, in the appropriate legislation.
820 -- The formula_name will closely resemble the calc rule.
821 -- For deductions, formula is "pieced together" according to calc_rule
822 -- and other attributes. (Maybe, let's pre-defined them first. Having them
823 -- pre-defined AND COMPILED makes things a bit easier.)
824
825 -- To copy a formula from seed data to the user's business group, we can
826 -- select the formula_text LONG field into a VARCHAR2; the LONG field
827 -- in the table can then accept the VARCHAR2 formula text as long as it
828 -- does not exceed 32767 bytes (varchar2 will be 32000 to be safe).
829
830 -- Make direct calls to CORE_API packaged procedures to:
831 -- 1) Insert status proc rule of 'PROCESS' for Asst status type 'ACTIVE_ASSIGN'
832 -- and appropriate formula according to calculation method
833 -- 2) Insert input values according to calculation method
834 -- - Also make call to ins_uie_input_vals for insertion of additional
835 -- input values based on class/category and others required of
836 -- ALL template earnings elements.
837 -- 3) Insert formula result rules as appropriate for formula and amount rule.
838 -- 4) Insert additional formula result rules according to other deduction
839 -- attributes.
840 --
841 BEGIN -- Deduction Formula Processing
842 --
843 -- Check for % Earnings amount rule.
844 --
845 -- "% EARNINGS"
846 -- First, update SCL
847 -- ( ) Category in segment 1;
848 -- ( ) Partial EE Contributions in seg 2 (if not Involuntary Dedn)
849 -- ( ) MIX Category in segment 9
850 -- ( ) insert formula and save formula_id;
851 -- Insert status proc rule;
852 -- Insert input vals;
853 -- (*) "Percentage"
854 -- (*) "Earnings Balance" (lkp val to hr lkps for val bals)
855 -- (*) "Earn Bal Dimension" (default to "ASS_RUN"; lkp val for dims)
856 -- (*) "Additional Amount" (wait to insert this "Global" inpval)
857 -- (*) "Replacement Amount" (wait to insert this "Global" inpval)
858 -- (These are the ONLY "global" inpval for deductions.
859 -- Insert formula result rules;
860 -- (*) dedn_amt --> Direct
861 -- (*) stop_entry --> Stop
862 -- ( ) clear_addl_amt --> Update Recurring inpval "Additional Amount"
863 -- ( ) clear_repl_amt --> Update Rec inpval "Replacement Amount"
864 --
865 -- *** IF UPPER(p_ele_proc_type) = 'R' THEN
866 -- Resolve this issue over Rec/Nonrec versions of formulae
867 -- ( ) Do we need to do this? If a nonrecurring element has no formula
868 -- result rule for STOP_ENTRY, then what difference does it make?
869 -- Ie. why do we need separate formulae for Recurring and Nonrecurring
870 -- if the only difference is stop entry?;
871 --
872 -- Recurring version of formula has final pay "Stop Entry". See issue above.
873 --
874 -- Find formula id of element's % Earnings formula;
875 -- May be able to pass in element's formula name as created earlier,
876 -- instead if [re]constructing it here.
877 -- Now create formula for element by selecting "skeleton" calculation formula
878 -- and performing string substitutions for element name in proper placeholders.
879 -- The formula is then inserted into the current business group.
880 -- Other placeholders will be substituted based on other attributes (ie.
881 -- balances and arrears). When finished, the formula can be compiled.
882 --
883 IF UPPER(p_amount_rule) = 'PE' THEN
884 -- Set SCL:
885 -- IF UPPER(p_ele_class_name) = 'INVOLUNTARY DEDUCTIONS' THEN
886 -- Populate segments 1,2,3 w/Category, PayTab, PayTab Row.
887 hr_utility.set_location('hr_user_init_dedn.ins_dedn_formula_processing',05);
888 UPDATE pay_element_types_f
889 SET element_information_category = g_ele_info_cat,
890 element_information1 = p_ele_cat,
891 element_information2 = p_partial_dedn,
892 element_information3 = p_proc_runtype,
893 element_information9 = p_mix_category,
894 benefit_classification_id = p_ben_class_id
895 WHERE element_type_id = p_ele_id
896 AND business_group_id + 0 = p_bg_id;
897
898 hr_utility.set_location('hr_user_init_dedn.ins_dedn_formula_processing',05);
899 UPDATE pay_element_types_f
900 SET element_information_category = g_ele_info_cat,
901 element_information1 = p_ele_cat,
902 element_information2 = p_partial_dedn,
903 element_information3 = p_proc_runtype
904 WHERE element_type_id = p_shadow_ele_id
905 AND business_group_id + 0 = p_bg_id;
906
907
908 If p_ele_proc_type = 'R' then /* Not required for NR Elements */
909
910 hr_utility.set_location('hr_user_init_dedn.ins_dedn_formula_processing',05);
911 UPDATE pay_element_types_f
912 SET element_information_category = g_ele_info_cat,
913 element_information1 = p_ele_cat,
914 element_information2 = p_partial_dedn,
915 element_information3 = p_proc_runtype
916 WHERE element_type_id = p_inputs_ele_id
917 AND business_group_id + 0 = p_bg_id;
918
919 End if; /* Not required for NR Elements */
920
921 --
922 -- *1* Testing note: formula name should be '<ELE_NAME>_PERCENT_EARNINGS'
923 -- in this case.
924 --
925 hr_utility.set_location('hr_user_init_dedn.ins_dedn_formula_processing',15);
926 v_calc_rule_formula_id := ins_formula (
927 p_ff_ele_name => p_ele_name,
928 p_ff_suffix => '_PERCENT_EARNINGS',
929 p_ff_desc => 'Percent Earnings calculation for deductions.',
930 p_ff_bg_id => p_bg_id,
931 p_amt_rule => NULL,
932 p_row_type => NULL,
933 p_ele_processing_type => p_ele_proc_type );
934
935 hr_utility.set_location('hr_user_init_dedn.ins_dedn_formula_processing',17);
936 v_stat_proc_rule_id :=
937 pay_formula_results.ins_stat_proc_rule (
938 p_business_group_id => p_bg_id,
939 p_legislation_code => NULL,
940 p_legislation_subgroup => g_template_leg_subgroup,
941 p_effective_start_date => p_eff_start_date,
942 p_effective_end_date => p_eff_end_date,
943 p_element_type_id => p_ele_id,
944 p_assignment_status_type_id => v_asst_status_type_id,
945 p_formula_id => v_calc_rule_formula_id,
946 p_processing_rule => v_proc_rule);
947 --
948 -- Remember: NULL asst_status_type_id means "Standard" processing rule!
949
950 -- REQUIRED FOR EACH INPUT VALUE CREATED IN THIS MANNER, TO (HERE).
951 -- Creating "Percentage" inpval
952 hr_utility.set_location('hr_user_init_dedn.ins_dedn_formula_processing',20);
953 g_inpval_disp_seq := g_inpval_disp_seq + 1;
954 v_inpval_name := 'Percentage';
955 v_inpval_uom := 'Number';
956 v_gen_dbi := 'Y';
957 v_lkp_type := NULL;
958 v_dflt_value := NULL;
959 v_inpval_id := pay_db_pay_setup.create_input_value (
960 p_element_name => p_ele_name,
961 p_name => v_inpval_name,
962 p_uom => v_inpval_uom,
963 p_uom_code => NULL,
964 p_mandatory_flag => 'N',
965 p_generate_db_item_flag => v_gen_dbi,
966 p_default_value => v_dflt_value,
967 p_min_value => NULL,
968 p_max_value => NULL,
969 p_warning_or_error => NULL,
970 p_lookup_type => v_lkp_type,
971 p_formula_id => v_val_formula_id,
972 p_hot_default_flag => 'N',
973 p_display_sequence => g_inpval_disp_seq,
974 p_business_group_name => p_bg_name,
975 p_effective_start_date => p_eff_start_date,
976 p_effective_end_date => p_eff_end_date);
977
978 hr_utility.set_location('hr_user_init_dedn.ins_dedn_formula_processing',25);
979 hr_input_values.chk_input_value(
980 p_element_type_id => p_ele_id,
981 p_legislation_code => g_template_leg_code,
982 p_val_start_date => p_eff_start_date,
983 p_val_end_date => p_eff_end_date,
984 p_insert_update_flag => 'UPDATE',
985 p_input_value_id => v_inpval_id,
986 p_rowid => NULL,
987 p_recurring_flag => p_ele_proc_type,
988 p_mandatory_flag => 'N',
989 p_hot_default_flag => 'N',
990 p_standard_link_flag => 'N',
991 p_classification_type => 'N',
992 p_name => v_inpval_name,
993 p_uom => v_inpval_uom,
994 p_min_value => NULL,
995 p_max_value => NULL,
996 p_default_value => v_dflt_value,
997 p_lookup_type => v_lkp_type,
998 p_formula_id => v_val_formula_id,
999 p_generate_db_items_flag => v_gen_dbi,
1000 p_warning_or_error => NULL);
1001
1002 hr_utility.set_location('hr_user_init_dedn.ins_dedn_formula_processing',27);
1003 hr_input_values.ins_3p_input_values(
1004 p_val_start_date => p_eff_start_date,
1005 p_val_end_date => p_eff_end_date,
1006 p_element_type_id => p_ele_id,
1007 p_primary_classification_id => p_primary_class_id,
1008 p_input_value_id => v_inpval_id,
1009 p_default_value => v_dflt_value,
1010 p_max_value => NULL,
1011 p_min_value => NULL,
1012 p_warning_or_error_flag => NULL,
1013 p_input_value_name => v_inpval_name,
1014 p_db_items_flag => v_gen_dbi,
1015 p_costable_type => NULL,
1016 p_hot_default_flag => 'N',
1017 p_business_group_id => p_bg_id,
1018 p_legislation_code => NULL,
1019 p_startup_mode => NULL);
1020 --
1021 -- (HERE) Done inserting "Percentage" input value.
1022 --
1023 -- Creating "Earnings Balance" inpval
1024 -- 08 Jun 1994: This is not used as of yet - Fast Formula requires the
1025 -- balance name to be "hard-coded" in order to retreive its' value; possibly
1026 -- next version of plsql/FF will allow us to hold the balance name on an
1027 -- input value. Until then, the user may replace the REGULAR_EARNINGS
1028 -- balance used by default with any balance they choose.
1029 -- When this is implemented, create both a Balance Name inpval along with
1030 -- a Balance Dimension inpval. Lookups can be created in order to validate
1031 -- these inpvals.
1032 --
1033 --
1034 -- Now insert appropriate formula_result_rules for this element
1035 --
1036
1037 hr_utility.set_location('hr_user_init_dedn.ins_dedn_formula_processing',47);
1038 v_fres_rule_id := pay_formula_results.ins_form_res_rule (
1039 p_business_group_id => p_bg_id,
1040 p_legislation_code => NULL,
1041 p_legislation_subgroup => g_template_leg_subgroup,
1042 p_effective_start_date => p_eff_start_date,
1043 p_effective_end_date => p_eff_end_date,
1044 p_status_processing_rule_id => v_stat_proc_rule_id,
1045 p_input_value_id => NULL,
1046 p_result_name => 'DEDN_AMT',
1047 p_result_rule_type => 'D',
1048 p_severity_level => NULL,
1049 p_element_type_id => NULL);
1050
1051 IF p_ele_proc_type = 'R' THEN
1052 hr_utility.set_location('hr_user_init_dedn.ins_dedn_formula_processing',50);
1053 v_fres_rule_id := pay_formula_results.ins_form_res_rule (
1054 p_business_group_id => p_bg_id,
1055 p_legislation_code => NULL,
1056 p_legislation_subgroup => g_template_leg_subgroup,
1057 p_effective_start_date => p_eff_start_date,
1058 p_effective_end_date => p_eff_end_date,
1059 p_status_processing_rule_id => v_stat_proc_rule_id,
1060 p_input_value_id => NULL,
1061 p_result_name => 'STOP_ENTRY',
1062 p_result_rule_type => 'S',
1063 p_severity_level => NULL,
1064 p_element_type_id => p_ele_id);
1065 END IF;
1066 --
1067 -- Check for Payroll Table amount rule:
1068 --
1069 ELSIF UPPER(p_amount_rule) = 'PT' THEN
1070 -- Update element type SCL as appropriate;
1071 -- ( ) Populate Category segment 1
1072 -- ( ) Populate Partial EE Contributions segment 2
1073 -- ( ) Populate payroll table/row segments
1074 -- (segments 6,7 if Vol or Pre-Tax;
1075 -- segments 3,4 if Invol)
1076 -- Insert input vals;
1077 -- (*) "Table Column" (default to p_ele_paytab_col)
1078 -- ( ) Also requires input value for "Table Row" if value in
1079 -- p_ele_paytab_row is NOT a database item. If it IS a dbi_name,
1080 -- then we do not create inpval for it, the value is stored on
1081 -- the SCL and formula picks it up from there. This will amount
1082 -- to an input value required when the user enters a value OTHER
1083 -- then "Salary Range" or "Age Range" in the Row Type field.
1084 -- Insert formula result rules;
1085 -- (*) dedn_amt --> Direct
1086 -- (*) stop_entry --> Stop
1087 --
1088 -- Find formula id of element's Payroll Table deduction formula;
1089 -- May be able to pass in element's formula name as created earlier,
1090 -- instead if [re]constructing it here.
1091 -- Testing note: formula name should be '<ELE_NAME>_PAYROLL_TABLE'
1092 -- in this case.
1093 --
1094 -- Find table id
1095 hr_utility.set_location('hr_user_init_dedn.ins_dedn_formula_processing',53);
1096 SELECT user_table_id
1097 INTO v_paytab_id
1098 FROM pay_user_tables
1099 WHERE UPPER(user_table_name) = UPPER(p_paytab_name)
1100 AND NVL(business_group_id, p_bg_id) = p_bg_id;
1101 -- Find row code
1102 hr_utility.set_location('hr_user_init_dedn.ins_dedn_formula_processing',55);
1103 SELECT lookup_code
1104 INTO v_row_code
1105 FROM hr_lookups
1106 WHERE UPPER(meaning) = UPPER(p_paytab_row_type)
1107 AND lookup_type = 'US_TABLE_ROW_TYPES';
1108 --
1109 -- Set SCL:
1110 -- Note: Changing "Payroll Table" and "Row Type" columns (ele_info6,7)
1111 -- to hold the actual table name and lookup CODE.
1112 -- Previously stored the ID and Meaning (09 Feb 1994)
1113 --
1114 -- IF UPPER(p_ele_class_name) = 'INVOLUNTARY DEDUCTIONS' THEN
1115 -- Populate segments 1,2,3 w/Category, PayTab, PayTab Row.
1116 hr_utility.set_location('hr_user_init_dedn.ins_dedn_formula_processing',57);
1117 UPDATE pay_element_types_f
1118 SET element_information_category = g_ele_info_cat,
1119 element_information1 = p_ele_cat,
1120 element_information2 = p_partial_dedn,
1121 element_information3 = p_proc_runtype,
1122 element_information6 = p_paytab_name,
1123 element_information7 = v_row_code,
1124 element_information9 = p_mix_category,
1125 benefit_classification_id = p_ben_class_id
1126 WHERE element_type_id = p_ele_id
1127 AND business_group_id + 0 = p_bg_id;
1128
1129 hr_utility.set_location('hr_user_init_dedn.ins_dedn_formula_processing',57);
1130 UPDATE pay_element_types_f
1131 SET element_information_category = g_ele_info_cat,
1132 element_information1 = p_ele_cat,
1133 element_information2 = p_partial_dedn,
1134 element_information3 = p_proc_runtype,
1135 element_information6 = p_paytab_name,
1136 element_information7 = v_row_code
1137 WHERE element_type_id = p_shadow_ele_id
1138 AND business_group_id + 0 = p_bg_id;
1139
1140
1141 If p_ele_proc_type = 'R' Then /* Not required for NR Elements */
1142
1143 hr_utility.set_location('hr_user_init_dedn.ins_dedn_formula_processing',57);
1144 UPDATE pay_element_types_f
1145 SET element_information_category = g_ele_info_cat,
1146 element_information1 = p_ele_cat,
1147 element_information2 = p_partial_dedn,
1148 element_information3 = p_proc_runtype,
1149 element_information6 = p_paytab_name,
1150 element_information7 = v_row_code
1151 WHERE element_type_id = p_inputs_ele_id
1152 AND business_group_id + 0 = p_bg_id;
1153
1154 End if; /* Not required for NR Elements */
1155
1156 hr_utility.set_location('hr_user_init_dedn.ins_dedn_formula_processing',59);
1157 v_calc_rule_formula_id := ins_formula (
1158 p_ff_ele_name => p_ele_name,
1159 p_ff_suffix => '_PAYROLL_TABLE',
1160 p_ff_desc => 'Payroll Table calculation for deductions.',
1161 p_ff_bg_id => p_bg_id,
1162 p_amt_rule => p_amount_rule,
1163 p_row_type => p_paytab_row_type,
1164 p_ele_processing_type => p_ele_proc_type );
1165
1166 hr_utility.set_location('hr_user_init_dedn.ins_dedn_formula_processing',60);
1167 v_stat_proc_rule_id :=
1168 pay_formula_results.ins_stat_proc_rule (
1169 p_business_group_id => p_bg_id,
1170 p_legislation_code => NULL,
1171 p_legislation_subgroup => g_template_leg_subgroup,
1172 p_effective_start_date => p_eff_start_date,
1173 p_effective_end_date => p_eff_end_date,
1174 p_element_type_id => p_ele_id,
1175 p_assignment_status_type_id => v_asst_status_type_id,
1176 p_formula_id => v_calc_rule_formula_id,
1177 p_processing_rule => v_proc_rule);
1178 --
1179 -- Remember: NULL asst_status_type_id means "Standard" processing rule!
1180
1181 -- REQUIRED FOR EACH INPUT VALUE CREATED IN THIS MANNER, TO (HERE).
1182 --
1183 -- Creating "Table Column" inpval
1184 --
1185 hr_utility.set_location('hr_user_init_dedn.ins_dedn_formula_processing',65);
1186 g_inpval_disp_seq := g_inpval_disp_seq + 1;
1187 v_inpval_name := 'Table Column';
1188 v_inpval_uom := 'Character';
1189 v_gen_dbi := 'N';
1190 v_lkp_type := NULL;
1191 -- lkp could be created to validate user_Table_columns based on user_table.
1192 v_dflt_value := p_paytab_col;
1193 -- Should be: v_dflt_value := p_paytab_col; but procedure checks for valid
1194 -- values according to lookup - which is cool, since we'll have validated
1195 -- this client side anyway. ( ) Update procedure when appropriate.
1196 --
1197 v_inpval_id := pay_db_pay_setup.create_input_value (
1198 p_element_name => p_ele_name,
1199 p_name => v_inpval_name,
1200 p_uom => v_inpval_uom,
1201 p_uom_code => NULL,
1202 p_mandatory_flag => 'N',
1203 p_generate_db_item_flag => v_gen_dbi,
1204 p_default_value => v_dflt_value,
1205 p_min_value => NULL,
1206 p_max_value => NULL,
1207 p_warning_or_error => NULL,
1208 p_lookup_type => v_lkp_type,
1209 p_formula_id => v_val_formula_id,
1210 p_hot_default_flag => 'N',
1211 p_display_sequence => g_inpval_disp_seq,
1212 p_business_group_name => p_bg_name,
1213 p_effective_start_date => p_eff_start_date,
1214 p_effective_end_date => p_eff_end_date);
1215
1216 hr_utility.set_location('hr_user_init_dedn.ins_dedn_formula_processing',67);
1217 hr_input_values.chk_input_value(
1218 p_element_type_id => p_ele_id,
1219 p_legislation_code => g_template_leg_code,
1220 p_val_start_date => p_eff_start_date,
1221 p_val_end_date => p_eff_end_date,
1222 p_insert_update_flag => 'UPDATE',
1223 p_input_value_id => v_inpval_id,
1224 p_rowid => NULL,
1225 p_recurring_flag => p_ele_proc_type,
1226 p_mandatory_flag => 'N',
1227 p_hot_default_flag => 'N',
1228 p_standard_link_flag => 'N',
1229 p_classification_type => 'N',
1230 p_name => v_inpval_name,
1231 p_uom => v_inpval_uom,
1232 p_min_value => NULL,
1233 p_max_value => NULL,
1234 p_default_value => v_dflt_value,
1235 p_lookup_type => v_lkp_type,
1236 p_formula_id => v_val_formula_id,
1237 p_generate_db_items_flag => v_gen_dbi,
1238 p_warning_or_error => NULL);
1239
1240 hr_utility.set_location('hr_user_init_dedn.ins_dedn_formula_processing',70);
1241 hr_input_values.ins_3p_input_values(
1242 p_val_start_date => p_eff_start_date,
1243 p_val_end_date => p_eff_end_date,
1244 p_element_type_id => p_ele_id,
1245 p_primary_classification_id => p_primary_class_id,
1246 p_input_value_id => v_inpval_id,
1247 p_default_value => v_dflt_value,
1248 p_max_value => NULL,
1249 p_min_value => NULL,
1250 p_warning_or_error_flag => NULL,
1251 p_input_value_name => v_inpval_name,
1252 p_db_items_flag => v_gen_dbi,
1253 p_costable_type => NULL,
1254 p_hot_default_flag => 'N',
1255 p_business_group_id => p_bg_id,
1256 p_legislation_code => NULL,
1257 p_startup_mode => NULL);
1258 --
1259 -- (HERE) Done inserting "Table Column" input value.
1260 --
1261 -- Place logic determining to create or not create additional input value here:
1262 -- 1) If p_paytab_row_type = 'Age Range' or 'Salary Range' then DO NOT create
1263 -- addl inpval;
1264 -- 2) Compare p_paytab_row_type with database item names:
1265 -- If p_paytab_row_type = dbi.name then DO NOT create addl inpval;
1266 -- Else create addl inpval where name = PAY_USER_TABLES.USER_ROW_TITLE
1267 -- (and user_table_name = p_paytab_name)
1268 --
1269
1270 IF p_paytab_row_type NOT IN ('Salary Range', 'Age Range') THEN
1271 -- Create inpval for row type.
1272 hr_utility.set_location('hr_user_init_dedn.ins_dedn_formula_processing',75);
1273 --
1274 -- Creating "Row Type" inpval
1275 --
1276 hr_utility.set_location('hr_user_init_dedn.ins_dedn_formula_processing',65);
1277 g_inpval_disp_seq := g_inpval_disp_seq + 1;
1278 v_inpval_name := p_paytab_row_type;
1279 v_inpval_uom := 'Character'; -- Get_Table_Value only accepts chars
1280 v_gen_dbi := 'Y';
1281 v_lkp_type := NULL;
1282 -- lkp could be created to validate user_Table_columns based on user_table.
1283 v_dflt_value := NULL;
1284
1285 v_inpval_id := pay_db_pay_setup.create_input_value (
1286 p_element_name => p_ele_name,
1287 p_name => v_inpval_name,
1288 p_uom => v_inpval_uom,
1289 p_uom_code => NULL,
1290 p_mandatory_flag => 'N',
1291 p_generate_db_item_flag => v_gen_dbi,
1292 p_default_value => v_dflt_value,
1293 p_min_value => NULL,
1294 p_max_value => NULL,
1295 p_warning_or_error => NULL,
1296 p_lookup_type => v_lkp_type,
1297 p_formula_id => v_val_formula_id,
1298 p_hot_default_flag => 'N',
1299 p_display_sequence => g_inpval_disp_seq,
1300 p_business_group_name => p_bg_name,
1301 p_effective_start_date => p_eff_start_date,
1302 p_effective_end_date => p_eff_end_date);
1303
1304 hr_utility.set_location('hr_user_init_dedn.ins_dedn_formula_processing',67);
1305 hr_input_values.chk_input_value(
1306 p_element_type_id => p_ele_id,
1307 p_legislation_code => g_template_leg_code,
1308 p_val_start_date => p_eff_start_date,
1309 p_val_end_date => p_eff_end_date,
1310 p_insert_update_flag => 'UPDATE',
1311 p_input_value_id => v_inpval_id,
1312 p_rowid => NULL,
1313 p_recurring_flag => p_ele_proc_type,
1314 p_mandatory_flag => 'N',
1315 p_hot_default_flag => 'N',
1316 p_standard_link_flag => 'N',
1317 p_classification_type => 'N',
1318 p_name => v_inpval_name,
1319 p_uom => v_inpval_uom,
1320 p_min_value => NULL,
1321 p_max_value => NULL,
1322 p_default_value => v_dflt_value,
1323 p_lookup_type => v_lkp_type,
1324 p_formula_id => v_val_formula_id,
1325 p_generate_db_items_flag => v_gen_dbi,
1326 p_warning_or_error => NULL);
1327
1328 hr_utility.set_location('hr_user_init_dedn.ins_dedn_formula_processing',70);
1329 hr_input_values.ins_3p_input_values(
1330 p_val_start_date => p_eff_start_date,
1331 p_val_end_date => p_eff_end_date,
1332 p_element_type_id => p_ele_id,
1333 p_primary_classification_id => p_primary_class_id,
1334 p_input_value_id => v_inpval_id,
1335 p_default_value => v_dflt_value,
1336 p_max_value => NULL,
1337 p_min_value => NULL,
1338 p_warning_or_error_flag => NULL,
1339 p_input_value_name => v_inpval_name,
1340 p_db_items_flag => v_gen_dbi,
1341 p_costable_type => NULL,
1342 p_hot_default_flag => 'N',
1343 p_business_group_id => p_bg_id,
1344 p_legislation_code => NULL,
1345 p_startup_mode => NULL);
1346 --
1347 -- (HERE) Done inserting "Row Type" input value.
1348 --
1349 END IF; -- rowtype = dbi name check.
1350 --
1351 -- END IF; -- Row type = Age or Sal range.
1352 --
1353 --
1354 -- Now insert appropriate formula_result_rules for this element
1355 --
1356
1357 hr_utility.set_location('hr_user_init_dedn.ins_dedn_formula_processing',75);
1358 v_fres_rule_id := pay_formula_results.ins_form_res_rule (
1359 p_business_group_id => p_bg_id,
1360 p_legislation_code => NULL,
1361 p_legislation_subgroup => g_template_leg_subgroup,
1362 p_effective_start_date => p_eff_start_date,
1363 p_effective_end_date => p_eff_end_date,
1364 p_status_processing_rule_id => v_stat_proc_rule_id,
1365 p_input_value_id => NULL,
1366 p_result_name => 'DEDN_AMT',
1367 p_result_rule_type => 'D',
1368 p_severity_level => NULL,
1369 p_element_type_id => NULL);
1370
1371 IF p_ele_proc_type = 'R' THEN
1372 hr_utility.set_location('hr_user_init_dedn.ins_dedn_formula_processing',77);
1373 v_fres_rule_id := pay_formula_results.ins_form_res_rule (
1374 p_business_group_id => p_bg_id,
1375 p_legislation_code => NULL,
1376 p_legislation_subgroup => g_template_leg_subgroup,
1377 p_effective_start_date => p_eff_start_date,
1378 p_effective_end_date => p_eff_end_date,
1379 p_status_processing_rule_id => v_stat_proc_rule_id,
1380 p_input_value_id => NULL,
1381 p_result_name => 'STOP_ENTRY',
1382 p_result_rule_type => 'S',
1383 p_severity_level => NULL,
1384 p_element_type_id => p_ele_id);
1385 END IF;
1386 --
1387 -- Check for Benefits Table amount rule:
1388 --
1389 ELSIF UPPER(p_amount_rule) = 'BT' THEN
1390
1391
1392 /* Note:
1393 Deductions template generator relies on input value creation
1394 API to create proper "benefits" database items based on input
1395 values created here. If the API looks for a Benefits
1396 classification, it won't find it - so we need the dbitem
1397 creation mechanism to look for this scenario (ie. Coverage, EE,
1398 and ER Contr input values).
1399
1400 (*) Another API is being provided by UK to create these dbi(create_contr_items)
1401 */
1402 -- Find formula id of element's Payroll Table deduction formula;
1403 -- May be able to pass in element's formula name as created earlier,
1404 -- instead if [re]constructing it here.
1405 -- Set SCL:
1406 -- IF UPPER(p_ele_class_name) = 'INVOLUNTARY DEDUCTIONS' THEN
1407 -- Populate segments 1 w/Category
1408 hr_utility.set_location('hr_user_init_dedn.ins_dedn_formula_processing',81);
1409 UPDATE pay_element_types_f
1410 SET element_information_category = g_ele_info_cat,
1411 element_information1 = p_ele_cat,
1412 element_information2 = p_partial_dedn,
1413 element_information3 = p_proc_runtype,
1414 element_information9 = p_mix_category,
1415 benefit_classification_id = p_ben_class_id
1416 WHERE element_type_id = p_ele_id
1417 AND business_group_id + 0 = p_bg_id;
1418
1419 hr_utility.set_location('hr_user_init_dedn.ins_dedn_formula_processing',83);
1420 UPDATE pay_element_types_f
1421 SET element_information_category = g_ele_info_cat,
1422 element_information1 = p_ele_cat,
1423 element_information2 = p_partial_dedn,
1424 element_information3 = p_proc_runtype
1425 WHERE element_type_id = p_shadow_ele_id
1426 AND business_group_id + 0 = p_bg_id;
1427
1428 If p_ele_proc_type = 'R' then /* Not required for NR Elements */
1429
1430 hr_utility.set_location('hr_user_init_dedn.ins_dedn_formula_processing',83);
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 WHERE element_type_id = p_inputs_ele_id
1437 AND business_group_id + 0 = p_bg_id;
1438
1439 End if; /* Not required for NR Elements */
1440
1441 --
1442 -- Testing note: formula name should be '<ELE_NAME>_BENEFITS_TABLE'
1443 -- in this case.
1444 hr_utility.set_location('hr_user_init_dedn.ins_dedn_formula_processing',85);
1445 v_calc_rule_formula_id := ins_formula (
1446 p_ff_ele_name => p_ele_name,
1447 p_ff_suffix => '_BENEFITS_TABLE',
1448 p_ff_desc => 'Benefits Table calculation for deductions.',
1449 p_ff_bg_id => p_bg_id,
1450 p_amt_rule => p_amount_rule,
1451 p_row_type => NULL,
1452 p_ele_processing_type => p_ele_proc_type );
1453
1454 hr_utility.set_location('hr_user_init_dedn.ins_dedn_formula_processing',87);
1455 v_stat_proc_rule_id :=
1456 pay_formula_results.ins_stat_proc_rule (
1457 p_business_group_id => p_bg_id,
1458 p_legislation_code => NULL,
1459 p_legislation_subgroup => g_template_leg_subgroup,
1460 p_effective_start_date => p_eff_start_date,
1461 p_effective_end_date => p_eff_end_date,
1462 p_element_type_id => p_ele_id,
1463 p_assignment_status_type_id => v_asst_status_type_id,
1464 p_formula_id => v_calc_rule_formula_id,
1465 p_processing_rule => v_proc_rule);
1466 --
1467 -- Remember: NULL asst_status_type_id means "Standard" processing rule!
1468
1469 -- REQUIRED FOR EACH INPUT VALUE CREATED IN THIS MANNER, TO (HERE).
1470 --
1471 -- Creating "Coverage" inpval
1472 --
1473 hr_utility.set_location('hr_user_init_dedn.ins_dedn_formula_processing',90);
1474 g_inpval_disp_seq := g_inpval_disp_seq + 1;
1475 v_inpval_name := 'Coverage';
1476 v_inpval_uom := 'Character';
1477 v_gen_dbi := 'Y';
1478 v_lkp_type := 'US_BENEFIT_COVERAGE';
1479 v_dflt_value := 'EMP ONLY';
1480 v_val_formula_id := NULL; -- ??? By a formula to do some special sql?
1481 v_inpval_id := pay_db_pay_setup.create_input_value (
1482 p_element_name => p_ele_name,
1483 p_name => v_inpval_name,
1484 p_uom => v_inpval_uom,
1485 p_uom_code => NULL,
1486 p_mandatory_flag => 'Y',
1487 p_generate_db_item_flag => v_gen_dbi,
1488 p_default_value => v_dflt_value,
1489 p_min_value => NULL,
1490 p_max_value => NULL,
1491 p_warning_or_error => NULL,
1492 p_lookup_type => v_lkp_type,
1493 p_formula_id => v_val_formula_id,
1494 p_hot_default_flag => 'N',
1495 p_display_sequence => g_inpval_disp_seq,
1496 p_business_group_name => p_bg_name,
1497 p_effective_start_date => p_eff_start_date,
1498 p_effective_end_date => p_eff_end_date);
1499
1500 hr_utility.set_location('hr_user_init_dedn.ins_dedn_formula_processing',95);
1501 hr_input_values.chk_input_value(
1502 p_element_type_id => p_ele_id,
1503 p_legislation_code => g_template_leg_code,
1504 p_val_start_date => p_eff_start_date,
1505 p_val_end_date => p_eff_end_date,
1506 p_insert_update_flag => 'UPDATE',
1507 p_input_value_id => v_inpval_id,
1508 p_rowid => NULL,
1509 p_recurring_flag => p_ele_proc_type,
1510 p_mandatory_flag => 'Y',
1511 p_hot_default_flag => 'N',
1512 p_standard_link_flag => 'N',
1513 p_classification_type => 'N',
1514 p_name => v_inpval_name,
1515 p_uom => v_inpval_uom,
1516 p_min_value => NULL,
1517 p_max_value => NULL,
1518 p_default_value => v_dflt_value,
1519 p_lookup_type => v_lkp_type,
1520 p_formula_id => v_val_formula_id,
1521 p_generate_db_items_flag => v_gen_dbi,
1522 p_warning_or_error => NULL);
1523
1524 hr_utility.set_location('hr_user_init_dedn.ins_dedn_formula_processing',97);
1525 hr_input_values.ins_3p_input_values(
1526 p_val_start_date => p_eff_start_date,
1527 p_val_end_date => p_eff_end_date,
1528 p_element_type_id => p_ele_id,
1529 p_primary_classification_id => p_primary_class_id,
1530 p_input_value_id => v_inpval_id,
1531 p_default_value => v_dflt_value,
1532 p_max_value => NULL,
1533 p_min_value => NULL,
1534 p_warning_or_error_flag => NULL,
1535 p_input_value_name => v_inpval_name,
1536 p_db_items_flag => v_gen_dbi,
1537 p_costable_type => NULL,
1538 p_hot_default_flag => 'N',
1539 p_business_group_id => p_bg_id,
1540 p_legislation_code => NULL,
1541 p_startup_mode => NULL);
1542 --
1543 -- (HERE) Done inserting "Coverage" input value.
1544 --
1545 --
1546 -- Creating "ER Contr" inpval
1547 --
1548 hr_utility.set_location('hr_user_init_dedn.ins_dedn_formula_processing',100);
1549 g_inpval_disp_seq := g_inpval_disp_seq + 1;
1550 v_inpval_name := 'ER Contr';
1551 v_inpval_uom := 'Money';
1552 v_gen_dbi := 'Y';
1553 v_lkp_type := NULL;
1554 v_dflt_value := NULL;
1555 v_val_formula_id := NULL;
1556 v_inpval_id := pay_db_pay_setup.create_input_value (
1557 p_element_name => p_ele_name,
1558 p_name => v_inpval_name,
1559 p_uom => v_inpval_uom,
1560 p_uom_code => NULL,
1561 p_mandatory_flag => 'N',
1562 p_generate_db_item_flag => v_gen_dbi,
1563 p_default_value => v_dflt_value,
1564 p_min_value => NULL,
1565 p_max_value => NULL,
1566 p_warning_or_error => NULL,
1567 p_lookup_type => v_lkp_type,
1568 p_formula_id => v_val_formula_id,
1569 p_hot_default_flag => 'N',
1570 p_display_sequence => g_inpval_disp_seq,
1571 p_business_group_name => p_bg_name,
1572 p_effective_start_date => p_eff_start_date,
1573 p_effective_end_date => p_eff_end_date);
1574
1575 hr_utility.set_location('hr_user_init_dedn.ins_dedn_formula_processing',101);
1576 hr_input_values.chk_input_value(
1577 p_element_type_id => p_ele_id,
1578 p_legislation_code => g_template_leg_code,
1579 p_val_start_date => p_eff_start_date,
1580 p_val_end_date => p_eff_end_date,
1581 p_insert_update_flag => 'UPDATE',
1582 p_input_value_id => v_inpval_id,
1583 p_rowid => NULL,
1584 p_recurring_flag => p_ele_proc_type,
1585 p_mandatory_flag => 'N',
1586 p_hot_default_flag => 'N',
1587 p_standard_link_flag => 'N',
1588 p_classification_type => 'N',
1589 p_name => v_inpval_name,
1590 p_uom => v_inpval_uom,
1591 p_min_value => NULL,
1592 p_max_value => NULL,
1593 p_default_value => v_dflt_value,
1594 p_lookup_type => v_lkp_type,
1595 p_formula_id => v_val_formula_id,
1596 p_generate_db_items_flag => v_gen_dbi,
1597 p_warning_or_error => NULL);
1598
1599 hr_utility.set_location('hr_user_init_dedn.ins_dedn_formula_processing',102);
1600 hr_input_values.ins_3p_input_values(
1601 p_val_start_date => p_eff_start_date,
1602 p_val_end_date => p_eff_end_date,
1603 p_element_type_id => p_ele_id,
1604 p_primary_classification_id => p_primary_class_id,
1605 p_input_value_id => v_inpval_id,
1606 p_default_value => v_dflt_value,
1607 p_max_value => NULL,
1608 p_min_value => NULL,
1609 p_warning_or_error_flag => NULL,
1610 p_input_value_name => v_inpval_name,
1611 p_db_items_flag => v_gen_dbi,
1612 p_costable_type => NULL,
1613 p_hot_default_flag => 'N',
1614 p_business_group_id => p_bg_id,
1615 p_legislation_code => NULL,
1616 p_startup_mode => NULL);
1617 --
1618 -- (HERE) Done inserting "ER Contr" input value.
1619 --
1620 --
1621 -- Creating "EE Contr" inpval
1622 --
1623 hr_utility.set_location('hr_user_init_dedn.ins_dedn_formula_processing',103);
1624 g_inpval_disp_seq := g_inpval_disp_seq + 1;
1625 v_inpval_name := 'EE Contr';
1626 v_inpval_uom := 'Money';
1627 v_gen_dbi := 'Y';
1628 v_lkp_type := NULL;
1629 v_dflt_value := NULL;
1630 v_val_formula_id := NULL;
1631 v_er_contr_inpval_id := pay_db_pay_setup.create_input_value (
1632 p_element_name => p_ele_name,
1633 p_name => v_inpval_name,
1634 p_uom => v_inpval_uom,
1635 p_uom_code => NULL,
1636 p_mandatory_flag => 'N',
1637 p_generate_db_item_flag => v_gen_dbi,
1638 p_default_value => v_dflt_value,
1639 p_min_value => NULL,
1640 p_max_value => NULL,
1641 p_warning_or_error => NULL,
1642 p_lookup_type => v_lkp_type,
1643 p_formula_id => v_val_formula_id,
1644 p_hot_default_flag => 'N',
1645 p_display_sequence => g_inpval_disp_seq,
1646 p_business_group_name => p_bg_name,
1647 p_effective_start_date => p_eff_start_date,
1648 p_effective_end_date => p_eff_end_date);
1649
1650 hr_utility.set_location('hr_user_init_dedn.ins_dedn_formula_processing',105);
1651 hr_input_values.chk_input_value(
1652 p_element_type_id => p_ele_id,
1653 p_legislation_code => g_template_leg_code,
1654 p_val_start_date => p_eff_start_date,
1655 p_val_end_date => p_eff_end_date,
1656 p_insert_update_flag => 'UPDATE',
1657 p_input_value_id => v_er_contr_inpval_id,
1658 p_rowid => NULL,
1659 p_recurring_flag => p_ele_proc_type,
1660 p_mandatory_flag => 'N',
1661 p_hot_default_flag => 'N',
1662 p_standard_link_flag => 'N',
1663 p_classification_type => 'N',
1664 p_name => v_inpval_name,
1665 p_uom => v_inpval_uom,
1666 p_min_value => NULL,
1667 p_max_value => NULL,
1668 p_default_value => v_dflt_value,
1669 p_lookup_type => v_lkp_type,
1670 p_formula_id => v_val_formula_id,
1671 p_generate_db_items_flag => v_gen_dbi,
1672 p_warning_or_error => NULL);
1673
1674 hr_utility.set_location('hr_user_init_dedn.ins_dedn_formula_processing',107);
1675 hr_input_values.ins_3p_input_values(
1676 p_val_start_date => p_eff_start_date,
1677 p_val_end_date => p_eff_end_date,
1678 p_element_type_id => p_ele_id,
1679 p_primary_classification_id => p_primary_class_id,
1680 p_input_value_id => v_er_contr_inpval_id,
1681 p_default_value => v_dflt_value,
1682 p_max_value => NULL,
1683 p_min_value => NULL,
1684 p_warning_or_error_flag => NULL,
1685 p_input_value_name => v_inpval_name,
1686 p_db_items_flag => v_gen_dbi,
1687 p_costable_type => NULL,
1688 p_hot_default_flag => 'N',
1689 p_business_group_id => p_bg_id,
1690 p_legislation_code => NULL,
1691 p_startup_mode => NULL);
1692 --
1693 -- (HERE) Done inserting "EE Contr" input value.
1694 --
1695 --
1696 -- Now insert appropriate formula_result_rules for this element
1697 --
1698 hr_utility.set_location('hr_user_init_dedn.ins_dedn_formula_processing',109);
1699 v_fres_rule_id := pay_formula_results.ins_form_res_rule (
1700 p_business_group_id => p_bg_id,
1701 p_legislation_code => NULL,
1702 p_legislation_subgroup => g_template_leg_subgroup,
1703 p_effective_start_date => p_eff_start_date,
1704 p_effective_end_date => p_eff_end_date,
1705 p_status_processing_rule_id => v_stat_proc_rule_id,
1706 p_input_value_id => NULL,
1707 p_result_name => 'DEDN_AMT',
1708 p_result_rule_type => 'D',
1709 p_severity_level => NULL,
1710 p_element_type_id => NULL);
1711
1712 IF p_ele_proc_type = 'R' THEN
1713 hr_utility.set_location('hr_user_init_dedn.ins_dedn_formula_processing',111);
1714 v_fres_rule_id := pay_formula_results.ins_form_res_rule (
1715 p_business_group_id => p_bg_id,
1716 p_legislation_code => NULL,
1717 p_legislation_subgroup => g_template_leg_subgroup,
1718 p_effective_start_date => p_eff_start_date,
1719 p_effective_end_date => p_eff_end_date,
1720 p_status_processing_rule_id => v_stat_proc_rule_id,
1721 p_input_value_id => NULL,
1722 p_result_name => 'STOP_ENTRY',
1723 p_result_rule_type => 'S',
1724 p_severity_level => NULL,
1725 p_element_type_id => p_ele_id);
1726 END IF;
1727 --
1728 -- In order to create indirect result feeding Employer Charge element for
1729 -- this benefit, we must find the input_value_id for the pay_value of the
1730 -- employer charge element.
1731 --
1732 hr_utility.set_location('hr_user_init_dedn.ins_deduction_template',70);
1733 v_payval_name := hr_input_values.get_pay_value_name(g_template_leg_code);
1734
1735 hr_utility.set_location('hr_user_init_dedn.ins_deduction_template',80);
1736 -- We need inpval_id of pay value for this element:
1737 SELECT IV.input_value_id
1738 INTO v_er_payval_id
1739 FROM pay_input_values_f IV
1740 WHERE IV.element_type_id = p_er_charge_eletype_id
1741 AND IV.name = v_payval_name;
1742 --
1743 hr_utility.set_location('hr_user_init_dedn.ins_dedn_formula_processing',115);
1744 v_fres_rule_id := pay_formula_results.ins_form_res_rule (
1745 p_business_group_id => p_bg_id,
1746 p_legislation_code => NULL,
1747 p_legislation_subgroup => g_template_leg_subgroup,
1748 p_effective_start_date => p_eff_start_date,
1749 p_effective_end_date => p_eff_end_date,
1750 p_status_processing_rule_id => v_stat_proc_rule_id,
1751 p_input_value_id => v_er_payval_id,
1752 p_result_name => 'BENE_ER_CONTR',
1753 p_result_rule_type => 'I',
1754 p_severity_level => NULL,
1755 p_element_type_id => p_er_charge_eletype_id);
1756 -- (*) ATTN: ins_form_res_rule API has been updated to accept a new param
1757 -- for element type id.
1758 -- I've taken a copy of benchmark package and called it pay_formula_results
1759 -- since benchmark is unsupported by the UK.
1760 --
1761 ELSE
1762 --
1763 -- Default to Flat Amount processing of deduction.
1764 -- (ie. IF UPPER(p_amount_rule) = 'FA' THEN...)
1765 --
1766 -- First, get formula_id for appropriate formula (created earlier this pkg);
1767 -- Insert status proc rule;
1768 -- Insert input vals ("Amount");
1769 -- Insert formula result rules;
1770 -- (*) dedn_amt --> Direct
1771 -- (*) stop_entry --> Stop
1772 --
1773 -- Set DDF Segment values:
1774 --
1775 hr_utility.set_location('hr_user_init_dedn.ins_dedn_formula_processing',81);
1776 UPDATE pay_element_types_f
1777 SET element_information_category = g_ele_info_cat,
1778 element_information1 = p_ele_cat,
1779 element_information2 = p_partial_dedn,
1780 element_information3 = p_proc_runtype,
1781 element_information9 = p_mix_category,
1782 benefit_classification_id = p_ben_class_id
1783 WHERE element_type_id = p_ele_id
1784 AND business_group_id + 0 = p_bg_id;
1785
1786 hr_utility.set_location('hr_user_init_dedn.ins_dedn_formula_processing',81);
1787 UPDATE pay_element_types_f
1788 SET element_information_category = g_ele_info_cat,
1789 element_information1 = p_ele_cat,
1790 element_information2 = p_partial_dedn,
1791 element_information3 = p_proc_runtype
1792 WHERE element_type_id = p_shadow_ele_id
1793 AND business_group_id + 0 = p_bg_id;
1794
1795
1796 If p_ele_proc_type = 'R' then /* Not required for NR Elements */
1797
1798 hr_utility.set_location('hr_user_init_dedn.ins_dedn_formula_processing',81);
1799 UPDATE pay_element_types_f
1800 SET element_information_category = g_ele_info_cat,
1801 element_information1 = p_ele_cat,
1802 element_information2 = p_partial_dedn,
1803 element_information3 = p_proc_runtype
1804 WHERE element_type_id = p_inputs_ele_id
1805 AND business_group_id + 0 = p_bg_id;
1806
1807 End if; /* Not required for NR Elements */
1808
1809 hr_utility.set_location('hr_user_init_dedn.ins_dedn_formula_processing',15);
1810 v_calc_rule_formula_id := ins_formula (
1811 p_ff_ele_name => p_ele_name,
1812 p_ff_suffix => '_FLAT_AMOUNT',
1813 p_ff_desc => 'Flat Amount calculation for deductions.',
1814 p_ff_bg_id => p_bg_id,
1815 p_amt_rule => NULL,
1816 p_row_type => NULL,
1817 p_ele_processing_type => p_ele_proc_type );
1818
1819 hr_utility.set_location('hr_user_init_dedn.ins_dedn_formula_processing',119);
1820 v_stat_proc_rule_id :=
1821 pay_formula_results.ins_stat_proc_rule (
1822 p_business_group_id => p_bg_id,
1823 p_legislation_code => NULL,
1824 p_legislation_subgroup => g_template_leg_subgroup,
1825 p_effective_start_date => p_eff_start_date,
1826 p_effective_end_date => p_eff_end_date,
1827 p_element_type_id => p_ele_id,
1828 p_assignment_status_type_id => v_asst_status_type_id,
1829 p_formula_id => v_calc_rule_formula_id,
1830 p_processing_rule => v_proc_rule);
1831 --
1832 -- Flat "Amount" inpval.
1833 --
1834 hr_utility.set_location('hr_user_init_dedn.ins_uie_inp_values',40);
1835 g_inpval_disp_seq := g_inpval_disp_seq + 1;
1836 v_inpval_name := 'Amount';
1837 v_inpval_uom := 'Money';
1838 v_gen_dbi := 'Y';
1839 v_lkp_type := NULL;
1840 v_dflt_value := NULL;
1841
1842 v_inpval_id := pay_db_pay_setup.create_input_value (
1843 p_element_name => p_ele_name,
1844 p_name => v_inpval_name,
1845 p_uom => v_inpval_uom,
1846 p_uom_code => NULL,
1847 p_mandatory_flag => 'N',
1848 p_generate_db_item_flag => v_gen_dbi,
1849 p_default_value => v_dflt_value,
1850 p_min_value => NULL,
1851 p_max_value => NULL,
1852 p_warning_or_error => NULL,
1853 p_lookup_type => v_lkp_type,
1854 p_formula_id => NULL,
1855 p_hot_default_flag => 'N',
1856 p_display_sequence => g_inpval_disp_seq,
1857 p_business_group_name => p_bg_name,
1858 p_effective_start_date => p_eff_start_date,
1859 p_effective_end_date => p_eff_end_date);
1860
1861 hr_utility.set_location('hr_user_init_dedn.ins_uie_inp_values',50);
1862 hr_input_values.chk_input_value(
1863 p_element_type_id => p_ele_id,
1864 p_legislation_code => g_template_leg_code,
1865 p_val_start_date => p_eff_start_date,
1866 p_val_end_date => p_eff_end_date,
1867 p_insert_update_flag => 'UPDATE',
1868 p_input_value_id => v_inpval_id,
1869 p_rowid => NULL,
1870 p_recurring_flag => p_ele_proc_type,
1871 p_mandatory_flag => 'N',
1872 p_hot_default_flag => 'N',
1873 p_standard_link_flag => 'N',
1874 p_classification_type => 'N',
1875 p_name => v_inpval_name,
1876 p_uom => v_inpval_uom,
1877 p_min_value => NULL,
1878 p_max_value => NULL,
1879 p_default_value => NULL,
1880 p_lookup_type => v_dflt_value,
1881 p_formula_id => NULL,
1882 p_generate_db_items_flag => v_gen_dbi,
1883 p_warning_or_error => NULL);
1884
1885 hr_utility.set_location('hr_user_init_dedn.ins_uie_inp_values',60);
1886 hr_input_values.ins_3p_input_values(
1887 p_val_start_date => p_eff_start_date,
1888 p_val_end_date => p_eff_end_date,
1889 p_element_type_id => p_ele_id,
1890 p_primary_classification_id => p_primary_class_id,
1891 p_input_value_id => v_inpval_id,
1892 p_default_value => v_dflt_value,
1893 p_max_value => NULL,
1894 p_min_value => NULL,
1895 p_warning_or_error_flag => NULL,
1896 p_input_value_name => v_inpval_name,
1897 p_db_items_flag => v_gen_dbi,
1898 p_costable_type => NULL,
1899 p_hot_default_flag => 'N',
1900 p_business_group_id => p_bg_id,
1901 p_legislation_code => NULL,
1902 p_startup_mode => NULL);
1903 -- Done inserting "Amount" input value.
1904 --
1905 -- Now insert appropriate formula_result_rules for this element
1906 --
1907 hr_utility.set_location('hr_user_init_dedn.ins_dedn_formula_processing',121);
1908 v_fres_rule_id := pay_formula_results.ins_form_res_rule (
1909 p_business_group_id => p_bg_id,
1910 p_legislation_code => NULL,
1911 p_legislation_subgroup => g_template_leg_subgroup,
1912 p_effective_start_date => p_eff_start_date,
1913 p_effective_end_date => p_eff_end_date,
1914 p_status_processing_rule_id => v_stat_proc_rule_id,
1915 p_input_value_id => NULL,
1916 p_result_name => 'DEDN_AMT',
1917 p_result_rule_type => 'D',
1918 p_severity_level => NULL,
1919 p_element_type_id => NULL);
1920
1921 IF p_ele_proc_type = 'R' THEN
1922 hr_utility.set_location('hr_user_init_dedn.ins_dedn_formula_processing',123);
1923 v_fres_rule_id := pay_formula_results.ins_form_res_rule (
1924 p_business_group_id => p_bg_id,
1925 p_legislation_code => NULL,
1926 p_legislation_subgroup => g_template_leg_subgroup,
1927 p_effective_start_date => p_eff_start_date,
1928 p_effective_end_date => p_eff_end_date,
1929 p_status_processing_rule_id => v_stat_proc_rule_id,
1930 p_input_value_id => NULL,
1931 p_result_name => 'STOP_ENTRY',
1932 p_result_rule_type => 'S',
1933 p_severity_level => NULL,
1934 p_element_type_id => p_ele_id);
1935 END IF;
1936
1937 END IF; -- Amount rule checks
1938
1939 -- ER Component Checks
1940 IF p_ele_er_match = 'Y' THEN
1941
1942 -- create input values for:
1943 -- (*) "Deduction Actually Taken"
1944 -- create formula result rule for:
1945 -- (*) DEDN_AMT --> Deduction Actually Taken --> <ELE_NAME>_ER.
1946 --
1947
1948 --
1949 hr_utility.set_location('hr_user_init_dedn.ins_uie_inp_values',983);
1950 g_er_inpval_disp_seq := g_er_inpval_disp_seq + 1;
1951 v_inpval_name := 'Deduction Actually Taken';
1952 v_inpval_uom := 'Money';
1953 v_gen_dbi := 'N';
1954 v_lkp_type := NULL;
1955 v_dflt_value := NULL;
1956
1957 v_actdedn_inpval_id := pay_db_pay_setup.create_input_value (
1958 p_element_name => v_er_charge_ele_name,
1959 p_name => v_inpval_name,
1960 p_uom => v_inpval_uom,
1961 p_uom_code => NULL,
1962 p_mandatory_flag => 'X',
1963 p_generate_db_item_flag => v_gen_dbi,
1964 p_default_value => v_dflt_value,
1965 p_min_value => NULL,
1966 p_max_value => NULL,
1967 p_warning_or_error => NULL,
1968 p_lookup_type => v_lkp_type,
1969 p_formula_id => v_val_formula_id,
1970 p_hot_default_flag => 'N',
1971 p_display_sequence => g_er_inpval_disp_seq,
1972 p_business_group_name => p_bg_name,
1973 p_effective_start_date => p_eff_start_date,
1974 p_effective_end_date => p_eff_end_date);
1975
1976 hr_utility.set_location('hr_user_init_dedn.ins_uie_inp_values',984);
1977 hr_input_values.chk_input_value(
1978 p_element_type_id => v_er_charge_eletype_id,
1979 p_legislation_code => g_template_leg_code,
1980 p_val_start_date => p_eff_start_date,
1981 p_val_end_date => p_eff_end_date,
1982 p_insert_update_flag => 'UPDATE',
1983 p_input_value_id => v_actdedn_inpval_id,
1984 p_rowid => NULL,
1985 p_recurring_flag => 'N',
1986 p_mandatory_flag => 'X',
1987 p_hot_default_flag => 'N',
1988 p_standard_link_flag => 'N',
1989 p_classification_type => 'N',
1990 p_name => v_inpval_name,
1991 p_uom => v_inpval_uom,
1992 p_min_value => NULL,
1993 p_max_value => NULL,
1994 p_default_value => NULL,
1995 p_lookup_type => v_dflt_value,
1996 p_formula_id => NULL,
1997 p_generate_db_items_flag => v_gen_dbi,
1998 p_warning_or_error => NULL);
1999
2000 hr_utility.set_location('hr_user_init_dedn.ins_uie_inp_values',985);
2001 hr_input_values.ins_3p_input_values(
2002 p_val_start_date => p_eff_start_date,
2003 p_val_end_date => p_eff_end_date,
2004 p_element_type_id => v_er_charge_eletype_id,
2005 p_primary_classification_id => p_primary_class_id,
2006 p_input_value_id => v_actdedn_inpval_id,
2007 p_default_value => v_dflt_value,
2008 p_max_value => NULL,
2009 p_min_value => NULL,
2010 p_warning_or_error_flag => NULL,
2011 p_input_value_name => v_inpval_name,
2012 p_db_items_flag => v_gen_dbi,
2013 p_costable_type => NULL,
2014 p_hot_default_flag => 'N',
2015 p_business_group_id => p_bg_id,
2016 p_legislation_code => NULL,
2017 p_startup_mode => NULL);
2018 --
2019 -- Done inserting "Deduction Actually Taken" input value.
2020
2021 hr_utility.set_location('hr_user_init_dedn.ins_dedn_formula_processing',986);
2022 v_fres_rule_id := pay_formula_results.ins_form_res_rule (
2023 p_business_group_id => p_bg_id,
2024 p_legislation_code => NULL,
2025 p_legislation_subgroup => g_template_leg_subgroup,
2026 p_effective_start_date => p_eff_start_date,
2027 p_effective_end_date => p_eff_end_date,
2028 p_status_processing_rule_id => v_stat_proc_rule_id,
2029 p_input_value_id => v_actdedn_inpval_id,
2030 p_result_name => 'DEDN_AMT',
2031 p_result_rule_type => 'I',
2032 p_severity_level => NULL,
2033 p_element_type_id => v_er_charge_eletype_id);
2034
2035 hr_utility.set_location('hr_user_init_dedn.ins_dedn_formula_processing',987);
2036
2037
2038 END IF; -- ER matching component
2039
2040 IF p_ele_ee_bond = 'Y' THEN
2041 -- create input values for:
2042 -- (*) "Purchase Price"
2043 -- (*) "Towards Purchase" (Feeds "Towards Bond Purchase" balance, get id)
2044 -- 03 Dec 1993: No longer create "Towards Purchase" input value; instead
2045 -- we have a new element type "Bond Purchase", fed by new indirect result
2046 -- from formula,
2047 -- which in turn feeds "Towards Bond Purchase" balance.
2048 -- Remember to delete appropriately from do_deletions procedure.
2049 -- Create formula result rule for:
2050 -- (*) to_purch_bal --> Update Recurring to <ELE_NAME>.TOWARDS_PURCHASE inpval
2051 -- (*) bond_refund --> Indirect to <ELE_NAME>_REFUND.AMOUNT (directpymt)
2052 --
2053 -- Creating "Purchase Price" inpval
2054 --
2055 hr_utility.set_location('hr_user_init_dedn.ins_dedn_formula_processing',125);
2056 g_inpval_disp_seq := g_inpval_disp_seq + 1;
2057 v_inpval_name := 'Purchase Price';
2058 v_inpval_uom := 'Money';
2059 v_gen_dbi := 'N'; -- Can be 'N', not needed by any other formulae
2060 v_lkp_type := NULL;
2061 v_dflt_value := NULL;
2062 v_inpval_id := pay_db_pay_setup.create_input_value (
2063 p_element_name => p_ele_name,
2064 p_name => v_inpval_name,
2065 p_uom => v_inpval_uom,
2066 p_uom_code => NULL,
2067 p_mandatory_flag => 'Y',
2068 p_generate_db_item_flag => v_gen_dbi,
2069 p_default_value => v_dflt_value,
2070 p_min_value => NULL,
2071 p_max_value => NULL,
2072 p_warning_or_error => NULL,
2073 p_lookup_type => v_lkp_type,
2074 p_formula_id => v_val_formula_id,
2075 p_hot_default_flag => 'N',
2076 p_display_sequence => g_inpval_disp_seq,
2077 p_business_group_name => p_bg_name,
2078 p_effective_start_date => p_eff_start_date,
2079 p_effective_end_date => p_eff_end_date);
2080
2081 hr_utility.set_location('hr_user_init_dedn.ins_dedn_formula_processing',127);
2082 hr_input_values.chk_input_value(
2083 p_element_type_id => p_ele_id,
2084 p_legislation_code => g_template_leg_code,
2085 p_val_start_date => p_eff_start_date,
2086 p_val_end_date => p_eff_end_date,
2087 p_insert_update_flag => 'UPDATE',
2088 p_input_value_id => v_inpval_id,
2089 p_rowid => NULL,
2090 p_recurring_flag => p_ele_proc_type,
2091 p_mandatory_flag => 'N',
2092 p_hot_default_flag => 'N',
2093 p_standard_link_flag => 'N',
2094 p_classification_type => 'N',
2095 p_name => v_inpval_name,
2096 p_uom => v_inpval_uom,
2097 p_min_value => NULL,
2098 p_max_value => NULL,
2099 p_default_value => v_dflt_value,
2100 p_lookup_type => v_lkp_type,
2101 p_formula_id => v_val_formula_id,
2102 p_generate_db_items_flag => v_gen_dbi,
2103 p_warning_or_error => NULL);
2104
2105 hr_utility.set_location('hr_user_init_dedn.ins_dedn_formula_processing',129);
2106 hr_input_values.ins_3p_input_values(
2107 p_val_start_date => p_eff_start_date,
2108 p_val_end_date => p_eff_end_date,
2109 p_element_type_id => p_ele_id,
2110 p_primary_classification_id => p_primary_class_id,
2111 p_input_value_id => v_inpval_id,
2112 p_default_value => v_dflt_value,
2113 p_max_value => NULL,
2114 p_min_value => NULL,
2115 p_warning_or_error_flag => NULL,
2116 p_input_value_name => v_inpval_name,
2117 p_db_items_flag => v_gen_dbi,
2118 p_costable_type => NULL,
2119 p_hot_default_flag => 'N',
2120 p_business_group_id => p_bg_id,
2121 p_legislation_code => NULL,
2122 p_startup_mode => NULL);
2123 --
2124 -- (HERE) Done inserting "Purchase Price" input value.
2125 --
2126 --
2127 -- Creating "Toward Purchase" inpval
2128 --
2129 hr_utility.set_location('hr_user_init_dedn.ins_dedn_formula_processing',125);
2130 g_shadow_inpval_disp_seq := g_shadow_inpval_disp_seq + 1;
2131 v_inpval_name := 'Toward Purchase';
2132 v_inpval_uom := 'Money';
2133 v_gen_dbi := 'N'; -- Can be 'N', not needed by any other formulae
2134 v_lkp_type := NULL;
2135 v_dflt_value := NULL;
2136 g_topurch_inpval_id := pay_db_pay_setup.create_input_value (
2137 p_element_name => p_shadow_ele_name,
2138 p_name => v_inpval_name,
2139 p_uom => v_inpval_uom,
2140 p_uom_code => NULL,
2141 p_mandatory_flag => 'N',
2142 p_generate_db_item_flag => v_gen_dbi,
2143 p_default_value => v_dflt_value,
2144 p_min_value => NULL,
2145 p_max_value => NULL,
2146 p_warning_or_error => NULL,
2147 p_lookup_type => v_lkp_type,
2148 p_formula_id => v_val_formula_id,
2149 p_hot_default_flag => 'N',
2150 p_display_sequence => g_shadow_inpval_disp_seq,
2151 p_business_group_name => p_bg_name,
2152 p_effective_start_date => p_eff_start_date,
2153 p_effective_end_date => p_eff_end_date);
2154
2155 hr_utility.set_location('hr_user_init_dedn.ins_dedn_formula_processing',127);
2156 hr_input_values.chk_input_value(
2157 p_element_type_id => p_shadow_ele_id,
2158 p_legislation_code => g_template_leg_code,
2159 p_val_start_date => p_eff_start_date,
2160 p_val_end_date => p_eff_end_date,
2161 p_insert_update_flag => 'UPDATE',
2162 p_input_value_id => g_topurch_inpval_id,
2163 p_rowid => NULL,
2164 p_recurring_flag => 'N',
2165 p_mandatory_flag => 'N',
2166 p_hot_default_flag => 'N',
2167 p_standard_link_flag => 'N',
2168 p_classification_type => 'N',
2169 p_name => v_inpval_name,
2170 p_uom => v_inpval_uom,
2171 p_min_value => NULL,
2172 p_max_value => NULL,
2173 p_default_value => v_dflt_value,
2174 p_lookup_type => v_lkp_type,
2175 p_formula_id => v_val_formula_id,
2176 p_generate_db_items_flag => v_gen_dbi,
2177 p_warning_or_error => NULL);
2178
2179 hr_utility.set_location('hr_user_init_dedn.ins_dedn_formula_processing',129);
2180 hr_input_values.ins_3p_input_values(
2181 p_val_start_date => p_eff_start_date,
2182 p_val_end_date => p_eff_end_date,
2183 p_element_type_id => p_shadow_ele_id,
2184 p_primary_classification_id => p_primary_class_id,
2185 p_input_value_id => g_topurch_inpval_id,
2186 p_default_value => v_dflt_value,
2187 p_max_value => NULL,
2188 p_min_value => NULL,
2189 p_warning_or_error_flag => NULL,
2190 p_input_value_name => v_inpval_name,
2191 p_db_items_flag => v_gen_dbi,
2192 p_costable_type => NULL,
2193 p_hot_default_flag => 'N',
2194 p_business_group_id => p_bg_id,
2195 p_legislation_code => NULL,
2196 p_startup_mode => NULL);
2197 --
2198 -- (HERE) Done inserting "Toward Purchase" input value.
2199 --
2200 -- Formula results
2201 -- Find id for <ELE_NAME>_REFUND pay value. Non-Payroll Payment,
2202 -- ie. direct pay(?); Used on Final Pay.
2203 /* Now it's passed in since we had to create it for non-payroll element.
2204 PLEASE NOTE: The situation was a bit reversed in another account where
2205 NON_PAYMENTS_FLAG = 'N' for class = 'Non-Payroll Payments'; but non-pay-flag
2206 was 'Y' for 'Employer Liab' class! So i hope it stays one way or the other.
2207 */
2208
2209 hr_utility.set_location('hr_user_init_dedn.ins_dedn_formula_processing',140);
2210 SELECT inp.input_value_id
2211 INTO v_eerefund_payval_id
2212 FROM pay_input_values_f inp,
2213 hr_lookups hl
2214 WHERE inp.element_type_id = p_eerefund_eletype_id
2215 AND inp.name = hl.meaning
2216 AND hl.lookup_code = 'PAY VALUE'
2217 AND hl.lookup_type = 'NAME_TRANSLATIONS';
2218
2219 hr_utility.set_location('hr_user_init_dedn.ins_dedn_formula_processing',150);
2220 v_fres_rule_id := pay_formula_results.ins_form_res_rule (
2221 p_business_group_id => p_bg_id,
2222 p_legislation_code => NULL,
2223 p_legislation_subgroup => g_template_leg_subgroup,
2224 p_effective_start_date => p_eff_start_date,
2225 p_effective_end_date => p_eff_end_date,
2226 p_status_processing_rule_id => v_stat_proc_rule_id,
2227 p_input_value_id => v_eerefund_payval_id,
2228 p_result_name => 'BOND_REFUND',
2229 p_result_rule_type => 'I',
2230 p_severity_level => NULL,
2231 p_element_type_id => p_eerefund_eletype_id);
2232
2233 hr_utility.set_location('hr_user_init_dedn.ins_dedn_formula_processing',150);
2234 v_fres_rule_id := pay_formula_results.ins_form_res_rule (
2235 p_business_group_id => p_bg_id,
2236 p_legislation_code => NULL,
2237 p_legislation_subgroup => g_template_leg_subgroup,
2238 p_effective_start_date => p_eff_start_date,
2239 p_effective_end_date => p_eff_end_date,
2240 p_status_processing_rule_id => v_stat_proc_rule_id,
2241 p_input_value_id => g_topurch_inpval_id,
2242 p_result_name => 'TO_PURCH_BAL',
2243 p_result_rule_type => 'I',
2244 p_severity_level => NULL,
2245 p_element_type_id => p_shadow_ele_id);
2246
2247 hr_utility.set_location('hr_user_init_dedn.ins_dedn_formula_processing',160);
2248
2249 END IF; -- EE Bond
2250 --
2251 -- Start Rule checks.
2252 --
2253 IF p_ele_start_rule = 'ET' THEN
2254 -- create input values for:
2255 -- (*) "Threshold Balance"
2256 -- (*) "Threshold Bal Dim"
2257 -- (*) "Threshold Amount"
2258 --
2259 -- Creating "Threshold Balance" and "Threshold Dimension" inpval
2260 --
2261 -- Creating "Threshold Amount" inpval
2262 --
2263 hr_utility.set_location('hr_user_init_dedn.ins_dedn_formula_processing',230);
2264 g_inpval_disp_seq := g_inpval_disp_seq + 1;
2265 v_inpval_name := 'Threshold Amount';
2266 v_inpval_uom := 'Money';
2267 v_gen_dbi := 'N';
2268 v_lkp_type := NULL;
2269 v_dflt_value := NULL;
2270 v_inpval_id := pay_db_pay_setup.create_input_value (
2271 p_element_name => p_ele_name,
2272 p_name => v_inpval_name,
2273 p_uom => v_inpval_uom,
2274 p_uom_code => NULL,
2275 p_mandatory_flag => 'N',
2276 p_generate_db_item_flag => v_gen_dbi,
2277 p_default_value => v_dflt_value,
2278 p_min_value => NULL,
2279 p_max_value => NULL,
2280 p_warning_or_error => NULL,
2281 p_lookup_type => v_lkp_type,
2282 p_formula_id => v_val_formula_id,
2283 p_hot_default_flag => 'N',
2284 p_display_sequence => g_inpval_disp_seq,
2285 p_business_group_name => p_bg_name,
2286 p_effective_start_date => p_eff_start_date,
2287 p_effective_end_date => p_eff_end_date);
2288
2289 hr_utility.set_location('hr_user_init_dedn.ins_dedn_formula_processing',240);
2290 hr_input_values.chk_input_value(
2291 p_element_type_id => p_ele_id,
2292 p_legislation_code => g_template_leg_code,
2293 p_val_start_date => p_eff_start_date,
2294 p_val_end_date => p_eff_end_date,
2295 p_insert_update_flag => 'UPDATE',
2296 p_input_value_id => v_inpval_id,
2297 p_rowid => NULL,
2298 p_recurring_flag => p_ele_proc_type,
2299 p_mandatory_flag => 'N',
2300 p_hot_default_flag => 'N',
2301 p_standard_link_flag => 'N',
2302 p_classification_type => 'N',
2303 p_name => v_inpval_name,
2304 p_uom => v_inpval_uom,
2305 p_min_value => NULL,
2306 p_max_value => NULL,
2307 p_default_value => v_dflt_value,
2308 p_lookup_type => v_lkp_type,
2309 p_formula_id => v_val_formula_id,
2310 p_generate_db_items_flag => v_gen_dbi,
2311 p_warning_or_error => NULL);
2312
2313 hr_utility.set_location('hr_user_init_dedn.ins_dedn_formula_processing',250);
2314 hr_input_values.ins_3p_input_values(
2315 p_val_start_date => p_eff_start_date,
2316 p_val_end_date => p_eff_end_date,
2317 p_element_type_id => p_ele_id,
2318 p_primary_classification_id => p_primary_class_id,
2319 p_input_value_id => v_inpval_id,
2320 p_default_value => v_dflt_value,
2321 p_max_value => NULL,
2322 p_min_value => NULL,
2323 p_warning_or_error_flag => NULL,
2324 p_input_value_name => v_inpval_name,
2325 p_db_items_flag => v_gen_dbi,
2326 p_costable_type => NULL,
2327 p_hot_default_flag => 'N',
2328 p_business_group_id => p_bg_id,
2329 p_legislation_code => NULL,
2330 p_startup_mode => NULL);
2331 --
2332 -- (HERE) Done inserting "Threshold Amount" input value.
2333 --
2334 ELSIF p_ele_start_rule = 'CHAINED' THEN
2335 -- create input values for:
2336 -- (*) "Chained To"
2337 --
2338 -- Creating "Chained To" inpval
2339 --
2340 hr_utility.set_location('hr_user_init_dedn.ins_dedn_formula_processing',260);
2341 g_inpval_disp_seq := g_inpval_disp_seq + 1;
2342 v_inpval_name := 'Chained To';
2343 v_inpval_uom := 'Character';
2344 v_gen_dbi := 'N';
2345 -- v_lkp_type := 'US_VALID_ELE_TYPES'; User can define.
2346 v_dflt_value := NULL;
2347 v_inpval_id := pay_db_pay_setup.create_input_value (
2348 p_element_name => p_ele_name,
2349 p_name => v_inpval_name,
2350 p_uom => v_inpval_uom,
2351 p_uom_code => NULL,
2352 p_mandatory_flag => 'N',
2353 p_generate_db_item_flag => v_gen_dbi,
2354 p_default_value => v_dflt_value,
2355 p_min_value => NULL,
2356 p_max_value => NULL,
2357 p_warning_or_error => NULL,
2358 p_lookup_type => v_lkp_type,
2359 p_formula_id => v_val_formula_id,
2360 p_hot_default_flag => 'N',
2361 p_display_sequence => g_inpval_disp_seq,
2362 p_business_group_name => p_bg_name,
2363 p_effective_start_date => p_eff_start_date,
2364 p_effective_end_date => p_eff_end_date);
2365
2366 hr_utility.set_location('hr_user_init_dedn.ins_dedn_formula_processing',270);
2367 hr_input_values.chk_input_value(
2368 p_element_type_id => p_ele_id,
2369 p_legislation_code => g_template_leg_code,
2370 p_val_start_date => p_eff_start_date,
2371 p_val_end_date => p_eff_end_date,
2372 p_insert_update_flag => 'UPDATE',
2373 p_input_value_id => v_inpval_id,
2374 p_rowid => NULL,
2375 p_recurring_flag => p_ele_proc_type,
2376 p_mandatory_flag => 'N',
2377 p_hot_default_flag => 'N',
2378 p_standard_link_flag => 'N',
2379 p_classification_type => 'N',
2380 p_name => v_inpval_name,
2381 p_uom => v_inpval_uom,
2382 p_min_value => NULL,
2383 p_max_value => NULL,
2384 p_default_value => v_dflt_value,
2385 p_lookup_type => v_lkp_type,
2386 p_formula_id => v_val_formula_id,
2387 p_generate_db_items_flag => v_gen_dbi,
2388 p_warning_or_error => NULL);
2389
2390 hr_utility.set_location('hr_user_init_dedn.ins_dedn_formula_processing',280);
2391 hr_input_values.ins_3p_input_values(
2392 p_val_start_date => p_eff_start_date,
2393 p_val_end_date => p_eff_end_date,
2394 p_element_type_id => p_ele_id,
2395 p_primary_classification_id => p_primary_class_id,
2396 p_input_value_id => v_inpval_id,
2397 p_default_value => v_dflt_value,
2398 p_max_value => NULL,
2399 p_min_value => NULL,
2400 p_warning_or_error_flag => NULL,
2401 p_input_value_name => v_inpval_name,
2402 p_db_items_flag => v_gen_dbi,
2403 p_costable_type => NULL,
2404 p_hot_default_flag => 'N',
2405 p_business_group_id => p_bg_id,
2406 p_legislation_code => NULL,
2407 p_startup_mode => NULL);
2408 --
2409 -- (HERE) Done inserting "Chained To" input value.
2410 --
2411 END IF; -- Start Rule checks
2412
2413 --
2414 -- Stop Rule checks:
2415
2416 IF UPPER(p_ele_stop_rule) = 'TOTAL REACHED' THEN
2417
2418 -- create input values for:
2419 -- (*) "Total Owed"
2420 -- (*) "Accrued" (Feeds "Accrued" balance)
2421 -- (*) "Towards Owed" (y/n val)
2422 -- create formula result rule for:
2423 -- (*) to_total_owed --> Upd Recurring to <ELE_NAME>.ACCRUED.
2424 --
2425 -- Creating "Total Owed" inpval
2426 --
2427 hr_utility.set_location('hr_user_init_dedn.ins_dedn_formula_processing',290);
2428 g_inpval_disp_seq := g_inpval_disp_seq + 1;
2429 v_inpval_name := 'Total Owed';
2430 v_inpval_uom := 'Money';
2431 v_gen_dbi := 'N';
2432 v_lkp_type := NULL;
2433 v_dflt_value := NULL;
2434 v_inpval_id := pay_db_pay_setup.create_input_value (
2435 p_element_name => p_ele_name,
2436 p_name => v_inpval_name,
2437 p_uom => v_inpval_uom,
2438 p_uom_code => NULL,
2439 p_mandatory_flag => 'N',
2440 p_generate_db_item_flag => v_gen_dbi,
2441 p_default_value => v_dflt_value,
2442 p_min_value => NULL,
2443 p_max_value => NULL,
2444 p_warning_or_error => NULL,
2445 p_lookup_type => v_lkp_type,
2446 p_formula_id => v_val_formula_id,
2447 p_hot_default_flag => 'N',
2448 p_display_sequence => g_inpval_disp_seq,
2449 p_business_group_name => p_bg_name,
2450 p_effective_start_date => p_eff_start_date,
2451 p_effective_end_date => p_eff_end_date);
2452
2453 hr_utility.set_location('hr_user_init_dedn.ins_dedn_formula_processing',300);
2454 hr_input_values.chk_input_value(
2455 p_element_type_id => p_ele_id,
2456 p_legislation_code => g_template_leg_code,
2457 p_val_start_date => p_eff_start_date,
2458 p_val_end_date => p_eff_end_date,
2459 p_insert_update_flag => 'UPDATE',
2460 p_input_value_id => v_inpval_id,
2461 p_rowid => NULL,
2462 p_recurring_flag => p_ele_proc_type,
2463 p_mandatory_flag => 'N',
2464 p_hot_default_flag => 'N',
2465 p_standard_link_flag => 'N',
2466 p_classification_type => 'N',
2467 p_name => v_inpval_name,
2468 p_uom => v_inpval_uom,
2469 p_min_value => NULL,
2470 p_max_value => NULL,
2471 p_default_value => v_dflt_value,
2472 p_lookup_type => v_lkp_type,
2473 p_formula_id => v_val_formula_id,
2474 p_generate_db_items_flag => v_gen_dbi,
2475 p_warning_or_error => NULL);
2476
2477 hr_utility.set_location('hr_user_init_dedn.ins_dedn_formula_processing',310);
2478 hr_input_values.ins_3p_input_values(
2479 p_val_start_date => p_eff_start_date,
2480 p_val_end_date => p_eff_end_date,
2481 p_element_type_id => p_ele_id,
2482 p_primary_classification_id => p_primary_class_id,
2483 p_input_value_id => v_inpval_id,
2484 p_default_value => v_dflt_value,
2485 p_max_value => NULL,
2486 p_min_value => NULL,
2487 p_warning_or_error_flag => NULL,
2488 p_input_value_name => v_inpval_name,
2489 p_db_items_flag => v_gen_dbi,
2490 p_costable_type => NULL,
2491 p_hot_default_flag => 'N',
2492 p_business_group_id => p_bg_id,
2493 p_legislation_code => NULL,
2494 p_startup_mode => NULL);
2495 --
2496 -- (HERE) Done inserting "Total Owed" input value.
2497 --
2498 --
2499 -- Creating "Accrued" inpval
2500 --
2501 hr_utility.set_location('hr_user_init_dedn.ins_dedn_formula_processing',320);
2502 g_shadow_inpval_disp_seq := g_shadow_inpval_disp_seq + 1;
2503 v_inpval_name := 'Accrued';
2504 v_inpval_uom := 'Money';
2505 v_gen_dbi := 'N';
2506 v_lkp_type := NULL;
2507 v_dflt_value := NULL;
2508 g_to_tot_inpval_id := pay_db_pay_setup.create_input_value (
2509 p_element_name => p_shadow_ele_name,
2510 p_name => v_inpval_name,
2511 p_uom => v_inpval_uom,
2512 p_uom_code => NULL,
2513 p_mandatory_flag => 'N',
2514 p_generate_db_item_flag => v_gen_dbi,
2515 p_default_value => v_dflt_value,
2516 p_min_value => NULL,
2517 p_max_value => NULL,
2518 p_warning_or_error => NULL,
2519 p_lookup_type => v_lkp_type,
2520 p_formula_id => NULL,
2521 p_hot_default_flag => 'N',
2522 p_display_sequence => g_shadow_inpval_disp_seq,
2523 p_business_group_name => p_bg_name,
2524 p_effective_start_date => p_eff_start_date,
2525 p_effective_end_date => p_eff_end_date);
2526
2527 hr_utility.set_location('hr_user_init_dedn.ins_dedn_formula_processing',330);
2528 hr_input_values.chk_input_value(
2529 p_element_type_id => p_shadow_ele_id,
2530 p_legislation_code => g_template_leg_code,
2531 p_val_start_date => p_eff_start_date,
2532 p_val_end_date => p_eff_end_date,
2533 p_insert_update_flag => 'UPDATE',
2534 p_input_value_id => g_to_tot_inpval_id,
2535 p_rowid => NULL,
2536 p_recurring_flag => p_ele_proc_type,
2537 p_mandatory_flag => 'N',
2538 p_hot_default_flag => 'N',
2539 p_standard_link_flag => 'N',
2540 p_classification_type => 'N',
2541 p_name => v_inpval_name,
2542 p_uom => v_inpval_uom,
2543 p_min_value => NULL,
2544 p_max_value => NULL,
2545 p_default_value => v_dflt_value,
2546 p_lookup_type => v_lkp_type,
2547 p_formula_id => v_val_formula_id,
2548 p_generate_db_items_flag => v_gen_dbi,
2549 p_warning_or_error => NULL);
2550
2551 hr_utility.set_location('hr_user_init_dedn.ins_dedn_formula_processing',340);
2552 hr_input_values.ins_3p_input_values(
2553 p_val_start_date => p_eff_start_date,
2554 p_val_end_date => p_eff_end_date,
2555 p_element_type_id => p_shadow_ele_id,
2556 p_primary_classification_id => p_primary_class_id,
2557 p_input_value_id => g_to_tot_inpval_id,
2558 p_default_value => v_dflt_value,
2559 p_max_value => NULL,
2560 p_min_value => NULL,
2561 p_warning_or_error_flag => NULL,
2562 p_input_value_name => v_inpval_name,
2563 p_db_items_flag => v_gen_dbi,
2564 p_costable_type => NULL,
2565 p_hot_default_flag => 'N',
2566 p_business_group_id => p_bg_id,
2567 p_legislation_code => NULL,
2568 p_startup_mode => NULL);
2569 --
2570 -- (HERE) Done inserting "Accrued" input value.
2571 --
2572 --
2573 -- Creating "Towards Owed" inpval
2574 --
2575 hr_utility.set_location('hr_user_init_dedn.ins_dedn_formula_processing',350);
2576 g_inpval_disp_seq := g_inpval_disp_seq + 1;
2577 v_inpval_name := 'Towards Owed';
2578 v_inpval_uom := 'Character';
2579 v_gen_dbi := 'N';
2580 v_lkp_type := 'YES_NO';
2581 v_dflt_value := 'Y';
2582 v_inpval_id :=pay_db_pay_setup.create_input_value (
2583 p_element_name => p_ele_name,
2584 p_name => v_inpval_name,
2585 p_uom => v_inpval_uom,
2586 p_uom_code => NULL,
2587 p_mandatory_flag => 'N',
2588 p_generate_db_item_flag => v_gen_dbi,
2589 p_default_value => v_dflt_value,
2590 p_min_value => NULL,
2591 p_max_value => NULL,
2592 p_warning_or_error => NULL,
2593 p_lookup_type => v_lkp_type,
2594 p_formula_id => v_val_formula_id,
2595 p_hot_default_flag => 'N',
2596 p_display_sequence => g_inpval_disp_seq,
2597 p_business_group_name => p_bg_name,
2598 p_effective_start_date => p_eff_start_date,
2599 p_effective_end_date => p_eff_end_date);
2600
2601 hr_utility.set_location('hr_user_init_dedn.ins_dedn_formula_processing',360);
2602 hr_input_values.chk_input_value(
2603 p_element_type_id => p_ele_id,
2604 p_legislation_code => g_template_leg_code,
2605 p_val_start_date => p_eff_start_date,
2606 p_val_end_date => p_eff_end_date,
2607 p_insert_update_flag => 'UPDATE',
2608 p_input_value_id => v_inpval_id,
2609 p_rowid => NULL,
2610 p_recurring_flag => p_ele_proc_type,
2611 p_mandatory_flag => 'N',
2612 p_hot_default_flag => 'N',
2613 p_standard_link_flag => 'N',
2614 p_classification_type => 'N',
2615 p_name => v_inpval_name,
2616 p_uom => v_inpval_uom,
2617 p_min_value => NULL,
2618 p_max_value => NULL,
2619 p_default_value => v_dflt_value,
2620 p_lookup_type => v_lkp_type,
2621 p_formula_id => v_val_formula_id,
2622 p_generate_db_items_flag => v_gen_dbi,
2623 p_warning_or_error => NULL);
2624
2625 hr_utility.set_location('hr_user_init_dedn.ins_dedn_formula_processing',370);
2626 hr_input_values.ins_3p_input_values(
2627 p_val_start_date => p_eff_start_date,
2628 p_val_end_date => p_eff_end_date,
2629 p_element_type_id => p_ele_id,
2630 p_primary_classification_id => p_primary_class_id,
2631 p_input_value_id => v_inpval_id,
2632 p_default_value => v_dflt_value,
2633 p_max_value => NULL,
2634 p_min_value => NULL,
2635 p_warning_or_error_flag => NULL,
2636 p_input_value_name => v_inpval_name,
2637 p_db_items_flag => v_gen_dbi,
2638 p_costable_type => NULL,
2639 p_hot_default_flag => 'N',
2640 p_business_group_id => p_bg_id,
2641 p_legislation_code => NULL,
2642 p_startup_mode => NULL);
2643 --
2644 -- (HERE) Done inserting "Towards Owed" input value.
2645 --
2646 -- IF p_ele_proc_type = 'R' THEN
2647 hr_utility.set_location('hr_user_init_dedn.ins_dedn_formula_processing',380);
2648 --
2649 -- Note this indirect result feeds "Accrued" inpval on
2650 -- "<ELE_NAME> Special Features" ele.
2651 --
2652 v_fres_rule_id := pay_formula_results.ins_form_res_rule (
2653 p_business_group_id => p_bg_id,
2654 p_legislation_code => NULL,
2655 p_legislation_subgroup => g_template_leg_subgroup,
2656 p_effective_start_date => p_eff_start_date,
2657 p_effective_end_date => p_eff_end_date,
2658 p_status_processing_rule_id => v_stat_proc_rule_id,
2659 p_input_value_id => g_to_tot_inpval_id,
2660 p_result_name => 'TO_TOTAL_OWED',
2661 p_result_rule_type => 'I',
2662 p_severity_level => NULL,
2663 p_element_type_id => p_shadow_ele_id);
2664
2665 -- END IF;
2666
2667 END IF; -- Stop Rule checks
2668
2669
2670
2671 -- Arrearage checks.
2672
2673 IF p_arrearage = 'Y' THEN
2674
2675 -- create input values for:
2676 -- ( ) "Clear Arrears" (on base ele)
2677 -- (*) "Arrears Contr" (on Special Features ele Feeds "Arrears" balance)
2678 -- (*) "Not Taken" (on Special Features ele)
2679 -- create formula result rule for:
2680 -- (*) to_arrears --> Indirect to <ELE_NAME>.ARREARS_CONTR
2681 -- (*) not_taken --> Indirect to <ELE_NAME>.NOT_TAKEN
2682
2683 -- Creating "Clear Arrears" inpval
2684
2685 hr_utility.set_location('hr_user_init_dedn.ins_dedn_formula_processing',381);
2686 g_inpval_disp_seq := g_inpval_disp_seq + 1;
2687 v_inpval_name := 'Clear Arrears';
2688 v_inpval_uom := 'Character';
2689 v_gen_dbi := 'N';
2690 v_lkp_type := 'YES_NO';
2691 v_dflt_value := 'N';
2692 v_inpval_id :=pay_db_pay_setup.create_input_value (
2693 p_element_name => p_ele_name,
2694 p_name => v_inpval_name,
2695 p_uom => v_inpval_uom,
2696 p_uom_code => NULL,
2697 p_mandatory_flag => 'N',
2698 p_generate_db_item_flag => v_gen_dbi,
2699 p_default_value => v_dflt_value,
2700 p_min_value => NULL,
2701 p_max_value => NULL,
2702 p_warning_or_error => NULL,
2703 p_lookup_type => v_lkp_type,
2704 p_formula_id => v_val_formula_id,
2705 p_hot_default_flag => 'N',
2706 p_display_sequence => g_inpval_disp_seq,
2707 p_business_group_name => p_bg_name,
2708 p_effective_start_date => p_eff_start_date,
2709 p_effective_end_date => p_eff_end_date);
2710
2711 hr_utility.set_location('hr_user_init_dedn.ins_dedn_formula_processing',382);
2712 hr_input_values.chk_input_value(
2713 p_element_type_id => p_ele_id,
2714 p_legislation_code => g_template_leg_code,
2715 p_val_start_date => p_eff_start_date,
2716 p_val_end_date => p_eff_end_date,
2717 p_insert_update_flag => 'UPDATE',
2718 p_input_value_id => v_inpval_id,
2719 p_rowid => NULL,
2720 p_recurring_flag => p_ele_proc_type,
2721 p_mandatory_flag => 'N',
2722 p_hot_default_flag => 'N',
2723 p_standard_link_flag => 'N',
2724 p_classification_type => 'N',
2725 p_name => v_inpval_name,
2726 p_uom => v_inpval_uom,
2727 p_min_value => NULL,
2728 p_max_value => NULL,
2729 p_default_value => v_dflt_value,
2730 p_lookup_type => v_lkp_type,
2731 p_formula_id => v_val_formula_id,
2732 p_generate_db_items_flag => v_gen_dbi,
2733 p_warning_or_error => NULL);
2734
2735 hr_utility.set_location('hr_user_init_dedn.ins_dedn_formula_processing',383);
2736 hr_input_values.ins_3p_input_values(
2737 p_val_start_date => p_eff_start_date,
2738 p_val_end_date => p_eff_end_date,
2739 p_element_type_id => p_ele_id,
2740 p_primary_classification_id => p_primary_class_id,
2741 p_input_value_id => v_inpval_id,
2742 p_default_value => v_dflt_value,
2743 p_max_value => NULL,
2744 p_min_value => NULL,
2745 p_warning_or_error_flag => NULL,
2746 p_input_value_name => v_inpval_name,
2747 p_db_items_flag => v_gen_dbi,
2748 p_costable_type => NULL,
2749 p_hot_default_flag => 'N',
2750 p_business_group_id => p_bg_id,
2751 p_legislation_code => NULL,
2752 p_startup_mode => NULL);
2753
2754 -- Done inserting "Clear Arrears" input value.
2755 hr_utility.set_location('hr_user_init_dedn.ins_dedn_formula_processing',450);
2756 v_fres_rule_id := pay_formula_results.ins_form_res_rule (
2757 p_business_group_id => p_bg_id,
2758 p_legislation_code => NULL,
2759 p_legislation_subgroup => g_template_leg_subgroup,
2760 p_effective_start_date => p_eff_start_date,
2761 p_effective_end_date => p_eff_end_date,
2762 p_status_processing_rule_id => v_stat_proc_rule_id,
2763 p_input_value_id => v_inpval_id,
2764 p_result_name => 'SET_CLEAR',
2765 p_result_rule_type => 'U',
2766 p_severity_level => NULL,
2767 p_element_type_id => p_ele_id);
2768
2769 END IF;
2770
2771 -- Creating "Arrears Contr" inpval
2772
2773 hr_utility.set_location('hr_user_init_dedn.ins_dedn_formula_processing',390);
2774 g_shadow_inpval_disp_seq := g_shadow_inpval_disp_seq + 1;
2775 v_inpval_name := 'Arrears Contr';
2776 v_inpval_uom := 'Money';
2777 v_gen_dbi := 'N';
2778 v_lkp_type := NULL;
2779 v_dflt_value := NULL;
2780 g_arrears_contr_inpval_id := pay_db_pay_setup.create_input_value (
2781 p_element_name => p_shadow_ele_name,
2782 p_name => v_inpval_name,
2783 p_uom => v_inpval_uom,
2784 p_uom_code => NULL,
2785 p_mandatory_flag => 'N',
2786 p_generate_db_item_flag => v_gen_dbi,
2787 p_default_value => v_dflt_value,
2788 p_min_value => NULL,
2789 p_max_value => NULL,
2790 p_warning_or_error => NULL,
2791 p_lookup_type => v_lkp_type,
2792 p_formula_id => v_val_formula_id,
2793 p_hot_default_flag => 'N',
2794 p_display_sequence => g_shadow_inpval_disp_seq,
2795 p_business_group_name => p_bg_name,
2796 p_effective_start_date => p_eff_start_date,
2797 p_effective_end_date => p_eff_end_date);
2798
2799 hr_utility.set_location('hr_user_init_dedn.ins_dedn_formula_processing',400);
2800 hr_input_values.chk_input_value(
2801 p_element_type_id => p_shadow_ele_id,
2802 p_legislation_code => g_template_leg_code,
2803 p_val_start_date => p_eff_start_date,
2804 p_val_end_date => p_eff_end_date,
2805 p_insert_update_flag => 'UPDATE',
2806 p_input_value_id => g_arrears_contr_inpval_id,
2807 p_rowid => NULL,
2808 p_recurring_flag => 'N',
2809 p_mandatory_flag => 'N',
2810 p_hot_default_flag => 'N',
2811 p_standard_link_flag => 'N',
2812 p_classification_type => 'N',
2813 p_name => v_inpval_name,
2814 p_uom => v_inpval_uom,
2815 p_min_value => NULL,
2816 p_max_value => NULL,
2817 p_default_value => v_dflt_value,
2818 p_lookup_type => v_lkp_type,
2819 p_formula_id => v_val_formula_id,
2820 p_generate_db_items_flag => v_gen_dbi,
2821 p_warning_or_error => NULL);
2822
2823 hr_utility.set_location('hr_user_init_dedn.ins_dedn_formula_processing',410);
2824 hr_input_values.ins_3p_input_values(
2825 p_val_start_date => p_eff_start_date,
2826 p_val_end_date => p_eff_end_date,
2827 p_element_type_id => p_shadow_ele_id,
2828 p_primary_classification_id => p_primary_class_id,
2829 p_input_value_id => g_arrears_contr_inpval_id,
2830 p_default_value => v_dflt_value,
2831 p_max_value => NULL,
2832 p_min_value => NULL,
2833 p_warning_or_error_flag => NULL,
2834 p_input_value_name => v_inpval_name,
2835 p_db_items_flag => v_gen_dbi,
2836 p_costable_type => NULL,
2837 p_hot_default_flag => 'N',
2838 p_business_group_id => p_bg_id,
2839 p_legislation_code => NULL,
2840 p_startup_mode => NULL);
2841 --
2842 -- (HERE) Done inserting "Arrears Contr" input value.
2843 --
2844 -- Can create FRR for arrears since form (PAYSUDDE) validates that
2845 -- Arrearage = 'N' for Nonrecurring deductions - but just in case something
2846 -- slips thru, we'll check proc_type again.
2847 --
2848 IF p_ele_proc_type = 'R' THEN
2849 hr_utility.set_location('hr_user_init_dedn.ins_dedn_formula_processing',450);
2850 v_fres_rule_id := pay_formula_results.ins_form_res_rule (
2851 p_business_group_id => p_bg_id,
2852 p_legislation_code => NULL,
2853 p_legislation_subgroup => g_template_leg_subgroup,
2854 p_effective_start_date => p_eff_start_date,
2855 p_effective_end_date => p_eff_end_date,
2856 p_status_processing_rule_id => v_stat_proc_rule_id,
2857 p_input_value_id => g_arrears_contr_inpval_id,
2858 p_result_name => 'TO_ARREARS',
2859 p_result_rule_type => 'I',
2860 p_severity_level => NULL,
2861 p_element_type_id => p_shadow_ele_id);
2862
2863 END IF;
2864
2865 -- IF p_partial_dedn = 'Y' THEN
2866 -- Actually, we want Not Taken inpval whether partial = Y or N, ie. if it's
2867 -- No and dedn cannot be taken, then we need to see that on Dedns Not Taken
2868 -- report.
2869 -- Creating "Not Taken" inpval
2870
2871 hr_utility.set_location('hr_user_init_dedn.ins_dedn_formula_processing',420);
2872 g_shadow_inpval_disp_seq := g_shadow_inpval_disp_seq + 1;
2873 v_inpval_name := 'Not Taken';
2874 v_inpval_uom := 'Money';
2875 v_gen_dbi := 'N';
2876 v_lkp_type := NULL;
2877 v_dflt_value := NULL;
2878 g_notaken_inpval_id := pay_db_pay_setup.create_input_value (
2879 p_element_name => p_shadow_ele_name,
2880 p_name => v_inpval_name,
2881 p_uom => v_inpval_uom,
2882 p_uom_code => NULL,
2883 p_mandatory_flag => 'N',
2884 p_generate_db_item_flag => v_gen_dbi,
2885 p_default_value => v_dflt_value,
2886 p_min_value => NULL,
2887 p_max_value => NULL,
2888 p_warning_or_error => NULL,
2889 p_lookup_type => v_lkp_type,
2890 p_formula_id => v_val_formula_id,
2891 p_hot_default_flag => 'N',
2892 p_display_sequence => g_shadow_inpval_disp_seq,
2893 p_business_group_name => p_bg_name,
2894 p_effective_start_date => p_eff_start_date,
2895 p_effective_end_date => p_eff_end_date);
2896
2897 hr_utility.set_location('hr_user_init_dedn.ins_dedn_formula_processing',430);
2898 hr_input_values.chk_input_value(
2899 p_element_type_id => p_shadow_ele_id,
2900 p_legislation_code => g_template_leg_code,
2901 p_val_start_date => p_eff_start_date,
2902 p_val_end_date => p_eff_end_date,
2903 p_insert_update_flag => 'UPDATE',
2904 p_input_value_id => g_notaken_inpval_id,
2905 p_rowid => NULL,
2906 p_recurring_flag => 'N',
2907 p_mandatory_flag => 'N',
2908 p_hot_default_flag => 'N',
2909 p_standard_link_flag => 'N',
2910 p_classification_type => 'N',
2911 p_name => v_inpval_name,
2912 p_uom => v_inpval_uom,
2913 p_min_value => NULL,
2914 p_max_value => NULL,
2915 p_default_value => v_dflt_value,
2916 p_lookup_type => v_lkp_type,
2917 p_formula_id => v_val_formula_id,
2918 p_generate_db_items_flag => v_gen_dbi,
2919 p_warning_or_error => NULL);
2920
2921 hr_utility.set_location('hr_user_init_dedn.ins_dedn_formula_processing',440);
2922 hr_input_values.ins_3p_input_values(
2923 p_val_start_date => p_eff_start_date,
2924 p_val_end_date => p_eff_end_date,
2925 p_element_type_id => p_shadow_ele_id,
2926 p_primary_classification_id => p_primary_class_id,
2927 p_input_value_id => g_notaken_inpval_id,
2928 p_default_value => v_dflt_value,
2929 p_max_value => NULL,
2930 p_min_value => NULL,
2931 p_warning_or_error_flag => NULL,
2932 p_input_value_name => v_inpval_name,
2933 p_db_items_flag => v_gen_dbi,
2934 p_costable_type => NULL,
2935 p_hot_default_flag => 'N',
2936 p_business_group_id => p_bg_id,
2937 p_legislation_code => NULL,
2938 p_startup_mode => NULL);
2939
2940 -- (HERE) Done inserting "Not Taken" input value.
2941
2942 -- IF p_ele_proc_type = 'R' THEN
2943 -- Recurring/Nonrecurring has nothing to do with Partial Dedns, if an amount
2944 -- is not taken - then it is NOT TAKEN !
2945
2946 hr_utility.set_location('hr_user_init_dedn.ins_dedn_formula_processing',460);
2947 v_fres_rule_id := pay_formula_results.ins_form_res_rule (
2948 p_business_group_id => p_bg_id,
2949 p_legislation_code => NULL,
2950 p_legislation_subgroup => g_template_leg_subgroup,
2951 p_effective_start_date => p_eff_start_date,
2952 p_effective_end_date => p_eff_end_date,
2953 p_status_processing_rule_id => v_stat_proc_rule_id,
2954 p_input_value_id => g_notaken_inpval_id,
2955 p_result_name => 'NOT_TAKEN',
2956 p_result_rule_type => 'I',
2957 p_severity_level => NULL,
2958 p_element_type_id => p_shadow_ele_id);
2959
2960 -- END IF; -- Not Taken
2961
2962 /* Involuntary Deductions do not have After Tax Component
2963
2964 -- Begin Insert Aftertax Passthrough Input Value
2965 -- create input values for:
2966 -- (*) "'Take OverLimit AT'" - -- should calculated deductions over the pretax limit be
2967 -- taken as aftertax deductions?
2968 --
2969
2970 --
2971 hr_utility.set_location('hr_user_init_dedn.ins_uie_inp_values',993);
2972 g_inpval_disp_seq := g_inpval_disp_seq + 1;
2973 v_inpval_name := 'Take OverLimit AT';
2974 v_inpval_uom := 'Character';
2975 v_gen_dbi := 'N';
2976 v_lkp_type := 'YES_NO';
2977 v_dflt_value := 'N';
2978
2979 v_passthru_inpval_id := pay_db_pay_setup.create_input_value (
2980 p_element_name => p_ele_name,
2981 p_name => v_inpval_name,
2982 p_uom => v_inpval_uom,
2983 p_uom_code => NULL,
2984 p_mandatory_flag => 'N',
2985 p_generate_db_item_flag => v_gen_dbi,
2986 p_default_value => v_dflt_value,
2987 p_min_value => NULL,
2988 p_max_value => NULL,
2989 p_warning_or_error => NULL,
2990 p_lookup_type => v_lkp_type,
2991 p_formula_id => v_val_formula_id,
2992 p_hot_default_flag => 'N',
2993 p_display_sequence => g_inpval_disp_seq,
2994 p_business_group_name => p_bg_name,
2995 p_effective_start_date => p_eff_start_date,
2996 p_effective_end_date => p_eff_end_date);
2997
2998 hr_utility.set_location('hr_user_init_dedn.ins_uie_inp_values',994);
2999 hr_input_values.chk_input_value(
3000 p_element_type_id => p_ele_id,
3001 p_legislation_code => g_template_leg_code,
3002 p_val_start_date => p_eff_start_date,
3003 p_val_end_date => p_eff_end_date,
3004 p_insert_update_flag => 'UPDATE',
3005 p_input_value_id => v_passthru_inpval_id,
3006 p_rowid => NULL,
3007 p_recurring_flag => p_ele_proc_type,
3008 p_mandatory_flag => 'N',
3009 p_hot_default_flag => 'N',
3010 p_standard_link_flag => 'N',
3011 p_classification_type => 'N',
3012 p_name => v_inpval_name,
3013 p_uom => v_inpval_uom,
3014 p_min_value => NULL,
3015 p_max_value => NULL,
3016 p_default_value => NULL,
3017 p_lookup_type => v_dflt_value,
3018 p_formula_id => NULL,
3019 p_generate_db_items_flag => v_gen_dbi,
3020 p_warning_or_error => NULL);
3021
3022 hr_utility.set_location('hr_user_init_dedn.ins_uie_inp_values',995);
3023 hr_input_values.ins_3p_input_values(
3024 p_val_start_date => p_eff_start_date,
3025 p_val_end_date => p_eff_end_date,
3026 p_element_type_id => p_ele_id,
3027 p_primary_classification_id => p_primary_class_id,
3028 p_input_value_id => v_passthru_inpval_id,
3029 p_default_value => v_dflt_value,
3030 p_max_value => NULL,
3031 p_min_value => NULL,
3032 p_warning_or_error_flag => NULL,
3033 p_input_value_name => v_inpval_name,
3034 p_db_items_flag => v_gen_dbi,
3035 p_costable_type => NULL,
3036 p_hot_default_flag => 'N',
3037 p_business_group_id => p_bg_id,
3038 p_legislation_code => NULL,
3039 p_startup_mode => NULL);
3040 --
3041 -- Done inserting Insert Aftertax Passthrough Input Value
3042
3043 */
3044
3045
3046 END ins_dedn_formula_processing;
3047
3048 ---------------------------- ins_dedn_input_vals ------------------------------
3049 PROCEDURE ins_dedn_input_vals (
3050 p_ele_type_id in number,
3051 p_ele_name in varchar2,
3052 p_shadow_ele_type_id in number,
3053 p_shadow_ele_name in varchar2,
3054 p_inputs_ele_type_id in number,
3055 p_inputs_ele_name in varchar2,
3056 p_eff_start in date,
3057 p_eff_end in date,
3058 p_primary_class_id in number,
3059 p_ele_class in varchar2,
3060 p_ele_cat in varchar2,
3061 p_ele_proc_type in varchar2,
3062 p_bg_id in number,
3063 p_bg_name in varchar2,
3064 p_amt_rule in varchar2) IS
3065 --
3066 -- local vars
3067 --
3068 v_inpval_id NUMBER(9);
3069 v_inpval_name VARCHAR2(30);
3070 v_inpval_uom VARCHAR2(80);
3071 v_gen_dbi VARCHAR2(1);
3072 v_dflt_value VARCHAR2(60);
3073 v_lkp_type VARCHAR2(30);
3074 v_formula_id NUMBER(9);
3075 v_status_proc_id NUMBER(9);
3076 v_resrule_id NUMBER(9);
3077 --
3078 -- More input values may be necessary, so make direct calls to CORE_API
3079 -- packaged procedures to:
3080 -- 1) Insert input values according to Class and Category
3081 -- (Done in ins_dedn_formula_processing)
3082 -- 2) Insert input values required for ALL user-initiated earnings elements
3083 -- (ie. batch entry fields, override fields)
3084
3085 -- Add input values (in order) "Replacement Amount", "Additional Amount"
3086 -- Only create Replacement Amount if calc rule <> Flat Amount.
3087 --
3088 BEGIN
3089
3090
3091 If p_ele_proc_type = 'R' then /* Not required for NR Elements */
3092
3093 --
3094 -- Create inpvals for "Special Inputs"
3095 --
3096 hr_utility.set_location('hr_user_init_dedn.ins_uie_inp_values',40);
3097 g_inputs_inpval_disp_seq := 1;
3098 v_inpval_name := 'Replace Amt';
3099 v_inpval_uom := 'Money';
3100 v_gen_dbi := 'N';
3101 v_lkp_type := NULL;
3102 v_dflt_value := NULL;
3103
3104 gi_repl_inpval_id := pay_db_pay_setup.create_input_value (
3105 p_element_name => p_inputs_ele_name,
3106 p_name => v_inpval_name,
3107 p_uom => v_inpval_uom,
3108 p_uom_code => NULL,
3109 p_mandatory_flag => 'N',
3110 p_generate_db_item_flag => v_gen_dbi,
3111 p_default_value => v_dflt_value,
3112 p_min_value => NULL,
3113 p_max_value => NULL,
3114 p_warning_or_error => NULL,
3115 p_lookup_type => v_lkp_type,
3116 p_formula_id => v_formula_id,
3117 p_hot_default_flag => 'N',
3118 p_display_sequence => g_inputs_inpval_disp_seq,
3119 p_business_group_name => p_bg_name,
3120 p_effective_start_date => p_eff_start,
3121 p_effective_end_date => p_eff_end);
3122
3123 hr_utility.set_location('hr_user_init_dedn.ins_uie_inp_values',50);
3124 hr_input_values.chk_input_value(
3125 p_element_type_id => p_inputs_ele_type_id,
3126 p_legislation_code => g_template_leg_code,
3127 p_val_start_date => p_eff_start,
3128 p_val_end_date => p_eff_end,
3129 p_insert_update_flag => 'UPDATE',
3130 p_input_value_id => gi_repl_inpval_id,
3131 p_rowid => NULL,
3132 p_recurring_flag => 'N',
3133 p_mandatory_flag => 'N',
3134 p_hot_default_flag => 'N',
3135 p_standard_link_flag => 'N',
3136 p_classification_type => 'N',
3137 p_name => v_inpval_name,
3138 p_uom => v_inpval_uom,
3139 p_min_value => NULL,
3140 p_max_value => NULL,
3141 p_default_value => NULL,
3142 p_lookup_type => v_dflt_value,
3143 p_formula_id => NULL,
3144 p_generate_db_items_flag => v_gen_dbi,
3145 p_warning_or_error => NULL);
3146
3147 hr_utility.set_location('hr_user_init_dedn.ins_uie_inp_values',60);
3148 hr_input_values.ins_3p_input_values(
3149 p_val_start_date => p_eff_start,
3150 p_val_end_date => p_eff_end,
3151 p_element_type_id => p_inputs_ele_type_id,
3152 p_primary_classification_id => p_primary_class_id,
3153 p_input_value_id => gi_repl_inpval_id,
3154 p_default_value => v_dflt_value,
3155 p_max_value => NULL,
3156 p_min_value => NULL,
3157 p_warning_or_error_flag => NULL,
3158 p_input_value_name => v_inpval_name,
3159 p_db_items_flag => v_gen_dbi,
3160 p_costable_type => NULL,
3161 p_hot_default_flag => 'N',
3162 p_business_group_id => p_bg_id,
3163 p_legislation_code => NULL,
3164 p_startup_mode => NULL);
3165 --
3166 -- Done inserting "Replacement Amt" input value.
3167 --
3168 hr_utility.set_location('hr_user_init_dedn.ins_uie_inp_values',70);
3169 g_inputs_inpval_disp_seq := g_inputs_inpval_disp_seq + 1;
3170 v_inpval_name := 'Addl Amt';
3171 v_inpval_uom := 'Money';
3172 v_gen_dbi := 'N';
3173 v_lkp_type := NULL;
3174 v_dflt_value := NULL;
3175
3176 gi_addl_inpval_id := pay_db_pay_setup.create_input_value (
3177 p_element_name => p_inputs_ele_name,
3178 p_name => v_inpval_name,
3179 p_uom => v_inpval_uom,
3180 p_uom_code => NULL,
3181 p_mandatory_flag => 'N',
3182 p_generate_db_item_flag => v_gen_dbi,
3183 p_default_value => v_dflt_value,
3184 p_min_value => NULL,
3185 p_max_value => NULL,
3186 p_warning_or_error => NULL,
3187 p_lookup_type => v_lkp_type,
3188 p_formula_id => v_formula_id,
3189 p_hot_default_flag => 'N',
3190 p_display_sequence => g_inputs_inpval_disp_seq,
3191 p_business_group_name => p_bg_name,
3192 p_effective_start_date => p_eff_start,
3193 p_effective_end_date => p_eff_end);
3194
3195 hr_utility.set_location('hr_user_init_dedn.ins_uie_inp_values',75);
3196 hr_input_values.chk_input_value(
3197 p_element_type_id => p_inputs_ele_type_id,
3198 p_legislation_code => g_template_leg_code,
3199 p_val_start_date => p_eff_start,
3200 p_val_end_date => p_eff_end,
3201 p_insert_update_flag => 'UPDATE',
3202 p_input_value_id => gi_addl_inpval_id,
3203 p_rowid => NULL,
3204 p_recurring_flag => 'N',
3205 p_mandatory_flag => 'N',
3206 p_hot_default_flag => 'N',
3207 p_standard_link_flag => 'N',
3208 p_classification_type => 'N',
3209 p_name => v_inpval_name,
3210 p_uom => v_inpval_uom,
3211 p_min_value => NULL,
3212 p_max_value => NULL,
3213 p_default_value => NULL,
3214 p_lookup_type => v_dflt_value,
3215 p_formula_id => NULL,
3216 p_generate_db_items_flag => v_gen_dbi,
3217 p_warning_or_error => NULL);
3218
3219 hr_utility.set_location('hr_user_init_dedn.ins_uie_inp_values',80);
3220 hr_input_values.ins_3p_input_values(
3221 p_val_start_date => p_eff_start,
3222 p_val_end_date => p_eff_end,
3223 p_element_type_id => p_inputs_ele_type_id,
3224 p_primary_classification_id => p_primary_class_id,
3225 p_input_value_id => gi_addl_inpval_id,
3226 p_default_value => v_dflt_value,
3227 p_max_value => NULL,
3228 p_min_value => NULL,
3229 p_warning_or_error_flag => NULL,
3230 p_input_value_name => v_inpval_name,
3231 p_db_items_flag => v_gen_dbi,
3232 p_costable_type => NULL,
3233 p_hot_default_flag => 'N',
3234 p_business_group_id => p_bg_id,
3235 p_legislation_code => NULL,
3236 p_startup_mode => NULL);
3237
3238
3239 hr_utility.set_location('hr_user_init_dedn.ins_uie_inp_values',81);
3240 g_inputs_inpval_disp_seq := g_inputs_inpval_disp_seq + 1;
3241 v_inpval_name := 'Adjust Arrears';
3242 v_inpval_uom := 'Money';
3243 v_gen_dbi := 'N';
3244 v_lkp_type := NULL;
3245 v_dflt_value := NULL;
3246
3247 g_adj_arrears_inpval_id := pay_db_pay_setup.create_input_value (
3248 p_element_name => p_inputs_ele_name,
3249 p_name => v_inpval_name,
3250 p_uom => v_inpval_uom,
3251 p_uom_code => NULL,
3252 p_mandatory_flag => 'N',
3253 p_generate_db_item_flag => v_gen_dbi,
3254 p_default_value => v_dflt_value,
3255 p_min_value => NULL,
3256 p_max_value => NULL,
3257 p_warning_or_error => NULL,
3258 p_lookup_type => v_lkp_type,
3259 p_formula_id => v_formula_id,
3260 p_hot_default_flag => 'N',
3261 p_display_sequence => g_inputs_inpval_disp_seq,
3262 p_business_group_name => p_bg_name,
3263 p_effective_start_date => p_eff_start,
3264 p_effective_end_date => p_eff_end);
3265
3266 hr_utility.set_location('hr_user_init_dedn.ins_uie_inp_values',50);
3267 hr_input_values.chk_input_value(
3268 p_element_type_id => p_inputs_ele_type_id,
3269 p_legislation_code => g_template_leg_code,
3270 p_val_start_date => p_eff_start,
3271 p_val_end_date => p_eff_end,
3272 p_insert_update_flag => 'UPDATE',
3273 p_input_value_id => g_adj_arrears_inpval_id,
3274 p_rowid => NULL,
3275 p_recurring_flag => 'N',
3276 p_mandatory_flag => 'N',
3277 p_hot_default_flag => 'N',
3278 p_standard_link_flag => 'N',
3279 p_classification_type => 'N',
3280 p_name => v_inpval_name,
3281 p_uom => v_inpval_uom,
3282 p_min_value => NULL,
3283 p_max_value => NULL,
3284 p_default_value => NULL,
3285 p_lookup_type => v_dflt_value,
3286 p_formula_id => NULL,
3287 p_generate_db_items_flag => v_gen_dbi,
3288 p_warning_or_error => NULL);
3289
3290 hr_utility.set_location('hr_user_init_dedn.ins_uie_inp_values',60);
3291 hr_input_values.ins_3p_input_values(
3292 p_val_start_date => p_eff_start,
3293 p_val_end_date => p_eff_end,
3294 p_element_type_id => p_inputs_ele_type_id,
3295 p_primary_classification_id => p_primary_class_id,
3296 p_input_value_id => g_adj_arrears_inpval_id,
3297 p_default_value => v_dflt_value,
3298 p_max_value => NULL,
3299 p_min_value => NULL,
3300 p_warning_or_error_flag => NULL,
3301 p_input_value_name => v_inpval_name,
3302 p_db_items_flag => v_gen_dbi,
3303 p_costable_type => NULL,
3304 p_hot_default_flag => 'N',
3305 p_business_group_id => p_bg_id,
3306 p_legislation_code => NULL,
3307 p_startup_mode => NULL);
3308
3309 End if; /* Not required for NR Elements */
3310 --
3311 -- Done inserting "Adjust Arrears" input value.
3312 --
3313 -- Create inpvals for "Special Features"
3314 --
3315
3316 If p_ele_proc_type = 'R' then /* Not required for NR Elements */
3317
3318 hr_utility.set_location('hr_user_init_dedn.ins_uie_inp_values',40);
3319 g_shadow_inpval_disp_seq := g_shadow_inpval_disp_seq + 1;
3320 v_inpval_name := 'Replacement Amt';
3321 v_inpval_uom := 'Money';
3322 v_gen_dbi := 'N';
3323 v_lkp_type := NULL;
3324 v_dflt_value := NULL;
3325
3326 g_repl_inpval_id := pay_db_pay_setup.create_input_value (
3327 p_element_name => p_shadow_ele_name,
3328 p_name => v_inpval_name,
3329 p_uom => v_inpval_uom,
3330 p_uom_code => NULL,
3331 p_mandatory_flag => 'N',
3332 p_generate_db_item_flag => v_gen_dbi,
3333 p_default_value => v_dflt_value,
3334 p_min_value => NULL,
3335 p_max_value => NULL,
3336 p_warning_or_error => NULL,
3337 p_lookup_type => v_lkp_type,
3338 p_formula_id => v_formula_id,
3339 p_hot_default_flag => 'N',
3340 p_display_sequence => g_shadow_inpval_disp_seq,
3341 p_business_group_name => p_bg_name,
3342 p_effective_start_date => p_eff_start,
3343 p_effective_end_date => p_eff_end);
3344
3345 hr_utility.set_location('hr_user_init_dedn.ins_uie_inp_values',50);
3346 hr_input_values.chk_input_value(
3347 p_element_type_id => p_shadow_ele_type_id,
3348 p_legislation_code => g_template_leg_code,
3349 p_val_start_date => p_eff_start,
3350 p_val_end_date => p_eff_end,
3351 p_insert_update_flag => 'UPDATE',
3352 p_input_value_id => g_repl_inpval_id,
3353 p_rowid => NULL,
3354 p_recurring_flag => 'N',
3355 p_mandatory_flag => 'N',
3356 p_hot_default_flag => 'N',
3357 p_standard_link_flag => 'N',
3358 p_classification_type => 'N',
3359 p_name => v_inpval_name,
3360 p_uom => v_inpval_uom,
3361 p_min_value => NULL,
3362 p_max_value => NULL,
3363 p_default_value => NULL,
3364 p_lookup_type => v_dflt_value,
3365 p_formula_id => NULL,
3366 p_generate_db_items_flag => v_gen_dbi,
3367 p_warning_or_error => NULL);
3368
3369 hr_utility.set_location('hr_user_init_dedn.ins_uie_inp_values',60);
3370 hr_input_values.ins_3p_input_values(
3371 p_val_start_date => p_eff_start,
3372 p_val_end_date => p_eff_end,
3373 p_element_type_id => p_shadow_ele_type_id,
3374 p_primary_classification_id => p_primary_class_id,
3375 p_input_value_id => g_repl_inpval_id,
3376 p_default_value => v_dflt_value,
3377 p_max_value => NULL,
3378 p_min_value => NULL,
3379 p_warning_or_error_flag => NULL,
3380 p_input_value_name => v_inpval_name,
3381 p_db_items_flag => v_gen_dbi,
3382 p_costable_type => NULL,
3383 p_hot_default_flag => 'N',
3384 p_business_group_id => p_bg_id,
3385 p_legislation_code => NULL,
3386 p_startup_mode => NULL);
3387 --
3388 -- Done inserting "Replacement Amt" input value.
3389 --
3390 hr_utility.set_location('hr_user_init_dedn.ins_uie_inp_values',70);
3391 g_shadow_inpval_disp_seq := g_shadow_inpval_disp_seq + 1;
3392 v_inpval_name := 'Additional Amt';
3393 v_inpval_uom := 'Money';
3394 v_gen_dbi := 'N';
3395 v_lkp_type := NULL;
3396 v_dflt_value := NULL;
3397
3398 g_addl_inpval_id := pay_db_pay_setup.create_input_value (
3399 p_element_name => p_shadow_ele_name,
3400 p_name => v_inpval_name,
3401 p_uom => v_inpval_uom,
3402 p_uom_code => NULL,
3403 p_mandatory_flag => 'N',
3404 p_generate_db_item_flag => v_gen_dbi,
3405 p_default_value => v_dflt_value,
3406 p_min_value => NULL,
3407 p_max_value => NULL,
3408 p_warning_or_error => NULL,
3409 p_lookup_type => v_lkp_type,
3410 p_formula_id => v_formula_id,
3411 p_hot_default_flag => 'N',
3412 p_display_sequence => g_shadow_inpval_disp_seq,
3413 p_business_group_name => p_bg_name,
3414 p_effective_start_date => p_eff_start,
3415 p_effective_end_date => p_eff_end);
3416
3417 hr_utility.set_location('hr_user_init_dedn.ins_uie_inp_values',75);
3418 hr_input_values.chk_input_value(
3419 p_element_type_id => p_shadow_ele_type_id,
3420 p_legislation_code => g_template_leg_code,
3421 p_val_start_date => p_eff_start,
3422 p_val_end_date => p_eff_end,
3423 p_insert_update_flag => 'UPDATE',
3424 p_input_value_id => g_addl_inpval_id,
3425 p_rowid => NULL,
3426 p_recurring_flag => 'N',
3427 p_mandatory_flag => 'N',
3428 p_hot_default_flag => 'N',
3429 p_standard_link_flag => 'N',
3430 p_classification_type => 'N',
3431 p_name => v_inpval_name,
3432 p_uom => v_inpval_uom,
3433 p_min_value => NULL,
3434 p_max_value => NULL,
3435 p_default_value => NULL,
3436 p_lookup_type => v_dflt_value,
3437 p_formula_id => NULL,
3438 p_generate_db_items_flag => v_gen_dbi,
3439 p_warning_or_error => NULL);
3440
3441 hr_utility.set_location('hr_user_init_dedn.ins_uie_inp_values',80);
3442 hr_input_values.ins_3p_input_values(
3443 p_val_start_date => p_eff_start,
3444 p_val_end_date => p_eff_end,
3445 p_element_type_id => p_shadow_ele_type_id,
3446 p_primary_classification_id => p_primary_class_id,
3447 p_input_value_id => g_addl_inpval_id,
3448 p_default_value => v_dflt_value,
3449 p_max_value => NULL,
3450 p_min_value => NULL,
3451 p_warning_or_error_flag => NULL,
3452 p_input_value_name => v_inpval_name,
3453 p_db_items_flag => v_gen_dbi,
3454 p_costable_type => NULL,
3455 p_hot_default_flag => 'N',
3456 p_business_group_id => p_bg_id,
3457 p_legislation_code => NULL,
3458 p_startup_mode => NULL);
3459 --
3460 -- Done inserting "Addl Amt" input value.
3461
3462 end if; /* Not required for NR Elements */
3463
3464
3465 -- Generic input values for [all] deductions.
3466 --
3467 -- *** Also need to create FORMULA RESULT RULES to "zero" out these values.
3468 --
3469 -- Find "Standard" status processing rule id for this element.
3470 -- We know this must be the only one right now:
3471 --
3472 --
3473
3474 hr_utility.set_location('hr_user_init_dedn.ins_uie_inp_values',85);
3475 SELECT status_processing_rule_id
3476 INTO v_status_proc_id
3477 FROM pay_status_processing_rules_f
3478 WHERE assignment_status_type_id IS NULL
3479 AND element_type_id = p_ele_type_id;
3480
3481 --
3482 -- Additional and Replacement amount input values only need UPDREE FRR if
3483 -- they are recurring! Otherwise payroll run bombs w/ ASSERTION ERROR
3484 -- when trying to "update recurring" on a nonrecurring element (i think
3485 -- that's the prob).
3486 --
3487 IF p_ele_proc_type = 'R' THEN
3488 hr_utility.set_location('hr_user_init_dedn.ins_uie_inp_values',90);
3489 v_resrule_id := pay_formula_results.ins_form_res_rule (
3490 p_business_group_id => p_bg_id,
3491 p_legislation_code => NULL,
3492 p_legislation_subgroup => g_template_leg_subgroup,
3493 p_effective_start_date => p_eff_start,
3494 p_effective_end_date => p_eff_end,
3495 p_status_processing_rule_id => v_status_proc_id,
3496 p_input_value_id => g_repl_inpval_id,
3497 p_result_name => 'CLEAR_REPL_AMT',
3498 p_result_rule_type => 'I',
3499 p_severity_level => NULL,
3500 p_element_type_id => p_shadow_ele_type_id);
3501
3502 hr_utility.set_location('hr_user_init_dedn.ins_uie_inp_values',95);
3503 v_resrule_id := pay_formula_results.ins_form_res_rule (
3504 p_business_group_id => p_bg_id,
3505 p_legislation_code => NULL,
3506 p_legislation_subgroup => g_template_leg_subgroup,
3507 p_effective_start_date => p_eff_start,
3508 p_effective_end_date => p_eff_end,
3509 p_status_processing_rule_id => v_status_proc_id,
3510 p_input_value_id => g_addl_inpval_id,
3511 p_result_name => 'CLEAR_ADDL_AMT',
3512 p_result_rule_type => 'I',
3513 p_severity_level => NULL,
3514 p_element_type_id => p_shadow_ele_type_id);
3515
3516 END IF;
3517
3518 hr_utility.set_location('hr_user_init_dedn.ins_uie_inp_values',100);
3519 v_resrule_id := pay_formula_results.ins_form_res_rule (
3520 p_business_group_id => p_bg_id,
3521 p_legislation_code => NULL,
3522 p_legislation_subgroup => g_template_leg_subgroup,
3523 p_effective_start_date => p_eff_start,
3524 p_effective_end_date => p_eff_end,
3525 p_status_processing_rule_id => v_status_proc_id,
3526 p_input_value_id => NULL,
3527 p_result_name => 'MESG',
3528 p_result_rule_type => 'M',
3529 p_severity_level => 'W',
3530 p_element_type_id => NULL);
3531
3532 END ins_dedn_input_vals;
3533
3534 PROCEDURE do_defined_balances ( p_bal_name IN VARCHAR2,
3535 p_bg_name IN VARCHAR2,
3536 p_no_payments IN BOOLEAN default FALSE) IS
3537
3538 -- local vars
3539
3540 TYPE text_table IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
3541
3542 suffixes text_table;
3543 dim_id number(9);
3544 dim_name varchar2(80);
3545 num_suffixes number;
3546
3547 already_exists number;
3548 v_business_group_id number;
3549
3550 BEGIN
3551
3552 suffixes(1) := '_ASG_RUN';
3553 suffixes(2) := '_ASG_PTD';
3554 suffixes(3) := '_ASG_MONTH';
3555 suffixes(4) := '_ASG_QTD';
3556 suffixes(5) := '_ASG_YTD';
3557 suffixes(6) := '_ASG_GRE_RUN';
3558 suffixes(7) := '_ASG_GRE_PTD';
3559 suffixes(8) := '_ASG_GRE_MONTH';
3560 suffixes(9) := '_ASG_GRE_QTD';
3561 suffixes(10) := '_ASG_GRE_YTD';
3562
3563 suffixes(11) := '_PER_RUN';
3564 suffixes(12) := '_PER_MONTH';
3565 suffixes(13) := '_PER_QTD';
3566 suffixes(14) := '_PER_YTD';
3567 suffixes(15) := '_PER_GRE_RUN';
3568 suffixes(16) := '_PER_GRE_MONTH';
3569 suffixes(17) := '_PER_GRE_QTD';
3570 suffixes(18) := '_PER_GRE_YTD';
3571
3572 /* *** WWBug 133133 start *** */
3573 /*
3574 Add suffixes to create defined bals for GRE_YTD, GRE_RUN, GRE_ITD.
3575 Number of defined balance suffixes increases to 22
3576 */
3577 suffixes(19) := '_GRE_RUN';
3578 suffixes(20) := '_GRE_YTD';
3579 suffixes(21) := '_GRE_ITD';
3580
3581 suffixes(22) := '_PAYMENTS';
3582 suffixes(23) := '_ASG_PAYMENTS';
3583
3584 num_suffixes := 23;
3585
3586 select business_group_id
3587 into v_business_group_id
3588 from per_business_groups
3589 where upper(name) = upper(p_bg_name);
3590
3591 /* *** WWBug 133133 finish *** */
3592
3593 for i in 1..num_suffixes loop
3594
3595 select dimension_name, balance_dimension_id
3596 into dim_name, dim_id
3597 from pay_balance_dimensions
3598 where database_item_suffix = suffixes(i)
3599 and legislation_code = g_template_leg_code
3600 and business_group_id is null;
3601
3602 /* the following select statement has been commented. Earlier it was not
3603 checking whether record already exists in pay_defined_balance or not. Now
3604 it is checking it and for a particular business_group_id.
3605 */
3606
3607 SELECT count(0)
3608 INTO already_exists
3609 FROM pay_defined_balances db,
3610 pay_balance_types bt
3611 WHERE db.balance_type_id = bt.balance_type_id
3612 AND bt.balance_name = p_bal_name
3613 AND db.balance_dimension_id = dim_id
3614 AND bt.business_group_id = v_business_group_id;
3615
3616
3617 if (already_exists = 0) then
3618
3619
3620 IF p_no_payments = TRUE and
3621 (suffixes(i) = '_PAYMENTS' or suffixes(i) = '_ASG_PAYMENTS') THEN
3622
3623 NULL;
3624
3625 ELSIF suffixes(i) = '_ASG_GRE_RUN' THEN
3626
3627 pay_db_pay_setup.create_defined_balance(
3628 p_balance_name => p_bal_name,
3629 p_balance_dimension => dim_name,
3630 p_business_group_name => p_bg_name,
3631 p_legislation_code => NULL,
3632 p_save_run_bal => 'Y');
3633
3634 ELSE
3635
3636 pay_db_pay_setup.create_defined_balance(
3637 p_balance_name => p_bal_name,
3638 p_balance_dimension => dim_name,
3639 p_business_group_name => p_bg_name,
3640 p_legislation_code => NULL);
3641
3642 END IF;
3643
3644 end if;
3645
3646 end loop;
3647
3648 END do_defined_balances;
3649
3650 ----------------------- ins_deduction_template Main ------------------------
3651 --
3652 -- Main Procedure
3653
3654 BEGIN
3655
3656 --
3657 -- Set session date
3658 hr_utility.set_location('hr_user_init_dedn.ins_deduction_template',10);
3659 pay_db_pay_setup.set_session_date(nvl(p_ele_eff_start_date, sysdate));
3660 g_eff_start_date := nvl(p_ele_eff_start_date, sysdate);
3661 g_eff_end_date := nvl(p_ele_eff_end_date, c_end_of_time);
3662
3663
3664 -- Set "globals": v_bg_name
3665 hr_utility.set_location('hr_user_init_dedn.ins_deduction_template',20);
3666 select name
3667 into v_bg_name
3668 from per_business_groups
3669 where business_group_id = p_bg_id;
3670
3671 --------------------- Create Balances Types and Defined Balances --------------
3672 --
3673 -- Create associated balances for deductions.
3674 --
3675 -- "Primary" Balance:
3676 --
3677 /* Call pay_balance_types_pkg.chk_balance_type twice in order to check that
3678 the "Primary Balance" is ok to generate:
3679 procedure chk_balance_type
3680 (
3681 p_row_id varchar2,
3682 p_business_group_id number,
3683 p_legislation_code varchar2,
3684 p_balance_name varchar2,
3685 p_reporting_name varchar2,
3686 p_assignment_remuneration_flag varchar2
3687 ) is
3688 */
3689
3690 hr_utility.set_location('pyusuidt',217);
3691 -- Check element name, ie. primary balance name, is unique to
3692 -- balances within this BG.
3693 pay_balance_types_pkg.chk_balance_type(
3694 p_row_id => NULL,
3695 p_business_group_id => p_bg_id,
3696 p_legislation_code => NULL,
3697 p_balance_name => p_ele_name,
3698 p_reporting_name => p_ele_name,
3699 p_assignment_remuneration_flag => 'N');
3700
3701 hr_utility.set_location('pyusuidt',317);
3702 -- Check element name, ie. primary balance name, is unique to
3703 -- balances provided as startup data.
3704 pay_balance_types_pkg.chk_balance_type(
3705 p_row_id => NULL,
3706 p_business_group_id => NULL,
3707 p_legislation_code => 'US',
3708 p_balance_name => p_ele_name,
3709 p_reporting_name => p_ele_name,
3710 p_assignment_remuneration_flag => 'N');
3711
3712 hr_utility.set_location('hr_user_init_dedn.ins_deduction_template',25);
3713
3714 -- Emp Balance for enhancement Bug 3311781
3715 if upper(p_ele_classification) = 'VOLUNTARY DEDUCTIONS' then
3716 l_balance_category := 'Voluntary Deductions';
3717 else
3718 l_balance_category := 'After-Tax Deductions';
3719 end if;
3720
3721 v_bal_type_id := pay_db_pay_setup.create_balance_type(
3722 p_balance_name => p_ele_name,
3723 p_uom => 'Money',
3724 p_reporting_name => p_ele_name,
3725 p_business_group_name => v_bg_name,
3726 p_legislation_code => NULL,
3727 p_legislation_subgroup => NULL,
3728 p_balance_category => l_balance_category, -- Bug 3311781
3729 p_bc_leg_code => 'US',
3730 p_effective_date => g_eff_start_date);
3731
3732 do_defined_balances( p_bal_name => p_ele_name,
3733 p_bg_name => v_bg_name);
3734
3735 --
3736 -- Other balances based on certain attributes:
3737 --
3738
3739 If p_ele_processing_type = 'R' then /* Not required for NR Elements */
3740
3741 v_addl_amt_bal_name := SUBSTR(p_ele_name, 1, 67)||' Additional';
3742 --v_bal_rpt_name := SUBSTR(p_ele_name, 1, 17)||' Additional';
3743 hr_utility.set_location('pyusuidt',119);
3744 v_addl_amt_bal_type_id := pay_db_pay_setup.create_balance_type(
3745 p_balance_name => v_addl_amt_bal_name,
3746 p_uom => 'Money',
3747 p_reporting_name => v_addl_amt_bal_name,
3748 p_business_group_name => v_bg_name,
3749 p_legislation_code => NULL,
3750 p_legislation_subgroup => NULL,
3751 p_balance_category => l_balance_category, -- Bug 3311781
3752 p_bc_leg_code => 'US',
3753 p_effective_date => g_eff_start_date);
3754
3755 hr_utility.set_location('pyusuidt',127);
3756 pay_db_pay_setup.create_defined_balance (
3757 p_balance_name => v_addl_amt_bal_name,
3758 p_balance_dimension => 'Assignment within Government Reporting Entity Inception to Date',
3759 p_business_group_name => v_bg_name,
3760 p_legislation_code => NULL);
3761
3762 v_repl_amt_bal_name := SUBSTR(p_ele_name, 1, 67)||' Replacement';
3763 --v_bal_rpt_name := SUBSTR(p_ele_name, 1, 17)||' Replacement';
3764 hr_utility.set_location('pyusuidt',119);
3765 v_repl_amt_bal_type_id := pay_db_pay_setup.create_balance_type(
3766 p_balance_name => v_repl_amt_bal_name,
3767 p_uom => 'Money',
3768 p_reporting_name => v_repl_amt_bal_name,
3769 p_business_group_name => v_bg_name,
3770 p_legislation_code => NULL,
3771 p_legislation_subgroup => NULL,
3772 p_balance_category => l_balance_category, -- Bug 3311781
3773 p_bc_leg_code => 'US',
3774 p_effective_date => g_eff_start_date);
3775
3776 hr_utility.set_location('pyusuidt',129);
3777 pay_db_pay_setup.create_defined_balance (
3778 p_balance_name => v_repl_amt_bal_name,
3779 p_balance_dimension => 'Assignment within Government Reporting Entity Inception to Date',
3780 p_business_group_name => v_bg_name,
3781 p_legislation_code => NULL);
3782
3783 End if; /* Not required for NR Elements */
3784
3785 v_balance_name := SUBSTR(p_ele_name, 1, 67)||' Not Taken';
3786 --v_bal_rpt_name := SUBSTR(p_ele_name, 1, 17)||' Not Taken';
3787 hr_utility.set_location('pyusuidt',130);
3788 v_notaken_bal_type_id := pay_db_pay_setup.create_balance_type(
3789 p_balance_name => v_balance_name,
3790 p_uom => 'Money',
3791 p_reporting_name => v_balance_name,
3792 p_business_group_name => v_bg_name,
3793 p_legislation_code => NULL,
3794 p_legislation_subgroup => NULL,
3795 p_balance_category => l_balance_category, -- Bug 3311781
3796 p_bc_leg_code => 'US',
3797 p_effective_date => g_eff_start_date);
3798
3799 hr_utility.set_location('pyusuidt',131);
3800 pay_db_pay_setup.create_defined_balance (
3801 p_balance_name => v_balance_name,
3802 p_balance_dimension => 'Person Run',
3803 p_business_group_name => v_bg_name,
3804 p_legislation_code => NULL);
3805
3806 pay_db_pay_setup.create_defined_balance (
3807 p_balance_name => v_balance_name,
3808 p_balance_dimension => 'Person within Government Reporting Entity Run',
3809 p_business_group_name => v_bg_name,
3810 p_legislation_code => NULL);
3811
3812 hr_utility.set_location('pyusuidt',131);
3813 pay_db_pay_setup.create_defined_balance (
3814 p_balance_name => v_balance_name,
3815 p_balance_dimension => 'Assignment-Level Current Run',
3816 p_business_group_name => v_bg_name,
3817 p_legislation_code => NULL);
3818
3819 pay_db_pay_setup.create_defined_balance (
3820 p_balance_name => v_balance_name,
3821 p_balance_dimension => 'Assignment within Government Reporting Entity Run',
3822 p_business_group_name => v_bg_name,
3823 p_legislation_code => NULL,
3824 p_save_run_bal => 'Y');
3825
3826
3827 If p_ele_processing_type = 'R' then /* Not required for NR Elements */
3828
3829 v_balance_name := substr(p_ele_name, 1, 55)||' Toward Bond Purchase';
3830 --v_bal_rpt_name := substr(p_ele_name, 1, 13)||' To Bond Purch';
3831 hr_utility.set_location('hr_user_init_dedn.ins_deduction_template',39);
3832 v_eepurch_bal_type_id := pay_db_pay_setup.create_balance_type(
3833 p_balance_name => v_balance_name,
3834 p_uom => 'Money',
3835 p_reporting_name => v_balance_name,
3836 p_business_group_name => v_bg_name,
3837 p_legislation_code => NULL,
3838 p_legislation_subgroup => NULL,
3839 p_balance_category => l_balance_category, -- Bug 3311781
3840 p_bc_leg_code => 'US',
3841 p_effective_date => g_eff_start_date);
3842
3843 hr_utility.set_location('hr_user_init_dedn.ins_deduction_template',41);
3844 pay_db_pay_setup.create_defined_balance (
3845 p_balance_name => v_balance_name,
3846 p_balance_dimension => 'Assignment Inception to Date',
3847 p_business_group_name => v_bg_name,
3848 p_legislation_code => NULL);
3849
3850 pay_db_pay_setup.create_defined_balance (
3851 p_balance_name => v_balance_name,
3852 p_balance_dimension => 'Assignment within Government Reporting Entity Inception to Date',
3853 p_business_group_name => v_bg_name,
3854 p_legislation_code => NULL);
3855
3856 hr_utility.set_location('hr_user_init_dedn.ins_deduction_template',43);
3857 v_balance_name := substr(p_ele_name, 1, 71)||' Accrued';
3858 --v_bal_rpt_name := substr(p_ele_name, 1, 21)||' Accrued';
3859 v_totowed_bal_type_id := pay_db_pay_setup.create_balance_type(
3860 p_balance_name => v_balance_name,
3861 p_uom => 'Money',
3862 p_reporting_name => v_balance_name,
3863 p_business_group_name => v_bg_name,
3864 p_legislation_code => NULL,
3865 p_legislation_subgroup => NULL,
3866 p_balance_category => l_balance_category, -- Bug 3311781
3867 p_bc_leg_code => 'US',
3868 p_effective_date => g_eff_start_date);
3869
3870 hr_utility.set_location('hr_user_init_dedn.ins_deduction_template',45);
3871 pay_db_pay_setup.create_defined_balance (
3872 p_balance_name => v_balance_name,
3873 p_balance_dimension => 'Assignment Inception to Date',
3874 p_business_group_name => v_bg_name,
3875 p_legislation_code => NULL);
3876
3877 pay_db_pay_setup.create_defined_balance (
3878 p_balance_name => v_balance_name,
3879 p_balance_dimension => 'Assignment within Government Reporting Entity Inception to Date',
3880 p_business_group_name => v_bg_name,
3881 p_legislation_code => NULL);
3882
3883 pay_db_pay_setup.create_defined_balance(
3884 p_balance_name => v_balance_name,
3885 p_balance_dimension => 'Assignment within Government Reporting Entity Run',
3886 p_business_group_name => v_bg_name,
3887 p_legislation_code => NULL,
3888 p_save_run_bal => 'Y');
3889 --
3890 -- Adding _ENTRY_ITD Balance Dimension For Bug# 6270794
3891 --
3892 pay_db_pay_setup.create_defined_balance (
3893 p_balance_name => v_balance_name,
3894 p_balance_dimension => 'US Element Entry Inception to Date',
3895 p_business_group_name => v_bg_name,
3896 p_legislation_code => NULL);
3897
3898 v_balance_name := substr(p_ele_name, 1, 71)||' Arrears';
3899 --v_bal_rpt_name := substr(p_ele_name, 1, 21)||' Arrears';
3900 hr_utility.set_location('hr_user_init_dedn.ins_deduction_template',47);
3901 v_arrears_bal_type_id := pay_db_pay_setup.create_balance_type(
3902 p_balance_name => v_balance_name,
3903 p_uom => 'Money',
3904 p_reporting_name => v_balance_name,
3905 p_business_group_name => v_bg_name,
3906 p_legislation_code => NULL,
3907 p_legislation_subgroup => NULL,
3908 p_balance_category => l_balance_category, -- Bug 3311781
3909 p_bc_leg_code => 'US',
3910 p_effective_date => g_eff_start_date);
3911
3912 hr_utility.set_location('hr_user_init_dedn.ins_deduction_template',49);
3913 pay_db_pay_setup.create_defined_balance (
3914 p_balance_name => v_balance_name,
3915 p_balance_dimension => 'Assignment Inception to Date',
3916 p_business_group_name => v_bg_name,
3917 p_legislation_code => NULL);
3918
3919 pay_db_pay_setup.create_defined_balance (
3920 p_balance_name => v_balance_name,
3921 p_balance_dimension => 'Assignment within Government Reporting Entity Inception to Date',
3922 p_business_group_name => v_bg_name,
3923 p_legislation_code => NULL);
3924
3925 -- WWbug333133 - create _GRE_ITD dimension for arrears
3926
3927 pay_db_pay_setup.create_defined_balance (
3928 p_balance_name => v_balance_name,
3929 p_balance_dimension => 'Government Reporting Entity Inception to Date',
3930 p_business_group_name => v_bg_name,
3931 p_legislation_code => NULL);
3932
3933 --
3934 pay_db_pay_setup.create_defined_balance(
3935 p_balance_name => v_balance_name,
3936 p_balance_dimension => 'Assignment within Government Reporting Entity Run',
3937 p_business_group_name => v_bg_name,
3938 p_legislation_code => NULL,
3939 p_save_run_bal => 'Y');
3940
3941
3942 end if; /* Not required for NR Elements */
3943
3944 -- Begin Create Eligible Comp Balance
3945 v_balance_name := SUBSTR(p_ele_name, 1, 66)||' Eligible Comp';
3946 --v_bal_rpt_name := SUBSTR(p_ele_name, 1, 16)||' Eligible Comp';
3947 hr_utility.set_location('pyusuidt',240);
3948 v_eligiblecomp_bal_type_id := pay_db_pay_setup.create_balance_type(
3949 p_balance_name => v_balance_name,
3950 p_uom => 'Money',
3951 p_reporting_name => v_balance_name,
3952 p_business_group_name => v_bg_name,
3953 p_legislation_code => NULL,
3954 p_legislation_subgroup => NULL,
3955 p_bc_leg_code => 'US',
3956 p_effective_date => g_eff_start_date);
3957
3958 if v_balance_name like '%Eligible%' then
3959
3960 open get_reg_earn_feeds(p_bg_id);
3961 loop
3962 FETCH get_reg_earn_feeds INTO l_reg_earn_classification_id,
3963 l_reg_earn_business_group_id, l_reg_earn_legislation_code,
3964 l_reg_earn_balance_type_id, l_reg_earn_input_value_id,
3965 l_reg_earn_scale, l_reg_earn_element_type_id;
3966 EXIT WHEN get_reg_earn_feeds%NOTFOUND;
3967
3968 hr_balances.ins_balance_feed(
3969 p_option => 'INS_MANUAL_FEED',
3970 p_input_value_id => l_reg_earn_input_value_id,
3971 p_element_type_id => l_reg_earn_element_type_id,
3972 p_primary_classification_id => l_reg_earn_classification_id,
3973 p_sub_classification_id => NULL,
3974 p_sub_classification_rule_id => NULL,
3975 p_balance_type_id => v_eligiblecomp_bal_type_id ,
3976 p_scale => l_reg_earn_scale,
3977 p_session_date => g_eff_start_date,
3978 p_business_group => p_bg_id,
3979 p_legislation_code => NULL,
3980 p_mode => 'USER');
3981
3982 end loop;
3983 close get_reg_earn_feeds;
3984
3985 end if;
3986
3987 hr_utility.set_location('pyusuidt',241);
3988 pay_db_pay_setup.create_defined_balance (
3989 p_balance_name => v_balance_name,
3990 p_balance_dimension => 'Person Run',
3991 p_business_group_name => v_bg_name,
3992 p_legislation_code => NULL);
3993
3994 pay_db_pay_setup.create_defined_balance (
3995 p_balance_name => v_balance_name,
3996 p_balance_dimension => 'Person within Government Reporting Entity Run',
3997 p_business_group_name => v_bg_name,
3998 p_legislation_code => NULL);
3999
4000 hr_utility.set_location('pyusuidt',243);
4001 pay_db_pay_setup.create_defined_balance (
4002 p_balance_name => v_balance_name,
4003 p_balance_dimension => 'Assignment-Level Current Run',
4004 p_business_group_name => v_bg_name,
4005 p_legislation_code => NULL);
4006
4007 pay_db_pay_setup.create_defined_balance (
4008 p_balance_name => v_balance_name,
4009 p_balance_dimension => 'Assignment within Government Reporting Entity Run',
4010 p_business_group_name => v_bg_name,
4011 p_legislation_code => NULL);
4012
4013
4014 pay_db_pay_setup.create_defined_balance (
4015 p_balance_name => v_balance_name,
4016 p_balance_dimension => 'Assignment within Government Reporting Entity Year to Date',
4017 p_business_group_name => v_bg_name,
4018 p_legislation_code => NULL);
4019
4020 -- End Eligible Comp Balance
4021
4022 /* Vol. Deductions are after-tax components. They will never have Overlimit
4023
4024 -- Begin Aftertax Component Balance
4025
4026 --IF p_ele_at_component = 'Y' THEN
4027 v_balance_name := SUBSTR(p_ele_name, 1, 67)||' Overlimit';
4028 --v_bal_rpt_name := SUBSTR(p_ele_name, 1, 17)||' Overlimit';
4029 hr_utility.set_location('pyusuidt',245);
4030 --v_notaken_bal_type_id := pay_db_pay_setup.create_balance_type(
4031 v_overlimit_bal_type_id := pay_db_pay_setup.create_balance_type(
4032 p_balance_name => v_balance_name,
4033 p_uom => 'Money',
4034 p_reporting_name => v_balance_name,
4035 p_business_group_name => v_bg_name,
4036 p_legislation_code => NULL,
4037 p_legislation_subgroup => NULL,
4038 p_balance_category => l_balance_category, -- Bug 3311781
4039 p_bc_leg_code => 'US',
4040 p_effective_date => g_eff_start_date);
4041
4042 hr_utility.set_location('pyusuidt',246);
4043 pay_db_pay_setup.create_defined_balance (
4044 p_balance_name => v_balance_name,
4045 p_balance_dimension => 'Person Run',
4046 p_business_group_name => v_bg_name,
4047 p_legislation_code => NULL);
4048
4049
4050 hr_utility.set_location('pyusuidt',247);
4051 pay_db_pay_setup.create_defined_balance (
4052 p_balance_name => v_balance_name,
4053 p_balance_dimension => 'Assignment-Level Current Run',
4054 p_business_group_name => v_bg_name,
4055 p_legislation_code => NULL);
4056
4057 pay_db_pay_setup.create_defined_balance (
4058 p_balance_name => v_balance_name,
4059 p_balance_dimension => 'Assignment within Government Reporting Entity Run',
4060 p_business_group_name => v_bg_name,
4061 p_legislation_code => NULL);
4062
4063
4064 */
4065 --
4066 ----------------------- Create Element Type -----------------------------
4067 --
4068 -- Determine deduction skip rule; or we may use the "single" formulae method.
4069 -- 27Sep93: At this moment, a single skip rule will handle all deduction
4070 -- templates.
4071 --
4072 -- Need to determine and get skip rule formula id and pass it
4073 -- create_element.
4074 --
4075 hr_utility.set_location('hr_user_init_dedn.ins_dedn_ele_type',10);
4076 --
4077 IF UPPER(p_ele_start_rule) = 'CHAINED' THEN
4078 hr_utility.set_location('hr_user_init_dedn.ins_dedn_ele_type',15);
4079 SELECT FF.formula_id
4080 INTO v_skip_formula_id
4081 FROM ff_formulas_f FF
4082 WHERE FF.formula_name = 'CHAINED_SKIP_FORMULA'
4083 AND FF.business_group_id IS NULL
4084 -- added legislation_code asasthan
4085 AND FF.legislation_code = 'US'
4086 AND p_ele_eff_start_date >= FF.effective_start_date
4087 AND p_ele_eff_start_date <= FF.effective_end_date
4088 AND FF.formula_id >= 0; --Bug 3349594
4089 --
4090 ELSIF UPPER(p_ele_start_rule) = 'ET' THEN
4091 hr_utility.set_location('hr_user_init_dedn.ins_dedn_ele_type',20);
4092 SELECT FF.formula_id
4093 INTO v_skip_formula_id
4094 FROM ff_formulas_f FF
4095 WHERE FF.formula_name = 'THRESHOLD_SKIP_FORMULA'
4096 AND FF.business_group_id IS NULL
4097 -- added legislation code asasthan
4098 AND FF.legislation_code = 'US'
4099 AND p_ele_eff_start_date >= FF.effective_start_date
4100 AND p_ele_eff_start_date <= FF.effective_end_date
4101 AND FF.formula_id >= 0; --Bug 3349594
4102 -- AND FF.business_group_id IS NULL
4103 --
4104 ELSE -- Just check skip rule and separate check flag.
4105 hr_utility.set_location('hr_user_init_dedn.ins_dedn_ele_type',25);
4106 SELECT FF.formula_id
4107 INTO v_skip_formula_id
4108 FROM ff_formulas_f FF
4109 WHERE FF.formula_name = 'FREQ_RULE_SKIP_FORMULA'
4110 AND FF.legislation_code = 'US'
4111 AND FF.business_group_id IS NULL
4112 AND p_ele_eff_start_date >= FF.effective_start_date
4113 AND p_ele_eff_start_date <= FF.effective_end_date
4114 AND FF.formula_id >= 0; --Bug 3349594
4115 --
4116 END IF;
4117 --
4118 -- Find what ele info category will be for SCL.
4119 --
4120 IF UPPER(p_ele_classification) = 'VOLUNTARY DEDUCTIONS' THEN
4121 g_ele_info_cat := 'US_VOLUNTARY DEDUCTIONS';
4122 END IF;
4123 --
4124 hr_utility.set_location('hr_user_init_dedn.ins_deduction_template',51);
4125 v_ele_type_id := ins_dedn_ele_type ( p_ele_name,
4126 p_ele_reporting_name,
4127 p_ele_description,
4128 p_ele_classification,
4129 p_ele_category,
4130 p_ele_start_rule,
4131 p_ele_processing_type,
4132 p_ele_priority,
4133 p_ele_standard_link,
4134 v_skip_formula_id,
4135 'N',
4136 g_eff_start_date,
4137 g_eff_end_date,
4138 v_bg_name);
4139 --
4140 -- Need to find PRIMARY_CLASSIFICATION_ID of element type.
4141 -- For future calls to various API.
4142 --
4143 hr_utility.set_location('hr_user_init_dedn.ins_deduction_template',53);
4144
4145 select distinct(classification_id)
4146 into v_primary_class_id
4147 from pay_element_types_f
4148 where element_type_id = v_ele_type_id;
4149 --
4150 -- Need to update termination rule.(bug 2276457)
4151 --
4152 UPDATE pay_element_types_f
4153 SET post_termination_rule = p_termination_rule
4154 WHERE element_type_id = v_ele_type_id;
4155 --
4156 hr_utility.set_location('pyusuiet',130);
4157 SELECT default_low_priority,
4158 default_high_priority
4159 INTO v_class_lo_priority,
4160 v_class_hi_priority
4161 FROM pay_element_classifications
4162 WHERE classification_id = v_primary_class_id
4163 AND nvl(business_group_id, p_bg_id) = p_bg_id;
4164
4165
4166 If p_ele_processing_type = 'R' then /* Not required for NR Elements */
4167 --
4168 --
4169 -- Create "special inputs" element
4170 --
4171 hr_utility.set_location('hr_user_init_dedn.ins_deduction_template',51);
4172 v_inputs_ele_name := SUBSTR(p_ele_name, 1, 61)||' Special Inputs';
4173 v_ele_repname := SUBSTR(p_ele_name, 1, 27)||' SI';
4174 v_inputs_ele_type_id := ins_dedn_ele_type (
4175 v_inputs_ele_name,
4176 v_ele_repname,
4177 p_ele_description,
4178 p_ele_classification,
4179 p_ele_category,
4180 'OE',
4181 'N',
4182 v_class_lo_priority,
4183 'N',
4184 NULL,
4185 'N',
4186 g_eff_start_date,
4187 g_eff_end_date,
4188 v_bg_name);
4189
4190 --
4191 -- Need to update termination rule.(bug 2276457)
4192 --
4193 UPDATE pay_element_types_f
4194 SET post_termination_rule = p_termination_rule
4195 WHERE element_type_id = v_inputs_ele_type_id;
4196
4197 End if; /* Not required for NR Elements */
4198
4199 --
4200 --
4201 -- Create "shadow" element
4202 --
4203 hr_utility.set_location('hr_user_init_dedn.ins_deduction_template',51);
4204 v_shadow_ele_name := SUBSTR(p_ele_name, 1, 61)||' Special Features';
4205 v_ele_repname := SUBSTR(p_ele_name, 1, 27)||' SF';
4206 v_shadow_ele_type_id := ins_dedn_ele_type (
4207 v_shadow_ele_name,
4208 v_ele_repname,
4209 p_ele_description,
4210 p_ele_classification,
4211 p_ele_category,
4212 'OE',
4213 'N',
4214 v_class_hi_priority,
4215 'N',
4216 NULL,
4217 'Y',
4218 g_eff_start_date,
4219 g_eff_end_date,
4220 v_bg_name);
4221
4222 --
4223 -- Need to update termination rule.(bug 2276457)
4224 --
4225 UPDATE pay_element_types_f
4226 SET post_termination_rule = p_termination_rule
4227 WHERE element_type_id = v_shadow_ele_type_id;
4228
4229 --
4230 -- Need to create employer charge element for Benefits Table deductions or
4231 -- deductions where there is an employer match component (e.g. 401k, 403b)
4232 --
4233 IF p_ele_amount_rule = 'BT' or p_ele_er_match = 'Y' THEN
4234 hr_utility.set_location('hr_user_init_dedn.ins_deduction_template',55);
4235 v_er_charge_ele_name := SUBSTR(p_ele_name, 1, 77)||' ER';
4236 v_ele_repname := SUBSTR(p_ele_name, 1, 27)||' ER';
4237 v_er_charge_eletype_id := ins_dedn_ele_type (
4238 v_er_charge_ele_name,
4239 v_ele_repname,
4240 'Employer portion of benefit.',
4241 'Employer Liabilities',
4242 'Benefits',
4243 NULL,
4244 'N',
4245 '6500',
4246 'N',
4247 NULL,
4248 'N',
4249 g_eff_start_date,
4250 g_eff_end_date,
4251 v_bg_name);
4252 --
4253 hr_utility.set_location('hr_user_init_dedn.ins_deduction_template',57);
4254 --
4255 -- Create Pay Value for this element.
4256 --
4257 -- NO, "Employer Liabilities" has non-payments flag = 'N'!
4258 -- So a payvalue is created by pay_db_pay_setup.create_element
4259 -- Need to do this for Non-Payments where non_payments_flag = 'Y',
4260 -- (done for Earnings, but was snagging on "dbi name already used"
4261 -- which i think is a bug.
4262
4263 --
4264 -- Create "Primary" balance for this ER Liab and "associate" appropriately.
4265 --
4266 hr_utility.set_location('hr_user_init_dedn.ins_deduction_template',58);
4267 v_balance_name := v_er_charge_ele_name;
4268 --v_bal_rpt_name := v_ele_repname;
4269 v_er_charge_baltype_id := pay_db_pay_setup.create_balance_type(
4270 p_balance_name => v_balance_name,
4271 p_uom => 'Money',
4272 p_reporting_name => v_balance_name,
4273 p_business_group_name => v_bg_name,
4274 p_legislation_code => NULL,
4275 p_legislation_subgroup => NULL,
4276 p_balance_category => l_balance_category, -- Bug 3311781
4277 p_bc_leg_code => 'US',
4278 p_effective_date => g_eff_start_date);
4279
4280 do_defined_balances( p_bal_name => v_balance_name,
4281 p_bg_name => v_bg_name);
4282
4283 --
4284 -- Primary balance feeds
4285 --
4286 hr_utility.set_location('hr_user_init_dedn.ins_deduction_template',70);
4287 v_payval_name := hr_input_values.get_pay_value_name(g_template_leg_code);
4288
4289 -- We need inpval_id of pay value for this element:
4290 hr_utility.set_location('hr_user_init_dedn.ins_deduction_template',80);
4291 SELECT IV.input_value_id
4292 INTO v_payval_id
4293 FROM pay_input_values_f IV
4294 WHERE IV.element_type_id = v_er_charge_eletype_id
4295 AND IV.name = v_payval_name;
4296 --
4297 -- Now, insert feed.
4298 -- Note, there is a packaged function "chk_ins_balance_feed" in pybalnce.pkb.
4299 -- Since this is definitely a new balance feed for a new element and balance,
4300 -- there is no chance for duplicating an existing feed.
4301 --
4302 hr_utility.set_location('hr_user_init_dedn.ins_deduction_template',90);
4303 hr_balances.ins_balance_feed(
4304 p_option => 'INS_MANUAL_FEED',
4305 p_input_value_id => v_payval_id,
4306 p_element_type_id => NULL,
4307 p_primary_classification_id => NULL,
4308 p_sub_classification_id => NULL,
4309 p_sub_classification_rule_id => NULL,
4310 p_balance_type_id => v_er_charge_baltype_id,
4311 p_scale => '1',
4312 p_session_date => g_eff_start_date,
4313 p_business_group => p_bg_id,
4314 p_legislation_code => NULL,
4315 p_mode => 'USER');
4316
4317
4318
4319 ---Inserted by lwthomps for bug 345102. Associate Primary balance with
4320 ---Employer Liabiliity type benefits.
4321
4322 UPDATE pay_element_types_f
4323 SET ELEMENT_INFORMATION10 = v_er_charge_baltype_id
4324 WHERE element_type_id = v_er_charge_eletype_id;
4325
4326 -- Bug Fix 5763867
4327 UPDATE pay_element_types_f
4328 SET element_information_category = 'US_EMPLOYER LIABILITIES',
4329 element_information1 = 'B'
4330 WHERE element_type_id = v_er_charge_eletype_id
4331 AND business_group_id + 0 = p_bg_id;
4332
4333 --
4334 END IF; -- Benefit
4335 --
4336 IF p_ele_ee_bond = 'Y' THEN
4337 hr_utility.set_location('hr_user_init_dedn.ins_deduction_template',63);
4338 v_eerefund_ele_name := SUBSTR(p_ele_name, 1, 72)||' Refund';
4339 v_ele_repname := SUBSTR(p_ele_name, 1, 22)||' Refund';
4340 v_eerefund_eletype_id := ins_dedn_ele_type (
4341 v_eerefund_ele_name,
4342 v_ele_repname,
4343 'EE Bond Refund element.',
4344 'Non-Payroll Payments',
4345 'Expense Reimbursement',
4346 NULL,
4347 'N',
4348 p_ele_priority + 1,
4349 'N',
4350 NULL,
4351 'N',
4352 g_eff_start_date,
4353 g_eff_end_date,
4354 v_bg_name);
4355 --
4356 -- Create Bond Refund Primary associated balance...and feeds...
4357 --
4358 --
4359 -- Create "Primary" balance for this EE Bond Refund and
4360 -- "associate" appropriately.
4361 --
4362 hr_utility.set_location('hr_user_init_dedn.ins_deduction_template',58);
4363 v_balance_name := v_eerefund_ele_name;
4364 --v_bal_rpt_name := v_ele_repname;
4365 v_eerefund_baltype_id := pay_db_pay_setup.create_balance_type(
4366 p_balance_name => v_balance_name,
4367 p_uom => 'Money',
4368 p_reporting_name => v_balance_name,
4369 p_business_group_name => v_bg_name,
4370 p_legislation_code => NULL,
4371 p_legislation_subgroup => NULL,
4372 p_balance_category => l_balance_category, -- Bug 3311781
4373 p_bc_leg_code => 'US',
4374 p_effective_date => g_eff_start_date);
4375
4376 -- Update ele type DDF for primary associated balance.
4377
4378 UPDATE pay_element_types_f
4379 SET ELEMENT_INFORMATION10 = v_eerefund_baltype_id
4380 WHERE element_type_id = v_eerefund_eletype_id;
4381
4382 do_defined_balances( p_bal_name => v_balance_name,
4383 p_bg_name => v_bg_name,
4384 p_no_payments => TRUE);
4385
4386 --
4387 -- Primary balance feeds
4388 --
4389 hr_utility.set_location('hr_user_init_dedn.ins_deduction_template',70);
4390 v_payval_name := hr_input_values.get_pay_value_name(g_template_leg_code);
4391
4392 -- We need inpval_id of pay value for this element:
4393 hr_utility.set_location('hr_user_init_dedn.ins_deduction_template',80);
4394 SELECT IV.input_value_id
4395 INTO v_payval_id
4396 FROM pay_input_values_f IV
4397 WHERE IV.element_type_id = v_eerefund_eletype_id
4398 AND IV.name = v_payval_name;
4399 --
4400 -- Now, insert feed.
4401 -- Note, there is a packaged function "chk_ins_balance_feed" in pybalnce.pkb.
4402 -- Since this is definitely a new balance feed for a new element and balance,
4403 -- there is no chance for duplicating an existing feed.
4404 --
4405 hr_utility.set_location('hr_user_init_dedn.ins_deduction_template',90);
4406 hr_balances.ins_balance_feed(
4407 p_option => 'INS_MANUAL_FEED',
4408 p_input_value_id => v_payval_id,
4409 p_element_type_id => NULL,
4410 p_primary_classification_id => NULL,
4411 p_sub_classification_id => NULL,
4412 p_sub_classification_rule_id => NULL,
4413 p_balance_type_id => v_eerefund_baltype_id,
4414 p_scale => '1',
4415 p_session_date => g_eff_start_date,
4416 p_business_group => p_bg_id,
4417 p_legislation_code => NULL,
4418 p_mode => 'USER');
4419
4420 -- Done creating Bond Refund element
4421 --
4422 END IF; -- EE Bond
4423 --
4424 -------------------------- Insert Formula Processing records -------------
4425 --
4426 hr_utility.set_location('hr_user_init_dedn.ins_deduction_template',68);
4427 ins_dedn_formula_processing ( v_ele_type_id,
4428 p_ele_name,
4429 v_shadow_ele_type_id,
4430 v_shadow_ele_name,
4431 v_inputs_ele_type_id,
4432 v_inputs_ele_name,
4433 v_primary_class_id,
4434 p_ele_classification,
4435 p_ele_category,
4436 p_ele_processing_type,
4437 p_ele_amount_rule,
4438 p_ele_proc_runtype,
4439 p_ele_start_rule,
4440 p_ele_stop_rule,
4441 p_ele_ee_bond,
4442 p_ele_paytab_name,
4443 p_ele_paytab_col,
4444 p_ele_paytab_row_type,
4445 p_ele_arrearage,
4446 p_ele_partial_dedn,
4447 v_er_charge_eletype_id,
4448 v_er_charge_payval_id,
4449 v_eerefund_eletype_id,
4450 p_bg_id,
4451 p_mix_flag,
4452 g_eff_start_date,
4453 g_eff_end_date,
4454 v_bg_name);
4455 --
4456 -------------------------Insert Input Values --------------------------
4457 --
4458 -- Make insertion of all basic earnings input vals (ie. req'd for all
4459 -- earnings elements, not based on calc rule; instead on Class).
4460 hr_utility.set_location('hr_user_init_dedn.ins_deduction_template',69);
4461 ins_dedn_input_vals ( v_ele_type_id,
4462 p_ele_name,
4463 v_shadow_ele_type_id,
4464 v_shadow_ele_name,
4465 v_inputs_ele_type_id,
4466 v_inputs_ele_name,
4467 g_eff_start_date,
4468 g_eff_end_date,
4469 v_primary_class_id,
4470 p_ele_classification,
4471 p_ele_category,
4472 p_ele_processing_type,
4473 p_bg_id,
4474 v_bg_name,
4475 p_ele_amount_rule);
4476 --
4477 ------------------------ Insert Balance Feeds -------------------------
4478 --
4479 -- First, call the "category feeder" API which creates manual pay value feeds
4480 -- to pre-existing balances depending on the element classn/category.
4481 -- (Added by ALLEE - 5-MAY-1995) Pass 'g_ele_eff_start_date' to
4482 -- create_category_feeds in order for datetrack to work.
4483 pay_us_ctgy_feeds_pkg.create_category_feeds(
4484 p_element_type_id => v_ele_type_id,
4485 p_date => g_eff_start_date);
4486 --
4487 -- These are manual feeds for "primary" balance for earnings element.
4488 -- For manual feeds, only baltype id, inpval id, and scale are req'd.
4489 --
4490 -- NOTE: For primary balances, the feeds should not be altered - only one
4491 -- value should ever feed the primary balance (ie. the element's payvalue).
4492 -- In order to query the balance on Define Balances, the LEG Subgroup should
4493 -- be NULL instead of 'TEMPLATE'.
4494 --
4495 -- We also need to feed the Section 125 and 401k balances
4496 -- when the deduction Classification/Category = PreTax/125 or Deferred
4497 -- as appropriate.
4498 --
4499 -- And we need to update element type DDF with associated balances (G1241).
4500 --
4501 hr_utility.set_location('hr_user_init_dedn.ins_deduction_template',70);
4502 v_payval_name := hr_input_values.get_pay_value_name(g_template_leg_code);
4503
4504 -- We need inpval_id of pay value for this element:
4505 hr_utility.set_location('hr_user_init_dedn.ins_deduction_template',80);
4506 SELECT IV.input_value_id
4507 INTO v_payval_id
4508 FROM pay_input_values_f IV
4509 WHERE IV.element_type_id = v_ele_type_id
4510 AND IV.name = v_payval_name;
4511 --
4512 -- Now, insert feed.
4513 -- Note, there is a packaged function "chk_ins_balance_feed" in pybalnce.pkb.
4514 -- Since this is definitely a new balance feed for a new element and balance,
4515 -- there is no chance for duplicating an existing feed.
4516 --
4517 hr_utility.set_location('hr_user_init_dedn.ins_deduction_template',90);
4518 hr_balances.ins_balance_feed(
4519 p_option => 'INS_MANUAL_FEED',
4520 p_input_value_id => v_payval_id,
4521 p_element_type_id => NULL,
4522 p_primary_classification_id => NULL,
4523 p_sub_classification_id => NULL,
4524 p_sub_classification_rule_id => NULL,
4525 p_balance_type_id => v_bal_type_id,
4526 p_scale => '1',
4527 p_session_date => g_eff_start_date,
4528 p_business_group => p_bg_id,
4529 p_legislation_code => NULL,
4530 p_mode => 'USER');
4531 --
4532 -- Addl/Repl Amount balance feeds from Special Features ele:
4533 --
4534
4535 if p_ele_processing_type = 'R' then /* Not required for NR Elements */
4536
4537 hr_utility.set_location('pyusuiet',147);
4538 hr_balances.ins_balance_feed(
4539 p_option => 'INS_MANUAL_FEED',
4540 p_input_value_id => g_addl_inpval_id,
4541 p_element_type_id => NULL,
4542 p_primary_classification_id => NULL,
4543 p_sub_classification_id => NULL,
4544 p_sub_classification_rule_id => NULL,
4545 p_balance_type_id => v_addl_amt_bal_type_id,
4546 p_scale => '1',
4547 p_session_date => g_eff_start_date,
4548 p_business_group => p_bg_id,
4549 p_legislation_code => NULL,
4550 p_mode => 'USER');
4551 --
4552 --
4553 hr_utility.set_location('pyusuiet',146);
4554 --
4555 -- Now, insert feed.
4556 -- Note, there is a packaged function "chk_ins_balance_feed" in pybalnce.pkb.
4557 -- Since this is definitely a new balance feed for a new element and balance,
4558 -- there is no chance for duplicating an existing feed.
4559 hr_utility.set_location('pyusuiet',147);
4560 hr_balances.ins_balance_feed(
4561 p_option => 'INS_MANUAL_FEED',
4562 p_input_value_id => g_repl_inpval_id,
4563 p_element_type_id => NULL,
4564 p_primary_classification_id => NULL,
4565 p_sub_classification_id => NULL,
4566 p_sub_classification_rule_id => NULL,
4567 p_balance_type_id => v_repl_amt_bal_type_id,
4568 p_scale => '1',
4569 p_session_date => g_eff_start_date,
4570 p_business_group => p_bg_id,
4571 p_legislation_code => NULL,
4572 p_mode => 'USER');
4573 --
4574 -- Addl/Repl Amount balance feeds from Special Inputs ele:
4575 --
4576 hr_utility.set_location('pyusuiet',147);
4577 hr_balances.ins_balance_feed(
4578 p_option => 'INS_MANUAL_FEED',
4579 p_input_value_id => gi_addl_inpval_id,
4580 p_element_type_id => NULL,
4581 p_primary_classification_id => NULL,
4582 p_sub_classification_id => NULL,
4583 p_sub_classification_rule_id => NULL,
4584 p_balance_type_id => v_addl_amt_bal_type_id,
4585 p_scale => '1',
4586 p_session_date => g_eff_start_date,
4587 p_business_group => p_bg_id,
4588 p_legislation_code => NULL,
4589 p_mode => 'USER');
4590 --
4591 --
4592 hr_utility.set_location('pyusuiet',146);
4593 --
4594 -- Now, insert feed.
4595 -- Note, there is a packaged function "chk_ins_balance_feed" in pybalnce.pkb.
4596 -- Since this is definitely a new balance feed for a new element and balance,
4597 -- there is no chance for duplicating an existing feed.
4598 hr_utility.set_location('pyusuiet',147);
4599 hr_balances.ins_balance_feed(
4600 p_option => 'INS_MANUAL_FEED',
4601 p_input_value_id => gi_repl_inpval_id,
4602 p_element_type_id => NULL,
4603 p_primary_classification_id => NULL,
4604 p_sub_classification_id => NULL,
4605 p_sub_classification_rule_id => NULL,
4606 p_balance_type_id => v_repl_amt_bal_type_id,
4607 p_scale => '1',
4608 p_session_date => g_eff_start_date,
4609 p_business_group => p_bg_id,
4610 p_legislation_code => NULL,
4611 p_mode => 'USER');
4612
4613 End if; /* Not required for NR Elements */
4614
4615 --
4616 -- Arrearage bal feeds
4617 IF p_ele_arrearage = 'Y' THEN
4618 hr_balances.ins_balance_feed(
4619 p_option => 'INS_MANUAL_FEED',
4620 p_input_value_id => g_arrears_contr_inpval_id,
4621 p_element_type_id => NULL,
4622 p_primary_classification_id => NULL,
4623 p_sub_classification_id => NULL,
4624 p_sub_classification_rule_id => NULL,
4625 p_balance_type_id => v_arrears_bal_type_id,
4626 p_scale => '1',
4627 p_session_date => g_eff_start_date,
4628 p_business_group => p_bg_id,
4629 p_legislation_code => NULL,
4630 p_mode => 'USER');
4631
4632 hr_balances.ins_balance_feed(
4633 p_option => 'INS_MANUAL_FEED',
4634 p_input_value_id => g_adj_arrears_inpval_id,
4635 p_element_type_id => NULL,
4636 p_primary_classification_id => NULL,
4637 p_sub_classification_id => NULL,
4638 p_sub_classification_rule_id => NULL,
4639 p_balance_type_id => v_arrears_bal_type_id,
4640 p_scale => '1',
4641 p_session_date => g_eff_start_date,
4642 p_business_group => p_bg_id,
4643 p_legislation_code => NULL,
4644 p_mode => 'USER');
4645
4646 END IF; -- Arrearage balfeeds
4647 --
4648 -- Not Taken bal feed
4649 --
4650 hr_balances.ins_balance_feed(
4651 p_option => 'INS_MANUAL_FEED',
4652 p_input_value_id => g_notaken_inpval_id,
4653 p_element_type_id => NULL,
4654 p_primary_classification_id => NULL,
4655 p_sub_classification_id => NULL,
4656 p_sub_classification_rule_id => NULL,
4657 p_balance_type_id => v_notaken_bal_type_id,
4658 p_scale => '1',
4659 p_session_date => g_eff_start_date,
4660 p_business_group => p_bg_id,
4661 p_legislation_code => NULL,
4662 p_mode => 'USER');
4663 --
4664 -- EE Bond bal feeds
4665 IF p_ele_ee_bond = 'Y' THEN
4666 hr_balances.ins_balance_feed(
4667 p_option => 'INS_MANUAL_FEED',
4668 p_input_value_id => g_topurch_inpval_id,
4669 p_element_type_id => NULL,
4670 p_primary_classification_id => NULL,
4671 p_sub_classification_id => NULL,
4672 p_sub_classification_rule_id => NULL,
4673 p_balance_type_id => v_eepurch_bal_type_id,
4674 p_scale => '1',
4675 p_session_date => g_eff_start_date,
4676 p_business_group => p_bg_id,
4677 p_legislation_code => NULL,
4678 p_mode => 'USER');
4679 --
4680 END IF; -- EE Bond bal feeds
4681 --
4682 -- Total Reached bal feeds (stop rule)
4683 IF UPPER(p_ele_stop_rule) = 'TOTAL REACHED' THEN
4684 hr_balances.ins_balance_feed(
4685 p_option => 'INS_MANUAL_FEED',
4686 p_input_value_id => g_to_tot_inpval_id,
4687 p_element_type_id => NULL,
4688 p_primary_classification_id => NULL,
4689 p_sub_classification_id => NULL,
4690 p_sub_classification_rule_id => NULL,
4691 p_balance_type_id => v_totowed_bal_type_id,
4692 p_scale => '1',
4693 p_session_date => g_eff_start_date,
4694 p_business_group => p_bg_id,
4695 p_legislation_code => NULL,
4696 p_mode => 'USER');
4697 --
4698 END IF; -- Stop rule bal feeds.
4699 --
4700 --
4701 -- Other associated balances:
4702 --
4703
4704 if p_ele_processing_type = 'R' then /* Not required for NR Elements */
4705 UPDATE pay_element_types_f
4706 SET element_information10 = v_bal_type_id,
4707 element_information11 = v_totowed_bal_type_id,
4708 element_information12 = v_arrears_bal_type_id,
4709 element_information13 = v_notaken_bal_type_id,
4710 element_information14 = v_eepurch_bal_type_id,
4711 element_information16 = v_addl_amt_bal_type_id,
4712 element_information17 = v_repl_amt_bal_type_id,
4713 element_information18 = v_inputs_ele_type_id,
4714 element_information19 = v_shadow_ele_type_id
4715 WHERE element_type_id = v_ele_type_id
4716 AND business_group_id + 0 = p_bg_id;
4717
4718 else /* Not required for NR Elements */
4719
4720 UPDATE pay_element_types_f
4721 SET element_information10 = v_bal_type_id,
4722 element_information13 = v_notaken_bal_type_id,
4723 element_information19 = v_shadow_ele_type_id
4724 WHERE element_type_id = v_ele_type_id
4725 AND business_group_id + 0 = p_bg_id;
4726
4727 end if; /* Not required for NR Elements */
4728 --
4729 ----------------------------- Conclude Main -----------------------------
4730 --
4731 RETURN v_ele_type_id;
4732
4733 END ins_deduction_template;
4734 --
4735 -------------------------------- Locking procedures ----------------------
4736 --
4737 PROCEDURE lock_template_rows (
4738 p_ele_type_id in number,
4739 p_ele_eff_start_date in date default NULL,
4740 p_ele_eff_end_date in date default NULL,
4741 p_ele_name in varchar2,
4742 p_ele_reporting_name in varchar2,
4743 p_ele_description in varchar2 default NULL,
4744 p_ele_classification in varchar2,
4745 p_ele_category in varchar2 default NULL,
4746 p_ele_processing_type in varchar2,
4747 p_ele_priority in number default NULL,
4748 p_ele_standard_link in varchar2 default 'N') IS
4749
4750 CURSOR chk_for_lock IS
4751 SELECT *
4752 FROM pay_all_deduction_types_v
4753 WHERE element_type_id = p_ele_type_id;
4754 -- FOR UPDATE OF element_type_id NOWAIT;
4755
4756 recinfo chk_for_lock%ROWTYPE;
4757
4758 BEGIN
4759 hr_utility.set_location('hr_user_init_dedn.lock_template_rows',10);
4760 OPEN chk_for_lock;
4761 FETCH chk_for_lock INTO recinfo;
4762 CLOSE chk_for_lock;
4763
4764 -- Note: Not checking eff dates.
4765
4766 hr_utility.set_location('hr_user_init_dedn.lock_template_rows',20);
4767 IF ( ( (recinfo.element_type_id = p_ele_type_id)
4768 OR (recinfo.element_type_id IS NULL AND p_ele_type_id IS NULL))
4769 -- AND ( (recinfo.effective_start_date = fnd_date.canonical_to_date(p_ele_eff_start_date))
4770 -- OR (recinfo.effective_start_date IS NULL
4771 -- AND p_ele_eff_start_date IS NULL))
4772 -- AND ( (recinfo.effective_end_date = fnd_date.canonical_to_date(p_ele_eff_end_date))
4773 -- OR (recinfo.effective_end_date IS NULL
4774 -- AND p_ele_eff_end_date IS NULL))
4775 AND ( (recinfo.element_name = p_ele_name)
4776 OR (recinfo.element_name IS NULL AND p_ele_name IS NULL))
4777 AND ( (recinfo.reporting_name = p_ele_reporting_name)
4778 OR (recinfo.reporting_name IS NULL AND p_ele_reporting_name IS NULL))
4779 AND ( (recinfo.description = p_ele_description)
4780 OR (recinfo.description IS NULL AND p_ele_description IS NULL))
4781 AND ( (recinfo.classification_name = p_ele_classification)
4782 OR (recinfo.classification_name IS NULL
4783 AND p_ele_classification IS NULL))
4784 AND ( (recinfo.category = p_ele_category)
4785 OR (recinfo.category IS NULL AND p_ele_category IS NULL))
4786 AND ( (recinfo.processing_type = p_ele_processing_type)
4787 OR (recinfo.processing_type IS NULL AND p_ele_processing_type IS NULL))
4788 AND ( (recinfo.processing_priority = p_ele_priority)
4789 OR (recinfo.processing_priority IS NULL AND p_ele_priority IS NULL))
4790 AND ( (recinfo.standard_link_flag = p_ele_standard_link)
4791 OR (recinfo.standard_link_flag IS NULL AND p_ele_standard_link IS NULL)))
4792 THEN
4793 hr_utility.set_location('hr_user_init_dedn.lock_template_rows',30);
4794 RETURN;
4795 ELSE
4796 hr_utility.set_location('hr_user_init_dedn.lock_template_rows',40);
4797 hr_utility.set_message(801,'PAY_xxxx_COULD_NOT_OBTAIN_LOCK');
4798 hr_utility.raise_error;
4799
4800 -- FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED_BY_ANOTHER_USER');
4801 -- APP_EXCEPTION.RAISE_EXCEPTION;
4802
4803 END IF;
4804
4805 END lock_template_rows;
4806 --
4807 --
4808 ------------------------- Deletion procedures -----------------------------
4809 --
4810 -- element_information10 = primary bal
4811 -- element_information11 = accrued bal
4812 -- element_information12 = arrears bal
4813 -- element_information13 = not taken bal
4814 -- element_information15 = able amount bal
4815 -- element_information16 = addl amount bal
4816 -- element_information17 = repl amount bal
4817 -- element_information18 = Special Inputs
4818 -- element_information19 = Special Features
4819 -- element_information20 = Withholding ele
4820 --
4821 -- Configuration deletion follows this algorithm:
4822 -- 0. Delete frequency rules for wage attachment.
4823 -- 1. Delete all associated balances.
4824 -- 2. For all associated element types of configured wage attachment...
4825 -- 3. Delete all formula result rules.
4826 -- 4. Delete all status processing rules.
4827 -- 5. Delete all formulae.
4828 -- 6. Delete all input values.
4829 -- 7. Delete element types.
4830 /* Bug 787491: All select statements are using business_group_id in where
4831 condition */
4832 PROCEDURE do_deletions (p_business_group_id in number,
4833 p_ele_type_id in number,
4834 p_ele_name in varchar2,
4835 p_ele_priority in number,
4836 p_ele_amount_rule in varchar2,
4837 p_ele_ee_bond in varchar2,
4838 p_ele_arrearage in varchar2,
4839 p_ele_stop_rule in varchar2,
4840 p_ele_info_10 in varchar2 default null,
4841 p_ele_info_11 in varchar2 default null,
4842 p_ele_info_12 in varchar2 default null,
4843 p_ele_info_13 in varchar2 default null,
4844 p_ele_info_14 in varchar2 default null,
4845 p_ele_info_15 in varchar2 default null,
4846 p_ele_info_16 in varchar2 default null,
4847 p_ele_info_17 in varchar2 default null,
4848 p_ele_info_18 in varchar2 default null,
4849 p_ele_info_19 in varchar2 default null,
4850 p_ele_info_20 in varchar2 default null,
4851 p_del_sess_date in date,
4852 p_del_val_start_date in date,
4853 p_del_val_end_date in date) IS
4854 -- local constants
4855 c_end_of_time CONSTANT DATE := TO_DATE('31/12/4712','DD/MM/YYYY');
4856
4857 -- local vars
4858
4859 TYPE text_table IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
4860 TYPE num_table IS TABLE OF NUMBER(9) INDEX BY BINARY_INTEGER;
4861
4862 assoc_eles num_table;
4863 assoc_bals num_table;
4864
4865 i number;
4866 j number;
4867 l_num_assoc_bals number;
4868 l_num_assoc_eles number;
4869
4870 v_del_mode VARCHAR2(80) := 'ZAP'; -- Completely remove template.
4871 v_startup_mode VARCHAR2(80) := 'USER';
4872 v_del_sess_date DATE := NULL;
4873 v_del_val_start DATE := NULL;
4874 v_del_val_end DATE := NULL;
4875 v_bal_type_id NUMBER(9);
4876 v_eletype_id NUMBER(9);
4877 v_shadow_eletype_id NUMBER(9);
4878 v_shadow_ele_priority NUMBER(9);
4879 v_inputs_eletype_id NUMBER(9);
4880 v_inputs_ele_priority NUMBER(9);
4881 v_ff_id NUMBER(9);
4882 v_ff_count NUMBER(3);
4883 v_fname_suffix VARCHAR2(20);
4884 v_eletype_count NUMBER(3);
4885 v_baltype_count NUMBER(3);
4886 v_addl_bal_type_id NUMBER(9);
4887 v_addl_bal_name VARCHAR2(80);
4888 v_repl_bal_type_id NUMBER(9);
4889 v_repl_bal_name VARCHAR2(80);
4890 v_freqrule_id NUMBER(9);
4891 v_not_taken_baltype_id number;
4892 v_class_name VARCHAR2(80);
4893
4894 v_employer_match_flag varchar2(2000);
4895 v_after_tax_flag varchar2(2000);
4896 v_after_tax_id number;
4897 v_after_tax_si_id number;
4898 v_after_tax_sf_id number;
4899 v_employer_match_id number;
4900 v_at_er_id number;
4901
4902 v_elig_bal_id number;
4903 v_overlimit_bal_id number;
4904 v_at_er_bal_id number;
4905 v_at_bal_id number;
4906 v_at_accr_id number;
4907 v_at_addl_id number;
4908 v_at_arr_id number;
4909 v_at_elig_id number;
4910 v_at_not_taken_id number;
4911 v_at_overlimit_id number;
4912 v_at_repl_id number;
4913 v_at_to_bond_id number;
4914
4915 v_spr_id number(9);
4916 v_assoc_ele_priority number(9);
4917
4918 CURSOR get_freqrule IS
4919 SELECT ele_payroll_freq_rule_id
4920 FROM pay_ele_payroll_freq_rules
4921 WHERE element_type_id = p_ele_type_id
4922 AND business_group_id = p_business_group_id;
4923
4924 CURSOR get_formulae(l_ele_id in number) IS
4925 SELECT distinct ff.formula_id
4926 FROM pay_status_processing_rules_f spr, ff_formulas_f ff
4927 WHERE spr.element_type_id = l_ele_id
4928 AND ff.formula_id = spr.formula_id
4929 AND ff.business_group_id = p_business_group_id;
4930
4931 CURSOR get_old_formulae(l_ele_name in varchar2) IS
4932 SELECT distinct ff.formula_id
4933 FROM ff_formulas_f ff
4934 WHERE ff.formula_name like upper('OLD%'||p_ele_name||'_FLAT%')
4935 OR ff.formula_name like upper('OLD%'||p_ele_name||'_PERCENT%')
4936 OR ff.formula_name like upper('OLD%'||p_ele_name||'_BENEFIT%')
4937 OR ff.formula_name like upper('OLD%'||p_ele_name||'_PAYROLL%')
4938 OR ff.formula_name like upper('OLD%'||p_ele_name||'_WITHHOLDING')
4939 AND ff.business_group_id = p_business_group_id;
4940
4941 CURSOR get_spr(l_ele_id in number) IS
4942 SELECT distinct status_processing_rule_id
4943 FROM pay_status_processing_rules_f
4944 WHERE element_type_id = l_ele_id
4945 AND business_group_id = p_business_group_id;
4946
4947 --
4948 BEGIN
4949 -- Populate vars.
4950 v_del_val_end := nvl(p_del_val_end_date, c_end_of_time);
4951 v_del_val_start := nvl(p_del_val_start_date, sysdate);
4952 v_del_sess_date := nvl(p_del_sess_date, sysdate);
4953 pay_db_pay_setup.set_session_date(nvl(p_del_val_start_date, sysdate));
4954
4955 assoc_eles(1) := fnd_number.canonical_to_number(p_ele_info_18); -- Special Inputs ele
4956 assoc_eles(2) := fnd_number.canonical_to_number(p_ele_info_19); -- Special Features ele
4957 assoc_eles(3) := fnd_number.canonical_to_number(p_ele_info_20); -- Withholding ele
4958 assoc_eles(4) := p_ele_type_id; -- Base ele
4959
4960 l_num_assoc_eles := 4;
4961
4962 --Begin
4963 select employer_match_flag, aftertax_component_flag, classification_name
4964 into v_employer_match_flag, v_after_tax_flag, v_class_name
4965 from pay_all_deduction_types_v
4966 where element_type_id = p_ele_type_id
4967 and v_del_sess_date >= effective_start_date
4968 and v_del_sess_date <= effective_end_date
4969 and business_group_id + 0 = p_business_group_id ;
4970
4971 --Exception when no_data_found then
4972 -- v_employer_match_flag := 'N';
4973 -- v_after_tax_flag := 'N';
4974 -- v_class_name := 'Voluntary Deductions';
4975 --End;
4976
4977 Begin
4978 select balance_type_id
4979 into v_not_taken_baltype_id
4980 from pay_balance_types
4981 where balance_name like p_ele_name||' Not Taken'
4982 and business_group_id + 0 = p_business_group_id ;
4983
4984 exception when NO_DATA_FOUND then null;
4985 End;
4986
4987 assoc_bals(1) := fnd_number.canonical_to_number(p_ele_info_10); -- Primary bal
4988 assoc_bals(2) := fnd_number.canonical_to_number(p_ele_info_11); -- Accrued bal
4989 assoc_bals(3) := fnd_number.canonical_to_number(p_ele_info_12); -- Arrears bal
4990 assoc_bals(4) := v_not_taken_baltype_id; -- Not Taken bal
4991 assoc_bals(5) := fnd_number.canonical_to_number(p_ele_info_14); -- Bond Purch bal
4992 assoc_bals(6) := fnd_number.canonical_to_number(p_ele_info_15); -- Able bal
4993 assoc_bals(7) := fnd_number.canonical_to_number(p_ele_info_16); -- Additional Amt bal
4994 assoc_bals(8) := fnd_number.canonical_to_number(p_ele_info_17); -- Replacement Amt bal
4995
4996 Begin
4997 select balance_type_id
4998 into v_elig_bal_id
4999 from pay_balance_types
5000 where balance_name like p_ele_name||' Eligible Comp'
5001 and business_group_id + 0 = p_business_group_id ;
5002
5003 exception when NO_DATA_FOUND then null;
5004 End;
5005
5006 assoc_bals(9) := v_elig_bal_id;
5007 l_num_assoc_bals := 9;
5008
5009 if v_employer_match_flag = 'Y' then
5010 l_num_assoc_eles := l_num_assoc_eles + 1;
5011
5012 select element_type_id
5013 into v_employer_match_id
5014 from pay_element_types_f
5015 where element_name like p_ele_name||' ER'
5016 and v_del_sess_date >= effective_start_date
5017 and v_del_sess_date <= effective_end_date
5018 and business_group_id + 0 = p_business_group_id ;
5019
5020 assoc_eles(l_num_assoc_eles) := v_employer_match_id;
5021 end if;
5022
5023 if v_after_tax_flag = 'Y' then
5024 l_num_assoc_eles := l_num_assoc_eles + 1;
5025
5026 select element_type_id
5027 into v_after_tax_id
5028 from pay_element_types_f
5029 where element_name like p_ele_name||' AT'
5030 AND v_del_sess_date >= effective_start_date
5031 AND v_del_sess_date <= effective_end_date
5032 and business_group_id + 0 = p_business_group_id;
5033
5034 assoc_eles(l_num_assoc_eles) := v_after_tax_id;
5035
5036 l_num_assoc_eles := l_num_assoc_eles + 1;
5037
5038 select element_type_id
5039 into v_after_tax_si_id
5040 from pay_element_types_f
5041 where element_name like p_ele_name||' AT Special Inputs'
5042 AND v_del_sess_date >= effective_start_date
5043 AND v_del_sess_date <= effective_end_date
5044 and business_group_id + 0 = p_business_group_id;
5045
5046 assoc_eles(l_num_assoc_eles) := v_after_tax_si_id;
5047
5048 l_num_assoc_eles := l_num_assoc_eles + 1;
5049
5050 select element_type_id
5051 into v_after_tax_sf_id
5052 from pay_element_types_f
5053 where element_name like p_ele_name||' AT Special Features'
5054 AND v_del_sess_date >= effective_start_date
5055 AND v_del_sess_date <= effective_end_date
5056 and business_group_id + 0 = p_business_group_id;
5057
5058 assoc_eles(l_num_assoc_eles) := v_after_tax_sf_id;
5059
5060 -- Bug 3613575 -- Added table pay_elements_types_f to remove Merge Join Cartesian
5061 select primary_baltype_id, accrued_baltype_id, arrears_baltype_id,
5062 not_taken_baltype_id, tobondpurch_baltype_id,
5063 additional_baltype_id, replacement_baltype_id
5064 into v_at_bal_id, v_at_accr_id, v_at_arr_id, v_at_not_taken_id,
5065 v_at_to_bond_id, v_at_addl_id, v_at_repl_id
5066 from pay_all_deduction_types_v padt,
5067 pay_element_types_f pet
5068 WHERE pet.element_type_id = padt.element_type_id
5069 AND padt.element_name like p_ele_name||' AT'
5070 AND v_del_sess_date >= padt.effective_start_date
5071 AND v_del_sess_date <= padt.effective_end_date
5072 AND padt.business_group_id + 0 = p_business_group_id
5073 AND rownum<2;
5074
5075 select balance_type_id
5076 into v_at_not_taken_id
5077 from pay_balance_types
5078 where balance_name like p_ele_name||' AT Not Taken'
5079 and business_group_id + 0 = p_business_group_id ;
5080
5081 select balance_type_id
5082 into v_at_elig_id
5083 from pay_balance_types
5084 where balance_name like p_ele_name||' AT Eligible Comp'
5085 and business_group_id + 0 = p_business_group_id ;
5086
5087 select balance_type_id
5088 into v_at_overlimit_id
5089 from pay_balance_types
5090 where balance_name like p_ele_name||' AT Overlimit'
5091 and business_group_id + 0 = p_business_group_id ;
5092
5093 assoc_bals(10) := v_at_bal_id;
5094 assoc_bals(11) := v_at_accr_id;
5095 assoc_bals(12) := v_at_addl_id;
5096 assoc_bals(13) := v_at_arr_id;
5097 assoc_bals(14) := v_at_elig_id;
5098 assoc_bals(15) := v_at_not_taken_id;
5099 assoc_bals(16) := v_at_overlimit_id;
5100 assoc_bals(17) := v_at_repl_id;
5101 assoc_bals(18) := v_at_to_bond_id;
5102
5103 l_num_assoc_bals := 18;
5104 end if;
5105
5106 if v_employer_match_flag = 'Y' and v_after_tax_flag = 'Y' then
5107
5108 l_num_assoc_eles := l_num_assoc_eles + 1;
5109
5110 select element_type_id
5111 into v_at_er_id
5112 from pay_element_types_f
5113 where element_name like p_ele_name||' AT ER'
5114 AND v_del_sess_date >= effective_start_date
5115 AND v_del_sess_date <= effective_end_date
5116 and business_group_id + 0 = p_business_group_id ;
5117
5118 assoc_eles(l_num_assoc_eles) := v_at_er_id;
5119
5120 select balance_type_id
5121 into v_at_er_bal_id
5122 from pay_balance_types
5123 where balance_name like p_ele_name||' AT ER'
5124 and business_group_id + 0 = p_business_group_id ;
5125
5126 assoc_bals(19) := v_at_er_bal_id;
5127 l_num_assoc_bals := 19;
5128
5129 end if;
5130
5131 if v_class_name = 'Voluntary Deductions' then
5132
5133 l_num_assoc_bals := l_num_assoc_bals + 1;
5134
5135 begin
5136 select balance_type_id
5137 into v_overlimit_bal_id
5138 from pay_balance_types
5139 where balance_name like p_ele_name||' Overlimit'
5140 and business_group_id + 0 = p_business_group_id ;
5141
5142 exception when NO_DATA_FOUND then null;
5143 end;
5144
5145 assoc_bals(l_num_assoc_bals) := v_overlimit_bal_id;
5146
5147 end if;
5148
5149 --
5150 -- Do not allow deletion of Special Features element.
5151 --
5152 IF p_ele_name like '% Special Features' THEN
5153 hr_utility.set_location('hr_user_init_dedn.do_deletion',40);
5154 hr_utility.set_message(801,'PAY_xxxx_CANNOT_DEL_ELE');
5155 hr_utility.raise_error;
5156 END IF;
5157 --
5158 -- Do not allow deletion of Special Inputs element.
5159 --
5160 IF p_ele_name like '% Special Inputs' THEN
5161 hr_utility.set_location('hr_user_init_dedn.do_deletion',40);
5162 hr_utility.set_message(801,'PAY_xxxx_CANNOT_DEL_ELE');
5163 hr_utility.raise_error;
5164 END IF;
5165 --
5166 -- Do not allow deletion of Withholding element.
5167 --
5168 IF p_ele_name like '% Withholding' THEN
5169 hr_utility.set_location('hr_user_init_dedn.do_deletion',40);
5170 hr_utility.set_message(801,'PAY_xxxx_CANNOT_DEL_ELE');
5171 hr_utility.raise_error;
5172 END IF;
5173 --
5174
5175 --
5176 -- Delete frequency rule info:
5177 --
5178 -- Deletion of any Deduction Frequency Rules should be handled
5179 -- by cascade delete according to db constraint.
5180 -- 14 June 1995: Bug# 271622 notes that ele freq rules are NOT deleted
5181 -- when a deduction is deleted. Constraint not working...
5182 --
5183
5184 OPEN get_freqrule;
5185 LOOP
5186 FETCH get_freqrule INTO v_freqrule_id;
5187 EXIT WHEN get_freqrule%NOTFOUND;
5188 hr_utility.set_location('pyusuidt',153);
5189
5190 begin
5191
5192 DELETE FROM pay_freq_rule_periods
5193 WHERE ele_payroll_freq_rule_id = v_freqrule_id;
5194
5195 exception when NO_DATA_FOUND then
5196 null; -- No freq rule periods exist.
5197 end;
5198
5199 begin
5200
5201 DELETE FROM pay_ele_payroll_freq_rules
5202 WHERE ele_payroll_freq_rule_id = v_freqrule_id;
5203
5204 exception when NO_DATA_FOUND then
5205 null; -- No freq rule exists.
5206 end;
5207
5208 END LOOP;
5209 CLOSE get_freqrule;
5210
5211 --
5212 -- Loop to delete formula result rules, status proc rules, and formulae.
5213 --
5214 -- Note: deletion of formula result rules is handled by
5215 -- del_status_processing_rules.
5216 --
5217
5218 FOR i in 1..l_num_assoc_eles LOOP
5219
5220 IF assoc_eles(i) IS NOT NULL THEN
5221
5222 -- Get formula_ids to delete from various tables:
5223 -- FF_FORMULAS_F
5224 -- FF_FDI_USAGES_F
5225 -- FF_COMPILED_INFO_F
5226 --
5227 OPEN get_formulae(assoc_eles(i));
5228 LOOP
5229
5230 FETCH get_formulae INTO v_ff_id;
5231 EXIT WHEN get_formulae%NOTFOUND;
5232
5233 begin
5234
5235 DELETE FROM ff_formulas_f
5236 WHERE formula_id = v_ff_id;
5237
5238 exception when NO_DATA_FOUND then
5239 null;
5240 end;
5241
5242 begin
5243
5244 DELETE FROM ff_fdi_usages_f
5245 WHERE formula_id = v_ff_id;
5246
5247 exception when NO_DATA_FOUND then
5248 null;
5249 end;
5250
5251 begin
5252
5253 DELETE FROM ff_compiled_info_f
5254 WHERE formula_id = v_ff_id;
5255
5256 exception when NO_DATA_FOUND then
5257 null;
5258 end;
5259
5260 END LOOP;
5261 CLOSE get_formulae;
5262
5263 OPEN get_spr(assoc_eles(i));
5264 LOOP
5265
5266 FETCH get_spr INTO v_spr_id;
5267 EXIT WHEN get_spr%NOTFOUND;
5268
5269 hr_utility.set_location('hr_us_garn_gen.delete_dedn', 10);
5270 hr_elements.del_status_processing_rules(
5271 p_element_type_id => assoc_eles(i),
5272 p_delete_mode => v_del_mode,
5273 p_val_session_date => v_del_sess_date,
5274 p_val_start_date => v_del_val_start,
5275 p_val_end_date => v_del_val_end,
5276 p_startup_mode => v_startup_mode);
5277
5278 END LOOP;
5279 CLOSE get_spr;
5280
5281 END IF;
5282
5283 END LOOP;
5284 --
5285 -- Delete all OLD formulae created during upgrades.
5286 --
5287 OPEN get_old_formulae(p_ele_name);
5288 LOOP
5289 FETCH get_old_formulae
5290 INTO v_ff_id;
5291 EXIT WHEN get_old_formulae%NOTFOUND;
5292
5293 begin
5294
5295 DELETE FROM ff_formulas_f
5296 WHERE formula_id = v_ff_id;
5297
5298 exception when NO_DATA_FOUND then
5299 null;
5300 end;
5301
5302 begin
5303
5304 DELETE FROM ff_fdi_usages_f
5305 WHERE formula_id = v_ff_id;
5306
5307 exception when NO_DATA_FOUND then
5308 null;
5309 end;
5310
5311 begin
5312
5313 DELETE FROM ff_compiled_info_f
5314 WHERE formula_id = v_ff_id;
5315
5316 exception when NO_DATA_FOUND then
5317 null;
5318 end;
5319
5320 END LOOP;
5321 CLOSE get_old_formulae;
5322
5323 --
5324 -- Delete all associated balances.
5325 --
5326 -- Balance type ids of associated balances for this element are passed in
5327 -- via the p_ele_info_xx params.
5328 -- Note, all balance feeds for each balance type are deleted
5329 -- by del_balance_type_cascade.
5330 --
5331 FOR i in 1..l_num_assoc_bals LOOP
5332
5333 IF assoc_bals(i) IS NOT NULL THEN
5334
5335 hr_utility.set_location('hr_us_garn_gen.delete_dedn', 50);
5336 hr_balances.del_balance_type_cascade (
5337 p_balance_type_id => assoc_bals(i),
5338 p_legislation_code => g_template_leg_code,
5339 p_mode => v_del_mode);
5340
5341 hr_utility.set_location('hr_us_garn_gen.delete_dedn', 60);
5342 DELETE FROM pay_balance_types
5343 WHERE balance_type_id = assoc_bals(i);
5344
5345 END IF;
5346
5347 END LOOP;
5348
5349 --
5350 -- Now delete associated eles:
5351 --
5352 -- Note: Input value deletion is handled by del_3p_element_type
5353 --
5354
5355 FOR j in 1..l_num_assoc_eles LOOP
5356
5357 IF assoc_eles(j) IS NOT NULL THEN
5358
5359 select processing_priority
5360 into v_assoc_ele_priority
5361 from pay_element_types_f
5362 where element_type_id = assoc_eles(j)
5363 and v_del_sess_date between effective_start_date
5364 and effective_end_date;
5365
5366 hr_utility.set_location('hr_us_garn_gen.delete_dedn', 20);
5367 hr_elements.chk_del_element_type (
5368 p_mode => v_del_mode,
5369 p_element_type_id => assoc_eles(j),
5370 p_processing_priority => v_assoc_ele_priority,
5371 p_session_date => v_del_sess_date,
5372 p_val_start_date => v_del_val_start,
5373 p_val_end_date => v_del_val_end);
5374
5375 hr_utility.set_location('hr_us_garn_gen.delete_dedn', 30);
5376 hr_elements.del_3p_element_type (
5377 p_element_type_id => assoc_eles(j),
5378 p_delete_mode => v_del_mode,
5379 p_val_session_date => v_del_sess_date,
5380 p_val_start_date => v_del_val_start,
5381 p_val_end_date => v_del_val_end,
5382 p_startup_mode => v_startup_mode);
5383 --
5384 -- Delete element type record:
5385 -- Remember, we're 'ZAP'ing, no need to worry about date-effective delete.
5386 --
5387 hr_utility.set_location('hr_us_garn_gen.delete_dedn', 35);
5388 delete from PAY_ELEMENT_TYPES_F
5389 where element_type_id = assoc_eles(j);
5390
5391 END IF;
5392
5393 END LOOP;
5394
5395 -- ********
5396 -- END LOOP TO DELETE ELES
5397 -- ********
5398
5399 --
5400 -- Special deletion handling for deductions:
5401 -- 1. A benefit deduction will have an employer liability element
5402 -- and balance.
5403 -- 2. A bond deduction will have a bond refund element and balance.
5404 --
5405
5406 --
5407 -- Check for benefit deduction and delete ER element and balance if present.
5408 --
5409
5410 begin
5411
5412 SELECT DISTINCT balance_type_id
5413 INTO v_bal_type_id
5414 FROM pay_balance_types
5415 WHERE balance_name = p_ele_name || ' ER' --Bug 3349594
5416 and business_group_id + 0 = p_business_group_id ;
5417
5418 hr_balances.del_balance_type_cascade (
5419 p_balance_type_id => v_bal_type_id,
5420 p_legislation_code => g_template_leg_code,
5421 p_mode => v_del_mode);
5422
5423 hr_utility.set_location('pyusuiet',165);
5424 delete from PAY_BALANCE_TYPES
5425 where balance_type_id = v_bal_type_id;
5426
5427 exception when NO_DATA_FOUND then
5428 null;
5429 end;
5430
5431 begin
5432
5433 /** Bug 566328: The select statement below is modified to put business group id
5434 in the where clause. Because now it is allowing to enter deduction with
5435 same name in different business groups( ref. Bug 502307), selection only by
5436 element name will fetch more than one row and raise error. **/
5437
5438 SELECT DISTINCT element_type_id
5439 INTO v_eletype_id
5440 FROM pay_element_types_f
5441 WHERE element_name = p_ele_name || ' ER' --Bug 3349594
5442 AND v_del_sess_date >= effective_start_date
5443 AND v_del_sess_date <= effective_end_date
5444 and business_group_id + 0 = p_business_group_id ;
5445
5446 hr_utility.set_location('hr_user_init_dedn.do_deletions', 20);
5447 hr_elements.chk_del_element_type (
5448 p_mode => v_del_mode,
5449 p_element_type_id => v_eletype_id,
5450 p_processing_priority => NULL,
5451 p_session_date => v_del_sess_date,
5452 p_val_start_date => v_del_val_start,
5453 p_val_end_date => v_del_val_end);
5454
5455 hr_utility.set_location('hr_user_init_dedn.do_deletions', 30);
5456 hr_elements.del_3p_element_type (
5457 p_element_type_id => v_eletype_id,
5458 p_delete_mode => v_del_mode,
5459 p_val_session_date => v_del_sess_date,
5460 p_val_start_date => v_del_val_start,
5461 p_val_end_date => v_del_val_end,
5462 p_startup_mode => v_startup_mode);
5463 --
5464 -- Delete element type record:
5465 -- Remember, we're 'ZAP'ing, so no need to worry about date-effective delete.
5466 --
5467 hr_utility.set_location('hr_user_init_dedn.do_deletions', 35);
5468 delete from PAY_ELEMENT_TYPES_F
5469 where element_type_id = v_eletype_id;
5470
5471 exception when NO_DATA_FOUND then
5472 null;
5473 end;
5474
5475 --
5476 -- Check for EE bond and delete refund element if present.
5477 --
5478
5479 begin
5480
5481 SELECT DISTINCT balance_type_id
5482 INTO v_bal_type_id
5483 FROM pay_balance_types
5484 WHERE balance_name = p_ele_name || ' REFUND' --Bug 3349594
5485 and business_group_id + 0 = p_business_group_id ;
5486
5487 hr_balances.del_balance_type_cascade (
5488 p_balance_type_id => v_bal_type_id,
5489 p_legislation_code => g_template_leg_code,
5490 p_mode => v_del_mode);
5491
5492 hr_utility.set_location('pyusuiet',165);
5493 delete from PAY_BALANCE_TYPES
5494 where balance_type_id = v_bal_type_id;
5495
5496 exception when NO_DATA_FOUND then
5497 null;
5498 end;
5499
5500 begin
5501
5502 /** Bug 566328: The select statement below is modified to put business group id
5503 in the where clause. Because now it is allowing to enter deduction with
5504 same name in different business groups( ref. Bug 502307), selection only by
5505 element name will fetch more than one row and raise error. **/
5506
5507 hr_utility.set_location('hr_user_init_dedn.do_deletions', 17);
5508 SELECT DISTINCT element_type_id
5509 INTO v_eletype_id
5510 FROM pay_element_types_f
5511 WHERE element_name = p_ele_name || ' REFUND' --Bug 3349594
5512 AND v_del_sess_date >= effective_start_date
5513 AND v_del_sess_date <= effective_end_date
5514 and business_group_id + 0 = p_business_group_id ;
5515
5516 hr_utility.set_location('hr_user_init_dedn.do_deletions', 20);
5517 hr_elements.chk_del_element_type (
5518 p_mode => v_del_mode,
5519 p_element_type_id => v_eletype_id,
5520 p_processing_priority => NULL,
5521 p_session_date => v_del_sess_date,
5522 p_val_start_date => v_del_val_start,
5523 p_val_end_date => v_del_val_end);
5524
5525 hr_utility.set_location('hr_user_init_dedn.do_deletions', 30);
5526 hr_elements.del_3p_element_type (
5527 p_element_type_id => v_eletype_id,
5528 p_delete_mode => v_del_mode,
5529 p_val_session_date => v_del_sess_date,
5530 p_val_start_date => v_del_val_start,
5531 p_val_end_date => v_del_val_end,
5532 p_startup_mode => v_startup_mode);
5533
5534 hr_utility.set_location('hr_user_init_dedn.do_deletions', 35);
5535 delete from PAY_ELEMENT_TYPES_F
5536 where element_type_id = v_eletype_id;
5537
5538 exception when NO_DATA_FOUND then
5539 null;
5540 end;
5541
5542
5543 END do_deletions; -- Del recs according to lockladder.
5544
5545
5546 FUNCTION get_assoc_ele(p_ele_type_id in NUMBER
5547 ,p_suffix in VARCHAR2
5548 ,p_eff_start_date in DATE
5549 ,p_bg_id in NUMBER) RETURN varchar2 IS
5550
5551 CURSOR csr_sfx IS
5552 SELECT decode(x.element_name,null,'N','Y')
5553 FROM pay_element_types x, pay_element_types b
5554 WHERE b.element_type_id = p_ele_type_id
5555 AND b.business_group_id + 0 = p_bg_id
5556 AND p_eff_start_date between b.effective_start_date
5557 AND b.effective_end_date
5558 AND x.business_group_id + 0 = p_bg_id
5559 AND x.effective_start_date between b.effective_start_date
5560 AND b.effective_end_date
5561 AND b.element_name||' '||p_suffix = x.element_name(+);
5562
5563 /* Bug 703234: l_val is defaulted to 'N' , so that if csr_sfx does not
5564 does not fetch any row l_val passes the value 'N', instead of '', which was
5565 creating problem in the form. Because aftertax_component_flag and
5566 employer_match_flag were not being set to any value */
5567
5568 l_val varchar2(1) := 'N';
5569 BEGIN
5570 OPEN csr_sfx;
5571 FETCH csr_sfx INTO l_val;
5572 CLOSE csr_sfx;
5573 RETURN l_val;
5574 END get_assoc_ele;
5575 END hr_user_init_dedn;