APPS.PER_COBRA_ECI dependencies on PER_COBRA_COVERAGE_STATUSES

Line 521: FROM per_cobra_coverage_statuses ccs

517: -- declare cursor
518: --
519: CURSOR elect_exists IS
520: SELECT 'Y'
521: FROM per_cobra_coverage_statuses ccs
522: WHERE ccs.cobra_coverage_enrollment_id = p_cobra_coverage_enrollment_id
523: AND ccs.cobra_coverage_status_type = 'ELEC';
524: --
525: BEGIN

Line 1867: per_cobra_coverage_statuses ccs

1863: SELECT ccs.cobra_coverage_status_type,
1864: ccs.effective_date,
1865: h.meaning
1866: FROM hr_lookups h,
1867: per_cobra_coverage_statuses ccs
1868: WHERE ccs.cobra_coverage_enrollment_id = p_cobra_coverage_enrollment_id
1869: AND ccs.cobra_coverage_status_id =
1870: (SELECT MAX(ccs1.cobra_coverage_status_id)
1871: FROM per_cobra_coverage_statuses ccs1

Line 1871: FROM per_cobra_coverage_statuses ccs1

1867: per_cobra_coverage_statuses ccs
1868: WHERE ccs.cobra_coverage_enrollment_id = p_cobra_coverage_enrollment_id
1869: AND ccs.cobra_coverage_status_id =
1870: (SELECT MAX(ccs1.cobra_coverage_status_id)
1871: FROM per_cobra_coverage_statuses ccs1
1872: WHERE ccs1.cobra_coverage_enrollment_id = ccs.cobra_coverage_enrollment_id
1873: AND ccs1.effective_date =
1874: (SELECT MAX(ccs2.effective_date)
1875: FROM per_cobra_coverage_statuses ccs2

Line 1875: FROM per_cobra_coverage_statuses ccs2

1871: FROM per_cobra_coverage_statuses ccs1
1872: WHERE ccs1.cobra_coverage_enrollment_id = ccs.cobra_coverage_enrollment_id
1873: AND ccs1.effective_date =
1874: (SELECT MAX(ccs2.effective_date)
1875: FROM per_cobra_coverage_statuses ccs2
1876: WHERE ccs2.effective_date <= p_session_date
1877: AND ccs2.cobra_coverage_enrollment_id = ccs1.cobra_coverage_enrollment_id))
1878: AND ccs.cobra_coverage_status_type = h.lookup_code
1879: AND h.lookup_type = 'US_COBRA_STATUS';