[Home] [Help]
PACKAGE BODY: APPS.PER_HU_ABS_REP_ARCHIVE_PKG
Source
1 PACKAGE BODY PER_HU_ABS_REP_ARCHIVE_PKG AS
2 /* $Header: pehuarep.pkb 120.1 2006/08/30 13:33:41 rbhardwa noship $ */
3 --
4 --globals
5 --
6 g_reporting_date DATE;
7 g_effective_date DATE;
8 g_business_group_id NUMBER;
9 g_payroll_id NUMBER;
10 g_assignment_set_id NUMBER;
11 --
12 --------------------------------------------------------------------------------
13 -- GET_PARAMETER
14 --------------------------------------------------------------------------------
15 FUNCTION get_parameter(
16 p_parameter_string IN VARCHAR2
17 ,p_token IN VARCHAR2
18 ) RETURN VARCHAR2 IS
19 --
20 l_parameter pay_payroll_actions.legislative_parameters%TYPE;
21 l_start_pos NUMBER;
22 l_delimiter VARCHAR2(1);
23 --
24 BEGIN
25 l_delimiter := ' ';
26 l_parameter := NULL;
27 l_start_pos := instr(' '||p_parameter_string,l_delimiter||p_token||'=');
28 IF l_start_pos = 0 THEN
29 l_delimiter := '|';
30 l_start_pos := instr(' '||p_parameter_string,l_delimiter||p_token||'=');
31 END IF;
32 IF l_start_pos <> 0 THEN
33 l_start_pos := l_start_pos + length(p_token||'=');
34 l_parameter := substr(p_parameter_string,
35 l_start_pos,
36 instr(p_parameter_string||' ',
37 l_delimiter,l_start_pos)
38 - l_start_pos);
39
40 END IF;
41 RETURN l_parameter;
42 END get_parameter;
43 --------------------------------------------------------------------------------
44 -- GET_ALL_PARAMETERS
45 --------------------------------------------------------------------------------
46 PROCEDURE get_all_parameters(p_payroll_action_id IN NUMBER
47 ,p_business_group_id OUT NOCOPY NUMBER
48 ,p_effective_date OUT NOCOPY DATE
49 ,p_reporting_date OUT NOCOPY DATE
50 ,p_payroll_id OUT NOCOPY NUMBER
51 ,p_assignment_set_id OUT NOCOPY NUMBER
52 ,p_employee_id OUT NOCOPY NUMBER
53 ) IS
54 --
55 CURSOR csr_parameter_info (c_payroll_action_id NUMBER) IS
56 SELECT effective_date
57 ,fnd_date.canonical_to_date(per_hu_abs_rep_archive_pkg.get_parameter(legislative_parameters, 'DATE'))
58 ,per_hu_abs_rep_archive_pkg.get_parameter(legislative_parameters, 'PAYROLL_ID')
59 ,per_hu_abs_rep_archive_pkg.get_parameter(legislative_parameters, 'ASG_SET_ID')
60 ,per_hu_abs_rep_archive_pkg.get_parameter(legislative_parameters, 'EMP_ID')
61 ,business_group_id
62 FROM pay_payroll_actions
63 WHERE payroll_action_id = c_payroll_action_id;
64 --
65 BEGIN
66 --
67 OPEN csr_parameter_info (p_payroll_action_id);
68 FETCH csr_parameter_info INTO p_effective_date
69 ,p_reporting_date
70 ,p_payroll_id
71 ,p_assignment_set_id
72 ,p_employee_id
73 ,p_business_group_id;
74 CLOSE csr_parameter_info;
75 --
76 END;
77 --
78 --------------------------------------------------------------------------------
79 -- FUNCTION GET_ABS_REP_PARAMETER
80 --------------------------------------------------------------------------------
81 FUNCTION get_abs_rep_parameter (p_actid IN NUMBER) RETURN VARCHAR2 IS
82
83 --
84 CURSOR csr_get_payroll_name(cpayroll_id NUMBER
85 ,ceffective_date DATE) IS
86 SELECT payroll_name
87 FROM pay_all_payrolls_f
88 WHERE payroll_id = cpayroll_id
89 AND ceffective_date BETWEEN effective_start_date AND effective_end_date;
90 --
91 CURSOR csr_get_person_name(cperson_id NUMBER
92 ,ceffective_date DATE) IS
93 SELECT full_name
94 FROM per_All_people_f
95 WHERE person_id = cperson_id
96 AND ceffective_date BETWEEN effective_start_date AND effective_end_date;
97 --
98 leffective_date DATE;
99 lreporting_date DATE;
100 lpayroll_id pay_all_payrolls_f.payroll_id%TYPE;
101 lassignment_set_id hr_assignment_sets.assignment_set_id%TYPE;
102 lperson_id per_all_people_f.person_id%TYPE;
103 lpayroll_name pay_all_payrolls_f.payroll_name%TYPE;
104 lreturn_val VARCHAR2(400);
105 lfull_name per_all_people_f.full_name%TYPE;
106 lbusiness_group_id per_all_people_f.business_group_id%TYPE;
107 BEGIN
108 --
109 per_hu_abs_rep_archive_pkg.get_all_parameters (
110 p_payroll_action_id => p_actid
111 ,p_business_group_id => lbusiness_group_id
112 ,p_effective_date => leffective_date
113 ,p_reporting_date => lreporting_date
114 ,p_payroll_id => lpayroll_id
115 ,p_assignment_set_id => lassignment_set_id
116 ,p_employee_id => lperson_id
117 );
118
119 OPEN csr_get_payroll_name(lpayroll_id,leffective_date );
120 FETCH csr_get_payroll_name INTO lpayroll_name;
121 CLOSE csr_get_payroll_name;
122
123 OPEN csr_get_person_name(lperson_id ,leffective_date );
124 FETCH csr_get_person_name INTO lfull_name;
125 CLOSE csr_get_person_name;
126
127 lreturn_val := fnd_date.date_to_displaydate(leffective_date)
128 ||' - '||fnd_date.date_to_displaydate(lreporting_date)
129 ||' - '||rpad(nvl(lpayroll_name,' '),40,' ')
130 ||' - '||rpad(nvl(to_char(lassignment_set_id),' '),10,' ')
131 ||' - '||rpad(nvl(lfull_name,' '),40,' ');
132
133 RETURN lreturn_val;
134 END get_abs_rep_parameter;
135
136 --
137 --------------------------------------------------------------------------------
138 -- RANGE_CODE
139 --------------------------------------------------------------------------------
140 PROCEDURE range_code(p_actid IN NUMBER
141 ,sqlstr OUT NOCOPY VARCHAR2) IS
142
143 CURSOR csr_utv_check(c_business_group_id NUMBER
144 ,c_reporting_date DATE) IS
145 SELECT 1
146 FROM pay_user_column_instances_f pui
147 ,pay_user_columns puc
148 ,pay_user_tables put
149 WHERE pui.user_column_id = puc.user_column_id
150 AND puc.user_column_name = 'Holiday Type'
151 AND puc.legislation_code = 'HU'
152 AND puc.user_table_id = put.user_table_id
153 AND put.user_table_name = 'HU_ABSENCE_REPORT_ACCRUAL_PLAN_MAPPINGS'
154 AND pui.business_group_id = c_business_group_id
155 AND put.legislation_code = 'HU'
156 AND c_reporting_date BETWEEN pui.effective_start_date
157 AND pui.effective_end_date
158 AND pui.value LIKE 'HU%';
159
160 l_effective_date DATE;
161 l_emp_id per_all_people_f.person_id%TYPE;
162 l_exsist NUMBER;
163 BEGIN
164 --
165 per_hu_abs_rep_archive_pkg.get_all_parameters (
166 p_payroll_action_id => p_actid
167 ,p_business_group_id => g_business_group_id
168 ,p_effective_date => l_effective_date
169 ,p_reporting_date => g_reporting_date
170 ,p_payroll_id => g_payroll_id
171 ,p_assignment_set_id => g_assignment_set_id
172 ,p_employee_id => l_emp_id
173 );
174
175 OPEN csr_utv_check(g_business_group_id, g_reporting_date);
176 FETCH csr_utv_check INTO l_exsist;
177 IF csr_utv_check%NOTFOUND THEN
178 --
179 sqlstr := 'SELECT 1 FROM dual WHERE to_char(:payroll_action_id) = dummy';
180 -- Message to written to log file.
181 fnd_file.put_line(fnd_file.log,fnd_message.get_string('PER'
182 ,'HR_HU_ABS_REP_UDT_VALUE'));
183 --
184 ELSE
185 --
186 sqlstr := 'SELECT distinct person_id
187 FROM per_people_f ppf
188 ,pay_payroll_actions ppa
189 WHERE ppa.payroll_action_id = :payroll_action_id
190 AND ppa.business_group_id = ppf.business_group_id
191 ORDER BY ppf.person_id';
192 --
193 END IF;
194 CLOSE csr_utv_check;
195
196 EXCEPTION
197 WHEN OTHERS THEN
198 sqlstr := 'SELECT 1 FROM dual WHERE to_char(:payroll_action_id) = dummy';
199 END range_code;
200
201 --------------------------------------------------------------------------------
202 -- ACTION_CREATION_CODE
203 --------------------------------------------------------------------------------
204 PROCEDURE action_creation_code (p_actid IN NUMBER
205 ,stperson IN NUMBER
206 ,endperson IN NUMBER
207 ,chunk IN NUMBER) IS
208 ----
209 CURSOR csr_qualifying_assignments(c_pact_id NUMBER
210 ,c_stperson NUMBER
211 ,c_endperson NUMBER
212 ,c_reporting_date DATE
213 ,c_payroll_id NUMBER
214 ,c_emp_id NUMBER) IS
215 SELECT paa.assignment_id assignment_id
216 ,paa.people_group_id people_group_id
217 ,hoi.org_information1 company_name
218 ,pap.full_name full_name
219 ,pap.person_id person_id
220 ,pap.employee_NUMBER emp_no
221 ,pap.date_of_birth date_of_birth
222 ,ppf.date_start hire_date
223 ,ppf.actual_termination_date termination_date
224 ,hou.name organization
225 ,paa.location_id location_id
226 ,paa.payroll_id payroll_id
227 ,paa.job_id job_id
228 FROM pay_payroll_actions ppa
229 ,per_all_assignments_f paa
230 ,per_all_people_f pap
231 ,per_periods_of_service ppf
232 ,hr_organization_information hoi
233 ,hr_all_organization_units hou
234 WHERE ppa.payroll_action_id = c_pact_id
235 AND pap.person_id = paa.person_id
236 AND pap.person_id BETWEEN c_stperson
237 AND c_endperson
238 AND pap.person_id = NVL(c_emp_id,pap.person_id)
239 AND paa.primary_flag = 'Y'
240 AND paa.assignment_type = 'E'
241 AND ppa.business_group_id = paa.business_group_id
242 AND pap.business_group_id = paa.business_group_id
243 AND paa.period_of_service_id = ppf.period_of_service_id
244 AND NVL(paa.payroll_id,0) = NVL(c_payroll_id,NVL(paa.payroll_id,0))
245 AND hoi.organization_id = paa.business_group_id
246 AND hoi.org_information_context = 'HU_COMPANY_INFORMATION_DETAILS'
247 AND hou.organization_id = paa.organization_id
248 AND c_reporting_date BETWEEN pap.effective_start_date
249 AND pap.effective_end_date
250 AND c_reporting_date BETWEEN paa.effective_start_date
251 AND paa.effective_end_date
252 AND (ppf.actual_termination_date IS NULL
253 OR ppf.actual_termination_date > c_reporting_date)
254 ORDER BY assignment_id;
255 --
256 CURSOR csr_get_utv(c_business_group_id NUMBER
257 ,c_reporting_date DATE
258 ,c_holiday_type VARCHAR2) IS
259 SELECT pur.row_low_range_or_name accrual_plan
260 FROM pay_user_column_instances_f pui
261 ,pay_user_columns puc
262 ,pay_user_tables put
263 ,pay_user_rows_f pur
264 WHERE pui.user_column_id = puc.user_column_id
265 AND puc.user_column_name = 'Holiday Type'
266 AND puc.legislation_code = 'HU'
267 AND puc.user_table_id = put.user_table_id
268 AND put.user_table_name = 'HU_ABSENCE_REPORT_ACCRUAL_PLAN_MAPPINGS'
269 AND pui.business_group_id = c_business_group_id
270 AND pui.value = c_holiday_type
271 AND put.legislation_code = 'HU'
272 AND pui.user_row_id = pur.user_row_id
273 AND c_reporting_date BETWEEN pui.effective_start_date
274 AND pui.effective_end_date
275 AND c_reporting_date BETWEEN pur.effective_start_date
276 AND pur.effective_end_date ;
277 --
278 CURSOR csr_location_code(c_location_id NUMBER) IS
279 SELECT hrl.location_code
280 FROM hr_locations hrl
281 WHERE hrl.location_id = c_location_id;
282 --
283 CURSOR csr_job_name(c_job_id NUMBER) IS
284 SELECT jbt.name
285 FROM per_jobs_tl jbt
286 WHERE jbt.language = userenv('LANG')
287 AND jbt.job_id = c_job_id;
288 --
289 CURSOR csr_incl_excl(c_assignment_id NUMBER
290 ,c_assignment_set_id NUMBER) IS
291 SELECT 1
292 FROM hr_assignment_sets has
293 ,per_all_assignments_f paa
294 WHERE has.assignment_set_id = NVL(c_assignment_set_id, has.assignment_set_id)
295 AND paa.assignment_id = c_assignment_id
296 AND NVL(paa.payroll_id,0) = NVL(has.payroll_id,NVL(paa.payroll_id,0))
297 AND NOT EXISTS
298 (
299 SELECT 1
300 FROM hr_assignment_set_amendments hasa
301 WHERE hasa.assignment_set_id = c_assignment_set_id
302 AND hasa.assignment_id = c_assignment_id
303 AND hasa.include_or_exclude = 'E'
304 )
305 AND NOT EXISTS
306 (
307 SELECT 1
308 FROM hr_assignment_set_amendments hasa
309 WHERE hasa.assignment_set_id = c_assignment_set_id
310 AND hasa.assignment_id <> c_assignment_id
311 AND hasa.include_or_exclude = 'I'
312 AND NOT EXISTS
313 (
314 SELECT 1
315 FROM hr_assignment_set_amendments hasa
316 WHERE hasa.assignment_set_id = c_assignment_set_id
317 AND hasa.assignment_id = c_assignment_id
318 AND hasa.include_or_exclude = 'I'
319 )
320 );
321 --
322 CURSOR csr_accrual_details(c_accrual_plan VARCHAR2
323 ,c_business_group_id NUMBER) IS
324 SELECT accrual_plan_id
325 ,accrual_plan_element_type_id
326 ,co_formula_id
327 FROM pay_accrual_plans
328 WHERE accrual_plan_name = c_accrual_plan
329 AND business_group_id = c_business_group_id;
330 --
331 CURSOR suspended_asg_end_dt(c_assignment_id NUMBER
332 ,c_reporting_date DATE ) IS
333 SELECT MAX(paa.effective_end_date)
334 FROM per_all_assignments_f paa
335 ,per_assignment_status_types pas
336 WHERE paa.assignment_id = c_assignment_id
337 AND pas.assignment_status_type_id = paa.assignment_status_type_id
338 AND pas.per_system_status = 'SUSP_ASSIGN'
339 AND paa.effective_end_date
340 BETWEEN to_date('01-JAN'||to_char(c_reporting_date,'YYYY'),'dd/mm/yyyy')
341 AND to_date('31-DEC'||to_char(c_reporting_date,'YYYY'),'dd/mm/yyyy');
342 --
343
344 l_actid NUMBER;
345 l_action_info_id pay_action_information.action_information_id%TYPE;
346 l_ovn pay_action_information.object_version_NUMBER%TYPE;
347 l_emp_id per_all_people_f.person_id%TYPE;
348 l_sort_1 VARCHAR2(30);
349 l_sort_2 VARCHAR2(30);
350 l_incl_excl NUMBER;
351 l_effective_date DATE;
352
353 l_Base_ele_type_id pay_accrual_plans.accrual_plan_element_type_id%TYPE;
354 l_childcare_ele_type_id pay_accrual_plans.accrual_plan_element_type_id%TYPE;
355 l_other_ele_type_id pay_accrual_plans.accrual_plan_element_type_id%TYPE;
356 l_sickness_ele_type_id pay_accrual_plans.accrual_plan_element_type_id%TYPE;
357
358 l_accrual_plan_id_1 pay_accrual_plans.accrual_plan_id%TYPE;
359 l_accrual_plan_id_2 pay_accrual_plans.accrual_plan_id%TYPE;
360 l_accrual_plan_id_3 pay_accrual_plans.accrual_plan_id%TYPE;
361 l_accrual_plan_id_4 pay_accrual_plans.accrual_plan_id%TYPE;
362
363 l_base_holiday NUMBER;
364 l_base_holiday_prev NUMBER;
365 l_child_care_holiday NUMBER;
366 l_child_care_holiday_prev NUMBER;
367 l_additional_holiday NUMBER;
368 l_additional_holiday_prev NUMBER;
369 l_normal_paid_holiday_prev NUMBER;
370 l_sickness_holiday NUMBER;
371 l_normal_holiday_total NUMBER;
372
373 l_base_holiday_sum NUMBER;
374 l_child_care_holiday_sum NUMBER;
375 l_additional_holiday_sum NUMBER;
376 l_sickness_holiday_sum NUMBER;
377
378 l_base_accrual_sum NUMBER;
379 l_child_care_accrual_sum NUMBER;
380 l_additional_accrual_sum NUMBER;
381 l_sickness_accrual_sum NUMBER;
382
383 --l_base_absence_sum NUMBER;
384 --l_child_care_absence_sum NUMBER;
385 --l_additional_absence_sum NUMBER;
386
387 l_base_holiday_carry_over NUMBER;
388 l_child_hol_carry_over NUMBER;
389 l_add_holiday_carry_over NUMBER;
390
391 l_base_hol_carry_over_sum NUMBER;
392 l_child_hol_carry_over_sum NUMBER;
393 l_add_hol_carry_over_sum NUMBER;
394
395 l_start_date DATE;
396 l_end_date DATE;
397 l_accrual_end_date DATE;
398 l_base_accrual NUMBER;
399 l_child_care_accrual NUMBER;
400 l_additional_accrual NUMBER;
401 l_sickness_accrual NUMBER;
402
403 l_base_exp_date DATE;
404 l_child_care_exp_date DATE;
405 l_additional_exp_date DATE;
406
407 --l_base_absence NUMBER;
408 --l_child_care_absence NUMBER;
409 --l_additional_absence NUMBER;
410
411 l_location_code hr_locations.location_code%TYPE;
412 l_job_name per_job_definitions.segment1%TYPE;
413
414 l_calculation_date DATE;
415 l_term_end_date DATE;
416 l_co_formula_id_1 pay_accrual_plans.co_formula_id%TYPE;
417 l_co_formula_id_2 pay_accrual_plans.co_formula_id%TYPE;
418 l_co_formula_id_3 pay_accrual_plans.co_formula_id%TYPE;
419 l_co_formula_id_4 pay_accrual_plans.co_formula_id%TYPE;
420 l_max_co NUMBER;
421 l_dummy DATE;
422 l_sus_asg_end_dt DATE;
423 l_emp_enrolment NUMBER;
424 l_absence NUMBER;
425 --
426 BEGIN
427 --
428 l_base_holiday_sum := 0;
429 l_child_care_holiday_sum := 0;
430 l_additional_holiday_sum := 0;
431 l_sickness_holiday_sum := 0;
432 --
433 l_base_accrual_sum := 0;
434 l_child_care_accrual_sum := 0;
435 l_additional_accrual_sum := 0;
436 l_sickness_accrual_sum := 0;
437 --
438 --l_base_absence_sum := 0;
439 --l_child_care_absence_sum := 0;
440 --l_additional_absence_sum := 0;
441 --
442 l_base_hol_carry_over_sum := 0;
443 l_child_hol_carry_over_sum := 0;
444 l_add_hol_carry_over_sum := 0;
445 --
446 per_hu_abs_rep_archive_pkg.get_all_parameters (
447 p_payroll_action_id => p_actid
448 ,p_business_group_id => g_business_group_id
449 ,p_effective_date => l_effective_date
450 ,p_reporting_date => g_reporting_date
451 ,p_payroll_id => g_payroll_id
452 ,p_assignment_set_id => g_assignment_set_id
453 ,p_employee_id => l_emp_id
454 );
455
456 FOR csr_rec IN csr_qualifying_assignments(p_actid
457 ,stperson
458 ,endperson
459 ,g_reporting_date
460 ,g_payroll_id
461 ,l_emp_id) LOOP
462 l_emp_enrolment := 0;
463 --
464 OPEN csr_location_code(csr_rec.location_id);
465 FETCH csr_location_code INTO l_location_code;
466 CLOSE csr_location_code;
467 --
468 OPEN suspended_asg_end_dt(csr_rec.assignment_id,g_reporting_date);
469 FETCH suspended_asg_end_dt INTO l_sus_asg_end_dt;
470 CLOSE suspended_asg_end_dt;
471 --
472 OPEN csr_job_name(csr_rec.job_id);
473 FETCH csr_job_name INTO l_job_name;
474 CLOSE csr_job_name;
475 --
476 IF g_assignment_set_id IS NOT NULL THEN
477 OPEN csr_incl_excl(csr_rec.assignment_id
478 ,g_assignment_set_id);
479 FETCH csr_incl_excl INTO l_incl_excl;
480 CLOSE csr_incl_excl;
481 END IF;
482
483 IF l_incl_excl = 1 OR g_assignment_set_id IS NULL THEN
484 FOR csr_utv IN csr_get_utv(g_business_group_id
485 ,g_reporting_date
486 ,'HU1') LOOP
487 IF csr_utv.accrual_plan IS NOT NULL THEN
488 OPEN csr_accrual_details(csr_utv.accrual_plan,g_business_group_id);
489 FETCH csr_accrual_details INTO l_accrual_plan_id_1
490 ,l_Base_ele_type_id
491 ,l_co_formula_id_1;
492 CLOSE csr_accrual_details;
493
494 IF l_accrual_plan_id_1 IS NOT NULL THEN
495 IF per_accrual_calc_functions.check_assignment_enrollment(
496 csr_rec.assignment_id
497 ,l_Base_ele_type_id
498 ,g_reporting_date) THEN
499
500 l_emp_enrolment := 1;
501
502 per_accrual_calc_functions.get_carry_over_values
503 (p_co_formula_id => l_co_formula_id_1
504 ,p_assignment_id => csr_rec.assignment_id
505 ,p_calculation_date => g_reporting_date
506 ,p_accrual_plan_id => l_accrual_plan_id_1
507 ,p_business_group_id => g_business_group_id
508 ,p_payroll_id => csr_rec.payroll_id
509 ,p_accrual_term => 'CURRENT'
510 ,p_effective_date => l_term_end_date
511 ,p_session_date => g_reporting_date
512 ,p_max_carry_over => l_max_co
513 ,p_expiry_date => l_base_exp_date
514 );
515
516 l_calculation_date := LEAST(l_term_end_date
517 ,NVL(csr_rec.termination_date
518 ,TO_DATE('31-12-4712','DD-MM-YYYY')));
519
520 per_accrual_calc_functions.get_net_accrual
521 (p_assignment_id => csr_rec.assignment_id
522 ,p_plan_id => l_accrual_plan_id_1
523 ,p_payroll_id => csr_rec.payroll_id
524 ,p_business_group_id => g_business_group_id
525 ,p_assignment_action_id => -1
526 ,p_calculation_date => l_calculation_date
527 ,p_accrual_start_date => NULL
528 ,p_accrual_latest_balance => NULL
529 ,p_calling_point => 'FRM'
530 ,p_start_date => l_start_date
531 ,p_end_date => l_end_date
532 ,p_accrual_end_date => l_accrual_end_date
533 ,p_accrual => l_base_accrual
534 ,p_net_entitlement => l_base_holiday);
535
536 /* ***********************************************************
537 l_base_holiday_prev :=
538 per_accrual_calc_functions.get_carry_over
539 (p_assignment_id => csr_rec.assignment_id
540 ,p_plan_id => l_accrual_plan_id_1
541 ,p_start_date => l_start_date
542 ,p_calculation_date => l_end_date
543 );
544
545
546 l_base_holiday_carry_over :=
547 per_accrual_calc_functions.get_carry_over
548 (p_assignment_id => csr_rec.assignment_id
549 ,p_plan_id => l_accrual_plan_id_1
550 ,p_start_date => l_start_date
551 ,p_calculation_date => to_date('01-JAN'||to_char(g_reporting_date,'YYYY'),'dd/mm/yyyy')
552 );
553
554
555 l_base_holiday := nvl(l_base_holiday,0) - nvl(l_base_holiday_prev,0);
556
557 l_base_absence := per_accrual_calc_functions.get_absence(
558 p_assignment_id => csr_rec.assignment_id
559 ,p_plan_id => l_accrual_plan_id_1
560 ,p_start_date => l_start_date
561 ,p_calculation_date =>g_reporting_date); -- to_date(to_char(l_base_exp_date,'dd/mm')||to_char(g_reporting_date,'YYYY'),'dd/mm/yyyy')
562 *********************************************************/
563
564 IF TO_NUMBER(TO_CHAR(g_reporting_date,'mmdd')) <= TO_NUMBER(TO_CHAR(l_base_exp_date,'mmdd')) THEN
565 l_base_holiday_carry_over :=
566 per_accrual_calc_functions.get_carry_over
567 (p_assignment_id => csr_rec.assignment_id
568 ,p_plan_id => l_accrual_plan_id_1
569 ,p_start_date => l_start_date
570 ,p_calculation_date => to_date('01/01/'||to_char(g_reporting_date,'YYYY'),'dd/mm/yyyy')
571 );
572 l_normal_holiday_total := nvl(l_normal_holiday_total,0)
573 +nvl(l_base_accrual,0)
574 +nvl(l_base_holiday_carry_over,0) ;
575
576 ELSE
577 l_base_holiday_carry_over := 0;
578 l_normal_holiday_total := nvl(l_normal_holiday_total,0)
579 +nvl(l_base_accrual,0);
580 END IF;
581 ELSE
582 --l_base_absence := 0;
583 l_base_accrual :=0;
584 l_base_holiday := 0;
585 l_base_holiday_carry_over := 0;
586 END IF;
587 END IF;
588 --l_base_absence_sum := l_base_absence_sum + nvl(l_base_absence,0);
589 --l_base_holiday_sum := l_base_holiday_sum + nvl(l_base_holiday,0);
590 l_base_accrual_sum := l_base_accrual_sum + nvl(l_base_accrual,0);
591 l_base_hol_carry_over_sum := l_base_hol_carry_over_sum + nvl(l_base_holiday_carry_over,0);
592 END IF;
593 END LOOP;
594
595 FOR csr_utv IN csr_get_utv(g_business_group_id
596 ,g_reporting_date
597 ,'HU2') LOOP
598 IF csr_utv.accrual_plan IS NOT NULL THEN
599 OPEN csr_accrual_details(csr_utv.accrual_plan,g_business_group_id);
600 FETCH csr_accrual_details INTO l_accrual_plan_id_2
601 ,l_childcare_ele_type_id
602 ,l_co_formula_id_2;
603 CLOSE csr_accrual_details;
604 IF l_accrual_plan_id_2 IS NOT NULL THEN
605 IF per_accrual_calc_functions.check_assignment_enrollment(
606 csr_rec.assignment_id
607 ,l_childcare_ele_type_id
608 ,g_reporting_date) THEN
609
610 l_emp_enrolment := 1;
611
612 per_accrual_calc_functions.get_carry_over_values
613 (p_co_formula_id => l_co_formula_id_2
614 ,p_assignment_id => csr_rec.assignment_id
615 ,p_calculation_date => g_reporting_date
616 ,p_accrual_plan_id => l_accrual_plan_id_2
617 ,p_business_group_id => g_business_group_id
618 ,p_payroll_id => csr_rec.payroll_id
619 ,p_accrual_term => 'CURRENT'
620 ,p_effective_date => l_term_end_date
621 ,p_session_date => g_reporting_date
622 ,p_max_carry_over => l_max_co
623 ,p_expiry_date => l_child_care_exp_date
624 );
625
626 l_calculation_date := LEAST(l_term_end_date
627 ,NVL(csr_rec.termination_date
628 ,TO_DATE('31-12-4712','DD-MM-YYYY')));
629
630 per_accrual_calc_functions.get_net_accrual
631 (p_assignment_id => csr_rec.assignment_id
632 ,p_plan_id => l_accrual_plan_id_2
633 ,p_payroll_id => csr_rec.payroll_id
634 ,p_business_group_id => g_business_group_id
635 ,p_assignment_action_id => -1
636 ,p_calculation_date => l_calculation_date
637 ,p_accrual_start_date => NULL
638 ,p_accrual_latest_balance => NULL
639 ,p_calling_point => 'FRM'
640 ,p_start_date => l_start_date
641 ,p_end_date => l_end_date
642 ,p_accrual_end_date => l_accrual_end_date
643 ,p_accrual => l_child_care_accrual
644 ,p_net_entitlement => l_child_care_holiday);
645
646 /*************************************************************
647 l_child_care_holiday_prev :=
648 per_accrual_calc_functions.get_carry_over
649 (p_assignment_id => csr_rec.assignment_id
650 ,p_plan_id => l_accrual_plan_id_2
651 ,p_start_date => l_start_date
652 ,p_calculation_date => l_end_date
653 );
654
655
656 l_child_hol_carry_over :=
657 per_accrual_calc_functions.get_carry_over
658 (p_assignment_id => csr_rec.assignment_id
659 ,p_plan_id => l_accrual_plan_id_2
660 ,p_start_date => l_start_date
661 ,p_calculation_date => to_date('01-JAN'||to_char(g_reporting_date,'YYYY'),'dd/mm/yyyy')
662 );
663
664 l_child_care_holiday := nvl(l_child_care_holiday,0) -
665 nvl(l_child_care_holiday_prev,0);
666
667 l_child_care_absence := per_accrual_calc_functions.get_absence(
668 p_assignment_id => csr_rec.assignment_id
669 ,p_plan_id => l_accrual_plan_id_2
670 ,p_start_date => l_start_date
671 ,p_calculation_date => g_reporting_date -- to_date(to_char(l_child_care_exp_date,'dd/mm')||to_char(g_reporting_date,'YYYY'),'dd/mm/yyyy')
672 );
673 ********************************************************/
674
675 IF TO_NUMBER(TO_CHAR(g_reporting_date,'mmdd')) <= TO_NUMBER(TO_CHAR(l_child_care_exp_date,'mmdd')) THEN
676 l_child_hol_carry_over :=
677 per_accrual_calc_functions.get_carry_over
678 (p_assignment_id => csr_rec.assignment_id
679 ,p_plan_id => l_accrual_plan_id_2
680 ,p_start_date => l_start_date
681 ,p_calculation_date => to_date('01/01/'||to_char(g_reporting_date,'YYYY'),'dd/mm/yyyy')
682 );
683 l_normal_holiday_total := nvl(l_normal_holiday_total,0)
684 +nvl(l_child_care_accrual,0)
685 +nvl(l_child_hol_carry_over,0);
686 ELSE
687 l_child_hol_carry_over := 0;
688 l_normal_holiday_total := nvl(l_normal_holiday_total,0)+
689 nvl(l_child_care_accrual,0);
690 END IF;
691 ELSE
692 --l_child_care_absence := 0;
693 l_child_care_accrual := 0;
694 l_child_care_holiday := 0;
695 l_child_hol_carry_over := 0;
696 END IF;
697 END IF;
698 --l_child_care_absence_sum := l_child_care_absence_sum + nvl(l_child_care_absence,0);
699 l_child_care_accrual_sum := l_child_care_accrual_sum + nvl(l_child_care_accrual,0);
700 --l_child_care_holiday_sum := l_child_care_holiday_sum + nvl(l_child_care_holiday,0);
701 l_child_hol_carry_over_sum := l_child_hol_carry_over_sum + nvl(l_child_hol_carry_over,0);
702 END IF;
703 END LOOP;
704
705 FOR csr_utv IN csr_get_utv(g_business_group_id
706 ,g_reporting_date
707 ,'HU3') LOOP
708 IF csr_utv.accrual_plan IS NOT NULL THEN
709 OPEN csr_accrual_details(csr_utv.accrual_plan,g_business_group_id);
710 FETCH csr_accrual_details INTO l_accrual_plan_id_3
711 ,l_other_ele_type_id
712 ,l_co_formula_id_3;
713 CLOSE csr_accrual_details;
714
715 IF l_accrual_plan_id_3 IS NOT NULL THEN
716 IF per_accrual_calc_functions.check_assignment_enrollment(
717 csr_rec.assignment_id
718 ,l_other_ele_type_id
719 ,g_reporting_date) THEN
720
721 l_emp_enrolment := 1;
722
723 per_accrual_calc_functions.get_carry_over_values
724 (p_co_formula_id => l_co_formula_id_3
725 ,p_assignment_id => csr_rec.assignment_id
726 ,p_calculation_date => g_reporting_date
727 ,p_accrual_plan_id => l_accrual_plan_id_3
728 ,p_business_group_id => g_business_group_id
729 ,p_payroll_id => csr_rec.payroll_id
730 ,p_accrual_term => 'CURRENT'
731 ,p_effective_date => l_term_end_date
732 ,p_session_date => g_reporting_date
733 ,p_max_carry_over => l_max_co
734 ,p_expiry_date => l_additional_exp_date
735 );
736
737 l_calculation_date := LEAST(l_term_end_date
738 ,NVL(csr_rec.termination_date
739 ,TO_DATE('31-12-4712','DD-MM-YYYY')));
740
741 per_accrual_calc_functions.get_net_accrual
742 (p_assignment_id => csr_rec.assignment_id
743 ,p_plan_id => l_accrual_plan_id_3
744 ,p_payroll_id => csr_rec.payroll_id
745 ,p_business_group_id => g_business_group_id
746 ,p_assignment_action_id => -1
747 ,p_calculation_date => l_calculation_date
748 ,p_accrual_start_date => NULL
749 ,p_accrual_latest_balance => NULL
750 ,p_calling_point => 'FRM'
751 ,p_start_date => l_start_date
752 ,p_end_date => l_end_date
753 ,p_accrual_end_date => l_accrual_end_date
754 ,p_accrual => l_additional_accrual
755 ,p_net_entitlement => l_additional_holiday);
756
757 /*************************************************************
758 l_additional_holiday_prev :=
759 per_accrual_calc_functions.get_carry_over
760 (p_assignment_id => csr_rec.assignment_id
761 ,p_plan_id => l_accrual_plan_id_3
762 ,p_start_date => l_start_date
763 ,p_calculation_date => l_end_date
764 );
765
766
767 l_add_holiday_carry_over :=
768 per_accrual_calc_functions.get_carry_over
769 (p_assignment_id => csr_rec.assignment_id
770 ,p_plan_id => l_accrual_plan_id_3
771 ,p_start_date => l_start_date
772 ,p_calculation_date => to_date('01-JAN'||to_char(g_reporting_date,'YYYY'),'dd/mm/yyyy')
773 );
774
775 l_additional_holiday := nvl(l_additional_holiday,0) -
776 nvl(l_additional_holiday_prev,0);
777
778 l_additional_absence := per_accrual_calc_functions.get_absence(
779 p_assignment_id => csr_rec.assignment_id
780 ,p_plan_id => l_accrual_plan_id_3
781 ,p_start_date => l_start_date
782 ,p_calculation_date => g_reporting_date -- to_date(to_char(l_additional_exp_date,'dd/mm')||to_char(g_reporting_date,'YYYY'),'dd/mm/yyyy')
783 );
784 **********************************************************/
785 IF TO_NUMBER(TO_CHAR(g_reporting_date,'mmdd')) <= TO_NUMBER(TO_CHAR(l_additional_exp_date,'mmdd')) THEN
786 l_add_holiday_carry_over :=
787 per_accrual_calc_functions.get_carry_over
788 (p_assignment_id => csr_rec.assignment_id
789 ,p_plan_id => l_accrual_plan_id_3
790 ,p_start_date => l_start_date
791 ,p_calculation_date => to_date('01/01/'||to_char(g_reporting_date,'YYYY'),'dd/mm/yyyy')
792 );
793 l_normal_holiday_total := nvl(l_normal_holiday_total,0)
794 +nvl(l_additional_accrual,0)
795 +nvl(l_add_holiday_carry_over,0);
796
797 ELSE
798 l_add_holiday_carry_over := 0;
799 l_normal_holiday_total := nvl(l_normal_holiday_total,0)+
800 nvl(l_additional_accrual,0);
801 END IF;
802 ELSE
803 --l_additional_absence := 0;
804 l_additional_accrual := 0;
805 l_additional_holiday := 0;
806 l_add_holiday_carry_over := 0;
807 END IF;
808 END IF;
809 --l_additional_absence_sum := l_additional_absence_sum + nvl(l_additional_absence,0);
810 l_additional_accrual_sum := l_additional_accrual_sum + nvl(l_additional_accrual,0);
811 --l_additional_holiday_sum := l_additional_holiday_sum + nvl(l_additional_holiday,0);
812 l_add_hol_carry_over_sum := l_add_hol_carry_over_sum + nvl(l_add_holiday_carry_over,0);
813 END IF;
814 END LOOP;
815
816 FOR csr_utv IN csr_get_utv(g_business_group_id
817 ,g_reporting_date
818 ,'HU4') LOOP
819 IF csr_utv.accrual_plan IS NOT NULL THEN
820 OPEN csr_accrual_details(csr_utv.accrual_plan,g_business_group_id);
821 FETCH csr_accrual_details INTO l_accrual_plan_id_4
822 ,l_sickness_ele_type_id
823 ,l_co_formula_id_4;
824 CLOSE csr_accrual_details;
825
826 IF l_accrual_plan_id_4 IS NOT NULL THEN
827 IF per_accrual_calc_functions.check_assignment_enrollment(
828 csr_rec.assignment_id
829 ,l_sickness_ele_type_id
830 ,g_reporting_date) THEN
831
832 l_emp_enrolment := 1;
833
834 per_accrual_calc_functions.get_carry_over_values
835 (p_co_formula_id => l_co_formula_id_4
836 ,p_assignment_id => csr_rec.assignment_id
837 ,p_calculation_date => g_reporting_date
838 ,p_accrual_plan_id => l_accrual_plan_id_4
839 ,p_business_group_id => g_business_group_id
840 ,p_payroll_id => csr_rec.payroll_id
841 ,p_accrual_term => 'CURRENT'
842 ,p_effective_date => l_term_end_date
843 ,p_session_date => g_reporting_date
844 ,p_max_carry_over => l_max_co
845 ,p_expiry_date => l_dummy
846 );
847
848 l_calculation_date := LEAST(l_term_end_date
849 ,NVL(csr_rec.termination_date
850 ,TO_DATE('31-12-4712','DD-MM-YYYY')));
851
852 per_accrual_calc_functions.get_net_accrual
853 (p_assignment_id => csr_rec.assignment_id
854 ,p_plan_id => l_accrual_plan_id_4
855 ,p_payroll_id => csr_rec.payroll_id
856 ,p_business_group_id => g_business_group_id
857 ,p_assignment_action_id => -1
858 ,p_calculation_date => l_calculation_date
859 ,p_accrual_start_date => NULL
860 ,p_accrual_latest_balance => NULL
861 ,p_calling_point => 'FRM'
862 ,p_start_date => l_start_date
863 ,p_end_date => l_end_date
864 ,p_accrual_end_date => l_accrual_end_date
865 ,p_accrual => l_sickness_accrual
866 ,p_net_entitlement => l_sickness_holiday);
867 ELSE
868 l_sickness_holiday := 0;
869 l_sickness_accrual := 0;
870 END IF;
871 END IF;
872 --l_sickness_holiday_sum := l_sickness_holiday_sum + nvl(l_sickness_holiday,0);
873 l_sickness_accrual_sum := l_sickness_accrual_sum + nvl(l_sickness_accrual,0);
874 END IF;
875 END LOOP;
876
877 l_normal_paid_holiday_prev := nvl(l_base_hol_carry_over_sum,0) +
878 nvl(l_child_hol_carry_over_sum,0) +
879 nvl(l_add_hol_carry_over_sum,0);
880
881 IF l_emp_enrolment = 1 THEN
882
883 --
884 SELECT pay_assignment_actions_s.NEXTVAL
885 INTO l_actid
886 FROM dual;
887 --
888 hr_nonrun_asact.insact(l_actid,csr_rec.assignment_id,p_actid,chunk,NULL);
889 --
890 pay_action_information_api.create_action_information (
891 p_action_information_id => l_action_info_id
892 , p_action_context_id => l_actid
893 , p_action_context_type => 'AAP'
894 , p_object_version_NUMBER => l_ovn
895 , p_assignment_id => csr_rec.assignment_id
896 , p_effective_date => l_effective_date
897 , p_action_information_category => 'HU_ABSENCE_REPORT'
898 , p_action_information4 => csr_rec.company_name
899 , p_action_information5 => csr_rec.full_name
900 , p_action_information6 => csr_rec.organization
901 , p_action_information7 => l_location_code
902 , p_action_information8 => csr_rec.emp_no
903 , p_action_information9 => fnd_date.date_to_displaydate(csr_rec.date_of_birth)
904 , p_action_information10 => l_job_name
905 , p_action_information11 => fnd_date.date_to_displaydate(csr_rec.hire_date)
906 , p_action_information12 => fnd_date.date_to_displaydate(l_sus_asg_end_dt)
907 , p_action_information13 => to_char(g_reporting_date,'YYYY')
908 , p_action_information14 => l_base_accrual_sum
909 , p_action_information15 => l_child_care_accrual_sum
910 , p_action_information16 => l_additional_accrual_sum
911 , p_action_information17 => l_normal_paid_holiday_prev
912 , p_action_information18 => l_sickness_accrual_sum
913 , p_action_information19 => l_normal_holiday_total
914 , p_action_information20 => fnd_date.date_to_displaydate(g_reporting_date)
915 , p_action_information21 => csr_rec.people_group_id);
916 ELSE
917 fnd_file.put_line(fnd_file.log,substr(csr_rec.full_name,1,30)
918 || ' ['
919 || substr(csr_rec.emp_no,1,30) || '] : '
920 || fnd_message.get_string('PER','HR_HU_ABS_REP_EXC_LIST'));
921 END IF;
922 END IF;
923 END LOOP;
924 END action_creation_code;
925 --------------------------------------------------------------------------------
926 -- GET_CHILDREN_INFO
927 --------------------------------------------------------------------------------
928 FUNCTION get_children_info( p_assignment_id IN NUMBER
929 ,p_business_group_id IN NUMBER
930 ,p_start_date IN DATE
931 ,p_end_date IN DATE
932 ,p_no_child_less_16 OUT NOCOPY NUMBER
933 ,p_no_child_16 OUT NOCOPY NUMBER
934 ,p_child_dob1 OUT NOCOPY DATE
935 ,p_child_dob2 OUT NOCOPY DATE
936 ,p_child_dob3 OUT NOCOPY DATE
937 ) RETURN NUMBER IS
938 --
939 CURSOR csr_child_less_then_16 is
940 SELECT count(*)
941 FROM PER_CONTACT_RELATIONSHIPS pcr
942 ,per_all_people_f pap
943 ,per_all_assignments_f paa
944 WHERE pcr.person_id = paa.person_id
945 AND pap.business_group_id = p_business_group_id
946 AND pcr.business_group_id = p_business_group_id
947 AND pcr.cont_information3 = 'Y'
948 AND paa.assignment_id = p_assignment_id
949 AND pcr.contact_person_id = pap.person_id
950 AND pcr.contact_type IN ('C','A')
951 AND p_start_date BETWEEN decode(pcr.date_start,NULL,to_date('01010001','ddmmyyyy'),pcr.date_start)
952 AND decode(pcr.date_end,NULL,to_date('01014712','ddmmyyyy'),pcr.date_end)
953 AND p_start_date BETWEEN pap.effective_start_date AND pap.effective_end_date
954 AND p_start_date BETWEEN paa.effective_start_date AND paa.effective_end_date
955 AND months_between(p_end_date,pap.date_of_birth)/12 < 16;
956 --
957 CURSOR csr_child_16 is
958 SELECT count(*)
959 FROM PER_CONTACT_RELATIONSHIPS pcr
960 ,per_all_people_f pap
961 ,per_all_assignments_f paa
962 WHERE pcr.person_id = paa.person_id
963 AND pap.business_group_id = p_business_group_id
964 AND pcr.business_group_id = p_business_group_id
965 AND pcr.cont_information3 = 'Y'
966 AND paa.assignment_id = p_assignment_id
967 AND pcr.contact_person_id = pap.person_id
968 AND pcr.contact_type IN ('C','A')
969 AND p_start_date BETWEEN decode(pcr.date_start,NULL,to_date('01010001','ddmmyyyy'),pcr.date_start)
970 AND decode(pcr.date_end,NULL,to_date('01014712','ddmmyyyy'),pcr.date_end)
971 AND p_start_date BETWEEN pap.effective_start_date AND pap.effective_end_date
972 AND p_start_date BETWEEN paa.effective_start_date AND paa.effective_end_date
973 AND to_char(pap.date_of_birth,'mmdd') BETWEEN to_char(p_start_date,'mmdd')
974 AND to_char(p_end_date,'mmdd')
975 AND to_char(p_end_date,'yyyy') - to_char(pap.date_of_birth,'yyyy') = 16;
976 --
977 CURSOR csr_child_16_dob is
978 SELECT pap.date_of_birth dob
979 FROM per_contact_relationships pcr
980 ,per_all_people_f pap
981 ,per_all_assignments_f paa
982 WHERE pcr.person_id = paa.person_id
983 AND pap.business_group_id = p_business_group_id
984 AND pcr.business_group_id = p_business_group_id
985 AND pcr.cont_information3 = 'Y'
986 AND paa.assignment_id = p_assignment_id
987 AND pcr.contact_person_id = pap.person_id
988 AND pcr.contact_type IN ('C','A')
989 AND p_start_date between decode(pcr.date_start,NULL,to_date('01010001','ddmmyyyy'),pcr.date_start)
990 AND decode(pcr.date_end,NULL,to_date('01014712','ddmmyyyy'),pcr.date_end)
991 AND p_start_date between pap.effective_start_date AND pap.effective_end_date
992 AND p_start_date between paa.effective_start_date AND paa.effective_end_date
993 AND to_char(pap.date_of_birth,'mmdd') between to_char(p_start_date,'mmdd') AND to_char(p_end_date,'mmdd')
994 AND to_char(p_end_date,'yyyy') - to_char(pap.date_of_birth,'yyyy') = 16
995 ORDER BY pap.date_of_birth desc;
996 --
997 mcnt NUMBER;
998 --
999 BEGIN
1000 OPEN csr_child_less_then_16;
1001 FETCH csr_child_less_then_16 INTO p_no_child_less_16;
1002 CLOSE csr_child_less_then_16;
1003 OPEN csr_child_16;
1004 FETCH csr_child_16 INTO p_no_child_16;
1005 CLOSE csr_child_16;
1006
1007 mcnt := 1;
1008
1009 FOR child_info IN csr_child_16_dob LOOP
1010 IF mcnt = 1 THEN
1011 p_child_dob1 := to_date(to_char(child_info.dob,'dd/mm/')||
1012 to_char(p_start_date,'yyyy'),'dd/mm/yyyy');
1013 ELSIF mcnt = 2 THEN
1014 p_child_dob2 := to_date(to_char(child_info.dob,'dd/mm/')||
1015 to_char(p_start_date,'yyyy'),'dd/mm/yyyy');
1016 ELSIF mcnt = 2 THEN
1017 p_child_dob3 := to_date(to_char(child_info.dob,'dd/mm/')||
1018 to_char(p_start_date,'yyyy'),'dd/mm/yyyy');
1019 END IF;
1020 mcnt := mcnt+1;
1021
1022 END LOOP;
1023 RETURN 0;
1024 END get_children_info;
1025 --------------------------------------------------------------------------------
1026 -- GET_PAYROLL_PERIOD
1027 --------------------------------------------------------------------------------
1028 FUNCTION get_payroll_Period
1029 (p_payroll_id IN NUMBER
1030 ,p_calculation_date IN DATE
1031 ,p_accrual_frequency OUT NOCOPY VARCHAR2
1032 ,p_accrual_multiplier OUT NOCOPY NUMBER
1033 ) RETURN NUMBER IS
1034 --
1035 CURSOR csr_pay_period_count IS
1036 SELECT ptp.number_per_fiscal_year
1037 ,ptp.period_type
1038 FROM pay_payrolls_f ppf
1039 ,per_time_period_types ptp
1040 WHERE payroll_id = p_payroll_id
1041 AND ptp.period_type = ppf.period_type
1042 AND p_calculation_date
1043 BETWEEN ppf.effective_start_date
1044 AND ppf.effective_end_date ;
1045 --
1046 l_periods NUMBER;
1047 l_period_types VARCHAR2(30);
1048 --
1049 BEGIN
1050 OPEN csr_pay_period_count;
1051 FETCH csr_pay_period_count INTO l_periods, l_period_types;
1052 CLOSE csr_pay_period_count;
1053
1054 IF l_period_types IN ('Bi-Month','Calendar Month','Semi-Month'
1055 ,'Year','Semi-Year','Quarter') THEN
1056 p_accrual_frequency := 'M';
1057 p_accrual_multiplier := 12/l_periods ;
1058 ELSIF l_period_types IN ('Bi-Week','Week','Lunar Month') THEN
1059 p_accrual_frequency := 'W';
1060 p_accrual_multiplier := 52/l_periods ;
1061 END IF;
1062
1063 RETURN l_periods;
1064 END get_payroll_Period;
1065 --------------------------------------------------------------------------------
1066 -- WORKING_DAY_COUNT
1067 --------------------------------------------------------------------------------
1068 FUNCTION working_day_count
1069 (p_assignment_id IN NUMBER
1070 ,p_business_group_id IN NUMBER
1071 ,p_start_date IN DATE
1072 ,p_end_date IN DATE) RETURN NUMBER IS
1073 l_is_wrking_day VARCHAR2(1);
1074 l_error_code NUMBER;
1075 l_error_msg VARCHAR2(2000);
1076 l_date DATE;
1077 l_cnt NUMBER := 0;
1078 BEGIN
1079 l_date := p_start_date;
1080 LOOP
1081 EXIT WHEN l_date > p_end_date;
1082 l_is_wrking_day := PQP_SCHEDULE_CALCULATION_PKG.is_working_day
1083 (p_assignment_id => p_assignment_id
1084 ,p_business_group_id => p_business_group_id
1085 ,p_date => l_date
1086 ,p_error_code => l_error_code
1087 ,p_error_message => l_error_msg
1088 ,p_default_wp => NULL
1089 ,p_override_wp => NULL
1090 );
1091
1092 IF l_is_wrking_day = 'Y' THEN
1093 l_cnt := l_cnt + 1;
1094 END IF;
1095 l_date := l_date + 1;
1096 END LOOP;
1097 RETURN l_cnt;
1098 END working_day_count;
1099 --------------------------------------------------------------------------------
1100 -- GET_PERSON_DOB
1101 --------------------------------------------------------------------------------
1102 FUNCTION get_person_dob
1103 (p_assignment_id IN NUMBER
1104 ,p_calculation_date IN date ) RETURN Date is
1105 CURSOR csr_person_dob IS
1106 SELECT pap.date_of_birth
1107 FROM per_all_people_f pap
1108 ,per_all_assignments_f paa
1109 WHERE paa.assignment_id = p_assignment_id
1110 AND paa.person_id = pap.person_id
1111 AND p_calculation_date
1112 BETWEEN paa.effective_start_date
1113 AND paa.effective_end_date
1114 AND p_calculation_date
1115 BETWEEN pap.effective_start_date
1116 AND pap.effective_end_date;
1117 l_dob date;
1118 BEGIN
1119 OPEN csr_person_dob;
1120 FETCH csr_person_dob INTO l_dob;
1121 CLOSE csr_person_dob;
1122 RETURN l_dob;
1123 END get_person_dob;
1124 --------------------------------------------------------------------------------
1125 -- GET_PREV_EMP_SICKNESS_LEAVE
1126 --------------------------------------------------------------------------------
1127 FUNCTION get_prev_emp_sickness_leave(p_assignment_id IN NUMBER
1128 ,p_business_group_id IN NUMBER
1129 ,p_termination_year IN VARCHAR2
1130 ,p_prev_emp OUT NOCOPY VARCHAR2)
1131 RETURN NUMBER
1132 IS
1133 CURSOR csr_pre_emp_sickness_holiday(c_person_id NUMBER) IS
1134 SELECT pem_information1
1135 FROM per_previous_employers
1136 WHERE business_group_id = p_business_group_id
1137 AND person_id = c_person_id
1138 AND to_char(end_date,'YYYY') = p_termination_year
1139 ORDER BY end_date DESC;
1140
1141 CURSOR csr_get_person_id IS
1142 SELECT person_id FROM per_All_assignments_f
1143 WHERE assignment_id = p_assignment_id;
1144
1145 l_sickness_leave NUMBER;
1146 l_person_id NUMBER;
1147
1148 BEGIN
1149 OPEN csr_get_person_id;
1150 FETCH csr_get_person_id into l_person_id;
1151 CLOSE csr_get_person_id;
1152
1153 OPEN csr_pre_emp_sickness_holiday(l_person_id);
1154 FETCH csr_pre_emp_sickness_holiday into l_sickness_leave;
1155 IF NOT csr_pre_emp_sickness_holiday%Found THEN
1156 p_prev_emp := 'N';
1157 ELSE
1158 p_prev_emp := 'Y';
1159 END IF;
1160 CLOSE csr_pre_emp_sickness_holiday;
1161 return nvl(l_sickness_leave,0);
1162
1163 END get_prev_emp_sickness_leave;
1164 --------------------------------------------------------------------------------
1165 -- GET_DISABILITY
1166 --------------------------------------------------------------------------------
1167 FUNCTION get_disability(p_assignment_id NUMBER
1168 ,p_business_group_id NUMBER
1169 ,p_period_start_date DATE
1170 ,p_period_end_date DATE) RETURN NUMBER IS
1171 CURSOR csr_disability is
1172 SELECT pdf.effective_start_date,pdf.effective_end_date
1173 FROM per_disabilities_f pdf ,per_all_people_f papf, per_all_assignments_f paaf
1174 WHERE paaf.assignment_id=p_assignment_id
1175 AND paaf.business_group_id=p_business_group_id
1176 AND paaf.person_id=papf.person_id
1177 AND papf.person_id=pdf.person_id
1178 AND pdf.dis_information1='Y'
1179 AND p_period_start_date between papf.effective_start_date and papf.effective_end_date
1180 AND p_period_start_date between paaf.effective_start_date and paaf.effective_end_date
1181 AND pdf.effective_start_date <= p_period_end_date
1182 AND pdf.effective_end_date>=p_period_start_date;
1183 l_blind_days NUMBER:=0;
1184 l_days NUMBER:=0;
1185 p_disability_start_date DATE;
1186 p_disability_end_date DATE;
1187 BEGIN
1188
1189 OPEN csr_disability;
1190 LOOP
1191 FETCH csr_disability INTO p_disability_start_date,p_disability_end_date;
1192 EXIT WHEN csr_disability%NOTFOUND;
1193 IF p_disability_start_date>=p_period_start_date AND
1194 p_disability_start_date<=p_period_end_date AND
1195 p_disability_end_date>=p_period_end_date THEN
1196 l_blind_days := PER_HU_ABS_REP_ARCHIVE_PKG.working_day_count(p_assignment_id
1197 ,p_business_group_id
1198 ,p_disability_start_date
1199 ,p_period_end_date);
1200 ELSIF p_disability_start_date>=p_period_start_date AND
1201 p_disability_start_date<=p_period_end_date AND
1202 p_disability_end_date<p_period_end_date THEN
1203 l_blind_days := PER_HU_ABS_REP_ARCHIVE_PKG.working_day_count(p_assignment_id
1204 ,p_business_group_id
1205 ,p_disability_start_date
1206 ,p_disability_end_date);
1207 ELSIF p_disability_start_date<p_period_start_date AND
1208 p_disability_end_date<=p_period_end_date THEN
1209 l_blind_days := PER_HU_ABS_REP_ARCHIVE_PKG.working_day_count(p_assignment_id
1210 ,p_business_group_id
1211 ,p_period_start_date
1212 ,p_disability_end_date);
1213 ELSIF p_disability_start_date<=p_period_start_date AND
1214 p_disability_end_date>=p_period_end_date THEN
1215 l_blind_days := PER_HU_ABS_REP_ARCHIVE_PKG.working_day_count(p_assignment_id
1216 ,p_business_group_id
1217 ,p_period_start_date
1218 ,p_period_end_date);
1219 END IF;
1220 l_days := l_blind_days + l_days ;
1221 END LOOP;
1222 CLOSE csr_disability;
1223 RETURN l_days;
1224 END get_disability;
1225 --------------------------------------------------------------------------------
1226 --GET_JOB_INFO
1227 --------------------------------------------------------------------------------
1228 FUNCTION get_job_info(p_assignment_id NUMBER
1229 ,p_business_group_id NUMBER
1230 ,p_period_start_date DATE
1231 ,p_period_end_date DATE) RETURN NUMBER is
1232 CURSOR csr_job is
1233 SELECT paaf.effective_start_date,paaf.effective_end_date,pj.date_to
1234 FROM per_all_assignments_f paaf,per_jobs pj
1235 WHERE paaf.assignment_id=p_assignment_id
1236 AND paaf.business_group_id=p_business_group_id
1237 AND paaf.job_id=pj.job_id
1238 AND pj.job_information3='Y'
1239 AND paaf.effective_start_date <= p_period_end_date
1240 AND paaf.effective_end_date>= p_period_start_date
1241 AND pj.date_FROM <= p_period_end_date
1242 AND nvl(pj.date_to,to_date('31-12-4712','dd-mm-yyyy')) >= p_period_start_date;
1243 l_job_days NUMBER:=0;
1244 l_days NUMBER:=0;
1245 p_job_start_date DATE;
1246 p_job_end_date DATE;
1247 p_date_to DATE;
1248 BEGIN
1249 OPEN csr_job;
1250 LOOP
1251 FETCH csr_job into p_job_start_date,p_job_end_date,p_date_to;
1252 EXIT WHEN csr_job%NOTFOUND;
1253 IF p_job_start_date>=p_period_start_date AND p_job_start_date<=p_period_end_date
1254 AND p_job_end_date>=p_period_end_date THEN
1255 -- check for date_to
1256 IF p_date_to <= p_period_end_date THEN
1257 l_job_days := PER_HU_ABS_REP_ARCHIVE_PKG.working_day_count(p_assignment_id
1258 ,p_business_group_id
1259 ,p_job_start_date
1260 ,p_date_to);
1261 ELSE
1262 l_job_days := PER_HU_ABS_REP_ARCHIVE_PKG.working_day_count(p_assignment_id
1263 ,p_business_group_id
1264 ,p_job_start_date
1265 ,p_period_end_date);
1266 END IF;
1267 ELSIF p_job_start_date >= p_period_start_date AND
1268 p_job_start_date <= p_period_end_date AND
1269 p_job_end_date < p_period_end_date THEN
1270 -- check for date_to
1271 IF p_date_to <= p_job_end_date THEN
1272 l_job_days := PER_HU_ABS_REP_ARCHIVE_PKG.working_day_count(p_assignment_id
1273 ,p_business_group_id
1274 ,p_job_start_date
1275 ,p_date_to);
1276 ELSE
1277 l_job_days := PER_HU_ABS_REP_ARCHIVE_PKG.working_day_count(p_assignment_id
1278 ,p_business_group_id
1279 ,p_job_start_date
1280 ,p_job_end_date);
1281 END IF;
1282 ELSIF p_job_start_date < p_period_start_date AND
1283 p_job_end_date <= p_period_end_date THEN
1284 -- check for date_to
1285 IF p_date_to <=p_job_end_date THEN
1286 l_job_days := PER_HU_ABS_REP_ARCHIVE_PKG.working_day_count(p_assignment_id
1287 ,p_business_group_id
1288 ,p_period_start_date
1289 ,p_date_to);
1290 ELSE
1291 l_job_days := PER_HU_ABS_REP_ARCHIVE_PKG.working_day_count(p_assignment_id
1292 ,p_business_group_id
1293 ,p_period_start_date
1294 ,p_job_end_date);
1295 END IF;
1296 ELSIF p_job_start_date <= p_period_start_date AND
1297 p_job_end_date >= p_period_end_date THEN
1298 l_job_days := PER_HU_ABS_REP_ARCHIVE_PKG.working_day_count(p_assignment_id
1299 ,p_business_group_id
1300 ,p_period_start_date
1301 ,p_period_end_date);
1302 END IF;
1303 l_days := l_job_days + l_days ;
1304 END LOOP;
1305 CLOSE csr_job;
1306 RETURN l_days;
1307 END get_job_info;
1308 --------------------------------------------------------------------------------
1309 -- CHK_ENTRY_IN_ACCRUAL_PLAN
1310 --------------------------------------------------------------------------------
1311 FUNCTION chk_entry_in_accrual_plan
1312 (p_entry_val IN VARCHAR2
1313 ,p_message OUT NOCOPY VARCHAR2) RETURN VARCHAR2 IS
1314 --
1315 l_found_value VARCHAR2(1);
1316 l_msg VARCHAR2(255);
1317 --
1318 BEGIN
1319 --
1320 l_msg := ' ';
1321 l_found_value := 'N';
1322 --
1323 IF p_entry_val IN ('HU1','HU2','HU3','HU4') THEN
1324 l_found_value := 'Y';
1325 ELSE
1326 l_msg := fnd_message.get_string('PER','HR_HU_UDT_VAL_CHECK');
1327 l_found_value := 'N';
1328 END IF;
1329 --
1330 p_message := l_msg;
1331 RETURN l_found_value;
1332 --
1333 END chk_entry_in_accrual_plan;
1334 --
1335 END per_hu_abs_rep_archive_pkg;