APPS.BEN_DETERMINE_DPNT_ELIGIBILITY dependencies on PER_CONTACT_RELATIONSHIPS

Line 484: from per_contact_relationships ctr,

480: ctr.contact_relationship_id,
481: ctr.contact_type,
482: ctr.date_end, -- 4697057 Added this
483: 'Y' contact_active_flag
484: from per_contact_relationships ctr,
485: per_all_people_f per
486: where per.person_id = ctr.contact_person_id
487: and l_lf_evt_ocrd_dt <= nvl(per.DATE_OF_DEATH, l_lf_evt_ocrd_dt) /* Bug 4287999 */
488: and l_lf_evt_ocrd_dt between per.effective_start_date and per.effective_end_date

Line 492: from per_contact_relationships ctr2

488: and l_lf_evt_ocrd_dt between per.effective_start_date and per.effective_end_date
489: and ctr.personal_flag = 'Y'
490: and ctr.contact_relationship_id =
491: ( select min(contact_relationship_id)
492: from per_contact_relationships ctr2
493: where ctr2.contact_person_id = ctr.contact_person_id
494: and ctr2.person_id = p_person_id /* Bug 4692782 */
495: and ctr2.personal_flag = 'Y'
496: and l_lf_evt_ocrd_dt between

Line 516: from per_contact_relationships ctr,

512: ctr.contact_relationship_id,
513: ctr.contact_type,
514: ctr.date_end, -- 4697057 Added this
515: 'N' contact_active_flag
516: from per_contact_relationships ctr,
517: per_all_people_f per,
518: ben_elig_cvrd_dpnt_f pdp,
519: ben_per_in_ler pil
520: where per.person_id = ctr.contact_person_id

Line 537: from per_contact_relationships ctr1,

533: and pil.per_in_ler_id=pdp.per_in_ler_id
534: and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
535: and not exists (
536: select null
537: from per_contact_relationships ctr1,
538: per_all_people_f per1
539: where ctr1.contact_person_id=ctr.contact_person_id
540: and per1.person_id = ctr1.contact_person_id
541: and l_lf_evt_ocrd_dt between per1.effective_start_date and per1.effective_end_date