[Home] [Help]
PACKAGE BODY: APPS.PAY_FI_DPSR
Source
1 PACKAGE BODY PAY_FI_DPSR AS
2 /* $Header: pyfidpsr.pkb 120.4.12010000.2 2009/03/10 06:42:07 rsengupt 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_payroll_action_id PAY_ACTION_INFORMATION. ACTION_INFORMATION1%TYPE;
16 /* End of declaration*/
17
18 /* Cursors */
19 /* Cursor to fetch data related to Record 1 of VSPSERIE*/
20 CURSOR csr_VSPSERIE1 (p_payroll_action_id NUMBER) IS
21 SELECT pai.*
22 FROM pay_action_information pai , pay_assignment_actions paa
23 WHERE paa.payroll_action_id = p_payroll_action_id
24 AND pai.action_context_id= paa.assignment_action_id
25 AND pai.action_context_type= 'AAP'
26 AND pai.action_information_category = 'EMEA REPORT INFORMATION'
27 AND pai.action_information1 = 'PYFIDPSA'
28 AND pai.action_information3 ='VSPSERIE'
29 AND action_information29 ='1'
30 ORDER BY pai.action_information2;
31
32 /* Cursor to fetch data related to Record 2 of VSPSERIE*/
33 CURSOR csr_VSPSERIE2 (p_payroll_action_id NUMBER, p_action_context_id NUMBER) IS
34 SELECT pai.*
35 FROM pay_action_information pai , pay_assignment_actions paa
36 WHERE paa.payroll_action_id = p_payroll_action_id
37 AND pai.action_context_id= paa.assignment_action_id
38 AND pai.action_context_type= 'AAP'
39 AND pai.action_information_category = 'EMEA REPORT INFORMATION'
40 AND pai.action_information1 = 'PYFIDPSA'
41 AND pai.action_information3 ='VSPSERIE'
42 AND action_information29 ='2'
43 AND pai.action_context_id = p_action_context_id
44 ORDER BY pai.action_information2;
45
46 rg_csr_VSPSERIE2 csr_VSPSERIE2%rowtype;
47
48
49 /* Cursor to fetch data related to Record VSRAERIE*/
50 CURSOR csr_VSRAERIE(p_payroll_action_id NUMBER) IS
51 SELECT pai.*
52 FROM pay_action_information pai , pay_assignment_actions paa
53 WHERE paa.payroll_action_id = p_payroll_action_id
54 AND pai.action_context_id= paa.assignment_action_id
55 AND pai.action_context_type= 'AAP'
56 AND pai.action_information_category = 'EMEA REPORT INFORMATION'
57 AND pai.action_information1 = 'PYFIDPSA'
58 AND pai.action_information3 ='VSRAERIE'
59 ORDER BY pai.action_information2;
60
61 /* Cursor to fetch data related to Record VSPSTUKI*/
62 CURSOR csr_VSPSTUKI(p_payroll_action_id NUMBER) IS
63 SELECT pai.*
64 FROM pay_action_information pai , pay_assignment_actions paa
65 WHERE paa.payroll_action_id = p_payroll_action_id
66 AND pai.action_context_id= paa.assignment_action_id
67 AND pai.action_context_type= 'AAP'
68 AND pai.action_information_category = 'EMEA REPORT INFORMATION'
69 AND pai.action_information1 = 'PYFIDPSA'
70 AND pai.action_information3 = 'VSPSTUKI'
71 ORDER BY pai.action_information2;
72
73 /* Cursor to fetch data related to Record VSPSVYSL*/
74 CURSOR csr_VSPSVYSL(p_payroll_action_id NUMBER) IS
75 SELECT *
76 FROM pay_action_information pai
77 WHERE pai.action_context_id= p_payroll_action_id
78 AND pai.action_context_type= 'PA'
79 AND pai.action_information_category = 'EMEA REPORT INFORMATION'
80 AND pai.action_information1 = 'PYFIDPSA'
81 AND pai.action_information2 = 'VSPSVYSL'
82 ORDER BY pai.action_information4;
83
84
85 /* Cursor to fetch data related to Record VSPSVYHT*/
86 CURSOR csr_VSPSVYHT(p_payroll_action_id NUMBER) IS
87 SELECT *
88 FROM pay_action_information pai
89 WHERE pai.action_context_id= p_payroll_action_id
90 AND pai.action_context_type= 'PA'
91 AND pai.action_information_category = 'EMEA REPORT INFORMATION'
92 AND pai.action_information1 = 'PYFIDPSA'
93 AND pai.action_information2 = 'VSPSVYHT';
94
95 /* End of Cursors */
96 BEGIN
97
98 IF p_payroll_action_id IS NULL THEN
99
100 BEGIN
101
102 SELECT payroll_action_id
103 INTO l_payroll_action_id
104 FROM pay_payroll_actions ppa,
105 fnd_conc_req_summary_v fcrs,
106 fnd_conc_req_summary_v fcrs1
107 WHERE fcrs.request_id = FND_GLOBAL.CONC_REQUEST_ID
108 AND fcrs.priority_request_id = fcrs1.priority_request_id
109 AND ppa.request_id between fcrs1.request_id and fcrs.request_id
110 AND ppa.request_id = fcrs1.request_id;
111
112 EXCEPTION
113 WHEN others THEN
114 NULL;
115 END ;
116
117 ELSE
118
119 l_payroll_action_id :=p_payroll_action_id;
120
121 END IF;
122
123 hr_utility.set_location('Entered Procedure GETDATA',10);
124
125 /* Pick up the data related to Record VSPSERIE*/
126 FOR rg_csr_VSPSERIE1 IN csr_VSPSERIE1 (l_payroll_action_id)
127 LOOP
128 OPEN csr_VSPSERIE2(p_payroll_action_id, rg_csr_VSPSERIE1.action_context_id);
129 FETCH csr_VSPSERIE2 INTO rg_csr_VSPSERIE2;
130 CLOSE csr_VSPSERIE2;
131
132 /*Record Id*/
133 gtagdata(l_counter).TagName := '000';
134 gtagdata(l_counter).TagValue := rg_csr_VSPSERIE1.action_information3;
135 l_counter := l_counter + 1;
136
137 /*Transaction Type*/
138 gtagdata(l_counter).TagName := '101';
139 gtagdata(l_counter).TagValue := rg_csr_VSPSERIE1.action_information5;
140 l_counter := l_counter + 1;
141
142 /*Payment Type*/
143 gtagdata(l_counter).TagName := '110';
144 gtagdata(l_counter).TagValue := rg_csr_VSPSERIE1.action_information4;
145 l_counter := l_counter + 1;
146
147 /*Filing year*/
148 gtagdata(l_counter).TagName := '109';
149 gtagdata(l_counter).TagValue := rg_csr_VSPSERIE1.action_information6;
150 l_counter := l_counter + 1;
151
152 /*Payer ID*/
153 gtagdata(l_counter).TagName := '102';
154 gtagdata(l_counter).TagValue := rg_csr_VSPSERIE1.action_information7;
155 l_counter := l_counter + 1;
156
157 /*Payee's ID*/
158 gtagdata(l_counter).TagName := '111';
159 gtagdata(l_counter).TagValue := rg_csr_VSPSERIE1.action_information8;
160 l_counter := l_counter + 1;
161
162 /*Amount of Payment*/
163 gtagdata(l_counter).TagName := '114';
164 gtagdata(l_counter).TagValue := FND_NUMBER.CANONICAL_TO_NUMBER(rg_csr_VSPSERIE1.action_information9);
165 l_counter := l_counter + 1;
166
167 /*Withhold tax*/
168 gtagdata(l_counter).TagName := '115';
169 gtagdata(l_counter).TagValue := FND_NUMBER.CANONICAL_TO_NUMBER(rg_csr_VSPSERIE1.action_information10);
170 l_counter := l_counter + 1;
171
172 IF rg_csr_VSPSERIE1.action_information4 IN ('P','1','5','P2','H','H2') THEN
173
174 /*Employees' statutory pension and unemployment insurance contributions*/
175 gtagdata(l_counter).TagName := '116';
176 gtagdata(l_counter).TagValue := FND_NUMBER.CANONICAL_TO_NUMBER(rg_csr_VSPSERIE1.action_information11);
177 l_counter := l_counter + 1;
178
179 /*Deduction prior to withhold tax*/
180 gtagdata(l_counter).TagName := '117';
181 gtagdata(l_counter).TagValue := FND_NUMBER.CANONICAL_TO_NUMBER(rg_csr_VSPSERIE1.action_information12);
182 l_counter := l_counter + 1;
183
184 /*Taxable car benefit*/
185 gtagdata(l_counter).TagName := '120';
186 gtagdata(l_counter).TagValue := FND_NUMBER.CANONICAL_TO_NUMBER(rg_csr_VSPSERIE1.action_information13);
187 l_counter := l_counter + 1;
188
189 /*Car Benefit Deduction made by employer*/
190 gtagdata(l_counter).TagName := '121';
191 gtagdata(l_counter).TagValue := FND_NUMBER.CANONICAL_TO_NUMBER(rg_csr_VSPSERIE2.action_information14);
192 l_counter := l_counter + 1;
193
194
195 /*Km according to driver's log*/
196 gtagdata(l_counter).TagName := '122';
197 gtagdata(l_counter).TagValue := FND_NUMBER.CANONICAL_TO_NUMBER(rg_csr_VSPSERIE1.action_information14);
198 l_counter := l_counter + 1;
199
200 -- changes 2009 Start
201 /*Car Bemefit , Age Category */
202 gtagdata(l_counter).TagName := '123';
203 gtagdata(l_counter).TagValue := rg_csr_VSPSERIE1.action_information15;
204 l_counter := l_counter + 1;
205
206 /*Full Car Benefit */
207 gtagdata(l_counter).TagName := '124';
208 gtagdata(l_counter).TagValue := rg_csr_VSPSERIE2.action_information16;
209 l_counter := l_counter + 1;
210
211 -- Changes 2009 End
212
213
214 /*Interest benefit from (accommodation) mortage*/
215 gtagdata(l_counter).TagName := '130';
216 gtagdata(l_counter).TagValue := FND_NUMBER.CANONICAL_TO_NUMBER(rg_csr_VSPSERIE1.action_information16);
217 l_counter := l_counter + 1;
218
219 /*Other taxable benefits*/
220 gtagdata(l_counter).TagName := '140';
221 gtagdata(l_counter).TagValue := FND_NUMBER.CANONICAL_TO_NUMBER(rg_csr_VSPSERIE1.action_information17);
222 l_counter := l_counter + 1;
223
224 /*Other benefits,Deduction made by Employer */
225 gtagdata(l_counter).TagName := '141';
226 gtagdata(l_counter).TagValue := FND_NUMBER.CANONICAL_TO_NUMBER(rg_csr_VSPSERIE2.action_information15);
227 l_counter := l_counter + 1;
228
229
230 /*Accommodation*/
231 gtagdata(l_counter).TagName := '142';
232 gtagdata(l_counter).TagValue := SUBSTR(rg_csr_VSPSERIE1.action_information18,1,1);
233 l_counter := l_counter + 1;
234
235 /*Phone*/
236 gtagdata(l_counter).TagName := '143';
237 gtagdata(l_counter).TagValue := SUBSTR(rg_csr_VSPSERIE1.action_information18,2,1);
238 l_counter := l_counter + 1;
239
240 /*Lunch*/
241 gtagdata(l_counter).TagName := '144';
242 gtagdata(l_counter).TagValue := SUBSTR(rg_csr_VSPSERIE1.action_information18,3,1);
243 l_counter := l_counter + 1;
244
245 /*Other benefit in kind*/
246 gtagdata(l_counter).TagName := '145';
247 gtagdata(l_counter).TagValue := SUBSTR(rg_csr_VSPSERIE1.action_information18,4,1);
248 l_counter := l_counter + 1;
249
250 /*Deduction made from lunch benefit is equivalent to the taxable value */
251 gtagdata(l_counter).TagName := '146';
252 gtagdata(l_counter).TagValue := SUBSTR(rg_csr_VSPSERIE1.action_information18,6,1);
253 l_counter := l_counter + 1;
254
255
256 /*Work-related Travel ticket */
257 gtagdata(l_counter).TagName := '147';
258 gtagdata(l_counter).TagValue := SUBSTR(rg_csr_VSPSERIE1.action_information18,5,1);
259 l_counter := l_counter + 1;
260
261 /*Tax free Daily allowances and meal compensations (etc.), total */
262 gtagdata(l_counter).TagName := '150';
263 gtagdata(l_counter).TagValue := FND_NUMBER.CANONICAL_TO_NUMBER(rg_csr_VSPSERIE1.action_information19);
264 l_counter := l_counter + 1;
265
266 /*Tax free Daily allowance (domestic)*/
267 gtagdata(l_counter).TagName := '151';
268 gtagdata(l_counter).TagValue := SUBSTR(rg_csr_VSPSERIE1.action_information20,1,1);
269 l_counter := l_counter + 1;
270
271 /*Tax free Half-day allowance*/
272 gtagdata(l_counter).TagName := '152';
273 gtagdata(l_counter).TagValue := SUBSTR(rg_csr_VSPSERIE1.action_information20,2,1);
274 l_counter := l_counter + 1;
275
276 /*Tax free Foreign daily allowance*/
277 gtagdata(l_counter).TagName := '153';
278 gtagdata(l_counter).TagValue := SUBSTR(rg_csr_VSPSERIE1.action_information20,3,1);
279 l_counter := l_counter + 1;
280
281 /*Tax free Meal compensation*/
282 gtagdata(l_counter).TagName := '154';
283 gtagdata(l_counter).TagValue := SUBSTR(rg_csr_VSPSERIE1.action_information20,4,1);
284 l_counter := l_counter + 1;
285
286 /*Tax-free mileage allowance, km total*/
287 gtagdata(l_counter).TagName := '155';
288 gtagdata(l_counter).TagValue := FND_NUMBER.CANONICAL_TO_NUMBER(rg_csr_VSPSERIE1.action_information21);
289 l_counter := l_counter + 1;
290
291 /*Tax-free mileage allowance, Euros total*/
292 gtagdata(l_counter).TagName := '156';
293 gtagdata(l_counter).TagValue := FND_NUMBER.CANONICAL_TO_NUMBER(rg_csr_VSPSERIE1.action_information22);
294 l_counter := l_counter + 1;
295
296 /*Taxable Compensations from expenses*/
297 gtagdata(l_counter).TagName := '157';
298 gtagdata(l_counter).TagValue := FND_NUMBER.CANONICAL_TO_NUMBER(rg_csr_VSPSERIE2.action_information9);
299 l_counter := l_counter + 1;
300
301 /*Payments for elected official in a (communal position of trust) */
302 gtagdata(l_counter).TagName := '160';
303 gtagdata(l_counter).TagValue := FND_NUMBER.CANONICAL_TO_NUMBER(rg_csr_VSPSERIE2.action_information10);
304 l_counter := l_counter + 1;
305
306
307 /*Benefit from Employer stock option schemes*/
308 gtagdata(l_counter).TagName := '135';
309 gtagdata(l_counter).TagValue := FND_NUMBER.CANONICAL_TO_NUMBER(rg_csr_VSPSERIE1.action_information23);
310 l_counter := l_counter + 1;
311
312 /*Pension insurance payments paid by Employer*/
313 gtagdata(l_counter).TagName := '180';
314 gtagdata(l_counter).TagValue := FND_NUMBER.CANONICAL_TO_NUMBER(rg_csr_VSPSERIE1.action_information24);
315 l_counter := l_counter + 1;
316
317 /*Voluntary pension insurance payments paid by Employee */
318 gtagdata(l_counter).TagName := '181';
319 gtagdata(l_counter).TagValue := FND_NUMBER.CANONICAL_TO_NUMBER(rg_csr_VSPSERIE2.action_information11);
320 l_counter := l_counter + 1;
321
322 /*Voluntary pension insurance payments paid by Employee*/
323 gtagdata(l_counter).TagName := '182';
324 gtagdata(l_counter).TagValue := FND_NUMBER.CANONICAL_TO_NUMBER(rg_csr_VSPSERIE2.action_information12);
325 l_counter := l_counter + 1;
326
327
328 /*Salary payments not subject to Social insurance daily allowance fee*/
329 gtagdata(l_counter).TagName := '136';
330 gtagdata(l_counter).TagValue := FND_NUMBER.CANONICAL_TO_NUMBER(rg_csr_VSPSERIE1.action_information27);
331 l_counter := l_counter + 1;
332
333 /*Work-related Travel ticket as benefit in kind*/
334 gtagdata(l_counter).TagName := '148';
335 gtagdata(l_counter).TagValue := FND_NUMBER.CANONICAL_TO_NUMBER(rg_csr_VSPSERIE1.action_information26);
336 l_counter := l_counter + 1;
337
338 END IF;
339
340 /*End Code*/
341 gtagdata(l_counter).TagName := '999';
342 gtagdata(l_counter).TagValue := rg_csr_VSPSERIE1.action_information30;
343 l_counter := l_counter + 1;
344
345 END LOOP;
346
347 /* Pick up the data related to Record VSRAERIE*/
348 FOR rg_csr_VSRAERIE IN csr_VSRAERIE(l_payroll_action_id)
349 LOOP
350 /*Record Id*/
351 gtagdata(l_counter).TagName := '000';
352 gtagdata(l_counter).TagValue := rg_csr_VSRAERIE.action_information3;
353 l_counter := l_counter + 1;
354
355 /*Transaction Type*/
356 gtagdata(l_counter).TagName := '301';
357 gtagdata(l_counter).TagValue := rg_csr_VSRAERIE.action_information5;
358 l_counter := l_counter + 1;
359
360 /*Payment Type*/
361 gtagdata(l_counter).TagName := '316';
362 gtagdata(l_counter).TagValue := rg_csr_VSRAERIE.action_information4;
363 l_counter := l_counter + 1;
364
365 /*Filing year*/
366 gtagdata(l_counter).TagName := '303';
367 gtagdata(l_counter).TagValue := rg_csr_VSRAERIE.action_information6;
368 l_counter := l_counter + 1;
369
370 /*Payer ID*/
371 gtagdata(l_counter).TagName := '302';
372 gtagdata(l_counter).TagValue := rg_csr_VSRAERIE.action_information7;
373 l_counter := l_counter + 1;
374
375 /*Payee's ID*/
376 gtagdata(l_counter).TagName := '312';
377 gtagdata(l_counter).TagValue := rg_csr_VSRAERIE.action_information8;
378 l_counter := l_counter + 1;
379
380 /*Payee's last name*/
381 gtagdata(l_counter).TagName := '307';
382 gtagdata(l_counter).TagValue := rg_csr_VSRAERIE.action_information9;
383 l_counter := l_counter + 1;
384
385 /*Payee's First name(s)*/
386 gtagdata(l_counter).TagName := '308';
387 gtagdata(l_counter).TagValue := rg_csr_VSRAERIE.action_information10;
388 l_counter := l_counter + 1;
389
390 /*Visiting address in country of permanent residence (home country)*/
391 gtagdata(l_counter).TagName := '309';
392 gtagdata(l_counter).TagValue := rg_csr_VSRAERIE.action_information11;
393 l_counter := l_counter + 1;
394
395 /*Postal code in country of permanent residence (home country)*/
396 gtagdata(l_counter).TagName := '310';
397 gtagdata(l_counter).TagValue := rg_csr_VSRAERIE.action_information12;
398 l_counter := l_counter + 1;
399
400 /*City/County or equivalent country of permanent residence*/
401 gtagdata(l_counter).TagName := '311';
402 gtagdata(l_counter).TagValue := rg_csr_VSRAERIE.action_information13;
403 l_counter := l_counter + 1;
404
405 /*Foreign Personal ID */
406 gtagdata(l_counter).TagName := '313';
407 gtagdata(l_counter).TagValue := rg_csr_VSRAERIE.action_information25;
408 l_counter := l_counter + 1;
409
410
411 /*Country code of home country (ISO 3166)*/
412 gtagdata(l_counter).TagName := '341';
413 gtagdata(l_counter).TagValue := rg_csr_VSRAERIE.action_information14;
414 l_counter := l_counter + 1;
415
416 /*Name of home country*/
417 gtagdata(l_counter).TagName := '342';
418 gtagdata(l_counter).TagValue := rg_csr_VSRAERIE.action_information24;
419 l_counter := l_counter + 1;
420
421 /*Gross pay amount, as the basis for the tax at source*/
422 gtagdata(l_counter).TagName := '317';
423 gtagdata(l_counter).TagValue := FND_NUMBER.CANONICAL_TO_NUMBER(rg_csr_VSRAERIE.action_information15);
424 l_counter := l_counter + 1;
425
426 /*Amount of tax at source deducted*/
427 gtagdata(l_counter).TagName := '318';
428 gtagdata(l_counter).TagValue := FND_NUMBER.CANONICAL_TO_NUMBER(rg_csr_VSRAERIE.action_information16);
429 l_counter := l_counter + 1;
430
431 IF rg_csr_VSRAERIE.action_information4 IN ('A1','A2','A4','A5','A6','A7') THEN
432
433 /*Salary in money, cash*/
434 gtagdata(l_counter).TagName := '319';
435 gtagdata(l_counter).TagValue := FND_NUMBER.CANONICAL_TO_NUMBER(rg_csr_VSRAERIE.action_information17);
436 l_counter := l_counter + 1;
437
438 /*Benefit in kind*/
439 gtagdata(l_counter).TagName := '320';
440 gtagdata(l_counter).TagValue := FND_NUMBER.CANONICAL_TO_NUMBER(rg_csr_VSRAERIE.action_information18);
441 l_counter := l_counter + 1;
442
443 /*Tax at source deduction*/
444 gtagdata(l_counter).TagName := '321';
445 gtagdata(l_counter).TagValue := FND_NUMBER.CANONICAL_TO_NUMBER(rg_csr_VSRAERIE.action_information19);
446 l_counter := l_counter + 1;
447
448 /*Daily allowances and expenses compensations*/
449 gtagdata(l_counter).TagName := '322';
450 gtagdata(l_counter).TagValue := FND_NUMBER.CANONICAL_TO_NUMBER(rg_csr_VSRAERIE.action_information20);
451 l_counter := l_counter + 1;
452
453 /*Social security fee */
454 gtagdata(l_counter).TagName := '324';
455 gtagdata(l_counter).TagValue := FND_NUMBER.CANONICAL_TO_NUMBER(rg_csr_VSRAERIE.action_information23);
456 l_counter := l_counter + 1;
457
458 -- Changes 2009 Start
459
460 /*Bank Account Number */
461 gtagdata(l_counter).TagName := '325';
462 gtagdata(l_counter).TagValue := rg_csr_VSRAERIE.action_information26;
463 l_counter := l_counter + 1;
464
465 -- Changes 2009 End
466
467
468
469 END IF;
470 /*Contact person*/
471 gtagdata(l_counter).TagName := '305';
472 gtagdata(l_counter).TagValue := rg_csr_VSRAERIE.action_information21;
473 l_counter := l_counter + 1;
474
475 /*Contact person: Telephone Number*/
476 gtagdata(l_counter).TagName := '336';
477 gtagdata(l_counter).TagValue := rg_csr_VSRAERIE.action_information22;
478 l_counter := l_counter + 1;
479
480
481 /*End Code*/
482 gtagdata(l_counter).TagName := '999';
483 gtagdata(l_counter).TagValue := rg_csr_VSRAERIE.action_information30;
484 l_counter := l_counter + 1;
485
486 END LOOP;
487
488
489 /* Pick up the data related to Record VSPSTUKI*/
490 FOR rg_csr_VSPSTUKI IN csr_VSPSTUKI(l_payroll_action_id)
491 LOOP
492 /*Record Id*/
493 gtagdata(l_counter).TagName := '000';
494 gtagdata(l_counter).TagValue := rg_csr_VSPSTUKI.action_information3;
495 l_counter := l_counter + 1;
496
497 /*Transaction Type*/
498 gtagdata(l_counter).TagName := '019';
499 gtagdata(l_counter).TagValue := rg_csr_VSPSTUKI.action_information5;
500 l_counter := l_counter + 1;
501
502 /*Tax Type*/
503 gtagdata(l_counter).TagName := '010';
504 gtagdata(l_counter).TagValue := rg_csr_VSPSTUKI.action_information4;
505 l_counter := l_counter + 1;
506
507 /*Filing year*/
508 gtagdata(l_counter).TagName := '602';
509 gtagdata(l_counter).TagValue := rg_csr_VSPSTUKI.action_information6;
510 l_counter := l_counter + 1;
511
512 /*Payer ID*/
513 gtagdata(l_counter).TagName := '002';
514 gtagdata(l_counter).TagValue := rg_csr_VSPSTUKI.action_information7;
515 l_counter := l_counter + 1;
516
517 /*Payee ID*/
518 gtagdata(l_counter).TagName := '011';
519 gtagdata(l_counter).TagValue := rg_csr_VSPSTUKI.action_information8;
520 l_counter := l_counter + 1;
521
522 /*Payment month*/
523 gtagdata(l_counter).TagName := '013';
524 gtagdata(l_counter).TagValue := rg_csr_VSPSTUKI.action_information9;
525 l_counter := l_counter + 1;
526
527 /*Basis for the deduction*/
528 gtagdata(l_counter).TagName := '014';
529 gtagdata(l_counter).TagValue := FND_NUMBER.CANONICAL_TO_NUMBER(rg_csr_VSPSTUKI.action_information10);
530 l_counter := l_counter + 1;
531
532 /*Calculatory salary for the part-time retired employee*/
533 gtagdata(l_counter).TagName := '015';
534 gtagdata(l_counter).TagValue := FND_NUMBER.CANONICAL_TO_NUMBER(rg_csr_VSPSTUKI.action_information11);
535 l_counter := l_counter + 1;
536
537 /*Amount deducted as su bsidy of the low-paid*/
538 gtagdata(l_counter).TagName := '016';
539 gtagdata(l_counter).TagValue := FND_NUMBER.CANONICAL_TO_NUMBER(rg_csr_VSPSTUKI.action_information12);
540 l_counter := l_counter + 1;
541
542 /*Hours from months at work*/
543 gtagdata(l_counter).TagName := '017';
544 gtagdata(l_counter).TagValue := rg_csr_VSPSTUKI.action_information13;
545 l_counter := l_counter + 1;
546
547 /*Ending code*/
548 gtagdata(l_counter).TagName := '999';
549 gtagdata(l_counter).TagValue := rg_csr_VSPSTUKI.action_information30;
550 l_counter := l_counter + 1;
551
552 END LOOP;
553
554
555 /* Pick up the data related to Record VSPSVYSL*/
556 FOR rg_csr_VSPSVYSL IN csr_VSPSVYSL(l_payroll_action_id)
557 LOOP
558 /*Record Id*/
559 gtagdata(l_counter).TagName := '000';
560 gtagdata(l_counter).TagValue := rg_csr_VSPSVYSL.action_information2;
561 l_counter := l_counter + 1;
562
563 /*Transaction Type*/
564 gtagdata(l_counter).TagName := '611';
565 gtagdata(l_counter).TagValue := rg_csr_VSPSVYSL.action_information3;
566 l_counter := l_counter + 1;
567
568 /*Payment Type*/
569 gtagdata(l_counter).TagName := '606';
570 gtagdata(l_counter).TagValue := rg_csr_VSPSVYSL.action_information4;
571 l_counter := l_counter + 1;
572
573 /*Filing year*/
574 gtagdata(l_counter).TagName := '602';
575 gtagdata(l_counter).TagValue := rg_csr_VSPSVYSL.action_information5;
576 l_counter := l_counter + 1;
577
578 /*Payer ID*/
579 gtagdata(l_counter).TagName := '601';
580 gtagdata(l_counter).TagValue := rg_csr_VSPSVYSL.action_information6;
581 l_counter := l_counter + 1;
582
583 /*Total amount of payments*/
584 gtagdata(l_counter).TagName := '607';
585 gtagdata(l_counter).TagValue := FND_NUMBER.CANONICAL_TO_NUMBER(rg_csr_VSPSVYSL.action_information7);
586 l_counter := l_counter + 1;
587
588 /*Withhold tax/tax at source*/
589 gtagdata(l_counter).TagName := '608';
590 gtagdata(l_counter).TagValue := FND_NUMBER.CANONICAL_TO_NUMBER(rg_csr_VSPSVYSL.action_information8);
591 l_counter := l_counter + 1;
592
593 /*Number of records)*/
594 gtagdata(l_counter).TagName := '620';
595 gtagdata(l_counter).TagValue := rg_csr_VSPSVYSL.action_information9;
596 l_counter := l_counter + 1;
597
598 /*Ending code*/
599 gtagdata(l_counter).TagName := '999';
600 gtagdata(l_counter).TagValue := rg_csr_VSPSVYSL.action_information30;
601 l_counter := l_counter + 1;
602
603 END LOOP;
604
605 /* Pick up the data related to Record VSPSVYHT*/
606 FOR rg_csr_VSPSVYHT IN csr_VSPSVYHT(l_payroll_action_id)
607 LOOP
608 /*Record Id*/
609 gtagdata(l_counter).TagName := '000';
610 gtagdata(l_counter).TagValue := rg_csr_VSPSVYHT.action_information2;
611 l_counter := l_counter + 1;
612
613 /*Transaction Type*/
614 gtagdata(l_counter).TagName := '611';
615 gtagdata(l_counter).TagValue := rg_csr_VSPSVYHT.action_information3;
616 l_counter := l_counter + 1;
617
618 /*Filing year*/
619 gtagdata(l_counter).TagName := '602';
620 gtagdata(l_counter).TagValue := rg_csr_VSPSVYHT.action_information4;
621 l_counter := l_counter + 1;
622
623 /*Payer ID*/
624 gtagdata(l_counter).TagName := '601';
625 gtagdata(l_counter).TagValue := rg_csr_VSPSVYHT.action_information5;
626 l_counter := l_counter + 1;
627
628 /*Contact person*/
629 gtagdata(l_counter).TagName := '605';
630 gtagdata(l_counter).TagValue := rg_csr_VSPSVYHT.action_information6;
631 l_counter := l_counter + 1;
632
633 /*Contact Person Telephone Number*/
634 gtagdata(l_counter).TagName := '604';
635 gtagdata(l_counter).TagValue := rg_csr_VSPSVYHT.action_information7;
636 l_counter := l_counter + 1;
637
638 /*Number of summary records */
639 gtagdata(l_counter).TagName := '612';
640 gtagdata(l_counter).TagValue := rg_csr_VSPSVYHT.action_information8;
641 l_counter := l_counter + 1;
642
643 /*Benefits in kind */
644 gtagdata(l_counter).TagName := '615';
645 gtagdata(l_counter).TagValue := FND_NUMBER.CANONICAL_TO_NUMBER(rg_csr_VSPSVYHT.action_information9);
646 l_counter := l_counter + 1;
647
648 /*Employees' statutory pension and unemployment insurance contributions*/
649 gtagdata(l_counter).TagName := '640';
650 gtagdata(l_counter).TagValue := FND_NUMBER.CANONICAL_TO_NUMBER(rg_csr_VSPSVYHT.action_information10);
651 l_counter := l_counter + 1;
652
653 /*Deductions prior to withhold tax, total(pre tax deductions)*/
654 gtagdata(l_counter).TagName := '641';
655 gtagdata(l_counter).TagValue := FND_NUMBER.CANONICAL_TO_NUMBER(rg_csr_VSPSVYHT.action_information11);
656 l_counter := l_counter + 1;
657
658 /*Salaries have not been paid during filing year*/
659 gtagdata(l_counter).TagName := '613';
660 gtagdata(l_counter).TagValue := FND_NUMBER.CANONICAL_TO_NUMBER(rg_csr_VSPSVYHT.action_information12);
661 l_counter := l_counter + 1;
662
663 /*Deductions from social security fee*/
664 gtagdata(l_counter).TagName := '631';
665 gtagdata(l_counter).TagValue := FND_NUMBER.CANONICAL_TO_NUMBER(rg_csr_VSPSVYHT.action_information13);
666 l_counter := l_counter + 1;
667
668 /*Amount deducted from salaries subject to social insurance fee.*/
669 gtagdata(l_counter).TagName := '670';
670 gtagdata(l_counter).TagValue := FND_NUMBER.CANONICAL_TO_NUMBER(rg_csr_VSPSVYHT.action_information14);
671 l_counter := l_counter + 1;
672
673
674 /*Ending code*/
675 gtagdata(l_counter).TagName := '999';
676 gtagdata(l_counter).TagValue := rg_csr_VSPSVYHT.action_information30;
677 l_counter := l_counter + 1;
678
679 END LOOP;
680 hr_utility.set_location('After populating pl/sql table',30);
681
682
683 WritetoCLOB (p_xml );
684
685
686 END GET_DATA;
687
688 -----------------------------------------------------------------------------------------------------------------
689 PROCEDURE WritetoCLOB(p_xfdf_clob out nocopy CLOB) is
690 l_xfdf_string clob;
691 l_str1 varchar2(1000);
692 l_str2 varchar2(20);
693 l_str3 varchar2(20);
694 l_str4 varchar2(20);
695 l_str5 varchar2(20);
696 l_str6 varchar2(30);
697 l_str7 varchar2(1000);
698 l_str8 varchar2(240);
699 l_str9 varchar2(240);
700
701 current_index pls_integer;
702 l_IANA_charset VARCHAR2 (50);
703
704 BEGIN
705 l_IANA_charset :=hr_fi_utility.get_IANA_charset ;
706 hr_utility.set_location('Entering WritetoCLOB ',70);
707 l_str1 := '<?xml version="1.0" encoding="'||l_IANA_charset||'"?> <ROOT><DPSR>' ;
708 l_str2 := '<';
709 l_str3 := '>';
710 l_str4 := '</';
711 l_str5 := '>';
712 l_str6 := '</DPSR></ROOT>';
713 l_str7 := '<?xml version="1.0" encoding="'||l_IANA_charset||'"?> <ROOT></ROOT>';
714
715
716 dbms_lob.createtemporary(l_xfdf_string,FALSE,DBMS_LOB.CALL);
717 dbms_lob.open(l_xfdf_string,dbms_lob.lob_readwrite);
718
719 current_index := 0;
720
721 IF gtagdata.count > 0 THEN
722
723 dbms_lob.writeAppend( l_xfdf_string, length(l_str1), l_str1 );
724
725
726 FOR table_counter IN gtagdata.FIRST .. gtagdata.LAST LOOP
727
728 l_str8 := gtagdata(table_counter).TagName;
729 l_str9 := '<![CDATA[ '|| gtagdata(table_counter).TagValue ||' ]]>';
730
731 if l_str9 is not null then
732 dbms_lob.writeAppend(l_xfdf_string, length(l_str2), l_str2);
733 dbms_lob.writeAppend(l_xfdf_string, 11 , 'RECORD_DPSR');
734 dbms_lob.writeAppend(l_xfdf_string, length(l_str3), l_str3);
735
736 dbms_lob.writeAppend(l_xfdf_string, length(l_str2), l_str2);
737 dbms_lob.writeAppend(l_xfdf_string, 4, 'CODE');
738 dbms_lob.writeAppend(l_xfdf_string, length(l_str3), l_str3);
739 dbms_lob.writeAppend(l_xfdf_string, length(l_str8), l_str8);
740 dbms_lob.writeAppend(l_xfdf_string, length(l_str4), l_str4);
741 dbms_lob.writeAppend(l_xfdf_string, 4 , 'CODE');
742 dbms_lob.writeAppend(l_xfdf_string, length(l_str5), l_str5);
743
744 dbms_lob.writeAppend(l_xfdf_string, length(l_str2), l_str2);
745 dbms_lob.writeAppend(l_xfdf_string, 5, 'VALUE');
746 dbms_lob.writeAppend(l_xfdf_string, length(l_str3), l_str3);
747 dbms_lob.writeAppend(l_xfdf_string, length(l_str9), l_str9);
748 dbms_lob.writeAppend(l_xfdf_string, length(l_str4), l_str4);
749 dbms_lob.writeAppend(l_xfdf_string, 5, 'VALUE');
750 dbms_lob.writeAppend(l_xfdf_string, length(l_str5), l_str5);
751 dbms_lob.writeAppend(l_xfdf_string, length(l_str4), l_str4);
752 dbms_lob.writeAppend(l_xfdf_string, 11 , 'RECORD_DPSR');
753 dbms_lob.writeAppend(l_xfdf_string, length(l_str5), l_str5);
754
755 /*
756 else
757
758 dbms_lob.writeAppend(l_xfdf_string, length(l_str2), l_str2);
759 dbms_lob.writeAppend(l_xfdf_string, 11 , 'RECORD_DPSR');
760 dbms_lob.writeAppend(l_xfdf_string, length(l_str3), l_str3);
761 dbms_lob.writeAppend(l_xfdf_string, length(l_str4), l_str4);
762 dbms_lob.writeAppend(l_xfdf_string, 11 , 'RECORD_DPSR');
763 dbms_lob.writeAppend(l_xfdf_string, length(l_str5), l_str5);
764 */
765 end if;
766
767 END LOOP;
768
769 dbms_lob.writeAppend(l_xfdf_string, length(l_str6), l_str6 );
770
771 ELSE
772 dbms_lob.writeAppend(l_xfdf_string, length(l_str7), l_str7 );
773 END IF;
774
775 p_xfdf_clob := l_xfdf_string;
776
777 hr_utility.set_location('Leaving WritetoCLOB ',40);
778
779 EXCEPTION
780 WHEN OTHERS then
781 HR_UTILITY.TRACE('sqlerrm ' || SQLERRM);
782 HR_UTILITY.RAISE_ERROR;
783 END WritetoCLOB;
784 -------------------------------------------------------------------------------------------------------------------------
785
786 END PAY_FI_DPSR;