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