[Home] [Help]
PACKAGE BODY: APPS.PAY_FI_TELR
Source
1 PACKAGE BODY PAY_FI_TELR AS
2 /* $Header: pyfitelr.pkb 120.12.12000000.4 2007/03/22 12:55:01 dbehera noship $ */
3
4 PROCEDURE GET_DATA (
5 p_business_group_id IN NUMBER,
6 p_payroll_action_id IN VARCHAR2 ,
7 p_test_run IN VARCHAR2,
8 p_template_name IN VARCHAR2,
9 p_xml OUT NOCOPY CLOB
10 )
11 IS
12
13 /* Start of declaration*/
14
15 -- Variables needed for the report
16 l_counter number := 0;
17 l_payroll_action_id PAY_ACTION_INFORMATION.ACTION_INFORMATION1%TYPE;
18
19 L_TARGET_YEAR VARCHAR2(4);
20 l_TEL_Action_id varchar2(30);
21
22 l_EIT_PG_Action_Type per_people_extra_info.pei_information6%TYPE;
23 l_SHIFT_FROM_TEL DATE;
24 l_EIT_PT_value per_people_extra_info.pei_information6%TYPE;
25 l_EIT_PT_Reported per_people_extra_info.pei_information5%TYPE;
26 l_EIT_PG_Reported per_people_extra_info.pei_information5%TYPE;
27
28
29 l_EIT_Action_Type per_people_extra_info.pei_information6%TYPE;
30 l_EIT_IsReported per_people_extra_info.pei_information5%TYPE;
31 L_LEL_EMPLOYMENT_START_DATE DATE;
32
33 l_TIME_RECORD_1 boolean :=false;
34 l_TIME_RECORD_2 boolean :=false;
35 l_TIME_RECORD_5 boolean :=false;
36
37
38 L_OVN per_people_extra_info.object_version_number%TYPE;
39 /* End of declaration*/
40 /* Cursors */
41
42 Cursor csr_TEL_PERSON_DETAILS(
43 csr_v_pa_id PAY_ACTION_INFORMATION. ACTION_INFORMATION1%TYPE
44 )
45 IS
46 SELECT *
47 FROM PAY_ACTION_INFORMATION
48 WHERE ACTION_CONTEXT_TYPE = 'AAP'
49 AND ACTION_INFORMATION_CATEGORY='EMEA REPORT INFORMATION'
50 AND ACTION_INFORMATION2='PER'
51 AND ACTION_INFORMATION1='PYFITELA'
52 AND ACTION_INFORMATION3 = csr_v_pa_id
53 ORDER BY action_information8;
54
55 --lr_TEL_PERSON_DETAILS csr_TEL_PERSON_DETAILS%rowtype;
56
57 Cursor csr_TEL_PER1_DETAILS(
58 csr_v_pa_id PAY_ACTION_INFORMATION. ACTION_INFORMATION1%TYPE
59 ,CSR_V_PERSON_ID per_all_people_f.person_id%TYPE
60 )
61 IS
62 SELECT ACTION_INFORMATION4 FIRST_CODE,
63 decode(ACTION_INFORMATION6,null,
64 (to_CHAR(fnd_date.canonical_to_date(ACTION_INFORMATION5),'DDMMYY'))
65 ,(to_CHAR(fnd_date.canonical_to_date(ACTION_INFORMATION5),'DDMMYY'))||
66 (to_CHAR(fnd_date.canonical_to_date(ACTION_INFORMATION6),'DDMMYY')) ) FIRSTDATEPAIR,
67 ACTION_INFORMATION7 SECOND_CODE,
68 decode(ACTION_INFORMATION9,null,
69 (to_CHAR(fnd_date.canonical_to_date(ACTION_INFORMATION8),'DDMMYY'))
70 ,(to_CHAR(fnd_date.canonical_to_date(ACTION_INFORMATION8),'DDMMYY'))||
71 (to_CHAR(fnd_date.canonical_to_date(ACTION_INFORMATION9),'DDMMYY')) ) SECONDDATEPAIR,
72 ACTION_INFORMATION10 THIRD_CODE,
73 decode(ACTION_INFORMATION12,null,
74 (to_CHAR(fnd_date.canonical_to_date(ACTION_INFORMATION11),'DDMMYY'))
75 ,(to_CHAR(fnd_date.canonical_to_date(ACTION_INFORMATION11),'DDMMYY'))||
76 (to_CHAR(fnd_date.canonical_to_date(ACTION_INFORMATION12),'DDMMYY')) ) THIRDDATEPAIR
77 , PAI.*
78 FROM PAY_ACTION_INFORMATION PAI
79 WHERE ACTION_CONTEXT_TYPE = 'AAP'
80 AND ACTION_INFORMATION_CATEGORY='EMEA REPORT INFORMATION'
81 AND ACTION_INFORMATION2='PER1'
82 AND ACTION_INFORMATION1='PYFITELA'
83 AND ACTION_INFORMATION3 = csr_v_pa_id
84 AND ACTION_INFORMATION30 = CSR_V_PERSON_ID
85 ORDER BY action_information5 desc;
86
87 lr_TEL_PER1_DETAILS csr_TEL_PER1_DETAILS%rowtype;
88
89 Cursor CSR_REPORT_DETAILS(
90 csr_v_pa_id PAY_ACTION_INFORMATION. ACTION_INFORMATION1%TYPE
91 )
92 IS
93 SELECT *
94 FROM PAY_ACTION_INFORMATION
95 WHERE ACTION_CONTEXT_TYPE = 'PA'
96 AND ACTION_INFORMATION_CATEGORY='EMEA REPORT DETAILS'
97 AND ACTION_INFORMATION1='PYFITELA'
98 AND ACTION_CONTEXT_ID=csr_v_pa_id ;
99
100 LR_REPORT_DETAILS CSR_REPORT_DETAILS%rowtype;
101
102 CURSOR CSR_EIT_DETAILS (
103 csr_v_pa_id PAY_ACTION_INFORMATION. ACTION_INFORMATION1%TYPE,
104 CSR_V_PERSON_ID per_all_people_f.person_id%TYPE,
105 CSR_V_COLUMN_NAME per_people_extra_info.PEI_INFORMATION3%TYPE )
106 IS
107 SELECT *
108 FROM PAY_ACTION_INFORMATION
109 WHERE ACTION_CONTEXT_TYPE = 'AAP'
110 AND ACTION_INFORMATION_CATEGORY ='EMEA REPORT INFORMATION'
111 AND ACTION_INFORMATION1 ='PYFITELA'
112 AND ACTION_INFORMATION2 ='PERSON_EIT'
113 AND ACTION_INFORMATION3 = csr_v_pa_id
114 AND ACTION_INFORMATION6 = CSR_V_COLUMN_NAME
115 AND ACTION_INFORMATION30 = CSR_V_PERSON_ID;
116
117 LR_EIT_DETAILS CSR_EIT_DETAILS%ROWTYPE;
118
119 CURSOR CSR_PERSON_EIT (
120 CSR_V_PERSON_ID per_all_people_f.person_id%TYPE,
121 CSR_V_COLUMN_NAME per_people_extra_info.PEI_INFORMATION3%TYPE
122 )
123 IS
124 select PERSON_EXTRA_INFO_ID,
125 object_version_number,
126 person_id,
127 information_type,
128 pei_information_category,
129 pei_information1,
130 pei_information2,
131 pei_information3,
132 pei_information4,
133 pei_information5,
134 pei_information6,
135 pei_information7
136 from per_people_extra_info
137 where information_type='FI_PENSION'
138 AND PEI_INFORMATION_CATEGORY='FI_PENSION'
139 AND PEI_INFORMATION3=CSR_V_COLUMN_NAME
140 AND PERSON_ID = CSR_V_PERSON_ID;
141
142 LR_PERSON_EIT CSR_PERSON_EIT%ROWTYPE;
143
144 -- for searching record 2 type
145 CURSOR CSR_RECORD_TWO_TYPE (
146 csr_v_pa_id PAY_ACTION_INFORMATION. ACTION_INFORMATION1%TYPE,
147 CSR_V_PERSON_ID per_all_people_f.person_id%TYPE
148 )IS
149 SELECT DISTINCT(ACTION_INFORMATION3) "FOUND"
150 FROM PAY_ACTION_INFORMATION pai
151 WHERE pai.ACTION_CONTEXT_TYPE = 'AAP'
152 AND pai.ACTION_INFORMATION_CATEGORY ='EMEA REPORT INFORMATION'
153 AND pai.ACTION_INFORMATION2 ='PERSON_EIT'
154 AND pai.ACTION_INFORMATION1 ='PYFITELA'
155 AND pai.ACTION_INFORMATION3 = csr_v_pa_id
156 AND pai.ACTION_INFORMATION30 = CSR_V_PERSON_ID
157 AND pai.ACTION_INFORMATION8 ='N'
158 AND pai.ACTION_INFORMATION9 = decode (pai.ACTION_INFORMATION6,
159 'Pension Joining Date','I',
160 'Local Unit','U',
161 'Pension Group','U',
162 'Insurance Number','U') ;
163
164 LR_RECORD_TWO_TYPE CSR_RECORD_TWO_TYPE%ROWTYPE;
165
166 l_Record_two_type_found BOOLEAN := false;
167
168 /* End of Cursors */
169
170 BEGIN
171 --fnd_file.put_line(fnd_file.log,'=======================' );
172 --fnd_file.put_line(fnd_file.log,'== p_payroll_action_id ==' || p_payroll_action_id);
173 --fnd_file.put_line(fnd_file.log,'=======================' );
174
175
176 IF p_payroll_action_id IS NULL THEN
177
178 BEGIN
179
180 SELECT payroll_action_id
181 INTO l_payroll_action_id
182 FROM pay_payroll_actions ppa,
183 fnd_conc_req_summary_v fcrs,
184 fnd_conc_req_summary_v fcrs1
185 WHERE fcrs.request_id = FND_GLOBAL.CONC_REQUEST_ID
186 AND fcrs.priority_request_id = fcrs1.priority_request_id
187 AND ppa.request_id between fcrs1.request_id and fcrs.request_id
188 AND ppa.request_id = fcrs1.request_id;
189
190 EXCEPTION
191 WHEN others THEN
192 NULL;
193 END ;
194
195 ELSE
196
197 l_payroll_action_id :=p_payroll_action_id;
198
199 END IF;
200
201 hr_utility.set_location('Entered Procedure GETDATA',10);
202
203 /* Pick up the details belonging to Header */
204 OPEN CSR_REPORT_DETAILS( l_payroll_action_id);
205 FETCH CSR_REPORT_DETAILS INTO LR_REPORT_DETAILS;
206 CLOSE CSR_REPORT_DETAILS;
207
208
209
210 hr_utility.set_location('Before populating pl/sql table',20);
211 FOR lr_TEL_PERSON_DETAILS IN csr_TEL_PERSON_DETAILS( l_payroll_action_id)
212 LOOP
213 l_TIME_RECORD_1 :=false;
214 l_TIME_RECORD_2 :=false;
215 l_TIME_RECORD_5 :=false;
216 l_Record_two_type_found := false;
217 --fnd_file.put_line(fnd_file.log,'== person ==' || lr_TEL_PERSON_DETAILS.ACTION_INFORMATION30);
218 OPEN CSR_RECORD_TWO_TYPE( l_payroll_action_id,
219 lr_TEL_PERSON_DETAILS.ACTION_INFORMATION30);
220 FETCH CSR_RECORD_TWO_TYPE
221 INTO LR_RECORD_TWO_TYPE;
222
223 IF CSR_RECORD_TWO_TYPE%NOTFOUND
224 THEN
225 -- Not found ; search for other type
226 --fnd_file.put_line(fnd_file.log,'== first falied null ==');
227
228 IF LR_REPORT_DETAILS.ACTION_INFORMATION8 ='A'
229 THEN
230 l_Record_two_type_found := TRUE;
231 ELSE
232 l_Record_two_type_found := false;
233 END IF;
234
235 ELSE
236 -- Found the record two type
237 -- so set this variable to true exit this if
238 --fnd_file.put_line(fnd_file.log,'== first Passed ==');
239 l_Record_two_type_found := true;
240 END IF;
241 CLOSE CSR_RECORD_TWO_TYPE;
242
243 IF l_Record_two_type_found = TRUE
244 THEN
245 TEL_DATA(l_counter).TagName := 'PERSON';
246 TEL_DATA(l_counter).TagValue := 'PERSON';
247 l_counter := l_counter + 1;
248
249 TEL_DATA(l_counter).TagName := 'EMP_RECORD';
250 TEL_DATA(l_counter).TagValue := 'EMP_RECORD';
251 l_counter := l_counter + 1;
252
253 TEL_DATA(l_counter).TagName := 'RECORD_ONE';
254 TEL_DATA(l_counter).TagValue :=hr_general.decode_lookup('FI_FORM_LABELS','REC_A');
255 l_counter := l_counter + 1;
256
257 TEL_DATA(l_counter).TagName := 'INSURANCE_POLICY_NUMBER';
258 TEL_DATA(l_counter).TagValue :=lr_TEL_PERSON_DETAILS.ACTION_INFORMATION4;
259 l_counter := l_counter + 1;
260
261 TEL_DATA(l_counter).TagName := 'PIN';
262 TEL_DATA(l_counter).TagValue := lr_TEL_PERSON_DETAILS.ACTION_INFORMATION5;
263 l_counter := l_counter + 1;
264
265 TEL_DATA(l_counter).TagName := 'PENSION_GROUP';
266 TEL_DATA(l_counter).TagValue := lr_TEL_PERSON_DETAILS.ACTION_INFORMATION6;
267 l_counter := l_counter + 1;
268
269 TEL_DATA(l_counter).TagName := 'DEPARTMENT';
270 TEL_DATA(l_counter).TagValue := lr_TEL_PERSON_DETAILS.ACTION_INFORMATION7;
271 l_counter := l_counter + 1;
272
273 TEL_DATA(l_counter).TagName :='SUR_FIRST_NAME';
274 TEL_DATA(l_counter).TagValue := lr_TEL_PERSON_DETAILS.ACTION_INFORMATION8;
275 l_counter := l_counter + 1;
276
277 TEL_DATA(l_counter).TagName := 'EMP_RECORD';
278 TEL_DATA(l_counter).TagValue := 'EMP_RECORD_END';
279 l_counter := l_counter + 1;
280
281 TEL_DATA(l_counter).TagName := 'TIME_RECORD';
282 TEL_DATA(l_counter).TagValue := 'TIME_RECORD';
283 l_counter := l_counter + 1;
284
285 --fnd_file.put_line(fnd_file.log,'Added TIME_RECORD Start==> ' );
286 --fnd_file.put_line(fnd_file.log,'Record One displayed ' );
287 --fnd_file.put_line(fnd_file.log,'INSURANCE_POLICY_NUMBER' ||' '||'PIN ' ||' '||
288 --'PENSION_GROUP' ||' '||'DEPARTMENT' ||' '||'SUR_FIRST_NAME ');
289 --fnd_file.put_line(fnd_file.log,
290 --rpad(lr_TEL_PERSON_DETAILS.ACTION_INFORMATION4,length('INSURANCE_POLICY_NUMBER')) ||' '||
291 --rpad(lr_TEL_PERSON_DETAILS.ACTION_INFORMATION5,12) ||' '||
292 --rpad(lr_TEL_PERSON_DETAILS.ACTION_INFORMATION20,length('PENSION_GROUP')) ||' '||
293 --rpad(lr_TEL_PERSON_DETAILS.ACTION_INFORMATION7,length('DEPARTMENT')) ||' '||
294 --rpad(lr_TEL_PERSON_DETAILS.ACTION_INFORMATION8,20)
295 --);
296
297 -- Pick up the person Reocrd 1 Also
298 OPEN csr_TEL_PER1_DETAILS( l_payroll_action_id,
299 lr_TEL_PERSON_DETAILS.ACTION_INFORMATION30);
300 FETCH csr_TEL_PER1_DETAILS
301 INTO lr_TEL_PER1_DETAILS;
302 CLOSE csr_TEL_PER1_DETAILS;
303 -- Pick up the person Reocrd 1 Also
304 -- ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
305 -- TO find the Employment start dispatch
306 --fnd_file.put_line(fnd_file.log,'=========================' );
307
308
309 --fnd_file.put_line(fnd_file.log,'=========================Employment start dispatch=========================' );
310
311 OPEN CSR_EIT_DETAILS(l_payroll_action_id,lr_TEL_PERSON_DETAILS.ACTION_INFORMATION30,'Pension Joining Date');
312 FETCH CSR_EIT_DETAILS
313 INTO LR_EIT_DETAILS;
314 CLOSE CSR_EIT_DETAILS;
315 /*
316 OPEN CSR_PERSON_EIT(lr_TEL_PERSON_DETAILS.ACTION_INFORMATION30,'Pension Joining Date');
317 FETCH CSR_PERSON_EIT
318 INTO LR_PERSON_EIT;
319 CLOSE CSR_PERSON_EIT;
320 */
321 l_EIT_Action_Type := LR_EIT_DETAILS.ACTION_INFORMATION9;
322 l_EIT_IsReported := LR_EIT_DETAILS.ACTION_INFORMATION8;
323
324 --fnd_file.put_line(fnd_file.log,'l_EIT_Action_Type for PJD ==> '||l_EIT_Action_Type );
325 --fnd_file.put_line(fnd_file.log,'l_EIT_IsReported PJD ==> '||l_EIT_IsReported );
326
327
328
329 IF l_EIT_Action_Type ='I' AND l_EIT_IsReported ='N'
330 THEN
331 l_TEL_Action_id := '1';
332 l_TIME_RECORD_1 := true;
333
334 --fnd_file.put_line(fnd_file.log,'l_TEL_Action_id ==> '||l_TEL_Action_id );
335
336 -- ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
337 --fnd_file.put_line(fnd_file.log,'Adding Record 2 for Action 1 ==> '||l_TEL_Action_id );
338
339 TEL_DATA(l_counter).TagName := 'TIME_RECORD_TYPE';
340 TEL_DATA(l_counter).TagValue := 'TIME_RECORD_TYPE';
341 l_counter := l_counter + 1;
342
343 TEL_DATA(l_counter).TagName := 'RECORD_TWO';
344 TEL_DATA(l_counter).TagValue := hr_general.decode_lookup('FI_FORM_LABELS','REC_B');
345 l_counter := l_counter + 1;
346
347 -- Find the Action Id here
348 TEL_DATA(l_counter).TagName := 'ACTION_ID';
352
349 TEL_DATA(l_counter).TagValue := '1';
350 l_counter := l_counter + 1;
351 -- Find the Action Id here
353 TEL_DATA(l_counter).TagName := 'START_DATE';
354 TEL_DATA(l_counter).TagValue := fnd_date.canonical_to_date(lr_TEL_PERSON_DETAILS.ACTION_INFORMATION9);
355 l_counter := l_counter + 1;
356
357 TEL_DATA(l_counter).TagName := 'START_DATE_DDMMYY';
358 TEL_DATA(l_counter).TagValue := to_char(fnd_date.canonical_to_date(lr_TEL_PERSON_DETAILS.ACTION_INFORMATION9),'DDMMYY');
359 l_counter := l_counter + 1;
360
361 -- This Target year has to be included in case of only ACTION ID 2
362 -- Find if from g_effective date of Report prameters
363 --IF LR_REPORT_DETAILS.ACTION_INFORMATION8 = 'Y'
364 --THEN
365 -- L_TARGET_YEAR := to_char(fnd_date.canonical_to_date(LR_REPORT_DETAILS.ACTION_INFORMATION8),'RRRR');
366 --ELSE
367 L_TARGET_YEAR := null;
368 -- END IF;
369
370 TEL_DATA(l_counter).TagName := 'TARGET_YEAR_FOR_ANNUAL_INCOME';
371 TEL_DATA(l_counter).TagValue := L_TARGET_YEAR;
372 l_counter := l_counter + 1;
373
374 -- Find if from g_effective date of Report prameters
375
376 -- Find it from Balance values from 11 to 16
377 TEL_DATA(l_counter).TagName := 'INCOME';
378 IF LR_REPORT_DETAILS.ACTION_INFORMATION8 = 'M'
379 THEN
380 TEL_DATA(l_counter).TagValue := TO_CHAR(NVL(FND_NUMBER.CANONICAL_TO_NUMBER(lr_TEL_PERSON_DETAILS.ACTION_INFORMATION10) +
381 FND_NUMBER.CANONICAL_TO_NUMBER(lr_TEL_PERSON_DETAILS.ACTION_INFORMATION11) +
382 FND_NUMBER.CANONICAL_TO_NUMBER(lr_TEL_PERSON_DETAILS.ACTION_INFORMATION12),0) ,'999G999G990D99' );
383 ELSIF LR_REPORT_DETAILS.ACTION_INFORMATION8 = 'A'
384 THEN
385 TEL_DATA(l_counter).TagValue := TO_CHAR(NVL(FND_NUMBER.CANONICAL_TO_NUMBER(lr_TEL_PERSON_DETAILS.ACTION_INFORMATION13) +
386 FND_NUMBER.CANONICAL_TO_NUMBER(lr_TEL_PERSON_DETAILS.ACTION_INFORMATION14) +
387 FND_NUMBER.CANONICAL_TO_NUMBER(lr_TEL_PERSON_DETAILS.ACTION_INFORMATION15) ,0) ,'999G999G990D99' );
388 ELSIF LR_REPORT_DETAILS.ACTION_INFORMATION8 = 'Q'
389 THEN
390 TEL_DATA(l_counter).TagValue := TO_CHAR(NVL(FND_NUMBER.CANONICAL_TO_NUMBER(lr_TEL_PER1_DETAILS.ACTION_INFORMATION13) +
391 FND_NUMBER.CANONICAL_TO_NUMBER(lr_TEL_PER1_DETAILS.ACTION_INFORMATION14) +
392 FND_NUMBER.CANONICAL_TO_NUMBER(lr_TEL_PER1_DETAILS.ACTION_INFORMATION15) ,0) ,'999G999G990D99' );
393 END IF;
394
395 l_counter := l_counter + 1;
396 -- Find it from Balance values
397 /*
398 TEL_DATA(l_counter).TagName := 'BENEFIT_IN_KIND';
399 TEL_DATA(l_counter).TagValue := null;
400 l_counter := l_counter + 1;
401 */
402
403 TEL_DATA(l_counter).TagName := 'TERMINATION_DATE';
404 TEL_DATA(l_counter).TagValue := NULL;
405 -- TEL_DATA(l_counter).TagValue :=lr_TEL_PERSON_DETAILS.ACTION_INFORMATION8;
406 l_counter := l_counter + 1;
407
408 TEL_DATA(l_counter).TagName := 'TERMINATION_DATE_DDMMYY';
409 TEL_DATA(l_counter).TagValue := NULL;
410 l_counter := l_counter + 1;
411
412 TEL_DATA(l_counter).TagName := 'CAUSE_OF_TERMINATION';
413 TEL_DATA(l_counter).TagValue := NULL;
414 -- TEL_DATA(l_counter).TagValue := fnd_date.canonical_to_date(lr_TEL_PERSON_DETAILS.ACTION_INFORMATION9);
415 l_counter := l_counter + 1;
416
417 TEL_DATA(l_counter).TagName := 'YEARLY_TEL_INCOME_PRIOR';
418 TEL_DATA(l_counter).TagValue := NULL;
419 --TEL_DATA(l_counter).TagValue := to_number(lr_TEL_PERSON_DETAILS.ACTION_INFORMATION18) + to_number(lr_TEL_PERSON_DETAILS.ACTION_INFORMATION19);
420 l_counter := l_counter + 1;
421
422 /*
423 TEL_DATA(l_counter).TagName := 'BENEFIT_IN_KIND_PRIOR';
424 TEL_DATA(l_counter).TagValue := NULL;
425 --TEL_DATA(l_counter).TagValue := to_number(lr_TEL_PERSON_DETAILS.ACTION_INFORMATION20);
426 l_counter := l_counter + 1;
427
428 TEL_DATA(l_counter).TagName :='LEL_EMPLOYMENT_START_DATE';
429 TEL_DATA(l_counter).TagValue := NULL;
430 --TEL_DATA(l_counter).TagValue := fnd_date.canonical_to_date( lr_TEL_PERSON_DETAILS.ACTION_INFORMATION9 );
431 l_counter := l_counter + 1;
432
433 TEL_DATA(l_counter).TagName :='LEL_EMPLOYMENT_START_DATE_DDMMYY';
434 TEL_DATA(l_counter).TagValue := NULL;
435 --TEL_DATA(l_counter).TagValue := fnd_date.canonical_to_date( lr_TEL_PERSON_DETAILS.ACTION_INFORMATION9 );
436 l_counter := l_counter + 1;
437
438 -- find it If there is change in Pension Group report the date of starting new record from EIT
439
443
440 TEL_DATA(l_counter).TagName := 'SHIFT_FRM_ANOTHER_TEL_EMP';
441 TEL_DATA(l_counter).TagValue := null;
442 l_counter := l_counter + 1;
444 TEL_DATA(l_counter).TagName := 'SHIFT_FRM_ANOTHER_TEL_EMP_DDMMYY';
445 TEL_DATA(l_counter).TagValue := null;
446 l_counter := l_counter + 1;
447
448 -- find it If there is change in Pension Group report the date of starting new record from EIT
449 TEL_DATA(l_counter).TagName := 'CURRENCY';
450 TEL_DATA(l_counter).TagValue := lr_TEL_PERSON_DETAILS.ACTION_INFORMATION25;
451 l_counter := l_counter + 1;
452
453 */
454
455
456 TEL_DATA(l_counter).TagName := 'REPORTING_METHOD';
457 TEL_DATA(l_counter).TagValue := to_number(lr_TEL_PERSON_DETAILS.ACTION_INFORMATION29);
458 l_counter := l_counter + 1;
459
460
461 TEL_DATA(l_counter).TagName := 'TIME_RECORD_TYPE';
462 TEL_DATA(l_counter).TagValue := 'TIME_RECORD_TYPE_END';
463 l_counter := l_counter + 1;
464
465 END IF; -- For the Action ID = '1' IF
466
467 --fnd_file.put_line(fnd_file.log,'=========================END===============================================' );
468 --fnd_file.put_line(fnd_file.log,'=========================Annual Report=====================================' );
469 -- Annual Report
470 --fnd_file.put_line(fnd_file.log,'annual_report ??? ==> '||LR_REPORT_DETAILS.ACTION_INFORMATION8 );
471
472 IF LR_REPORT_DETAILS.ACTION_INFORMATION8 = 'A'
473 THEN
474 l_TEL_Action_id := '2';
475 l_TIME_RECORD_2 := true;
476 --fnd_file.put_line(fnd_file.log,'l_TEL_Action_id ==> '||l_TEL_Action_id );
477
478 --fnd_file.put_line(fnd_file.log,'Adding Record 2 for Action 2 ==> '||l_TEL_Action_id );
479
480 TEL_DATA(l_counter).TagName := 'TIME_RECORD_TYPE';
481 TEL_DATA(l_counter).TagValue := 'TIME_RECORD_TYPE';
482 l_counter := l_counter + 1;
483
484 TEL_DATA(l_counter).TagName := 'RECORD_TWO';
485 TEL_DATA(l_counter).TagValue := hr_general.decode_lookup('FI_FORM_LABELS','REC_B');
486 l_counter := l_counter + 1;
487
488 -- Find the Action Id here
489 TEL_DATA(l_counter).TagName := 'ACTION_ID';
490 TEL_DATA(l_counter).TagValue := '2';
491 l_counter := l_counter + 1;
492 -- Find the Action Id here
493
494 TEL_DATA(l_counter).TagName := 'START_DATE';
495 TEL_DATA(l_counter).TagValue := fnd_date.canonical_to_date( lr_TEL_PERSON_DETAILS.ACTION_INFORMATION9 );
496 l_counter := l_counter + 1;
497
498 TEL_DATA(l_counter).TagName := 'START_DATE_DDMMYY';
499 TEL_DATA(l_counter).TagValue := TO_CHAR(fnd_date.canonical_to_date( lr_TEL_PERSON_DETAILS.ACTION_INFORMATION9 ),'DDMMYY');
500 l_counter := l_counter + 1;
501
502 -- Find if from g_effective date of Report prameters
503
504 L_TARGET_YEAR := to_char(fnd_date.canonical_to_date(LR_REPORT_DETAILS.ACTION_INFORMATION9),'RRRR');
505
506 TEL_DATA(l_counter).TagName := 'TARGET_YEAR_FOR_ANNUAL_INCOME';
507 TEL_DATA(l_counter).TagValue := L_TARGET_YEAR;
508 l_counter := l_counter + 1;
509
510 -- Find if from g_effective date of Report prameters
511
512 -- Find it from Balance values from 11 to 16
513 TEL_DATA(l_counter).TagName := 'INCOME';
517 l_counter := l_counter + 1;
514 TEL_DATA(l_counter).TagValue := TO_CHAR(NVL(FND_NUMBER.CANONICAL_TO_NUMBER(lr_TEL_PERSON_DETAILS.ACTION_INFORMATION13) +
515 FND_NUMBER.CANONICAL_TO_NUMBER(lr_TEL_PERSON_DETAILS.ACTION_INFORMATION14) +
516 FND_NUMBER.CANONICAL_TO_NUMBER(lr_TEL_PERSON_DETAILS.ACTION_INFORMATION15) ,0) ,'999G999G990D99' );
518
519 /* -- Find it from Balance values
520 -- In case of Annual Report or Start and Termination we need to report this field
521 TEL_DATA(l_counter).TagName := 'BENEFIT_IN_KIND';
522 TEL_DATA(l_counter).TagValue := TO_CHAR(NVL(FND_NUMBER.CANONICAL_TO_NUMBER(lr_TEL_PERSON_DETAILS.ACTION_INFORMATION14) ,0) ,'999G999G990D99' );
523 l_counter := l_counter + 1;
524 */
525
526 TEL_DATA(l_counter).TagName := 'TERMINATION_DATE';
527 TEL_DATA(l_counter).TagValue := NULL;
528 -- TEL_DATA(l_counter).TagValue :=fnd_date.canonical_to_date(lr_TEL_PERSON_DETAILS.ACTION_INFORMATION8);
529 l_counter := l_counter + 1;
530
531 TEL_DATA(l_counter).TagName := 'TERMINATION_DATE_DDMMYY';
532 TEL_DATA(l_counter).TagValue := NULL;
533 l_counter := l_counter + 1;
534
535
536 TEL_DATA(l_counter).TagName := 'CAUSE_OF_TERMINATION';
537 TEL_DATA(l_counter).TagValue := NULL;
538 --TEL_DATA(l_counter).TagValue := lr_TEL_PERSON_DETAILS.ACTION_INFORMATION9;
539 l_counter := l_counter + 1;
540
541 TEL_DATA(l_counter).TagName := 'YEARLY_TEL_INCOME_PRIOR';
542 TEL_DATA(l_counter).TagValue := NULL;
543 --TEL_DATA(l_counter).TagValue := to_number(lr_TEL_PERSON_DETAILS.ACTION_INFORMATION18 ) + to_number(lr_TEL_PERSON_DETAILS.ACTION_INFORMATION19 );
544 l_counter := l_counter + 1;
545
546 /*
547 TEL_DATA(l_counter).TagName := 'BENEFIT_IN_KIND_PRIOR';
548 TEL_DATA(l_counter).TagValue := NULL;
549 --TEL_DATA(l_counter).TagValue := to_number( lr_TEL_PERSON_DETAILS.ACTION_INFORMATION20 );
550 l_counter := l_counter + 1;
551
552 TEL_DATA(l_counter).TagName :='LEL_EMPLOYMENT_START_DATE';
553 TEL_DATA(l_counter).TagValue := NULL;
554 l_counter := l_counter + 1;
555
556 TEL_DATA(l_counter).TagName :='LEL_EMPLOYMENT_START_DATE_DDMMYY';
557 TEL_DATA(l_counter).TagValue := NULL;
558 l_counter := l_counter + 1;
559
560 -- find it If there is change in Pension Group report the date of starting new record from EIT
561
562 TEL_DATA(l_counter).TagName := 'SHIFT_FRM_ANOTHER_TEL_EMP';
563 TEL_DATA(l_counter).TagValue := null;
564 l_counter := l_counter + 1;
565
566 TEL_DATA(l_counter).TagName := 'SHIFT_FRM_ANOTHER_TEL_EMP_DDMMYY';
567 TEL_DATA(l_counter).TagValue := null;
568 l_counter := l_counter + 1;
569 -- find it If there is change in Pension Group report the date of starting new record from EIT
570 TEL_DATA(l_counter).TagName := 'CURRENCY';
571 TEL_DATA(l_counter).TagValue := lr_TEL_PERSON_DETAILS.ACTION_INFORMATION25;
572 l_counter := l_counter + 1;
573 */
574
575 TEL_DATA(l_counter).TagName := 'REPORTING_METHOD';
576 TEL_DATA(l_counter).TagValue := to_number(lr_TEL_PERSON_DETAILS.ACTION_INFORMATION29);
577 l_counter := l_counter + 1;
578
579 TEL_DATA(l_counter).TagName := 'TIME_RECORD_TYPE';
580 TEL_DATA(l_counter).TagValue := 'TIME_RECORD_TYPE_END';
581 l_counter := l_counter + 1;
582
583 END IF; -- For the Action ID = '2' IF
584 --fnd_file.put_line(fnd_file.log,'=========================END===============================================' );
585 --fnd_file.put_line(fnd_file.log,'=========================Terminated 5=================' );
586
587 -- 5 = Terminated
588 OPEN CSR_EIT_DETAILS(l_payroll_action_id,lr_TEL_PERSON_DETAILS.ACTION_INFORMATION30,'Pension Types');
589 FETCH CSR_EIT_DETAILS
590 INTO LR_EIT_DETAILS;
591 CLOSE CSR_EIT_DETAILS;
592 /*
593 OPEN CSR_PERSON_EIT(lr_TEL_PERSON_DETAILS.ACTION_INFORMATION30,'Pension Types');
594 FETCH CSR_PERSON_EIT
595 INTO LR_PERSON_EIT;
596 CLOSE CSR_PERSON_EIT;
597 */
598 l_EIT_Action_Type := LR_EIT_DETAILS.ACTION_INFORMATION9;
599
600 --fnd_file.put_line(fnd_file.log,'Terminated ==> '||l_EIT_Action_Type );
601
602 IF lr_TEL_PERSON_DETAILS.ACTION_INFORMATION16 IS NOT NULL
603 --AND LR_EIT_DETAILS.ACTION_INFORMATION8 ='N'
604 THEN
605 l_TEL_Action_id := '5';
606 l_TIME_RECORD_5 := true;
607
608 --fnd_file.put_line(fnd_file.log,'l_TEL_Action_id ==> '||l_TEL_Action_id );
609 --fnd_file.put_line(fnd_file.log,'Adding Record 2 for Action 5 ==> '||l_TEL_Action_id );
610
611 TEL_DATA(l_counter).TagName := 'TIME_RECORD_TYPE';
615 TEL_DATA(l_counter).TagName := 'RECORD_TWO';
612 TEL_DATA(l_counter).TagValue := 'TIME_RECORD_TYPE';
613 l_counter := l_counter + 1;
614
616 TEL_DATA(l_counter).TagValue := '2';
617 l_counter := l_counter + 1;
618
619 -- Find the Action Id here
620 TEL_DATA(l_counter).TagName := 'ACTION_ID';
621 TEL_DATA(l_counter).TagValue := '5';
622 l_counter := l_counter + 1;
623 -- Find the Action Id here
624
625 TEL_DATA(l_counter).TagName := 'START_DATE';
626 TEL_DATA(l_counter).TagValue := fnd_date.canonical_to_date(lr_TEL_PERSON_DETAILS.ACTION_INFORMATION9 );
627 l_counter := l_counter + 1;
628
629 TEL_DATA(l_counter).TagName := 'START_DATE_DDMMYY';
630 TEL_DATA(l_counter).TagValue := TO_CHAR(fnd_date.canonical_to_date( lr_TEL_PERSON_DETAILS.ACTION_INFORMATION9 ),'DDMMYY');
631 l_counter := l_counter + 1;
632
633 -- Find if from g_effective date of Report prameters
634
635 --IF LR_REPORT_DETAILS.ACTION_INFORMATION8 = 'Y'
636 --THEN
637 --L_TARGET_YEAR := to_char(fnd_date.canonical_to_date(LR_REPORT_DETAILS.ACTION_INFORMATION8),'RRRR');
638 --ELSE
639 L_TARGET_YEAR := null;
640 --END IF;
641
642 TEL_DATA(l_counter).TagName := 'TARGET_YEAR_FOR_ANNUAL_INCOME';
643 TEL_DATA(l_counter).TagValue := L_TARGET_YEAR;
644 l_counter := l_counter + 1;
645
646 -- Find if from g_effective date of Report prameters
647
648 -- Find it from Balance values from 11 to 16
649 TEL_DATA(l_counter).TagName := 'INCOME';
650 IF LR_REPORT_DETAILS.ACTION_INFORMATION8 = 'M'
651 THEN
652 TEL_DATA(l_counter).TagValue :=TO_CHAR(NVL( FND_NUMBER.CANONICAL_TO_NUMBER(lr_TEL_PERSON_DETAILS.ACTION_INFORMATION10) +
653 FND_NUMBER.CANONICAL_TO_NUMBER(lr_TEL_PERSON_DETAILS.ACTION_INFORMATION11) +
654 FND_NUMBER.CANONICAL_TO_NUMBER(lr_TEL_PERSON_DETAILS.ACTION_INFORMATION12),0) ,'999G999G990D99' );
655 ELSIF LR_REPORT_DETAILS.ACTION_INFORMATION8 = 'A'
656 THEN
657 TEL_DATA(l_counter).TagValue := TO_CHAR(NVL(FND_NUMBER.CANONICAL_TO_NUMBER(lr_TEL_PERSON_DETAILS.ACTION_INFORMATION13) +
658 FND_NUMBER.CANONICAL_TO_NUMBER(lr_TEL_PERSON_DETAILS.ACTION_INFORMATION14) +
659 FND_NUMBER.CANONICAL_TO_NUMBER(lr_TEL_PERSON_DETAILS.ACTION_INFORMATION15) ,0) ,'999G999G990D99' );
660 ELSIF LR_REPORT_DETAILS.ACTION_INFORMATION8 = 'Q'
661 THEN
662 TEL_DATA(l_counter).TagValue := TO_CHAR(NVL(FND_NUMBER.CANONICAL_TO_NUMBER(lr_TEL_PER1_DETAILS.ACTION_INFORMATION13) +
663 FND_NUMBER.CANONICAL_TO_NUMBER(lr_TEL_PER1_DETAILS.ACTION_INFORMATION14) +
664 FND_NUMBER.CANONICAL_TO_NUMBER(lr_TEL_PER1_DETAILS.ACTION_INFORMATION15),0) ,'999G999G990D99' );
665 END IF;
666
667 l_counter := l_counter + 1;
668 -- Find it from Balance values
669
670 /*
671 TEL_DATA(l_counter).TagName := 'BENEFIT_IN_KIND';
672 TEL_DATA(l_counter).TagValue := TO_CHAR(NVL(FND_NUMBER.CANONICAL_TO_NUMBER(lr_TEL_PERSON_DETAILS.ACTION_INFORMATION11),0) ,'999G999G990D99' );
673 l_counter := l_counter + 1;
674
675 */
676 TEL_DATA(l_counter).TagName := 'TERMINATION_DATE';
677 TEL_DATA(l_counter).TagValue :=fnd_date.canonical_to_date(lr_TEL_PERSON_DETAILS.ACTION_INFORMATION16);
678 l_counter := l_counter + 1;
679
680 TEL_DATA(l_counter).TagName := 'TERMINATION_DATE_DDMMYY';
681 TEL_DATA(l_counter).TagValue :=TO_CHAR(fnd_date.canonical_to_date(lr_TEL_PERSON_DETAILS.ACTION_INFORMATION16),'DDMMYY');
682 l_counter := l_counter + 1;
683
684
685 TEL_DATA(l_counter).TagName := 'CAUSE_OF_TERMINATION';
686 TEL_DATA(l_counter).TagValue := lr_TEL_PERSON_DETAILS.ACTION_INFORMATION17;
687 l_counter := l_counter + 1;
688
689 TEL_DATA(l_counter).TagName := 'YEARLY_TEL_INCOME_PRIOR';
690 TEL_DATA(l_counter).TagValue := TO_CHAR(NVL(FND_NUMBER.CANONICAL_TO_NUMBER(lr_TEL_PERSON_DETAILS.ACTION_INFORMATION18) + FND_NUMBER.CANONICAL_TO_NUMBER(lr_TEL_PERSON_DETAILS.ACTION_INFORMATION19) ,0) ,'999G999G990D99' );
691 l_counter := l_counter + 1;
692
693 IF NVL(FND_NUMBER.CANONICAL_TO_NUMBER(lr_TEL_PERSON_DETAILS.ACTION_INFORMATION18) + FND_NUMBER.CANONICAL_TO_NUMBER(lr_TEL_PERSON_DETAILS.ACTION_INFORMATION19) ,0) > 0 THEN
694
695 TEL_DATA(l_counter).TagName := 'TARGET_YEAR_FOR_TERM';
696 TEL_DATA(l_counter).TagValue := TO_CHAR(fnd_date.canonical_to_date(lr_TEL_PERSON_DETAILS.ACTION_INFORMATION16),'YYYY');
697 l_counter := l_counter + 1;
698
699 END IF;
700
701 /*
702 TEL_DATA(l_counter).TagName := 'BENEFIT_IN_KIND_PRIOR';
703 TEL_DATA(l_counter).TagValue := TO_CHAR(NVL(FND_NUMBER.CANONICAL_TO_NUMBER(lr_TEL_PERSON_DETAILS.ACTION_INFORMATION20) ,0) ,'999G999G990D99' );
707 TEL_DATA(l_counter).TagValue := NULL;
704 l_counter := l_counter + 1;
705
706 TEL_DATA(l_counter).TagName :='LEL_EMPLOYMENT_START_DATE';
708 l_counter := l_counter + 1;
709
710 TEL_DATA(l_counter).TagName :='LEL_EMPLOYMENT_START_DATE_DDMMYY';
711 TEL_DATA(l_counter).TagValue := NULL;
712 l_counter := l_counter + 1;
713
714 -- find it If there is change in Pension Group report the date of starting new record from EIT
715
716 TEL_DATA(l_counter).TagName := 'SHIFT_FRM_ANOTHER_TEL_EMP';
717 TEL_DATA(l_counter).TagValue := null;
718 l_counter := l_counter + 1;
719
720 TEL_DATA(l_counter).TagName := 'SHIFT_FRM_ANOTHER_TEL_EMP_DDMMYY';
721 TEL_DATA(l_counter).TagValue := null;
722 l_counter := l_counter + 1;
723
724 -- find it If there is change in Pension Group report the date of starting new record from EIT
725 TEL_DATA(l_counter).TagName := 'CURRENCY';
726 TEL_DATA(l_counter).TagValue := lr_TEL_PERSON_DETAILS.ACTION_INFORMATION25;
727 l_counter := l_counter + 1;
728 */
729
730 TEL_DATA(l_counter).TagName := 'REPORTING_METHOD';
731 TEL_DATA(l_counter).TagValue := to_number(lr_TEL_PERSON_DETAILS.ACTION_INFORMATION29);
732 l_counter := l_counter + 1;
733
734
735 TEL_DATA(l_counter).TagName := 'TIME_RECORD_TYPE';
736 TEL_DATA(l_counter).TagValue := 'TIME_RECORD_TYPE_END';
737 l_counter := l_counter + 1;
738
739 END IF; -- End of Action Type = S
740
741
742
743 TEL_DATA(l_counter).TagName := 'TIME_RECORD';
744 TEL_DATA(l_counter).TagValue := 'TIME_RECORD_END';
745 l_counter := l_counter + 1;
746
747
748 IF l_TIME_RECORD_1
749 or l_TIME_RECORD_2
750 or l_TIME_RECORD_5
751 THEN
752 -- If atleast one date pair exists then add the record 3
753 IF lr_TEL_PER1_DETAILS.ACTION_INFORMATION4 IS NOT NULL
754 or lr_TEL_PER1_DETAILS.ACTION_INFORMATION7 IS NOT NULL
755 or lr_TEL_PER1_DETAILS.ACTION_INFORMATION10 IS NOT NULL
756 THEN
757 TEL_DATA(l_counter).TagName := 'ABSENCE_RECORD';
758 TEL_DATA(l_counter).TagValue := 'ABSENCE_RECORD';
759 l_counter := l_counter + 1;
760
761 TEL_DATA(l_counter).TagName := 'RECORD_THREE';
762 TEL_DATA(l_counter).TagValue :=hr_general.decode_lookup('FI_FORM_LABELS','REC_C');
763 l_counter := l_counter + 1;
764 --fnd_file.put_line(fnd_file.log,' lr_TEL_PER1_DETAILS.FIRST_CODE ==> '||lr_TEL_PER1_DETAILS.FIRST_CODE );
765 TEL_DATA(l_counter).TagName := 'ABS_CODE_FIRST_DATE_PAIR';
766 TEL_DATA(l_counter).TagValue :=lr_TEL_PER1_DETAILS.FIRST_CODE;
767 l_counter := l_counter + 1;
768 --fnd_file.put_line(fnd_file.log,' lr_TEL_PER1_DETAILS.FIRSTDATEPAIR ==> '||lr_TEL_PER1_DETAILS.FIRSTDATEPAIR );
769 TEL_DATA(l_counter).TagName := 'FIRST_DATE_PAIR';
770 TEL_DATA(l_counter).TagValue :=lr_TEL_PER1_DETAILS.FIRSTDATEPAIR;
771 l_counter := l_counter + 1;
772 --fnd_file.put_line(fnd_file.log,' lr_TEL_PER1_DETAILS.ABS_CODE_SECOND_DATE_PAIR ==> '||lr_TEL_PER1_DETAILS.SECOND_CODE );
773 TEL_DATA(l_counter).TagName := 'ABS_CODE_SECOND_DATE_PAIR';
774 TEL_DATA(l_counter).TagValue :=lr_TEL_PER1_DETAILS.SECOND_CODE;
775 l_counter := l_counter + 1;
776 --fnd_file.put_line(fnd_file.log,' lr_TEL_PER1_DETAILS.SECONDDATEPAIR ==> '||lr_TEL_PER1_DETAILS.SECONDDATEPAIR );
777 TEL_DATA(l_counter).TagName := 'SECOND_DATE_PAIR';
778 TEL_DATA(l_counter).TagValue :=lr_TEL_PER1_DETAILS.SECONDDATEPAIR;
779 l_counter := l_counter + 1;
780 --fnd_file.put_line(fnd_file.log,' lr_TEL_PER1_DETAILS.THIRD_CODE ==> '||lr_TEL_PER1_DETAILS.THIRD_CODE );
781 TEL_DATA(l_counter).TagName := 'ABS_CODE_THIRD_DATE_PAIR';
782 TEL_DATA(l_counter).TagValue :=lr_TEL_PER1_DETAILS.THIRD_CODE;
783 l_counter := l_counter + 1;
784 --fnd_file.put_line(fnd_file.log,' lr_TEL_PER1_DETAILS.THIRDDATEPAIR ==> '||lr_TEL_PER1_DETAILS.THIRDDATEPAIR );
785 TEL_DATA(l_counter).TagName := 'THIRD_DATE_PAIR';
786 TEL_DATA(l_counter).TagValue :=lr_TEL_PER1_DETAILS.THIRDDATEPAIR;
787 l_counter := l_counter + 1;
788
789 TEL_DATA(l_counter).TagName := 'ABS_ADDITIONAL_INFORMATION';
790 TEL_DATA(l_counter).TagValue :='1';
791 l_counter := l_counter + 1;
792
793 TEL_DATA(l_counter).TagName := 'ABSENCE_RECORD';
794 TEL_DATA(l_counter).TagValue := 'ABSENCE_RECORD_END';
795 l_counter := l_counter + 1;
796 END IF;
797
798
799 END IF; -- end of Record 4
800
801
802 IF lr_TEL_PERSON_DETAILS.ACTION_INFORMATION21 IS NOT NULL
803 or lr_TEL_PERSON_DETAILS.ACTION_INFORMATION22 IS NOT NULL
804 or lr_TEL_PERSON_DETAILS.ACTION_INFORMATION23 IS NOT NULL
805 THEN
809
806 TEL_DATA(l_counter).TagName := 'TRANSFER_RECORD';
807 TEL_DATA(l_counter).TagValue := 'TRANSFER_RECORD';
808 l_counter := l_counter + 1;
810 TEL_DATA(l_counter).TagName := 'RECORD_FOUR';
811 TEL_DATA(l_counter).TagValue :=hr_general.decode_lookup('FI_FORM_LABELS','REC_D');
812 l_counter := l_counter + 1;
813
814 TEL_DATA(l_counter).TagName := 'NEW_INSURANCE_POLICY_NUMBER';
815 TEL_DATA(l_counter).TagValue :=lr_TEL_PERSON_DETAILS.ACTION_INFORMATION21;
816 l_counter := l_counter + 1;
817
818 TEL_DATA(l_counter).TagName := 'NEW_PENSION_GROUP';
819 TEL_DATA(l_counter).TagValue := lr_TEL_PERSON_DETAILS.ACTION_INFORMATION23;
820 l_counter := l_counter + 1;
821
822 TEL_DATA(l_counter).TagName := 'NEW_DEPARTMENT';
823 TEL_DATA(l_counter).TagValue := lr_TEL_PERSON_DETAILS.ACTION_INFORMATION22;
824 l_counter := l_counter + 1;
825
826 TEL_DATA(l_counter).TagName := 'TRANSFER_RECORD';
827 TEL_DATA(l_counter).TagValue := 'TRANSFER_RECORD_END';
828 l_counter := l_counter + 1;
829 END IF;
830
831 TEL_DATA(l_counter).TagName := 'PERSON';
832 TEL_DATA(l_counter).TagValue := 'PERSON_END';
833 l_counter := l_counter + 1;
834
835 -- Here Update the Person Extra Info EIT
836 IF p_test_run = 'N'
837 THEN
838
839 --fnd_file.put_line(fnd_file.log,' Before Updatign the EIT ==> '||lr_TEL_PERSON_DETAILS.ACTION_INFORMATION30 );
840 -- Person id is avilable in lr_TEL_PERSON_DETAILS.ACTION_INFORMATION30
841 -- PJD
842
843 OPEN CSR_PERSON_EIT(lr_TEL_PERSON_DETAILS.ACTION_INFORMATION30,'Pension Joining Date');
844 FETCH CSR_PERSON_EIT
845 INTO LR_PERSON_EIT;
846
847 IF csr_PERSON_EIT%FOUND
848 THEN
849 L_OVN := lr_PERSON_EIT.object_version_number;
850 --fnd_file.put_line(fnd_file.log,' PJD ' );
851 --fnd_file.put_line(fnd_file.log,' IS REPORTED '||LR_PERSON_EIT.pei_information5 );
852 --fnd_file.put_line(fnd_file.log,' LAST REPORTED DATE '||LR_PERSON_EIT.pei_information2 );
853 --fnd_file.put_line(fnd_file.log,' FIRST CHANGED DATE '||LR_PERSON_EIT.pei_information7 );
854
855 IF ( LR_PERSON_EIT.pei_information5 = 'N'
856 AND
857 NVL(LR_PERSON_EIT.pei_information2,to_date('01/01/0001','DD/MM/YYYY')) <= LR_REPORT_DETAILS.ACTION_INFORMATION9
858 AND
859 LR_PERSON_EIT.pei_information7 <= LR_REPORT_DETAILS.ACTION_INFORMATION9
860 )
861 THEN
862 --fnd_file.put_line(fnd_file.log,' Updating PJD ' );
863 hr_person_extra_info_api.update_person_extra_info
864 (
865 p_person_extra_info_id => lr_PERSON_EIT.person_extra_info_id,
866 p_object_version_number => L_OVN,
867 -- p_pei_information_category => lr_PERSON_EIT.pei_information_category,
868 -- p_pei_information1 => lr_PERSON_EIT.pei_information1,
869 p_pei_information2 => LR_REPORT_DETAILS.ACTION_INFORMATION9,
870 -- p_pei_information3 => lr_PERSON_EIT.pei_information3,
871 p_pei_information4 => lr_TEL_PERSON_DETAILS.ACTION_INFORMATION9,
872 p_pei_information5 => 'Y'
873 -- p_pei_information6 => 'U',
874 -- p_pei_information7 =>FND_DATE.DATE_TO_CANONICAL(LR_REPORT_DETAILS.ACTION_INFORMATION8 )
875 );
876 END IF;
877 END IF;
878 CLOSE CSR_PERSON_EIT;
879 -- PG
880 OPEN CSR_PERSON_EIT(lr_TEL_PERSON_DETAILS.ACTION_INFORMATION30,'Pension Group');
881 FETCH CSR_PERSON_EIT
882 INTO LR_PERSON_EIT;
883 --fnd_file.put_line(fnd_file.log,' PG ' );
884 --fnd_file.put_line(fnd_file.log,' IS REPORTED '||LR_PERSON_EIT.pei_information5 );
885 --fnd_file.put_line(fnd_file.log,' LAST REPORTED DATE '||LR_PERSON_EIT.pei_information2 );
886 --fnd_file.put_line(fnd_file.log,' FIRST CHANGED DATE '||LR_PERSON_EIT.pei_information7 );
887
888 IF csr_PERSON_EIT%FOUND
889 THEN
890 L_OVN := lr_PERSON_EIT.object_version_number;
891 IF ( LR_PERSON_EIT.pei_information5 = 'N'
892 AND
893 NVL(LR_PERSON_EIT.pei_information2,to_date('01/01/0001','DD/MM/YYYY')) <= LR_REPORT_DETAILS.ACTION_INFORMATION9
894 AND
895 LR_PERSON_EIT.pei_information7 <= LR_REPORT_DETAILS.ACTION_INFORMATION9
896 )
897 THEN
898 --fnd_file.put_line(fnd_file.log,' Updating PG ' );
899 hr_person_extra_info_api.update_person_extra_info
900 (
901 p_person_extra_info_id => lr_PERSON_EIT.person_extra_info_id,
902 p_object_version_number => L_OVN,
903 -- p_pei_information_category => lr_PERSON_EIT.pei_information_category,
904 -- p_pei_information1 => lr_PERSON_EIT.pei_information1,
905 p_pei_information2 => LR_REPORT_DETAILS.ACTION_INFORMATION9,
909 -- p_pei_information6 => 'U',
906 -- p_pei_information3 => lr_PERSON_EIT.pei_information3,
907 p_pei_information4 => lr_TEL_PERSON_DETAILS.ACTION_INFORMATION27,
908 p_pei_information5 => 'Y'
910 -- p_pei_information7 =>FND_DATE.DATE_TO_CANONICAL(LR_REPORT_DETAILS.ACTION_INFORMATION8 )
911 );
912 END IF;
913 END IF;
914 CLOSE CSR_PERSON_EIT;
915 -- PT
916 OPEN CSR_PERSON_EIT(lr_TEL_PERSON_DETAILS.ACTION_INFORMATION30,'Pension Types');
917 FETCH CSR_PERSON_EIT
918 INTO LR_PERSON_EIT;
919
920 IF csr_PERSON_EIT%FOUND
921 THEN
922 L_OVN := lr_PERSON_EIT.object_version_number;
923 --fnd_file.put_line(fnd_file.log,' PT ' );
924 --fnd_file.put_line(fnd_file.log,' IS REPORTED '||LR_PERSON_EIT.pei_information5 );
925 --fnd_file.put_line(fnd_file.log,' LAST REPORTED DATE '||LR_PERSON_EIT.pei_information2 );
926 --fnd_file.put_line(fnd_file.log,' FIRST CHANGED DATE '||LR_PERSON_EIT.pei_information7 );
927
928 IF ( LR_PERSON_EIT.pei_information5 = 'N'
929 AND
930 NVL(LR_PERSON_EIT.pei_information2,to_date('01/01/0001','DD/MM/YYYY')) <= LR_REPORT_DETAILS.ACTION_INFORMATION9
931 AND
932 LR_PERSON_EIT.pei_information7 <= LR_REPORT_DETAILS.ACTION_INFORMATION9
933 )
934 THEN
935 --fnd_file.put_line(fnd_file.log,' Updating PT ' );
936 hr_person_extra_info_api.update_person_extra_info
937 (
938 p_person_extra_info_id => lr_PERSON_EIT.person_extra_info_id,
939 p_object_version_number => L_OVN,
940 -- p_pei_information_category => lr_PERSON_EIT.pei_information_category,
941 -- p_pei_information1 => lr_PERSON_EIT.pei_information1,
942 p_pei_information2 => LR_REPORT_DETAILS.ACTION_INFORMATION9,
943 -- p_pei_information3 => lr_PERSON_EIT.pei_information3,
944 p_pei_information4 => lr_TEL_PERSON_DETAILS.ACTION_INFORMATION26,
945 p_pei_information5 => 'Y'
946 -- p_pei_information6 => 'U',
947 -- p_pei_information7 =>FND_DATE.DATE_TO_CANONICAL(LR_REPORT_DETAILS.ACTION_INFORMATION8 )
948 );
949 END IF;
950 END IF;
951 CLOSE CSR_PERSON_EIT;
952
953 -- Pension Insurance Number
954 OPEN CSR_PERSON_EIT(lr_TEL_PERSON_DETAILS.ACTION_INFORMATION30,'Insurance Number');
955 FETCH CSR_PERSON_EIT
956 INTO LR_PERSON_EIT;
957
958 IF csr_PERSON_EIT%FOUND
959 THEN
960 L_OVN := lr_PERSON_EIT.object_version_number;
961 --fnd_file.put_line(fnd_file.log,' PT ' );
962 --fnd_file.put_line(fnd_file.log,' IS REPORTED '||LR_PERSON_EIT.pei_information5 );
963 --fnd_file.put_line(fnd_file.log,' LAST REPORTED DATE '||LR_PERSON_EIT.pei_information2 );
964 --fnd_file.put_line(fnd_file.log,' FIRST CHANGED DATE '||LR_PERSON_EIT.pei_information7 );
965
966 IF ( LR_PERSON_EIT.pei_information5 = 'N'
967 AND
968 NVL(LR_PERSON_EIT.pei_information2,to_date('01/01/0001','DD/MM/YYYY')) <= LR_REPORT_DETAILS.ACTION_INFORMATION9
969 AND
970 LR_PERSON_EIT.pei_information7 <= LR_REPORT_DETAILS.ACTION_INFORMATION9
971 )
972 THEN
973 --fnd_file.put_line(fnd_file.log,' Updating PT ' );
974 hr_person_extra_info_api.update_person_extra_info
975 (
976 p_person_extra_info_id => lr_PERSON_EIT.person_extra_info_id,
977 p_object_version_number => L_OVN,
978 -- p_pei_information_category => lr_PERSON_EIT.pei_information_category,
979 -- p_pei_information1 => lr_PERSON_EIT.pei_information1,
980 p_pei_information2 => LR_REPORT_DETAILS.ACTION_INFORMATION9,
981 -- p_pei_information3 => lr_PERSON_EIT.pei_information3,
982 p_pei_information4 => lr_TEL_PERSON_DETAILS.ACTION_INFORMATION21,
983 p_pei_information5 => 'Y'
984 -- p_pei_information6 => 'U',
985 -- p_pei_information7 =>FND_DATE.DATE_TO_CANONICAL(LR_REPORT_DETAILS.ACTION_INFORMATION8 )
986 );
987 END IF;
988 END IF;
989 CLOSE CSR_PERSON_EIT;
990
991 -- Local Unit
992 OPEN CSR_PERSON_EIT(lr_TEL_PERSON_DETAILS.ACTION_INFORMATION30,'Local Unit');
993 FETCH CSR_PERSON_EIT
994 INTO LR_PERSON_EIT;
995 --fnd_file.put_line(fnd_file.log,' LU ' );
996 --fnd_file.put_line(fnd_file.log,' IS REPORTED '||LR_PERSON_EIT.pei_information5 );
997 --fnd_file.put_line(fnd_file.log,' LAST REPORTED DATE '||LR_PERSON_EIT.pei_information2 );
998 --fnd_file.put_line(fnd_file.log,' FIRST CHANGED DATE '||LR_PERSON_EIT.pei_information7 );
999
1000 IF csr_PERSON_EIT%FOUND
1001 THEN
1002 L_OVN := lr_PERSON_EIT.object_version_number;
1003 IF ( LR_PERSON_EIT.pei_information5 = 'N'
1007 LR_PERSON_EIT.pei_information7 <= LR_REPORT_DETAILS.ACTION_INFORMATION9
1004 AND
1005 NVL(LR_PERSON_EIT.pei_information2,to_date('01/01/0001','DD/MM/YYYY')) <= LR_REPORT_DETAILS.ACTION_INFORMATION9
1006 AND
1008 )
1009 THEN
1010 --fnd_file.put_line(fnd_file.log,' Updating LOCAL UNIT ' );
1011 hr_person_extra_info_api.update_person_extra_info
1012 (
1013 p_person_extra_info_id => lr_PERSON_EIT.person_extra_info_id,
1014 p_object_version_number => L_OVN,
1015 -- p_pei_information_category => lr_PERSON_EIT.pei_information_category,
1016 -- p_pei_information1 => lr_PERSON_EIT.pei_information1,
1017 p_pei_information2 => LR_REPORT_DETAILS.ACTION_INFORMATION9,
1018 -- p_pei_information3 => lr_PERSON_EIT.pei_information3,
1019 p_pei_information4 => lr_TEL_PERSON_DETAILS.ACTION_INFORMATION28,
1020 p_pei_information5 => 'Y'
1021 -- p_pei_information6 => 'U',
1022 -- p_pei_information7 =>FND_DATE.DATE_TO_CANONICAL(LR_REPORT_DETAILS.ACTION_INFORMATION8 )
1023 );
1024 END IF;
1025 END IF;
1026
1027 CLOSE CSR_PERSON_EIT;
1028
1029 END IF;
1030
1031 -- Here Update the Person Extra Info EIT
1032 END IF; -- for record two found cursor
1033 --fnd_file.put_line(fnd_file.log,'END OF A PERSON ' );
1034 --fnd_file.put_line(fnd_file.log,'+++++++++++++++++++++++++END+++++++++++++++++++++++++++++++++++++++++++++++' );
1035
1036 END LOOP;
1037 hr_utility.set_location('After populating pl/sql table',30);
1038 commit;
1039
1040 WritetoCLOB (p_xml );
1041
1042
1043 END GET_DATA;
1044
1045 -----------------------------------------------------------------------------------------------------------------
1046 PROCEDURE WritetoCLOB(p_xfdf_clob out nocopy CLOB) IS
1047 l_xfdf_string clob;
1048 l_str1 varchar2(1000);
1049 l_str2 varchar2(20);
1050 l_str3 varchar2(20);
1051 l_str4 varchar2(20);
1052 l_str5 varchar2(20);
1053 l_str6 varchar2(30);
1054 l_str7 varchar2(1000);
1055 l_str8 varchar2(240);
1056 l_str9 varchar2(240);
1057 l_str10 varchar2(20);
1058 l_str11 varchar2(20);
1059
1060 current_index number;
1061
1062 l_IANA_charset VARCHAR2 (50);
1063 BEGIN
1064 l_IANA_charset :=hr_fi_utility.get_IANA_charset ;
1065 --hr_utility.trace_on(NULL,'SR1');
1066 l_str1 := '<?xml version="1.0" encoding="'||l_IANA_charset||'"?> <ROOT>';
1067 l_str2 := '<';
1068 l_str3 := '>';
1069 l_str4 := '</';
1070 l_str5 := '>';
1071 l_str6 := '</ROOT>';
1072 l_str7 := '<?xml version="1.0" encoding="'||l_IANA_charset||'"?> <ROOT></ROOT>';
1073 l_str10 := '<PERSON>';
1074 l_str11 := '</PERSON>';
1075
1076
1077 dbms_lob.createtemporary(l_xfdf_string,FALSE,DBMS_LOB.CALL);
1078 dbms_lob.open(l_xfdf_string,dbms_lob.lob_readwrite);
1079
1080 current_index := 0;
1081
1082 if TEL_DATA.count > 0 then
1083
1084
1085
1086 dbms_lob.writeAppend( l_xfdf_string, length(l_str1), l_str1 );
1087
1088 FOR table_counter IN TEL_DATA.FIRST .. TEL_DATA.LAST LOOP
1089 l_str8 := TEL_DATA(table_counter).TagName;
1090 l_str9 := TEL_DATA(table_counter).TagValue;
1091
1092 ----fnd_file.put_line(fnd_file.log,'outside str8 ==> '||l_str8 );
1093 ----fnd_file.put_line(fnd_file.log,'outside str9 ==> '||l_str9 );
1094
1095 IF l_str9 IN ('PERSON','PERSON_END',
1096 'TIME_RECORD' ,'TIME_RECORD_END',
1097 'EMP_RECORD','EMP_RECORD_END',
1098 'ABSENCE_RECORD','ABSENCE_RECORD_END',
1099 'TRANSFER_RECORD','TRANSFER_RECORD_END',
1100 'TIME_RECORD_TYPE','TIME_RECORD_TYPE_END'
1101 )
1102 THEN
1103
1104 IF l_str9 IN ('PERSON','TIME_RECORD' ,'EMP_RECORD','ABSENCE_RECORD','TRANSFER_RECORD',
1105 'TIME_RECORD_TYPE')
1106 THEN
1107 /* IF l_str9 = 'EMP_RECORD'
1108 THEN
1109 dbms_lob.writeAppend(l_xfdf_string, length(l_str10), l_str10);
1110 END IF;
1111 */
1112 --fnd_file.put_line(fnd_file.log,'IF l_str8 ==> '||l_str8 );
1113 --fnd_file.put_line(fnd_file.log,'IF l_str9 ==> '||l_str9 );
1114
1115
1116 dbms_lob.writeAppend(l_xfdf_string, length(l_str2), l_str2);
1117 dbms_lob.writeAppend(l_xfdf_string, length(l_str8), l_str8);
1118 dbms_lob.writeAppend(l_xfdf_string, length(l_str3), l_str3);
1119
1120
1121 ELSE
1122 --fnd_file.put_line(fnd_file.log,'ELSE l_str8 ==> '||l_str8 );
1123 --fnd_file.put_line(fnd_file.log,'ELSE l_str9 ==> '||l_str9 );
1124 dbms_lob.writeAppend(l_xfdf_string, length(l_str4), l_str4);
1125 dbms_lob.writeAppend(l_xfdf_string, length(l_str8), l_str8);
1126 dbms_lob.writeAppend(l_xfdf_string, length(l_str5), l_str5);
1127 /*
1128 IF l_str9 = 'ADDITIONAL_EMPLOYMENT_END' THEN
1132 END IF;
1129 -- TO start a new person record
1130 dbms_lob.writeAppend(l_xfdf_string, length(l_str11), l_str11);
1131
1133 */
1134 END IF;
1135
1136 ELSE
1137
1138 if l_str9 is not null then
1139
1140 dbms_lob.writeAppend(l_xfdf_string, length(l_str2), l_str2);
1141 dbms_lob.writeAppend(l_xfdf_string, length(l_str8), l_str8);
1142 dbms_lob.writeAppend(l_xfdf_string, length(l_str3), l_str3);
1143 dbms_lob.writeAppend(l_xfdf_string, length(l_str9), l_str9);
1144 dbms_lob.writeAppend(l_xfdf_string, length(l_str4), l_str4);
1145 dbms_lob.writeAppend(l_xfdf_string, length(l_str8), l_str8);
1146 dbms_lob.writeAppend(l_xfdf_string, length(l_str5), l_str5);
1147 else
1148
1149 dbms_lob.writeAppend(l_xfdf_string, length(l_str2), l_str2);
1150 dbms_lob.writeAppend(l_xfdf_string, length(l_str8), l_str8);
1151 dbms_lob.writeAppend(l_xfdf_string, length(l_str3), l_str3);
1152 dbms_lob.writeAppend(l_xfdf_string, length(l_str4), l_str4);
1153 dbms_lob.writeAppend(l_xfdf_string, length(l_str8), l_str8);
1154 dbms_lob.writeAppend(l_xfdf_string, length(l_str5), l_str5);
1155
1156 end if;
1157
1158 END IF;
1159
1160
1161
1162 END LOOP;
1163
1164
1165 --dbms_lob.writeAppend(l_xfdf_string, length(l_str11), l_str11 );
1166 dbms_lob.writeAppend(l_xfdf_string, length(l_str6), l_str6 );
1167 else
1168 dbms_lob.writeAppend(l_xfdf_string, length(l_str7), l_str7 );
1169 end if;
1170
1171 --DBMS_LOB.CREATETEMPORARY(p_xfdf_blob,TRUE);
1172
1173
1174 p_xfdf_clob := l_xfdf_string;
1175
1176
1177 --hr_utility.trace(l_xfdf_string);
1178
1179 --clob_to_blob(l_xfdf_string,p_xfdf_blob);
1180
1181
1182 hr_utility.set_location('Finished Procedure Write to CLOB ,Before clob to blob ',110);
1183
1184 --return p_xfdf_blob;
1185 EXCEPTION
1186 WHEN OTHERS then
1187 HR_UTILITY.TRACE('sqleerm ' || SQLERRM);
1188 HR_UTILITY.RAISE_ERROR;
1189 END WritetoCLOB;
1190 -------------------------------------------------------------------------------------------------------------------------
1191
1192 END PAY_FI_TELR;