[Home] [Help]
PACKAGE BODY: APPS.PAY_FI_DPSR
Source
1 PACKAGE BODY PAY_FI_DPSR AS
2 /* $Header: pyfidpsr.pkb 120.4.12000000.1 2007/04/26 13:01:31 dbehera 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 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 /*Interest benefit from (accommodation) mortage*/
201 gtagdata(l_counter).TagName := '130';
202 gtagdata(l_counter).TagValue := FND_NUMBER.CANONICAL_TO_NUMBER(rg_csr_VSPSERIE1.action_information16);
203 l_counter := l_counter + 1;
204
205 /*Other taxable benefits*/
206 gtagdata(l_counter).TagName := '140';
207 gtagdata(l_counter).TagValue := FND_NUMBER.CANONICAL_TO_NUMBER(rg_csr_VSPSERIE1.action_information17);
208 l_counter := l_counter + 1;
209
210 /*Other benefits,Deduction made by Employer */
211 gtagdata(l_counter).TagName := '141';
212 gtagdata(l_counter).TagValue := FND_NUMBER.CANONICAL_TO_NUMBER(rg_csr_VSPSERIE2.action_information15);
213 l_counter := l_counter + 1;
214
215
216 /*Accommodation*/
217 gtagdata(l_counter).TagName := '142';
218 gtagdata(l_counter).TagValue := SUBSTR(rg_csr_VSPSERIE1.action_information18,1,1);
219 l_counter := l_counter + 1;
220
221 /*Phone*/
222 gtagdata(l_counter).TagName := '143';
223 gtagdata(l_counter).TagValue := SUBSTR(rg_csr_VSPSERIE1.action_information18,2,1);
224 l_counter := l_counter + 1;
225
226 /*Lunch*/
227 gtagdata(l_counter).TagName := '144';
228 gtagdata(l_counter).TagValue := SUBSTR(rg_csr_VSPSERIE1.action_information18,3,1);
229 l_counter := l_counter + 1;
230
231 /*Other benefit in kind*/
232 gtagdata(l_counter).TagName := '145';
233 gtagdata(l_counter).TagValue := SUBSTR(rg_csr_VSPSERIE1.action_information18,4,1);
234 l_counter := l_counter + 1;
235
236 /*Deduction made from lunch benefit is equivalent to the taxable value */
237 gtagdata(l_counter).TagName := '146';
238 gtagdata(l_counter).TagValue := SUBSTR(rg_csr_VSPSERIE1.action_information18,6,1);
239 l_counter := l_counter + 1;
240
241
242 /*Work-related Travel ticket */
243 gtagdata(l_counter).TagName := '147';
244 gtagdata(l_counter).TagValue := SUBSTR(rg_csr_VSPSERIE1.action_information18,5,1);
245 l_counter := l_counter + 1;
246
247 /*Tax free Daily allowances and meal compensations (etc.), total */
248 gtagdata(l_counter).TagName := '150';
249 gtagdata(l_counter).TagValue := FND_NUMBER.CANONICAL_TO_NUMBER(rg_csr_VSPSERIE1.action_information19);
250 l_counter := l_counter + 1;
251
252 /*Tax free Daily allowance (domestic)*/
253 gtagdata(l_counter).TagName := '151';
254 gtagdata(l_counter).TagValue := SUBSTR(rg_csr_VSPSERIE1.action_information20,1,1);
255 l_counter := l_counter + 1;
256
257 /*Tax free Half-day allowance*/
258 gtagdata(l_counter).TagName := '152';
259 gtagdata(l_counter).TagValue := SUBSTR(rg_csr_VSPSERIE1.action_information20,2,1);
260 l_counter := l_counter + 1;
261
262 /*Tax free Foreign daily allowance*/
263 gtagdata(l_counter).TagName := '153';
264 gtagdata(l_counter).TagValue := SUBSTR(rg_csr_VSPSERIE1.action_information20,3,1);
265 l_counter := l_counter + 1;
266
267 /*Tax free Meal compensation*/
268 gtagdata(l_counter).TagName := '154';
269 gtagdata(l_counter).TagValue := SUBSTR(rg_csr_VSPSERIE1.action_information20,4,1);
270 l_counter := l_counter + 1;
271
272 /*Tax-free mileage allowance, km total*/
273 gtagdata(l_counter).TagName := '155';
274 gtagdata(l_counter).TagValue := FND_NUMBER.CANONICAL_TO_NUMBER(rg_csr_VSPSERIE1.action_information21);
275 l_counter := l_counter + 1;
276
277 /*Tax-free mileage allowance, Euros total*/
278 gtagdata(l_counter).TagName := '156';
279 gtagdata(l_counter).TagValue := FND_NUMBER.CANONICAL_TO_NUMBER(rg_csr_VSPSERIE1.action_information22);
280 l_counter := l_counter + 1;
281
282 /*Taxable Compensations from expenses*/
283 gtagdata(l_counter).TagName := '157';
284 gtagdata(l_counter).TagValue := FND_NUMBER.CANONICAL_TO_NUMBER(rg_csr_VSPSERIE2.action_information9);
285 l_counter := l_counter + 1;
286
287 /*Payments for elected official in a (communal position of trust) */
288 gtagdata(l_counter).TagName := '160';
289 gtagdata(l_counter).TagValue := FND_NUMBER.CANONICAL_TO_NUMBER(rg_csr_VSPSERIE2.action_information10);
290 l_counter := l_counter + 1;
291
292
293 /*Benefit from Employer stock option schemes*/
294 gtagdata(l_counter).TagName := '135';
295 gtagdata(l_counter).TagValue := FND_NUMBER.CANONICAL_TO_NUMBER(rg_csr_VSPSERIE1.action_information23);
296 l_counter := l_counter + 1;
297
298 /*Pension insurance payments paid by Employer*/
299 gtagdata(l_counter).TagName := '180';
300 gtagdata(l_counter).TagValue := FND_NUMBER.CANONICAL_TO_NUMBER(rg_csr_VSPSERIE1.action_information24);
301 l_counter := l_counter + 1;
302
303 /*Voluntary pension insurance payments paid by Employee */
304 gtagdata(l_counter).TagName := '181';
305 gtagdata(l_counter).TagValue := FND_NUMBER.CANONICAL_TO_NUMBER(rg_csr_VSPSERIE2.action_information11);
306 l_counter := l_counter + 1;
307
308 /*Voluntary pension insurance payments paid by Employee*/
309 gtagdata(l_counter).TagName := '182';
310 gtagdata(l_counter).TagValue := FND_NUMBER.CANONICAL_TO_NUMBER(rg_csr_VSPSERIE2.action_information12);
311 l_counter := l_counter + 1;
312
313
314 /*Salary payments not subject to Social insurance daily allowance fee*/
315 gtagdata(l_counter).TagName := '136';
316 gtagdata(l_counter).TagValue := FND_NUMBER.CANONICAL_TO_NUMBER(rg_csr_VSPSERIE1.action_information27);
317 l_counter := l_counter + 1;
318
319 /*Work-related Travel ticket as benefit in kind*/
320 gtagdata(l_counter).TagName := '148';
321 gtagdata(l_counter).TagValue := FND_NUMBER.CANONICAL_TO_NUMBER(rg_csr_VSPSERIE1.action_information26);
322 l_counter := l_counter + 1;
323
324 END IF;
325
326 /*End Code*/
327 gtagdata(l_counter).TagName := '999';
328 gtagdata(l_counter).TagValue := rg_csr_VSPSERIE1.action_information30;
329 l_counter := l_counter + 1;
330
331 END LOOP;
332
333 /* Pick up the data related to Record VSRAERIE*/
334 FOR rg_csr_VSRAERIE IN csr_VSRAERIE(l_payroll_action_id)
335 LOOP
336 /*Record Id*/
337 gtagdata(l_counter).TagName := '000';
338 gtagdata(l_counter).TagValue := rg_csr_VSRAERIE.action_information3;
339 l_counter := l_counter + 1;
340
341 /*Transaction Type*/
342 gtagdata(l_counter).TagName := '301';
343 gtagdata(l_counter).TagValue := rg_csr_VSRAERIE.action_information5;
344 l_counter := l_counter + 1;
345
346 /*Payment Type*/
347 gtagdata(l_counter).TagName := '316';
348 gtagdata(l_counter).TagValue := rg_csr_VSRAERIE.action_information4;
349 l_counter := l_counter + 1;
350
351 /*Filing year*/
352 gtagdata(l_counter).TagName := '303';
353 gtagdata(l_counter).TagValue := rg_csr_VSRAERIE.action_information6;
354 l_counter := l_counter + 1;
355
356 /*Payer ID*/
357 gtagdata(l_counter).TagName := '302';
358 gtagdata(l_counter).TagValue := rg_csr_VSRAERIE.action_information7;
359 l_counter := l_counter + 1;
360
361 /*Payee's ID*/
362 gtagdata(l_counter).TagName := '312';
363 gtagdata(l_counter).TagValue := rg_csr_VSRAERIE.action_information8;
364 l_counter := l_counter + 1;
365
366 /*Payee's last name*/
367 gtagdata(l_counter).TagName := '307';
368 gtagdata(l_counter).TagValue := rg_csr_VSRAERIE.action_information9;
369 l_counter := l_counter + 1;
370
371 /*Payee's First name(s)*/
372 gtagdata(l_counter).TagName := '308';
373 gtagdata(l_counter).TagValue := rg_csr_VSRAERIE.action_information10;
374 l_counter := l_counter + 1;
375
376 /*Visiting address in country of permanent residence (home country)*/
377 gtagdata(l_counter).TagName := '309';
378 gtagdata(l_counter).TagValue := rg_csr_VSRAERIE.action_information11;
379 l_counter := l_counter + 1;
380
381 /*Postal code in country of permanent residence (home country)*/
382 gtagdata(l_counter).TagName := '310';
383 gtagdata(l_counter).TagValue := rg_csr_VSRAERIE.action_information12;
384 l_counter := l_counter + 1;
385
386 /*City/County or equivalent country of permanent residence*/
387 gtagdata(l_counter).TagName := '311';
388 gtagdata(l_counter).TagValue := rg_csr_VSRAERIE.action_information13;
389 l_counter := l_counter + 1;
390
391 /*Foreign Personal ID */
392 gtagdata(l_counter).TagName := '313';
393 gtagdata(l_counter).TagValue := rg_csr_VSRAERIE.action_information25;
394 l_counter := l_counter + 1;
395
396
397 /*Country code of home country (ISO 3166)*/
398 gtagdata(l_counter).TagName := '341';
399 gtagdata(l_counter).TagValue := rg_csr_VSRAERIE.action_information14;
400 l_counter := l_counter + 1;
401
402 /*Name of home country*/
403 gtagdata(l_counter).TagName := '342';
404 gtagdata(l_counter).TagValue := rg_csr_VSRAERIE.action_information24;
405 l_counter := l_counter + 1;
406
407 /*Gross pay amount, as the basis for the tax at source*/
408 gtagdata(l_counter).TagName := '317';
409 gtagdata(l_counter).TagValue := FND_NUMBER.CANONICAL_TO_NUMBER(rg_csr_VSRAERIE.action_information15);
410 l_counter := l_counter + 1;
411
412 /*Amount of tax at source deducted*/
413 gtagdata(l_counter).TagName := '318';
417 IF rg_csr_VSRAERIE.action_information4 IN ('A1','A2','A4','A5','A6','A7') THEN
414 gtagdata(l_counter).TagValue := FND_NUMBER.CANONICAL_TO_NUMBER(rg_csr_VSRAERIE.action_information16);
415 l_counter := l_counter + 1;
416
418
419 /*Salary in money, cash*/
420 gtagdata(l_counter).TagName := '319';
421 gtagdata(l_counter).TagValue := FND_NUMBER.CANONICAL_TO_NUMBER(rg_csr_VSRAERIE.action_information17);
422 l_counter := l_counter + 1;
423
424 /*Benefit in kind*/
425 gtagdata(l_counter).TagName := '320';
426 gtagdata(l_counter).TagValue := FND_NUMBER.CANONICAL_TO_NUMBER(rg_csr_VSRAERIE.action_information18);
427 l_counter := l_counter + 1;
428
429 /*Tax at source deduction*/
430 gtagdata(l_counter).TagName := '321';
431 gtagdata(l_counter).TagValue := FND_NUMBER.CANONICAL_TO_NUMBER(rg_csr_VSRAERIE.action_information19);
432 l_counter := l_counter + 1;
433
434 /*Daily allowances and expenses compensations*/
435 gtagdata(l_counter).TagName := '322';
436 gtagdata(l_counter).TagValue := FND_NUMBER.CANONICAL_TO_NUMBER(rg_csr_VSRAERIE.action_information20);
437 l_counter := l_counter + 1;
438
439 /*Social security fee */
440 gtagdata(l_counter).TagName := '324';
441 gtagdata(l_counter).TagValue := FND_NUMBER.CANONICAL_TO_NUMBER(rg_csr_VSRAERIE.action_information23);
442 l_counter := l_counter + 1;
443
444
445 END IF;
446 /*Contact person*/
447 gtagdata(l_counter).TagName := '305';
448 gtagdata(l_counter).TagValue := rg_csr_VSRAERIE.action_information21;
449 l_counter := l_counter + 1;
450
451 /*Contact person: Telephone Number*/
452 gtagdata(l_counter).TagName := '336';
453 gtagdata(l_counter).TagValue := rg_csr_VSRAERIE.action_information22;
454 l_counter := l_counter + 1;
455
456
457 /*End Code*/
458 gtagdata(l_counter).TagName := '999';
459 gtagdata(l_counter).TagValue := rg_csr_VSRAERIE.action_information30;
460 l_counter := l_counter + 1;
461
462 END LOOP;
463
464
465 /* Pick up the data related to Record VSPSTUKI*/
466 FOR rg_csr_VSPSTUKI IN csr_VSPSTUKI(l_payroll_action_id)
467 LOOP
468 /*Record Id*/
469 gtagdata(l_counter).TagName := '000';
470 gtagdata(l_counter).TagValue := rg_csr_VSPSTUKI.action_information3;
471 l_counter := l_counter + 1;
472
473 /*Transaction Type*/
474 gtagdata(l_counter).TagName := '019';
475 gtagdata(l_counter).TagValue := rg_csr_VSPSTUKI.action_information5;
476 l_counter := l_counter + 1;
477
478 /*Tax Type*/
479 gtagdata(l_counter).TagName := '010';
480 gtagdata(l_counter).TagValue := rg_csr_VSPSTUKI.action_information4;
481 l_counter := l_counter + 1;
482
483 /*Filing year*/
484 gtagdata(l_counter).TagName := '602';
485 gtagdata(l_counter).TagValue := rg_csr_VSPSTUKI.action_information6;
486 l_counter := l_counter + 1;
487
488 /*Payer ID*/
489 gtagdata(l_counter).TagName := '002';
490 gtagdata(l_counter).TagValue := rg_csr_VSPSTUKI.action_information7;
491 l_counter := l_counter + 1;
492
493 /*Payee ID*/
494 gtagdata(l_counter).TagName := '011';
495 gtagdata(l_counter).TagValue := rg_csr_VSPSTUKI.action_information8;
496 l_counter := l_counter + 1;
497
498 /*Payment month*/
499 gtagdata(l_counter).TagName := '013';
500 gtagdata(l_counter).TagValue := rg_csr_VSPSTUKI.action_information9;
501 l_counter := l_counter + 1;
502
503 /*Basis for the deduction*/
504 gtagdata(l_counter).TagName := '014';
505 gtagdata(l_counter).TagValue := FND_NUMBER.CANONICAL_TO_NUMBER(rg_csr_VSPSTUKI.action_information10);
506 l_counter := l_counter + 1;
507
508 /*Calculatory salary for the part-time retired employee*/
509 gtagdata(l_counter).TagName := '015';
510 gtagdata(l_counter).TagValue := FND_NUMBER.CANONICAL_TO_NUMBER(rg_csr_VSPSTUKI.action_information11);
511 l_counter := l_counter + 1;
512
513 /*Amount deducted as su bsidy of the low-paid*/
514 gtagdata(l_counter).TagName := '016';
515 gtagdata(l_counter).TagValue := FND_NUMBER.CANONICAL_TO_NUMBER(rg_csr_VSPSTUKI.action_information12);
516 l_counter := l_counter + 1;
517
518 /*Hours from months at work*/
519 gtagdata(l_counter).TagName := '017';
520 gtagdata(l_counter).TagValue := rg_csr_VSPSTUKI.action_information13;
521 l_counter := l_counter + 1;
522
523 /*Ending code*/
524 gtagdata(l_counter).TagName := '999';
525 gtagdata(l_counter).TagValue := rg_csr_VSPSTUKI.action_information30;
526 l_counter := l_counter + 1;
527
528 END LOOP;
529
530
531 /* Pick up the data related to Record VSPSVYSL*/
532 FOR rg_csr_VSPSVYSL IN csr_VSPSVYSL(l_payroll_action_id)
533 LOOP
534 /*Record Id*/
535 gtagdata(l_counter).TagName := '000';
536 gtagdata(l_counter).TagValue := rg_csr_VSPSVYSL.action_information2;
537 l_counter := l_counter + 1;
538
539 /*Transaction Type*/
540 gtagdata(l_counter).TagName := '611';
541 gtagdata(l_counter).TagValue := rg_csr_VSPSVYSL.action_information3;
542 l_counter := l_counter + 1;
543
544 /*Payment Type*/
545 gtagdata(l_counter).TagName := '606';
546 gtagdata(l_counter).TagValue := rg_csr_VSPSVYSL.action_information4;
547 l_counter := l_counter + 1;
548
549 /*Filing year*/
550 gtagdata(l_counter).TagName := '602';
551 gtagdata(l_counter).TagValue := rg_csr_VSPSVYSL.action_information5;
552 l_counter := l_counter + 1;
553
554 /*Payer ID*/
555 gtagdata(l_counter).TagName := '601';
556 gtagdata(l_counter).TagValue := rg_csr_VSPSVYSL.action_information6;
557 l_counter := l_counter + 1;
558
559 /*Total amount of payments*/
560 gtagdata(l_counter).TagName := '607';
564 /*Withhold tax/tax at source*/
561 gtagdata(l_counter).TagValue := FND_NUMBER.CANONICAL_TO_NUMBER(rg_csr_VSPSVYSL.action_information7);
562 l_counter := l_counter + 1;
563
565 gtagdata(l_counter).TagName := '608';
566 gtagdata(l_counter).TagValue := FND_NUMBER.CANONICAL_TO_NUMBER(rg_csr_VSPSVYSL.action_information8);
567 l_counter := l_counter + 1;
568
569 /*Number of records)*/
570 gtagdata(l_counter).TagName := '620';
571 gtagdata(l_counter).TagValue := rg_csr_VSPSVYSL.action_information9;
572 l_counter := l_counter + 1;
573
574 /*Ending code*/
575 gtagdata(l_counter).TagName := '999';
576 gtagdata(l_counter).TagValue := rg_csr_VSPSVYSL.action_information30;
577 l_counter := l_counter + 1;
578
579 END LOOP;
580
581 /* Pick up the data related to Record VSPSVYHT*/
582 FOR rg_csr_VSPSVYHT IN csr_VSPSVYHT(l_payroll_action_id)
583 LOOP
584 /*Record Id*/
585 gtagdata(l_counter).TagName := '000';
586 gtagdata(l_counter).TagValue := rg_csr_VSPSVYHT.action_information2;
587 l_counter := l_counter + 1;
588
589 /*Transaction Type*/
590 gtagdata(l_counter).TagName := '611';
591 gtagdata(l_counter).TagValue := rg_csr_VSPSVYHT.action_information3;
592 l_counter := l_counter + 1;
593
594 /*Filing year*/
595 gtagdata(l_counter).TagName := '602';
596 gtagdata(l_counter).TagValue := rg_csr_VSPSVYHT.action_information4;
597 l_counter := l_counter + 1;
598
599 /*Payer ID*/
600 gtagdata(l_counter).TagName := '601';
601 gtagdata(l_counter).TagValue := rg_csr_VSPSVYHT.action_information5;
602 l_counter := l_counter + 1;
603
604 /*Contact person*/
605 gtagdata(l_counter).TagName := '605';
606 gtagdata(l_counter).TagValue := rg_csr_VSPSVYHT.action_information6;
607 l_counter := l_counter + 1;
608
609 /*Contact Person Telephone Number*/
610 gtagdata(l_counter).TagName := '604';
611 gtagdata(l_counter).TagValue := rg_csr_VSPSVYHT.action_information7;
612 l_counter := l_counter + 1;
613
614 /*Number of summary records */
615 gtagdata(l_counter).TagName := '612';
616 gtagdata(l_counter).TagValue := rg_csr_VSPSVYHT.action_information8;
617 l_counter := l_counter + 1;
618
619 /*Benefits in kind */
620 gtagdata(l_counter).TagName := '615';
621 gtagdata(l_counter).TagValue := FND_NUMBER.CANONICAL_TO_NUMBER(rg_csr_VSPSVYHT.action_information9);
622 l_counter := l_counter + 1;
623
624 /*Employees' statutory pension and unemployment insurance contributions*/
625 gtagdata(l_counter).TagName := '640';
626 gtagdata(l_counter).TagValue := FND_NUMBER.CANONICAL_TO_NUMBER(rg_csr_VSPSVYHT.action_information10);
627 l_counter := l_counter + 1;
628
629 /*Deductions prior to withhold tax, total(pre tax deductions)*/
630 gtagdata(l_counter).TagName := '641';
631 gtagdata(l_counter).TagValue := FND_NUMBER.CANONICAL_TO_NUMBER(rg_csr_VSPSVYHT.action_information11);
632 l_counter := l_counter + 1;
633
634 /*Salaries have not been paid during filing year*/
635 gtagdata(l_counter).TagName := '613';
636 gtagdata(l_counter).TagValue := FND_NUMBER.CANONICAL_TO_NUMBER(rg_csr_VSPSVYHT.action_information12);
637 l_counter := l_counter + 1;
638
639 /*Deductions from social security fee*/
640 gtagdata(l_counter).TagName := '631';
641 gtagdata(l_counter).TagValue := FND_NUMBER.CANONICAL_TO_NUMBER(rg_csr_VSPSVYHT.action_information13);
642 l_counter := l_counter + 1;
643
644 /*Amount deducted from salaries subject to social insurance fee.*/
645 gtagdata(l_counter).TagName := '670';
646 gtagdata(l_counter).TagValue := FND_NUMBER.CANONICAL_TO_NUMBER(rg_csr_VSPSVYHT.action_information14);
647 l_counter := l_counter + 1;
648
649
650 /*Ending code*/
651 gtagdata(l_counter).TagName := '999';
652 gtagdata(l_counter).TagValue := rg_csr_VSPSVYHT.action_information30;
653 l_counter := l_counter + 1;
654
655 END LOOP;
656 hr_utility.set_location('After populating pl/sql table',30);
657
658
659 WritetoCLOB (p_xml );
660
661
662 END GET_DATA;
663
664 -----------------------------------------------------------------------------------------------------------------
665 PROCEDURE WritetoCLOB(p_xfdf_clob out nocopy CLOB) is
666 l_xfdf_string clob;
667 l_str1 varchar2(1000);
668 l_str2 varchar2(20);
669 l_str3 varchar2(20);
670 l_str4 varchar2(20);
671 l_str5 varchar2(20);
672 l_str6 varchar2(30);
673 l_str7 varchar2(1000);
674 l_str8 varchar2(240);
675 l_str9 varchar2(240);
676
677 current_index pls_integer;
678 l_IANA_charset VARCHAR2 (50);
679
680 BEGIN
681 l_IANA_charset :=hr_fi_utility.get_IANA_charset ;
682 hr_utility.set_location('Entering WritetoCLOB ',70);
683 l_str1 := '<?xml version="1.0" encoding="'||l_IANA_charset||'"?> <ROOT><DPSR>' ;
684 l_str2 := '<';
685 l_str3 := '>';
686 l_str4 := '</';
687 l_str5 := '>';
688 l_str6 := '</DPSR></ROOT>';
689 l_str7 := '<?xml version="1.0" encoding="'||l_IANA_charset||'"?> <ROOT></ROOT>';
690
691
692 dbms_lob.createtemporary(l_xfdf_string,FALSE,DBMS_LOB.CALL);
693 dbms_lob.open(l_xfdf_string,dbms_lob.lob_readwrite);
694
695 current_index := 0;
696
697 IF gtagdata.count > 0 THEN
698
699 dbms_lob.writeAppend( l_xfdf_string, length(l_str1), l_str1 );
700
701
702 FOR table_counter IN gtagdata.FIRST .. gtagdata.LAST LOOP
703
704 l_str8 := gtagdata(table_counter).TagName;
705 l_str9 := '<![CDATA[ '|| gtagdata(table_counter).TagValue ||' ]]>';
706
707 if l_str9 is not null then
708 dbms_lob.writeAppend(l_xfdf_string, length(l_str2), l_str2);
709 dbms_lob.writeAppend(l_xfdf_string, 11 , 'RECORD_DPSR');
710 dbms_lob.writeAppend(l_xfdf_string, length(l_str3), l_str3);
711
712 dbms_lob.writeAppend(l_xfdf_string, length(l_str2), l_str2);
713 dbms_lob.writeAppend(l_xfdf_string, 4, 'CODE');
714 dbms_lob.writeAppend(l_xfdf_string, length(l_str3), l_str3);
715 dbms_lob.writeAppend(l_xfdf_string, length(l_str8), l_str8);
716 dbms_lob.writeAppend(l_xfdf_string, length(l_str4), l_str4);
717 dbms_lob.writeAppend(l_xfdf_string, 4 , 'CODE');
718 dbms_lob.writeAppend(l_xfdf_string, length(l_str5), l_str5);
719
720 dbms_lob.writeAppend(l_xfdf_string, length(l_str2), l_str2);
721 dbms_lob.writeAppend(l_xfdf_string, 5, 'VALUE');
722 dbms_lob.writeAppend(l_xfdf_string, length(l_str3), l_str3);
723 dbms_lob.writeAppend(l_xfdf_string, length(l_str9), l_str9);
724 dbms_lob.writeAppend(l_xfdf_string, length(l_str4), l_str4);
725 dbms_lob.writeAppend(l_xfdf_string, 5, 'VALUE');
726 dbms_lob.writeAppend(l_xfdf_string, length(l_str5), l_str5);
727 dbms_lob.writeAppend(l_xfdf_string, length(l_str4), l_str4);
728 dbms_lob.writeAppend(l_xfdf_string, 11 , 'RECORD_DPSR');
729 dbms_lob.writeAppend(l_xfdf_string, length(l_str5), l_str5);
730
731 /*
732 else
733
734 dbms_lob.writeAppend(l_xfdf_string, length(l_str2), l_str2);
735 dbms_lob.writeAppend(l_xfdf_string, 11 , 'RECORD_DPSR');
736 dbms_lob.writeAppend(l_xfdf_string, length(l_str3), l_str3);
737 dbms_lob.writeAppend(l_xfdf_string, length(l_str4), l_str4);
738 dbms_lob.writeAppend(l_xfdf_string, 11 , 'RECORD_DPSR');
739 dbms_lob.writeAppend(l_xfdf_string, length(l_str5), l_str5);
740 */
741 end if;
742
743 END LOOP;
744
745 dbms_lob.writeAppend(l_xfdf_string, length(l_str6), l_str6 );
746
747 ELSE
748 dbms_lob.writeAppend(l_xfdf_string, length(l_str7), l_str7 );
749 END IF;
750
751 p_xfdf_clob := l_xfdf_string;
752
753 hr_utility.set_location('Leaving WritetoCLOB ',40);
754
755 EXCEPTION
756 WHEN OTHERS then
757 HR_UTILITY.TRACE('sqlerrm ' || SQLERRM);
758 HR_UTILITY.RAISE_ERROR;
759 END WritetoCLOB;
760 -------------------------------------------------------------------------------------------------------------------------
761
762 END PAY_FI_DPSR;