[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
13-May-09 stee 115.35 Bug 8463981 : Change check_elig_cbr_quald_bnf to select
the latest quald bnf row.
07-Sep-09 krupani 115.36 Bug 8872046 : Corrected the cursor c1 in check_prtt_in_anthr_pl_elig
24-Sep-09 stee 115.37 Bug 8685338 : Removed the exception handling when a rule fails.
01-Jan-12 amnaraya 120.6.12000000.12 Enh 13478736: Broke dual Maintenance from 11i - 12.0B
Implemented the logic for newly added Assignment Types CWAO, EAAO, ECWA for
OLM Enh. OLM will always pass the correct assignment_id for evaluating the eligibility
so created a new procedure get_assgn_object in ben_person_object for fetching the assignment
details by passing the person_id and assignment_id.
06-Mar-12 velvanop 120.6.12000000.13 Enh Bug 13809302: Evaluate the Eligibility Profiles attached to the Communication and Life Event. Added new param p_source
to function Eligible. If the param is 'C' or 'L', evaluate the eligibility profiles attached under Related coverages tab.
*/
--------------------------------------------------------------------------------
--
g_package varchar2(30) := 'ben_evaluate_elig_profiles.';
select bep.rt_hrs_wkd_val
from ben_elig_per_f bep,
ben_per_in_ler pil
where bep.person_id = p_person_id
and nvl(bep.pgm_id,-1) = nvl(p_pgm_id,-1)
and nvl(bep.pl_id,-1) = nvl(p_pl_id,-1)
and nvl(l_fonm_cvg_strt_dt,p_effective_date) between bep.effective_start_date
and bep.effective_end_date
and pil.per_in_ler_id(+)=bep.per_in_ler_id
and pil.business_group_id(+)=bep.business_group_id
and (pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT') -- found row condition
or pil.per_in_ler_stat_cd is null); -- outer join condition
select epo.rt_hrs_wkd_val
from ben_elig_per_f bep, ben_elig_per_opt_f epo,
ben_per_in_ler pil
where bep.person_id = p_person_id
and bep.elig_per_id = epo.elig_per_id
and epo.opt_id = p_opt_id
and nvl(bep.pgm_id,-1) = nvl(p_pgm_id,-1)
and nvl(bep.pl_id,-1) = nvl(p_pl_id,-1)
and nvl(l_fonm_cvg_strt_dt,p_effective_date) between epo.effective_start_date
and epo.effective_end_date
and nvl(l_fonm_cvg_strt_dt,p_effective_date) between bep.effective_start_date
and bep.effective_end_date
and pil.per_in_ler_id(+)=bep.per_in_ler_id
and pil.business_group_id(+)=bep.business_group_id
and (pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT') -- found row condition
or pil.per_in_ler_stat_cd is null); -- outer join condition
select epo.rt_hrs_wkd_val
from ben_elig_per_f bep, ben_elig_per_opt_f epo,
ben_per_in_ler pil
where bep.person_id = p_person_id
and bep.elig_per_id = epo.elig_per_id
and epo.opt_id = p_opt_id
and nvl(bep.pgm_id,-1) = nvl(p_pgm_id,-1)
and nvl(bep.plip_id,-1) = nvl(p_plip_id,-1)
and nvl(l_fonm_cvg_strt_dt,p_effective_date) between epo.effective_start_date
and epo.effective_end_date
and nvl(l_fonm_cvg_strt_dt,p_effective_date) between bep.effective_start_date
and bep.effective_end_date
and pil.per_in_ler_id(+)=bep.per_in_ler_id
and pil.business_group_id(+)=bep.business_group_id
and (pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT') -- found row condition
or pil.per_in_ler_stat_cd is null); -- outer join condition
select bep.rt_pct_fl_tm_val
from ben_elig_per_f bep,
ben_per_in_ler pil
where bep.person_id = p_person_id
and nvl(bep.pgm_id,-1) = nvl(p_pgm_id,-1)
and nvl(bep.pl_id,-1) = nvl(p_pl_id,-1)
and p_effective_date
between bep.effective_start_date
and bep.effective_end_date
and pil.per_in_ler_id(+)=bep.per_in_ler_id
and pil.business_group_id(+)=bep.business_group_id+0
and (pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT') -- found row condition
or pil.per_in_ler_stat_cd is null -- outer join condition
);
select epo.rt_pct_fl_tm_val
from ben_elig_per_f bep, ben_elig_per_opt_f epo,
ben_per_in_ler pil
where bep.person_id = p_person_id
and bep.elig_per_id = epo.elig_per_id
and epo.opt_id = p_opt_id
and nvl(bep.pgm_id,-1) = nvl(p_pgm_id,-1)
and nvl(bep.pl_id,-1) = nvl(p_pl_id,-1)
and p_effective_date between epo.effective_start_date
and epo.effective_end_date
and p_effective_date between bep.effective_start_date
and bep.effective_end_date
and pil.per_in_ler_id(+)=bep.per_in_ler_id
and pil.business_group_id(+)=bep.business_group_id
and (pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT') -- found row condition
or pil.per_in_ler_stat_cd is null); -- outer join condition
select epo.rt_pct_fl_tm_val
from ben_elig_per_f bep, ben_elig_per_opt_f epo,
ben_per_in_ler pil
where bep.person_id = p_person_id
and bep.elig_per_id = epo.elig_per_id
and epo.opt_id = p_opt_id
and nvl(bep.pgm_id,-1) = nvl(p_pgm_id,-1)
and nvl(bep.plip_id,-1) = nvl(p_plip_id,-1)
and p_effective_date between epo.effective_start_date
and epo.effective_end_date
and p_effective_date between bep.effective_start_date
and bep.effective_end_date
and pil.per_in_ler_id(+)=bep.per_in_ler_id
and pil.business_group_id(+)=bep.business_group_id
and (pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT') -- found row condition
or pil.per_in_ler_stat_cd is null); -- outer join condition
l_score_tab.delete;
select 'Y'
from ff_fdi_usages_f
where FORMULA_ID = cv_formula_id
and ITEM_NAME = 'ASSIGNMENT_ID'
and usage = 'U';
select ppg.people_group_id,
ppg.segment1 ,
ppg.segment2 ,
ppg.segment3 ,
ppg.segment4 ,
ppg.segment5 ,
ppg.segment6 ,
ppg.segment7 ,
ppg.segment8 ,
ppg.segment9 ,
ppg.segment10,
ppg.segment11,
ppg.segment12,
ppg.segment13,
ppg.segment14,
ppg.segment15,
ppg.segment16,
ppg.segment17,
ppg.segment18,
ppg.segment19,
ppg.segment20,
ppg.segment21,
ppg.segment22,
ppg.segment23,
ppg.segment24,
ppg.segment25,
ppg.segment26,
ppg.segment27,
ppg.segment28,
ppg.segment29,
ppg.segment30
from pay_people_groups ppg
where ppg.people_group_id = p_people_group_id ;
select elig_svc.elig_svc_area_prte_id,
elig_svc.excld_flag,
elig_svc.svc_area_id,
elig_svc.criteria_score,
elig_svc.criteria_weight
from ben_elig_svc_area_prte_f elig_svc
where eligy_prfl_id = p_eligy_prfl_id
and p_effective_date between effective_start_date
and effective_end_date;
select zip.from_value, zip.to_value
from ben_pstl_zip_rng_f zip
where zip.pstl_zip_rng_id in (
select pstl_zip_rng_id
from ben_svc_area_pstl_zip_rng_f rng
where rng.SVC_AREA_ID = p_svc_area_id
and p_effective_date between rng.effective_start_date
and rng.effective_end_date)
and length(p_zip_code) >= length(zip.from_value)
and (substr( nvl(p_zip_code,'-1'),1,length(zip.from_value))
between zip.from_value and nvl(zip.to_value,p_zip_code)
or nvl(p_zip_code,'-1') = zip.from_value
or nvl(p_zip_code,'-1') = zip.to_value)
and p_effective_date between zip.effective_start_date
and zip.effective_end_date;
select elig_zip.elig_pstl_cd_r_rng_prte_id,
elig_zip.excld_flag,
elig_zip.pstl_zip_rng_id,
elig_zip.criteria_score,
elig_zip.criteria_weight
from ben_elig_pstl_cd_r_rng_prte_f elig_zip
where elig_zip.eligy_prfl_id = p_eligy_prfl_id
and p_effective_date between effective_start_date
and effective_end_date;
select zip.from_value, zip.to_value
from ben_pstl_zip_rng_f zip
where zip.pstl_zip_rng_id = p_pstl_zip_rng_id
and length(p_zip_code) >= length(zip.from_value)
and (substr( nvl(p_zip_code,'-1'),1,length(zip.from_value))
between zip.from_value and nvl(zip.to_value,p_zip_code)
or nvl(p_zip_code,'-1') = zip.from_value
or nvl(p_zip_code,'-1') = zip.to_value)
and p_effective_date between zip.effective_start_date
and zip.effective_end_date;
select sum(normal_hours)
from per_all_assignments_f asg
where asg.person_id = p_person_id
and asg.business_group_id = p_business_group_id
and (
(nvl(p_use_all_asnts_elig_flag,'N') = 'N' and asg.assignment_id = p_assignment_id)
or
(nvl(p_use_all_asnts_elig_flag,'N') = 'Y')
)
and l_sched_effective_date between asg.effective_start_date
and asg.effective_end_date
and frequency = p_frequency ;
select null
from per_absence_attendances abs
where abs.person_id = p_person_id
and abs.absence_attendance_type_id = p_absence_attendance_type_id
and nvl(abs.abs_attendance_reason_id,-1) =
nvl(p_abs_attendance_reason_id,nvl(abs.abs_attendance_reason_id,-1))
and p_effective_date
between nvl(abs.date_start,p_effective_date)
and nvl(abs.date_end, p_effective_date)
and abs.business_group_id = p_business_group_id;
select null
from ben_elig_per_f epo,
ben_per_in_ler pil
where epo.person_id = p_person_id
and epo.pl_id = p_pl_id
and p_effective_date
between epo.effective_start_date
and epo.effective_end_date
and epo.business_group_id = p_business_group_id
and pil.per_in_ler_id(+)=epo.per_in_ler_id
and epo.elig_flag = 'Y' /* 8872046 */
-- and pil.business_group_id(+)=epo.business_group_id
and ( pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT') -- found row condition
or pil.per_in_ler_stat_cd is null -- outer join condition
)
;
select null
from ben_prtt_enrt_rslt_f pen
where pen.business_group_id = p_business_group_id
and pen.oipl_id = p_oipl_id
and pen.person_id = p_person_id
and p_date_to_use
between pen.enrt_cvg_strt_dt
and pen.enrt_cvg_thru_dt
and pen.enrt_cvg_thru_dt <= pen.effective_end_date
and pen.prtt_enrt_rslt_stat_cd is null;
select null
from ben_elig_cvrd_dpnt_f pdp,
ben_prtt_enrt_rslt_f pen
where pen.business_group_id = p_business_group_id
and pen.pl_id = p_pl_id
and pdp.dpnt_person_id = p_person_id
and l_date_to_use
between pen.enrt_cvg_strt_dt
and pen.enrt_cvg_thru_dt
and pen.enrt_cvg_thru_dt <= pen.effective_end_date
and pdp.business_group_id = pen.business_group_id
and pdp.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
--
and pen.prtt_enrt_rslt_stat_cd is null
and l_date_to_use
between pdp.cvg_strt_dt
and pdp.cvg_thru_dt
and pdp.effective_end_date = hr_api.g_eot;
select null
from ben_prtt_enrt_rslt_f pen
,ben_plip_f cpp
where pen.business_group_id = p_business_group_id
and pen.pgm_id = cpp.pgm_id
and pen.pl_id = cpp.pl_id
and cpp.plip_id = p_plip_id
and p_date_to_use
between cpp.effective_start_date
and cpp.effective_end_date
and cpp.business_group_id = pen.business_group_id
and pen.person_id = p_person_id
and p_date_to_use
between pen.enrt_cvg_strt_dt
and pen.enrt_cvg_thru_dt
and pen.enrt_cvg_thru_dt <= pen.effective_end_date
and pen.prtt_enrt_rslt_stat_cd is null;
select null
from ben_prtt_enrt_rslt_f pen
,ben_elig_cvrd_dpnt_f pdp
,ben_plip_f cpp
where pen.prtt_enrt_rslt_id = pdp.prtt_enrt_rslt_id
--and pen.prtt_enrt_rslt_stat_cd not in ('BCKDT', 'VOIDD')
and pen.prtt_enrt_rslt_stat_cd is null
and pdp.dpnt_person_id = p_person_id
and pen.pgm_id = cpp.pgm_id
and pen.pl_id = cpp.pl_id
and cpp.plip_id = p_plip_id
and p_date_to_use
between cpp.effective_start_date
and cpp.effective_end_date
and cpp.business_group_id = pen.business_group_id
and p_date_to_use
between pen.enrt_cvg_strt_dt
and pen.enrt_cvg_thru_dt
and pen.business_group_id = p_business_group_id
and pen.enrt_cvg_thru_dt <= pen.effective_end_date
and pen.prtt_enrt_rslt_stat_cd is null
and pdp.business_group_id = pen.business_group_id
and p_date_to_use
between pdp.cvg_strt_dt
and pdp.cvg_thru_dt
and pdp.effective_end_date = hr_api.g_eot;
select null
from ben_pl_f pln,
ben_pl_regn_f prg,
ben_regn_f reg
where pln.pl_id = p_pl_id
and pln.business_group_id = p_business_group_id
and p_date_to_use
between pln.effective_start_date
and pln.effective_end_date
and pln.pl_id = prg.pl_id
and prg.business_group_id = pln.business_group_id
and p_date_to_use
between prg.effective_start_date
and prg.effective_end_date
and prg.regn_id = reg.regn_id
and reg.business_group_id = prg.business_group_id
and p_date_to_use
between reg.effective_start_date
and reg.effective_end_date
and reg.sttry_citn_name = 'COBRA';
select pen.pl_id
from ben_prtt_enrt_rslt_f pen
,ben_elig_cvrd_dpnt_f pdp
where pen.prtt_enrt_rslt_id = pdp.prtt_enrt_rslt_id
--and pen.prtt_enrt_rslt_stat_cd not in ('BCKDT', 'VOIDD')
and pen.prtt_enrt_rslt_stat_cd is null
and pdp.dpnt_person_id = p_person_id
and pen.ptip_id = p_ptip_id
and p_date_to_use
between pen.enrt_cvg_strt_dt
and pen.enrt_cvg_thru_dt
and pen.business_group_id = p_business_group_id
and pen.enrt_cvg_thru_dt <= pen.effective_end_date
and pen.prtt_enrt_rslt_stat_cd is null
and pdp.business_group_id = pen.business_group_id
and p_date_to_use
between pdp.cvg_strt_dt
and pdp.cvg_thru_dt
and pdp.effective_end_date = hr_api.g_eot;
select null
from ben_pl_regn_f prg
,ben_regn_f reg
where prg.pl_id = p_pl_id
and prg.regn_id = reg.regn_id
and reg.sttry_citn_name = 'COBRA'
and prg.business_group_id = p_business_group_id
and p_date_to_use
between prg.effective_start_date
and prg.effective_end_date
and prg.business_group_id = reg.business_group_id
and p_date_to_use
between reg.effective_start_date
and reg.effective_end_date;
select null
from ben_elig_cvrd_dpnt_f pdp,
ben_prtt_enrt_rslt_f pen
where pen.business_group_id = p_business_group_id
and pen.pgm_id = p_pgm_id
and pdp.dpnt_person_id = p_person_id
and l_date_to_use -- 5550851
between pen.enrt_cvg_strt_dt
and pen.enrt_cvg_thru_dt
and pen.enrt_cvg_thru_dt <= pen.effective_end_date
and pdp.business_group_id = pen.business_group_id
and pdp.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
--and pen.prtt_enrt_rslt_stat_cd not in ('BCKDT', 'VOIDD')
and pen.prtt_enrt_rslt_stat_cd is null
and l_date_to_use -- 5550851
between pdp.cvg_strt_dt
and pdp.cvg_thru_dt
and pdp.effective_end_date = hr_api.g_eot;
select cqb.quald_bnf_flag
,cqb.cbr_elig_perd_strt_dt
from ben_cbr_quald_bnf cqb
,ben_cbr_per_in_ler crp
,ben_per_in_ler pil
where cqb.quald_bnf_person_id = p_person_id
-- lamc added these next 2 lines
and cqb.pgm_id = nvl(p_pgm_id,cqb.pgm_id)
and nvl(cqb.ptip_id,-1) = nvl(p_ptip_id, -1)
--
and nvl(p_lf_evt_ocrd_dt,p_effective_date)
between cqb.cbr_elig_perd_strt_dt
and cqb.cbr_elig_perd_end_dt
and cqb.business_group_id = p_business_group_id
and cqb.cbr_quald_bnf_id = crp.cbr_quald_bnf_id
and crp.per_in_ler_id = pil.per_in_ler_id
and crp.business_group_id = cqb.business_group_id
-- and pil.business_group_id = crp.business_group_id
and crp.init_evt_flag = 'Y'
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
order by cbr_elig_perd_strt_dt desc; -- 8463981
l_inst_set.delete;
l_inst_set.delete;
select sum(nvl(pen.bnft_amt,0))
from ben_prtt_enrt_rslt_f pen
where pen.business_group_id = cp_business_group_id
and nvl(cp_lf_evt_ocrd_dt,cp_effective_date)
between pen.enrt_cvg_strt_dt and pen.enrt_cvg_thru_dt
and pen.sspndd_flag = 'N'
and pen.prtt_enrt_rslt_stat_cd is null
and pen.effective_end_date = l_eot;
select null
from ben_prtt_enrt_rslt_f pen
where pen.person_id = cp_person_id
and pen.business_group_id = cp_business_group_id
and nvl(cp_lf_evt_ocrd_dt,cp_effective_date)
between pen.enrt_cvg_strt_dt and pen.enrt_cvg_thru_dt
and pen.sspndd_flag = 'N'
and pen.prtt_enrt_rslt_stat_cd is null
and pen.effective_end_date = l_eot;
select count(pen.prtt_enrt_rslt_id)
from ben_prtt_enrt_rslt_f pen
where pen.business_group_id = cp_business_group_id
and nvl(cp_lf_evt_ocrd_dt,cp_effective_date)
between pen.enrt_cvg_strt_dt and pen.enrt_cvg_thru_dt
and pen.sspndd_flag = 'N'
and pen.prtt_enrt_rslt_stat_cd is null
and pen.effective_end_date = l_eot;
select null
from ben_prtt_enrt_rslt_f pen
where pen.person_id = cp_person_id
and pen.business_group_id = cp_business_group_id
and nvl(cp_lf_evt_ocrd_dt,cp_effective_date)
between pen.enrt_cvg_strt_dt and pen.enrt_cvg_thru_dt
and pen.sspndd_flag = 'N'
and pen.prtt_enrt_rslt_stat_cd is null
and pen.effective_end_date = l_eot;
select null
from ben_prtt_enrt_rslt_f pen
where pen.business_group_id = cp_business_group_id
and pen.person_id = cp_person_id
and pen.pl_id = cp_pl_id
and nvl(cp_lf_evt_ocrd_dt,cp_effective_date)
between pen.enrt_cvg_strt_dt and pen.enrt_cvg_thru_dt
and pen.sspndd_flag = 'N'
and pen.prtt_enrt_rslt_stat_cd is null
and pen.effective_end_date = l_eot;
select null
from ben_prtt_enrt_rslt_f pen
, ben_pl_typ_opt_typ_f pto
where pto.pl_typ_opt_typ_id = cp_pl_typ_opt_typ_id
and pto.business_group_id = cp_business_group_id
and nvl(cp_lf_evt_ocrd_dt,cp_effective_date)
between pto.effective_start_date and pto.effective_end_date
and pen.person_id = cp_person_id
and pen.pl_typ_id = pto.pl_typ_id
and pen.oipl_id = cp_oipl_id
and pen.business_group_id = cp_business_group_id
and nvl(cp_lf_evt_ocrd_dt,cp_effective_date)
between pen.enrt_cvg_strt_dt and pen.enrt_cvg_thru_dt
and pen.sspndd_flag = 'N'
and pen.prtt_enrt_rslt_stat_cd is null
and pen.effective_end_date = l_eot;
select null
from per_competence_elements cmp
where cmp.person_id = cp_person_id
and cmp.type = 'PERSONAL'
and cmp.competence_id = cp_competence_id
and cmp.proficiency_level_id = cp_rating_level_id
and cmp.business_group_id = cp_business_group_id
and cp_effective_date
between nvl(cmp.effective_date_from, cp_effective_date)
and nvl(cmp.effective_date_to, cp_effective_date) ;
SELECT ppr.performance_rating
FROM per_performance_reviews ppr,
per_events pev,
per_all_assignments_f asg
WHERE pev.assignment_id = asg.assignment_id
AND pev.TYPE = p_event_type
AND pev.business_group_id = p_business_group_id
AND p_effective_date BETWEEN NVL (pev.date_start,
p_effective_date)
AND NVL (pev.date_end, p_effective_date)
AND ppr.event_id = pev.event_id
-- AND ppr.performance_rating = p_performance_rating
AND p_effective_date BETWEEN NVL (asg.effective_start_date,
p_effective_date
)
AND NVL (asg.effective_end_date,
p_effective_date
)
AND asg.business_group_id = p_business_group_id
AND asg.primary_flag = 'Y'
AND asg.person_id = ppr.person_id
AND ppr.person_id = p_person_id
ORDER BY pev.date_start desc, ppr.review_date desc;
* If ELPRO criteria does not specify Performance Type then we would select
* only those performance reviews which do have Performance (Interview) Type
* as NULL i.e PPR.EVENT_ID IS NULL
*/
CURSOR c2_without_events (
p_person_id NUMBER,
p_effective_date DATE
)
IS
SELECT ppr.performance_rating
FROM per_performance_reviews ppr
WHERE ppr.person_id = p_person_id
AND ppr.review_date <= p_effective_date
AND ppr.event_id IS NULL
ORDER BY ppr.review_date desc;
select (maximum * grade_annualization_factor) maximum ,
(minimum * grade_annualization_factor) minimum
from pay_grade_rules_f pgr,
per_pay_bases ppb -- 2594204
where ppb.pay_basis_id = p_pay_basis_id
and ppb.business_group_id = p_business_group_id
and pgr.rate_id = ppb.rate_id
and pgr.business_group_id = p_business_group_id
and pgr.grade_or_spinal_point_id = p_grade_id
and p_lf_evt_ocrd_dt between nvl(pgr.effective_start_date, p_lf_evt_ocrd_dt)
and nvl(pgr.effective_end_date, p_lf_evt_ocrd_dt);
select ppp.proposed_salary_n * ppb.pay_annualization_factor annual_salary
from per_pay_bases ppb,
per_pay_proposals ppp
where ppb.pay_basis_id = p_pay_basis_id
and ppb.business_group_id = p_business_group_id
and ppp.assignment_id = p_assignment_id
and ppp.change_date <= p_lf_evt_ocrd_dt
order by ppp.change_date desc ;
select ppp.proposed_salary_n
from per_pay_proposals ppp
where ppp.assignment_id = p_assignment_id
and ppp.business_group_id = p_business_group_id
and ppp.approved = 'Y'
and ppp.change_date <= p_lf_evt_ocrd_dt
order by ppp.change_date desc;
select ppb.pay_annualization_factor
from per_pay_bases ppb
where ppb.pay_basis_id = p_pay_basis_id
and ppb.business_group_id = ppb.business_group_id;
select null,
p_pgm_id,
p_ptip_id,
p_plip_id,
p_pl_id,
p_oipl_id,
null,
null, -- prtn_elig_id
null, -- mndtry_flag
'N',
'N',
tab3.eligy_prfl_id,
tab3.asmt_to_use_cd,
tab3.elig_enrld_plip_flag,
tab3.elig_cbr_quald_bnf_flag,
tab3.elig_enrld_ptip_flag,
tab3.elig_dpnt_cvrd_plip_flag,
tab3.elig_dpnt_cvrd_ptip_flag,
tab3.elig_dpnt_cvrd_pgm_flag,
tab3.elig_job_flag,
tab3.elig_hrly_slrd_flag,
tab3.elig_pstl_cd_flag,
tab3.elig_lbr_mmbr_flag,
tab3.elig_lgl_enty_flag,
tab3.elig_benfts_grp_flag,
tab3.elig_wk_loc_flag,
tab3.elig_brgng_unit_flag,
tab3.elig_age_flag,
tab3.elig_los_flag,
tab3.elig_per_typ_flag,
tab3.elig_fl_tm_pt_tm_flag,
tab3.elig_ee_stat_flag,
tab3.elig_grd_flag,
tab3.elig_pct_fl_tm_flag,
tab3.elig_asnt_set_flag,
tab3.elig_hrs_wkd_flag,
tab3.elig_comp_lvl_flag,
tab3.elig_org_unit_flag,
tab3.elig_loa_rsn_flag,
tab3.elig_pyrl_flag,
tab3.elig_schedd_hrs_flag,
tab3.elig_py_bss_flag,
tab3.eligy_prfl_rl_flag,
tab3.elig_cmbn_age_los_flag,
tab3.cntng_prtn_elig_prfl_flag,
tab3.elig_prtt_pl_flag,
tab3.elig_ppl_grp_flag,
tab3.elig_svc_area_flag,
tab3.elig_ptip_prte_flag,
tab3.elig_no_othr_cvg_flag,
tab3.elig_enrld_pl_flag,
tab3.elig_enrld_oipl_flag,
tab3.elig_enrld_pgm_flag,
tab3.elig_dpnt_cvrd_pl_flag,
tab3.elig_lvg_rsn_flag,
tab3.elig_optd_mdcr_flag,
tab3.elig_tbco_use_flag,
tab3.elig_dpnt_othr_ptip_flag,
tab3.ELIG_GNDR_FLAG,
tab3.ELIG_MRTL_STS_FLAG,
tab3.ELIG_DSBLTY_CTG_FLAG,
tab3.ELIG_DSBLTY_RSN_FLAG,
tab3.ELIG_DSBLTY_DGR_FLAG,
tab3.ELIG_SUPPL_ROLE_FLAG,
tab3.ELIG_QUAL_TITL_FLAG,
tab3.ELIG_PSTN_FLAG,
tab3.ELIG_PRBTN_PERD_FLAG,
tab3.ELIG_SP_CLNG_PRG_PT_FLAG,
tab3.BNFT_CAGR_PRTN_CD,
tab3.ELIG_DSBLD_FLAG,
tab3.ELIG_TTL_CVG_VOL_FLAG,
tab3.ELIG_TTL_PRTT_FLAG,
tab3.ELIG_COMPTNCY_FLAG,
tab3.ELIG_HLTH_CVG_FLAG,
tab3.ELIG_ANTHR_PL_FLAG,
tab3.elig_qua_in_gr_flag,
tab3.elig_perf_rtng_flag,
tab3.elig_crit_values_flag
from ben_eligy_prfl_f tab3
where tab3.eligy_prfl_id = p_eligy_prfl_id
and tab3.stat_cd = 'A'
and c_effective_date between tab3.effective_start_date
and tab3.effective_end_date;
select pl_typ_id
from ben_ptip_f
where ptip_id = p_ptip_id
and c_effective_date
between effective_start_date
and effective_end_date;
select pln.pl_typ_id
from ben_plip_f plip,
ben_pl_f pln
where plip_id = p_plip_id
and c_effective_date
between plip.effective_start_date and plip.effective_end_date
and pln.pl_id = plip.pl_id
and c_effective_date
between pln.effective_start_date and pln.effective_end_date ;
select pln.pl_typ_id
from ben_oipl_f oipl,
ben_pl_f pln
where oipl_id = p_oipl_id
and c_effective_date
between oipl.effective_start_date and oipl.effective_end_date
and pln.pl_id = oipl.pl_id
and c_effective_date
between pln.effective_start_date and pln.effective_end_date ;
select 'Y'
from ff_fdi_usages_f
where FORMULA_ID = cv_formula_id
and ITEM_NAME = 'ASSIGNMENT_ID'
and usage = 'U';
select 'x'
from ben_eligy_criteria
where short_code = p_short_code;
l_tmpelp_dets.delete;
l_eligprof_dets.delete;
l_profile_score_tab.delete;
l_appass_rec.delete;
l_appass_rec.delete;
l_appass_rec.delete;
l_appass_rec.delete;
l_profile_score_tab.delete;
p_score_tab.delete;
l_profile_score_tab.delete;
p_score_tab.delete;