480: select coverage.meaning,
481: benefit.employee_contribution,
482: benefit.employer_contribution
483: --
484: from ben_benefit_contributions_f BENEFIT,
485: hr_lookups COVERAGE
486: --
487: where coverage.lookup_type = 'US_BENEFIT_COVERAGE'
488: and coverage.lookup_code = benefit.coverage_type
489: and benefit.element_type_id = p_element_type_id
490: and p_effective_date between benefit.effective_start_date
491: and benefit.effective_end_date
492: and 1 = (select count (*)
493: from ben_benefit_contributions_f BENEFIT2
494: where benefit2.element_type_id = p_element_type_id
495: and p_effective_date between benefit2.effective_start_date
496: and benefit2.effective_end_date)
497: ;