[Home] [Help]
PACKAGE BODY: APPS.FV_FLATFILES
Source
1 PACKAGE BODY fv_flatfiles AS
2 /* $Header: FVFILCRB.pls 120.12.12010000.1 2008/07/28 06:31:04 appldev ship $*/
3 g_module_name VARCHAR2(100) ;
4
5 vp_errbuf VARCHAR2(1000);
6 vp_retcode NUMBER;
7 gbl_count number(15);
8
9 PROCEDURE main (errbuf OUT NOCOPY VARCHAR2,
10 retcode OUT NOCOPY VARCHAR2,
11 conc_prog IN VARCHAR2,
12 parameter1 IN NUMBER,
13 parameter2 IN NUMBER,
14 parameter3 IN VARCHAR2,
15 parameter4 IN VARCHAR2,
16 parameter5 IN VARCHAR2,
17 parameter6 IN VARCHAR2,
18 parameter7 IN VARCHAR2) IS
19
20 l_module_name VARCHAR2(200);
21 statement VARCHAR2(2000);
22 col1 VARCHAR2(500);
23 request_id NUMBER;
24 sob_id NUMBER;
25 entity_code VARCHAR2(50);
26 period_year NUMBER;
27 v_creditors_tin VARCHAR2(10);
28 payment_year NUMBER;
29 invoice_minimum NUMBER;
30 tape_indicator VARCHAR2(2);
31 transmitter_code VARCHAR2(5);
32
33 v_footnote_count VARCHAR2(10);
34 v_trailer_count VARCHAR2(10);
35 v_total_count VARCHAR2(10);
36 v_str varchar2(200);
37
38 BEGIN
39
40 request_id := parameter1;
41 sob_id := parameter2;
42
43 g_module_name := 'fv.plsql.FV_FLATFILES.';
44 l_module_name := g_module_name || 'main';
45 -- -----------------------------------------------------------------------------------
46 -- Create Flat File for FV1219BF
47 -- -----------------------------------------------------------------------------------
48 IF conc_prog = 'FV1219BF' THEN
49 IF (parameter3 = 'F')
50 THEN
51 FV_1219_TRANSACTIONS.gen_flat_file(parameter4, parameter5, parameter6, parameter7);
52 ELSE
53 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'The GOALS FMS 1219/1220 Program is not created when the FMS Form 1219/1220 Reports ');
54 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'request set is submitted in Preliminary mode.');
55 END IF;
56
57 -- -----------------------------------------------------------------------------------
58 -- Create Flat File for FVFCTHRC
59 -- -----------------------------------------------------------------------------------
60 ELSIF conc_prog = 'FVFCTHRC' THEN
61 statement :=
62 'SELECT facts_report_info
63 FROM fv_facts_temp fft,
64 fv_facts_submission ffs,
65 fv_treasury_symbols fts
66 WHERE fft.treasury_symbol_id = ffs.treasury_symbol_id
67 AND fts.treasury_symbol_id = fft.treasury_symbol_id
68 AND ffs.bulk_flag = ''Y''
69 AND fct_int_record_category = ''REPORTED_NEW''
70 AND fct_int_record_type = ''CNT_HDR''
71 AND ffs.Bulk_File_Sub_Id = '||request_id||
72 ' AND ffs.set_of_books_id = '||sob_id||
73 ' ORDER BY fts.treasury_symbol';
74
75 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
76 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'Creating Flat File for FVFCTHRC');
77 END IF;
78
79
80 fv_flatfiles.create_flat_file(statement);
81
82 statement :=
83 'SELECT ''TRL'' || LPAD(to_char(count(*)), 10, ''0'') || LPAD('' '', 403)
84 FROM fv_facts_submission ffs
85 WHERE ffs.bulk_flag = ''Y''
86 AND ffs.Bulk_File_Sub_Id = '||request_id||
87 ' AND ffs.set_of_books_id = '||sob_id ;
88
89 fv_flatfiles.create_flat_file(statement);
90
91 -- -----------------------------------------------------------------------------------
92 -- Create Flat File for FVXTIVC1
93 -- -----------------------------------------------------------------------------------
94 ELSIF conc_prog = 'FVXTIVC1' THEN
95 statement :=
96 'SELECT record
97 FROM fv_ecs_ncrpay_temp
98 ORDER by line_no';
99
100 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
101 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'Creating Flat File for FVXTIVC1');
102 END IF;
103
104
105 fv_flatfiles.create_flat_file(statement);
106
107 -- -----------------------------------------------------------------------------------
108 -- Create Flat File for FVTICTXR
109 -- -----------------------------------------------------------------------------------
110 /*Enhancement PMC-17 CTX Payment Format.
111 The Following Select Statement for FVTICTXR is changed.
112 After generating the Output the Temporary table is Purged*/
113
114 ELSIF conc_prog = 'FVTICTXR' THEN
115 statement :=
116 'SELECT record
117 FROM fv_payment_format_temp
118 WHERE set_of_books_id = '||parameter1||
119 ' AND org_id = '||parameter2||
120 ' AND checkrun_name = '||''''||parameter3||''''||
121 ' ORDER by line_no';
122
123 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
124 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'Creating Flat File for FVTICTXR');
125 END IF;
126
127 fv_flatfiles.create_flat_file(statement);
128 DELETE FROM fv_payment_format_temp
129 WHERE set_of_books_id = parameter1
130 AND org_id = parameter2
131 AND checkrun_name = parameter3 ;
132 COMMIT ;
133
134 -- -----------------------------------------------------------------------------------
135 -- Create Flat File for FVTIACHR
136 -- -----------------------------------------------------------------------------------
137 ELSIF conc_prog = 'FVTIACHR' THEN
138 statement :=
139 'SELECT record
140 FROM fv_ecs_ach_vendor_temp
141 ORDER by line_no';
142
143 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
144 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'Creating Flat File for FVTIACHR');
145 END IF;
146
147 fv_flatfiles.create_flat_file(statement);
148
149 -- -----------------------------------------------------------------------------------
150 -- Create Flat File for FVFCTDRC
151 -- -----------------------------------------------------------------------------------
152 ELSIF conc_prog = 'FVFCTDRC' THEN
153 statement :=
154 'SELECT facts_report_info
155 FROM fv_facts_temp fft,
156 fv_facts_submission ffs,
157 fv_treasury_symbols fts
158 WHERE fft.treasury_symbol_id = ffs.treasury_symbol_id
159 AND fts.treasury_symbol_id = fft.treasury_symbol_id
160 AND ffs.bulk_flag = ''Y''
161 AND fct_int_record_category = ''REPORTED_NEW''
162 AND fct_int_record_type = ''BLK_DTL''
163 AND ffs.Bulk_File_Sub_id = '||request_id||
164 ' AND ffs.set_of_books_id = '||sob_id||
165 ' AND fts.set_of_books_id = '||sob_id||
166 ' ORDER BY fts.treasury_symbol, fft.sgl_acct_number' ;
167
168 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
169 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'Creating Flat File for FVFCTDRC');
170 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'Detail Records...');
171 END IF;
172
173 fv_flatfiles.create_flat_file(statement);
174
175 -- Modified the foll for Facts II 2002 enhancements
176 statement :=
177 'SELECT SUBSTR(fft.facts_report_info,1,50)
178 ||''F''||SUBSTR(fft.facts_report_info,52,74)
179 ||RPAD('' '', 17)||SUBSTR(fft.facts_report_info,143,1)
180 ||LPAD(TO_CHAR(ffl.footnote_seq_number),3,''0'')
181 ||RPAD(ffl.footnote_text,255)
182 FROM fv_facts_submission ffs,
183 fv_facts_temp fft,
184 fv_facts_footnote_hdr ffh,
185 fv_facts_footnote_lines ffl,
186 fv_treasury_symbols fts
187 WHERE ffs.set_of_books_id = '||sob_id||
188 ' AND ffs.bulk_file_sub_id = '||request_id||
189 ' AND ffs.bulk_flag = ''Y''
190 AND ffs.foot_note_flag = ''Y''
191 AND fft.fct_int_record_category = ''REPORTED_NEW''
192 AND fft.document_number = ''Y''
193 AND ffs.treasury_symbol_id = fft.treasury_symbol_id
194 AND ffh.treasury_symbol_id = fft.treasury_symbol_id
195 AND ffh.footnote_header_id = ffl.footnote_header_id
196 AND fft.sgl_acct_number = ffh.sgl_acct_number
197 AND fts.treasury_symbol_id = fft.treasury_symbol_id
198 ORDER BY fts.treasury_symbol, ffh.sgl_acct_number,
199 ffl.footnote_seq_number' ;
200
201 fv_flatfiles.create_flat_file(statement);
202
203 -- count the footnotes to print in the trailer record
204 SELECT LPAD(to_char(count(*)), 10, '0')
205 INTO v_footnote_count
206 FROM fv_facts_footnote_hdr ffh,
207 fv_facts_footnote_lines ffl,
208 fv_facts_submission ffs
209 WHERE ffh.footnote_header_id = ffl.footnote_header_id
210 AND ffh.treasury_symbol_id = ffs.treasury_symbol_id
211 AND ffs.Bulk_File_Sub_id = request_id
212 AND ffs.set_of_books_id = sob_id
213 AND ffs.foot_note_flag = 'Y'
214 AND ffs.bulk_flag = 'Y';
215
216 SELECT LPAD(to_char(count(*)), 10, '0')
217 INTO v_trailer_count
218 FROM fv_facts_submission ffs ,
219 fv_facts_temp fft
220 WHERE fft.treasury_symbol_id = ffs.treasury_symbol_id
221 AND ffs.bulk_flag = 'Y'
222 AND fct_int_record_category = 'REPORTED_NEW'
223 AND ffs.Bulk_File_Sub_id = request_id
224 AND ffs.set_of_books_id = sob_id
225 AND fct_int_record_type = 'BLK_DTL';
226
227 v_total_count := v_trailer_count + v_footnote_count;
228
229 statement :=
230 'SELECT ''TRL''||LPAD('||v_total_count||',10,''0'')
231 ||'||''''||v_footnote_count||''''||'
232 ||LPAD('' '', 378)
233 FROM DUAL' ;
234
235 fv_flatfiles.create_flat_file(statement);
236 -- -----------------------------------------------------------------------------------
237 -- Create Flat File for FVTI224R
238 -- -----------------------------------------------------------------------------------
239 ELSIF conc_prog = 'FVTI224R' THEN
240 statement :=
241 'SELECT goals_224_record
242 FROM fv_goals_224_temp
243 ORDER BY goals_224_temp_id';
244
245 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
246 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'Creating Flat File for FVTI224R');
247 END IF;
248
249 fv_flatfiles.create_flat_file(statement);
250 -- -----------------------------------------------------------------------------------
251 -- Create Flat File for FVTIOBUR
252 -- -----------------------------------------------------------------------------------
253 ELSIF conc_prog = 'FVTIOBUR' THEN
254 statement :=
255 'SELECT record
256 FROM fv_opac_upload_temp
257 ORDER BY line_no';
258
259 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
260 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'Creating Flat File for FVTIOBUR');
261 END IF;
262
263 fv_flatfiles.create_flat_file(statement);
264 -- -----------------------------------------------------------------------------------
265 -- Create Flat File for FVFACTSR
266 -- -----------------------------------------------------------------------------------
267 ELSIF conc_prog = 'FVFACTSR' THEN
268
269 period_year := parameter1;
270
271
272 -- statement :=
273 -- 'SELECT ''ENTITY IS ''' || entity_code ||' From Dual' ;
274
275 -- Enh No:1541559
276 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
277 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'Creating Flat File for FVFACTSR');
278 END IF;
279 --fnd_file.put_line(fnd_file.output, 'ENTITY IS '||entity_code);
280
281 -- fv_flatfiles.create_flat_file(statement);
282
283 statement :=
284 'SELECT '||parameter1||'||
285 RPAD(dept_id, 2,'' '') ||
286 RPAD(bureau_id, 2, '' '') ||
287 LPAD(fund_group, 4, ''0'') ||
288 RPAD(NVL(USSGL_ACCOUNT,'' ''),4,'' '') ||
289 NVL(g_ng_indicator,'' '')||
290 NVL(RPAD(eliminations_dept,2,'' ''),'' '')||
291 d_c_indicator ||
292 LPAD(TO_CHAR(SUBSTR(amount,1,DECODE(INSTR(amount,''.''),0,LENGTH(amount),INSTR(amount,''.'')-1))) ||
293 RPAD(NVL(TO_CHAR(SUBSTR(amount,DECODE(INSTR(amount,''.''),0,LENGTH(amount)+1,INSTR(amount,''.'')+1))),''0''),2,''0''),17,''0'')||
294 ''1''||
295 NVL(exch_non_exch,'' '') ||
296 ''2''||
297 NVL(RPAD(budget_subfunction,3,'' ''),'' '')||
298 ''3''||
299 NVL(cust_non_cust,'' '')
300 FROM fv_facts_report_t2
301 WHERE set_of_books_id = '||sob_id||
302 ' AND reported_status in (''F'')
303 AND amount <> 0
304 ORDER BY
305 DEPT_ID,
306 BUREAU_ID,
307 FUND_GROUP,
308 USSGL_ACCOUNT,
309 ELIMINATIONS_DEPT,
310 g_ng_indicator' ;
311
312 --fnd_file.put_line(fnd_file.log,statement );
313
314 fv_flatfiles.create_flat_file(statement);
315
316 statement :=
317 'SELECT ''TRL'' ||
318 LPAD(TO_CHAR(COUNT(*)),10,''0'')||
319 RPAD('' '',32,'' '')
320 FROM fv_facts_report_t2
321 WHERE set_of_books_id = '||sob_id||
322 'AND reported_status in (''F'')
323 AND amount <> 0
324 ORDER BY
325 DEPT_ID,
326 BUREAU_ID,
327 FUND_GROUP,
328 USSGL_ACCOUNT,
329 ELIMINATIONS_DEPT,
330 g_ng_indicator';
331 fv_flatfiles.create_flat_file(statement);
332
333 -- -----------------------------------------------------------------------------------
334 -- Create Flat File for FVFC1ATB
335 -- -----------------------------------------------------------------------------------
336 ELSIF conc_prog = 'FVFC1ATB' THEN
337
338 period_year := parameter1;
339
340 FV_UTILITY.LOG_MESG('Creating Flat File for FVFC1ATB');
341
342 gbl_count := 0;
343 statement :=
344 'SELECT '||parameter1||'||
345 RPAD(dept_id, 2,'' '') ||
346 RPAD(bureau_id, 2, '' '')||
347 LPAD(fund_group, 4, ''0'')||
348 RPAD(NVL(ussgl_account,'' ''),4,'' '')||
349 NVL(g_ng_indicator,'' '')||
350 NVL(RPAD(eliminations_dept,2,'' ''),'' '')||
351 DECODE(SIGN(SUM(NVL(amount, 0))), 0, ''D'', 1, ''D'', -1, ''C'')||
352 TO_CHAR(ABS(SUM(NVL(amount,0))), ''FM00000000000000V00'')||
353 ''1''||
354 NVL(exch_non_exch,'' '')||
355 ''2''||
356 NVL(RPAD(budget_subfunction,3,'' ''),'' '')||
357 ''3''||
358 NVL(cust_non_cust,'' '')
359 FROM fv_facts1_period_balances_v
360 WHERE set_of_books_id = '||sob_id||
361 ' AND period_year = '||parameter1||
362 ' AND period_num <= '||parameter3||
363 ' AND amount <> 0
364 HAVING SUM(NVL(amount, 0)) <> 0
365 GROUP BY LPAD(fund_group, 4, ''0''),
366 RPAD(dept_id, 2,'' ''),
367 RPAD(bureau_id, 2, '' ''),
368 RPAD(NVL(ussgl_account,'' ''),4,'' ''),
369 NVL(g_ng_indicator,'' ''),
370 NVL(RPAD(eliminations_dept,2,'' ''),'' ''),
371 NVL(exch_non_exch,'' ''),
372 NVL(RPAD(budget_subfunction,3,'' ''),'' ''),
373 NVL(cust_non_cust,'' '')
374 ORDER BY RPAD(dept_id, 2,'' ''),
375 RPAD(bureau_id, 2, '' ''),
376 LPAD(fund_group, 4, ''0''),
377 RPAD(NVL(ussgl_account,'' ''),4,'' ''),
378 NVL(RPAD(eliminations_dept,2,'' ''),'' ''),
379 NVL(g_ng_indicator,'' '')';
380
381 fv_flatfiles.create_flat_file(statement);
382
383
384 /*
385 statement :=
386 'SELECT ''TRL'' ||
387 LPAD(TO_CHAR(COUNT(*)),10,''0'')||
388 RPAD('' '',32,'' '')
389 FROM (
393 ' AND period_year = '||parameter1||
390 SELECT COUNT(*)
391 FROM fv_facts1_period_balances_v
392 WHERE set_of_books_id = '||sob_id||
394 ' AND period_num <= '||parameter3||
395 ' AND amount <> 0
396 HAVING SUM(NVL(amount,0)) <> 0
397 GROUP BY fund_group,
398 dept_id,
399 bureau_id,
400 ussgl_account,
401 g_ng_indicator,
402 eliminations_dept,
403 exch_non_exch,
404 budget_subfunction,
405 cust_non_cust)';
406
407 */
408 /* ------ Trail records printing ---------------- */
409
410 v_str := 'TRL' || LPAD(TO_CHAR(gbl_count),10,'0')|| RPAD(' ',32,' ');
411 statement := 'SELECT ''' || v_str || ''' FROM dual ';
412
413 fnd_file.put_line (fnd_file.log, statement);
414 fv_flatfiles.create_flat_file(statement);
415
416 -- -----------------------------------------------------------------------------------
417 -- -----------------------------------------------------------------------------------
418 -- Create Flat File for FVTI133R
419 -- -----------------------------------------------------------------------------------
420 ELSIF conc_prog = 'FVTI133R' THEN
421 statement :=
422 'SELECT substr(goals_133_record,1,75)
423 FROM fv_goals_133_temp
424 ORDER BY goals_133_record_type, goals_133_temp_id';
425
426 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
427 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'Creating Flat File for FVTI133R');
428 END IF;
429
430 fv_flatfiles.create_flat_file(statement);
431 -- -----------------------------------------------------------------------------------
432 -- Create Flat File for FVTI133R
433 -- -----------------------------------------------------------------------------------
434 ELSIF conc_prog = 'FVTI133R' THEN
435
436 payment_year := parameter1;
437 invoice_minimum := parameter3;
438 tape_indicator := parameter4;
439 transmitter_code := parameter5;
440
441
442 -- ---------------------------------------------------
443 -- Select creditors tin
444 -- ---------------------------------------------------
445 SELECT distinct rpad(replace(creditors_tin,'-',''),9) ct
446 INTO v_creditors_tin
447 FROM fv_1099c;
448
449 -- ---------------------------------------------------
450 -- Create Creditor/Transmitter 'A' Record
451 -- ---------------------------------------------------
452 statement :=
453 'SELECT ''A''
454 ||payment_year
455 ||'' ''
456 ||rpad(v_creditors_tin,9)
457 ||'' ''
458 ||'' ''
459 ||'' ''
460 ||''5''
461 ||''23 ''
462 ||'' ''
463 ||'' ''
464 ||'' ''
465 ||rpad(tape_indicator,2)
466 ||rpad(transmitter_code,5)
467 ||'' ''
468 ||rpad(substr(hou.name,1,40),40)
469 ||rpad(nvl(substr(hou.name,41,60),'' ''),40)
470 ||'' ''
471 ||rpad(substr(address_line_1||address_line_2||address_line_3,1,40),40)
472 ||rpad(substr(town_or_city||'',''||substr(region_2,1,2)||'' ''||postal_code,1,40),40)
473 ||'' ''
474 ||'' ''
475 ||'' ''
476 ||'' ''
477 FROM hr_locations hl,
478 hr_organization_units hou,
479 fv_operating_units fou
480 WHERE hou.organization_id = fou.organization_id
481 AND hl.location_id = hou.location_id
482 AND fou.set_of_books_id = sob_id';
483
484 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
485 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'Creating Flat File for FVTI133R');
486 END IF;
487
488 fv_flatfiles.create_flat_file(statement);
489 -- ---------------------------------------------------
490 -- Create Debtor 'B' Record
491 -- ---------------------------------------------------
492 statement :=
493 'SELECT ''B''
494 ||payment_year
495 ||fvr_1099t
496 FROM fv_1099t_v
497 WHERE amount >= invoice_minimum
498 AND reportable_flag = ''Y''
499 AND set_of_books_id = sob_id';
500
501 fv_flatfiles.create_flat_file(statement);
502 -- ---------------------------------------------------
503 -- Create Debtor 'C' Record
504 -- ---------------------------------------------------
505 statement :=
506 'SELECT ''C''
507 ||lpad(count(*),6,0)
508 ||'' ''
509 ||''000000000000000''
510 ||to_char(sum(amount),''S000000000000V99'')
511 ||to_char(sum(finance_charge_amount),''S000000000000V99'')
512 ||''000000000000000''
513 ||''000000000000000''
514 ||''000000000000000''
515 ||''000000000000000''
516 ||''000000000000000''
517 ||''000000000000000''
518 ||lpad('' '',275)
519 FROM fv_1099c
520 WHERE amount > invoice_minimum
521 AND reportable_flag = ''Y''
522 AND set_of_books_id = sob_id';
523
524 fv_flatfiles.create_flat_file(statement);
525 -- ---------------------------------------------------
526 -- Create Debtor 'F' Record
527 -- ---------------------------------------------------
528 statement :=
529 'SELECT ''F''
530 ||''0001''
531 ||''0000000000000000000000000''
532 ||lpad('' '',390)
533 FROM dual';
534
535 fv_flatfiles.create_flat_file(statement);
536 -- -----------------------------------------------------------------------------------
537 END IF;
538 EXCEPTION
539 WHEN OTHERS THEN
540 vp_errbuf := SQLERRM;
541 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',vp_errbuf);
542 RAISE;
543 end main;
544 -- ----------------------------------------------------------------------------
545 PROCEDURE create_flat_file(v_statement VARCHAR2) IS
546 l_module_name VARCHAR2(200) ;
547 v_cursor integer;
548 l_fetch_count integer;
549 col1 VARCHAR2(2000);
550
551 BEGIN
552 l_module_name := g_module_name || 'create_flat_file';
553
554 BEGIN
555 v_cursor := DBMS_SQL.OPEN_CURSOR;
556 EXCEPTION WHEN OTHERS THEN
557 vp_errbuf := sqlerrm;
558 vp_retcode := sqlcode;
559 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.open_v_cursor',vp_errbuf);
560 RETURN;
561 END;
562
563 BEGIN
564 DBMS_SQL.PARSE(v_cursor, v_statement, DBMS_SQL.V7);
565 EXCEPTION WHEN OTHERS THEN
566 vp_retcode := sqlcode ;
567 VP_ERRBUF := sqlerrm ;
568 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.parse_v_cursor',vp_errbuf);
569 RETURN ;
570 END ;
571
572 DBMS_SQL.DEFINE_COLUMN(v_cursor, 1, col1, 2000);
573
574 BEGIN
575 l_fetch_count := DBMS_SQL.EXECUTE(v_cursor);
576 EXCEPTION WHEN OTHERS THEN
577 vp_retcode := sqlcode ;
578 VP_ERRBUF := sqlerrm ;
579 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.execute_v_cursor',vp_errbuf);
580 RETURN ;
581 END;
582
583 gbl_count := 0;
584
585 LOOP
586 l_fetch_count := DBMS_SQL.FETCH_ROWS(v_cursor);
587 IF l_fetch_count = 0
588 THEN return;
589 END IF;
590 gbl_count := gbl_count + 1;
591 DBMS_SQL.COLUMN_VALUE(v_cursor, 1, col1);
592 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,col1);
593 END LOOP;
594 EXCEPTION
595 WHEN OTHERS THEN
596 vp_errbuf := SQLERRM;
597 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',vp_errbuf);
598 RAISE;
599 end create_flat_file;
600 -- ----------------------------------------------------------------------------
601 end fv_flatfiles;