The following lines contain the word 'select', 'insert', 'update' or 'delete':
select pbg.security_group_id
from per_business_groups pbg
, pay_element_types_f etp
where etp.element_type_id = p_element_type_id
and pbg.business_group_id = etp.business_group_id;
select pbg.legislation_code
from per_business_groups pbg
, pay_element_types_f etp
where etp.element_type_id = p_element_type_id
and pbg.business_group_id (+) = etp.business_group_id;
Procedure chk_non_updateable_args
(p_effective_date in date
,p_rec in pay_etp_shd.g_rec_type
) IS
--
l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
End chk_non_updateable_args;
Procedure dt_update_validate
(p_datetrack_mode in varchar2
,p_validation_start_date in date
,p_validation_end_date in date
) Is
--
l_proc varchar2(72) := g_package||'dt_update_validate';
End dt_update_validate;
Procedure dt_delete_validate
(p_element_type_id in number
,p_datetrack_mode in varchar2
,p_validation_start_date in date
,p_validation_end_date in date
) Is
--
l_proc varchar2(72) := g_package||'dt_delete_validate';
If (p_datetrack_mode = hr_api.g_delete or
p_datetrack_mode = hr_api.g_zap) then
--
--
-- Ensure the arguments are not null
--
hr_api.mandatory_arg_error
(p_api_name => l_proc
,p_argument => 'validation_start_date'
,p_argument_value => p_validation_start_date
);
End dt_delete_validate;
(p_insert IN boolean
,p_business_group_id IN number
,p_legislation_code IN varchar2
,p_legislation_subgroup IN varchar2 DEFAULT NULL) IS
--
BEGIN
--
-- Call the supporting procedure to check startup mode
--
IF (p_insert) THEN
hr_startup_data_api_support.chk_startup_action
(p_generic_allowed => TRUE
,p_startup_allowed => TRUE
,p_user_allowed => TRUE
,p_business_group_id => p_business_group_id
,p_legislation_code => p_legislation_code
,p_legislation_subgroup => p_legislation_subgroup
);
select '1'
from hr_organization_units
where business_group_id = p_business_group_id;
select '1'
from fnd_territories
where territory_code = p_legislation_code;
select '1'
from ff_formulas_f fml, ff_formula_types ftp
where fml.formula_id = p_formula_id
and p_effective_date between fml.effective_start_date
and fml.effective_end_date
and ftp.formula_type_id = fml.formula_type_id
and upper(ftp.formula_type_name) = 'ELEMENT SKIP';
select '1'
from fnd_currencies
where currency_code = p_currency_code
and ( ( enabled_flag = 'Y') or
( hr_startup_data_api_support.g_startup_mode IN ('STARTUP') ))
and currency_flag = 'Y'
and p_effective_date between nvl(start_date_active,p_effective_date)
and nvl(end_date_active,p_effective_date);
select non_payments_flag
from pay_element_classifications
where classification_id = p_classification_id;
select rule_mode
from pay_legislation_rules
where legislation_code = hr_api.return_legislation_code
(p_business_group_id)
and rule_type = 'DC';
select '1'
from pay_element_classifications
where classification_id = p_classification_id
and nvl(balance_initialization_flag,'N') = 'Y';
select '1'
from ben_benefit_classifications
where benefit_classification_id = p_benefit_classification_id
and cobra_flag = 'Y';
If pay_element_types_pkg.update_recurring_rules_exist
(p_element_type_id
,p_validation_start_date
,p_validation_end_date) Then
--
fnd_message.set_name('PAY','HR_6953_PAY_ELE_NO_STOP_ENTRY');
select default_high_priority,
default_low_priority
from pay_element_classifications
where classification_id = p_classification_id;
select formula_type_name
from ff_formula_types ftp, ff_formulas_f fml
where fml.formula_id = p_iterative_formula_id
and p_effective_date between fml.effective_start_date
and fml.effective_end_date
and ftp.formula_type_id = fml.formula_type_id;
select '1', event_group_type
from pay_event_groups
where event_group_id = p_event_group_id;
select 'Y'
from pay_time_definitions
where time_definition_id = p_time_definition_id
AND DEFINITION_TYPE = 'S'
and nvl (business_group_id, nvl(p_business_group_id, -1)) = nvl(p_business_group_id,-1)
and nvl (legislation_code, nvl(p_legislation_code, '-1')) = nvl(p_legislation_code, '-1');
if p_datetrack_mode = 'INSERT' then
-- Normal element with Advance Element
if p_advance_payable = 'Y' and p_advance_element_type_id is not null then
select count(*) into l_count from pay_element_types_f
where element_type_id = p_advance_element_type_id and
effective_start_date <= p_validation_start_date and
effective_end_date >= p_validation_end_date and
p_effective_date between effective_start_date and effective_end_date;
select count(*)
into l_count
from pay_element_types_f
where element_type_id = p_deduction_element_type_id and
effective_start_date <= p_validation_start_date and
effective_end_date >= p_validation_end_date and
p_effective_date between effective_start_date and effective_end_date;
-- No INSERT Validation For Advance Or Deduction Element
end if; -- end of Insert
if p_datetrack_mode = 'UPDATE' or p_datetrack_mode = 'CORRECTION' then
-- UPDATE Validation For Normal Advance Element
if p_advance_payable = 'Y' and p_advance_element_type_id is not null then
select count(*)
into l_count
from pay_element_types_f
where element_type_id = p_advance_element_type_id and
effective_start_date <= p_validation_start_date and
effective_end_date >= p_validation_end_date and
p_effective_date between effective_start_date and effective_end_date;
-- UPDATE Validation For Normal Deduction Element
if p_advance_deduction = 'Y' and p_deduction_element_type_id is not null then
select count(*)
into l_count
from pay_element_types_f
where element_type_id = p_deduction_element_type_id and
effective_start_date <= p_validation_start_date and
effective_end_date >= p_validation_end_date and
p_effective_date between effective_start_date and effective_end_date;
-- No UPDATE Validation For Advance/Deduction Element
end if; -- end of Update
if p_datetrack_mode = 'DELETE_NEXT_CHANGE' or p_datetrack_mode = 'DELETE_FUTURE_CHANGE' then
if p_advance_payable = 'Y' and p_advance_element_type_id is not null then
--
select count(*) into l_count from pay_element_types_f
where element_type_id = p_advance_element_type_id and
effective_start_date <= p_validation_start_date and
effective_end_date >= p_validation_end_date;
fnd_message.set_token('DELETE_MODE', p_datetrack_mode);
-- Delete Next Change Validation for Normal Deduction Element
if p_advance_deduction = 'Y' and p_deduction_element_type_id is not null then
--
select count(*) into l_count from pay_element_types_f
where element_type_id = p_deduction_element_type_id and
effective_start_date <= p_validation_start_date and
effective_end_date >= p_validation_end_date;
fnd_message.set_token('DELETE_MODE', p_datetrack_mode);
end if; -- end of delete_next_change
select count(*) into l_count from pay_element_types_f where advance_element_type_id = p_element_type_id ;
fnd_message.set_name('PAY','PAY_33450_ADVANCE_ELE_DELETE');
fnd_message.set_token('DELETE_MODE', p_datetrack_mode);
select count(*) into l_count from pay_element_types_f where deduction_element_type_id = p_element_type_id ;
fnd_message.set_name('PAY','PAY_33450_ADVANCE_ELE_DELETE');
fnd_message.set_token('DELETE_MODE', p_datetrack_mode);
if p_datetrack_mode = 'DELETE' and ((p_advance_payable = 'N' OR p_advance_payable is null) and (p_advance_deduction = 'N' OR p_advance_deduction is null)) then
select count(*) into l_count
from pay_element_types_f
where (advance_element_type_id = p_element_type_id or
deduction_element_type_id = p_element_type_id );
select count(*) into l_a_count from pay_element_types_f
where advance_element_type_id = p_element_type_id
and not (effective_start_date >= p_validation_start_date
and effective_end_date <= p_validation_end_date)
and p_effective_date between effective_start_date and effective_end_date;
select count(*) into l_d_count from pay_element_types_f
where deduction_element_type_id = p_element_type_id
and not (effective_start_date >= p_validation_start_date
and effective_end_date <= p_validation_end_date)
and p_effective_date between effective_start_date and effective_end_date;
fnd_message.set_name('PAY','PAY_33450_ADVANCE_ELE_DELETE');
fnd_message.set_token('DELETE_MODE', p_datetrack_mode);
Procedure chk_update_allowed
(p_element_type_id in number
,p_indirect_only_flag in varchar2
,p_additional_entry_allowed_flg in varchar2
,p_standard_link_flag in varchar2
,p_adjustment_only_flag in varchar2
,p_post_termination_rule in varchar2
,p_process_in_run_flag in varchar2
,p_validation_start_date in date
,p_validation_end_date in date
,p_once_each_period_flag in varchar2
) is
--
l_proc varchar2(72) := g_package||'chk_update_allowed';
fnd_message.set_name('PAY','PAY_6147_ELEMENT_LINK_UPDATE');
Procedure chk_delete_allowed
(p_element_type_id in number
,p_processing_priority in number
,p_validation_start_date in date
,p_validation_end_date in date
,p_datetrack_delete_mode in varchar2
) is
--
l_proc varchar2(72) := g_package||'chk_delete_allowed';
select null
from pay_accrual_plans
where accrual_plan_element_type_id = p_element_type_id;
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;
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;
If p_datetrack_delete_mode = 'DELETE_NEXT_CHANGE' Then
If pay_element_types_pkg.priority_result_rule_violated
(p_element_type_id,
p_processing_priority,
p_validation_start_date,
p_validation_end_date) Then
--
fnd_message.set_name('PAY','PAY_6149_ELEMENT_PRIORITY_UPD');
If p_datetrack_delete_mode = 'DELETE' Then
--
If pay_element_types_pkg.links_exist
(p_element_type_id,
p_validation_start_date,
p_validation_end_date) Then
--
fnd_message.set_name('PAY','PAY_6155_ELEMENT_NO_DEL_LINK');
If p_datetrack_delete_mode = 'ZAP' Then
--
If pay_element_types_pkg.element_is_in_an_element_set
(p_element_type_id) Then
--
fnd_message.set_name('PAY','PAY_6713_ELEMENT_NO_DEL_RULE');
Procedure insert_validate
(p_rec in pay_etp_shd.g_rec_type
,p_effective_date in date
,p_datetrack_mode in varchar2
,p_validation_start_date in date
,p_validation_end_date in date
,p_processing_priority_warning out nocopy boolean
) is
--
l_proc varchar2(72) := g_package||'insert_validate';
End insert_validate;
Procedure update_validate
(p_rec in pay_etp_shd.g_rec_type
,p_effective_date in date
,p_datetrack_mode in varchar2
,p_validation_start_date in date
,p_validation_end_date in date
,p_processing_priority_warning out nocopy boolean
) is
--
l_proc varchar2(72) := g_package||'update_validate';
chk_update_allowed
(p_rec.element_type_id
,p_rec.indirect_only_flag
,p_rec.additional_entry_allowed_flag
,p_rec.standard_link_flag
,p_rec.adjustment_only_flag
,p_rec.post_termination_rule
,p_rec.process_in_run_flag
,p_validation_start_date
,p_validation_end_date
,p_rec.once_each_period_flag
);
dt_update_validate
(p_datetrack_mode => p_datetrack_mode
,p_validation_start_date => p_validation_start_date
,p_validation_end_date => p_validation_end_date
);
chk_non_updateable_args
(p_effective_date => p_effective_date
,p_rec => p_rec
);
End update_validate;
Procedure delete_validate
(p_rec in pay_etp_shd.g_rec_type
,p_effective_date in date
,p_datetrack_mode in varchar2
,p_validation_start_date in date
,p_validation_end_date in date
) is
--
l_proc varchar2(72) := g_package||'delete_validate';
chk_delete_allowed
(p_rec.element_type_id
,p_rec.processing_priority
,p_validation_start_date
,p_validation_end_date
,p_datetrack_mode);
dt_delete_validate
(p_datetrack_mode => p_datetrack_mode
,p_validation_start_date => p_validation_start_date
,p_validation_end_date => p_validation_end_date
,p_element_type_id => p_rec.element_type_id
);
End delete_validate;