[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
04-NOV-93 hparicha 1.1 Added updates to SCL, inpval
defaulting (where applicable),
Adding locking and delete
procedures.
18-JAN-94 hparicha 40.19 p_ele_category is receiving
LOOKUP_CODE! Make appropriate
comparison for Section 125 and
Deferred Compensation.
(Been lax on this change list
- check the arcs log).
21-FEB-94 hparicha 40.24 G571 Changed 'COVERAGE_TYPE' to
'US_BENEFIT_COVERAGE'.
(Been lax on this change list
- check the arcs log).
03-JUN-94 hparicha 40.1 G815 Added "MIX Category" param
and DDF segment.
16-JUN-94 hparicha 40.2 G934
03-JUL-94 hparicha 40.3 Tidy up for 10G install.
13-JUL-94 hparicha 40.4 G907 New implementation of Earnings
and Deductions without use
of update recurring fres rules.
(aka BETA I freeze)
22-AUG-94 hparicha 40.5 G1241 Add new defined balances req'd
for view dedns screen.
Creating "Not Taken" inpval
on shadow whether or not
partial flag is Yes or No - ie.
we need to report dedns not
taken in either case. Same
for "Arrears Contr", just for
predictability and consistency.
Create "Not Taken" balance
type for Dedns Not Taken rpt.
Feed "Not Taken" bal by
Special Features "Not Taken"
inpval. Changed name of
"Towards Bond Purchase" to
"Toward...".
Update element type DDF with
associated balances.
26-SEP-94 hparicha 40.6 G1201 Add "_GRE_YTD" defined bal w/
Primary balance type - for
summary reporting.
Add deletion of "Not Taken" bal
and handle name change of
"...Towards Bond Purchase" to
"...Toward Bond Purchase" in
formula and deletion.
05-OCT-94 rfine 40.7 Changed calls to DB_PAY_SETUP
to PAY_DB_PAY_SETUP.
18-OCT-94 spanwar 40.8 Removed "PER_PTD" and "PER_LR"
balance dimension from
dimension list since it is no
longer supported.
40.9 changed '-Able' to '_Able' for
pretax dedn balance name.
21-OCT-94 spanwar 40.10 Removed "PER_LR".
24-NOV-94 rfine 40.11 Suppressed index on
business_group_id
05-DEC-94 hparicha 40.12 G1571 Added 'Payments' defbal for
primary balance.
21-DEC-94 hparicha 40.13 G1681 Clear/Adjust arrears fn'ality.
02-MAR-95 spanwar Added call to category feeder
in insert bal feeds section.
05-MAY-95 allee Added global session date to
the call to category feeder.
14-JUN-95 hparicha 40.17 286491 Deletion of all balances via
assoc bal ids held in
ELEMENT_INFORMATIONxx columns.
New params to "do_deletions".
16-JUN-95 hparicha 40.18 271622 Deletion of freq rule info.
29-JUN-95 hparicha 40.19 289319 Generate "Primary" balance
and feeds for "ER Liab" when
dedn is a benefit. Remember
to delete the bal and feeds!
Defined balances are
required for "_ASG_GRE_RUN/
PTD/MONTH/QTD/YTD" - and
also for PER and PER_GRE.
30-JUN-95 hparicha 40.20 Added "Court Order" input
value to garnishments.
Also, set new param to
create_element_type for
p_third_party_pay_only = Y
for garns.
30-JUN-95 hparicha 40.21 Add benefit classification id
to ele type update NO MATTER
WHAT the calculation method is.
03-AUG-95 hparicha 40.22 EE Bond Refund primary assoc
balance created, defbals and
feed also created.
?Ben class may have
?CONTRIBUTIONS_USED = 'N', in
?which case the formula must
?be altered NOT TO USE the
?dbi "...BEN_EE_CONTR_VALUE"
?and "...BEN_ER_CONTR_VALUE".
17-OCT-95 hparicha 40.25 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.26 333133 Added defined balances for "_GRE_RUN",
_GRE_YTD, _GRE_ITD to do_defined_balances
procedure. This allows for GRE-level
reporting of earnings and deductions.
09-JAN-96 mswanson 40.27 333133 Add defined bal for arrears deductions with
"_GRE_ITD" dimensions.
12-JAN-96 mswanson 40.28 Restrict qry on dimension by legislation code.
01-APR-96 hparicha 40.29 348658 Added element type id to Indirect formula
result rules enabling Formula Results screen
to display element name.
15-Jul-1996 hparicha 40.30 373543 Changing creation of pretax
deductions such that they are
calculated before taxes, yet withheld
after taxes and wage attachments.
Now calls new package to create
pretax dedns.
96/08/14 lwthomps 40.32 345102 Added the primary balance_id to post tax ER
elements.
16-Aug-1996 gpaytonm 40.33 Removed call to hr_generate_pretax.pretax_ded
uction_template which removes bug fix 373543
12-Sep-1996 hparicha 40.34 373543 calls hr_generate_pretax again.
05-Nov-1996 hparicha 40.35 413211 updated deletion procedure
to handle latest configuration -
esp. for pretax dedns...involved
addition of params to deletion
procedure. Also added DDF
associations on voluntary
deductions for special inputs
and special features elements,
additional, replacement and
ee bond refund balances.
07-Nov-1996 hparicha 40.36 413211 Deletion procedure
needs to cleanup OLD formulae
created during pretax upgrades.
08 Nov 1996 hparicha 40.37 Dimension name for _PER_GRE_RUN
reverted in generator code.
11-Jul-1997 mmukherj 40.38 502307 changed do_defined_balances procedure to check
whether record already exists in
pay_defined_balances table or not for that
business_group.Same changes has been made in
pywatgen.pkb(involuntary deduction) and
pygenptx.pkb(pretax deduction)
18-Feb-1998 mmukherj 40.39 566328 do_deletions procedure is modified to
include business_group_id in one a select
(without cursor) statement, which was
selecting more than one row for a given
element_name.
30-Apr-1998 pmadore 40.40 Added additonal input values, formula result
rules, elements, and balances to support the
Aftertax components of a pretax deduction in
category of Deferred Comp 401k. The logic to
create these objects depends upon the values
of two new parameters to the main package
function:
p_ele_er_match AND p_ele_at_component
18-Aug-1998 mmukherj 40.42 703234 Changed the procedure get_assoc_ele, set
default value of l_val as 'N', so that if
csr_sfx does not does not fetch any row
l_val passes the value 'N', instead of '',
which was creating problem in the form.
Because aftertax_component_flag and
employer_match_flag were not being set to
any value.
30 Dec 1998 mmukherj 110.4 787491 Entered business_group_id in all where
condition of the select statements of
do_deletions procedure.
NOTE:
Data used for certain inserts depend on the calculation method
selected. Calls to these procedures may be bundled in a procedure
that will handle putting together a logical set of calls - ie.
instead of repeating the same logic in each of the insert procedures,
the logic can be performed once and the appropriate calls made
immediately. The data involved includes input values, status
processing rules, formula result rules, and skip rules.
See ins_uie_formula below.
Also note, *could* make insertion (and validation) procedures
externally callable. Consider usefulness of such a design.
For All Future Bugfixes: Please use business group_id in new DML statements ,
whenever necessary, because we are allowing to create deduction with same name
for two different business groups - mmukherj.
01/08/1997 asasthan 110.5 773036 Added legislation code for US/Canada
interoperability.
07/29/1999 Rpotnuru 110.6 The variable v_notaken_bal_type_id was
used for Eligible comp and Over limit
balances were also created using the same
variable and because of this the system
is creating wrong balance feeds. So two
new variables for eleigible comp and
Over limit balance were created and used
accordingly. Added ASG_GRE_RUN dimension
to Arrears balance.
01/22/2002 ahanda 115.6 Added _ASG_PAYMENTS dimension
25-Mar-2002 ekim 115.8 2276457 Added p_termination_rule to
ins_deduction_template and update of
pay_element_types_f for
termination rule update
24-Jul-2002 ekim 115.9 changed v_bg_name to
per_business_groups.name%TYPE
23-DEC-2002 tclewis 115.10 11.5.9. performance changes.
27-DEC-2002 meshah 115.11 fixed gscc warnings.
01-APR-2003 ahanda 115.12 Changed the defined balance creation for
ASG_GRE_RUN to save run balance for Primary,
Not Taken, Arrears and Accrued Bal.
26-JUN-2003 ahanda 115.13 Changed call to create_balance_type procedure
to pass the balance name as reporting name.
Added code to populate 'After-Tax Deductions'
category for balances
07-JAN-2004 rsethupa 115.15 3349594 11.5.10 performance changes
08-JAN-2004 rsethupa 115.16 3349594 Removed extra comment added in 115.15 version
18-MAR-2004 kvsankar 115.17 3311781 Changed call to create_balance_type
procedure to pass the balance_category value
depending upon the classification of the
element instead of passing 'After-Tax Deductions'
for deduction elements.
20-MAY-2004 meshah 115.18 removed the logic not required for
Non-Recurring elements. Like creation of
additional and replacement defined balances.
21-MAY-2004 meshah 115.19 fixed gscc error File.Sql.2 and File.Sql.17
21-JUL-2004 schauhan 115.20 3613575 Added a table pay_element_types_f to a query
in procedure do_deletions to remove Merge Join Cartesian.
22-JUL-2004 schauhan 115.21 3613575 Added rownum<2 condition to the query modified in previous
version.
18-AUG-2004 sdhole 115.22 3651755 Removed balance category parameter for the
Eligible Comp balance.
23-JAN-2007 alikhar 115.23 5763867 Added code to populate element_information_category
115.24 for Voluntary Deduction ER shadow element with
amount type as Benefits Table.
21-JAN-2008 sudedas 115.25 6270794 Added Defined Balance for ' Accrued' Balance
with Dimension _ENTRY_ITD.
17-NOV-2008 sudedas 115.26 7535681 Procedure do_deletions is modified
to remove ' Refund' element/balance
when "EE Series Bond" is checked.
13-Nov-2011 emunisek 115.28 13484606 Replaced FF_FORMULAS_F.FORMULA_TEXT%TYPE
for variables based on formula_text column.
22-Dec-2011 emunisek 115.29 13512417 Attached Dimension '_ENTRY_ITD' to Primary Balance
of Deduction Element. Added '_ASG_PTD' Dimension
to Accrued Balance of Deduction Element. Added
new Input Value "Clear Earlier Accrual" to Deduction
Special Features element and this will feed the
Accrued Balance of Deduction Element.
*/
/*
---------------------------------------------------------------------
This package contains calls to core API used to insert records comprising an
entire deduction template.
The procedures responsible for creating
appropriate records based on data entered on the User-Initiated Earnings form
must perform simple logic to determine the exact attributes required for the
earnings template. Attributes (and their determining factors) are:
- skip rules (Class): will be determined during insert of ele type.
- calculation formulas (CalcMeth)
- status processing rules (CalcMeth)
- input values (Class/Cat, Calc Method)
- formula result rules (CalcMeth)
---------------------------------------------------------------------
*/
-- Controlling procedure that calls all insert procedures according to
-- locking ladder. May perform some simple logic. More involved logic
-- is handled inside various insertion procedures as required,
-- especially ins_uie_formula_processing.
--
-- 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.
-- Please Note: PL/SQL v1 does not support explicit naming
-- of parameters in procedure/function calls. Which only means
-- Forms 4 cannot call server-side PL/SQL using explicitly named parameters.
--
------------------------------- Insertions ------------------------------
--
-- Procedures to perform insertions of user-initiated earnings data:
-- Move inside ins_deduction_template.
--
------------------------- ins_deduction_template ------------------------
--
-- Move all other insert fns and procedures into here(?).
FUNCTION ins_deduction_template (
p_ele_name in varchar2,
p_ele_reporting_name in varchar2,
p_ele_description in varchar2 default NULL,
p_ele_classification in varchar2,
p_ben_class_id in number,
p_ele_category in varchar2 default NULL,
p_ele_processing_type in varchar2,
p_ele_priority in number default NULL,
p_ele_standard_link in varchar2 default 'N',
p_ele_proc_runtype in varchar2,
p_ele_start_rule in varchar2,
p_ele_stop_rule in varchar2,
p_ele_ee_bond in varchar2 default 'N',
p_ele_amount_rule in varchar2,
p_ele_paytab_name in varchar2 default NULL,
p_ele_paytab_col in varchar2 default NULL,
p_ele_paytab_row_type in varchar2 default NULL,
p_ele_arrearage in varchar2 default 'N',
p_ele_partial_dedn in varchar2 default 'N',
p_mix_flag in varchar2 default NULL,
p_ele_er_match in varchar2 default 'N',
p_ele_at_component in varchar2 default 'N',
p_ele_eff_start_date in date default NULL,
p_ele_eff_end_date in date default NULL,
p_bg_id in number,
p_termination_rule in varchar2 default 'F'
) RETURN NUMBER IS
-- global vars
-- Legislation Subgroup Code for all template elements.
g_template_leg_code VARCHAR2(30) := 'US';
v_ele_type_id NUMBER(9); -- 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_payval_id NUMBER(9); -- ID of payval for bal feed insert.
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.
SELECT /*+ no_merge(pbf) */
bc.CLASSIFICATION_ID, pbf.BUSINESS_GROUP_ID,
pbf.LEGISLATION_CODE, pbf.BALANCE_TYPE_ID,
pbf.INPUT_VALUE_ID, pbf.SCALE, pbf.ELEMENT_TYPE_ID
FROM PAY_BALANCE_FEEDS_V pbf,
pay_balance_classifications bc
WHERE NVL(pbf.BALANCE_INITIALIZATION_FLAG,'N') = 'N'
AND ((pbf.BUSINESS_GROUP_ID IS NULL OR pbf.BUSINESS_GROUP_ID = p_bg_id)
AND (pbf.LEGISLATION_CODE IS NULL OR pbf.LEGISLATION_CODE = 'US'))
and (pbf.BALANCE_NAME = 'Regular Earnings')
and bc.balance_type_id = pbf.balance_type_id
order by pbf.element_name;
UPDATE pay_input_values_f
SET mandatory_flag = 'X'
WHERE element_type_id = ret
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 = 'SYSTEM_DEDN_CALC_FORMULA'
AND FF.business_group_id IS NULL
AND FF.legislation_code = 'US'
AND FF.formula_id >= 0 --Bug 3349594
AND g_eff_start_date between FF.effective_start_date
AND FF.effective_end_date;
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 = 'SYSTEM_DEDN_CALC_NR_FORMULA'
AND FF.business_group_id IS NULL
AND FF.legislation_code = 'US'
AND FF.formula_id >= 0 --Bug 3349594
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 = g_ele_info_cat,
element_information1 = p_ele_cat,
element_information2 = p_partial_dedn,
element_information3 = p_proc_runtype,
element_information9 = p_mix_category,
benefit_classification_id = p_ben_class_id
WHERE element_type_id = p_ele_id
AND business_group_id + 0 = p_bg_id;
UPDATE pay_element_types_f
SET element_information_category = g_ele_info_cat,
element_information1 = p_ele_cat,
element_information2 = p_partial_dedn,
element_information3 = p_proc_runtype
WHERE element_type_id = p_shadow_ele_id
AND business_group_id + 0 = p_bg_id;
UPDATE pay_element_types_f
SET element_information_category = g_ele_info_cat,
element_information1 = p_ele_cat,
element_information2 = p_partial_dedn,
element_information3 = p_proc_runtype
WHERE element_type_id = p_inputs_ele_id
AND business_group_id + 0 = 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 => v_dflt_value,
p_lookup_type => v_lkp_type,
p_formula_id => v_val_formula_id,
p_generate_db_items_flag => v_gen_dbi,
p_warning_or_error => NULL);
SELECT user_table_id
INTO v_paytab_id
FROM pay_user_tables
WHERE UPPER(user_table_name) = UPPER(p_paytab_name)
AND NVL(business_group_id, p_bg_id) = p_bg_id;
SELECT lookup_code
INTO v_row_code
FROM hr_lookups
WHERE UPPER(meaning) = UPPER(p_paytab_row_type)
AND lookup_type = 'US_TABLE_ROW_TYPES';
UPDATE pay_element_types_f
SET element_information_category = g_ele_info_cat,
element_information1 = p_ele_cat,
element_information2 = p_partial_dedn,
element_information3 = p_proc_runtype,
element_information6 = p_paytab_name,
element_information7 = v_row_code,
element_information9 = p_mix_category,
benefit_classification_id = p_ben_class_id
WHERE element_type_id = p_ele_id
AND business_group_id + 0 = p_bg_id;
UPDATE pay_element_types_f
SET element_information_category = g_ele_info_cat,
element_information1 = p_ele_cat,
element_information2 = p_partial_dedn,
element_information3 = p_proc_runtype,
element_information6 = p_paytab_name,
element_information7 = v_row_code
WHERE element_type_id = p_shadow_ele_id
AND business_group_id + 0 = p_bg_id;
UPDATE pay_element_types_f
SET element_information_category = g_ele_info_cat,
element_information1 = p_ele_cat,
element_information2 = p_partial_dedn,
element_information3 = p_proc_runtype,
element_information6 = p_paytab_name,
element_information7 = v_row_code
WHERE element_type_id = p_inputs_ele_id
AND business_group_id + 0 = 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 => v_dflt_value,
p_lookup_type => v_lkp_type,
p_formula_id => v_val_formula_id,
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 => v_dflt_value,
p_lookup_type => v_lkp_type,
p_formula_id => v_val_formula_id,
p_generate_db_items_flag => v_gen_dbi,
p_warning_or_error => NULL);
UPDATE pay_element_types_f
SET element_information_category = g_ele_info_cat,
element_information1 = p_ele_cat,
element_information2 = p_partial_dedn,
element_information3 = p_proc_runtype,
element_information9 = p_mix_category,
benefit_classification_id = p_ben_class_id
WHERE element_type_id = p_ele_id
AND business_group_id + 0 = p_bg_id;
UPDATE pay_element_types_f
SET element_information_category = g_ele_info_cat,
element_information1 = p_ele_cat,
element_information2 = p_partial_dedn,
element_information3 = p_proc_runtype
WHERE element_type_id = p_shadow_ele_id
AND business_group_id + 0 = p_bg_id;
UPDATE pay_element_types_f
SET element_information_category = g_ele_info_cat,
element_information1 = p_ele_cat,
element_information2 = p_partial_dedn,
element_information3 = p_proc_runtype
WHERE element_type_id = p_inputs_ele_id
AND business_group_id + 0 = 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 => 'Y',
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 => v_val_formula_id,
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 => v_dflt_value,
p_lookup_type => v_lkp_type,
p_formula_id => v_val_formula_id,
p_generate_db_items_flag => v_gen_dbi,
p_warning_or_error => NULL);
p_insert_update_flag => 'UPDATE',
p_input_value_id => v_er_contr_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 => v_val_formula_id,
p_generate_db_items_flag => v_gen_dbi,
p_warning_or_error => NULL);
SELECT IV.input_value_id
INTO v_er_payval_id
FROM pay_input_values_f IV
WHERE IV.element_type_id = p_er_charge_eletype_id
AND IV.name = v_payval_name;
UPDATE pay_element_types_f
SET element_information_category = g_ele_info_cat,
element_information1 = p_ele_cat,
element_information2 = p_partial_dedn,
element_information3 = p_proc_runtype,
element_information9 = p_mix_category,
benefit_classification_id = p_ben_class_id
WHERE element_type_id = p_ele_id
AND business_group_id + 0 = p_bg_id;
UPDATE pay_element_types_f
SET element_information_category = g_ele_info_cat,
element_information1 = p_ele_cat,
element_information2 = p_partial_dedn,
element_information3 = p_proc_runtype
WHERE element_type_id = p_shadow_ele_id
AND business_group_id + 0 = p_bg_id;
UPDATE pay_element_types_f
SET element_information_category = g_ele_info_cat,
element_information1 = p_ele_cat,
element_information2 = p_partial_dedn,
element_information3 = p_proc_runtype
WHERE element_type_id = p_inputs_ele_id
AND business_group_id + 0 = 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 => v_dflt_value,
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_actdedn_inpval_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 => v_inpval_name,
p_uom => v_inpval_uom,
p_min_value => NULL,
p_max_value => NULL,
p_default_value => NULL,
p_lookup_type => v_dflt_value,
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 => v_dflt_value,
p_lookup_type => v_lkp_type,
p_formula_id => v_val_formula_id,
p_generate_db_items_flag => v_gen_dbi,
p_warning_or_error => NULL);
p_insert_update_flag => 'UPDATE',
p_input_value_id => g_topurch_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 => v_dflt_value,
p_lookup_type => v_lkp_type,
p_formula_id => v_val_formula_id,
p_generate_db_items_flag => v_gen_dbi,
p_warning_or_error => NULL);
SELECT inp.input_value_id
INTO v_eerefund_payval_id
FROM pay_input_values_f inp,
hr_lookups hl
WHERE inp.element_type_id = p_eerefund_eletype_id
AND inp.name = hl.meaning
AND hl.lookup_code = 'PAY VALUE'
AND hl.lookup_type = 'NAME_TRANSLATIONS';
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 => v_val_formula_id,
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 => v_dflt_value,
p_lookup_type => v_lkp_type,
p_formula_id => v_val_formula_id,
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 => v_dflt_value,
p_lookup_type => v_lkp_type,
p_formula_id => v_val_formula_id,
p_generate_db_items_flag => v_gen_dbi,
p_warning_or_error => NULL);
p_insert_update_flag => 'UPDATE',
p_input_value_id => g_to_tot_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 => v_val_formula_id,
p_generate_db_items_flag => v_gen_dbi,
p_warning_or_error => NULL);
p_insert_update_flag => 'UPDATE',
p_input_value_id => g_clraccr_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 => v_val_formula_id,
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 => v_dflt_value,
p_lookup_type => v_lkp_type,
p_formula_id => v_val_formula_id,
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 => v_dflt_value,
p_lookup_type => v_lkp_type,
p_formula_id => v_val_formula_id,
p_generate_db_items_flag => v_gen_dbi,
p_warning_or_error => NULL);
p_insert_update_flag => 'UPDATE',
p_input_value_id => g_arrears_contr_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 => v_dflt_value,
p_lookup_type => v_lkp_type,
p_formula_id => v_val_formula_id,
p_generate_db_items_flag => v_gen_dbi,
p_warning_or_error => NULL);
p_insert_update_flag => 'UPDATE',
p_input_value_id => g_notaken_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 => v_dflt_value,
p_lookup_type => v_lkp_type,
p_formula_id => v_val_formula_id,
p_generate_db_items_flag => v_gen_dbi,
p_warning_or_error => NULL);
p_insert_update_flag => 'UPDATE',
p_input_value_id => v_passthru_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 => v_dflt_value,
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 => v_dflt_value,
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 => v_dflt_value,
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_adj_arrears_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 => v_dflt_value,
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 => v_dflt_value,
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 => v_dflt_value,
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 business_group_id
into v_business_group_id
from per_business_groups
where upper(name) = upper(p_bg_name);
select dimension_name, balance_dimension_id
into dim_name, dim_id
from pay_balance_dimensions
where database_item_suffix = suffixes(i)
and legislation_code = g_template_leg_code
and business_group_id is null;
/* the following select statement has been commented. Earlier it was not
checking whether record already exists in pay_defined_balance or not. Now
it is checking it and for a particular business_group_id.
*/
SELECT count(0)
INTO already_exists
FROM pay_defined_balances db,
pay_balance_types bt
WHERE db.balance_type_id = bt.balance_type_id
AND bt.balance_name = p_bal_name
AND db.balance_dimension_id = dim_id
AND bt.business_group_id = v_business_group_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 = 'CHAINED_SKIP_FORMULA'
AND FF.business_group_id IS NULL
-- added legislation_code asasthan
AND FF.legislation_code = 'US'
AND p_ele_eff_start_date >= FF.effective_start_date
AND p_ele_eff_start_date <= FF.effective_end_date
AND FF.formula_id >= 0; --Bug 3349594
SELECT FF.formula_id
INTO v_skip_formula_id
FROM ff_formulas_f FF
WHERE FF.formula_name = 'THRESHOLD_SKIP_FORMULA'
AND FF.business_group_id IS NULL
-- added legislation code asasthan
AND FF.legislation_code = 'US'
AND p_ele_eff_start_date >= FF.effective_start_date
AND p_ele_eff_start_date <= FF.effective_end_date
AND FF.formula_id >= 0; --Bug 3349594
SELECT FF.formula_id
INTO v_skip_formula_id
FROM ff_formulas_f FF
WHERE FF.formula_name = 'FREQ_RULE_SKIP_FORMULA'
AND FF.legislation_code = 'US'
AND FF.business_group_id IS NULL
AND p_ele_eff_start_date >= FF.effective_start_date
AND p_ele_eff_start_date <= FF.effective_end_date
AND FF.formula_id >= 0; --Bug 3349594
select distinct(classification_id)
into v_primary_class_id
from pay_element_types_f
where element_type_id = v_ele_type_id;
UPDATE pay_element_types_f
SET post_termination_rule = p_termination_rule
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;
UPDATE pay_element_types_f
SET post_termination_rule = p_termination_rule
WHERE element_type_id = v_inputs_ele_type_id;
UPDATE pay_element_types_f
SET post_termination_rule = p_termination_rule
WHERE element_type_id = v_shadow_ele_type_id;
SELECT IV.input_value_id
INTO v_payval_id
FROM pay_input_values_f IV
WHERE IV.element_type_id = v_er_charge_eletype_id
AND IV.name = v_payval_name;
UPDATE pay_element_types_f
SET ELEMENT_INFORMATION10 = v_er_charge_baltype_id
WHERE element_type_id = v_er_charge_eletype_id;
UPDATE pay_element_types_f
SET element_information_category = 'US_EMPLOYER LIABILITIES',
element_information1 = 'B'
WHERE element_type_id = v_er_charge_eletype_id
AND business_group_id + 0 = p_bg_id;
UPDATE pay_element_types_f
SET ELEMENT_INFORMATION10 = v_eerefund_baltype_id
WHERE element_type_id = v_eerefund_eletype_id;
SELECT IV.input_value_id
INTO v_payval_id
FROM pay_input_values_f IV
WHERE IV.element_type_id = v_eerefund_eletype_id
AND IV.name = v_payval_name;
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.name = v_payval_name;
UPDATE pay_element_types_f
SET element_information10 = v_bal_type_id,
element_information11 = v_totowed_bal_type_id,
element_information12 = v_arrears_bal_type_id,
element_information13 = v_notaken_bal_type_id,
element_information14 = v_eepurch_bal_type_id,
element_information16 = v_addl_amt_bal_type_id,
element_information17 = v_repl_amt_bal_type_id,
element_information18 = v_inputs_ele_type_id,
element_information19 = v_shadow_ele_type_id
WHERE element_type_id = v_ele_type_id
AND business_group_id + 0 = p_bg_id;
UPDATE pay_element_types_f
SET element_information10 = v_bal_type_id,
element_information13 = v_notaken_bal_type_id,
element_information19 = v_shadow_ele_type_id
WHERE element_type_id = v_ele_type_id
AND business_group_id + 0 = p_bg_id;
SELECT *
FROM pay_all_deduction_types_v
WHERE element_type_id = p_ele_type_id;
/* Bug 787491: All select statements are using business_group_id in where
condition */
PROCEDURE do_deletions (p_business_group_id in number,
p_ele_type_id in number,
p_ele_name in varchar2,
p_ele_priority in number,
p_ele_amount_rule in varchar2,
p_ele_ee_bond in varchar2,
p_ele_arrearage in varchar2,
p_ele_stop_rule in varchar2,
p_ele_info_10 in varchar2 default null,
p_ele_info_11 in varchar2 default null,
p_ele_info_12 in varchar2 default null,
p_ele_info_13 in varchar2 default null,
p_ele_info_14 in varchar2 default null,
p_ele_info_15 in varchar2 default null,
p_ele_info_16 in varchar2 default null,
p_ele_info_17 in varchar2 default null,
p_ele_info_18 in varchar2 default null,
p_ele_info_19 in varchar2 default null,
p_ele_info_20 in varchar2 default null,
p_del_sess_date in date,
p_del_val_start_date in date,
p_del_val_end_date in date) IS
-- local constants
c_end_of_time CONSTANT DATE := TO_DATE('31/12/4712','DD/MM/YYYY');
SELECT ele_payroll_freq_rule_id
FROM pay_ele_payroll_freq_rules
WHERE element_type_id = p_ele_type_id
AND business_group_id = p_business_group_id;
SELECT distinct ff.formula_id
FROM pay_status_processing_rules_f spr, ff_formulas_f ff
WHERE spr.element_type_id = l_ele_id
AND ff.formula_id = spr.formula_id
AND ff.business_group_id = p_business_group_id;
SELECT distinct ff.formula_id
FROM ff_formulas_f ff
WHERE ff.formula_name like upper('OLD%'||p_ele_name||'_FLAT%')
OR ff.formula_name like upper('OLD%'||p_ele_name||'_PERCENT%')
OR ff.formula_name like upper('OLD%'||p_ele_name||'_BENEFIT%')
OR ff.formula_name like upper('OLD%'||p_ele_name||'_PAYROLL%')
OR ff.formula_name like upper('OLD%'||p_ele_name||'_WITHHOLDING')
AND ff.business_group_id = p_business_group_id;
SELECT distinct status_processing_rule_id
FROM pay_status_processing_rules_f
WHERE element_type_id = l_ele_id
AND business_group_id = p_business_group_id;
select employer_match_flag, aftertax_component_flag, classification_name
into v_employer_match_flag, v_after_tax_flag, v_class_name
from pay_all_deduction_types_v
where element_type_id = p_ele_type_id
and v_del_sess_date >= effective_start_date
and v_del_sess_date <= effective_end_date
and business_group_id + 0 = p_business_group_id ;
select balance_type_id
into v_not_taken_baltype_id
from pay_balance_types
where balance_name like p_ele_name||' Not Taken'
and business_group_id + 0 = p_business_group_id ;
select balance_type_id
into v_elig_bal_id
from pay_balance_types
where balance_name like p_ele_name||' Eligible Comp'
and business_group_id + 0 = p_business_group_id ;
select element_type_id
into v_employer_match_id
from pay_element_types_f
where element_name like p_ele_name||' ER'
and v_del_sess_date >= effective_start_date
and v_del_sess_date <= effective_end_date
and business_group_id + 0 = p_business_group_id ;
select element_type_id
into v_after_tax_id
from pay_element_types_f
where element_name like p_ele_name||' AT'
AND v_del_sess_date >= effective_start_date
AND v_del_sess_date <= effective_end_date
and business_group_id + 0 = p_business_group_id;
select element_type_id
into v_after_tax_si_id
from pay_element_types_f
where element_name like p_ele_name||' AT Special Inputs'
AND v_del_sess_date >= effective_start_date
AND v_del_sess_date <= effective_end_date
and business_group_id + 0 = p_business_group_id;
select element_type_id
into v_after_tax_sf_id
from pay_element_types_f
where element_name like p_ele_name||' AT Special Features'
AND v_del_sess_date >= effective_start_date
AND v_del_sess_date <= effective_end_date
and business_group_id + 0 = p_business_group_id;
select primary_baltype_id, accrued_baltype_id, arrears_baltype_id,
not_taken_baltype_id, tobondpurch_baltype_id,
additional_baltype_id, replacement_baltype_id
into v_at_bal_id, v_at_accr_id, v_at_arr_id, v_at_not_taken_id,
v_at_to_bond_id, v_at_addl_id, v_at_repl_id
from pay_all_deduction_types_v padt,
pay_element_types_f pet
WHERE pet.element_type_id = padt.element_type_id
AND padt.element_name like p_ele_name||' AT'
AND v_del_sess_date >= padt.effective_start_date
AND v_del_sess_date <= padt.effective_end_date
AND padt.business_group_id + 0 = p_business_group_id
AND rownum<2;
select balance_type_id
into v_at_not_taken_id
from pay_balance_types
where balance_name like p_ele_name||' AT Not Taken'
and business_group_id + 0 = p_business_group_id ;
select balance_type_id
into v_at_elig_id
from pay_balance_types
where balance_name like p_ele_name||' AT Eligible Comp'
and business_group_id + 0 = p_business_group_id ;
select balance_type_id
into v_at_overlimit_id
from pay_balance_types
where balance_name like p_ele_name||' AT Overlimit'
and business_group_id + 0 = p_business_group_id ;
select element_type_id
into v_at_er_id
from pay_element_types_f
where element_name like p_ele_name||' AT ER'
AND v_del_sess_date >= effective_start_date
AND v_del_sess_date <= effective_end_date
and business_group_id + 0 = p_business_group_id ;
select balance_type_id
into v_at_er_bal_id
from pay_balance_types
where balance_name like p_ele_name||' AT ER'
and business_group_id + 0 = p_business_group_id ;
select balance_type_id
into v_overlimit_bal_id
from pay_balance_types
where balance_name like p_ele_name||' Overlimit'
and business_group_id + 0 = p_business_group_id ;
SELECT balance_type_id
INTO v_refund_bal_id
FROM pay_balance_types
WHERE balance_name like p_ele_name||' Refund'
AND business_group_id + 0 = p_business_group_id ;
SELECT element_type_id
INTO v_refund_ele_id
FROM pay_element_types_f
WHERE element_name like p_ele_name||' Refund'
AND v_del_sess_date >= effective_start_date
AND v_del_sess_date <= effective_end_date
AND business_group_id + 0 = p_business_group_id;
DELETE FROM pay_freq_rule_periods
WHERE ele_payroll_freq_rule_id = v_freqrule_id;
DELETE FROM pay_ele_payroll_freq_rules
WHERE ele_payroll_freq_rule_id = v_freqrule_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;
hr_utility.set_location('hr_us_garn_gen.delete_dedn', 10);
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 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;
hr_utility.set_location('hr_us_garn_gen.delete_dedn', 50);
hr_utility.set_location('hr_us_garn_gen.delete_dedn', 60);
DELETE FROM pay_balance_types
WHERE balance_type_id = assoc_bals(i);
select processing_priority
into v_assoc_ele_priority
from pay_element_types_f
where element_type_id = assoc_eles(j)
and v_del_sess_date between effective_start_date
and effective_end_date;
hr_utility.set_location('hr_us_garn_gen.delete_dedn', 20);
hr_utility.set_location('hr_us_garn_gen.delete_dedn', 30);
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);
hr_utility.set_location('hr_us_garn_gen.delete_dedn', 35);
delete from PAY_ELEMENT_TYPES_F
where element_type_id = assoc_eles(j);
SELECT DISTINCT balance_type_id
INTO v_bal_type_id
FROM pay_balance_types
WHERE balance_name = p_ele_name || ' ER' --Bug 3349594
and business_group_id + 0 = p_business_group_id ;
delete from PAY_BALANCE_TYPES
where balance_type_id = v_bal_type_id;
/** Bug 566328: The select statement below is modified to put business group id
in the where clause. Because now it is allowing to enter deduction with
same name in different business groups( ref. Bug 502307), selection only by
element name will fetch more than one row and raise error. **/
SELECT DISTINCT element_type_id
INTO v_eletype_id
FROM pay_element_types_f
WHERE element_name = p_ele_name || ' ER' --Bug 3349594
AND v_del_sess_date >= effective_start_date
AND v_del_sess_date <= effective_end_date
and business_group_id + 0 = 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 = v_eletype_id;
SELECT DISTINCT balance_type_id
INTO v_bal_type_id
FROM pay_balance_types
WHERE balance_name = p_ele_name || ' REFUND' --Bug 3349594
and business_group_id + 0 = p_business_group_id ;
delete from PAY_BALANCE_TYPES
where balance_type_id = v_bal_type_id;
/** Bug 566328: The select statement below is modified to put business group id
in the where clause. Because now it is allowing to enter deduction with
same name in different business groups( ref. Bug 502307), selection only by
element name will fetch more than one row and raise error. **/
hr_utility.set_location('hr_user_init_dedn.do_deletions', 17);
SELECT DISTINCT element_type_id
INTO v_eletype_id
FROM pay_element_types_f
WHERE element_name = p_ele_name || ' REFUND' --Bug 3349594
AND v_del_sess_date >= effective_start_date
AND v_del_sess_date <= effective_end_date
and business_group_id + 0 = 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 = v_eletype_id;
SELECT decode(x.element_name,null,'N','Y')
FROM pay_element_types x, pay_element_types b
WHERE b.element_type_id = p_ele_type_id
AND b.business_group_id + 0 = p_bg_id
AND p_eff_start_date between b.effective_start_date
AND b.effective_end_date
AND x.business_group_id + 0 = p_bg_id
AND x.effective_start_date between b.effective_start_date
AND b.effective_end_date
AND b.element_name||' '||p_suffix = x.element_name(+);