View: AST_LM_ORG_EXPAND_V

Product:	AST - TeleSales
Description:
Implementation/DBA Data:	APPS.AST_LM_ORG_EXPAND_V

View Text

SELECT PARTY.PARTY_ID
, SITE.PARTY_SITE_ID
, PARTY.PARTY_NUMBER
, ACCOUNT.ACCOUNT_NUMBER
, ACCOUNT.CUST_ACCOUNT_ID
, PARTY.PARTY_NAME
, LOC.ADDRESS1||' '|| LOC.ADDRESS2||' '|| LOC.ADDRESS3||' '|| LOC.ADDRESS4
, LOC.ADDRESS1
, LOC.ADDRESS2
, LOC.ADDRESS3
, LOC.ADDRESS4
, LOC.CITY
, LOC.COUNTY
, LOC.STATE
, LOC.PROVINCE
, LOC.POSTAL_CODE
, LOC.ADDRESS_LINES_PHONETIC
, LOC.ACTUAL_CONTENT_SOURCE
, LOC.ADDRESS_EFFECTIVE_DATE
, LOC.ADDRESS_ERROR_CODE
, LOC.ADDRESS_EXPIRATION_DATE
, LOC.ADDRESS_KEY
, LOC.ADDRESS_STYLE
, LOC.APARTMENT_FLAG
, LOC.APARTMENT_NUMBER
, LOC.APPLICATION_ID
, LOC.BUILDING
, LOC.CLLI_CODE
, LOC.CONTENT_SOURCE_TYPE
, LOC.COUNTRY
, LOC.CREATED_BY
, LOC.CREATED_BY_MODULE
, LOC.CREATION_DATE
, LOC.DELIVERY_POINT_CODE
, LOC.DESCRIPTION
, LOC.DODAAC
, LOC.FA_LOCATION_ID
, LOC.FLOOR
, LOC.GEOMETRY_STATUS_CODE
, LOC.GLOBAL_ATTRIBUTE_CATEGORY
, LOC.GLOBAL_ATTRIBUTE1
, LOC.GLOBAL_ATTRIBUTE10
, LOC.GLOBAL_ATTRIBUTE11
, LOC.GLOBAL_ATTRIBUTE12
, LOC.GLOBAL_ATTRIBUTE13
, LOC.GLOBAL_ATTRIBUTE14
, LOC.GLOBAL_ATTRIBUTE15
, LOC.GLOBAL_ATTRIBUTE16
, LOC.GLOBAL_ATTRIBUTE17
, LOC.GLOBAL_ATTRIBUTE18
, LOC.GLOBAL_ATTRIBUTE19
, LOC.GLOBAL_ATTRIBUTE2
, LOC.GLOBAL_ATTRIBUTE20
, LOC.GLOBAL_ATTRIBUTE3
, LOC.GLOBAL_ATTRIBUTE4
, LOC.GLOBAL_ATTRIBUTE5
, LOC.GLOBAL_ATTRIBUTE6
, LOC.GLOBAL_ATTRIBUTE7
, LOC.GLOBAL_ATTRIBUTE8
, LOC.GLOBAL_ATTRIBUTE9
, LOC.HOUSE_NUMBER
, LOC.LAST_UPDATE_DATE
, LOC.LAST_UPDATE_LOGIN
, LOC.LAST_UPDATED_BY
, LOC.LIFE_CYCLE_STATUS
, LOC.LOC_HIERARCHY_ID
, LOC.LOCATION_DIRECTIONS
, LOC.OBJECT_VERSION_NUMBER
, LOC.ORIG_SYSTEM_REFERENCE
, LOC.OVERSEAS_ADDRESS_FLAG
, LOC.PO_BOX_NUMBER
, LOC.POSITION
, LOC.POST_OFFICE
, LOC.POSTAL_PLUS4_CODE
, LOC.PROGRAM_APPLICATION_ID
, LOC.PROGRAM_ID
, LOC.PROGRAM_UPDATE_DATE
, LOC.REQUEST_ID
, LOC.ROOM
, LOC.RURAL_ROUTE_NUMBER
, LOC.RURAL_ROUTE_TYPE
, LOC.SALES_TAX_GEOCODE
, LOC.SALES_TAX_INSIDE_CITY_LIMITS
, LOC.SECONDARY_SUFFIX_ELEMENT
, LOC.SHORT_DESCRIPTION
, LOC.STREET
, LOC.STREET_NUMBER
, LOC.STREET_SUFFIX
, LOC.SUITE
, TIME.NAME TIME_ZONE
, LOC.TRAILING_DIRECTORY_CODE
, LOC.VALIDATED_FLAG
, FTT.TERRITORY_SHORT_NAME
, HCP2.EMAIL_ADDRESS
, CONT_POINT.PHONE_COUNTRY_CODE
, CONT_POINT.PHONE_AREA_CODE
, CONT_POINT.PHONE_NUMBER
, CONT_POINT.PHONE_EXTENSION
, NVL(CONT_POINT.PHONE_COUNTRY_CODE
, ' ')||'-'|| NVL(CONT_POINT.PHONE_AREA_CODE
, ' ')||'-'|| CONT_POINT.PHONE_NUMBER ||''|| CONT_POINT.PHONE_EXTENSION
, CONT_POINT.PHONE_AREA_CODE|| CONT_POINT.PHONE_NUMBER
, PARTY.STATUS
, ALK.MEANING
, PARTY.URL
, PARTY.EMPLOYEES_TOTAL
, ALK1.MEANING
, PARTY.JGZZ_FISCAL_CODE
, PARTY.CURR_FY_POTENTIAL_REVENUE
, PARTY.DUNS_NUMBER_C
, PARTY.FISCAL_YEAREND_MONTH
, PARTY.SIC_CODE
, PARTY.TAX_REFERENCE
, PARTY.ATTRIBUTE1
, PARTY.ATTRIBUTE2
, PARTY.ATTRIBUTE3
, PARTY.ATTRIBUTE4
, PARTY.ATTRIBUTE5
, PARTY.ATTRIBUTE6
, PARTY.ATTRIBUTE7
, PARTY.ATTRIBUTE8
, PARTY.ATTRIBUTE9
, PARTY.ATTRIBUTE10
, PARTY.ATTRIBUTE11
, PARTY.ATTRIBUTE12
, PARTY.ATTRIBUTE13
, PARTY.ATTRIBUTE14
, PARTY.ATTRIBUTE15
, PARTY.ATTRIBUTE16
, PARTY.ATTRIBUTE17
, PARTY.ATTRIBUTE18
, PARTY.ATTRIBUTE19
, PARTY.ATTRIBUTE20
, CONT_POINT.PRIMARY_FLAG
, PARTY.CERTIFICATION_LEVEL
, ALK2.MEANING CERT_LEVEL_MEAN
FROM HZ_PARTIES PARTY
, HZ_CONTACT_POINTS CONT_POINT
, HZ_CONTACT_POINTS HCP2
, FND_TERRITORIES_TL FTT
, AR_LOOKUPS ALK
, AR_LOOKUPS ALK1
, AR_LOOKUPS ALK2
, HZ_CUST_ACCOUNTS ACCOUNT
, HZ_PARTY_SITES SITE
, HZ_LOCATIONS LOC
, HZ_TIMEZONES_TL TIME
WHERE SITE.LOCATION_ID = LOC.LOCATION_ID(+)
AND TIME.TIMEZONE_ID(+) = LOC.TIMEZONE_ID
AND TIME.LANGUAGE(+) = USERENV('LANG')
AND PARTY.PARTY_ID = SITE.PARTY_ID(+)
AND SITE.IDENTIFYING_ADDRESS_FLAG(+) = 'Y'
AND PARTY.PARTY_TYPE = 'ORGANIZATION'
AND CONT_POINT.CONTACT_POINT_TYPE(+) = 'PHONE'
AND CONT_POINT.OWNER_TABLE_NAME(+) = 'HZ_PARTIES'
AND CONT_POINT.OWNER_TABLE_ID(+) = PARTY.PARTY_ID
AND CONT_POINT.STATUS(+) = 'A'
AND HCP2.CONTACT_POINT_TYPE(+) = 'EMAIL'
AND HCP2.OWNER_TABLE_NAME(+)= 'HZ_PARTIES'
AND HCP2.OWNER_TABLE_ID(+) = PARTY.PARTY_ID
AND LOC.COUNTRY= FTT.TERRITORY_CODE(+)
AND FTT.LANGUAGE(+) = USERENV('LANG')
AND FTT.SOURCE_LANG(+) = USERENV('LANG')
AND ALK.LOOKUP_TYPE (+) = 'CODE_STATUS'
AND ALK.LOOKUP_CODE(+) = PARTY.STATUS
AND ACCOUNT.PARTY_ID(+) = PARTY.PARTY_ID
AND ALK1.LOOKUP_TYPE (+) = 'CUSTOMER_CATEGORY'
AND ALK1.ENABLED_FLAG (+) = 'Y'
AND ALK1.LOOKUP_CODE (+) = PARTY.CATEGORY_CODE
AND PARTY.STATUS IN ('A'
, 'I')
AND ALK2.LOOKUP_TYPE (+) = 'HZ_PARTY_CERT_LEVEL'
AND ALK2.LOOKUP_CODE (+) = PARTY.CERTIFICATION_LEVEL

Columns