[Home] [Help]
PACKAGE BODY: APPS.RRS_SITE_UTILS
Source
1 PACKAGE BODY RRS_SITE_UTILS AS
2 /* $Header: RRSUTILB.pls 120.30 2010/09/20 06:52:42 nnrao ship $ */
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
460
461 PROCEDURE Update_geometry_for_locations
462 (p_loc_id IN NUMBER,
463 p_lat IN NUMBER,
464 p_long IN NUMBER,
465 p_status IN VARCHAR2,
466 p_geo_source IN VARCHAR2 DEFAULT 'RRS_GOOGLE',
467 x_return_status OUT NOCOPY VARCHAR2,
468 x_msg_count OUT NOCOPY NUMBER,
469 x_msg_data OUT NOCOPY VARCHAR2
470 )
471 IS
472 l_is_invalid_geocode NUMBER := 0; /* Bug 7143445 */
473 BEGIN
474 x_return_status := 'S';
475 x_msg_data := null;
476 /* Bug 7143445 check if lat, long = 0,0 */
477 IF ((p_lat IS NOT NULL AND p_lat=0 AND p_long IS NOT NULL AND p_long=0 AND p_geo_source = 'RRS_GOOGLE') OR
478 (p_lat IS NULL OR p_long IS NULL)) THEN
479 l_is_invalid_geocode := 1;
480 END IF;
481
482 /* Bug 7143445 Do not stamp 0,0 for geometry */
483 UPDATE hz_locations
484 SET
485 geometry = decode(l_is_invalid_geocode, 1, NULL,
486 MDSYS.SDO_GEOMETRY(2001,
487 8307,
488 SDO_POINT_TYPE(p_long, p_lat, NULL),
489 NULL,
490 NULL)),
491 geometry_status_code = p_status,
492 geometry_source = p_geo_source,
493 last_update_date = sysdate,
494 last_updated_by = fnd_global.user_id,
495 last_update_login = fnd_global.login_id,
496 request_id = fnd_global.conc_request_id,
497 program_application_id = fnd_global.prog_appl_id,
498 program_id = fnd_global.conc_program_id,
499 program_update_date = sysdate
500 WHERE
501 location_id = p_loc_id;
502
503 if sql%rowcount = 0 THEN
504 x_return_status := 'E';
505 x_msg_count := 1;
506 FND_MESSAGE.SET_NAME('RRS','RRS_UPD_LOC_FAILED');
507 FND_MESSAGE.SET_TOKEN('LOCATION_ID',p_loc_id);
508 x_msg_data := FND_MESSAGE.GET;
509 END IF;
510
511 EXCEPTION
512 when others then
513 x_return_status := 'E';
514 x_msg_count := 1;
515 x_msg_data := sqlerrm;
516
517 END Update_geometry_for_locations;
518
519 PROCEDURE get_geometry_for_location
520 (p_loc_id IN NUMBER,
521 x_geo_source OUT NOCOPY VARCHAR2,
522 x_null_flag OUT NOCOPY VARCHAR2,
523 x_latitude OUT NOCOPY NUMBER,
524 x_longitude OUT NOCOPY NUMBER,
525 x_return_status OUT NOCOPY VARCHAR2,
526 x_msg_count OUT NOCOPY NUMBER,
527 x_msg_data OUT NOCOPY VARCHAR2
528 )
529 IS
530 l_is_invalid_geocode NUMBER := 0; /* Bug 7143445 */
531 l_default_geosource VARCHAR2(30);
532 l_loc_found VARCHAR2(1);
533 BEGIN
534 x_return_status := 'S';
535 x_null_flag := 'F';
536 x_msg_data := null;
537 x_geo_source := null;
538 x_latitude := null;
539 x_longitude := null;
540
541 select
542 ROUND(HL.geometry.SDO_POINT.X,8) longitude,
543 ROUND(HL.geometry.SDO_POINT.Y,8) latitude,
544 HL.GEOMETRY_SOURCE
545 into
546 x_latitude,
547 x_longitude,
548 x_geo_source
549 from hz_locations HL
550 where location_id = p_loc_id;
551
552 IF (sql%rowcount = 0) THEN
553 x_return_status := 'E';
554 x_msg_count := 1;
555 FND_MESSAGE.SET_NAME('RRS','RRS_INVALID_OBJ_VALUE');
556 FND_MESSAGE.SET_TOKEN('OBJECT_NAME','LOCATION');
557 FND_MESSAGE.SET_TOKEN('OBJECT_VALUE',p_loc_id);
558 x_msg_data := FND_MESSAGE.GET;
559 END IF;
560
561 IF x_latitude IS NULL THEN
562 x_null_flag := 'T';
563 END IF;
564
565 EXCEPTION
566 when others then
567 x_return_status := 'E';
568 x_msg_count := 1;
569 x_msg_data := sqlerrm;
570
571 END get_geometry_for_location;
572
573 /* Added for bugfix 8903725 */
574 PROCEDURE set_geometry_src_for_location
575 (p_loc_id IN NUMBER,
576 x_geo_source_was_null OUT NOCOPY VARCHAR2,
577 x_geo_source_set_value OUT NOCOPY VARCHAR2,
578 x_return_status OUT NOCOPY VARCHAR2,
579 x_msg_count OUT NOCOPY NUMBER,
580 x_msg_data OUT NOCOPY VARCHAR2
581 )
582 IS
583 l_is_invalid_geocode NUMBER := 0; /* Bug 7143445 */
584 l_default_geosource VARCHAR2(30);
585 l_curr_geosource VARCHAR2(30);
586 BEGIN
587 x_return_status := 'S';
588 x_msg_data := null;
589 x_geo_source_was_null := 'N';
590
591 l_default_geosource := FND_PROFILE.VALUE('RRS_GEOCODE_SRC_PREFERENCE');
592 IF (l_default_geosource IS NULL) THEN
593 l_default_geosource := 'RRS_GOOGLE';
594 END IF;
595
596 select geometry_source
597 into l_curr_geosource
598 from hz_locations
599 where location_id = p_loc_id;
600
601 IF (sql%rowcount = 0) THEN
602 x_return_status := 'E';
603 x_msg_count := 1;
604 FND_MESSAGE.SET_NAME('RRS','RRS_INVALID_OBJ_VALUE');
605 FND_MESSAGE.SET_TOKEN('OBJECT_NAME','LOCATION');
606 FND_MESSAGE.SET_TOKEN('OBJECT_VALUE',p_loc_id);
607 x_msg_data := FND_MESSAGE.GET;
608 ELSE
609
610 UPDATE hz_locations
611 SET geometry_source = l_default_geosource
612 WHERE location_id = p_loc_id
613 AND geometry_source is null;
614
615 if sql%rowcount = 1 THEN
616 x_geo_source_was_null := 'Y';
617 end if;
618
619 END IF;
620
621 IF (x_geo_source_was_null = 'N') THEN
622 x_geo_source_set_value := l_curr_geosource;
623 ELSE
624 x_geo_source_set_value := l_default_geosource;
625 END IF;
626
627 EXCEPTION
628 when others then
629 x_return_status := 'E';
630 x_msg_count := 1;
631 x_msg_data := sqlerrm;
632
633 END set_geometry_src_for_location;
634
635
636
637 FUNCTION get_ordinate
638 (geom IN MDSYS.SDO_GEOMETRY,
639 indx IN NUMBER
640 ) RETURN NUMBER
641 IS
642 BEGIN
643 if ( geom.sdo_ordinates is null ) then return null;
644 elsif ( geom.sdo_ordinates.count < indx) then return null;
645 else return geom.sdo_ordinates (indx);
646 end if;
647 END;
648
649 FUNCTION get_address
650 (p_loc_id IN NUMBER
651 ) RETURN VARCHAR2
652 IS
653
654 l_address1 hz_locations.address1%type;
655 l_city hz_locations.city%type;
656 l_state hz_locations.city%type;
657 l_postal_code hz_locations.postal_code%type;
658 l_country hz_locations.country%type;
659 l_ret varchar2(500) := null;
660
661 BEGIN
662
663 select replace(hl.address1,' ','%20'),
664 replace(hl.city,' ','%20'),
665 replace(hl.state,' ','%20'),
666 replace(hl.postal_code,' ','%20'),
667 replace(hl.country,' ','%20')
668 into l_address1,
669 l_city,
670 l_state,
671 l_postal_code,
672 l_country
673 from hz_locations hl
674 where hl.location_id = p_loc_id;
675
676 IF(l_address1 is not null) THEN
677 l_ret := l_address1;
678 END IF;
679
680 IF(l_city is not null) THEN
681 l_ret := l_ret||'+'||l_city;
682 END IF;
683
684 IF(l_state is not null) THEN
685 l_ret := l_ret||'+'||l_state;
686 END IF;
687
688 IF(l_postal_code is not null) THEN
689 l_ret := l_ret||'+'||l_postal_code;
690 END IF;
691
692 IF(l_country is not null) THEN
693 l_ret := l_ret||'+'||l_country;
694 END IF;
695
696 RETURN l_ret;
697
698 EXCEPTION
699 when others then
700 RETURN l_ret;
701
702 END get_address;
703 PROCEDURE default_site_numbers
704 (p_result_format_usage_id IN NUMBER,
705 p_site_number_col_name VARCHAR2,
706 p_site_name_col_name VARCHAR2,
707 x_return_status OUT NOCOPY VARCHAR2,
708 x_msg_data OUT NOCOPY VARCHAR2
709 )
710 IS
711 l_default_seq_num NUMBER;
712 l_site_name VARCHAR2(1000);
713 l_cursor_sql VARCHAR2(1000);
714 l_row_id ROWID;--Added for Bug#9455517 Fix
715 l_mr_ag_count NUMBER;--Added for Bug#9455517 Fix
716 TYPE EBI_CURSOR_TYP IS REF CURSOR;
717 ebi_cursor EBI_CURSOR_TYP;
718
719 BEGIN
720 x_return_status := 'S';
721 x_msg_data := null;
722 --Modified query to fetch row id also
723 l_cursor_sql := 'SELECT ' || p_site_name_col_name || ' AS SITE_NAME, rowid' ||
724 ' FROM EGO_BULKLOAD_INTF WHERE RESULTFMT_USAGE_ID =' || TO_CHAR(p_result_format_usage_id) ||
725 ' AND ' || p_site_number_col_name || ' IS NULL';
726
727 OPEN ebi_cursor FOR l_cursor_sql;
728 LOOP
729 FETCH ebi_cursor INTO l_site_name,l_row_id;
730 -- l_default_seq_num := RRS_DEFAULT_SITE_NUMBER_S.NEXTVAL;
731 SELECT RRS_DEFAULT_SITE_NUMBER_S.NEXTVAL
732 INTO l_default_seq_num
733 FROM DUAL;
734
735 EXIT WHEN ebi_cursor%NOTFOUND;
736 --Start Bug#9455517 Fix
737 --Check whether Result usage Format contains Multi Row Attribute Group
738 --Logic
739 --IF Import format contains any Multi_row AG attr we need to treat all rows with same site name as one site and stamp same site number to all.
740 --IF Import format contains only Single_row AG attrs treat all rows with same site name as different sites and stamp new site number for each row.
741 --Added below query to fetch the number multi row AG's in a result format.
742 select count(*)
743 INTO l_mr_ag_count
744 FROM EGO_FND_DSC_FLX_CTX_EXT
745 WHERE attr_group_id in (
746 SELECT distinct( SUBSTR(fmtusg.attribute_code, 1,INSTR(fmtusg.attribute_code, '$$') - 1) )attr_group_id
747 FROM ego_results_fmt_usages fmtusg
748 WHERE fmtusg.resultfmt_usage_id = p_result_format_usage_id
749 AND fmtusg.attribute_code LIKE '%$$%'
750 )
751 AND MULTI_ROW='Y';
752 --
753 IF(l_mr_ag_count>0) --contians multi row
754 THEN
755 EXECUTE IMMEDIATE
756 'UPDATE EGO_BULKLOAD_INTF SET ' || p_site_number_col_name || ' = :1' ||
757 ' WHERE ' || p_site_name_col_name || ' = :2' ||
758 ' AND RESULTFMT_USAGE_ID = ' || TO_CHAR(p_result_format_usage_id) ||
759 ' AND ' || p_site_number_col_name || ' IS NULL'
760 USING TO_CHAR(l_default_seq_num), l_site_name;
761 ELSE
762 EXECUTE IMMEDIATE
763 'UPDATE EGO_BULKLOAD_INTF SET ' || p_site_number_col_name || ' = :1' ||
764 ' WHERE ' || p_site_name_col_name || ' = :2' ||
765 ' AND RESULTFMT_USAGE_ID = ' || TO_CHAR(p_result_format_usage_id) ||
766 ' AND ' || p_site_number_col_name || ' IS NULL'||
767 ' AND rowid'||'=:3'
768 USING TO_CHAR(l_default_seq_num), l_site_name,l_row_id;
769 END IF;
770 --End Bug#9455517 Fix
771 END LOOP;
772 CLOSE ebi_cursor;
773 EXCEPTION
774 when others then
775 x_return_status := 'E';
776 x_msg_data := sqlerrm;
777
778 END default_site_numbers;
779
780 Procedure Add_Favorite_objects(P_OBJECT_TYPE IN VARCHAR2,
781 P_OBJECT_ID IN VARCHAR2,
782 P_OBJECT_NAME IN VARCHAR2,
783 P_USER_ID IN NUMBER,
784 X_RET_STATUS OUT NOCOPY VARCHAR2)
785 is
786 type t_object_type is table of varchar2(20) index by binary_integer;
787 type t_object_id is table of Number index by binary_integer;
788 type t_object_name is table of varchar2(300) index by binary_integer;
789
790 v_object_type t_object_type;
791 v_object_id t_object_id;
792 v_object_name t_object_name;
793 obj_index Number;
794 str_index Number;
795 l_obj_type Varchar2(20);
796 l_obj_id VARCHAR2(20);
797 l_obj_name VARCHAR2(300);
798 l_ret_status VARCHAR2(1);
799 l_temp Number;
800
801 begin
802 obj_index := 1;
803 str_index := 1;
804 l_ret_status := 'S';
805 FND_MSG_PUB.Delete_Msg(null);
806 WHILE str_index <= length(P_OBJECT_TYPE)
807 LOOP
808 IF ( substr(P_OBJECT_TYPE,str_index,4) = '~@#^') THEN
809 v_object_type(obj_index) := l_obj_type;
810 obj_index := obj_index + 1;
811 l_obj_type := '';
812 str_index := str_index + 4;
813 ELSE
814 l_obj_type := l_obj_type || substr(P_OBJECT_TYPE,str_index,1);
815 str_index := str_index + 1;
816 END IF;
817 END LOOP;
818 obj_index := 1;
819 str_index := 1;
820
821 WHILE str_index <= length(P_OBJECT_ID)
822 LOOP
823 IF ( substr(P_OBJECT_ID,str_index,4) = '~@#^') THEN
824 v_object_id(obj_index) := to_number(l_obj_id);
825 obj_index := obj_index + 1;
826 l_obj_id := '';
827 str_index := str_index + 4;
828 ELSE
829 l_obj_id := l_obj_id || substr(P_OBJECT_ID,str_index,1);
830 str_index := str_index + 1;
831 END IF;
832 END LOOP;
833 obj_index := 1;
834 str_index := 1;
835 WHILE str_index <= length(P_OBJECT_NAME)
836 LOOP
837 IF ( substr(P_OBJECT_NAME,str_index,4) = '~@#^') THEN
838 v_object_name(obj_index) := l_obj_name;
839 obj_index := obj_index + 1;
840 l_obj_name := '';
841 str_index := str_index + 4;
842 ELSE
843 l_obj_name := l_obj_name || substr(P_OBJECT_NAME,str_index,1);
844 str_index := str_index + 1;
845 END IF;
846 END LOOP;
847
848 obj_index := v_object_type.first;
849 while obj_index <= v_object_type.last
850 LOOP
851 BEGIN
852 select 1 into l_temp from rrs_user_favorites
853 where object_type = v_object_type(obj_index) and
854 object_id = v_object_id(obj_index) and
855 user_id = P_USER_ID;
856
857 v_object_type.delete(obj_index);
858 v_object_id.delete(obj_index);
859 EXCEPTION
860 WHEN OTHERS THEN
861 null;
862 END;
863 obj_index := v_object_type.next(obj_index);
864 END LOOP;
865
866 if l_ret_status = 'S' then
867 /* Bug Fix 10120905 --Narasimha */
868 if v_object_type.count>0 then
869 /*Bug Fix 9700523 -- Modified by Narasimha */
870 for ins_index in v_object_type.first..v_object_type.last
871 loop
872 if v_object_type.exists(ins_index) then
873 insert into rrs_user_favorites
874 (USER_FAVORITE_ID,
875 OBJECT_TYPE,
876 OBJECT_ID,
877 USER_ID,
878 LAST_UPDATE_DATE,
879 LAST_UPDATED_BY,
880 CREATION_DATE,
881 CREATED_BY,
882 LAST_UPDATE_LOGIN)
883 values
884 (RRS_FAVORITE_S.nextval,
885 v_object_type(ins_index),
886 v_object_id(ins_index),
887 P_USER_ID,
888 sysdate,
889 fnd_global.user_id,
890 sysdate,
891 fnd_global.user_id,
892 fnd_global.login_id);
893 end if;
894 end loop;
895 end if;
896 end if;
897
898 X_RET_STATUS := l_ret_status;
899
900 EXCEPTION
901 when others then
902 FND_MSG_PUB.Add_Exc_Msg('RRS_SITE_UTILS','Add_Favorite_objects',sqlerrm(sqlcode));
903 X_RET_STATUS := 'E';
904 end Add_Favorite_objects;
905
906
907
908 --Bug Fix 8502761: check whether the assigned attribute group has been populated for some site. - jijiao 1/29/2010
909 Procedure isAGAndClsAssocDeletable
910 (
911 p_application_id IN NUMBER,
912 p_classification_code IN VARCHAR2,
913 p_attr_group_type IN VARCHAR2,
914 p_attr_group_name IN VARCHAR2,
915 x_is_ag_deletable OUT NOCOPY VARCHAR2
916 )IS
917
918 l_attr_group_id NUMBER;
919 l_num_of_rows NUMBER;
920
921 BEGIN
922 SELECT ATTR_GROUP_ID
923 INTO l_attr_group_id
924 FROM EGO_OBJ_ATTR_GRP_ASSOCS_V
925 WHERE APPLICATION_ID = p_application_id
926 AND CLASSIFICATION_CODE = p_classification_code
927 AND ATTR_GROUP_TYPE = p_attr_group_type
928 AND ATTR_GROUP_NAME = p_attr_group_name;
929
930 IF p_attr_group_type = 'RRS_SITEMGMT_GROUP' THEN
931
932 SELECT COUNT(*)
933 INTO l_num_of_rows
934 FROM RRS_SITES_EXT_VL
935 WHERE ATTR_GROUP_ID = l_attr_group_id
936 AND SITE_USE_TYPE_CODE = p_classification_code;
937
938 ELSIF p_attr_group_type = 'RRS_LOCATION_GROUP' THEN
939
940 SELECT COUNT(*)
941 INTO l_num_of_rows
942 FROM RRS_LOCATIONS_EXT_VL
943 WHERE ATTR_GROUP_ID = l_attr_group_id
944 AND COUNTRY = p_classification_code;
945
946 ELSIF p_attr_group_type = 'RRS_TRADE_AREA_GROUP' THEN
947
948 SELECT COUNT(*)
949 INTO l_num_of_rows
950 FROM RRS_TRADE_AREAS_EXT_VL
951 WHERE ATTR_GROUP_ID = l_attr_group_id
952 AND GROUP_ID = p_classification_code;
953
954 ELSIF p_attr_group_type = 'RRS_HIERARCHY_GROUP' THEN
955
956 SELECT COUNT(*)
957 INTO l_num_of_rows
958 FROM RRS_HIERARCHIES_EXT_VL
959 WHERE ATTR_GROUP_ID = l_attr_group_id
960 AND HIERARCHY_PURPOSE_CODE = p_classification_code;
961
962 END IF;
963
964 IF l_num_of_rows = 0 THEN
965 x_is_ag_deletable := 'Y';
966 ELSE
967 x_is_ag_deletable := 'N';
968 END IF;
969
970 END isAGAndClsAssocDeletable;
971 --Added for Bug#9839096 -Vishnu
972 --Funtion to get multi attribute count
973 FUNCTION get_multiAG_count(p_result_format_usage_id IN NUMBER)
974 RETURN NUMBER
975 IS
976 l_mr_ag_count NUMBER :=0;
977 BEGIN
978 select count(*)
979 INTO l_mr_ag_count
980 FROM EGO_FND_DSC_FLX_CTX_EXT
981 WHERE attr_group_id in (
982 SELECT distinct( SUBSTR(fmtusg.attribute_code, 1,INSTR(fmtusg.attribute_code, '$$') - 1) )attr_group_id
983 FROM ego_results_fmt_usages fmtusg
984 WHERE fmtusg.resultfmt_usage_id = p_result_format_usage_id
985 AND fmtusg.attribute_code LIKE '%$$%'
986 )
987 AND MULTI_ROW='Y';
988 RETURN l_mr_ag_count;
989 END get_multiAG_count;
990
991 END RRS_SITE_UTILS;