[Home] [Help]
PACKAGE BODY: APPS.PAY_SE_INCOME_STATEMENT
Source
1 PACKAGE BODY pay_se_income_statement AS
2 /* $Header: pyseinsr.pkb 120.2 2008/01/25 11:54:01 rsengupt noship $ */
3 g_debug BOOLEAN := hr_utility.debug_enabled;
4
5 TYPE lock_rec IS RECORD (
6 archive_assact_id NUMBER
7 );
8
9 TYPE lock_table IS TABLE OF lock_rec
10 INDEX BY BINARY_INTEGER;
11
12 g_lock_table lock_table;
13 g_index NUMBER := -1;
14 g_index_assact NUMBER := -1;
15 g_index_bal NUMBER := -1;
16 g_package VARCHAR2 (100)
17 := 'PAY_SE_INCOME_STATEMENT.';
18 g_payroll_action_id NUMBER;
19 g_arc_payroll_action_id NUMBER;
20 -- Globals to pick up all the parameter
21 g_business_group_id NUMBER;
22 g_effective_date DATE;
23 g_income_statement_provider_id NUMBER;
24 g_legal_employer_id NUMBER;
25 g_local_unit_id NUMBER;
26 g_request_for VARCHAR2 (20);
27 g_person_for VARCHAR2 (20);
28 g_person_number NUMBER;
29 g_income_year NUMBER;
30 g_test_or_production VARCHAR2 (20);
31 g_income_start_date DATE;
32 g_income_end_date DATE;
33 g_sort_order VARCHAR2 (20);
34 --End of Globals to pick up all the parameter
35 g_format_mask VARCHAR2 (50);
36 g_err_num NUMBER;
37 g_errm VARCHAR2 (150);
38
39 FUNCTION get_parameter (
40 p_parameter_string IN VARCHAR2
41 ,p_token IN VARCHAR2
42 ,p_segment_number IN NUMBER DEFAULT NULL
43 )
44 RETURN VARCHAR2
45 IS
46 l_parameter pay_payroll_actions.legislative_parameters%TYPE := NULL;
47 l_start_pos NUMBER;
48 l_delimiter VARCHAR2 (1) := ' ';
49 l_proc VARCHAR2 (40) := g_package || ' get parameter ';
50 BEGIN
51 --
52 IF g_debug
53 THEN
54 hr_utility.set_location (' Entering Function GET_PARAMETER', 10);
55 END IF;
56
57 l_start_pos :=
58 INSTR (' ' || p_parameter_string, l_delimiter || p_token || '=');
59
60 --
61 IF l_start_pos = 0
62 THEN
63 l_delimiter := '|';
64 l_start_pos :=
65 INSTR (' ' || p_parameter_string, l_delimiter || p_token || '=');
66 END IF;
67
68 IF l_start_pos <> 0
69 THEN
70 l_start_pos := l_start_pos + LENGTH (p_token || '=');
71 l_parameter :=
72 SUBSTR (p_parameter_string
73 ,l_start_pos
74 , INSTR (p_parameter_string || ' '
75 ,l_delimiter
76 ,l_start_pos
77 )
78 - (l_start_pos)
79 );
80
81 IF p_segment_number IS NOT NULL
82 THEN
83 l_parameter := ':' || l_parameter || ':';
84 l_parameter :=
85 SUBSTR (l_parameter
86 , INSTR (l_parameter, ':', 1, p_segment_number) + 1
87 , INSTR (l_parameter, ':', 1, p_segment_number + 1)
88 - 1
89 - INSTR (l_parameter, ':', 1, p_segment_number)
90 );
91 END IF;
92 END IF;
93
94 --
95 IF g_debug
96 THEN
97 hr_utility.set_location (' Leaving Function GET_PARAMETER', 20);
98 END IF;
99
100 RETURN l_parameter;
101 END;
102
103 /* GET ALL PARAMETERS */
104 PROCEDURE get_all_parameters (
105 p_payroll_action_id IN NUMBER -- In parameter
106 ,p_business_group_id OUT NOCOPY NUMBER -- Core parameter
107 ,p_effective_date OUT NOCOPY DATE -- Core parameter
108 ,p_income_statement_provider_id OUT NOCOPY NUMBER -- User parameter
109 ,p_request_for_all_or_not OUT NOCOPY VARCHAR2 -- User parameter
110 ,p_legal_employer_id OUT NOCOPY NUMBER -- User parameter
111 ,p_income_year OUT NOCOPY VARCHAR2 -- User parameter
112 ,p_person_for OUT NOCOPY VARCHAR2 -- User parameter
113 ,p_person_number OUT NOCOPY NUMBER -- User parameter
114 ,p_sort_order OUT NOCOPY VARCHAR2 -- User parameter
115 ,p_test_or_production OUT NOCOPY VARCHAR2 -- User parameter
116 )
117 IS
118 CURSOR csr_parameter_info (p_payroll_action_id NUMBER)
119 IS
120 SELECT (pay_se_income_statement.get_parameter
121 (legislative_parameters
122 ,'INCOME_STATEMENT_PROVIDER'
123 )
124 ) income_statement_provider
125 , (pay_se_income_statement.get_parameter
126 (legislative_parameters
127 ,'REQUEST_FOR'
128 )
129 ) request_for
130 , (pay_se_income_statement.get_parameter
131 (legislative_parameters
132 ,'LEGAL_EMPLOYER'
133 )
134 ) legal_employer
135 , (pay_se_income_statement.get_parameter
136 (legislative_parameters
137 ,'INCOME_YEAR'
138 )
139 ) income_year
140 , (pay_se_income_statement.get_parameter
141 (legislative_parameters
142 ,'PERSON_REQUEST'
143 )
144 ) person_for
145 , (pay_se_income_statement.get_parameter
146 (legislative_parameters
147 ,'REQUESTING_PERSON'
148 )
149 ) person_number
150 , (pay_se_income_statement.get_parameter
151 (legislative_parameters
152 ,'TEST_PRODUCTION'
153 )
154 ) test_production
155 , (pay_se_income_statement.get_parameter
156 (legislative_parameters
157 ,'SORT_ORDER'
158 )
159 ) sort_order
160 ,effective_date
161 ,business_group_id bg_id
162 FROM pay_payroll_actions
163 WHERE payroll_action_id = p_payroll_action_id;
164
165 lr_parameter_info csr_parameter_info%ROWTYPE;
166 l_proc VARCHAR2 (240)
167 := g_package || ' GET_ALL_PARAMETERS ';
168 BEGIN
169 -- logger ('Entering Procedure ', 'GET_ALL_PARAMETER');
170 -- logger ('p_payroll_action_id', p_payroll_action_id);
171
172 OPEN csr_parameter_info (p_payroll_action_id);
173
174 FETCH csr_parameter_info
175 INTO lr_parameter_info;
176
177 CLOSE csr_parameter_info;
178
179 p_income_statement_provider_id :=
180 lr_parameter_info.income_statement_provider;
181 p_legal_employer_id := lr_parameter_info.legal_employer;
182 p_request_for_all_or_not := lr_parameter_info.request_for;
183 p_person_for := lr_parameter_info.person_for;
184 p_person_number := lr_parameter_info.person_number;
185 p_effective_date := lr_parameter_info.effective_date;
186 p_business_group_id := lr_parameter_info.bg_id;
187 p_income_year := lr_parameter_info.income_year;
188 p_test_or_production := lr_parameter_info.test_production;
189 p_sort_order :=lr_parameter_info.sort_order;
190 -- logger ('p_income_statement_provider_id'
191 -- ,p_income_statement_provider_id);
192 -- logger ('p_legal_employer_id', p_legal_employer_id);
193 -- logger ('p_request_for_all_or_not', p_request_for_all_or_not);
194 -- logger ('p_person_for', p_person_for);
195 -- logger ('p_person_number', p_person_number);
196 -- logger ('p_income_year', p_income_year);
197 -- logger ('p_effective_date', p_effective_date);
198 -- logger ('p_business_group_id', p_business_group_id);
199 -- logger ('p_test_or_production', p_test_or_production);
200
201 IF g_debug
202 THEN
203 hr_utility.set_location (' Leaving Procedure GET_ALL_PARAMETERS'
204 ,30);
205 END IF;
206 END get_all_parameters;
207
208 /* RANGE CODE */
209 PROCEDURE range_code (
210 p_payroll_action_id IN NUMBER
211 ,p_sql OUT NOCOPY VARCHAR2
212 )
213 IS
214 l_action_info_id NUMBER;
215 l_ovn NUMBER;
216 l_business_group_id NUMBER;
217 l_start_date VARCHAR2 (30);
218 l_end_date VARCHAR2 (30);
219 l_effective_date DATE;
220 l_consolidation_set NUMBER;
221 l_defined_balance_id NUMBER := 0;
222 l_count NUMBER := 0;
223 l_prev_prepay NUMBER := 0;
224 l_canonical_start_date DATE;
225 l_canonical_end_date DATE;
226 l_payroll_id NUMBER;
227 l_prepay_action_id NUMBER;
228 l_actid NUMBER;
229 l_assignment_id NUMBER;
230 l_action_sequence NUMBER;
231 l_assact_id NUMBER;
232 l_pact_id NUMBER;
233 l_flag NUMBER := 0;
234 l_element_context VARCHAR2 (5);
235
236 -- Archiving the data , as this will fire once
237 CURSOR csr_check_empty_le (
238 csr_v_legal_employer_id NUMBER
239 ,csr_v_canonical_start_date DATE
240 ,csr_v_canonical_end_date DATE
241 )
242 IS
243 SELECT '1'
244 FROM pay_payroll_actions appa
245 ,pay_assignment_actions act
246 ,per_all_assignments_f as1
247 ,pay_payroll_actions ppa
248 WHERE ppa.payroll_action_id = p_payroll_action_id
249 AND appa.effective_date BETWEEN csr_v_canonical_start_date
250 AND csr_v_canonical_end_date
251 AND appa.action_type IN ('R', 'Q')
252 -- Payroll Run or Quickpay Run
253 AND act.payroll_action_id = appa.payroll_action_id
254 AND act.source_action_id IS NULL -- Master Action
255 AND as1.assignment_id = act.assignment_id
256 AND as1.business_group_id = g_business_group_id
257 AND act.action_status = 'C' -- Completed
258 AND act.tax_unit_id = csr_v_legal_employer_id
259 AND appa.effective_date BETWEEN as1.effective_start_date
260 AND as1.effective_end_date
261 AND ppa.effective_date BETWEEN as1.effective_start_date
262 AND as1.effective_end_date
263 ORDER BY as1.person_id
264 ,act.assignment_id;
265
266 l_le_has_employee VARCHAR2 (2);
267 -- Archiving the data , as this will fire once
268 -- *****************************************************************************
269 -- Variables and cursors for for File INFO.KU (Media Provider Details)
270 -- *****************************************************************************
271 l_product VARCHAR2 (10);
272 l_period VARCHAR2 (10);
273 l_test_or_production VARCHAR2 (10);
274 l_mp_org_number VARCHAR2 (100);
275 l_mp_name VARCHAR2 (240);
276 l_mp_department VARCHAR2 (240);
277 l_mp_contact_person VARCHAR2 (240);
278 l_mp_address VARCHAR2 (240);
279 l_mp_postcode VARCHAR2 (15);
280 l_mp_postal_address VARCHAR2 (50);
281 l_mp_phonenumber VARCHAR2 (15);
282 l_mp_faxnumber VARCHAR2 (15);
283 l_mp_email VARCHAR2 (50);
284 l_location_id VARCHAR2 (100);
285 l_phone_number VARCHAR2 (100);
286 l_location_code VARCHAR2 (100);
287 l_address_line_1 VARCHAR2 (100);
288 l_address_line_2 VARCHAR2 (100);
289 l_address_line_3 VARCHAR2 (100);
290 l_postal_code VARCHAR2 (100);
291 l_town_or_city VARCHAR2 (100);
292 l_region_1 VARCHAR2 (100);
293 l_region_2 VARCHAR2 (100);
294 l_territory_short_name VARCHAR2 (100);
295 l_sender VARCHAR2 (100);
296 l_receiver VARCHAR2 (100);
297 l_information_type VARCHAR2 (100);
298 l_medium_identity varchar2(30);
299 l_medium_program varchar2(30);
300
301 CURSOR csr_media_provider_details (
302 csr_v_media_provider_id hr_all_organization_units.organization_id%TYPE
303 )
304 IS
305 SELECT o.NAME "MP_NAME",o.location_id
306 ,hoi1.org_information1 "MP_ORG_NUMBER"
307 ,hoi1.org_information2 "MP_DEPARTMENT"
308 ,hoi1.org_information3 "MEDIUM_IDENTITY"
309 ,hoi1.org_information4 "PROGRAM"
310 FROM hr_all_organization_units o
311 ,hr_organization_information hoi
312 ,hr_organization_information hoi1
313 WHERE o.business_group_id = g_business_group_id
314 AND o.organization_id = hoi.organization_id
315 AND hoi.org_information_context = 'CLASS'
316 AND hoi.org_information1 = 'SE_INC_STMT_PROVIDER'
317 AND o.organization_id = hoi1.organization_id
318 AND hoi1.org_information_context = 'SE_INC_STMT_PROVIDER_DETAILS'
319 AND o.organization_id = csr_v_media_provider_id;
320
321 lr_media_provider_details csr_media_provider_details%ROWTYPE;
322
323 CURSOR csr_org_contacts (
324 csr_v_media_provider_id hr_organization_information.organization_id%TYPE
325 ,csr_v_type hr_organization_information.org_information1%TYPE
326 )
327 IS
328 SELECT hoi22.org_information1
329 ,hoi22.org_information2
330 ,hoi22.org_information3
331 ,hoi22.org_information_id
332 FROM hr_organization_information hoi11
333 ,hr_organization_information hoi22
334 WHERE hoi11.organization_id = csr_v_media_provider_id
335 AND hoi11.org_information_context = 'CLASS'
336 AND hoi11.org_information1 = 'SE_INC_STMT_PROVIDER'
337 AND hoi22.organization_id = hoi11.organization_id
338 AND hoi22.org_information_context = 'SE_ORG_CONTACT_DETAILS'
339 AND hoi22.org_information1 = csr_v_type
340 AND ROWNUM < 2
341 ORDER BY hoi22.org_information_id ASC;
342
343 lr_org_contacts csr_org_contacts%ROWTYPE;
344
345 CURSOR csr_legal_employer_details (
346 csr_v_legal_employer_id hr_organization_information.organization_id%TYPE
347 ,csr_v_media_provider_id hr_organization_information.organization_id%TYPE
348 )
349 IS
350 /* select o1.NAME legal_employer_name
351 , hoi2.org_information2 org_number
352 , hoi1.organization_id legal_id
353 FROM hr_organization_units o1
354 , hr_organization_information hoi1
355 , hr_organization_information hoi2
356 , hr_organization_units o2
357 , hr_organization_information hoi3
358 WHERE o1.business_group_id = g_business_group_id
359 AND hoi1.organization_id = o1.organization_id
360 AND hoi1.organization_id = NVL(csr_v_legal_employer_id,hoi1.organization_id)
361 AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
362 AND hoi1.org_information_context = 'CLASS'
363 AND o1.organization_id = hoi2.organization_id
364 AND hoi2.org_information_context = 'SE_LEGAL_EMPLOYER_DETAILS'
365 AND o2.organization_id = csr_v_media_provider_id
366 AND hoi3.org_information_context = 'SE_LEGAL_EMPLOYERS'
367 and hoi3.org_information1 = o1.organization_id;*/
368 SELECT o1.NAME legal_employer_name
369 ,o1.location_id
370 ,hoi2.org_information2 org_number
371 ,hoi1.organization_id legal_id
372 FROM hr_organization_units o1
373 ,hr_organization_information hoi1
374 ,hr_organization_information hoi2
375 ,hr_organization_information hoi3
376 WHERE o1.business_group_id = g_business_group_id
377 AND hoi1.organization_id = o1.organization_id
378 AND hoi1.organization_id =
379 NVL (csr_v_legal_employer_id, hoi1.organization_id)
380 AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
381 AND hoi1.org_information_context = 'CLASS'
382 AND o1.organization_id = hoi2.organization_id
383 AND hoi2.org_information_context = 'SE_LEGAL_EMPLOYER_DETAILS'
384 AND o1.organization_id = hoi3.organization_id
385 AND hoi3.org_information_context = 'SE_INC_STMT_PROVIDERS'
386 AND hoi3.org_information1 = csr_v_media_provider_id;
387
388 l_legal_employer_details csr_legal_employer_details%ROWTYPE;
389
390 CURSOR csr_address_details (
391 csr_v_location_id hr_locations.location_id%TYPE
392 )
393 IS
394 SELECT hl.location_code
395 ,hl.description
396 ,hl.address_line_1
397 ,hl.address_line_2
398 ,hl.address_line_3
399 ,hl.postal_code
400 ,hl.town_or_city
401 ,hl.region_1
402 ,hl.region_2
403 ,ft.territory_short_name
404 FROM hr_organization_units hou
405 ,hr_locations hl
406 ,fnd_territories_vl ft
407 WHERE hl.location_id = csr_v_location_id
408 AND hl.country = ft.territory_code;
409
410 lr_address_details csr_address_details%ROWTYPE;
411
412 CURSOR csr_post_header
413 IS
414 SELECT ORG_INFORMATION1,ORG_INFORMATION2,ORG_INFORMATION3
415 FROM hr_organization_information
416 WHERE organization_id = g_business_group_id
417 AND org_information_context = 'SE_POST_HEADER_INFO';
418
419 lr_post_header csr_post_header%ROWTYPE;
420 -- *****************************************************************************
421 BEGIN
422 --logger ('Range Code ', '=====> Started');
423
424 IF g_debug
425 THEN
426 hr_utility.set_location (' Entering Procedure RANGE_CODE', 40);
427 END IF;
428
429 p_sql :=
430 'SELECT DISTINCT person_id
431 FROM per_people_f ppf
432 ,pay_payroll_actions ppa
433 WHERE ppa.payroll_action_id = :payroll_action_id
434 AND ppa.business_group_id = ppf.business_group_id
435 ORDER BY ppf.person_id';
436 g_payroll_action_id := p_payroll_action_id;
437 g_business_group_id := NULL;
438 g_effective_date := NULL;
439 g_income_statement_provider_id := NULL;
440 g_legal_employer_id := NULL;
441 g_local_unit_id := NULL;
442 g_income_year := NULL;
443 g_person_for := NULL;
444 g_person_number := NULL;
445 pay_se_income_statement.get_all_parameters
446 (p_payroll_action_id
447 ,g_business_group_id
448 ,g_effective_date
449 ,g_income_statement_provider_id
450 ,g_request_for
451 ,g_legal_employer_id
452 ,g_income_year
453 ,g_person_for
454 ,g_person_number
455 ,g_sort_order
456 ,g_test_or_production
457 );
458 --logger ('Parameters are ', '=====');
459 --logger ('p_payroll_action_id ', p_payroll_action_id);
460 --logger ('g_business_group_id ', g_business_group_id);
461 --logger ('g_effective_date ', g_effective_date);
462 --logger ('g_income_statement_provider_id ' ,g_income_statement_provider_id );
463 --logger ('g_request_for ', g_request_for);
464 --logger ('g_legal_employer_id ', g_legal_employer_id);
465 --logger ('g_income_year ', g_income_year);
466 --logger ('g_person_from ', g_person_for);
467 --logger ('g_person_to ', g_person_number);
468 --logger ('g_test_or_production ', g_test_or_production);
469 g_income_start_date := TO_DATE ('01-01-' || g_income_year, 'DD-MM-YYYY');
470 g_income_end_date := TO_DATE ('31-12-' || g_income_year, 'DD-MM-YYYY');
471 --logger ('g_income_start_date ', g_income_start_date);
472 --logger ('g_income_end_date ', g_income_end_date);
473 -- *****************************************************************************
474 -- To pick up the details for File INFO.KU (Media Provider Details)
475 -- *****************************************************************************
476 l_product := TO_CHAR (g_effective_date, 'YYYY');
477 l_period := g_income_year;
478 l_test_or_production := g_test_or_production;
479
480 OPEN csr_media_provider_details (g_income_statement_provider_id);
481
482 FETCH csr_media_provider_details
483 INTO lr_media_provider_details;
484
485 CLOSE csr_media_provider_details;
486
487 l_mp_name := lr_media_provider_details.mp_name;
488 l_mp_org_number := lr_media_provider_details.mp_org_number;
489 l_mp_department := lr_media_provider_details.mp_department;
490 l_medium_identity := lr_MEDIA_PROVIDER_DETAILS.MEDIUM_IDENTITY;
491 l_medium_program := lr_MEDIA_PROVIDER_DETAILS.PROGRAM;
492
493 OPEN csr_address_details (lr_MEDIA_PROVIDER_DETAILS.location_id);
494
495 FETCH csr_address_details
496 INTO lr_address_details;
497
498 CLOSE csr_address_details;
499
500
501 IF lr_address_details.location_code IS NOT NULL
502 THEN
503 l_mp_address := lr_address_details.location_code ;
504 END IF;
505
506 IF lr_address_details.address_line_1 IS NOT NULL
507 THEN
508 l_mp_address := l_mp_address||' '||lr_address_details.address_line_1;
509 END IF;
510
511 IF lr_address_details.address_line_2 IS NOT NULL
512 THEN
513 l_mp_address := l_mp_address||' '||lr_address_details.address_line_2;
514 END IF;
515
516 IF lr_address_details.address_line_3 IS NOT NULL
517 THEN
518 l_mp_address := l_mp_address||' '||lr_address_details.address_line_3;
519 END IF;
520
521 l_mp_postcode := lr_address_details.postal_code ;
522
523 IF lr_address_details.town_or_city IS NOT NULL
524 THEN
525 l_mp_postal_address := l_mp_postal_address||' '||lr_address_details.town_or_city;
526 END IF;
527
528 IF lr_address_details.region_1 IS NOT NULL
529 THEN
530 l_mp_postal_address := l_mp_postal_address||' '||lr_address_details.region_1;
531 END IF;
532
533 IF lr_address_details.region_2 IS NOT NULL
534 THEN
535 l_mp_postal_address := l_mp_postal_address||' '||lr_address_details.region_2;
536 END IF;
537
538 IF lr_address_details.territory_short_name IS NOT NULL
539 THEN
540 l_mp_postal_address := l_mp_postal_address||' '||lr_address_details.territory_short_name;
541 END IF;
542
543 lr_address_details := null;
544
545 --logger ('l_mp_name ', l_mp_name);
546 --logger ('l_mp_org_number ', l_mp_org_number);
547 --logger ('l_mp_department ', l_mp_department);
548 --logger ('l_medium_identity ', l_medium_identity );
549 --logger ('l_medium_program ', l_medium_program );
550
551 --logger ('l_mp_address ', l_mp_address );
552 --logger ('l_mp_postcode ', l_mp_postcode );
553 --logger ('l_mp_postal_address ', l_mp_postal_address );
554
555 lr_org_contacts := NULL;
556
557 OPEN csr_org_contacts (g_income_statement_provider_id, 'PERSON');
558
559 FETCH csr_org_contacts
560 INTO lr_org_contacts;
561
562 CLOSE csr_org_contacts;
563
564 l_mp_contact_person := lr_org_contacts.org_information3;
565 lr_org_contacts := NULL;
566
567 OPEN csr_org_contacts (g_income_statement_provider_id, 'PHONE');
568
569 FETCH csr_org_contacts
570 INTO lr_org_contacts;
571
572 CLOSE csr_org_contacts;
573
574 l_mp_phonenumber := lr_org_contacts.org_information3;
575 lr_org_contacts := NULL;
576
577 OPEN csr_org_contacts (g_income_statement_provider_id, 'EMAIL');
578
579 FETCH csr_org_contacts
580 INTO lr_org_contacts;
581
582 CLOSE csr_org_contacts;
583
584 l_mp_email := lr_org_contacts.org_information3;
585 lr_org_contacts := NULL;
586
587 OPEN csr_org_contacts (g_income_statement_provider_id, 'FAX');
588
589 FETCH csr_org_contacts
590 INTO lr_org_contacts;
591
592 CLOSE csr_org_contacts;
593
594 l_mp_faxnumber := lr_org_contacts.org_information3;
595 lr_org_contacts := NULL;
596 --logger ('l_mp_contact_person ', l_mp_contact_person);
597 --logger ('l_mp_phonenumber ', l_mp_phonenumber);
598 --logger ('l_mp_email ', l_mp_email);
599 --logger ('l_mp_faxnumber ', l_mp_faxnumber);
600 -- *****************************************************************************
601 OPEN csr_post_header ;
602 FETCH csr_post_header INTO lr_post_header;
603 CLOSE csr_post_header;
604 l_sender := lr_post_header.org_information1;
605 l_receiver := lr_post_header.org_information2;
606 l_information_type := lr_post_header.org_information3;
607
608 -- Insert the report Parameters
609 pay_action_information_api.create_action_information
610 (p_action_information_id => l_action_info_id
611 ,p_action_context_id => p_payroll_action_id
612 ,p_action_context_type => 'PA'
613 ,p_object_version_number => l_ovn
614 ,p_effective_date => g_effective_date
615 ,p_source_id => NULL
616 ,p_source_text => NULL
617 ,p_action_information_category => 'EMEA REPORT DETAILS'
618 ,p_action_information1 => 'PYSEINSA'
619 ,p_action_information2 => g_income_statement_provider_id
620 ,p_action_information3 => g_request_for
621 ,p_action_information4 => g_legal_employer_id
622 ,p_action_information5 => g_person_for
623 ,p_action_information6 => g_person_number
624 ,p_action_information7 => g_income_year
625 ,p_action_information8 => g_business_group_id
626 ,p_action_information9 => g_test_or_production
627 ,p_action_information10 => g_sort_order
628 ,p_action_information11 => l_sender
629 ,p_action_information12 => l_receiver
630 ,p_action_information13 => l_information_type
631 );
632 -- *****************************************************************************
633 -- Insert for Media Provider Details
634 -- *****************************************************************************
635 -- *****************************************************************************
636 pay_action_information_api.create_action_information
637 (p_action_information_id => l_action_info_id
638 ,p_action_context_id => p_payroll_action_id
639 ,p_action_context_type => 'PA'
640 ,p_object_version_number => l_ovn
641 ,p_effective_date => g_effective_date
642 ,p_source_id => NULL
643 ,p_source_text => NULL
644 ,p_action_information_category => 'EMEA REPORT INFORMATION'
645 ,p_action_information1 => 'PYSEINSA'
646 ,p_action_information2 => 'MP'
647 ,p_action_information3 => l_product
648 ,p_action_information4 => l_period
649 ,p_action_information5 => l_test_or_production
650 ,p_action_information6 => l_mp_org_number
651 ,p_action_information7 => l_mp_name
652 ,p_action_information8 => l_mp_department
653 ,p_action_information9 => l_mp_contact_person
654 ,p_action_information10 => l_mp_address
655 ,p_action_information11 => l_mp_postcode
656 ,p_action_information12 => l_mp_postal_address
657 ,p_action_information13 => l_mp_phonenumber
658 ,p_action_information14 => l_mp_faxnumber
659 ,p_action_information15 => l_mp_email
660 , p_action_information16 => l_medium_identity
661 , p_action_information17 => l_medium_program
662
663 );
664
665 -- *****************************************************************************
666 -- *****************************************************************************
667 --Insert for LE or ALL LE
668 -- *****************************************************************************
669 -- *****************************************************************************
670 IF g_request_for = 'REQUESTING_ORG'
671 THEN
672 -- Information regarding the Legal Employer
673 OPEN csr_legal_employer_details (g_legal_employer_id
674 ,g_income_statement_provider_id
675 );
676
677 FETCH csr_legal_employer_details
678 INTO l_legal_employer_details;
679
680 CLOSE csr_legal_employer_details;
681
682 l_location_id := l_legal_employer_details.location_id;
683 lr_address_details := NULL;
684 --logger ('l_location_id', l_location_id);
685
686 OPEN csr_address_details (l_location_id);
687
688 FETCH csr_address_details
689 INTO lr_address_details;
690
691 CLOSE csr_address_details;
692
693 l_location_code := lr_address_details.location_code;
694 l_address_line_1 := lr_address_details.address_line_1;
695 l_address_line_2 := lr_address_details.address_line_2;
696 l_address_line_3 := lr_address_details.address_line_3;
697 l_postal_code := lr_address_details.postal_code;
698 l_town_or_city := lr_address_details.town_or_city;
699 l_region_1 := lr_address_details.region_1;
700 l_region_2 := lr_address_details.region_2;
701 l_territory_short_name := lr_address_details.territory_short_name;
702 --logger ('l_location_code', l_location_code);
703 --logger ('l_address_line_1', l_address_line_1);
704 --logger ('l_address_line_2', l_address_line_2);
705 --logger ('l_address_line_3', l_address_line_3);
706 --logger ('l_postal_code', l_postal_code);
707 --logger ('l_TOWN_OR_CITY', l_town_or_city);
708 --logger ('l_REGION_1', l_region_1);
709 --logger ('l_REGION_2', l_region_2);
710 --logger ('l_TERRITORY_SHORT_NAME', l_territory_short_name);
711 pay_action_information_api.create_action_information
712 (p_action_information_id => l_action_info_id
713 ,p_action_context_id => p_payroll_action_id
714 ,p_action_context_type => 'PA'
715 ,p_object_version_number => l_ovn
716 ,p_effective_date => g_effective_date
717 ,p_source_id => NULL
718 ,p_source_text => NULL
719 ,p_action_information_category => 'EMEA REPORT INFORMATION'
720 ,p_action_information1 => 'PYSEINSA'
721 ,p_action_information2 => 'LE'
722 ,p_action_information3 => g_legal_employer_id
723 ,p_action_information4 => l_legal_employer_details.legal_employer_name
724 ,p_action_information5 => l_legal_employer_details.org_number
725 ,p_action_information6 => l_location_code
726 ,p_action_information7 => l_address_line_1
727 ,p_action_information8 => l_address_line_2
728 ,p_action_information9 => l_address_line_3
729 ,p_action_information10 => l_postal_code
730 ,p_action_information11 => l_town_or_city
731 ,p_action_information12 => l_region_1
732 ,p_action_information13 => l_region_2
733 ,p_action_information14 => l_territory_short_name
734 ,p_action_information15 => NULL
735 );
736 -- *****************************************************************************
737 ELSE
738 -- *****************************************************************************
739 FOR rec_legal_employer_details IN
740 csr_legal_employer_details (NULL, g_income_statement_provider_id)
741 LOOP
742 l_location_id := rec_legal_employer_details.location_id;
743 --logger ('l_location_id', l_location_id);
744 lr_address_details := NULL;
745
746 OPEN csr_address_details (l_location_id);
747
748 FETCH csr_address_details
749 INTO lr_address_details;
750
751 CLOSE csr_address_details;
752
753 l_location_code := lr_address_details.location_code;
754 l_address_line_1 := lr_address_details.address_line_1;
755 l_address_line_2 := lr_address_details.address_line_2;
756 l_address_line_3 := lr_address_details.address_line_3;
757 l_postal_code := lr_address_details.postal_code;
758 l_town_or_city := lr_address_details.town_or_city;
759 l_region_1 := lr_address_details.region_1;
760 l_region_2 := lr_address_details.region_2;
761 l_territory_short_name := lr_address_details.territory_short_name;
762 --logger ('l_location_code', l_location_code);
763 --logger ('l_address_line_1', l_address_line_1);
764 --logger ('l_address_line_2', l_address_line_2);
765 --logger ('l_address_line_3', l_address_line_3);
766 --logger ('l_postal_code', l_postal_code);
767 --logger ('l_TOWN_OR_CITY', l_town_or_city);
768 --logger ('l_REGION_1', l_region_1);
769 --logger ('l_REGION_2', l_region_2);
770 --logger ('l_TERRITORY_SHORT_NAME', l_territory_short_name);
771 pay_action_information_api.create_action_information
772 (p_action_information_id => l_action_info_id
773 ,p_action_context_id => p_payroll_action_id
774 ,p_action_context_type => 'PA'
775 ,p_object_version_number => l_ovn
776 ,p_effective_date => g_effective_date
777 ,p_source_id => NULL
778 ,p_source_text => NULL
779 ,p_action_information_category => 'EMEA REPORT INFORMATION'
780 ,p_action_information1 => 'PYSEINSA'
781 ,p_action_information2 => 'LE'
782 ,p_action_information3 => rec_legal_employer_details.legal_id
783 ,p_action_information4 => rec_legal_employer_details.legal_employer_name
784 ,p_action_information5 => rec_legal_employer_details.org_number
785 ,p_action_information6 => l_location_code
786 ,p_action_information7 => l_address_line_1
787 ,p_action_information8 => l_address_line_2
788 ,p_action_information9 => l_address_line_3
789 ,p_action_information10 => l_postal_code
790 ,p_action_information11 => l_town_or_city
791 ,p_action_information12 => l_region_1
792 ,p_action_information13 => l_region_2
793 ,p_action_information14 => l_territory_short_name
794 );
795 END LOOP;
796 END IF; -- FOR G_LEGAL_EMPLOYER_ID
797
798 -- *****************************************************************************
799 --END OF Insert for LE or ALL LE
800 -- *****************************************************************************
801 IF g_debug
802 THEN
803 hr_utility.set_location (' Leaving Procedure RANGE_CODE', 50);
804 END IF;
805 EXCEPTION
806 WHEN OTHERS
807 THEN
808 -- Return cursor that selects no rows
809 p_sql :=
810 'select 1 from dual where to_char(:payroll_action_id) = dummy';
811 END range_code;
812
813 /* ASSIGNMENT ACTION CODE */
814 PROCEDURE assignment_action_code (
815 p_payroll_action_id IN NUMBER
816 ,p_start_person IN NUMBER
817 ,p_end_person IN NUMBER
818 ,p_chunk IN NUMBER
819 )
820 IS
821 CURSOR csr_prepaid_assignments_le (
822 p_payroll_action_id NUMBER
823 ,p_start_person NUMBER
824 ,p_end_person NUMBER
825 ,p_legal_employer_id NUMBER
826 ,l_canonical_start_date DATE
827 ,l_canonical_end_date DATE
828 )
829 IS
830 SELECT as1.person_id person_id
831 ,act.assignment_id assignment_id
832 ,act.assignment_action_id run_action_id
833 ,act.tax_unit_id legal_employer_id
834 FROM pay_payroll_actions appa
835 ,pay_assignment_actions act
836 ,per_all_assignments_f as1
837 ,pay_payroll_actions ppa
838 WHERE ppa.payroll_action_id = p_payroll_action_id
839 AND appa.effective_date BETWEEN l_canonical_start_date
840 AND l_canonical_end_date
841 AND as1.person_id BETWEEN p_start_person AND p_end_person
842 AND appa.action_type IN ('R', 'Q')
843 -- Payroll Run or Quickpay Run
844 AND act.payroll_action_id = appa.payroll_action_id
845 AND act.source_action_id IS NULL -- Master Action
846 AND as1.assignment_id = act.assignment_id
847 AND as1.business_group_id = g_business_group_id
848 AND act.action_status = 'C' -- Completed
849 AND appa.effective_date BETWEEN as1.effective_start_date
850 AND as1.effective_end_date
851 -- AND ppa.effective_date BETWEEN as1.effective_start_date
852 -- AND as1.effective_end_date
853 AND act.tax_unit_id IN (
854 SELECT o.organization_id
855 FROM hr_all_organization_units o
856 ,hr_organization_information hoi1
857 ,hr_organization_information hoi2
858 WHERE o.business_group_id = g_business_group_id
859 AND hoi1.organization_id = o.organization_id
860 AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
861 AND hoi1.org_information_context = 'CLASS'
862 AND o.organization_id = hoi2.organization_id
863 AND hoi2.org_information_context =
864 'SE_INC_STMT_PROVIDERS'
865 AND hoi2.org_information1 =
866 g_income_statement_provider_id)
867 ORDER BY as1.person_id
868 ,act.assignment_id;
869
870 CURSOR csr_get_defined_balance_id (
871 csr_v_balance_name ff_database_items.user_name%TYPE
872 )
873 IS
874 SELECT ue.creator_id
875 FROM ff_user_entities ue
876 ,ff_database_items di
877 WHERE di.user_name = csr_v_balance_name
878 AND ue.user_entity_id = di.user_entity_id
879 AND ue.legislation_code = 'SE'
880 AND ue.business_group_id IS NULL
881 AND ue.creator_type = 'B';
882
883 lr_get_defined_balance_id csr_get_defined_balance_id%ROWTYPE;
884 l_count NUMBER := 0;
885 l_prev_prepay NUMBER := 0;
886 l_canonical_start_date DATE;
887 l_canonical_end_date DATE;
888 l_pension_type hr_organization_information.org_information1%TYPE;
889 l_prepay_action_id NUMBER;
890 l_actid NUMBER;
891 l_assignment_id NUMBER;
892 l_action_sequence NUMBER;
893 l_assact_id NUMBER;
894 l_pact_id NUMBER;
895 l_flag NUMBER := 0;
896 l_defined_balance_id NUMBER := 0;
897 l_action_info_id NUMBER;
898 l_ovn NUMBER;
899 -- User pARAMETERS needed
900 l_business_group_id NUMBER;
901 l_effective_date DATE;
902 l_pension_provider_id NUMBER;
903 l_legal_employer_id NUMBER;
904 l_person_id NUMBER;
905 l_archive VARCHAR2 (10);
906
907 l_a_tax_card_or_not NUMBER;
908 -- End of User pARAMETERS needed
909
910 /* GET THE PERSON NUMBE VALIDATED */
911 FUNCTION validate_person_number (
912 val_person_id IN number,
913 val_person_number IN number
914 )
915 RETURN varchar2
916 IS
917 /* Cursor to retrieve Defined Balance Id */
918 CURSOR csr_check_person_number
919 IS
920 SELECT count('1') "VALID" from per_all_people_f pap
921 where pap.person_id=val_person_id
922 AND pap.EMPLOYEE_NUMBER=val_person_number
923 AND pap.EFFECTIVE_START_DATE <= g_income_end_date
924 AND pap.EFFECTIVE_END_DATE > = g_income_start_date
925 ;
926 lr_check_person_number csr_check_person_number%rowtype;
927
928 BEGIN
929
930 OPEN csr_check_person_number;
931 FETCH csr_check_person_number INTO lr_check_person_number;
932 CLOSE csr_check_person_number;
933 IF lr_check_person_number.valid > 0
934 THEN
935 RETURN 'VALID';
936 ELSE
937 RETURN NULL;
938 END IF;
939 END validate_person_number;
940
941
942
943 /* Proc to check A tax card */
944 PROCEDURE check_a_taxcard (
945 p_l_person_id IN NUMBER
946 ,p_return_count_value OUT NOCOPY NUMBER
947 )
948 IS
949 CURSOR csr_get_prim_assignments (csr_v_person_id NUMBER)
950 IS
951 SELECT paa.assignment_id
952 ,paa.effective_start_date
953 ,paa.effective_end_date
954 ,scl.segment2
955 FROM per_all_assignments_f paa
956 ,hr_soft_coding_keyflex scl
957 WHERE person_id = csr_v_person_id
958 AND paa.effective_start_date <= g_income_end_date
959 AND paa.effective_end_date > = g_income_start_date
960 AND paa.primary_flag = 'Y'
961 AND paa.assignment_status_type_id IN (
962 SELECT assignment_status_type_id
963 FROM per_assignment_status_types
964 WHERE per_system_status = 'ACTIVE_ASSIGN'
965 AND active_flag = 'Y'
966 AND ( ( legislation_code IS NULL
967 AND business_group_id IS NULL
968 )
969 OR (business_group_id = g_business_group_id)
970 ))
971 AND scl.soft_coding_keyflex_id = paa.soft_coding_keyflex_id;
972
973 lr_get_prim_assignments csr_get_prim_assignments%ROWTYPE;
974
975 CURSOR csr_get_element_ids
976 IS
977 SELECT pet.element_type_id
978 ,piv.input_value_id
979 ,pel.element_link_id
980 FROM pay_element_types_f pet
981 ,pay_input_values_f piv
982 ,pay_element_links_f pel
983 WHERE pet.element_name = 'Tax Card'
984 AND pet.legislation_code = 'SE'
985 AND piv.element_type_id = pet.element_type_id
986 AND piv.NAME = 'Tax Card Type'
987 AND pel.element_type_id = pet.element_type_id
988 AND pel.business_group_id = g_business_group_id
989 AND pet.effective_start_date <= g_income_end_date
990 AND pet.effective_end_date > = g_income_start_date
991 AND piv.effective_start_date <= g_income_end_date
992 AND piv.effective_end_date > = g_income_start_date
993 AND pel.effective_start_date <= g_income_end_date
994 AND pel.effective_end_date > = g_income_start_date;
995
996 lr_get_element_ids csr_get_element_ids%ROWTYPE;
997
998 CURSOR csr_chk_a_taxcard (
999 csr_v_input_value_id pay_element_entry_values_f.input_value_id%TYPE
1000 ,csr_v_link_id pay_element_entries_f.element_link_id%TYPE
1001 ,csr_v_type_id pay_element_entries_f.element_type_id%TYPE
1002 ,csr_v_prim_assignment_id NUMBER
1003 )
1004 IS
1005 SELECT COUNT ('Y') valid
1006 FROM pay_element_entries_f pee
1007 ,pay_element_entry_values_f peev
1008 WHERE peev.screen_entry_value = 'A'
1009 AND peev.element_entry_id = pee.element_entry_id
1010 AND peev.effective_start_date = pee.effective_start_date
1011 AND peev.effective_end_date = pee.effective_end_date
1012 AND peev.input_value_id = csr_v_input_value_id
1013 --AND pee.element_link_id = csr_v_link_id
1014 AND pee.element_type_id = csr_v_type_id
1015 AND pee.assignment_id = csr_v_prim_assignment_id
1016 AND pee.effective_start_date <= g_income_end_date
1017 AND pee.effective_end_date > = g_income_start_date;
1018
1019 lr_chk_a_taxcard csr_chk_a_taxcard%ROWTYPE;
1020
1021 -- *****************************************************************************
1022 -- Income Statement Specification Details
1023 CURSOR csr_person_inc_stmt_spec (
1024 csr_v_person_id NUMBER
1025 ,csr_v_information_type per_people_extra_info.information_type%TYPE
1026 )
1027 IS
1028 SELECT pei_information1
1029 ,pei_information2
1030 ,pei_information3
1031 ,pei_information4
1032 ,pei_information5
1033 ,pei_information6
1034 ,pei_information7
1035 ,pei_information8
1036 ,pei_information9
1037 FROM per_people_extra_info
1038 WHERE person_id = csr_v_person_id
1039 AND information_type = csr_v_information_type;
1040
1041 lr_person_inc_stmt_spec csr_person_inc_stmt_spec%ROWTYPE;
1042 -- *****************************************************************************
1043 BEGIN
1044 -- *****************************************************************************
1045 lr_person_inc_stmt_spec := NULL;
1046
1047 OPEN csr_person_inc_stmt_spec (p_l_person_id
1048 ,'SE_INC_STMT_DATA_CORRECTION'
1049 );
1050
1051 FETCH csr_person_inc_stmt_spec
1052 INTO lr_person_inc_stmt_spec;
1053
1054 CLOSE csr_person_inc_stmt_spec;
1055
1056 --logger ('lr_Person_inc_stmt_spec.PEI_INFORMATION1' ,lr_person_inc_stmt_spec.pei_information1 );
1057
1058 IF lr_person_inc_stmt_spec.pei_information1 = 'NA'
1059 THEN
1060 p_return_count_value := 0;
1061 --logger ('NA. not VALID', p_return_count_value);
1062 ELSIF lr_person_inc_stmt_spec.pei_information1 IS NULL
1063 THEN
1064 OPEN csr_get_prim_assignments (p_l_person_id);
1065
1066 FETCH csr_get_prim_assignments
1067 INTO lr_get_prim_assignments;
1068
1069 CLOSE csr_get_prim_assignments;
1070
1071 OPEN csr_get_element_ids;
1072
1073 FETCH csr_get_element_ids
1074 INTO lr_get_element_ids;
1075
1076 CLOSE csr_get_element_ids;
1077
1078 lr_chk_a_taxcard := NULL;
1079
1080 OPEN csr_chk_a_taxcard (lr_get_element_ids.input_value_id
1081 ,lr_get_element_ids.element_link_id
1082 ,lr_get_element_ids.element_type_id
1083 ,lr_get_prim_assignments.assignment_id
1084 );
1085
1086 FETCH csr_chk_a_taxcard
1087 INTO lr_chk_a_taxcard;
1088
1089 CLOSE csr_chk_a_taxcard;
1090
1091 --logger ('lr_chk_A_taxcard.VALID', lr_chk_a_taxcard.valid);
1092 p_return_count_value := lr_chk_a_taxcard.valid;
1093 ELSE -- if the value has been entereed as KU10, KU13, KU14
1094 -- then dont check this tax card
1095 p_return_count_value := 1;
1096 --logger ('Else .VALID', p_return_count_value);
1097 END IF;
1098 END check_a_taxcard;
1099 /* End of Proc to Add the tag value and Name */
1100 BEGIN
1101
1102
1103 --logger ('ASSIGNMENT_ACTION_CODE ', '--------------------------------- Started');
1104
1105 pay_se_income_statement.get_all_parameters (p_payroll_action_id
1106 , g_business_group_id
1107 , g_effective_date
1108 , g_income_statement_provider_id
1109 , g_request_for
1110 , g_legal_employer_id
1111 , g_income_year
1112 , g_person_for
1113 , g_person_number
1114 , g_sort_order
1115 , g_test_or_production
1116 );
1117
1118 l_canonical_start_date := NULL;
1119 l_canonical_end_date := NULL;
1120 l_prepay_action_id := 0;
1121 /*
1122 logger ('p_payroll_action_id ', p_payroll_action_id );
1123 logger ('g_business_group_id ', g_business_group_id );
1124 logger ('g_effective_date ', g_effective_date );
1125 logger ('g_income_statement_provider_id ', g_income_statement_provider_id );
1126 logger ('g_request_for ', g_request_for );
1127 logger ('g_legal_employer_id ', g_legal_employer_id );
1128 logger ('g_income_year ', g_income_year );
1129 logger ('g_person_from ', g_person_for );
1130 logger ('g_person_to ', g_person_number );
1131 logger ('g_test_or_production ', g_test_or_production );
1132 */
1133 g_income_start_date := TO_DATE ('01-01-' || g_income_year, 'DD-MM-YYYY');
1134 g_income_end_date := TO_DATE ('31-12-' || g_income_year, 'DD-MM-YYYY');
1135
1136 l_canonical_start_date := g_income_start_date;
1137 l_canonical_end_date := g_income_end_date;
1138 --logger ('l_canonical_start_date ', l_canonical_start_date );
1139 --logger ('l_canonical_end_date ', l_canonical_end_date );
1140 l_assignment_id := 0;
1141 l_legal_employer_id := 0;
1142 l_person_id := 0;
1143
1144 IF g_person_for = 'PER_ALL'
1145 THEN
1146 IF g_request_for <>'REQUESTING_ORG'
1147 THEN
1148 FOR rec_prepaid_assignments IN
1149 csr_prepaid_assignments_le (p_payroll_action_id
1150 ,p_start_person
1151 ,p_end_person
1152 ,g_legal_employer_id
1153 ,l_canonical_start_date
1154 ,l_canonical_end_date
1155 )
1156 LOOP
1157 --logger ('FOR ALL EMP ', 'FOR ALL LE');
1158 --logger ('rec_prepaid_assignments.person_id ' ,rec_prepaid_assignments.person_id );
1159 --logger ('rec_prepaid_assignments.LEGAL_EMPLOYER_ID ' ,rec_prepaid_assignments.legal_employer_id );
1160 --logger ('Person ID ', l_person_id);
1161 --logger ('Legal Employer id ', l_legal_employer_id);
1162
1163 IF ( l_person_id <> rec_prepaid_assignments.person_id
1164 or l_legal_employer_id <>
1165 rec_prepaid_assignments.legal_employer_id
1166 )
1167 THEN
1168 --logger ('Passed ', '+-+-+-+-');
1169 l_a_tax_card_or_not := NULL;
1170 check_a_taxcard (rec_prepaid_assignments.person_id
1171 ,l_a_tax_card_or_not
1172 );
1173
1174 IF l_a_tax_card_or_not > 0
1175 THEN
1176 SELECT pay_assignment_actions_s.NEXTVAL
1177 INTO l_actid
1178 FROM DUAL;
1179
1180 -- Create the archive assignment action
1181 hr_nonrun_asact.insact
1182 (l_actid
1183 ,rec_prepaid_assignments.assignment_id
1184 ,p_payroll_action_id
1185 ,p_chunk
1186 ,NULL
1187 );
1188 -- Create archive to prepayment assignment action interlock
1189 --
1190 --hr_nonrun_asact.insint(l_actid,rec_prepaid_assignments.prepaid_action_id);
1191 END IF;
1192 l_assignment_id := rec_prepaid_assignments.assignment_id;
1193 l_legal_employer_id := rec_prepaid_assignments.legal_employer_id;
1194 l_person_id := rec_prepaid_assignments.person_id;
1195
1196 END IF;
1197
1198 -- create archive to master assignment action interlock
1199 --hr_nonrun_asact.insint(l_actid,rec_prepaid_assignments.run_action_id);
1200 END LOOP;
1201 ELSE
1202 --logger ('FOR ALL EMP ', 'UNDER GIVEN LE');
1203 FOR rec_prepaid_assignments IN
1204 csr_prepaid_assignments_le (p_payroll_action_id
1205 ,p_start_person
1206 ,p_end_person
1207 ,g_legal_employer_id
1208 ,l_canonical_start_date
1209 ,l_canonical_end_date
1210 )
1211 LOOP
1212
1213 --logger ('rec_prepaid_assignments.person_id ' ,rec_prepaid_assignments.person_id );
1214 --logger ('rec_prepaid_assignments.LEGAL_EMPLOYER_ID ' ,rec_prepaid_assignments.legal_employer_id );
1215 --logger ('Person ID ', l_person_id);
1216 --logger ('Legal Employer id ', l_legal_employer_id);
1217
1218 IF ( l_person_id <> rec_prepaid_assignments.person_id
1219 AND rec_prepaid_assignments.legal_employer_id = g_legal_employer_id
1220 )
1221 THEN
1222 --logger ('Passed ', '+-+-+-+-');
1223 l_a_tax_card_or_not := NULL;
1224 check_a_taxcard (rec_prepaid_assignments.person_id
1225 ,l_a_tax_card_or_not
1226 );
1227
1228 IF l_a_tax_card_or_not > 0
1229 THEN
1230 SELECT pay_assignment_actions_s.NEXTVAL
1231 INTO l_actid
1232 FROM DUAL;
1233
1234 -- Create the archive assignment action
1235 hr_nonrun_asact.insact
1236 (l_actid
1237 ,rec_prepaid_assignments.assignment_id
1238 ,p_payroll_action_id
1239 ,p_chunk
1240 ,NULL
1241 );
1242 -- Create archive to prepayment assignment action interlock
1243 --
1244 --hr_nonrun_asact.insint(l_actid,rec_prepaid_assignments.prepaid_action_id);
1245 END IF;
1246 l_assignment_id := rec_prepaid_assignments.assignment_id;
1247 l_legal_employer_id := rec_prepaid_assignments.legal_employer_id;
1248 l_person_id := rec_prepaid_assignments.person_id;
1249
1250 END IF;
1251
1252 -- create archive to master assignment action interlock
1253 --hr_nonrun_asact.insint(l_actid,rec_prepaid_assignments.run_action_id);
1254 END LOOP;
1255 END IF;
1256 ELSE
1257 --logger ('FOR GIVEN EMP ', '8****8*****8****8****8');
1258 IF g_request_for <>'REQUESTING_ORG'
1259 THEN
1260 --logger ('FOR GIVEN EMP ', 'UNDER ALL LE');
1261 FOR rec_prepaid_assignments IN
1262 csr_prepaid_assignments_le (p_payroll_action_id
1263 ,p_start_person
1264 ,p_end_person
1265 ,g_legal_employer_id
1266 ,l_canonical_start_date
1267 ,l_canonical_end_date
1268 )
1269 LOOP
1270 --logger ('============================Person Number to be checked ', rec_prepaid_assignments.person_id);
1271 --logger ('===========LE ', rec_prepaid_assignments.legal_employer_id);
1272
1273 IF ( l_person_id <> rec_prepaid_assignments.person_id
1274 or l_legal_employer_id <> rec_prepaid_assignments.legal_employer_id
1275 )
1276 THEN
1277 --logger ('Person ID ', l_person_id);
1278 --logger ('Legal Employer id ', l_legal_employer_id);
1279 IF validate_person_number(rec_prepaid_assignments.person_id,g_person_number) IS NOT NULL
1280 THEN
1281 --logger ('Person validated ', rec_prepaid_assignments.person_id);
1282 check_a_taxcard (rec_prepaid_assignments.person_id
1283 ,l_a_tax_card_or_not
1284 );
1285
1286 IF l_a_tax_card_or_not > 0
1287 THEN
1288 SELECT pay_assignment_actions_s.NEXTVAL
1289 INTO l_actid
1290 FROM DUAL;
1291
1292 -- Create the archive assignment action
1293 hr_nonrun_asact.insact
1294 (l_actid
1295 ,rec_prepaid_assignments.assignment_id
1296 ,p_payroll_action_id
1297 ,p_chunk
1298 ,NULL
1299 );
1300 -- Create archive to prepayment assignment action interlock
1301 --
1302 --hr_nonrun_asact.insint(l_actid,rec_prepaid_assignments.prepaid_action_id);
1303 END IF;
1304 END IF;-- person number
1305 l_assignment_id := rec_prepaid_assignments.assignment_id;
1306 l_legal_employer_id := rec_prepaid_assignments.legal_employer_id;
1307 l_person_id := rec_prepaid_assignments.person_id;
1308 END IF;
1309
1310
1311 -- create archive to master assignment action interlock
1312 --hr_nonrun_asact.insint(l_actid,rec_prepaid_assignments.run_action_id);
1313 END LOOP;
1314 ELSE
1315 --logger ('FOR GIVEN EMP ', 'UNDER GIVEN LE');
1316 FOR rec_prepaid_assignments IN
1317 csr_prepaid_assignments_le (p_payroll_action_id
1318 ,p_start_person
1319 ,p_end_person
1320 ,g_legal_employer_id
1321 ,l_canonical_start_date
1322 ,l_canonical_end_date
1323 )
1324 LOOP
1325 --logger ('============================Person Number to be checked ', rec_prepaid_assignments.person_id);
1326 --logger ('===========LE ', rec_prepaid_assignments.legal_employer_id);
1327
1328 IF ( l_person_id <> rec_prepaid_assignments.person_id
1329 AND rec_prepaid_assignments.legal_employer_id = g_legal_employer_id
1330 )
1331 THEN
1332 --logger ('Person ID ', l_person_id);
1333 --logger ('Legal Employer id ', l_legal_employer_id);
1334 IF validate_person_number(rec_prepaid_assignments.person_id,g_person_number) IS NOT NULL
1335 THEN
1336 --logger ('Person validated ', rec_prepaid_assignments.person_id);
1337 check_a_taxcard (rec_prepaid_assignments.person_id
1338 ,l_a_tax_card_or_not
1339 );
1340
1341 IF l_a_tax_card_or_not > 0
1342 THEN
1343 SELECT pay_assignment_actions_s.NEXTVAL
1344 INTO l_actid
1345 FROM DUAL;
1346
1347 -- Create the archive assignment action
1348 hr_nonrun_asact.insact
1349 (l_actid
1350 ,rec_prepaid_assignments.assignment_id
1351 ,p_payroll_action_id
1352 ,p_chunk
1353 ,NULL
1354 );
1355 -- Create archive to prepayment assignment action interlock
1356 --
1357 --hr_nonrun_asact.insint(l_actid,rec_prepaid_assignments.prepaid_action_id);
1358 END IF;
1359 END IF;-- person number
1360 l_assignment_id := rec_prepaid_assignments.assignment_id;
1361 l_legal_employer_id := rec_prepaid_assignments.legal_employer_id;
1362 l_person_id := rec_prepaid_assignments.person_id;
1363 END IF;
1364
1365
1366 -- create archive to master assignment action interlock
1367 --hr_nonrun_asact.insint(l_actid,rec_prepaid_assignments.run_action_id);
1368 END LOOP;
1369 END IF;
1370
1371 END IF; -- for PER_ALL
1372
1373 --logger ('ASSIGNMENT_ACTION_CODE ', '--------------------------------- Ended');
1374
1375 IF g_debug
1376 THEN
1377 hr_utility.set_location
1378 (' Leaving Procedure ASSIGNMENT_ACTION_CODE'
1379 ,70
1380 );
1381 END IF;
1382 EXCEPTION
1383 WHEN OTHERS
1384 THEN
1385 IF g_debug
1386 THEN
1387 hr_utility.set_location ('error raised assignment_action_code '
1388 ,5
1389 );
1390 END IF;
1391
1392 RAISE;
1393 END assignment_action_code;
1394
1395 /*fffffffffffffffffffffffffff*/
1396
1397 /* INITIALIZATION CODE */
1398 PROCEDURE initialization_code (p_payroll_action_id IN NUMBER)
1399 IS
1400 l_action_info_id NUMBER;
1401 l_ovn NUMBER;
1402 l_count NUMBER := 0;
1403 l_business_group_id NUMBER;
1404 l_start_date VARCHAR2 (20);
1405 l_end_date VARCHAR2 (20);
1406 l_effective_date DATE;
1407 l_payroll_id NUMBER;
1408 l_consolidation_set NUMBER;
1409 l_le NUMBER := 0;
1410
1411 CURSOR csr_get_all_legal_employer_id
1412 IS
1413 SELECT o.organization_id
1414 FROM hr_all_organization_units o
1415 ,hr_organization_information hoi1
1416 ,hr_organization_information hoi2
1417 WHERE o.business_group_id = g_business_group_id
1418 AND hoi1.organization_id = o.organization_id
1419 AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
1420 AND hoi1.org_information_context = 'CLASS'
1421 AND o.organization_id = hoi2.organization_id
1422 AND hoi2.org_information_context = 'SE_INC_STMT_PROVIDERS'
1423 AND hoi2.org_information1 = g_income_statement_provider_id
1424 AND o.organization_id =
1425 DECODE (g_request_for
1426 ,'ALL_ORG', o.organization_id
1427 ,g_legal_employer_id
1428 );
1429
1430 /* CURSOR csr_get_all_Legal_employer_id
1431 IS
1432 select o.organization_id,hoi3.ORG_INFORMATION1,hoi3.ORG_INFORMATION2
1433 FROM hr_all_organization_units o
1434 , hr_organization_information hoi1
1435 , hr_organization_information hoi2
1436 , hr_organization_information hoi3
1437 WHERE o.business_group_id = g_business_group_id
1438 AND hoi1.organization_id = o.organization_id
1439 AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
1440 AND hoi1.org_information_context = 'CLASS'
1441 AND o.organization_id = hoi2.organization_id
1442 AND hoi2.org_information_context = 'SE_INC_STMT_PROVIDERS'
1443 and hoi2.org_information1 = g_income_statement_provider_id
1444 and o.organization_id = decode(g_request_for,'ALL_ORG',o.organization_id,g_legal_employer_id)
1445 AND o.organization_id = hoi3.organization_id
1446 AND hoi3.org_information_context = 'SE_INC_STMT_KU10_INFORMATION';
1447 */
1448 /*
1449 CURSOR csr_get_all_info(csr_v_LE_id NUMBER)
1450 IS
1451 select o.organization_id,hoi3.ORG_INFORMATION1,hoi3.ORG_INFORMATION2
1452 FROM hr_all_organization_units o
1453 , hr_organization_information hoi1
1454 , hr_organization_information hoi3
1455 WHERE o.business_group_id = g_business_group_id
1456 AND hoi1.organization_id = o.organization_id
1457 AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
1458 AND hoi1.org_information_context = 'CLASS'
1459 and o.organization_id = csr_v_LE_id
1460 AND o.organization_id = hoi3.organization_id
1461 AND hoi3.org_information_context = 'SE_INC_STMT_KU10_INFORMATION';
1462
1463 */
1464 CURSOR csr_get_all_info (csr_v_le_id NUMBER, csr_v_code VARCHAR2)
1465 IS
1466 SELECT o.organization_id
1467 ,hoi3.org_information1
1468 ,hoi3.org_information2
1469 FROM hr_all_organization_units o
1470 ,hr_organization_information hoi1
1471 ,hr_organization_information hoi3
1472 WHERE o.business_group_id = g_business_group_id
1473 AND hoi1.organization_id = o.organization_id
1474 AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
1475 AND hoi1.org_information_context = 'CLASS'
1476 AND o.organization_id = csr_v_le_id
1477 AND o.organization_id = hoi3.organization_id
1478 -- AND hoi3.org_information_context = 'SE_INC_STMT_KU10_INFORMATION'
1479 AND hoi3.org_information_context IN
1480 ('SE_INC_STMT_KU10_INFORMATION'
1481 ,'SE_INC_STMT_KU13_INFORMATION'
1482 ,'SE_INC_STMT_KU14_INFORMATION'
1483 )
1484 AND hoi3.org_information1 = csr_v_code;
1485
1486 lr_get_all_info csr_get_all_info%ROWTYPE;
1487
1488 CURSOR csr_get_all_codes
1489 IS
1490 SELECT h.lookup_code
1491 FROM hr_lookups h
1492 WHERE h.lookup_type = 'SE_INCOME_STATEMENT_FIELDS'
1493 AND h.lookup_code LIKE 'KU%'
1494 AND h.enabled_flag = 'Y'
1495 ORDER BY h.meaning;
1496
1497 l_temp_counter VARCHAR2 (200);
1498 BEGIN
1499 --logger ('Initialization Code ', '=====> Started');
1500 g_payroll_action_id := p_payroll_action_id;
1501 g_business_group_id := NULL;
1502 g_effective_date := NULL;
1503 g_legal_employer_id := NULL;
1504 pay_se_income_statement.get_all_parameters
1505 (p_payroll_action_id
1506 ,g_business_group_id
1507 ,g_effective_date
1508 ,g_income_statement_provider_id
1509 ,g_request_for
1510 ,g_legal_employer_id
1511 ,g_income_year
1512 ,g_person_for
1513 ,g_person_number
1514 ,g_sort_order
1515 ,g_test_or_production
1516 );
1517 g_income_start_date := TO_DATE ('01-01-' || g_income_year, 'DD-MM-YYYY');
1518 g_income_end_date := TO_DATE ('31-12-' || g_income_year, 'DD-MM-YYYY');
1519 --logger ('Initialization Code ', '=====> ; In');
1520 l_count := 1;
1521
1522 FOR row_get_all_legal_employer_id IN csr_get_all_legal_employer_id
1523 LOOP
1524 --logger ('organization_id ' ,row_get_all_legal_employer_id.organization_id );
1525 each_field_value (row_get_all_legal_employer_id.organization_id).legal_employer_id :=
1526 row_get_all_legal_employer_id.organization_id;
1527
1528 -- each_field_value(row_get_all_Legal_employer_id.organization_id).FIELD_CODE(row_get_all_Legal_employer_id.ORG_INFORMATION1) := row_get_all_Legal_employer_id.ORG_INFORMATION2;
1529 FOR row_get_all_codes IN csr_get_all_codes
1530 LOOP
1531 --logger ('CODE', row_get_all_codes.lookup_code);
1532 lr_get_all_info := NULL;
1533
1534 OPEN csr_get_all_info
1535 (row_get_all_legal_employer_id.organization_id
1536 ,row_get_all_codes.lookup_code
1537 );
1538
1539 FETCH csr_get_all_info
1540 INTO lr_get_all_info;
1541
1542 CLOSE csr_get_all_info;
1543
1544 --logger ('ORG_INFORMATION1 ', lr_get_all_info.org_information1);
1545 each_field_value (row_get_all_legal_employer_id.organization_id).field_code
1546 (row_get_all_codes.lookup_code) :=
1547 lr_get_all_info.org_information2;
1548 --logger ('ORG_INFORMATION2 ', lr_get_all_info.org_information2);
1549 END LOOP;
1550 /*
1551 FOR row_get_all_info IN csr_get_all_info(row_get_all_Legal_employer_id.organization_id)
1552 LOOP
1553
1554 logger ('ORG_INFORMATION1 ',row_get_all_info.ORG_INFORMATION1 );
1555 each_field_value(row_get_all_Legal_employer_id.organization_id).FIELD_CODE(row_get_all_info.ORG_INFORMATION1) := row_get_all_info.ORG_INFORMATION2;
1556 logger ('ORG_INFORMATION2 ',row_get_all_info.ORG_INFORMATION2 );
1557 -- l_count := l_count + 1 ;
1558 END LOOP;
1559 */
1560 END LOOP;
1561
1562 l_temp_counter := each_field_value.FIRST;
1563
1564 WHILE l_temp_counter IS NOT NULL
1565 LOOP
1566 --logger ('each__value' ,each_field_value (l_temp_counter).legal_employer_id );
1567 l_temp_counter := each_field_value.NEXT (l_temp_counter);
1568 END LOOP;
1569
1570 -- *****************************************************************************
1571 /* FOR i IN each_field_value.FIRST .. each_field_value.LAST
1572 LOOP
1573
1574
1575 l_LE :=each_field_value (i).LEGAL_EMPLOYER_ID;
1576 logger ('each_field_value ', l_LE);
1577 l_temp_counter := each_field_value (l_LE).FIELD_CODE.FIRST;
1578
1579 FOR row_get_all_codes IN csr_get_all_codes
1580 LOOP
1581
1582 IF each_field_value(l_LE).FIELD_CODE.EXISTS(row_get_all_codes.LOOKUP_CODE) = FALSE
1583 THEN
1584 logger (' Is not There',row_get_all_codes.LOOKUP_CODE );
1585 each_field_value(l_LE).FIELD_CODE(row_get_all_codes.LOOKUP_CODE) := NULL;
1586 END IF;
1587
1588 logger ('LooK ',row_get_all_codes.LOOKUP_CODE );
1589 logger ('Value ',each_field_value(l_LE).FIELD_CODE(row_get_all_codes.LOOKUP_CODE) );
1590 END LOOP;
1591 /*
1592 WHILE l_temp_counter IS NOT NULL
1593 LOOP
1594 logger ( 'Vslue',each_field_value (l_LE).FIELD_CODE(l_temp_counter) );
1595 l_temp_counter :=each_field_value (l_LE).FIELD_CODE.NEXT(l_temp_counter);
1596 END LOOP;
1597 END LOOP;*/
1598
1599 -- *****************************************************************************
1600 --logger ('Initialization Code ', '=====> ; after ');
1601 -- logger ('each_field_value (3134).FIELD_CODE(KU10_RENT) ',each_field_value ('3134').--field_code ('KU10_RENT') );
1602 -- logger ('each_field_value (3134).FIELD_CODE(KU10_RENT) ',each_field_value ('3267').--field_code ('KU10_RENT') );
1603 /*
1604 FOR i IN each_field_value.FIRST .. each_field_value.LAST
1605 LOOP
1606 logger ('Legal Employer ', each_field_value (i).LEGAL_EMPLOYER_ID);
1607 END LOOP;
1608 */
1609 --logger ('Initialization Code ', '********> Ended');
1610 EXCEPTION
1611 WHEN OTHERS
1612 THEN
1613 g_err_num := SQLCODE;
1614 --logger ('Initialization Code ', '********> Errorrr');
1615 END initialization_code;
1616
1617 /* GET DEFINED BALANCE ID */
1618 FUNCTION get_defined_balance_id (p_user_name IN VARCHAR2)
1619 RETURN NUMBER
1620 IS
1621 /* Cursor to retrieve Defined Balance Id */
1622 CURSOR csr_def_bal_id (p_user_name VARCHAR2)
1623 IS
1624 SELECT u.creator_id
1625 FROM ff_user_entities u
1626 ,ff_database_items d
1627 WHERE d.user_name = p_user_name
1628 AND u.user_entity_id = d.user_entity_id
1629 AND (u.legislation_code = 'SE')
1630 AND (u.business_group_id IS NULL)
1631 AND u.creator_type = 'B';
1632
1633 l_defined_balance_id ff_user_entities.user_entity_id%TYPE;
1634 BEGIN
1635 IF g_debug
1636 THEN
1637 hr_utility.set_location
1638 (' Entering Function GET_DEFINED_BALANCE_ID'
1639 ,240
1640 );
1641 END IF;
1642
1643 OPEN csr_def_bal_id (p_user_name);
1644
1645 FETCH csr_def_bal_id
1646 INTO l_defined_balance_id;
1647
1648 CLOSE csr_def_bal_id;
1649
1650 RETURN l_defined_balance_id;
1651
1652 IF g_debug
1653 THEN
1654 hr_utility.set_location (' Leaving Function GET_DEFINED_BALANCE_ID'
1655 ,250
1656 );
1657 END IF;
1658 END get_defined_balance_id;
1659
1660 FUNCTION get_defined_balance_value (
1661 p_user_name IN VARCHAR2
1662 ,p_in_assignment_id IN NUMBER
1663 ,p_in_virtual_date IN DATE
1664 ,p_tax_unit_id IN NUMBER
1665 ,p_local_unit_id IN NUMBER
1666 )
1667 RETURN NUMBER
1668 IS
1669 /* Cursor to retrieve Defined Balance Id */
1670 CURSOR csr_def_bal_id (p_user_name VARCHAR2)
1671 IS
1672 SELECT u.creator_id
1673 FROM ff_user_entities u
1674 ,ff_database_items d
1675 WHERE d.user_name = p_user_name
1676 AND u.user_entity_id = d.user_entity_id
1677 AND (u.legislation_code = 'SE')
1678 AND (u.business_group_id IS NULL)
1679 AND u.creator_type = 'B';
1680
1681 l_defined_balance_id ff_user_entities.user_entity_id%TYPE;
1682 l_return_balance_value NUMBER;
1683 BEGIN
1684 IF g_debug
1685 THEN
1686 hr_utility.set_location
1687 (' Entering Function GET_DEFINED_BALANCE_VALUE'
1688 ,240
1689 );
1690 END IF;
1691
1692 OPEN csr_def_bal_id (p_user_name);
1693
1694 FETCH csr_def_bal_id
1695 INTO l_defined_balance_id;
1696
1697 CLOSE csr_def_bal_id;
1698
1699 pay_balance_pkg.set_context ('TAX_UNIT_ID', p_tax_unit_id);
1700 pay_balance_pkg.set_context ('LOCAL_UNIT_ID', p_local_unit_id);
1701 l_return_balance_value :=
1702 -- TO_CHAR (
1703 pay_balance_pkg.get_value
1704 (p_defined_balance_id => l_defined_balance_id
1705 ,p_assignment_id => p_in_assignment_id
1706 ,p_virtual_date => p_in_virtual_date
1707 )
1708 -- ,'999999999D99' )
1709 ;
1710 RETURN l_return_balance_value;
1711
1712 IF g_debug
1713 THEN
1714 hr_utility.set_location
1715 (' Leaving Function GET_DEFINED_BALANCE_VALUE'
1716 ,250
1717 );
1718 END IF;
1719 END get_defined_balance_value;
1720
1721 FUNCTION get_balance_value (
1722 p_balance_type_id IN NUMBER
1723 ,p_in_assignment_id IN NUMBER
1724 ,p_in_virtual_date IN DATE
1725 ,p_tax_unit_id IN NUMBER
1726 ,p_local_unit_id IN NUMBER
1727 )
1728 RETURN NUMBER
1729 IS
1730 /* Cursor to retrieve Defined Balance Id */
1731 CURSOR csr_def_bal_id (csr_v_balance_type_id NUMBER)
1732 IS
1733 SELECT pd.defined_balance_id
1734 FROM pay_defined_balances pd
1735 ,pay_balance_dimensions pbd
1736 WHERE pd.balance_type_id = csr_v_balance_type_id
1737 AND pbd.balance_dimension_id = pd.balance_dimension_id
1738 AND pbd.legislation_code = 'SE'
1739 AND pbd.database_item_suffix = '_PER_LE_YTD';
1740
1741 l_defined_balance_id ff_user_entities.user_entity_id%TYPE;
1742 l_return_balance_value NUMBER;
1743 BEGIN
1744 IF p_balance_type_id IS NOT NULL
1745 THEN
1746 OPEN csr_def_bal_id (p_balance_type_id);
1747
1748 FETCH csr_def_bal_id
1749 INTO l_defined_balance_id;
1750
1751 CLOSE csr_def_bal_id;
1752
1753 IF l_defined_balance_id IS NOT NULL
1754 THEN
1755 pay_balance_pkg.set_context ('TAX_UNIT_ID', p_tax_unit_id);
1756 pay_balance_pkg.set_context ('LOCAL_UNIT_ID', p_local_unit_id);
1757 l_return_balance_value :=
1758 -- TO_CHAR (
1759 pay_balance_pkg.get_value
1760 (p_defined_balance_id => l_defined_balance_id
1761 ,p_assignment_id => p_in_assignment_id
1762 ,p_virtual_date => p_in_virtual_date
1763 )
1764 -- ,'999999999D99' )
1765 ;
1766 END IF;
1767 ELSE
1768 l_return_balance_value := 0;
1769 END IF;
1770
1771 RETURN l_return_balance_value;
1772 END get_balance_value;
1773
1774 /* ARCHIVE CODE */
1775 PROCEDURE archive_code (
1776 p_assignment_action_id IN NUMBER
1777 ,p_effective_date IN DATE
1778 )
1779 IS
1780 CURSOR csr_get_defined_balance_id (
1781 csr_v_balance_name ff_database_items.user_name%TYPE
1782 )
1783 IS
1784 SELECT ue.creator_id
1785 FROM ff_user_entities ue
1786 ,ff_database_items di
1787 WHERE di.user_name = csr_v_balance_name
1788 AND ue.user_entity_id = di.user_entity_id
1789 AND ue.legislation_code = 'SE'
1790 AND ue.business_group_id IS NULL
1791 AND ue.creator_type = 'B';
1792
1793 lr_get_defined_balance_id csr_get_defined_balance_id%ROWTYPE;
1794 l_actid NUMBER;
1795 l_end_date per_time_periods.end_date%TYPE;
1796 l_date_earned pay_payroll_actions.date_earned%TYPE;
1797 l_effective_date pay_payroll_actions.effective_date%TYPE;
1798 l_start_date per_time_periods.start_date%TYPE;
1799 l_action_info_id pay_action_information.action_information_id%TYPE;
1800 l_ovn pay_action_information.object_version_number%TYPE;
1801 l_flag NUMBER := 0;
1802 -- The place for Variables which fetches the values to be archived
1803 l_employee_last_name VARCHAR2 (240);
1804 l_employee_name VARCHAR2 (240);
1805 l_employee_pin VARCHAR2 (240);
1806 l_employees_address VARCHAR2 (240);
1807 l_employees_postalcode VARCHAR2 (240);
1808 l_employee_postal_address VARCHAR2 (240);
1809 l_month_from VARCHAR2 (10);
1810 l_month_to VARCHAR2 (10);
1811 l_date_of_correction VARCHAR2 (50);
1812 l_a_tax_withheld NUMBER;
1813 l_a_tax_withheld_flag VARCHAR2 (10);
1814 l_gross_salary NUMBER;
1815 l_tb_exclusive_car_fuel NUMBER;
1816 l_tb_exclusive_fuel NUMBER;
1817 l_rsv_code VARCHAR2 (240);
1818 l_number_of_months_car NUMBER;
1819 l_number_of_kilometers NUMBER;
1820 l_emp_payment_car NUMBER;
1821 l_free_fuel_car NUMBER;
1822 l_compensation_for_expenses NUMBER;
1823 l_occupational_pension NUMBER;
1824 l_other_tax_rem NUMBER;
1825 l_tax_rem_without_sjd NUMBER; --EOY 2008
1826 l_tax_rem_paid NUMBER;
1827 l_not_tax_rem NUMBER;
1828 l_certain_deductions NUMBER;
1829 l_rent NUMBER;
1830 l_work_site_number VARCHAR2 (100);
1831 l_free_housing VARCHAR2 (100);
1832 l_free_meals VARCHAR2 (100);
1833 l_free_housing_other41 VARCHAR2 (100);
1834 l_interest VARCHAR2 (100);
1835 l_other_benefits VARCHAR2 (100);
1836 l_benefit_adjusted VARCHAR2 (100);
1837 l_mileage_allowance VARCHAR2 (100);
1838 l_per_diem_sweden VARCHAR2 (100);
1839 l_per_diem_other VARCHAR2 (100);
1840 l_within_sweden VARCHAR2 (100);
1841 l_other_countries VARCHAR2 (100);
1842 l_business_travel_expenses VARCHAR2 (100);
1843 l_acc_business_travels VARCHAR2 (100);
1844 l_other_benefits_up65 VARCHAR2 (100);
1845 l_compe_for_expenses_up66 VARCHAR2 (100);
1846 l_tax_rem_paid_up67 VARCHAR2 (100);
1847 l_other_tax_rem_up68 VARCHAR2 (100);
1848 l_tax_rem_without_sjd_up69 VARCHAR2 (100); ---EOY 2008
1849 l_benefit_as_pension VARCHAR2 (100); --EOY 2008
1850 l_benefit_as_pension_flag VARCHAR2 (100); --EOY 2008
1851 l_certain_deductions_up70 VARCHAR2 (100);
1852 l_car_ben_ytd NUMBER;
1853 l_fuel_ben_ytd NUMBER;
1854 l_ben_ytd NUMBER;
1855 l_primary_local_unit_id NUMBER;
1856 l_primary_assignment_id NUMBER;
1857 l_temp NUMBER;
1858 l_temp_balance_value NUMBER;
1859 l_free_housing_other41_flag VARCHAR2 (240);
1860 l_interest_flag VARCHAR2 (240);
1861 l_other_benefits_flag VARCHAR2 (240);
1862 l_busi_travel_expenses_flag VARCHAR2 (240);
1863 l_acc_business_travels_flag VARCHAR2 (240);
1864 l_car_elem_end_date DATE;
1865 l_car_elem_start_date DATE;
1866 l_car_elem_entry_id NUMBER;
1867 l_statement_type VARCHAR2 (240);
1868 l_correction_date VARCHAR2 (240);
1869 l_tax_country_meaning VARCHAR2 (240);
1870 l_tax_country_code VARCHAR2 (240);
1871 l_ftin VARCHAR2 (240);
1872 l_work_country_meaning VARCHAR2 (240);
1873 l_work_country_code VARCHAR2 (240);
1874 l_work_period VARCHAR2 (240);
1875 l_emp_regulation_category VARCHAR2 (240);
1876 l_emp_regulation_category_code VARCHAR2 (240);
1877 l_article_details VARCHAR2 (240);
1878 l_occupational_pension_ku13 NUMBER;
1879 l_compen_for_benefit_ku13 NUMBER;
1880 l_tax_rem_ssc_ku13 NUMBER;
1881 l_not_tax_rem_ku14 NUMBER;
1882 l_occupational_pension_ku14 NUMBER;
1883 l_tax_rem_ssc_ku14 NUMBER;
1884 l_other_tax_rem_ku14 NUMBER;
1885 l_compe_for_expenses_ku14 NUMBER;
1886 l_in_plain_writing_code VARCHAR2 (240);
1887 l_in_plain_writing_meaning VARCHAR2 (240);
1888 l_employee_number VARCHAR2 (240);
1889 l_employee_code VARCHAR2 (240);
1890 l_action_id VARCHAR2 (2);
1891 l_local_unit_id_fetched NUMBER;
1892 l_eit_local_unit NUMBER;
1893 l_legal_employer_id_fetched NUMBER;
1894 -- Temp needed Variables
1895 l_person_id per_all_people_f.person_id%TYPE;
1896 l_assignment_id per_all_assignments_f.assignment_id%TYPE;
1897
1898 -- Temp needed Variables
1899
1900 -- End of place for Variables which fetches the values to be archived
1901
1902 -- The place for Cursor which fetches the values to be archived
1903
1904 --
1905 -- Cursor to pick up
1906 CURSOR csr_get_person_id (p_asg_act_id NUMBER)
1907 IS
1908 SELECT *
1909 FROM (SELECT paa.effective_start_date,paa.effective_end_date
1910 ,paa.person_id
1911 ,paa.assignment_id
1912 FROM per_all_assignments_f paa
1913 ,pay_assignment_actions pac
1914 WHERE pac.assignment_action_id = p_asg_act_id
1915 AND paa.assignment_id = pac.assignment_id
1916 AND paa.effective_start_date <= g_income_end_date
1917 AND paa.effective_end_date > = g_income_start_date
1918 AND assignment_status_type_id IN (
1919 SELECT assignment_status_type_id
1920 FROM per_assignment_status_types
1921 WHERE per_system_status = 'ACTIVE_ASSIGN'
1922 AND active_flag = 'Y'
1923 AND ( ( legislation_code IS NULL
1924 AND business_group_id IS NULL
1925 )
1926 OR (business_group_id =
1927 g_business_group_id
1928 )
1929 ))
1930 ORDER BY paa.effective_start_date DESC)
1931 WHERE ROWNUM < 2;
1932
1933 lr_get_person_id csr_get_person_id%ROWTYPE;
1934
1935 /* Cursor to retrieve Person Details */
1936 CURSOR csr_get_person_details (
1937 csr_v_person_id NUMBER
1938 ,csr_v_effective_date DATE
1939 )
1940 IS
1941 SELECT pap.last_name
1942 ,pap.pre_name_adjunct
1943 ,pap.first_name
1944 ,pap.national_identifier
1945 ,pap.person_id
1946 ,pap.per_information1
1947 ,ft.territory_short_name
1948 ,ft.territory_code
1949 ,pap.effective_end_date
1950 ,pap.EMPLOYEE_NUMBER
1951 FROM per_all_people_f pap
1952 ,fnd_territories_vl ft
1953 WHERE pap.person_id = csr_v_person_id
1954 AND pap.per_information_category = 'SE'
1955 AND ft.obsolete_flag = 'N'
1956 AND ft.territory_code = pap.per_information1
1957 AND csr_v_effective_date BETWEEN pap.effective_start_date
1958 AND pap.effective_end_date;
1959
1960 /* SELECT pap.last_name, pap.pre_name_adjunct, pap.first_name
1961 , pap.national_identifier, pap.person_id
1962 FROM
1963 per_all_people_f pap
1964 WHERE pap.person_id = csr_v_person_id
1965 AND pap.per_information_category = 'SE'
1966 AND csr_v_effective_date BETWEEN pap.effective_start_date
1967 AND pap.effective_end_date;
1968 */
1969 lr_get_person_details csr_get_person_details%ROWTYPE;
1970
1971 CURSOR csr_get_employee_address (
1972 csr_v_person_id NUMBER
1973 ,csr_v_effective_date DATE
1974 )
1975 IS
1976 SELECT address_line1
1977 ,address_line2
1978 ,address_line3
1979 ,postal_code
1980 ,country
1981 ,ft.territory_short_name
1982 FROM per_addresses
1983 ,fnd_territories_vl ft
1984 WHERE business_group_id = g_business_group_id
1985 AND person_id = csr_v_person_id
1986 AND country = ft.territory_code
1987 AND csr_v_effective_date BETWEEN date_from
1988 AND NVL (date_to
1989 ,TO_DATE ('31-12-4712'
1990 ,'DD-MM-YYYY'
1991 )
1992 );
1993
1994 lr_get_employee_address csr_get_employee_address%ROWTYPE;
1995
1996 CURSOR csr_get_month_to_from (
1997 csr_v_person_id NUMBER
1998 ,csr_v_legal_employer NUMBER
1999 )
2000 IS
2001 SELECT MIN (paa.effective_start_date) effective_start_date
2002 ,MAX (paa.effective_end_date) effective_end_date
2003 FROM per_all_assignments_f paa
2004 ,hr_soft_coding_keyflex scl
2005 WHERE person_id = csr_v_person_id
2006 AND paa.effective_start_date <= g_income_end_date
2007 AND paa.effective_end_date > = g_income_start_date
2008 AND assignment_status_type_id IN (
2009 SELECT assignment_status_type_id
2010 FROM per_assignment_status_types
2011 WHERE per_system_status = 'ACTIVE_ASSIGN'
2012 AND active_flag = 'Y'
2013 AND ( ( legislation_code IS NULL
2014 AND business_group_id IS NULL
2015 )
2016 OR (business_group_id = g_business_group_id)
2017 ))
2018 AND scl.soft_coding_keyflex_id = paa.soft_coding_keyflex_id
2019 AND scl.segment2 IN (
2020 SELECT o1.organization_id
2021 FROM hr_organization_units o1
2022 ,hr_organization_information hoi1
2023 ,hr_organization_information hoi2
2024 ,hr_organization_information hoi3
2025 WHERE o1.business_group_id = g_business_group_id
2026 AND hoi1.organization_id = o1.organization_id
2027 AND hoi1.org_information1 = 'SE_LOCAL_UNIT'
2028 AND hoi1.org_information_context = 'CLASS'
2029 AND NVL (hoi1.org_information2, 'N') = 'Y'
2030 AND o1.organization_id = hoi2.org_information1
2031 AND hoi2.org_information_context = 'SE_LOCAL_UNITS'
2032 AND hoi2.organization_id = hoi3.organization_id
2033 AND hoi3.org_information_context = 'CLASS'
2034 AND hoi3.org_information1 = 'HR_LEGAL_EMPLOYER'
2035 AND hoi3.organization_id = csr_v_legal_employer
2036 AND NVL (hoi3.org_information2, 'N') = 'Y');
2037
2038 lr_get_month_to_from csr_get_month_to_from%ROWTYPE;
2039
2040 CURSOR csr_person_correction_date (csr_v_person_id NUMBER)
2041 IS
2042 SELECT pei_information1
2043 FROM per_people_extra_info
2044 WHERE person_id = csr_v_person_id
2045 AND information_type = 'SE_INC_STMT_DATA_CORRECTION';
2046
2047 lr_person_correction_date csr_person_correction_date%ROWTYPE;
2048
2049 -- Cursor to pick up segment2
2050 CURSOR csr_get_segment2 (csr_v_effective_date DATE)
2051 IS
2052 SELECT scl.segment2
2053 ,scl.segment8
2054 FROM per_all_assignments_f paa
2055 ,hr_soft_coding_keyflex scl
2056 ,pay_assignment_actions pasa
2057 WHERE pasa.assignment_action_id = p_assignment_action_id
2058 AND pasa.assignment_id = paa.assignment_id
2059 AND scl.soft_coding_keyflex_id = paa.soft_coding_keyflex_id
2060 AND csr_v_effective_date BETWEEN paa.effective_start_date
2061 AND paa.effective_end_date;
2062
2063 lr_get_segment2 csr_get_segment2%ROWTYPE;
2064
2065 -- Cursor to pick up LEGAL EMPLOYER
2066 CURSOR csr_find_legal_employer (
2067 csr_v_organization_id hr_organization_information.organization_id%TYPE
2068 )
2069 IS
2070 SELECT hoi3.organization_id legal_id
2071 FROM hr_all_organization_units o1
2072 ,hr_organization_information hoi1
2073 ,hr_organization_information hoi2
2074 ,hr_organization_information hoi3
2075 WHERE o1.business_group_id = g_business_group_id
2076 AND hoi1.organization_id = o1.organization_id
2077 AND hoi1.organization_id = csr_v_organization_id
2078 AND hoi1.org_information1 = 'SE_LOCAL_UNIT'
2079 AND hoi1.org_information_context = 'CLASS'
2080 AND o1.organization_id = hoi2.org_information1
2081 AND hoi2.org_information_context = 'SE_LOCAL_UNITS'
2082 AND hoi2.organization_id = hoi3.organization_id
2083 AND hoi3.org_information_context = 'CLASS'
2084 AND hoi3.org_information1 = 'HR_LEGAL_EMPLOYER';
2085
2086 lr_find_legal_employer csr_find_legal_employer%ROWTYPE;
2087
2088 -- Cursor to pick up Local Unit Details
2089 CURSOR csr_local_unit_details (
2090 csr_v_local_unit_id hr_organization_information.organization_id%TYPE
2091 )
2092 IS
2093 SELECT o1.NAME
2094 ,hoi2.org_information2
2095 FROM hr_organization_units o1
2096 ,hr_organization_information hoi1
2097 ,hr_organization_information hoi2
2098 WHERE o1.business_group_id = g_business_group_id
2099 AND hoi1.organization_id = o1.organization_id
2100 AND hoi1.organization_id = csr_v_local_unit_id
2101 AND hoi1.org_information1 = 'SE_LOCAL_UNIT'
2102 AND hoi1.org_information_context = 'CLASS'
2103 AND o1.organization_id = hoi2.organization_id
2104 AND hoi2.org_information_context = 'SE_LOCAL_UNIT_DETAILS';
2105
2106 lr_local_unit_details csr_local_unit_details%ROWTYPE;
2107
2108 CURSOR csr_get_assignments (csr_v_person_id NUMBER)
2109 IS
2110 SELECT paa.assignment_id
2111 ,paa.effective_start_date
2112 ,paa.effective_end_date
2113 ,scl.segment2
2114 FROM per_all_assignments_f paa
2115 ,hr_soft_coding_keyflex scl
2116 WHERE paa.person_id = csr_v_person_id
2117 AND paa.effective_start_date <= g_income_end_date
2118 AND paa.effective_end_date > = g_income_start_date
2119 AND paa.assignment_status_type_id IN (
2120 SELECT assignment_status_type_id
2121 FROM per_assignment_status_types
2122 WHERE per_system_status = 'ACTIVE_ASSIGN'
2123 AND active_flag = 'Y'
2124 AND ( ( legislation_code IS NULL
2125 AND business_group_id IS NULL
2126 )
2127 OR (business_group_id = g_business_group_id)
2128 ))
2129 AND scl.soft_coding_keyflex_id = paa.soft_coding_keyflex_id;
2130
2131 lr_get_assignments csr_get_assignments%ROWTYPE;
2132
2133 CURSOR csr_get_prim_assignments (csr_v_person_id NUMBER)
2134 IS
2135 SELECT paa.assignment_id
2136 ,paa.effective_start_date
2137 ,paa.effective_end_date
2138 ,scl.segment2
2139 FROM per_all_assignments_f paa
2140 ,hr_soft_coding_keyflex scl
2141 WHERE person_id = csr_v_person_id
2142 AND paa.effective_start_date <= g_income_end_date
2143 AND paa.effective_end_date > = g_income_start_date
2144 AND paa.primary_flag = 'Y'
2145 AND paa.assignment_status_type_id IN (
2146 SELECT assignment_status_type_id
2147 FROM per_assignment_status_types
2148 WHERE per_system_status = 'ACTIVE_ASSIGN'
2149 AND active_flag = 'Y'
2150 AND ( ( legislation_code IS NULL
2151 AND business_group_id IS NULL
2152 )
2153 OR (business_group_id = g_business_group_id)
2154 ))
2155 AND scl.soft_coding_keyflex_id = paa.soft_coding_keyflex_id;
2156
2157 lr_get_prim_assignments csr_get_prim_assignments%ROWTYPE;
2158
2159 CURSOR csr_chk_valid_le_lu (
2160 csr_v_local_unit_id hr_organization_information.organization_id%TYPE
2161 ,csr_v_legal_employer_id hr_organization_information.organization_id%TYPE
2162 )
2163 IS
2164 SELECT 'Y' "VALID"
2165 FROM hr_organization_units o1
2166 ,hr_organization_information hoi1
2167 ,hr_organization_information hoi2
2168 ,hr_organization_information hoi3
2169 WHERE o1.business_group_id = g_business_group_id
2170 AND hoi1.organization_id = o1.organization_id
2171 AND hoi1.org_information1 = 'SE_LOCAL_UNIT'
2172 AND hoi1.org_information_context = 'CLASS'
2173 AND NVL (hoi1.org_information2, 'N') = 'Y'
2174 AND o1.organization_id = hoi2.org_information1
2175 AND hoi2.org_information_context = 'SE_LOCAL_UNITS'
2176 AND hoi2.organization_id = hoi3.organization_id
2177 AND hoi3.org_information_context = 'CLASS'
2178 AND hoi3.org_information1 = 'HR_LEGAL_EMPLOYER'
2179 AND hoi3.organization_id = csr_v_legal_employer_id
2180 AND NVL (hoi3.org_information2, 'N') = 'Y'
2181 AND o1.organization_id = csr_v_local_unit_id;
2182
2183 lr_chk_valid_le_lu csr_chk_valid_le_lu%ROWTYPE;
2184
2185 CURSOR csr_get_element_ids
2186 IS
2187 SELECT pet.element_type_id
2188 ,piv.input_value_id
2189 ,pel.element_link_id
2190 FROM pay_element_types_f pet
2191 ,pay_input_values_f piv
2192 ,pay_element_links_f pel
2193 WHERE pet.element_name = 'Tax Card'
2194 AND pet.legislation_code = 'SE'
2195 AND piv.element_type_id = pet.element_type_id
2196 AND piv.NAME = 'Tax Card Type'
2197 AND pel.element_type_id = pet.element_type_id
2198 AND pel.business_group_id = g_business_group_id
2199 AND pet.effective_start_date <= g_income_end_date
2200 AND pet.effective_end_date > = g_income_start_date
2201 AND piv.effective_start_date <= g_income_end_date
2202 AND piv.effective_end_date > = g_income_start_date
2203 AND pel.effective_start_date <= g_income_end_date
2204 AND pel.effective_end_date > = g_income_start_date;
2205
2206 lr_get_element_ids csr_get_element_ids%ROWTYPE;
2207
2208 CURSOR csr_get_element_type_id (csr_v_element_name VARCHAR2)
2209 IS
2210 SELECT pet.element_type_id
2211 ,pel.element_link_id
2212 FROM pay_element_types_f pet
2213 ,pay_element_links_f pel
2214 WHERE pet.element_name = csr_v_element_name
2215 AND pet.legislation_code = 'SE'
2216 AND pel.element_type_id = pet.element_type_id
2217 AND pel.business_group_id = g_business_group_id
2218 AND pet.effective_start_date <= g_income_end_date
2219 AND pet.effective_end_date > = g_income_start_date
2220 AND pel.effective_start_date <= g_income_end_date
2221 AND pel.effective_end_date > = g_income_start_date;
2222
2223 lr_get_element_type_id csr_get_element_type_id%ROWTYPE;
2224
2225 /*
2226 CURSOR csr_chk_A_taxcard (
2227 csr_v_input_value_id pay_element_entry_values_f.input_value_id%TYPE
2228 ,csr_v_link_id pay_element_entries_f.ELEMENT_LINK_ID%TYPE
2229 ,csr_v_type_id pay_element_entries_f.ELEMENT_TYPE_ID%TYPE
2230 ,csr_v_person_id NUMBER
2231 ) is
2232 SELECT count('Y') VALID
2233 from pay_element_entries_f pee
2234 , per_all_assignments_f paa
2235 , pay_element_entry_values_f peev
2236 where peev.screen_entry_value ='A'
2237 AND peev.element_entry_id = pee.element_entry_id
2238 AND peev.input_value_id = csr_v_input_value_id
2239 AND peev.EFFECTIVE_START_DATE = pee.EFFECTIVE_START_DATE
2240 AND peev.EFFECTIVE_END_DATE = pee.EFFECTIVE_END_DATE
2241 and pee.ELEMENT_LINK_ID = csr_v_link_id
2242 and paa.ASSIGNMENT_ID = pee.ASSIGNMENT_ID
2243 and pee.ELEMENT_TYPE_ID = csr_v_type_id
2244 and pee.ASSIGNMENT_ID = paa.assignment_id
2245 AND PAA.PERSON_ID = csr_v_person_id
2246 AND PAA.BUSINESS_GROUP_ID = g_business_group_id
2247 AND PAA.PRIMARY_FLAG = 'Y'
2248 AND pee.EFFECTIVE_START_DATE <= g_income_end_date AND pee.EFFECTIVE_END_DATE > = g_income_start_date
2249 AND paa.EFFECTIVE_START_DATE <= g_income_end_date AND paa.EFFECTIVE_END_DATE > = g_income_start_date
2250 AND paa.assignment_status_type_id IN
2251 (select assignment_status_type_id
2252 from per_assignment_status_types
2253 where per_system_status = 'ACTIVE_ASSIGN'
2254 and active_flag = 'Y'
2255 and (
2256 ( legislation_code is null
2257 and business_group_id is null
2258 )
2259 OR
2260 ( BUSINESS_GROUP_ID = g_business_group_id )
2261 )
2262 );
2263 */
2264 CURSOR csr_chk_a_taxcard (
2265 csr_v_input_value_id pay_element_entry_values_f.input_value_id%TYPE
2266 ,csr_v_link_id pay_element_entries_f.element_link_id%TYPE
2267 ,csr_v_type_id pay_element_entries_f.element_type_id%TYPE
2268 ,csr_v_prim_assignment_id NUMBER
2269 )
2270 IS
2271 SELECT COUNT ('Y') valid
2272 FROM pay_element_entries_f pee
2273 ,pay_element_entry_values_f peev
2274 WHERE peev.screen_entry_value = 'A'
2275 AND peev.element_entry_id = pee.element_entry_id
2276 AND peev.effective_start_date = pee.effective_start_date
2277 AND peev.effective_end_date = pee.effective_end_date
2278 AND peev.input_value_id = csr_v_input_value_id
2279 AND pee.element_link_id = csr_v_link_id
2280 AND pee.element_type_id = csr_v_type_id
2281 AND pee.assignment_id = csr_v_prim_assignment_id
2282 AND pee.effective_start_date <= g_income_end_date
2283 AND pee.effective_end_date > = g_income_start_date;
2284
2285 lr_chk_a_taxcard csr_chk_a_taxcard%ROWTYPE;
2286
2287 -- *****************************************************************************
2288 -- Income Statement Specification Details
2289 CURSOR csr_person_inc_stmt_spec (
2290 csr_v_person_id NUMBER
2291 ,csr_v_information_type per_people_extra_info.information_type%TYPE
2292 )
2293 IS
2294 SELECT pei_information1
2295 ,pei_information2
2296 ,pei_information3
2297 ,pei_information4
2298 ,pei_information5
2299 ,pei_information6
2300 ,pei_information7
2301 ,pei_information8
2302 ,pei_information9
2303 FROM per_people_extra_info
2304 WHERE person_id = csr_v_person_id
2305 AND information_type = csr_v_information_type;
2306
2307 lr_person_inc_stmt_spec csr_person_inc_stmt_spec%ROWTYPE;
2308
2309 -- *****************************************************************************
2310 CURSOR csr_get_ben_elem_type_id (
2311 csr_v_assignment_id NUMBER
2312 ,csr_v_elem_code VARCHAR2
2313 ,csr_v_category VARCHAR2
2314 )
2315 IS
2316 SELECT pet.element_type_id
2317 ,pel.element_link_id
2318 ,pee.element_entry_id
2319 FROM pay_element_types_f pet
2320 ,pay_element_links_f pel
2321 ,pay_element_entries_f pee
2322 WHERE pel.element_type_id = pet.element_type_id
2323 AND (pet.legislation_code = 'SE' OR pet.legislation_code IS NULL
2324 )
2325 AND pel.business_group_id = g_business_group_id
2326 AND pet.effective_start_date <= g_income_end_date
2327 AND pet.effective_end_date > = g_income_start_date
2328 AND pel.effective_start_date <= g_income_end_date
2329 AND pel.effective_end_date > = g_income_start_date
2330 AND pee.effective_start_date <= g_income_end_date
2331 AND pee.effective_end_date > = g_income_start_date
2332 AND pet.element_information1 = csr_v_elem_code
2333 AND pet.element_information_category = csr_v_category
2334 AND pee.element_link_id = pel.element_link_id
2335 AND pee.assignment_id = csr_v_assignment_id;
2336
2337 CURSOR csr_get_elem_processed (csr_v_element_entry_id NUMBER)
2338 IS
2339 SELECT 'Y' "PROCESSED"
2340 FROM pay_run_results prr
2341 ,pay_element_entries_f pee
2342 WHERE pee.element_entry_id =
2343 csr_v_element_entry_id
2344 --p_p_element_entry_id
2345 /*and p_effective_date* between pee.effective_start_date
2346 and pee.effective_end_date*/
2347 AND pee.effective_start_date BETWEEN g_income_start_date
2348 AND g_income_end_date
2349 AND pee.effective_end_date BETWEEN g_income_start_date
2350 AND g_income_end_date
2351 AND prr.source_id = pee.element_entry_id
2352 AND prr.entry_type = pee.entry_type
2353 AND prr.source_type = 'E'
2354 AND prr.status <> 'U'
2355 AND NOT EXISTS (
2356 SELECT 1
2357 FROM pay_run_results sub_rr
2358 WHERE sub_rr.source_id = prr.run_result_id
2359 AND sub_rr.source_type IN ('R', 'V'));
2360
2361 CURSOR csr_get_car_elem (csr_v_assignment_id NUMBER)
2362 IS
2363 SELECT pee.element_entry_id
2364 ,pet.element_name
2365 ,pee.effective_start_date
2366 ,pee.effective_end_date
2367 FROM pay_element_entries_f pee
2368 ,pay_element_types_f pet
2369 WHERE pet.element_name = 'Car Benefit'
2370 AND pet.legislation_code = 'SE'
2371 AND pee.assignment_id = csr_v_assignment_id
2372 AND pee.element_type_id = pet.element_type_id
2373 AND pee.effective_start_date <= g_income_end_date
2374 AND pee.effective_end_date >= g_income_start_date
2375 AND pet.effective_start_date <= g_income_end_date
2376 AND pet.effective_end_date >= g_income_start_date
2377 ORDER BY pee.effective_end_date DESC;
2378
2379 lr_get_car_elem csr_get_car_elem%ROWTYPE;
2380
2381 CURSOR csr_get_car_elem_details (
2382 --csr_v_assignment_id NUMBER , Not Needed now
2383 csr_v_ee_id NUMBER)
2384 IS
2385 SELECT pee.element_entry_id
2386 ,pee.effective_start_date
2387 ,pee.effective_end_date
2388 ,peev.screen_entry_value
2389 FROM pay_element_entries_f pee
2390 ,pay_input_values_f piv
2391 ,pay_element_entry_values_f peev
2392 WHERE pee.element_entry_id = csr_v_ee_id
2393 AND piv.element_type_id = piv.element_type_id
2394 AND piv.NAME = 'RSV Code'
2395 AND peev.element_entry_id = pee.element_entry_id
2396 AND peev.input_value_id = piv.input_value_id
2397 -- AND pee.ASSIGNMENT_ID = csr_v_assignment_id Not Needed now.
2398 AND pee.effective_start_date <= g_income_end_date
2399 AND pee.effective_end_date > = g_income_start_date
2400 AND piv.effective_start_date <= g_income_end_date
2401 AND piv.effective_end_date > = g_income_start_date;
2402
2403 lr_get_car_elem_details csr_get_car_elem_details%ROWTYPE;
2404 -- End of Cursors
2405 l_period_start_date DATE;
2406 l_period_end_date DATE;
2407 l_effective_end_date DATE;
2408 -- Cursor to pick up the Absence details
2409 --#########################################
2410
2411 -- End of place for Cursor which fetches the values to be archived
2412 BEGIN
2413 IF g_debug
2414 THEN
2415 hr_utility.set_location (' Entering Procedure ARCHIVE_CODE', 380);
2416 END IF;
2417
2418 --logger ('ARCHIVE_CODE ', '--------------------------------Started');
2419 --logger ('p_assignment_action_id ', p_assignment_action_id);
2420 --logger ('p_effective_date ', p_effective_date);
2421
2422 OPEN csr_get_person_id (p_assignment_action_id);
2423
2424 FETCH csr_get_person_id
2425 INTO lr_get_person_id;
2426
2427 CLOSE csr_get_person_id;
2428
2429 l_person_id := lr_get_person_id.person_id;
2430 l_effective_date :=
2431 GREATEST (lr_get_person_id.effective_start_date, g_income_start_date);
2432 l_assignment_id := lr_get_person_id.assignment_id;
2433 --logger ('l_person_id ', l_person_id);
2434 --logger ('l_effective_date ', l_effective_date);
2435 --logger ('l_assignment_id ', l_assignment_id);
2436
2437 OPEN csr_get_prim_assignments (l_person_id);
2438
2439 FETCH csr_get_prim_assignments
2440 INTO lr_get_prim_assignments;
2441
2442 CLOSE csr_get_prim_assignments;
2443
2444 l_primary_local_unit_id := lr_get_prim_assignments.segment2;
2445 l_primary_assignment_id := lr_get_prim_assignments.assignment_id;
2446
2447 -- *****************************************************************************
2448 -- TO pick up the Local Unit
2449 OPEN csr_get_segment2 (l_effective_date);
2450
2451 FETCH csr_get_segment2
2452 INTO lr_get_segment2;
2453
2454 CLOSE csr_get_segment2;
2455
2456 l_local_unit_id_fetched := lr_get_segment2.segment2;
2457
2458 OPEN csr_find_legal_employer (l_local_unit_id_fetched);
2459
2460 FETCH csr_find_legal_employer
2461 INTO lr_find_legal_employer;
2462
2463 CLOSE csr_find_legal_employer;
2464
2465 l_legal_employer_id_fetched := lr_find_legal_employer.legal_id;
2466 -- *****************************************************************************
2467 --logger ('==============PERSON================== ', '=');
2468
2469 -- *****************************************************************************
2470 -- TO pick up the PIN
2471 OPEN csr_get_person_details (l_person_id, l_effective_date);
2472
2473 FETCH csr_get_person_details
2474 INTO lr_get_person_details;
2475
2476 CLOSE csr_get_person_details;
2477 l_effective_end_date :=lr_get_person_details.effective_end_date;
2478 l_employee_pin := lr_get_person_details.national_identifier;
2479 --logger ('l_employee_pin ', l_employee_pin);
2480 l_employee_last_name := lr_get_person_details.last_name;
2481 --logger ('l_employee_last_name ', l_employee_last_name);
2482 l_employee_number := lr_get_person_details.employee_number;
2483 --logger ('l_employee_number ', l_employee_number);
2484
2485
2486 l_employee_name :=
2487 lr_get_person_details.last_name
2488 || ' '
2489 || lr_get_person_details.first_name;
2490 --logger ('l_employee_name ', l_employee_name);
2491 l_in_plain_writing_meaning := lr_get_person_details.territory_short_name;
2492 --logger ('l_In_plain_Writing_meaning ', l_in_plain_writing_meaning);
2493 l_in_plain_writing_code := lr_get_person_details.territory_code;
2494 --logger ('l_In_plain_Writing_code ', l_in_plain_writing_code);
2495
2496 -- *****************************************************************************
2497 OPEN csr_get_employee_address (l_person_id, l_effective_date);
2498
2499 FETCH csr_get_employee_address
2500 INTO lr_get_employee_address;
2501
2502 CLOSE csr_get_employee_address;
2503
2504 l_employees_address :=
2505 lr_get_employee_address.address_line1
2506 || ' '
2507 || lr_get_employee_address.address_line2
2508 || ' '
2509 || lr_get_employee_address.address_line3;
2510 l_employees_postalcode := lr_get_employee_address.postal_code;
2511 l_employee_postal_address :=
2512 lr_get_employee_address.territory_short_name;
2513
2514 -- *****************************************************************************
2515 OPEN csr_get_month_to_from (l_person_id, l_legal_employer_id_fetched);
2516
2517 FETCH csr_get_month_to_from
2518 INTO lr_get_month_to_from;
2519
2520 CLOSE csr_get_month_to_from;
2521
2522 l_month_from :=
2523 TO_CHAR (GREATEST (g_income_start_date
2524 ,lr_get_month_to_from.effective_start_date
2525 )
2526 ,'MM'
2527 );
2528 l_month_to :=
2529 TO_CHAR (LEAST (g_income_end_date
2530 ,lr_get_month_to_from.effective_end_date
2531 )
2532 ,'MM'
2533 );
2534 --logger ('l_month_from ', l_month_from);
2535 --logger ('l_month_to ', l_month_to);
2536 -- *****************************************************************************
2537 /* OPEN csr_Person_correction_date (l_person_id);
2538 FETCH csr_Person_correction_date INTO lr_Person_correction_date;
2539 CLOSE csr_Person_correction_date;
2540
2541 l_date_of_correction := lr_Person_correction_date.PEI_INFORMATION1;
2542 logger ('l_date_of_correction ', l_date_of_correction);
2543 */
2544 -- *****************************************************************************
2545 l_free_housing_other41_flag := 'N';
2546 l_interest_flag := 'N';
2547 l_other_benefits_flag := 'N';
2548 l_busi_travel_expenses_flag := 'N';
2549 l_acc_business_travels_flag := 'N';
2550 l_car_elem_end_date := NULL;
2551 l_car_elem_start_date := NULL;
2552 l_car_elem_entry_id := NULL;
2553 -- Amount of A-tax withheld
2554 -- With this Person id and Legal employer id.
2555 -- find all the assignment for this person for this legal employer
2556 -- for these assignments find the tax card element .
2557 -- if any of these elements is having value A-Tax Card
2558 -- populate this from " Employee Taxable Base PER_LE_YTD "
2559 l_a_tax_withheld_flag := 'N';
2560
2561 FOR row_get_assignments IN csr_get_assignments (l_person_id)
2562 LOOP
2563 -- *****************************************************************************
2564 -- A Tax card field
2565 -- *****************************************************************************
2566 OPEN csr_chk_valid_le_lu (row_get_assignments.segment2
2567 ,l_legal_employer_id_fetched
2568 );
2569
2570 FETCH csr_chk_valid_le_lu
2571 INTO lr_chk_valid_le_lu;
2572
2573 CLOSE csr_chk_valid_le_lu;
2574
2575 -- *****************************************************************************
2576 IF lr_chk_valid_le_lu.valid = 'Y'
2577 THEN
2578 /*
2579 -- *****************************************************************************
2580 -- l_A_TAX_WITHHELD_FLAG
2581 -- *****************************************************************************
2582 IF l_A_TAX_WITHHELD_FLAG <> 'Y'
2583 THEN
2584
2585 OPEN csr_get_element_ids ;
2586 FETCH csr_get_element_ids INTO lr_get_element_ids;
2587 CLOSE csr_get_element_ids;
2588
2589 OPEN csr_chk_A_taxcard(
2590 lr_get_element_ids.input_value_id
2591 , lr_get_element_ids.ELEMENT_LINK_ID
2592 , lr_get_element_ids.ELEMENT_TYPE_ID
2593 , l_primary_assignment_id
2594 ) ;
2595 FETCH csr_chk_A_taxcard INTO lr_chk_A_taxcard;
2596 CLOSE csr_chk_A_taxcard;
2597
2598 IF lr_chk_A_taxcard.VALID > 0
2599 THEN
2600 l_A_TAX_WITHHELD := --TO_CHAR
2601 round(get_defined_balance_value
2602 ('EMPLOYEE_TAX_PER_LE_YTD'
2603 , l_assignment_id
2604 , g_income_end_date
2605 , l_legal_employer_id_fetched
2606 , NULL
2607 )
2608 -- , '999999999D99'
2609 );
2610 l_A_TAX_WITHHELD_FLAG := 'Y';
2611 END IF;
2612
2613 END IF;
2614 */
2615 -- *****************************************************************************
2616 -- *****************************************************************************
2617 -- *****************************************************************************
2618 -- END OF A Tax card field
2619 -- *****************************************************************************
2620 --logger ('row_get_assignments.assignment_id ' ,row_get_assignments.assignment_id );
2621
2622 -- *****************************************************************************
2623 -- free_housing_other41
2624 -- *****************************************************************************
2625 IF l_free_housing_other41_flag <> 'Y'
2626 THEN
2627 FOR row_ben_elem_type_id IN
2628 csr_get_ben_elem_type_id
2629 (row_get_assignments.assignment_id
2630 ,'43'
2631 ,'SE_BENEFITS IN KIND'
2632 )
2633 LOOP
2634 --logger ('row_ben_elem_type_id ELEMENT_ENTRY_ID ' ,row_ben_elem_type_id.element_entry_id );
2635
2636 -- row_ben_elem_type_id.ELEMENT_TYPE_ID
2637 -- row_ben_elem_type_id.ELEMENT_ENTRY_ID
2638 IF l_free_housing_other41_flag <> 'Y'
2639 THEN
2640 FOR row_get_elem_processed IN
2641 csr_get_elem_processed
2642 (row_ben_elem_type_id.element_entry_id)
2643 LOOP
2644 IF row_get_elem_processed.processed = 'Y'
2645 THEN
2646 l_free_housing_other41_flag := 'Y';
2647 --logger ('free_housing_other41_flag ' ,l_free_housing_other41_flag );
2648 END IF;
2649
2650 EXIT WHEN l_free_housing_other41_flag = 'Y';
2651 END LOOP;
2652 END IF;
2653
2654 EXIT WHEN l_free_housing_other41_flag = 'Y';
2655 END LOOP;
2656 END IF;
2657
2658 -- *****************************************************************************
2659 -- END OF free_housing_other41
2660 -- *****************************************************************************
2661 -- *****************************************************************************
2662 -- l_interest
2663 -- *****************************************************************************
2664 IF l_interest_flag <> 'Y'
2665 THEN
2666 FOR row_ben_elem_type_id IN
2667 csr_get_ben_elem_type_id
2668 (row_get_assignments.assignment_id
2669 ,'44'
2670 ,'SE_BENEFITS IN KIND'
2671 )
2672 LOOP
2673 --logger ('row_ben_elem_type_id ELEMENT_ENTRY_ID ' ,row_ben_elem_type_id.element_entry_id );
2674
2675 -- row_ben_elem_type_id.ELEMENT_TYPE_ID
2676 -- row_ben_elem_type_id.ELEMENT_ENTRY_ID
2677 IF l_interest_flag <> 'Y'
2678 THEN
2679 FOR row_get_elem_processed IN
2680 csr_get_elem_processed
2681 (row_ben_elem_type_id.element_entry_id)
2682 LOOP
2683 IF row_get_elem_processed.processed = 'Y'
2684 THEN
2685 l_interest_flag := 'Y';
2686 --logger ('l_interest_flag ', l_interest_flag);
2687 END IF;
2688
2689 EXIT WHEN l_interest_flag = 'Y';
2690 END LOOP;
2691 END IF;
2692
2693 EXIT WHEN l_interest_flag = 'Y';
2694 END LOOP;
2695 END IF;
2696
2697 -- *****************************************************************************
2698 -- END OF l_interest
2699 -- *****************************************************************************
2700
2701 -- *****************************************************************************
2702 -- l_Other_benefits_flag
2703 -- *****************************************************************************
2704 IF l_other_benefits_flag <> 'Y'
2705 THEN
2706 FOR row_ben_elem_type_id IN
2707 csr_get_ben_elem_type_id
2708 (row_get_assignments.assignment_id
2709 ,'47'
2710 ,'SE_BENEFITS IN KIND'
2711 )
2712 LOOP
2713 --logger ('row_ben_elem_type_id ELEMENT_ENTRY_ID ' ,row_ben_elem_type_id.element_entry_id );
2714
2715 -- row_ben_elem_type_id.ELEMENT_TYPE_ID
2716 -- row_ben_elem_type_id.ELEMENT_ENTRY_ID
2717 IF l_other_benefits_flag <> 'Y'
2718 THEN
2719 FOR row_get_elem_processed IN
2720 csr_get_elem_processed
2721 (row_ben_elem_type_id.element_entry_id)
2722 LOOP
2723 IF row_get_elem_processed.processed = 'Y'
2724 THEN
2725 l_other_benefits_flag := 'Y';
2726 --logger ('l_Other_benefits_flag ' ,l_other_benefits_flag );
2727 END IF;
2728
2729 EXIT WHEN l_other_benefits_flag = 'Y';
2730 END LOOP;
2731 END IF;
2732
2733 EXIT WHEN l_other_benefits_flag = 'Y';
2734 END LOOP;
2735 END IF;
2736
2737 -- *****************************************************************************
2738 -- END OF l_Other_benefits_flag
2739 -- *****************************************************************************
2740
2741 -- *****************************************************************************
2742 -- l_Busi_travel_expenses_flag
2743 -- *****************************************************************************
2744 IF l_busi_travel_expenses_flag <> 'Y'
2745 THEN
2746 FOR row_ben_elem_type_id IN
2747 csr_get_ben_elem_type_id
2748 (row_get_assignments.assignment_id
2749 ,'55'
2750 ,'SE_TAXABLE EXPENSES'
2751 )
2752 LOOP
2753 --logger ('row_ben_elem_type_id ELEMENT_ENTRY_ID ' ,row_ben_elem_type_id.element_entry_id );
2754
2755 -- row_ben_elem_type_id.ELEMENT_TYPE_ID
2756 -- row_ben_elem_type_id.ELEMENT_ENTRY_ID
2757 IF l_busi_travel_expenses_flag <> 'Y'
2758 THEN
2759 FOR row_get_elem_processed IN
2760 csr_get_elem_processed
2761 (row_ben_elem_type_id.element_entry_id)
2762 LOOP
2763 IF row_get_elem_processed.processed = 'Y'
2764 THEN
2765 l_busi_travel_expenses_flag := 'Y';
2766 --logger ('l_Busi_travel_expenses_flag ' ,l_busi_travel_expenses_flag );
2767 END IF;
2768
2769 EXIT WHEN l_busi_travel_expenses_flag = 'Y';
2770 END LOOP;
2771 END IF;
2772
2773 EXIT WHEN l_busi_travel_expenses_flag = 'Y';
2774 END LOOP;
2775 END IF;
2776
2777 -- *****************************************************************************
2778 -- END OF l_Busi_travel_expenses_flag
2779 -- *****************************************************************************
2780
2781 -- *****************************************************************************
2782 -- l_Acc_business_travels_flag
2783 -- *****************************************************************************
2784 IF l_acc_business_travels_flag <> 'Y'
2785 THEN
2786 FOR row_ben_elem_type_id IN
2787 csr_get_ben_elem_type_id
2788 (row_get_assignments.assignment_id
2789 ,'56'
2790 ,'SE_TAXABLE EXPENSES'
2791 )
2792 LOOP
2793 --logger ('row_ben_elem_type_id ELEMENT_ENTRY_ID ' ,row_ben_elem_type_id.element_entry_id );
2794
2795 -- row_ben_elem_type_id.ELEMENT_TYPE_ID
2796 -- row_ben_elem_type_id.ELEMENT_ENTRY_ID
2797 IF l_acc_business_travels_flag <> 'Y'
2798 THEN
2799 FOR row_get_elem_processed IN
2800 csr_get_elem_processed
2801 (row_ben_elem_type_id.element_entry_id)
2802 LOOP
2803 IF row_get_elem_processed.processed = 'Y'
2804 THEN
2805 l_acc_business_travels_flag := 'Y';
2806 --logger ('l_Acc_business_travels_flag ' ,l_acc_business_travels_flag );
2807 END IF;
2808
2809 EXIT WHEN l_acc_business_travels_flag = 'Y';
2810 END LOOP;
2811 END IF;
2812
2813 EXIT WHEN l_acc_business_travels_flag = 'Y';
2814 END LOOP;
2815 END IF;
2816
2817 -- *****************************************************************************
2818 -- END OF l_Busi_travel_expenses_flag
2819 -- *****************************************************************************
2820
2821 -- *****************************************************************************
2822 -- CAR ELEMENT
2823 -- *****************************************************************************
2824 --logger ('FOR EACH.assignment_id ' ,row_get_assignments.assignment_id );
2825 lr_get_car_elem := NULL;
2826
2827 OPEN csr_get_car_elem (row_get_assignments.assignment_id);
2828
2829 FETCH csr_get_car_elem
2830 INTO lr_get_car_elem;
2831
2832 CLOSE csr_get_car_elem;
2833
2834 --logger ('lr_get_Car_elem.EFFECTIVE_END_DATE ' ,lr_get_car_elem.effective_end_date );
2835 --logger ('lr_get_Car_elem.EFFECTIVE_START_DATE ' ,lr_get_car_elem.effective_start_date );
2836 --logger ('lr_get_Car_elem.ELEMENT_ENTRY_ID ' ,lr_get_car_elem.element_entry_id );
2837
2838 /* For the firsttime the value has to be put in variables ;)*/
2839 IF l_car_elem_end_date IS NULL
2840 THEN
2841 l_car_elem_end_date := lr_get_car_elem.effective_end_date;
2842 l_car_elem_start_date := lr_get_car_elem.effective_start_date;
2843 l_car_elem_entry_id := lr_get_car_elem.element_entry_id;
2844 /* From the next-time the value has to be put in variables after comparing ;)*/
2845 ELSIF lr_get_car_elem.effective_end_date IS NOT NULL
2846 AND lr_get_car_elem.effective_end_date > l_car_elem_end_date
2847 THEN
2848 l_car_elem_end_date := lr_get_car_elem.effective_end_date;
2849 l_car_elem_start_date := lr_get_car_elem.effective_start_date;
2850 l_car_elem_entry_id := lr_get_car_elem.element_entry_id;
2851 END IF;
2852 -- *****************************************************************************
2853 -- END OF CAR ELEMENT
2854 -- *****************************************************************************
2855 END IF; -- for valid LE nad LE
2856 END LOOP;
2857
2858 --logger ('l_free_housing_flag ', l_free_housing_other41_flag);
2859 --logger ('l_interest_flag ', l_interest_flag);
2860 --logger ('l_Other_benefits_flag ', l_other_benefits_flag);
2861 --logger ('l_Busi_travel_expenses_flag ', l_busi_travel_expenses_flag);
2862 --logger ('l_Acc_business_travels_flag ', l_acc_business_travels_flag);
2863 --logger ('l_car_elem_end_date ', l_car_elem_end_date);
2864 --logger ('l_car_elem_start_date ', l_car_elem_start_date);
2865 --logger ('l_car_elem_entry_id ', l_car_elem_entry_id);
2866 --logger ('Balance ', 'Values');
2867 --logger ('l_assignment_id ', l_assignment_id);
2868 --logger ('g_income_end_date ', g_income_end_date);
2869 --logger ('l_legal_employer_id_fetched ', l_legal_employer_id_fetched);
2870 -- *****************************************************************************
2871 -- Employer Taxable Base PER_LE_YTD
2872 g_income_end_date := least(l_effective_end_date,g_income_end_date);
2873 --logger ('After Least g_income_end_date ', g_income_end_date);
2874 l_a_tax_withheld :=
2875 -- TO_CHAR
2876 round(get_defined_balance_value ('EMPLOYEE_TAX_PER_LE_YTD'
2877 ,l_assignment_id
2878 ,g_income_end_date
2879 ,l_legal_employer_id_fetched
2880 ,NULL
2881 )
2882 -- ,'999999999D99'
2883 );
2884 --logger ('l_A_TAX_WITHHELD ', l_a_tax_withheld);
2885 -- *****************************************************************************
2886 -- Gross salary get from Taxable Base PER_LE_YTD
2887 l_gross_salary :=
2888 -- TO_CHAR
2889 round(get_defined_balance_value ('GROSS_SALARY_PER_LE_YTD'
2890 ,l_assignment_id
2891 ,g_income_end_date
2892 ,l_legal_employer_id_fetched
2893 ,NULL
2894 )
2895 -- ,'999999999D99'
2896 );
2897 --logger ('l_gross_salary ', l_gross_salary);
2898 -- *****************************************************************************
2899 --Taxable benefits exclusive of employer-provided car and fuel
2900 -- get from Using Balance:
2901 -- Benefits in Kind PER_LE_YTD -
2902 -- ( Car Benefit in Kind PER_LE_YTD + Fuel Benefit in Kind PER_LE_YTD )
2903 l_ben_ytd :=
2904 -- TO_CHAR
2905 round(get_defined_balance_value ('BENEFITS_IN_KIND_PER_LE_YTD'
2906 ,l_assignment_id
2907 ,g_income_end_date
2908 ,l_legal_employer_id_fetched
2909 ,NULL
2910 )
2911 -- ,'999999999D99'
2912 );
2913 --logger ('l_ben_ytd ', l_ben_ytd);
2914 l_car_ben_ytd :=
2915 -- TO_CHAR
2916 round(get_defined_balance_value ('CAR_BENEFIT_IN_KIND_PER_LE_YTD'
2917 ,l_assignment_id
2918 ,g_income_end_date
2919 ,l_legal_employer_id_fetched
2920 ,NULL
2921 )
2922 -- ,'999999999D99'
2923 );
2924 --logger ('l_car_ben_YTD ', l_car_ben_ytd);
2925 l_fuel_ben_ytd :=
2926 -- TO_CHAR
2927 round(get_defined_balance_value ('FUEL_BENEFIT_IN_KIND_PER_LE_YTD'
2928 ,l_assignment_id
2929 ,g_income_end_date
2930 ,l_legal_employer_id_fetched
2931 ,NULL
2932 )
2933 -- ,'999999999D99'
2934 );
2935 --logger ('l_fuel_ben_YTD ', l_fuel_ben_ytd);
2936 l_tb_exclusive_car_fuel := l_ben_ytd - (l_car_ben_ytd + l_fuel_ben_ytd);
2937 --logger ('l_tb_exclusive_car_fuel ', l_tb_exclusive_car_fuel);
2938 -- *****************************************************************************
2939 -- Taxable benefit of employer-provided car exclusive of fuel
2940 -- Using Balance: Car Benefit in Kind PER_LE_YTD
2941 l_tb_exclusive_fuel := l_car_ben_ytd;
2942 --logger ('l_tb_exclusive_fuel ', l_tb_exclusive_fuel);
2943
2944 -- *****************************************************************************
2945 -- RSV-code of employer-provided car
2946 -- the "Car Benefit" Element input value RSV Code.
2947 -- For the given person id and Legal employer and income year
2948 -- all assignments under the above condition
2949 -- find last element entry in these assignments
2950 -- get the input value
2951 IF l_car_elem_entry_id IS NOT NULL
2952 THEN
2953 lr_get_car_elem_details := NULL;
2954
2955 OPEN csr_get_car_elem_details (l_car_elem_entry_id);
2956
2957 FETCH csr_get_car_elem_details
2958 INTO lr_get_car_elem_details;
2959
2960 CLOSE csr_get_car_elem_details;
2961
2962 l_rsv_code := lr_get_car_elem_details.screen_entry_value;
2963 l_car_elem_end_date := LEAST (l_car_elem_end_date, g_income_end_date);
2964 l_car_elem_start_date :=
2965 GREATEST (l_car_elem_start_date, g_income_start_date);
2966 ELSE
2967 l_rsv_code := NULL;
2968 END IF;
2969
2970 --logger ('l_rsv_code ', l_rsv_code);
2971 -- *****************************************************************************
2972 -- Number of months with employer-provided car
2973 -- Using Element: Car Benefit
2974 -- Using the Start - End Dates - Report Number of Months
2975 -- (Only, if less than 12 Months)
2976 -- Note: Part of month should be calculated as whole month.
2977 --logger ('l_car_elem_end_date ', l_car_elem_end_date);
2978 --logger ('l_car_elem_start_date ', l_car_elem_start_date);
2979 --logger ('last_day(l_car_elem_end_date) ' ,LAST_DAY (l_car_elem_end_date));
2980 --logger ('trunc(l_car_elem_start_date,) ' ,TRUNC (l_car_elem_start_date, 'MM') );
2981 l_number_of_months_car :=
2982 CEIL (MONTHS_BETWEEN (LAST_DAY (l_car_elem_end_date)
2983 ,TRUNC (l_car_elem_start_date, 'MM')
2984 )
2985 );
2986 --logger ('l_number_of_months_car ', l_number_of_months_car);
2987 -- *****************************************************************************
2988 -- Number of kilometers with mileage allowance for employer-provided car
2989 -- Using Balance : Cumulative Distance
2990 --
2991 l_number_of_kilometers :=
2992 -- TO_CHAR
2993 round(get_defined_balance_value ('CUMULATIVE_DISTANCE_PER_LE_YTD'
2994 ,l_assignment_id
2995 ,g_income_end_date
2996 ,l_legal_employer_id_fetched
2997 ,NULL
2998 )
2999 -- ,'999999999D99'
3000 );
3001 --logger ('l_number_of_kilometers ', l_number_of_kilometers);
3002 -- *****************************************************************************
3003 -- Employee's payment for employer-provided car.
3004 -- Using Balance : Car Benefit in Kind
3005 l_emp_payment_car := l_car_ben_ytd;
3006 -- *****************************************************************************
3007 -- Free fuel in connection with employer-provided car
3008 -- Using Balance: Fuel Benefit in Kind PER_LE_YTD
3009 l_free_fuel_car := l_fuel_ben_ytd;
3010 -- *****************************************************************************
3011 -- Compensation for expenses not ticked in boxes at codes 50-56
3012 -- Pick up from the EIT
3013
3014 --l_compensation_for_expenses := 0 ;
3015 --logger ('l_legal_employer_id_fetched ', l_legal_employer_id_fetched);
3016 --logger ('each_field_value ().FIELD_CODE(KU10_CFE) ' ,each_field_value (l_legal_employer_id_fetched).field_code ('KU10_CFE') );
3017 l_temp :=
3018 each_field_value (l_legal_employer_id_fetched).field_code ('KU10_CFE');
3019 l_compensation_for_expenses :=
3020 -- TO_CHAR
3021 round(get_balance_value (l_temp
3022 ,l_assignment_id
3023 ,g_income_end_date
3024 ,l_legal_employer_id_fetched
3025 ,NULL
3026 )
3027 -- ,'999999999D99'
3028 );
3029 --logger ('l_compensation_for_expenses ', l_compensation_for_expenses);
3030 -- *****************************************************************************
3031 -- Occupational pension
3032 -- Pick up from the EIT
3033
3034 --l_Occupational_pension := 0 ;
3035 --logger ('each_field_value ().FIELD_CODE(KU10_OCP) ' ,each_field_value (l_legal_employer_id_fetched).field_code ('KU10_OCP') );
3036 l_temp :=
3037 each_field_value (l_legal_employer_id_fetched).field_code ('KU10_OCP');
3038 l_occupational_pension :=
3039 -- TO_CHAR
3040 round(get_balance_value (l_temp
3041 ,l_assignment_id
3042 ,g_income_end_date
3043 ,l_legal_employer_id_fetched
3044 ,NULL
3045 )
3046 -- ,'999999999D99'
3047 );
3048 --logger ('l_Occupational_pension ', l_occupational_pension);
3049 -- *****************************************************************************
3050 --Other Taxable Remunerations
3051 --Taxable remunerations for which social security contributions are not paid.
3052 -- Pick up from the EIT
3053 l_other_tax_rem := 0;
3054 --logger ('each_field_value ().FIELD_CODE(KU10_OTR) ' ,each_field_value (l_legal_employer_id_fetched).field_code ('KU10_OTR') );
3055 l_temp :=
3056 each_field_value (l_legal_employer_id_fetched).field_code ('KU10_OTR');
3057 l_other_tax_rem :=
3058 -- TO_CHAR
3059 round(get_balance_value (l_temp
3060 ,l_assignment_id
3061 ,g_income_end_date
3062 ,l_legal_employer_id_fetched
3063 ,NULL
3064 )
3065 -- ,'999999999D99'
3066 );
3067 --logger ('l_other_tax_rem ', l_other_tax_rem);
3068 -- *****************************************************************************
3069 --Other Taxable Remunerations
3070 --Taxable remunerations for which social security contributions are not paid
3071 -- and which are not entitled to Special Job Deduction
3072 -- Pick up from the EIT
3073 l_tax_rem_without_sjd := 0;
3074 --logger ('each_field_value ().FIELD_CODE(KU10_OTRSJD) ' ,each_field_value (l_legal_employer_id_fetched).field_code ('KU10_OTRSJD') );
3075 l_temp :=
3076 each_field_value (l_legal_employer_id_fetched).field_code ('KU10_OTRSJD');
3077 l_tax_rem_without_sjd :=
3078 -- TO_CHAR
3079 round(get_balance_value (l_temp
3080 ,l_assignment_id
3081 ,g_income_end_date
3082 ,l_legal_employer_id_fetched
3083 ,NULL
3084 )
3085 -- ,'999999999D99'
3086 );
3087 --logger ('l_tax_rem_without_sjd ', l_tax_rem_without_sjd);
3088 -- *****************************************************************************
3089 -- *****************************************************************************
3090 --Benefits As Pension
3091 --If Benefit is Given As Pension the box will be ticked
3092 -- Pick up from the EIT
3093 -- Check Box
3094 l_benefit_as_pension := 0;
3095 --logger ('each_field_value ().FIELD_CODE(KU10_BENPEN) ' ,each_field_value (l_legal_employer_id_fetched).field_code ('KU10_BENPEN') );
3096 l_temp :=
3097 each_field_value (l_legal_employer_id_fetched).field_code ('KU10_BENPEN');
3098 l_benefit_as_pension :=
3099 -- TO_CHAR
3100 round(get_balance_value (l_temp
3101 ,l_assignment_id
3102 ,g_income_end_date
3103 ,l_legal_employer_id_fetched
3104 ,NULL
3105 )
3106 -- ,'999999999D99'
3107 );
3108 IF l_benefit_as_pension > 0
3109 THEN
3110 l_benefit_as_pension_flag := 'Y';
3111 ELSE
3112 l_benefit_as_pension_flag := 'N';
3113 END IF;
3114
3115 --logger ('l_benefit_as_pension ', l_benefit_as_pension);
3116 --logger ('l_benefit_as_pension_flag', l_benefit_as_pension_flag);
3117 -- *****************************************************************************
3118 --Taxable remunerations for which the employee pays social security contributions.
3119 -- Pick up from the EIT
3120 l_tax_rem_paid := 0;
3121 --logger ('each_field_value ().FIELD_CODE(KU10_TRSSC) ' ,each_field_value (l_legal_employer_id_fetched).field_code ('KU10_TRSSC') );
3122 l_temp :=
3123 each_field_value (l_legal_employer_id_fetched).field_code
3124 ('KU10_TRSSC');
3125 l_tax_rem_paid :=
3126 -- TO_CHAR
3127 round(get_balance_value (l_temp
3128 ,l_assignment_id
3129 ,g_income_end_date
3130 ,l_legal_employer_id_fetched
3131 ,NULL
3132 )
3133 -- ,'999999999D99'
3134 );
3135 --logger ('l_tax_rem_paid ', l_tax_rem_paid);
3136 -- *****************************************************************************
3137
3138 --Not taxable remunerations to foreign key persons working in Sweden
3139 -- Pick up from the EIT
3140 l_not_tax_rem := 0;
3141 --logger ('each_field_value ().FIELD_CODE(KU10_NTR) ' ,each_field_value (l_legal_employer_id_fetched).field_code ('KU10_NTR') );
3142 l_temp :=
3143 each_field_value (l_legal_employer_id_fetched).field_code ('KU10_NTR');
3144 l_not_tax_rem :=
3145 -- TO_CHAR
3146 round(get_balance_value (l_temp
3147 ,l_assignment_id
3148 ,g_income_end_date
3149 ,l_legal_employer_id_fetched
3150 ,NULL
3151 )
3152 -- ,'999999999D99'
3153 );
3154 --logger ('l_not_tax_rem ', l_not_tax_rem);
3155 -- *****************************************************************************
3156 -- Certain deductions
3157 -- Pick up from the EIT
3158 l_certain_deductions := 0;
3159 --logger ('each_field_value ().FIELD_CODE(KU10_CD) ' ,each_field_value (l_legal_employer_id_fetched).field_code ('KU10_CD') );
3160 l_temp :=
3161 each_field_value (l_legal_employer_id_fetched).field_code ('KU10_CD');
3162 l_certain_deductions :=
3163 -- TO_CHAR
3164 round(get_balance_value (l_temp
3165 ,l_assignment_id
3166 ,g_income_end_date
3167 ,l_legal_employer_id_fetched
3168 ,NULL
3169 )
3170 -- ,'999999999D99'
3171 );
3172 --logger ('l_Certain_deductions ', l_certain_deductions);
3173 -- *****************************************************************************
3174 -- Rent
3175 -- Pick up from the EIT
3176 l_rent := 0;
3177 --logger ('each_field_value ().FIELD_CODE(KU10_RENT) ' ,each_field_value (l_legal_employer_id_fetched).field_code ('KU10_RENT') );
3178 l_temp :=
3179 each_field_value (l_legal_employer_id_fetched).field_code
3180 ('KU10_RENT');
3181 l_rent :=
3182 -- TO_CHAR
3183 round(get_balance_value (l_temp
3184 ,l_assignment_id
3185 ,g_income_end_date
3186 ,l_legal_employer_id_fetched
3187 ,NULL
3188 )
3189 -- ,'999999999D99'
3190 );
3191 --logger ('l_rent ', l_rent);
3192
3193 -- *****************************************************************************
3194 -- Work site number allocated by the Central Bureau of Statistics (SCB)
3195 -- Swedish Local Unit Details EIT CFAR Number
3196 -- If a person is terminated during the year or have changed Local Unit,
3197 -- enter the last Local Unit number used for that employee.
3198
3199 -- Note: Local Unit of the Primary Assignment
3200 OPEN csr_local_unit_details (l_primary_local_unit_id);
3201
3202 FETCH csr_local_unit_details
3203 INTO lr_local_unit_details;
3204
3205 CLOSE csr_local_unit_details;
3206
3207 l_work_site_number := lr_local_unit_details.org_information2;
3208 --logger ('l_work_site_number ', l_work_site_number);
3209 -- *****************************************************************************
3210 -- Free housing 1- or 2-family house
3211 -- Check Box,
3212 -- if Element "Accommodation Benefit" contains value during the reporting Year.
3213 l_temp :=
3214 -- TO_CHAR
3215 round(get_defined_balance_value ('ACCOMMODATION_BENEFIT_PER_LE_YTD'
3216 ,l_assignment_id
3217 ,g_income_end_date
3218 ,l_legal_employer_id_fetched
3219 ,NULL
3220 )
3221 -- ,'999999999D99'
3222 );
3223 --logger ('ACCOMMODATION_BENEFIT_PER_LE_YTD ', l_temp);
3224
3225 IF l_temp > 0
3226 THEN
3227 l_free_housing := 'Y';
3228 ELSE
3229 l_free_housing := 'N';
3230 END IF;
3231
3232 --logger ('l_free_housing ', l_free_housing);
3233 -- *****************************************************************************
3234 -- Free meals
3235 -- Check Box,
3236 -- if Element "Food Benefit" contains value during the reporting Year.
3237 l_temp :=
3238 -- TO_CHAR
3239 round(get_defined_balance_value ('FOOD_BENEFIT_PER_LE_YTD'
3240 ,l_assignment_id
3241 ,g_income_end_date
3242 ,l_legal_employer_id_fetched
3243 ,NULL
3244 )
3245 -- ,'999999999D99'
3246 );
3247 --logger ('FOOD_BENEFIT_PER_LE_YTD ', l_temp);
3248
3249 IF l_temp > 0
3250 THEN
3251 l_free_meals := 'Y';
3252 ELSE
3253 l_free_meals := 'N';
3254 END IF;
3255
3256 --logger ('l_free_meals ', l_free_meals);
3257 -- *****************************************************************************
3258 -- Free housing, other than code 41
3259 -- Check Box,
3260 -- if "User Defined" Element (To be identified using the Element:
3261 -- Further Information Details, Benefit Type value matches "Free Housing")
3262 -- contains value during the reporting Year.
3263 l_free_housing_other41 := l_free_housing_other41_flag;
3264 --logger ('l_free_housing_other41 ', l_free_housing_other41);
3265 -- *****************************************************************************
3266 -- Interest
3267 -- Check Box,
3268 -- "User Defined" Element (To be identified using the Element:
3269 -- Further Information Details, Benefit Type value matches "Interest")
3270 -- contains value during the reporting Year.
3271 l_interest := l_interest_flag;
3272 --logger ('l_interest ', l_interest);
3273 -- *****************************************************************************
3274 -- Other benefits
3275 -- Check Box,
3276 -- "User Defined" Element (To be identified using the Element:
3277 -- Further Information Details, Benefit Type value matches "Other Benefits")
3278 -- contains value during the reporting Year.
3279 l_other_benefits := l_other_benefits_flag;
3280 --logger ('l_Other_benefits ', l_other_benefits);
3281 -- *****************************************************************************
3282 --Benefit has been adjusted
3283 -- Check Box,
3284 -- if "Reducement Value"
3285 -- exists in Elements Car OR Food OR Accommodation Benefit Elements.
3286 l_temp :=
3287 -- TO_CHAR
3288 round(get_defined_balance_value ('BENEFIT_IN_KIND_ADJUSTED_PER_LE_YTD'
3289 ,l_assignment_id
3290 ,g_income_end_date
3291 ,l_legal_employer_id_fetched
3292 ,NULL
3293 )
3294 -- ,'999999999D99'
3295 );
3296 --logger ('BENEFIT_IN_KIND_ADJUSTED_PER_LE_YTD ', l_temp);
3297
3298 IF l_temp > 0
3299 THEN
3300 l_benefit_adjusted := 'Y';
3301 ELSE
3302 l_benefit_adjusted := 'N';
3303 END IF;
3304
3305 --logger ('l_benefit_adjusted ', l_benefit_adjusted);
3306
3307
3308 -- *****************************************************************************
3309 --Mileage allowance
3310 -- Check Box,
3311 -- if Element "Mileage" contains "Cumulative Distance" value
3312 -- AND
3313 -- if "Mileage Employee" is NOT created for that period.
3314 l_temp :=
3315 -- TO_CHAR
3316 round(get_defined_balance_value ('MILEAGE_EMPLOYEE_PER_LE_YTD'
3317 ,l_assignment_id
3318 ,g_income_end_date
3319 ,l_legal_employer_id_fetched
3320 ,NULL
3321 )
3322 -- ,'999999999D99'
3323 );
3324 --logger ('MILEAGE_EMPLOYEE_PER_LE_YTD ', l_temp);
3325
3326 IF l_number_of_kilometers > 0 AND l_temp = 0
3327 THEN
3328 l_mileage_allowance := 'Y';
3329 ELSE
3330 l_mileage_allowance := 'N';
3331 END IF;
3332
3333 --logger ('l_Mileage_allowance ', l_mileage_allowance);
3334 -- *****************************************************************************
3335
3336 -- Per diem, Sweden
3337 -- Check Box,
3338 -- if Element "Per Diem Sweden" contains "Number of Days upto 3 Months" value
3339 -- AND
3340 -- if "Per Diem Sweden Employee" is NOT created for that period.
3341 l_temp_balance_value :=
3342 -- TO_CHAR
3343 round(get_defined_balance_value
3344 ('PER_DIEM_SWEDEN_DAYS_UPTO_3_MONTHS_PER_LE_YTD'
3345 ,l_assignment_id
3346 ,g_income_end_date
3347 ,l_legal_employer_id_fetched
3348 ,NULL
3349 )
3350 -- ,'999999999D99'
3351 );
3352 --logger ('PER_DIEM_SWEDEN_DAYS_UPTO_3_MONTHS_PER_LE_YTD ' ,l_temp_balance_value );
3353 l_temp :=
3354 -- TO_CHAR
3355 round(get_defined_balance_value ('PER_DIEM_SWEDEN_EMPLOYEE_PER_LE_YTD'
3356 ,l_assignment_id
3357 ,g_income_end_date
3358 ,l_legal_employer_id_fetched
3359 ,NULL
3360 )
3361 -- ,'999999999D99'
3362 );
3363 --logger ('PER_DIEM_SWEDEN_EMPLOYEE_PER_LE_YTD ', l_temp);
3364
3365 IF l_temp_balance_value > 0 AND l_temp = 0
3366 THEN
3367 l_per_diem_sweden := 'Y';
3368 ELSE
3369 l_per_diem_sweden := 'N';
3370 END IF;
3371
3372 --logger ('l_Per_diem_Sweden ', l_per_diem_sweden);
3373 -- *****************************************************************************
3374 -- Per diem, other countries
3375 -- Check Box,
3376 -- if Element "Per Diem Other Countries" contains "Number of Days upto 3 Months" value
3377 -- AND
3378 -- if "Per Diem Other Countries Employee" is NOT created for that period
3379 l_temp_balance_value :=
3380 -- TO_CHAR
3381 round(get_defined_balance_value
3382 ('PER_DIEM_OTHER_COUNTRIES_DAYS_UPTO_3_MONTHS_PER_LE_YTD'
3383 ,l_assignment_id
3384 ,g_income_end_date
3385 ,l_legal_employer_id_fetched
3386 ,NULL
3387 )
3388 -- ,'999999999D99'
3389 );
3390 --logger ('PER_DIEM_OTHER_COUNTRIES_DAYS_UPTO_3_MONTHS_PER_LE_YTD ' ,l_temp_balance_value );
3391 l_temp :=
3392 -- TO_CHAR
3393 round(get_defined_balance_value
3394 ('PER_DIEM_OTHER_COUNTRIES_EMPLOYEE_PER_LE_YTD'
3395 ,l_assignment_id
3396 ,g_income_end_date
3397 ,l_legal_employer_id_fetched
3398 ,NULL
3399 )
3400 -- ,'999999999D99'
3401 );
3402 --logger ('PER_DIEM_OTHER_COUNTRIES_EMPLOYEE_PER_LE_YTD ', l_temp);
3403
3404 IF l_temp_balance_value > 0 AND l_temp = 0
3405 THEN
3406 l_per_diem_other := 'Y';
3407 ELSE
3408 l_per_diem_other := 'N';
3409 END IF;
3410
3411 --logger ('l_Per_diem_other ', l_per_diem_other);
3412 -- *****************************************************************************
3413 -- Within Sweden
3414 -- Check Box,
3415 -- if Element "Per Diem Sweden" contains "Number of Days above 3 Months" value.
3416 l_temp :=
3417 -- TO_CHAR
3418 round(get_defined_balance_value
3419 ('PER_DIEM_SWEDEN_DAYS_ABOVE_3_MONTHS_PER_LE_YTD'
3420 ,l_assignment_id
3421 ,g_income_end_date
3422 ,l_legal_employer_id_fetched
3423 ,NULL
3424 )
3425 -- ,'999999999D99'
3426 );
3427 --logger ('PER_DIEM_SWEDEN_DAYS_ABOVE_3_MONTHS_PER_LE_YTD ', l_temp);
3428
3429 IF l_temp > 0
3430 THEN
3431 l_within_sweden := 'Y';
3432 ELSE
3433 l_within_sweden := 'N';
3434 END IF;
3435
3436 --logger ('l_Within_Sweden ', l_within_sweden);
3437 -- *****************************************************************************
3438 --Other countries
3439 -- Check Box,
3440 -- if Element "Per Diem Other Countries" contains "Number of Days above 3 Months" value.
3441 l_temp :=
3442 -- TO_CHAR
3443 round(get_defined_balance_value
3444 ('PER_DIEM_OTHER_COUNTRIES_DAYS_ABOVE_3_MONTHS_PER_LE_YTD'
3445 ,l_assignment_id
3446 ,g_income_end_date
3447 ,l_legal_employer_id_fetched
3448 ,NULL
3449 )
3450 -- ,'999999999D99'
3451 );
3452 --logger ('PER_DIEM_OTHER_COUNTRIES_DAYS_ABOVE_3_MONTHS_PER_LE_YTD ' ,l_temp );
3453
3454 IF l_temp > 0
3455 THEN
3456 l_other_countries := 'Y';
3457 ELSE
3458 l_other_countries := 'N';
3459 END IF;
3460
3461 --logger ('l_Other_countries ', l_other_countries);
3462 -- *****************************************************************************
3463 -- Business travel expenses
3464 -- Check Box,
3465 -- if "User Defined" Element (To be identified using the Element:
3466 -- Further Information Details, Expense Type value matches "Business Travel Expense")
3467 -- contains value during the reporting Year.
3468 l_business_travel_expenses := l_busi_travel_expenses_flag;
3469 --logger ('l_Business_travel_expenses ', l_business_travel_expenses);
3470 -- *****************************************************************************
3471
3472 -- Accomodation, business travels
3473 -- Check Box,
3474 -- if "User Defined" Element (To be identified using the Element:
3475 -- Further Information Details, Expense Type value matches "Accomodation Business Travel")
3476 -- contains value during the reporting Year.
3477 l_acc_business_travels := l_acc_business_travels_flag;
3478 --logger ('l_Acc_business_travels ', l_acc_business_travels);
3479 -- *****************************************************************************
3480 lr_person_inc_stmt_spec := NULL;
3481
3482 OPEN csr_person_inc_stmt_spec (l_person_id, 'SE_INC_STMT_SPEC_DETAILS');
3483
3484 FETCH csr_person_inc_stmt_spec
3485 INTO lr_person_inc_stmt_spec;
3486
3487 CLOSE csr_person_inc_stmt_spec;
3488
3489 -- *****************************************************************************
3490 -- Other benefits
3491 -- Person Form: Extra Information (Income Statement Specification Details)
3492 l_other_benefits_up65 := lr_person_inc_stmt_spec.pei_information1;
3493 --logger ('l_Other_benefits_UP65 ', l_other_benefits_up65);
3494 -- *****************************************************************************
3495 --Compensation for expenses
3496 -- Person Form: Extra Information (Income Statement Specification Details)
3497 l_compe_for_expenses_up66 := lr_person_inc_stmt_spec.pei_information2;
3498 --logger ('l_Compe_for_expenses_UP66 ', l_compe_for_expenses_up66);
3499 -- *****************************************************************************
3500 -- Taxable remunerations for which the employee pays social security contributions
3501 -- Person Form: Extra Information (Income Statement Specification Details)
3502 l_tax_rem_paid_up67 := lr_person_inc_stmt_spec.pei_information3;
3503 --logger ('l_tax_rem_paid_UP67 ', l_tax_rem_paid_up67);
3504 -- *****************************************************************************
3505 -- Taxable remunerations for which social security contributions are not paid.
3506 -- Person Form: Extra Information (Income Statement Specification Details)
3507 l_other_tax_rem_up68 := lr_person_inc_stmt_spec.pei_information4;
3508 --logger ('l_other_tax_rem_UP68 ', l_other_tax_rem_up68);
3509 -- *****************************************************************************
3510 -- Taxable remunerations for which social security contributions are not paid
3511 -- and which are not entitled to Special Job Deduction
3512 -- Person Form: Extra Information (Income Statement Specification Details)
3513 l_tax_rem_without_sjd_up69 := lr_person_inc_stmt_spec.pei_information6;
3514 --logger ('l_tax_rem_without_sjd_UP69 ', l_tax_rem_without_sjd_up69);
3515 -- *****************************************************************************
3516 -- Certain deductions
3517 -- Person Form: Extra Information (Income Statement Specification Details)
3518 l_certain_deductions_up70 := lr_person_inc_stmt_spec.pei_information5;
3519 --logger ('l_Certain_deductions_UP70 ', l_certain_deductions_up70);
3520 -- *****************************************************************************
3521 -- *****************************************************************************
3522 -- *****************************************************************************
3523 lr_person_inc_stmt_spec := NULL;
3524
3525 OPEN csr_person_inc_stmt_spec (l_person_id
3526 ,'SE_INC_STMT_DATA_CORRECTION'
3527 );
3528
3529 FETCH csr_person_inc_stmt_spec
3530 INTO lr_person_inc_stmt_spec;
3531
3532 CLOSE csr_person_inc_stmt_spec;
3533
3534 l_statement_type :=
3535 hr_general.decode_lookup ('SE_INCOME_STATEMENT_TYPE'
3536 ,lr_person_inc_stmt_spec.pei_information1
3537 );
3538 l_correction_date := lr_person_inc_stmt_spec.pei_information2;
3539 l_tax_country_code := lr_person_inc_stmt_spec.pei_information4;
3540 l_tax_country_meaning := get_country(lr_person_inc_stmt_spec.pei_information4);
3541 l_ftin := lr_person_inc_stmt_spec.pei_information5;
3542 l_work_country_meaning := get_country(lr_person_inc_stmt_spec.pei_information7);
3543 l_work_country_code := lr_person_inc_stmt_spec.pei_information7;
3544 l_work_period := lr_person_inc_stmt_spec.pei_information8;
3545 -- l_WOrk_period_meaning := hr_general.decode_lookup ('SE_INCOME_WORK_PERIOD',lr_Person_inc_stmt_spec.PEI_INFORMATION6);
3546 --logger ('l_statement_type ', l_statement_type);
3547 --logger ('l_correction_date ', l_correction_date);
3548 --logger ('l_tax_country_code ', l_tax_country_code);
3549 --logger ('l_tax_country_meaning ', l_tax_country_meaning);
3550 --logger ('l_FTIN ', l_ftin);
3551 --logger ('l_work_country_meaning ', l_work_country_meaning);
3552 --logger ('l_work_country_code ', l_work_country_code);
3553 -- logger ('l_WOrk_period meaning', l_WOrk_period_meaning);
3554 --logger ('l_WOrk_period_code', l_work_period);
3555 lr_person_inc_stmt_spec := NULL;
3556
3557 OPEN csr_person_inc_stmt_spec (l_person_id, 'SE_INC_STMT_KU14_SPECIAL');
3558
3559 FETCH csr_person_inc_stmt_spec
3560 INTO lr_person_inc_stmt_spec;
3561
3562 CLOSE csr_person_inc_stmt_spec;
3563
3564 l_emp_regulation_category_code := lr_person_inc_stmt_spec.pei_information1;
3565 l_emp_regulation_category :=
3566 hr_general.decode_lookup ('SE_EMPLOYER_REGULATION'
3567 ,lr_person_inc_stmt_spec.pei_information1
3568 );
3569 l_article_details := lr_person_inc_stmt_spec.pei_information2;
3570 --logger ('l_emp_regulation_category ', l_emp_regulation_category);
3571 --logger ('l_article_details ', l_article_details);
3572 -- *****************************************************************************
3573 -- Occupational pension ku 13
3574 -- Pick up from the EIT
3575 --logger ('each_field_value ().FIELD_CODE(KU13_OCP) ' ,each_field_value (l_legal_employer_id_fetched).field_code ('KU13_OCP') );
3576 l_temp :=
3577 each_field_value (l_legal_employer_id_fetched).field_code ('KU13_OCP');
3578 l_occupational_pension_ku13 :=
3579 -- TO_CHAR
3580 round(get_balance_value (l_temp
3581 ,l_assignment_id
3582 ,g_income_end_date
3583 ,l_legal_employer_id_fetched
3584 ,NULL
3585 )
3586 -- ,'999999999D99'
3587 );
3588 --logger ('l_Occupational_pension_ku13 ', l_occupational_pension_ku13);
3589 -- *****************************************************************************
3590 -- *****************************************************************************
3591 -- Compensation for benefit ku 13
3592 -- Pick up from the EIT
3593 --logger ('each_field_value ().FIELD_CODE(KU13_CFBHT) ' ,each_field_value (l_legal_employer_id_fetched).field_code ('KU13_CFBHT') );
3594 l_temp :=
3595 each_field_value (l_legal_employer_id_fetched).field_code
3596 ('KU13_CFBHT');
3597 l_compen_for_benefit_ku13 :=
3598 -- TO_CHAR
3599 round(get_balance_value (l_temp
3600 ,l_assignment_id
3601 ,g_income_end_date
3602 ,l_legal_employer_id_fetched
3603 ,NULL
3604 )
3605 -- ,'999999999D99'
3606 );
3607 --logger ('l_compen_for_benefit_ku13 ', l_compen_for_benefit_ku13);
3608 -- *****************************************************************************
3609
3610 -- *****************************************************************************
3611 -- Taxable remunerations for social security contributions (KU13)
3612 -- Pick up from the EIT
3613 --logger ('each_field_value ().FIELD_CODE(KU13_TRSSC) ' ,each_field_value (l_legal_employer_id_fetched).field_code ('KU13_TRSSC') );
3614 l_temp :=
3615 each_field_value (l_legal_employer_id_fetched).field_code
3616 ('KU13_TRSSC');
3617 l_tax_rem_ssc_ku13 :=
3618 -- TO_CHAR
3619 round(get_balance_value (l_temp
3620 ,l_assignment_id
3621 ,g_income_end_date
3622 ,l_legal_employer_id_fetched
3623 ,NULL
3624 )
3625 -- ,'999999999D99'
3626 );
3627 --logger ('l_tax_rem_ssc_ku13 ', l_tax_rem_ssc_ku13);
3628 -- *****************************************************************************
3629
3630 -- *****************************************************************************
3631 -- Taxable remunerations for social security contributions (KU13)
3632 -- Pick up from the EIT
3633 --logger ('each_field_value ().FIELD_CODE(KU14_TRSSC) ' ,each_field_value (l_legal_employer_id_fetched).field_code ('KU14_TRSSC') );
3634 l_temp :=
3635 each_field_value (l_legal_employer_id_fetched).field_code
3636 ('KU14_TRSSC');
3637 l_tax_rem_ssc_ku14 :=
3638 -- TO_CHAR
3639 round(get_balance_value (l_temp
3640 ,l_assignment_id
3641 ,g_income_end_date
3642 ,l_legal_employer_id_fetched
3643 ,NULL
3644 )
3645 -- ,'999999999D99'
3646 );
3647 --logger ('l_tax_rem_ssc_ku14 ', l_tax_rem_ssc_ku14);
3648 -- *****************************************************************************
3649
3650 -- *****************************************************************************
3651 -- Occupational pension ku 14
3652 -- Pick up from the EIT
3653 --logger ('each_field_value ().FIELD_CODE(KU14_OCP) ' ,each_field_value (l_legal_employer_id_fetched).field_code ('KU14_OCP') );
3654 l_temp :=
3655 each_field_value (l_legal_employer_id_fetched).field_code ('KU14_OCP');
3656 l_occupational_pension_ku14 :=
3657 -- TO_CHAR
3658 round(get_balance_value (l_temp
3659 ,l_assignment_id
3660 ,g_income_end_date
3661 ,l_legal_employer_id_fetched
3662 ,NULL
3663 )
3664 -- ,'999999999D99'
3665 );
3666 --logger ('l_Occupational_pension_ku14 ', l_occupational_pension_ku14);
3667 -- *****************************************************************************
3668 -- *****************************************************************************
3669
3670 --Not taxable remunerations ku 14
3671 -- Pick up from the EIT
3672 l_not_tax_rem_ku14 := 0;
3673 --logger ('each_field_value ().FIELD_CODE(KU14_NTR) ' ,each_field_value (l_legal_employer_id_fetched).field_code ('KU14_NTR') );
3674 l_temp :=
3675 each_field_value (l_legal_employer_id_fetched).field_code ('KU14_NTR');
3676 l_not_tax_rem_ku14 :=
3677 -- TO_CHAR
3678 round(get_balance_value (l_temp
3679 ,l_assignment_id
3680 ,g_income_end_date
3681 ,l_legal_employer_id_fetched
3682 ,NULL
3683 )
3684 -- ,'999999999D99'
3685 );
3686 --logger ('l_not_tax_rem_ku14 ', l_not_tax_rem_ku14);
3687 -- *****************************************************************************
3688 -- *****************************************************************************
3689 --Other Taxable Remunerations KU 14
3690
3691 -- Pick up from the EIT
3692 l_other_tax_rem_ku14 := 0;
3693 --logger ('each_field_value ().FIELD_CODE(KU14_OTR) ' ,each_field_value (l_legal_employer_id_fetched).field_code ('KU14_OTR') );
3694 l_temp :=
3695 each_field_value (l_legal_employer_id_fetched).field_code ('KU14_OTR');
3696 l_other_tax_rem_ku14 :=
3697 -- TO_CHAR
3698 round(get_balance_value (l_temp
3699 ,l_assignment_id
3700 ,g_income_end_date
3701 ,l_legal_employer_id_fetched
3702 ,NULL
3703 )
3704 -- ,'999999999D99'
3705 );
3706 --logger ('l_other_tax_rem_ku14 ', l_other_tax_rem_ku14);
3707 -- *****************************************************************************
3708 -- Compensation for expenses not ticked in boxes at codes 50-56
3709 -- Pick up from the EIT KU 14
3710
3711 --l_compensation_for_expenses := 0 ;
3712 --logger ('l_legal_employer_id_fetched ', l_legal_employer_id_fetched);
3713 --logger ('each_field_value ().FIELD_CODE(KU14_CFE) ' ,each_field_value (l_legal_employer_id_fetched).field_code ('KU14_CFE') );
3714 l_temp :=
3715 each_field_value (l_legal_employer_id_fetched).field_code ('KU14_CFE');
3716 l_compe_for_expenses_ku14 :=
3717 -- TO_CHAR
3718 round(get_balance_value (l_temp
3719 ,l_assignment_id
3720 ,g_income_end_date
3721 ,l_legal_employer_id_fetched
3722 ,NULL
3723 )
3724 -- ,'999999999D99'
3725 );
3726 --logger ('l_compe_for_expenses_ku14 ', l_compe_for_expenses_ku14);
3727 -- *****************************************************************************
3728
3729 -- *****************************************************************************
3730 --logger ('###############PERSON ENDED##############======== ', '=');
3731
3732 -- End of Pickingup the Data
3733 BEGIN
3734 /*
3735 SELECT 1
3736 INTO l_flag
3737 FROM pay_action_information
3738 WHERE action_information_category = 'EMEA REPORT INFORMATION'
3739 AND action_information1 = 'PYSEINSA'
3740 AND action_context_id = p_assignment_action_id;
3741 EXCEPTION
3742 WHEN NO_DATA_FOUND
3743 THEN
3744 */
3745 --logger ('g_payroll_action_id', g_payroll_action_id);
3746 pay_action_information_api.create_action_information
3747 (p_action_information_id => l_action_info_id
3748 ,p_action_context_id => p_assignment_action_id
3749 ,p_action_context_type => 'AAP'
3750 ,p_object_version_number => l_ovn
3751 ,p_effective_date => l_effective_date
3752 ,p_source_id => NULL
3753 ,p_source_text => NULL
3754 ,p_action_information_category => 'EMEA REPORT INFORMATION'
3755 ,p_action_information1 => 'PYSEINSA'
3756 ,p_action_information2 => 'PERSON'
3757 ,p_action_information3 => g_payroll_action_id
3758 ,p_action_information4 => l_statement_type
3759 ,p_action_information5 => l_employee_number
3760 ,p_action_information6 => l_employee_pin
3761 ,p_action_information7 => l_employee_last_name
3762 ,p_action_information8 => NULL
3763 ,p_action_information9 => NULL
3764 ,p_action_information10 => NULL
3765 ,p_action_information11 => NULL
3766 ,p_action_information12 => NULL
3767 ,p_action_information13 => NULL
3768 ,p_action_information14 => NULL
3769 ,p_action_information15 => NULL
3770 ,p_action_information16 => NULL
3771 ,p_action_information17 => NULL
3772 ,p_action_information18 => NULL
3773 ,p_action_information19 => NULL
3774 ,p_action_information20 => NULL
3775 ,p_action_information21 => NULL
3776 ,p_action_information22 => NULL
3777 ,p_action_information23 => NULL
3778 ,p_action_information24 => NULL
3779 ,p_action_information25 => NULL
3780 ,p_action_information26 => NULL
3781 ,p_action_information27 => NULL
3782 ,p_action_information28 => NULL
3783 ,p_action_information29 => l_legal_employer_id_fetched
3784 ,p_action_information30 => l_person_id
3785 ,p_assignment_id => l_assignment_id
3786 );
3787 --logger ('l_action_info_id', l_action_info_id);
3788
3789 pay_action_information_api.create_action_information
3790 (p_action_information_id => l_action_info_id
3791 ,p_action_context_id => p_assignment_action_id
3792 ,p_action_context_type => 'AAP'
3793 ,p_object_version_number => l_ovn
3794 ,p_effective_date => l_effective_date
3795 ,p_source_id => NULL
3796 ,p_source_text => NULL
3797 ,p_action_information_category => 'EMEA REPORT INFORMATION'
3798 ,p_action_information1 => 'PYSEINSA'
3799 ,p_action_information2 => 'PERSON1'
3800 ,p_action_information3 => g_payroll_action_id
3801 ,p_action_information4 => NVL (l_statement_type
3802 ,'KU10'
3803 )
3804 ,p_action_information5 => l_month_from
3805 ,p_action_information6 => l_month_to
3806 ,p_action_information7 => l_employee_pin
3807 ,p_action_information8 => l_employee_name
3808 ,p_action_information9 => l_correction_date
3809 ,p_action_information10 => l_work_site_number
3810 ,p_action_information11 => fnd_number.number_to_canonical
3811 (l_a_tax_withheld)
3812 ,p_action_information12 => fnd_number.number_to_canonical
3813 (l_gross_salary)
3814 ,p_action_information13 => fnd_number.number_to_canonical
3815 (l_tb_exclusive_car_fuel)
3816 ,p_action_information14 => l_free_housing
3817 ,p_action_information15 => l_free_meals
3818 ,p_action_information16 => l_free_housing_other41
3819 ,p_action_information17 => l_interest
3820 ,p_action_information18 => l_other_benefits
3821 ,p_action_information19 => l_benefit_adjusted
3822 ,p_action_information20 => l_tb_exclusive_fuel
3823 ,p_action_information21 => l_rsv_code
3824 ,p_action_information22 => l_number_of_months_car
3825 ,p_action_information23 => l_number_of_kilometers
3826 ,p_action_information24 => l_emp_payment_car
3827 ,p_action_information25 => l_free_fuel_car
3828 ,p_action_information26 => l_employees_address
3829 ,p_action_information27 => l_employees_postalcode
3830 ,p_action_information28 => l_employee_postal_address
3831 ,p_action_information29 => l_legal_employer_id_fetched
3832 ,p_action_information30 => l_person_id
3833 ,p_assignment_id => l_assignment_id
3834 );
3835 --logger ('l_action_info_id', l_action_info_id);
3836 pay_action_information_api.create_action_information
3837 (p_action_information_id => l_action_info_id
3838 ,p_action_context_id => p_assignment_action_id
3839 ,p_action_context_type => 'AAP'
3840 ,p_object_version_number => l_ovn
3841 ,p_effective_date => l_effective_date
3842 ,p_source_id => NULL
3843 ,p_source_text => NULL
3844 ,p_action_information_category => 'EMEA REPORT INFORMATION'
3845 ,p_action_information1 => 'PYSEINSA'
3846 ,p_action_information2 => 'PERSON2'
3847 ,p_action_information3 => g_payroll_action_id
3848 ,p_action_information4 => fnd_number.number_to_canonical
3849 (l_compensation_for_expenses
3850 )
3851 ,p_action_information5 => l_mileage_allowance
3852 ,p_action_information6 => l_per_diem_sweden
3853 ,p_action_information7 => l_per_diem_other
3854 ,p_action_information8 => l_busi_travel_expenses_flag
3855 ,p_action_information9 => l_acc_business_travels_flag
3856 ,p_action_information10 => l_within_sweden
3857 ,p_action_information11 => l_other_countries
3858 ,p_action_information12 => fnd_number.number_to_canonical
3859 (l_occupational_pension)
3860 ,p_action_information13 => fnd_number.number_to_canonical
3861 (l_other_tax_rem)
3862 ,p_action_information14 => fnd_number.number_to_canonical
3863 (l_tax_rem_paid)
3864 ,p_action_information15 => fnd_number.number_to_canonical
3865 (l_certain_deductions)
3866 ,p_action_information16 => l_other_benefits_up65
3867 ,p_action_information17 => l_compe_for_expenses_up66
3868 ,p_action_information18 => l_tax_rem_paid_up67
3869 ,p_action_information19 => l_other_tax_rem_up68
3870 ,p_action_information20 => l_certain_deductions_up70
3871 ,p_action_information21 => fnd_number.number_to_canonical
3872 (l_not_tax_rem)
3873 ,p_action_information22 => fnd_number.number_to_canonical
3874 (l_rent)
3875 ,p_action_information23 => fnd_number.number_to_canonical
3876 (l_tax_rem_without_sjd) -- EOY 2008
3877 ,p_action_information24 =>l_tax_rem_without_sjd_up69 --EOY 2008
3878 ,p_action_information25 => l_benefit_as_pension_flag --EOY 2008
3879 ,p_action_information26 => NULL
3880 ,p_action_information27 => NULL
3881 ,p_action_information28 => NULL
3882 ,p_action_information29 => NULL
3883 ,p_action_information30 => l_person_id
3884 ,p_assignment_id => l_assignment_id
3885 );
3886 --logger ('l_action_info_id', l_action_info_id);
3887 pay_action_information_api.create_action_information
3888 (p_action_information_id => l_action_info_id
3889 ,p_action_context_id => p_assignment_action_id
3890 ,p_action_context_type => 'AAP'
3891 ,p_object_version_number => l_ovn
3892 ,p_effective_date => l_effective_date
3893 ,p_source_id => NULL
3894 ,p_source_text => NULL
3895 ,p_action_information_category => 'EMEA REPORT INFORMATION'
3896 ,p_action_information1 => 'PYSEINSA'
3897 ,p_action_information2 => 'PERSON3'
3898 ,p_action_information3 => g_payroll_action_id
3899 ,p_action_information4 => l_ftin
3900 ,p_action_information5 => l_tax_country_code
3901 ,p_action_information6 => fnd_number.number_to_canonical
3902 (l_occupational_pension_ku13
3903 )
3904 ,p_action_information7 => fnd_number.number_to_canonical
3905 (l_tax_rem_ssc_ku13)
3906 ,p_action_information8 => fnd_number.number_to_canonical
3907 (l_compen_for_benefit_ku13
3908 )
3909 ,p_action_information9 => l_work_country_code
3910 ,p_action_information10 => l_in_plain_writing_code
3911 ,p_action_information11 => fnd_number.number_to_canonical
3912 (l_occupational_pension_ku14
3913 )
3914 ,p_action_information12 => fnd_number.number_to_canonical
3915 (l_other_tax_rem_ku14)
3916 ,p_action_information13 => fnd_number.number_to_canonical
3917 (l_tax_rem_ssc_ku14)
3918 ,p_action_information14 => fnd_number.number_to_canonical
3919 (l_not_tax_rem_ku14)
3920 ,p_action_information15 => l_work_period
3921 ,p_action_information16 => l_emp_regulation_category
3922 ,p_action_information17 => l_article_details
3923 ,p_action_information18 => l_work_country_meaning
3924 ,p_action_information19 => l_in_plain_writing_meaning
3925 ,p_action_information20 => l_tax_country_meaning
3926 ,p_action_information21 => fnd_number.number_to_canonical
3927 (l_compe_for_expenses_ku14
3928 )
3929 ,p_action_information22 => l_emp_regulation_category_code
3930 ,p_action_information23 => NULL
3931 ,p_action_information24 => NULL
3932 ,p_action_information25 => NULL
3933 ,p_action_information26 => NULL
3934 ,p_action_information27 => NULL
3935 ,p_action_information28 => NULL
3936 ,p_action_information29 => NULL
3937 ,p_action_information30 => l_person_id
3938 ,p_assignment_id => l_assignment_id
3939 );
3940 --logger ('l_action_info_id', l_action_info_id);
3941 --logger('l_action_info_id',l_action_info_id);
3942
3943 /* WHEN OTHERS
3944 THEN
3945 NULL;
3946 */
3947 END;
3948
3949 --logger ('ARCHIVE_CODE ' ,'::::::::::::::::::::::::::::::::::::::::Ended');
3950 END archive_code;
3951
3952 --- Report XML generating code
3953 PROCEDURE writetoclob (p_xfdf_clob OUT NOCOPY CLOB)
3954 IS
3955 l_xfdf_string CLOB;
3956 l_str1 VARCHAR2 (1000);
3957 l_str2 VARCHAR2 (20);
3958 l_str3 VARCHAR2 (20);
3959 l_str4 VARCHAR2 (20);
3960 l_str5 VARCHAR2 (20);
3961 l_str6 VARCHAR2 (30);
3962 l_str7 VARCHAR2 (1000);
3963 l_str8 VARCHAR2 (240);
3964 l_str9 VARCHAR2 (240);
3965 l_str10 VARCHAR2 (20);
3966 l_str11 VARCHAR2 (20);
3967 current_index PLS_INTEGER;
3968 l_iana_charset VARCHAR2 (50);
3969 BEGIN
3970 l_iana_charset := hr_se_utility.get_iana_charset;
3971 hr_utility.set_location ('Entering WritetoCLOB ', 70);
3972 l_str1 :=
3973 '<?xml version="1.0" encoding="'
3974 || l_iana_charset
3975 || '"?> <ROOT><INSR>';
3976 l_str2 := '<';
3977 l_str3 := '>';
3978 l_str4 := '</';
3979 l_str5 := '>';
3980 l_str6 := '</INSR></ROOT>';
3981 l_str7 :=
3982 '<?xml version="1.0" encoding="'
3983 || l_iana_charset
3984 || '"?> <ROOT></ROOT>';
3985 l_str10 := '<INSR>';
3986 l_str11 := '</INSR>';
3987 DBMS_LOB.createtemporary (l_xfdf_string, FALSE, DBMS_LOB.CALL);
3988 DBMS_LOB.OPEN (l_xfdf_string, DBMS_LOB.lob_readwrite);
3989 current_index := 0;
3990
3991 IF gins_data.COUNT > 0
3992 THEN
3993 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str1), l_str1);
3994
3995 FOR table_counter IN gins_data.FIRST .. gins_data.LAST
3996 LOOP
3997 l_str8 := gins_data (table_counter).tagname;
3998 l_str9 := gins_data (table_counter).tagvalue;
3999
4000 IF l_str9 IN
4001 ('LEGAL_EMPLOYER'
4002 ,'LE_DETAILS'
4003 ,'EMPLOYEES'
4004 ,'PERSON'
4005 ,'LE_DETAILS_END'
4006 ,'PERSON_END'
4007 ,'EMPLOYEES_END'
4008 ,'LEGAL_EMPLOYER_END'
4009 ,'INCOME_STATEMENT_END'
4010 ,'INCOME_STATEMENT'
4011 ,'INFO_KU_END'
4012 ,'INFO_KU'
4013 ,'KU10_PERSON_END'
4014 ,'KU13_PERSON_END'
4015 ,'KU14_PERSON_END'
4016 ,'KU10_PERSON'
4017 ,'KU13_PERSON'
4018 ,'KU14_PERSON'
4019 ,'LE_ADDRESS_END'
4020 ,'LE_ADDRESS'
4021 )
4022 THEN
4023 IF l_str9 IN
4024 ('LEGAL_EMPLOYER'
4025 ,'LE_DETAILS'
4026 ,'EMPLOYEES'
4027 ,'PERSON'
4028 ,'INCOME_STATEMENT'
4029 ,'INFO_KU'
4030 ,'KU10_PERSON'
4031 ,'KU13_PERSON'
4032 ,'KU14_PERSON'
4033 ,'LE_ADDRESS'
4034 )
4035 THEN
4036 DBMS_LOB.writeappend (l_xfdf_string
4037 ,LENGTH (l_str2)
4038 ,l_str2
4039 );
4040 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str8)
4041 ,l_str8);
4042 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str3)
4043 ,l_str3);
4044 ELSE
4045 DBMS_LOB.writeappend (l_xfdf_string
4046 ,LENGTH (l_str4)
4047 ,l_str4
4048 );
4049 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str8)
4050 ,l_str8);
4051 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str5)
4052 ,l_str5);
4053 END IF;
4054 ELSE
4055 IF l_str9 IS NOT NULL
4056 THEN
4057 DBMS_LOB.writeappend (l_xfdf_string
4058 ,LENGTH (l_str2)
4059 ,l_str2
4060 );
4061 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str8)
4062 ,l_str8);
4063 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str3)
4064 ,l_str3);
4065 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str9)
4066 ,l_str9);
4067 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str4)
4068 ,l_str4);
4069 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str8)
4070 ,l_str8);
4071 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str5)
4072 ,l_str5);
4073 ELSE
4074 DBMS_LOB.writeappend (l_xfdf_string
4075 ,LENGTH (l_str2)
4076 ,l_str2
4077 );
4078 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str8)
4079 ,l_str8);
4080 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str3)
4081 ,l_str3);
4082 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str4)
4083 ,l_str4);
4084 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str8)
4085 ,l_str8);
4086 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str5)
4087 ,l_str5);
4088 END IF;
4089 END IF;
4090 END LOOP;
4091
4092 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str6), l_str6);
4093 ELSE
4094 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str7), l_str7);
4095 END IF;
4096
4097 p_xfdf_clob := l_xfdf_string;
4098 hr_utility.set_location ('Leaving WritetoCLOB ', 40);
4099 EXCEPTION
4100 WHEN OTHERS
4101 THEN
4102 hr_utility.TRACE ('sqlerrm ' || SQLERRM);
4103 hr_utility.raise_error;
4104 END writetoclob;
4105 PROCEDURE get_xml_for_report (
4106 p_business_group_id IN NUMBER
4107 ,p_payroll_action_id IN VARCHAR2
4108 ,p_template_name IN VARCHAR2
4109 ,p_xml OUT NOCOPY CLOB
4110 )
4111 IS
4112 --Variables needed for the report
4113 l_counter NUMBER := 0;
4114 l_payroll_action_id pay_action_information.action_information1%TYPE;
4115
4116 --Cursors needed for report
4117 CURSOR csr_all_legal_employer (
4118 csr_v_pa_id pay_action_information.action_context_id%TYPE
4119 )
4120 IS
4121 SELECT *
4122 FROM pay_action_information
4123 WHERE action_context_type = 'PA'
4124 AND action_context_id = csr_v_pa_id
4125 AND action_information_category = 'EMEA REPORT INFORMATION'
4126 AND action_information1 = 'PYSEINSA'
4127 AND action_information2 = 'LE';
4128
4129 CURSOR csr_media_provider (
4130 csr_v_pa_id pay_action_information.action_context_id%TYPE
4131 )
4132 IS
4133 SELECT *
4134 FROM pay_action_information
4135 WHERE action_context_type = 'PA'
4136 AND action_context_id = csr_v_pa_id
4137 AND action_information_category = 'EMEA REPORT INFORMATION'
4138 AND action_information1 = 'PYSEINSA'
4139 AND action_information2 = 'MP';
4140
4141 lr_media_provider csr_media_provider%ROWTYPE;
4142
4143 CURSOR csr_report_details (
4144 csr_v_pa_id pay_action_information.action_context_id%TYPE
4145 )
4146 IS
4147 SELECT *
4148 FROM pay_action_information
4149 WHERE action_context_type = 'PA'
4150 AND action_context_id = csr_v_pa_id
4151 AND action_information_category = 'EMEA REPORT DETAILS'
4152 AND action_information1 = 'PYSEINSA';
4153
4154 lr_report_details csr_report_details%ROWTYPE;
4155
4156 CURSOR csr_all_employees_under_le (
4157 csr_v_pa_id pay_action_information.action_information3%TYPE
4158 ,csr_v_le_id pay_action_information.action_information15%TYPE
4159 ,csr_v_stmt_type pay_action_information.action_information4%TYPE
4160 ,csr_v_sort_order pay_action_information.action_information4%TYPE
4161 )
4162 IS
4163 SELECT *
4164 FROM pay_action_information
4165 WHERE action_context_type = 'AAP'
4166 AND action_information_category = 'EMEA REPORT INFORMATION'
4167 AND action_information1 = 'PYSEINSA'
4168 AND action_information2 = 'PERSON'
4169 AND action_information3 = csr_v_pa_id
4170 AND action_information4 = csr_v_stmt_type
4171 AND action_information29 = csr_v_le_id
4172 ORDER BY decode(csr_v_sort_order
4173 ,'EMPNUM',action_information5
4174 ,'EMPPIN',action_information6
4175 ,'EMPLAN',action_information7
4176 );
4177
4178 CURSOR csr_get_person (
4179 csr_v_record pay_action_information.action_information3%TYPE
4180 ,csr_v_pa_id pay_action_information.action_information3%TYPE
4181 ,csr_v_person_id pay_action_information.action_information30%TYPE
4182 )
4183 IS
4184 SELECT *
4185 FROM pay_action_information
4186 WHERE action_context_type = 'AAP'
4187 AND action_information_category = 'EMEA REPORT INFORMATION'
4188 AND action_information1 = 'PYSEINSA'
4189 AND action_information2 = csr_v_record
4190 AND action_information3 = csr_v_pa_id
4191 AND action_information30 = csr_v_person_id
4192 ORDER BY action_information30;
4193
4194 lr_get_person csr_get_person%ROWTYPE;
4195
4196 /* End of declaration*/
4197 /* Proc to Add the tag value and Name */
4198 PROCEDURE add_tag_value (p_tag_name IN VARCHAR2, p_tag_value IN VARCHAR2)
4199 IS
4200 BEGIN
4201 gins_data (l_counter).tagname := p_tag_name;
4202 gins_data (l_counter).tagvalue := p_tag_value;
4203 l_counter := l_counter + 1;
4204 END add_tag_value;
4205 /* End of Proc to Add the tag value and Name */
4206 /* Start of GET_HPD_XML */
4207 BEGIN
4208 IF p_payroll_action_id IS NULL
4209 THEN
4210 BEGIN
4211 SELECT payroll_action_id
4212 INTO l_payroll_action_id
4213 FROM pay_payroll_actions ppa
4214 ,fnd_conc_req_summary_v fcrs
4215 ,fnd_conc_req_summary_v fcrs1
4216 WHERE fcrs.request_id = fnd_global.conc_request_id
4217 AND fcrs.priority_request_id = fcrs1.priority_request_id
4218 AND ppa.request_id BETWEEN fcrs1.request_id AND fcrs.request_id
4219 AND ppa.request_id = fcrs1.request_id;
4220 EXCEPTION
4221 WHEN OTHERS
4222 THEN
4223 NULL;
4224 END;
4225 ELSE
4226 l_payroll_action_id := p_payroll_action_id;
4227 --logger ('Entered Reporting', ' XML Creation ');
4228 --logger ('p_payroll_action_id', p_payroll_action_id);
4229
4230 /* Structure of Xml should look like this
4231 <LE>
4232 <DETAILS>
4233 </DETAILS>
4234 <EMPLOYEES>
4235 <PERSON>
4236 </PERSON>
4237 </EMPLOYEES>
4238 </LE>
4239 */
4240 OPEN csr_report_details (l_payroll_action_id);
4241
4242 FETCH csr_report_details
4243 INTO lr_report_details;
4244
4245 CLOSE csr_report_details;
4246 --logger ('SORT by ', lr_report_details.action_information10);
4247
4248 --logger ('Before ', ' Csr for Legal ');
4249
4250 OPEN csr_media_provider (l_payroll_action_id);
4251
4252 FETCH csr_media_provider
4253 INTO lr_media_provider;
4254
4255 CLOSE csr_media_provider;
4256
4257 --logger ('PRODUCT', lr_media_provider.action_information3);
4258 --logger ('PERIOD', lr_media_provider.action_information4);
4259 --logger ('TEST_OR_PRODUCTION', lr_media_provider.action_information5);
4260 --logger ('ORG_NUMBER', lr_media_provider.action_information6);
4261 --logger ('MEDIA_PROVIDER_NAME', lr_media_provider.action_information7);
4262 --logger ('DIVISION', lr_media_provider.action_information8);
4263 --logger ('CONTACT_PERSON', lr_media_provider.action_information9);
4264 --logger ('ADDRESS', lr_media_provider.action_information10);
4265 --logger ('POSTAL_CODE', lr_media_provider.action_information11);
4266 --logger ('POSTAL_ADDRESS', lr_media_provider.action_information12);
4267 --logger ('PHONE_NUMBER', lr_media_provider.action_information13);
4268 --logger ('FAX_NUMBER', lr_media_provider.action_information14);
4269 --logger ('EMAIL', lr_media_provider.action_information15);
4270 add_tag_value ('INCOME_STATEMENT', 'INCOME_STATEMENT');
4271 -- Add header for file
4272 add_tag_value ('SENDER', lr_report_details.action_information11);
4273 add_tag_value ('RECEIVER', lr_report_details.action_information12);
4274 add_tag_value ('INFORMATION_TYPE', lr_report_details.action_information13);
4275 -- Add header for file
4276 add_tag_value ('INFO_KU', 'INFO_KU');
4277 add_tag_value ('PRODUCT', lr_media_provider.action_information3);
4278 add_tag_value ('PERIOD', lr_media_provider.action_information4);
4279 add_tag_value ('TEST_OR_PRODUCTION'
4280 ,lr_media_provider.action_information5
4281 );
4282 add_tag_value ('ORG_NUMBER', lr_media_provider.action_information6);
4283 add_tag_value ('MEDIA_PROVIDER_NAME'
4284 ,lr_media_provider.action_information7
4285 );
4286 add_tag_value ('DIVISION', lr_media_provider.action_information8);
4287 add_tag_value ('CONTACT_PERSON'
4288 ,lr_media_provider.action_information9
4289 );
4290 add_tag_value ('ADDRESS', lr_media_provider.action_information10);
4291 add_tag_value ('POSTAL_CODE', lr_media_provider.action_information11);
4292 add_tag_value ('POSTAL_ADDRESS'
4293 ,lr_media_provider.action_information12
4294 );
4295 add_tag_value ('PHONE_NUMBER'
4296 ,lr_media_provider.action_information13);
4297 add_tag_value ('FAX_NUMBER', lr_media_provider.action_information14);
4298 add_tag_value ('EMAIL', lr_media_provider.action_information15);
4299 add_tag_value('IDENTITY',lr_Media_provider.action_information16);
4300 add_tag_value('PROGRAM',lr_Media_provider.action_information17);
4301
4302 add_tag_value ('INFO_KU', 'INFO_KU_END');
4303
4304 FOR rec_all_le IN csr_all_legal_employer (l_payroll_action_id)
4305 LOOP
4306 --logger ('LE_NAME', rec_all_le.action_information4);
4307 --logger ('LE_ORG_NUM', rec_all_le.action_information5);
4308 add_tag_value ('LEGAL_EMPLOYER', 'LEGAL_EMPLOYER');
4309 add_tag_value ('LE_DETAILS', 'LE_DETAILS');
4310 add_tag_value ('LE_NAME', rec_all_le.action_information4);
4311 add_tag_value ('LE_ORG_NUM', rec_all_le.action_information5);
4312 add_tag_value ('LE_ADDRESS', 'LE_ADDRESS');
4313 add_tag_value ('LOCATION_CODE', rec_all_le.action_information6);
4314 add_tag_value ('ADDRESS_LINE_1', rec_all_le.action_information7);
4315 add_tag_value ('ADDRESS_LINE_2', rec_all_le.action_information8);
4316 add_tag_value ('ADDRESS_LINE_3', rec_all_le.action_information9);
4317 add_tag_value ('POSTAL_CODE', rec_all_le.action_information10);
4318 add_tag_value ('TOWN_OR_CITY', rec_all_le.action_information11);
4319 add_tag_value ('REGION_1', rec_all_le.action_information12);
4320 add_tag_value ('REGION_2', rec_all_le.action_information13);
4321 add_tag_value ('TERRITORY_SHORT_NAME'
4322 ,rec_all_le.action_information14
4323 );
4324 add_tag_value ('LE_ADDRESS', 'LE_ADDRESS_END');
4325 add_tag_value ('LE_DETAILS', 'LE_DETAILS_END');
4326 add_tag_value ('EMPLOYEES', 'EMPLOYEES');
4327 --logger ('LE_ORG_NUM', rec_all_le.action_information5);
4328 --logger ('LE ID', rec_all_le.action_information3);
4329 --logger ('Before Person Query', '^^^^^^^^^^^^^^^^^^^^^');
4330 -- *****************************************************************************
4331 -- FOR KU10
4332 add_tag_value ('KU10_PERSON', 'KU10_PERSON');
4333
4334 -- *****************************************************************************
4335 FOR rec_all_emp_under_le IN
4336 csr_all_employees_under_le (l_payroll_action_id
4337 ,rec_all_le.action_information3
4338 ,'KU10'
4339 ,lr_report_details.action_information10
4340 )
4341 LOOP
4342 add_tag_value ('PERSON', 'PERSON');
4343 add_tag_value ('TYPE', 'KU10');
4344
4345 lr_get_person := NULL;
4346 OPEN csr_get_person ('PERSON1'
4347 ,l_payroll_action_id
4348 ,rec_all_emp_under_le.action_information30
4349 );
4350 FETCH csr_get_person
4351 INTO lr_get_person;
4352
4353 CLOSE csr_get_person;
4354 add_tag_value ('FROM'
4355 ,lr_get_person.action_information5
4356 );
4357 add_tag_value ('TO', lr_get_person.action_information6);
4358 add_tag_value ('PIN', lr_get_person.action_information7);
4359 add_tag_value ('NAME'
4360 ,lr_get_person.action_information8);
4361 add_tag_value ('ADDRESS'
4362 ,lr_get_person.action_information26
4363 );
4364 add_tag_value ('POSTAL_CODE'
4365 ,lr_get_person.action_information27
4366 );
4367 add_tag_value ('POSTAL_TOWN'
4368 ,lr_get_person.action_information28
4369 );
4370 add_tag_value
4371 ('CORRECTION_DATE'
4372 ,TO_CHAR
4373 (fnd_date.canonical_to_date
4374 (lr_get_person.action_information9)
4375 ,'YYYY-MM-DD'
4376 )
4377 );
4378 add_tag_value ('WORK_SITE_NUMBER'
4379 ,lr_get_person.action_information10
4380 );
4381 add_tag_value ('AMOUNT_TAX_WITHHELD'
4382 ,lr_get_person.action_information11
4383 );
4384 add_tag_value ('GROSS_SALARY'
4385 ,lr_get_person.action_information12
4386 );
4387 add_tag_value ('TB_EXCLUSIVE_CAR_FUEL'
4388 ,lr_get_person.action_information13
4389 );
4390 add_tag_value ('FREE_HOUSING'
4391 ,lr_get_person.action_information14
4392 );
4393
4394 IF lr_get_person.action_information14='Y' THEN
4395
4396 add_tag_value ('F_H'
4397 ,'X');
4398
4399 END IF;
4400
4401 add_tag_value ('FREE_MEALS'
4402 ,lr_get_person.action_information15
4403 );
4404
4405 IF lr_get_person.action_information15='Y' THEN
4406
4407 add_tag_value ('F_M'
4408 ,'X');
4409
4410 END IF;
4411
4412 add_tag_value ('FREE_HOUSING_OTHER41'
4413 ,lr_get_person.action_information16
4414 );
4415
4416 IF lr_get_person.action_information16='Y' THEN
4417
4418 add_tag_value ('F_H41'
4419 ,'X');
4420
4421 END IF;
4422
4423 add_tag_value ('INTEREST'
4424 ,lr_get_person.action_information17
4425 );
4426
4427 IF lr_get_person.action_information17='Y' THEN
4428
4429 add_tag_value ('INT'
4430 ,'X');
4431
4432 END IF;
4433
4434 add_tag_value ('OTHER_BENEFITS'
4435 ,lr_get_person.action_information18
4436 );
4437
4438 IF lr_get_person.action_information18='Y' THEN
4439
4440 add_tag_value ('OTH_BEN'
4441 ,'X');
4442
4443 END IF;
4444
4445 add_tag_value ('BENEFIT_ADJUSTED'
4446 ,lr_get_person.action_information19
4447 );
4448
4449 IF lr_get_person.action_information19='Y' THEN
4450
4451 add_tag_value ('BEN_ADJ'
4452 ,'X');
4453
4454 END IF;
4455
4456
4457 add_tag_value ('TB_EXCLUSIVE_FUEL'
4458 ,lr_get_person.action_information20
4459 );
4460 add_tag_value ('RSV_CODE'
4461 ,lr_get_person.action_information21
4462 );
4463 add_tag_value ('NUMBER_OF_MONTHS_CAR'
4464 ,lr_get_person.action_information22
4465 );
4466 add_tag_value ('NUMBER_OF_KILOMETERS'
4467 ,lr_get_person.action_information23
4468 );
4469 add_tag_value ('EMPLOYEE_PAYMENT_CAR'
4470 ,lr_get_person.action_information24
4471 );
4472 add_tag_value ('FREE_FUEL_CAR'
4473 ,lr_get_person.action_information25
4474 );
4475 lr_get_person := NULL;
4476
4477 OPEN csr_get_person ('PERSON2'
4478 ,l_payroll_action_id
4479 ,rec_all_emp_under_le.action_information30
4480 );
4481
4482 FETCH csr_get_person
4483 INTO lr_get_person;
4484
4485 CLOSE csr_get_person;
4486
4487
4488 add_tag_value ('COMPENSATION_FOR_EXPENSES'
4489 ,lr_get_person.action_information4
4490 );
4491 add_tag_value ('MILEAGE_ALLOWANCE'
4492 ,lr_get_person.action_information5
4493 );
4494 IF lr_get_person.action_information5='Y' THEN
4495
4496 add_tag_value ('MIL_ALLOW'
4497 ,'X');
4498
4499 END IF;
4500
4501 add_tag_value ('PER_DIEM_SWEDEN'
4502 ,lr_get_person.action_information6
4503 );
4504 IF lr_get_person.action_information6='Y' THEN
4505
4506 add_tag_value ('PD_SW'
4507 ,'X');
4508
4509 END IF;
4510 add_tag_value ('PER_DIEM_OTHER'
4511 ,lr_get_person.action_information7
4512 );
4513 IF lr_get_person.action_information7='Y' THEN
4514
4515 add_tag_value ('PD_OTH'
4516 ,'X');
4517
4518 END IF;
4519 add_tag_value ('BUSI_TRAVEL_EXPENSES_FLAG'
4520 ,lr_get_person.action_information8
4521 );
4522 IF lr_get_person.action_information8='Y' THEN
4523
4524 add_tag_value ('BTE'
4525 ,'X');
4526
4527 END IF;
4528 add_tag_value ('ACC_BUSINESS_TRAVELS_FLAG'
4529 ,lr_get_person.action_information9
4530 );
4531 IF lr_get_person.action_information9='Y' THEN
4532
4533 add_tag_value ('ABTF'
4534 ,'X');
4535
4536 END IF;
4537 add_tag_value ('WITHIN_SWEDEN'
4538 ,lr_get_person.action_information10
4539 );
4540 IF lr_get_person.action_information10='Y' THEN
4541
4542 add_tag_value ('WS'
4543 ,'X');
4544
4545 END IF;
4546 add_tag_value ('OTHER_COUNTRIES'
4547 ,lr_get_person.action_information11
4548 );
4549 IF lr_get_person.action_information11='Y' THEN
4550
4551 add_tag_value ('OTH_C'
4552 ,'X');
4553
4554 END IF;
4555 add_tag_value ('OCCUPATIONAL_PENSION'
4556 ,lr_get_person.action_information12
4557 );
4558 add_tag_value ('OTHER_TAX_REM'
4559 ,lr_get_person.action_information13
4560 );
4561 add_tag_value ('TAX_REM_WITHOUT_SJD' --EOY 2008
4562 ,lr_get_person.action_information23
4563 );
4564 --------------------------------------------------------------------------------------------------
4565 --Taxable remunerations forwhich social security contributionsare not paid and which are notentitled to special job deduction
4566 -------------------------------------------------------------------------------------------------------
4567 add_tag_value ('TAX_REM_PAID'
4568 ,lr_get_person.action_information14
4569 );
4570 add_tag_value ('CERTAIN_DEDUCTIONS'
4571 ,lr_get_person.action_information15
4572 );
4573 add_tag_value ('OTHER_BENEFITS_UP65'
4574 ,lr_get_person.action_information16
4575 );
4576 add_tag_value ('COMPE_FOR_EXPENSES_UP66'
4577 ,lr_get_person.action_information17
4578 );
4579 add_tag_value ('TAX_REM_PAID_UP67'
4580 ,lr_get_person.action_information18
4581 );
4582 add_tag_value ('OTHER_TAX_REM_UP68'
4583 ,lr_get_person.action_information19
4584 );
4585 add_tag_value ('TAX_REM_WITHOUT_SJD_UP69' ---EOY 2008
4586 ,lr_get_person.action_information24
4587 );
4588 --------------------------------------------------------------------------------------------------------------------------
4589 --Taxable remunerations for which social security contributionsare not paid and which are not entitled to special job deduction
4590 ----------------------------------------------------------------------------------------------------------
4591 add_tag_value ('CERTAIN_DEDUCTIONS_UP70'
4592 ,lr_get_person.action_information20
4593 );
4594 add_tag_value ('NOT_TAX_REM'
4595 ,lr_get_person.action_information21
4596 );
4597 add_tag_value ('RENT', lr_get_person.action_information22);
4598
4599 add_tag_value ('BENEFIT_AS_PENSION' --EOY 2008
4600 ,lr_get_person.action_information25
4601 );
4602
4603 IF lr_get_person.action_information25='Y' THEN
4604
4605 add_tag_value ('BEN_PEN'
4606 ,'X');
4607
4608 END IF;
4609
4610 --------------------------------------------------------------------------------------------------------------------------
4611 -----------------------------------------New Benefit as pension-----------------------------------------------------------
4612 --------------------------------------------------------------------------------------------------------------------------
4613
4614
4615 add_tag_value ('PERSON', 'PERSON_END');
4616 END LOOP;
4617
4618 -- *****************************************************************************
4619 add_tag_value ('KU10_PERSON', 'KU10_PERSON_END');
4620 -- FOR KU13
4621 add_tag_value ('KU13_PERSON', 'KU13_PERSON');
4622
4623 -- *****************************************************************************
4624 FOR rec_all_emp_under_le IN
4625 csr_all_employees_under_le (l_payroll_action_id
4626 ,rec_all_le.action_information3
4627 ,'KU13'
4628 ,lr_report_details.action_information10
4629 )
4630 LOOP
4631 add_tag_value ('PERSON', 'PERSON');
4632 add_tag_value ('TYPE', 'KU13');
4633 lr_get_person := NULL;
4634 OPEN csr_get_person ('PERSON1'
4635 ,l_payroll_action_id
4636 ,rec_all_emp_under_le.action_information30
4637 );
4638 FETCH csr_get_person
4639 INTO lr_get_person;
4640
4641 CLOSE csr_get_person;
4642 add_tag_value ('FROM'
4643 ,lr_get_person.action_information5
4644 );
4645 add_tag_value ('TO', lr_get_person.action_information6);
4646 add_tag_value ('PIN', lr_get_person.action_information7);
4647 add_tag_value ('NAME'
4648 ,lr_get_person.action_information8);
4649 add_tag_value ('ADDRESS'
4650 ,lr_get_person.action_information26
4651 );
4652 add_tag_value ('POSTAL_CODE'
4653 ,lr_get_person.action_information27
4654 );
4655 add_tag_value ('POSTAL_TOWN'
4656 ,lr_get_person.action_information28
4657 );
4658 add_tag_value
4659 ('CORRECTION_DATE'
4660 ,TO_CHAR
4661 (fnd_date.canonical_to_date
4662 (lr_get_person.action_information9)
4663 ,'YYYY-MM-DD'
4664 )
4665 );
4666 add_tag_value ('WORK_SITE_NUMBER'
4667 ,lr_get_person.action_information10
4668 );
4669 add_tag_value ('AMOUNT_TAX_WITHHELD'
4670 ,lr_get_person.action_information11
4671 );
4672 add_tag_value ('GROSS_SALARY'
4673 ,lr_get_person.action_information12
4674 );
4675 add_tag_value ('TB_EXCLUSIVE_CAR_FUEL'
4676 ,lr_get_person.action_information13
4677 );
4678 add_tag_value ('FREE_HOUSING'
4679 ,lr_get_person.action_information14
4680 );
4681 IF lr_get_person.action_information14='Y' THEN
4682
4683 add_tag_value ('F_H'
4684 ,'X');
4685
4686 END IF;
4687
4688 add_tag_value ('FREE_MEALS'
4689 ,lr_get_person.action_information15
4690 );
4691
4692 IF lr_get_person.action_information15='Y' THEN
4693
4694 add_tag_value ('F_M'
4695 ,'X');
4696
4697 END IF;
4698
4699 add_tag_value ('FREE_HOUSING_OTHER41'
4700 ,lr_get_person.action_information16
4701 );
4702
4703 IF lr_get_person.action_information16='Y' THEN
4704
4705 add_tag_value ('F_H41'
4706 ,'X');
4707
4708 END IF;
4709
4710 add_tag_value ('INTEREST'
4711 ,lr_get_person.action_information17
4712 );
4713
4714 IF lr_get_person.action_information17='Y' THEN
4715
4716 add_tag_value ('INT'
4717 ,'X');
4718
4719 END IF;
4720
4721 add_tag_value ('OTHER_BENEFITS'
4722 ,lr_get_person.action_information18
4723 );
4724
4725 IF lr_get_person.action_information18='Y' THEN
4726
4727 add_tag_value ('OTH_BEN'
4728 ,'X');
4729
4730 END IF;
4731
4732 add_tag_value ('BENEFIT_ADJUSTED'
4733 ,lr_get_person.action_information19
4734 );
4735
4736
4737 IF lr_get_person.action_information19='Y' THEN
4738
4739 add_tag_value ('BEN_ADJ'
4740 ,'X');
4741
4742 END IF;
4743
4744 add_tag_value ('TB_EXCLUSIVE_FUEL'
4745 ,lr_get_person.action_information20
4746 );
4747 add_tag_value ('RSV_CODE'
4748 ,lr_get_person.action_information21
4749 );
4750 add_tag_value ('NUMBER_OF_MONTHS_CAR'
4751 ,lr_get_person.action_information22
4752 );
4753 add_tag_value ('NUMBER_OF_KILOMETERS'
4754 ,lr_get_person.action_information23
4755 );
4756 add_tag_value ('EMPLOYEE_PAYMENT_CAR'
4757 ,lr_get_person.action_information24
4758 );
4759 add_tag_value ('FREE_FUEL_CAR'
4760 ,lr_get_person.action_information25
4761 );
4762 lr_get_person := NULL;
4763
4764 OPEN csr_get_person ('PERSON3'
4765 ,l_payroll_action_id
4766 ,rec_all_emp_under_le.action_information30
4767 );
4768
4769 FETCH csr_get_person
4770 INTO lr_get_person;
4771
4772 CLOSE csr_get_person;
4773
4774 add_tag_value ('FTIN', lr_get_person.action_information4);
4775 add_tag_value ('TAX_COUNTRY_CODE'
4776 ,lr_get_person.action_information5
4777 );
4778 add_tag_value ('OCCUPATIONAL_PENSION'
4779 ,lr_get_person.action_information6
4780 );
4781 add_tag_value ('OTHER_TAX_REM'
4782 ,lr_get_person.action_information7
4783 );
4784 add_tag_value ('COMPEN_FOR_BENEFIT'
4785 ,lr_get_person.action_information8
4786 );
4787 add_tag_value ('IN_PLAIN_WRITING_CODE'
4788 ,lr_get_person.action_information10
4789 );
4790 add_tag_value ('IN_PLAIN_WRITING_MEANING'
4791 ,lr_get_person.action_information19
4792 );
4793 add_tag_value ('PERSON', 'PERSON_END');
4794 END LOOP;
4795
4796 -- *****************************************************************************
4797 add_tag_value ('KU13_PERSON', 'KU13_PERSON_END');
4798 -- FOR KU14
4799 add_tag_value ('KU14_PERSON', 'KU14_PERSON');
4800
4801 -- *****************************************************************************
4802 FOR rec_all_emp_under_le IN
4803 csr_all_employees_under_le (l_payroll_action_id
4804 ,rec_all_le.action_information3
4805 ,'KU14'
4806 ,lr_report_details.action_information10
4807 )
4808 LOOP
4809 add_tag_value ('PERSON', 'PERSON');
4810 add_tag_value ('TYPE', 'KU14');
4811
4812 lr_get_person := NULL;
4813 OPEN csr_get_person ('PERSON1'
4814 ,l_payroll_action_id
4815 ,rec_all_emp_under_le.action_information30
4816 );
4817 FETCH csr_get_person
4818 INTO lr_get_person;
4819
4820 CLOSE csr_get_person;
4821 add_tag_value ('FROM'
4822 ,lr_get_person.action_information5
4823 );
4824 add_tag_value ('TO', lr_get_person.action_information6);
4825 add_tag_value ('PIN', lr_get_person.action_information7);
4826 add_tag_value ('NAME'
4827 ,lr_get_person.action_information8);
4828 add_tag_value ('ADDRESS'
4829 ,lr_get_person.action_information26
4830 );
4831 add_tag_value ('POSTAL_CODE'
4832 ,lr_get_person.action_information27
4833 );
4834 add_tag_value ('POSTAL_TOWN'
4835 ,lr_get_person.action_information28
4836 );
4837 add_tag_value
4838 ('CORRECTION_DATE'
4839 ,TO_CHAR
4840 (fnd_date.canonical_to_date
4841 (lr_get_person.action_information9)
4842 ,'YYYY-MM-DD'
4843 )
4844 );
4845 add_tag_value ('WORK_SITE_NUMBER'
4846 ,lr_get_person.action_information10
4847 );
4848 add_tag_value ('AMOUNT_TAX_WITHHELD'
4849 ,lr_get_person.action_information11
4850 );
4851 add_tag_value ('GROSS_SALARY'
4852 ,lr_get_person.action_information12
4853 );
4854 add_tag_value ('TB_EXCLUSIVE_CAR_FUEL'
4855 ,lr_get_person.action_information13
4856 );
4857 add_tag_value ('FREE_HOUSING'
4858 ,lr_get_person.action_information14
4859 );
4860
4861 IF lr_get_person.action_information14='Y' THEN
4862
4863 add_tag_value ('F_H'
4864 ,'X');
4865
4866 END IF;
4867
4868 add_tag_value ('FREE_MEALS'
4869 ,lr_get_person.action_information15
4870 );
4871
4872 IF lr_get_person.action_information15='Y' THEN
4873
4874 add_tag_value ('F_M'
4875 ,'X');
4876
4877 END IF;
4878
4879 add_tag_value ('FREE_HOUSING_OTHER41'
4880 ,lr_get_person.action_information16
4881 );
4882
4883 IF lr_get_person.action_information16='Y' THEN
4884
4885 add_tag_value ('F_H41'
4886 ,'X');
4887
4888 END IF;
4889
4890 add_tag_value ('INTEREST'
4891 ,lr_get_person.action_information17
4892 );
4893
4894 IF lr_get_person.action_information17='Y' THEN
4895
4896 add_tag_value ('INT'
4897 ,'X');
4898
4899 END IF;
4900
4901
4902 add_tag_value ('OTHER_BENEFITS'
4903 ,lr_get_person.action_information18
4904 );
4905 IF lr_get_person.action_information18='Y' THEN
4906
4907 add_tag_value ('OTH_BEN'
4908 ,'X');
4909
4910 END IF;
4911
4912 add_tag_value ('BENEFIT_ADJUSTED'
4913 ,lr_get_person.action_information19
4914 );
4915
4916 IF lr_get_person.action_information19='Y' THEN
4917
4918 add_tag_value ('BEN_ADJ'
4919 ,'X');
4920
4921 END IF;
4922
4923 add_tag_value ('TB_EXCLUSIVE_FUEL'
4924 ,lr_get_person.action_information20
4925 );
4926 add_tag_value ('RSV_CODE'
4927 ,lr_get_person.action_information21
4928 );
4929 add_tag_value ('NUMBER_OF_MONTHS_CAR'
4930 ,lr_get_person.action_information22
4931 );
4932 add_tag_value ('NUMBER_OF_KILOMETERS'
4933 ,lr_get_person.action_information23
4934 );
4935 add_tag_value ('EMPLOYEE_PAYMENT_CAR'
4936 ,lr_get_person.action_information24
4937 );
4938 add_tag_value ('FREE_FUEL_CAR'
4939 ,lr_get_person.action_information25
4940 );
4941 lr_get_person := NULL;
4942
4943 OPEN csr_get_person ('PERSON2'
4944 ,l_payroll_action_id
4945 ,rec_all_emp_under_le.action_information30
4946 );
4947
4948 FETCH csr_get_person
4949 INTO lr_get_person;
4950
4951 CLOSE csr_get_person;
4952
4953 add_tag_value ('MILEAGE_ALLOWANCE'
4954 ,lr_get_person.action_information5
4955 );
4956 IF lr_get_person.action_information5='Y' THEN
4957
4958 add_tag_value ('MIL_ALLOW'
4959 ,'X');
4960
4961 END IF;
4962 add_tag_value ('PER_DIEM_SWEDEN'
4963 ,lr_get_person.action_information6
4964 );
4965 IF lr_get_person.action_information6='Y' THEN
4966
4967 add_tag_value ('PD_SW'
4968 ,'X');
4969
4970 END IF;
4971 add_tag_value ('PER_DIEM_OTHER'
4972 ,lr_get_person.action_information7
4973 );
4974 IF lr_get_person.action_information7='Y' THEN
4975
4976 add_tag_value ('PD_OTH'
4977 ,'X');
4978
4979 END IF;
4980 add_tag_value ('BUSI_TRAVEL_EXPENSES_FLAG'
4981 ,lr_get_person.action_information8
4982 );
4983 IF lr_get_person.action_information8='Y' THEN
4984
4985 add_tag_value ('BTE'
4986 ,'X');
4987
4988 END IF;
4989 add_tag_value ('ACC_BUSINESS_TRAVELS_FLAG'
4990 ,lr_get_person.action_information9
4991 );
4992 IF lr_get_person.action_information9='Y' THEN
4993
4994 add_tag_value ('ABTF'
4995 ,'X');
4996
4997 END IF;
4998 add_tag_value ('WITHIN_SWEDEN'
4999 ,lr_get_person.action_information10
5000 );
5001 IF lr_get_person.action_information10='Y' THEN
5002
5003 add_tag_value ('WS'
5004 ,'X');
5005
5006 END IF;
5007 add_tag_value ('OTHER_COUNTRIES'
5008 ,lr_get_person.action_information11
5009 );
5010 IF lr_get_person.action_information11='Y' THEN
5011
5012 add_tag_value ('OTH_C'
5013 ,'X');
5014
5015 END IF;
5016 lr_get_person := NULL;
5017
5018 OPEN csr_get_person ('PERSON3'
5019 ,l_payroll_action_id
5020 ,rec_all_emp_under_le.action_information30
5021 );
5022
5023 FETCH csr_get_person
5024 INTO lr_get_person;
5025
5026 CLOSE csr_get_person;
5027
5028 add_tag_value ('FTIN', lr_get_person.action_information4);
5029 add_tag_value ('TAX_COUNTRY_CODE'
5030 ,lr_get_person.action_information5
5031 );
5032 add_tag_value ('OCCUPATIONAL_PENSION'
5033 ,lr_get_person.action_information11
5034 );
5035 add_tag_value ('OTHER_TAX_REM'
5036 ,lr_get_person.action_information12
5037 );
5038 add_tag_value ('TAX_REM_PAID'
5039 ,lr_get_person.action_information13
5040 );
5041 add_tag_value ('NOT_TAX_REM'
5042 ,lr_get_person.action_information14
5043 );
5044 add_tag_value ('WORK_COUNTRY_CODE'
5045 ,lr_get_person.action_information9
5046 );
5047 add_tag_value ('WORK_COUNTRY_MEANING'
5048 ,lr_get_person.action_information18
5049 );
5050 add_tag_value ('IN_PLAIN_WRITING_CODE'
5051 ,lr_get_person.action_information10
5052 );
5053 add_tag_value ('IN_PLAIN_WRITING_MEANING'
5054 ,lr_get_person.action_information19
5055 );
5056 add_tag_value ('WORK_PERIOD'
5057 ,lr_get_person.action_information15
5058 );
5059 IF lr_get_person.action_information15='SIX_MONTHS_LESS' THEN
5060
5061 add_tag_value ('WP_6'
5062 ,'X');
5063 ELSIF lr_get_person.action_information15='SIX_TO_ONE_YEAR' THEN
5064
5065 add_tag_value ('WP_L_12'
5066 ,'X');
5067
5068 ELSIF lr_get_person.action_information15='ONE_YEAR_OR_MORE' THEN
5069
5070 add_tag_value ('WP_G_12'
5071 ,'X');
5072
5073 END IF;
5074
5075 add_tag_value ('EMP_REGULATION_CATEGORY'
5076 ,lr_get_person.action_information16
5077 );
5078 IF lr_get_person.action_information16='92A' THEN
5079
5080 add_tag_value ('CC_92A'
5081 ,'X');
5082 ELSIF lr_get_person.action_information16='92B' THEN
5083
5084 add_tag_value ('CC_92B'
5085 ,'X');
5086
5087 ELSIF lr_get_person.action_information16='92C' THEN
5088
5089 add_tag_value ('CC_92C'
5090 ,'X');
5091
5092 ELSIF lr_get_person.action_information16='92D' THEN
5093
5094 add_tag_value ('CC_92D'
5095 ,'X');
5096 ELSIF lr_get_person.action_information16='92E' THEN
5097
5098 add_tag_value ('CC_92E'
5099 ,'X');
5100
5101 ELSIF lr_get_person.action_information16='92F' THEN
5102
5103 add_tag_value ('CC_92F'
5104 ,'X');
5105
5106 END IF;
5107
5108 add_tag_value ('EMP_REGULATION_CATEGORY_CODE'
5109 ,lr_get_person.action_information22
5110 );
5111 add_tag_value ('ARTICLE_DETAILS'
5112 ,lr_get_person.action_information17
5113 );
5114 add_tag_value ('COMPENSATION_FOR_EXPENSES', lr_get_PERSON.action_information21);
5115 add_tag_value ('PERSON', 'PERSON_END');
5116 END LOOP;
5117
5118 -- *****************************************************************************
5119 add_tag_value ('KU14_PERSON', 'KU14_PERSON_END');
5120 -- *****************************************************************************
5121 fnd_file.put_line (fnd_file.LOG, '^^^^^^^^^^^^^^^^^^^^^');
5122 add_tag_value ('EMPLOYEES', 'EMPLOYEES_END');
5123 add_tag_value ('LEGAL_EMPLOYER', 'LEGAL_EMPLOYER_END');
5124 END LOOP; /* For all LEGAL_EMPLYER */
5125
5126 add_tag_value ('INCOME_STATEMENT', 'INCOME_STATEMENT_END');
5127 END IF; /* for p_payroll_action_id IS NULL */
5128
5129 writetoclob (p_xml);
5130
5131 -- INSERT INTO clob_table VALUES (p_xml );
5132 END get_xml_for_report;
5133
5134 -- *****************************************************************************
5135 /* Proc to Add the tag value and Name */
5136 PROCEDURE logger (p_display IN VARCHAR2, p_value IN VARCHAR2)
5137 IS
5138 BEGIN
5139 fnd_file.put_line (fnd_file.LOG
5140 , p_display || ' ==> ' || p_value
5141 );
5142 END logger;
5143 /* End of Proc to Add the tag value and Name */
5144 -- *****************************************************************************
5145 -- *****************************************************************************
5146 /* Proc to Add the tag value and Name */
5147 FUNCTION get_country (p_code IN VARCHAR2 )
5148 return Varchar2
5149 IS
5150 CURSOR csr_get_country_details
5151 IS
5152 SELECT ft.territory_short_name
5153 FROM fnd_territories_vl ft
5154 WHERE ft.territory_code = p_code;
5155
5156 lr_get_country_details csr_get_country_details%ROWTYPE;
5157
5158 l_country_name varchar2(240);
5159
5160 BEGIN
5161 l_country_name := NULL;
5162 lr_get_country_details := NULL;
5163
5164 OPEN csr_get_country_details ;
5165 FETCH csr_get_country_details INTO lr_get_country_details;
5166 CLOSE csr_get_country_details;
5167 l_country_name := lr_get_country_details.territory_short_name;
5168 return l_country_name;
5169 END get_country;
5170 /* End of Proc to Add the tag value and Name */
5171 -- *****************************************************************************
5172 END pay_se_income_statement;