[Home] [Help]
2691: -- l_person_id number(15);
2692: -- l_pl_id number(15);
2693: -- l_oipl_id number(15);
2694: -- l_opt_id number(15);
2695: l_contact_type per_contact_relationships.contact_type%type; -- varchar2(30);
2696: l_t_mx_dpnts_alwd_num number(15);
2697: l_t_no_mx_num_dfnd_flag varchar2(1);
2698: l_r_mx_dpnts_alwd_num number(15);
2699: l_r_no_mx_num_dfnd_flag varchar2(1);
2746:
2747: cursor info2_c(cv_person_id number,
2748: cv_dpnt_person_id number) is
2749: select c.contact_type
2750: from per_contact_relationships c
2751: where c.person_id = cv_person_id
2752: and c.contact_person_id = cv_dpnt_person_id
2753: -- Bug 1762932 added the personal_flag condition
2754: and nvl(c.personal_flag, 'N') = 'Y'
2818: and p_effective_date between nvl(r.effective_start_date, p_effective_date)
2819: and nvl(r.effective_end_date, p_effective_date)
2820: and dr.dsgn_rqmt_id = r.dsgn_rqmt_id
2821: and dr.rlshp_typ_cd in (select c.contact_type
2822: from per_contact_relationships c
2823: where c.person_id = cv_person_id
2824: and c.contact_person_id = cv_dpnt_person_id
2825: and nvl(c.personal_flag, 'N') = 'Y'
2826: and c.business_group_id = p_business_group_id
2855: cursor rlshp_num_dpnt_c(cv_person_id number,
2856: cv_cvg_strt_dt date,
2857: cv_cvg_thru_dt date) is
2858: select count(*)
2859: from per_contact_relationships c
2860: , ben_elig_cvrd_dpnt_f d
2861: , ben_per_in_ler pil
2862: where
2863: c.person_id = cv_person_id