eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.MSD_DEM_COLLECT_PRICE_LISTS SQL Statements

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

Line: 13


select meaning
from fnd_lookup_values
where lookup_type = p_lookup_type
and lookup_code = p_lookup_code
and language = 'US';

Line: 20


select fnd_profile.value('MSD_DEM_SCHEMA')
from dual;

Line: 57


select table_name, column_name
from msd_dem_entities_inuse
where ebs_entity = 'PRL'
and demantra_entity = 'DISPLAY_UNIT'
and internal_name = p_prl_code;

Line: 81


			msd_dem_common_utilities.log_message('Price List deleted. Please recreate the Price List');

Line: 82


			msd_dem_common_utilities.log_debug('Price List deleted. Please recreate the Price List');

Line: 115


		l_stmt_get_series_id := 'select forecast_type_id from ' || get_lookup_value('MSD_DEM_TABLES', 'COMPUTED_FIELDS') ||
														' where computed_name = ''' || l_column_name || '''';

Line: 126


    l_stmt := 'update ' || get_lookup_value('MSD_DEM_TABLES', 'TRANSFER_QUERY_SERIES')  || ' tqs set tqs.load_option = 0, tqs.purge_option = 0 '
				          || ' where tqs.id = (select tq.id from ' || get_lookup_value('MSD_DEM_TABLES', 'TRANSFER_LIST')  || ' tl '
									|| ' ,' || get_lookup_value('MSD_DEM_TABLES', 'TRANSFER_QUERY')  || ' tq '
									|| ' where tl.name = :1 '
									|| ' and tq.transfer_id = tl.id) and tqs.series_id = ' || l_series_id;

Line: 153


select 1 from
msd_dem_entities_inuse
where internal_name = p_prl_code
and ebs_entity = 'PRL';

Line: 200


		l_stmt_get_seeded_unit := 'select display_units from ' || get_lookup_value('MSD_DEM_TABLES', 'DISPLAY_UNITS') ||
		                                   ' where display_units = ''' || p_prl_code || '''';

Line: 220


				l_stmt_new_prl_display_unit := 'select display_units ,display_units_id ,data_table ,data_field ' ||
																			 ' from ' || get_lookup_value('MSD_DEM_TABLES', 'DISPLAY_UNITS') || ' ' ||
																			 ' where display_units_id in ' ||
																			 ' (select distinct display_units_id from ' || get_lookup_value('MSD_DEM_TABLES', 'DISPLAY_UNITS') || ' ' ||
			 																 ' minus ' ||
			 																 ' select distinct display_units_id from ' || get_lookup_value('MSD_DEM_TABLES', 'DCM_PRODUCTS_UNITS') || ' ' || ')' ||
																			 ' and display_units like ''%EBSPRICELIST%'' and rownum < 2';

Line: 242


				l_stmt_get_profile_table := 'select tq.table_name from ' || get_lookup_value('MSD_DEM_TABLES', 'TRANSFER_LIST')  || ' tl '
																		|| ' ,' || get_lookup_value('MSD_DEM_TABLES', 'TRANSFER_QUERY')  || ' tq '
																		|| ' where tl.name = ''' || fnd_profile.value('MSD_DEM_PRICE_LIST_PROFILE') || ''''
																		|| ' and tq.transfer_id = tl.id';

Line: 254


				l_stmt := 'insert into msd_dem_entities_inuse(
									 ebs_entity
                   ,demantra_entity
                   ,internal_name
                   ,table_name
                   ,column_name
                   ,last_update_date
	                 ,last_updated_by
	                 ,creation_date
	                 ,created_by
	                 ,last_update_login
                   ) values
                   (
                   ''PRL''
                   ,''DISPLAY_UNIT''
                   ,:1
                   ,:2
                   ,:3
                   ,:4
                   ,:5
                   ,:6
                   ,:7
                   ,:8
                   )';

Line: 284


				l_stmt := 'update ' || get_lookup_value('MSD_DEM_TABLES', 'DISPLAY_UNITS') ||
									' set display_units = :1 ' ||
									' where display_units_id = :2';

Line: 292


				l_stmt := 'insert into ' || get_lookup_value('MSD_DEM_TABLES', 'AVAIL_UNITS') ||
				          ' (group_table_id ,display_units_id )' ||
				          ' (select group_table_id, :1 ' ||
				          ' from ' || get_lookup_value('MSD_DEM_TABLES', 'GROUP_TABLES') ||
				          ' where group_type = 1 ' ||
				          ' )';

Line: 303


				l_stmt_get_component := 'select dcm_product_id from ' ||get_lookup_value('MSD_DEM_TABLES', 'DCM_PRODUCTS') || ' where product_name = ''' || msd_dem_common_utilities.get_lookup_value('MSD_DEM_COMPONENTS', 'DEMAND_MANAGEMENT') || '''';

Line: 311


				l_stmt := 'insert into ' || get_lookup_value('MSD_DEM_TABLES', 'DCM_PRODUCTS_UNITS') ||
									' (dcm_product_id ,display_units_id) ' ||
									' (select :1, :2 from dual)';

Line: 318


				l_stmt := 'insert into ' || get_lookup_value('MSD_DEM_TABLES', 'INDEXES_FOR_UNITS') ||
				          ' (display_units_id, real_value_id) ' ||
				          ' (select :1, real_value_id from ' || get_lookup_value('MSD_DEM_TABLES', 'DCM_PRODUCTS_INDEX') || ' dpi ' ||
				          ' where dpi.dcm_product_id = ' || l_component_id ||
				          ')';

Line: 330


				l_stmt_get_component_sop := 'select dcm_product_id from ' ||get_lookup_value('MSD_DEM_TABLES', 'DCM_PRODUCTS') || ' where product_name = ''' || msd_dem_common_utilities.get_lookup_value('MSD_DEM_COMPONENTS', 'SOP') || '''';

Line: 338


				l_stmt := 'insert into ' || get_lookup_value('MSD_DEM_TABLES', 'DCM_PRODUCTS_UNITS') ||
									' (dcm_product_id ,display_units_id) ' ||
									' (select :1, :2 from dual)';

Line: 345


				l_stmt := 'update ' || get_lookup_value('MSD_DEM_TABLES', 'COMPUTED_FIELDS')
									|| ' set computed_title = :1 '
									|| ' where computed_name = :2 ';

Line: 354


				l_stmt := 'update msd_dem_entities_inuse set table_name = ''biio_ebs_price_list''' ||
				          ', column_name =  (select data_field from ' || get_lookup_value('MSD_DEM_TABLES', 'DISPLAY_UNITS') || ' where display_units = :1)' ||
				          'where ebs_entity = ''PRL'' and demantra_entity = ''DISPLAY_UNIT'' and internal_name = :2';

Line: 397


		l_stmt := 'update ' || get_lookup_value('MSD_DEM_TABLES', 'TRANSFER_QUERY_SERIES')  || ' tqs set tqs.load_option = 2, tqs.purge_option = 0 '
				          || ' where tqs.id = (select tq.id from ' || get_lookup_value('MSD_DEM_TABLES', 'TRANSFER_LIST')  || ' tl '
									|| ' ,' || get_lookup_value('MSD_DEM_TABLES', 'TRANSFER_QUERY')  || ' tq '
									|| ' where tl.name = :1 '
									|| ' and tq.transfer_id = tl.id)';

Line: 409


		l_stmt := 'select distinct price_list_name price_list_name
							 from msd_dem_price_lists_gtt';

Line: 429


			l_stmt := 'select price_list_name from (' || l_stmt;

Line: 503


select count(*)
from msd_dem_price_lists;

Line: 509


select price_list_name
from msd_dem_price_lists;

Line: 607


			msd_dem_common_utilities.log_message('Price list collection cannot continue as no price lists are selected in the price list form');

Line: 608


			msd_dem_common_utilities.log_debug('Price list collection cannot continue as no price lists are selected in the price list form');

Line: 614


			msd_dem_common_utilities.log_message('Include Price List does not contain any price list among the price lists, which are selected in the price list form.');

Line: 615


			msd_dem_common_utilities.log_debug('Include Price List does not contain any price list among the price lists, which are selected in the price list form.');

Line: 752


                   x_stmt:=  'SELECT tq.id FROM ' || get_lookup_value('MSD_DEM_TABLES', 'TRANSFER_LIST')  || ' tl '
								|| ' ,' || get_lookup_value('MSD_DEM_TABLES', 'TRANSFER_QUERY')  || ' tq '
								|| ' WHERE tl.name = ''' || fnd_profile.value('MSD_DEM_PRICE_LIST_PROFILE') || ''''
								|| ' AND tq.transfer_id = tl.id';

Line: 771


		      msd_dem_common_utilities.log_message('WARNING: Failed to update the price list data profile');

Line: 796


procedure delete_price_lists(errbuf              out nocopy varchar2,
														 retcode             out nocopy number,
														 p_list              in  varchar2)
as

cursor entities_inuse_cur(p_price_list_name varchar2) is
select internal_name, column_name
from msd_dem_entities_inuse
where internal_name = p_price_list_name
and ebs_entity = 'PRL';

Line: 818


l_stmt_deletes varchar2(1000);

Line: 820


l_stmt_updates varchar2(1000);

Line: 858


					l_stmt_prl_display_unit_id := 'select display_units_id ' ||
																				' from ' || get_lookup_value('MSD_DEM_TABLES', 'DISPLAY_UNITS') ||
																				' where display_units = ''' || l_internal_name || '''';

Line: 869


					l_stmt_deletes := 'delete from ' || get_lookup_value('MSD_DEM_TABLES', 'AVAIL_UNITS') ||
														' where display_units_id = :1';

Line: 872


					msd_dem_common_utilities.log_message(l_stmt_deletes);

Line: 873


					msd_dem_common_utilities.log_debug(l_stmt_deletes);

Line: 875


					execute immediate l_stmt_deletes using l_display_unit_id;

Line: 877


					l_stmt_deletes := 'delete from ' || get_lookup_value('MSD_DEM_TABLES', 'DCM_PRODUCTS_UNITS') ||
														' where display_units_id = :1';

Line: 880


					msd_dem_common_utilities.log_message(l_stmt_deletes);

Line: 881


					msd_dem_common_utilities.log_debug(l_stmt_deletes);

Line: 883


					execute immediate l_stmt_deletes using l_display_unit_id;

Line: 885


					l_stmt_deletes := 'delete from ' || get_lookup_value('MSD_DEM_TABLES', 'INDEXES_FOR_UNITS') ||
														' where display_units_id = :1';

Line: 888


					msd_dem_common_utilities.log_message(l_stmt_deletes);

Line: 889


					msd_dem_common_utilities.log_debug(l_stmt_deletes);

Line: 891


					execute immediate l_stmt_deletes using l_display_unit_id;

Line: 893


					l_stmt_updates := 'update ' || get_lookup_value('MSD_DEM_TABLES', 'DISPLAY_UNITS') ||
														' set display_units = :1 where display_units_id = :2';

Line: 896


					msd_dem_common_utilities.log_message(l_stmt_updates);

Line: 897


					msd_dem_common_utilities.log_debug(l_stmt_updates);

Line: 899


					execute immediate l_stmt_updates using l_column_name, l_display_unit_id;

Line: 901


					l_stmt_updates := 'update ' || get_lookup_value('MSD_DEM_TABLES', 'COMPUTED_FIELDS') ||
														' set computed_title = :1 ' ||
														' where computed_name = :2 ';

Line: 905


					msd_dem_common_utilities.log_message(l_stmt_updates);

Line: 906


					msd_dem_common_utilities.log_debug(l_stmt_updates);

Line: 908


					execute immediate l_stmt_updates using l_column_name, l_column_name;

Line: 910


					l_stmt_deletes := 'delete from msd_dem_entities_inuse' ||
														' where internal_name = :1 and ebs_entity = ''PRL''';

Line: 913


					msd_dem_common_utilities.log_message(l_stmt_deletes);

Line: 914


					msd_dem_common_utilities.log_debug(l_stmt_deletes);

Line: 916


					execute immediate l_stmt_deletes using l_internal_name;

Line: 918


					l_stmt_updates := 'update ' || get_lookup_value('MSD_DEM_TABLES', 'SALES_DATA') ||
														' set ' || l_column_name || ' = null';

Line: 921


					msd_dem_common_utilities.log_message(l_stmt_updates);

Line: 922


					msd_dem_common_utilities.log_debug(l_stmt_updates);

Line: 924


					execute immediate l_stmt_updates;

Line: 928


					msd_dem_common_utilities.log_message('Deleted price list ' || l_internal_name);

Line: 929


					msd_dem_common_utilities.log_debug('Deleted price list ' || l_internal_name);