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.RRS_SITE_UTILS SQL Statements

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

Line: 17


select  hl.address1,
	hl.city,
	hl.state,
	nvl(hl.postal_code,' ')
into
	l_address1,
	l_city,
	l_state,
	l_postal_code
from
	hz_locations hl,
	rrs_sites_b rs
where
	rs.location_id = hl.location_id
	and rs.site_id = p_site_id;

Line: 62


select     rs.site_identification_number
into       l_ret
from	   rrs_sites_b rs
where      rs.site_id = p_site_id;

Line: 120


PROCEDURE INSERT_TEMP_FOR_MAP
(
	x_theme_id OUT NOCOPY NUMBER,
	p_session_id IN VARCHAR2,
	p_context_flag IN VARCHAR2,
	p_site_ids IN RRS_NUMBER_TBL_TYPE DEFAULT NULL,
	p_tag_code IN NUMBER DEFAULT NULL,
	p_x_coord IN NUMBER DEFAULT NULL,
	p_y_coord IN NUMBER DEFAULT NULL
)
IS
l_geometry RRS_SITE_TMP.geometry%type;

Line: 136


		select
			SDO_UTIL.CIRCLE_POLYGON(l.GEOMETRY.SDO_POINT.X , l.GEOMETRY.SDO_POINT.Y ,
			DECODE(rtag.unit_of_measure_code,'KILOMETER',rgta.outer_bound*1000,'MILE',rgta.outer_bound*1609.344),   10),
			rgta.trade_area_number_code
		from
			rrs_trade_area_groups_b rtag,
			rrs_group_trade_areas rgta,
			rrs_loc_trade_area_grps rltag,
			hz_locations l,
			rrs_sites_b rs
		where
			rltag.is_primary_flag	= 'Y' and
			rtag.group_id 		= rltag.group_id and
			rgta.group_id 		= rltag.group_id and
			rltag.location_id 	= l.location_id and
			l.location_id 		= rs.location_id and
			rs.site_id in (select site_id from RRS_SITE_TMP where session_id = p_session_id and geometry is null)
		order by
			rgta.trade_area_number_code desc;

Line: 156


		select
			SDO_UTIL.CIRCLE_POLYGON(l.GEOMETRY.SDO_POINT.X , l.GEOMETRY.SDO_POINT.Y ,
			DECODE(rtag.unit_of_measure_code,'KILOMETER',rgta.outer_bound*1000,'MILE',rgta.outer_bound*1609.344),   10),
			rgta.trade_area_number_code
		from
			rrs_trade_area_groups_b rtag,
			rrs_group_trade_areas rgta,
			rrs_loc_trade_area_grps rltag,
			hz_locations l,
			rrs_sites_b rs
		where
			rgta.group_id		= p_tag_code and
			rtag.group_id 		= rltag.group_id and
			rgta.group_id 		= rltag.group_id and
			rltag.location_id 	= l.location_id and
			l.location_id 		= rs.location_id and
			rs.site_id in (select site_id from RRS_SITE_TMP where session_id = p_session_id and geometry is null)
		order by
			rgta.trade_area_number_code desc;

Line: 177


		select
			SDO_UTIL.CIRCLE_POLYGON(l.GEOMETRY.SDO_POINT.X , l.GEOMETRY.SDO_POINT.Y ,
			DECODE(rtag.unit_of_measure_code,'KILOMETER',rgta.outer_bound*1000,'MILE',rgta.outer_bound*1609.344),   10),
			rgta.trade_area_number_code
		from
			rrs_trade_area_groups_b rtag,
			rrs_group_trade_areas rgta,
			rrs_loc_trade_area_grps rltag,
			hz_locations l,
			rrs_sites_b rs,
			rrs_site_tmp tmp
		where
			rltag.is_primary_flag	= 'Y' and
			rtag.group_id 		= rltag.group_id and
			rgta.group_id 		= rltag.group_id and
			rltag.location_id 	= l.location_id and
			l.location_id 		= rs.location_id and
			rs.site_id = tmp.site_id and
			tmp.session_id = p_session_id and
			tmp.geometry is null
		order by
			rgta.trade_area_number_code desc;

Line: 200


		select
			SDO_UTIL.CIRCLE_POLYGON(l.GEOMETRY.SDO_POINT.X , l.GEOMETRY.SDO_POINT.Y ,
			DECODE(rtag.unit_of_measure_code,'KILOMETER',rgta.outer_bound*1000,'MILE',rgta.outer_bound*1609.344),   10),
			rgta.trade_area_number_code
		from
			rrs_trade_area_groups_b rtag,
			rrs_group_trade_areas rgta,
			rrs_loc_trade_area_grps rltag,
			hz_locations l,
			rrs_sites_b rs,
			rrs_site_tmp   tmp
		where
			rgta.group_id = p_tag_code and
			rtag.group_id = rltag.group_id and
			rgta.group_id = rltag.group_id and
			rltag.location_id = l.location_id and
			l.location_id = rs.location_id
			and rs.site_id = tmp.site_id
			and tmp.session_id = p_session_id
			and tmp.geometry is null
		order by rgta.trade_area_number_code desc;

Line: 226


	select nvl(max(to_number(theme_id)),0) +1
	into x_theme_id
	from RRS_SITE_TMP
	where session_id= p_session_id and geometry is null;

Line: 232


		insert into RRS_SITE_TMP
		(session_id,theme_id,site_id)
		values
		(p_session_id,x_theme_id,p_site_ids(i));

Line: 243


			insert into RRS_SITE_TMP
			(session_id,geometry,theme_id)
			values
			(p_session_id,l_geometry,l_trade_area_number_code);

Line: 254


			insert into RRS_SITE_TMP
			(session_id,geometry,theme_id)
			values
			(p_session_id,l_geometry,l_trade_area_number_code);

Line: 265


	select SDO_GEOMETRY(2001,8307,SDO_POINT_TYPE(p_x_coord,p_y_coord,null),null,null)
	into l_geometry
	from dual;

Line: 270


		insert into RRS_SITE_TMP
		(session_id,theme_id,geometry)
		values
		(p_session_id,'-1',l_geometry);

Line: 275


		insert into RRS_SITE_TMP
		(session_id,theme_id,geometry)
		values
		(p_session_id,'0',l_geometry);

Line: 280


		insert into RRS_SITE_TMP
		(session_id,theme_id,geometry)
		values
		(p_session_id,'-2',l_geometry);

Line: 291


END INSERT_TEMP_FOR_MAP;

Line: 296


	p_delete_theme IN VARCHAR2
)
IS
BEGIN
/*
p_delete_theme is a parameter used to define which themes to delete from the temporary table.
It can assume two values:
'ALL' -> Delete All themes for the given SessionId
'ALL_BUT_BASE' -> Delete All themes except the first(Base) theme for the given SessionId.
		  Also delete the themes added for Trade Area Mapping and for the Identify functionality.
'IDENTIFY' -> Delete the point-geometry theme added for the Identify functionality.
'POINT_FEATURE' -> Delete the point-geometry theme added for mapping the Point Feature.
'POINT_FEATURE_AT_LOCATION' -> Delete the point-geometry theme added for mapping the Point Feature at a Location.
*/

IF(p_delete_theme = 'ALL') THEN
    delete from RRS_SITE_TMP where session_id = p_session_id;

Line: 313


ELSIF(p_delete_theme = 'ALL_BUT_BASE') THEN
    delete from RRS_SITE_TMP where session_id = p_session_id
    and ((to_number(theme_id) > 1 and site_id is not null) or (theme_id <> '0' and site_id is null));

Line: 316


ELSIF(p_delete_theme = 'IDENTIFY') THEN
    delete from RRS_SITE_TMP where session_id = p_session_id and theme_id = '-1';

Line: 318


ELSIF(p_delete_theme = 'POINT_FEATURE') THEN
    delete from RRS_SITE_TMP where session_id = p_session_id and theme_id = '0';

Line: 320


ELSIF(p_delete_theme = 'POINT_FEATURE_AT_LOCATION') THEN
    delete from RRS_SITE_TMP where session_id = p_session_id and theme_id = '-2';

Line: 332


SELECT pn.location_type_lookup_code
  FROM pn_locations_all pn
      ,rrs_sites_b site
 WHERE site.property_location_id = pn.location_id
   AND site.site_id = c_site_id ;

Line: 339


SELECT pn.building
  FROM pn_locations_all pn
      ,rrs_sites_b site
 WHERE site.property_location_id = pn.location_id
   AND site.site_id = c_site_id ;

Line: 346


SELECT pn.floor
  FROM pn_locations_all pn
      ,rrs_sites_b site
 WHERE site.property_location_id = pn.location_id
   AND site.site_id = c_site_id ;

Line: 353


SELECT pn.office
  FROM pn_locations_all pn
      ,rrs_sites_b site
 WHERE site.property_location_id = pn.location_id
   AND site.site_id = c_site_id ;

Line: 382


SELECT pn.location_code
  FROM pn_locations_all pn
      ,rrs_sites_b site
 WHERE site.property_location_id = pn.location_id
   AND site.site_id = c_site_id ;

Line: 403


SELECT prop.property_name
  FROM pn_locations_all pn
      ,pn_properties_all prop
 WHERE pn.location_id = c_location_id
   AND prop.property_id = pn.property_id ;

Line: 425


SELECT
	UNIT_OF_MEASURE
FROM
	MTL_UNITS_OF_MEASURE
WHERE
	LANGUAGE = USERENV('LANG')
	and (BASE_UOM_FLAG = 'Y' or
	     exists (select 1 from  MTL_UOM_CONVERSIONS
		     where MTL_UNITS_OF_MEASURE .UOM_CLASS =  MTL_UOM_CONVERSIONS.UOM_CLASS
		     and MTL_UNITS_OF_MEASURE .UNIT_OF_MEASURE =  MTL_UOM_CONVERSIONS.UNIT_OF_MEASURE) )
	and (DISABLE_DATE is null or DISABLE_DATE >= sysdate)
	and UOM_CLASS = c_uom_class
ORDER BY
	BASE_UOM_FLAG DESC;

Line: 459


FUNCTION Update_geometry_for_locations
 (p_loc_id IN NUMBER,
  p_lat IN NUMBER,
  p_long IN NUMBER,
  p_status IN VARCHAR2
 ) RETURN NUMBER
 IS
 l_is_invalid_geocode NUMBER := 0; /* Bug 7143445 */

Line: 475


   UPDATE hz_locations
   SET
   geometry = decode(l_is_invalid_geocode, 1, NULL,
                     MDSYS.SDO_GEOMETRY(2001,
                                        8307,
                                        SDO_POINT_TYPE(p_long, p_lat, NULL),
                                        NULL,
                                        NULL)),
   geometry_status_code = p_status,
   last_update_date = sysdate,
   last_updated_by = fnd_global.user_id,
   last_update_login = fnd_global.login_id,
   request_id = fnd_global.conc_request_id,
   program_application_id = fnd_global.prog_appl_id,
   program_id = fnd_global.conc_program_id,
   program_update_date = sysdate
   WHERE
   location_id = p_loc_id;

Line: 500


 END Update_geometry_for_locations;

Line: 528


  select  replace(hl.address1,' ','%20'),
          replace(hl.city,' ','%20'),
          replace(hl.state,' ','%20'),
          replace(hl.postal_code,' ','%20'),
          replace(hl.country,' ','%20')
  into    l_address1,
          l_city,
          l_state,
          l_postal_code,
          l_country
  from    hz_locations hl
  where   hl.location_id = p_loc_id;