View: AST_LM_REL_SMARTSEARCH_V

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

View Text

SELECT O.PARTY_NAME
, O.PARTY_ID
, O.PARTY_NUMBER
, 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
, R.PARTY_ID
, R.PARTY_NUMBER
, R.PARTY_NAME
, ALK1.MEANING
, ORGCNT.JOB_TITLE
, ALK.MEANING JOB_TITLE_CODE
, SMARTCONTACT.SCORE SCORE
, R.EMAIL_ADDRESS
, NVL(PRIMPHONE.PHONE_COUNTRY_CODE
, ' ')||'-'|| NVL(PRIMPHONE.PHONE_AREA_CODE
, ' ')||'-'|| PRIMPHONE.PHONE_NUMBER ||''||PRIMPHONE.PHONE_EXTENSION FULL_PHONE_NUMBER
, PRIMPHONE.PHONE_AREA_CODE||PRIMPHONE.PHONE_NUMBER PHONE_AREA_NUMBER
, PRIMPHONE.PHONE_AREA_CODE
, PRIMPHONE.PHONE_COUNTRY_CODE
, PRIMPHONE.PHONE_NUMBER
, PRIMPHONE.PHONE_EXTENSION
, R.URL
, ARP_ADDR_LABEL_PKG.FORMAT_ADDRESS_LABEL ( NULL
, R.ADDRESS1
, R.ADDRESS2
, R.ADDRESS3
, R.ADDRESS4
, R.CITY
, R.COUNTY
, R.STATE
, R.PROVINCE
, R.POSTAL_CODE
, R.COUNTRY
, FTT.TERRITORY_SHORT_NAME
, NULL
, NULL
, NULL
, NULL
, NULL
, FND_PROFILE.VALUE('AST_DEFAULT_COUNTRY')
, NULL
, NULL
, 2000
, 1
, 1 )
, R.ADDRESS1||' '||R.ADDRESS2||' '||R.ADDRESS3||' '||R.ADDRESS4 ADDRESS
, R.CITY
, R.COUNTY
, R.STATE
, R.POSTAL_CODE
, R.PROVINCE
, R.COUNTRY
, FTT.TERRITORY_SHORT_NAME
, REL.STATUS
, ALK2.MEANING STATUS_MEAN
, R.PARTY_TYPE
, O.CERTIFICATION_LEVEL
, ALK3.MEANING CERT_LEVEL_MEAN
, NULL ACCOUNT_NUMBER
, TO_NUMBER(NULL) CUST_ACCOUNT_ID
, SMARTCONTACT.SEARCH_CONTEXT_ID
FROM HZ_PARTIES O
, HZ_PARTIES P
, HZ_PARTIES R
, HZ_RELATIONSHIPS REL
, HZ_RELATIONSHIP_TYPES RELTYPE
, HZ_CONTACT_POINTS PRIMPHONE
, HZ_ORG_CONTACTS ORGCNT
, HZ_MATCHED_CONTACTS_GT SMARTCONTACT
, FND_TERRITORIES_TL FTT
, AR_LOOKUPS ALK
, AR_LOOKUPS ALK1
, AR_LOOKUPS ALK2
, AR_LOOKUPS ALK3
WHERE O.PARTY_ID=REL.OBJECT_ID
AND O.STATUS IN ('A'
, 'I')
AND O.PARTY_TYPE='ORGANIZATION'
AND P.PARTY_ID=REL.SUBJECT_ID
AND P.STATUS IN ('A'
, 'I')
AND P.PARTY_TYPE = 'PERSON'
AND REL.PARTY_ID=R.PARTY_ID
AND R.PARTY_TYPE='PARTY_RELATIONSHIP'
AND R.STATUS IN ('A'
, 'I') AND REL.SUBJECT_TYPE='PERSON'
AND REL.OBJECT_TYPE='ORGANIZATION'
AND RELTYPE.RELATIONSHIP_TYPE=REL.RELATIONSHIP_TYPE
AND REL.RELATIONSHIP_CODE=RELTYPE.FORWARD_REL_CODE AND RELTYPE.SUBJECT_TYPE=REL.SUBJECT_TYPE
AND RELTYPE.OBJECT_TYPE=REL.OBJECT_TYPE
AND SMARTCONTACT.ORG_CONTACT_ID=ORGCNT.ORG_CONTACT_ID
AND ORGCNT.PARTY_RELATIONSHIP_ID=REL.RELATIONSHIP_ID
AND SMARTCONTACT.PARTY_ID=REL.OBJECT_ID
AND ALK.LOOKUP_TYPE(+) = 'RESPONSIBILITY'
AND ALK.ENABLED_FLAG(+) = 'Y'
AND ALK.LOOKUP_CODE(+) = ORGCNT.JOB_TITLE_CODE
AND ALK1.LOOKUP_TYPE='HZ_RELATIONSHIP_ROLE'
AND ALK1.LOOKUP_CODE=RELTYPE.ROLE
AND ALK2.LOOKUP_TYPE='REGISTRY_STATUS'
AND ALK2.LOOKUP_CODE=REL.STATUS
AND PRIMPHONE.OWNER_TABLE_ID(+)=REL.PARTY_ID
AND PRIMPHONE.OWNER_TABLE_NAME(+)='HZ_PARTIES'
AND PRIMPHONE.CONTACT_POINT_TYPE(+)='PHONE'
AND PRIMPHONE.PRIMARY_FLAG(+)='Y'
AND ALK3.LOOKUP_TYPE(+)='HZ_PARTY_CERT_LEVEL'
AND ALK3.LOOKUP_CODE(+)=O.CERTIFICATION_LEVEL
AND R.COUNTRY = FTT.TERRITORY_CODE(+)
AND FTT.LANGUAGE(+) = USERENV('LANG')
AND FTT.SOURCE_LANG(+) = USERENV('LANG')

Columns