[Home] [Help]
PACKAGE BODY: APPS.PAY_FI_ARCHIVE_DPSA
Source
1 PACKAGE BODY PAY_FI_ARCHIVE_DPSA AS
2 /* $Header: pyfidpsa.pkb 120.5.12000000.1 2007/04/26 13:01:12 dbehera noship $ */
3
4 g_debug boolean := hr_utility.debug_enabled;
5 g_package VARCHAR2(33) := ' PAY_FI_ARCHIVE_DPSA.';
6 g_payroll_action_id NUMBER ;
7 g_le_assignment_action_id NUMBER ;
8 g_lu_assignment_action_id NUMBER ;
9 g_emp_type VARCHAR2(2);
10 g_business_group_id NUMBER;
11 g_legal_employer_id NUMBER;
12 g_local_unit_id NUMBER;
13 g_year VARCHAR2(4);
14 g_transact_type VARCHAR2(1);
15 g_deduction_ss NUMBER;
16 g_effective_date DATE;
17 g_archive VARCHAR2(1);
18
19 /* GET PARAMETER */
20 FUNCTION GET_PARAMETER(
21 p_parameter_string IN VARCHAR2
22 ,p_token IN VARCHAR2
23 ,p_segment_number IN NUMBER default NULL ) RETURN VARCHAR2
24 IS
25 l_parameter pay_payroll_actions.legislative_parameters%TYPE:=NULL;
26 l_start_pos NUMBER;
27 l_delimiter VARCHAR2(1):=' ';
28 l_proc VARCHAR2(40):= g_package||' get parameter ';
29 BEGIN
30 --
31 IF g_debug THEN
32 hr_utility.set_location(' Entering Function GET_PARAMETER',10);
33 END IF;
34 l_start_pos := instr(' '||p_parameter_string,l_delimiter||p_token||'=');
35 --
36 IF l_start_pos = 0 THEN
37 l_delimiter := '|';
38 l_start_pos := instr(' '||p_parameter_string,l_delimiter||p_token||'=');
39 END IF;
40
41 IF l_start_pos <> 0 THEN
42 l_start_pos := l_start_pos + length(p_token||'=');
43 l_parameter := substr(p_parameter_string,
44 l_start_pos,
45 instr(p_parameter_string||' ',
46 l_delimiter,l_start_pos)
47 - l_start_pos);
48 IF p_segment_number IS NOT NULL THEN
49 l_parameter := ':'||l_parameter||':';
50 l_parameter := substr(l_parameter,
51 instr(l_parameter,':',1,p_segment_number)+1,
52 instr(l_parameter,':',1,p_segment_number+1) -1
53 - instr(l_parameter,':',1,p_segment_number));
54 END IF;
55 END IF;
56 --
57 IF g_debug THEN
58 hr_utility.set_location(' Leaving Function GET_PARAMETER',20);
59 END IF;
60
61 RETURN l_parameter;
62
63 END;
64
65
66 /* GET ALL PARAMETERS */
67 PROCEDURE GET_ALL_PARAMETERS(
68 p_payroll_action_id IN NUMBER
69 ,p_business_group_id OUT NOCOPY NUMBER
70 ,p_legal_employer_id OUT NOCOPY NUMBER
71 ,p_local_unit_id OUT NOCOPY NUMBER
72 ,p_year OUT NOCOPY VARCHAR2
73 ,p_transact_type OUT NOCOPY VARCHAR2
74 ,p_deduction_ss OUT NOCOPY NUMBER
75 ,p_effective_date OUT NOCOPY DATE
76 ,p_archive OUT NOCOPY VARCHAR2
77 ) IS
78
79 CURSOR csr_parameter_info(p_payroll_action_id NUMBER) IS
80 SELECT PAY_FI_ARCHIVE_DPSA.GET_PARAMETER(legislative_parameters,'LEGAL_EMPLOYER_ID')
81 ,PAY_FI_ARCHIVE_DPSA.GET_PARAMETER(legislative_parameters,'LOCAL_UNIT_ID')
82 ,PAY_FI_ARCHIVE_DPSA.GET_PARAMETER(legislative_parameters,'YEAR_RPT')
83 ,PAY_FI_ARCHIVE_DPSA.GET_PARAMETER(legislative_parameters,'TRANSACTION_TYPE')
84 ,PAY_FI_ARCHIVE_DPSA.GET_PARAMETER(legislative_parameters,'DEDUCTIONS_SS') DEDUCTIONS_SS
85 ,PAY_FI_ARCHIVE_DPSA.GET_PARAMETER(legislative_parameters,'ARCHIVE')
86 ,effective_date
87 ,business_group_id
88 FROM pay_payroll_actions
89 WHERE payroll_action_id = p_payroll_action_id;
90
91 l_proc VARCHAR2(240):= g_package||' GET_ALL_PARAMETERS ';
92 --
93 BEGIN
94
95 OPEN csr_parameter_info (p_payroll_action_id);
96
97 FETCH csr_parameter_info
98 INTO p_legal_employer_id
99 ,p_local_unit_id
100 ,p_year
101 ,p_transact_type
102 ,p_deduction_ss
103 ,p_archive
104 ,p_effective_date
105 ,p_business_group_id;
106 CLOSE csr_parameter_info;
107 --
108 IF g_debug THEN
109 hr_utility.set_location(' Leaving Procedure GET_ALL_PARAMETERS',30);
110 END IF;
111
112 END GET_ALL_PARAMETERS;
113
114 /* RANGE CODE */
115 PROCEDURE RANGE_CODE
116 (p_payroll_action_id IN NUMBER
117 ,p_sql OUT NOCOPY VARCHAR2)
118 IS
119 l_action_info_id NUMBER;
120 l_ovn NUMBER;
121 l_count NUMBER := 0;
122 l_business_group_id NUMBER;
123 l_emp_id hr_organization_units.organization_id%TYPE ;
124 l_le_name hr_organization_units.name%TYPE ;
125 l_lu_name hr_organization_units.name%TYPE ;
126 l_business_id hr_organization_information.org_information1%TYPE ;
127 l_y_number hr_organization_information.org_information1%TYPE ;
128 l_contact_person hr_organization_information.org_information1%TYPE ;
129 l_phone hr_organization_information.org_information1%TYPE ;
130 l_org_type hr_organization_information.org_information1%TYPE ;
131
132 /*Cursors */
133 /*Local Unit Information*/
134 Cursor csr_Local_Unit_Details ( csr_v_local_unit_id hr_organization_information.ORGANIZATION_ID%TYPE)
135 IS
136 SELECT o1.name , hoi2.ORG_INFORMATION1
137 FROM hr_organization_units o1
138 , hr_organization_information hoi1
139 , hr_organization_information hoi2
140 WHERE o1.business_group_id =l_business_group_id
141 AND hoi1.organization_id = o1.organization_id
142 AND hoi1.organization_id = csr_v_local_unit_id
143 AND hoi1.org_information1 = 'FI_LOCAL_UNIT'
144 AND hoi1.org_information_context = 'CLASS'
145 AND o1.organization_id =hoi2.organization_id
146 AND hoi2.ORG_INFORMATION_CONTEXT='FI_LOCAL_UNIT_DETAILS';
147
148 rg_Local_Unit_Details csr_Local_Unit_Details%rowtype;
149
150 /*Legal Employer Information*/
151 Cursor csr_Legal_Emp_Details ( csr_v_legal_emp_id hr_organization_information.ORGANIZATION_ID%TYPE)
152 IS
153 SELECT o1.name ,hoi2.ORG_INFORMATION1 , hoi2.ORG_INFORMATION13
154 FROM hr_organization_units o1
155 , hr_organization_information hoi1
156 , hr_organization_information hoi2
157 WHERE o1.business_group_id =l_business_group_id
158 AND hoi1.organization_id = o1.organization_id
159 AND hoi1.organization_id = csr_v_legal_emp_id
160 AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
161 AND hoi1.org_information_context = 'CLASS'
162 AND o1.organization_id =hoi2.organization_id
163 AND hoi2.ORG_INFORMATION_CONTEXT='FI_LEGAL_EMPLOYER_DETAILS' ;
164
165
166 rg_Legal_Emp_Details csr_Legal_Emp_Details%rowtype;
167
168 /*Legal Employer Contact Information*/
169 Cursor csr_Legal_Emp_Contact ( csr_v_legal_emp_id hr_organization_information.ORGANIZATION_ID%TYPE)
170 IS
171 SELECT hoi4.ORG_INFORMATION2 contact_person , hoi3.ORG_INFORMATION2 phone
172 FROM hr_organization_units o1
173 , hr_organization_information hoi1
174 , hr_organization_information hoi3
175 , hr_organization_information hoi4
176 WHERE o1.business_group_id =l_business_group_id
177 AND hoi1.organization_id = o1.organization_id
178 AND hoi1.organization_id = csr_v_legal_emp_id
179 AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
180 AND hoi1.org_information_context = 'CLASS'
181 AND hoi3.organization_id (+)= o1.organization_id
182 AND hoi3.ORG_INFORMATION_CONTEXT (+)='ORG_CONTACT_DETAILS'
183 AND hoi3.org_information1 (+)= 'PHONE'
184 AND hoi4.organization_id (+)= o1.organization_id
185 AND hoi4.ORG_INFORMATION_CONTEXT (+)='ORG_CONTACT_DETAILS'
186 AND hoi4.org_information1 (+)= 'PERSON' ;
187
188 rg_Legal_Emp_Contact csr_Legal_Emp_Contact%rowtype;
189
190 /*Local Unit Contact Information*/
191 Cursor csr_Local_Unit_contact ( csr_v_local_unit_id hr_organization_information.ORGANIZATION_ID%TYPE)
192 IS
193 SELECT hoi4.ORG_INFORMATION2 contact_person , hoi3.ORG_INFORMATION2 phone
194 FROM hr_organization_units o1
195 , hr_organization_information hoi1
196 , hr_organization_information hoi3
197 , hr_organization_information hoi4
198 WHERE o1.business_group_id =l_business_group_id
199 AND hoi1.organization_id = o1.organization_id
200 AND hoi1.organization_id = csr_v_local_unit_id
201 AND hoi1.org_information1 = 'FI_LOCAL_UNIT'
202 AND hoi1.org_information_context = 'CLASS'
203 AND hoi3.organization_id (+)= o1.organization_id
204 AND hoi3.ORG_INFORMATION_CONTEXT (+)='ORG_CONTACT_DETAILS'
205 AND hoi3.org_information1 (+)= 'PHONE'
206 AND hoi4.organization_id (+)= o1.organization_id
207 AND hoi4.ORG_INFORMATION_CONTEXT (+)='ORG_CONTACT_DETAILS'
208 AND hoi4.org_information1 (+)= 'PERSON' ;
209
210 rg_Local_Unit_contact csr_Local_Unit_contact%rowtype;
211
212 /* End of Cursors */
213 BEGIN
214
215 fnd_file.put_line(fnd_file.log,'Range Code 1');
216
217 IF g_debug THEN
218 hr_utility.set_location(' Entering Procedure RANGE_CODE',40);
219 END IF;
220
221 p_sql := 'SELECT DISTINCT person_id
222 FROM per_people_f ppf
223 ,pay_payroll_actions ppa
224 WHERE ppa.payroll_action_id = :payroll_action_id
225 AND ppa.business_group_id = ppf.business_group_id
226 ORDER BY ppf.person_id';
227
228 fnd_file.put_line(fnd_file.log,'Range Code 2');
229
230 g_archive := NULL;
231 g_emp_type := NULL ;
232 g_legal_employer_id := NULL ;
233 g_local_unit_id := NULL ;
234 g_effective_date := NULL ;
235 g_payroll_action_id := p_payroll_action_id ;
236 g_le_assignment_action_id := NULL ;
237 g_lu_assignment_action_id := NULL ;
238 g_transact_type := NULL ;
239 g_deduction_ss := NULL ;
240
241 fnd_file.put_line(fnd_file.log,'Range Code 3');
242
243 PAY_FI_ARCHIVE_DPSA.GET_ALL_PARAMETERS(
244 p_payroll_action_id
245 ,l_business_group_id
246 ,g_legal_employer_id
247 ,g_local_unit_id
248 ,g_year
249 ,g_transact_type
250 ,g_deduction_ss
251 ,g_effective_date
252 ,g_archive ) ;
253
254 fnd_file.put_line(fnd_file.log,'Range Code 4');
255
256 IF g_archive = 'Y' THEN
257
258 fnd_file.put_line(fnd_file.log,'Range Code 5');
259
260 SELECT count(*)
261 INTO l_count
262 FROM pay_action_information
263 WHERE action_information_category = 'EMEA REPORT DETAILS'
264 AND action_information1 = 'PYFIDPSA'
265 AND action_context_id = p_payroll_action_id;
266
267 fnd_file.put_line(fnd_file.log,'Range Code 6');
268
269 IF l_count < 1 then
270
271 hr_utility.set_location('Entered Procedure GETDATA',10);
272
273 fnd_file.put_line(fnd_file.log,'Range Code 7');
274
275 OPEN csr_Legal_Emp_Details(g_legal_employer_id);
276 FETCH csr_Legal_Emp_Details INTO rg_Legal_Emp_Details;
277 CLOSE csr_Legal_Emp_Details;
278
279 l_le_name := rg_Legal_Emp_Details.name ;
280 l_y_number := rg_Legal_Emp_Details.ORG_INFORMATION1 ;
281 l_org_type := rg_Legal_Emp_Details.ORG_INFORMATION13 ;
282
283 fnd_file.put_line(fnd_file.log,'Range Code 8');
284 IF g_local_unit_id IS NOT NULL THEN
285
286 fnd_file.put_line(fnd_file.log,'Range Code 9');
287
288 g_emp_type:='LU' ;
289 l_emp_id:=g_local_unit_id;
290 hr_utility.set_location('Calculation for Local Unit',40);
291
292 /* Pick up the details belonging to Local Unit */
293
294 OPEN csr_Local_Unit_Details( g_local_unit_id);
295 FETCH csr_Local_Unit_Details INTO rg_Local_Unit_Details;
296 CLOSE csr_Local_Unit_Details;
297
298 l_lu_name := rg_Local_Unit_Details.name ;
299 l_business_id := l_y_number||'-'||rg_Local_Unit_Details.ORG_INFORMATION1 ;
300
301 OPEN csr_Local_Unit_Details( g_local_unit_id);
302 FETCH csr_Local_Unit_Details INTO rg_Local_Unit_Details;
303 CLOSE csr_Local_Unit_Details;
304
305 hr_utility.set_location('Pick up the details belonging to Local Unit',60);
306
307 /* Pick up the contact details belonging to Local Unit*/
308
309 OPEN csr_Local_Unit_contact( g_local_unit_id);
310 FETCH csr_Local_Unit_contact INTO rg_Local_Unit_contact;
311 CLOSE csr_Local_Unit_contact;
312
313 l_contact_person := rg_Local_Unit_contact.contact_person ;
314 l_phone := rg_Local_Unit_contact.phone ;
315
316 hr_utility.set_location('Pick up the contact details belonging to Local Unit',70);
317 fnd_file.put_line(fnd_file.log,'Range Code 10');
318 ELSE
319 fnd_file.put_line(fnd_file.log,'Range Code 11');
320 g_emp_type:='LE' ;
321 l_emp_id:=g_legal_employer_id ;
322
323 /* Pick up the details belonging to Legal Employer */
324
325 OPEN csr_Legal_Emp_Details(g_legal_employer_id);
326 FETCH csr_Legal_Emp_Details INTO rg_Legal_Emp_Details;
327 CLOSE csr_Legal_Emp_Details;
328
329 l_le_name := rg_Legal_Emp_Details.name ;
330 l_business_id := rg_Legal_Emp_Details.ORG_INFORMATION1 ;
331
332 /* Pick up the contact details belonging to Legal Employer */
333
334 OPEN csr_Legal_Emp_contact( g_legal_employer_id);
335 FETCH csr_Legal_Emp_contact INTO rg_Legal_Emp_contact;
336 CLOSE csr_Legal_Emp_contact;
337
338 l_contact_person := rg_Legal_Emp_Contact .contact_person ;
339 l_phone := rg_Legal_Emp_Contact .phone ;
340
341 fnd_file.put_line(fnd_file.log,'Range Code 12');
342
343 END IF ;
344
345 fnd_file.put_line(fnd_file.log,'Range Code 13');
346
347
348 pay_action_information_api.create_action_information (
349 p_action_information_id => l_action_info_id
350 ,p_action_context_id => p_payroll_action_id
351 ,p_action_context_type => 'PA'
352 ,p_object_version_number => l_ovn
353 ,p_effective_date => g_effective_date
354 ,p_source_id => NULL
355 ,p_source_text => NULL
356 ,p_action_information_category => 'EMEA REPORT INFORMATION'
357 ,p_action_information1 => 'PYFIDPSA'
358 ,p_action_information2 => g_emp_type
359 ,p_action_information3 => l_emp_id
360 ,p_action_information4 => l_business_id
361 ,p_action_information5 => l_org_type
362 ,p_action_information6 => l_contact_person
363 ,p_action_information7 => l_phone
364 ,p_action_information8 => g_year
365 ,p_action_information9 => g_transact_type
366 ,p_action_information10 => null
367 ,p_action_information11 =>null
368 ,p_action_information12 =>null
369 ,p_action_information13 => null
370 ,p_action_information14 => null
371 ,p_action_information15 => null
372 ,p_action_information16 => null
373 ,p_action_information17 => null
374 ,p_action_information18 => null
375 ,p_action_information19 => null
376 ,p_action_information20 => null
377 ,p_action_information21 => null
378 ,p_action_information22 => null
379 ,p_action_information23 => null
380 ,p_action_information24 => null
381 ,p_action_information25 => null
382 ,p_action_information26 => null
383 ,p_action_information27 => null
384 ,p_action_information28 => null
385 ,p_action_information29 => null
386 ,p_action_information30 => null );
387
388 fnd_file.put_line(fnd_file.log,'Range Code 14');
389
390 pay_action_information_api.create_action_information (
391 p_action_information_id => l_action_info_id
392 ,p_action_context_id => p_payroll_action_id
393 ,p_action_context_type => 'PA'
394 ,p_object_version_number => l_ovn
395 ,p_effective_date => g_effective_date
396 ,p_source_id => NULL
397 ,p_source_text => NULL
398 ,p_action_information_category => 'EMEA REPORT DETAILS'
399 ,p_action_information1 => 'PYFIDPSA'
400 ,p_action_information2 => l_le_name
401 ,p_action_information3 => l_lu_name
402 ,p_action_information4 => g_year
403 ,p_action_information5 => g_transact_type
404 ,p_action_information6 => null
405 ,p_action_information7 => null
406 ,p_action_information8 => null
407 ,p_action_information9 => null
408 ,p_action_information10 => null
409 ,p_action_information11 => null
410 ,p_action_information12 => null
411 ,p_action_information13 => null
412 ,p_action_information14 => null
413 ,p_action_information15 => null
414 ,p_action_information16 => null
415 ,p_action_information17 => null
416 ,p_action_information18 => null
417 ,p_action_information19 => null
418 ,p_action_information20 => null
419 ,p_action_information21 => null
420 ,p_action_information22 => null
421 ,p_action_information23 => null
422 ,p_action_information24 => null
423 ,p_action_information25 => null
424 ,p_action_information26 => null
425 ,p_action_information27 => null
426 ,p_action_information28 => null
427 ,p_action_information29 => null
428 ,p_action_information30 => null );
429
430 fnd_file.put_line(fnd_file.log,'Range Code 15');
431
432 END IF;
433
434 END IF;
435
436 IF g_debug THEN
437 hr_utility.set_location(' Leaving Procedure RANGE_CODE',50);
438 END IF;
439
440 EXCEPTION
441 WHEN others THEN
442 IF g_debug THEN
443 hr_utility.set_location('error raised assignment_action_code ',5);
444 END if;
445 RAISE;
446 END RANGE_CODE;
447
448 /* ASSIGNMENT ACTION CODE */
449 PROCEDURE ASSIGNMENT_ACTION_CODE
450 (p_payroll_action_id IN NUMBER
451 ,p_start_person IN NUMBER
452 ,p_end_person IN NUMBER
453 ,p_chunk IN NUMBER )
454 IS
455
456 l_canonical_start_date DATE;
457 l_canonical_end_date DATE;
458 l_prepay_action_id NUMBER;
459 l_prev_person_id NUMBER;
460 l_prev_local_unit_id NUMBER;
461 l_actid NUMBER;
462
463 CURSOR csr_prepaid_assignments_lu
464 (p_payroll_action_id NUMBER
465 ,p_start_person NUMBER
466 ,p_end_person NUMBER
467 ,p_legal_employer_id NUMBER
468 ,p_local_unit_id NUMBER
469 ,l_canonical_start_date DATE
470 ,l_canonical_end_date DATE)
471 IS
472 SELECT as1.person_id person_id,
473 act.assignment_id assignment_id,
474 act.assignment_action_id run_action_id,
475 act1.assignment_action_id prepaid_action_id
476 FROM pay_payroll_actions ppa
477 ,pay_payroll_actions appa
478 ,pay_payroll_actions appa2
479 ,pay_assignment_actions act
480 ,pay_assignment_actions act1
481 ,pay_action_interlocks pai
482 ,per_all_assignments_f as1
483 ,hr_soft_coding_keyflex hsck
484 ,pay_run_result_values TARGET
485 ,pay_run_results RR
486 WHERE ppa.payroll_action_id = p_payroll_action_id
487 AND appa.effective_date BETWEEN l_canonical_start_date
488 AND l_canonical_end_date
489 AND as1.person_id BETWEEN p_start_person
490 AND p_end_person
491 AND appa.action_type IN ('R','Q')
492 -- Payroll Run or Quickpay Run
493 AND act.payroll_action_id = appa.payroll_action_id
494 AND act.source_action_id IS NULL -- Master Action
495 AND as1.assignment_id = act.assignment_id
496 -- Commenting Code to Include Terminated Assignments
497 -- AND ppa.effective_date BETWEEN as1.effective_start_date
498 -- AND as1.effective_end_date
499 AND act.action_status = 'C' -- Completed
500 AND act.assignment_action_id = pai.locked_action_id
501 AND act1.assignment_action_id = pai.locking_action_id
502 AND act1.action_status = 'C' -- Completed
503 AND act1.payroll_action_id = appa2.payroll_action_id
504 AND appa2.action_type IN ('P','U')
505 AND appa2.effective_date BETWEEN l_canonical_start_date
506 AND l_canonical_end_date
507 -- Prepayments or Quickpay Prepayments
508 AND hsck.SOFT_CODING_KEYFLEX_ID=as1.SOFT_CODING_KEYFLEX_ID
509 AND hsck.segment2 = to_char(p_local_unit_id)
510 AND act.TAX_UNIT_ID = act1.TAX_UNIT_ID
511 AND act.TAX_UNIT_ID = p_legal_employer_id
512 and TARGET.run_result_id = RR.run_result_id
513 AND (( RR.assignment_action_id
514 in ( Select act2.assignment_action_id
515 from pay_assignment_actions act2
516 Where act2.source_action_id=act.assignment_action_id
517 AND act2.action_status = 'C' -- Completed
518 AND act2.payroll_action_id = act.payroll_action_id))
519 or
520 (RR.assignment_action_id=act.assignment_action_id))
521 and RR.status in ('P','PA')
522 ORDER BY as1.person_id , act.assignment_id ;
523
524
525 CURSOR csr_prepaid_assignments_le(p_payroll_action_id NUMBER,
526 p_start_person NUMBER,
527 p_end_person NUMBER,
528 p_legal_employer_id NUMBER,
529 l_canonical_start_date DATE,
530 l_canonical_end_date DATE)
531 IS
532 SELECT as1.person_id person_id,
533 act.assignment_id assignment_id,
534 act.assignment_action_id run_action_id,
535 act1.assignment_action_id prepaid_action_id
536 FROM pay_payroll_actions ppa
537 ,pay_payroll_actions appa
538 ,pay_payroll_actions appa2
539 ,pay_assignment_actions act
540 ,pay_assignment_actions act1
541 ,pay_action_interlocks pai
542 ,per_all_assignments_f as1
543 ,pay_run_result_values TARGET
544 ,pay_run_results RR
545 WHERE ppa.payroll_action_id = p_payroll_action_id
546 AND appa.effective_date BETWEEN l_canonical_start_date
547 AND l_canonical_end_date
548 AND as1.person_id BETWEEN p_start_person
549 AND p_end_person
550 AND appa.action_type IN ('R','Q')
551 -- Payroll Run or Quickpay Run
552 AND act.payroll_action_id = appa.payroll_action_id
553 AND act.source_action_id IS NULL -- Master Action
554 AND as1.assignment_id = act.assignment_id
555 -- Commenting Code to Include Terminated Assignments
556 -- AND ppa.effective_date BETWEEN as1.effective_start_date
557 -- AND as1.effective_end_date
558 AND act.action_status = 'C' -- Completed
559 AND act.assignment_action_id = pai.locked_action_id
560 AND act1.assignment_action_id = pai.locking_action_id
561 AND act1.action_status = 'C' -- Completed
562 AND act1.payroll_action_id = appa2.payroll_action_id
563 AND appa2.action_type IN ('P','U')
564 AND appa2.effective_date BETWEEN l_canonical_start_date
565 AND l_canonical_end_date
566 -- Prepayments or Quickpay Prepayments
567 AND act.TAX_UNIT_ID = act1.TAX_UNIT_ID
568 AND act.TAX_UNIT_ID = p_legal_employer_id
569 and TARGET.run_result_id = RR.run_result_id
570 AND (( RR.assignment_action_id
571 in ( Select act2.assignment_action_id
572 from pay_assignment_actions act2
573 Where act2.source_action_id=act.assignment_action_id
574 AND act2.action_status = 'C' -- Completed
575 AND act2.payroll_action_id = act.payroll_action_id))
576 or
577 (RR.assignment_action_id=act.assignment_action_id))
578 and RR.status in ('P','PA')
579 ORDER BY as1.person_id , act.assignment_id;
580
581
582
583 BEGIN
584
585 fnd_file.put_line(fnd_file.log,'ASSIGNMENT_ACTION_CODE 1');
586
587 IF g_debug THEN
588 hr_utility.set_location(' Entering Procedure ASSIGNMENT_ACTION_CODE',60);
589 END IF;
590
591 PAY_FI_ARCHIVE_DPSA.GET_ALL_PARAMETERS(
592 p_payroll_action_id
593 ,g_business_group_id
594 ,g_legal_employer_id
595 ,g_local_unit_id
596 ,g_year
597 ,g_transact_type
598 ,g_deduction_ss
599 ,g_effective_date
600 ,g_archive ) ;
601
602 fnd_file.put_line(fnd_file.log,'ASSIGNMENT_ACTION_CODE 2');
603
604 g_payroll_action_id :=p_payroll_action_id;
605 l_canonical_start_date := TO_DATE('01'||g_year,'MMYYYY');
606 l_canonical_end_date := LAST_DAY(TO_DATE('12'||g_year,'MMYYYY'));
607 l_prepay_action_id := 0;
608 l_prev_person_id := 0;
609
610
611 fnd_file.put_line(fnd_file.log,'ASSIGNMENT_ACTION_CODE 3');
612
613 IF g_local_unit_id IS NOT NULL THEN
614
615 g_emp_type := 'LU';
616
617 fnd_file.put_line(fnd_file.log,'ASSIGNMENT_ACTION_CODE 4');
618
619 FOR rec_prepaid_assignments IN csr_prepaid_assignments_lu(p_payroll_action_id
620 ,p_start_person
621 ,p_end_person
622 ,g_legal_employer_id
623 ,g_local_unit_id
624 ,l_canonical_start_date
625 ,l_canonical_end_date)
626 LOOP
627 IF l_prepay_action_id <> rec_prepaid_assignments.prepaid_action_id
628 AND l_prev_person_id <> rec_prepaid_assignments.person_id THEN
629
630 SELECT pay_assignment_actions_s.NEXTVAL
631 INTO l_actid
632 FROM dual;
633
634 -- Create the archive assignment action
635
636 hr_nonrun_asact.insact(l_actid
637 ,rec_prepaid_assignments.assignment_id
638 ,p_payroll_action_id
639 ,p_chunk
640 ,NULL);
641 fnd_file.put_line(fnd_file.log,'ASSIGNMENT_ACTION_CODE 5'||rec_prepaid_assignments.person_id);
642
643 END IF;
644 l_prepay_action_id := rec_prepaid_assignments.prepaid_action_id;
645 l_prev_person_id := rec_prepaid_assignments.person_id;
646 END LOOP;
647
648 ELSE
649 g_emp_type := 'LE';
650
651 fnd_file.put_line(fnd_file.log,'ASSIGNMENT_ACTION_CODE 6');
652
653 FOR rec_prepaid_assignments IN csr_prepaid_assignments_le(p_payroll_action_id
654 ,p_start_person
655 ,p_end_person
656 ,g_legal_employer_id
657 ,l_canonical_start_date
658 ,l_canonical_end_date)
659 LOOP
660 IF l_prepay_action_id <> rec_prepaid_assignments.prepaid_action_id
661 AND l_prev_person_id <> rec_prepaid_assignments.person_id THEN
662
663 SELECT pay_assignment_actions_s.NEXTVAL
664 INTO l_actid
665 FROM dual;
666
667 -- Create the archive assignment action
668 hr_nonrun_asact.insact(l_actid
669 ,rec_prepaid_assignments.assignment_id
670 ,p_payroll_action_id
671 ,p_chunk
672 ,NULL);
673 fnd_file.put_line(fnd_file.log,'ASSIGNMENT_ACTION_CODE 7'||rec_prepaid_assignments.person_id);
674
675 END IF;
676 l_prepay_action_id := rec_prepaid_assignments.prepaid_action_id;
677 l_prev_person_id := rec_prepaid_assignments.person_id;
678 END LOOP;
679 END IF;
680
681 IF g_debug THEN
682 hr_utility.set_location(' Leaving Procedure ASSIGNMENT_ACTION_CODE',70);
683 END IF;
684
685 EXCEPTION
686 WHEN others THEN
687 IF g_debug THEN
688 hr_utility.set_location('error raised assignment_action_code ',5);
689 END if;
690 RAISE;
691 END ASSIGNMENT_ACTION_CODE;
692
693
694 /* INITIALIZATION CODE */
695 PROCEDURE INITIALIZATION_CODE(p_payroll_action_id IN NUMBER)
696 IS
697
698 BEGIN
699 IF g_debug THEN
700 hr_utility.set_location(' Entering Procedure INITIALIZATION_CODE',80);
701 END IF;
702 fnd_file.put_line(fnd_file.log,'INITIALIZATION_CODE 1');
703 IF g_debug THEN
704 hr_utility.set_location(' Leaving Procedure INITIALIZATION_CODE',90);
705 END IF;
706
707 EXCEPTION
708 WHEN others THEN
709 IF g_debug THEN
710 hr_utility.set_location('error raised initialization code ',5);
711 END if;
712 RAISE;
713 END INITIALIZATION_CODE;
714
715 /* ARCHIVE CODE */
716 PROCEDURE ARCHIVE_CODE(p_assignment_action_id IN NUMBER
717 ,p_effective_date IN DATE)
718 IS
719 /* Cursor to retrieve effective end date of the assignment*/
720 CURSOR csr_asg_effective_date
721 (p_asg_act_id NUMBER
722 ,p_start_date DATE
723 ,p_end_date DATE
724 ,p_business_group_id NUMBER) IS
725 SELECT MAX( EFFECTIVE_END_DATE) EFFECTIVE_END_DATE
726 FROM per_all_assignments paa
727 ,pay_assignment_actions pac
728 WHERE pac.assignment_action_id = p_asg_act_id
729 AND paa.assignment_id = pac.assignment_id
730 AND paa.EFFECTIVE_START_DATE <= p_end_date
731 AND paa.EFFECTIVE_END_DATE > = p_start_date
732 AND assignment_status_type_id IN
733 (select assignment_status_type_id
734 from per_assignment_status_types
735 where per_system_status = 'ACTIVE_ASSIGN'
736 and active_flag = 'Y'
737 and (( legislation_code is null
738 and business_group_id is null)
739 OR (BUSINESS_GROUP_ID = p_business_group_id)));
740
741 rg_csr_asg_effective_date csr_asg_effective_date%rowtype;
742
743 /* Cursor to retrieve Person Details */
744 CURSOR csr_get_person_details(p_asg_act_id NUMBER , p_asg_effective_date DATE ) IS
745 SELECT pap.first_name first_name , pap.last_name last_name , pap. national_identifier , pap. person_id , pac.assignment_id,
746 pap.per_information1 place_residence , pap.business_group_id , pap.per_information23 fpin
747 FROM
748 pay_assignment_actions pac,
749 per_all_assignments_f assign,
750 per_all_people_f pap
751 WHERE pac.assignment_action_id = p_asg_act_id
752 AND assign.assignment_id = pac.assignment_id
753 AND assign.person_id = pap.person_id
754 AND pap.per_information_category = 'FI'
755 AND p_asg_effective_date BETWEEN assign.effective_start_date
756 AND assign.effective_end_date
757 AND p_asg_effective_date BETWEEN pap.effective_start_date
758 AND pap.effective_end_date;
759
760 rg_csr_get_person_details csr_get_person_details%rowtype;
761
762 /* Cursor to retrieve Defined Balance Id */
763 Cursor csr_get_defined_balance_id(csr_v_Balance_Name FF_DATABASE_ITEMS.USER_NAME%TYPE)
764 IS
765 SELECT ue.creator_id
766 FROM ff_user_entities ue,
767 ff_database_items di
768 WHERE di.user_name = csr_v_Balance_Name
769 AND ue.user_entity_id = di.user_entity_id
770 AND ue.legislation_code = 'FI'
771 AND ue.business_group_id is NULL
772 AND ue.creator_type = 'B';
773
774 rg_csr_get_defined_balance_id csr_get_defined_balance_id%rowtype;
775
776 /* Cursor to retrieve Defined Balance Id */
777 Cursor csr_bg_get_defined_balance_id
778 (csr_v_Balance_Name FF_DATABASE_ITEMS.USER_NAME%TYPE
779 ,p_business_group_id NUMBER)
780 IS
781 SELECT ue.creator_id
782 FROM ff_user_entities ue,
783 ff_database_items di
784 WHERE di.user_name = csr_v_Balance_Name
785 AND ue.user_entity_id = di.user_entity_id
786 AND ue.legislation_code is NULL
787 AND ue.business_group_id = p_business_group_id
788 AND ue.creator_type = 'B';
789
790 rg_csr_bg_get_defined_bal_id csr_bg_get_defined_balance_id%rowtype;
791
792 /* Cursor to retrieve Balance Types having a particular Balance Category */
793 CURSOR csr_balance
794 (p_balance_category_name VARCHAR2
795 ,p_business_group_id NUMBER)
796 IS
797 SELECT REPLACE(UPPER(pbt.balance_name),' ' ,'_') balance_name
798 FROM pay_balance_types pbt , pay_balance_categories_f pbc
799 WHERE pbc.legislation_code='FI'
800 AND pbt.business_group_id =p_business_group_id
801 AND pbt.balance_category_id = pbc.balance_category_id
802 AND pbc.category_name = p_balance_category_name ;
803
804
805 /* Cursor to retrieve data from the Header record(Employer level) */
806 CURSOR csr_rpt_header (p_asg_act_id NUMBER) IS
807 SELECT action_context_id payroll_action_id ,action_information2 emp_type ,action_information3 emp_id
808 ,action_information4 business_id ,action_information5 org_type ,action_information6 contact_person
809 ,action_information7 phone ,action_information8 year ,action_information9 transact_type
810 FROM pay_action_information pai , pay_assignment_actions paa
811 WHERE pai.action_information_category = 'EMEA REPORT INFORMATION'
812 AND pai.action_information1 = 'PYFIDPSA'
813 AND pai.action_context_id = paa.payroll_action_id
814 AND paa.assignment_action_id = p_asg_act_id;
815
816 rg_csr_rpt_header csr_rpt_header%rowtype;
817
818 /* Cursor to retrieve Person Address Details */
819 CURSOR csr_per_address
820 (p_person_id PER_ADDRESSES_V.PERSON_ID%TYPE
821 ,p_business_group_id PER_ADDRESSES_V.BUSINESS_GROUP_ID%TYPE )
822 IS
823 SELECT address_line1||' '||address_line2 address , postal_code , d_country
824 FROM per_addresses_v
825 WHERE ADDRESS_TYPE='FI_PR'
826 AND BUSINESS_GROUP_ID = p_business_group_id
827 AND PERSON_ID = p_person_id;
828
829 rg_csr_per_address csr_per_address%rowtype;
830
831 /* Cursor to retrieve Tax Card Type*/
832 CURSOR csr_get_tax_card_type(p_assignment_id NUMBER , p_start_date DATE , p_end_date DATE ) IS
833 SELECT eev1.screen_entry_value screen_entry_value
834 FROM per_all_assignments_f asg1
835 ,per_all_assignments_f asg2
836 ,per_all_people_f per
837 ,pay_element_links_f el
838 ,pay_element_types_f et
839 ,pay_input_values_f iv1
840 ,pay_element_entries_f ee
841 ,pay_element_entry_values_f eev1
842 WHERE asg1.assignment_id = p_assignment_id
843 AND per.person_id = asg1.person_id
844 AND asg2.person_id = per.person_id
845 AND asg2.primary_flag = 'Y'
846 AND et.element_name = 'Tax Card'
847 AND et.legislation_code = 'FI'
848 AND iv1.element_type_id = et.element_type_id
849 AND iv1.name = 'Tax Card Type'
850 AND el.business_group_id = per.business_group_id
851 AND el.element_type_id = et.element_type_id
852 AND ee.assignment_id = asg2.assignment_id
853 AND ee.element_link_id = el.element_link_id
854 AND eev1.element_entry_id = ee.element_entry_id
855 AND eev1.input_value_id = iv1.input_value_id
856 AND asg1.effective_end_date > p_start_date
857 AND asg1.effective_start_date < p_end_date
858 AND per.effective_end_date > p_start_date
859 AND per.effective_start_date < p_end_date
860 AND asg2.effective_end_date > p_start_date
861 AND asg2.effective_start_date < p_end_date
862 AND ee.effective_end_date > p_start_date
863 AND ee.effective_start_date < p_end_date
864 AND ((eev1.effective_start_date < p_start_date
865 AND eev1.effective_end_date > p_start_date )
866 OR (eev1.effective_start_date BETWEEN p_start_date AND p_end_date
867 AND eev1.effective_end_date > p_end_date ));
868
869 rg_csr_get_tax_card_type csr_get_tax_card_type%rowtype;
870
871 /* Cursor to check archived data */
872 CURSOR csr_arch_chk (p_record_id VARCHAR2, p_payment_type VARCHAR2, p_assignment_action_id NUMBER) IS
873 SELECT COUNT(*)
874 FROM pay_action_information
875 WHERE action_information_category = 'EMEA REPORT INFORMATION'
876 AND action_context_type = 'AAP'
877 AND action_context_id= p_assignment_action_id
878 AND action_information1 =p_record_id
879 AND action_information2 = p_payment_type ;
880
881 /* Cursor to check archived data */
882 CURSOR csr_country (p_country_code VARCHAR2) IS
883 SELECT territory_short_name territory_name , TERRITORY_CODE||' - '||territory_short_name territory_short_name
884 FROM fnd_territories_VL
885 WHERE TERRITORY_CODE=p_country_code;
886
887 rg_csr_country csr_country%rowtype;
888
889
890 l_assignment_action_id NUMBER;
891 l_action_context_id NUMBER;
892 l_flag NUMBER := 0;
893 l_action_info_id NUMBER;
894 l_ovn NUMBER;
895 l_tax_card_type VARCHAR2(5);
896 l_payment_type VARCHAR2(5);
897 l_source_text VARCHAR2(10);
898 l_source_text2 VARCHAR2(10);
899 l_org_type VARCHAR2(5);
900 l_country_code varchar2(50);
901 l_age_category varchar2(1);
902
903 l_wtax_base NUMBER ;
904 l_tstax_base NUMBER;
905 l_tax_base NUMBER;
906 l_notional_base NUMBER;
907 l_person_type VARCHAR2(3);
908 l_record_id VARCHAR2(10);
909 l_dimension VARCHAR2(100);
910 l_dimension1 VARCHAR2(100);
911 l_dimension2 VARCHAR2(100);
912 l_start_date DATE ;
913 l_end_date DATE ;
914 l_effective_date DATE ;
915 l_ptp_1_wtax_base NUMBER;
916 l_ptp_2_wtax_base NUMBER;
917 l_pt1_1_wtax_base NUMBER;
918 l_pt1_2_wtax_base NUMBER;
919 l_ptp2_1_wtax_base NUMBER;
920 l_ptp2_2_wtax_base NUMBER;
921 l_pth_1_wtax_base NUMBER;
922 l_pth_2_wtax_base NUMBER;
923 l_pth2_1_wtax_base NUMBER;
924 l_pth2_2_wtax_base NUMBER;
925 l_ptg1_base NUMBER;
926 l_ptg_base NUMBER;
927 l_pth4_base NUMBER;
928 l_mtax_base NUMBER;
929 l_mtax NUMBER;
930 l_tax NUMBER;
931 l_empl_unemp_ins NUMBER;
932 l_pretax_ded NUMBER;
933 l_cum_car_benefit NUMBER;
934 l_cum_mileage NUMBER;
935 l_bik NUMBER;
936 l_tot_mortgage_bik NUMBER;
937 l_mortgage_bik NUMBER;
938 l_mortgage_bik_status NUMBER;
939 l_other_bik NUMBER;
940 l_tot_other_bik NUMBER;
941 l_other_bik_status NUMBER;
942 l_tot_housing_bik NUMBER;
943 l_housing_bik NUMBER;
944 l_housing_bik_status NUMBER;
945 l_tot_phone_bik NUMBER;
946 l_phone_bik NUMBER;
947 l_phone_bik_status NUMBER;
948 l_lunch_bik NUMBER;
949 l_lunch_bik_status NUMBER;
950 l_external_expenses NUMBER;
951 l_daily_allowance_d_expenses NUMBER;
952 l_daily_allowance_d_status NUMBER;
953 l_half_day_allowance_expenses NUMBER;
954 l_half_day_allowance_status NUMBER;
955 l_daily_allowance_fe_expenses NUMBER;
956 l_daily_allowance_fe_status NUMBER;
957 l_meal_comp_expenses NUMBER;
958 l_meal_comp_status NUMBER;
959 l_tax_free_mileage NUMBER;
960 l_tax_free_mileage_expenses NUMBER;
961 l_stock_option_bik NUMBER;
962 l_tot_stock_option_bik NUMBER;
963 l_emp_pension NUMBER;
964 l_travel_ticket_bik NUMBER;
965 l_tot_travel_ticket_bik NUMBER;
966 l_travel_ticket_bik_status NUMBER;
967 l_pta1_1_tstax_base NUMBER;
968 l_pta1_2_tstax_base NUMBER;
969 l_pta2_1_tstax_base NUMBER;
970 l_pta2_2_tstax_base NUMBER;
971 l_pta4_1_tstax_base NUMBER;
972 l_pta5_1_tstax_base NUMBER;
973 l_pta6_1_tstax_base NUMBER;
974 l_pta7_1_tstax_base NUMBER;
975 l_salary_income NUMBER;
976 l_social_security NUMBER;
977 l_count NUMBER;
978 l_empl_pension NUMBER;
979 l_full_car_benefit_status NUMBER;
980 l_bik_use_car_status NUMBER;
981 l_pt5_1_wtax_base NUMBER;
982 l_pt5_2_wtax_base NUMBER;
983 l_tax_status VARCHAR2(1);
984 l_place_residence VARCHAR2(60);
985 l_ptg1_tax NUMBER;
986 l_s1_tax_base NUMBER;
987 l_s1_tax NUMBER;
988 l_s2_tax_base NUMBER;
989 l_s2_tax NUMBER;
990 l_s3_tax_base NUMBER;
991 l_s3_tax NUMBER;
992 l_lunch_bik_ded_status NUMBER;
993 l_tot_lunch_bik_ded NUMBER;
994 l_lunch_bik_ded NUMBER;
995 l_tot_car_bik_ded NUMBER;
996 l_car_bik_ded NUMBER;
997 l_tot_other_bik_ded NUMBER;
998 l_other_bik_ded NUMBER;
999 l_tax_comp NUMBER;
1000 l_tot_pay_eoff NUMBER;
1001 l_pay_eoff NUMBER;
1002 l_tot_vol_pi NUMBER;
1003 l_vol_pi NUMBER;
1004 l_tot_total_vol_pi NUMBER;
1005 l_total_vol_pi NUMBER;
1006 l_te_exem_ss NUMBER;
1007 l_bik_exem_ss NUMBER;
1008 l_sm_exem_ss NUMBER;
1009 l_tot_exem_ss NUMBER;
1010 l_ss_ded NUMBER;
1011 l_631 NUMBER;
1012 l_exem_ss NUMBER;
1013 l_tot_ss_ded NUMBER;
1014 l_bal_date DATE;
1015 l_subsidy NUMBER;
1016 l_subsidy_status VARCHAR2(1) DEFAULT 'N' ;
1017 l_tax_type VARCHAR2(2);
1018 l_pt_pension_amt NUMBER;
1019 l_subsidy_amt NUMBER;
1020 l_subsidy_basis NUMBER;
1021 CODE_014 NUMBER;
1022 CODE_015 NUMBER;
1023 l_wtax NUMBER;
1024 l_te NUMBER;
1025 l_te_ss NUMBER;
1026 l_bik_ss NUMBER;
1027 l_sm_ss NUMBER;
1028 l_month VARCHAR2(2);
1029
1030 BEGIN
1031 fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 1');
1032 /*Initializing all balance variables*/
1033
1034 l_count :=0;
1035 l_wtax_base :=0;
1036 l_tstax_base :=0;
1037 l_tax_base :=0;
1038 l_notional_base :=0;
1039 l_ptp_1_wtax_base :=0;
1040 l_ptp_2_wtax_base :=0;
1041 l_pt1_1_wtax_base :=0;
1042 l_pt1_2_wtax_base :=0;
1043 l_ptp2_2_wtax_base :=0;
1044 l_ptp2_2_wtax_base :=0;
1045 l_pth_1_wtax_base :=0;
1046 l_pth_2_wtax_base :=0;
1047 l_pth2_1_wtax_base :=0;
1048 l_pth2_2_wtax_base :=0;
1049 l_ptg1_base :=0;
1050 l_ptg_base :=0;
1051 l_pth4_base :=0;
1052 l_mtax_base :=0;
1053 l_mtax :=0;
1054 l_tax :=0;
1055 l_empl_pension :=0;
1056 l_empl_unemp_ins :=0;
1057 l_pretax_ded :=0;
1058 l_cum_car_benefit :=0;
1059 l_cum_mileage :=0;
1060 l_bik :=0;
1061 l_tot_mortgage_bik :=0;
1062 l_mortgage_bik :=0;
1063 l_mortgage_bik_status :=0;
1064 l_other_bik :=0;
1065 l_tot_other_bik :=0;
1066 l_other_bik_status :=0;
1067 l_tot_housing_bik :=0;
1068 l_housing_bik :=0;
1069 l_housing_bik_status :=0;
1070 l_tot_phone_bik :=0;
1071 l_phone_bik :=0;
1072 l_phone_bik_status :=0;
1073 l_lunch_bik :=0;
1074 l_lunch_bik_status :=0;
1075 l_external_expenses :=0;
1076 l_daily_allowance_d_expenses :=0;
1077 l_daily_allowance_d_status :=0;
1078 l_half_day_allowance_expenses :=0;
1079 l_half_day_allowance_status :=0;
1080 l_daily_allowance_fe_expenses :=0;
1081 l_daily_allowance_fe_status :=0;
1082 l_meal_comp_expenses :=0;
1083 l_meal_comp_status :=0;
1084 l_tax_free_mileage :=0;
1085 l_tax_free_mileage_expenses :=0;
1086 l_stock_option_bik :=0;
1087 l_tot_stock_option_bik :=0;
1088 l_emp_pension :=0;
1089 l_travel_ticket_bik :=0;
1090 l_tot_travel_ticket_bik :=0;
1091 l_travel_ticket_bik_status :=0;
1092 l_pta1_1_tstax_base :=0;
1093 l_pta1_2_tstax_base :=0;
1094 l_pta2_1_tstax_base :=0;
1095 l_pta2_2_tstax_base :=0;
1096 l_pta4_1_tstax_base :=0;
1097 l_pta5_1_tstax_base :=0;
1098 l_pta6_1_tstax_base :=0;
1099 l_pta7_1_tstax_base :=0;
1100 l_salary_income :=0;
1101 l_social_security :=0;
1102 l_pt5_1_wtax_base :=0;
1103 l_pt5_2_wtax_base :=0;
1104 l_ptg1_tax := 0;
1105 l_s1_tax_base := 0;
1106 l_s1_tax := 0 ;
1107 l_s2_tax_base := 0;
1108 l_s2_tax := 0 ;
1109 l_s3_tax_base := 0;
1110 l_s3_tax := 0 ;
1111 l_lunch_bik_ded_status := 0 ;
1112 l_tot_lunch_bik_ded := 0 ;
1113 l_lunch_bik_ded := 0 ;
1114 l_tot_car_bik_ded := 0 ;
1115 l_car_bik_ded := 0 ;
1116 l_tot_other_bik_ded := 0 ;
1117 l_other_bik_ded := 0 ;
1118 l_tax_comp := 0 ;
1119 l_tot_pay_eoff := 0 ;
1120 l_pay_eoff := 0 ;
1121 l_tot_vol_pi := 0 ;
1122 l_vol_pi := 0 ;
1123 l_total_vol_pi := 0 ;
1124 l_tot_total_vol_pi := 0 ;
1125 l_te_exem_ss := 0 ;
1126 l_bik_exem_ss := 0 ;
1127 l_sm_exem_ss := 0 ;
1128 l_tot_exem_ss := 0 ;
1129 l_ss_ded := 0 ;
1130 l_631 := 0 ;
1131 l_exem_ss := 0 ;
1132 l_tot_ss_ded := 0 ;
1133 l_subsidy := 0 ;
1134 l_pt_pension_amt := 0 ;
1135 l_subsidy_amt := 0 ;
1136 l_subsidy_basis := 0 ;
1137 CODE_014 := 0 ;
1138 CODE_015 := 0 ;
1139 l_wtax := 0 ;
1140 l_te := 0 ;
1141 l_te_ss := 0 ;
1142 l_bik_ss := 0 ;
1143 l_sm_ss := 0 ;
1144 l_month := 0 ;
1145
1146
1147 IF g_debug THEN
1148 hr_utility.set_location(' Entering Procedure ARCHIVE_CODE',380);
1149 END IF;
1150
1151 /* Fetching data from the Header record(Employer level) */
1152 OPEN csr_rpt_header(p_assignment_action_id);
1153 FETCH csr_rpt_header INTO rg_csr_rpt_header;
1154 CLOSE csr_rpt_header;
1155
1156 /* Fetching report parameters */
1157 PAY_FI_ARCHIVE_DPSA.GET_ALL_PARAMETERS
1158 ( rg_csr_rpt_header.payroll_action_id
1159 ,g_business_group_id
1160 ,g_legal_employer_id
1161 ,g_local_unit_id
1162 ,g_year
1163 ,g_transact_type
1164 ,g_deduction_ss
1165 ,g_effective_date
1166 ,g_archive ) ;
1167
1168 fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 2');
1169
1170 IF g_archive='Y' THEN
1171
1172 l_start_date := TO_DATE('01'||g_year,'MMYYYY');
1173 l_end_date := LAST_DAY(TO_DATE('12'||g_year,'MMYYYY'));
1174 l_effective_date := l_end_date ;
1175 fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 3');
1176
1177 /* Fetching Person Details */
1178 OPEN csr_asg_effective_date(p_assignment_action_id , l_start_date , l_end_date , g_business_group_id );
1179 FETCH csr_asg_effective_date INTO rg_csr_asg_effective_date;
1180 CLOSE csr_asg_effective_date;
1181
1182 /* Fetching Person Details */
1183 OPEN csr_get_person_details(p_assignment_action_id , rg_csr_asg_effective_date.EFFECTIVE_END_DATE );
1184 FETCH csr_get_person_details INTO rg_csr_get_person_details;
1185 CLOSE csr_get_person_details;
1186
1187 /* Setting Context */
1188 BEGIN
1189 pay_balance_pkg.set_context('ASSIGNMENT_ACTION_ID',p_assignment_action_id);
1190 pay_balance_pkg.set_context('TAX_UNIT_ID',g_legal_employer_id);
1191 IF rg_csr_rpt_header.emp_type = 'LU' THEN
1192 pay_balance_pkg.set_context('LOCAL_UNIT_ID',g_local_unit_id);
1193 END IF;
1194
1195 END;
1196
1197 fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 4'||rg_csr_get_person_details.person_id);
1198 l_assignment_action_id:=p_assignment_action_id;
1199
1200 IF rg_csr_rpt_header.emp_type = 'LU' THEN
1201 l_dimension:='_PER_LU_EMPLTYPE_TC_YTD';
1202 l_dimension1:='_PER_LU_YTD';
1203 l_dimension2:='_PER_LU_MONTH';
1204 ELSE
1205 l_dimension:='_PER_LE_EMPLTYPE_TC_YTD';
1206 l_dimension1:='_PER_LE_YTD';
1207 l_dimension2:='_PER_LE_MONTH';
1208 END IF ;
1209
1210 fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 5');
1211 OPEN csr_Get_Defined_Balance_Id( 'WITHHOLDING_TAX_BASE'||l_dimension1);
1212 FETCH csr_Get_Defined_Balance_Id INTO rg_csr_get_defined_balance_id;
1213 CLOSE csr_Get_Defined_Balance_Id;
1214 l_wtax_base :=pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>rg_csr_get_defined_balance_id.creator_id, P_ASSIGNMENT_ACTION_ID =>l_assignment_action_id ) ;
1215
1216 OPEN csr_Get_Defined_Balance_Id( 'TAX_AT_SOURCE_BASE'||l_dimension1);
1217 FETCH csr_Get_Defined_Balance_Id INTO rg_csr_get_defined_balance_id;
1218 CLOSE csr_Get_Defined_Balance_Id;
1219 l_tstax_base :=pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>rg_csr_get_defined_balance_id.creator_id, P_ASSIGNMENT_ACTION_ID =>l_assignment_action_id ) ;
1220
1221 OPEN csr_Get_Defined_Balance_Id( 'NOTIONAL_SALARY'||l_dimension1);
1222 FETCH csr_Get_Defined_Balance_Id INTO rg_csr_get_defined_balance_id;
1223 CLOSE csr_Get_Defined_Balance_Id;
1224 l_notional_base :=pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>rg_csr_get_defined_balance_id.creator_id, P_ASSIGNMENT_ACTION_ID =>l_assignment_action_id ) ;
1225
1226 fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 6');
1227
1228
1229 /*Determining Resident Type*/
1230 IF l_wtax_base > 0 THEN
1231 -- Finnish Resident
1232 l_person_type:= 'FI';
1233 l_record_id := 'VSPSERIE';
1234 ELSIF l_tstax_base > 0 THEN
1235 -- Foreign Resident
1236 l_person_type:= 'FR';
1237 l_record_id := 'VSRAERIE';
1238 ELSIF l_notional_base > 0 THEN
1239 -- Finnish Resident Working Abroad
1240 l_person_type:= 'WO';
1241 l_record_id := 'VSPSERIE';
1242 END IF;
1243
1244 fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 7');
1245
1246 OPEN csr_get_tax_card_type(rg_csr_get_person_details.assignment_id ,l_start_date, l_end_date );
1247 FETCH csr_get_tax_card_type INTO l_tax_card_type;
1248 CLOSE csr_get_tax_card_type;
1249
1250 fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 8');
1251 IF l_tax_card_type IS NOT NULL AND l_person_type IS NOT NULL THEN
1252 fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 9');
1253
1254 OPEN csr_Get_Defined_Balance_Id( 'BENEFITS_IN_KIND'||l_dimension1);
1255 FETCH csr_Get_Defined_Balance_Id INTO rg_csr_get_defined_balance_id;
1256 CLOSE csr_Get_Defined_Balance_Id;
1257
1258 l_bik :=pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>rg_csr_get_defined_balance_id.creator_id, P_ASSIGNMENT_ACTION_ID =>l_assignment_action_id ) ;
1259
1260
1261 OPEN csr_Get_Defined_Balance_Id( 'SALARY_INCOME'||l_dimension1);
1262 FETCH csr_Get_Defined_Balance_Id INTO rg_csr_get_defined_balance_id;
1263 CLOSE csr_Get_Defined_Balance_Id;
1264
1265 l_salary_income :=pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>rg_csr_get_defined_balance_id.creator_id, P_ASSIGNMENT_ACTION_ID =>l_assignment_action_id ) ;
1266
1267 /*Low Paid Employees*/
1268 OPEN csr_Get_Defined_Balance_Id( 'SUBSIDY_FOR_LOW_PAID_EMPLOYEES'||l_dimension1);
1269 FETCH csr_Get_Defined_Balance_Id INTO rg_csr_get_defined_balance_id;
1270 CLOSE csr_Get_Defined_Balance_Id;
1271 l_subsidy :=pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>rg_csr_get_defined_balance_id.creator_id, P_ASSIGNMENT_ACTION_ID =>l_assignment_action_id ) ;
1272
1273 IF l_subsidy > 0 THEN
1274 l_subsidy_status:='Y' ;
1275 END IF;
1276
1277 fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 10');
1278
1279 IF l_person_type= 'FI' THEN
1280
1281 /*Determining Payment Type*/
1282 OPEN csr_Get_Defined_Balance_Id( 'WITHHOLDING_TAX_BASE'||l_dimension);
1283 FETCH csr_Get_Defined_Balance_Id INTO rg_csr_get_defined_balance_id;
1284 CLOSE csr_Get_Defined_Balance_Id;
1285
1286 fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 11');
1287
1288 IF l_tax_card_type in ('P', 'C' ) THEN
1289
1290 fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 12');
1291 l_ptp_1_wtax_base :=pay_balance_pkg.get_value
1292 (P_DEFINED_BALANCE_ID =>rg_csr_get_defined_balance_id.creator_id
1293 ,P_ASSIGNMENT_ACTION_ID =>l_assignment_action_id
1294 ,P_TAX_UNIT_ID =>g_legal_employer_id
1295 ,P_JURISDICTION_CODE =>NULL
1296 ,P_SOURCE_ID =>NULL
1297 ,P_SOURCE_TEXT =>'PEMP'
1298 ,P_TAX_GROUP =>NULL
1299 ,P_DATE_EARNED =>l_effective_date
1300 ,P_GET_RR_ROUTE =>NULL
1301 ,P_GET_RB_ROUTE =>NULL
1302 ,P_SOURCE_TEXT2 =>'N'
1303 ,P_SOURCE_NUMBER =>NULL
1304 ,P_TIME_DEF_ID =>NULL
1305 ,P_BALANCE_DATE =>NULL
1306 ,P_PAYROLL_ID =>NULL
1307 ,P_ORIGINAL_ENTRY_ID =>NULL
1308 ,P_LOCAL_UNIT_ID =>g_local_unit_id
1309 ,P_SOURCE_NUMBER2 =>NULL
1310 ,P_ORGANIZATION_ID =>NULL
1311 ) ;
1312
1313 l_ptp_2_wtax_base :=pay_balance_pkg.get_value
1314 (P_DEFINED_BALANCE_ID =>rg_csr_get_defined_balance_id.creator_id
1315 ,P_ASSIGNMENT_ACTION_ID =>l_assignment_action_id
1316 ,P_TAX_UNIT_ID =>g_legal_employer_id
1317 ,P_JURISDICTION_CODE =>NULL
1318 ,P_SOURCE_ID =>NULL
1319 ,P_SOURCE_TEXT =>'PEMP'
1320 ,P_TAX_GROUP =>NULL
1321 ,P_DATE_EARNED =>l_effective_date
1322 ,P_GET_RR_ROUTE =>NULL
1323 ,P_GET_RB_ROUTE =>NULL
1324 ,P_SOURCE_TEXT2 =>'S'
1325 ,P_SOURCE_NUMBER =>NULL
1326 ,P_TIME_DEF_ID =>NULL
1327 ,P_BALANCE_DATE =>NULL
1328 ,P_PAYROLL_ID =>NULL
1329 ,P_ORIGINAL_ENTRY_ID =>NULL
1330 ,P_LOCAL_UNIT_ID =>g_local_unit_id
1331 ,P_SOURCE_NUMBER2 =>NULL
1332 ,P_ORGANIZATION_ID =>NULL
1333 ) ;
1334
1335 fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 13');
1336 END IF;
1337
1338 IF l_tax_card_type in ('EI', 'FT','S' ) AND (l_ptp_1_wtax_base + l_ptp_2_wtax_base) <=0 THEN
1339 fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 14');
1340 l_pt1_1_wtax_base :=pay_balance_pkg.get_value
1341 (P_DEFINED_BALANCE_ID =>rg_csr_get_defined_balance_id.creator_id
1342 ,P_ASSIGNMENT_ACTION_ID =>l_assignment_action_id
1343 ,P_TAX_UNIT_ID =>g_legal_employer_id
1344 ,P_JURISDICTION_CODE =>NULL
1345 ,P_SOURCE_ID =>NULL
1346 ,P_SOURCE_TEXT =>'SEMP'
1347 ,P_TAX_GROUP =>NULL
1348 ,P_DATE_EARNED =>l_effective_date
1349 ,P_GET_RR_ROUTE =>NULL
1350 ,P_GET_RB_ROUTE =>NULL
1351 ,P_SOURCE_TEXT2 =>'N'
1352 ,P_SOURCE_NUMBER =>NULL
1353 ,P_TIME_DEF_ID =>NULL
1354 ,P_BALANCE_DATE =>NULL
1355 ,P_PAYROLL_ID =>NULL
1356 ,P_ORIGINAL_ENTRY_ID =>NULL
1357 ,P_LOCAL_UNIT_ID =>g_local_unit_id
1358 ,P_SOURCE_NUMBER2 =>NULL
1359 ,P_ORGANIZATION_ID =>NULL
1360 ) ;
1361
1362 l_pt1_2_wtax_base :=pay_balance_pkg.get_value
1363 (P_DEFINED_BALANCE_ID =>rg_csr_get_defined_balance_id.creator_id
1364 ,P_ASSIGNMENT_ACTION_ID =>l_assignment_action_id
1365 ,P_TAX_UNIT_ID =>g_legal_employer_id
1366 ,P_JURISDICTION_CODE =>NULL
1367 ,P_SOURCE_ID =>NULL
1368 ,P_SOURCE_TEXT =>'SEMP'
1369 ,P_TAX_GROUP =>NULL
1370 ,P_DATE_EARNED =>l_effective_date
1371 ,P_GET_RR_ROUTE =>NULL
1372 ,P_GET_RB_ROUTE =>NULL
1373 ,P_SOURCE_TEXT2 =>'S'
1374 ,P_SOURCE_NUMBER =>NULL
1375 ,P_TIME_DEF_ID =>NULL
1376 ,P_BALANCE_DATE =>NULL
1377 ,P_PAYROLL_ID =>NULL
1378 ,P_ORIGINAL_ENTRY_ID =>NULL
1379 ,P_LOCAL_UNIT_ID =>g_local_unit_id
1380 ,P_SOURCE_NUMBER2 =>NULL
1381 ,P_ORGANIZATION_ID =>NULL
1382 ) ;
1383 fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 15');
1384 END IF;
1385
1386 IF l_tax_card_type in ('C','FT','EI','P', 'S' )
1387 AND (l_ptp_1_wtax_base + l_ptp_2_wtax_base + l_pt1_1_wtax_base + l_pt1_2_wtax_base ) <=0 THEN
1388 fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 16');
1389 l_ptp2_1_wtax_base :=pay_balance_pkg.get_value
1390 (P_DEFINED_BALANCE_ID =>rg_csr_get_defined_balance_id.creator_id
1391 ,P_ASSIGNMENT_ACTION_ID =>l_assignment_action_id
1392 ,P_TAX_UNIT_ID =>g_legal_employer_id
1393 ,P_JURISDICTION_CODE =>NULL
1394 ,P_SOURCE_ID =>NULL
1395 ,P_SOURCE_TEXT =>'PEMP'
1396 ,P_TAX_GROUP =>NULL
1397 ,P_DATE_EARNED =>l_effective_date
1398 ,P_GET_RR_ROUTE =>NULL
1399 ,P_GET_RB_ROUTE =>NULL
1400 ,P_SOURCE_TEXT2 =>'A'
1401 ,P_SOURCE_NUMBER =>NULL
1402 ,P_TIME_DEF_ID =>NULL
1403 ,P_BALANCE_DATE =>NULL
1404 ,P_PAYROLL_ID =>NULL
1405 ,P_ORIGINAL_ENTRY_ID =>NULL
1406 ,P_LOCAL_UNIT_ID =>g_local_unit_id
1407 ,P_SOURCE_NUMBER2 =>NULL
1408 ,P_ORGANIZATION_ID =>NULL
1409 ) ;
1410
1411 l_ptp2_2_wtax_base :=pay_balance_pkg.get_value
1412 (P_DEFINED_BALANCE_ID =>rg_csr_get_defined_balance_id.creator_id
1413 ,P_ASSIGNMENT_ACTION_ID =>l_assignment_action_id
1414 ,P_TAX_UNIT_ID =>g_legal_employer_id
1415 ,P_JURISDICTION_CODE =>NULL
1416 ,P_SOURCE_ID =>NULL
1417 ,P_SOURCE_TEXT =>'SEMP'
1418 ,P_TAX_GROUP =>NULL
1419 ,P_DATE_EARNED =>l_effective_date
1420 ,P_GET_RR_ROUTE =>NULL
1421 ,P_GET_RB_ROUTE =>NULL
1422 ,P_SOURCE_TEXT2 =>'A'
1423 ,P_SOURCE_NUMBER =>NULL
1424 ,P_TIME_DEF_ID =>NULL
1425 ,P_BALANCE_DATE =>NULL
1426 ,P_PAYROLL_ID =>NULL
1427 ,P_ORIGINAL_ENTRY_ID =>NULL
1428 ,P_LOCAL_UNIT_ID =>g_local_unit_id
1429 ,P_SOURCE_NUMBER2 =>NULL
1430 ,P_ORGANIZATION_ID =>NULL
1431 ) ;
1432 fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 17');
1433
1434 END IF;
1435
1436
1437 IF l_tax_card_type in ('EI', 'FT','S' )
1438 AND (l_ptp_1_wtax_base + l_ptp_2_wtax_base + l_pt1_1_wtax_base + l_pt1_2_wtax_base + l_ptp2_2_wtax_base + l_ptp2_2_wtax_base) <=0 THEN
1439 fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 18');
1440 l_pth_1_wtax_base :=pay_balance_pkg.get_value
1441 (P_DEFINED_BALANCE_ID =>rg_csr_get_defined_balance_id.creator_id
1442 ,P_ASSIGNMENT_ACTION_ID =>l_assignment_action_id
1443 ,P_TAX_UNIT_ID =>g_legal_employer_id
1444 ,P_JURISDICTION_CODE =>NULL
1445 ,P_SOURCE_ID =>NULL
1446 ,P_SOURCE_TEXT =>'PUNEMP'
1447 ,P_TAX_GROUP =>NULL
1448 ,P_DATE_EARNED =>l_effective_date
1449 ,P_GET_RR_ROUTE =>NULL
1450 ,P_GET_RB_ROUTE =>NULL
1451 ,P_SOURCE_TEXT2 =>'N'
1452 ,P_SOURCE_NUMBER =>NULL
1453 ,P_TIME_DEF_ID =>NULL
1454 ,P_BALANCE_DATE =>NULL
1455 ,P_PAYROLL_ID =>NULL
1456 ,P_ORIGINAL_ENTRY_ID =>NULL
1457 ,P_LOCAL_UNIT_ID =>g_local_unit_id
1458 ,P_SOURCE_NUMBER2 =>NULL
1459 ,P_ORGANIZATION_ID =>NULL
1460 ) ;
1461
1462 l_pth_2_wtax_base :=pay_balance_pkg.get_value
1463 (P_DEFINED_BALANCE_ID =>rg_csr_get_defined_balance_id.creator_id
1464 ,P_ASSIGNMENT_ACTION_ID =>l_assignment_action_id
1465 ,P_TAX_UNIT_ID =>g_legal_employer_id
1466 ,P_JURISDICTION_CODE =>NULL
1467 ,P_SOURCE_ID =>NULL
1468 ,P_SOURCE_TEXT =>'SUNEMP'
1469 ,P_TAX_GROUP =>NULL
1470 ,P_DATE_EARNED =>l_effective_date
1471 ,P_GET_RR_ROUTE =>NULL
1472 ,P_GET_RB_ROUTE =>NULL
1473 ,P_SOURCE_TEXT2 =>'N'
1474 ,P_SOURCE_NUMBER =>NULL
1475 ,P_TIME_DEF_ID =>NULL
1476 ,P_BALANCE_DATE =>NULL
1477 ,P_PAYROLL_ID =>NULL
1478 ,P_ORIGINAL_ENTRY_ID =>NULL
1479 ,P_LOCAL_UNIT_ID =>g_local_unit_id
1480 ,P_SOURCE_NUMBER2 =>NULL
1481 ,P_ORGANIZATION_ID =>NULL
1482 ) ;
1483
1484 fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 19');
1485 END IF;
1486
1487 IF l_tax_card_type in ('EI', 'FT','S' )
1488 AND (l_ptp_1_wtax_base + l_ptp_2_wtax_base + l_pt1_1_wtax_base + l_pt1_2_wtax_base + l_ptp2_2_wtax_base
1489 + l_ptp2_2_wtax_base + l_pth_1_wtax_base + l_pth_2_wtax_base ) <=0 THEN
1490 fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 20');
1491 l_pth2_1_wtax_base :=pay_balance_pkg.get_value
1492 (P_DEFINED_BALANCE_ID =>rg_csr_get_defined_balance_id.creator_id
1493 ,P_ASSIGNMENT_ACTION_ID =>l_assignment_action_id
1494 ,P_TAX_UNIT_ID =>g_legal_employer_id
1495 ,P_JURISDICTION_CODE =>NULL
1496 ,P_SOURCE_ID =>NULL
1497 ,P_SOURCE_TEXT =>'PUNEMP'
1498 ,P_TAX_GROUP =>NULL
1499 ,P_DATE_EARNED =>l_effective_date
1500 ,P_GET_RR_ROUTE =>NULL
1501 ,P_GET_RB_ROUTE =>NULL
1502 ,P_SOURCE_TEXT2 =>'A'
1503 ,P_SOURCE_NUMBER =>NULL
1504 ,P_TIME_DEF_ID =>NULL
1505 ,P_BALANCE_DATE =>NULL
1506 ,P_PAYROLL_ID =>NULL
1507 ,P_ORIGINAL_ENTRY_ID =>NULL
1508 ,P_LOCAL_UNIT_ID =>g_local_unit_id
1509 ,P_SOURCE_NUMBER2 =>NULL
1510 ,P_ORGANIZATION_ID =>NULL
1511 ) ;
1512
1513 l_pth2_2_wtax_base :=pay_balance_pkg.get_value
1514 (P_DEFINED_BALANCE_ID =>rg_csr_get_defined_balance_id.creator_id
1515 ,P_ASSIGNMENT_ACTION_ID =>l_assignment_action_id
1516 ,P_TAX_UNIT_ID =>g_legal_employer_id
1517 ,P_JURISDICTION_CODE =>NULL
1518 ,P_SOURCE_ID =>NULL
1519 ,P_SOURCE_TEXT =>'SUNEMP'
1520 ,P_TAX_GROUP =>NULL
1521 ,P_DATE_EARNED =>l_effective_date
1522 ,P_GET_RR_ROUTE =>NULL
1523 ,P_GET_RB_ROUTE =>NULL
1524 ,P_SOURCE_TEXT2 =>'A'
1525 ,P_SOURCE_NUMBER =>NULL
1526 ,P_TIME_DEF_ID =>NULL
1527 ,P_BALANCE_DATE =>NULL
1528 ,P_PAYROLL_ID =>NULL
1529 ,P_ORIGINAL_ENTRY_ID =>NULL
1530 ,P_LOCAL_UNIT_ID =>g_local_unit_id
1531 ,P_SOURCE_NUMBER2 =>NULL
1532 ,P_ORGANIZATION_ID =>NULL
1533 ) ;
1534 fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 21');
1535 END IF;
1536 fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 22');
1537 /*Determining Contexts*/
1538 IF l_tax_card_type IN ('P', 'C' ) AND ( l_ptp_1_wtax_base + l_ptp_2_wtax_base ) > 0 THEN
1539 l_payment_type:= 'P';
1540 IF l_ptp_1_wtax_base > 0 THEN
1541 l_source_text :='PEMP';
1542 l_source_text2 :='N';
1543 l_tax_base:=l_ptp_1_wtax_base;
1544 ELSE
1545 l_source_text :='PEMP';
1546 l_source_text2 :='S';
1547 l_tax_base:=l_ptp_2_wtax_base;
1548 END IF;
1549 ELSIF l_tax_card_type in ('EI', 'FT','S' ) AND (l_pt1_1_wtax_base + l_pt1_2_wtax_base ) > 0 THEN
1550 l_payment_type:= '1';
1551 IF l_pt1_1_wtax_base > 0 THEN
1552 l_source_text :='SEMP';
1553 l_source_text2 :='N';
1554 l_tax_base:=l_pt1_1_wtax_base;
1555 ELSE
1556 l_source_text :='SEMP';
1557 l_source_text2 :='S';
1558 l_tax_base:=l_pt1_2_wtax_base;
1559 END IF;
1560 ELSIF l_tax_card_type in ('C','FT','EI','P', 'S' ) AND (l_ptp2_1_wtax_base + l_ptp2_2_wtax_base) > 0 THEN
1561 l_payment_type:= 'P2';
1562 IF l_ptp2_1_wtax_base > 0 THEN
1563 l_source_text :='PEMP';
1564 l_source_text2 :='A';
1565 l_tax_base:=l_ptp2_1_wtax_base;
1566 ELSE
1567 l_source_text :='SEMP';
1568 l_source_text2 :='A';
1569 l_tax_base:=l_ptp2_2_wtax_base;
1570 END IF;
1571
1572 ELSIF l_tax_card_type in ('EI', 'FT','S' ) AND (l_pth_1_wtax_base + l_pth_2_wtax_base ) > 0 THEN
1573 l_payment_type:= 'H';
1574 IF l_pth_1_wtax_base > 0 THEN
1575 l_source_text :='PUNEMP';
1576 l_source_text2 :='N';
1577 l_tax_base:=l_pth_1_wtax_base;
1578 ELSE
1579 l_source_text :='SUNEMP';
1580 l_source_text2 :='N';
1581 l_tax_base:=l_pth_2_wtax_base;
1582 END IF;
1583
1584 ELSIF l_tax_card_type in ('EI', 'FT','S' ) AND (l_pth2_1_wtax_base + l_pth2_2_wtax_base ) > 0 THEN
1585 l_payment_type:= 'H2';
1586 IF l_pth2_1_wtax_base > 0 THEN
1587 l_source_text :='PUNEMP';
1588 l_source_text2 :='A';
1589 l_tax_base:=l_pth2_1_wtax_base;
1590 ELSE
1591 l_source_text :='SUNEMP';
1592 l_source_text2 :='A';
1593 l_tax_base:=l_pth2_2_wtax_base;
1594 END IF;
1595 END IF;
1596
1597
1598 /*Fetching Balance values*/
1599 OPEN csr_Get_Defined_Balance_Id( 'WITHHOLDING_TAX'||l_dimension1);
1600 FETCH csr_Get_Defined_Balance_Id INTO rg_csr_get_defined_balance_id;
1601 CLOSE csr_Get_Defined_Balance_Id;
1602
1603 l_tax :=pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>rg_csr_get_defined_balance_id.creator_id, P_ASSIGNMENT_ACTION_ID =>l_assignment_action_id ) ;
1604
1605 OPEN csr_Get_Defined_Balance_Id( 'PENSION'||l_dimension1);
1606 FETCH csr_Get_Defined_Balance_Id INTO rg_csr_get_defined_balance_id;
1607 CLOSE csr_Get_Defined_Balance_Id;
1608
1609 l_empl_pension :=pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>rg_csr_get_defined_balance_id.creator_id, P_ASSIGNMENT_ACTION_ID =>l_assignment_action_id ) ;
1610
1611 OPEN csr_Get_Defined_Balance_Id( 'UNEMPLOYMENT_INSURANCE'||l_dimension1);
1612 FETCH csr_Get_Defined_Balance_Id INTO rg_csr_get_defined_balance_id;
1613 CLOSE csr_Get_Defined_Balance_Id;
1614
1615 l_empl_unemp_ins :=pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>rg_csr_get_defined_balance_id.creator_id, P_ASSIGNMENT_ACTION_ID =>l_assignment_action_id ) ;
1616
1617 OPEN csr_Get_Defined_Balance_Id( 'CUMULATIVE_CAR_BENEFIT'||l_dimension1);
1618 FETCH csr_Get_Defined_Balance_Id INTO rg_csr_get_defined_balance_id;
1619 CLOSE csr_Get_Defined_Balance_Id;
1620
1621 l_cum_car_benefit :=pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>rg_csr_get_defined_balance_id.creator_id, P_ASSIGNMENT_ACTION_ID =>l_assignment_action_id ) ;
1622
1623 OPEN csr_Get_Defined_Balance_Id( 'CUMULATIVE_VEHICLE_MILEAGE'||l_dimension1);
1624 FETCH csr_Get_Defined_Balance_Id INTO rg_csr_get_defined_balance_id;
1625 CLOSE csr_Get_Defined_Balance_Id;
1626
1627 l_cum_mileage :=pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>rg_csr_get_defined_balance_id.creator_id, P_ASSIGNMENT_ACTION_ID =>l_assignment_action_id ) ;
1628
1629 fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 23');
1630 BEGIN
1631 FOR balance_rec IN csr_balance('Mortgage Benefit' , rg_csr_get_person_details.business_group_id)
1632 LOOP
1633 OPEN csr_bg_Get_Defined_Balance_Id( balance_rec.balance_name||l_dimension1,rg_csr_get_person_details.business_group_id);
1634 FETCH csr_bg_Get_Defined_Balance_Id INTO rg_csr_bg_get_defined_bal_id;
1635 CLOSE csr_bg_Get_Defined_Balance_Id;
1636 IF csr_balance%FOUND THEN
1637 l_mortgage_bik :=pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>rg_csr_bg_get_defined_bal_id.creator_id, P_ASSIGNMENT_ACTION_ID =>l_assignment_action_id ) ;
1638 l_tot_mortgage_bik := l_tot_mortgage_bik + l_mortgage_bik;
1639 END IF;
1640 END LOOP ;
1641
1642 IF l_tot_mortgage_bik > 0 THEN
1643 l_mortgage_bik_status := 1;
1644 ELSE
1645 l_mortgage_bik_status := 0;
1646 END IF;
1647 EXCEPTION
1648 WHEN others THEN
1649 null;
1650 END;
1651
1652 BEGIN
1653 FOR balance_rec IN csr_balance('Other Benefits' , rg_csr_get_person_details.business_group_id)
1654 LOOP
1655 OPEN csr_bg_Get_Defined_Balance_Id( balance_rec.balance_name||l_dimension1,rg_csr_get_person_details.business_group_id);
1656 FETCH csr_bg_Get_Defined_Balance_Id INTO rg_csr_bg_get_defined_bal_id;
1657 CLOSE csr_bg_Get_Defined_Balance_Id;
1658 IF csr_balance%FOUND THEN
1659 l_other_bik :=pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>rg_csr_bg_get_defined_bal_id.creator_id, P_ASSIGNMENT_ACTION_ID =>l_assignment_action_id ) ;
1660 l_tot_other_bik := l_tot_other_bik + l_other_bik;
1661 END IF;
1662 END LOOP ;
1663
1664 IF l_tot_other_bik > 0 THEN
1665 l_other_bik_status := 1;
1666 ELSE
1667 l_other_bik_status := 0;
1668 END IF;
1669
1670
1671 EXCEPTION
1672 WHEN others THEN
1673 null;
1674 END;
1675
1676 BEGIN
1677 FOR balance_rec IN csr_balance('Housing Benefit' , rg_csr_get_person_details.business_group_id)
1678 LOOP
1679 OPEN csr_bg_Get_Defined_Balance_Id( balance_rec.balance_name||l_dimension1,rg_csr_get_person_details.business_group_id);
1680 FETCH csr_bg_Get_Defined_Balance_Id INTO rg_csr_bg_get_defined_bal_id;
1681 CLOSE csr_bg_Get_Defined_Balance_Id;
1682 IF csr_balance%FOUND THEN
1683 l_housing_bik :=pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>rg_csr_bg_get_defined_bal_id.creator_id, P_ASSIGNMENT_ACTION_ID =>l_assignment_action_id ) ;
1684 l_tot_housing_bik := l_tot_housing_bik + l_housing_bik;
1685 END IF;
1686 END LOOP ;
1687
1688 IF l_tot_housing_bik > 0 THEN
1689 l_housing_bik_status := 1;
1690 ELSE
1691 l_housing_bik_status := 0;
1692 END IF;
1693 EXCEPTION
1694 WHEN others THEN
1695 null;
1696 END;
1697
1698 BEGIN
1699 FOR balance_rec IN csr_balance('Phone Benefit' , rg_csr_get_person_details.business_group_id)
1700 LOOP
1701 OPEN csr_bg_Get_Defined_Balance_Id( balance_rec.balance_name||l_dimension1,rg_csr_get_person_details.business_group_id);
1702 FETCH csr_bg_Get_Defined_Balance_Id INTO rg_csr_bg_get_defined_bal_id;
1703 CLOSE csr_bg_Get_Defined_Balance_Id;
1704 IF csr_balance%FOUND THEN
1705 l_phone_bik :=pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>rg_csr_bg_get_defined_bal_id.creator_id, P_ASSIGNMENT_ACTION_ID =>l_assignment_action_id ) ;
1706 l_tot_phone_bik := l_tot_phone_bik + l_phone_bik;
1707 END IF;
1708 END LOOP ;
1709
1710 IF l_tot_phone_bik > 0 THEN
1711 l_phone_bik_status := 1;
1712 ELSE
1713 l_phone_bik_status := 0;
1714 END IF;
1715 EXCEPTION
1716 WHEN others THEN
1717 null;
1718 END;
1719
1720
1721 OPEN csr_Get_Defined_Balance_Id('LUNCH_BENEFIT'||l_dimension1);
1722 FETCH csr_Get_Defined_Balance_Id INTO rg_csr_get_defined_balance_id;
1723 CLOSE csr_Get_Defined_Balance_Id;
1724
1725 l_lunch_bik :=pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>rg_csr_get_defined_balance_id.creator_id, P_ASSIGNMENT_ACTION_ID =>l_assignment_action_id ) ;
1726
1727 IF l_lunch_bik > 0 THEN
1728 l_lunch_bik_status := 1;
1729 ELSE
1730 l_lunch_bik_status := 0;
1731 END IF;
1732
1733 OPEN csr_Get_Defined_Balance_Id( 'HALF_DAY_ALLOWANCE_EXPENSES'||l_dimension1);
1734 FETCH csr_Get_Defined_Balance_Id INTO rg_csr_get_defined_balance_id;
1735 CLOSE csr_Get_Defined_Balance_Id;
1736
1737 l_half_day_allowance_expenses :=pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>rg_csr_get_defined_balance_id.creator_id, P_ASSIGNMENT_ACTION_ID =>l_assignment_action_id ) ;
1738
1739 IF l_half_day_allowance_expenses > 0 THEN
1740 l_half_day_allowance_status :=1;
1741 ELSE
1742 l_half_day_allowance_status :=0;
1743 END IF;
1744
1745
1746 OPEN csr_Get_Defined_Balance_Id( 'DAILY_ALLOWANCE_DOMESTIC_EXPENSES'||l_dimension1);
1747 FETCH csr_Get_Defined_Balance_Id INTO rg_csr_get_defined_balance_id;
1748 CLOSE csr_Get_Defined_Balance_Id;
1749
1750 l_daily_allowance_d_expenses :=pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>rg_csr_get_defined_balance_id.creator_id, P_ASSIGNMENT_ACTION_ID =>l_assignment_action_id ) ;
1751 IF l_daily_allowance_d_expenses > 0 THEN
1752 l_daily_allowance_d_status :=1;
1753 ELSE
1754 l_daily_allowance_d_status :=0;
1755 END IF;
1756
1757
1758 OPEN csr_Get_Defined_Balance_Id( 'DAILY_ALLOWANCE_FOREIGN_EXPENSES'||l_dimension1);
1759 FETCH csr_Get_Defined_Balance_Id INTO rg_csr_get_defined_balance_id;
1760 CLOSE csr_Get_Defined_Balance_Id;
1761
1762 l_daily_allowance_fe_expenses :=pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>rg_csr_get_defined_balance_id.creator_id, P_ASSIGNMENT_ACTION_ID =>l_assignment_action_id ) ;
1763 IF l_daily_allowance_fe_expenses > 0 THEN
1764 l_daily_allowance_fe_status :=1;
1765 ELSE
1766 l_daily_allowance_fe_status :=0;
1767 END IF;
1768
1769
1770 OPEN csr_Get_Defined_Balance_Id( 'MEAL_COMPENSATION_EXPENSES'||l_dimension1);
1771 FETCH csr_Get_Defined_Balance_Id INTO rg_csr_get_defined_balance_id;
1772 CLOSE csr_Get_Defined_Balance_Id;
1773
1774 l_meal_comp_expenses :=pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>rg_csr_get_defined_balance_id.creator_id, P_ASSIGNMENT_ACTION_ID =>l_assignment_action_id ) ;
1775
1776 IF l_meal_comp_expenses > 0 THEN
1777 l_meal_comp_status :=1;
1778 ELSE
1779 l_meal_comp_status :=0;
1780 END IF;
1781
1782 OPEN csr_Get_Defined_Balance_Id( 'TAX_FREE_MILEAGE_EXPENSES'||l_dimension1);
1783 FETCH csr_Get_Defined_Balance_Id INTO rg_csr_get_defined_balance_id;
1784 CLOSE csr_Get_Defined_Balance_Id;
1785
1786 l_tax_free_mileage_expenses :=pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>rg_csr_get_defined_balance_id.creator_id, P_ASSIGNMENT_ACTION_ID =>l_assignment_action_id ) ;
1787
1788
1789 OPEN csr_Get_Defined_Balance_Id( 'TAX_FREE_MILEAGE'||l_dimension1);
1790 FETCH csr_Get_Defined_Balance_Id INTO rg_csr_get_defined_balance_id;
1791 CLOSE csr_Get_Defined_Balance_Id;
1792
1793 l_tax_free_mileage :=pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>rg_csr_get_defined_balance_id.creator_id, P_ASSIGNMENT_ACTION_ID =>l_assignment_action_id ) ;
1794
1795 BEGIN
1796 FOR balance_rec IN csr_balance('Stock Options Benefit' , rg_csr_get_person_details.business_group_id)
1797 LOOP
1798 OPEN csr_bg_Get_Defined_Balance_Id( balance_rec.balance_name||l_dimension1,rg_csr_get_person_details.business_group_id);
1799 FETCH csr_bg_Get_Defined_Balance_Id INTO rg_csr_bg_get_defined_bal_id;
1800 CLOSE csr_bg_Get_Defined_Balance_Id;
1801 IF csr_balance%FOUND THEN
1802 l_stock_option_bik :=pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>rg_csr_bg_get_defined_bal_id.creator_id, P_ASSIGNMENT_ACTION_ID =>l_assignment_action_id ) ;
1803 l_tot_stock_option_bik := l_tot_stock_option_bik + l_stock_option_bik;
1804 END IF;
1805 END LOOP ;
1806 EXCEPTION
1807 WHEN others THEN
1808 null;
1809 END;
1810
1811 OPEN csr_Get_Defined_Balance_Id( 'EMPLOYER_PENSION'||l_dimension1);
1812 FETCH csr_Get_Defined_Balance_Id INTO rg_csr_get_defined_balance_id;
1813 CLOSE csr_Get_Defined_Balance_Id;
1814
1815 l_emp_pension :=pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>rg_csr_get_defined_balance_id.creator_id, P_ASSIGNMENT_ACTION_ID =>l_assignment_action_id ) ;
1816
1817 BEGIN
1818 FOR balance_rec IN csr_balance('Travel Ticket Benefit' , rg_csr_get_person_details.business_group_id)
1819 LOOP
1820 OPEN csr_bg_Get_Defined_Balance_Id( balance_rec.balance_name||l_dimension1,rg_csr_get_person_details.business_group_id);
1821 FETCH csr_bg_Get_Defined_Balance_Id INTO rg_csr_bg_get_defined_bal_id;
1822 CLOSE csr_bg_Get_Defined_Balance_Id;
1823 IF csr_balance%FOUND THEN
1824 l_travel_ticket_bik :=pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>rg_csr_bg_get_defined_bal_id.creator_id, P_ASSIGNMENT_ACTION_ID =>l_assignment_action_id ) ;
1825 l_tot_travel_ticket_bik := l_tot_travel_ticket_bik + l_travel_ticket_bik;
1826 END IF;
1827 END LOOP ;
1828
1829 IF l_tot_travel_ticket_bik > 0 THEN
1830 l_travel_ticket_bik_status := 1;
1831 ELSE
1832 l_travel_ticket_bik_status := 0;
1833 END IF;
1834 EXCEPTION
1835 WHEN others THEN
1836 null;
1837 END;
1838
1839
1840 BEGIN
1841 FOR balance_rec IN csr_balance('Other Benefits Deductions' , rg_csr_get_person_details.business_group_id)
1842 LOOP
1843 OPEN csr_bg_Get_Defined_Balance_Id( balance_rec.balance_name||l_dimension1,rg_csr_get_person_details.business_group_id);
1844 FETCH csr_bg_Get_Defined_Balance_Id INTO rg_csr_bg_get_defined_bal_id;
1845 CLOSE csr_bg_Get_Defined_Balance_Id;
1846 IF csr_balance%FOUND THEN
1847 l_other_bik_ded :=pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>rg_csr_bg_get_defined_bal_id.creator_id, P_ASSIGNMENT_ACTION_ID =>l_assignment_action_id ) ;
1848 l_tot_other_bik_ded := l_tot_other_bik_ded + l_other_bik_ded;
1849 END IF;
1850 END LOOP ;
1851
1852 EXCEPTION
1853 WHEN others THEN
1854 null;
1855 END;
1856
1857
1858 BEGIN
1859 FOR balance_rec IN csr_balance('Car Benefit Deductions' , rg_csr_get_person_details.business_group_id)
1860 LOOP
1861 OPEN csr_bg_Get_Defined_Balance_Id( balance_rec.balance_name||l_dimension1,rg_csr_get_person_details.business_group_id);
1862 FETCH csr_bg_Get_Defined_Balance_Id INTO rg_csr_bg_get_defined_bal_id;
1863 CLOSE csr_bg_Get_Defined_Balance_Id;
1864 IF csr_balance%FOUND THEN
1865 l_car_bik_ded :=pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>rg_csr_bg_get_defined_bal_id.creator_id, P_ASSIGNMENT_ACTION_ID =>l_assignment_action_id ) ;
1866 l_tot_car_bik_ded := l_tot_car_bik_ded + l_car_bik_ded;
1867 END IF;
1868 END LOOP ;
1869
1870 EXCEPTION
1871 WHEN others THEN
1872 null;
1873 END;
1874
1875 BEGIN
1876 FOR balance_rec IN csr_balance('Lunch Benefit Deductions' , rg_csr_get_person_details.business_group_id)
1877 LOOP
1878 OPEN csr_bg_Get_Defined_Balance_Id( balance_rec.balance_name||l_dimension1,rg_csr_get_person_details.business_group_id);
1879 FETCH csr_bg_Get_Defined_Balance_Id INTO rg_csr_bg_get_defined_bal_id;
1880 CLOSE csr_bg_Get_Defined_Balance_Id;
1881 IF csr_balance%FOUND THEN
1882 l_lunch_bik_ded :=pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>rg_csr_bg_get_defined_bal_id.creator_id, P_ASSIGNMENT_ACTION_ID =>l_assignment_action_id ) ;
1883 l_tot_lunch_bik_ded := l_tot_lunch_bik_ded + l_lunch_bik_ded;
1884 END IF;
1885 END LOOP ;
1886
1887 IF l_tot_lunch_bik_ded > 0 THEN
1888 l_lunch_bik_ded_status := 1;
1889 ELSE
1890 l_lunch_bik_ded_status := 0;
1891 END IF;
1892 EXCEPTION
1893 WHEN others THEN
1894 null;
1895 END;
1896
1897 OPEN csr_Get_Defined_Balance_Id( 'TAXABLE_COMPENSATION'||l_dimension1);
1898 FETCH csr_Get_Defined_Balance_Id INTO rg_csr_get_defined_balance_id;
1899 CLOSE csr_Get_Defined_Balance_Id;
1900
1901 l_tax_comp :=pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>rg_csr_get_defined_balance_id.creator_id, P_ASSIGNMENT_ACTION_ID =>l_assignment_action_id ) ;
1902
1903 BEGIN
1904 FOR balance_rec IN csr_balance('Payments for Elected Official' , rg_csr_get_person_details.business_group_id)
1905 LOOP
1906 OPEN csr_bg_Get_Defined_Balance_Id( balance_rec.balance_name||l_dimension1,rg_csr_get_person_details.business_group_id);
1907 FETCH csr_bg_Get_Defined_Balance_Id INTO rg_csr_bg_get_defined_bal_id;
1908 CLOSE csr_bg_Get_Defined_Balance_Id;
1909 IF csr_balance%FOUND THEN
1910 l_pay_eoff :=pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>rg_csr_bg_get_defined_bal_id.creator_id, P_ASSIGNMENT_ACTION_ID =>l_assignment_action_id ) ;
1911 l_tot_pay_eoff := l_tot_pay_eoff + l_pay_eoff;
1912 END IF;
1913 END LOOP ;
1914 EXCEPTION
1915 WHEN others THEN
1916 null;
1917 END;
1918
1919
1920 BEGIN
1921 FOR balance_rec IN csr_balance('Voluntary PI Fees' , rg_csr_get_person_details.business_group_id)
1922 LOOP
1923 OPEN csr_bg_Get_Defined_Balance_Id( balance_rec.balance_name||l_dimension1,rg_csr_get_person_details.business_group_id);
1924 FETCH csr_bg_Get_Defined_Balance_Id INTO rg_csr_bg_get_defined_bal_id;
1925 CLOSE csr_bg_Get_Defined_Balance_Id;
1926 IF csr_balance%FOUND THEN
1927 l_vol_pi :=pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>rg_csr_bg_get_defined_bal_id.creator_id, P_ASSIGNMENT_ACTION_ID =>l_assignment_action_id ) ;
1928 l_tot_vol_pi := l_tot_vol_pi + l_vol_pi;
1929 END IF;
1930 END LOOP ;
1931 EXCEPTION
1932 WHEN others THEN
1933 null;
1934 END;
1935
1936 BEGIN
1937 FOR balance_rec IN csr_balance('Total Voluntary PI Fees' , rg_csr_get_person_details.business_group_id)
1938 LOOP
1939 OPEN csr_bg_Get_Defined_Balance_Id( balance_rec.balance_name||l_dimension1,rg_csr_get_person_details.business_group_id);
1940 FETCH csr_bg_Get_Defined_Balance_Id INTO rg_csr_bg_get_defined_bal_id;
1941 CLOSE csr_bg_Get_Defined_Balance_Id;
1942 IF csr_balance%FOUND THEN
1943 l_total_vol_pi :=pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>rg_csr_bg_get_defined_bal_id.creator_id, P_ASSIGNMENT_ACTION_ID =>l_assignment_action_id ) ;
1944 l_tot_total_vol_pi := l_tot_total_vol_pi + l_total_vol_pi;
1945 END IF;
1946 END LOOP ;
1947 EXCEPTION
1948 WHEN others THEN
1949 null;
1950 END;
1951
1952
1953 OPEN csr_Get_Defined_Balance_Id( 'TAXABLE_EXPENSES'||l_dimension1);
1954 FETCH csr_Get_Defined_Balance_Id INTO rg_csr_get_defined_balance_id;
1955 CLOSE csr_Get_Defined_Balance_Id;
1956 l_te :=pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>rg_csr_get_defined_balance_id.creator_id, P_ASSIGNMENT_ACTION_ID =>l_assignment_action_id ) ;
1957
1958 OPEN csr_Get_Defined_Balance_Id( 'TAXABLE_EXPENSES_SUBJECT_TO_SOCIAL_SECURITY'||l_dimension1);
1959 FETCH csr_Get_Defined_Balance_Id INTO rg_csr_get_defined_balance_id;
1960 CLOSE csr_Get_Defined_Balance_Id;
1961 l_te_ss :=pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>rg_csr_get_defined_balance_id.creator_id, P_ASSIGNMENT_ACTION_ID =>l_assignment_action_id ) ;
1962 l_te_exem_ss :=l_te - l_te_ss;
1963
1964 OPEN csr_Get_Defined_Balance_Id( 'BIK_SUBJECT_TO_SOCIAL_SECURITY'||l_dimension1);
1965 FETCH csr_Get_Defined_Balance_Id INTO rg_csr_get_defined_balance_id;
1966 CLOSE csr_Get_Defined_Balance_Id;
1967
1968 l_bik_ss :=pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>rg_csr_get_defined_balance_id.creator_id, P_ASSIGNMENT_ACTION_ID =>l_assignment_action_id ) ;
1969 l_bik_exem_ss :=l_bik - l_bik_ss;
1970
1971 OPEN csr_Get_Defined_Balance_Id( 'SALARY_SUBJECT_TO_SOCIAL_SECURITY'||l_dimension1);
1972 FETCH csr_Get_Defined_Balance_Id INTO rg_csr_get_defined_balance_id;
1973 CLOSE csr_Get_Defined_Balance_Id;
1974
1975 l_sm_ss :=pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>rg_csr_get_defined_balance_id.creator_id, P_ASSIGNMENT_ACTION_ID =>l_assignment_action_id ) ;
1976 l_sm_exem_ss := l_salary_income - l_sm_ss;
1977
1978 l_tot_exem_ss := l_te_exem_ss + l_bik_exem_ss + l_sm_exem_ss;
1979
1980 OPEN csr_Get_Defined_Balance_Id( 'EXEMPTED_SOCIAL_SECURITY'||l_dimension1);
1981 FETCH csr_Get_Defined_Balance_Id INTO rg_csr_get_defined_balance_id;
1982 CLOSE csr_Get_Defined_Balance_Id;
1983
1984 l_exem_ss :=pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>rg_csr_get_defined_balance_id.creator_id, P_ASSIGNMENT_ACTION_ID =>l_assignment_action_id ) ;
1985
1986 BEGIN
1987 FOR balance_rec IN csr_balance('Social Security Fee Deductions' , rg_csr_get_person_details.business_group_id)
1988 LOOP
1989 OPEN csr_bg_Get_Defined_Balance_Id( balance_rec.balance_name||l_dimension1,rg_csr_get_person_details.business_group_id);
1990 FETCH csr_bg_Get_Defined_Balance_Id INTO rg_csr_bg_get_defined_bal_id;
1991 CLOSE csr_bg_Get_Defined_Balance_Id;
1992 IF csr_balance%FOUND THEN
1993 l_ss_ded :=pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>rg_csr_bg_get_defined_bal_id.creator_id, P_ASSIGNMENT_ACTION_ID =>l_assignment_action_id ) ;
1994 l_tot_ss_ded := l_tot_ss_ded + l_ss_ded;
1995 END IF;
1996 END LOOP ;
1997 EXCEPTION
1998 WHEN others THEN
1999 null;
2000 END;
2001
2002 l_631:= l_tot_ss_ded + l_exem_ss;
2003
2004 fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 24');
2005
2006
2007
2008 ELSIF l_person_type= 'FR' THEN
2009
2010 fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 25');
2011
2012 /*Determining Payment Type*/
2013 OPEN csr_Get_Defined_Balance_Id( 'TAX_AT_SOURCE_BASE'||l_dimension);
2014 FETCH csr_Get_Defined_Balance_Id INTO rg_csr_get_defined_balance_id;
2015 CLOSE csr_Get_Defined_Balance_Id;
2016
2017 l_pta1_1_tstax_base :=pay_balance_pkg.get_value
2018 (P_DEFINED_BALANCE_ID =>rg_csr_get_defined_balance_id.creator_id
2019 ,P_ASSIGNMENT_ACTION_ID =>l_assignment_action_id
2020 ,P_TAX_UNIT_ID =>g_legal_employer_id
2021 ,P_JURISDICTION_CODE =>NULL
2022 ,P_SOURCE_ID =>NULL
2023 ,P_SOURCE_TEXT =>'PEMP'
2024 ,P_TAX_GROUP =>NULL
2025 ,P_DATE_EARNED =>l_effective_date
2026 ,P_GET_RR_ROUTE =>NULL
2027 ,P_GET_RB_ROUTE =>NULL
2028 ,P_SOURCE_TEXT2 =>'N'
2029 ,P_SOURCE_NUMBER =>NULL
2030 ,P_TIME_DEF_ID =>NULL
2031 ,P_BALANCE_DATE =>NULL
2032 ,P_PAYROLL_ID =>NULL
2033 ,P_ORIGINAL_ENTRY_ID =>NULL
2034 ,P_LOCAL_UNIT_ID =>g_local_unit_id
2035 ,P_SOURCE_NUMBER2 =>NULL
2036 ,P_ORGANIZATION_ID =>NULL
2037 ) ;
2038
2039 IF l_pta1_1_tstax_base <= 0 THEN
2040 l_pta1_2_tstax_base :=pay_balance_pkg.get_value
2041 (P_DEFINED_BALANCE_ID =>rg_csr_get_defined_balance_id.creator_id
2042 ,P_ASSIGNMENT_ACTION_ID =>l_assignment_action_id
2043 ,P_TAX_UNIT_ID =>g_legal_employer_id
2044 ,P_JURISDICTION_CODE =>NULL
2045 ,P_SOURCE_ID =>NULL
2046 ,P_SOURCE_TEXT =>'PEMP'
2047 ,P_TAX_GROUP =>NULL
2048 ,P_DATE_EARNED =>l_effective_date
2049 ,P_GET_RR_ROUTE =>NULL
2050 ,P_GET_RB_ROUTE =>NULL
2051 ,P_SOURCE_TEXT2 =>'S'
2052 ,P_SOURCE_NUMBER =>NULL
2053 ,P_TIME_DEF_ID =>NULL
2054 ,P_BALANCE_DATE =>NULL
2055 ,P_PAYROLL_ID =>NULL
2056 ,P_ORIGINAL_ENTRY_ID =>NULL
2057 ,P_LOCAL_UNIT_ID =>g_local_unit_id
2058 ,P_SOURCE_NUMBER2 =>NULL
2059 ,P_ORGANIZATION_ID =>NULL
2060 ) ;
2061 END IF;
2062
2063 l_pta2_1_tstax_base := l_pta1_1_tstax_base;
2064
2065 l_pta2_2_tstax_base :=l_pta1_2_tstax_base;
2066
2067 IF (l_pta1_1_tstax_base + l_pta1_2_tstax_base + l_pta2_1_tstax_base + l_pta2_2_tstax_base ) <= 0 THEN
2068 l_pta4_1_tstax_base :=pay_balance_pkg.get_value
2069 (P_DEFINED_BALANCE_ID =>rg_csr_get_defined_balance_id.creator_id
2070 ,P_ASSIGNMENT_ACTION_ID =>l_assignment_action_id
2071 ,P_TAX_UNIT_ID =>g_legal_employer_id
2072 ,P_JURISDICTION_CODE =>NULL
2073 ,P_SOURCE_ID =>NULL
2074 ,P_SOURCE_TEXT =>'PUNEMP'
2075 ,P_TAX_GROUP =>NULL
2076 ,P_DATE_EARNED =>l_effective_date
2077 ,P_GET_RR_ROUTE =>NULL
2078 ,P_GET_RB_ROUTE =>NULL
2079 ,P_SOURCE_TEXT2 =>'N'
2080 ,P_SOURCE_NUMBER =>NULL
2081 ,P_TIME_DEF_ID =>NULL
2082 ,P_BALANCE_DATE =>NULL
2083 ,P_PAYROLL_ID =>NULL
2084 ,P_ORIGINAL_ENTRY_ID =>NULL
2085 ,P_LOCAL_UNIT_ID =>g_local_unit_id
2086 ,P_SOURCE_NUMBER2 =>NULL
2087 ,P_ORGANIZATION_ID =>NULL
2088 ) ;
2089 END IF;
2090
2091 IF (l_pta1_1_tstax_base + l_pta1_2_tstax_base + l_pta2_1_tstax_base + l_pta2_2_tstax_base + l_pta4_1_tstax_base ) <= 0 THEN
2092 l_pta5_1_tstax_base :=pay_balance_pkg.get_value
2093 (P_DEFINED_BALANCE_ID =>rg_csr_get_defined_balance_id.creator_id
2094 ,P_ASSIGNMENT_ACTION_ID =>l_assignment_action_id
2095 ,P_TAX_UNIT_ID =>g_legal_employer_id
2096 ,P_JURISDICTION_CODE =>NULL
2097 ,P_SOURCE_ID =>NULL
2098 ,P_SOURCE_TEXT =>'PUNEMP'
2099 ,P_TAX_GROUP =>NULL
2100 ,P_DATE_EARNED =>l_effective_date
2101 ,P_GET_RR_ROUTE =>NULL
2102 ,P_GET_RB_ROUTE =>NULL
2103 ,P_SOURCE_TEXT2 =>'PA'
2104 ,P_SOURCE_NUMBER =>NULL
2105 ,P_TIME_DEF_ID =>NULL
2106 ,P_BALANCE_DATE =>NULL
2107 ,P_PAYROLL_ID =>NULL
2108 ,P_ORIGINAL_ENTRY_ID =>NULL
2109 ,P_LOCAL_UNIT_ID =>g_local_unit_id
2110 ,P_SOURCE_NUMBER2 =>NULL
2111 ,P_ORGANIZATION_ID =>NULL
2112 ) ;
2113 END IF;
2114
2115 IF (l_pta1_1_tstax_base + l_pta1_2_tstax_base + l_pta2_1_tstax_base + l_pta2_2_tstax_base + l_pta4_1_tstax_base + l_pta5_1_tstax_base ) <= 0 THEN
2116 l_pta6_1_tstax_base :=pay_balance_pkg.get_value
2117 (P_DEFINED_BALANCE_ID =>rg_csr_get_defined_balance_id.creator_id
2118 ,P_ASSIGNMENT_ACTION_ID =>l_assignment_action_id
2119 ,P_TAX_UNIT_ID =>g_legal_employer_id
2120 ,P_JURISDICTION_CODE =>NULL
2121 ,P_SOURCE_ID =>NULL
2122 ,P_SOURCE_TEXT =>'PUNEMP'
2123 ,P_TAX_GROUP =>NULL
2124 ,P_DATE_EARNED =>l_effective_date
2125 ,P_GET_RR_ROUTE =>NULL
2126 ,P_GET_RB_ROUTE =>NULL
2127 ,P_SOURCE_TEXT2 =>'A'
2128 ,P_SOURCE_NUMBER =>NULL
2129 ,P_TIME_DEF_ID =>NULL
2130 ,P_BALANCE_DATE =>NULL
2131 ,P_PAYROLL_ID =>NULL
2132 ,P_ORIGINAL_ENTRY_ID =>NULL
2133 ,P_LOCAL_UNIT_ID =>g_local_unit_id
2134 ,P_SOURCE_NUMBER2 =>NULL
2135 ,P_ORGANIZATION_ID =>NULL
2136 ) ;
2137 END IF;
2138
2139 IF (l_pta1_1_tstax_base + l_pta1_2_tstax_base + l_pta2_1_tstax_base + l_pta2_2_tstax_base + l_pta4_1_tstax_base + l_pta5_1_tstax_base + l_pta6_1_tstax_base) <= 0 THEN
2140 l_pta7_1_tstax_base :=pay_balance_pkg.get_value
2141 (P_DEFINED_BALANCE_ID =>rg_csr_get_defined_balance_id.creator_id
2142 ,P_ASSIGNMENT_ACTION_ID =>l_assignment_action_id
2143 ,P_TAX_UNIT_ID =>g_legal_employer_id
2144 ,P_JURISDICTION_CODE =>NULL
2145 ,P_SOURCE_ID =>NULL
2146 ,P_SOURCE_TEXT =>'PEMP'
2147 ,P_TAX_GROUP =>NULL
2148 ,P_DATE_EARNED =>l_effective_date
2149 ,P_GET_RR_ROUTE =>NULL
2150 ,P_GET_RB_ROUTE =>NULL
2151 ,P_SOURCE_TEXT2 =>'KP'
2152 ,P_SOURCE_NUMBER =>NULL
2153 ,P_TIME_DEF_ID =>NULL
2154 ,P_BALANCE_DATE =>NULL
2155 ,P_PAYROLL_ID =>NULL
2156 ,P_ORIGINAL_ENTRY_ID =>NULL
2157 ,P_LOCAL_UNIT_ID =>g_local_unit_id
2158 ,P_SOURCE_NUMBER2 =>NULL
2159 ,P_ORGANIZATION_ID =>NULL
2160 ) ;
2161 END IF;
2162 fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 26');
2163 l_org_type:=rg_csr_rpt_header.org_type;
2164
2165 /*Determining Contexts*/
2166 IF l_tax_card_type IN ('TS' ) THEN
2167 fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 26'||l_tax_card_type);
2168
2169 IF (l_pta1_1_tstax_base + l_pta1_2_tstax_base) > 0 AND l_org_type = 'PRIV' THEN
2170 l_payment_type:= 'A1';
2171 IF l_pta1_1_tstax_base > 0 THEN
2172 l_source_text :='PEMP';
2173 l_source_text2 :='N';
2174 l_tax_base:=l_pta1_1_tstax_base;
2175 ELSE
2176 l_source_text :='PEMP';
2177 l_source_text2 :='S';
2178 l_tax_base:=l_pta1_2_tstax_base;
2179 END IF;
2180
2181 ELSIF (l_pta2_1_tstax_base + l_pta2_2_tstax_base) > 0 AND l_org_type = 'PUB' THEN
2182 l_payment_type:= 'A2';
2183 IF l_pta1_1_tstax_base > 0 THEN
2184 l_source_text :='PEMP';
2185 l_source_text2 :='N';
2186 l_tax_base:=l_pta2_1_tstax_base;
2187 ELSE
2188 l_source_text :='PEMP';
2189 l_source_text2 :='S';
2190 l_tax_base:=l_pta2_2_tstax_base;
2191 END IF;
2192
2193 ELSIF (l_pta4_1_tstax_base > 0 ) THEN
2194 l_payment_type:= 'A4';
2195 l_source_text :='PUNEMP';
2196 l_source_text2 :='N' ;
2197 l_tax_base:=l_pta4_1_tstax_base;
2198 ELSIF (l_pta5_1_tstax_base > 0 ) THEN
2199 l_payment_type:= 'A5';
2200 l_source_text :='PUNEMP';
2201 l_source_text2 :='PA' ;
2202 l_tax_base:=l_pta5_1_tstax_base;
2203 ELSIF (l_pta6_1_tstax_base > 0 ) THEN
2204 l_payment_type:= 'A6';
2205 l_source_text :='PUNEMP';
2206 l_source_text2 :='A' ;
2207 l_tax_base:=l_pta6_1_tstax_base;
2208 ELSIF (l_pta7_1_tstax_base > 0 ) THEN
2209 l_payment_type:= 'A7';
2210 l_source_text :='PEMP';
2211 l_source_text2 :='KP' ;
2212 l_tax_base:=l_pta7_1_tstax_base;
2213 END IF;
2214 fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 27');
2215 /* Fetching Person Address Details */
2216 OPEN csr_per_address( rg_csr_get_person_details.person_id , rg_csr_get_person_details.business_group_id );
2217 FETCH csr_per_address INTO rg_csr_per_address;
2218 CLOSE csr_per_address;
2219
2220 OPEN csr_Get_Defined_Balance_Id( 'TAX_AT_SOURCE'||l_dimension1);
2221 FETCH csr_Get_Defined_Balance_Id INTO rg_csr_get_defined_balance_id;
2222 CLOSE csr_Get_Defined_Balance_Id;
2223
2224 l_tax :=pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>rg_csr_get_defined_balance_id.creator_id, P_ASSIGNMENT_ACTION_ID =>l_assignment_action_id ) ;
2225
2226
2227 OPEN csr_Get_Defined_Balance_Id( 'SOCIAL_SECURITY'||l_dimension1);
2228 FETCH csr_Get_Defined_Balance_Id INTO rg_csr_get_defined_balance_id;
2229 CLOSE csr_Get_Defined_Balance_Id;
2230
2231 l_social_security :=pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>rg_csr_get_defined_balance_id.creator_id, P_ASSIGNMENT_ACTION_ID =>l_assignment_action_id ) ;
2232
2233 /* Cursor to fetch country code against which Tax Agreement is present*/
2234 OPEN csr_country( rg_csr_get_person_details.place_residence);
2235 FETCH csr_country INTO rg_csr_country;
2236 CLOSE csr_country;
2237
2238 IF rg_csr_country. territory_short_name IS NOT NULL THEN
2239 BEGIN
2240 l_tax_status:=hruserdt.get_table_value(g_business_group_id,'FI_REGIONAL_MEMBERSHIP','TAX AGREEMENT',rg_csr_country. territory_short_name,l_effective_date);
2241 EXCEPTION
2242 WHEN OTHERS THEN
2243 NULL;
2244 END;
2245 END IF;
2246
2247 IF l_tax_status<>'Y' THEN
2248 l_country_code:=null;
2249 l_place_residence:=rg_csr_country. territory_name ;
2250 ELSE
2251 l_country_code:=rg_csr_get_person_details.place_residence;
2252 l_place_residence:=null;
2253 END IF;
2254
2255 END IF;
2256 fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 28');
2257
2258 ELSIF l_person_type= 'WO' THEN
2259
2260 l_payment_type:= '5';
2261 /*
2262 fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 29');
2263 OPEN csr_Get_Defined_Balance_Id( 'NOTIONAL_SALARY'||l_dimension);
2264 FETCH csr_Get_Defined_Balance_Id INTO rg_csr_get_defined_balance_id;
2265 CLOSE csr_Get_Defined_Balance_Id;
2266
2267 l_pt5_1_wtax_base :=pay_balance_pkg.get_value
2268 (P_DEFINED_BALANCE_ID =>rg_csr_get_defined_balance_id.creator_id
2269 ,P_ASSIGNMENT_ACTION_ID =>l_assignment_action_id
2270 ,P_TAX_UNIT_ID =>g_legal_employer_id
2271 ,P_JURISDICTION_CODE =>NULL
2272 ,P_SOURCE_ID =>NULL
2273 ,P_SOURCE_TEXT =>'PEMP'
2274 ,P_TAX_GROUP =>NULL
2275 ,P_DATE_EARNED =>l_effective_date
2276 ,P_GET_RR_ROUTE =>NULL
2277 ,P_GET_RB_ROUTE =>NULL
2278 ,P_SOURCE_TEXT2 =>'WO'
2279 ,P_SOURCE_NUMBER =>NULL
2280 ,P_TIME_DEF_ID =>NULL
2281 ,P_BALANCE_DATE =>NULL
2282 ,P_PAYROLL_ID =>NULL
2283 ,P_ORIGINAL_ENTRY_ID =>NULL
2284 ,P_LOCAL_UNIT_ID =>g_local_unit_id
2285 ,P_SOURCE_NUMBER2 =>NULL
2286 ,P_ORGANIZATION_ID =>NULL
2287 ) ;
2288 l_pt5_2_wtax_base :=pay_balance_pkg.get_value
2289 (P_DEFINED_BALANCE_ID =>rg_csr_get_defined_balance_id.creator_id
2290 ,P_ASSIGNMENT_ACTION_ID =>l_assignment_action_id
2291 ,P_TAX_UNIT_ID =>g_legal_employer_id
2292 ,P_JURISDICTION_CODE =>NULL
2293 ,P_SOURCE_ID =>NULL
2294 ,P_SOURCE_TEXT =>'SEMP'
2295 ,P_TAX_GROUP =>NULL
2296 ,P_DATE_EARNED =>l_effective_date
2297 ,P_GET_RR_ROUTE =>NULL
2298 ,P_GET_RB_ROUTE =>NULL
2299 ,P_SOURCE_TEXT2 =>'WO'
2300 ,P_SOURCE_NUMBER =>NULL
2301 ,P_TIME_DEF_ID =>NULL
2302 ,P_BALANCE_DATE =>NULL
2303 ,P_PAYROLL_ID =>NULL
2304 ,P_ORIGINAL_ENTRY_ID =>NULL
2305 ,P_LOCAL_UNIT_ID =>g_local_unit_id
2306 ,P_SOURCE_NUMBER2 =>NULL
2307 ,P_ORGANIZATION_ID =>NULL
2308 ) ;
2309
2310
2311 IF (l_pt5_1_wtax_base > 0 ) THEN
2312 l_source_text :='PEMP';
2313 l_source_text2 :='WO' ;
2314 l_wtax_base:= l_pt5_1_wtax_base;
2315 ELSE
2316 l_source_text :='SEMP';
2317 l_source_text2 :='WO' ;
2318 l_wtax_base:= l_pt5_2_wtax_base;
2319 END IF;
2320 */
2321
2322 OPEN csr_Get_Defined_Balance_Id( 'NOTIONAL_SALARY'||l_dimension1);
2323 FETCH csr_Get_Defined_Balance_Id INTO rg_csr_get_defined_balance_id;
2324 CLOSE csr_Get_Defined_Balance_Id;
2325 l_tax_base :=pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>rg_csr_get_defined_balance_id.creator_id, P_ASSIGNMENT_ACTION_ID =>l_assignment_action_id ) ;
2326 l_tax := 0;
2327 l_mtax_base := l_tax_base ;
2328 l_mtax := l_tax;
2329
2330 END IF;
2331
2332
2333 IF l_person_type<> 'WO' THEN
2334 fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 31');
2335
2336 OPEN csr_Get_Defined_Balance_Id( 'OTHER_PAYMENTS_SUBJECT_TO_TAX'||l_dimension1);
2337 FETCH csr_Get_Defined_Balance_Id INTO rg_csr_get_defined_balance_id;
2338 CLOSE csr_Get_Defined_Balance_Id;
2339
2340 l_pth4_base :=pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>rg_csr_get_defined_balance_id.creator_id, P_ASSIGNMENT_ACTION_ID =>l_assignment_action_id ) ;
2341
2342 OPEN csr_Get_Defined_Balance_Id( 'COMPENSATION_FOR_USE_OF_ITEM'||l_dimension1);
2343 FETCH csr_Get_Defined_Balance_Id INTO rg_csr_get_defined_balance_id;
2344 CLOSE csr_Get_Defined_Balance_Id;
2345
2346 l_ptg_base :=pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>rg_csr_get_defined_balance_id.creator_id, P_ASSIGNMENT_ACTION_ID =>l_assignment_action_id ) ;
2347
2348 fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 32');
2349
2350 IF l_tax_card_type <>'TS' THEN
2351 fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 33');
2352
2353 OPEN csr_Get_Defined_Balance_Id( 'CAPITAL_INCOME_BASE'||l_dimension1);
2354 FETCH csr_Get_Defined_Balance_Id INTO rg_csr_get_defined_balance_id;
2355 CLOSE csr_Get_Defined_Balance_Id;
2356
2357 l_ptg1_base :=pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>rg_csr_get_defined_balance_id.creator_id, P_ASSIGNMENT_ACTION_ID =>l_assignment_action_id ) ;
2358
2359 OPEN csr_Get_Defined_Balance_Id( 'CAPITAL_INCOME_TAX'||l_dimension1);
2360 FETCH csr_Get_Defined_Balance_Id INTO rg_csr_get_defined_balance_id;
2361 CLOSE csr_Get_Defined_Balance_Id;
2362
2363 l_ptg1_tax :=pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>rg_csr_get_defined_balance_id.creator_id, P_ASSIGNMENT_ACTION_ID =>l_assignment_action_id ) ;
2364
2365
2366 END IF;
2367
2368 /*Proportion the Tax Base and Tax according to Payment Types*/
2369 fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 34');
2370 IF l_tax_base > 0 THEN
2371 l_mtax_base := l_tax_base - (l_pth4_base + l_ptg_base) ;
2372 l_mtax := round((l_mtax_base/l_tax_base) * l_tax,2);
2373
2374 IF l_pth4_base > 0 THEN
2375 l_s1_tax_base := l_pth4_base ;
2376 l_s1_tax := round((l_s1_tax_base/l_tax_base) * l_tax,2);
2377 END IF;
2378
2379 IF l_ptg_base > 0 THEN
2380 l_s2_tax_base := l_ptg_base ;
2381 l_s2_tax := round((l_s2_tax_base/l_tax_base) * l_tax,2);
2382 END IF;
2383
2384 IF l_ptg1_base > 0 THEN
2385 l_s3_tax_base := l_ptg1_base;
2386 l_s3_tax := l_ptg1_tax ;
2387 END IF;
2388
2389 END IF;
2390
2391
2392
2393
2394 OPEN csr_Get_Defined_Balance_Id( 'DEDUCTIONS_BEFORE_TAX'||l_dimension1);
2395 FETCH csr_Get_Defined_Balance_Id INTO rg_csr_get_defined_balance_id;
2396 CLOSE csr_Get_Defined_Balance_Id;
2397
2398 l_pretax_ded :=pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>rg_csr_get_defined_balance_id.creator_id, P_ASSIGNMENT_ACTION_ID =>l_assignment_action_id ) ;
2399
2400 OPEN csr_Get_Defined_Balance_Id( 'EXTERNAL_EXPENSES'||l_dimension1);
2401 FETCH csr_Get_Defined_Balance_Id INTO rg_csr_get_defined_balance_id;
2402 CLOSE csr_Get_Defined_Balance_Id;
2403
2404 l_external_expenses :=pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>rg_csr_get_defined_balance_id.creator_id, P_ASSIGNMENT_ACTION_ID =>l_assignment_action_id ) ;
2405 fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 35');
2406 END IF;
2407
2408 IF l_payment_type IS NOT NULL THEN
2409 fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 36');
2410 OPEN csr_arch_chk(l_record_id , l_payment_type , p_assignment_action_id);
2411 FETCH csr_arch_chk INTO l_count;
2412 CLOSE csr_arch_chk;
2413
2414 IF l_count < 1 and l_record_id ='VSPSERIE' THEN
2415 /* Values in action_information29 for record id VSPSERIE
2416 1 Identifier for record no 1 for primary payment type record for VSPSERIE
2417 2 Identifier for record no 2 for primary payment type record for VSPSERIE
2418 3 Identifier for records for Legal Persons
2419 */
2420
2421 IF l_source_text2 ='N' AND LENGTH(rg_csr_get_person_details.national_identifier) = 9 AND l_payment_type='H' THEN
2422
2423 FOR i IN 1..12
2424 LOOP
2425
2426 SELECT LAST_DAY(TO_DATE('01'||LPAD(i,2,'0')||g_year,'DDMMYYYY')) , LPAD(i,2,'0')
2427 INTO l_bal_date, l_month
2428 FROM DUAL;
2429
2430 IF TO_NUMBER(TO_CHAR(l_bal_date,'MM')) <= TO_NUMBER(TO_CHAR(rg_csr_asg_effective_date.EFFECTIVE_END_DATE,'MM')) THEN
2431
2432
2433 OPEN csr_Get_Defined_Balance_Id( 'WITHHOLDING_TAX'||l_dimension2);
2434 FETCH csr_Get_Defined_Balance_Id INTO rg_csr_get_defined_balance_id;
2435 CLOSE csr_Get_Defined_Balance_Id;
2436 l_wtax :=pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>rg_csr_get_defined_balance_id.creator_id, P_ASSIGNMENT_ID =>rg_csr_get_person_details.assignment_id , P_VIRTUAL_DATE => l_bal_date) ;
2437
2438
2439 OPEN csr_Get_Defined_Balance_Id( 'WITHHOLDING_TAX_BASE'||l_dimension2);
2440 FETCH csr_Get_Defined_Balance_Id INTO rg_csr_get_defined_balance_id;
2441 CLOSE csr_Get_Defined_Balance_Id;
2442 l_wtax_base :=pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>rg_csr_get_defined_balance_id.creator_id, P_ASSIGNMENT_ID =>rg_csr_get_person_details.assignment_id, P_VIRTUAL_DATE => l_bal_date) ;
2443
2444 pay_action_information_api.create_action_information (
2445 p_action_information_id=> l_action_info_id,
2446 p_action_context_id=> p_assignment_action_id,
2447 p_action_context_type=> 'AAP',
2448 p_object_version_number=> l_ovn,
2449 p_effective_date=> g_effective_date,
2450 p_source_id=> NULL,
2451 p_source_text=> NULL,
2452 p_action_information_category=> 'EMEA REPORT INFORMATION',
2453 p_action_information1=> 'PYFIDPSA',
2454 p_action_information2=>rg_csr_get_person_details.person_id,
2455 p_action_information3=>l_record_id ,
2456 p_action_information4=>l_payment_type,
2457 p_action_information5=>g_transact_type ,
2458 p_action_information6=> g_year,
2459 p_action_information7=> rg_csr_rpt_header.business_id,
2460 p_action_information8=> rg_csr_get_person_details.national_identifier,
2461 p_action_information9=> FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_wtax_base*100,0)) ,
2462 p_action_information10=> FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_wtax*100,0)) ,
2463 p_action_information11=> NULL ,
2464 p_action_information12=> NULL ,
2465 p_action_information13=> NULL ,
2466 p_action_information14=> NULL ,
2467 p_action_information15=> l_month ,
2468 p_action_information16=> NULL ,
2469 p_action_information17=> NULL ,
2470 p_action_information18=> NULL ,
2471 p_action_information19=> NULL ,
2472 p_action_information20=> NULL ,
2473 p_action_information21=> NULL ,
2474 p_action_information22=> NULL ,
2475 p_action_information23=> NULL ,
2476 p_action_information24=> NULL ,
2477 p_action_information25=> NULL ,
2478 p_action_information26=> NULL ,
2479 p_action_information27=> NULL ,
2480 p_action_information28=> NULL ,
2481 p_action_information29=> '3' ,
2482 p_action_information30 => NULL);
2483
2484 /*Reason for putting i in action_information15 is to allow summation of records of type 3 and 1
2485 of the record id VSPSERIE*/
2486
2487 END IF;
2488
2489 END LOOP;
2490
2491 ELSE
2492
2493 fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 37');
2494 pay_action_information_api.create_action_information (
2495 p_action_information_id=> l_action_info_id,
2496 p_action_context_id=> p_assignment_action_id,
2497 p_action_context_type=> 'AAP',
2498 p_object_version_number=> l_ovn,
2499 p_effective_date=> g_effective_date,
2500 p_source_id=> NULL,
2501 p_source_text=> NULL,
2502 p_action_information_category=> 'EMEA REPORT INFORMATION',
2503 p_action_information1=> 'PYFIDPSA',
2504 p_action_information2=>rg_csr_get_person_details.person_id,
2505 p_action_information3=>l_record_id ,
2506 p_action_information4=>l_payment_type,
2507 p_action_information5=>g_transact_type ,
2508 p_action_information6=> g_year,
2509 p_action_information7=> rg_csr_rpt_header.business_id,
2510 p_action_information8=> rg_csr_get_person_details.national_identifier,
2511 p_action_information9=> FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_mtax_base*100,0) - nvl(l_bik*100,0)),
2512 p_action_information10=> FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_mtax*100,0)) ,
2513 p_action_information11=> FND_NUMBER.NUMBER_TO_CANONICAL((nvl(l_empl_pension,0) + nvl(l_empl_unemp_ins,0))*100 ) ,
2514 p_action_information12=> FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_pretax_ded*100,0)) ,
2515 p_action_information13=> FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_cum_car_benefit*100,0)) ,
2516 p_action_information14=> FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_cum_mileage,0)) ,
2517 p_action_information15=> l_age_category,
2518 p_action_information16=> FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_tot_mortgage_bik*100,0)) ,
2519 p_action_information17=> FND_NUMBER.NUMBER_TO_CANONICAL((nvl(l_tot_other_bik,0) +nvl(ROUND((l_tot_travel_ticket_bik*(3/4)),2),0))*100) ,
2520 p_action_information18=> l_housing_bik_status||l_phone_bik_status||l_lunch_bik_status||l_other_bik_status||l_travel_ticket_bik_status||l_lunch_bik_ded_status,
2521 p_action_information19=> FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_external_expenses*100,0)),
2522 p_action_information20=> l_daily_allowance_d_status||l_half_day_allowance_status||l_daily_allowance_fe_status||l_meal_comp_status,
2523 p_action_information21=> FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_tax_free_mileage,0)),
2524 p_action_information22=>FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_tax_free_mileage_expenses*100,0)),
2525 p_action_information23=> FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_tot_stock_option_bik*100,0)),
2526 p_action_information24=> FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_emp_pension*100,0)),
2527 p_action_information25=> FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_bik*100,0)),
2528 p_action_information26=> FND_NUMBER.NUMBER_TO_CANONICAL(nvl((ROUND((l_tot_travel_ticket_bik/4),2))*100,0)),
2529 p_action_information27=> FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_tot_exem_ss*100,0)),
2530 p_action_information28=> FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_631*100,0)),
2531 p_action_information29=> '1' ,
2532 p_action_information30 => NULL
2533 );
2534
2535 pay_action_information_api.create_action_information (
2536 p_action_information_id=> l_action_info_id,
2537 p_action_context_id=> p_assignment_action_id,
2538 p_action_context_type=> 'AAP',
2539 p_object_version_number=> l_ovn,
2540 p_effective_date=> g_effective_date,
2541 p_source_id=> NULL,
2542 p_source_text=> NULL,
2543 p_action_information_category=> 'EMEA REPORT INFORMATION',
2544 p_action_information1=> 'PYFIDPSA',
2545 p_action_information2=>rg_csr_get_person_details.person_id,
2546 p_action_information3=>l_record_id ,
2547 p_action_information4=>l_payment_type,
2548 p_action_information5=>g_transact_type ,
2549 p_action_information6=> g_year,
2550 p_action_information7=> rg_csr_rpt_header.business_id,
2551 p_action_information8=> rg_csr_get_person_details.national_identifier,
2552 p_action_information9=> FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_tax_comp*100,0)),
2553 p_action_information10=> FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_tot_pay_eoff *100,0)),
2554 p_action_information11=> FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_tot_vol_pi*100,0)),
2555 p_action_information12=> FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_tot_total_vol_pi*100,0)),
2556 p_action_information13=> l_full_car_benefit_status||l_bik_use_car_status,
2557 p_action_information14=> FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_tot_car_bik_ded*100,0)),
2558 p_action_information15=> FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_tot_other_bik_ded*100,0)),
2559 p_action_information16=> NULL,
2560 p_action_information17=> NULL,
2561 p_action_information18=> NULL,
2562 p_action_information19=> NULL,
2563 p_action_information20=> NULL,
2564 p_action_information21=> NULL,
2565 p_action_information22=> NULL,
2566 p_action_information23=> NULL,
2567 p_action_information24=> NULL,
2568 p_action_information25=> NULL,
2569 p_action_information26=> NULL,
2570 p_action_information27=> NULL,
2571 p_action_information28=> NULL,
2572 p_action_information29=> '2',
2573 p_action_information30 => NULL
2574 );
2575
2576 IF l_s1_tax_base > 0 THEN
2577 pay_action_information_api.create_action_information (
2578 p_action_information_id=> l_action_info_id,
2579 p_action_context_id=> p_assignment_action_id,
2580 p_action_context_type=> 'AAP',
2581 p_object_version_number=> l_ovn,
2582 p_effective_date=> g_effective_date,
2583 p_source_id=> NULL,
2584 p_source_text=> NULL,
2585 p_action_information_category=> 'EMEA REPORT INFORMATION',
2586 p_action_information1=> 'PYFIDPSA',
2587 p_action_information2=>rg_csr_get_person_details.person_id,
2588 p_action_information3=>l_record_id ,
2589 p_action_information4=>'H4',
2590 p_action_information5=>g_transact_type ,
2591 p_action_information6=> g_year,
2592 p_action_information7=> rg_csr_rpt_header.business_id,
2593 p_action_information8=> rg_csr_get_person_details.national_identifier,
2594 p_action_information9=> FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_s1_tax_base*100,0)),
2595 p_action_information10=> FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_s1_tax*100,0)) ,
2596 p_action_information11=> NULL,
2597 p_action_information12=> NULL,
2598 p_action_information13=> NULL,
2599 p_action_information14=> NULL,
2600 p_action_information15=> NULL,
2601 p_action_information16=> NULL,
2602 p_action_information17=> NULL,
2603 p_action_information18=> NULL,
2604 p_action_information19=> NULL,
2605 p_action_information20=> NULL,
2606 p_action_information21=> NULL,
2607 p_action_information22=> NULL,
2608 p_action_information23=> NULL,
2609 p_action_information24=> NULL,
2610 p_action_information25=> NULL,
2611 p_action_information26=> NULL,
2612 p_action_information27=> NULL,
2613 p_action_information28=> NULL,
2614 p_action_information29=> 1,
2615 p_action_information30 => NULL
2616 );
2617 END IF;
2618
2619 IF l_s2_tax_base > 0 THEN
2620 pay_action_information_api.create_action_information (
2621 p_action_information_id=> l_action_info_id,
2622 p_action_context_id=> p_assignment_action_id,
2623 p_action_context_type=> 'AAP',
2624 p_object_version_number=> l_ovn,
2625 p_effective_date=> g_effective_date,
2626 p_source_id=> NULL,
2627 p_source_text=> NULL,
2628 p_action_information_category=> 'EMEA REPORT INFORMATION',
2629 p_action_information1=> 'PYFIDPSA',
2630 p_action_information2=>rg_csr_get_person_details.person_id,
2631 p_action_information3=>l_record_id ,
2632 p_action_information4=>'G',
2633 p_action_information5=>g_transact_type ,
2634 p_action_information6=> g_year,
2635 p_action_information7=> rg_csr_rpt_header.business_id,
2636 p_action_information8=> rg_csr_get_person_details.national_identifier,
2637 p_action_information9=> FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_s2_tax_base*100,0)),
2638 p_action_information10=> FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_s2_tax*100,0)) ,
2639 p_action_information11=> NULL,
2640 p_action_information12=> NULL,
2641 p_action_information13=> NULL,
2642 p_action_information14=> NULL,
2643 p_action_information15=> NULL,
2644 p_action_information16=> NULL,
2645 p_action_information17=> NULL,
2646 p_action_information18=> NULL,
2647 p_action_information19=> NULL,
2648 p_action_information20=> NULL,
2649 p_action_information21=> NULL,
2650 p_action_information22=> NULL,
2651 p_action_information23=> NULL,
2652 p_action_information24=> NULL,
2653 p_action_information25=> NULL,
2654 p_action_information26=> NULL,
2655 p_action_information27=> NULL,
2656 p_action_information28=> NULL,
2657 p_action_information29=> 1,
2658 p_action_information30 =>NULL
2659 );
2660 END IF;
2661
2662 fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 38');
2663 IF l_s3_tax_base > 0 THEN
2664 pay_action_information_api.create_action_information (
2665 p_action_information_id=> l_action_info_id,
2666 p_action_context_id=> p_assignment_action_id,
2667 p_action_context_type=> 'AAP',
2668 p_object_version_number=> l_ovn,
2669 p_effective_date=> g_effective_date,
2670 p_source_id=> NULL,
2671 p_source_text=> NULL,
2672 p_action_information_category=> 'EMEA REPORT INFORMATION',
2673 p_action_information1=> 'PYFIDPSA',
2674 p_action_information2=>rg_csr_get_person_details.person_id,
2675 p_action_information3=>l_record_id ,
2676 p_action_information4=>'G1',
2677 p_action_information5=>g_transact_type ,
2678 p_action_information6=> g_year,
2679 p_action_information7=> rg_csr_rpt_header.business_id,
2680 p_action_information8=> rg_csr_get_person_details.national_identifier,
2681 p_action_information9=> FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_s3_tax_base*100,0)),
2682 p_action_information10=> FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_s3_tax*100,0)) ,
2683 p_action_information11=> NULL,
2684 p_action_information12=> NULL,
2685 p_action_information13=> NULL,
2686 p_action_information14=> NULL,
2687 p_action_information15=> NULL,
2688 p_action_information16=> NULL,
2689 p_action_information17=> NULL,
2690 p_action_information18=> NULL,
2691 p_action_information19=> NULL,
2692 p_action_information20=> NULL,
2693 p_action_information21=> NULL,
2694 p_action_information22=> NULL,
2695 p_action_information23=> NULL,
2696 p_action_information24=> NULL,
2697 p_action_information25=> NULL,
2698 p_action_information26=> NULL,
2699 p_action_information27=> NULL,
2700 p_action_information28=> NULL,
2701 p_action_information29=> 1,
2702 p_action_information30 => NULL
2703 );
2704 END IF;
2705
2706 END IF;
2707
2708 ELSIF l_count < 1 and l_record_id ='VSRAERIE' THEN
2709 fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 39');
2710
2711 pay_action_information_api.create_action_information (
2712 p_action_information_id=> l_action_info_id,
2713 p_action_context_id=> p_assignment_action_id,
2714 p_action_context_type=> 'AAP',
2715 p_object_version_number=> l_ovn,
2716 p_effective_date=> g_effective_date,
2717 p_source_id=> NULL,
2718 p_source_text=> NULL,
2719 p_action_information_category=> 'EMEA REPORT INFORMATION',
2720 p_action_information1=> 'PYFIDPSA',
2721 p_action_information2=>rg_csr_get_person_details.person_id,
2722 p_action_information3=>l_record_id ,
2723 p_action_information4=>l_payment_type,
2724 p_action_information5=>g_transact_type ,
2725 p_action_information6=> g_year,
2726 p_action_information7=> rg_csr_rpt_header.business_id,
2727 p_action_information8=> rg_csr_get_person_details.national_identifier,
2728 p_action_information9=> rg_csr_get_person_details.last_name ,
2729 p_action_information10=> rg_csr_get_person_details.first_name ,
2730 p_action_information11=> rg_csr_per_address.address ,
2731 p_action_information12=> rg_csr_per_address.postal_code ,
2732 p_action_information13=> rg_csr_per_address.d_country ,
2733 p_action_information14=> l_country_code ,
2734 p_action_information15=> FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_mtax_base*100,0) - nvl(l_bik*100,0)),
2735 p_action_information16=> FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_mtax*100,0)) ,
2736 p_action_information17=> FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_salary_income*100,0)) ,
2737 p_action_information18=> FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_bik*100,0)),
2738 p_action_information19=> FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_pretax_ded*100,0)) ,
2739 p_action_information20=> FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_external_expenses*100,0)),
2740 p_action_information21=> rg_csr_rpt_header.contact_person,
2741 p_action_information22=>rg_csr_rpt_header.phone,
2742 p_action_information23=> FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_social_security *100,0)),
2743 p_action_information24=> l_place_residence,
2744 p_action_information25=> rg_csr_get_person_details.fpin,
2745 p_action_information26=> NULL,
2746 p_action_information27=> NULL,
2747 p_action_information28=> NULL,
2748 p_action_information29=> NULL,
2749 p_action_information30=> NULL
2750 );
2751 IF l_s1_tax_base > 0 THEN
2752 pay_action_information_api.create_action_information (
2753 p_action_information_id=> l_action_info_id,
2754 p_action_context_id=> p_assignment_action_id,
2755 p_action_context_type=> 'AAP',
2756 p_object_version_number=> l_ovn,
2757 p_effective_date=> g_effective_date,
2758 p_source_id=> NULL,
2759 p_source_text=> NULL,
2760 p_action_information_category=> 'EMEA REPORT INFORMATION',
2761 p_action_information1=> 'PYFIDPSA',
2762 p_action_information2=>rg_csr_get_person_details.person_id,
2763 p_action_information3=>l_record_id ,
2764 p_action_information4=>'D1',
2765 p_action_information5=>g_transact_type ,
2766 p_action_information6=> g_year,
2767 p_action_information7=> rg_csr_rpt_header.business_id,
2768 p_action_information8=> rg_csr_get_person_details.national_identifier,
2769 p_action_information9=> rg_csr_get_person_details.last_name ,
2770 p_action_information10=> rg_csr_get_person_details.first_name ,
2771 p_action_information11=> rg_csr_per_address.address ,
2772 p_action_information12=> rg_csr_per_address.postal_code ,
2773 p_action_information13=> rg_csr_per_address.d_country ,
2774 p_action_information14=> l_country_code ,
2775 p_action_information15=> FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_s1_tax_base*100,0)),
2776 p_action_information16=> FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_s1_tax*100,0)) ,
2777 p_action_information17=> NULL,
2778 p_action_information18=> NULL,
2779 p_action_information19=> NULL,
2780 p_action_information20=> NULL,
2781 p_action_information21=> rg_csr_rpt_header.contact_person,
2782 p_action_information22=>rg_csr_rpt_header.phone,
2783 p_action_information23=> NULL,
2784 p_action_information24=> l_place_residence,
2785 p_action_information25=> rg_csr_get_person_details.fpin,
2786 p_action_information26=> NULL,
2787 p_action_information27=> NULL,
2788 p_action_information28=> NULL,
2789 p_action_information29=> NULL,
2790 p_action_information30=>NULL
2791 );
2792 END IF;
2793
2794 IF l_s2_tax_base > 0 THEN
2795 pay_action_information_api.create_action_information (
2796 p_action_information_id=> l_action_info_id,
2797 p_action_context_id=> p_assignment_action_id,
2798 p_action_context_type=> 'AAP',
2799 p_object_version_number=> l_ovn,
2800 p_effective_date=> g_effective_date,
2801 p_source_id=> NULL,
2802 p_source_text=> NULL,
2803 p_action_information_category=> 'EMEA REPORT INFORMATION',
2804 p_action_information1=> 'PYFIDPSA',
2805 p_action_information2=>rg_csr_get_person_details.person_id,
2806 p_action_information3=>l_record_id ,
2807 p_action_information4=>'A3',
2808 p_action_information5=>g_transact_type ,
2809 p_action_information6=> g_year,
2810 p_action_information7=> rg_csr_rpt_header.business_id,
2811 p_action_information8=> rg_csr_get_person_details.national_identifier,
2812 p_action_information9=> rg_csr_get_person_details.last_name ,
2813 p_action_information10=> rg_csr_get_person_details.first_name ,
2814 p_action_information11=> rg_csr_per_address.address ,
2815 p_action_information12=> rg_csr_per_address.postal_code ,
2816 p_action_information13=> rg_csr_per_address.d_country ,
2817 p_action_information14=> l_country_code ,
2818 p_action_information15=> FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_s2_tax_base*100,0)),
2819 p_action_information16=> FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_s2_tax*100,0)) ,
2820 p_action_information17=> NULL,
2821 p_action_information18=> NULL,
2822 p_action_information19=> NULL,
2823 p_action_information20=> NULL,
2824 p_action_information21=> rg_csr_rpt_header.contact_person,
2825 p_action_information22=>rg_csr_rpt_header.phone,
2826 p_action_information23=> NULL,
2827 p_action_information24=>l_place_residence,
2828 p_action_information25=> rg_csr_get_person_details.fpin,
2829 p_action_information26=> NULL,
2830 p_action_information27=> NULL,
2831 p_action_information28=> NULL,
2832 p_action_information29=> NULL,
2833 p_action_information30=> NULL
2834 );
2835 END IF;
2836
2837 fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 40');
2838 END IF;
2839
2840 IF l_subsidy_status ='Y' THEN
2841
2842 IF l_record_id='VSPSERIE' THEN
2843 l_tax_type:='80';
2844 ELSE
2845 l_tax_type:='82';
2846 END IF;
2847 l_record_id:='VSPSTUKI';
2848
2849
2850 FOR i IN 1..12
2851 LOOP
2852 SELECT LAST_DAY(TO_DATE('01'||LPAD(i,2,'0')||g_year,'DDMMYYYY')),LPAD(i,2,'0')
2853 INTO l_bal_date,l_month
2854 FROM DUAL;
2855
2856 IF TO_NUMBER(TO_CHAR(l_bal_date,'MM')) <= TO_NUMBER(TO_CHAR(rg_csr_asg_effective_date.EFFECTIVE_END_DATE,'MM')) THEN
2857
2858 CODE_014:= 0;
2859 CODE_015:= 0;
2860
2861 OPEN csr_Get_Defined_Balance_Id( 'PART_TIME_PENSIONER_AMOUNT'||l_dimension2);
2862 FETCH csr_Get_Defined_Balance_Id INTO rg_csr_get_defined_balance_id;
2863 CLOSE csr_Get_Defined_Balance_Id;
2864
2865 l_pt_pension_amt :=pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>rg_csr_get_defined_balance_id.creator_id, P_ASSIGNMENT_ID =>rg_csr_get_person_details.assignment_id , P_VIRTUAL_DATE => l_bal_date) ;
2866
2867 OPEN csr_Get_Defined_Balance_Id( 'SUBSIDY_FOR_LOW_PAID_EMPLOYEES'||l_dimension2);
2868 FETCH csr_Get_Defined_Balance_Id INTO rg_csr_get_defined_balance_id;
2869 CLOSE csr_Get_Defined_Balance_Id;
2870 l_subsidy_amt :=pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>rg_csr_get_defined_balance_id.creator_id, P_ASSIGNMENT_ID =>rg_csr_get_person_details.assignment_id , P_VIRTUAL_DATE => l_bal_date) ;
2871
2872
2873 IF l_subsidy_amt > 0 THEN
2874
2875 OPEN csr_Get_Defined_Balance_Id( 'SUBSIDY_BASIS'||l_dimension2);
2876 FETCH csr_Get_Defined_Balance_Id INTO rg_csr_get_defined_balance_id;
2877 CLOSE csr_Get_Defined_Balance_Id;
2878 l_subsidy_basis :=pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>rg_csr_get_defined_balance_id.creator_id, P_ASSIGNMENT_ID =>rg_csr_get_person_details.assignment_id , P_VIRTUAL_DATE => l_bal_date) ;
2879
2880
2881 IF l_pt_pension_amt > 0 THEN
2882 CODE_015:= l_subsidy_basis;
2883 ELSE
2884 CODE_014:= l_subsidy_basis;
2885 END IF;
2886
2887 pay_action_information_api.create_action_information (
2888 p_action_information_id=> l_action_info_id,
2889 p_action_context_id=> p_assignment_action_id,
2890 p_action_context_type=> 'AAP',
2891 p_object_version_number=> l_ovn,
2892 p_effective_date=> g_effective_date,
2893 p_source_id=> NULL,
2894 p_source_text=> NULL,
2895 p_action_information_category=> 'EMEA REPORT INFORMATION',
2896 p_action_information1=> 'PYFIDPSA',
2897 p_action_information2=>rg_csr_get_person_details.person_id,
2898 p_action_information3=>l_record_id ,
2899 p_action_information4=>l_tax_type,
2900 p_action_information5=>g_transact_type ,
2901 p_action_information6=> g_year,
2902 p_action_information7=> rg_csr_rpt_header.business_id,
2903 p_action_information8=> rg_csr_get_person_details.national_identifier,
2904 p_action_information9=> l_month,
2905 p_action_information10=> FND_NUMBER.NUMBER_TO_CANONICAL(nvl(CODE_014*100 ,0)) ,
2906 p_action_information11=> FND_NUMBER.NUMBER_TO_CANONICAL(nvl(CODE_015 *100,0)) ,
2907 p_action_information12=> FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_subsidy_amt*100,0)) ,
2908 p_action_information13=> '1',
2909 p_action_information14=> NULL ,
2910 p_action_information15=> NULL,
2911 p_action_information16=> NULL ,
2912 p_action_information17=> NULL ,
2913 p_action_information18=> NULL ,
2914 p_action_information19=> NULL ,
2915 p_action_information20=> NULL ,
2916 p_action_information21=> NULL ,
2917 p_action_information22=> NULL ,
2918 p_action_information23=> NULL ,
2919 p_action_information24=> NULL ,
2920 p_action_information25=> NULL ,
2921 p_action_information26=> NULL ,
2922 p_action_information27=> NULL ,
2923 p_action_information28=> NULL ,
2924 p_action_information29=> NULL,
2925 p_action_information30 => NULL
2926 );
2927 END IF;
2928 END IF;
2929 END LOOP;
2930
2931 END IF;
2932
2933 END IF;
2934 END IF;
2935 END IF;---ARCHIVE=YES
2936 IF g_debug THEN
2937 hr_utility.set_location(' Leaving Procedure ARCHIVE_CODE',390);
2938 END IF;
2939
2940 EXCEPTION
2941 WHEN others THEN
2942 IF g_debug THEN
2943 hr_utility.set_location('error raised in archive code ',5);
2944 END if;
2945 RAISE;
2946 END ARCHIVE_CODE;
2947
2948 PROCEDURE DEINITIALIZATION_CODE
2949 (p_payroll_action_id in pay_payroll_actions.payroll_action_id%type) is
2950
2951 /* Cursor to retrieve data from the Header record(Employer level) */
2952 CURSOR csr_prpt_header (p_payroll_action_id NUMBER) IS
2953 SELECT action_information2 emp_type ,action_information3 emp_id
2954 ,action_information4 business_id ,action_information5 org_type ,action_information6 contact_person
2955 ,action_information7 phone ,action_information8 year ,action_information9 transact_type
2956 FROM pay_action_information pai
2957 WHERE action_information_category = 'EMEA REPORT INFORMATION'
2958 AND action_information1 = 'PYFIDPSA'
2959 AND action_context_id = p_payroll_action_id;
2960
2961 rg_csr_prpt_header csr_prpt_header%rowtype;
2962
2963 /* Cursor to fetch data related to Finnish Residents*/
2964 CURSOR csr_arch_fi (p_payroll_action_id NUMBER) IS
2965 SELECT substr(action_information4,1,1) payment_type , SUM(nvl(action_information9,0)) payment , SUM(nvl(action_information10,0)) tax , COUNT(*) num
2966 ,SUM(nvl(action_information12,0)) pretax_ded, SUM(nvl(action_information11,0)) pen_unemp_ins , SUM(nvl(action_information25,0)) bik
2967 , SUM(nvl(action_information27,0) + nvl(action_information23,0)) CODE_670 ,SUM(nvl(action_information28,0)) CODE_631
2968 FROM pay_action_information pai , pay_assignment_actions paa
2969 WHERE pai.action_information_category = 'EMEA REPORT INFORMATION'
2970 AND pai.action_context_type= 'AAP'
2971 AND pai.action_context_id= paa.assignment_action_id
2972 AND paa.payroll_action_id = p_payroll_action_id
2973 AND action_information3 ='VSPSERIE'
2974 AND action_information4 <>'P2'
2975 AND action_information29 IN ('1','3')
2976 GROUP BY substr(action_information4 ,1,1);
2977
2978 /* Cursor to fetch data related to Finnish Residents Payment Type P2*/
2979 CURSOR csr_arch_fi_p2 (p_payroll_action_id NUMBER) IS
2980 SELECT substr(action_information4,1,1) payment_type , SUM(nvl(action_information9,0)) payment , SUM(nvl(action_information10,0)) tax , COUNT(*) num
2981 ,SUM(nvl(action_information12,0)) pretax_ded, SUM(nvl(action_information11,0)) pen_unemp_ins , SUM(nvl(action_information25,0)) bik
2982 , SUM(nvl(action_information27,0) + nvl(action_information23,0)) CODE_670 ,SUM(nvl(action_information28,0)) CODE_631
2983 FROM pay_action_information pai , pay_assignment_actions paa
2984 WHERE pai.action_information_category = 'EMEA REPORT INFORMATION'
2985 AND pai.action_context_type= 'AAP'
2986 AND pai.action_context_id= paa.assignment_action_id
2987 AND paa.payroll_action_id = p_payroll_action_id
2988 AND action_information3 ='VSPSERIE'
2989 AND action_information4 ='P2'
2990 AND action_information29 ='1'
2991 GROUP BY substr(action_information4 ,1,1);
2992
2993 rg_csr_arch_fi_p2 csr_arch_fi_p2%rowtype;
2994
2995
2996
2997 /* Cursor to fetch data related to Foreign Residents Social Security Not Liable*/
2998 CURSOR csr_arch_fr_nss ( p_payroll_action_id NUMBER) IS
2999 SELECT SUM(nvl(action_information15,0)) payment, SUM(nvl(action_information16,0)) tax , COUNT(*) num,
3000 SUM(nvl(action_information19,0)) pretax_ded, SUM(nvl(action_information18,0)) bik
3001 FROM pay_action_information pai , pay_assignment_actions paa
3002 WHERE pai.action_information_category = 'EMEA REPORT INFORMATION'
3003 AND pai.action_context_type= 'AAP'
3004 AND pai.action_context_id= paa.assignment_action_id
3005 AND paa.payroll_action_id = p_payroll_action_id
3006 AND action_information3 ='VSRAERIE'
3007 AND NVL(action_information23,0) < 1
3008 GROUP BY action_information3;
3009
3010
3011 /* Cursor to fetch data related to Foreign Residents Social Security liable*/
3012 CURSOR csr_arch_fr_ss ( p_payroll_action_id NUMBER) IS
3013 SELECT SUM(nvl(action_information15,0)) payment, SUM(nvl(action_information16,0)) tax , COUNT(*) num,
3014 SUM(nvl(action_information19,0)) pretax_ded, SUM(nvl(action_information18,0)) bik
3015 FROM pay_action_information pai , pay_assignment_actions paa
3016 WHERE pai.action_information_category = 'EMEA REPORT INFORMATION'
3017 AND pai.action_context_type= 'AAP'
3018 AND pai.action_context_id= paa.assignment_action_id
3019 AND paa.payroll_action_id = p_payroll_action_id
3020 AND action_information3 ='VSRAERIE'
3021 AND NVL(action_information23,0) > 0
3022 GROUP BY action_information3;
3023
3024 /* Cursor to fetch data related to Record VSPSERIE*/
3025 CURSOR csr_VSPSERIE (p_payroll_action_id NUMBER) IS
3026 SELECT pai.action_information30
3027 FROM pay_action_information pai , pay_assignment_actions paa
3028 WHERE paa.payroll_action_id = p_payroll_action_id
3029 AND pai.action_context_id= paa.assignment_action_id
3030 AND pai.action_context_type= 'AAP'
3031 AND pai.action_information_category = 'EMEA REPORT INFORMATION'
3032 AND pai.action_information1 = 'PYFIDPSA'
3033 AND pai.action_information3 ='VSPSERIE'
3034 AND action_information29 IN ('1','3')
3035 ORDER BY pai.action_information2 , action_information29 , action_information15
3036 FOR UPDATE OF pai.action_information30;
3037
3038 /* Cursor to fetch data related to Record VSRAERIE*/
3039 CURSOR csr_VSRAERIE(p_payroll_action_id NUMBER) IS
3040 SELECT pai.action_information30
3041 FROM pay_action_information pai , pay_assignment_actions paa
3042 WHERE paa.payroll_action_id = p_payroll_action_id
3043 AND pai.action_context_id= paa.assignment_action_id
3044 AND pai.action_context_type= 'AAP'
3045 AND pai.action_information_category = 'EMEA REPORT INFORMATION'
3046 AND pai.action_information1 = 'PYFIDPSA'
3047 AND pai.action_information3 ='VSRAERIE'
3048 ORDER BY pai.action_information2
3049 FOR UPDATE OF pai.action_information30;
3050
3051 /* Cursor to fetch data related to Record VSPSVYSL*/
3052 CURSOR csr_VSPSVYSL(p_payroll_action_id NUMBER) IS
3053 SELECT *
3054 FROM pay_action_information pai
3055 WHERE pai.action_context_id= p_payroll_action_id
3056 AND pai.action_context_type= 'PA'
3057 AND pai.action_information_category = 'EMEA REPORT INFORMATION'
3058 AND pai.action_information1 = 'PYFIDPSA'
3059 AND pai.action_information2 = 'VSPSVYSL'
3060 ORDER BY pai.action_information4
3061 FOR UPDATE OF pai.action_information30;
3062
3063 rg_chk_csr_VSPSVYSL csr_VSPSVYSL%rowtype;
3064
3065 /* Cursor to fetch data related to Record VSPSVYHT*/
3066 CURSOR csr_VSPSVYHT(p_payroll_action_id NUMBER) IS
3067 SELECT *
3068 FROM pay_action_information pai
3069 WHERE pai.action_context_id= p_payroll_action_id
3070 AND pai.action_context_type= 'PA'
3071 AND pai.action_information_category = 'EMEA REPORT INFORMATION'
3072 AND pai.action_information1 = 'PYFIDPSA'
3073 AND pai.action_information2 = 'VSPSVYHT'
3074 FOR UPDATE OF pai.action_information30;
3075
3076 rg_chk_csr_VSPSVYHT csr_VSPSVYHT%rowtype;
3077
3078 /* Cursor to fetch data related to Record VSPSTUKI*/
3079 CURSOR csr_VSPSTUKI(p_payroll_action_id NUMBER) IS
3080 SELECT pai.action_information30
3081 FROM pay_action_information pai , pay_assignment_actions paa
3082 WHERE paa.payroll_action_id = p_payroll_action_id
3083 AND pai.action_context_id= paa.assignment_action_id
3084 AND pai.action_context_type= 'AAP'
3085 AND pai.action_information_category = 'EMEA REPORT INFORMATION'
3086 AND pai.action_information1 = 'PYFIDPSA'
3087 AND pai.action_information3 = 'VSPSTUKI'
3088 ORDER BY pai.action_information2
3089 FOR UPDATE OF pai.action_information30;
3090
3091 rg_chk_csr_VSPSTUKI csr_VSPSTUKI%rowtype;
3092
3093
3094 l_tot_count NUMBER;
3095 l_tot_bik NUMBER;
3096 l_tot_pretax_ded NUMBER;
3097 l_tot_pen_unemp_ins NUMBER;
3098
3099 l_tot_payment NUMBER;
3100 l_payment_status NUMBER;
3101 l_action_info_id NUMBER;
3102 l_ovn NUMBER;
3103 l_end_code NUMBER;
3104 l_vspsvysl_status NUMBER;
3105 l_tot_631 NUMBER;
3106 l_tot_670 NUMBER;
3107
3108
3109
3110 BEGIN
3111 fnd_file.put_line(fnd_file.log,'DEINITIALIZATION_CODE 1');
3112 /*Initializing the variables*/
3113
3114 l_tot_count :=0;
3115 l_tot_bik :=0;
3116 l_tot_pretax_ded :=0;
3117 l_tot_pen_unemp_ins :=0;
3118 l_tot_payment :=0;
3119 l_payment_status :=0;
3120 l_vspsvysl_status :=0;
3121 l_tot_631 :=0;
3122 l_tot_670 :=0;
3123
3124 IF g_debug THEN
3125 hr_utility.set_location(' Entering Procedure DEINITIALIZATION_CODE',380);
3126 END IF;
3127
3128 /* Fetching data from the Header record(Employer level) */
3129 OPEN csr_prpt_header(p_payroll_action_id);
3130 FETCH csr_prpt_header INTO rg_csr_prpt_header;
3131 CLOSE csr_prpt_header;
3132
3133 PAY_FI_ARCHIVE_DPSA.GET_ALL_PARAMETERS(
3134 p_payroll_action_id
3135 ,g_business_group_id
3136 ,g_legal_employer_id
3137 ,g_local_unit_id
3138 ,g_year
3139 ,g_transact_type
3140 ,g_deduction_ss
3141 ,g_effective_date
3142 ,g_archive ) ;
3143
3144 g_emp_type:= rg_csr_prpt_header.emp_type;
3145 IF g_transact_type IN ( 1 , 2) THEN
3146 g_transact_type := 2;
3147 END IF;
3148
3149 IF g_archive='Y' THEN
3150 fnd_file.put_line(fnd_file.log,'DEINITIALIZATION_CODE 3');
3151
3152 FOR rg_csr_arch_fi IN csr_arch_fi(p_payroll_action_id)
3153 LOOP
3154 fnd_file.put_line(fnd_file.log,'DEINITIALIZATION_CODE 4');
3155
3156
3157 IF rg_csr_arch_fi.payment_type ='H' THEN
3158 OPEN csr_arch_fi_p2(p_payroll_action_id);
3159 FETCH csr_arch_fi_p2 INTO rg_csr_arch_fi_p2;
3160 CLOSE csr_arch_fi_p2;
3161
3162 fnd_file.put_line(fnd_file.log,'DEINITIALIZATION_CODE 5');
3163 pay_action_information_api.create_action_information (
3164 p_action_information_id=> l_action_info_id,
3165 p_action_context_id=> p_payroll_action_id,
3166 p_action_context_type=> 'PA',
3167 p_object_version_number=> l_ovn,
3168 p_effective_date=> g_effective_date,
3169 p_source_id=> NULL,
3170 p_source_text=> NULL,
3171 p_action_information_category=> 'EMEA REPORT INFORMATION',
3172 p_action_information1=> 'PYFIDPSA',
3173 p_action_information2=> 'VSPSVYSL',
3174 p_action_information3=>g_transact_type,
3175 p_action_information4=> rg_csr_arch_fi.payment_type ,
3176 p_action_information5=> g_year,
3177 p_action_information6=> rg_csr_prpt_header.business_id,
3178 p_action_information7=> FND_NUMBER.NUMBER_TO_CANONICAL(rg_csr_arch_fi.payment + nvl(rg_csr_arch_fi_p2.payment,0) ),
3179 p_action_information8=> FND_NUMBER.NUMBER_TO_CANONICAL(rg_csr_arch_fi.tax + nvl(rg_csr_arch_fi_p2.tax,0)) ,
3180 p_action_information9=> FND_NUMBER.NUMBER_TO_CANONICAL(rg_csr_arch_fi.num + nvl(rg_csr_arch_fi_p2.num,0)),
3181 p_action_information10=> FND_NUMBER.NUMBER_TO_CANONICAL(rg_csr_arch_fi.CODE_670 + nvl(rg_csr_arch_fi_p2.CODE_670,0) ),
3182 p_action_information11=> FND_NUMBER.NUMBER_TO_CANONICAL(rg_csr_arch_fi.CODE_631 + nvl(rg_csr_arch_fi_p2.CODE_631,0) ),
3183 p_action_information12=> NULL,
3184 p_action_information13=> NULL,
3185 p_action_information14=> NULL,
3186 p_action_information15=> NULL,
3187 p_action_information16=> NULL,
3188 p_action_information17=> NULL,
3189 p_action_information18=> NULL,
3190 p_action_information19=> NULL,
3191 p_action_information20=> NULL,
3192 p_action_information21=> NULL,
3193 p_action_information22=> NULL,
3194 p_action_information23=> NULL,
3195 p_action_information24=> NULL,
3196 p_action_information25=> NULL,
3197 p_action_information26=> NULL,
3198 p_action_information27=> NULL,
3199 p_action_information28=> NULL,
3200 p_action_information29=> NULL,
3201 p_action_information30=> NULL );
3202
3203 l_tot_count := l_tot_count + 1 ;
3204 l_tot_bik := nvl(rg_csr_arch_fi.bik,0) + nvl(rg_csr_arch_fi_p2.bik,0) + l_tot_bik;
3205 l_tot_pretax_ded:= nvl(rg_csr_arch_fi.pretax_ded,0) + nvl(rg_csr_arch_fi_p2.pretax_ded,0) + l_tot_pretax_ded;
3206 l_tot_pen_unemp_ins:= nvl(rg_csr_arch_fi.pen_unemp_ins,0) + nvl(rg_csr_arch_fi_p2.pen_unemp_ins,0) + l_tot_pen_unemp_ins;
3207 l_tot_payment:= nvl(rg_csr_arch_fi.payment,0) + nvl(rg_csr_arch_fi_p2.payment,0) + l_tot_payment;
3208 l_tot_631:= nvl(rg_csr_arch_fi.code_631,0) + nvl(rg_csr_arch_fi_p2.code_631,0) + l_tot_631;
3209 l_tot_670:= nvl(rg_csr_arch_fi.code_670,0) + nvl(rg_csr_arch_fi_p2.code_670,0) + l_tot_670;
3210
3211 fnd_file.put_line(fnd_file.log,'DEINITIALIZATION_CODE 6');
3212 ELSE
3213 pay_action_information_api.create_action_information (
3214 p_action_information_id=> l_action_info_id,
3215 p_action_context_id=> p_payroll_action_id,
3216 p_action_context_type=> 'PA',
3217 p_object_version_number=> l_ovn,
3218 p_effective_date=> g_effective_date,
3219 p_source_id=> NULL,
3220 p_source_text=> NULL,
3221 p_action_information_category=> 'EMEA REPORT INFORMATION',
3222 p_action_information1=> 'PYFIDPSA',
3223 p_action_information2=> 'VSPSVYSL',
3224 p_action_information3=>g_transact_type,
3225 p_action_information4=> rg_csr_arch_fi.payment_type ,
3226 p_action_information5=> g_year,
3227 p_action_information6=> rg_csr_prpt_header.business_id,
3228 p_action_information7=> FND_NUMBER.NUMBER_TO_CANONICAL(nvl(rg_csr_arch_fi.payment ,0) ),
3229 p_action_information8=> FND_NUMBER.NUMBER_TO_CANONICAL(nvl(rg_csr_arch_fi.tax,0) ) ,
3230 p_action_information9=> FND_NUMBER.NUMBER_TO_CANONICAL(nvl(rg_csr_arch_fi.num,0)) ,
3231 p_action_information10=> FND_NUMBER.NUMBER_TO_CANONICAL(nvl(rg_csr_arch_fi.CODE_670,0)) ,
3232 p_action_information11=> FND_NUMBER.NUMBER_TO_CANONICAL(nvl(rg_csr_arch_fi.CODE_631,0)) ,
3233 p_action_information12=> NULL,
3234 p_action_information13=> NULL,
3235 p_action_information14=> NULL,
3236 p_action_information15=> NULL,
3237 p_action_information16=> NULL,
3238 p_action_information17=> NULL,
3239 p_action_information18=> NULL,
3240 p_action_information19=> NULL,
3241 p_action_information20=> NULL,
3242 p_action_information21=> NULL,
3243 p_action_information22=> NULL,
3244 p_action_information23=> NULL,
3245 p_action_information24=> NULL,
3246 p_action_information25=> NULL,
3247 p_action_information26=> NULL,
3248 p_action_information27=> NULL,
3249 p_action_information28=> NULL,
3250 p_action_information29=> NULL,
3251 p_action_information30=> NULL
3252 );
3253
3254 l_tot_count := l_tot_count + 1 ;
3255 l_tot_bik := nvl(rg_csr_arch_fi.bik,0) + l_tot_bik;
3256 l_tot_pretax_ded:= nvl(rg_csr_arch_fi.pretax_ded,0) + l_tot_pretax_ded;
3257 l_tot_pen_unemp_ins:= nvl(rg_csr_arch_fi.pen_unemp_ins,0) + l_tot_pen_unemp_ins;
3258 l_tot_payment:= nvl(rg_csr_arch_fi.payment,0) + l_tot_payment;
3259 l_tot_631:= nvl(rg_csr_arch_fi.code_631,0) + l_tot_631;
3260 l_tot_670:= nvl(rg_csr_arch_fi.code_670,0) + l_tot_670;
3261 fnd_file.put_line(fnd_file.log,'DEINITIALIZATION_CODE 6');
3262
3263 END IF;
3264 END LOOP;
3265 fnd_file.put_line(fnd_file.log,'DEINITIALIZATION_CODE 7');
3266 FOR rg_csr_arch_fr_ss IN csr_arch_fr_ss(p_payroll_action_id)
3267 LOOP
3268 fnd_file.put_line(fnd_file.log,'DEINITIALIZATION_CODE 8');
3269 pay_action_information_api.create_action_information (
3270 p_action_information_id=> l_action_info_id,
3271 p_action_context_id=> p_payroll_action_id,
3272 p_action_context_type=> 'PA',
3273 p_object_version_number=> l_ovn,
3274 p_effective_date=> g_effective_date,
3275 p_source_id=> NULL,
3276 p_source_text=> NULL,
3277 p_action_information_category=> 'EMEA REPORT INFORMATION',
3278 p_action_information1=> 'PYFIDPSA',
3279 p_action_information2=> 'VSPSVYSL',
3280 p_action_information3=>g_transact_type,
3281 p_action_information4=> '14' ,
3282 p_action_information5=> g_year,
3283 p_action_information6=> rg_csr_prpt_header.business_id,
3284 p_action_information7=> FND_NUMBER.NUMBER_TO_CANONICAL(nvl(rg_csr_arch_fr_ss.payment,0)),
3285 p_action_information8=> FND_NUMBER.NUMBER_TO_CANONICAL(nvl(rg_csr_arch_fr_ss.tax,0)) ,
3286 p_action_information9=> FND_NUMBER.NUMBER_TO_CANONICAL(nvl(rg_csr_arch_fr_ss.num,0)),
3287 p_action_information10=> NULL,
3288 p_action_information11=> NULL,
3289 p_action_information12=> NULL,
3290 p_action_information13=> NULL,
3291 p_action_information14=> NULL,
3292 p_action_information15=> NULL,
3293 p_action_information16=> NULL,
3294 p_action_information17=> NULL,
3295 p_action_information18=> NULL,
3296 p_action_information19=> NULL,
3297 p_action_information20=> NULL,
3298 p_action_information21=> NULL,
3299 p_action_information22=> NULL,
3300 p_action_information23=> NULL,
3301 p_action_information24=> NULL,
3302 p_action_information25=> NULL,
3303 p_action_information26=> NULL,
3304 p_action_information27=> NULL,
3305 p_action_information28=> NULL,
3306 p_action_information29=> NULL,
3307 p_action_information30=> NULL
3308 );
3309
3310 l_tot_count := l_tot_count + 1;
3311 l_tot_bik := nvl(rg_csr_arch_fr_ss.bik,0) + l_tot_bik;
3312 l_tot_pretax_ded:= nvl(rg_csr_arch_fr_ss.pretax_ded,0) + l_tot_pretax_ded;
3313 l_tot_payment:= nvl(rg_csr_arch_fr_ss.payment,0) + l_tot_payment;
3314 fnd_file.put_line(fnd_file.log,'DEINITIALIZATION_CODE 9');
3315
3316 END LOOP;
3317 fnd_file.put_line(fnd_file.log,'DEINITIALIZATION_CODE 10');
3318 FOR rg_csr_arch_fr_nss IN csr_arch_fr_nss(p_payroll_action_id)
3319 LOOP
3320 fnd_file.put_line(fnd_file.log,'DEINITIALIZATION_CODE 11');
3321 pay_action_information_api.create_action_information (
3322 p_action_information_id=> l_action_info_id,
3323 p_action_context_id=> p_payroll_action_id,
3324 p_action_context_type=> 'PA',
3325 p_object_version_number=> l_ovn,
3326 p_effective_date=> g_effective_date,
3327 p_source_id=> NULL,
3328 p_source_text=> NULL,
3329 p_action_information_category=> 'EMEA REPORT INFORMATION',
3330 p_action_information1=> 'PYFIDPSA',
3331 p_action_information2=> 'VSPSVYSL',
3332 p_action_information3=>g_transact_type,
3333 p_action_information4=> '22',
3334 p_action_information5=> g_year,
3335 p_action_information6=> rg_csr_prpt_header.business_id,
3336 p_action_information7=> FND_NUMBER.NUMBER_TO_CANONICAL(nvl(rg_csr_arch_fr_nss.payment,0)),
3337 p_action_information8=> FND_NUMBER.NUMBER_TO_CANONICAL(nvl(rg_csr_arch_fr_nss.tax,0)) ,
3338 p_action_information9=> FND_NUMBER.NUMBER_TO_CANONICAL(nvl(rg_csr_arch_fr_nss.num,0)),
3339 p_action_information10=> NULL,
3340 p_action_information11=> NULL,
3341 p_action_information12=> NULL,
3342 p_action_information13=> NULL,
3343 p_action_information14=> NULL,
3344 p_action_information15=> NULL,
3345 p_action_information16=> NULL,
3346 p_action_information17=> NULL,
3347 p_action_information18=> NULL,
3348 p_action_information19=> NULL,
3349 p_action_information20=> NULL,
3350 p_action_information21=> NULL,
3351 p_action_information22=> NULL,
3352 p_action_information23=> NULL,
3353 p_action_information24=> NULL,
3354 p_action_information25=> NULL,
3355 p_action_information26=> NULL,
3356 p_action_information27=> NULL,
3357 p_action_information28=> NULL,
3358 p_action_information29=> NULL,
3359 p_action_information30=> NULL
3360 );
3361
3362 l_tot_count := l_tot_count + 1;
3363 l_tot_bik := nvl(rg_csr_arch_fr_nss.bik,0) + l_tot_bik;
3364 l_tot_pretax_ded:= nvl(rg_csr_arch_fr_nss.pretax_ded,0) + l_tot_pretax_ded;
3365 l_tot_payment:= nvl(rg_csr_arch_fr_nss.payment ,0) + l_tot_payment;
3366 fnd_file.put_line(fnd_file.log,'DEINITIALIZATION_CODE 11');
3367 END LOOP;
3368
3369 IF l_tot_payment = 0 THEN
3370 l_payment_status:=1;
3371 END IF;
3372
3373 OPEN csr_VSPSVYSL(p_payroll_action_id );
3374 FETCH csr_VSPSVYSL INTO rg_chk_csr_VSPSVYSL;
3375 IF csr_VSPSVYSL%FOUND THEN
3376 l_vspsvysl_status:=1;
3377 END IF;
3378 CLOSE csr_VSPSVYSL;
3379
3380 IF l_vspsvysl_status=1 THEN
3381
3382 IF g_deduction_ss IS NOT NULL THEN
3383 l_tot_631:= g_deduction_ss*100;
3384 END IF;
3385
3386 fnd_file.put_line(fnd_file.log,'DEINITIALIZATION_CODE 12');
3387 pay_action_information_api.create_action_information (
3388 p_action_information_id=> l_action_info_id,
3389 p_action_context_id=> p_payroll_action_id,
3390 p_action_context_type=> 'PA',
3391 p_object_version_number=> l_ovn,
3392 p_effective_date=> g_effective_date,
3393 p_source_id=> NULL,
3394 p_source_text=> NULL,
3395 p_action_information_category=> 'EMEA REPORT INFORMATION',
3396 p_action_information1=> 'PYFIDPSA',
3397 p_action_information2=> 'VSPSVYHT',
3398 p_action_information3=>g_transact_type,
3399 p_action_information4=> g_year,
3400 p_action_information5=> rg_csr_prpt_header.business_id,
3401 p_action_information6=> rg_csr_prpt_header.contact_person,
3402 p_action_information7=> rg_csr_prpt_header.phone,
3403 p_action_information8=> FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_tot_count,0)),
3404 p_action_information9=> FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_tot_bik,0)),
3405 p_action_information10=> FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_tot_pen_unemp_ins,0)),
3406 p_action_information11=> FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_tot_pretax_ded,0)),
3407 p_action_information12=> l_payment_status,
3408 p_action_information13=> FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_tot_631,0)),
3409 p_action_information14=> FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_tot_670,0)),
3410 p_action_information15=> NULL,
3411 p_action_information16=> NULL,
3412 p_action_information17=> NULL,
3413 p_action_information18=> NULL,
3414 p_action_information19=> NULL,
3415 p_action_information20=> NULL,
3416 p_action_information21=> NULL,
3417 p_action_information22=> NULL,
3418 p_action_information23=> NULL,
3419 p_action_information24=> NULL,
3420 p_action_information25=> NULL,
3421 p_action_information26=> NULL,
3422 p_action_information27=> NULL,
3423 p_action_information28=> NULL,
3424 p_action_information29=> NULL,
3425 p_action_information30=> NULL
3426 );
3427
3428 fnd_file.put_line(fnd_file.log,'DEINITIALIZATION_CODE 14');
3429 END IF;
3430
3431 l_end_code:=0 ;
3432
3433 FOR rg_csr_VSPSERIE IN csr_VSPSERIE(p_payroll_action_id )
3434 LOOP
3435 l_end_code:=l_end_code + 1 ;
3436 UPDATE pay_action_information pai
3437 SET pai.action_information30 =l_end_code
3438 WHERE CURRENT OF csr_VSPSERIE;
3439 END LOOP;
3440
3441 FOR rg_csr_VSRAERIE IN csr_VSRAERIE(p_payroll_action_id )
3442 LOOP
3443 l_end_code:=l_end_code + 1 ;
3444 UPDATE pay_action_information pai
3445 SET pai.action_information30 =l_end_code
3446 WHERE CURRENT OF csr_VSRAERIE;
3447 END LOOP;
3448
3449 FOR rg_csr_VSPSTUKI IN csr_VSPSTUKI(p_payroll_action_id )
3450 LOOP
3451 l_end_code:=l_end_code + 1 ;
3452 UPDATE pay_action_information
3453 SET action_information30 =l_end_code
3454 WHERE CURRENT OF csr_VSPSTUKI;
3455 END LOOP;
3456
3457 FOR rg_csr_VSPSVYSL IN csr_VSPSVYSL(p_payroll_action_id )
3458 LOOP
3459 l_end_code:=l_end_code + 1 ;
3460 UPDATE pay_action_information
3461 SET action_information30 =l_end_code
3462 WHERE CURRENT OF csr_VSPSVYSL;
3463 END LOOP;
3464
3465 FOR rg_csr_VSPSVYHT IN csr_VSPSVYHT(p_payroll_action_id )
3466 LOOP
3467 l_end_code:=l_end_code + 1 ;
3468 UPDATE pay_action_information
3469 SET action_information30 =l_end_code
3470 WHERE CURRENT OF csr_VSPSVYHT;
3471 END LOOP;
3472
3473
3474
3475
3476
3477 END IF;---ARCHIVE=YES
3478
3479 IF g_debug THEN
3480 hr_utility.set_location(' Leaving Procedure DEINITIALIZATION_CODE',390);
3481 END IF;
3482
3483 EXCEPTION
3484 WHEN others THEN
3485 IF g_debug THEN
3486 hr_utility.set_location('error raised in DEINITIALIZATION_CODE ',5);
3487 END if;
3488 RAISE;
3489 END;
3490
3491 BEGIN
3492
3493 g_payroll_action_id :=NULL;
3494 g_le_assignment_action_id :=NULL;
3495 g_lu_assignment_action_id :=NULL;
3496 g_emp_type :=NULL;
3497 g_business_group_id :=NULL;
3498 g_legal_employer_id :=NULL;
3499 g_local_unit_id :=NULL;
3500 g_year :=NULL;
3501 g_transact_type :=NULL;
3502 g_deduction_ss :=NULL;
3503 g_effective_date :=NULL;
3504 g_archive :=NULL;
3505
3506 END PAY_FI_ARCHIVE_DPSA;