DBA Data[Home] [Help]

APPS.AMV_SEARCH_PVT SQL Statements

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

Line: 395

PROCEDURE insert_temp_numbers(p_id_array IN  AMV_NUMBER_VARRAY_TYPE,

			      x_status 	 OUT NOCOPY VARCHAR2);
Line: 401

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);
Line: 953

l_select		varchar2(100);
Line: 989

	-- select construct

	l_select := ' SELECT a.channel_id, a.channel_id, 50 ';
Line: 1013

	 l_where  := l_where || ' and a.last_update_date >= (sysdate - ' || p_days || ' )';
Line: 1027

	  x_sql_statement(p_index) := l_select;
Line: 1129

	  		x_sql_statement(p_index) := l_select;
Line: 1157

	  x_sql_statement(p_index) := l_select;
Line: 1257

l_select		varchar2(100);
Line: 1288

l_mainSelect		varchar2(100);
Line: 1292

l_subSelect		varchar2(100);
Line: 1309

	-- select construct

	--l_select := ' SELECT a.item_id ';
Line: 1313

	l_subSelect := ' SELECT b.item_id ';
Line: 1317

	l_mainSelect := ' SELECT cim.item_id ';
Line: 1324

	 --l_select := l_select || ', cim.channel_id';
Line: 1327

		l_mainSelect := l_mainSelect||' , cim.channel_id ';
Line: 1332

	 --l_select := l_select || ', cim.channel_category_id';
Line: 1335

		l_mainSelect := l_mainSelect||', cim.channel_category_id';
Line: 1340

	 --l_select := l_select || ', a.item_id';
Line: 1343

		l_mainSelect := l_mainSelect||', cim.item_id';
Line: 1352

		--l_select := l_select || ', 50 ';
Line: 1355

		l_mainSelect := l_mainSelect||', 50 ';
Line: 1411

	 --l_where  := l_where || ' AND a.last_update_date >= (sysdate - ' || p_days || ' )';
Line: 1415

	 	 l_subWhere  := l_subWhere || ' AND a.last_update_date >= (sysdate - ' || p_days || ' )';
Line: 1520

	  --x_sql_statement(p_index) := l_select;		-- 2
Line: 1534

	  x_sqlSubSql(sqlSubSql_index) := l_subSelect;		-- 2
Line: 1549

	--DBMS_OUTPUT.PUT_LINE('l_select: ' || l_select);
Line: 1706

	  		--x_sql_statement(p_index) := l_select;
Line: 1756

	  --x_sql_statement(p_index) := l_select;
Line: 1826

	x_sql_statement(p_index) := l_mainSelect;
Line: 1923

	x_sql_statement(p_index) := ' select b.channel_id';
Line: 2133

		' and b.last_update_date >= (sysdate - ' || p_days || ' )';
Line: 2195

select number_value

from amv_temp_numbers;
Line: 2270

	x_sql_statement(p_index) := ' select b.item_id';
Line: 2510

		' and b.last_update_date >= (sysdate - ' || p_days || ' )';
Line: 2634

select number_value

from amv_temp_numbers;
Line: 2710

	x_sql_statement(p_index) := ' select b.item_id';
Line: 2884

		' and b.last_update_date >= (sysdate - ' || p_days || ' )';
Line: 3020

select number_value

from amv_temp_numbers;
Line: 3098

	x_sql_statement(p_index) := ' select b.item_id';
Line: 3262

		' and b.last_update_date >= (sysdate - ' || p_days || ' )';
Line: 3382

select number_value

from amv_temp_numbers;
Line: 3460

	x_sql_statement(p_index) := ' select b.item_id';
Line: 3624

		' and b.last_update_date >= (sysdate - ' || p_days || ' )';
Line: 3732

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;
Line: 3844

select channel_category_id

,	  channel_category_name

from   amv_c_categories_vl

where	  application_id = p_application_id;
Line: 4072

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)';
Line: 4089

	--DBMS_OUTPUT.PUT_LINE('Enter : insert_temp_numbers' );
Line: 4091

   	-- 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);
Line: 4106

  	-- 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) ||');');
Line: 4134

END insert_temp_numbers;
Line: 4140

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) ';
Line: 4220

		  	TRUE,             -- Insert linefeed for each row

	    		DBMS_SQL.NATIVE);
Line: 4252

	--DBMS_OUTPUT.PUT_LINE('Others : insert_temp_ids' );
Line: 4254

	--DBMS_OUTPUT.PUT_LINE('Exception : insert_temp_ids' );
Line: 4260

END insert_temp_ids;
Line: 4284

l_last_update_date	date;
Line: 4308

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;
Line: 4334

select sum(count(distinct id))

from	  amv_temp_ids

group by number_value;
Line: 4344

select channel_category_id

,	  channel_name

from	  amv_c_channels_vl

where  channel_id = l_id;
Line: 4378

							l_last_update_date,

							l_channel_name,

							l_description,

							l_id,

							l_score;
Line: 4424

			                    to_char(l_last_update_date, 'DD-MON-YYYY');
Line: 4448

						to_char(l_last_update_date, 'DD-MON-YYYY'),

						l_id,

						G_CATEGORY);
Line: 4518

l_last_update_date	date;
Line: 4546

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;
Line: 4562

select sum(count(distinct id))

from	  amv_temp_ids

group by id;
Line: 4572

select number_value

,	  max(score)

from	  amv_temp_ids tmp

where id = l_item_id

group by number_value;
Line: 4586

select channel_category_id

,	  channel_category_name

from	  amv_c_categories_vl

where  channel_category_id = l_id;
Line: 4598

select channel_id

,	  channel_name

from	  amv_c_channels_vl

where  channel_id = l_id;
Line: 4620

select count(*) into l_count from amv_temp_ids;
Line: 4652

							l_last_update_date,

							l_item_name,

							l_description,

							l_score;
Line: 4718

			                    to_char(l_last_update_date, 'DD-MON-YYYY');
Line: 4742

							to_char(l_last_update_date,'DD-MON-YYYY'),

							l_id,

							p_search_level);
Line: 4832

l_chan_insert_status varchar2(1);
Line: 4872

		-- 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;
Line: 4924

				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);
Line: 4932

				x_chan_sql_stmt.delete;
Line: 4984

				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);
Line: 4992

				x_chan_sql_stmt.delete;
Line: 5028

			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);
Line: 5036

				x_chan_sql_stmt.delete;
Line: 5116

l_item_insert_status varchar2(1);
Line: 5148

		x_item_sql_stmt.delete;
Line: 5204

				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);
Line: 5212

				x_item_sql_stmt.delete;
Line: 5260

				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);
Line: 5268

				x_item_sql_stmt.delete;
Line: 5324

			  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);
Line: 5332

			   x_item_sql_stmt.delete;
Line: 5384

			  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);
Line: 5392

			   x_item_sql_stmt.delete;
Line: 5436

   			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);
Line: 5444

			 x_item_sql_stmt.delete;
Line: 5484

   			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);
Line: 5492

			 x_item_sql_stmt.delete;
Line: 5536

		         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);
Line: 5544

			 x_item_sql_stmt.delete;
Line: 5582

	 		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);
Line: 5590

			x_item_sql_stmt.delete;
Line: 5636

	 		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);
Line: 5644

			x_item_sql_stmt.delete;
Line: 5682

			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);
Line: 5690

			x_item_sql_stmt.delete;
Line: 5730

	 		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);
Line: 5738

			x_item_sql_stmt.delete;
Line: 5776

	 		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);
Line: 5784

			x_item_sql_stmt.delete;
Line: 5870

l_chan_insert_status varchar2(1);
Line: 5872

l_item_insert_status varchar2(1);
Line: 5970

	  	-- 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);
Line: 6048

	  	-- 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);
Line: 6416

    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||'''';
Line: 7026

    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 ||'''';
Line: 7810

l_id_insert_status	 varchar2(1);
Line: 7834

cursor id_csr is select id from amv_temp_ids;
Line: 8144

 		insert_temp_numbers(p_id_array => l_category_array,

						x_status 	 => l_id_insert_status);
Line: 8198

  		insert_temp_numbers(p_id_array => l_channel_array,

						x_status 	 => l_id_insert_status);