[Home] [Help]
PACKAGE BODY: APPS.PAY_FI_ARCHIVE_DPSA
Source
1 PACKAGE BODY PAY_FI_ARCHIVE_DPSA AS
2 /* $Header: pyfidpsa.pkb 120.8 2010/10/27 14:27:05 vijranga ship $ */
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 IN ('C','S') -- 10229501
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 IN ('C','S') -- 10229501
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 IN ('C','S') -- 10229501
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 IN ('C','S') -- 10229501
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 /*Cursor TO retrive Result Values FOR Elements Attached */ -- Changes 2009
891 -- Start
892 Cursor csr_result_value ( p_assignment_id IN NUMBER,
893 p_element_name IN VARCHAR2,
894 p_input_val_name IN VARCHAR2) IS
895 SELECT prrv.result_value FROM
896 pay_assignment_actions paa,
897 pay_run_results prr,
898 pay_element_types_f petf,
899 pay_input_values_f pivf,
900 pay_run_result_values prrv
901 WHERE paa.assignment_id = p_assignment_id
902 AND paa.assignment_action_id = prr.assignment_action_id
903 AND prr.element_type_id = petf.element_type_id
904 AND petf.element_name = p_element_name
905 AND petf.legislation_code = 'FI'
906 AND pivf.element_type_id = petf.element_type_id
907 AND pivf.name = p_input_val_name
908 AND prrv.input_value_id = pivf.input_value_id
909 AND prrv.run_result_id = prr.run_result_id
910 AND paa.action_sequence = (SELECT MAX(paa.action_sequence) FROM
911 pay_assignment_actions paa,
912 pay_run_results prr,
913 pay_element_types_f
914 WHERE paa.assignment_id = p_assignment_id
915 AND paa.assignment_action_id = prr.assignment_action_id
916 AND prr.element_type_id = petf.element_type_id
917 AND petf.element_name = p_element_name
918 AND petf.legislation_code = 'FI');
919
920 -- Cursor to find the Bank Details od the Person who is paid.
921
922 CURSOR csr_bank_details(p_assignment_id IN NUMBER,
923 p_business_group_id IN NUMBER,
924 p_report_date IN DATE) IS
925 SELECT pea.segment1 Bank_Name,
926 pea.segment2 Bank_Branch,
927 pea.segment3 Account_Number
928 FROM pay_personal_payment_methods_f ppmf,
929 pay_external_accounts pea
930 WHERE ppmf.assignment_id = p_assignment_id
931 AND pea.external_account_id = ppmf.external_account_id
932 AND ppmf.business_group_id = p_business_group_id
933 AND p_report_date
934 BETWEEN ppmf.effective_start_date
935 AND ppmf.effective_end_date;
936
937 rg_bank_details csr_bank_details%rowtype;
938
939 -- End 2009
940
941
942 l_assignment_action_id NUMBER;
943 l_action_context_id NUMBER;
944 l_flag NUMBER := 0;
945 l_action_info_id NUMBER;
946 l_ovn NUMBER;
947 l_tax_card_type VARCHAR2(5);
948 l_payment_type VARCHAR2(5);
949 l_source_text VARCHAR2(10);
950 l_source_text2 VARCHAR2(10);
951 l_org_type VARCHAR2(5);
952 l_country_code varchar2(50);
953 -- l_age_category varchar2(1); defined below
954
955 l_wtax_base NUMBER ;
956 l_tstax_base NUMBER;
957 l_tax_base NUMBER;
958 l_notional_base NUMBER;
959 l_person_type VARCHAR2(3);
960 l_record_id VARCHAR2(10);
961 l_dimension VARCHAR2(100);
962 l_dimension1 VARCHAR2(100);
963 l_dimension2 VARCHAR2(100);
964 l_start_date DATE ;
965 l_end_date DATE ;
966 l_effective_date DATE ;
967 l_ptp_1_wtax_base NUMBER;
968 l_ptp_2_wtax_base NUMBER;
969 l_pt1_1_wtax_base NUMBER;
970 l_pt1_2_wtax_base NUMBER;
971 l_ptp2_1_wtax_base NUMBER;
972 l_ptp2_2_wtax_base NUMBER;
973 l_pth_1_wtax_base NUMBER;
974 l_pth_2_wtax_base NUMBER;
975 l_pth2_1_wtax_base NUMBER;
976 l_pth2_2_wtax_base NUMBER;
977 l_ptg1_base NUMBER;
978 l_ptg_base NUMBER;
979 l_pth4_base NUMBER;
980 l_mtax_base NUMBER;
981 l_mtax NUMBER;
982 l_tax NUMBER;
983 l_empl_unemp_ins NUMBER;
984 l_pretax_ded NUMBER;
985 l_cum_car_benefit NUMBER;
986 l_cum_mileage NUMBER;
987 l_bik NUMBER;
988 l_tot_mortgage_bik NUMBER;
989 l_mortgage_bik NUMBER;
990 l_mortgage_bik_status NUMBER;
991 l_other_bik NUMBER;
992 l_tot_other_bik NUMBER;
993 l_other_bik_status NUMBER;
994 l_tot_housing_bik NUMBER;
995 l_housing_bik NUMBER;
996 l_housing_bik_status NUMBER;
997 l_tot_phone_bik NUMBER;
998 l_phone_bik NUMBER;
999 l_phone_bik_status NUMBER;
1000 l_lunch_bik NUMBER;
1001 l_lunch_bik_status NUMBER;
1002 l_external_expenses NUMBER;
1003 l_daily_allowance_d_expenses NUMBER;
1004 l_daily_allowance_d_status NUMBER;
1005 l_half_day_allowance_expenses NUMBER;
1006 l_half_day_allowance_status NUMBER;
1007 l_daily_allowance_fe_expenses NUMBER;
1008 l_daily_allowance_fe_status NUMBER;
1009 l_meal_comp_expenses NUMBER;
1010 l_meal_comp_status NUMBER;
1011 l_tax_free_mileage NUMBER;
1012 l_tax_free_mileage_expenses NUMBER;
1013 l_stock_option_bik NUMBER;
1014 l_tot_stock_option_bik NUMBER;
1015 l_emp_pension NUMBER;
1016 l_travel_ticket_bik NUMBER;
1017 l_tot_travel_ticket_bik NUMBER;
1018 l_travel_ticket_bik_status NUMBER;
1019 l_pta1_1_tstax_base NUMBER;
1020 l_pta1_2_tstax_base NUMBER;
1021 l_pta2_1_tstax_base NUMBER;
1022 l_pta2_2_tstax_base NUMBER;
1023 l_pta4_1_tstax_base NUMBER;
1024 l_pta5_1_tstax_base NUMBER;
1025 l_pta6_1_tstax_base NUMBER;
1026 l_pta7_1_tstax_base NUMBER;
1027 l_salary_income NUMBER;
1028 l_social_security NUMBER;
1029 l_count NUMBER;
1030 l_empl_pension NUMBER;
1031 l_full_car_benefit_status NUMBER;
1032 l_bik_use_car_status NUMBER;
1033 l_pt5_1_wtax_base NUMBER;
1034 l_pt5_2_wtax_base NUMBER;
1035 l_tax_status VARCHAR2(1);
1036 l_place_residence VARCHAR2(60);
1037 l_ptg1_tax NUMBER;
1038 l_s1_tax_base NUMBER;
1039 l_s1_tax NUMBER;
1040 l_s2_tax_base NUMBER;
1041 l_s2_tax NUMBER;
1042 l_s3_tax_base NUMBER;
1043 l_s3_tax NUMBER;
1044 l_lunch_bik_ded_status NUMBER;
1045 l_tot_lunch_bik_ded NUMBER;
1046 l_lunch_bik_ded NUMBER;
1047 l_tot_car_bik_ded NUMBER;
1048 l_car_bik_ded NUMBER;
1049 l_tot_other_bik_ded NUMBER;
1050 l_other_bik_ded NUMBER;
1051 l_tax_comp NUMBER;
1052 l_tot_pay_eoff NUMBER;
1053 l_pay_eoff NUMBER;
1054 l_tot_vol_pi NUMBER;
1055 l_vol_pi NUMBER;
1056 l_tot_total_vol_pi NUMBER;
1057 l_total_vol_pi NUMBER;
1058 l_te_exem_ss NUMBER;
1059 l_bik_exem_ss NUMBER;
1060 l_sm_exem_ss NUMBER;
1061 l_tot_exem_ss NUMBER;
1062 l_ss_ded NUMBER;
1063 l_631 NUMBER;
1064 l_exem_ss NUMBER;
1065 l_tot_ss_ded NUMBER;
1066 l_bal_date DATE;
1067 l_subsidy NUMBER;
1068 l_subsidy_status VARCHAR2(1) DEFAULT 'N' ;
1069 l_tax_type VARCHAR2(2);
1070 l_pt_pension_amt NUMBER;
1071 l_subsidy_amt NUMBER;
1072 l_subsidy_basis NUMBER;
1073 CODE_014 NUMBER;
1074 CODE_015 NUMBER;
1075 l_wtax NUMBER;
1076 l_te NUMBER;
1077 l_te_ss NUMBER;
1078 l_bik_ss NUMBER;
1079 l_sm_ss NUMBER;
1080 l_month VARCHAR2(2);
1081
1082 -- Added for 2009 changes
1083 l_car_ben_val NUMBER := 0;
1084 l_full_car_ben VARCHAR2(2) := 'N';
1085 l_mobilization_year NUMBER := 0;
1086 l_car_abroad VARCHAR(2) := 'N';
1087 l_age_category VARCHAR2(2) ;
1088 l_bank_acc_num VARCHAR(20) ;
1089
1090
1091
1092 BEGIN
1093 -- fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 1');
1094 /*Initializing all balance variables*/
1095
1096 l_count :=0;
1097 l_wtax_base :=0;
1098 l_tstax_base :=0;
1099 l_tax_base :=0;
1100 l_notional_base :=0;
1101 l_ptp_1_wtax_base :=0;
1102 l_ptp_2_wtax_base :=0;
1103 l_pt1_1_wtax_base :=0;
1104 l_pt1_2_wtax_base :=0;
1105 l_ptp2_2_wtax_base :=0;
1106 l_ptp2_2_wtax_base :=0;
1107 l_pth_1_wtax_base :=0;
1108 l_pth_2_wtax_base :=0;
1109 l_pth2_1_wtax_base :=0;
1110 l_pth2_2_wtax_base :=0;
1111 l_ptg1_base :=0;
1112 l_ptg_base :=0;
1113 l_pth4_base :=0;
1114 l_mtax_base :=0;
1115 l_mtax :=0;
1116 l_tax :=0;
1117 l_empl_pension :=0;
1118 l_empl_unemp_ins :=0;
1119 l_pretax_ded :=0;
1120 l_cum_car_benefit :=0;
1121 l_cum_mileage :=0;
1122 l_bik :=0;
1123 l_tot_mortgage_bik :=0;
1124 l_mortgage_bik :=0;
1125 l_mortgage_bik_status :=0;
1126 l_other_bik :=0;
1127 l_tot_other_bik :=0;
1128 l_other_bik_status :=0;
1129 l_tot_housing_bik :=0;
1130 l_housing_bik :=0;
1131 l_housing_bik_status :=0;
1132 l_tot_phone_bik :=0;
1133 l_phone_bik :=0;
1134 l_phone_bik_status :=0;
1135 l_lunch_bik :=0;
1136 l_lunch_bik_status :=0;
1137 l_external_expenses :=0;
1138 l_daily_allowance_d_expenses :=0;
1139 l_daily_allowance_d_status :=0;
1140 l_half_day_allowance_expenses :=0;
1141 l_half_day_allowance_status :=0;
1142 l_daily_allowance_fe_expenses :=0;
1143 l_daily_allowance_fe_status :=0;
1144 l_meal_comp_expenses :=0;
1145 l_meal_comp_status :=0;
1146 l_tax_free_mileage :=0;
1147 l_tax_free_mileage_expenses :=0;
1148 l_stock_option_bik :=0;
1149 l_tot_stock_option_bik :=0;
1150 l_emp_pension :=0;
1151 l_travel_ticket_bik :=0;
1152 l_tot_travel_ticket_bik :=0;
1153 l_travel_ticket_bik_status :=0;
1154 l_pta1_1_tstax_base :=0;
1155 l_pta1_2_tstax_base :=0;
1156 l_pta2_1_tstax_base :=0;
1157 l_pta2_2_tstax_base :=0;
1158 l_pta4_1_tstax_base :=0;
1159 l_pta5_1_tstax_base :=0;
1160 l_pta6_1_tstax_base :=0;
1161 l_pta7_1_tstax_base :=0;
1162 l_salary_income :=0;
1163 l_social_security :=0;
1164 l_pt5_1_wtax_base :=0;
1165 l_pt5_2_wtax_base :=0;
1166 l_ptg1_tax := 0;
1167 l_s1_tax_base := 0;
1168 l_s1_tax := 0 ;
1169 l_s2_tax_base := 0;
1170 l_s2_tax := 0 ;
1171 l_s3_tax_base := 0;
1172 l_s3_tax := 0 ;
1173 l_lunch_bik_ded_status := 0 ;
1174 l_tot_lunch_bik_ded := 0 ;
1175 l_lunch_bik_ded := 0 ;
1176 l_tot_car_bik_ded := 0 ;
1177 l_car_bik_ded := 0 ;
1178 l_tot_other_bik_ded := 0 ;
1179 l_other_bik_ded := 0 ;
1180 l_tax_comp := 0 ;
1181 l_tot_pay_eoff := 0 ;
1182 l_pay_eoff := 0 ;
1183 l_tot_vol_pi := 0 ;
1184 l_vol_pi := 0 ;
1185 l_total_vol_pi := 0 ;
1186 l_tot_total_vol_pi := 0 ;
1187 l_te_exem_ss := 0 ;
1188 l_bik_exem_ss := 0 ;
1189 l_sm_exem_ss := 0 ;
1190 l_tot_exem_ss := 0 ;
1191 l_ss_ded := 0 ;
1192 l_631 := 0 ;
1193 l_exem_ss := 0 ;
1194 l_tot_ss_ded := 0 ;
1195 l_subsidy := 0 ;
1196 l_pt_pension_amt := 0 ;
1197 l_subsidy_amt := 0 ;
1198 l_subsidy_basis := 0 ;
1199 CODE_014 := 0 ;
1200 CODE_015 := 0 ;
1201 l_wtax := 0 ;
1202 l_te := 0 ;
1203 l_te_ss := 0 ;
1204 l_bik_ss := 0 ;
1205 l_sm_ss := 0 ;
1206 l_month := 0 ;
1207
1208
1209 IF g_debug THEN
1210 hr_utility.set_location(' Entering Procedure ARCHIVE_CODE',380);
1211 END IF;
1212
1213 /* Fetching data from the Header record(Employer level) */
1214 OPEN csr_rpt_header(p_assignment_action_id);
1215 FETCH csr_rpt_header INTO rg_csr_rpt_header;
1216 CLOSE csr_rpt_header;
1217
1218 /* Fetching report parameters */
1219 PAY_FI_ARCHIVE_DPSA.GET_ALL_PARAMETERS
1220 ( rg_csr_rpt_header.payroll_action_id
1221 ,g_business_group_id
1222 ,g_legal_employer_id
1223 ,g_local_unit_id
1224 ,g_year
1225 ,g_transact_type
1226 ,g_deduction_ss
1227 ,g_effective_date
1228 ,g_archive ) ;
1229
1230 -- fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 2');
1231
1232 IF g_archive='Y' THEN
1233
1234 l_start_date := TO_DATE('01'||g_year,'MMYYYY');
1235 l_end_date := LAST_DAY(TO_DATE('12'||g_year,'MMYYYY'));
1236 l_effective_date := l_end_date ;
1237 -- fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 3');
1238
1239 /* Fetching Person Details */
1240 OPEN csr_asg_effective_date(p_assignment_action_id , l_start_date , l_end_date , g_business_group_id );
1241 FETCH csr_asg_effective_date INTO rg_csr_asg_effective_date;
1242 CLOSE csr_asg_effective_date;
1243
1244 /* Fetching Person Details */
1245 OPEN csr_get_person_details(p_assignment_action_id , rg_csr_asg_effective_date.EFFECTIVE_END_DATE );
1246 FETCH csr_get_person_details INTO rg_csr_get_person_details;
1247 CLOSE csr_get_person_details;
1248
1249 /* Setting Context */
1250 BEGIN
1251 pay_balance_pkg.set_context('ASSIGNMENT_ACTION_ID',p_assignment_action_id);
1252 pay_balance_pkg.set_context('TAX_UNIT_ID',g_legal_employer_id);
1253 IF rg_csr_rpt_header.emp_type = 'LU' THEN
1254 pay_balance_pkg.set_context('LOCAL_UNIT_ID',g_local_unit_id);
1255 END IF;
1256
1257 END;
1258
1259 -- fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 4'||rg_csr_get_person_details.person_id);
1260 l_assignment_action_id:=p_assignment_action_id;
1261
1262 IF rg_csr_rpt_header.emp_type = 'LU' THEN
1263 l_dimension:='_PER_LU_EMPLTYPE_TC_YTD';
1264 l_dimension1:='_PER_LU_YTD';
1265 l_dimension2:='_PER_LU_MONTH';
1266 ELSE
1267 l_dimension:='_PER_LE_EMPLTYPE_TC_YTD';
1268 l_dimension1:='_PER_LE_YTD';
1269 l_dimension2:='_PER_LE_MONTH';
1270 END IF ;
1271
1272 -- fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 5');
1273 OPEN csr_Get_Defined_Balance_Id( 'WITHHOLDING_TAX_BASE'||l_dimension1);
1274 FETCH csr_Get_Defined_Balance_Id INTO rg_csr_get_defined_balance_id;
1275 CLOSE csr_Get_Defined_Balance_Id;
1276 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 ) ;
1277
1278 OPEN csr_Get_Defined_Balance_Id( 'TAX_AT_SOURCE_BASE'||l_dimension1);
1279 FETCH csr_Get_Defined_Balance_Id INTO rg_csr_get_defined_balance_id;
1280 CLOSE csr_Get_Defined_Balance_Id;
1281 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 ) ;
1282
1283 OPEN csr_Get_Defined_Balance_Id( 'NOTIONAL_SALARY'||l_dimension1);
1284 FETCH csr_Get_Defined_Balance_Id INTO rg_csr_get_defined_balance_id;
1285 CLOSE csr_Get_Defined_Balance_Id;
1286 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 ) ;
1287
1288 -- fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 6');
1289
1290
1291 /*Determining Resident Type*/
1292 IF l_wtax_base > 0 THEN
1293 -- Finnish Resident
1294 l_person_type:= 'FI';
1295 l_record_id := 'VSPSERIE';
1296 ELSIF l_tstax_base > 0 THEN
1297 -- Foreign Resident
1298 l_person_type:= 'FR';
1299 l_record_id := 'VSRAERIE';
1300 ELSIF l_notional_base > 0 THEN
1301 -- Finnish Resident Working Abroad
1302 l_person_type:= 'WO';
1303 l_record_id := 'VSPSERIE';
1304 END IF;
1305
1306 -- fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 7');
1307
1308 -- Start Changes 2009
1309 /*Added for Codes 123,124 - VSPSERIE (For Finnish Residents Only) */
1310 -- Code 123 - Car Benefit , Age category
1311
1312 IF l_person_type = 'FI' THEN
1313 -- Code 123
1314 -- Allowed values (note that values will not change, only the rule
1315 -- for calculating the taxable value):
1316 -- A: (Mobilization year: 2008, 2007 or 2006)
1317 -- B: (Mobilization year: 2005, 2004 or 2003)
1318 -- C: Mobilization year 2002 or before)
1319 -- U: Car benefit used abroad
1320
1321 OPEN csr_result_value(rg_csr_get_person_details.assignment_id,'Car Benefit','Mobilization Year');
1322 FETCH csr_result_value INTO l_mobilization_year;
1323 CLOSE csr_result_value;
1324
1325 OPEN csr_result_value(rg_csr_get_person_details.assignment_id,'Car Benefit','Car Abroad');
1326 FETCH csr_result_value INTO l_car_abroad;
1327 CLOSE csr_result_value;
1328
1329 IF l_car_abroad = 'Y' THEN
1330 l_age_category := 'U';
1331 ELSE
1332 IF l_mobilization_year IN (2008,2007,2006) THEN
1333 l_age_category := 'A';
1334 END IF;
1335
1336 IF l_mobilization_year IN (2005,2004,2003) THEN
1337 l_age_category := 'B';
1338 END IF;
1339
1340 IF l_mobilization_year <= 2002 AND l_mobilization_year > 0 THEN
1341 l_age_category := 'C';
1342 END IF;
1343
1344
1345 END IF;
1346
1347 -- Code 124 Full Car Benefit
1348 -- Allowed Values
1349 -- 0 = No
1350 -- 1 = Yes
1351
1352 OPEN csr_result_value(rg_csr_get_person_details.assignment_id,'Car Benefit','Full Benefit');
1353 FETCH csr_result_value INTO l_full_car_ben;
1354 CLOSE csr_result_value;
1355
1356 IF l_full_car_ben = 'Y' THEN
1357 l_car_ben_val := 1;
1358 ELSE
1359 l_car_ben_val := 0;
1360 END IF;
1361
1362 END IF;
1363
1364 -- Code-325 VSRAERIE - For Foreign Residents.
1365 IF l_person_type = 'FR' THEN
1366 OPEN csr_bank_details (rg_csr_get_person_details.assignment_id,g_business_group_id,p_effective_date);
1367 FETCH csr_bank_details INTO rg_bank_details;
1368 CLOSE csr_bank_details;
1369
1370 l_bank_acc_num := rg_bank_details.Account_Number;
1371 END IF;
1372
1373
1374
1375 -- End changes 2009
1376
1377 OPEN csr_get_tax_card_type(rg_csr_get_person_details.assignment_id ,l_start_date, l_end_date );
1378 FETCH csr_get_tax_card_type INTO l_tax_card_type;
1379 CLOSE csr_get_tax_card_type;
1380
1381 -- fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 8');
1382 IF l_tax_card_type IS NOT NULL AND l_person_type IS NOT NULL THEN
1383 -- fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 9');
1384
1385 OPEN csr_Get_Defined_Balance_Id( 'BENEFITS_IN_KIND'||l_dimension1);
1386 FETCH csr_Get_Defined_Balance_Id INTO rg_csr_get_defined_balance_id;
1387 CLOSE csr_Get_Defined_Balance_Id;
1388
1389 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 ) ;
1390
1391
1392 OPEN csr_Get_Defined_Balance_Id( 'SALARY_INCOME'||l_dimension1);
1393 FETCH csr_Get_Defined_Balance_Id INTO rg_csr_get_defined_balance_id;
1394 CLOSE csr_Get_Defined_Balance_Id;
1395
1396 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 ) ;
1397
1398 /*Low Paid Employees*/
1399 OPEN csr_Get_Defined_Balance_Id( 'SUBSIDY_FOR_LOW_PAID_EMPLOYEES'||l_dimension1);
1400 FETCH csr_Get_Defined_Balance_Id INTO rg_csr_get_defined_balance_id;
1401 CLOSE csr_Get_Defined_Balance_Id;
1402 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 ) ;
1403
1404 IF l_subsidy > 0 THEN
1405 l_subsidy_status:='Y' ;
1406 END IF;
1407
1408 -- fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 10');
1409
1410 IF l_person_type= 'FI' THEN
1411
1412 /*Determining Payment Type*/
1413 OPEN csr_Get_Defined_Balance_Id( 'WITHHOLDING_TAX_BASE'||l_dimension);
1414 FETCH csr_Get_Defined_Balance_Id INTO rg_csr_get_defined_balance_id;
1415 CLOSE csr_Get_Defined_Balance_Id;
1416
1417 -- fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 11');
1418
1419 IF l_tax_card_type in ('P', 'C' ) THEN
1420
1421 -- fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 12');
1422 l_ptp_1_wtax_base :=pay_balance_pkg.get_value
1423 (P_DEFINED_BALANCE_ID =>rg_csr_get_defined_balance_id.creator_id
1424 ,P_ASSIGNMENT_ACTION_ID =>l_assignment_action_id
1425 ,P_TAX_UNIT_ID =>g_legal_employer_id
1426 ,P_JURISDICTION_CODE =>NULL
1427 ,P_SOURCE_ID =>NULL
1428 ,P_SOURCE_TEXT =>'PEMP'
1429 ,P_TAX_GROUP =>NULL
1430 ,P_DATE_EARNED =>l_effective_date
1431 ,P_GET_RR_ROUTE =>NULL
1432 ,P_GET_RB_ROUTE =>NULL
1433 ,P_SOURCE_TEXT2 =>'N'
1434 ,P_SOURCE_NUMBER =>NULL
1435 ,P_TIME_DEF_ID =>NULL
1436 ,P_BALANCE_DATE =>NULL
1437 ,P_PAYROLL_ID =>NULL
1438 ,P_ORIGINAL_ENTRY_ID =>NULL
1439 ,P_LOCAL_UNIT_ID =>g_local_unit_id
1440 ,P_SOURCE_NUMBER2 =>NULL
1441 ,P_ORGANIZATION_ID =>NULL
1442 ) ;
1443
1444 l_ptp_2_wtax_base :=pay_balance_pkg.get_value
1445 (P_DEFINED_BALANCE_ID =>rg_csr_get_defined_balance_id.creator_id
1446 ,P_ASSIGNMENT_ACTION_ID =>l_assignment_action_id
1447 ,P_TAX_UNIT_ID =>g_legal_employer_id
1448 ,P_JURISDICTION_CODE =>NULL
1449 ,P_SOURCE_ID =>NULL
1450 ,P_SOURCE_TEXT =>'PEMP'
1451 ,P_TAX_GROUP =>NULL
1452 ,P_DATE_EARNED =>l_effective_date
1453 ,P_GET_RR_ROUTE =>NULL
1454 ,P_GET_RB_ROUTE =>NULL
1455 ,P_SOURCE_TEXT2 =>'S'
1456 ,P_SOURCE_NUMBER =>NULL
1457 ,P_TIME_DEF_ID =>NULL
1458 ,P_BALANCE_DATE =>NULL
1459 ,P_PAYROLL_ID =>NULL
1460 ,P_ORIGINAL_ENTRY_ID =>NULL
1461 ,P_LOCAL_UNIT_ID =>g_local_unit_id
1462 ,P_SOURCE_NUMBER2 =>NULL
1463 ,P_ORGANIZATION_ID =>NULL
1464 ) ;
1465
1466 -- fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 13');
1467 END IF;
1468
1469 IF l_tax_card_type in ('EI', 'FT','S' ) AND (l_ptp_1_wtax_base + l_ptp_2_wtax_base) <=0 THEN
1470 -- fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 14');
1471 l_pt1_1_wtax_base :=pay_balance_pkg.get_value
1472 (P_DEFINED_BALANCE_ID =>rg_csr_get_defined_balance_id.creator_id
1473 ,P_ASSIGNMENT_ACTION_ID =>l_assignment_action_id
1474 ,P_TAX_UNIT_ID =>g_legal_employer_id
1475 ,P_JURISDICTION_CODE =>NULL
1476 ,P_SOURCE_ID =>NULL
1477 ,P_SOURCE_TEXT =>'SEMP'
1478 ,P_TAX_GROUP =>NULL
1479 ,P_DATE_EARNED =>l_effective_date
1480 ,P_GET_RR_ROUTE =>NULL
1481 ,P_GET_RB_ROUTE =>NULL
1482 ,P_SOURCE_TEXT2 =>'N'
1483 ,P_SOURCE_NUMBER =>NULL
1484 ,P_TIME_DEF_ID =>NULL
1485 ,P_BALANCE_DATE =>NULL
1486 ,P_PAYROLL_ID =>NULL
1487 ,P_ORIGINAL_ENTRY_ID =>NULL
1488 ,P_LOCAL_UNIT_ID =>g_local_unit_id
1489 ,P_SOURCE_NUMBER2 =>NULL
1490 ,P_ORGANIZATION_ID =>NULL
1491 ) ;
1492
1493 l_pt1_2_wtax_base :=pay_balance_pkg.get_value
1494 (P_DEFINED_BALANCE_ID =>rg_csr_get_defined_balance_id.creator_id
1495 ,P_ASSIGNMENT_ACTION_ID =>l_assignment_action_id
1496 ,P_TAX_UNIT_ID =>g_legal_employer_id
1497 ,P_JURISDICTION_CODE =>NULL
1498 ,P_SOURCE_ID =>NULL
1499 ,P_SOURCE_TEXT =>'SEMP'
1500 ,P_TAX_GROUP =>NULL
1501 ,P_DATE_EARNED =>l_effective_date
1502 ,P_GET_RR_ROUTE =>NULL
1503 ,P_GET_RB_ROUTE =>NULL
1504 ,P_SOURCE_TEXT2 =>'S'
1505 ,P_SOURCE_NUMBER =>NULL
1506 ,P_TIME_DEF_ID =>NULL
1507 ,P_BALANCE_DATE =>NULL
1508 ,P_PAYROLL_ID =>NULL
1509 ,P_ORIGINAL_ENTRY_ID =>NULL
1510 ,P_LOCAL_UNIT_ID =>g_local_unit_id
1511 ,P_SOURCE_NUMBER2 =>NULL
1512 ,P_ORGANIZATION_ID =>NULL
1513 ) ;
1514 -- fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 15');
1515 END IF;
1516
1517 IF l_tax_card_type in ('C','FT','EI','P', 'S' )
1518 AND (l_ptp_1_wtax_base + l_ptp_2_wtax_base + l_pt1_1_wtax_base + l_pt1_2_wtax_base ) <=0 THEN
1519 -- fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 16');
1520 l_ptp2_1_wtax_base :=pay_balance_pkg.get_value
1521 (P_DEFINED_BALANCE_ID =>rg_csr_get_defined_balance_id.creator_id
1522 ,P_ASSIGNMENT_ACTION_ID =>l_assignment_action_id
1523 ,P_TAX_UNIT_ID =>g_legal_employer_id
1524 ,P_JURISDICTION_CODE =>NULL
1525 ,P_SOURCE_ID =>NULL
1526 ,P_SOURCE_TEXT =>'PEMP'
1527 ,P_TAX_GROUP =>NULL
1528 ,P_DATE_EARNED =>l_effective_date
1529 ,P_GET_RR_ROUTE =>NULL
1530 ,P_GET_RB_ROUTE =>NULL
1531 ,P_SOURCE_TEXT2 =>'A'
1532 ,P_SOURCE_NUMBER =>NULL
1533 ,P_TIME_DEF_ID =>NULL
1534 ,P_BALANCE_DATE =>NULL
1535 ,P_PAYROLL_ID =>NULL
1536 ,P_ORIGINAL_ENTRY_ID =>NULL
1537 ,P_LOCAL_UNIT_ID =>g_local_unit_id
1538 ,P_SOURCE_NUMBER2 =>NULL
1539 ,P_ORGANIZATION_ID =>NULL
1540 ) ;
1541
1542 l_ptp2_2_wtax_base :=pay_balance_pkg.get_value
1543 (P_DEFINED_BALANCE_ID =>rg_csr_get_defined_balance_id.creator_id
1544 ,P_ASSIGNMENT_ACTION_ID =>l_assignment_action_id
1545 ,P_TAX_UNIT_ID =>g_legal_employer_id
1546 ,P_JURISDICTION_CODE =>NULL
1547 ,P_SOURCE_ID =>NULL
1548 ,P_SOURCE_TEXT =>'SEMP'
1549 ,P_TAX_GROUP =>NULL
1550 ,P_DATE_EARNED =>l_effective_date
1551 ,P_GET_RR_ROUTE =>NULL
1552 ,P_GET_RB_ROUTE =>NULL
1553 ,P_SOURCE_TEXT2 =>'A'
1554 ,P_SOURCE_NUMBER =>NULL
1555 ,P_TIME_DEF_ID =>NULL
1556 ,P_BALANCE_DATE =>NULL
1557 ,P_PAYROLL_ID =>NULL
1558 ,P_ORIGINAL_ENTRY_ID =>NULL
1559 ,P_LOCAL_UNIT_ID =>g_local_unit_id
1560 ,P_SOURCE_NUMBER2 =>NULL
1561 ,P_ORGANIZATION_ID =>NULL
1562 ) ;
1563 -- fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 17');
1564
1565 END IF;
1566
1567
1568 IF l_tax_card_type in ('EI', 'FT','S' )
1569 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
1570 -- fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 18');
1571 l_pth_1_wtax_base :=pay_balance_pkg.get_value
1572 (P_DEFINED_BALANCE_ID =>rg_csr_get_defined_balance_id.creator_id
1573 ,P_ASSIGNMENT_ACTION_ID =>l_assignment_action_id
1574 ,P_TAX_UNIT_ID =>g_legal_employer_id
1575 ,P_JURISDICTION_CODE =>NULL
1576 ,P_SOURCE_ID =>NULL
1577 ,P_SOURCE_TEXT =>'PUNEMP'
1578 ,P_TAX_GROUP =>NULL
1579 ,P_DATE_EARNED =>l_effective_date
1580 ,P_GET_RR_ROUTE =>NULL
1581 ,P_GET_RB_ROUTE =>NULL
1582 ,P_SOURCE_TEXT2 =>'N'
1583 ,P_SOURCE_NUMBER =>NULL
1584 ,P_TIME_DEF_ID =>NULL
1585 ,P_BALANCE_DATE =>NULL
1586 ,P_PAYROLL_ID =>NULL
1587 ,P_ORIGINAL_ENTRY_ID =>NULL
1588 ,P_LOCAL_UNIT_ID =>g_local_unit_id
1589 ,P_SOURCE_NUMBER2 =>NULL
1590 ,P_ORGANIZATION_ID =>NULL
1591 ) ;
1592
1593 l_pth_2_wtax_base :=pay_balance_pkg.get_value
1594 (P_DEFINED_BALANCE_ID =>rg_csr_get_defined_balance_id.creator_id
1595 ,P_ASSIGNMENT_ACTION_ID =>l_assignment_action_id
1596 ,P_TAX_UNIT_ID =>g_legal_employer_id
1597 ,P_JURISDICTION_CODE =>NULL
1598 ,P_SOURCE_ID =>NULL
1599 ,P_SOURCE_TEXT =>'SUNEMP'
1600 ,P_TAX_GROUP =>NULL
1601 ,P_DATE_EARNED =>l_effective_date
1602 ,P_GET_RR_ROUTE =>NULL
1603 ,P_GET_RB_ROUTE =>NULL
1604 ,P_SOURCE_TEXT2 =>'N'
1605 ,P_SOURCE_NUMBER =>NULL
1606 ,P_TIME_DEF_ID =>NULL
1607 ,P_BALANCE_DATE =>NULL
1608 ,P_PAYROLL_ID =>NULL
1609 ,P_ORIGINAL_ENTRY_ID =>NULL
1610 ,P_LOCAL_UNIT_ID =>g_local_unit_id
1611 ,P_SOURCE_NUMBER2 =>NULL
1612 ,P_ORGANIZATION_ID =>NULL
1613 ) ;
1614
1615 -- fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 19');
1616 END IF;
1617
1618 IF l_tax_card_type in ('EI', 'FT','S' )
1619 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
1620 + l_ptp2_2_wtax_base + l_pth_1_wtax_base + l_pth_2_wtax_base ) <=0 THEN
1621 -- fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 20');
1622 l_pth2_1_wtax_base :=pay_balance_pkg.get_value
1623 (P_DEFINED_BALANCE_ID =>rg_csr_get_defined_balance_id.creator_id
1624 ,P_ASSIGNMENT_ACTION_ID =>l_assignment_action_id
1625 ,P_TAX_UNIT_ID =>g_legal_employer_id
1626 ,P_JURISDICTION_CODE =>NULL
1627 ,P_SOURCE_ID =>NULL
1628 ,P_SOURCE_TEXT =>'PUNEMP'
1629 ,P_TAX_GROUP =>NULL
1630 ,P_DATE_EARNED =>l_effective_date
1631 ,P_GET_RR_ROUTE =>NULL
1632 ,P_GET_RB_ROUTE =>NULL
1633 ,P_SOURCE_TEXT2 =>'A'
1634 ,P_SOURCE_NUMBER =>NULL
1635 ,P_TIME_DEF_ID =>NULL
1636 ,P_BALANCE_DATE =>NULL
1637 ,P_PAYROLL_ID =>NULL
1638 ,P_ORIGINAL_ENTRY_ID =>NULL
1639 ,P_LOCAL_UNIT_ID =>g_local_unit_id
1640 ,P_SOURCE_NUMBER2 =>NULL
1641 ,P_ORGANIZATION_ID =>NULL
1642 ) ;
1643
1644 l_pth2_2_wtax_base :=pay_balance_pkg.get_value
1645 (P_DEFINED_BALANCE_ID =>rg_csr_get_defined_balance_id.creator_id
1646 ,P_ASSIGNMENT_ACTION_ID =>l_assignment_action_id
1647 ,P_TAX_UNIT_ID =>g_legal_employer_id
1648 ,P_JURISDICTION_CODE =>NULL
1649 ,P_SOURCE_ID =>NULL
1650 ,P_SOURCE_TEXT =>'SUNEMP'
1651 ,P_TAX_GROUP =>NULL
1652 ,P_DATE_EARNED =>l_effective_date
1653 ,P_GET_RR_ROUTE =>NULL
1654 ,P_GET_RB_ROUTE =>NULL
1655 ,P_SOURCE_TEXT2 =>'A'
1656 ,P_SOURCE_NUMBER =>NULL
1657 ,P_TIME_DEF_ID =>NULL
1658 ,P_BALANCE_DATE =>NULL
1659 ,P_PAYROLL_ID =>NULL
1660 ,P_ORIGINAL_ENTRY_ID =>NULL
1661 ,P_LOCAL_UNIT_ID =>g_local_unit_id
1662 ,P_SOURCE_NUMBER2 =>NULL
1663 ,P_ORGANIZATION_ID =>NULL
1664 ) ;
1665 -- fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 21');
1666 END IF;
1667 -- fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 22');
1668 /*Determining Contexts*/
1669 IF l_tax_card_type IN ('P', 'C' ) AND ( l_ptp_1_wtax_base + l_ptp_2_wtax_base ) > 0 THEN
1670 l_payment_type:= 'P';
1671 IF l_ptp_1_wtax_base > 0 THEN
1672 l_source_text :='PEMP';
1673 l_source_text2 :='N';
1674 l_tax_base:=l_ptp_1_wtax_base;
1675 ELSE
1676 l_source_text :='PEMP';
1677 l_source_text2 :='S';
1678 l_tax_base:=l_ptp_2_wtax_base;
1679 END IF;
1680 ELSIF l_tax_card_type in ('EI', 'FT','S' ) AND (l_pt1_1_wtax_base + l_pt1_2_wtax_base ) > 0 THEN
1681 l_payment_type:= '1';
1682 IF l_pt1_1_wtax_base > 0 THEN
1683 l_source_text :='SEMP';
1684 l_source_text2 :='N';
1685 l_tax_base:=l_pt1_1_wtax_base;
1686 ELSE
1687 l_source_text :='SEMP';
1688 l_source_text2 :='S';
1689 l_tax_base:=l_pt1_2_wtax_base;
1690 END IF;
1691 ELSIF l_tax_card_type in ('C','FT','EI','P', 'S' ) AND (l_ptp2_1_wtax_base + l_ptp2_2_wtax_base) > 0 THEN
1692 l_payment_type:= 'P2';
1693 IF l_ptp2_1_wtax_base > 0 THEN
1694 l_source_text :='PEMP';
1695 l_source_text2 :='A';
1696 l_tax_base:=l_ptp2_1_wtax_base;
1697 ELSE
1698 l_source_text :='SEMP';
1699 l_source_text2 :='A';
1700 l_tax_base:=l_ptp2_2_wtax_base;
1701 END IF;
1702
1703 ELSIF l_tax_card_type in ('EI', 'FT','S' ) AND (l_pth_1_wtax_base + l_pth_2_wtax_base ) > 0 THEN
1704 l_payment_type:= 'H';
1705 IF l_pth_1_wtax_base > 0 THEN
1706 l_source_text :='PUNEMP';
1707 l_source_text2 :='N';
1708 l_tax_base:=l_pth_1_wtax_base;
1709 ELSE
1710 l_source_text :='SUNEMP';
1711 l_source_text2 :='N';
1712 l_tax_base:=l_pth_2_wtax_base;
1713 END IF;
1714
1715 ELSIF l_tax_card_type in ('EI', 'FT','S' ) AND (l_pth2_1_wtax_base + l_pth2_2_wtax_base ) > 0 THEN
1716 l_payment_type:= 'H2';
1717 IF l_pth2_1_wtax_base > 0 THEN
1718 l_source_text :='PUNEMP';
1719 l_source_text2 :='A';
1720 l_tax_base:=l_pth2_1_wtax_base;
1721 ELSE
1722 l_source_text :='SUNEMP';
1723 l_source_text2 :='A';
1724 l_tax_base:=l_pth2_2_wtax_base;
1725 END IF;
1726 END IF;
1727
1728
1729 /*Fetching Balance values*/
1730 OPEN csr_Get_Defined_Balance_Id( 'WITHHOLDING_TAX'||l_dimension1);
1731 FETCH csr_Get_Defined_Balance_Id INTO rg_csr_get_defined_balance_id;
1732 CLOSE csr_Get_Defined_Balance_Id;
1733
1734 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 ) ;
1735
1736 OPEN csr_Get_Defined_Balance_Id( 'PENSION'||l_dimension1);
1737 FETCH csr_Get_Defined_Balance_Id INTO rg_csr_get_defined_balance_id;
1738 CLOSE csr_Get_Defined_Balance_Id;
1739
1740 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 ) ;
1741
1742 OPEN csr_Get_Defined_Balance_Id( 'UNEMPLOYMENT_INSURANCE'||l_dimension1);
1743 FETCH csr_Get_Defined_Balance_Id INTO rg_csr_get_defined_balance_id;
1744 CLOSE csr_Get_Defined_Balance_Id;
1745
1746 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 ) ;
1747
1748 OPEN csr_Get_Defined_Balance_Id( 'CUMULATIVE_CAR_BENEFIT'||l_dimension1);
1749 FETCH csr_Get_Defined_Balance_Id INTO rg_csr_get_defined_balance_id;
1750 CLOSE csr_Get_Defined_Balance_Id;
1751
1752 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 ) ;
1753
1754 OPEN csr_Get_Defined_Balance_Id( 'CUMULATIVE_VEHICLE_MILEAGE'||l_dimension1);
1755 FETCH csr_Get_Defined_Balance_Id INTO rg_csr_get_defined_balance_id;
1756 CLOSE csr_Get_Defined_Balance_Id;
1757
1758 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 ) ;
1759
1760 -- fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 23');
1761 BEGIN
1762 FOR balance_rec IN csr_balance('Mortgage Benefit' , rg_csr_get_person_details.business_group_id)
1763 LOOP
1764 OPEN csr_bg_Get_Defined_Balance_Id( balance_rec.balance_name||l_dimension1,rg_csr_get_person_details.business_group_id);
1765 FETCH csr_bg_Get_Defined_Balance_Id INTO rg_csr_bg_get_defined_bal_id;
1766 CLOSE csr_bg_Get_Defined_Balance_Id;
1767 IF csr_balance%FOUND THEN
1768 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 ) ;
1769 l_tot_mortgage_bik := l_tot_mortgage_bik + l_mortgage_bik;
1770 END IF;
1771 END LOOP ;
1772
1773 IF l_tot_mortgage_bik > 0 THEN
1774 l_mortgage_bik_status := 1;
1775 ELSE
1776 l_mortgage_bik_status := 0;
1777 END IF;
1778 EXCEPTION
1779 WHEN others THEN
1780 null;
1781 END;
1782
1783 BEGIN
1784 FOR balance_rec IN csr_balance('Other Benefits' , rg_csr_get_person_details.business_group_id)
1785 LOOP
1786 OPEN csr_bg_Get_Defined_Balance_Id( balance_rec.balance_name||l_dimension1,rg_csr_get_person_details.business_group_id);
1787 FETCH csr_bg_Get_Defined_Balance_Id INTO rg_csr_bg_get_defined_bal_id;
1788 CLOSE csr_bg_Get_Defined_Balance_Id;
1789 IF csr_balance%FOUND THEN
1790 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 ) ;
1791 l_tot_other_bik := l_tot_other_bik + l_other_bik;
1792 END IF;
1793 END LOOP ;
1794
1795 IF l_tot_other_bik > 0 THEN
1796 l_other_bik_status := 1;
1797 ELSE
1798 l_other_bik_status := 0;
1799 END IF;
1800
1801
1802 EXCEPTION
1803 WHEN others THEN
1804 null;
1805 END;
1806
1807 BEGIN
1808 FOR balance_rec IN csr_balance('Housing Benefit' , rg_csr_get_person_details.business_group_id)
1809 LOOP
1810 OPEN csr_bg_Get_Defined_Balance_Id( balance_rec.balance_name||l_dimension1,rg_csr_get_person_details.business_group_id);
1811 FETCH csr_bg_Get_Defined_Balance_Id INTO rg_csr_bg_get_defined_bal_id;
1812 CLOSE csr_bg_Get_Defined_Balance_Id;
1813 IF csr_balance%FOUND THEN
1814 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 ) ;
1815 l_tot_housing_bik := l_tot_housing_bik + l_housing_bik;
1816 END IF;
1817 END LOOP ;
1818
1819 IF l_tot_housing_bik > 0 THEN
1820 l_housing_bik_status := 1;
1821 ELSE
1822 l_housing_bik_status := 0;
1823 END IF;
1824 EXCEPTION
1825 WHEN others THEN
1826 null;
1827 END;
1828
1829 BEGIN
1830 FOR balance_rec IN csr_balance('Phone Benefit' , rg_csr_get_person_details.business_group_id)
1831 LOOP
1832 OPEN csr_bg_Get_Defined_Balance_Id( balance_rec.balance_name||l_dimension1,rg_csr_get_person_details.business_group_id);
1833 FETCH csr_bg_Get_Defined_Balance_Id INTO rg_csr_bg_get_defined_bal_id;
1834 CLOSE csr_bg_Get_Defined_Balance_Id;
1835 IF csr_balance%FOUND THEN
1836 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 ) ;
1837 l_tot_phone_bik := l_tot_phone_bik + l_phone_bik;
1838 END IF;
1839 END LOOP ;
1840
1841 ---- Code added below for Phone Benefit Added Minor Legislative Changes
1842 -- Bug 8425533
1843 OPEN csr_Get_Defined_Balance_Id( 'PHONE_BENEFIT'||l_dimension1);
1844 FETCH csr_Get_Defined_Balance_Id INTO rg_csr_get_defined_balance_id;
1845 CLOSE csr_Get_Defined_Balance_Id;
1846 l_phone_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 ) ;
1847 l_tot_phone_bik := l_tot_phone_bik + l_phone_bik;
1848
1849 IF l_tot_phone_bik > 0 THEN
1850 l_phone_bik_status := 1;
1851 ELSE
1852 l_phone_bik_status := 0;
1853 END IF;
1854 EXCEPTION
1855 WHEN others THEN
1856 null;
1857 END;
1858
1859
1860 OPEN csr_Get_Defined_Balance_Id('LUNCH_BENEFIT'||l_dimension1);
1861 FETCH csr_Get_Defined_Balance_Id INTO rg_csr_get_defined_balance_id;
1862 CLOSE csr_Get_Defined_Balance_Id;
1863
1864 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 ) ;
1865
1866 IF l_lunch_bik > 0 THEN
1867 l_lunch_bik_status := 1;
1868 ELSE
1869 l_lunch_bik_status := 0;
1870 END IF;
1871
1872 OPEN csr_Get_Defined_Balance_Id( 'HALF_DAY_ALLOWANCE_EXPENSES'||l_dimension1);
1873 FETCH csr_Get_Defined_Balance_Id INTO rg_csr_get_defined_balance_id;
1874 CLOSE csr_Get_Defined_Balance_Id;
1875
1876 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 ) ;
1877
1878 IF l_half_day_allowance_expenses > 0 THEN
1879 l_half_day_allowance_status :=1;
1880 ELSE
1881 l_half_day_allowance_status :=0;
1882 END IF;
1883
1884
1885 OPEN csr_Get_Defined_Balance_Id( 'DAILY_ALLOWANCE_DOMESTIC_EXPENSES'||l_dimension1);
1886 FETCH csr_Get_Defined_Balance_Id INTO rg_csr_get_defined_balance_id;
1887 CLOSE csr_Get_Defined_Balance_Id;
1888
1889 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 ) ;
1890 IF l_daily_allowance_d_expenses > 0 THEN
1891 l_daily_allowance_d_status :=1;
1892 ELSE
1893 l_daily_allowance_d_status :=0;
1894 END IF;
1895
1896
1897 OPEN csr_Get_Defined_Balance_Id( 'DAILY_ALLOWANCE_FOREIGN_EXPENSES'||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_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 ) ;
1902 IF l_daily_allowance_fe_expenses > 0 THEN
1903 l_daily_allowance_fe_status :=1;
1904 ELSE
1905 l_daily_allowance_fe_status :=0;
1906 END IF;
1907
1908
1909 OPEN csr_Get_Defined_Balance_Id( 'MEAL_COMPENSATION_EXPENSES'||l_dimension1);
1910 FETCH csr_Get_Defined_Balance_Id INTO rg_csr_get_defined_balance_id;
1911 CLOSE csr_Get_Defined_Balance_Id;
1912
1913 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 ) ;
1914
1915 IF l_meal_comp_expenses > 0 THEN
1916 l_meal_comp_status :=1;
1917 ELSE
1918 l_meal_comp_status :=0;
1919 END IF;
1920
1921 OPEN csr_Get_Defined_Balance_Id( 'TAX_FREE_MILEAGE_EXPENSES'||l_dimension1);
1922 FETCH csr_Get_Defined_Balance_Id INTO rg_csr_get_defined_balance_id;
1923 CLOSE csr_Get_Defined_Balance_Id;
1924
1925 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 ) ;
1926
1927
1928 OPEN csr_Get_Defined_Balance_Id( 'TAX_FREE_MILEAGE'||l_dimension1);
1929 FETCH csr_Get_Defined_Balance_Id INTO rg_csr_get_defined_balance_id;
1930 CLOSE csr_Get_Defined_Balance_Id;
1931
1932 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 ) ;
1933
1934 BEGIN
1935 FOR balance_rec IN csr_balance('Stock Options Benefit' , rg_csr_get_person_details.business_group_id)
1936 LOOP
1937 OPEN csr_bg_Get_Defined_Balance_Id( balance_rec.balance_name||l_dimension1,rg_csr_get_person_details.business_group_id);
1938 FETCH csr_bg_Get_Defined_Balance_Id INTO rg_csr_bg_get_defined_bal_id;
1939 CLOSE csr_bg_Get_Defined_Balance_Id;
1940 IF csr_balance%FOUND THEN
1941 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 ) ;
1942 l_tot_stock_option_bik := l_tot_stock_option_bik + l_stock_option_bik;
1943 END IF;
1944 END LOOP ;
1945 EXCEPTION
1946 WHEN others THEN
1947 null;
1948 END;
1949
1950 OPEN csr_Get_Defined_Balance_Id( 'EMPLOYER_PENSION'||l_dimension1);
1951 FETCH csr_Get_Defined_Balance_Id INTO rg_csr_get_defined_balance_id;
1952 CLOSE csr_Get_Defined_Balance_Id;
1953
1954 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 ) ;
1955
1956 BEGIN
1957 FOR balance_rec IN csr_balance('Travel Ticket Benefit' , rg_csr_get_person_details.business_group_id)
1958 LOOP
1959 OPEN csr_bg_Get_Defined_Balance_Id( balance_rec.balance_name||l_dimension1,rg_csr_get_person_details.business_group_id);
1960 FETCH csr_bg_Get_Defined_Balance_Id INTO rg_csr_bg_get_defined_bal_id;
1961 CLOSE csr_bg_Get_Defined_Balance_Id;
1962 IF csr_balance%FOUND THEN
1963 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 ) ;
1964 l_tot_travel_ticket_bik := l_tot_travel_ticket_bik + l_travel_ticket_bik;
1965 END IF;
1966 END LOOP ;
1967
1968 IF l_tot_travel_ticket_bik > 0 THEN
1969 l_travel_ticket_bik_status := 1;
1970 ELSE
1971 l_travel_ticket_bik_status := 0;
1972 END IF;
1973 EXCEPTION
1974 WHEN others THEN
1975 null;
1976 END;
1977
1978
1979 BEGIN
1980 FOR balance_rec IN csr_balance('Other Benefits Deductions' , rg_csr_get_person_details.business_group_id)
1981 LOOP
1982 OPEN csr_bg_Get_Defined_Balance_Id( balance_rec.balance_name||l_dimension1,rg_csr_get_person_details.business_group_id);
1983 FETCH csr_bg_Get_Defined_Balance_Id INTO rg_csr_bg_get_defined_bal_id;
1984 CLOSE csr_bg_Get_Defined_Balance_Id;
1985 IF csr_balance%FOUND THEN
1986 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 ) ;
1987 l_tot_other_bik_ded := l_tot_other_bik_ded + l_other_bik_ded;
1988 END IF;
1989 END LOOP ;
1990
1991 EXCEPTION
1992 WHEN others THEN
1993 null;
1994 END;
1995
1996
1997 BEGIN
1998 FOR balance_rec IN csr_balance('Car Benefit Deductions' , rg_csr_get_person_details.business_group_id)
1999 LOOP
2000 OPEN csr_bg_Get_Defined_Balance_Id( balance_rec.balance_name||l_dimension1,rg_csr_get_person_details.business_group_id);
2001 FETCH csr_bg_Get_Defined_Balance_Id INTO rg_csr_bg_get_defined_bal_id;
2002 CLOSE csr_bg_Get_Defined_Balance_Id;
2003 IF csr_balance%FOUND THEN
2004 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 ) ;
2005 l_tot_car_bik_ded := l_tot_car_bik_ded + l_car_bik_ded;
2006 END IF;
2007 END LOOP ;
2008
2009 EXCEPTION
2010 WHEN others THEN
2011 null;
2012 END;
2013
2014 BEGIN
2015 FOR balance_rec IN csr_balance('Lunch Benefit Deductions' , rg_csr_get_person_details.business_group_id)
2016 LOOP
2017 OPEN csr_bg_Get_Defined_Balance_Id( balance_rec.balance_name||l_dimension1,rg_csr_get_person_details.business_group_id);
2018 FETCH csr_bg_Get_Defined_Balance_Id INTO rg_csr_bg_get_defined_bal_id;
2019 CLOSE csr_bg_Get_Defined_Balance_Id;
2020 IF csr_balance%FOUND THEN
2021 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 ) ;
2022 l_tot_lunch_bik_ded := l_tot_lunch_bik_ded + l_lunch_bik_ded;
2023 END IF;
2024 END LOOP ;
2025
2026 IF l_tot_lunch_bik_ded > 0 THEN
2027 l_lunch_bik_ded_status := 1;
2028 ELSE
2029 l_lunch_bik_ded_status := 0;
2030 END IF;
2031 EXCEPTION
2032 WHEN others THEN
2033 null;
2034 END;
2035
2036 OPEN csr_Get_Defined_Balance_Id( 'TAXABLE_COMPENSATION'||l_dimension1);
2037 FETCH csr_Get_Defined_Balance_Id INTO rg_csr_get_defined_balance_id;
2038 CLOSE csr_Get_Defined_Balance_Id;
2039
2040 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 ) ;
2041
2042 BEGIN
2043 FOR balance_rec IN csr_balance('Payments for Elected Official' , rg_csr_get_person_details.business_group_id)
2044 LOOP
2045 OPEN csr_bg_Get_Defined_Balance_Id( balance_rec.balance_name||l_dimension1,rg_csr_get_person_details.business_group_id);
2046 FETCH csr_bg_Get_Defined_Balance_Id INTO rg_csr_bg_get_defined_bal_id;
2047 CLOSE csr_bg_Get_Defined_Balance_Id;
2048 IF csr_balance%FOUND THEN
2049 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 ) ;
2050 l_tot_pay_eoff := l_tot_pay_eoff + l_pay_eoff;
2051 END IF;
2052 END LOOP ;
2053 EXCEPTION
2054 WHEN others THEN
2055 null;
2056 END;
2057
2058
2059 BEGIN
2060 FOR balance_rec IN csr_balance('Voluntary PI Fees' , rg_csr_get_person_details.business_group_id)
2061 LOOP
2062 OPEN csr_bg_Get_Defined_Balance_Id( balance_rec.balance_name||l_dimension1,rg_csr_get_person_details.business_group_id);
2063 FETCH csr_bg_Get_Defined_Balance_Id INTO rg_csr_bg_get_defined_bal_id;
2064 CLOSE csr_bg_Get_Defined_Balance_Id;
2065 IF csr_balance%FOUND THEN
2066 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 ) ;
2067 l_tot_vol_pi := l_tot_vol_pi + l_vol_pi;
2068 END IF;
2069 END LOOP ;
2070 EXCEPTION
2071 WHEN others THEN
2072 null;
2073 END;
2074
2075 BEGIN
2076 FOR balance_rec IN csr_balance('Total Voluntary PI Fees' , rg_csr_get_person_details.business_group_id)
2077 LOOP
2078 OPEN csr_bg_Get_Defined_Balance_Id( balance_rec.balance_name||l_dimension1,rg_csr_get_person_details.business_group_id);
2079 FETCH csr_bg_Get_Defined_Balance_Id INTO rg_csr_bg_get_defined_bal_id;
2080 CLOSE csr_bg_Get_Defined_Balance_Id;
2081 IF csr_balance%FOUND THEN
2082 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 ) ;
2083 l_tot_total_vol_pi := l_tot_total_vol_pi + l_total_vol_pi;
2084 END IF;
2085 END LOOP ;
2086 EXCEPTION
2087 WHEN others THEN
2088 null;
2089 END;
2090
2091
2092 OPEN csr_Get_Defined_Balance_Id( 'TAXABLE_EXPENSES'||l_dimension1);
2093 FETCH csr_Get_Defined_Balance_Id INTO rg_csr_get_defined_balance_id;
2094 CLOSE csr_Get_Defined_Balance_Id;
2095 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 ) ;
2096
2097 OPEN csr_Get_Defined_Balance_Id( 'TAXABLE_EXPENSES_SUBJECT_TO_SOCIAL_SECURITY'||l_dimension1);
2098 FETCH csr_Get_Defined_Balance_Id INTO rg_csr_get_defined_balance_id;
2099 CLOSE csr_Get_Defined_Balance_Id;
2100 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 ) ;
2101 l_te_exem_ss :=l_te - l_te_ss;
2102
2103 OPEN csr_Get_Defined_Balance_Id( 'BIK_SUBJECT_TO_SOCIAL_SECURITY'||l_dimension1);
2104 FETCH csr_Get_Defined_Balance_Id INTO rg_csr_get_defined_balance_id;
2105 CLOSE csr_Get_Defined_Balance_Id;
2106
2107 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 ) ;
2108 l_bik_exem_ss :=l_bik - l_bik_ss;
2109
2110 OPEN csr_Get_Defined_Balance_Id( 'SALARY_SUBJECT_TO_SOCIAL_SECURITY'||l_dimension1);
2111 FETCH csr_Get_Defined_Balance_Id INTO rg_csr_get_defined_balance_id;
2112 CLOSE csr_Get_Defined_Balance_Id;
2113
2114 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 ) ;
2115 l_sm_exem_ss := l_salary_income - l_sm_ss;
2116
2117 l_tot_exem_ss := l_te_exem_ss + l_bik_exem_ss + l_sm_exem_ss;
2118
2119 OPEN csr_Get_Defined_Balance_Id( 'EXEMPTED_SOCIAL_SECURITY'||l_dimension1);
2120 FETCH csr_Get_Defined_Balance_Id INTO rg_csr_get_defined_balance_id;
2121 CLOSE csr_Get_Defined_Balance_Id;
2122
2123 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 ) ;
2124
2125 BEGIN
2126 FOR balance_rec IN csr_balance('Social Security Fee Deductions' , rg_csr_get_person_details.business_group_id)
2127 LOOP
2128 OPEN csr_bg_Get_Defined_Balance_Id( balance_rec.balance_name||l_dimension1,rg_csr_get_person_details.business_group_id);
2129 FETCH csr_bg_Get_Defined_Balance_Id INTO rg_csr_bg_get_defined_bal_id;
2130 CLOSE csr_bg_Get_Defined_Balance_Id;
2131 IF csr_balance%FOUND THEN
2132 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 ) ;
2133 l_tot_ss_ded := l_tot_ss_ded + l_ss_ded;
2134 END IF;
2135 END LOOP ;
2136 EXCEPTION
2137 WHEN others THEN
2138 null;
2139 END;
2140
2141 l_631:= l_tot_ss_ded + l_exem_ss;
2142
2143 -- fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 24');
2144
2145
2146
2147 ELSIF l_person_type= 'FR' THEN
2148
2149 -- fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 25');
2150
2151 /*Determining Payment Type*/
2152 OPEN csr_Get_Defined_Balance_Id( 'TAX_AT_SOURCE_BASE'||l_dimension);
2153 FETCH csr_Get_Defined_Balance_Id INTO rg_csr_get_defined_balance_id;
2154 CLOSE csr_Get_Defined_Balance_Id;
2155
2156 l_pta1_1_tstax_base :=pay_balance_pkg.get_value
2157 (P_DEFINED_BALANCE_ID =>rg_csr_get_defined_balance_id.creator_id
2158 ,P_ASSIGNMENT_ACTION_ID =>l_assignment_action_id
2159 ,P_TAX_UNIT_ID =>g_legal_employer_id
2160 ,P_JURISDICTION_CODE =>NULL
2161 ,P_SOURCE_ID =>NULL
2162 ,P_SOURCE_TEXT =>'PEMP'
2163 ,P_TAX_GROUP =>NULL
2164 ,P_DATE_EARNED =>l_effective_date
2165 ,P_GET_RR_ROUTE =>NULL
2166 ,P_GET_RB_ROUTE =>NULL
2167 ,P_SOURCE_TEXT2 =>'N'
2168 ,P_SOURCE_NUMBER =>NULL
2169 ,P_TIME_DEF_ID =>NULL
2170 ,P_BALANCE_DATE =>NULL
2171 ,P_PAYROLL_ID =>NULL
2172 ,P_ORIGINAL_ENTRY_ID =>NULL
2173 ,P_LOCAL_UNIT_ID =>g_local_unit_id
2174 ,P_SOURCE_NUMBER2 =>NULL
2175 ,P_ORGANIZATION_ID =>NULL
2176 ) ;
2177
2178 IF l_pta1_1_tstax_base <= 0 THEN
2179 l_pta1_2_tstax_base :=pay_balance_pkg.get_value
2180 (P_DEFINED_BALANCE_ID =>rg_csr_get_defined_balance_id.creator_id
2181 ,P_ASSIGNMENT_ACTION_ID =>l_assignment_action_id
2182 ,P_TAX_UNIT_ID =>g_legal_employer_id
2183 ,P_JURISDICTION_CODE =>NULL
2184 ,P_SOURCE_ID =>NULL
2185 ,P_SOURCE_TEXT =>'PEMP'
2186 ,P_TAX_GROUP =>NULL
2187 ,P_DATE_EARNED =>l_effective_date
2188 ,P_GET_RR_ROUTE =>NULL
2189 ,P_GET_RB_ROUTE =>NULL
2190 ,P_SOURCE_TEXT2 =>'S'
2191 ,P_SOURCE_NUMBER =>NULL
2192 ,P_TIME_DEF_ID =>NULL
2193 ,P_BALANCE_DATE =>NULL
2194 ,P_PAYROLL_ID =>NULL
2195 ,P_ORIGINAL_ENTRY_ID =>NULL
2196 ,P_LOCAL_UNIT_ID =>g_local_unit_id
2197 ,P_SOURCE_NUMBER2 =>NULL
2198 ,P_ORGANIZATION_ID =>NULL
2199 ) ;
2200 END IF;
2201
2202 l_pta2_1_tstax_base := l_pta1_1_tstax_base;
2203
2204 l_pta2_2_tstax_base :=l_pta1_2_tstax_base;
2205
2206 IF (l_pta1_1_tstax_base + l_pta1_2_tstax_base + l_pta2_1_tstax_base + l_pta2_2_tstax_base ) <= 0 THEN
2207 l_pta4_1_tstax_base :=pay_balance_pkg.get_value
2208 (P_DEFINED_BALANCE_ID =>rg_csr_get_defined_balance_id.creator_id
2209 ,P_ASSIGNMENT_ACTION_ID =>l_assignment_action_id
2210 ,P_TAX_UNIT_ID =>g_legal_employer_id
2211 ,P_JURISDICTION_CODE =>NULL
2212 ,P_SOURCE_ID =>NULL
2213 ,P_SOURCE_TEXT =>'PUNEMP'
2214 ,P_TAX_GROUP =>NULL
2215 ,P_DATE_EARNED =>l_effective_date
2216 ,P_GET_RR_ROUTE =>NULL
2217 ,P_GET_RB_ROUTE =>NULL
2218 ,P_SOURCE_TEXT2 =>'N'
2219 ,P_SOURCE_NUMBER =>NULL
2220 ,P_TIME_DEF_ID =>NULL
2221 ,P_BALANCE_DATE =>NULL
2222 ,P_PAYROLL_ID =>NULL
2223 ,P_ORIGINAL_ENTRY_ID =>NULL
2224 ,P_LOCAL_UNIT_ID =>g_local_unit_id
2225 ,P_SOURCE_NUMBER2 =>NULL
2226 ,P_ORGANIZATION_ID =>NULL
2227 ) ;
2228 END IF;
2229
2230 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
2231 l_pta5_1_tstax_base :=pay_balance_pkg.get_value
2232 (P_DEFINED_BALANCE_ID =>rg_csr_get_defined_balance_id.creator_id
2233 ,P_ASSIGNMENT_ACTION_ID =>l_assignment_action_id
2234 ,P_TAX_UNIT_ID =>g_legal_employer_id
2235 ,P_JURISDICTION_CODE =>NULL
2236 ,P_SOURCE_ID =>NULL
2237 ,P_SOURCE_TEXT =>'PUNEMP'
2238 ,P_TAX_GROUP =>NULL
2239 ,P_DATE_EARNED =>l_effective_date
2240 ,P_GET_RR_ROUTE =>NULL
2241 ,P_GET_RB_ROUTE =>NULL
2242 ,P_SOURCE_TEXT2 =>'PA'
2243 ,P_SOURCE_NUMBER =>NULL
2244 ,P_TIME_DEF_ID =>NULL
2245 ,P_BALANCE_DATE =>NULL
2246 ,P_PAYROLL_ID =>NULL
2247 ,P_ORIGINAL_ENTRY_ID =>NULL
2248 ,P_LOCAL_UNIT_ID =>g_local_unit_id
2249 ,P_SOURCE_NUMBER2 =>NULL
2250 ,P_ORGANIZATION_ID =>NULL
2251 ) ;
2252 END IF;
2253
2254 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
2255 l_pta6_1_tstax_base :=pay_balance_pkg.get_value
2256 (P_DEFINED_BALANCE_ID =>rg_csr_get_defined_balance_id.creator_id
2257 ,P_ASSIGNMENT_ACTION_ID =>l_assignment_action_id
2258 ,P_TAX_UNIT_ID =>g_legal_employer_id
2259 ,P_JURISDICTION_CODE =>NULL
2260 ,P_SOURCE_ID =>NULL
2261 ,P_SOURCE_TEXT =>'PUNEMP'
2262 ,P_TAX_GROUP =>NULL
2263 ,P_DATE_EARNED =>l_effective_date
2264 ,P_GET_RR_ROUTE =>NULL
2265 ,P_GET_RB_ROUTE =>NULL
2266 ,P_SOURCE_TEXT2 =>'A'
2267 ,P_SOURCE_NUMBER =>NULL
2268 ,P_TIME_DEF_ID =>NULL
2269 ,P_BALANCE_DATE =>NULL
2270 ,P_PAYROLL_ID =>NULL
2271 ,P_ORIGINAL_ENTRY_ID =>NULL
2272 ,P_LOCAL_UNIT_ID =>g_local_unit_id
2273 ,P_SOURCE_NUMBER2 =>NULL
2274 ,P_ORGANIZATION_ID =>NULL
2275 ) ;
2276 END IF;
2277
2278 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
2279 l_pta7_1_tstax_base:=pay_balance_pkg.get_value
2280 (P_DEFINED_BALANCE_ID =>rg_csr_get_defined_balance_id.creator_id
2281 ,P_ASSIGNMENT_ACTION_ID =>l_assignment_action_id
2282 ,P_TAX_UNIT_ID =>g_legal_employer_id
2283 ,P_JURISDICTION_CODE =>NULL
2284 ,P_SOURCE_ID =>NULL
2285 ,P_SOURCE_TEXT =>'PEMP'
2286 ,P_TAX_GROUP =>NULL
2287 ,P_DATE_EARNED =>l_effective_date
2288 ,P_GET_RR_ROUTE =>NULL
2289 ,P_GET_RB_ROUTE =>NULL
2290 ,P_SOURCE_TEXT2 =>'KP'
2291 ,P_SOURCE_NUMBER =>NULL
2292 ,P_TIME_DEF_ID =>NULL
2293 ,P_BALANCE_DATE =>NULL
2294 ,P_PAYROLL_ID =>NULL
2295 ,P_ORIGINAL_ENTRY_ID =>NULL
2296 ,P_LOCAL_UNIT_ID =>g_local_unit_id
2297 ,P_SOURCE_NUMBER2 =>NULL
2298 ,P_ORGANIZATION_ID =>NULL
2299 ) ;
2300 END IF;
2301 -- fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 26');
2302 l_org_type:=rg_csr_rpt_header.org_type;
2303
2304 /*Determining Contexts*/
2305 IF l_tax_card_type IN ('TS' ) THEN
2306 -- fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 26'||l_tax_card_type);
2307
2308 IF (l_pta1_1_tstax_base + l_pta1_2_tstax_base) > 0 AND l_org_type = 'PRIV' THEN
2309 l_payment_type:= 'A1';
2310 IF l_pta1_1_tstax_base > 0 THEN
2311 l_source_text :='PEMP';
2312 l_source_text2 :='N';
2313 l_tax_base:=l_pta1_1_tstax_base;
2314 ELSE
2315 l_source_text :='PEMP';
2316 l_source_text2 :='S';
2317 l_tax_base:=l_pta1_2_tstax_base;
2318 END IF;
2319
2320 ELSIF (l_pta2_1_tstax_base + l_pta2_2_tstax_base) > 0 AND l_org_type = 'PUB' THEN
2321 l_payment_type:= 'A2';
2322 IF l_pta1_1_tstax_base > 0 THEN
2323 l_source_text :='PEMP';
2324 l_source_text2 :='N';
2325 l_tax_base:=l_pta2_1_tstax_base;
2326 ELSE
2327 l_source_text :='PEMP';
2328 l_source_text2 :='S';
2329 l_tax_base:=l_pta2_2_tstax_base;
2330 END IF;
2331
2332 ELSIF (l_pta4_1_tstax_base > 0 ) THEN
2333 l_payment_type:= 'A4';
2334 l_source_text :='PUNEMP';
2335 l_source_text2 :='N' ;
2336 l_tax_base:=l_pta4_1_tstax_base;
2337 ELSIF (l_pta5_1_tstax_base > 0 ) THEN
2338 l_payment_type:= 'A5';
2339 l_source_text :='PUNEMP';
2340 l_source_text2 :='PA' ;
2341 l_tax_base:=l_pta5_1_tstax_base;
2342 ELSIF (l_pta6_1_tstax_base > 0 ) THEN
2343 l_payment_type:= 'A6';
2344 l_source_text :='PUNEMP';
2345 l_source_text2 :='A' ;
2346 l_tax_base:=l_pta6_1_tstax_base;
2347 ELSIF (l_pta7_1_tstax_base > 0 ) THEN
2348 l_payment_type:= 'A7';
2349 l_source_text :='PEMP';
2350 l_source_text2 :='KP' ;
2351 l_tax_base:=l_pta7_1_tstax_base;
2352 END IF;
2353 -- fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 27');
2354 /* Fetching Person Address Details */
2355 OPEN csr_per_address( rg_csr_get_person_details.person_id , rg_csr_get_person_details.business_group_id );
2356 FETCH csr_per_address INTO rg_csr_per_address;
2357 CLOSE csr_per_address;
2358
2359 OPEN csr_Get_Defined_Balance_Id( 'TAX_AT_SOURCE'||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_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 OPEN csr_Get_Defined_Balance_Id( 'SOCIAL_SECURITY'||l_dimension1);
2367 FETCH csr_Get_Defined_Balance_Id INTO rg_csr_get_defined_balance_id;
2368 CLOSE csr_Get_Defined_Balance_Id;
2369
2370 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 ) ;
2371
2372 /* Cursor to fetch country code against which Tax Agreement is present*/
2373 OPEN csr_country( rg_csr_get_person_details.place_residence);
2374 FETCH csr_country INTO rg_csr_country;
2375 CLOSE csr_country;
2376
2377 IF rg_csr_country. territory_short_name IS NOT NULL THEN
2378 BEGIN
2379 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);
2380 EXCEPTION
2381 WHEN OTHERS THEN
2382 NULL;
2383 END;
2384 END IF;
2385
2386 IF l_tax_status<>'Y' THEN
2387 l_country_code:=null;
2388 l_place_residence:=rg_csr_country. territory_name ;
2389 ELSE
2390 l_country_code:=rg_csr_get_person_details.place_residence;
2391 l_place_residence:=null;
2392 END IF;
2393
2394 END IF;
2395 -- fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 28');
2396
2397 ELSIF l_person_type= 'WO' THEN
2398
2399 l_payment_type:= '5';
2400 /*
2401 -- fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 29');
2402 OPEN csr_Get_Defined_Balance_Id( 'NOTIONAL_SALARY'||l_dimension);
2403 FETCH csr_Get_Defined_Balance_Id INTO rg_csr_get_defined_balance_id;
2404 CLOSE csr_Get_Defined_Balance_Id;
2405
2406 l_pt5_1_wtax_base :=pay_balance_pkg.get_value
2407 (P_DEFINED_BALANCE_ID =>rg_csr_get_defined_balance_id.creator_id
2408 ,P_ASSIGNMENT_ACTION_ID =>l_assignment_action_id
2409 ,P_TAX_UNIT_ID =>g_legal_employer_id
2410 ,P_JURISDICTION_CODE =>NULL
2411 ,P_SOURCE_ID =>NULL
2412 ,P_SOURCE_TEXT =>'PEMP'
2413 ,P_TAX_GROUP =>NULL
2414 ,P_DATE_EARNED =>l_effective_date
2415 ,P_GET_RR_ROUTE =>NULL
2416 ,P_GET_RB_ROUTE =>NULL
2417 ,P_SOURCE_TEXT2 =>'WO'
2418 ,P_SOURCE_NUMBER =>NULL
2419 ,P_TIME_DEF_ID =>NULL
2420 ,P_BALANCE_DATE =>NULL
2421 ,P_PAYROLL_ID =>NULL
2422 ,P_ORIGINAL_ENTRY_ID =>NULL
2423 ,P_LOCAL_UNIT_ID =>g_local_unit_id
2424 ,P_SOURCE_NUMBER2 =>NULL
2425 ,P_ORGANIZATION_ID =>NULL
2426 ) ;
2427 l_pt5_2_wtax_base :=pay_balance_pkg.get_value
2428 (P_DEFINED_BALANCE_ID =>rg_csr_get_defined_balance_id.creator_id
2429 ,P_ASSIGNMENT_ACTION_ID =>l_assignment_action_id
2430 ,P_TAX_UNIT_ID =>g_legal_employer_id
2431 ,P_JURISDICTION_CODE =>NULL
2432 ,P_SOURCE_ID =>NULL
2433 ,P_SOURCE_TEXT =>'SEMP'
2434 ,P_TAX_GROUP =>NULL
2435 ,P_DATE_EARNED =>l_effective_date
2436 ,P_GET_RR_ROUTE =>NULL
2437 ,P_GET_RB_ROUTE =>NULL
2438 ,P_SOURCE_TEXT2 =>'WO'
2439 ,P_SOURCE_NUMBER =>NULL
2440 ,P_TIME_DEF_ID =>NULL
2441 ,P_BALANCE_DATE =>NULL
2442 ,P_PAYROLL_ID =>NULL
2443 ,P_ORIGINAL_ENTRY_ID =>NULL
2444 ,P_LOCAL_UNIT_ID =>g_local_unit_id
2445 ,P_SOURCE_NUMBER2 =>NULL
2446 ,P_ORGANIZATION_ID =>NULL
2447 ) ;
2448
2449
2450 IF (l_pt5_1_wtax_base > 0 ) THEN
2451 l_source_text :='PEMP';
2452 l_source_text2 :='WO' ;
2453 l_wtax_base:= l_pt5_1_wtax_base;
2454 ELSE
2455 l_source_text :='SEMP';
2456 l_source_text2 :='WO' ;
2457 l_wtax_base:= l_pt5_2_wtax_base;
2458 END IF;
2459 */
2460
2461 OPEN csr_Get_Defined_Balance_Id( 'NOTIONAL_SALARY'||l_dimension1);
2462 FETCH csr_Get_Defined_Balance_Id INTO rg_csr_get_defined_balance_id;
2463 CLOSE csr_Get_Defined_Balance_Id;
2464 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 ) ;
2465 l_tax := 0;
2466 l_mtax_base := l_tax_base ;
2467 l_mtax := l_tax;
2468
2469 END IF;
2470
2471
2472 IF l_person_type<> 'WO' THEN
2473 -- fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 31');
2474
2475 OPEN csr_Get_Defined_Balance_Id( 'OTHER_PAYMENTS_SUBJECT_TO_TAX'||l_dimension1);
2476 FETCH csr_Get_Defined_Balance_Id INTO rg_csr_get_defined_balance_id;
2477 CLOSE csr_Get_Defined_Balance_Id;
2478
2479 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 ) ;
2480
2481 OPEN csr_Get_Defined_Balance_Id( 'COMPENSATION_FOR_USE_OF_ITEM'||l_dimension1);
2482 FETCH csr_Get_Defined_Balance_Id INTO rg_csr_get_defined_balance_id;
2483 CLOSE csr_Get_Defined_Balance_Id;
2484
2485 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 ) ;
2486
2487 -- fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 32');
2488
2489 IF l_tax_card_type <>'TS' THEN
2490 -- fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 33');
2491
2492 OPEN csr_Get_Defined_Balance_Id( 'CAPITAL_INCOME_BASE'||l_dimension1);
2493 FETCH csr_Get_Defined_Balance_Id INTO rg_csr_get_defined_balance_id;
2494 CLOSE csr_Get_Defined_Balance_Id;
2495
2496 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 ) ;
2497
2498 OPEN csr_Get_Defined_Balance_Id( 'CAPITAL_INCOME_TAX'||l_dimension1);
2499 FETCH csr_Get_Defined_Balance_Id INTO rg_csr_get_defined_balance_id;
2500 CLOSE csr_Get_Defined_Balance_Id;
2501
2502 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 ) ;
2503
2504
2505 END IF;
2506
2507 /*Proportion the Tax Base and Tax according to Payment Types*/
2508 -- fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 34');
2509 IF l_tax_base > 0 THEN
2510 l_mtax_base := l_tax_base - (l_pth4_base + l_ptg_base) ;
2511 l_mtax := round((l_mtax_base/l_tax_base) * l_tax,2);
2512
2513 IF l_pth4_base > 0 THEN
2514 l_s1_tax_base := l_pth4_base ;
2515 l_s1_tax := round((l_s1_tax_base/l_tax_base) * l_tax,2);
2516 END IF;
2517
2518 IF l_ptg_base > 0 THEN
2519 l_s2_tax_base := l_ptg_base ;
2520 l_s2_tax := round((l_s2_tax_base/l_tax_base) * l_tax,2);
2521 END IF;
2522
2523 IF l_ptg1_base > 0 THEN
2524 l_s3_tax_base := l_ptg1_base;
2525 l_s3_tax := l_ptg1_tax ;
2526 END IF;
2527
2528 END IF;
2529
2530
2531
2532
2533 OPEN csr_Get_Defined_Balance_Id( 'DEDUCTIONS_BEFORE_TAX'||l_dimension1);
2534 FETCH csr_Get_Defined_Balance_Id INTO rg_csr_get_defined_balance_id;
2535 CLOSE csr_Get_Defined_Balance_Id;
2536
2537 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 ) ;
2538
2539 OPEN csr_Get_Defined_Balance_Id( 'EXTERNAL_EXPENSES'||l_dimension1);
2540 FETCH csr_Get_Defined_Balance_Id INTO rg_csr_get_defined_balance_id;
2541 CLOSE csr_Get_Defined_Balance_Id;
2542
2543 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 ) ;
2544 -- fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 35');
2545 END IF;
2546
2547 IF l_payment_type IS NOT NULL THEN
2548 -- fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 36');
2549 OPEN csr_arch_chk(l_record_id , l_payment_type , p_assignment_action_id);
2550 FETCH csr_arch_chk INTO l_count;
2551 CLOSE csr_arch_chk;
2552
2553 IF l_count < 1 and l_record_id ='VSPSERIE' THEN
2554 /* Values in action_information29 for record id VSPSERIE
2555 1 Identifier for record no 1 for primary payment type record for VSPSERIE
2556 2 Identifier for record no 2 for primary payment type record for VSPSERIE
2557 3 Identifier for records for Legal Persons
2558 */
2559
2560 IF l_source_text2 ='N' AND LENGTH(rg_csr_get_person_details.national_identifier) = 9 AND l_payment_type='H' THEN
2561
2562 FOR i IN 1..12
2563 LOOP
2564
2565 SELECT LAST_DAY(TO_DATE('01'||LPAD(i,2,'0')||g_year,'DDMMYYYY')) , LPAD(i,2,'0')
2566 INTO l_bal_date, l_month
2567 FROM DUAL;
2568
2569 IF TO_NUMBER(TO_CHAR(l_bal_date,'MM')) <= TO_NUMBER(TO_CHAR(rg_csr_asg_effective_date.EFFECTIVE_END_DATE,'MM')) THEN
2570
2571
2572 OPEN csr_Get_Defined_Balance_Id( 'WITHHOLDING_TAX'||l_dimension2);
2573 FETCH csr_Get_Defined_Balance_Id INTO rg_csr_get_defined_balance_id;
2574 CLOSE csr_Get_Defined_Balance_Id;
2575 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) ;
2576
2577
2578 OPEN csr_Get_Defined_Balance_Id( 'WITHHOLDING_TAX_BASE'||l_dimension2);
2579 FETCH csr_Get_Defined_Balance_Id INTO rg_csr_get_defined_balance_id;
2580 CLOSE csr_Get_Defined_Balance_Id;
2581 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) ;
2582
2583 pay_action_information_api.create_action_information (
2584 p_action_information_id=> l_action_info_id,
2585 p_action_context_id=> p_assignment_action_id,
2586 p_action_context_type=> 'AAP',
2587 p_object_version_number=> l_ovn,
2588 p_effective_date=> g_effective_date,
2589 p_source_id=> NULL,
2590 p_source_text=> NULL,
2591 p_action_information_category=> 'EMEA REPORT INFORMATION',
2592 p_action_information1=> 'PYFIDPSA',
2593 p_action_information2=>rg_csr_get_person_details.person_id,
2594 p_action_information3=>l_record_id ,
2595 p_action_information4=>l_payment_type,
2596 p_action_information5=>g_transact_type ,
2597 p_action_information6=> g_year,
2598 p_action_information7=> rg_csr_rpt_header.business_id,
2599 p_action_information8=> rg_csr_get_person_details.national_identifier,
2600 p_action_information9=> FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_wtax_base*100,0)) ,
2601 p_action_information10=> FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_wtax*100,0)) ,
2602 p_action_information11=> NULL ,
2603 p_action_information12=> NULL ,
2604 p_action_information13=> NULL ,
2605 p_action_information14=> NULL ,
2606 p_action_information15=> l_month ,
2607 p_action_information16=> NULL ,
2608 p_action_information17=> NULL ,
2609 p_action_information18=> NULL ,
2610 p_action_information19=> NULL ,
2611 p_action_information20=> NULL ,
2612 p_action_information21=> NULL ,
2613 p_action_information22=> NULL ,
2614 p_action_information23=> NULL ,
2615 p_action_information24=> NULL ,
2616 p_action_information25=> NULL ,
2617 p_action_information26=> NULL ,
2618 p_action_information27=> NULL ,
2619 p_action_information28=> NULL ,
2620 p_action_information29=> '3' ,
2621 p_action_information30 => NULL);
2622
2623 /*Reason for putting i in action_information15 is to allow summation of records of type 3 and 1
2624 of the record id VSPSERIE*/
2625
2626 END IF;
2627
2628 END LOOP;
2629
2630 ELSE
2631
2632 -- fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 37');
2633 pay_action_information_api.create_action_information (
2634 p_action_information_id=> l_action_info_id,
2635 p_action_context_id=> p_assignment_action_id,
2636 p_action_context_type=> 'AAP',
2637 p_object_version_number=> l_ovn,
2638 p_effective_date=> g_effective_date,
2639 p_source_id=> NULL,
2640 p_source_text=> NULL,
2641 p_action_information_category=> 'EMEA REPORT INFORMATION',
2642 p_action_information1=> 'PYFIDPSA',
2643 p_action_information2=>rg_csr_get_person_details.person_id,
2644 p_action_information3=>l_record_id ,
2645 p_action_information4=>l_payment_type,
2646 p_action_information5=>g_transact_type ,
2647 p_action_information6=> g_year,
2648 p_action_information7=> rg_csr_rpt_header.business_id,
2649 p_action_information8=> rg_csr_get_person_details.national_identifier,
2650 p_action_information9=> FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_mtax_base*100,0) - nvl(l_bik*100,0)),
2651 p_action_information10=> FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_mtax*100,0)) ,
2652 p_action_information11=> FND_NUMBER.NUMBER_TO_CANONICAL((nvl(l_empl_pension,0) + nvl(l_empl_unemp_ins,0))*100 ) ,
2653 p_action_information12=> FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_pretax_ded*100,0)) ,
2654 p_action_information13=> FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_cum_car_benefit*100,0)) ,
2655 p_action_information14=> FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_cum_mileage,0)) ,
2656 p_action_information15=> l_age_category, -- Changes 2009
2657 p_action_information16=> FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_tot_mortgage_bik*100,0)) ,
2658 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) ,
2659 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,
2660 p_action_information19=> FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_external_expenses*100,0)),
2661 p_action_information20=> l_daily_allowance_d_status||l_half_day_allowance_status||l_daily_allowance_fe_status||l_meal_comp_status,
2662 p_action_information21=> FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_tax_free_mileage,0)),
2663 p_action_information22=>FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_tax_free_mileage_expenses*100,0)),
2664 p_action_information23=> FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_tot_stock_option_bik*100,0)),
2665 p_action_information24=> FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_emp_pension*100,0)),
2666 p_action_information25=> FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_bik*100,0)),
2667 p_action_information26=> FND_NUMBER.NUMBER_TO_CANONICAL(nvl((ROUND((l_tot_travel_ticket_bik/4),2))*100,0)),
2668 p_action_information27=> FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_tot_exem_ss*100,0)),
2669 p_action_information28=> FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_631*100,0)),
2670 p_action_information29=> '1' ,
2671 p_action_information30 => NULL
2672 );
2673
2674 pay_action_information_api.create_action_information (
2675 p_action_information_id=> l_action_info_id,
2676 p_action_context_id=> p_assignment_action_id,
2677 p_action_context_type=> 'AAP',
2678 p_object_version_number=> l_ovn,
2679 p_effective_date=> g_effective_date,
2680 p_source_id=> NULL,
2681 p_source_text=> NULL,
2682 p_action_information_category=> 'EMEA REPORT INFORMATION',
2683 p_action_information1=> 'PYFIDPSA',
2684 p_action_information2=>rg_csr_get_person_details.person_id,
2685 p_action_information3=>l_record_id ,
2686 p_action_information4=>l_payment_type,
2687 p_action_information5=>g_transact_type ,
2688 p_action_information6=> g_year,
2689 p_action_information7=> rg_csr_rpt_header.business_id,
2690 p_action_information8=> rg_csr_get_person_details.national_identifier,
2691 p_action_information9=> FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_tax_comp*100,0)),
2692 p_action_information10=> FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_tot_pay_eoff *100,0)),
2693 p_action_information11=> FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_tot_vol_pi*100,0)),
2694 p_action_information12=> FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_tot_total_vol_pi*100,0)),
2695 p_action_information13=> l_full_car_benefit_status||l_bik_use_car_status,
2696 p_action_information14=> FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_tot_car_bik_ded*100,0)),
2697 p_action_information15=> FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_tot_other_bik_ded*100,0)),
2698 p_action_information16=> l_car_ben_val, -- Changes 2009
2699 p_action_information17=> NULL,
2700 p_action_information18=> NULL,
2701 p_action_information19=> NULL,
2702 p_action_information20=> NULL,
2703 p_action_information21=> NULL,
2704 p_action_information22=> NULL,
2705 p_action_information23=> NULL,
2706 p_action_information24=> NULL,
2707 p_action_information25=> NULL,
2708 p_action_information26=> NULL,
2709 p_action_information27=> NULL,
2710 p_action_information28=> NULL,
2711 p_action_information29=> '2',
2712 p_action_information30 => NULL
2713 );
2714
2715 IF l_s1_tax_base > 0 THEN
2716 pay_action_information_api.create_action_information (
2717 p_action_information_id=> l_action_info_id,
2718 p_action_context_id=> p_assignment_action_id,
2719 p_action_context_type=> 'AAP',
2720 p_object_version_number=> l_ovn,
2721 p_effective_date=> g_effective_date,
2722 p_source_id=> NULL,
2723 p_source_text=> NULL,
2724 p_action_information_category=> 'EMEA REPORT INFORMATION',
2725 p_action_information1=> 'PYFIDPSA',
2726 p_action_information2=>rg_csr_get_person_details.person_id,
2727 p_action_information3=>l_record_id ,
2728 p_action_information4=>'H4',
2729 p_action_information5=>g_transact_type ,
2730 p_action_information6=> g_year,
2731 p_action_information7=> rg_csr_rpt_header.business_id,
2732 p_action_information8=> rg_csr_get_person_details.national_identifier,
2733 p_action_information9=> FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_s1_tax_base*100,0)),
2734 p_action_information10=> FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_s1_tax*100,0)) ,
2735 p_action_information11=> NULL,
2736 p_action_information12=> NULL,
2737 p_action_information13=> NULL,
2738 p_action_information14=> NULL,
2739 p_action_information15=> NULL,
2740 p_action_information16=> NULL,
2741 p_action_information17=> NULL,
2742 p_action_information18=> NULL,
2743 p_action_information19=> NULL,
2744 p_action_information20=> NULL,
2745 p_action_information21=> NULL,
2746 p_action_information22=> NULL,
2747 p_action_information23=> NULL,
2748 p_action_information24=> NULL,
2749 p_action_information25=> NULL,
2750 p_action_information26=> NULL,
2751 p_action_information27=> NULL,
2752 p_action_information28=> NULL,
2753 p_action_information29=> 1,
2754 p_action_information30 => NULL
2755 );
2756 END IF;
2757
2758 IF l_s2_tax_base > 0 THEN
2759 pay_action_information_api.create_action_information (
2760 p_action_information_id=> l_action_info_id,
2761 p_action_context_id=> p_assignment_action_id,
2762 p_action_context_type=> 'AAP',
2763 p_object_version_number=> l_ovn,
2764 p_effective_date=> g_effective_date,
2765 p_source_id=> NULL,
2766 p_source_text=> NULL,
2767 p_action_information_category=> 'EMEA REPORT INFORMATION',
2768 p_action_information1=> 'PYFIDPSA',
2769 p_action_information2=>rg_csr_get_person_details.person_id,
2770 p_action_information3=>l_record_id ,
2771 p_action_information4=>'G',
2772 p_action_information5=>g_transact_type ,
2773 p_action_information6=> g_year,
2774 p_action_information7=> rg_csr_rpt_header.business_id,
2775 p_action_information8=> rg_csr_get_person_details.national_identifier,
2776 p_action_information9=> FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_s2_tax_base*100,0)),
2777 p_action_information10=> FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_s2_tax*100,0)) ,
2778 p_action_information11=> NULL,
2779 p_action_information12=> NULL,
2780 p_action_information13=> NULL,
2781 p_action_information14=> NULL,
2782 p_action_information15=> NULL,
2783 p_action_information16=> NULL,
2784 p_action_information17=> NULL,
2785 p_action_information18=> NULL,
2786 p_action_information19=> NULL,
2787 p_action_information20=> NULL,
2788 p_action_information21=> NULL,
2789 p_action_information22=> NULL,
2790 p_action_information23=> NULL,
2791 p_action_information24=> NULL,
2792 p_action_information25=> NULL,
2793 p_action_information26=> NULL,
2794 p_action_information27=> NULL,
2795 p_action_information28=> NULL,
2796 p_action_information29=> 1,
2797 p_action_information30 =>NULL
2798 );
2799 END IF;
2800
2801 -- fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 38');
2802 IF l_s3_tax_base > 0 THEN
2803 pay_action_information_api.create_action_information (
2804 p_action_information_id=> l_action_info_id,
2805 p_action_context_id=> p_assignment_action_id,
2806 p_action_context_type=> 'AAP',
2807 p_object_version_number=> l_ovn,
2808 p_effective_date=> g_effective_date,
2809 p_source_id=> NULL,
2810 p_source_text=> NULL,
2811 p_action_information_category=> 'EMEA REPORT INFORMATION',
2812 p_action_information1=> 'PYFIDPSA',
2813 p_action_information2=>rg_csr_get_person_details.person_id,
2814 p_action_information3=>l_record_id ,
2815 p_action_information4=>'G1',
2816 p_action_information5=>g_transact_type ,
2817 p_action_information6=> g_year,
2818 p_action_information7=> rg_csr_rpt_header.business_id,
2819 p_action_information8=> rg_csr_get_person_details.national_identifier,
2820 p_action_information9=> FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_s3_tax_base*100,0)),
2821 p_action_information10=> FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_s3_tax*100,0)) ,
2822 p_action_information11=> NULL,
2823 p_action_information12=> NULL,
2824 p_action_information13=> NULL,
2825 p_action_information14=> NULL,
2826 p_action_information15=> NULL,
2827 p_action_information16=> NULL,
2828 p_action_information17=> NULL,
2829 p_action_information18=> NULL,
2830 p_action_information19=> NULL,
2831 p_action_information20=> NULL,
2832 p_action_information21=> NULL,
2833 p_action_information22=> NULL,
2834 p_action_information23=> NULL,
2835 p_action_information24=> NULL,
2836 p_action_information25=> NULL,
2837 p_action_information26=> NULL,
2838 p_action_information27=> NULL,
2839 p_action_information28=> NULL,
2840 p_action_information29=> 1,
2841 p_action_information30 => NULL
2842 );
2843 END IF;
2844
2845 END IF;
2846
2847 ELSIF l_count < 1 and l_record_id ='VSRAERIE' THEN
2848 -- fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 39');
2849
2850 pay_action_information_api.create_action_information (
2851 p_action_information_id=> l_action_info_id,
2852 p_action_context_id=> p_assignment_action_id,
2853 p_action_context_type=> 'AAP',
2854 p_object_version_number=> l_ovn,
2855 p_effective_date=> g_effective_date,
2856 p_source_id=> NULL,
2857 p_source_text=> NULL,
2858 p_action_information_category=> 'EMEA REPORT INFORMATION',
2859 p_action_information1=> 'PYFIDPSA',
2860 p_action_information2=>rg_csr_get_person_details.person_id,
2861 p_action_information3=>l_record_id ,
2862 p_action_information4=>l_payment_type,
2863 p_action_information5=>g_transact_type ,
2864 p_action_information6=> g_year,
2865 p_action_information7=> rg_csr_rpt_header.business_id,
2866 p_action_information8=> rg_csr_get_person_details.national_identifier,
2867 p_action_information9=> rg_csr_get_person_details.last_name ,
2868 p_action_information10=> rg_csr_get_person_details.first_name ,
2869 p_action_information11=> rg_csr_per_address.address ,
2870 p_action_information12=> rg_csr_per_address.postal_code ,
2871 p_action_information13=> rg_csr_per_address.d_country ,
2872 p_action_information14=> l_country_code ,
2873 p_action_information15=> FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_mtax_base*100,0) - nvl(l_bik*100,0)),
2874 p_action_information16=> FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_mtax*100,0)) ,
2875 p_action_information17=> FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_salary_income*100,0)) ,
2876 p_action_information18=> FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_bik*100,0)),
2877 p_action_information19=> FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_pretax_ded*100,0)) ,
2878 p_action_information20=> FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_external_expenses*100,0)),
2879 p_action_information21=> rg_csr_rpt_header.contact_person,
2880 p_action_information22=>rg_csr_rpt_header.phone,
2881 p_action_information23=> FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_social_security *100,0)),
2882 p_action_information24=> l_place_residence,
2883 p_action_information25=> rg_csr_get_person_details.fpin,
2884 p_action_information26=> l_bank_acc_num, -- changes 2009
2885 p_action_information27=> NULL,
2886 p_action_information28=> NULL,
2887 p_action_information29=> NULL,
2888 p_action_information30=> NULL
2889 );
2890 IF l_s1_tax_base > 0 THEN
2891 pay_action_information_api.create_action_information (
2892 p_action_information_id=> l_action_info_id,
2893 p_action_context_id=> p_assignment_action_id,
2894 p_action_context_type=> 'AAP',
2895 p_object_version_number=> l_ovn,
2896 p_effective_date=> g_effective_date,
2897 p_source_id=> NULL,
2898 p_source_text=> NULL,
2899 p_action_information_category=> 'EMEA REPORT INFORMATION',
2900 p_action_information1=> 'PYFIDPSA',
2901 p_action_information2=>rg_csr_get_person_details.person_id,
2902 p_action_information3=>l_record_id ,
2903 p_action_information4=>'D1',
2904 p_action_information5=>g_transact_type ,
2905 p_action_information6=> g_year,
2906 p_action_information7=> rg_csr_rpt_header.business_id,
2907 p_action_information8=> rg_csr_get_person_details.national_identifier,
2908 p_action_information9=> rg_csr_get_person_details.last_name ,
2909 p_action_information10=> rg_csr_get_person_details.first_name ,
2910 p_action_information11=> rg_csr_per_address.address ,
2911 p_action_information12=> rg_csr_per_address.postal_code ,
2912 p_action_information13=> rg_csr_per_address.d_country ,
2913 p_action_information14=> l_country_code ,
2914 p_action_information15=> FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_s1_tax_base*100,0)),
2915 p_action_information16=> FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_s1_tax*100,0)) ,
2916 p_action_information17=> NULL,
2917 p_action_information18=> NULL,
2918 p_action_information19=> NULL,
2919 p_action_information20=> NULL,
2920 p_action_information21=> rg_csr_rpt_header.contact_person,
2921 p_action_information22=>rg_csr_rpt_header.phone,
2922 p_action_information23=> NULL,
2923 p_action_information24=> l_place_residence,
2924 p_action_information25=> rg_csr_get_person_details.fpin,
2925 p_action_information26=> NULL,
2926 p_action_information27=> NULL,
2927 p_action_information28=> NULL,
2928 p_action_information29=> NULL,
2929 p_action_information30=>NULL
2930 );
2931 END IF;
2932
2933 IF l_s2_tax_base > 0 THEN
2934 pay_action_information_api.create_action_information (
2935 p_action_information_id=> l_action_info_id,
2936 p_action_context_id=> p_assignment_action_id,
2937 p_action_context_type=> 'AAP',
2938 p_object_version_number=> l_ovn,
2939 p_effective_date=> g_effective_date,
2940 p_source_id=> NULL,
2941 p_source_text=> NULL,
2942 p_action_information_category=> 'EMEA REPORT INFORMATION',
2943 p_action_information1=> 'PYFIDPSA',
2944 p_action_information2=>rg_csr_get_person_details.person_id,
2945 p_action_information3=>l_record_id ,
2946 p_action_information4=>'A3',
2947 p_action_information5=>g_transact_type ,
2948 p_action_information6=> g_year,
2949 p_action_information7=> rg_csr_rpt_header.business_id,
2950 p_action_information8=> rg_csr_get_person_details.national_identifier,
2951 p_action_information9=> rg_csr_get_person_details.last_name ,
2952 p_action_information10=> rg_csr_get_person_details.first_name ,
2953 p_action_information11=> rg_csr_per_address.address ,
2954 p_action_information12=> rg_csr_per_address.postal_code ,
2955 p_action_information13=> rg_csr_per_address.d_country ,
2956 p_action_information14=> l_country_code ,
2957 p_action_information15=> FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_s2_tax_base*100,0)),
2958 p_action_information16=> FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_s2_tax*100,0)) ,
2959 p_action_information17=> NULL,
2960 p_action_information18=> NULL,
2961 p_action_information19=> NULL,
2962 p_action_information20=> NULL,
2963 p_action_information21=> rg_csr_rpt_header.contact_person,
2964 p_action_information22=>rg_csr_rpt_header.phone,
2965 p_action_information23=> NULL,
2966 p_action_information24=>l_place_residence,
2967 p_action_information25=> rg_csr_get_person_details.fpin,
2968 p_action_information26=> NULL,
2969 p_action_information27=> NULL,
2970 p_action_information28=> NULL,
2971 p_action_information29=> NULL,
2972 p_action_information30=> NULL
2973 );
2974 END IF;
2975
2976 -- fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 40');
2977 END IF;
2978
2979 IF l_subsidy_status ='Y' THEN
2980
2981 IF l_record_id='VSPSERIE' THEN
2982 l_tax_type:='80';
2983 ELSE
2984 l_tax_type:='82';
2985 END IF;
2986 l_record_id:='VSPSTUKI';
2987
2988
2989 FOR i IN 1..12
2990 LOOP
2991 SELECT LAST_DAY(TO_DATE('01'||LPAD(i,2,'0')||g_year,'DDMMYYYY')),LPAD(i,2,'0')
2992 INTO l_bal_date,l_month
2993 FROM DUAL;
2994
2995 IF TO_NUMBER(TO_CHAR(l_bal_date,'MM')) <= TO_NUMBER(TO_CHAR(rg_csr_asg_effective_date.EFFECTIVE_END_DATE,'MM')) THEN
2996
2997 CODE_014:= 0;
2998 CODE_015:= 0;
2999
3000 OPEN csr_Get_Defined_Balance_Id( 'PART_TIME_PENSIONER_AMOUNT'||l_dimension2);
3001 FETCH csr_Get_Defined_Balance_Id INTO rg_csr_get_defined_balance_id;
3002 CLOSE csr_Get_Defined_Balance_Id;
3003
3004 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) ;
3005
3006 OPEN csr_Get_Defined_Balance_Id( 'SUBSIDY_FOR_LOW_PAID_EMPLOYEES'||l_dimension2);
3007 FETCH csr_Get_Defined_Balance_Id INTO rg_csr_get_defined_balance_id;
3008 CLOSE csr_Get_Defined_Balance_Id;
3009 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) ;
3010
3011
3012 IF l_subsidy_amt > 0 THEN
3013
3014 OPEN csr_Get_Defined_Balance_Id( 'SUBSIDY_BASIS'||l_dimension2);
3015 FETCH csr_Get_Defined_Balance_Id INTO rg_csr_get_defined_balance_id;
3016 CLOSE csr_Get_Defined_Balance_Id;
3017 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) ;
3018
3019
3020 IF l_pt_pension_amt > 0 THEN
3021 CODE_015:= l_subsidy_basis;
3022 ELSE
3023 CODE_014:= l_subsidy_basis;
3024 END IF;
3025
3026 pay_action_information_api.create_action_information (
3027 p_action_information_id=> l_action_info_id,
3028 p_action_context_id=> p_assignment_action_id,
3029 p_action_context_type=> 'AAP',
3030 p_object_version_number=> l_ovn,
3031 p_effective_date=> g_effective_date,
3032 p_source_id=> NULL,
3033 p_source_text=> NULL,
3034 p_action_information_category=> 'EMEA REPORT INFORMATION',
3035 p_action_information1=> 'PYFIDPSA',
3036 p_action_information2=>rg_csr_get_person_details.person_id,
3037 p_action_information3=>l_record_id ,
3038 p_action_information4=>l_tax_type,
3039 p_action_information5=>g_transact_type ,
3040 p_action_information6=> g_year,
3041 p_action_information7=> rg_csr_rpt_header.business_id,
3042 p_action_information8=> rg_csr_get_person_details.national_identifier,
3043 p_action_information9=> l_month,
3044 p_action_information10=> FND_NUMBER.NUMBER_TO_CANONICAL(nvl(CODE_014*100 ,0)) ,
3045 p_action_information11=> FND_NUMBER.NUMBER_TO_CANONICAL(nvl(CODE_015 *100,0)) ,
3046 p_action_information12=> FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_subsidy_amt*100,0)) ,
3047 p_action_information13=> '1',
3048 p_action_information14=> NULL ,
3049 p_action_information15=> NULL,
3050 p_action_information16=> NULL ,
3051 p_action_information17=> NULL ,
3052 p_action_information18=> NULL ,
3053 p_action_information19=> NULL ,
3054 p_action_information20=> NULL ,
3055 p_action_information21=> NULL ,
3056 p_action_information22=> NULL ,
3057 p_action_information23=> NULL ,
3058 p_action_information24=> NULL ,
3059 p_action_information25=> NULL ,
3060 p_action_information26=> NULL ,
3061 p_action_information27=> NULL ,
3062 p_action_information28=> NULL ,
3063 p_action_information29=> NULL,
3064 p_action_information30 => NULL
3065 );
3066 END IF;
3067 END IF;
3068 END LOOP;
3069
3070 END IF;
3071
3072 END IF;
3073 END IF;
3074 END IF;---ARCHIVE=YES
3075 IF g_debug THEN
3076 hr_utility.set_location(' Leaving Procedure ARCHIVE_CODE',390);
3077 END IF;
3078
3079 EXCEPTION
3080 WHEN others THEN
3081 IF g_debug THEN
3082 hr_utility.set_location('error raised in archive code ',5);
3083 END if;
3084 RAISE;
3085 END ARCHIVE_CODE;
3086
3087 PROCEDURE DEINITIALIZATION_CODE
3088 (p_payroll_action_id in pay_payroll_actions.payroll_action_id%type) is
3089
3090 /* Cursor to retrieve data from the Header record(Employer level) */
3091 CURSOR csr_prpt_header (p_payroll_action_id NUMBER) IS
3092 SELECT action_information2 emp_type ,action_information3 emp_id
3093 ,action_information4 business_id ,action_information5 org_type ,action_information6 contact_person
3094 ,action_information7 phone ,action_information8 year ,action_information9 transact_type
3095 FROM pay_action_information pai
3096 WHERE action_information_category = 'EMEA REPORT INFORMATION'
3097 AND action_information1 = 'PYFIDPSA'
3098 AND action_context_id = p_payroll_action_id;
3099
3100 rg_csr_prpt_header csr_prpt_header%rowtype;
3101
3102 /* Cursor to fetch data related to Finnish Residents*/
3103 CURSOR csr_arch_fi (p_payroll_action_id NUMBER) IS
3104 SELECT substr(action_information4,1,1) payment_type , SUM(nvl(action_information9,0)) payment , SUM(nvl(action_information10,0)) tax , COUNT(*) num
3105 ,SUM(nvl(action_information12,0)) pretax_ded, SUM(nvl(action_information11,0)) pen_unemp_ins , SUM(nvl(action_information25,0)) bik
3106 , SUM(nvl(action_information27,0) + nvl(action_information23,0)) CODE_670 ,SUM(nvl(action_information28,0)) CODE_631
3107 FROM pay_action_information pai , pay_assignment_actions paa
3108 WHERE pai.action_information_category = 'EMEA REPORT INFORMATION'
3109 AND pai.action_context_type= 'AAP'
3110 AND pai.action_context_id= paa.assignment_action_id
3111 AND paa.payroll_action_id = p_payroll_action_id
3112 AND action_information3 ='VSPSERIE'
3113 AND action_information4 <>'P2'
3114 AND action_information29 IN ('1','3')
3115 GROUP BY substr(action_information4 ,1,1);
3116
3117 /* Cursor to fetch data related to Finnish Residents Payment Type P2*/
3118 CURSOR csr_arch_fi_p2 (p_payroll_action_id NUMBER) IS
3119 SELECT substr(action_information4,1,1) payment_type , SUM(nvl(action_information9,0)) payment , SUM(nvl(action_information10,0)) tax , COUNT(*) num
3120 ,SUM(nvl(action_information12,0)) pretax_ded, SUM(nvl(action_information11,0)) pen_unemp_ins , SUM(nvl(action_information25,0)) bik
3121 , SUM(nvl(action_information27,0) + nvl(action_information23,0)) CODE_670 ,SUM(nvl(action_information28,0)) CODE_631
3122 FROM pay_action_information pai , pay_assignment_actions paa
3123 WHERE pai.action_information_category = 'EMEA REPORT INFORMATION'
3124 AND pai.action_context_type= 'AAP'
3125 AND pai.action_context_id= paa.assignment_action_id
3126 AND paa.payroll_action_id = p_payroll_action_id
3127 AND action_information3 ='VSPSERIE'
3128 AND action_information4 ='P2'
3129 AND action_information29 ='1'
3130 GROUP BY substr(action_information4 ,1,1);
3131
3132 rg_csr_arch_fi_p2 csr_arch_fi_p2%rowtype;
3133
3134
3135
3136 /* Cursor to fetch data related to Foreign Residents Social Security Not Liable*/
3137 CURSOR csr_arch_fr_nss ( p_payroll_action_id NUMBER) IS
3138 SELECT SUM(nvl(action_information15,0)) payment, SUM(nvl(action_information16,0)) tax , COUNT(*) num,
3139 SUM(nvl(action_information19,0)) pretax_ded, SUM(nvl(action_information18,0)) bik
3140 FROM pay_action_information pai , pay_assignment_actions paa
3141 WHERE pai.action_information_category = 'EMEA REPORT INFORMATION'
3142 AND pai.action_context_type= 'AAP'
3143 AND pai.action_context_id= paa.assignment_action_id
3144 AND paa.payroll_action_id = p_payroll_action_id
3145 AND action_information3 ='VSRAERIE'
3146 AND NVL(action_information23,0) < 1
3147 GROUP BY action_information3;
3148
3149
3150 /* Cursor to fetch data related to Foreign Residents Social Security liable*/
3151 CURSOR csr_arch_fr_ss ( p_payroll_action_id NUMBER) IS
3152 SELECT SUM(nvl(action_information15,0)) payment, SUM(nvl(action_information16,0)) tax , COUNT(*) num,
3153 SUM(nvl(action_information19,0)) pretax_ded, SUM(nvl(action_information18,0)) bik
3154 FROM pay_action_information pai , pay_assignment_actions paa
3155 WHERE pai.action_information_category = 'EMEA REPORT INFORMATION'
3156 AND pai.action_context_type= 'AAP'
3157 AND pai.action_context_id= paa.assignment_action_id
3158 AND paa.payroll_action_id = p_payroll_action_id
3159 AND action_information3 ='VSRAERIE'
3160 AND NVL(action_information23,0) > 0
3161 GROUP BY action_information3;
3162
3163 /* Cursor to fetch data related to Record VSPSERIE*/
3164 CURSOR csr_VSPSERIE (p_payroll_action_id NUMBER) IS
3165 SELECT pai.action_information30
3166 FROM pay_action_information pai , pay_assignment_actions paa
3167 WHERE paa.payroll_action_id = p_payroll_action_id
3168 AND pai.action_context_id= paa.assignment_action_id
3169 AND pai.action_context_type= 'AAP'
3170 AND pai.action_information_category = 'EMEA REPORT INFORMATION'
3171 AND pai.action_information1 = 'PYFIDPSA'
3172 AND pai.action_information3 ='VSPSERIE'
3173 AND action_information29 IN ('1','3')
3174 ORDER BY pai.action_information2 , action_information29 , action_information15
3175 FOR UPDATE OF pai.action_information30;
3176
3177 /* Cursor to fetch data related to Record VSRAERIE*/
3178 CURSOR csr_VSRAERIE(p_payroll_action_id NUMBER) IS
3179 SELECT pai.action_information30
3180 FROM pay_action_information pai , pay_assignment_actions paa
3181 WHERE paa.payroll_action_id = p_payroll_action_id
3182 AND pai.action_context_id= paa.assignment_action_id
3183 AND pai.action_context_type= 'AAP'
3184 AND pai.action_information_category = 'EMEA REPORT INFORMATION'
3185 AND pai.action_information1 = 'PYFIDPSA'
3186 AND pai.action_information3 ='VSRAERIE'
3187 ORDER BY pai.action_information2
3188 FOR UPDATE OF pai.action_information30;
3189
3190 /* Cursor to fetch data related to Record VSPSVYSL*/
3191 CURSOR csr_VSPSVYSL(p_payroll_action_id NUMBER) IS
3192 SELECT *
3193 FROM pay_action_information pai
3194 WHERE pai.action_context_id= p_payroll_action_id
3195 AND pai.action_context_type= 'PA'
3196 AND pai.action_information_category = 'EMEA REPORT INFORMATION'
3197 AND pai.action_information1 = 'PYFIDPSA'
3198 AND pai.action_information2 = 'VSPSVYSL'
3199 ORDER BY pai.action_information4
3200 FOR UPDATE OF pai.action_information30;
3201
3202 rg_chk_csr_VSPSVYSL csr_VSPSVYSL%rowtype;
3203
3204 /* Cursor to fetch data related to Record VSPSVYHT*/
3205 CURSOR csr_VSPSVYHT(p_payroll_action_id NUMBER) IS
3206 SELECT *
3207 FROM pay_action_information pai
3208 WHERE pai.action_context_id= p_payroll_action_id
3209 AND pai.action_context_type= 'PA'
3210 AND pai.action_information_category = 'EMEA REPORT INFORMATION'
3211 AND pai.action_information1 = 'PYFIDPSA'
3212 AND pai.action_information2 = 'VSPSVYHT'
3213 FOR UPDATE OF pai.action_information30;
3214
3215 rg_chk_csr_VSPSVYHT csr_VSPSVYHT%rowtype;
3216
3217 /* Cursor to fetch data related to Record VSPSTUKI*/
3218 CURSOR csr_VSPSTUKI(p_payroll_action_id NUMBER) IS
3219 SELECT pai.action_information30
3220 FROM pay_action_information pai , pay_assignment_actions paa
3221 WHERE paa.payroll_action_id = p_payroll_action_id
3222 AND pai.action_context_id= paa.assignment_action_id
3223 AND pai.action_context_type= 'AAP'
3224 AND pai.action_information_category = 'EMEA REPORT INFORMATION'
3225 AND pai.action_information1 = 'PYFIDPSA'
3226 AND pai.action_information3 = 'VSPSTUKI'
3227 ORDER BY pai.action_information2
3228 FOR UPDATE OF pai.action_information30;
3229
3230 rg_chk_csr_VSPSTUKI csr_VSPSTUKI%rowtype;
3231
3232
3233 l_tot_count NUMBER;
3234 l_tot_bik NUMBER;
3235 l_tot_pretax_ded NUMBER;
3236 l_tot_pen_unemp_ins NUMBER;
3237
3238 l_tot_payment NUMBER;
3239 l_payment_status NUMBER;
3240 l_action_info_id NUMBER;
3241 l_ovn NUMBER;
3242 l_end_code NUMBER;
3243 l_vspsvysl_status NUMBER;
3244 l_tot_631 NUMBER;
3245 l_tot_670 NUMBER;
3246
3247
3248
3249 BEGIN
3250 -- fnd_file.put_line(fnd_file.log,'DEINITIALIZATION_CODE 1');
3251 /*Initializing the variables*/
3252
3253 l_tot_count :=0;
3254 l_tot_bik :=0;
3255 l_tot_pretax_ded :=0;
3256 l_tot_pen_unemp_ins :=0;
3257 l_tot_payment :=0;
3258 l_payment_status :=0;
3259 l_vspsvysl_status :=0;
3260 l_tot_631 :=0;
3261 l_tot_670 :=0;
3262
3263 IF g_debug THEN
3264 hr_utility.set_location(' Entering Procedure DEINITIALIZATION_CODE',380);
3265 END IF;
3266
3267 /* Fetching data from the Header record(Employer level) */
3268 OPEN csr_prpt_header(p_payroll_action_id);
3269 FETCH csr_prpt_header INTO rg_csr_prpt_header;
3270 CLOSE csr_prpt_header;
3271
3272 PAY_FI_ARCHIVE_DPSA.GET_ALL_PARAMETERS(
3273 p_payroll_action_id
3274 ,g_business_group_id
3275 ,g_legal_employer_id
3276 ,g_local_unit_id
3277 ,g_year
3278 ,g_transact_type
3279 ,g_deduction_ss
3280 ,g_effective_date
3281 ,g_archive ) ;
3282
3283 g_emp_type:= rg_csr_prpt_header.emp_type;
3284 IF g_transact_type IN ( 1 , 2) THEN
3285 g_transact_type := 2;
3286 END IF;
3287
3288 IF g_archive='Y' THEN
3289 -- fnd_file.put_line(fnd_file.log,'DEINITIALIZATION_CODE 3');
3290
3291 FOR rg_csr_arch_fi IN csr_arch_fi(p_payroll_action_id)
3292 LOOP
3293 -- fnd_file.put_line(fnd_file.log,'DEINITIALIZATION_CODE 4');
3294
3295
3296 IF rg_csr_arch_fi.payment_type ='H' THEN
3297 OPEN csr_arch_fi_p2(p_payroll_action_id);
3298 FETCH csr_arch_fi_p2 INTO rg_csr_arch_fi_p2;
3299 CLOSE csr_arch_fi_p2;
3300
3301 -- fnd_file.put_line(fnd_file.log,'DEINITIALIZATION_CODE 5');
3302 pay_action_information_api.create_action_information (
3303 p_action_information_id=> l_action_info_id,
3304 p_action_context_id=> p_payroll_action_id,
3305 p_action_context_type=> 'PA',
3306 p_object_version_number=> l_ovn,
3307 p_effective_date=> g_effective_date,
3308 p_source_id=> NULL,
3309 p_source_text=> NULL,
3310 p_action_information_category=> 'EMEA REPORT INFORMATION',
3311 p_action_information1=> 'PYFIDPSA',
3312 p_action_information2=> 'VSPSVYSL',
3313 p_action_information3=>g_transact_type,
3314 p_action_information4=> rg_csr_arch_fi.payment_type ,
3315 p_action_information5=> g_year,
3316 p_action_information6=> rg_csr_prpt_header.business_id,
3317 p_action_information7=> FND_NUMBER.NUMBER_TO_CANONICAL(rg_csr_arch_fi.payment + nvl(rg_csr_arch_fi_p2.payment,0) ),
3318 p_action_information8=> FND_NUMBER.NUMBER_TO_CANONICAL(rg_csr_arch_fi.tax + nvl(rg_csr_arch_fi_p2.tax,0)) ,
3319 p_action_information9=> FND_NUMBER.NUMBER_TO_CANONICAL(rg_csr_arch_fi.num + nvl(rg_csr_arch_fi_p2.num,0)),
3320 p_action_information10=> FND_NUMBER.NUMBER_TO_CANONICAL(rg_csr_arch_fi.CODE_670 + nvl(rg_csr_arch_fi_p2.CODE_670,0) ),
3321 p_action_information11=> FND_NUMBER.NUMBER_TO_CANONICAL(rg_csr_arch_fi.CODE_631 + nvl(rg_csr_arch_fi_p2.CODE_631,0) ),
3322 p_action_information12=> NULL,
3323 p_action_information13=> NULL,
3324 p_action_information14=> NULL,
3325 p_action_information15=> NULL,
3326 p_action_information16=> NULL,
3327 p_action_information17=> NULL,
3328 p_action_information18=> NULL,
3329 p_action_information19=> NULL,
3330 p_action_information20=> NULL,
3331 p_action_information21=> NULL,
3332 p_action_information22=> NULL,
3333 p_action_information23=> NULL,
3334 p_action_information24=> NULL,
3335 p_action_information25=> NULL,
3336 p_action_information26=> NULL,
3337 p_action_information27=> NULL,
3338 p_action_information28=> NULL,
3339 p_action_information29=> NULL,
3340 p_action_information30=> NULL );
3341
3342 l_tot_count := l_tot_count + 1 ;
3343 l_tot_bik := nvl(rg_csr_arch_fi.bik,0) + nvl(rg_csr_arch_fi_p2.bik,0) + l_tot_bik;
3344 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;
3345 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;
3346 l_tot_payment:= nvl(rg_csr_arch_fi.payment,0) + nvl(rg_csr_arch_fi_p2.payment,0) + l_tot_payment;
3347 l_tot_631:= nvl(rg_csr_arch_fi.code_631,0) + nvl(rg_csr_arch_fi_p2.code_631,0) + l_tot_631;
3348 l_tot_670:= nvl(rg_csr_arch_fi.code_670,0) + nvl(rg_csr_arch_fi_p2.code_670,0) + l_tot_670;
3349
3350 -- fnd_file.put_line(fnd_file.log,'DEINITIALIZATION_CODE 6');
3351 ELSE
3352 pay_action_information_api.create_action_information (
3353 p_action_information_id=> l_action_info_id,
3354 p_action_context_id=> p_payroll_action_id,
3355 p_action_context_type=> 'PA',
3356 p_object_version_number=> l_ovn,
3357 p_effective_date=> g_effective_date,
3358 p_source_id=> NULL,
3359 p_source_text=> NULL,
3360 p_action_information_category=> 'EMEA REPORT INFORMATION',
3361 p_action_information1=> 'PYFIDPSA',
3362 p_action_information2=> 'VSPSVYSL',
3363 p_action_information3=>g_transact_type,
3364 p_action_information4=> rg_csr_arch_fi.payment_type ,
3365 p_action_information5=> g_year,
3366 p_action_information6=> rg_csr_prpt_header.business_id,
3367 p_action_information7=> FND_NUMBER.NUMBER_TO_CANONICAL(nvl(rg_csr_arch_fi.payment ,0) ),
3368 p_action_information8=> FND_NUMBER.NUMBER_TO_CANONICAL(nvl(rg_csr_arch_fi.tax,0) ) ,
3369 p_action_information9=> FND_NUMBER.NUMBER_TO_CANONICAL(nvl(rg_csr_arch_fi.num,0)) ,
3370 p_action_information10=> FND_NUMBER.NUMBER_TO_CANONICAL(nvl(rg_csr_arch_fi.CODE_670,0)) ,
3371 p_action_information11=> FND_NUMBER.NUMBER_TO_CANONICAL(nvl(rg_csr_arch_fi.CODE_631,0)) ,
3372 p_action_information12=> NULL,
3373 p_action_information13=> NULL,
3374 p_action_information14=> NULL,
3375 p_action_information15=> NULL,
3376 p_action_information16=> NULL,
3377 p_action_information17=> NULL,
3378 p_action_information18=> NULL,
3379 p_action_information19=> NULL,
3380 p_action_information20=> NULL,
3381 p_action_information21=> NULL,
3382 p_action_information22=> NULL,
3383 p_action_information23=> NULL,
3384 p_action_information24=> NULL,
3385 p_action_information25=> NULL,
3386 p_action_information26=> NULL,
3387 p_action_information27=> NULL,
3388 p_action_information28=> NULL,
3389 p_action_information29=> NULL,
3390 p_action_information30=> NULL
3391 );
3392
3393 l_tot_count := l_tot_count + 1 ;
3394 l_tot_bik := nvl(rg_csr_arch_fi.bik,0) + l_tot_bik;
3395 l_tot_pretax_ded:= nvl(rg_csr_arch_fi.pretax_ded,0) + l_tot_pretax_ded;
3396 l_tot_pen_unemp_ins:= nvl(rg_csr_arch_fi.pen_unemp_ins,0) + l_tot_pen_unemp_ins;
3397 l_tot_payment:= nvl(rg_csr_arch_fi.payment,0) + l_tot_payment;
3398 l_tot_631:= nvl(rg_csr_arch_fi.code_631,0) + l_tot_631;
3399 l_tot_670:= nvl(rg_csr_arch_fi.code_670,0) + l_tot_670;
3400 -- fnd_file.put_line(fnd_file.log,'DEINITIALIZATION_CODE 6');
3401
3402 END IF;
3403 END LOOP;
3404 -- fnd_file.put_line(fnd_file.log,'DEINITIALIZATION_CODE 7');
3405 FOR rg_csr_arch_fr_ss IN csr_arch_fr_ss(p_payroll_action_id)
3406 LOOP
3407 -- fnd_file.put_line(fnd_file.log,'DEINITIALIZATION_CODE 8');
3408 pay_action_information_api.create_action_information (
3409 p_action_information_id=> l_action_info_id,
3410 p_action_context_id=> p_payroll_action_id,
3411 p_action_context_type=> 'PA',
3412 p_object_version_number=> l_ovn,
3413 p_effective_date=> g_effective_date,
3414 p_source_id=> NULL,
3415 p_source_text=> NULL,
3416 p_action_information_category=> 'EMEA REPORT INFORMATION',
3417 p_action_information1=> 'PYFIDPSA',
3418 p_action_information2=> 'VSPSVYSL',
3419 p_action_information3=>g_transact_type,
3420 p_action_information4=> '14' ,
3421 p_action_information5=> g_year,
3422 p_action_information6=> rg_csr_prpt_header.business_id,
3423 p_action_information7=> FND_NUMBER.NUMBER_TO_CANONICAL(nvl(rg_csr_arch_fr_ss.payment,0)),
3424 p_action_information8=> FND_NUMBER.NUMBER_TO_CANONICAL(nvl(rg_csr_arch_fr_ss.tax,0)) ,
3425 p_action_information9=> FND_NUMBER.NUMBER_TO_CANONICAL(nvl(rg_csr_arch_fr_ss.num,0)),
3426 p_action_information10=> NULL,
3427 p_action_information11=> NULL,
3428 p_action_information12=> NULL,
3429 p_action_information13=> NULL,
3430 p_action_information14=> NULL,
3431 p_action_information15=> NULL,
3432 p_action_information16=> NULL,
3433 p_action_information17=> NULL,
3434 p_action_information18=> NULL,
3435 p_action_information19=> NULL,
3436 p_action_information20=> NULL,
3437 p_action_information21=> NULL,
3438 p_action_information22=> NULL,
3439 p_action_information23=> NULL,
3440 p_action_information24=> NULL,
3441 p_action_information25=> NULL,
3442 p_action_information26=> NULL,
3443 p_action_information27=> NULL,
3444 p_action_information28=> NULL,
3445 p_action_information29=> NULL,
3446 p_action_information30=> NULL
3447 );
3448
3449 l_tot_count := l_tot_count + 1;
3450 l_tot_bik := nvl(rg_csr_arch_fr_ss.bik,0) + l_tot_bik;
3451 l_tot_pretax_ded:= nvl(rg_csr_arch_fr_ss.pretax_ded,0) + l_tot_pretax_ded;
3452 l_tot_payment:= nvl(rg_csr_arch_fr_ss.payment,0) + l_tot_payment;
3453 -- fnd_file.put_line(fnd_file.log,'DEINITIALIZATION_CODE 9');
3454
3455 END LOOP;
3456 -- fnd_file.put_line(fnd_file.log,'DEINITIALIZATION_CODE 10');
3457 FOR rg_csr_arch_fr_nss IN csr_arch_fr_nss(p_payroll_action_id)
3458 LOOP
3459 -- fnd_file.put_line(fnd_file.log,'DEINITIALIZATION_CODE 11');
3460 pay_action_information_api.create_action_information (
3461 p_action_information_id=> l_action_info_id,
3462 p_action_context_id=> p_payroll_action_id,
3463 p_action_context_type=> 'PA',
3464 p_object_version_number=> l_ovn,
3465 p_effective_date=> g_effective_date,
3466 p_source_id=> NULL,
3467 p_source_text=> NULL,
3468 p_action_information_category=> 'EMEA REPORT INFORMATION',
3469 p_action_information1=> 'PYFIDPSA',
3470 p_action_information2=> 'VSPSVYSL',
3471 p_action_information3=>g_transact_type,
3472 p_action_information4=> '22',
3473 p_action_information5=> g_year,
3474 p_action_information6=> rg_csr_prpt_header.business_id,
3475 p_action_information7=> FND_NUMBER.NUMBER_TO_CANONICAL(nvl(rg_csr_arch_fr_nss.payment,0)),
3476 p_action_information8=> FND_NUMBER.NUMBER_TO_CANONICAL(nvl(rg_csr_arch_fr_nss.tax,0)) ,
3477 p_action_information9=> FND_NUMBER.NUMBER_TO_CANONICAL(nvl(rg_csr_arch_fr_nss.num,0)),
3478 p_action_information10=> NULL,
3479 p_action_information11=> NULL,
3480 p_action_information12=> NULL,
3481 p_action_information13=> NULL,
3482 p_action_information14=> NULL,
3483 p_action_information15=> NULL,
3484 p_action_information16=> NULL,
3485 p_action_information17=> NULL,
3486 p_action_information18=> NULL,
3487 p_action_information19=> NULL,
3488 p_action_information20=> NULL,
3489 p_action_information21=> NULL,
3490 p_action_information22=> NULL,
3491 p_action_information23=> NULL,
3492 p_action_information24=> NULL,
3493 p_action_information25=> NULL,
3494 p_action_information26=> NULL,
3495 p_action_information27=> NULL,
3496 p_action_information28=> NULL,
3497 p_action_information29=> NULL,
3498 p_action_information30=> NULL
3499 );
3500
3501 l_tot_count := l_tot_count + 1;
3502 l_tot_bik := nvl(rg_csr_arch_fr_nss.bik,0) + l_tot_bik;
3503 l_tot_pretax_ded:= nvl(rg_csr_arch_fr_nss.pretax_ded,0) + l_tot_pretax_ded;
3504 l_tot_payment:= nvl(rg_csr_arch_fr_nss.payment ,0) + l_tot_payment;
3505 -- fnd_file.put_line(fnd_file.log,'DEINITIALIZATION_CODE 11');
3506 END LOOP;
3507
3508 IF l_tot_payment = 0 THEN
3509 l_payment_status:=1;
3510 END IF;
3511
3512 OPEN csr_VSPSVYSL(p_payroll_action_id );
3513 FETCH csr_VSPSVYSL INTO rg_chk_csr_VSPSVYSL;
3514 IF csr_VSPSVYSL%FOUND THEN
3515 l_vspsvysl_status:=1;
3516 END IF;
3517 CLOSE csr_VSPSVYSL;
3518
3519 IF l_vspsvysl_status=1 THEN
3520
3521 IF g_deduction_ss IS NOT NULL THEN
3522 l_tot_631:= g_deduction_ss*100;
3523 END IF;
3524
3525 -- fnd_file.put_line(fnd_file.log,'DEINITIALIZATION_CODE 12');
3526 pay_action_information_api.create_action_information (
3527 p_action_information_id=> l_action_info_id,
3528 p_action_context_id=> p_payroll_action_id,
3529 p_action_context_type=> 'PA',
3530 p_object_version_number=> l_ovn,
3531 p_effective_date=> g_effective_date,
3532 p_source_id=> NULL,
3533 p_source_text=> NULL,
3534 p_action_information_category=> 'EMEA REPORT INFORMATION',
3535 p_action_information1=> 'PYFIDPSA',
3536 p_action_information2=> 'VSPSVYHT',
3537 p_action_information3=>g_transact_type,
3538 p_action_information4=> g_year,
3539 p_action_information5=> rg_csr_prpt_header.business_id,
3540 p_action_information6=> rg_csr_prpt_header.contact_person,
3541 p_action_information7=> rg_csr_prpt_header.phone,
3542 p_action_information8=> FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_tot_count,0)),
3543 p_action_information9=> FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_tot_bik,0)),
3544 p_action_information10=> FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_tot_pen_unemp_ins,0)),
3545 p_action_information11=> FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_tot_pretax_ded,0)),
3546 p_action_information12=> l_payment_status,
3547 p_action_information13=> FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_tot_631,0)),
3548 p_action_information14=> FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_tot_670,0)),
3549 p_action_information15=> NULL,
3550 p_action_information16=> NULL,
3551 p_action_information17=> NULL,
3552 p_action_information18=> NULL,
3553 p_action_information19=> NULL,
3554 p_action_information20=> NULL,
3555 p_action_information21=> NULL,
3556 p_action_information22=> NULL,
3557 p_action_information23=> NULL,
3558 p_action_information24=> NULL,
3559 p_action_information25=> NULL,
3560 p_action_information26=> NULL,
3561 p_action_information27=> NULL,
3562 p_action_information28=> NULL,
3563 p_action_information29=> NULL,
3564 p_action_information30=> NULL
3565 );
3566
3567 -- fnd_file.put_line(fnd_file.log,'DEINITIALIZATION_CODE 14');
3568 END IF;
3569
3570 l_end_code:=0 ;
3571
3572 FOR rg_csr_VSPSERIE IN csr_VSPSERIE(p_payroll_action_id )
3573 LOOP
3574 l_end_code:=l_end_code + 1 ;
3575 UPDATE pay_action_information pai
3576 SET pai.action_information30 =l_end_code
3577 WHERE CURRENT OF csr_VSPSERIE;
3578 END LOOP;
3579
3580 FOR rg_csr_VSRAERIE IN csr_VSRAERIE(p_payroll_action_id )
3581 LOOP
3582 l_end_code:=l_end_code + 1 ;
3583 UPDATE pay_action_information pai
3584 SET pai.action_information30 =l_end_code
3585 WHERE CURRENT OF csr_VSRAERIE;
3586 END LOOP;
3587
3588 FOR rg_csr_VSPSTUKI IN csr_VSPSTUKI(p_payroll_action_id )
3589 LOOP
3590 l_end_code:=l_end_code + 1 ;
3591 UPDATE pay_action_information
3592 SET action_information30 =l_end_code
3593 WHERE CURRENT OF csr_VSPSTUKI;
3594 END LOOP;
3595
3596 FOR rg_csr_VSPSVYSL IN csr_VSPSVYSL(p_payroll_action_id )
3597 LOOP
3598 l_end_code:=l_end_code + 1 ;
3599 UPDATE pay_action_information
3600 SET action_information30 =l_end_code
3601 WHERE CURRENT OF csr_VSPSVYSL;
3602 END LOOP;
3603
3604 FOR rg_csr_VSPSVYHT IN csr_VSPSVYHT(p_payroll_action_id )
3605 LOOP
3606 l_end_code:=l_end_code + 1 ;
3607 UPDATE pay_action_information
3608 SET action_information30 =l_end_code
3609 WHERE CURRENT OF csr_VSPSVYHT;
3610 END LOOP;
3611
3612
3613
3614
3615
3616 END IF;---ARCHIVE=YES
3617
3618 IF g_debug THEN
3619 hr_utility.set_location(' Leaving Procedure DEINITIALIZATION_CODE',390);
3620 END IF;
3621
3622 EXCEPTION
3623 WHEN others THEN
3624 IF g_debug THEN
3625 hr_utility.set_location('error raised in DEINITIALIZATION_CODE ',5);
3626 END if;
3627 RAISE;
3628 END;
3629
3630 BEGIN
3631
3632 g_payroll_action_id :=NULL;
3633 g_le_assignment_action_id :=NULL;
3634 g_lu_assignment_action_id :=NULL;
3635 g_emp_type :=NULL;
3636 g_business_group_id :=NULL;
3637 g_legal_employer_id :=NULL;
3638 g_local_unit_id :=NULL;
3639 g_year :=NULL;
3640 g_transact_type :=NULL;
3641 g_deduction_ss :=NULL;
3642 g_effective_date :=NULL;
3643 g_archive :=NULL;
3644
3645 END PAY_FI_ARCHIVE_DPSA;