APPS.HRWSECI dependencies on PER_COBRA_COVERAGE_STATUSES

Line 478: FROM per_cobra_coverage_statuses ccs

474: -- declare cursor
475: --
476: CURSOR elect_exists IS
477: SELECT 'Y'
478: FROM per_cobra_coverage_statuses ccs
479: WHERE ccs.cobra_coverage_enrollment_id = p_cobra_coverage_enrollment_id
480: AND ccs.cobra_coverage_status_type = 'ELEC';
481: --
482: BEGIN

Line 1382: per_cobra_coverage_statuses ccs

1378: SELECT ccs.cobra_coverage_status_type,
1379: ccs.effective_date,
1380: h.meaning
1381: FROM hr_lookups h,
1382: per_cobra_coverage_statuses ccs
1383: WHERE ccs.cobra_coverage_enrollment_id = p_cobra_coverage_enrollment_id
1384: AND ccs.cobra_coverage_status_id =
1385: (SELECT MAX(ccs1.cobra_coverage_status_id)
1386: FROM per_cobra_coverage_statuses ccs1

Line 1386: FROM per_cobra_coverage_statuses ccs1

1382: per_cobra_coverage_statuses ccs
1383: WHERE ccs.cobra_coverage_enrollment_id = p_cobra_coverage_enrollment_id
1384: AND ccs.cobra_coverage_status_id =
1385: (SELECT MAX(ccs1.cobra_coverage_status_id)
1386: FROM per_cobra_coverage_statuses ccs1
1387: WHERE ccs1.cobra_coverage_enrollment_id = ccs.cobra_coverage_enrollment_id
1388: AND ccs1.effective_date =
1389: (SELECT MAX(ccs2.effective_date)
1390: FROM per_cobra_coverage_statuses ccs2

Line 1390: FROM per_cobra_coverage_statuses ccs2

1386: FROM per_cobra_coverage_statuses ccs1
1387: WHERE ccs1.cobra_coverage_enrollment_id = ccs.cobra_coverage_enrollment_id
1388: AND ccs1.effective_date =
1389: (SELECT MAX(ccs2.effective_date)
1390: FROM per_cobra_coverage_statuses ccs2
1391: WHERE ccs2.effective_date <= p_session_date
1392: AND ccs2.cobra_coverage_enrollment_id = ccs1.cobra_coverage_enrollment_id))
1393: AND ccs.cobra_coverage_status_type = h.lookup_code
1394: AND h.lookup_type = 'US_COBRA_STATUS';