[Home] [Help]
83: --
84: -- Added p_qualifying_event to fix bug#4599753
85: cursor c1 is
86: select months_between(a.coverage_end_date,a.coverage_start_date)
87: from per_cobra_cov_enrollments a
88: where a.assignment_id = p_assignment_id
89: and a.cobra_coverage_enrollment_id <>
90: nvl(p_cobra_coverage_enrollment_id,-1)
91: and a.qualifying_event = p_qualifying_event;
715: event twice with same qualifying date, coverage periods.
716: This might be needed for twins scenario when they turn to
717: 18 and they no longer need the medical coverage. Bug#4599753
718:
719: per_cobra_cov_enrollments_pkg.hr_cobra_chk_unique_enrollment (
720: p_cobra_coverage_enrollment_id,
721: p_assignment_id,
722: p_contact_relationship_id,
723: p_qualifying_event,
752: --
753: --
754: hr_utility.set_location(l_package,4);
755: --
756: per_cobra_cov_enrollments_pkg.insert_row(
757: p_rowid,
758: p_cobra_coverage_enrollment_id,
759: p_business_group_id,
760: p_assignment_id,
1364: --
1365: /* We don't need this validation when we update the record
1366: part of fix for bug#4599753
1367:
1368: per_cobra_cov_enrollments_pkg.hr_cobra_chk_unique_enrollment (
1369: p_cobra_coverage_enrollment_id,
1370: p_assignment_id,
1371: p_contact_relationship_id,
1372: p_qualifying_event,
1376: hr_utility.set_location(l_package, 20);
1377: --
1378: -- do update
1379: --
1380: per_cobra_cov_enrollments_pkg.update_row(
1381: p_rowid,
1382: p_business_group_id,
1383: p_assignment_id,
1384: p_period_type,
1473: p_amount_charged,
1474: p_grace_days );
1475: END IF;
1476: -- Modified the validation to avoid the duplicate record error while updating
1477: -- cobra enrollment record in per_cobra_cov_enrollments table. Fix for bug#4599753
1478: ELSIF ( p_old_first_payment_due_date is NULL and p_first_payment_due_date IS NOT NULL )
1479: THEN
1480: hr_cobra_ins_schedule( p_business_group_id,
1481: p_cobra_coverage_enrollment_id,
2210:
2211: ld_session_date := p_session_date;
2212: hr_utility.trace('p_status TERM satisfied');
2213:
2214: update per_cobra_cov_enrollments
2215: set coverage_end_date = p_session_date
2216: where cobra_coverage_enrollment_id = p_cobra_coverage_enrollment_id
2217: and coverage_start_date = p_coverage_start_date
2218: and coverage_end_date = p_coverage_end_date
2217: and coverage_start_date = p_coverage_start_date
2218: and coverage_end_date = p_coverage_end_date
2219: and qualifying_event = p_qualifying_event;
2220:
2221: hr_utility.trace('Update to per_cobra_cov_enrollments done ');
2222:
2223: p_coverage_end_date := ld_session_date;
2224: --
2225:
2409: /*
2410: if p_status = 'TERM' then
2411:
2412: hr_utility.trace('p_status TERM satisfied');
2413: update per_cobra_cov_enrollments
2414: set coverage_end_date = p_session_date
2415: where cobra_coverage_enrollment_id = p_cobra_coverage_enrollment_id
2416: and p_session_date between coverage_start_date and coverage_end_date;
2417:
2414: set coverage_end_date = p_session_date
2415: where cobra_coverage_enrollment_id = p_cobra_coverage_enrollment_id
2416: and p_session_date between coverage_start_date and coverage_end_date;
2417:
2418: hr_utility.trace('Update to per_cobra_cov_enrollments done ');
2419: end if;
2420: */
2421: --
2422: --
3254: -- be between the event coverage start and end dates.
3255: --
3256: cursor c1 is
3257: select null
3258: from per_cobra_cov_enrollments cov
3259: where cov.cobra_coverage_enrollment_id = p_cobra_coverage_enrollment_id
3260: and p_effective_start_date
3261: between cov.coverage_start_date
3262: and cov.coverage_end_date
3855: -- Ensure that two cobra events do not occur in the same timeframe
3856: -- Added qualifying_event condition to fix bug#4599753
3857: cursor c1 is
3858: select null
3859: from per_cobra_cov_enrollments a
3860: where a.cobra_coverage_enrollment_id <> nvl(p_cobra_coverage_enrollment_id,-1)
3861: and a.assignment_id = p_assignment_id
3862: and (p_coverage_start_date
3863: between a.coverage_start_date