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

PROCEDURE Update_geometry_for_locations
 (p_loc_id IN NUMBER,
  p_lat IN NUMBER,
  p_long IN NUMBER,
  p_status IN VARCHAR2,
  p_geo_source IN VARCHAR2 DEFAULT 'RRS_GOOGLE',
  x_return_status OUT NOCOPY VARCHAR2,
  x_msg_count OUT NOCOPY NUMBER,
  x_msg_data  OUT NOCOPY VARCHAR2
 )
 IS
 l_is_invalid_geocode NUMBER := 0; /* Bug 7143445 */

Line: 483

   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,
   geometry_source = p_geo_source,
   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: 517

 END Update_geometry_for_locations;

Line: 541

   select
   ROUND(HL.geometry.SDO_POINT.X,8) longitude,
   ROUND(HL.geometry.SDO_POINT.Y,8) latitude,
   HL.GEOMETRY_SOURCE
   into
   x_latitude,
   x_longitude,
   x_geo_source
   from hz_locations HL
   where location_id = p_loc_id;

Line: 596

   select geometry_source
   into l_curr_geosource
   from hz_locations
   where location_id = p_loc_id;

Line: 610

      UPDATE hz_locations
      SET geometry_source = l_default_geosource
      WHERE location_id = p_loc_id
      AND geometry_source is null;

Line: 663

  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;

Line: 723

	l_cursor_sql := 'SELECT ' || p_site_name_col_name || ' AS SITE_NAME, rowid' ||
 		' FROM EGO_BULKLOAD_INTF WHERE RESULTFMT_USAGE_ID =' || TO_CHAR(p_result_format_usage_id) ||
 		' AND ' || p_site_number_col_name || ' IS NULL';

Line: 731

		SELECT 	RRS_DEFAULT_SITE_NUMBER_S.NEXTVAL
		INTO	l_default_seq_num
		FROM 	DUAL;

Line: 742

		select count(*)
		INTO l_mr_ag_count
		FROM   EGO_FND_DSC_FLX_CTX_EXT
		WHERE  attr_group_id in (
								SELECT distinct(  SUBSTR(fmtusg.attribute_code, 1,INSTR(fmtusg.attribute_code, '$$') - 1) )attr_group_id
								FROM   ego_results_fmt_usages fmtusg
								WHERE  fmtusg.resultfmt_usage_id = p_result_format_usage_id
								AND   fmtusg.attribute_code LIKE '%$$%'
								)
		AND MULTI_ROW='Y';

Line: 756

			'UPDATE EGO_BULKLOAD_INTF SET ' || p_site_number_col_name || ' = :1' ||
			' WHERE ' || p_site_name_col_name || ' = :2' ||
			' AND RESULTFMT_USAGE_ID = ' || TO_CHAR(p_result_format_usage_id) ||
			' AND ' || p_site_number_col_name || ' IS NULL'
			USING TO_CHAR(l_default_seq_num), l_site_name;

Line: 763

			'UPDATE EGO_BULKLOAD_INTF SET ' || p_site_number_col_name || ' = :1' ||
			' WHERE ' || p_site_name_col_name || ' = :2' ||
			' AND RESULTFMT_USAGE_ID = ' || TO_CHAR(p_result_format_usage_id) ||
			' AND ' || p_site_number_col_name || ' IS NULL'||
			' AND rowid'||'=:3'
			USING TO_CHAR(l_default_seq_num), l_site_name,l_row_id;

Line: 805

    FND_MSG_PUB.Delete_Msg(null);

Line: 852

            select 1 into l_temp from rrs_user_favorites
            where object_type = v_object_type(obj_index) and
            object_id = v_object_id(obj_index) and
            user_id = P_USER_ID;

Line: 857

            v_object_type.delete(obj_index);

Line: 858

            v_object_id.delete(obj_index);

Line: 873

           insert into rrs_user_favorites
           (USER_FAVORITE_ID,
            OBJECT_TYPE,
            OBJECT_ID,
            USER_ID,
            LAST_UPDATE_DATE,
            LAST_UPDATED_BY,
            CREATION_DATE,
            CREATED_BY,
            LAST_UPDATE_LOGIN)
           values
           (RRS_FAVORITE_S.nextval,
            v_object_type(ins_index),
            v_object_id(ins_index),
            P_USER_ID,
            sysdate,
            fnd_global.user_id,
            sysdate,
            fnd_global.user_id,
            fnd_global.login_id);

Line: 922

	SELECT ATTR_GROUP_ID
	  INTO l_attr_group_id
	  FROM EGO_OBJ_ATTR_GRP_ASSOCS_V
	 WHERE APPLICATION_ID = p_application_id
	   AND CLASSIFICATION_CODE = p_classification_code
	   AND ATTR_GROUP_TYPE = p_attr_group_type
	   AND ATTR_GROUP_NAME = p_attr_group_name;

Line: 932

		SELECT COUNT(*)
		  INTO l_num_of_rows
		  FROM RRS_SITES_EXT_VL
		 WHERE ATTR_GROUP_ID = l_attr_group_id
		   AND SITE_USE_TYPE_CODE = p_classification_code;

Line: 940

		SELECT COUNT(*)
		  INTO l_num_of_rows
		  FROM RRS_LOCATIONS_EXT_VL
		 WHERE ATTR_GROUP_ID = l_attr_group_id
		   AND COUNTRY = p_classification_code;

Line: 948

		SELECT COUNT(*)
		  INTO l_num_of_rows
		  FROM RRS_TRADE_AREAS_EXT_VL
		 WHERE ATTR_GROUP_ID = l_attr_group_id
		   AND GROUP_ID = p_classification_code;

Line: 956

		SELECT COUNT(*)
		  INTO l_num_of_rows
		  FROM RRS_HIERARCHIES_EXT_VL
		 WHERE ATTR_GROUP_ID = l_attr_group_id
		   AND HIERARCHY_PURPOSE_CODE = p_classification_code;

Line: 978

	select count(*)
		INTO l_mr_ag_count
		FROM   EGO_FND_DSC_FLX_CTX_EXT
		WHERE  attr_group_id in (
								SELECT distinct(  SUBSTR(fmtusg.attribute_code, 1,INSTR(fmtusg.attribute_code, '$$') - 1) )attr_group_id
								FROM   ego_results_fmt_usages fmtusg
								WHERE  fmtusg.resultfmt_usage_id = p_result_format_usage_id
								AND   fmtusg.attribute_code LIKE '%$$%'
								)
		AND MULTI_ROW='Y';