[Home] [Help]
PACKAGE BODY: APPS.PER_COBRA_ECI
Source
1 PACKAGE BODY per_cobra_eci AS
2 /* $Header: pecobeci.pkb 120.3.12010000.2 2009/08/25 07:17:39 pannapur ship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := 'per_cobra_eci.';
7
8
9 --
10 --
11 -- Name person_disabled
12 --
13 -- Purpose
14 --
15 -- Check that the person was disbaled at the start of the COBRA event or
16 -- within the first sixty days of coverage.
17 --
18 -- Example
19 --
20 -- Notes
21 --
22 FUNCTION person_disabled (p_person_id IN NUMBER,
23 p_qualifying_start_date IN DATE) RETURN BOOLEAN IS
24 --
25 l_disabled VARCHAR2(30);
26 l_package VARCHAR2(70) := g_package || 'person_disabled';
27 --
28 -- This next cursor checks whether a person holds disabled status at the
29 -- start of the qualifying event or within the first sixty days of the
30 -- coverage period.
31 -- The OR clause of the where condition covers the case where a person is
32 -- disabled at the beginning of the cobra coverage or disabled during the
33 -- cobra coverage.
34 --
35 CURSOR C1 IS
36 SELECT PPF.registered_disabled_flag
37 FROM per_people_f PPF
38 WHERE PPF.person_id = p_person_id
39 AND PPF.registered_disabled_flag = 'Y'
40 AND (p_qualifying_start_date
41 BETWEEN PPF.effective_start_date
42 AND PPF.effective_end_date
43 OR PPF.effective_start_date
44 BETWEEN p_qualifying_start_date
45 AND p_qualifying_start_date + 60);
46 --
47 BEGIN
48 --
49 hr_utility.set_location('Entering '||l_package,10);
50 --
51 -- Check if person is disabled within first 60 days of cobra coverage.
52 --
53 OPEN C1;
54 --
55 FETCH C1 INTO l_disabled;
56 --
57 CLOSE C1;
58 --
59 IF l_disabled = 'Y' THEN
60 --
61 RETURN TRUE;
62 --
63 ELSE
64 --
65 RETURN FALSE;
66 --
67 END IF;
68 --
69 hr_utility.set_location('Leaving '||l_package,10);
70 --
71 END person_disabled;
72 --
73 -- Added p_qualifying_event to fix bug#4599753
74 Function coverage_exceeded
75 (p_assignment_id in number,
76 p_cobra_coverage_enrollment_id in number,
77 p_coverage_start_date in date,
78 p_coverage_end_date in date,
79 p_qualifying_event in varchar2) return boolean is
80 --
81 -- Coverage for any number of events cannot exceed 36 months. Enforce this
82 -- by the use of this business rule.
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;
92 --
93
94 -- Added this query to check whether the maximum coverage
95 -- is exceeded for an COBRA enrollment event. Bug#4599753
96 cursor c_get_max_coverage is
97 select event_coverage
98 from per_cobra_qfying_events_f
99 where legislation_code = 'US'
100 and qualifying_event = p_qualifying_event;
101
102 l_months number(9);
103 l_total_months number(9) := 0;
104 ln_max_coverage number;
105 --
106 begin
107 --
108 -- grab all the enrollments for the assignment and make sure that they
109 -- do not break the maximum 36 month coverage period.
110 --
111
112 /* This logic has been changed because we will allow multiple COBRA events
113 to be enrolled by an employee. In this case we have to check for each
114 specific event whether the COBRA coverage period has exceeded maximum
115 coverage, if it exceeds then return TRUE else return FALSE. Bug#4599753
116 */
117 hr_utility.set_location('In coverage_exceeded ->',10);
118 hr_utility.set_location('p_cobra_coverage_enrollment_id ->'||p_cobra_coverage_enrollment_id,10);
119 hr_utility.set_location('p_coverage_start_date ->'||p_coverage_start_date,10);
120 hr_utility.set_location('p_coverage_end_date ->'||p_coverage_end_date,10);
121 hr_utility.set_location('p_qualifying_event ->'||p_qualifying_event,10);
122
123 open c1;
124 fetch c1 into l_months;
125 hr_utility.set_location('l_months ->'||l_months,20);
126 if c1%notfound then
127 l_months := months_between(p_coverage_end_date,p_coverage_start_date);
128
129 end if;
130 close c1;
131
132 hr_utility.set_location('l_months ->'||l_months,30);
133
134 open c_get_max_coverage;
135 fetch c_get_max_coverage into ln_max_coverage;
136 close c_get_max_coverage;
137 hr_utility.set_location('ln_max_coverage ->'||ln_max_coverage,40);
138 /* check if the coverage period is greater than the maximum coverage */
139 if l_months > 0 then
140
141 if l_months > ln_max_coverage then
142 return TRUE;
143 else
144 return FALSE;
145 end if;
146
147 end if;
148 hr_utility.set_location('Returning FALSE from coverage_exceeded ->',50);
149 return FALSE; /* Added for 5203801*/
150
151 end coverage_exceeded;
152
153 --
154 -- Name check_cobra_coverage_period
155 --
156 -- Purpose
157 --
158 -- Check that the coverage period defaulted from the qualifying event
159 -- is in fact right. Legislative changes have happened over time so check
160 -- what the coverage period is at a particular time. The session date is
161 -- not used to track time but instead the qualifying event start date as this
162 -- is the day that they are starting the coverage.
163 --
164 -- Example
165 --
166 -- Notes
167 --
168 PROCEDURE check_cobra_coverage_period
169 (p_qualifying_event IN VARCHAR2,
170 p_qualifying_start_date IN DATE,
171 p_type_code IN VARCHAR2,
172 p_coverage OUT nocopy NUMBER,
173 p_coverage_uom OUT nocopy VARCHAR2) IS
174 --
175 l_package VARCHAR2(70) := g_package || 'check_cobra_coverage_period';
176 --
177 -- This next cursor returns the coverage period for the condition.
178 --
179 CURSOR C1 IS
180 SELECT CCP.coverage,
181 CCP.coverage_uom
182 FROM per_cobra_coverage_periods CCP
183 -- hr_lookups HR1, -- BUG3804891
184 -- hr_lookups HR2, -- BUG3804891
185 -- hr_lookups HR3 -- BUG3804891
186 WHERE CCP.qualifying_event = p_qualifying_event
187 AND CCP.type_code = p_type_code
188 --AND HR1.lookup_type = 'US_COBRA_EVENT' -- BUG3804891
189 --AND HR1.lookup_code = CCP.qualifying_event -- BUG3804891
190 --AND HR2.lookup_type = 'US_COBRA_SPECIAL_TYPES' -- BUG3804891
191 --AND HR2.lookup_code = CCP.type_code -- BUG3804891
192 --AND HR3.lookup_type = 'US_COBRA_COVERAGE_UOM' -- BUG3804891
193 --AND HR3.lookup_code = CCP.coverage_uom -- BUG3804891
194 AND p_qualifying_start_date
195 BETWEEN EFFECTIVE_START_DATE
196 AND EFFECTIVE_END_DATE;
197 --
198 BEGIN
199 --
200 hr_utility.set_location('Entering '||l_package,10);
201 hr_utility.trace('p_qualifying_event = ' || p_qualifying_event);
202 hr_utility.trace('p_qualifying_start_date = ' || p_qualifying_start_date);
203 hr_utility.trace('p_type_code = ' || p_type_code);
204
205 --
206 -- Check Condition Code and whether it returns any coverage
207 -- period and unit of measure.
208 --
209 OPEN C1;
210 --
211 FETCH C1 INTO p_coverage, p_coverage_uom;
212 --
213 CLOSE C1;
214 --
215 hr_utility.trace('p_coverage = ' || p_coverage);
216 hr_utility.trace('p_coverage_uom = ' || p_coverage_uom);
217 hr_utility.set_location('Leaving '||l_package,20);
218 --
219 END check_cobra_coverage_period;
220 --
221 -- Name hr_cobra_chk_event_eligible
222 --
223 -- Purpose
224 --
225 -- check whether or not the enrolled is infact
226 -- entitled to the Qualifying event entered
227 --
228 -- Arguments
229 --
230 -- p_organization_id NUMBER
231 -- p_business_group_id NUMBER
232 -- p_assignment_id NUMBER
233 -- p_person_id NUMBER
234 -- p_qualifying_event VARCHAR2
235 -- p_qualifying_date DATE
236 --
237 -- Example
238 --
239 -- Notes
240 --
241 PROCEDURE hr_cobra_chk_event_eligible (p_organization_id NUMBER,
242 p_business_group_id NUMBER,
243 p_assignment_id NUMBER,
244 p_person_id NUMBER,
245 p_position_id NUMBER,
246 p_qualifying_event VARCHAR2,
247 p_qualifying_date IN OUT nocopy DATE ) IS
248 -- declare local variables
249 --
250 -- l_actual_termination_date DATE;
251 l_event_exists VARCHAR2(1) := 'N';
252 l_std_hrs NUMBER;
253 l_proc varchar2(72) := g_package || 'chk_cobra_event_eligible';
254 --
255 -- declare cursors
256 --
257 CURSOR chk_termination IS
258 SELECT 'Y',
259 pos.actual_termination_date + 1
260 FROM per_periods_of_service pos
261 WHERE pos.business_group_id + 0 = p_business_group_id
262 AND pos.person_id = p_person_id
263 AND pos.actual_termination_date IS NOT NULL
264 AND pos.actual_termination_date <= p_qualifying_date
265 ORDER BY pos.actual_termination_date DESC; --BUG1712478
266 --
267 CURSOR get_org_std_hrs IS
268 SELECT fnd_number.canonical_to_number(working_hours)
269 FROM per_organization_units ou
270 WHERE ou.organization_id = p_organization_id
271 AND ou.business_group_id + 0 = p_business_group_id
272 AND ou.date_from <=p_qualifying_date;
273 --
274 -- Changed 02-Oct-99 SCNair (per_positions to hr_positions_f) Date tracked positions requirement
275 --
276 CURSOR get_pos_std_hrs IS
277 SELECT working_hours
278 FROM hr_positions_f
279 WHERE position_id = p_position_id
280 and p_qualifying_date
281 between effective_start_date
282 and effective_end_date;
283 --
284 CURSOR chk_hrs_reduced IS
285 SELECT 'Y'
286 FROM per_assignments_f a
287 WHERE a.assignment_id = p_assignment_id
288 AND a.business_group_id + 0 = p_business_group_id
289 AND p_qualifying_date
290 BETWEEN a.effective_start_date AND
291 a.effective_end_date
292 AND ( a.normal_hours < l_std_hrs
293 OR a.normal_hours IS NULL );
294 --
295 BEGIN
296 --
297 hr_utility.set_location('Entering...:' || l_proc, 10);
298 hr_utility.trace('p_qualifying_event : ' || p_qualifying_event);
299 hr_utility.trace('p_qualifying_date : ' || p_qualifying_date);
300 --
301 --
302 -- Check to see what event we are testing for.
303 --
304 IF (p_qualifying_event = 'T')
305 THEN
306 --
307 -- check if employee has actually been terminated as of the current
308 -- session date.
309 --
310 hr_utility.set_location(l_proc, 20);
311 OPEN chk_termination;
312 FETCH chk_termination INTO l_event_exists, p_qualifying_date;
313 CLOSE chk_termination;
314 hr_utility.trace('p_qualifying_date : ' || p_qualifying_date);
315 --
316 ELSIF (p_qualifying_event = 'RH')
317 THEN
318 --
319 -- check if a position has been specofied for the assignment
320 --
321 IF ( p_position_id IS NOT NULL )
322 THEN
323 --
324 -- get position's standard hours
325 --
326 OPEN get_pos_std_hrs;
327 FETCH get_pos_std_hrs INTO l_std_hrs;
328 CLOSE get_pos_std_hrs;
329 --
330 ELSE
331 --
332 -- get organization's standard hours
333 --
334 OPEN get_org_std_hrs;
335 FETCH get_org_std_hrs INTO l_std_hrs;
336 CLOSE get_org_std_hrs;
337 --
338 END IF;
339 --
340 -- check if the employees hours have actually been reduced
341 --
342 OPEN chk_hrs_reduced;
343 FETCH chk_hrs_reduced INTO l_event_exists;
344 CLOSE chk_hrs_reduced;
345 --
346 END IF;
347 --
348 -- check flag to see if event exists
349 --
350 IF (l_event_exists = 'N')
351 THEN
352 --
353 -- chk which event and raise error
354 --
355 IF (p_qualifying_event = 'T')
356 THEN
357 hr_utility.set_message(801, 'HR_13110_COBRA_NOT_TERM');
358 hr_utility.raise_error;
359 ELSE
360 hr_utility.set_message(801, 'HR_13111_COBRA_NOT_RDCD_HRS');
361 hr_utility.raise_error;
362 END IF;
363 --
364 END IF;
365 --
366 END hr_cobra_chk_event_eligible;
367 --
368 --
369 --
370 --
371 -- Name hr_cobra_chk_benefits_exist
372 --
373 -- Purpose
374 --
375 -- Checks that an employee is currently enolled in COBRA eligible
376 -- benefit plans
377 --
378 -- Arguments
379 --
380 -- p_assignment_id NUMBER
381 -- p_qualifying_date DATE
382 --
383 -- Example
384 --
385 -- Notes
386 --
387 PROCEDURE hr_cobra_chk_benefits_exist ( p_assignment_id NUMBER,
388 p_qualifying_date DATE ) IS
389 --
390 -- declare local variables
391 --
392 l_cobra_benefits_exist VARCHAR2(1) := 'N';
393 l_last_eligible_date DATE;
394 --
395 -- declare cursors
396 --
397 CURSOR get_cobra_benefits IS
398 SELECT 'Y'
399 FROM
400 ben_benefit_classifications bc,
401 pay_element_types et,
402 pay_element_links_f el,
403 pay_element_entries_f ee
404 WHERE
405 ee.assignment_id = p_assignment_id AND
406 l_last_eligible_date BETWEEN
407 ee.effective_start_date AND ee.effective_end_date
408 AND
409 el.element_link_id = ee.element_link_id AND
410 l_last_eligible_date BETWEEN
411 el.effective_start_date AND el.effective_end_date
412 AND
413 et.element_type_id = el.element_type_id AND
414 et.processing_type = 'R'
415 AND
416 bc.benefit_classification_id = et.benefit_classification_id AND
417 bc.cobra_flag = 'Y';
418 --
419 BEGIN
420 --
421 hr_utility.set_location(g_package || 'per_cobra_eci.hr_cobra_chk_benefits_exist', 0);
422 --
423 --
424 -- Initialise last eligible date - i.e. to pick out plans
425 -- the employee was eligible for the day before qualifying
426 --
427 l_last_eligible_date := p_qualifying_date - 1;
428 --
429 --
430 -- check if employee has cobra eligible benefit plans
431 --
432 OPEN get_cobra_benefits;
433 FETCH get_cobra_benefits INTO l_cobra_benefits_exist;
434 CLOSE get_cobra_benefits;
435 --
436 -- check to se if plans exist
437 --
438 IF (l_cobra_benefits_exist = 'N')
439 THEN
440 -- raise error
441 hr_utility.set_message(801, 'HR_13112_COBRA_NO_BEN_EXIST');
442 hr_utility.raise_error;
443 --
444 END IF;
445 END hr_cobra_chk_benefits_exist;
446 --
447 --
448 --
449 -- Name hr_get_assignment_info
450 --
451 -- Purpose
452 --
453 -- gets assignment's org id
454 --
455 -- Arguments
456 --
457 -- Example
458 --
459 -- Notes
460 --
461 PROCEDURE hr_get_assignment_info (p_assignment_id NUMBER,
462 p_business_group_id NUMBER,
463 p_qualifying_date DATE,
464 p_organization_id IN OUT nocopy NUMBER,
465 p_position_id IN OUT nocopy NUMBER) IS
466 --
467 -- declare cursor
468 --
469 CURSOR org_id IS
470 SELECT organization_id,
471 position_id
472 FROM per_assignments_F a
473 WHERE a.assignment_id = p_assignment_id
474 AND a.business_group_id + 0 = p_business_group_id
475 AND p_qualifying_date
476 BETWEEN a.effective_start_date AND
477 a.effective_end_date;
478 --
479 BEGIN
480 --
481 hr_utility.set_location(g_package || 'hr_get_assignment_info', 0);
482 --
483 -- get org id
484 --
485 OPEN org_id;
486 FETCH org_id INTO p_organization_id, p_position_id;
487 CLOSE org_id;
488 --
489 END hr_get_assignment_info;
490 --
491 --
492 --
493 -- Name hr_cobra_chk_elect_status
494 --
495 -- Purpose
496 --
497 -- check to see if a status of 'ELEC' exists for the
498 -- COBRA enrollment
499 --
500 -- Arguments
501 --
502 -- p_cobra_coverage_enrollment_id NUMBER
503 --
504 -- Example
505 --
506 -- Notes
507 --
508 -- Called from client hr_cobra_chk_cov_dates_null
509 -- returns TRUE if ELECT status exists.
510 --
511 FUNCTION hr_cobra_chk_elect_status (p_cobra_coverage_enrollment_id NUMBER) RETURN BOOLEAN IS
512 --
513 -- declare local variables
514 --
515 l_elected VARCHAR2(1) := 'N';
516 --
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
526 --
527 hr_utility.set_location(g_package || 'hr_cobra_chk_elect_status', 0);
528 --
529 --
530 -- fetch status
531 --
532 OPEN elect_exists;
533 FETCH elect_exists INTO l_elected;
534 CLOSE elect_exists;
535 --
536 -- check to see if elected status exists
537 --
538 IF (l_elected = 'Y')
539 THEN
540 -- return true
541 --
542 RETURN TRUE;
543 ELSE
544 -- return FALSE to calling procedure.
545 --
546 RETURN FALSE;
547 END IF;
548 END hr_cobra_chk_elect_status;
549 --
550 --
551 --
552 -- Name hr_cobra_get_await_meaning
553 --
554 -- Purpose
555 --
556 -- gets the meaning of the statsus 'AWAIT' for initial
557 -- default of this field in the COBRA Coverage Enrollment
558 -- block. This meaning could be changed by the user.
559 --
560 -- Arguments
561 --
562 -- None
563 --
564 FUNCTION hr_cobra_get_await_meaning RETURN VARCHAR2 IS
565 --
566 -- declare local variable to hold meaning
567 --
568 l_await_meaning VARCHAR2(80);
569 --
570 -- decalre cursor to get meaning
571 --
572 CURSOR await_meaning IS
573 SELECT meaning
574 FROM hr_lookups l
575 WHERE lookup_type = 'US_COBRA_STATUS'
576 AND lookup_code = 'AWAIT';
577 --
578 BEGIN
579 --
580 hr_utility.set_location(g_package || 'per_cobra_eci.hr_cobra_get_await_meaning', 0);
581 --
582 --
583 -- get the meaning
584 --
585 OPEN await_meaning;
586 FETCH await_meaning INTO l_await_meaning;
587 CLOSE await_meaning;
588 --
589 -- return the meaning
590 --
591 RETURN l_await_meaning;
592 --
593 END hr_cobra_get_await_meaning;
594 --
595 --
596 --
597 -- Name hr_cobra_get_period_type
598 --
599 -- Purpose
600 --
601 -- Retrives default time period for payment cycle
602 --
603 -- Arguments
604 --
605 -- None
606 --
607 FUNCTION hr_cobra_get_period_type RETURN VARCHAR2 IS
608 --
609 -- declare local variables
610 --
611 l_period_type VARCHAR2(30);
612 --
613 -- declare cursor
614 --
615 CURSOR period_type IS
616 SELECT tpt.period_type
617 FROM per_time_period_types tpt
618 WHERE tpt.number_per_fiscal_year = 12
619 AND tpt.system_flag = 'Y';
620 --
621 BEGIN
622 --
623 hr_utility.set_location(g_package || 'hr_cobra_get_period_type', 0);
624 --
625 --
626 -- get default period type
627 --
628 OPEN period_type;
629 FETCH period_type INTO l_period_type;
630 CLOSE period_type;
631 --
632 -- return period type
633 --
634 RETURN l_period_type;
635 --
636 END hr_cobra_get_period_type;
637 --
638 --
639 --
640 -- Name hr_cobra_do_cce_insert
641 --
642 -- Purpose
643 --
644 -- Bundles insert calls and logic
645 --
646 -- Arguments
647 --
648 -- many, many ...
649 --
650 -- Example
651 --
652 -- Notes
653 --
654 PROCEDURE hr_cobra_do_cce_insert ( p_Rowid IN OUT nocopy VARCHAR2,
655 p_Cobra_Coverage_Enrollment_Id IN OUT nocopy NUMBER,
656 p_Business_Group_Id NUMBER,
657 p_Assignment_Id NUMBER,
658 p_Period_Type VARCHAR2,
659 p_Qualifying_Date IN OUT nocopy DATE,
660 p_Qualifying_Event VARCHAR2,
661 p_Coverage_End_Date DATE,
662 p_Coverage_Start_Date DATE,
663 p_Termination_Reason VARCHAR2,
664 p_Contact_Relationship_Id NUMBER,
665 p_Attribute_Category VARCHAR2,
666 p_Attribute1 VARCHAR2,
667 p_Attribute2 VARCHAR2,
668 p_Attribute3 VARCHAR2,
669 p_Attribute4 VARCHAR2,
670 p_Attribute5 VARCHAR2,
671 p_Attribute6 VARCHAR2,
672 p_Attribute7 VARCHAR2,
673 p_Attribute8 VARCHAR2,
674 p_Attribute9 VARCHAR2,
675 p_Attribute10 VARCHAR2,
676 p_Attribute11 VARCHAR2,
677 p_Attribute12 VARCHAR2,
678 p_Attribute13 VARCHAR2,
679 p_Attribute14 VARCHAR2,
680 p_Attribute15 VARCHAR2,
681 p_Attribute16 VARCHAR2,
682 p_Attribute17 VARCHAR2,
683 p_Attribute18 VARCHAR2,
684 p_Attribute19 VARCHAR2,
685 p_Attribute20 VARCHAR2,
686 p_Grace_Days NUMBER,
687 p_comments VARCHAR2,
688 p_organization_id NUMBER,
689 p_person_id NUMBER,
690 p_position_id NUMBER,
691 p_status VARCHAR2,
692 p_status_date DATE,
693 p_amount_charged IN OUT nocopy VARCHAR2,
694 p_first_payment_due_date DATE,
695 p_event_coverage NUMBER) IS
696 --
697 -- Declare local variables
698 --
699 l_dummy_rowid VARCHAR2(30);
700 l_cobra_coverage_status_id NUMBER(15);
701 l_amount_charged VARCHAR2(60);
702 l_package VARCHAR2(70) := g_package || 'hr_cobra_do_cce_insert';
703
704 --
705 BEGIN
706 --
707 --
708 --
709 --
710 --
711 hr_utility.set_location(l_package,1);
712 --
713 /* We will not have this validatino because we want to allow
714 end user to enter the multiple events and also the same
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,
724 p_qualifying_date );
725 */
726 --
727 -- Call check benefits exists
728 --
729 --
730 hr_utility.set_location(l_package,2);
731 --
732 hr_cobra_chk_benefits_exist ( p_assignment_id,
733 p_qualifying_date );
734 --
735 -- check eligible for event
736 --
737 --
738 hr_utility.set_location(l_package,3);
739 --
740 IF (p_qualifying_event IN ('T', 'RH'))
741 THEN
742 hr_cobra_chk_event_eligible( p_organization_id,
743 p_business_group_id,
744 p_assignment_id,
745 p_person_id,
746 p_position_id,
747 p_qualifying_event,
748 p_qualifying_date );
749 END IF;
750 --
751 -- Do insert
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,
761 p_period_type,
762 p_qualifying_date,
763 p_qualifying_event,
764 p_coverage_end_date,
765 p_coverage_start_date,
766 p_termination_reason,
767 p_contact_relationship_id,
768 p_attribute_category,
769 p_attribute1,
770 p_attribute2,
771 p_attribute3,
772 p_attribute4,
773 p_attribute5,
774 p_attribute6,
775 p_attribute7,
776 p_attribute8,
777 p_attribute9,
778 p_attribute10,
779 p_attribute11,
780 p_attribute12,
781 p_attribute13,
782 p_attribute14,
783 p_attribute15,
784 p_attribute16,
785 p_attribute17,
786 p_attribute18,
787 p_attribute19,
788 p_attribute20,
789 p_grace_days,
790 p_comments);
791 --
792 --
793 -- Insert Awaiting Notification Status
794 --
795 per_cobra_cov_statuses_pkg.insert_row(l_dummy_rowid,
796 l_Cobra_Coverage_Status_Id,
797 p_Business_Group_Id,
798 p_Cobra_Coverage_Enrollment_Id,
799 'AWAIT',
800 p_qualifying_date,
801 NULL,
802 NULL,
803 NULL,
804 NULL,
805 NULL,
806 NULL,
807 NULL,
808 NULL,
809 NULL,
810 NULL,
811 NULL,
812 NULL,
813 NULL,
814 NULL,
815 NULL,
816 NULL,
817 NULL,
818 NULL,
819 NULL,
820 NULL,
821 NULL,
822 NULL);
823 --
824 -- clear out dummy local variables
825 --
826 l_dummy_rowid := NULL;
827 l_Cobra_Coverage_Status_Id := NULL;
828 --
829 --
830 --
831 -- create cobra coverage benefits
832 --
833 hr_utility.set_location(l_package,7);
834 --
835 hr_cobra_ins_benefits(p_cobra_coverage_enrollment_id,
836 p_business_group_id,
837 p_assignment_id,
838 p_qualifying_date);
839 --
840 hr_utility.set_location(l_package,8);
841 --
842 --
843 -- Calculate amount charged
844 --
845 p_amount_charged := hr_cobra_calc_amt_charged ( p_cobra_coverage_enrollment_id,p_qualifying_date );
846
847 /* Added this to format p_amount_charged part of fix#4599753 */
848
849 p_amount_charged := hr_chkfmt.changeformat(p_amount_charged,'M','USD');
850
851 --
852 --
853 hr_utility.set_location(l_package,9);
854 --
855 -- Check to see if need to create payments
856 --
857 IF ( p_first_payment_due_date IS NOT NULL )
858 THEN
859 hr_cobra_ins_schedule( p_business_group_id,
860 p_cobra_coverage_enrollment_id,
861 p_event_coverage,
862 p_first_payment_due_date,
863 p_amount_charged,
864 p_grace_days );
865 END IF;
866 --
867 --
868 --
869 END hr_cobra_do_cce_insert;
870 --
871 --
872 --
873 -- Name hr_cobra_ins_benefits;
874 --
875 -- Purpose
876 --
877 -- Creates row in PER_COBRA_COVERAGE_BENEFITS
878 --
879 -- Arguments
880 --
881 -- p_cobra_coverage_enrollment_id
882 -- p_business_group_id
883 -- p_assignment_id
884 -- p_qualifying_date
885 --
886 -- Example
887 --
888 -- Notes
889 --
890 PROCEDURE hr_cobra_ins_benefits (p_cobra_coverage_enrollment_id NUMBER,
891 p_business_group_id NUMBER,
892 p_assignment_id NUMBER,
893 p_qualifying_date DATE) IS
894 --
895 -- declare local variables
896 --
897 l_last_eligible_date DATE := p_qualifying_date - 1;
898 l_user_id NUMBER := FND_PROFILE.Value('USER_ID');
899 l_login_id NUMBER := FND_PROFILE.Value('LOGIN_ID');
900 --
901 BEGIN
902 --
903 hr_utility.set_location(g_package || 'hr_cobra_ins_benefits', 0);
904 hr_utility.trace('last_eligible_date : ' || l_last_eligible_date);
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,
914 element_type_id,
915 business_group_id,
916 coverage_type,
917 accept_reject_flag,
918 coverage_amount,
919 last_update_date,
920 last_updated_by,
921 creation_date,
922 created_by,
923 last_update_login)
924 SELECT
925 per_cobra_coverage_benefits_s.nextval,
926 p_cobra_coverage_enrollment_id,
927 p_qualifying_date,
928 to_date('31-12-4712', 'DD-MM-YYYY'),
929 et.element_type_id,
930 p_business_group_id,
931 NVL(eev_cov.screen_entry_value, iv_cov.default_value),
932 'ACC',
933 fnd_number.number_to_canonical(
934 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))) +
935 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)))
936 ),
937 trunc(sysdate),
938 l_user_id,
939 TRUNC(sysdate),
940 l_user_id,
941 l_login_id
942 FROM
943 pay_input_values_f iv_cov,
944 pay_input_values_f iv_ee,
945 pay_input_values_f iv_er,
946 pay_element_entry_values_f eev_cov,
947 pay_element_entry_values_f eev_er,
948 pay_element_entry_values_f eev_ee,
949 ben_benefit_contributions_f bc,
950 ben_benefit_classifications bc2,
951 pay_element_types_f et,
952 pay_element_links_f el,
953 pay_element_entries_f ee
954 WHERE
955 ee.assignment_id = p_assignment_id AND
956 l_last_eligible_date
957 BETWEEN ee.effective_start_date AND ee.effective_end_Date
958 AND
959 el.element_link_id = ee.element_link_id AND
960 el.business_group_id + 0 = p_business_group_id AND
961 l_last_eligible_date
962 BETWEEN el.effective_start_date AND el.effective_end_Date
963 AND
964 et.element_type_id = el.element_type_id AND
965 et.processing_type = 'R' AND
966 l_last_eligible_date
967 BETWEEN et.effective_start_date AND et.effective_end_Date
968 AND
969 bc2.benefit_classification_id = et.benefit_classification_id AND
970 bc2.cobra_flag = 'Y'
971 AND
972 iv_cov.element_type_id = et.element_type_id AND
973 l_last_eligible_date
974 BETWEEN iv_cov.effective_start_date AND iv_cov.effective_end_date AND
975 UPPER(iv_cov.name) = 'COVERAGE'
976 AND
977 iv_er.element_type_id = et.element_type_id AND
978 l_last_eligible_date
979 BETWEEN iv_er.effective_start_date AND iv_er.effective_end_date AND
980 UPPER(iv_er.name) = 'ER CONTR'
981 AND
982 iv_ee.element_type_id = et.element_type_id AND
983 l_last_eligible_date
984 BETWEEN iv_ee.effective_start_date AND iv_ee.effective_end_Date AND
985 UPPER(iv_ee.name) = 'EE CONTR'
986 AND
987 eev_er.element_entry_id = ee.element_entry_id AND
988 eev_er.input_value_id = iv_er.input_value_id AND
989 l_last_eligible_date
990 BETWEEN eev_er.effective_start_date AND eev_er.effective_end_date
991 AND
992 eev_ee.element_entry_id = ee.element_entry_id AND
993 eev_ee.input_value_id = iv_ee.input_value_id AND
994 l_last_eligible_date
995 BETWEEN eev_ee.effective_start_date AND eev_ee.effective_end_Date
996 AND
997 eev_cov.element_entry_id = ee.element_entry_id AND
998 eev_cov.input_value_id = iv_cov.input_value_id AND
999 l_last_eligible_date
1000 BETWEEN eev_cov.effective_start_date AND eev_cov.effective_end_date
1001 AND
1002 bc.element_type_id(+) = et.element_type_id AND
1003 l_last_eligible_date
1004 BETWEEN bc.effective_start_date(+) AND bc.effective_end_date(+) AND
1005 ( bc.coverage_type = NVL(eev_cov.screen_entry_value, iv_cov.default_value)
1006 OR
1007 bc.element_type_id IS NULL
1008 );
1009 --
1010 --
1011 hr_utility.set_location(g_package || 'hr_cobra_ins_benefits', 100);
1012 --
1013 --
1014 --
1015 END hr_cobra_ins_benefits;
1016 --
1017 --
1018 --
1019 -- Name hr_cobra_calc_amt_charged
1020 --
1021 -- Purpose
1022 --
1023 -- Calculates the sum of the COBRA costs for ACCepted ben plans
1024 --
1025 -- Arguments
1026 --
1027 -- p_cobra_coverage_enrollment_id
1028 -- p_session_date
1029 --
1030 -- Example
1031 --
1032 -- Notes
1033 --
1034 FUNCTION hr_cobra_calc_amt_charged ( p_cobra_coverage_enrollment_id NUMBER,
1035 p_session_date DATE ) RETURN VARCHAR2 IS
1036 --
1037 -- declare local variables
1038 --
1039 l_amount_charged VARCHAR2(60);
1040 --
1041 -- declare cursor
1042 -- 946707: Should not have TO_CHAR or TO_NUMBER calls directly. Use the fnd_number
1043 -- procedures instead. Note: Presumes coverage amounts are stored in the canonical
1044 -- format
1045 -- SELECT TO_CHAR(sum(TO_NUMBER(ccb.coverage_amount)), 999999999990.99)
1046 --
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 --
1056 BEGIN
1057 --
1058 hr_utility.set_location(g_package || 'hr_cobra_calc_amt_charged', 0);
1059 --
1060 --
1061 -- get amount charged
1062 --
1063 OPEN amount_charged;
1064 FETCH amount_charged INTO l_amount_charged;
1065 CLOSE amount_charged;
1066 --
1067 -- return amount charged
1068 -- Added hr_chkfmt to format the value, part of fix for bug#4599753
1069 l_amount_charged := hr_chkfmt.changeformat(l_amount_charged,'M','USD');
1070
1071 RETURN l_amount_charged;
1072 --
1073 END hr_cobra_calc_amt_charged;
1074 --
1075 --
1076 --
1077 --
1078 -- Name hr_cobra_ins_schedule
1079 --
1080 -- Purpose
1081 --
1082 -- insert payment schedules into PER_SCHED_COBRA_PAYMENTS
1083 --
1084 -- Arguments
1085 --
1086 -- p_business_group_id NUMBER
1087 -- p_cobra_coverage_enrollment_id NUMBER
1088 -- p_event_coverage NUMBER
1089 -- p_first_payment_due_date DATE
1090 -- p_amount_charged NUMBER
1091 -- p_grace_days NUMBER
1092 --
1093 -- Example
1094 --
1095 -- Notes
1096 --
1097 PROCEDURE hr_cobra_ins_schedule (p_business_group_id NUMBER,
1098 p_cobra_coverage_enrollment_id NUMBER,
1099 p_event_coverage NUMBER,
1100 p_first_payment_due_date DATE,
1101 p_amount_charged NUMBER,
1102 p_grace_days NUMBER) IS
1103 --
1104 -- declare local variables
1105 --
1106 l_count NUMBER(2) := 0;
1107 l_user_id NUMBER := FND_PROFILE.Value('USER_ID');
1108 l_login_id NUMBER := FND_PROFILE.Value('LOGIN_ID');
1109 l_scp_id NUMBER;
1110 --
1111 -- declare cursor for scp_id
1112 --
1113 CURSOR get_scp_id IS
1114 SELECT per_sched_cobra_payments_s.nextval
1115 FROM dual;
1116
1117 -- Added to check existing scheduled payments Bug#4599753
1118 CURSOR check_scp IS
1119 SELECT NVL(COUNT(*),0)
1120 FROM per_sched_cobra_payments
1121 WHERE cobra_coverage_enrollment_id = p_cobra_coverage_enrollment_id
1122 AND business_group_id = p_business_group_id;
1123
1124 ln_rec_count number;
1125 --
1126 BEGIN
1127 --
1128 hr_utility.set_location(g_package || 'hr_cobra_ins_schedule', 0);
1129
1130 -- Added this check_scp rec count condition to avoid error when
1131 -- trying to save the records. Part of fix for bug#4599753
1132 ln_rec_count := 0;
1133 --
1134 open check_scp;
1135 fetch check_scp into ln_rec_count;
1136 close check_scp;
1137
1138 if ln_rec_count = 0 then
1139
1140 WHILE (l_count < (p_event_coverage)) LOOP
1141 --
1142 -- get scp id
1143 --
1144 OPEN get_scp_id;
1145 FETCH get_scp_id INTO l_scp_id;
1146 CLOSE get_scp_id;
1147 --
1148 -- insert payment schedules
1149 --
1150 INSERT INTO per_sched_cobra_payments (
1151 scheduled_cobra_payment_id,
1152 business_group_id,
1153 cobra_coverage_enrollment_id,
1154 amount_due,
1155 date_due,
1156 grace_due_date,
1157 amount_received,
1158 date_received,
1159 comments,
1160 attribute_category,
1161 attribute1,
1162 attribute2,
1163 attribute3,
1164 attribute4,
1165 attribute5,
1166 attribute6,
1167 attribute7,
1168 attribute8,
1169 attribute9,
1170 attribute10,
1171 attribute11,
1172 attribute12,
1173 attribute13,
1174 attribute14,
1175 attribute15,
1176 attribute16,
1177 attribute17,
1178 attribute18,
1179 attribute19,
1180 attribute20,
1181 last_update_date,
1182 last_updated_by,
1183 last_update_login,
1184 created_by,
1185 creation_date)
1186 SELECT
1187 l_scp_id,
1188 p_business_group_id,
1189 p_cobra_coverage_enrollment_id,
1190 fnd_number.number_to_canonical(NVL(SUM(fnd_number.canonical_to_number(coverage_amount)),'0')),
1191 ADD_MONTHS(p_first_payment_due_date, l_count),
1192 ADD_MONTHS(p_first_payment_due_date, l_count) + p_grace_days,
1193 NULL,
1194 NULL,
1195 NULL,
1196 NULL,
1197 NULL,
1198 NULL,
1199 NULL,
1200 NULL,
1201 NULL,
1202 NULL,
1203 NULL,
1204 NULL,
1205 NULL,
1206 NULL,
1207 NULL,
1208 NULL,
1209 NULL,
1210 NULL,
1211 NULL,
1212 NULL,
1213 NULL,
1214 NULL,
1215 NULL,
1216 NULL,
1217 TRUNC(sysdate),
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';
1227 --
1228 -- increment counter
1229 --
1230 l_count := l_count + 1;
1231 --
1232 --
1233 END LOOP;
1234
1235 end if; -- ln_rec_count = 0
1236 --
1237 END hr_cobra_ins_schedule;
1238
1239
1240 -- *************************************************
1241 -- SCP - Update Schedule Cobra Payments Grace Period
1242 -- *************************************************
1243 --
1244 --
1245 -- Name correct_cobra_scp_graceperiod
1246 --
1247 -- Purpose
1248 --
1249 -- updates grace_due_date in per_sched_cobra_payments table if the
1250 -- user has updated the cobra grace period.
1251 --
1252 -- Arguments
1253 --
1254 -- p_cobra_coverage_enrollment_id
1255 -- p_session_date
1256 -- p_new_grace_days
1257 --
1258 -- Example
1259 --
1260 -- Notes
1261 -- As part of fix to bug#4599753
1262 --
1263 PROCEDURE correct_cobra_scp_graceperiod( p_cobra_coverage_enrollment_id NUMBER,
1264 p_session_date DATE,
1265 p_new_grace_days NUMBER) IS
1266 BEGIN
1267 --
1268 update per_sched_cobra_payments
1269 set grace_due_date = date_due + p_new_grace_days
1270 where COBRA_COVERAGE_ENROLLMENT_ID = p_cobra_coverage_enrollment_id
1271 and grace_due_date - date_due <> p_new_grace_days
1272 and amount_received is null
1273 and date_received is null
1274 and date_due >= p_session_date;
1275
1276 --
1277 END correct_cobra_scp_graceperiod;
1278
1279
1280 --
1281 --
1282 -- Name hr_cobra_do_cce_update
1283 --
1284 -- Purpose
1285 --
1286 -- Update evwent handler - bundles update logic and parameters
1287 --
1288 -- Arguments
1289 --
1290 --
1291 -- Example
1292 --
1293 -- Notes
1294 --
1295 --
1296 --
1297 PROCEDURE hr_cobra_do_cce_update ( p_Rowid IN OUT nocopy VARCHAR2,
1298 p_Cobra_Coverage_Enrollment_Id IN OUT nocopy NUMBER,
1299 p_Business_Group_Id NUMBER,
1300 p_Assignment_Id NUMBER,
1301 p_Period_Type VARCHAR2,
1302 p_Qualifying_Date DATE,
1303 p_Qualifying_Event VARCHAR2,
1304 p_Coverage_End_Date DATE,
1305 p_Coverage_Start_Date DATE,
1306 p_Termination_Reason VARCHAR2,
1307 p_Contact_Relationship_Id NUMBER,
1308 p_Attribute_Category VARCHAR2,
1309 p_Attribute1 VARCHAR2,
1310 p_Attribute2 VARCHAR2,
1311 p_Attribute3 VARCHAR2,
1312 p_Attribute4 VARCHAR2,
1313 p_Attribute5 VARCHAR2,
1314 p_Attribute6 VARCHAR2,
1315 p_Attribute7 VARCHAR2,
1316 p_Attribute8 VARCHAR2,
1317 p_Attribute9 VARCHAR2,
1318 p_Attribute10 VARCHAR2,
1319 p_Attribute11 VARCHAR2,
1320 p_Attribute12 VARCHAR2,
1321 p_Attribute13 VARCHAR2,
1322 p_Attribute14 VARCHAR2,
1323 p_Attribute15 VARCHAR2,
1324 p_Attribute16 VARCHAR2,
1325 p_Attribute17 VARCHAR2,
1326 p_Attribute18 VARCHAR2,
1327 p_Attribute19 VARCHAR2,
1328 p_Attribute20 VARCHAR2,
1329 p_Grace_Days NUMBER,
1330 p_comments VARCHAR2,
1331 p_event_coverage NUMBER,
1332 p_session_date DATE,
1333 p_status VARCHAR2,
1334 p_status_date IN OUT nocopy DATE,
1335 p_status_meaning IN OUT nocopy VARCHAR2,
1336 p_first_payment_due_date DATE,
1337 p_old_first_payment_due_date VARCHAR2,
1338 p_amount_charged IN OUT nocopy VARCHAR2 ) IS
1339 --
1340 -- declare local variables
1341 --
1342 l_package VARCHAR2(70) := g_package || 'hr_cobra_do_cce_update';
1343
1344 -- Bug#4599753 new cursor
1345 cursor c_get_old_scp_graceperiod(cp_cov_enrollment_id number,
1346 cp_session_date date) is
1347 select SCHEDULED_COBRA_PAYMENT_ID,grace_due_date - date_due
1348 from per_sched_cobra_payments
1349 where COBRA_COVERAGE_ENROLLMENT_ID = cp_cov_enrollment_id
1350 and amount_received is null
1351 and date_received is null
1352 and date_due >= cp_session_date;
1353
1354 ln_scp_id number;
1355 ln_old_graceperiod number;
1356
1357 -- End of bug#4599753
1358
1359 --
1360 BEGIN
1361 --
1362 --
1363 hr_utility.set_location('Entering...' || l_package, 10);
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,
1373 p_qualifying_date );
1374 */
1375 --
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,
1385 p_qualifying_date,
1386 p_qualifying_event,
1387 p_coverage_end_date,
1388 p_coverage_start_date,
1389 p_termination_reason,
1390 p_contact_relationship_id,
1391 p_attribute_category,
1392 p_attribute1,
1393 p_attribute2,
1394 p_attribute3,
1395 p_attribute4,
1396 p_attribute5,
1397 p_attribute6,
1398 p_attribute7,
1399 p_attribute8,
1400 p_attribute9,
1401 p_attribute10,
1402 p_attribute11,
1403 p_attribute12,
1404 p_attribute13,
1405 p_attribute14,
1406 p_attribute15,
1407 p_attribute16,
1408 p_attribute17,
1409 p_attribute18,
1410 p_attribute19,
1411 p_attribute20,
1412 p_grace_days,
1413 p_comments);
1414 --
1415 -- check insert or update status
1416 --
1417 --
1418 hr_utility.set_location(l_package, 30);
1419 --
1420 --
1421 -- Check coverage dates
1422 --
1423 IF (p_coverage_start_date IS NULL OR p_coverage_end_date IS NULL)
1424 THEN
1425 --
1426 -- check if status of elect exists
1427 --
1428 --
1429 hr_utility.set_location(l_package, 40);
1430 --
1431 IF (hr_cobra_chk_elect_status( p_cobra_coverage_enrollment_id ))
1432 THEN
1433 --
1434 -- error
1435 --
1436 --
1437 hr_utility.set_location(l_package, 50);
1438 --
1439 hr_utility.set_message( 801, 'HR_13113_COBRA_MAND_DATES_ELEC');
1440 hr_utility.raise_error;
1441 --
1442 END IF;
1443 --
1444 END IF;
1445 hr_utility.set_location(l_package, 60);
1446 --
1447 --
1448 -- calculate amount charged
1449 --
1450 p_amount_charged := hr_cobra_calc_amt_charged( p_cobra_coverage_enrollment_id,p_session_date);
1451
1452 /* Added this to format p_amount_charged part of fix#4599753 */
1453 p_amount_charged := hr_chkfmt.changeformat(p_amount_charged,'M','USD');
1454
1455 --
1456 hr_utility.set_location(l_package, 70);
1457 -- hr_utility.trace(p_amount_charged);
1458 --
1459 --
1460 -- check if need to create payment schedules
1461 --
1462 IF ( p_old_first_payment_due_date IS NOT NULL AND
1463 p_first_payment_due_date IS NOT NULL ) THEN
1464
1465 -- Modified the validation date conversion function from
1466 -- fnd_date.canonical_to_date to fnd_date.chardate_to_date. Fix for bug#4599753
1467 IF ( fnd_date.chardate_to_date(p_old_first_payment_due_date) <> p_first_payment_due_date )
1468 THEN
1469 hr_cobra_ins_schedule( p_business_group_id,
1470 p_cobra_coverage_enrollment_id,
1471 p_event_coverage,
1472 p_first_payment_due_date,
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,
1482 p_event_coverage,
1483 p_first_payment_due_date,
1484 p_amount_charged,
1485 p_grace_days );
1486 END IF;
1487
1488 -- check if need to update sched_cobra_payment with new grace_period, bug#4599753
1489
1490 open c_get_old_scp_graceperiod(p_Cobra_Coverage_Enrollment_Id, p_session_date);
1491 fetch c_get_old_scp_graceperiod into ln_scp_id,ln_old_graceperiod;
1492 /* check if sched_cobra_payments exist or not */
1493 if c_get_old_scp_graceperiod%FOUND then
1494
1495 /* check if the old sched_cobra_payment grace_period and new grace_period
1496 is same or not, if it is not same then update the sched_cobra_payment
1497 with new grace_period */
1498
1499 if p_Grace_Days <> ln_old_graceperiod then
1500 correct_cobra_scp_graceperiod(p_Cobra_Coverage_Enrollment_Id,
1501 p_session_date,
1502 p_Grace_Days);
1503 end if;
1504
1505 end if;
1506 close c_get_old_scp_graceperiod;
1507 --
1508 --
1509 --
1510 END hr_cobra_do_cce_update;
1511
1512 -------------------------------------------------------------------------
1513 --
1514 -- Name : hr_cobra_update_elemnet
1515 --
1516 -- Purpose: Update cobra element entry when the current element entry
1517 -- is changed. BUG2974921
1518 --
1519 --------------------------------------------------------------------------
1520 function hr_cobra_update_element (
1521 p_effective_date date
1522 ,p_Cobra_Coverage_Enrollment_Id NUMBER
1523 ,p_Business_Group_Id NUMBER
1524 ,p_Assignment_Id NUMBER
1525 ,p_amount_charged in out nocopy varchar2
1526 ,p_cobra_coverage_benefit_id in out nocopy number
1527 ,p_effective_start_date in out nocopy date
1528 ,p_effective_end_date in out nocopy date
1529 ) return boolean IS
1530 --
1531 -- declare local variables
1532 --
1533 l_package VARCHAR2(70) := g_package || 'hr_cobra_update_element';
1534 l_effective_start_date date;
1535 l_effective_end_date date;
1536 l_cobra_coverage_benefit_id number;
1537 l_user_id NUMBER := FND_PROFILE.Value('USER_ID');
1538 l_login_id NUMBER := FND_PROFILE.Value('LOGIN_ID');
1539 l_element_entry_id number;
1540 l_element_type_id number;
1541 l_effective_start_date2 date;
1542 l_effective_end_date2 date;
1543 l_ccb_effective_start_date date;
1544 l_ee_effective_start_date date;
1545 l_ee_effective_end_date date;
1546 l_new_start_date date;
1547 l_return boolean := false;
1548
1549 --
1550 -- get current element entry
1551 --
1552 cursor csr_current_element_entry is
1553 select ee.element_entry_id
1554 ,ee.effective_start_date
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
1564 and ccb.effective_end_date
1565 and ccb.element_type_id = el.element_type_id
1566 and p_effective_date between el.effective_start_date
1567 and el.effective_end_date
1568 and el.business_group_id = p_business_group_id
1569 and el.element_link_id = ee.element_link_id
1570 and ee.assignment_id = p_assignment_id;
1571
1572 --
1573 -- get available element entry
1574 --
1575 cursor csr_available_element_entry is
1576 select et.element_type_id
1577 ,ee.effective_start_date
1578 ,ee.effective_end_date
1579 from pay_element_entries_f ee
1580 ,pay_element_links_f el
1581 ,pay_element_types_f et
1582 ,pay_element_classifications ec
1583 ,ben_benefit_classifications bc
1584 where ee.assignment_id = p_assignment_id
1585 and p_effective_date between ee.effective_start_date
1586 and ee.effective_end_date
1587 and el.element_link_id = ee.element_link_id
1588 and el.business_group_id = p_business_group_id
1589 and p_effective_date between el.effective_start_date
1590 and el.effective_end_date
1591 and el.element_type_id = et.element_type_id
1592 and p_effective_date between et.effective_start_date
1593 and et.effective_end_date
1594 and et.classification_id = ec.classification_id
1595 and ec.legislation_code = 'US'
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
1605 and p_effective_date between ccbf.effective_start_date
1606 and ccbf.effective_end_date
1607 and ccbf.business_group_id = p_business_group_id);
1608 --
1609 CURSOR get_benefit_id IS
1610 SELECT per_cobra_coverage_benefits_s.nextval
1611 FROM dual;
1612
1613 BEGIN
1614
1615 hr_utility.set_location('Entering...' || l_package, 10);
1616 hr_utility.trace('p_effective_date : ' || p_effective_date);
1617 --
1618 open csr_current_element_entry;
1619 LOOP
1620 fetch csr_current_element_entry into l_element_entry_id
1621 ,l_ee_effective_start_date, l_ee_effective_end_date
1622 ,l_element_type_id
1623 ,l_cobra_coverage_benefit_id
1624 ,l_ccb_effective_start_date;
1625 exit when csr_current_element_entry%NOTFOUND;
1626
1627 hr_utility.set_location(l_package, 20);
1628 hr_utility.trace('l_element_entry_id : ' || l_element_entry_id);
1629 hr_utility.trace('l_ee_effective_start_date : ' || l_ee_effective_start_date);
1630 hr_utility.trace('l_ee_effective_end_date : ' || l_ee_effective_end_date);
1631 hr_utility.trace('l_element_type_id : ' || l_element_type_id);
1632 hr_utility.trace('l_ccb_effective_start_date : ' || l_ccb_effective_start_date);
1633
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')
1643 and cobra_coverage_enrollment_id = p_cobra_coverage_enrollment_id
1644 and business_group_id = p_business_group_id
1645 and element_type_id = l_element_type_id;
1646 --
1647 l_return := true;
1648 --
1649 p_effective_end_date := l_ee_effective_end_date;
1650 end if;
1651
1652 END LOOP;
1653 close csr_current_element_entry;
1654
1655 hr_utility.set_location(l_package, 40);
1656 --
1657 --
1658 --
1659 open csr_available_element_entry;
1660 LOOP
1661 fetch csr_available_element_entry into l_element_type_id
1662 ,l_effective_start_date2, l_effective_end_date2;
1663 exit when csr_available_element_entry%NOTFOUND;
1664
1665 hr_utility.set_location(l_package, 50);
1666 hr_utility.trace('l_element_type_id : ' || l_element_type_id);
1667 hr_utility.trace('l_effective_start_date2 : ' || l_effective_start_date2);
1668 hr_utility.trace('l_effective_end_date2 : ' || l_effective_end_date2);
1669
1670 l_new_start_date := l_effective_start_date2;
1671
1672 open get_benefit_id;
1673 fetch get_benefit_id into l_cobra_coverage_benefit_id;
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,
1683 element_type_id,
1684 business_group_id,
1685 coverage_type,
1686 accept_reject_flag,
1687 coverage_amount,
1688 last_update_date,
1689 last_updated_by,
1690 creation_date,
1691 created_by,
1692 last_update_login)
1693 SELECT
1694 l_cobra_coverage_benefit_id,
1695 p_cobra_coverage_enrollment_id,
1696 l_new_start_date,
1697 -- to_date('31-12-4712','DD-MM-YYYY'),
1698 l_effective_end_date2,
1699 et.element_type_id,
1700 p_business_group_id,
1701 NVL(eev_cov.screen_entry_value, iv_cov.default_value),
1702 'ACC',
1703 fnd_number.number_to_canonical(
1704 NVL(fnd_number.canonical_to_number(eev_er.screen_entry_value),
1705 NVL(fnd_number.canonical_to_number(bc.employer_contribution),
1706 fnd_number.canonical_to_number(iv_er.default_value))) +
1707 NVL(fnd_number.canonical_to_number(eev_ee.screen_entry_value),
1708 NVL(fnd_number.canonical_to_number(bc.employee_contribution),
1709 fnd_number.canonical_to_number(iv_ee.default_value)))),
1710 trunc(sysdate),
1711 l_user_id,
1712 TRUNC(sysdate),
1713 l_user_id,
1714 l_login_id
1715 from pay_input_values_f iv_cov,
1716 pay_input_values_f iv_ee,
1717 pay_input_values_f iv_er,
1718 pay_element_entry_values_f eev_cov,
1719 pay_element_entry_values_f eev_er,
1720 pay_element_entry_values_f eev_ee,
1721 ben_benefit_contributions_f bc,
1722 ben_benefit_classifications bc2,
1723 pay_element_types_f et,
1724 pay_element_links_f el,
1725 pay_element_entries_f ee
1726 WHERE
1727 ee.assignment_id = p_assignment_id
1728 AND
1729 et.element_type_id = l_element_type_id
1730 AND
1731 l_new_start_date
1732 BETWEEN ee.effective_start_date AND ee.effective_end_Date
1733 AND
1734 el.element_link_id = ee.element_link_id AND
1735 el.business_group_id + 0 = p_business_group_id AND
1736 -- p_qualifying_date
1737 l_new_start_date
1738 BETWEEN el.effective_start_date AND el.effective_end_Date
1739 AND
1740 et.element_type_id = el.element_type_id AND
1741 et.processing_type = 'R' AND
1742 -- p_qualifying_date
1743 l_new_start_date
1744 BETWEEN et.effective_start_date AND et.effective_end_Date
1745 AND
1746 bc2.benefit_classification_id = et.benefit_classification_id AND
1747 bc2.cobra_flag = 'Y'
1748 AND
1749 iv_cov.element_type_id = et.element_type_id AND
1750 -- p_qualifying_date
1751 l_new_start_date
1752 BETWEEN iv_cov.effective_start_date AND iv_cov.effective_end_date AND
1753 UPPER(iv_cov.name) = 'COVERAGE'
1754 AND
1755 iv_er.element_type_id = et.element_type_id AND
1756 -- p_qualifying_date
1757 l_new_start_date
1758 BETWEEN iv_er.effective_start_date AND iv_er.effective_end_date AND
1759 UPPER(iv_er.name) = 'ER CONTR'
1760 AND
1761 iv_ee.element_type_id = et.element_type_id AND
1762 -- p_qualifying_date
1763 l_new_start_date
1764 BETWEEN iv_ee.effective_start_date AND iv_ee.effective_end_Date AND
1765 UPPER(iv_ee.name) = 'EE CONTR'
1766 AND
1767 eev_er.element_entry_id = ee.element_entry_id AND
1768 eev_er.input_value_id = iv_er.input_value_id AND
1769 -- p_qualifying_date
1770 l_new_start_date
1771 BETWEEN eev_er.effective_start_date AND eev_er.effective_end_date
1772 AND
1773 eev_ee.element_entry_id = ee.element_entry_id AND
1774 eev_ee.input_value_id = iv_ee.input_value_id AND
1775 --p_qualifying_date
1776 l_new_start_date
1777 BETWEEN eev_ee.effective_start_date AND eev_ee.effective_end_Date
1778 AND
1779 eev_cov.element_entry_id = ee.element_entry_id AND
1780 eev_cov.input_value_id = iv_cov.input_value_id AND
1781 --p_qualifying_date
1782 l_new_start_date
1783 BETWEEN eev_cov.effective_start_date AND eev_cov.effective_end_date
1784 AND
1785 bc.element_type_id(+) = et.element_type_id AND
1786 --p_qualifying_date
1787 l_new_start_date
1788 BETWEEN bc.effective_start_date(+) AND bc.effective_end_date(+) AND
1789 ( bc.coverage_type = NVL(eev_cov.screen_entry_value, iv_cov.default_value)
1790 OR
1791 bc.element_type_id IS NULL
1792 );
1793
1794 hr_utility.set_location(l_package, 70);
1795 l_return := true;
1796 END LOOP;
1797 close csr_available_element_entry;
1798
1799 hr_utility.set_location(l_package, 80);
1800
1801 if (l_return = true) then
1802 hr_utility.set_location(l_package, 90);
1803 --
1804 -- calculate amount charged
1805 --
1806 p_amount_charged :=
1807 hr_cobra_calc_amt_charged( p_cobra_coverage_enrollment_id,p_effective_date );
1808
1809 p_cobra_coverage_benefit_id := l_cobra_coverage_benefit_id;
1810 p_effective_start_date := l_new_start_date;
1811 p_effective_end_date := l_effective_end_date2;
1812 -- commit;
1813 end if;
1814
1815 hr_utility.set_location(l_package, 100);
1816
1817 return l_return;
1818 --
1819 --
1820 END hr_cobra_update_element;
1821 --
1822 --
1823 --
1824 -- ************************************
1825 -- CCS - Cobra Coverage Statuses Stuff
1826 -- ************************************
1827 --
1828 --
1829 --
1830 --
1831 --
1832 --
1833 -- Name hr_cobra_get_current_status
1834 --
1835 -- Purpose
1836 --
1837 -- gets the latest cobra status
1838 --
1839 -- Arguments
1840 --
1841 -- Example
1842 --
1843 -- Notes
1844 --
1845 --
1846 --
1847 PROCEDURE hr_cobra_get_current_status ( p_cobra_coverage_enrollment_id NUMBER,
1848 p_session_date DATE,
1849 p_status IN OUT nocopy VARCHAR2,
1850 p_status_meaning IN OUT nocopy VARCHAR2,
1851 p_status_date IN OUT nocopy DATE,
1852 p_d_status_date IN OUT nocopy DATE ) IS
1853 --
1854 -- declare local variables
1855 --
1856 l_status VARCHAR2(30);
1857 l_status_meaning VARCHAR2(80);
1858 l_status_date DATE;
1859 --
1860 -- declare cursor
1861 --
1862 CURSOR latest_status_info IS
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
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';
1880 --
1881 BEGIN
1882 --
1883 hr_utility.set_location(g_package || 'hr_cobra_get_current_status', 0);
1884 --
1885 --
1886 -- get latest status info
1887 --
1888 OPEN latest_status_info;
1889 FETCH latest_status_info INTO l_status, l_status_date, l_status_meaning;
1890 CLOSE latest_status_info;
1891 --
1892 -- set outgoing variables
1893 --
1894 p_status_meaning := l_status_meaning;
1895 p_status := l_status;
1896 p_status_date := l_status_date;
1897 p_d_status_date := l_status_date;
1898 --
1899 --
1900 END hr_cobra_get_current_status;
1901 --
1902 --
1903 --
1904 -- Name hr_cobra_do_ccs_insert
1905 --
1906 -- Purpose
1907 --
1908 -- insert bundle
1909 --
1910 -- Arguments
1911 --
1912 --
1913 PROCEDURE hr_cobra_do_ccs_insert ( p_Rowid IN OUT nocopy VARCHAR2,
1914 p_Cobra_Coverage_Status_Id IN OUT nocopy NUMBER,
1915 p_Business_Group_Id NUMBER,
1916 p_Cobra_Coverage_Enrollment_Id NUMBER,
1917 p_Cobra_Coverage_Status_Type VARCHAR2,
1918 p_Effective_Date DATE,
1919 p_current_status IN OUT nocopy VARCHAR2,
1920 p_current_status_meaning IN OUT nocopy VARCHAR2,
1921 p_current_status_date IN OUT nocopy DATE,
1922 p_current_d_status_date IN OUT nocopy DATE,
1923 p_Attribute_Category VARCHAR2,
1924 p_Attribute1 VARCHAR2,
1925 p_Attribute2 VARCHAR2,
1926 p_Attribute3 VARCHAR2,
1927 p_Attribute4 VARCHAR2,
1928 p_Attribute5 VARCHAR2,
1929 p_Attribute6 VARCHAR2,
1930 p_Attribute7 VARCHAR2,
1931 p_Attribute8 VARCHAR2,
1932 p_Attribute9 VARCHAR2,
1933 p_Attribute10 VARCHAR2,
1934 p_Attribute11 VARCHAR2,
1935 p_Attribute12 VARCHAR2,
1936 p_Attribute13 VARCHAR2,
1937 p_Attribute14 VARCHAR2,
1938 p_Attribute15 VARCHAR2,
1939 p_Attribute16 VARCHAR2,
1940 p_Attribute17 VARCHAR2,
1941 p_Attribute18 VARCHAR2,
1942 p_Attribute19 VARCHAR2,
1943 p_Attribute20 VARCHAR2,
1944 p_comments VARCHAR2,
1945 p_session_date DATE ) IS
1946 --
1947 -- Declare local variable
1948 --
1949 l_package VARCHAR2(70) := g_package || 'hr_cobra_do_ccs_insert';
1950 --
1951
1952
1953 BEGIN
1954 --
1955 --
1956 hr_utility.set_location(l_package, 1);
1957 --
1958 --
1959 -- Call to check status is unique
1960 --
1961 per_cobra_cov_statuses_pkg.hr_cobra_chk_status_unique
1962 ( p_business_group_id,
1963 p_cobra_coverage_status_id,
1964 p_cobra_coverage_enrollment_id,
1965 p_cobra_coverage_status_type );
1966 --
1967 hr_utility.set_location(l_package, 2);
1968 --
1969 --
1970 -- Call to check elect/rej not co-existing
1971 --
1972 hr_utility.trace('p_cobra_coverage_status_type = ' || p_cobra_coverage_status_type);
1973 IF (p_cobra_coverage_status_type IN ('ELEC', 'REJ'))
1974 THEN
1975 per_cobra_cov_statuses_pkg.hr_cobra_chk_status_elect_rej
1976 ( p_business_group_id,
1977 p_cobra_coverage_enrollment_id,
1978 p_cobra_coverage_status_id,
1979 p_cobra_coverage_status_type );
1980 END IF;
1981 --
1982 hr_utility.set_location(l_package, 3);
1983 --
1984 --
1985 -- Call to check status inserted in correct order
1986 --
1987 per_cobra_cov_statuses_pkg.hr_cobra_chk_status_order
1988 ( p_business_group_id,
1989 p_cobra_coverage_enrollment_id,
1990 p_cobra_coverage_status_id,
1991 p_cobra_coverage_status_type,
1992 p_effective_date );
1993 --
1994 hr_utility.set_location(l_package, 4);
1995 --
1996 --
1997 -- do insert
1998 --
1999 per_cobra_cov_statuses_pkg.insert_row
2000 ( p_Rowid,
2001 p_Cobra_Coverage_Status_Id,
2002 p_Business_Group_Id,
2003 p_Cobra_Coverage_Enrollment_Id,
2004 p_Cobra_Coverage_Status_Type,
2005 p_Effective_Date,
2006 p_Attribute_Category,
2007 p_Attribute1,
2008 p_Attribute2,
2009 p_Attribute3,
2010 p_Attribute4,
2011 p_Attribute5,
2012 p_Attribute6,
2013 p_Attribute7,
2014 p_Attribute8,
2015 p_Attribute9,
2016 p_Attribute10,
2017 p_Attribute11,
2018 p_Attribute12,
2019 p_Attribute13,
2020 p_Attribute14,
2021 p_Attribute15,
2022 p_Attribute16,
2023 p_Attribute17,
2024 p_Attribute18,
2025 p_Attribute19,
2026 p_Attribute20,
2027 p_comments );
2028 --
2029 hr_utility.set_location(l_package, 5);
2030 --
2031 --
2032 -- get the current status
2033 --
2034 hr_cobra_get_current_status ( p_cobra_coverage_enrollment_id,
2035 p_session_date,
2036 p_current_status,
2037 p_current_status_meaning,
2038 p_current_status_date,
2039 p_current_d_status_date );
2040 --
2041 hr_utility.set_location(l_package, 6);
2042 --
2043 --
2044 END hr_cobra_do_ccs_insert;
2045 --
2046 --
2047 --
2048 -- Name hr_cobra_do_ccs_update
2049 --
2050 -- Purpose
2051 --
2052 -- update bundle
2053 --
2054 -- Arguments
2055 --
2056 PROCEDURE hr_cobra_do_ccs_update ( p_Rowid IN OUT nocopy VARCHAR2,
2057 p_Cobra_Coverage_Status_Id IN OUT nocopy NUMBER,
2058 p_Business_Group_Id NUMBER,
2059 p_Cobra_Coverage_Enrollment_Id NUMBER,
2060 p_Cobra_Coverage_Status_Type VARCHAR2,
2061 p_Effective_Date DATE,
2062 p_current_status IN OUT nocopy VARCHAR2,
2063 p_current_status_meaning IN OUT nocopy VARCHAR2,
2064 p_current_status_date IN OUT nocopy DATE,
2065 p_current_d_status_date IN OUT nocopy DATE,
2066 p_Attribute_Category VARCHAR2,
2067 p_Attribute1 VARCHAR2,
2068 p_Attribute2 VARCHAR2,
2069 p_Attribute3 VARCHAR2,
2070 p_Attribute4 VARCHAR2,
2071 p_Attribute5 VARCHAR2,
2072 p_Attribute6 VARCHAR2,
2073 p_Attribute7 VARCHAR2,
2074 p_Attribute8 VARCHAR2,
2075 p_Attribute9 VARCHAR2,
2076 p_Attribute10 VARCHAR2,
2077 p_Attribute11 VARCHAR2,
2078 p_Attribute12 VARCHAR2,
2079 p_Attribute13 VARCHAR2,
2080 p_Attribute14 VARCHAR2,
2081 p_Attribute15 VARCHAR2,
2082 p_Attribute16 VARCHAR2,
2083 p_Attribute17 VARCHAR2,
2084 p_Attribute18 VARCHAR2,
2085 p_Attribute19 VARCHAR2,
2086 p_Attribute20 VARCHAR2,
2087 p_comments VARCHAR2,
2088 p_session_date DATE ) IS
2089 --
2090 -- declare local variables
2091 --
2092 l_package VARCHAR2(70) := g_package || 'hr_cobra_do_ccs_update';
2093 --
2094
2095 BEGIN
2096 --
2097 --
2098 --
2099 hr_utility.set_location(l_package, 1);
2100 --
2101 -- Call to check status is unique
2102 --
2103 per_cobra_cov_statuses_pkg.hr_cobra_chk_status_unique
2104 ( p_business_group_id,
2105 p_cobra_coverage_status_id,
2106 p_cobra_coverage_enrollment_id,
2107 p_cobra_coverage_status_type );
2108 --
2109 --
2110 --
2111 hr_utility.set_location(l_package, 2);
2112 --
2113 -- before inserting new status check that elct/reject to not coexist
2114 --
2115 IF( p_cobra_coverage_status_type IN ('ELEC', 'REJ'))
2116 THEN
2117 --
2118 hr_utility.set_location(l_package, 3);
2119 per_cobra_cov_statuses_pkg.hr_cobra_chk_status_elect_rej (
2120 p_business_group_id,
2121 p_cobra_coverage_enrollment_id,
2122 p_cobra_coverage_status_id,
2123 p_cobra_coverage_status_type );
2124 END IF;
2125 --
2126 hr_utility.set_location(l_package, 4);
2127 --
2128 --
2129 -- check status order
2130 --
2131 per_cobra_cov_statuses_pkg.hr_cobra_chk_status_order(
2132 p_business_group_id,
2133 p_cobra_coverage_enrollment_id,
2134 p_cobra_coverage_status_id,
2135 p_cobra_coverage_status_type,
2136 p_effective_date );
2137 --
2138 --
2139 -- update status
2140 --
2141 --
2142 hr_utility.set_location(l_package, 5);
2143 --
2144 per_cobra_cov_statuses_pkg.update_row(p_rowid,
2145 p_Business_Group_Id,
2146 p_Cobra_Coverage_Enrollment_Id,
2147 p_Cobra_Coverage_Status_Type,
2148 p_Effective_Date,
2149 p_Attribute_Category,
2150 p_Attribute1,
2151 p_Attribute2,
2152 p_Attribute3,
2153 p_Attribute4,
2154 p_Attribute5,
2155 p_Attribute6,
2156 p_Attribute7,
2157 p_Attribute8,
2158 p_Attribute9,
2159 p_Attribute10,
2160 p_Attribute11,
2161 p_Attribute12,
2162 p_Attribute13,
2163 p_Attribute14,
2164 p_Attribute15,
2165 p_Attribute16,
2166 p_Attribute17,
2167 p_Attribute18,
2168 p_Attribute19,
2169 p_Attribute20,
2170 p_comments );
2171 --
2172 -- get the current status
2173 --
2174 hr_cobra_get_current_status ( p_cobra_coverage_enrollment_id,
2175 p_session_date,
2176 p_current_status_meaning,
2177 p_current_status,
2178 p_current_status_date,
2179 p_current_d_status_date );
2180 --
2181 --
2182 --
2183 END hr_cobra_do_ccs_update;
2184 --
2185 -- Name hr_cobra_term_enroll
2186 --
2187 -- Purpose
2188 -- Update COBRA Coverage End date with Session date when Cobra
2189 -- event is terminated. Part of fix for bug#4599753
2190 --
2191 -- Arguments
2192 --
2193 -- p_business_group_id NUMBER
2194 -- p_cobra_coverage_enrollment_id NUMBER
2195 -- p_session_date DATE
2196 -- p_qualifying_event VARCHAR2
2197 -- p_coverage_start_date DATE
2198 -- p_coverage_end_date DATE
2199 --
2200 PROCEDURE hr_cobra_term_enroll( p_business_group_id NUMBER,
2201 p_cobra_coverage_enrollment_id NUMBER,
2202 p_session_date DATE,
2203 p_qualifying_event VARCHAR2,
2204 p_coverage_start_date DATE,
2205 p_coverage_end_date IN OUT nocopy DATE) IS
2206
2207 ld_session_date date;
2208
2209 BEGIN
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
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
2226 END hr_cobra_term_enroll;
2227
2228
2229 -- Name hr_cobra_term_dependents
2230 --
2231 -- Purpose
2232 -- Update COBRA Coverage Dependents Effective_End_date with Session date
2233 -- when Cobra event is terminated. Part of fix for bug#4599753
2234 --
2235 -- Arguments
2236 --
2237 -- p_business_group_id NUMBER
2238 -- p_cobra_coverage_enrollment_id NUMBER
2239 -- p_session_date DATE
2240 -- p_qualifying_event VARCHAR2
2241 -- p_coverage_start_date DATE
2242 -- p_coverage_end_date DATE
2243 --
2244 PROCEDURE hr_cobra_term_dependents( p_business_group_id NUMBER,
2245 p_cobra_coverage_enrollment_id NUMBER,
2246 p_session_date DATE,
2247 p_qualifying_event VARCHAR2,
2248 p_coverage_start_date DATE,
2249 p_coverage_end_date DATE) IS
2250
2251 ld_session_date date;
2252 ln_ovn number;
2253 ln_cdp_id number;
2254 ld_eff_end_date date;
2255 ld_eff_start_date date;
2256
2257 cursor c_get_cobra_dependents IS
2258 select cobra_dependent_id,effective_end_date,object_version_number,effective_start_date
2259 from per_cobra_dependents_f
2260 where cobra_coverage_enrollment_id = p_cobra_coverage_enrollment_id
2261 and p_session_date between effective_start_date and effective_end_date;
2262
2263
2264 BEGIN
2265
2266 ld_session_date := p_session_date;
2267 hr_utility.trace('p_status TERM satisfied');
2268
2269 open c_get_cobra_dependents;
2270 loop
2271 fetch c_get_cobra_dependents into ln_cdp_id,ld_eff_end_date, ln_ovn,ld_eff_start_date;
2272 exit when c_get_cobra_dependents%NOTFOUND;
2273
2274 if c_get_cobra_dependents%FOUND then
2275
2276 update per_cobra_dependents_f
2277 set effective_end_date = ld_session_date,
2278 object_version_number = ln_ovn + 1
2279 where cobra_dependent_id = ln_cdp_id
2280 and effective_start_date = ld_eff_start_date
2281 and effective_end_date = ld_eff_end_date;
2282
2283 end if;
2284
2285 end loop;
2286 close c_get_cobra_dependents;
2287
2288 --
2289
2290 END hr_cobra_term_dependents;
2291
2292
2293
2294 -- Name hr_cobra_button_status
2295 --
2296
2297 -- Name hr_cobra_button_status
2298 --
2299 -- Purpose
2300 --
2301 -- Inserts COBRA Coverage Status according to which button the
2302 -- user presses
2303 --
2304 -- Arguments
2305 --
2306 -- p_business_group_id NUMBER
2307 -- p_cobra_coverage_enrollment_id NUMBER
2308 -- p_status VARCHAR2
2309 -- p_cce_status IN OUT VARCHAR2
2310 -- p_status_date DATE
2311 -- p_d_status_date DATE
2312 -- p_status_meaning VARCHAR2
2313 --
2314 PROCEDURE hr_cobra_button_status ( p_business_group_id NUMBER,
2315 p_cobra_coverage_enrollment_id NUMBER,
2316 p_session_date DATE,
2317 p_status VARCHAR2,
2318 p_cce_status IN OUT nocopy VARCHAR2,
2319 p_status_date IN OUT nocopy DATE,
2320 p_d_status_date IN OUT nocopy DATE,
2321 p_status_meaning IN OUT nocopy VARCHAR2 ) IS
2322 --
2323 -- declare local variables
2324 --
2325 p_dummy_rowid VARCHAR2(30);
2326 p_dummy_id NUMBER(15);
2327 l_package VARCHAR2(70) := g_package || 'hr_cobra_button_status';
2328 --
2329 BEGIN
2330 --
2331 --
2332 hr_utility.set_location(l_package, 0);
2333 --
2334 --
2335 -- Call to check status is unique
2336 --
2337 per_cobra_cov_statuses_pkg.hr_cobra_chk_status_unique
2338 ( p_business_group_id,
2339 NULL,
2340 p_cobra_coverage_enrollment_id,
2341 p_status );
2342 --
2343 hr_utility.set_location(l_package, 1);
2344 --
2345 --
2346 -- Call to check elect/rej not co-existing
2347 --
2348 IF (p_status IN ('ELEC', 'REJ'))
2349 THEN
2350 per_cobra_cov_statuses_pkg.hr_cobra_chk_status_elect_rej
2351 ( p_business_group_id,
2352 p_cobra_coverage_enrollment_id,
2353 NULL,
2354 p_status );
2355 END IF;
2356 --
2357 hr_utility.set_location(l_package, 2);
2358 --
2359 --
2360 -- Call to check status inserted in correct order
2361 --
2362 per_cobra_cov_statuses_pkg.hr_cobra_chk_status_order
2363 ( p_business_group_id,
2364 p_cobra_coverage_enrollment_id,
2365 NULL,
2366 p_status,
2367 p_status_date);
2368 --
2369 hr_utility.set_location(l_package, 3);
2370 --
2371 --
2372 -- do insert
2373 --
2374 per_cobra_cov_statuses_pkg.insert_row
2375 ( p_dummy_rowid,
2376 p_dummy_Id,
2377 p_Business_Group_Id,
2378 p_Cobra_Coverage_Enrollment_Id,
2379 p_Status,
2380 p_status_Date,
2381 NULL,
2382 NULL,
2383 NULL,
2384 NULL,
2385 NULL,
2386 NULL,
2387 NULL,
2388 NULL,
2389 NULL,
2390 NULL,
2391 NULL,
2392 NULL,
2393 NULL,
2394 NULL,
2395 NULL,
2396 NULL,
2397 NULL,
2398 NULL,
2399 NULL,
2400 NULL,
2401 NULL,
2402 NULL);
2403 --
2404 hr_utility.set_location(l_package, 4);
2405 --
2406 --
2407 hr_utility.trace('checking new code for TERM');
2408
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
2418 hr_utility.trace('Update to per_cobra_cov_enrollments done ');
2419 end if;
2420 */
2421 --
2422 --
2423 -- get current status
2424 --
2425 hr_cobra_get_current_status ( p_cobra_coverage_enrollment_id,
2426 p_session_date,
2427 p_cce_status,
2428 p_status_meaning,
2429 p_status_date,
2430 p_d_status_date );
2431 --
2432 hr_utility.set_location(l_package, 5);
2433
2434 --
2435 --
2436 --
2437 END hr_cobra_button_status;
2438 --
2439 --
2440 --
2441 -- Name hr_cobra_do_ccs_delete
2442 --
2443 -- Purpose
2444 --
2445 -- delete bundle
2446 --
2447 -- Arguments
2448 --
2449 PROCEDURE hr_cobra_do_ccs_delete ( p_Rowid VARCHAR2,
2450 p_cobra_coverage_enrollment_id NUMBER,
2451 p_session_date DATE,
2452 p_status IN OUT nocopy VARCHAR2,
2453 p_status_meaning IN OUT nocopy VARCHAR2,
2454 p_status_date IN OUT nocopy DATE,
2455 p_d_status_date IN OUT nocopy DATE ) IS
2456 BEGIN
2457 --
2458 -- delete row
2459 --
2460 per_cobra_cov_statuses_pkg.Delete_Row( p_rowid );
2461 --
2462 -- get current status
2463 --
2464 hr_cobra_get_current_status ( p_cobra_coverage_enrollment_id,
2465 p_session_date,
2466 p_status,
2467 p_status_meaning,
2468 p_status_date,
2469 p_d_status_date );
2470 --
2471 --
2472 --
2473 END hr_cobra_do_ccs_delete;
2474 --
2475 --
2476 --
2477 -- ************************************
2478 -- SCP - Schedule COBRA Payments Stuff
2479 -- ************************************
2480 --
2481 --
2482 --
2483 -- Name hr_cobra_chk_dup_pay_due_date
2484 --
2485 -- Purpose
2486 --
2487 -- ensure that duplicate due dates are not entered.
2488 --
2489 -- Arguments
2490 --
2491 -- p_scheduled_cobra_payment_id NUMBER
2492 -- p_cobra_coverage_enrollment_id NUMBER
2493 -- p_due_date DATE
2494 --
2495 -- Example
2496 --
2497 -- Notes
2498 --
2499 PROCEDURE hr_cobra_chk_dup_pay_due_date ( p_scheduled_cobra_payment_id NUMBER,
2500 p_cobra_coverage_enrollment_id NUMBER,
2501 p_due_date DATE ) IS
2502 --
2503 -- declare local variables
2504 --
2505 l_duplicate_due_date VARCHAR2(1) := 'N';
2506 --
2507 -- declare cursor
2508 --
2509 CURSOR due_date IS
2510 SELECT 'Y'
2511 FROM per_sched_cobra_payments scp
2512 WHERE scp.cobra_coverage_enrollment_id = p_cobra_coverage_enrollment_id
2513 AND ( scp.scheduled_cobra_payment_id <> p_scheduled_cobra_payment_id
2514 OR p_scheduled_cobra_payment_id IS NULL
2515 )
2516 AND scp.date_due = p_due_date;
2517
2518 l_package VARCHAR2(70) := g_package || 'hr_cobra_chk_dup_pay_due_date';
2519 --
2520 BEGIN
2521 --
2522 hr_utility.set_location(l_package, 0);
2523 --
2524 --
2525 -- get duplicate due dates
2526 --
2527 OPEN due_date;
2528 FETCH due_date INTO l_duplicate_due_date;
2529 CLOSE due_date;
2530 --
2531 hr_utility.set_location(l_package, 1);
2532 --
2533 --
2534 -- chk duplicate due dates
2535 --
2536 IF (l_duplicate_due_date = 'Y')
2537 THEN
2538 --
2539 hr_utility.set_location(l_package, 2);
2540 --
2541 hr_utility.set_message( 801, 'HR_13145_COBRA_DUP_SCHED');
2542 hr_utility.raise_error;
2543 --
2544 END IF;
2545 --
2546 hr_utility.set_location(l_package, 3);
2547 --
2548 --
2549 END hr_cobra_chk_dup_pay_due_date;
2550 --
2551 -- Name hr_cobra_do_scp_pre_insert
2552 --
2553 -- Purpose
2554 --
2555 -- Bundles pre-insert logic to server
2556 --
2557 -- Arguments
2558 --
2559 -- p_scheduled_cobra_payment_id NUMBER
2560 -- p_cobra_coverage_enrollment_id NUMBER
2561 -- p_due_date DATE
2562 --
2563 -- Example
2564 --
2565 -- Notes
2566 --
2567 PROCEDURE hr_cobra_do_scp_pre_insert ( p_scheduled_cobra_payment_id IN OUT nocopy NUMBER,
2568 p_cobra_coverage_enrollment_id NUMBER,
2569 p_due_date DATE ) IS
2570 --
2571 -- declare local variables
2572 --
2573 --
2574 -- declare cursor
2575 --
2576 CURSOR scp_id IS
2577 SELECT per_sched_cobra_payments_s.nextval
2578 FROM sys.dual;
2579
2580 l_package VARCHAR2(70) := g_package || 'hr_cobra_do_scp_insert';
2581 --
2582 BEGIN
2583 --
2584 --
2585 -- chekc for duplicate due date
2586 --
2587 --
2588 hr_utility.set_location(l_package, 1);
2589 --
2590 hr_cobra_chk_dup_pay_due_date( p_scheduled_cobra_payment_id,
2591 p_cobra_coverage_enrollment_id,
2592 p_due_date );
2593 --
2594 hr_utility.set_location(l_package, 2);
2595 --
2596 --
2597 -- get new scp_id
2598 --
2599 OPEN scp_id;
2600 FETCH scp_id INTO p_scheduled_cobra_payment_id;
2601 CLOSE scp_id;
2602 --
2603 hr_utility.set_location(l_package, 3);
2604 --
2605 END hr_cobra_do_scp_pre_insert;
2606 --
2607 --
2608 --
2609 -- Name hr_cobra_do_scp_pre_update
2610 --
2611 -- Purpose
2612 --
2613 -- Bundles pre-update logic to server
2614 --
2615 -- Arguments
2616 --
2617 -- p_scheduled_cobra_payment_id NUMBER
2618 -- p_cobra_coverage_enrollment_id NUMBER
2619 -- p_due_date DATE
2620 --
2621 -- Example
2622 --
2623 -- Notes
2624 --
2625 PROCEDURE hr_cobra_do_scp_pre_update ( p_scheduled_cobra_payment_id IN OUT nocopy NUMBER,
2626 p_cobra_coverage_enrollment_id NUMBER,
2627 p_due_date DATE ) IS
2628 BEGIN
2629 --
2630 --
2631 -- chekc for duplicate due date
2632 --
2633 --
2634 hr_utility.set_location(g_package || 'hr_cobra_do_scp_update', 1);
2635 --
2636 hr_cobra_chk_dup_pay_due_date( p_scheduled_cobra_payment_id,
2637 p_cobra_coverage_enrollment_id,
2638 p_due_date );
2639 --
2640 END hr_cobra_do_scp_pre_update;
2641 --
2642 --
2643 --
2644 -- Name hr_cobra_lock_scp
2645 --
2646 -- Purpose
2647 --
2648 -- locks scp rows if cobra cost is being changed
2649 --
2650 -- Arguments
2651 --
2652 -- p_business_group_id
2653 -- p_cobra_coverage_enrollment_id
2654 --
2655 --
2656 PROCEDURE hr_cobra_lock_scp ( p_business_group_id NUMBER,
2657 p_cobra_coverage_enrollment_id NUMBER) IS
2658 --
2659 -- declare local variables
2660 --
2661 l_lock_scp VARCHAR2(30);
2662 --
2663 -- define cursor
2664 --
2665 CURSOR lock_scp IS
2666 SELECT 'lock payment_schedules'
2667 FROM per_sched_cobra_payments scp
2668 WHERE scp.business_group_id + 0 = p_business_group_id
2669 AND scp.cobra_coverage_enrollment_id = p_cobra_coverage_enrollment_id
2670 FOR UPDATE OF scp.scheduled_cobra_payment_id;
2671 --
2672 BEGIN
2673 --
2674 -- lock table
2675 --
2676 OPEN lock_scp;
2677 FETCH lock_scp INTO l_lock_scp;
2678 CLOSE lock_scp;
2679 --
2680 --
2681 --
2682 END hr_cobra_lock_scp;
2683 --
2684 --
2685 --
2686 -- ************************************
2687 -- CCB - Cobra Coverage Benefits Stuff
2688 -- ************************************
2689 --
2690 --
2691 --
2692 -- Name
2693 --
2694 -- Purpose
2695 --
2696 -- defaults COBRA cost for chosen coverage and benefit plan
2697 --
2698 -- Arguments
2699 --
2700 -- p_element_type_id NUMBER
2701 -- p_coverage_type VARCHAR2
2702 -- p_qualifying_date DATE
2703 -- p_business_group_id NUMBER
2704 -- p_coverage_amount IN OUT VARCHAR2
2705 -- p_basic_cost IN OUT VARCHAR2
2706 --
2707 -- Example
2708 --
2709 -- Notes
2710 --
2711 PROCEDURE hr_cobra_default_cobra_cost ( p_element_type_id NUMBER,
2712 p_coverage_type VARCHAR2,
2713 p_session_date DATE,
2714 p_business_group_id NUMBER,
2715 p_coverage_amount IN OUT nocopy VARCHAR2,
2716 p_basic_cost IN OUT nocopy VARCHAR2) IS
2717 --
2718 -- declare cursor
2719 --
2720 CURSOR coverage_amount IS
2721 SELECT bc.employer_contribution + bc.employee_contribution,
2722 bc.employer_contribution + bc.employee_contribution
2723 FROM ben_benefit_contributions bc
2724 WHERE bc.business_group_id + 0 = p_business_group_id
2725 AND bc.coverage_type = p_coverage_type
2726 AND bc.element_type_id = p_element_type_id
2727 AND p_session_date BETWEEN
2728 bc.effective_start_date AND bc.effective_end_date;
2729
2730 l_package VARCHAR2(70) := g_package || 'hr_cobra_default_cobra_cost';
2731 --
2732 BEGIN
2733 --
2734 -- get contributions - if any
2735 --
2736 hr_utility.set_location(l_package, 1);
2737 OPEN coverage_amount;
2738 FETCH coverage_amount INTO p_coverage_amount, p_basic_cost;
2739 --
2740 -- check if any coverage
2741 --
2742 hr_utility.set_location(l_package, 2);
2743 IF (coverage_amount%NOTFOUND)
2744 THEN
2745 hr_utility.set_location(l_package, 3);
2746 --
2747 -- default 0.00
2748 --
2749 p_basic_cost := 0;
2750 p_coverage_amount := 0;
2751 END IF;
2752 --
2753 CLOSE coverage_amount;
2754 --
2755 hr_utility.set_location(l_package, 4);
2756 --
2757 --
2758 END hr_cobra_default_cobra_cost;
2759 --
2760 --
2761 --
2762 -- Name hr_cobra_do_ccb_update
2763 --
2764 -- Purpose
2765 --
2766 -- bundles update logic
2767 --
2768 -- Arguments
2769 --
2770 -- Example
2771 --
2772 -- Notes
2773 --
2774 PROCEDURE hr_cobra_do_ccb_update ( p_Rowid IN OUT nocopy VARCHAR2,
2775 p_Cobra_Coverage_Benefit_Id IN OUT nocopy NUMBER,
2776 p_Effective_Start_Date DATE,
2777 p_Effective_End_Date DATE,
2778 p_Business_Group_Id NUMBER,
2779 p_Cobra_Coverage_Enrollment_Id NUMBER,
2780 p_Element_Type_Id NUMBER,
2781 p_Accept_Reject_Flag VARCHAR2,
2782 p_Coverage_Amount VARCHAR2,
2783 p_Coverage_Type VARCHAR2,
2784 p_Attribute_Category VARCHAR2,
2785 p_Attribute1 VARCHAR2,
2786 p_Attribute2 VARCHAR2,
2787 p_Attribute3 VARCHAR2,
2788 p_Attribute4 VARCHAR2,
2789 p_Attribute5 VARCHAR2,
2790 p_Attribute6 VARCHAR2,
2791 p_Attribute7 VARCHAR2,
2792 p_Attribute8 VARCHAR2,
2793 p_Attribute9 VARCHAR2,
2794 p_Attribute10 VARCHAR2,
2795 p_Attribute11 VARCHAR2,
2796 p_Attribute12 VARCHAR2,
2797 p_Attribute13 VARCHAR2,
2798 p_Attribute14 VARCHAR2,
2799 p_Attribute15 VARCHAR2,
2800 p_Attribute16 VARCHAR2,
2801 p_Attribute17 VARCHAR2,
2802 p_Attribute18 VARCHAR2,
2803 p_Attribute19 VARCHAR2,
2804 p_Attribute20 VARCHAR2,
2805 p_qualifying_event VARCHAR2,
2806 p_new_amount_charged IN OUT nocopy VARCHAR2 ) IS
2807 BEGIN
2808 --
2809 -- do update
2810 --
2811 per_cobra_cov_benefits_pkg.update_row
2812 (p_Rowid,
2813 p_Cobra_Coverage_Benefit_Id,
2814 p_Effective_Start_Date,
2815 p_Effective_End_Date,
2816 p_Business_Group_Id,
2817 p_Cobra_Coverage_Enrollment_Id,
2818 p_Element_Type_Id,
2819 p_Accept_Reject_Flag,
2820 p_Coverage_Amount,
2821 p_Coverage_Type,
2822 p_Attribute_Category,
2823 p_Attribute1,
2824 p_Attribute2,
2825 p_Attribute3,
2826 p_Attribute4,
2827 p_Attribute5,
2828 p_Attribute6,
2829 p_Attribute7,
2830 p_Attribute8,
2831 p_Attribute9,
2832 p_Attribute10,
2833 p_Attribute11,
2834 p_Attribute12,
2835 p_Attribute13,
2836 p_Attribute14,
2837 p_Attribute15,
2838 p_Attribute16,
2839 p_Attribute17,
2840 p_Attribute18,
2841 p_Attribute19,
2842 p_Attribute20);
2843 --
2844 -- Calculate new amount charged
2845 --
2846 p_new_amount_charged := hr_cobra_calc_amt_charged( p_cobra_coverage_enrollment_id,p_Effective_Start_Date);
2847 --
2848 END hr_cobra_do_ccb_update;
2849 --
2850 --
2851 --
2852 -- Name hr_cobra_chk_rej_to_acc
2853 --
2854 -- Purpose
2855 --
2856 -- If the user changes from Reject to accept - should prompt them to manually
2857 -- re-activate element entries for the particular benefit if the person enrolled
2858 -- is to pay thriugh payroll.
2859 --
2860 -- returns TRUE if changing from rej to acc
2861 --
2862 -- Arguments
2863 --
2864 -- p_rowid
2865 --
2866 FUNCTION hr_cobra_chk_rej_to_acc (p_rowid VARCHAR2 ) RETURN BOOLEAN IS
2867 --
2868 -- declare local variables
2869 --
2870 l_rej_to_acc VARCHAR2(1) := 'N';
2871 --
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
2881 --
2882 OPEN rej_to_acc;
2883 FETCH rej_to_acc INTO l_rej_to_acc;
2884 CLOSE rej_to_acc;
2885 --
2886 -- check if changed
2887 --
2888 IF( l_rej_to_acc = 'Y' )
2889 THEN
2890 -- return TRUE
2891 --
2892 RETURN TRUE;
2893 ELSE
2894 -- return FALSE;
2895 --
2896 RETURN FALSE;
2897 END IF;
2898 --
2899 END hr_cobra_chk_rej_to_acc;
2900 --
2901 --
2902 --
2903 -- Name hr_do_ccb_post_update
2904 --
2905 -- Purpose
2906 --
2907 -- ccb post update logic
2908 --
2909 -- Arguments
2910 --
2911 -- p_cobra_coverage_enrollment_id NUMBER
2912 -- p_first_payment_due_date DATE
2913 -- d_amount_charged IN OUT VARCHAR2
2914 --
2915 PROCEDURE hr_cobra_do_ccb_post_update ( p_cobra_coverage_enrollment_id NUMBER,
2916 p_new_amount_charged VARCHAR2,
2917 p_session_date DATE ) IS
2918 --
2919 BEGIN
2920 --
2921 -- update payment schedules
2922 --
2923 hr_cobra_correct_scp( p_cobra_coverage_enrollment_id,
2924 p_session_date );
2925 --
2926 --
2927 END hr_cobra_do_ccb_post_update;
2928 --
2929 --
2930 --
2931 -- Name get_basic_cost
2932 --
2933 -- Purpose
2934 --
2935 -- get the sum of the ER and EE inoput values fro a given element, coverage
2936 -- and assignment
2937 --
2938 -- Arguments
2939 --
2940 -- p_cobra_coverage_enrollment_id
2941 -- p_new_amount_charged
2942 --
2943 -- Example
2944 --
2945 -- Notes
2946 --
2947 --
2948 FUNCTION get_basic_cost ( p_element_type_id NUMBER,
2949 p_coverage_type VARCHAR2,
2950 p_assignment_id NUMBER) RETURN NUMBER IS
2951 --
2952 -- declare cursors
2953 --
2954 -- chk_ee_exists - tests whether element entries still exist for the given element
2955 -- AND coverage
2956 --
2957 CURSOR chk_ee_exists IS
2958 SELECT 'Y'
2959 FROM dual
2960 WHERE EXISTS
2961 ( SELECT 'x'
2962 FROM pay_element_entry_values eev_cov,
2963 pay_input_values iv_cov,
2964 pay_element_entries ee,
2965 pay_element_links el,
2966 pay_element_types et
2967 WHERE
2968 et.element_type_id = p_element_type_id
2969 AND
2970 el.element_type_id = et.element_type_id
2971 AND
2972 ee.element_link_id = el.element_link_id AND
2973 ee.assignment_id = p_assignment_id
2974 AND
2975 iv_cov.element_type_id = et.element_type_id AND
2976 UPPER(iv_cov.name) = 'COVERAGE'
2977 AND
2978 iv_cov.input_value_id = eev_cov.input_value_id AND
2979 eev_cov.element_entry_id = ee.element_entry_id AND
2980 eev_cov.screen_entry_value = p_coverage_type );
2981 --
2982 --
2983 -- chk_bc_exists - tests whether ben cont exists for given element and coverage
2984 --
2985 CURSOR chk_bc_exists IS
2986 SELECT 'Y'
2987 FROM ben_benefit_contributions
2988 WHERE
2989 element_type_id = p_element_type_id AND
2990 coverage_type = p_coverage_type;
2991 --
2992 -- get_basic_cost_ee - retrieves basic cost looking at the ee level
2993 --
2994 CURSOR get_basic_cost_ee IS
2995 SELECT
2996 NVL(fnd_number.number_to_canonical(
2997 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))) +
2998 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)))
2999 ),'0')
3000 FROM
3001 pay_element_entry_values eev_er,
3002 pay_element_entry_values eev_ee,
3003 pay_element_entry_values eev_cov,
3004 pay_input_values iv_er,
3005 pay_input_values iv_ee,
3006 pay_input_values iv_cov,
3007 ben_benefit_contributions bc,
3008 pay_element_entries ee,
3009 pay_element_links el,
3010 pay_element_types et
3011 WHERE
3012 bc.element_type_id(+) = et.element_type_id AND
3013 (
3014 (bc.coverage_type IS NULL)
3015 OR
3016 ( bc.coverage_type = p_coverage_type)
3017 )
3018 AND
3019 eev_ee.element_entry_id = ee.element_entry_id AND
3020 eev_ee.input_value_id = iv_ee.input_value_id
3021 AND
3022 eev_er.element_entry_id = ee.element_entry_id AND
3023 eev_er.input_value_id = iv_er.input_value_id
3024 AND
3025 iv_ee.element_type_id = et.element_type_id AND
3026 UPPER(iv_ee.name) = 'EE CONTR'
3027 AND
3028 iv_er.element_type_id = et.element_type_id AND
3029 UPPER(iv_er.name) = 'ER CONTR'
3030 AND
3031 iv_cov.input_value_id = eev_cov.input_value_id AND
3032 eev_cov.element_entry_id = ee.element_entry_id AND
3033 eev_cov.screen_entry_value = p_coverage_type
3034 AND
3035 iv_cov.element_type_id = et.element_type_id AND
3036 UPPER(iv_cov.name) = 'COVERAGE'
3037 AND
3038 ee.element_link_id = el.element_link_id AND
3039 ee.assignment_id = p_assignment_id
3040 AND
3041 el.element_type_id = et.element_type_id
3042 AND
3043 et.element_type_id = p_element_type_id;
3044 --
3045 --
3046 -- get_basic_cost - retrieves basic cost without looking at the ee level
3047 --
3048 CURSOR get_basic_cost IS
3049 SELECT
3050 /* NVL(fnd_number.number_to_canonical(
3051 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))) +
3052 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)))
3053 ),'0') */
3054
3055 NVL(fnd_number.number_to_canonical(
3056 NVL(fnd_number.canonical_to_number(bc.employer_contribution), DECODE(iv_cov.default_value, NULL, 0, fnd_number.canonical_to_number(iv_er.default_value))) +
3057 NVL(fnd_number.canonical_to_number(bc.employee_contribution), DECODE(iv_cov.default_value, NULL, 0, fnd_number.canonical_to_number(iv_ee.default_value)))
3058 ),'0') --Fix 8646350
3059 FROM
3060 ben_benefit_contributions bc,
3061 pay_input_values iv_er,
3062 pay_input_values iv_ee,
3063 pay_input_values iv_cov,
3064 pay_element_types et
3065 WHERE
3066 bc.element_type_id(+) = et.element_type_id AND
3067 (
3068 (bc.coverage_type IS NULL)
3069 OR
3070 ( bc.coverage_type = p_coverage_type)
3071 )
3072 AND
3073 iv_ee.element_type_id = et.element_type_id AND
3074 UPPER(iv_ee.name) = 'EE CONTR'
3075 AND
3076 iv_er.element_type_id = et.element_type_id AND
3077 UPPER(iv_er.name) = 'ER CONTR'
3078 AND
3079 iv_cov.element_type_id (+)= et.element_type_id AND
3080 UPPER(iv_cov.name(+)) = 'COVERAGE' AND
3081 iv_cov.default_value (+)= p_coverage_type
3082 AND
3083 et.element_type_id = p_element_type_id;
3084 --
3085 -- declare local variables
3086 --
3087 l_ees_exist VARCHAR2(1) := 'N';
3088 l_ets_exist VARCHAR2(1) := 'N';
3089 l_basic_cost NUMBER(15,2) := 0;
3090 l_package VARCHAR2(70) := g_package || 'get_basic_cost';
3091 --
3092 BEGIN
3093 --
3094 hr_utility.set_location('Entering:' || l_package, 0);
3095 hr_utility.trace('p_element_type_id : ' || p_element_type_id);
3096 hr_utility.trace('p_coverage_type : ' || p_coverage_type);
3097 hr_utility.trace('p_assignment_id : ' || p_assignment_id);
3098
3099 --
3100 --
3101 OPEN chk_ee_exists;
3102 FETCH chk_ee_exists INTO l_ees_exist;
3103 CLOSE chk_ee_exists;
3104 --
3105 hr_utility.set_location(l_package, 1);
3106 --
3107 --
3108 IF ( l_ees_exist = 'Y' )
3109 THEN
3110 --
3111 hr_utility.set_location(l_package, 2);
3112 --
3113 --
3114 OPEN get_basic_cost_ee;
3115 FETCH get_basic_cost_ee INTO l_basic_cost;
3116 CLOSE get_basic_cost_ee;
3117 --
3118 ELSE
3119 --
3120 hr_utility.set_location(l_package, 3);
3121 --
3122 --
3123 OPEN get_basic_cost;
3124 FETCH get_basic_cost INTO l_basic_cost;
3125 CLOSE get_basic_cost;
3126 --
3127 END IF;
3128 --
3129 -- return basic cost
3130 --
3131 hr_utility.trace('l_basic_cost : ' || l_basic_cost);
3132 hr_utility.set_location(' Leaving : ' || l_package, 4);
3133 --
3134 --
3135 RETURN l_basic_cost;
3136 --
3137 --
3138 END get_basic_cost;
3139 --
3140 --
3141 --
3142 --
3143 -- ************************************
3144 -- SCP - Schedule Cobra Payments Stuff
3145 -- ************************************
3146 --
3147 --
3148 --
3149 -- Name hr_cobra_correct_scp
3150 --
3151 -- Purpose
3152 --
3153 -- updates amount due of payment schedules if the
3154 -- user has updated the cobra cost
3155 --
3156 -- Arguments
3157 --
3158 -- p_cobra_coverage_enrollment_id
3159 -- p_new_amount_charged
3160 --
3161 -- Example
3162 --
3163 -- Notes
3164 --
3165 --
3166 --
3167 PROCEDURE hr_cobra_correct_scp( p_cobra_coverage_enrollment_id NUMBER,
3168 p_session_date DATE ) IS
3169 BEGIN
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' )
3179 WHERE scp.cobra_coverage_enrollment_id = p_cobra_coverage_enrollment_id
3180 AND scp.amount_received IS NULL
3181 AND scp.date_due >= p_session_date;
3182 --
3183 END hr_cobra_correct_scp;
3184 --
3185 --
3186 PROCEDURE eci_init_form( p_assignment_id NUMBER,
3187 p_business_group_id NUMBER,
3188 p_qualifying_date DATE,
3189 p_organization_id IN OUT nocopy NUMBER,
3190 p_position_id IN OUT nocopy NUMBER,
3191 p_await_meaning IN OUT nocopy VARCHAR2,
3192 p_period_type IN OUT nocopy VARCHAR2) IS
3193 --
3194 BEGIN
3195 --
3196 hr_get_assignment_info ( p_assignment_id,
3197 p_business_group_id,
3198 p_qualifying_date,
3199 p_organization_id,
3200 p_position_id);
3201 --
3202 p_await_meaning := per_cobra_eci.hr_cobra_get_await_meaning;
3203 p_period_type := per_cobra_eci.hr_cobra_get_period_type;
3204 --
3205 END eci_init_form;
3206 --
3207 Procedure chk_cobra_dependent_id(p_cobra_dependent_id in number,
3208 p_object_version_number in number) is
3209 --
3210 l_proc varchar2(72) := g_package || 'chk_cobra_dependent_id';
3211 --
3212 Begin
3213 --
3214 hr_utility.set_location('Entering:'||l_proc, 5);
3215 --
3216 if (p_object_version_number is not null and
3217 p_cobra_dependent_id
3218 <> nvl(g_old_rec.cobra_dependent_id,hr_api.g_number)) then
3219 --
3220 -- raise error as PK has changed
3221 --
3222 hr_utility.set_message(801,'HR_52271_CDP_PK_INV');
3223 hr_utility.raise_error;
3224 --
3225 elsif p_object_version_number is null then
3226 --
3227 -- check if PK is null.
3228 --
3229 if p_cobra_dependent_id is not null then
3230 --
3231 -- raise error as PK is not null
3232 --
3233 hr_utility.set_message(801,'HR_52271_CDP_PK_INV');
3234 hr_utility.raise_error;
3235 --
3236 end if;
3237 --
3238 end if;
3239 --
3240 hr_utility.set_location(' Leaving:'||l_proc, 10);
3241 --
3242 End chk_cobra_dependent_id;
3243 --
3244 Procedure chk_enrollment_id(p_cobra_dependent_id in number,
3245 p_cobra_coverage_enrollment_id in number,
3246 p_effective_start_date in date,
3247 p_effective_end_date in date,
3248 p_object_version_number in number) is
3249 --
3250 l_proc varchar2(72) := 'chk_enrollment_id';
3251 l_dummy varchar2(1);
3252 --
3253 -- The effective start and end date for the dependents coverage must
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
3263 and p_effective_end_date
3264 between cov.coverage_start_date
3265 and cov.coverage_end_date;
3266 --
3267 Begin
3268 --
3269 hr_utility.set_location('Entering:'||l_proc, 5);
3270 --
3271 if p_cobra_coverage_enrollment_id is null then
3272 --
3273 -- raise error as FK is null
3274 --
3275 hr_utility.set_message(801,'HR_52272_CDP_ENROLL_FK');
3276 hr_utility.raise_error;
3277 --
3278 end if;
3279 --
3280 -- Check if the enrollment is still valid as of the validation start
3281 -- and end dates.
3282 --
3283 if (p_object_version_number is not null
3284 and (nvl(p_effective_start_date,hr_api.g_date)
3285 <> g_old_rec.effective_start_date
3286 or nvl(p_effective_end_date,hr_api.g_date)
3287 <> g_old_rec.effective_end_date)) then
3288 --
3289 -- The dates have changed i.e. an update has occured or correction
3290 -- so make sure that the dependent effective dates are within the
3291 -- coverage period of the enrollment.
3292 --
3293 open c1;
3294 --
3295 fetch c1 into l_dummy;
3296 if c1%notfound then
3297 --
3298 close c1;
3299 --
3300 -- The dates for the covered dependent are outside of the dates of
3301 -- the cobra enrollment event. Raise ERROR.
3302 --
3303 hr_utility.set_message(801,'HR_52273_CDP_DEP_DATES');
3304 hr_utility.raise_error;
3305 --
3306 end if;
3307 --
3308 close c1;
3309 --
3310 end if;
3311 --
3312 hr_utility.set_location(' Leaving:'||l_proc, 10);
3313 --
3314 End chk_enrollment_id;
3315 --
3316 Procedure chk_contact_relationship_id
3317 (p_cobra_dependent_id in number,
3318 p_cobra_coverage_enrollment_id in number,
3319 p_contact_relationship_id in number,
3320 p_object_version_number in number) is
3321 --
3322 l_proc varchar2(72) := 'chk_contact_relationship_id';
3323 l_dummy varchar2(1);
3324 --
3325 cursor c1 is
3326 select null
3327 from per_contact_relationships cre
3328 where cre.contact_relationship_id = p_contact_relationship_id;
3329 --
3330 Begin
3331 --
3332 hr_utility.set_location('Entering:'||l_proc, 5);
3333 --
3334 if p_contact_relationship_id is null then
3335 --
3336 -- raise error as FK is null
3337 --
3338 hr_utility.set_message(801, 'HR_52274_CDP_INV_CONTACT');
3339 hr_utility.raise_error;
3340 --
3341 end if;
3342 --
3343 -- Make sure that contact exists in PER_CONTACT_RELATIONSHIPS table
3344 --
3345 open c1;
3346 --
3347 fetch c1 into l_dummy;
3348 if c1%notfound then
3349 --
3350 close c1;
3351 hr_utility.set_message(801, 'HR_52274_CDP_INV_CONTACT');
3352 hr_utility.raise_error;
3353 --
3354 end if;
3355 --
3356 close c1;
3357 --
3358 hr_utility.set_location(' Leaving:'||l_proc, 10);
3359 --
3360 End chk_contact_relationship_id;
3361 --
3362 Procedure chk_overlap (p_cobra_dependent_id in number,
3363 p_cobra_coverage_enrollment_id in number,
3364 p_contact_relationship_id in number,
3365 p_effective_start_date in date,
3366 p_effective_end_date in date,
3367 p_object_version_number in number) is
3368 --
3369 l_proc varchar2(72) := 'chk_overlap';
3370 l_dummy varchar2(1);
3371 --
3372 -- We do not link the dependent just to the one enrollment is as the
3373 -- dependent must not be covered more than once at any one time by any
3374 -- amount of employees. In other words coverage for a dependent can not
3375 -- be undertaken by multiple employees.
3376 --
3377 cursor c1 is
3378 select null
3379 from per_cobra_dependents_f cdp
3380 where cdp.contact_relationship_id = p_contact_relationship_id
3381 and cdp.cobra_coverage_enrollment_id = p_cobra_coverage_enrollment_id
3382 and cdp.cobra_dependent_id <> nvl(p_cobra_dependent_id,-1)
3383 and (p_effective_start_date
3384 between cdp.effective_start_date
3385 and cdp.effective_end_date
3386 or p_effective_end_date
3387 between cdp.effective_start_date
3388 and cdp.effective_end_date);
3389 --
3390 Begin
3391 --
3392 hr_utility.set_location('Entering:'||l_proc, 5);
3393 --
3394 -- check whether the new record already exists within the timeframe
3395 -- for the same dependent. A dependent can only be covered once by
3396 -- any enrollment for any particular timeframe.
3397 --
3398 open c1;
3399 --
3400 fetch c1 into l_dummy;
3401 if c1%found then
3402 --
3403 close c1;
3404 hr_utility.set_message(801,'HR_52275_CDP_DEP_COVERED');
3405 hr_utility.raise_error;
3406 --
3407 end if;
3408 --
3409 close c1;
3410 --
3411 hr_utility.set_location(' Leaving:'||l_proc, 10);
3412 --
3413 End chk_overlap;
3414 --
3415 Procedure chk_unique_key (p_cobra_dependent_id in number,
3416 p_contact_relationship_id in number,
3417 p_effective_start_date in date,
3418 p_effective_end_date in date) is
3419 --
3420 l_proc varchar2(72) := 'chk_unique_key';
3421 l_dummy varchar2(1);
3422 --
3423 cursor c1 is
3424 select null
3425 from per_cobra_dependents_f cdp
3426 where cdp.cobra_dependent_id = nvl(p_cobra_dependent_id,-1)
3427 and cdp.contact_relationship_id = p_contact_relationship_id
3428 and cdp.effective_start_date = p_effective_start_date
3429 and cdp.effective_end_date = p_effective_end_date;
3430 --
3431 Begin
3432 --
3433 hr_utility.set_location('Entering:'||l_proc, 5);
3434 --
3435 open c1;
3436 --
3437 fetch c1 into l_dummy;
3438 if c1%found then
3439 --
3440 close c1;
3441 hr_utility.set_message(801, 'HR_52276_CDP_DEP_UK');
3442 hr_utility.raise_error;
3443 --
3444 end if;
3445 --
3446 close c1;
3447 --
3448 hr_utility.set_location(' Leaving:'||l_proc, 10);
3449 --
3450 End chk_unique_key;
3451 --
3452 PROCEDURE hr_cobra_do_cdp_insert
3453 (p_cobra_dependent_id out nocopy number,
3454 p_cobra_coverage_enrollment_id in number,
3455 p_contact_relationship_id in number,
3456 p_effective_start_date in date,
3457 p_effective_end_date in date,
3458 p_object_version_number out nocopy number,
3459 p_attribute_category in varchar2,
3460 p_attribute1 in varchar2,
3461 p_attribute2 in varchar2,
3462 p_attribute3 in varchar2,
3463 p_attribute4 in varchar2,
3464 p_attribute5 in varchar2,
3465 p_attribute6 in varchar2,
3466 p_attribute7 in varchar2,
3467 p_attribute8 in varchar2,
3468 p_attribute9 in varchar2,
3469 p_attribute10 in varchar2,
3470 p_attribute11 in varchar2,
3471 p_attribute12 in varchar2,
3472 p_attribute13 in varchar2,
3473 p_attribute14 in varchar2,
3474 p_attribute15 in varchar2,
3475 p_attribute16 in varchar2,
3476 p_attribute17 in varchar2,
3477 p_attribute18 in varchar2,
3478 p_attribute19 in varchar2,
3479 p_attribute20 in varchar2) is
3480 --
3481 begin
3482 --
3483 -- Do business rule checks
3484 --
3485 chk_cobra_dependent_id
3486 (p_cobra_dependent_id => null,
3487 p_object_version_number => null);
3488 --
3489 chk_enrollment_id
3490 (p_cobra_dependent_id => null,
3491 p_cobra_coverage_enrollment_id => p_cobra_coverage_enrollment_id,
3492 p_effective_start_date => p_effective_start_date,
3493 p_effective_end_date => p_effective_end_date,
3494 p_object_version_number => null);
3495 --
3496 chk_contact_relationship_id
3497 (p_cobra_dependent_id => null,
3498 p_cobra_coverage_enrollment_id => p_cobra_coverage_enrollment_id,
3499 p_contact_relationship_id => p_contact_relationship_id,
3500 p_object_version_number => null);
3501 --
3502 chk_overlap
3503 (p_cobra_dependent_id => null,
3504 p_cobra_coverage_enrollment_id => p_cobra_coverage_enrollment_id,
3505 p_contact_relationship_id => p_contact_relationship_id,
3506 p_effective_start_date => p_effective_start_date,
3507 p_effective_end_date => p_effective_end_date,
3508 p_object_version_number => null);
3509 --
3510 chk_unique_key
3511 (p_cobra_dependent_id => null,
3512 p_contact_relationship_id => p_contact_relationship_id,
3513 p_effective_start_date => p_effective_start_date,
3514 p_effective_end_date => p_effective_end_date);
3515 --
3516 select per_cobra_dependents_s.nextval
3517 into p_cobra_dependent_id
3518 from dual;
3519 --
3520 p_object_version_number := 1;
3521 --
3522 insert into per_cobra_dependents_f
3523 (cobra_dependent_id,
3524 cobra_coverage_enrollment_id,
3525 contact_relationship_id,
3526 effective_start_date,
3527 effective_end_date,
3528 object_version_number,
3529 attribute_category,
3530 attribute1,
3531 attribute2,
3532 attribute3,
3533 attribute4,
3534 attribute5,
3535 attribute6,
3536 attribute7,
3537 attribute8,
3538 attribute9,
3539 attribute10,
3540 attribute11,
3541 attribute12,
3542 attribute13,
3543 attribute14,
3544 attribute15,
3545 attribute16,
3546 attribute17,
3547 attribute18,
3548 attribute19,
3549 attribute20
3550 )
3551 Values
3552 (p_cobra_dependent_id,
3553 p_cobra_coverage_enrollment_id,
3554 p_contact_relationship_id,
3555 p_effective_start_date,
3556 p_effective_end_date,
3557 p_object_version_number,
3558 p_attribute_category,
3559 p_attribute1,
3560 p_attribute2,
3561 p_attribute3,
3562 p_attribute4,
3563 p_attribute5,
3564 p_attribute6,
3565 p_attribute7,
3566 p_attribute8,
3567 p_attribute9,
3568 p_attribute10,
3569 p_attribute11,
3570 p_attribute12,
3571 p_attribute13,
3572 p_attribute14,
3573 p_attribute15,
3574 p_attribute16,
3575 p_attribute17,
3576 p_attribute18,
3577 p_attribute19,
3578 p_attribute20
3579 );
3580 end hr_cobra_do_cdp_insert;
3581 --
3582 PROCEDURE hr_cobra_do_cdp_update
3583 (p_row_id in varchar2,
3584 p_cobra_dependent_id in number,
3585 p_cobra_coverage_enrollment_id in number,
3586 p_contact_relationship_id in number,
3587 p_effective_start_date in date,
3588 p_effective_end_date in date,
3589 p_object_version_number in out nocopy number,
3590 p_attribute_category in varchar2,
3591 p_attribute1 in varchar2,
3592 p_attribute2 in varchar2,
3593 p_attribute3 in varchar2,
3594 p_attribute4 in varchar2,
3595 p_attribute5 in varchar2,
3596 p_attribute6 in varchar2,
3597 p_attribute7 in varchar2,
3598 p_attribute8 in varchar2,
3599 p_attribute9 in varchar2,
3600 p_attribute10 in varchar2,
3601 p_attribute11 in varchar2,
3602 p_attribute12 in varchar2,
3603 p_attribute13 in varchar2,
3604 p_attribute14 in varchar2,
3605 p_attribute15 in varchar2,
3606 p_attribute16 in varchar2,
3607 p_attribute17 in varchar2,
3608 p_attribute18 in varchar2,
3609 p_attribute19 in varchar2,
3610 p_attribute20 in varchar2) as
3611 --
3612 l_object_version_number number(9) := p_object_version_number + 1;
3613 --
3614 begin
3615 --
3616 -- Do business rule checks
3617 --
3618 chk_cobra_dependent_id
3619 (p_cobra_dependent_id => p_cobra_dependent_id,
3620 p_object_version_number => p_object_version_number);
3621 --
3622 chk_enrollment_id
3623 (p_cobra_dependent_id => p_cobra_dependent_id,
3624 p_cobra_coverage_enrollment_id => p_cobra_coverage_enrollment_id,
3625 p_effective_start_date => p_effective_start_date,
3626 p_effective_end_date => p_effective_end_date,
3627 p_object_version_number => p_object_version_number);
3628 --
3629 chk_contact_relationship_id
3630 (p_cobra_dependent_id => p_cobra_dependent_id,
3631 p_cobra_coverage_enrollment_id => p_cobra_coverage_enrollment_id,
3632 p_contact_relationship_id => p_contact_relationship_id,
3633 p_object_version_number => p_object_version_number);
3634 --
3635 chk_overlap
3636 (p_cobra_dependent_id => p_cobra_dependent_id,
3637 p_cobra_coverage_enrollment_id => p_cobra_coverage_enrollment_id,
3638 p_contact_relationship_id => p_contact_relationship_id,
3639 p_effective_start_date => p_effective_start_date,
3640 p_effective_end_date => p_effective_end_date,
3641 p_object_version_number => p_object_version_number);
3642 --
3643 chk_unique_key
3644 (p_cobra_dependent_id => p_cobra_dependent_id,
3645 p_contact_relationship_id => p_contact_relationship_id,
3646 p_effective_start_date => p_effective_start_date,
3647 p_effective_end_date => p_effective_end_date);
3648 --
3649 update per_cobra_dependents_f
3650 set effective_start_date = p_effective_start_date,
3651 effective_end_date = p_effective_end_date,
3652 contact_relationship_id = p_contact_relationship_id,
3653 object_version_number = l_object_version_number,
3654 attribute_category = p_attribute_category,
3655 attribute1 = p_attribute1,
3656 attribute2 = p_attribute2,
3657 attribute3 = p_attribute3,
3658 attribute4 = p_attribute4,
3659 attribute5 = p_attribute5,
3660 attribute6 = p_attribute6,
3661 attribute7 = p_attribute7,
3662 attribute8 = p_attribute8,
3663 attribute9 = p_attribute9,
3664 attribute10 = p_attribute10,
3665 attribute11 = p_attribute11,
3666 attribute12 = p_attribute12,
3667 attribute13 = p_attribute13,
3668 attribute14 = p_attribute14,
3669 attribute15 = p_attribute15,
3670 attribute16 = p_attribute16,
3671 attribute17 = p_attribute17,
3672 attribute18 = p_attribute18,
3673 attribute19 = p_attribute19,
3674 attribute20 = p_attribute20
3675 where rowid = p_row_id;
3676 --
3677 p_object_version_number := l_object_version_number;
3678 --
3679 end hr_cobra_do_cdp_update;
3680 --
3681 PROCEDURE hr_cobra_do_cdp_delete
3682 (p_cobra_dependent_id in number,
3683 p_effective_start_date in date,
3684 p_effective_end_date in date,
3685 p_object_version_number in number) is
3686 --
3687 l_proc varchar2(40) := g_package || 'hr_cobra_do_cdp_delete';
3688 begin
3689 --
3690 hr_utility.set_location('Entering.. ' || l_proc,10);
3691 hr_utility.trace('p_cobra_dependent_id = ' || p_cobra_dependent_id);
3692 hr_utility.trace('p_effective_start_date = ' || p_effective_start_date);
3693 hr_utility.trace('p_effective_end_date = ' || p_effective_end_date);
3694 hr_utility.trace('p_object_version_number= ' || p_object_version_number);
3695 --
3696 delete from per_cobra_dependents_f
3697 where cobra_dependent_id = p_cobra_dependent_id
3698 and effective_start_date = p_effective_start_date
3699 and effective_end_date = p_effective_end_date
3700 and object_version_number = p_object_version_number;
3701 --
3702 hr_utility.set_location('Leaving.. ' || l_proc,20);
3703 --
3704 end hr_cobra_do_cdp_delete;
3705 --
3706 procedure hr_cobra_do_cdp_lock ( p_cobra_dependent_id in number,
3707 p_effective_start_date in date,
3708 p_effective_end_date in date,
3709 p_object_version_number in number) is
3710 --
3711 -- declare local variables
3712 --
3713 l_lock_cdp VARCHAR2(30);
3714 l_object_invalid exception;
3715 --
3716 -- define cursor
3717 --
3718 cursor lock_cdp is
3719 select
3720 cobra_dependent_id,
3721 cobra_coverage_enrollment_id,
3722 contact_relationship_id,
3723 effective_start_date,
3724 effective_end_date,
3725 object_version_number,
3726 attribute_category,
3727 attribute1,
3728 attribute2,
3729 attribute3,
3730 attribute4,
3731 attribute5,
3732 attribute6,
3733 attribute7,
3734 attribute8,
3735 attribute9,
3736 attribute10,
3737 attribute11,
3738 attribute12,
3739 attribute13,
3740 attribute14,
3741 attribute15,
3742 attribute16,
3743 attribute17,
3744 attribute18,
3745 attribute19,
3746 attribute20
3747 from per_cobra_dependents_f
3748 where cobra_dependent_id = p_cobra_dependent_id
3749 and p_effective_start_date = effective_start_date
3750 and p_effective_end_date = effective_end_date
3751 for update nowait;
3752 --
3753 begin
3754 --
3755 -- lock table
3756 --
3757 open lock_cdp;
3758 --
3759 fetch lock_cdp into g_old_rec;
3760 if lock_cdp%notfound then
3761 close lock_cdp;
3762 --
3763 -- The primary key is invalid therefore we must error
3764 --
3765 hr_utility.set_message(801,'HR_7220_INVALID_PRIMARY_KEY');
3766 hr_utility.raise_error;
3767 --
3768 end if;
3769 --
3770 close lock_cdp;
3771 --
3772 if (p_object_version_number <> g_old_rec.object_version_number) Then
3773 hr_utility.set_message(801, 'HR_7155_OBJECT_INVALID');
3774 hr_utility.raise_error;
3775 end if;
3776 --
3777 exception
3778 when hr_api.object_locked then
3779 --
3780 -- The object is locked therefore we need to supply a meaningful
3781 -- error message.
3782 --
3783 hr_utility.set_message(801, 'HR_7165_OBJECT_LOCKED');
3784 hr_utility.set_message_token('TABLE_NAME', 'per_cobra_dependents_f');
3785 hr_utility.raise_error;
3786 --
3787 when l_object_invalid then
3788 --
3789 -- The object doesn't exist or is invalid
3790 --
3791 hr_utility.set_message(801, 'HR_7155_OBJECT_INVALID');
3792 hr_utility.set_message_token('TABLE_NAME', 'per_cobra_dependents_f');
3793 hr_utility.raise_error;
3794 --
3795 end hr_cobra_do_cdp_lock;
3796 --
3797 function dependent_born_in_coverage
3798 (p_contact_relationship_id in number,
3799 p_coverage_start_date in date,
3800 p_coverage_end_date in date) return boolean is
3801 --
3802 cursor c1 is
3803 select a.date_of_birth
3804 from per_people_f a,
3805 per_contact_relationships b
3806 where a.person_id = b.contact_person_id
3807 and b.contact_relationship_id = p_contact_relationship_id;
3808 --
3809 l_dob date;
3810 --
3811 begin
3812 --
3813 open c1;
3814 --
3815 fetch c1 into l_dob;
3816 --
3817 close c1;
3818 --
3819 if l_dob is not null then
3820 --
3821 -- Check if dependent birth date is between coverage start and end
3822 -- dates.
3823 --
3824 if l_dob
3825 between p_coverage_start_date
3826 and p_coverage_end_date then
3827 --
3828 return true;
3829 --
3830 else
3831 --
3832 return false;
3833 --
3834 end if;
3835 --
3836 else
3837 --
3838 -- Cannot derive birth date so return false, i.e. dependent birth date
3839 -- unknown.
3840 --
3841 return false;
3842 --
3843 end if;
3844 --
3845 end dependent_born_in_coverage;
3846 --
3847
3848 Function check_clashing_periods
3849 (p_cobra_coverage_enrollment_id in number,
3850 p_assignment_id in number,
3851 p_coverage_start_date in date,
3852 p_coverage_end_date in date,
3853 p_qualifying_event in varchar2) return boolean is
3854 --
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
3864 and a.coverage_end_date
3865 or
3866 p_coverage_end_date
3867 between a.coverage_start_date
3868 and a.coverage_end_date)
3869 and a.qualifying_event = p_qualifying_event;
3870 --
3871 l_dummy varchar2(1);
3872 --
3873 begin
3874 --
3875 -- Check if overlap of periods occurs!
3876 --
3877 open c1;
3878 --
3879 fetch c1 into l_dummy;
3880 if c1%found then
3881 --
3882 close c1;
3883 return true;
3884 --
3885 end if;
3886 --
3887 close c1;
3888 --
3889 return false;
3890 --
3891 end check_clashing_periods;
3892 --
3893 procedure check_date_invalidation
3894 (p_cobra_coverage_enrollment_id in number,
3895 p_coverage_start_date in date,
3896 p_coverage_end_date in date) is
3897 --
3898 -- Cursor checks that event dates don't affect any
3899 -- dependents who are linked to the enrollment
3900 --
3901 -- Bugs 609701 and 669253. Correct the cursor to prevent it raising
3902 -- the error when it shouldn't. The clause on the enrollment id
3903 -- was missing (bug 609701) and also the brackets (bug 669253).
3904 --
3905 cursor c1 is
3906 select null
3907 from per_cobra_dependents_f a
3908 where a.cobra_coverage_enrollment_id = p_cobra_coverage_enrollment_id
3909 and ( ( a.effective_start_date
3910 not between p_coverage_start_date
3911 and p_coverage_end_date )
3912 or
3913 ( a.effective_end_date
3914 not between p_coverage_start_date
3915 and p_coverage_end_date )
3916 );
3917 --
3918 l_dummy varchar2(1);
3919 --
3920 begin
3921 --
3922 open c1;
3923 --
3924 fetch c1 into l_dummy;
3925 if c1%found then
3926 --
3927 close c1;
3928 hr_utility.set_message(801,'HR_52277_CDP_DEP_INVALID');
3929 hr_utility.raise_error;
3930 --
3931 end if;
3932 --
3933 close c1;
3934 --
3935 end check_date_invalidation;
3936 --
3937 END per_cobra_eci;