[Home] [Help]
PACKAGE BODY: APPS.PAY_NO_SAL_STATISTICS_ARCHIVE
Source
1 PACKAGE BODY pay_no_sal_statistics_archive AS
2 /* $Header: pynossta.pkb 120.0.12020000.3 2013/01/18 09:20:48 smeduri ship $ */
3 --
4 --
5 -- -----------------------------------------------------------------------------
6 -- Data types.
7 -- -----------------------------------------------------------------------------
8 --
9 TYPE t_rep_code_rec IS RECORD
10 (record_type VARCHAR2(10)
11 ,reporting_code VARCHAR2(10)
12 ,amount NUMBER
13 ,info1 VARCHAR2(30)
14 ,info2 VARCHAR2(30)
15 ,info3 VARCHAR2(30)
16 ,info4 VARCHAR2(30)
17 ,info5 VARCHAR2(30)
18 ,info6 VARCHAR2(30));
19 --
20 TYPE t_xml_element_rec IS RECORD
21 (tagname VARCHAR2(240)
22 ,tagvalue VARCHAR2(240));
23 --
24 TYPE t_xml_element_table IS TABLE OF t_xml_element_rec INDEX BY BINARY_INTEGER;
25 --
26 --
27 -- -----------------------------------------------------------------------------
28 -- Global variables.
29 -- -----------------------------------------------------------------------------
30 --
31 g_xml_element_table t_xml_element_table;
32 g_empty_rep_code_rec t_rep_code_rec;
33 g_business_group_id NUMBER;
34 g_legal_employer_id NUMBER;
35 g_report_date DATE;
36 g_effective_date DATE;
37 g_archive VARCHAR2(30);
38 g_debug boolean := hr_utility.debug_enabled;
39 g_payroll_action_id NUMBER;
40 g_package VARCHAR2(30) := 'pay_no_sal_statistics_archive.';
41 g_version VARCHAR2(10);
42 g_sp_org_id VARCHAR2(39);
43 --
44 --
45 -- -----------------------------------------------------------------------------
46 -- Parse out parameters from string.
47 -- -----------------------------------------------------------------------------
48 --
49 FUNCTION get_parameter
50 (p_parameter_string IN VARCHAR2
51 ,p_token IN VARCHAR) RETURN VARCHAR2 IS
52 --
53 l_parameter pay_payroll_actions.legislative_parameters%TYPE := NULL;
54 l_start_pos NUMBER;
55 l_delimiter VARCHAR2(1) := ' ';
56 --
57 BEGIN
58 --
59 l_start_pos := INSTR(' ' || p_parameter_string, l_delimiter || p_token || '=');
60 --
61 IF l_start_pos = 0 THEN
62 l_delimiter := '|';
63 l_start_pos := INSTR(' ' || p_parameter_string, l_delimiter || p_token || '=');
64 END IF;
65 --
66 IF l_start_pos <> 0 THEN
67 l_start_pos := l_start_pos + LENGTH(p_token || '=');
68 l_parameter := SUBSTR(p_parameter_string, l_start_pos, INSTR(p_parameter_string || ' ', l_delimiter, l_start_pos) - l_start_pos);
69 END IF;
70 --
71 RETURN l_parameter;
72 --
73 END;
74 --
75 --
76 -- Get All Parameters
77 --
78 --
79 PROCEDURE GET_ALL_PARAMETERS(
80 p_payroll_action_id IN NUMBER -- In parameter
81 ,p_business_group_id OUT NOCOPY NUMBER -- Core parameter
82 ,p_effective_date OUT NOCOPY Date -- Core parameter
83 ,p_name_sp OUT NOCOPY NUMBER -- Statement Provider Name
84 ,p_legal_employer_id OUT NOCOPY NUMBER -- User parameter
85 ,p_version OUT NOCOPY VARCHAR2
86 ,p_archive OUT NOCOPY VARCHAR2 -- User parameter
87 )
88 IS
89 CURSOR csr_parameter_info(p_payroll_action_id NUMBER) IS
90 SELECT
91 get_parameter(legislative_parameters,'NAME_SP') name_sp,
92 TO_NUMBER ( GET_PARAMETER(legislative_parameters,'LE_ID') ) Legal
93 ,get_parameter(legislative_parameters,'VERSION') VERSION
94 ,GET_PARAMETER(legislative_parameters,'ARCHIVE') ARCHIVE_OR_NOT
95 ,fnd_date.canonical_to_date(GET_PARAMETER(legislative_parameters,'DATE'))
96 ,business_group_id BG_ID FROM pay_payroll_actions
97 WHERE payroll_action_id = p_payroll_action_id;
98
99 lr_parameter_info csr_parameter_info%ROWTYPE;
100
101 l_proc VARCHAR2(240):= g_package||' GET_ALL_PARAMETERS ';
102
103 BEGIN
104 OPEN csr_parameter_info (p_payroll_action_id);
105
106 FETCH csr_parameter_info
107 INTO p_name_sp
108 ,p_legal_employer_id
109 ,p_version
110 ,p_archive
111 ,p_effective_date
112 ,p_business_group_id;
113 CLOSE csr_parameter_info;
114
115 --fnd_file.put_line(fnd_file.log,'After csr_parameter_info in ' );
116 --fnd_file.put_line(fnd_file.log,'After p_legal_employer_id in ' || p_legal_employer_id);
117 --fnd_file.put_line(fnd_file.log,'After p_local_unit_id in ' || p_local_unit_id );
118 --fnd_file.put_line(fnd_file.log,'After p_archive' || p_archive );
119
120 IF g_debug THEN
121 hr_utility.set_location(' Leaving Procedure GET_ALL_PARAMETERS',30);
122 END IF;
123 END GET_ALL_PARAMETERS;
124
125 --
126 --
127 -- -----------------------------------------------------------------------------
128 -- Sets all legislative parameters as global variables for future use.
129 -- -----------------------------------------------------------------------------
130 --
131 PROCEDURE set_parameters
132 (p_payroll_action_id IN NUMBER) IS
133 --
134 CURSOR csr_parameters
135 (p_payroll_action_id IN NUMBER) IS
136 SELECT business_group_id
137 ,legislative_parameters
138 ,get_parameter(legislative_parameters, 'LEGAL_EMPLOYER_ID') legal_employer_id
139 ,fnd_date.canonical_to_date(get_parameter(legislative_parameters, 'DATE')) report_date
140 FROM pay_payroll_actions
141 WHERE payroll_action_id = p_payroll_action_id;
142 --
143 l_parameter_rec csr_parameters%ROWTYPE;
144 --
145 BEGIN
146 --
147 OPEN csr_parameters(p_payroll_action_id);
148 FETCH csr_parameters INTO l_parameter_rec;
149 CLOSE csr_parameters;
150 --
151 g_business_group_id := l_parameter_rec.business_group_id;
152 g_legal_employer_id := l_parameter_rec.legal_employer_id;
153 g_report_date := l_parameter_rec.report_date;
154 --
155 END set_parameters;
156 --
157 --
158 -- -----------------------------------------------------------------------------
159 --
160 -- -----------------------------------------------------------------------------
161 --
162 PROCEDURE range_code
163 (p_payroll_action_id IN NUMBER
164 ,p_sql OUT NOCOPY VARCHAR2) IS
165 --
166 CURSOR csr_legal_employers
167 (p_legal_employer_id IN NUMBER) IS
168 SELECT org.organization_id legal_employer_id
169 ,org.name
170 ,org.location_id
171 ,hoi1.org_information1
172 FROM hr_all_organization_units org
173 ,hr_organization_information hoi1
174 WHERE org.organization_id = p_legal_employer_id
175 AND hoi1.organization_id (+) = org.organization_id
176 AND hoi1.org_information_context (+) = 'NO_LEGAL_EMPLOYER_DETAILS';
177
178 -- Cursor to pick up Local Unit Details
179
180 CURSOR csr_all_local_unit_details (
181 csr_v_legal_employer_id hr_organization_information.organization_id%TYPE )
182 IS
183 SELECT hoi_le.org_information1 local_unit_id,
184 hou_lu.NAME local_unit_name,
185 hoi_lu.org_information1,
186 hoi_lu.org_information3,
187 hou_lu.location_id
188 FROM hr_all_organization_units hou_le,
189 hr_organization_information hoi_le,
190 hr_all_organization_units hou_lu,
191 hr_organization_information hoi_lu
192 WHERE hoi_le.organization_id = hou_le.organization_id
193 AND hou_le.organization_id = csr_v_legal_employer_id
194 AND hoi_le.org_information_context = 'NO_LOCAL_UNITS'
195 AND hou_lu.organization_id = hoi_le.org_information1
196 AND hou_lu.organization_id = hoi_lu.organization_id
197 AND hoi_lu.org_information_context = 'NO_LOCAL_UNIT_DETAILS';
198
199 CURSOR csr_org_details (p_sp_org_id NUMBER) IS
200 SELECT org.organization_id
201 ,org.name
202 ,org.location_id
203 ,hoi1.org_information1 sp_org_number
204 FROM hr_all_organization_units org
205 ,hr_organization_information hoi1
206 WHERE org.organization_id = p_sp_org_id
207 AND hoi1.organization_id (+) = org.organization_id
208 AND hoi1.org_information_context (+) = 'NO_STATEMENT_PROVIDER_DETAILS';
209
210
211 CURSOR csr_location (p_location_id NUMBER) IS
212 SELECT rpad(Address_line_1 || ', ' || Address_line_2 ||', ' ||Address_line_3,30,' ') address, postal_code
213 FROM hr_locations
214 WHERE location_id = p_location_id;
215
216 --
217 L_ACTION_INFO_ID NUMBER;
218 l_ovn NUMBER;
219 l_location_rec csr_location%rowtype;
220 l_legal_employer_rec csr_legal_employers%ROWTYPE;
221 l_org_details_rec csr_org_details%ROWTYPE;
222 --
223 BEGIN
224 --
225 --
226 -- Setup legislative parameters as global values for future use.
227 --
228 set_parameters(p_payroll_action_id);
229 --
230 --
231 -- Archive report information.
232 --
233 --
234 IF g_debug THEN
235 hr_utility.set_location(' Entering Procedure RANGE_CODE',40);
236 END IF;
237
238 p_sql := 'SELECT DISTINCT person_id
239 FROM per_people_f ppf
240 ,pay_payroll_actions ppa
241 WHERE ppa.payroll_action_id = :payroll_action_id
242 AND ppa.business_group_id = ppf.business_group_id
243 ORDER BY ppf.person_id';
244
245 g_payroll_action_id :=p_payroll_action_id;
246 g_business_group_id := null;
247 g_legal_employer_id := null;
248 g_sp_org_id := null;
249 g_legal_employer_id := null;
250 g_version := null;
251 g_archive := null;
252
253 GET_ALL_PARAMETERS
254 (p_payroll_action_id
255 ,g_business_group_id
256 ,g_effective_date
257 ,g_sp_org_id
258 ,g_legal_employer_id
259 ,g_version
260 ,g_archive
261 );
262
263 /* pay_balance_pkg.set_context('TAX_UNIT_ID',g_legal_employer_id);
264 pay_balance_pkg.set_context('LOCAL_UNIT_ID',g_local_unit_id);
265 pay_balance_pkg.set_context('DATE_EARNED',fnd_date.date_to_canonical(g_ref_date));
266 pay_balance_pkg.set_context('SOURCE_ID',NULL);
267 pay_balance_pkg.set_context('TAX_GROUP',NULL);*/
268
269 IF g_archive = 'Y'
270 THEN
271
272 IF nvl(g_sp_org_id,-9999) <> -9999 THEN
273 OPEN csr_org_details(g_sp_org_id);
274 FETCH csr_org_details INTO l_org_details_rec;
275 CLOSE csr_org_details;
276 END IF;
277
278 OPEN csr_legal_employers(g_legal_employer_id);
279 FETCH csr_legal_employers INTO l_legal_employer_rec;
280 CLOSE csr_legal_employers;
281
282
283 pay_action_information_api.create_action_information (
284 p_action_information_id=> l_action_info_id,
285 p_action_context_id=> p_payroll_action_id,
286 p_action_context_type=> 'PA',
287 p_object_version_number=> l_ovn,
288 p_effective_date=> g_effective_date,
289 p_source_id=> NULL,
290 p_source_text=> NULL,
291 p_action_information_category=> 'EMEA REPORT DETAILS',
292 p_action_information1=> 'PYNOSSTA',
293 p_action_information2=> to_char(g_business_group_id),
294 p_action_information3=> to_char(g_legal_employer_id),
295 p_action_information4=> l_legal_employer_rec.name,
296 p_action_information5=> fnd_date.date_to_canonical(g_effective_date),
297 p_action_information6=> g_version,
298 p_action_information7=> g_sp_org_id,
299 p_action_information8=> l_org_details_rec.name,
300 p_action_information9=> NULL,
301 p_action_information10=> NULL,
302 p_action_information11=> NULL,
303 p_action_information12=> NULL,
304 p_action_information13=> NULL,
305 p_action_information14=> NULL,
306 p_action_information15=> NULL,
307 p_action_information16=> NULL,
308 p_action_information17=> NULL,
309 p_action_information18=> NULL,
310 p_action_information19=> NULL,
311 p_action_information20=> NULL,
312 p_action_information21=> NULL,
313 p_action_information22=> NULL,
314 p_action_information23=> NULL,
315 p_action_information24=> NULL,
316 p_action_information25=> NULL,
317 p_action_information26=> NULL,
318 p_action_information27=> NULL,
319 p_action_information28=> NULL,
320 p_action_information29=> NULL,
321 p_action_information30=> NULL
322 );
323
324 IF nvl(g_sp_org_id,-9999) <> -9999 THEN
325 OPEN csr_location(l_org_details_rec.location_id);
326 FETCH csr_location INTO l_location_rec;
327 CLOSE csr_location;
328
329 ELSE
330 OPEN csr_location(l_legal_employer_rec.location_id);
331 FETCH csr_location INTO l_location_rec;
332 CLOSE csr_location;
333 END IF;
334 pay_action_information_api.create_action_information (
335 p_action_information_id=> l_action_info_id,
336 p_action_context_id=> p_payroll_action_id,
337 p_action_context_type=> 'PA',
338 p_object_version_number=> l_ovn,
339 p_effective_date=> g_effective_date,
340 p_source_id=> NULL,
341 p_source_text=> NULL,
342 p_action_information_category=> 'EMEA REPORT INFORMATION',
343 p_action_information1=> 'PYNOSSTA',
344 p_action_information2=> 'LE',
345 p_action_information3=> nvl(g_sp_org_id,g_legal_employer_id),
346 p_action_information4=> nvl(l_org_details_rec.name,l_legal_employer_rec.name),
347 p_action_information5=> l_legal_employer_rec.ORG_INFORMATION1,
348 p_action_information6=> l_location_rec.address,
349 p_action_information7=> l_location_rec.postal_code,
350 p_action_information8=> l_org_details_rec.sp_org_number,
351 p_action_information9=> NULL,
352 p_action_information10=> NULL,
353 p_action_information11=> NULL,
354 p_action_information12=> NULL,
355 p_action_information13=> NULL,
356 p_action_information14=> NULL,
357 p_action_information15=> NULL,
358 p_action_information16=> NULL,
359 p_action_information17=> NULL,
360 p_action_information18=> NULL,
361 p_action_information19=> NULL,
362 p_action_information20=> NULL,
363 p_action_information21=> NULL,
364 p_action_information22=> NULL,
365 p_action_information23=> NULL,
366 p_action_information24=> NULL,
367 p_action_information25=> NULL,
368 p_action_information26=> NULL,
369 p_action_information27=> NULL,
370 p_action_information28=> NULL,
371 p_action_information29=> NULL,
372 p_action_information30=> NULL
373 );
374
375
376 FOR l_all_local_unit_details_rec IN
377 csr_all_local_unit_details (g_legal_employer_id)
378 LOOP
379
380
381 OPEN csr_location(l_all_local_unit_details_rec.location_id);
382 FETCH csr_location INTO l_location_rec;
383 CLOSE csr_location;
384
385
386 pay_action_information_api.create_action_information (
387 p_action_information_id=> l_action_info_id,
388 p_action_context_id=> p_payroll_action_id,
389 p_action_context_type=> 'PA',
390 p_object_version_number=> l_ovn,
391 p_effective_date=> g_effective_date,
392 p_source_id=> NULL,
393 p_source_text=> NULL,
394 p_action_information_category=> 'EMEA REPORT INFORMATION',
395 p_action_information1=> 'PYNOSSTA',
396 p_action_information2=> 'LU',
397 p_action_information3=> to_char(l_all_local_unit_details_rec.local_unit_id),
398 p_action_information4=> l_all_local_unit_details_rec.local_unit_name,
399 p_action_information5=> l_all_local_unit_details_rec.ORG_INFORMATION1,
400 p_action_information6=> l_location_rec.address,
401 p_action_information7=> l_location_rec.postal_code,
402 p_action_information8=> l_all_local_unit_details_rec.org_information3,
403 p_action_information9=> NULL,
404 p_action_information10=> NULL,
405 p_action_information11=> NULL,
406 p_action_information12=> NULL,
407 p_action_information13=> NULL,
408 p_action_information14=> NULL,
409 p_action_information15=> NULL,
410 p_action_information16=> NULL,
411 p_action_information17=> NULL,
412 p_action_information18=> NULL,
413 p_action_information19=> NULL,
414 p_action_information20=> NULL,
415 p_action_information21=> NULL,
416 p_action_information22=> NULL,
417 p_action_information23=> NULL,
418 p_action_information24=> NULL,
419 p_action_information25=> NULL,
420 p_action_information26=> NULL,
421 p_action_information27=> NULL,
422 p_action_information28=> NULL,
423 p_action_information29=> NULL,
424 p_action_information30=> NULL
425 );
426
427 END LOOP;
428
429 END IF; -- G_Archive End
430
431 IF g_debug THEN
432 hr_utility.set_location(' Leaving Procedure RANGE_CODE',50);
433 END IF; --
434 END range_code;
435 --
436 -- ---------------------------------------------------------------------
437 -- Function to get defined balance id
438 -- ---------------------------------------------------------------------
439 --
440 FUNCTION GET_DEFINED_BALANCE_ID(p_user_name IN VARCHAR2) RETURN NUMBER
441 IS
442 /* Cursor to retrieve Defined Balance Id */
443 CURSOR csr_def_bal_id(p_user_name VARCHAR2) IS
444 SELECT u.creator_id
445 FROM ff_user_entities u,
446 ff_database_items d
447 WHERE d.user_name = p_user_name
448 AND u.user_entity_id = d.user_entity_id
449 AND (u.legislation_code = 'NO' )
450 AND (u.business_group_id IS NULL )
451 AND u.creator_type = 'B';
452 l_defined_balance_id ff_user_entities.user_entity_id%TYPE;
453
454 BEGIN
455 IF g_debug THEN
456 hr_utility.set_location(' Entering Function GET_DEFINED_BALANCE_ID',240);
457 END IF;
458
459 OPEN csr_def_bal_id(p_user_name);
460 FETCH csr_def_bal_id INTO l_defined_balance_id;
461 CLOSE csr_def_bal_id;
462
463 RETURN l_defined_balance_id;
464
465 IF g_debug THEN
466 hr_utility.set_location(' Leaving Function GET_DEFINED_BALANCE_ID',250);
467 END IF;
468 END GET_DEFINED_BALANCE_ID;
469 --
470 -- -----------------------------------------------------------------------------
471 -- Create assignment actions for all assignments to be archived.
472 -- -----------------------------------------------------------------------------
473 --
474 PROCEDURE assignment_action_code
475 (p_payroll_action_id IN NUMBER
476 ,p_start_person IN NUMBER
477 ,p_end_person IN NUMBER
478 ,p_chunk IN NUMBER) IS
479 --
480 CURSOR csr_assignments
481 (p_local_unit_id NUMBER
482 ,p_start_person NUMBER
483 ,p_end_person NUMBER
484 ,p_report_date DATE) IS
485 SELECT pap.person_id,
486 pap.full_name,
487 substr(pap.national_identifier,0,6) || substr(pap.national_identifier,8,5) national_identifier,
488 paa.assignment_number employee_number,
489 to_char(pap.original_date_of_hire,'YYYYMMDD') seniority,
490 hsc.segment2 local_unit,
491 hsc.segment3 Position_code,
492 hsc.segment4 work_title,
493 hsc.segment5 job_status,
494 hsc.segment6 cond_of_emp,
495 hsc.segment7 full_part_time,
496 hsc.segment8 shift_work,
497 hsc.segment9 payroll_period,
498 hsc.segment10 agreed_working_hrs,
499 decode(nvl(decode(hsc.segment23,'N',null,hsc.segment23),paa.hourly_salaried_code),'S',1,'H',2,3) hourly_salaried_code,
500 hsc.segment20 spl_info_1,
501 hsc.segment21 spl_info_2,
502 hsc.segment22 spl_info_3,
503 paa.assignment_id,
504 paa.payroll_id
505 FROM per_all_assignments_f paa,
506 per_all_people_f pap,
507 HR_SOFT_CODING_KEYFLEX hsc,
508 per_person_types ppt
509 WHERE pap.person_id BETWEEN p_start_person AND p_end_person
510 AND pap.effective_start_date <= p_report_date
511 AND pap.effective_end_date >= trunc(p_report_date,'Y')
512 AND ppt.system_person_type like 'EMP%'
513 AND ppt.person_type_id= pap.person_type_id
514 AND pap.person_id = paa.person_id
515 AND paa.effective_start_date <= p_report_date
516 AND paa.effective_end_date >= trunc(p_report_date,'Y')
517 AND hsc.soft_coding_keyflex_id = paa.soft_coding_keyflex_id
518 AND hsc.segment2 = to_char(p_local_unit_id);
519
520
521 CURSOR csr_org_emp_defaults
522 IS
523 select org_information1 job_status,
524 org_information2 cond_of_emp,
525 org_information3 full_part_time,
526 org_information4 shift_work,
527 org_information5 payroll_period,
528 org_information6 agreed_working_hrs
529 FROM hr_organization_information hoi,
530 hr_organization_units hou
531 WHERE hou.organization_id=g_legal_employer_id
532 AND hoi.organization_id = hou.organization_id
533 AND hoi.org_information_context='NO_EMPLOYMENT_DEFAULTS';
534
535
536
537 CURSOR csr_all_local_unit_details
538 IS
539 SELECT hoi_le.org_information1 local_unit_id,
540 hou_lu.NAME local_unit_name,
541 hoi_lu.org_information1,
542 hou_lu.location_id
543 FROM hr_all_organization_units hou_le,
544 hr_organization_information hoi_le,
545 hr_all_organization_units hou_lu,
546 hr_organization_information hoi_lu
547 WHERE hoi_le.organization_id = hou_le.organization_id
548 AND hou_le.organization_id = g_legal_employer_id
549 AND hoi_le.org_information_context = 'NO_LOCAL_UNITS'
550 AND hou_lu.organization_id = hoi_le.org_information1
551 AND hou_lu.organization_id = hoi_lu.organization_id
552 AND hoi_lu.org_information_context = 'NO_LOCAL_UNIT_DETAILS';
553
554
555 CURSOR csr_period_add_info_result
556 (p_assignment_id NUMBER,
557 p_ssb_code VARCHAR2,
558 p_report_date DATE)
559
560 IS
561 SELECT sum(prrv.result_value) result
562 FROM
563 pay_assignment_actions paa,
564 pay_payroll_actions ppa,
565 per_time_periods ptp,
566 pay_run_results prr,
567 pay_run_result_values prrv,
568 pay_element_types_f pet,
569 pay_element_type_extra_info pxi
570 WHERE
571 paa.assignment_id =p_assignment_id
572 and paa.payroll_action_id = ppa.payroll_action_id
573 and ptp.time_period_id = ppa.time_period_id
574 and p_report_date between ptp.start_date and ptp.end_date
575 and ppa.effective_date between ptp.start_date and ptp.end_date
576 and paa.assignment_action_id = prr.assignment_action_id
577 and prr.element_type_id = pet.element_type_id
578 and pet.element_type_id = pxi.element_type_id
579 and pxi.eei_information_category = 'NO_SSB_CODES'
580 and pxi.eei_information3 = 'ADD_INFO'
581 and pxi.eei_information2 = p_ssb_code
582 and to_char(prrv.input_value_id) = pxi.eei_information1
583 and prrv.run_result_id = prr.run_result_id
584 order by pxi.eei_information2;
585
586 CURSOR csr_year_add_info_result
587 (p_assignment_id NUMBER,
588 p_ssb_code VARCHAR2,
589 p_report_date DATE)
590 IS
591 SELECT sum(prrv.result_value) result
592 FROM
593 pay_assignment_actions paa,
594 pay_payroll_actions ppa,
595 pay_run_results prr,
596 pay_run_result_values prrv,
597 pay_element_types_f pet,
598 pay_element_type_extra_info pxi
599 WHERE
600 paa.assignment_id =p_assignment_id
601 and paa.payroll_action_id = ppa.payroll_action_id
602 and ppa.effective_date between trunc(p_report_date,'Y') and p_report_date
603 and paa.assignment_action_id = prr.assignment_action_id
604 and prr.element_type_id = pet.element_type_id
605 and pet.element_type_id = pxi.element_type_id
606 and pxi.eei_information_category = 'NO_SSB_CODES'
607 and pxi.eei_information3 = 'ADD_INFO'
608 and pxi.eei_information2 = p_ssb_code
609 and to_char(prrv.input_value_id) = pxi.eei_information1
610 and prrv.run_result_id = prr.run_result_id
611 order by pxi.eei_information2;
612
613 CURSOR csr_payroll_details
614 (p_payroll_id NUMBER,
615 p_report_date DATE)
616 IS
617 SELECT end_date,
618 start_date
619 from per_time_periods
620 where payroll_id = p_payroll_id
621 and p_report_date BETWEEN start_date and end_date;
622
623 CURSOR csr_previous_period
624 (p_payroll_id NUMBER,
625 p_report_date DATE)
626 IS
627 SELECT max(end_date) prev_end_date
628 from per_time_periods
629 where payroll_id = p_payroll_id
630 and end_date < p_report_date;
631
632 --
633 l_csr_org_emp_defaults_rec csr_org_emp_defaults%ROWTYPE;
634 l_csr_payroll_details_rec csr_payroll_details%ROWTYPE;
635 l_csr_previous_period_rec csr_previous_period%ROWTYPE;
636 l_all_local_unit_details_rec csr_all_local_unit_details%ROWTYPE;
637 l_year_add_info_result_rec csr_year_add_info_result%ROWTYPE;
638 l_period_add_info_result_rec csr_period_add_info_result%ROWTYPE;
639 l_assact_id NUMBER;
640 l_person_id NUMBER := -1;
641 l_action_info_id NUMBER;
642 l_ovn NUMBER;
643 l_asg_rec csr_assignments%ROWTYPE;
644
645 l_ssb_code_0010_ptd NUMBER;
646 l_ssb_code_0010_ytd NUMBER;
647 l_ssb_code_0020_ptd NUMBER;
648 l_ssb_code_0020_ytd NUMBER;
649 l_ssb_code_0010_hrs_ptd NUMBER;
650 l_ssb_code_0010_hrs_ytd NUMBER;
651 l_ssb_code_0030_ytd NUMBER;
652 l_ssb_code_0035_ytd NUMBER;
653 l_ssb_code_0035_hrs_ytd NUMBER;
654 l_ssb_code_0037_ytd NUMBER;
655 l_ssb_code_0038_ytd NUMBER;
656 l_ssb_code_0038_pyqtd NUMBER;
657 l_ssb_code_0040_ytd NUMBER;
658 l_ssb_code_0041_ytd NUMBER;
659
660 l_ptd_defined_balance_id NUMBER;
661 l_ytd_defined_balance_id NUMBER;
662 l_pyqtd_defined_balance_id NUMBER;
663 l_effective_date DATE;
664
665 --
666 BEGIN
667 --
668 --
669 -- Setup legislative parameters as global values for future use.
670 --
671 set_parameters(p_payroll_action_id);
672
673
674 g_payroll_action_id :=p_payroll_action_id;
675 g_business_group_id := null;
676 g_legal_employer_id := null;
677 g_sp_org_id := null;
678 g_legal_employer_id := null;
679 g_version := null;
680 g_archive := null;
681
682 GET_ALL_PARAMETERS
683 (p_payroll_action_id
684 ,g_business_group_id
685 ,g_effective_date
686 ,g_sp_org_id
687 ,g_legal_employer_id
688 ,g_version
689 ,g_archive
690 );
691
692
693 IF g_archive = 'Y'
694 THEN
695
696
697 for l_all_local_unit_details_rec in csr_all_local_unit_details loop
698
699 --
700 FOR l_asg_rec IN csr_assignments(l_all_local_unit_details_rec.local_unit_id, p_start_person, p_end_person, g_report_date) LOOP
701 --
702 --
703 -- Create assignment action for archive process.
704 --
705 OPEN CSR_PAYROLL_DETAILS (l_asg_rec.payroll_id, g_effective_date);
706 FETCH CSR_PAYROLL_DETAILS INTO l_csr_payroll_details_rec;
707 CLOSE CSR_PAYROLL_DETAILS;
708
709 IF l_csr_payroll_details_rec.end_date > g_effective_date THEN
710 OPEN csr_previous_period (l_asg_rec.payroll_id, g_effective_date);
711 FETCH csr_previous_period INTO l_csr_previous_period_rec;
712 CLOSE csr_previous_period;
713
714 l_effective_date := l_csr_previous_period_rec.prev_end_date;
715
716 ELSE
717
718 l_effective_date := g_effective_date;
719
720 END IF;
721
722 SELECT pay_assignment_actions_s.nextval INTO l_assact_id FROM dual;
723 hr_nonrun_asact.insact
724 (l_assact_id
725 ,l_asg_rec.assignment_id
726 ,p_payroll_action_id
727 ,p_chunk
728 ,NULL);
729 --
730 --
731 -- Create assignment action archive information :-
732 --
733 --
734
735 OPEN csr_org_emp_defaults;
736 FETCH csr_org_emp_defaults INTO l_csr_org_emp_defaults_rec;
737 CLOSE csr_org_emp_defaults;
738
739 pay_action_information_api.create_action_information (
740 p_action_information_id=> l_action_info_id,
741 p_action_context_id=> l_assact_id,
742 p_action_context_type=> 'AAP',
743 p_object_version_number=> l_ovn,
744 p_effective_date=> g_effective_date,
745 p_assignment_id => l_asg_rec.assignment_id,
746 p_action_information_category=> 'EMEA REPORT INFORMATION',
747 p_action_information1=> 'PYNOSSTA',
748 p_action_information2=> 'ASG',
749 p_action_information3=> l_asg_rec.local_unit,
750 p_action_information4=> to_char(l_asg_rec.person_id),
751 p_action_information5=> l_asg_rec.full_name,
752 p_action_information6=> l_asg_rec.national_identifier,
753 p_action_information7=> l_asg_rec.employee_number,
754 p_action_information8=> l_asg_rec.seniority,
755 p_action_information9=> l_asg_rec.Position_code,
756 p_action_information10=> l_asg_rec.work_title,
757 p_action_information11=> nvl(l_asg_rec.job_status,l_csr_org_emp_defaults_rec.job_status),
758 p_action_information12=> nvl(l_asg_rec.cond_of_emp,l_csr_org_emp_defaults_rec.cond_of_emp),
759 p_action_information13=> nvl(l_asg_rec.full_part_time,l_csr_org_emp_defaults_rec.full_part_time),
760 p_action_information14=> nvl(l_asg_rec.shift_work,l_csr_org_emp_defaults_rec.shift_work),
761 p_action_information15=> nvl(l_asg_rec.agreed_working_hrs,l_csr_org_emp_defaults_rec.agreed_working_hrs),
762 p_action_information16=> l_asg_rec.spl_info_1,
763 p_action_information17=> l_asg_rec.spl_info_2,
764 p_action_information18=> l_asg_rec.spl_info_3,
765 p_action_information19=> l_asg_rec.hourly_salaried_code,
766 p_action_information20=> nvl(l_asg_rec.payroll_period,l_csr_org_emp_defaults_rec.payroll_period)
767 );
768
769 --
770
771
772 l_ptd_defined_balance_id := get_defined_balance_id('SUMMED_RESULTS_ASG_ELE_CODE_PTD');
773 l_ytd_defined_balance_id := get_defined_balance_id('SUMMED_RESULTS_ASG_ELE_CODE_YTD');
774 l_pyqtd_defined_balance_id := get_defined_balance_id('SUMMED_RESULTS_ASG_ELE_CODE_PYLQ');
775
776 pay_balance_pkg.set_context('SOURCE_TEXT','SSB CODE 0010');
777
778 begin
779 l_ssb_code_0010_ptd := to_char(pay_balance_pkg.get_value(p_defined_balance_id => l_ptd_defined_balance_id,
780 p_assignment_id => l_asg_rec.assignment_id,
781 p_virtual_date => l_effective_date),'999999999D99') ;
782 exception
783 when no_data_found then
784 null;
785 end;
786
787 begin
788 l_ssb_code_0010_ytd := to_char(pay_balance_pkg.get_value(p_defined_balance_id => l_ytd_defined_balance_id,
789 p_assignment_id => l_asg_rec.assignment_id,
790 p_virtual_date => l_effective_date),'999999999D99') ;
791 exception
792 when no_data_found then
793 null;
794 end;
795
796 pay_balance_pkg.set_context('SOURCE_TEXT','SSB CODE 0020');
797 begin
798 l_ssb_code_0020_ptd := to_char(pay_balance_pkg.get_value(p_defined_balance_id => l_ptd_defined_balance_id,
799 p_assignment_id => l_asg_rec.assignment_id,
800 p_virtual_date => l_effective_date),'999999999D99') ;
801 exception
802 when no_data_found then
803 null;
804 end;
805
806
807 begin
808 l_ssb_code_0020_ytd := to_char(pay_balance_pkg.get_value(p_defined_balance_id => l_ytd_defined_balance_id,
809 p_assignment_id => l_asg_rec.assignment_id,
810 p_virtual_date => l_effective_date),'999999999D99') ;
811 exception
812 when no_data_found then
813 null;
814 end;
815
816
817 OPEN csr_period_add_info_result(l_asg_rec.assignment_id,'SSB CODE 0010',l_effective_date);
818 FETCH csr_period_add_info_result INTO l_ssb_code_0010_hrs_ptd;
819 CLOSE csr_period_add_info_result;
820
821
822 OPEN csr_year_add_info_result(l_asg_rec.assignment_id,'SSB CODE 0010',l_effective_date);
823 FETCH csr_year_add_info_result INTO l_ssb_code_0010_hrs_ytd;
824 CLOSE csr_year_add_info_result;
825
826
827 pay_balance_pkg.set_context('SOURCE_TEXT','SSB CODE 0030');
828 begin
829 l_ssb_code_0030_ytd := to_char(pay_balance_pkg.get_value(p_defined_balance_id => l_ytd_defined_balance_id,
830 p_assignment_id => l_asg_rec.assignment_id,
831 p_virtual_date => l_effective_date),'999999999D99') ;
832 exception
833 when no_data_found then
834 null;
835 end;
836
837 pay_balance_pkg.set_context('SOURCE_TEXT','SSB CODE 0035');
838 begin
839 l_ssb_code_0035_ytd := to_char(pay_balance_pkg.get_value(p_defined_balance_id => l_ytd_defined_balance_id,
840 p_assignment_id => l_asg_rec.assignment_id,
841 p_virtual_date => l_effective_date),'999999999D99') ;
842
843 exception
844 when no_data_found then
845 null;
846 end;
847
848 OPEN csr_year_add_info_result(l_asg_rec.assignment_id,'SSB CODE 0035',l_effective_date);
849 FETCH csr_year_add_info_result INTO l_ssb_code_0035_hrs_ytd;
850 CLOSE csr_year_add_info_result;
851
852 pay_balance_pkg.set_context('SOURCE_TEXT','SSB CODE 0037');
853 begin
854 l_ssb_code_0037_ytd := to_char(pay_balance_pkg.get_value(p_defined_balance_id => l_ytd_defined_balance_id,
855 p_assignment_id => l_asg_rec.assignment_id,
856 p_virtual_date => l_effective_date),'999999999D99') ;
857 exception
858 when no_data_found then
859 null;
860 end;
861
862 pay_balance_pkg.set_context('SOURCE_TEXT','SSB CODE 0038');
863 begin
864 l_ssb_code_0038_ytd := to_char(pay_balance_pkg.get_value(p_defined_balance_id => l_ytd_defined_balance_id,
865 p_assignment_id => l_asg_rec.assignment_id,
866 p_virtual_date => l_effective_date),'999999999D99') ;
867 exception
868 when no_data_found then
869 null;
870 end;
871
872 begin
873 l_ssb_code_0038_pyqtd := to_char(pay_balance_pkg.get_value(p_defined_balance_id => l_pyqtd_defined_balance_id,
874 p_assignment_id => l_asg_rec.assignment_id,
875 p_virtual_date => l_effective_date),'999999999D99') ;
876 exception
877 when no_data_found then
878 null;
879 end;
880
881 pay_balance_pkg.set_context('SOURCE_TEXT','SSB CODE 0040');
882 begin
883 l_ssb_code_0040_ytd := to_char(pay_balance_pkg.get_value(p_defined_balance_id => l_ytd_defined_balance_id,
884 p_assignment_id => l_asg_rec.assignment_id,
885 p_virtual_date => l_effective_date),'999999999D99') ;
886 exception
887 when no_data_found then
888 null;
889 end;
890
891 pay_balance_pkg.set_context('SOURCE_TEXT','SSB CODE 0041');
892 begin
893 l_ssb_code_0041_ytd := to_char(pay_balance_pkg.get_value(p_defined_balance_id => l_ytd_defined_balance_id,
894 p_assignment_id => l_asg_rec.assignment_id,
895 p_virtual_date => l_effective_date),'999999999D99') ;
896 exception
897 when no_data_found then
898 null;
899 end;
900
901
902 pay_action_information_api.create_action_information (
903 p_action_information_id=> l_action_info_id,
904 p_action_context_id=> l_assact_id,
905 p_action_context_type=> 'AAP',
906 p_object_version_number=> l_ovn,
907 p_effective_date=> g_effective_date,
908 p_assignment_id => l_asg_rec.assignment_id,
909 p_action_information_category=> 'EMEA REPORT INFORMATION',
910 p_action_information1=> 'PYNOSSTA',
911 p_action_information2=> 'ASG SAL',
912 p_action_information3=> l_asg_rec.local_unit,
913 p_action_information4=> l_asg_rec.employee_number,
914 p_action_information5=> l_asg_rec.national_identifier,
915 p_action_information6=> nvl(l_ssb_code_0010_ptd,0),
916 p_action_information7=> nvl(l_ssb_code_0010_ytd,0),
917 p_action_information8=> nvl(l_ssb_code_0020_ptd,0),
918 p_action_information9=> nvl(l_ssb_code_0020_ytd,0),
919 p_action_information10=> round(nvl(l_ssb_code_0010_hrs_ptd,0)),
920 p_action_information11=> round(nvl(l_ssb_code_0010_hrs_ytd,0)),
921 p_action_information12=> nvl(l_ssb_code_0030_ytd,0),
922 p_action_information13=> nvl(l_ssb_code_0035_ytd,0),
923 p_action_information14=> round(nvl(l_ssb_code_0035_hrs_ytd,0)),
924 p_action_information15=> nvl(l_ssb_code_0037_ytd,0),
925 p_action_information16=> nvl(l_ssb_code_0038_ytd,0),
926 p_action_information17=> nvl(l_ssb_code_0038_pyqtd,0),
927 p_action_information18=> nvl(l_ssb_code_0040_ytd,0),
928 p_action_information19=> nvl(l_ssb_code_0041_ytd,0)
929 );
930
931 --
932 END LOOP; -- assignments within the local unit
933 END LOOP; --local unit
934 END IF; --Archive
935 --
936 END assignment_action_code;
937 --
938 --
939 -- -----------------------------------------------------------------------------
940 --
941 -- -----------------------------------------------------------------------------
942 --
943 PROCEDURE initialization_code
944 (p_payroll_action_id IN NUMBER) IS
945 BEGIN
946 NULL;
947 END initialization_code;
948 --
949 --
950 -- -----------------------------------------------------------------------------
951 -- Create archive information for individual assignment actions.
952 -- -----------------------------------------------------------------------------
953 --
954 PROCEDURE archive_code
955 (p_assignment_action_id IN NUMBER
956 ,p_effective_date IN DATE) IS
957 --
958 --
959 BEGIN
960 --
961 --
962 null;
963 END archive_code;
964 --
965 --
966 -- -----------------------------------------------------------------------------
967 -- Assemble XML for reporting.
968 -- -----------------------------------------------------------------------------
969 --
970
971 PROCEDURE WritetoCLOB(p_xfdf_clob out nocopy CLOB) is
972 l_xfdf_string clob;
973 l_str1 varchar2(1000);
974 l_str2 varchar2(20);
975 l_str3 varchar2(20);
976 l_str4 varchar2(20);
977 l_str5 varchar2(20);
978 l_str6 varchar2(30);
979 l_str7 varchar2(1000);
980 l_str8 varchar2(240);
981 l_str9 varchar2(240);
982 l_str10 varchar2(20);
983 l_str11 varchar2(20);
984 l_IANA_charset VARCHAR2 (50);
985
986 current_index pls_integer;
987
988 BEGIN
989
990 hr_utility.set_location('Entering WritetoCLOB ',10);
991
992
993 l_IANA_charset := HR_NO_UTILITY.get_IANA_charset ;
994 l_str1 := '<?xml version="1.0" encoding="'||l_IANA_charset||'"?> <ROOT><PAACR>';
995 l_str2 := '<';
996 l_str3 := '>';
997 l_str4 := '</';
998 l_str5 := '>';
999 l_str6 := '</PAACR></ROOT>';
1000 l_str7 := '<?xml version="1.0" encoding="'||l_IANA_charset||'"?> <ROOT></ROOT>';
1001 l_str10 := '<PAACR>';
1002 l_str11 := '</PAACR>';
1003
1004
1005 dbms_lob.createtemporary(l_xfdf_string,FALSE,DBMS_LOB.CALL);
1006 dbms_lob.open(l_xfdf_string,dbms_lob.lob_readwrite);
1007
1008 current_index := 0;
1009
1010 IF xml_tab.count > 0 THEN
1011
1012 dbms_lob.writeAppend( l_xfdf_string, length(l_str1), l_str1 );
1013
1014
1015 FOR table_counter IN xml_tab.FIRST .. xml_tab.LAST LOOP
1016
1017 l_str8 := xml_tab(table_counter).TagName;
1018 l_str9 := xml_tab(table_counter).TagValue;
1019
1020 IF l_str9 IN ('STATEMENT_PROVIDER','LOCAL_UNIT','EMPLOYEE_DETAILS','EMPLOYEE',
1021 'EMPLOYEE_SALARY','LOCAL_UNIT_END','FILLER','STATEMENT_PROVIDER_END'
1022 ,'END_EMPLOYEE_DETAILS','END_EMPLOYEE','END_EMPLOYEE_SALARY',
1023 'END_LOCAL_UNIT_END','END_LOCAL_UNIT','END_FILLER',
1024 'END_STATEMENT_PROVIDER_END','END_STATEMENT_PROVIDER'
1025 ) THEN
1026
1027 IF l_str9 IN ('STATEMENT_PROVIDER','LOCAL_UNIT','EMPLOYEE_DETAILS',
1028 'EMPLOYEE', 'EMPLOYEE_SALARY','LOCAL_UNIT_END','FILLER',
1029 'STATEMENT_PROVIDER_END') THEN
1030 dbms_lob.writeAppend(l_xfdf_string, length(l_str2), l_str2);
1031 dbms_lob.writeAppend(l_xfdf_string, length(l_str8), l_str8);
1032 dbms_lob.writeAppend(l_xfdf_string, length(l_str3), l_str3);
1033 ELSE
1034 dbms_lob.writeAppend(l_xfdf_string, length(l_str4), l_str4);
1035 dbms_lob.writeAppend(l_xfdf_string, length(l_str8), l_str8);
1036 dbms_lob.writeAppend(l_xfdf_string, length(l_str5), l_str5);
1037 END IF;
1038
1039 ELSE
1040
1041 if l_str9 is not null then
1042
1043 dbms_lob.writeAppend(l_xfdf_string, length(l_str2), l_str2);
1044 dbms_lob.writeAppend(l_xfdf_string, length(l_str8), l_str8);
1045 dbms_lob.writeAppend(l_xfdf_string, length(l_str3), l_str3);
1046 dbms_lob.writeAppend(l_xfdf_string, length(l_str9), l_str9);
1047 dbms_lob.writeAppend(l_xfdf_string, length(l_str4), l_str4);
1048 dbms_lob.writeAppend(l_xfdf_string, length(l_str8), l_str8);
1049 dbms_lob.writeAppend(l_xfdf_string, length(l_str5), l_str5);
1050 else
1051
1052 dbms_lob.writeAppend(l_xfdf_string, length(l_str2), l_str2);
1053 dbms_lob.writeAppend(l_xfdf_string, length(l_str8), l_str8);
1054 dbms_lob.writeAppend(l_xfdf_string, length(l_str3), l_str3);
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
1059 end if;
1060
1061 END IF;
1062
1063 END LOOP;
1064
1065 dbms_lob.writeAppend(l_xfdf_string, length(l_str6), l_str6 );
1066
1067 ELSE
1068 dbms_lob.writeAppend(l_xfdf_string, length(l_str7), l_str7 );
1069 END IF;
1070
1071 p_xfdf_clob := l_xfdf_string;
1072
1073 hr_utility.set_location('Leaving WritetoCLOB ',20);
1074
1075 EXCEPTION
1076 WHEN OTHERS then
1077 HR_UTILITY.TRACE('sqlerrm ' || SQLERRM);
1078 HR_UTILITY.RAISE_ERROR;
1079 END WritetoCLOB;
1080
1081 --
1082 --
1083 -----------------------------------------------------------------------------------
1084 --
1085 -----------------------------------------------------------------------------------
1086 --
1087 --
1088 PROCEDURE POPULATE_DATA_DETAIL
1089 (p_business_group_id IN NUMBER,
1090 p_payroll_action_id IN VARCHAR2 ,
1091 p_template_name IN VARCHAR2,
1092 p_xml OUT NOCOPY CLOB)
1093 IS
1094
1095
1096 /* Cursor to fetch Header Information */
1097
1098
1099 CURSOR csr_version (p_payroll_action_id NUMBER)
1100 IS
1101 SELECT fnd_date.canonical_to_date(action_information5) report_date,
1102 action_information6 version,
1103 nvl(action_information8,action_information4) name_sp
1104 FROM pay_action_information pai
1105 WHERE pai.action_context_id = p_payroll_action_id
1106 AND pai.action_context_type='PA'
1107 AND pai.action_information_category='EMEA REPORT DETAILS'
1108 AND pai.action_information1='PYNOSSTA';
1109
1110
1111 CURSOR csr_legal_employer_details (p_payroll_action_id NUMBER)
1112 IS
1113 SELECT
1114 action_information1,
1115 action_information2,
1116 action_information3,
1117 action_information4,
1118 action_information5,
1119 action_information6,
1120 action_information7,
1121 action_information8
1122 FROM pay_action_information pai
1123 WHERE pai.action_context_id = p_payroll_action_id
1124 AND pai.action_context_type='PA'
1125 AND pai.action_information_category='EMEA REPORT INFORMATION'
1126 AND pai.action_information1='PYNOSSTA'
1127 AND pai.action_information2='LE';
1128
1129 CURSOR csr_local_unit_details (p_payroll_action_id NUMBER)
1130 IS
1131 SELECT
1132 action_information1,
1133 action_information2,
1134 action_information3,
1135 action_information4,
1136 action_information5,
1137 action_information6,
1138 action_information7,
1139 action_information8
1140 FROM pay_action_information pai
1141 WHERE pai.action_context_id = p_payroll_action_id
1142 AND pai.action_context_type='PA'
1143 AND pai.action_information_category='EMEA REPORT INFORMATION'
1144 AND pai.action_information1='PYNOSSTA'
1145 AND pai.action_information2='LU';
1146
1147 CURSOR csr_get_employee_details (p_payroll_action_id NUMBER,
1148 p_local_unit_id NUMBER)
1149 IS
1150 SELECT
1151 pai.action_information6 national_identifier,
1152 pai.action_information7 employee_number,
1153 pai.action_information8 seniority,
1154 pai.action_information9 position_code,
1155 pai.action_information10 work_title,
1156 pai.action_information11 job_status,
1157 pai.action_information12 cond_of_emp,
1158 pai.action_information13 full_part_time,
1159 pai.action_information14 shift_work,
1160 pai.action_information15 agreed_working_hrs,
1161 pai.action_information16 spl_info_1,
1162 pai.action_information17 spl_info_2,
1163 pai.action_information18 spl_info_3,
1164 pai.action_information19 hourly_salaried,
1165 pai.action_information20 payroll_period,
1166 pai_sal.action_information6 SSB_CODE_0010_PTD,
1167 pai_sal.action_information7 SSB_CODE_0010_YTD,
1168 pai_sal.action_information8 SSB_CODE_0020_PTD,
1169 pai_sal.action_information9 SSB_CODE_0020_YTD,
1170 pai_sal.action_information10 SSB_CODE_0010_HRS_PTD,
1171 pai_sal.action_information11 SSB_CODE_0010_HRS_YTD,
1172 pai_sal.action_information12 SSB_CODE_0030_YTD,
1173 pai_sal.action_information13 SSB_CODE_0035_YTD,
1174 pai_sal.action_information14 SSB_CODE_0035_HRS_YTD,
1175 pai_sal.action_information15 SSB_CODE_0037_YTD,
1176 pai_sal.action_information16 SSB_CODE_0038_YTD,
1177 pai_sal.action_information17 SSB_CODE_0038_PYQTD,
1178 pai_sal.action_information18 SSB_CODE_0040_YTD,
1179 pai_sal.action_information19 SSB_CODE_0041_YTD
1180 FROM
1181 pay_payroll_actions paa,
1182 pay_assignment_actions assg,
1183 pay_action_information pai,
1184 pay_action_information pai_sal
1185 WHERE
1186 paa.payroll_action_id = p_payroll_action_id
1187 AND assg.payroll_action_id = paa.payroll_action_id
1188 AND pai.action_context_id= assg.assignment_action_id
1189 AND pai.action_context_type='AAP'
1190 AND pai.action_information_category='EMEA REPORT INFORMATION'
1191 AND pai.action_information1='PYNOSSTA'
1192 AND pai.action_information2='ASG'
1193 AND pai.action_information3=p_local_unit_id
1194 AND pai_sal.action_context_id= assg.assignment_action_id
1195 AND pai_sal.action_context_type='AAP'
1196 AND pai_sal.action_information_category='EMEA REPORT INFORMATION'
1197 AND pai_sal.action_information1='PYNOSSTA'
1198 AND pai_sal.action_information2='ASG SAL'
1199 AND pai_sal.action_information3=p_local_unit_id order by national_identifier;
1200
1201 l_employee_rec csr_get_employee_details%rowtype;
1202 l_local_unit_details_rec csr_local_unit_details%rowtype;
1203 l_legal_employer_details_rec csr_legal_employer_details%rowtype;
1204 l_version_rec csr_version%rowtype;
1205
1206
1207
1208 l_per_lu_counter NUMBER;
1209 l_per_lu_counter_eft NUMBER;
1210 l_counter NUMBER;
1211 l_total NUMBER;
1212 l_total_eft NUMBER;
1213 l_count NUMBER;
1214 l_payroll_action_id NUMBER;
1215 l_lu_counter_reset VARCHAR2(10);
1216 l_prev_local_unit VARCHAR2(15);
1217 l_prev_ni_num VARCHAR2(25);
1218
1219
1220 BEGIN
1221
1222 l_per_lu_counter:=0;
1223 l_per_lu_counter_eft:=0;
1224 l_total :=0;
1225 l_total_eft :=3;
1226 l_counter:=0;
1227 l_prev_local_unit := '-9999';
1228 l_lu_counter_reset := 'N';
1229 l_prev_ni_num := '-9999';
1230
1231
1232 IF p_payroll_action_id IS NULL THEN
1233
1234 BEGIN
1235
1236 SELECT payroll_action_id
1237 INTO l_payroll_action_id
1238 FROM pay_payroll_actions ppa,
1239 fnd_conc_req_summary_v fcrs,
1240 fnd_conc_req_summary_v fcrs1
1241 WHERE fcrs.request_id = fnd_global.conc_request_id
1242 AND fcrs.priority_request_id = fcrs1.priority_request_id
1243 AND ppa.request_id between fcrs1.request_id and fcrs.request_id
1244 AND ppa.request_id = fcrs1.request_id;
1245
1246 EXCEPTION
1247 WHEN OTHERS THEN
1248 NULL;
1249 END ;
1250
1251 ELSE
1252
1253 l_payroll_action_id := p_payroll_action_id;
1254
1255 END IF;
1256
1257 hr_utility.set_location('Entered Procedure GETDATA',10);
1258
1259 OPEN csr_version(l_payroll_action_id);
1260 FETCH csr_version INTO l_version_rec;
1261 CLOSE csr_version;
1262
1263
1264 /* Get the File Header Information */
1265 OPEN csr_legal_employer_details(l_payroll_action_id);
1266 FETCH csr_legal_employer_details INTO l_legal_employer_details_rec;
1267 CLOSE csr_legal_employer_details;
1268
1269 hr_utility.set_location('Before populating pl/sql table',20);
1270
1271 xml_tab(l_counter).TagName :='STATEMENT_PROVIDER';
1272 xml_tab(l_counter).TagValue :='STATEMENT_PROVIDER';
1273 l_counter:=l_counter+1;
1274
1275 xml_tab(l_counter).TagName :='ORG_NUMBER_SV';
1276 xml_tab(l_counter).TagValue := '939319891'; -- Fixed value legal entity number of Oracle.
1277 l_counter:=l_counter+1;
1278
1279 xml_tab(l_counter).TagName :='RECORD_ONE';
1280 xml_tab(l_counter).TagValue := '1';
1281 l_counter:=l_counter+1;
1282
1283 xml_tab(l_counter).TagName :='CHAR_CODE';
1284 xml_tab(l_counter).TagValue := '2';
1285 l_counter:=l_counter+1;
1286
1287 xml_tab(l_counter).TagName :='VERSION';
1288 xml_tab(l_counter).TagValue := l_version_rec.version;
1289 l_counter:=l_counter+1;
1290
1291 xml_tab(l_counter).TagName :='DATE';
1292 xml_tab(l_counter).TagValue := to_char(l_version_rec.report_date,'YYYYMMDD');
1293 l_counter:=l_counter+1;
1294
1295 xml_tab(l_counter).TagName :='ORG_NUMBER_LE';
1296 xml_tab(l_counter).TagValue := nvl(l_legal_employer_details_rec.action_information8,l_legal_employer_details_rec.action_information5);
1297 l_counter:=l_counter+1;
1298
1299 xml_tab(l_counter).TagName :='ORG_NUMBER_SP_LE';
1300 xml_tab(l_counter).TagValue := l_legal_employer_details_rec.action_information5;
1301 l_counter:=l_counter+1;
1302
1303 xml_tab(l_counter).TagName :='NAME_SP';
1304 xml_tab(l_counter).TagValue := nvl(l_version_rec.name_sp,l_legal_employer_details_rec.action_information4);
1305 l_counter:=l_counter+1;
1306
1307 xml_tab(l_counter).TagName :='ADDRESS_SP';
1308 xml_tab(l_counter).TagValue := l_legal_employer_details_rec.action_information6;
1309 l_counter:=l_counter+1;
1310
1311 xml_tab(l_counter).TagName :='POSTAL_CODE_SP';
1312 xml_tab(l_counter).TagValue := l_legal_employer_details_rec.action_information7;
1313 l_counter:=l_counter+1;
1314
1315
1316
1317 FOR l_local_unit_details_rec IN csr_local_unit_details(l_payroll_action_id)
1318 LOOP
1319
1320 -- Local Unit Data
1321 xml_tab(l_counter).TagName :='LOCAL_UNIT';
1322 xml_tab(l_counter).TagValue := 'LOCAL_UNIT';
1323 l_counter:=l_counter+1;
1324
1325 xml_tab(l_counter).TagName :='ORG_NUMBER_LU';
1326 xml_tab(l_counter).TagValue := l_local_unit_details_rec.action_information5;
1327 l_counter:=l_counter+1;
1328
1329
1330 xml_tab(l_counter).TagName :='RECORD_TWO';
1331 xml_tab(l_counter).TagValue := '2';
1332 l_counter:=l_counter+1;
1333
1334 xml_tab(l_counter).TagName :='ORG_NUMBER_LE';
1335 xml_tab(l_counter).TagValue := l_legal_employer_details_rec.action_information5;
1336 l_counter:=l_counter+1;
1337
1338 xml_tab(l_counter).TagName :='ORG_NUMBER_SP';
1339 xml_tab(l_counter).TagValue := nvl(l_legal_employer_details_rec.action_information8,l_legal_employer_details_rec.action_information5);
1340 l_counter:=l_counter+1;
1341
1342 xml_tab(l_counter).TagName :='NAME_LU';
1343 xml_tab(l_counter).TagValue := l_local_unit_details_rec.action_information4;
1344 l_counter:=l_counter+1;
1345
1346 xml_tab(l_counter).TagName :='CONF_CODE';
1347 xml_tab(l_counter).TagValue := l_local_unit_details_rec.action_information8;
1348 l_counter:=l_counter+1;
1349
1350 xml_tab(l_counter).TagName :='ADDRESS_LU';
1351 xml_tab(l_counter).TagValue := l_local_unit_details_rec.action_information6;
1352 l_counter:=l_counter+1;
1353
1354 xml_tab(l_counter).TagName :='POSTAL_CODE_LU';
1355 xml_tab(l_counter).TagValue := l_local_unit_details_rec.action_information7;
1356 l_counter:=l_counter+1;
1357
1358
1359 -- Employee Data
1360
1361 l_total_eft := l_total_eft + 2;
1362 FOR l_get_employee_details_rec IN csr_get_employee_details(l_payroll_action_id, to_number(l_local_unit_details_rec.action_information3))
1363 LOOP
1364
1365
1366 /* Begins Employee record*/
1367
1368 IF l_prev_local_unit <> l_local_unit_details_rec.action_information3 and l_lu_counter_reset = 'N' THEN
1369 l_per_lu_counter_eft := 2;
1370 l_per_lu_counter := 0;
1371 l_lu_counter_reset := 'Y';
1372 END IF;
1373
1374
1375 --l_per_lu_counter :=l_per_lu_counter+ 1;
1376 l_per_lu_counter_eft := l_per_lu_counter_eft +2;
1377 --l_total:= l_total+1;
1378 l_total_eft:= l_total_eft + 2;
1379 if l_prev_ni_num <> l_get_employee_details_rec.national_identifier then
1380 l_per_lu_counter := l_per_lu_counter +1;
1381 l_total:= l_total + 1;
1382 end if;
1383
1384 xml_tab(l_counter).TagName :='EMPLOYEE_DETAILS';
1385 xml_tab(l_counter).TagValue :='EMPLOYEE_DETAILS';
1386 l_counter:=l_counter+1;
1387
1388 xml_tab(l_counter).TagName :='EMPLOYEE';
1389 xml_tab(l_counter).TagValue :='EMPLOYEE';
1390 l_counter:=l_counter+1;
1391
1392 xml_tab(l_counter).TagName :='ORG_NUMBER_LU';
1393 xml_tab(l_counter).TagValue := l_local_unit_details_rec.action_information5;
1394 l_counter:=l_counter+1;
1395
1396 xml_tab(l_counter).TagName :='RECORD_THREE';
1397 xml_tab(l_counter).TagValue := '3';
1398 l_counter:=l_counter+1;
1399
1400 xml_tab(l_counter).TagName :='NI';
1401 xml_tab(l_counter).TagValue := l_get_employee_details_rec.national_identifier;
1402 l_counter:=l_counter+1;
1403 l_prev_ni_num := l_get_employee_details_rec.national_identifier;
1404
1405 xml_tab(l_counter).TagName :='EMP_NUM';
1406 xml_tab(l_counter).TagValue := l_get_employee_details_rec.employee_number;
1407 l_counter:=l_counter+1;
1408
1409 xml_tab(l_counter).TagName :='WORK_TITLE';
1410 xml_tab(l_counter).TagValue := '<![CDATA['||l_get_employee_details_rec.work_title||']]>';
1411 l_counter:=l_counter+1;
1412
1413 xml_tab(l_counter).TagName :='POSITION_CODE';
1414 xml_tab(l_counter).TagValue := l_get_employee_details_rec.position_code;
1415 l_counter:=l_counter+1;
1416
1417 xml_tab(l_counter).TagName :='JOB_STATUS';
1418 xml_tab(l_counter).TagValue := l_get_employee_details_rec.job_status;
1419 l_counter:=l_counter+1;
1420
1421 xml_tab(l_counter).TagName :='COND_OF_EMP';
1422 xml_tab(l_counter).TagValue := l_get_employee_details_rec.cond_of_emp;
1423 l_counter:=l_counter+1;
1424
1425 xml_tab(l_counter).TagName :='FULL_PART_TIME';
1426 xml_tab(l_counter).TagValue := l_get_employee_details_rec.full_part_time;
1427 l_counter:=l_counter+1;
1428
1429 xml_tab(l_counter).TagName :='SHIFT_WORK';
1430 xml_tab(l_counter).TagValue := l_get_employee_details_rec.shift_work;
1431 l_counter:=l_counter+1;
1432
1433 xml_tab(l_counter).TagName :='AGREED_WORK_HRS';
1434 xml_tab(l_counter).TagValue := l_get_employee_details_rec.agreed_working_hrs;
1435 l_counter:=l_counter+1;
1436
1437 xml_tab(l_counter).TagName :='HOURLY_SALARIED';
1438 xml_tab(l_counter).TagValue := l_get_employee_details_rec.hourly_salaried;
1439 l_counter:=l_counter+1;
1440
1441 xml_tab(l_counter).TagName :='PAYROLL_PERIOD';
1442 xml_tab(l_counter).TagValue := l_get_employee_details_rec.payroll_period;
1443 l_counter:=l_counter+1;
1444
1445 xml_tab(l_counter).TagName :='SENIORITY';
1446 xml_tab(l_counter).TagValue := l_get_employee_details_rec.seniority;
1447 l_counter:=l_counter+1;
1448
1449 xml_tab(l_counter).TagName :='SPL_INFO_1';
1450 xml_tab(l_counter).TagValue := l_get_employee_details_rec.spl_info_1;
1451 l_counter:=l_counter+1;
1452
1453 xml_tab(l_counter).TagName :='SPL_INFO_2';
1454 xml_tab(l_counter).TagValue := l_get_employee_details_rec.spl_info_2;
1455 l_counter:=l_counter+1;
1456
1457 xml_tab(l_counter).TagName :='SPL_INFO_3';
1458 xml_tab(l_counter).TagValue := l_get_employee_details_rec.spl_info_3;
1459 l_counter:=l_counter+1;
1460
1461 xml_tab(l_counter).TagName :='EMPLOYEE';
1462 xml_tab(l_counter).TagValue :='END_EMPLOYEE';
1463 l_counter:=l_counter+1;
1464
1465 /* Ends Employee record*/
1466 /* Begins Salary record*/
1467
1468 xml_tab(l_counter).TagName :='EMPLOYEE_SALARY';
1469 xml_tab(l_counter).TagValue :='EMPLOYEE_SALARY';
1470 l_counter:=l_counter+1;
1471
1472 xml_tab(l_counter).TagName :='ORG_NUMBER_LU';
1473 xml_tab(l_counter).TagValue :=l_local_unit_details_rec.action_information5;
1474 l_counter:=l_counter+1;
1475
1476 xml_tab(l_counter).TagName :='RECORD_FOUR';
1477 xml_tab(l_counter).TagValue :='4';
1478 l_counter:=l_counter+1;
1479
1480 xml_tab(l_counter).TagName :='NI';
1481 xml_tab(l_counter).TagValue := l_get_employee_details_rec.national_identifier;
1482 l_counter:=l_counter+1;
1483
1484 xml_tab(l_counter).TagName :='EMP_NUM';
1485 xml_tab(l_counter).TagValue := l_get_employee_details_rec.employee_number;
1486 l_counter:=l_counter+1;
1487
1488 xml_tab(l_counter).TagName :='SSB_CODE_0010_PTD';
1489 xml_tab(l_counter).TagValue :=l_get_employee_details_rec.SSB_CODE_0010_PTD;
1490 l_counter:=l_counter+1;
1491
1492 xml_tab(l_counter).TagName :='SSB_CODE_0010_YTD';
1493 xml_tab(l_counter).TagValue :=l_get_employee_details_rec.SSB_CODE_0010_YTD;
1494 l_counter:=l_counter+1;
1495
1496 xml_tab(l_counter).TagName :='SSB_CODE_0020_PTD';
1497 xml_tab(l_counter).TagValue :=l_get_employee_details_rec.SSB_CODE_0020_PTD;
1498 l_counter:=l_counter+1;
1499
1500 xml_tab(l_counter).TagName :='SSB_CODE_0020_YTD';
1501 xml_tab(l_counter).TagValue :=l_get_employee_details_rec.SSB_CODE_0020_YTD;
1502 l_counter:=l_counter+1;
1503
1504 xml_tab(l_counter).TagName :='SSB_CODE_0010_HRS_PTD';
1505 xml_tab(l_counter).TagValue :=l_get_employee_details_rec.SSB_CODE_0010_HRS_PTD;
1506 l_counter:=l_counter+1;
1507
1508 xml_tab(l_counter).TagName :='SSB_CODE_0010_HRS_PTD_EFT';
1509 xml_tab(l_counter).TagValue :=l_get_employee_details_rec.SSB_CODE_0010_HRS_PTD*10;
1510 l_counter:=l_counter+1;
1511
1512 xml_tab(l_counter).TagName :='SSB_CODE_0010_HRS_YTD';
1513 xml_tab(l_counter).TagValue :=l_get_employee_details_rec.SSB_CODE_0010_HRS_YTD;
1514 l_counter:=l_counter+1;
1515
1516 xml_tab(l_counter).TagName :='SSB_CODE_0010_HRS_YTD_EFT';
1517 xml_tab(l_counter).TagValue :=l_get_employee_details_rec.SSB_CODE_0010_HRS_YTD*10;
1518 l_counter:=l_counter+1;
1519
1520 xml_tab(l_counter).TagName :='SSB_CODE_0030_YTD';
1521 xml_tab(l_counter).TagValue :=l_get_employee_details_rec.SSB_CODE_0030_YTD;
1522 l_counter:=l_counter+1;
1523
1524 xml_tab(l_counter).TagName :='SSB_CODE_0035_YTD';
1525 xml_tab(l_counter).TagValue :=l_get_employee_details_rec.SSB_CODE_0035_YTD;
1526 l_counter:=l_counter+1;
1527
1528 xml_tab(l_counter).TagName :='SSB_CODE_0035_HRS_YTD';
1529 xml_tab(l_counter).TagValue :=l_get_employee_details_rec.SSB_CODE_0035_HRS_YTD;
1530 l_counter:=l_counter+1;
1531
1532 xml_tab(l_counter).TagName :='SSB_CODE_0035_HRS_YTD_EFT';
1533 xml_tab(l_counter).TagValue :=l_get_employee_details_rec.SSB_CODE_0035_HRS_YTD*10;
1534 l_counter:=l_counter+1;
1535
1536 xml_tab(l_counter).TagName :='SSB_CODE_0037_YTD';
1537 xml_tab(l_counter).TagValue :=l_get_employee_details_rec.SSB_CODE_0037_YTD;
1538 l_counter:=l_counter+1;
1539
1540 xml_tab(l_counter).TagName :='SSB_CODE_0038_YTD';
1541 xml_tab(l_counter).TagValue :=l_get_employee_details_rec.SSB_CODE_0038_YTD;
1542 l_counter:=l_counter+1;
1543
1544 xml_tab(l_counter).TagName :='SSB_CODE_0038_PYQTD';
1545 xml_tab(l_counter).TagValue :=l_get_employee_details_rec.SSB_CODE_0038_PYQTD;
1546 l_counter:=l_counter+1;
1547
1548 xml_tab(l_counter).TagName :='SSB_CODE_0040_YTD';
1549 xml_tab(l_counter).TagValue :=l_get_employee_details_rec.SSB_CODE_0040_YTD;
1550 l_counter:=l_counter+1;
1551
1552 xml_tab(l_counter).TagName :='SSB_CODE_0041_YTD';
1553 xml_tab(l_counter).TagValue :=l_get_employee_details_rec.SSB_CODE_0041_YTD;
1554 l_counter:=l_counter+1;
1555
1556 xml_tab(l_counter).TagName :='EMPLOYEE_SALARY';
1557 xml_tab(l_counter).TagValue :='END_EMPLOYEE_SALARY';
1558 l_counter:=l_counter+1;
1559
1560 xml_tab(l_counter).TagName :='EMPLOYEE_DETAILS';
1561 xml_tab(l_counter).TagValue :='END_EMPLOYEE_DETAILS';
1562 l_counter:=l_counter+1;
1563
1564 /* Ends Salary record*/
1565
1566
1567 END LOOP; -- employee
1568
1569 l_prev_local_unit := l_local_unit_details_rec.action_information3;
1570 l_lu_counter_reset := 'N' ;
1571
1572 xml_tab(l_counter).TagName :='LOCAL_UNIT_END';
1573 xml_tab(l_counter).TagValue :='LOCAL_UNIT_END';
1574 l_counter:=l_counter+1;
1575
1576 xml_tab(l_counter).TagName :='ORG_NUMBER_LU';
1577 xml_tab(l_counter).TagValue :=l_local_unit_details_rec.action_information5;
1578 l_counter:=l_counter+1;
1579
1580 xml_tab(l_counter).TagName :='RECORD_FIVE';
1581 xml_tab(l_counter).TagValue :='5';
1582 l_counter:=l_counter+1;
1583
1584 xml_tab(l_counter).TagName :='TOTAL_RECORD_LU';
1585 xml_tab(l_counter).TagValue :=l_per_lu_counter;
1586 l_counter:=l_counter+1;
1587
1588 xml_tab(l_counter).TagName :='TOTAL_RECORD_LU_EFT';
1589 xml_tab(l_counter).TagValue :=l_per_lu_counter_eft;
1590 l_counter:=l_counter+1;
1591
1592 xml_tab(l_counter).TagName :='LOCAL_UNIT_END';
1593 xml_tab(l_counter).TagValue :='END_LOCAL_UNIT_END';
1594 l_counter:=l_counter+1;
1595
1596 xml_tab(l_counter).TagName :='LOCAL_UNIT';
1597 xml_tab(l_counter).TagValue := 'END_LOCAL_UNIT';
1598 l_counter:=l_counter+1;
1599
1600
1601 END LOOP; -- Local Unit
1602
1603
1604 xml_tab(l_counter).TagName :='FILLER';
1605 xml_tab(l_counter).TagValue :='FILLER';
1606 l_counter:=l_counter+1;
1607
1608 xml_tab(l_counter).TagName :='RECORD_SIX';
1609 xml_tab(l_counter).TagValue :='6';
1610 l_counter:=l_counter+1;
1611
1612 xml_tab(l_counter).TagName :='FILLER';
1613 xml_tab(l_counter).TagValue := 'END_FILLER';
1614 l_counter:=l_counter+1;
1615
1616
1617 xml_tab(l_counter).TagName :='STATEMENT_PROVIDER_END';
1618 xml_tab(l_counter).TagValue :='STATEMENT_PROVIDER_END';
1619 l_counter:=l_counter+1;
1620
1621 xml_tab(l_counter).TagName :='RECORD_SEVEN';
1622 xml_tab(l_counter).TagValue :='7';
1623 l_counter:=l_counter+1;
1624
1625 xml_tab(l_counter).TagName :='TOTAL_RECORD_SP';
1626 xml_tab(l_counter).TagValue := l_total;
1627 l_counter:=l_counter+1;
1628 xml_tab(l_counter).TagName :='TOTAL_RECORD_SP_EFT';
1629 xml_tab(l_counter).TagValue := l_total_eft;
1630 l_counter:=l_counter+1;
1631
1632 xml_tab(l_counter).TagName :='STATEMENT_PROVIDER_END';
1633 xml_tab(l_counter).TagValue :='END_STATEMENT_PROVIDER_END';
1634 l_counter:=l_counter+1;
1635
1636 xml_tab(l_counter).TagName :='STATEMENT_PROVIDER';
1637 xml_tab(l_counter).TagValue :='END_STATEMENT_PROVIDER';
1638 l_counter := l_counter + 1;
1639
1640
1641 hr_utility.set_location('After populating pl/sql table',30);
1642 hr_utility.set_location('Entered Procedure GETDATA',10);
1643
1644
1645 WritetoCLOB (p_xml );
1646
1647 END POPULATE_DATA_DETAIL;
1648
1649 --
1650 END pay_no_sal_statistics_archive;