The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 'Y'
FROM ben_benefit_contributions_f bc
WHERE ( bc.benefit_contribution_id <> p_benefit_contribution_id
OR p_benefit_contribution_id IS NULL )
AND bc.element_type_id = p_element_type_id
AND bc.business_group_id + 0 = p_business_group_id
AND bc.coverage_type = p_coverage_type
AND ( p_effective_start_date BETWEEN
bc.effective_start_date AND bc.effective_end_date
OR
p_effective_end_date BETWEEN
bc.effective_start_date AND bc.effective_end_date
);
SELECT ben_benefit_contributions_s.nextval
FROM sys.dual;
SELECT 'Y'
FROM ben_benefit_contributions_f bc
WHERE bc.benefit_contribution_id = p_benefit_contribution_id
AND bc.business_group_id + 0 = p_business_group_id
AND bc.effective_end_date > p_effective_end_date;
p_dt_delete_mode VARCHAR2,
p_validation_start_date DATE,
p_validation_end_date DATE,
p_element_effective_start_date DATE ) IS
--
-- declare local variables
--
l_element_exists VARCHAR2(1) := 'N';
SELECT 'Y'
FROM pay_element_types_f et
WHERE et.element_type_id = p_element_type_id
AND et.effective_end_date = to_date('31-12-4712','DD-MM-YYYY')
AND p_session_date BETWEEN
et.effective_start_date and et.effective_end_date;
SELECT
iv_cov.input_value_id iv_cov
FROM
pay_input_values_f iv_cov,
pay_element_types_f et
WHERE
et.element_type_id = p_element_type_id
AND
iv_cov.element_type_id = et.element_type_id AND
UPPER(iv_cov.name) = 'COVERAGE';
SELECT
'Y'
FROM
dual
WHERE EXISTS (
SELECT
'x'
FROM
pay_element_entry_values_f eev_cov,
pay_element_entry_values_f eev,
pay_element_entries_f ee,
pay_element_links_f el
WHERE
el.element_type_id = p_element_type_id AND
p_validation_start_date
BETWEEN el.effective_start_date AND
el.effective_end_date
AND
ee.element_link_id = el.element_link_id AND
(ee.effective_start_date
BETWEEN p_validation_start_date AND
p_validation_end_date OR
ee.effective_end_date
BETWEEN p_validation_start_date AND
p_validation_end_date
)
-- p_validation_start_date
-- BETWEEN ee.effective_start_date AND
-- ee.effective_end_date
AND
eev_cov.element_entry_id = ee.element_entry_id AND
eev_cov.input_value_id = l_iv_cov_id AND
eev_cov.screen_entry_value = p_coverage_type AND
(eev_cov.effective_start_date
BETWEEN p_validation_start_date AND
p_validation_end_date OR
eev_cov.effective_end_date
BETWEEN p_validation_start_date AND
p_validation_end_date
)
-- p_validation_start_date
-- BETWEEN eev_cov.effective_start_date AND
-- eev_cov.effective_end_date
AND
eev.element_entry_id = ee.element_entry_id AND
eev.input_value_id = p_iv_er_id AND
(eev.effective_start_date
BETWEEN p_validation_start_date AND
p_validation_end_date OR
eev.effective_end_date
BETWEEN p_validation_start_date AND
p_validation_end_date
) AND
-- p_validation_start_date
-- BETWEEN eev.effective_start_date AND
-- eev.effective_end_date AND
eev.screen_entry_value IS NULL
);
SELECT
'Y'
FROM
dual
WHERE EXISTS (
SELECT
'x'
FROM
pay_element_entry_values_f eev_cov,
pay_element_entry_values_f eev,
pay_element_entries_f ee,
pay_element_links_f el
WHERE
el.element_type_id = p_element_type_id
AND
ee.element_link_id = el.element_link_id
AND
eev_cov.element_entry_id = ee.element_entry_id AND
eev_cov.input_value_id = l_iv_cov_id AND
eev_cov.screen_entry_value = p_coverage_type
AND
eev.element_entry_id = ee.element_entry_id AND
eev.input_value_id = p_iv_er_id AND
eev.screen_entry_value IS NULL
);
IF (p_dt_delete_mode = 'ZAP')
THEN
--
-- check if referenced by ANY element entries
--
--
hr_utility.set_location('hr_ben_ref_chk', 5);