[Home] [Help]
PACKAGE BODY: APPS.PAY_SE_SLSA_ARCHIVE
Source
1 PACKAGE BODY pay_se_slsa_archive AS
2 /* $Header: pyseslsa.pkb 120.1 2007/08/21 05:29:27 psingla noship $ */
3 g_debug BOOLEAN := hr_utility.debug_enabled;
4 g_package VARCHAR2 (33) := 'PAY_SE_SLSA_ARCHIVE.';
5 g_payroll_action_id NUMBER;
6 -- Globals to pick up all the parameter
7 g_business_group_id NUMBER;
8 g_effective_date DATE;
9 g_legal_employer_id NUMBER;
10 g_local_unit_id NUMBER;
11 g_request_for VARCHAR2 (20);
12 g_start_date DATE;
13 g_end_date DATE;
14 g_lower_age_group NUMBER := 29;
15 -- g_middle_start_age_group NUMBER := 30;
16 -- g_middle_end_age_group NUMBER := 49;
17 g_upper_age_group NUMBER := 50;
18 g_no_of_long_leave NUMBER := 60;
19 /*
20 1. Lower Age Group - 29 and below
21 2. Middle Age Group - Between 30 and 49
22 3. Upper Age Group - 50 and Above */
23
24 --End of Globals to pick up all the parameter
25
26 /* GET PARAMETER */
27 FUNCTION get_parameter (
28 p_parameter_string IN VARCHAR2,
29 p_token IN VARCHAR2,
30 p_segment_number IN NUMBER DEFAULT NULL
31 )
32 RETURN VARCHAR2 IS
33 l_parameter pay_payroll_actions.legislative_parameters%TYPE := NULL;
34 l_start_pos NUMBER;
35 l_delimiter VARCHAR2 (1) := ' ';
36 l_proc VARCHAR2 (40) := g_package || ' get parameter ';
37 BEGIN
38 --
39 IF g_debug THEN
40 hr_utility.set_location (' Entering Function GET_PARAMETER', 10);
41 END IF;
42
43 l_start_pos := instr (' ' || p_parameter_string, l_delimiter || p_token || '=');
44
45 --
46 IF l_start_pos = 0 THEN
47 l_delimiter := '|';
48 l_start_pos := instr (' ' || p_parameter_string, l_delimiter || p_token || '=');
49 END IF;
50
51 IF l_start_pos <> 0 THEN
52 l_start_pos := l_start_pos + LENGTH (p_token || '=');
53 l_parameter := substr (
54 p_parameter_string,
55 l_start_pos,
56 instr (p_parameter_string || ' ', l_delimiter, l_start_pos) - (l_start_pos)
57 );
58
59 IF p_segment_number IS NOT NULL THEN
60 l_parameter := ':' || l_parameter || ':';
61 l_parameter := substr (
62 l_parameter,
63 instr (l_parameter, ':', 1, p_segment_number) + 1,
64 instr (l_parameter, ':', 1, p_segment_number + 1) - 1
65 - instr (l_parameter, ':', 1, p_segment_number)
66 );
67 END IF;
68 END IF;
69
70 --
71 IF g_debug THEN
72 hr_utility.set_location (' Leaving Function GET_PARAMETER', 20);
73 END IF;
74
75 RETURN l_parameter;
76 END;
77 /* GET ALL PARAMETERS */
78 PROCEDURE get_all_parameters (
79 p_payroll_action_id IN NUMBER -- In parameter
80 ,
81 p_business_group_id OUT NOCOPY NUMBER -- Core parameter
82 ,
83 p_effective_date OUT NOCOPY DATE -- Core parameter
84 ,
85 p_legal_employer_id OUT NOCOPY NUMBER -- User parameter
86 ,
87 p_request_for_all_or_not OUT NOCOPY VARCHAR2 -- User parameter
88 ,
89 p_start_date OUT NOCOPY DATE -- User parameter
90 ,
91 p_end_date OUT NOCOPY DATE -- User parameter
92 ) IS
93 CURSOR csr_parameter_info (
94 p_payroll_action_id NUMBER
95 ) IS
96 SELECT to_number (
97 substr (
98 pay_se_slsa_archive.get_parameter (legislative_parameters, 'LEGAL_EMPLOYER'),
99 1,
100 LENGTH (pay_se_slsa_archive.get_parameter (legislative_parameters, 'LEGAL_EMPLOYER')) - 1
101 )
102 ) legal,
103 substr (
104 pay_se_slsa_archive.get_parameter (legislative_parameters, 'REQUEST_FOR'),
105 1,
106 LENGTH (pay_se_slsa_archive.get_parameter (legislative_parameters, 'REQUEST_FOR')) - 1
107 ) request_for,
108 (pay_se_slsa_archive.get_parameter (legislative_parameters, 'EFFECTIVE_START_DATE')) eff_start_date,
109 (pay_se_slsa_archive.get_parameter (legislative_parameters, 'EFFECTIVE_END_DATE'))
110 eff_end_date,
111 effective_date effective_date, business_group_id bg_id
112 FROM pay_payroll_actions
113 WHERE payroll_action_id = p_payroll_action_id;
114
115 lr_parameter_info csr_parameter_info%ROWTYPE;
116 l_proc VARCHAR2 (240) := g_package || ' GET_ALL_PARAMETERS ';
117 BEGIN
118 fnd_file.put_line (fnd_file.LOG, 'Entering Procedure GET_ALL_PARAMETER ');
119 OPEN csr_parameter_info (p_payroll_action_id);
120 --FETCH csr_parameter_info into lr_parameter_info;
121 FETCH csr_parameter_info INTO lr_parameter_info;
122 CLOSE csr_parameter_info;
123 p_legal_employer_id := lr_parameter_info.legal;
124 p_request_for_all_or_not := lr_parameter_info.request_for;
125 p_start_date := fnd_date.canonical_to_date (lr_parameter_info.eff_start_date);
126 p_end_date := fnd_date.canonical_to_date (lr_parameter_info.eff_end_date);
127 p_effective_date := lr_parameter_info.effective_date;
128 p_business_group_id := lr_parameter_info.bg_id;
129
130 IF g_debug THEN
131 hr_utility.set_location (' Leaving Procedure GET_ALL_PARAMETERS', 30);
132 END IF;
133 END get_all_parameters;
134 /* procedure to get the working hours and day for the business group */
135 PROCEDURE get_schedule_duration (
136 p_start_date IN DATE,
137 p_end_date IN DATE,
138 p_days_or_hours IN VARCHAR2,
139 p_duration IN OUT NOCOPY NUMBER
140 ) IS
141 -- l_schedule_source VARCHAR2(10);
142 l_schedule cac_avlblty_time_varray;
143 l_msg_count NUMBER;
144 l_return NUMBER;
145 l_idx NUMBER;
146 l_ref_date DATE;
147 l_first_band BOOLEAN;
148 l_day_start_time VARCHAR2 (5);
149 l_day_end_time VARCHAR2 (5);
150 l_start_time VARCHAR2 (5);
151 l_end_time VARCHAR2 (5);
152 --
153 l_start_date DATE;
154 l_end_date DATE;
155 --l_schedule cac_avlblty_time_varray;
156 l_schedule_source VARCHAR2 (10);
157 l_return_status VARCHAR2 (1);
158 l_return_message VARCHAR2 (2000);
159 --
160 l_time_start VARCHAR2 (10);
161 l_time_end VARCHAR2 (10);
162 --
163 e_bad_time_format EXCEPTION;
164 BEGIN
165 cac_avlblty_pub.get_schedule (
166 p_api_version => 1.0,
167 p_init_msg_list => 'F',
168 p_object_type => 'BUSINESS_GROUP',
169 p_object_id => g_business_group_id,
170 p_start_date_time => p_start_date,
171 p_end_date_time => p_end_date,
172 p_schedule_category => NULL,
173 p_include_exception => 'Y',
174 p_busy_tentative => 'FREE',
175 x_schedule => l_schedule,
176 x_return_status => l_return_status,
177 x_msg_count => l_msg_count,
178 x_msg_data => l_return_message
179 );
180
181 IF l_return_status = 'S' THEN
182 l_idx := l_schedule.FIRST;
183
184 IF p_days_or_hours = 'D' THEN
185 --
186 l_first_band := TRUE ;
187 l_ref_date := NULL;
188
189 WHILE l_idx IS NOT NULL
190 LOOP
191 -- l_schedule(l_idx).FREE_BUSY_TYPE := 'FREE' ;
192 IF l_schedule (l_idx).free_busy_type IS NOT NULL THEN
193 IF l_schedule (l_idx).free_busy_type = 'FREE' THEN
194 -- dbms_output.put_line('Inside FREE_BUSY_TYPE' ||l_schedule(l_idx).FREE_BUSY_TYPE ) ;
195 IF l_first_band THEN
196 l_first_band := FALSE ;
197 l_ref_date := trunc (l_schedule (l_idx).start_date_time);
198 hr_utility.set_location ('start date time ' || l_schedule (l_idx).start_date_time, 20);
199 hr_utility.set_location ('end date time ' || l_schedule (l_idx).end_date_time, 20);
200
201 IF (trunc (l_schedule (l_idx).end_date_time) = trunc (l_schedule (l_idx).start_date_time)) THEN
202 p_duration := p_duration
203 + (trunc (l_schedule (l_idx).end_date_time)
204 - trunc (l_schedule (l_idx).start_date_time) + 1
205 );
206 ELSE
207 p_duration := p_duration
208 + (trunc (l_schedule (l_idx).end_date_time)
209 - trunc (l_schedule (l_idx).start_date_time)
210 );
211 END IF;
212 ELSE -- not first time
213 IF trunc (l_schedule (l_idx).start_date_time) = l_ref_date THEN
214 p_duration := p_duration
215 + (trunc (l_schedule (l_idx).end_date_time)
216 - trunc (l_schedule (l_idx).start_date_time)
217 );
218 ELSE
219 l_ref_date := trunc (l_schedule (l_idx).end_date_time);
220
221 IF (trunc (l_schedule (l_idx).end_date_time) = trunc (l_schedule (l_idx).start_date_time)) THEN
222 p_duration := p_duration
223 + (trunc (l_schedule (l_idx).end_date_time)
224 - trunc (l_schedule (l_idx).start_date_time) + 1
225 );
226 ELSE
227 p_duration := p_duration
228 + (trunc (l_schedule (l_idx).end_date_time)
229 - trunc (l_schedule (l_idx).start_date_time)
230 );
231 END IF;
232 END IF;
233 END IF;
234 END IF;
235 END IF;
236
237 l_idx := l_schedule (l_idx).next_object_index;
238 END LOOP;
239 --
240 ELSE -- p_days_or_hours is 'H'
241 --
242 l_day_start_time := '00:00';
243 l_day_end_time := '23:59';
244
245 WHILE l_idx IS NOT NULL
246 LOOP
247 IF l_schedule (l_idx).free_busy_type IS NOT NULL THEN
248 IF l_schedule (l_idx).free_busy_type = 'FREE' THEN
249 IF l_schedule (l_idx).end_date_time < l_schedule (l_idx).start_date_time THEN
250 -- Skip this invalid slot which ends before it starts
251 NULL;
252 ELSE
253 IF trunc (l_schedule (l_idx).end_date_time) > trunc (l_schedule (l_idx).start_date_time) THEN
254 -- Start and End on different days
255 --
256 -- Get first day hours
257 l_start_time := to_char (l_schedule (l_idx).start_date_time, 'HH24:MI');
258
259 SELECT p_duration
260 + (((substr (l_day_end_time, 1, 2) * 60 + substr (l_day_end_time, 4, 2))
261 - (substr (l_start_time, 1, 2) * 60 + substr (l_start_time, 4, 2))
262 )
263 / 60
264 )
265 INTO p_duration
266 FROM dual;
267
268 --
269 -- Get last day hours
270 l_end_time := to_char (l_schedule (l_idx).end_date_time, 'HH24:MI');
271
272 SELECT p_duration
273 + (((substr (l_end_time, 1, 2) * 60 + substr (l_end_time, 4, 2))
274 - (substr (l_day_start_time, 1, 2) * 60 + substr (l_day_start_time, 4, 2)) + 1
275 )
276 / 60
277 )
278 INTO p_duration
279 FROM dual;
280
281 --
282 -- Get between full day hours
283 SELECT p_duration
284 + ((trunc (l_schedule (l_idx).end_date_time) - trunc (l_schedule (l_idx).start_date_time) - 1)
285 * 24
286 )
287 INTO p_duration
288 FROM dual;
289 ELSE
290 -- Start and End on same day
291 l_start_time := to_char (l_schedule (l_idx).start_date_time, 'HH24:MI');
292 l_end_time := to_char (l_schedule (l_idx).end_date_time, 'HH24:MI');
293
294 SELECT p_duration
295 + (((substr (l_end_time, 1, 2) * 60 + substr (l_end_time, 4, 2))
296 - (substr (l_start_time, 1, 2) * 60 + substr (l_start_time, 4, 2))
297 )
298 / 60
299 )
300 INTO p_duration
301 FROM dual;
302 END IF;
303 END IF;
304 END IF;
305 END IF;
306
307 l_idx := l_schedule (l_idx).next_object_index;
308 END LOOP;
309
310 p_duration := round (p_duration, 2);
311 --
312 END IF;
313 END IF;
314 END;
315 /* RANGE CODE */
316 PROCEDURE range_code (
317 p_payroll_action_id IN NUMBER,
318 p_sql OUT NOCOPY VARCHAR2
319 ) IS
320 l_action_info_id NUMBER;
321 l_ovn NUMBER;
322 l_business_group_id NUMBER;
323 l_effective_date DATE;
324 l_current_start_date DATE;
325 l_current_end_date DATE;
326 l_previous_start_date DATE;
327 l_previous_end_date DATE;
328
329 -- Archiving the data , as this will fire once
330 CURSOR csr_legal_employer_details (
331 csr_v_legal_employer_id hr_organization_information.organization_id%TYPE
332 ) IS
333 SELECT o1.NAME legal_employer_name, hoi2.org_information2 org_number, hoi1.organization_id legal_id
334 FROM hr_organization_units o1, hr_organization_information hoi1, hr_organization_information hoi2
335 WHERE o1.business_group_id = g_business_group_id
336 AND hoi1.organization_id = o1.organization_id
337 AND hoi1.organization_id = nvl (csr_v_legal_employer_id, hoi1.organization_id)
338 AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
339 AND hoi1.org_information_context = 'CLASS'
340 AND o1.organization_id = hoi2.organization_id
341 AND hoi2.org_information_context = 'SE_LEGAL_EMPLOYER_DETAILS';
342
343 l_legal_employer_details csr_legal_employer_details%ROWTYPE;
344 /* Procedure to get the employee data */
345 PROCEDURE get_employee_data (
346 p_legal_employer_id hr_organization_information.organization_id%TYPE,
347 p_effective_start_date DATE,
348 p_effective_end_date DATE,
349 p_curr_prev_flag VARCHAR2 -- 'C' for current year and 'P' for Previous Year
350 ) IS
351 CURSOR csr_get_employee_detail (
352 p_legal_employer_id hr_organization_information.organization_id%TYPE,
353 p_effective_start_date DATE,
354 p_effective_end_date DATE
355 ) IS
356 SELECT papf.person_id, paaf.assignment_id, papf.date_of_birth, papf.sex, nvl (hsc.segment9, 100) work_percentage
357 FROM per_all_people_f papf,
358 per_all_assignments_f paaf,
359 hr_soft_coding_keyflex hsc,
360 per_assignment_status_types past,
361 hr_organization_information hoi,
362 per_person_types ppt,
363 per_all_people_f papfs,
364 per_all_assignments_f paafs
365 WHERE paaf.person_id = papf.person_id
366 AND paaf.business_group_id = papf.business_group_id
367 AND hsc.soft_coding_keyflex_id = paaf.soft_coding_keyflex_id
368 AND hsc.segment2 = hoi.org_information1
369 AND hoi.organization_id = p_legal_employer_id
370 AND ppt.system_person_type LIKE 'EMP%'
371 AND ppt.person_type_id = papf.person_type_id
372 AND paaf.assignment_status_type_id = past.assignment_status_type_id
373 AND past.per_system_status = 'ACTIVE_ASSIGN'
374 AND paaf.primary_flag = 'Y'
375 AND paaf.assignment_id = paafs.assignment_id
376 AND papf.person_id = papfs.person_id
377 AND p_effective_end_date BETWEEN papfs.effective_start_date AND papfs.effective_end_date
378 AND p_effective_end_date BETWEEN papf.effective_start_date AND papf.effective_end_date
379 AND p_effective_end_date BETWEEN paaf.effective_start_date AND paaf.effective_end_date
380 AND p_effective_end_date BETWEEN paafs.effective_start_date AND paafs.effective_end_date;
381
382 CURSOR csr_legal_employer_details (
383 csr_v_legal_employer_id hr_organization_information.organization_id%TYPE
384 ) IS
385 SELECT o1.NAME legal_employer_name, hoi2.org_information2 org_number, hoi1.organization_id legal_id
386 FROM hr_organization_units o1, hr_organization_information hoi1, hr_organization_information hoi2
387 WHERE o1.business_group_id = g_business_group_id
388 AND hoi1.organization_id = o1.organization_id
389 AND hoi1.organization_id = nvl (csr_v_legal_employer_id, hoi1.organization_id)
390 AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
391 AND hoi1.org_information_context = 'CLASS'
392 AND o1.organization_id = hoi2.organization_id
393 AND hoi2.org_information_context = 'SE_LEGAL_EMPLOYER_DETAILS';
394
395 /* Cursor to get the standard work details from the 'Standard Work Details' EIT */
396 CURSOR csr_get_std_work_details (
397 p_legal_employer_id hr_organization_information.organization_id%TYPE,
398 p_year hr_organization_information.org_information1%TYPE
399 ) IS
400 SELECT org_information2 weekly_working_hours, org_information3 daily_working_hours,
401 org_information4
402 days_per_year, org_information5 hours_per_year
403 FROM hr_organization_information
404 WHERE organization_id = p_legal_employer_id
405 AND org_information_context = 'SE_STD_WORK_DETAILS'
406 AND org_information1 = p_year;
407
408 /* Cursor to get the defined balance id */
409 CURSOR csr_get_defined_balance_id (
410 csr_v_balance_name ff_database_items.user_name%TYPE
411 ) IS
412 SELECT defined_balance_id
413 FROM pay_balance_types pbt, pay_balance_dimensions pbd, pay_defined_balances pdb
414 WHERE pbt.balance_name = csr_v_balance_name
415 AND nvl (pbt.business_group_id, g_business_group_id) = g_business_group_id
416 AND pbt.balance_type_id = pdb.balance_type_id
417 AND pbd.database_item_suffix = '_ASG_YTD'
418 AND pbd.legislation_code = 'SE'
419 AND pbd.balance_dimension_id = pdb.balance_dimension_id;
420
421 CURSOR csr_element_type (
422 p_element_name VARCHAR2,
423 p_report_end_date DATE
424 ) IS
425 SELECT element_type_id
426 FROM pay_element_types_f
427 WHERE element_name = p_element_name
428 AND legislation_code = 'SE'
429 AND business_group_id IS NULL
430 AND p_report_end_date BETWEEN effective_start_date AND effective_end_date;
431
432 CURSOR csr_input_values (
433 p_element_type_id NUMBER,
434 p_report_end_date DATE,
435 p_input_value VARCHAR2
436 ) IS
437 SELECT input_value_id
438 FROM pay_input_values_f
439 WHERE element_type_id = p_element_type_id
440 AND p_report_end_date BETWEEN effective_start_date AND effective_end_date
441 AND NAME = p_input_value
442 AND legislation_code = 'SE'
443 AND business_group_id IS NULL;
444
445 /* This cursor is used to take start date for each of the sickness group */
446 CURSOR csr_group_start_date (
447 p_element_type_id NUMBER,
448 p_assignment_id NUMBER,
449 p_report_start_date DATE,
450 p_report_end_date DATE,
451 p_input_value_id NUMBER
452 ) IS
453 SELECT prrv.result_value start_date
454 FROM pay_assignment_actions paa, pay_payroll_actions ppa, pay_run_results prr, pay_run_result_values prrv
455 WHERE ppa.effective_date BETWEEN p_report_start_date AND p_report_end_date
456 AND ppa.payroll_action_id = paa.payroll_action_id
457 AND paa.assignment_id = p_assignment_id
458 AND paa.assignment_action_id = prr.assignment_action_id
459 AND prr.element_type_id = p_element_type_id
460 AND prr.run_result_id = prrv.run_result_id
461 AND prrv.input_value_id = p_input_value_id
462 GROUP BY result_value;
463
464 CURSOR csr_get_input_value (
465 p_group_start_date VARCHAR2,
466 p_input_value_id NUMBER,
467 p_assignment_id NUMBER,
468 p_report_start_date DATE,
469 p_report_end_date DATE
470
471 ) IS
472 SELECT sum (fnd_number.canonical_to_number (result_value))
473 FROM pay_run_result_values prrv
474 WHERE input_value_id = p_input_value_id
475 AND run_result_id IN (SELECT prr.run_result_id
476 FROM pay_assignment_actions paa, pay_payroll_actions ppa,pay_run_result_values prrv1,
477 pay_run_results prr
478 WHERE ppa.effective_date BETWEEN p_report_start_date AND p_report_end_date
479 AND ppa.payroll_action_id = paa.payroll_action_id
480 AND paa.assignment_id = p_assignment_id
481 and paa.assignment_action_id = prr.assignment_action_id
482 and prr.run_result_id = prrv1.run_result_id
483 and prrv1.result_value = p_group_start_date
484 ) ;
485
486
487 l_def_bal_id_hours NUMBER;
488 l_def_bal_id_days NUMBER;
489 l_get_def_bal_id_till_14 NUMBER;
490 l_get_def_bal_id_after_14 NUMBER;
491 l_current_year VARCHAR2 (4);
492 l_legal_employer_details csr_legal_employer_details%ROWTYPE;
493 l_le_has_employee VARCHAR2 (2);
494 l_curr_prev_data VARCHAR2 (30); -- 'LE_CURR_YR_DATA' for current year and 'LE_PREV_YR_DATA' for previous year
495 l_curr_prev_stat VARCHAR2 (30); -- 'LE_CURR_YR_STAT' for current year and 'LE_PREV_YR_STAT' for previous year
496 l_curr_prev_std_hour VARCHAR2 (30); -- 'LE_CURR_YR_STD_HOUR' for current year and 'LE_PREV_YR_STD_HOUR' for previous year
497 l_long_sick_leave_flag CHAR (1); -- 'L' - for long leave and 'N' for Normal leave
498 l_men_lower_age_count NUMBER := 0; -- No of men employees for lower age group
499 l_men_middle_age_count NUMBER := 0; -- No of men employees for middle age group
500 l_men_upper_age_count NUMBER := 0; -- No of men employees for upper age group
501 l_women_lower_age_count NUMBER := 0; -- No of women employees for lower age group
502 l_women_middle_age_count NUMBER := 0; -- No of women employees for middle age group
503 l_women_upper_age_count NUMBER := 0; -- No of women employees for upper age group
504 l_men_lower_age_work_hour NUMBER := 0; -- No of men employees hours for lower age group
505 l_men_middle_age_work_hour NUMBER := 0; -- No of men employees hours for middle age group
506 l_men_upper_age_work_hour NUMBER := 0; -- No of men employees hours for upper age group
507 l_women_lower_age_work_hour NUMBER := 0; -- No of women employees hours for lower age group
508 l_women_middle_age_work_hour NUMBER := 0; -- No of women employees hours for middle age group
509 l_women_upper_age_work_hour NUMBER := 0; -- No of women employees hours for upper age group
510 l_men_l_age_sick_leaves NUMBER := 0; -- No Sick leaves for men employees for lower age group
511 l_men_m_age_sick_leaves NUMBER := 0; -- No Sick leaves for men employees for middle age group
512 l_men_u_age_sick_leaves NUMBER := 0; -- No Sick leaves for men employees for upper age group
513 l_women_l_age_sick_leaves NUMBER := 0; -- No Sick leaves for women employees for lower age group
514 l_women_m_age_sick_leaves NUMBER := 0; -- No Sick leaves for women employees for middle age group
515 l_women_u_age_sick_leaves NUMBER := 0; -- No Sick leaves for women employees for upper age group
516 l_men_l_age_l_sick_leaves NUMBER := 0; -- No Long Sick leaves for men employees for lower age group
517 l_men_m_age_l_sick_leaves NUMBER := 0; -- No Long Sick leaves for men employees for middle age group
518 l_men_u_age_l_sick_leaves NUMBER := 0; -- No Long Sick leaves for men employees for upper age group
519 l_women_l_age_l_sick_leaves NUMBER := 0; -- No Long Sick leaves for women employees for lower age group
520 l_women_m_age_l_sick_leaves NUMBER := 0; -- No Long Sick leaves for women employees for middle age group
521 l_women_u_age_l_sick_leaves NUMBER := 0; -- No Long Sick leaves for women employees for upper age group
522 l_curr_week_hours NUMBER; -- No of weekly working hours for the current year
523 l_curr_daily_hours NUMBER; -- NO of daily working hours for the current year
524 l_curr_days_per_year NUMBER; -- No of day per year for the current year
525 l_curr_hours_per_year NUMBER; -- No of working hours for the current year
526 l_current_age NUMBER;
527 l_emp_gender VARCHAR2 (2);
528 /* l_emp_gender :-
529 1. ML - Male Small Age Group
530 2. MM - Male Middle Age Group
531 3. MU - Male Upper Age Group
532 4. WL - Female Small Age Group
533 5. WM - Female Middle Age Group
534 6. WU - Female Upper Age Group */
535 l_emp_work_hours NUMBER;
536 l_emp_sick_hours NUMBER;
537 l_total_sick_days NUMBER;
538 l_org_tot_men_work_hours NUMBER := 0;
539 l_org_tot_women_work_hours NUMBER := 0;
540 l_org_total_work_hours NUMBER := 0;
541 l_org_tot_men_sick_hours NUMBER := 0;
542 l_org_tot_women_sick_hours NUMBER := 0;
543 l_org_total_sick_hours NUMBER := 0;
544 l_org_total_l_sick_hours NUMBER := 0;
545 l_org_tot_l_age_work_hours NUMBER := 0;
546 l_org_tot_m_age_work_hours NUMBER := 0;
547 l_org_tot_u_age_work_hours NUMBER := 0;
548 l_org_tot_l_age_sick_hours NUMBER := 0;
549 l_org_tot_m_age_sick_hours NUMBER := 0;
550 l_org_tot_u_age_sick_hours NUMBER := 0;
551 l_leave_to_work_hours NUMBER := 0;
552 l_long_leave_part NUMBER := 0;
553 l_women_leave_to_work_hour NUMBER := 0;
554 l_men_leave_to_work_hour NUMBER := 0;
555 l_leave_to_work_hour_lower NUMBER := 0;
556 l_leave_to_work_hour_middle NUMBER := 0;
557 l_leave_to_work_hour_upper NUMBER := 0;
558 l_duration NUMBER := 0;
559 l_element_type_id pay_element_types_f.element_type_id%TYPE;
560 l_input_value_start_date pay_input_values_f.input_value_id%TYPE;
561 l_input_value_end_date pay_input_values_f.input_value_id%TYPE;
562 l_input_value_cal_days pay_input_values_f.input_value_id%TYPE;
563 l_input_value_work_days pay_input_values_f.input_value_id%TYPE;
564 l_input_value_work_hours pay_input_values_f.input_value_id%TYPE;
565 l_group_date DATE;
566 l_working_days NUMBER;
567 l_working_hours NUMBER;
568 l_calendar_days NUMBER;
569 l_long_sick_leave NUMBER;
570 BEGIN
571 l_current_year := to_char (p_effective_start_date, 'YYYY');
572
573 IF p_curr_prev_flag = 'C' THEN
574 l_curr_prev_data := 'LE_CURR_YR_DATA';
575 l_curr_prev_stat := 'LE_CURR_YR_STAT';
576 l_curr_prev_std_hour := 'LE_CURR_YR_STD_HOUR';
577 ELSIF p_curr_prev_flag = 'P' THEN
578 l_curr_prev_data := 'LE_PREV_YR_DATA';
579 l_curr_prev_stat := 'LE_PREV_YR_STAT';
580 l_curr_prev_std_hour := 'LE_PREV_YR_STD_HOUR';
581 END IF;
582
583
584 OPEN csr_legal_employer_details (p_legal_employer_id);
585 FETCH csr_legal_employer_details INTO l_legal_employer_details;
586 CLOSE csr_legal_employer_details;
587 l_current_age := 0;
588 l_men_lower_age_count := 0;
589 l_men_middle_age_count := 0;
590 l_men_upper_age_count := 0;
591 l_women_lower_age_count := 0;
592 l_women_middle_age_count := 0;
593 l_women_upper_age_count := 0;
594 l_emp_work_hours := 0;
595 l_emp_sick_hours := 0;
596 /* Get the Standard working hours */
597 OPEN csr_get_std_work_details (p_legal_employer_id, to_char (p_effective_end_date, 'YYYY'));
598 FETCH csr_get_std_work_details INTO l_curr_week_hours, l_curr_daily_hours, l_curr_days_per_year, l_curr_hours_per_year;
599 CLOSE csr_get_std_work_details;
600
601 IF l_curr_days_per_year IS NULL THEN
602 l_curr_days_per_year := 0;
603 l_curr_hours_per_year := 0;
604 get_schedule_duration (
605 p_start_date => p_effective_start_date,
606 p_end_date => p_effective_end_date,
607 p_days_or_hours => 'D',
608 p_duration => l_curr_days_per_year
609 );
610 get_schedule_duration (
611 p_start_date => p_effective_start_date,
612 p_end_date => p_effective_end_date,
613 p_days_or_hours => 'H',
614 p_duration => l_curr_hours_per_year
615 );
616 END IF;
617
618 pay_action_information_api.create_action_information (
619 p_action_information_id => l_action_info_id,
620 p_action_context_id => p_payroll_action_id,
621 p_action_context_type => 'PA',
622 p_object_version_number => l_ovn,
623 p_effective_date => g_effective_date,
624 p_source_id => NULL,
625 p_source_text => NULL,
626 p_action_information_category => 'EMEA REPORT INFORMATION',
627 p_action_information1 => 'PYSESLSA',
628 p_action_information2 => l_curr_prev_std_hour,
629 p_action_information3 => p_legal_employer_id,
630 p_action_information4 => l_legal_employer_details.legal_employer_name,
631 p_action_information5 => l_legal_employer_details.org_number,
632 p_action_information6 => fnd_number.number_to_canonical (l_curr_week_hours),
633 p_action_information7 => fnd_number.number_to_canonical (l_curr_daily_hours),
634 p_action_information8 => fnd_number.number_to_canonical (l_curr_days_per_year),
635 p_action_information9 => fnd_number.number_to_canonical (l_curr_hours_per_year),
636 p_action_information10 => fnd_date.date_to_canonical (p_effective_start_date),
637 p_action_information11 => fnd_date.date_to_canonical (p_effective_end_date)
638 );
639 /* Get the Element Type Id for the given element Name */
640 OPEN csr_element_type ('Sickness Group Details', p_effective_end_date);
641 FETCH csr_element_type INTO l_element_type_id;
642 CLOSE csr_element_type;
643 /* Get the Input Value id for the given Input Value Name */
644 OPEN csr_input_values (l_element_type_id, p_effective_end_date, 'Start Date');
645 FETCH csr_input_values INTO l_input_value_start_date;
646 CLOSE csr_input_values;
647 --
648 OPEN csr_input_values (l_element_type_id, p_effective_end_date, 'End Date');
649 FETCH csr_input_values INTO l_input_value_end_date;
650 CLOSE csr_input_values;
651 --
652 OPEN csr_input_values (l_element_type_id, p_effective_end_date, 'Calendar Days');
653 FETCH csr_input_values INTO l_input_value_cal_days;
654 CLOSE csr_input_values;
655 --
656 OPEN csr_input_values (l_element_type_id, p_effective_end_date, 'Working Days');
657 FETCH csr_input_values INTO l_input_value_work_days;
658 CLOSE csr_input_values;
659 --
660 OPEN csr_input_values (l_element_type_id, p_effective_end_date, 'Working Hours');
661 FETCH csr_input_values INTO l_input_value_work_hours;
662 CLOSE csr_input_values;
663 --
664 FOR i IN csr_get_employee_detail (
665 p_legal_employer_id => p_legal_employer_id,
666 p_effective_start_date => p_effective_start_date,
667 p_effective_end_date => p_effective_end_date
668 )
669 LOOP
670 l_emp_sick_hours := 0;
671 l_total_sick_days := 0;
672 l_current_age := floor (months_between (g_end_date, i.date_of_birth) / 12);
673 /* to_number (l_current_year) - to_number (to_char (i.date_of_birth, 'YYYY'));*/
674 l_emp_work_hours := (l_curr_hours_per_year * i.work_percentage) / 100;
675 l_long_sick_leave := 0;
676 l_emp_sick_hours := 0;
677
678 FOR rec_group_start_date IN csr_group_start_date (
679 p_element_type_id => l_element_type_id,
680 p_assignment_id => i.assignment_id,
681 p_report_start_date => p_effective_start_date,
682 p_report_end_date => p_effective_end_date,
683 p_input_value_id => l_input_value_start_date
684 )
685 LOOP
686
687 l_calendar_days := 0;
688 l_working_hours := 0;
689 /* Get the no of Calendar days for the group */
690 OPEN csr_get_input_value (
691 p_group_start_date => rec_group_start_date.start_date,
692 p_input_value_id => l_input_value_cal_days,
693 p_assignment_id => i.assignment_id,
694 p_report_start_date => p_effective_start_date,
695 p_report_end_date => p_effective_end_date
696 );
697 FETCH csr_get_input_value INTO l_calendar_days;
698 CLOSE csr_get_input_value;
699 --
700
701
702 /* Get the no of Working Hours for the group */
703 OPEN csr_get_input_value (
704 p_group_start_date => rec_group_start_date.start_date,
705 p_input_value_id => l_input_value_work_hours ,
706 p_assignment_id => i.assignment_id,
707 p_report_start_date => p_effective_start_date,
708 p_report_end_date => p_effective_end_date
709 );
710 FETCH csr_get_input_value INTO l_working_hours;
711 CLOSE csr_get_input_value;
712
713 l_emp_sick_hours := l_emp_sick_hours + l_working_hours;
714
715 IF l_calendar_days >= g_no_of_long_leave THEN
716 l_long_sick_leave := l_long_sick_leave + l_working_hours;
717 END IF;
718
719 END LOOP;
720
721 IF i.sex = 'M' AND l_current_age > g_lower_age_group AND l_current_age < g_upper_age_group THEN
722 l_emp_gender := 'MM';
723 l_men_middle_age_count := l_men_middle_age_count + 1;
724 l_men_middle_age_work_hour := l_men_middle_age_work_hour + l_emp_work_hours;
725 l_men_m_age_sick_leaves := l_men_m_age_sick_leaves + l_emp_sick_hours;
726 l_men_m_age_l_sick_leaves := l_men_m_age_l_sick_leaves + l_long_sick_leave;
727 -- Get The Woen count for the middle age group
728 ELSIF i.sex = 'F' AND l_current_age > g_lower_age_group AND l_current_age < g_upper_age_group THEN
729 l_emp_gender := 'WM';
730 l_women_middle_age_count := l_women_middle_age_count + 1;
731 l_women_middle_age_work_hour := l_women_middle_age_work_hour + l_emp_work_hours;
732 l_women_m_age_sick_leaves := l_women_m_age_sick_leaves + l_emp_sick_hours;
733 l_women_m_age_l_sick_leaves := l_women_m_age_l_sick_leaves + l_long_sick_leave;
734 -- Get The Men count for lower age group
735 ELSIF i.sex = 'M' AND l_current_age <= g_lower_age_group THEN
736 l_emp_gender := 'ML';
737 l_men_lower_age_count := l_men_lower_age_count + 1;
738 l_men_lower_age_work_hour := l_men_lower_age_work_hour + l_emp_work_hours;
739 l_men_l_age_sick_leaves := l_men_l_age_sick_leaves + l_emp_sick_hours;
740 l_men_l_age_l_sick_leaves := l_men_l_age_l_sick_leaves + l_long_sick_leave;
741 -- Get The Women count for lower age group
742 ELSIF i.sex = 'F' AND l_current_age <= g_lower_age_group THEN
743 l_emp_gender := 'WL';
744 l_women_lower_age_count := l_women_lower_age_count + 1;
745 l_women_lower_age_work_hour := l_women_lower_age_work_hour + l_emp_work_hours;
746 l_women_l_age_sick_leaves := l_women_l_age_sick_leaves + l_emp_sick_hours;
747 l_women_l_age_l_sick_leaves := l_women_l_age_l_sick_leaves + l_long_sick_leave;
748 -- Get The Men count for upper age group
749 ELSIF i.sex = 'M' AND l_current_age >= g_upper_age_group THEN
750 l_emp_gender := 'MU';
751 l_men_upper_age_count := l_men_upper_age_count + 1;
752 l_men_upper_age_work_hour := l_men_upper_age_work_hour + l_emp_work_hours;
753 l_men_u_age_sick_leaves := l_men_u_age_sick_leaves + l_emp_sick_hours;
754 l_men_u_age_l_sick_leaves := l_men_u_age_l_sick_leaves + l_long_sick_leave;
755 -- Get The Women count for upper age group
756 ELSIF i.sex = 'F' AND l_current_age >= g_upper_age_group THEN
757 l_emp_gender := 'WU';
758 l_women_upper_age_count := l_women_upper_age_count + 1;
759 l_women_upper_age_work_hour := l_women_upper_age_work_hour + l_emp_work_hours;
760 l_women_u_age_sick_leaves := l_women_u_age_sick_leaves + l_emp_sick_hours;
761 l_women_u_age_l_sick_leaves := l_women_u_age_l_sick_leaves + l_long_sick_leave;
762 END IF;
763 END LOOP; -- End loop for csr_get_employee_detail
764 /* Total Men Working Hours */
765 l_org_tot_men_work_hours := l_men_lower_age_work_hour + l_men_middle_age_work_hour + l_men_upper_age_work_hour;
766 --
767 /* Total Women Working Hours */
768 l_org_tot_women_work_hours := l_women_lower_age_work_hour + l_women_middle_age_work_hour + l_women_upper_age_work_hour;
769 --
770 /* Total Working Hours */
771 l_org_total_work_hours := l_org_tot_men_work_hours + l_org_tot_women_work_hours;
772 l_org_tot_l_age_work_hours := l_men_lower_age_work_hour + l_women_lower_age_work_hour;
773 l_org_tot_m_age_work_hours := l_men_middle_age_work_hour + l_women_middle_age_work_hour;
774 l_org_tot_u_age_work_hours := l_men_upper_age_work_hour + l_women_upper_age_work_hour;
775 --
776 /* Total Men Sick Hours */
777 l_org_tot_men_sick_hours := l_men_l_age_sick_leaves + l_men_m_age_sick_leaves + l_men_u_age_sick_leaves;
778 --
779 /* Total Women Sick Hours */
780 l_org_tot_women_sick_hours := l_women_l_age_sick_leaves + l_women_m_age_sick_leaves + l_women_u_age_sick_leaves;
781 --
782 /* Total Sick leaves */
783 l_org_total_sick_hours := l_org_tot_men_sick_hours + l_org_tot_women_sick_hours;
784 l_org_tot_l_age_sick_hours := l_men_l_age_sick_leaves + l_women_l_age_sick_leaves;
785 l_org_tot_m_age_sick_hours := l_men_m_age_sick_leaves + l_women_m_age_sick_leaves;
786 l_org_tot_u_age_sick_hours := l_men_u_age_sick_leaves + l_women_u_age_sick_leaves;
787 --
788 /* Total Long Sick leaves */
789 l_org_total_l_sick_hours := l_men_l_age_l_sick_leaves + l_women_l_age_l_sick_leaves + l_men_m_age_l_sick_leaves
790 + l_women_m_age_l_sick_leaves + l_men_m_age_l_sick_leaves + l_women_u_age_l_sick_leaves;
791
792 --
793 /* Find the Statistics */
794 /* Find the Total sick leave related to total standard working hours */
795 IF l_org_total_work_hours > 0 THEN
796 l_leave_to_work_hours := round ((l_org_total_sick_hours / l_org_total_work_hours * 100), 1);
797 ELSE
798 l_leave_to_work_hours := 0;
799 END IF;
800
801 --
802 /* Find the Part of total sick leave, which is 60 days or longer */
803 IF l_org_total_sick_hours > 0 THEN
804 l_long_leave_part := round ((l_org_total_l_sick_hours / l_org_total_sick_hours * 100), 1);
805 ELSE
806 l_long_leave_part := 0;
807 END IF;
808
809 --
810 /* Find the Sick leave for women related to total standard working hours for women */
811 IF l_org_tot_women_work_hours > 0 THEN
812 l_women_leave_to_work_hour := round ((l_org_tot_women_sick_hours / l_org_tot_women_work_hours * 100), 1);
813 ELSE
814 l_women_leave_to_work_hour := 0;
815 END IF;
816
817 /* Find the Sick leave for men related to total standard working hours for men */
818 IF l_org_tot_men_work_hours > 0 THEN
819 l_men_leave_to_work_hour := round ((l_org_tot_men_sick_hours / l_org_tot_men_work_hours * 100), 1);
820 ELSE
821 l_men_leave_to_work_hour := 0;
822 END IF;
823
824 /* Find the Sick leave for the lower age group, related to the total standard working hours for this group */
825 IF l_org_tot_l_age_work_hours > 0 THEN
826 l_leave_to_work_hour_lower := round ((l_org_tot_l_age_sick_hours / l_org_tot_l_age_work_hours * 100), 1);
827 ELSE
828 l_leave_to_work_hour_lower := 0;
829 END IF;
830
831 /* Find the Sick leave for the middle age group, related to the total standard working hours for this group */
832 IF l_org_tot_m_age_work_hours > 0 THEN
833 l_leave_to_work_hour_middle := round ((l_org_tot_m_age_sick_hours / l_org_tot_m_age_work_hours * 100), 1);
834 ELSE
835 l_leave_to_work_hour_middle := 0;
836 END IF;
837
838 /* Find the Sick leave for the upper age group, related to the total standard working hours for this group */
839 IF l_org_tot_u_age_work_hours > 0 THEN
840 l_leave_to_work_hour_upper := round ((l_org_tot_u_age_sick_hours / l_org_tot_u_age_work_hours * 100), 1);
841 ELSE
842 l_leave_to_work_hour_upper := 0;
843 END IF;
844
845 /* Archive the data for the Report Details */
846 pay_action_information_api.create_action_information (
847 p_action_information_id => l_action_info_id,
848 p_action_context_id => p_payroll_action_id,
849 p_action_context_type => 'PA',
850 p_object_version_number => l_ovn,
851 p_effective_date => g_effective_date,
852 p_source_id => NULL,
853 p_source_text => NULL,
854 p_action_information_category => 'EMEA REPORT INFORMATION',
855 p_action_information1 => 'PYSESLSA',
856 p_action_information2 => l_curr_prev_data,
857 p_action_information3 => p_legal_employer_id,
858 p_action_information4 => l_legal_employer_details.legal_employer_name,
859 p_action_information5 => l_legal_employer_details.org_number,
860 /* Archive the no of employees agewise in each gender group */
861 p_action_information6 => fnd_number.number_to_canonical (l_men_lower_age_count),
862 p_action_information7 => fnd_number.number_to_canonical (l_men_middle_age_count),
863 p_action_information8 => fnd_number.number_to_canonical (l_men_upper_age_count),
864 p_action_information9 => fnd_number.number_to_canonical (l_women_lower_age_count),
865 p_action_information10 => fnd_number.number_to_canonical (l_women_middle_age_count),
866 p_action_information11 => fnd_number.number_to_canonical (l_women_upper_age_count),
867 /* Archive the no of working hours agewise in each gender group */
868 p_action_information12 => fnd_number.number_to_canonical (l_men_lower_age_work_hour),
869 p_action_information13 => fnd_number.number_to_canonical (l_men_middle_age_work_hour),
870 p_action_information14 => fnd_number.number_to_canonical (l_men_upper_age_work_hour),
871 p_action_information15 => fnd_number.number_to_canonical (l_women_lower_age_work_hour),
872 p_action_information16 => fnd_number.number_to_canonical (l_women_middle_age_work_hour),
873 p_action_information17 => fnd_number.number_to_canonical (l_women_upper_age_work_hour),
874 /* Archive the no of sick leaves agewise in each gender group */
875 p_action_information18 => fnd_number.number_to_canonical (l_men_l_age_sick_leaves),
876 p_action_information19 => fnd_number.number_to_canonical (l_men_m_age_sick_leaves),
877 p_action_information20 => fnd_number.number_to_canonical (l_men_u_age_sick_leaves),
878 p_action_information21 => fnd_number.number_to_canonical (l_women_l_age_sick_leaves),
879 p_action_information22 => fnd_number.number_to_canonical (l_women_m_age_sick_leaves),
880 p_action_information23 => fnd_number.number_to_canonical (l_women_u_age_sick_leaves),
881 /* Archive the no of long sick leaves agewise in each gender group */
882 p_action_information24 => fnd_number.number_to_canonical (l_men_l_age_l_sick_leaves),
883 p_action_information25 => fnd_number.number_to_canonical (l_men_m_age_l_sick_leaves),
884 p_action_information26 => fnd_number.number_to_canonical (l_men_u_age_l_sick_leaves),
885 p_action_information27 => fnd_number.number_to_canonical (l_women_l_age_l_sick_leaves),
886 p_action_information28 => fnd_number.number_to_canonical (l_women_m_age_l_sick_leaves),
887 p_action_information29 => fnd_number.number_to_canonical (l_women_u_age_l_sick_leaves)
888 );
889 pay_action_information_api.create_action_information (
890 p_action_information_id => l_action_info_id,
891 p_action_context_id => p_payroll_action_id,
892 p_action_context_type => 'PA',
893 p_object_version_number => l_ovn,
894 p_effective_date => g_effective_date,
895 p_source_id => NULL,
896 p_source_text => NULL,
897 p_action_information_category => 'EMEA REPORT INFORMATION',
898 p_action_information1 => 'PYSESLSA',
899 p_action_information2 => l_curr_prev_stat,
900 p_action_information3 => p_legal_employer_id,
901 p_action_information4 => l_legal_employer_details.legal_employer_name,
902 p_action_information5 => l_legal_employer_details.org_number,
903 /* Archive the no of employees agewise in each gender group */
904 p_action_information6 => fnd_number.number_to_canonical (l_leave_to_work_hours),
905 p_action_information7 => fnd_number.number_to_canonical (l_long_leave_part),
906 p_action_information8 => fnd_number.number_to_canonical (l_men_leave_to_work_hour),
907 p_action_information9 => fnd_number.number_to_canonical (l_women_leave_to_work_hour),
908 p_action_information10 => fnd_number.number_to_canonical (l_leave_to_work_hour_lower),
909 p_action_information11 => fnd_number.number_to_canonical (l_leave_to_work_hour_middle),
910 p_action_information12 => fnd_number.number_to_canonical (l_leave_to_work_hour_upper)
911 );
912 END;
913 -- Archiving the data , as this will fire once
914 BEGIN
915 g_debug := TRUE ;
916
917 IF g_debug THEN
918 hr_utility.set_location (' Entering Procedure RANGE_CODE', 40);
919 END IF;
920
921 p_sql :=
922 'SELECT DISTINCT person_id
923 FROM per_people_f ppf
924 ,pay_payroll_actions ppa
925 WHERE ppa.payroll_action_id = :payroll_action_id
926 AND ppa.business_group_id = ppf.business_group_id
927 ORDER BY ppf.person_id';
928 g_payroll_action_id := p_payroll_action_id;
929 g_business_group_id := NULL;
930 g_effective_date := NULL;
931 g_legal_employer_id := NULL;
932 g_local_unit_id := NULL;
933 pay_se_slsa_archive.get_all_parameters (
934 p_payroll_action_id,
935 g_business_group_id,
936 g_effective_date,
937 g_legal_employer_id,
938 g_request_for,
939 g_start_date,
940 g_end_date
941 );
942 l_current_start_date := g_start_date;
943
944 IF g_effective_date < g_end_date THEN
945 l_current_end_date := g_effective_date;
946 ELSE
947 l_current_end_date := g_end_date;
948 END IF;
949
950 l_previous_start_date := add_months (g_start_date, -12);
951 l_previous_end_date := add_months (g_end_date, -12);
952
953 IF g_request_for = 'REQUESTING_ORG' THEN
954 OPEN csr_legal_employer_details (g_legal_employer_id);
955 FETCH csr_legal_employer_details INTO l_legal_employer_details;
956 CLOSE csr_legal_employer_details;
957 END IF;
958
959 -- Insert the report Parameters
960 pay_action_information_api.create_action_information (
961 p_action_information_id => l_action_info_id,
962 p_action_context_id => p_payroll_action_id,
963 p_action_context_type => 'PA',
964 p_object_version_number => l_ovn,
965 p_effective_date => g_effective_date,
966 p_source_id => NULL,
967 p_source_text => NULL,
968 p_action_information_category => 'EMEA REPORT DETAILS',
969 p_action_information1 => 'PYSESLSA',
970 p_action_information2 => hr_general.decode_lookup ('SE_TAX_CARD_REQUEST_LEVEL', g_request_for),
971 p_action_information3 => g_legal_employer_id,
972 p_action_information4 => l_legal_employer_details.legal_employer_name,
973 p_action_information5 => fnd_date.date_to_canonical (g_start_date),
974 p_action_information6 => fnd_date.date_to_canonical (g_end_date),
975 p_action_information7 => NULL,
976 p_action_information8 => NULL,
977 p_action_information9 => NULL,
978 p_action_information10 => NULL
979 );
980
981 IF g_request_for = 'REQUESTING_ORG' THEN
982 /* For Current Year */
983 get_employee_data (
984 p_legal_employer_id => g_legal_employer_id,
985 p_effective_start_date => g_start_date,
986 p_effective_end_date => g_end_date,
987 p_curr_prev_flag => 'C'
988 );
989 /* For Previous Year */
990 get_employee_data (
991 p_legal_employer_id => g_legal_employer_id,
992 p_effective_start_date => add_months (g_start_date, -12),
993 p_effective_end_date => add_months (g_end_date, -12),
994 p_curr_prev_flag => 'P'
995 );
996
997
998
999 ELSE
1000
1001 -- *****************************************************************************
1002 FOR rec_legal_employer_details IN csr_legal_employer_details (NULL)
1003 LOOP
1004 /* For Current Year */
1005 get_employee_data (
1006 p_legal_employer_id => rec_legal_employer_details.legal_id,
1007 p_effective_start_date => g_start_date,
1008 p_effective_end_date => g_end_date,
1009 p_curr_prev_flag => 'C'
1010 );
1011 /* For Previous Year */
1012 get_employee_data (
1013 p_legal_employer_id => rec_legal_employer_details.legal_id,
1014 p_effective_start_date => add_months (g_start_date, -12),
1015 p_effective_end_date => add_months (g_end_date, -12),
1016 p_curr_prev_flag => 'P'
1017 );
1018 END LOOP;
1019 END IF; -- FOR G_LEGAL_EMPLOYER
1020 --END IF; -- G_Archive End
1021 IF g_debug THEN
1022 hr_utility.set_location (' Leaving Procedure RANGE_CODE', 50);
1023 END IF;
1024 EXCEPTION
1025 WHEN OTHERS THEN
1026 -- Return cursor that selects no rows
1027 p_sql := 'select 1 from dual where to_char(:payroll_action_id) = dummy';
1028 END range_code;
1029 /* ASSIGNMENT ACTION CODE */
1030 PROCEDURE assignment_action_code (
1031 p_payroll_action_id IN NUMBER,
1032 p_start_person IN NUMBER,
1033 p_end_person IN NUMBER,
1034 p_chunk IN NUMBER
1035 ) IS
1036 BEGIN
1037 IF g_debug THEN
1038 hr_utility.set_location (' Entering Procedure ASSIGNMENT_ACTION_CODE', 60);
1039 END IF;
1040 END assignment_action_code;
1041 /* INITIALIZATION CODE */
1042 PROCEDURE initialization_code (
1043 p_payroll_action_id IN NUMBER
1044 ) IS
1045 BEGIN
1046 IF g_debug THEN
1047 hr_utility.set_location (' Entering Procedure INITIALIZATION_CODE', 80);
1048 END IF;
1049 END initialization_code;
1050 /* ARCHIVE CODE */
1051 PROCEDURE archive_code (
1052 p_assignment_action_id IN NUMBER,
1053 p_effective_date IN DATE
1054 ) IS
1055 BEGIN
1056 IF g_debug THEN
1057 hr_utility.set_location (' Entering Procedure ARCHIVE_CODE', 380);
1058 END IF;
1059 END archive_code;
1060 END pay_se_slsa_archive;