[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT NULL
FROM fnd_concurrent_requests fnd
WHERE fnd.phase_code <> 'C' AND fnd.request_id = p_request_id;
procedure delete_per_con
is
--
l_proc varchar2(80) := g_package || '.delete_per_con';
delete /*+ parallel(a) */ from per_contact_relationships a
where a.contact_person_id is not null
and a.contact_person_id not in ( select /*+ hash_aj index_ffs(ppf) parallel_index(ppf) */ person_id
from per_all_people_f ppf );
delete /*+ parallel(a) */ from per_contact_relationships a
where a.person_id is not null
and a.person_id not in ( select /*+ hash_aj index_ffs(ppf) parallel_index(ppf) */ person_id
from per_all_people_f ppf );
end delete_per_con;
procedure delete_cbr_quald_bnf
is
--
l_proc varchar2(80) := g_package || '.delete_cbr_quald_bnf';
DELETE FROM ben_cbr_quald_bnf a
WHERE NOT EXISTS (SELECT 1
FROM per_all_people_f per
WHERE per.person_id = a.cvrd_emp_person_id);
end delete_cbr_quald_bnf;
procedure delete_crt_ordr
is
--
l_proc varchar2(80) := g_package || '.delete_crt_ordr';
DELETE FROM ben_crt_ordr a
WHERE NOT EXISTS (SELECT 1
FROM per_all_people_f per
WHERE per.person_id = a.person_id);
end delete_crt_ordr;
procedure delete_crt_ordr_cvrd_per
is
--
l_proc varchar2(80) := g_package || '.delete_crt_ordr_cvrd_per';
DELETE FROM ben_crt_ordr_cvrd_per a
WHERE NOT EXISTS (SELECT 1
FROM per_all_people_f per
WHERE per.person_id = a.person_id);
end delete_crt_ordr_cvrd_per;
procedure delete_elig_dpnt
is
--
l_proc varchar2(80) := g_package || '.delete_elig_dpnt';
DELETE /*+ PARALLEL(A) */ FROM ben_elig_dpnt a
WHERE a.dpnt_person_id IS NOT NULL
AND a.dpnt_person_id NOT IN (SELECT /*+ HASH_AJ INDEX_FFS(PER) PARALLEL_INDEX(PER) */ person_id
FROM per_all_people_f per);
end delete_elig_dpnt;
procedure delete_elig_per_f
is
--
l_proc varchar2(80) := g_package || '.delete_elig_per_f';
DELETE /*+ PARALLEL(A) */FROM ben_elig_per_f a
WHERE a.person_id IS NOT NULL
AND a.person_id NOT IN (SELECT /*+ HASH_AJ INDEX_FFS(PER) PARALLEL_INDEX(PER) */ person_id
FROM per_all_people_f per);
end delete_elig_per_f;
procedure delete_ext_chg_evt_log
is
--
l_proc varchar2(80) := g_package || '.delete_ext_chg_evt_log';
DELETE /*+ PARALLEL(A) */FROM ben_ext_chg_evt_log a
WHERE a.person_id IS NOT NULL
AND a.person_id NOT IN (SELECT /*+ HASH_AJ INDEX_FFS(PER) PARALLEL_INDEX(PER) */ person_id
FROM per_all_people_f per);
end delete_ext_chg_evt_log;
procedure delete_ext_rslt_dtl
is
--
l_proc varchar2(80) := g_package || '.delete_ext_rslt_dtl';
DELETE /*+ PARALLEL(A) */FROM ben_ext_rslt_dtl a
WHERE a.person_id IS NOT NULL
AND a.person_id NOT IN (0, 999999999999)
AND a.person_id NOT IN (SELECT /*+ HASH_AJ INDEX_FFS(PER) PARALLEL_INDEX(PER) */ person_id
FROM per_all_people_f per);
end delete_ext_rslt_dtl;
procedure delete_ext_rslt_err
is
--
l_proc varchar2(80) := g_package || '.delete_ext_rslt_err';
DELETE /*+ PARALLEL(A) */FROM ben_ext_rslt_err a
WHERE a.person_id IS NOT NULL
AND a.person_id NOT IN (SELECT /*+ HASH_AJ INDEX_FFS(PER) PARALLEL_INDEX(PER) */ person_id
FROM per_all_people_f per);
end delete_ext_rslt_err;
procedure delete_le_clsn_n_rstr
is
--
l_proc varchar2(80) := g_package || '.delete_le_clsn_n_rstr';
DELETE /*+ PARALLEL(A) */FROM ben_le_clsn_n_rstr a
WHERE a.person_id IS NOT NULL
AND a.person_id NOT IN (SELECT /*+ HASH_AJ INDEX_FFS(PER) PARALLEL_INDEX(PER) */ person_id
FROM per_all_people_f per);
end delete_le_clsn_n_rstr;
procedure delete_person_actions
is
--
l_proc varchar2(80) := g_package || '.delete_person_actions';
DELETE /*+ PARALLEL(A) */FROM ben_person_actions a
WHERE a.person_id IS NOT NULL
AND a.person_id NOT IN (SELECT /*+ HASH_AJ INDEX_FFS(PER) PARALLEL_INDEX(PER) */ person_id
FROM per_all_people_f per);
end delete_person_actions;
procedure delete_per_bnfts_bal_f
is
--
l_proc varchar2(80) := g_package || '.delete_per_bnfts_bal_f';
DELETE FROM ben_per_bnfts_bal_f a
WHERE NOT EXISTS (SELECT 1
FROM per_all_people_f per
WHERE per.person_id = a.person_id);
end delete_per_bnfts_bal_f;
procedure delete_per_dlvry_mthd_f
is
--
l_proc varchar2(80) := g_package || '.delete_per_dlvry_mthd_f';
DELETE FROM ben_per_dlvry_mthd_f a
WHERE NOT EXISTS (SELECT 1
FROM per_all_people_f per
WHERE per.person_id = a.person_id);
end delete_per_dlvry_mthd_f;
procedure delete_per_pin_f
is
--
l_proc varchar2(80) := g_package || '.delete_per_pin_f';
DELETE FROM ben_per_pin_f a
WHERE NOT EXISTS (SELECT 1
FROM per_all_people_f per
WHERE per.person_id = a.person_id);
end delete_per_pin_f;
procedure delete_pl_bnf_f
is
--
l_proc varchar2(80) := g_package || '.delete_pl_bnf_f';
DELETE FROM ben_pl_bnf_f a
WHERE NOT EXISTS (
SELECT 1
FROM per_all_people_f per
WHERE per.person_id = a.bnf_person_id
OR per.person_id = a.ttee_person_id);
end delete_pl_bnf_f;
procedure delete_prtt_reimbmt_rqst_f
is
--
l_proc varchar2(80) := g_package || '.delete_prtt_reimbmt_rqst_f';
DELETE FROM ben_prtt_reimbmt_rqst_f a
WHERE NOT EXISTS (
SELECT 1
FROM per_all_people_f per
WHERE per.person_id = a.submitter_person_id
OR per.person_id = a.recipient_person_id
OR per.person_id = a.provider_person_id
OR per.person_id = a.provider_ssn_person_id);
end delete_prtt_reimbmt_rqst_f;
procedure delete_ptnl_ler_for_per
is
--
l_proc varchar2(80) := g_package || '.delete_ptnl_ler_for_per';
DELETE /*+ PARALLEL(A) */FROM ben_ptnl_ler_for_per a
WHERE a.person_id IS NOT NULL
AND a.person_id NOT IN (SELECT /*+ HASH_AJ INDEX_FFS(PER) PARALLEL_INDEX(PER) */ person_id
FROM per_all_people_f per);
end delete_ptnl_ler_for_per;
procedure delete_benefit_actions
is
--
l_proc varchar2(80) := g_package || '.delete_benefit_actions';
SELECT /*+ PARALLEL(A) */
DISTINCT benefit_action_id
FROM ben_benefit_actions a
WHERE a.person_id IS NOT NULL
AND a.person_id NOT IN (SELECT /*+ HASH_AJ INDEX_FFS(PER) PARALLEL_INDEX(PER) */ person_id
FROM per_all_people_f per);
DELETE FROM BEN_BATCH_ACTN_ITEM_INFO
WHERE benefit_action_id = l_data(i);
DELETE FROM BEN_BATCH_BNFT_CERT_INFO
WHERE benefit_action_id = l_data(i);
DELETE FROM BEN_BATCH_COMMU_INFO
WHERE benefit_action_id = l_data(i);
DELETE FROM BEN_BATCH_DPNT_INFO
WHERE benefit_action_id = l_data(i);
DELETE FROM BEN_BATCH_ELCTBL_CHC_INFO
WHERE benefit_action_id = l_data(i);
DELETE FROM BEN_BATCH_ELIG_INFO
WHERE benefit_action_id = l_data(i);
DELETE FROM BEN_BATCH_LER_INFO
WHERE benefit_action_id = l_data(i);
DELETE FROM BEN_BATCH_RATE_INFO
WHERE benefit_action_id = l_data(i);
DELETE FROM BEN_BATCH_PROC_INFO
WHERE benefit_action_id = l_data(i);
DELETE FROM BEN_BATCH_RANGES
WHERE benefit_action_id = l_data(i);
DELETE FROM BEN_REPORTING
WHERE benefit_action_id = l_data(i);
DELETE FROM ben_benefit_actions
WHERE benefit_action_id = l_data(i);
end delete_benefit_actions;
procedure delete_per_in_ler
is
--
l_proc varchar2(80) := g_package || '.delete_per_in_ler';
SELECT /*+ PARALLEL(A) */
DISTINCT per_in_ler_id
FROM ben_per_in_ler a
WHERE a.person_id IS NOT NULL
AND a.person_id NOT IN (SELECT /*+ HASH_AJ INDEX_FFS(PER) PARALLEL_INDEX(PER) */ person_id
FROM per_all_people_f per);
DELETE FROM BEN_BNFT_PRVDD_LDGR_F
WHERE PER_IN_LER_ID = l_data(i);
DELETE FROM BEN_CBR_PER_IN_LER
WHERE PER_IN_LER_ID = l_data(i);
DELETE FROM BEN_ELIG_PER_OPT_F
WHERE PER_IN_LER_ID = l_data(i);
delete from BEN_ELIG_DPNT a
where PER_IN_LER_ID = l_data(i);
delete from BEN_PIL_ELCTBL_CHC_POPL a
where PER_IN_LER_ID = l_data(i);
delete from BEN_ENRT_RT
where ELIG_PER_ELCTBL_CHC_ID in ( select ELIG_PER_ELCTBL_CHC_ID
from BEN_ELIG_PER_ELCTBL_CHC a
where a.PER_IN_LER_ID = l_data(i) );
delete from BEN_PRTT_PREM_BY_MO_F
where PRTT_PREM_ID IN ( select PRTT_PREM_ID
from BEN_PRTT_PREM_F a
where a.PER_IN_LER_ID = l_data(i) );
delete from BEN_PRTT_PREM_F a
where PER_IN_LER_ID = l_data(i);
delete from BEN_ENRT_PREM
where ELIG_PER_ELCTBL_CHC_ID in ( select ELIG_PER_ELCTBL_CHC_ID
from BEN_ELIG_PER_ELCTBL_CHC a
where a.PER_IN_LER_ID = l_data(i) );
delete from BEN_ENRT_BNFT
where ELIG_PER_ELCTBL_CHC_ID in ( select ELIG_PER_ELCTBL_CHC_ID
from BEN_ELIG_PER_ELCTBL_CHC a
where a.PER_IN_LER_ID = l_data(i) );
delete from BEN_ELIG_PER_ELCTBL_CHC a
where PER_IN_LER_ID = l_data(i) ;
DELETE FROM BEN_PER_IN_LER
WHERE PER_IN_LER_ID = l_data(i) ;
end delete_per_in_ler;
procedure delete_prtt_enrt_rslt_f
is
--
l_proc varchar2(80) := g_package || '.delete_prtt_enrt_rslt_f';
SELECT /*+ PARALLEL(A) */
DISTINCT prtt_enrt_rslt_id
FROM ben_prtt_enrt_rslt_f a
WHERE a.person_id IS NOT NULL
AND a.person_id NOT IN (SELECT /*+ HASH_AJ INDEX_FFS(PER) PARALLEL_INDEX(PER) */ person_id
FROM per_all_people_f per);
DELETE FROM BEN_PRMRY_CARE_PRVDR_F
WHERE prtt_enrt_rslt_id = l_data(i);
DELETE FROM BEN_PRTT_ENRT_ACTN_F
WHERE prtt_enrt_rslt_id = l_data(i);
DELETE FROM BEN_PRTT_ENRT_CTFN_PRVDD_F
WHERE prtt_enrt_rslt_id = l_data(i);
DELETE FROM BEN_PRTT_PREM_F
WHERE prtt_enrt_rslt_id = l_data(i);
DELETE FROM BEN_PRTT_RT_VAL
WHERE prtt_enrt_rslt_id = l_data(i);
DELETE FROM BEN_PRTT_ENRT_RSLT_F
WHERE prtt_enrt_rslt_id = l_data(i);
end delete_prtt_enrt_rslt_f;
procedure delete_elig_cvrd_dpnt_f
is
--
l_proc varchar2(80) := g_package || '.delete_elig_cvrd_dpnt_f';
SELECT /*+ PARALLEL(A) */
DISTINCT elig_cvrd_dpnt_id
FROM ben_elig_cvrd_dpnt_f a
WHERE NOT EXISTS (SELECT /*+ HASH_AJ INDEX_FFS(PER) PARALLEL(PER) */ 1
FROM per_all_people_f per
WHERE per.person_id = a.dpnt_person_id);
DELETE FROM ben_cvrd_dpnt_ctfn_prvdd_f
WHERE elig_cvrd_dpnt_id = l_data (i);
DELETE FROM ben_ext_crit_val
WHERE ext_crit_val_id IN (
SELECT DISTINCT ext_crit_val_id
FROM ben_ext_crit_val val, ben_ext_crit_typ typ
WHERE typ.crit_typ_cd = 'PID'
AND val.ext_crit_typ_id = typ.ext_crit_typ_id
AND val.val_1 = TO_CHAR (l_data (i)));
DELETE FROM ben_elig_cvrd_dpnt_f
WHERE elig_cvrd_dpnt_id = l_data (i);
end delete_elig_cvrd_dpnt_f;
procedure delete_per_cm_f
is
--
l_proc varchar2(80) := g_package || '.delete_per_cm_f';
SELECT /*+ PARALLEL(A) */
DISTINCT per_cm_id
FROM ben_per_cm_f a
WHERE NOT EXISTS (SELECT /*+ HASH_AJ INDEX_FFS(PER) PARALLEL(PER) */ 1
FROM per_all_people_f per
WHERE per.person_id = a.person_id);
delete FROM ben_per_cm_prvdd_f
WHERE per_cm_id = l_data(i) ;
delete FROM ben_per_cm_trgr_f
WHERE per_cm_id = l_data(i) ;
delete FROM ben_per_cm_usg_f
WHERE per_cm_id = l_data(i) ;
delete FROM ben_per_cm_f
WHERE per_cm_id = l_data(i) ;
end delete_per_cm_f;
l_title_text := 'Number of rows deleted from the following tables :';
delete_elig_per_f;
delete_cbr_quald_bnf;
delete_crt_ordr;
delete_crt_ordr_cvrd_per ;
delete_elig_dpnt;
delete_per_dlvry_mthd_f ;
delete_pl_bnf_f;
delete_per_pin_f ;
delete_ext_rslt_dtl ;
delete_per_bnfts_bal_f ;
delete_prtt_reimbmt_rqst_f;
delete_ext_rslt_err;
delete_person_actions;
delete_benefit_actions;
delete_le_clsn_n_rstr;
delete_per_in_ler;
delete_prtt_enrt_rslt_f;
delete_elig_cvrd_dpnt_f;
delete_ext_chg_evt_log;
delete_per_cm_f;
delete_ptnl_ler_for_per;
delete_per_con ;
,p_program_update_date => sysdate
);