The following lines contain the word 'select', 'insert', 'update' or 'delete':
Select information1 plip_id,
information2 effective_start_date,
information3 effective_end_date,
information4 business_group_id,
information261 pl_id,
information265 ovn
From ben_copy_entity_results
Where copy_entity_txn_id = p_copy_entity_txn_id
And table_alias = 'CPP'
And dml_operation = 'DELETE';
Select plip_Id,
Effective_Start_Date, Effective_End_Date,
Object_version_number
From Ben_plip_F
Where plip_id = p_plip_id
and p_effective_date
between Effective_Start_Date and Effective_End_Date;
l_datetrack_mode := 'DELETE';
ben_Plan_in_Program_api.delete_Plan_in_Program
(p_plip_id => del_plip_rec.plip_id
,p_effective_start_date => del_plip_rec.effective_start_date
,p_effective_end_date => del_plip_rec.effective_end_date
,p_object_version_number => del_plip_rec.ovn
,p_effective_date => p_effective_date
,p_datetrack_mode => l_datetrack_mode);
hr_utility.set_location('delete success '||l_plip_id,9);
Select information1 oipl_id,
information2 effective_start_date,
information3 effective_end_date,
information4 business_group_id,
information247 opt_id,
information265 oipl_ovn
From ben_copy_entity_results
Where copy_entity_txn_id = p_copy_entity_txn_id
And table_alias = 'COP'
And dml_operation = 'DELETE';
Select oipl_id,
Effective_Start_Date, Effective_End_Date,
Object_version_number
From Ben_oipl_F
Where oipl_id = p_oipl_id
and p_effective_date
between Effective_Start_Date and Effective_End_Date;
l_datetrack_mode := 'DELETE';
ben_Option_in_Plan_api.delete_Option_in_Plan
(p_oipl_id => del_oipl_rec.oipl_id
,p_effective_start_date => del_oipl_rec.effective_start_date
,p_effective_end_date => del_oipl_rec.effective_end_date
,p_object_version_number => del_oipl_rec.oipl_ovn
,p_effective_date => p_effective_date
,p_datetrack_mode => l_datetrack_mode);
Select Obj.Table_Alias , Obj.Dml_Operation,
Obj.Gs_Parent_Entity_Result_Id
From Ben_Copy_Entity_Results Cep,
Ben_Copy_Entity_Results Epa,
Ben_Copy_Entity_Results Obj
Where Cep.Copy_Entity_Result_id = P_Cer_id
and Epa.Copy_Entity_Result_id = Cep.GS_MIRROR_SRC_ENTITY_RESULT_ID
and Obj.Copy_Entity_Result_id = Epa.GS_MIRROR_SRC_ENTITY_RESULT_ID;
Select Information253 Grade_Id,
Information255 Scale_Id
from Ben_Copy_Entity_Results Cpp
Where Copy_Entity_Txn_id = P_Copy_Entity_Txn_Id
and Copy_Entity_Result_Id = p_Cpp_Cer_Id
and Table_Alias = 'CPP'
and Result_type_Cd = 'DISPLAY';
Select Parent_Spine_Id
From Per_Grade_Spines_F
Where Grade_Id = P_grade_Id
and P_Effective_Date
Between Effective_Start_Date
and Effective_End_Date;
If l_table_Alias = 'COP' and l_Dml_operation = 'DELETE' then
Open Csr_plip(l_Plip_Cer_Id);
Cursor csr_delete_cep is
Select information1 cep_id,
information2 effective_start_date,
information3 effective_end_date,
information4 business_group_id,
information265 cep_ovn,
Copy_Entity_Result_Id
From ben_copy_entity_results
Where copy_entity_txn_id = p_copy_entity_txn_id
And table_alias = 'CEP'
And dml_operation ='DELETE';
Select prtn_elig_prfl_id,
Effective_Start_Date, Effective_End_Date,
Object_version_number
From Ben_prtn_elig_prfl_f
Where prtn_elig_prfl_id = p_prtn_elig_prfl_id
and p_effective_date
between Effective_Start_Date and Effective_End_Date;
Cursor csr_delete_epa is
Select information1 epa_id,
information2 effective_start_date,
information3 effective_end_date,
information4 business_group_id,
information265 epa_ovn,
Copy_Entity_Result_id
From ben_copy_entity_results
Where copy_entity_txn_id = p_copy_entity_txn_id
And table_alias = 'EPA'
And dml_operation ='DELETE';
Select prtn_elig_id,
Effective_Start_Date, Effective_End_Date,
Object_version_number
From Ben_prtn_elig_f
Where prtn_elig_id = p_prtn_elig_id
and p_effective_date
between Effective_Start_Date and Effective_End_Date;
Select Copy_Entity_Result_Id
From Ben_Copy_Entity_Results
Where Copy_Entity_Txn_Id = P_Copy_Entity_Txn_Id
and GS_MIRROR_SRC_ENTITY_RESULT_ID = P_Epa_Cer_id;
For del_cep_rec in csr_delete_cep loop
--
l_Effective_Date := Get_Prfl_Del_Eff_Dt (del_Cep_Rec.Copy_Entity_Result_Id, p_copy_entity_txn_id, P_Effective_Date);
l_datetrack_mode := 'DELETE';
ben_PRTN_ELIG_PRFL_api.delete_PRTN_ELIG_PRFL
(
p_validate => false
,p_prtn_elig_prfl_id => del_cep_rec.cep_id
,p_effective_start_date => del_cep_rec.effective_start_date
,p_effective_end_date => del_cep_rec.effective_end_date
,p_object_version_number => del_cep_rec.cep_ovn
,p_effective_date => l_effective_date
,p_datetrack_mode => l_datetrack_mode
);
For del_epa_rec in csr_delete_epa loop
--
Open Csr_Epa_cer (del_epa_rec.Copy_Entity_Result_id);
l_datetrack_mode := 'DELETE';
ben_Participation_Elig_api.delete_Participation_Elig
(
p_validate => false
,p_prtn_elig_id => del_epa_rec.epa_id
,p_effective_start_date => del_epa_rec.effective_start_date
,p_effective_end_date => del_epa_rec.effective_end_date
,p_object_version_number => del_epa_rec.epa_ovn
,p_effective_date => l_effective_date
,p_datetrack_mode => l_datetrack_mode
);
Function delete_option (p_copy_entity_txn_id in number,
p_effective_date in date,
p_datetrack_mode in varchar2)
RETURN varchar2 is
--
Cursor csr_delete_opt is
Select information1 opt_id,
information2 effective_start_date,
information3 effective_end_date,
information4 business_group_id,
information265 opt_ovn
From ben_copy_entity_results
Where copy_entity_txn_id = p_copy_entity_txn_id
And table_alias = 'OPT'
And dml_operation ='DELETE';
Select opt_id,
Effective_Start_Date , Effective_End_Date,
Object_version_number
From Ben_opt_F
Where opt_id = p_opt_id
and p_effective_date
between Effective_Start_Date and Effective_End_Date
and Mapping_table_name = 'PER_SPINAL_POINTS';
Select Pl_typ_opt_Typ_Id,
Effective_Start_Date,
Effective_End_Date,
Object_Version_Number
from Ben_Pl_Typ_Opt_Typ_F
Where Opt_Id = P_Opt_id
and Pl_Typ_Opt_Typ_Cd = 'GSP';
Select Oipl_id
From Ben_Oipl_F
Where Opt_id = p_Opt_id;
hr_utility.set_location('Entering: delete_option',5);
For del_opt_rec in csr_delete_opt loop
--
-- When a point that is not used as step is brought to staging area, an option
-- is created for it anyway, if the option does not already exist.
-- When the point is deleted the option record will be marked
-- delete but theere will be not opt_id as there is no record in BEN.
--
--
If del_opt_rec.opt_id is not null then
--
-- Determine the date-tracked mode to use when deleting the row. If no date-tracked
-- mode is passed, the system will determine date-tracked mode to use when deleting
-- by reading actual BEN table rows.
--
--
Open csr_ben_opt(del_opt_rec.opt_id);
l_datetrack_mode := 'DELETE';
-- Delete Pl_TYp_Opt_typ record
Open csr_Pl_Opt_Type(del_opt_rec.opt_id);
ben_plan_type_option_type_api.Delete_Plan_Type_Option_Type
(P_PL_TYP_OPT_TYP_ID => l_Pl_Typ_Opt_Typ_Id
,P_EFFECTIVE_START_DATE => l_opt_typ_Esd
,P_EFFECTIVE_END_DATE => l_Opt_Typ_Eed
,P_OBJECT_VERSION_NUMBER => l_Opt_typ_Ovn
,P_EFFECTIVE_DATE => P_Effective_Date
,P_DATETRACK_MODE => l_datetrack_mode);
ben_option_definition_api.delete_option_definition
(p_opt_id => del_opt_rec.opt_id
,p_effective_start_date => del_opt_rec.effective_start_date
,p_effective_end_date => del_opt_rec.effective_end_date
,p_object_version_number => del_opt_rec.opt_ovn
,p_effective_date => p_effective_date
,p_datetrack_mode => l_datetrack_mode);
hr_utility.set_location('Leaving: delete_option',10);
hr_utility.set_location('Exception raised: delete_option',99);
Function delete_from_ben (p_copy_entity_txn_id in number,
p_effective_date in date,
p_datetrack_mode in varchar2)
RETURN varchar2 is
--
l_status varchar2(30);
hr_utility.set_location('Entering: delete_from_ben',5);
l_status := delete_option (p_copy_entity_txn_id => p_copy_entity_txn_id,
p_effective_date => p_effective_date,
p_datetrack_mode => p_datetrack_mode);
hr_utility.set_location('Leaving: delete_from_ben',10);