HZ_LOCATIONS
The HZ_LOCATIONS table stores information about a delivery or postal address such as building number, street address, postal code, and directions to a location. This table provides physical location information about parties (organizations and people) and customer accounts. For example, this table store a physical location such as Building 300, 500 Oracle Parkway, CA, US 94065. Records in the HZ_LOCATIONS table can store delivery and postal information about a location through columns such as the LOCATION_DIRECTIONS, POST_OFFICE and TIME_ZONE columns. One can also use the HZ_LOCATIONS table to store latitude and longitude information. Data in the HZ_LOCATIONS table is also used to determine the appropriate tax rates for sales tax and VAT calculations.
Details
-
Schema: FUSION
-
Object owner: HZ
-
Object type: TABLE
-
Tablespace: APPS_TS_TX_DATA
Primary Key
| Name | Columns |
|---|---|
|
HZ_LOCATIONS_PK |
LOCATION_ID |
Columns
| Name | Datatype | Length | Precision | Not-null | Comments | Flexfield-mapping | Status |
|---|---|---|---|---|---|---|---|
| LOCATION_ID | NUMBER | 18 | Yes | Unique identifier for this location | |||
| TIMEZONE_CODE | VARCHAR2 | 64 | Foreign key to FND_TIMEZONES_B.TIMEZONE_CODE | ||||
| LAST_UPDATE_DATE | TIMESTAMP | Yes | Who column: indicates the date and time of the last update of the row. | ||||
| LAST_UPDATED_BY | VARCHAR2 | 64 | Yes | Who column: indicates the user who last updated the row. | |||
| CREATION_DATE | TIMESTAMP | Yes | Who column: indicates the date and time of the creation of the row. | Active | |||
| CREATED_BY | VARCHAR2 | 64 | Yes | Who column: indicates the user who created the row. | Active | ||
| LAST_UPDATE_LOGIN | VARCHAR2 | 32 | Who column: indicates the session login associated to the user who last updated the row. | ||||
| REQUEST_ID | NUMBER | 18 | Enterprise Service Scheduler: indicates the request ID of the job that created or last updated the row. | Active | |||
| JOB_DEFINITION_NAME | VARCHAR2 | 100 | Enterprise Service Scheduler: indicates the name of the job that created or last updated the row. | ||||
| JOB_DEFINITION_PACKAGE | VARCHAR2 | 900 | Enterprise Service Scheduler: indicates the package name of the job that created or last updated the row. | ||||
| ATTRIBUTE_CATEGORY | VARCHAR2 | 30 | Descriptive Flexfield: structure definition of the user descriptive flexfield. | Location (HZ_LOCATIONS) | Active | ||
| ATTRIBUTE1 | VARCHAR2 | 150 | Descriptive Flexfield: segment of the user descriptive flexfield. | Location (HZ_LOCATIONS) | Active | ||
| ATTRIBUTE2 | VARCHAR2 | 150 | Descriptive Flexfield: segment of the user descriptive flexfield. | Location (HZ_LOCATIONS) | Active | ||
| ATTRIBUTE3 | VARCHAR2 | 150 | Descriptive Flexfield: segment of the user descriptive flexfield. | Location (HZ_LOCATIONS) | Active | ||
| ATTRIBUTE4 | VARCHAR2 | 150 | Descriptive Flexfield: segment of the user descriptive flexfield. | Location (HZ_LOCATIONS) | Active | ||
| ATTRIBUTE5 | VARCHAR2 | 150 | Descriptive Flexfield: segment of the user descriptive flexfield. | Location (HZ_LOCATIONS) | Active | ||
| ATTRIBUTE6 | VARCHAR2 | 150 | Descriptive Flexfield: segment of the user descriptive flexfield. | Location (HZ_LOCATIONS) | Active | ||
| ATTRIBUTE7 | VARCHAR2 | 150 | Descriptive Flexfield: segment of the user descriptive flexfield. | Location (HZ_LOCATIONS) | Active | ||
| ATTRIBUTE8 | VARCHAR2 | 150 | Descriptive Flexfield: segment of the user descriptive flexfield. | Location (HZ_LOCATIONS) | Active | ||
| ATTRIBUTE9 | VARCHAR2 | 150 | Descriptive Flexfield: segment of the user descriptive flexfield. | Location (HZ_LOCATIONS) | Active | ||
| ATTRIBUTE10 | VARCHAR2 | 150 | Descriptive Flexfield: segment of the user descriptive flexfield. | Location (HZ_LOCATIONS) | Active | ||
| ATTRIBUTE11 | VARCHAR2 | 150 | Descriptive Flexfield: segment of the user descriptive flexfield. | Location (HZ_LOCATIONS) | Active | ||
| ATTRIBUTE12 | VARCHAR2 | 150 | Descriptive Flexfield: segment of the user descriptive flexfield. | Location (HZ_LOCATIONS) | Active | ||
| ATTRIBUTE13 | VARCHAR2 | 150 | Descriptive Flexfield: segment of the user descriptive flexfield. | Location (HZ_LOCATIONS) | Active | ||
| ATTRIBUTE14 | VARCHAR2 | 150 | Descriptive Flexfield: segment of the user descriptive flexfield. | Location (HZ_LOCATIONS) | Active | ||
| ATTRIBUTE15 | VARCHAR2 | 150 | Descriptive Flexfield: segment of the user descriptive flexfield. | Location (HZ_LOCATIONS) | Active | ||
| ATTRIBUTE16 | VARCHAR2 | 150 | Descriptive Flexfield: segment of the user descriptive flexfield. | Location (HZ_LOCATIONS) | Active | ||
| ATTRIBUTE17 | VARCHAR2 | 150 | Descriptive Flexfield: segment of the user descriptive flexfield. | Location (HZ_LOCATIONS) | Active | ||
| ATTRIBUTE18 | VARCHAR2 | 150 | Descriptive Flexfield: segment of the user descriptive flexfield. | Location (HZ_LOCATIONS) | Active | ||
| ATTRIBUTE19 | VARCHAR2 | 150 | Descriptive Flexfield: segment of the user descriptive flexfield. | Location (HZ_LOCATIONS) | Active | ||
| ATTRIBUTE20 | VARCHAR2 | 150 | Descriptive Flexfield: segment of the user descriptive flexfield. | Location (HZ_LOCATIONS) | Active | ||
| ORIG_SYSTEM_REFERENCE | VARCHAR2 | 240 | Yes | Address identifier from foreign system | Active | ||
| COUNTRY | VARCHAR2 | 2 | Yes | Country code from the TERRITORY_CODE column in the FND_TERRITORY table | Active | ||
| ADDRESS1 | VARCHAR2 | 240 | First line for address | Active | |||
| ADDRESS2 | VARCHAR2 | 240 | Second line for address | Active | |||
| ADDRESS3 | VARCHAR2 | 240 | Third line for address | Active | |||
| ADDRESS4 | VARCHAR2 | 240 | Fourth line for address | Active | |||
| CITY | VARCHAR2 | 60 | City | Active | |||
| POSTAL_CODE | VARCHAR2 | 60 | Postal code | ||||
| STATE | VARCHAR2 | 60 | State | Active | |||
| PROVINCE | VARCHAR2 | 60 | Province | ||||
| COUNTY | VARCHAR2 | 60 | County | Active | |||
| ADDRESS_STYLE | VARCHAR2 | 30 | Used as context value for Flexible Address Format descriptive flexfield. Do not use this column, join to the FND_TERRITORIES table via COUNTRY = TERRITORY_CODE to retrieve the address style for the country. | Active | |||
| ADDRESS_LINES_PHONETIC | VARCHAR2 | 560 | Phonetic or Kana representation of the Kanji address lines (used in Japan) | Active | |||
| POSTAL_PLUS4_CODE | VARCHAR2 | 20 | Four digit extension to the United States Postal ZIP code. | ||||
| POSITION | VARCHAR2 | 50 | The primary direction such as North or East that is used to access the location. | ||||
| LOCATION_DIRECTIONS | VARCHAR2 | 640 | Directions to the location | ||||
| ADDRESS_EFFECTIVE_DATE | DATE | Date when the location is valid. | Active | ||||
| ADDRESS_EXPIRATION_DATE | DATE | Date when the location is no longer valid | Active | ||||
| CLLI_CODE | VARCHAR2 | 60 | Common Language Location Identifier (CLLI) code | Active | |||
| SHORT_DESCRIPTION | VARCHAR2 | 240 | Short description of the location | Active | |||
| DESCRIPTION | VARCHAR2 | 2000 | An extensive description of the location | Active | |||
| VALIDATED_FLAG | VARCHAR2 | 1 | Indicates if the location was validated. Y for validated, N for unvalidated. | Active | |||
| SALES_TAX_GEOCODE | VARCHAR2 | 30 | US Sales Tax Jurisdiction code. Use this field to provide a Jurisdiction Code (also called as Geocode) defined by wither Vertex or Taxware. This value is passed as a ship-to locaiton jurisdiction code to the tax partner API. | Active | |||
| SALES_TAX_INSIDE_CITY_LIMITS | VARCHAR2 | 1 | Indicates if the location is inside the boundary of a city. Used to calculate state and local taxes in the United States. Value '1' is for locations inside the city limits and '0' is for locations outside the city limits. Defaults to null if '1'. | ||||
| FA_LOCATION_ID | NUMBER | 18 | Foreign key to the FA_LOCATIONS table | Active | |||
| OBJECT_VERSION_NUMBER | NUMBER | 9 | Yes | Used to implement optimistic locking. This number is incremented every time that the row is updated. The number is compared at the start and end of a transaction to detect whether another session has updated the row since it was queried. | Active | ||
| CREATED_BY_MODULE | VARCHAR2 | 30 | TCA Who column | Active | |||
| TIMEZONE_ID | NUMBER | This column is obsolete in fusion V1 | |||||
| GEOMETRY_STATUS_CODE | VARCHAR2 | 30 | Spatial data integration status for given location. Not Used in Fusion V1. | Active | |||
| ACTUAL_CONTENT_SOURCE | VARCHAR2 | 30 | Additional Content Source Type column for backward compatibility. | Active | |||
| VALIDATION_STATUS_CODE | VARCHAR2 | 30 | Standardized status code describing the results of the validation | Active | |||
| DATE_VALIDATED | DATE | Date the address was last validated. | Active | ||||
| DO_NOT_VALIDATE_FLAG | VARCHAR2 | 1 | A new attribute that indicates that a particular location is not eligible for address validatino regardless of the current Validation Status Code. The default is "No" meaning that by default, all address are eligible for validation. This attribute is not unset on update. | Active | |||
| COMMENTS | VARCHAR2 | 2000 | Description for a Location. | Active | |||
| CONFLICT_ID | NUMBER | 18 | Yes | Disconnected Mobile: this value is used to guarantee the uniqueness of the row when duplicates are created in different databases (i.e. mobile databases or the server). | Active | ||
| USER_LAST_UPDATE_DATE | TIMESTAMP | Disconnected Mobile: indicates the date and time of the last update of the row. This value is different from LAST_UPDATE_DATE if the update originally happened in a different database (i.e. a different mobile database or the server). | Active | ||||
| ATTRIBUTE21 | VARCHAR2 | 150 | Descriptive Flexfield: segment of the user descriptive flexfield. | Location (HZ_LOCATIONS) | Active | ||
| ATTRIBUTE22 | VARCHAR2 | 150 | Descriptive Flexfield: segment of the user descriptive flexfield. | Location (HZ_LOCATIONS) | Active | ||
| ATTRIBUTE23 | VARCHAR2 | 150 | Descriptive Flexfield: segment of the user descriptive flexfield. | Location (HZ_LOCATIONS) | Active | ||
| ATTRIBUTE24 | VARCHAR2 | 150 | Descriptive Flexfield: segment of the user descriptive flexfield. | Location (HZ_LOCATIONS) | Active | ||
| ATTRIBUTE25 | VARCHAR2 | 150 | Descriptive Flexfield: segment of the user descriptive flexfield. | Location (HZ_LOCATIONS) | Active | ||
| ATTRIBUTE26 | VARCHAR2 | 150 | Descriptive Flexfield: segment of the user descriptive flexfield. | Location (HZ_LOCATIONS) | Active | ||
| ATTRIBUTE27 | VARCHAR2 | 150 | Descriptive Flexfield: segment of the user descriptive flexfield. | Location (HZ_LOCATIONS) | Active | ||
| ATTRIBUTE28 | VARCHAR2 | 150 | Descriptive Flexfield: segment of the user descriptive flexfield. | Location (HZ_LOCATIONS) | Active | ||
| ATTRIBUTE29 | VARCHAR2 | 150 | Descriptive Flexfield: segment of the user descriptive flexfield. | Location (HZ_LOCATIONS) | Active | ||
| ATTRIBUTE30 | VARCHAR2 | 255 | Descriptive Flexfield: segment of the user descriptive flexfield. | Location (HZ_LOCATIONS) | Active | ||
| HOUSE_TYPE | VARCHAR2 | 30 | A new Attribute to Indicate the Building Type | Active | |||
| CPDRF_VER_SOR | NUMBER | 9 | Cross Pillar Data Replication Framework (CPDRF) system column for tracking record version in System of Record (SOR) pillar when a record change is replicated to subscribing pillars by the replication flows. | ||||
| CPDRF_VER_PILLAR | NUMBER | 9 | Cross Pillar Data Replication Framework (CPDRF) system column for tracking record version when a record is changed by replication flows | ||||
| CPDRF_LAST_UPD | VARCHAR2 | 15 | Cross Pillar Data Replication Framework (CPDRF) system column for tracking source pillar name when a record is changed by the replication flows. | ||||
| ATTRIBUTE_NUMBER1 | NUMBER | Descriptive Flexfield: segment of the user descriptive flexfield. | Location (HZ_LOCATIONS) | ||||
| ATTRIBUTE_NUMBER2 | NUMBER | Descriptive Flexfield: segment of the user descriptive flexfield. | Location (HZ_LOCATIONS) | ||||
| ATTRIBUTE_NUMBER3 | NUMBER | Descriptive Flexfield: segment of the user descriptive flexfield. | Location (HZ_LOCATIONS) | ||||
| ATTRIBUTE_NUMBER4 | NUMBER | Descriptive Flexfield: segment of the user descriptive flexfield. | Location (HZ_LOCATIONS) | ||||
| ATTRIBUTE_NUMBER5 | NUMBER | Descriptive Flexfield: segment of the user descriptive flexfield. | Location (HZ_LOCATIONS) | ||||
| ATTRIBUTE_NUMBER6 | NUMBER | Descriptive Flexfield: segment of the user descriptive flexfield. | Location (HZ_LOCATIONS) | ||||
| ATTRIBUTE_NUMBER7 | NUMBER | Descriptive Flexfield: segment of the user descriptive flexfield. | Location (HZ_LOCATIONS) | ||||
| ATTRIBUTE_NUMBER8 | NUMBER | Descriptive Flexfield: segment of the user descriptive flexfield. | Location (HZ_LOCATIONS) | ||||
| ATTRIBUTE_NUMBER9 | NUMBER | Descriptive Flexfield: segment of the user descriptive flexfield. | Location (HZ_LOCATIONS) | ||||
| ATTRIBUTE_NUMBER10 | NUMBER | Descriptive Flexfield: segment of the user descriptive flexfield. | Location (HZ_LOCATIONS) | ||||
| ATTRIBUTE_NUMBER11 | NUMBER | Descriptive Flexfield: segment of the user descriptive flexfield. | Location (HZ_LOCATIONS) | ||||
| ATTRIBUTE_NUMBER12 | NUMBER | Descriptive Flexfield: segment of the user descriptive flexfield. | Location (HZ_LOCATIONS) | ||||
| ATTRIBUTE_DATE1 | DATE | Descriptive Flexfield: segment of the user descriptive flexfield. | Location (HZ_LOCATIONS) | ||||
| ATTRIBUTE_DATE2 | DATE | Descriptive Flexfield: segment of the user descriptive flexfield. | Location (HZ_LOCATIONS) | ||||
| ATTRIBUTE_DATE3 | DATE | Descriptive Flexfield: segment of the user descriptive flexfield. | Location (HZ_LOCATIONS) | ||||
| ATTRIBUTE_DATE4 | DATE | Descriptive Flexfield: segment of the user descriptive flexfield. | Location (HZ_LOCATIONS) | ||||
| ATTRIBUTE_DATE5 | DATE | Descriptive Flexfield: segment of the user descriptive flexfield. | Location (HZ_LOCATIONS) | ||||
| ATTRIBUTE_DATE6 | DATE | Descriptive Flexfield: segment of the user descriptive flexfield. | Location (HZ_LOCATIONS) | ||||
| ATTRIBUTE_DATE7 | DATE | Descriptive Flexfield: segment of the user descriptive flexfield. | Location (HZ_LOCATIONS) | ||||
| ATTRIBUTE_DATE8 | DATE | Descriptive Flexfield: segment of the user descriptive flexfield. | Location (HZ_LOCATIONS) | ||||
| ATTRIBUTE_DATE9 | DATE | Descriptive Flexfield: segment of the user descriptive flexfield. | Location (HZ_LOCATIONS) | ||||
| ATTRIBUTE_DATE10 | DATE | Descriptive Flexfield: segment of the user descriptive flexfield. | Location (HZ_LOCATIONS) | ||||
| ATTRIBUTE_DATE11 | DATE | Descriptive Flexfield: segment of the user descriptive flexfield. | Location (HZ_LOCATIONS) | ||||
| ATTRIBUTE_DATE12 | DATE | Descriptive Flexfield: segment of the user descriptive flexfield. | Location (HZ_LOCATIONS) | ||||
| LOCATION_LANGUAGE | VARCHAR2 | 4 | Operating Language for the Locations | ||||
| STATUS_FLAG | VARCHAR2 | 1 | Yes | Record Status Flag used to soft delete entity. Possible values are 'A' (active) and 'I' (inactive). | |||
| BUILDING | VARCHAR2 | 240 | Specific building name or number at a given address | ||||
| FLOOR_NUMBER | VARCHAR2 | 40 | Specific floor number at a given address or in a particular building when building number is provided | ||||
| ADDR_ELEMENT_ATTRIBUTE1 | VARCHAR2 | 150 | Additional address element to support flexible address format | ||||
| ADDR_ELEMENT_ATTRIBUTE2 | VARCHAR2 | 150 | Additional address element to support flexible address format | ||||
| ADDR_ELEMENT_ATTRIBUTE3 | VARCHAR2 | 150 | Additional address element to support flexible address format | ||||
| ADDR_ELEMENT_ATTRIBUTE4 | VARCHAR2 | 150 | Additional address element to support flexible address format | ||||
| ADDR_ELEMENT_ATTRIBUTE5 | VARCHAR2 | 150 | Additional address element to support flexible address format | ||||
| INTERNAL_FLAG | VARCHAR2 | 1 | This is set to Y for identifying internal/legal location. Possible values are Y and N. Default is N. | ||||
| LONGITUDE | NUMBER | Used to store Longitude Information for the Location for Spatial Proximity and containment purposes | |||||
| LATITUDE | NUMBER | Used to store Latitude Information for the Location for Spatial Proximity and containment purposes | |||||
| PERSONAL_ADDRESS_FLAG | VARCHAR2 | 1 | Indicates if this is the personal address of the contact. |
Foreign Keys
| Table | Foreign Table | Foreign Key Column |
|---|---|---|
| svc_work_orders | hz_locations | ADDRESS_ID |
| her_location_association | hz_locations | LOCATION_ID |
| her_org_location_extension | hz_locations | LOCATION_ID |
| iby_creditcard | hz_locations | UPGRADE_ADDRESSID |
| zx_lines_det_factors | hz_locations | SHIP_TO_LOCATION_ID |
| zx_lines_det_factors | hz_locations | SHIP_FROM_LOCATION_ID |
| zx_lines_det_factors | hz_locations | BILL_TO_LOCATION_ID |
| zx_lines_det_factors | hz_locations | BILL_FROM_LOCATION_ID |
| ar_remit_to_locs_all | hz_locations | LOCATION_ID |
| hz_location_profiles | hz_locations | LOCATION_ID |
| her_building_b | hz_locations | LOCATION_ID |
| her_curric_schedule_b | hz_locations | LOCATION_ID |
| hz_party_sites | hz_locations | LOCATION_ID |
| hz_location_locales | hz_locations | LOCATION_ID |
Indexes
| Index | Uniqueness | Tablespace | Columns |
|---|---|---|---|
| HZ_LOCATIONS_N1 | Non Unique | Default | ADDRESS1 |
| HZ_LOCATIONS_N10 | Non Unique | Default | DATE_VALIDATED |
| HZ_LOCATIONS_N11 | Non Unique | Default | INTERNAL_FLAG |
| HZ_LOCATIONS_N12 | Non Unique | Default | UPPER("ADDRESS1") |
| HZ_LOCATIONS_N13 | Non Unique | Default | "LAST_UPDATE_DATE", LOCATION_ID, STATUS_FLAG |
| HZ_LOCATIONS_N14 | Non Unique | Default | LONGITUDE, LATITUDE |
| HZ_LOCATIONS_N15 | Non Unique | Default | UPPER(CASE WHEN NVL("PERSONAL_ADDRESS_FLAG",'N')<>'Y' THEN "STATE" ELSE NULL END ), CASE WHEN NVL("PERSONAL_ADDRESS_FLAG",'N')<>'Y' THEN "COUNTRY" ELSE NULL END |
| HZ_LOCATIONS_N16 | Non Unique | Default | UPPER(CASE WHEN NVL("PERSONAL_ADDRESS_FLAG",'N')<>'Y' THEN "CITY" ELSE NULL END ), CASE WHEN NVL("PERSONAL_ADDRESS_FLAG",'N')<>'Y' THEN "COUNTRY" ELSE NULL END |
| HZ_LOCATIONS_N17 | Non Unique | Default | UPPER(CASE WHEN NVL("PERSONAL_ADDRESS_FLAG",'N')<>'Y' THEN "POSTAL_CODE" ELSE NULL END ), CASE WHEN NVL("PERSONAL_ADDRESS_FLAG",'N')<>'Y' THEN "COUNTRY" ELSE NULL END |
| HZ_LOCATIONS_N18 | Non Unique | Default | UPPER(CASE WHEN NVL("PERSONAL_ADDRESS_FLAG",'N')<>'Y' THEN "ADDRESS1" ELSE NULL END ) |
| HZ_LOCATIONS_N19 | Non Unique | Default | REQUEST_ID |
| HZ_LOCATIONS_N2 | Non Unique | Default | UPPER("STATE"), COUNTRY |
| HZ_LOCATIONS_N3 | Non Unique | Default | UPPER("CITY"), COUNTRY |
| HZ_LOCATIONS_N4 | Non Unique | Default | UPPER("POSTAL_CODE"), COUNTRY |
| HZ_LOCATIONS_N5 | Non Unique | Default | COUNTRY, STATE |
| HZ_LOCATIONS_N6 | Non Unique | Default | CLLI_CODE |
| HZ_LOCATIONS_N7 | Non Unique | Default | ORIG_SYSTEM_REFERENCE |
| HZ_LOCATIONS_N8 | Non Unique | Default | GEOMETRY_STATUS_CODE |
| HZ_LOCATIONS_N9 | Non Unique | Default | VALIDATION_STATUS_CODE |
| HZ_LOCATIONS_PK | Unique | Default | LOCATION_ID |