[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE delete_from_engine_tables
(p_cagr_entitlement_line_id IN NUMBER
,p_effective_date IN DATE) IS
--
CURSOR csr_entitlement_results IS
SELECT cagr_request_id,
cagr_entitlement_result_id,
assignment_id
FROM per_cagr_entitlement_results cer
WHERE cer.cagr_entitlement_line_id = p_cagr_entitlement_line_id
FOR UPDATE;
SELECT cagr_entitlement_result_id
FROM per_cagr_entitlement_results cer
WHERE cer.cagr_entitlement_line_id <> p_cagr_entitlement_line_id
AND cer.cagr_request_id = p_cagr_request_id
AND cer.cagr_entitlement_result_id <> p_cagr_entitlement_result_id
AND cer.assignment_id = p_assignment_id;
l_proc VARCHAR2(72) := g_package||'delete_from_engine_tables';
-- assignment and request id as the line being deleted then
-- delete from retained rights, log and requests
--
IF csr_chk_for_other_lines%NOTFOUND THEN
--
hr_utility.set_location(l_proc,30);
DELETE FROM per_cagr_retained_rights
WHERE cagr_entitlement_line_id = p_cagr_entitlement_line_id;
DELETE FROM per_cagr_entitlement_results
WHERE cagr_entitlement_result_id = c_results.cagr_entitlement_result_id;
DELETE FROM per_cagr_log
WHERE cagr_request_id = c_results.cagr_request_id;
DELETE FROM per_cagr_requests
WHERE cagr_request_id = c_results.cagr_request_id;
DELETE FROM per_cagr_retained_rights
WHERE cagr_entitlement_result_id = c_results.cagr_entitlement_result_id;
DELETE FROM per_cagr_entitlement_results
WHERE cagr_entitlement_result_id = c_results.cagr_entitlement_result_id;
END delete_from_engine_tables;
SELECT pl_id,
pca.business_group_id,
cei.opt_id
FROM per_collective_agreements pca,
per_cagr_entitlements pce,
per_cagr_entitlement_items cei
WHERE pca.collective_agreement_id = pce.collective_agreement_id
AND pce.cagr_entitlement_id = p_cagr_entitlement_id
and cei.cagr_entitlement_item_id = pce.cagr_entitlement_item_id;
PROCEDURE update_entitlement_line
(p_validate in boolean default false
,p_cagr_entitlement_line_id in NUMBER
,p_mandatory in VARCHAR2 default hr_api.g_VARCHAR2
,p_value in VARCHAR2 default hr_api.g_VARCHAR2
,p_range_from in VARCHAR2 default hr_api.g_VARCHAR2
,p_range_to in VARCHAR2 default hr_api.g_VARCHAR2
,p_effective_start_date out nocopy DATE
,p_effective_END_date out nocopy DATE
,p_grade_spine_id in NUMBER default hr_api.g_number
,p_parent_spine_id in NUMBER default hr_api.g_number
,p_cagr_entitlement_id in NUMBER default hr_api.g_number
,p_status in VARCHAR2 default hr_api.g_VARCHAR2
,p_oipl_id in NUMBER default hr_api.g_number
,p_eligy_prfl_id in NUMBER default hr_api.g_number
,p_step_id in NUMBER default hr_api.g_number
,p_from_step_id in NUMBER default hr_api.g_number
,p_to_step_id in NUMBER default hr_api.g_number
,p_object_version_number in out nocopy NUMBER
,p_effective_date in DATE
,p_datetrack_mode in VARCHAR2
) is
--
CURSOR get_prtn_elig_prfl_id IS
SELECT b2.prtn_elig_prfl_id,
b2.object_version_number
FROM ben_prtn_elig_prfl_f b2,
ben_prtn_elig_f b1
WHERE p_effective_date BETWEEN b2.effective_start_Date
AND b2.effective_end_date
AND b2.prtn_elig_id = b1.prtn_elig_id
AND b1.oipl_id = p_oipl_id
AND p_effective_date BETWEEN b1.effective_start_date
AND b1.effective_end_date;
l_proc VARCHAR2(72) := g_package||'update_entitlement_line';
SAVEPOINT update_entitlement_line;
hr_cagr_ent_lines_bk2.update_entitlement_line_b
(
p_cagr_entitlement_line_id => p_cagr_entitlement_line_id
,p_mandatory => p_mandatory
,p_value => p_value
,p_range_from => p_range_from
,p_range_to => p_range_to
,p_grade_spine_id => p_grade_spine_id
,p_parent_spine_id => p_parent_spine_id
,p_cagr_entitlement_id => p_cagr_entitlement_id
,p_status => p_status
,p_oipl_id => p_oipl_id
,p_eligy_prfl_id => p_eligy_prfl_id
,p_step_id => p_step_id
,p_from_step_id => p_from_step_id
,p_to_step_id => p_to_step_id
,p_object_version_number => p_object_version_number
,p_effective_date => l_effective_date
,p_datetrack_mode => p_datetrack_mode
);
(p_module_name => 'UPDATE_entitlement_line'
,p_hook_type => 'BP'
);
ben_prtn_elig_prfl_api.update_prtn_elig_prfl
(p_validate => p_validate
,p_PRTN_ELIG_PRFL_ID => l_prtn_elig_prfl_id
,p_EFFECTIVE_START_DATE => l_prtn_eff_st_date
,p_EFFECTIVE_END_DATE => l_prtn_eff_end_date
--,p_BUSINESS_GROUP_ID => l_business_group_id
--,p_MNDTRY_FLAG => 'Y'
--,p_PRTN_ELIG_ID => to_number(name_in('CEP.PRTN_ELIG_ID'))
,p_ELIGY_PRFL_ID => p_eligy_prfl_id
--,p_oipl_id => l_oipl_id
,p_object_version_number => l_prtn_ovn
,p_effective_date => l_effective_date
,p_datetrack_mode => p_datetrack_mode);
hr_cagr_ent_lines_bk2.update_entitlement_line_a
(
p_cagr_entitlement_line_id => p_cagr_entitlement_line_id
,p_mandatory => p_mandatory
,p_value => p_value
,p_range_from => p_range_from
,p_range_to => p_range_to
,p_effective_start_date => l_effective_start_date
,p_effective_END_date => l_effective_END_date
,p_grade_spine_id => p_grade_spine_id
,p_parent_spine_id => p_parent_spine_id
,p_cagr_entitlement_id => p_cagr_entitlement_id
,p_status => p_status
,p_oipl_id => p_oipl_id
,p_eligy_prfl_id => p_eligy_prfl_id
,p_step_id => p_step_id
,p_from_step_id => p_from_step_id
,p_to_step_id => p_to_step_id
,p_object_version_number => l_object_version_number
,p_effective_date => TRUNC(p_effective_date)
,p_datetrack_mode => p_datetrack_mode
);
(p_module_name => 'UPDATE_entitlement_line'
,p_hook_type => 'AP'
);
ROLLBACK TO update_entitlement_line;
ROLLBACK TO update_entitlement_line;
END update_entitlement_line;
PROCEDURE delete_entitlement_line
(p_validate in boolean default false
,p_cagr_entitlement_line_id in NUMBER
,p_effective_start_date out nocopy DATE
,p_effective_END_date out nocopy DATE
,p_object_version_number in out nocopy NUMBER
,p_effective_date in DATE
,p_datetrack_mode in VARCHAR2
) is
--
-- Declare cursors and local variables
--
CURSOR csr_oipl_details IS
SELECT bof.oipl_id,
bof.object_version_number,
bpe.prtn_elig_id,
bpe.object_version_number,
bep.prtn_elig_prfl_id,
bep.object_version_number
FROM ben_prtn_elig_prfl_f bep,
ben_prtn_elig_f bpe,
ben_oipl_f bof,
per_cagr_entitlement_lines_f pcl
WHERE bep.prtn_elig_id = bpe.prtn_elig_id
AND bpe.oipl_id = bof.oipl_id
AND bof.oipl_id = pcl.oipl_id
AND pcl.cagr_entitlement_line_id = p_cagr_entitlement_line_id;
l_proc VARCHAR2(72) := g_package||'update_entitlement_line';
SAVEPOINT delete_entitlement_line;
hr_cagr_ent_lines_bk3.delete_entitlement_line_b
(p_cagr_entitlement_line_id => p_cagr_entitlement_line_id
,p_object_version_number => p_object_version_number
,p_effective_date => TRUNC(p_effective_date)
,p_datetrack_mode => p_datetrack_mode);
(p_module_name => 'DELETE_entitlement_line'
,p_hook_type => 'BP'
);
-- Delete any results, retained rights, logs
-- that are linked to the entitlement line
--
delete_from_engine_tables
(p_cagr_entitlement_line_id => p_cagr_entitlement_line_id
,p_effective_date => p_effective_date);
-- Delete option in plans created
-- for the entitlement line
--
hr_utility.set_location(l_proc, 30);
ben_prtn_elig_prfl_api.delete_prtn_elig_prfl
(p_validate => p_validate
,p_prtn_elig_prfl_id => l_prtn_elig_prfl_id
,p_effective_start_date => l_start_date
,p_effective_end_date => l_end_date
,p_object_version_number => l_prtn_elig_prfl_ovn
,p_effective_date => p_effective_Date
,p_datetrack_mode => p_datetrack_mode);
ben_participation_elig_api.delete_participation_elig
(p_validate => p_validate
,p_prtn_elig_id => l_prtn_elig_id
,p_effective_start_date => l_start_date
,p_effective_end_date => l_end_date
,p_object_version_number => l_prtn_elig_ovn
,p_effective_date => p_effective_date
,p_datetrack_mode => p_datetrack_mode);
ben_option_in_plan_api.delete_option_in_plan
(p_validate => p_validate
,p_oipl_id => l_oipl_id
,p_effective_start_date => l_start_date
,p_effective_end_date => l_end_date
,p_object_version_number => l_oipl_ovn
,p_effective_date => p_effective_date
,p_datetrack_mode => p_datetrack_mode);
hr_cagr_ent_lines_bk3.delete_entitlement_line_a
(
p_cagr_entitlement_line_id => p_cagr_entitlement_line_id
,p_effective_start_date => l_effective_start_date
,p_effective_END_date => l_effective_END_date
,p_object_version_number => l_object_version_number
,p_effective_date => TRUNC(p_effective_date)
,p_datetrack_mode => p_datetrack_mode
);
(p_module_name => 'DELETE_entitlement_line'
,p_hook_type => 'AP'
);
ROLLBACK TO delete_entitlement_line;
ROLLBACK TO delete_entitlement_line;
END delete_entitlement_line;