[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
we need to delete input values
27-Sep-2006 sudedas 115.2 5497299 Modify proc modify_calc_res_rule.
Formula Result WH_FEE_AMT will
feed Old Fees 'Withheld Fee Amount'
Instead of New Fees.
Changed cursor c_get_mig_elem_bg
in proc qual_elem_upg.
*****************************************************************************/
/************************************************************
** Local Package Variables
************************************************************/
type varchar2_tab is table of varchar2(100) index by binary_integer;
select 'Exists'
from pay_element_types_f
where (upper(element_name) like upper(c_ele_name) OR
upper(element_name) like upper(c_ele_name || ' Calculator'))
and business_group_id = p_business_grp_id;
select formula_text
from ff_formulas_f
where formula_name = cp_formula_name
and business_group_id = p_business_grp_id;
update ff_formulas_f
set formula_text = l_new_formula_text
where formula_name = l_formula_name
and business_group_id = p_business_grp_id;
delete
from ff_compiled_info
where formula_id in (select formula_id
from ff_formulas_f
where formula_name in (l_formula_name,
l_cal_formula_name));
delete
from ff_fdi_usages
where formula_id in (select formula_id
from ff_formulas_f
where formula_name in (l_formula_name,
l_cal_formula_name));
select element_name,
reporting_name,
description,
benefit_classification_id,
element_information1 category,
processing_type,
processing_priority,
standard_link_flag,
element_information3 processing_runtype,
post_termination_rule
from pay_element_types_f
where element_type_id = p_old_ele_type_id
and business_group_id = p_business_grp_id
and cp_eff_date between effective_start_date
and effective_end_date
order by effective_start_date;
select formula_id, -- Formula ID for Skip Rule
element_information2,
element_information5, -- Calculator Element
element_information8, -- Voluntary Deductions
element_information19, -- Special Features
element_information20 -- Verifier
from pay_element_types_f
where element_type_id = cp_ele_type_id
and business_group_id = p_business_grp_id;
gv_name_not_gen.delete;
gv_name_not_gen.delete;
update pay_element_types_f
set formula_id = l_skip_rule
,element_information2 = l_elem_inf2
,element_information5 = l_cal_elem
,element_information8 = l_vol_dedn
,element_information19 = l_spec_feat
,element_information20 = l_verifier
where element_type_id = p_old_ele_type_id
and business_group_id = p_business_grp_id;
This function first deletes all the formula result rules for
the old element.
STEP 2
It copies the formula result rules from the Base element
of the New element created to the Base element of the Old
element.
*****************************************************************************/
PROCEDURE copy_result_rules(p_old_ele_type_id number,
p_new_ele_type_id number,
p_business_grp_id number
)
IS
-- Get processing rules that need to be copied
cursor c_get_stat_proc_rules(cp_ele_type_id number,
cp_bg_grp_id number) IS
select business_group_id,
legislation_code,
legislation_subgroup,
effective_start_date,
effective_end_date,
assignment_status_type_id,
formula_id,
processing_rule
from pay_status_processing_rules_f
where element_type_id in (select element_type_id
from pay_element_types_f
where element_type_id = cp_ele_type_id
and business_group_id = cp_bg_grp_id);
select pfrrf.business_group_id,
NULL legislation_code,
NULL legislation_sub_grp,
pfrrf.effective_start_date,
pfrrf.effective_end_date,
pfrrf.input_value_id,
pfrrf.result_name,
pfrrf.result_rule_type,
pfrrf.severity_level,
pfrrf.element_type_id
from pay_status_processing_rules_f psprf,
pay_element_types_f petf,
pay_formula_result_rules_f pfrrf
where petf.element_type_id = cp_ele_type_id
and petf.business_group_id = cp_bg_grp_id
and psprf.element_type_id = petf.element_type_id
and pfrrf.status_processing_rule_id = psprf.status_processing_rule_id;
* Delete the formula result rules for the Old element.
*/
delete from pay_formula_result_rules_f
where status_processing_rule_id in (select distinct status_processing_rule_id
from pay_status_processing_rules_f psprf,
pay_element_types_f petf
where petf.element_type_id = p_old_ele_type_id
and psprf.element_type_id = petf.element_type_id
and petf.business_group_id = p_business_grp_id);
delete from pay_status_processing_rules_f
where element_type_id in (select distinct element_type_id
from pay_element_types_f
where element_type_id = p_old_ele_type_id
and business_group_id = p_business_grp_id);
select balance_type_id
from pay_balance_types
where business_group_id = cp_business_grp_id
and balance_name in (cp_ele_name,
cp_ele_name || ' Accrued',
cp_ele_name || ' Accrued Fees',
cp_ele_name || ' Additional',
cp_ele_name || ' Arrears',
cp_ele_name || ' Fees',
cp_ele_name || ' Not Taken',
cp_ele_name || ' Replacement')
order by balance_name;
select effective_start_date,
effective_end_date,
legislation_code,
input_value_id,
scale,
legislation_subgroup
from pay_balance_feeds_f
where business_group_id = cp_business_grp_id
and balance_type_id = cp_balance_type_id;
select 'Exists'
from pay_balance_feeds_f
where balance_type_id = cp_balance_type_id
and input_value_id = cp_input_val_id
and business_group_id = cp_business_grp_id;
pay_balance_feeds_f_pkg.Insert_Row(
X_Rowid => l_rowid,
X_Balance_Feed_Id => l_balance_feed_id,
X_Effective_Start_Date => l_eff_start_date,
X_Effective_End_Date => l_eff_end_date,
X_Business_Group_Id => p_business_grp_id,
X_Legislation_Code => l_leg_code,
X_Balance_Type_Id => l_old_bal_type_id_tab(l_count),
X_Input_Value_Id => l_inp_val_id,
X_Scale => l_scale,
X_Legislation_Subgroup => l_leg_sub_group);
Description : This procedure deletes all the balance feeds for the balances
associated to the element passed.
*****************************************************************************/
PROCEDURE del_balance_feeds(p_new_ele_name varchar2,
p_business_grp_id number)
IS
BEGIN
hr_utility.trace('Entering ' || gv_package_name || '.del_balances');
* This delete statement deletes the balance feeds for the balances
* associated with the element passed.
*/
delete
from pay_balance_feeds_f
where balance_type_id in (select balance_type_id
from pay_balance_types
where balance_name in
(p_new_ele_name,
p_new_ele_name || ' Accrued',
p_new_ele_name || ' Accrued Fees',
p_new_ele_name || ' Additional',
p_new_ele_name || ' Arrears',
p_new_ele_name || ' Fees',
p_new_ele_name || ' Not Taken',
p_new_ele_name || ' Replacement')
and business_group_id = p_business_grp_id);
select status_processing_rule_id
from pay_status_processing_rules_f
where element_type_id in (select element_type_id
from pay_element_types_f
where element_name = cp_ele_name
and business_group_id = cp_bg_grp_id);
select petf.element_type_id,
pivf.input_value_id
from pay_element_types_f petf,
pay_input_values_f pivf
where petf.element_name like cp_ele_name
and petf.business_group_id = cp_business_group_id
and pivf.element_type_id = petf.element_type_id
and pivf.name = cp_inp_val_name;
* Get the status processing rule id. This is then used in the Update
* statement below'
*/
open c_get_stat_proc_rules(p_new_ele_name || ' Calculator',
p_business_grp_id);
* the old Base element. These values are then updated for the
* STOP_ENTRY result in the Calculator formula
*/
open c_get_inp_val_id_general(p_old_ele_name,
'Pay Value',
p_business_grp_id);
* Updates the New Calculator formula result rule so that the
* STOP_ENTY result goes to the 'Pay Value' of Old Base element
*/
update pay_formula_result_rules_f
set element_type_id = l_ele_type_id,
input_value_id = l_inp_val_id
where status_processing_rule_id = l_stat_proc_rule_id
and result_name = 'STOP_ENTRY'
and business_group_id = p_business_grp_id;
update pay_formula_result_rules_f
set element_type_id = l_old_fees_ele_type_id,
input_value_id = l_old_fees_inp_val_id
where status_processing_rule_id = l_stat_proc_rule_id
and result_name = 'WH_FEE_AMT'
and business_group_id = p_business_grp_id;
Description : This procedure deletes the base element of the New element
created. We do not want the customer to use the Base element
in the future for processing. We will also have to delete
input Values associated with the base element.
*****************************************************************************/
PROCEDURE del_base_element(p_new_ele_name varchar2,
p_business_grp_id number)
IS
CURSOR c_element_name(cp_element_name varchar2) IS
SELECT element_Type_id
FROM pay_element_Types_f
WHERE element_name = cp_element_name
AND business_group_id = p_business_grp_id;
* Delete the formula result rules associated with the New Base element
*/
DELETE
FROM pay_formula_result_rules_f
WHERE status_processing_rule_id
IN (SELECT status_processing_rule_id
FROM pay_status_processing_rules_f psprf
WHERE psprf.element_type_id = l_element_type_id );
DELETE
FROM pay_status_processing_rules_f
WHERE element_type_id = l_element_Type_id;
/* Delete the Input Values associated with the Base Element */
DELETE
FROM pay_input_values_f
WHERE element_type_id = l_element_type_id;
* Deleted the base element
*/
DELETE
FROM pay_element_types_f
WHERE element_Type_id = l_element_type_id;
* Update statement for elements of category 'Alimony', 'Child Support' and
* 'Spousal Support'.
*/
update pay_input_values_f pivf
set mandatory_flag = 'X'
where pivf.element_type_id in (select distinct element_type_id
from pay_element_types_f
where element_name = p_ele_name
and business_group_id = p_business_grp_id)
and pivf.name in ('Allowances')
and pivf.business_group_id = p_business_grp_id;
* Update statement for elements of category 'Credit Debt' and 'Garnishment'.
*/
update pay_input_values_f pivf
set mandatory_flag = 'X'
where pivf.element_type_id in (select distinct element_type_id
from pay_element_types_f
where element_name = p_ele_name
and business_group_id = p_business_grp_id)
and pivf.name in ('Dedns at Time of Writ',
'Allowances',
'Clear Arrears')
and pivf.business_group_id = p_business_grp_id;
* Update statement for elements of category 'Bankruptcy Order'.
*/
update pay_input_values_f pivf
set mandatory_flag = 'X'
where pivf.element_type_id in (select distinct element_type_id
from pay_element_types_f
where element_name = p_ele_name
and business_group_id = p_business_grp_id)
and pivf.name in ('Dedns at Time of Writ',
'Allowances',
'Clear Arrears')
and pivf.business_group_id = p_business_grp_id;
* Update statement for elements of category 'Education Loan'.
*/
update pay_input_values_f pivf
set mandatory_flag = 'X'
where pivf.element_type_id in (select distinct element_type_id
from pay_element_types_f
where element_name = p_ele_name
and business_group_id = p_business_grp_id)
and pivf.name in ('Dedns at Time of Writ',
'Filing Status',
'Allowances',
'Num Dependents',
'Clear Arrears')
and pivf.business_group_id = p_business_grp_id;
* Update statement for elements of category 'Tax Levy'.
*/
update pay_input_values_f pivf
set mandatory_flag = 'X'
where pivf.element_type_id in (select distinct element_type_id
from pay_element_types_f
where element_name = p_ele_name
and business_group_id = p_business_grp_id)
and pivf.name in ('Percentage',
'Num Dependents',
'Clear Arrears')
and pivf.business_group_id = p_business_grp_id;
* Update statement for elements of category 'Employee Requested'.
*/
update pay_input_values_f pivf
set mandatory_flag = 'X'
where pivf.element_type_id in (select distinct element_type_id
from pay_element_types_f
where element_name = p_ele_name
and business_group_id = p_business_grp_id)
and pivf.name in ('Dedns at Time of Writ',
'Allowances',
'Clear Arrears')
and pivf.business_group_id = p_business_grp_id;
category passed in as a parameter, we will select the elements
that will be migrated in ine request.
*****************************************************************************/
PROCEDURE upgrade_garnishment
(p_elem_type_id in number)
IS
-- Get the elements that need to be migrated for the BG
-- This package will be called with the element category passed
-- to it as a parameter. This method is used to multi-thread the whole process
cursor c_get_mig_elem_bg(cp_ele_type_id number) IS
select /*+ Choose */
petf.element_type_id, min(petf.effective_start_date), max(petf.effective_end_date)
from pay_element_types_f petf
where petf.element_type_id = p_elem_type_id
and petf.element_information4 is NULL
group by petf.element_type_id;
select element_name,
element_information1 element_category,
element_information5 calc_ele_type_id
from pay_element_types_f
where element_type_id = cp_ele_type_id
and business_group_id = cp_business_grp_id;
select element_name
from pay_element_types_f
where element_type_id = cp_ele_type_id
and business_group_id = cp_business_grp_id;
update pay_element_types_f
set element_information4 = l_calc_ele_type_id
where element_type_id = l_old_ele_type_id;
* Delete Balance Feeds for New Balances
*/
del_balance_feeds(l_new_ele_name
,l_business_group_id);
* Delete the New Base Element
*/
del_base_element(l_new_ele_name,
l_business_group_id);
select /*+ Choose */
'Upgrade'
from pay_element_types_f petf,
pay_element_classifications pec
where petf.element_type_id = cp_ele_type_id
and petf.legislation_code is NULL
and petf.effective_end_date > cp_mig_date
and pec.classification_id = petf.classification_id
and pec.classification_name = 'Involuntary Deductions'
and petf.element_name not like '%Calculator'
and petf.element_name not like '%Verifier'
and petf.element_name not like '%Special Inputs'
and petf.element_name not like '%Special Features'
and petf.element_name not like '%Fees'
and petf.element_name not like '%Priority'
and petf.element_information4 is NULL
and petf.element_information1 in ('CS', 'SS', 'AY', 'EL', 'ER', 'BO', 'CD', 'G', 'TL')
and exists (select 'Exists'
from pay_element_types_f petfi
where petfi.element_name like petf.element_name || ' Verifier'
and petfi.business_group_id = petf.business_group_id
and petfi.legislation_code is NULL);
select parameter_value
from pay_action_parameters
where parameter_name = 'US_ADVANCED_WAGE_ATTACHMENT';
* If OLD architecture is selected currently then set the value for
* 'US_ADVANCED_WAGE_ATTACHMENT' to 'Y' and make an explicit
* commit. This ensures all elements are created using the New
* architecture.
*/
open c_get_curr_arch;
update pay_action_parameters
set parameter_value = 'Y'
where parameter_name = 'US_ADVANCED_WAGE_ATTACHMENT';