[Home] [Help]
PACKAGE BODY: APPS.PER_COBRA_COV_ENROLLMENTS_PKG
Source
1 PACKAGE BODY PER_COBRA_COV_ENROLLMENTS_PKG as
2 /* $Header: pecobcce.pkb 120.0 2006/04/18 18:14:28 ssouresr noship $ */
3 --
4 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
5 X_Cobra_Coverage_Enrollment_Id IN OUT NOCOPY NUMBER,
6 X_Business_Group_Id NUMBER,
7 X_Assignment_Id NUMBER,
8 X_Period_Type VARCHAR2,
9 X_Qualifying_Date DATE,
10 X_Qualifying_Event VARCHAR2,
11 X_Coverage_End_Date DATE,
12 X_Coverage_Start_Date DATE,
13 X_Termination_Reason VARCHAR2,
14 X_Contact_Relationship_Id NUMBER,
15 X_Attribute_Category VARCHAR2,
16 X_Attribute1 VARCHAR2,
17 X_Attribute2 VARCHAR2,
18 X_Attribute3 VARCHAR2,
19 X_Attribute4 VARCHAR2,
20 X_Attribute5 VARCHAR2,
21 X_Attribute6 VARCHAR2,
22 X_Attribute7 VARCHAR2,
23 X_Attribute8 VARCHAR2,
24 X_Attribute9 VARCHAR2,
25 X_Attribute10 VARCHAR2,
26 X_Attribute11 VARCHAR2,
27 X_Attribute12 VARCHAR2,
28 X_Attribute13 VARCHAR2,
29 X_Attribute14 VARCHAR2,
30 X_Attribute15 VARCHAR2,
31 X_Attribute16 VARCHAR2,
32 X_Attribute17 VARCHAR2,
33 X_Attribute18 VARCHAR2,
34 X_Attribute19 VARCHAR2,
35 X_Attribute20 VARCHAR2,
36 X_Grace_Days NUMBER,
37 X_Comments VARCHAR2
38 ) IS
39 CURSOR C IS SELECT rowid FROM per_cobra_cov_enrollments
40 WHERE cobra_coverage_enrollment_id = X_Cobra_Coverage_Enrollment_Id;
41 --
42 --
43 --
44 --
45 --
46 CURSOR C2 IS SELECT per_cobra_cov_enrollments_s.nextval FROM sys.dual;
47 BEGIN
48 --
49 hr_utility.set_location('cce insert_row', 0);
50 --
51 if (X_Cobra_Coverage_Enrollment_Id is NULL) then
52 OPEN C2;
53 FETCH C2 INTO X_Cobra_Coverage_Enrollment_Id;
54 CLOSE C2;
55 end if;
56 --
57 INSERT INTO per_cobra_cov_enrollments(
58 cobra_coverage_enrollment_id,
59 business_group_id,
60 assignment_id,
61 period_type,
62 qualifying_date,
63 qualifying_event,
64 coverage_end_date,
65 coverage_start_date,
66 termination_reason,
67 contact_relationship_id,
68 attribute_category,
69 attribute1,
70 attribute2,
71 attribute3,
72 attribute4,
73 attribute5,
74 attribute6,
75 attribute7,
76 attribute8,
77 attribute9,
78 attribute10,
79 attribute11,
80 attribute12,
81 attribute13,
82 attribute14,
83 attribute15,
84 attribute16,
85 attribute17,
86 attribute18,
87 attribute19,
88 attribute20,
89 grace_days,
90 comments
91 ) VALUES (
92 X_Cobra_Coverage_Enrollment_Id,
93 X_Business_Group_Id,
94 X_Assignment_Id,
95 X_Period_Type,
96 X_Qualifying_Date,
97 X_Qualifying_Event,
98 X_Coverage_End_Date,
99 X_Coverage_Start_Date,
100 X_Termination_Reason,
101 X_Contact_Relationship_Id,
102 X_Attribute_Category,
103 X_Attribute1,
104 X_Attribute2,
105 X_Attribute3,
106 X_Attribute4,
107 X_Attribute5,
108 X_Attribute6,
109 X_Attribute7,
110 X_Attribute8,
111 X_Attribute9,
112 X_Attribute10,
113 X_Attribute11,
114 X_Attribute12,
115 X_Attribute13,
116 X_Attribute14,
117 X_Attribute15,
118 X_Attribute16,
119 X_Attribute17,
120 X_Attribute18,
121 X_Attribute19,
122 X_Attribute20,
123 X_Grace_Days,
124 X_comments
125 );
126 --
127 OPEN C;
128 FETCH C INTO X_Rowid;
129 if (C%NOTFOUND) then
130 CLOSE C;
131 RAISE NO_DATA_FOUND;
132 end if;
133 CLOSE C;
134 END Insert_Row;
135 --
136 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
137 X_Cobra_Coverage_Enrollment_Id NUMBER,
138 X_Business_Group_Id NUMBER,
139 X_Assignment_Id NUMBER,
140 X_Period_Type VARCHAR2,
141 X_Qualifying_Date DATE,
142 X_Qualifying_Event VARCHAR2,
143 X_Coverage_End_Date DATE,
144 X_Coverage_Start_Date DATE,
145 X_Termination_Reason VARCHAR2,
146 X_Contact_Relationship_Id NUMBER,
147 X_Attribute_Category VARCHAR2,
148 X_Attribute1 VARCHAR2,
149 X_Attribute2 VARCHAR2,
150 X_Attribute3 VARCHAR2,
151 X_Attribute4 VARCHAR2,
152 X_Attribute5 VARCHAR2,
153 X_Attribute6 VARCHAR2,
154 X_Attribute7 VARCHAR2,
155 X_Attribute8 VARCHAR2,
156 X_Attribute9 VARCHAR2,
157 X_Attribute10 VARCHAR2,
158 X_Attribute11 VARCHAR2,
159 X_Attribute12 VARCHAR2,
160 X_Attribute13 VARCHAR2,
161 X_Attribute14 VARCHAR2,
162 X_Attribute15 VARCHAR2,
163 X_Attribute16 VARCHAR2,
164 X_Attribute17 VARCHAR2,
165 X_Attribute18 VARCHAR2,
166 X_Attribute19 VARCHAR2,
167 X_Attribute20 VARCHAR2,
168 X_Grace_Days NUMBER,
169 X_Comments VARCHAR2) IS
170 CURSOR C IS
171 SELECT *
172 FROM per_cobra_cov_enrollments
173 WHERE rowid = X_Rowid
174 FOR UPDATE of COBRA_COVERAGE_ENROLLMENT_ID NOWAIT;
175 /* FOR UPDATE of COBRA_COVERAGE_ENROLLMENT_ID NOWAIT; */
176 Recinfo C%ROWTYPE;
177 BEGIN
178 --
179 hr_utility.set_location('cce lock_row', 0);
180 --
181 OPEN C;
182 FETCH C INTO Recinfo;
183 if (C%NOTFOUND) then
184 CLOSE C;
185 RAISE NO_DATA_FOUND;
186 end if;
187 CLOSE C;
188 --
189 -- Ensure that we're not tricked into thinking the record has
190 -- changed if the user has inserted using sqlplus leaving trailing spaces
191 --
192 Recinfo.attribute9 := RTRIM(Recinfo.attribute9);
193 Recinfo.attribute10 := RTRIM(Recinfo.attribute10);
194 Recinfo.attribute11 := RTRIM(Recinfo.attribute11);
195 Recinfo.attribute12 := RTRIM(Recinfo.attribute12);
196 Recinfo.attribute13 := RTRIM(Recinfo.attribute13);
197 Recinfo.attribute14 := RTRIM(Recinfo.attribute14);
198 Recinfo.attribute15 := RTRIM(Recinfo.attribute15);
199 Recinfo.attribute16 := RTRIM(Recinfo.attribute16);
200 Recinfo.attribute17 := RTRIM(Recinfo.attribute17);
201 Recinfo.attribute18 := RTRIM(Recinfo.attribute18);
202 Recinfo.attribute19 := RTRIM(Recinfo.attribute19);
203 Recinfo.attribute20 := RTRIM(Recinfo.attribute20);
204 Recinfo.period_type := RTRIM(Recinfo.period_type);
205 Recinfo.qualifying_event := RTRIM(Recinfo.qualifying_event);
206 Recinfo.comments := RTRIM(Recinfo.comments);
207 Recinfo.termination_reason := RTRIM(Recinfo.termination_reason);
208 Recinfo.attribute_category := RTRIM(Recinfo.attribute_category);
209 Recinfo.attribute1 := RTRIM(Recinfo.attribute1);
210 Recinfo.attribute2 := RTRIM(Recinfo.attribute2);
211 Recinfo.attribute3 := RTRIM(Recinfo.attribute3);
212 Recinfo.attribute4 := RTRIM(Recinfo.attribute4);
213 Recinfo.attribute5 := RTRIM(Recinfo.attribute5);
214 Recinfo.attribute6 := RTRIM(Recinfo.attribute6);
215 Recinfo.attribute7 := RTRIM(Recinfo.attribute7);
216 Recinfo.attribute8 := RTRIM(Recinfo.attribute8);
217 --
218 if (
219 ( (Recinfo.cobra_coverage_enrollment_id =
220 X_Cobra_Coverage_Enrollment_Id)
221 OR ( (Recinfo.cobra_coverage_enrollment_id IS NULL)
222 AND (X_Cobra_Coverage_Enrollment_Id IS NULL)))
223 AND ( (Recinfo.business_group_id = X_Business_Group_Id)
224 OR ( (Recinfo.business_group_id IS NULL)
225 AND (X_Business_Group_Id IS NULL)))
226 AND ( (Recinfo.assignment_id = X_Assignment_Id)
227 OR ( (Recinfo.assignment_id IS NULL)
228 AND (X_Assignment_Id IS NULL)))
229 AND ( (Recinfo.period_type = X_Period_Type)
230 OR ( (Recinfo.period_type IS NULL)
231 AND (X_Period_Type IS NULL)))
232 AND ( (Recinfo.qualifying_date = X_Qualifying_Date)
233 OR ( (Recinfo.qualifying_date IS NULL)
234 AND (X_Qualifying_Date IS NULL)))
235 AND ( (Recinfo.qualifying_event = X_Qualifying_Event)
236 OR ( (Recinfo.qualifying_event IS NULL)
237 AND (X_Qualifying_Event IS NULL)))
238 AND ( (Recinfo.coverage_end_date = X_Coverage_End_Date)
239 OR ( (Recinfo.coverage_end_date IS NULL)
240 AND (X_Coverage_End_Date IS NULL)))
241 AND ( (Recinfo.coverage_start_date = X_Coverage_Start_Date)
242 OR ( (Recinfo.coverage_start_date IS NULL)
243 AND (X_Coverage_Start_Date IS NULL)))
244 AND ( (Recinfo.termination_reason = X_Termination_Reason)
245 OR ( (Recinfo.termination_reason IS NULL)
246 AND (X_Termination_Reason IS NULL)))
247 AND ( (Recinfo.contact_relationship_id = X_Contact_Relationship_Id)
248 OR ( (Recinfo.contact_relationship_id IS NULL)
249 AND (X_Contact_Relationship_Id IS NULL)))
250 AND ( (Recinfo.attribute_category = X_Attribute_Category)
251 OR ( (Recinfo.attribute_category IS NULL)
252 AND (X_Attribute_Category IS NULL)))
253 AND ( (Recinfo.attribute1 = X_Attribute1)
254 OR ( (Recinfo.attribute1 IS NULL)
255 AND (X_Attribute1 IS NULL)))
256 AND ( (Recinfo.attribute2 = X_Attribute2)
257 OR ( (Recinfo.attribute2 IS NULL)
258 AND (X_Attribute2 IS NULL)))
259 AND ( (Recinfo.attribute3 = X_Attribute3)
260 OR ( (Recinfo.attribute3 IS NULL)
261 AND (X_Attribute3 IS NULL)))
262 AND ( (Recinfo.attribute4 = X_Attribute4)
263 OR ( (Recinfo.attribute4 IS NULL)
264 AND (X_Attribute4 IS NULL)))
265 AND ( (Recinfo.attribute5 = X_Attribute5)
266 OR ( (Recinfo.attribute5 IS NULL)
267 AND (X_Attribute5 IS NULL)))
268 AND ( (Recinfo.attribute6 = X_Attribute6)
269 OR ( (Recinfo.attribute6 IS NULL)
270 AND (X_Attribute6 IS NULL)))
271 AND ( (Recinfo.attribute7 = X_Attribute7)
272 OR ( (Recinfo.attribute7 IS NULL)
273 AND (X_Attribute7 IS NULL)))
274 AND ( (Recinfo.attribute8 = X_Attribute8)
275 OR ( (Recinfo.attribute8 IS NULL)
276 AND (X_Attribute8 IS NULL)))
277 AND ( (Recinfo.attribute9 = X_Attribute9)
278 OR ( (Recinfo.attribute9 IS NULL)
279 AND (X_Attribute9 IS NULL)))
280 AND ( (Recinfo.attribute10 = X_Attribute10)
281 OR ( (Recinfo.attribute10 IS NULL)
282 AND (X_Attribute10 IS NULL)))
283 AND ( (Recinfo.attribute11 = X_Attribute11)
284 OR ( (Recinfo.attribute11 IS NULL)
285 AND (X_Attribute11 IS NULL)))
286 AND ( (Recinfo.attribute12 = X_Attribute12)
287 OR ( (Recinfo.attribute12 IS NULL)
288 AND (X_Attribute12 IS NULL)))
289 AND ( (Recinfo.attribute13 = X_Attribute13)
290 OR ( (Recinfo.attribute13 IS NULL)
291 AND (X_Attribute13 IS NULL)))
292 AND ( (Recinfo.attribute14 = X_Attribute14)
293 OR ( (Recinfo.attribute14 IS NULL)
294 AND (X_Attribute14 IS NULL)))
295 AND ( (Recinfo.attribute15 = X_Attribute15)
296 OR ( (Recinfo.attribute15 IS NULL)
297 AND (X_Attribute15 IS NULL)))
298 AND ( (Recinfo.attribute16 = X_Attribute16)
299 OR ( (Recinfo.attribute16 IS NULL)
300 AND (X_Attribute16 IS NULL)))
301 AND ( (Recinfo.attribute17 = X_Attribute17)
302 OR ( (Recinfo.attribute17 IS NULL)
303 AND (X_Attribute17 IS NULL)))
304 AND ( (Recinfo.attribute18 = X_Attribute18)
305 OR ( (Recinfo.attribute18 IS NULL)
306 AND (X_Attribute18 IS NULL)))
307 AND ( (Recinfo.attribute19 = X_Attribute19)
308 OR ( (Recinfo.attribute19 IS NULL)
309 AND (X_Attribute19 IS NULL)))
310 AND ( (Recinfo.attribute20 = X_Attribute20)
311 OR ( (Recinfo.attribute20 IS NULL)
312 AND (X_Attribute20 IS NULL)))
313 AND ( (Recinfo.grace_days = X_Grace_Days)
314 OR ( (Recinfo.grace_days IS NULL)
315 AND (X_Grace_Days IS NULL)))
316 AND ( (Recinfo.comments = X_comments)
317 OR ( (Recinfo.comments IS NULL)
318 AND (X_comments IS NULL)))
319 ) then
320 return;
321 else
322 hr_utility.set_message(0, 'FORM_RECORD_CHANGED');
323 hr_utility.raise_error;
324 end if;
325 END Lock_Row;
326 --
327 PROCEDURE Update_Row(X_Rowid VARCHAR2,
328 X_Business_Group_Id NUMBER,
329 X_Assignment_Id NUMBER,
330 X_Period_Type VARCHAR2,
331 X_Qualifying_Date DATE,
332 X_Qualifying_Event VARCHAR2,
333 X_Coverage_End_Date DATE,
334 X_Coverage_Start_Date DATE,
335 X_Termination_Reason VARCHAR2,
336 X_Contact_Relationship_Id NUMBER,
337 X_Attribute_Category VARCHAR2,
338 X_Attribute1 VARCHAR2,
339 X_Attribute2 VARCHAR2,
340 X_Attribute3 VARCHAR2,
341 X_Attribute4 VARCHAR2,
342 X_Attribute5 VARCHAR2,
343 X_Attribute6 VARCHAR2,
344 X_Attribute7 VARCHAR2,
345 X_Attribute8 VARCHAR2,
346 X_Attribute9 VARCHAR2,
347 X_Attribute10 VARCHAR2,
348 X_Attribute11 VARCHAR2,
349 X_Attribute12 VARCHAR2,
350 X_Attribute13 VARCHAR2,
351 X_Attribute14 VARCHAR2,
352 X_Attribute15 VARCHAR2,
353 X_Attribute16 VARCHAR2,
354 X_Attribute17 VARCHAR2,
355 X_Attribute18 VARCHAR2,
356 X_Attribute19 VARCHAR2,
357 X_Attribute20 VARCHAR2,
361 BEGIN
358 X_Grace_Days NUMBER,
359 X_Comments VARCHAR2
360 ) IS
362 --
363 hr_utility.set_location('cce update_row', 0);
364 --
365 UPDATE per_cobra_cov_enrollments
366 SET
367 business_group_id = X_Business_Group_Id,
368 assignment_id = X_Assignment_Id,
369 period_type = X_Period_Type,
370 qualifying_date = X_Qualifying_Date,
371 qualifying_event = X_Qualifying_Event,
372 coverage_end_date = X_Coverage_End_Date,
373 coverage_start_date = X_Coverage_Start_Date,
374 termination_reason = X_Termination_Reason,
375 contact_relationship_id = X_Contact_Relationship_Id,
376 attribute_category = X_Attribute_Category,
377 attribute1 = X_Attribute1,
378 attribute2 = X_Attribute2,
379 attribute3 = X_Attribute3,
380 attribute4 = X_Attribute4,
381 attribute5 = X_Attribute5,
382 attribute6 = X_Attribute6,
383 attribute7 = X_Attribute7,
384 attribute8 = X_Attribute8,
385 attribute9 = X_Attribute9,
386 attribute10 = X_Attribute10,
387 attribute11 = X_Attribute11,
388 attribute12 = X_Attribute12,
389 attribute13 = X_Attribute13,
390 attribute14 = X_Attribute14,
391 attribute15 = X_Attribute15,
392 attribute16 = X_Attribute16,
393 attribute17 = X_Attribute17,
394 attribute18 = X_Attribute18,
395 attribute19 = X_Attribute19,
396 attribute20 = X_Attribute20,
397 grace_days = X_Grace_Days,
398 comments = X_Comments
399 WHERE rowid = X_rowid;
400 --
401 if (SQL%NOTFOUND) then
402 RAISE NO_DATA_FOUND;
403 end if;
404 --
405 END Update_Row;
406 --
407 PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
408 BEGIN
409 --
410 hr_utility.set_location('cce delete_row', 0);
411 --
412 DELETE FROM per_cobra_cov_enrollments
413 WHERE rowid = X_Rowid;
414 --
415 if (SQL%NOTFOUND) then
416 RAISE NO_DATA_FOUND;
417 end if;
418 END Delete_Row;
419 --
420 --
421 --
422 -- Name hr_cobra_chk_unique_enrollment
423 --
424 -- Purpose
425 --
426 -- Checks that the enrollment entered is unique
427 --
428 -- Arguments
429 --
430 -- p_cobra_coverage_enrollment_id
431 -- p_assignment_id
432 -- p_contact_relationship_id
433 -- p_qualifying_event
434 -- p_qualifying_date
435 --
436 -- Example
437 --
438 -- Notes
439 --
440 --
441 PROCEDURE hr_cobra_chk_unique_enrollment ( p_cobra_coverage_enrollment_id NUMBER,
442 p_assignment_id NUMBER,
443 p_contact_relationship_id NUMBER,
447 -- declare local variables
444 p_qualifying_event VARCHAR2,
445 p_qualifying_date DATE ) IS
446 --
448 --
449 l_coverage_exists VARCHAR2(2) := 'N';
450 --
451 -- declare cursors
452 --
453 CURSOR employee_coverage IS
454 SELECT 'E'
455 FROM per_cobra_cov_enrollments cce
456 WHERE ( cce.cobra_coverage_enrollment_id <> p_cobra_coverage_enrollment_id
457 OR p_cobra_coverage_enrollment_id IS NULL)
458 AND cce.assignment_id = p_assignment_id
459 AND cce.qualifying_date = p_qualifying_date
460 AND cce.qualifying_event= p_qualifying_event
461 AND cce.contact_relationship_id IS NULL;
462 --
463 CURSOR contact_coverage IS
464 SELECT 'C'
465 FROM per_cobra_cov_enrollments cce
466 WHERE ( cce.cobra_coverage_enrollment_id <> p_cobra_coverage_enrollment_id
467 OR p_cobra_coverage_enrollment_id IS NULL)
468 AND cce.contact_relationship_id = p_contact_relationship_id
469 AND cce.qualifying_date = p_qualifying_date
470 AND cce.qualifying_event = p_qualifying_event
471 AND cce.contact_relationship_id IS NOT NULL;
472 --
473 BEGIN
474 --
475 hr_utility.set_location('cce.hr_cobra_chk_uinique_enrollment',1);
476 --
477 --
478 -- check to see if contact entered
479 --
480 IF (p_contact_relationship_id IS NULL)
481 THEN
482 --
483 -- Check employee coverage
484 --
485 --
486 hr_utility.set_location('cce.hr_cobra_chk_uinique_enrollment',2);
487 --
488 OPEN employee_coverage;
489 FETCH employee_coverage INTO l_coverage_exists;
490 CLOSE employee_coverage;
491 --
492 hr_utility.set_location('cce.hr_cobra_chk_uinique_enrollment',3);
493 --
494 ELSE
495 --
496 -- Check contact coverage
497 --
498 --
499 hr_utility.set_location('cce.hr_cobra_chk_uinique_enrollment',4);
500 --
501 OPEN contact_coverage;
502 FETCH contact_coverage INTO l_coverage_exists;
503 CLOSE contact_coverage;
504 --
505 hr_utility.set_location('cce.hr_cobra_chk_uinique_enrollment',5);
506 --
507 END IF;
508 --
509 -- check to see if coverage exists
510 --
511 --
512 hr_utility.set_location('cce.hr_cobra_chk_uinique_enrollment',6);
513 --
514 IF (l_coverage_exists = 'C')
515 THEN
516 --
517 -- error
518 --
519 --
520 hr_utility.set_location('cce.hr_cobra_chk_uinique_enrollment',7);
521 --
522 hr_utility.set_message(801, 'HR_13142_DEP_COV_EXISTS');
523 hr_utility.raise_error;
524 --
525 ELSIF (l_coverage_exists = 'E')
526 THEN
527 --
528 hr_utility.set_location('cce.hr_cobra_chk_uinique_enrollment',8);
529 --
530 --
531 -- error
532 --
533 hr_utility.set_message(801, 'HR_13141_COBRA_EMP_COV_EXISTS');
534 hr_utility.raise_error;
535 --
536 END IF;
537 --
538 END hr_cobra_chk_unique_enrollment;
539 --
540 --
541 --
542 END PER_COBRA_COV_ENROLLMENTS_PKG;