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