[Home] [Help]
PACKAGE BODY: APPS.PAY_AE_CHEQUE_REPORT
Source
1 PACKAGE BODY pay_ae_cheque_report AS
2 /* $Header: pyaechqr.pkb 120.0.12000000.1 2007/02/23 09:23:06 vbattu noship $ */
3
4
5 -------------------------------------------------------------------------------------------
6 lg_format_mask varchar2(50);
7
8 PROCEDURE set_currency_mask
9 (p_business_group_id IN NUMBER) IS
10 /* Cursor to retrieve Currency */
11 CURSOR csr_currency IS
12 SELECT org_information10
13 FROM hr_organization_information
14 WHERE organization_id = p_business_group_id
15 AND org_information_context = 'Business Group Information';
16 l_currency VARCHAR2(40);
17 BEGIN
18 OPEN csr_currency;
19 FETCH csr_currency into l_currency;
20 CLOSE csr_currency;
21 lg_format_mask := FND_CURRENCY.GET_FORMAT_MASK(l_currency,40);
22 END set_currency_mask;
23 -------------------------------------------------------------------------------------------
24 FUNCTION get_lookup_meaning
25 (p_lookup_type varchar2
26 ,p_lookup_code varchar2)
27 RETURN VARCHAR2 IS
28
29 CURSOR csr_lookup IS
30 select meaning
31 from hr_lookups
32 where lookup_type = p_lookup_type
33 and lookup_code = p_lookup_code;
34 l_meaning hr_lookups.meaning%type;
35
36 BEGIN
37
38 OPEN csr_lookup;
39 FETCH csr_lookup INTO l_Meaning;
40 CLOSE csr_lookup;
41
42 RETURN l_meaning;
43
44 END get_lookup_meaning;
45 ------------------------------------------------------------------------------------------
46 PROCEDURE CHEQUE_LISTING
47 (p_request_id NUMBER
48 ,p_report VARCHAR2
49 ,p_business_group_id NUMBER
50 ,p_pact_id NUMBER
51 ,p_sort VARCHAR2
52 ,l_xfdf_blob OUT NOCOPY BLOB
53 )
54 AS
55
56
57 /* Cursor to fetch date_earned for the payroll action */
58 CURSOR csr_get_DE (l_pact_id number) IS
59 SELECT nvl(date_earned,effective_date)
60 FROM pay_payroll_actions
61 WHERE payroll_action_id = l_pact_id;
62
63 /* Cursor to fetch payroll_name for the payroll action */
64 CURSOR csr_get_PY (l_pact_id number) IS
65 SELECT payroll_name
66 FROM pay_all_payrolls_f pap, pay_payroll_actions ppa
67 WHERE ppa.payroll_action_id = l_pact_id
68 AND ppa.payroll_id = pap.payroll_id;
69
70
71 /*** ORDER BY ORG ***/
72 /* Cursor for fetching assignment action id and assignment id for current payroll action id order by org name */
73
74
75 CURSOR csr_get_assact_det_ORG (l_pact_id number , l_date date) IS
76 select paa.assignment_action_id , paa.assignment_id
77 from pay_assignment_actions paa , per_all_assignments_f paf, hr_all_organization_units hou, per_all_people_f ppf
78 where paa.payroll_action_id = p_pact_id
79 and paa.action_status = 'C'
80 and not exists ( select 1
81 from pay_assignment_actions paa1, pay_payroll_actions ppa1, pay_action_interlocks lck
82 where lck.locked_action_id = paa.assignment_action_id
83 and lck.locking_action_id = paa1.assignment_action_id
84 and ppa1.payroll_action_id = paa1.payroll_action_id
85 and ppa1.action_type = 'D'
86 and ppa1.action_status = 'C'
87 and paa1.action_status = 'C')
88 and paa.assignment_id = paf.assignment_id
89 and trunc(l_date,'MM') between trunc(paf.effective_start_date,'MM') and paf.effective_end_date
90 and paf.person_id = ppf.person_id
91 and trunc(l_date,'MM') between trunc(ppf.effective_start_date,'MM') and ppf.effective_end_date
92 and paf.organization_id = hou.organization_id
93 and trunc(l_date,'MM') between trunc(hou.date_from,'MM') and nvl(hou.date_to, to_date('31/12/4712','DD/MM/YYYY'))
94 order by hou.name, ppf.full_name;
95
96
97 /*** ORDER BY EMPNO ***/
98 /* Cursor for fetching assignment action id and assignment id for current payroll action id order by emp no*/
99
100 CURSOR csr_get_assact_det_EMPNO (l_pact_id number , l_date date) IS
101 select paa.assignment_action_id , paa.assignment_id
102 from pay_assignment_actions paa , per_all_assignments_f paf, per_all_people_f ppf
103 where paa.payroll_action_id = p_pact_id
104 and paa.action_status = 'C'
105 and not exists ( select 1
106 from pay_assignment_actions paa1, pay_payroll_actions ppa1, pay_action_interlocks lck
107 where lck.locked_action_id = paa.assignment_action_id
108 and lck.locking_action_id = paa1.assignment_action_id
109 and ppa1.payroll_action_id = paa1.payroll_action_id
110 and ppa1.action_type = 'D'
111 and ppa1.action_status = 'C'
112 and paa1.action_status = 'C')
113 and paa.assignment_id = paf.assignment_id
114 and trunc(l_date,'MM') between trunc(paf.effective_start_date,'MM') and paf.effective_end_date
115 and paf.person_id = ppf.person_id
116 and trunc(l_date,'MM') between trunc(ppf.effective_start_date,'MM') and ppf.effective_end_date
117 order by ppf.employee_number;
118
119 /*** ORDER BY PAYDATE ***/
120 /* Cursor for fetching assignment action id and assignment id for current payroll action id order by payment date*/
121
122 CURSOR csr_get_assact_det_PD (l_pact_id number,l_date date) IS
123 select paa.assignment_action_id , paa.assignment_id
124 from pay_assignment_actions paa , pay_payroll_actions ppa , pay_pre_payments ppp , pay_assignment_actions paa1,
125 per_all_people_f ppf, per_all_assignments_f paf
126 where paa.payroll_action_id = p_pact_id
127 and paa.action_status = 'C'
128 and not exists ( select 1
129 from pay_assignment_actions paa1, pay_payroll_actions ppa1, pay_action_interlocks lck
130 where lck.locked_action_id = paa.assignment_action_id
131 and lck.locking_action_id = paa1.assignment_action_id
132 and ppa1.payroll_action_id = paa1.payroll_action_id
133 and ppa1.action_type = 'D'
134 and ppa1.action_status = 'C'
135 and paa1.action_status = 'C')
136 and paa.pre_payment_id = ppp.pre_payment_id
137 and ppp.assignment_action_id = paa1.assignment_action_id
138 and paa1.action_status = 'C'
139 and ppa.payroll_action_id = paa1.payroll_action_id
140 and ppa.action_status = 'C'
141 and ppa.action_type in ('P','U')
142 and paa.assignment_id = paf.assignment_id
143 and trunc(l_date,'MM') between trunc(paf.effective_start_date,'MM') and paf.effective_end_date
144 and paf.person_id = ppf.person_id
145 and trunc(l_date,'MM') between trunc(ppf.effective_start_date,'MM') and ppf.effective_end_date
146 order by nvl(ppa.date_earned,ppa.effective_date),ppf.full_name;
147
148
149 /* Cursor for fetching the details for the assignments */
150
151 CURSOR csr_get_per_det (l_assignment_id number , l_date date) IS
152 SELECT ppf.full_name , ppf.employee_number , paf.job_id , paf.organization_id
153 FROM per_all_people_f ppf, per_all_assignments_f paf
154 WHERE paf.assignment_id = l_assignment_id
155 AND paf.person_id = ppf.person_id
156 AND trunc(l_date,'MM') between trunc(paf.effective_start_date,'MM') and paf.effective_end_date
157 AND trunc(l_date,'MM') between trunc(ppf.effective_start_date,'MM') and ppf.effective_end_date;
158
159 /* Cursor for fetching the Cheque details */
160
161 CURSOR csr_chq_det (l_assact_id number,l_date date) IS
162 SELECT serial_number, value
163 FROM pay_pre_payments_v2 pv2
164 WHERE pv2.business_group_id+0 = p_business_group_id
165 AND pv2.assignment_action_id = l_assact_id
166 AND pv2.pre_payment_id in (select ppp.pre_payment_id
167 from pay_pre_payments ppp,pay_action_interlocks lck , pay_org_payment_methods_f org ,
168 pay_payment_types pt,pay_assignment_actions paa1
169 where lck.locking_action_id = l_assact_id
170 and lck.locked_action_id = ppp.assignment_action_id
171 and paa1.assignment_action_id = lck.locking_action_id
172 and paa1.pre_payment_id = ppp.pre_payment_id
173 and paa1.action_status = 'C'
174 and ppp.org_payment_method_id = org.org_payment_method_id
175 and org.payment_type_id = pt.payment_type_id
176 and pt.category = 'CH')
177 AND trunc(l_date ,'MM') between trunc(opm_effective_start_date,'MM') and opm_effective_end_date;
178
179 /* Cursor for fetching the job name */
180
181 CURSOR csr_job (l_job number, l_date date) IS
182 SELECT name
183 FROM per_jobs
184 WHERE job_id = l_job
185 AND trunc(l_date,'MM') between date_from and nvl(date_to, to_date('31/12/4712','DD/MM/YYYY'));
186
187 /* Cursor for fetching the Organization name */
188
189 CURSOR csr_org (l_org_id number,l_date date) IS
190 SELECT name
191 FROM hr_all_organization_units
192 WHERE organization_id = l_org_id
193 AND trunc(l_date,'MM') between date_from and nvl(date_to, to_date('31/12/4712','DD/MM/YYYY'));
194
195 /* Cursor for fetching the payment date of the pre payment for cheques */
196
197 CURSOR csr_pay_date (l_assact_id number) IS
198 select nvl(ppa.date_earned,ppa.effective_date)
199 from pay_payroll_actions ppa, pay_assignment_actions paa, pay_pre_payments ppp, pay_assignment_actions paa1
200 where paa.assignment_action_id = l_assact_id
201 and paa.action_status = 'C'
202 and paa.pre_payment_id = ppp.pre_payment_id
203 and ppp.assignment_action_id = paa1.assignment_action_id
204 and paa1.action_status = 'C'
205 and paa1.payroll_action_id = ppa.payroll_action_id
206 and ppa.action_type in ('P','U')
207 and ppa.action_status = 'C';
208
209
210 TYPE assact_rec IS RECORD
211 (assignment_action_id NUMBER
212 ,assignment_id NUMBER);
213
214 TYPE t_assact_table IS TABLE OF assact_rec INDEX BY BINARY_INTEGER;
215
216 t_store_assact t_assact_table;
217
218 rec_get_assact csr_get_assact_det_ORG%ROWTYPE;
219
220 l_input_date date;
221 l_effective_date date;
222
223 i number;
224 j number;
225
226 l_assact_id number;
227 l_assignment_id number;
228
229 l_date_earned date;
230
231 l_full_name varchar2(240);
232 l_employee_number varchar2(30);
233 l_organization_id number(15);
234 l_organization varchar2(240);
235 l_job varchar2(240);
236 l_job_id number(15);
237 l_amount number(15,2);
238 l_amount_fm varchar2(40);
239 l_chq_number number;
240 l_chq_date date;
241 l_pay_date date;
242 l_py_name varchar2(240);
243
244 seq number;
245
246
247 l_str_py_name varchar2(400);
248 l_str_seq varchar2(400);
249 l_str_er_name1 varchar2(400);
250 l_str_er_name2 varchar2(400);
251 l_str_er_name3 varchar2(400);
252 l_str_er_name4 varchar2(400);
253 l_str_er_name5 varchar2(400);
254 l_str_er_name6 varchar2(400);
255 l_str_er_name7 varchar2(400);
256
257 l_str_rep_label varchar2(100);
258 l_str_py_label varchar2(100);
259 l_str_n varchar2(100);
260 l_str_name_l varchar2(100);
261 l_str_amt_l varchar2(100);
262 l_str_job_l varchar2(100);
263 l_str_org_l varchar2(100);
264 l_str_eno_l varchar2(100);
265 l_str_chq_l varchar2(100);
266 l_str_pd_l varchar2(100);
267
268 l_xfdf_string CLOB;
269
270
271 BEGIN
272
273 set_currency_mask(p_business_group_id);
274
275 OPEN csr_get_DE (p_pact_id);
276 FETCH csr_get_DE into l_input_date;
277 CLOSE csr_get_DE;
278
279 OPEN csr_get_PY(p_pact_id);
280 FETCH csr_get_PY into l_py_name;
281 CLOSE csr_get_PY;
282
283 l_effective_date := last_day(l_input_date);
284
285 INSERT INTO fnd_sessions (session_id, effective_date)
286 VALUES (userenv('sessionid'), l_effective_date);
287
288 -- To clear the PL/SQL Table values.
289
290 vXMLTable.DELETE;
291 vCtr := 1;
292
293 dbms_lob.createtemporary(l_xfdf_string,FALSE,DBMS_LOB.CALL);
294 dbms_lob.open(l_xfdf_string,dbms_lob.lob_readwrite);
295 DBMS_LOB.CREATETEMPORARY(l_xfdf_blob,TRUE);
296 clob_to_blob(l_xfdf_string,l_xfdf_blob);
297 dbms_lob.writeAppend( l_xfdf_string, length('<START>'),'<START>');
298
299
300 l_str_rep_label := '<REP_LABEL>' || get_lookup_meaning('AE_FORM_LABELS','AE_CHQR') || '</REP_LABEL>';
301 dbms_lob.writeAppend( l_xfdf_string, length(l_str_rep_label),l_str_rep_label);
302
303 l_str_py_label := '<PY_LABEL>' || get_lookup_meaning('AE_FORM_LABELS','PY_LABEL') || '</PY_LABEL>';
304 dbms_lob.writeAppend( l_xfdf_string, length(l_str_py_label),l_str_py_label);
305
306 l_str_py_name := '<PY_NAME>' || l_py_name || '</PY_NAME>';
307 dbms_lob.writeAppend( l_xfdf_string, length(l_str_py_name),l_str_py_name);
308
309 l_str_n := '<N>' || get_lookup_meaning('AE_FORM_LABELS','S_NO') || '</N>';
310 dbms_lob.writeAppend( l_xfdf_string, length(l_str_n),l_str_n);
311
312 l_str_name_l := '<NAME_L>' || get_lookup_meaning('AE_FORM_LABELS','NAME') || '</NAME_L>';
313 dbms_lob.writeAppend( l_xfdf_string, length(l_str_name_l),l_str_name_l);
314
315 l_str_eno_l := '<ENO_L>' || get_lookup_meaning('AE_FORM_LABELS','EMP_NO') || '</ENO_L>';
316 dbms_lob.writeAppend( l_xfdf_string, length(l_str_eno_l),l_str_eno_l);
317
318 l_str_org_l := '<ORG_L>' || get_lookup_meaning('AE_FORM_LABELS','ORG_L') || '</ORG_L>';
319 dbms_lob.writeAppend( l_xfdf_string, length(l_str_org_l),l_str_org_l);
320
321 l_str_job_l := '<JOB_L>' || get_lookup_meaning('AE_FORM_LABELS','JOB_L') || '</JOB_L>';
322 dbms_lob.writeAppend( l_xfdf_string, length(l_str_job_l),l_str_job_l);
323
324 l_str_amt_l := '<AMT_L>' || get_lookup_meaning('AE_FORM_LABELS','AMOUNT') || '</AMT_L>';
325 dbms_lob.writeAppend( l_xfdf_string, length(l_str_amt_l),l_str_amt_l);
326
327 l_str_chq_l := '<CHQ_L>' || get_lookup_meaning('AE_FORM_LABELS','CHQ_L') || '</CHQ_L>';
328 dbms_lob.writeAppend( l_xfdf_string, length(l_str_chq_l),l_str_chq_l);
329
330 l_str_pd_l := '<PD_L>' || get_lookup_meaning('AE_FORM_LABELS','PD_L') || '</PD_L>';
331 dbms_lob.writeAppend( l_xfdf_string, length(l_str_pd_l),l_str_pd_l);
332
333
334
335 hr_utility.set_location('Entering CHQR ',10);
336
337 i := 0;
338
339 If p_sort = 'ORG' then
340
341 open csr_get_assact_det_ORG (p_pact_id,l_effective_date);
342
343 LOOP
344 FETCH csr_get_assact_det_ORG INTO rec_get_assact;
345 EXIT WHEN csr_get_assact_det_ORG%NOTFOUND ;
346
347 i := i + 1;
348
349 t_store_assact(i).assignment_action_id := rec_get_assact.assignment_action_id;
350 t_store_assact(i).assignment_id := rec_get_assact.assignment_id;
351 END LOOP;
352
353 CLOSE csr_get_assact_det_ORG;
354
355 Elsif p_sort = 'EMPNO' then
356 open csr_get_assact_det_EMPNO (p_pact_id,l_effective_date);
357
358 LOOP
359 FETCH csr_get_assact_det_EMPNO INTO rec_get_assact;
360 EXIT WHEN csr_get_assact_det_EMPNO%NOTFOUND ;
361
362 i := i + 1;
363
364 t_store_assact(i).assignment_action_id := rec_get_assact.assignment_action_id;
365 t_store_assact(i).assignment_id := rec_get_assact.assignment_id;
366 END LOOP;
367
368 CLOSE csr_get_assact_det_EMPNO;
369
370 Elsif p_sort = 'DATE' then
371 open csr_get_assact_det_PD (p_pact_id,l_effective_date);
372
373 LOOP
374 FETCH csr_get_assact_det_PD INTO rec_get_assact;
375 EXIT WHEN csr_get_assact_det_PD%NOTFOUND ;
376
377 i := i + 1;
378
379 t_store_assact(i).assignment_action_id := rec_get_assact.assignment_action_id;
380 t_store_assact(i).assignment_id := rec_get_assact.assignment_id;
381 END LOOP;
382
383 CLOSE csr_get_assact_det_PD;
384 End If;
385
386 j := 1;
387
388 seq := 1;
389
390 If i > 0 then
391
392 WHILE j <= i LOOP
393
394 l_full_name := null;
395 l_employee_number := null;
396 l_organization_id := null;
397 l_organization := null;
398 l_job := null;
399 l_job_id := null;
400 l_amount := null;
401 l_chq_number := null;
402 l_chq_date := null;
403
404 OPEN csr_get_DE (p_pact_id);
405 FETCH csr_get_DE into l_chq_date;
406 CLOSE csr_get_DE;
407
408 OPEN csr_pay_date (t_store_assact(j).assignment_action_id);
409 FETCH csr_pay_date into l_pay_date;
410 CLOSE csr_pay_date;
411
412 OPEN csr_get_per_det(t_store_assact(j).assignment_id , l_chq_date);
413 FETCH csr_get_per_det into l_full_name,l_employee_number,l_job_id,l_organization_id;
414 CLOSE csr_get_per_det;
415
416 If l_job_id is not null then
417 OPEN csr_job(l_job_id,l_chq_date);
418 FETCH csr_job into l_job;
419 CLOSE csr_job;
420 End If;
421
422 OPEN csr_org (l_organization_id,l_chq_date);
423 FETCH csr_org INTO l_organization;
424 close csr_org;
425
426 OPEN csr_chq_det (t_store_assact(j).assignment_action_id,l_chq_date);
427 LOOP
428 FETCH csr_chq_det into l_chq_number,l_amount;
429 Exit when csr_chq_det%NOTFOUND;
430
431 l_amount_fm := to_char(l_amount,lg_format_mask);
432
433 dbms_lob.writeAppend( l_xfdf_string, length('<RECORD>'),'<RECORD>');
434
435 l_str_seq := '<SNO>'||seq||'</SNO>';
436 l_str_er_name1 := '<EENAME>'||substr(l_full_name,1,60)||'</EENAME>';
437 l_str_er_name2 := '<EENO>'||l_employee_number||'</EENO>';
438 l_str_er_name3 := '<EEORG>'||substr(l_organization,1,40)||'</EEORG>';
439 l_str_er_name4 := '<EEJOB>'||substr(l_job,1,40)||'</EEJOB>';
440 l_str_er_name5 := '<EEAMOUNT>'||l_amount_fm||'</EEAMOUNT>';
441 l_str_er_name6 := '<EECHQ>'||l_chq_number||'</EECHQ>';
442 l_str_er_name7 := '<EEDATE>'||l_pay_date||'</EEDATE>';
443
444 dbms_lob.writeAppend( l_xfdf_string, length(l_str_seq), l_str_seq);
445 dbms_lob.writeAppend( l_xfdf_string, length(l_str_er_name1), l_str_er_name1);
446 dbms_lob.writeAppend( l_xfdf_string, length(l_str_er_name2), l_str_er_name2);
447 dbms_lob.writeAppend( l_xfdf_string, length(l_str_er_name3), l_str_er_name3);
448 dbms_lob.writeAppend( l_xfdf_string, length(l_str_er_name4), l_str_er_name4);
449 dbms_lob.writeAppend( l_xfdf_string, length(l_str_er_name5), l_str_er_name5);
450 dbms_lob.writeAppend( l_xfdf_string, length(l_str_er_name6), l_str_er_name6);
451 dbms_lob.writeAppend( l_xfdf_string, length(l_str_er_name7), l_str_er_name7);
452
453 dbms_lob.writeAppend( l_xfdf_string, length('</RECORD>'),'</RECORD>');
454 seq := seq + 1;
455 END LOOP;
456 CLOSE csr_chq_det;
457 j := j + 1;
458
459 END LOOP;
460 End If;
461
462 dbms_lob.writeAppend( l_xfdf_string, length('</START>'),'</START>');
463
464 hr_utility.set_location('Finished creating xml data for Procedure CHQR ',20);
465
466 DBMS_LOB.CREATETEMPORARY(l_xfdf_blob,TRUE);
467 clob_to_blob(l_xfdf_string,l_xfdf_blob);
468
469
470 /*EXCEPTION
471 WHEN utl_file.invalid_path then
472 hr_utility.set_message(8301, 'GHR_38830_INVALID_UTL_FILE_PATH');
473 fnd_file.put_line(fnd_file.log,HR_UTILITY.get_message);
474 hr_utility.raise_error;
475 --
476 WHEN utl_file.invalid_mode then
477 hr_utility.set_message(8301, 'GHR_38831_INVALID_FILE_MODE');
478 fnd_file.put_line(fnd_file.log,HR_UTILITY.get_message);
479 hr_utility.raise_error;
480 --
481 WHEN utl_file.invalid_filehandle then
482 hr_utility.set_message(8301, 'GHR_38832_INVALID_FILE_HANDLE');
483 fnd_file.put_line(fnd_file.log,HR_UTILITY.get_message);
484 hr_utility.raise_error;
485 --
486 WHEN utl_file.invalid_operation then
487 hr_utility.set_message(8301, 'GHR_38833_INVALID_OPER');
488 fnd_file.put_line(fnd_file.log,HR_UTILITY.get_message);
489 hr_utility.raise_error;
490 --
491 WHEN utl_file.read_error then
492 hr_utility.set_message(8301, 'GHR_38834_FILE_READ_ERROR');
493 fnd_file.put_line(fnd_file.log,HR_UTILITY.get_message);
494 hr_utility.raise_error;
495 --
496 WHEN others THEN
497 hr_utility.set_message(800,'FFU10_GENERAL_ORACLE_ERROR');
498 hr_utility.set_message_token('2',substr(sqlerrm,1,200));
499 fnd_file.put_line(fnd_file.log,HR_UTILITY.get_message);
500 hr_utility.raise_error;*/
501 END CHEQUE_LISTING;
502 -------------------------------------------------------------------------------------------
503
504 PROCEDURE WritetoCLOB
505 (p_xfdf_blob out nocopy blob)
506 IS
507 l_xfdf_string clob;
508 l_str1 varchar2(1000);
509 l_str2 varchar2(20);
510 l_str3 varchar2(20);
511 l_str4 varchar2(20);
512 l_str5 varchar2(20);
513 l_str6 varchar2(30);
514 l_str7 varchar2(1000);
515 l_str8 varchar2(240);
516 l_str9 varchar2(240);
517 BEGIN
518 hr_utility.set_location('Entered Procedure Write to clob ',100);
519 l_str1 := '<?xml version="1.0" encoding="UTF-8"?>
520 <xfdf xmlns="http://ns.adobe.com/xfdf/" xml:space="preserve">
521 <fields> ' ;
522 l_str2 := '<field name="';
523 l_str3 := '">';
524 l_str4 := '<value>' ;
525 l_str5 := '</value> </field>' ;
526 l_str6 := '</fields> </xfdf>';
527 l_str7 := '<?xml version="1.0" encoding="UTF-8"?>
528 <xfdf xmlns="http://ns.adobe.com/xfdf/" xml:space="preserve">
529 <fields>
530 </fields> </xfdf>';
531 dbms_lob.createtemporary(l_xfdf_string,FALSE,DBMS_LOB.CALL);
532 dbms_lob.open(l_xfdf_string,dbms_lob.lob_readwrite);
533 if vXMLTable.COUNT > 0 then
534 dbms_lob.writeAppend( l_xfdf_string, length(l_str1), l_str1 );
535 FOR ctr_table IN vXMLTable.FIRST .. vXMLTable.LAST LOOP
536 l_str8 := vXMLTable(ctr_table).TagName;
537 l_str9 := vXMLTable(ctr_table).TagValue;
538 if (l_str9 is not null) then
539 dbms_lob.writeAppend( l_xfdf_string, length(l_str2), l_str2 );
540 dbms_lob.writeAppend( l_xfdf_string, length(l_str8),l_str8);
541 dbms_lob.writeAppend( l_xfdf_string, length(l_str3), l_str3 );
542 dbms_lob.writeAppend( l_xfdf_string, length(l_str4), l_str4 );
543 dbms_lob.writeAppend( l_xfdf_string, length(l_str9), l_str9);
544 dbms_lob.writeAppend( l_xfdf_string, length(l_str5), l_str5 );
545 elsif (l_str9 is null and l_str8 is not null) then
546 dbms_lob.writeAppend(l_xfdf_string,length(l_str2),l_str2);
547 dbms_lob.writeAppend(l_xfdf_string,length(l_str8),l_str8);
548 dbms_lob.writeAppend(l_xfdf_string,length(l_str3),l_str3);
549 dbms_lob.writeAppend(l_xfdf_string,length(l_str4),l_str4);
550 dbms_lob.writeAppend(l_xfdf_string,length(l_str5),l_str5);
551 else
552 null;
553 end if;
554 END LOOP;
555 dbms_lob.writeAppend( l_xfdf_string, length(l_str6), l_str6 );
556 else
557 dbms_lob.writeAppend( l_xfdf_string, length(l_str7), l_str7 );
558 end if;
559 DBMS_LOB.CREATETEMPORARY(p_xfdf_blob,TRUE);
560 clob_to_blob(l_xfdf_string,p_xfdf_blob);
561 hr_utility.set_location('Finished Procedure Write to CLOB ,Before clob to blob ',110);
562 --return p_xfdf_blob;
563 EXCEPTION
564 WHEN OTHERS then
565 HR_UTILITY.TRACE('sqleerm ' || SQLERRM);
566 HR_UTILITY.RAISE_ERROR;
567 END WritetoCLOB;
568 ----------------------------------------------------------------
569 Procedure clob_to_blob
570 (p_clob clob,
571 p_blob IN OUT NOCOPY Blob)
572 is
573 l_length_clob number;
574 l_offset pls_integer;
575 l_varchar_buffer varchar2(32767);
576 l_raw_buffer raw(32767);
577 l_buffer_len number;
578 l_chunk_len number;
579 l_blob blob;
580 g_nls_db_char varchar2(60);
581 l_raw_buffer_len pls_integer;
582 l_blob_offset pls_integer := 1;
583 begin
584 l_buffer_len := 20000;
585 hr_utility.set_location('Entered Procedure clob to blob',120);
586 select userenv('LANGUAGE') into g_nls_db_char from dual;
587 l_length_clob := dbms_lob.getlength(p_clob);
588 l_offset := 1;
589 while l_length_clob > 0 loop
590 hr_utility.trace('l_length_clob '|| l_length_clob);
591 if l_length_clob < l_buffer_len then
592 l_chunk_len := l_length_clob;
593 else
594 l_chunk_len := l_buffer_len;
595 end if;
596 DBMS_LOB.READ(p_clob,l_chunk_len,l_offset,l_varchar_buffer);
597 fnd_file.put_line(fnd_file.log,l_varchar_buffer);
598 --l_raw_buffer := utl_raw.cast_to_raw(l_varchar_buffer);
599 l_raw_buffer := utl_raw.convert(utl_raw.cast_to_raw(l_varchar_buffer),'American_America.UTF8',g_nls_db_char);
600 l_raw_buffer_len := utl_raw.length(utl_raw.convert(utl_raw.cast_to_raw(l_varchar_buffer),'American_America.UTF8',g_nls_db_char));
601 hr_utility.trace('l_varchar_buffer '|| l_varchar_buffer);
602 --dbms_lob.write(p_blob,l_chunk_len, l_offset, l_raw_buffer);
603 dbms_lob.write(p_blob,l_raw_buffer_len, l_blob_offset, l_raw_buffer);
604 l_blob_offset := l_blob_offset + l_raw_buffer_len;
605 l_offset := l_offset + l_chunk_len;
606 l_length_clob := l_length_clob - l_chunk_len;
607 hr_utility.trace('l_length_blob '|| dbms_lob.getlength(p_blob));
608 end loop;
609 hr_utility.set_location('Finished Procedure clob to blob ',130);
610 end clob_to_blob;
611 ------------------------------------------------------------------
612 Procedure fetch_pdf_blob
613 (p_report in varchar2,
614 p_pdf_blob OUT NOCOPY blob)
615 IS
616 BEGIN
617 IF (p_report='CHQR') THEN
618 Select file_data
619 Into p_pdf_blob
620 From fnd_lobs
621 Where file_id = (select max(file_id) from fnd_lobs where file_name like '%PAY_CHQ_ar_AE.rtf');
622 END IF;
623
624 EXCEPTION
625 when no_data_found then
626 null;
627 END fetch_pdf_blob;
628 -------------------------------------------------------------------
629 END pay_ae_cheque_report;