eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.FV_FLATFILES SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 63


		'SELECT facts_report_info
		FROM fv_facts_temp fft,
     	             fv_facts_submission ffs,
     		     fv_treasury_symbols fts
		WHERE fft.treasury_symbol_id = ffs.treasury_symbol_id
		AND   fts.treasury_symbol_id = fft.treasury_symbol_id
		AND   ffs.bulk_flag = ''Y''
		AND   fct_int_record_category = ''REPORTED_NEW''
		AND   fct_int_record_type = ''CNT_HDR''
		AND   ffs.Bulk_File_Sub_Id = '||request_id||
		' AND   ffs.set_of_books_id = '||sob_id||
		' ORDER BY fts.treasury_symbol';

Line: 84


		'SELECT ''TRL'' || LPAD(to_char(count(*)), 10, ''0'') || LPAD('' '', 403)
		FROM  fv_facts_submission ffs
		WHERE ffs.bulk_flag = ''Y''
		AND   ffs.Bulk_File_Sub_Id = '||request_id||
		' AND   ffs.set_of_books_id = '||sob_id ;

Line: 97


		'SELECT record
		FROM   fv_ecs_ncrpay_temp
		ORDER  by line_no';

Line: 112


  The Following Select Statement for FVTICTXR is changed.
  After generating the Output the Temporary table is Purged*/

	ELSIF conc_prog = 'FVTICTXR' THEN
	statement :=
		'SELECT record
		FROM   fv_payment_format_temp
		WHERE  set_of_books_id = '||parameter1||
		' AND    org_id = '||parameter2||
		' AND    checkrun_name = '||''''||parameter3||''''||
		' ORDER  by line_no';

Line: 129


	DELETE FROM fv_payment_format_temp
	WHERE set_of_books_id = parameter1
	AND    org_id = parameter2
	AND    checkrun_name = parameter3 ;

Line: 140


		'SELECT record
		FROM   fv_ecs_ach_vendor_temp
		ORDER  by line_no';

Line: 155


		'SELECT facts_report_info
		FROM fv_facts_temp  fft,
     		fv_facts_submission ffs,
     		fv_treasury_symbols fts
		WHERE fft.treasury_symbol_id = ffs.treasury_symbol_id
		AND   fts.treasury_symbol_id = fft.treasury_symbol_id
		AND   ffs.bulk_flag = ''Y''
		AND   fct_int_record_category = ''REPORTED_NEW''
		AND   fct_int_record_type = ''BLK_DTL''
		AND   ffs.Bulk_File_Sub_id = '||request_id||
		' AND   ffs.set_of_books_id = '||sob_id||
		' AND   fts.set_of_books_id = '||sob_id||
		' ORDER BY fts.treasury_symbol, fft.sgl_acct_number' ;

Line: 178


		'SELECT    SUBSTR(fft.facts_report_info,1,50)
       			||''F''||SUBSTR(fft.facts_report_info,52,74)
			||RPAD('' '', 17)||SUBSTR(fft.facts_report_info,143,1)
        		||LPAD(TO_CHAR(ffl.footnote_seq_number),3,''0'')
			||RPAD(ffl.footnote_text,255)
		FROM   	fv_facts_submission ffs,
			fv_facts_temp fft,
       			fv_facts_footnote_hdr ffh,
       			fv_facts_footnote_lines ffl,
			fv_treasury_symbols fts
		WHERE   ffs.set_of_books_id = '||sob_id||
		' AND   ffs.bulk_file_sub_id = '||request_id||
		' AND   ffs.bulk_flag = ''Y''
		AND     ffs.foot_note_flag = ''Y''
		AND	fft.fct_int_record_category = ''REPORTED_NEW''
		AND	fft.document_number = ''Y''
		AND     ffs.treasury_symbol_id = fft.treasury_symbol_id
		AND     ffh.treasury_symbol_id = fft.treasury_symbol_id
		AND     ffh.footnote_header_id = ffl.footnote_header_id
		AND     fft.sgl_acct_number = ffh.sgl_acct_number
		AND     fts.treasury_symbol_id = fft.treasury_symbol_id
		ORDER BY fts.treasury_symbol, ffh.sgl_acct_number,
			 ffl.footnote_seq_number' ;

Line: 205


	SELECT 	LPAD(to_char(count(*)), 10, '0')
	INTO   	v_footnote_count
        FROM   	fv_facts_footnote_hdr ffh,
		fv_facts_footnote_lines ffl,
		fv_facts_submission ffs
	WHERE ffh.footnote_header_id = ffl.footnote_header_id
	AND   ffh.treasury_symbol_id = ffs.treasury_symbol_id
	AND   ffs.Bulk_File_Sub_id = request_id
	AND   ffs.set_of_books_id = sob_id
	AND   ffs.foot_note_flag = 'Y'
	AND   ffs.bulk_flag = 'Y';

Line: 217


	SELECT  LPAD(to_char(count(*)), 10, '0')
	INTO    v_trailer_count
        FROM    fv_facts_submission ffs ,
                fv_facts_temp       fft
        WHERE fft.treasury_symbol_id = ffs.treasury_symbol_id
        AND   ffs.bulk_flag = 'Y'
        AND   fct_int_record_category = 'REPORTED_NEW'
        AND   ffs.Bulk_File_Sub_id = request_id
        AND   ffs.set_of_books_id = sob_id
        AND   fct_int_record_type = 'BLK_DTL';

Line: 231


               'SELECT ''TRL''||LPAD('||v_total_count||',10,''0'')
                        ||'||''''||v_footnote_count||''''||'
                        ||LPAD('' '', 378)
                 FROM   DUAL' ;

Line: 242


		'SELECT goals_224_record
		FROM   fv_goals_224_temp
		ORDER BY goals_224_temp_id';

Line: 256


	       'SELECT record
		FROM    fv_opac_upload_temp
		ORDER BY line_no';

Line: 274


	--	'SELECT ''ENTITY IS ''' || entity_code ||' From Dual' ;

Line: 285


		'SELECT '||parameter1||'||
			RPAD(dept_id, 2,'' '') ||
        		RPAD(bureau_id, 2, '' '') ||
        		LPAD(fund_group, 4, ''0'') ||
        		RPAD(NVL(USSGL_ACCOUNT,'' ''),4,'' '') ||
        		NVL(g_ng_indicator,'' '')||
        		NVL(RPAD(eliminations_dept,2,'' ''),''  '')||
        		d_c_indicator ||
        		LPAD(TO_CHAR(SUBSTR(amount,1,DECODE(INSTR(amount,''.''),0,LENGTH(amount),INSTR(amount,''.'')-1))) ||
    			RPAD(NVL(TO_CHAR(SUBSTR(amount,DECODE(INSTR(amount,''.''),0,LENGTH(amount)+1,INSTR(amount,''.'')+1))),''0''),2,''0''),17,''0'')||
    			''1''||
    			NVL(exch_non_exch,'' '') ||
    			''2''||
    			NVL(RPAD(budget_subfunction,3,'' ''),''   '')||
    			''3''||
    			NVL(cust_non_cust,'' '')
        	 FROM fv_facts_report_t2
		 WHERE set_of_books_id = '||sob_id||
  		 ' AND reported_status in (''F'')
  		 AND amount <> 0
		 ORDER BY
        		DEPT_ID,
        		BUREAU_ID,
        		FUND_GROUP,
        		USSGL_ACCOUNT,
        		ELIMINATIONS_DEPT,
        		g_ng_indicator' ;

Line: 318


		'SELECT ''TRL'' ||
		        LPAD(TO_CHAR(COUNT(*)),10,''0'')||
		        RPAD('' '',32,'' '')
		 FROM fv_facts_report_t2
		 WHERE set_of_books_id = '||sob_id||
  		 'AND reported_status in (''F'')
  		 AND amount <> 0
  		 ORDER BY
  		   DEPT_ID,
  		   BUREAU_ID,
  		   FUND_GROUP,
  		   USSGL_ACCOUNT,
  		   ELIMINATIONS_DEPT,
  		   g_ng_indicator';

Line: 345


          'SELECT '||parameter1||'||
                 RPAD(dept_id, 2,'' '') ||
                 RPAD(bureau_id, 2, '' '')||
                 LPAD(fund_group, 4, ''0'')||
                 RPAD(NVL(ussgl_account,'' ''),4,'' '')||
                 NVL(g_ng_indicator,'' '')||
                 NVL(RPAD(eliminations_dept,2,'' ''),''  '')||
                 DECODE(SIGN(SUM(NVL(amount, 0))), 0, ''D'', 1, ''D'', -1, ''C'')||
                 TO_CHAR(ABS(SUM(NVL(amount,0))), ''FM000000000000000V00'')||
                 ''1''||
                 NVL(exch_non_exch,'' '')||
                 ''2''||
                 NVL(RPAD(budget_subfunction,3,'' ''),''   '')||
                 ''3''||
                 NVL(cust_non_cust,'' '')
            FROM fv_facts1_period_balances_v
           WHERE set_of_books_id = '||sob_id||
            ' AND period_year = '||parameter1||
            ' AND period_num <= '||parameter3||
            ' AND amount <> 0
           HAVING SUM(NVL(amount, 0)) <> 0
           GROUP BY LPAD(fund_group, 4, ''0''),
                    RPAD(dept_id, 2,'' ''),
                    RPAD(bureau_id, 2, '' ''),
                    RPAD(NVL(ussgl_account,'' ''),4,'' ''),
                    NVL(g_ng_indicator,'' ''),
                    NVL(RPAD(eliminations_dept,2,'' ''),''  ''),
                    NVL(exch_non_exch,'' ''),
                    NVL(RPAD(budget_subfunction,3,'' ''),''   ''),
                    NVL(cust_non_cust,'' '')
           ORDER BY RPAD(dept_id, 2,'' ''),
                    RPAD(bureau_id, 2, '' ''),
                    LPAD(fund_group, 4, ''0''),
                    RPAD(NVL(ussgl_account,'' ''),4,'' ''),
                    NVL(RPAD(eliminations_dept,2,'' ''),''  ''),
                    NVL(g_ng_indicator,'' '')';

Line: 387


                'SELECT ''TRL'' ||
                        LPAD(TO_CHAR(COUNT(*)),10,''0'')||
                        RPAD('' '',32,'' '')
                  FROM (
          	  SELECT COUNT(*)
            	  FROM fv_facts1_period_balances_v
           	  WHERE set_of_books_id = '||sob_id||
            	  ' AND period_year = '||parameter1||
            	  ' AND period_num <= '||parameter3||
            	  ' AND amount <> 0
                  HAVING SUM(NVL(amount,0)) <> 0
           	  GROUP BY fund_group,
                    	  dept_id,
                    	  bureau_id,
                    	  ussgl_account,
                    	  g_ng_indicator,
                    	  eliminations_dept,
                    	  exch_non_exch,
                    	  budget_subfunction,
                    	  cust_non_cust)';

Line: 412


           statement := 'SELECT ''' || v_str  || ''' FROM  dual ';

Line: 437


'select f1.period_year ||' ||''''||reporting_period||''''||
   '||RPAD(NVL(tas.dept_transfer,'' ''),3)||
rpad(NVL(tas.department_id,'' ''),3) ||
RPAD(NVL(TO_CHAR(tas.ESTABLISHED_FISCAL_YR),'' ''),4)||
RPAD(NVL(TO_CHAR(tas.END_YEAR_AVAIL),'' ''),4)||
NVL(tas.AVAILABILITY_TYPE_CODE,'' '')||tas.FUND_GROUP_CODE||
rpad(NVL(tas.TAFS_SUB_ACCT,'' ''),3)||
f1.ussgl_Account||
lpad(ABS(SUM(f1.amount)*100),21,0)||
--f1.D_C_INDICATOR||
DECODE(SIGN(SUM(f1.amount)), 1, ''D'', -1, ''C'', ''D'')||
f1.begin_end||NVL(f1.authority_type_code,'' '')||
NVL(f1.DIRECT_OR_REIMB_CODE,'' '')||
NVL(f1.APPOR_CAT_CODE, '' '')||rpad(nvl(f1.APPOR_CAT_B_CODE,'' ''),4)||
rpad(NVL(f1.PROGRAM_REPT_CODE,'' ''), 2)||
NVL(f1.FED_NON_FED,'' '')||rpad(NVL(f1.TRADING_PARTNER_AGENCY_ID,'' ''),3)||
rpad(NVL(f1.TRADING_PARTNER_MAIN_ACCOUNT,'' ''),4)||
rpad(NVL(f1.NEW_BAL_CODE,'' ''),3)||
NVL(f1.CUR_SUBSEQUENT_CODE,'' '')||NVL(f1.BEA_CAT_CODE,'' '')||
NVL(f1.BORR_SRC_CODE,'' '')||
NVL(f1.EXCH_NON_EXCH,'' '')||NVL(f1.CUST_NON_CUST,'' '')||
NVL(f1.BUDGET_IMPACT_IND,'' '') ||
NVL(f1.PYA_CODE,'' '')||rpad(NVL(f1.CREDIT_COHORT_YR,'' ''), 4)||
NVL(f1.PROGRAM_COST_IND,'' '') '||
'from fv_gtas1_period_Balances_v f1,'||
'fv_treasury_symbols tas,'||
'fv_fund_parameters fund '||
'where f1.fund_value = fund.fund_value '||
' and f1.treasury_symbol_id = fund.treasury_symbol_id '||
' and fund.treasury_symbol_id = tas.treasury_symbol_id '||
--' and f1.reported_Status <>''E'' '||
' and f1.period_year ='|| period_year ||
' and f1.set_of_books_id = '||sob_id ||
' AND period_num <= '||period_num ||
' AND ABS(f1.amount) <> 0
 group by
f1.period_year,
tas.dept_transfer,
tas.department_id,
tas.ESTABLISHED_FISCAL_YR,
tas.END_YEAR_AVAIL,
tas.AVAILABILITY_TYPE_CODE,
tas.FUND_GROUP_CODE,
tas.TAFS_SUB_ACCT,
f1.ussgl_Account,
--f1.D_C_INDICATOR,
f1.begin_end,f1.authority_type_code,
f1.DIRECT_OR_REIMB_CODE,
f1.APPOR_CAT_CODE,f1.APPOR_CAT_B_CODE,
f1.PROGRAM_REPT_CODE,
f1.FED_NON_FED,
f1.TRADING_PARTNER_AGENCY_ID,
f1.TRADING_PARTNER_MAIN_ACCOUNT,
f1.NEW_BAL_CODE,
f1.CUR_SUBSEQUENT_CODE,f1.BEA_CAT_CODE,
f1.BORR_SRC_CODE,
f1.EXCH_NON_EXCH,
f1.CUST_NON_CUST,
f1.BUDGET_IMPACT_IND,
f1.PYA_CODE,f1.CREDIT_COHORT_YR,
f1.PROGRAM_COST_IND
HAVING SUM(amount) <> 0
order by
f1.period_year,
tas.dept_transfer,
tas.department_id,
tas.ESTABLISHED_FISCAL_YR,
tas.END_YEAR_AVAIL,
tas.AVAILABILITY_TYPE_CODE,
tas.FUND_GROUP_CODE,
tas.TAFS_SUB_ACCT,
f1.ussgl_Account,
--f1.D_C_INDICATOR,
f1.begin_end,f1.authority_type_code,
f1.DIRECT_OR_REIMB_CODE,
f1.APPOR_CAT_CODE,f1.APPOR_CAT_B_CODE,
f1.PROGRAM_REPT_CODE,
f1.FED_NON_FED,
f1.TRADING_PARTNER_AGENCY_ID,
f1.TRADING_PARTNER_MAIN_ACCOUNT,
f1.NEW_BAL_CODE,
f1.CUR_SUBSEQUENT_CODE,f1.BEA_CAT_CODE,
f1.BORR_SRC_CODE,
f1.EXCH_NON_EXCH,
f1.CUST_NON_CUST,
f1.BUDGET_IMPACT_IND,
f1.PYA_CODE,f1.CREDIT_COHORT_YR,
f1.PROGRAM_COST_IND
 ';

Line: 535


		'SELECT substr(goals_133_record,1,75)
		FROM   fv_goals_133_temp
		ORDER BY goals_133_record_type, goals_133_temp_id';

Line: 556


	 -- Select creditors tin
	 -- ---------------------------------------------------
		SELECT distinct rpad(replace(creditors_tin,'-',''),9) ct
		INTO   v_creditors_tin
  		FROM   fv_1099c;

Line: 566


		'SELECT ''A''
       		||payment_year
       		||''   ''
       		||rpad(v_creditors_tin,9)
       		||''    ''
       		||'' ''
       		||'' ''
       		||''5''
       		||''23       ''
       		||'' ''
       		||'' ''
       		||''        ''
       		||rpad(tape_indicator,2)
       		||rpad(transmitter_code,5)
       		||'' ''
       		||rpad(substr(hou.name,1,40),40)
       		||rpad(nvl(substr(hou.name,41,60),'' ''),40)
       		||'' ''
       		||rpad(substr(address_line_1||address_line_2||address_line_3,1,40),40)
       		||rpad(substr(town_or_city||'',''||substr(region_2,1,2)||'' ''||postal_code,1,40),40)
       		||''                                                                                ''
       		||''                                        ''
       		||''                                        ''
       		||''                                                  ''
  		FROM hr_locations hl,
       		hr_organization_units hou,
       		fv_operating_units fou
 		WHERE hou.organization_id = fou.organization_id
   		AND hl.location_id      = hou.location_id
   		AND fou.set_of_books_id = sob_id';

Line: 606


		'SELECT ''B''
       		||payment_year
		||fvr_1099t
		FROM fv_1099t_v
		WHERE amount >= invoice_minimum
		AND   reportable_flag = ''Y''
		AND   set_of_books_id = sob_id';

Line: 619


		'SELECT ''C''
       		||lpad(count(*),6,0)
       		||''   ''
       		||''000000000000000''
       		||to_char(sum(amount),''S000000000000V99'')
       		||to_char(sum(finance_charge_amount),''S000000000000V99'')
       		||''000000000000000''
       		||''000000000000000''
       		||''000000000000000''
       		||''000000000000000''
       		||''000000000000000''
       		||''000000000000000''
       		||lpad('' '',275)
  		FROM fv_1099c
 		WHERE amount > invoice_minimum
   		AND reportable_flag = ''Y''
   		AND set_of_books_id = sob_id';

Line: 642


		'SELECT ''F''
       		||''0001''
       		||''0000000000000000000000000''
       		||lpad('' '',390)
  		FROM dual';