[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
'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';
'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 ;
'SELECT record
FROM fv_ecs_ncrpay_temp
ORDER by line_no';
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';
DELETE FROM fv_payment_format_temp
WHERE set_of_books_id = parameter1
AND org_id = parameter2
AND checkrun_name = parameter3 ;
'SELECT record
FROM fv_ecs_ach_vendor_temp
ORDER by line_no';
'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' ;
'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' ;
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';
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';
'SELECT ''TRL''||LPAD('||v_total_count||',10,''0'')
||'||''''||v_footnote_count||''''||'
||LPAD('' '', 378)
FROM DUAL' ;
'SELECT goals_224_record
FROM fv_goals_224_temp
ORDER BY goals_224_temp_id';
'SELECT record
FROM fv_opac_upload_temp
ORDER BY line_no';
-- 'SELECT ''ENTITY IS ''' || entity_code ||' From Dual' ;
'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' ;
'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';
'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))), ''FM00000000000000V00'')||
''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,'' '')';
'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)';
statement := 'SELECT ''' || v_str || ''' FROM dual ';
'SELECT substr(goals_133_record,1,75)
FROM fv_goals_133_temp
ORDER BY goals_133_record_type, goals_133_temp_id';
-- Select creditors tin
-- ---------------------------------------------------
SELECT distinct rpad(replace(creditors_tin,'-',''),9) ct
INTO v_creditors_tin
FROM fv_1099c;
'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';
'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';
'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';
'SELECT ''F''
||''0001''
||''0000000000000000000000000''
||lpad('' '',390)
FROM dual';