[Home] [Help]
PACKAGE BODY: APPS.PAY_FI_ARCHIVE_TEL
Source
1 PACKAGE BODY PAY_FI_ARCHIVE_TEL AS
2 /* $Header: pyfitela.pkb 120.20 2012/01/19 09:20:28 rpahune ship $ */
3
4 g_debug boolean := hr_utility.debug_enabled;
5
6 TYPE lock_rec IS RECORD (
7 archive_assact_id NUMBER);
8
9 TYPE lock_table IS TABLE OF lock_rec INDEX BY BINARY_INTEGER;
10
11 g_lock_table lock_table;
12
13 g_index NUMBER := -1;
14 g_index_assact NUMBER := -1;
15 g_index_bal NUMBER := -1;
16 g_package VARCHAR2(33) := ' PAY_FI_ARCHIVE_TEL.';
17 g_payroll_action_id NUMBER;
18
19 g_arc_payroll_action_id NUMBER;
20 -- Record for Absence
21 TYPE ABSENCES IS RECORD
22 (
23 CATEGORY VARCHAR2(240),
24 START_DATE DATE,
25 END_DATE DATE
26 );
27
28 TYPE ABSENCES_RECORD
29 IS TABLE OF ABSENCES
30 INDEX BY BINARY_INTEGER;
31
32 ABS_RECORDS ABSENCES_RECORD;
33
34 -- Globals to pick up all the parameter
35 g_business_group_id NUMBER;
36 g_effective_date DATE;
37 g_pension_provider_id NUMBER;
38 g_pension_ins_num varchar2(11);
39 g_legal_employer_id NUMBER;
40 g_local_unit_id NUMBER;
41 g_archive varchar2(20);
42 g_annual_report varchar2(20);
43 g_ref_date DATE;
44
45
46 --End of Globals to pick up all the parameter
47 g_format_mask VARCHAR2(50);
48 g_err_num NUMBER;
49 g_errm VARCHAR2(150);
50
51 /* GET PARAMETER */
52 FUNCTION GET_PARAMETER(
53 p_parameter_string IN VARCHAR2
54 ,p_token IN VARCHAR2
55 ,p_segment_number IN NUMBER default NULL ) RETURN VARCHAR2
56 IS
57 l_parameter pay_payroll_actions.legislative_parameters%TYPE:=NULL;
58 l_start_pos NUMBER;
59 l_delimiter VARCHAR2(1):=' ';
60 l_proc VARCHAR2(40):= g_package||' get parameter ';
61
62 BEGIN
63 --
64 IF g_debug THEN
65 hr_utility.set_location(' Entering Function GET_PARAMETER',10);
66 END IF;
67 l_start_pos := instr(' '||p_parameter_string,l_delimiter||p_token||'=');
68 --
69 IF l_start_pos = 0 THEN
70 l_delimiter := '|';
71 l_start_pos := instr(' '||p_parameter_string,l_delimiter||p_token||'=');
72 END IF;
73 IF l_start_pos <> 0 THEN
74 l_start_pos := l_start_pos + length(p_token||'=');
75 l_parameter := substr(p_parameter_string,
76 l_start_pos,
77 instr(p_parameter_string||' ',
78 l_delimiter,l_start_pos)
79 - l_start_pos);
80 IF p_segment_number IS NOT NULL THEN
81 l_parameter := ':'||l_parameter||':';
82 l_parameter := substr(l_parameter,
83 instr(l_parameter,':',1,p_segment_number)+1,
84 instr(l_parameter,':',1,p_segment_number+1) -1
85 - instr(l_parameter,':',1,p_segment_number));
86 END IF;
87 END IF;
88 --
89 RETURN l_parameter;
90 IF g_debug THEN
91 hr_utility.set_location(' Leaving Function GET_PARAMETER',20);
92 END IF;
93 END;
94 /* GET ALL PARAMETERS */
95 PROCEDURE GET_ALL_PARAMETERS(
96 p_payroll_action_id IN NUMBER -- In parameter
97 ,p_business_group_id OUT NOCOPY NUMBER -- Core parameter
98 ,p_effective_date OUT NOCOPY Date -- Core parameter
99 ,p_pension_ins_num OUT NOCOPY VARCHAR2 -- User parameter
100 ,p_legal_employer_id OUT NOCOPY NUMBER -- User parameter
101 ,p_local_unit_id OUT NOCOPY NUMBER -- User parameter
102 ,p_annual_report OUT NOCOPY VARCHAR2 -- User parameter
103 ,p_ref_date OUT NOCOPY Date -- User parameter
104 ,p_archive OUT NOCOPY VARCHAR2 -- User parameter
105 )
106 IS
107 CURSOR csr_parameter_info(p_payroll_action_id NUMBER) IS
108 SELECT
109 PAY_FI_ARCHIVE_TEL.GET_PARAMETER(legislative_parameters,'PENSION_INS_NUM') PPID
110 ,TO_NUMBER ( PAY_FI_ARCHIVE_TEL.GET_PARAMETER(legislative_parameters,'LEGAL_EMPLOYER_ID') ) Legal
111 ,TO_NUMBER (PAY_FI_ARCHIVE_TEL.GET_PARAMETER(legislative_parameters,'LOCAL_UNIT_ID') ) Local_unit
112 ,PAY_FI_ARCHIVE_TEL.GET_PARAMETER(legislative_parameters,'REPORT') Annual_Report
113 ,FND_DATE.CANONICAL_TO_DATE(PAY_FI_ARCHIVE_TEL.GET_PARAMETER(legislative_parameters,'REF_DATE'))
114 ,PAY_FI_ARCHIVE_TEL.GET_PARAMETER(legislative_parameters,'ARCHIVE') ARCHIVE_OR_NOT
115 -- ,PAY_FI_ARCHIVE_TEL.GET_PARAMETER(legislative_parameters,'TEST') TEST_OR_NOT
116 ,effective_date Effective_date
117 ,business_group_id BG_ID FROM pay_payroll_actions
118 WHERE payroll_action_id = p_payroll_action_id;
119
120 lr_parameter_info csr_parameter_info%ROWTYPE;
121
122 l_proc VARCHAR2(240):= g_package||' GET_ALL_PARAMETERS ';
123
124 BEGIN
125 fnd_file.put_line(fnd_file.log,'Entering Procedure GET_ALL_PARAMETER ');
126 fnd_file.put_line(fnd_file.log,'Payroill Action iD ' || p_payroll_action_id );
127 OPEN csr_parameter_info (p_payroll_action_id);
128 --FETCH csr_parameter_info into lr_parameter_info;
129 FETCH csr_parameter_info
130 INTO p_pension_ins_num
131 ,p_legal_employer_id
132 ,p_local_unit_id
133 ,p_annual_report
134 ,p_ref_date
135 ,p_archive
136 ,p_effective_date
137 ,p_business_group_id;
138 CLOSE csr_parameter_info;
139
140 fnd_file.put_line(fnd_file.log,'After csr_parameter_info in ' );
141 fnd_file.put_line(fnd_file.log,'After p_pension_provider_id ' || g_pension_provider_id);
142 fnd_file.put_line(fnd_file.log,'After p_legal_employer_id in ' || p_legal_employer_id);
143 fnd_file.put_line(fnd_file.log,'After p_local_unit_id in ' || p_local_unit_id );
144 fnd_file.put_line(fnd_file.log,'After p_archive' || p_archive );
145
146 IF g_debug THEN
147 hr_utility.set_location(' Leaving Procedure GET_ALL_PARAMETERS',30);
148 END IF;
149 END GET_ALL_PARAMETERS;
150
151
152 /* RANGE CODE */
153 PROCEDURE RANGE_CODE (p_payroll_action_id IN NUMBER
154 ,p_sql OUT NOCOPY VARCHAR2)
155 IS
156 l_action_info_id NUMBER;
157 l_ovn NUMBER;
158 l_business_group_id NUMBER;
159 l_start_date VARCHAR2(30);
160 l_end_date VARCHAR2(30);
161 l_effective_date DATE;
162 l_consolidation_set NUMBER;
163 l_defined_balance_id NUMBER := 0;
164 l_count NUMBER := 0;
165 l_prev_prepay NUMBER := 0;
166 l_canonical_start_date DATE;
167 l_canonical_end_date DATE;
168 l_payroll_id NUMBER;
169 l_prepay_action_id NUMBER;
170 l_actid NUMBER;
171 l_assignment_id NUMBER;
172 l_action_sequence NUMBER;
173 l_assact_id NUMBER;
174 l_pact_id NUMBER;
175 l_flag NUMBER := 0;
176 l_element_context VARCHAR2(5);
177
178
179 -- Archiving the data , as this will fire once
180
181 Cursor csr_pension_provider
182 ( csr_v_legal_emp_id hr_organization_information.ORGANIZATION_ID%TYPE
183 , csr_v_pension_ins_num hr_organization_information.org_information1%TYPE
184 ,csr_v_effective_date DATE )
185 IS
186 SELECT o1.name,
187 hoi2.ORG_INFORMATION3,
188 hoi2.ORG_INFORMATION4,
189 hoi2.ORG_INFORMATION5,
190 hoi2.ORG_INFORMATION8
191 FROM hr_organization_units o1
192 , hr_organization_information hoi1
193 , hr_organization_information hoi2
194 WHERE o1.business_group_id =g_business_group_id
195 AND hoi1.organization_id = o1.organization_id
196 AND hoi1.organization_id = csr_v_legal_emp_id
197 AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
198 AND hoi1.org_information_context = 'CLASS'
199 AND o1.organization_id =hoi2.organization_id
200 AND hoi2.ORG_INFORMATION_CONTEXT='FI_PENSION_PROVIDERS'
201 AND hoi2.org_information6=csr_v_pension_ins_num;
202
203
204 lr_pension_provider csr_pension_provider%ROWTYPE;
205
206 CURSOR csr_pension_provider_details (
207 csr_v_pension_provider_id hr_organization_information.organization_id%TYPE
208 )
209 IS
210 SELECT o1.NAME
211 FROM hr_organization_units o1
212 , hr_organization_information hoi1
213 WHERE o1.business_group_id =g_business_group_id
214 AND hoi1.organization_id = o1.organization_id
215 AND hoi1.organization_id = csr_v_pension_provider_id
216 AND hoi1.org_information_context = 'CLASS'
217 AND hoi1.org_information1 = 'FR_PENSION' ;
218
219 lr_pension_provider_details csr_pension_provider_details%ROWTYPE;
220
221 /*
222 CURSOR csr_Pension_group_code (
223 csr_v_pension_provider_id hr_organization_information.organization_id%TYPE
224 )
225 IS
226 SELECT o1.NAME,hoi2.ORG_INFORMATION2
227 FROM hr_organization_units o1
228 , hr_organization_information hoi1
229 , hr_organization_information hoi2
230 WHERE o1.business_group_id =g_business_group_id
231 AND hoi1.organization_id = o1.organization_id
232 AND hoi1.organization_id = csr_v_pension_provider_id
233 AND hoi1.org_information_context = 'CLASS'
234 AND hoi1.org_information1 = 'FR_PENSION'
235 AND o1.organization_id =hoi2.organization_id
236 AND hoi2.ORG_INFORMATION_CONTEXT='FI_PENSION_TYPES'
237 AND hoi2.org_information1='TEL';
238
239 Lr_Pension_group_code csr_Pension_group_code%ROWTYPE;
240
241 CURSOR csr_Department_code (
242 csr_v_pension_provider_id hr_organization_information.organization_id%TYPE
243 )
244 IS
245 SELECT hoi2.ORG_INFORMATION3
246 FROM hr_organization_units o1
247 , hr_organization_information hoi1
248 , hr_organization_information hoi2
249 WHERE o1.business_group_id =g_business_group_id
250 AND hoi1.organization_id = o1.organization_id
251 AND hoi1.organization_id = csr_v_pension_provider_id
252 AND hoi1.org_information_context = 'CLASS'
253 AND hoi1.org_information1 = 'FR_PENSION'
254 AND o1.organization_id =hoi2.organization_id
255 AND hoi2.ORG_INFORMATION_CONTEXT='FI_PENSION_DEPARTMENT_CODES';
256
257 lr_Department_code csr_Department_code%ROWTYPE;
258 */
259 -- Cursor to pick up Local Unit Details
260 Cursor csr_Local_Unit_Details ( csr_v_local_unit_id hr_organization_information.ORGANIZATION_ID%TYPE)
261 IS
262 SELECT o1.name , hoi2.ORG_INFORMATION1
263 FROM hr_organization_units o1
264 , hr_organization_information hoi1
265 , hr_organization_information hoi2
266 WHERE o1.business_group_id =g_business_group_id
267 AND hoi1.organization_id = o1.organization_id
268 AND hoi1.organization_id = csr_v_local_unit_id
269 AND hoi1.org_information1 = 'FI_LOCAL_UNIT'
270 AND hoi1.org_information_context = 'CLASS'
271 AND o1.organization_id =hoi2.organization_id
272 AND hoi2.ORG_INFORMATION_CONTEXT='FI_LOCAL_UNIT_DETAILS';
273
274 lr_Local_Unit_Details csr_Local_Unit_Details%rowtype;
275
276 CURSOR csr_all_local_unit_details (
277 csr_v_legal_employer_id hr_organization_information.organization_id%TYPE )
278 IS
279 SELECT hoi_le.org_information1 local_unit_id,
280 hou_lu.NAME local_unit_name,
281 hoi_lu.org_information1
282 FROM hr_organization_units hou_le,
283 hr_organization_information hoi_le,
284 hr_organization_units hou_lu,
285 hr_organization_information hoi_lu
286 WHERE hoi_le.organization_id = hou_le.organization_id
287 AND hou_le.organization_id = csr_v_legal_employer_id
288 AND hoi_le.org_information_context = 'FI_LOCAL_UNITS'
289 AND hou_lu.organization_id = hoi_le.org_information1
290 AND hou_lu.organization_id = hoi_lu.organization_id
291 AND hoi_lu.org_information_context = 'FI_LOCAL_UNIT_DETAILS';
292
293 Cursor csr_lu_pp_dtls (
294 csr_v_local_unit_id hr_organization_information.ORGANIZATION_ID%TYPE
295 , csr_v_pension_ins_num hr_organization_information.org_information1%TYPE)
296 IS
297 SELECT hoi2.ORG_INFORMATION2
298 FROM hr_organization_units o1
299 , hr_organization_information hoi1
300 , hr_organization_information hoi2
301 WHERE o1.business_group_id =l_business_group_id
302 AND hoi1.organization_id = o1.organization_id
303 AND hoi1.organization_id = csr_v_local_unit_id
304 AND hoi1.org_information1 = 'FI_LOCAL_UNIT'
305 AND hoi1.org_information_context = 'CLASS'
306 AND o1.organization_id =hoi2.organization_id
307 AND hoi2.ORG_INFORMATION_CONTEXT='FI_LU_PENSION_PROVIDERS'
308 AND hoi2.org_information1=csr_v_pension_ins_num ;
309
310 lr_lu_pp_dtls csr_lu_pp_dtls%rowtype;
311
312
313 l_Insurance_pol_number VARCHAR2(240); --Pension Insurance Number
314 l_Pension_group_code varchar2(240); --
315 l_Department varchar2(240); --sub disbursement Number
316 l_old_Department varchar2(240);
317
318 -- Archiving the data , as this will fire once
319
320 BEGIN
321
322 fnd_file.put_line(fnd_file.log,'In RANGE_CODE 0');
323
324 IF g_debug THEN
325 hr_utility.set_location(' Entering Procedure RANGE_CODE',40);
326 END IF;
327
328 p_sql := 'SELECT DISTINCT person_id
329 FROM per_people_f ppf
330 ,pay_payroll_actions ppa
331 WHERE ppa.payroll_action_id = :payroll_action_id
332 AND ppa.business_group_id = ppf.business_group_id
333 ORDER BY ppf.person_id';
334
335 g_payroll_action_id :=p_payroll_action_id;
336 g_business_group_id := null;
337 g_effective_date := null;
338 g_pension_provider_id := null;
339 g_pension_ins_num := null;
340 g_legal_employer_id := null;
341 g_local_unit_id := null;
342 g_annual_report:= null;
343 g_archive := null;
344
345 PAY_FI_ARCHIVE_TEL.GET_ALL_PARAMETERS
346 (p_payroll_action_id
347 ,g_business_group_id
348 ,g_effective_date
349 ,g_pension_ins_num
350 ,g_legal_employer_id
351 ,g_local_unit_id
352 ,g_annual_report
353 ,g_ref_date
354 ,g_archive
355 );
356
357 pay_balance_pkg.set_context('TAX_UNIT_ID',g_legal_employer_id);
358 pay_balance_pkg.set_context('LOCAL_UNIT_ID',g_local_unit_id);
359 pay_balance_pkg.set_context('DATE_EARNED',fnd_date.date_to_canonical(g_ref_date));
360 pay_balance_pkg.set_context('JURISDICTION_CODE',NULL);
361 pay_balance_pkg.set_context('SOURCE_ID',NULL);
362 pay_balance_pkg.set_context('TAX_GROUP',NULL);
363 pay_balance_pkg.set_context('ORGANIZATION_ID',g_pension_provider_id);
364
365 IF g_archive = 'Y'
366 THEN
367 -- *****************************************************************************
368 -- TO pick up the required details for Pension Providers
369
370 OPEN csr_pension_provider( g_legal_employer_id,
371 g_pension_ins_num,
372 g_ref_date);
373 FETCH csr_pension_provider
374 INTO lr_pension_provider;
375 CLOSE csr_pension_provider;
376 -- *****************************************************************************
377 /*
378 OPEN csr_pension_group_code(g_pension_provider_id);
379 FETCH csr_pension_group_code
380 INTO lr_pension_group_code;
381 CLOSE csr_pension_group_code;
382 OPEN csr_department_code(g_pension_provider_id);
383 FETCH csr_department_code
384 INTO lr_department_code;
385 CLOSE csr_department_code;
386
387 l_Insurance_pol_number :=lr_pension_provider.ORG_INFORMATION8;
388 l_Pension_group_code :=lr_pension_provider.ORG_INFORMATION5;
389 fnd_file.put_line(fnd_file.log,'l_Insurance_pol_number ==> '||l_Insurance_pol_number );
390 fnd_file.put_line(fnd_file.log,'l_Pension_group_code ==> '||l_Pension_group_code );
391 */
392 -- *****************************************************************************
393 -- To pick Up the Pension Provider Name
394 OPEN csr_pension_provider_details(lr_pension_provider.org_information4);
395 FETCH csr_pension_provider_details INTO lr_pension_provider_details;
396 CLOSE csr_pension_provider_details;
397 -- *****************************************************************************
398 -- To pick Up the Local Unit Name
399 OPEN csr_Local_Unit_Details( g_local_unit_id );
400 FETCH csr_Local_Unit_Details
401 INTO lr_Local_Unit_Details;
402 CLOSE csr_Local_Unit_Details;
403 -- *****************************************************************************
404 -- If local Unit is given
405 /*
406 IF g_local_unit_id IS NOT NULL
407 THEN
408 -- *********************************************************************
409 -- To pick up the Sub-disbursement Number for the given Local Unit
410 OPEN csr_Local_Unit_Details( g_local_unit_id );
411 FETCH csr_Local_Unit_Details
412 INTO lr_Local_Unit_Details;
413 CLOSE csr_Local_Unit_Details;
414 -- *********************************************************************
415
416 pay_action_information_api.create_action_information (
417 p_action_information_id=> l_action_info_id,
418 p_action_context_id=> p_payroll_action_id,
419 p_action_context_type=> 'PA',
420 p_object_version_number=> l_ovn,
421 p_effective_date=> g_effective_date,
422 p_source_id=> NULL,
423 p_source_text=> NULL,
424 p_action_information_category=> 'EMEA REPORT INFORMATION',
425 p_action_information1=> 'PYFITELA',
426 p_action_information2=> 'LU',
427 p_action_information3=> g_local_unit_id,
428 p_action_information4=> lr_local_unit_details.NAME,
429 p_action_information5=> lr_local_unit_details.ORG_INFORMATION1,
430 p_action_information6=> NULL,
431 p_action_information7=> NULL,
432 p_action_information8=> NULL,
433 p_action_information9=> NULL,
434 p_action_information10=> NULL,
435 p_action_information11=> NULL,
436 p_action_information12=> NULL,
437 p_action_information13=> NULL,
438 p_action_information14=> NULL,
439 p_action_information15=> NULL,
440 p_action_information16=> NULL,
441 p_action_information17=> NULL,
442 p_action_information18=> NULL,
443 p_action_information19=> NULL,
444 p_action_information20=> NULL,
445 p_action_information21=> NULL,
446 p_action_information22=> NULL,
447 p_action_information23=> NULL,
448 p_action_information24=> NULL,
449 p_action_information25=> NULL,
450 p_action_information26=> NULL,
451 p_action_information27=> NULL,
452 p_action_information28=> NULL,
453 p_action_information29=> NULL,
454 p_action_information30=> NULL
455 );
456 -- *********************************************************************
457
458 fnd_file.put_line(fnd_file.log,' ================================ ' );
459 fnd_file.put_line(fnd_file.log,'Local Unit Not Null' );
460 fnd_file.put_line(fnd_file.log,'Name ==> '||lr_local_unit_details.NAME || ' ID==> ' ||g_local_unit_id);
461 fnd_file.put_line(fnd_file.log,'sub disburse ==> '||lr_local_unit_details.ORG_INFORMATION1 );
462 fnd_file.put_line(fnd_file.log,'acti_info_id ==> '||l_action_info_id );
463 fnd_file.put_line(fnd_file.log,' ================================ ' );
464 -- *****************************************************************************
465 -- If the Local unit is not selected then pick up all the local unit details
466 ELSE
467 FOR lr_all_local_unit_details IN
468 csr_all_local_unit_details (g_legal_employer_id)
469 LOOP
470 pay_action_information_api.create_action_information (
471 p_action_information_id=> l_action_info_id,
472 p_action_context_id=> p_payroll_action_id,
473 p_action_context_type=> 'PA',
474 p_object_version_number=> l_ovn,
475 p_effective_date=> g_effective_date,
476 p_source_id=> NULL,
477 p_source_text=> NULL,
478 p_action_information_category=> 'EMEA REPORT INFORMATION',
479 p_action_information1=> 'PYFITELA',
480 p_action_information2=> 'LU',
481 p_action_information3=> lr_all_local_unit_details.local_unit_id,
482 p_action_information4=> lr_all_local_unit_details.local_unit_name,
483 p_action_information5=> lr_all_local_unit_details.ORG_INFORMATION1,
484 p_action_information6=> NULL,
485 p_action_information7=> NULL,
486 p_action_information8=> NULL,
487 p_action_information9=> NULL,
488 p_action_information10=> NULL,
489 p_action_information11=> NULL,
490 p_action_information12=> NULL,
491 p_action_information13=> NULL,
492 p_action_information14=> NULL,
493 p_action_information15=> NULL,
494 p_action_information16=> NULL,
495 p_action_information17=> NULL,
496 p_action_information18=> NULL,
497 p_action_information19=> NULL,
498 p_action_information20=> NULL,
499 p_action_information21=> NULL,
500 p_action_information22=> NULL,
501 p_action_information23=> NULL,
502 p_action_information24=> NULL,
503 p_action_information25=> NULL,
504 p_action_information26=> NULL,
505 p_action_information27=> NULL,
506 p_action_information28=> NULL,
507 p_action_information29=> NULL,
508 p_action_information30=> NULL
509 );
510 END LOOP;
511 -- *****************************************************************************
512 END IF; -- END OF G_LOCAL_UNIT_ID NULL CHECK
513 */
514 -- *****************************************************************************
515 -- Insert the report Parameters
516 pay_action_information_api.create_action_information (
517 p_action_information_id=> l_action_info_id,
518 p_action_context_id=> p_payroll_action_id,
519 p_action_context_type=> 'PA',
520 p_object_version_number=> l_ovn,
521 p_effective_date=> g_effective_date,
522 p_source_id=> NULL,
523 p_source_text=> NULL,
524 p_action_information_category=> 'EMEA REPORT DETAILS',
525 p_action_information1=> 'PYFITELA',
526 p_action_information2=> lr_pension_provider_details.NAME,
527 p_action_information3=> lr_pension_provider.org_information4,
528 p_action_information4=> lr_pension_provider.NAME,
529 p_action_information5=> g_legal_employer_id,
530 p_action_information6=> lr_local_unit_details.NAME,
531 p_action_information7=> g_local_unit_id,
532 p_action_information8=> g_annual_report,
533 p_action_information9=> fnd_date.date_to_canonical ( g_ref_date ),
534 p_action_information10=> g_pension_ins_num,
535 p_action_information11=> NULL,
536 p_action_information12=> NULL,
537 p_action_information13=> NULL,
538 p_action_information14=> NULL,
539 p_action_information15=> NULL,
540 p_action_information16=> NULL,
541 p_action_information17=> NULL,
542 p_action_information18=> NULL,
543 p_action_information19=> NULL,
544 p_action_information20=> NULL,
545 p_action_information21=> NULL,
546 p_action_information22=> NULL,
547 p_action_information23=> NULL,
548 p_action_information24=> NULL,
549 p_action_information25=> NULL,
550 p_action_information26=> NULL,
551 p_action_information27=> NULL,
552 p_action_information28=> NULL,
553 p_action_information29=> NULL,
554 p_action_information30=> NULL
555 );
556 -- *****************************************************************************
557 fnd_file.put_line(fnd_file.log,' ================ ALL ================ ' );
558 fnd_file.put_line(fnd_file.log,'PENSION provider name ==> '||lr_pension_provider_details.NAME );
559 fnd_file.put_line(fnd_file.log,'PENSION provider ID ==> '||g_pension_provider_id);
560 fnd_file.put_line(fnd_file.log,'Legal Emp Name ==> '||lr_pension_provider.NAME);
561 fnd_file.put_line(fnd_file.log,'Legal Emp ID ==> '||g_legal_employer_id);
562 fnd_file.put_line(fnd_file.log,'Local Unit Name ==> '||lr_local_unit_details.NAME);
563 fnd_file.put_line(fnd_file.log,'Local Unit ID ==> '||g_local_unit_id);
564 fnd_file.put_line(fnd_file.log,'acti_info_id ==> '||l_action_info_id );
565 fnd_file.put_line(fnd_file.log,' ================================ ' );
566
567 -- *****************************************************************************
568 -- Information regarding the Legal Employer
569 /*
570 pay_action_information_api.create_action_information (
571 p_action_information_id=> l_action_info_id,
572 p_action_context_id=> p_payroll_action_id,
573 p_action_context_type=> 'PA',
574 p_object_version_number=> l_ovn,
575 p_effective_date=> g_effective_date,
576 p_source_id=> NULL,
577 p_source_text=> NULL,
578 p_action_information_category=> 'EMEA REPORT INFORMATION',
579 p_action_information1=> 'PYFITELA',
580 p_action_information2=> 'LE',
581 p_action_information3=> g_legal_employer_id ,
582 p_action_information4=> lr_pension_provider.NAME,
583 p_action_information5=> lr_pension_provider.ORG_INFORMATION5,
584 p_action_information6=> lr_pension_provider.ORG_INFORMATION6,
585 p_action_information7=> lr_pension_provider.ORG_INFORMATION8,
586 p_action_information8=> NULL,
587 p_action_information9=> NULL,
588 p_action_information10=> NULL,
589 p_action_information11=> NULL,
590 p_action_information12=> NULL,
591 p_action_information13=> NULL,
592 p_action_information14=> NULL,
593 p_action_information15=> NULL,
594 p_action_information16=> NULL,
595 p_action_information17=> NULL,
596 p_action_information18=> NULL,
597 p_action_information19=> NULL,
598 p_action_information20=> NULL,
599 p_action_information21=> NULL,
600 p_action_information22=> NULL,
601 p_action_information23=> NULL,
602 p_action_information24=> NULL,
603 p_action_information25=> NULL,
604 p_action_information26=> NULL,
605 p_action_information27=> NULL,
606 p_action_information28=> NULL,
607 p_action_information29=> NULL,
608 p_action_information30=> NULL
609 );
610 */
611 -- *****************************************************************************
612
613
614 END IF; -- G_Archive End
615
616 IF g_debug THEN
617 hr_utility.set_location(' Leaving Procedure RANGE_CODE',50);
618 END IF;
619 EXCEPTION
620 WHEN OTHERS
621 THEN
622 -- Return cursor that selects no rows
623 p_sql := 'select 1 from dual where to_char(:payroll_action_id) = dummy';
624 END RANGE_CODE;
625
626
627 /* ASSIGNMENT ACTION CODE */
628 PROCEDURE ASSIGNMENT_ACTION_CODE
629 (p_payroll_action_id IN NUMBER
630 ,p_start_person IN NUMBER
631 ,p_end_person IN NUMBER
632 ,p_chunk IN NUMBER)
633 IS
634 CURSOR csr_prepaid_assignments_lu(p_payroll_action_id NUMBER,
635 p_start_person NUMBER,
636 p_end_person NUMBER,
637 p_legal_employer_id NUMBER,
638 p_local_unit_id NUMBER,
639 p_pension_ins_num VARCHAR2,
640 l_canonical_start_date DATE,
641 l_canonical_end_date DATE)
642 IS
643 SELECT act.assignment_id assignment_id,
644 act.assignment_action_id run_action_id,
645 act1.assignment_action_id prepaid_action_id
646 FROM pay_payroll_actions ppa
647 ,pay_payroll_actions appa
648 ,pay_payroll_actions appa2
649 ,pay_assignment_actions act
650 ,pay_assignment_actions act1
651 ,pay_action_interlocks pai
652 ,per_all_assignments_f as1
653 ,hr_soft_coding_keyflex hsck
654 , per_all_people_f pap
655 WHERE ppa.payroll_action_id = p_payroll_action_id
656 AND appa.effective_date BETWEEN l_canonical_start_date
657 AND l_canonical_end_date
658 AND as1.person_id BETWEEN p_start_person
659 AND p_end_person
660 AND appa.action_type IN ('R','Q')
661 -- Payroll Run or Quickpay Run
662 AND act.payroll_action_id = appa.payroll_action_id
663 AND act.source_action_id IS NULL -- Master Action
664 AND as1.assignment_id = act.assignment_id
665 AND as1.person_id = pap.person_id
666 AND pap.per_information24 = p_pension_ins_num
667 AND ppa.effective_date BETWEEN as1.effective_start_date
668 AND as1.effective_end_date
669 AND ppa.effective_date BETWEEN pap.effective_start_date
670 AND pap.effective_end_date
671 AND act.action_status IN ('C','S') -- 10229501
672 AND act.assignment_action_id = pai.locked_action_id
673 AND act1.assignment_action_id = pai.locking_action_id
674 AND act1.action_status IN ('C','S') -- 10229501
675 AND act1.payroll_action_id = appa2.payroll_action_id
676 AND appa2.action_type IN ('P','U')
677 AND appa2.effective_date BETWEEN l_canonical_start_date
678 AND l_canonical_end_date
679 -- Prepayments or Quickpay Prepayments
680 AND hsck.SOFT_CODING_KEYFLEX_ID=as1.SOFT_CODING_KEYFLEX_ID
681 AND hsck.segment2 = to_char(p_local_unit_id)
682 AND act.TAX_UNIT_ID = act1.TAX_UNIT_ID
683 AND act.TAX_UNIT_ID = p_legal_employer_id
684 ORDER BY act.assignment_id;
685
686 CURSOR csr_prepaid_assignments_le(p_payroll_action_id NUMBER,
687 p_start_person NUMBER,
688 p_end_person NUMBER,
689 p_legal_employer_id NUMBER,
690 p_pension_ins_num VARCHAR2,
691 l_canonical_start_date DATE,
692 l_canonical_end_date DATE)
693 IS
694 SELECT act.assignment_id assignment_id,
695 act.assignment_action_id run_action_id,
696 act1.assignment_action_id prepaid_action_id
697 FROM pay_payroll_actions ppa,
698 pay_payroll_actions appa,
699 pay_payroll_actions appa2,
700 pay_assignment_actions act,
701 pay_assignment_actions act1,
702 pay_action_interlocks pai,
703 per_all_assignments_f as1
704 , per_all_people_f pap
705 WHERE ppa.payroll_action_id = p_payroll_action_id
706 AND appa.effective_date BETWEEN l_canonical_start_date
707 AND l_canonical_end_date
708 AND as1.person_id BETWEEN p_start_person
709 AND p_end_person
710 AND appa.action_type IN ('R','Q')
711 -- Payroll Run or Quickpay Run
712 AND act.payroll_action_id = appa.payroll_action_id
713 AND act.source_action_id IS NULL -- Master Action
714 AND as1.assignment_id = act.assignment_id
715 AND as1.person_id = pap.person_id
716 AND pap.per_information24 = p_pension_ins_num
717 AND ppa.effective_date BETWEEN as1.effective_start_date
718 AND as1.effective_end_date
719 AND ppa.effective_date BETWEEN pap.effective_start_date
720 AND pap.effective_end_date
721 AND act.action_status IN ('C','S') -- 10229501
722 AND act.assignment_action_id = pai.locked_action_id
723 AND act1.assignment_action_id = pai.locking_action_id
724 AND act1.action_status IN ('C','S') -- 10229501
725 AND act1.payroll_action_id = appa2.payroll_action_id
726 AND appa2.action_type IN ('P','U')
727 AND appa2.effective_date BETWEEN l_canonical_start_date
728 AND l_canonical_end_date
729 -- Prepayments or Quickpay Prepayments
730 AND act.TAX_UNIT_ID = act1.TAX_UNIT_ID
731 AND act.TAX_UNIT_ID = p_legal_employer_id
732 ORDER BY act.assignment_id;
733
734 Cursor csr_Get_Defined_Balance_Id(csr_v_Balance_Name FF_DATABASE_ITEMS.USER_NAME%TYPE)
735 IS
736 SELECT ue.creator_id
737 FROM ff_user_entities ue,
738 ff_database_items di
739 WHERE di.user_name = csr_v_Balance_Name
740 AND ue.user_entity_id = di.user_entity_id
741 AND ue.legislation_code = 'FI'
742 AND ue.business_group_id is NULL
743 AND ue.creator_type = 'B';
744
745 lr_Get_Defined_Balance_Id csr_Get_Defined_Balance_Id%rowtype;
746
747 l_count NUMBER := 0;
748 l_prev_prepay NUMBER := 0;
749
750 l_canonical_start_date DATE;
751 l_canonical_end_date DATE;
752 l_pension_type hr_organization_information.org_information1%TYPE ;
753
754
755 l_prepay_action_id NUMBER;
756 l_actid NUMBER;
757 l_assignment_id NUMBER;
758 l_action_sequence NUMBER;
759 l_assact_id NUMBER;
760 l_pact_id NUMBER;
761 l_flag NUMBER := 0;
762 l_defined_balance_id NUMBER :=0;
763 l_action_info_id NUMBER;
764 l_ovn NUMBER;
765 -- User pARAMETERS needed
766 l_business_group_id NUMBER;
767 l_effective_date DATE;
768
769 l_pension_provider_id NUMBER;
770 l_legal_employer_id NUMBER;
771 l_local_unit_id NUMBER;
772 l_archive varchar2(10);
773 -- End of User pARAMETERS needed
774
775 BEGIN
776 IF g_debug THEN
777 hr_utility.set_location(' Entering Procedure ASSIGNMENT_ACTION_CODE',60);
778 END IF;
779 fnd_file.put_line(fnd_file.log,' ASSIGNMENT_ACTION_CODE ');
780
781 PAY_FI_ARCHIVE_TEL.GET_ALL_PARAMETERS
782 (p_payroll_action_id
783 ,g_business_group_id
784 ,g_effective_date
785 ,g_pension_ins_num
786 ,g_legal_employer_id
787 ,g_local_unit_id
788 ,g_annual_report
789 ,g_ref_date
790 ,g_archive
791 );
792
793 IF g_annual_report ='M'
794 THEN
795 l_canonical_start_date :=LAST_DAY(ADD_MONTHS(g_ref_date , -1) ) + 1 ;
796 l_canonical_end_date := LAST_DAY(g_ref_date);
797 ELSIF g_annual_report ='Q'
798 THEN
799 l_canonical_start_date := TRUNC(g_ref_date, 'Q') ;
800 l_canonical_end_date := ADD_MONTHS(last_day(TRUNC(g_ref_date, 'Q') ), 2);
801 ELSIF g_annual_report ='A'
802 THEN
803 l_canonical_start_date :=TRUNC(g_ref_date, 'Y') ;
804 l_canonical_end_date := ADD_MONTHS(last_day(TRUNC(g_ref_date, 'Y') ), 11);
805 END IF;
806
807 l_prepay_action_id := 0;
808
809 fnd_file.put_line(fnd_file.log,' g_local_unit_id '|| g_local_unit_id);
810
811 IF g_local_unit_id IS NOT NULL THEN
812 fnd_file.put_line(fnd_file.log,' INSIDE IF LOCAL UNIT NOT NULL ');
813
814
815 fnd_file.put_line(fnd_file.log,' p_payroll_action_id ==> ' || p_payroll_action_id);
816 fnd_file.put_line(fnd_file.log,' g_legal_employer_id ==> ' || g_legal_employer_id);
817 fnd_file.put_line(fnd_file.log,' g_local_unit_id ==> ' || g_local_unit_id);
818 fnd_file.put_line(fnd_file.log,' g_pension_provider_id ==> ' || g_pension_provider_id);
819 fnd_file.put_line(fnd_file.log,' g_effective_date ==> ' || g_effective_date);
820 fnd_file.put_line(fnd_file.log,' l_canonical_start_date ==> ' || l_canonical_start_date);
821 fnd_file.put_line(fnd_file.log,' l_canonical_end_date ==> ' || l_canonical_end_date);
822
823 FOR rec_prepaid_assignments IN csr_prepaid_assignments_lu(p_payroll_action_id
824 ,p_start_person
825 ,p_end_person
826 ,g_legal_employer_id
827 ,g_local_unit_id
828 ,g_pension_ins_num
829 ,l_canonical_start_date
830 ,l_canonical_end_date)
831 LOOP
832 fnd_file.put_line(fnd_file.log,' LU Inside the Csr Prepaid Cursor ');
833 IF l_prepay_action_id <> rec_prepaid_assignments.prepaid_action_id THEN
834 SELECT pay_assignment_actions_s.NEXTVAL
835 INTO l_actid
836 FROM dual;
837 --
838 g_index_assact := g_index_assact + 1;
839 g_lock_table(g_index_assact).archive_assact_id := l_actid; /* For Element archival */
840 -- Create the archive assignment action
841 hr_nonrun_asact.insact(l_actid
842 ,rec_prepaid_assignments.assignment_id
843 ,p_payroll_action_id
844 ,p_chunk
845 ,NULL);
846 -- Create archive to prepayment assignment action interlock
847 --
848 --hr_nonrun_asact.insint(l_actid,rec_prepaid_assignments.prepaid_action_id);
849 END IF;
850 -- create archive to master assignment action interlock
851 --hr_nonrun_asact.insint(l_actid,rec_prepaid_assignments.run_action_id);
852 l_prepay_action_id := rec_prepaid_assignments.prepaid_action_id;
853 END LOOP;
854
855 ELSE
856 fnd_file.put_line(fnd_file.log,' INSIDE ELS LOCAL UNIT NULL ');
857 FOR rec_prepaid_assignments IN csr_prepaid_assignments_le(p_payroll_action_id
858 ,p_start_person
859 ,p_end_person
860 ,g_legal_employer_id
861 ,g_pension_ins_num
862 ,l_canonical_start_date
863 ,l_canonical_end_date)
864 LOOP
865 fnd_file.put_line(fnd_file.log,' LE Inside the Csr Prepaid Cursor ');
866 IF l_prepay_action_id <> rec_prepaid_assignments.prepaid_action_id THEN
867 SELECT pay_assignment_actions_s.NEXTVAL
868 INTO l_actid
869 FROM dual;
870 --
871 g_index_assact := g_index_assact + 1;
872 g_lock_table(g_index_assact).archive_assact_id := l_actid; /* For Element archival */
873 -- Create the archive assignment action
874 hr_nonrun_asact.insact(l_actid
875 ,rec_prepaid_assignments.assignment_id
876 ,p_payroll_action_id
877 ,p_chunk
878 ,NULL);
879 -- Create archive to prepayment assignment action interlock
880 --
881 --hr_nonrun_asact.insint(l_actid,rec_prepaid_assignments.prepaid_action_id);
882 END IF;
883 -- create archive to master assignment action interlock
884 --hr_nonrun_asact.insint(l_actid,rec_prepaid_assignments.run_action_id);
885 l_prepay_action_id := rec_prepaid_assignments.prepaid_action_id;
886 END LOOP;
887 END IF;
888 fnd_file.put_line(fnd_file.log,' After Ending Assignment Act Code the Locking Cursor ');
889
890 IF g_debug THEN
891 hr_utility.set_location(' Leaving Procedure ASSIGNMENT_ACTION_CODE',70);
892 END IF;
893 EXCEPTION
894 WHEN others THEN
895 IF g_debug THEN
896 hr_utility.set_location('error raised assignment_action_code ',5);
897 END if;
898 RAISE;
899
900 END ASSIGNMENT_ACTION_CODE;
901 /*fffffffffffffffffffffffffff*/
902
903 /* INITIALIZATION CODE */
904 PROCEDURE INITIALIZATION_CODE(p_payroll_action_id IN NUMBER)
905 IS
906 l_action_info_id NUMBER;
907 l_ovn NUMBER;
908 l_count NUMBER := 0;
909 l_business_group_id NUMBER;
910 l_start_date VARCHAR2(20);
911 l_end_date VARCHAR2(20);
912 l_effective_date DATE;
913 l_payroll_id NUMBER;
914 l_consolidation_set NUMBER;
915 l_prev_prepay NUMBER := 0;
916
917 BEGIN
918 IF g_debug THEN
919 hr_utility.set_location(' Entering Procedure INITIALIZATION_CODE',80);
920 END IF;
921 fnd_file.put_line(fnd_file.log,'In INIT_CODE 0');
922
923 g_payroll_action_id :=p_payroll_action_id;
924
925 g_business_group_id := null;
926 g_effective_date := null;
927 g_pension_ins_num := null;
928 g_legal_employer_id := null;
929 g_local_unit_id := null;
930 g_annual_report:= null;
931 g_archive := null;
932
933 PAY_FI_ARCHIVE_TEL.GET_ALL_PARAMETERS
934 (p_payroll_action_id
935 ,g_business_group_id
936 ,g_effective_date
937 ,g_pension_ins_num
938 ,g_legal_employer_id
939 ,g_local_unit_id
940 ,g_annual_report
941 ,g_ref_date
942 ,g_archive
943 );
944
945 fnd_file.put_line(fnd_file.log,'In the INITIALIZATION_CODE After Initiliazing the global parameter ' );
946
947 IF g_debug THEN
948 hr_utility.set_location(' Leaving Procedure INITIALIZATION_CODE',90);
949 END IF;
950 EXCEPTION
951 WHEN OTHERS
952 THEN
953 g_err_num := SQLCODE;
954 IF g_debug THEN
955 hr_utility.set_location('ORA_ERR: ' || g_err_num || 'In INITIALIZATION_CODE',180);
956 END IF;
957 END INITIALIZATION_CODE;
958
959
960 /* GET DEFINED BALANCE ID */
961 FUNCTION GET_DEFINED_BALANCE_ID(p_user_name IN VARCHAR2)
962 RETURN NUMBER
963 IS
964 /* Cursor to retrieve Defined Balance Id */
965 CURSOR csr_def_bal_id(p_user_name VARCHAR2) IS
966 SELECT u.creator_id
967 FROM ff_user_entities u,
968 ff_database_items d
969 WHERE d.user_name = p_user_name
970 AND u.user_entity_id = d.user_entity_id
971 AND (u.legislation_code = 'FI' )
972 AND (u.business_group_id IS NULL )
973 AND u.creator_type = 'B';
974
975 l_defined_balance_id ff_user_entities.user_entity_id%TYPE;
976 BEGIN
977 IF g_debug THEN
978 hr_utility.set_location(' Entering Function GET_DEFINED_BALANCE_ID',240);
979 END IF;
980 OPEN csr_def_bal_id(p_user_name);
981 FETCH csr_def_bal_id
982 INTO l_defined_balance_id;
983 CLOSE csr_def_bal_id;
984 RETURN l_defined_balance_id;
985 IF g_debug THEN
986 hr_utility.set_location(' Leaving Function GET_DEFINED_BALANCE_ID',250);
987 END IF;
988 END GET_DEFINED_BALANCE_ID;
989
990
991 /* ARCHIVE CODE */
992 PROCEDURE ARCHIVE_CODE(
993 p_assignment_action_id IN NUMBER
994 ,p_effective_date IN DATE)
995 IS
996 /* Cursor to retrieve Archive Payroll Action Id */
997 Cursor csr_Get_Defined_Balance_Id(csr_v_Balance_Name FF_DATABASE_ITEMS.USER_NAME%TYPE)
998 IS
999 SELECT ue.creator_id
1000 FROM ff_user_entities ue,
1001 ff_database_items di
1002 WHERE di.user_name = csr_v_Balance_Name
1003 AND ue.user_entity_id = di.user_entity_id
1004 AND ue.legislation_code = 'FI'
1005 AND ue.business_group_id is NULL
1006 AND ue.creator_type = 'B';
1007
1008 lr_Get_Defined_Balance_Id csr_Get_Defined_Balance_Id%rowtype;
1009
1010 l_archive_payact_id NUMBER;
1011 l_record_count NUMBER;
1012 l_actid NUMBER;
1013 l_end_date per_time_periods.end_date%TYPE;
1014 l_pre_end_date per_time_periods.end_date%TYPE;
1015 l_reg_payment_date per_time_periods.regular_payment_date%TYPE;
1016 l_pre_reg_payment_date per_time_periods.regular_payment_date%TYPE;
1017 l_date_earned pay_payroll_actions.date_earned%TYPE;
1018 l_pre_date_earned pay_payroll_actions.date_earned%TYPE;
1019 l_effective_date pay_payroll_actions.effective_date%TYPE;
1020 l_pre_effective_date pay_payroll_actions.effective_date%TYPE;
1021 l_run_payact_id NUMBER;
1022 l_action_context_id NUMBER;
1023 g_archive_pact NUMBER;
1024 p_assactid NUMBER;
1025 l_time_period_id per_time_periods.time_period_id%TYPE;
1026 l_pre_time_period_id per_time_periods.time_period_id%TYPE;
1027 l_start_date per_time_periods.start_date%TYPE;
1028 l_pre_start_date per_time_periods.start_date%TYPE;
1029 l_fnd_session NUMBER := 0;
1030 l_prev_prepay NUMBER := 0;
1031
1032 l_action_info_id pay_action_information.action_information_id%TYPE;
1033 l_ovn pay_action_information.object_version_number%TYPE;
1034 l_flag number:=0;
1035
1036 -- The place for Variables which fetches the values to be archived
1037 l_Insurance_pol_number VARCHAR2(240); --Pension Insurance Number
1038 l_Employee_Pin VARCHAR2(240);
1039 l_Pension_group_code NUMBER; --
1040
1041 l_Employee_name VARCHAR2(240);
1042 l_Action_id VARCHAR2(2);
1043 l_Pension_Start_date DATE;
1044 l_Target_year NUMBER; -- Target year for Annual income 19YY, 20YY or blank.
1045 l_Income NUMBER;
1046 l_Benefit_in_Kind NUMBER;
1047 l_Termination_date DATE;
1048 l_Cause_of_termination VARCHAR2(240);
1049 l_Yearly_TEL_Income NUMBER;
1050 l_Benefit_in_Kind_prior NUMBER;
1051 l_LEL_Employment_start_date DATE;
1052 l_Shift_from_another DATE;
1053 l_Currency VARCHAR2(2) := '1'; -- 1 ==> EURO
1054 l_Employed_or_not varchar2(15);
1055
1056 l_old_pension_group_code VARCHAR2(240);
1057 l_new_pension_group_code VARCHAR2(240);
1058 l_old_Department VARCHAR2(240);
1059 l_new_Department VARCHAR2(240); --sub disbursement Number
1060 l_new_policy_number VARCHAR2(240);
1061 l_old_policy_number VARCHAR2(240);
1062 l_local_unit_id_fetched NUMBER;
1063 l_eit_local_unit NUMBER;
1064
1065
1066 -- Temp needed Variables
1067 l_person_id per_all_people_f.person_id%TYPE;
1068 l_assignment_id per_all_assignments_f.assignment_id%TYPE;
1069
1070
1071 l_EIT_Reported_or_not per_people_extra_info.PEI_INFORMATION5%TYPE;
1072 l_EIT_Insert_or_Update per_people_extra_info.PEI_INFORMATION6%TYPE;
1073 l_EIT_Last_reported per_people_extra_info.PEI_INFORMATION4%TYPE;
1074 l_current_value varchar2(240);
1075
1076 l_Sal_subject_pension_MTD NUMBER := 0;
1077 l_bik_subject_pension_MTD NUMBER := 0;
1078 l_tax_exp_subject_pension_MTD NUMBER := 0;
1079 l_Sal_subject_pension_QTD NUMBER := 0;
1080 l_bik_subject_pension_QTD NUMBER := 0;
1081 l_tax_exp_subject_pension_QTD NUMBER := 0;
1082
1083 l_Sal_subject_pension_YTD NUMBER := 0;
1084 l_bik_subject_pension_YTD NUMBER := 0;
1085 l_tax_exp_subject_pension_YTD NUMBER := 0;
1086
1087 l_sal_sub_pension_YTD_before NUMBER := 0;
1088 l_tax_exp_pension_YTD_before NUMBER :=0;
1089 l_bik_sub_pension_YTD_before NUMBER := 0;
1090 -- Temp needed Variables
1091
1092
1093 -- End of place for Variables which fetches the values to be archived
1094 -- The place for Cursor which fetches the values to be archived
1095
1096 --
1097 -- Cursor to pick up
1098 Cursor csr_pension_provider
1099 ( csr_v_legal_emp_id hr_organization_information.ORGANIZATION_ID%TYPE
1100 , csr_v_pension_ins_num hr_organization_information.org_information1%TYPE
1101 ,csr_v_effective_date DATE )
1102 IS
1103 SELECT hoi2.ORG_INFORMATION4, hoi2.ORG_INFORMATION5, hoi2.ORG_INFORMATION8, hoi2.ORG_INFORMATION10
1104 FROM hr_organization_units o1
1105 , hr_organization_information hoi1
1106 , hr_organization_information hoi2
1107 WHERE o1.business_group_id =g_business_group_id
1108 AND hoi1.organization_id = o1.organization_id
1109 AND hoi1.organization_id = csr_v_legal_emp_id
1110 AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
1111 AND hoi1.org_information_context = 'CLASS'
1112 AND o1.organization_id =hoi2.organization_id
1113 AND hoi2.ORG_INFORMATION_CONTEXT='FI_PENSION_PROVIDERS'
1114 AND hoi2.org_information6=csr_v_pension_ins_num
1115 AND csr_v_effective_date BETWEEN fnd_date.canonical_to_date(hoi2.org_information1) AND
1116 nvl(fnd_date.canonical_to_date(hoi2.org_information2),to_date('31/12/4712','DD/MM/YYYY')) ;
1117
1118 lr_pension_provider csr_pension_provider%ROWTYPE;
1119
1120 /* Cursor to retrieve Person Details */
1121 CURSOR csr_get_person_details(p_asg_act_id NUMBER)
1122 IS
1123 SELECT pap.LAST_NAME,pap.PRE_NAME_ADJUNCT,pap.FIRST_NAME,
1124 pap.national_identifier ,
1125 pap.person_id ,
1126 pap.PER_INFORMATION14,
1127 pap.PER_INFORMATION15,
1128 pap.PER_INFORMATION16,
1129 pap.PER_INFORMATION17,
1130 pap.PER_INFORMATION20,
1131 pap.PER_INFORMATION21,
1132 pap.PER_INFORMATION24,
1133 pac.assignment_id
1134 FROM
1135 pay_assignment_actions pac,
1136 per_all_assignments_f paa,
1137 per_all_people_f pap
1138 WHERE pac.assignment_action_id = p_asg_act_id
1139 AND paa.assignment_id = pac.assignment_id
1140 AND paa.person_id = pap.person_id
1141 AND pap.per_information_category = 'FI'
1142 and p_effective_date between pap.effective_Start_date AND pap.effective_end_date
1143 and p_effective_date between paa.effective_Start_date AND paa.effective_end_date;
1144
1145 lr_get_person_details csr_get_person_details%ROWTYPE;
1146
1147 -- Cursor to pick up segment2
1148 cursor csr_get_Segment2
1149 is
1150 SELECT scl.segment2,scl.segment8
1151 FROM PER_ALL_ASSIGNMENTS_F paa
1152 ,HR_SOFT_CODING_KEYFLEX scl
1153 ,pay_assignment_actions pasa
1154 WHERE pasa.ASSIGNMENT_ACTION_ID = p_assignment_action_id
1155 AND pasa.ASSIGNMENT_ID = paa.ASSIGNMENT_ID
1156 AND scl.soft_coding_keyflex_id=paa.soft_coding_keyflex_id
1157 AND paa.primary_flag='Y'
1158 AND p_effective_date between paa.effective_Start_date AND paa.effective_end_date;
1159
1160
1161 lr_get_Segment2 csr_get_Segment2%ROWTYPE;
1162
1163 -- Cursor to pick up Local Unit Details
1164 Cursor csr_Local_Unit_Details ( csr_v_local_unit_id hr_organization_information.ORGANIZATION_ID%TYPE)
1165 IS
1166 SELECT o1.name , hoi2.ORG_INFORMATION1
1167 FROM hr_organization_units o1
1168 , hr_organization_information hoi1
1169 , hr_organization_information hoi2
1170 WHERE o1.business_group_id =g_business_group_id
1171 AND hoi1.organization_id = o1.organization_id
1172 AND hoi1.organization_id = csr_v_local_unit_id
1173 AND hoi1.org_information1 = 'FI_LOCAL_UNIT'
1174 AND hoi1.org_information_context = 'CLASS'
1175 AND o1.organization_id =hoi2.organization_id
1176 AND hoi2.ORG_INFORMATION_CONTEXT='FI_LOCAL_UNIT_DETAILS';
1177
1178 lr_Local_Unit_Details csr_Local_Unit_Details%rowtype;
1179
1180 -- End of Cursors
1181
1182 CURSOR CSR_PERSON_EIT (
1183 CSR_V_PERSON_ID per_all_people_f.person_id%TYPE,
1184 CSR_V_COLUMN_NAME per_people_extra_info.PEI_INFORMATION3%TYPE )
1185 IS
1186 select PERSON_EXTRA_INFO_ID,
1187 object_version_number,
1188 person_id,
1189 information_type,
1190 pei_information_category,
1191 pei_information1,
1192 pei_information2,
1193 pei_information3,
1194 pei_information4,
1195 pei_information5,
1196 pei_information6,
1197 pei_information7
1198 from per_people_extra_info
1199 where information_type='FI_PENSION'
1200 AND PEI_INFORMATION_CATEGORY='FI_PENSION'
1201 AND PEI_INFORMATION3=CSR_V_COLUMN_NAME
1202 AND PERSON_ID = CSR_V_PERSON_ID;
1203
1204 LR_PERSON_EIT CSR_PERSON_EIT%ROWTYPE;
1205
1206 CURSOR CSR_PERSON_ALL_EIT (
1207 CSR_V_PERSON_ID per_all_people_f.person_id%TYPE )
1208 IS
1209 select PERSON_EXTRA_INFO_ID,
1210 object_version_number,
1211 person_id,
1212 information_type,
1213 pei_information_category,
1214 pei_information1,
1215 pei_information2,
1216 pei_information3,
1217 pei_information4,
1218 pei_information5,
1219 pei_information6,
1220 pei_information7
1221 from per_people_extra_info
1222 where information_type='FI_PENSION'
1223 AND PEI_INFORMATION_CATEGORY='FI_PENSION'
1224 AND PERSON_ID = CSR_V_PERSON_ID;
1225
1226
1227 CURSOR csr_Department_code (
1228 csr_v_pension_provider_id hr_organization_information.organization_id%TYPE,
1229 csr_v_Local_unit_id hr_organization_information.ORG_INFORMATION2%TYPE
1230 )
1231 IS
1232 SELECT hoi2.ORG_INFORMATION3
1233 FROM hr_organization_units o1
1234 , hr_organization_information hoi1
1235 , hr_organization_information hoi2
1236 WHERE o1.business_group_id =g_business_group_id
1237 AND hoi1.organization_id = o1.organization_id
1238 AND hoi1.organization_id = csr_v_pension_provider_id
1239 AND hoi1.org_information_context = 'CLASS'
1240 AND hoi1.org_information1 = 'FR_PENSION'
1241 AND o1.organization_id =hoi2.organization_id
1242 AND hoi2.ORG_INFORMATION_CONTEXT='FI_PENSION_DEPARTMENT_CODES'
1243 AND hoi2.ORG_INFORMATION1 = g_legal_employer_id
1244 AND hoi2.ORG_INFORMATION2 = csr_v_Local_unit_id;
1245
1246
1247 lr_Department_code csr_Department_code%ROWTYPE;
1248 --#########################################
1249 -- Cursor to pick up the Absence details
1250 cursor csr_absence_details
1251 ( CSR_V_PERSON_ID per_all_people_f.person_id%TYPE
1252 ,csr_v_start_effective_date DATE
1253 ,csr_v_end_effective_date DATE)
1254 is
1255
1256 SELECT * FROM (
1257 SELECT PAA.DATE_START,PAA.DATE_END,PAAT.INFORMATION1
1258 FROM PER_ABSENCE_ATTENDANCES PAA,PER_ABSENCE_ATTENDANCE_TYPES PAAT
1259 WHERE PAA.BUSINESS_GROUP_ID = g_business_group_id
1260 AND PAA.PERSON_ID = CSR_V_PERSON_ID
1261 AND PAAT.BUSINESS_GROUP_ID = g_business_group_id
1262 AND PAAT.INFORMATION_CATEGORY = 'FI'
1263 AND PAAT.ABSENCE_ATTENDANCE_TYPE_ID = PAA.ABSENCE_ATTENDANCE_TYPE_ID
1264 AND PAAT.DATE_EFFECTIVE <= csr_v_start_effective_date
1265 AND PAA.DATE_START BETWEEN csr_v_start_effective_date
1266 AND csr_v_end_effective_date
1267 ORDER BY PAA.DATE_START DESC )
1268 WHERE ROWNUM <4;
1269
1270
1271 lr_absence_details csr_absence_details%ROWTYPE;
1272
1273 l_period_start_date DATE;
1274 l_period_end_date DATE;
1275 l_absence_count number;
1276
1277 l_first_category varchar2(240);
1278 l_first_start_date date;
1279 l_first_end_date date;
1280 l_second_category varchar2(240);
1281 l_second_start_date date;
1282 l_second_end_date date;
1283 l_third_category varchar2(240);
1284 l_third_start_date date;
1285 l_third_end_date date;
1286
1287 -- Cursor to pick up the Absence details
1288 --#########################################
1289
1290 -- End of place for Cursor which fetches the values to be archived
1291
1292 BEGIN
1293 IF g_debug THEN
1294 hr_utility.set_location(' Entering Procedure ARCHIVE_CODE',380);
1295 END IF;
1296
1297 fnd_file.put_line(fnd_file.log,'Entering ARCHIVE_CODE ' );
1298 IF g_archive ='Y'
1299 THEN
1300 -- *****************************************************************************
1301 -- TO pick up the PIN
1302 OPEN csr_get_person_details(p_assignment_action_id);
1303 FETCH csr_get_person_details
1304 INTO lr_get_person_details;
1305 CLOSE csr_get_person_details;
1306
1307
1308 l_Employee_Pin :=lr_get_person_details.national_identifier;
1309 IF lr_get_person_details.PRE_NAME_ADJUNCT IS NULL
1310 THEN
1311 l_Employee_name :=lr_get_person_details.LAST_NAME
1312 ||' '||lr_get_person_details.FIRST_NAME;
1313 ELSE
1314 l_Employee_name :=lr_get_person_details.PRE_NAME_ADJUNCT||
1315 ' '|| lr_get_person_details.LAST_NAME||
1316 ' '|| lr_get_person_details.FIRST_NAME;
1317 END IF;
1318
1319
1320 fnd_file.put_line(fnd_file.log,' ==============PERSON================== ' );
1321 fnd_file.put_line(fnd_file.log,'l_Employee_Pin ==> '||l_Employee_Pin );
1322 fnd_file.put_line(fnd_file.log,'l_Employee_name ==> '||l_Employee_name );
1323
1324 fnd_file.put_line(fnd_file.log,' ================================ ' );
1325
1326 -- *****************************************************************************
1327 -- TO pick up the Local Unit Sub-disbursement Number
1328
1329 OPEN csr_get_Segment2( );
1330 FETCH csr_get_Segment2
1331 INTO lr_get_Segment2;
1332 CLOSE csr_get_Segment2;
1333 l_Employed_or_not :=lr_get_Segment2.segment8;
1334 l_local_unit_id_fetched := lr_get_Segment2.segment2;
1335 -- Used to display Record 5 , if value is 2 [Not employed ] then report as 5 else NULL
1336 fnd_file.put_line(fnd_file.log,'l_Employed_or_not ==> '||l_Employed_or_not );
1337
1338 -- TO pick up the Local Unit Sub-disbursement Number
1339 -- TO pick up New SUb-disburesement number
1340 -- From the assignment local unit, go to legal employer,
1341 -- then Pension provider EIT,
1342 -- pick up the org_information1 that is SUB_DISBURSEMENT NUMBER
1343 /*OPEN csr_Local_Unit_Details( to_number(lr_get_Segment2.segment2));
1344 FETCH csr_Local_Unit_Details
1345 INTO lr_Local_Unit_Details;
1346 CLOSE csr_Local_Unit_Details;*/
1347
1348 OPEN csr_pension_provider(g_legal_employer_id ,g_pension_ins_num,g_effective_date);
1349 FETCH csr_pension_provider
1350 INTO lr_pension_provider;
1351 CLOSE csr_pension_provider;
1352
1353 g_pension_provider_id:= lr_pension_provider.ORG_INFORMATION4;
1354
1355 OPEN csr_Department_code( g_pension_provider_id,to_number(lr_get_Segment2.segment2));
1356 FETCH csr_Department_code
1357 INTO lr_Department_code;
1358 CLOSE csr_Department_code;
1359
1360
1361 l_new_Department := lr_Department_code.ORG_INFORMATION3; -- NEW Department code
1362 fnd_file.put_line(fnd_file.log,'l_new_Department ==> '||l_new_Department );
1363 -- TO pick up New SUb-disburesement number
1364 -- *****************************************************************************
1365
1366
1367 -- *****************************************************************************
1368 -- Pick up Person ID
1369 l_person_id := lr_get_person_details.person_id;
1370 fnd_file.put_line(fnd_file.log,'l_person_id ==> '||l_person_id );
1371 -- *****************************************************************************
1372
1373 -- *****************************************************************************
1374 -- TO pick up the Start Date [ Pension Hire date stored at person level. ]
1375
1376 l_Pension_Start_date := fnd_date.canonical_to_date(lr_get_person_details.PER_INFORMATION14);
1377
1378 fnd_file.put_line(fnd_file.log,'Pension_Start_date ==> '||l_Pension_Start_date );
1379
1380 -- *****************************************************************************
1381 -- Pick up the Balance value
1382 l_assignment_id :=lr_get_person_details.assignment_id;
1383 fnd_file.put_line(fnd_file.log,'l_assignment_id ==> '||l_assignment_id );
1384 BEGIN
1385 pay_balance_pkg.set_context('ASSIGNMENT_ID',l_assignment_id);
1386
1387 pay_balance_pkg.set_context('TAX_UNIT_ID',g_legal_employer_id);
1388 pay_balance_pkg.set_context('LOCAL_UNIT_ID',g_local_unit_id);
1389 pay_balance_pkg.set_context('DATE_EARNED',fnd_date.date_to_canonical(g_ref_date));
1390 pay_balance_pkg.set_context('JURISDICTION_CODE',NULL);
1391 pay_balance_pkg.set_context('SOURCE_ID',NULL);
1392 pay_balance_pkg.set_context('TAX_GROUP',NULL);
1393 pay_balance_pkg.set_context('ORGANIZATION_ID',g_pension_provider_id);
1394
1395 END;
1396 -- *****************************************************************************
1397 -- IF local unit selected then pick up the Local Unit context Balance
1398 IF g_local_unit_id is NOT NULL
1399 THEN
1400 -- *****************************************************************************
1401 OPEN csr_Get_Defined_Balance_Id( 'SALARY_SUBJECT_TO_PENSION_PER_PENSION_LU_MONTH');
1402 FETCH csr_Get_Defined_Balance_Id INTO lr_Get_Defined_Balance_Id;
1403 CLOSE csr_Get_Defined_Balance_Id;
1404
1405 fnd_file.put_line(fnd_file.log,'DEFINED_BALANCE_ID ==> ' ||lr_Get_Defined_Balance_Id.creator_id );
1406 fnd_file.put_line(fnd_file.log,'g_effective_date ==> ' ||g_effective_date );
1407
1408 l_Sal_subject_pension_MTD :=to_char(pay_balance_pkg.get_value
1409 (P_DEFINED_BALANCE_ID =>lr_Get_Defined_Balance_Id.creator_id,
1410 P_ASSIGNMENT_ID =>l_assignment_id ,
1411 P_VIRTUAL_DATE => g_ref_date ),'999999999D99') ;
1412
1413
1414 fnd_file.put_line(fnd_file.log,'l_Sal_subject_pension_PER_PENSION_LU_MONTH ==> '||l_Sal_subject_pension_MTD );
1415 -- *****************************************************************************
1416
1417 OPEN csr_Get_Defined_Balance_Id( 'BIK_SUBJECT_TO_PENSION_PER_PENSION_LU_MONTH');
1418 FETCH csr_Get_Defined_Balance_Id INTO lr_Get_Defined_Balance_Id;
1419 CLOSE csr_Get_Defined_Balance_Id;
1420
1421 fnd_file.put_line(fnd_file.log,'DEFINED_BALANCE_ID ==> '||lr_Get_Defined_Balance_Id.creator_id );
1422 l_bik_subject_pension_MTD :=to_char(pay_balance_pkg.get_value
1423 (P_DEFINED_BALANCE_ID =>lr_Get_Defined_Balance_Id.creator_id,
1424 P_ASSIGNMENT_ID =>l_assignment_id ,
1425 P_VIRTUAL_DATE => g_ref_date ),'999999999D99') ;
1426
1427
1428 fnd_file.put_line(fnd_file.log,'l_bik_subject_pension_PER_PENSION_LU_MONTH ==> '||l_bik_subject_pension_MTD );
1429 -- *****************************************************************************
1430
1431 OPEN csr_Get_Defined_Balance_Id( 'TAXABLE_EXPENSES_SUBJECT_TO_PENSION_PER_PENSION_LU_MONTH');
1432 FETCH csr_Get_Defined_Balance_Id INTO lr_Get_Defined_Balance_Id;
1433 CLOSE csr_Get_Defined_Balance_Id;
1434
1435 fnd_file.put_line(fnd_file.log,'DEFINED_BALANCE_ID ==> ' ||lr_Get_Defined_Balance_Id.creator_id );
1436 l_tax_exp_subject_pension_MTD :=to_char(pay_balance_pkg.get_value
1437 (P_DEFINED_BALANCE_ID =>lr_Get_Defined_Balance_Id.creator_id,
1438 P_ASSIGNMENT_ID =>l_assignment_id ,
1439 P_VIRTUAL_DATE => g_ref_date ),'999999999D99') ;
1440
1441
1442 fnd_file.put_line(fnd_file.log,'l_tax_exp_subject_pension_PER_PENSION_LU_MONTH ==> '||l_tax_exp_subject_pension_MTD );
1443 -- *****************************************************************************
1444 -- QTD START
1445 -- *****************************************************************************
1446 --Salary QTD
1447 OPEN csr_Get_Defined_Balance_Id( 'SALARY_SUBJECT_TO_PENSION_PER_PENSION_LU_QUARTER');
1448 FETCH csr_Get_Defined_Balance_Id INTO lr_Get_Defined_Balance_Id;
1449 CLOSE csr_Get_Defined_Balance_Id;
1450
1451 fnd_file.put_line(fnd_file.log,'DEFINED_BALANCE_ID ==> ' ||lr_Get_Defined_Balance_Id.creator_id );
1452 fnd_file.put_line(fnd_file.log,'g_effective_date ==> ' ||g_effective_date );
1453
1454 l_Sal_subject_pension_QTD :=to_char(pay_balance_pkg.get_value
1455 (P_DEFINED_BALANCE_ID =>lr_Get_Defined_Balance_Id.creator_id,
1456 P_ASSIGNMENT_ID =>l_assignment_id ,
1457 P_VIRTUAL_DATE => g_ref_date ),'999999999D99') ;
1458
1459
1460 fnd_file.put_line(fnd_file.log,'l_Sal_subject_pension_PER_PENSION_LU_QUARTER ==> '||l_Sal_subject_pension_QTD );
1461 -- *****************************************************************************
1462 -- BIK QTD
1463
1464 OPEN csr_Get_Defined_Balance_Id( 'BIK_SUBJECT_TO_PENSION_PER_PENSION_LU_QUARTER');
1465 FETCH csr_Get_Defined_Balance_Id INTO lr_Get_Defined_Balance_Id;
1466 CLOSE csr_Get_Defined_Balance_Id;
1467
1468 fnd_file.put_line(fnd_file.log,'DEFINED_BALANCE_ID ==> '||lr_Get_Defined_Balance_Id.creator_id );
1469 l_bik_subject_pension_QTD :=to_char(pay_balance_pkg.get_value
1470 (P_DEFINED_BALANCE_ID =>lr_Get_Defined_Balance_Id.creator_id,
1471 P_ASSIGNMENT_ID =>l_assignment_id ,
1472 P_VIRTUAL_DATE => g_ref_date ),'999999999D99') ;
1473
1474
1475 fnd_file.put_line(fnd_file.log,'l_bik_subject_pension_PER_PENSION_LU_QUARTER ==> '||l_bik_subject_pension_QTD );
1476 -- *****************************************************************************
1477 --EXPENSE QTD
1478
1479 OPEN csr_Get_Defined_Balance_Id( 'TAXABLE_EXPENSES_SUBJECT_TO_PENSION_PER_PENSION_LU_QUARTER');
1480 FETCH csr_Get_Defined_Balance_Id INTO lr_Get_Defined_Balance_Id;
1481 CLOSE csr_Get_Defined_Balance_Id;
1482
1483 fnd_file.put_line(fnd_file.log,'DEFINED_BALANCE_ID ==> ' ||lr_Get_Defined_Balance_Id.creator_id );
1484 l_tax_exp_subject_pension_QTD :=to_char(pay_balance_pkg.get_value
1485 (P_DEFINED_BALANCE_ID =>lr_Get_Defined_Balance_Id.creator_id,
1486 P_ASSIGNMENT_ID =>l_assignment_id ,
1487 P_VIRTUAL_DATE => g_ref_date ),'999999999D99') ;
1488
1489
1490 fnd_file.put_line(fnd_file.log,'l_tax_exp_subject_pension_PER_PENSION_LU_QUARTER ==> '||l_tax_exp_subject_pension_QTD );
1491 -- *****************************************************************************
1492 -- MONTH QTD END
1493 -- Salary YTD
1494 OPEN csr_Get_Defined_Balance_Id( 'SALARY_SUBJECT_TO_PENSION_PER_PENSION_LU_YEAR');
1495 FETCH csr_Get_Defined_Balance_Id INTO lr_Get_Defined_Balance_Id;
1496 CLOSE csr_Get_Defined_Balance_Id;
1497
1498 fnd_file.put_line(fnd_file.log,'DEFINED_BALANCE_ID ==> ' ||lr_Get_Defined_Balance_Id.creator_id );
1499 fnd_file.put_line(fnd_file.log,'g_effective_date ==> ' ||g_effective_date );
1500
1501 l_Sal_subject_pension_YTD :=to_char(pay_balance_pkg.get_value
1502 (P_DEFINED_BALANCE_ID =>lr_Get_Defined_Balance_Id.creator_id,
1503 P_ASSIGNMENT_ID =>l_assignment_id ,
1504 P_VIRTUAL_DATE => g_ref_date ),'999999999D99') ;
1505
1506 fnd_file.put_line(fnd_file.log,'l_Sal_subject_pension_PER_PENSION_LU_YEAR ==> '||l_Sal_subject_pension_YTD );
1507 -- *****************************************************************************
1508
1509 -- Salary Balance value before termination date
1510
1511 IF lr_get_person_details.PER_INFORMATION20 IS NOT NULL
1512 THEN
1513 l_sal_sub_pension_YTD_before :=to_char(pay_balance_pkg.get_value
1514 (P_DEFINED_BALANCE_ID =>lr_Get_Defined_Balance_Id.creator_id,
1515 P_ASSIGNMENT_ID =>l_assignment_id ,
1516 P_VIRTUAL_DATE => fnd_date.canonical_to_date(lr_get_person_details.PER_INFORMATION20) ),'999999999D99') ;
1517 ELSE
1518 l_sal_sub_pension_YTD_before := 0;
1519 END IF;
1520 fnd_file.put_line(fnd_file.log,'l_Sal_subject_pension_PER_PENSION_LU_YEAR_BEFORE ==> '||l_sal_sub_pension_YTD_before );
1521 -- *****************************************************************************
1522 -- Salary YTD
1523 -- BIK YTD
1524
1525 OPEN csr_Get_Defined_Balance_Id( 'BIK_SUBJECT_TO_PENSION_PER_PENSION_LU_YEAR');
1526 FETCH csr_Get_Defined_Balance_Id INTO lr_Get_Defined_Balance_Id;
1527 CLOSE csr_Get_Defined_Balance_Id;
1528
1529 fnd_file.put_line(fnd_file.log,'DEFINED_BALANCE_ID ==> ' ||lr_Get_Defined_Balance_Id.creator_id );
1530 l_bik_subject_pension_YTD :=to_char(pay_balance_pkg.get_value
1531 (P_DEFINED_BALANCE_ID =>lr_Get_Defined_Balance_Id.creator_id,
1532 P_ASSIGNMENT_ID =>l_assignment_id ,
1533 P_VIRTUAL_DATE => g_ref_date ),'999999999D99') ;
1534
1535 fnd_file.put_line(fnd_file.log,'l_bik_subject_pension_PER_PENSION_LU_YEAR ==> '||l_bik_subject_pension_YTD );
1536 -- *****************************************************************************
1537 -- Balance value before termination date
1538 IF lr_get_person_details.PER_INFORMATION20 IS NOT NULL
1539 THEN
1540 l_bik_sub_pension_YTD_before :=to_char(pay_balance_pkg.get_value
1541 (P_DEFINED_BALANCE_ID =>lr_Get_Defined_Balance_Id.creator_id,
1542 P_ASSIGNMENT_ID =>l_assignment_id ,
1543 P_VIRTUAL_DATE => fnd_date.canonical_to_date(lr_get_person_details.PER_INFORMATION20) ),'999999999D99') ;
1544 ELSE
1545 l_bik_sub_pension_YTD_before := 0;
1546 END IF;
1547 fnd_file.put_line(fnd_file.log,'l_bik_subject_pension_PER_PENSION_LU_YEAR_before ==> '||l_bik_sub_pension_YTD_before );
1548
1549 -- *****************************************************************************
1550
1551 -- BIK YTD
1552 -- TAXABLE EXPENSES YTD
1553 OPEN csr_Get_Defined_Balance_Id( 'TAXABLE_EXPENSES_SUBJECT_TO_PENSION_PER_PENSION_LU_YEAR');
1554 FETCH csr_Get_Defined_Balance_Id INTO lr_Get_Defined_Balance_Id;
1555 CLOSE csr_Get_Defined_Balance_Id;
1556
1557 l_tax_exp_subject_pension_YTD :=to_char(pay_balance_pkg.get_value
1558 (P_DEFINED_BALANCE_ID =>lr_Get_Defined_Balance_Id.creator_id,
1559 P_ASSIGNMENT_ID =>l_assignment_id ,
1560 P_VIRTUAL_DATE => g_ref_date ),'999999999D99') ;
1561
1562 fnd_file.put_line(fnd_file.log,'l_tax_exp_subject_pension_PER_PENSION_LU_YEAR ==> '||l_tax_exp_subject_pension_YTD );
1563 -- *****************************************************************************
1564 -- Taxable Expenses Balance value before termination date
1565
1566 OPEN csr_Get_Defined_Balance_Id( 'TAXABLE_EXPENSES_SUBJECT_TO_PENSION_PER_PENSION_LU_YEAR');
1567 FETCH csr_Get_Defined_Balance_Id INTO lr_Get_Defined_Balance_Id;
1568 CLOSE csr_Get_Defined_Balance_Id;
1569
1570 IF lr_get_person_details.PER_INFORMATION20 IS NOT NULL
1571 THEN
1572 l_tax_exp_pension_YTD_before :=to_char(pay_balance_pkg.get_value
1573 (P_DEFINED_BALANCE_ID =>lr_Get_Defined_Balance_Id.creator_id,
1574 P_ASSIGNMENT_ID =>l_assignment_id ,
1575 P_VIRTUAL_DATE => fnd_date.canonical_to_date(lr_get_person_details.PER_INFORMATION20) ),'999999999D99') ;
1576 ELSE
1577 l_tax_exp_pension_YTD_before := 0;
1578 END IF;
1579 fnd_file.put_line(fnd_file.log,'l_tax_exp_subject_pension_PER_PENSION_LU_YEAR_BEFORE ==> '||l_tax_exp_pension_YTD_before );
1580 -- TAXABLE EXPENSES YTD
1581 -- *****************************************************************************
1582 -- IF the Local unit is not selected then pick up LE context Balance values
1583 -- *****************************************************************************
1584 ELSE
1585 --SALARY LE MTD
1586 OPEN csr_Get_Defined_Balance_Id( 'SALARY_SUBJECT_TO_PENSION_PER_PENSION_LE_MONTH');
1587 FETCH csr_Get_Defined_Balance_Id INTO lr_Get_Defined_Balance_Id;
1588 CLOSE csr_Get_Defined_Balance_Id;
1589
1590 fnd_file.put_line(fnd_file.log,'DEFINED_BALANCE_ID ==> '||lr_Get_Defined_Balance_Id.creator_id );
1591 fnd_file.put_line(fnd_file.log,'g_effective_date ==> '||g_effective_date );
1592
1593 l_Sal_subject_pension_MTD :=to_char(pay_balance_pkg.get_value
1594 (P_DEFINED_BALANCE_ID =>lr_Get_Defined_Balance_Id.creator_id,
1595 P_ASSIGNMENT_ID =>l_assignment_id ,
1596 P_VIRTUAL_DATE => g_ref_date ),'999999999D99') ;
1597
1598
1599 fnd_file.put_line(fnd_file.log,'l_Sal_subject_pension_PER_PENSION_LE_MONTH ==> '||l_Sal_subject_pension_MTD );
1600 -- *****************************************************************************
1601 OPEN csr_Get_Defined_Balance_Id( 'BIK_SUBJECT_TO_PENSION_PER_PENSION_LE_MONTH');
1602 FETCH csr_Get_Defined_Balance_Id INTO lr_Get_Defined_Balance_Id;
1603 CLOSE csr_Get_Defined_Balance_Id;
1604
1605 l_bik_subject_pension_MTD :=to_char(pay_balance_pkg.get_value
1606 (P_DEFINED_BALANCE_ID =>lr_Get_Defined_Balance_Id.creator_id,
1607 P_ASSIGNMENT_ID =>l_assignment_id ,
1608 P_VIRTUAL_DATE => g_ref_date ),'999999999D99') ;
1609
1610
1611 fnd_file.put_line(fnd_file.log,'l_bik_subject_pension_PER_PENSION_LE_MONTH ==> '||l_bik_subject_pension_MTD );
1612
1613 -- *****************************************************************************
1614
1615 OPEN csr_Get_Defined_Balance_Id( 'TAXABLE_EXPENSES_SUBJECT_TO_PENSION_PER_PENSION_LE_MONTH');
1616 FETCH csr_Get_Defined_Balance_Id INTO lr_Get_Defined_Balance_Id;
1617 CLOSE csr_Get_Defined_Balance_Id;
1618
1619 l_tax_exp_subject_pension_MTD :=to_char(pay_balance_pkg.get_value
1620 (P_DEFINED_BALANCE_ID =>lr_Get_Defined_Balance_Id.creator_id,
1621 P_ASSIGNMENT_ID =>l_assignment_id ,
1622 P_VIRTUAL_DATE => g_ref_date ),'999999999D99') ;
1623
1624
1625 fnd_file.put_line(fnd_file.log,'l_tax_exp_subject_pension_PER_PENSION_LE_MONTH ==> '||l_tax_exp_subject_pension_MTD );
1626 -- *****************************************************************************
1627 --SALARY LE QTD
1628 OPEN csr_Get_Defined_Balance_Id( 'SALARY_SUBJECT_TO_PENSION_PER_PENSION_LE_QUARTER');
1629 FETCH csr_Get_Defined_Balance_Id INTO lr_Get_Defined_Balance_Id;
1630 CLOSE csr_Get_Defined_Balance_Id;
1631
1632 fnd_file.put_line(fnd_file.log,'DEFINED_BALANCE_ID ==> '||lr_Get_Defined_Balance_Id.creator_id );
1633 fnd_file.put_line(fnd_file.log,'g_effective_date ==> '||g_effective_date );
1634
1635 l_Sal_subject_pension_QTD :=to_char(pay_balance_pkg.get_value
1636 (P_DEFINED_BALANCE_ID =>lr_Get_Defined_Balance_Id.creator_id,
1637 P_ASSIGNMENT_ID =>l_assignment_id ,
1638 P_VIRTUAL_DATE => g_ref_date ),'999999999D99') ;
1639
1640
1641 fnd_file.put_line(fnd_file.log,'l_Sal_subject_pension_PER_PENSION_LE_QUARTER ==> '||l_Sal_subject_pension_QTD );
1642 -- *****************************************************************************
1643 --BIK QTD
1644 OPEN csr_Get_Defined_Balance_Id( 'BIK_SUBJECT_TO_PENSION_PER_PENSION_LE_QUARTER');
1645 FETCH csr_Get_Defined_Balance_Id INTO lr_Get_Defined_Balance_Id;
1646 CLOSE csr_Get_Defined_Balance_Id;
1647
1648 l_bik_subject_pension_QTD :=to_char(pay_balance_pkg.get_value
1649 (P_DEFINED_BALANCE_ID =>lr_Get_Defined_Balance_Id.creator_id,
1650 P_ASSIGNMENT_ID =>l_assignment_id ,
1651 P_VIRTUAL_DATE => g_ref_date ),'999999999D99') ;
1652
1653
1654 fnd_file.put_line(fnd_file.log,'l_bik_subject_pension_PER_PENSION_LE_QUARTER ==> '||l_bik_subject_pension_QTD );
1655
1656 -- *****************************************************************************
1657 -- EXPENSE QTD
1658
1659 OPEN csr_Get_Defined_Balance_Id( 'TAXABLE_EXPENSES_SUBJECT_TO_PENSION_PER_PENSION_LE_QUARTER');
1660 FETCH csr_Get_Defined_Balance_Id INTO lr_Get_Defined_Balance_Id;
1661 CLOSE csr_Get_Defined_Balance_Id;
1662
1663 l_tax_exp_subject_pension_QTD :=to_char(pay_balance_pkg.get_value
1664 (P_DEFINED_BALANCE_ID =>lr_Get_Defined_Balance_Id.creator_id,
1665 P_ASSIGNMENT_ID =>l_assignment_id ,
1666 P_VIRTUAL_DATE => g_ref_date ),'999999999D99') ;
1667
1668
1669 fnd_file.put_line(fnd_file.log,'l_tax_exp_subject_pension_PER_PENSION_LE_QUARTER ==> '||l_tax_exp_subject_pension_QTD );
1670 -- *****************************************************************************
1671
1672 -- END OF MTD QTD
1673
1674 OPEN csr_Get_Defined_Balance_Id( 'SALARY_SUBJECT_TO_PENSION_PER_PENSION_LE_YEAR');
1675 FETCH csr_Get_Defined_Balance_Id INTO lr_Get_Defined_Balance_Id;
1676 CLOSE csr_Get_Defined_Balance_Id;
1677
1678 fnd_file.put_line(fnd_file.log,'DEFINED_BALANCE_ID ==> ' ||lr_Get_Defined_Balance_Id.creator_id );
1679 fnd_file.put_line(fnd_file.log,'g_effective_date ==> ' ||g_effective_date );
1680
1681 l_Sal_subject_pension_YTD :=to_char(pay_balance_pkg.get_value
1682 (P_DEFINED_BALANCE_ID =>lr_Get_Defined_Balance_Id.creator_id,
1683 P_ASSIGNMENT_ID =>l_assignment_id ,
1684 P_VIRTUAL_DATE => g_ref_date ),'999999999D99') ;
1685
1686 fnd_file.put_line(fnd_file.log,'l_Sal_subject_pension_PER_PENSION_LE_YEAR ==> '||l_Sal_subject_pension_YTD );
1687 -- *****************************************************************************
1688
1689 -- Salary Balance value before termination date
1690
1691 IF lr_get_person_details.PER_INFORMATION20 IS NOT NULL
1692 THEN
1693 l_sal_sub_pension_YTD_before :=to_char(pay_balance_pkg.get_value
1694 (P_DEFINED_BALANCE_ID =>lr_Get_Defined_Balance_Id.creator_id,
1695 P_ASSIGNMENT_ID =>l_assignment_id ,
1696 P_VIRTUAL_DATE => fnd_date.canonical_to_date(lr_get_person_details.PER_INFORMATION20) ),'999999999D99') ;
1697 ELSE
1698 l_sal_sub_pension_YTD_before := 0;
1699 END IF;
1700 fnd_file.put_line(fnd_file.log,'l_Sal_subject_pension_PER_PENSION_LE_YEAR_BEFORE ==> '||l_sal_sub_pension_YTD_before );
1701 -- *****************************************************************************
1702
1703
1704 OPEN csr_Get_Defined_Balance_Id( 'BIK_SUBJECT_TO_PENSION_PER_PENSION_LE_YEAR');
1705 FETCH csr_Get_Defined_Balance_Id INTO lr_Get_Defined_Balance_Id;
1706 CLOSE csr_Get_Defined_Balance_Id;
1707
1708 l_bik_subject_pension_YTD :=to_char(pay_balance_pkg.get_value
1709 (P_DEFINED_BALANCE_ID =>lr_Get_Defined_Balance_Id.creator_id,
1710 P_ASSIGNMENT_ID =>l_assignment_id ,
1711 P_VIRTUAL_DATE => g_ref_date ),'999999999D99') ;
1712
1713 fnd_file.put_line(fnd_file.log,'l_bik_subject_pension_PER_PENSION_LE_YEAR ==> '||l_bik_subject_pension_YTD );
1714 -- *****************************************************************************
1715 -- Balance value before termination date
1716 IF lr_get_person_details.PER_INFORMATION20 IS NOT NULL
1717 THEN
1718 l_bik_sub_pension_YTD_before :=to_char(pay_balance_pkg.get_value
1719 (P_DEFINED_BALANCE_ID =>lr_Get_Defined_Balance_Id.creator_id,
1720 P_ASSIGNMENT_ID =>l_assignment_id ,
1721 P_VIRTUAL_DATE => fnd_date.canonical_to_date(lr_get_person_details.PER_INFORMATION20) ),'999999999D99') ;
1722 ELSE
1723 l_bik_sub_pension_YTD_before := 0;
1724 END IF;
1725 fnd_file.put_line(fnd_file.log,'l_bik_subject_pension_PER_PENSION_LE_YEAR_before ==> '||l_bik_sub_pension_YTD_before );
1726
1727 -- *****************************************************************************
1728
1729 OPEN csr_Get_Defined_Balance_Id( 'TAXABLE_EXPENSES_SUBJECT_TO_PENSION_PER_PENSION_LE_YEAR');
1730 FETCH csr_Get_Defined_Balance_Id INTO lr_Get_Defined_Balance_Id;
1731 CLOSE csr_Get_Defined_Balance_Id;
1732
1733 l_tax_exp_subject_pension_YTD :=to_char(pay_balance_pkg.get_value
1734 (P_DEFINED_BALANCE_ID =>lr_Get_Defined_Balance_Id.creator_id,
1735 P_ASSIGNMENT_ID =>l_assignment_id ,
1736 P_VIRTUAL_DATE => g_ref_date ),'999999999D99') ;
1737
1738 fnd_file.put_line(fnd_file.log,'l_tax_exp_subject_pension_PER_PENSION_LE_YEAR ==> '||l_tax_exp_subject_pension_YTD );
1739 -- *****************************************************************************
1740 OPEN csr_Get_Defined_Balance_Id( 'TAXABLE_EXPENSES_SUBJECT_TO_PENSION_PER_PENSION_LE_YEAR');
1741 FETCH csr_Get_Defined_Balance_Id INTO lr_Get_Defined_Balance_Id;
1742 CLOSE csr_Get_Defined_Balance_Id;
1743 -- Taxable Expenses Balance value before termination date
1744 IF lr_get_person_details.PER_INFORMATION20 IS NOT NULL
1745 THEN
1746 l_tax_exp_pension_YTD_before :=to_char(pay_balance_pkg.get_value
1747 (P_DEFINED_BALANCE_ID =>lr_Get_Defined_Balance_Id.creator_id,
1748 P_ASSIGNMENT_ID =>l_assignment_id ,
1749 P_VIRTUAL_DATE => fnd_date.canonical_to_date(lr_get_person_details.PER_INFORMATION20) ),'999999999D99') ;
1750 ELSE
1751 l_tax_exp_pension_YTD_before := 0;
1752 END IF;
1753 fnd_file.put_line(fnd_file.log,'l_tax_exp_subject_pension_PER_PENSION_LE_YEAR_BEFORE ==> '||l_tax_exp_pension_YTD_before );
1754
1755 END IF; -- END OF G_LOCAL_UNIT_ID NULL CHECK
1756
1757 -- *****************************************************************************
1758 -- Pick up Termiantion date form person table
1759 l_Termination_date := fnd_date.canonical_to_date(lr_get_person_details.PER_INFORMATION20);
1760 fnd_file.put_line(fnd_file.log,'l_Termination_date ==> '||l_Termination_date );
1761 -- *****************************************************************************
1762 -- Pick up the Cause of termination
1763 -- from person record
1764
1765 l_Cause_of_termination := lr_get_person_details.PER_INFORMATION21;
1766
1767 fnd_file.put_line(fnd_file.log,'Cause termination ==> '||l_Cause_of_termination );
1768 -- *****************************************************************************
1769 -- to find the old group code
1770 -- Go to Person EIT , find the record for the " Pension Group ", if available
1771 -- Then pick up the value PEI_INFORMATION4 <=> Pension Group Code
1772
1773 OPEN CSR_PERSON_EIT(l_person_id,'Pension Group');
1774 FETCH CSR_PERSON_EIT
1775 INTO LR_PERSON_EIT;
1776 CLOSE CSR_PERSON_EIT;
1777 l_old_pension_group_code :=LR_PERSON_EIT.pei_information4;
1778 fnd_file.put_line(fnd_file.log,'Old Group Code ==> '||LR_PERSON_EIT.pei_information4);
1779 -- to find the old group code
1780 -- *****************************************************************************
1781 -- *****************************************************************************
1782 -- Go to Person EIT , find the record for the " Local Unit ", if available
1783 -- Then pick up the value PEI_INFORMATION4 <=> Local Unit Id
1784 -- Finding the OLD INSURANCE POLICY NUMBER
1785 OPEN CSR_PERSON_EIT(l_person_id,'Local Unit');
1786 FETCH CSR_PERSON_EIT
1787 INTO LR_PERSON_EIT;
1788 CLOSE CSR_PERSON_EIT;
1789 l_eit_local_unit := LR_PERSON_EIT.pei_information4;
1790 -- *****************************************************************************
1791 -- Using the Above Local unit ID
1792 -- Pick up the SUB-DISBURSEMENT NUMBER from Local Unit Details
1793 -- this SUB-DISBURSEMENT NUMBER is which is Last reported SUB-DISBURSEMENT
1794 -- to find the old Department
1795 /*OPEN csr_Local_Unit_Details(to_number(LR_PERSON_EIT.pei_information4 ));
1796 FETCH csr_Local_Unit_Details
1797 INTO lr_Local_Unit_Details;
1798 CLOSE csr_Local_Unit_Details;*/
1799 OPEN csr_Department_code( g_pension_provider_id,to_number(LR_PERSON_EIT.pei_information4 ));
1800 FETCH csr_Department_code
1801 INTO lr_Department_code;
1802 CLOSE csr_Department_code;
1803
1804 l_old_Department := lr_Department_code.ORG_INFORMATION3; -- OLD Department Code
1805
1806 fnd_file.put_line(fnd_file.log,'l_old_Department ==> '||l_old_Department );
1807 -- *****************************************************************************
1808 -- to find the old Department END
1809 -- Using the Above Local unit ID find the legal employer , Pension Provider,
1810 -- Then select the pension policy Number for this Local Unit
1811 -- which is Last reported Policy Number
1812 -- to find the old Department
1813 -- Finding the OLD INSURANCE POLICY NUMBER
1814 OPEN CSR_PERSON_EIT(l_person_id,'Insurance Number');
1815 FETCH CSR_PERSON_EIT
1816 INTO LR_PERSON_EIT;
1817 CLOSE CSR_PERSON_EIT;
1818 l_old_policy_number :=LR_PERSON_EIT.pei_information4;
1819
1820 -- Finding the OLD INSURANCE POLICY NUMBER END
1821 -- *****************************************************************************
1822 -- TO pick up new pension group code
1823 -- Pick the Current Pension Group Code from the Person Record
1824 l_new_pension_group_code :=lr_get_person_details.PER_INFORMATION16;
1825 fnd_file.put_line(fnd_file.log,'New Group Code ==> '||lr_get_person_details.PER_INFORMATION16);
1826 -- TO pick up new pension group code
1827 -- *****************************************************************************
1828 -- *****************************************************************************
1829 -- Finding the new INSURANCE POLICY NUMBER
1830 -- Pick the Current INSURANCE POLICY NUMBER from the Person Record
1831 l_new_policy_number := lr_get_person_details.PER_INFORMATION24;
1832 fnd_file.put_line(fnd_file.log,'New Policy Number ==> '||lr_get_person_details.PER_INFORMATION24);
1833
1834 -- Finding the new INSURANCE POLICY NUMBER
1835 -- *****************************************************************************
1836
1837 -- End of Pickingup the Data
1838
1839 BEGIN
1840 SELECT 1 INTO l_flag
1841 FROM pay_action_information
1842 WHERE action_information_category = 'EMEA REPORT DETAILS'
1843 AND action_information1 = 'PYFITELA'
1844 AND action_context_id = p_assignment_action_id;
1845
1846 EXCEPTION
1847 WHEN NO_DATA_FOUND
1848 THEN
1849 fnd_file.put_line(fnd_file.log,'Not found In Archive record ' );
1850 fnd_file.put_line(fnd_file.log,'g_payroll_action_id ' || g_payroll_action_id);
1851 pay_action_information_api.create_action_information (
1852 p_action_information_id => l_action_info_id
1853 ,p_action_context_id => p_assignment_action_id
1854 ,p_action_context_type => 'AAP'
1855 ,p_object_version_number => l_ovn
1856 ,p_effective_date => l_effective_date
1857 ,p_source_id => NULL
1858 ,p_source_text => NULL
1859 ,p_action_information_category => 'EMEA REPORT INFORMATION'
1860 ,p_action_information1 => 'PYFITELA'
1861 ,p_action_information2 => 'PER'
1862 ,p_action_information3 => g_payroll_action_id
1863 ,p_action_information4 => l_old_policy_number
1864 ,p_action_information5 => l_Employee_Pin
1865 ,p_action_information6 => l_old_pension_group_code
1866 ,p_action_information7 => l_old_Department
1867 ,p_action_information8 => l_Employee_name
1868 ,p_action_information9 => fnd_date.date_to_canonical(l_Pension_Start_date)
1869 ,p_action_information10 => FND_NUMBER.NUMBER_TO_CANONICAL(l_Sal_subject_pension_MTD)
1870 ,p_action_information11 => FND_NUMBER.NUMBER_TO_CANONICAL(l_bik_subject_pension_MTD)
1871 ,p_action_information12 => FND_NUMBER.NUMBER_TO_CANONICAL(l_tax_exp_subject_pension_MTD)
1872 ,p_action_information13 => FND_NUMBER.NUMBER_TO_CANONICAL(l_Sal_subject_pension_YTD)
1873 ,p_action_information14 => FND_NUMBER.NUMBER_TO_CANONICAL(l_bik_subject_pension_YTD)
1874 ,p_action_information15 => FND_NUMBER.NUMBER_TO_CANONICAL(l_tax_exp_subject_pension_YTD)
1875 ,p_action_information16 => fnd_date.date_to_canonical(l_Termination_date)
1876 ,p_action_information17 => l_Cause_of_termination
1877 ,p_action_information18 => FND_NUMBER.NUMBER_TO_CANONICAL(l_sal_sub_pension_YTD_before)
1878 ,p_action_information19 => FND_NUMBER.NUMBER_TO_CANONICAL(l_tax_exp_pension_YTD_before)
1879 ,p_action_information20 => FND_NUMBER.NUMBER_TO_CANONICAL(l_bik_sub_pension_YTD_before)
1880 ,p_action_information21 => l_new_policy_number
1881 ,p_action_information22 => l_new_Department
1882 ,p_action_information23 => l_new_pension_group_code
1883 ,p_action_information24 => l_Employed_or_not
1884 ,p_action_information25 => l_Currency
1885 ,p_action_information26 => lr_get_person_details.PER_INFORMATION15
1886 ,p_action_information27 => lr_get_person_details.PER_INFORMATION16
1887 ,p_action_information28 => l_local_unit_id_fetched
1888 ,p_action_information29 => lr_pension_provider.ORG_INFORMATION10
1889 ,p_action_information30 => l_person_id
1890 ,p_assignment_id => l_assignment_id );
1891
1892 fnd_file.put_line(fnd_file.log,'l_action_info_id ==> '||l_action_info_id );
1893 fnd_file.put_line(fnd_file.log,'l_action_info_id ==> '||l_person_id );
1894 fnd_file.put_line(fnd_file.log,'g_annual_report ==> '||g_annual_report );
1895 IF g_annual_report ='M'
1896 THEN
1897 l_period_start_date :=LAST_DAY(ADD_MONTHS(g_ref_date , -1) ) + 1 ;
1898 l_period_end_date := LAST_DAY(g_ref_date);
1899 ELSIF g_annual_report ='Q'
1900 THEN
1901 l_period_start_date := TRUNC(g_ref_date, 'Q') ;
1902 l_period_end_date := ADD_MONTHS(last_day(TRUNC(g_ref_date, 'Q') ), 2);
1903 ELSIF g_annual_report ='A'
1904 THEN
1905 l_period_start_date :=TRUNC(g_ref_date, 'Y') ;
1906 l_period_end_date := ADD_MONTHS(last_day(TRUNC(g_ref_date, 'Y') ), 11);
1907 END IF;
1908 l_absence_count := 0;
1909
1910 FOR lr_absence_details IN
1911 csr_absence_details(l_person_id,l_period_start_date,l_period_end_date)
1912 LOOP
1913
1914 IF l_absence_count = 3
1915 THEN
1916 EXIT;
1917 END IF;
1918 IF lr_absence_details.INFORMATION1 ='4' -- Sick Leave Without Pay
1919 or lr_absence_details.INFORMATION1 ='2' -- Lay-off
1920 or lr_absence_details.INFORMATION1 ='5' -- Company Specific Leave
1921 or lr_absence_details.INFORMATION1 ='7' -- Study Leave
1922 THEN
1923 IF MONTHS_BETWEEN(lr_absence_details.DATE_END,lr_absence_details.DATE_START) >= 2
1924 THEN
1925 fnd_file.put_line(fnd_file.log,'Cursor Looping IF count==> '||l_absence_count );
1926 fnd_file.put_line(fnd_file.log,'START==> '||lr_absence_details.DATE_START );
1927 fnd_file.put_line(fnd_file.log,'END==> '||lr_absence_details.DATE_END );
1928
1929
1930 ABS_RECORDS(l_absence_count).CATEGORY := lr_absence_details.INFORMATION1;
1931 ABS_RECORDS(l_absence_count).START_DATE := lr_absence_details.DATE_START ;
1932 ABS_RECORDS(l_absence_count).END_DATE := lr_absence_details.DATE_END;
1933 l_absence_count := l_absence_count + 1;
1934 END IF;
1935 /* ELSIF lr_absence_details.ABSENCE_CATEGORY = 'FI_MNM'
1936 or lr_absence_details.ABSENCE_CATEGORY = 'FI_NL'
1937 or lr_absence_details.ABSENCE_CATEGORY = 'FI_SAL'
1938 or lr_absence_details.ABSENCE_CATEGORY = 'M'
1939 THEN
1940 */ ELSE -- For all others, even if user adds his own code
1941 fnd_file.put_line(fnd_file.log,'Cursor Looping Else count==> '||l_absence_count );
1942 fnd_file.put_line(fnd_file.log,'START==> '||lr_absence_details.DATE_START );
1943 fnd_file.put_line(fnd_file.log,'END==> '||lr_absence_details.DATE_END );
1944
1945
1946 ABS_RECORDS(l_absence_count).CATEGORY := lr_absence_details.INFORMATION1;
1947 ABS_RECORDS(l_absence_count).START_DATE := lr_absence_details.DATE_START ;
1948 ABS_RECORDS(l_absence_count).END_DATE := lr_absence_details.DATE_END;
1949 l_absence_count := l_absence_count + 1;
1950 END IF;
1951 END LOOP;
1952 l_first_category := null;
1953 l_first_start_date :=null;
1954 l_first_end_date := null;
1955 l_second_category := null;
1956 l_second_start_date := null;
1957 l_second_end_date := null;
1958 l_third_category := null;
1959 l_third_start_date := null;
1960 l_third_end_date := null;
1961
1962 IF l_absence_count = 1
1963 THEN
1964 fnd_file.put_line(fnd_file.log,'ABS_RECORDS(0).CATEGORY ==> '||ABS_RECORDS(0).CATEGORY );
1965 fnd_file.put_line(fnd_file.log,'ABS_RECORDS(0).START_DATE ==> '||ABS_RECORDS(0).START_DATE );
1966 fnd_file.put_line(fnd_file.log,'ABS_RECORDS(0).END_DATE ==> '||ABS_RECORDS(0).END_DATE );
1967 l_first_category :=ABS_RECORDS(0).CATEGORY;
1968 l_first_start_date :=ABS_RECORDS(0).START_DATE;
1969 l_first_end_date :=ABS_RECORDS(0).END_DATE ;
1970 l_second_category := null;
1971 l_second_start_date := null;
1972 l_second_end_date := null;
1973 l_third_category := null;
1974 l_third_start_date := null;
1975 l_third_end_date := null;
1976
1977 ELSIF l_absence_count = 2
1978 THEN
1979 fnd_file.put_line(fnd_file.log,'ABS_RECORDS(0).CATEGORY ==> '||ABS_RECORDS(0).CATEGORY );
1980 fnd_file.put_line(fnd_file.log,'ABS_RECORDS(0).START_DATE ==> '||ABS_RECORDS(0).START_DATE );
1981 fnd_file.put_line(fnd_file.log,'ABS_RECORDS(0).END_DATE ==> '||ABS_RECORDS(0).END_DATE );
1982 fnd_file.put_line(fnd_file.log,'ABS_RECORDS(1)CATEGORY ==> '||ABS_RECORDS(1).CATEGORY );
1983 fnd_file.put_line(fnd_file.log,'ABS_RECORDS(1)START_DATE ==> '||ABS_RECORDS(1).START_DATE );
1984 fnd_file.put_line(fnd_file.log,'ABS_RECORDS(1) END_DATE==> '||ABS_RECORDS(1).END_DATE );
1985
1986 l_first_category :=ABS_RECORDS(0).CATEGORY;
1987 l_first_start_date :=ABS_RECORDS(0).START_DATE;
1988 l_first_end_date :=ABS_RECORDS(0).END_DATE ;
1989 l_second_category :=ABS_RECORDS(1).CATEGORY;
1990 l_second_start_date :=ABS_RECORDS(1).START_DATE;
1991 l_second_end_date :=ABS_RECORDS(1).END_DATE ;
1992 l_third_category :=null;
1993 l_third_start_date :=null;
1994 l_third_end_date :=null;
1995 ELSIF l_absence_count = 3
1996 THEN
1997 fnd_file.put_line(fnd_file.log,'ABS_RECORDS(0).CATEGORY ==> '||ABS_RECORDS(0).CATEGORY );
1998 fnd_file.put_line(fnd_file.log,'ABS_RECORDS(0).START_DATE ==> '||ABS_RECORDS(0).START_DATE );
1999 fnd_file.put_line(fnd_file.log,'ABS_RECORDS(0).END_DATE ==> '||ABS_RECORDS(0).END_DATE );
2000 fnd_file.put_line(fnd_file.log,'ABS_RECORDS(1)CATEGORY ==> '||ABS_RECORDS(1).CATEGORY );
2001 fnd_file.put_line(fnd_file.log,'ABS_RECORDS(1)START_DATE ==> '||ABS_RECORDS(1).START_DATE );
2002 fnd_file.put_line(fnd_file.log,'ABS_RECORDS(1) END_DATE==> '||ABS_RECORDS(1).END_DATE );
2003 fnd_file.put_line(fnd_file.log,'ABS_RECORDS(2)CATEGORY ==> '||ABS_RECORDS(2).CATEGORY );
2004 fnd_file.put_line(fnd_file.log,'ABS_RECORDS(2) START_DATE==> '||ABS_RECORDS(2).START_DATE );
2005 fnd_file.put_line(fnd_file.log,'ABS_RECORDS(2) END_DATE==> '||ABS_RECORDS(2).END_DATE );
2006 l_first_category :=ABS_RECORDS(0).CATEGORY;
2007 l_first_start_date :=ABS_RECORDS(0).START_DATE;
2008 l_first_end_date :=ABS_RECORDS(0).END_DATE ;
2009 l_second_category :=ABS_RECORDS(1).CATEGORY;
2010 l_second_start_date :=ABS_RECORDS(1).START_DATE;
2011 l_second_end_date :=ABS_RECORDS(1).END_DATE;
2012 l_third_category :=ABS_RECORDS(2).CATEGORY;
2013 l_third_start_date :=ABS_RECORDS(2).START_DATE;
2014 l_third_end_date :=ABS_RECORDS(2).END_DATE;
2015 ELSE
2016
2017 l_first_category := null;
2018 l_first_start_date :=null;
2019 l_first_end_date := null;
2020 l_second_category := null;
2021 l_second_start_date := null;
2022 l_second_end_date := null;
2023 l_third_category := null;
2024 l_third_start_date := null;
2025 l_third_end_date := null;
2026 END IF;
2027 fnd_file.put_line(fnd_file.log,'FINALLY+++++++++++++');
2028 fnd_file.put_line(fnd_file.log,'(0).CATEGORY ==> '|| l_first_category);
2029 fnd_file.put_line(fnd_file.log,'(0).START_DATE ==> '||l_first_start_date );
2030 fnd_file.put_line(fnd_file.log,'(0).END_DATE ==> '|| l_first_end_date);
2031 fnd_file.put_line(fnd_file.log,'(1)CATEGORY ==> '||l_second_category );
2032 fnd_file.put_line(fnd_file.log,'(1)START_DATE ==> '||l_second_start_date );
2033 fnd_file.put_line(fnd_file.log,'(1) END_DATE==> '|| l_second_end_date);
2034 fnd_file.put_line(fnd_file.log,'(2)CATEGORY ==> '||l_third_category );
2035 fnd_file.put_line(fnd_file.log,'(2) START_DATE==> '|| l_third_start_date);
2036 fnd_file.put_line(fnd_file.log,'(2) END_DATE==> '|| l_third_end_date);
2037
2038
2039
2040 pay_action_information_api.create_action_information (
2041 p_action_information_id => l_action_info_id
2042 ,p_action_context_id => p_assignment_action_id
2043 ,p_action_context_type => 'AAP'
2044 ,p_object_version_number => l_ovn
2045 ,p_effective_date => l_effective_date
2046 ,p_source_id => NULL
2047 ,p_source_text => NULL
2048 ,p_action_information_category => 'EMEA REPORT INFORMATION'
2049 ,p_action_information1 => 'PYFITELA'
2050 ,p_action_information2 => 'PER1'
2051 ,p_action_information3 => g_payroll_action_id
2052 ,p_action_information4 => l_first_category
2053 ,p_action_information5 => fnd_date.date_to_canonical(l_first_start_date)
2054 ,p_action_information6 => fnd_date.date_to_canonical(l_first_end_date)
2055 ,p_action_information7 => l_second_category
2056 ,p_action_information8 => fnd_date.date_to_canonical(l_second_start_date)
2057 ,p_action_information9 => fnd_date.date_to_canonical(l_second_end_date)
2058 ,p_action_information10 => l_third_category
2059 ,p_action_information11 => fnd_date.date_to_canonical(l_third_start_date)
2060 ,p_action_information12 => fnd_date.date_to_canonical(l_third_end_date)
2061 ,p_action_information13 => FND_NUMBER.NUMBER_TO_CANONICAL(l_Sal_subject_pension_QTD)
2062 ,p_action_information14 => FND_NUMBER.NUMBER_TO_CANONICAL(l_bik_subject_pension_QTD)
2063 ,p_action_information15 => FND_NUMBER.NUMBER_TO_CANONICAL(l_tax_exp_subject_pension_QTD)
2064 ,p_action_information16 => null
2065 ,p_action_information17 => null
2066 ,p_action_information18 => null
2067 ,p_action_information19 => null
2068 ,p_action_information20 => null
2069 ,p_action_information21 => null
2070 ,p_action_information22 => null
2071 ,p_action_information23 => null
2072 ,p_action_information24 => null
2073 ,p_action_information25 => null
2074 ,p_action_information26 => null
2075 ,p_action_information27 => null
2076 ,p_action_information28 => null
2077 ,p_action_information29 => null
2078 ,p_action_information30 => l_person_id
2079 ,p_assignment_id => l_assignment_id );
2080
2081
2082
2083 FOR l_PERSON_ALL_EIT IN
2084 CSR_PERSON_ALL_EIT (l_person_id)
2085 LOOP
2086 pay_action_information_api.create_action_information (
2087 p_action_information_id => l_action_info_id,
2088 p_action_context_id => p_assignment_action_id,
2089 p_action_context_type => 'AAP',
2090 p_object_version_number => l_ovn,
2091 p_effective_date => l_effective_date,
2092 p_source_id => NULL,
2093 p_source_text => NULL,
2094 p_action_information_category=> 'EMEA REPORT INFORMATION',
2095 p_action_information1 => 'PYFITELA',
2096 p_action_information2 => 'PERSON_EIT',
2097 p_action_information3 => g_payroll_action_id,
2098 p_action_information4 => l_PERSON_ALL_EIT.PERSON_EXTRA_INFO_ID,
2099 p_action_information5 => l_PERSON_ALL_EIT.PEI_INFORMATION2,
2100 p_action_information6 => l_PERSON_ALL_EIT.PEI_INFORMATION3,
2101 p_action_information7 => l_PERSON_ALL_EIT.PEI_INFORMATION4,
2102 p_action_information8 => l_PERSON_ALL_EIT.PEI_INFORMATION5,
2103 p_action_information9 => l_PERSON_ALL_EIT.PEI_INFORMATION6,
2104 p_action_information10 => l_PERSON_ALL_EIT.PEI_INFORMATION7,
2105 p_action_information11 => NULL,
2106 p_action_information12 => NULL,
2107 p_action_information13 => NULL,
2108 p_action_information14 => NULL,
2109 p_action_information15 => NULL,
2110 p_action_information16 => NULL,
2111 p_action_information17 => NULL,
2112 p_action_information18 => NULL,
2113 p_action_information19 => NULL,
2114 p_action_information20 => NULL,
2115 p_action_information21 => NULL,
2116 p_action_information22 => NULL,
2117 p_action_information23 => NULL,
2118 p_action_information24 => NULL,
2119 p_action_information25 => NULL,
2120 p_action_information26 => NULL,
2121 p_action_information27 => NULL,
2122 p_action_information28 => NULL,
2123 p_action_information29 => NULL,
2124 p_action_information30 => l_person_id,
2125 p_assignment_id => l_assignment_id
2126 );
2127 fnd_file.put_line(fnd_file.log,'End of a person ==> '||l_action_info_id );
2128 END LOOP;
2129
2130 WHEN OTHERS
2131 THEN
2132 NULL;
2133 END;
2134 END IF;
2135 fnd_file.put_line(fnd_file.log,'Leaving Procedure ARCHIVE_CODE');
2136 IF g_debug THEN
2137 hr_utility.set_location(' Leaving Procedure ARCHIVE_CODE',390);
2138 END IF;
2139
2140 END ARCHIVE_CODE;
2141 END PAY_FI_ARCHIVE_TEL;