[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Update all formula results for arrears elements to the
negative ammount.
110.1 14/04/98 M.Lisiecki Removed changes introduced to fix bug 585429 as it proved not
to be a bug.
115.1 21/04/99 S.Grant Multi-radix changes.
115.4 16-jun-1999 achauhan replaced dbms_output with hr_utility.trace
================================================================= */
--
-- ====================== declare tables ============================
--
TYPE char_tabtype IS TABLE OF VARCHAR2(50)
INDEX BY BINARY_INTEGER;
select formula_type_id
into v_formula_type_id
from ff_formula_types
where formula_type_name = 'Oracle Payroll'
;
select element_type_id from pay_element_types_f
where element_name = element_name_list (el_cnt) || 'POST_VERTEX';
select input_value_id from pay_input_values_f
where element_type_id = p_element_type_id and
name = post_inp_value_list(p_inp_cnt);
select element_type_id from pay_element_types_f
where element_name = element_name_list (el_count) || 'ARR';
select input_value_id from pay_input_values_f
where element_type_id = p_element_type_id and
name = arr_inp_value_list(p_inp_cnt);
select balance_type_id from pay_balance_types
where balance_name = arrear_bal_name_list (p_bal_cnt);
SELECT balance_type_id
INTO ln_id
FROM pay_balance_types
WHERE balance_name = lc_name;
SELECT balance_type_id
INTO ln_id
FROM pay_balance_types
WHERE balance_name = lc_name;
select 'Y' results_exist
from dual
where
exists ( select 'x'
from pay_run_results rr,
pay_element_types_f ele
where
ele.element_type_id = rr.element_type_id
and ele.element_name in ( p_element_string||'POST_VERTEX', p_element_string||'ARR')
) ;
PROCEDURE delete_bal_dim
IS
CURSOR bal_dim (p_bal_name VARCHAR2)
IS
SELECT db.defined_balance_id
FROM pay_balance_types bt,
pay_defined_balances db
WHERE bt.balance_name = p_bal_name AND
bt.balance_type_id = db.balance_type_id;
hr_utility.set_location('pynegnet01.delete_bal_dim',1);
DELETE FROM pay_defined_balances
WHERE defined_balance_id = ln_id;
hr_utility.set_location('pynegnet01.delete_bal_dim',2);
END delete_bal_dim;
PROCEDURE delete_bal_feed
IS
CURSOR bal_dim (p_bal_name VARCHAR2)
IS
SELECT bf.balance_feed_id
FROM pay_balance_types bt,
pay_balance_feeds_f bf
WHERE
bt.balance_name = p_bal_name AND
bt.balance_type_id = bf.balance_type_id;
hr_utility.set_location('pynegnet01.delete_bal_feed',1);
DELETE FROM pay_balance_feeds_f
WHERE balance_feed_id = ln_id;
hr_utility.set_location('pynegnet01.delete_bal_feed',2);
END delete_bal_feed;
PROCEDURE delete_bal
IS
/* balances counter */
total_bal BINARY_INTEGER := v_total_balances;
hr_utility.set_location('pynegnet01.delete_bal',1);
DELETE FROM pay_balance_types
WHERE balance_name = lc_name;
hr_utility.set_location('pynegnet01.delete_bal',2);
END delete_bal;
SELECT bd.dimension_name
FROM pay_balance_types bt,
pay_balance_dimensions bd,
pay_defined_balances db
WHERE bt.balance_name = p_proto_bal_name AND
bt.balance_type_id = db.balance_type_id AND
db.balance_dimension_id = bd.balance_dimension_id;
-- update jurisdiction level for the arrears balance.
--
--
update pay_balance_types
set jurisdiction_level =
( select jurisdiction_level
from pay_balance_types
where balance_name = lc_proto
and business_group_id is null
and legislation_code = 'US')
where balance_type_id = ln_id;
PROCEDURE delete_ff_el
IS
-- elements counter
total_el BINARY_INTEGER := v_total_elements;
hr_utility.set_location('pynegnet01.delete_ff_el',1);
DELETE FROM ff_formulas_f
WHERE formula_name = lc_ff_name;
hr_utility.set_location('pynegnet01.delete_ff_el',2);
END delete_ff_el;
SELECT ff_formulas_s.NEXTVAL
INTO ln_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
(ln_formula_id,
v_effective_start_date,
v_effective_end_date,
v_business_group_id,
v_legislation_code,
v_formula_type_id,
lc_ff_name,
lc_ff_desc,
lc_text,
lc_sticky_flag,
v_sysdate,
v_uid,
v_uid,
v_uid,
v_sysdate);
PROCEDURE delete_arr_inp
IS
CURSOR arr_inp (p_arr_name VARCHAR2)
IS
SELECT iv.input_value_id
FROM pay_input_values_f iv,
pay_element_types_f et
WHERE et.element_name = p_arr_name AND
et.element_type_id = iv.element_type_id;
hr_utility.set_location('pynegnet01.delete_arr_inp',1);
DELETE FROM pay_balance_feeds_f
WHERE input_value_id = ln_id AND
balance_type_id = ln_spec_id;
DELETE FROM pay_input_values_f
WHERE input_value_id = ln_id and
business_group_id is null;
hr_utility.set_location('pynegnet01.delete_arr_inp',2);
END delete_arr_inp;
PROCEDURE delete_arr_el
IS
/* elements counter */
total_el BINARY_INTEGER := 0;
hr_utility.set_location('pynegnet01.delete_arr_el',1);
DELETE FROM pay_element_types_f
WHERE element_name = lc_name and
business_group_id is null;
DELETE FROM ff_user_entities
WHERE user_entity_name LIKE lc_name;
hr_utility.set_location('pynegnet01.delete_arr_el',2);
END delete_arr_el;
PROCEDURE delete_arr_spec
IS
CURSOR arr_inp (p_arr_name VARCHAR2)
IS
SELECT iv.input_value_id
FROM pay_input_values_f iv,
pay_element_types_f et
WHERE et.element_name = p_arr_name AND
et.element_type_id = iv.element_type_id;
hr_utility.set_location('pynegnet01.delete_arr_spec',1);
DELETE FROM pay_balance_feeds_f
WHERE input_value_id = ln_id AND
( balance_type_id = ln_spec_id
OR balance_type_id = ln_whld_id);
hr_utility.set_location('pynegnet01.delete_arr_spec',2);
END delete_arr_spec;
SELECT classification_id
INTO ln_prim_class_id
FROM pay_element_classifications
WHERE classification_name = 'Information'
AND legislation_code = 'US'
AND business_group_id is null
;
SELECT classification_id
INTO ln_prim_class_id
FROM pay_element_classifications
WHERE classification_name = 'Information'
and legislation_code = 'US'
and business_group_id is null ;
update pay_balance_feeds_f
set business_group_id = null,
effective_start_date = v_effective_start_date
where balance_type_id = ln_balance_type_id
;
PROCEDURE delete_post_inp
IS
CURSOR post_inp (p_post_name VARCHAR2)
IS
SELECT iv.input_value_id
FROM pay_input_values_f iv,
pay_element_types_f et
WHERE et.element_name = p_post_name AND
et.element_type_id = iv.element_type_id;
hr_utility.set_location('pynegnet01.delete_post_input',1);
DELETE FROM pay_input_values_f
WHERE input_value_id = ln_id and
business_group_id is null;
hr_utility.set_location('pynegnet01.delete_post_input',2);
END delete_post_inp;
PROCEDURE delete_post_el
IS
/* elements counter */
total_el BINARY_INTEGER := 0;
SELECT psprf.status_processing_rule_id
FROM pay_status_processing_rules_f psprf,
pay_element_types_f petf
WHERE petf.element_type_id = psprf.element_type_id and
petf.element_name = p_lc_name and
petf.business_group_id is null;
hr_utility.set_location('pynegnet01.delete_post_el',1);
DELETE FROM pay_element_types_f
WHERE element_name = lc_name and
business_group_id is null;
hr_utility.set_location('pynegnet01.delete_post_el',2);
DELETE FROM ff_user_entities
WHERE user_entity_name LIKE lc_name and
business_group_id is null;
hr_utility.set_location('pynegnet01.delete_post_el',3);
SELECT element_type_id
INTO ln_id
FROM pay_element_types_f
WHERE element_name = lc_name;
DELETE FROM pay_status_processing_rules_f
WHERE element_type_id = ln_id;
DELETE FROM pay_formula_result_rules_f
WHERE status_processing_rule_id = l_rule_id;
DELETE FROM pay_formula_result_rules_f
WHERE status_processing_rule_id = l_rule_id;
hr_utility.set_location('pynegnet01.delete_post_el',4);
END delete_post_el;
/* insert status processing rule */
ln_formula_id := ff_id_list (el_cnt);
select status_processing_rule_id into ln_stat_id
from pay_status_processing_rules_f
where element_type_id = post_element_id_list (el_cnt);
/* insert_formula result rules */
/* part 1 */
rule_cnt := 1;
PROCEDURE delete_vertex_results
IS
CURSOR first_inp (p_post_name VARCHAR2)
IS
SELECT iv.input_value_id
FROM pay_input_values_f iv,
pay_element_types_f et
WHERE et.element_name = p_post_name AND
et.element_type_id = iv.element_type_id
;
hr_utility.set_location('pynegnet.delete_vertex_results',1);
SELECT element_type_id
INTO ln_vertex_id
FROM pay_element_types_f
WHERE element_name = lc_name;
DELETE FROM pay_formula_result_rules_f
WHERE
input_value_id = ln_id
;
hr_utility.set_location('pynegnet.delete_vertex_results',2);
END delete_vertex_results;
/* insert_formula result rules */
rule_cnt BINARY_INTEGER := 1;
SELECT status_processing_rule_id
INTO ln_stat_id
FROM pay_status_processing_rules_f sp,
pay_element_types_f et
WHERE et.element_name = 'VERTEX_RESULTS'
AND et.element_type_id = sp.element_type_id;
select
element_type_id
from pay_element_types_f
where
element_name = p_element
and legislation_code = 'US'
;
update pay_input_values_f
set business_group_id = null
where element_type_id = lc_element_type_id
and business_group_id is not null
;
update pay_balance_feeds_f
set business_group_id = null,
effective_start_date = v_effective_start_date
where input_value_id in
(select input_value_id
from pay_input_values_f
where element_type_id = lc_element_type_id)
and (business_group_id is not null
or effective_start_date <> v_effective_start_date)
;
update pay_input_values_f
set business_group_id = null
where element_type_id = lc_element_type_id
and business_group_id is not null
;
select element_type_id from pay_element_types_f
where element_name = element_name_list (el_count) || 'ARR';
SELECT pbf.balance_feed_id, pbt.balance_name
FROM pay_balance_feeds_f pbf, pay_input_values_f pivf,
pay_element_types_f petf, pay_balance_types pbt
WHERE pbf.input_value_id = pivf.input_value_id and
petf.element_type_id = pivf.element_type_id and
petf.element_type_id = p_arr_element_type_id and
pbf.balance_type_id = pbt.balance_type_id;
update pay_balance_feeds_f
set scale = decode
(upper(l_balance_name),
upper(arrear_bal_name_list(l_assign_list)),-1,
upper(arr_bal_feed_name_list (2)),-1,
upper(arr_bal_feed_name_list (3)),-1,
upper(arrear_bal_name_list (13)),-1, +1)
where balance_feed_id = l_balance_feed_id and
business_group_id is null;
update ff_formulas_f
set formula_text = lc_text
where formula_name = lc_ff_name and business_group_id is null;
select prrv.run_result_id, prrv.input_value_id, prrv.result_value
from pay_run_result_values prrv,
pay_input_values_f pivf,
pay_element_types_f petf
where prrv.input_value_id = pivf.input_value_id and
upper(pivf.name) = 'PAY VALUE' and
pivf.element_type_id = petf.element_type_id and
petf.element_name like '%_ARR' and
petf.business_group_id is null;
update pay_run_result_values
set result_value = fnd_number.number_to_canonical(fnd_number.canonical_to_number(result_value) * (-1))
where run_result_id = l_run_result_id and
input_value_id = l_input_value_id;
select name into v_business_group_name
from hr_organization_units
where organization_id = 0 ;
delete_ff_el;
delete_bal_dim;
delete_bal_feed;
delete_arr_spec;
delete_bal;
delete_arr_inp;
delete_arr_el;
delete_vertex_results;
delete_post_inp;
delete_post_el;
-- run results exist so be careful about what gets updated.
hr_utility.trace('Error. Run results exist, this script has already been applied. Contact your Oracle representative');