[Home] [Help]
Skip to content
PACKAGE BODY: APPS.PAY_SE_TRNA_ARCHIVE
Source
4 archive_assact_id NUMBER
1 PACKAGE BODY pay_se_trna_archive AS
2 /* $Header: pysetrna.pkb 120.2.12010000.2 2009/03/13 07:05:36 rsengupt ship $ */
3 TYPE lock_rec IS RECORD (
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 (
150 ) agreement_area,
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
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
253 AND hoi1.organization_id = nvl (csr_v_legal_employer_id, hoi1.organization_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
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,
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,
331 p_action_information12 => hr_general.decode_lookup ('SE_AGREEMENT_CODE', 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'
419 AND hoi.org_information1 = hsck.segment2
420 AND (SELECT nvl(fnd_date.canonical_to_date (hs.segment16),TO_DATE ('01/01/0001', 'dd/mm/yyyy'))
421 FROM hr_soft_coding_keyflex hs
422 WHERE hs.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id) <= nvl (
423 g_precedence_end_date,
424 TO_DATE ('31/12/4712', 'dd/mm/yyyy')
425 ); -- Inner Query for 7162312
426 /*AND fnd_date.canonical_to_date (nvl('2001/01/01 00:00:00','2001/01/01 00:00:00')) <=
427 nvl(g_precedence_end_date,
428 fnd_date.canonical_to_date ('4712/12/31 00:00:00')
429 ); */
430
431 /* Cursor to get the Start Date of the Assignment */
432 CURSOR csr_start_date (
433 p_assignment_id per_all_assignments_f.assignment_id%TYPE
434 ) IS
435 SELECT min (effective_start_date)
436 FROM per_all_assignments_f paaf, per_assignment_status_types past
437 WHERE assignment_id = p_assignment_id
438 AND paaf.assignment_status_type_id = past.assignment_status_type_id
439 AND past.per_system_status IN ('ACTIVE_ASSIGN', 'SUSP_ASSIGN')
440 AND effective_start_date BETWEEN nvl(g_start_date,effective_start_date) AND nvl(g_end_date, effective_end_date); -- nvl for 7162312
441
442 /* Cursor to get the Termination and Prcedence Date */
443 CURSOR csr_emp_details (
444 p_assignment_id per_all_assignments_f.assignment_id%TYPE,
445 p_effective_date DATE
446 ) IS
447 SELECT fnd_date.canonical_to_date (hsck.segment6) termination_date,
448 fnd_date.canonical_to_date (hsck.segment16)
449 precedence_date
450 FROM per_all_assignments_f paaf, hr_soft_coding_keyflex hsck
451 WHERE paaf.assignment_id = p_assignment_id
452 AND hsck.soft_coding_keyflex_id = paaf.soft_coding_keyflex_id
453 AND p_effective_date BETWEEN paaf.effective_start_date AND paaf.effective_end_date;
454
455 /* Cursor to retrieve Person Previous Employment Time Details */
456 CURSOR csr_person_prev_teta (
457 csr_v_person_id NUMBER,
458 csr_v_business_group_id NUMBER,
459 csr_v_effective_date DATE
460 ) IS
461 SELECT start_date, end_date, end_date - start_date prev_emp_days
462 FROM per_previous_employers_v ppev
463 WHERE ppev.person_id = csr_v_person_id
464 AND ppev.business_group_id = csr_v_business_group_id
465 AND ppev.start_date <= csr_v_effective_date
466 ORDER BY ppev.start_date ASC;
467
468 l_prepay_action_id NUMBER;
469 l_actid NUMBER;
470 l_assignment_id NUMBER;
471 l_action_sequence NUMBER;
472 l_assact_id NUMBER;
473 l_pact_id NUMBER;
474 l_flag NUMBER := 0;
475 l_action_info_id NUMBER;
476 l_ovn NUMBER;
477 l_emp_start_date DATE;
478 l_current_emp_time NUMBER;
479 l_prev_total_emp_time NUMBER;
480 l_total_emp_time NUMBER;
481 l_hire_date DATE;
482 l_termination_date DATE;
483 l_precedence_date DATE;
487 BEGIN
484 l_emp_termination_date DATE;
485 l_emp_precedence_date DATE;
486 l_emp_type VARCHAR2 (10);
488 IF g_debug THEN
489 hr_utility.set_location ('Entering Procedure ASSIGNMENT_ACTION_CODE', 60);
490 END IF;
491
492 pay_se_trna_archive.get_all_parameters (
493 p_payroll_action_id,
494 g_business_group_id,
495 g_effective_date,
496 g_legal_employer_id,
497 g_request_for,
498 g_div_code,
499 g_request_for_div,
500 g_agreement_area,
501 g_request_for_agreement,
502 g_report_date,
503 g_precedence_end_date,
504 g_start_date_of_birth,
505 g_end_date_of_birth,
506 g_emp_catg,
507 g_asg_catg,
508 g_emp_sec,
509 g_sort_order,
510 g_start_date,
511 g_end_date
512 );
513
514 FOR rec_legal_employer_details IN csr_legal_employer_details (g_legal_employer_id)
515 LOOP
516 FOR person_assignments_rec IN csr_person_assignments (rec_legal_employer_details.legal_id)
517 LOOP
518 l_hire_date := NULL;
519 l_emp_start_date := NULL;
520 l_prev_total_emp_time := 0;
521 l_termination_date := NULL;
522 l_precedence_date := NULL;
523 l_emp_type := NULL;
524 l_emp_termination_date := NULL;
525 l_emp_precedence_date := NULL;
526 -- Get the Start Date For Current Employment
527 OPEN csr_start_date (person_assignments_rec.assignment_id);
528 FETCH csr_start_date INTO l_emp_start_date;
529 CLOSE csr_start_date;
530
531 IF l_emp_start_date IS NOT NULL THEN
532 l_current_emp_time := g_effective_date - l_emp_start_date;
533
534 FOR person_prev_teta_rec IN csr_person_prev_teta (
535 person_assignments_rec.person_id,
536 g_business_group_id,
537 g_effective_date
538 )
539 LOOP
540 IF l_hire_date IS NULL THEN
541 l_hire_date := person_prev_teta_rec.start_date;
542 END IF;
543
544 l_prev_total_emp_time := l_prev_total_emp_time + person_prev_teta_rec.prev_emp_days;
545 END LOOP;
546
547 IF l_hire_date IS NULL THEN
548 l_hire_date := l_emp_start_date;
549 END IF;
550
551 l_total_emp_time := l_current_emp_time + l_prev_total_emp_time;
552 OPEN csr_emp_details (person_assignments_rec.assignment_id, g_effective_date);
553 FETCH csr_emp_details INTO l_emp_termination_date, l_emp_precedence_date;
554 CLOSE csr_emp_details;
555
556 --If the termination date is in future and and the assignment category is termporay employment
557
558 IF l_emp_termination_date IS NOT NULL
559 AND l_emp_termination_date > g_effective_date
560 AND person_assignments_rec.employment_category = 'SE_TE' THEN
561 l_termination_date := l_emp_termination_date;
562 END IF;
563
564 IF l_emp_precedence_date IS NOT NULL AND l_emp_termination_date > g_effective_date THEN
565 l_termination_date := NULL;
566 l_precedence_date := l_emp_precedence_date;
567 END IF;
568
569 SELECT pay_assignment_actions_s.NEXTVAL
570 INTO l_actid
571 FROM dual;
572
573 --
574 g_index_assact := g_index_assact + 1;
575 g_lock_table (g_index_assact).archive_assact_id := l_actid;
576 -- Create the archive assignment action
577 hr_nonrun_asact.insact (l_actid, person_assignments_rec.assignment_id, p_payroll_action_id, p_chunk, NULL);
578
579 IF l_precedence_date IS NOT NULL THEN
580 l_emp_type := 'PRE';
581 ELSIF l_termination_date IS NOT NULL THEN
582 l_emp_type := 'TLE';
583 ELSE
584 l_emp_type := 'REG';
585 END IF;
586
587 IF g_emp_sec IS NULL THEN
588 pay_action_information_api.create_action_information (
589 p_action_information_id => l_action_info_id,
590 p_action_context_id => l_actid,
591 p_action_context_type => 'AAP',
592 p_object_version_number => l_ovn,
593 p_effective_date => g_effective_date,
594 p_source_id => NULL,
595 p_source_text => NULL,
596 p_action_information_category => 'EMEA REPORT INFORMATION',
597 p_action_information1 => 'PYSETRNA',
598 p_action_information2 => g_payroll_action_id,
599 p_action_information3 => person_assignments_rec.person_id,
600 p_action_information4 => person_assignments_rec.national_identifier,
601 p_action_information5 => person_assignments_rec.assignment_number,
602 p_action_information6 => person_assignments_rec.full_name,
603 p_action_information7 => fnd_date.date_to_canonical (l_hire_date),
607 p_action_information11 => l_emp_type,
604 p_action_information8 => fnd_number.number_to_canonical (l_total_emp_time),
605 p_action_information9 => fnd_date.date_to_canonical (l_precedence_date),
606 p_action_information10 => fnd_date.date_to_canonical (l_termination_date),
608 p_action_information12 => g_emp_sec,
609 p_action_information13 => rec_legal_employer_details.legal_id,
610 p_action_information14 => person_assignments_rec.div_code,
611 p_action_information15 => person_assignments_rec.area_code,
612 p_action_information16 => hr_general.decode_lookup (
613 'SE_DIVISION_CODE',
614 person_assignments_rec.div_code
615 ),
616 p_action_information17 => hr_general.decode_lookup (
617 'SE_AGREEMENT_CODE',
618 person_assignments_rec.area_code
619 )
620 );
621 ELSIF g_emp_sec = 'REGULAR' AND l_precedence_date IS NULL AND l_termination_date IS NULL THEN
622 pay_action_information_api.create_action_information (
623 p_action_information_id => l_action_info_id,
624 p_action_context_id => l_actid,
625 p_action_context_type => 'AAP',
626 p_object_version_number => l_ovn,
627 p_effective_date => g_effective_date,
628 p_source_id => NULL,
629 p_source_text => NULL,
630 p_action_information_category => 'EMEA REPORT INFORMATION',
631 p_action_information1 => 'PYSETRNA',
632 p_action_information2 => g_payroll_action_id,
633 p_action_information3 => person_assignments_rec.person_id,
634 p_action_information4 => person_assignments_rec.national_identifier,
635 p_action_information5 => person_assignments_rec.assignment_number,
636 p_action_information6 => person_assignments_rec.full_name,
637 p_action_information7 => fnd_date.date_to_canonical (l_hire_date),
638 p_action_information8 => l_total_emp_time,
639 p_action_information9 => NULL,
640 p_action_information10 => NULL,
641 p_action_information11 => l_emp_type,
642 p_action_information12 => g_emp_sec,
643 p_action_information13 => rec_legal_employer_details.legal_id,
644 p_action_information14 => person_assignments_rec.div_code,
645 p_action_information15 => person_assignments_rec.area_code,
646 p_action_information16 => hr_general.decode_lookup (
647 'SE_DIVISION_CODE',
648 person_assignments_rec.div_code
649 ),
650 p_action_information17 => hr_general.decode_lookup (
651 'SE_AGREEMENT_CODE',
652 person_assignments_rec.area_code
653 )
654 );
655 ELSIF g_emp_sec = 'PREVIOUS' AND l_precedence_date IS NOT NULL THEN
656 pay_action_information_api.create_action_information (
657 p_action_information_id => l_action_info_id,
658 p_action_context_id => l_actid,
659 p_action_context_type => 'AAP',
660 p_object_version_number => l_ovn,
661 p_effective_date => g_effective_date,
662 p_source_id => NULL,
663 p_source_text => NULL,
664 p_action_information_category => 'EMEA REPORT INFORMATION',
665 p_action_information1 => 'PYSETRNA',
666 p_action_information2 => g_payroll_action_id,
667 p_action_information3 => person_assignments_rec.person_id,
668 p_action_information4 => person_assignments_rec.national_identifier,
669 p_action_information5 => person_assignments_rec.assignment_number,
670 p_action_information6 => person_assignments_rec.full_name,
671 p_action_information7 => fnd_date.date_to_canonical (l_hire_date),
672 p_action_information8 => l_total_emp_time,
673 p_action_information9 => fnd_date.date_to_canonical (l_precedence_date),
674 p_action_information10 => NULL,
675 p_action_information11 => l_emp_type,
676 p_action_information12 => g_emp_sec,
677 p_action_information13 => rec_legal_employer_details.legal_id,
678 p_action_information14 => person_assignments_rec.div_code,
679 p_action_information15 => person_assignments_rec.area_code,
680 p_action_information16 => hr_general.decode_lookup (
681 'SE_DIVISION_CODE',
682 person_assignments_rec.div_code
683 ),
684 p_action_information17 => hr_general.decode_lookup (
685 'SE_AGREEMENT_CODE',
686 person_assignments_rec.area_code
687 )
688 );
689 ELSIF g_emp_sec = 'TIME_LIMITED' AND l_precedence_date IS NULL AND l_termination_date IS NOT NULL THEN
690 pay_action_information_api.create_action_information (
691 p_action_information_id => l_action_info_id,
692 p_action_context_id => l_actid,
693 p_action_context_type => 'AAP',
694 p_object_version_number => l_ovn,
695 p_effective_date => g_effective_date,
696 p_source_id => NULL,
697 p_source_text => NULL,
698 p_action_information_category => 'EMEA REPORT INFORMATION',
699 p_action_information1 => 'PYSETRNA',
700 p_action_information2 => g_payroll_action_id,
701 p_action_information3 => person_assignments_rec.person_id,
702 p_action_information4 => person_assignments_rec.national_identifier,
703 p_action_information5 => person_assignments_rec.assignment_number,
704 p_action_information6 => person_assignments_rec.full_name,
705 p_action_information7 => fnd_date.date_to_canonical (l_hire_date),
706 p_action_information8 => l_total_emp_time,
707 p_action_information9 => NULL,
708 p_action_information10 => fnd_date.date_to_canonical (l_termination_date),
709 p_action_information11 => l_emp_type,
710 p_action_information12 => g_emp_sec,
711 p_action_information13 => rec_legal_employer_details.legal_id,
712 p_action_information14 => person_assignments_rec.div_code,
713 p_action_information15 => person_assignments_rec.area_code,
714 p_action_information16 => hr_general.decode_lookup (
715 'SE_DIVISION_CODE',
716 person_assignments_rec.div_code
717 ),
718 p_action_information17 => hr_general.decode_lookup (
719 'SE_AGREEMENT_CODE',
720 person_assignments_rec.area_code
721 )
722 );
723 END IF;
724 END IF;
725 -- Create archive to prepayment assignment action interlock
726 --
727 --hr_nonrun_asact.insint(l_actid,rec_prepaid_assignments.prepaid_action_id);
728
729 END LOOP;
730 END LOOP;
731 END assignment_action_code;
732 /* INITIALIZATION CODE */
733 PROCEDURE initialization_code (
734 p_payroll_action_id IN NUMBER
735 ) IS
736 BEGIN
737 IF g_debug THEN
738 hr_utility.set_location (' Entering Procedure INITIALIZATION_CODE', 80);
739 END IF;
740 END initialization_code;
741 /* ARCHIVE CODE */
742 PROCEDURE archive_code (
743 p_assignment_action_id IN NUMBER,
744 p_effective_date IN DATE
745 ) IS
746 BEGIN
747 IF g_debug THEN
748 hr_utility.set_location (' Entering Procedure ARCHIVE_CODE', 380);
749 END IF;
750 END archive_code;
751 END pay_se_trna_archive;