APPS.PER_COBRA_ECI dependencies on PER_COBRA_COVERAGE_BENEFITS_F

Line 909: INSERT INTO per_cobra_coverage_benefits_f (

905: --
906: --
907: -- insert benefits
908: --
909: INSERT INTO per_cobra_coverage_benefits_f (
910: cobra_coverage_benefit_id,
911: cobra_coverage_enrollment_id,
912: effective_start_date,
913: effective_end_date,

Line 1051: FROM per_cobra_coverage_benefits_f ccb

1047: -- Added p_session_date to get correct amount_charged value when there are more
1048: -- than one record for same cobra_coverage_enrollment_id. Part of fix#4599753
1049: CURSOR amount_charged IS
1050: SELECT fnd_number.number_to_canonical(SUM(fnd_number.canonical_to_number(ccb.coverage_amount)))
1051: FROM per_cobra_coverage_benefits_f ccb
1052: WHERE ccb.cobra_coverage_enrollment_id = p_cobra_coverage_enrollment_id
1053: and p_session_date between ccb.effective_start_date and ccb.effective_end_date
1054: AND ccb.accept_reject_flag = 'ACC';
1055: --

Line 1222: FROM per_cobra_coverage_benefits_f ccb

1218: l_user_id,
1219: l_login_id,
1220: l_user_id,
1221: TRUNC(sysdate)
1222: FROM per_cobra_coverage_benefits_f ccb
1223: WHERE ccb.cobra_coverage_enrollment_id = p_cobra_coverage_enrollment_id
1224: AND ADD_MONTHS(p_first_payment_due_date, l_count) BETWEEN
1225: ccb.effective_start_date AND ccb.effective_end_date
1226: AND ccb.accept_reject_flag = 'ACC';

Line 1559: from per_cobra_coverage_benefits_f ccb

1555: ,ee.effective_end_date
1556: ,ccb.element_type_id
1557: ,ccb.cobra_coverage_benefit_id
1558: ,ccb.effective_start_date
1559: from per_cobra_coverage_benefits_f ccb
1560: ,pay_element_entries_f ee
1561: ,pay_element_links_f el
1562: where ccb.cobra_coverage_enrollment_id = p_cobra_coverage_enrollment_id
1563: and p_effective_date between ccb.effective_start_date

Line 1600: (select 1 from per_cobra_coverage_benefits_f ccbf

1596: and et.benefit_classification_id = bc.benefit_classification_id
1597: and bc.legislation_code = 'US'
1598: and bc.cobra_flag = 'Y'
1599: and not exists
1600: (select 1 from per_cobra_coverage_benefits_f ccbf
1601: where ccbf.cobra_coverage_enrollment_id = p_cobra_coverage_enrollment_id
1602: and ccbf.element_type_id = el.element_type_id
1603: and ccbf.effective_start_date >= el.effective_start_date
1604: and ccbf.effective_end_date <= el.effective_end_date

Line 1638: update per_cobra_coverage_benefits_f

1634: if p_effective_date >= l_ee_effective_end_date then
1635:
1636: hr_utility.set_location(l_package, 30);
1637:
1638: update per_cobra_coverage_benefits_f
1639: set effective_end_date = l_ee_effective_end_date
1640: where cobra_coverage_benefit_id = l_cobra_coverage_benefit_id
1641: and effective_start_date = l_ccb_effective_start_date
1642: and effective_end_date = to_date('31-12-4712','DD-MM-YYYY')

Line 1678: INSERT INTO per_cobra_coverage_benefits_f (

1674: close get_benefit_id;
1675:
1676: hr_utility.trace('l_cobra_coverage_benefit_id : '|| l_cobra_coverage_benefit_id);
1677:
1678: INSERT INTO per_cobra_coverage_benefits_f (
1679: cobra_coverage_benefit_id,
1680: cobra_coverage_enrollment_id,
1681: effective_start_date,
1682: effective_end_date,

Line 2876: FROM per_cobra_coverage_benefits_f ccb

2872: -- declare cursor
2873: --
2874: CURSOR rej_to_acc Is
2875: SELECT 'Y'
2876: FROM per_cobra_coverage_benefits_f ccb
2877: WHERE ccb.rowid = p_rowid
2878: AND ccb.accept_reject_flag = 'REJ';
2879: --
2880: BEGIN

Line 3174: FROM per_cobra_coverage_benefits_f ccb

3170: --
3171: UPDATE per_sched_cobra_payments scp
3172: SET amount_due =
3173: ( SELECT fnd_number.number_to_canonical(NVL(SUM(fnd_number.canonical_to_number(coverage_amount)),'0'))
3174: FROM per_cobra_coverage_benefits_f ccb
3175: WHERE ccb.cobra_coverage_enrollment_id = p_cobra_coverage_enrollment_id
3176: AND scp.date_due BETWEEN
3177: ccb.effective_start_date AND ccb.effective_end_date
3178: AND ccb.accept_reject_flag = 'ACC' )