[Home] [Help]
PACKAGE BODY: APPS.PER_ABS_SHD
Source
1 Package Body per_abs_shd as
2 /* $Header: peabsrhi.pkb 120.17.12010000.3 2008/09/12 11:33:01 pvelugul ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' per_abs_shd.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------------------------< return_api_dml_status >-------------------------|
12 -- ----------------------------------------------------------------------------
13 Function return_api_dml_status Return Boolean Is
14 --
15 Begin
16 --
17 Return (nvl(g_api_dml, false));
18 --
19 End return_api_dml_status;
20 --
21 -- ----------------------------------------------------------------------------
22 -- |---------------------------< constraint_error >---------------------------|
23 -- ----------------------------------------------------------------------------
24 Procedure constraint_error
25 (p_constraint_name in all_constraints.constraint_name%TYPE
26 ) Is
27 --
28 l_proc varchar2(72) := g_package||'constraint_error';
29 --
30 Begin
31 --
32 If (p_constraint_name = 'PER_ABSENCE_ATTENDANCES_FK1') Then
33 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
34 fnd_message.set_token('PROCEDURE', l_proc);
35 fnd_message.set_token('STEP','5');
36 fnd_message.raise_error;
37 ElsIf (p_constraint_name = 'PER_ABSENCE_ATTENDANCES_FK2') Then
38 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
39 fnd_message.set_token('PROCEDURE', l_proc);
40 fnd_message.set_token('STEP','10');
41 fnd_message.raise_error;
42 ElsIf (p_constraint_name = 'PER_ABSENCE_ATTENDANCES_FK3') Then
43 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
44 fnd_message.set_token('PROCEDURE', l_proc);
45 fnd_message.set_token('STEP','15');
46 fnd_message.raise_error;
47 ElsIf (p_constraint_name = 'PER_ABSENCE_ATTENDANCES_FK4') Then
48 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
49 fnd_message.set_token('PROCEDURE', l_proc);
50 fnd_message.set_token('STEP','20');
51 fnd_message.raise_error;
52 ElsIf (p_constraint_name = 'PER_ABSENCE_ATTENDANCES_PK') Then
53 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
54 fnd_message.set_token('PROCEDURE', l_proc);
55 fnd_message.set_token('STEP','25');
56 fnd_message.raise_error;
57 ElsIf (p_constraint_name = 'SSP_ABA_MATERNITY_NOT_SICKNESS') Then
58 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
59 fnd_message.set_token('PROCEDURE', l_proc);
60 fnd_message.set_token('STEP','30');
61 fnd_message.raise_error;
62 ElsIf (p_constraint_name = 'SSP_ABA_SICKNESS_ATTRIBUTES') Then
63 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
64 fnd_message.set_token('PROCEDURE', l_proc);
65 fnd_message.set_token('STEP','35');
66 fnd_message.raise_error;
67 ElsIf (p_constraint_name = 'SSP_ABA_SICKNESS_END_DATE') Then
68 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
69 fnd_message.set_token('PROCEDURE', l_proc);
70 fnd_message.set_token('STEP','40');
71 fnd_message.raise_error;
72 ElsIf (p_constraint_name = 'SSP_ABA_SICKNESS_START_DATE') Then
73 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
74 fnd_message.set_token('PROCEDURE', l_proc);
75 fnd_message.set_token('STEP','45');
76 fnd_message.raise_error;
77 ElsIf (p_constraint_name = 'SSP_ABA_SICK_NOTIFICATION_DATE') Then
78 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
79 fnd_message.set_token('PROCEDURE', l_proc);
80 fnd_message.set_token('STEP','50');
81 fnd_message.raise_error;
82 ElsIf (p_constraint_name = 'SSP_ABA_START_AND_END_DATES') Then
83 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
84 fnd_message.set_token('PROCEDURE', l_proc);
85 fnd_message.set_token('STEP','55');
86 fnd_message.raise_error;
87 ElsIf (p_constraint_name = 'SSP_ABA_TIME_END_FORMAT') Then
88 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
89 fnd_message.set_token('PROCEDURE', l_proc);
90 fnd_message.set_token('STEP','60');
91 fnd_message.raise_error;
92 ElsIf (p_constraint_name = 'SSP_ABA_TIME_START_FORMAT') Then
93 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
94 fnd_message.set_token('PROCEDURE', l_proc);
95 fnd_message.set_token('STEP','65');
96 fnd_message.raise_error;
97 Else
98 fnd_message.set_name('PAY', 'HR_7877_API_INVALID_CONSTRAINT');
99 fnd_message.set_token('PROCEDURE', l_proc);
100 fnd_message.set_token('CONSTRAINT_NAME', p_constraint_name);
101 fnd_message.raise_error;
102 End If;
103 --
104 End constraint_error;
105 --
106 -- ----------------------------------------------------------------------------
107 -- |-----------------------------< api_updating >-----------------------------|
108 -- ----------------------------------------------------------------------------
109 Function api_updating
110 (p_absence_attendance_id in number
111 ,p_object_version_number in number
112 )
113 Return Boolean Is
114 --
115 --
116 -- Cursor selects the 'current' row from the HR Schema
117 --
118 Cursor C_Sel1 is
119 select
120 absence_attendance_id
121 ,business_group_id
122 ,absence_attendance_type_id
123 ,abs_attendance_reason_id
124 ,person_id
125 ,authorising_person_id
126 ,replacement_person_id
127 ,period_of_incapacity_id
128 ,absence_days
129 ,absence_hours
130 ,comments
131 ,date_end
132 ,date_notification
133 ,date_projected_end
134 ,date_projected_start
135 ,date_start
136 ,occurrence
137 ,ssp1_issued
138 ,time_end
139 ,time_projected_end
140 ,time_projected_start
141 ,time_start
142 ,request_id
143 ,program_application_id
144 ,program_id
145 ,program_update_date
146 ,attribute_category
147 ,attribute1
148 ,attribute2
149 ,attribute3
150 ,attribute4
151 ,attribute5
152 ,attribute6
153 ,attribute7
154 ,attribute8
155 ,attribute9
156 ,attribute10
157 ,attribute11
158 ,attribute12
159 ,attribute13
160 ,attribute14
161 ,attribute15
162 ,attribute16
163 ,attribute17
164 ,attribute18
165 ,attribute19
166 ,attribute20
167 ,maternity_id
168 ,sickness_start_date
169 ,sickness_end_date
170 ,pregnancy_related_illness
171 ,reason_for_notification_delay
172 ,accept_late_notification_flag
173 ,linked_absence_id
174 ,abs_information_category
175 ,abs_information1
176 ,abs_information2
177 ,abs_information3
178 ,abs_information4
179 ,abs_information5
180 ,abs_information6
181 ,abs_information7
182 ,abs_information8
183 ,abs_information9
184 ,abs_information10
185 ,abs_information11
186 ,abs_information12
187 ,abs_information13
188 ,abs_information14
189 ,abs_information15
190 ,abs_information16
191 ,abs_information17
192 ,abs_information18
193 ,abs_information19
194 ,abs_information20
195 ,abs_information21
196 ,abs_information22
197 ,abs_information23
198 ,abs_information24
199 ,abs_information25
200 ,abs_information26
201 ,abs_information27
202 ,abs_information28
203 ,abs_information29
204 ,abs_information30
205 ,absence_case_id
206 ,batch_id
207 ,object_version_number
208 from per_absence_attendances
209 where absence_attendance_id = p_absence_attendance_id;
210 --
211 l_fct_ret boolean;
212 --
213 Begin
214 --
215 If (p_absence_attendance_id is null and
216 p_object_version_number is null
217 ) Then
218 --
219 -- One of the primary key arguments is null therefore we must
220 -- set the returning function value to false
221 --
222 l_fct_ret := false;
223 Else
224 If (p_absence_attendance_id
225 = per_abs_shd.g_old_rec.absence_attendance_id and
226 p_object_version_number
227 = per_abs_shd.g_old_rec.object_version_number
228 ) Then
229 --
230 -- The g_old_rec is current therefore we must
231 -- set the returning function to true
232 --
233 l_fct_ret := true;
234 Else
235 --
236 -- Select the current row into g_old_rec
237 --
238 Open C_Sel1;
239 Fetch C_Sel1 Into per_abs_shd.g_old_rec;
240 If C_Sel1%notfound Then
241 Close C_Sel1;
242 --
243 -- The primary key is invalid therefore we must error
244 --
245 fnd_message.set_name('PAY', 'HR_7220_INVALID_PRIMARY_KEY');
246 fnd_message.raise_error;
247 End If;
248 Close C_Sel1;
249 If (p_object_version_number
250 <> per_abs_shd.g_old_rec.object_version_number) Then
251 fnd_message.set_name('PAY', 'HR_7155_OBJECT_INVALID');
252 fnd_message.raise_error;
253 End If;
254 l_fct_ret := true;
255 End If;
256 End If;
257 Return (l_fct_ret);
258 --
259 End api_updating;
260 --
261 -- ----------------------------------------------------------------------------
262 -- |---------------------------------< lck >----------------------------------|
263 -- ----------------------------------------------------------------------------
264 Procedure lck
265 (p_absence_attendance_id in number
266 ,p_object_version_number in number
267 ) is
268 --
269 -- Cursor selects the 'current' row from the HR Schema
270 --
271 Cursor C_Sel1 is
272 select
273 absence_attendance_id
274 ,business_group_id
275 ,absence_attendance_type_id
276 ,abs_attendance_reason_id
277 ,person_id
278 ,authorising_person_id
279 ,replacement_person_id
280 ,period_of_incapacity_id
281 ,absence_days
282 ,absence_hours
283 ,comments
284 ,date_end
285 ,date_notification
286 ,date_projected_end
287 ,date_projected_start
288 ,date_start
289 ,occurrence
290 ,ssp1_issued
291 ,time_end
292 ,time_projected_end
293 ,time_projected_start
294 ,time_start
295 ,request_id
296 ,program_application_id
297 ,program_id
298 ,program_update_date
299 ,attribute_category
300 ,attribute1
301 ,attribute2
302 ,attribute3
303 ,attribute4
304 ,attribute5
305 ,attribute6
306 ,attribute7
307 ,attribute8
308 ,attribute9
309 ,attribute10
310 ,attribute11
311 ,attribute12
312 ,attribute13
313 ,attribute14
314 ,attribute15
315 ,attribute16
316 ,attribute17
317 ,attribute18
318 ,attribute19
319 ,attribute20
320 ,maternity_id
321 ,sickness_start_date
322 ,sickness_end_date
323 ,pregnancy_related_illness
324 ,reason_for_notification_delay
325 ,accept_late_notification_flag
326 ,linked_absence_id
327 ,abs_information_category
328 ,abs_information1
329 ,abs_information2
330 ,abs_information3
331 ,abs_information4
332 ,abs_information5
333 ,abs_information6
334 ,abs_information7
335 ,abs_information8
336 ,abs_information9
337 ,abs_information10
338 ,abs_information11
339 ,abs_information12
340 ,abs_information13
341 ,abs_information14
342 ,abs_information15
343 ,abs_information16
344 ,abs_information17
345 ,abs_information18
346 ,abs_information19
347 ,abs_information20
348 ,abs_information21
349 ,abs_information22
350 ,abs_information23
351 ,abs_information24
352 ,abs_information25
353 ,abs_information26
354 ,abs_information27
355 ,abs_information28
356 ,abs_information29
357 ,abs_information30
358 ,absence_case_id
359 ,batch_id
360 ,object_version_number
361 from per_absence_attendances
362 where absence_attendance_id = p_absence_attendance_id
363 for update nowait;
364 --
365 l_proc varchar2(72) := g_package||'lck';
366 --
367 Begin
368 hr_utility.set_location('Entering:'||l_proc, 5);
369 --
370 hr_api.mandatory_arg_error
371 (p_api_name => l_proc
372 ,p_argument => 'ABSENCE_ATTENDANCE_ID'
373 ,p_argument_value => p_absence_attendance_id
374 );
375 --
376 Open C_Sel1;
377 Fetch C_Sel1 Into per_abs_shd.g_old_rec;
378 If C_Sel1%notfound then
379 Close C_Sel1;
380 --
381 -- The primary key is invalid therefore we must error
382 --
383 fnd_message.set_name('PAY', 'HR_7220_INVALID_PRIMARY_KEY');
384 fnd_message.raise_error;
385 End If;
386 Close C_Sel1;
387 If (p_object_version_number
388 <> per_abs_shd.g_old_rec.object_version_number) Then
389 fnd_message.set_name('PAY', 'HR_7155_OBJECT_INVALID');
390 fnd_message.raise_error;
391 End If;
392 --
393 hr_utility.set_location(' Leaving:'||l_proc, 10);
394 --
395 -- We need to trap the ORA LOCK exception
396 --
397 Exception
398 When HR_Api.Object_Locked then
399 --
400 -- The object is locked therefore we need to supply a meaningful
401 -- error message.
402 --
403 fnd_message.set_name('PAY', 'HR_7165_OBJECT_LOCKED');
404 fnd_message.set_token('TABLE_NAME', 'per_absence_attendances');
405 fnd_message.raise_error;
406 End lck;
407 --
408 -- ----------------------------------------------------------------------------
409 -- |-----------------------------< convert_args >-----------------------------|
413 ,p_business_group_id in number
410 -- ----------------------------------------------------------------------------
411 Function convert_args
412 (p_absence_attendance_id in number
414 ,p_absence_attendance_type_id in number
415 ,p_abs_attendance_reason_id in number
416 ,p_person_id in number
417 ,p_authorising_person_id in number
418 ,p_replacement_person_id in number
419 ,p_period_of_incapacity_id in number
420 ,p_absence_days in number
421 ,p_absence_hours in number
422 --changes start for bug 5987410
423 --,p_comments in varchar2
424 ,p_comments in long
425 --changes end for bug 5987410
426 ,p_date_end in date
427 ,p_date_notification in date
428 ,p_date_projected_end in date
429 ,p_date_projected_start in date
430 ,p_date_start in date
431 ,p_occurrence in number
432 ,p_ssp1_issued in varchar2
433 ,p_time_end in varchar2
434 ,p_time_projected_end in varchar2
435 ,p_time_projected_start in varchar2
436 ,p_time_start in varchar2
437 ,p_request_id in number
438 ,p_program_application_id in number
439 ,p_program_id in number
440 ,p_program_update_date in date
441 ,p_attribute_category in varchar2
442 ,p_attribute1 in varchar2
443 ,p_attribute2 in varchar2
444 ,p_attribute3 in varchar2
445 ,p_attribute4 in varchar2
446 ,p_attribute5 in varchar2
447 ,p_attribute6 in varchar2
448 ,p_attribute7 in varchar2
449 ,p_attribute8 in varchar2
450 ,p_attribute9 in varchar2
451 ,p_attribute10 in varchar2
452 ,p_attribute11 in varchar2
453 ,p_attribute12 in varchar2
454 ,p_attribute13 in varchar2
455 ,p_attribute14 in varchar2
456 ,p_attribute15 in varchar2
457 ,p_attribute16 in varchar2
458 ,p_attribute17 in varchar2
459 ,p_attribute18 in varchar2
460 ,p_attribute19 in varchar2
461 ,p_attribute20 in varchar2
462 ,p_maternity_id in number
463 ,p_sickness_start_date in date
464 ,p_sickness_end_date in date
465 ,p_pregnancy_related_illness in varchar2
466 ,p_reason_for_notification_dela in varchar2
467 ,p_accept_late_notification_fla in varchar2
468 ,p_linked_absence_id in number
469 ,p_abs_information_category in varchar2
470 ,p_abs_information1 in varchar2
471 ,p_abs_information2 in varchar2
472 ,p_abs_information3 in varchar2
473 ,p_abs_information4 in varchar2
474 ,p_abs_information5 in varchar2
475 ,p_abs_information6 in varchar2
476 ,p_abs_information7 in varchar2
477 ,p_abs_information8 in varchar2
478 ,p_abs_information9 in varchar2
479 ,p_abs_information10 in varchar2
480 ,p_abs_information11 in varchar2
481 ,p_abs_information12 in varchar2
482 ,p_abs_information13 in varchar2
483 ,p_abs_information14 in varchar2
484 ,p_abs_information15 in varchar2
485 ,p_abs_information16 in varchar2
486 ,p_abs_information17 in varchar2
487 ,p_abs_information18 in varchar2
488 ,p_abs_information19 in varchar2
489 ,p_abs_information20 in varchar2
490 ,p_abs_information21 in varchar2
491 ,p_abs_information22 in varchar2
492 ,p_abs_information23 in varchar2
493 ,p_abs_information24 in varchar2
494 ,p_abs_information25 in varchar2
495 ,p_abs_information26 in varchar2
496 ,p_abs_information27 in varchar2
497 ,p_abs_information28 in varchar2
498 ,p_abs_information29 in varchar2
499 ,p_abs_information30 in varchar2
500 ,p_absence_case_id in number
501 ,p_batch_id in number
502 ,p_object_version_number in number
503 )
504 Return g_rec_type is
505 --
506 l_rec g_rec_type;
507 --
508 Begin
509 --
510 -- Convert arguments into local l_rec structure.
511 --
512 l_rec.absence_attendance_id := p_absence_attendance_id;
513 l_rec.business_group_id := p_business_group_id;
514 l_rec.absence_attendance_type_id := p_absence_attendance_type_id;
515 l_rec.abs_attendance_reason_id := p_abs_attendance_reason_id;
516 l_rec.person_id := p_person_id;
517 l_rec.authorising_person_id := p_authorising_person_id;
518 l_rec.replacement_person_id := p_replacement_person_id;
522 l_rec.comments := p_comments;
519 l_rec.period_of_incapacity_id := p_period_of_incapacity_id;
520 l_rec.absence_days := p_absence_days;
521 l_rec.absence_hours := p_absence_hours;
523 l_rec.date_end := p_date_end;
524 l_rec.date_notification := p_date_notification;
525 l_rec.date_projected_end := p_date_projected_end;
526 l_rec.date_projected_start := p_date_projected_start;
527 l_rec.date_start := p_date_start;
528 l_rec.occurrence := p_occurrence;
529 l_rec.ssp1_issued := p_ssp1_issued;
530 l_rec.time_end := p_time_end;
531 l_rec.time_projected_end := p_time_projected_end;
532 l_rec.time_projected_start := p_time_projected_start;
533 l_rec.time_start := p_time_start;
534 l_rec.request_id := p_request_id;
535 l_rec.program_application_id := p_program_application_id;
536 l_rec.program_id := p_program_id;
537 l_rec.program_update_date := p_program_update_date;
538 l_rec.attribute_category := p_attribute_category;
539 l_rec.attribute1 := p_attribute1;
540 l_rec.attribute2 := p_attribute2;
541 l_rec.attribute3 := p_attribute3;
542 l_rec.attribute4 := p_attribute4;
543 l_rec.attribute5 := p_attribute5;
544 l_rec.attribute6 := p_attribute6;
545 l_rec.attribute7 := p_attribute7;
546 l_rec.attribute8 := p_attribute8;
547 l_rec.attribute9 := p_attribute9;
548 l_rec.attribute10 := p_attribute10;
549 l_rec.attribute11 := p_attribute11;
550 l_rec.attribute12 := p_attribute12;
551 l_rec.attribute13 := p_attribute13;
552 l_rec.attribute14 := p_attribute14;
553 l_rec.attribute15 := p_attribute15;
554 l_rec.attribute16 := p_attribute16;
555 l_rec.attribute17 := p_attribute17;
556 l_rec.attribute18 := p_attribute18;
557 l_rec.attribute19 := p_attribute19;
558 l_rec.attribute20 := p_attribute20;
559 l_rec.maternity_id := p_maternity_id;
560 l_rec.sickness_start_date := p_sickness_start_date;
561 l_rec.sickness_end_date := p_sickness_end_date;
562 l_rec.pregnancy_related_illness := p_pregnancy_related_illness;
563 l_rec.reason_for_notification_delay := p_reason_for_notification_dela;
564 l_rec.accept_late_notification_flag := p_accept_late_notification_fla;
565 l_rec.linked_absence_id := p_linked_absence_id;
566 l_rec.abs_information_category := p_abs_information_category;
567 l_rec.abs_information1 := p_abs_information1;
568 l_rec.abs_information2 := p_abs_information2;
569 l_rec.abs_information3 := p_abs_information3;
570 l_rec.abs_information4 := p_abs_information4;
571 l_rec.abs_information5 := p_abs_information5;
572 l_rec.abs_information6 := p_abs_information6;
573 l_rec.abs_information7 := p_abs_information7;
574 l_rec.abs_information8 := p_abs_information8;
575 l_rec.abs_information9 := p_abs_information9;
576 l_rec.abs_information10 := p_abs_information10;
577 l_rec.abs_information11 := p_abs_information11;
578 l_rec.abs_information12 := p_abs_information12;
579 l_rec.abs_information13 := p_abs_information13;
580 l_rec.abs_information14 := p_abs_information14;
581 l_rec.abs_information15 := p_abs_information15;
582 l_rec.abs_information16 := p_abs_information16;
583 l_rec.abs_information17 := p_abs_information17;
584 l_rec.abs_information18 := p_abs_information18;
585 l_rec.abs_information19 := p_abs_information19;
586 l_rec.abs_information20 := p_abs_information20;
587 l_rec.abs_information21 := p_abs_information21;
588 l_rec.abs_information22 := p_abs_information22;
589 l_rec.abs_information23 := p_abs_information23;
590 l_rec.abs_information24 := p_abs_information24;
591 l_rec.abs_information25 := p_abs_information25;
592 l_rec.abs_information26 := p_abs_information26;
593 l_rec.abs_information27 := p_abs_information27;
594 l_rec.abs_information28 := p_abs_information28;
595 l_rec.abs_information29 := p_abs_information29;
596 l_rec.abs_information30 := p_abs_information30;
597 l_rec.absence_case_id := p_absence_case_id;
598 l_rec.batch_id := p_batch_id;
599 l_rec.object_version_number := p_object_version_number;
600 --
601 -- Return the plsql record structure.
602 --
603 Return(l_rec);
604 --
605 End convert_args;
606 --
607 end per_abs_shd;