[Home] [Help]
PACKAGE BODY: APPS.PAY_DK_EINR
Source
1 PACKAGE BODY PAY_DK_EINR AS
2 /* $Header: pydkeinr.pkb 120.1 2008/03/05 13:01:32 rmurahar noship $ */
3
4 PROCEDURE GET_DATA (
5 p_business_group_id IN NUMBER,
6 p_payroll_action_id IN VARCHAR2 ,
7 p_template_name IN VARCHAR2,
8 p_xml OUT NOCOPY CLOB
9 )
10 IS
11
12 /* Start of declaration*/
13 -- Variables needed for the report
14 l_counter number := 0;
15 l_payroll_action_id PAY_ACTION_INFORMATION. ACTION_INFORMATION1%TYPE;
16 /* End of declaration*/
17
18 /* Cursors */
19 /* Cursor to fetch data related to Record 1000*/
20 CURSOR csr_1000 (p_payroll_action_id NUMBER) IS
21 SELECT pai.*
22 FROM pay_action_information pai
23 WHERE pai.action_context_id= p_payroll_action_id
24 AND pai.action_context_type= 'PA'
25 AND pai.action_information_category = 'EMEA REPORT INFORMATION'
26 AND pai.action_information1 = 'PYDKEINA'
27 AND pai.action_information3 ='1000';
28 -- ORDER BY pai.action_information2;
29
30 /* Cursor to fetch data related to Record 2001*/
31 CURSOR csr_2001 (p_payroll_action_id NUMBER) IS
32 SELECT pai.*
33 FROM pay_action_information pai
34 WHERE pai.action_context_id= p_payroll_action_id
35 AND pai.action_context_type= 'PA'
36 AND pai.action_information_category = 'EMEA REPORT INFORMATION'
37 AND pai.action_information1 = 'PYDKEINA'
38 AND pai.action_information3 ='2001';
39 -- AND action_information29 ='2'
40 -- AND pai.action_context_id = p_action_context_id
41 -- ORDER BY pai.action_information2;
42
43 rg_csr_2001 csr_2001%rowtype;
44
45 /* Cursor to fetch data related to Record 2101*/
46 CURSOR csr_2101(p_payroll_action_id NUMBER) IS
47 SELECT pai.*
48 FROM pay_action_information pai , pay_assignment_actions paa
49 WHERE paa.payroll_action_id = p_payroll_action_id
50 AND pai.action_context_id= paa.assignment_action_id
51 AND pai.action_context_type= 'AAP'
52 AND pai.action_information_category = 'EMEA REPORT INFORMATION'
53 AND pai.action_information1 = 'PYDKEINA'
54 AND pai.action_information3 ='2101'
55 order by pai.action_information30;
56
57 rg_csr_2101 csr_2101%rowtype;
58
59 /* Cursor to fetch data related to Record 5000*/
60 CURSOR csr_5000(p_payroll_action_id NUMBER) IS
61 SELECT pai.*
62 FROM pay_action_information pai
63 WHERE pai.action_context_id = p_payroll_action_id
64 AND pai.action_context_type= 'PA'
65 AND pai.action_information_category = 'EMEA REPORT INFORMATION'
66 AND pai.action_information1 = 'PYDKEINA'
67 AND pai.action_information3 = '5000';
68
69 rg_csr_5000 csr_5000%rowtype;
70
71 /* Cursor to fetch data related to Record 6000*/
72 CURSOR csr_6000(p_payroll_action_id NUMBER) IS
73 SELECT pai.*
74 FROM pay_action_information pai, pay_assignment_actions paa
75 WHERE paa.payroll_action_id= p_payroll_action_id
76 AND pai.action_context_id= paa.assignment_action_id
77 AND pai.action_context_type= 'AAP'
78 AND pai.action_information_category = 'EMEA REPORT INFORMATION'
79 AND pai.action_information1 = 'PYDKEINA'
80 AND pai.action_information3 = '6000'
81 order by pai.action_information30;
82
83 rg_csr_6000 csr_6000%rowtype;
84
85 /* Cursor to fetch data related to Record 6001 */
86 CURSOR csr_6001(p_payroll_action_id NUMBER, p_assignment_id NUMBER) IS
87 SELECT pai.*
88 FROM pay_action_information pai, pay_assignment_actions paa
89 WHERE paa.payroll_action_id= p_payroll_action_id
90 AND pai.action_context_id= paa.assignment_action_id
91 AND pai.action_context_type= 'AAP'
92 AND pai.action_information_category = 'EMEA REPORT INFORMATION'
93 AND pai.action_information1 = 'PYDKEINA'
94 AND pai.action_information3 = '6001'
95 AND pai.action_information5 = p_assignment_id
96 ORDER BY pai.action_information30;
97
98 rg_csr_6001 csr_6001%rowtype;
99
100 /* Cursor to fetch data related to Record 6002 */
101 CURSOR csr_6002(p_payroll_action_id NUMBER, p_assignment_id NUMBER) IS
102 SELECT pai.*
103 FROM pay_action_information pai, pay_assignment_actions paa
104 WHERE paa.payroll_action_id= p_payroll_action_id
105 AND pai.action_context_id= paa.assignment_action_id
106 AND pai.action_context_type= 'AAP'
107 AND pai.action_information_category = 'EMEA REPORT INFORMATION'
108 AND pai.action_information1 = 'PYDKEINA'
109 AND pai.action_information3 = '6002'
110 AND pai.action_information5 = p_assignment_id;
111
112 rg_csr_6002 csr_6002%rowtype;
113
114 /* Cursor to fetch data related to Record 6004 */
115 CURSOR csr_6004(p_payroll_action_id NUMBER, p_assignment_id NUMBER) IS
116 SELECT pai.*
117 FROM pay_action_information pai, pay_assignment_actions paa
118 WHERE paa.payroll_action_id= p_payroll_action_id
119 AND pai.action_context_id= paa.assignment_action_id
120 AND pai.action_context_type= 'AAP'
121 AND pai.action_information_category = 'EMEA REPORT INFORMATION'
122 AND pai.action_information1 = 'PYDKEINA'
123 AND pai.action_information3 = '6004'
124 AND pai.action_information5 = p_assignment_id;
125
126 rg_csr_6004 csr_6004%rowtype;
127
128 /* Cursor to fetch data related to Record 6005 */
129 CURSOR csr_6005(p_payroll_action_id NUMBER, p_assignment_id NUMBER) IS
130 SELECT pai.*
131 FROM pay_action_information pai, pay_assignment_actions paa
132 WHERE paa.payroll_action_id= p_payroll_action_id
133 AND pai.action_context_id= paa.assignment_action_id
134 AND pai.action_context_type= 'AAP'
135 AND pai.action_information_category = 'EMEA REPORT INFORMATION'
136 AND pai.action_information1 = 'PYDKEINA'
137 AND pai.action_information3 = '6005'
138 AND pai.action_information5 = p_assignment_id;
139
140 rg_csr_6005 csr_6005%rowtype;
141
142 /* Cursor to fetch data related to Record 9999 */
143 CURSOR csr_9999(p_payroll_action_id NUMBER) IS
144 SELECT pai.*
145 FROM pay_action_information pai
146 WHERE pai.action_context_id = p_payroll_action_id
147 AND pai.action_context_type= 'PA'
148 AND pai.action_information_category = 'EMEA REPORT INFORMATION'
149 AND pai.action_information1 = 'PYDKEINA'
150 AND pai.action_information3 = '9999';
151
152 rg_csr_9999 csr_9999%rowtype;
153
154 /* End of Cursors */
155 BEGIN
156
157 --fnd_file.put_line(fnd_file.log,'1');
158 IF p_payroll_action_id IS NULL THEN
159
160 BEGIN
161
162 SELECT payroll_action_id
163 INTO l_payroll_action_id
164 FROM pay_payroll_actions ppa,
165 fnd_conc_req_summary_v fcrs,
166 fnd_conc_req_summary_v fcrs1
167 WHERE fcrs.request_id = FND_GLOBAL.CONC_REQUEST_ID
168 AND fcrs.priority_request_id = fcrs1.priority_request_id
169 AND ppa.request_id between fcrs1.request_id and fcrs.request_id
170 AND ppa.request_id = fcrs1.request_id;
171
172 EXCEPTION
173 WHEN others THEN
174 NULL;
175 END ;
176 --fnd_file.put_line(fnd_file.log,'2');
177 ELSE
178
179 l_payroll_action_id :=p_payroll_action_id;
180 --fnd_file.put_line(fnd_file.log,'3');
181 END IF;
182
183 hr_utility.set_location('Entered Procedure GETDATA',10);
184
185 /* Pick up the data related to Record 1000*/
186 FOR rg_csr_1000 IN csr_1000 (l_payroll_action_id)
187 LOOP
188 --fnd_file.put_line(fnd_file.log,'4');
189
190 gtagdata(l_counter).TagName := 'REC_1000';
191 gtagdata(l_counter).TagValue := 'REC_1000';
192 l_counter := l_counter + 1;
193
194 /* line num */
195 gtagdata(l_counter).TagName := 'RT1000_01';
196 gtagdata(l_counter).TagValue := rg_csr_1000.action_information30;
197 l_counter := l_counter + 1;
198
199 /*Rec num */
200 gtagdata(l_counter).TagName := 'RT1000_02';
201 gtagdata(l_counter).TagValue := rg_csr_1000.action_information3;
202 l_counter := l_counter + 1;
203
204 /*Date sent*/
205 gtagdata(l_counter).TagName := 'RT1000_03';
206 gtagdata(l_counter).TagValue := rg_csr_1000.action_information4;
207 l_counter := l_counter + 1;
208
209 /*Time sent*/
210 gtagdata(l_counter).TagName := 'RT1000_04';
211 gtagdata(l_counter).TagValue := rg_csr_1000.action_information5;
212 l_counter := l_counter + 1;
213
214 /*SE number*/
215 gtagdata(l_counter).TagName := 'RT1000_05';
216 gtagdata(l_counter).TagValue := lpad(rg_csr_1000.action_information6,8,'0');
217 l_counter := l_counter + 1;
218
219 /*CVR number*/
220 gtagdata(l_counter).TagName := 'RT1000_06';
221 gtagdata(l_counter).TagValue := rg_csr_1000.action_information7;
222 l_counter := l_counter + 1;
223
224 /*Sender type*/
225 gtagdata(l_counter).TagName := 'RT1000_07';
226 gtagdata(l_counter).TagValue := rg_csr_1000.action_information8;
227 l_counter := l_counter + 1;
228
229 /*Filler*/
230 gtagdata(l_counter).TagName := 'RT1000_08';
231 gtagdata(l_counter).TagValue := lpad('0',5,'0');
232 l_counter := l_counter + 1;
233
234 /*Report Method name*/
235 gtagdata(l_counter).TagName := 'RT1000_09';
236 gtagdata(l_counter).TagValue := rg_csr_1000.action_information9;
237 l_counter := l_counter + 1;
238
239 /*IT System*/
240 gtagdata(l_counter).TagName := 'RT1000_10';
241 gtagdata(l_counter).TagValue := rg_csr_1000.action_information10;
242 l_counter := l_counter + 1;
243
244 /* IT System Version */
245 gtagdata(l_counter).TagName := 'RT1000_11';
246 gtagdata(l_counter).TagValue := rg_csr_1000.action_information11;
247 l_counter := l_counter + 1;
248
249 /*Main sender ID*/
250 gtagdata(l_counter).TagName := 'RT1000_12';
251 gtagdata(l_counter).TagValue := rg_csr_1000.action_information12;
252 l_counter := l_counter + 1;
253
254 /*E-Income version*/
255 gtagdata(l_counter).TagName := 'RT1000_13';
256 gtagdata(l_counter).TagValue := rg_csr_1000.action_information13;
257 l_counter := l_counter + 1;
258
259 /*Test Marking*/
260 gtagdata(l_counter).TagName := 'RT1000_14';
261 gtagdata(l_counter).TagValue := rg_csr_1000.action_information14;
262 l_counter := l_counter + 1;
263
264 /*Filler */
265 gtagdata(l_counter).TagName := 'RT1000_15';
266 gtagdata(l_counter).TagValue := ''; -- 16 spaces
267 l_counter := l_counter + 1;
268
269 /*Filler */
270 gtagdata(l_counter).TagName := 'RT1000_16';
271 gtagdata(l_counter).TagValue := ''; -- 16 spaces
272 l_counter := l_counter + 1;
273
274 /*Indication of E-Income*/
275 gtagdata(l_counter).TagName := 'RT1000_17';
276 gtagdata(l_counter).TagValue := rg_csr_1000.action_information15;
277 l_counter := l_counter + 1;
278
279
280 -- Record 2001
281 OPEN csr_2001(l_payroll_action_id);
282 FETCH csr_2001 INTO rg_csr_2001;
283 CLOSE csr_2001;
284
285 --fnd_file.put_line(fnd_file.log,'5');
286
287 gtagdata(l_counter).TagName := 'REC_2001';
288 gtagdata(l_counter).TagValue := 'REC_2001';
289 l_counter := l_counter + 1;
290
291 /* line num */
292 gtagdata(l_counter).TagName := 'RT2001_01';
293 gtagdata(l_counter).TagValue := rg_csr_2001.action_information30;
294 l_counter := l_counter + 1;
295
296 /*Rec num */
297 gtagdata(l_counter).TagName := 'RT2001_02';
298 gtagdata(l_counter).TagValue := rg_csr_2001.action_information3;
299 l_counter := l_counter + 1;
300
301 /*Filler*/
302 gtagdata(l_counter).TagName := 'RT2001_03';
303 gtagdata(l_counter).TagValue := ''; --16 spaces
304 l_counter := l_counter + 1;
305
306 /*Company */
307 gtagdata(l_counter).TagName := 'RT2001_04';
311 /*Termination of company*/
308 gtagdata(l_counter).TagValue := rg_csr_2001.action_information4;
309 l_counter := l_counter + 1;
310
312 gtagdata(l_counter).TagName := 'RT2001_05';
313 gtagdata(l_counter).TagValue := rg_csr_2001.action_information5;
314 l_counter := l_counter + 1;
315
316 /*Currency */
317 gtagdata(l_counter).TagName := 'RT2001_06';
318 gtagdata(l_counter).TagValue := rg_csr_2001.action_information6;
319 l_counter := l_counter + 1;
320
321 fnd_file.put_line(fnd_file.log,'Company Terminating value : '||rg_csr_2001.action_information5);
322 if(rg_csr_2001.action_information5 IS NULL) then
323 -- Record 2101
324 FOR rg_csr_2101 IN csr_2101(l_payroll_action_id)
325 LOOP
326
327 --fnd_file.put_line(fnd_file.log,'6');
328
329 gtagdata(l_counter).TagName := 'REC_2101';
330 gtagdata(l_counter).TagValue := 'REC_2101';
331 l_counter := l_counter + 1;
332
333 /* line num */
334 gtagdata(l_counter).TagName := 'RT2101_01';
335 gtagdata(l_counter).TagValue := rg_csr_2101.action_information30;
336 l_counter := l_counter + 1;
337
338 /*Rec num */
339 gtagdata(l_counter).TagName := 'RT2101_02';
340 gtagdata(l_counter).TagValue := rg_csr_2101.action_information3;
341 l_counter := l_counter + 1;
342
343 /*CPR number */
344 gtagdata(l_counter).TagName := 'RT2101_03';
345 gtagdata(l_counter).TagValue := rg_csr_2101.action_information6;
346 l_counter := l_counter + 1;
347
348 /*Numeric Filler*/
349 gtagdata(l_counter).TagName := 'RT2101_04';
350 gtagdata(l_counter).TagValue := lpad('0',8,'0'); -- 8 zeros
351 l_counter := l_counter + 1;
352
353 /*Filler*/
354 gtagdata(l_counter).TagName := 'RT2101_05';
355 gtagdata(l_counter).TagValue := ''; -- 15 spaces
356 l_counter := l_counter + 1;
357
358 /*Hire date */
359 gtagdata(l_counter).TagName := 'RT2101_06';
360 gtagdata(l_counter).TagValue := rg_csr_2101.action_information7;
361 l_counter := l_counter + 1;
362
363 /*Termination Date*/
364 gtagdata(l_counter).TagName := 'RT2101_07';
365 if(rg_csr_2101.action_information8 IS NULL) then
366 gtagdata(l_counter).TagValue := lpad('0',8,'0'); -- 8 zeros
367 else
368 gtagdata(l_counter).TagValue := rg_csr_2101.action_information8;
369 end if;
370 l_counter := l_counter + 1;
371
372 /*Numeric Filler*/
373 gtagdata(l_counter).TagName := 'RT2101_08';
374 gtagdata(l_counter).TagValue := lpad('0',5,'0'); -- 5 zeros
375 l_counter := l_counter + 1;
376
377 /*Numeric Filler*/
378 gtagdata(l_counter).TagName := 'RT2101_09';
379 gtagdata(l_counter).TagValue := lpad('0',5,'0'); -- 5 zeros
380 l_counter := l_counter + 1;
381
382 /*Numeric Filler*/
383 gtagdata(l_counter).TagName := 'RT2101_10';
384 gtagdata(l_counter).TagValue := lpad('0',4,'0'); -- 4 zeros
385 l_counter := l_counter + 1;
386
387 /*Numeric Filler */
388 gtagdata(l_counter).TagName := 'RT2101_11';
389 gtagdata(l_counter).TagValue := lpad('0',10,'0'); -- 10 zeros
390 l_counter := l_counter + 1;
391
392 /*Tax Card type*/
393 gtagdata(l_counter).TagName := 'RT2101_12';
394 gtagdata(l_counter).TagValue := rg_csr_2101.action_information9;
395 l_counter := l_counter + 1;
396
397 /*Valid from */
398 gtagdata(l_counter).TagName := 'RT2101_13';
399 gtagdata(l_counter).TagValue := rg_csr_2101.action_information10;
400 l_counter := l_counter + 1;
401
402 /*emp num - filler*/
403 gtagdata(l_counter).TagName := 'RT2101_14';
404 gtagdata(l_counter).TagValue := ''; -- 50 spaces
405 l_counter := l_counter + 1;
406
407 /*Rekv_taxcard */
408 gtagdata(l_counter).TagName := 'RT2101_15';
409 gtagdata(l_counter).TagValue := ''; -- 1 space
410 l_counter := l_counter + 1;
411
412 gtagdata(l_counter).TagName := 'REC_2101';
413 gtagdata(l_counter).TagValue := 'REC_2101_END';
414 l_counter := l_counter + 1;
415
416
417 END LOOP;
418
419 -- Record 5000
420 OPEN csr_5000(l_payroll_action_id);
421 FETCH csr_5000 INTO rg_csr_5000;
422 CLOSE csr_5000;
423 gtagdata(l_counter).TagName := 'REC_5000';
424 gtagdata(l_counter).TagValue := 'REC_5000';
425 l_counter := l_counter + 1;
426
427 --fnd_file.put_line(fnd_file.log,'7');
428
429 /* line num */
430 gtagdata(l_counter).TagName := 'RT5000_01';
431 gtagdata(l_counter).TagValue := rg_csr_5000.action_information30;
432 l_counter := l_counter + 1;
433
434 /*Rec num */
435 gtagdata(l_counter).TagName := 'RT5000_02';
436 gtagdata(l_counter).TagValue := rg_csr_5000.action_information3;
437 l_counter := l_counter + 1;
438
439 gtagdata(l_counter).TagName := 'RT5000_03';
440 gtagdata(l_counter).TagValue := ''; -- 1 space
441 l_counter := l_counter + 1;
442
443 gtagdata(l_counter).TagName := 'RT5000_04';
444 gtagdata(l_counter).TagValue := ''; -- 16 spaces
445 l_counter := l_counter + 1;
446
447 gtagdata(l_counter).TagName := 'RT5000_05';
448 gtagdata(l_counter).TagValue := ''; -- 16 spaces
449 l_counter := l_counter + 1;
450
451 /*Pay period start date*/
452 gtagdata(l_counter).TagName := 'RT5000_06';
453 gtagdata(l_counter).TagValue := rg_csr_5000.action_information4;
454 l_counter := l_counter + 1;
455
456 /*period end date*/
457 gtagdata(l_counter).TagName := 'RT5000_07';
458 gtagdata(l_counter).TagValue := rg_csr_5000.action_information5;
459 l_counter := l_counter + 1;
460
464 l_counter := l_counter + 1;
461 /*Disposal date*/
462 gtagdata(l_counter).TagName := 'RT5000_08';
463 gtagdata(l_counter).TagValue := rg_csr_5000.action_information6;
465
466 gtagdata(l_counter).TagName := 'RT5000_09';
467 gtagdata(l_counter).TagValue := ''; -- 1 space
468 l_counter := l_counter + 1;
469
470 /*Payment*/
471 gtagdata(l_counter).TagName := 'RT5000_10';
472 gtagdata(l_counter).TagValue := rg_csr_5000.action_information7;
473 l_counter := l_counter + 1;
474
475 /*Greenland code*/
476 gtagdata(l_counter).TagName := 'RT5000_11';
477 gtagdata(l_counter).TagValue := rg_csr_5000.action_information8;
478 l_counter := l_counter + 1;
479
480 /*Employment code*/
481 gtagdata(l_counter).TagName := 'RT5000_12';
482 gtagdata(l_counter).TagValue := rg_csr_5000.action_information9;
483 l_counter := l_counter + 1;
484
485 gtagdata(l_counter).TagName := 'REC_5000';
486 gtagdata(l_counter).TagValue := 'REC_5000_END';
487 l_counter := l_counter + 1;
488
489
490 -- record 6000
491 FOR rg_csr_6000 IN csr_6000(l_payroll_action_id)
492 LOOP
493
494 --fnd_file.put_line(fnd_file.log,'8');
495
496 gtagdata(l_counter).TagName := 'REC_6000';
497 gtagdata(l_counter).TagValue := 'REC_6000';
498 l_counter := l_counter + 1;
499
500 /* line num */
501 gtagdata(l_counter).TagName := 'RT6000_01';
502 gtagdata(l_counter).TagValue := rg_csr_6000.action_information30;
503 l_counter := l_counter + 1;
504
505 /*Rec num */
506 gtagdata(l_counter).TagName := 'RT6000_02';
507 gtagdata(l_counter).TagValue := rg_csr_6000.action_information3;
508 l_counter := l_counter + 1;
509
510 -- CVR num
511 gtagdata(l_counter).TagName := 'RT6000_03';
512 gtagdata(l_counter).TagValue := rg_csr_6000.action_information6;
513 l_counter := l_counter + 1;
514
515 -- CPR
516 gtagdata(l_counter).TagName := 'RT6000_04';
517 gtagdata(l_counter).TagValue := rg_csr_6000.action_information7;
518 l_counter := l_counter + 1;
519
520 -- Numeric Filler
521 gtagdata(l_counter).TagName := 'RT6000_05';
522 gtagdata(l_counter).TagValue := lpad('0',8,'0'); -- 8 zeros
523 l_counter := l_counter + 1;
524
525 /*Assignment num*/
526 gtagdata(l_counter).TagName := 'RT6000_06';
527 gtagdata(l_counter).TagValue := rg_csr_6000.action_information8;
528 l_counter := l_counter + 1;
529
530 gtagdata(l_counter).TagName := 'RT6000_07';
531 gtagdata(l_counter).TagValue := ''; -- 1 space
532 l_counter := l_counter + 1;
533
534 gtagdata(l_counter).TagName := 'RT6000_08';
535 gtagdata(l_counter).TagValue := lpad('0',3,'0'); -- 3 zeros
536 l_counter := l_counter + 1;
537
538 gtagdata(l_counter).TagName := 'RT6000_09';
539 gtagdata(l_counter).TagValue := ''; -- 25 spaces
540 l_counter := l_counter + 1;
541
542 /*Code 68*/
543 gtagdata(l_counter).TagName := 'RT6000_10';
544 gtagdata(l_counter).TagValue := rg_csr_6000.action_information9;
545 l_counter := l_counter + 1;
546
547 /*PU code*/
548 gtagdata(l_counter).TagName := 'RT6000_11';
549 gtagdata(l_counter).TagValue := rg_csr_6000.action_information10;
550 l_counter := l_counter + 1;
551
552 -- record 6001
553 FOR rg_csr_6001 IN csr_6001(l_payroll_action_id, rg_csr_6000.action_information5)
554 LOOP
555
556 --fnd_file.put_line(fnd_file.log,'9');
557
558 gtagdata(l_counter).TagName := 'REC_6001';
559 gtagdata(l_counter).TagValue := 'REC_6001';
560 l_counter := l_counter + 1;
561
562 /* line num */
563 gtagdata(l_counter).TagName := 'RT6001_01';
564 gtagdata(l_counter).TagValue := rg_csr_6001.action_information30;
565 l_counter := l_counter + 1;
566
567 /*Rec num */
568 gtagdata(l_counter).TagName := 'RT6001_02';
569 gtagdata(l_counter).TagValue := rg_csr_6001.action_information3;
570 l_counter := l_counter + 1;
571
572 -- Field num
573 gtagdata(l_counter).TagName := 'RT6001_03';
574 gtagdata(l_counter).TagValue := rg_csr_6001.action_information6;
575 l_counter := l_counter + 1;
576
577 -- Amount
578 gtagdata(l_counter).TagName := 'RT6001_04';
579 gtagdata(l_counter).TagValue := rg_csr_6001.action_information7;
580 l_counter := l_counter + 1;
581
582 -- Sign
583 gtagdata(l_counter).TagName := 'RT6001_05';
584 gtagdata(l_counter).TagValue := rg_csr_6001.action_information8;
585 l_counter := l_counter + 1;
586
587 gtagdata(l_counter).TagName := 'REC_6001';
588 gtagdata(l_counter).TagValue := 'REC_6001_END';
589 l_counter := l_counter + 1;
590 END LOOP;
591
592 -- Record 6002
593 OPEN csr_6002(l_payroll_action_id, rg_csr_6000.action_information5);
594 FETCH csr_6002 INTO rg_csr_6002;
595 CLOSE csr_6002;
596
597 --fnd_file.put_line(fnd_file.log,'10');
598
599 gtagdata(l_counter).TagName := 'REC_6002';
600 gtagdata(l_counter).TagValue := 'REC_6002';
601 l_counter := l_counter + 1;
602
603 /* line num */
604 gtagdata(l_counter).TagName := 'RT6002_01';
605 gtagdata(l_counter).TagValue := rg_csr_6002.action_information30;
606 l_counter := l_counter + 1;
607
608 /*Rec num */
609 gtagdata(l_counter).TagName := 'RT6002_02';
610 gtagdata(l_counter).TagValue := rg_csr_6002.action_information3;
611 l_counter := l_counter + 1;
612
613 -- Field num
614 gtagdata(l_counter).TagName := 'RT6002_03';
615 gtagdata(l_counter).TagValue := rg_csr_6002.action_information6;
616 l_counter := l_counter + 1;
617
618 -- Code Field
619 gtagdata(l_counter).TagName := 'RT6002_04';
620 gtagdata(l_counter).TagValue := rg_csr_6002.action_information7;
621 l_counter := l_counter + 1;
622
623 gtagdata(l_counter).TagName := 'REC_6002';
624 gtagdata(l_counter).TagValue := 'REC_6002_END';
625 l_counter := l_counter + 1;
626
627
628 -- Record 6004
629 OPEN csr_6004(l_payroll_action_id, rg_csr_6000.action_information5);
630 FETCH csr_6004 INTO rg_csr_6004;
631 CLOSE csr_6004;
632
633 --fnd_file.put_line(fnd_file.log,'11');
634
635 gtagdata(l_counter).TagName := 'REC_6004';
636 gtagdata(l_counter).TagValue := 'REC_6004';
637 l_counter := l_counter + 1;
638
639
640 /* line num */
641 gtagdata(l_counter).TagName := 'RT6004_01';
642 gtagdata(l_counter).TagValue := rg_csr_6004.action_information30;
646 gtagdata(l_counter).TagName := 'RT6004_02';
643 l_counter := l_counter + 1;
644
645 /*Rec num */
647 gtagdata(l_counter).TagValue := rg_csr_6004.action_information3;
648 l_counter := l_counter + 1;
649
650 -- Field num
651 gtagdata(l_counter).TagName := 'RT6004_03';
652 gtagdata(l_counter).TagValue := rg_csr_6004.action_information6;
653 l_counter := l_counter + 1;
654
655 -- Code Field
656 gtagdata(l_counter).TagName := 'RT6004_04';
657 gtagdata(l_counter).TagValue := rg_csr_6004.action_information7;
658 l_counter := l_counter + 1;
659
660 gtagdata(l_counter).TagName := 'REC_6004';
661 gtagdata(l_counter).TagValue := 'REC_6004_END';
662 l_counter := l_counter + 1;
663
664
665 -- Record 6005
666 OPEN csr_6005(l_payroll_action_id, rg_csr_6000.action_information5);
667 FETCH csr_6005 INTO rg_csr_6005;
668 CLOSE csr_6005;
669
670 gtagdata(l_counter).TagName := 'REC_6005';
671 gtagdata(l_counter).TagValue := 'REC_6005';
672 l_counter := l_counter + 1;
673
674 /* line num */
675 gtagdata(l_counter).TagName := 'RT6005_01';
676 gtagdata(l_counter).TagValue := rg_csr_6005.action_information30;
677 l_counter := l_counter + 1;
678
679 /*Rec num */
680 gtagdata(l_counter).TagName := 'RT6005_02';
681 gtagdata(l_counter).TagValue := rg_csr_6005.action_information3;
682 l_counter := l_counter + 1;
683
684 -- Field num
685 gtagdata(l_counter).TagName := 'RT6005_03';
686 gtagdata(l_counter).TagValue := rg_csr_6005.action_information6;
687 l_counter := l_counter + 1;
688
689 -- HOURS/Days worked
690 gtagdata(l_counter).TagName := 'RT6005_04';
691 gtagdata(l_counter).TagValue := lpad(rg_csr_6005.action_information7,8,'0');
692 l_counter := l_counter + 1;
693
694 -- Sign
695 gtagdata(l_counter).TagName := 'RT6005_05';
696 gtagdata(l_counter).TagValue := rg_csr_6005.action_information8;
697 l_counter := l_counter + 1;
698
699 gtagdata(l_counter).TagName := 'REC_6005';
700 gtagdata(l_counter).TagValue := 'REC_6005_END';
701 l_counter := l_counter + 1;
702
703
704 gtagdata(l_counter).TagName := 'REC_6000';
705 gtagdata(l_counter).TagValue := 'REC_6000_END';
706 l_counter := l_counter + 1;
707
708 END LOOP;
709 END IF;
710
711 gtagdata(l_counter).TagName := 'REC_2001';
712 gtagdata(l_counter).TagValue := 'REC_2001_END';
713 l_counter := l_counter + 1;
714
715
716 gtagdata(l_counter).TagName := 'REC_1000';
717 gtagdata(l_counter).TagValue := 'REC_1000_END';
718 l_counter := l_counter + 1;
719
720 gtagdata(l_counter).TagName := 'REC_9999';
721 gtagdata(l_counter).TagValue := 'REC_9999';
722 l_counter := l_counter + 1;
723
724 -- Record 9999
725 OPEN csr_9999(l_payroll_action_id);
726 FETCH csr_9999 INTO rg_csr_9999;
727 CLOSE csr_9999;
728
729 /* line num */
730 gtagdata(l_counter).TagName := 'RT9999_01';
731 gtagdata(l_counter).TagValue := rg_csr_9999.action_information30;
732 l_counter := l_counter + 1;
733
734 /*Rec num */
735 gtagdata(l_counter).TagName := 'RT9999_02';
736 gtagdata(l_counter).TagValue := rg_csr_9999.action_information3;
737 l_counter := l_counter + 1;
738
739 -- Number of records
740 gtagdata(l_counter).TagName := 'RT9999_03';
741 gtagdata(l_counter).TagValue := lpad(rg_csr_9999.action_information4,7,'0');
742 l_counter := l_counter + 1;
743
744 gtagdata(l_counter).TagName := 'REC_9999';
745 gtagdata(l_counter).TagValue := 'REC_9999_END';
746 l_counter := l_counter + 1;
747
748
749
750
751 END LOOP;
752
753
754
755 hr_utility.set_location('After populating pl/sql table',30);
756
757
758 WritetoCLOB (p_xml );
759
760
761 END GET_DATA;
762
763 -----------------------------------------------------------------------------------------------------------------
764 PROCEDURE WritetoCLOB(p_xfdf_clob out nocopy CLOB) is
765 l_xfdf_string clob;
766 l_str1 varchar2(1000);
767 l_str2 varchar2(20);
768 l_str3 varchar2(20);
769 l_str4 varchar2(20);
770 l_str5 varchar2(20);
771 l_str6 varchar2(30);
772 l_str7 varchar2(1000);
773 l_str8 varchar2(240);
774 l_str9 varchar2(240);
775
776 current_index pls_integer;
777 l_IANA_charset VARCHAR2 (50);
778
779 BEGIN
780 l_IANA_charset :=PAY_DK_GENERAL.get_IANA_charset ;
781 hr_utility.set_location('Entering WritetoCLOB ',70);
782 l_str1 := '<?xml version="1.0" encoding="'||l_IANA_charset||'"?> <ROOT><EINR>' ;
783 l_str2 := '<';
784 l_str3 := '>';
785 l_str4 := '</';
786 l_str5 := '>';
787 l_str6 := '</EINR></ROOT>';
788 l_str7 := '<?xml version="1.0" encoding="'||l_IANA_charset||'"?> <ROOT></ROOT>';
789
790 --fnd_file.put_line(fnd_file.log,'wc1');
791
792 dbms_lob.createtemporary(l_xfdf_string,FALSE,DBMS_LOB.CALL);
793 dbms_lob.open(l_xfdf_string,dbms_lob.lob_readwrite);
794
795 current_index := 0;
796
797 --fnd_file.put_line(fnd_file.log,'wc2');
798
799 IF gtagdata.count > 0 THEN
800
801 dbms_lob.writeAppend( l_xfdf_string, length(l_str1), l_str1 );
802
803 --fnd_file.put_line(fnd_file.log,'wc3');
804 FOR table_counter IN gtagdata.FIRST .. gtagdata.LAST LOOP
805
806 l_str8 := gtagdata(table_counter).TagName;
807 l_str9 := gtagdata(table_counter).TagValue ;
808
812
809 IF l_str9 IN ('REC_1000','REC_1000_END','REC_2001','REC_2001_END','REC_2101','REC_2101_END',
810 'REC_5000','REC_5000_END','REC_6000','REC_6000_END','REC_6001','REC_6001_END','REC_6002','REC_6002_END',
811 'REC_6004','REC_6004_END','REC_6005','REC_6005_END','REC_9999','REC_9999_END') THEN
813 --fnd_file.put_line(fnd_file.log,'wc4');
814 IF l_str9 IN ('REC_1000','REC_2001','REC_2101','REC_5000','REC_6000','REC_6001','REC_6002','REC_6004','REC_6005','REC_9999') THEN
815 dbms_lob.writeAppend(l_xfdf_string, length(l_str2), l_str2);
816 dbms_lob.writeAppend(l_xfdf_string, length(l_str8), l_str8);
817 dbms_lob.writeAppend(l_xfdf_string, length(l_str3), l_str3);
818 ELSE
819 dbms_lob.writeAppend(l_xfdf_string, length(l_str4), l_str4);
820 dbms_lob.writeAppend(l_xfdf_string, length(l_str8), l_str8);
821 dbms_lob.writeAppend(l_xfdf_string, length(l_str5), l_str5);
822 END IF;
823
824 ELSE
825
826 if l_str9 is not null then
827 l_str9 := hr_dk_utility.REPLACE_SPECIAL_CHARS(l_str9);
828
829 dbms_lob.writeAppend(l_xfdf_string, length(l_str2), l_str2);
830 dbms_lob.writeAppend(l_xfdf_string, length(l_str8), l_str8);
831 dbms_lob.writeAppend(l_xfdf_string, length(l_str3), l_str3);
832 dbms_lob.writeAppend(l_xfdf_string, length(l_str9), l_str9);
833 dbms_lob.writeAppend(l_xfdf_string, length(l_str4), l_str4);
834 dbms_lob.writeAppend(l_xfdf_string, length(l_str8), l_str8);
835 dbms_lob.writeAppend(l_xfdf_string, length(l_str5), l_str5);
836 else
837
838 dbms_lob.writeAppend(l_xfdf_string, length(l_str2), l_str2);
839 dbms_lob.writeAppend(l_xfdf_string, length(l_str8), l_str8);
840 dbms_lob.writeAppend(l_xfdf_string, length(l_str3), l_str3);
841 dbms_lob.writeAppend(l_xfdf_string, length(l_str4), l_str4);
842 dbms_lob.writeAppend(l_xfdf_string, length(l_str8), l_str8);
843 dbms_lob.writeAppend(l_xfdf_string, length(l_str5), l_str5);
844
845 end if;
846
847 END IF;
848
849
850 END LOOP;
851
852 dbms_lob.writeAppend(l_xfdf_string, length(l_str6), l_str6 );
853
854 ELSE
855 dbms_lob.writeAppend(l_xfdf_string, length(l_str7), l_str7 );
856 END IF;
857
858
859 --fnd_file.put_line(fnd_file.log,'wc5');
860 p_xfdf_clob := l_xfdf_string;
861
862 hr_utility.set_location('Leaving WritetoCLOB ',40);
863
864 EXCEPTION
865 WHEN OTHERS then
866 HR_UTILITY.TRACE('sqlerrm ' || SQLERRM);
867 HR_UTILITY.RAISE_ERROR;
868 END WritetoCLOB;
869 -------------------------------------------------------------------------------------------------------------------------
870
871 END PAY_DK_EINR;