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