[Home] [Help]
PACKAGE BODY: APPS.PAY_FI_ARCHIVE_LTFA
Source
1 PACKAGE BODY PAY_FI_ARCHIVE_LTFA AS
2 /* $Header: pyfiltfa.pkb 120.4.12000000.4 2007/03/20 05:39:29 dbehera noship $ */
3
4 TYPE lock_rec IS RECORD (
5 archive_assact_id NUMBER);
6
7 TYPE lock_table IS TABLE OF lock_rec INDEX BY BINARY_INTEGER;
8
9 g_debug boolean := hr_utility.debug_enabled;
10 g_lock_table lock_table;
11 g_index NUMBER := -1;
12 g_index_assact NUMBER := -1;
13 g_index_bal NUMBER := -1;
14 g_package VARCHAR2(33) := ' PAY_FI_ARCHIVE_LTFA.';
15 g_archive VARCHAR2(1);
16 g_emp_type VARCHAR2(2);
17 g_legal_employer_id NUMBER;
18 g_local_unit_id NUMBER ;
19 g_effective_date DATE;
20 g_pension_provider hr_organization_units.organization_id%TYPE ;
21 g_pension_ins_num hr_organization_information.org_information1%TYPE ;
22 g_payroll_action_id NUMBER ;
23 g_le_assignment_action_id NUMBER ;
24 g_lu_assignment_action_id NUMBER ;
25
26
27
28 /* GET PARAMETER */
29 FUNCTION GET_PARAMETER(
30 p_parameter_string IN VARCHAR2
31 ,p_token IN VARCHAR2
32 ,p_segment_number IN NUMBER default NULL ) RETURN VARCHAR2
33 IS
34 l_parameter pay_payroll_actions.legislative_parameters%TYPE:=NULL;
35 l_start_pos NUMBER;
36 l_delimiter VARCHAR2(1):=' ';
37 l_proc VARCHAR2(240):= g_package||' get parameter ';
38 BEGIN
39 --
40 fnd_file.put_line(fnd_file.log,'Range Code 23'||p_token);
41 IF g_debug THEN
42 hr_utility.set_location(' Entering Function GET_PARAMETER',10);
43 END IF;
44 l_start_pos := instr(' '||p_parameter_string,l_delimiter||p_token||'=');
45 --
46 IF l_start_pos = 0 THEN
47 l_delimiter := '|';
48 l_start_pos := instr(' '||p_parameter_string,l_delimiter||p_token||'=');
49 END IF;
50
51 IF l_start_pos <> 0 THEN
52 l_start_pos := l_start_pos + length(p_token||'=');
53 l_parameter := substr(p_parameter_string,
54 l_start_pos,
55 instr(p_parameter_string||' ',
56 l_delimiter,l_start_pos)
57 - l_start_pos);
58 IF p_segment_number IS NOT NULL THEN
59 l_parameter := ':'||l_parameter||':';
60 l_parameter := substr(l_parameter,
61 instr(l_parameter,':',1,p_segment_number)+1,
62 instr(l_parameter,':',1,p_segment_number+1) -1
63 - instr(l_parameter,':',1,p_segment_number));
64 END IF;
65 END IF;
66 --
67 IF g_debug THEN
68 hr_utility.set_location(' Leaving Function GET_PARAMETER',20);
69 END IF;
70
71 --fnd_file.put_line(fnd_file.log,'Range Code 24');
72 RETURN l_parameter;
73
74 END;
75
76 /* GET ALL PARAMETERS */
77 PROCEDURE GET_ALL_PARAMETERS(
78 p_payroll_action_id IN NUMBER
79 ,p_business_group_id OUT NOCOPY NUMBER
80 ,p_pension_ins_num OUT NOCOPY VARCHAR2
81 ,p_legal_employer_id OUT NOCOPY NUMBER
82 ,p_local_unit_id OUT NOCOPY NUMBER
83 ,p_month OUT NOCOPY VARCHAR2
84 ,p_year OUT NOCOPY VARCHAR2
85 ,p_effective_date OUT NOCOPY DATE
86 ,p_archive OUT NOCOPY VARCHAR2
87 ) IS
88
89 CURSOR csr_parameter_info(p_payroll_action_id NUMBER) IS
90 SELECT PAY_FI_ARCHIVE_LTFA.GET_PARAMETER(legislative_parameters,'LEGAL_EMPLOYER_NAME')
91 ,PAY_FI_ARCHIVE_LTFA.GET_PARAMETER(legislative_parameters,'LOCAL_UNIT_NAME')
92 ,PAY_FI_ARCHIVE_LTFA.GET_PARAMETER(legislative_parameters,'PENSION_INS_NUM')
93 ,PAY_FI_ARCHIVE_LTFA.GET_PARAMETER(legislative_parameters,'MONTH_RPT')
94 ,PAY_FI_ARCHIVE_LTFA.GET_PARAMETER(legislative_parameters,'YEAR_RPT')
95 ,PAY_FI_ARCHIVE_LTFA.GET_PARAMETER(legislative_parameters,'ARCHIVE')
96 ,effective_date
97 ,business_group_id
98 FROM pay_payroll_actions
99 WHERE payroll_action_id = p_payroll_action_id;
100 l_proc VARCHAR2(240):= g_package||' GET_ALL_PARAMETERS ';
101 --
102 BEGIN
103
104 --fnd_file.put_line(fnd_file.log,'Range Code 21'||TO_CHAR(p_payroll_action_id));
105
106 OPEN csr_parameter_info (p_payroll_action_id);
107
108 FETCH csr_parameter_info
109 INTO p_legal_employer_id
110 ,p_local_unit_id
111 ,p_pension_ins_num
112 ,p_month
113 ,p_year
114 ,p_archive
115 ,p_effective_date
116 ,p_business_group_id;
117 CLOSE csr_parameter_info;
118
119 --fnd_file.put_line(fnd_file.log,'Range Code 22');
120 --
121 IF g_debug THEN
122 hr_utility.set_location(' Leaving Procedure GET_ALL_PARAMETERS',30);
123 END IF;
124 --fnd_file.put_line(fnd_file.log,'Range Code 22222');
125 END GET_ALL_PARAMETERS;
126
127 /* RANGE CODE */
128 PROCEDURE RANGE_CODE (p_payroll_action_id IN NUMBER
129 ,p_sql OUT NOCOPY VARCHAR2)
130 IS
131
132
133 l_action_info_id NUMBER;
134 l_ovn NUMBER;
135 l_start_date VARCHAR2(30);
136 l_end_date VARCHAR2(30);
137 l_defined_balance_id NUMBER := 0;
138 l_count NUMBER := 0;
139 l_prev_prepay NUMBER := 0;
140 l_prepay_action_id NUMBER;
141 l_actid NUMBER;
142 l_assignment_id NUMBER;
143 l_action_sequence NUMBER;
144 l_assact_id NUMBER;
145 l_pact_id NUMBER;
146 l_flag NUMBER := 0;
147 l_element_context VARCHAR2(5);
148 l_business_group_id NUMBER;
149 l_month VARCHAR2(2);
150 l_year VARCHAR2(4);
151 l_canonical_start_date DATE;
152 l_canonical_end_date DATE;
153 l_emp_type VARCHAR2(2);
154 l_emp_id hr_organization_units.organization_id%TYPE ;
155 l_customer_num hr_organization_information.org_information1%TYPE ;
156 l_pension_ins_num hr_organization_information.org_information1%TYPE ;
157 l_dept_code VARCHAR2(3);
158
159 l_Record_code VARCHAR2(240) :=' ';
160 l_pp_name hr_organization_units.name%TYPE ;
161 l_emp_name hr_organization_units.name%TYPE ;
162 l_le_name hr_organization_units.name%TYPE ;
163 l_lu_name hr_organization_units.name%TYPE ;
164 l_business_id hr_organization_information.org_information1%TYPE ;
165 l_y_number hr_organization_information.org_information1%TYPE ;
166 l_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE;
167 l_counter number := 0;
168
169
170 /* Cursors */
171
172
173 CURSOR csr_pension_provider_details ( csr_v_pension_provider_id hr_organization_information.organization_id%TYPE
174 )
175 IS
176 SELECT o1.NAME
177 FROM hr_organization_units o1
178 , hr_organization_information hoi1
179 WHERE o1.business_group_id =l_business_group_id
180 AND hoi1.organization_id = o1.organization_id
181 AND hoi1.organization_id = csr_v_pension_provider_id
182 AND hoi1.org_information_context = 'CLASS'
183 AND hoi1.org_information1 = 'FR_PENSION' ;
184
185 rg_pension_provider_details csr_pension_provider_details%ROWTYPE;
186
187
188
189 Cursor csr_Local_Unit_Details ( csr_v_local_unit_id hr_organization_information.ORGANIZATION_ID%TYPE)
190 IS
191 SELECT o1.name , hoi2.ORG_INFORMATION1
192 FROM hr_organization_units o1
193 , hr_organization_information hoi1
194 , hr_organization_information hoi2
195 WHERE o1.business_group_id =l_business_group_id
196 AND hoi1.organization_id = o1.organization_id
197 AND hoi1.organization_id = csr_v_local_unit_id
198 AND hoi1.org_information1 = 'FI_LOCAL_UNIT'
199 AND hoi1.org_information_context = 'CLASS'
200 AND o1.organization_id =hoi2.organization_id
201 AND hoi2.ORG_INFORMATION_CONTEXT='FI_LOCAL_UNIT_DETAILS';
202
203 rg_Local_Unit_Details csr_Local_Unit_Details%rowtype;
204
205 Cursor csr_Legal_Emp_Details
206 ( csr_v_legal_emp_id hr_organization_information.ORGANIZATION_ID%TYPE
207 , csr_v_pension_ins_num hr_organization_information.ORG_INFORMATION1%TYPE
208 , csr_v_effective_date DATE )
209 IS
210 SELECT o1.name ,hoi2.ORG_INFORMATION1 , hoi3.ORG_INFORMATION4 , hoi3.ORG_INFORMATION6, hoi3.ORG_INFORMATION8
211 FROM hr_organization_units o1
212 , hr_organization_information hoi1
213 , hr_organization_information hoi2
214 , hr_organization_information hoi3
215 WHERE o1.business_group_id =l_business_group_id
216 AND hoi1.organization_id = o1.organization_id
217 AND hoi1.organization_id = csr_v_legal_emp_id
218 AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
219 AND hoi1.org_information_context = 'CLASS'
220 AND o1.organization_id =hoi2.organization_id
221 AND hoi2.ORG_INFORMATION_CONTEXT='FI_LEGAL_EMPLOYER_DETAILS'
222 AND o1.organization_id =hoi3.organization_id
223 AND hoi3.ORG_INFORMATION_CONTEXT='FI_PENSION_PROVIDERS'
224 AND hoi3.org_information6=csr_v_pension_ins_num
225 AND csr_v_effective_date BETWEEN fnd_date.canonical_to_date(hoi3.org_information1) AND
226 nvl(fnd_date.canonical_to_date(hoi3.org_information2),to_date('31/12/4712','DD/MM/YYYY')) ;
227
228 rg_Legal_Emp_Details csr_Legal_Emp_Details%rowtype;
229 /*
230 Cursor csr_lu_pp_dtls ( csr_v_local_unit_id hr_organization_information.ORGANIZATION_ID%TYPE
231 , csr_v_pension_ins_num hr_organization_information.org_information1%TYPE)
232 IS
233 SELECT hoi2.ORG_INFORMATION2
234 FROM hr_organization_units o1
235 , hr_organization_information hoi1
236 , hr_organization_information hoi2
237 WHERE o1.business_group_id =l_business_group_id
238 AND hoi1.organization_id = o1.organization_id
239 AND hoi1.organization_id = csr_v_local_unit_id
240 AND hoi1.org_information1 = 'FI_LOCAL_UNIT'
241 AND hoi1.org_information_context = 'CLASS'
242 AND o1.organization_id =hoi2.organization_id
243 AND hoi2.ORG_INFORMATION_CONTEXT='FI_LU_PENSION_PROVIDERS'
244 AND hoi2.org_information1=csr_v_pension_ins_num ;
245
246 rg_lu_pp_dtls csr_lu_pp_dtls%rowtype;
247
248 Cursor csr_le_pp_dtls
249 ( csr_v_legal_emp_id hr_organization_information.ORGANIZATION_ID%TYPE
250 , csr_v_pension_type hr_organization_information.org_information1%TYPE
251 , csr_v_pension_provider hr_organization_information.org_information1%TYPE
252 ,csr_v_effective_date DATE )
253 IS
254 SELECT hoi2.ORG_INFORMATION6 , hoi2.ORG_INFORMATION8
255 FROM hr_organization_units o1
256 , hr_organization_information hoi1
257 , hr_organization_information hoi2
258 WHERE o1.business_group_id =l_business_group_id
259 AND hoi1.organization_id = o1.organization_id
260 AND hoi1.organization_id = csr_v_legal_emp_id
261 AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
262 AND hoi1.org_information_context = 'CLASS'
263 AND o1.organization_id =hoi2.organization_id
264 AND hoi2.ORG_INFORMATION_CONTEXT='FI_PENSION_PROVIDERS'
265 AND hoi2.org_information3=csr_v_pension_type
266 AND hoi2.org_information4=csr_v_pension_provider
267 AND csr_v_effective_date BETWEEN fnd_date.canonical_to_date(hoi2.org_information1) AND
268 nvl(fnd_date.canonical_to_date(hoi2.org_information2),to_date('31/12/4712','DD/MM/YYYY')) ;
269
270
271 rg_le_pp_dtls csr_le_pp_dtls%rowtype;
272 */
273
274 CURSOR csr_Department_code (
275 csr_v_pension_provider_id hr_organization_information.organization_id%TYPE,
276 csr_v_legal_emp_id hr_organization_information.organization_id%TYPE,
277 csr_v_Local_unit_id hr_organization_information.ORG_INFORMATION2%TYPE
278 )
279 IS
280 SELECT hoi2.ORG_INFORMATION3
281 FROM hr_organization_units o1
282 , hr_organization_information hoi1
283 , hr_organization_information hoi2
284 WHERE o1.business_group_id =l_business_group_id
285 AND hoi1.organization_id = o1.organization_id
286 AND hoi1.organization_id = csr_v_pension_provider_id
287 AND hoi1.org_information_context = 'CLASS'
288 AND hoi1.org_information1 = 'FR_PENSION'
289 AND o1.organization_id =hoi2.organization_id
290 AND hoi2.ORG_INFORMATION_CONTEXT='FI_PENSION_DEPARTMENT_CODES'
291 AND hoi2.ORG_INFORMATION1 = csr_v_legal_emp_id
292 AND hoi2.ORG_INFORMATION2 = csr_v_Local_unit_id;
293
294
295 rg_Department_code csr_Department_code%ROWTYPE;
296
297
298
299 /* End of Cursors */
300
301 BEGIN
302
303 --fnd_file.put_line(fnd_file.log,'Range Code 1');
304
305 IF g_debug THEN
306 hr_utility.set_location(' Entering Procedure RANGE_CODE',40);
307 END IF;
308
309 p_sql := 'SELECT DISTINCT person_id
310 FROM per_people_f ppf
311 ,pay_payroll_actions ppa
312 WHERE ppa.payroll_action_id = :payroll_action_id
313 AND ppa.business_group_id = ppf.business_group_id
314 ORDER BY ppf.person_id';
315 g_archive := NULL;
316 g_emp_type := NULL ;
317 g_legal_employer_id := NULL ;
318 g_local_unit_id := NULL ;
319 g_effective_date := NULL ;
320 g_pension_provider := NULL ;
321 g_pension_ins_num := NULL ;
322 g_payroll_action_id := p_payroll_action_id ;
323 g_le_assignment_action_id := NULL ;
324 g_lu_assignment_action_id := NULL ;
325
326
327 --fnd_file.put_line(fnd_file.log,'Range Code 2');
328 PAY_FI_ARCHIVE_LTFA.GET_ALL_PARAMETERS(
329 p_payroll_action_id
330 ,l_business_group_id
331 ,g_pension_ins_num
332 ,g_legal_employer_id
333 ,g_local_unit_id
334 ,l_month
335 ,l_year
336 ,g_effective_date
337 ,g_archive ) ;
338
339
340 --fnd_file.put_line(fnd_file.log,'Range Code 3');
341
342 IF g_archive = 'Y' THEN
343
344 SELECT count(*)
345 INTO l_count
346 FROM pay_action_information
347 WHERE action_information_category = 'EMEA REPORT DETAILS'
348 AND action_information1 = 'PYFILTFA'
349 AND action_context_id = p_payroll_action_id;
350
351 IF l_count < 1 then
352
353 l_dept_code:='';
354
355 --fnd_file.put_line(fnd_file.log,'Range Code 5');
356 hr_utility.set_location('Entered Procedure GETDATA',10);
357
358 OPEN csr_Legal_Emp_Details(g_legal_employer_id, g_pension_ins_num, g_effective_date);
359 FETCH csr_Legal_Emp_Details INTO rg_Legal_Emp_Details;
360 CLOSE csr_Legal_Emp_Details;
361
362 l_le_name := rg_Legal_Emp_Details.name ;
363 --l_y_number := rg_Legal_Emp_Details.ORG_INFORMATION1 ;
364 g_pension_provider := rg_Legal_Emp_Details.ORG_INFORMATION4 ;
365
366 --fnd_file.put_line(fnd_file.log,'Range Code 4');
367 BEGIN
368 pay_balance_pkg.set_context('TAX_UNIT_ID',g_legal_employer_id);
369 pay_balance_pkg.set_context('LOCAL_UNIT_ID',g_local_unit_id);
370 pay_balance_pkg.set_context('DATE_EARNED',fnd_date.date_to_canonical(g_effective_date));
371 pay_balance_pkg.set_context('JURISDICTION_CODE',NULL);
372 pay_balance_pkg.set_context('SOURCE_ID',NULL);
373 pay_balance_pkg.set_context('TAX_GROUP',NULL);
374 pay_balance_pkg.set_context('ORGANIZATION_ID',g_pension_provider);
375 END;
376
377
378
379 OPEN csr_pension_provider_details(g_pension_provider);
380 FETCH csr_pension_provider_details INTO rg_pension_provider_details;
381 CLOSE csr_pension_provider_details;
382
383 l_pp_name := rg_pension_provider_details.name ;
384
385 /*
386 OPEN csr_le_pp_dtls(g_legal_employer_id,l_pension_type,g_pension_provider,g_effective_date );
387 FETCH csr_le_pp_dtls INTO rg_le_pp_dtls;
388 CLOSE csr_le_pp_dtls;
389
390 l_customer_num :=rg_le_pp_dtls.ORG_INFORMATION8 ;
391 l_pension_ins_num :=rg_le_pp_dtls.ORG_INFORMATION6 ;
392
393 */
394
395 IF g_local_unit_id IS NOT NULL THEN
396
397 --fnd_file.put_line(fnd_file.log,'Range Code 6');
398 BEGIN
399
400 SELECT MAX(ASSIGNMENT_ACTION_ID)
401 INTO g_lu_assignment_action_id
402 FROM pay_run_balances
403 WHERE local_unit_id = g_local_unit_id
404 AND organization_id =g_pension_provider
405 AND TO_CHAR(effective_date,'MMYYYY')=l_month||l_year ;
406
407 EXCEPTION
408 WHEN others THEN
409 NULL;
410 END;
411
412 l_emp_type:='LU' ;
413 l_emp_id:=g_local_unit_id;
414 hr_utility.set_location('Calculation for Local Unit',40);
415
416 /* Pick up the details belonging to Local Unit */
417
418 OPEN csr_Local_Unit_Details( g_local_unit_id);
419 FETCH csr_Local_Unit_Details INTO rg_Local_Unit_Details;
420 CLOSE csr_Local_Unit_Details;
421
422 l_lu_name := rg_Local_Unit_Details.name ;
423
424 OPEN csr_Department_code( g_pension_provider ,g_legal_employer_id,g_local_unit_id);
425 FETCH csr_Department_code INTO rg_Department_code;
426 CLOSE csr_Department_code;
427
428 l_dept_code := rg_Department_code.ORG_INFORMATION3;
429
430 --l_emp_name := rg_Local_Unit_Details.name ;
431 --l_business_id := l_y_number||'-'||rg_Local_Unit_Details.ORG_INFORMATION1 ;
432
433 /*
434 OPEN csr_Local_Unit_Details( g_local_unit_id);
435 FETCH csr_Local_Unit_Details INTO rg_Local_Unit_Details;
436 CLOSE csr_Local_Unit_Details;
437
438 OPEN csr_lu_pp_dtls( g_local_unit_id , l_pension_ins_num);
439 FETCH csr_lu_pp_dtls INTO rg_lu_pp_dtls;
440 CLOSE csr_lu_pp_dtls;
441
442 l_customer_num :=rg_lu_pp_dtls.ORG_INFORMATION2 ;
443 */
444 hr_utility.set_location('Pick up the details belonging to Local Unit',60);
445 --fnd_file.put_line(fnd_file.log,'Range Code 7');
446
447 ELSE
448 --fnd_file.put_line(fnd_file.log,'Range Code 8');
449 l_emp_type:='LE' ;
450 l_emp_id:=g_legal_employer_id ;
451
452 BEGIN
453
454 SELECT MAX(ASSIGNMENT_ACTION_ID)
455 INTO g_le_assignment_action_id
456 FROM pay_run_balances
457 WHERE tax_unit_id =g_legal_employer_id
458 AND organization_id =g_pension_provider
459 AND TO_CHAR(effective_date,'MMYYYY')=l_month||l_year ;
460
461 EXCEPTION
462 WHEN others THEN
463 NULL;
464 END;
465
466
467
468 /* Pick up the details belonging to Legal Employer */
469
470 OPEN csr_Legal_Emp_Details(g_legal_employer_id, g_pension_ins_num, g_effective_date);
471 FETCH csr_Legal_Emp_Details INTO rg_Legal_Emp_Details;
472 CLOSE csr_Legal_Emp_Details;
473
474 l_le_name := rg_Legal_Emp_Details.name ;
475 --l_emp_name := rg_Legal_Emp_Details.name ;
476 --l_business_id := rg_Legal_Emp_Details.ORG_INFORMATION1 ;
477
478 --fnd_file.put_line(fnd_file.log,'Range Code 9');
479 END IF ;
480
481 /*
482 --fnd_file.put_line(fnd_file.log,'Range Code 10');
483 pay_action_information_api.create_action_information (
484 p_action_information_id => l_action_info_id
485 ,p_action_context_id => p_payroll_action_id
486 ,p_action_context_type => 'PA'
487 ,p_object_version_number => l_ovn
488 ,p_effective_date => g_effective_date
489 ,p_source_id => NULL
490 ,p_source_text => NULL
491 ,p_action_information_category => 'EMEA REPORT INFORMATION'
492 ,p_action_information1 => 'PYFILTFA'
493 ,p_action_information2 => l_emp_type
494 ,p_action_information3 => l_emp_id
495 ,p_action_information4 => l_month||substr(l_year,3,2)
496 ,p_action_information5 => substr(l_emp_name,1,33)
497 ,p_action_information6 => l_business_id
498 ,p_action_information7 => g_pension_provider
499 ,p_action_information8 => l_customer_num
500 ,p_action_information9 => TO_CHAR(g_effective_date,'DDMMYY')
501 ,p_action_information10 => '1'
502 ,p_action_information11 => 'A'
503 ,p_action_information12 =>null
504 ,p_action_information13 => null
505 ,p_action_information14 => null
506 ,p_action_information15 => null
507 ,p_action_information16 => null
508 ,p_action_information17 => null
509 ,p_action_information18 => null
510 ,p_action_information19 => null
511 ,p_action_information20 => null
512 ,p_action_information21 => null
513 ,p_action_information22 => null
514 ,p_action_information23 => null
515 ,p_action_information24 => null
516 ,p_action_information25 => null
517 ,p_action_information26 => null
518 ,p_action_information27 => null
519 ,p_action_information28 => null
520 ,p_action_information29 => null
521 ,p_action_information30 => null );
522
523 */
524 pay_action_information_api.create_action_information (
525 p_action_information_id => l_action_info_id
526 ,p_action_context_id => p_payroll_action_id
527 ,p_action_context_type => 'PA'
528 ,p_object_version_number => l_ovn
529 ,p_effective_date => g_effective_date
530 ,p_source_id => NULL
531 ,p_source_text => NULL
532 ,p_action_information_category => 'EMEA REPORT DETAILS'
533 ,p_action_information1 => 'PYFILTFA'
534 ,p_action_information2 => g_pension_ins_num
535 ,p_action_information3 => l_pp_name
536 ,p_action_information4 => l_le_name
537 ,p_action_information5 => l_lu_name
538 ,p_action_information6 => l_month
539 ,p_action_information7 => l_year
540 ,p_action_information8 => g_legal_employer_id
541 ,p_action_information9 => g_local_unit_id
542 ,p_action_information10 => l_emp_type
543 ,p_action_information11 => l_dept_code
544 ,p_action_information12 => null
545 ,p_action_information13 => null
546 ,p_action_information14 => null
547 ,p_action_information15 => null
548 ,p_action_information16 => null
549 ,p_action_information17 => null
550 ,p_action_information18 => null
551 ,p_action_information19 => null
552 ,p_action_information20 => null
553 ,p_action_information21 => null
554 ,p_action_information22 => null
555 ,p_action_information23 => null
556 ,p_action_information24 => null
557 ,p_action_information25 => null
558 ,p_action_information26 => null
559 ,p_action_information27 => null
560 ,p_action_information28 => null
561 ,p_action_information29 => null
562 ,p_action_information30 => null );
563
564 --fnd_file.put_line(fnd_file.log,'Range Code 11');
565 END IF;
566
567 END IF;
568
569 g_emp_type := l_emp_type;
570
571 IF g_debug THEN
572 hr_utility.set_location(' Leaving Procedure RANGE_CODE',50);
573 END IF;
574 EXCEPTION
575 WHEN others THEN
576 IF g_debug THEN
577 hr_utility.set_location('error raised assignment_action_code ',5);
578 END if;
579 RAISE;
580 END RANGE_CODE;
581
582 /* ASSIGNMENT ACTION CODE */
583 PROCEDURE ASSIGNMENT_ACTION_CODE
584 (p_payroll_action_id IN NUMBER
585 ,p_start_person IN NUMBER
586 ,p_end_person IN NUMBER
587 ,p_chunk IN NUMBER)
588 IS
589 CURSOR csr_prepaid_assignments_lu(p_payroll_action_id NUMBER,
590 p_start_person NUMBER,
591 p_end_person NUMBER,
592 p_legal_employer_id NUMBER,
593 p_local_unit_id NUMBER,
594 p_pension_ins_num VARCHAR2,
595 l_canonical_start_date DATE,
596 l_canonical_end_date DATE)
597 IS
598 SELECT act.assignment_id assignment_id,
599 act.assignment_action_id run_action_id,
600 act1.assignment_action_id prepaid_action_id
601 FROM pay_payroll_actions ppa
602 ,pay_payroll_actions appa
603 ,pay_payroll_actions appa2
604 ,pay_assignment_actions act
605 ,pay_assignment_actions act1
606 ,pay_action_interlocks pai
607 ,per_all_assignments_f as1
608 ,hr_soft_coding_keyflex hsck
609 , per_all_people_f pap
610 WHERE ppa.payroll_action_id = p_payroll_action_id
611 AND appa.effective_date BETWEEN l_canonical_start_date
612 AND l_canonical_end_date
613 AND as1.person_id BETWEEN p_start_person
614 AND p_end_person
615 AND appa.action_type IN ('R','Q')
616 -- Payroll Run or Quickpay Run
617 AND act.payroll_action_id = appa.payroll_action_id
618 AND act.source_action_id IS NULL -- Master Action
619 AND as1.assignment_id = act.assignment_id
620 AND as1.person_id = pap.person_id
621 AND pap.per_information24 = p_pension_ins_num
622 AND ppa.effective_date BETWEEN as1.effective_start_date
623 AND as1.effective_end_date
624 AND ppa.effective_date BETWEEN pap.effective_start_date
625 AND pap.effective_end_date
626 AND act.action_status = 'C' -- Completed
627 AND act.assignment_action_id = pai.locked_action_id
628 AND act1.assignment_action_id = pai.locking_action_id
629 AND act1.action_status = 'C' -- Completed
630 AND act1.payroll_action_id = appa2.payroll_action_id
631 AND appa2.action_type IN ('P','U')
632 AND appa2.effective_date BETWEEN l_canonical_start_date
633 AND l_canonical_end_date
634 -- Prepayments or Quickpay Prepayments
635 AND hsck.SOFT_CODING_KEYFLEX_ID=as1.SOFT_CODING_KEYFLEX_ID
636 AND hsck.segment2 = p_local_unit_id
637 AND act.TAX_UNIT_ID = act1.TAX_UNIT_ID
638 AND act.TAX_UNIT_ID = p_legal_employer_id
639 ORDER BY act.assignment_id;
640
641 CURSOR csr_prepaid_assignments_le(p_payroll_action_id NUMBER,
642 p_start_person NUMBER,
643 p_end_person NUMBER,
644 p_legal_employer_id NUMBER,
645 p_pension_ins_num VARCHAR2,
646 l_canonical_start_date DATE,
647 l_canonical_end_date DATE)
648 IS
649 SELECT act.assignment_id assignment_id,
650 act.assignment_action_id run_action_id,
651 act1.assignment_action_id prepaid_action_id
652 FROM pay_payroll_actions ppa,
653 pay_payroll_actions appa,
654 pay_payroll_actions appa2,
655 pay_assignment_actions act,
656 pay_assignment_actions act1,
657 pay_action_interlocks pai,
658 per_all_assignments_f as1
659 , per_all_people_f pap
660 WHERE ppa.payroll_action_id = p_payroll_action_id
661 AND appa.effective_date BETWEEN l_canonical_start_date
662 AND l_canonical_end_date
663 AND as1.person_id BETWEEN p_start_person
664 AND p_end_person
665 AND appa.action_type IN ('R','Q')
666 -- Payroll Run or Quickpay Run
667 AND act.payroll_action_id = appa.payroll_action_id
668 AND act.source_action_id IS NULL -- Master Action
669 AND as1.assignment_id = act.assignment_id
670 AND as1.person_id = pap.person_id
671 AND pap.per_information24 = p_pension_ins_num
672 AND ppa.effective_date BETWEEN as1.effective_start_date
673 AND as1.effective_end_date
674 AND ppa.effective_date BETWEEN pap.effective_start_date
675 AND pap.effective_end_date
676 AND act.action_status = 'C' -- Completed
677 AND act.assignment_action_id = pai.locked_action_id
678 AND act1.assignment_action_id = pai.locking_action_id
679 AND act1.action_status = 'C' -- Completed
680 AND act1.payroll_action_id = appa2.payroll_action_id
681 AND appa2.action_type IN ('P','U')
682 AND appa2.effective_date BETWEEN l_canonical_start_date
683 AND l_canonical_end_date
684 -- Prepayments or Quickpay Prepayments
685 AND act.TAX_UNIT_ID = act1.TAX_UNIT_ID
686 AND act.TAX_UNIT_ID = p_legal_employer_id
687 ORDER BY act.assignment_id;
688
689 Cursor csr_Get_Defined_Balance_Id(csr_v_Balance_Name FF_DATABASE_ITEMS.USER_NAME%TYPE)
690 IS
691 SELECT ue.creator_id
692 FROM ff_user_entities ue,
693 ff_database_items di
694 WHERE di.user_name = csr_v_Balance_Name
695 AND ue.user_entity_id = di.user_entity_id
696 AND ue.legislation_code = 'FI'
697 AND ue.business_group_id is NULL
698 AND ue.creator_type = 'B';
699
700 lr_Get_Defined_Balance_Id csr_Get_Defined_Balance_Id%rowtype;
701
702 l_count NUMBER := 0;
703 l_prev_prepay NUMBER := 0;
704 l_business_group_id NUMBER;
705 l_month VARCHAR2(2);
706 l_year VARCHAR2(4);
707 l_canonical_start_date DATE;
708 l_canonical_end_date DATE;
709 l_pension_ins_num hr_organization_information.org_information1%TYPE ;
710
711
712 l_prepay_action_id NUMBER;
713 l_actid NUMBER;
714 l_assignment_id NUMBER;
715 l_action_sequence NUMBER;
716 l_assact_id NUMBER;
717 l_pact_id NUMBER;
718 l_flag NUMBER := 0;
719 l_defined_balance_id NUMBER :=0;
720 l_action_info_id NUMBER;
721 l_ovn NUMBER;
722 BEGIN
723 IF g_debug THEN
724 hr_utility.set_location(' Entering Procedure ASSIGNMENT_ACTION_CODE',60);
725 END IF;
726
727 --fnd_file.put_line(fnd_file.log,'Assignment Action Code 1');
728 PAY_FI_ARCHIVE_LTFA.GET_ALL_PARAMETERS(
729 p_payroll_action_id
730 ,l_business_group_id
731 ,g_pension_ins_num
732 ,g_legal_employer_id
733 ,g_local_unit_id
734 ,l_month
735 ,l_year
736 ,g_effective_date
737 ,g_archive ) ;
738
739 g_payroll_action_id :=p_payroll_action_id;
740 --fnd_file.put_line(fnd_file.log,'Assignment Action Code 2');
741
742
743 BEGIN
744 pay_balance_pkg.set_context('TAX_UNIT_ID',g_legal_employer_id);
745 pay_balance_pkg.set_context('LOCAL_UNIT_ID',g_local_unit_id);
746 pay_balance_pkg.set_context('DATE_EARNED',fnd_date.date_to_canonical(g_effective_date));
747 pay_balance_pkg.set_context('JURISDICTION_CODE',NULL);
748 pay_balance_pkg.set_context('SOURCE_ID',NULL);
749 pay_balance_pkg.set_context('TAX_GROUP',NULL);
750 pay_balance_pkg.set_context('ORGANIZATION_ID',g_pension_provider);
751 END;
752
753 l_canonical_start_date := TO_DATE(l_month||l_year,'MMYYYY');
754 l_canonical_end_date := LAST_DAY(TO_DATE(l_month||l_year,'MMYYYY'));
755 l_prepay_action_id := 0;
756
757 IF g_local_unit_id IS NOT NULL THEN
758 g_emp_type := 'LU';
759 --fnd_file.put_line(fnd_file.log,'Assignment Action Code 3');
760
761 FOR rec_prepaid_assignments IN csr_prepaid_assignments_lu(p_payroll_action_id
762 ,p_start_person
763 ,p_end_person
764 ,g_legal_employer_id
765 ,g_local_unit_id
766 ,g_pension_ins_num
767 ,l_canonical_start_date
768 ,l_canonical_end_date)
769 LOOP
770 --fnd_file.put_line(fnd_file.log,'Assignment Action Code 4');
771 IF l_prepay_action_id <> rec_prepaid_assignments.prepaid_action_id THEN
772 SELECT pay_assignment_actions_s.NEXTVAL
773 INTO l_actid
774 FROM dual;
775 --
776 g_index_assact := g_index_assact + 1;
777 g_lock_table(g_index_assact).archive_assact_id := l_actid; /* For Element archival */
778 -- Create the archive assignment action
779 hr_nonrun_asact.insact(l_actid
780 ,rec_prepaid_assignments.assignment_id
781 ,p_payroll_action_id
782 ,p_chunk
783 ,NULL);
784 -- Create archive to prepayment assignment action interlock
785 --
786 --hr_nonrun_asact.insint(l_actid,rec_prepaid_assignments.prepaid_action_id);
787 END IF;
788 -- create archive to master assignment action interlock
789 --hr_nonrun_asact.insint(l_actid,rec_prepaid_assignments.run_action_id);
790 l_prepay_action_id := rec_prepaid_assignments.prepaid_action_id;
791 END LOOP;
792
793 ELSE
794 --fnd_file.put_line(fnd_file.log,'Assignment Action Code 5');
795 g_emp_type := 'LE';
796
797 FOR rec_prepaid_assignments IN csr_prepaid_assignments_le(p_payroll_action_id
798 ,p_start_person
799 ,p_end_person
800 ,g_legal_employer_id
801 ,g_pension_ins_num
802 ,l_canonical_start_date
803 ,l_canonical_end_date)
804 LOOP
805 --fnd_file.put_line(fnd_file.log,'Assignment Action Code 6');
806 IF l_prepay_action_id <> rec_prepaid_assignments.prepaid_action_id THEN
807 SELECT pay_assignment_actions_s.NEXTVAL
808 INTO l_actid
809 FROM dual;
810 --
811 g_index_assact := g_index_assact + 1;
812 g_lock_table(g_index_assact).archive_assact_id := l_actid; /* For Element archival */
813 -- Create the archive assignment action
814 hr_nonrun_asact.insact(l_actid
815 ,rec_prepaid_assignments.assignment_id
816 ,p_payroll_action_id
817 ,p_chunk
818 ,NULL);
819 -- Create archive to prepayment assignment action interlock
820 --
821 --hr_nonrun_asact.insint(l_actid,rec_prepaid_assignments.prepaid_action_id);
822 END IF;
823 -- create archive to master assignment action interlock
824 --hr_nonrun_asact.insint(l_actid,rec_prepaid_assignments.run_action_id);
825 l_prepay_action_id := rec_prepaid_assignments.prepaid_action_id;
826 END LOOP;
827 END IF;
828 --fnd_file.put_line(fnd_file.log,'Assignment Action Code 7');
829 IF g_debug THEN
830 hr_utility.set_location(' Leaving Procedure ASSIGNMENT_ACTION_CODE',70);
831 END IF;
832 EXCEPTION
833 WHEN others THEN
834 IF g_debug THEN
835 hr_utility.set_location('error raised assignment_action_code ',5);
836 END if;
837 RAISE;
838
839 END ASSIGNMENT_ACTION_CODE;
840 /* INITIALIZATION CODE */
841 PROCEDURE INITIALIZATION_CODE(p_payroll_action_id IN NUMBER)
842 IS
843
844 BEGIN
845 IF g_debug THEN
846 hr_utility.set_location(' Entering Procedure INITIALIZATION_CODE',80);
847 END IF;
848
849 IF g_debug THEN
850 hr_utility.set_location(' Leaving Procedure INITIALIZATION_CODE',90);
851 END IF;
852
853 EXCEPTION
854 WHEN others THEN
855 IF g_debug THEN
856 hr_utility.set_location('error raised initialization code ',5);
857 END if;
858 RAISE;
859 END INITIALIZATION_CODE;
860
861 /* ARCHIVE CODE */
862 PROCEDURE ARCHIVE_CODE(p_assignment_action_id IN NUMBER
863 ,p_effective_date IN DATE)
864 IS
865 /* Cursor to retrieve Person Details */
866 CURSOR csr_get_person_details(p_asg_act_id NUMBER) IS
867 SELECT pap.first_name, pap.last_name, pap.pre_name_adjunct, pap.national_identifier , pap.person_id , pac.assignment_id
868 FROM
869 pay_assignment_actions pac,
870 per_all_assignments assign,
871 per_all_people pap
872 WHERE pac.assignment_action_id = p_asg_act_id
873 AND assign.assignment_id = pac.assignment_id
874 AND assign.person_id = pap.person_id
875 AND pap.per_information_category = 'FI';
876
877 Cursor csr_Get_Defined_Balance_Id(csr_v_Balance_Name FF_DATABASE_ITEMS.USER_NAME%TYPE)
878 IS
879 SELECT ue.creator_id
880 FROM ff_user_entities ue,
881 ff_database_items di
882 WHERE di.user_name = csr_v_Balance_Name
883 AND ue.user_entity_id = di.user_entity_id
884 AND ue.legislation_code = 'FI'
885 AND ue.business_group_id is NULL
886 AND ue.creator_type = 'B';
887
888 lr_Get_Defined_Balance_Id csr_Get_Defined_Balance_Id%rowtype;
889
890 rg_get_person_details csr_get_person_details%rowtype;
891
892 l_Sal_subject_pension NUMBER ;
893 l_bik_subject_pension NUMBER ;
894 l_tax_exp_subject_pension NUMBER ;
895 l_assignment_id NUMBER;
896 l_pension NUMBER ;
897 l_emp_pension NUMBER ;
898 l_action_context_id NUMBER;
899 l_flag NUMBER := 0;
900 l_action_info_id NUMBER;
901 l_ovn NUMBER;
902 l_Employee_name VARCHAR2(240);
903
904
905
906 BEGIN
907 IF g_debug THEN
908 hr_utility.set_location(' Entering Procedure ARCHIVE_CODE',380);
909 END IF;
910 --fnd_file.put_line(fnd_file.log,'Archive Code 1');
911
912
913 IF g_archive='Y' THEN
914
915 OPEN csr_get_person_details(p_assignment_action_id);
916 FETCH csr_get_person_details INTO rg_get_person_details;
917 CLOSE csr_get_person_details;
918
919 IF rg_get_person_details.PRE_NAME_ADJUNCT IS NULL
920 THEN
921 l_Employee_name :=rg_get_person_details.LAST_NAME
922 ||' '||rg_get_person_details.FIRST_NAME;
923 ELSE
924 l_Employee_name :=rg_get_person_details.PRE_NAME_ADJUNCT||
925 ' '|| rg_get_person_details.LAST_NAME||
926 ' '|| rg_get_person_details.FIRST_NAME;
927 END IF;
928
929
930 -- Pick up the defined balance id belonging to
931
932 BEGIN
933 pay_balance_pkg.set_context('ASSIGNMENT_ID',rg_get_person_details.assignment_id);
934 END;
935
936 l_assignment_id:=rg_get_person_details.assignment_id;
937 --fnd_file.put_line(fnd_file.log,'Archive Code 2');
938
939 IF g_emp_type = 'LU' THEN
940
941 --fnd_file.put_line(fnd_file.log,'Archive Code 3');
942 OPEN csr_Get_Defined_Balance_Id( 'SALARY_SUBJECT_TO_PENSION_PER_PENSION_LU_MONTH');
943 FETCH csr_Get_Defined_Balance_Id INTO lr_Get_Defined_Balance_Id;
944 CLOSE csr_Get_Defined_Balance_Id;
945
946 l_Sal_subject_pension :=to_char(pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>lr_Get_Defined_Balance_Id.creator_id, P_ASSIGNMENT_ID =>l_assignment_id , P_VIRTUAL_DATE => g_effective_date ),'999999999D99') *100;
947
948 OPEN csr_Get_Defined_Balance_Id( 'BIK_SUBJECT_TO_PENSION_PER_PENSION_LU_MONTH');
949 FETCH csr_Get_Defined_Balance_Id INTO lr_Get_Defined_Balance_Id;
950 CLOSE csr_Get_Defined_Balance_Id;
951
952 l_bik_subject_pension :=to_char(pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>lr_Get_Defined_Balance_Id.creator_id, P_ASSIGNMENT_ID =>l_assignment_id , P_VIRTUAL_DATE => g_effective_date ),'999999999D99') *100;
953
954 OPEN csr_Get_Defined_Balance_Id( 'TAXABLE_EXPENSES_SUBJECT_TO_PENSION_PER_PENSION_LU_MONTH');
955 FETCH csr_Get_Defined_Balance_Id INTO lr_Get_Defined_Balance_Id;
956 CLOSE csr_Get_Defined_Balance_Id;
957
958 l_tax_exp_subject_pension :=to_char(pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>lr_Get_Defined_Balance_Id.creator_id, P_ASSIGNMENT_ID =>l_assignment_id , P_VIRTUAL_DATE => g_effective_date ),'999999999D99') *100;
959
960 /*
961 OPEN csr_Get_Defined_Balance_Id( 'PENSION_PER_PENSION_LU_MONTH');
962 FETCH csr_Get_Defined_Balance_Id INTO lr_Get_Defined_Balance_Id;
963 CLOSE csr_Get_Defined_Balance_Id;
964
965 l_pension :=TO_CHAR(pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>lr_Get_Defined_Balance_Id.creator_id, P_ASSIGNMENT_ACTION_ID =>g_lu_assignment_action_id ),'999999999D99') * 100;
966
967 OPEN csr_Get_Defined_Balance_Id( 'EMPLOYER_PENSION_PER_PENSION_LU_MONTH');
968 FETCH csr_Get_Defined_Balance_Id INTO lr_Get_Defined_Balance_Id;
969 CLOSE csr_Get_Defined_Balance_Id;
970
971 l_emp_pension :=TO_CHAR(pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>lr_Get_Defined_Balance_Id.creator_id, P_ASSIGNMENT_ACTION_ID =>g_lu_assignment_action_id ),'999999999D99') *100;
972 */
973
974 --fnd_file.put_line(fnd_file.log,'Archive Code 4');
975
976 ELSIF g_emp_type = 'LE' THEN
977
978 --fnd_file.put_line(fnd_file.log,'Archive Code 5');
979 OPEN csr_Get_Defined_Balance_Id( 'SALARY_SUBJECT_TO_PENSION_PER_PENSION_LE_MONTH');
980 FETCH csr_Get_Defined_Balance_Id INTO lr_Get_Defined_Balance_Id;
981 CLOSE csr_Get_Defined_Balance_Id;
982 l_Sal_subject_pension :=to_char(pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>lr_Get_Defined_Balance_Id.creator_id, P_ASSIGNMENT_ID =>l_assignment_id , P_VIRTUAL_DATE => g_effective_date ),'999999999D99') *100;
983
984 OPEN csr_Get_Defined_Balance_Id( 'BIK_SUBJECT_TO_PENSION_PER_PENSION_LE_MONTH');
985 FETCH csr_Get_Defined_Balance_Id INTO lr_Get_Defined_Balance_Id;
986 CLOSE csr_Get_Defined_Balance_Id;
987 l_bik_subject_pension :=to_char(pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>lr_Get_Defined_Balance_Id.creator_id, P_ASSIGNMENT_ID =>l_assignment_id , P_VIRTUAL_DATE => g_effective_date ),'999999999D99') *100;
988
989 OPEN csr_Get_Defined_Balance_Id( 'TAXABLE_EXPENSES_SUBJECT_TO_PENSION_PER_PENSION_LE_MONTH');
990 FETCH csr_Get_Defined_Balance_Id INTO lr_Get_Defined_Balance_Id;
991 CLOSE csr_Get_Defined_Balance_Id;
992 l_tax_exp_subject_pension :=to_char(pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>lr_Get_Defined_Balance_Id.creator_id, P_ASSIGNMENT_ID =>l_assignment_id , P_VIRTUAL_DATE => g_effective_date ),'999999999D99') *100;
993
994 /*
995 OPEN csr_Get_Defined_Balance_Id( 'PENSION_PER_PENSION_LE_MONTH');
996 FETCH csr_Get_Defined_Balance_Id INTO lr_Get_Defined_Balance_Id;
997 CLOSE csr_Get_Defined_Balance_Id;
998 l_pension := TO_CHAR(pay_balance_pkg.get_value(lr_Get_Defined_Balance_Id.creator_id,NULL, g_legal_employer_id, NULL, NULL, NULL, g_effective_date ),'999999999D99') * 100;
999
1000 OPEN csr_Get_Defined_Balance_Id( 'EMPLOYER_PENSION_PER_PENSION_LE_MONTH');
1001 FETCH csr_Get_Defined_Balance_Id INTO lr_Get_Defined_Balance_Id;
1002 CLOSE csr_Get_Defined_Balance_Id;
1003 l_emp_pension := TO_CHAR(pay_balance_pkg.get_value(lr_Get_Defined_Balance_Id.creator_id,NULL, g_legal_employer_id, NULL, NULL, NULL, g_effective_date ),'999999999D99') *100;
1004 */
1005 --fnd_file.put_line(fnd_file.log,'Archive Code 6');
1006 END IF;
1007
1008 BEGIN
1009 SELECT 1
1010 INTO l_flag
1011 FROM pay_action_information
1012 WHERE action_information_category = 'EMEA REPORT INFORMATION'
1013 AND action_information1 = 'PYFILTFA'
1014 AND action_information2 = 'PER'
1015 AND action_context_id = p_assignment_action_id;
1016 EXCEPTION
1017 WHEN NO_DATA_FOUND
1018 THEN
1019
1020 --fnd_file.put_line(fnd_file.log,'Archive Code 7');
1021 pay_action_information_api.create_action_information (
1022 p_action_information_id=> l_action_info_id,
1023 p_action_context_id=> p_assignment_action_id,
1024 p_action_context_type=> 'AAP',
1025 p_object_version_number=> l_ovn,
1026 p_effective_date=> g_effective_date,
1027 p_source_id=> NULL,
1028 p_source_text=> NULL,
1029 p_action_information_category=> 'EMEA REPORT INFORMATION',
1030 p_action_information1=> 'PYFILTFA',
1031 p_action_information2=> 'PER',
1032 p_action_information3=>rg_get_person_details.person_id ,
1033 p_action_information4=>l_Employee_name ,
1034 p_action_information5=> rg_get_person_details.national_identifier ,
1035 p_action_information6=> '3' ,
1036 p_action_information7=> FND_NUMBER.NUMBER_TO_CANONICAL(l_Sal_subject_pension) ,
1037 p_action_information8=> FND_NUMBER.NUMBER_TO_CANONICAL(l_bik_subject_pension) ,
1038 p_action_information9=> FND_NUMBER.NUMBER_TO_CANONICAL(l_tax_exp_subject_pension) ,
1039 p_action_information10=> g_payroll_action_id,
1040 p_action_information11=> NULL,
1041 p_action_information12=> NULL ,
1042 p_action_information13=> NULL,
1043 p_action_information14=> NULL,
1044 p_action_information15=> NULL,
1045 p_action_information16=> NULL,
1046 p_action_information17=> NULL,
1047 p_action_information18=> NULL,
1048 p_action_information19=> NULL,
1049 p_action_information20=> NULL,
1050 p_action_information21=> NULL,
1051 p_action_information22=> NULL,
1052 p_action_information23=> NULL,
1053 p_action_information24=> NULL,
1054 p_action_information25=> NULL,
1055 p_action_information26=> NULL,
1056 p_action_information27=> NULL,
1057 p_action_information28=> NULL,
1058 p_action_information29=> NULL,
1059 p_action_information30=> NULL
1060 );
1061 --fnd_file.put_line(fnd_file.log,'Archive Code 8');
1062 WHEN OTHERS
1063 THEN
1064 NULL;
1065 END;
1066
1067 --fnd_file.put_line(fnd_file.log,'Archive Code 9');
1068 END IF;---ARCHIVE=YES
1069 IF g_debug THEN
1070 hr_utility.set_location(' Leaving Procedure ARCHIVE_CODE',390);
1071 END IF;
1072
1073
1074 EXCEPTION
1075 WHEN others THEN
1076 IF g_debug THEN
1077 hr_utility.set_location('error raised in archive code ',5);
1078 END if;
1079 RAISE;
1080 END ARCHIVE_CODE;
1081
1082
1083 PROCEDURE DEINITIALIZATION_CODE
1084 (p_payroll_action_id in pay_payroll_actions.payroll_action_id%type) is
1085
1086 /*
1087 CURSOR csr_person_pay_action_info(p_payroll_action_id pay_action_information.action_information1%TYPE)
1088 IS
1089 SELECT COUNT(*) emp_count , sum (FND_NUMBER.CANONICAL_TO_NUMBER(action_information7) + FND_NUMBER.CANONICAL_TO_NUMBER(action_information8) + FND_NUMBER.CANONICAL_TO_NUMBER(action_information9)) emp_sal
1090 ,sum (FND_NUMBER.CANONICAL_TO_NUMBER(action_information11)) pension , sum (FND_NUMBER.CANONICAL_TO_NUMBER(action_information12)) emp_pension
1091 FROM pay_action_information
1092 WHERE action_information_category = 'EMEA REPORT INFORMATION'
1093 AND action_information1 = 'PYFILTFA'
1094 AND action_information2 = 'PER'
1095 AND action_information10 = p_payroll_action_id ;
1096
1097 Cursor csr_Get_Defined_Balance_Id(csr_v_Balance_Name FF_DATABASE_ITEMS.USER_NAME%TYPE)
1098 IS
1099 SELECT ue.creator_id
1100 FROM ff_user_entities ue,
1101 ff_database_items di
1102 WHERE di.user_name = csr_v_Balance_Name
1103 AND ue.user_entity_id = di.user_entity_id
1104 AND ue.legislation_code = 'FI'
1105 AND ue.business_group_id is NULL
1106 AND ue.creator_type = 'B';
1107
1108 lr_Get_Defined_Balance_Id csr_Get_Defined_Balance_Id%rowtype;
1109
1110
1111 l_total NUMBER ;
1112 l_total_ins_fee NUMBER ;
1113 l_emp_count NUMBER ;
1114 l_emp_sal NUMBER ;
1115 l_pension NUMBER ;
1116 l_emp_pension NUMBER ;
1117 l_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE;
1118 l_action_info_id NUMBER;
1119 l_ovn NUMBER;
1120 l_business_group_id NUMBER;
1121 l_month VARCHAR2(2);
1122 l_year VARCHAR2(4);
1123 l_pension_type hr_organization_information.org_information1%TYPE ;
1124 */
1125 BEGIN
1126 IF g_debug THEN
1127 hr_utility.set_location(' Entering Procedure DEINITIALIZATION_CODE',380);
1128 END IF;
1129 /*
1130 --fnd_file.put_line(fnd_file.log,'Deinitialization Code 1');
1131 PAY_FI_ARCHIVE_LTFA.GET_ALL_PARAMETERS(
1132 p_payroll_action_id
1133 ,l_business_group_id
1134 ,l_pension_type
1135 ,g_pension_provider
1136 ,g_legal_employer_id
1137 ,g_local_unit_id
1138 ,l_month
1139 ,l_year
1140 ,g_effective_date
1141 ,g_archive ) ;
1142
1143 OPEN csr_person_pay_action_info(p_payroll_action_id);
1144 FETCH csr_person_pay_action_info INTO l_emp_count , l_emp_sal , l_pension , l_emp_pension ;
1145 CLOSE csr_person_pay_action_info ;
1146
1147 IF g_local_unit_id IS NULL THEN
1148
1149 g_emp_type:= 'LE';
1150
1151 ELSE
1152 g_emp_type:= 'LU';
1153 END IF;
1154
1155 IF g_archive='Y' THEN
1156 --fnd_file.put_line(fnd_file.log,'Deinitialization Code 2');
1157 l_total_ins_fee := l_pension + l_emp_pension;
1158
1159 l_total := l_total_ins_fee + (nvl(g_penalty_amt,0)*100) ;
1160
1161 pay_action_information_api.create_action_information (
1162 p_action_information_id=> l_action_info_id,
1163 p_action_context_id=> p_payroll_action_id,
1164 p_action_context_type=> 'PA',
1165 p_object_version_number=> l_ovn,
1166 p_effective_date=> g_effective_date,
1167 p_source_id=> NULL,
1168 p_source_text=> NULL,
1169 p_action_information_category=> 'EMEA REPORT INFORMATION',
1170 p_action_information1=> 'PYFILTFA',
1171 p_action_information2=> g_emp_type,
1172 p_action_information3=>'S' ,
1173 p_action_information4=> l_emp_count ,
1174 p_action_information5=> FND_NUMBER.NUMBER_TO_CANONICAL(l_emp_sal) ,
1175 p_action_information6=> FND_NUMBER.NUMBER_TO_CANONICAL(l_total_ins_fee) ,
1176 p_action_information7=> FND_NUMBER.NUMBER_TO_CANONICAL((nvl(g_penalty_amt,0))*100) ,
1177 p_action_information8=> FND_NUMBER.NUMBER_TO_CANONICAL(l_total) ,
1178 p_action_information9=> NULL ,
1179 p_action_information10=> p_payroll_action_id,
1180 p_action_information11=> NULL,
1181 p_action_information12=> NULL,
1182 p_action_information13=> NULL,
1183 p_action_information14=> NULL,
1184 p_action_information15=> NULL,
1185 p_action_information16=> NULL,
1186 p_action_information17=> NULL,
1187 p_action_information18=> NULL,
1188 p_action_information19=> NULL,
1189 p_action_information20=> NULL,
1190 p_action_information21=> NULL,
1191 p_action_information22=> NULL,
1192 p_action_information23=> NULL,
1193 p_action_information24=> NULL,
1194 p_action_information25=> NULL,
1195 p_action_information26=> NULL,
1196 p_action_information27=> NULL,
1197 p_action_information28=> NULL,
1198 p_action_information29=> NULL,
1199 p_action_information30=> NULL
1200 );
1201
1202 --fnd_file.put_line(fnd_file.log,'Deinitialization Code 3');
1203
1204 END IF;---ARCHIVE=YES
1205
1206 */
1207 IF g_debug THEN
1208 hr_utility.set_location(' Leaving Procedure DEINITIALIZATION_CODE',390);
1209 END IF;
1210
1211 EXCEPTION
1212 WHEN others THEN
1213 IF g_debug THEN
1214 hr_utility.set_location('error raised in DEINITIALIZATION_CODE ',5);
1215 END if;
1216 RAISE;
1217 END;
1218
1219 BEGIN
1220 g_archive := NULL;
1221 g_emp_type := NULL ;
1222 g_legal_employer_id := NULL ;
1223 g_local_unit_id := NULL ;
1224 g_effective_date := NULL ;
1225 g_pension_ins_num := NULL ;
1226 g_payroll_action_id := NULL ;
1227 END PAY_FI_ARCHIVE_LTFA;