[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select result.status_processing_rule_id
from pay_formula_result_rules_f RESULT,
pay_input_values_f INPUT
where input.element_type_id = p_element_type_id
and input.input_value_id = result.input_value_id
and result.result_rule_type = p_rule_type
and result.effective_start_date between p_validation_start_date
and p_validation_end_date;
SELECT 1
FROM pay_element_types_f_tl ettl,
pay_element_types_f et
WHERE upper(translate(ettl.element_name,'x_','x '))
= upper(translate(p_element_name,'x_','x '))
AND ettl.element_type_id = et.element_type_id
AND ettl.language = p_language
AND ( et.element_type_id <> p_element_type_id OR p_element_type_id is null)
AND ( g_business_group_id = et.business_group_id + 0 OR g_business_group_id is null )
AND ( g_legislation_code = et.legislation_code OR g_legislation_code is null );
select 1
from pay_batch_lines
where element_type_id = p_element_type_id
and element_type_id is not null
union all
select 1
from pay_batch_lines
where element_type_id is null
and upper (element_name) = upper (p_element_name);
hrdyndbi.delete_element_type_dict(p_element_type_id);
procedure INSERT_ROW(
--
--******************************************************************************
--* Handles the insertion of rows into the base table for the form which is *
--* based on a non-updatable view. It also ensures the correct integrity of *
--* cascading actions is enforced. *
--******************************************************************************
--
-- Parameters to be passed in/out are:
--
-- The rowid and element type ID are generated by this procedure and
-- passed back to the form
p_rowid in out nocopy varchar2,
p_element_type_id in out nocopy number,
--
-- All the base table fields from the forms block
p_effective_start_date date,
p_effective_end_date date,
p_business_group_id number,
p_legislation_code varchar2,
p_formula_id number ,
p_input_currency_code varchar2,
p_output_currency_code varchar2,
p_classification_id number,
p_benefit_classification_id number,
p_additional_entry_allowed varchar2,
p_adjustment_only_flag varchar2,
p_closed_for_entry_flag varchar2,
p_element_name varchar2,
-- --
p_base_element_name varchar2,
-- --
p_indirect_only_flag varchar2,
p_multiple_entries_allowed varchar2,
p_multiply_value_flag varchar2,
p_post_termination_rule varchar2,
p_process_in_run_flag varchar2,
p_processing_priority number,
p_processing_type varchar2,
p_standard_link_flag varchar2,
p_comment_id number,
p_description varchar2,
p_legislation_subgroup varchar2,
p_qualifying_age number,
p_qualifying_length_of_service number,
p_qualifying_units varchar2,
p_reporting_name varchar2,
p_attribute_category varchar2,
p_attribute1 varchar2,
p_attribute2 varchar2,
p_attribute3 varchar2,
p_attribute4 varchar2,
p_attribute5 varchar2,
p_attribute6 varchar2,
p_attribute7 varchar2,
p_attribute8 varchar2,
p_attribute9 varchar2,
p_attribute10 varchar2,
p_attribute11 varchar2,
p_attribute12 varchar2,
p_attribute13 varchar2,
p_attribute14 varchar2,
p_attribute15 varchar2,
p_attribute16 varchar2,
p_attribute17 varchar2,
p_attribute18 varchar2,
p_attribute19 varchar2,
p_attribute20 varchar2,
p_element_information_category varchar2,
p_element_information1 varchar2,
p_element_information2 varchar2,
p_element_information3 varchar2,
p_element_information4 varchar2,
p_element_information5 varchar2,
p_element_information6 varchar2,
p_element_information7 varchar2,
p_element_information8 varchar2,
p_element_information9 varchar2,
p_element_information10 varchar2,
p_element_information11 varchar2,
p_element_information12 varchar2,
p_element_information13 varchar2,
p_element_information14 varchar2,
p_element_information15 varchar2,
p_element_information16 varchar2,
p_element_information17 varchar2,
p_element_information18 varchar2,
p_element_information19 varchar2,
p_element_information20 varchar2,
--
-- The type of element will affect further actions
p_non_payments_flag varchar2,
--
-- The benefits attributes may be needed for defaulting input values
--
p_default_benefit_uom varchar2,
p_contributions_used varchar2,
--
p_third_party_pay_only_flag varchar2,
p_retro_summ_ele_id number,
p_iterative_flag varchar2,
p_iterative_formula_id number,
p_iterative_priority number,
p_process_mode varchar2,
p_grossup_flag varchar2,
p_advance_indicator varchar2,
p_advance_payable varchar2,
p_advance_deduction varchar2,
p_process_advance_entry varchar2,
p_proration_group_id number,
--Code added by prsundar for Continous calculation enhancement
p_proration_formula_id number,
p_recalc_event_group_id number,
p_once_each_period_flag varchar2 default null,
-- Added for FLSA Dynamic Period Allocation
p_time_definition_type varchar2 default null,
p_time_definition_id varchar2 default null,
-- Added for Advance Pay
p_advance_element_type_id number default null,
p_deduction_element_type_id number default null) is
--
cursor csr_new_id is
select pay_element_types_s.nextval
from sys.dual;
select rowid
from pay_element_types_f
where element_type_id = p_element_type_id
and effective_start_date = p_effective_start_date;
hr_utility.set_location ('PAY_ELEMENT_TYPES_PKG.INSERT_ROW',1);
insert into pay_element_types_f ( element_type_id,
effective_start_date,
effective_end_date,
business_group_id,
legislation_code,
formula_id,
input_currency_code,
output_currency_code,
classification_id,
benefit_classification_id,
additional_entry_allowed_flag,
adjustment_only_flag,
closed_for_entry_flag,
element_name,
indirect_only_flag,
multiple_entries_allowed_flag,
multiply_value_flag,
post_termination_rule,
process_in_run_flag,
processing_priority,
processing_type,
standard_link_flag,
comment_id,
description,
legislation_subgroup,
qualifying_age,
qualifying_length_of_service,
qualifying_units,
reporting_name,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute16,
attribute17,
attribute18,
attribute19,
attribute20,
element_information_category,
element_information1,
element_information2,
element_information3,
element_information4,
element_information5,
element_information6,
element_information7,
element_information8,
element_information9,
element_information10,
element_information11,
element_information12,
element_information13,
element_information14,
element_information15,
element_information16,
element_information17,
element_information18,
element_information19,
element_information20,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
third_party_pay_only_flag,
retro_summ_ele_id,
iterative_flag,
iterative_formula_id,
iterative_priority,
process_mode,
grossup_flag,
advance_indicator,
advance_payable,
advance_deduction,
process_advance_entry,
proration_group_id,
proration_formula_id,
recalc_event_group_id,
once_each_period_flag,
time_definition_type,
time_definition_id,
advance_element_type_id,
deduction_element_type_id)
--
values ( p_element_type_id,
p_effective_start_date,
p_effective_end_date,
p_business_group_id,
p_legislation_code,
p_formula_id,
p_input_currency_code,
p_output_currency_code,
p_classification_id,
p_benefit_classification_id,
p_additional_entry_allowed,
p_adjustment_only_flag,
p_closed_for_entry_flag,
-- p_element_name,
-- --
p_base_element_name,
-- --
p_indirect_only_flag,
p_multiple_entries_allowed,
p_multiply_value_flag,
p_post_termination_rule,
p_process_in_run_flag,
p_processing_priority,
p_processing_type,
p_standard_link_flag,
p_comment_id,
p_description,
p_legislation_subgroup,
p_qualifying_age,
p_qualifying_length_of_service,
p_qualifying_units,
p_reporting_name,
p_attribute_category,
p_attribute1,
p_attribute2,
p_attribute3,
p_attribute4,
p_attribute5,
p_attribute6,
p_attribute7,
p_attribute8,
p_attribute9,
p_attribute10,
p_attribute11,
p_attribute12,
p_attribute13,
p_attribute14,
p_attribute15,
p_attribute16,
p_attribute17,
p_attribute18,
p_attribute19,
p_attribute20,
p_element_information_category,
p_element_information1,
p_element_information2,
p_element_information3,
p_element_information4,
p_element_information5,
p_element_information6,
p_element_information7,
p_element_information8,
p_element_information9,
p_element_information10,
p_element_information11,
p_element_information12,
p_element_information13,
p_element_information14,
p_element_information15,
p_element_information16,
p_element_information17,
p_element_information18,
p_element_information19,
p_element_information20,
c_user_id,
sysdate,
c_user_id,
sysdate,
c_login_id,
p_third_party_pay_only_flag,
p_retro_summ_ele_id,
p_iterative_flag,
p_iterative_formula_id,
p_iterative_priority,
p_process_mode,
p_grossup_flag,
p_advance_indicator,
p_advance_payable,
p_advance_deduction,
p_process_advance_entry,
p_proration_group_id,
p_proration_formula_id,
p_recalc_event_group_id,
p_once_each_period_flag,
p_time_definition_type,
p_time_definition_id,
p_advance_element_type_id,
p_deduction_element_type_id);
insert into PAY_ELEMENT_TYPES_F_TL (
ELEMENT_TYPE_ID,
ELEMENT_NAME,
REPORTING_NAME,
DESCRIPTION,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATED_BY,
CREATION_DATE,
LANGUAGE,
SOURCE_LANG
) select
P_ELEMENT_TYPE_ID,
P_ELEMENT_NAME,
P_REPORTING_NAME,
P_DESCRIPTION,
sysdate,
c_user_id,
c_user_id,
c_login_id,
sysdate,
L.LANGUAGE_CODE,
userenv('LANG')
from FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B')
and not exists
(select NULL
from PAY_ELEMENT_TYPES_F_TL T
where T.ELEMENT_TYPE_ID = P_ELEMENT_TYPE_ID
and T.LANGUAGE = L.LANGUAGE_CODE);
hr_utility.set_location ('PAY_ELEMENT_TYPES_PKG.INSERT_ROW',2);
hr_utility.set_message_token ('PROCEDURE','PAY_ELEMENT_TYPES_PKG.INSERT_ROW');
hr_utility.set_location ('PAY_ELEMENT_TYPES_PKG.INSERT_ROW',3);
hr_utility.set_location ('PAY_ELEMENT_TYPES_PKG.INSERT_ROW',4);
pay_input_values_pkg.insert_row (
-- change 115.10
p_base_name => 'Pay Value',
p_element_type_id => p_element_type_id,
p_effective_start_date => p_effective_start_date,
p_effective_end_date => p_effective_end_date,
p_legislation_code => p_legislation_code,
p_business_group_id => p_business_group_id,
p_legislation_subgroup => p_legislation_subgroup,
p_input_value_id => g_dummy_number,
p_rowid => g_dummy_char );
pay_input_values_pkg.insert_row (
--
p_element_type_id => p_element_type_id,
p_effective_start_date => p_effective_start_date,
p_effective_end_date => p_effective_end_date,
p_legislation_code => p_legislation_code,
p_business_group_id => p_business_group_id,
p_legislation_subgroup => p_legislation_subgroup,
p_input_value_id => g_dummy_number,
p_rowid => g_dummy_char,
-- change 115.10
--p_name => 'Coverage',
p_base_name => 'Coverage',
p_display_sequence => 1,
p_hot_default_flag => 'N',
p_mandatory_flag => 'Y',
p_lookup_type => 'US_BENEFIT_COVERAGE',
p_generate_db_items_flag=> 'Y',
p_uom => 'C' );
pay_input_values_pkg.insert_row (
--
p_element_type_id => p_element_type_id,
p_effective_start_date => p_effective_start_date,
p_effective_end_date => p_effective_end_date,
p_legislation_code => p_legislation_code,
p_business_group_id => p_business_group_id,
p_legislation_subgroup => p_legislation_subgroup,
p_input_value_id => g_dummy_number,
p_rowid => g_dummy_char,
-- change 115.10
--p_name => 'ER Contr',
p_base_name => 'ER Contr',
p_display_sequence => 2,
p_hot_default_flag => 'N',
p_mandatory_flag => 'N',
p_generate_db_items_flag=> 'Y',
p_uom => p_default_benefit_uom);
pay_input_values_pkg.insert_row (
--
p_element_type_id => p_element_type_id,
p_effective_start_date => p_effective_start_date,
p_effective_end_date => p_effective_end_date,
p_legislation_code => p_legislation_code,
p_business_group_id => p_business_group_id,
p_legislation_subgroup => p_legislation_subgroup,
p_input_value_id => g_dummy_number,
p_rowid => g_dummy_char,
-- change 115.10
--p_name => 'EE Contr',
p_base_name => 'EE Contr',
p_display_sequence => 3,
p_hot_default_flag => 'N',
p_mandatory_flag => 'N',
p_generate_db_items_flag=> 'Y',
p_uom => p_default_benefit_uom);
hr_utility.set_location ('PAY_ELEMENT_TYPES_PKG.INSERT_ROW',5);
pay_sub_class_rules_pkg.insert_defaults (
--
p_element_type_id,
p_classification_id,
p_effective_start_date,
p_effective_end_date,
p_business_group_id,
p_legislation_code);
end insert_row;
procedure UPDATE_ROW(
--
--******************************************************************************
--* Handles the updating of the base table for the form which is based on a *
--* non-updatable view. It also ensures the integrity of data is maintained *
--* according to the business rules. *
--******************************************************************************
--
-- Parameters to be passed in are:
--
-- All base table column values
p_rowid varchar2,
p_element_type_id number,
p_effective_start_date date,
p_effective_end_date date,
p_business_group_id number,
p_legislation_code varchar2,
p_formula_id number,
p_input_currency_code varchar2,
p_output_currency_code varchar2,
p_classification_id number,
p_benefit_classification_id number,
p_additional_entry_allowed varchar2,
p_adjustment_only_flag varchar2,
p_closed_for_entry_flag varchar2,
p_element_name varchar2,
p_indirect_only_flag varchar2,
p_multiple_entries_allowed varchar2,
p_multiply_value_flag varchar2,
p_post_termination_rule varchar2,
p_process_in_run_flag varchar2,
p_processing_priority number,
p_processing_type varchar2,
p_standard_link_flag varchar2,
p_comment_id number,
p_description varchar2,
p_legislation_subgroup varchar2,
p_qualifying_age number,
p_qualifying_length_of_service number,
p_qualifying_units varchar2,
p_reporting_name varchar2,
p_attribute_category varchar2,
p_attribute1 varchar2,
p_attribute2 varchar2,
p_attribute3 varchar2,
p_attribute4 varchar2,
p_attribute5 varchar2,
p_attribute6 varchar2,
p_attribute7 varchar2,
p_attribute8 varchar2,
p_attribute9 varchar2,
p_attribute10 varchar2,
p_attribute11 varchar2,
p_attribute12 varchar2,
p_attribute13 varchar2,
p_attribute14 varchar2,
p_attribute15 varchar2,
p_attribute16 varchar2,
p_attribute17 varchar2,
p_attribute18 varchar2,
p_attribute19 varchar2,
p_attribute20 varchar2,
p_element_information_category varchar2,
p_element_information1 varchar2,
p_element_information2 varchar2,
p_element_information3 varchar2,
p_element_information4 varchar2,
p_element_information5 varchar2,
p_element_information6 varchar2,
p_element_information7 varchar2,
p_element_information8 varchar2,
p_element_information9 varchar2,
p_element_information10 varchar2,
p_element_information11 varchar2,
p_element_information12 varchar2,
p_element_information13 varchar2,
p_element_information14 varchar2,
p_element_information15 varchar2,
p_element_information16 varchar2,
p_element_information17 varchar2,
p_element_information18 varchar2,
p_element_information19 varchar2,
p_element_information20 varchar2,
p_third_party_pay_only_flag varchar2,
p_retro_summ_ele_id number,
p_iterative_flag varchar2,
p_iterative_formula_id number,
p_iterative_priority number,
p_process_mode varchar2,
p_grossup_flag varchar2,
p_advance_indicator varchar2,
p_advance_payable varchar2,
p_advance_deduction varchar2,
p_process_advance_entry varchar2,
p_proration_group_id number,
p_base_element_name varchar2,
p_proration_formula_id number,
p_recalc_event_group_id number,
p_once_each_period_flag varchar2 default null,
-- Added for FLSA Dynamic Period Allocation
p_time_definition_type varchar2 default null,
p_time_definition_id varchar2 default null,
-- Added for Advance Pay Enhancement
p_advance_element_type_id number default null,
p_deduction_element_type_id number default null
)
is
--
begin
--
hr_utility.set_location ('PAY_ELEMENT_TYPES_PKG.UPDATE_ROW',1);
update pay_element_types_f
set
element_type_id = p_element_type_id,
effective_start_date = p_effective_start_date,
effective_end_date = p_effective_end_date,
business_group_id = p_business_group_id,
legislation_code = p_legislation_code,
formula_id = p_formula_id,
input_currency_code = p_input_currency_code,
output_currency_code = p_output_currency_code,
classification_id = p_classification_id,
benefit_classification_id = p_benefit_classification_id,
additional_entry_allowed_flag = p_additional_entry_allowed,
adjustment_only_flag = p_adjustment_only_flag,
closed_for_entry_flag = p_closed_for_entry_flag,
-- --
element_name = p_base_element_name,
-- --
indirect_only_flag = p_indirect_only_flag,
multiple_entries_allowed_flag = p_multiple_entries_allowed,
multiply_value_flag = p_multiply_value_flag,
post_termination_rule = p_post_termination_rule,
process_in_run_flag = p_process_in_run_flag,
processing_priority = p_processing_priority,
processing_type = p_processing_type,
standard_link_flag = p_standard_link_flag,
comment_id = p_comment_id,
description = p_description,
legislation_subgroup = p_legislation_subgroup,
qualifying_age = p_qualifying_age,
qualifying_length_of_service = p_qualifying_length_of_service,
qualifying_units = p_qualifying_units,
reporting_name = p_reporting_name,
attribute_category = p_attribute_category,
attribute1 = p_attribute1,
attribute2 = p_attribute2,
attribute3 = p_attribute3,
attribute4 = p_attribute4,
attribute5 = p_attribute5,
attribute6 = p_attribute6,
attribute7 = p_attribute7,
attribute8 = p_attribute8,
attribute9 = p_attribute9,
attribute10 = p_attribute10,
attribute11 = p_attribute11,
attribute12 = p_attribute12,
attribute13 = p_attribute13,
attribute14 = p_attribute14,
attribute15 = p_attribute15,
attribute16 = p_attribute16,
attribute17 = p_attribute17,
attribute18 = p_attribute18,
attribute19 = p_attribute19,
attribute20 = p_attribute20,
last_update_date = sysdate,
last_updated_by = c_user_id,
last_update_login = c_login_id,
element_information_category = p_element_information_category,
element_information1 = p_element_information1,
element_information2 = p_element_information2,
element_information3 = p_element_information3,
element_information4 = p_element_information4,
element_information5 = p_element_information5,
element_information6 = p_element_information6,
element_information7 = p_element_information7,
element_information8 = p_element_information8,
element_information9 = p_element_information9,
element_information10 = p_element_information10,
element_information11 = p_element_information11,
element_information12 = p_element_information12,
element_information13 = p_element_information13,
element_information14 = p_element_information14,
element_information15 = p_element_information15,
element_information16 = p_element_information16,
element_information17 = p_element_information17,
element_information18 = p_element_information18,
element_information19 = p_element_information19,
element_information20 = p_element_information20,
third_party_pay_only_flag = p_third_party_pay_only_flag,
retro_summ_ele_id = p_retro_summ_ele_id,
iterative_flag = p_iterative_flag,
iterative_formula_id = p_iterative_formula_id,
iterative_priority = p_iterative_priority,
process_mode = p_process_mode ,
grossup_flag = p_grossup_flag,
advance_indicator = p_advance_indicator,
advance_payable = p_advance_payable,
advance_deduction = p_advance_deduction,
process_advance_entry = p_process_advance_entry,
proration_group_id = p_proration_group_id,
proration_formula_id = p_proration_formula_id,
recalc_event_group_id = p_recalc_event_group_id,
once_each_period_flag = p_once_each_period_flag,
time_definition_type = p_time_definition_type,
time_definition_id = p_time_definition_id,
advance_element_type_id = p_advance_element_type_id,
deduction_element_type_id = p_deduction_element_type_id
where rowid = p_rowid;
if (sql%notfound) then -- trap system errors during update
hr_utility.set_message (801,'HR_6153_ALL_PROCEDURE_FAIL');
hr_utility.set_message_token ('PROCEDURE','PAY_ELEMENT_TYPES_PKG.UPDATE_ROW');
update PAY_ELEMENT_TYPES_F_TL
set
ELEMENT_NAME = P_ELEMENT_NAME,
REPORTING_NAME = P_REPORTING_NAME,
DESCRIPTION = P_DESCRIPTION,
last_update_date = sysdate,
last_updated_by = c_user_id,
last_update_login = c_login_id,
SOURCE_LANG = userenv('LANG')
where ELEMENT_TYPE_ID = P_ELEMENT_TYPE_ID
and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
if (sql%notfound) then -- trap system errors during update
hr_utility.set_message (801,'HR_6153_ALL_PROCEDURE_FAIL');
hr_utility.set_message_token ('PROCEDURE','PAY_ELEMENT_TYPES_PKG.UPDATE_TL_ROW');
end update_row;
procedure DELETE_ROW (
--
--*********************************************************
--* Performs deletion of an element type and its children *
--*********************************************************
--
-- Parameters are:
--
p_element_type_id number,
p_rowid varchar2,
--
-- The priority of the element for integrity checks
p_processing_priority number,
--
-- The type of deletion action being performed (Date Track)
p_delete_mode varchar2 default 'DELETE',
--
-- The effective date
p_session_date date default trunc (sysdate),
--
-- The validation period for integrity checks
p_validation_start_date date
default to_date ('01/01/0001','DD/MM/YYYY'),
p_validation_end_date date
default to_date ('31/12/4712','DD/MM/YYYY')
--
--
) is
--
begin
--
if deletion_allowed ( p_element_type_id,
p_processing_priority,
p_validation_start_date,
p_validation_end_date,
p_delete_mode ) then
--
-- Cascade deletion through child entities
--
hr_utility.set_location ('PAY_ELEMENT_TYPES_PKG.DELETE_ROW',1);
pay_input_values_pkg.parent_deleted ( p_element_type_id,
p_session_date,
p_validation_start_date,
p_validation_end_date,
p_delete_mode );
hr_utility.set_location ('PAY_ELEMENT_TYPES_PKG.DELETE_ROW',2);
ben_benefit_contributions_pkg.parent_deleted ( p_element_type_id,
p_delete_mode,
p_session_date,
c_base_table );
hr_utility.set_location ('PAY_ELEMENT_TYPES_PKG.DELETE_ROW',3);
pay_sub_class_rules_pkg.parent_deleted ( p_element_type_id,
p_session_date,
p_validation_start_date,
p_validation_end_date,
p_delete_mode,
c_base_table );
hr_utility.set_location ('PAY_ELEMENT_TYPES_PKG.DELETE_ROW',4);
pay_status_rules_pkg.parent_deleted ( p_element_type_id,
p_session_date,
p_delete_mode );
if p_delete_mode = 'ZAP' then
-- We need to delete the database items for the deleted element
hrdyndbi.delete_element_type_dict (p_element_type_id);
delete from pay_ele_payroll_freq_rules
where element_type_id = p_element_type_id;
pay_retro_comp_usage_internal.delete_child_retro_comp_usages
(p_effective_date => p_session_date
,p_element_type_id => p_element_type_id
);
delete from pay_element_span_usages
where RETRO_ELEMENT_TYPE_ID = p_element_type_id;
hr_utility.trace('Deleted entry in PAY_ELEMENT_SPAN_USAGES with RETRO_ELEMENT_TYPE_ID ' || p_element_type_id );
elsif p_delete_mode = 'DELETE' then
--
-- We need to remove any payroll frequency rules starting after the new end
-- date
--
delete from pay_ele_payroll_freq_rules
where element_type_id = p_element_type_id
and start_date > p_session_date;
hr_utility.set_location ('PAY_ELEMENT_TYPES_PKG.DELETE_ROW',5);
delete
from pay_element_types_f
where rowid = p_rowid;
hr_utility.set_message_token ('PROCEDURE','PAY_ELEMENT_TYPES_PKG.DELETE_ROW');
if p_delete_mode = 'ZAP' then
--
-- delete from MLS table (TL)
--
delete from PAY_ELEMENT_TYPES_F_TL
where ELEMENT_TYPE_ID = P_ELEMENT_TYPE_ID;
hr_utility.set_message_token ('PROCEDURE','PAY_ELEMENT_TYPES_PKG.DELETE_TL_ROW');
end delete_row;
select *
from pay_element_types_f
where rowid = p_rowid
for update of element_type_id nowait;
select ELEMENT_NAME,
REPORTING_NAME,
DESCRIPTION,
decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
from PAY_ELEMENT_TYPES_F_TL
where ELEMENT_TYPE_ID = P_ELEMENT_TYPE_ID
and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
for update of ELEMENT_TYPE_ID nowait;
select DECODE(element_record.time_definition_type, 'N', NULL, element_record.TIME_DEFINITION_TYPE)
into l_time_definition_type
from dual;
function DATE_EFFECTIVELY_UPDATED (
--
--******************************************************************************
--* Returns TRUE if more than one row exists with the parameter element type ID*
--******************************************************************************
--
-- parameters are:
--
p_element_type_id number,
p_rowid varchar2,
p_error_if_true boolean default FALSE
--
) return boolean is
--
v_dated_updates boolean := FALSE;
cursor csr_dated_updates is
select 1
from pay_element_types_f
where element_type_id = p_element_type_id
and rowid <> p_rowid;
hr_utility.set_location ('PAY_ELEMENT_TYPES_PKG.DATE_EFFECTIVELY_UPDATED',1);
open csr_dated_updates;
fetch csr_dated_updates into g_dummy_number;
v_dated_updates := csr_dated_updates%found;
close csr_dated_updates;
if v_dated_updates and p_error_if_true then
hr_utility.set_message (801,'PAY_6460_ELEMENT_NO_PROC_CORR');
return v_dated_updates;
end date_effectively_updated;
select null
from pay_formula_result_rules_f FRR,
pay_status_processing_rules_f SPR
where p_element_type_id = frr.element_type_id
and frr.result_rule_type = 'S'
and spr.STATUS_PROCESSING_RULE_ID = frr.STATUS_PROCESSING_RULE_ID
and spr.element_type_id <> p_element_type_id
and spr.effective_start_date between p_validation_start_date
and p_validation_end_date
and frr.effective_start_date between p_validation_start_date
and p_validation_end_date;
p_DML_action_being_checked varchar2 default 'UPDATE',
p_error_if_true boolean default FALSE
--
) return boolean is
--
v_run_results_exist boolean;
select iv.input_value_id
from pay_input_values_f iv
where iv.element_type_id = p_element_type_id;
select 1
from dual
where exists
(select /*+ ORDERED INDEX(RESULT PAY_RUN_RESULTS_PK)
USE_NL(RESULT ASSIGN PAYROLL) */ 1
from pay_run_result_values VALUE,
pay_run_results RESULT,
pay_assignment_actions ASSIGN,
pay_payroll_actions PAYROLL
where value.run_result_id = result.run_result_id
and assign.assignment_action_id = result.assignment_action_id
and assign.payroll_action_id = payroll.payroll_action_id
and value.input_value_id = p_input_value_id
and payroll.effective_date between p_validation_start_date
and p_validation_end_date);
select 1
from dual
where exists
(select /*+ INDEX(PAYROLL PAY_PAYROLL_ACTIONS_PK)
INDEX(ASSIGN PAY_ASSIGNMENT_ACTIONS_PK) */ 1
from pay_run_results RUN,
pay_payroll_actions PAYROLL,
pay_assignment_actions ASSIGN
where run.element_type_id = p_element_type_id
and assign.assignment_action_id = run.assignment_action_id
and assign.payroll_action_id = payroll.payroll_action_id
and payroll.effective_date between p_validation_start_date
and p_validation_end_date);
select 1
from dual
where exists
(select 1
from pay_run_result_values VALUE
where value.input_value_id = p_input_value_id);
if p_DML_action_being_checked = 'UPDATE' then
hr_utility.set_message (801,'PAY_6909_ELEMENT_NO_UPD_RR');
elsif p_DML_action_being_checked = 'DELETE' then
hr_utility.set_message (801,'PAY_6242_ELEMENTS_NO_DEL_RR');
function UPDATE_RECURRING_RULES_EXIST (
--
--**************************************************************************
--* Returns TRUE if the parameter element type has input values which are *
--* subject to result rules of type update-recurring during the validation *
--* period, and the source element is different from the target element. *
--**************************************************************************
--
-- Parameters are:
--
p_element_type_id number,
p_validation_start_date date,
p_validation_end_date date,
p_error_if_true boolean default FALSE
--
) return boolean is
--
v_update_recurring boolean := FALSE;
select element_type_id
from pay_status_processing_rules_f
where element_type_id = v_element_type_id
and status_processing_rule_id = v_status_processing_rule_id;
hr_utility.set_location ('PAY_ELEMENT_TYPES_PKG.UPDATE_RECURRING_RULES_EXIST',1);
v_update_recurring := (csr_source_element%found
and v_element_type_id <> p_element_type_id);
if v_update_recurring and p_error_if_true then
hr_utility.set_message (801,'HR_6954_PAY_ELE_NO_UPD_REC');
return v_update_recurring;
end update_recurring_rules_exist;
select 1
from per_pay_bases BASIS,
pay_input_values_f IV
where iv.input_value_id = basis.input_value_id
and iv.element_type_id = p_element_type_id;
p_dated_updates out nocopy boolean,
p_update_recurring out nocopy boolean,
p_pay_basis out nocopy boolean,
p_stop_entry_rules out nocopy boolean) is
--
begin
--
hr_utility.set_location ('PAY_ELEMENT_TYPES_PKG.CHECK_RELATIONSHIPS',1);
p_dated_updates := date_effectively_updated ( p_element_type_id,
p_rowid);
p_update_recurring := update_recurring_rules_exist (p_element_type_id,
p_validation_start_date,
p_validation_end_date);
select null
from pay_element_type_rules
where element_type_id = p_element_type_id;
p_DML_action_being_checked varchar2 default 'UPDATE',
p_error_if_true boolean default FALSE
--
) return boolean is
--
v_links_exist boolean;
select 1
from pay_element_links_f
where element_type_id = p_element_type_id
and effective_end_date >= p_validation_start_date
and effective_start_date <= p_validation_end_date;
if p_DML_action_being_checked = 'UPDATE' then
hr_utility.set_message (801,'PAY_6147_ELEMENT_LINK_UPDATE');
elsif p_DML_action_being_checked = 'DELETE' then
hr_utility.set_message (801,'PAY_6155_ELEMENT_NO_DEL_LINK');
select null
from pay_accrual_plans
where accrual_plan_element_type_id = p_element_type_id;
select null
from per_cobra_coverage_benefits_f
where element_type_id = p_element_type_id
and effective_start_date <= p_validation_end_date
and effective_end_date >= p_validation_start_date;
select 1
from ben_benefit_contributions_f
where element_type_id = p_element_type_id
and effective_start_date <= p_validation_end_date
and effective_end_date >= p_validation_start_date;
p_delete_mode varchar2 default 'ZAP'
) return boolean is
--
v_deletion_allowed boolean := TRUE;
if (p_delete_mode = 'DELETE_NEXT_CHANGE'
and priority_result_rule_violated ( p_element_type_id,
p_processing_priority,
p_validation_start_date,
p_validation_end_date,
p_error_if_true => TRUE))
--
or (p_delete_mode <> 'DELETE_NEXT_CHANGE'
--
and (links_exist ( p_element_type_id,
p_validation_start_date,
p_validation_end_date,
p_DML_action_being_checked => 'DELETE',
p_error_if_true => TRUE)
-- Bug # 4991482 : Added to raise an error message if any run results exists.
or run_results_exist ( p_element_type_id,
p_validation_start_date,
p_validation_end_date,
p_DML_action_being_checked => 'DELETE',
p_error_if_true => TRUE)
--
or cobra_benefits_exist (p_element_type_id,
p_validation_start_date,
p_validation_end_date,
p_error_if_true => TRUE)
--
or (p_delete_mode = 'ZAP'
--
and (element_is_in_an_element_set ( p_element_type_id,
p_error_if_true => TRUE)
--
or element_used_as_pay_basis (p_element_type_id,
p_error_if_true => TRUE)
--
or benefit_contributions_exist (p_element_type_id,
p_validation_start_date,
p_validation_end_date,
p_error_if_true => TRUE)
--
or accrual_plan_exists ( p_element_type_id,
p_error_if_true => TRUE)
--
or stop_entry_rules_exist ( p_element_type_id,
p_error_if_true => TRUE)
)
)
or pay_input_values_pkg.cant_delete_all_input_values (
--
p_element_type_id,
p_delete_mode,
p_validation_start_date,
p_validation_end_date,
p_error_if_true => TRUE)
)
)
or dt_api.rows_exist(
p_base_table_name => 'ben_acty_base_rt_f',
p_base_key_column => 'element_type_id',
p_base_key_value => p_element_type_id,
p_from_date => p_validation_start_date,
p_to_date => p_validation_end_date
)
THEN
v_deletion_allowed := FALSE;
select 1
from pay_status_processing_rules_f STATUS,
pay_formula_result_rules_f RESULT,
pay_input_values_f INPUT,
pay_element_types_f ELEMENT
where status.status_processing_rule_id = result.status_processing_rule_id
and result.input_value_id = input.input_value_id
and input.element_type_id = element.element_type_id
and result.result_rule_type = 'I'
and status.element_type_id = p_element_type_id
and element.element_type_id <> p_element_type_id
and element.processing_priority < p_processing_priority
and (status.effective_end_date >= p_validation_start_date
and status.effective_start_date <= p_validation_end_date)
and (result.effective_end_date >= p_validation_start_date
and result.effective_start_date <= p_validation_end_date);
select 1
from pay_status_processing_rules_f STATUS,
pay_formula_result_rules_f RESULT,
pay_input_values_f INPUT,
pay_element_types_f ELEMENT
where result.input_value_id = input.input_value_id
and status.element_type_id = element.element_type_id
and result.status_processing_rule_id= status.status_processing_rule_id
and result.result_rule_type = 'I'
and input.element_type_id = p_element_type_id
and element.element_type_id <> p_element_type_id
and element.processing_priority > p_processing_priority
and (status.effective_end_date >= p_validation_start_date
and status.effective_start_date <= p_validation_end_date)
and (result.effective_end_date >= p_validation_start_date
and result.effective_start_date<= p_validation_end_date);
select null
from pay_element_types_f et,
pay_element_types_f_tl et_tl
where upper(translate(p_element_name,'x_','x '))
= upper(translate(et_tl.element_name,'x_','x '))
and (et.element_type_id <> p_element_type_id
or p_element_type_id is null)
and ( p_business_group_id = et.business_group_id + 0
or ( et.business_group_id is null
and p_legislation_code = et.legislation_code ))
and et_tl.element_type_id = et.element_type_id
and et_tl.language = userenv('LANG');
select min (effective_start_date)
from pay_element_types_f
where element_type_id = p_element_type_id;
select max (effective_end_date)
from pay_element_types_f
where element_type_id = p_element_type_id;
select null
from pay_element_entries_f ENTRY,
pay_element_links_f LINK
where link.element_link_id = entry.element_link_id
and link.element_type_id = p_element_type_id;
delete from PAY_ELEMENT_TYPES_F_TL T
where not exists
(select NULL
from PAY_ELEMENT_TYPES_F B
where B.ELEMENT_TYPE_ID = T.ELEMENT_TYPE_ID
);
update PAY_ELEMENT_TYPES_F_TL T set (
ELEMENT_NAME,
REPORTING_NAME,
DESCRIPTION
) = (select
B.ELEMENT_NAME,
B.REPORTING_NAME,
B.DESCRIPTION
from PAY_ELEMENT_TYPES_F_TL B
where B.ELEMENT_TYPE_ID = T.ELEMENT_TYPE_ID
and B.LANGUAGE = T.SOURCE_LANG)
where (
T.ELEMENT_TYPE_ID,
T.LANGUAGE
) in (select
SUBT.ELEMENT_TYPE_ID,
SUBT.LANGUAGE
from PAY_ELEMENT_TYPES_F_TL SUBB, PAY_ELEMENT_TYPES_F_TL SUBT
where SUBB.ELEMENT_TYPE_ID = SUBT.ELEMENT_TYPE_ID
and SUBB.LANGUAGE = SUBT.SOURCE_LANG
and (SUBB.ELEMENT_NAME <> SUBT.ELEMENT_NAME
or SUBB.REPORTING_NAME <> SUBT.REPORTING_NAME
or (SUBB.REPORTING_NAME is null and SUBT.REPORTING_NAME is not null)
or (SUBB.REPORTING_NAME is not null and SUBT.REPORTING_NAME is null)
or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
));
insert into PAY_ELEMENT_TYPES_F_TL (
ELEMENT_TYPE_ID,
ELEMENT_NAME,
REPORTING_NAME,
DESCRIPTION,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATED_BY,
CREATION_DATE,
LANGUAGE,
SOURCE_LANG
) select
B.ELEMENT_TYPE_ID,
B.ELEMENT_NAME,
B.REPORTING_NAME,
B.DESCRIPTION,
B.LAST_UPDATE_DATE,
B.LAST_UPDATED_BY,
B.LAST_UPDATE_LOGIN,
B.CREATED_BY,
B.CREATION_DATE,
L.LANGUAGE_CODE,
B.SOURCE_LANG
from PAY_ELEMENT_TYPES_F_TL B, FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B')
and B.LANGUAGE = userenv('LANG')
and not exists
(select NULL
from PAY_ELEMENT_TYPES_F_TL T
where T.ELEMENT_TYPE_ID = B.ELEMENT_TYPE_ID
and T.LANGUAGE = L.LANGUAGE_CODE);
SELECT count(*) INTO result
FROM pay_element_types_f
WHERE nvl(ELEMENT_NAME,'~null~') = nvl(X_E_ELEMENT_NAME,'~null~')
and nvl(LEGISLATION_CODE,'~null~') = nvl(X_E_LEGISLATION_CODE,'~null~')
and EFFECTIVE_START_DATE = X_E_EFFECTIVE_START_DATE
and EFFECTIVE_end_DATE = X_E_EFFECTIVE_END_DATE
and X_E_EFFECTIVE_START_DATE is not NULL
and X_E_EFFECTIVE_END_DATE is not NULL
and BUSINESS_GROUP_ID is NULL;
select element_type_id
from pay_element_types_f
where nvl(ELEMENT_NAME,'~null~') = nvl(X_E_ELEMENT_NAME,'~null~')
and nvl(LEGISLATION_CODE,'~null~') = nvl(X_E_LEGISLATION_CODE,'~null~')
and EFFECTIVE_START_DATE = X_E_EFFECTIVE_START_DATE
and EFFECTIVE_end_DATE = X_E_EFFECTIVE_END_DATE
and X_E_EFFECTIVE_START_DATE is not NULL
and X_E_EFFECTIVE_END_DATE is not NULL
and BUSINESS_GROUP_ID is NULL
;
select element_name
, language
from pay_element_types_f_tl
where element_type_id = p_element_type_id
and p_language in (language, source_lang)
;
UPDATE pay_element_types_f_tl
SET element_name = nvl(x_element_name,element_name),
reporting_name = nvl(x_reporting_name,reporting_name),
description = nvl(x_description,description),
last_update_date = SYSDATE,
last_updated_by = decode(x_owner,'SEED',1,0),
last_update_login = 0,
source_lang = l_lang
WHERE l_lang IN (language,source_lang)
AND element_type_id = l_element_type_id
;