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