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.HZ_ELOCATION_PKG dependencies on HZ_LOCATIONS

Line 122: -- Call the HZ_LOCATIONS_PKG Table handler to update location

118: ': ' || SUBSTRB(msg, 1, 240));
119: END time_put_line;
120:
121: -------------------------------------------------------------
122: -- Call the HZ_LOCATIONS_PKG Table handler to update location
123: -------------------------------------------------------------
124: PROCEDURE update_geo_location (
125: p_location_id IN NUMBER,
126: p_geo IN mdsys.sdo_geometry,

Line 137: FROM hz_locations

133: l_rowid VARCHAR2(1000);
134: l_debug_prefix VARCHAR2(30) := '';
135: CURSOR curowid IS
136: SELECT rowid
137: FROM hz_locations
138: WHERE location_id = p_location_id;
139: BEGIN
140: IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
141: hz_utility_v2pub.debug(p_message=>'hz_locations_pkg.update_geo_location for location_id :'||

Line 141: hz_utility_v2pub.debug(p_message=>'hz_locations_pkg.update_geo_location for location_id :'||

137: FROM hz_locations
138: WHERE location_id = p_location_id;
139: BEGIN
140: IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
141: hz_utility_v2pub.debug(p_message=>'hz_locations_pkg.update_geo_location for location_id :'||
142: TO_CHAR(p_location_id)||'(+)',
143: p_prefix=>l_debug_prefix,
144: p_msg_level=>fnd_log.level_procedure);
145: END IF;

Line 160: hz_utility_v2pub.debug(p_message=>'hz_locations_pkg.update_row for location_id :' ||

156: p_msg_level=>fnd_log.level_error);
157: END IF;
158: ELSE
159: IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
160: hz_utility_v2pub.debug(p_message=>'hz_locations_pkg.update_row for location_id :' ||
161: TO_CHAR(p_location_id)||'(+)',
162: p_prefix=>l_debug_prefix,
163: p_msg_level=>fnd_log.level_procedure);
164: END IF;

Line 165: hz_locations_pkg.update_row (

161: TO_CHAR(p_location_id)||'(+)',
162: p_prefix=>l_debug_prefix,
163: p_msg_level=>fnd_log.level_procedure);
164: END IF;
165: hz_locations_pkg.update_row (
166: x_rowid => l_rowid,
167: x_location_id => p_location_id,
168: x_attribute_category => NULL,
169: x_attribute1 => NULL,

Line 243: hz_utility_v2pub.debug(p_message=>'hz_locations_pkg.update_row for location_id :' ||

239: p_prefix =>l_debug_prefix,
240: p_msg_level=>fnd_log.level_statement);
241: END IF;
242: IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
243: hz_utility_v2pub.debug(p_message=>'hz_locations_pkg.update_row for location_id :' ||
244: TO_CHAR(p_location_id)||'(-)',
245: p_prefix=>l_debug_prefix,
246: p_msg_level=>fnd_log.level_procedure);
247: END IF;

Line 264: hz_utility_v2pub.debug(p_message=>'hz_locations_pkg.update_geo_location for location_id :'

260: p_count => x_msg_count,
261: p_data => x_msg_data
262: );
263: IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
264: hz_utility_v2pub.debug(p_message=>'hz_locations_pkg.update_geo_location for location_id :'
265: || TO_CHAR(p_location_id)||'(-)',
266: p_prefix=>l_debug_prefix,
267: p_msg_level=>fnd_log.level_procedure);
268: END IF;

Line 274: -- Synchronized geometry in hz_locations with latitude and longitude from

270:
271: --------------------------------------
272: -- PUBLIC PROCEDURE update_geometry
273: -- DESCRIPTION
274: -- Synchronized geometry in hz_locations with latitude and longitude from
275: -- Oracle's eLocation service.
276: -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
277: -- hz_utility_v2pub
278: -- fnd_message

Line 304: /* TYPE locationlist IS TABLE OF hz_locations.location_id%TYPE;

300: p_nb_row IN NUMBER DEFAULT 20,
301: p_nb_try IN NUMBER DEFAULT 3
302:
303: ) IS
304: /* TYPE locationlist IS TABLE OF hz_locations.location_id%TYPE;
305: TYPE addresslist IS TABLE OF hz_locations.address1%TYPE;
306: TYPE citylist IS TABLE OF hz_locations.city%TYPE;
307: TYPE pcodelist IS TABLE OF hz_locations.postal_code%TYPE;
308: TYPE statelist IS TABLE OF hz_locations.state%TYPE;

Line 305: TYPE addresslist IS TABLE OF hz_locations.address1%TYPE;

301: p_nb_try IN NUMBER DEFAULT 3
302:
303: ) IS
304: /* TYPE locationlist IS TABLE OF hz_locations.location_id%TYPE;
305: TYPE addresslist IS TABLE OF hz_locations.address1%TYPE;
306: TYPE citylist IS TABLE OF hz_locations.city%TYPE;
307: TYPE pcodelist IS TABLE OF hz_locations.postal_code%TYPE;
308: TYPE statelist IS TABLE OF hz_locations.state%TYPE;
309: TYPE countrylist IS TABLE OF hz_locations.country%TYPE;

Line 306: TYPE citylist IS TABLE OF hz_locations.city%TYPE;

302:
303: ) IS
304: /* TYPE locationlist IS TABLE OF hz_locations.location_id%TYPE;
305: TYPE addresslist IS TABLE OF hz_locations.address1%TYPE;
306: TYPE citylist IS TABLE OF hz_locations.city%TYPE;
307: TYPE pcodelist IS TABLE OF hz_locations.postal_code%TYPE;
308: TYPE statelist IS TABLE OF hz_locations.state%TYPE;
309: TYPE countrylist IS TABLE OF hz_locations.country%TYPE;
310: TYPE countylist IS TABLE OF hz_locations.county%TYPE;

Line 307: TYPE pcodelist IS TABLE OF hz_locations.postal_code%TYPE;

303: ) IS
304: /* TYPE locationlist IS TABLE OF hz_locations.location_id%TYPE;
305: TYPE addresslist IS TABLE OF hz_locations.address1%TYPE;
306: TYPE citylist IS TABLE OF hz_locations.city%TYPE;
307: TYPE pcodelist IS TABLE OF hz_locations.postal_code%TYPE;
308: TYPE statelist IS TABLE OF hz_locations.state%TYPE;
309: TYPE countrylist IS TABLE OF hz_locations.country%TYPE;
310: TYPE countylist IS TABLE OF hz_locations.county%TYPE;
311: TYPE provincelist IS TABLE OF hz_locations.province%TYPE;

Line 308: TYPE statelist IS TABLE OF hz_locations.state%TYPE;

304: /* TYPE locationlist IS TABLE OF hz_locations.location_id%TYPE;
305: TYPE addresslist IS TABLE OF hz_locations.address1%TYPE;
306: TYPE citylist IS TABLE OF hz_locations.city%TYPE;
307: TYPE pcodelist IS TABLE OF hz_locations.postal_code%TYPE;
308: TYPE statelist IS TABLE OF hz_locations.state%TYPE;
309: TYPE countrylist IS TABLE OF hz_locations.country%TYPE;
310: TYPE countylist IS TABLE OF hz_locations.county%TYPE;
311: TYPE provincelist IS TABLE OF hz_locations.province%TYPE;
312: l_location_ids locationlist;

Line 309: TYPE countrylist IS TABLE OF hz_locations.country%TYPE;

305: TYPE addresslist IS TABLE OF hz_locations.address1%TYPE;
306: TYPE citylist IS TABLE OF hz_locations.city%TYPE;
307: TYPE pcodelist IS TABLE OF hz_locations.postal_code%TYPE;
308: TYPE statelist IS TABLE OF hz_locations.state%TYPE;
309: TYPE countrylist IS TABLE OF hz_locations.country%TYPE;
310: TYPE countylist IS TABLE OF hz_locations.county%TYPE;
311: TYPE provincelist IS TABLE OF hz_locations.province%TYPE;
312: l_location_ids locationlist;
313: l_address1s addresslist;

Line 310: TYPE countylist IS TABLE OF hz_locations.county%TYPE;

306: TYPE citylist IS TABLE OF hz_locations.city%TYPE;
307: TYPE pcodelist IS TABLE OF hz_locations.postal_code%TYPE;
308: TYPE statelist IS TABLE OF hz_locations.state%TYPE;
309: TYPE countrylist IS TABLE OF hz_locations.country%TYPE;
310: TYPE countylist IS TABLE OF hz_locations.county%TYPE;
311: TYPE provincelist IS TABLE OF hz_locations.province%TYPE;
312: l_location_ids locationlist;
313: l_address1s addresslist;
314: l_address2s addresslist;

Line 311: TYPE provincelist IS TABLE OF hz_locations.province%TYPE;

307: TYPE pcodelist IS TABLE OF hz_locations.postal_code%TYPE;
308: TYPE statelist IS TABLE OF hz_locations.state%TYPE;
309: TYPE countrylist IS TABLE OF hz_locations.country%TYPE;
310: TYPE countylist IS TABLE OF hz_locations.county%TYPE;
311: TYPE provincelist IS TABLE OF hz_locations.province%TYPE;
312: l_location_ids locationlist;
313: l_address1s addresslist;
314: l_address2s addresslist;
315: l_address3s addresslist;

Line 331: FROM hz_locations hl

327: -- and p_country was not passed
328: CURSOR cu_loc1a (p_request_id IN NUMBER) IS
329: SELECT hl.location_id, hl.address1, hl.address2, hl.address3, hl.address4,
330: hl.city, hl.postal_code, hl.state, hl.country, hl.county, hl.province
331: FROM hz_locations hl
332: WHERE EXISTS (SELECT 1
333: FROM hz_party_site_uses hpsu,
334: hz_party_sites hps
335: WHERE hpsu.site_use_type = p_site_use_type

Line 349: FROM hz_locations hl

345: -- and p_country was passed
346: CURSOR cu_loc1ac (p_request_id IN NUMBER) IS
347: SELECT hl.location_id, hl.address1, hl.address2, hl.address3, hl.address4,
348: hl.city, hl.postal_code, hl.state, hl.country, hl.county, hl.province
349: FROM hz_locations hl
350: WHERE EXISTS (SELECT 1
351: FROM hz_party_site_uses hpsu,
352: hz_party_sites hps
353: WHERE hpsu.site_use_type = p_site_use_type

Line 367: FROM hz_locations hl

363: -- and p_country was not passed
364: CURSOR cu_loc1b (p_request_id IN NUMBER) IS
365: SELECT hl.location_id, hl.address1, hl.address2, hl.address3, hl.address4,
366: hl.city, hl.postal_code, hl.state, hl.country, hl.county, hl.province
367: FROM hz_locations hl
368: WHERE EXISTS (SELECT 1
369: FROM hz_party_site_uses hpsu,
370: hz_party_sites hps
371: WHERE hpsu.site_use_type = p_site_use_type

Line 385: FROM hz_locations hl

381: -- and p_country was passed
382: CURSOR cu_loc1bc (p_request_id IN NUMBER) IS
383: SELECT hl.location_id, hl.address1, hl.address2, hl.address3, hl.address4,
384: hl.city, hl.postal_code, hl.state, hl.country, hl.county, hl.province
385: FROM hz_locations hl
386: WHERE EXISTS (SELECT 1
387: FROM hz_party_site_uses hpsu,
388: hz_party_sites hps
389: WHERE hpsu.site_use_type = p_site_use_type

Line 404: FROM hz_locations hl

400: -- and p_country was not passed
401: CURSOR cu_loc2a (p_request_id IN NUMBER) IS
402: SELECT hl.location_id, hl.address1, hl.address2, hl.address3, hl.address4,
403: hl.city, hl.postal_code, hl.state, hl.country, hl.county, hl.province
404: FROM hz_locations hl
405: WHERE EXISTS (SELECT 1
406: FROM hz_party_sites hps
407: WHERE hps.identifying_address_flag = DECODE(p_iden_addr_only, 'Y', 'Y', hps.identifying_address_flag)
408: AND hps.location_id = hl.location_id)

Line 418: FROM hz_locations hl

414: -- and p_country was passed
415: CURSOR cu_loc2ac (p_request_id IN NUMBER) IS
416: SELECT hl.location_id, hl.address1, hl.address2, hl.address3, hl.address4,
417: hl.city, hl.postal_code, hl.state, hl.country, hl.county, hl.province
418: FROM hz_locations hl
419: WHERE EXISTS (SELECT 1
420: FROM hz_party_sites hps
421: WHERE hps.identifying_address_flag = DECODE(p_iden_addr_only, 'Y', 'Y', hps.identifying_address_flag)
422: AND hps.location_id = hl.location_id)

Line 432: FROM hz_locations hl

428: -- and we are NOT running for Standalone Locations
429: CURSOR cu_loc2b (p_request_id IN NUMBER) IS
430: SELECT hl.location_id, hl.address1, hl.address2, hl.address3, hl.address4,
431: hl.city, hl.postal_code, hl.state, hl.country, hl.county, hl.province
432: FROM hz_locations hl
433: WHERE EXISTS (SELECT 1
434: FROM hz_party_sites hps
435: WHERE hps.identifying_address_flag = DECODE(p_iden_addr_only, 'Y', 'Y', hps.identifying_address_flag)
436: AND hps.location_id = hl.location_id)

Line 447: FROM hz_locations hl

443: -- and p_country was passed
444: CURSOR cu_loc2bc (p_request_id IN NUMBER) IS
445: SELECT hl.location_id, hl.address1, hl.address2, hl.address3, hl.address4,
446: hl.city, hl.postal_code, hl.state, hl.country, hl.county, hl.province
447: FROM hz_locations hl
448: WHERE EXISTS (SELECT 1
449: FROM hz_party_sites hps
450: WHERE hps.identifying_address_flag = DECODE(p_iden_addr_only, 'Y', 'Y', hps.identifying_address_flag)
451: AND hps.location_id = hl.location_id)

Line 462: FROM hz_locations hl

458: -- and p_country was not passed
459: CURSOR cu_loc3a (p_request_id IN NUMBER) IS
460: SELECT hl.location_id, hl.address1, hl.address2, hl.address3, hl.address4,
461: hl.city, hl.postal_code, hl.state, hl.country, hl.county, hl.province
462: FROM hz_locations hl
463: WHERE NVL(hl.request_id, -1) <> NVL(p_request_id, -2)
464: AND NOT EXISTS (SELECT 1
465: FROM HZ_PARTY_SITES hps
466: WHERE hps.location_id = hl.location_id );

Line 474: FROM hz_locations hl

470: -- and p_country was passed
471: CURSOR cu_loc3ac (p_request_id IN NUMBER) IS
472: SELECT hl.location_id, hl.address1, hl.address2, hl.address3, hl.address4,
473: hl.city, hl.postal_code, hl.state, hl.country, hl.county, hl.province
474: FROM hz_locations hl
475: WHERE hl.country = p_country
476: AND NVL(hl.request_id, -1) <> NVL(p_request_id, -2)
477: AND NOT EXISTS (SELECT 1
478: FROM HZ_PARTY_SITES hps

Line 487: FROM hz_locations hl

483: -- and p_country was not passed
484: CURSOR cu_loc3b (p_request_id IN NUMBER) IS
485: SELECT hl.location_id, hl.address1, hl.address2, hl.address3, hl.address4,
486: hl.city, hl.postal_code, hl.state, hl.country, hl.county, hl.province
487: FROM hz_locations hl
488: WHERE NVL(hl.request_id, -1) <> NVL(p_request_id, -2)
489: AND (hl.geometry_status_code = 'DIRTY' OR hl.geometry_status_code IS NULL)
490: AND NOT EXISTS (SELECT 1
491: FROM HZ_PARTY_SITES hps

Line 500: FROM hz_locations hl

496: -- and p_country was passed
497: CURSOR cu_loc3bc (p_request_id IN NUMBER) IS
498: SELECT hl.location_id, hl.address1, hl.address2, hl.address3, hl.address4,
499: hl.city, hl.postal_code, hl.state, hl.country, hl.county, hl.province
500: FROM hz_locations hl
501: WHERE hl.country = p_country
502: AND NVL(hl.request_id, -1) <> NVL(p_request_id, -2)
503: AND (hl.geometry_status_code = 'DIRTY' OR hl.geometry_status_code IS NULL)
504: AND NOT EXISTS (SELECT 1

Line 1916: -- Synchronized geometry in hz_locations with latitude,longitude and accuracy from

1912:
1913: --------------------------------------
1914: -- PUBLIC PROCEDURE update_geometry_all
1915: -- DESCRIPTION
1916: -- Synchronized geometry in hz_locations with latitude,longitude and accuracy from
1917: -- Oracle's eLocation service.
1918: -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1919: -- hz_utility_v2pub
1920: -- fnd_message

Line 1926: -- Geometry (NULL value) should be removed from HZ_LOCATIONS Update.

1922: -- MODIFICATION HISTORY
1923: -- 29-11-2010 Sudhir Gokavarapu Created - ER10211310
1924: -- Bug12386319 default value of p_nb_row is changed to 10.
1925: -- Bug12386226 - When Match Count is zero then
1926: -- Geometry (NULL value) should be removed from HZ_LOCATIONS Update.
1927:
1928: PROCEDURE update_geometry_all (
1929: errbuf OUT NOCOPY VARCHAR2,
1930: retcode OUT NOCOPY VARCHAR2,

Line 1948: TYPE locationlist IS TABLE OF hz_locations.location_id%TYPE;

1944: p_nb_row IN NUMBER DEFAULT 10,
1945: p_nb_try IN NUMBER DEFAULT 3
1946:
1947: ) IS
1948: TYPE locationlist IS TABLE OF hz_locations.location_id%TYPE;
1949: TYPE addresslist IS TABLE OF hz_locations.address1%TYPE;
1950: TYPE citylist IS TABLE OF hz_locations.city%TYPE;
1951: TYPE pcodelist IS TABLE OF hz_locations.postal_code%TYPE;
1952: TYPE statelist IS TABLE OF hz_locations.state%TYPE;

Line 1949: TYPE addresslist IS TABLE OF hz_locations.address1%TYPE;

1945: p_nb_try IN NUMBER DEFAULT 3
1946:
1947: ) IS
1948: TYPE locationlist IS TABLE OF hz_locations.location_id%TYPE;
1949: TYPE addresslist IS TABLE OF hz_locations.address1%TYPE;
1950: TYPE citylist IS TABLE OF hz_locations.city%TYPE;
1951: TYPE pcodelist IS TABLE OF hz_locations.postal_code%TYPE;
1952: TYPE statelist IS TABLE OF hz_locations.state%TYPE;
1953: TYPE countrylist IS TABLE OF hz_locations.country%TYPE;

Line 1950: TYPE citylist IS TABLE OF hz_locations.city%TYPE;

1946:
1947: ) IS
1948: TYPE locationlist IS TABLE OF hz_locations.location_id%TYPE;
1949: TYPE addresslist IS TABLE OF hz_locations.address1%TYPE;
1950: TYPE citylist IS TABLE OF hz_locations.city%TYPE;
1951: TYPE pcodelist IS TABLE OF hz_locations.postal_code%TYPE;
1952: TYPE statelist IS TABLE OF hz_locations.state%TYPE;
1953: TYPE countrylist IS TABLE OF hz_locations.country%TYPE;
1954: TYPE countylist IS TABLE OF hz_locations.county%TYPE;

Line 1951: TYPE pcodelist IS TABLE OF hz_locations.postal_code%TYPE;

1947: ) IS
1948: TYPE locationlist IS TABLE OF hz_locations.location_id%TYPE;
1949: TYPE addresslist IS TABLE OF hz_locations.address1%TYPE;
1950: TYPE citylist IS TABLE OF hz_locations.city%TYPE;
1951: TYPE pcodelist IS TABLE OF hz_locations.postal_code%TYPE;
1952: TYPE statelist IS TABLE OF hz_locations.state%TYPE;
1953: TYPE countrylist IS TABLE OF hz_locations.country%TYPE;
1954: TYPE countylist IS TABLE OF hz_locations.county%TYPE;
1955: TYPE provincelist IS TABLE OF hz_locations.province%TYPE;

Line 1952: TYPE statelist IS TABLE OF hz_locations.state%TYPE;

1948: TYPE locationlist IS TABLE OF hz_locations.location_id%TYPE;
1949: TYPE addresslist IS TABLE OF hz_locations.address1%TYPE;
1950: TYPE citylist IS TABLE OF hz_locations.city%TYPE;
1951: TYPE pcodelist IS TABLE OF hz_locations.postal_code%TYPE;
1952: TYPE statelist IS TABLE OF hz_locations.state%TYPE;
1953: TYPE countrylist IS TABLE OF hz_locations.country%TYPE;
1954: TYPE countylist IS TABLE OF hz_locations.county%TYPE;
1955: TYPE provincelist IS TABLE OF hz_locations.province%TYPE;
1956: l_location_ids locationlist;

Line 1953: TYPE countrylist IS TABLE OF hz_locations.country%TYPE;

1949: TYPE addresslist IS TABLE OF hz_locations.address1%TYPE;
1950: TYPE citylist IS TABLE OF hz_locations.city%TYPE;
1951: TYPE pcodelist IS TABLE OF hz_locations.postal_code%TYPE;
1952: TYPE statelist IS TABLE OF hz_locations.state%TYPE;
1953: TYPE countrylist IS TABLE OF hz_locations.country%TYPE;
1954: TYPE countylist IS TABLE OF hz_locations.county%TYPE;
1955: TYPE provincelist IS TABLE OF hz_locations.province%TYPE;
1956: l_location_ids locationlist;
1957: l_address1s addresslist;

Line 1954: TYPE countylist IS TABLE OF hz_locations.county%TYPE;

1950: TYPE citylist IS TABLE OF hz_locations.city%TYPE;
1951: TYPE pcodelist IS TABLE OF hz_locations.postal_code%TYPE;
1952: TYPE statelist IS TABLE OF hz_locations.state%TYPE;
1953: TYPE countrylist IS TABLE OF hz_locations.country%TYPE;
1954: TYPE countylist IS TABLE OF hz_locations.county%TYPE;
1955: TYPE provincelist IS TABLE OF hz_locations.province%TYPE;
1956: l_location_ids locationlist;
1957: l_address1s addresslist;
1958: l_address2s addresslist;

Line 1955: TYPE provincelist IS TABLE OF hz_locations.province%TYPE;

1951: TYPE pcodelist IS TABLE OF hz_locations.postal_code%TYPE;
1952: TYPE statelist IS TABLE OF hz_locations.state%TYPE;
1953: TYPE countrylist IS TABLE OF hz_locations.country%TYPE;
1954: TYPE countylist IS TABLE OF hz_locations.county%TYPE;
1955: TYPE provincelist IS TABLE OF hz_locations.province%TYPE;
1956: l_location_ids locationlist;
1957: l_address1s addresslist;
1958: l_address2s addresslist;
1959: l_address3s addresslist;

Line 2167: l_qry_str := l_qry_str ||'FROM hz_locations hl ,ar_lookups arl ';

2163:
2164: -- Dynamic SQL statement
2165: l_qry_str := 'SELECT hl.location_id, hl.address1, hl.address2, hl.address3, hl.address4, hl.city, ';
2166: l_qry_str := l_qry_str ||'hl.postal_code, hl.state, hl.country, hl.county, hl.province ';
2167: l_qry_str := l_qry_str ||'FROM hz_locations hl ,ar_lookups arl ';
2168: l_qry_str := l_qry_str ||'WHERE arl.lookup_type = '|| '''' ||'HZ_SPATIAL_VALID_COUNTRIES'|| '''' ;
2169: l_qry_str := l_qry_str ||' AND hl.country = arl.lookup_code ';
2170:
2171:

Line 2372: UPDATE HZ_LOCATIONS

2368: );
2369: */
2370: -- Instead of calling Table Handler changed to direct update statement.
2371: IF l_array(j).geometry_accuracy = 0 THEN
2372: UPDATE HZ_LOCATIONS
2373: SET geometry = NULL,
2374: geometry_status_code = l_array(j).geometry_status_code,
2375: geometry_source = 'ELOCATION',
2376: geometry_accuracy = l_array(j).geometry_accuracy,

Line 2386: UPDATE HZ_LOCATIONS

2382: program_id = hz_utility_v2pub.program_id,
2383: program_update_date = hz_utility_v2pub.program_update_date
2384: WHERE location_id = l_array(j).location_id;
2385: ELSE
2386: UPDATE HZ_LOCATIONS
2387: SET geometry = l_array(j).geometry,
2388: geometry_status_code = l_array(j).geometry_status_code,
2389: geometry_source = 'ELOCATION',
2390: geometry_accuracy = l_array(j).geometry_accuracy,

Line 2681: -- Rebuilds the spatial index on HZ_LOCATIONS.GEOMETRY. Rebuilding the

2677:
2678: --------------------------------------
2679: -- PUBLIC PROCEDURE rebuild_location_index
2680: -- DESCRIPTION
2681: -- Rebuilds the spatial index on HZ_LOCATIONS.GEOMETRY. Rebuilding the
2682: -- spatial index is required so that the index performs adequately and
2683: -- that queries can accurately extract the spatial data.
2684: -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
2685: -- hz_utility_v2pub

Line 2710: -- in insert proper table owner for HZ_LOCATIONS

2706: -- function at exception block.
2707: -- 11-NOV-2008 Nishant Singhai Bug 7262437 : changed insert statement
2708: -- from view user_sdo_geom_metadata to
2709: -- table MDSYS.SDO_GEOM_METADATA_TABLE
2710: -- in insert proper table owner for HZ_LOCATIONS
2711: -- when setup data is created from conc. program.
2712: --------------------------------------
2713: PROCEDURE rebuild_location_index (
2714: errbuf OUT NOCOPY VARCHAR2,

Line 2729: WHERE index_name = 'HZ_LOCATIONS_N15' and owner = x_index_owner;

2725:
2726: CURSOR c_index (x_index_owner IN VARCHAR2) IS
2727: SELECT status, domidx_opstatus
2728: FROM sys.all_indexes
2729: WHERE index_name = 'HZ_LOCATIONS_N15' and owner = x_index_owner;
2730: /* Fix perf bug 4956727
2731: CURSOR c_policies IS
2732: SELECT object_owner, object_name, policy_name
2733: FROM sys.all_policies

Line 2735: AND object_name = 'HZ_LOCATIONS';

2731: CURSOR c_policies IS
2732: SELECT object_owner, object_name, policy_name
2733: FROM sys.all_policies
2734: WHERE enable LIKE 'Y%'
2735: AND object_name = 'HZ_LOCATIONS';
2736: */
2737: CURSOR c_policies(l_schema VARCHAR2) IS
2738: SELECT object_owner, object_name, policy_name
2739: FROM sys.dba_policies

Line 2742: AND object_name = 'HZ_LOCATIONS';

2738: SELECT object_owner, object_name, policy_name
2739: FROM sys.dba_policies
2740: WHERE enable = 'YES'
2741: AND object_owner = l_schema
2742: AND object_name = 'HZ_LOCATIONS';
2743:
2744: TYPE t_namelist IS TABLE OF VARCHAR2(30);
2745: l_owners t_namelist;
2746: l_objects t_namelist;

Line 2777: Where table_name = ''HZ_LOCATIONS''

2773: IF c_index%NOTFOUND THEN /* Index is Missing */
2774: -- Delete Meta Data
2775: -- Bug 7262437 : Changed deleting from view to directly from table
2776: /* x_del_meta := 'Delete user_sdo_geom_metadata
2777: Where table_name = ''HZ_LOCATIONS''
2778: And column_name= ''GEOMETRY''';
2779: */
2780: x_del_meta := 'Delete MDSYS.SDO_GEOM_METADATA_TABLE
2781: Where sdo_table_name = ''HZ_LOCATIONS''

Line 2781: Where sdo_table_name = ''HZ_LOCATIONS''

2777: Where table_name = ''HZ_LOCATIONS''
2778: And column_name= ''GEOMETRY''';
2779: */
2780: x_del_meta := 'Delete MDSYS.SDO_GEOM_METADATA_TABLE
2781: Where sdo_table_name = ''HZ_LOCATIONS''
2782: AND sdo_column_name= ''GEOMETRY''
2783: AND sdo_owner = '''||x_index_owner||'''';
2784: EXECUTE IMMEDIATE x_del_meta;
2785: -- Create Meta Data

Line 2790: ''HZ_LOCATIONS'', ''GEOMETRY'',

2786: -- Bug 7262437 : Changed inserting directly into table insteda of view
2787: -- to avoid default user name (APPS) getting inserted in sdo_owner column
2788: /* x_ins_meta := 'INSERT INTO user_sdo_geom_metadata (
2789: table_name, column_name, diminfo, srid ) VALUES (
2790: ''HZ_LOCATIONS'', ''GEOMETRY'',
2791: mdsys.sdo_dim_array(
2792: mdsys.sdo_dim_element(''longitude'', -180, 180, 0.00005),
2793: mdsys.sdo_dim_element(''latitude'', -90, 90, 0.00005)), 8307 )';
2794: */

Line 2798: '''HZ_LOCATIONS'', ''GEOMETRY'',

2794: */
2795: x_ins_meta := 'INSERT INTO MDSYS.SDO_GEOM_METADATA_TABLE (
2796: sdo_owner, sdo_table_name, sdo_column_name, sdo_diminfo, sdo_srid ) VALUES ('''
2797: ||x_index_owner||''', '||
2798: '''HZ_LOCATIONS'', ''GEOMETRY'',
2799: mdsys.sdo_dim_array(
2800: mdsys.sdo_dim_element(''longitude'', -180, 180, 0.00005),
2801: mdsys.sdo_dim_element(''latitude'', -90, 90, 0.00005)), 8307 )';
2802:

Line 2810: x_drop_index := 'drop index '||x_index_owner||'.'|| 'HZ_LOCATIONS_N15 force';

2806: ELSIF c_index%FOUND THEN
2807: IF l_status <> 'VALID' OR l_domidx_opstatus <> 'VALID' THEN
2808: /* Index Is Invalid */
2809: -- Drop Index
2810: x_drop_index := 'drop index '||x_index_owner||'.'|| 'HZ_LOCATIONS_N15 force';
2811: EXECUTE IMMEDIATE x_drop_index;
2812: -- Create Index
2813: Create_Index;
2814: ELSE /* Index Exists and Valid */

Line 2828: l_rebuild_string := 'ALTER INDEX ' || x_index_owner ||'.' ||'HZ_LOCATIONS_N15 REBUILD ' ||

2824: retcode := '0';
2825: END IF;
2826:
2827: -- Bug 12916969 (Moved Rebuild Index script here from top)
2828: l_rebuild_string := 'ALTER INDEX ' || x_index_owner ||'.' ||'HZ_LOCATIONS_N15 REBUILD ' ||
2829: 'PARAMETERS(''sdo_commit_interval=' ||g_commit_interval || ''')';
2830:
2831: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2832: hz_utility_v2pub.debug(p_message=>'rebuilding with:' || l_rebuild_string,

Line 2933: l_exec_string := 'CREATE INDEX '||x_index_owner||'.'|| 'hz_locations_n15 ON '||x_index_owner||'.'||

2929: physical_tspace_name varchar2(100); --Bug 3299301
2930: BEGIN
2931: x_dummy := fnd_installation.GET_APP_INFO('AR',x_status,x_ind,x_index_owner);
2932: AD_TSPACE_UTIL.get_tablespace_name('AR','TRANSACTION_INDEXES','Y',check_tspace_exist,physical_tspace_name);
2933: l_exec_string := 'CREATE INDEX '||x_index_owner||'.'|| 'hz_locations_n15 ON '||x_index_owner||'.'||
2934: 'hz_locations(geometry) INDEXTYPE IS mdsys.spatial_index parameters(''TABLESPACE='||
2935: physical_tspace_name||''')'; --Bug 3299301
2936: -- create the index
2937: BEGIN

Line 2934: 'hz_locations(geometry) INDEXTYPE IS mdsys.spatial_index parameters(''TABLESPACE='||

2930: BEGIN
2931: x_dummy := fnd_installation.GET_APP_INFO('AR',x_status,x_ind,x_index_owner);
2932: AD_TSPACE_UTIL.get_tablespace_name('AR','TRANSACTION_INDEXES','Y',check_tspace_exist,physical_tspace_name);
2933: l_exec_string := 'CREATE INDEX '||x_index_owner||'.'|| 'hz_locations_n15 ON '||x_index_owner||'.'||
2934: 'hz_locations(geometry) INDEXTYPE IS mdsys.spatial_index parameters(''TABLESPACE='||
2935: physical_tspace_name||''')'; --Bug 3299301
2936: -- create the index
2937: BEGIN
2938: if(check_tspace_exist = 'Y') THEN --Bug 3299301