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