Product: | AMS - Marketing |
---|---|
Description: | |
Implementation/DBA Data: | Not implemented in this database |
SELECT HL.LOCATION_ID
, HL.CONTENT_SOURCE_TYPE
, HL.COUNTRY
, LANG. TERRITORY_SHORT_NAME COUNTRY_NAME
, HL.ADDRESS1
, HL.ADDRESS2
, HL.ADDRESS3
, HL.ADDRESS4
, HL.CITY
, HL.POSTAL_CODE
, HL.POSTAL_PLUS4_CODE
, HL.STATE
, HL.PROVINCE
, HL.COUNTY
, HL.LIFE_CYCLE_STATUS
, HL.VALIDATED_FLAG
, HL.TIME_ZONE
, HL.LANGUAGE
, HL.ADDRESS_EFFECTIVE_DATE
, HL.ADDRESS_EXPIRATION_DATE
, HL.ADDRESS_LINES_PHONETIC
, HL.APARTMENT_FLAG
, HL.APARTMENT_NUMBER
, HL.PO_BOX_NUMBER
, HL.HOUSE_NUMBER
, HL.STREET_SUFFIX
, HL.SECONDARY_SUFFIX_ELEMENT
, HL.STREET
, HL.STREET_NUMBER
, HL.BUILDING
, HL.FLOOR
, HL.SUITE
, HL.ROOM
, HL.POST_OFFICE
, HL.RURAL_ROUTE_TYPE
, HL.RURAL_ROUTE_NUMBER
, HL.OVERSEAS_ADDRESS_FLAG
, HL.POSITION
, HL.DELIVERY_POINT_CODE
, HL.LOCATION_DIRECTIONS
, HL.SALES_TAX_GEOCODE
, HL.SALES_TAX_INSIDE_CITY_LIMITS
, HL.CLLI_CODE
, HL.DODAAC
, HL.TRAILING_DIRECTORY_CODE
, HL.SHORT_DESCRIPTION
, HL.LOC_HIERARCHY_ID
FROM HZ_LOCATIONS HL
, HZ_PARTY_SITES HPS
, HZ_PARTIES HP
, FND_TERRITORIES_TL LANG
WHERE HL.LOCATION_ID = HPS.LOCATION_ID
AND HPS.STATUS = 'A'
AND HPS.PARTY_ID = HP.PARTY_ID
AND HPS.ACTUAL_CONTENT_SOURCE = 'USER_ENTERED'
AND HP.STATUS = 'A'
AND HP.PARTY_TYPE = 'PERSON'
AND LANG. LANGUAGE = USERENV('LANG')
AND LANG.TERRITORY_CODE = HL.COUNTRY