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