[Home] [Help]
PACKAGE BODY: APPS.PAY_SE_HOLIDAY_PAY
Source
1 PACKAGE BODY pay_se_holiday_pay AS
2 /*$Header: pyseholi.pkb 120.2 2010/05/28 12:34:01 krreddy ship $*/
3 FUNCTION get_earning_year_workingdays (
4 p_assignment_id IN NUMBER
5 ,p_effective_date IN DATE
6 ,p_assignment_action_id IN NUMBER
7 )
8 RETURN NUMBER
9 IS
10 l_earning_start_date DATE;
11 l_earning_end_date DATE;
12 l_start_month NUMBER;
13 l_end_month NUMBER;
14 l_person_id NUMBER;
15 l_business_group_id NUMBER;
16 l_assignment_entitlement NUMBER;
17 l_person_entitlement NUMBER;
18 l_legal_entitlement NUMBER;
19 l_gen_entitlement NUMBER;
20 lr_get_defined_balance_id NUMBER;
21 l_value NUMBER;
22 l_generate NUMBER;
23 l_max_days NUMBER;
24 --l_absence_days number;
25 l_days_year NUMBER;
26 l_absence_days NUMBER;
27 l_paid_holiday_days NUMBER;
28 l_unpaid_holiday_days NUMBER;
29 l_saved_days NUMBER;
30 l_assignment_start DATE;
31
32 /*Cursor csr_Earning_Year is
33 SELECT substr(hoi4.ORG_INFORMATION1,4,2),substr(hoi4.ORG_INFORMATION2,4,2)
34 FROM HR_ORGANIZATION_UNITS o1
35 ,HR_ORGANIZATION_INFORMATION hoi1
36 ,HR_ORGANIZATION_INFORMATION hoi2
37 ,HR_ORGANIZATION_INFORMATION hoi3
38 ,HR_ORGANIZATION_INFORMATION hoi4
39 ,( SELECT TRIM(SCL.SEGMENT2) AS ORG_ID
40 FROM PER_ALL_ASSIGNMENTS_F ASG
41 ,HR_SOFT_CODING_KEYFLEX SCL
42 WHERE ASG.ASSIGNMENT_ID = p_assignment_id
43 AND ASG.SOFT_CODING_KEYFLEX_ID = SCL.SOFT_CODING_KEYFLEX_ID
44 AND p_effective_date BETWEEN ASG.EFFECTIVE_START_DATE AND ASG.EFFECTIVE_END_DATE ) X
45 WHERE o1.business_group_id = l_business_group_id
46 AND hoi1.organization_id = o1.organization_id
47 AND hoi1.organization_id = X.ORG_ID
48 AND hoi1.org_information1 = 'SE_LOCAL_UNIT'
49 AND hoi1.org_information_context = 'CLASS'
50 AND o1.organization_id = hoi2.org_information1
51 AND hoi2.ORG_INFORMATION_CONTEXT='SE_LOCAL_UNITS'
52 AND hoi2.organization_id = hoi3.organization_id
53 AND hoi3.ORG_INFORMATION_CONTEXT='CLASS'
54 AND hoi3.org_information1 = 'HR_LEGAL_EMPLOYER'
55 AND hoi3.organization_id = hoi4.organization_id
56 AND hoi4.ORG_INFORMATION_CONTEXT='SE_HOLIDAY_YEAR_DEFN' 'SE_LE_HOLIDAY_PAY_DETAILS'
60 SELECT aei_information1
57 AND hoi4.org_information1 IS NOT NULL;*/
58 CURSOR csr_assignment_entitlement
59 IS
61 FROM per_assignment_extra_info
62 WHERE assignment_id = p_assignment_id
63 AND information_type = 'SE_ASSIGN_HOLIDAY_PAY_DETAILS';
64
65 CURSOR csr_person_entitlement
66 IS
67 SELECT pei_information1
68 FROM per_people_extra_info
69 WHERE person_id = l_person_id
70 AND information_type = 'SE_PERSON_HOLIDAY_PAY_DETAILS';
71
72 CURSOR csr_legal_employer_entitlement
73 IS
74 SELECT hoi4.org_information1
75 FROM hr_organization_units o1
76 ,hr_organization_information hoi1
77 ,hr_organization_information hoi2
78 ,hr_organization_information hoi3
79 ,hr_organization_information hoi4
80 , (SELECT TRIM (scl.segment2) AS org_id
81 FROM per_all_assignments_f asg
82 ,hr_soft_coding_keyflex scl
83 WHERE asg.assignment_id = p_assignment_id
84 AND asg.soft_coding_keyflex_id =
85 scl.soft_coding_keyflex_id
86 AND p_effective_date BETWEEN asg.effective_start_date
87 AND asg.effective_end_date) x
88 WHERE o1.business_group_id = l_business_group_id
89 AND hoi1.organization_id = o1.organization_id
90 AND hoi1.organization_id = x.org_id
91 AND hoi1.org_information1 = 'SE_LOCAL_UNIT'
92 AND hoi1.org_information_context = 'CLASS'
93 AND o1.organization_id = hoi2.org_information1
94 AND hoi2.org_information_context = 'SE_LOCAL_UNITS'
95 AND hoi2.organization_id = hoi3.organization_id
96 AND hoi3.org_information_context = 'CLASS'
97 AND hoi3.org_information1 = 'HR_LEGAL_EMPLOYER'
98 AND hoi3.organization_id = hoi4.organization_id
99 AND hoi4.org_information_context = 'SE_LE_HOLIDAY_PAY_DETAILS'
100 AND hoi4.org_information1 IS NOT NULL;
101
102 CURSOR csr_attendance_type_id
103 IS
104 SELECT DISTINCT eev1.screen_entry_value attendance_type_id
105 FROM per_all_assignments_f asg1
106 --,per_all_assignments_f asg2
107 -- ,per_all_people_f per
108 , pay_element_links_f el
109 ,pay_element_types_f et
110 ,pay_input_values_f iv1
111 ,pay_element_entries_f ee
112 ,pay_element_entry_values_f eev1
113 WHERE asg1.assignment_id = p_assignment_id
114 AND p_effective_date BETWEEN asg1.effective_start_date
115 AND asg1.effective_end_date
116 --AND p_effective_date BETWEEN asg2.effective_start_date AND asg2.effective_end_date
117 -- AND per.person_id = asg1.person_id
118 -- AND asg2.person_id = per.person_id
119 -- AND asg2.primary_flag = 'Y'
120 AND et.element_name = 'Absence Details'
121 AND et.legislation_code = 'SE'
122 --OR et.business_group_id=3261 ) --checking for the business group, it should be removed
123 AND iv1.element_type_id = et.element_type_id
124 AND iv1.NAME = 'Absence Category' --l_inp_val_name
125 AND el.business_group_id = asg1.business_group_id
126 AND el.element_type_id = et.element_type_id
127 AND ee.assignment_id = asg1.assignment_id
128 AND ee.element_link_id = el.element_link_id
129 AND eev1.element_entry_id = ee.element_entry_id
130 AND eev1.input_value_id = iv1.input_value_id
131 AND ee.effective_start_date >= l_earning_start_date
132 AND ee.effective_end_date <= l_earning_end_date
133 AND eev1.effective_start_date >= l_earning_start_date
134 AND eev1.effective_end_date <= l_earning_end_date;
135
136 CURSOR csr_get_defined_balance_id (
137 csr_v_balance_name ff_database_items.user_name%TYPE
138 )
139 IS
140 SELECT ue.creator_id
141 FROM ff_user_entities ue
142 ,ff_database_items di
143 WHERE di.user_name = csr_v_balance_name
144 AND ue.user_entity_id = di.user_entity_id
145 AND ue.legislation_code = 'SE'
146 AND ue.business_group_id IS NULL
147 AND ue.creator_type = 'B';
148
149 CURSOR csr_generate_max_days (csr_v_absence_type_id NUMBER)
150 IS
151 SELECT information2 generate
152 ,information3 max_days
153 FROM per_absence_attendance_types
154 WHERE absence_attendance_type_id = csr_v_absence_type_id;
155
156 CURSOR csr_earning_year
157 IS
158 SELECT SUBSTR (hoi4.org_information1, 4, 2)
159 ,SUBSTR (hoi4.org_information2, 4, 2)
160 FROM hr_organization_units o1
161 ,hr_organization_information hoi1
162 ,hr_organization_information hoi2
163 ,hr_organization_information hoi3
164 ,hr_organization_information hoi4
165 , (SELECT TRIM (scl.segment2) AS org_id
166 FROM per_all_assignments_f asg
167 ,hr_soft_coding_keyflex scl
168 WHERE asg.assignment_id = p_assignment_id
169 AND asg.soft_coding_keyflex_id =
173 WHERE o1.business_group_id = l_business_group_id
170 scl.soft_coding_keyflex_id
171 AND p_effective_date BETWEEN asg.effective_start_date
172 AND asg.effective_end_date) x
174 AND hoi1.organization_id = o1.organization_id
175 AND hoi1.organization_id = x.org_id
176 AND hoi1.org_information1 = 'SE_LOCAL_UNIT'
177 AND hoi1.org_information_context = 'CLASS'
178 AND o1.organization_id = hoi2.org_information1
179 AND hoi2.org_information_context = 'SE_LOCAL_UNITS'
180 AND hoi2.organization_id = hoi3.organization_id
181 AND hoi3.org_information_context = 'CLASS'
182 AND hoi3.org_information1 = 'HR_LEGAL_EMPLOYER'
183 AND hoi3.organization_id = hoi4.organization_id
184 AND hoi4.org_information_context = 'SE_HOLIDAY_YEAR_DEFN'
185 AND hoi4.org_information1 IS NOT NULL;
186 /*CURSOR csr_assignment_start IS
187 SELECT min(EFFECTIVE_START_DATE) FROM
188 per_all_assignments_f
189 WHERE assignment_id=p_assignment_id;*/
190 BEGIN
191 SELECT papf.business_group_id
192 ,papf.person_id
193 INTO l_business_group_id
194 ,l_person_id
195 FROM per_all_assignments_f paaf
196 ,per_all_people_f papf
197 ,hr_soft_coding_keyflex hsck
198 WHERE paaf.assignment_id = p_assignment_id
199 AND paaf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
200 AND papf.person_id = paaf.person_id
201 AND p_effective_date BETWEEN paaf.effective_start_date
202 AND paaf.effective_end_date
203 AND p_effective_date BETWEEN papf.effective_start_date
204 AND papf.effective_end_date;
205
206 OPEN csr_earning_year;
207
208 FETCH csr_earning_year
209 INTO l_start_month
210 ,l_end_month;
211
212 CLOSE csr_earning_year;
213
214 IF l_start_month IS NULL AND l_end_month IS NULL
215 THEN
216 RETURN -1;
217 ELSE
218 l_earning_start_date :=
219 TO_DATE ( '01/'
220 || l_start_month
221 || '/'
222 || TO_NUMBER (TO_CHAR (p_effective_date, 'YYYY') - 1)
223 ,'dd/mm/yyyy'
224 );
225 l_earning_end_date :=
226 TO_DATE ( '01/'
227 || l_start_month
228 || '/'
229 || TO_NUMBER (TO_CHAR (p_effective_date, 'YYYY') - 1)
230 ,'dd/mm/yyyy'
231 )
232 + 360;
233 l_earning_end_date := LAST_DAY (l_earning_end_date);
234 END IF;
235
236 /*OPEN csr_assignment_start;
237 FETCH csr_assignment_start INTO l_assignment_start;
238 CLOSE csr_assignment_start;*/
239 /*l_days_year:=(p_earning_end_date-(greatest(p_earning_start_date,l_assignment_start)+1));*/
240 FOR csr_context IN csr_attendance_type_id
241 LOOP
242 pay_balance_pkg.set_context ('SOURCE_NUMBER'
243 ,csr_context.attendance_type_id
244 );
245 pay_balance_pkg.set_context ('ASSIGNMENT_ACTION_ID'
246 ,p_assignment_action_id
247 );
248
249 OPEN csr_get_defined_balance_id
250 ('TOTAL_ABSENCE_DAYS_HOLIDAY_PAY_ABC_PER_YTD');
251
252 FETCH csr_get_defined_balance_id
253 INTO lr_get_defined_balance_id;
254
255 CLOSE csr_get_defined_balance_id;
256
257 l_value :=
258 TO_CHAR
259 (pay_balance_pkg.get_value
260 (p_defined_balance_id => lr_get_defined_balance_id
261 ,p_assignment_action_id => p_assignment_action_id
262 )
263 );
264
265 OPEN csr_generate_max_days (csr_context.attendance_type_id);
266
267 FETCH csr_generate_max_days
268 INTO l_generate
269 ,l_max_days;
270
271 CLOSE csr_generate_max_days;
272
273 /* If generate is Y then value greater than the max is considered as absence, else whole value */
274 IF l_generate = 'Y'
275 THEN
276 IF l_value > l_max_days
277 THEN
278 l_absence_days := l_absence_days + (l_value - l_max_days);
279 ELSE
280 l_absence_days := l_absence_days + l_value;
281 END IF;
282 ELSE
283 l_absence_days := l_absence_days + l_value;
284 END IF;
285 END LOOP;
286
287 RETURN l_absence_days;
288 END get_earning_year_workingdays;
289
290 FUNCTION check_entitlement (
291 p_assignment_id IN NUMBER
292 ,p_effective_date IN DATE
293 ,p_pay_start_date IN DATE
294 ,p_pay_end_date IN DATE
295 ,p_earning_start_date OUT NOCOPY DATE
296 ,p_earning_end_date OUT NOCOPY DATE
297 )
298 RETURN VARCHAR2
299 IS
303 l_earning_start_date DATE;
300 l_business_group_id NUMBER;
301 l_start_month NUMBER;
302 l_end_month NUMBER;
304 l_earning_end_date DATE;
305 l_assignment_start DATE;
306
307 CURSOR csr_earning_year
308 IS
309 SELECT SUBSTR (hoi4.org_information1, 4, 2)
310 ,SUBSTR (hoi4.org_information2, 4, 2)
311 FROM hr_organization_units o1
312 ,hr_organization_information hoi1
313 ,hr_organization_information hoi2
314 ,hr_organization_information hoi3
315 ,hr_organization_information hoi4
316 , (SELECT TRIM (scl.segment2) AS org_id
317 FROM per_all_assignments_f asg
318 ,hr_soft_coding_keyflex scl
319 WHERE asg.assignment_id = p_assignment_id
320 AND asg.soft_coding_keyflex_id =
321 scl.soft_coding_keyflex_id
322 AND p_effective_date BETWEEN asg.effective_start_date
323 AND asg.effective_end_date) x
324 WHERE o1.business_group_id = l_business_group_id
325 AND hoi1.organization_id = o1.organization_id
326 AND hoi1.organization_id = x.org_id
327 AND hoi1.org_information1 = 'SE_LOCAL_UNIT'
328 AND hoi1.org_information_context = 'CLASS'
329 AND o1.organization_id = hoi2.org_information1
330 AND hoi2.org_information_context = 'SE_LOCAL_UNITS'
331 AND hoi2.organization_id = hoi3.organization_id
332 AND hoi3.org_information_context = 'CLASS'
333 AND hoi3.org_information1 = 'HR_LEGAL_EMPLOYER'
334 AND hoi3.organization_id = hoi4.organization_id
335 AND hoi4.org_information_context = 'SE_HOLIDAY_YEAR_DEFN'
336 AND hoi4.org_information1 IS NOT NULL;
337
338 CURSOR csr_assignment_start
339 IS
340 SELECT MIN (effective_start_date)
341 FROM per_all_assignments_f
342 WHERE assignment_id = p_assignment_id;
343 BEGIN
344 SELECT papf.business_group_id
345 INTO l_business_group_id
346 FROM per_all_assignments_f paaf
347 ,per_all_people_f papf
348 ,hr_soft_coding_keyflex hsck
349 WHERE paaf.assignment_id = p_assignment_id
350 AND paaf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
351 AND papf.person_id = paaf.person_id
352 AND p_effective_date BETWEEN paaf.effective_start_date
353 AND paaf.effective_end_date
354 AND p_effective_date BETWEEN papf.effective_start_date
355 AND papf.effective_end_date;
356
357 OPEN csr_earning_year;
358
359 FETCH csr_earning_year
360 INTO l_start_month
361 ,l_end_month;
362
363 CLOSE csr_earning_year;
364
365 IF l_start_month IS NULL AND l_end_month IS NULL
366 THEN
367 RETURN 'N';
368 ELSE
369 l_earning_start_date :=
370 TO_DATE ( '01/'
371 || l_start_month
372 || '/'
373 || TO_NUMBER (TO_CHAR (p_effective_date, 'YYYY') - 1)
374 ,'dd/mm/yyyy'
375 );
376 l_earning_end_date :=
377 TO_DATE ( '01/'
378 || l_start_month
379 || '/'
380 || TO_NUMBER (TO_CHAR (p_effective_date, 'YYYY') - 1)
381 ,'dd/mm/yyyy'
382 )
383 + 360;
384 l_earning_end_date := LAST_DAY (l_earning_end_date);
385 p_earning_start_date := l_earning_start_date;
386 p_earning_end_date := l_earning_end_date;
387
388 --checking the l_earning_end_date+1 lies between the payroll periods for first payroll
389 --period after earning year
390 IF (p_pay_start_date <= (l_earning_end_date + 1))
391 AND ((l_earning_end_date + 1) <= p_pay_end_date)
392 THEN
393 --IF (p_effective_date>=l_earning_start_date) AND (p_effective_date<=l_earning_end_date) THEN
394 /* check whether the person has the assignment in the earning year */
395 OPEN csr_assignment_start;
396
397 FETCH csr_assignment_start
398 INTO l_assignment_start;
399
400 CLOSE csr_assignment_start;
401
402 IF l_assignment_start <= l_earning_end_date
403 THEN
404 RETURN 'F';
405 ELSE
406 RETURN 'N';
407 END IF;
408 --checking the earning_end_date lies between payroll_start and end_date, to find the last payroll
409 --period
410 /*ELSIF (p_pay_start_date>=l_earning_end_date) AND (l_earning_end_date<= p_pay_end_date) THEN
411 RETURN 'L';*/
412 ELSE
413 RETURN 'N';
414 END IF;
415 END IF;
416 EXCEPTION
417 WHEN OTHERS
418 THEN
419 RETURN 'N';
420 END check_entitlement;
421
422 FUNCTION get_paid_unpaid_days (
423 p_assignment_id IN NUMBER
424 ,p_effective_date IN DATE
425 ,p_assignment_action_id IN NUMBER
426 ,p_tax_unit_id IN NUMBER
427 ,p_earning_start_date IN DATE
428 ,p_earning_end_date IN DATE
432 )
429 ,p_paid_holiday_days OUT NOCOPY NUMBER
430 ,p_unpaid_holiday_days OUT NOCOPY NUMBER
431 ,p_total_working_days OUT NOCOPY NUMBER
433 RETURN NUMBER
434 IS
435 l_person_id NUMBER;
436 l_business_group_id NUMBER;
437 l_assignment_entitlement NUMBER;
438 l_person_entitlement NUMBER;
439 l_legal_entitlement NUMBER;
440 l_gen_entitlement NUMBER;
441 lr_get_defined_balance_id NUMBER;
442 l_value NUMBER;
443 l_generate VARCHAR (1);
444 l_max_days NUMBER;
445 --l_absence_days number;
446 l_days_year NUMBER;
447 l_work_days_year NUMBER;
448 l_absence_days NUMBER := 0;
449 l_paid_holiday_days NUMBER;
450 l_unpaid_holiday_days NUMBER;
451 l_saved_days NUMBER;
452 l_assignment_start DATE;
453 l_attendance_category_id VARCHAR2 (30);
454 l_working_perc VARCHAR2 (30); --NUMBER; --Existing bug fixed as part of 9747212
455 l_days NUMBER;
456
457 /*Cursor csr_Earning_Year is
458 SELECT substr(hoi4.ORG_INFORMATION1,4,2),substr(hoi4.ORG_INFORMATION2,4,2)
459 FROM HR_ORGANIZATION_UNITS o1
460 ,HR_ORGANIZATION_INFORMATION hoi1
461 ,HR_ORGANIZATION_INFORMATION hoi2
462 ,HR_ORGANIZATION_INFORMATION hoi3
463 ,HR_ORGANIZATION_INFORMATION hoi4
464 ,( SELECT TRIM(SCL.SEGMENT2) AS ORG_ID
465 FROM PER_ALL_ASSIGNMENTS_F ASG
466 ,HR_SOFT_CODING_KEYFLEX SCL
467 WHERE ASG.ASSIGNMENT_ID = p_assignment_id
468 AND ASG.SOFT_CODING_KEYFLEX_ID = SCL.SOFT_CODING_KEYFLEX_ID
469 AND p_effective_date BETWEEN ASG.EFFECTIVE_START_DATE AND ASG.EFFECTIVE_END_DATE ) X
470 WHERE o1.business_group_id = l_business_group_id
471 AND hoi1.organization_id = o1.organization_id
472 AND hoi1.organization_id = X.ORG_ID
473 AND hoi1.org_information1 = 'SE_LOCAL_UNIT'
474 AND hoi1.org_information_context = 'CLASS'
475 AND o1.organization_id = hoi2.org_information1
476 AND hoi2.ORG_INFORMATION_CONTEXT='SE_LOCAL_UNITS'
477 AND hoi2.organization_id = hoi3.organization_id
478 AND hoi3.ORG_INFORMATION_CONTEXT='CLASS'
479 AND hoi3.org_information1 = 'HR_LEGAL_EMPLOYER'
480 AND hoi3.organization_id = hoi4.organization_id
481 AND hoi4.ORG_INFORMATION_CONTEXT='SE_HOLIDAY_YEAR_DEFN' 'SE_LE_HOLIDAY_PAY_DETAILS'
482 AND hoi4.org_information1 IS NOT NULL;*/
483 CURSOR csr_assignment_entitlement
484 IS
485 SELECT aei_information1
486 FROM per_assignment_extra_info
487 WHERE assignment_id = p_assignment_id
488 AND information_type = 'SE_ASSIGN_HOLIDAY_PAY_DETAILS';
489
490 CURSOR csr_person_entitlement
491 IS
492 SELECT pei_information1
493 FROM per_people_extra_info
494 WHERE person_id = l_person_id
495 AND information_type = 'SE_PERSON_HOLIDAY_PAY_DETAILS';
496
497 CURSOR csr_legal_employer_entitlement
498 IS
499 SELECT hoi4.org_information1
500 FROM hr_organization_units o1
501 ,hr_organization_information hoi1
502 ,hr_organization_information hoi2
503 ,hr_organization_information hoi3
504 ,hr_organization_information hoi4
505 , (SELECT TRIM (scl.segment2) AS org_id
506 FROM per_all_assignments_f asg
507 ,hr_soft_coding_keyflex scl
508 WHERE asg.assignment_id = p_assignment_id
509 AND asg.soft_coding_keyflex_id =
510 scl.soft_coding_keyflex_id
511 AND p_effective_date BETWEEN asg.effective_start_date
512 AND asg.effective_end_date) x
513 WHERE o1.business_group_id = l_business_group_id
514 AND hoi1.organization_id = o1.organization_id
515 AND hoi1.organization_id = x.org_id
516 AND hoi1.org_information1 = 'SE_LOCAL_UNIT'
517 AND hoi1.org_information_context = 'CLASS'
518 AND o1.organization_id = hoi2.org_information1
519 AND hoi2.org_information_context = 'SE_LOCAL_UNITS'
520 AND hoi2.organization_id = hoi3.organization_id
521 AND hoi3.org_information_context = 'CLASS'
522 AND hoi3.org_information1 = 'HR_LEGAL_EMPLOYER'
523 AND hoi3.organization_id = hoi4.organization_id
524 AND hoi4.org_information_context = 'SE_LE_HOLIDAY_PAY_DETAILS'
525 AND hoi4.org_information1 IS NOT NULL;
526
527 CURSOR csr_attendance_category_id
528 IS
529 SELECT DISTINCT eev1.screen_entry_value attendance_category_id
530 FROM per_all_assignments_f asg1
531 --,per_all_assignments_f asg2
532 -- ,per_all_people_f per
533 , pay_element_links_f el
534 ,pay_element_types_f et
535 ,pay_input_values_f iv1
536 ,pay_element_entries_f ee
537 ,pay_element_entry_values_f eev1
538 WHERE asg1.assignment_id = p_assignment_id
539 AND p_effective_date BETWEEN asg1.effective_start_date
540 AND asg1.effective_end_date
541 --AND p_effective_date BETWEEN asg2.effective_start_date AND asg2.effective_end_date
545 --AND et.element_name = 'Absence Details'
542 -- AND per.person_id = asg1.person_id
543 -- AND asg2.person_id = per.person_id
544 -- AND asg2.primary_flag = 'Y'
546 AND et.legislation_code = 'SE'
547 --OR et.business_group_id=3261 ) --checking for the business group, it should be removed
548 AND iv1.element_type_id = et.element_type_id
549 AND iv1.NAME = 'Absence Category' --l_inp_val_name
550 AND el.business_group_id = asg1.business_group_id
551 AND el.element_type_id = et.element_type_id
552 AND ee.assignment_id = asg1.assignment_id
553 AND ee.element_link_id = el.element_link_id
554 AND eev1.element_entry_id = ee.element_entry_id
555 AND eev1.input_value_id = iv1.input_value_id
556 AND ee.effective_start_date <= p_earning_end_date
557 AND ee.effective_end_date >= p_earning_start_date
558 AND eev1.effective_start_date <= p_earning_end_date
559 AND eev1.effective_end_date >= p_earning_start_date
560 AND et.element_name NOT IN
561 ('Advance Holiday Details', 'Advance Holiday Pay');
562
563 CURSOR csr_get_defined_balance_id (
564 csr_v_balance_name ff_database_items.user_name%TYPE
565 )
566 IS
567 SELECT ue.creator_id
568 FROM ff_user_entities ue
569 ,ff_database_items di
570 WHERE di.user_name = csr_v_balance_name
571 AND ue.user_entity_id = di.user_entity_id
572 AND ue.legislation_code = 'SE'
573 AND ue.business_group_id IS NULL
574 AND ue.creator_type = 'B';
575
576 CURSOR csr_generate_max_days
577 IS
578 /*SELECT INFORMATION2 Generate
579 ,INFORMATION3 Max_Days
580 FROM PER_ABSENCE_ATTENDANCE_TYPES
581 WHERE ABSENCE_ATTENDANCE_TYPE_ID=csr_v_absence_type_id;*/
582 SELECT hoi4.org_information2
583 ,hoi4.org_information3
584 FROM hr_organization_units o1
585 ,hr_organization_information hoi1
586 ,hr_organization_information hoi2
587 ,hr_organization_information hoi3
588 ,hr_organization_information hoi4
589 , (SELECT TRIM (scl.segment2) AS org_id
590 FROM per_all_assignments_f asg
591 ,hr_soft_coding_keyflex scl
592 WHERE asg.assignment_id = p_assignment_id
593 AND asg.soft_coding_keyflex_id =
594 scl.soft_coding_keyflex_id
595 AND p_effective_date BETWEEN asg.effective_start_date
596 AND asg.effective_end_date) x
597 WHERE o1.business_group_id = l_business_group_id
598 AND hoi1.organization_id = o1.organization_id
599 AND hoi1.organization_id = x.org_id
600 --AND hoi1.org_information1 = 'SE_LOCAL_UNIT'
601 AND hoi1.org_information_context = 'CLASS'
602 AND o1.organization_id = hoi2.org_information1
603 AND hoi2.org_information_context = 'SE_LOCAL_UNITS'
604 AND hoi2.organization_id = hoi3.organization_id
605 AND hoi3.org_information_context = 'CLASS'
606 AND hoi3.org_information1 = 'HR_LEGAL_EMPLOYER'
607 AND hoi3.organization_id = hoi4.organization_id
608 AND hoi4.org_information_context = 'SE_ABSENCE_CATEGORY_LIMIT'
609 AND hoi4.org_information1 IS NOT NULL
610 AND hoi4.org_information1 = l_attendance_category_id;
611
612 CURSOR csr_assignment_start
613 IS
614 SELECT MIN (effective_start_date)
615 FROM per_all_assignments_f
616 WHERE assignment_id = p_assignment_id;
617 BEGIN
618 SELECT papf.business_group_id
619 ,papf.person_id
620 ,segment9
621 INTO l_business_group_id
622 ,l_person_id
623 ,l_working_perc
624 FROM per_all_assignments_f paaf
625 ,per_all_people_f papf
626 ,hr_soft_coding_keyflex hsck
627 WHERE paaf.assignment_id = p_assignment_id --15381
628 AND paaf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
629 AND papf.person_id = paaf.person_id
630 AND p_effective_date BETWEEN paaf.effective_start_date
631 AND paaf.effective_end_date
632 AND p_effective_date BETWEEN papf.effective_start_date
633 AND papf.effective_end_date;
634
635 /* To get the entitlement */
636 OPEN csr_assignment_entitlement;
637
638 FETCH csr_assignment_entitlement
639 INTO l_assignment_entitlement;
640
641 CLOSE csr_assignment_entitlement;
642
643 OPEN csr_person_entitlement;
644
645 FETCH csr_person_entitlement
646 INTO l_person_entitlement;
647
648 CLOSE csr_person_entitlement;
649
650 OPEN csr_legal_employer_entitlement;
651
652 FETCH csr_legal_employer_entitlement
653 INTO l_legal_entitlement;
654
655 CLOSE csr_legal_employer_entitlement;
656
657 l_gen_entitlement :=
658 NVL (l_assignment_entitlement
659 ,NVL (l_person_entitlement, l_legal_entitlement)
660 );
661
662 OPEN csr_assignment_start;
663
664 FETCH csr_assignment_start
668
665 INTO l_assignment_start;
666
667 CLOSE csr_assignment_start;
669 /* After discussing with vinod, assignment start date should not be considered while
670 calculating the number of days in a year, entitlement calculation, changing the same on 20-sep-2006 */
671 l_work_days_year :=
672 ( p_earning_end_date
673 - (GREATEST (p_earning_start_date, l_assignment_start))
674 + 1
675 );
676 l_days_year := (p_earning_end_date - p_earning_start_date + 1);
677
678 FOR csr_category IN csr_attendance_category_id
679 LOOP
680 l_attendance_category_id := csr_category.attendance_category_id;
681 pay_balance_pkg.set_context ('SOURCE_TEXT'
682 ,csr_category.attendance_category_id
683 );
684 pay_balance_pkg.set_context ('ASSIGNMENT_ACTION_ID'
685 ,p_assignment_action_id
686 );
687 pay_balance_pkg.set_context ('TAX_UNIT_ID', p_tax_unit_id);
688
689 -- pay_balance_pkg.set_context('DATE_EARNED',p_effective_date);
690 --OPEN csr_Get_Defined_Balance_Id( 'TOTAL_ABSENCE_DAYS_HOLIDAY_PAY_ABC_PER_YTD');
691 --OPEN csr_Get_Defined_Balance_Id( 'TOTAL_ABSENCE_DAYS_HOLIDAY_PAY_ABS_CAT_ASG_EARN_YTD');
692 OPEN csr_get_defined_balance_id
693 ('TOTAL_ABSENCE_DAYS_HOLIDAY_PAY_ASG_LE_ABS_CAT_EARN_YEAR');
694
695 FETCH csr_get_defined_balance_id
696 INTO lr_get_defined_balance_id;
697
698 CLOSE csr_get_defined_balance_id;
699
700 /*l_value :=to_char(pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>lr_Get_Defined_Balance_Id,
701 P_ASSIGNMENT_ACTION_ID =>p_assignment_action_id ) );*/
702 l_value :=
703 pay_balance_pkg.get_value
704 (p_defined_balance_id => lr_get_defined_balance_id
705 ,p_assignment_action_id => p_assignment_action_id
706 ,p_tax_unit_id => p_tax_unit_id
707 ,p_jurisdiction_code => NULL
708 ,p_source_id => NULL
709 ,p_source_text => csr_category.attendance_category_id
710 ,p_tax_group => NULL
711 ,p_date_earned => p_effective_date
712 );
713
714 OPEN csr_generate_max_days;
715
716 FETCH csr_generate_max_days
717 INTO l_generate
718 ,l_max_days;
719
720 CLOSE csr_generate_max_days;
721
722 /* If generate is Y then value greater than the max is considered as absence, else whole value */
723 IF l_generate = 'Y'
724 THEN
725 IF l_value > l_max_days
726 THEN
727 l_absence_days := l_absence_days + (l_value - l_max_days);
728 ELSE
729 l_absence_days := l_absence_days; -- + l_value;
730 END IF;
731 ELSE
732 l_absence_days := l_absence_days + l_value;
733 END IF;
734 END LOOP;
735
736 /*Commented for Bug 5662967 */
737 /*IF l_absence_days IS NULL or l_absence_days=0 THEN
738
739 l_paid_holiday_days:=trunc(0.01*l_working_perc*l_gen_entitlement);
740 l_unpaid_holiday_days:=l_gen_entitlement-l_paid_holiday_days;
741 p_total_working_days:=l_days_year;
742 ELSE*/
743
744 -- l_paid_holiday_days:=trunc(0.01*l_working_perc*((l_days_year-l_absence_days)/l_days_year)*l_gen_entitlement) + 1;
745 /*l_paid_holiday_days :=
746 TRUNC ( 0.01
747 * l_working_perc
748 * ((l_work_days_year - l_absence_days) / l_days_year)
749 * l_gen_entitlement
750 );
751
752 IF ( 0.01
753 * l_working_perc
754 * ((l_days_year - l_absence_days) / l_days_year)
755 * l_gen_entitlement
756 ) > l_paid_holiday_days
757 THEN
758 l_paid_holiday_days := l_paid_holiday_days + 1;
759 END IF;*/
760
761 l_paid_holiday_days:=ceil(((l_work_days_year-l_absence_days)/l_days_year)*l_gen_entitlement) ;
762 l_unpaid_holiday_days:=l_gen_entitlement-l_paid_holiday_days;
763 p_total_working_days:=(l_work_days_year-l_absence_days);
764
765 /* END IF;*/
766 p_paid_holiday_days := l_paid_holiday_days;
767 p_unpaid_holiday_days := l_unpaid_holiday_days;
768 /*IF l_absence_days>p_paid_holiday_days THEN
769 l_saved_days:=0;
770 /* We need to get the previous saved days from the balance and check, if the absence doesnt cross the
771 total days */
772 /* ELSE
773 l_saved_days:=(p_paid_holiday_days-l_absence_days);
774 END IF;*/
775 RETURN 0;
776 END get_paid_unpaid_days;
777
778 FUNCTION get_vacation_days (
779 p_assignment_id IN NUMBER
780 ,p_effective_date IN DATE
781 ,p_payroll_start_date IN DATE
782 ,p_payroll_end_date IN DATE
783 )
784 RETURN NUMBER
785 IS
786 lr_get_defined_balance_id NUMBER;
787 l_vacation_days NUMBER;
788
789 CURSOR csr_get_vacation_days
790 IS
791 SELECT NVL (SUM (peevf2.screen_entry_value), 0)
792 FROM per_all_assignments_f paaf
793 ,pay_element_types_f et
797 ,pay_input_values_f pivf1
794 ,pay_element_entries_f ee
795 ,pay_element_entry_values_f peevf1
796 ,pay_element_entry_values_f peevf2
798 ,pay_input_values_f pivf2
799 WHERE paaf.assignment_id = p_assignment_id
800 AND p_effective_date BETWEEN paaf.effective_start_date
801 AND paaf.effective_end_date
802 AND et.element_name = 'Absence Details'
803 AND et.legislation_code = 'SE'
804 AND ee.assignment_id = paaf.assignment_id
805 AND ee.element_type_id = et.element_type_id
806 AND ee.effective_start_date >= p_payroll_start_date
807 AND ee.effective_end_date <= p_payroll_end_date
808 AND ee.element_entry_id = peevf1.element_entry_id
809 AND pivf1.element_type_id = et.element_type_id
810 AND pivf1.NAME = 'Absence Category'
811 AND peevf1.input_value_id = pivf1.input_value_id
812 AND peevf1.screen_entry_value = 'V'
813 AND ee.element_entry_id = peevf2.element_entry_id
814 AND pivf2.element_type_id = et.element_type_id
815 AND pivf2.NAME = 'Days'
816 AND peevf2.input_value_id = pivf2.input_value_id
817 AND p_payroll_start_date BETWEEN et.effective_start_date
818 AND et.effective_end_date
819 AND p_payroll_end_date BETWEEN et.effective_start_date
820 AND et.effective_end_date
821 --AND peevf1.effective_start_date >= to_date('01-jan-2000')
822 --AND peevf1.effective_end_date <= to_date('31-jan-2000')
823 --AND peevf2.effective_start_date >= to_date('01-jan-2000')
824 --AND peevf2.effective_end_date <= to_date('31-jan-2000')
825 ;
826
827 CURSOR csr_get_defined_balance_id (
828 csr_v_balance_name ff_database_items.user_name%TYPE
829 )
830 IS
831 SELECT ue.creator_id
832 FROM ff_user_entities ue
833 ,ff_database_items di
834 WHERE di.user_name = csr_v_balance_name
835 AND ue.user_entity_id = di.user_entity_id
836 AND ue.legislation_code = 'SE'
837 AND ue.business_group_id IS NULL
838 AND ue.creator_type = 'B';
839 BEGIN
840 OPEN csr_get_vacation_days;
841
842 FETCH csr_get_vacation_days
843 INTO l_vacation_days;
844
845 CLOSE csr_get_vacation_days;
846
847 RETURN l_vacation_days;
848 END get_vacation_days;
849
850 FUNCTION get_saved_year_limit_level (
851 p_assignment_id IN NUMBER
852 ,p_effective_date IN DATE
853 ,p_legal_employer OUT NOCOPY VARCHAR2
854 ,p_person OUT NOCOPY VARCHAR2
855 ,p_assignment OUT NOCOPY VARCHAR2
856 )
857 RETURN NUMBER
858 IS
859 l_person_id NUMBER;
860 l_business_group_id NUMBER;
861 l_assignment_entitlement NUMBER;
862 l_person_entitlement NUMBER;
863 l_legal_entitlement NUMBER;
864
865 CURSOR csr_assignment_entitlement
866 IS
867 SELECT aei_information1
868 FROM per_assignment_extra_info
869 WHERE assignment_id = p_assignment_id
870 AND information_type = 'SE_ASSIGN_HOLIDAY_PAY_DETAILS';
871
872 CURSOR csr_person_entitlement
873 IS
874 SELECT pei_information1
875 FROM per_people_extra_info
876 WHERE person_id = l_person_id
877 AND information_type = 'SE_PERSON_HOLIDAY_PAY_DETAILS';
878
879 CURSOR csr_legal_employer_entitlement
880 IS
881 SELECT hoi4.org_information1
882 FROM hr_organization_units o1
883 ,hr_organization_information hoi1
884 ,hr_organization_information hoi2
885 ,hr_organization_information hoi3
886 ,hr_organization_information hoi4
887 , (SELECT TRIM (scl.segment2) AS org_id
888 FROM per_all_assignments_f asg
889 ,hr_soft_coding_keyflex scl
890 WHERE asg.assignment_id = p_assignment_id
891 AND asg.soft_coding_keyflex_id =
892 scl.soft_coding_keyflex_id
893 AND p_effective_date BETWEEN asg.effective_start_date
894 AND asg.effective_end_date) x
895 WHERE o1.business_group_id = l_business_group_id
896 AND hoi1.organization_id = o1.organization_id
897 AND hoi1.organization_id = x.org_id
898 AND hoi1.org_information1 = 'SE_LOCAL_UNIT'
899 AND hoi1.org_information_context = 'CLASS'
900 AND o1.organization_id = hoi2.org_information1
901 AND hoi2.org_information_context = 'SE_LOCAL_UNITS'
902 AND hoi2.organization_id = hoi3.organization_id
903 AND hoi3.org_information_context = 'CLASS'
904 AND hoi3.org_information1 = 'HR_LEGAL_EMPLOYER'
905 AND hoi3.organization_id = hoi4.organization_id
906 AND hoi4.org_information_context = 'SE_LE_HOLIDAY_PAY_DETAILS'
907 AND hoi4.org_information1 IS NOT NULL;
908 BEGIN
909 SELECT papf.business_group_id
910 ,papf.person_id
911 INTO l_business_group_id
915 ,hr_soft_coding_keyflex hsck
912 ,l_person_id
913 FROM per_all_assignments_f paaf
914 ,per_all_people_f papf
916 WHERE paaf.assignment_id = p_assignment_id --15381
917 AND paaf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
918 AND papf.person_id = paaf.person_id
919 AND p_effective_date BETWEEN paaf.effective_start_date
920 AND paaf.effective_end_date
921 AND p_effective_date BETWEEN papf.effective_start_date
922 AND papf.effective_end_date;
923
924 OPEN csr_assignment_entitlement;
925
926 FETCH csr_assignment_entitlement
927 INTO l_assignment_entitlement;
928
929 CLOSE csr_assignment_entitlement;
930
931 OPEN csr_person_entitlement;
932
933 FETCH csr_person_entitlement
934 INTO l_person_entitlement;
935
936 CLOSE csr_person_entitlement;
937
938 OPEN csr_legal_employer_entitlement;
939
940 FETCH csr_legal_employer_entitlement
941 INTO l_legal_entitlement;
942
943 CLOSE csr_legal_employer_entitlement;
944
945 IF l_legal_entitlement IS NOT NULL
946 THEN
947 p_legal_employer := 'Y';
948 END IF;
949
950 IF l_person_entitlement IS NOT NULL
951 THEN
952 p_person := 'Y';
953 END IF;
954
955 IF l_assignment_entitlement IS NOT NULL
956 THEN
957 p_assignment := 'Y';
958 END IF;
959
960 RETURN 0;
961 END get_saved_year_limit_level;
962
963 FUNCTION get_calculation_option (
964 p_assignment_id IN NUMBER
965 ,p_effective_date IN DATE
966 ,p_local_unit_id IN NUMBER
967 ,p_tax_unit_id IN NUMBER
968 ,p_absence_category IN VARCHAR2
969 ,p_return_vacation OUT NOCOPY VARCHAR2
970 )
971 RETURN NUMBER
972 IS
973 BEGIN
974 /* OPEN csr_get_vacation_days;
975 FETCH csr_get_vacation_days INTO l_vacation_days;
976 CLOSE csr_get_vacation_days;
977
978 IF l_vacation_days is not null
979 P_return_vacation := l_vacation_days;
980 ELSE
981 P_return_vacation := 0;
982 END IF;*/
983 p_return_vacation := '';
984 RETURN 1;
985 END get_calculation_option;
986
987 FUNCTION element_exist (
988 p_assignment_id IN NUMBER
989 ,p_date_earned IN DATE
990 ,p_element_name IN VARCHAR2
991 )
992 RETURN NUMBER
993 IS
994 l_element_exist NUMBER;
995
996 CURSOR check_element_exist (
997 p_assignment_id IN NUMBER
998 ,p_effective_date IN DATE
999 ,p_element_name IN VARCHAR2
1000 )
1001 IS
1002 SELECT 1
1003 FROM per_all_assignments_f asg
1004 ,pay_element_links_f el
1005 ,pay_element_types_f et
1006 ,pay_element_entries_f ee
1007 WHERE asg.assignment_id = p_assignment_id
1008 AND et.element_name = p_element_name
1009 AND et.legislation_code = 'SE'
1010 AND el.business_group_id = asg.business_group_id
1011 AND el.element_type_id = et.element_type_id
1012 AND ee.assignment_id = asg.assignment_id
1013 AND ee.element_link_id = el.element_link_id
1014 AND p_effective_date BETWEEN ee.effective_start_date
1015 AND ee.effective_end_date
1016 AND p_effective_date BETWEEN asg.effective_start_date
1017 AND asg.effective_end_date
1018 AND p_effective_date BETWEEN et.effective_start_date
1019 AND et.effective_end_date
1020 AND p_effective_date BETWEEN el.effective_start_date
1021 AND el.effective_end_date;
1022 BEGIN
1023 l_element_exist := 0;
1024
1025 OPEN check_element_exist (p_assignment_id
1026 ,p_date_earned
1027 ,p_element_name
1028 );
1029
1030 FETCH check_element_exist
1031 INTO l_element_exist;
1032
1033 CLOSE check_element_exist;
1034
1035 RETURN l_element_exist;
1036 END element_exist;
1037
1038 -- Function to get the Further period for the payroll Run.
1039 FUNCTION get_further_period_details (
1040 p_payroll_id IN NUMBER
1041 ,p_date_earned IN DATE
1042 ,p_pay_saved_holiday OUT NOCOPY VARCHAR2
1043 ,p_no_of_saved_days OUT NOCOPY NUMBER
1044 ,p_pay_remaining_saved_days OUT NOCOPY VARCHAR2
1045 ,p_pay_additional_holiday OUT NOCOPY VARCHAR2
1046 ,p_no_of_additional_holiday OUT NOCOPY NUMBER
1047 ,p_pay_remaining_addl_holiday OUT NOCOPY VARCHAR2
1048 )
1049 RETURN NUMBER
1050 IS
1051 l_fixed_period NUMBER;
1052
1053 CURSOR csr_further_period_details
1054 IS
1055 SELECT prd_information1
1056 ,prd_information3
1057 ,prd_information4
1058 ,prd_information6
1059 ,prd_information8
1060 ,prd_information9
1061 FROM per_time_periods
1065 lr_further_period_details csr_further_period_details%ROWTYPE;
1062 WHERE payroll_id = p_payroll_id
1063 AND p_date_earned BETWEEN start_date AND end_date;
1064
1066 BEGIN
1067 OPEN csr_further_period_details;
1068
1069 FETCH csr_further_period_details
1070 INTO lr_further_period_details;
1071
1072 CLOSE csr_further_period_details;
1073
1074 p_pay_saved_holiday :=
1075 NVL (lr_further_period_details.prd_information1, 'N');
1076 p_no_of_saved_days := lr_further_period_details.prd_information3;
1077 p_pay_remaining_saved_days :=
1078 NVL (lr_further_period_details.prd_information4, 'N');
1079 p_pay_additional_holiday := lr_further_period_details.prd_information6;
1080 p_no_of_additional_holiday := lr_further_period_details.prd_information8;
1081 p_pay_remaining_addl_holiday :=
1082 lr_further_period_details.prd_information9;
1083 l_fixed_period := 1;
1084 RETURN l_fixed_period;
1085 END get_further_period_details;
1086
1087 FUNCTION get_saved_holiday_limit (
1088 p_assignment_id IN NUMBER
1089 ,p_effective_date IN DATE
1090 )
1091 RETURN NUMBER
1092 IS
1093 l_assignment_limit NUMBER;
1094 l_person_limit NUMBER;
1095 l_legal_limit NUMBER;
1096 l_person_id NUMBER;
1097 l_business_group_id NUMBER;
1098 l_gen_limit NUMBER;
1099 l_assignment_entitlement NUMBER;
1100 l_person_entitlement NUMBER;
1101 l_legal_entitlement NUMBER;
1102 l_gen_entitlement NUMBER;
1103
1104 CURSOR csr_assignment_limit
1105 IS
1106 SELECT aei_information3
1107 FROM per_assignment_extra_info
1108 WHERE assignment_id = p_assignment_id
1109 AND information_type = 'SE_ASSIGN_HOLIDAY_PAY_DETAILS';
1110
1111 CURSOR csr_person_limit
1112 IS
1113 SELECT pei_information3
1114 FROM per_people_extra_info
1115 WHERE person_id = l_person_id
1116 AND information_type = 'SE_PERSON_HOLIDAY_PAY_DETAILS';
1117
1118 CURSOR csr_legal_employer_limit
1119 IS
1120 SELECT hoi4.org_information3
1121 FROM hr_organization_units o1
1122 ,hr_organization_information hoi1
1123 ,hr_organization_information hoi2
1124 ,hr_organization_information hoi3
1125 ,hr_organization_information hoi4
1126 , (SELECT TRIM (scl.segment2) AS org_id
1127 FROM per_all_assignments_f asg
1128 ,hr_soft_coding_keyflex scl
1129 WHERE asg.assignment_id = p_assignment_id
1130 AND asg.soft_coding_keyflex_id =
1131 scl.soft_coding_keyflex_id
1132 AND p_effective_date BETWEEN asg.effective_start_date
1133 AND asg.effective_end_date) x
1134 WHERE o1.business_group_id = l_business_group_id
1135 AND hoi1.organization_id = o1.organization_id
1136 AND hoi1.organization_id = x.org_id
1137 AND hoi1.org_information1 = 'SE_LOCAL_UNIT'
1138 AND hoi1.org_information_context = 'CLASS'
1139 AND o1.organization_id = hoi2.org_information1
1140 AND hoi2.org_information_context = 'SE_LOCAL_UNITS'
1141 AND hoi2.organization_id = hoi3.organization_id
1142 AND hoi3.org_information_context = 'CLASS'
1143 AND hoi3.org_information1 = 'HR_LEGAL_EMPLOYER'
1144 AND hoi3.organization_id = hoi4.organization_id
1145 AND hoi4.org_information_context = 'SE_LE_HOLIDAY_PAY_DETAILS'
1146 AND hoi4.org_information1 IS NOT NULL;
1147
1148 CURSOR csr_assignment_entitlement
1149 IS
1150 SELECT aei_information1
1151 FROM per_assignment_extra_info
1152 WHERE assignment_id = p_assignment_id
1153 AND information_type = 'SE_ASSIGN_HOLIDAY_PAY_DETAILS';
1154
1155 CURSOR csr_person_entitlement
1156 IS
1157 SELECT pei_information1
1158 FROM per_people_extra_info
1159 WHERE person_id = l_person_id
1160 AND information_type = 'SE_PERSON_HOLIDAY_PAY_DETAILS';
1161
1162 CURSOR csr_legal_employer_entitlement
1163 IS
1164 SELECT hoi4.org_information1
1165 FROM hr_organization_units o1
1166 ,hr_organization_information hoi1
1167 ,hr_organization_information hoi2
1168 ,hr_organization_information hoi3
1169 ,hr_organization_information hoi4
1170 , (SELECT TRIM (scl.segment2) AS org_id
1171 FROM per_all_assignments_f asg
1172 ,hr_soft_coding_keyflex scl
1173 WHERE asg.assignment_id = p_assignment_id
1174 AND asg.soft_coding_keyflex_id =
1175 scl.soft_coding_keyflex_id
1176 AND p_effective_date BETWEEN asg.effective_start_date
1177 AND asg.effective_end_date) x
1178 WHERE o1.business_group_id = l_business_group_id
1179 AND hoi1.organization_id = o1.organization_id
1180 AND hoi1.organization_id = x.org_id
1181 AND hoi1.org_information1 = 'SE_LOCAL_UNIT'
1182 AND hoi1.org_information_context = 'CLASS'
1183 AND o1.organization_id = hoi2.org_information1
1187 AND hoi3.org_information1 = 'HR_LEGAL_EMPLOYER'
1184 AND hoi2.org_information_context = 'SE_LOCAL_UNITS'
1185 AND hoi2.organization_id = hoi3.organization_id
1186 AND hoi3.org_information_context = 'CLASS'
1188 AND hoi3.organization_id = hoi4.organization_id
1189 AND hoi4.org_information_context = 'SE_LE_HOLIDAY_PAY_DETAILS'
1190 AND hoi4.org_information1 IS NOT NULL;
1191 BEGIN
1192 SELECT papf.business_group_id
1193 ,papf.person_id
1194 INTO l_business_group_id
1195 ,l_person_id
1196 FROM per_all_assignments_f paaf
1197 ,per_all_people_f papf
1198 ,hr_soft_coding_keyflex hsck
1199 WHERE paaf.assignment_id = p_assignment_id --15381
1200 AND paaf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
1201 AND papf.person_id = paaf.person_id
1202 AND p_effective_date BETWEEN paaf.effective_start_date
1203 AND paaf.effective_end_date
1204 AND p_effective_date BETWEEN papf.effective_start_date
1205 AND papf.effective_end_date;
1206
1207 OPEN csr_assignment_limit;
1208
1209 FETCH csr_assignment_limit
1210 INTO l_assignment_limit;
1211
1212 CLOSE csr_assignment_limit;
1213
1214 OPEN csr_person_limit;
1215
1216 FETCH csr_person_limit
1217 INTO l_person_limit;
1218
1219 CLOSE csr_person_limit;
1220
1221 OPEN csr_legal_employer_limit;
1222
1223 FETCH csr_legal_employer_limit
1224 INTO l_legal_limit;
1225
1226 CLOSE csr_legal_employer_limit;
1227
1228 l_gen_limit :=
1229 NVL (l_assignment_limit, NVL (l_person_limit, l_legal_limit));
1230
1231 OPEN csr_assignment_entitlement;
1232
1233 FETCH csr_assignment_entitlement
1234 INTO l_assignment_entitlement;
1235
1236 CLOSE csr_assignment_entitlement;
1237
1238 OPEN csr_person_entitlement;
1239
1240 FETCH csr_person_entitlement
1241 INTO l_person_entitlement;
1242
1243 CLOSE csr_person_entitlement;
1244
1245 OPEN csr_legal_employer_entitlement;
1246
1247 FETCH csr_legal_employer_entitlement
1248 INTO l_legal_entitlement;
1249
1250 CLOSE csr_legal_employer_entitlement;
1251
1252 l_gen_entitlement :=
1253 NVL (l_assignment_entitlement
1254 ,NVL (l_person_entitlement, l_legal_entitlement)
1255 );
1256 RETURN (l_gen_entitlement - l_gen_limit);
1257 END get_saved_holiday_limit;
1258
1259 FUNCTION get_end_year (p_date_earned IN DATE, p_tax_unit_id IN NUMBER)
1260 RETURN NUMBER
1261 IS
1262 l_start_month CHAR (2);
1263 l_end_month CHAR (2);
1264
1265 CURSOR csr_earning_year
1266 IS
1267 SELECT SUBSTR (hoi2.org_information1, 4, 2)
1268 ,SUBSTR (hoi2.org_information2, 4, 2)
1269 FROM hr_organization_units o1
1270 ,hr_organization_information hoi1
1271 ,hr_organization_information hoi2
1272 WHERE hoi1.organization_id = o1.organization_id
1273 AND hoi1.organization_id = p_tax_unit_id --3134
1274 AND hoi1.org_information_context = 'CLASS'
1275 AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
1276 AND hoi1.organization_id = hoi2.organization_id
1277 AND hoi2.org_information_context = 'SE_HOLIDAY_YEAR_DEFN'
1278 AND hoi2.org_information1 IS NOT NULL;
1279 BEGIN
1280 OPEN csr_earning_year;
1281
1282 FETCH csr_earning_year
1283 INTO l_start_month
1284 ,l_end_month;
1285
1286 CLOSE csr_earning_year;
1287
1288 /* Logic for Earning Year is from Jan-Dec */
1289 IF l_start_month = '01' AND l_end_month = '12'
1290 THEN
1291 RETURN TO_NUMBER (TO_CHAR (p_date_earned, 'YYYY'));
1292 ELSE
1293 IF TO_NUMBER (TO_CHAR (p_date_earned, 'MM')) <
1294 TO_NUMBER (l_start_month)
1295 THEN
1296 RETURN TO_NUMBER (TO_CHAR (p_date_earned, 'YYYY') - 1);
1297 ELSE
1298 RETURN TO_NUMBER (TO_CHAR (p_date_earned, 'YYYY'));
1299 END IF;
1300 END IF;
1301 END get_end_year;
1302
1303 FUNCTION get_remaining_saved_pay (
1304 p_assignment_id IN NUMBER
1305 ,p_assignment_action_id IN NUMBER
1306 ,p_effective_date IN DATE
1307 ,p_payroll_id IN NUMBER
1308 ,p_tax_unit_id IN NUMBER
1309 ,p_days_to_pay OUT NOCOPY NUMBER
1310 )
1311 RETURN VARCHAR2
1312 IS
1313 l_pay_date DATE;
1314 l_end_year NUMBER;
1315 l_start_month NUMBER;
1316 l_end_month NUMBER;
1317 l_start_date DATE;
1318 l_end_date DATE;
1319 l_value NUMBER;
1320 l_total_saved_holidays NUMBER;
1321 l_total_saved_days_tracking NUMBER;
1322 l_tracking_start_date DATE;
1323 l_tracking_end_date DATE;
1324 l_days_to_pay DATE;
1325 lr_get_defined_balance_id NUMBER;
1326 l_pay_yes_no VARCHAR (1);
1327
1328 CURSOR csr_further_period_details
1329 IS
1330 SELECT prd_information4
1331 FROM per_time_periods
1332 WHERE payroll_id = p_payroll_id
1333 AND p_effective_date BETWEEN start_date AND end_date;
1334
1335 CURSOR csr_earning_year
1336 IS
1337 SELECT SUBSTR (hoi2.org_information1, 4, 2)
1338 ,SUBSTR (hoi2.org_information2, 4, 2)
1339 FROM hr_organization_units o1
1340 ,hr_organization_information hoi1
1341 ,hr_organization_information hoi2
1342 WHERE hoi1.organization_id = o1.organization_id
1343 AND hoi1.organization_id = p_tax_unit_id --3134
1344 AND hoi1.org_information_context = 'CLASS'
1345 AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
1346 AND hoi1.organization_id = hoi2.organization_id
1347 AND hoi2.org_information_context = 'SE_HOLIDAY_YEAR_DEFN'
1348 AND hoi2.org_information1 IS NOT NULL;
1349
1350 CURSOR csr_get_defined_balance_id (
1351 csr_v_balance_name ff_database_items.user_name%TYPE
1352 )
1353 IS
1354 SELECT ue.creator_id
1355 FROM ff_user_entities ue
1356 ,ff_database_items di
1357 WHERE di.user_name = csr_v_balance_name
1358 AND ue.user_entity_id = di.user_entity_id
1359 AND ue.legislation_code = 'SE'
1360 AND ue.business_group_id IS NULL
1361 AND ue.creator_type = 'B';
1362 BEGIN
1363 OPEN csr_further_period_details;
1364
1365 FETCH csr_further_period_details
1366 INTO l_pay_yes_no;
1367
1368 CLOSE csr_further_period_details;
1369
1370 IF l_pay_yes_no IS NULL
1371 THEN
1372 l_pay_yes_no := 'N';
1373 END IF;
1374
1375 p_days_to_pay := 0;
1376 RETURN l_pay_yes_no;
1377 /* OPEN csr_Further_period_details;
1378 FETCH csr_Further_period_details INTO l_pay_date;
1379 CLOSE csr_Further_period_details;
1380 l_end_year:=GET_END_YEAR(l_pay_date,p_tax_unit_id);
1381 OPEN csr_Earning_Year;
1382 FETCH csr_Earning_Year INTO l_start_month,l_end_month;
1383 CLOSE csr_Earning_Year;
1384 l_start_date:=TO_DATE('01/'|| l_start_month || '/' || l_end_year-1,'dd/mm/yyyy');
1385 l_end_date:=TO_DATE(last_day('01/'|| l_end_month || '/' || l_end_year),'dd/mm/yyyy');
1386
1387 IF to_number(to_char(l_pay_date,'YYYY')) - to_number(to_char(l_start_date,'YYYY')) < 5 THEN
1388 /* we dont need to pay anything, exit */
1389 /* P_days_to_pay:=0;
1390 RETURN 'N';
1391 ELSIF to_number(to_char(l_pay_date,'YYYY')) - to_number(to_char(l_start_date,'YYYY')) = 5 THEN
1392 /* do the calculation in the formula */
1393 /* P_days_to_pay:=0;
1394 RETURN 'E';
1395 ELSE
1396
1397 pay_balance_pkg.set_context('TAX_UNIT',p_tax_unit_id);
1398 pay_balance_pkg.set_context('ASSIGNMENT_ACTION_ID',p_assignment_action_id);
1399 OPEN csr_Get_Defined_Balance_Id( 'TOTAL_SAVED_HOLIDAY_DAYS_ASG_HY_YTD');
1400 FETCH csr_Get_Defined_Balance_Id INTO lr_Get_Defined_Balance_Id;
1401 CLOSE csr_Get_Defined_Balance_Id;
1402 l_total_saved_holidays :=to_char(pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>lr_Get_Defined_Balance_Id,
1403 P_ASSIGNMENT_ACTION_ID =>p_assignment_action_id ) );
1404 /*l_total_saved_holidays:=pay_balance_pkg.get_value(lr_Get_Defined_Balance_Id,NULL,p_tax_unit_id,
1405 NULL,NULL,NULL,l_pay_date);*/
1406
1407 /* l_tracking_start_date:=to_date('01/'|| to_char(l_start_date,'MM') ||'/' || to_number(to_char(l_start_date,'YYYY'))+4,'dd/mm/yyyy');
1408 l_tracking_end_date:=last_day(to_date('01/'|| to_char(l_end_date,'MM') ||'/' || to_number(to_char(l_end_date,'YYYY'))+4,'dd/mm/yyyy'));
1409 pay_balance_pkg.set_context('TAX_UNIT',p_tax_unit_id);
1410 pay_balance_pkg.set_context('ASSIGNMENT_ACTION_ID',p_assignment_action_id);
1411 OPEN csr_Get_Defined_Balance_Id( 'TOTAL_SAVED_HOLIDAY_DAYS_TRACKING_ASG_HY_YTD');
1412 FETCH csr_Get_Defined_Balance_Id INTO lr_Get_Defined_Balance_Id;
1413 CLOSE csr_Get_Defined_Balance_Id;
1414 l_total_saved_days_tracking :=to_char(pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>lr_Get_Defined_Balance_Id,
1415 P_ASSIGNMENT_ACTION_ID =>p_assignment_action_id ) );
1416 /*l_total_saved_days_tracking:=(pay_balance_pkg.get_value(lr_Get_Defined_Balance_Id,NULL,p_tax_unit_id,
1417 NULL,NULL,NULL,l_tracking_start_date);*/
1418 /* IF l_total_saved_holidays>l_total_saved_days_tracking THEN
1422 P_days_to_pay:=0;
1419 /* We need to pay the remaining days for that person */
1420 /* P_days_to_pay:= (l_total_saved_holidays-l_total_saved_days_tracking);
1421 ELSE
1423 END IF;
1424 RETURN 'G';
1425 END IF;*/
1426 END get_remaining_saved_pay;
1427
1428 FUNCTION get_hourly_salaried_code (
1429 p_assignment_id_id IN NUMBER
1430 ,p_date_earned IN DATE
1431 )
1432 RETURN VARCHAR2
1433 IS
1434 CURSOR csr_hourly_salaried_code
1435 IS
1436 SELECT NVL (hourly_salaried_code, '##') hsc
1437 FROM per_all_assignments_f
1438 WHERE assignment_id = p_assignment_id_id
1439 AND p_date_earned BETWEEN effective_start_date AND effective_end_date;
1440
1441 lr_hourly_salaried_code csr_hourly_salaried_code%ROWTYPE;
1442 BEGIN
1443 --hr_utility.trace_on(null,'raja');
1444 OPEN csr_hourly_salaried_code;
1445
1446 FETCH csr_hourly_salaried_code
1447 INTO lr_hourly_salaried_code;
1448
1449 CLOSE csr_hourly_salaried_code;
1450
1451 RETURN lr_hourly_salaried_code.hsc;
1452 END get_hourly_salaried_code;
1453
1454 -- Function to get the Further period for the payroll Run.
1455 FUNCTION get_absence_day_with_as_per (
1456 p_payroll_id IN NUMBER
1457 ,p_date_earned IN DATE
1458 ,p_pay_saved_holiday OUT NOCOPY VARCHAR2
1459 ,p_no_of_saved_days OUT NOCOPY NUMBER
1460 ,p_pay_remaining_saved_days OUT NOCOPY VARCHAR2
1461 ,p_pay_additional_holiday OUT NOCOPY VARCHAR2
1462 ,p_no_of_additional_holiday OUT NOCOPY NUMBER
1463 ,p_pay_remaining_addl_holiday OUT NOCOPY VARCHAR2
1464 )
1465 RETURN NUMBER
1466 IS
1467 l_fixed_period NUMBER;
1468
1469 CURSOR csr_further_period_details
1470 IS
1471 SELECT prd_information1
1472 ,prd_information3
1473 ,prd_information4
1474 ,prd_information5
1475 ,prd_information7
1476 ,prd_information8
1477 FROM per_time_periods
1478 WHERE payroll_id = p_payroll_id
1479 AND p_date_earned BETWEEN start_date AND end_date;
1480
1481 lr_further_period_details csr_further_period_details%ROWTYPE;
1482 BEGIN
1483 OPEN csr_further_period_details;
1484
1485 FETCH csr_further_period_details
1486 INTO lr_further_period_details;
1487
1488 CLOSE csr_further_period_details;
1489
1490 p_pay_saved_holiday :=
1491 NVL (lr_further_period_details.prd_information1, 'N');
1492 p_no_of_saved_days := lr_further_period_details.prd_information3;
1493 p_pay_remaining_saved_days :=
1494 NVL (lr_further_period_details.prd_information4, 'N');
1495 p_pay_additional_holiday := lr_further_period_details.prd_information5;
1496 p_no_of_additional_holiday := lr_further_period_details.prd_information7;
1497 p_pay_remaining_addl_holiday :=
1498 lr_further_period_details.prd_information8;
1499 l_fixed_period := 1;
1500 RETURN l_fixed_period;
1501 END get_absence_day_with_as_per;
1502
1503 FUNCTION update_entitlement_ran (p_tax_unit_id IN NUMBER)
1504 RETURN NUMBER
1505 IS
1506 BEGIN
1507 UPDATE hr_organization_information
1508 SET org_information5 = 'Y'
1509 WHERE org_information_id =
1510 (SELECT hoi2.org_information_id
1511 FROM hr_organization_units o1
1512 ,hr_organization_information hoi1
1513 ,hr_organization_information hoi2
1514 WHERE hoi1.organization_id = o1.organization_id
1515 AND hoi1.organization_id = p_tax_unit_id --3134
1516 AND hoi1.org_information_context = 'CLASS'
1517 AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
1518 AND hoi1.organization_id = hoi2.organization_id
1519 AND hoi2.org_information_context = 'SE_HOLIDAY_YEAR_DEFN'
1520 AND hoi2.org_information1 IS NOT NULL);
1521
1522 RETURN 0;
1523 EXCEPTION
1524 WHEN OTHERS
1525 THEN
1526 RETURN -1;
1527 END update_entitlement_ran;
1528
1529 FUNCTION get_calendar_days (
1530 p_date_earned IN DATE
1531 ,p_tax_unit_id IN NUMBER
1532 ,p_assignment_id IN NUMBER
1533 ,p_pay_proc_period_start_date IN DATE
1534 ,p_pay_proc_period_end_date IN DATE
1535 ,p_earn_end_date OUT NOCOPY DATE
1536 )
1537 RETURN NUMBER
1538 IS
1539 l_end_month CHAR (2);
1540 l_start_month CHAR (2);
1541 l_end_date DATE;
1542 l_start_date DATE;
1543 l_days_year NUMBER;
1544 l_status_return CHAR (1);
1545 l_termination_date DATE;
1546 l_year NUMBER;
1547
1548 CURSOR csr_earning_year
1549 IS
1550 SELECT SUBSTR (hoi2.org_information1, 4, 2)
1551 ,SUBSTR (hoi2.org_information2, 4, 2)
1552 FROM hr_organization_units o1
1553 ,hr_organization_information hoi1
1554 ,hr_organization_information hoi2
1555 WHERE hoi1.organization_id = o1.organization_id
1556 AND hoi1.organization_id = p_tax_unit_id
1557 AND hoi1.org_information_context = 'CLASS'
1558 AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
1559 AND hoi1.organization_id = hoi2.organization_id
1560 AND hoi2.org_information_context = 'SE_HOLIDAY_YEAR_DEFN'
1561 AND hoi2.org_information1 IS NOT NULL;
1562
1563 CURSOR csr_assignment_start
1564 IS
1565 SELECT MIN (effective_start_date)
1566 FROM per_all_assignments_f
1567 WHERE assignment_id = p_assignment_id;
1568 BEGIN
1569 --l_year:=GET_END_YEAR(p_date_earned,p_tax_unit_id);
1570 OPEN csr_earning_year;
1571
1572 FETCH csr_earning_year
1573 INTO l_start_month
1574 ,l_end_month;
1575
1576 CLOSE csr_earning_year;
1577
1578 --l_end_date:=last_day(to_date('01/'||l_end_month||'/'||l_year,'dd/mm/yyyy'));
1579
1580 --l_start_date:= to_date('01/'||l_start_month||'/'||l_year,'dd/mm/yyyy');
1581 IF l_start_month = '01' AND l_end_month = '12'
1582 THEN
1583 l_end_date :=
1584 LAST_DAY (TO_DATE ( '01/'
1585 || l_end_month
1586 || '/'
1587 || TO_NUMBER ( TO_CHAR (p_date_earned, 'yyyy')
1588 - 1
1589 )
1590 ,'dd/mm/yyyy'
1591 )
1592 );
1593 l_start_date :=
1594 TO_DATE ( '01/'
1595 || l_start_month
1596 || '/'
1597 || TO_NUMBER (TO_CHAR (p_date_earned, 'yyyy') - 1)
1598 ,'dd/mm/yyyy'
1599 );
1600 ELSE
1601 l_end_date :=
1602 LAST_DAY (TO_DATE ( '01/'
1603 || l_end_month
1604 || '/'
1605 || TO_NUMBER (TO_CHAR (p_date_earned, 'yyyy'))
1606 ,'dd/mm/yyyy'
1607 )
1608 );
1609 l_start_date :=
1610 TO_DATE ( '01/'
1611 || l_start_month
1612 || '/'
1613 || TO_NUMBER (TO_CHAR (p_date_earned, 'yyyy') - 1)
1614 ,'dd/mm/yyyy'
1615 );
1616 END IF;
1617
1618 l_days_year := l_end_date - l_start_date;
1619 p_earn_end_date := l_end_date;
1620 /*l_status_return:=get_assg_status(p_tax_unit_id,p_assignment_id,p_pay_proc_period_start_date,p_pay_proc_period_end_date ,l_termination_date);
1621 IF l_status_return='T' then
1622 l_days_year:=(l_termination_date-l_end_date+1);
1623 ELSE
1624 l_days_year:=0;
1625 END IF;*/
1626 RETURN l_days_year;
1627 END get_calendar_days;
1628
1629 FUNCTION get_assg_status (
1630 p_business_group_id IN NUMBER
1631 ,p_asg_id IN NUMBER
1632 ,p_pay_proc_period_start_date IN DATE
1633 ,p_pay_proc_period_end_date IN DATE
1634 ,p_termination_date OUT NOCOPY DATE
1635 )
1636 RETURN VARCHAR2
1637 IS
1638 CURSOR csr_asg
1639 IS
1640 SELECT paaf.effective_start_date effective_start_date
1641 FROM per_all_assignments_f paaf
1642 WHERE paaf.business_group_id = p_business_group_id
1643 AND paaf.assignment_id = p_asg_id
1644 AND paaf.assignment_status_type_id = 3;
1645
1646 l_flag VARCHAR2 (1);
1647 l_asg_status csr_asg%ROWTYPE;
1651 FETCH csr_asg
1648 BEGIN
1649 OPEN csr_asg;
1650
1652 INTO l_asg_status;
1653
1654 CLOSE csr_asg;
1655
1656 p_termination_date := l_asg_status.effective_start_date;
1657
1658 IF l_asg_status.effective_start_date >= p_pay_proc_period_start_date
1659 AND l_asg_status.effective_start_date <=
1660 (p_pay_proc_period_end_date + 1
1661 )
1662 THEN
1663 l_flag := 'T';
1664 ELSE
1665 l_flag := 'A';
1666 END IF;
1667
1668 RETURN l_flag;
1669 END get_assg_status;
1670
1671 FUNCTION compensation_entitlement (
1672 p_date_earned IN DATE
1673 ,p_tax_unit_id IN NUMBER
1674 ,p_assignment_id IN NUMBER
1675 ,p_assignment_action_id IN NUMBER
1676 ,p_pay_proc_period_start_date IN DATE
1677 ,p_pay_proc_period_end_date IN DATE
1678 ,p_paid_holiday_days OUT NOCOPY NUMBER
1679 ,p_termination_date IN DATE
1680 ,p_earn_end_date IN DATE
1681 )
1682 RETURN NUMBER
1683 IS
1684 l_termination_date DATE;
1685 l_year NUMBER;
1686 l_end_month CHAR (2);
1687 l_end_date DATE;
1688 l_status_return CHAR (1);
1689 l_days_year NUMBER;
1690 l_worked_days_year NUMBER;
1691 lr_get_defined_balance_id NUMBER;
1692 l_generate CHAR (1);
1693 l_max_days NUMBER;
1694 l_value NUMBER;
1695 l_absence_days NUMBER := 0;
1696 l_attendance_category_id VARCHAR2 (30);
1697 l_business_group_id NUMBER;
1698 l_paid_holiday_days NUMBER;
1699 l_assignment_entitlement NUMBER;
1700 l_person_entitlement NUMBER;
1701 l_legal_entitlement NUMBER;
1702 l_gen_entitlement NUMBER;
1703 l_person_id NUMBER;
1704 l_working_perc VARCHAR2 (30); --NUMBER; --Existing bug fixed as part of 9747212
1705
1706 CURSOR csr_attendance_category_id
1707 IS
1708 SELECT DISTINCT eev1.screen_entry_value attendance_category_id
1709 FROM per_all_assignments_f asg1
1710 --,per_all_assignments_f asg2
1711 -- ,per_all_people_f per
1712 , pay_element_links_f el
1713 ,pay_element_types_f et
1714 ,pay_input_values_f iv1
1715 ,pay_element_entries_f ee
1716 ,pay_element_entry_values_f eev1
1717 WHERE asg1.assignment_id = p_assignment_id
1718 AND p_date_earned BETWEEN asg1.effective_start_date
1719 AND asg1.effective_end_date
1720 --AND p_effective_date BETWEEN asg2.effective_start_date AND asg2.effective_end_date
1721 -- AND per.person_id = asg1.person_id
1722 -- AND asg2.person_id = per.person_id
1723 -- AND asg2.primary_flag = 'Y'
1724 --AND et.element_name = 'Absence Details'
1725 AND et.legislation_code = 'SE'
1726 --OR et.business_group_id=3261 ) --checking for the business group, it should be removed
1727 AND iv1.element_type_id = et.element_type_id
1728 AND iv1.NAME = 'Absence Category' --l_inp_val_name
1729 AND el.business_group_id = asg1.business_group_id
1730 AND el.element_type_id = et.element_type_id
1731 AND ee.assignment_id = asg1.assignment_id
1732 AND ee.element_link_id = el.element_link_id
1733 AND eev1.element_entry_id = ee.element_entry_id
1734 AND eev1.input_value_id = iv1.input_value_id
1735 AND ee.effective_start_date > p_earn_end_date
1736 AND ee.effective_end_date <= p_termination_date
1737 AND eev1.effective_start_date > p_earn_end_date
1738 AND eev1.effective_end_date <= p_termination_date;
1739
1740 CURSOR csr_generate_max_days
1741 IS
1742 SELECT hoi4.org_information2
1743 ,hoi4.org_information3
1744 FROM hr_organization_units o1
1745 ,hr_organization_information hoi1
1746 ,hr_organization_information hoi2
1747 ,hr_organization_information hoi3
1748 ,hr_organization_information hoi4
1749 , (SELECT TRIM (scl.segment2) AS org_id
1750 FROM per_all_assignments_f asg
1751 ,hr_soft_coding_keyflex scl
1752 WHERE asg.assignment_id = p_assignment_id
1753 AND asg.soft_coding_keyflex_id =
1754 scl.soft_coding_keyflex_id
1755 AND p_date_earned BETWEEN asg.effective_start_date
1756 AND asg.effective_end_date) x
1757 WHERE o1.business_group_id = l_business_group_id
1758 AND hoi1.organization_id = o1.organization_id
1759 AND hoi1.organization_id = x.org_id
1760 --AND hoi1.org_information1 = 'SE_LOCAL_UNIT'
1761 AND hoi1.org_information_context = 'CLASS'
1762 AND o1.organization_id = hoi2.org_information1
1763 AND hoi2.org_information_context = 'SE_LOCAL_UNITS'
1764 AND hoi2.organization_id = hoi3.organization_id
1765 AND hoi3.org_information_context = 'CLASS'
1766 AND hoi3.org_information1 = 'HR_LEGAL_EMPLOYER'
1767 AND hoi3.organization_id = hoi4.organization_id
1768 AND hoi4.org_information_context = 'SE_ABSENCE_CATEGORY_LIMIT'
1769 AND hoi4.org_information1 IS NOT NULL
1770 AND hoi4.org_information1 = l_attendance_category_id;
1771
1772 CURSOR csr_earning_year
1773 IS
1774 SELECT SUBSTR (hoi2.org_information2, 4, 2)
1775 FROM hr_organization_units o1
1776 ,hr_organization_information hoi1
1777 ,hr_organization_information hoi2
1778 WHERE hoi1.organization_id = o1.organization_id
1779 AND hoi1.organization_id = p_tax_unit_id
1780 AND hoi1.org_information_context = 'CLASS'
1781 AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
1782 AND hoi1.organization_id = hoi2.organization_id
1783 AND hoi2.org_information_context = 'SE_HOLIDAY_YEAR_DEFN'
1784 AND hoi2.org_information1 IS NOT NULL;
1785
1786 CURSOR csr_get_defined_balance_id (
1787 csr_v_balance_name ff_database_items.user_name%TYPE
1788 )
1789 IS
1790 SELECT ue.creator_id
1791 FROM ff_user_entities ue
1792 ,ff_database_items di
1793 WHERE di.user_name = csr_v_balance_name
1794 AND ue.user_entity_id = di.user_entity_id
1795 AND ue.legislation_code = 'SE'
1796 AND ue.business_group_id IS NULL
1797 AND ue.creator_type = 'B';
1798
1799 CURSOR csr_assignment_entitlement
1800 IS
1801 SELECT aei_information1
1802 FROM per_assignment_extra_info
1803 WHERE assignment_id = p_assignment_id
1804 AND information_type = 'SE_ASSIGN_HOLIDAY_PAY_DETAILS';
1805
1806 CURSOR csr_person_entitlement
1807 IS
1808 SELECT pei_information1
1809 FROM per_people_extra_info
1810 WHERE person_id = l_person_id
1811 AND information_type = 'SE_PERSON_HOLIDAY_PAY_DETAILS';
1812
1813 CURSOR csr_legal_employer_entitlement
1814 IS
1815 SELECT hoi4.org_information1
1816 FROM hr_organization_units o1
1817 ,hr_organization_information hoi1
1818 ,hr_organization_information hoi2
1819 ,hr_organization_information hoi3
1820 ,hr_organization_information hoi4
1821 , (SELECT TRIM (scl.segment2) AS org_id
1822 FROM per_all_assignments_f asg
1823 ,hr_soft_coding_keyflex scl
1824 WHERE asg.assignment_id = p_assignment_id
1825 AND asg.soft_coding_keyflex_id =
1826 scl.soft_coding_keyflex_id
1827 AND p_date_earned BETWEEN asg.effective_start_date
1828 AND asg.effective_end_date) x
1829 WHERE o1.business_group_id = l_business_group_id
1830 AND hoi1.organization_id = o1.organization_id
1831 AND hoi1.organization_id = x.org_id
1832 AND hoi1.org_information1 = 'SE_LOCAL_UNIT'
1833 AND hoi1.org_information_context = 'CLASS'
1834 AND o1.organization_id = hoi2.org_information1
1835 AND hoi2.org_information_context = 'SE_LOCAL_UNITS'
1836 AND hoi2.organization_id = hoi3.organization_id
1837 AND hoi3.org_information_context = 'CLASS'
1838 AND hoi3.org_information1 = 'HR_LEGAL_EMPLOYER'
1839 AND hoi3.organization_id = hoi4.organization_id
1840 AND hoi4.org_information_context = 'SE_LE_HOLIDAY_PAY_DETAILS'
1841 AND hoi4.org_information1 IS NOT NULL;
1842 BEGIN
1843 SELECT papf.business_group_id
1844 ,papf.person_id
1845 ,segment9
1846 INTO l_business_group_id
1847 ,l_person_id
1848 ,l_working_perc
1849 FROM per_all_assignments_f paaf
1850 ,per_all_people_f papf
1851 ,hr_soft_coding_keyflex hsck
1852 WHERE paaf.assignment_id = p_assignment_id --15381
1853 AND paaf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
1854 AND papf.person_id = paaf.person_id
1855 AND p_date_earned BETWEEN paaf.effective_start_date
1856 AND paaf.effective_end_date
1857 AND p_date_earned BETWEEN papf.effective_start_date
1858 AND papf.effective_end_date;
1859
1860 l_year := get_end_year (p_date_earned, p_tax_unit_id);
1861
1862 OPEN csr_earning_year;
1863
1864 FETCH csr_earning_year
1865 INTO l_end_month;
1866
1867 CLOSE csr_earning_year;
1868
1869 --l_end_date:=last_day(to_date('01/'||l_end_month||'/'||l_year,'dd/mm/yyyy'));
1870
1871 /*l_status_return:=get_assg_status(p_tax_unit_id,p_assignment_id,p_pay_proc_period_start_date,p_pay_proc_period_end_date
1872 /*,l_termination_date);*/
1873 /*IF l_status_return='T' then*/
1874 l_worked_days_year:= (p_termination_date - p_earn_end_date + 1);
1875 l_days_year:=(add_months(p_earn_end_date,12) - p_earn_end_date );
1876
1877 /* ELSE
1878 l_days_year:=0;
1879 END IF;*/
1880 OPEN csr_assignment_entitlement;
1881
1882 FETCH csr_assignment_entitlement
1883 INTO l_assignment_entitlement;
1884
1885 CLOSE csr_assignment_entitlement;
1886
1887 OPEN csr_person_entitlement;
1888
1889 FETCH csr_person_entitlement
1890 INTO l_person_entitlement;
1891
1892 CLOSE csr_person_entitlement;
1893
1894 OPEN csr_legal_employer_entitlement;
1895
1896 FETCH csr_legal_employer_entitlement
1897 INTO l_legal_entitlement;
1898
1899 CLOSE csr_legal_employer_entitlement;
1900
1901 l_gen_entitlement :=
1902 NVL (l_assignment_entitlement
1903 ,NVL (l_person_entitlement, l_legal_entitlement)
1904 );
1905
1906 FOR csr_category IN csr_attendance_category_id
1907 LOOP
1908 l_attendance_category_id := csr_category.attendance_category_id;
1909 pay_balance_pkg.set_context ('SOURCE_TEXT'
1910 ,csr_category.attendance_category_id
1911 );
1912 pay_balance_pkg.set_context ('ASSIGNMENT_ACTION_ID'
1913 ,p_assignment_action_id
1914 );
1915 pay_balance_pkg.set_context ('TAX_UNIT_ID', p_tax_unit_id);
1916
1917 -- pay_balance_pkg.set_context('DATE_EARNED',p_date_earned);
1918 --OPEN csr_Get_Defined_Balance_Id( 'TOTAL_ABSENCE_DAYS_HOLIDAY_PAY_ABC_PER_YTD');
1919 --OPEN csr_Get_Defined_Balance_Id( 'TOTAL_ABSENCE_DAYS_HOLIDAY_PAY_ABS_CAT_ASG_HY_YTD');
1920 OPEN csr_get_defined_balance_id
1921 ('TOTAL_ABSENCE_DAYS_HOLIDAY_PAY_ASG_LE_ABS_CAT_HY_YEAR');
1922
1923 FETCH csr_get_defined_balance_id
1924 INTO lr_get_defined_balance_id;
1925
1926 CLOSE csr_get_defined_balance_id;
1927
1928 /*l_value :=to_char(pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>lr_Get_Defined_Balance_Id,
1929 P_ASSIGNMENT_ACTION_ID =>p_assignment_action_id ) );*/
1930 l_value :=
1931 pay_balance_pkg.get_value
1932 (p_defined_balance_id => lr_get_defined_balance_id
1933 ,p_assignment_action_id => p_assignment_action_id
1934 ,p_tax_unit_id => p_tax_unit_id
1935 ,p_jurisdiction_code => NULL
1936 ,p_source_id => NULL
1937 ,p_source_text => csr_category.attendance_category_id
1938 ,p_tax_group => NULL
1939 ,p_date_earned => p_date_earned
1940 );
1941
1942 OPEN csr_generate_max_days;
1943
1944 FETCH csr_generate_max_days
1945 INTO l_generate
1946 ,l_max_days;
1947
1948 CLOSE csr_generate_max_days;
1949
1950 /* If generate is Y then value greater than the max is considered as absence, else whole value */
1951 IF l_generate = 'Y'
1952 THEN
1953 IF l_value > l_max_days
1954 THEN
1955 l_absence_days := l_absence_days + (l_value - l_max_days);
1956 ELSE
1957 l_absence_days := l_absence_days; -- + l_value;
1958 END IF;
1959 ELSE
1960 l_absence_days := l_absence_days + l_value;
1961 END IF;
1962 END LOOP;
1963
1964 /*IF l_absence_days IS NULL or l_absence_days=0 THEN
1965
1966 l_paid_holiday_days:=25;
1967 l_unpaid_holiday_days:=0;
1968 p_total_working_days:=l_days_year;
1969 ELSE*/
1970 /*l_paid_holiday_days :=
1971 TRUNC ( 0.01
1972 * l_working_perc
1973 * ((l_days_year - l_absence_days) / l_days_year)
1974 * l_gen_entitlement
1975 );
1976
1977 IF ( 0.01
1978 * l_working_perc
1979 * ((l_days_year - l_absence_days) / l_days_year)
1980 * l_gen_entitlement
1981 ) > l_paid_holiday_days
1982 THEN
1983 l_paid_holiday_days := l_paid_holiday_days + 1;
1984 END IF;*/
1985 l_paid_holiday_days:=ceil(((l_worked_days_year-l_absence_days)/l_days_year)*l_gen_entitlement) ;
1986 l_paid_holiday_days := LEAST (l_paid_holiday_days, l_gen_entitlement);
1987 /* l_unpaid_holiday_days:=l_gen_entitlement-l_paid_holiday_days;*/
1988 /*p_total_working_days:=(l_days_year-l_absence_days);*/
1989
1990 /* END IF;*/
1991 p_paid_holiday_days := l_paid_holiday_days;
1992 /*p_unpaid_holiday_days:=l_unpaid_holiday_days;*/
1993 RETURN 0;
1994 END compensation_entitlement;
1995
1996 FUNCTION get_sickness_days (
1997 p_assignment_action_id IN NUMBER
1998 ,p_assignment_id IN NUMBER
1999 ,p_tax_unit_id IN NUMBER
2000 ,p_date_earned IN DATE
2001 )
2002 RETURN NUMBER
2003 IS
2004 l_termination_date DATE;
2005 l_year NUMBER;
2006 l_end_month CHAR (2);
2007 l_end_date DATE;
2008 l_status_return CHAR (1);
2009 l_days_year NUMBER;
2010 lr_get_defined_balance_id NUMBER;
2011 l_generate CHAR (1);
2012 l_max_days NUMBER;
2013 l_value NUMBER;
2014 l_absence_days NUMBER := 0;
2015 l_attendance_category_id VARCHAR2 (30);
2016 l_business_group_id NUMBER;
2017 l_paid_holiday_days NUMBER;
2018 l_assignment_entitlement NUMBER;
2019 l_person_entitlement NUMBER;
2020 l_legal_entitlement NUMBER;
2021 l_gen_entitlement NUMBER;
2022 l_person_id NUMBER;
2023
2024 CURSOR csr_generate_max_days
2025 IS
2026 SELECT hoi4.org_information2
2027 ,hoi4.org_information3
2028 FROM hr_organization_units o1
2029 ,hr_organization_information hoi1
2030 ,hr_organization_information hoi2
2031 ,hr_organization_information hoi3
2032 ,hr_organization_information hoi4
2033 , (SELECT TRIM (scl.segment2) AS org_id
2034 FROM per_all_assignments_f asg
2035 ,hr_soft_coding_keyflex scl
2036 WHERE asg.assignment_id = p_assignment_id
2037 AND asg.soft_coding_keyflex_id =
2038 scl.soft_coding_keyflex_id
2039 AND p_date_earned BETWEEN asg.effective_start_date
2040 AND asg.effective_end_date) x
2041 WHERE o1.business_group_id = l_business_group_id
2042 AND hoi1.organization_id = o1.organization_id
2043 AND hoi1.organization_id = x.org_id
2044 --AND hoi1.org_information1 = 'SE_LOCAL_UNIT'
2045 AND hoi1.org_information_context = 'CLASS'
2046 AND o1.organization_id = hoi2.org_information1
2047 AND hoi2.org_information_context = 'SE_LOCAL_UNITS'
2048 AND hoi2.organization_id = hoi3.organization_id
2049 AND hoi3.org_information_context = 'CLASS'
2050 AND hoi3.org_information1 = 'HR_LEGAL_EMPLOYER'
2051 AND hoi3.organization_id = hoi4.organization_id
2052 AND hoi4.org_information_context = 'SE_ABSENCE_CATEGORY_LIMIT'
2053 AND hoi4.org_information1 IS NOT NULL
2054 AND hoi4.org_information1 = 'S';
2055
2056 CURSOR csr_earning_year
2057 IS
2058 SELECT SUBSTR (hoi2.org_information2, 4, 2)
2059 FROM hr_organization_units o1
2060 ,hr_organization_information hoi1
2061 ,hr_organization_information hoi2
2062 WHERE hoi1.organization_id = o1.organization_id
2063 AND hoi1.organization_id = p_tax_unit_id
2064 AND hoi1.org_information_context = 'CLASS'
2065 AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
2066 AND hoi1.organization_id = hoi2.organization_id
2067 AND hoi2.org_information_context = 'SE_HOLIDAY_YEAR_DEFN'
2068 AND hoi2.org_information1 IS NOT NULL;
2069
2070 CURSOR csr_get_defined_balance_id (
2071 csr_v_balance_name ff_database_items.user_name%TYPE
2072 )
2073 IS
2074 SELECT ue.creator_id
2075 FROM ff_user_entities ue
2076 ,ff_database_items di
2080 AND ue.business_group_id IS NULL
2077 WHERE di.user_name = csr_v_balance_name
2078 AND ue.user_entity_id = di.user_entity_id
2079 AND ue.legislation_code = 'SE'
2081 AND ue.creator_type = 'B';
2082
2083 CURSOR csr_assignment_entitlement
2084 IS
2085 SELECT aei_information1
2086 FROM per_assignment_extra_info
2087 WHERE assignment_id = p_assignment_id
2088 AND information_type = 'SE_ASSIGN_HOLIDAY_PAY_DETAILS';
2089
2090 CURSOR csr_person_entitlement
2091 IS
2092 SELECT pei_information1
2093 FROM per_people_extra_info
2094 WHERE person_id = l_person_id
2095 AND information_type = 'SE_PERSON_HOLIDAY_PAY_DETAILS';
2096
2097 CURSOR csr_legal_employer_entitlement
2098 IS
2099 SELECT hoi4.org_information1
2100 FROM hr_organization_units o1
2101 ,hr_organization_information hoi1
2102 ,hr_organization_information hoi2
2103 ,hr_organization_information hoi3
2104 ,hr_organization_information hoi4
2105 , (SELECT TRIM (scl.segment2) AS org_id
2106 FROM per_all_assignments_f asg
2107 ,hr_soft_coding_keyflex scl
2108 WHERE asg.assignment_id = p_assignment_id
2109 AND asg.soft_coding_keyflex_id =
2110 scl.soft_coding_keyflex_id
2111 AND p_date_earned BETWEEN asg.effective_start_date
2112 AND asg.effective_end_date) x
2113 WHERE o1.business_group_id = l_business_group_id
2114 AND hoi1.organization_id = o1.organization_id
2115 AND hoi1.organization_id = x.org_id
2116 AND hoi1.org_information1 = 'SE_LOCAL_UNIT'
2117 AND hoi1.org_information_context = 'CLASS'
2118 AND o1.organization_id = hoi2.org_information1
2119 AND hoi2.org_information_context = 'SE_LOCAL_UNITS'
2120 AND hoi2.organization_id = hoi3.organization_id
2121 AND hoi3.org_information_context = 'CLASS'
2122 AND hoi3.org_information1 = 'HR_LEGAL_EMPLOYER'
2123 AND hoi3.organization_id = hoi4.organization_id
2124 AND hoi4.org_information_context = 'SE_LE_HOLIDAY_PAY_DETAILS'
2125 AND hoi4.org_information1 IS NOT NULL;
2126 BEGIN
2127 --l_attendance_category_id:='V';
2128 pay_balance_pkg.set_context ('SOURCE_TEXT', 'S');
2129 pay_balance_pkg.set_context ('ASSIGNMENT_ACTION_ID'
2130 ,p_assignment_action_id
2131 );
2132 pay_balance_pkg.set_context ('TAX_UNIT_ID', p_tax_unit_id);
2133
2134 --pay_balance_pkg.set_context('DATE_EARNED',p_date_earned);
2135 --OPEN csr_Get_Defined_Balance_Id( 'TOTAL_ABSENCE_DAYS_HOLIDAY_PAY_ABC_PER_YTD');
2136 --OPEN csr_Get_Defined_Balance_Id( 'TOTAL_ABSENCE_DAYS_HOLIDAY_PAY_ABS_CAT_ASG_YTD');
2137 OPEN csr_get_defined_balance_id
2138 ('TOTAL_ABSENCE_DAYS_HOLIDAY_PAY_ASG_LE_ABS_CAT_EARN_YEAR');
2139
2140 FETCH csr_get_defined_balance_id
2141 INTO lr_get_defined_balance_id;
2142
2143 CLOSE csr_get_defined_balance_id;
2144
2145 /*l_value :=to_char(pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>lr_Get_Defined_Balance_Id,
2146 P_ASSIGNMENT_ACTION_ID =>p_assignment_action_id ) );*/
2147 l_value :=
2148 pay_balance_pkg.get_value
2149 (p_defined_balance_id => lr_get_defined_balance_id
2150 ,p_assignment_action_id => p_assignment_action_id
2151 ,p_tax_unit_id => p_tax_unit_id
2152 ,p_jurisdiction_code => NULL
2153 ,p_source_id => NULL
2154 ,p_source_text => 'S'
2155 ,p_tax_group => NULL
2156 ,p_date_earned => p_date_earned
2157 );
2158
2159 OPEN csr_generate_max_days;
2160
2161 FETCH csr_generate_max_days
2162 INTO l_generate
2163 ,l_max_days;
2164
2165 CLOSE csr_generate_max_days;
2166
2167 /* If generate is Y then value greater than the max is considered as absence, else whole value */
2168 IF l_generate = 'Y'
2169 THEN
2170 IF l_value > l_max_days
2171 THEN
2172 l_absence_days := l_absence_days + (l_value - l_max_days);
2173 ELSE
2174 l_absence_days := l_absence_days; -- + l_value;
2175 END IF;
2176 ELSE
2177 l_absence_days := l_absence_days + l_value;
2178 END IF;
2179
2180 RETURN l_absence_days;
2181 END get_sickness_days;
2182
2183 FUNCTION check_advance_holiday_limit (
2184 p_assignment_id IN NUMBER
2185 ,p_date_earned IN DATE
2186 )
2187 RETURN VARCHAR2
2188 IS
2189 l_advance_holiday_year_limit NUMBER;
2190 l_assignment_start_date DATE;
2191 l_months_worked NUMBER;
2192
2193 CURSOR csr_global_value (csr_v_effective_date DATE)
2194 IS
2195 SELECT global_value
2196 FROM ff_globals_f fgf
2197 WHERE csr_v_effective_date BETWEEN effective_start_date
2198 AND effective_end_date
2199 AND GLOBAL_NAME = 'SE_ADVANCE_HOLIDAY_YEAR_LIMIT';
2200
2201 CURSOR csr_assignment_start (csr_v_assignment_id NUMBER)
2202 IS
2203 SELECT MIN (effective_start_date)
2204 FROM per_all_assignments_f paaf
2205 WHERE paaf.assignment_id = csr_v_assignment_id;
2206 BEGIN
2207 OPEN csr_assignment_start (p_assignment_id);
2208
2209 FETCH csr_assignment_start
2210 INTO l_assignment_start_date;
2211
2212 CLOSE csr_assignment_start;
2213
2214 --dbms_output.put_line(' l_assignment_start_date'||l_assignment_start_date);
2215 l_months_worked :=
2216 MONTHS_BETWEEN (p_date_earned, l_assignment_start_date);
2217
2218 -- DBMS_OUTPUT.put_line(' l_months_worked'||l_months_worked);
2219 OPEN csr_global_value (p_date_earned);
2220
2221 FETCH csr_global_value
2222 INTO l_advance_holiday_year_limit;
2223
2224 CLOSE csr_global_value;
2225
2226 --DBMS_OUTPUT.put_line(' l_months_worked'||l_months_worked);
2227 l_advance_holiday_year_limit := l_advance_holiday_year_limit * 12;
2228
2229 --DBMS_OUTPUT.put_line(' l_months_worked'||l_months_worked);
2230 /* check whether he has worked for more than the year for advance year limit */
2231 IF l_months_worked >= l_advance_holiday_year_limit
2232 THEN
2233 RETURN 'Y';
2234 ELSE
2235 RETURN 'N';
2236 END IF;
2237 END check_advance_holiday_limit;
2238
2239 FUNCTION get_cy_start_date (
2240 p_assignment_id IN NUMBER
2241 ,p_effective_date IN DATE
2242 ,p_business_group_id IN NUMBER
2243 ,p_tax_unit_id IN NUMBER
2244 ,p_payroll_start_date IN DATE
2245 ,p_payroll_end_date IN DATE
2246 ,p_cy_start_date OUT NOCOPY DATE
2247 ,p_cy_end_date OUT NOCOPY DATE
2248 )
2249 RETURN VARCHAR2
2250 IS
2251 l_business_group_id NUMBER;
2252 l_start_month NUMBER;
2253 l_end_month NUMBER;
2254 l_cy_start_date DATE;
2255 l_cy_end_date DATE;
2256 l_assignment_start DATE;
2257 l_year NUMBER;
2258 l_payroll_id NUMBER;
2259 l_min_payroll_start_date DATE;
2260
2261 CURSOR csr_earning_year
2262 IS
2263 SELECT SUBSTR (hoi4.org_information1, 4, 2)
2264 ,SUBSTR (hoi4.org_information2, 4, 2)
2265 FROM hr_organization_units o1
2266 ,hr_organization_information hoi3
2267 ,hr_organization_information hoi4
2268 WHERE o1.business_group_id = l_business_group_id
2269 AND o1.organization_id = hoi3.organization_id
2270 AND hoi3.organization_id = p_tax_unit_id
2271 AND hoi3.org_information_context = 'CLASS'
2272 AND hoi3.org_information1 = 'HR_LEGAL_EMPLOYER'
2273 AND hoi3.organization_id = hoi4.organization_id
2274 AND hoi4.org_information_context = 'SE_HOLIDAY_YEAR_DEFN'
2275 AND hoi4.org_information1 IS NOT NULL;
2276
2277 CURSOR csr_assignment_start
2278 IS
2279 SELECT MIN (effective_start_date)
2280 FROM per_all_assignments_f
2281 WHERE assignment_id = p_assignment_id;
2282
2283 CURSOR csr_payroll_id
2284 IS
2285 SELECT payroll_id
2286 FROM per_all_assignments_f
2287 WHERE assignment_id = p_assignment_id
2288 AND p_effective_date BETWEEN effective_start_date
2289 AND effective_end_date;
2290
2291 CURSOR csr_first_payroll_start_date (v_payroll_id NUMBER, v_date DATE)
2292 IS
2293 SELECT MIN (start_date)
2294 FROM per_time_periods
2295 WHERE payroll_id = v_payroll_id AND start_date >= v_date;
2296 BEGIN
2297 l_business_group_id := p_business_group_id;
2298
2299 OPEN csr_earning_year;
2300
2301 FETCH csr_earning_year
2302 INTO l_start_month
2303 ,l_end_month;
2304
2305 CLOSE csr_earning_year;
2306
2307 IF l_start_month IS NULL AND l_end_month IS NULL
2308 THEN
2309 RETURN 'N';
2310 ELSE
2311 /* Logic for Earning Year is from Jan-Dec */
2312 IF l_start_month = '01' AND l_end_month = '12'
2313 THEN
2314 l_year := TO_NUMBER (TO_CHAR (p_effective_date, 'YYYY'));
2315 ELSE
2316 IF TO_NUMBER (TO_CHAR (p_effective_date, 'MM')) <
2317 TO_NUMBER (l_start_month)
2318 THEN
2319 l_year := TO_NUMBER (TO_CHAR (p_effective_date, 'YYYY') - 1);
2320 ELSE
2321 l_year := TO_NUMBER (TO_CHAR (p_effective_date, 'YYYY'));
2322 END IF;
2323 END IF;
2324
2325 -- get the start date of the Holiday year
2326 l_cy_start_date :=
2327 TO_DATE ('01/' || l_start_month || '/' || l_year, 'dd/mm/yyyy');
2328
2329 OPEN csr_assignment_start;
2330
2331 FETCH csr_assignment_start
2332 INTO l_assignment_start;
2333
2334 CLOSE csr_assignment_start;
2335
2336 l_cy_start_date := GREATEST (l_assignment_start, l_cy_start_date);
2337 l_cy_end_date :=
2338 TO_DATE ('01/' || l_start_month || '/' || l_year, 'dd/mm/yyyy')
2339 + 360;
2340 --l_cy_end_date:=least(last_day(l_cy_end_date),p_payroll_end_date);
2341 l_cy_end_date := LAST_DAY (l_cy_end_date);
2342 p_cy_start_date := l_cy_start_date;
2343 p_cy_end_date := l_cy_end_date;
2344
2345 OPEN csr_payroll_id;
2346
2347 FETCH csr_payroll_id
2348 INTO l_payroll_id;
2349
2350 CLOSE csr_payroll_id;
2351
2352 -- hr_utility.trace(' In l_payroll_id => ' || l_payroll_id);
2353 OPEN csr_first_payroll_start_date (l_payroll_id, p_cy_start_date);
2354
2355 FETCH csr_first_payroll_start_date
2356 INTO l_min_payroll_start_date;
2357
2358 CLOSE csr_first_payroll_start_date;
2359
2360 -- hr_utility.trace(' p_cy_start_date => ' || p_cy_start_date);
2361 -- hr_utility.trace(' p_cy_end_date => ' || p_cy_end_date);
2362 -- hr_utility.trace(' l_min_payroll_start_date => ' || l_min_payroll_start_date);
2363
2364 --checking the l_earning_end_date+1 lies between the payroll periods for first payroll
2365 --period after earning year
2366 IF (l_min_payroll_start_date = p_payroll_start_date)
2367 THEN
2368 --IF (p_effective_date>=l_earning_start_date) AND (p_effective_date<=l_earning_end_date) THEN
2369 /* check whether the person has the assignment in the earning year */
2370 -- hr_utility.trace(' retuning => ' || 'FIRST');
2371 RETURN 'FIRST';
2372 ELSE
2373 -- hr_utility.trace(' retuning => ' || 'OTHERS');
2374 RETURN 'OTHERS';
2375 END IF;
2376 --checking the earning_end_date lies between payroll_start and end_date, to find the last payroll
2377 --period
2378 /*ELSIF (p_pay_start_date>=l_earning_end_date) AND (l_earning_end_date<= p_pay_end_date) THEN
2379 RETURN 'L';*/
2380 --ELSE
2381 -- return 'N';
2382 -- end if;
2383 END IF;
2384 EXCEPTION
2385 WHEN OTHERS
2386 THEN
2387 RETURN 'N';
2388 END get_cy_start_date;
2389
2390 FUNCTION get_paid_days_limit (
2391 p_assignment_id IN NUMBER
2392 ,p_effective_date IN DATE
2393 ,p_tax_unit_id IN NUMBER
2394 )
2395 RETURN NUMBER
2396 IS
2397 l_person_id NUMBER;
2398 l_business_group_id NUMBER;
2399 l_assignment_entitlement NUMBER;
2400 l_person_entitlement NUMBER;
2401 l_legal_entitlement NUMBER;
2402 l_paid_holiday_days NUMBER;
2403
2404 CURSOR csr_assignment_entitlement
2405 IS
2406 SELECT aei_information1
2407 FROM per_assignment_extra_info
2408 WHERE assignment_id = p_assignment_id
2409 AND information_type = 'SE_ASSIGN_HOLIDAY_PAY_DETAILS';
2410
2411 CURSOR csr_person_entitlement
2412 IS
2413 SELECT pei_information1
2414 FROM per_people_extra_info
2415 WHERE person_id = l_person_id
2416 AND information_type = 'SE_PERSON_HOLIDAY_PAY_DETAILS';
2417
2418 CURSOR csr_legal_employer_entitlement
2419 IS
2420 SELECT hoi4.org_information1
2421 FROM hr_organization_units o1
2422 ,hr_organization_information hoi3
2423 ,hr_organization_information hoi4
2424 WHERE o1.business_group_id = l_business_group_id
2425 AND o1.organization_id = hoi3.organization_id
2429 AND hoi3.organization_id = hoi4.organization_id
2426 AND hoi3.organization_id = p_tax_unit_id
2427 AND hoi3.org_information_context = 'CLASS'
2428 AND hoi3.org_information1 = 'HR_LEGAL_EMPLOYER'
2430 AND hoi4.org_information_context = 'SE_LE_HOLIDAY_PAY_DETAILS'
2431 AND hoi4.org_information1 IS NOT NULL;
2432
2433 CURSOR csr_get_details
2434 IS
2435 SELECT papf.business_group_id
2436 ,papf.person_id
2437 FROM per_all_assignments_f paaf
2438 ,per_all_people_f papf
2439 ,hr_soft_coding_keyflex hsck
2440 WHERE paaf.assignment_id = p_assignment_id --15381
2441 --AND paaf.soft_coding_keyflex_id=hsck.soft_coding_keyflex_id
2442 AND papf.person_id = paaf.person_id
2443 AND p_effective_date BETWEEN paaf.effective_start_date
2444 AND paaf.effective_end_date
2445 AND p_effective_date BETWEEN papf.effective_start_date
2446 AND papf.effective_end_date;
2447 BEGIN
2448 l_paid_holiday_days := 0;
2449
2450 OPEN csr_get_details;
2451
2452 FETCH csr_get_details
2453 INTO l_business_group_id
2454 ,l_person_id;
2455
2456 CLOSE csr_get_details;
2457
2458 OPEN csr_assignment_entitlement;
2459
2460 FETCH csr_assignment_entitlement
2461 INTO l_assignment_entitlement;
2462
2463 CLOSE csr_assignment_entitlement;
2464
2465 OPEN csr_person_entitlement;
2466
2467 FETCH csr_person_entitlement
2468 INTO l_person_entitlement;
2469
2470 CLOSE csr_person_entitlement;
2471
2472 OPEN csr_legal_employer_entitlement;
2473
2474 FETCH csr_legal_employer_entitlement
2475 INTO l_legal_entitlement;
2476
2477 CLOSE csr_legal_employer_entitlement;
2478
2479 l_paid_holiday_days :=
2480 NVL (l_assignment_entitlement
2481 ,NVL (l_person_entitlement, l_legal_entitlement)
2482 );
2483 RETURN l_paid_holiday_days;
2484 END get_paid_days_limit;
2485
2486 FUNCTION get_cy_paid_unpaid_days (
2487 p_assignment_id IN NUMBER
2488 ,p_effective_date IN DATE
2489 ,p_assignment_action_id IN NUMBER
2490 ,p_tax_unit_id IN NUMBER
2491 ,p_cy_start_date IN DATE
2492 ,p_cy_end_date IN DATE
2493 ,p_paid_holiday_days OUT NOCOPY NUMBER
2494 ,p_unpaid_holiday_days OUT NOCOPY NUMBER
2495 --p_total_working_days OUT nocopy NUMBER
2496 )
2497 RETURN NUMBER
2498 IS
2499 l_person_id NUMBER;
2500 l_business_group_id NUMBER;
2501 l_assignment_entitlement NUMBER;
2502 l_person_entitlement NUMBER;
2503 l_legal_entitlement NUMBER;
2504 l_gen_entitlement NUMBER;
2505 lr_get_defined_balance_id NUMBER;
2506 l_value NUMBER;
2507 l_generate VARCHAR (1);
2508 l_max_days NUMBER;
2509 l_days_year NUMBER;
2510 l_work_days_year NUMBER;
2511 l_absence_days NUMBER := 0;
2512 l_paid_holiday_days NUMBER;
2513 l_unpaid_holiday_days NUMBER;
2514 l_saved_days NUMBER;
2515 l_assignment_start DATE;
2516 l_attendance_category_id VARCHAR2 (30);
2517 l_working_perc NUMBER;
2518 l_days NUMBER;
2519
2520 CURSOR csr_assignment_entitlement
2521 IS
2522 SELECT aei_information1
2523 FROM per_assignment_extra_info
2524 WHERE assignment_id = p_assignment_id
2525 AND information_type = 'SE_ASSIGN_HOLIDAY_PAY_DETAILS';
2526
2527 CURSOR csr_person_entitlement
2528 IS
2529 SELECT pei_information1
2530 FROM per_people_extra_info
2531 WHERE person_id = l_person_id
2532 AND information_type = 'SE_PERSON_HOLIDAY_PAY_DETAILS';
2533
2534 CURSOR csr_legal_employer_entitlement
2535 IS
2536 SELECT hoi4.org_information1
2537 FROM hr_organization_units o1
2538 ,hr_organization_information hoi3
2539 ,hr_organization_information hoi4
2540 WHERE o1.business_group_id = l_business_group_id
2541 AND o1.organization_id = hoi3.organization_id
2542 AND hoi3.organization_id = p_tax_unit_id
2543 AND hoi3.org_information_context = 'CLASS'
2544 AND hoi3.org_information1 = 'HR_LEGAL_EMPLOYER'
2545 AND hoi3.organization_id = hoi4.organization_id
2546 AND hoi4.org_information_context = 'SE_LE_HOLIDAY_PAY_DETAILS'
2547 AND hoi4.org_information1 IS NOT NULL;
2548
2549 CURSOR csr_attendance_category_id
2550 IS
2551 SELECT DISTINCT eev1.screen_entry_value attendance_category_id
2552 FROM per_all_assignments_f asg1
2553 ,pay_element_links_f el
2554 ,pay_element_types_f et
2555 ,pay_input_values_f iv1
2556 ,pay_element_entries_f ee
2557 ,pay_element_entry_values_f eev1
2558 WHERE asg1.assignment_id = p_assignment_id
2562 AND iv1.element_type_id = et.element_type_id
2559 AND p_effective_date BETWEEN asg1.effective_start_date
2560 AND asg1.effective_end_date
2561 AND et.legislation_code = 'SE'
2563 AND iv1.NAME = 'Absence Category' --l_inp_val_name
2564 AND el.business_group_id = asg1.business_group_id
2565 AND el.element_type_id = et.element_type_id
2566 AND ee.assignment_id = asg1.assignment_id
2567 AND ee.element_link_id = el.element_link_id
2568 AND eev1.element_entry_id = ee.element_entry_id
2569 AND eev1.input_value_id = iv1.input_value_id
2570 AND ee.effective_start_date <= p_cy_end_date
2571 AND ee.effective_end_date >= p_cy_start_date
2572 AND eev1.effective_start_date <= p_cy_end_date
2573 AND eev1.effective_end_date >= p_cy_start_date
2574 AND et.element_name NOT IN
2575 ('Advance Holiday Details', 'Advance Holiday Pay');
2576
2577 CURSOR csr_get_defined_balance_id (
2578 csr_v_balance_name ff_database_items.user_name%TYPE
2579 )
2580 IS
2581 SELECT ue.creator_id
2582 FROM ff_user_entities ue
2583 ,ff_database_items di
2584 WHERE di.user_name = csr_v_balance_name
2585 AND ue.user_entity_id = di.user_entity_id
2586 AND ue.legislation_code = 'SE'
2587 AND ue.business_group_id IS NULL
2588 AND ue.creator_type = 'B';
2589
2590 CURSOR csr_generate_max_days
2591 IS
2592 SELECT hoi4.org_information2
2593 ,hoi4.org_information3
2594 FROM hr_organization_units o1
2595 ,hr_organization_information hoi3
2596 ,hr_organization_information hoi4
2597 WHERE o1.business_group_id = l_business_group_id
2598 AND hoi3.organization_id = o1.organization_id
2599 AND hoi3.organization_id = p_tax_unit_id
2600 AND hoi3.org_information_context = 'CLASS'
2601 AND hoi3.org_information1 = 'HR_LEGAL_EMPLOYER'
2602 AND hoi3.organization_id = hoi4.organization_id
2603 AND hoi4.org_information_context = 'SE_ABSENCE_CATEGORY_LIMIT'
2604 AND hoi4.org_information1 IS NOT NULL
2605 AND hoi4.org_information1 = l_attendance_category_id;
2606
2607 CURSOR csr_assignment_start
2608 IS
2609 SELECT MIN (effective_start_date)
2610 FROM per_all_assignments_f
2611 WHERE assignment_id = p_assignment_id;
2612 BEGIN
2613 SELECT papf.business_group_id
2614 ,papf.person_id
2615 ,segment9
2616 INTO l_business_group_id
2617 ,l_person_id
2618 ,l_working_perc
2619 FROM per_all_assignments_f paaf
2620 ,per_all_people_f papf
2621 ,hr_soft_coding_keyflex hsck
2622 WHERE paaf.assignment_id = p_assignment_id
2623 AND paaf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
2624 AND papf.person_id = paaf.person_id
2625 AND p_effective_date BETWEEN paaf.effective_start_date
2626 AND paaf.effective_end_date
2627 AND p_effective_date BETWEEN papf.effective_start_date
2628 AND papf.effective_end_date;
2629
2630 -- hr_utility.trace(' l_person_id => ' || l_person_id);
2631
2632 /* To get the entitlement */
2633 OPEN csr_assignment_entitlement;
2634
2635 FETCH csr_assignment_entitlement
2636 INTO l_assignment_entitlement;
2637
2638 CLOSE csr_assignment_entitlement;
2639
2640 -- hr_utility.trace(' l_assignment_entitlement => ' || l_assignment_entitlement);
2641 OPEN csr_person_entitlement;
2642
2643 FETCH csr_person_entitlement
2644 INTO l_person_entitlement;
2645
2646 CLOSE csr_person_entitlement;
2647
2648 -- hr_utility.trace(' l_person_entitlement => ' || l_person_entitlement);
2649 OPEN csr_legal_employer_entitlement;
2650
2651 FETCH csr_legal_employer_entitlement
2652 INTO l_legal_entitlement;
2653
2654 CLOSE csr_legal_employer_entitlement;
2655
2656 -- hr_utility.trace(' l_legal_entitlement => ' || l_legal_entitlement);
2657 l_gen_entitlement :=
2658 NVL (l_assignment_entitlement
2659 ,NVL (l_person_entitlement, l_legal_entitlement)
2660 );
2661
2662 -- hr_utility.trace(' l_gen_entitlement => ' || l_gen_entitlement);
2663 OPEN csr_assignment_start;
2664
2665 FETCH csr_assignment_start
2666 INTO l_assignment_start;
2667
2668 CLOSE csr_assignment_start;
2669
2670 -- hr_utility.trace(' l_assignment_start => ' || l_assignment_start);
2671
2672 /* After discussing with vinod, assignment start date should not be considered while
2673 calculating the number of days in a year, entitlement calculation, changing the same on 20-sep-2006 */
2674 l_work_days_year :=
2675 (p_cy_end_date - (GREATEST (p_cy_start_date, l_assignment_start)) + 1
2676 );
2677 l_days_year := (p_cy_end_date - p_cy_start_date + 1);
2678
2679 -- hr_utility.trace(' p_cy_start_date => ' || p_cy_start_date);
2680 -- hr_utility.trace(' p_cy_end_date => ' || p_cy_end_date);
2681
2682 -- hr_utility.trace(' l_work_days_year => ' || l_work_days_year);
2683 -- hr_utility.trace(' l_days_year => ' || l_days_year);
2687 pay_balance_pkg.set_context ('SOURCE_TEXT'
2684 FOR csr_category IN csr_attendance_category_id
2685 LOOP
2686 l_attendance_category_id := csr_category.attendance_category_id;
2688 ,csr_category.attendance_category_id
2689 );
2690 pay_balance_pkg.set_context ('ASSIGNMENT_ACTION_ID'
2691 ,p_assignment_action_id
2692 );
2693 pay_balance_pkg.set_context ('TAX_UNIT_ID', p_tax_unit_id);
2694
2695 OPEN csr_get_defined_balance_id
2696 ('TOTAL_ABSENCE_DAYS_HOLIDAY_PAY_ASG_LE_ABS_CAT_HY_YEAR');
2697
2698 FETCH csr_get_defined_balance_id
2699 INTO lr_get_defined_balance_id;
2700
2701 CLOSE csr_get_defined_balance_id;
2702
2703 l_value :=
2704 pay_balance_pkg.get_value
2705 (p_defined_balance_id => lr_get_defined_balance_id
2706 ,p_assignment_action_id => p_assignment_action_id
2707 ,p_tax_unit_id => p_tax_unit_id
2708 ,p_jurisdiction_code => NULL
2709 ,p_source_id => NULL
2710 ,p_source_text => csr_category.attendance_category_id
2711 ,p_tax_group => NULL
2712 ,p_date_earned => p_effective_date
2713 );
2714 l_generate := NULL;
2715 l_max_days := 0;
2716 OPEN csr_generate_max_days;
2717
2718 FETCH csr_generate_max_days
2719 INTO l_generate
2720 ,l_max_days;
2721
2722 CLOSE csr_generate_max_days;
2723
2724 -- hr_utility.trace(' csr_Category.Attendance_Category_Id => ' || csr_Category.Attendance_Category_Id);
2725 -- hr_utility.trace(' l_value => ' || l_value);
2726 -- hr_utility.trace(' l_max_days => ' || l_max_days);
2727 -- hr_utility.trace(' l_generate => ' || l_generate);
2728
2729 /* If generate is Y then value greater than the max is considered as absence, else whole value */
2730 IF l_generate = 'Y'
2731 THEN
2732 -- hr_utility.trace(' In Y Y Y => ' || l_absence_days);
2733 IF l_value > l_max_days
2734 THEN
2735 l_absence_days := l_absence_days + (l_value - l_max_days);
2736 ELSE
2737 l_absence_days := l_absence_days + l_value;
2738 END IF;
2739 -- hr_utility.trace(' OUT OUT Y Y Y => ' || l_absence_days);
2740 ELSIF l_generate IS NOT NULL
2741 THEN
2742 -- hr_utility.trace(' In No No No => ' || l_absence_days);
2743 l_absence_days := l_absence_days + l_value;
2744 -- hr_utility.trace(' OUT OUT No No No => ' || l_absence_days);
2745 END IF;
2746 END LOOP;
2747
2748 -- hr_utility.trace(' l_absence_days => ' || l_absence_days);
2749
2750 -- l_paid_holiday_days:=trunc(0.01*l_working_perc*((l_work_days_year-l_absence_days)/l_days_year)*l_gen_entitlement) ;
2751 l_paid_holiday_days :=
2752 CEIL ( ((l_work_days_year - l_absence_days) / l_days_year)
2753 * l_gen_entitlement
2754 );
2755 -- hr_utility.trace(' l_paid_holiday_days => ' || l_paid_holiday_days);
2756
2757 -- hr_utility.trace(' l_gen_entitlement => ' || l_gen_entitlement);
2758 l_unpaid_holiday_days := l_gen_entitlement - l_paid_holiday_days;
2759 -- p_total_working_days:=(l_work_days_year-l_absence_days);
2760 -- hr_utility.trace(' l_unpaid_holiday_days => ' || l_unpaid_holiday_days);
2761
2762 -- p_paid_holiday_days:=l_gen_entitlement - l_paid_holiday_days;
2763 p_paid_holiday_days := l_paid_holiday_days;
2764 p_unpaid_holiday_days := l_unpaid_holiday_days;
2765 -- hr_utility.trace(' p_paid_holiday_days => ' || p_paid_holiday_days);
2766 -- hr_utility.trace(' p_unpaid_holiday_days => ' || p_unpaid_holiday_days);
2767 RETURN 0;
2768 END get_cy_paid_unpaid_days;
2769
2770 FUNCTION get_earning_year (p_date_earned IN DATE, p_tax_unit_id IN NUMBER)
2771 RETURN NUMBER
2772 IS
2773 l_start_month CHAR (2);
2774 l_end_month CHAR (2);
2775
2776 CURSOR csr_earning_year
2777 IS
2778 SELECT SUBSTR (hoi2.org_information1, 4, 2)
2779 ,SUBSTR (hoi2.org_information2, 4, 2)
2780 FROM hr_organization_units o1
2781 ,hr_organization_information hoi1
2782 ,hr_organization_information hoi2
2783 WHERE hoi1.organization_id = o1.organization_id
2784 AND hoi1.organization_id = p_tax_unit_id --3134
2785 AND hoi1.org_information_context = 'CLASS'
2786 AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
2787 AND hoi1.organization_id = hoi2.organization_id
2788 AND hoi2.org_information_context = 'SE_HOLIDAY_YEAR_DEFN'
2789 AND hoi2.org_information1 IS NOT NULL;
2790 BEGIN
2791 OPEN csr_earning_year;
2792
2793 FETCH csr_earning_year
2794 INTO l_start_month
2795 ,l_end_month;
2796
2797 CLOSE csr_earning_year;
2798
2799 /* Logic for Earning Year is from Jan-Dec */
2800 IF l_start_month = '01' AND l_end_month = '12'
2801 THEN
2802 RETURN TO_NUMBER (TO_CHAR (p_date_earned, 'YYYY') - 1);
2803 ELSE
2804 IF TO_NUMBER (TO_CHAR (p_date_earned, 'MM')) <
2805 TO_NUMBER (l_start_month)
2806 THEN
2810 END IF;
2807 RETURN TO_NUMBER (TO_CHAR (p_date_earned, 'YYYY') - 1);
2808 ELSE
2809 RETURN TO_NUMBER (TO_CHAR (p_date_earned, 'YYYY'));
2811 END IF;
2812 END get_earning_year;
2813
2814 FUNCTION get_employee_category_type (
2815 p_asg_id IN NUMBER
2816 ,p_business_group_id IN NUMBER
2817 ,p_pay_proc_period_start_date IN DATE
2818 ,p_tax_unit_id IN NUMBER
2819 )
2820 RETURN VARCHAR2
2821 IS
2822 l_start_month NUMBER;
2823 l_end_month NUMBER;
2824 l_cy_start_date DATE;
2825 l_assignment_start DATE;
2826 l_year NUMBER;
2827 l_what_collar VARCHAR2 (50);
2828
2829 CURSOR csr_asg_employee_category (csr_v_effective_date DATE)
2830 IS
2831 SELECT employee_category
2832 FROM per_all_assignments_f
2833 WHERE assignment_id = p_asg_id
2834 AND csr_v_effective_date BETWEEN effective_start_date
2835 AND effective_end_date;
2836 BEGIN
2837 OPEN csr_asg_employee_category (p_pay_proc_period_start_date);
2838
2839 FETCH csr_asg_employee_category
2840 INTO l_what_collar;
2841
2842 CLOSE csr_asg_employee_category;
2843
2844 RETURN NVL(l_what_collar,'ORACLE_NO_COLLAR');
2845 END get_employee_category_type;
2846
2847 FUNCTION get_coincident_holiday_year (
2848 p_business_group_id IN NUMBER
2849 ,p_tax_unit_id IN NUMBER
2850 )
2851 RETURN VARCHAR2
2852 IS
2853 l_what_year VARCHAR2 (50);
2854
2855 CURSOR csr_get_concidental
2856 IS
2857 SELECT hoi4.org_information6
2858 FROM hr_organization_units o1
2859 ,hr_organization_information hoi3
2860 ,hr_organization_information hoi4
2861 WHERE o1.business_group_id = p_business_group_id
2862 AND o1.organization_id = hoi3.organization_id
2863 AND hoi3.organization_id = p_tax_unit_id
2864 AND hoi3.org_information_context = 'CLASS'
2865 AND hoi3.org_information1 = 'HR_LEGAL_EMPLOYER'
2866 AND hoi3.organization_id = hoi4.organization_id
2867 AND hoi4.org_information_context = 'SE_HOLIDAY_YEAR_DEFN'
2868 AND hoi4.org_information1 IS NOT NULL;
2869 BEGIN
2870 OPEN csr_get_concidental;
2871
2872 FETCH csr_get_concidental
2873 INTO l_what_year;
2874
2875 CLOSE csr_get_concidental;
2876
2877 RETURN l_what_year;
2878 END get_coincident_holiday_year;
2879
2880 FUNCTION get_min_assignment_start (p_assignment_id IN NUMBER)
2881 RETURN DATE
2882 IS
2883 l_return DATE;
2884 BEGIN
2885 SELECT MIN (effective_start_date)
2886 INTO l_return
2887 FROM per_all_assignments_f
2888 WHERE assignment_id = p_assignment_id;
2889
2890 RETURN l_return;
2891 END get_min_assignment_start;
2892
2893 FUNCTION part_time_employee (
2894 p_assignment_id IN NUMBER
2895 ,p_date_earned IN DATE
2896 ,p_full_time OUT NOCOPY NUMBER
2897 ,p_days_week OUT NOCOPY NUMBER
2898 )
2899 RETURN VARCHAR2
2900 IS
2901 l_days_week NUMBER;
2902 l_full_time NUMBER := 5;
2903
2904 CURSOR csr_part_time (
2905 csr_v_assignment_id NUMBER
2906 ,csr_v_effective_date DATE
2907 )
2908 IS
2909 SELECT nvl(TRUNC
2910 (fnd_number.canonical_to_number (segment13)),l_full_time)
2911 /* change this to the field which we are going to add for part time employee */
2912 FROM per_all_assignments_f paaf
2913 ,
2914 -- per_all_people_f papf,
2915 hr_soft_coding_keyflex hsck
2916 WHERE paaf.assignment_id = csr_v_assignment_id --15381
2917 AND paaf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
2918 --AND papf.person_id=paaf.person_id
2919 AND csr_v_effective_date BETWEEN paaf.effective_start_date
2920 AND paaf.effective_end_date;
2921 --AND p_effective_date BETWEEN papf.effective_start_date
2922 --AND papf.effective_end_date;
2923 BEGIN
2924 OPEN csr_part_time (p_assignment_id, p_date_earned);
2925
2926 FETCH csr_part_time
2927 INTO l_days_week;
2928
2929 CLOSE csr_part_time;
2930
2931 p_full_time := l_full_time;
2932 p_days_week := l_days_week;
2933
2934 IF l_days_week = l_full_time
2935 THEN
2936 RETURN 'N';
2937 ELSE
2938 RETURN 'Y';
2939 END IF;
2940 END part_time_employee;
2941
2942 FUNCTION get_holiday_pay_agreement_row (
2943 p_assignment_id IN NUMBER
2944 ,p_date_earned IN DATE
2945 ,p_business_group_id IN NUMBER
2946 )
2947 RETURN VARCHAR2
2948 IS
2949 l_row_id NUMBER;
2950 l_row_name VARCHAR2 (240);
2951
2952 CURSOR csr_get_details (
2953 csr_v_assignment_id NUMBER
2954 ,csr_v_effective_date DATE
2955 )
2956 IS
2957 SELECT segment12
2958 FROM per_all_assignments_f paaf
2959 ,hr_soft_coding_keyflex hsck
2960 WHERE paaf.assignment_id = csr_v_assignment_id
2961 AND paaf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
2962 AND csr_v_effective_date BETWEEN paaf.effective_start_date
2963 AND paaf.effective_end_date;
2964
2965 CURSOR csr_get_row_name (csr_v_row_id NUMBER, csr_v_effective_date DATE)
2966 IS
2967 SELECT r.row_low_range_or_name
2968 FROM pay_user_rows_f r
2969 ,pay_user_tables t
2970 WHERE r.legislation_code IS NULL
2971 AND t.legislation_code = 'SE'
2972 AND UPPER (t.user_table_name) = UPPER ('SE_HOLIDAY_PAY_AGREEMENT')
2973 AND t.user_table_id = r.user_table_id
2974 AND r.business_group_id = p_business_group_id
2975 AND r.user_row_id = csr_v_row_id
2976 AND csr_v_effective_date BETWEEN r.effective_start_date
2977 AND r.effective_end_date;
2978 BEGIN
2979 OPEN csr_get_details (p_assignment_id, p_date_earned);
2980
2981 FETCH csr_get_details
2982 INTO l_row_id;
2983
2984 CLOSE csr_get_details;
2985
2986 OPEN csr_get_row_name (l_row_id, p_date_earned);
2987
2988 FETCH csr_get_row_name
2989 INTO l_row_name;
2990
2991 CLOSE csr_get_row_name;
2992
2993 RETURN NVL(l_row_name,'ORACLENULL');
2994 END get_holiday_pay_agreement_row;
2995
2996 FUNCTION get_ey_start_end_date (
2997 p_effective_date IN DATE
2998 ,p_business_group_id IN NUMBER
2999 ,p_tax_unit_id IN NUMBER
3000 ,p_ey_start_date OUT NOCOPY DATE
3001 ,p_ey_end_date OUT NOCOPY DATE
3002 )
3003 RETURN VARCHAR2
3004 IS
3005 l_business_group_id NUMBER;
3006 l_start_month NUMBER;
3007 l_end_month NUMBER;
3008 l_ey_start_date DATE;
3009 l_ey_end_date DATE;
3010 l_assignment_start DATE;
3011 l_year NUMBER;
3012 l_payroll_id NUMBER;
3013 l_min_payroll_start_date DATE;
3014
3015 CURSOR csr_earning_year
3016 IS
3017 SELECT SUBSTR (hoi4.org_information1, 4, 2)
3018 ,SUBSTR (hoi4.org_information2, 4, 2)
3019 FROM hr_organization_units o1
3020 ,hr_organization_information hoi3
3021 ,hr_organization_information hoi4
3022 WHERE o1.business_group_id = l_business_group_id
3023 AND o1.organization_id = hoi3.organization_id
3024 AND hoi3.organization_id = p_tax_unit_id
3025 AND hoi3.org_information_context = 'CLASS'
3026 AND hoi3.org_information1 = 'HR_LEGAL_EMPLOYER'
3027 AND hoi3.organization_id = hoi4.organization_id
3028 AND hoi4.org_information_context = 'SE_HOLIDAY_YEAR_DEFN'
3029 AND hoi4.org_information1 IS NOT NULL;
3030 BEGIN
3031 l_business_group_id := p_business_group_id;
3032
3033 OPEN csr_earning_year;
3034
3035 FETCH csr_earning_year
3036 INTO l_start_month
3037 ,l_end_month;
3038
3039 CLOSE csr_earning_year;
3040
3041 IF l_start_month IS NULL AND l_end_month IS NULL
3042 THEN
3043 RETURN 'N';
3044 ELSE
3045 l_ey_start_date :=
3046 TO_DATE ( '01/'
3047 || l_start_month
3048 || '/'
3049 || TO_NUMBER (TO_CHAR (p_effective_date, 'YYYY') - 1)
3050 ,'dd/mm/yyyy'
3051 );
3052 l_ey_end_date :=
3053 TO_DATE ( '01/'
3054 || l_start_month
3055 || '/'
3056 || TO_NUMBER (TO_CHAR (p_effective_date, 'YYYY') - 1)
3057 ,'dd/mm/yyyy'
3058 )
3059 + 360;
3060 -- get the start date of the Holiday year
3061 l_ey_end_date := LAST_DAY (l_ey_end_date);
3062 p_ey_start_date := l_ey_start_date;
3063 p_ey_end_date := l_ey_end_date;
3064 RETURN 'Y';
3065 END IF;
3066 EXCEPTION
3067 WHEN OTHERS
3068 THEN
3069 RETURN 'N';
3070 END get_ey_start_end_date;
3071
3072 FUNCTION get_avg_working_percentage (
3073 p_assignment_id IN NUMBER
3074 ,p_effective_date IN DATE
3075 ,p_business_group_id IN NUMBER
3076 ,p_tax_unit_id IN NUMBER
3077 )
3078 RETURN NUMBER
3079 IS
3080 l_work_percentage VARCHAR2(30); --NUMBER; --Existing bug fixed as part of 9747212
3081 l_ey_start_date DATE;
3082 l_ey_end_date DATE;
3083 l_call_sub_fun VARCHAR2 (10);
3084
3085 /*
3086 Cursor csr_all_asg_EYear(csr_v_ey_start date,csr_v_ey_end date)
3087 is
3088 SELECT paaf.business_group_id,
3089 paaf.person_id,
3090 segment9
3091 FROM per_all_assignments_f paaf,
3092 hr_soft_coding_keyflex hsck
3093 WHERE paaf.assignment_id = p_assignment_id --15381
3094 AND paaf.soft_coding_keyflex_id=hsck.soft_coding_keyflex_id
3095 AND paaf.effective_start_date <= l_ey_end_date
3096 AND paaf.effective_end_date >= l_ey_start_date;
3097 */
3098 --((csr_v_ey_end - csr_v_ey_start) + 1)
3099 CURSOR csr_get_all_asg_eyear (csr_v_ey_start DATE, csr_v_ey_end DATE)
3100 IS
3101 SELECT ROUND (SUM (perc * days) / sum(days)
3102 ,2
3103 )
3104 FROM (SELECT
3105 -- paaf.business_group_id,
3106 -- paaf.person_id,
3107 -- greatest(paaf.effective_start_date,'01-Apr-2000'),
3108 -- least(paaf.effective_end_date,'31-Mar-2001'),
3109 fnd_number.canonical_to_number (segment9) perc --segment9 perc --Existing bug fixed as part of 9747212
3110 , LEAST (paaf.effective_end_date, csr_v_ey_end)
3111 - GREATEST (paaf.effective_start_date
3112 ,csr_v_ey_start)
3113 + 1 "DAYS"
3114 FROM per_all_assignments_f paaf
3115 ,hr_soft_coding_keyflex hsck
3116 WHERE paaf.assignment_id = p_assignment_id
3117 AND paaf.soft_coding_keyflex_id =
3118 hsck.soft_coding_keyflex_id
3119 AND paaf.effective_start_date <= csr_v_ey_end
3120 AND paaf.effective_end_date >= csr_v_ey_start);
3121 BEGIN
3122 l_call_sub_fun :=
3123 get_ey_start_end_date (p_effective_date
3124 ,p_business_group_id
3125 ,p_tax_unit_id
3126 ,l_ey_start_date
3127 ,l_ey_end_date
3128 );
3129
3130 IF l_call_sub_fun = 'Y'
3131 THEN
3132 OPEN csr_get_all_asg_eyear (l_ey_start_date, l_ey_end_date);
3133
3134 FETCH csr_get_all_asg_eyear
3135 INTO l_work_percentage;
3136
3137 CLOSE csr_get_all_asg_eyear;
3138 ELSE
3139 l_work_percentage := 0;
3140 END IF;
3141
3142 RETURN l_work_percentage;
3143 END get_avg_working_percentage;
3144
3145 FUNCTION get_employee_age_experience (
3146 p_assignment_id IN NUMBER
3147 ,p_effective_date IN DATE
3148 )
3149 RETURN varchar2
3150 IS
3151 l_days_in_year NUMBER := 365;
3152 l_months_in_year NUMBER := 12;
3153 l_months_between NUMBER;
3154 l_assignment_start DATE;
3155 l_prev_exp_days NUMBER;
3156 l_curr_exp_days NUMBER;
3157
3158 CURSOR csr_assignment_start
3159 IS
3160 SELECT MIN (effective_start_date)
3161 FROM per_all_assignments_f
3162 WHERE assignment_id = p_assignment_id;
3163
3164 CURSOR csr_get_dob
3165 IS
3166 SELECT TRUNC (MONTHS_BETWEEN (p_effective_date, papf.date_of_birth))
3167 FROM per_all_assignments_f paaf
3168 ,per_all_people_f papf
3169 WHERE paaf.assignment_id = p_assignment_id
3170 AND papf.person_id = paaf.person_id
3171 AND p_effective_date BETWEEN paaf.effective_start_date
3172 AND paaf.effective_end_date
3173 AND p_effective_date BETWEEN papf.effective_start_date
3174 AND papf.effective_end_date;
3175
3176 CURSOR csr_get_prev_exp_days
3177 IS
3178 SELECT SUM (end_date - start_date)
3179 FROM per_previous_job_usages
3180 WHERE assignment_id = p_assignment_id;
3181 BEGIN
3182 OPEN csr_get_dob;
3183
3184 FETCH csr_get_dob
3185 INTO l_months_between;
3186
3187 CLOSE csr_get_dob;
3188
3189 OPEN csr_assignment_start;
3190
3191 FETCH csr_assignment_start
3192 INTO l_assignment_start;
3193
3194 CLOSE csr_assignment_start;
3195
3196 l_curr_exp_days := p_effective_date - l_assignment_start;
3197
3198 OPEN csr_get_prev_exp_days;
3199
3200 FETCH csr_get_prev_exp_days
3201 INTO l_prev_exp_days;
3202
3203 CLOSE csr_get_prev_exp_days;
3204
3205 IF ( l_months_between >= (18 * l_months_in_year)
3206 AND (l_curr_exp_days + l_prev_exp_days) >= (3 * l_days_in_year)
3207 )
3208 THEN
3209 RETURN 'ABOVE';
3210 ELSE
3211 RETURN 'BELOW';
3212 END IF;
3213 END get_employee_age_experience;
3214
3215 FUNCTION get_sdays_wrking_percentage (
3216 p_assignment_id IN NUMBER
3217 ,p_effective_date IN DATE
3218 ,p_business_group_id IN NUMBER
3219 ,p_tax_unit_id IN NUMBER
3220 ,p_first_year IN NUMBER
3221 ,p_second_year IN NUMBER
3222 ,p_third_year IN NUMBER
3223 ,p_fourth_year IN NUMBER
3224 ,p_fifth_year IN NUMBER
3225 ,p_sixth_year IN NUMBER
3226 ,p_seventh_year IN NUMBER
3227 ,p_all_years IN NUMBER
3231 RETURN NUMBER
3228 ,p_saved_days_taken IN NUMBER
3229 ,p_saved_days_availed IN NUMBER
3230 )
3232 IS
3233 l_work_percentage NUMBER;
3234
3235 TYPE balance_tab IS VARRAY (7) OF NUMBER;
3236
3237 balance_value balance_tab;
3238 l_year NUMBER;
3239 l_already_taken NUMBER;
3240 l_availed NUMBER;
3241 l_current_year_balance NUMBER;
3242 l_sday_wrk_percentage NUMBER := 0;
3243 l_current_year_wrk_perc NUMBER := 0;
3244 l_exit VARCHAR2 (10);
3245
3246 CURSOR csr_legal_employer_entitlement
3247 IS
3248 SELECT hoi4.org_information2
3249 FROM hr_organization_units o1
3250 ,hr_organization_information hoi3
3251 ,hr_organization_information hoi4
3252 WHERE o1.business_group_id = p_business_group_id
3253 AND o1.organization_id = hoi3.organization_id
3254 AND hoi3.organization_id = p_tax_unit_id
3255 AND hoi3.org_information_context = 'CLASS'
3256 AND hoi3.org_information1 = 'HR_LEGAL_EMPLOYER'
3257 AND hoi3.organization_id = hoi4.organization_id
3258 AND hoi4.org_information_context = 'SE_LE_HOLIDAY_PAY_DETAILS'
3259 AND hoi4.org_information1 IS NOT NULL;
3260 BEGIN
3261 l_exit := 'FALSE';
3262 balance_value :=
3263 balance_tab (p_first_year
3264 ,p_second_year
3265 ,p_third_year
3266 ,p_fourth_year
3267 ,p_fifth_year
3268 ,p_sixth_year
3269 ,p_seventh_year
3270 );
3271
3272 -- Get the Year value from the EIT
3273 OPEN csr_legal_employer_entitlement;
3274
3275 FETCH csr_legal_employer_entitlement
3276 INTO l_year;
3277
3278 CLOSE csr_legal_employer_entitlement;
3279
3280 -- DBMS_OUTPUT.Put_Line( l_year );
3281 -- Assign the value from the input parameters
3282 l_already_taken := p_saved_days_taken;
3283 l_availed := p_saved_days_availed;
3284
3285 -- to ge teh init for the year and taken;
3286 -- DBMS_OUTPUT.Put_Line( '********************' );
3287 WHILE (l_exit = 'FALSE' AND l_year > 0)
3288 LOOP
3289 l_current_year_balance := balance_value (l_year);
3290
3291 IF l_current_year_balance = 0
3292 THEN
3293 l_year := l_year - 1;
3294
3295 ELSIF (l_already_taken - l_current_year_balance) > 0
3296 THEN
3297 -- DBMS_OUTPUT.Put_Line( l_current_year_balance );
3298 l_year := l_year - 1;
3299 l_already_taken := l_already_taken - l_current_year_balance;
3300 -- DBMS_OUTPUT.Put_Line( l_year );
3301 -- DBMS_OUTPUT.Put_Line( l_already_taken );
3302 ELSIF (l_already_taken - l_current_year_balance) = 0
3303 THEN
3304 -- DBMS_OUTPUT.Put_Line( l_current_year_balance );
3305 l_year := l_year - 1;
3306 l_already_taken := l_already_taken - l_current_year_balance;
3307 -- DBMS_OUTPUT.Put_Line( l_year );
3308 -- DBMS_OUTPUT.Put_Line( l_already_taken );
3309 l_exit := 'TRUE';
3310 ELSE
3311 l_exit := 'TRUE';
3312 END IF;
3313 -- DBMS_OUTPUT.Put_Line( '*%%%%%%%%%%%%%%%%%%%%' );
3314 -- DBMS_OUTPUT.Put_Line( l_year );
3315 -- DBMS_OUTPUT.Put_Line( l_already_taken );
3316
3317 -- DBMS_OUTPUT.Put_Line( '*******************' );
3318 END LOOP;
3319
3320 -- end of teh init for the year and taken;
3321 l_exit := 'FALSE';
3322
3323 -- DBMS_OUTPUT.Put_Line( '11111111111111111111' );
3324 WHILE (l_exit = 'FALSE' AND l_year > 0)
3325 LOOP
3326 l_current_year_balance := balance_value (l_year);
3327 -- DBMS_OUTPUT.Put_Line( 'current balance ' ||l_current_year_balance );
3328 l_current_year_wrk_perc :=
3329 get_avg_working_percentage (p_assignment_id
3330 , ((p_effective_date) - 365 * l_year)
3331 ,p_business_group_id
3332 ,p_tax_unit_id
3333 );
3334
3335 --DBMS_OUTPUT.Put_Line( 'l_current_year_wrk_perc ' ||l_current_year_wrk_perc );
3336 IF ((l_already_taken + l_availed) > l_current_year_balance)
3337 THEN
3338 -- DBMS_OUTPUT.Put_Line( 'Greater ');
3339 l_sday_wrk_percentage :=
3340 l_sday_wrk_percentage
3341 + (l_current_year_balance - l_already_taken)
3342 * l_current_year_wrk_perc;
3343 l_availed :=
3344 l_availed
3345 - (l_current_year_balance - l_already_taken);
3346 l_already_taken := 0;
3347 -- DBMS_OUTPUT.Put_Line( 'l_year ' ||l_year );
3348 -- DBMS_OUTPUT.Put_Line( 'l_availed '||l_availed );
3349 -- DBMS_OUTPUT.Put_Line( ' sday ' ||l_sday_wrk_percentage );
3350 ELSIF ((l_already_taken + l_availed) = l_current_year_balance)
3351 THEN
3352 -- DBMS_OUTPUT.Put_Line( 'Equal ');
3353 l_sday_wrk_percentage :=
3354 l_sday_wrk_percentage
3355 + (l_current_year_balance - l_already_taken)
3356 * l_current_year_wrk_perc;
3357 l_availed :=
3358 l_availed
3362 -- DBMS_OUTPUT.Put_Line( 'l_availed '||l_availed );
3359 - (l_current_year_balance - l_already_taken);
3360 l_already_taken := 0;
3361 -- DBMS_OUTPUT.Put_Line( 'l_year ' ||l_year );
3363 -- DBMS_OUTPUT.Put_Line( ' sday ' ||l_sday_wrk_percentage );
3364 l_exit := 'TRUE';
3365 ELSE
3366 -- DBMS_OUTPUT.Put_Line( 'Lesser');
3367 l_sday_wrk_percentage :=
3368 l_sday_wrk_percentage
3369 + (l_availed * l_current_year_wrk_perc);
3370 -- DBMS_OUTPUT.Put_Line( 'l_year ' ||l_year );
3371 -- DBMS_OUTPUT.Put_Line( 'l_availed '||l_availed );
3372 -- DBMS_OUTPUT.Put_Line( ' sday ' ||l_sday_wrk_percentage );
3373 l_exit := 'TRUE';
3374 END IF;
3375
3376 -- DBMS_OUTPUT.Put_Line( '@@@@@@@@@@@@@@@@@@@@@@@@@@' );
3377
3378 -- DBMS_OUTPUT.Put_Line( 'l_year ' ||l_year );
3379 -- DBMS_OUTPUT.Put_Line( 'l_availed '||l_availed );
3380 -- DBMS_OUTPUT.Put_Line( ' sday ' || l_sday_wrk_percentage );
3381 l_year := l_year - 1;
3382 -- DBMS_OUTPUT.Put_Line( '*******************' );
3383 END LOOP;
3384
3385 l_sday_wrk_percentage := ROUND (l_sday_wrk_percentage / 100, 2);
3386 -- DBMS_OUTPUT.Put_Line( ' sday ' || l_sday_wrk_percentage );
3387 RETURN NVL(l_sday_wrk_percentage,0);
3388 END get_sdays_wrking_percentage;
3389
3390 PROCEDURE get_weekend_public_holidays (
3391 p_assignment_id IN NUMBER
3392 ,p_start_date IN DATE
3393 ,p_end_date IN DATE
3394 ,p_start_time IN VARCHAR2
3395 ,p_end_time IN VARCHAR2
3396 ,p_calc_type IN VARCHAR2
3397 ,p_total_holidays OUT NOCOPY NUMBER
3398 )
3399 IS
3400 l_return_frm_wrk_schd NUMBER;
3401 l_days_wth_public NUMBER;
3402 l_days_wthout_public NUMBER;
3403 l_total_days NUMBER;
3404 l_current_public_holidays NUMBER;
3405 l_current_weekends NUMBER;
3406 l_start_date DATE;
3407 l_end_date DATE;
3408
3409 CURSOR get_total_days (csr_end_date DATE, csr_start_date DATE)
3410 IS
3411 SELECT FLOOR (csr_end_date - csr_start_date) + 1
3412 FROM DUAL;
3413
3414 CURSOR get_time_format (l_time VARCHAR2)
3415 IS
3416 SELECT REPLACE (TRIM (l_time), ':', '.')
3417 FROM DUAL;
3418
3419 l_start_time VARCHAR2 (5);
3420 l_end_time VARCHAR2 (5);
3421 BEGIN
3422 OPEN get_time_format (p_start_time);
3423
3424 FETCH get_time_format
3425 INTO l_start_time;
3426
3427 CLOSE get_time_format;
3428
3429 OPEN get_time_format (p_end_time);
3430
3431 FETCH get_time_format
3432 INTO l_end_time;
3433
3434 CLOSE get_time_format;
3435
3436 l_start_date :=
3437 TO_DATE ( TO_CHAR (p_start_date
3438 ,'DD-MM-YYYY')
3439 || ' '
3440 || l_start_time
3441 ,'DD-MM-YYYY HH24:MI'
3442 );
3443 l_end_date :=
3444 TO_DATE ( TO_CHAR (p_end_date, 'DD-MM-YYYY')
3445 || ' '
3446 || l_end_time
3447 ,'DD-MM-YYYY HH24:MI'
3448 );
3449
3450 OPEN get_total_days (l_end_date, l_start_date);
3451
3452 FETCH get_total_days
3453 INTO l_total_days;
3454
3455 CLOSE get_total_days;
3456
3457 -- Get Total days Excluding Public Holidays exculding Weekends
3458 l_return_frm_wrk_schd :=
3459 hr_loc_work_schedule.calc_sch_based_dur (p_assignment_id
3460 ,p_calc_type
3461 ,'N'
3462 ,p_start_date
3463 ,p_end_date
3464 ,l_start_time
3465 ,l_end_time
3466 ,l_days_wthout_public
3467 );
3468 p_total_holidays := l_days_wthout_public;
3469 END get_weekend_public_holidays;
3470
3471 FUNCTION get_avg_earning_year_hours (
3472 p_assignment_id IN NUMBER
3473 ,p_effective_date IN DATE
3474 ,p_business_group_id IN NUMBER
3475 ,p_tax_unit_id IN NUMBER
3476 ,p_total_absence IN NUMBER
3477 )
3478 RETURN NUMBER
3479 IS
3480 l_hours NUMBER;
3481 l_ey_start_date DATE;
3482 l_ey_end_date DATE;
3483 l_call_sub_fun VARCHAR2 (10);
3484
3485 CURSOR csr_get_all_asg_eyear (csr_v_ey_start DATE, csr_v_ey_end DATE)
3486 IS
3487 SELECT ROUND (SUM (perc * in_hours) / 100, 2)
3488 FROM (SELECT segment9 perc
3489 , LEAST (paaf.effective_end_date, csr_v_ey_end)
3490 - GREATEST (paaf.effective_start_date, csr_v_ey_start)
3491 + 1 "DAYS"
3492 ,normal_hours
3493 ,frequency
3494 ,segment13 days_in_week
3495 ,CASE
3496 WHEN frequency = 'D'
3497 THEN ( LEAST (paaf.effective_end_date
3498 ,csr_v_ey_end
3502 )
3499 )
3500 - GREATEST (paaf.effective_start_date
3501 ,csr_v_ey_start
3503 + 1
3504 )
3505 * normal_hours
3506 WHEN frequency = 'W'
3507 THEN ( LEAST (paaf.effective_end_date
3508 ,csr_v_ey_end
3509 )
3510 - GREATEST (paaf.effective_start_date
3511 ,csr_v_ey_start
3512 )
3513 + 1
3514 )
3515 * (normal_hours / segment13)
3516 WHEN frequency = 'M'
3517 THEN ( LEAST (paaf.effective_end_date
3518 ,csr_v_ey_end
3519 )
3520 - GREATEST (paaf.effective_start_date
3521 ,csr_v_ey_start
3522 )
3523 + 1
3524 )
3525 * ( (normal_hours * 12)
3526 / ( ( (csr_v_ey_end)
3527 - (csr_v_ey_start)
3528 )
3529 + 1
3530 )
3531 )
3532 END "IN_HOURS"
3533 FROM per_all_assignments_f paaf
3534 ,hr_soft_coding_keyflex hsck
3535 WHERE paaf.assignment_id = p_assignment_id
3536 AND paaf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
3537 AND paaf.effective_start_date <= csr_v_ey_end
3538 AND paaf.effective_end_date >= csr_v_ey_start);
3539 BEGIN
3540 l_hours := 0;
3541 l_call_sub_fun :=
3542 get_ey_start_end_date (p_effective_date
3543 ,p_business_group_id
3544 ,p_tax_unit_id
3545 ,l_ey_start_date
3546 ,l_ey_end_date
3547 );
3548
3549 IF l_call_sub_fun = 'Y'
3550 THEN
3551 get_weekend_public_holidays (p_assignment_id
3552 ,l_ey_start_date
3553 ,l_ey_end_date
3554 ,'00.00'
3555 ,'23.59'
3556 ,'H'
3557 ,l_hours
3558 );
3559
3560 IF l_hours <= 0
3561 THEN
3562 OPEN csr_get_all_asg_eyear (l_ey_start_date, l_ey_end_date);
3563
3564 FETCH csr_get_all_asg_eyear
3565 INTO l_hours;
3566
3567 CLOSE csr_get_all_asg_eyear;
3568 END IF;
3569 ELSE
3570 l_hours := 0;
3571 END IF;
3572
3573 l_hours :=
3574 ROUND ( l_hours
3575 - (l_hours / ((l_ey_end_date - l_ey_start_date) + 1))
3576 * p_total_absence
3577 ,2
3578 );
3579 RETURN NVL (l_hours, 0);
3580 END get_avg_earning_year_hours;
3581
3582
3583 FUNCTION get_first_three_payroll_check (
3584 p_assignment_id IN NUMBER
3585 ,p_effective_date IN DATE
3586 ,p_business_group_id IN NUMBER
3587 ,p_tax_unit_id IN NUMBER
3588 ,p_pay_start_date IN DATE
3589 ,p_pay_end_date IN DATE
3590 )
3591 RETURN VARCHAR2
3592 IS
3593 l_business_group_id NUMBER;
3594 l_start_month NUMBER;
3595 l_end_month NUMBER;
3596 l_earning_start_date DATE;
3597 l_earning_end_date DATE;
3598 l_assignment_start DATE;
3599
3600 CURSOR csr_earning_year
3601 IS
3602 SELECT SUBSTR (hoi2.org_information1, 4, 2)
3603 ,SUBSTR (hoi2.org_information2, 4, 2)
3604 FROM hr_organization_units o1
3605 ,hr_organization_information hoi1
3606 ,hr_organization_information hoi2
3607 WHERE hoi1.organization_id = o1.organization_id
3608 AND hoi1.organization_id = p_tax_unit_id --3134
3609 AND hoi1.org_information_context = 'CLASS'
3610 AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
3611 AND hoi1.organization_id = hoi2.organization_id
3612 AND hoi2.org_information_context = 'SE_HOLIDAY_YEAR_DEFN'
3613 AND hoi2.org_information1 IS NOT NULL;
3614
3615 CURSOR csr_assignment_start
3616 IS
3617 SELECT MIN (effective_start_date)
3618 FROM per_all_assignments_f
3619 WHERE assignment_id = p_assignment_id;
3620 BEGIN
3621 l_business_group_id := p_business_group_id;
3622
3623 OPEN csr_earning_year;
3624
3625 FETCH csr_earning_year
3626 INTO l_start_month
3627 ,l_end_month;
3628
3629 CLOSE csr_earning_year;
3630
3631 IF l_start_month IS NULL AND l_end_month IS NULL
3632 THEN
3633 RETURN 'N';
3634 ELSE
3635 l_earning_start_date :=
3636 TO_DATE ( '01/'
3637 || l_start_month
3638 || '/'
3642 l_earning_end_date :=
3639 || TO_NUMBER (TO_CHAR (p_effective_date, 'YYYY') - 1)
3640 ,'dd/mm/yyyy'
3641 );
3643 TO_DATE ( '01/'
3644 || l_start_month
3645 || '/'
3646 || TO_NUMBER (TO_CHAR (p_effective_date, 'YYYY') - 1)
3647 ,'dd/mm/yyyy'
3648 )
3649 + 360;
3650 l_earning_end_date := LAST_DAY (l_earning_end_date);
3651
3652 --checking the l_earning_end_date+1 lies between the payroll periods for first payroll
3653 --period after earning year
3654 IF (p_pay_start_date <= (ADD_MONTHS (l_earning_end_date, 3)))
3655 AND (ADD_MONTHS (l_earning_end_date, 3) >= p_pay_end_date)
3656 THEN
3657 /* check whether the person has the assignment in the earning year */
3658 OPEN csr_assignment_start;
3659
3660 FETCH csr_assignment_start
3661 INTO l_assignment_start;
3662
3663 CLOSE csr_assignment_start;
3664
3665 IF l_assignment_start <= l_earning_end_date
3666 THEN
3667 RETURN 'Y';
3668 ELSE
3669 RETURN 'N';
3670 END IF;
3671 ELSE
3672 RETURN 'N';
3673 END IF;
3674 END IF;
3675 EXCEPTION
3676 WHEN OTHERS
3677 THEN
3678 RETURN 'N';
3679 END get_first_three_payroll_check;
3680
3681
3682 END pay_se_holiday_pay;