[Home] [Help]
PACKAGE BODY: APPS.PER_COBRA_COV_BENEFITS_PKG
Source
1 PACKAGE BODY PER_COBRA_COV_BENEFITS_PKG as
2 /* $Header: pecobccb.pkb 115.0 99/07/17 18:49:46 porting ship $ */
3
4
5 PROCEDURE Insert_Row(X_Rowid IN OUT VARCHAR2,
6 X_Cobra_Coverage_Benefit_Id IN OUT NUMBER,
7 X_Effective_Start_Date DATE,
8 X_Effective_End_Date DATE,
9 X_Business_Group_Id NUMBER,
10 X_Cobra_Coverage_Enrollment_Id NUMBER,
11 X_Element_Type_Id NUMBER,
12 X_Accept_Reject_Flag VARCHAR2,
13 X_Coverage_Amount VARCHAR2,
14 X_Coverage_Type VARCHAR2,
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 ) IS
37 CURSOR C IS SELECT rowid FROM PER_COBRA_COVERAGE_BENEFITS_F
38
39 WHERE cobra_coverage_benefit_id = X_Cobra_Coverage_Benefit_Id;
40
41
42
43
44
45 CURSOR C2 IS SELECT per_cobra_coverage_benefits_s.nextval FROM sys.dual;
46 BEGIN
47
48 if (X_Cobra_Coverage_Benefit_Id is NULL) then
49 OPEN C2;
50 FETCH C2 INTO X_Cobra_Coverage_Benefit_Id;
51 CLOSE C2;
52 end if;
53 INSERT INTO PER_COBRA_COVERAGE_BENEFITS_F(
54 cobra_coverage_benefit_id,
55 effective_start_date,
56 effective_end_date,
57 business_group_id,
58 cobra_coverage_enrollment_id,
59 element_type_id,
60 accept_reject_flag,
61 coverage_amount,
62 coverage_type,
63 attribute_category,
64 attribute1,
65 attribute2,
66 attribute3,
67 attribute4,
68 attribute5,
69 attribute6,
70 attribute7,
71 attribute8,
72 attribute9,
73 attribute10,
74 attribute11,
75 attribute12,
76 attribute13,
77 attribute14,
78 attribute15,
79 attribute16,
80 attribute17,
81 attribute18,
82 attribute19,
83 attribute20
84 ) VALUES (
85 X_Cobra_Coverage_Benefit_Id,
86 X_Effective_Start_Date,
87 X_Effective_End_Date,
88 X_Business_Group_Id,
89 X_Cobra_Coverage_Enrollment_Id,
90 X_Element_Type_Id,
91 X_Accept_Reject_Flag,
92 X_Coverage_Amount,
93 X_Coverage_Type,
94 X_Attribute_Category,
95 X_Attribute1,
96 X_Attribute2,
97 X_Attribute3,
98 X_Attribute4,
99 X_Attribute5,
100 X_Attribute6,
101 X_Attribute7,
102 X_Attribute8,
103 X_Attribute9,
104 X_Attribute10,
105 X_Attribute11,
106 X_Attribute12,
107 X_Attribute13,
108 X_Attribute14,
109 X_Attribute15,
110 X_Attribute16,
111 X_Attribute17,
112 X_Attribute18,
113 X_Attribute19,
114 X_Attribute20
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 END Insert_Row;
125
126 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
127 X_Cobra_Coverage_Benefit_Id NUMBER,
128 X_Effective_Start_Date DATE,
129 X_Effective_End_Date DATE,
130 X_Business_Group_Id NUMBER,
131 X_Cobra_Coverage_Enrollment_Id NUMBER,
132 X_Element_Type_Id NUMBER,
133 X_Accept_Reject_Flag VARCHAR2,
134 X_Coverage_Amount VARCHAR2,
135 X_Coverage_Type VARCHAR2,
136 X_Attribute_Category VARCHAR2,
137 X_Attribute1 VARCHAR2,
138 X_Attribute2 VARCHAR2,
139 X_Attribute3 VARCHAR2,
140 X_Attribute4 VARCHAR2,
141 X_Attribute5 VARCHAR2,
142 X_Attribute6 VARCHAR2,
143 X_Attribute7 VARCHAR2,
144 X_Attribute8 VARCHAR2,
145 X_Attribute9 VARCHAR2,
146 X_Attribute10 VARCHAR2,
147 X_Attribute11 VARCHAR2,
148 X_Attribute12 VARCHAR2,
149 X_Attribute13 VARCHAR2,
150 X_Attribute14 VARCHAR2,
151 X_Attribute15 VARCHAR2,
152 X_Attribute16 VARCHAR2,
153 X_Attribute17 VARCHAR2,
154 X_Attribute18 VARCHAR2,
155 X_Attribute19 VARCHAR2,
156 X_Attribute20 VARCHAR2
157 ) IS
158 CURSOR C IS
159 SELECT *
160 FROM PER_COBRA_COVERAGE_BENEFITS_F
161 WHERE rowid = X_Rowid
162 FOR UPDATE of Cobra_Coverage_Benefit_Id NOWAIT;
163 Recinfo C%ROWTYPE;
164 BEGIN
165 -- hr_utility.trace_on;
166 hr_utility.set_location('ccb on lock', 1);
167 OPEN C;
168 hr_utility.set_location('ccb on lock', 2);
169 FETCH C INTO Recinfo;
170 if (C%NOTFOUND) then
171 CLOSE C;
172 hr_utility.set_location('ccb on lock', 3);
173 RAISE NO_DATA_FOUND;
174 end if;
175 hr_utility.set_location('ccb on lock', 4);
176 CLOSE C;
177 --
178 -- Ensure that we're not tricked into thinking the record has
179 -- changed if the user has inserted using sqlplus leaving trailing spaces
180 --
181 Recinfo.attribute18 := RTRIM(Recinfo.attribute18);
182 Recinfo.attribute19 := RTRIM(Recinfo.attribute19);
183 Recinfo.attribute20 := RTRIM(Recinfo.attribute20);
184 Recinfo.accept_reject_flag := RTRIM(Recinfo.accept_reject_flag);
185 Recinfo.coverage_amount := RTRIM(Recinfo.coverage_amount);
186 Recinfo.coverage_type := RTRIM(Recinfo.coverage_type);
187 Recinfo.attribute_category := RTRIM(Recinfo.attribute_category);
188 Recinfo.attribute1 := RTRIM(Recinfo.attribute1);
192 Recinfo.attribute5 := RTRIM(Recinfo.attribute5);
189 Recinfo.attribute2 := RTRIM(Recinfo.attribute2);
190 Recinfo.attribute3 := RTRIM(Recinfo.attribute3);
191 Recinfo.attribute4 := RTRIM(Recinfo.attribute4);
193 Recinfo.attribute6 := RTRIM(Recinfo.attribute6);
194 Recinfo.attribute7 := RTRIM(Recinfo.attribute7);
195 Recinfo.attribute8 := RTRIM(Recinfo.attribute8);
196 Recinfo.attribute9 := RTRIM(Recinfo.attribute9);
197 Recinfo.attribute10 := RTRIM(Recinfo.attribute10);
198 Recinfo.attribute11 := RTRIM(Recinfo.attribute11);
199 Recinfo.attribute12 := RTRIM(Recinfo.attribute12);
200 Recinfo.attribute13 := RTRIM(Recinfo.attribute13);
201 Recinfo.attribute14 := RTRIM(Recinfo.attribute14);
202 Recinfo.attribute15 := RTRIM(Recinfo.attribute15);
203 Recinfo.attribute16 := RTRIM(Recinfo.attribute16);
204 Recinfo.attribute17 := RTRIM(Recinfo.attribute17);
205 --
206 if (
207 ( (Recinfo.cobra_coverage_benefit_id = X_Cobra_Coverage_Benefit_Id)
208 OR ( (Recinfo.cobra_coverage_benefit_id IS NULL)
209 AND (X_Cobra_Coverage_Benefit_Id IS NULL)))
210 AND ( (Recinfo.effective_start_date = X_Effective_Start_Date)
211 OR ( (Recinfo.effective_start_date IS NULL)
212 AND (X_Effective_Start_Date IS NULL)))
213 AND ( (Recinfo.effective_end_date = X_Effective_End_Date)
214 OR ( (Recinfo.effective_end_date IS NULL)
215 AND (X_Effective_End_Date IS NULL)))
216 AND ( (Recinfo.business_group_id = X_Business_Group_Id)
217 OR ( (Recinfo.business_group_id IS NULL)
218 AND (X_Business_Group_Id IS NULL)))
219 AND ( (Recinfo.cobra_coverage_enrollment_id = X_Cobra_Coverage_Enrollment_Id)
220 OR ( (Recinfo.cobra_coverage_enrollment_id IS NULL)
221 AND (X_Cobra_Coverage_Enrollment_Id IS NULL)))
222 AND ( (Recinfo.element_type_id = X_Element_Type_Id)
223 OR ( (Recinfo.element_type_id IS NULL)
224 AND (X_Element_Type_Id IS NULL)))
225 AND ( (Recinfo.accept_reject_flag = X_Accept_Reject_Flag)
226 OR ( (Recinfo.accept_reject_flag IS NULL)
227 AND (X_Accept_Reject_Flag IS NULL)))
228 AND ( (TO_NUMBER(Recinfo.coverage_amount) = TO_NUMBER(X_Coverage_Amount))
229 OR ( (Recinfo.coverage_amount IS NULL)
230 AND (X_Coverage_Amount IS NULL)))
231 AND ( (Recinfo.coverage_type = X_Coverage_Type)
232 OR ( (Recinfo.coverage_type IS NULL)
233 AND (X_Coverage_Type IS NULL)))
234 AND ( (Recinfo.attribute_category = X_Attribute_Category)
235 OR ( (Recinfo.attribute_category IS NULL)
236 AND (X_Attribute_Category IS NULL)))
237 AND ( (Recinfo.attribute1 = X_Attribute1)
238 OR ( (Recinfo.attribute1 IS NULL)
239 AND (X_Attribute1 IS NULL)))
240 AND ( (Recinfo.attribute2 = X_Attribute2)
241 OR ( (Recinfo.attribute2 IS NULL)
242 AND (X_Attribute2 IS NULL)))
243 AND ( (Recinfo.attribute3 = X_Attribute3)
244 OR ( (Recinfo.attribute3 IS NULL)
245 AND (X_Attribute3 IS NULL)))
246 AND ( (Recinfo.attribute4 = X_Attribute4)
247 OR ( (Recinfo.attribute4 IS NULL)
248 AND (X_Attribute4 IS NULL)))
249 AND ( (Recinfo.attribute5 = X_Attribute5)
250 OR ( (Recinfo.attribute5 IS NULL)
251 AND (X_Attribute5 IS NULL)))
252 AND ( (Recinfo.attribute6 = X_Attribute6)
253 OR ( (Recinfo.attribute6 IS NULL)
254 AND (X_Attribute6 IS NULL)))
255 AND ( (Recinfo.attribute7 = X_Attribute7)
256 OR ( (Recinfo.attribute7 IS NULL)
257 AND (X_Attribute7 IS NULL)))
258 AND ( (Recinfo.attribute8 = X_Attribute8)
262 OR ( (Recinfo.attribute9 IS NULL)
259 OR ( (Recinfo.attribute8 IS NULL)
260 AND (X_Attribute8 IS NULL)))
261 AND ( (Recinfo.attribute9 = X_Attribute9)
263 AND (X_Attribute9 IS NULL)))
264 AND ( (Recinfo.attribute10 = X_Attribute10)
265 OR ( (Recinfo.attribute10 IS NULL)
266 AND (X_Attribute10 IS NULL)))
267 AND ( (Recinfo.attribute11 = X_Attribute11)
268 OR ( (Recinfo.attribute11 IS NULL)
269 AND (X_Attribute11 IS NULL)))
270 AND ( (Recinfo.attribute12 = X_Attribute12)
271 OR ( (Recinfo.attribute12 IS NULL)
272 AND (X_Attribute12 IS NULL)))
273 AND ( (Recinfo.attribute13 = X_Attribute13)
274 OR ( (Recinfo.attribute13 IS NULL)
275 AND (X_Attribute13 IS NULL)))
276 AND ( (Recinfo.attribute14 = X_Attribute14)
277 OR ( (Recinfo.attribute14 IS NULL)
278 AND (X_Attribute14 IS NULL)))
279 AND ( (Recinfo.attribute15 = X_Attribute15)
280 OR ( (Recinfo.attribute15 IS NULL)
281 AND (X_Attribute15 IS NULL)))
282 AND ( (Recinfo.attribute16 = X_Attribute16)
283 OR ( (Recinfo.attribute16 IS NULL)
284 AND (X_Attribute16 IS NULL)))
285 AND ( (Recinfo.attribute17 = X_Attribute17)
286 OR ( (Recinfo.attribute17 IS NULL)
287 AND (X_Attribute17 IS NULL)))
288 AND ( (Recinfo.attribute18 = X_Attribute18)
289 OR ( (Recinfo.attribute18 IS NULL)
290 AND (X_Attribute18 IS NULL)))
291 AND ( (Recinfo.attribute19 = X_Attribute19)
292 OR ( (Recinfo.attribute19 IS NULL)
293 AND (X_Attribute19 IS NULL)))
294 AND ( (Recinfo.attribute20 = X_Attribute20)
295 OR ( (Recinfo.attribute20 IS NULL)
296 AND (X_Attribute20 IS NULL)))
297 ) then
298 hr_utility.set_location('ccb on lock', 5);
299 return;
300 else
301 hr_utility.set_location('ccb on lock', 6);
302 hr_utility.set_message(0, 'FORM_RECORD_CHANGED');
303 hr_utility.raise_error;
304 end if;
305 END Lock_Row;
306
307 PROCEDURE Update_Row(X_Rowid VARCHAR2,
308 X_Cobra_Coverage_Benefit_Id NUMBER,
309 X_Effective_Start_Date DATE,
310 X_Effective_End_Date DATE,
311 X_Business_Group_Id NUMBER,
312 X_Cobra_Coverage_Enrollment_Id NUMBER,
313 X_Element_Type_Id NUMBER,
314 X_Accept_Reject_Flag VARCHAR2,
315 X_Coverage_Amount VARCHAR2,
316 X_Coverage_Type VARCHAR2,
317 X_Attribute_Category VARCHAR2,
318 X_Attribute1 VARCHAR2,
319 X_Attribute2 VARCHAR2,
320 X_Attribute3 VARCHAR2,
321 X_Attribute4 VARCHAR2,
322 X_Attribute5 VARCHAR2,
323 X_Attribute6 VARCHAR2,
324 X_Attribute7 VARCHAR2,
325 X_Attribute8 VARCHAR2,
326 X_Attribute9 VARCHAR2,
327 X_Attribute10 VARCHAR2,
328 X_Attribute11 VARCHAR2,
329 X_Attribute12 VARCHAR2,
330 X_Attribute13 VARCHAR2,
331 X_Attribute14 VARCHAR2,
332 X_Attribute15 VARCHAR2,
333 X_Attribute16 VARCHAR2,
334 X_Attribute17 VARCHAR2,
335 X_Attribute18 VARCHAR2,
336 X_Attribute19 VARCHAR2,
337 X_Attribute20 VARCHAR2
338 ) IS
339 BEGIN
340 UPDATE PER_COBRA_COVERAGE_BENEFITS_F
341 SET
342 cobra_coverage_benefit_id = X_Cobra_Coverage_Benefit_Id,
343 effective_start_date = X_Effective_Start_Date,
344 effective_end_date = X_Effective_End_Date,
345 business_group_id = X_Business_Group_Id,
346 cobra_coverage_enrollment_id = X_Cobra_Coverage_Enrollment_Id,
347 element_type_id = X_Element_Type_Id,
348 accept_reject_flag = X_Accept_Reject_Flag,
349 coverage_amount = X_Coverage_Amount,
350 coverage_type = X_Coverage_Type,
351 attribute_category = X_Attribute_Category,
352 attribute1 = X_Attribute1,
353 attribute2 = X_Attribute2,
354 attribute3 = X_Attribute3,
355 attribute4 = X_Attribute4,
356 attribute5 = X_Attribute5,
357 attribute6 = X_Attribute6,
358 attribute7 = X_Attribute7,
359 attribute8 = X_Attribute8,
360 attribute9 = X_Attribute9,
361 attribute10 = X_Attribute10,
365 attribute14 = X_Attribute14,
362 attribute11 = X_Attribute11,
363 attribute12 = X_Attribute12,
364 attribute13 = X_Attribute13,
366 attribute15 = X_Attribute15,
367 attribute16 = X_Attribute16,
368 attribute17 = X_Attribute17,
369 attribute18 = X_Attribute18,
370 attribute19 = X_Attribute19,
371 attribute20 = X_Attribute20
372 WHERE rowid = X_rowid;
373
374 if (SQL%NOTFOUND) then
375 RAISE NO_DATA_FOUND;
376 end if;
377
378 END Update_Row;
379
380 PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
381 BEGIN
382 DELETE FROM PER_COBRA_COVERAGE_BENEFITS_F
383 WHERE rowid = X_Rowid;
384
385 if (SQL%NOTFOUND) then
386 RAISE NO_DATA_FOUND;
387 end if;
388 END Delete_Row;
389
390 END PER_COBRA_COV_BENEFITS_PKG;