[Home] [Help]
PACKAGE BODY: APPS.PAY_NO_ARCHIVE_ABSENCE
Source
1 PACKAGE BODY PAY_NO_ARCHIVE_ABSENCE as
2 /* $Header: pynoabsa.pkb 120.0.12000000.1 2007/05/22 05:24:58 rajesrin noship $ */
3 TYPE lock_rec IS RECORD (
4 archive_assact_id NUMBER);
5 TYPE lock_table IS TABLE OF lock_rec INDEX BY BINARY_INTEGER;
6 g_debug boolean := hr_utility.debug_enabled;
7 g_lock_table lock_table;
8 g_package VARCHAR2(33) := ' PAY_NO_ARCHIVE_ABSENCE.';
9 g_err_num NUMBER;
10 g_errm VARCHAR2(150);
11 /* GET PARAMETER */
12 FUNCTION GET_PARAMETER(
13 p_parameter_string IN VARCHAR2
14 ,p_token IN VARCHAR2
15 ,p_segment_number IN NUMBER default NULL ) RETURN VARCHAR2
16 IS
17 l_parameter pay_payroll_actions.legislative_parameters%TYPE:=NULL;
18 l_start_pos NUMBER;
19 l_delimiter VARCHAR2(1):=' ';
20 l_proc VARCHAR2(40):= g_package||' get parameter ';
21 BEGIN
22
23 IF g_debug THEN
24 hr_utility.set_location(' Entering Function GET_PARAMETER',10);
25 END IF;
26 l_start_pos := instr(' '||p_parameter_string,l_delimiter||p_token||'=');
27 --
28 IF l_start_pos = 0 THEN
29 l_delimiter := '|';
30 l_start_pos := instr(' '||p_parameter_string,l_delimiter||p_token||'=');
31 END IF;
32 IF l_start_pos <> 0 THEN
33 l_start_pos := l_start_pos + length(p_token||'=');
34 l_parameter := substr(p_parameter_string,
35 l_start_pos,
36 instr(p_parameter_string||' ',
37 l_delimiter,l_start_pos)
38 - l_start_pos);
39 IF p_segment_number IS NOT NULL THEN
40 l_parameter := ':'||l_parameter||':';
41 l_parameter := substr(l_parameter,
42 instr(l_parameter,':',1,p_segment_number)+1,
43 instr(l_parameter,':',1,p_segment_number+1) -1
44 - instr(l_parameter,':',1,p_segment_number));
45 END IF;
46 END IF;
47 --
48 IF g_debug THEN
49 hr_utility.set_location(' Leaving Function GET_PARAMETER',20);
50 END IF;
51 RETURN l_parameter;
52 END;
53 /* GET ALL PARAMETERS */
54 PROCEDURE GET_ALL_PARAMETERS(
55 p_payroll_action_id IN NUMBER
56 ,p_business_group_id OUT NOCOPY NUMBER
57 ,p_legal_employer_id OUT NOCOPY NUMBER
58 ,p_start_date OUT NOCOPY DATE
59 ,p_end_date OUT NOCOPY DATE
60 ,p_archive OUT NOCOPY VARCHAR2
61 ,p_effective_date OUT NOCOPY DATE
62 ) IS
63 CURSOR csr_parameter_info(p_payroll_action_id NUMBER) IS
64 SELECT PAY_NO_ARCHIVE_ABSENCE.GET_PARAMETER(legislative_parameters,
65 'LEGAL_EMPLOYER')
66 ,FND_DATE.canonical_to_date(PAY_NO_ARCHIVE_ABSENCE.GET_PARAMETER
67 (legislative_parameters,'START_DATE'))
68 ,FND_DATE.canonical_to_date(PAY_NO_ARCHIVE_ABSENCE.GET_PARAMETER
69 (legislative_parameters,'END_DATE'))
70 ,PAY_NO_ARCHIVE_ABSENCE.GET_PARAMETER(legislative_parameters,'ARCHIVE')
71 ,effective_date
72 ,business_group_id
73 FROM pay_payroll_actions
74 WHERE payroll_action_id = p_payroll_action_id;
75 l_proc VARCHAR2(240):= g_package||' GET_ALL_PARAMETERS ';
76 --
77 BEGIN
78 fnd_file.put_line (fnd_file.LOG, 'Entering Get all Parameters' );
79 OPEN csr_parameter_info (p_payroll_action_id);
80 FETCH csr_parameter_info
81 INTO p_legal_employer_id
82 ,p_start_date
83 ,p_end_date
84 ,p_archive
85 ,p_effective_date
86 ,p_business_group_id;
87 CLOSE csr_parameter_info;
88 --
89 IF g_debug THEN
90 hr_utility.set_location(' Leaving Procedure GET_ALL_PARAMETERS',30);
91 END IF;
92 END GET_ALL_PARAMETERS;
93
94 /*Start of sum_no_absence*/
95 Procedure sum_no_absence(p_sex in varchar2,
96 p_absence_days in number,
97 p_quater in varchar2,
98 p_certificate_type in varchar2,
99 p_occurence in number,
100 p_absence_category in varchar2
101 )
102 is
103 l_quater number:=0;
104 Begin
105 l_quater := to_number(substr(p_quater,2)) - 1;
106
107 IF p_absence_category in ('S','PTS') then
108 if p_sex = 'M' then
109 absmale(l_quater).initialized:='Y';
110 absmale(l_quater).quater:=p_quater;
111
112 if p_absence_days <= 3 then
113 if p_certificate_type= 'SC' then
114 absmale(l_quater).sick_1_3_ocr_sc := nvl(absmale(l_quater).sick_1_3_ocr_sc,0) + p_occurence;
115 absmale(l_quater).sick_1_3_days_sc := nvl(absmale(l_quater).sick_1_3_days_sc,0) + p_absence_days;
116 elsif p_certificate_type = 'DC' then
117 absmale(l_quater).sick_1_3_ocr_dc := nvl(absmale(l_quater).sick_1_3_ocr_dc,0) + p_occurence;
118 absmale(l_quater).sick_1_3_days_dc := nvl(absmale(l_quater).sick_1_3_days_dc,0) + p_absence_days;
119 end if;
120 elsif p_absence_days > 3 and p_absence_days <= 16 then
121 absmale(l_quater).sick_4_16_ocrs := nvl(absmale(l_quater).sick_4_16_ocrs,0) + p_occurence;
122 absmale(l_quater).sick_4_16_days := nvl(absmale(l_quater).sick_4_16_days,0) + p_absence_days;
123
124 elsif p_absence_days > 16 then
125 absmale(l_quater).sick_more_16_ocrs := nvl(absmale(l_quater).sick_more_16_ocrs,0) + p_occurence;
126 absmale(l_quater).sick_more_16_days := nvl(absmale(l_quater).sick_more_16_days,0) + p_absence_days;
127 If p_absence_days > 40 then
128 absmale(l_quater).sick_8_weeks_ocr := nvl(absmale(l_quater).sick_8_weeks_ocr,0) + p_occurence;
129 absmale(l_quater).sick_8_weeks_days := nvl(absmale(l_quater).sick_8_weeks_days,0) + p_absence_days;
130 End if;
131 end if;
132
133 elsif p_sex = 'F' then
134 absfemale(l_quater).initialized:='Y';
135 absfemale(l_quater).quater:=p_quater;
136
137 if p_absence_days <= 3 then
138 if p_certificate_type = 'SC' then
139 absfemale(l_quater).sick_1_3_ocr_sc := nvl(absfemale(l_quater).sick_1_3_ocr_sc,0) + p_occurence;
140 absfemale(l_quater).sick_1_3_days_sc := nvl(absfemale(l_quater).sick_1_3_days_sc,0) + p_absence_days;
141 elsif p_certificate_type = 'DC' then
142 absfemale(l_quater).sick_1_3_ocr_dc := nvl(absfemale(l_quater).sick_1_3_ocr_dc,0) + p_occurence;
143 absfemale(l_quater).sick_1_3_days_dc := nvl(absfemale(l_quater).sick_1_3_days_dc,0) + p_absence_days;
144 end if;
145 elsif p_absence_days > 3 and p_absence_days <= 16 then
146 absfemale(l_quater).sick_4_16_ocrs := nvl(absfemale(l_quater).sick_4_16_ocrs,0) + p_occurence;
147 absfemale(l_quater).sick_4_16_days := nvl(absfemale(l_quater).sick_4_16_days,0) + p_absence_days;
148 elsif p_absence_days > 16 then
149 absfemale(l_quater).sick_more_16_ocrs := nvl(absfemale(l_quater).sick_more_16_ocrs,0) + p_occurence;
150 absfemale(l_quater).sick_more_16_days := nvl(absfemale(l_quater).sick_more_16_days,0) + p_absence_days;
151
152 If p_absence_days > 40 then
153 absfemale(l_quater).sick_8_weeks_ocr := nvl(absfemale(l_quater).sick_8_weeks_ocr,0) + p_occurence;
154 absfemale(l_quater).sick_8_weeks_days := nvl(absfemale(l_quater).sick_8_weeks_days,0) + p_absence_days;
155 End if;
156
157 end if;
158 End if;
159 ELSIF p_absence_category = 'CMS' then
160
161 if p_sex = 'M' then
162 absmale(l_quater).initialized:='Y';
163 absmale(l_quater).quater:=p_quater;
164
165 absmale(l_quater).cms_abs_ocrs := nvl(absmale(l_quater).cms_abs_ocrs,0) + p_occurence;
166 absmale(l_quater).cms_abs_days := nvl(absmale(l_quater).cms_abs_days,0) + p_absence_days;
167 elsif p_sex = 'F' then
168 absfemale(l_quater).initialized:='Y';
169 absfemale(l_quater).quater:=p_quater;
170
171 absfemale(l_quater).cms_abs_ocrs := nvl(absfemale(l_quater).cms_abs_ocrs,0) + p_occurence;
172 absfemale(l_quater).cms_abs_days := nvl(absfemale(l_quater).cms_abs_days,0) + p_absence_days;
173 end if;
174
175 ELSIF p_absence_category in ('PA','M','IE_AL','PTM','PTP','PTA') then
176
177 if p_sex = 'M' then
178 absmale(l_quater).initialized:='Y';
179 absmale(l_quater).quater:=p_quater;
180
181 absmale(l_quater).parental_abs_ocrs := nvl(absmale(l_quater).parental_abs_ocrs,0) + p_occurence;
182 absmale(l_quater).parental_abs_days := nvl(absmale(l_quater).parental_abs_days,0) + p_absence_days;
183 elsif p_sex = 'F' then
184 absfemale(l_quater).initialized:='Y';
185 absfemale(l_quater).quater:=p_quater;
186
187 absfemale(l_quater).parental_abs_ocrs := nvl(absfemale(l_quater).parental_abs_ocrs,0) + p_occurence;
188 absfemale(l_quater).parental_abs_days := nvl(absfemale(l_quater).parental_abs_days,0) + p_absence_days;
189 end if;
190 ELSIF p_absence_category in ('UN','ZZB','UL') then --Need to add Unpaid Leave
191 if p_sex = 'M' then
192 absmale(l_quater).initialized:='Y';
193 absmale(l_quater).quater:=p_quater;
194
195 absmale(l_quater).other_abs_ocrs := nvl(absmale(l_quater).other_abs_ocrs,0) + p_occurence;
196 absmale(l_quater).other_abs_days := nvl(absmale(l_quater).other_abs_days,0) + p_absence_days;
197 elsif p_sex = 'F' then
198 absfemale(l_quater).initialized:='Y';
199 absfemale(l_quater).quater:=p_quater;
200
201 absfemale(l_quater).other_abs_ocrs := nvl(absfemale(l_quater).other_abs_ocrs,0) + p_occurence;
202 absfemale(l_quater).other_abs_days := nvl(absfemale(l_quater).other_abs_days,0) + p_absence_days;
203 end if;
204 ELSIF p_absence_category <> 'VAC' then
205 if p_sex = 'M' then
206 absmale(l_quater).initialized:='Y';
207 absmale(l_quater).quater:=p_quater;
208
209 absmale(l_quater).other_abs_paid_ocrs := nvl(absmale(l_quater).other_abs_paid_ocrs,0) + p_occurence;
210 absmale(l_quater).other_abs_paid_days := nvl(absmale(l_quater).other_abs_paid_days,0) + p_absence_days;
211 elsif p_sex = 'F' then
212 absfemale(l_quater).initialized:='Y';
213 absfemale(l_quater).quater:=p_quater;
214
215 absfemale(l_quater).other_abs_paid_ocrs := nvl(absfemale(l_quater).other_abs_paid_ocrs,0) + p_occurence;
216 absfemale(l_quater).other_abs_paid_days := nvl(absfemale(l_quater).other_abs_paid_days,0) + p_absence_days;
217 end if;
221
218
219 END IF;
220 End sum_no_absence;
222 /*End of sum_no_absence*/
223
224 /* RANGE CODE */
225 PROCEDURE RANGE_CODE (p_payroll_action_id IN NUMBER
226 ,p_sql OUT NOCOPY VARCHAR2)
227 IS
228 Begin
229 p_sql := 'SELECT DISTINCT person_id
230 FROM per_people_f ppf
231 ,pay_payroll_actions ppa
232 WHERE ppa.payroll_action_id = :payroll_action_id
233 AND ppa.business_group_id = ppf.business_group_id
234 ORDER BY ppf.person_id';
235
236 IF g_debug THEN
237 hr_utility.set_location(' Leaving Procedure RANGE_CODE',50);
238 END IF;
239 EXCEPTION
240 WHEN OTHERS THEN
241 -- Return cursor that selects no rows
242 p_sql := 'select 1 from dual where to_char(:payroll_action_id) = dummy';
243 END RANGE_CODE;
244 /* ASSIGNMENT ACTION CODE */
245 PROCEDURE ASSIGNMENT_ACTION_CODE
246 (p_payroll_action_id IN NUMBER
247 ,p_start_person IN NUMBER
248 ,p_end_person IN NUMBER
249 ,p_chunk IN NUMBER)
250 IS
251 BEGIN
252 -- fnd_file.put_line (fnd_file.LOG, 'Entering Assignment Action Code' );
253 IF g_debug THEN
254 hr_utility.set_location(' Entering Procedure ASSIGNMENT_ACTION_CODE',60);
255 END IF;
256 IF g_debug THEN
257 hr_utility.set_location(' Leaving Procedure ASSIGNMENT_ACTION_CODE',70);
258 END IF;
259 -- fnd_file.put_line (fnd_file.LOG, 'Exiting Assignment Action Code' );
260 END ASSIGNMENT_ACTION_CODE;
261 /* INITIALIZATION CODE */
262 PROCEDURE INITIALIZATION_CODE(p_payroll_action_id IN NUMBER)
263 IS
264 BEGIN
265 fnd_file.put_line (fnd_file.LOG, 'Entering Initialization Code' );
266 IF g_debug THEN
267 hr_utility.set_location(' Entering Procedure INITIALIZATION_CODE',80);
268 END IF;
269 IF g_debug THEN
270 hr_utility.set_location(' Leaving Procedure INITIALIZATION_CODE',90);
271 END IF;
272 fnd_file.put_line (fnd_file.LOG, 'Existing Initialization Code' );
273 EXCEPTION WHEN OTHERS THEN
274 g_err_num := SQLCODE;
275 IF g_debug THEN
276 hr_utility.set_location('ORA_ERR: ' || g_err_num ||
277 'In INITIALIZATION_CODE',180);
278 END IF;
279 fnd_file.put_line (fnd_file.LOG, 'Error in Initialization Code' );
280 END INITIALIZATION_CODE;
281
282 PROCEDURE DEINITIALIZE_CODE(p_payroll_action_id IN NUMBER)
283 IS
284
285 l_business_group_id NUMBER;
286 l_legal_employer_id NUMBER;
287 l_start_date date;
288 l_end_date date;
289 l_effective_date date;
290 l_archive varchar2(20);
291 l_count number;
292 l_action_info_id NUMBER;
293 l_ovn NUMBER;
294 l_quater_cnt NUMBER;
295 l_qtr_start_date date;
296 l_qtr_end_date date;
297 l_quater varchar2(4);
298 l_absence_days number;
299 l_possible_working_days number:=0;
300 l_quater_start date;
301 l_quater_end date;
302 l_full_time_percentage number;
303 l_hr_work_schedule number;
304 l_pos_assignment_id per_all_assignments_f.assignment_id%type;
305 l_total_possible_working_days number:=0;
306
307 /* Cursors */
308
309
310 Cursor csr_person_abs_details(csr_v_business_group_id number,
311 csr_v_legal_employer_id hr_organization_information.organization_id%TYPE,
312 csr_v_start_date date,
313 csr_v_end_date date,
314 csr_v_effective_date date
315 )is select papf.person_id,
316 papf.full_name,
317 papf.sex sex,
318 'Q'||to_char(paa.date_start,'Q') quaterstart,
319 'Q'||to_char(paa.date_end,'Q') quaterend,
320 paa.date_start,
321 paa.date_end,
322 paaf.assignment_id,
323 decode(paat.absence_category,'CMS',paa.abs_information1,'S',paa.abs_information1,'PTS',paa.abs_information1,null) certificate_type,
324 paat.absence_category,
325 paa.abs_information3,
326 paa.abs_information4
327 from per_absence_attendances paa,
328 per_all_people_f papf,
329 per_all_assignments_f paaf,
330 per_absence_attendance_types paat,
331 hr_soft_coding_keyflex hsc,
332 hr_organization_information hoi
333 where paa.person_id = papf.person_id
334 and paa.absence_attendance_type_id = paat.absence_attendance_type_id
335 and paaf.person_id = papf.person_id
336 and papf.business_group_id = csr_v_business_group_id
337 and hsc.soft_coding_keyflex_id = paaf.soft_coding_keyflex_id
338 and hoi.org_information1 = hsc.segment2
339 and hoi.organization_id = csr_v_legal_employer_id
340 and hoi.ORG_INFORMATION_CONTEXT = 'NO_LOCAL_UNITS'
341 and paaf.primary_flag = 'Y'
342 and papf.per_information15 = 'N'
343 and paat.absence_category <> 'VAC'
344 and ((paa.date_start between csr_v_start_date and csr_v_end_date)
345 or (paa.date_end between csr_v_start_date and csr_v_end_date))
346 and csr_v_effective_date between papf.effective_start_date and papf.effective_end_date
347 and csr_v_effective_date between paaf.effective_start_date and paaf.effective_end_date
348 and not exists (select '1'
349 from
350 pay_element_entries_f peef,
351 pay_element_types_f petf,
352 per_all_assignments_f paaf1
353 where peef.element_type_id = petf.element_type_id
354 and paaf1.person_id = papf.person_id
355 and peef.assignment_id = paaf1.assignment_id
356 and petf.element_name = 'Sickness Unpaid'
357 and peef.effective_start_date between csr_v_start_date and csr_v_end_date);
358
359 cursor csr_possible_wrk_days
360 (csr_v_start_date date
364 ,csr_v_effective_date date
361 ,csr_v_end_date date
362 ,csr_v_business_group_id number
363 ,csr_v_legal_employer_id number
365 ,csr_v_sex varchar2) is
366 select paaf.assignment_id
367 from per_all_people_f papf,
368 per_all_assignments_f paaf,
369 hr_soft_coding_keyflex hsc,
370 hr_organization_information hoi
371 where paaf.person_id = papf.person_id
372 and papf.business_group_id = csr_v_business_group_id
373 and hsc.soft_coding_keyflex_id = paaf.soft_coding_keyflex_id
374 and hoi.org_information1 = hsc.segment2
375 and hoi.organization_id = csr_v_legal_employer_id
376 and hoi.ORG_INFORMATION_CONTEXT = 'NO_LOCAL_UNITS'
377 and paaf.primary_flag = 'Y'
378 and papf.per_information15 = 'N'
379 and csr_v_effective_date between papf.effective_start_date and papf.effective_end_date
380 and csr_v_effective_date between paaf.effective_start_date and paaf.effective_end_date
381 and papf.sex = csr_v_sex
382 and not exists (select '1'
383 from
384 pay_element_entries_f peef,
385 pay_element_types_f petf,
386 per_all_assignments_f paaf1
387 where peef.element_type_id = petf.element_type_id
388 and paaf1.person_id = papf.person_id
389 and peef.assignment_id = paaf1.assignment_id
390 and petf.element_name = 'Sickness Unpaid'
391 and peef.effective_start_date between csr_v_start_date and csr_v_end_date);
392
393 cursor csr_full_time(csr_business_grp_id NUMBER,csr_assignment_id NUMBER,csr_v_effective_date date)
394 is select value
395 from PER_ASSIGNMENT_BUDGET_VALUES_F
396 where business_group_id = csr_business_grp_id
397 and assignment_id = csr_assignment_id and unit = 'PFT'
398 and csr_v_effective_date between effective_start_date and effective_end_date;
399
400 /* End of Cursors */
401
402 BEGIN
403
404
405 hr_utility.set_location(' Entering Procedure RANGE_CODE',10);
406
407
408
409
410 PAY_NO_ARCHIVE_ABSENCE.GET_ALL_PARAMETERS(
411 p_payroll_action_id
412 ,l_business_group_id
413 ,l_legal_employer_id
414 ,l_start_date
415 ,l_end_date
416 ,l_archive
417 ,l_effective_date
418 );
419
420
421 IF l_archive = 'Y' THEN
422
423 /** Insert report parameters **/
424
425 pay_action_information_api.create_action_information (
426 p_action_information_id => l_action_info_id,
427 p_action_context_id => p_payroll_action_id,
428 p_action_context_type => 'PA',
429 p_object_version_number => l_ovn,
430 p_effective_date => l_effective_date,
431 p_source_id => NULL,
432 p_source_text => NULL,
433 p_action_information_category=> 'EMEA REPORT DETAILS',
434 p_action_information1 => 'PYNOABSA',
435 p_action_information2 => l_legal_employer_id,
436 p_action_information3 => fnd_date.date_to_canonical(l_start_date),
437 p_action_information4 => fnd_date.date_to_canonical(l_end_date),
438 p_action_information5 => fnd_date.date_to_canonical(l_effective_date)
439 );
440
441 SELECT count(*) INTO l_count
442 FROM pay_action_information
443 WHERE action_information_category = 'EMEA REPORT INFORMATION'
444 AND action_information1= 'PYNOABSA'
445 AND action_context_id= p_payroll_action_id;
446
447 hr_utility.set_location('****************Inside Archive ',10);
448
449 IF l_count < 1 then
450
451 absmale(0).initialized:='N';
452 absmale(1).initialized:='N';
453 absmale(2).initialized:='N';
454 absmale(3).initialized:='N';
455
456 absfemale(0).initialized:='N';
457 absfemale(1).initialized:='N';
458 absfemale(2).initialized:='N';
459 absfemale(3).initialized:='N';
460
461 FOR csr_abs in csr_person_abs_details(l_business_group_id,l_legal_employer_id,l_start_date,l_end_date,l_effective_date)
462 Loop
463
464 l_full_time_percentage:=null;
465
466 open csr_full_time(l_business_group_id,csr_abs.assignment_id,l_effective_date);
467 fetch csr_full_time into l_full_time_percentage;
468 close csr_full_time;
469
470 IF l_full_time_percentage is null then
471
472 l_full_time_percentage:=100;
473
474 End if;
475
476 If csr_abs.quaterstart = csr_abs.quaterend then
477
478
479
480 l_hr_work_schedule:=hr_loc_work_schedule.calc_sch_based_dur(csr_abs.assignment_id,'D','Y',csr_abs.date_start,csr_abs.date_end,'00','23',l_absence_days);
481
482 l_absence_days := l_absence_days * l_full_time_percentage/100;
483
484 if csr_abs.absence_category in ('PTS') then
485 l_absence_days := l_absence_days * nvl(csr_abs.abs_information4,100)/100;
486 elsif csr_abs.absence_category in ('PTM','PTP','PTA') then
487
488 l_absence_days := l_absence_days * nvl(csr_abs.abs_information3,100)/100;
489 end if;
490
491 l_quater:= 'Q'||to_number(to_char(csr_abs.date_start,'Q'));
492
493 sum_no_absence(csr_abs.sex,l_absence_days,l_quater,csr_abs.certificate_type,1,csr_abs.absence_category);
494
495
496 else
497 hr_utility.set_location('*****Inside Else Archive******',20);
498 /* Modified for bug 5388892*/
499 If l_start_date > csr_abs.date_start then
500 l_qtr_start_date := l_start_date;
501 Else
502 l_qtr_start_date :=csr_abs.date_start;
503 End if;
507 l_quater_cnt := to_number(to_char(l_end_date,'Q')) - to_number(to_char(l_qtr_start_date,'Q'));
504
505 /* Added for bug 5388892*/
506 If l_end_date < csr_abs.date_end or csr_abs.date_end is null then
508 Else
509 l_quater_cnt := to_number(to_char(csr_abs.date_end,'Q')) - to_number(to_char(l_qtr_start_date,'Q'));
510 End if;
511
512
513
514 l_qtr_end_date :=trunc(csr_abs.date_end,'Q')-1;
515 --l_linked_quater := 'Q'||to_char(l_qtr_start_date,'Q');
516
517 hr_utility.set_location('*****Inside Else Archive**'||l_quater_cnt,20);
518
519 For qtrs in 0..l_quater_cnt
520 Loop
521
522 if qtrs = l_quater_cnt then
523 /* Modified for bug 5388892*/
524 If l_end_date < csr_abs.date_end then
525 l_qtr_end_date := l_end_date;
526 Else
527 l_qtr_end_date := nvl(csr_abs.date_end,l_end_date);
528 End if;
529 elsif qtrs <> 0 then
530 l_qtr_end_date :=trunc(add_months(l_qtr_start_date,3),'Q')-1;
531 end if;
532 l_hr_work_schedule:=hr_loc_work_schedule.calc_sch_based_dur(csr_abs.assignment_id,'D','Y',l_qtr_start_date,l_qtr_end_date,'00','23',l_absence_days);
533 hr_utility.set_location(' ****1111Absence Days : '||l_absence_days||' : '||l_full_time_percentage,5);
534 l_absence_days := l_absence_days * l_full_time_percentage/100;
535
536 /*Added for Bugs 5409100,5409124*/
537 if csr_abs.absence_category in ('PTS') then
538 l_absence_days := l_absence_days * nvl(csr_abs.abs_information4,100)/100;
539 elsif csr_abs.absence_category in ('PTM','PTP','PTA') then
540 l_absence_days := l_absence_days * nvl(csr_abs.abs_information3,100)/100;
541 end if;
542
543 if qtrs = 0 then
544 /* Added for Bug 5381390*/
545 l_quater:= 'Q'||to_number(to_char(l_qtr_start_date,'Q'));
546 If l_start_date > csr_abs.date_start then
547 sum_no_absence(csr_abs.sex,l_absence_days,l_quater,csr_abs.certificate_type,0,csr_abs.absence_category);
548 Else
549 sum_no_absence(csr_abs.sex,l_absence_days,l_quater,csr_abs.certificate_type,1,csr_abs.absence_category);
550 End if;
551
552 else
553 l_quater:= 'Q'||to_number(to_char(l_qtr_start_date,'Q'));
554 sum_no_absence(csr_abs.sex,l_absence_days,l_quater,csr_abs.certificate_type,0,csr_abs.absence_category);
555 end if;
556
557 l_qtr_start_date :=l_qtr_end_date+1;
558 End loop;
559
560 End if;
561 End Loop;
562
563 END IF;
564
565 hr_utility.set_location('***Table Populated***',50);
566
567 l_quater_start := trunc(l_start_date,'Q');
568 l_quater_end := trunc(add_months(l_quater_start,3),'Q')-1;
569
570 For itr in absmale.FIRST .. absmale.LAST
571 Loop
572
573 FOR csr_possible_wrk_days1 in csr_possible_wrk_days(l_quater_start,l_quater_end,l_business_group_id,l_legal_employer_id,l_effective_date,'M')
574 Loop
575
576 l_full_time_percentage:=null;
577
578 open csr_full_time(l_business_group_id,csr_possible_wrk_days1.assignment_id,l_effective_date);
579 fetch csr_full_time into l_full_time_percentage;
580 close csr_full_time;
581
582 IF l_full_time_percentage is null then
583
584 l_full_time_percentage:=100;
585
586 End if;
587
588 l_pos_assignment_id := csr_possible_wrk_days1.assignment_id;
589 l_hr_work_schedule:=hr_loc_work_schedule.calc_sch_based_dur(l_pos_assignment_id,'D','Y',l_quater_start,l_quater_end,'00','23',l_possible_working_days);
590 l_total_possible_working_days := nvl(l_total_possible_working_days,0) + (nvl(l_possible_working_days,0)*l_full_time_percentage/100);
591
592 End loop;
593 hr_utility.trace('Entered l_quater_end ******* '||l_quater_end);
594
595 If absmale(itr).initialized = 'Y' then
596 pay_action_information_api.create_action_information (
597 p_action_information_id => l_action_info_id
598 ,p_action_context_id => p_payroll_action_id
599 ,p_action_context_type => 'PA'
600 ,p_object_version_number => l_ovn
601 ,p_effective_date => l_effective_date
602 ,p_source_id => NULL
603 ,p_source_text => NULL
604 ,p_action_information_category => 'EMEA REPORT INFORMATION'
605 ,p_action_information1 => 'PYNOABSA'
606 ,p_action_information2 => l_legal_employer_id
607 ,p_action_information3 => absmale(itr).quater
608 ,p_action_information4 => l_total_possible_working_days
609 ,p_action_information5 => 'M'
610 ,p_action_information6 => absmale(itr).sick_1_3_ocr_sc
611 ,p_action_information7 => absmale(itr).sick_1_3_days_sc
612 ,p_action_information8 => absmale(itr).sick_1_3_ocr_dc
613 ,p_action_information9 => absmale(itr).sick_1_3_days_dc
614 ,p_action_information10 => absmale(itr).sick_4_16_ocrs
615 ,p_action_information11 => absmale(itr).sick_4_16_days
616 ,p_action_information12 => absmale(itr).sick_more_16_ocrs
617 ,p_action_information13 => absmale(itr).sick_more_16_days
618 ,p_action_information14 => absmale(itr).cms_abs_ocrs
619 ,p_action_information15 => absmale(itr).cms_abs_days
620 ,p_action_information16 => absmale(itr).parental_abs_ocrs
621 ,p_action_information17 => absmale(itr).parental_abs_days
622 ,p_action_information18 => absmale(itr).other_abs_ocrs
623 ,p_action_information19 => absmale(itr).other_abs_days
624 ,p_action_information20 => absmale(itr).sick_8_weeks_ocr
625 ,p_action_information21 => absmale(itr).sick_8_weeks_days
626 ,p_action_information22 => absmale(itr).other_abs_paid_ocrs
627 ,p_action_information23 => absmale(itr).other_abs_paid_days
628 ,p_action_information24 => l_business_group_id);
629
630 End if;
631
632 l_quater_start := l_quater_end + 1;
633 l_quater_end := trunc(add_months(l_quater_start,3),'Q')-1;
634 /* Added for bug 5381141*/
635 l_total_possible_working_days := 0;
636 End loop;
637
638 hr_utility.set_location('***Done with Male*** '||absmale.LAST,50);
639
640 l_quater_start := trunc(l_start_date,'Q');
641 l_quater_end := trunc(add_months(l_quater_start,3),'Q')-1;
642 l_total_possible_working_days:=0;
643
644 For itrf in absfemale.FIRST .. absfemale.LAST
645 Loop
646 For csr_possible_wrk_days1 in csr_possible_wrk_days(l_quater_start,l_quater_end,l_business_group_id,l_legal_employer_id,l_effective_date,'F')
647 Loop
648
649 l_full_time_percentage:=null;
650
651 open csr_full_time(l_business_group_id,csr_possible_wrk_days1.assignment_id,l_effective_date);
652 fetch csr_full_time into l_full_time_percentage;
653 close csr_full_time;
654
655 IF l_full_time_percentage is null then
656
657 l_full_time_percentage:=100;
658
659 End if;
660 l_pos_assignment_id := csr_possible_wrk_days1.assignment_id;
661 l_hr_work_schedule:=hr_loc_work_schedule.calc_sch_based_dur(l_pos_assignment_id,'D','Y',l_quater_start,l_quater_end,'00','23',l_possible_working_days);
662 l_total_possible_working_days := nvl(l_total_possible_working_days,0) + (nvl(l_possible_working_days,0)*l_full_time_percentage/100);
663
664 End loop;
665
666 If absfemale(itrf).initialized = 'Y' then
667 pay_action_information_api.create_action_information (
668 p_action_information_id => l_action_info_id
669 ,p_action_context_id => p_payroll_action_id
670 ,p_action_context_type => 'PA'
671 ,p_object_version_number => l_ovn
672 ,p_effective_date => l_effective_date
673 ,p_source_id => NULL
674 ,p_source_text => NULL
675 ,p_action_information_category => 'EMEA REPORT INFORMATION'
676 ,p_action_information1 => 'PYNOABSA'
677 ,p_action_information2 => l_legal_employer_id
678 ,p_action_information3 => absfemale(itrf).quater
679 ,p_action_information4 => l_total_possible_working_days
680 ,p_action_information5 => 'F'
681 ,p_action_information6 => absfemale(itrf).sick_1_3_ocr_sc
682 ,p_action_information7 => absfemale(itrf).sick_1_3_days_sc
683 ,p_action_information8 => absfemale(itrf).sick_1_3_ocr_dc
684 ,p_action_information9 => absfemale(itrf).sick_1_3_days_dc
685 ,p_action_information10 => absfemale(itrf).sick_4_16_ocrs
686 ,p_action_information11 => absfemale(itrf).sick_4_16_days
687 ,p_action_information12 => absfemale(itrf).sick_more_16_ocrs
688 ,p_action_information13 => absfemale(itrf).sick_more_16_days
689 ,p_action_information14 => absfemale(itrf).cms_abs_ocrs
690 ,p_action_information15 => absfemale(itrf).cms_abs_days
691 ,p_action_information16 => absfemale(itrf).parental_abs_ocrs
692 ,p_action_information17 => absfemale(itrf).parental_abs_days
693 ,p_action_information18 => absfemale(itrf).other_abs_ocrs
694 ,p_action_information19 => absfemale(itrf).other_abs_days
695 ,p_action_information20 => absfemale(itrf).sick_8_weeks_ocr
696 ,p_action_information21 => absfemale(itrf).sick_8_weeks_days
697 ,p_action_information22 => absfemale(itrf).other_abs_paid_ocrs
698 ,p_action_information23 => absfemale(itrf).other_abs_paid_days
699 ,p_action_information24 => l_business_group_id);
700
701 End if;
702
703 l_quater_start := l_quater_end + 1;
704 l_quater_end := trunc(add_months(l_quater_start,3),'Q')-1;
705 /* Added for bug 5381141*/
706 l_total_possible_working_days := 0;
707 End loop;
708
709 hr_utility.set_location('***Done with Female*** '||absfemale.LAST,50);
710
711 END IF;
712 fnd_file.put_line (fnd_file.LOG, 'Existing Initialization Code' );
713 EXCEPTION WHEN OTHERS THEN
714 g_err_num := SQLCODE;
715 IF g_debug THEN
716 hr_utility.set_location('ORA_ERR: ' || g_err_num ||
717 'In INITIALIZATION_CODE',180);
718 END IF;
719 fnd_file.put_line (fnd_file.LOG, 'Error in Initialization Code' );
720 END DEINITIALIZE_CODE;
721
722 /* ARCHIVE CODE */
723 PROCEDURE ARCHIVE_CODE(p_assignment_action_id IN NUMBER
724 ,p_effective_date IN DATE)
725 IS
726 BEGIN
727 fnd_file.put_line (fnd_file.LOG, 'entering archive code' );
728 IF g_debug THEN
729 hr_utility.set_location(' Entering Procedure ARCHIVE_CODE',80);
730 END IF;
731 IF g_debug THEN
732 hr_utility.set_location(' Leaving Procedure ARCHIVE_CODE',90);
733 END IF;
734 fnd_file.put_line (fnd_file.LOG, 'Exiting archive code' );
735 END ARCHIVE_CODE;
736 END PAY_NO_ARCHIVE_ABSENCE;