View: AST_LM_ORG_SMARTSEARCH_V

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

View Text

SELECT PARTY_NAME
, HZ.PARTY_ID
, HZ.PARTY_NUMBER
, ARP_ADDR_LABEL_PKG.FORMAT_ADDRESS_LABEL ( NULL
, NVL(LOC.ADDRESS1
, HZ.ADDRESS1)
, NVL(LOC.ADDRESS2
, HZ.ADDRESS2)
, NVL(LOC.ADDRESS3
, HZ.ADDRESS3)
, NVL(LOC.ADDRESS4
, HZ.ADDRESS4)
, NVL(LOC.CITY
, HZ.CITY)
, NVL(LOC.COUNTY
, HZ.COUNTY)
, NVL(LOC.STATE
, HZ.STATE)
, NVL(LOC.PROVINCE
, HZ.PROVINCE)
, NVL(LOC.POSTAL_CODE
, HZ.POSTAL_CODE)
, NVL(LOC.COUNTRY
, HZ.COUNTRY)
, NVL(LOC.COUNTRY_NAME
, FTT.TERRITORY_SHORT_NAME)
, NULL
, NULL
, NULL
, NULL
, NULL
, FND_PROFILE.VALUE('AST_DEFAULT_COUNTRY')
, NULL
, NULL
, 2000
, 1
, 1 )
, LOC.ADDRESS1||' '||LOC.ADDRESS2||' '||LOC.ADDRESS3||' '||LOC.ADDRESS4 ADDRESS
, NVL(LOC.CITY
, HZ.CITY)
, NVL(LOC.COUNTY
, HZ.COUNTY)
, NVL(LOC.STATE
, HZ.STATE)
, NVL(LOC.POSTAL_CODE
, HZ.POSTAL_CODE)
, NVL(LOC.PROVINCE
, HZ.PROVINCE)
, NVL(LOC.COUNTRY_NAME
, FTT.TERRITORY_SHORT_NAME)
, SMARTPARTY.SCORE SCORE
, NVL(CPTS.EMAIL_ADDRESS
, HZ.EMAIL_ADDRESS)
, NVL(NVL(CPTS.PHONE_COUNTRY_CODE
, PRIMPHONE.PHONE_COUNTRY_CODE)
, ' ')||'-'|| NVL(NVL(CPTS.PHONE_AREA_CODE
, PRIMPHONE.PHONE_AREA_CODE)
, ' ')||'-'|| NVL(CPTS.PHONE_NUMBER
, PRIMPHONE.PHONE_NUMBER) ||''|| NVL(CPTS.PHONE_EXTENSION
, PRIMPHONE.PHONE_EXTENSION) FULL_PHONE_NUMBER
, NVL(CPTS.PHONE_AREA_CODE
, PRIMPHONE.PHONE_AREA_CODE)|| NVL(CPTS.PHONE_NUMBER
, PRIMPHONE.PHONE_NUMBER) PHONE_AREA_NUMBER
, NVL(CPTS.PHONE_AREA_CODE
, PRIMPHONE.PHONE_AREA_CODE)
, NVL(CPTS.PHONE_COUNTRY_CODE
, PRIMPHONE.PHONE_COUNTRY_CODE)
, NVL(CPTS.PHONE_NUMBER
, PRIMPHONE.PHONE_NUMBER)
, NVL(CPTS.PHONE_EXTENSION
, PRIMPHONE.PHONE_EXTENSION)
, HZ.STATUS
, ALK1.MEANING STATUS_MEAN
, HZ.URL
, HZ.CERTIFICATION_LEVEL
, ALK2.MEANING CERT_LEVEL_MEAN
, NULL ACCOUNT_NUMBER
, TO_NUMBER(NULL) CUST_ACCOUNT_ID
, HZ.PARTY_TYPE
, SMARTPARTY.SEARCH_CONTEXT_ID
FROM HZ_PARTIES HZ
, HZ_CONTACT_POINTS PRIMPHONE
, HZ_MATCHED_PARTIES_GT SMARTPARTY
, (SELECT SEARCH_CONTEXT_ID
, C.PARTY_ID
, A.EMAIL_ADDRESS
, B.PHONE_AREA_CODE
, B.PHONE_COUNTRY_CODE
, B.PHONE_NUMBER
, B.PHONE_EXTENSION
, C.SCORE CPTS_SCORE
FROM HZ_CONTACT_POINTS A
, HZ_CONTACT_POINTS B
, HZ_MATCHED_CPTS_GT C
WHERE A.OWNER_TABLE_ID(+)=C.PARTY_ID
AND A.OWNER_TABLE_NAME(+)='HZ_PARTIES'
AND A.CONTACT_POINT_ID(+)=C.CONTACT_POINT_ID
AND A.CONTACT_POINT_TYPE='EMAIL'
AND B.OWNER_TABLE_ID(+)=C.PARTY_ID
AND B.OWNER_TABLE_NAME(+)='HZ_PARTIES'
AND B.CONTACT_POINT_ID(+)=C.CONTACT_POINT_ID
AND B.CONTACT_POINT_TYPE(+)='PHONE' AND B.STATUS(+)='A' AND A.STATUS IN ('A'
, 'I') ) CPTS
, (SELECT C.SEARCH_CONTEXT_ID
, C.PARTY_ID
, B.ADDRESS1
, B.ADDRESS2
, B.ADDRESS3
, B.ADDRESS4
, B.CITY
, B.COUNTY
, B.STATE
, B.PROVINCE
, B.POSTAL_CODE
, B.COUNTRY COUNTRY
, FTT.TERRITORY_SHORT_NAME COUNTRY_NAME
, C.SCORE SITES_SCORE
FROM HZ_PARTY_SITES A
, HZ_LOCATIONS B
, FND_TERRITORIES_TL FTT
, HZ_MATCHED_PARTY_SITES_GT C
WHERE A.PARTY_ID=C.PARTY_ID AND A.PARTY_SITE_ID=C.PARTY_SITE_ID
AND B.LOCATION_ID=A.LOCATION_ID
AND B.COUNTRY = FTT.TERRITORY_CODE(+)
AND FTT.LANGUAGE(+) = USERENV('LANG')
AND FTT.SOURCE_LANG(+) = USERENV('LANG') ) LOC
, FND_TERRITORIES_TL FTT
, AR_LOOKUPS ALK1
, AR_LOOKUPS ALK2
WHERE HZ.PARTY_ID=SMARTPARTY.PARTY_ID
AND HZ.PARTY_TYPE='ORGANIZATION'
AND CPTS.SEARCH_CONTEXT_ID(+)=SMARTPARTY.SEARCH_CONTEXT_ID
AND CPTS.PARTY_ID(+)= SMARTPARTY.PARTY_ID
AND PRIMPHONE.OWNER_TABLE_ID(+)=SMARTPARTY.PARTY_ID
AND PRIMPHONE.OWNER_TABLE_NAME(+)='HZ_PARTIES'
AND PRIMPHONE.CONTACT_POINT_TYPE(+)='PHONE'
AND PRIMPHONE.PRIMARY_FLAG(+)='Y'
AND LOC.SEARCH_CONTEXT_ID(+)=SMARTPARTY.SEARCH_CONTEXT_ID
AND LOC.PARTY_ID(+)=SMARTPARTY.PARTY_ID
AND HZ.COUNTRY = FTT.TERRITORY_CODE(+)
AND FTT.LANGUAGE(+) = USERENV('LANG')
AND FTT.SOURCE_LANG(+) = USERENV('LANG')
AND ALK1.LOOKUP_TYPE='REGISTRY_STATUS'
AND ALK1.LOOKUP_CODE=HZ.STATUS
AND ALK2.LOOKUP_TYPE(+)='HZ_PARTY_CERT_LEVEL'
AND ALK2.LOOKUP_CODE(+)=HZ.CERTIFICATION_LEVEL

Columns