[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
/* Bug 13067080; Procedure added to delete person related CWB table records */
PROCEDURE delete_missing_cwb_tables (
p_person_id NUMBER
) IS
CURSOR c1 IS
SELECT cwb_matrix_id
FROM ben_cwb_matrix
WHERE person_id = p_person_id
FOR UPDATE OF cwb_matrix_id;
SELECT cwb_matrix_dtl_id
FROM ben_cwb_matrix_dtl
WHERE cwb_matrix_id = p_cwb_matrix_id
FOR UPDATE OF cwb_matrix_dtl_id;
delete from BEN_CWB_STOCK_OPTN_DTLS where person_id = p_person_id;
delete from BEN_CWB_RPT_DETAIL where person_id = p_person_id;
DELETE FROM ben_cwb_matrix_dtl
WHERE CURRENT OF c2;
DELETE FROM ben_cwb_matrix
WHERE CURRENT OF c1;
END delete_missing_cwb_tables;
SELECT 1
FROM ben_batch_actn_item_info
WHERE person_id = p_person_id;
SELECT 1
FROM ben_batch_bnft_cert_info
WHERE person_id = p_person_id;
SELECT 1
FROM ben_batch_commu_info
WHERE person_id = p_person_id;
SELECT 1
FROM ben_batch_dpnt_info
WHERE person_id = p_person_id;
SELECT 1
FROM ben_batch_elctbl_chc_info
WHERE person_id = p_person_id;
SELECT 1
FROM ben_batch_elig_info
WHERE person_id = p_person_id;
SELECT 1
FROM ben_batch_ler_info
WHERE person_id = p_person_id;
SELECT 1
FROM ben_batch_rate_info
WHERE person_id = p_person_id;
SELECT 1
FROM ben_benefit_actions
WHERE person_id = p_person_id;
SELECT 1
FROM ben_crt_ordr
WHERE person_id = p_person_id;
SELECT 1
FROM ben_crt_ordr_cvrd_per
WHERE person_id = p_person_id;
SELECT 1
FROM ben_elig_per_f
WHERE person_id = p_person_id;
SELECT 1
FROM ben_ext_chg_evt_log
WHERE person_id = p_person_id;
SELECT 1
FROM ben_ext_rslt_dtl
WHERE person_id = p_person_id;
SELECT 1
FROM ben_ext_rslt_err
WHERE person_id = p_person_id;
SELECT 1
FROM ben_le_clsn_n_rstr lct,
ben_per_in_ler pil
WHERE pil.person_id = p_person_id
AND pil.per_in_ler_id = lct.per_in_ler_id; /* Bug 4882374 : Perf */
SELECT 1
FROM ben_person_actions
WHERE person_id = p_person_id;
SELECT 1
FROM ben_per_bnfts_bal_f
WHERE person_id = p_person_id;
SELECT 1
FROM ben_per_cm_f
WHERE person_id = p_person_id;
SELECT 1
FROM ben_per_dlvry_mthd_f
WHERE person_id = p_person_id;
SELECT 1
FROM ben_per_in_ler
WHERE person_id = p_person_id;
SELECT 1
FROM ben_per_in_lgl_enty_f
WHERE person_id = p_person_id;
SELECT 1
FROM ben_per_in_org_unit_f
WHERE person_id = p_person_id;
SELECT 1
FROM ben_per_pin_f
WHERE person_id = p_person_id;
SELECT 1
FROM ben_popl_org_f
WHERE person_id = p_person_id;
SELECT 1
FROM ben_prtt_enrt_rslt_f
WHERE person_id = p_person_id;
SELECT 1
FROM ben_ptnl_ler_for_per
WHERE person_id = p_person_id;
SELECT 1
FROM ben_reporting rep,
ben_person_actions pat
WHERE pat.person_id = p_person_id
AND rep.benefit_action_id = pat.benefit_action_id; /* Bug 4882374 : Perf */
SELECT 1
FROM ben_elig_cvrd_dpnt_f
WHERE dpnt_person_id = p_person_id;
SELECT 1
FROM ben_prtt_reimbmt_rqst_f
WHERE submitter_person_id = p_person_id;
SELECT 1
FROM ben_prtt_reimbmt_rqst_f
WHERE recipient_person_id = p_person_id;
SELECT 1
FROM ben_prtt_reimbmt_rqst_f
WHERE provider_person_id = p_person_id;
SELECT 1
FROM ben_prtt_reimbmt_rqst_f
WHERE provider_ssn_person_id = p_person_id;
SELECT 1
FROM ben_cbr_quald_bnf
WHERE cvrd_emp_person_id = p_person_id;
SELECT 1
FROM ben_pl_bnf_f
WHERE bnf_person_id = p_person_id;
PROCEDURE delete_dependent_information (
p_person_id IN NUMBER,
p_per_in_ler_id IN NUMBER default NULL
) IS
l_proc varchar2(100):= g_package||'delete_dependent_information';
SELECT elig_cvrd_dpnt_id, dpnt_person_id
FROM ben_elig_cvrd_dpnt_f
WHERE dpnt_person_id = p_person_id
and per_in_ler_id = nvl(p_per_in_ler_id,per_in_ler_id)
FOR UPDATE OF elig_cvrd_dpnt_id,dpnt_person_id;
SELECT crt_ordr_cvrd_per_id
FROM ben_crt_ordr_cvrd_per
WHERE crt_ordr_id IN (SELECT crt_ordr_id
FROM ben_crt_ordr
WHERE person_id = p_dpnt_person_id)
FOR UPDATE OF crt_ordr_id;
SELECT cvrd_dpnt_ctfn_prvdd_id
FROM ben_cvrd_dpnt_ctfn_prvdd_f
WHERE elig_cvrd_dpnt_id = p_elig_cvrd_dpnt_id
FOR UPDATE OF elig_cvrd_dpnt_id;
SELECT elig_cvrd_dpnt_id
FROM ben_prtt_enrt_actn_f
WHERE elig_cvrd_dpnt_id = p_elig_cvrd_dpnt_id
FOR UPDATE OF elig_cvrd_dpnt_id;
SELECT elig_cvrd_dpnt_id
FROM ben_prmry_care_prvdr_f
WHERE elig_cvrd_dpnt_id = p_elig_cvrd_dpnt_id
FOR UPDATE OF elig_cvrd_dpnt_id;
SELECT elig_cvrd_dpnt_id
FROM ben_cvrd_dpnt_ctfn_prvdd_f
WHERE elig_cvrd_dpnt_id = p_elig_cvrd_dpnt_id
FOR UPDATE OF elig_cvrd_dpnt_id;
SELECT elig_cvrd_dpnt_id
FROM ben_elig_cvrd_dpnt_f
WHERE elig_cvrd_dpnt_id = p_elig_cvrd_dpnt_id
FOR UPDATE OF elig_cvrd_dpnt_id;
SELECT elig_cvrd_dpnt_id
FROM ben_elig_dpnt
WHERE elig_cvrd_dpnt_id = p_elig_cvrd_dpnt_id
FOR UPDATE OF elig_cvrd_dpnt_id;
SELECT ext_crit_val_id
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 (
p_elig_cvrd_dpnt_id
))
FOR UPDATE OF ext_crit_val_id;
SELECT elig_cvrd_dpnt_id
FROM ben_elig_dpnt
WHERE dpnt_person_id = p_elig_cvrd_dpnt_id
FOR UPDATE OF elig_cvrd_dpnt_id;
DELETE FROM ben_crt_ordr_cvrd_per
WHERE CURRENT OF c_crt_ordr_cvrd;
DELETE ben_cvrd_dpnt_ctfn_prvdd_f
WHERE CURRENT OF c_cvrd_dpnt;
DELETE ben_prtt_enrt_actn_f
WHERE CURRENT OF c1;
DELETE ben_prmry_care_prvdr_f
WHERE CURRENT OF c2;
DELETE ben_cvrd_dpnt_ctfn_prvdd_f
WHERE CURRENT OF c3;
DELETE ben_elig_cvrd_dpnt_f
WHERE CURRENT OF c4;
DELETE ben_elig_dpnt
WHERE CURRENT OF c5;
DELETE FROM ben_ext_crit_val
WHERE CURRENT OF c6;
DELETE ben_elig_dpnt
WHERE CURRENT OF c7;
DELETE ben_elig_cvrd_dpnt_f
WHERE CURRENT OF c_ecd;
END delete_dependent_information;
PROCEDURE delete_communications (
p_person_id IN NUMBER,
p_per_in_ler_id IN NUMBER default NULL
) IS
l_proc varchar2(100):= g_package||'delete_communications';
SELECT DISTINCT per_cm_id
FROM ben_per_cm_f
WHERE person_id = p_person_id
and per_in_ler_id = nvl(p_per_in_ler_id,per_in_ler_id);
SELECT per_cm_prvdd_id
FROM ben_per_cm_prvdd_f
WHERE per_cm_id = p_per_cm_id
FOR UPDATE OF per_cm_prvdd_id;
SELECT per_cm_trgr_id
FROM ben_per_cm_trgr_f
WHERE per_cm_id = p_per_cm_id
FOR UPDATE OF per_cm_trgr_id;
SELECT per_cm_usg_id
FROM ben_per_cm_usg_f
WHERE per_cm_id = p_per_cm_id
FOR UPDATE OF per_cm_usg_id;
DELETE FROM ben_per_cm_prvdd_f
WHERE CURRENT OF c2;
DELETE FROM ben_per_cm_trgr_f
WHERE CURRENT OF c3;
DELETE FROM ben_per_cm_usg_f
WHERE CURRENT OF c4;
END delete_communications;
PROCEDURE delete_life_events (
p_person_id IN NUMBER,
p_per_in_ler_id IN NUMBER default NULL
) IS
l_proc varchar2(100):= g_package||'delete_life_events';
SELECT DISTINCT per_in_ler_id
FROM ben_per_in_ler
WHERE person_id = p_person_id
and per_in_ler_id = nvl(p_per_in_ler_id,per_in_ler_id);
SELECT DISTINCT prtt_prem_id
FROM ben_prtt_prem_f
WHERE per_in_ler_id = p_per_in_ler_id;
SELECT prtt_prem_by_mo_id
FROM ben_prtt_prem_by_mo_f
WHERE prtt_prem_id = p_prtt_prem_id
FOR UPDATE OF prtt_prem_by_mo_id;
SELECT DISTINCT prtt_enrt_rslt_id
FROM ben_prtt_enrt_rslt_f
WHERE per_in_ler_id = p_per_in_ler_id;
SELECT prmry_care_prvdr_id
FROM ben_prmry_care_prvdr_f
WHERE prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
FOR UPDATE OF prmry_care_prvdr_id;
SELECT bnft_prvdd_ldgr_id
FROM ben_bnft_prvdd_ldgr_f
WHERE prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
FOR UPDATE OF bnft_prvdd_ldgr_id;
SELECT elig_cvrd_dpnt_id
FROM ben_elig_cvrd_dpnt_f
WHERE prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
FOR UPDATE OF elig_cvrd_dpnt_id;
SELECT prtt_enrt_ctfn_prvdd_id
FROM ben_prtt_enrt_ctfn_prvdd_f
WHERE prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
FOR UPDATE OF prtt_enrt_ctfn_prvdd_id;
SELECT prtt_prem_id
FROM ben_prtt_prem_f
WHERE prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
FOR UPDATE OF prtt_prem_id;
SELECT prtt_rt_val_id
FROM ben_prtt_rt_val
WHERE prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
FOR UPDATE OF prtt_rt_val_id;
SELECT prtt_enrt_actn_id
FROM ben_prtt_enrt_actn_f
WHERE prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
FOR UPDATE OF prtt_enrt_actn_id;
SELECT DISTINCT prtt_enrt_actn_id
FROM ben_prtt_enrt_actn_f
WHERE per_in_ler_id = p_per_in_ler_id;
SELECT pl_bnf_ctfn_prvdd_id
FROM ben_pl_bnf_ctfn_prvdd_f
WHERE prtt_enrt_actn_id = p_prtt_enrt_actn_id
FOR UPDATE OF pl_bnf_ctfn_prvdd_id;
SELECT cvrd_dpnt_ctfn_prvdd_id
FROM ben_cvrd_dpnt_ctfn_prvdd_f
WHERE prtt_enrt_actn_id = p_prtt_enrt_actn_id
FOR UPDATE OF cvrd_dpnt_ctfn_prvdd_id;
SELECT DISTINCT enrt_bnft_id
FROM ben_elig_per_elctbl_chc b1, ben_enrt_bnft b2
WHERE b1.per_in_ler_id = p_per_in_ler_id
AND b2.elig_per_elctbl_chc_id =
b1.elig_per_elctbl_chc_id;
SELECT enrt_rt_id
FROM ben_enrt_rt
WHERE enrt_bnft_id = p_enrt_bnft_id
FOR UPDATE OF enrt_rt_id;
SELECT DISTINCT elig_per_elctbl_chc_id
FROM ben_elig_per_elctbl_chc
WHERE per_in_ler_id = p_per_in_ler_id;
SELECT enrt_bnft_id
FROM ben_enrt_bnft
WHERE elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
FOR UPDATE OF enrt_bnft_id;
SELECT enrt_prem_id
FROM ben_enrt_prem
WHERE elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
FOR UPDATE OF enrt_prem_id;
SELECT enrt_rt_id
FROM ben_enrt_rt
WHERE elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
FOR UPDATE OF enrt_rt_id;
SELECT elctbl_chc_ctfn_id
FROM ben_elctbl_chc_ctfn
WHERE elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
FOR UPDATE OF elctbl_chc_ctfn_id;
SELECT DISTINCT elig_cvrd_dpnt_id
FROM ben_prtt_enrt_rslt_f b1, ben_elig_cvrd_dpnt b2
WHERE b1.per_in_ler_id = p_per_in_ler_id
AND b2.prtt_enrt_rslt_id = b1.prtt_enrt_rslt_id;
SELECT prmry_care_prvdr_id
FROM ben_prmry_care_prvdr_f
WHERE elig_cvrd_dpnt_id = p_elig_cvrd_dpnt_id
FOR UPDATE OF prmry_care_prvdr_id;
SELECT cbr_per_in_ler_id
FROM ben_cbr_per_in_ler
WHERE per_in_ler_id = p_per_in_ler_id
FOR UPDATE OF cbr_per_in_ler_id;
SELECT elig_dpnt_id
FROM ben_elig_dpnt
WHERE per_in_ler_id = p_per_in_ler_id
FOR UPDATE OF elig_dpnt_id;
SELECT elig_per_elctbl_chc_id
FROM ben_elig_per_elctbl_chc
WHERE per_in_ler_id = p_per_in_ler_id
FOR UPDATE OF elig_per_elctbl_chc_id;
SELECT elig_per_opt_id
FROM ben_elig_per_opt_f
WHERE per_in_ler_id = p_per_in_ler_id
FOR UPDATE OF elig_per_opt_id;
SELECT bkup_tbl_id
FROM ben_le_clsn_n_rstr
WHERE per_in_ler_id = p_per_in_ler_id
FOR UPDATE OF bkup_tbl_id;
SELECT pil_elctbl_chc_popl_id
FROM ben_pil_elctbl_chc_popl
WHERE per_in_ler_id = p_per_in_ler_id
FOR UPDATE OF pil_elctbl_chc_popl_id;
SELECT pl_bnf_id
FROM ben_pl_bnf_f
WHERE per_in_ler_id = p_per_in_ler_id
FOR UPDATE OF pl_bnf_id;
SELECT prtt_enrt_rslt_id
FROM ben_prtt_enrt_rslt_f
WHERE per_in_ler_id = p_per_in_ler_id
FOR UPDATE OF prtt_enrt_rslt_id;
DELETE FROM ben_prtt_prem_by_mo_f
WHERE CURRENT OF c3;
DELETE FROM ben_prmry_care_prvdr_f
WHERE CURRENT OF c5;
DELETE FROM ben_bnft_prvdd_ldgr_f
WHERE CURRENT OF c6;
DELETE FROM ben_elig_cvrd_dpnt_f
WHERE CURRENT OF c7;
DELETE FROM ben_prtt_enrt_ctfn_prvdd_f
WHERE CURRENT OF c8;
DELETE FROM ben_prtt_prem_f
WHERE CURRENT OF c9;
DELETE FROM ben_prtt_rt_val
WHERE CURRENT OF c10;
DELETE FROM ben_prtt_enrt_actn_f
WHERE CURRENT OF c11;
DELETE FROM ben_pl_bnf_ctfn_prvdd_f
WHERE CURRENT OF c13;
DELETE FROM ben_cvrd_dpnt_ctfn_prvdd_f
WHERE CURRENT OF c14;
DELETE FROM ben_enrt_rt
WHERE CURRENT OF c16;
DELETE FROM ben_enrt_bnft
WHERE CURRENT OF c18;
DELETE FROM ben_enrt_prem
WHERE CURRENT OF c19;
DELETE FROM ben_enrt_rt
WHERE CURRENT OF c20;
DELETE FROM ben_elctbl_chc_ctfn
WHERE CURRENT OF c21;
DELETE FROM ben_prmry_care_prvdr_f
WHERE CURRENT OF c23;
DELETE FROM ben_cbr_per_in_ler
WHERE CURRENT OF c24;
DELETE FROM ben_elig_dpnt
WHERE CURRENT OF c25;
DELETE FROM ben_elig_per_elctbl_chc
WHERE CURRENT OF c26;
DELETE FROM ben_elig_per_opt_f
WHERE CURRENT OF c27;
DELETE FROM ben_le_clsn_n_rstr
WHERE CURRENT OF c28;
DELETE FROM ben_pil_elctbl_chc_popl
WHERE CURRENT OF c29;
DELETE FROM ben_pl_bnf_f
WHERE CURRENT OF c30;
DELETE FROM ben_prtt_enrt_rslt_f
WHERE CURRENT OF c31;
END delete_life_events;
PROCEDURE delete_participant_information (
p_person_id IN NUMBER,
p_per_in_ler_id IN NUMBER default NULL
) IS
l_proc varchar2(100):= g_package||'delete_participant_information';
SELECT DISTINCT prtt_enrt_rslt_id
FROM ben_prtt_enrt_rslt_f
WHERE person_id = p_person_id
and per_in_ler_id = nvl(p_per_in_ler_id,per_in_ler_id);
SELECT prmry_care_prvdr_id
FROM ben_prmry_care_prvdr_f
WHERE prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
FOR UPDATE OF prmry_care_prvdr_id;
SELECT bnft_prvdd_ldgr_id
FROM ben_bnft_prvdd_ldgr_f
WHERE prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
FOR UPDATE OF bnft_prvdd_ldgr_id;
SELECT elig_cvrd_dpnt_id
FROM ben_elig_cvrd_dpnt_f
WHERE prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
FOR UPDATE OF elig_cvrd_dpnt_id;
SELECT prtt_enrt_ctfn_prvdd_id
FROM ben_prtt_enrt_ctfn_prvdd_f
WHERE prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
FOR UPDATE OF prtt_enrt_ctfn_prvdd_id;
SELECT prtt_prem_id
FROM ben_prtt_prem_f
WHERE prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
FOR UPDATE OF prtt_prem_id;
SELECT prtt_rt_val_id
FROM ben_prtt_rt_val
WHERE prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
FOR UPDATE OF prtt_rt_val_id;
SELECT prtt_enrt_actn_id
FROM ben_prtt_enrt_actn_f
WHERE prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
FOR UPDATE OF prtt_enrt_actn_id;
DELETE FROM ben_prmry_care_prvdr_f
WHERE CURRENT OF c2;
DELETE FROM ben_bnft_prvdd_ldgr_f
WHERE CURRENT OF c3;
DELETE FROM ben_elig_cvrd_dpnt_f
WHERE CURRENT OF c4;
DELETE FROM ben_prtt_enrt_ctfn_prvdd_f
WHERE CURRENT OF c5;
DELETE FROM ben_prtt_prem_f
WHERE CURRENT OF c6;
DELETE FROM ben_prtt_rt_val
WHERE CURRENT OF c7;
DELETE FROM ben_prtt_enrt_actn_f
WHERE CURRENT OF c8;
END delete_participant_information;
PROCEDURE delete_benefit_action_children (
p_person_id IN NUMBER,
p_per_in_ler_id IN NUMBER DEFAULT NULL
) IS
l_proc varchar2(100):= g_package||'delete_benefit_action_children';
SELECT DISTINCT benefit_action_id
FROM ben_benefit_actions bba
--,ben_ler_f ler
WHERE person_id = p_person_id
and ( (p_per_in_ler_id is not NULL and MODE_CD = 'U') or (p_per_in_ler_id is NULL) );
SELECT reporting_id
FROM ben_reporting
WHERE benefit_action_id = p_benefit_action_id
FOR UPDATE OF reporting_id;
SELECT person_action_id
FROM ben_person_actions
WHERE benefit_action_id = p_benefit_action_id
FOR UPDATE OF person_action_id;
SELECT range_id
FROM ben_batch_ranges
WHERE benefit_action_id = p_benefit_action_id
FOR UPDATE OF range_id;
DELETE FROM ben_reporting
WHERE CURRENT OF c2;
DELETE FROM ben_person_actions
WHERE CURRENT OF c3;
DELETE FROM ben_batch_ranges
WHERE CURRENT OF c4;
END delete_benefit_action_children;
PROCEDURE delete_reimbmt_rqst (
p_person_id NUMBER
) IS
--** C|c1
--** CN|c1|Fetch the prtt_reimbmt_rqst_id from ben_prtt_reimbmt_rqst_f associated with a person_id.
CURSOR c1 IS
SELECT prtt_reimbmt_rqst_id
FROM ben_prtt_reimbmt_rqst_f
WHERE submitter_person_id = p_person_id
OR recipient_person_id = p_person_id
OR provider_person_id = p_person_id
OR provider_ssn_person_id = p_person_id
OR contact_relationship_id IN
(SELECT contact_relationship_id
FROM per_contact_relationships
WHERE person_id = p_person_id)
FOR UPDATE OF prtt_reimbmt_rqst_id;
SELECT prtt_reimbmt_recon_id
FROM ben_prtt_reimbmt_recon
WHERE prtt_reimbmt_rqst_id = p_prtt_reimbmt_rqst_id
FOR UPDATE OF prtt_reimbmt_recon_id;
DELETE FROM ben_prtt_reimbmt_recon
WHERE CURRENT OF c2;
DELETE FROM ben_prtt_reimbmt_rqst_f
WHERE CURRENT OF c1;
END delete_reimbmt_rqst;
/* Bug 13540525 : Added new parameter 'p_per_in_ler_id' to pass Unrestricted per_in_ler_id. If p_per_in_ler_id is not NULL then delete Unrestricted LE
enrollment data, else delete all Benefits data*/
PROCEDURE delete_ben_rows (
p_person_id NUMBER,
p_per_in_ler_id IN NUMBER default NULL
) IS
--** C|c01
CURSOR c01 (
p_benefit_action_id IN NUMBER
) IS
SELECT benefit_action_id
FROM ben_reporting
WHERE benefit_action_id = p_benefit_action_id
FOR UPDATE OF benefit_action_id;
SELECT DISTINCT elig_per_id
FROM ben_elig_per_f
WHERE person_id = p_person_id
and per_in_ler_id = nvl(p_per_in_ler_id,per_in_ler_id);
SELECT DISTINCT element_entry_id
FROM per_all_assignments_f paf, pay_element_entries_f pee
WHERE paf.person_id = p_person_id
AND pee.assignment_id = paf.assignment_id;
SELECT batch_actn_item_id
FROM ben_batch_actn_item_info a,ben_benefit_actions b
WHERE a.person_id = p_person_id
and a.BENEFIT_ACTION_ID = b.BENEFIT_ACTION_ID
and b.person_id = p_person_id--Bug 14505730
and ( (p_per_in_ler_id is not NULL and b.MODE_CD = 'U') or (p_per_in_ler_id is NULL) )
FOR UPDATE OF batch_actn_item_id;
SELECT batch_benft_cert_id
FROM ben_batch_bnft_cert_info a,ben_benefit_actions b
WHERE a.person_id = p_person_id
and a.BENEFIT_ACTION_ID = b.BENEFIT_ACTION_ID
and b.person_id = p_person_id--Bug 14505730
and ( (p_per_in_ler_id is not NULL and b.MODE_CD = 'U') or (p_per_in_ler_id is NULL) )
FOR UPDATE OF batch_benft_cert_id;
SELECT batch_commu_id
FROM ben_batch_commu_info a,ben_benefit_actions b
WHERE a.person_id = p_person_id
and a.BENEFIT_ACTION_ID = b.BENEFIT_ACTION_ID
and b.person_id = p_person_id--Bug 14505730
and ( (p_per_in_ler_id is not NULL and b.MODE_CD = 'U') or (p_per_in_ler_id is NULL) )
FOR UPDATE OF batch_commu_id;
SELECT batch_dpnt_id
FROM ben_batch_dpnt_info a,ben_benefit_actions b
WHERE a.person_id = p_person_id
and a.BENEFIT_ACTION_ID = b.BENEFIT_ACTION_ID
and b.person_id = p_person_id--Bug 14505730
and ( (p_per_in_ler_id is not NULL and b.MODE_CD = 'U') or (p_per_in_ler_id is NULL) )
FOR UPDATE OF batch_dpnt_id;
SELECT batch_elctbl_id
FROM ben_batch_elctbl_chc_info a,ben_benefit_actions b
WHERE a.person_id = p_person_id
and a.BENEFIT_ACTION_ID = b.BENEFIT_ACTION_ID
and b.person_id = p_person_id--Bug 14505730
and ( (p_per_in_ler_id is not NULL and b.MODE_CD = 'U') or (p_per_in_ler_id is NULL) )
FOR UPDATE OF batch_elctbl_id;
SELECT batch_elig_id
FROM ben_batch_elig_info a,ben_benefit_actions b
WHERE a.person_id = p_person_id
and a.BENEFIT_ACTION_ID = b.BENEFIT_ACTION_ID
and b.person_id = p_person_id--Bug 14505730
and ( (p_per_in_ler_id is not NULL and b.MODE_CD = 'U') or (p_per_in_ler_id is NULL) )
FOR UPDATE OF batch_elig_id;
SELECT batch_ler_id
FROM ben_batch_ler_info a,ben_benefit_actions b
WHERE a.person_id = p_person_id
and a.BENEFIT_ACTION_ID = b.BENEFIT_ACTION_ID
and b.person_id = p_person_id--Bug 14505730
and ( (p_per_in_ler_id is not NULL and b.MODE_CD = 'U') or (p_per_in_ler_id is NULL) )
FOR UPDATE OF batch_ler_id;
SELECT batch_rt_id
FROM ben_batch_rate_info a,ben_benefit_actions b
WHERE a.person_id = p_person_id
and a.BENEFIT_ACTION_ID = b.BENEFIT_ACTION_ID
and b.person_id = p_person_id--Bug 14505730
and ( (p_per_in_ler_id is not NULL and b.MODE_CD = 'U') or (p_per_in_ler_id is NULL) )
FOR UPDATE OF batch_rt_id;
SELECT reporting_id
FROM ben_reporting rep,
ben_person_actions pat,
ben_benefit_actions b
WHERE pat.person_id = p_person_id
and pat.BENEFIT_ACTION_ID = b.BENEFIT_ACTION_ID
and ( (p_per_in_ler_id is not NULL and b.MODE_CD = 'U') or (p_per_in_ler_id is NULL) )
AND rep.benefit_action_id = pat.benefit_action_id /* Bug 4882374 : Perf */
AND rep.person_id = p_person_id --Bug 14505730
AND b.person_id = p_person_id--Bug 14505730
FOR UPDATE OF rep.reporting_id;
SELECT person_action_id
FROM ben_person_actions a,ben_benefit_actions b
WHERE a.person_id = p_person_id
and a.BENEFIT_ACTION_ID = b.BENEFIT_ACTION_ID
and b.person_id = p_person_id--Bug 14505730
and ( (p_per_in_ler_id is not NULL and b.MODE_CD = 'U') or (p_per_in_ler_id is NULL) )
FOR UPDATE OF person_action_id;
SELECT benefit_action_id
FROM ben_benefit_actions b
WHERE person_id = p_person_id
and ( (p_per_in_ler_id is not NULL and b.MODE_CD = 'U') or (p_per_in_ler_id is NULL) )
FOR UPDATE OF benefit_action_id;
SELECT cbr_quald_bnf_id
FROM ben_cbr_quald_bnf
WHERE cvrd_emp_person_id = p_person_id
FOR UPDATE OF cbr_quald_bnf_id;
SELECT crt_ordr_cvrd_per_id
FROM ben_crt_ordr_cvrd_per
WHERE crt_ordr_id IN (SELECT crt_ordr_id
FROM ben_crt_ordr
WHERE person_id = p_person_id)
FOR UPDATE OF crt_ordr_cvrd_per_id;
SELECT crt_ordr_id
FROM ben_crt_ordr
WHERE person_id = p_person_id
FOR UPDATE OF crt_ordr_id;
SELECT elig_per_id
FROM ben_elig_per_f
WHERE person_id = p_person_id
and per_in_ler_id = nvl(p_per_in_ler_id,per_in_ler_id)
FOR UPDATE OF elig_per_id;
SELECT ext_chg_evt_log_id
FROM ben_ext_chg_evt_log
WHERE person_id = p_person_id
FOR UPDATE OF ext_chg_evt_log_id;
SELECT ext_rslt_dtl_id
FROM ben_ext_rslt_dtl
WHERE person_id = p_person_id
FOR UPDATE OF ext_rslt_dtl_id;
SELECT ext_rslt_err_id
FROM ben_ext_rslt_err
WHERE person_id = p_person_id
FOR UPDATE OF ext_rslt_err_id;
SELECT per_bnfts_bal_id
FROM ben_per_bnfts_bal_f
WHERE person_id = p_person_id
FOR UPDATE OF per_bnfts_bal_id;
SELECT per_cm_id
FROM ben_per_cm_f
WHERE person_id = p_person_id
and per_in_ler_id = nvl(p_per_in_ler_id,per_in_ler_id)
FOR UPDATE OF per_cm_id;
SELECT per_dlvry_mthd_id
FROM ben_per_dlvry_mthd_f
WHERE person_id = p_person_id
FOR UPDATE OF per_dlvry_mthd_id;
SELECT per_in_ler_id
FROM ben_per_in_ler
WHERE person_id = p_person_id
and per_in_ler_id = nvl(p_per_in_ler_id,per_in_ler_id)
FOR UPDATE OF per_in_ler_id;
SELECT per_in_lgl_enty_id
FROM ben_per_in_lgl_enty_f
WHERE person_id = p_person_id
FOR UPDATE OF per_in_lgl_enty_id;
SELECT per_in_org_unit_id
FROM ben_per_in_org_unit_f
WHERE person_id = p_person_id
FOR UPDATE OF per_in_org_unit_id;
SELECT per_pin_id
FROM ben_per_pin_f
WHERE person_id = p_person_id
FOR UPDATE OF per_pin_id;
SELECT ptnl_ler_for_per_id
FROM ben_ptnl_ler_for_per ptnl,ben_ler_f ler
WHERE person_id = p_person_id
and ler.ler_id = ptnl.ler_id
and ( (p_per_in_ler_id is not NULL and ler.typ_cd = 'SCHEDDU') or (p_per_in_ler_id is NULL) )
and trunc(sysdate) between ler.effective_start_date and ler.effective_end_date
FOR UPDATE OF ptnl_ler_for_per_id;
SELECT popl_org_id
FROM ben_popl_org_f
WHERE person_id = p_person_id
FOR UPDATE OF popl_org_id;
SELECT ext_crit_val_id
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 (
p_person_id
))
FOR UPDATE OF ext_crit_val_id;
SELECT pl_bnf_id
FROM ben_pl_bnf_f
WHERE bnf_person_id = p_person_id
and per_in_ler_id = nvl(p_per_in_ler_id,per_in_ler_id)
FOR UPDATE OF pl_bnf_id;
SELECT PIL_ELCTBL_CHC_POPL_ID
FROM ben_pil_elctbl_chc_popl
WHERE per_in_ler_id = nvl(p_per_in_ler_id,per_in_ler_id)
FOR UPDATE OF PIL_ELCTBL_CHC_POPL_ID;
SELECT ELIG_PER_ELCTBL_CHC_ID
FROM BEN_ELIG_PER_ELCTBL_CHC
WHERE per_in_ler_id = nvl(p_per_in_ler_id,per_in_ler_id)
FOR UPDATE OF ELIG_PER_ELCTBL_CHC_ID;
delete_dependent_information (
p_person_id,
p_per_in_ler_id
);
delete_communications (
p_person_id,
p_per_in_ler_id
);
delete_life_events (
p_person_id,
p_per_in_ler_id
);
delete_participant_information (
p_person_id,
p_per_in_ler_id
);
/*Bug 14505730 Duplicate delete cursors
delete_benefit_action_children (
p_person_id,
p_per_in_ler_id
);
/* Bug 13067080; Procedure added to delete person related CWB table records */
delete_missing_cwb_tables(
p_person_id
);
DELETE FROM ben_elig_per_wv_pl_typ_f
WHERE elig_per_id = r7.elig_per_id;
DELETE FROM ben_prtt_vstg_f
WHERE element_entry_id = r9.element_entry_id;
delete_reimbmt_rqst (
p_person_id
);
DELETE FROM ben_batch_actn_item_info
WHERE CURRENT OF c20;
DELETE FROM ben_batch_bnft_cert_info
WHERE CURRENT OF c21;
DELETE FROM ben_batch_commu_info
WHERE CURRENT OF c22;
DELETE FROM ben_batch_dpnt_info
WHERE CURRENT OF c23;
DELETE FROM ben_batch_elctbl_chc_info
WHERE CURRENT OF c24;
DELETE FROM ben_batch_elig_info
WHERE CURRENT OF c25;
DELETE FROM ben_batch_ler_info
WHERE CURRENT OF c26;
DELETE FROM ben_batch_rate_info
WHERE CURRENT OF c27;
DELETE FROM ben_reporting
WHERE CURRENT OF c28;
DELETE FROM ben_person_actions
WHERE CURRENT OF c29;
DELETE FROM ben_benefit_actions
WHERE CURRENT OF c30;
DELETE FROM ben_cbr_quald_bnf
WHERE CURRENT OF c31;
DELETE FROM ben_crt_ordr_cvrd_per
WHERE CURRENT OF c32;
DELETE FROM ben_crt_ordr
WHERE CURRENT OF c33;
DELETE FROM ben_elig_per_f
WHERE CURRENT OF c34;
DELETE FROM ben_ext_chg_evt_log
WHERE CURRENT OF c35;
DELETE FROM ben_ext_rslt_dtl
WHERE CURRENT OF c36;
DELETE FROM ben_ext_rslt_err
WHERE CURRENT OF c37;
DELETE FROM ben_per_bnfts_bal_f
WHERE CURRENT OF c38;
DELETE FROM ben_per_cm_f
WHERE CURRENT OF c39;
DELETE FROM ben_per_dlvry_mthd_f
WHERE CURRENT OF c40;
DELETE FROM BEN_CWB_AUDIT WHERE group_per_in_ler_id=l_id;
DELETE FROM BEN_CWB_GROUP_HRCHY WHERE emp_per_in_ler_id=l_id;
DELETE FROM BEN_CWB_PERSON_GROUPS WHERE group_per_in_ler_id=l_id;
DELETE FROM BEN_CWB_PERSON_TASKS WHERE group_per_in_ler_id=l_id;
DELETE FROM BEN_CWB_HRCHY WHERE EMP_PIL_ELCTBL_CHC_POPL_ID = l_pil_elctbl_chc_popl_id;
DELETE FROM BEN_CWB_MGR_HRCHY WHERE EMP_ELIG_PER_ELCTBL_CHC_ID = l_elig_per_elctbl_chc_id;
DELETE FROM BEN_CWB_MGR_HRCHY_RBV WHERE EMP_ELIG_PER_ELCTBL_CHC_ID = l_elig_per_elctbl_chc_id;
DELETE FROM ben_per_in_ler
WHERE CURRENT OF c41;
DELETE from BEN_CWB_PERSON_INFO where person_id = p_person_id;
DELETE from BEN_CWB_PERSON_RATES where person_id = p_person_id;
DELETE from BEN_CWB_SUMMARY where person_id = p_person_id;
DELETE FROM ben_per_in_lgl_enty_f
WHERE CURRENT OF c42;
DELETE FROM ben_per_in_org_unit_f
WHERE CURRENT OF c43;
DELETE FROM ben_per_pin_f
WHERE CURRENT OF c44;
DELETE FROM ben_ptnl_ler_for_per
WHERE CURRENT OF c45;
DELETE FROM ben_popl_org_f
WHERE CURRENT OF c46;
DELETE FROM ben_ext_crit_val
WHERE CURRENT OF c47;
DELETE FROM ben_pl_bnf_f
WHERE CURRENT OF c48;
END delete_ben_rows;
PROCEDURE check_ben_rows_before_delete(
p_person_id number ,
p_effective_date date
) is
--
l_proc varchar2(200) := 'ben_person_delete.check_ben_rows_before_delete' ;
Allow Delete when UnRestricted LE is in STRTD state
Do Not Allow Delete when Other types of LE is in STRTD state
Do Not Allow Delete when Other types of LE is in PROCD state
Allow Delete when Other types of LE is in BCKDT/VOIDD state
*/
--cursor for the pils with STRTD status
CURSOR c_pil( p_person_id number ,
p_effective_date date
) is
SELECT 'Y'
FROM ben_per_in_ler pil,
ben_ler_f ler
WHERE pil.person_id = p_person_id
AND pil.per_in_ler_stat_cd = 'STRTD'
AND pil.ler_id = ler.ler_id
AND pil.business_group_id = ler.business_group_id
AND p_effective_date between ler.effective_start_date and
ler.effective_end_date
AND ler.typ_cd <> 'SCHEDDU' ;
SELECT 'Y'
FROM ben_prtt_enrt_rslt_f pen,
ben_ler_f ler
WHERE pen.person_id=p_person_id and
pen.prtt_enrt_rslt_stat_cd is null and
-- pen.sspndd_flag='N' and Needs to resolve suspended record also
pen.effective_end_date = hr_api.g_eot and
p_effective_date between pen.enrt_cvg_strt_dt and
pen.enrt_cvg_thru_dt
and ler.ler_id=pen.ler_id
AND pen.business_group_id = ler.business_group_id
AND p_effective_date between ler.effective_start_date and
ler.effective_end_date
AND ler.typ_cd <> 'SCHEDDU';
SELECT 'Y'
FROM ben_prtt_rt_val prv,
ben_prtt_enrt_rslt_f pen,
ben_ler_f ler
WHERE
pen.person_id=p_person_id and
prv.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id and
pen.prtt_enrt_rslt_stat_cd is null and
p_effective_date between prv.rt_strt_dt and prv.rt_end_dt and
prv.business_group_id = pen.business_group_id and
prv.prtt_rt_val_stat_cd is null
and ler.ler_id=pen.ler_id
AND pen.business_group_id = ler.business_group_id
AND p_effective_date between ler.effective_start_date and
ler.effective_end_date
AND ler.typ_cd <> 'SCHEDDU';
SELECT 'Y'
FROM ben_elig_cvrd_dpnt_f ecd,
ben_per_in_ler pil,
ben_ler_f ler,
ben_prtt_enrt_rslt_f pen
WHERE
pil.person_id=p_person_id and
pil.per_in_ler_id = ecd.per_in_ler_id and
pil.business_group_id = ecd.business_group_id and
p_effective_date between ecd.cvg_strt_dt and cvg_thru_dt and
ecd.effective_end_date = hr_api.g_eot
and ecd.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id and
pen.prtt_enrt_rslt_stat_cd is null
AND pil.ler_id = ler.ler_id
AND pil.business_group_id = ler.business_group_id
AND p_effective_date between ler.effective_start_date and
ler.effective_end_date
AND ler.typ_cd <> 'SCHEDDU';
SELECT 'Y'
FROM ben_elig_cvrd_dpnt_f ecd,
ben_per_in_ler pil,
ben_ler_f ler,
ben_prtt_enrt_rslt_f pen
WHERE
ecd.dpnt_person_id = p_person_id and
p_effective_date between ecd.cvg_strt_dt and cvg_thru_dt and
ecd.effective_end_date = hr_api.g_eot
and pil.per_in_ler_id = ecd.per_in_ler_id
and pil.business_group_id = ecd.business_group_id
and ecd.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id and
pen.prtt_enrt_rslt_stat_cd is null
AND pil.ler_id = ler.ler_id
AND pil.business_group_id = ler.business_group_id
AND p_effective_date between ler.effective_start_date and
ler.effective_end_date
AND ler.typ_cd <> 'SCHEDDU';
SELECT 'Y'
FROM ben_pl_bnf_f pbn,
ben_per_in_ler pil,
ben_ler_f ler,
ben_prtt_enrt_rslt_f pen
WHERE
pbn.bnf_person_id = p_person_id and
p_effective_date between pbn.dsgn_strt_dt and pbn.dsgn_thru_dt and
pbn.effective_end_date = hr_api.g_eot
and pbn.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id and
pen.prtt_enrt_rslt_stat_cd is null
and pil.per_in_ler_id=pbn.per_in_ler_id
AND pil.business_group_id = pbn.business_group_id
AND pil.ler_id = ler.ler_id
AND pil.business_group_id = ler.business_group_id
AND p_effective_date between ler.effective_start_date and
ler.effective_end_date
AND ler.typ_cd <> 'SCHEDDU';
END check_ben_rows_before_delete ;
/* Bug 13540525: Enhancement to delete Unrestricted LE and its related data.Added new procedure delete_unrestricted_le_enrts.
Check whether Payroll is processed for the employee. If not check if element entries are present for the enrollments.
If yes,delete the element entries. Now delete the Benefits data.*/
procedure delete_unrestricted_le_enrts(errbuf out nocopy varchar2
,retcode out nocopy number
,p_business_group_id in number
,p_effective_date in varchar2
,p_person_id in number
,p_validate in varchar2 default 'N') is
l_effective_date date;
select '1' from
ben_prtt_enrt_rslt_f pen,
ben_per_in_ler pil, ben_ler_f ler,
ben_prtt_rt_val prv
where pen.person_id = p_person_id
and pen.business_group_id = p_business_group_id
and pen.per_in_ler_id = pil.per_in_ler_id
and pen.prtt_enrt_rslt_stat_cd is null
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
and pil.ler_id = ler.ler_id
and ler.typ_cd in ('SCHEDDU')
and l_effective_date between ler.effective_start_date and ler.effective_end_date
and ler.business_group_id = p_business_group_id
and pen.enrt_cvg_strt_dt <= pen.enrt_cvg_thru_dt
and prv.per_in_ler_id = pil.per_in_ler_id
and prv.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
and prv.PRTT_RT_VAL_STAT_CD is NULL
and prv.ELEMENT_ENTRY_VALUE_ID is not NULL;
select 'y' from dual where exists
(SELECT NULL
FROM pay_payroll_actions pac,
pay_assignment_actions act,
per_assignments_f asg
WHERE asg.person_id = p_person_id
AND act.assignment_id = asg.assignment_id
--AND asg.assignment_type = p_type
AND pac.payroll_action_id = act.payroll_action_id
AND pac.action_status = 'C');
select
distinct ele.element_entry_id ele_entry_id,
ele.object_version_number ovn,
ele.effective_start_date esd
from
ben_prtt_enrt_rslt_f pen,
ben_per_in_ler pil,
ben_ler_f ler,
ben_prtt_rt_val prv,
pay_element_entry_values_f elv,
pay_element_entries_f ele
where pen.person_id = p_person_id
and pen.business_group_id = p_business_group_id
and pen.per_in_ler_id = pil.per_in_ler_id
and pen.prtt_enrt_rslt_stat_cd is null
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
and pil.ler_id = ler.ler_id
and ler.typ_cd in ('SCHEDDU')
and l_effective_date between ler.effective_start_date and ler.effective_end_date
and ler.business_group_id = p_business_group_id
and pen.enrt_cvg_strt_dt <= pen.enrt_cvg_thru_dt
and prv.per_in_ler_id = pil.per_in_ler_id
and prv.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
and prv.PRTT_RT_VAL_STAT_CD is NULL
and prv.ELEMENT_ENTRY_VALUE_ID = elv.ELEMENT_ENTRY_VALUE_ID
and elv.element_entry_id = ele.element_entry_id
order by ele.effective_start_date desc;
select pil.per_in_ler_id
from ben_per_in_ler pil,
ben_ler_f ler
where pil.ler_id = ler.ler_id
and l_effective_date between ler.effective_start_date and ler.effective_end_date
and pil.person_id = p_person_id
and ler.business_group_id = p_business_group_id
and ler.typ_cd in ('SCHEDDU');
l_delete_warning boolean;
l_proc varchar2(100) := 'g_package'||'.delete_unrestricted_le_enrts';
,p_program_update_date => sysdate
);
py_element_entry_api.delete_element_entry
(p_validate => l_validate
,p_datetrack_delete_mode => hr_api.g_zap
,p_effective_date => l_ele_rec.esd
,p_element_entry_id => l_ele_rec.ele_entry_id
,p_object_version_number => l_ele_rec.ovn
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_delete_warning => l_delete_warning
);
ben_person_delete.delete_ben_rows(p_person_id,l_per_in_ler_id);
ben_person_delete.delete_ben_rows(p_person_id,l_per_in_ler_id);
update ben_person_actions
set action_status_cd = 'E'
where person_action_id = l_person_action_id;
end delete_unrestricted_le_enrts;