[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
function get_select_list return varchar2;
select sub_report_id, sub_attribute_set
into m_report.report_id, m_report.attribute_set
from fa_rx_multiformat_reps
where request_id = p_request_id
and seq_number = (
select min(seq_number) from fa_rx_multiformat_reps
where request_id = p_request_id)
and rownum=1;
select
nvl(print_title, 'Y') print_title,
nvl(print_sob_flag, 'Y') print_sob_flag,
nvl(print_func_curr_flag, 'Y') print_func_curr_flag,
nvl(print_submission_date,'Y') print_submission_date,
nvl(print_current_page, 'Y') print_current_pages,
nvl(print_total_pages, 'N') print_total_pages,
report_title report_title,
nvl(page_width, 0) page_width,
nvl(page_height, 0) page_height
into
m_report.display_report_title,
m_report.display_set_of_books,
m_report.display_functional_currency,
m_report.display_submission_date,
m_report.display_current_page,
m_report.display_total_page,
m_report.report_title,
m_report.page_width, m_report.page_height
from
fa_rx_attrsets
where
report_id = m_report.report_id and
attribute_set = m_report.attribute_set;
select distinct substrb (user_program_name, 1, 100)
into m_report.report_title
from fa_rx_reports_v
where report_id = m_report.report_id;
select meaning into m_report.functional_currency_prompt
from fnd_lookups where lookup_type='FARX_NLS_PARAMS' and lookup_code = 'CURRENCY_PROMPT';
select meaning into m_report.date_prompt
from fnd_lookups where lookup_type='FARX_NLS_PARAMS' and lookup_code = 'DATE_PROMPT';
select meaning into m_report.current_page_prompt
from fnd_lookups where lookup_type='FARX_NLS_PARAMS' and lookup_code = 'CURRENT_PAGE';
select meaning into m_report.total_page_prompt
from fnd_lookups where lookup_type='FARX_NLS_PARAMS' and lookup_code = 'TOTAL_PAGE';
select meaning into m_report.nls_end_of_report
from fnd_lookups where lookup_type='FARX_NLS_PARAMS' and lookup_code = 'END_OF_REPORT';
select meaning into m_report.nls_no_data_found
from fnd_lookups where lookup_type='FARX_NLS_PARAMS' and lookup_code = 'NO_DATA_FOUND';
sqlstmt := 'SELECT ORGANIZATION_NAME, FUNCTIONAL_CURRENCY_CODE '||NEWLINE||
Get_From_Clause||NEWLINE||get_where_clause;
select f.end_user_column_name ,
decode(v.format_type,
'C', 'VARCHAR2',
'D', 'DATE',
'I', 'DATE',
'N', 'NUMBER',
'T', 'DATE',
'X', 'DATE',
'Y', 'DATE',
'VARCHAR2')
from
fnd_application a,
fnd_concurrent_programs p,
fnd_descr_flex_column_usages f,
fnd_flex_value_sets v
WHERE
a.application_short_name = m_report.conc_appname AND
a.application_id = p.application_id AND
p.concurrent_program_name = m_report.concurrent_program_name AND
f.descriptive_flexfield_name = '$SRS$.'||p.concurrent_program_name AND
f.enabled_flag = 'Y' AND
f.flex_value_set_id = v.flex_value_set_id
ORDER BY f.column_seq_num;
cursor c is select Least(display_length, 255) display_length
from fa_rx_rep_columns
where report_id = m_formats(fidx).report_id
and attribute_set = m_formats(fidx).attribute_set
and break = 'Y'
and break_group_level >=
decode(m_formats(fidx).display_page_break, 'Y', 2, 1)
and display_status = 'YES'
order by break_group_level, attribute_counter;
select max(Least(display_length, 255)) into max_len
from fa_rx_rep_columns
where report_id = m_formats(fidx).report_id
and attribute_set = m_formats(fidx).attribute_set
and nvl(break, 'N') = 'N'
and display_status = 'YES';
select
m.sub_report_id,
m.sub_request_id,
m.sub_attribute_set,
m.group_id,
m.complex_flag,
m.seq_number
from fa_rx_multiformat_reps m
where m.request_id = m_report.request_id
order by m.group_id, m.seq_number;
select
nvl(print_page_break_cols, 'N') print_page_break_cols,
nvl(print_parameters, 'Y') print_parameters,
nvl(group_display_type, 'GROUP LEFT') group_display_type,
default_date_format, default_date_time_format
into
l_formats(idx).display_page_break,
l_formats(idx).display_parameters,
l_formats(idx).group_display_type,
l_formats(idx).default_date_format,
l_formats(idx).default_date_time_format
from fa_rx_attrsets
where report_id = rformat.sub_report_id and
attribute_set = rformat.sub_attribute_set;
select interface_table, where_clause_api
into l_formats(idx).interface_table,
l_formats(idx).where_clause_api
from fa_rx_reports
where report_id = rformat.sub_report_id;
select
nvl(print_page_break_cols, 'N') print_page_break_cols,
nvl(print_parameters, 'N') print_parameters,
nvl(group_display_type, 'GROUP LEFT') group_display_type,
default_date_format, default_date_time_format
into m_formats(1).display_page_break, m_formats(1).display_parameters,
m_formats(1).group_display_type,
m_formats(1).default_date_format,
m_formats(1).default_date_time_format
from fa_rx_attrsets
where report_id = m_report.report_id and
attribute_set = m_report.attribute_set;
select interface_table, where_clause_api
into m_formats(m_format_count).interface_table,
m_formats(m_format_count).where_clause_api
from fa_rx_reports
where report_id = m_report.report_id;
select
d.form_left_prompt,
v.format_type,
d.display_flag,
v.flex_value_set_id,
v.flex_value_set_name
from
fnd_descr_flex_col_usage_vl d,
fnd_flex_value_sets v,
fnd_concurrent_programs c,
fnd_concurrent_requests r
WHERE
r.request_id = l_request_id
AND c.application_id = r.program_application_id
AND c.concurrent_program_id = r.concurrent_program_id
and d.application_id = c.application_id
and d.descriptive_flexfield_name = '$SRS$.'||c.concurrent_program_name
and d.enabled_flag = 'Y'
-- and d.flex_value_set_application_id = v.application_id
and d.flex_value_set_id = v.flex_value_set_id
order by d.column_seq_num;
sqlstmt := 'SELECT ';
select
attribute_name,
column_name,
ordering,
display_length,
display_format,
nvl(display_status, 'NO') display_status,
nvl(break, 'N') break,
currency_column,
precision,
minimum_accountable_unit,
units,
format_mask,
break_group_level
from
fa_rx_rep_columns
where
report_id = m_formats(s_current_format_idx).report_id
and display_status = 'YES'
and attribute_set = m_formats(s_current_format_idx).attribute_set
order by decode(break, 'Y', 1, 2), break_group_level, attribute_counter;
select
summary_prompt,
reset_level,
compute_level,
print_level,
summary_function function,
column_name
from
fa_rx_summary s
where
report_id = m_formats(s_current_format_idx).report_id
and attribute_set = m_formats(s_current_format_idx).attribute_set
and display_status = 'Y'
and column_name in
(select column_name from fa_rx_rep_columns c
where c.report_id=s.report_id and
c.attribute_set=s.attribute_set and
display_status = 'YES')
order by print_level, compute_level, reset_level;
function get_select_list return varchar2
is
l_select varchar2(10000);
l_select := 'SELECT ';
l_select := l_select || sep || m_columns(idx).column_name
||'/'||to_char(m_columns(idx).units);
l_select := l_select || sep ||
'to_char('||m_columns(idx).column_name||', '''||m_columns(idx).format_mask||''')';
l_select := l_select || sep ||
'fnd_date.date_to_displaydate('||m_columns(idx).column_name||',calendar_aware=>'||FND_DATE.calendar_aware||')';
l_select := l_select || sep || '''"''||' || m_columns(idx).column_name || '||''"''';
l_select := l_select || sep || m_columns(idx).column_name;
return l_select;
end get_select_list;
select a.application_short_name, c.concurrent_program_name
into p_appname, p_concname
from
fa_rx_reports rx,
fnd_concurrent_programs c,
fnd_application a
where
rx.application_id = c.application_id and
rx.concurrent_program_id = c.concurrent_program_id and
rx.application_id = a.application_id and
rx.report_id = p_report_id;
select
p.concurrent_program_name,
a.application_short_name
INTO
m_report.concurrent_program_name,
m_report.conc_appname
from
fnd_concurrent_requests r,
fnd_concurrent_programs p,
fnd_application a
where
r.request_id = conc_request_id and
r.program_application_id = p.application_id and
r.concurrent_program_id = p.concurrent_program_id and
p.application_id = a.application_id;
function get_select_stmt return varchar2
is
sqlstmt varchar2(10000);
sqlstmt := get_select_list||NEWLINE||
get_from_clause||NEWLINE||
tmp_where_clause||NEWLINE||
get_order_by_clause;
arp_util_tax.debug('get_select_stmt(-) wrong PL_SQL statement');
end get_select_stmt;
select count(*) into cnt
from fa_rx_multiformat_reps
where request_id = p_request_id;
cursor cols(p_report_id in number, p_attribute_set in varchar2) is select
column_name, display_format
from fa_rx_rep_columns
where report_id = p_report_id
and attribute_set = p_attribute_set
and break = 'Y'
and break_group_level = 1
order by attribute_counter;
sqlstmt := 'SELECT DISTINCT ';
sqlstmt := sqlstmt ||' UNION SELECT DISTINCT ';
cursor col(min_level in NUMBER, break_flag IN varchar2) is select
attribute_name,
Least(display_length, 255) display_length
from fa_rx_rep_columns
where report_id = m_formats(format_idx).report_id and attribute_set = m_formats(format_idx).attribute_set
and display_status = 'YES'
and decode(break, 'Y', break_group_level, -999) >= min_level
AND Nvl(break, 'N') = break_flag
ORDER BY Nvl(break,'N') DESC, break_group_level, attribute_counter;
select min(break_group_level) into min_level from fa_rx_rep_columns
where report_id = m_formats(format_idx).report_id and attribute_set = m_formats(format_idx).attribute_set
and display_status = 'YES';
select purge_api into l_purge_api
from fa_rx_reports
where report_id = l_report_id;