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