APPS.MSD_DEM_COLLECT_UOMS SQL Statements

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

Line: 11

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

Line: 18

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

Line: 56

SELECT t.conversion_rate std_to_rate,
  t.to_uom_class std_to_class,
  f.conversion_rate std_from_rate,
  f.from_uom_class std_from_class
FROM msd_dem_uom_conversions_gtt t,
  msd_dem_uom_conversions_gtt f
WHERE t.sr_item_pk in (item_id, 0)
 AND t.to_uom_code = to_unit
 AND t.to_uom_class = t.from_uom_class
 AND f.sr_item_pk in (item_id, 0)
 AND f.to_uom_code = from_unit
 AND f.to_uom_class = f.from_uom_class
ORDER BY t.sr_item_pk DESC,
  f.sr_item_pk DESC;

Line: 76

select decode(from_uom_class, p_from_class, 1, 2) from_flag,
       decode(to_uom_class, p_to_class, 1, 2) to_flag,
       conversion_rate rate
from   msd_dem_uom_conversions_gtt
where  sr_item_pk = item_id and
       ( (from_uom_class = p_from_class and to_uom_class = p_to_class) or
         (from_uom_class = p_to_class   and to_uom_class = p_from_class) );

Line: 238

select table_name, column_name
from msd_dem_entities_inuse
where ebs_entity = 'UOM'
and demantra_entity = 'DISPLAY_UNIT'
and internal_name = p_uom_code;

Line: 256

			msd_dem_common_utilities.log_message('UOM deleted. Please recreate the UOM');

Line: 257

			msd_dem_common_utilities.log_debug('UOM deleted. Please recreate the UOM');

Line: 265

			l_stmt := 'update ' || fnd_profile.value('MSD_DEM_SCHEMA') || '.' || l_table_name || ' tei ';

Line: 267

			l_stmt := 'update ' || l_table_name || ' tei ';

Line: 271

								' select msd_dem_collect_uoms.msd_dem_uom_conversion(msc.uom_code, ''' || p_uom_code || ''', msc.sr_inventory_item_id)' ||
								' from  msc_system_items msc' ||
								' where plan_id = -1 ' ||
								' and sr_instance_id = :1 ' ||
								' and tei.ebs_item_dest_key = msc.inventory_item_id ' ||
								' and rownum < 2 ' ||
								')';

Line: 301

select 1 from
msd_dem_entities_inuse
where internal_name = p_uom_code
and ebs_entity = 'UOM';

Line: 343

				l_stmt_new_uom_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 ''%EBSUOM%'' and rownum < 2';

Line: 365

				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
                   (
                   ''UOM''
                   ,''DISPLAY_UNIT''
                   ,:1
                   ,:2
                   ,:3
                   ,:4
                   ,:5
                   ,:6
                   ,:7
                   ,:8
                   )';

Line: 394

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

Line: 402

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

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

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

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

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

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

Line: 488

		l_stmt := 'select distinct from_uom_code uom_code
							 from msd_dem_uom_conversions_gtt
							 union
							 select distinct to_uom_code uom_code
							 from msd_dem_uom_conversions_gtt';

Line: 512

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