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