[Home] [Help]
PACKAGE BODY: APPS.PER_COBRA_ECI
Source
1 PACKAGE BODY per_cobra_eci AS
2 /* $Header: pecobeci.pkb 120.3 2006/06/13 06:03:07 saikrish 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 FROM
3055 ben_benefit_contributions bc,
3056 pay_input_values iv_er,
3057 pay_input_values iv_ee,
3058 pay_input_values iv_cov,
3059 pay_element_types et
3060 WHERE
3061 bc.element_type_id(+) = et.element_type_id AND
3062 (
3063 (bc.coverage_type IS NULL)
3064 OR
3065 ( bc.coverage_type = p_coverage_type)
3066 )
3067 AND
3068 iv_ee.element_type_id = et.element_type_id AND
3069 UPPER(iv_ee.name) = 'EE CONTR'
3070 AND
3071 iv_er.element_type_id = et.element_type_id AND
3072 UPPER(iv_er.name) = 'ER CONTR'
3073 AND
3074 iv_cov.element_type_id (+)= et.element_type_id AND
3075 UPPER(iv_cov.name(+)) = 'COVERAGE' AND
3076 iv_cov.default_value (+)= p_coverage_type
3077 AND
3078 et.element_type_id = p_element_type_id;
3079 --
3080 -- declare local variables
3081 --
3082 l_ees_exist VARCHAR2(1) := 'N';
3083 l_ets_exist VARCHAR2(1) := 'N';
3084 l_basic_cost NUMBER(15,2) := 0;
3085 l_package VARCHAR2(70) := g_package || 'get_basic_cost';
3086 --
3087 BEGIN
3088 --
3089 hr_utility.set_location('Entering:' || l_package, 0);
3090 hr_utility.trace('p_element_type_id : ' || p_element_type_id);
3091 hr_utility.trace('p_coverage_type : ' || p_coverage_type);
3092 hr_utility.trace('p_assignment_id : ' || p_assignment_id);
3093
3094 --
3095 --
3096 OPEN chk_ee_exists;
3097 FETCH chk_ee_exists INTO l_ees_exist;
3098 CLOSE chk_ee_exists;
3099 --
3100 hr_utility.set_location(l_package, 1);
3101 --
3102 --
3103 IF ( l_ees_exist = 'Y' )
3104 THEN
3105 --
3106 hr_utility.set_location(l_package, 2);
3107 --
3108 --
3109 OPEN get_basic_cost_ee;
3110 FETCH get_basic_cost_ee INTO l_basic_cost;
3111 CLOSE get_basic_cost_ee;
3112 --
3113 ELSE
3114 --
3115 hr_utility.set_location(l_package, 3);
3116 --
3117 --
3118 OPEN get_basic_cost;
3119 FETCH get_basic_cost INTO l_basic_cost;
3120 CLOSE get_basic_cost;
3121 --
3122 END IF;
3123 --
3124 -- return basic cost
3125 --
3126 hr_utility.trace('l_basic_cost : ' || l_basic_cost);
3127 hr_utility.set_location(' Leaving : ' || l_package, 4);
3128 --
3129 --
3130 RETURN l_basic_cost;
3131 --
3132 --
3133 END get_basic_cost;
3134 --
3135 --
3136 --
3137 --
3138 -- ************************************
3139 -- SCP - Schedule Cobra Payments Stuff
3140 -- ************************************
3141 --
3142 --
3143 --
3144 -- Name hr_cobra_correct_scp
3145 --
3146 -- Purpose
3147 --
3148 -- updates amount due of payment schedules if the
3149 -- user has updated the cobra cost
3150 --
3151 -- Arguments
3152 --
3153 -- p_cobra_coverage_enrollment_id
3154 -- p_new_amount_charged
3155 --
3156 -- Example
3157 --
3158 -- Notes
3159 --
3160 --
3161 --
3162 PROCEDURE hr_cobra_correct_scp( p_cobra_coverage_enrollment_id NUMBER,
3163 p_session_date DATE ) IS
3164 BEGIN
3165 --
3166 UPDATE per_sched_cobra_payments scp
3167 SET amount_due =
3168 ( SELECT fnd_number.number_to_canonical(NVL(SUM(fnd_number.canonical_to_number(coverage_amount)),'0'))
3169 FROM per_cobra_coverage_benefits_f ccb
3170 WHERE ccb.cobra_coverage_enrollment_id = p_cobra_coverage_enrollment_id
3171 AND scp.date_due BETWEEN
3172 ccb.effective_start_date AND ccb.effective_end_date
3173 AND ccb.accept_reject_flag = 'ACC' )
3174 WHERE scp.cobra_coverage_enrollment_id = p_cobra_coverage_enrollment_id
3175 AND scp.amount_received IS NULL
3176 AND scp.date_due >= p_session_date;
3177 --
3178 END hr_cobra_correct_scp;
3179 --
3180 --
3181 PROCEDURE eci_init_form( p_assignment_id NUMBER,
3182 p_business_group_id NUMBER,
3183 p_qualifying_date DATE,
3184 p_organization_id IN OUT nocopy NUMBER,
3185 p_position_id IN OUT nocopy NUMBER,
3186 p_await_meaning IN OUT nocopy VARCHAR2,
3187 p_period_type IN OUT nocopy VARCHAR2) IS
3188 --
3189 BEGIN
3190 --
3191 hr_get_assignment_info ( p_assignment_id,
3192 p_business_group_id,
3193 p_qualifying_date,
3194 p_organization_id,
3195 p_position_id);
3196 --
3197 p_await_meaning := per_cobra_eci.hr_cobra_get_await_meaning;
3198 p_period_type := per_cobra_eci.hr_cobra_get_period_type;
3199 --
3200 END eci_init_form;
3201 --
3202 Procedure chk_cobra_dependent_id(p_cobra_dependent_id in number,
3203 p_object_version_number in number) is
3204 --
3205 l_proc varchar2(72) := g_package || 'chk_cobra_dependent_id';
3206 --
3207 Begin
3208 --
3209 hr_utility.set_location('Entering:'||l_proc, 5);
3210 --
3211 if (p_object_version_number is not null and
3212 p_cobra_dependent_id
3213 <> nvl(g_old_rec.cobra_dependent_id,hr_api.g_number)) then
3214 --
3215 -- raise error as PK has changed
3216 --
3217 hr_utility.set_message(801,'HR_52271_CDP_PK_INV');
3218 hr_utility.raise_error;
3219 --
3220 elsif p_object_version_number is null then
3221 --
3222 -- check if PK is null.
3223 --
3224 if p_cobra_dependent_id is not null then
3225 --
3226 -- raise error as PK is not null
3227 --
3228 hr_utility.set_message(801,'HR_52271_CDP_PK_INV');
3229 hr_utility.raise_error;
3230 --
3231 end if;
3232 --
3233 end if;
3234 --
3235 hr_utility.set_location(' Leaving:'||l_proc, 10);
3236 --
3237 End chk_cobra_dependent_id;
3238 --
3239 Procedure chk_enrollment_id(p_cobra_dependent_id in number,
3240 p_cobra_coverage_enrollment_id in number,
3241 p_effective_start_date in date,
3242 p_effective_end_date in date,
3243 p_object_version_number in number) is
3244 --
3245 l_proc varchar2(72) := 'chk_enrollment_id';
3246 l_dummy varchar2(1);
3247 --
3248 -- The effective start and end date for the dependents coverage must
3249 -- be between the event coverage start and end dates.
3250 --
3251 cursor c1 is
3252 select null
3253 from per_cobra_cov_enrollments cov
3254 where cov.cobra_coverage_enrollment_id = p_cobra_coverage_enrollment_id
3255 and p_effective_start_date
3256 between cov.coverage_start_date
3257 and cov.coverage_end_date
3258 and p_effective_end_date
3259 between cov.coverage_start_date
3260 and cov.coverage_end_date;
3261 --
3262 Begin
3263 --
3264 hr_utility.set_location('Entering:'||l_proc, 5);
3265 --
3266 if p_cobra_coverage_enrollment_id is null then
3267 --
3268 -- raise error as FK is null
3269 --
3270 hr_utility.set_message(801,'HR_52272_CDP_ENROLL_FK');
3271 hr_utility.raise_error;
3272 --
3273 end if;
3274 --
3275 -- Check if the enrollment is still valid as of the validation start
3276 -- and end dates.
3277 --
3278 if (p_object_version_number is not null
3279 and (nvl(p_effective_start_date,hr_api.g_date)
3280 <> g_old_rec.effective_start_date
3281 or nvl(p_effective_end_date,hr_api.g_date)
3282 <> g_old_rec.effective_end_date)) then
3283 --
3284 -- The dates have changed i.e. an update has occured or correction
3285 -- so make sure that the dependent effective dates are within the
3286 -- coverage period of the enrollment.
3287 --
3288 open c1;
3289 --
3290 fetch c1 into l_dummy;
3291 if c1%notfound then
3292 --
3293 close c1;
3294 --
3295 -- The dates for the covered dependent are outside of the dates of
3296 -- the cobra enrollment event. Raise ERROR.
3297 --
3298 hr_utility.set_message(801,'HR_52273_CDP_DEP_DATES');
3299 hr_utility.raise_error;
3300 --
3301 end if;
3302 --
3303 close c1;
3304 --
3305 end if;
3306 --
3307 hr_utility.set_location(' Leaving:'||l_proc, 10);
3308 --
3309 End chk_enrollment_id;
3310 --
3311 Procedure chk_contact_relationship_id
3312 (p_cobra_dependent_id in number,
3313 p_cobra_coverage_enrollment_id in number,
3314 p_contact_relationship_id in number,
3315 p_object_version_number in number) is
3316 --
3317 l_proc varchar2(72) := 'chk_contact_relationship_id';
3318 l_dummy varchar2(1);
3319 --
3320 cursor c1 is
3321 select null
3322 from per_contact_relationships cre
3323 where cre.contact_relationship_id = p_contact_relationship_id;
3324 --
3325 Begin
3326 --
3327 hr_utility.set_location('Entering:'||l_proc, 5);
3328 --
3329 if p_contact_relationship_id is null then
3330 --
3331 -- raise error as FK is null
3332 --
3333 hr_utility.set_message(801, 'HR_52274_CDP_INV_CONTACT');
3334 hr_utility.raise_error;
3335 --
3336 end if;
3337 --
3338 -- Make sure that contact exists in PER_CONTACT_RELATIONSHIPS table
3339 --
3340 open c1;
3341 --
3342 fetch c1 into l_dummy;
3343 if c1%notfound then
3344 --
3345 close c1;
3346 hr_utility.set_message(801, 'HR_52274_CDP_INV_CONTACT');
3347 hr_utility.raise_error;
3348 --
3349 end if;
3350 --
3351 close c1;
3352 --
3353 hr_utility.set_location(' Leaving:'||l_proc, 10);
3354 --
3355 End chk_contact_relationship_id;
3356 --
3357 Procedure chk_overlap (p_cobra_dependent_id in number,
3358 p_cobra_coverage_enrollment_id in number,
3359 p_contact_relationship_id in number,
3360 p_effective_start_date in date,
3361 p_effective_end_date in date,
3362 p_object_version_number in number) is
3363 --
3364 l_proc varchar2(72) := 'chk_overlap';
3365 l_dummy varchar2(1);
3366 --
3367 -- We do not link the dependent just to the one enrollment is as the
3368 -- dependent must not be covered more than once at any one time by any
3369 -- amount of employees. In other words coverage for a dependent can not
3370 -- be undertaken by multiple employees.
3371 --
3372 cursor c1 is
3373 select null
3374 from per_cobra_dependents_f cdp
3375 where cdp.contact_relationship_id = p_contact_relationship_id
3376 and cdp.cobra_coverage_enrollment_id = p_cobra_coverage_enrollment_id
3377 and cdp.cobra_dependent_id <> nvl(p_cobra_dependent_id,-1)
3378 and (p_effective_start_date
3379 between cdp.effective_start_date
3380 and cdp.effective_end_date
3381 or p_effective_end_date
3382 between cdp.effective_start_date
3383 and cdp.effective_end_date);
3384 --
3385 Begin
3386 --
3387 hr_utility.set_location('Entering:'||l_proc, 5);
3388 --
3389 -- check whether the new record already exists within the timeframe
3390 -- for the same dependent. A dependent can only be covered once by
3391 -- any enrollment for any particular timeframe.
3392 --
3393 open c1;
3394 --
3395 fetch c1 into l_dummy;
3396 if c1%found then
3397 --
3398 close c1;
3399 hr_utility.set_message(801,'HR_52275_CDP_DEP_COVERED');
3400 hr_utility.raise_error;
3401 --
3402 end if;
3403 --
3404 close c1;
3405 --
3406 hr_utility.set_location(' Leaving:'||l_proc, 10);
3407 --
3408 End chk_overlap;
3409 --
3410 Procedure chk_unique_key (p_cobra_dependent_id in number,
3411 p_contact_relationship_id in number,
3412 p_effective_start_date in date,
3413 p_effective_end_date in date) is
3414 --
3415 l_proc varchar2(72) := 'chk_unique_key';
3416 l_dummy varchar2(1);
3417 --
3418 cursor c1 is
3419 select null
3420 from per_cobra_dependents_f cdp
3421 where cdp.cobra_dependent_id = nvl(p_cobra_dependent_id,-1)
3422 and cdp.contact_relationship_id = p_contact_relationship_id
3423 and cdp.effective_start_date = p_effective_start_date
3424 and cdp.effective_end_date = p_effective_end_date;
3425 --
3426 Begin
3427 --
3428 hr_utility.set_location('Entering:'||l_proc, 5);
3429 --
3430 open c1;
3431 --
3432 fetch c1 into l_dummy;
3433 if c1%found then
3434 --
3435 close c1;
3436 hr_utility.set_message(801, 'HR_52276_CDP_DEP_UK');
3437 hr_utility.raise_error;
3438 --
3439 end if;
3440 --
3441 close c1;
3442 --
3443 hr_utility.set_location(' Leaving:'||l_proc, 10);
3444 --
3445 End chk_unique_key;
3446 --
3447 PROCEDURE hr_cobra_do_cdp_insert
3448 (p_cobra_dependent_id out nocopy number,
3449 p_cobra_coverage_enrollment_id in number,
3450 p_contact_relationship_id in number,
3451 p_effective_start_date in date,
3452 p_effective_end_date in date,
3453 p_object_version_number out nocopy number,
3454 p_attribute_category in varchar2,
3455 p_attribute1 in varchar2,
3456 p_attribute2 in varchar2,
3457 p_attribute3 in varchar2,
3458 p_attribute4 in varchar2,
3459 p_attribute5 in varchar2,
3460 p_attribute6 in varchar2,
3461 p_attribute7 in varchar2,
3462 p_attribute8 in varchar2,
3463 p_attribute9 in varchar2,
3464 p_attribute10 in varchar2,
3465 p_attribute11 in varchar2,
3466 p_attribute12 in varchar2,
3467 p_attribute13 in varchar2,
3468 p_attribute14 in varchar2,
3469 p_attribute15 in varchar2,
3470 p_attribute16 in varchar2,
3471 p_attribute17 in varchar2,
3472 p_attribute18 in varchar2,
3473 p_attribute19 in varchar2,
3474 p_attribute20 in varchar2) is
3475 --
3476 begin
3477 --
3478 -- Do business rule checks
3479 --
3480 chk_cobra_dependent_id
3481 (p_cobra_dependent_id => null,
3482 p_object_version_number => null);
3483 --
3484 chk_enrollment_id
3485 (p_cobra_dependent_id => null,
3486 p_cobra_coverage_enrollment_id => p_cobra_coverage_enrollment_id,
3487 p_effective_start_date => p_effective_start_date,
3488 p_effective_end_date => p_effective_end_date,
3489 p_object_version_number => null);
3490 --
3491 chk_contact_relationship_id
3492 (p_cobra_dependent_id => null,
3493 p_cobra_coverage_enrollment_id => p_cobra_coverage_enrollment_id,
3494 p_contact_relationship_id => p_contact_relationship_id,
3495 p_object_version_number => null);
3496 --
3497 chk_overlap
3498 (p_cobra_dependent_id => null,
3499 p_cobra_coverage_enrollment_id => p_cobra_coverage_enrollment_id,
3500 p_contact_relationship_id => p_contact_relationship_id,
3501 p_effective_start_date => p_effective_start_date,
3502 p_effective_end_date => p_effective_end_date,
3503 p_object_version_number => null);
3504 --
3505 chk_unique_key
3506 (p_cobra_dependent_id => null,
3507 p_contact_relationship_id => p_contact_relationship_id,
3508 p_effective_start_date => p_effective_start_date,
3509 p_effective_end_date => p_effective_end_date);
3510 --
3511 select per_cobra_dependents_s.nextval
3512 into p_cobra_dependent_id
3513 from dual;
3514 --
3515 p_object_version_number := 1;
3516 --
3517 insert into per_cobra_dependents_f
3518 (cobra_dependent_id,
3519 cobra_coverage_enrollment_id,
3520 contact_relationship_id,
3521 effective_start_date,
3522 effective_end_date,
3523 object_version_number,
3524 attribute_category,
3525 attribute1,
3526 attribute2,
3527 attribute3,
3528 attribute4,
3529 attribute5,
3530 attribute6,
3531 attribute7,
3532 attribute8,
3533 attribute9,
3534 attribute10,
3535 attribute11,
3536 attribute12,
3537 attribute13,
3538 attribute14,
3539 attribute15,
3540 attribute16,
3541 attribute17,
3542 attribute18,
3543 attribute19,
3544 attribute20
3545 )
3546 Values
3547 (p_cobra_dependent_id,
3548 p_cobra_coverage_enrollment_id,
3549 p_contact_relationship_id,
3550 p_effective_start_date,
3551 p_effective_end_date,
3552 p_object_version_number,
3553 p_attribute_category,
3554 p_attribute1,
3555 p_attribute2,
3556 p_attribute3,
3557 p_attribute4,
3558 p_attribute5,
3559 p_attribute6,
3560 p_attribute7,
3561 p_attribute8,
3562 p_attribute9,
3563 p_attribute10,
3564 p_attribute11,
3565 p_attribute12,
3566 p_attribute13,
3567 p_attribute14,
3568 p_attribute15,
3569 p_attribute16,
3570 p_attribute17,
3571 p_attribute18,
3572 p_attribute19,
3573 p_attribute20
3574 );
3575 end hr_cobra_do_cdp_insert;
3576 --
3577 PROCEDURE hr_cobra_do_cdp_update
3578 (p_row_id in varchar2,
3579 p_cobra_dependent_id in number,
3580 p_cobra_coverage_enrollment_id in number,
3581 p_contact_relationship_id in number,
3582 p_effective_start_date in date,
3583 p_effective_end_date in date,
3584 p_object_version_number in out nocopy number,
3585 p_attribute_category in varchar2,
3586 p_attribute1 in varchar2,
3587 p_attribute2 in varchar2,
3588 p_attribute3 in varchar2,
3589 p_attribute4 in varchar2,
3590 p_attribute5 in varchar2,
3591 p_attribute6 in varchar2,
3592 p_attribute7 in varchar2,
3593 p_attribute8 in varchar2,
3594 p_attribute9 in varchar2,
3595 p_attribute10 in varchar2,
3596 p_attribute11 in varchar2,
3597 p_attribute12 in varchar2,
3598 p_attribute13 in varchar2,
3599 p_attribute14 in varchar2,
3600 p_attribute15 in varchar2,
3601 p_attribute16 in varchar2,
3602 p_attribute17 in varchar2,
3603 p_attribute18 in varchar2,
3604 p_attribute19 in varchar2,
3605 p_attribute20 in varchar2) as
3606 --
3607 l_object_version_number number(9) := p_object_version_number + 1;
3608 --
3609 begin
3610 --
3611 -- Do business rule checks
3612 --
3613 chk_cobra_dependent_id
3614 (p_cobra_dependent_id => p_cobra_dependent_id,
3615 p_object_version_number => p_object_version_number);
3616 --
3617 chk_enrollment_id
3618 (p_cobra_dependent_id => p_cobra_dependent_id,
3619 p_cobra_coverage_enrollment_id => p_cobra_coverage_enrollment_id,
3620 p_effective_start_date => p_effective_start_date,
3621 p_effective_end_date => p_effective_end_date,
3622 p_object_version_number => p_object_version_number);
3623 --
3624 chk_contact_relationship_id
3625 (p_cobra_dependent_id => p_cobra_dependent_id,
3626 p_cobra_coverage_enrollment_id => p_cobra_coverage_enrollment_id,
3627 p_contact_relationship_id => p_contact_relationship_id,
3628 p_object_version_number => p_object_version_number);
3629 --
3630 chk_overlap
3631 (p_cobra_dependent_id => p_cobra_dependent_id,
3632 p_cobra_coverage_enrollment_id => p_cobra_coverage_enrollment_id,
3633 p_contact_relationship_id => p_contact_relationship_id,
3634 p_effective_start_date => p_effective_start_date,
3635 p_effective_end_date => p_effective_end_date,
3636 p_object_version_number => p_object_version_number);
3637 --
3638 chk_unique_key
3639 (p_cobra_dependent_id => p_cobra_dependent_id,
3640 p_contact_relationship_id => p_contact_relationship_id,
3641 p_effective_start_date => p_effective_start_date,
3642 p_effective_end_date => p_effective_end_date);
3643 --
3644 update per_cobra_dependents_f
3645 set effective_start_date = p_effective_start_date,
3646 effective_end_date = p_effective_end_date,
3647 contact_relationship_id = p_contact_relationship_id,
3648 object_version_number = l_object_version_number,
3649 attribute_category = p_attribute_category,
3650 attribute1 = p_attribute1,
3651 attribute2 = p_attribute2,
3652 attribute3 = p_attribute3,
3653 attribute4 = p_attribute4,
3654 attribute5 = p_attribute5,
3655 attribute6 = p_attribute6,
3656 attribute7 = p_attribute7,
3657 attribute8 = p_attribute8,
3658 attribute9 = p_attribute9,
3659 attribute10 = p_attribute10,
3660 attribute11 = p_attribute11,
3661 attribute12 = p_attribute12,
3662 attribute13 = p_attribute13,
3663 attribute14 = p_attribute14,
3664 attribute15 = p_attribute15,
3665 attribute16 = p_attribute16,
3666 attribute17 = p_attribute17,
3667 attribute18 = p_attribute18,
3668 attribute19 = p_attribute19,
3669 attribute20 = p_attribute20
3670 where rowid = p_row_id;
3671 --
3672 p_object_version_number := l_object_version_number;
3673 --
3674 end hr_cobra_do_cdp_update;
3675 --
3676 PROCEDURE hr_cobra_do_cdp_delete
3677 (p_cobra_dependent_id in number,
3678 p_effective_start_date in date,
3679 p_effective_end_date in date,
3680 p_object_version_number in number) is
3681 --
3682 l_proc varchar2(40) := g_package || 'hr_cobra_do_cdp_delete';
3683 begin
3684 --
3685 hr_utility.set_location('Entering.. ' || l_proc,10);
3686 hr_utility.trace('p_cobra_dependent_id = ' || p_cobra_dependent_id);
3687 hr_utility.trace('p_effective_start_date = ' || p_effective_start_date);
3688 hr_utility.trace('p_effective_end_date = ' || p_effective_end_date);
3689 hr_utility.trace('p_object_version_number= ' || p_object_version_number);
3690 --
3691 delete from per_cobra_dependents_f
3692 where cobra_dependent_id = p_cobra_dependent_id
3693 and effective_start_date = p_effective_start_date
3694 and effective_end_date = p_effective_end_date
3695 and object_version_number = p_object_version_number;
3696 --
3697 hr_utility.set_location('Leaving.. ' || l_proc,20);
3698 --
3699 end hr_cobra_do_cdp_delete;
3700 --
3701 procedure hr_cobra_do_cdp_lock ( p_cobra_dependent_id in number,
3702 p_effective_start_date in date,
3703 p_effective_end_date in date,
3704 p_object_version_number in number) is
3705 --
3706 -- declare local variables
3707 --
3708 l_lock_cdp VARCHAR2(30);
3709 l_object_invalid exception;
3710 --
3711 -- define cursor
3712 --
3713 cursor lock_cdp is
3714 select
3715 cobra_dependent_id,
3716 cobra_coverage_enrollment_id,
3717 contact_relationship_id,
3718 effective_start_date,
3719 effective_end_date,
3720 object_version_number,
3721 attribute_category,
3722 attribute1,
3723 attribute2,
3724 attribute3,
3725 attribute4,
3726 attribute5,
3727 attribute6,
3728 attribute7,
3729 attribute8,
3730 attribute9,
3731 attribute10,
3732 attribute11,
3733 attribute12,
3734 attribute13,
3735 attribute14,
3736 attribute15,
3737 attribute16,
3738 attribute17,
3739 attribute18,
3740 attribute19,
3741 attribute20
3742 from per_cobra_dependents_f
3743 where cobra_dependent_id = p_cobra_dependent_id
3744 and p_effective_start_date = effective_start_date
3745 and p_effective_end_date = effective_end_date
3746 for update nowait;
3747 --
3748 begin
3749 --
3750 -- lock table
3751 --
3752 open lock_cdp;
3753 --
3754 fetch lock_cdp into g_old_rec;
3755 if lock_cdp%notfound then
3756 close lock_cdp;
3757 --
3758 -- The primary key is invalid therefore we must error
3759 --
3760 hr_utility.set_message(801,'HR_7220_INVALID_PRIMARY_KEY');
3761 hr_utility.raise_error;
3762 --
3763 end if;
3764 --
3765 close lock_cdp;
3766 --
3767 if (p_object_version_number <> g_old_rec.object_version_number) Then
3768 hr_utility.set_message(801, 'HR_7155_OBJECT_INVALID');
3769 hr_utility.raise_error;
3770 end if;
3771 --
3772 exception
3773 when hr_api.object_locked then
3774 --
3775 -- The object is locked therefore we need to supply a meaningful
3776 -- error message.
3777 --
3778 hr_utility.set_message(801, 'HR_7165_OBJECT_LOCKED');
3779 hr_utility.set_message_token('TABLE_NAME', 'per_cobra_dependents_f');
3780 hr_utility.raise_error;
3781 --
3782 when l_object_invalid then
3783 --
3784 -- The object doesn't exist or is invalid
3785 --
3786 hr_utility.set_message(801, 'HR_7155_OBJECT_INVALID');
3787 hr_utility.set_message_token('TABLE_NAME', 'per_cobra_dependents_f');
3788 hr_utility.raise_error;
3789 --
3790 end hr_cobra_do_cdp_lock;
3791 --
3792 function dependent_born_in_coverage
3793 (p_contact_relationship_id in number,
3794 p_coverage_start_date in date,
3795 p_coverage_end_date in date) return boolean is
3796 --
3797 cursor c1 is
3798 select a.date_of_birth
3799 from per_people_f a,
3800 per_contact_relationships b
3801 where a.person_id = b.contact_person_id
3802 and b.contact_relationship_id = p_contact_relationship_id;
3803 --
3804 l_dob date;
3805 --
3806 begin
3807 --
3808 open c1;
3809 --
3810 fetch c1 into l_dob;
3811 --
3812 close c1;
3813 --
3814 if l_dob is not null then
3815 --
3816 -- Check if dependent birth date is between coverage start and end
3817 -- dates.
3818 --
3819 if l_dob
3820 between p_coverage_start_date
3821 and p_coverage_end_date then
3822 --
3823 return true;
3824 --
3825 else
3826 --
3827 return false;
3828 --
3829 end if;
3830 --
3831 else
3832 --
3833 -- Cannot derive birth date so return false, i.e. dependent birth date
3834 -- unknown.
3835 --
3836 return false;
3837 --
3838 end if;
3839 --
3840 end dependent_born_in_coverage;
3841 --
3842
3843 Function check_clashing_periods
3844 (p_cobra_coverage_enrollment_id in number,
3845 p_assignment_id in number,
3846 p_coverage_start_date in date,
3847 p_coverage_end_date in date,
3848 p_qualifying_event in varchar2) return boolean is
3849 --
3850 -- Ensure that two cobra events do not occur in the same timeframe
3851 -- Added qualifying_event condition to fix bug#4599753
3852 cursor c1 is
3853 select null
3854 from per_cobra_cov_enrollments a
3855 where a.cobra_coverage_enrollment_id <> nvl(p_cobra_coverage_enrollment_id,-1)
3856 and a.assignment_id = p_assignment_id
3857 and (p_coverage_start_date
3858 between a.coverage_start_date
3859 and a.coverage_end_date
3860 or
3861 p_coverage_end_date
3862 between a.coverage_start_date
3863 and a.coverage_end_date)
3864 and a.qualifying_event = p_qualifying_event;
3865 --
3866 l_dummy varchar2(1);
3867 --
3868 begin
3869 --
3870 -- Check if overlap of periods occurs!
3871 --
3872 open c1;
3873 --
3874 fetch c1 into l_dummy;
3875 if c1%found then
3876 --
3877 close c1;
3878 return true;
3879 --
3880 end if;
3881 --
3882 close c1;
3883 --
3884 return false;
3885 --
3886 end check_clashing_periods;
3887 --
3888 procedure check_date_invalidation
3889 (p_cobra_coverage_enrollment_id in number,
3890 p_coverage_start_date in date,
3891 p_coverage_end_date in date) is
3892 --
3893 -- Cursor checks that event dates don't affect any
3894 -- dependents who are linked to the enrollment
3895 --
3896 -- Bugs 609701 and 669253. Correct the cursor to prevent it raising
3897 -- the error when it shouldn't. The clause on the enrollment id
3898 -- was missing (bug 609701) and also the brackets (bug 669253).
3899 --
3900 cursor c1 is
3901 select null
3902 from per_cobra_dependents_f a
3903 where a.cobra_coverage_enrollment_id = p_cobra_coverage_enrollment_id
3904 and ( ( a.effective_start_date
3905 not between p_coverage_start_date
3906 and p_coverage_end_date )
3907 or
3908 ( a.effective_end_date
3909 not between p_coverage_start_date
3910 and p_coverage_end_date )
3911 );
3912 --
3913 l_dummy varchar2(1);
3914 --
3915 begin
3916 --
3917 open c1;
3918 --
3919 fetch c1 into l_dummy;
3920 if c1%found then
3921 --
3922 close c1;
3923 hr_utility.set_message(801,'HR_52277_CDP_DEP_INVALID');
3924 hr_utility.raise_error;
3925 --
3926 end if;
3927 --
3928 close c1;
3929 --
3930 end check_date_invalidation;
3931 --
3932 END per_cobra_eci;