APPS.BEN_DM_DELETE SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 20

select distinct per.person_id, dif.group_order
  from ben_dm_phase_items itm,
       ben_dm_phases  phs,
       ben_dm_input_file dif,
       per_all_people_f per,
       per_business_groups bg
 where phs.migration_id = p_migration_id
   and phs.phase_id     = itm.phase_id
   and phs.phase_name   = 'UP' --use UP phase for now
   and mod(itm.phase_item_id,l_no_of_threads) + 1 = p_process_number
--added check for person records only
   and dif.person_type = 'P'
   and dif.group_order = itm.group_order
   and per.national_identifier = nvl(dif.target_national_identifier,dif.source_national_identifier)
   and bg.name = dif.target_business_group_name
   and per.business_group_id = bg.business_group_id;

Line: 41

select distinct per.person_id
  from ben_dm_phase_items itm,
       ben_dm_phases  phs,
       ben_dm_input_file dif,
       per_all_people_f per,
       per_business_groups bg,
       per_contact_relationships pcr
 where phs.migration_id = p_migration_id
   and phs.phase_id     = itm.phase_id
   and phs.phase_name   = 'UP' --use UP phase for now
   and mod(itm.phase_item_id,l_no_of_threads) + 1 = p_process_number
   and dif.person_type = 'D'
   and dif.group_order = p_group_order
   and dif.group_order = itm.group_order
   and per.national_identifier = nvl(dif.target_national_identifier,dif.source_national_identifier)
   and per.person_id = pcr.contact_person_id
   and pcr.person_id = p_person_id
   and bg.name = dif.target_business_group_name
   and per.business_group_id = bg.business_group_id;

Line: 88

       delete_person(l_person_id);

Line: 92

     delete_person(p_person_id   =>  l_person_rec(i).person_id);

Line: 99

procedure delete_person(p_person_id number) is
begin

hr_general.g_data_migrator_mode := 'Y';

Line: 104

delete from ben_le_clsn_n_rstr
where per_in_ler_id in (select per_in_ler_id from ben_per_in_ler
where person_id =p_person_id);

Line: 108

delete from ben_per_bnfts_bal_f where person_id = p_person_id;

Line: 110

delete from ben_cbr_per_in_ler cpl where cpl.cbr_quald_bnf_id in
(select cbf.cbr_quald_bnf_id from ben_cbr_quald_bnf cbf where cbf.quald_bnf_person_id = p_person_id);

Line: 113

delete from ben_cbr_quald_bnf where quald_bnf_person_id = p_person_id;

Line: 115

delete from ben_prmry_care_prvdr_f pcp
where pcp.prtt_enrt_rslt_id in
(select pen.prtt_enrt_rslt_id
   from ben_prtt_enrt_rslt_f pen
  where pen.person_id = p_person_id);

Line: 121

delete from ben_prmry_care_prvdr_f pcp
where pcp.elig_cvrd_dpnt_id in
(select egd.elig_cvrd_dpnt_id
   from ben_elig_cvrd_dpnt_f egd,
        ben_prtt_enrt_rslt_f pen
  where pen.person_id = p_person_id
    and egd.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id);

Line: 129

delete from ben_enrt_rt
where elig_per_elctbl_chc_id in
(select elig_per_elctbl_chc_id from ben_elig_per_elctbl_chc
where per_in_ler_id in (select per_in_ler_id from ben_per_in_ler
where person_id =p_person_id));

Line: 135

delete from ben_enrt_prem
where elig_per_elctbl_chc_id in
(select elig_per_elctbl_chc_id from ben_elig_per_elctbl_chc
where per_in_ler_id in (select per_in_ler_id from ben_per_in_ler
where person_id =p_person_id));

Line: 141

delete from ben_enrt_rt
where enrt_bnft_id in
(select enrt_bnft_id from ben_enrt_bnft
where elig_per_elctbl_chc_id in
(select elig_per_elctbl_chc_id from ben_elig_per_elctbl_chc
where per_in_ler_id in (select per_in_ler_id from ben_per_in_ler
where person_id =p_person_id)));

Line: 149

delete from BEN_ELCTBL_CHC_CTFN
where elig_per_elctbl_chc_id in
(select elig_per_elctbl_chc_id from ben_elig_per_elctbl_chc
where per_in_ler_id in (select per_in_ler_id from ben_per_in_ler
where person_id =p_person_id));

Line: 155

delete from BEN_ELCTBL_CHC_CTFN
where enrt_bnft_id in
(select enrt_bnft_id from ben_enrt_bnft
where elig_per_elctbl_chc_id in
(select elig_per_elctbl_chc_id from ben_elig_per_elctbl_chc
where per_in_ler_id in (select per_in_ler_id from ben_per_in_ler
where person_id =p_person_id)));

Line: 163

delete from ben_enrt_bnft
where elig_per_elctbl_chc_id in
(select elig_per_elctbl_chc_id from ben_elig_per_elctbl_chc
where per_in_ler_id in (select per_in_ler_id from ben_per_in_ler
where person_id =p_person_id));

Line: 169

delete from ben_elig_per_elctbl_chc
where per_in_ler_id in (select per_in_ler_id from ben_per_in_ler
where person_id =p_person_id);

Line: 173

delete from ben_pil_elctbl_chc_popl
where per_in_ler_id in (select per_in_ler_id from ben_per_in_ler
where person_id =p_person_id);

Line: 177

delete from PAY_ELEMENT_ENTRY_VALUES_F
where element_entry_id
in (select element_entry_id
from pay_element_entries_f
where assignment_id in
(select assignment_id
from per_all_assignments_f
where person_id =p_person_id));

Line: 186

delete from PAY_ELEMENT_ENTRIES_F
where assignment_id in
(select assignment_id
from per_all_assignments_f
where person_id =p_person_id);

Line: 192

delete from ben_prtt_prem_f
where prtt_enrt_rslt_id in
(select prtt_enrt_rslt_id from ben_prtt_enrt_rslt_f where person_id =p_person_id);

Line: 195

delete from ben_prtt_rt_val where prtt_enrt_rslt_id in
(select prtt_enrt_rslt_id from ben_prtt_enrt_rslt_f where person_id =p_person_id);

Line: 198

delete from ben_bnft_prvdd_ldgr_f where prtt_enrt_rslt_id in
(select prtt_enrt_rslt_id from ben_prtt_enrt_rslt_f where person_id =p_person_id);

Line: 201

delete from ben_prtt_enrt_actn_f where prtt_enrt_rslt_id in
(select prtt_enrt_rslt_id from ben_prtt_enrt_rslt_f where person_id =p_person_id);

Line: 204

delete from ben_prtt_enrt_actn_f where elig_cvrd_dpnt_id in
(select elig_cvrd_dpnt_id from ben_elig_cvrd_dpnt_f where prtt_enrt_rslt_id in
(select prtt_enrt_rslt_id from ben_prtt_enrt_rslt_f where person_id =p_person_id));

Line: 208

delete from ben_prtt_enrt_actn_f where pl_bnf_id in
(select pl_bnf_id from ben_pl_bnf_f where prtt_enrt_rslt_id in
(select prtt_enrt_rslt_id from ben_prtt_enrt_rslt_f where person_id =p_person_id));

Line: 212

delete from ben_prtt_enrt_ctfn_prvdd_f where prtt_enrt_rslt_id in
(select prtt_enrt_rslt_id from ben_prtt_enrt_rslt_f where person_id =p_person_id);

Line: 215

delete from ben_cvrd_dpnt_ctfn_prvdd_f where elig_cvrd_dpnt_id in
(select elig_cvrd_dpnt_id from ben_elig_cvrd_dpnt_f where prtt_enrt_rslt_id in
(select prtt_enrt_rslt_id from ben_prtt_enrt_rslt_f where person_id =p_person_id));

Line: 219

delete from ben_elig_cvrd_dpnt_f where prtt_enrt_rslt_id in
(select prtt_enrt_rslt_id from ben_prtt_enrt_rslt_f where person_id =p_person_id);

Line: 222

delete from ben_elig_dpnt where per_in_ler_id in
(select per_in_ler_id from ben_per_in_ler where person_id =p_person_id);

Line: 225

delete from ben_pl_bnf_ctfn_prvdd_f where pl_bnf_id in
(select pl_bnf_id from ben_pl_bnf_f where prtt_enrt_rslt_id in
(select prtt_enrt_rslt_id from ben_prtt_enrt_rslt_f where person_id =p_person_id));

Line: 229

delete from ben_pl_bnf_f where prtt_enrt_rslt_id in
(select prtt_enrt_rslt_id from ben_prtt_enrt_rslt_f where person_id =p_person_id);

Line: 232

delete from ben_prtt_enrt_rslt_f where person_id =p_person_id;

Line: 234

delete from ben_per_cm_prvdd_f where per_cm_id in
(select per_cm_id from ben_per_cm_f where person_id =p_person_id);

Line: 237

delete from ben_per_cm_trgr_f where per_cm_id in
(select per_cm_id from ben_per_cm_f where person_id =p_person_id);

Line: 240

delete from ben_per_cm_usg_f where per_cm_id in
(select per_cm_id from ben_per_cm_f where person_id =p_person_id);

Line: 243

delete from ben_per_cm_f where person_id =p_person_id;

Line: 245

delete from ben_per_in_ler where person_id =p_person_id;

Line: 247

delete from ben_ptnl_ler_for_per where person_id =p_person_id;

Line: 249

delete from ben_elig_per_opt_f where elig_per_id in
(select elig_per_id from ben_elig_per_f where person_id =p_person_id);

Line: 252

delete from ben_elig_per_f where person_id =p_person_id;

Line: 255

delete from ben_ext_rslt_dtl      where person_id=p_person_id;

Line: 256

delete from ben_ext_rslt_err      where person_id=p_person_id;

Line: 257

delete from ben_ext_chg_evt_log   where person_id=p_person_id;

Line: 260

delete from per_addresses where person_id=p_person_id;

Line: 261

delete from per_assignment_extra_info  where assignment_id in
(select assignment_id from per_all_assignments_f where person_id=p_person_id);

Line: 265

delete from per_all_assignments_f where person_id=p_person_id;

Line: 266

delete from per_person_type_usages_f where person_id=p_person_id;

Line: 267

delete from per_periods_of_service where person_id=p_person_id;

Line: 268

delete from per_contact_relationships where person_id=p_person_id;

Line: 269

delete from per_absence_attendances where person_id=p_person_id;

Line: 270

delete from per_all_people_f where person_id=p_person_id;

Line: 278

end delete_person;