[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Update element type DDF with
associated balances.
21-SEP-94 hparicha 40.7 G1343 Feed "Earnings 401k" balance
when Class/Cat = Earnings/Reg
or Supp/Bonus-Comm.
26-SEP-94 hparicha 40.8 G1201 Add "_GRE_YTD" defined bal for
Primary bal - used in summary
reports.
22-NOV-94 hparicha 40.12 G1529 Fixes for decoupling decoupling
G1601 "Deduction Processing" inpval
from Separate Check processing.
19-DEC-94 hparicha 40.13 G1564 New calculation of OT Base Rate
18-JAN-95 hparicha 40.14 G1565 New associated balance for
"Earnings Hours" needs to be
created and associated when
an earnings has an "Hours"
input value.
Also making change for when
"Non-Payroll Payments" are
generated - ie. dbitem names
clash b/c defined balance
with "Payments" dimension
has same name as dbi created
for element type (ELE_COUNT,
ELE_PAY_VALUE, etc...)
23-FEB-95 allee Changed "Earned Date" to
"Date Earned"
02-MAR-95 spanwar Added call to category feeder
in insert bal feeds section.
05-MAY-95 allee Added session date to
category feeder call
15-JUN-95 hparicha 40.21 Use "associated balance" ids
for deletion of those balances.
New params for do_deletions.
Clean up select count(*)'s
by using exceptions.
21-JUN-95 hparicha 40.22 Comment out call to
pay_db_pay_us_gross.
29-JUN-95 hparicha 40.23 Defined balances are
required for "_ASG_GRE_RUN/
PTD/MONTH/QTD/YTD" - and
also for PER and PER_GRE.
17-OCT-95 hparicha 40.26 315814 Added call to bal type pkg
to check for uniqueness of
"Primary Associated Balance"
name - ie. has same name as
element type being generated.
09-JAN-96 hparicha 40.27 133133 Added defined balances for
Primary associated bal of
GRE_ITD and GRE_RUN. Enables
company-level reports on earnings.
16-FEB-96 hparicha 40.28 Added "PAYMENTS" defined bal to
Hours associated balance.
01-APR-96 hparicha 40.29 348658 Added element type id to Indirect formula
result rules... enabling Formula Results
screen to display element name.
21-APR-1996 hparicha 40.30 337007 Added reducing input values on
340391 Special Features to feed
regular hours worked and
regsal/wages hours balances...
Also added feeds reducing reg pay bals
when p_reduce_regular = Yes.
ie. reduce regular from paid absences.
Added param for p_reduce_regular, new
seggie on ele type ddf. Create appropriate
feeds... Also added to create feeds to Reg
Hours Worked high level bal when class/cat
= earn/reg. Also added feeds to high level
"regpay" bals when earn/reg.
27-AUG-96 ramurthy 40.31 390388 Added the creation of the Jurisdiction
input value to Non-Recurring Imputed
Earnings elements.
6th Sep 1996 hparicha 40.32 385252. No longer allow input of
40.33 Separate Check or Deduction Processing
input values for imputed earnings or
earnings classifications...ie. only
supplementals are allowed to enter
these ivs. Exceptions to this rule
are below:
384282. Attach supplemental earnings
skip formula to imputed earning if it is
a nonrecurring imputed.
399471. Enhancement to allow Earnings
categorized as Shift or Overtime to
process in a tax only run...done by
setting skip formula to Supplemental
Earnings skip ff when this is the case.
All three above bugs fixed in one go.
17th Dec 1996 hparicha 40.34 407348. Do not allow input of Separate Check or
Deduction Processing for nonrecurring imputeds.
22 Jul 1997 mreid 110.1 Removed Ctrl+M
28 Feb 1998 mlisieck 110.2 Changed do_deletions. Included missing
business_group_id condition.
110.3 Bug 633443.
26-APR-98 djeng 110.
09-JUN-99 dsaxby 115.3 873555 corrected the set up of
the c_end_of_time constant to use
hr_general.end_of_time. Were getting
ora-01847.
Also, removed tabs from file, not
supposed to use 'em!
03-NOV-1999 dscully 962590 Feeds to FLSA Hours and FLSA earnings
were being accidently created for all
Regular Earnings elements. They are
no only created if checked on the
Earnings form.
14-FEB-2000 alogue 115.6 Utf8 Support. Input value name lengthened
to 80.
29-APR-2001 ekim 115.7 Added process_mode
14-MAY-2001 ssarma 115.8 Added Hours By Rate result rules
31-jul-2001 tclewis 115.9 Removed check for element classification of
NON-PAYROLL Payments when creating the
payments defined balance (bug 1835350).
25-SEP-2001 meshah 115.10 1952471 Added a new parameter p_prcess_mode to
ins_uie_ele_type. All Special Input and
Special Features elements are created with
process mode of N and the actual elements
have process mode of S.
19-OCT-2001 tclewis 115.12 944995 Added the following input values and
associated formula result rules from the
Main element (The hours x Rate formulas
will be modified to return these values.
RED_SAL_HOURS Reduce regular hours for reg
salary ele.
RED_SAL_PAY Reduce regular pay for reg
salary ele.
RED_WAG_HOURS Reduce regular hours for
reg wage ele.
RED_WAG_HOURS Reduce regular pay for
reg wage ele.
708373 Modified the formula result rules for he
RED_REG_PAY return value from the
RED_REG_PAY input value on the special
features element to the special features
PAY_VALUE input value.
Also modified the cursor in the
create_reduce_regpay_feeds procedure to not
return balance that are fed via the
element classification balance feeder
processes.
21-DEC-2001 ahanda 115.15 1902232 Changed insert into ff_formulas_f
to insert null for legislation_code.
22-JAN-2002 ahanda 115.16 Added call to create_defined_balance
ID for 'Assignment Payments' dimension.
04-FEB-2002 rmonge 115.18 Bug 2074337. Shift input value element
needs to be added when creating an
Earning Element with a Shift Category.
17-May-2002 ekim 115.19 Added p_termination_rule
23-Jul-2002 ekim 115.20 Changed v_bg_name type to use
per_business_groups.name%TYPE
Re-numbered hr_utility.set_location
number to avoid duplicates.
08-Oct-2002 ekim 115.21 Changed to use p_uom_code instead of
p_uom in call to
pay_db_pay_setup.create_input_value
10-Oct-2002 ekim 115.22 fixed GSCC warnings.
11-Nov-2002 ekim 115.23 for Input value of Rate Code, changed
v_gen_dbi = 'N' for formula
HOURS_X_RATE_NONRECUR_V2.
12-Nov-2002 ekim 115.24 Removed default value from do_insertions
to fix GSCC warning.
18-Nov-2002 ekim 115.25 for Input value of Rate Code, changed
v_gen_dbi = 'N' for formula
HOURS_X_RATE_MULTIPLE_NONRECUR_V2.
14-JAN-2003 tclewis 115.26 Modifed processing of the special features
element. If P_REGUCE_REGULAR = 'Y'
then we will create the SF element as
an Earnings element, and not follow
the base elements classification.
Also reviewed for NOCOPY directive. None
required.
12-Mar-2003 ekim 115.27 for create_input_value for Warning,
changed to use p_warn_or_error_code rather
than p_warning_or_error.
01-APR-2003 ahanda 115.28 Added logic to save ASG_GRE_RUN as run level
balances.
26-JUN-2003 ahanda 115.29 Changed call to create_balance_type procedure to
pass the balance name as reporting name.
Added code to populate 'Earnings' category for
balances
08-JAN-2004 ardsouza 115.30 Performance tuning of few queries as required by
bug 3349586.
08-JAN-2004 ardsouza 115.31 Non-mergable views eliminated in 2 queries.
18-MAR-2004 kvsankar 115.32 Changed call to create_balance_type
procedure to pass the balance_category value
depending upon the classification of the
element instead of passing 'Earnings' for
all types of elements (Earnings/Supplemental/
Non-payroll Payments/Imputed Earnings)
created. Elements created with Alien/Expat
classification are not created using this package.
Bug 3311781
20-MAY-2004 meshah 115.33 removed the logic not required for
Non-Recurring elements. Like creation of
additional and replacement defined balances.
03-FEB-2005 RMONGE 115.34 Bug 4134473.
Modified package hr_user_init_earn to null out
the default values of local variables
v_lkp_type and v_dflt_value before calling
'pay_db_pay_setup.create_input_value.
This applies to the following Input Values
Neg Earnings
Reduce Reg Hours
Reduce Reg Pay
Reduce Sal Hours
Reduce Sal Pay
Reduce Wag Hours
Reduce Wag Pay
09-JUN-2005 kvsankar 115.35 Bug 4420211
Modified the procedure ins_uie_input_vals
so that MANDATORY Flag is set to 'N'
for the input value 'Deduction Processing'
in case of 'Non-payroll Payments' Elements
13-Nov-2011 emunisek 115.37 Bug 13484606 Replaced FF_FORMULAS_F.FORMULA_TEXT%TYPE
for variables based on formula_text column.
*/
--
-- If any part of this package body fails, then rollback entire transaction.
-- Return to form and alert user of corrections required or to notify
-- Oracle HR staff in case of more serious error.
-- Pre-Insert validation procedures: called during insert procedures/functions.
-- Provided as part of API for the following:
-- 1) Element creation;
FUNCTION do_insertions (
p_ele_name in varchar2,
p_ele_reporting_name in varchar2,
p_ele_description in varchar2,
p_ele_classification in varchar2,
p_ele_category in varchar2,
p_ele_ot_base in varchar2,
p_flsa_hours in varchar2,
p_ele_processing_type in varchar2,
p_ele_priority in number,
p_ele_standard_link in varchar2,
p_ele_calc_ff_id in number,
p_ele_calc_ff_name in varchar2,
p_sep_check_option in varchar2,
p_dedn_proc in varchar2,
p_mix_flag in varchar2,
p_reduce_regular in varchar2,
p_ele_eff_start_date in date ,
p_ele_eff_end_date in date ,
p_bg_id in number,
p_termination_rule in varchar2
) RETURN NUMBER IS
--
-- global constants
c_end_of_time CONSTANT DATE := hr_general.end_of_time;
v_ele_type_id NUMBER(9); -- Populated by insertion of element type.
v_shadow_ele_type_id NUMBER(9); -- Populated by insertion of element type.
v_inputs_ele_type_id NUMBER(9); -- Populated by insertion of element type.
v_bal_type_id NUMBER(9); -- Pop'd by insertion of balance type.
v_neg_earn_bal_type_id NUMBER(9); -- Pop'd by insertion of balance type.
v_hrs_bal_type_id NUMBER(9); -- Pop'd by insertion of balance type.
v_addl_amt_bal_type_id NUMBER(9); -- Pop'd by insertion of balance type.
v_repl_amt_bal_type_id NUMBER(9); -- Pop'd by insertion of balance type.
v_payval_id NUMBER(9); -- ID of payval for bal feed insert.
UPDATE pay_input_values_f
SET mandatory_flag = 'X'
WHERE element_type_id = v_eletype_id
AND name = v_pay_value_name;
SELECT FF.formula_text, FF.formula_type_id
INTO v_skeleton_formula_text, v_skeleton_formula_type_id
FROM ff_formulas_f FF
WHERE FF.formula_name = v_formula_name --UPPER(FF.formula_name) = UPPER(p_amt_rule)
AND FF.business_group_id IS NULL
AND FF.legislation_code = 'US'
AND g_eff_start_date BETWEEN FF.effective_start_date
AND FF.effective_end_date;
SELECT ff_formulas_s.nextval
INTO v_formula_id
FROM sys.dual;
INSERT INTO ff_formulas_f (
FORMULA_ID,
EFFECTIVE_START_DATE,
EFFECTIVE_END_DATE,
BUSINESS_GROUP_ID,
LEGISLATION_CODE,
FORMULA_TYPE_ID,
FORMULA_NAME,
DESCRIPTION,
FORMULA_TEXT,
STICKY_FLAG,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATED_BY,
CREATION_DATE)
values (
v_formula_id,
g_eff_start_date,
g_eff_end_date,
p_bg_id,
NULL,
v_skeleton_formula_type_id,
v_ele_formula_name,
p_ff_desc,
v_ele_formula_text,
'N',
NULL,
NULL,
NULL,
-1,
g_eff_start_date);
UPDATE pay_element_types_f
SET element_information_category = v_ele_info_cat,
element_information1 = p_ele_cat,
element_information8 = p_include_in_ot,
element_information11 = p_flsa_hrs,
element_information9 = v_mix_category,
element_information12 = p_hrs_baltype_id
WHERE element_type_id = p_ele_id
AND business_group_id = p_bg_id;
UPDATE pay_element_types_f
SET element_information_category = v_ele_info_cat,
element_information1 = p_ele_cat,
element_information8 = p_include_in_ot
WHERE element_type_id = p_shadow_ele_id
AND business_group_id = p_bg_id;
UPDATE pay_element_types_f
SET element_information_category = v_ele_info_cat,
element_information1 = p_ele_cat,
element_information8 = p_include_in_ot
WHERE element_type_id = p_inputs_ele_id
AND business_group_id = p_bg_id;
UPDATE pay_element_types_f
SET element_information_category = v_ele_info_cat,
element_information1 = p_ele_cat,
element_information8 = p_include_in_ot,
element_information11 = p_flsa_hrs,
element_information9 = v_mix_category,
element_information12 = p_hrs_baltype_id
WHERE element_type_id = p_ele_id
AND business_group_id = p_bg_id;
UPDATE pay_element_types_f
SET element_information_category = v_ele_info_cat,
element_information1 = p_ele_cat,
element_information8 = p_include_in_ot
WHERE element_type_id = p_shadow_ele_id
AND business_group_id = p_bg_id;
UPDATE pay_element_types_f
SET element_information_category = v_ele_info_cat,
element_information1 = p_ele_cat,
element_information8 = p_include_in_ot
WHERE element_type_id = p_inputs_ele_id
AND business_group_id = p_bg_id;
UPDATE pay_element_types_f
SET element_information_category = v_ele_info_cat,
element_information1 = p_ele_cat,
element_information9 = v_mix_category,
element_information12 = p_hrs_baltype_id
WHERE element_type_id = p_ele_id
AND business_group_id = p_bg_id;
UPDATE pay_element_types_f
SET element_information_category = v_ele_info_cat,
element_information1 = p_ele_cat
WHERE element_type_id = p_shadow_ele_id
AND business_group_id = p_bg_id;
UPDATE pay_element_types_f
SET element_information_category = v_ele_info_cat,
element_information1 = p_ele_cat
WHERE element_type_id = p_inputs_ele_id
AND business_group_id = p_bg_id;
UPDATE pay_element_types_f
SET element_information_category = v_ele_info_cat,
element_information1 = p_ele_cat,
element_information9 = v_mix_category
WHERE element_type_id = p_ele_id
AND business_group_id = p_bg_id;
UPDATE pay_element_types_f
SET element_information_category = v_ele_info_cat,
element_information1 = p_ele_cat
WHERE element_type_id = p_shadow_ele_id
AND business_group_id = p_bg_id;
UPDATE pay_element_types_f
SET element_information_category = v_ele_info_cat,
element_information1 = p_ele_cat
WHERE element_type_id = p_inputs_ele_id
AND business_group_id = p_bg_id;
UPDATE pay_element_types_f
SET element_information9 = v_mix_category
WHERE element_type_id = p_ele_id
AND business_group_id = p_bg_id;
p_insert_update_flag => 'UPDATE',
p_input_value_id => v_inpval_id,
p_rowid => NULL,
p_recurring_flag => p_ele_proc_type,
p_mandatory_flag => 'N',
p_hot_default_flag => 'N',
p_standard_link_flag => 'N',
p_classification_type => 'N',
p_name => v_inpval_name,
p_uom => v_inpval_uom,
p_min_value => NULL,
p_max_value => NULL,
p_default_value => NULL,
p_lookup_type => NULL,
p_formula_id => NULL,
p_generate_db_items_flag => v_gen_dbi,
p_warning_or_error => NULL);
p_insert_update_flag => 'UPDATE',
p_input_value_id => v_inpval_id,
p_rowid => NULL,
p_recurring_flag => p_ele_proc_type,
p_mandatory_flag => 'N',
p_hot_default_flag => 'N',
p_standard_link_flag => 'N',
p_classification_type => 'N',
p_name => v_inpval_name,
p_uom => v_inpval_uom,
p_min_value => NULL,
p_max_value => NULL,
p_default_value => NULL,
p_lookup_type => NULL,
p_formula_id => NULL,
p_generate_db_items_flag => v_gen_dbi,
p_warning_or_error => NULL);
p_insert_update_flag => 'UPDATE',
p_input_value_id => v_inpval_id,
p_rowid => NULL,
p_recurring_flag => p_ele_proc_type,
p_mandatory_flag => 'N',
p_hot_default_flag => 'N',
p_standard_link_flag => 'N',
p_classification_type => 'N',
p_name => v_inpval_name,
p_uom => v_inpval_uom,
p_min_value => NULL,
p_max_value => NULL,
p_default_value => NULL,
p_lookup_type => NULL,
p_formula_id => NULL,
p_generate_db_items_flag => v_gen_dbi,
p_warning_or_error => NULL);
p_insert_update_flag => 'UPDATE',
p_input_value_id => v_inpval_id,
p_rowid => NULL,
p_recurring_flag => p_ele_proc_type,
p_mandatory_flag => 'N',
p_hot_default_flag => 'N',
p_standard_link_flag => 'N',
p_classification_type => 'N',
p_name => v_inpval_name,
p_uom => v_inpval_uom,
p_min_value => NULL,
p_max_value => NULL,
p_default_value => NULL,
p_lookup_type => NULL,
p_formula_id => NULL,
p_generate_db_items_flag => v_gen_dbi,
p_warning_or_error => NULL);
p_insert_update_flag => 'UPDATE',
p_input_value_id => v_inpval_id,
p_rowid => NULL,
p_recurring_flag => p_ele_proc_type,
p_mandatory_flag => 'N',
p_hot_default_flag => 'N',
p_standard_link_flag => 'N',
p_classification_type => 'N',
p_name => v_inpval_name,
p_uom => v_inpval_uom,
p_min_value => NULL,
p_max_value => NULL,
p_default_value => NULL,
p_lookup_type => NULL,
p_formula_id => NULL,
p_generate_db_items_flag => v_gen_dbi,
p_warning_or_error => NULL);
select element_type_id
into v_hbyr_ele_type_id
from pay_element_types_f
where element_name = 'Hours by Rate'
and legislation_code = g_template_leg_code
and sysdate between effective_start_date
and effective_end_date;
select input_value_id
into v_hbyr_ele_inpval_id
from pay_input_values_f
where element_type_id = v_hbyr_ele_type_id
and name = 'Element Type Id'
and sysdate between effective_start_date
and effective_end_date;
select input_value_id
into v_hbyr_rate_inpval_id
from pay_input_values_f
where element_type_id = v_hbyr_ele_type_id
and name = 'Rate'
and sysdate between effective_start_date
and effective_end_date;
select input_value_id
into v_hbyr_hours_inpval_id
from pay_input_values_f
where element_type_id = v_hbyr_ele_type_id
and name = 'Hours'
and sysdate between effective_start_date
and effective_end_date;
p_insert_update_flag => 'UPDATE',
p_input_value_id => v_inpval_id,
p_rowid => NULL,
p_recurring_flag => p_ele_proc_type,
p_mandatory_flag => 'N',
p_hot_default_flag => 'N',
p_standard_link_flag => 'N',
p_classification_type => 'N',
p_name => v_inpval_name,
p_uom => v_inpval_uom,
p_min_value => NULL,
p_max_value => NULL,
p_default_value => NULL,
p_lookup_type => NULL,
p_formula_id => NULL,
p_generate_db_items_flag => v_gen_dbi,
p_warning_or_error => NULL);
p_insert_update_flag => 'UPDATE',
p_input_value_id => v_inpval_id,
p_rowid => NULL,
p_recurring_flag => p_ele_proc_type,
p_mandatory_flag => 'N',
p_hot_default_flag => 'N',
p_standard_link_flag => 'N',
p_classification_type => 'N',
p_name => v_inpval_name,
p_uom => v_inpval_uom,
p_min_value => NULL,
p_max_value => NULL,
p_default_value => NULL,
p_lookup_type => NULL,
p_formula_id => NULL,
p_generate_db_items_flag => v_gen_dbi,
p_warning_or_error => NULL);
p_insert_update_flag => 'UPDATE',
p_input_value_id => v_inpval_id,
p_rowid => NULL,
p_recurring_flag => p_ele_proc_type,
p_mandatory_flag => 'N',
p_hot_default_flag => 'N',
p_standard_link_flag => 'N',
p_classification_type => 'N',
p_name => v_inpval_name,
p_uom => v_inpval_uom,
p_min_value => NULL,
p_max_value => NULL,
p_default_value => NULL,
p_lookup_type => NULL,
p_formula_id => NULL,
p_generate_db_items_flag => v_gen_dbi,
p_warning_or_error => NULL);
p_insert_update_flag => 'UPDATE',
p_input_value_id => v_inpval_id,
p_rowid => NULL,
p_recurring_flag => p_ele_proc_type,
p_mandatory_flag => 'N',
p_hot_default_flag => 'N',
p_standard_link_flag => 'N',
p_classification_type => 'N',
p_name => v_inpval_name,
p_uom => v_inpval_uom,
p_min_value => NULL,
p_max_value => NULL,
p_default_value => NULL,
p_lookup_type => NULL,
p_formula_id => NULL,
p_generate_db_items_flag => v_gen_dbi,
p_warning_or_error => NULL);
select element_type_id
into v_hbyr_ele_type_id
from pay_element_types_f
where element_name = 'Hours by Rate'
and legislation_code = g_template_leg_code
and sysdate between effective_start_date
and effective_end_date;
select input_value_id
into v_hbyr_ele_inpval_id
from pay_input_values_f
where element_type_id = v_hbyr_ele_type_id
and name = 'Element Type Id'
and sysdate between effective_start_date
and effective_end_date;
select input_value_id
into v_hbyr_rate_inpval_id
from pay_input_values_f
where element_type_id = v_hbyr_ele_type_id
and name = 'Rate'
and sysdate between effective_start_date
and effective_end_date;
select input_value_id
into v_hbyr_hours_inpval_id
from pay_input_values_f
where element_type_id = v_hbyr_ele_type_id
and name = 'Hours'
and sysdate between effective_start_date
and effective_end_date;
select input_value_id
into v_hbyr_mult_inpval_id
from pay_input_values_f
where element_type_id = v_hbyr_ele_type_id
and name = 'Multiple'
and sysdate between effective_start_date
and effective_end_date;
p_insert_update_flag => 'UPDATE',
p_input_value_id => v_inpval_id,
p_rowid => NULL,
p_recurring_flag => p_ele_proc_type,
p_mandatory_flag => 'N',
p_hot_default_flag => 'N',
p_standard_link_flag => 'N',
p_classification_type => 'N',
p_name => v_inpval_name,
p_uom => v_inpval_uom,
p_min_value => NULL,
p_max_value => NULL,
p_default_value => NULL,
p_lookup_type => NULL,
p_formula_id => NULL,
p_generate_db_items_flag => v_gen_dbi,
p_warning_or_error => NULL);
p_insert_update_flag => 'UPDATE',
p_input_value_id => v_inpval_id,
p_rowid => NULL,
p_recurring_flag => p_ele_proc_type,
p_mandatory_flag => 'N',
p_hot_default_flag => 'N',
p_standard_link_flag => 'N',
p_classification_type => 'N',
p_name => v_inpval_name,
p_uom => v_inpval_uom,
p_min_value => NULL,
p_max_value => NULL,
p_default_value => NULL,
p_lookup_type => NULL,
p_formula_id => NULL,
p_generate_db_items_flag => v_gen_dbi,
p_warning_or_error => NULL);
SELECT formula_id
INTO v_val_formula_id
FROM ff_formulas_f
WHERE business_group_id IS NULL
AND legislation_code = 'US'
AND formula_name = 'JURISDICTION_VALIDATION';
p_insert_update_flag => 'UPDATE',
p_input_value_id => v_inpval_id,
p_rowid => NULL,
p_recurring_flag => p_ele_proc_type,
p_mandatory_flag => 'N',
p_hot_default_flag => 'N',
p_standard_link_flag => 'N',
p_classification_type => 'N',
p_name => v_inpval_name,
p_uom => v_inpval_uom,
p_min_value => NULL,
p_max_value => NULL,
p_default_value => NULL,
p_lookup_type => NULL,
p_formula_id => v_val_formula_id,
p_generate_db_items_flag => v_gen_dbi,
p_warning_or_error => 'Error');
p_insert_update_flag => 'UPDATE',
p_input_value_id => v_inpval_id,
p_rowid => NULL,
p_recurring_flag => p_ele_proc_type,
p_mandatory_flag => 'N',
p_hot_default_flag => 'N',
p_standard_link_flag => 'N',
p_classification_type => 'N',
p_name => v_inpval_name,
p_uom => v_inpval_uom,
p_min_value => NULL,
p_max_value => NULL,
p_default_value => v_dflt_value,
p_lookup_type => v_lkp_type,
p_formula_id => NULL,
p_generate_db_items_flag => v_gen_dbi,
p_warning_or_error => NULL);
p_insert_update_flag => 'UPDATE',
p_input_value_id => v_inpval_id,
p_rowid => NULL,
p_recurring_flag => p_ele_proc_type,
p_mandatory_flag => v_mand_flag,
p_hot_default_flag => 'N',
p_standard_link_flag => 'N',
p_classification_type => 'N',
p_name => v_inpval_name,
p_uom => v_inpval_uom,
p_min_value => NULL,
p_max_value => NULL,
p_default_value => v_dflt_value,
p_lookup_type => v_lkp_type,
p_formula_id => NULL,
p_generate_db_items_flag => v_gen_dbi,
p_warning_or_error => NULL);
p_insert_update_flag => 'UPDATE',
p_input_value_id => v_inpval_id,
p_rowid => NULL,
p_recurring_flag => p_ele_proc_type,
p_mandatory_flag => v_mand_flag,
p_hot_default_flag => 'N',
p_standard_link_flag => 'N',
p_classification_type => 'N',
p_name => v_inpval_name,
p_uom => v_inpval_uom,
p_min_value => NULL,
p_max_value => NULL,
p_default_value => v_dflt_value,
p_lookup_type => v_lkp_type,
p_formula_id => NULL,
p_generate_db_items_flag => v_gen_dbi,
p_warning_or_error => NULL);
p_insert_update_flag => 'UPDATE',
p_input_value_id => v_inpval_id,
p_rowid => NULL,
p_recurring_flag => p_ele_proc_type,
p_mandatory_flag => 'N',
p_hot_default_flag => 'N',
p_standard_link_flag => 'N',
p_classification_type => 'N',
p_name => v_inpval_name,
p_uom => v_inpval_uom,
p_min_value => NULL,
p_max_value => NULL,
p_default_value => NULL,
p_lookup_type => NULL,
p_formula_id => NULL,
p_generate_db_items_flag => v_gen_dbi,
p_warning_or_error => NULL);
p_insert_update_flag => 'UPDATE',
p_input_value_id => gi_repl_inpval_id,
p_rowid => NULL,
p_recurring_flag => 'N',
p_mandatory_flag => 'N',
p_hot_default_flag => 'N',
p_standard_link_flag => 'N',
p_classification_type => 'N',
p_name => v_inpval_name,
p_uom => v_inpval_uom,
p_min_value => NULL,
p_max_value => NULL,
p_default_value => NULL,
p_lookup_type => NULL,
p_formula_id => NULL,
p_generate_db_items_flag => v_gen_dbi,
p_warning_or_error => NULL);
p_insert_update_flag => 'UPDATE',
p_input_value_id => gi_addl_inpval_id,
p_rowid => NULL,
p_recurring_flag => 'N',
p_mandatory_flag => 'N',
p_hot_default_flag => 'N',
p_standard_link_flag => 'N',
p_classification_type => 'N',
p_name => v_inpval_name,
p_uom => v_inpval_uom,
p_min_value => NULL,
p_max_value => NULL,
p_default_value => NULL,
p_lookup_type => NULL,
p_formula_id => NULL,
p_generate_db_items_flag => v_gen_dbi,
p_warning_or_error => NULL);
p_insert_update_flag => 'UPDATE',
p_input_value_id => g_repl_inpval_id,
p_rowid => NULL,
p_recurring_flag => 'N',
p_mandatory_flag => 'N',
p_hot_default_flag => 'N',
p_standard_link_flag => 'N',
p_classification_type => 'N',
p_name => v_inpval_name,
p_uom => v_inpval_uom,
p_min_value => NULL,
p_max_value => NULL,
p_default_value => NULL,
p_lookup_type => NULL,
p_formula_id => NULL,
p_generate_db_items_flag => v_gen_dbi,
p_warning_or_error => NULL);
p_insert_update_flag => 'UPDATE',
p_input_value_id => g_addl_inpval_id,
p_rowid => NULL,
p_recurring_flag => 'N',
p_mandatory_flag => 'N',
p_hot_default_flag => 'N',
p_standard_link_flag => 'N',
p_classification_type => 'N',
p_name => v_inpval_name,
p_uom => v_inpval_uom,
p_min_value => NULL,
p_max_value => NULL,
p_default_value => NULL,
p_lookup_type => NULL,
p_formula_id => NULL,
p_generate_db_items_flag => v_gen_dbi,
p_warning_or_error => NULL);
p_insert_update_flag => 'UPDATE',
p_input_value_id => g_neg_earn_inpval_id,
p_rowid => NULL,
p_recurring_flag => 'N',
p_mandatory_flag => 'N',
p_hot_default_flag => 'N',
p_standard_link_flag => 'N',
p_classification_type => 'N',
p_name => v_inpval_name,
p_uom => v_inpval_uom,
p_min_value => NULL,
p_max_value => NULL,
p_default_value => NULL,
p_lookup_type => NULL,
p_formula_id => NULL,
p_generate_db_items_flag => v_gen_dbi,
p_warning_or_error => NULL);
p_insert_update_flag => 'UPDATE',
p_input_value_id => g_reduce_hrs_inpval_id,
p_rowid => NULL,
p_recurring_flag => 'N',
p_mandatory_flag => 'N',
p_hot_default_flag => 'N',
p_standard_link_flag => 'N',
p_classification_type => 'N',
p_name => v_inpval_name,
p_uom => v_inpval_uom,
p_min_value => NULL,
p_max_value => NULL,
p_default_value => NULL,
p_lookup_type => NULL,
p_formula_id => NULL,
p_generate_db_items_flag => v_gen_dbi,
p_warning_or_error => NULL);
p_insert_update_flag => 'UPDATE',
p_input_value_id => g_reduce_pay_inpval_id,
p_rowid => NULL,
p_recurring_flag => 'N',
p_mandatory_flag => 'N',
p_hot_default_flag => 'N',
p_standard_link_flag => 'N',
p_classification_type => 'N',
p_name => v_inpval_name,
p_uom => v_inpval_uom,
p_min_value => NULL,
p_max_value => NULL,
p_default_value => NULL,
p_lookup_type => NULL,
p_formula_id => NULL,
p_generate_db_items_flag => v_gen_dbi,
p_warning_or_error => NULL);
p_insert_update_flag => 'UPDATE',
p_input_value_id => g_reduce_sal_hrs_inpval_id,
p_rowid => NULL,
p_recurring_flag => 'N',
p_mandatory_flag => 'N',
p_hot_default_flag => 'N',
p_standard_link_flag => 'N',
p_classification_type => 'N',
p_name => v_inpval_name,
p_uom => v_inpval_uom,
p_min_value => NULL,
p_max_value => NULL,
p_default_value => NULL,
p_lookup_type => NULL,
p_formula_id => NULL,
p_generate_db_items_flag => v_gen_dbi,
p_warning_or_error => NULL);
p_insert_update_flag => 'UPDATE',
p_input_value_id => g_reduce_sal_pay_inpval_id,
p_rowid => NULL,
p_recurring_flag => 'N',
p_mandatory_flag => 'N',
p_hot_default_flag => 'N',
p_standard_link_flag => 'N',
p_classification_type => 'N',
p_name => v_inpval_name,
p_uom => v_inpval_uom,
p_min_value => NULL,
p_max_value => NULL,
p_default_value => NULL,
p_lookup_type => NULL,
p_formula_id => NULL,
p_generate_db_items_flag => v_gen_dbi,
p_warning_or_error => NULL);
p_insert_update_flag => 'UPDATE',
p_input_value_id => g_reduce_wag_hrs_inpval_id,
p_rowid => NULL,
p_recurring_flag => 'N',
p_mandatory_flag => 'N',
p_hot_default_flag => 'N',
p_standard_link_flag => 'N',
p_classification_type => 'N',
p_name => v_inpval_name,
p_uom => v_inpval_uom,
p_min_value => NULL,
p_max_value => NULL,
p_default_value => NULL,
p_lookup_type => NULL,
p_formula_id => NULL,
p_generate_db_items_flag => v_gen_dbi,
p_warning_or_error => NULL);
p_insert_update_flag => 'UPDATE',
p_input_value_id => g_reduce_wag_pay_inpval_id,
p_rowid => NULL,
p_recurring_flag => 'N',
p_mandatory_flag => 'N',
p_hot_default_flag => 'N',
p_standard_link_flag => 'N',
p_classification_type => 'N',
p_name => v_inpval_name,
p_uom => v_inpval_uom,
p_min_value => NULL,
p_max_value => NULL,
p_default_value => NULL,
p_lookup_type => NULL,
p_formula_id => NULL,
p_generate_db_items_flag => v_gen_dbi,
p_warning_or_error => NULL);
SELECT status_processing_rule_id
INTO v_status_proc_id
FROM pay_status_processing_rules_f
WHERE assignment_status_type_id IS NULL
AND element_type_id = p_ele_type_id;
select pivf.input_value_id
into g_shadow_info_payval_id
from pay_input_values_f pivf
where pivf.element_type_id = p_shadow_ele_type_id
and pivf.name = 'Pay Value';
SELECT f.balance_type_id
FROM pay_balance_feeds_f f,
pay_balance_types bt
WHERE f.input_value_id = p_iv_id
AND p_eff_start_date between f.effective_start_date
and f.effective_end_date
AND bt.balance_name not in ('FLSA Earnings','FLSA Hours')
AND bt.balance_type_id = f.balance_type_id
and bt.balance_type_id not in
(select bc.balance_type_id
from pay_balance_classifications bc,
pay_element_classifications ec
where ec.legislation_code = 'US'
and ec.classification_name = 'Earnings'
and bc.classification_id = ec.classification_id);
select i.input_value_id
into l_pv_id
from pay_element_types_f e,
pay_input_values_f i
where e.element_name = 'Regular Salary'
and p_eff_start_date between e.effective_start_date
and e.effective_end_date
and e.business_group_id is null
and e.legislation_code = 'US'
and i.element_type_id = e.element_type_id
and i.name = l_pv_name
and p_eff_start_date between i.effective_start_date
and i.effective_end_date
and i.business_group_id is null
and i.legislation_code = 'US';
select COUNT(0)
into already_exists
from pay_balance_feeds_f
where input_value_id = g_reduce_pay_inpval_id
and balance_type_id = l_bal_id;
select i.input_value_id
into l_pv_id
from pay_element_types_f e,
pay_input_values_f i
where e.element_name = 'Regular Wages'
and p_eff_start_date between e.effective_start_date
and e.effective_end_date
and e.business_group_id is null
and e.legislation_code = 'US'
and i.element_type_id = e.element_type_id
and i.name = l_pv_name
and p_eff_start_date between i.effective_start_date
and i.effective_end_date
and i.business_group_id is null
and i.legislation_code = 'US';
select COUNT(0)
into already_exists
from pay_balance_feeds_f
where input_value_id = g_reduce_pay_inpval_id
and balance_type_id = l_bal_id;
SELECT f.balance_type_id
FROM pay_balance_feeds_f f,
pay_balance_types bt
WHERE f.input_value_id = p_iv_id
AND p_eff_start_date between f.effective_start_date
and f.effective_end_date
AND ( (bt.business_group_id is NULL and bt.legislation_code = 'US')
OR (bt.business_group_id = p_business_group_id and bt.legislation_code is NULL) )
AND bt.balance_type_id = f.balance_type_id
AND bt.balance_name not in ('Regular Salary', 'Regular Wages','FLSA Hours','FLSA Earnings');
select i.input_value_id
into l_pv_id
from pay_element_types_f e,
pay_input_values_f i
where e.element_name = 'Regular Salary'
and p_eff_start_date between e.effective_start_date
and e.effective_end_date
and e.business_group_id is null
and e.legislation_code = 'US'
and i.element_type_id = e.element_type_id
and i.name = l_pv_name
and p_eff_start_date between i.effective_start_date
and i.effective_end_date
and i.business_group_id is null
and i.legislation_code = 'US';
select COUNT(0)
into already_exists
from pay_balance_feeds_f
where input_value_id = p_iv_id
and balance_type_id = l_bal_id;
select i.input_value_id
into l_pv_id
from pay_element_types_f e,
pay_input_values_f i
where e.element_name = 'Regular Wages'
and p_eff_start_date between e.effective_start_date
and e.effective_end_date
and e.business_group_id is null
and e.legislation_code = 'US'
and i.element_type_id = e.element_type_id
and i.name = l_pv_name
and p_eff_start_date between i.effective_start_date
and i.effective_end_date
and i.business_group_id is null
and i.legislation_code = 'US';
select COUNT(0)
into already_exists
from pay_balance_feeds_f
where input_value_id = p_iv_id
and balance_type_id = l_bal_id;
select name
into v_bg_name
from per_business_groups
where business_group_id = p_bg_id;
SELECT FF.formula_id
INTO v_skip_formula_id
FROM ff_formulas_f FF
WHERE FF.formula_name = 'REGULAR_EARNINGS'
AND FF.formula_id >= 0
AND FF.business_group_id IS NULL
AND legislation_code = g_template_leg_code
AND p_ele_eff_start_date BETWEEN FF.effective_start_date
AND FF.effective_end_date;
SELECT FF.formula_id
INTO v_skip_formula_id
FROM ff_formulas_f FF
WHERE FF.formula_name = 'SUPPLEMENTAL_EARNINGS'
AND FF.formula_id >= 0
AND FF.business_group_id IS NULL
AND legislation_code = g_template_leg_code
AND p_ele_eff_start_date BETWEEN FF.effective_start_date
AND FF.effective_end_date;
SELECT FF.formula_id
INTO v_skip_formula_id
FROM ff_formulas_f FF
WHERE FF.formula_name = 'SUPPLEMENTAL_EARNINGS'
AND FF.formula_id >= 0
AND FF.business_group_id IS NULL
AND legislation_code = g_template_leg_code
AND p_ele_eff_start_date BETWEEN FF.effective_start_date
AND FF.effective_end_date;
select distinct(classification_id)
into v_primary_class_id
from pay_element_types_f
where element_type_id = v_ele_type_id;
SELECT default_low_priority,
default_high_priority
INTO v_class_lo_priority,
v_class_hi_priority
FROM pay_element_classifications
WHERE classification_id = v_primary_class_id
AND nvl(business_group_id, p_bg_id) = p_bg_id;
SELECT classification_id,
default_priority,
default_low_priority,
default_high_priority
INTO v_primary_class_id,
v_info_dflt_priority,
v_class_lo_priority,
v_class_hi_priority
FROM pay_element_classifications
WHERE classification_name = 'Information'
AND nvl(business_group_id, p_bg_id) = p_bg_id;
p_insert_update_flag => 'UPDATE',
p_input_value_id => v_info_payval_id,
p_rowid => NULL,
p_recurring_flag => p_ele_processing_type,
p_mandatory_flag => 'N',
p_hot_default_flag => 'X',
p_standard_link_flag => 'N',
p_classification_type => 'N',
p_name => 'Pay Value',
p_uom => 'M',
p_min_value => NULL,
p_max_value => NULL,
p_default_value => NULL,
p_lookup_type => NULL,
p_formula_id => NULL,
p_generate_db_items_flag => 'Y',
p_warning_or_error => NULL);
p_insert_update_flag => 'UPDATE',
p_input_value_id => v_shadow_info_payval_id,
p_rowid => NULL,
p_recurring_flag => 'N',
p_mandatory_flag => 'X',
p_hot_default_flag => 'N',
p_standard_link_flag => 'N',
p_classification_type => 'N',
p_name => 'Pay Value',
p_uom => 'M',
p_min_value => NULL,
p_max_value => NULL,
p_default_value => NULL,
p_lookup_type => NULL,
p_formula_id => NULL,
p_generate_db_items_flag => 'Y',
p_warning_or_error => NULL);
p_insert_update_flag => 'UPDATE',
p_input_value_id => v_inputs_info_payval_id,
p_rowid => NULL,
p_recurring_flag => 'N',
p_mandatory_flag => 'X',
p_hot_default_flag => 'N',
p_standard_link_flag => 'N',
p_classification_type => 'N',
p_name => 'Pay Value',
p_uom => 'M',
p_min_value => NULL,
p_max_value => NULL,
p_default_value => NULL,
p_lookup_type => NULL,
p_formula_id => NULL,
p_generate_db_items_flag => 'Y',
p_warning_or_error => NULL);
UPDATE pay_element_types_f
SET element_information10 = v_bal_type_id,
post_termination_rule = p_termination_rule
WHERE element_type_id = v_ele_type_id
AND business_group_id = p_bg_id;
select iv.input_value_id
into v_payval_id
from pay_input_values_f iv
where iv.element_type_id = v_ele_type_id
and iv.business_group_id = p_bg_id
and iv.name = v_payval_name;
SELECT formula_id
INTO v_payval_formula_id
FROM ff_formulas_f
WHERE business_group_id IS NULL
AND legislation_code = 'US'
AND formula_name = 'PAYVALUE_VALIDATION';
UPDATE pay_input_values_f
SET formula_id = v_payval_formula_id,
warning_or_error = 'E'
WHERE input_value_id = v_payval_id;
select iv.input_value_id
into v_negearn_inpval_id
from pay_input_values_f iv
where iv.element_type_id = v_shadow_ele_type_id
and iv.name = 'Neg Earnings';
select iv.input_value_id
into v_addl_inpval_id
from pay_input_values_f iv
where iv.element_type_id = v_shadow_ele_type_id
and iv.name = 'Addl Amt';
select iv.input_value_id
into g_repl_inpval_id
from pay_input_values_f iv
where iv.element_type_id = v_shadow_ele_type_id
and iv.name = 'Replacement Amt';
SELECT balance_type_id
INTO v_earn401k_bal_type_id
FROM pay_balance_types
WHERE balance_name = 'Earnings 401k'
AND business_group_id IS NULL
AND legislation_code = 'US';
SELECT balance_type_id
INTO v_flsa_earnbal_id
FROM pay_balance_types
WHERE balance_name = 'FLSA Earnings'
AND business_group_id IS NULL
AND legislation_code = 'US';
SELECT balance_type_id
INTO v_flsa_hrsbal_id
FROM pay_balance_types
WHERE balance_name = 'FLSA Hours'
AND business_group_id IS NULL
AND legislation_code = 'US';
SELECT input_value_id
INTO v_hrs_inpval_id
FROM pay_input_values_f
WHERE element_type_id = v_ele_type_id
AND business_group_id = p_bg_id
AND name = 'Hours';
SELECT input_value_id
INTO v_hrs_inpval_id
FROM pay_input_values_f
WHERE element_type_id = v_ele_type_id
AND business_group_id = p_bg_id
AND name = 'Hours';
select balance_type_id
into v_reghrs_bal_id
from pay_balance_types
where balance_name = 'Regular Hours Worked'
and business_group_id is null
and legislation_code = 'US';
select balance_type_id
into v_reghrs_bal_id
from pay_balance_types
where balance_name = 'Regular Hours Worked'
and business_group_id is null
and legislation_code = 'US';
select balance_type_id
into v_reghrs_bal_id
from pay_balance_types
where upper(balance_name ) = 'REGULAR HOURS WORKED'
and business_group_id is null
and legislation_code = 'US';
select balance_type_id
into v_reghrs_bal_id
from pay_balance_types
where balance_name = 'Regular Salary Hours'
and business_group_id is null
and legislation_code = 'US';
select balance_type_id
into v_reghrs_bal_id
from pay_balance_types
where balance_name = 'Regular Wages Hours'
and business_group_id is null
and legislation_code = 'US';
update pay_element_types_f
set element_information13 = 'Y'
where element_type_id = v_ele_type_id;
END do_insertions;
SELECT *
FROM pay_all_earnings_types_v
WHERE element_type_id = p_ele_type_id
FOR UPDATE OF element_type_id NOWAIT;
SELECT input_value_id
FROM pay_input_values_f
WHERE element_type_id = p_ele_type_id;
SELECT formula_id
FROM pay_status_processing_rules_f
WHERE element_type_id = p_ele_type_id;
DELETE FROM ff_formulas_f
WHERE formula_id = v_ff_id;
DELETE FROM ff_fdi_usages_f
WHERE formula_id = v_ff_id;
DELETE FROM ff_compiled_info_f
WHERE formula_id = v_ff_id;
SELECT status_processing_rule_id
INTO v_spr_id
FROM pay_status_processing_rules_f
WHERE element_type_id = p_ele_type_id;
p_delete_mode => v_del_mode,
p_val_session_date => v_del_sess_date,
p_val_start_date => v_del_val_start,
p_val_end_date => v_del_val_end,
p_startup_mode => v_startup_mode);
DELETE FROM pay_formula_result_rules_f
WHERE input_value_id = v_inpval_id;
SELECT element_type_id,
processing_priority
INTO v_shadow_eletype_id,
v_shadow_ele_priority
FROM pay_element_types_f
WHERE element_name = SUBSTR(p_ele_name || ' Special Features', 1, 80)
AND business_group_id = p_business_group_id;
SELECT element_type_id,
processing_priority
INTO v_inputs_eletype_id,
v_inputs_ele_priority
FROM pay_element_types_f
WHERE element_name = SUBSTR(p_ele_name || ' Special Inputs', 1, 80)
AND business_group_id = p_business_group_id;
p_delete_mode => v_del_mode,
p_val_session_date => v_del_sess_date,
p_val_start_date => v_del_val_start,
p_val_end_date => v_del_val_end,
p_startup_mode => v_startup_mode);
delete from PAY_ELEMENT_TYPES_F
where element_type_id = p_ele_type_id;
delete from PAY_BALANCE_TYPES
where balance_type_id = fnd_number.canonical_to_number(p_ele_info_10);
DELETE FROM pay_balance_types
WHERE balance_type_id = fnd_number.canonical_to_number(p_ele_info_12);
select balance_type_id
into v_negearn_bal_type_id
from pay_balance_types
where balance_name = v_negearn_bal_name and
business_group_id + 0 = p_business_group_id ;
delete from PAY_BALANCE_TYPES
where balance_type_id = v_negearn_bal_type_id;
select balance_type_id
into v_addl_bal_type_id
from pay_balance_types
where balance_name = v_addl_bal_name and
business_group_id + 0 = p_business_group_id ;
delete from PAY_BALANCE_TYPES
where balance_type_id = v_addl_bal_type_id;
select balance_type_id
into v_repl_bal_type_id
from pay_balance_types
where balance_name = v_repl_bal_name and
business_group_id + 0 = p_business_group_id ;
delete from PAY_BALANCE_TYPES
where balance_type_id = v_repl_bal_type_id;
p_delete_mode => v_del_mode,
p_val_session_date => v_del_sess_date,
p_val_start_date => v_del_val_start,
p_val_end_date => v_del_val_end,
p_startup_mode => v_startup_mode);
delete from PAY_ELEMENT_TYPES_F
where element_type_id = v_shadow_eletype_id;
p_delete_mode => v_del_mode,
p_val_session_date => v_del_sess_date,
p_val_start_date => v_del_val_start,
p_val_end_date => v_del_val_end,
p_startup_mode => v_startup_mode);
delete from PAY_ELEMENT_TYPES_F
where element_type_id = v_inputs_eletype_id;