[Home] [Help]
PACKAGE BODY: APPS.RRS_SITE_UTILS
Source
1 PACKAGE BODY RRS_SITE_UTILS AS
2 /* $Header: RRSUTILB.pls 120.15 2008/06/02 22:58:03 nlal noship $ */
3
4 FUNCTION GET_LOCATION_ADDRESS
5 (
6 p_site_id IN NUMBER
7 ) RETURN VARCHAR2
8 IS
9 l_address1 hz_locations.address1%type;
10 l_city hz_locations.city%type;
11 l_state hz_locations.city%type;
12 l_postal_code hz_locations.postal_code%type;
13 l_ret varchar2(2000);
14
15 BEGIN
16
17 select hl.address1,
18 hl.city,
19 hl.state,
20 nvl(hl.postal_code,' ')
21 into
22 l_address1,
23 l_city,
24 l_state,
25 l_postal_code
26 from
27 hz_locations hl,
28 rrs_sites_b rs
29 where
30 rs.location_id = hl.location_id
31 and rs.site_id = p_site_id;
32
33 IF(l_address1 is not null) THEN
34 l_ret := l_address1;
35 END IF;
36 IF(l_ret is not null) THEN
37 IF(l_city is not null) THEN
38 l_ret := l_ret || ', '||l_city;
39 END IF;
40 ELSE
41 l_ret := l_city;
42 END IF;
43
44 IF(l_ret is not null) THEN
45 IF(l_state is not null) THEN
46 l_ret := l_ret || ', '||l_state ||' '||l_postal_code;
47 END IF;
48 ELSE
49 l_ret := l_state;
50 END IF;
51 RETURN l_ret;
52 END GET_LOCATION_ADDRESS;
53
54 FUNCTION GET_SITE_DISPLAY_NAME
55 (
56 p_site_id IN NUMBER
57 ) RETURN VARCHAR2
58 IS
59 l_ret varchar2(2000);
60
61 BEGIN
62 select rs.site_identification_number
63 into l_ret
64 from rrs_sites_b rs
65 where rs.site_id = p_site_id;
66
67 RETURN l_ret;
68 END GET_SITE_DISPLAY_NAME;
69
70 FUNCTION GET_USER_ATTR_VAL
71 (
72 p_attr_grp_type IN VARCHAR2,
73 p_attr_grp_name IN VARCHAR2,
74 p_attr_name IN VARCHAR2,
75 p_object_name IN VARCHAR2,
76 p_pk_col_val IN VARCHAR2
77 )RETURN VARCHAR2
78 IS
79 l_user_attr_val VARCHAR2(1000);
80
81 BEGIN
82 IF(p_object_name = 'RRS_SITE') THEN
83 l_user_attr_val := EGO_USER_ATTRS_DATA_PVT.Get_User_Attr_Val
84 (
85 p_appl_id => 718
86 ,p_attr_grp_type => p_attr_grp_type
87 ,p_attr_grp_name => p_attr_grp_name
88 ,p_attr_name => p_attr_name
89 ,p_object_name => p_object_name
90 ,p_pk_col1 => 'SITE_ID'
91 ,p_pk_value1 => p_pk_col_val
92 );
93 ELSIF(p_object_name = 'RRS_LOCATION') THEN
94 l_user_attr_val := EGO_USER_ATTRS_DATA_PVT.Get_User_Attr_Val
95 (
96 p_appl_id => 718
97 ,p_attr_grp_type => p_attr_grp_type
98 ,p_attr_grp_name => p_attr_grp_name
99 ,p_attr_name => p_attr_name
100 ,p_object_name => p_object_name
101 ,p_pk_col1 => 'LOCATION_ID'
102 ,p_pk_value1 => p_pk_col_val
103 );
104 ELSIF(p_object_name = 'RRS_TRADE_AREA') THEN
105 l_user_attr_val := EGO_USER_ATTRS_DATA_PVT.Get_User_Attr_Val
106 (
107 p_appl_id => 718
108 ,p_attr_grp_type => p_attr_grp_type
109 ,p_attr_grp_name => p_attr_grp_name
110 ,p_attr_name => p_attr_name
111 ,p_object_name => p_object_name
112 ,p_pk_col1 => 'TRADE_AREA_ID'
113 ,p_pk_value1 => p_pk_col_val
114 );
115 END IF;
116
117 RETURN l_user_attr_val;
118 END Get_User_Attr_Val;
119
120 PROCEDURE INSERT_TEMP_FOR_MAP
121 (
122 x_theme_id OUT NOCOPY NUMBER,
123 p_session_id IN VARCHAR2,
124 p_context_flag IN VARCHAR2,
125 p_site_ids IN RRS_NUMBER_TBL_TYPE DEFAULT NULL,
126 p_tag_code IN NUMBER DEFAULT NULL,
127 p_x_coord IN NUMBER DEFAULT NULL,
128 p_y_coord IN NUMBER DEFAULT NULL
129 )
130 IS
131 l_geometry RRS_SITE_TMP.geometry%type;
132 l_trade_area_number_code rrs_group_trade_areas.trade_area_number_code%type;
133 --Bug 4903895 - Start Code
134 /*
135 CURSOR primary_tag_cursor IS
136 select
137 SDO_UTIL.CIRCLE_POLYGON(l.GEOMETRY.SDO_POINT.X , l.GEOMETRY.SDO_POINT.Y ,
138 DECODE(rtag.unit_of_measure_code,'KILOMETER',rgta.outer_bound*1000,'MILE',rgta.outer_bound*1609.344), 10),
139 rgta.trade_area_number_code
140 from
141 rrs_trade_area_groups_b rtag,
142 rrs_group_trade_areas rgta,
143 rrs_loc_trade_area_grps rltag,
144 hz_locations l,
145 rrs_sites_b rs
146 where
147 rltag.is_primary_flag = 'Y' and
148 rtag.group_id = rltag.group_id and
149 rgta.group_id = rltag.group_id and
150 rltag.location_id = l.location_id and
151 l.location_id = rs.location_id and
152 rs.site_id in (select site_id from RRS_SITE_TMP where session_id = p_session_id and geometry is null)
153 order by
154 rgta.trade_area_number_code desc;
155 CURSOR other_tag_cursor IS
156 select
157 SDO_UTIL.CIRCLE_POLYGON(l.GEOMETRY.SDO_POINT.X , l.GEOMETRY.SDO_POINT.Y ,
158 DECODE(rtag.unit_of_measure_code,'KILOMETER',rgta.outer_bound*1000,'MILE',rgta.outer_bound*1609.344), 10),
159 rgta.trade_area_number_code
160 from
161 rrs_trade_area_groups_b rtag,
162 rrs_group_trade_areas rgta,
163 rrs_loc_trade_area_grps rltag,
164 hz_locations l,
165 rrs_sites_b rs
166 where
167 rgta.group_id = p_tag_code and
168 rtag.group_id = rltag.group_id and
169 rgta.group_id = rltag.group_id and
170 rltag.location_id = l.location_id and
171 l.location_id = rs.location_id and
172 rs.site_id in (select site_id from RRS_SITE_TMP where session_id = p_session_id and geometry is null)
173 order by
174 rgta.trade_area_number_code desc;
175 */
176 CURSOR primary_tag_cursor IS
177 select
178 SDO_UTIL.CIRCLE_POLYGON(l.GEOMETRY.SDO_POINT.X , l.GEOMETRY.SDO_POINT.Y ,
179 DECODE(rtag.unit_of_measure_code,'KILOMETER',rgta.outer_bound*1000,'MILE',rgta.outer_bound*1609.344), 10),
180 rgta.trade_area_number_code
181 from
182 rrs_trade_area_groups_b rtag,
183 rrs_group_trade_areas rgta,
184 rrs_loc_trade_area_grps rltag,
185 hz_locations l,
186 rrs_sites_b rs,
187 rrs_site_tmp tmp
188 where
189 rltag.is_primary_flag = 'Y' and
190 rtag.group_id = rltag.group_id and
191 rgta.group_id = rltag.group_id and
192 rltag.location_id = l.location_id and
193 l.location_id = rs.location_id and
194 rs.site_id = tmp.site_id and
195 tmp.session_id = p_session_id and
196 tmp.geometry is null
197 order by
198 rgta.trade_area_number_code desc;
199 CURSOR other_tag_cursor IS
200 select
201 SDO_UTIL.CIRCLE_POLYGON(l.GEOMETRY.SDO_POINT.X , l.GEOMETRY.SDO_POINT.Y ,
202 DECODE(rtag.unit_of_measure_code,'KILOMETER',rgta.outer_bound*1000,'MILE',rgta.outer_bound*1609.344), 10),
203 rgta.trade_area_number_code
204 from
205 rrs_trade_area_groups_b rtag,
206 rrs_group_trade_areas rgta,
207 rrs_loc_trade_area_grps rltag,
208 hz_locations l,
209 rrs_sites_b rs,
210 rrs_site_tmp tmp
211 where
212 rgta.group_id = p_tag_code and
213 rtag.group_id = rltag.group_id and
214 rgta.group_id = rltag.group_id and
215 rltag.location_id = l.location_id and
216 l.location_id = rs.location_id
217 and rs.site_id = tmp.site_id
218 and tmp.session_id = p_session_id
219 and tmp.geometry is null
220 order by rgta.trade_area_number_code desc;
221 --Bug 4903895 - End Code
222
223 BEGIN
224 IF(p_context_flag = 'SITE') THEN
225
226 select nvl(max(to_number(theme_id)),0) +1
227 into x_theme_id
228 from RRS_SITE_TMP
229 where session_id= p_session_id and geometry is null;
230
231 forall i in 1..p_site_ids.count
232 insert into RRS_SITE_TMP
233 (session_id,theme_id,site_id)
234 values
235 (p_session_id,x_theme_id,p_site_ids(i));
236
237 ELSIF(p_context_flag = 'TRADE_AREA') THEN
238 IF(p_tag_code = 0) THEN
239 OPEN primary_tag_cursor;
240 LOOP
241 FETCH primary_tag_cursor INTO l_geometry, l_trade_area_number_code;
242 EXIT WHEN primary_tag_cursor%NOTFOUND;
243 insert into RRS_SITE_TMP
244 (session_id,geometry,theme_id)
245 values
246 (p_session_id,l_geometry,l_trade_area_number_code);
247 END LOOP;
248 CLOSE primary_tag_cursor;
249 ELSIF(p_tag_code > 0) THEN
250 OPEN other_tag_cursor;
251 LOOP
252 FETCH other_tag_cursor INTO l_geometry, l_trade_area_number_code;
253 EXIT WHEN other_tag_cursor%NOTFOUND;
254 insert into RRS_SITE_TMP
255 (session_id,geometry,theme_id)
256 values
257 (p_session_id,l_geometry,l_trade_area_number_code);
258 END LOOP;
259 CLOSE other_tag_cursor;
260 END IF;
261 x_theme_id := 0;
262
263 ELSIF(p_context_flag = 'IDENTIFY' OR p_context_flag = 'POINT_FEATURE' OR p_context_flag = 'POINT_FEATURE_AT_LOCATION') THEN
264
265 select SDO_GEOMETRY(2001,8307,SDO_POINT_TYPE(p_x_coord,p_y_coord,null),null,null)
266 into l_geometry
267 from dual;
268
269 IF(p_context_flag = 'IDENTIFY') THEN
270 insert into RRS_SITE_TMP
271 (session_id,theme_id,geometry)
272 values
273 (p_session_id,'-1',l_geometry);
274 ELSIF(p_context_flag = 'POINT_FEATURE') THEN
275 insert into RRS_SITE_TMP
276 (session_id,theme_id,geometry)
277 values
278 (p_session_id,'0',l_geometry);
279 ELSIF(p_context_flag = 'POINT_FEATURE_AT_LOCATION') THEN
280 insert into RRS_SITE_TMP
281 (session_id,theme_id,geometry)
282 values
283 (p_session_id,'-2',l_geometry);
284 END IF;
285 x_theme_id := 0;
286
287 END IF;
288 EXCEPTION when others then
289 x_theme_id := -1;
290
291 END INSERT_TEMP_FOR_MAP;
292
293 PROCEDURE CLEAR_TEMP_FOR_MAP
294 (
295 p_session_id IN VARCHAR2,
296 p_delete_theme IN VARCHAR2
297 )
298 IS
299 BEGIN
300 /*
301 p_delete_theme is a parameter used to define which themes to delete from the temporary table.
302 It can assume two values:
303 'ALL' -> Delete All themes for the given SessionId
304 'ALL_BUT_BASE' -> Delete All themes except the first(Base) theme for the given SessionId.
305 Also delete the themes added for Trade Area Mapping and for the Identify functionality.
306 'IDENTIFY' -> Delete the point-geometry theme added for the Identify functionality.
307 'POINT_FEATURE' -> Delete the point-geometry theme added for mapping the Point Feature.
308 'POINT_FEATURE_AT_LOCATION' -> Delete the point-geometry theme added for mapping the Point Feature at a Location.
309 */
310
311 IF(p_delete_theme = 'ALL') THEN
312 delete from RRS_SITE_TMP where session_id = p_session_id;
313 ELSIF(p_delete_theme = 'ALL_BUT_BASE') THEN
314 delete from RRS_SITE_TMP where session_id = p_session_id
315 and ((to_number(theme_id) > 1 and site_id is not null) or (theme_id <> '0' and site_id is null));
316 ELSIF(p_delete_theme = 'IDENTIFY') THEN
317 delete from RRS_SITE_TMP where session_id = p_session_id and theme_id = '-1';
318 ELSIF(p_delete_theme = 'POINT_FEATURE') THEN
319 delete from RRS_SITE_TMP where session_id = p_session_id and theme_id = '0';
320 ELSIF(p_delete_theme = 'POINT_FEATURE_AT_LOCATION') THEN
321 delete from RRS_SITE_TMP where session_id = p_session_id and theme_id = '-2';
322 END IF;
323
324 END CLEAR_TEMP_FOR_MAP;
325
326 FUNCTION GET_LOCATION_NAME
327 (
328 p_site_id IN NUMBER
329 ) RETURN VARCHAR2
330 IS
331 /*CURSOR c_location_type_code(c_site_id NUMBER) IS
332 SELECT pn.location_type_lookup_code
333 FROM pn_locations_all pn
334 ,rrs_sites_b site
335 WHERE site.property_location_id = pn.location_id
336 AND site.site_id = c_site_id ;
337
338 CURSOR c_building(c_site_id NUMBER) IS
339 SELECT pn.building
340 FROM pn_locations_all pn
341 ,rrs_sites_b site
342 WHERE site.property_location_id = pn.location_id
343 AND site.site_id = c_site_id ;
344
345 CURSOR c_floor(c_site_id NUMBER) IS
346 SELECT pn.floor
347 FROM pn_locations_all pn
348 ,rrs_sites_b site
349 WHERE site.property_location_id = pn.location_id
350 AND site.site_id = c_site_id ;
351
352 CURSOR c_office(c_site_id NUMBER) IS
353 SELECT pn.office
354 FROM pn_locations_all pn
355 ,rrs_sites_b site
356 WHERE site.property_location_id = pn.location_id
357 AND site.site_id = c_site_id ;
358
359 l_location_type_lookup_code pn_locations_all.location_type_lookup_code%TYPE ;
360 l_location_name VARCHAR2(30);
361
362 BEGIN
363 OPEN c_location_type_code(p_site_id);
364 FETCH c_location_type_code INTO l_location_type_lookup_code ;
365 CLOSE c_location_type_code ;
366
367 IF l_location_type_lookup_code IN ('BUILDING','LAND') THEN
368 OPEN c_building(p_site_id) ;
369 FETCH c_building INTO l_location_name ;
370 CLOSE c_building ;
371 ELSIF l_location_type_lookup_code IN ('FLOOR','PARCEL') THEN
372 OPEN c_floor(p_site_id) ;
373 FETCH c_floor INTO l_location_name ;
374 CLOSE c_floor ;
375 ELSIF l_location_type_lookup_code IN ('OFFICE','SECTION') THEN
376 OPEN c_office(p_site_id) ;
377 FETCH c_office INTO l_location_name ;
378 CLOSE c_office ;
379 END IF ;
380 RETURN l_location_name ;*/
381 CURSOR c_location_code(c_site_id NUMBER) IS
382 SELECT pn.location_code
383 FROM pn_locations_all pn
384 ,rrs_sites_b site
385 WHERE site.property_location_id = pn.location_id
386 AND site.site_id = c_site_id ;
387
388 l_location_code pn_locations_all.location_code%TYPE ;
389
390 BEGIN
391 OPEN c_location_code(p_site_id);
392 FETCH c_location_code INTO l_location_code ;
393 CLOSE c_location_code ;
394 RETURN l_location_code ;
395 END GET_LOCATION_NAME;
396
397 FUNCTION GET_PROPERTY_NAME
398 (
399 p_location_id IN NUMBER
400 ) RETURN VARCHAR2
401 IS
402 CURSOR c_property_name(c_location_id NUMBER) IS
403 SELECT prop.property_name
404 FROM pn_locations_all pn
405 ,pn_properties_all prop
406 WHERE pn.location_id = c_location_id
407 AND prop.property_id = pn.property_id ;
408
409 l_property_name pn_properties_all.property_name%type ;
410
411 BEGIN
412 OPEN c_property_name(p_location_id);
413 FETCH c_property_name INTO l_property_name ;
414 CLOSE c_property_name ;
415 RETURN l_property_name ;
416 END GET_PROPERTY_NAME;
417
418 FUNCTION GET_UOM_COLUMN_PROMPT
419 (
420 p_uom_class IN VARCHAR2
421 )RETURN VARCHAR2
422 IS
423
424 CURSOR UOM_CURSOR(c_uom_class VARCHAR2) IS
425 SELECT
426 UNIT_OF_MEASURE
427 FROM
428 MTL_UNITS_OF_MEASURE
429 WHERE
430 LANGUAGE = USERENV('LANG')
431 and (BASE_UOM_FLAG = 'Y' or
432 exists (select 1 from MTL_UOM_CONVERSIONS
433 where MTL_UNITS_OF_MEASURE .UOM_CLASS = MTL_UOM_CONVERSIONS.UOM_CLASS
434 and MTL_UNITS_OF_MEASURE .UNIT_OF_MEASURE = MTL_UOM_CONVERSIONS.UNIT_OF_MEASURE) )
435 and (DISABLE_DATE is null or DISABLE_DATE >= sysdate)
436 and UOM_CLASS = c_uom_class
437 ORDER BY
438 BASE_UOM_FLAG DESC;
439 uom MTL_UNITS_OF_MEASURE.UNIT_OF_MEASURE%TYPE;
440
441 BEGIN
442
443 IF(p_uom_class IS NULL) THEN
444 RETURN '';
445 ELSE
446 OPEN UOM_CURSOR(p_uom_class);
447 FETCH UOM_CURSOR INTO uom;
448 CLOSE UOM_CURSOR;
449 END IF;
450
451 IF(uom IS NULL) THEN
452 RETURN '';
453 ELSE
454 RETURN ' (' || uom || ')';
455 END IF;
456
457 END GET_UOM_COLUMN_PROMPT;
458
459 FUNCTION Update_geometry_for_locations
460 (p_loc_id IN NUMBER,
461 p_lat IN NUMBER,
462 p_long IN NUMBER,
463 p_status IN VARCHAR2
464 ) RETURN NUMBER
465 IS
466 l_is_invalid_geocode NUMBER := 0; /* Bug 7143445 */
467 BEGIN
468
469 /* Bug 7143445 check if lat, long = 0,0 */
470 IF (p_lat IS NOT NULL AND p_lat=0 AND p_long IS NOT NULL AND p_long=0) THEN
471 l_is_invalid_geocode := 1;
472 END IF;
473
474 /* Bug 7143445 Do not stamp 0,0 for geometry */
475 UPDATE hz_locations
476 SET
477 geometry = decode(l_is_invalid_geocode, 1, NULL,
478 MDSYS.SDO_GEOMETRY(2001,
479 8307,
480 SDO_POINT_TYPE(p_long, p_lat, NULL),
481 NULL,
482 NULL)),
483 geometry_status_code = p_status,
484 last_update_date = sysdate,
485 last_updated_by = fnd_global.user_id,
486 last_update_login = fnd_global.login_id,
487 request_id = fnd_global.conc_request_id,
488 program_application_id = fnd_global.prog_appl_id,
489 program_id = fnd_global.conc_program_id,
490 program_update_date = sysdate
491 WHERE
492 location_id = p_loc_id;
493
494 return sql%rowcount;
495
496 EXCEPTION
497 when others then
498 RETURN 0;
499
500 END Update_geometry_for_locations;
501
502 FUNCTION get_ordinate
503 (geom IN MDSYS.SDO_GEOMETRY,
504 indx IN NUMBER
505 ) RETURN NUMBER
506 IS
507 BEGIN
508 if ( geom.sdo_ordinates is null ) then return null;
509 elsif ( geom.sdo_ordinates.count < indx) then return null;
510 else return geom.sdo_ordinates (indx);
511 end if;
512 END;
513
514 FUNCTION get_address
515 (p_loc_id IN NUMBER
516 ) RETURN VARCHAR2
517 IS
518
519 l_address1 hz_locations.address1%type;
520 l_city hz_locations.city%type;
521 l_state hz_locations.city%type;
522 l_postal_code hz_locations.postal_code%type;
523 l_country hz_locations.country%type;
524 l_ret varchar2(500) := null;
525
526 BEGIN
527
528 select replace(hl.address1,' ','%20'),
529 replace(hl.city,' ','%20'),
530 replace(hl.state,' ','%20'),
531 replace(hl.postal_code,' ','%20'),
532 replace(hl.country,' ','%20')
533 into l_address1,
534 l_city,
535 l_state,
536 l_postal_code,
537 l_country
538 from hz_locations hl
539 where hl.location_id = p_loc_id;
540
541 IF(l_address1 is not null) THEN
542 l_ret := l_address1;
543 END IF;
544
545 IF(l_city is not null) THEN
546 l_ret := l_ret||'+'||l_city;
547 END IF;
548
549 IF(l_state is not null) THEN
550 l_ret := l_ret||'+'||l_state;
551 END IF;
552
553 IF(l_postal_code is not null) THEN
554 l_ret := l_ret||'+'||l_postal_code;
555 END IF;
556
557 IF(l_country is not null) THEN
558 l_ret := l_ret||'+'||l_country;
559 END IF;
560
561 RETURN l_ret;
562
563 EXCEPTION
564 when others then
565 RETURN l_ret;
566
567 END get_address;
568
569 END RRS_SITE_UTILS;
570