[Home] [Help]
483: ctr.contact_type,
484: ctr.date_end, -- 4697057 Added this
485: 'Y' contact_active_flag,
486: per.date_of_death /* Bug 7481099 */
487: from per_contact_relationships ctr,
488: per_all_people_f per
489: where per.person_id = ctr.contact_person_id
490: -- and l_lf_evt_ocrd_dt <= nvl(per.DATE_OF_DEATH, l_lf_evt_ocrd_dt) /* Bug 4287999 */
491: and l_lf_evt_ocrd_dt between per.effective_start_date and per.effective_end_date
491: and l_lf_evt_ocrd_dt between per.effective_start_date and per.effective_end_date
492: and ctr.personal_flag = 'Y'
493: and ctr.contact_relationship_id =
494: ( select min(contact_relationship_id)
495: from per_contact_relationships ctr2
496: where ctr2.contact_person_id = ctr.contact_person_id
497: and ctr2.person_id = p_person_id /* Bug 4692782 */
498: and ctr2.personal_flag = 'Y'
499: and l_lf_evt_ocrd_dt between
516: ctr.contact_type,
517: ctr.date_end, -- 4697057 Added this
518: 'N' contact_active_flag,
519: per.date_of_death /* Bug 7481099 */
520: from per_contact_relationships ctr,
521: per_all_people_f per,
522: ben_elig_cvrd_dpnt_f pdp,
523: ben_per_in_ler pil
524: where per.person_id = ctr.contact_person_id
537: and pil.per_in_ler_id=pdp.per_in_ler_id
538: and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
539: and not exists (
540: select null
541: from per_contact_relationships ctr1,
542: per_all_people_f per1
543: where ctr1.contact_person_id=ctr.contact_person_id
544: and per1.person_id = ctr1.contact_person_id
545: and l_lf_evt_ocrd_dt between per1.effective_start_date and per1.effective_end_date