[Home] [Help]
PACKAGE BODY: APPS.PAY_SE_STAT_OFFICE_ARCHIVE
Source
1 PACKAGE BODY pay_se_stat_office_archive AS
2 /* $Header: pysestoa.pkb 120.0.12000000.1 2007/04/20 09:06:00 abhgangu noship $ */
3 --
4 --
5 -- -----------------------------------------------------------------------------
6 -- Data types.
7 -- -----------------------------------------------------------------------------
8 --
9 g_business_group_id NUMBER;
10 g_legal_employer_id NUMBER;
11 g_report_date DATE;
12 g_effective_date DATE;
13 g_archive VARCHAR2(30);
14 g_debug boolean := hr_utility.debug_enabled;
15 g_payroll_action_id NUMBER;
16 g_package VARCHAR2(30) := 'pay_se_stat_office_archive.';
17 g_version VARCHAR2(10);
18 g_start_date DATE;
19 g_end_date DATE;
20
21 --
22 --
23 -- -----------------------------------------------------------------------------
24 -- Parse out parameters from string.
25 -- -----------------------------------------------------------------------------
26 --
27 FUNCTION get_parameter
28 (p_parameter_string IN VARCHAR2
29 ,p_token IN VARCHAR) RETURN VARCHAR2 IS
30 --
31 l_parameter pay_payroll_actions.legislative_parameters%TYPE := NULL;
32 l_start_pos NUMBER;
33 l_delimiter VARCHAR2(1) := ' ';
34 --
35 BEGIN
36 --
37 l_start_pos := INSTR(' ' || p_parameter_string, l_delimiter || p_token || '=');
38 --
39 IF l_start_pos = 0 THEN
40 l_delimiter := '|';
41 l_start_pos := INSTR(' ' || p_parameter_string, l_delimiter || p_token || '=');
42 END IF;
43 --
44 IF l_start_pos <> 0 THEN
45 l_start_pos := l_start_pos + LENGTH(p_token || '=');
46 l_parameter := SUBSTR(p_parameter_string, l_start_pos, INSTR(p_parameter_string || ' ', l_delimiter, l_start_pos) - l_start_pos);
47 END IF;
48 --
49 RETURN l_parameter;
50 --
51 END;
52 --
53 --
54 -- Get All Parameters
55 --
56 --
57 PROCEDURE GET_ALL_PARAMETERS(
58 p_payroll_action_id IN NUMBER -- In parameter
59 ,p_business_group_id OUT NOCOPY NUMBER -- Core parameter
60 ,p_effective_date OUT NOCOPY Date -- Core parameter
61 ,p_legal_employer_id OUT NOCOPY NUMBER -- User parameter
62 ,p_start_date OUT NOCOPY DATE -- User Parameter
63 ,p_end_date OUT NOCOPY DATE -- User Parameter
64 ,p_archive OUT NOCOPY VARCHAR2 -- User parameter
65 )
66 IS
67
68 CURSOR csr_parameter_info
69 (p_payroll_action_id NUMBER) IS
70 SELECT
71 TO_NUMBER ( GET_PARAMETER(legislative_parameters,'LEGAL_EMPLOYER') ) Legal
72 ,fnd_date.canonical_to_date(get_parameter(legislative_parameters,'START_DATE')) start_date
73 ,fnd_date.canonical_to_date(get_parameter(legislative_parameters,'END_DATE')) end_date
74 ,GET_PARAMETER(legislative_parameters,'ARCHIVE') ARCHIVE_OR_NOT
75 ,business_group_id BG_ID
76 ,effective_date
77 FROM pay_payroll_actions
78 WHERE payroll_action_id = p_payroll_action_id;
79
80
81 lr_parameter_info csr_parameter_info%ROWTYPE;
82 l_proc VARCHAR2(240):= g_package||' GET_ALL_PARAMETERS ';
83
84 BEGIN
85 OPEN csr_parameter_info (p_payroll_action_id);
86
87 FETCH csr_parameter_info
88 INTO p_legal_employer_id
89 ,p_start_date
90 ,p_end_date
91 ,p_archive
92 ,p_business_group_id
93 ,p_effective_date;
94 CLOSE csr_parameter_info;
95
96
97 IF g_debug THEN
98 hr_utility.set_location(' Leaving Procedure GET_ALL_PARAMETERS',30);
99 END IF;
100 END GET_ALL_PARAMETERS;
101
102 --
103 --
104 -- -----------------------------------------------------------------------------
105 -- Sets all legislative parameters as global variables for future use.
106 -- -----------------------------------------------------------------------------
107 --
108 PROCEDURE set_parameters
109 (p_payroll_action_id IN NUMBER) IS
110 --
111 CURSOR csr_parameters
112 (p_payroll_action_id IN NUMBER) IS
113 SELECT business_group_id
114 ,legislative_parameters
115 ,get_parameter(legislative_parameters, 'LEGAL_EMPLOYER_ID') legal_employer_id
116 ,fnd_date.canonical_to_date(get_parameter(legislative_parameters, 'DATE')) report_date
117 FROM pay_payroll_actions
118 WHERE payroll_action_id = p_payroll_action_id;
119 --
120 l_parameter_rec csr_parameters%ROWTYPE;
121 --
122 BEGIN
123 --
124 OPEN csr_parameters(p_payroll_action_id);
125 FETCH csr_parameters INTO l_parameter_rec;
126 CLOSE csr_parameters;
127 --
128 g_business_group_id := l_parameter_rec.business_group_id;
129 g_legal_employer_id := l_parameter_rec.legal_employer_id;
130 g_report_date := l_parameter_rec.report_date;
131 --
132 END set_parameters;
133 --
134 --
135 -- -----------------------------------------------------------------------------
136 --
137 -- -----------------------------------------------------------------------------
138 --
139 PROCEDURE range_code
140 (p_payroll_action_id IN NUMBER
141 ,p_sql OUT NOCOPY VARCHAR2) IS
142 --
143 CURSOR csr_legal_employer_details
144 (p_legal_employer_id IN NUMBER) IS
145 SELECT org.organization_id legal_employer_id
146 ,org.name
147 ,hoi1.org_information2
148 ,org.location_id
149 FROM hr_all_organization_units org
150 ,hr_organization_information hoi1
151 WHERE org.organization_id = p_legal_employer_id
152 AND hoi1.organization_id (+) = org.organization_id
153 AND hoi1.org_information_context (+) = 'SE_LEGAL_EMPLOYER_DETAILS';
154
155 CURSOR csr_location
156 (p_location_id NUMBER) IS
157 SELECT rpad(Address_line_1 || ', ' || Address_line_2 ||', ' ||Address_line_3,30,' ') address,
158 hr_general.DECODE_FND_COMM_LOOKUP('SE_POSTAL_CODE',postal_code) postal_code,
159 hr_general.decode_territory (country) country
160 FROM hr_locations
161 WHERE location_id = p_location_id;
162
163 CURSOR csr_le_contact_person
164 (p_legal_employer_id IN NUMBER) IS
165 SELECT substr(hoi.org_information3,1,25) contact_person
166 FROM hr_organization_information hoi
167 WHERE hoi.organization_id = p_legal_employer_id
168 AND hoi.org_information_context = 'SE_ORG_CONTACT_DETAILS';
169
170 --
171 l_legal_employer_details_rec csr_Legal_employer_details%ROWTYPE;
172 l_location_rec csr_location%ROWTYPE;
173 l_le_contact_person_rec csr_le_contact_person%ROWTYPE;
174 --
175 l_assact_id NUMBER;
176 l_ovn NUMBER;
177 l_action_info_id NUMBER;
178
179 BEGIN
180 --
181 --
182 -- Setup legislative parameters as global values for future use.
183 --
184 set_parameters(p_payroll_action_id);
185 --
186 --
187 -- Archive report information.
188 --
189 --
190 IF g_debug THEN
191 hr_utility.set_location(' Entering Procedure RANGE_CODE',40);
192 END IF;
193
194 p_sql := 'SELECT DISTINCT person_id
195 FROM per_people_f ppf
196 ,pay_payroll_actions ppa
197 WHERE ppa.payroll_action_id = :payroll_action_id
198 AND ppa.business_group_id = ppf.business_group_id
199 ORDER BY ppf.person_id';
200
201 g_payroll_action_id :=p_payroll_action_id;
202 g_business_group_id := null;
203 g_legal_employer_id := null;
204 g_start_date := null;
205 g_end_date := null;
206 g_version := null;
207 g_archive := null;
208
209
210 GET_ALL_PARAMETERS
211 (p_payroll_action_id
212 ,g_business_group_id
213 ,g_effective_date
214 ,g_legal_employer_id
215 ,g_start_date
216 ,g_end_date
217 ,g_archive
218 );
219
220
221 IF g_archive = 'Y'
222 THEN
223
224
225 OPEN csr_legal_employer_details(g_legal_employer_id);
226 FETCH csr_legal_employer_details INTO l_legal_employer_details_rec;
227 CLOSE csr_legal_employer_details;
228
229
230 pay_action_information_api.create_action_information (
231 p_action_information_id=> l_action_info_id,
232 p_action_context_id=> p_payroll_action_id,
233 p_action_context_type=> 'PA',
234 p_object_version_number=> l_ovn,
235 p_effective_date=> g_effective_date,
236 p_source_id=> NULL,
237 p_source_text=> NULL,
238 p_action_information_category=> 'EMEA REPORT DETAILS',
239 p_action_information1=> 'PYSESTOA',
240 p_action_information2=> to_char(g_business_group_id),
241 p_action_information3=> to_char(g_legal_employer_id),
242 p_action_information4=> l_legal_employer_details_rec.name,
243 p_action_information5=> fnd_date.date_to_canonical(g_effective_date),
244 p_action_information6=> fnd_date.date_to_canonical(g_start_date),
245 p_action_information7=> fnd_date.date_to_canonical(g_end_date),
246 p_action_information8=> NULL,
247 p_action_information9=> NULL,
248 p_action_information10=> NULL,
249 p_action_information11=> NULL,
250 p_action_information12=> NULL,
251 p_action_information13=> NULL,
252 p_action_information14=> NULL,
253 p_action_information15=> NULL,
254 p_action_information16=> NULL,
255 p_action_information17=> NULL,
256 p_action_information18=> NULL,
257 p_action_information19=> NULL,
258 p_action_information20=> NULL,
259 p_action_information21=> NULL,
260 p_action_information22=> NULL,
261 p_action_information23=> NULL,
262 p_action_information24=> NULL,
263 p_action_information25=> NULL,
264 p_action_information26=> NULL,
265 p_action_information27=> NULL,
266 p_action_information28=> NULL,
267 p_action_information29=> NULL,
268 p_action_information30=> NULL
269 );
270
271
272 OPEN csr_location(l_legal_employer_details_rec.location_id);
273 FETCH csr_location INTO l_location_rec;
274 CLOSE csr_location;
275
276
277 OPEN csr_le_contact_person(g_legal_employer_id);
278 FETCH csr_le_contact_person INTO l_le_contact_person_rec;
279 CLOSE csr_le_contact_person;
280
281 pay_action_information_api.create_action_information (
282 p_action_information_id=> l_action_info_id,
283 p_action_context_id=> p_payroll_action_id,
284 p_action_context_type=> 'PA',
285 p_object_version_number=> l_ovn,
286 p_effective_date=> g_effective_date,
287 p_source_id=> NULL,
288 p_source_text=> NULL,
289 p_action_information_category=> 'EMEA REPORT INFORMATION',
290 p_action_information1=> 'PYSESTOA',
291 p_action_information2=> 'LE',
292 p_action_information3=> g_legal_employer_id,
293 p_action_information4=> l_legal_employer_details_rec.name,
294 p_action_information5=> l_legal_employer_details_rec.ORG_INFORMATION2,
295 p_action_information6=> l_location_rec.address,
296 p_action_information7=> l_location_rec.postal_code,
297 p_action_information8=> l_location_rec.country,
298 p_action_information9=> l_le_contact_person_rec.contact_person,
299 p_action_information10=> NULL,
300 p_action_information11=> NULL,
301 p_action_information12=> NULL,
302 p_action_information13=> NULL,
303 p_action_information14=> NULL,
304 p_action_information15=> NULL,
305 p_action_information16=> NULL,
306 p_action_information17=> NULL,
307 p_action_information18=> NULL,
308 p_action_information19=> NULL,
309 p_action_information20=> NULL,
310 p_action_information21=> NULL,
311 p_action_information22=> NULL,
312 p_action_information23=> NULL,
313 p_action_information24=> NULL,
314 p_action_information25=> NULL,
315 p_action_information26=> NULL,
316 p_action_information27=> NULL,
317 p_action_information28=> NULL,
318 p_action_information29=> NULL,
319 p_action_information30=> NULL
320 );
321
322
323 END IF; -- G_Archive End
324
325 IF g_debug THEN
326 hr_utility.set_location(' Leaving Procedure RANGE_CODE',50);
327 END IF; --
328 END range_code;
329 --
330 -- ---------------------------------------------------------------------
331 -- Function to get defined balance id
332 -- ---------------------------------------------------------------------
333 --
334 FUNCTION GET_DEFINED_BALANCE_ID(p_user_name IN VARCHAR2) RETURN NUMBER
335 IS
336 /* Cursor to retrieve Defined Balance Id */
337
338 CURSOR csr_def_bal_id
339 (p_user_name VARCHAR2) IS
340 SELECT u.creator_id
341 FROM ff_user_entities u,
342 ff_database_items d
343 WHERE d.user_name = p_user_name
344 AND u.user_entity_id = d.user_entity_id
345 AND (u.legislation_code = 'NO' )
346 AND (u.business_group_id IS NULL )
347 AND u.creator_type = 'B';
348
349 l_defined_balance_id ff_user_entities.user_entity_id%TYPE;
350
351 BEGIN
352 IF g_debug THEN
353 hr_utility.set_location(' Entering Function GET_DEFINED_BALANCE_ID',240);
354 END IF;
355
356 OPEN csr_def_bal_id(p_user_name);
357 FETCH csr_def_bal_id INTO l_defined_balance_id;
358 CLOSE csr_def_bal_id;
359
360 RETURN l_defined_balance_id;
361
362 IF g_debug THEN
363 hr_utility.set_location(' Leaving Function GET_DEFINED_BALANCE_ID',250);
364 END IF;
365 END GET_DEFINED_BALANCE_ID;
366
367
368 --
369 -- -----------------------------------------------------------------------------
370 -- Create assignment actions for all assignments to be archived.
371 -- -----------------------------------------------------------------------------
372 --
373 PROCEDURE assignment_action_code
374 (p_payroll_action_id IN NUMBER
375 ,p_start_person IN NUMBER
376 ,p_end_person IN NUMBER
377 ,p_chunk IN NUMBER) IS
378 --
379 CURSOR csr_element_type
380 (p_element_name VARCHAR2,
381 p_report_end_date DATE) IS
382 SELECT element_type_id
383 FROM pay_element_types_f
384 WHERE element_name=p_element_name
385 AND legislation_code='SE'
386 AND business_group_id IS NULL
387 AND p_report_end_date BETWEEN effective_start_date
388 AND effective_end_date;
389
390 CURSOR csr_input_values
391 (p_element_type_id NUMBER,
392 p_report_end_date DATE,
393 p_input_value VARCHAR2) IS
394 SELECT input_value_id
395 FROm pay_input_values_f
396 WHERE element_type_id = p_element_type_id
397 AND p_report_end_date between effective_start_date
398 AND effective_end_date
399 AND name = p_input_value
400 AND legislation_code='SE'
401 AND business_group_id IS NULL;
402
403
404 CURSOR csr_all_local_unit_details
405 (p_legal_employer_id NUMBER) IS
406 SELECT hoi_le.org_information1 local_unit_id,
407 hou_lu.NAME local_unit_name,
408 hoi_lu.org_information1,
409 hou_lu.location_id
410 FROM hr_all_organization_units hou_le,
411 hr_organization_information hoi_le,
412 hr_all_organization_units hou_lu,
413 hr_organization_information hoi_lu
414 WHERE hoi_le.organization_id = hou_le.organization_id
415 AND hou_le.organization_id = p_legal_employer_id
416 AND hoi_le.org_information_context = 'SE_LOCAL_UNITS'
417 AND hou_lu.organization_id = hoi_le.org_information1
418 AND hou_lu.organization_id = hoi_lu.organization_id
419 AND hoi_lu.org_information_context = 'SE_LOCAL_UNIT_DETAILS';
420
421
422 CURSOR csr_assignments
423 (p_local_unit_id NUMBER
424 ,p_start_person NUMBER
425 ,p_end_person NUMBER
426 ,p_report_start_date DATE
427 ,p_report_end_date DATE) IS
428 SELECT pap.person_id,
429 pap.full_name,
430 '19' || substr(pap.national_identifier,0,6) || substr(pap.national_identifier,8) national_identifier,
431 paa.assignment_number employee_number,
432 paa.assignment_id
433 FROM per_all_assignments_f paa,
434 per_all_people_f pap,
435 HR_SOFT_CODING_KEYFLEX hsc,
436 per_person_types ppt
437 WHERE pap.person_id BETWEEN p_start_person AND p_end_person
438 AND pap.effective_start_date <= p_report_end_date
439 AND pap.effective_end_date >= p_report_start_date
440 AND ppt.system_person_type like 'EMP%'
441 AND ppt.person_type_id= pap.person_type_id
442 AND pap.person_id = paa.person_id
443 AND paa.effective_start_date <= p_report_end_date
444 AND paa.effective_end_date >= p_report_start_date
445 AND hsc.soft_coding_keyflex_id = paa.soft_coding_keyflex_id
446 AND hsc.segment2 = to_char(p_local_unit_id)
447 ORDER by assignment_id;
448
449
450 /* CURSOR csr_sickness_start_end_date
451 (p_element_type_id NUMBER,
452 p_assignment_id NUMBER,
453 p_report_start_date DATE,
454 p_report_end_date DATE,
455 p_start_date_iv NUMBER,
456 p_end_date_iv NUMBER) IS
457 SELECT pee.element_entry_id,
458 NVL(fnd_date.canonical_to_date(peev1.screen_entry_value), pee.effective_start_date) start_date,
459 NVL(fnd_date.canonical_to_date(peev2.screen_entry_value), pee.effective_end_date) end_date
460 FROM pay_element_entries_f pee ,
461 pay_element_entry_values_f peev1,
462 pay_element_entry_values_f peev2
463 WHERE
464 element_type_id= p_element_type_id
465 AND assignment_id= p_assignment_id
466 AND p_report_start_date < nvl(pee.effective_end_date, p_report_start_date)
467 AND p_report_end_date > pee.effective_start_date
468 AND pee.element_entry_id = peev1.element_entry_id
469 AND p_report_start_date < nvl(peev1.effective_end_date, p_report_start_date)
470 AND p_report_end_date > peev1.effective_start_date
471 AND peev1.input_value_id= p_start_date_iv
472 AND pee.element_entry_id = peev2.element_entry_id
473 AND p_report_start_date < nvl(peev2.effective_end_date, p_report_start_date)
474 AND p_report_end_date > peev2.effective_start_date
475 AND peev2.input_value_id=p_end_date_iv; */
476
477 CURSOR csr_sickness_start_end_date
478 (p_element_type_id NUMBER,
479 p_assignment_id NUMBER,
480 p_report_start_date DATE,
481 p_report_end_date DATE,
482 p_start_date_iv NUMBER,
483 p_full_day_iv NUMBER,
484 p_end_date_iv NUMBER,
485 p_part_day_iv NUMBER) IS
486 SELECT prr.source_id,
487 fnd_date.canonical_to_date(prrv1.result_value) start_date,
488 prrv2.result_value full_day,
489 fnd_date.canonical_to_date(prrv3.result_value) end_date,
490 prrv4.result_value part_day
491 FROM pay_assignment_actions paa,
492 pay_payroll_actions ppa,
493 pay_run_results prr,
494 pay_run_result_values prrv1,
495 pay_run_result_values prrv2,
496 pay_run_result_values prrv3,
497 pay_run_result_values prrv4
498 WHERE ppa.effective_date BETWEEN p_report_start_date
499 AND p_report_end_date
500 AND ppa.payroll_action_id = paa.payroll_action_id
501 AND paa.assignment_id = p_assignment_id
502 AND paa.assignment_action_id = prr.assignment_action_id
503 AND prr.element_type_id = p_element_type_id
504 AND prr.run_result_id = prrv1.run_result_id
505 AND prrv1.input_value_id = p_start_date_iv
506 AND prr.run_result_id = prrv2.run_result_id
507 AND prrv2.input_value_id = p_full_day_iv
508 AND prr.run_result_id = prrv3.run_result_id
509 AND prrv3.input_value_id = p_end_date_iv
510 AND prr.run_result_id = prrv4.run_result_id
511 AND prrv4.input_value_id = p_part_day_iv
512 ORDER BY prr.run_result_id;
513 /* Order by in above cursor is needed to query results in order of their creation.
514 This is important in case same absence streches across periods*/
515
516 CURSOR csr_total_sickness_days
517 (p_element_type_id NUMBER,
518 p_assignment_id NUMBER,
519 p_report_start_date DATE,
520 p_report_end_date DATE,
521 p_full_day_iv NUMBER,
522 p_source_id NUMBER) IS
523 SELECT max(to_number(prrv1.result_value)) full_day
524 FROM pay_assignment_actions paa,
525 pay_payroll_actions ppa,
526 pay_run_results prr,
527 pay_run_result_values prrv1
528 WHERE ppa.effective_date BETWEEN p_report_start_date
529 AND p_report_end_date
530 AND ppa.payroll_action_id = paa.payroll_action_id
531 AND paa.assignment_id = p_assignment_id
532 AND paa.assignment_action_id = prr.assignment_action_id
533 AND prr.element_type_id = p_element_type_id
534 AND prr.run_result_id = prrv1.run_result_id
535 AND prrv1.input_value_id = p_full_day_iv
536 AND prr.source_id = p_source_id;
537
538
539 CURSOR csr_group_start_end_date
540 (p_element_type_id NUMBER,
541 p_assignment_id NUMBER,
542 p_report_start_date DATE,
543 p_report_end_date DATE,
544 p_start_date_iv NUMBER,
545 p_14th_date_iv NUMBER,
546 p_end_date_iv NUMBER,
547 p_emp_days_iv NUMBER) IS
548 SELECT fnd_date.canonical_to_date(prrv1.result_value) start_date,
549 fnd_date.canonical_to_date(prrv2.result_value) fourteenth_date,
550 fnd_date.canonical_to_date(prrv3.result_value) end_date,
551 prrv4.result_value employer_days
552 FROM pay_assignment_actions paa,
553 pay_payroll_actions ppa,
554 pay_run_results prr,
555 pay_run_result_values prrv1,
556 pay_run_result_values prrv2,
557 pay_run_result_values prrv3,
558 pay_run_result_values prrv4
559 WHERE ppa.effective_date BETWEEN p_report_start_date
560 AND p_report_end_date
561 AND ppa.payroll_action_id = paa.payroll_action_id
562 AND paa.assignment_id = p_assignment_id
563 AND paa.assignment_action_id = prr.assignment_action_id
564 AND prr.element_type_id = p_element_type_id
565 AND prr.run_result_id = prrv1.run_result_id
566 AND prrv1.input_value_id = p_start_date_iv
567 AND prr.run_result_id = prrv2.run_result_id
568 AND prrv2.input_value_id = p_14th_date_iv
569 AND prr.run_result_id = prrv3.run_result_id
570 AND prrv3.input_value_id = p_end_date_iv
571 AND prr.run_result_id = prrv4.run_result_id
572 AND prrv4.input_value_id = p_emp_days_iv;
573
574 CURSOR csr_correction_data
575 (p_assignment_id NUMBER) IS
576 SELECT assignment_extra_info_id,
577 fnd_date.canonical_to_date(AEI_INFORMATION1) start_date,
578 fnd_date.canonical_to_date(AEI_INFORMATION2) end_date,
579 AEI_INFORMATION3 full_day,
580 AEI_INFORMATION4 part_day
581 FROM PER_ASSIGNMENT_EXTRA_INFO
582 WHERE assignment_id=p_assignment_id
583 AND aei_information_category ='SE_SICKNESS_CORRECTION_DATA'
584 AND aei_information5='Y';
585
586 ----
587 l_element_type_rec csr_element_type%ROWTYPE;
588 l_input_values_start_rec csr_input_values%ROWTYPE;
589 l_input_values_14th_rec csr_input_values%ROWTYPE;
590 l_input_values_end_rec csr_input_values%ROWTYPE;
591 l_input_values_emplr_days_rec csr_input_values%ROWTYPE;
592 l_sickness_element_type_rec csr_element_type%ROWTYPE;
593 l_sickness_start_date_rec csr_input_values%ROWTYPE;
594 l_sickness_full_day_rec csr_input_values%ROWTYPE;
595 l_sickness_part_day_rec csr_input_values%ROWTYPE;
596 l_sickness_end_date_rec csr_input_values%ROWTYPE;
597 l_all_local_unit_details_rec csr_all_local_unit_details%ROWTYPE;
598 l_assignments_rec csr_assignments%ROWTYPE;
599 l_total_sickness_days_rec csr_total_sickness_days%ROWTYPE;
600 ----
601 l_assignment_id PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_ID%TYPE;
602 l_assact_id NUMBER;
603 l_ovn NUMBER;
604 l_action_info_id NUMBER;
605 l_exit_flag VARCHAR2(10);
606 l_sickness_end_date DATE;
607 l_sickness_start_date DATE;
608 l_full_day NUMBER(5);
609 l_part_day NUMBER(5);
610 l_employer_days NUMBER(5);
611 l_total_full_day NUMBER(5);
612 l_check_end_date VARCHAR2(20);
613 l_check_days NUMBER(5);
614
615
616 BEGIN
617
618 l_assignment_id:=0;
619 l_exit_flag := 'N';
620 l_total_full_day :=0;
621 l_full_day := 0;
622
623 g_payroll_action_id :=p_payroll_action_id;
624 g_business_group_id := null;
625 g_legal_employer_id := null;
626 g_start_date := null;
627 g_end_date := null;
628 g_version := null;
629 g_archive := null;
630
631
632 GET_ALL_PARAMETERS
633 (p_payroll_action_id
634 ,g_business_group_id
635 ,g_effective_date
636 ,g_legal_employer_id
637 ,g_start_date
638 ,g_end_date
639 ,g_archive
640 );
641
642
643 IF g_archive = 'Y'
644 THEN
645
646 /* Get Input value ids for input values for Sick Pay 1 to 14 days element*/
647
648 OPEN csr_element_type ('Sick Pay 1 to 14 days',g_end_date);
649 FETCH csr_element_type INTO l_element_type_rec;
650 CLOSE csr_element_type;
651
652 OPEN csr_input_values(l_element_type_rec.element_type_id, g_end_date, 'Start Date');
653 FETCH csr_input_values INTO l_input_values_start_rec;
654 CLOSE csr_input_values;
655
656 OPEN csr_input_values(l_element_type_rec.element_type_id, g_end_date, 'Fourteenth Date');
657 FETCH csr_input_values INTO l_input_values_14th_rec;
658 CLOSE csr_input_values;
659
660
661 OPEN csr_input_values(l_element_type_rec.element_type_id, g_end_date, 'End Date');
662 FETCH csr_input_values INTO l_input_values_end_rec;
663 CLOSE csr_input_values;
664
665
666 OPEN csr_input_values(l_element_type_rec.element_type_id, g_end_date, 'Full Days');
667 FETCH csr_input_values INTO l_input_values_emplr_days_rec;
668 CLOSE csr_input_values;
669
670 /* Get Input value ids for input values for Sickness Details element*/
671
672 OPEN csr_element_type ('Sickness Details',g_end_date);
673 FETCH csr_element_type INTO l_sickness_element_type_rec;
674 CLOSE csr_element_type;
675
676 OPEN csr_input_values(l_sickness_element_type_rec.element_type_id, g_end_date, 'Start Date');
677 FETCH csr_input_values INTO l_sickness_start_date_rec;
678 CLOSE csr_input_values;
679
680 OPEN csr_input_values(l_sickness_element_type_rec.element_type_id, g_end_date, 'Full Time Sickness Days');
681 FETCH csr_input_values INTO l_sickness_full_day_rec;
682 CLOSE csr_input_values;
683
684 OPEN csr_input_values(l_sickness_element_type_rec.element_type_id, g_end_date, 'Part Time Sickness Days');
685 FETCH csr_input_values INTO l_sickness_part_day_rec;
686 CLOSE csr_input_values;
687
688 OPEN csr_input_values(l_sickness_element_type_rec.element_type_id, g_end_date, 'End Date');
689 FETCH csr_input_values INTO l_sickness_end_date_rec;
690 CLOSE csr_input_values;
691
692 FOR l_all_local_unit_details_rec IN csr_all_local_unit_details (g_legal_employer_id) LOOP
693
694 FOR l_assignments_rec IN csr_assignments( l_all_local_unit_details_rec.local_unit_id
695 ,p_start_person
696 ,p_end_person
697 ,g_start_date
698 ,g_end_date) LOOP
699
700 IF l_assignment_id <> l_assignments_rec.assignment_id THEN
701 l_assignment_id := l_assignments_rec.assignment_id;
702
703
704 SELECT pay_assignment_actions_s.nextval INTO l_assact_id FROM dual;
705 hr_nonrun_asact.insact
706 (l_assact_id
707 ,l_assignments_rec.assignment_id
708 ,p_payroll_action_id
709 ,p_chunk
710 ,NULL);
711
712
713 l_employer_days:=0;
714
715 FOR l_sickness_group IN csr_group_start_end_date (l_element_type_rec.element_type_id
716 ,l_assignments_rec.assignment_id
717 ,g_start_date
718 ,g_end_date
719 ,l_input_values_start_rec.input_value_id
720 ,l_input_values_14th_rec.input_value_id
721 ,l_input_values_end_rec.input_value_id
722 ,l_input_values_emplr_days_rec.input_value_id) LOOP
723
724 l_exit_flag := 'N';
725 l_total_full_day :=0;
726 l_full_day := 0;
727 l_part_day := 0;
728 l_employer_days := l_sickness_group.employer_days;
729
730 FOR l_start_end_date IN csr_sickness_start_end_date (l_sickness_element_type_rec.element_type_id
731 ,l_assignments_rec.assignment_id
732 ,g_start_date
733 ,g_end_date
734 ,l_sickness_start_date_rec.input_value_id
735 ,l_sickness_full_day_rec.input_value_id
736 ,l_sickness_end_date_rec.input_value_id
737 ,l_sickness_part_day_rec.input_value_id) LOOP
738
739
740
741
742 IF l_start_end_date.start_date BETWEEN
743 l_sickness_group.start_date AND nvl(l_sickness_group.fourteenth_date,l_sickness_group.end_date ) THEN
744
745 l_sickness_end_date := least(l_sickness_group.fourteenth_date,l_start_end_date.end_date);
746 BEGIN
747 SELECT action_information8,
748 action_information10
749 INTO l_check_end_date,
750 l_check_days
751 FROM pay_action_information
752 WHERE action_context_id = l_assact_id
753 AND action_information13 = l_start_end_date.source_id;
754 /*
755 If more than one results exist for the same entry
756 */
757
758
759 IF l_check_end_date <> fnd_date.date_to_canonical(l_sickness_end_date) THEN
760 OPEN csr_total_sickness_days(l_sickness_element_type_rec.element_type_id
761 ,l_assignments_rec.assignment_id
762 ,g_start_date
763 ,g_end_date
764 ,l_sickness_full_day_rec.input_value_id
765 ,l_start_end_date.source_id);
766 FETCH csr_total_sickness_days INTO l_total_sickness_days_rec;
767 CLOSE csr_total_sickness_days;
768
769 UPDATE pay_action_information
770 set action_information8 = fnd_date.date_to_canonical(greatest(l_sickness_end_date,fnd_date.canonical_to_date(l_check_end_date))),
771 action_information10 = least(l_total_sickness_days_rec.full_day,l_employer_days)
772 WHERE action_context_id = l_assact_id
773 AND action_information13 = l_start_end_date.source_id;
774 l_total_full_day:= nvl(least(l_total_sickness_days_rec.full_day,l_employer_days),0) + nvl(l_total_full_day,0)
775 + nvl(l_start_end_date.part_day,0);
776 ELSE
777 l_total_full_day:= nvl(l_start_end_date.full_day,0) + nvl(l_total_full_day,0)
778 + nvl(l_start_end_date.part_day,0);
779 END IF;
780
781 EXCEPTION
782 WHEN NO_DATA_FOUND THEN
783
784 /*
785 1) In normal cases sickness group end date and fouteenth date will be same as sickness end date
786 2) When sickness crosses 14 days then fourteenth date will be less than or equal to group end date.
787 3) In case of change in pay period when sickness is across period border fourteenth date and
788 group end date both will have period end date.
789 */
790
791 /* IF l_sickness_group.fourteenth_date IS NOT NULL AND l_sickness_group.fourteenth_date
792 between l_start_end_date.start_date and ( l_start_end_date.end_date -1 )
793 and l_sickness_group.fourteenth_date <> l_sickness_group.end_date THEN
794 l_exit_flag := 'Y';
795 if nvl(l_start_end_date.full_day,0) > 0 then
796 l_full_day := l_employer_days - l_total_full_day;
797 elsif nvl(l_start_end_date.part_day,0) > 0 then
798 l_part_day := l_employer_days - l_total_full_day;
799 end if;
800
801 l_sickness_end_date :=l_sickness_group.fourteenth_date;
802 ELSIF l_sickness_group.fourteenth_date IS NOT NULL AND l_sickness_group.fourteenth_date
803 between l_start_end_date.start_date and ( l_start_end_date.end_date -1 )
804 and l_sickness_group.fourteenth_date = l_sickness_group.end_date THEN
805
806 l_full_day := l_start_end_date.full_day;
807 l_part_day := l_start_end_date.part_day;
808 l_sickness_end_date :=l_sickness_group.fourteenth_date;
809
810 ELSE
811
812 l_full_day := l_start_end_date.full_day;
813 l_part_day := l_start_end_date.part_day;
814 l_sickness_end_date :=l_start_end_date.end_date;
815 END IF;*/
816
817 l_sickness_end_date := LEAST(l_start_end_date.end_date, l_sickness_group.fourteenth_date );
818 IF l_sickness_group.fourteenth_date < l_start_end_date.end_date THEN
819
820 fnd_file.put_line(fnd_file.log,'location: 1 l_total_full_day: '||l_total_full_day);
821 fnd_file.put_line(fnd_file.log,'location: 1 l_start_end_date.end_date : '||l_start_end_date.end_date );
822
823 l_exit_flag := 'Y';
824 if nvl(l_start_end_date.full_day,0) > 0 then
825 l_full_day := l_employer_days - l_total_full_day;
826 elsif nvl(l_start_end_date.part_day,0) > 0 then
827 l_part_day := l_employer_days - l_total_full_day;
828 end if;
829 ELSE
830 if nvl(l_start_end_date.full_day,0) > 0 then
831 l_full_day := least (l_start_end_date.full_day,l_employer_days);
832 elsif nvl(l_start_end_date.part_day,0) > 0 then
833 l_part_day := least (l_start_end_date.part_day,l_employer_days);
834 end if;
835 END IF;
836
837
838 l_total_full_day:= nvl(l_start_end_date.full_day,0) + nvl(l_total_full_day,0)
839 + nvl(l_start_end_date.part_day,0);
840
841 fnd_file.put_line(fnd_file.log,'location: 2 l_total_full_day: '||l_total_full_day);
842 fnd_file.put_line(fnd_file.log,'location: 2 l_exit_flag: '||l_exit_flag);
843 fnd_file.put_line(fnd_file.log,'location: 2 l_start_end_date.full_day: '||l_start_end_date.full_day);
844 fnd_file.put_line(fnd_file.log,'location: 2 l_start_end_date.part_day: '||l_start_end_date.part_day);
845 fnd_file.put_line(fnd_file.log,'location: 2 l_start_end_date.end_date : '||l_start_end_date.end_date );
846 fnd_file.put_line(fnd_file.log,'location: 2 l_sickness_group.end_date : '||l_sickness_group.end_date );
847 fnd_file.put_line(fnd_file.log,'in assignment_action_code total full day ' || to_char(l_total_full_day));
848
849 IF l_start_end_date.start_date < g_start_date THEN
850 l_sickness_start_date := g_start_date;
851 ELSE
852 l_sickness_start_date := l_start_end_date.start_date;
853 END IF;
854
855 pay_action_information_api.create_action_information (
856 p_action_information_id=> l_action_info_id,
857 p_action_context_id=> l_assact_id,
858 p_action_context_type=> 'AAP',
859 p_object_version_number=> l_ovn,
860 p_effective_date=> g_effective_date,
861 p_assignment_id => l_assignments_rec.assignment_id,
862 p_action_information_category=> 'EMEA REPORT INFORMATION',
863 p_action_information1=> 'PYSESTOA',
864 p_action_information2=> 'ASG',
865 p_action_information3=> g_legal_employer_id,
866 p_action_information4=> l_assignments_rec.national_identifier,
867 p_action_information5=> l_assignments_rec.full_name,
868 p_action_information6=> l_assignments_rec.employee_number,
869 p_action_information7=> fnd_date.date_to_canonical(l_sickness_start_date ),
870 p_action_information8=> fnd_date.date_to_canonical(l_sickness_end_date),
871 p_action_information9=> to_char(l_assignments_rec.person_id),
872 p_action_information10=> l_full_day,
873 p_action_information11=> l_part_day, -- part day balance to be added
874 p_action_information12=> NULL, -- correction
875 p_action_information13=> l_start_end_date.source_id,
876 p_action_information14=> NULL,
877 p_action_information15=> NULL,
878 p_action_information16=> NULL,
879 p_action_information17=> NULL,
880 p_action_information18=> NULL,
881 p_action_information19=> NULL,
882 p_action_information20=> NULL
883 );
884 END;
885 END IF;
886
887 IF l_exit_flag = 'Y' THEN
888
889 l_total_full_day :=0;
890 l_exit_flag := 'N';
891 l_full_day := 0;
892 l_part_day := 0;
893
894 EXIT;
895 END IF;
896 END LOOP; -- sickness results
897
898
899 END LOOP; -- group results
900
901
902
903 FOR l_csr_correction_data IN csr_correction_data (l_assignments_rec.assignment_id) LOOP
904
905 IF l_csr_correction_data.start_date >= g_start_date and l_csr_correction_data.start_date <= g_end_date THEN
906 pay_action_information_api.create_action_information (
907 p_action_information_id=> l_action_info_id,
908 p_action_context_id=> l_assact_id,
909 p_action_context_type=> 'AAP',
910 p_object_version_number=> l_ovn,
911 p_effective_date=> g_effective_date,
912 p_assignment_id => l_assignments_rec.assignment_id,
913 p_action_information_category=> 'EMEA REPORT INFORMATION',
914 p_action_information1=> 'PYSESTOA',
915 p_action_information2=> 'ASG',
916 p_action_information3=> g_legal_employer_id,
917 p_action_information4=> l_assignments_rec.national_identifier,
918 p_action_information5=> l_assignments_rec.full_name,
919 p_action_information6=> l_assignments_rec.employee_number,
920 p_action_information7=> fnd_date.date_to_canonical(l_csr_correction_data.start_date),
921 p_action_information8=> fnd_date.date_to_canonical(l_csr_correction_data.end_date),
922 p_action_information9=> to_char(l_assignments_rec.person_id),
923 p_action_information10=> l_csr_correction_data.full_day,
924 p_action_information11=> l_csr_correction_data.part_day,
925 p_action_information12=> '1',
926 p_action_information13=> NULL,
927 p_action_information14=> NULL,
928 p_action_information15=> NULL,
929 p_action_information16=> NULL,
930 p_action_information17=> NULL,
931 p_action_information18=> NULL,
932 p_action_information19=> NULL,
933 p_action_information20=> NULL
934 );
935
936
937 update PER_ASSIGNMENT_EXTRA_INFO
938 set aei_information5='N'
939 where assignment_extra_info_id = l_csr_correction_data.assignment_extra_info_id;
940 END IF;
941
942 END LOOP; -- Correction Loop;
943
944 END IF; -- if assignment is same
945
946 END LOOP; -- Assignment
947
948 END LOOP; -- Local Unit
949
950 END IF; -- g_archive = 'Y'
951
952 END;
953
954
955 PROCEDURE initialization_code
956 (p_payroll_action_id IN NUMBER) IS
957 BEGIN
958 NULL;
959 END initialization_code;
960 --
961 --
962 -- -----------------------------------------------------------------------------
963 -- Create archive information for individual assignment actions.
964 -- -----------------------------------------------------------------------------
965 --
966 PROCEDURE archive_code
967 (p_assignment_action_id IN NUMBER
968 ,p_effective_date IN DATE) IS
969 --
970 --
971 BEGIN
972 --
973 --
974 NULL;
975 END archive_code;
976
977 --
978 --
979 -- -----------------------------------------------------------------------------
980 -- Assemble XML for reporting.
981 -- -----------------------------------------------------------------------------
982 --
983
984 PROCEDURE WritetoCLOB(p_xfdf_clob out nocopy CLOB) is
985 l_xfdf_string clob;
986 l_str1 varchar2(1000);
987 l_str2 varchar2(20);
988 l_str3 varchar2(20);
989 l_str4 varchar2(20);
990 l_str5 varchar2(20);
991 l_str6 varchar2(30);
992 l_str7 varchar2(1000);
993 l_str8 varchar2(240);
994 l_str9 varchar2(240);
995 l_str10 varchar2(20);
996 l_str11 varchar2(20);
997 l_IANA_charset VARCHAR2 (50);
998
999 current_index pls_integer;
1000
1001 BEGIN
1002
1003 hr_utility.set_location('Entering WritetoCLOB ',10);
1004
1005
1006 l_IANA_charset := HR_SE_UTILITY.get_IANA_charset ;
1007 l_str1 := '<?xml version="1.0" encoding="'||l_IANA_charset||'"?> <ROOT><PAACR>';
1008 l_str2 := '<';
1009 l_str3 := '>';
1010 l_str4 := '</';
1011 l_str5 := '>';
1012 l_str6 := '</PAACR></ROOT>';
1013 l_str7 := '<?xml version="1.0" encoding="'||l_IANA_charset||'"?> <ROOT></ROOT>';
1014 l_str10 := '<PAACR>';
1015 l_str11 := '</PAACR>';
1016
1017
1018 dbms_lob.createtemporary(l_xfdf_string,FALSE,DBMS_LOB.CALL);
1019 dbms_lob.open(l_xfdf_string,dbms_lob.lob_readwrite);
1020
1021 current_index := 0;
1022
1023 IF xml_tab.count > 0 THEN
1024
1025 dbms_lob.writeAppend( l_xfdf_string, length(l_str1), l_str1 );
1026
1027
1028 FOR table_counter IN xml_tab.FIRST .. xml_tab.LAST LOOP
1029
1030 l_str8 := xml_tab(table_counter).TagName;
1031 l_str9 := xml_tab(table_counter).TagValue;
1032
1033 IF l_str9 IN ('ORG_DETAILS','EMP_DETAILS','END_ORG_DETAILS','END_EMP_DETAILS'
1034 ) THEN
1035
1036 IF l_str9 IN ('ORG_DETAILS','EMP_DETAILS') THEN
1037 dbms_lob.writeAppend(l_xfdf_string, length(l_str2), l_str2);
1038 dbms_lob.writeAppend(l_xfdf_string, length(l_str8), l_str8);
1039 dbms_lob.writeAppend(l_xfdf_string, length(l_str3), l_str3);
1040 ELSE
1041 dbms_lob.writeAppend(l_xfdf_string, length(l_str4), l_str4);
1042 dbms_lob.writeAppend(l_xfdf_string, length(l_str8), l_str8);
1043 dbms_lob.writeAppend(l_xfdf_string, length(l_str5), l_str5);
1044 END IF;
1045
1046 ELSE
1047
1048 if l_str9 is not null then
1049
1050 dbms_lob.writeAppend(l_xfdf_string, length(l_str2), l_str2);
1051 dbms_lob.writeAppend(l_xfdf_string, length(l_str8), l_str8);
1052 dbms_lob.writeAppend(l_xfdf_string, length(l_str3), l_str3);
1053 dbms_lob.writeAppend(l_xfdf_string, length(l_str9), l_str9);
1054 dbms_lob.writeAppend(l_xfdf_string, length(l_str4), l_str4);
1055 dbms_lob.writeAppend(l_xfdf_string, length(l_str8), l_str8);
1056 dbms_lob.writeAppend(l_xfdf_string, length(l_str5), l_str5);
1057 else
1058
1059 dbms_lob.writeAppend(l_xfdf_string, length(l_str2), l_str2);
1060 dbms_lob.writeAppend(l_xfdf_string, length(l_str8), l_str8);
1061 dbms_lob.writeAppend(l_xfdf_string, length(l_str3), l_str3);
1062 dbms_lob.writeAppend(l_xfdf_string, length(l_str4), l_str4);
1063 dbms_lob.writeAppend(l_xfdf_string, length(l_str8), l_str8);
1064 dbms_lob.writeAppend(l_xfdf_string, length(l_str5), l_str5);
1065
1066 end if;
1067
1068 END IF;
1069
1070 END LOOP;
1071
1072 dbms_lob.writeAppend(l_xfdf_string, length(l_str6), l_str6 );
1073
1074 ELSE
1075 dbms_lob.writeAppend(l_xfdf_string, length(l_str7), l_str7 );
1076 END IF;
1077
1078 p_xfdf_clob := l_xfdf_string;
1079
1080 hr_utility.set_location('Leaving WritetoCLOB ',20);
1081
1082 EXCEPTION
1083 WHEN OTHERS then
1084 HR_UTILITY.TRACE('sqlerrm ' || SQLERRM);
1085 HR_UTILITY.RAISE_ERROR;
1086 END WritetoCLOB;
1087
1088
1089 --
1090 --
1091 -----------------------------------------------------------------------------------
1092 -- POPULATE_DATA_DETAIL generates xml for the reports.
1093 -----------------------------------------------------------------------------------
1094 --
1095 --
1096 PROCEDURE POPULATE_DATA_DETAIL
1097 (p_business_group_id IN NUMBER,
1098 p_payroll_action_id IN VARCHAR2 ,
1099 p_template_name IN VARCHAR2,
1100 p_xml OUT NOCOPY CLOB)
1101 IS
1102
1103
1104 /* Cursor to fetch Header Information */
1105
1106
1107 CURSOR csr_legal_employer_details (p_payroll_action_id NUMBER)
1108 IS
1109 SELECT
1110 action_information3 legal_employer_id,
1111 action_information4 name,
1112 action_information5 org_num,
1113 action_information6 address,
1114 action_information7 postal_code,
1115 action_information8 country,
1116 action_information9 contact_person,
1117 effective_date
1118 FROM pay_action_information pai
1119 WHERE pai.action_context_id = p_payroll_action_id
1120 AND pai.action_context_type='PA'
1121 AND pai.action_information_category='EMEA REPORT INFORMATION'
1122 AND pai.action_information1='PYSESTOA'
1123 AND pai.action_information2='LE';
1124
1125 CURSOR csr_employee_details (p_payroll_action_id NUMBER,
1126 p_legal_employer_id NUMBER)
1127 IS
1128 SELECT
1129 pai.action_information4 national_identifier,
1130 pai.action_information5 full_name,
1131 fnd_date.canonical_to_date(pai.action_information7) start_date,
1132 fnd_date.canonical_to_date(pai.action_information8) end_date,
1133 pai.action_information10 full_day,
1134 pai.action_information11 part_day,
1135 pai.action_information12 correction
1136 FROM
1137 pay_payroll_actions paa,
1138 pay_assignment_actions assg,
1139 pay_action_information pai
1140 WHERE
1141 paa.payroll_action_id = p_payroll_action_id
1142 AND assg.payroll_action_id = paa.payroll_action_id
1143 AND pai.action_context_id= assg.assignment_action_id
1144 AND pai.action_context_type='AAP'
1145 AND pai.action_information_category='EMEA REPORT INFORMATION'
1146 AND pai.action_information1='PYSESTOA'
1147 AND pai.action_information2='ASG'
1148 AND pai.action_information3=p_legal_employer_id;
1149
1150 l_legal_employer_details_rec csr_legal_employer_details%rowtype;
1151 l_employee_details_rec csr_employee_details%rowtype;
1152
1153
1154 l_counter NUMBER;
1155 l_total NUMBER;
1156 l_total_eft NUMBER;
1157 l_count NUMBER;
1158 l_payroll_action_id NUMBER;
1159 l_lu_counter_reset VARCHAR2(10);
1160 l_prev_local_unit VARCHAR2(15);
1161
1162
1163 BEGIN
1164
1165 l_counter:=0;
1166
1167
1168
1169 IF p_payroll_action_id IS NULL THEN
1170
1171 BEGIN
1172
1173 SELECT payroll_action_id
1174 INTO l_payroll_action_id
1175 FROM pay_payroll_actions ppa,
1176 fnd_conc_req_summary_v fcrs,
1177 fnd_conc_req_summary_v fcrs1
1178 WHERE fcrs.request_id = fnd_global.conc_request_id
1179 AND fcrs.priority_request_id = fcrs1.priority_request_id
1180 AND ppa.request_id between fcrs1.request_id and fcrs.request_id
1181 AND ppa.request_id = fcrs1.request_id;
1182
1183 EXCEPTION
1184 WHEN OTHERS THEN
1185 NULL;
1186 END ;
1187
1188 ELSE
1189
1190 l_payroll_action_id := p_payroll_action_id;
1191
1192 END IF;
1193
1194
1195 g_payroll_action_id :=p_payroll_action_id;
1196 g_business_group_id := null;
1197 g_legal_employer_id := null;
1198 g_start_date := null;
1199 g_end_date := null;
1200 g_version := null;
1201 g_archive := null;
1202
1203
1204 GET_ALL_PARAMETERS
1205 (l_payroll_action_id
1206 ,g_business_group_id
1207 ,g_effective_date
1208 ,g_legal_employer_id
1209 ,g_start_date
1210 ,g_end_date
1211 ,g_archive
1212 );
1213
1214 hr_utility.set_location('Entered Procedure GETDATA',10);
1215
1216
1217 /* Get the File Header Information */
1218 OPEN csr_legal_employer_details(l_payroll_action_id);
1219 FETCH csr_legal_employer_details INTO l_legal_employer_details_rec;
1220 CLOSE csr_legal_employer_details;
1221
1222 hr_utility.set_location('Before populating pl/sql table',20);
1223
1224 xml_tab(l_counter).TagName :='ORG_DETAILS';
1225 xml_tab(l_counter).TagValue :='ORG_DETAILS';
1226 l_counter:=l_counter+1;
1227
1228 xml_tab(l_counter).TagName :='ORG_NAME';
1229 xml_tab(l_counter).TagValue := l_legal_employer_details_rec.NAME;
1230 l_counter:=l_counter+1;
1231
1232 xml_tab(l_counter).TagName :='ORG_NUM';
1233 xml_tab(l_counter).TagValue := l_legal_employer_details_rec.org_num;
1234 l_counter:=l_counter+1;
1235
1236 xml_tab(l_counter).TagName :='ADDRESS';
1237 xml_tab(l_counter).TagValue := l_legal_employer_details_rec.address;
1238 l_counter:=l_counter+1;
1239
1240 xml_tab(l_counter).TagName :='START_DATE';
1241 xml_tab(l_counter).TagValue := to_char(g_start_date,'YYYYMMDD');
1242 l_counter:=l_counter+1;
1243
1244 xml_tab(l_counter).TagName :='END_DATE';
1245 xml_tab(l_counter).TagValue := to_char(g_end_date,'YYYYMMDD');
1246 l_counter:=l_counter+1;
1247
1248 xml_tab(l_counter).TagName :='POSTAL_CODE';
1249 xml_tab(l_counter).TagValue := l_legal_employer_details_rec.postal_code;
1250 l_counter:=l_counter+1;
1251
1252 xml_tab(l_counter).TagName :='COUNTRY';
1253 xml_tab(l_counter).TagValue := l_legal_employer_details_rec.country;
1254 l_counter:=l_counter+1;
1255
1256 xml_tab(l_counter).TagName :='CONTACT_PERSON';
1257 xml_tab(l_counter).TagValue := l_legal_employer_details_rec.CONTACT_PERSON;
1258 l_counter:=l_counter+1;
1259
1260 xml_tab(l_counter).TagName :='REPORT_DATE';
1261 xml_tab(l_counter).TagValue := to_char(l_legal_employer_details_rec.effective_date,'YYYYMMDD');
1262 l_counter:=l_counter+1;
1263
1264 -- Employee Data
1265
1266 FOR l_employee_details_rec IN csr_employee_details(l_payroll_action_id, to_number(l_legal_employer_details_rec.legal_employer_id))
1267 LOOP
1268
1269
1270 /* Begins Employee record*/
1271
1272
1273 xml_tab(l_counter).TagName :='EMP_DETAILS';
1274 xml_tab(l_counter).TagValue :='EMP_DETAILS';
1275 l_counter:=l_counter+1;
1276
1277 xml_tab(l_counter).TagName :='ORG_NUM';
1278 xml_tab(l_counter).TagValue := l_legal_employer_details_rec.org_num;
1279 l_counter:=l_counter+1;
1280
1281 xml_tab(l_counter).TagName :='NATIONAL_IDENTIFIER';
1282 xml_tab(l_counter).TagValue := l_employee_details_rec.national_identifier;
1283 l_counter:=l_counter+1;
1284
1285 xml_tab(l_counter).TagName :='FULL_NAME';
1286 xml_tab(l_counter).TagValue := l_employee_details_rec.full_name;
1287 l_counter:=l_counter+1;
1288
1289 xml_tab(l_counter).TagName :='START_DATE';
1290 xml_tab(l_counter).TagValue := to_char(l_employee_details_rec.start_date,'YYYYMMDD');
1291 l_counter:=l_counter+1;
1292
1293
1294 xml_tab(l_counter).TagName :='END_DATE';
1295 xml_tab(l_counter).TagValue := to_char(l_employee_details_rec.end_date,'YYYYMMDD');
1296 l_counter:=l_counter+1;
1297
1298 xml_tab(l_counter).TagName :='FULL_DAY';
1299 xml_tab(l_counter).TagValue := l_employee_details_rec.full_day;
1300 l_counter:=l_counter+1;
1301
1302 xml_tab(l_counter).TagName :='PART_DAY';
1303 xml_tab(l_counter).TagValue := l_employee_details_rec.part_day;
1304 l_counter:=l_counter+1;
1305
1306 xml_tab(l_counter).TagName :='CORRECTION';
1307 xml_tab(l_counter).TagValue := l_employee_details_rec.correction;
1308 l_counter:=l_counter+1;
1309
1310 xml_tab(l_counter).TagName :='EMP_DETAILS';
1311 xml_tab(l_counter).TagValue :='END_EMP_DETAILS';
1312 l_counter:=l_counter+1;
1313
1314 END LOOP; -- employee
1315
1316 xml_tab(l_counter).TagName :='ORG_DETAILS';
1317 xml_tab(l_counter).TagValue :='END_ORG_DETAILS';
1318 l_counter := l_counter + 1;
1319
1320
1321 hr_utility.set_location('After populating pl/sql table',30);
1322 hr_utility.set_location('Entered Procedure GETDATA',10);
1323
1324
1325 WritetoCLOB (p_xml );
1326
1327 END POPULATE_DATA_DETAIL;
1328
1329 END pay_se_stat_office_archive;