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