View: AST_LM_REL_EXPAND_V

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

View Text

SELECT DISTINCT O.PARTY_NAME
, SITE.PARTY_SITE_ID
, O.PARTY_ID
, O.PARTY_NUMBER
, NULL
, TO_NUMBER(NULL) CUST_ACCOUNT_ID
, R.PARTY_ID
, R.PARTY_NUMBER
, REL.STATUS
, O.SIC_CODE
, O.FISCAL_YEAREND_MONTH
, O.CURR_FY_POTENTIAL_REVENUE
, O.TAX_REFERENCE
, P.PARTY_ID
, P.PARTY_NAME
, P.PERSON_FIRST_NAME
, P.PERSON_MIDDLE_NAME
, P.PERSON_LAST_NAME
, P.PERSON_FIRST_NAME || ' ' || P.PERSON_LAST_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
, CPT2.EMAIL_ADDRESS
, CNT.JOB_TITLE
, ALK1.MEANING
, CNT.DEPARTMENT_CODE
, CNT.DEPARTMENT
, CPT.TELEPHONE_TYPE
, CPT.PHONE_LINE_TYPE
, CPT.PHONE_COUNTRY_CODE
, CPT.PHONE_AREA_CODE
, CPT.PHONE_NUMBER
, CPT.PHONE_EXTENSION
, NVL(CPT.PHONE_COUNTRY_CODE
, ' ')||'-'|| NVL(CPT.PHONE_AREA_CODE
, ' ')||'-'|| CPT.PHONE_NUMBER||' '|| CPT.PHONE_EXTENSION
, CPT.PHONE_AREA_CODE|| CPT.PHONE_NUMBER
, ALK.MEANING
, REL.RELATIONSHIP_ID
, R.ATTRIBUTE1
, R.ATTRIBUTE2
, R.ATTRIBUTE3
, R.ATTRIBUTE4
, R.ATTRIBUTE5
, R.ATTRIBUTE6
, R.ATTRIBUTE7
, R.ATTRIBUTE8
, R.ATTRIBUTE9
, R.ATTRIBUTE10
, R.ATTRIBUTE11
, R.ATTRIBUTE12
, R.ATTRIBUTE13
, R.ATTRIBUTE14
, R.ATTRIBUTE15
, R.ATTRIBUTE16
, R.ATTRIBUTE17
, R.ATTRIBUTE18
, R.ATTRIBUTE19
, R.ATTRIBUTE20
, CPT.PRIMARY_FLAG
, O.CERTIFICATION_LEVEL
, ALK2.MEANING CERT_LEVEL_MEAN
FROM HZ_RELATIONSHIPS REL
, HZ_PARTIES P
, HZ_PARTIES O
, HZ_PARTIES R
, HZ_CONTACT_POINTS CPT
, HZ_CONTACT_POINTS CPT2
, HZ_ORG_CONTACTS CNT
, AR_LOOKUPS ALK
, AR_LOOKUPS ALK1
, AR_LOOKUPS ALK2
, FND_TERRITORIES_TL FTT
, HZ_PARTY_SITES SITE
, HZ_LOCATIONS LOC
, HZ_TIMEZONES_TL TIME
, HZ_RELATIONSHIP_TYPES HZ
WHERE SITE.LOCATION_ID = LOC.LOCATION_ID(+)
AND TIME.TIMEZONE_ID(+) = LOC.TIMEZONE_ID
AND TIME.LANGUAGE(+) = USERENV('LANG')
AND R.PARTY_ID = SITE.PARTY_ID(+)
AND SITE.IDENTIFYING_ADDRESS_FLAG(+) = 'Y'
AND O.PARTY_TYPE = 'ORGANIZATION'
AND P.PARTY_TYPE = 'PERSON'
AND O.PARTY_ID = REL.OBJECT_ID
AND REL.OBJECT_TYPE = O.PARTY_TYPE
AND REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
AND REL.SUBJECT_ID = P.PARTY_ID
AND REL.SUBJECT_TYPE = P.PARTY_TYPE
AND REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND REL.RELATIONSHIP_ID = CNT.PARTY_RELATIONSHIP_ID
AND R.PARTY_ID = REL.PARTY_ID
AND R.PARTY_TYPE = 'PARTY_RELATIONSHIP'
AND CPT.OWNER_TABLE_NAME(+) = 'HZ_PARTIES'
AND CPT.OWNER_TABLE_ID(+) = REL.PARTY_ID
AND CPT.CONTACT_POINT_TYPE(+) = 'PHONE'
AND CPT2.OWNER_TABLE_NAME(+) = 'HZ_PARTIES'
AND CPT2.OWNER_TABLE_ID(+) = REL.PARTY_ID
AND CPT2.CONTACT_POINT_TYPE(+) = 'EMAIL'
AND ALK.LOOKUP_TYPE(+) = 'HZ_RELATIONSHIP_ROLE'
AND HZ.ROLE = ALK.LOOKUP_CODE(+)
AND REL.OBJECT_TYPE = HZ.OBJECT_TYPE
AND REL.SUBJECT_TYPE = HZ.SUBJECT_TYPE
AND REL.RELATIONSHIP_TYPE= HZ.RELATIONSHIP_TYPE
AND REL.RELATIONSHIP_CODE= HZ.FORWARD_REL_CODE
AND ALK.ENABLED_FLAG (+) = 'Y'
AND LOC.COUNTRY = FTT.TERRITORY_CODE(+)
AND FTT.LANGUAGE(+) = USERENV('LANG')
AND FTT.SOURCE_LANG(+) = USERENV('LANG')
AND ALK1.LOOKUP_TYPE(+) = 'RESPONSIBILITY'
AND ALK1.ENABLED_FLAG(+) = 'Y'
AND ALK1.LOOKUP_CODE(+) = CNT.JOB_TITLE_CODE
AND O.STATUS IN('A'
, 'I')
AND ALK2.LOOKUP_TYPE(+)='HZ_PARTY_CERT_LEVEL'
AND ALK2.LOOKUP_CODE(+)=O.CERTIFICATION_LEVEL

Columns