[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT PPF.registered_disabled_flag
FROM per_people_f PPF
WHERE PPF.person_id = p_person_id
AND PPF.registered_disabled_flag = 'Y'
AND (p_qualifying_start_date
BETWEEN PPF.effective_start_date
AND PPF.effective_end_date
OR PPF.effective_start_date
BETWEEN p_qualifying_start_date
AND p_qualifying_start_date + 60);
select months_between(a.coverage_end_date,a.coverage_start_date)
from per_cobra_cov_enrollments a
where a.assignment_id = p_assignment_id
and a.cobra_coverage_enrollment_id <>
nvl(p_cobra_coverage_enrollment_id,-1)
and a.qualifying_event = p_qualifying_event;
select event_coverage
from per_cobra_qfying_events_f
where legislation_code = 'US'
and qualifying_event = p_qualifying_event;
SELECT CCP.coverage,
CCP.coverage_uom
FROM per_cobra_coverage_periods CCP
-- hr_lookups HR1, -- BUG3804891
-- hr_lookups HR2, -- BUG3804891
-- hr_lookups HR3 -- BUG3804891
WHERE CCP.qualifying_event = p_qualifying_event
AND CCP.type_code = p_type_code
--AND HR1.lookup_type = 'US_COBRA_EVENT' -- BUG3804891
--AND HR1.lookup_code = CCP.qualifying_event -- BUG3804891
--AND HR2.lookup_type = 'US_COBRA_SPECIAL_TYPES' -- BUG3804891
--AND HR2.lookup_code = CCP.type_code -- BUG3804891
--AND HR3.lookup_type = 'US_COBRA_COVERAGE_UOM' -- BUG3804891
--AND HR3.lookup_code = CCP.coverage_uom -- BUG3804891
AND p_qualifying_start_date
BETWEEN EFFECTIVE_START_DATE
AND EFFECTIVE_END_DATE;
SELECT 'Y',
pos.actual_termination_date + 1
FROM per_periods_of_service pos
WHERE pos.business_group_id + 0 = p_business_group_id
AND pos.person_id = p_person_id
AND pos.actual_termination_date IS NOT NULL
AND pos.actual_termination_date <= p_qualifying_date
ORDER BY pos.actual_termination_date DESC; --BUG1712478
SELECT fnd_number.canonical_to_number(working_hours)
FROM per_organization_units ou
WHERE ou.organization_id = p_organization_id
AND ou.business_group_id + 0 = p_business_group_id
AND ou.date_from <=p_qualifying_date;
SELECT working_hours
FROM hr_positions_f
WHERE position_id = p_position_id
and p_qualifying_date
between effective_start_date
and effective_end_date;
SELECT 'Y'
FROM per_assignments_f a
WHERE a.assignment_id = p_assignment_id
AND a.business_group_id + 0 = p_business_group_id
AND p_qualifying_date
BETWEEN a.effective_start_date AND
a.effective_end_date
AND ( a.normal_hours < l_std_hrs
OR a.normal_hours IS NULL );
SELECT 'Y'
FROM
ben_benefit_classifications bc,
pay_element_types et,
pay_element_links_f el,
pay_element_entries_f ee
WHERE
ee.assignment_id = p_assignment_id AND
l_last_eligible_date BETWEEN
ee.effective_start_date AND ee.effective_end_date
AND
el.element_link_id = ee.element_link_id AND
l_last_eligible_date BETWEEN
el.effective_start_date AND el.effective_end_date
AND
et.element_type_id = el.element_type_id AND
et.processing_type = 'R'
AND
bc.benefit_classification_id = et.benefit_classification_id AND
bc.cobra_flag = 'Y';
SELECT organization_id,
position_id
FROM per_assignments_F a
WHERE a.assignment_id = p_assignment_id
AND a.business_group_id + 0 = p_business_group_id
AND p_qualifying_date
BETWEEN a.effective_start_date AND
a.effective_end_date;
SELECT 'Y'
FROM per_cobra_coverage_statuses ccs
WHERE ccs.cobra_coverage_enrollment_id = p_cobra_coverage_enrollment_id
AND ccs.cobra_coverage_status_type = 'ELEC';
SELECT meaning
FROM hr_lookups l
WHERE lookup_type = 'US_COBRA_STATUS'
AND lookup_code = 'AWAIT';
SELECT tpt.period_type
FROM per_time_period_types tpt
WHERE tpt.number_per_fiscal_year = 12
AND tpt.system_flag = 'Y';
PROCEDURE hr_cobra_do_cce_insert ( p_Rowid IN OUT nocopy VARCHAR2,
p_Cobra_Coverage_Enrollment_Id IN OUT nocopy NUMBER,
p_Business_Group_Id NUMBER,
p_Assignment_Id NUMBER,
p_Period_Type VARCHAR2,
p_Qualifying_Date IN OUT nocopy DATE,
p_Qualifying_Event VARCHAR2,
p_Coverage_End_Date DATE,
p_Coverage_Start_Date DATE,
p_Termination_Reason VARCHAR2,
p_Contact_Relationship_Id NUMBER,
p_Attribute_Category VARCHAR2,
p_Attribute1 VARCHAR2,
p_Attribute2 VARCHAR2,
p_Attribute3 VARCHAR2,
p_Attribute4 VARCHAR2,
p_Attribute5 VARCHAR2,
p_Attribute6 VARCHAR2,
p_Attribute7 VARCHAR2,
p_Attribute8 VARCHAR2,
p_Attribute9 VARCHAR2,
p_Attribute10 VARCHAR2,
p_Attribute11 VARCHAR2,
p_Attribute12 VARCHAR2,
p_Attribute13 VARCHAR2,
p_Attribute14 VARCHAR2,
p_Attribute15 VARCHAR2,
p_Attribute16 VARCHAR2,
p_Attribute17 VARCHAR2,
p_Attribute18 VARCHAR2,
p_Attribute19 VARCHAR2,
p_Attribute20 VARCHAR2,
p_Grace_Days NUMBER,
p_comments VARCHAR2,
p_organization_id NUMBER,
p_person_id NUMBER,
p_position_id NUMBER,
p_status VARCHAR2,
p_status_date DATE,
p_amount_charged IN OUT nocopy VARCHAR2,
p_first_payment_due_date DATE,
p_event_coverage NUMBER) IS
--
-- Declare local variables
--
l_dummy_rowid VARCHAR2(30);
l_package VARCHAR2(70) := g_package || 'hr_cobra_do_cce_insert';
per_cobra_cov_enrollments_pkg.insert_row(
p_rowid,
p_cobra_coverage_enrollment_id,
p_business_group_id,
p_assignment_id,
p_period_type,
p_qualifying_date,
p_qualifying_event,
p_coverage_end_date,
p_coverage_start_date,
p_termination_reason,
p_contact_relationship_id,
p_attribute_category,
p_attribute1,
p_attribute2,
p_attribute3,
p_attribute4,
p_attribute5,
p_attribute6,
p_attribute7,
p_attribute8,
p_attribute9,
p_attribute10,
p_attribute11,
p_attribute12,
p_attribute13,
p_attribute14,
p_attribute15,
p_attribute16,
p_attribute17,
p_attribute18,
p_attribute19,
p_attribute20,
p_grace_days,
p_comments);
per_cobra_cov_statuses_pkg.insert_row(l_dummy_rowid,
l_Cobra_Coverage_Status_Id,
p_Business_Group_Id,
p_Cobra_Coverage_Enrollment_Id,
'AWAIT',
p_qualifying_date,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL);
END hr_cobra_do_cce_insert;
INSERT INTO per_cobra_coverage_benefits_f (
cobra_coverage_benefit_id,
cobra_coverage_enrollment_id,
effective_start_date,
effective_end_date,
element_type_id,
business_group_id,
coverage_type,
accept_reject_flag,
coverage_amount,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login)
SELECT
per_cobra_coverage_benefits_s.nextval,
p_cobra_coverage_enrollment_id,
p_qualifying_date,
to_date('31-12-4712', 'DD-MM-YYYY'),
et.element_type_id,
p_business_group_id,
NVL(eev_cov.screen_entry_value, iv_cov.default_value),
'ACC',
fnd_number.number_to_canonical(
NVL(fnd_number.canonical_to_number(eev_er.screen_entry_value), NVL(fnd_number.canonical_to_number(bc.employer_contribution), fnd_number.canonical_to_number(iv_er.default_value))) +
NVL(fnd_number.canonical_to_number(eev_ee.screen_entry_value), NVL(fnd_number.canonical_to_number(bc.employee_contribution), fnd_number.canonical_to_number(iv_ee.default_value)))
),
trunc(sysdate),
l_user_id,
TRUNC(sysdate),
l_user_id,
l_login_id
FROM
pay_input_values_f iv_cov,
pay_input_values_f iv_ee,
pay_input_values_f iv_er,
pay_element_entry_values_f eev_cov,
pay_element_entry_values_f eev_er,
pay_element_entry_values_f eev_ee,
ben_benefit_contributions_f bc,
ben_benefit_classifications bc2,
pay_element_types_f et,
pay_element_links_f el,
pay_element_entries_f ee
WHERE
ee.assignment_id = p_assignment_id AND
l_last_eligible_date
BETWEEN ee.effective_start_date AND ee.effective_end_Date
AND
el.element_link_id = ee.element_link_id AND
el.business_group_id + 0 = p_business_group_id AND
l_last_eligible_date
BETWEEN el.effective_start_date AND el.effective_end_Date
AND
et.element_type_id = el.element_type_id AND
et.processing_type = 'R' AND
l_last_eligible_date
BETWEEN et.effective_start_date AND et.effective_end_Date
AND
bc2.benefit_classification_id = et.benefit_classification_id AND
bc2.cobra_flag = 'Y'
AND
iv_cov.element_type_id = et.element_type_id AND
l_last_eligible_date
BETWEEN iv_cov.effective_start_date AND iv_cov.effective_end_date AND
UPPER(iv_cov.name) = 'COVERAGE'
AND
iv_er.element_type_id = et.element_type_id AND
l_last_eligible_date
BETWEEN iv_er.effective_start_date AND iv_er.effective_end_date AND
UPPER(iv_er.name) = 'ER CONTR'
AND
iv_ee.element_type_id = et.element_type_id AND
l_last_eligible_date
BETWEEN iv_ee.effective_start_date AND iv_ee.effective_end_Date AND
UPPER(iv_ee.name) = 'EE CONTR'
AND
eev_er.element_entry_id = ee.element_entry_id AND
eev_er.input_value_id = iv_er.input_value_id AND
l_last_eligible_date
BETWEEN eev_er.effective_start_date AND eev_er.effective_end_date
AND
eev_ee.element_entry_id = ee.element_entry_id AND
eev_ee.input_value_id = iv_ee.input_value_id AND
l_last_eligible_date
BETWEEN eev_ee.effective_start_date AND eev_ee.effective_end_Date
AND
eev_cov.element_entry_id = ee.element_entry_id AND
eev_cov.input_value_id = iv_cov.input_value_id AND
l_last_eligible_date
BETWEEN eev_cov.effective_start_date AND eev_cov.effective_end_date
AND
bc.element_type_id(+) = et.element_type_id AND
l_last_eligible_date
BETWEEN bc.effective_start_date(+) AND bc.effective_end_date(+) AND
( bc.coverage_type = NVL(eev_cov.screen_entry_value, iv_cov.default_value)
OR
bc.element_type_id IS NULL
);
SELECT fnd_number.number_to_canonical(SUM(fnd_number.canonical_to_number(ccb.coverage_amount)))
FROM per_cobra_coverage_benefits_f ccb
WHERE ccb.cobra_coverage_enrollment_id = p_cobra_coverage_enrollment_id
and p_session_date between ccb.effective_start_date and ccb.effective_end_date
AND ccb.accept_reject_flag = 'ACC';
SELECT per_sched_cobra_payments_s.nextval
FROM dual;
SELECT NVL(COUNT(*),0)
FROM per_sched_cobra_payments
WHERE cobra_coverage_enrollment_id = p_cobra_coverage_enrollment_id
AND business_group_id = p_business_group_id;
INSERT INTO per_sched_cobra_payments (
scheduled_cobra_payment_id,
business_group_id,
cobra_coverage_enrollment_id,
amount_due,
date_due,
grace_due_date,
amount_received,
date_received,
comments,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute16,
attribute17,
attribute18,
attribute19,
attribute20,
last_update_date,
last_updated_by,
last_update_login,
created_by,
creation_date)
SELECT
l_scp_id,
p_business_group_id,
p_cobra_coverage_enrollment_id,
fnd_number.number_to_canonical(NVL(SUM(fnd_number.canonical_to_number(coverage_amount)),'0')),
ADD_MONTHS(p_first_payment_due_date, l_count),
ADD_MONTHS(p_first_payment_due_date, l_count) + p_grace_days,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
TRUNC(sysdate),
l_user_id,
l_login_id,
l_user_id,
TRUNC(sysdate)
FROM per_cobra_coverage_benefits_f ccb
WHERE ccb.cobra_coverage_enrollment_id = p_cobra_coverage_enrollment_id
AND ADD_MONTHS(p_first_payment_due_date, l_count) BETWEEN
ccb.effective_start_date AND ccb.effective_end_date
AND ccb.accept_reject_flag = 'ACC';
update per_sched_cobra_payments
set grace_due_date = date_due + p_new_grace_days
where COBRA_COVERAGE_ENROLLMENT_ID = p_cobra_coverage_enrollment_id
and grace_due_date - date_due <> p_new_grace_days
and amount_received is null
and date_received is null
and date_due >= p_session_date;
PROCEDURE hr_cobra_do_cce_update ( p_Rowid IN OUT nocopy VARCHAR2,
p_Cobra_Coverage_Enrollment_Id IN OUT nocopy NUMBER,
p_Business_Group_Id NUMBER,
p_Assignment_Id NUMBER,
p_Period_Type VARCHAR2,
p_Qualifying_Date DATE,
p_Qualifying_Event VARCHAR2,
p_Coverage_End_Date DATE,
p_Coverage_Start_Date DATE,
p_Termination_Reason VARCHAR2,
p_Contact_Relationship_Id NUMBER,
p_Attribute_Category VARCHAR2,
p_Attribute1 VARCHAR2,
p_Attribute2 VARCHAR2,
p_Attribute3 VARCHAR2,
p_Attribute4 VARCHAR2,
p_Attribute5 VARCHAR2,
p_Attribute6 VARCHAR2,
p_Attribute7 VARCHAR2,
p_Attribute8 VARCHAR2,
p_Attribute9 VARCHAR2,
p_Attribute10 VARCHAR2,
p_Attribute11 VARCHAR2,
p_Attribute12 VARCHAR2,
p_Attribute13 VARCHAR2,
p_Attribute14 VARCHAR2,
p_Attribute15 VARCHAR2,
p_Attribute16 VARCHAR2,
p_Attribute17 VARCHAR2,
p_Attribute18 VARCHAR2,
p_Attribute19 VARCHAR2,
p_Attribute20 VARCHAR2,
p_Grace_Days NUMBER,
p_comments VARCHAR2,
p_event_coverage NUMBER,
p_session_date DATE,
p_status VARCHAR2,
p_status_date IN OUT nocopy DATE,
p_status_meaning IN OUT nocopy VARCHAR2,
p_first_payment_due_date DATE,
p_old_first_payment_due_date VARCHAR2,
p_amount_charged IN OUT nocopy VARCHAR2 ) IS
--
-- declare local variables
--
l_package VARCHAR2(70) := g_package || 'hr_cobra_do_cce_update';
select SCHEDULED_COBRA_PAYMENT_ID,grace_due_date - date_due
from per_sched_cobra_payments
where COBRA_COVERAGE_ENROLLMENT_ID = cp_cov_enrollment_id
and amount_received is null
and date_received is null
and date_due >= cp_session_date;
/* We don't need this validation when we update the record
part of fix for bug#4599753
per_cobra_cov_enrollments_pkg.hr_cobra_chk_unique_enrollment (
p_cobra_coverage_enrollment_id,
p_assignment_id,
p_contact_relationship_id,
p_qualifying_event,
p_qualifying_date );
per_cobra_cov_enrollments_pkg.update_row(
p_rowid,
p_business_group_id,
p_assignment_id,
p_period_type,
p_qualifying_date,
p_qualifying_event,
p_coverage_end_date,
p_coverage_start_date,
p_termination_reason,
p_contact_relationship_id,
p_attribute_category,
p_attribute1,
p_attribute2,
p_attribute3,
p_attribute4,
p_attribute5,
p_attribute6,
p_attribute7,
p_attribute8,
p_attribute9,
p_attribute10,
p_attribute11,
p_attribute12,
p_attribute13,
p_attribute14,
p_attribute15,
p_attribute16,
p_attribute17,
p_attribute18,
p_attribute19,
p_attribute20,
p_grace_days,
p_comments);
is same or not, if it is not same then update the sched_cobra_payment
with new grace_period */
if p_Grace_Days <> ln_old_graceperiod then
correct_cobra_scp_graceperiod(p_Cobra_Coverage_Enrollment_Id,
p_session_date,
p_Grace_Days);
END hr_cobra_do_cce_update;
function hr_cobra_update_element (
p_effective_date date
,p_Cobra_Coverage_Enrollment_Id NUMBER
,p_Business_Group_Id NUMBER
,p_Assignment_Id NUMBER
,p_amount_charged in out nocopy varchar2
,p_cobra_coverage_benefit_id in out nocopy number
,p_effective_start_date in out nocopy date
,p_effective_end_date in out nocopy date
) return boolean IS
--
-- declare local variables
--
l_package VARCHAR2(70) := g_package || 'hr_cobra_update_element';
select ee.element_entry_id
,ee.effective_start_date
,ee.effective_end_date
,ccb.element_type_id
,ccb.cobra_coverage_benefit_id
,ccb.effective_start_date
from per_cobra_coverage_benefits_f ccb
,pay_element_entries_f ee
,pay_element_links_f el
where ccb.cobra_coverage_enrollment_id = p_cobra_coverage_enrollment_id
and p_effective_date between ccb.effective_start_date
and ccb.effective_end_date
and ccb.element_type_id = el.element_type_id
and p_effective_date between el.effective_start_date
and el.effective_end_date
and el.business_group_id = p_business_group_id
and el.element_link_id = ee.element_link_id
and ee.assignment_id = p_assignment_id;
select et.element_type_id
,ee.effective_start_date
,ee.effective_end_date
from pay_element_entries_f ee
,pay_element_links_f el
,pay_element_types_f et
,pay_element_classifications ec
,ben_benefit_classifications bc
where ee.assignment_id = p_assignment_id
and p_effective_date between ee.effective_start_date
and ee.effective_end_date
and el.element_link_id = ee.element_link_id
and el.business_group_id = p_business_group_id
and p_effective_date between el.effective_start_date
and el.effective_end_date
and el.element_type_id = et.element_type_id
and p_effective_date between et.effective_start_date
and et.effective_end_date
and et.classification_id = ec.classification_id
and ec.legislation_code = 'US'
and et.benefit_classification_id = bc.benefit_classification_id
and bc.legislation_code = 'US'
and bc.cobra_flag = 'Y'
and not exists
(select 1 from per_cobra_coverage_benefits_f ccbf
where ccbf.cobra_coverage_enrollment_id = p_cobra_coverage_enrollment_id
and ccbf.element_type_id = el.element_type_id
and ccbf.effective_start_date >= el.effective_start_date
and ccbf.effective_end_date <= el.effective_end_date
and p_effective_date between ccbf.effective_start_date
and ccbf.effective_end_date
and ccbf.business_group_id = p_business_group_id);
SELECT per_cobra_coverage_benefits_s.nextval
FROM dual;
update per_cobra_coverage_benefits_f
set effective_end_date = l_ee_effective_end_date
where cobra_coverage_benefit_id = l_cobra_coverage_benefit_id
and effective_start_date = l_ccb_effective_start_date
and effective_end_date = to_date('31-12-4712','DD-MM-YYYY')
and cobra_coverage_enrollment_id = p_cobra_coverage_enrollment_id
and business_group_id = p_business_group_id
and element_type_id = l_element_type_id;
INSERT INTO per_cobra_coverage_benefits_f (
cobra_coverage_benefit_id,
cobra_coverage_enrollment_id,
effective_start_date,
effective_end_date,
element_type_id,
business_group_id,
coverage_type,
accept_reject_flag,
coverage_amount,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login)
SELECT
l_cobra_coverage_benefit_id,
p_cobra_coverage_enrollment_id,
l_new_start_date,
-- to_date('31-12-4712','DD-MM-YYYY'),
l_effective_end_date2,
et.element_type_id,
p_business_group_id,
NVL(eev_cov.screen_entry_value, iv_cov.default_value),
'ACC',
fnd_number.number_to_canonical(
NVL(fnd_number.canonical_to_number(eev_er.screen_entry_value),
NVL(fnd_number.canonical_to_number(bc.employer_contribution),
fnd_number.canonical_to_number(iv_er.default_value))) +
NVL(fnd_number.canonical_to_number(eev_ee.screen_entry_value),
NVL(fnd_number.canonical_to_number(bc.employee_contribution),
fnd_number.canonical_to_number(iv_ee.default_value)))),
trunc(sysdate),
l_user_id,
TRUNC(sysdate),
l_user_id,
l_login_id
from pay_input_values_f iv_cov,
pay_input_values_f iv_ee,
pay_input_values_f iv_er,
pay_element_entry_values_f eev_cov,
pay_element_entry_values_f eev_er,
pay_element_entry_values_f eev_ee,
ben_benefit_contributions_f bc,
ben_benefit_classifications bc2,
pay_element_types_f et,
pay_element_links_f el,
pay_element_entries_f ee
WHERE
ee.assignment_id = p_assignment_id
AND
et.element_type_id = l_element_type_id
AND
l_new_start_date
BETWEEN ee.effective_start_date AND ee.effective_end_Date
AND
el.element_link_id = ee.element_link_id AND
el.business_group_id + 0 = p_business_group_id AND
-- p_qualifying_date
l_new_start_date
BETWEEN el.effective_start_date AND el.effective_end_Date
AND
et.element_type_id = el.element_type_id AND
et.processing_type = 'R' AND
-- p_qualifying_date
l_new_start_date
BETWEEN et.effective_start_date AND et.effective_end_Date
AND
bc2.benefit_classification_id = et.benefit_classification_id AND
bc2.cobra_flag = 'Y'
AND
iv_cov.element_type_id = et.element_type_id AND
-- p_qualifying_date
l_new_start_date
BETWEEN iv_cov.effective_start_date AND iv_cov.effective_end_date AND
UPPER(iv_cov.name) = 'COVERAGE'
AND
iv_er.element_type_id = et.element_type_id AND
-- p_qualifying_date
l_new_start_date
BETWEEN iv_er.effective_start_date AND iv_er.effective_end_date AND
UPPER(iv_er.name) = 'ER CONTR'
AND
iv_ee.element_type_id = et.element_type_id AND
-- p_qualifying_date
l_new_start_date
BETWEEN iv_ee.effective_start_date AND iv_ee.effective_end_Date AND
UPPER(iv_ee.name) = 'EE CONTR'
AND
eev_er.element_entry_id = ee.element_entry_id AND
eev_er.input_value_id = iv_er.input_value_id AND
-- p_qualifying_date
l_new_start_date
BETWEEN eev_er.effective_start_date AND eev_er.effective_end_date
AND
eev_ee.element_entry_id = ee.element_entry_id AND
eev_ee.input_value_id = iv_ee.input_value_id AND
--p_qualifying_date
l_new_start_date
BETWEEN eev_ee.effective_start_date AND eev_ee.effective_end_Date
AND
eev_cov.element_entry_id = ee.element_entry_id AND
eev_cov.input_value_id = iv_cov.input_value_id AND
--p_qualifying_date
l_new_start_date
BETWEEN eev_cov.effective_start_date AND eev_cov.effective_end_date
AND
bc.element_type_id(+) = et.element_type_id AND
--p_qualifying_date
l_new_start_date
BETWEEN bc.effective_start_date(+) AND bc.effective_end_date(+) AND
( bc.coverage_type = NVL(eev_cov.screen_entry_value, iv_cov.default_value)
OR
bc.element_type_id IS NULL
);
END hr_cobra_update_element;
SELECT ccs.cobra_coverage_status_type,
ccs.effective_date,
h.meaning
FROM hr_lookups h,
per_cobra_coverage_statuses ccs
WHERE ccs.cobra_coverage_enrollment_id = p_cobra_coverage_enrollment_id
AND ccs.cobra_coverage_status_id =
(SELECT MAX(ccs1.cobra_coverage_status_id)
FROM per_cobra_coverage_statuses ccs1
WHERE ccs1.cobra_coverage_enrollment_id = ccs.cobra_coverage_enrollment_id
AND ccs1.effective_date =
(SELECT MAX(ccs2.effective_date)
FROM per_cobra_coverage_statuses ccs2
WHERE ccs2.effective_date <= p_session_date
AND ccs2.cobra_coverage_enrollment_id = ccs1.cobra_coverage_enrollment_id))
AND ccs.cobra_coverage_status_type = h.lookup_code
AND h.lookup_type = 'US_COBRA_STATUS';
PROCEDURE hr_cobra_do_ccs_insert ( p_Rowid IN OUT nocopy VARCHAR2,
p_Cobra_Coverage_Status_Id IN OUT nocopy NUMBER,
p_Business_Group_Id NUMBER,
p_Cobra_Coverage_Enrollment_Id NUMBER,
p_Cobra_Coverage_Status_Type VARCHAR2,
p_Effective_Date DATE,
p_current_status IN OUT nocopy VARCHAR2,
p_current_status_meaning IN OUT nocopy VARCHAR2,
p_current_status_date IN OUT nocopy DATE,
p_current_d_status_date IN OUT nocopy DATE,
p_Attribute_Category VARCHAR2,
p_Attribute1 VARCHAR2,
p_Attribute2 VARCHAR2,
p_Attribute3 VARCHAR2,
p_Attribute4 VARCHAR2,
p_Attribute5 VARCHAR2,
p_Attribute6 VARCHAR2,
p_Attribute7 VARCHAR2,
p_Attribute8 VARCHAR2,
p_Attribute9 VARCHAR2,
p_Attribute10 VARCHAR2,
p_Attribute11 VARCHAR2,
p_Attribute12 VARCHAR2,
p_Attribute13 VARCHAR2,
p_Attribute14 VARCHAR2,
p_Attribute15 VARCHAR2,
p_Attribute16 VARCHAR2,
p_Attribute17 VARCHAR2,
p_Attribute18 VARCHAR2,
p_Attribute19 VARCHAR2,
p_Attribute20 VARCHAR2,
p_comments VARCHAR2,
p_session_date DATE ) IS
--
-- Declare local variable
--
l_package VARCHAR2(70) := g_package || 'hr_cobra_do_ccs_insert';
per_cobra_cov_statuses_pkg.insert_row
( p_Rowid,
p_Cobra_Coverage_Status_Id,
p_Business_Group_Id,
p_Cobra_Coverage_Enrollment_Id,
p_Cobra_Coverage_Status_Type,
p_Effective_Date,
p_Attribute_Category,
p_Attribute1,
p_Attribute2,
p_Attribute3,
p_Attribute4,
p_Attribute5,
p_Attribute6,
p_Attribute7,
p_Attribute8,
p_Attribute9,
p_Attribute10,
p_Attribute11,
p_Attribute12,
p_Attribute13,
p_Attribute14,
p_Attribute15,
p_Attribute16,
p_Attribute17,
p_Attribute18,
p_Attribute19,
p_Attribute20,
p_comments );
END hr_cobra_do_ccs_insert;
PROCEDURE hr_cobra_do_ccs_update ( p_Rowid IN OUT nocopy VARCHAR2,
p_Cobra_Coverage_Status_Id IN OUT nocopy NUMBER,
p_Business_Group_Id NUMBER,
p_Cobra_Coverage_Enrollment_Id NUMBER,
p_Cobra_Coverage_Status_Type VARCHAR2,
p_Effective_Date DATE,
p_current_status IN OUT nocopy VARCHAR2,
p_current_status_meaning IN OUT nocopy VARCHAR2,
p_current_status_date IN OUT nocopy DATE,
p_current_d_status_date IN OUT nocopy DATE,
p_Attribute_Category VARCHAR2,
p_Attribute1 VARCHAR2,
p_Attribute2 VARCHAR2,
p_Attribute3 VARCHAR2,
p_Attribute4 VARCHAR2,
p_Attribute5 VARCHAR2,
p_Attribute6 VARCHAR2,
p_Attribute7 VARCHAR2,
p_Attribute8 VARCHAR2,
p_Attribute9 VARCHAR2,
p_Attribute10 VARCHAR2,
p_Attribute11 VARCHAR2,
p_Attribute12 VARCHAR2,
p_Attribute13 VARCHAR2,
p_Attribute14 VARCHAR2,
p_Attribute15 VARCHAR2,
p_Attribute16 VARCHAR2,
p_Attribute17 VARCHAR2,
p_Attribute18 VARCHAR2,
p_Attribute19 VARCHAR2,
p_Attribute20 VARCHAR2,
p_comments VARCHAR2,
p_session_date DATE ) IS
--
-- declare local variables
--
l_package VARCHAR2(70) := g_package || 'hr_cobra_do_ccs_update';
per_cobra_cov_statuses_pkg.update_row(p_rowid,
p_Business_Group_Id,
p_Cobra_Coverage_Enrollment_Id,
p_Cobra_Coverage_Status_Type,
p_Effective_Date,
p_Attribute_Category,
p_Attribute1,
p_Attribute2,
p_Attribute3,
p_Attribute4,
p_Attribute5,
p_Attribute6,
p_Attribute7,
p_Attribute8,
p_Attribute9,
p_Attribute10,
p_Attribute11,
p_Attribute12,
p_Attribute13,
p_Attribute14,
p_Attribute15,
p_Attribute16,
p_Attribute17,
p_Attribute18,
p_Attribute19,
p_Attribute20,
p_comments );
END hr_cobra_do_ccs_update;
update per_cobra_cov_enrollments
set coverage_end_date = p_session_date
where cobra_coverage_enrollment_id = p_cobra_coverage_enrollment_id
and coverage_start_date = p_coverage_start_date
and coverage_end_date = p_coverage_end_date
and qualifying_event = p_qualifying_event;
hr_utility.trace('Update to per_cobra_cov_enrollments done ');
select cobra_dependent_id,effective_end_date,object_version_number,effective_start_date
from per_cobra_dependents_f
where cobra_coverage_enrollment_id = p_cobra_coverage_enrollment_id
and p_session_date between effective_start_date and effective_end_date;
update per_cobra_dependents_f
set effective_end_date = ld_session_date,
object_version_number = ln_ovn + 1
where cobra_dependent_id = ln_cdp_id
and effective_start_date = ld_eff_start_date
and effective_end_date = ld_eff_end_date;
per_cobra_cov_statuses_pkg.insert_row
( p_dummy_rowid,
p_dummy_Id,
p_Business_Group_Id,
p_Cobra_Coverage_Enrollment_Id,
p_Status,
p_status_Date,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL);
update per_cobra_cov_enrollments
set coverage_end_date = p_session_date
where cobra_coverage_enrollment_id = p_cobra_coverage_enrollment_id
and p_session_date between coverage_start_date and coverage_end_date;
hr_utility.trace('Update to per_cobra_cov_enrollments done ');
PROCEDURE hr_cobra_do_ccs_delete ( p_Rowid VARCHAR2,
p_cobra_coverage_enrollment_id NUMBER,
p_session_date DATE,
p_status IN OUT nocopy VARCHAR2,
p_status_meaning IN OUT nocopy VARCHAR2,
p_status_date IN OUT nocopy DATE,
p_d_status_date IN OUT nocopy DATE ) IS
BEGIN
--
-- delete row
--
per_cobra_cov_statuses_pkg.Delete_Row( p_rowid );
END hr_cobra_do_ccs_delete;
SELECT 'Y'
FROM per_sched_cobra_payments scp
WHERE scp.cobra_coverage_enrollment_id = p_cobra_coverage_enrollment_id
AND ( scp.scheduled_cobra_payment_id <> p_scheduled_cobra_payment_id
OR p_scheduled_cobra_payment_id IS NULL
)
AND scp.date_due = p_due_date;
PROCEDURE hr_cobra_do_scp_pre_insert ( p_scheduled_cobra_payment_id IN OUT nocopy NUMBER,
p_cobra_coverage_enrollment_id NUMBER,
p_due_date DATE ) IS
--
-- declare local variables
--
--
-- declare cursor
--
CURSOR scp_id IS
SELECT per_sched_cobra_payments_s.nextval
FROM sys.dual;
l_package VARCHAR2(70) := g_package || 'hr_cobra_do_scp_insert';
END hr_cobra_do_scp_pre_insert;
PROCEDURE hr_cobra_do_scp_pre_update ( p_scheduled_cobra_payment_id IN OUT nocopy NUMBER,
p_cobra_coverage_enrollment_id NUMBER,
p_due_date DATE ) IS
BEGIN
--
--
-- chekc for duplicate due date
--
--
hr_utility.set_location(g_package || 'hr_cobra_do_scp_update', 1);
END hr_cobra_do_scp_pre_update;
SELECT 'lock payment_schedules'
FROM per_sched_cobra_payments scp
WHERE scp.business_group_id + 0 = p_business_group_id
AND scp.cobra_coverage_enrollment_id = p_cobra_coverage_enrollment_id
FOR UPDATE OF scp.scheduled_cobra_payment_id;
SELECT bc.employer_contribution + bc.employee_contribution,
bc.employer_contribution + bc.employee_contribution
FROM ben_benefit_contributions bc
WHERE bc.business_group_id + 0 = p_business_group_id
AND bc.coverage_type = p_coverage_type
AND bc.element_type_id = p_element_type_id
AND p_session_date BETWEEN
bc.effective_start_date AND bc.effective_end_date;
PROCEDURE hr_cobra_do_ccb_update ( p_Rowid IN OUT nocopy VARCHAR2,
p_Cobra_Coverage_Benefit_Id IN OUT nocopy NUMBER,
p_Effective_Start_Date DATE,
p_Effective_End_Date DATE,
p_Business_Group_Id NUMBER,
p_Cobra_Coverage_Enrollment_Id NUMBER,
p_Element_Type_Id NUMBER,
p_Accept_Reject_Flag VARCHAR2,
p_Coverage_Amount VARCHAR2,
p_Coverage_Type VARCHAR2,
p_Attribute_Category VARCHAR2,
p_Attribute1 VARCHAR2,
p_Attribute2 VARCHAR2,
p_Attribute3 VARCHAR2,
p_Attribute4 VARCHAR2,
p_Attribute5 VARCHAR2,
p_Attribute6 VARCHAR2,
p_Attribute7 VARCHAR2,
p_Attribute8 VARCHAR2,
p_Attribute9 VARCHAR2,
p_Attribute10 VARCHAR2,
p_Attribute11 VARCHAR2,
p_Attribute12 VARCHAR2,
p_Attribute13 VARCHAR2,
p_Attribute14 VARCHAR2,
p_Attribute15 VARCHAR2,
p_Attribute16 VARCHAR2,
p_Attribute17 VARCHAR2,
p_Attribute18 VARCHAR2,
p_Attribute19 VARCHAR2,
p_Attribute20 VARCHAR2,
p_qualifying_event VARCHAR2,
p_new_amount_charged IN OUT nocopy VARCHAR2 ) IS
BEGIN
--
-- do update
--
per_cobra_cov_benefits_pkg.update_row
(p_Rowid,
p_Cobra_Coverage_Benefit_Id,
p_Effective_Start_Date,
p_Effective_End_Date,
p_Business_Group_Id,
p_Cobra_Coverage_Enrollment_Id,
p_Element_Type_Id,
p_Accept_Reject_Flag,
p_Coverage_Amount,
p_Coverage_Type,
p_Attribute_Category,
p_Attribute1,
p_Attribute2,
p_Attribute3,
p_Attribute4,
p_Attribute5,
p_Attribute6,
p_Attribute7,
p_Attribute8,
p_Attribute9,
p_Attribute10,
p_Attribute11,
p_Attribute12,
p_Attribute13,
p_Attribute14,
p_Attribute15,
p_Attribute16,
p_Attribute17,
p_Attribute18,
p_Attribute19,
p_Attribute20);
END hr_cobra_do_ccb_update;
SELECT 'Y'
FROM per_cobra_coverage_benefits_f ccb
WHERE ccb.rowid = p_rowid
AND ccb.accept_reject_flag = 'REJ';
PROCEDURE hr_cobra_do_ccb_post_update ( p_cobra_coverage_enrollment_id NUMBER,
p_new_amount_charged VARCHAR2,
p_session_date DATE ) IS
--
BEGIN
--
-- update payment schedules
--
hr_cobra_correct_scp( p_cobra_coverage_enrollment_id,
p_session_date );
END hr_cobra_do_ccb_post_update;
SELECT 'Y'
FROM dual
WHERE EXISTS
( SELECT 'x'
FROM pay_element_entry_values eev_cov,
pay_input_values iv_cov,
pay_element_entries ee,
pay_element_links el,
pay_element_types et
WHERE
et.element_type_id = p_element_type_id
AND
el.element_type_id = et.element_type_id
AND
ee.element_link_id = el.element_link_id AND
ee.assignment_id = p_assignment_id
AND
iv_cov.element_type_id = et.element_type_id AND
UPPER(iv_cov.name) = 'COVERAGE'
AND
iv_cov.input_value_id = eev_cov.input_value_id AND
eev_cov.element_entry_id = ee.element_entry_id AND
eev_cov.screen_entry_value = p_coverage_type );
SELECT 'Y'
FROM ben_benefit_contributions
WHERE
element_type_id = p_element_type_id AND
coverage_type = p_coverage_type;
SELECT
NVL(fnd_number.number_to_canonical(
NVL(fnd_number.canonical_to_number(eev_er.screen_entry_value), NVL(fnd_number.canonical_to_number(bc.employer_contribution), fnd_number.canonical_to_number(iv_er.default_value))) +
NVL(fnd_number.canonical_to_number(eev_ee.screen_entry_value), NVL(fnd_number.canonical_to_number(bc.employee_contribution), fnd_number.canonical_to_number(iv_ee.default_value)))
),'0')
FROM
pay_element_entry_values eev_er,
pay_element_entry_values eev_ee,
pay_element_entry_values eev_cov,
pay_input_values iv_er,
pay_input_values iv_ee,
pay_input_values iv_cov,
ben_benefit_contributions bc,
pay_element_entries ee,
pay_element_links el,
pay_element_types et
WHERE
bc.element_type_id(+) = et.element_type_id AND
(
(bc.coverage_type IS NULL)
OR
( bc.coverage_type = p_coverage_type)
)
AND
eev_ee.element_entry_id = ee.element_entry_id AND
eev_ee.input_value_id = iv_ee.input_value_id
AND
eev_er.element_entry_id = ee.element_entry_id AND
eev_er.input_value_id = iv_er.input_value_id
AND
iv_ee.element_type_id = et.element_type_id AND
UPPER(iv_ee.name) = 'EE CONTR'
AND
iv_er.element_type_id = et.element_type_id AND
UPPER(iv_er.name) = 'ER CONTR'
AND
iv_cov.input_value_id = eev_cov.input_value_id AND
eev_cov.element_entry_id = ee.element_entry_id AND
eev_cov.screen_entry_value = p_coverage_type
AND
iv_cov.element_type_id = et.element_type_id AND
UPPER(iv_cov.name) = 'COVERAGE'
AND
ee.element_link_id = el.element_link_id AND
ee.assignment_id = p_assignment_id
AND
el.element_type_id = et.element_type_id
AND
et.element_type_id = p_element_type_id;
SELECT
NVL(fnd_number.number_to_canonical(
NVL(fnd_number.canonical_to_number(bc.employer_contribution), DECODE(fnd_number.canonical_to_number(iv_cov.default_value), NULL, 0, fnd_number.canonical_to_number(iv_er.default_value))) +
NVL(fnd_number.canonical_to_number(bc.employee_contribution), DECODE(fnd_number.canonical_to_number(iv_cov.default_value), NULL, 0, fnd_number.canonical_to_number(iv_ee.default_value)))
),'0')
FROM
ben_benefit_contributions bc,
pay_input_values iv_er,
pay_input_values iv_ee,
pay_input_values iv_cov,
pay_element_types et
WHERE
bc.element_type_id(+) = et.element_type_id AND
(
(bc.coverage_type IS NULL)
OR
( bc.coverage_type = p_coverage_type)
)
AND
iv_ee.element_type_id = et.element_type_id AND
UPPER(iv_ee.name) = 'EE CONTR'
AND
iv_er.element_type_id = et.element_type_id AND
UPPER(iv_er.name) = 'ER CONTR'
AND
iv_cov.element_type_id (+)= et.element_type_id AND
UPPER(iv_cov.name(+)) = 'COVERAGE' AND
iv_cov.default_value (+)= p_coverage_type
AND
et.element_type_id = p_element_type_id;
UPDATE per_sched_cobra_payments scp
SET amount_due =
( SELECT fnd_number.number_to_canonical(NVL(SUM(fnd_number.canonical_to_number(coverage_amount)),'0'))
FROM per_cobra_coverage_benefits_f ccb
WHERE ccb.cobra_coverage_enrollment_id = p_cobra_coverage_enrollment_id
AND scp.date_due BETWEEN
ccb.effective_start_date AND ccb.effective_end_date
AND ccb.accept_reject_flag = 'ACC' )
WHERE scp.cobra_coverage_enrollment_id = p_cobra_coverage_enrollment_id
AND scp.amount_received IS NULL
AND scp.date_due >= p_session_date;
select null
from per_cobra_cov_enrollments cov
where cov.cobra_coverage_enrollment_id = p_cobra_coverage_enrollment_id
and p_effective_start_date
between cov.coverage_start_date
and cov.coverage_end_date
and p_effective_end_date
between cov.coverage_start_date
and cov.coverage_end_date;
select null
from per_contact_relationships cre
where cre.contact_relationship_id = p_contact_relationship_id;
select null
from per_cobra_dependents_f cdp
where cdp.contact_relationship_id = p_contact_relationship_id
and cdp.cobra_coverage_enrollment_id = p_cobra_coverage_enrollment_id
and cdp.cobra_dependent_id <> nvl(p_cobra_dependent_id,-1)
and (p_effective_start_date
between cdp.effective_start_date
and cdp.effective_end_date
or p_effective_end_date
between cdp.effective_start_date
and cdp.effective_end_date);
select null
from per_cobra_dependents_f cdp
where cdp.cobra_dependent_id = nvl(p_cobra_dependent_id,-1)
and cdp.contact_relationship_id = p_contact_relationship_id
and cdp.effective_start_date = p_effective_start_date
and cdp.effective_end_date = p_effective_end_date;
PROCEDURE hr_cobra_do_cdp_insert
(p_cobra_dependent_id out nocopy number,
p_cobra_coverage_enrollment_id in number,
p_contact_relationship_id in number,
p_effective_start_date in date,
p_effective_end_date in date,
p_object_version_number out nocopy number,
p_attribute_category in varchar2,
p_attribute1 in varchar2,
p_attribute2 in varchar2,
p_attribute3 in varchar2,
p_attribute4 in varchar2,
p_attribute5 in varchar2,
p_attribute6 in varchar2,
p_attribute7 in varchar2,
p_attribute8 in varchar2,
p_attribute9 in varchar2,
p_attribute10 in varchar2,
p_attribute11 in varchar2,
p_attribute12 in varchar2,
p_attribute13 in varchar2,
p_attribute14 in varchar2,
p_attribute15 in varchar2,
p_attribute16 in varchar2,
p_attribute17 in varchar2,
p_attribute18 in varchar2,
p_attribute19 in varchar2,
p_attribute20 in varchar2) is
--
begin
--
-- Do business rule checks
--
chk_cobra_dependent_id
(p_cobra_dependent_id => null,
p_object_version_number => null);
select per_cobra_dependents_s.nextval
into p_cobra_dependent_id
from dual;
insert into per_cobra_dependents_f
(cobra_dependent_id,
cobra_coverage_enrollment_id,
contact_relationship_id,
effective_start_date,
effective_end_date,
object_version_number,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute16,
attribute17,
attribute18,
attribute19,
attribute20
)
Values
(p_cobra_dependent_id,
p_cobra_coverage_enrollment_id,
p_contact_relationship_id,
p_effective_start_date,
p_effective_end_date,
p_object_version_number,
p_attribute_category,
p_attribute1,
p_attribute2,
p_attribute3,
p_attribute4,
p_attribute5,
p_attribute6,
p_attribute7,
p_attribute8,
p_attribute9,
p_attribute10,
p_attribute11,
p_attribute12,
p_attribute13,
p_attribute14,
p_attribute15,
p_attribute16,
p_attribute17,
p_attribute18,
p_attribute19,
p_attribute20
);
end hr_cobra_do_cdp_insert;
PROCEDURE hr_cobra_do_cdp_update
(p_row_id in varchar2,
p_cobra_dependent_id in number,
p_cobra_coverage_enrollment_id in number,
p_contact_relationship_id in number,
p_effective_start_date in date,
p_effective_end_date in date,
p_object_version_number in out nocopy number,
p_attribute_category in varchar2,
p_attribute1 in varchar2,
p_attribute2 in varchar2,
p_attribute3 in varchar2,
p_attribute4 in varchar2,
p_attribute5 in varchar2,
p_attribute6 in varchar2,
p_attribute7 in varchar2,
p_attribute8 in varchar2,
p_attribute9 in varchar2,
p_attribute10 in varchar2,
p_attribute11 in varchar2,
p_attribute12 in varchar2,
p_attribute13 in varchar2,
p_attribute14 in varchar2,
p_attribute15 in varchar2,
p_attribute16 in varchar2,
p_attribute17 in varchar2,
p_attribute18 in varchar2,
p_attribute19 in varchar2,
p_attribute20 in varchar2) as
--
l_object_version_number number(9) := p_object_version_number + 1;
update per_cobra_dependents_f
set effective_start_date = p_effective_start_date,
effective_end_date = p_effective_end_date,
contact_relationship_id = p_contact_relationship_id,
object_version_number = l_object_version_number,
attribute_category = p_attribute_category,
attribute1 = p_attribute1,
attribute2 = p_attribute2,
attribute3 = p_attribute3,
attribute4 = p_attribute4,
attribute5 = p_attribute5,
attribute6 = p_attribute6,
attribute7 = p_attribute7,
attribute8 = p_attribute8,
attribute9 = p_attribute9,
attribute10 = p_attribute10,
attribute11 = p_attribute11,
attribute12 = p_attribute12,
attribute13 = p_attribute13,
attribute14 = p_attribute14,
attribute15 = p_attribute15,
attribute16 = p_attribute16,
attribute17 = p_attribute17,
attribute18 = p_attribute18,
attribute19 = p_attribute19,
attribute20 = p_attribute20
where rowid = p_row_id;
end hr_cobra_do_cdp_update;
PROCEDURE hr_cobra_do_cdp_delete
(p_cobra_dependent_id in number,
p_effective_start_date in date,
p_effective_end_date in date,
p_object_version_number in number) is
--
l_proc varchar2(40) := g_package || 'hr_cobra_do_cdp_delete';
delete from per_cobra_dependents_f
where cobra_dependent_id = p_cobra_dependent_id
and effective_start_date = p_effective_start_date
and effective_end_date = p_effective_end_date
and object_version_number = p_object_version_number;
end hr_cobra_do_cdp_delete;
select
cobra_dependent_id,
cobra_coverage_enrollment_id,
contact_relationship_id,
effective_start_date,
effective_end_date,
object_version_number,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute16,
attribute17,
attribute18,
attribute19,
attribute20
from per_cobra_dependents_f
where cobra_dependent_id = p_cobra_dependent_id
and p_effective_start_date = effective_start_date
and p_effective_end_date = effective_end_date
for update nowait;
select a.date_of_birth
from per_people_f a,
per_contact_relationships b
where a.person_id = b.contact_person_id
and b.contact_relationship_id = p_contact_relationship_id;
select null
from per_cobra_cov_enrollments a
where a.cobra_coverage_enrollment_id <> nvl(p_cobra_coverage_enrollment_id,-1)
and a.assignment_id = p_assignment_id
and (p_coverage_start_date
between a.coverage_start_date
and a.coverage_end_date
or
p_coverage_end_date
between a.coverage_start_date
and a.coverage_end_date)
and a.qualifying_event = p_qualifying_event;
select null
from per_cobra_dependents_f a
where a.cobra_coverage_enrollment_id = p_cobra_coverage_enrollment_id
and ( ( a.effective_start_date
not between p_coverage_start_date
and p_coverage_end_date )
or
( a.effective_end_date
not between p_coverage_start_date
and p_coverage_end_date )
);