[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select 1,decode(p_level,'PRTT',cop.pcp_dsgn_cd,'DPNT',cop.pcp_dpnt_dsgn_cd)
from ben_oipl_f cop
where oipl_id in (select pen.oipl_id
from ben_prtt_enrt_rslt_f pen
where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and prtt_enrt_rslt_stat_cd is null)
and p_effective_date between cop.effective_Start_date and cop.effective_end_date
UNION
select 2,decode(p_level,'PRTT',cop.pcp_dsgn_cd,'DPNT',cop.pcp_dpnt_dsgn_cd)
from ben_pl_pcp cop
where pl_id in ( select pen.pl_id
from ben_prtt_enrt_rslt_f pen
where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and prtt_enrt_rslt_stat_cd is null)
order by 1;
select r2.effective_start_date, r2.effective_end_date
from ben_prtt_enrt_rslt_f r,
ben_prtt_enrt_rslt_f r2
where r.prtt_enrt_rslt_id = p_old_prtt_enrt_rslt_id
and r2.prtt_enrt_rslt_id = p_new_prtt_enrt_rslt_id
and r.pl_id = r2.pl_id
and r.business_group_id = p_business_group_id
and p_effective_date between r.effective_start_date
and r.effective_end_date
and r2.business_group_id = p_business_group_id
and p_effective_date between r2.effective_start_date
and r2.effective_end_date;
select NAME,
EXT_IDENT,
PRMRY_CARE_PRVDR_TYP_CD,
PRTT_ENRT_RSLT_ID,
ELIG_CVRD_DPNT_ID,
BUSINESS_GROUP_ID,
PPR_ATTRIBUTE_CATEGORY,
PPR_ATTRIBUTE1,
PPR_ATTRIBUTE2,
PPR_ATTRIBUTE3,
PPR_ATTRIBUTE4,
PPR_ATTRIBUTE5,
PPR_ATTRIBUTE6,
PPR_ATTRIBUTE7,
PPR_ATTRIBUTE8,
PPR_ATTRIBUTE9,
PPR_ATTRIBUTE10,
PPR_ATTRIBUTE11,
PPR_ATTRIBUTE12,
PPR_ATTRIBUTE13,
PPR_ATTRIBUTE14,
PPR_ATTRIBUTE15,
PPR_ATTRIBUTE16,
PPR_ATTRIBUTE17,
PPR_ATTRIBUTE18,
PPR_ATTRIBUTE19,
PPR_ATTRIBUTE20,
PPR_ATTRIBUTE21,
PPR_ATTRIBUTE22,
PPR_ATTRIBUTE23,
PPR_ATTRIBUTE24,
PPR_ATTRIBUTE25,
PPR_ATTRIBUTE26,
PPR_ATTRIBUTE27,
PPR_ATTRIBUTE28,
PPR_ATTRIBUTE29,
PPR_ATTRIBUTE30
from ben_prmry_care_prvdr_f pcp
where pcp.prtt_enrt_rslt_id = p_old_prtt_enrt_rslt_id
and pcp.elig_cvrd_dpnt_id is null
and pcp.business_group_id = p_business_group_id
and p_effective_date between pcp.effective_start_date
and pcp.effective_end_date
;
select pcp.NAME,
pcp.EXT_IDENT,
pcp.PRMRY_CARE_PRVDR_TYP_CD,
pcp.PRTT_ENRT_RSLT_ID,
pcp.ELIG_CVRD_DPNT_ID old_elig_cvrd_dpnt_id,
pcp.BUSINESS_GROUP_ID,
pcp.PPR_ATTRIBUTE_CATEGORY,
pcp.PPR_ATTRIBUTE1,
pcp.PPR_ATTRIBUTE2,
pcp.PPR_ATTRIBUTE3,
pcp.PPR_ATTRIBUTE4,
pcp.PPR_ATTRIBUTE5,
pcp.PPR_ATTRIBUTE6,
pcp.PPR_ATTRIBUTE7,
pcp.PPR_ATTRIBUTE8,
pcp.PPR_ATTRIBUTE9,
pcp.PPR_ATTRIBUTE10,
pcp.PPR_ATTRIBUTE11,
pcp.PPR_ATTRIBUTE12,
pcp.PPR_ATTRIBUTE13,
pcp.PPR_ATTRIBUTE14,
pcp.PPR_ATTRIBUTE15,
pcp.PPR_ATTRIBUTE16,
pcp.PPR_ATTRIBUTE17,
pcp.PPR_ATTRIBUTE18,
pcp.PPR_ATTRIBUTE19,
pcp.PPR_ATTRIBUTE20,
pcp.PPR_ATTRIBUTE21,
pcp.PPR_ATTRIBUTE22,
pcp.PPR_ATTRIBUTE23,
pcp.PPR_ATTRIBUTE24,
pcp.PPR_ATTRIBUTE25,
pcp.PPR_ATTRIBUTE26,
pcp.PPR_ATTRIBUTE27,
pcp.PPR_ATTRIBUTE28,
pcp.PPR_ATTRIBUTE29,
pcp.PPR_ATTRIBUTE30,
d2.elig_cvrd_dpnt_id elig_cvrd_dpnt_id,
d2.effective_start_date,
d2.effective_end_date
from ben_prtt_enrt_rslt_f r,
ben_prtt_enrt_rslt_f r2,
ben_elig_cvrd_dpnt_f d,
ben_elig_cvrd_dpnt_f d2,
ben_prmry_care_prvdr_f pcp,
ben_per_in_ler pil,
ben_per_in_ler pil2
where r.prtt_enrt_rslt_id = p_old_prtt_enrt_rslt_id
and r2.prtt_enrt_rslt_id = p_new_prtt_enrt_rslt_id
and d.prtt_enrt_rslt_id = r.prtt_enrt_rslt_id
and d2.prtt_enrt_rslt_id = r2.prtt_enrt_rslt_id
and d.dpnt_person_id = d2.dpnt_person_id
and d.elig_cvrd_dpnt_id = pcp.elig_cvrd_dpnt_id
and pcp.prtt_enrt_rslt_id is null
and r.business_group_id = p_business_group_id
and pcp.business_group_id = p_business_group_id
and p_effective_date between r.effective_start_date
and r.effective_end_date
and p_effective_date between r2.effective_start_date
and r2.effective_end_date
and p_effective_date between d.effective_start_date
and d.effective_end_date
and p_effective_date between d2.effective_start_date
and d2.effective_end_date
and p_effective_date between pcp.effective_start_date
and pcp.effective_end_date
and pil.per_in_ler_id=d.per_in_ler_id
and pil.business_group_id=p_business_group_id
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
and pil2.per_in_ler_id=d2.per_in_ler_id
and pil2.business_group_id=p_business_group_id
and pil2.per_in_ler_stat_cd not in ('VOIDD','BCKDT');
,p_program_update_date => sysdate
,p_object_version_number => l_object_version_number
,p_effective_date => p_effective_date
);
,p_program_update_date => sysdate
,p_object_version_number => l_object_version_number
,p_effective_date => p_effective_date
);