[Home] [Help]
PACKAGE BODY: APPS.PAY_DK_TCR
Source
1 PACKAGE BODY PAY_DK_TCR AS
2 /* $Header: pydktaxreq.pkb 120.0.12010000.1 2008/10/15 11:37:38 pvelugul noship $ */
3
4 PROCEDURE GET_DATA (
5 p_business_group_id IN NUMBER,
6 p_legal_employer IN VARCHAR2 ,
7 p_start_date IN VARCHAR2,
8 p_test_submission IN VARCHAR2,
9 p_template_name IN VARCHAR2,
10 p_xml OUT NOCOPY CLOB
11 )
12 IS
13
14 l_counter NUMBER := 0;
15 l_rec_counter NUMBER:=1;
16 l_date VARCHAR2(10);
17 l_time VARCHAR2(10);
18 l_sender_type VARCHAR2(2);
19 l_test_submission VARCHAR2(2);
20 l_le_cvr_number VARCHAR2(10);
21 l_se_number VARCHAR2(10);
22 l_cvr_number VARCHAR2(10);
23 l_cpr_number VARCHAR2(15);
24 l_req_status VARCHAR2(25);
25 l_tax_card_type VARCHAR2(3);
26 l_sex VARCHAR2(1);
27 l_bg_id per_all_assignments_f.business_group_id%type;
28 l_le_id VARCHAR2(60);
29 l_style per_addresses_v.style%type;
30 l_flag NUMBER:=0;
31 l_effective_start_date DATE;
32 l_effective_end_date DATE;
33 l_input_value_id pay_input_values_f.input_value_id%type;
34 l_update_warning BOOLEAN := FALSE;
35
36 NO_E_INCOME_DATA_SUPPLIER EXCEPTION;
37
38 /*Legal Employer Information*/
39 Cursor csr_Legal_Emp_Details (csr_v_legal_emp_id hr_organization_information.ORGANIZATION_ID%TYPE)
40 IS
41 SELECT o1.name ,hoi2.ORG_INFORMATION1 , hoi2.ORG_INFORMATION2, hoi2.ORG_INFORMATION3, hoi2.ORG_INFORMATION4, hoi2.ORG_INFORMATION5, hoi2.ORG_INFORMATION6, hoi2.ORG_INFORMATION13
42 FROM hr_organization_units o1
43 , hr_organization_information hoi1
44 , hr_organization_information hoi2
45 WHERE o1.business_group_id =p_business_group_id
46 AND hoi1.organization_id = o1.organization_id
47 AND hoi1.organization_id = csr_v_legal_emp_id
48 AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
49 AND hoi1.org_information_context = 'CLASS'
50 AND o1.organization_id =hoi2.organization_id
51 AND hoi2.ORG_INFORMATION_CONTEXT='DK_LEGAL_ENTITY_DETAILS' ;
52
53 rg_Legal_Emp_Details csr_Legal_Emp_Details%ROWTYPE;
54
55 /* Service Provider information */
56 CURSOR service_provider_details
57 IS
58 SELECT * FROM hr_organization_information
59 WHERE org_information_context = 'DK_SERVICE_PROVIDER_DETAILS'
60 AND organization_id IN (
61 SELECT organization_id FROM hr_organization_units
62 WHERE business_group_id= p_business_group_id);
63
64 sp service_provider_details%ROWTYPE;
65
66 /* Get the person id*/
67 CURSOR csr_get_person_id(p_le_id VARCHAR2, p_bg_id NUMBER)
68 IS
69 select pap.person_id, paa.assignment_id, pap.NATIONAL_IDENTIFIER, paa.assignment_number, to_char(pap.date_of_birth,'yyyymmdd') dob,
70 first_name||' '||middle_names||' '||last_name pname, pap.effective_start_date
71 from per_all_assignments_f paa,
72 per_all_people_f pap, hr_soft_coding_keyflex scl where
73 paa.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
74 and scl.segment1 = p_le_id
75 and paa.business_group_id = p_bg_id
76 and paa.PRIMARY_FLAG='Y'
77 and pap.person_id=paa.person_id
78 and pap.effective_start_date = (SELECT MAX(date_start) lhd FROM per_periods_of_service
79 WHERE person_id=pap.person_id)
80 and pap.effective_start_date >= fnd_date.canonical_to_date(p_start_date)
81 and pap.effective_start_date = paa.effective_start_date;
82
83 rg_csr_get_person_id csr_get_person_id%ROWTYPE;
84
85 /* Get the tax card details */
86 CURSOR csr_get_tax_card_details(p_assignment_id per_all_assignments_f.assignment_id%type, p_input_value pay_input_values_f.name%type, p_hire_date per_all_people_f.effective_start_date%type) IS
87 SELECT ee.effective_start_date, eev1.screen_entry_value, ee.element_entry_id, ee.object_version_number, iv1.input_value_id
88 FROM --per_all_assignments_f asg1
89 pay_element_types_f et
90 ,pay_input_values_f iv1
91 ,pay_element_entries_f ee
92 ,pay_element_entry_values_f eev1
93 WHERE -- asg1.assignment_id = p_assignment_id
94 et.element_name = 'Tax Card'
95 AND et.legislation_code = 'DK'
96 AND iv1.element_type_id = et.element_type_id
97 AND iv1.name = p_input_value
98 AND ee.element_type_id = et.element_type_id
99 AND ee.assignment_id = p_assignment_id--asg1.assignment_id
100 AND eev1.element_entry_id = ee.element_entry_id
101 AND eev1.input_value_id = iv1.input_value_id
102 and eev1.effective_start_date = ee.effective_start_date
103 and p_hire_date between ee.effective_start_date and ee.effective_end_date
104 and p_hire_date between et.effective_start_date and et.effective_end_Date
105 and p_hire_date between iv1.effective_start_date and iv1.effective_end_date;
106
107 rg_csr_get_tax_card_details csr_get_tax_card_details%rowtype;
108
109 /* Get the territory */
110 CURSOR csr_get_territory(pid per_all_people_f.person_id%type) IS
111 SELECT *
112 FROM per_addresses_v
113 WHERE person_id =pid
114 and primary_flag='Y'
115 and business_group_id=p_business_group_id;
116
117 rg_csr_get_territory csr_get_territory%rowtype;
118
119 /* End of Cursors */
120 BEGIN
121
122 /* Pick up the data related to Record 1000*/
123
124 -- fnd_file.put_line(fnd_file.log,'1');
125 gtagdata(l_counter).TagName := 'REC_1000';
126 gtagdata(l_counter).TagValue := 'REC_1000';
127 l_counter := l_counter + 1;
128
129 /* line num */
130 gtagdata(l_counter).TagName := 'RT1000_01';
131 gtagdata(l_counter).TagValue := lpad(to_char(l_rec_counter),7,'0');
132 l_counter := l_counter + 1;
133
134 /*Rec num */
135 gtagdata(l_counter).TagName := 'RT1000_02';
136 gtagdata(l_counter).TagValue := '1000';
137 l_counter := l_counter + 1;
138
139 /*Date sent*/
140 SELECT to_char(sysdate,'yyyymmdd') INTO l_date FROM dual;
141 gtagdata(l_counter).TagName := 'RT1000_03';
142 gtagdata(l_counter).TagValue := l_date;
143 l_counter := l_counter + 1;
144
145 /*Time sent*/
146 SELECT to_char(sysdate,'hhmiss') INTO l_time FROM dual;
147 gtagdata(l_counter).TagName := 'RT1000_04';
148 gtagdata(l_counter).TagValue := l_time;
149 l_counter := l_counter + 1;
150
151 OPEN csr_Legal_Emp_Details(p_legal_employer);
152 FETCH csr_Legal_Emp_Details INTO rg_Legal_Emp_Details;
153 l_le_cvr_number := rg_Legal_Emp_Details.ORG_INFORMATION1; -- this is for rec 2001
154 l_cvr_number:= rg_Legal_Emp_Details.ORG_INFORMATION1;
155 l_se_number := rg_Legal_Emp_Details.ORG_INFORMATION5;
156 CLOSE csr_Legal_Emp_Details;
157 l_sender_type:='01';
158
159 if(rg_Legal_Emp_Details.ORG_INFORMATION3 = 'N') then
160 OPEN service_provider_details;
161 FETCH service_provider_details INTO sp;
162 l_cvr_number:=sp.org_information1;
163 l_se_number:=sp.org_information1;
164 CLOSE service_provider_details;
165
166 /* If the service provider has Data Supplier set to 'N', output the message and stop processing */
167 if(sp.org_information3<>'Y') then
168 fnd_file.put_line(fnd_file.log,HR_DK_UTILITY.GET_MESSAGE('PAY','HR_377103_DK_EINCOME_STATUS'));
169 RAISE NO_E_INCOME_DATA_SUPPLIER;
170 end if;
171
172 l_sender_type:='02';
173 end if;
174
175 /*SE number*/
176 gtagdata(l_counter).TagName := 'RT1000_05';
177 gtagdata(l_counter).TagValue := l_se_number; -- lpad(rg_csr_1000.action_information6,8,'0')
178 l_counter := l_counter + 1;
179
180 /*CVR number*/
181 gtagdata(l_counter).TagName := 'RT1000_06';
182 gtagdata(l_counter).TagValue := l_cvr_number;
183 l_counter := l_counter + 1;
184
185 /*Sender type*/
186 gtagdata(l_counter).TagName := 'RT1000_07';
187 gtagdata(l_counter).TagValue := l_sender_type;
188 l_counter := l_counter + 1;
189
190 /*Filler*/
191 gtagdata(l_counter).TagName := 'RT1000_08';
192 gtagdata(l_counter).TagValue := lpad('0',5,'0');
193 l_counter := l_counter + 1;
194
195 /*Report Method name*/
196 gtagdata(l_counter).TagName := 'RT1000_09';
197 gtagdata(l_counter).TagValue := '0'; -- constant
198 l_counter := l_counter + 1;
199
200 /*IT System*/
201 gtagdata(l_counter).TagName := 'RT1000_10';
202 gtagdata(l_counter).TagValue := 'Oracle Payroll';
203 l_counter := l_counter + 1;
204
205 /* IT System Version */
206 gtagdata(l_counter).TagName := 'RT1000_11';
207 gtagdata(l_counter).TagValue := '1'; -- constant
208 l_counter := l_counter + 1;
209
210 /*Main sender ID*/
211 gtagdata(l_counter).TagName := 'RT1000_12';
212 gtagdata(l_counter).TagValue := l_cvr_number;
213 l_counter := l_counter + 1;
214
215 /*E-Income version*/
216 gtagdata(l_counter).TagName := 'RT1000_13';
217 gtagdata(l_counter).TagValue := '2.0';
218 l_counter := l_counter + 1;
219
220 /*Test Marking*/
221 if(p_test_submission='Y') then
222 l_test_submission:='T';
223 else
224 l_test_submission:='P';
225 end if;
226 gtagdata(l_counter).TagName := 'RT1000_14';
227 gtagdata(l_counter).TagValue := l_test_submission;
228 l_counter := l_counter + 1;
229
230 /*Filler */
231 gtagdata(l_counter).TagName := 'RT1000_15';
232 gtagdata(l_counter).TagValue := ''; -- 16 spaces
233 l_counter := l_counter + 1;
234
235 /*Filler */
236 gtagdata(l_counter).TagName := 'RT1000_16';
237 gtagdata(l_counter).TagValue := ''; -- 16 spaces
238 l_counter := l_counter + 1;
239
240 /*Indication of E-Income*/
241 gtagdata(l_counter).TagName := 'RT1000_17';
242 gtagdata(l_counter).TagValue := 'E';
243 l_counter := l_counter + 1;
244
245 -- fnd_file.put_line(fnd_file.log,'2');
246
247 -- Record 2001
248 gtagdata(l_counter).TagName := 'REC_2001';
249 gtagdata(l_counter).TagValue := 'REC_2001';
250 l_counter := l_counter + 1;
251
252 l_rec_counter:=l_rec_counter+1;
253 /* line num */
254 gtagdata(l_counter).TagName := 'RT2001_01';
255 gtagdata(l_counter).TagValue := lpad(to_char(l_rec_counter),7,'0');
256 l_counter := l_counter + 1;
257
258 /*Rec num */
259 gtagdata(l_counter).TagName := 'RT2001_02';
260 gtagdata(l_counter).TagValue := '2001';
261 l_counter := l_counter + 1;
262
263 /*Filler*/
264 gtagdata(l_counter).TagName := 'RT2001_03';
265 gtagdata(l_counter).TagValue := ''; --16 spaces
266 l_counter := l_counter + 1;
267
268 /*Company */
269 gtagdata(l_counter).TagName := 'RT2001_04';
270 gtagdata(l_counter).TagValue := l_le_cvr_number;
271 l_counter := l_counter + 1;
272
273 /*Termination of company*/
274 gtagdata(l_counter).TagName := 'RT2001_05';
275 gtagdata(l_counter).TagValue := '';
276 l_counter := l_counter + 1;
277
278 /*Currency */
279 gtagdata(l_counter).TagName := 'RT2001_06';
280 gtagdata(l_counter).TagValue := 'DKK'; -- constant
281 l_counter := l_counter + 1;
282
283 -- fnd_file.put_line(fnd_file.log,'3');
284 l_le_id:= p_legal_employer;
285
286 -- fnd_file.put_line(fnd_file.log,'4');
287 l_bg_id:= to_number(p_business_group_id);
288
289 -- fnd_file.put_line(fnd_file.log,'5');
290
291 -- fnd_file.put_line(fnd_file.log,'l_le_id : '||l_le_id);
292 -- fnd_file.put_line(fnd_file.log,'l_bg_id : '|| to_char(l_bg_id));
293
294 -- fnd_file.put_line(fnd_file.log,'p_start_date : '||p_start_date);
295 -- Record 2101
296 FOR rg_csr_get_person_id IN csr_get_person_id(l_le_id,l_bg_id)
297 LOOP
298
299 -- fnd_file.put_line(fnd_file.log,'6');
300
301 /* Get the tax requisition status */
302 OPEN csr_get_tax_card_details(rg_csr_get_person_id.assignment_id, 'Tax Card Requisition Status', rg_csr_get_person_id.effective_start_date);
303 FETCH csr_get_tax_card_details INTO rg_csr_get_tax_card_details;
304 CLOSE csr_get_tax_card_details;
305
306 -- fnd_file.put_line(fnd_file.log,'7');
307
308 l_input_value_id:=rg_csr_get_tax_card_details.input_value_id;
309 l_req_status:=rg_csr_get_tax_card_details.screen_entry_value;
310 if(l_req_status IN ('REQUIRED','RE-COMMISSION')) then
311 l_flag:=1;
312 -- fnd_file.put_line(fnd_file.log,'8');
313 gtagdata(l_counter).TagName := 'REC_2101';
314 gtagdata(l_counter).TagValue := 'REC_2101';
315 l_counter := l_counter + 1;
316
317 l_rec_counter:=l_rec_counter+1;
318 /* line num */
319 gtagdata(l_counter).TagName := 'RT2101_01';
320 gtagdata(l_counter).TagValue := lpad(to_char(l_rec_counter),7,'0');
321 l_counter := l_counter + 1;
322
323 /*Rec num */
324 gtagdata(l_counter).TagName := 'RT2101_02';
325 gtagdata(l_counter).TagValue := '2101';
326 l_counter := l_counter + 1;
327
328 /*CPR number */
329 l_cpr_number:= rg_csr_get_person_id.national_identifier; -- get the CPR number
330 l_cpr_number:=substr(l_cpr_number,1,6)||substr(l_cpr_number,8,4);
331 gtagdata(l_counter).TagName := 'RT2101_03';
332 gtagdata(l_counter).TagValue := l_cpr_number;
333 l_counter := l_counter + 1;
334
335 /*Numeric Filler*/
336 gtagdata(l_counter).TagName := 'RT2101_04';
337 gtagdata(l_counter).TagValue := lpad('0',8,'0'); -- 8 zeros
338 l_counter := l_counter + 1;
339
340 /*Filler*/
341 gtagdata(l_counter).TagName := 'RT2101_05';
342 gtagdata(l_counter).TagValue := ''; -- 15 spaces
343 l_counter := l_counter + 1;
344
345 /*Latest Hire date */
346 gtagdata(l_counter).TagName := 'RT2101_06';
347 gtagdata(l_counter).TagValue := to_char(rg_csr_get_person_id.effective_start_date,'yyyymmdd');
348 l_counter := l_counter + 1;
349
350 /*Termination Date*/
351 gtagdata(l_counter).TagName := 'RT2101_07';
352 gtagdata(l_counter).TagValue := lpad('0',8,'0'); -- 8 zeros
353 l_counter := l_counter + 1;
354
355 /*Numeric Filler*/
356 gtagdata(l_counter).TagName := 'RT2101_08';
357 gtagdata(l_counter).TagValue := lpad('0',5,'0'); -- 5 zeros
358 l_counter := l_counter + 1;
359
360 /*Numeric Filler*/
361 gtagdata(l_counter).TagName := 'RT2101_09';
362 gtagdata(l_counter).TagValue := lpad('0',5,'0'); -- 5 zeros
363 l_counter := l_counter + 1;
364
365 /*Numeric Filler*/
366 gtagdata(l_counter).TagName := 'RT2101_10';
367 gtagdata(l_counter).TagValue := lpad('0',4,'0'); -- 4 zeros
368 l_counter := l_counter + 1;
369
370 /*Numeric Filler */
371 gtagdata(l_counter).TagName := 'RT2101_11';
372 gtagdata(l_counter).TagValue := lpad('0',10,'0'); -- 10 zeros
373 l_counter := l_counter + 1;
374
375 /*Tax Card type*/
376 OPEN csr_get_tax_card_details(rg_csr_get_person_id.assignment_id, 'Tax Card Type', rg_csr_get_person_id.effective_start_date);
377 FETCH csr_get_tax_card_details INTO rg_csr_get_tax_card_details;
378 CLOSE csr_get_tax_card_details;
379
380 l_tax_card_type:=rg_csr_get_tax_card_details.screen_entry_value;
381 gtagdata(l_counter).TagName := 'RT2101_12';
382 if(l_tax_card_type IN ('H','F')) then
383 gtagdata(l_counter).TagValue := '1';
384 else
385 gtagdata(l_counter).TagValue := '2';
386 end if;
387 l_counter := l_counter + 1;
388
389 /*Valid from */
390 gtagdata(l_counter).TagName := 'RT2101_13';
391 gtagdata(l_counter).TagValue := to_char(rg_csr_get_person_id.effective_start_date,'yyyymmdd');
392 l_counter := l_counter + 1;
393
394 /* Assignment number */
395 gtagdata(l_counter).TagName := 'RT2101_14';
396 gtagdata(l_counter).TagValue := rg_csr_get_person_id.assignment_number;
397 l_counter := l_counter + 1;
398
399 /*Rekv_taxcard */
400 gtagdata(l_counter).TagName := 'RT2101_15';
401 if(l_req_status='RE-COMMISSION') then
402 gtagdata(l_counter).TagValue := 'R';
403 else
404 gtagdata(l_counter).TagValue := '';
405 end if;
406 l_counter := l_counter + 1;
407
408 /* Update the tax requisition status to - REQUEST COMPLETE*/
409 py_element_entry_api.update_element_entry
410 (p_validate => FALSE
411 ,p_datetrack_update_mode => 'CORRECTION' --p_datetrack_update_mode
412 ,p_effective_date => rg_csr_get_person_id.effective_start_date --p_effective_date
413 ,p_business_group_id => p_business_group_id
414 ,p_element_entry_id => rg_csr_get_tax_card_details.element_entry_id
415 ,p_object_version_number => rg_csr_get_tax_card_details.object_version_number --p_object_version_number
416 ,p_input_value_id1 => l_input_value_id
417 ,p_entry_value1 => 'REQUEST COMPLETE'
418 ,p_effective_start_date => l_effective_start_date
419 ,p_effective_end_date => l_effective_end_date
420 ,p_update_warning => l_update_warning
421 );
422
423 -- Record 8001
424 OPEN csr_get_territory(rg_csr_get_person_id.person_id);
425 FETCH csr_get_territory INTO rg_csr_get_territory;
426 CLOSE csr_get_territory;
427
428 if(rg_csr_get_territory.country NOT IN ('DK')) then
429 -- fnd_file.put_line(fnd_file.log,'9');
430 gtagdata(l_counter).TagName := 'REC_8001';
431 gtagdata(l_counter).TagValue := 'REC_8001';
432 l_counter := l_counter + 1;
433
434 l_rec_counter:=l_rec_counter+1;
435 /* line num */
436 gtagdata(l_counter).TagName := 'RT8001_01';
437 gtagdata(l_counter).TagValue := lpad(to_char(l_rec_counter),7,'0');
438 l_counter := l_counter + 1;
439
440 /*Rec num */
441 gtagdata(l_counter).TagName := 'RT8001_02';
442 gtagdata(l_counter).TagValue := '8001';
443 l_counter := l_counter + 1;
444
445 gtagdata(l_counter).TagName := 'RT8001_03';
446 gtagdata(l_counter).TagValue := rg_csr_get_person_id.dob;
447 l_counter := l_counter + 1;
448
449 -- Include a condition for checking M/F
450 select decode(mod(substr(l_cpr_number,10),2),0,'2','1') into l_sex from dual;
451 gtagdata(l_counter).TagName := 'RT8001_04';
452 gtagdata(l_counter).TagValue := l_sex;
453 l_counter := l_counter + 1;
454
455 /* Person Country */
456 gtagdata(l_counter).TagName := 'RT8001_05';
457 gtagdata(l_counter).TagValue := rg_csr_get_territory.country;
458 l_counter := l_counter + 1;
459
460 /*Person Name*/
461 gtagdata(l_counter).TagName := 'RT8001_06';
462 gtagdata(l_counter).TagValue := substr(rg_csr_get_person_id.pname,1,228);
463 l_counter := l_counter + 1;
464
465 /*Person Address*/
466 gtagdata(l_counter).TagName := 'RT8001_07';
467 gtagdata(l_counter).TagValue := substr(rg_csr_get_territory.address_line1,1,228);
468 l_counter := l_counter + 1;
469
470 /*Postal Code*/
471 gtagdata(l_counter).TagName := 'RT8001_08';
472 gtagdata(l_counter).TagValue := rg_csr_get_territory.postal_code;
473 l_counter := l_counter + 1;
474
475 l_style:=rg_csr_get_territory.style;
476 if(l_style = 'DK') then
477 /* Town */
478 gtagdata(l_counter).TagName := 'RT8001_09';
479 gtagdata(l_counter).TagValue := rg_csr_get_territory.postal_code;
480 l_counter := l_counter + 1;
481
482 elsif(l_style = 'DK_GLB') then
483 /* Town */
484 gtagdata(l_counter).TagName := 'RT8001_09';
485 gtagdata(l_counter).TagValue := rg_csr_get_territory.town_or_city;
486 l_counter := l_counter + 1;
487 end if;
488
489 gtagdata(l_counter).TagName := 'REC_8001';
490 gtagdata(l_counter).TagValue := 'REC_8001_END';
491 l_counter := l_counter + 1;
492
493 end if; -- territory check.
494
495 gtagdata(l_counter).TagName := 'REC_2101';
496 gtagdata(l_counter).TagValue := 'REC_2101_END';
497 l_counter := l_counter + 1;
498 end if;
499
500 END LOOP;
501
502 gtagdata(l_counter).TagName := 'REC_2001';
503 gtagdata(l_counter).TagValue := 'REC_2001_END';
504 l_counter := l_counter + 1;
505
506
507 gtagdata(l_counter).TagName := 'REC_1000';
508 gtagdata(l_counter).TagValue := 'REC_1000_END';
509 l_counter := l_counter + 1;
510
511 gtagdata(l_counter).TagName := 'REC_9999';
512 gtagdata(l_counter).TagValue := 'REC_9999';
513 l_counter := l_counter + 1;
514
515 -- Record 9999
516 /* OPEN csr_9999(l_payroll_action_id);
517 FETCH csr_9999 INTO rg_csr_9999;
518 CLOSE csr_9999;
519 */
520 l_rec_counter:=l_rec_counter+1;
521 /* line num */
522 gtagdata(l_counter).TagName := 'RT9999_01';
523 gtagdata(l_counter).TagValue := lpad(to_char(l_rec_counter),7,'0');
524 l_counter := l_counter + 1;
525
526 /*Rec num */
527 gtagdata(l_counter).TagName := 'RT9999_02';
528 gtagdata(l_counter).TagValue := '9999';
529 l_counter := l_counter + 1;
530
531 -- Number of records
532 gtagdata(l_counter).TagName := 'RT9999_03';
533 gtagdata(l_counter).TagValue := lpad(to_char(l_rec_counter),7,'0');
534 l_counter := l_counter + 1;
535
536 gtagdata(l_counter).TagName := 'REC_9999';
537 gtagdata(l_counter).TagValue := 'REC_9999_END';
538 l_counter := l_counter + 1;
539
540 if(l_flag=0) then -- output the message when there are no 2101 records reported
541 fnd_file.put_line(fnd_file.LOG,HR_DK_UTILITY.GET_MESSAGE('PAY','PAY_377104_DK_TCR'));
542 end if;
543
544
545 hr_utility.set_location('After populating pl/sql table',30);
546
547
548 WritetoCLOB (p_xml );
549
550 exception
551 when NO_E_INCOME_DATA_SUPPLIER then
552 null;
553
554
555 END GET_DATA;
556
557 -----------------------------------------------------------------------------------------------------------------
558 PROCEDURE WritetoCLOB(p_xfdf_clob out nocopy CLOB) is
559 l_xfdf_string clob;
560 l_str1 varchar2(1000);
561 l_str2 varchar2(20);
562 l_str3 varchar2(20);
563 l_str4 varchar2(20);
564 l_str5 varchar2(20);
565 l_str6 varchar2(30);
566 l_str7 varchar2(1000);
567 l_str8 varchar2(240);
568 l_str9 varchar2(240);
569
570 current_index pls_integer;
571 l_IANA_charset VARCHAR2 (50);
572
573 BEGIN
574 l_IANA_charset :=PAY_DK_GENERAL.get_IANA_charset ;
575 hr_utility.set_location('Entering WritetoCLOB ',70);
576 l_str1 := '<?xml version="1.0" encoding="'||l_IANA_charset||'"?> <ROOT><EINR>' ;
577 l_str2 := '<';
578 l_str3 := '>';
579 l_str4 := '</';
580 l_str5 := '>';
581 l_str6 := '</EINR></ROOT>';
582 l_str7 := '<?xml version="1.0" encoding="'||l_IANA_charset||'"?> <ROOT></ROOT>';
583
584 --fnd_file.put_line(fnd_file.log,'wc1');
585
586 dbms_lob.createtemporary(l_xfdf_string,FALSE,DBMS_LOB.CALL);
587 dbms_lob.open(l_xfdf_string,dbms_lob.lob_readwrite);
588
589 current_index := 0;
590
591 --fnd_file.put_line(fnd_file.log,'wc2');
592
593 IF gtagdata.count > 0 THEN
594
595 dbms_lob.writeAppend( l_xfdf_string, length(l_str1), l_str1 );
596
597 FOR table_counter IN gtagdata.FIRST .. gtagdata.LAST LOOP
598
599 l_str8 := gtagdata(table_counter).TagName;
600 l_str9 := gtagdata(table_counter).TagValue ;
601
602 IF l_str9 IN ('REC_1000','REC_1000_END','REC_2001','REC_2001_END','REC_2101','REC_2101_END',
603 'REC_8001','REC_8001_END','REC_9999','REC_9999_END') THEN
604
605 --fnd_file.put_line(fnd_file.log,'wc4');
606 IF l_str9 IN ('REC_1000','REC_2001','REC_2101','REC_8001','REC_9999') THEN
607 dbms_lob.writeAppend(l_xfdf_string, length(l_str2), l_str2);
608 dbms_lob.writeAppend(l_xfdf_string, length(l_str8), l_str8);
609 dbms_lob.writeAppend(l_xfdf_string, length(l_str3), l_str3);
610 ELSE
611 dbms_lob.writeAppend(l_xfdf_string, length(l_str4), l_str4);
612 dbms_lob.writeAppend(l_xfdf_string, length(l_str8), l_str8);
613 dbms_lob.writeAppend(l_xfdf_string, length(l_str5), l_str5);
614 END IF;
615
616 ELSE
617
618 if l_str9 is not null then
619 l_str9 := hr_dk_utility.REPLACE_SPECIAL_CHARS(l_str9);
620
621 dbms_lob.writeAppend(l_xfdf_string, length(l_str2), l_str2);
622 dbms_lob.writeAppend(l_xfdf_string, length(l_str8), l_str8);
623 dbms_lob.writeAppend(l_xfdf_string, length(l_str3), l_str3);
624 dbms_lob.writeAppend(l_xfdf_string, length(l_str9), l_str9);
625 dbms_lob.writeAppend(l_xfdf_string, length(l_str4), l_str4);
626 dbms_lob.writeAppend(l_xfdf_string, length(l_str8), l_str8);
627 dbms_lob.writeAppend(l_xfdf_string, length(l_str5), l_str5);
628
629 else
630
631 dbms_lob.writeAppend(l_xfdf_string, length(l_str2), l_str2);
632 dbms_lob.writeAppend(l_xfdf_string, length(l_str8), l_str8);
633 dbms_lob.writeAppend(l_xfdf_string, length(l_str3), l_str3);
634 dbms_lob.writeAppend(l_xfdf_string, length(l_str4), l_str4);
635 dbms_lob.writeAppend(l_xfdf_string, length(l_str8), l_str8);
636 dbms_lob.writeAppend(l_xfdf_string, length(l_str5), l_str5);
637
638 end if;
639
640 END IF;
641
642
643 END LOOP;
644
645 dbms_lob.writeAppend(l_xfdf_string, length(l_str6), l_str6 );
646
647 ELSE
648 dbms_lob.writeAppend(l_xfdf_string, length(l_str7), l_str7 );
649 END IF;
650
651
652 --fnd_file.put_line(fnd_file.log,'wc5');
653 p_xfdf_clob := l_xfdf_string;
654
655 hr_utility.set_location('Leaving WritetoCLOB ',40);
656
657 EXCEPTION
658 WHEN OTHERS then
659 HR_UTILITY.TRACE('sqlerrm ' || SQLERRM);
660 HR_UTILITY.RAISE_ERROR;
661 END WritetoCLOB;
662 -------------------------------------------------------------------------------------------------------------------------
663
664 END PAY_DK_TCR;