38: cursor c_pcp is
39: select 1,decode(p_level,'PRTT',cop.pcp_dsgn_cd,'DPNT',cop.pcp_dpnt_dsgn_cd)
40: from ben_oipl_f cop
41: where oipl_id in (select pen.oipl_id
42: from ben_prtt_enrt_rslt_f pen
43: where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
44: and prtt_enrt_rslt_stat_cd is null)
45: and p_effective_date between cop.effective_Start_date and cop.effective_end_date
46: UNION
46: UNION
47: select 2,decode(p_level,'PRTT',cop.pcp_dsgn_cd,'DPNT',cop.pcp_dpnt_dsgn_cd)
48: from ben_pl_pcp cop
49: where pl_id in ( select pen.pl_id
50: from ben_prtt_enrt_rslt_f pen
51: where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
52: and prtt_enrt_rslt_stat_cd is null)
53: order by 1;
54:
129:
130: -- check that results have same plan id
131: cursor chk_rslt_c is
132: select r2.effective_start_date, r2.effective_end_date
133: from ben_prtt_enrt_rslt_f r,
134: ben_prtt_enrt_rslt_f r2
135: where r.prtt_enrt_rslt_id = p_old_prtt_enrt_rslt_id
136: and r2.prtt_enrt_rslt_id = p_new_prtt_enrt_rslt_id
137: and r.pl_id = r2.pl_id
130: -- check that results have same plan id
131: cursor chk_rslt_c is
132: select r2.effective_start_date, r2.effective_end_date
133: from ben_prtt_enrt_rslt_f r,
134: ben_prtt_enrt_rslt_f r2
135: where r.prtt_enrt_rslt_id = p_old_prtt_enrt_rslt_id
136: and r2.prtt_enrt_rslt_id = p_new_prtt_enrt_rslt_id
137: and r.pl_id = r2.pl_id
138: and r.business_group_id = p_business_group_id
232: pcp.PPR_ATTRIBUTE30,
233: d2.elig_cvrd_dpnt_id elig_cvrd_dpnt_id,
234: d2.effective_start_date,
235: d2.effective_end_date
236: from ben_prtt_enrt_rslt_f r,
237: ben_prtt_enrt_rslt_f r2,
238: ben_elig_cvrd_dpnt_f d,
239: ben_elig_cvrd_dpnt_f d2,
240: ben_prmry_care_prvdr_f pcp,
233: d2.elig_cvrd_dpnt_id elig_cvrd_dpnt_id,
234: d2.effective_start_date,
235: d2.effective_end_date
236: from ben_prtt_enrt_rslt_f r,
237: ben_prtt_enrt_rslt_f r2,
238: ben_elig_cvrd_dpnt_f d,
239: ben_elig_cvrd_dpnt_f d2,
240: ben_prmry_care_prvdr_f pcp,
241: ben_per_in_ler pil,