[Home] [Help]
PACKAGE BODY: APPS.PER_ABT_PKG
Source
1 PACKAGE BODY PER_ABT_PKG as
2 /* $Header: peabt01t.pkb 115.1 99/07/17 18:23:36 porting ship $ */
3 /*===========================================================================+
4 | Copyright (c) 1993 Oracle Corporation |
5 | Redwood Shores, California, USA |
6 | All rights reserved. |
7 +===========================================================================*/
8 /*-----------------------------------------------------------------------------
9
10 Description
11 -----------
12
13 Date Version Author Description
14 -------- ------- --------- ---------------
15 17/02/94 80.0 JRhodes Initial Version
16 14/04/94 80.1 JRhodes Modified Lock-Row to rtrim VARCHAR2
17 01/07/94 70.1 JRhodes Chnaged Get_UOM to return only the lookup_code
18 now that UOM is a checkbox on the form
19 23/11/94 70.4 rfine Suppressed index on business_group_id
20 31/01/95 70.6 JRhodes Removed AOL WHO Columns
21 21/07/95 70.7 aforte Replaced tokenised messages, ALL_MANDATORY_FIELD amills with hard coded messages ALL_MAN_HOU_FIELD
22 ALL_MAN_INC_FIELD and ALL_MAN_VAL_FIELD.
23 04/03/97 70.8 JAlloun Changed all occurances of system.dual
24 to sys.dual for next release requirements.
25 ---------------------------------------------------------------------------*/
26 PROCEDURE Insert_Row(X_Rowid IN OUT VARCHAR2,
27 X_Absence_Attendance_Type_Id IN OUT NUMBER,
28 X_Business_Group_Id NUMBER,
29 X_Input_Value_Id NUMBER,
30 X_Date_Effective DATE,
31 X_Name VARCHAR2,
32 X_Absence_Category VARCHAR2,
33 X_Comments VARCHAR2,
34 X_Date_End IN OUT DATE,
35 X_Hours_Or_Days VARCHAR2,
36 X_Inc_Or_Dec_Flag VARCHAR2,
37 X_Attribute_Category VARCHAR2,
38 X_Attribute1 VARCHAR2,
39 X_Attribute2 VARCHAR2,
40 X_Attribute3 VARCHAR2,
41 X_Attribute4 VARCHAR2,
42 X_Attribute5 VARCHAR2,
43 X_Attribute6 VARCHAR2,
44 X_Attribute7 VARCHAR2,
45 X_Attribute8 VARCHAR2,
46 X_Attribute9 VARCHAR2,
47 X_Attribute10 VARCHAR2,
48 X_Attribute11 VARCHAR2,
49 X_Attribute12 VARCHAR2,
50 X_Attribute13 VARCHAR2,
51 X_Attribute14 VARCHAR2,
52 X_Attribute15 VARCHAR2,
53 X_Attribute16 VARCHAR2,
54 X_Attribute17 VARCHAR2,
55 X_Attribute18 VARCHAR2,
56 X_Attribute19 VARCHAR2,
57 X_Attribute20 VARCHAR2,
58 X_Element_Type_ID NUMBER,
59 X_Element_End_Date DATE,
60 X_END_OF_TIME DATE
61 ) IS
62 CURSOR C IS
63 SELECT rowid FROM PER_ABSENCE_ATTENDANCE_TYPES
64 WHERE absence_attendance_type_id = X_Absence_Attendance_Type_Id;
65 --
66 CURSOR C2 IS
67 SELECT per_absence_attendance_types_s.nextval
68 FROM sys.dual;
69 BEGIN
70 -- check the uniqueness of the name
71 --
72 per_abt_pkg.check_unique_name(X_Rowid
73 ,X_Business_Group_Id
74 ,X_Name);
75 --
76 --
77 per_abt_pkg.ensure_fields_populated(X_inc_or_dec_flag
78 ,X_hours_or_days
79 ,X_input_value_id
80 ,X_element_type_id);
81 --
82 --
83 per_abt_pkg.check_inputs_required(X_element_type_id
84 ,X_Input_Value_Id );
85 --
86 --
87 per_abt_pkg.val_date_end(X_date_end
88 ,X_element_end_date
89 ,X_end_of_time );
90 --
91 if (X_Absence_Attendance_Type_Id is NULL) then
92 OPEN C2;
93 FETCH C2 INTO X_Absence_Attendance_Type_Id;
94 CLOSE C2;
95 end if;
96 INSERT INTO PER_ABSENCE_ATTENDANCE_TYPES(
97 absence_attendance_type_id,
98 business_group_id,
99 input_value_id,
100 date_effective,
101 name,
102 absence_category,
103 comments,
104 date_end,
105 hours_or_days,
106 increasing_or_decreasing_flag,
107 attribute_category,
108 attribute1,
109 attribute2,
110 attribute3,
111 attribute4,
112 attribute5,
113 attribute6,
114 attribute7,
115 attribute8,
116 attribute9,
117 attribute10,
118 attribute11,
119 attribute12,
120 attribute13,
121 attribute14,
122 attribute15,
123 attribute16,
124 attribute17,
125 attribute18,
126 attribute19,
127 attribute20
128 ) VALUES (
129 X_Absence_Attendance_Type_Id,
130 X_Business_Group_Id,
131 X_Input_Value_Id,
132 X_Date_Effective,
133 X_Name,
134 X_Absence_Category,
135 X_Comments,
136 X_Date_End,
137 X_Hours_Or_Days,
138 X_Inc_Or_Dec_Flag ,
139 X_Attribute_Category,
140 X_Attribute1,
141 X_Attribute2,
142 X_Attribute3,
143 X_Attribute4,
144 X_Attribute5,
145 X_Attribute6,
146 X_Attribute7,
147 X_Attribute8,
148 X_Attribute9,
149 X_Attribute10,
150 X_Attribute11,
151 X_Attribute12,
152 X_Attribute13,
153 X_Attribute14,
154 X_Attribute15,
155 X_Attribute16,
156 X_Attribute17,
157 X_Attribute18,
158 X_Attribute19,
159 X_Attribute20
160 );
161 --
162 OPEN C;
163 FETCH C INTO X_Rowid;
164 if (C%NOTFOUND) then
165 CLOSE C;
166 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
167 hr_utility.set_message_token('PROCEDURE','PER_ABT_PKG.Insert_Row');
168 hr_utility.set_message_token('STEP','1');
172 --
169 hr_utility.raise_error;
170 end if;
171 CLOSE C;
173 hrdyndbi.create_absence_dict(X_ABSENCE_ATTENDANCE_TYPE_ID);
174 --
175 END Insert_Row;
176 --
177 --
178 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
179 X_Absence_Attendance_Type_Id NUMBER,
180 X_Business_Group_Id NUMBER,
181 X_Input_Value_Id NUMBER,
182 X_Date_Effective DATE,
183 X_Name VARCHAR2,
184 X_Absence_Category VARCHAR2,
185 X_Comments VARCHAR2,
186 X_Date_End DATE,
187 X_Hours_Or_Days VARCHAR2,
188 X_Inc_Or_Dec_Flag VARCHAR2,
189 X_Attribute_Category VARCHAR2,
190 X_Attribute1 VARCHAR2,
191 X_Attribute2 VARCHAR2,
192 X_Attribute3 VARCHAR2,
193 X_Attribute4 VARCHAR2,
194 X_Attribute5 VARCHAR2,
195 X_Attribute6 VARCHAR2,
196 X_Attribute7 VARCHAR2,
197 X_Attribute8 VARCHAR2,
198 X_Attribute9 VARCHAR2,
199 X_Attribute10 VARCHAR2,
200 X_Attribute11 VARCHAR2,
201 X_Attribute12 VARCHAR2,
202 X_Attribute13 VARCHAR2,
203 X_Attribute14 VARCHAR2,
204 X_Attribute15 VARCHAR2,
205 X_Attribute16 VARCHAR2,
206 X_Attribute17 VARCHAR2,
207 X_Attribute18 VARCHAR2,
208 X_Attribute19 VARCHAR2,
209 X_Attribute20 VARCHAR2
210 ) IS
211 CURSOR C IS
212 SELECT *
213 FROM PER_ABSENCE_ATTENDANCE_TYPES
214 WHERE rowid = X_Rowid
215 FOR UPDATE of Absence_Attendance_Type_Id NOWAIT;
216 Recinfo C%ROWTYPE;
217 --
218 BEGIN
219 OPEN C;
220 FETCH C INTO Recinfo;
221 if (C%NOTFOUND) then
222 CLOSE C;
223 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
224 hr_utility.set_message_token('PROCEDURE','PER_ABT_PKG.Lock_Row');
225 hr_utility.set_message_token('STEP','1');
226 hr_utility.raise_error;
227 end if;
228 CLOSE C;
229 --
230 Recinfo.Name := rtrim(Recinfo.Name);
231 Recinfo.Absence_Category := rtrim(Recinfo.Absence_Category);
232 Recinfo.Comments := rtrim(Recinfo.Comments);
233 Recinfo.Hours_Or_Days := rtrim(Recinfo.Hours_Or_Days);
234 Recinfo.increasing_or_decreasing_flag :=
235 rtrim(Recinfo.increasing_or_decreasing_flag);
236 Recinfo.Attribute_Category := rtrim(Recinfo.Attribute_Category);
237 Recinfo.Attribute1 := rtrim(Recinfo.Attribute1);
238 Recinfo.Attribute2 := rtrim(Recinfo.Attribute2);
242 Recinfo.Attribute6 := rtrim(Recinfo.Attribute6);
239 Recinfo.Attribute3 := rtrim(Recinfo.Attribute3);
240 Recinfo.Attribute4 := rtrim(Recinfo.Attribute4);
241 Recinfo.Attribute5 := rtrim(Recinfo.Attribute5);
243 Recinfo.Attribute7 := rtrim(Recinfo.Attribute7);
244 Recinfo.Attribute8 := rtrim(Recinfo.Attribute8);
245 Recinfo.Attribute9 := rtrim(Recinfo.Attribute9);
246 Recinfo.Attribute10 := rtrim(Recinfo.Attribute10);
247 Recinfo.Attribute11 := rtrim(Recinfo.Attribute11);
248 Recinfo.Attribute12 := rtrim(Recinfo.Attribute12);
249 Recinfo.Attribute13 := rtrim(Recinfo.Attribute13);
250 Recinfo.Attribute14 := rtrim(Recinfo.Attribute14);
251 Recinfo.Attribute15 := rtrim(Recinfo.Attribute15);
252 Recinfo.Attribute16 := rtrim(Recinfo.Attribute16);
253 Recinfo.Attribute17 := rtrim(Recinfo.Attribute17);
254 Recinfo.Attribute18 := rtrim(Recinfo.Attribute18);
255 Recinfo.Attribute19 := rtrim(Recinfo.Attribute19);
256 Recinfo.Attribute20 := rtrim(Recinfo.Attribute20);
257 --
258 if (
259 ( (Recinfo.absence_attendance_type_id = X_Absence_Attendance_Type_Id)
260 OR ( (Recinfo.absence_attendance_type_id IS NULL)
261 AND (X_Absence_Attendance_Type_Id IS NULL)))
262 AND ( (Recinfo.business_group_id = X_Business_Group_Id)
263 OR ( (Recinfo.business_group_id IS NULL)
264 AND (X_Business_Group_Id IS NULL)))
265 AND ( (Recinfo.input_value_id = X_Input_Value_Id)
266 OR ( (Recinfo.input_value_id IS NULL)
267 AND (X_Input_Value_Id IS NULL)))
268 AND ( (Recinfo.date_effective = X_Date_Effective)
269 OR ( (Recinfo.date_effective IS NULL)
270 AND (X_Date_Effective IS NULL)))
271 AND ( (Recinfo.name = X_Name)
272 OR ( (Recinfo.name IS NULL)
273 AND (X_Name IS NULL)))
274 AND ( (Recinfo.absence_category = X_Absence_Category)
275 OR ( (Recinfo.absence_category IS NULL)
276 AND (X_Absence_Category IS NULL)))
277 AND ( (Recinfo.comments = X_Comments)
278 OR ( (Recinfo.comments IS NULL)
279 AND (X_Comments IS NULL)))
280 AND ( (Recinfo.date_end = X_Date_End)
281 OR ( (Recinfo.date_end IS NULL)
282 AND (X_Date_End IS NULL)))
283 AND ( (Recinfo.hours_or_days = X_Hours_Or_Days)
284 OR ( (Recinfo.hours_or_days IS NULL)
285 AND (X_Hours_Or_Days IS NULL)))
286 AND ( (Recinfo.increasing_or_decreasing_flag =
287 X_Inc_Or_Dec_Flag )
288 OR ( (Recinfo.increasing_or_decreasing_flag IS NULL)
289 AND (X_Inc_Or_Dec_Flag IS NULL)))
290 AND ( (Recinfo.attribute_category = X_Attribute_Category)
291 OR ( (Recinfo.attribute_category IS NULL)
292 AND (X_Attribute_Category IS NULL)))
293 AND ( (Recinfo.attribute1 = X_Attribute1)
294 OR ( (Recinfo.attribute1 IS NULL)
295 AND (X_Attribute1 IS NULL)))
296 AND ( (Recinfo.attribute2 = X_Attribute2)
297 OR ( (Recinfo.attribute2 IS NULL)
298 AND (X_Attribute2 IS NULL)))
299 AND ( (Recinfo.attribute3 = X_Attribute3)
300 OR ( (Recinfo.attribute3 IS NULL)
301 AND (X_Attribute3 IS NULL)))
302 AND ( (Recinfo.attribute4 = X_Attribute4)
306 OR ( (Recinfo.attribute5 IS NULL)
303 OR ( (Recinfo.attribute4 IS NULL)
304 AND (X_Attribute4 IS NULL)))
305 AND ( (Recinfo.attribute5 = X_Attribute5)
307 AND (X_Attribute5 IS NULL)))
308 AND ( (Recinfo.attribute6 = X_Attribute6)
309 OR ( (Recinfo.attribute6 IS NULL)
310 AND (X_Attribute6 IS NULL)))
311 AND ( (Recinfo.attribute7 = X_Attribute7)
312 OR ( (Recinfo.attribute7 IS NULL)
313 AND (X_Attribute7 IS NULL)))
314 AND ( (Recinfo.attribute8 = X_Attribute8)
315 OR ( (Recinfo.attribute8 IS NULL)
316 AND (X_Attribute8 IS NULL)))
317 AND ( (Recinfo.attribute9 = X_Attribute9)
318 OR ( (Recinfo.attribute9 IS NULL)
319 AND (X_Attribute9 IS NULL)))
320 AND ( (Recinfo.attribute10 = X_Attribute10)
321 OR ( (Recinfo.attribute10 IS NULL)
322 AND (X_Attribute10 IS NULL)))
323 AND ( (Recinfo.attribute11 = X_Attribute11)
324 OR ( (Recinfo.attribute11 IS NULL)
325 AND (X_Attribute11 IS NULL)))
326 AND ( (Recinfo.attribute12 = X_Attribute12)
327 OR ( (Recinfo.attribute12 IS NULL)
328 AND (X_Attribute12 IS NULL)))
329 AND ( (Recinfo.attribute13 = X_Attribute13)
330 OR ( (Recinfo.attribute13 IS NULL)
331 AND (X_Attribute13 IS NULL)))
332 AND ( (Recinfo.attribute14 = X_Attribute14)
333 OR ( (Recinfo.attribute14 IS NULL)
334 AND (X_Attribute14 IS NULL)))
335 AND ( (Recinfo.attribute15 = X_Attribute15)
336 OR ( (Recinfo.attribute15 IS NULL)
337 AND (X_Attribute15 IS NULL)))
338 AND ( (Recinfo.attribute16 = X_Attribute16)
339 OR ( (Recinfo.attribute16 IS NULL)
340 AND (X_Attribute16 IS NULL)))
341 AND ( (Recinfo.attribute17 = X_Attribute17)
342 OR ( (Recinfo.attribute17 IS NULL)
343 AND (X_Attribute17 IS NULL)))
344 AND ( (Recinfo.attribute18 = X_Attribute18)
345 OR ( (Recinfo.attribute18 IS NULL)
346 AND (X_Attribute18 IS NULL)))
347 AND ( (Recinfo.attribute19 = X_Attribute19)
348 OR ( (Recinfo.attribute19 IS NULL)
349 AND (X_Attribute19 IS NULL)))
350 AND ( (Recinfo.attribute20 = X_Attribute20)
351 OR ( (Recinfo.attribute20 IS NULL)
352 AND (X_Attribute20 IS NULL)))
353 ) then
354 return;
355 else
356 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
357 APP_EXCEPTION.RAISE_EXCEPTION;
358 end if;
359 END Lock_Row;
360 --
361 PROCEDURE Update_Row(X_Rowid VARCHAR2,
362 X_Absence_Attendance_Type_Id NUMBER,
363 X_Business_Group_Id NUMBER,
364 X_Input_Value_Id NUMBER,
365 X_Date_Effective DATE,
366 X_Name VARCHAR2,
367 X_Absence_Category VARCHAR2,
368 X_Comments VARCHAR2,
369 X_Date_End IN OUT DATE,
370 X_Hours_Or_Days VARCHAR2,
371 X_Inc_Or_Dec_Flag VARCHAR2,
372 X_Attribute_Category VARCHAR2,
373 X_Attribute1 VARCHAR2,
374 X_Attribute2 VARCHAR2,
375 X_Attribute3 VARCHAR2,
376 X_Attribute4 VARCHAR2,
377 X_Attribute5 VARCHAR2,
378 X_Attribute6 VARCHAR2,
379 X_Attribute7 VARCHAR2,
380 X_Attribute8 VARCHAR2,
381 X_Attribute9 VARCHAR2,
382 X_Attribute10 VARCHAR2,
383 X_Attribute11 VARCHAR2,
384 X_Attribute12 VARCHAR2,
385 X_Attribute13 VARCHAR2,
386 X_Attribute14 VARCHAR2,
387 X_Attribute15 VARCHAR2,
388 X_Attribute16 VARCHAR2,
389 X_Attribute17 VARCHAR2,
390 X_Attribute18 VARCHAR2,
394 X_Element_End_Date DATE,
391 X_Attribute19 VARCHAR2,
392 X_Attribute20 VARCHAR2,
393 X_Element_Type_ID NUMBER,
395 X_END_OF_TIME DATE,
396 X_old_absence_category VARCHAR2,
397 X_Old_Name VARCHAR2
398 ) IS
399 BEGIN
400 -- check the uniqueness of the name
401 --
402 per_abt_pkg.check_unique_name(X_Rowid
403 ,X_Business_Group_Id
404 ,X_Name);
405 --
406 --
407 per_abt_pkg.ensure_fields_populated(X_inc_or_dec_flag
408 ,X_hours_or_days
409 ,X_input_value_id
410 ,X_element_type_id);
411 --
412 --
413 per_abt_pkg.check_inputs_required(X_element_type_id
414 ,X_Input_Value_Id );
415 --
416 --
417 per_abt_pkg.check_category(X_old_absence_category
418 ,X_absence_category
419 ,X_absence_attendance_type_id );
420 --
421 --
422 per_abt_pkg.val_date_end(X_date_end
423 ,X_element_end_date
424 ,X_end_of_time );
425 --
426 --
427 if X_Name <> X_Old_Name then
428 hrdyndbi.delete_absence_dict(X_ABSENCE_ATTENDANCE_TYPE_ID);
429 end if;
430 --
431 UPDATE PER_ABSENCE_ATTENDANCE_TYPES
432 SET
433 absence_attendance_type_id = X_Absence_Attendance_Type_Id,
434 business_group_id = X_Business_Group_Id,
435 input_value_id = X_Input_Value_Id,
436 date_effective = X_Date_Effective,
437 name = X_Name,
438 absence_category = X_Absence_Category,
439 comments = X_Comments,
440 date_end = X_Date_End,
441 hours_or_days = X_Hours_Or_Days,
442 increasing_or_decreasing_flag = X_Inc_Or_Dec_Flag ,
443 attribute_category = X_Attribute_Category,
444 attribute1 = X_Attribute1,
445 attribute2 = X_Attribute2,
446 attribute3 = X_Attribute3,
447 attribute4 = X_Attribute4,
448 attribute5 = X_Attribute5,
449 attribute6 = X_Attribute6,
450 attribute7 = X_Attribute7,
451 attribute8 = X_Attribute8,
452 attribute9 = X_Attribute9,
453 attribute10 = X_Attribute10,
454 attribute11 = X_Attribute11,
455 attribute12 = X_Attribute12,
456 attribute13 = X_Attribute13,
460 attribute17 = X_Attribute17,
457 attribute14 = X_Attribute14,
458 attribute15 = X_Attribute15,
459 attribute16 = X_Attribute16,
461 attribute18 = X_Attribute18,
462 attribute19 = X_Attribute19,
463 attribute20 = X_Attribute20
464 WHERE rowid = X_rowid;
465 --
466 if (SQL%NOTFOUND) then
467 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
468 hr_utility.set_message_token('PROCEDURE','PER_ABT_PKG.Update_Row');
469 hr_utility.set_message_token('STEP','1');
470 hr_utility.raise_error;
471 end if;
472 --
473 if X_Name <> X_Old_Name then
474 hrdyndbi.create_absence_dict(X_ABSENCE_ATTENDANCE_TYPE_ID);
475 end if;
476 --
477 END Update_Row;
478
479 PROCEDURE Delete_Row(X_Rowid VARCHAR2
480 ,X_Absence_attendance_type_id NUMBER) IS
481 BEGIN
482 --
483 hr_utility.set_location('per_abt_pkg.delete_row',1);
484 per_abt_pkg.abt_del_validation(X_Absence_attendance_type_id);
485 --
486 hr_utility.set_location('per_abt_pkg.delete_row',2);
487 hrdyndbi.delete_absence_dict(X_Absence_attendance_type_id);
488 --
489 hr_utility.set_location('per_abt_pkg.delete_row',3);
490 DELETE FROM PER_ABSENCE_ATTENDANCE_TYPES
491 WHERE rowid = X_Rowid;
492 --
493 if (SQL%NOTFOUND) then
494 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
495 hr_utility.set_message_token('PROCEDURE','PER_ABT_PKG.Delete_Row');
496 hr_utility.set_message_token('STEP','1');
497 hr_utility.raise_error;
498 end if;
499 END Delete_Row;
500 --
501 --
502 procedure check_unique_name(p_rowid varchar2
503 ,p_business_group_id number
504 ,p_name varchar2) is
505 --
506 cursor c is
507 select ''
508 from per_absence_attendance_types
509 where upper(p_name) = upper(name)
510 and business_group_id + 0 = p_business_group_id
511 and (rowidtochar(rowid) <> p_rowid or p_rowid is null);
512 abt_rec c%rowtype;
513 --
514 begin
515 --
516 hr_utility.set_location('per_abt_pkg.check_unique_name',1);
517 OPEN C;
518 FETCH C INTO ABT_REC;
519 if (C%FOUND) then
520 CLOSE C;
521 hr_utility.set_message(801,'PER_7806_DEF_ABS_EXISTS');
522 hr_utility.raise_error;
523 end if;
524 CLOSE C;
525
526 end check_unique_name;
527 --
528 --
529 procedure validate_date_effective(p_date_effective DATE
530 ,p_element_type_id NUMBER
531 ,p_absence_attendance_type_id NUMBER) is
532 --
533 cursor c1 is
534 select ''
535 from pay_element_types_f
536 where element_type_id = p_element_type_id
537 and p_date_effective between
541 cursor c2 is
538 effective_start_date and effective_end_date;
539 elt_rec c1%rowtype;
540 --
542 select ''
543 from per_absence_attendances
544 where absence_attendance_type_id = p_absence_attendance_type_id
545 and date_start < p_date_effective;
546 abs_rec c2%rowtype;
547 --
548 begin
549 --
550 hr_utility.set_location('per_abt_pkg.validate_date_effective',1);
551 if p_element_type_id is not null then
552 OPEN C1;
553 FETCH C1 INTO ELT_REC;
554 if (C1%NOTFOUND) then
555 CLOSE C1;
556 hr_utility.set_message(801,'HR_6789_ABS_NO_CHANGE_DATE');
557 hr_utility.raise_error;
558 end if;
559 CLOSE C1;
560 end if;
561 --
562 hr_utility.set_location('per_abt_pkg.validate_date_effective',2);
563 if p_absence_attendance_type_id is not null then
564 OPEN C2;
565 FETCH C2 INTO ABS_REC;
566 if (C2%FOUND) then
567 CLOSE C2;
568 hr_utility.set_message(801,'HR_6790_ABS_NO_CHANGE_2');
569 hr_utility.raise_error;
570 end if;
571 CLOSE C2;
572 end if;
573 --
574 end validate_date_effective;
575 --
576 --
577 procedure validate_element_name(p_element_type_id NUMBER
578 ,p_date_effective DATE
579 ,p_rowid VARCHAR2
580 ,p_default_value IN OUT VARCHAR2
581 ,p_input_value_name IN OUT VARCHAR2
582 ,p_input_value_id IN OUT NUMBER
583 ,p_value_uom IN OUT VARCHAR2
584 ,p_element_end_date IN OUT DATE) is
585 cursor c is
586 select ipv.name
587 , ipv.uom
588 , ipv.input_value_id
589 from pay_input_values ipv
590 , hr_lookups lkp
591 where ipv.element_type_id = p_element_type_id
592 and ipv.uom = lkp.lookup_code
593 and lkp.lookup_type = 'UNITS'
594 and lkp.lookup_code in
595 ('ND','H_HH','H_DECIMAL1','H_DECIMAL2','H_DECIMAL3')
596 and p_date_effective between
597 ipv.effective_start_date and ipv.effective_end_date;
598 --
599 IPV_REC c%rowtype;
600 IPV_REC2 c%rowtype;
601 --
602 cursor c1(l_input_value_id NUMBER) is
603 select ''
604 from per_absence_attendance_types abt
605 where abt.input_value_id = l_input_value_id
606 and p_rowid is not null
607 and chartorowid(p_rowid) <> abt.rowid;
608 --
609 ABT_REC c1%rowtype;
610 --
611 cursor c2 is
612 select max(effective_end_date)
613 from pay_element_types_f
614 where element_type_id = p_element_type_id;
615 --
616 l_temp_iv NUMBER(22);
617 --
618 begin
619 --
620 /* If there is only one row returned by this statement then default the
621 values otherwise nullify the values */
622 --
623 hr_utility.set_location('per_abt_pkg.validate_element_name',1);
624 p_default_value := 'N';
625 OPEN C;
626 FETCH C INTO IPV_REC2;
627 if (C%FOUND) then
628 l_temp_iv := IPV_REC2.input_value_id;
629 FETCH C INTO IPV_REC2;
630 if (C%NOTFOUND) then
631 OPEN C1(l_temp_iv);
632 FETCH C1 INTO ABT_REC;
633 if (C1%NOTFOUND) then
634 IPV_REC := IPV_REC2;
635 p_default_value := 'Y';
636 end if;
637 CLOSE C1;
638 end if;
639 end if;
640 CLOSE C;
641 --
642 p_input_value_name := IPV_REC.name;
643 p_input_value_id := IPV_REC.input_value_id;
644 p_value_uom := IPV_REC.uom;
645 --
646 --
647 /* Get the maximum end date of the Element Type */
648 --
649 p_element_end_date := null;
650 --
651 OPEN C2;
652 FETCH C2 INTO p_element_end_date;
653 CLOSE C2;
654 --
655 end validate_element_name;
656 --
657 --
658 procedure get_uom(p_value_uom varchar2
659 ,p_hours_or_days IN OUT varchar2) is
660 --
661 cursor c is
662 select lookup_code
663 from hr_lookups
664 where lookup_type = 'HOURS_OR_DAYS'
665 and lookup_code = 'H'
666 and p_value_uom in
667 ('H_HH','H_DECIMAL1','H_DECIMAL2','H_DECIMAL3');
668 LKP_REC c%rowtype;
669 --
670 begin
671 --
672 hr_utility.set_location('per_abt_pkg.get_uom',1);
673 OPEN C;
674 FETCH C INTO LKP_REC;
675 if (C%FOUND) then
676 p_hours_or_days := LKP_REC.lookup_code;
677 end if;
678 CLOSE C;
679 --
680 end;
681 --
682 --
683 procedure ensure_fields_populated(p_inc_or_dec_flag VARCHAR2
684 ,p_hours_or_days VARCHAR2
685 ,p_input_value_id NUMBER
686 ,p_element_type_id NUMBER) is
687 begin
688 --
689 hr_utility.set_location('per_abt_pkg.ensure_fields_populated',1);
690 if p_element_type_id is not null then
691 --
692 if p_input_value_id is null then
693 hr_utility.set_message(801,'HR_7572_ALL_MAN_VAL_FIELD');
694 hr_utility.raise_error;
695 end if;
696 if p_hours_or_days is null then
697 hr_utility.set_message(801,'HR_7582_ALL_MAN_HOU_FIELD');
698 hr_utility.raise_error;
699 end if;
700 if p_inc_or_dec_flag is null then
701 hr_utility.set_message(801,'HR_7583_ALL_MAN_INC_FIELD');
702 hr_utility.raise_error;
703 end if;
704 end if;
705 --
706 end ensure_fields_populated;
707 --
708 --
709 procedure check_inputs_required(p_element_type_id NUMBER
710 ,p_input_value_id NUMBER) is
711 --
712 cursor c is
713 select ''
714 from pay_input_values
715 where element_type_id = p_element_type_id
716 and input_value_id <> p_input_value_id
717 and ((hot_default_flag = 'N'
718 and mandatory_flag = 'Y')
719 or (hot_default_flag = 'Y'
720 and default_value is null));
721 ipv_rec c%rowtype;
722 --
723 begin
724 --
725 hr_utility.set_location('per_abt_pkg.check_inputs_required',1);
726 OPEN C;
727 FETCH C INTO IPV_REC;
728 if (C%FOUND) then
729 CLOSE C;
730 hr_utility.set_message(801,'PER_7717_ABS_IPVAL_EXISTS');
731 hr_utility.raise_error;
732 end if;
733 CLOSE C;
734 --
735 end check_inputs_required;
736 --
737 --
738 procedure check_category(p_old_absence_category VARCHAR2
739 ,p_new_absence_category VARCHAR2
740 ,p_absence_attendance_type_id NUMBER) is
741 --
742 cursor c is
743 select ''
744 from per_absence_attendances
745 where absence_attendance_type_id = p_absence_attendance_type_id;
746 abs_rec c%rowtype;
747 --
748 begin
749 hr_utility.set_location('per_abt_pkg.check_category',1);
750 if p_old_absence_category is null or
751 p_old_absence_category = p_new_absence_category then
752 null;
753 else
754 OPEN C;
755 FETCH C INTO ABS_REC;
756 if (C%FOUND) then
757 CLOSE C;
758 hr_utility.set_message(801,'HR_6383_ABS_DET_NO_CHANGE');
759 hr_utility.raise_error;
760 end if;
761 CLOSE C;
762 end if;
763 end check_category;
764 --
765 --
766 procedure val_date_end(p_date_end IN OUT DATE
767 ,p_element_end_date DATE
768 ,p_end_of_time DATE) is
769 begin
770 hr_utility.set_location('per_abt_pkg.val_date_end',1);
771 if p_date_end is null then
772 if p_element_end_date is null or
773 p_element_end_date = p_end_of_time then
774 null;
775 else
776 p_date_end := p_element_end_date;
777 end if;
778 else
779 if p_element_end_date is not null and p_element_end_date < p_date_end
780 or p_element_end_date is null and p_end_of_time < p_date_end then
781 hr_utility.set_message(801,'PER_7800_DEF_ABS_ELEMENT_ENDS');
782 hr_utility.raise_error;
783 end if;
784 end if;
785 end val_date_end;
786 --
787 --
788 procedure abt_del_validation(p_absence_attendance_type_id NUMBER) is
789 --
790 l_exists VARCHAR2(1);
791 --
792 cursor c1 is
793 select ''
794 from per_absence_attendances
795 where absence_attendance_type_id = p_absence_attendance_type_id;
796 --
797 cursor c2 is
798 select ''
799 from per_abs_attendance_reasons
800 where absence_attendance_type_id = p_absence_attendance_type_id;
801 --
802 begin
803 --
804 hr_utility.set_location('per_abt_pkg.abt_del_validation',1);
805 OPEN C1;
806 FETCH C1 INTO l_exists;
807 if (C1%FOUND) then
808 CLOSE C1;
809 hr_utility.set_message(801,'PER_7059_EMP_ABS_DEL_TYPE');
810 hr_utility.raise_error;
811 end if;
812 CLOSE C1;
813 --
814 hr_utility.set_location('per_abt_pkg.abt_del_validation',2);
815 OPEN C2;
816 FETCH C2 INTO l_exists;
817 if (C2%FOUND) then
818 CLOSE C2;
819 hr_utility.set_message(801,'PER_7805_DEF_ABS_DEL_REASON');
820 hr_utility.raise_error;
821 end if;
822 CLOSE C2;
823 --
824 end abt_del_validation;
825 --
826 END PER_ABT_PKG;