The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE insert_temp_numbers(p_id_array IN AMV_NUMBER_VARRAY_TYPE,
x_status OUT NOCOPY VARCHAR2);
PROCEDURE insert_temp_ids(p_stmt IN OUT NOCOPY DBMS_SQL.VARCHAR2S,
p_start_index IN PLS_INTEGER,
p_end_index IN PLS_INTEGER,
x_status OUT NOCOPY VARCHAR2);
l_select varchar2(100);
-- select construct
l_select := ' SELECT a.channel_id, a.channel_id, 50 ';
l_where := l_where || ' and a.last_update_date >= (sysdate - ' || p_days || ' )';
x_sql_statement(p_index) := l_select;
x_sql_statement(p_index) := l_select;
x_sql_statement(p_index) := l_select;
l_select varchar2(100);
l_mainSelect varchar2(100);
l_subSelect varchar2(100);
-- select construct
--l_select := ' SELECT a.item_id ';
l_subSelect := ' SELECT b.item_id ';
l_mainSelect := ' SELECT cim.item_id ';
--l_select := l_select || ', cim.channel_id';
l_mainSelect := l_mainSelect||' , cim.channel_id ';
--l_select := l_select || ', cim.channel_category_id';
l_mainSelect := l_mainSelect||', cim.channel_category_id';
--l_select := l_select || ', a.item_id';
l_mainSelect := l_mainSelect||', cim.item_id';
--l_select := l_select || ', 50 ';
l_mainSelect := l_mainSelect||', 50 ';
--l_where := l_where || ' AND a.last_update_date >= (sysdate - ' || p_days || ' )';
l_subWhere := l_subWhere || ' AND a.last_update_date >= (sysdate - ' || p_days || ' )';
--x_sql_statement(p_index) := l_select; -- 2
x_sqlSubSql(sqlSubSql_index) := l_subSelect; -- 2
--DBMS_OUTPUT.PUT_LINE('l_select: ' || l_select);
--x_sql_statement(p_index) := l_select;
--x_sql_statement(p_index) := l_select;
x_sql_statement(p_index) := l_mainSelect;
x_sql_statement(p_index) := ' select b.channel_id';
' and b.last_update_date >= (sysdate - ' || p_days || ' )';
select number_value
from amv_temp_numbers;
x_sql_statement(p_index) := ' select b.item_id';
' and b.last_update_date >= (sysdate - ' || p_days || ' )';
select number_value
from amv_temp_numbers;
x_sql_statement(p_index) := ' select b.item_id';
' and b.last_update_date >= (sysdate - ' || p_days || ' )';
select number_value
from amv_temp_numbers;
x_sql_statement(p_index) := ' select b.item_id';
' and b.last_update_date >= (sysdate - ' || p_days || ' )';
select number_value
from amv_temp_numbers;
x_sql_statement(p_index) := ' select b.item_id';
' and b.last_update_date >= (sysdate - ' || p_days || ' )';
select b.channel_id
from amv_c_channels_vl b
where b.channel_type = G_CONTENT
and b.access_level_type = G_PUBLIC
and b.application_id = p_application_id
UNION ALL
select b.channel_id
from amv_c_channels_vl b
where b.channel_type = G_PRIVATE
and b.owner_user_id = p_user_id
UNION ALL
select b.channel_id
from amv_c_channels_vl b
, amv_u_access au
, jtf_rs_group_members_vl jgm
where b.channel_type = G_GROUP
and b.channel_id = au.access_to_table_record_id
and au.access_to_table_code = G_CHANNEL
and au.user_or_group_type = G_GROUP
and au.user_or_group_id = jgm.group_id
and jgm.resource_id = p_user_id;
select channel_category_id
, channel_category_name
from amv_c_categories_vl
where application_id = p_application_id;
PROCEDURE insert_temp_numbers(p_id_array IN AMV_NUMBER_VARRAY_TYPE,
x_status OUT NOCOPY VARCHAR2)
IS
l_stmt varchar2(200) :='INSERT INTO amv_temp_numbers (number_value) VALUES (:id)';
--DBMS_OUTPUT.PUT_LINE('Enter : insert_temp_numbers' );
-- delete channels from temp table
--TRUNCATE also empties the session entries into amv_temp_ids
--Since temp tables are empty at beginning of session, then
-- amv_temp_ids must also be empty after TRUNCATE
l_return_status := FND_INSTALLATION.get_app_info('AMV',l_status,l_industry,l_schema);
-- build ids insert statement
FOR i in 1..p_id_array.count LOOP
--DBMS_OUTPUT.PUT_LINE('insert into amv_temp_numbers (number_value) values (' || p_id_array(i) ||');');
END insert_temp_numbers;
PROCEDURE insert_temp_ids(p_stmt IN OUT NOCOPY DBMS_SQL.VARCHAR2S,
p_start_index IN PLS_INTEGER,
p_end_index IN PLS_INTEGER,
x_status OUT NOCOPY VARCHAR2)
IS
l_ins_stmt varchar2(100) := 'INSERT INTO amv_temp_ids (id,number_value,score) ';
TRUE, -- Insert linefeed for each row
DBMS_SQL.NATIVE);
--DBMS_OUTPUT.PUT_LINE('Others : insert_temp_ids' );
--DBMS_OUTPUT.PUT_LINE('Exception : insert_temp_ids' );
END insert_temp_ids;
l_last_update_date date;
select chn.channel_id
, chn.last_update_date
, chn.channel_name
, chn.description
, tmp.number_value
, max(tmp.score)
from amv_c_channels_vl chn
, amv_temp_ids tmp
where chn.channel_id = tmp.id
group by chn.channel_id, chn.last_update_date, chn.channel_name, chn.description, tmp.number_value
order by max(tmp.score) desc;
select sum(count(distinct id))
from amv_temp_ids
group by number_value;
select channel_category_id
, channel_name
from amv_c_channels_vl
where channel_id = l_id;
l_last_update_date,
l_channel_name,
l_description,
l_id,
l_score;
to_char(l_last_update_date, 'DD-MON-YYYY');
to_char(l_last_update_date, 'DD-MON-YYYY'),
l_id,
G_CATEGORY);
l_last_update_date date;
select itm.item_id, itm.last_update_date, itm.item_name,
itm.description, max(tmp.score)
from jtf_amv_items_vl itm, amv_temp_ids tmp
where itm.item_id = tmp.id
group by itm.item_id, itm.last_update_date, itm.item_name, itm.description
order by max(tmp.score) desc;
select sum(count(distinct id))
from amv_temp_ids
group by id;
select number_value
, max(score)
from amv_temp_ids tmp
where id = l_item_id
group by number_value;
select channel_category_id
, channel_category_name
from amv_c_categories_vl
where channel_category_id = l_id;
select channel_id
, channel_name
from amv_c_channels_vl
where channel_id = l_id;
select count(*) into l_count from amv_temp_ids;
l_last_update_date,
l_item_name,
l_description,
l_score;
to_char(l_last_update_date, 'DD-MON-YYYY');
to_char(l_last_update_date,'DD-MON-YYYY'),
l_id,
p_search_level);
l_chan_insert_status varchar2(1);
-- reset p_index since we are inserting right after each
-- sub-statement construction
-- initial input 'p_index' should be '2' as well
p_index := l_start_index;
insert_temp_ids(p_stmt => x_chan_sql_stmt,
p_start_index => l_start_index,
p_end_index => p_index,
x_status => l_chan_insert_status);
x_chan_sql_stmt.delete;
insert_temp_ids(p_stmt => x_chan_sql_stmt,
p_start_index => l_start_index,
p_end_index => p_index,
x_status => l_chan_insert_status);
x_chan_sql_stmt.delete;
insert_temp_ids(p_stmt => x_chan_sql_stmt,
p_start_index => l_start_index,
p_end_index => p_index,
x_status => l_chan_insert_status);
x_chan_sql_stmt.delete;
l_item_insert_status varchar2(1);
x_item_sql_stmt.delete;
insert_temp_ids(p_stmt => x_item_sql_stmt,
p_start_index => l_start_index,
p_end_index => p_index,
x_status => l_item_insert_status);
x_item_sql_stmt.delete;
insert_temp_ids(p_stmt => x_item_sql_stmt,
p_start_index => l_start_index,
p_end_index => p_index,
x_status => l_item_insert_status);
x_item_sql_stmt.delete;
insert_temp_ids(p_stmt => x_item_sql_stmt,
p_start_index => l_start_index,
p_end_index => p_index,
x_status => l_item_insert_status);
x_item_sql_stmt.delete;
insert_temp_ids(p_stmt => x_item_sql_stmt,
p_start_index => l_start_index,
p_end_index => p_index,
x_status => l_item_insert_status);
x_item_sql_stmt.delete;
insert_temp_ids(p_stmt => x_item_sql_stmt,
p_start_index => l_start_index,
p_end_index => p_index,
x_status => l_item_insert_status);
x_item_sql_stmt.delete;
insert_temp_ids(p_stmt => x_item_sql_stmt,
p_start_index => l_start_index,
p_end_index => p_index,
x_status => l_item_insert_status);
x_item_sql_stmt.delete;
insert_temp_ids(p_stmt => x_item_sql_stmt,
p_start_index => l_start_index,
p_end_index => p_index,
x_status => l_item_insert_status);
x_item_sql_stmt.delete;
insert_temp_ids(p_stmt => x_item_sql_stmt,
p_start_index => l_start_index,
p_end_index => p_index,
x_status => l_item_insert_status);
x_item_sql_stmt.delete;
insert_temp_ids(p_stmt => x_item_sql_stmt,
p_start_index => l_start_index,
p_end_index => p_index,
x_status => l_item_insert_status);
x_item_sql_stmt.delete;
insert_temp_ids(p_stmt => x_item_sql_stmt,
p_start_index => l_start_index,
p_end_index => p_index,
x_status => l_item_insert_status);
x_item_sql_stmt.delete;
insert_temp_ids(p_stmt => x_item_sql_stmt,
p_start_index => l_start_index,
p_end_index => p_index,
x_status => l_item_insert_status);
x_item_sql_stmt.delete;
insert_temp_ids(p_stmt => x_item_sql_stmt,
p_start_index => l_start_index,
p_end_index => p_index,
x_status => l_item_insert_status);
x_item_sql_stmt.delete;
l_chan_insert_status varchar2(1);
l_item_insert_status varchar2(1);
-- execute sql statement and insert into temp table
--insert_temp_ids(p_stmt => l_chan_sql_stmt,
-- p_start_index => l_start_index,
-- p_end_index => l_index - 1, -- 1 for union
-- x_status => l_chan_insert_status);
-- execute sql statement and insert into temp table
--insert_temp_ids(p_stmt => l_item_sql_stmt,
-- p_start_index => l_start_index,
-- p_end_index => l_index -1, -- 1 for union
-- x_status => l_item_insert_status);
l_sql_statement := 'SELECT b.entity_id, ' ||
' b.table_name, ' ||
' tl.entity_name, ' ||
' b.status, ' ||
' b.object_version_number ' ||
'FROM amv_d_entities_b b ' ||
', amv_d_entities_tl tl ' ||
'WHERE b.usage_indicator = '''||
G_AMV_SEARCH||'''';
l_sql_statement := 'SELECT eb.entity_id, ' ||
' eb.table_name, ' ||
' ab.data_type, ' ||
' ab.attribute_id, ' ||
' ab.column_name, ' ||
' atl.attribute_name, ' ||
' ab.status, ' ||
' ab.object_version_number ' ||
'FROM amv_d_entities_b eb ' ||
', amv_d_entities_tl etl ' ||
', amv_d_ent_attributes_b ab ' ||
', amv_d_ent_attributes_tl atl ' ||
'WHERE ab.usage_indicator = '''|| G_AMV_SEARCH ||'''';
l_id_insert_status varchar2(1);
cursor id_csr is select id from amv_temp_ids;
insert_temp_numbers(p_id_array => l_category_array,
x_status => l_id_insert_status);
insert_temp_numbers(p_id_array => l_channel_array,
x_status => l_id_insert_status);