The following lines contain the word 'select', 'insert', 'update' or 'delete':
| delete_gross_up |
| |
| PURPOSE |
| |
| NOTES |
| |
\*----------------------------------------------------------------------*/
--
procedure delete_gross_up (
p_business_group_id IN NUMBER,
p_element_name IN VARCHAR2
) IS
-- Local Variables
v_element_name PAY_ELEMENT_TYPES_F.ELEMENT_NAME%TYPE;
SELECT pet.element_name,
pet.element_type_id,
processing_priority,
element_information10,
element_information12
FROM pay_element_types_f pet,
per_business_groups pbg
WHERE pbg.business_group_id + 0 = pet.business_group_id + 0
AND pbg.business_group_id + 0 = p_business_group_id
AND pet.element_name like v_element_name;
hr_utility.set_location('pay_db_pay_us_gross.delete_gross_up', 1);
hr_utility.set_location('pay_db_pay_us_gross.delete_gross_up', 2);
hr_utility.set_location('pay_db_pay_us_gross.delete_gross_up', 3);
end delete_gross_up;
SELECT business_group_id
INTO g_business_group_ID
FROM per_business_groups
WHERE upper(name) = upper(p_business_group_name);
SELECT type.element_type_id
INTO v_element_type_id
FROM pay_element_types_f_tl type_tl,
pay_element_types_f type
WHERE type_tl.element_type_id = type.element_type_id
and userenv('LANG') = type_tl.language
AND nvl(business_group_id, -1) = nvl(g_business_group_ID, -1)
AND type_tl.element_name = p_element_name;
SELECT formula_id
INTO v_formula_id
FROM ff_formulas_f ff,
ff_formula_types ft
WHERE ft.formula_type_name = 'Oracle Payroll'
AND ff.formula_type_id = ft.formula_type_id
AND ff.formula_name = p_formula_name;
SELECT piv.input_value_id
INTO v_input_value_ID
FROM pay_input_values_f_tl piv_tl,
pay_input_values_f piv,
pay_element_types_f_tl pet_tl,
pay_element_types_f pet
WHERE piv_tl.input_value_id = piv.input_value_id
and pet_tl.element_type_id = pet.element_type_id
and userenv('LANG') = piv_tl.language
and userenv('LANG') = pet_tl.language
AND piv_tl.name = v_piv_name
AND nvl(piv.business_group_id, -1) = nvl(g_business_group_ID, -1)
AND pet.element_type_id = piv.element_type_id
AND pet_tl.element_name = v_element_name;
SELECT piv.input_value_id
INTO v_input_value_ID
FROM pay_input_values_f_tl piv_tl,
pay_input_values_f piv,
pay_element_types_f pet
WHERE piv_tl.input_value_id = piv.input_value_id
and userenv('LANG') = piv_tl.language
and piv_tl.name = v_piv_name
AND piv.business_group_id is null
AND pet.element_type_id = piv.element_type_id
AND pet.element_name = 'FIT_GROSSUP_ADJUSTMENT';
SELECT STATUS_PROCESSING_RULE_ID
INTO v_prev_proc_rule_id
FROM PAY_STATUS_PROCESSING_RULES_F
WHERE ELEMENT_TYPE_ID = v_element_type_ID;
SELECT formula_id
INTO v_formula_id
FROM ff_formulas_f ff,
ff_formula_types ft
WHERE ft.formula_type_name = 'Oracle Payroll'
AND ff.formula_type_id = ft.formula_type_id
AND ff.formula_name = g_vtx_elem_tab(v_element_num);
hrdyndbi.delete_element_type_dict(
p_element_type_id=>v_element_type_ID);
SELECT pay_status_processing_rules_s.nextval
INTO v_rule_ID
FROM sys.dual;
INSERT INTO PAY_STATUS_PROCESSING_RULES_F
(
STATUS_PROCESSING_RULE_ID,
EFFECTIVE_START_DATE,
EFFECTIVE_END_DATE,
BUSINESS_GROUP_ID,
LEGISLATION_CODE,
ELEMENT_TYPE_ID,
ASSIGNMENT_STATUS_TYPE_ID,
FORMULA_ID,
PROCESSING_RULE,
COMMENT_ID,
LEGISLATION_SUBGROUP,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATED_BY,
CREATION_DATE
)
select
v_rule_ID,
p_effective_start_date,
p_effective_end_date,
g_business_group_ID,
'US',
p_element_type_ID,
ASSIGNMENT_STATUS_TYPE_ID, -- assignment_status_type_id
p_formula_ID,
'P', -- processing rule
NULL, -- comment ID
NULL, -- legislation subgroup
g_todays_date,
-1,
-1,
-1,
g_todays_date
from per_assignment_status_types
where USER_STATUS = 'Active Assignment';
SELECT pay_formula_result_rules_s.nextval
INTO v_rule_ID
FROM sys.dual;
INSERT INTO pay_formula_result_rules_f
(
FORMULA_RESULT_RULE_ID,
EFFECTIVE_START_DATE,
EFFECTIVE_END_DATE,
BUSINESS_GROUP_ID,
LEGISLATION_CODE,
STATUS_PROCESSING_RULE_ID,
RESULT_NAME,
RESULT_RULE_TYPE,
LEGISLATION_SUBGROUP,
SEVERITY_LEVEL,
INPUT_VALUE_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATED_BY,
CREATION_DATE
)
values
(
v_rule_ID,
p_effective_start_date,
p_effective_end_date,
g_business_group_ID,
p_legislation_code, -- Legislation Code
p_stat_proc_ID, -- Status Processing Rule ID
upper(p_result_name),
p_result_type,
NULL, -- Legislation Subgroup
p_severity,
p_input_value_ID,
g_todays_date,
-1,
-1,
-1,
g_todays_date
);