[Home] [Help]
PACKAGE BODY: APPS.PAY_SE_CWCR
Source
1 PACKAGE BODY PAY_SE_CWCR AS
2 /* $Header: pysecwcr.pkb 120.5.12020000.2 2012/07/04 05:47:34 asudhaka ship $ */
3
4
5 PROCEDURE get_digit_breakup(
6 p_number IN NUMBER,
7 p_digit1 OUT NOCOPY NUMBER,
8 p_digit2 OUT NOCOPY NUMBER,
9 p_digit3 OUT NOCOPY NUMBER,
10 p_digit4 OUT NOCOPY NUMBER,
11 p_digit5 OUT NOCOPY NUMBER,
12 p_digit6 OUT NOCOPY NUMBER,
13 p_digit7 OUT NOCOPY NUMBER,
14 p_digit8 OUT NOCOPY NUMBER,
15 p_digit9 OUT NOCOPY NUMBER,
16 p_digit10 OUT NOCOPY NUMBER
17 )
18 IS
19
20 TYPE digits IS
21 TABLE OF NUMBER
22 INDEX BY BINARY_INTEGER;
23 l_digit digits;
24 l_count NUMBER :=1;
25 l_number number(10);
26 BEGIN
27 l_number:=floor(p_number);
28 FOR I in 1..10 loop
29 l_digit(I):=null;
30 END loop;
31
32 WHILE l_number >= 1 LOOP
33
34 SELECT mod(l_number,10) INTO l_digit(l_count) from dual;
35 l_number:=floor(l_number/10);
36 l_count:=l_count+1;
37 END LOOP;
38
39 SELECT floor(l_number) INTO l_digit(l_number) from dual;
40 p_digit1:=l_digit(1);
41 p_digit2:=l_digit(2);
42 p_digit3:=l_digit(3);
43 p_digit4:=l_digit(4);
44 p_digit5:=l_digit(5);
45 p_digit6:=l_digit(6);
46 p_digit7:=l_digit(7);
47 p_digit8:=l_digit(8);
48 p_digit9:=l_digit(9);
49 p_digit10:=l_digit(10);
50 END get_digit_breakup;
51
52
53
54 PROCEDURE GET_DATA (
55 p_business_group_id IN NUMBER,
56 p_payroll_action_id IN VARCHAR2 ,
57 p_template_name IN VARCHAR2,
58 p_xml OUT NOCOPY CLOB
59 )
60
61 IS
62
63 /* Start of declaration*/
64
65 -- Variables needed for the report
66 l_counter number := 0;
67 l_payroll_action_id PAY_ACTION_INFORMATION. ACTION_INFORMATION1%TYPE;
68 l_digit1 NUMBER(1);
69 l_digit2 NUMBER(1);
70 l_digit3 NUMBER(1);
71 l_digit4 NUMBER(1);
72 l_digit5 NUMBER(1);
73 l_digit6 NUMBER(1);
74 l_digit7 NUMBER(1);
75 l_digit8 NUMBER(1);
76 l_digit9 NUMBER(1);
77 l_digit10 NUMBER(1);
78 l_person_number VARCHAR2(20);
79
80
81 /* End of declaration*/
82
83 /* Cursors */
84
85 Cursor csr_cwcr_header_rpt(csr_v_pa_id PAY_ACTION_INFORMATION.ACTION_INFORMATION1%TYPE)
86 IS
87 SELECT *
88 FROM PAY_ACTION_INFORMATION
89 WHERE ACTION_CONTEXT_TYPE = 'PA'
90 AND ACTION_CONTEXT_ID = csr_v_pa_id
91 AND ACTION_INFORMATION_CATEGORY='EMEA REPORT DETAILS'
92 AND ACTION_INFORMATION1='PYSECWCA';
93
94
95 Cursor csr_cwcr_b1(csr_v_pa_id PAY_ACTION_INFORMATION.ACTION_INFORMATION1%TYPE )
96 IS
97 SELECT *
98 FROM PAY_ACTION_INFORMATION
99 WHERE ACTION_CONTEXT_TYPE = 'PA'
100 AND ACTION_CONTEXT_ID = csr_v_pa_id
101 AND ACTION_INFORMATION1='PYSECWCA'
102 AND ACTION_INFORMATION2='CWC1';
103
104 rg_cwcr_b1 csr_cwcr_b1%rowtype;
105
106 Cursor csr_cwcr_b2(csr_v_pa_id PAY_ACTION_INFORMATION.ACTION_INFORMATION1%TYPE )
107 IS
108 SELECT *
109 FROM PAY_ACTION_INFORMATION
110 WHERE ACTION_CONTEXT_TYPE = 'PA'
111 AND ACTION_CONTEXT_ID = csr_v_pa_id
112 AND ACTION_INFORMATION1='PYSECWCA'
113 AND ACTION_INFORMATION2='CWC2'
114 ORDER BY ACTION_INFORMATION10 asc , ACTION_INFORMATION3 asc ;
115
116 Cursor csr_cwcr_b3(csr_v_pa_id PAY_ACTION_INFORMATION.ACTION_INFORMATION1%TYPE, csr_v_month VARCHAR2, csr_v_year VARCHAR2 ) --14219425
117 IS
118 SELECT *
119 FROM PAY_ACTION_INFORMATION
120 WHERE ACTION_CONTEXT_TYPE = 'PA'
121 AND ACTION_CONTEXT_ID = csr_v_pa_id
122 AND ACTION_INFORMATION1='PYSECWCA'
123 AND ACTION_INFORMATION2='CWC3'
124 AND ACTION_INFORMATION3 = csr_v_month
125 AND ACTION_INFORMATION10 = csr_v_year ;
126
127 Cursor csr_cwcr_b4(csr_v_pa_id PAY_ACTION_INFORMATION.ACTION_INFORMATION1%TYPE, csr_v_month VARCHAR2, csr_v_year VARCHAR2 ) --14219425
128 IS
129 SELECT *
130 FROM PAY_ACTION_INFORMATION
131 WHERE ACTION_CONTEXT_TYPE = 'PA'
132 AND ACTION_CONTEXT_ID = csr_v_pa_id
133 AND ACTION_INFORMATION1='PYSECWCA'
134 AND ACTION_INFORMATION2='CWC4'
135 AND ACTION_INFORMATION3 = csr_v_month
136 AND ACTION_INFORMATION10 = csr_v_year ;
137
138 Cursor csr_cwcr_b5(csr_v_pa_id PAY_ACTION_INFORMATION.ACTION_INFORMATION1%TYPE, csr_v_month VARCHAR2, csr_v_year VARCHAR2 ) --14219425
139 IS
140 SELECT *
141 FROM PAY_ACTION_INFORMATION
142 WHERE ACTION_CONTEXT_TYPE = 'PA'
143 AND ACTION_CONTEXT_ID = csr_v_pa_id
144 AND ACTION_INFORMATION1='PYSECWCA'
145 AND ACTION_INFORMATION2='CWC5'
146 AND ACTION_INFORMATION3 = csr_v_month
147 AND ACTION_INFORMATION10 = csr_v_year ;
148
149 -- Bug# 9222739 fix starts
150 Cursor csr_cwcr_b6(csr_v_pa_id PAY_ACTION_INFORMATION.ACTION_INFORMATION1%TYPE, csr_v_month VARCHAR2, csr_v_year VARCHAR2 ) --14219425
151 IS
152 SELECT *
153 FROM PAY_ACTION_INFORMATION
154 WHERE ACTION_CONTEXT_TYPE = 'PA'
155 AND ACTION_CONTEXT_ID = csr_v_pa_id
156 AND ACTION_INFORMATION1='PYSECWCA'
157 AND ACTION_INFORMATION2='CWC6'
158 AND ACTION_INFORMATION3 = csr_v_month
159 AND ACTION_INFORMATION10 = csr_v_year ;
160 -- Bug# 9222739 fix ends
161
162 /* End of Cursors */
163
164 BEGIN
165
166 IF p_payroll_action_id IS NULL THEN
167
168 BEGIN
169
170 SELECT payroll_action_id
171 INTO l_payroll_action_id
172 FROM pay_payroll_actions ppa,
173 fnd_conc_req_summary_v fcrs,
174 fnd_conc_req_summary_v fcrs1
175 WHERE fcrs.request_id = FND_GLOBAL.CONC_REQUEST_ID
176 AND fcrs.priority_request_id = fcrs1.priority_request_id
177 AND ppa.request_id between fcrs1.request_id and fcrs.request_id
178 AND ppa.request_id = fcrs1.request_id;
179
180 EXCEPTION
181 WHEN others THEN
182 NULL;
183 END ;
184
185 ELSE
186
187 l_payroll_action_id :=p_payroll_action_id;
188
189 END IF;
190
191 hr_utility.set_location('Entered Procedure GETDATA',10);
192
193 FOR rg_cwcr_header_rpt IN csr_cwcr_header_rpt( l_payroll_action_id)
194 LOOP
195
196
197 OPEN csr_cwcr_b1( l_payroll_action_id);
198 FETCH csr_cwcr_b1 INTO rg_cwcr_b1;
199 CLOSE csr_cwcr_b1;
200
201 gtagdata(l_counter).TagName := 'PERSON';
202 gtagdata(l_counter).TagValue := 'PERSON';
203 l_counter := l_counter + 1;
204
205 --gtagdata(l_counter).TagName := 'EMP_NUM';
206 --gtagdata(l_counter).TagValue := rg_cwcr_b1.action_information3;
207 --l_counter := l_counter + 1;
208
209
210 l_person_number := replace(TO_CHAR(rg_cwcr_b1.action_information3),'-','');
211 ---------------------------------------------------------------------------------------------------------------
212 --New Format of Person Number (of Ten Digits)
213 ---------------------------------------------------------------------------------------------------------------
214 --add_tag_value ('PERSON_NUMBER', lr_wtc_person1.person_number);
215
216 get_digit_breakup(FND_NUMBER.CANONICAL_TO_NUMBER(l_person_number),l_digit1,l_digit2,l_digit3,l_digit4,l_digit5,l_digit6,l_digit7,l_digit8,l_digit9,l_digit10);
217 gtagdata (l_counter).tagname := 'PN1';
218 gtagdata (l_counter).tagvalue := TO_CHAR (l_digit1);
219 l_counter := l_counter
220 + 1;
221 gtagdata (l_counter).tagname := 'PN2';
222 gtagdata (l_counter).tagvalue := TO_CHAR (l_digit2);
223 l_counter := l_counter
224 + 1;
225 gtagdata (l_counter).tagname := 'PN3';
226 gtagdata (l_counter).tagvalue := TO_CHAR (l_digit3);
227 l_counter := l_counter
228 + 1;
229 gtagdata (l_counter).tagname := 'PN4';
230 gtagdata (l_counter).tagvalue := TO_CHAR (l_digit4);
231 l_counter := l_counter
232 + 1;
233 gtagdata (l_counter).tagname := 'PN5';
234 gtagdata (l_counter).tagvalue := TO_CHAR (l_digit5);
235 l_counter := l_counter
236 + 1;
237 gtagdata (l_counter).tagname := 'PN6';
238 gtagdata (l_counter).tagvalue := TO_CHAR (l_digit6);
239 l_counter := l_counter
240 + 1;
241 gtagdata (l_counter).tagname := 'PN7';
242 gtagdata (l_counter).tagvalue := TO_CHAR (l_digit7);
243 l_counter := l_counter
244 + 1;
245 gtagdata (l_counter).tagname := 'PN8';
246 gtagdata (l_counter).tagvalue := TO_CHAR (l_digit8);
247 l_counter := l_counter
248 + 1;
249 gtagdata (l_counter).tagname := 'PN9';
250 gtagdata (l_counter).tagvalue := TO_CHAR (l_digit9);
251 l_counter := l_counter
252 + 1;
253 gtagdata (l_counter).tagname := 'PN10';
254 gtagdata (l_counter).tagvalue := TO_CHAR (l_digit10);
255 l_counter := l_counter
256 + 1;
257 ------------------------------------------------------------------------------------------------------------------
258
259
260
261
262 gtagdata(l_counter).TagName := 'EMP_LNAME';
263 gtagdata(l_counter).TagValue := rg_cwcr_b1.action_information4;
264 l_counter := l_counter + 1;
265
266 gtagdata(l_counter).TagName := 'EMP_FNAME';
267 gtagdata(l_counter).TagValue := rg_cwcr_b1.action_information5;
268 l_counter := l_counter + 1;
269
270 FOR rg_cwcr_b2 IN csr_cwcr_b2( l_payroll_action_id)
271 LOOP
272
273
274 gtagdata(l_counter).TagName := 'B1';
275 gtagdata(l_counter).TagValue := 'B1';
276 l_counter := l_counter + 1;
277
278 /* gtagdata(l_counter).TagName := 'YR';
279 gtagdata(l_counter).TagValue := rg_cwcr_b2.action_information10;
280 l_counter := l_counter + 1;
281
282 gtagdata(l_counter).TagName := 'MTH';
283 gtagdata(l_counter).TagValue := rg_cwcr_b2.action_information3;
284 l_counter := l_counter + 1;
285 */
286
287
288 gtagdata(l_counter).TagName := 'YRMTH'; -- EOY 2008
289 gtagdata(l_counter).TagValue := rg_cwcr_b2.action_information10||rg_cwcr_b2.action_information3;
290 l_counter := l_counter + 1;
291
292 gtagdata(l_counter).TagName := 'DAYS_WORKED';
293 gtagdata(l_counter).TagValue := rg_cwcr_b2.action_information12;
294 l_counter := l_counter + 1;
295
296 gtagdata(l_counter).TagName := 'AH';
297 gtagdata(l_counter).TagValue := rg_cwcr_b2.action_information9;
298 l_counter := l_counter + 1;
299
300
301 gtagdata(l_counter).TagName := 'AHW';
302 gtagdata(l_counter).TagValue := rg_cwcr_b2.action_information4;
303 l_counter := l_counter + 1;
304
305 gtagdata(l_counter).TagName := 'OH';
306 gtagdata(l_counter).TagValue := rg_cwcr_b2.action_information7;
307 l_counter := l_counter + 1;
308
309 gtagdata(l_counter).TagName := 'OHW';
313 gtagdata(l_counter).TagName := 'RDH';
310 gtagdata(l_counter).TagValue := rg_cwcr_b2.action_information8;
311 l_counter := l_counter + 1;
312
314 gtagdata(l_counter).TagValue := rg_cwcr_b2.action_information5;
315 l_counter := l_counter + 1;
316
317 gtagdata(l_counter).TagName := 'RDHW';
318 gtagdata(l_counter).TagValue := rg_cwcr_b2.action_information6;
319 l_counter := l_counter + 1;
320
321 gtagdata(l_counter).TagName := 'RDMW';
322 gtagdata(l_counter).TagValue := rg_cwcr_b2.action_information11;
323 l_counter := l_counter + 1;
324
325
326 -- Moving the Tags inside the loops Bug - 7605691
327 -- gtagdata(l_counter).TagName := 'B2';
328 -- gtagdata(l_counter).TagValue := 'B2';
329 -- l_counter := l_counter + 1;
330
331 FOR rg_cwcr_b3 IN csr_cwcr_b3( l_payroll_action_id, rg_cwcr_b2.action_information3, rg_cwcr_b2.action_information10 )
332 LOOP
333
334 gtagdata(l_counter).TagName := 'B2';
335 gtagdata(l_counter).TagValue := 'B2';
336 l_counter := l_counter + 1;
337
338
339 gtagdata(l_counter).TagName := 'OCK';
340 gtagdata(l_counter).TagValue := rg_cwcr_b3.action_information4;
341 l_counter := l_counter + 1;
342
343 gtagdata(l_counter).TagName := 'OCA';
344 gtagdata(l_counter).TagValue := rg_cwcr_b3.action_information5;
345 l_counter := l_counter + 1;
346
347
348 gtagdata(l_counter).TagName := 'B2';
349 gtagdata(l_counter).TagValue := 'B2_END';
350 l_counter := l_counter + 1;
351
352 END LOOP;
353
354 -- Moving the Tags inside the loops Bug - 7605691
355 -- gtagdata(l_counter).TagName := 'B2';
356 -- gtagdata(l_counter).TagValue := 'B2_END';
357 -- l_counter := l_counter + 1;
358
359 -- Moving the Tags inside the loops Bug - 7605691
360 -- gtagdata(l_counter).TagName := 'B3';
361 -- gtagdata(l_counter).TagValue := 'B3';
362 -- l_counter := l_counter + 1;
363
364
365 FOR rg_cwcr_b4 IN csr_cwcr_b4( l_payroll_action_id, rg_cwcr_b2.action_information3, rg_cwcr_b2.action_information10 )
366 LOOP
367
368
369 gtagdata(l_counter).TagName := 'B3';
370 gtagdata(l_counter).TagValue := 'B3';
371 l_counter := l_counter + 1;
372
373
374 gtagdata(l_counter).TagName := 'OVK';
375 gtagdata(l_counter).TagValue := rg_cwcr_b4.action_information4;
376 l_counter := l_counter + 1;
377
378 gtagdata(l_counter).TagName := 'OVA';
379 gtagdata(l_counter).TagValue := rg_cwcr_b4.action_information5;
380 l_counter := l_counter + 1;
381
382
383 gtagdata(l_counter).TagName := 'B3';
384 gtagdata(l_counter).TagValue := 'B3_END';
385 l_counter := l_counter + 1;
386
387
388
389 END LOOP;
390
391 -- Moving the Tags inside the loops Bug - 7605691
392 -- gtagdata(l_counter).TagName := 'B3';
393 -- gtagdata(l_counter).TagValue := 'B3_END';
394 -- l_counter := l_counter + 1;
395
396 -- Moving the Tags inside the loops Bug - 7605691
397 --gtagdata(l_counter).TagName := 'B4';
398 --gtagdata(l_counter).TagValue := 'B4';
399 --l_counter := l_counter + 1;
400
401 FOR rg_cwcr_b5 IN csr_cwcr_b5( l_payroll_action_id, rg_cwcr_b2.action_information3, rg_cwcr_b2.action_information10 )
402 LOOP
403
404 gtagdata(l_counter).TagName := 'B4';
405 gtagdata(l_counter).TagValue := 'B4';
406 l_counter := l_counter + 1;
407
408
409 gtagdata(l_counter).TagName := 'ADK';
410 gtagdata(l_counter).TagValue := rg_cwcr_b5.action_information4;
411 l_counter := l_counter + 1;
412
413 gtagdata(l_counter).TagName := 'ADA';
414 gtagdata(l_counter).TagValue := rg_cwcr_b5.action_information5;
415 l_counter := l_counter + 1;
416
417 gtagdata(l_counter).TagName := 'B4';
418 gtagdata(l_counter).TagValue := 'B4_END';
419 l_counter := l_counter + 1;
420
421 END LOOP;
422
423 -- Moving the Tags inside the loops Bug -7605691
424 --gtagdata(l_counter).TagName := 'B4';
425 --gtagdata(l_counter).TagValue := 'B4_END';
426 --l_counter := l_counter + 1;
427
428 -- Bug# 9222739 fix starts
429 FOR rg_cwcr_b6 IN csr_cwcr_b6( l_payroll_action_id, rg_cwcr_b2.action_information3, rg_cwcr_b2.action_information10 )
430 LOOP
431
432 gtagdata(l_counter).TagName := 'B5';
433 gtagdata(l_counter).TagValue := 'B5';
434 l_counter := l_counter + 1;
435
436
437 gtagdata(l_counter).TagName := 'SPH';
438 gtagdata(l_counter).TagValue := rg_cwcr_b6.action_information4;
439 l_counter := l_counter + 1;
440
441 gtagdata(l_counter).TagName := 'SP';
442 gtagdata(l_counter).TagValue := rg_cwcr_b6.action_information5;
443 l_counter := l_counter + 1;
444
445 gtagdata(l_counter).TagName := 'B5';
446 gtagdata(l_counter).TagValue := 'B5_END';
447 l_counter := l_counter + 1;
448
449 END LOOP;
450 -- Bug# 9222739 fix ends
451
452 gtagdata(l_counter).TagName := 'B1';
453 gtagdata(l_counter).TagValue := 'B1_END';
454 l_counter := l_counter + 1;
455
456
457 END LOOP;
458
459
460 gtagdata(l_counter).TagName := 'EMP_NAME';
461 gtagdata(l_counter).TagValue := rg_cwcr_b1.action_information6;
462 l_counter := l_counter + 1;
463
467
464 gtagdata(l_counter).TagName := 'ORG_NUMBER';
465 gtagdata(l_counter).TagValue := rg_cwcr_b1.action_information7;
466 l_counter := l_counter + 1;
468 gtagdata(l_counter).TagName := 'EMP_ADDR1';
469 gtagdata(l_counter).TagValue := rg_cwcr_b1.action_information9;
470 l_counter := l_counter + 1;
471
472 gtagdata(l_counter).TagName := 'EMP_ADDR2';
473 gtagdata(l_counter).TagValue := rg_cwcr_b1.action_information10;
474 l_counter := l_counter + 1;
475
476
477 gtagdata(l_counter).TagName := 'EMP_ADDR3';
478 gtagdata(l_counter).TagValue := rg_cwcr_b1.action_information11;
479 l_counter := l_counter + 1;
480
481 gtagdata(l_counter).TagName := 'TOWN_CITY';
482 gtagdata(l_counter).TagValue := rg_cwcr_b1.action_information13;
483 l_counter := l_counter + 1;
484
485 gtagdata(l_counter).TagName := 'POSTAL_CODE';
486 gtagdata(l_counter).TagValue := rg_cwcr_b1.action_information12;
487 l_counter := l_counter + 1;
488
489 gtagdata(l_counter).TagName := 'PHONE_NUMBER';
490 gtagdata(l_counter).TagValue := rg_cwcr_b1.action_information17;
491 l_counter := l_counter + 1;
492
493 gtagdata(l_counter).TagName := 'PERSON';
494 gtagdata(l_counter).TagValue := 'PERSON_END';
495 l_counter := l_counter + 1;
496
497 END LOOP;
498
499 hr_utility.set_location('After populating pl/sql table',30);
500
501
502 WritetoCLOB (p_xml );
503
504
505 END GET_DATA;
506
507 -----------------------------------------------------------------------------------------------------------------
508 PROCEDURE WritetoCLOB(p_xfdf_clob out nocopy CLOB) is
509 l_xfdf_string clob;
510 l_str1 varchar2(1000);
511 l_str2 varchar2(20);
512 l_str3 varchar2(20);
513 l_str4 varchar2(20);
514 l_str5 varchar2(20);
515 l_str6 varchar2(30);
516 l_str7 varchar2(1000);
517 l_str8 varchar2(240);
518 l_str9 varchar2(240);
519 l_str10 varchar2(20);
520 l_str11 varchar2(20);
521
522 current_index pls_integer;
523 l_IANA_charset VARCHAR2 (50);
524
525 BEGIN
526 l_IANA_charset :=hr_se_utility.get_IANA_charset ;
527 hr_utility.set_location('Entering WritetoCLOB ',70);
528 l_str1 := '<?xml version="1.0" encoding="'||l_IANA_charset||'"?> <ROOT>' ;
529 l_str2 := '<';
530 l_str3 := '>';
531 l_str4 := '</';
532 l_str5 := '>';
533 l_str6 := '</ROOT>';
534 l_str7 := '<?xml version="1.0" encoding="'||l_IANA_charset||'"?> <ROOT></ROOT>';
535 l_str10 := '<PERSON>';
536 l_str11 := '</PERSON>';
537
538
539 dbms_lob.createtemporary(l_xfdf_string,FALSE,DBMS_LOB.CALL);
540 dbms_lob.open(l_xfdf_string,dbms_lob.lob_readwrite);
541
542 current_index := 0;
543
544 IF gtagdata.count > 0 THEN
545
546 dbms_lob.writeAppend( l_xfdf_string, length(l_str1), l_str1 );
547
548
549 FOR table_counter IN gtagdata.FIRST .. gtagdata.LAST LOOP
550
551 l_str8 := gtagdata(table_counter).TagName;
552
553 l_str9 := gtagdata(table_counter).TagValue;
554
555
556
557 -- Bug# 9222739 fix starts
558 IF l_str9 IN ('PERSON' ,'PERSON_END','B1',
559 'B1_END','B2','B2_END','B3','B3_END','B4','B4_END','B5','B5_END') THEN
560
561 IF l_str9 IN ('PERSON' ,'B1','B2','B3','B4','B5') THEN
562 -- Bug# 9222739 fix ends
563 dbms_lob.writeAppend(l_xfdf_string, length(l_str2), l_str2);
564 dbms_lob.writeAppend(l_xfdf_string, length(l_str8), l_str8);
565 dbms_lob.writeAppend(l_xfdf_string, length(l_str3), l_str3);
566 ELSE
567 dbms_lob.writeAppend(l_xfdf_string, length(l_str4), l_str4);
568 dbms_lob.writeAppend(l_xfdf_string, length(l_str8), l_str8);
569 dbms_lob.writeAppend(l_xfdf_string, length(l_str5), l_str5);
570 END IF;
571
572 ELSE
573
574 if l_str9 is not null then
575
576 l_str9 := '<![CDATA[' || l_str9 || ']]>';
577
578 dbms_lob.writeAppend(l_xfdf_string, length(l_str2), l_str2);
579 dbms_lob.writeAppend(l_xfdf_string, length(l_str8), l_str8);
580 dbms_lob.writeAppend(l_xfdf_string, length(l_str3), l_str3);
581 dbms_lob.writeAppend(l_xfdf_string, length(l_str9), l_str9);
582 dbms_lob.writeAppend(l_xfdf_string, length(l_str4), l_str4);
583 dbms_lob.writeAppend(l_xfdf_string, length(l_str8), l_str8);
584 dbms_lob.writeAppend(l_xfdf_string, length(l_str5), l_str5);
585 else
586
587 dbms_lob.writeAppend(l_xfdf_string, length(l_str2), l_str2);
588 dbms_lob.writeAppend(l_xfdf_string, length(l_str8), l_str8);
589 dbms_lob.writeAppend(l_xfdf_string, length(l_str3), l_str3);
590 dbms_lob.writeAppend(l_xfdf_string, length(l_str4), l_str4);
591 dbms_lob.writeAppend(l_xfdf_string, length(l_str8), l_str8);
592 dbms_lob.writeAppend(l_xfdf_string, length(l_str5), l_str5);
593
594 end if;
595
596 END IF;
597
598 END LOOP;
599
600 dbms_lob.writeAppend(l_xfdf_string, length(l_str6), l_str6 );
601
602 ELSE
603 dbms_lob.writeAppend(l_xfdf_string, length(l_str7), l_str7 );
604 END IF;
605
606 p_xfdf_clob := l_xfdf_string;
607
608 hr_utility.set_location('Leaving WritetoCLOB ',40);
609
610 EXCEPTION
611 WHEN OTHERS then
612 HR_UTILITY.TRACE('sqlerrm ' || SQLERRM);
613 HR_UTILITY.RAISE_ERROR;
614 END WritetoCLOB;
615 -------------------------------------------------------------------------------------------------------------------------
616
617 END PAY_SE_CWCR;