[Home] [Help]
PACKAGE BODY: APPS.PER_COBRA_COV_STATUSES_PKG
Source
1 PACKAGE BODY PER_COBRA_COV_STATUSES_PKG as
2 /* $Header: pecobccs.pkb 115.1 99/07/17 18:50:00 porting ship $ */
3 --
4 PROCEDURE Insert_Row(X_Rowid IN OUT VARCHAR2,
5 X_Cobra_Coverage_Status_Id IN OUT NUMBER,
6 X_Business_Group_Id NUMBER,
7 X_Cobra_Coverage_Enrollment_Id NUMBER,
8 X_Cobra_Coverage_Status_Type VARCHAR2,
9 X_Effective_Date DATE,
10 X_Attribute_Category VARCHAR2,
11 X_Attribute1 VARCHAR2,
12 X_Attribute2 VARCHAR2,
13 X_Attribute3 VARCHAR2,
14 X_Attribute4 VARCHAR2,
15 X_Attribute5 VARCHAR2,
16 X_Attribute6 VARCHAR2,
17 X_Attribute7 VARCHAR2,
18 X_Attribute8 VARCHAR2,
19 X_Attribute9 VARCHAR2,
20 X_Attribute10 VARCHAR2,
21 X_Attribute11 VARCHAR2,
22 X_Attribute12 VARCHAR2,
23 X_Attribute13 VARCHAR2,
24 X_Attribute14 VARCHAR2,
25 X_Attribute15 VARCHAR2,
26 X_Attribute16 VARCHAR2,
27 X_Attribute17 VARCHAR2,
28 X_Attribute18 VARCHAR2,
29 X_Attribute19 VARCHAR2,
30 X_Attribute20 VARCHAR2,
31 X_Comments VARCHAR2
32 ) IS
33 CURSOR C IS SELECT rowid FROM per_cobra_coverage_statuses
34 WHERE cobra_coverage_status_id = X_Cobra_Coverage_Status_Id;
35 --
36 --
37 --
38 --
39 CURSOR C2 IS SELECT per_cobra_coverage_statuses_s.nextval FROM sys.dual;
40 BEGIN
41 --
42 hr_utility.set_location('per_cobra_cov_statuses_pkg hr_ccs.insert_row',1);
43 --
44 hr_utility.set_location('per_cobra_cov_statuses_pkg hr_ccs.insert_row',2);
45 --
46 if (X_Cobra_Coverage_Status_Id is NULL) then
47 OPEN C2;
48 FETCH C2 INTO X_Cobra_Coverage_Status_Id;
49 CLOSE C2;
50 end if;
51 --
52 hr_utility.set_location('per_cobra_cov_statuses_pkg hr_ccs.insert_row',3);
53 -- hr_utility.trace(X_Cobra_Coverage_Status_Id);
54 --
55 --
56 INSERT INTO per_cobra_coverage_statuses(
57 cobra_coverage_status_id,
58 business_group_id,
59 cobra_coverage_enrollment_id,
60 cobra_coverage_status_type,
61 effective_date,
62 attribute_category,
63 attribute1,
64 attribute2,
65 attribute3,
66 attribute4,
67 attribute5,
68 attribute6,
69 attribute7,
70 attribute8,
71 attribute9,
72 attribute10,
73 attribute11,
74 attribute12,
75 attribute13,
76 attribute14,
77 attribute15,
78 attribute16,
79 attribute17,
80 attribute18,
81 attribute19,
82 attribute20,
83 comments
84 ) VALUES (
85 X_Cobra_Coverage_Status_Id,
86 X_Business_Group_Id,
87 X_Cobra_Coverage_Enrollment_Id,
88 X_Cobra_Coverage_Status_Type,
89 X_Effective_Date,
90 X_Attribute_Category,
91 X_Attribute1,
92 X_Attribute2,
93 X_Attribute3,
94 X_Attribute4,
95 X_Attribute5,
96 X_Attribute6,
97 X_Attribute7,
98 X_Attribute8,
99 X_Attribute9,
100 X_Attribute10,
101 X_Attribute11,
102 X_Attribute12,
103 X_Attribute13,
104 X_Attribute14,
105 X_Attribute15,
106 X_Attribute16,
107 X_Attribute17,
108 X_Attribute18,
109 X_Attribute19,
110 X_Attribute20,
111 X_Comments
112 );
113 --
114 hr_utility.set_location('per_cobra_cov_statuses_pkg hr_ccs.insert_row',4);
115 --
116 --
117 OPEN C;
118 FETCH C INTO X_Rowid;
119 if (C%NOTFOUND) then
120 CLOSE C;
121 RAISE NO_DATA_FOUND;
122 end if;
123 CLOSE C;
124 --
125 hr_utility.set_location('per_cobra_cov_statuses_pkg hr_ccs.insert_row',5);
126 --
127 END Insert_Row;
128 --
129 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
130 X_Cobra_Coverage_Status_Id NUMBER,
131 X_Business_Group_Id NUMBER,
132 X_Cobra_Coverage_Enrollment_Id NUMBER,
133 X_Cobra_Coverage_Status_Type VARCHAR2,
134 X_Effective_Date DATE,
135 X_Attribute_Category VARCHAR2,
136 X_Attribute1 VARCHAR2,
137 X_Attribute2 VARCHAR2,
138 X_Attribute3 VARCHAR2,
139 X_Attribute4 VARCHAR2,
140 X_Attribute5 VARCHAR2,
141 X_Attribute6 VARCHAR2,
142 X_Attribute7 VARCHAR2,
143 X_Attribute8 VARCHAR2,
144 X_Attribute9 VARCHAR2,
145 X_Attribute10 VARCHAR2,
146 X_Attribute11 VARCHAR2,
147 X_Attribute12 VARCHAR2,
148 X_Attribute13 VARCHAR2,
149 X_Attribute14 VARCHAR2,
150 X_Attribute15 VARCHAR2,
151 X_Attribute16 VARCHAR2,
152 X_Attribute17 VARCHAR2,
153 X_Attribute18 VARCHAR2,
154 X_Attribute19 VARCHAR2,
155 X_Attribute20 VARCHAR2,
156 X_Comments VARCHAR2
157 ) IS
158 CURSOR C IS
159 SELECT *
160 FROM per_cobra_coverage_statuses
161 WHERE rowid = X_Rowid
162 FOR UPDATE of Cobra_Coverage_Status_Id NOWAIT;
163 Recinfo C%ROWTYPE;
164 BEGIN
165 --
166 hr_utility.set_location('per_cobra_cov_statuses_pkg lock_row ccs',0);
167 --
168 OPEN C;
169 FETCH C INTO Recinfo;
170 if (C%NOTFOUND) then
171 CLOSE C;
172 RAISE NO_DATA_FOUND;
173 end if;
174 CLOSE C;
175 --
176 -- Ensure that we're not tricked into thinking the record has
177 -- changed if the user has inserted using sqlplus leaving trailing spaces
178 --
179 Recinfo.attribute14 := RTRIM(Recinfo.attribute14);
180 Recinfo.attribute15 := RTRIM(Recinfo.attribute15);
181 Recinfo.attribute16 := RTRIM(Recinfo.attribute16);
182 Recinfo.attribute17 := RTRIM(Recinfo.attribute17);
183 Recinfo.attribute18 := RTRIM(Recinfo.attribute18);
184 Recinfo.attribute19 := RTRIM(Recinfo.attribute19);
185 Recinfo.attribute20 := RTRIM(Recinfo.attribute20);
186 Recinfo.cobra_coverage_status_type := RTRIM(Recinfo.cobra_coverage_status_type);
187 Recinfo.comments := RTRIM(Recinfo.comments);
188 Recinfo.attribute_category := RTRIM(Recinfo.attribute_category);
189 Recinfo.attribute1 := RTRIM(Recinfo.attribute1);
190 Recinfo.attribute2 := RTRIM(Recinfo.attribute2);
191 Recinfo.attribute3 := RTRIM(Recinfo.attribute3);
192 Recinfo.attribute4 := RTRIM(Recinfo.attribute4);
193 Recinfo.attribute5 := RTRIM(Recinfo.attribute5);
194 Recinfo.attribute6 := RTRIM(Recinfo.attribute6);
195 Recinfo.attribute7 := RTRIM(Recinfo.attribute7);
196 Recinfo.attribute8 := RTRIM(Recinfo.attribute8);
197 Recinfo.attribute9 := RTRIM(Recinfo.attribute9);
198 Recinfo.attribute10 := RTRIM(Recinfo.attribute10);
199 Recinfo.attribute11 := RTRIM(Recinfo.attribute11);
200 Recinfo.attribute12 := RTRIM(Recinfo.attribute12);
201 Recinfo.attribute13 := RTRIM(Recinfo.attribute13);
202 --
203 if (
204 ( (Recinfo.cobra_coverage_status_id = X_Cobra_Coverage_Status_Id)
205 OR ( (Recinfo.cobra_coverage_status_id IS NULL)
206 AND (X_Cobra_Coverage_Status_Id IS NULL)))
207 AND ( (Recinfo.business_group_id = X_Business_Group_Id)
208 OR ( (Recinfo.business_group_id IS NULL)
209 AND (X_Business_Group_Id IS NULL)))
210 AND ( (Recinfo.cobra_coverage_enrollment_id =
211 X_Cobra_Coverage_Enrollment_Id)
212 OR ( (Recinfo.cobra_coverage_enrollment_id IS NULL)
213 AND (X_Cobra_Coverage_Enrollment_Id IS NULL)))
214 AND ( (Recinfo.cobra_coverage_status_type =
215 X_Cobra_Coverage_Status_Type)
216 OR ( (Recinfo.cobra_coverage_status_type IS NULL)
217 AND (X_Cobra_Coverage_Status_Type IS NULL)))
218 AND ( (Recinfo.effective_date = X_Effective_Date)
219 OR ( (Recinfo.effective_date IS NULL)
220 AND (X_Effective_Date IS NULL)))
221 AND ( (Recinfo.attribute_category = X_Attribute_Category)
222 OR ( (Recinfo.attribute_category IS NULL)
223 AND (X_Attribute_Category IS NULL)))
224 AND ( (Recinfo.attribute1 = X_Attribute1)
225 OR ( (Recinfo.attribute1 IS NULL)
226 AND (X_Attribute1 IS NULL)))
227 AND ( (Recinfo.attribute2 = X_Attribute2)
228 OR ( (Recinfo.attribute2 IS NULL)
229 AND (X_Attribute2 IS NULL)))
230 AND ( (Recinfo.attribute3 = X_Attribute3)
231 OR ( (Recinfo.attribute3 IS NULL)
232 AND (X_Attribute3 IS NULL)))
233 AND ( (Recinfo.attribute4 = X_Attribute4)
234 OR ( (Recinfo.attribute4 IS NULL)
235 AND (X_Attribute4 IS NULL)))
236 AND ( (Recinfo.attribute5 = X_Attribute5)
237 OR ( (Recinfo.attribute5 IS NULL)
238 AND (X_Attribute5 IS NULL)))
239 AND ( (Recinfo.attribute6 = X_Attribute6)
240 OR ( (Recinfo.attribute6 IS NULL)
241 AND (X_Attribute6 IS NULL)))
242 AND ( (Recinfo.attribute7 = X_Attribute7)
243 OR ( (Recinfo.attribute7 IS NULL)
244 AND (X_Attribute7 IS NULL)))
245 AND ( (Recinfo.attribute8 = X_Attribute8)
246 OR ( (Recinfo.attribute8 IS NULL)
247 AND (X_Attribute8 IS NULL)))
248 AND ( (Recinfo.attribute9 = X_Attribute9)
249 OR ( (Recinfo.attribute9 IS NULL)
250 AND (X_Attribute9 IS NULL)))
251 AND ( (Recinfo.attribute10 = X_Attribute10)
252 OR ( (Recinfo.attribute10 IS NULL)
253 AND (X_Attribute10 IS NULL)))
254 AND ( (Recinfo.attribute11 = X_Attribute11)
255 OR ( (Recinfo.attribute11 IS NULL)
256 AND (X_Attribute11 IS NULL)))
257 AND ( (Recinfo.attribute12 = X_Attribute12)
258 OR ( (Recinfo.attribute12 IS NULL)
259 AND (X_Attribute12 IS NULL)))
260 AND ( (Recinfo.attribute13 = X_Attribute13)
261 OR ( (Recinfo.attribute13 IS NULL)
262 AND (X_Attribute13 IS NULL)))
263 AND ( (Recinfo.attribute14 = X_Attribute14)
264 OR ( (Recinfo.attribute14 IS NULL)
265 AND (X_Attribute14 IS NULL)))
266 AND ( (Recinfo.attribute15 = X_Attribute15)
267 OR ( (Recinfo.attribute15 IS NULL)
268 AND (X_Attribute15 IS NULL)))
269 AND ( (Recinfo.attribute16 = X_Attribute16)
270 OR ( (Recinfo.attribute16 IS NULL)
271 AND (X_Attribute16 IS NULL)))
272 AND ( (Recinfo.attribute17 = X_Attribute17)
273 OR ( (Recinfo.attribute17 IS NULL)
274 AND (X_Attribute17 IS NULL)))
275 AND ( (Recinfo.attribute18 = X_Attribute18)
276 OR ( (Recinfo.attribute18 IS NULL)
277 AND (X_Attribute18 IS NULL)))
278 AND ( (Recinfo.attribute19 = X_Attribute19)
279 OR ( (Recinfo.attribute19 IS NULL)
280 AND (X_Attribute19 IS NULL)))
281 AND ( (Recinfo.attribute20 = X_Attribute20)
282 OR ( (Recinfo.attribute20 IS NULL)
283 AND (X_Attribute20 IS NULL)))
284 AND ( (Recinfo.comments = X_Comments)
285 OR ( (Recinfo.comments IS NULL)
286 AND (X_Comments IS NULL)))
287 ) then
288 return;
289 else
290 hr_utility.set_message(0, 'FORM_RECORD_CHANGED');
291 hr_utility.raise_error;
292 end if;
293 END Lock_Row;
294 --
295 PROCEDURE Update_Row(X_Rowid VARCHAR2,
296 X_Business_Group_Id NUMBER,
297 X_Cobra_Coverage_Enrollment_Id NUMBER,
298 X_Cobra_Coverage_Status_Type VARCHAR2,
299 X_Effective_Date DATE,
300 X_Attribute_Category VARCHAR2,
301 X_Attribute1 VARCHAR2,
302 X_Attribute2 VARCHAR2,
303 X_Attribute3 VARCHAR2,
304 X_Attribute4 VARCHAR2,
305 X_Attribute5 VARCHAR2,
306 X_Attribute6 VARCHAR2,
307 X_Attribute7 VARCHAR2,
308 X_Attribute8 VARCHAR2,
309 X_Attribute9 VARCHAR2,
310 X_Attribute10 VARCHAR2,
311 X_Attribute11 VARCHAR2,
312 X_Attribute12 VARCHAR2,
313 X_Attribute13 VARCHAR2,
314 X_Attribute14 VARCHAR2,
315 X_Attribute15 VARCHAR2,
316 X_Attribute16 VARCHAR2,
317 X_Attribute17 VARCHAR2,
318 X_Attribute18 VARCHAR2,
319 X_Attribute19 VARCHAR2,
320 X_Attribute20 VARCHAR2,
321 X_Comments VARCHAR2
322 ) IS
326 --
323 BEGIN
324 --
325 hr_utility.set_location('per_cobra_cov_statuses_pkg update_row ccs',0);
327 UPDATE per_cobra_coverage_statuses
328 SET
329 business_group_id = X_Business_Group_Id,
330 cobra_coverage_enrollment_id = X_Cobra_Coverage_Enrollment_Id,
331 cobra_coverage_status_type = X_Cobra_Coverage_Status_Type,
332 effective_date = X_Effective_Date,
333 attribute_category = X_Attribute_Category,
334 attribute1 = X_Attribute1,
335 attribute2 = X_Attribute2,
336 attribute3 = X_Attribute3,
337 attribute4 = X_Attribute4,
338 attribute5 = X_Attribute5,
339 attribute6 = X_Attribute6,
340 attribute7 = X_Attribute7,
341 attribute8 = X_Attribute8,
342 attribute9 = X_Attribute9,
343 attribute10 = X_Attribute10,
344 attribute11 = X_Attribute11,
345 attribute12 = X_Attribute12,
346 attribute13 = X_Attribute13,
347 attribute14 = X_Attribute14,
348 attribute15 = X_Attribute15,
349 attribute16 = X_Attribute16,
350 attribute17 = X_Attribute17,
351 attribute18 = X_Attribute18,
352 attribute19 = X_Attribute19,
353 attribute20 = X_Attribute20,
354 comments = X_Comments
355 WHERE rowid = X_rowid;
356 --
357 if (SQL%NOTFOUND) then
358 RAISE NO_DATA_FOUND;
359 end if;
360 --
361 END Update_Row;
362 --
363 --
364 --
365 PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
366 BEGIN
367 --
368 hr_utility.set_location('per_cobra_cov_statuses_pkg delete_row',0);
369 --
370 DELETE FROM per_cobra_coverage_statuses
371 WHERE rowid = X_Rowid;
372
373 if (SQL%NOTFOUND) then
374 RAISE NO_DATA_FOUND;
375 end if;
376 END Delete_Row;
377 --
378 --
379 --
380 -- Name hr_cobra_chk_status_unique
381 --
382 -- Purpose
383 --
384 -- Ensures that the status being entered is unique
385 --
386 -- Arguments
387 --
388 -- Example
389 --
390 -- Notes
391 --
392 PROCEDURE hr_cobra_chk_status_unique ( p_business_group_id NUMBER,
393 p_cobra_coverage_status_id NUMBER,
394 p_cobra_coverage_enrollment_id NUMBER,
395 p_cobra_coverage_status_type VARCHAR2) IS
396 --
397 -- declare local variables
398 --
399 l_status_exists VARCHAR2(1) := 'N';
400 --
401 -- declare cursor
402 --
403 CURSOR status IS
404 SELECT 'Y'
405 FROM per_cobra_coverage_statuses ccs
406 WHERE ccs.business_group_id + 0 = p_business_group_id
407 AND ccs.cobra_coverage_enrollment_id = p_cobra_coverage_enrollment_id
408 AND ccs.cobra_coverage_status_type = p_cobra_coverage_status_type
409 AND ( ccs.cobra_coverage_status_id <> p_cobra_coverage_status_id
410 OR p_cobra_coverage_status_id IS NULL);
411 --
412 BEGIN
413 --
414 hr_utility.set_location('per_cobra_cov_statuses_pkg ccs hr_cobra_chk_status_unique',0);
415 --
416 --
417 -- get status
418 --
419 OPEN status;
420 FETCH status INTO l_status_exists;
421 CLOSE status;
422 --
423 hr_utility.set_location('per_cobra_cov_statuses_pkg ccs hr_cobra_chk_status_unique',1);
424 --
425 --
426 -- Check flag
427 --
428 IF (l_status_exists = 'Y')
429 THEN
430 --
431 hr_utility.set_location('per_cobra_cov_statuses_pkg ccs hr_cobra_chk_status_unique',2);
432 --
433 -- raise error
434 --
435 hr_utility.set_message(801, 'HR_13143_COBRA_DUP_STATUS');
436 --
437 hr_utility.set_location('per_cobra_cov_statuses_pkg ccs hr_cobra_chk_status_unique',2.5);
438 --
439 hr_utility.raise_error;
440 --
441 END IF;
442 --
443 hr_utility.set_location('per_cobra_cov_statuses_pkg ccs hr_cobra_chk_status_unique',3);
444 --
445 --
446 END hr_cobra_chk_status_unique;
447 --
448 --
449 --
450 -- Name
451 --
452 -- Purpose
453 --
454 -- Ensures status inserted in correct order
455 --
456 -- Arguments
457 --
458 -- Example
459 --
460 -- Notes
461 --
462 --
463 PROCEDURE hr_cobra_chk_status_order ( p_business_group_id NUMBER,
464 p_cobra_coverage_enrollment_id NUMBER,
465 p_cobra_coverage_status_id NUMBER,
466 p_cobra_coverage_status_type VARCHAR2,
467 p_effective_date DATE ) IS
468 --
469 -- declare local variables
470 --
471 l_status_order_ok VARCHAR2(1) := 'N';
472 --
473 -- declare cursors
474 --
475 CURSOR elect_or_reject_status IS
476 SELECT 'Y'
477 FROM per_cobra_coverage_statuses ccs
478 WHERE ccs.business_group_id + 0 = p_business_group_id
479 AND ccs.cobra_coverage_enrollment_id = p_cobra_coverage_enrollment_id
480 AND ( ccs.cobra_coverage_status_id <> p_cobra_coverage_status_id
481 OR p_cobra_coverage_status_id IS NULL )
482 AND ccs.cobra_coverage_status_type IN ('ELEC', 'REJ')
483 AND ccs.effective_date <= p_effective_date;
484 --
488 WHERE ccs.business_group_id + 0 = p_business_group_id
485 CURSOR notified_status IS
486 SELECT 'Y'
487 FROM per_cobra_coverage_statuses ccs
489 AND ccs.cobra_coverage_enrollment_id = p_cobra_coverage_enrollment_id
490 AND ( ccs.cobra_coverage_status_id <> p_cobra_coverage_status_id
491 OR p_cobra_coverage_status_id IS NULL )
492 AND ccs.cobra_coverage_status_type = 'NOT'
493 AND ccs.effective_date <= p_effective_date;
494 --
495 BEGIN
496 --
497 hr_utility.set_location('per_cobra_cov_statuses_pkg ccs pkg - chk correct order', 1);
498 --
499 --
500 -- chk terminated
501 --
502 IF (p_cobra_coverage_status_type = 'TERM')
503 THEN
504 --
505 hr_utility.set_location('per_cobra_cov_statuses_pkg ccs pkg - chk correct order', 2);
506 --
507 -- chk elect or reject exist
508 --
509 OPEN elect_or_reject_status;
510 FETCH elect_or_reject_status INTO l_status_order_ok;
511 CLOSE elect_or_reject_status;
512 --
513 --
514 -- check elect /reject
515 --
516 ELSIF (p_cobra_coverage_status_type IN ('ELEC', 'REJ'))
517 THEN
518 --
519 hr_utility.set_location('per_cobra_cov_statuses_pkg ccs pkg - chk correct order', 3);
520 --
521 -- chk notified exists
522 --
523 OPEN notified_status;
524 FETCH notified_status INTO l_status_order_ok;
525 CLOSE notified_status;
526 --
527 ELSE
528 -- do nothing
529 RETURN;
530 END IF;
531 --
532 -- check order ok
533 --
534 IF (l_status_order_ok = 'N')
535 THEN
536 --
537 hr_utility.set_location('per_cobra_cov_statuses_pkg ccs pkg - chk correct order', 4);
538 --
539 -- chk which error to raise
540 --
541 IF (p_cobra_coverage_status_type = 'TERM')
542 THEN
543 --
544 hr_utility.set_location('per_cobra_cov_statuses_pkg ccs pkg - chk correct order', 5);
545 --
546 -- Elect/Reject before Term error
547 --
548 hr_utility.set_message(801, 'HR_13134_COBRA_CANT_TERM_YET');
549 hr_utility.raise_error;
550 ELSE
551 --
552 hr_utility.set_location('per_cobra_cov_statuses_pkg ccs pkg - chk correct order', 6);
553 --
554 -- Notify before Elect/Reject error
555 --
556 hr_utility.set_message(801, 'HR_13135_COBRA_CANT_EL_REJ_YET');
557 hr_utility.raise_error;
558 END IF;
559 --
560 hr_utility.set_location('per_cobra_cov_statuses_pkg ccs pkg - chk correct order', 7);
561 END IF;
562 END hr_cobra_chk_status_order;
563 --
564 --
565 --
566 -- Name hr_cobra_chk_status_elect_rej
567 --
568 -- Purpose
569 --
570 -- Ensures that Accept/Reject do not coexist
571 --
572 -- Arguments
573 --
574 -- Example
575 --
576 -- Notes
577 --
578 PROCEDURE hr_cobra_chk_status_elect_rej( p_business_group_id NUMBER,
579 p_cobra_coverage_enrollment_id NUMBER,
580 p_cobra_coverage_status_id NUMBER,
581 p_cobra_coverage_status_type VARCHAR2) IS
582 --
583 -- declare local variables
584 --
585 l_status_ok VARCHAR2(1) := 'Y';
586 --
587 -- declare cursor
588 --
589 CURSOR elect_reject IS
590 SELECT 'N'
591 FROM per_cobra_coverage_statuses ccs
592 WHERE ccs.business_group_id + 0 = p_business_group_id
593 AND ccs.cobra_coverage_enrollment_id = p_cobra_coverage_enrollment_id
594 AND ccs.cobra_coverage_status_type IN ('ELEC', 'REJ')
595 AND ccs.cobra_coverage_status_type = DECODE(p_cobra_coverage_status_type,
596 'ELEC', 'REJ',
597 'ELEC');
598 --
599 BEGIN
600 --
601 hr_utility.set_location('per_cobra_cov_statuses_pkg ccs pkg - chk elect reject', 1);
602 --
603 -- check if elect / reject co-exist
604 --
605 OPEN elect_reject;
606 FETCH elect_reject INTO l_status_ok;
607 CLOSE elect_reject;
608 --
609 -- chk if Elect/Reject rule violated
610 --
611 IF (l_status_ok = 'N')
612 THEN
613 --
614 hr_utility.set_location('per_cobra_cov_statuses_pkg ccs pkg - chk elect reject', 2);
615 --
616 -- Reject/Elect cannot co-exist error
617 --
618 hr_utility.set_message(801, 'HR_13136_COBRA_EL_REJ_NOT_COEX');
619 hr_utility.raise_error;
620 END IF;
621 --
622 hr_utility.set_location('per_cobra_cov_statuses_pkg ccs pkg - chk elect reject', 3);
623 --
624 --
625 END hr_cobra_chk_status_elect_rej;
626 --
627 --
628 --
629 --
630 END PER_COBRA_COV_STATUSES_PKG;