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: 129


    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_QUERY')  || ' tq '
									|| ' where tq.transfer_id = ' || msd_dem_common_utilities.get_app_id_text('MSD_DEM_DEMANTRA_OBJECT_ID', 'INTG_INF_EBS_PRICE_LIST', 1, 'id')
                                    || ') and tqs.series_id = ' || l_series_id;

Line: 157


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

Line: 204


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

Line: 226


				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 data_field in ' ||
                                                 '  (select data_field from ' || get_lookup_value('MSD_DEM_TABLES', 'DISPLAY_UNITS') || ' where data_field like ''EBSPRICELIST%'' ' ||
                                                 '   minus ' ||
                                                 '   select column_name from msd_dem_entities_inuse  where ebs_entity = ''PRL'' )' ||
                                                 ' and rownum < 2';

Line: 254


				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 tq.transfer_id = ' || msd_dem_common_utilities.get_app_id_text('MSD_DEM_DEMANTRA_OBJECT_ID', 'INTG_INF_EBS_PRICE_LIST', 1, 'id');

Line: 266


				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: 296


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

Line: 306


				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 ' ||
                                          ' minus ' ||
                                          ' select group_table_id, display_units_id ' ||
                                          ' from ' || get_lookup_value('MSD_DEM_TABLES', 'AVAIL_UNITS') ||
                                          ' where display_units_id = :2 ' ||
				          ' )';

Line: 322


                /*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: 332


				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: 339


				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: 351


				/*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: 361


				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: 369


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

Line: 378


				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: 422


    	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_QUERY')  || ' tq '
									|| ' where tq.transfer_id = ' || msd_dem_common_utilities.get_app_id_text('MSD_DEM_DEMANTRA_OBJECT_ID', 'INTG_INF_EBS_PRICE_LIST', 1, 'id')
                                    || ')';

Line: 434


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

Line: 454


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

Line: 528


select count(*)
from msd_dem_price_lists;

Line: 534


select price_list_name
from msd_dem_price_lists;

Line: 633


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

Line: 634


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

Line: 640


			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: 641


			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: 725


  select instance_type
  from msc_apps_instances
  where instance_id = p_instance_id;

Line: 814


                   x_stmt:=  'SELECT tq.id FROM '
                                || get_lookup_value('MSD_DEM_TABLES', 'TRANSFER_QUERY')  || ' tq '
								|| ' WHERE tq.transfer_id = ' || msd_dem_common_utilities.get_app_id_text('MSD_DEM_DEMANTRA_OBJECT_ID', 'INTG_INF_EBS_PRICE_LIST', 1, 'id');

Line: 833


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

Line: 875


		   x_stmt := 'SELECT dbname FROM ' || x_schema || '.computed_fields '
		             || ' WHERE ' || msd_dem_common_utilities.get_app_id_text('MSD_DEM_DEMANTRA_OBJECT_ID', 'SERIES_UNIT_PRICE', 2, null);

Line: 921


		   /* Build insert statement for biio_cto_option_price */
		   x_stmt := 'INSERT /*+ APPEND NOLOGGING */ INTO ' || x_schema || '.BIIO_CTO_OPTION_PRICE '
		             || ' ( SDATE, LEVEL1, OPTION_PRICE ) '
		             || ' SELECT '
		             || '    SDATE, '
		             || '    LEVEL1, '
		             || x_price_list_col || ' '
		             || ' FROM ' || x_schema || '.MSD_DEM_PRICE_LIST '
		             || ' WHERE ' || x_price_list_col || ' IS NOT NULL ';

Line: 937


		   msd_dem_common_utilities.log_debug ('Number of rows inserted - ' || to_char(x_num_rows));

Line: 959


		x_stmt := 'SELECT count(1) FROM dba_objects WHERE owner = upper(''' || x_schema || ''') AND object_type = ''TABLE'' '
		          || ' AND object_name IN (''BIIO_SCENARIO_PRICE'', ''BIIO_SCENARIO_PRICE_ERR'') ';

Line: 970


		   x_stmt := 'SELECT dbname FROM ' || x_schema || '.computed_fields '
		             || ' WHERE ' || msd_dem_common_utilities.get_app_id_text('MSD_DEM_DEMANTRA_OBJECT_ID', 'SERIES_UNIT_PRICE', 2, null);

Line: 1016


		   /* Build insert statement for biio_scenario_price */
		   x_stmt := 'INSERT /*+ APPEND NOLOGGING */ INTO ' || x_schema || '.BIIO_SCENARIO_PRICE '
		             || ' ( SDATE, LEVEL1, SCENARIO_PRICE ) '
		             || ' SELECT '
		             || '    SDATE, '
		             || '    LEVEL1, '
		             || x_price_list_col || ' '
		             || ' FROM ' || x_schema || '.MSD_DEM_PRICE_LIST '
		             || ' WHERE ' || x_price_list_col || ' IS NOT NULL ';

Line: 1032


		   msd_dem_common_utilities.log_debug ('Number of rows inserted - ' || to_char(x_num_rows));

Line: 1050


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: 1072


l_stmt_deletes varchar2(1000);

Line: 1074


l_stmt_updates varchar2(1000);

Line: 1112


					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: 1123


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

Line: 1126


					msd_dem_common_utilities.log_message(l_stmt_deletes);

Line: 1127


					msd_dem_common_utilities.log_debug(l_stmt_deletes);

Line: 1129


					execute immediate l_stmt_deletes using l_display_unit_id;

Line: 1131


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

Line: 1134


					msd_dem_common_utilities.log_message(l_stmt_deletes);

Line: 1135


					msd_dem_common_utilities.log_debug(l_stmt_deletes);

Line: 1137


					execute immediate l_stmt_deletes using l_display_unit_id;

Line: 1139


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

Line: 1142


					msd_dem_common_utilities.log_message(l_stmt_deletes);

Line: 1143


					msd_dem_common_utilities.log_debug(l_stmt_deletes);

Line: 1145


					execute immediate l_stmt_deletes using l_display_unit_id;

Line: 1147


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

Line: 1150


					msd_dem_common_utilities.log_message(l_stmt_updates);

Line: 1151


					msd_dem_common_utilities.log_debug(l_stmt_updates);

Line: 1153


					execute immediate l_stmt_updates using l_column_name, l_display_unit_id;

Line: 1155


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

Line: 1159


					msd_dem_common_utilities.log_message(l_stmt_updates);

Line: 1160


					msd_dem_common_utilities.log_debug(l_stmt_updates);

Line: 1162


					execute immediate l_stmt_updates using l_column_name, l_column_name;

Line: 1164


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

Line: 1167


					msd_dem_common_utilities.log_message(l_stmt_deletes);

Line: 1168


					msd_dem_common_utilities.log_debug(l_stmt_deletes);

Line: 1170


					execute immediate l_stmt_deletes using l_internal_name;

Line: 1172


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

Line: 1175


					msd_dem_common_utilities.log_message(l_stmt_updates);

Line: 1176


					msd_dem_common_utilities.log_debug(l_stmt_updates);

Line: 1178


					execute immediate l_stmt_updates;

Line: 1182


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

Line: 1183


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