[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
procedure Insert_Style_Sheet_html is
styleSheet varchar2(5000);
procedure Insert_HTML_html(p_text varchar2) is
begin
line_out(p_text);
end Insert_HTML_html;
select a.name sobname,
count(b.period_name) total_periods,
count(decode(b.closing_status,'O',b.PERIOD_NAME,null)) open_periods,
a.accounted_period_type period_type
from gl_sets_of_books a,
gl_period_statuses b
where a.set_of_books_id = b.set_of_books_id (+)
and b.application_id = p_appId
and a.set_of_books_id = p_sobId
and b.period_type = a.accounted_period_type
group by a.name, a.accounted_period_type;
select application_name
into l_appname
from fnd_application_vl
where application_id = p_appid ;
select user_period_type into l_user_period_type
from gl_period_types
where period_type = c1_rec.period_type;
SELECT period_name, start_date, end_date, sysdate
INTO l_period_name, l_start_date, l_end_date, l_sysdate
FROM gl_period_statuses
WHERE adjustment_period_flag = 'N'
AND period_type = c1_rec.period_type
AND start_date = (
SELECT MAX(start_date)
FROM gl_period_statuses
WHERE closing_status = 'O'
AND adjustment_period_flag = 'N'
AND period_type = c1_rec.period_type
AND application_id = p_appId
AND set_of_books_id = p_sobId )
AND closing_status = 'O'
AND application_id = p_appId
AND set_of_books_id = p_sobId;
SELECT period_name, start_date, end_date, sysdate
INTO l_period_name, l_start_date, l_end_date, l_sysdate
FROM gl_period_statuses
WHERE adjustment_period_flag = 'N'
AND period_type = c1_rec.period_type
AND sysdate between start_date and end_date
AND closing_status = 'O'
AND application_id = p_appId
AND set_of_books_id = p_sobId;
select id_flex_num flex_str_num,
id_flex_structure_name flex_str_name,
to_char(last_update_date,'MM-DD-YYYY HH24:MI:SS') last_updated,
cross_segment_validation_flag cross_val,
dynamic_inserts_allowed_flag dyn_insert,
enabled_flag enabled,
freeze_flex_definition_flag frozen
from fnd_id_flex_structures_vl
where id_flex_code = p_f_code
and enabled_flag ='Y'
and id_flex_num = nvl(p_f_num,id_flex_num);
select s.application_column_name col_name,
s.segment_name seg_name,
s.segment_num seg_num,
s.enabled_flag enabled,
s.required_flag required,
s.display_flag displayed,
s.flex_value_set_id value_set_id,
vs.flex_value_set_name value_set_name,
DECODE(vs.validation_type,
'I', 'Independent', 'N', 'None', 'D', 'Dependent',
'U', 'Special', 'P', 'Pair', 'F', 'Table',
'X', 'Translatable Independent', 'Y', 'Translatable Dependent',
vs.validation_type) validation_type,
s.security_enabled_flag seg_security,
nvl(vs.security_enabled_flag,'N') value_set_security
from fnd_id_flex_segments_vl s, fnd_flex_value_sets vs
where s.flex_value_set_id = vs.flex_value_set_id (+)
and s.id_flex_code = p_f_code
and s.id_flex_num = p_f_num
order by s.segment_num ;
select segment_prompt
from fnd_segment_attribute_values sav,
fnd_segment_attribute_types sat
where sav.attribute_value = 'Y'
and sav.segment_attribute_type <> 'GL_GLOBAL'
and sav.application_id = sat.application_id
and sav.id_flex_code = sat.id_flex_code
and sav.segment_attribute_type = sat.segment_attribute_type
and sav.id_flex_code = p_f_code
and sav.id_flex_num = p_f_num
and sav.application_column_name = p_col_name;
select id_flex_name into l_flex_name
from fnd_id_flexs
where id_flex_code = p_flex_code;
l_ret_array(4) := str.dyn_insert;
||', Dynamic Inserts = '||str.dyn_insert||', Cross Validation Allowed = '
||str.cross_val||', Last Updated '||str.last_updated);
select count(*) into l_rule_count
from fnd_flex_value_rules_vl
where flex_value_set_id = seg.value_set_id;
select count(*) into l_rule_assign_count
from fnd_flex_value_rules_vl r,
fnd_flex_value_rule_usages ru
where r.flex_value_rule_id = ru.flex_value_rule_id
and r.flex_value_set_id = seg.value_set_id;
select user_profile_option_name,
nvl(start_date_active,sysdate-1),
nvl(end_date_active,sysdate+1)
into l_user_prof_name, l_start_date, l_end_date
from fnd_profile_options_vl
where profile_option_name = p_prof_name;
select count(*) into l_counter
from all_tab_columns z
where z.table_name = upper(p_tab)
and z.column_name = upper(p_col)
and upper(z.owner) = upper(p_owner);
select value into l_hold_date_format
from nls_session_parameters where parameter = 'NLS_DATE_FORMAT';
hold_sql := 'select ';
atleast one row and it will pirnt header only if rows are selected
*/
if hideHeader = TRUE then
line_out('
' || temp || '');
'0 Rows Selected
';
'1 Row Selected
';
' Rows Selected
';
dummy := Display_SQL ('select * from ' ||
replace(upper(p_table_name),'V_$','V$') || l_newline || p_where_clause ||
hold_char || nvl(p_order_by_clause,'order by 1')
, nvl(p_table_alias, p_table_name)
, p_display_longs);
return(Display_SQL ('select * from ' ||
replace(upper(p_table_name),'V_$','V$') || l_newline || p_where_clause ||
l_newline || nvl(p_order_by_clause,'order by 1')
, nvl(p_table_alias, p_table_name)
, p_display_longs));
select release_name into l_appsver from fnd_product_groups;
select instance_name
, host_name
, version
into l_instance_name
, l_host_name
, l_version
from v$instance;
Insert_Style_Sheet;
select substr(z.text, instr(z.text,'$Header')+10, 40)
into hold_version
from all_source z
where z.name = p_package
and z.type = p_type
and z.owner = p_schema
and z.text like '%$Header%';
'select b.USER_PROFILE_OPTION_NAME "Long
Name"'
|| ' , a.profile_option_name "Short
Name"'
|| ' , decode(to_char(c.level_id),''10001'',''Site'''
|| ' ,''10002'',''Application'''
|| ' ,''10003'',''Responsibility'''
|| ' ,''10004'',''User'''
|| ' ,''Unknown'') "Level"'
|| ' , decode(to_char(c.level_id),''10001'',''Site'''
|| ' ,''10002'',nvl(h.application_short_name,to_char(c.level_value))'
|| ' ,''10003'',nvl(g.responsibility_name,to_char(c.level_value))'
|| ' ,''10004'',nvl(e.user_name,to_char(c.level_value))'
|| ' ,''Unknown'') "Level
Value"'
|| ' , c.PROFILE_OPTION_VALUE "Profile
Value"'
|| ' , c.profile_option_id "Profile
ID"'
|| ' , to_char(c.LAST_UPDATE_DATE,''MM-DD-YYYY HH24:MI'') '
|| ' "Updated
Date"'
|| ' , nvl(d.user_name,to_char(c.last_updated_by)) "Updated
By"'
|| ' from fnd_profile_options a'
|| ' , FND_PROFILE_OPTIONS_TL b'
|| ' , FND_PROFILE_OPTION_VALUES c'
|| ' , FND_USER d'
|| ' , FND_USER e'
|| ' , FND_RESPONSIBILITY_TL g'
|| ' , FND_APPLICATION h'
|| ' where a.application_id = nvl(' || nvl(p_application_id,'null')
|| ' , a.application_id)'
|| ' and a.profile_option_name = nvl(''' || p_short_name
|| ''' , a.profile_option_name)'
|| ' and a.profile_option_name = b.profile_option_name'
|| ' and a.profile_option_id = c.profile_option_id'
|| ' and a.application_id = c.application_id'
|| ' and c.last_updated_by = d.user_id (+)'
|| ' and c.level_value = e.user_id (+)'
|| ' and c.level_value = g.responsibility_id (+)'
|| ' and c.level_value = h.application_id (+)'
|| ' and b.language = ''US'''
|| ' order by 1, 4, 5');
select user_id into l_user_id
from fnd_user where user_name = l_user_name;
sqltxt := 'select rg.application_id '||
'from fnd_user_responsibility rg '||
'where rg.responsibility_id = '||to_char(p_resp_id)||' '||
'and rg.user_id = '||to_char(l_user_id);
sqltxt := 'select rg.responsibility_application_id '||
'from fnd_user_resp_groups rg '||
'where rg.responsibility_id = '||to_char(p_resp_id)||' '||
'and rg.user_id = '||to_char(l_user_id);
select count(*) into l_counter
from all_tables z
where z.table_name = 'AD_BUGS'
and upper(z.owner) = 'APPLSYS';
l_sqltxt := 'select application_short_name'
|| ' , bug_number'
|| ' , creation_date'
|| ' from ad_bugs'
|| ' where upper(application_short_name) like '''
|| upper(p_app_short_name)
|| ''' and creation_date >= '''
|| nvl(to_char(p_start_date,'MM-DD-YYYY'),olddate)
|| ''' and bug_number like '''||p_bug_number||'''';
select name
into l_hold_name
from v$database;
select banner
into l_DB_Ver
from v$version
where banner like 'Oracle%';
select o.object_name, o.object_type, o.owner
from all_objects o
where o.status = 'INVALID'
and o.object_name like c_start_string escape '~'
and upper(o.owner) in ('APPS', 'JTF', 'APPLSYS')
order by o.object_name;
select substr(substr(s.text,instr(s.text,'$Header')+9),1,
instr(substr(s.text,instr(s.text,'$Header')+9),' ',1,2)-1) file_vers
from all_source s
where s.name = c_obj_name
and s.type = c_obj_type
and s.owner = c_obj_owner
and s.text like '%$Header%';
select to_char(sequence)||') LINE: '||to_char(line)||' CHR: '||
to_char(position)||' '||text error_row
from all_errors z
where z.name = c_obj_name
and z.type = c_obj_type
and z.owner = c_obj_owner;
Insert_HTML('
No Rows Selected
');
select count(*) into l_counter
from all_tab_columns z
where z.table_name = upper(p_tab)
and z.column_name = upper(p_col)
and upper(z.owner) = upper(p_owner);
select a.name sobname,
count(b.period_name) total_periods,
count(decode(b.closing_status,'O',b.PERIOD_NAME,null)) open_periods,
a.accounted_period_type period_type
from gl_sets_of_books a,
gl_period_statuses b
where a.set_of_books_id = b.set_of_books_id (+)
and b.application_id = p_appId
and a.set_of_books_id = p_sobId
and b.period_type = a.accounted_period_type
group by a.name, a.accounted_period_type;
select application_name
into l_appname
from fnd_application_vl
where application_id = p_appid ;
select user_period_type into l_user_period_type
from gl_period_types
where period_type = c1_rec.period_type;
SELECT period_name, start_date, end_date, sysdate
INTO l_period_name, l_start_date, l_end_date, l_sysdate
FROM gl_period_statuses
WHERE adjustment_period_flag = 'N'
AND period_type = c1_rec.period_type
AND start_date = (
SELECT MAX(start_date)
FROM gl_period_statuses
WHERE closing_status = 'O'
AND adjustment_period_flag = 'N'
AND period_type = c1_rec.period_type
AND application_id = p_appId
AND set_of_books_id = p_sobId )
AND closing_status = 'O'
AND application_id = p_appId
AND set_of_books_id = p_sobId;
SELECT period_name, start_date, end_date, sysdate
INTO l_period_name, l_start_date, l_end_date, l_sysdate
FROM gl_period_statuses
WHERE adjustment_period_flag = 'N'
AND period_type = c1_rec.period_type
AND sysdate between start_date and end_date
AND closing_status = 'O'
AND application_id = p_appId
AND set_of_books_id = p_sobId;
select id_flex_num flex_str_num,
id_flex_structure_name flex_str_name,
to_char(last_update_date,'MM-DD-YYYY HH24:MI:SS') last_updated,
cross_segment_validation_flag cross_val,
dynamic_inserts_allowed_flag dyn_insert,
enabled_flag enabled,
freeze_flex_definition_flag frozen
from fnd_id_flex_structures_vl
where id_flex_code = p_f_code
and enabled_flag ='Y'
and id_flex_num = nvl(p_f_num,id_flex_num);
select s.application_column_name col_name,
s.segment_name seg_name,
s.segment_num seg_num,
s.enabled_flag enabled,
s.required_flag required,
s.display_flag displayed,
s.flex_value_set_id value_set_id,
vs.flex_value_set_name value_set_name,
DECODE(vs.validation_type,
'I', 'Independent', 'N', 'None', 'D', 'Dependent',
'U', 'Special', 'P', 'Pair', 'F', 'Table',
'X', 'Translatable Independent', 'Y', 'Translatable Dependent',
vs.validation_type) validation_type,
s.security_enabled_flag seg_security,
nvl(vs.security_enabled_flag,'N') value_set_security
from fnd_id_flex_segments_vl s, fnd_flex_value_sets vs
where s.flex_value_set_id = vs.flex_value_set_id (+)
and s.id_flex_code = p_f_code
and s.id_flex_num = p_f_num
order by s.segment_num ;
select segment_prompt
from fnd_segment_attribute_values sav,
fnd_segment_attribute_types sat
where sav.attribute_value = 'Y'
and sav.segment_attribute_type <> 'GL_GLOBAL'
and sav.application_id = sat.application_id
and sav.id_flex_code = sat.id_flex_code
and sav.segment_attribute_type = sat.segment_attribute_type
and sav.id_flex_code = p_f_code
and sav.id_flex_num = p_f_num
and sav.application_column_name = p_col_name;
select id_flex_name into l_flex_name
from fnd_id_flexs
where id_flex_code = p_flex_code;
l_ret_array(4) := str.dyn_insert;
Tab1Print('Dynamic Inserts = '||str.dyn_insert);
Tab1Print('Last Updated Date = '||str.last_updated);
select count(*) into l_rule_count
from fnd_flex_value_rules_vl
where flex_value_set_id = seg.value_set_id;
select count(*) into l_rule_assign_count
from fnd_flex_value_rules_vl r,
fnd_flex_value_rule_usages ru
where r.flex_value_rule_id = ru.flex_value_rule_id
and r.flex_value_set_id = seg.value_set_id;
select user_profile_option_name,
nvl(start_date_active,sysdate-1),
nvl(end_date_active,sysdate+1)
into l_user_prof_name, l_start_date, l_end_date
from fnd_profile_options_vl
where profile_option_name = p_prof_name;
'column of the SQL select statement');
'(which can be null) must be passed for every column of the select');
line_out(to_char(l_row_counter)||' rows selected');
Displays the output of the 'select * from table' as an HTML table.
Examples:
begin
Display_Table('AR_SYSTEM_PARAMETERS_ALL', 'AR Parameters',
'Where Org_id <> -3113'
, 'order by org_id, set_of_books_id', 'N');
dummy := Display_SQL ('select * from ' || replace(upper(p_table_name),'V_$','V$') || l_newline || p_where_clause || hold_char || nvl(p_order_by_clause,'order by 1')
, nvl(p_table_alias, p_table_name)
, p_display_longs);
Displays the output of the 'select * from table' as an HTML table.
Returns:
Number of rows displayed.
Examples:
declare
num_rows number;
return(Display_SQL ('select * from ' || replace(upper(p_table_name),'V_$','V$') || l_newline || p_where_clause || l_newline || nvl(p_order_by_clause,'order by 1')
, nvl(p_table_alias, p_table_name)
, p_display_longs));
select release_name into l_appsver from fnd_product_groups;
select instance_name, host_name, version
into l_instance_name, l_host_name, l_version
from v$instance;
select upper(value) into l_language
from v$parameter
where name = 'nls_language';
select name into l_org_name
from hr_all_organization_units
where organization_id = g_org_id;
select substr(z.text, instr(z.text,'$Header')+10, 40)
into hold_version
from all_source z
where z.name = p_package
and z.type = p_type
and z.owner = p_schema
and z.text like '%$Header%';
select substr(ot.user_profile_option_name,1,45) user_profile_option_name,
substr(o.profile_option_name,1,30) profile_option_name,
decode(v.level_id,10001, 'Site', 10002, 'Appl',
10003, 'Resp',
10004, 'User') lev,
substr(decode(v.level_id,
10001, ' ',
10002, a.application_name,
10003, r.responsibility_name,
10004, u.user_name),1,20) lev_value,
v.profile_option_value opt_value
from fnd_profile_option_values v,
fnd_profile_options o,
fnd_profile_options_tl ot,
fnd_application_tl a,
fnd_responsibility_tl r,
fnd_user u
where o.application_id = nvl(p_application_id, o.application_id)
and o.profile_option_name = nvl(p_short_name, o.profile_option_name)
and v.LEVEL_VALUE =
decode(level_id, 10004, u.user_id, 10003, r.responsibility_id,
10002, a.application_id,10001,0)
and v.profile_option_id = o.profile_option_id
and v.application_id = o.application_id
and a.application_id (+) = v.level_value
and r.responsibility_id (+) = v.level_value
and u.user_id (+) = v.level_value
and ot.profile_option_name = o.profile_option_name
and nvl(ot.language,'US') = nvl(USERENV('LANG'),'US')
and sysdate between nvl(o.start_date_active, sysdate) and
nvl(o.end_date_active,sysdate)
and v.profile_option_value is not null
order by ot.user_profile_option_name, v.level_id,
decode(level_id,10001, 'Site', 10002, a.application_name, 10003,
r.responsibility_name, 10004, u.user_name);
select max(length(substr(ot.user_profile_option_name,1,45))) max_u_length,
max(length(substr(o.profile_option_name,1,30))) max_length,
max(length(decode(v.level_id,10001, 'Site', 10002, 'Appl',
10003, 'Resp',
10004, 'User'))),
max(length(substr(decode(v.level_id,
10001, ' ',
10002, a.application_name,
10003, r.responsibility_name,
10004, u.user_name),1,20))),
max(length(v.profile_option_value))
into max_user_opt, max_opt, max_lev, max_lev_value, max_opt_value
from fnd_profile_option_values v,
fnd_profile_options o,
fnd_profile_options_tl ot,
fnd_application_tl a,
fnd_responsibility_tl r,
fnd_user u
where o.application_id = nvl(p_application_id, o.application_id)
and o.profile_option_name = nvl(p_short_name, o.profile_option_name)
and v.LEVEL_VALUE =
decode(level_id, 10004, u.user_id, 10003, r.responsibility_id,
10002, a.application_id,10001,0)
and v.profile_option_id = o.profile_option_id
and v.application_id = o.application_id
and a.application_id (+) = v.level_value
and r.responsibility_id (+) = v.level_value
and u.user_id (+) = v.level_value
and ot.profile_option_name = o.profile_option_name
and nvl(ot.language,'US') = nvl(USERENV('LANG'),'US')
and sysdate between nvl(o.start_date_active, sysdate) and
nvl(o.end_date_active,sysdate)
and v.profile_option_value is not null;
select user_id into l_user_id
from fnd_user where user_name = l_user_name;
sqltxt := 'select rg.application_id '||
'from fnd_user_responsibility rg '||
'where rg.responsibility_id = '||to_char(p_resp_id)||' '||
'and rg.user_id = '||to_char(l_user_id);
sqltxt := 'select rg.responsibility_application_id '||
'from fnd_user_resp_groups rg '||
'where rg.responsibility_id = '||to_char(p_resp_id)||' '||
'and rg.user_id = '||to_char(l_user_id);
select count(*) into l_counter from all_tables z
where z.table_name = 'AD_BUGS'
and z.owner = 'APPLSYS';
sqltxt := 'select bug_number, creation_date, application_short_name ' ||
'from ad_bugs '||
'where upper(application_short_name) like '''||
p_app_short_name||''''||
'and bug_number like '''||p_bug_number||''''||
'and creation_date >= nvl(to_date('''||
to_char(p_start_date,'MM-DD-YYYY')||
''',''MM-DD-YYYY''),creation_date)';
select name
into l_hold_name
from v$database;
select banner
into l_DB_Ver
from v$version
where banner like 'Oracle%';
select z.text into vers_line
from dba_source z
where z.name = package_name
and z.owner = l_object_owner
and z.text like '%$Header%'
and z.type = 'PACKAGE BODY';
select z.text into vers_line
from dba_source z
where z.name = package_name
and z.owner = l_object_owner
and z.text like '%$Header%'
and z.type = 'PACKAGE';
select o.object_name, o.object_type, o.owner
from all_objects o
where o.status = 'INVALID'
and o.object_name like c_start_string escape '~'
and upper(o.owner) in ('APPS', 'JTF', 'APPLSYS')
order by o.object_name;
select substr(substr(s.text,instr(s.text,'$Header')+9),1,
instr(substr(s.text,instr(s.text,'$Header')+9),' ',1,2)-1) file_vers
from all_source s
where name = c_obj_name
and type = c_obj_type
and owner = c_obj_owner
and text like '%$Header%';
select to_char(z.sequence)||') LINE: '||to_char(z.line)||' CHR: '||
to_char(z.position)||' '||text error_row
from all_errors z
where z.name = c_obj_name
and z.type = c_obj_type
and z.owner = c_obj_owner;
Tab0Print(to_char(l_rows)||' rows selected');
procedure Insert_Style_Sheet IS
BEGIN
IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
Insert_Style_Sheet_html;
procedure Insert_HTML(p_text varchar2) IS
BEGIN
IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
Insert_HTML_html(p_text);
select oracle_username into apps_schema_name from fnd_oracle_userid where read_only_flag='U';