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