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 301: TYPE locationlist IS TABLE OF hz_locations.location_id%TYPE;

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

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

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

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

299:
300: ) IS
301: TYPE locationlist IS TABLE OF hz_locations.location_id%TYPE;
302: TYPE addresslist IS TABLE OF hz_locations.address1%TYPE;
303: TYPE citylist IS TABLE OF hz_locations.city%TYPE;
304: TYPE pcodelist IS TABLE OF hz_locations.postal_code%TYPE;
305: TYPE statelist IS TABLE OF hz_locations.state%TYPE;
306: TYPE countrylist IS TABLE OF hz_locations.country%TYPE;
307: TYPE countylist IS TABLE OF hz_locations.county%TYPE;

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

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

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

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

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

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

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

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

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

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

Line 328: FROM hz_locations hl

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

Line 346: FROM hz_locations hl

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

Line 364: FROM hz_locations hl

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

Line 382: FROM hz_locations hl

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

Line 401: FROM hz_locations hl

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

Line 415: FROM hz_locations hl

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

Line 429: FROM hz_locations hl

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

Line 444: FROM hz_locations hl

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

Line 459: FROM hz_locations hl

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

Line 471: FROM hz_locations hl

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

Line 484: FROM hz_locations hl

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

Line 497: FROM hz_locations hl

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

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

1758:
1759: --------------------------------------
1760: -- PUBLIC PROCEDURE rebuild_location_index
1761: -- DESCRIPTION
1762: -- Rebuilds the spatial index on HZ_LOCATIONS.GEOMETRY. Rebuilding the
1763: -- spatial index is required so that the index performs adequately and
1764: -- that queries can accurately extract the spatial data.
1765: -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1766: -- hz_utility_v2pub

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

1787: -- function at exception block.
1788: -- 11-NOV-2008 Nishant Singhai Bug 7262437 : changed insert statement
1789: -- from view user_sdo_geom_metadata to
1790: -- table MDSYS.SDO_GEOM_METADATA_TABLE
1791: -- in insert proper table owner for HZ_LOCATIONS
1792: -- when setup data is created from conc. program.
1793: --------------------------------------
1794: PROCEDURE rebuild_location_index (
1795: errbuf OUT NOCOPY VARCHAR2,

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

1802: g_commit_interval || ''')';
1803: CURSOR c_index (x_index_owner IN VARCHAR2) IS
1804: SELECT status, domidx_opstatus
1805: FROM sys.all_indexes
1806: WHERE index_name = 'HZ_LOCATIONS_N15' and owner = x_index_owner;
1807: /* Fix perf bug 4956727
1808: CURSOR c_policies IS
1809: SELECT object_owner, object_name, policy_name
1810: FROM sys.all_policies

Line 1812: AND object_name = 'HZ_LOCATIONS';

1808: CURSOR c_policies IS
1809: SELECT object_owner, object_name, policy_name
1810: FROM sys.all_policies
1811: WHERE enable LIKE 'Y%'
1812: AND object_name = 'HZ_LOCATIONS';
1813: */
1814: CURSOR c_policies(l_schema VARCHAR2) IS
1815: SELECT object_owner, object_name, policy_name
1816: FROM sys.dba_policies

Line 1819: AND object_name = 'HZ_LOCATIONS';

1815: SELECT object_owner, object_name, policy_name
1816: FROM sys.dba_policies
1817: WHERE enable = 'YES'
1818: AND object_owner = l_schema
1819: AND object_name = 'HZ_LOCATIONS';
1820:
1821: TYPE t_namelist IS TABLE OF VARCHAR2(30);
1822: l_owners t_namelist;
1823: l_objects t_namelist;

Line 1854: Where table_name = ''HZ_LOCATIONS''

1850: IF c_index%NOTFOUND THEN /* Index is Missing */
1851: -- Delete Meta Data
1852: -- Bug 7262437 : Changed deleting from view to directly from table
1853: /* x_del_meta := 'Delete user_sdo_geom_metadata
1854: Where table_name = ''HZ_LOCATIONS''
1855: And column_name= ''GEOMETRY''';
1856: */
1857: x_del_meta := 'Delete MDSYS.SDO_GEOM_METADATA_TABLE
1858: Where sdo_table_name = ''HZ_LOCATIONS''

Line 1858: Where sdo_table_name = ''HZ_LOCATIONS''

1854: Where table_name = ''HZ_LOCATIONS''
1855: And column_name= ''GEOMETRY''';
1856: */
1857: x_del_meta := 'Delete MDSYS.SDO_GEOM_METADATA_TABLE
1858: Where sdo_table_name = ''HZ_LOCATIONS''
1859: AND sdo_column_name= ''GEOMETRY''
1860: AND sdo_owner = '''||x_index_owner||'''';
1861: EXECUTE IMMEDIATE x_del_meta;
1862: -- Create Meta Data

Line 1867: ''HZ_LOCATIONS'', ''GEOMETRY'',

1863: -- Bug 7262437 : Changed inserting directly into table insteda of view
1864: -- to avoid default user name (APPS) getting inserted in sdo_owner column
1865: /* x_ins_meta := 'INSERT INTO user_sdo_geom_metadata (
1866: table_name, column_name, diminfo, srid ) VALUES (
1867: ''HZ_LOCATIONS'', ''GEOMETRY'',
1868: mdsys.sdo_dim_array(
1869: mdsys.sdo_dim_element(''longitude'', -180, 180, 0.00005),
1870: mdsys.sdo_dim_element(''latitude'', -90, 90, 0.00005)), 8307 )';
1871: */

Line 1875: '''HZ_LOCATIONS'', ''GEOMETRY'',

1871: */
1872: x_ins_meta := 'INSERT INTO MDSYS.SDO_GEOM_METADATA_TABLE (
1873: sdo_owner, sdo_table_name, sdo_column_name, sdo_diminfo, sdo_srid ) VALUES ('''
1874: ||x_index_owner||''', '||
1875: '''HZ_LOCATIONS'', ''GEOMETRY'',
1876: mdsys.sdo_dim_array(
1877: mdsys.sdo_dim_element(''longitude'', -180, 180, 0.00005),
1878: mdsys.sdo_dim_element(''latitude'', -90, 90, 0.00005)), 8307 )';
1879:

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

1883: ELSIF c_index%FOUND THEN
1884: IF l_status <> 'VALID' OR l_domidx_opstatus <> 'VALID' THEN
1885: /* Index Is Invalid */
1886: -- Drop Index
1887: x_drop_index := 'drop index '||x_index_owner||'.'|| 'HZ_LOCATIONS_N15 force';
1888: EXECUTE IMMEDIATE x_drop_index;
1889: -- Create Index
1890: Create_Index;
1891: ELSE /* Index Exists and Valid */

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

2001: physical_tspace_name varchar2(100); --Bug 3299301
2002: BEGIN
2003: x_dummy := fnd_installation.GET_APP_INFO('AR',x_status,x_ind,x_index_owner);
2004: AD_TSPACE_UTIL.get_tablespace_name('AR','TRANSACTION_INDEXES','Y',check_tspace_exist,physical_tspace_name);
2005: l_exec_string := 'CREATE INDEX '||x_index_owner||'.'|| 'hz_locations_n15 ON '||x_index_owner||'.'||
2006: 'hz_locations(geometry) INDEXTYPE IS mdsys.spatial_index parameters(''TABLESPACE='||
2007: physical_tspace_name||''')'; --Bug 3299301
2008: -- create the index
2009: BEGIN

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

2002: BEGIN
2003: x_dummy := fnd_installation.GET_APP_INFO('AR',x_status,x_ind,x_index_owner);
2004: AD_TSPACE_UTIL.get_tablespace_name('AR','TRANSACTION_INDEXES','Y',check_tspace_exist,physical_tspace_name);
2005: l_exec_string := 'CREATE INDEX '||x_index_owner||'.'|| 'hz_locations_n15 ON '||x_index_owner||'.'||
2006: 'hz_locations(geometry) INDEXTYPE IS mdsys.spatial_index parameters(''TABLESPACE='||
2007: physical_tspace_name||''')'; --Bug 3299301
2008: -- create the index
2009: BEGIN
2010: if(check_tspace_exist = 'Y') THEN --Bug 3299301