[Home] [Help]
PACKAGE BODY: APPS.SSPWSMAT_PKG
Source
1 PACKAGE BODY SSPWSMAT_PKG as
2 /* $Header: sspwsmat.pkb 120.1 2005/06/15 04:09:34 tukumar noship $ */
3
4 procedure calculate_pab_form_fields
5 (
6 p_due_date in date,
7 p_ewc in out NOCOPY date,
8 p_qw in out NOCOPY date,
9 p_cont_emp_start_date in out NOCOPY date
10 ) is
11
12 BEGIN
13 p_ewc := ssp_pab_pkg.expected_week_of_confinement (p_due_date);
14 p_qw := ssp_pab_pkg.qualifying_week (p_due_date);
15 p_cont_emp_start_date := ssp_pab_pkg.continuous_employment_date(p_due_date);
16 END calculate_pab_form_fields;
17
18 procedure calculate_pad_form_fields
19 (
20 p_matching_date in date,
21 p_qw in out NOCOPY date,
22 p_cont_emp_start_date in out NOCOPY date
23 ) is
24
25 BEGIN
26 if p_matching_date is not null then
27 p_qw := ssp_pad_pkg.MATCHING_WEEK_OF_ADOPTION (p_matching_date);
28 p_cont_emp_start_date := ssp_pad_pkg.continuous_employment_date(p_matching_date);
29 end if;
30 END calculate_pad_form_fields;
31
32 procedure calculate_sap_form_fields
33 (
34 p_due_date in date,
35 p_matching_date in date,
36 p_earliest_mpp_start in out NOCOPY date,
37 p_qw in out NOCOPY date,
38 p_cont_emp_start_date in out NOCOPY date
39 ) is
40
41 BEGIN
42 if p_matching_date is not null then
43 p_qw := ssp_sap_pkg.MATCHING_WEEK_OF_ADOPTION (p_matching_date);
44 p_cont_emp_start_date :=ssp_sap_pkg.continuous_employment_date (p_matching_date);
45 end if;
46 p_earliest_mpp_start := ssp_sap_pkg.EARLIEST_APP_START_DATE (p_due_date);
47 END calculate_sap_form_fields;
48
49 procedure calculate_smp_form_fields
50 (
51 p_due_date in date,
52 p_ewc in out NOCOPY date,
53 p_earliest_mpp_start in out NOCOPY date,
54 p_qw in out NOCOPY date,
55 p_cont_emp_start_date in out NOCOPY date
56 ) is
57
58 BEGIN
59 p_qw := ssp_smp_pkg.qualifying_week (p_due_date);
60 p_ewc := ssp_smp_pkg.expected_week_of_confinement (p_due_date);
61 p_earliest_mpp_start := ssp_smp_pkg.earliest_mpp_start_date (p_due_date);
62 p_cont_emp_start_date := ssp_smp_pkg.continuous_employment_date (p_due_date);
63 END calculate_smp_form_fields;
64
65
66 procedure get_latest_absence_date
67 (
68 p_maternity_id in number,
69 p_absence_attendance_id in out NOCOPY number,
70 p_abs_end_date in out NOCOPY date,
71 p_rec_found in out NOCOPY boolean
72 )
73 is
74 cursor c1 is
75 select absence_attendance_id,
76 max(p.date_end) end_date
77 from per_absence_attendances p
78 where p.maternity_id = p_maternity_id
79 group by absence_attendance_id;
80 c1_rec c1%ROWTYPE;
81 BEGIN
82 open c1;
83 fetch c1 into c1_rec;
84 if c1%NOTFOUND then
85 p_rec_found := FALSE;
86 else
87 p_rec_found := TRUE;
88 p_abs_end_date := c1_rec.end_date;
89 p_absence_attendance_id := c1_rec.absence_attendance_id;
90 end if;
91 close c1;
92 END get_latest_absence_date;
93
94
95 procedure upd_abse_end_date (p_maternity_id in number,
96 p_absence_attendance_id in number,
97 p_absence_end_date in date) is
98 cursor c1 is
99 select rowid,
100 absence_attendance_id,
101 business_group_id,
102 absence_attendance_type_id,
103 Abs_Attendance_Reason_Id,
104 Person_Id,
105 Authorising_Person_Id,
106 Replacement_Person_Id,
107 Period_Of_Incapacity_Id,
108 Absence_Days,
109 Absence_Hours,
110 Comments,
111 Date_End,
112 Date_Notification,
113 Date_Projected_End,
114 Date_Projected_Start,
115 Date_Start,
116 Occurrence,
117 Ssp1_Issued,
118 Time_End,
119 Time_Projected_End,
120 Time_Projected_Start,
121 Time_Start,
122 Attribute_Category,
123 Attribute1,
124 Attribute2,
125 Attribute3,
126 Attribute4,
127 Attribute5,
128 Attribute6,
129 Attribute7,
130 Attribute8,
131 Attribute9,
132 Attribute10,
133 Attribute11,
134 Attribute12,
135 Attribute13,
136 Attribute14,
137 Attribute15,
138 Attribute16,
139 Attribute17,
140 Attribute18,
141 Attribute19,
142 Attribute20,
143 Linked_Absence_Id,
144 Sickness_Start_Date,
145 Sickness_End_date,
146 Accept_Late_Notification_Flag,
147 reason_for_notification_delay,
148 Pregnancy_Related_Illness,
149 Maternity_Id,
150 abs_information_category,
151 abs_information1,
152 abs_information2,
153 abs_information3,
154 abs_information4,
155 abs_information5,
156 abs_information6,
157 abs_information7,
158 abs_information8,
159 abs_information9,
160 abs_information10,
161 abs_information11,
162 abs_information12,
163 abs_information13,
164 abs_information14,
165 abs_information15,
166 abs_information16,
167 abs_information17,
168 abs_information18,
169 abs_information19,
170 abs_information20,
171 abs_information21,
172 abs_information22,
173 abs_information23,
174 abs_information24,
175 abs_information25,
176 abs_information26,
177 abs_information27,
178 abs_information28,
179 abs_information29,
180 abs_information30,
181 object_version_number --Bug fix 3633461
182 from per_absence_attendances
183 where maternity_id = p_maternity_id
184 and absence_attendance_id = p_absence_attendance_id;
185
186 /* Added cursors for bug fix 3633461*/
187 CURSOR csr_get_date is
188 SELECT effective_date
189 FROM fnd_sessions
190 WHERE session_id=userenv('sessionid');
191
192 CURSOR csr_get_hours_or_days(p_absence_attendance_type_id in NUMBER) is
193 SELECT hours_or_days
194 FROM per_absence_attendance_types
195 WHERE absence_attendance_type_id = p_absence_attendance_type_id;
196
197 c1_rec c1%ROWTYPE;
198
199 l_absence_days NUMBER;
200 l_absence_hours NUMBER;
201 l_absence_days_calc NUMBER;
202 l_absence_hours_calc NUMBER;
203 l_use_formula BOOLEAN;
204 l_hours_or_days VARCHAR2(1);
205 l_effective_date DATE;
206
207 BEGIN
208
209
210 --Bug fix 3633461 Start
211 OPEN csr_get_date;
212 FETCH csr_get_date into l_effective_date;
213 CLOSE csr_get_date;
214
215 open c1;
216 fetch c1 into c1_rec;
217
218 OPEN csr_get_hours_or_days(c1_rec.Absence_Attendance_Type_Id);
219 FETCH csr_get_hours_or_days into l_hours_or_days;
220 CLOSE csr_get_hours_or_days;
221
222 IF c1_rec.Date_Start is not null and p_absence_end_date is not null
223 THEN
224 per_abs_bus.calculate_absence_duration
225 (p_absence_attendance_id =>c1_rec.Absence_Attendance_Id
226 ,p_absence_attendance_type_id =>c1_rec.Absence_Attendance_Type_Id
227 ,p_business_group_id =>c1_rec.Business_Group_Id
228 ,p_object_version_number =>c1_rec.object_version_number
229 ,p_effective_date =>l_effective_date
230 ,p_person_id =>c1_rec.Person_Id
231 ,p_date_start =>c1_rec.Date_Start
232 ,p_date_end =>p_absence_end_date
233 ,p_time_start =>c1_rec.Time_Start
234 ,p_time_end =>c1_rec.Time_End
235 ,p_absence_days =>l_absence_days
236 ,p_absence_hours =>l_absence_hours
237 ,p_use_formula =>l_use_formula);
238 END IF;
239
240
241 IF l_use_formula = TRUE and l_hours_or_days = 'H' and c1_rec.Time_Start is not null and c1_rec.Time_End is not null THEN
242 l_absence_hours_calc := l_absence_hours;
243 ELSIF l_use_formula = TRUE THEN
244 l_absence_days_calc := l_absence_days;
245 END IF;
246
247 --Bug fix 3633461 End
248
249 PER_ABSENCE_ATTENDANCES_PKG.Update_Row(
250 X_Rowid => c1_rec.RowId,
251 X_Absence_Attendance_Id => c1_rec.Absence_Attendance_Id,
252 X_Business_Group_Id => c1_rec.Business_Group_Id,
253 X_Absence_Attendance_Type_Id => c1_rec.Absence_Attendance_Type_Id,
254 X_Abs_Attendance_Reason_Id => c1_rec.Abs_Attendance_Reason_Id,
255 X_Person_Id => c1_rec.Person_Id,
256 X_Authorising_Person_Id => c1_rec.Authorising_Person_Id,
257 X_Replacement_Person_Id => c1_rec.Replacement_Person_Id,
258 X_Period_Of_Incapacity_Id => c1_rec.Period_Of_Incapacity_Id,
259 X_Absence_Days => nvl(l_absence_days_calc,c1_rec.Absence_Days), --Bug fix 3633461
260 X_Absence_Hours => nvl(l_absence_hours_calc,c1_rec.Absence_Hours), --Bug fix 3633461
261 X_Comments => c1_rec.Comments,
262 X_Date_End => p_absence_end_date,
263 X_Date_Notification => c1_rec.Date_Notification,
264 X_Date_Projected_End => c1_rec.Date_Projected_End,
265 X_Date_Projected_Start => c1_rec.Date_Projected_Start,
266 X_Date_Start => c1_rec.Date_Start,
267 X_Occurrence => c1_rec.Occurrence,
268 X_Ssp1_Issued => c1_rec.Ssp1_Issued,
269 X_Time_End => c1_rec.Time_End,
270 X_Time_Projected_End => c1_rec.Time_Projected_End,
271 X_Time_Projected_Start => c1_rec.Time_Projected_Start,
272 X_Time_Start => c1_rec.Time_Start,
273 X_Attribute_Category => c1_rec.Attribute_Category,
274 X_Attribute1 => c1_rec.Attribute1,
275 X_Attribute2 => c1_rec.Attribute2,
276 X_Attribute3 => c1_rec.Attribute3,
277 X_Attribute4 => c1_rec.Attribute4,
278 X_Attribute5 => c1_rec.Attribute5,
279 X_Attribute6 => c1_rec.Attribute6,
280 X_Attribute7 => c1_rec.Attribute7,
281 X_Attribute8 => c1_rec.Attribute8,
282 X_Attribute9 => c1_rec.Attribute9,
283 X_Attribute10 => c1_rec.Attribute10,
284 X_Attribute11 => c1_rec.Attribute11,
285 X_Attribute12 => c1_rec.Attribute12,
286 X_Attribute13 => c1_rec.Attribute13,
287 X_Attribute14 => c1_rec.Attribute14,
288 X_Attribute15 => c1_rec.Attribute15,
289 X_Attribute16 => c1_rec.Attribute16,
290 X_Attribute17 => c1_rec.Attribute17,
291 X_Attribute18 => c1_rec.Attribute18,
292 X_Attribute19 => c1_rec.Attribute19,
293 X_Attribute20 => c1_rec.Attribute20,
294 X_Linked_Absence_Id => c1_rec.Linked_Absence_Id,
295 X_Sickness_Start_Date => c1_rec.Sickness_Start_Date,
296 X_Sickness_End_Date => c1_rec.Sickness_End_Date,
297 X_Accept_Late_Notif_Flag => c1_rec.Accept_late_Notification_flag,
298 X_REASON_FOR_LATE_NOTIFICATION =>
299 c1_rec.reason_for_notification_delay,
300 X_Pregnancy_Related_Illness => c1_rec.Pregnancy_Related_Illness,
301 X_Maternity_Id => c1_rec.Maternity_Id,
302 X_Abs_Information_Category => c1_rec.abs_information_category,
303 X_Abs_Information1 => c1_rec.abs_information1,
304 X_Abs_Information2 => c1_rec.abs_information2,
305 X_Abs_Information3 => c1_rec.abs_information3,
306 X_Abs_Information4 => c1_rec.abs_information4,
307 X_Abs_Information5 => c1_rec.abs_information5,
308 X_Abs_Information6 => c1_rec.abs_information6,
309 X_Abs_Information7 => c1_rec.abs_information7,
310 X_Abs_Information8 => c1_rec.abs_information8,
311 X_Abs_Information9 => c1_rec.abs_information9,
312 X_Abs_Information10 => c1_rec.abs_information10,
313 X_Abs_Information11 => c1_rec.abs_information11,
314 X_Abs_Information12 => c1_rec.abs_information12,
315 X_Abs_Information13 => c1_rec.abs_information13,
316 X_Abs_Information14 => c1_rec.abs_information14,
317 X_Abs_Information15 => c1_rec.abs_information15,
318 X_Abs_Information16 => c1_rec.abs_information16,
319 X_Abs_Information17 => c1_rec.abs_information17,
320 X_Abs_Information18 => c1_rec.abs_information18,
321 X_Abs_Information19 => c1_rec.abs_information19,
322 X_Abs_Information20 => c1_rec.abs_information20,
323 X_Abs_Information21 => c1_rec.abs_information21,
324 X_Abs_Information22 => c1_rec.abs_information22,
325 X_Abs_Information23 => c1_rec.abs_information23,
326 X_Abs_Information24 => c1_rec.abs_information24,
327 X_Abs_Information25 => c1_rec.abs_information25,
328 X_Abs_Information26 => c1_rec.abs_information26,
329 X_Abs_Information27 => c1_rec.abs_information27,
330 X_Abs_Information28 => c1_rec.abs_information28,
331 X_Abs_Information29 => c1_rec.abs_information29,
332 X_Abs_Information30 => c1_rec.abs_information30);
333 close c1;
334 END upd_abse_end_date;
335
336 END SSPWSMAT_PKG;