[Home] [Help]
PACKAGE BODY: APPS.PAY_DK_EINR
Source
1 PACKAGE BODY PAY_DK_EINR AS
2 /* $Header: pydkeinr.pkb 120.12.12020000.3 2013/02/21 10:32:31 rsahai ship $ */
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_ctr number := 0;
16 l_line_num number := 0;
17 l_sender_id VARCHAR2(8);
18 l_payroll_action_id PAY_ACTION_INFORMATION. ACTION_INFORMATION1%TYPE;
19 l_flag VARCHAR2(1):='N';
20 l_flag_end VARCHAR2(1):='N';
21 /* End of declaration*/
22
23 /* Cursors */
24 /* Cursor to fetch data related to Record 1000*/
25 CURSOR csr_1000 (p_payroll_action_id NUMBER) IS
26 SELECT pai.*
27 FROM pay_action_information pai
28 WHERE pai.action_context_id= p_payroll_action_id
29 AND pai.action_context_type= 'PA'
30 AND pai.action_information_category = 'EMEA REPORT INFORMATION'
31 AND pai.action_information1 = 'PYDKEINA'
32 AND pai.action_information3 ='1000';
33 -- ORDER BY pai.action_information2;
34
35 /* Cursor to fetch data related to Record 2001*/
36 CURSOR csr_2001 (p_payroll_action_id NUMBER) IS
37 SELECT pai.*
38 FROM pay_action_information pai
39 WHERE pai.action_context_id= p_payroll_action_id
40 AND pai.action_context_type= 'PA'
41 AND pai.action_information_category = 'EMEA REPORT INFORMATION'
42 AND pai.action_information1 = 'PYDKEINA'
43 AND pai.action_information3 ='2001'
44 -- AND action_information29 ='2'
45 -- AND pai.action_context_id = p_action_context_id
46 ORDER BY pai.action_information30;
47
48 rg_csr_2001 csr_2001%rowtype;
49
50 /* Cursor to fetch data related to Record 2101*/
51 /* bug 10367494 added new parameter p_se_no */
52 CURSOR csr_2101(p_payroll_action_id NUMBER,p_se_no varchar2) IS
53 SELECT pai.*
54 FROM pay_action_information pai , pay_assignment_actions paa
55 WHERE paa.payroll_action_id = p_payroll_action_id
56 AND pai.action_context_id= paa.assignment_action_id
57 AND pai.action_context_type= 'AAP'
58 AND pai.action_information_category = 'EMEA REPORT INFORMATION'
59 AND pai.action_information1 = 'PYDKEINA'
60 AND pai.action_information3 ='2101'
61 AND pai.action_information11=p_se_no
62 order by pai.action_information30;
63
64 rg_csr_2101 csr_2101%rowtype;
65
66 /* Cursor to fetch data related to Record 5000*/
67 CURSOR csr_5000(p_payroll_action_id NUMBER,p_se_no varchar2) IS
68 SELECT pai.*
69 FROM pay_action_information pai
70 WHERE pai.action_context_id = p_payroll_action_id
71 AND pai.action_context_type= 'PA'
72 AND pai.action_information_category = 'EMEA REPORT INFORMATION'
73 AND pai.action_information1 = 'PYDKEINA'
74 AND pai.action_information3 = '5000'
75 and pai.action_information11 = p_se_no
76 order by pai.action_information30; /* 9587046 */
77 rg_csr_5000 csr_5000%rowtype;
78
79 CURSOR csr_5000R(p_payroll_action_id NUMBER,p_payroll_id NUMBER,p_employement_type NUMBER,p_time_period_id NUMBER,p_green_land_code NUMBER,p_se_no varchar2) IS
80 SELECT pai.*
81 FROM pay_action_information pai
82 WHERE pai.action_context_id = p_payroll_action_id
83 AND pai.action_context_type= 'PA'
84 AND pai.action_information_category = 'EMEA REPORT INFORMATION'
85 AND pai.action_information1 = 'PYDKEINA'
86 AND pai.action_information3 = '5000R'
87 AND pai.action_information25 = p_payroll_id
88 AND pai.action_information9=p_employement_type
89 AND pai.action_information8=p_green_land_code /* 8847591 */
90 AND pai.action_information24=p_time_period_id
91 AND pai.action_information11=p_se_no;
92
93 /* Cursor to fetch data related to Record 6000*/
94 /* bug 10367494 added new parameter p_se_no */
95 CURSOR csr_6000(p_payroll_action_id NUMBER, p_payroll_id NUMBER,p_employement_type NUMBER,p_time_period_id NUMBER,p_green_land_code NUMBER,p_se_no varchar2) IS /* 9489806 */
96 SELECT pai.*
97 FROM pay_action_information pai, pay_assignment_actions paa
98 WHERE paa.payroll_action_id= p_payroll_action_id
99 AND pai.action_context_id= paa.assignment_action_id
100 AND pai.action_context_type= 'AAP'
101 AND pai.action_information_category = 'EMEA REPORT INFORMATION'
102 AND pai.action_information1 = 'PYDKEINA'
103 AND pai.action_information3 = '6000'
104 AND pai.action_information25 = p_payroll_id
105 AND pai.action_information26=p_employement_type
106 AND pai.action_information24=p_time_period_id
107 AND pai.action_information27=p_green_land_code /* 8847591 */
108 AND pai.action_information11=p_se_no
109 order by pai.action_information30;
110
111 /* Cursor to fetch data related to Record 8001 */
112 CURSOR csr_8001(p_payroll_action_id NUMBER, p_assignment_id NUMBER, p_payroll_id NUMBER,p_employement_type NUMBER,p_time_period_id NUMBER,p_green_land_code NUMBER) IS /* 9489806 */
113 SELECT pai.*
114 FROM pay_action_information pai, pay_assignment_actions paa
115 WHERE paa.payroll_action_id= p_payroll_action_id
116 AND pai.action_context_id= paa.assignment_action_id
117 AND pai.action_context_type= 'AAP'
118 AND pai.action_information_category = 'EMEA REPORT INFORMATION'
119 AND pai.action_information1 = 'PYDKEINA'
120 AND pai.action_information3 = '8001'
121 AND pai.action_information5 = p_assignment_id
122 AND pai.action_information25 = p_payroll_id
123 AND pai.action_information26=p_employement_type
124 AND pai.action_information24=p_time_period_id
125 AND pai.action_information27=p_green_land_code /* 8847591 */
126 ORDER BY pai.action_information30;
127
128 /* 9587046R */
129 CURSOR csr_8001R(p_payroll_action_id NUMBER, p_assignment_id NUMBER, p_payroll_id NUMBER,p_employement_type NUMBER,p_time_period_id NUMBER,p_green_land_code NUMBER) IS /* 9489806 */
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 = '8001R'
138 AND pai.action_information5 = p_assignment_id
139 AND pai.action_information25 = p_payroll_id
140 AND pai.action_information26=p_employement_type
141 AND pai.action_information24=p_time_period_id
142 AND pai.action_information27=p_green_land_code /* 8847591 */
143 ORDER BY pai.action_information30;
144
145 /* Cursor to fetch data related to Record 6001 */
146 CURSOR csr_6001(p_payroll_action_id NUMBER, p_assignment_id NUMBER, p_payroll_id NUMBER,p_employement_type NUMBER,p_time_period_id NUMBER,p_green_land_code NUMBER) IS /* 9489806 */
147 SELECT pai.*
151 AND pai.action_context_type= 'AAP'
148 FROM pay_action_information pai, pay_assignment_actions paa
149 WHERE paa.payroll_action_id= p_payroll_action_id
150 AND pai.action_context_id= paa.assignment_action_id
152 AND pai.action_information_category = 'EMEA REPORT INFORMATION'
153 AND pai.action_information1 = 'PYDKEINA'
154 AND pai.action_information3 = '6001'
155 AND pai.action_information5 = p_assignment_id
156 AND nvl(pai.action_information29, 'N') <> 'Y'
157 AND pai.action_information25 = p_payroll_id
158 AND pai.action_information26=p_employement_type
159 AND pai.action_information24=p_time_period_id
160 AND pai.action_information27=p_green_land_code /* 8847591 */
161 ORDER BY pai.action_information30;
162
163 /* Cursor to fetch data related to Correction Record 6001 */
164 CURSOR csr_6001_corr(p_payroll_action_id NUMBER, p_payroll_id NUMBER,p_employement_type NUMBER,p_time_period_id NUMBER,p_green_land_code NUMBER,p_se_no varchar2) IS /* 9489806 */
165 SELECT distinct pai.action_information5,pai.action_information3,action_context_id /* 9587046R */
166 FROM pay_action_information pai, pay_assignment_actions paa
167 WHERE paa.payroll_action_id= p_payroll_action_id
168 AND pai.action_context_id= paa.assignment_action_id
169 AND pai.action_context_type= 'AAP'
170 AND pai.action_information_category = 'EMEA REPORT INFORMATION'
171 AND pai.action_information1 = 'PYDKEINA'
172 AND pai.action_information3 = '6001'
173 AND nvl(pai.action_information29, 'N') = 'Y'
174 AND pai.action_information25 = p_payroll_id
175 AND pai.action_information26=p_employement_type
176 AND pai.action_information24=p_time_period_id
177 AND pai.action_information27=p_green_land_code /* 8847591 */
178 AND pai.action_information11=p_se_no
179 ORDER BY pai.action_information3,action_context_id DESC; /* 9587046R */
180
181 /* Cursor to fetch data related to Correction Record 6001 */
182 CURSOR csr_6001_corr_asst(p_payroll_action_id NUMBER, p_assignment_id NUMBER, p_payroll_id NUMBER,p_employement_type NUMBER,p_time_period_id NUMBER,p_green_land_code NUMBER) IS /* 9489806 */
183 SELECT pai.*
184 FROM pay_action_information pai, pay_assignment_actions paa
185 WHERE paa.payroll_action_id= p_payroll_action_id
186 AND pai.action_context_id= paa.assignment_action_id
187 AND pai.action_context_type= 'AAP'
188 AND pai.action_information_category = 'EMEA REPORT INFORMATION'
189 AND pai.action_information1 = 'PYDKEINA'
190 AND pai.action_information3 = '6001'
191 AND pai.action_information5 = p_assignment_id
192 AND nvl(pai.action_information29, 'N') = 'Y'
193 AND pai.action_information25 = p_payroll_id
194 AND pai.action_information26=p_employement_type
195 AND pai.action_information24=p_time_period_id
196 AND pai.action_information27=p_green_land_code /* 8847591 */
197 ORDER BY pai.action_information30;
198
199 /* Cursor to fetch data related to Correction Record 6000*/
200 CURSOR csr_6000_corr(p_payroll_action_id NUMBER, p_assignment_id NUMBER, p_payroll_id NUMBER,p_employement_type NUMBER,p_time_period_id NUMBER,p_green_land_code NUMBER,p_se_no varchar2) IS /* 9489806 */
201 SELECT pai.*
202 FROM pay_action_information pai, pay_assignment_actions paa
203 WHERE paa.payroll_action_id= p_payroll_action_id
204 AND pai.action_context_id= paa.assignment_action_id
205 AND pai.action_context_type= 'AAP'
206 AND pai.action_information_category = 'EMEA REPORT INFORMATION'
207 AND pai.action_information1 = 'PYDKEINA'
208 AND pai.action_information3 = '6000R'
209 AND pai.action_information5 = p_assignment_id
210 AND pai.action_information25 = p_payroll_id
211 AND pai.action_information26=p_employement_type
212 AND pai.action_information24=p_time_period_id
213 AND pai.action_information27=p_green_land_code /* 8847591 */
214 AND pai.action_information11=p_se_no /* 10367494 */
215 order by pai.action_information30;
216
217 /* Cursor to fetch data related to Record 6002 */
218 CURSOR csr_6002(p_payroll_action_id NUMBER, p_assignment_id NUMBER, p_payroll_id NUMBER,p_employement_type NUMBER,p_time_period_id NUMBER,p_green_land_code NUMBER) IS /* 9489806 */
219 SELECT pai.*
220 FROM pay_action_information pai, pay_assignment_actions paa
221 WHERE paa.payroll_action_id= p_payroll_action_id
222 AND pai.action_context_id= paa.assignment_action_id
223 AND pai.action_context_type= 'AAP'
224 AND pai.action_information_category = 'EMEA REPORT INFORMATION'
225 AND pai.action_information1 = 'PYDKEINA'
226 AND pai.action_information3 = '6002'
227 AND pai.action_information5 = p_assignment_id
228 AND pai.action_information25 = p_payroll_id
229 AND pai.action_information26=p_employement_type
230 AND pai.action_information24=p_time_period_id
231 AND pai.action_information27=p_green_land_code; /* 8847591 */
232
233 /* Cursor to fetch data related to Record 6003 */
234 CURSOR csr_6003(p_payroll_action_id NUMBER, p_assignment_id NUMBER, p_payroll_id NUMBER,p_employement_type NUMBER,p_time_period_id NUMBER,p_green_land_code NUMBER) IS /* 9489806 */
235 SELECT pai.*
236 FROM pay_action_information pai, pay_assignment_actions paa
237 WHERE paa.payroll_action_id= p_payroll_action_id
238 AND pai.action_context_id= paa.assignment_action_id
239 AND pai.action_context_type= 'AAP'
240 AND pai.action_information_category = 'EMEA REPORT INFORMATION'
241 AND pai.action_information1 = 'PYDKEINA'
242 AND pai.action_information3 = '6003'
243 AND pai.action_information5 = p_assignment_id
244 AND pai.action_information25 = p_payroll_id
245 AND pai.action_information26=p_employement_type
246 AND pai.action_information24=p_time_period_id
247 AND pai.action_information27=p_green_land_code; /* 8847591 */
248
249 /* Cursor to fetch data related to Record 6004 */
250 CURSOR csr_6004(p_payroll_action_id NUMBER, p_assignment_id NUMBER, p_payroll_id NUMBER,p_employement_type NUMBER,p_time_period_id NUMBER,p_green_land_code NUMBER) IS /* 9489806 */
251 SELECT pai.*
252 FROM pay_action_information pai, pay_assignment_actions paa
253 WHERE paa.payroll_action_id= p_payroll_action_id
254 AND pai.action_context_id= paa.assignment_action_id
255 AND pai.action_context_type= 'AAP'
256 AND pai.action_information_category = 'EMEA REPORT INFORMATION'
257 AND pai.action_information1 = 'PYDKEINA'
258 AND pai.action_information3 = '6004'
259 AND pai.action_information5 = p_assignment_id
260 AND pai.action_information25 = p_payroll_id
261 AND pai.action_information26=p_employement_type
262 AND pai.action_information24=p_time_period_id
263 AND pai.action_information27=p_green_land_code; /* 8847591 */
264
265 /* Cursor to fetch data related to Record 6005 */
266 CURSOR csr_6005(p_payroll_action_id NUMBER, p_assignment_id NUMBER, p_payroll_id NUMBER,p_employement_type NUMBER,p_time_period_id NUMBER,p_green_land_code NUMBER) IS /* 9489806 */
267 SELECT pai.*
268 FROM pay_action_information pai, pay_assignment_actions paa
269 WHERE paa.payroll_action_id= p_payroll_action_id
270 AND pai.action_context_id= paa.assignment_action_id
271 AND pai.action_context_type= 'AAP'
272 AND pai.action_information_category = 'EMEA REPORT INFORMATION'
273 AND pai.action_information1 = 'PYDKEINA'
274 AND pai.action_information3 = '6005'
275 AND pai.action_information5 = p_assignment_id
276 AND pai.action_information25 = p_payroll_id
277 AND pai.action_information26=p_employement_type
278 AND pai.action_information24=p_time_period_id
279 AND pai.action_information27=p_green_land_code /* 8847591 */
280 ORDER BY pai.action_information30; --14095960
281
282 --13463885
283 /* Cursor to fetch data related to Record 6102 */
284 CURSOR csr_6102(p_payroll_action_id NUMBER, p_assignment_id NUMBER, p_payroll_id NUMBER,p_employement_type NUMBER,p_time_period_id NUMBER,p_green_land_code NUMBER) IS /* 9489806 */
285 SELECT pai.*
286 FROM pay_action_information pai, pay_assignment_actions paa
287 WHERE paa.payroll_action_id= p_payroll_action_id
288 AND pai.action_context_id= paa.assignment_action_id
289 AND pai.action_context_type= 'AAP'
290 AND pai.action_information_category = 'EMEA REPORT INFORMATION'
291 AND pai.action_information1 = 'PYDKEINA'
292 AND pai.action_information3 = '6102'
293 AND pai.action_information5 = p_assignment_id
294 AND pai.action_information25 = p_payroll_id
295 AND pai.action_information26=p_employement_type
296 AND pai.action_information24=p_time_period_id
297 AND pai.action_information27=p_green_land_code
298 order by pai.action_information30; --13630274 /* 8847591 */
299 --13463885
300
301 /* Cursor to fetch data related to Record 9999 */
302 CURSOR csr_9999(p_payroll_action_id NUMBER) IS
303 SELECT pai.*
304 FROM pay_action_information pai
305 WHERE pai.action_context_id = p_payroll_action_id
306 AND pai.action_context_type= 'PA'
307 AND pai.action_information_category = 'EMEA REPORT INFORMATION'
308 AND pai.action_information1 = 'PYDKEINA'
309 AND pai.action_information3 = '9999';
310
311 rg_csr_9999 csr_9999%rowtype;
312
313 /* End of Cursors */
314 BEGIN
315
316 --fnd_file.put_line(fnd_file.log,'1');
317 IF p_payroll_action_id IS NULL THEN
318
319 BEGIN
320
321 SELECT payroll_action_id
322 INTO l_payroll_action_id
323 FROM pay_payroll_actions ppa,
324 fnd_conc_req_summary_v fcrs,
325 fnd_conc_req_summary_v fcrs1
326 WHERE fcrs.request_id = FND_GLOBAL.CONC_REQUEST_ID
327 AND fcrs.priority_request_id = fcrs1.priority_request_id
328 AND ppa.request_id between fcrs1.request_id and fcrs.request_id
329 AND ppa.request_id = fcrs1.request_id;
330
331 EXCEPTION
332 WHEN others THEN
333 NULL;
334 END ;
335 --fnd_file.put_line(fnd_file.log,'2');
336 ELSE
337
338 l_payroll_action_id :=p_payroll_action_id;
339 --fnd_file.put_line(fnd_file.log,'3');
340 END IF;
341
342 hr_utility.set_location('Entered Procedure GETDATA',10);
343
344 /* Pick up the data related to Record 1000*/
345 FOR rg_csr_1000 IN csr_1000 (l_payroll_action_id)
346 LOOP
347 --fnd_file.put_line(fnd_file.log,'4');
348
349 gtagdata(l_counter).TagName := 'REC_1000';
350 gtagdata(l_counter).TagValue := 'REC_1000';
351 l_counter := l_counter + 1;
352
353 /* line num */
354 gtagdata(l_counter).TagName := 'RT1000_01';
355 gtagdata(l_counter).TagValue := rg_csr_1000.action_information30;
356 l_line_num := to_number(rg_csr_1000.action_information30);
357 l_counter := l_counter + 1;
358
359 /*Rec num */
360 gtagdata(l_counter).TagName := 'RT1000_02';
361 gtagdata(l_counter).TagValue := rg_csr_1000.action_information3;
362 l_counter := l_counter + 1;
363
364 /*Date sent*/
365 gtagdata(l_counter).TagName := 'RT1000_03';
366 gtagdata(l_counter).TagValue := rg_csr_1000.action_information4;
367 l_counter := l_counter + 1;
368
369 /*Time sent*/
370 gtagdata(l_counter).TagName := 'RT1000_04';
371 gtagdata(l_counter).TagValue := rg_csr_1000.action_information5;
372 l_counter := l_counter + 1;
373
374 /*SE number*/
375 gtagdata(l_counter).TagName := 'RT1000_05';
376 gtagdata(l_counter).TagValue := lpad(rg_csr_1000.action_information6,8,'0');
377 l_counter := l_counter + 1;
378
379 /*CVR number*/
380 gtagdata(l_counter).TagName := 'RT1000_06';
381 gtagdata(l_counter).TagValue := rg_csr_1000.action_information7;
382 l_counter := l_counter + 1;
383
384 /*Sender type*/
385 gtagdata(l_counter).TagName := 'RT1000_07';
386 gtagdata(l_counter).TagValue := rg_csr_1000.action_information8;
387 l_counter := l_counter + 1;
388
389 /*Filler*/
390 gtagdata(l_counter).TagName := 'RT1000_08';
391 gtagdata(l_counter).TagValue := lpad('0',5,'0');
392 l_counter := l_counter + 1;
393
394 /*Report Method name*/
395 gtagdata(l_counter).TagName := 'RT1000_09';
396 gtagdata(l_counter).TagValue := rg_csr_1000.action_information9;
397 l_counter := l_counter + 1;
398
399 /*IT System*/
400 gtagdata(l_counter).TagName := 'RT1000_10';
401 gtagdata(l_counter).TagValue := rg_csr_1000.action_information10;
402 l_counter := l_counter + 1;
403
404 /* IT System Version */
405 gtagdata(l_counter).TagName := 'RT1000_11';
406 gtagdata(l_counter).TagValue := rg_csr_1000.action_information11;
407 l_counter := l_counter + 1;
408
409 /*Main sender ID*/
410 gtagdata(l_counter).TagName := 'RT1000_12';
411 gtagdata(l_counter).TagValue := rg_csr_1000.action_information12;
412 l_sender_id := rg_csr_1000.action_information12;
413 l_counter := l_counter + 1;
414
415 /*E-Income version*/
416 gtagdata(l_counter).TagName := 'RT1000_13';
417 gtagdata(l_counter).TagValue := rg_csr_1000.action_information13;
418 l_counter := l_counter + 1;
419
420 /*Test Marking*/
421 gtagdata(l_counter).TagName := 'RT1000_14';
422 gtagdata(l_counter).TagValue := rg_csr_1000.action_information14;
423 l_counter := l_counter + 1;
424
425 /*Filler */
426 gtagdata(l_counter).TagName := 'RT1000_15';
427 gtagdata(l_counter).TagValue := ''; -- 16 spaces
428 l_counter := l_counter + 1;
429
430 /*Filler */
431 gtagdata(l_counter).TagName := 'RT1000_16';
432 gtagdata(l_counter).TagValue := ''; -- 16 spaces
433 l_counter := l_counter + 1;
434
435 /*Indication of E-Income*/
436 gtagdata(l_counter).TagName := 'RT1000_17';
437 gtagdata(l_counter).TagValue := rg_csr_1000.action_information15;
438 l_counter := l_counter + 1;
439
440
441 -- Record 2001
442 /* bug 10367494 added itearting logic for rec 2001 */
443 /* OPEN csr_2001(l_payroll_action_id);
444 FETCH csr_2001 INTO rg_csr_2001;
445 CLOSE csr_2001; */
446 FOR rg_csr_2001 IN csr_2001(l_payroll_action_id)
447 LOOP
448 --fnd_file.put_line(fnd_file.log,'5');
449
450 gtagdata(l_counter).TagName := 'REC_2001';
451 gtagdata(l_counter).TagValue := 'REC_2001';
452 l_counter := l_counter + 1;
453
454 /* line num */
455 gtagdata(l_counter).TagName := 'RT2001_01';
456 gtagdata(l_counter).TagValue := rg_csr_2001.action_information30;
457 l_line_num := to_number(rg_csr_2001.action_information30);
458 l_counter := l_counter + 1;
459
460 /*Rec num */
461 gtagdata(l_counter).TagName := 'RT2001_02';
462 gtagdata(l_counter).TagValue := rg_csr_2001.action_information3;
463 l_counter := l_counter + 1;
464
465 /*Filler*/
466 gtagdata(l_counter).TagName := 'RT2001_03';
467 gtagdata(l_counter).TagValue := ''; --16 spaces
468 l_counter := l_counter + 1;
469
470 /*Company */
471 gtagdata(l_counter).TagName := 'RT2001_04';
472 gtagdata(l_counter).TagValue := rg_csr_2001.action_information4;
473 l_counter := l_counter + 1;
474
475 /*Termination of company*/
476 gtagdata(l_counter).TagName := 'RT2001_05';
477 gtagdata(l_counter).TagValue := rg_csr_2001.action_information5;
478 l_counter := l_counter + 1;
479
480 /*Currency */
481 gtagdata(l_counter).TagName := 'RT2001_06';
482 gtagdata(l_counter).TagValue := rg_csr_2001.action_information6;
483 l_counter := l_counter + 1;
484
485 --fnd_file.put_line(fnd_file.log,'Company Terminating value : '||rg_csr_2001.action_information5);
486 if(rg_csr_2001.action_information5 IS NULL) then
487 -- Record 2101
488 /* bug 10367494 passing p_se_no to rec 2101 */
489 FOR rg_csr_2101 IN csr_2101(l_payroll_action_id,rg_csr_2001.action_information4)
490 LOOP
491
492 --fnd_file.put_line(fnd_file.log,'6');
493
494 gtagdata(l_counter).TagName := 'REC_2101';
495 gtagdata(l_counter).TagValue := 'REC_2101';
496 l_counter := l_counter + 1;
497
498 /* line num */
499 gtagdata(l_counter).TagName := 'RT2101_01';
500 gtagdata(l_counter).TagValue := rg_csr_2101.action_information30;
501 l_line_num := to_number(rg_csr_2101.action_information30);
502 l_counter := l_counter + 1;
503
504 /*Rec num */
505 gtagdata(l_counter).TagName := 'RT2101_02';
506 gtagdata(l_counter).TagValue := rg_csr_2101.action_information3;
507 l_counter := l_counter + 1;
508
509 /*CPR number */
510 gtagdata(l_counter).TagName := 'RT2101_03';
511 gtagdata(l_counter).TagValue := rg_csr_2101.action_information6;
512 l_counter := l_counter + 1;
516 gtagdata(l_counter).TagValue := lpad('0',8,'0'); -- 8 zeros
513
514 /*Numeric Filler*/
515 gtagdata(l_counter).TagName := 'RT2101_04';
517 l_counter := l_counter + 1;
518
519 /*Filler*/
520 gtagdata(l_counter).TagName := 'RT2101_05';
521 gtagdata(l_counter).TagValue := ''; -- 15 spaces
522 l_counter := l_counter + 1;
523
524 /*Hire date */
525 gtagdata(l_counter).TagName := 'RT2101_06';
526 gtagdata(l_counter).TagValue := rg_csr_2101.action_information7;
527 l_counter := l_counter + 1;
528
529 /*Termination Date*/
530 gtagdata(l_counter).TagName := 'RT2101_07';
531 if(rg_csr_2101.action_information8 IS NULL) then
532 gtagdata(l_counter).TagValue := lpad('0',8,'0'); -- 8 zeros
533 else
534 gtagdata(l_counter).TagValue := rg_csr_2101.action_information8;
535 end if;
536 l_counter := l_counter + 1;
537
538 /*Numeric Filler*/
539 gtagdata(l_counter).TagName := 'RT2101_08';
540 gtagdata(l_counter).TagValue := lpad('0',5,'0'); -- 5 zeros
541 l_counter := l_counter + 1;
542
543 /*Numeric Filler*/
544 gtagdata(l_counter).TagName := 'RT2101_09';
545 gtagdata(l_counter).TagValue := lpad('0',5,'0'); -- 5 zeros
546 l_counter := l_counter + 1;
547
548 /*Numeric Filler*/
549 gtagdata(l_counter).TagName := 'RT2101_10';
550 gtagdata(l_counter).TagValue := lpad('0',4,'0'); -- 4 zeros
551 l_counter := l_counter + 1;
552
553 /*Numeric Filler */
554 gtagdata(l_counter).TagName := 'RT2101_11';
555 gtagdata(l_counter).TagValue := lpad('0',10,'0'); -- 10 zeros
556 l_counter := l_counter + 1;
557
558 /*Tax Card type*/
559 gtagdata(l_counter).TagName := 'RT2101_12';
560 gtagdata(l_counter).TagValue := rg_csr_2101.action_information9;
561 l_counter := l_counter + 1;
562
563 /*Valid from */
564 gtagdata(l_counter).TagName := 'RT2101_13';
565 gtagdata(l_counter).TagValue := rg_csr_2101.action_information10;
566 l_counter := l_counter + 1;
567
568 /*emp num - filler*/
569 gtagdata(l_counter).TagName := 'RT2101_14';
570 gtagdata(l_counter).TagValue := ''; -- 50 spaces
571 l_counter := l_counter + 1;
572
573 /*Rekv_taxcard */
574 gtagdata(l_counter).TagName := 'RT2101_15';
575 gtagdata(l_counter).TagValue := ''; -- 1 space
576 l_counter := l_counter + 1;
577
578 gtagdata(l_counter).TagName := 'REC_2101';
579 gtagdata(l_counter).TagValue := 'REC_2101_END';
580 l_counter := l_counter + 1;
581
582
583 END LOOP;
584
585 -- Record 5000
586 /*OPEN csr_5000(l_payroll_action_id);
587 FETCH csr_5000 INTO rg_csr_5000;
588 CLOSE csr_5000;
589 */
590 FOR rg_csr_5000 IN csr_5000(l_payroll_action_id,rg_csr_2001.action_information4)
591 LOOP
592
593 /*Raji gtagdata(l_counter).TagName := 'REC_5000';
594 gtagdata(l_counter).TagValue := 'REC_5000_END';
595 l_counter := l_counter + 1; */
596
597
598 -- record 6000
599 l_flag:='N';
600 fnd_file.put_line(fnd_file.log,'csr_5000.person:' || l_flag);
601 fnd_file.put_line(fnd_file.log,'l_flag:' || l_flag);
602 fnd_file.put_line(fnd_file.log,'l_flag_end:' || l_flag_end);
603 /* bug 10367494 passing p_se_no to rec 2101 */
604 FOR rg_csr_6000 IN csr_6000(l_payroll_action_id,rg_csr_5000.action_information25,rg_csr_5000.action_information9,rg_csr_5000.action_information24,rg_csr_5000.action_information8,rg_csr_2001.action_information4) /* 9489806 */
605 LOOP
606 IF(l_flag='N') THEN
607
608 /* 9489806 Brought from outside for loop to inside for loop */
609 gtagdata(l_counter).TagName := 'REC_5000';
610 gtagdata(l_counter).TagValue := 'REC_5000';
611 l_counter := l_counter + 1;
612
613 --fnd_file.put_line(fnd_file.log,'7');
614
615 /* line num */
616 gtagdata(l_counter).TagName := 'RT5000_01';
617 gtagdata(l_counter).TagValue := rg_csr_5000.action_information30;
618 l_line_num := to_number(rg_csr_5000.action_information30);
619 l_counter := l_counter + 1;
620
621 /*Rec num */
622 gtagdata(l_counter).TagName := 'RT5000_02';
623 gtagdata(l_counter).TagValue := rg_csr_5000.action_information3;
624 l_counter := l_counter + 1;
625
626 gtagdata(l_counter).TagName := 'RT5000_03';
627 gtagdata(l_counter).TagValue := ''; -- 1 space
628 l_counter := l_counter + 1;
629
630 gtagdata(l_counter).TagName := 'RT5000_04';
631 gtagdata(l_counter).TagValue := rg_csr_5000.action_information10; /* bug fix 7579265 */
632 l_counter := l_counter + 1;
633
634 gtagdata(l_counter).TagName := 'RT5000_05';
635 gtagdata(l_counter).TagValue := ''; -- 16 spaces
636 l_counter := l_counter + 1;
637
638 /*Pay period start date*/
639 gtagdata(l_counter).TagName := 'RT5000_06';
640 gtagdata(l_counter).TagValue := rg_csr_5000.action_information4;
641 l_counter := l_counter + 1;
642
643 /*period end date*/
644 gtagdata(l_counter).TagName := 'RT5000_07';
645 gtagdata(l_counter).TagValue := rg_csr_5000.action_information5;
646 l_counter := l_counter + 1;
647
648 /*Disposal date*/
649 gtagdata(l_counter).TagName := 'RT5000_08';
650 gtagdata(l_counter).TagValue := rg_csr_5000.action_information6;
651 l_counter := l_counter + 1;
652
656
653 gtagdata(l_counter).TagName := 'RT5000_09';
654 gtagdata(l_counter).TagValue := ''; -- 1 space
655 l_counter := l_counter + 1;
657 /*Payment*/
658 gtagdata(l_counter).TagName := 'RT5000_10';
659 gtagdata(l_counter).TagValue := rg_csr_5000.action_information7;
660 l_counter := l_counter + 1;
661
662 /*Greenland code*/
663 gtagdata(l_counter).TagName := 'RT5000_11';
664 gtagdata(l_counter).TagValue := rg_csr_5000.action_information8;
665 l_counter := l_counter + 1;
666
667 /*Employment code*/
668 gtagdata(l_counter).TagName := 'RT5000_12';
669 gtagdata(l_counter).TagValue := rg_csr_5000.action_information9;
670 l_counter := l_counter + 1;
671
672 l_flag:='Y';
673 END IF;
674
675 --fnd_file.put_line(fnd_file.log,'8');
676
677 gtagdata(l_counter).TagName := 'REC_6000';
678 gtagdata(l_counter).TagValue := 'REC_6000';
679 l_counter := l_counter + 1;
680
681 /* line num */
682 gtagdata(l_counter).TagName := 'RT6000_01';
683 gtagdata(l_counter).TagValue := rg_csr_6000.action_information30;
684 l_line_num := to_number(rg_csr_6000.action_information30);
685 l_counter := l_counter + 1;
686
687 /*Rec num */
688 gtagdata(l_counter).TagName := 'RT6000_02';
689 gtagdata(l_counter).TagValue := rg_csr_6000.action_information3;
690 l_counter := l_counter + 1;
691
692 -- CVR num
693 gtagdata(l_counter).TagName := 'RT6000_03';
694 gtagdata(l_counter).TagValue := rg_csr_6000.action_information6;
695 l_counter := l_counter + 1;
696
697 -- CPR
698 gtagdata(l_counter).TagName := 'RT6000_04';
699 gtagdata(l_counter).TagValue := rg_csr_6000.action_information7;
700 l_counter := l_counter + 1;
701
702 -- Numeric Filler
703 gtagdata(l_counter).TagName := 'RT6000_05';
704 gtagdata(l_counter).TagValue := lpad('0',8,'0'); -- 8 zeros
705 l_counter := l_counter + 1;
706
707 /*Assignment num*/
708 gtagdata(l_counter).TagName := 'RT6000_06';
709 gtagdata(l_counter).TagValue := rg_csr_6000.action_information8;
710 l_counter := l_counter + 1;
711
712 gtagdata(l_counter).TagName := 'RT6000_07';
713 gtagdata(l_counter).TagValue := ''; -- 1 space
714 l_counter := l_counter + 1;
715
716 gtagdata(l_counter).TagName := 'RT6000_08';
717 gtagdata(l_counter).TagValue := lpad('0',3,'0'); -- 3 zeros
718 l_counter := l_counter + 1;
719
720 gtagdata(l_counter).TagName := 'RT6000_09';
721 gtagdata(l_counter).TagValue := ''; -- 25 spaces
722 l_counter := l_counter + 1;
723
724 /*Code 68*/
725 gtagdata(l_counter).TagName := 'RT6000_10';
726 gtagdata(l_counter).TagValue := rg_csr_6000.action_information9;
727 l_counter := l_counter + 1;
728
729 /*PU code*/
730 gtagdata(l_counter).TagName := 'RT6000_11';
731 gtagdata(l_counter).TagValue := rg_csr_6000.action_information10;
732 l_counter := l_counter + 1;
733
734 /* bug fix 7613211 */
735 -- record 8001
736 FOR rg_csr_8001 IN csr_8001(l_payroll_action_id, rg_csr_6000.action_information5,rg_csr_5000.action_information25
737 ,rg_csr_5000.action_information9,rg_csr_5000.action_information24,rg_csr_5000.action_information8) /* 9489806 8847591 */
738 LOOP
739 gtagdata(l_counter).TagName := 'REC_8001';
740 gtagdata(l_counter).TagValue := 'REC_8001';
741 l_counter := l_counter + 1;
742
743 /* line num */
744 gtagdata(l_counter).TagName := 'RT8001_01';
745 gtagdata(l_counter).TagValue := rg_csr_8001.action_information30;
746 l_line_num := to_number(rg_csr_8001.action_information30);
747 l_counter := l_counter + 1;
748
749 /*Rec num */
750 gtagdata(l_counter).TagName := 'RT8001_02';
751 gtagdata(l_counter).TagValue := rg_csr_8001.action_information3;
752 l_counter := l_counter + 1;
753
754 /*Birthday */
755 gtagdata(l_counter).TagName := 'RT8001_03';
756 gtagdata(l_counter).TagValue := rg_csr_8001.action_information6;
757 l_counter := l_counter + 1;
758
759 /*Person gender*/
760 gtagdata(l_counter).TagName := 'RT8001_04';
761 gtagdata(l_counter).TagValue := rg_csr_8001.action_information7;
762 l_counter := l_counter + 1;
763
764 /*Country*/
765 gtagdata(l_counter).TagName := 'RT8001_05';
766 gtagdata(l_counter).TagValue := rg_csr_8001.action_information8;
767 l_counter := l_counter + 1;
768
769 /*Person Name */
770 gtagdata(l_counter).TagName := 'RT8001_06';
771 gtagdata(l_counter).TagValue := rg_csr_8001.action_information9;
772 l_counter := l_counter + 1;
773
774 /*Address*/
775 gtagdata(l_counter).TagName := 'RT8001_07';
776 gtagdata(l_counter).TagValue := rg_csr_8001.action_information10;
777 l_counter := l_counter + 1;
778
779 /*Postal code*/
780 gtagdata(l_counter).TagName := 'RT8001_08';
781 gtagdata(l_counter).TagValue := rg_csr_8001.action_information11;
782 l_counter := l_counter + 1;
783
784 /*Town*/
785 gtagdata(l_counter).TagName := 'RT8001_09';
786 gtagdata(l_counter).TagValue := rg_csr_8001.action_information12;
787 l_counter := l_counter + 1;
788
789 gtagdata(l_counter).TagName := 'REC_8001';
790 gtagdata(l_counter).TagValue := 'REC_8001_END';
791 l_counter := l_counter + 1;
792 END LOOP;
793
794 -- record 6001
798
795 FOR rg_csr_6001 IN csr_6001(l_payroll_action_id, rg_csr_6000.action_information5,rg_csr_5000.action_information25
796 ,rg_csr_5000.action_information9,rg_csr_5000.action_information24,rg_csr_5000.action_information8) /* 9489806 8847591 */
797 LOOP
799 --fnd_file.put_line(fnd_file.log,'9');
800
801 gtagdata(l_counter).TagName := 'REC_6001';
802 gtagdata(l_counter).TagValue := 'REC_6001';
803 l_counter := l_counter + 1;
804
805 /* line num */
806 gtagdata(l_counter).TagName := 'RT6001_01';
807 gtagdata(l_counter).TagValue := rg_csr_6001.action_information30;
808 l_line_num := to_number(rg_csr_6001.action_information30);
809 l_counter := l_counter + 1;
810
811 /*Rec num */
812 gtagdata(l_counter).TagName := 'RT6001_02';
813 gtagdata(l_counter).TagValue := rg_csr_6001.action_information3;
814 l_counter := l_counter + 1;
815
816 -- Field num
817 gtagdata(l_counter).TagName := 'RT6001_03';
818 gtagdata(l_counter).TagValue := rg_csr_6001.action_information6;
819 l_counter := l_counter + 1;
820
821 -- Amount
822 gtagdata(l_counter).TagName := 'RT6001_04';
823 gtagdata(l_counter).TagValue := rg_csr_6001.action_information7;
824 l_counter := l_counter + 1;
825
826 -- Sign
827 gtagdata(l_counter).TagName := 'RT6001_05';
828 gtagdata(l_counter).TagValue := rg_csr_6001.action_information8;
829 l_counter := l_counter + 1;
830
831 gtagdata(l_counter).TagName := 'REC_6001';
832 gtagdata(l_counter).TagValue := 'REC_6001_END';
833 l_counter := l_counter + 1;
834 END LOOP;
835
836 -- Record 6002
837 FOR rg_csr_6002 IN csr_6002(l_payroll_action_id, rg_csr_6000.action_information5,rg_csr_5000.action_information25
838 ,rg_csr_5000.action_information9,rg_csr_5000.action_information24,rg_csr_5000.action_information8) /* 9489806 8847591 */
839 LOOP
840 --fnd_file.put_line(fnd_file.log,'10');
841
842 gtagdata(l_counter).TagName := 'REC_6002';
843 gtagdata(l_counter).TagValue := 'REC_6002';
844 l_counter := l_counter + 1;
845
846 /* line num */
847 gtagdata(l_counter).TagName := 'RT6002_01';
848 gtagdata(l_counter).TagValue := rg_csr_6002.action_information30;
849 l_line_num := to_number(rg_csr_6002.action_information30);
850 l_counter := l_counter + 1;
851
852 /*Rec num */
853 gtagdata(l_counter).TagName := 'RT6002_02';
854 gtagdata(l_counter).TagValue := rg_csr_6002.action_information3;
855 l_counter := l_counter + 1;
856
857 -- Field num
858 gtagdata(l_counter).TagName := 'RT6002_03';
859 gtagdata(l_counter).TagValue := rg_csr_6002.action_information6;
860 l_counter := l_counter + 1;
861
862 -- Code Field
863 gtagdata(l_counter).TagName := 'RT6002_04';
864 gtagdata(l_counter).TagValue := rg_csr_6002.action_information7;
865 l_counter := l_counter + 1;
866
867 gtagdata(l_counter).TagName := 'REC_6002';
868 gtagdata(l_counter).TagValue := 'REC_6002_END';
869 l_counter := l_counter + 1;
870
871 END LOOP;
872
873 FOR rg_csr_6003 IN csr_6003(l_payroll_action_id, rg_csr_6000.action_information5,rg_csr_5000.action_information25
874 ,rg_csr_5000.action_information9,rg_csr_5000.action_information24,rg_csr_5000.action_information8) /* 9489806 8847591 */
875 LOOP
876 --fnd_file.put_line(fnd_file.log,'10');
877
878 gtagdata(l_counter).TagName := 'REC_6003';
879 gtagdata(l_counter).TagValue := 'REC_6003';
880 l_counter := l_counter + 1;
881
882 /* line num */
883 gtagdata(l_counter).TagName := 'RT6003_01';
884 gtagdata(l_counter).TagValue := rg_csr_6003.action_information30;
885 l_line_num := to_number(rg_csr_6003.action_information30);
886 l_counter := l_counter + 1;
887
888 /*Rec num */
889 gtagdata(l_counter).TagName := 'RT6003_02';
890 gtagdata(l_counter).TagValue := rg_csr_6003.action_information3;
891 l_counter := l_counter + 1;
892
893 -- Field num
894 gtagdata(l_counter).TagName := 'RT6003_03';
895 gtagdata(l_counter).TagValue := rg_csr_6003.action_information6;
896 l_counter := l_counter + 1;
897
898 -- Code Field
899 gtagdata(l_counter).TagName := 'RT6003_04';
900 gtagdata(l_counter).TagValue := rg_csr_6003.action_information7;
901 l_counter := l_counter + 1;
902
903 gtagdata(l_counter).TagName := 'REC_6003';
904 gtagdata(l_counter).TagValue := 'REC_6003_END';
905 l_counter := l_counter + 1;
906
907 END LOOP;
908
909 -- Record 6004
910 FOR rg_csr_6004 IN csr_6004(l_payroll_action_id, rg_csr_6000.action_information5,rg_csr_5000.action_information25
911 ,rg_csr_5000.action_information9,rg_csr_5000.action_information24,rg_csr_5000.action_information8) /* 9489806 8847591 */
912 LOOP
913 --fnd_file.put_line(fnd_file.log,'11');
914
915 gtagdata(l_counter).TagName := 'REC_6004';
916 gtagdata(l_counter).TagValue := 'REC_6004';
917 l_counter := l_counter + 1;
918
919
920 /* line num */
921 gtagdata(l_counter).TagName := 'RT6004_01';
922 gtagdata(l_counter).TagValue := rg_csr_6004.action_information30;
923 l_line_num := to_number(rg_csr_6004.action_information30);
924 l_counter := l_counter + 1;
928 gtagdata(l_counter).TagValue := rg_csr_6004.action_information3;
925
926 /*Rec num */
927 gtagdata(l_counter).TagName := 'RT6004_02';
929 l_counter := l_counter + 1;
930
931 -- Field num
932 gtagdata(l_counter).TagName := 'RT6004_03';
933 gtagdata(l_counter).TagValue := rg_csr_6004.action_information6;
934 l_counter := l_counter + 1;
935
936 -- Code Field
937 gtagdata(l_counter).TagName := 'RT6004_04';
938 gtagdata(l_counter).TagValue := rg_csr_6004.action_information7;
939 l_counter := l_counter + 1;
940
941 gtagdata(l_counter).TagName := 'REC_6004';
942 gtagdata(l_counter).TagValue := 'REC_6004_END';
943 l_counter := l_counter + 1;
944
945 END LOOP;
946
947 -- Record 6005
948 FOR rg_csr_6005 IN csr_6005(l_payroll_action_id, rg_csr_6000.action_information5,rg_csr_5000.action_information25
949 ,rg_csr_5000.action_information9,rg_csr_5000.action_information24,rg_csr_5000.action_information8) /* 9489806 8847591 */
950 LOOP
951
952 gtagdata(l_counter).TagName := 'REC_6005';
953 gtagdata(l_counter).TagValue := 'REC_6005';
954 l_counter := l_counter + 1;
955
956 /* line num */
957 gtagdata(l_counter).TagName := 'RT6005_01';
958 gtagdata(l_counter).TagValue := rg_csr_6005.action_information30;
959 l_line_num := to_number(rg_csr_6005.action_information30);
960 l_counter := l_counter + 1;
961
962 /*Rec num */
963 gtagdata(l_counter).TagName := 'RT6005_02';
964 gtagdata(l_counter).TagValue := rg_csr_6005.action_information3;
965 l_counter := l_counter + 1;
966
967 -- Field num
968 gtagdata(l_counter).TagName := 'RT6005_03';
969 gtagdata(l_counter).TagValue := rg_csr_6005.action_information6;
970 l_counter := l_counter + 1;
971
972 -- HOURS/Days worked
973 gtagdata(l_counter).TagName := 'RT6005_04';
974 gtagdata(l_counter).TagValue := lpad(rg_csr_6005.action_information7,8,'0');
975 l_counter := l_counter + 1;
976
977 -- Sign
978 gtagdata(l_counter).TagName := 'RT6005_05';
979 gtagdata(l_counter).TagValue := rg_csr_6005.action_information8;
980 l_counter := l_counter + 1;
981
982 gtagdata(l_counter).TagName := 'REC_6005';
983 gtagdata(l_counter).TagValue := 'REC_6005_END';
984 l_counter := l_counter + 1;
985
986 END LOOP;
987
988 --13463885
989 -- Record 6102
990 FOR rg_csr_6102 IN csr_6102(l_payroll_action_id, rg_csr_6000.action_information5,rg_csr_5000.action_information25
991 ,rg_csr_5000.action_information9,rg_csr_5000.action_information24,rg_csr_5000.action_information8) /* 9489806 8847591 */
992 LOOP
993
994 gtagdata(l_counter).TagName := 'REC_6102';
995 gtagdata(l_counter).TagValue := 'REC_6102';
996 l_counter := l_counter + 1;
997
998 /* line num */
999 gtagdata(l_counter).TagName := 'RT6102_01';
1000 gtagdata(l_counter).TagValue := rg_csr_6102.action_information30;
1001 l_line_num := to_number(rg_csr_6102.action_information30);
1002 l_counter := l_counter + 1;
1003
1004 /*Rec num */
1005 gtagdata(l_counter).TagName := 'RT6102_02';
1006 gtagdata(l_counter).TagValue := rg_csr_6102.action_information3;
1007 l_counter := l_counter + 1;
1008
1009 -- Net Holiday Pay
1010 gtagdata(l_counter).TagName := 'RT6102_03';
1011 gtagdata(l_counter).TagValue := rg_csr_6102.action_information7;
1012 l_counter := l_counter + 1;
1013
1014 -- Sign
1015 gtagdata(l_counter).TagName := 'RT6102_04';
1016 gtagdata(l_counter).TagValue := rg_csr_6102.action_information8;
1017 l_counter := l_counter + 1;
1018
1019 -- Number of Holidays
1020 gtagdata(l_counter).TagName := 'RT6102_05';
1021 gtagdata(l_counter).TagValue := rg_csr_6102.action_information9;
1022 l_counter := l_counter + 1;
1023
1024 -- Sign
1025 gtagdata(l_counter).TagName := 'RT6102_06';
1026 gtagdata(l_counter).TagValue := rg_csr_6102.action_information10;
1027 l_counter := l_counter + 1;
1028
1029 -- Year
1030 gtagdata(l_counter).TagName := 'RT6102_07';
1031 gtagdata(l_counter).TagValue := rg_csr_6102.action_information6;
1032 l_counter := l_counter + 1;
1033
1034 -- Termination Date
1035 gtagdata(l_counter).TagName := 'RT6102_08';
1036 gtagdata(l_counter).TagValue := rg_csr_6102.action_information11;
1037 l_counter := l_counter + 1;
1038
1039 gtagdata(l_counter).TagName := 'REC_6102';
1040 gtagdata(l_counter).TagValue := 'REC_6102_END';
1041 l_counter := l_counter + 1;
1042
1043 END LOOP;
1044 --13463885
1045
1046 gtagdata(l_counter).TagName := 'REC_6000';
1047 gtagdata(l_counter).TagValue := 'REC_6000_END';
1048 l_counter := l_counter + 1;
1049
1050
1051
1052 END LOOP; /* end record 6000 */
1053 --Raji
1054 /* 9489806 */
1055 IF(l_flag='Y') THEN
1056 gtagdata(l_counter).TagName := 'REC_5000';
1057 gtagdata(l_counter).TagValue := 'REC_5000_END';
1058 l_counter := l_counter + 1;
1059 l_flag:='N';
1060 END IF;
1061 l_ctr := 1;
1062 FOR rg_csr_6001_corr IN csr_6001_corr(l_payroll_action_id,rg_csr_5000.action_information25
1063 ,rg_csr_5000.action_information9,rg_csr_5000.action_information24,rg_csr_5000.action_information8,rg_csr_2001.action_information4) /* 8847591 */
1064 LOOP
1065 IF l_ctr = 1 THEN
1069 FETCH csr_5000R INTO rg_csr_5000;
1066 -- make 5000 record
1067 OPEN csr_5000R(l_payroll_action_id,rg_csr_5000.action_information25
1068 ,rg_csr_5000.action_information9,rg_csr_5000.action_information24,rg_csr_5000.action_information8,rg_csr_2001.action_information4); /* 8847591 */
1070 CLOSE csr_5000R;
1071 gtagdata(l_counter).TagName := 'REC_5000';
1072 gtagdata(l_counter).TagValue := 'REC_5000';
1073 l_counter := l_counter + 1;
1074
1075 --fnd_file.put_line(fnd_file.log,'7');
1076
1077 /* line num */
1078 l_line_num := l_line_num + 1;
1079 gtagdata(l_counter).TagName := 'RT5000_01';
1080 gtagdata(l_counter).TagValue :=rg_csr_5000.action_information30;
1081 --lpad(to_char(l_line_num),7,'0');
1082 l_counter := l_counter + 1;
1083
1084 /*Rec num */
1085 gtagdata(l_counter).TagName := 'RT5000_02';
1086 gtagdata(l_counter).TagValue := rg_csr_5000.action_information3;
1087 l_counter := l_counter + 1;
1088
1089 gtagdata(l_counter).TagName := 'RT5000_03';
1090 gtagdata(l_counter).TagValue := 'R'; -- Correction Record
1091 l_counter := l_counter + 1;
1092
1093 gtagdata(l_counter).TagName := 'RT5000_04';
1094 gtagdata(l_counter).TagValue := rg_csr_5000.action_information10;
1095 /* rpad(substr(rg_csr_5000.action_information10,1, 8) ||
1096 l_sender_id, 16, ' '); bug fix 7579265 */
1097 l_counter := l_counter + 1;
1098
1099 gtagdata(l_counter).TagName := 'RT5000_05';
1100 gtagdata(l_counter).TagValue := ''; -- 16 spaces
1101 l_counter := l_counter + 1;
1102
1103 /*Pay period start date*/
1104 gtagdata(l_counter).TagName := 'RT5000_06';
1105 gtagdata(l_counter).TagValue := rg_csr_5000.action_information4;
1106 l_counter := l_counter + 1;
1107
1108 /*period end date*/
1109 gtagdata(l_counter).TagName := 'RT5000_07';
1110 gtagdata(l_counter).TagValue := rg_csr_5000.action_information5;
1111 l_counter := l_counter + 1;
1112
1113 /*Disposal date*/
1114 gtagdata(l_counter).TagName := 'RT5000_08';
1115 gtagdata(l_counter).TagValue := rg_csr_5000.action_information6;
1116 l_counter := l_counter + 1;
1117
1118 gtagdata(l_counter).TagName := 'RT5000_09';
1119 gtagdata(l_counter).TagValue := ''; -- 1 space
1120 l_counter := l_counter + 1;
1121
1122 /*Payment*/
1123 gtagdata(l_counter).TagName := 'RT5000_10';
1124 gtagdata(l_counter).TagValue := rg_csr_5000.action_information7;
1125 l_counter := l_counter + 1;
1126
1127 /*Greenland code*/
1128 gtagdata(l_counter).TagName := 'RT5000_11';
1129 gtagdata(l_counter).TagValue := rg_csr_5000.action_information8;
1130 l_counter := l_counter + 1;
1131
1132 /*Employment code*/
1133 gtagdata(l_counter).TagName := 'RT5000_12';
1134 gtagdata(l_counter).TagValue := rg_csr_5000.action_information9;
1135 l_counter := l_counter + 1;
1136 END IF;
1137 -- make 6000 record
1138 FOR rg_csr_6000 IN csr_6000_corr(l_payroll_action_id, rg_csr_6001_corr.action_information5,rg_csr_5000.action_information25
1139 ,rg_csr_5000.action_information9,rg_csr_5000.action_information24,rg_csr_5000.action_information8,rg_csr_2001.action_information4) /* 8847591 */
1140 LOOP
1141
1142 --fnd_file.put_line(fnd_file.log,'8');
1143
1144 gtagdata(l_counter).TagName := 'REC_6000';
1145 gtagdata(l_counter).TagValue := 'REC_6000';
1146 l_counter := l_counter + 1;
1147
1148 /* line num */
1149 l_line_num := l_line_num + 1;
1150 gtagdata(l_counter).TagName := 'RT6000_01';
1151 gtagdata(l_counter).TagValue := rg_csr_6000.action_information30;
1152 --lpad(to_char(l_line_num),7,'0');
1153 l_counter := l_counter + 1;
1154
1155 /*Rec num */
1156 gtagdata(l_counter).TagName := 'RT6000_02';
1157 gtagdata(l_counter).TagValue := rg_csr_6000.action_information3;
1158 l_counter := l_counter + 1;
1159
1160 -- CVR num
1161 gtagdata(l_counter).TagName := 'RT6000_03';
1162 gtagdata(l_counter).TagValue := rg_csr_6000.action_information6;
1163 l_counter := l_counter + 1;
1164
1165 -- CPR
1166 gtagdata(l_counter).TagName := 'RT6000_04';
1167 gtagdata(l_counter).TagValue := rg_csr_6000.action_information7;
1168 l_counter := l_counter + 1;
1169
1170 -- Numeric Filler
1171 gtagdata(l_counter).TagName := 'RT6000_05';
1172 gtagdata(l_counter).TagValue := lpad('0',8,'0'); -- 8 zeros
1173 l_counter := l_counter + 1;
1174
1175 /*Assignment num*/
1176 gtagdata(l_counter).TagName := 'RT6000_06';
1177 gtagdata(l_counter).TagValue := rg_csr_6000.action_information8;
1178 l_counter := l_counter + 1;
1179
1180 gtagdata(l_counter).TagName := 'RT6000_07';
1181 gtagdata(l_counter).TagValue := ''; -- 1 space
1182 l_counter := l_counter + 1;
1183
1184 gtagdata(l_counter).TagName := 'RT6000_08';
1185 gtagdata(l_counter).TagValue := lpad('0',3,'0'); -- 3 zeros
1186 l_counter := l_counter + 1;
1187
1188 gtagdata(l_counter).TagName := 'RT6000_09';
1189 gtagdata(l_counter).TagValue := ''; -- 25 spaces
1190 l_counter := l_counter + 1;
1191
1192 /*Code 68*/
1196
1193 gtagdata(l_counter).TagName := 'RT6000_10';
1194 gtagdata(l_counter).TagValue := rg_csr_6000.action_information9;
1195 l_counter := l_counter + 1;
1197 /*PU code*/
1198 gtagdata(l_counter).TagName := 'RT6000_11';
1199 gtagdata(l_counter).TagValue := rg_csr_6000.action_information10;
1200 l_counter := l_counter + 1;
1201 -- record 8001
1202 /* 9587046R */
1203 FOR rg_csr_8001 IN csr_8001R(l_payroll_action_id, rg_csr_6000.action_information5,rg_csr_5000.action_information25
1204 ,rg_csr_5000.action_information9,rg_csr_5000.action_information24,rg_csr_5000.action_information8) /* 8847591 */
1205 LOOP
1206 gtagdata(l_counter).TagName := 'REC_8001';
1207 gtagdata(l_counter).TagValue := 'REC_8001';
1208 l_counter := l_counter + 1;
1209
1210 /* line num */
1211 l_line_num := l_line_num + 1;
1212 gtagdata(l_counter).TagName := 'RT8001_01';
1213 gtagdata(l_counter).TagValue := rg_csr_8001.action_information30;
1214 --lpad(to_char(l_line_num),7,'0');
1215 l_counter := l_counter + 1;
1216
1217 /*Rec num */
1218 gtagdata(l_counter).TagName := 'RT8001_02';
1219 gtagdata(l_counter).TagValue := rg_csr_8001.action_information3;
1220 l_counter := l_counter + 1;
1221
1222 /*Birthday */
1223 gtagdata(l_counter).TagName := 'RT8001_03';
1224 gtagdata(l_counter).TagValue := rg_csr_8001.action_information6;
1225 l_counter := l_counter + 1;
1226
1227 /*Person gender*/
1228 gtagdata(l_counter).TagName := 'RT8001_04';
1229 gtagdata(l_counter).TagValue := rg_csr_8001.action_information7;
1230 l_counter := l_counter + 1;
1231
1232 /*Country*/
1233 gtagdata(l_counter).TagName := 'RT8001_05';
1234 gtagdata(l_counter).TagValue := rg_csr_8001.action_information8;
1235 l_counter := l_counter + 1;
1236
1237 /*Person Name */
1238 gtagdata(l_counter).TagName := 'RT8001_06';
1239 gtagdata(l_counter).TagValue := rg_csr_8001.action_information9;
1240 l_counter := l_counter + 1;
1241
1242 /*Address*/
1243 gtagdata(l_counter).TagName := 'RT8001_07';
1244 gtagdata(l_counter).TagValue := rg_csr_8001.action_information10;
1245 l_counter := l_counter + 1;
1246
1247 /*Postal code*/
1248 gtagdata(l_counter).TagName := 'RT8001_08';
1249 gtagdata(l_counter).TagValue := rg_csr_8001.action_information11;
1250 l_counter := l_counter + 1;
1251
1252 /*Town*/
1253 gtagdata(l_counter).TagName := 'RT8001_09';
1254 gtagdata(l_counter).TagValue := rg_csr_8001.action_information12;
1255 l_counter := l_counter + 1;
1256
1257 gtagdata(l_counter).TagName := 'REC_8001';
1258 gtagdata(l_counter).TagValue := 'REC_8001_END';
1259 l_counter := l_counter + 1;
1260 END LOOP;
1261
1262 END LOOP;
1263
1264 FOR rg_csr_6001 IN csr_6001_corr_asst
1265 (l_payroll_action_id, rg_csr_6001_corr.action_information5,rg_csr_5000.action_information25
1266 ,rg_csr_5000.action_information9,rg_csr_5000.action_information24,rg_csr_5000.action_information8) /* 8847591 */
1267 LOOP
1268 --make 6001 record
1269 gtagdata(l_counter).TagName := 'REC_6001';
1270 gtagdata(l_counter).TagValue := 'REC_6001';
1271 l_counter := l_counter + 1;
1272
1273 /* line num */
1274 l_line_num := l_line_num + 1;
1275 gtagdata(l_counter).TagName := 'RT6001_01';
1276 gtagdata(l_counter).TagValue := rg_csr_6001.action_information30;
1277 --lpad(to_char(l_line_num),7,'0');
1278 l_counter := l_counter + 1;
1279
1280 /*Rec num */
1281 gtagdata(l_counter).TagName := 'RT6001_02';
1282 gtagdata(l_counter).TagValue := rg_csr_6001.action_information3;
1283 l_counter := l_counter + 1;
1284
1285 -- Field num
1286 gtagdata(l_counter).TagName := 'RT6001_03';
1287 gtagdata(l_counter).TagValue := rg_csr_6001.action_information6;
1288 l_counter := l_counter + 1;
1289
1290 -- Amount
1291 gtagdata(l_counter).TagName := 'RT6001_04';
1292 gtagdata(l_counter).TagValue := rg_csr_6001.action_information7;
1293 l_counter := l_counter + 1;
1294
1295 -- Sign
1296 gtagdata(l_counter).TagName := 'RT6001_05';
1297 gtagdata(l_counter).TagValue := rg_csr_6001.action_information8;
1298 l_counter := l_counter + 1;
1299
1300 gtagdata(l_counter).TagName := 'REC_6001';
1301 gtagdata(l_counter).TagValue := 'REC_6001_END';
1302 l_counter := l_counter + 1;
1303
1304 END LOOP;
1305 gtagdata(l_counter).TagName := 'REC_6000';
1306 gtagdata(l_counter).TagValue := 'REC_6000_END';
1307 l_counter := l_counter + 1;
1308 l_ctr := l_ctr + 1;
1309 END LOOP;
1310 IF l_ctr > 1 THEN
1311 gtagdata(l_counter).TagName := 'REC_5000';
1312 gtagdata(l_counter).TagValue := 'REC_5000_END';
1313 l_counter := l_counter + 1;
1314 END IF;
1315 --Raji
1316 END LOOP; -- kal rec 5000
1317
1318 END IF;
1319
1320 gtagdata(l_counter).TagName := 'REC_2001';
1321 gtagdata(l_counter).TagValue := 'REC_2001_END';
1322 l_counter := l_counter + 1;
1323
1324 END LOOP ; -- rec 2000 10367494
1325 gtagdata(l_counter).TagName := 'REC_1000';
1326 gtagdata(l_counter).TagValue := 'REC_1000_END';
1327 l_counter := l_counter + 1;
1328
1329 gtagdata(l_counter).TagName := 'REC_9999';
1333 -- Record 9999
1330 gtagdata(l_counter).TagValue := 'REC_9999';
1331 l_counter := l_counter + 1;
1332
1334 OPEN csr_9999(l_payroll_action_id);
1335 FETCH csr_9999 INTO rg_csr_9999;
1336 CLOSE csr_9999;
1337
1338 /* line num */
1339 l_line_num := l_line_num + 1;
1340 gtagdata(l_counter).TagName := 'RT9999_01';
1341 gtagdata(l_counter).TagValue := lpad(to_char(l_line_num),7,'0');
1342 l_counter := l_counter + 1;
1343
1344 /*Rec num */
1345 gtagdata(l_counter).TagName := 'RT9999_02';
1346 gtagdata(l_counter).TagValue := rg_csr_9999.action_information3;
1347 l_counter := l_counter + 1;
1348
1349 -- Number of records
1350 gtagdata(l_counter).TagName := 'RT9999_03';
1351 gtagdata(l_counter).TagValue := lpad(to_char(l_line_num),7,'0');
1352 l_counter := l_counter + 1;
1353
1354 gtagdata(l_counter).TagName := 'REC_9999';
1355 gtagdata(l_counter).TagValue := 'REC_9999_END';
1356 l_counter := l_counter + 1;
1357
1358
1359
1360
1361 END LOOP;
1362
1363
1364
1365 hr_utility.set_location('After populating pl/sql table',30);
1366
1367
1368 WritetoCLOB (p_xml );
1369
1370
1371 END GET_DATA;
1372
1373 -----------------------------------------------------------------------------------------------------------------
1374 PROCEDURE WritetoCLOB(p_xfdf_clob out nocopy CLOB) is
1375 l_xfdf_string clob;
1376 l_str1 varchar2(1000);
1377 l_str2 varchar2(20);
1378 l_str3 varchar2(20);
1379 l_str4 varchar2(20);
1380 l_str5 varchar2(20);
1381 l_str6 varchar2(30);
1382 l_str7 varchar2(1000);
1383 l_str8 varchar2(240);
1384 l_str9 varchar2(240);
1385
1386 current_index pls_integer;
1387 l_IANA_charset VARCHAR2 (50);
1388
1389 BEGIN
1390 l_IANA_charset :=PAY_DK_GENERAL.get_IANA_charset ;
1391 hr_utility.set_location('Entering WritetoCLOB ',70);
1392 l_str1 := '<?xml version="1.0" encoding="'||l_IANA_charset||'"?> <ROOT><EINR>' ;
1393 l_str2 := '<';
1394 l_str3 := '>';
1395 l_str4 := '</';
1396 l_str5 := '>';
1397 l_str6 := '</EINR></ROOT>';
1398 l_str7 := '<?xml version="1.0" encoding="'||l_IANA_charset||'"?> <ROOT></ROOT>';
1399
1400 --fnd_file.put_line(fnd_file.log,'wc1');
1401
1402 dbms_lob.createtemporary(l_xfdf_string,FALSE,DBMS_LOB.CALL);
1403 dbms_lob.open(l_xfdf_string,dbms_lob.lob_readwrite);
1404
1405 current_index := 0;
1406
1407 --fnd_file.put_line(fnd_file.log,'wc2');
1408
1409 IF gtagdata.count > 0 THEN
1410
1411 dbms_lob.writeAppend( l_xfdf_string, length(l_str1), l_str1 );
1412
1413 --fnd_file.put_line(fnd_file.log,'wc3');
1414 FOR table_counter IN gtagdata.FIRST .. gtagdata.LAST LOOP
1415
1416 l_str8 := gtagdata(table_counter).TagName;
1417 l_str9 := gtagdata(table_counter).TagValue ;
1418
1419 IF l_str9 IN ('REC_1000','REC_1000_END','REC_2001','REC_2001_END','REC_2101','REC_2101_END',
1420 'REC_5000','REC_5000_END','REC_6000','REC_6000_END','REC_8001','REC_8001_END','REC_6001','REC_6001_END',
1421 'REC_6002','REC_6002_END','REC_6003','REC_6003_END','REC_6004','REC_6004_END','REC_6005','REC_6005_END','REC_6102','REC_6102_END','REC_9999','REC_9999_END') THEN --13463885
1422
1423 --fnd_file.put_line(fnd_file.log,'Processing '||l_str9);
1424 IF l_str9 IN ('REC_1000','REC_2001','REC_2101','REC_5000','REC_6000','REC_8001','REC_6001','REC_6002','REC_6003','REC_6004','REC_6005','REC_6102','REC_9999') THEN --13463885
1425 dbms_lob.writeAppend(l_xfdf_string, length(l_str2), l_str2);
1426 dbms_lob.writeAppend(l_xfdf_string, length(l_str8), l_str8);
1427 dbms_lob.writeAppend(l_xfdf_string, length(l_str3), l_str3);
1428 ELSE
1429 dbms_lob.writeAppend(l_xfdf_string, length(l_str4), l_str4);
1430 dbms_lob.writeAppend(l_xfdf_string, length(l_str8), l_str8);
1431 dbms_lob.writeAppend(l_xfdf_string, length(l_str5), l_str5);
1432 END IF;
1433
1434 ELSE
1435
1436 if l_str9 is not null then
1437 l_str9 := hr_dk_utility.REPLACE_SPECIAL_CHARS(l_str9);
1438
1439 dbms_lob.writeAppend(l_xfdf_string, length(l_str2), l_str2);
1440 dbms_lob.writeAppend(l_xfdf_string, length(l_str8), l_str8);
1441 dbms_lob.writeAppend(l_xfdf_string, length(l_str3), l_str3);
1442 dbms_lob.writeAppend(l_xfdf_string, length(l_str9), l_str9);
1443 dbms_lob.writeAppend(l_xfdf_string, length(l_str4), l_str4);
1444 dbms_lob.writeAppend(l_xfdf_string, length(l_str8), l_str8);
1445 dbms_lob.writeAppend(l_xfdf_string, length(l_str5), l_str5);
1446 else
1447
1448 dbms_lob.writeAppend(l_xfdf_string, length(l_str2), l_str2);
1449 dbms_lob.writeAppend(l_xfdf_string, length(l_str8), l_str8);
1450 dbms_lob.writeAppend(l_xfdf_string, length(l_str3), l_str3);
1451 dbms_lob.writeAppend(l_xfdf_string, length(l_str4), l_str4);
1452 dbms_lob.writeAppend(l_xfdf_string, length(l_str8), l_str8);
1453 dbms_lob.writeAppend(l_xfdf_string, length(l_str5), l_str5);
1454
1455 end if;
1456
1457 END IF;
1458
1459
1460 END LOOP;
1461
1462 dbms_lob.writeAppend(l_xfdf_string, length(l_str6), l_str6 );
1463
1464 ELSE
1465 dbms_lob.writeAppend(l_xfdf_string, length(l_str7), l_str7 );
1466 END IF;
1467
1468
1469 --fnd_file.put_line(fnd_file.log,'wc5');
1470 p_xfdf_clob := l_xfdf_string;
1471
1472 hr_utility.set_location('Leaving WritetoCLOB ',40);
1473
1474 EXCEPTION
1475 WHEN OTHERS then
1476 HR_UTILITY.TRACE('sqlerrm ' || SQLERRM);
1477 HR_UTILITY.RAISE_ERROR;
1478 END WritetoCLOB;
1479 -------------------------------------------------------------------------------------------------------------------------
1480 END PAY_DK_EINR;