[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
5th Nov 1996 hparicha 40.19 413211. Updated deletion procedure
with respect to latest configuration.
7th Nov 1996 hparicha 40.20 413211 - again...deletion procedure needs to
cleanup OLD formulae creating during upgrades.
10th July 1997 mmukherj 40.21 502307 Updated do_defined_balances
procedure.Included business_group_id
in where condition while checking
pay_defined_balnce has already exist
for that balance_name for that busines_
group_id or not
21st July 1997 mmukherj 40.22 Put Version No in the comment above (
10th July 1997) and added comment to
do_defined_balance procedure.
Changed the select statement of the
same proceduerto avoid using index on
business_group_id.
7th Nov 1997 rpanchap 40.23 459552 Updated the function create_garnishment
to Populate the text table, garn_base_iv_names,
based on 2 categories (Support and
the rest of the categories grouped as one)
9th Jan 1998 mmukherj 40.24 566328 delete_dedn 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.
14th Jul 1998 ssarma 40.25 625442 Fee indirect result from calculator
formula now feeds the fee element
instead of the verifier element
incase of non-support invlountary
deductions.
03rd Nov 1998 ssarma 40.26 658479 Added run result Calc_Subprio to all associated
elements so that subpriority can be processed.
Changed the processing priority of all Garnishment
elements so that CS/AY/SS and TL process at the
highest priority based on date served. followed by
BO,CD/G,EL and ER.
01 Dec 1998 ssarma 40.27 658479 Changed priority for Tax levy
calculation element to be equal to that of support
calculator elements. Added formula result rules
of Garn_fee in calculator. Added Diff_dedn_amt
and Diff_fee_amt formula result rules to verifier
element. Added delete stmt to delete formula
result rules from pay_formula_result_rules_f
so that no unnecessary result rules are hanging !
04 Dec 1998 ssarma 40.28 774717 Added back dedn at time of writ
to the tax levy element.
11 Dec 1998 ssarma 40.29 771631 Changed the processing priority of the tax
calculator element to be equal with the support
verifier element.
08-FEB-1999 ssarma 40.36 Max Per Period input value added in the base element
and corresponging input values/FRR to indirect elmt.
05-MAR-1999 ahanda 40.37 Added dimension for assignment Inception to Date.
Also changed the priority for Special Inputs for
garnishment and edu. loan to process before
base element.
23-SEP-1999 ssarma 40.39 Made change from = to like for old formula
select statement.
21-APR-2000 fusman 40.43 New PTD Fee Balance,Month Fee Balance and
Accrued Fee Balance are created.
For Garnishment: Acc_Fee=>Garn.Cal=>
Spl.Fee. PTD,Month=>Calculator
For Child.Supp
Acc_Fee=>Chi.Sup.Cal=>Prio=>Verif=>
Spl.Features PTD,Month=>Chi.Sup.Cal
=>Prio=>Verif.
21-APR-2000 fusman 40.44 Changed the suffixes from database_item
_suffixes to dimension names as names
have index which will speed up
the process when creating the balances.
24-APR-2000 fusman 40.45 Corrected the Dimension name
Assignment-Level Current Run.
24-APR-2000 fusman 40.46 Removed the balances PTD and Month since
only Accrued balance is used in two
dimensions
03-MAY-2000 fusman 115.17 Changed the dates to fnd.canonical format.
24-Oct-2000 fusman 115.18 New dimensions PTD,ITD and Month has been
included.
27-Dec-2000 fusman 115.19 1348004 Changed the priority of EL same as G,CD.
1498260 Added date check in the select stmt
of input_value_id
03-Jan-2001 fusman 115.20 Added Compile_flag 'N' for Old formulas.
22-JAN-2002 ahanda 115.21 Added creation of _ASG_PAYMENTS defined
balance ID.
19-Jul-2002 ekim 115.23 Added balance feed to 'Child Supp Total
Amount' for bug 2374248.
21-Aug-2002 ekim 115.24 Chaged the formula result rule of the
Calculator element. The result of
to_addl, and to_repl should feed Special
Features element not Special Inputs
element.
22-Aug-2002 ekim 115.25 2527761 Commented update of element_information12
and element_information13 to keep bug
980683 fix and remove setting not_taken
to 0 in the calculator formula
pyusgarnfedlycal.hdt for other wage attach
and verifier formula pyusgarnchsupver
for support.
Added following balances for Special
Features element with IV of Not Taken
will feed to this balance.
Support Not Taken Amount
Other Garn Not Taken Amount
12-sep-2002 ekim 115.27 Removed elisa from trace message.
08-Oct-2002 ekim 115.28 2603525 Changed UOM to pass lookup code
instead of meaning. (ex.M not Money)
Changed to use p_uom_code to call
pay_db_pay_setup.create_input_value
and create_balance_type instead
of p_uom.
10-Oct-2002 ekim 115.29 Removed default value from delete_dedn
for GSCC warning.
07-Jan-2003 ekim 115.30 Made performance change for the query
which gets l_count_already.
Bug 2721714.
01-APR-2003 ahanda 115.31 Fixed the issue with the select stmt
changed for bug 2721714.
01-APR-2003 ahanda 115.32 Changed the defined balance creation call
to store _ASG_GRE_RUN as run balance.
26-JUN-2003 ahanda 115.33 Changed call to create_balance_type procedure
to pass the balance name
Added code to populate 'After-Tax Deductions'
category for balances
18-MAR-2004 kvsankar 115.34 3311781 Changed call to create_balance_type procedure
to pass the balance category as
'Involuntary Deductions' instead of
'After-Tax Deductions'.
09-JUN-2004 kvsankar 115.35 3622290 Changed the delete_dedn procedure to
delete the elements based on the new
architecture for Involuntary deductions
element. The procedure does delete
elements created using the old
architecture.
17-JUN-2004 kvsankar 115.36 3682501 Added code for deleting the template
created while creating an Involuntary
Deduction element in the new
architecture. Also modified the
delete_dedn procedure to take into
account 'Arrears' and 'Not Taken'
balances for deletion.
18-AUG-2004 sdhole 115.37 3651755 Removed the balance category for the
following balances Additional, Replacement
Accrued Fees, Vol Dedns.
30-JAN-2006 kvsankar 115.38 4680388 Modified the delete_dedn procedure
to use delete_user_structure for
deleting elements created using
template engine
***************************************************************************/
FUNCTION create_garnishment (
p_garn_name IN VARCHAR2,
p_garn_reporting_name IN VARCHAR2,
p_garn_description IN VARCHAR2,
p_category IN VARCHAR2,
p_bg_id IN NUMBER,
p_ele_eff_start_date IN DATE) RETURN NUMBER IS
c_end_of_time CONSTANT DATE := fnd_date.canonical_to_date('4712/12/31 00:00:00');
select bt.balance_type_id,
bc.scale
from pay_balance_types bt,
pay_balance_classifications bc,
pay_element_classifications ec
where bt.balance_type_id = bc.balance_type_id
and bc.classification_id = ec.classification_id
and nvl(bc.business_group_id, p_bg_id) + 0 = p_bg_id
and ec.classification_name = 'Involuntary Deductions'
and ec.legislation_code = 'US'
order by bt.balance_name;
update pay_element_types_f
set processing_priority = p_ele_priority
where element_type_id = ret
and business_group_id + 0 = p_bg_id;
select dimension_name, balance_dimension_id
into dim_name, dim_id
from pay_balance_dimensions
where dimension_name = x_dimension_name(i)
and legislation_code = g_template_leg_code;
/* added line to include business_group_id in the where clause of the select
statement below. So that it checkes the existence of data for a the given
business_group_id Bug No: 502307.
*/
hr_utility.trace('Defined Balance for '||p_bal_name||suffixes(i));
SELECT count(0)
INTO already_exists
FROM pay_defined_balances db
WHERE db.balance_type_id = p_bal_id
AND db.balance_dimension_id = dim_id
AND db.business_group_id + 0 = p_bg_id;
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 = 'BALANCE_SETUP_FORMULA'
AND FF.business_group_id IS NULL
AND FF.legislation_code = 'US'
AND g_eff_start_date >= FF.effective_start_date
AND g_eff_start_date <= FF.effective_end_date;
SELECT ff_formulas_s.nextval
INTO v_new_ele_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_new_ele_formula_id,
g_eff_start_date,
g_eff_end_date,
p_ff_bg_id,
NULL,
v_skeleton_formula_type_id,
v_new_ele_formula_name,
v_ff_desc,
v_new_ele_formula_text,
'N',
NULL,
NULL,
NULL,
-1,
g_eff_start_date);
UPDATE ff_formulas_f
SET formula_text = v_new_ele_formula_text
WHERE formula_id = v_new_ele_formula_id
AND business_group_id+0 = p_ff_bg_id
AND g_eff_start_date BETWEEN effective_start_date
AND effective_end_date;
select count(0)
into l_count_already
from ff_formulas_f fff, ff_formula_types ffft
where (upper(formula_name) like upper('OLD%_'||v_orig_ele_formula_name) or
upper(formula_name) like upper(v_orig_ele_formula_name) or
upper(formula_name) like upper('%'||v_orig_ele_formula_name)||'_EXP' or
upper(formula_name) like upper('%'||v_orig_ele_formula_name)||'_OLD%' )
and business_group_id+0 = p_ff_bg_id
and fff.legislation_code = 'US'
and ffft.formula_type_name = 'Oracle Payroll'
and ffft.formula_type_id = fff.formula_type_id;
SELECT ff_formulas_s.nextval
INTO v_orig_ele_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,
COMPILE_FLAG)
values (
v_orig_ele_formula_id,
g_eff_start_date,
g_eff_end_date,
p_ff_bg_id,
NULL,
v_skeleton_formula_type_id,
v_orig_ele_formula_name,
v_ff_desc,
v_orig_ele_formula_text,
'N',
NULL,
NULL,
NULL,
-1,
g_eff_start_date,
'N');
select name
into v_bg_name
from per_business_groups
where business_group_id + 0 = p_bg_id;
select classification_id,
default_priority
into g_ele_class_id,
g_ele_priority
from pay_element_classifications
where UPPER(classification_name) = g_ele_classification
and legislation_code = 'US';
SELECT FF.formula_id
INTO g_skip_formula_id
FROM ff_formulas_f FF
WHERE FF.formula_name = 'WAT_SKIP'
AND FF.business_group_id IS NULL
AND legislation_code = 'US'
AND p_ele_eff_start_date >= FF.effective_start_date
AND p_ele_eff_start_date <= FF.effective_end_date;
SELECT balance_type_id
INTO g_voldedns_balance_id
FROM pay_balance_types
WHERE balance_name = 'Voluntary Deductions'
AND business_group_id IS NULL
AND legislation_code = 'US';
SELECT balance_type_id
INTO g_total_dedns_balance_id
FROM pay_balance_types
WHERE balance_name = 'Garn Total Dedns'
AND business_group_id IS NULL
AND legislation_code = 'US';
SELECT balance_type_id
INTO g_total_fees_balance_id
FROM pay_balance_types
WHERE balance_name = 'Garn Total Fees'
AND business_group_id IS NULL
AND legislation_code = 'US';
SELECT balance_type_id
INTO g_other_not_taken_bal_id
FROM pay_balance_types
WHERE balance_name = 'Other Garn Not Taken Amount'
AND business_group_id IS NULL
AND legislation_code = 'US';
SELECT balance_type_id
INTO g_tax_levies_balance_id
FROM pay_balance_types
WHERE balance_name = 'Tax Levies'
AND business_group_id IS NULL
AND legislation_code = 'US';
SELECT balance_type_id
INTO g_childsupp_count_balance_id
FROM pay_balance_types
WHERE balance_name = 'Child Supp Count'
AND business_group_id IS NULL
AND legislation_code = 'US';
SELECT balance_type_id
INTO g_total_dedns_balance_id
FROM pay_balance_types
WHERE balance_name = 'Child Supp Total Dedns'
AND business_group_id IS NULL
AND legislation_code = 'US';
SELECT balance_type_id
INTO g_total_amount_balance_id
FROM pay_balance_types
WHERE balance_name = 'Child Supp Total Amount'
AND business_group_id IS NULL
AND legislation_code = 'US';
SELECT balance_type_id
INTO g_supp_not_taken_bal_id
FROM pay_balance_types
WHERE balance_name = 'Support Not Taken Amount'
AND business_group_id IS NULL
AND legislation_code = 'US';
SELECT balance_type_id
INTO g_total_fees_balance_id
FROM pay_balance_types
WHERE balance_name = 'Child Supp Total Fees'
AND business_group_id IS NULL
AND legislation_code = 'US';
SELECT balance_type_id
INTO g_wh_support_balance_id
FROM pay_balance_types
WHERE balance_name = 'Total Withheld Support'
AND business_group_id IS NULL
AND legislation_code = 'US';
SELECT balance_type_id
INTO g_wh_fee_balance_id
FROM pay_balance_types
WHERE balance_name = 'Total Withheld Fee'
AND business_group_id IS NULL
AND legislation_code = 'US';
SELECT balance_type_id
INTO g_net_balance_id
FROM pay_balance_types
WHERE balance_name = 'Net'
AND business_group_id IS NULL
AND legislation_code = 'US';
SELECT balance_type_id
INTO g_payments_balance_id
FROM pay_balance_types
WHERE balance_name = 'Payments'
AND business_group_id IS NULL
AND legislation_code = 'US';
UPDATE pay_input_values_f
SET mandatory_flag = 'X'
WHERE element_type_id = v_ele_type_id
AND name = v_pay_value_name;
SELECT input_value_id
INTO v_payval_id
FROM pay_input_values_f
WHERE element_type_id = v_ele_type_id
AND name = v_pay_value_name
AND g_eff_end_date between effective_start_date
and effective_end_date; /*1498260*/
UPDATE pay_element_types_f
SET element_information_category = g_ele_info_cat,
element_information1 = p_category,
element_information2 = g_partial_dedn,
element_information3 = g_ele_runtype,
element_information9 = garn_mix_category(i)
WHERE element_type_id = v_ele_type_id
AND business_group_id + 0 = p_bg_id;
SELECT formula_id
INTO v_calc_formula_id
FROM ff_formulas_f
WHERE formula_name = garn_pay_formula(i)
AND g_eff_start_date between effective_start_date
and effective_end_date
AND nvl(business_group_id, p_bg_id) + 0 = p_bg_id
AND nvl(legislation_code, g_template_leg_code) = g_template_leg_code
AND rownum < 2;
UPDATE pay_status_processing_rules_f
SET formula_id = v_calc_formula_id
WHERE status_processing_rule_id = already_exists
AND g_eff_start_date between effective_start_date
and effective_end_date;
p_insert_update_flag => 'UPDATE',
p_input_value_id => garn_base_iv_ids(k),
p_rowid => NULL,
p_recurring_flag => 'N',
p_mandatory_flag => garn_base_iv_mand(k),
p_hot_default_flag => 'N',
p_standard_link_flag => 'N',
p_classification_type => 'N',
p_name => garn_base_iv_names(k),
p_uom => garn_base_iv_uom(k),
p_min_value => NULL,
p_max_value => NULL,
p_default_value => garn_base_iv_dflt(k),
p_lookup_type => garn_base_iv_lkp(k),
p_formula_id => NULL,
p_generate_db_items_flag => garn_base_iv_dbi(k),
p_warning_or_error => NULL);
p_insert_update_flag => 'UPDATE',
p_input_value_id => garn_base_iv_ids(k),
p_rowid => NULL,
p_recurring_flag => 'N',
p_mandatory_flag => garn_base_iv_mand(k),
p_hot_default_flag => 'N',
p_standard_link_flag => 'N',
p_classification_type => 'N',
p_name => garn_base_iv_names(k),
p_uom => garn_base_iv_uom(k),
p_min_value => NULL,
p_max_value => NULL,
p_default_value => garn_base_iv_dflt(k),
p_lookup_type => garn_base_iv_lkp(k),
p_formula_id => NULL,
p_generate_db_items_flag => garn_base_iv_dbi(k),
p_warning_or_error => NULL);
p_insert_update_flag => 'UPDATE',
p_input_value_id => garn_calc_iv_ids(c),
p_rowid => NULL,
p_recurring_flag => 'N',
p_mandatory_flag => garn_calc_iv_mand(c),
p_hot_default_flag => 'N',
p_standard_link_flag => 'N',
p_classification_type => 'N',
p_name => garn_calc_iv_names(c),
p_uom => garn_calc_iv_uom(c),
p_min_value => NULL,
p_max_value => NULL,
p_default_value => garn_calc_iv_dflt(c),
p_lookup_type => garn_calc_iv_lkp(c),
p_formula_id => NULL,
p_generate_db_items_flag => garn_calc_iv_dbi(c),
p_warning_or_error => NULL);
p_insert_update_flag => 'UPDATE',
p_input_value_id => garn_calc_iv_ids(c),
p_rowid => NULL,
p_recurring_flag => 'N',
p_mandatory_flag => garn_calc_iv_mand(c),
p_hot_default_flag => 'N',
p_standard_link_flag => 'N',
p_classification_type => 'N',
p_name => garn_calc_iv_names(c),
p_uom => garn_calc_iv_uom(c),
p_min_value => NULL,
p_max_value => NULL,
p_default_value => garn_calc_iv_dflt(c),
p_lookup_type => garn_calc_iv_lkp(c),
p_formula_id => NULL,
p_generate_db_items_flag => garn_calc_iv_dbi(c),
p_warning_or_error => NULL);
p_insert_update_flag => 'UPDATE',
p_input_value_id => garn_verif_iv_ids(v),
p_rowid => NULL,
p_recurring_flag => 'N',
p_mandatory_flag => garn_verif_iv_mand(v),
p_hot_default_flag => 'N',
p_standard_link_flag => 'N',
p_classification_type => 'N',
p_name => garn_verif_iv_names(v),
p_uom => garn_verif_iv_uom(v),
p_min_value => NULL,
p_max_value => NULL,
p_default_value => NULL,
p_lookup_type => garn_verif_iv_dflt(v),
p_formula_id => NULL,
p_generate_db_items_flag => garn_verif_iv_dbi(v),
p_warning_or_error => NULL);
p_insert_update_flag => 'UPDATE',
p_input_value_id => garn_verif_iv_ids(v),
p_rowid => NULL,
p_recurring_flag => 'N',
p_mandatory_flag => garn_verif_iv_mand(v),
p_hot_default_flag => 'N',
p_standard_link_flag => 'N',
p_classification_type => 'N',
p_name => garn_verif_iv_names(v),
p_uom => garn_verif_iv_uom(v),
p_min_value => NULL,
p_max_value => NULL,
p_default_value => NULL,
p_lookup_type => garn_verif_iv_dflt(v),
p_formula_id => NULL,
p_generate_db_items_flag => garn_verif_iv_dbi(v),
p_warning_or_error => NULL);
p_insert_update_flag => 'UPDATE',
p_input_value_id => garn_si_iv_ids(siv),
p_rowid => NULL,
p_recurring_flag => 'N',
p_mandatory_flag => garn_si_iv_mand(siv),
p_hot_default_flag => 'N',
p_standard_link_flag => 'N',
p_classification_type => 'N',
p_name => garn_si_iv_names(siv),
p_uom => garn_si_iv_uom(siv),
p_min_value => NULL,
p_max_value => NULL,
p_default_value => NULL,
p_lookup_type => garn_si_iv_dflt(siv),
p_formula_id => NULL,
p_generate_db_items_flag => garn_si_iv_dbi(siv),
p_warning_or_error => NULL);
p_insert_update_flag => 'UPDATE',
p_input_value_id => garn_si_iv_ids(siv),
p_rowid => NULL,
p_recurring_flag => 'N',
p_mandatory_flag => garn_si_iv_mand(siv),
p_hot_default_flag => 'N',
p_standard_link_flag => 'N',
p_classification_type => 'N',
p_name => garn_si_iv_names(siv),
p_uom => garn_si_iv_uom(siv),
p_min_value => NULL,
p_max_value => NULL,
p_default_value => NULL,
p_lookup_type => garn_si_iv_dflt(siv),
p_formula_id => NULL,
p_generate_db_items_flag => garn_si_iv_dbi(siv),
p_warning_or_error => NULL);
p_insert_update_flag => 'UPDATE',
p_input_value_id => garn_sf_iv_ids(sfv),
p_rowid => NULL,
p_recurring_flag => 'N',
p_mandatory_flag => garn_sf_iv_mand(sfv),
p_hot_default_flag => 'N',
p_standard_link_flag => 'N',
p_classification_type => 'N',
p_name => garn_sf_iv_names(sfv),
p_uom => garn_sf_iv_uom(sfv),
p_min_value => NULL,
p_max_value => NULL,
p_default_value => NULL,
p_lookup_type => garn_sf_iv_dflt(sfv),
p_formula_id => NULL,
p_generate_db_items_flag => garn_sf_iv_dbi(sfv),
p_warning_or_error => NULL);
p_insert_update_flag => 'UPDATE',
p_input_value_id => garn_sf_iv_ids(sfv),
p_rowid => NULL,
p_recurring_flag => 'N',
p_mandatory_flag => garn_sf_iv_mand(sfv),
p_hot_default_flag => 'N',
p_standard_link_flag => 'N',
p_classification_type => 'N',
p_name => garn_sf_iv_names(sfv),
p_uom => garn_sf_iv_uom(sfv),
p_min_value => NULL,
p_max_value => NULL,
p_default_value => NULL,
p_lookup_type => garn_sf_iv_dflt(sfv),
p_formula_id => NULL,
p_generate_db_items_flag => garn_sf_iv_dbi(sfv),
p_warning_or_error => NULL);
p_insert_update_flag => 'UPDATE',
p_input_value_id => garn_fee_iv_ids(lfee),
p_rowid => NULL,
p_recurring_flag => 'N',
p_mandatory_flag => garn_fee_iv_mand(lfee),
p_hot_default_flag => 'N',
p_standard_link_flag => 'N',
p_classification_type => 'N',
p_name => garn_fee_iv_names(lfee),
p_uom => garn_fee_iv_uom(lfee),
p_min_value => NULL,
p_max_value => NULL,
p_default_value => NULL,
p_lookup_type => garn_fee_iv_dflt(lfee),
p_formula_id => NULL,
p_generate_db_items_flag => garn_fee_iv_dbi(lfee),
p_warning_or_error => NULL);
p_insert_update_flag => 'UPDATE',
p_input_value_id => garn_fee_iv_ids(lfee),
p_rowid => NULL,
p_recurring_flag => 'N',
p_mandatory_flag => garn_fee_iv_mand(lfee),
p_hot_default_flag => 'N',
p_standard_link_flag => 'N',
p_classification_type => 'N',
p_name => garn_fee_iv_names(lfee),
p_uom => garn_fee_iv_uom(lfee),
p_min_value => NULL,
p_max_value => NULL,
p_default_value => NULL,
p_lookup_type => garn_fee_iv_dflt(lfee),
p_formula_id => NULL,
p_generate_db_items_flag => garn_fee_iv_dbi(lfee),
p_warning_or_error => NULL);
p_insert_update_flag => 'UPDATE',
p_input_value_id => garn_vp_iv_ids(lvp),
p_rowid => NULL,
p_recurring_flag => 'N',
p_mandatory_flag => garn_vp_iv_mand(lvp),
p_hot_default_flag => 'N',
p_standard_link_flag => 'N',
p_classification_type => 'N',
p_name => garn_vp_iv_names(lvp),
p_uom => garn_vp_iv_uom(lvp),
p_min_value => NULL,
p_max_value => NULL,
p_default_value => NULL,
p_lookup_type => garn_vp_iv_dflt(lvp),
p_formula_id => NULL,
p_generate_db_items_flag => garn_vp_iv_dbi(lvp),
p_warning_or_error => NULL);
p_insert_update_flag => 'UPDATE',
p_input_value_id => garn_vp_iv_ids(lvp),
p_rowid => NULL,
p_recurring_flag => 'N',
p_mandatory_flag => garn_vp_iv_mand(lvp),
p_hot_default_flag => 'N',
p_standard_link_flag => 'N',
p_classification_type => 'N',
p_name => garn_vp_iv_names(lvp),
p_uom => garn_vp_iv_uom(lvp),
p_min_value => NULL,
p_max_value => NULL,
p_default_value => NULL,
p_lookup_type => garn_vp_iv_dflt(lvp),
p_formula_id => NULL,
p_generate_db_items_flag => garn_vp_iv_dbi(lvp),
p_warning_or_error => NULL);
hr_utility.trace('After inserting Calculated fee for non supported elements');
delete from pay_formula_result_rules_f
where STATUS_PROCESSING_RULE_ID = garn_statproc_rule_id(1);
delete from pay_formula_result_rules_f
where STATUS_PROCESSING_RULE_ID = garn_statproc_rule_id(2);
delete from pay_formula_result_rules_f
where STATUS_PROCESSING_RULE_ID = garn_statproc_rule_id(7);
delete from pay_formula_result_rules_f
where STATUS_PROCESSING_RULE_ID = garn_statproc_rule_id(3);
hr_utility.trace('Insert feeds for fee amount input val to balances fed by invol dedns');
UPDATE pay_element_types_f
SET element_information5 = garn_ele_ids(2),
element_information8 = garn_assoc_bal_ids(8),
element_information10 = garn_assoc_bal_ids(1),
element_information11 = garn_assoc_bal_ids(4),
/* Not setting Arrears and Not Taken Balance for bug 980683
NotTaken and Arrears are set to 0 in the verifier formula
for bug 980683. However, these balances are needed for
bug 2527761. So, to make both work, the arrears and not taken
balance will be created but not set in the Further Element
Information Flexfield.
element_information12 = garn_assoc_bal_ids(5),
element_information13 = garn_assoc_bal_ids(6),
*/
element_information16 = garn_assoc_bal_ids(2),
element_information17 = garn_assoc_bal_ids(3),
element_information15 = garn_assoc_bal_ids(7) ,
element_information18 = garn_ele_ids(4),
element_information19 = garn_ele_ids(5),
element_information20 = garn_ele_ids(3)
WHERE element_type_id = garn_ele_ids(1);
hr_utility.trace('Before final update Line:4513 garn_assoc_bal_id '|| to_char(garn_assoc_bal_ids(7)));
UPDATE pay_element_types_f
SET element_information10 = garn_assoc_bal_ids(7),
element_information11 = garn_assoc_bal_ids(9)
WHERE element_type_id = garn_ele_ids(6);
PROCEDURE delete_dedn (p_business_group_id in number,
p_ele_type_id in number,
p_ele_name in varchar2,
p_ele_priority in number,
p_ele_info_10 in varchar2,
p_ele_info_11 in varchar2,
p_ele_info_12 in varchar2,
p_ele_info_13 in varchar2,
p_ele_info_14 in varchar2,
p_ele_info_15 in varchar2,
p_ele_info_16 in varchar2,
p_ele_info_17 in varchar2,
p_ele_info_18 in varchar2,
p_ele_info_19 in varchar2,
p_ele_info_20 in varchar2,
p_ele_info_5 in varchar2,
p_ele_info_8 in varchar2,
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 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 + 0 = p_business_group_id
AND ff.legislation_code IS NULL;
SELECT distinct ff.formula_id
FROM ff_formulas_f ff
WHERE ff.formula_name like upper('OLD%'||p_ele_name||'_BALANCE_SETUP_FORMULA%')
AND ff.business_group_id + 0 = p_business_group_id;
SELECT distinct status_processing_rule_id
FROM pay_status_processing_rules_f
WHERE element_type_id = l_ele_id;
SELECT ele_payroll_freq_rule_id
FROM pay_ele_payroll_freq_rules
WHERE element_type_id = p_ele_type_id;
SELECT template_id
FROM pay_shadow_element_types
WHERE element_name = p_ele_name;
SELECT element_type_id
FROM pay_element_types_f
WHERE element_name = p_ele_name||' Priority'
AND p_del_sess_date between effective_start_date
and effective_end_date
AND business_group_id + 0 = p_business_group_id;
SELECT balance_type_id
FROM pay_balance_types
WHERE balance_name = p_ele_name || l_bal_name
AND business_group_id + 0 = p_business_group_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 element_type_id
, element_information11
into l_fee_ele_id
, l_fee_accrued_bal_id
from pay_element_types_f
where element_name = p_ele_name||' Fees'
and p_del_sess_date between effective_start_date
and effective_end_date
and business_group_id + 0 = p_business_group_id ;
hr_utility.set_location('hr_us_garn_gen.delete_dedn',40);
hr_utility.set_location('hr_us_garn_gen.delete_dedn',40);
hr_utility.set_location('hr_us_garn_gen.delete_dedn',40);
hr_utility.set_location('hr_us_garn_gen.delete_dedn',40);
hr_utility.set_location('hr_us_garn_gen.delete_dedn',40);
hr_utility.set_location('hr_us_garn_gen.delete_dedn',40);
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);
hr_utility.set_location('hr_us_garn_gen.delete_dedn', 45);
pay_element_template_api.delete_user_structure(
p_drop_formula_packages => TRUE
,p_template_id => v_template_id);
END delete_dedn;