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