[Home] [Help]
PACKAGE BODY: APPS.PAY_SE_TRNA_ARCHIVE
Source
1 PACKAGE BODY pay_se_trna_archive AS
2 /* $Header: pysetrna.pkb 120.2 2007/07/30 12:22:21 psingla noship $ */
3 TYPE lock_rec IS RECORD (
4 archive_assact_id NUMBER
5 );
6
7 TYPE lock_table IS TABLE OF lock_rec
8 INDEX BY BINARY_INTEGER;
9
10 g_debug BOOLEAN := hr_utility.debug_enabled;
11 g_package VARCHAR2 (33) := 'pay_se_trna_archive.';
12 g_payroll_action_id NUMBER;
13 -- Globals to pick up all the parameter
14 g_business_group_id NUMBER;
15 g_effective_date DATE;
16 g_legal_employer_id NUMBER;
17 g_local_unit_id NUMBER;
18 g_request_for VARCHAR2 (20);
19 g_start_date DATE;
20 g_end_date DATE;
21 g_div_code VARCHAR2 (30);
22 g_request_for_div VARCHAR2 (30);
23 g_agreement_area VARCHAR2 (30);
24 g_emp_catg VARCHAR2 (30);
25 g_asg_catg VARCHAR2 (30);
26 g_precedence_end_date DATE;
27 g_start_date_of_birth DATE;
28 g_end_date_of_birth DATE;
29 g_request_for_agreement VARCHAR2 (30);
30 g_emp_sec VARCHAR2 (30);
31 g_lock_table lock_table;
32 g_index NUMBER := -1;
33 g_index_assact NUMBER := -1;
34 g_index_bal NUMBER := -1;
35 g_report_date DATE;
36 g_sort_order VARCHAR2 (30);
37 /* GET PARAMETER */
38 FUNCTION get_parameter (
39 p_parameter_string IN VARCHAR2,
40 p_token IN VARCHAR2,
41 p_segment_number IN NUMBER DEFAULT NULL
42 )
43 RETURN VARCHAR2 IS
44 l_parameter pay_payroll_actions.legislative_parameters%TYPE := NULL;
45 l_start_pos NUMBER;
46 l_delimiter VARCHAR2 (1) := ' ';
47 l_proc VARCHAR2 (40) := g_package || ' get parameter ';
48 BEGIN
49 --
50 IF g_debug THEN
51 hr_utility.set_location (' Entering Function GET_PARAMETER', 10);
52 END IF;
53
54 g_debug := FALSE ;
55 l_start_pos := instr (' ' || p_parameter_string, l_delimiter || p_token || '=');
56
57 --
58 IF l_start_pos = 0 THEN
59 l_delimiter := '|';
60 l_start_pos := instr (' ' || p_parameter_string, l_delimiter || p_token || '=');
61 END IF;
62
63 IF l_start_pos <> 0 THEN
64 l_start_pos := l_start_pos + LENGTH (p_token || '=');
65 l_parameter := substr (
66 p_parameter_string,
67 l_start_pos,
68 instr (p_parameter_string || ' ', l_delimiter, l_start_pos) - (l_start_pos)
69 );
70
71 IF p_segment_number IS NOT NULL THEN
72 l_parameter := ':' || l_parameter || ':';
73 l_parameter := substr (
74 l_parameter,
75 instr (l_parameter, ':', 1, p_segment_number) + 1,
76 instr (l_parameter, ':', 1, p_segment_number + 1) - 1
77 - instr (l_parameter, ':', 1, p_segment_number)
78 );
79 END IF;
80 END IF;
81
82 --
83 IF g_debug THEN
84 hr_utility.set_location (' Leaving Function GET_PARAMETER', 20);
85 END IF;
86
87 RETURN l_parameter;
88 END;
89 /* GET ALL PARAMETERS */
90 PROCEDURE get_all_parameters (
91 p_payroll_action_id IN NUMBER -- In parameter
92 ,
93 p_business_group_id OUT NOCOPY NUMBER -- Core parameter
94 ,
95 p_effective_date OUT NOCOPY DATE -- Core parameter
96 ,
97 p_legal_employer_id OUT NOCOPY NUMBER -- User parameter
98 ,
99 p_request_for_all_or_not OUT NOCOPY VARCHAR2, -- User parameter
100 p_div_code OUT NOCOPY VARCHAR2 -- User parameter
101 ,
102 p_request_for_div OUT NOCOPY VARCHAR2 -- User parameter
103 ,
104 p_agreement_area OUT NOCOPY VARCHAR2 -- User parameter
105 ,
106 p_request_for_agreement OUT NOCOPY VARCHAR2, -- User parameter
107 p_report_date OUT NOCOPY VARCHAR2,
108 p_precedence_end_date OUT NOCOPY DATE,
109 p_start_date_of_birth OUT NOCOPY DATE,
110 p_end_date_of_birth OUT NOCOPY DATE,
111 p_emp_catg OUT NOCOPY VARCHAR2 -- User parameter
112 ,
113 p_asg_catg OUT NOCOPY VARCHAR2 -- User parameter
114 ,
115 p_emp_sec OUT NOCOPY VARCHAR2,
116 p_sort_order OUT NOCOPY VARCHAR2,
117 p_start_date OUT NOCOPY DATE -- User parameter
118 ,
119 p_end_date OUT NOCOPY DATE
120 ) IS
121 CURSOR csr_parameter_info (
122 p_payroll_action_id NUMBER
123 ) IS
124 SELECT to_number (
125 substr (
126 pay_se_trna_archive.get_parameter (legislative_parameters, 'LEGAL_EMPLOYER'),
127 1,
128 LENGTH (pay_se_trna_archive.get_parameter (legislative_parameters, 'LEGAL_EMPLOYER')) - 1
129 )
130 ) legal,
131 substr (
132 pay_se_trna_archive.get_parameter (legislative_parameters, 'REQUEST_FOR'),
133 1,
134 LENGTH (pay_se_trna_archive.get_parameter (legislative_parameters, 'REQUEST_FOR')) - 1
135 ) request_for,
136 substr (
137 pay_se_trna_archive.get_parameter (legislative_parameters, 'DIVISION'),
138 1,
139 LENGTH (pay_se_trna_archive.get_parameter (legislative_parameters, 'DIVISION')) - 1
140 ) division,
141 substr (
142 pay_se_trna_archive.get_parameter (legislative_parameters, 'REQUEST_FOR_DIV'),
143 1,
144 LENGTH (pay_se_trna_archive.get_parameter (legislative_parameters, 'REQUEST_FOR_DIV')) - 1
145 ) request_for_div,
146 substr (
147 pay_se_trna_archive.get_parameter (legislative_parameters, 'AGREEMENT_AREA'),
148 1,
149 LENGTH (pay_se_trna_archive.get_parameter (legislative_parameters, 'AGREEMENT_AREA')) - 1
150 ) agreement_area,
151 substr (
152 pay_se_trna_archive.get_parameter (legislative_parameters, 'REQUEST_FOR_AGREEMENT'),
153 1,
154 LENGTH (pay_se_trna_archive.get_parameter (legislative_parameters, 'REQUEST_FOR_AGREEMENT')) - 1
155 )
156 request_for_agreement,
157 (pay_se_trna_archive.get_parameter (legislative_parameters, 'REPORT_DATE')) report_date,
158 substr (
159 pay_se_trna_archive.get_parameter (legislative_parameters, 'PRECEDENCE_END_DATE'),
160 1,
161 LENGTH (pay_se_trna_archive.get_parameter (legislative_parameters, 'PRECEDENCE_END_DATE')) - 1
162 )
163 precedence_end_date1,
164 substr (
165 pay_se_trna_archive.get_parameter (legislative_parameters, 'EMP_CATG'),
166 1,
167 LENGTH (pay_se_trna_archive.get_parameter (legislative_parameters, 'EMP_CATG')) - 1
168 ) emp_catg,
169 substr (
170 pay_se_trna_archive.get_parameter (legislative_parameters, 'ASG_CATG'),
171 1,
172 LENGTH (pay_se_trna_archive.get_parameter (legislative_parameters, 'ASG_CATG')) - 1
173 ) asg_catg,
174 substr (
175 pay_se_trna_archive.get_parameter (legislative_parameters, 'EMP_SEC'),
176 1,
177 LENGTH (pay_se_trna_archive.get_parameter (legislative_parameters, 'EMP_SEC')) - 1
178 ) requesting_emp_sec,
179 substr (
180 pay_se_trna_archive.get_parameter (legislative_parameters, 'SORT_ORDER'),
181 1,
182 LENGTH (pay_se_trna_archive.get_parameter (legislative_parameters, 'SORT_ORDER')) - 1
183 ) sort_order,
184 (pay_se_trna_archive.get_parameter (legislative_parameters, 'EFFECTIVE_START_DATE')) eff_start_date,
185 (pay_se_trna_archive.get_parameter (legislative_parameters, 'EFFECTIVE_END_DATE'))
186 eff_end_date,
187 (pay_se_trna_archive.get_parameter (legislative_parameters, 'PRECEDENCE_END_DATE')) precedence_end_date,
188 (pay_se_trna_archive.get_parameter (legislative_parameters, 'START_DATE_OF_BIRTH'))
189 start_date_of_birth,
190 (pay_se_trna_archive.get_parameter (legislative_parameters, 'END_DATE_OF_BIRTH')) end_date_of_birth,
191 effective_date
192 effective_date, business_group_id bg_id
193 FROM pay_payroll_actions
194 WHERE payroll_action_id = p_payroll_action_id;
195
196 lr_parameter_info csr_parameter_info%ROWTYPE;
197 l_proc VARCHAR2 (240) := g_package || ' GET_ALL_PARAMETERS ';
198 BEGIN
199 fnd_file.put_line (fnd_file.LOG, 'Entering Procedure GET_ALL_PARAMETER ');
200 OPEN csr_parameter_info (p_payroll_action_id);
201 --FETCH csr_parameter_info into lr_parameter_info;
202 FETCH csr_parameter_info INTO lr_parameter_info;
203 CLOSE csr_parameter_info;
204 p_legal_employer_id := lr_parameter_info.legal;
205 p_request_for_all_or_not := lr_parameter_info.request_for;
206 p_div_code := lr_parameter_info.division;
207 p_request_for_div := lr_parameter_info.request_for_div;
208 p_agreement_area := lr_parameter_info.agreement_area;
209 p_request_for_agreement := lr_parameter_info.request_for_agreement;
210 p_report_date := fnd_date.canonical_to_date (lr_parameter_info.report_date);
211 p_precedence_end_date := fnd_date.canonical_to_date (lr_parameter_info.precedence_end_date);
212 p_emp_catg := lr_parameter_info.emp_catg;
213 p_asg_catg := lr_parameter_info.asg_catg;
214 p_start_date_of_birth := fnd_date.canonical_to_date (lr_parameter_info.start_date_of_birth);
215 p_end_date_of_birth := fnd_date.canonical_to_date (lr_parameter_info.end_date_of_birth);
216 p_emp_sec := lr_parameter_info.requesting_emp_sec;
217 p_start_date := fnd_date.canonical_to_date (lr_parameter_info.eff_start_date);
218 p_end_date := fnd_date.canonical_to_date (lr_parameter_info.eff_end_date);
219 p_effective_date := lr_parameter_info.effective_date;
220 p_business_group_id := lr_parameter_info.bg_id;
221 p_sort_order := lr_parameter_info.sort_order;
222
223 SELECT decode (lr_parameter_info.emp_catg, 'B', 'BC', 'W', 'WC', NULL)
224 INTO p_emp_catg
225 FROM dual;
226
227 IF g_debug THEN
228 hr_utility.set_location (' Leaving Procedure GET_ALL_PARAMETERS', 30);
229 END IF;
230 END get_all_parameters;
231 /* RANGE CODE */
232 PROCEDURE range_code (
233 p_payroll_action_id IN NUMBER,
234 p_sql OUT NOCOPY VARCHAR2
235 ) IS
236 l_action_info_id NUMBER;
237 l_ovn NUMBER;
238 l_business_group_id NUMBER;
239 l_effective_date DATE;
240 l_current_start_date DATE;
241 l_current_end_date DATE;
242 l_previous_start_date DATE;
243 l_previous_end_date DATE;
244
245 -- Archiving the data , as this will fire once
246 CURSOR csr_legal_employer_details (
247 csr_v_legal_employer_id hr_organization_information.organization_id%TYPE
248 ) IS
249 SELECT o1.NAME legal_employer_name, hoi2.org_information2 org_number, hoi1.organization_id legal_id
250 FROM hr_organization_units o1, hr_organization_information hoi1, hr_organization_information hoi2
251 WHERE o1.business_group_id = g_business_group_id
252 AND hoi1.organization_id = o1.organization_id
253 AND hoi1.organization_id = nvl (csr_v_legal_employer_id, hoi1.organization_id)
254 AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
255 AND hoi1.org_information_context = 'CLASS'
256 AND o1.organization_id = hoi2.organization_id
257 AND hoi2.org_information_context = 'SE_LEGAL_EMPLOYER_DETAILS';
258
259 l_legal_employer_details csr_legal_employer_details%ROWTYPE;
260 l_le_has_employee VARCHAR2 (2);
261 l_curr_avg_men_count NUMBER;
262 l_curr_avg_women_count NUMBER;
263 l_prev_avg_men_count NUMBER;
264 l_prev_avg_women_count NUMBER;
265 -- Archiving the data , as this will fire once
266 BEGIN
267 IF g_debug THEN
268 hr_utility.set_location (' Entering Procedure RANGE_CODE', 40);
269 END IF;
270
271 p_sql :=
272 'SELECT DISTINCT person_id
273 FROM per_people_f ppf
274 ,pay_payroll_actions ppa
275 WHERE ppa.payroll_action_id = :payroll_action_id
276 AND ppa.business_group_id = ppf.business_group_id
277 ORDER BY ppf.person_id';
278 g_payroll_action_id := p_payroll_action_id;
279 g_business_group_id := NULL;
280 g_effective_date := NULL;
281 g_legal_employer_id := NULL;
282 g_local_unit_id := NULL;
283 pay_se_trna_archive.get_all_parameters (
284 g_payroll_action_id,
285 g_business_group_id,
286 g_effective_date,
287 g_legal_employer_id,
288 g_request_for,
289 g_div_code,
290 g_request_for_div,
291 g_agreement_area,
292 g_request_for_agreement,
293 g_report_date,
294 g_precedence_end_date,
295 g_start_date_of_birth,
296 g_end_date_of_birth,
297 g_emp_catg,
298 g_asg_catg,
299 g_emp_sec,
300 g_sort_order,
301 g_start_date,
302 g_end_date
303 );
304
305 IF g_request_for = 'REQUESTING_ORG' THEN
306 OPEN csr_legal_employer_details (g_legal_employer_id);
307 FETCH csr_legal_employer_details INTO l_legal_employer_details;
308 CLOSE csr_legal_employer_details;
309 END IF;
310
311 pay_action_information_api.create_action_information (
312 p_action_information_id => l_action_info_id,
313 p_action_context_id => g_payroll_action_id,
314 p_action_context_type => 'PA',
315 p_object_version_number => l_ovn,
316 p_effective_date => g_effective_date,
317 p_source_id => NULL,
318 p_source_text => NULL,
319 p_action_information_category => 'EMEA REPORT DETAILS',
320 p_action_information1 => 'PYSETRNA',
321 p_action_information2 => hr_general.decode_lookup ('SE_TAX_CARD_REQUEST_LEVEL', g_request_for),
322 p_action_information3 => g_legal_employer_id,
323 p_action_information4 => l_legal_employer_details.legal_employer_name,
324 p_action_information5 => fnd_date.date_to_canonical (g_start_date),
325 p_action_information6 => fnd_date.date_to_canonical (g_end_date),
326 p_action_information7 => hr_general.decode_lookup ('SE_REQUEST_LEVEL', g_request_for_div),
327 p_action_information8 => g_div_code,
331 p_action_information12 => hr_general.decode_lookup ('SE_AGREEMENT_CODE', g_agreement_area),
328 p_action_information9 => hr_general.decode_lookup ('SE_DIVISION_CODE', g_div_code),
329 p_action_information10 => hr_general.decode_lookup ('SE_REQUEST_LEVEL', g_request_for_agreement),
330 p_action_information11 => g_agreement_area,
332 p_action_information13 => hr_general.decode_lookup ('EMPLOYEE_CATG', g_emp_catg),
333 p_action_information14 => hr_general.decode_lookup ('EMP_CAT', g_asg_catg),
334 p_action_information15 => hr_general.decode_lookup ('HR_SE_EMP_SECTION', g_emp_sec),
335 p_action_information16 => fnd_date.date_to_canonical (g_start_date_of_birth),
336 p_action_information17 => fnd_date.date_to_canonical (g_end_date_of_birth),
337 p_action_information18 => fnd_date.date_to_canonical (g_precedence_end_date),
338 p_action_information19 => fnd_date.date_to_canonical (g_report_date),
339 p_action_information20 => g_sort_order
340 );
341
342 FOR rec_legal_employer_details IN csr_legal_employer_details (g_legal_employer_id)
343 LOOP
344 pay_action_information_api.create_action_information (
345 p_action_information_id => l_action_info_id,
346 p_action_context_id => p_payroll_action_id,
347 p_action_context_type => 'PA',
348 p_object_version_number => l_ovn,
349 p_effective_date => g_effective_date,
350 p_source_id => NULL,
351 p_source_text => NULL,
352 p_action_information_category => 'EMEA REPORT INFORMATION',
353 p_action_information1 => 'PYSETRNA',
354 p_action_information2 => 'LE',
355 p_action_information3 => rec_legal_employer_details.legal_id,
356 p_action_information4 => rec_legal_employer_details.legal_employer_name,
357 p_action_information5 => rec_legal_employer_details.org_number
358 );
359 END LOOP;
360
361 --END IF; -- G_Archive End
362 IF g_debug THEN
363 hr_utility.set_location (' Leaving Procedure RANGE_CODE', 50);
364 END IF;
365 EXCEPTION
366 WHEN OTHERS THEN
367 -- Return cursor that selects no rows
368 p_sql := 'select 1 from dual where to_char(:payroll_action_id) = dummy';
369 END range_code;
370 /* ASSIGNMENT ACTION CODE */
371 PROCEDURE assignment_action_code (
372 p_payroll_action_id IN NUMBER,
373 p_start_person IN NUMBER,
374 p_end_person IN NUMBER,
375 p_chunk IN NUMBER
376 ) IS
377 CURSOR csr_legal_employer_details (
378 csr_v_legal_employer_id hr_organization_information.organization_id%TYPE
379 ) IS
380 SELECT o1.NAME legal_employer_name, hoi2.org_information2 org_number, hoi1.organization_id legal_id
381 FROM hr_organization_units o1, hr_organization_information hoi1, hr_organization_information hoi2
382 WHERE o1.business_group_id = g_business_group_id
383 AND hoi1.organization_id = o1.organization_id
384 AND hoi1.organization_id = nvl (csr_v_legal_employer_id, hoi1.organization_id)
385 AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
386 AND hoi1.org_information_context = 'CLASS'
387 AND o1.organization_id = hoi2.organization_id
388 AND hoi2.org_information_context = 'SE_LEGAL_EMPLOYER_DETAILS';
389
390 CURSOR csr_person_assignments (
391 p_legal_employer_id hr_organization_information.organization_id%TYPE
392 ) IS
393 SELECT papf.person_id, paaf.assignment_id, papf.full_name, papf.national_identifier, paaf.assignment_number,
394 paaf.employment_category, hsck.segment15 div_code, hsck.segment14 area_code
395 FROM per_all_assignments_f paaf,
396 hr_soft_coding_keyflex hsck,
397 hr_organization_units hou,
398 hr_organization_information hoi,
399 per_all_people_f papf
400 WHERE papf.person_id BETWEEN p_start_person AND p_end_person
401 AND g_report_date BETWEEN paaf.effective_start_date AND paaf.effective_end_date
402 AND paaf.effective_start_date BETWEEN papf.effective_start_date AND papf.effective_end_date
403 AND papf.person_id = paaf.person_id
404 AND hou.business_group_id = g_business_group_id
405 AND papf.date_of_birth BETWEEN nvl (g_start_date_of_birth, TO_DATE ('01/01/0001', 'dd/mm/yyyy'))
406 AND nvl (g_end_date_of_birth, TO_DATE ('31/12/4712', 'dd/mm/yyyy'))
407 AND paaf.employee_category IN ('WC', 'BC')
408 AND paaf.employee_category = nvl (g_emp_catg, paaf.employee_category)
409 AND nvl (paaf.employment_category, '-1') = nvl (g_asg_catg, nvl (paaf.employment_category, '-1'))
410 AND hsck.segment15 IS NOT NULL
411 AND hsck.segment15 = nvl (g_div_code, hsck.segment15)
412 AND hsck.segment14 IS NOT NULL
413 AND hsck.segment14 = nvl (g_agreement_area, hsck.segment14)
414 AND primary_flag = 'Y'
415 AND hou.organization_id = hoi.organization_id
416 AND hsck.soft_coding_keyflex_id = paaf.soft_coding_keyflex_id
417 AND hoi.organization_id = nvl (p_legal_employer_id, hoi.organization_id)
418 AND hoi.org_information_context = 'SE_LOCAL_UNITS'
422 TO_DATE ('31/12/4712', 'dd/mm/yyyy')
419 AND hoi.org_information1 = hsck.segment2
420 AND nvl(fnd_date.canonical_to_date (hsck.segment16),TO_DATE ('01/01/0001', 'dd/mm/yyyy')) <= nvl (
421 g_precedence_end_date,
423 );
424 /*AND fnd_date.canonical_to_date (nvl('2001/01/01 00:00:00','2001/01/01 00:00:00')) <=
425 nvl(g_precedence_end_date,
426 fnd_date.canonical_to_date ('4712/12/31 00:00:00')
427 ); */
428
429 /* Cursor to get the Start Date of the Assignment */
430 CURSOR csr_start_date (
431 p_assignment_id per_all_assignments_f.assignment_id%TYPE
432 ) IS
433 SELECT min (effective_start_date)
434 FROM per_all_assignments_f paaf, per_assignment_status_types past
435 WHERE assignment_id = p_assignment_id
436 AND paaf.assignment_status_type_id = past.assignment_status_type_id
437 AND past.per_system_status IN ('ACTIVE_ASSIGN', 'SUSP_ASSIGN')
438 AND effective_start_date BETWEEN g_start_date AND g_end_date;
439
440 /* Cursor to get the Termination and Prcedence Date */
441 CURSOR csr_emp_details (
442 p_assignment_id per_all_assignments_f.assignment_id%TYPE,
443 p_effective_date DATE
444 ) IS
445 SELECT fnd_date.canonical_to_date (hsck.segment6) termination_date,
446 fnd_date.canonical_to_date (hsck.segment16)
447 precedence_date
448 FROM per_all_assignments_f paaf, hr_soft_coding_keyflex hsck
449 WHERE paaf.assignment_id = p_assignment_id
450 AND hsck.soft_coding_keyflex_id = paaf.soft_coding_keyflex_id
451 AND p_effective_date BETWEEN paaf.effective_start_date AND paaf.effective_end_date;
452
453 /* Cursor to retrieve Person Previous Employment Time Details */
454 CURSOR csr_person_prev_teta (
455 csr_v_person_id NUMBER,
456 csr_v_business_group_id NUMBER,
457 csr_v_effective_date DATE
458 ) IS
459 SELECT start_date, end_date, end_date - start_date prev_emp_days
460 FROM per_previous_employers_v ppev
461 WHERE ppev.person_id = csr_v_person_id
462 AND ppev.business_group_id = csr_v_business_group_id
463 AND ppev.start_date <= csr_v_effective_date
464 ORDER BY ppev.start_date ASC;
465
466 l_prepay_action_id NUMBER;
467 l_actid NUMBER;
468 l_assignment_id NUMBER;
469 l_action_sequence NUMBER;
470 l_assact_id NUMBER;
471 l_pact_id NUMBER;
472 l_flag NUMBER := 0;
473 l_action_info_id NUMBER;
474 l_ovn NUMBER;
475 l_emp_start_date DATE;
476 l_current_emp_time NUMBER;
477 l_prev_total_emp_time NUMBER;
478 l_total_emp_time NUMBER;
479 l_hire_date DATE;
480 l_termination_date DATE;
481 l_precedence_date DATE;
482 l_emp_termination_date DATE;
483 l_emp_precedence_date DATE;
484 l_emp_type VARCHAR2 (10);
485 BEGIN
486 IF g_debug THEN
487 hr_utility.set_location ('Entering Procedure ASSIGNMENT_ACTION_CODE', 60);
488 END IF;
489
490 pay_se_trna_archive.get_all_parameters (
491 p_payroll_action_id,
492 g_business_group_id,
493 g_effective_date,
494 g_legal_employer_id,
495 g_request_for,
496 g_div_code,
497 g_request_for_div,
498 g_agreement_area,
499 g_request_for_agreement,
500 g_report_date,
501 g_precedence_end_date,
502 g_start_date_of_birth,
503 g_end_date_of_birth,
504 g_emp_catg,
505 g_asg_catg,
506 g_emp_sec,
507 g_sort_order,
508 g_start_date,
509 g_end_date
510 );
511
512 FOR rec_legal_employer_details IN csr_legal_employer_details (g_legal_employer_id)
513 LOOP
514 FOR person_assignments_rec IN csr_person_assignments (rec_legal_employer_details.legal_id)
515 LOOP
516 l_hire_date := NULL;
517 l_emp_start_date := NULL;
518 l_prev_total_emp_time := 0;
519 l_termination_date := NULL;
520 l_precedence_date := NULL;
521 l_emp_type := NULL;
522 l_emp_termination_date := NULL;
523 l_emp_precedence_date := NULL;
524 -- Get the Start Date For Current Employment
525 OPEN csr_start_date (person_assignments_rec.assignment_id);
526 FETCH csr_start_date INTO l_emp_start_date;
527 CLOSE csr_start_date;
528
529 IF l_emp_start_date IS NOT NULL THEN
530 l_current_emp_time := g_effective_date - l_emp_start_date;
531
532 FOR person_prev_teta_rec IN csr_person_prev_teta (
533 person_assignments_rec.person_id,
534 g_business_group_id,
535 g_effective_date
536 )
537 LOOP
538 IF l_hire_date IS NULL THEN
539 l_hire_date := person_prev_teta_rec.start_date;
540 END IF;
541
542 l_prev_total_emp_time := l_prev_total_emp_time + person_prev_teta_rec.prev_emp_days;
543 END LOOP;
544
545 IF l_hire_date IS NULL THEN
546 l_hire_date := l_emp_start_date;
547 END IF;
548
549 l_total_emp_time := l_current_emp_time + l_prev_total_emp_time;
550 OPEN csr_emp_details (person_assignments_rec.assignment_id, g_effective_date);
551 FETCH csr_emp_details INTO l_emp_termination_date, l_emp_precedence_date;
552 CLOSE csr_emp_details;
553
554 --If the termination date is in future and and the assignment category is termporay employment
555
556 IF l_emp_termination_date IS NOT NULL
557 AND l_emp_termination_date > g_effective_date
558 AND person_assignments_rec.employment_category = 'SE_TE' THEN
559 l_termination_date := l_emp_termination_date;
560 END IF;
561
562 IF l_emp_precedence_date IS NOT NULL AND l_emp_termination_date > g_effective_date THEN
563 l_termination_date := NULL;
564 l_precedence_date := l_emp_precedence_date;
565 END IF;
566
567 SELECT pay_assignment_actions_s.NEXTVAL
568 INTO l_actid
569 FROM dual;
570
571 --
572 g_index_assact := g_index_assact + 1;
573 g_lock_table (g_index_assact).archive_assact_id := l_actid;
574 -- Create the archive assignment action
575 hr_nonrun_asact.insact (l_actid, person_assignments_rec.assignment_id, p_payroll_action_id, p_chunk, NULL);
576
577 IF l_precedence_date IS NOT NULL THEN
578 l_emp_type := 'PRE';
579 ELSIF l_termination_date IS NOT NULL THEN
580 l_emp_type := 'TLE';
581 ELSE
582 l_emp_type := 'REG';
583 END IF;
584
585 IF g_emp_sec IS NULL THEN
586 pay_action_information_api.create_action_information (
587 p_action_information_id => l_action_info_id,
588 p_action_context_id => l_actid,
589 p_action_context_type => 'AAP',
590 p_object_version_number => l_ovn,
591 p_effective_date => g_effective_date,
592 p_source_id => NULL,
593 p_source_text => NULL,
594 p_action_information_category => 'EMEA REPORT INFORMATION',
595 p_action_information1 => 'PYSETRNA',
596 p_action_information2 => g_payroll_action_id,
597 p_action_information3 => person_assignments_rec.person_id,
598 p_action_information4 => person_assignments_rec.national_identifier,
599 p_action_information5 => person_assignments_rec.assignment_number,
600 p_action_information6 => person_assignments_rec.full_name,
601 p_action_information7 => fnd_date.date_to_canonical (l_hire_date),
602 p_action_information8 => fnd_number.number_to_canonical (l_total_emp_time),
606 p_action_information12 => g_emp_sec,
603 p_action_information9 => fnd_date.date_to_canonical (l_precedence_date),
604 p_action_information10 => fnd_date.date_to_canonical (l_termination_date),
605 p_action_information11 => l_emp_type,
607 p_action_information13 => rec_legal_employer_details.legal_id,
608 p_action_information14 => person_assignments_rec.div_code,
609 p_action_information15 => person_assignments_rec.area_code,
610 p_action_information16 => hr_general.decode_lookup (
611 'SE_DIVISION_CODE',
612 person_assignments_rec.div_code
613 ),
614 p_action_information17 => hr_general.decode_lookup (
615 'SE_AGREEMENT_CODE',
616 person_assignments_rec.area_code
617 )
618 );
619 ELSIF g_emp_sec = 'REGULAR' AND l_precedence_date IS NULL AND l_termination_date IS NULL THEN
620 pay_action_information_api.create_action_information (
621 p_action_information_id => l_action_info_id,
622 p_action_context_id => l_actid,
623 p_action_context_type => 'AAP',
624 p_object_version_number => l_ovn,
625 p_effective_date => g_effective_date,
626 p_source_id => NULL,
627 p_source_text => NULL,
628 p_action_information_category => 'EMEA REPORT INFORMATION',
629 p_action_information1 => 'PYSETRNA',
630 p_action_information2 => g_payroll_action_id,
631 p_action_information3 => person_assignments_rec.person_id,
632 p_action_information4 => person_assignments_rec.national_identifier,
633 p_action_information5 => person_assignments_rec.assignment_number,
634 p_action_information6 => person_assignments_rec.full_name,
635 p_action_information7 => fnd_date.date_to_canonical (l_hire_date),
636 p_action_information8 => l_total_emp_time,
637 p_action_information9 => NULL,
638 p_action_information10 => NULL,
639 p_action_information11 => l_emp_type,
640 p_action_information12 => g_emp_sec,
641 p_action_information13 => rec_legal_employer_details.legal_id,
642 p_action_information14 => person_assignments_rec.div_code,
643 p_action_information15 => person_assignments_rec.area_code,
644 p_action_information16 => hr_general.decode_lookup (
645 'SE_DIVISION_CODE',
646 person_assignments_rec.div_code
647 ),
648 p_action_information17 => hr_general.decode_lookup (
649 'SE_AGREEMENT_CODE',
650 person_assignments_rec.area_code
651 )
652 );
653 ELSIF g_emp_sec = 'PREVIOUS' AND l_precedence_date IS NOT NULL THEN
654 pay_action_information_api.create_action_information (
655 p_action_information_id => l_action_info_id,
656 p_action_context_id => l_actid,
657 p_action_context_type => 'AAP',
658 p_object_version_number => l_ovn,
659 p_effective_date => g_effective_date,
660 p_source_id => NULL,
661 p_source_text => NULL,
662 p_action_information_category => 'EMEA REPORT INFORMATION',
663 p_action_information1 => 'PYSETRNA',
664 p_action_information2 => g_payroll_action_id,
665 p_action_information3 => person_assignments_rec.person_id,
666 p_action_information4 => person_assignments_rec.national_identifier,
667 p_action_information5 => person_assignments_rec.assignment_number,
668 p_action_information6 => person_assignments_rec.full_name,
669 p_action_information7 => fnd_date.date_to_canonical (l_hire_date),
670 p_action_information8 => l_total_emp_time,
671 p_action_information9 => fnd_date.date_to_canonical (l_precedence_date),
672 p_action_information10 => NULL,
673 p_action_information11 => l_emp_type,
674 p_action_information12 => g_emp_sec,
678 p_action_information16 => hr_general.decode_lookup (
675 p_action_information13 => rec_legal_employer_details.legal_id,
676 p_action_information14 => person_assignments_rec.div_code,
677 p_action_information15 => person_assignments_rec.area_code,
679 'SE_DIVISION_CODE',
680 person_assignments_rec.div_code
681 ),
682 p_action_information17 => hr_general.decode_lookup (
683 'SE_AGREEMENT_CODE',
684 person_assignments_rec.area_code
685 )
686 );
687 ELSIF g_emp_sec = 'TIME_LIMITED' AND l_precedence_date IS NULL AND l_termination_date IS NOT NULL THEN
688 pay_action_information_api.create_action_information (
689 p_action_information_id => l_action_info_id,
690 p_action_context_id => l_actid,
691 p_action_context_type => 'AAP',
692 p_object_version_number => l_ovn,
693 p_effective_date => g_effective_date,
694 p_source_id => NULL,
695 p_source_text => NULL,
696 p_action_information_category => 'EMEA REPORT INFORMATION',
697 p_action_information1 => 'PYSETRNA',
698 p_action_information2 => g_payroll_action_id,
699 p_action_information3 => person_assignments_rec.person_id,
700 p_action_information4 => person_assignments_rec.national_identifier,
701 p_action_information5 => person_assignments_rec.assignment_number,
702 p_action_information6 => person_assignments_rec.full_name,
703 p_action_information7 => fnd_date.date_to_canonical (l_hire_date),
704 p_action_information8 => l_total_emp_time,
705 p_action_information9 => NULL,
706 p_action_information10 => fnd_date.date_to_canonical (l_termination_date),
707 p_action_information11 => l_emp_type,
708 p_action_information12 => g_emp_sec,
709 p_action_information13 => rec_legal_employer_details.legal_id,
710 p_action_information14 => person_assignments_rec.div_code,
711 p_action_information15 => person_assignments_rec.area_code,
712 p_action_information16 => hr_general.decode_lookup (
713 'SE_DIVISION_CODE',
714 person_assignments_rec.div_code
715 ),
716 p_action_information17 => hr_general.decode_lookup (
717 'SE_AGREEMENT_CODE',
718 person_assignments_rec.area_code
719 )
720 );
721 END IF;
722 END IF;
723 -- Create archive to prepayment assignment action interlock
724 --
725 --hr_nonrun_asact.insint(l_actid,rec_prepaid_assignments.prepaid_action_id);
726
727 END LOOP;
728 END LOOP;
729 END assignment_action_code;
730 /* INITIALIZATION CODE */
731 PROCEDURE initialization_code (
732 p_payroll_action_id IN NUMBER
733 ) IS
734 BEGIN
735 IF g_debug THEN
736 hr_utility.set_location (' Entering Procedure INITIALIZATION_CODE', 80);
737 END IF;
738 END initialization_code;
739 /* ARCHIVE CODE */
740 PROCEDURE archive_code (
741 p_assignment_action_id IN NUMBER,
742 p_effective_date IN DATE
743 ) IS
744 BEGIN
745 IF g_debug THEN
746 hr_utility.set_location (' Entering Procedure ARCHIVE_CODE', 380);
747 END IF;
748 END archive_code;
749 END pay_se_trna_archive;