[Home] [Help]
PACKAGE BODY: APPS.PER_ABSENCE_RECORD
Source
1 PACKAGE BODY per_absence_record AS
2 /* $Header: peabsqry.pkb 120.0.12010000.4 2009/01/22 09:44:27 srgnanas noship $ */
3 PROCEDURE get_absence_details( p_query_options IN absence_input_rectype,
4 p_absences OUT NOCOPY absence_tabletype)
5 IS
6 CURSOR cur_absence(p_person_id PER_ABSENCE_ATTENDANCES_V.person_id%TYPE,
7 p_start_person_id PER_ABSENCE_ATTENDANCES_V.person_id%TYPE,
8 p_end_person_id PER_ABSENCE_ATTENDANCES_V.person_id%TYPE,
9 p_absence_attendance_id PER_ABSENCE_ATTENDANCES_V.absence_attendance_id%TYPE) IS
10 SELECT a.absence_attendance_id,
11 a.business_group_id,
12 a.absence_attendance_type_id,
13 a.abs_attendance_reason_id,
14 a.person_id,
15 a.authorising_person_id,
16 a.replacement_person_id,
17 a.period_of_incapacity_id,
18 a.absence_days,
19 a.absence_hours,
20 a.comments,
21 a.date_end,
22 a.date_notification,
23 a.date_projected_end,
24 a.date_projected_start,
25 a.date_start,
26 a.occurrence,
27 a.ssp1_issued,
28 a.time_end,
29 a.time_projected_end,
30 a.time_projected_start,
31 a.time_start,
32 a.request_id,
33 a.program_application_id,
34 a.program_id,
35 a.program_update_date,
36 a.attribute_category,
37 a.attribute1,
38 a.attribute2,
39 a.attribute3,
40 a.attribute4,
41 a.attribute5,
42 a.attribute6,
43 a.attribute7,
44 a.attribute8,
45 a.attribute9,
46 a.attribute10,
47 a.attribute11,
48 a.attribute12,
49 a.attribute13,
50 a.attribute14,
51 a.attribute15,
52 a.attribute16,
53 a.attribute17,
54 a.attribute18,
55 a.attribute19,
56 a.attribute20,
57 a.last_update_date,
58 a.last_updated_by,
59 a.last_update_login,
60 a.created_by,
61 a.creation_date,
62 a.object_version_number,
63 a.c_type_desc,
64 a.element_type_id,
65 a.absence_category,
66 a.category_meaning,
67 a.hours_or_days,
68 a.value_column,
69 a.increasing_or_decreasing,
70 a.value_uom,
71 a.c_abs_input_value_id,
72 a.abs_date_from,
73 a.abs_date_to,
74 a.c_auth_name,
75 a.c_auth_no,
76 a.c_rep_name,
77 a.c_rep_no,
78 a.c_reason_desc,
79 a.linked_absence_id,
80 a.sickness_start_date,
81 a.sickness_end_date,
82 a.accept_late_notification_flag,
83 a.reason_for_late_notification,
84 a.pregnancy_related_illness,
85 a.maternity_id,
86 a.smp_due_date,
87 a.abs_information_category,
88 a.abs_information1,
89 a.abs_information2,
90 a.abs_information3,
91 a.abs_information4,
92 a.abs_information5,
93 a.abs_information6,
94 a.abs_information7,
95 a.abs_information8,
96 a.abs_information9,
97 a.abs_information10,
98 a.abs_information11,
99 a.abs_information12,
100 a.abs_information13,
101 a.abs_information14,
102 a.abs_information15,
103 a.abs_information16,
104 a.abs_information17,
105 a.abs_information18,
106 a.abs_information19,
107 a.abs_information20,
108 a.abs_information21,
109 a.abs_information22,
110 a.abs_information23,
111 a.abs_information24,
112 a.abs_information25,
113 a.abs_information26,
114 a.abs_information27,
115 a.abs_information28,
116 a.abs_information29,
117 a.abs_information30,
118 a.approval_status,
119 a.confirmed_until,
120 a.source,
121 a.advance_pay,
122 a.absence_case_id
123 FROM PER_ABSENCE_ATTENDANCES_V a
124 WHERE nvl(p_person_id, a.person_id) = a.person_id
125 AND a.person_id BETWEEN nvl(p_start_person_id, a.person_id)
126 AND nvl(p_end_person_id, a.person_id)
127 AND nvl(p_absence_attendance_id, a.absence_attendance_id) = a.absence_attendance_id;
128
129 l_absence_rec absence_rectype;
130 l_absence_tbl absence_tabletype;
131 l_count NUMBER := 1;
132 BEGIN
133 l_absence_tbl := absence_tabletype();
134 OPEN cur_absence( p_query_options.person_id,
135 p_query_options.start_person_id,
136 p_query_options.end_person_id,
137 p_query_options.absence_attendance_id);
138 LOOP
139 FETCH cur_absence INTO l_absence_rec;
140 IF cur_absence%NOTFOUND THEN
141 EXIT;
142 END IF;
143 l_absence_tbl.EXTEND(1);
144 l_absence_tbl(l_count) := l_absence_rec;
145 l_count := l_count + 1;
146 END LOOP;
147 p_absences := l_absence_tbl;
148 END get_absence_details;
149 END per_absence_record;