View: AST_ORG_CONTACTS_V

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

View Text

SELECT PARTY_REL.ROWID
, PARTY_REL.RELATIONSHIP_ID
, PARTY_REL.SUBJECT_ID
, PARTY_REL.OBJECT_ID
, PARTY_REL.PARTY_ID
, HP.PARTY_NUMBER
, PARTY_REL.RELATIONSHIP_TYPE
, PARTY_REL.DIRECTIONAL_FLAG
, PARTY_REL.COMMENTS
, PARTY_REL.START_DATE
, PARTY_REL.END_DATE
, PARTY_REL.LAST_UPDATE_DATE
, PARTY_REL.STATUS
, PARTY_REL.CONTENT_SOURCE_TYPE
, ORG_CONT.ORG_CONTACT_ID
, ORG_CONT.COMMENTS
, ORG_CONT.CONTACT_NUMBER
, ORG_CONT.DEPARTMENT_CODE
, ORG_CONT.DEPARTMENT
, LKUP1.MEANING
, ORG_CONT.TITLE
, ORG_CONT.JOB_TITLE
, LKUP2.MEANING
, ORG_CONT.DECISION_MAKER_FLAG
, ORG_CONT.JOB_TITLE_CODE
, ORG_CONT.REFERENCE_USE_FLAG
, ORG_CONT.RANK
, ORG_CONT.LAST_UPDATE_DATE
, ORG_CONT.PARTY_SITE_ID
, ORG_CONT.ORIG_SYSTEM_REFERENCE
, HP.CATEGORY_CODE
, LKUP3.MEANING
, ORG_CONT.ATTRIBUTE_CATEGORY
, ORG_CONT.ATTRIBUTE1
, ORG_CONT.ATTRIBUTE2
, ORG_CONT.ATTRIBUTE3
, ORG_CONT.ATTRIBUTE4
, ORG_CONT.ATTRIBUTE5
, ORG_CONT.ATTRIBUTE6
, ORG_CONT.ATTRIBUTE7
, ORG_CONT.ATTRIBUTE8
, ORG_CONT.ATTRIBUTE9
, ORG_CONT.ATTRIBUTE10
, ORG_CONT.ATTRIBUTE11
, ORG_CONT.ATTRIBUTE12
, ORG_CONT.ATTRIBUTE13
, ORG_CONT.ATTRIBUTE14
, ORG_CONT.ATTRIBUTE15
, ORG_CONT.ATTRIBUTE16
, ORG_CONT.ATTRIBUTE17
, ORG_CONT.ATTRIBUTE18
, ORG_CONT.ATTRIBUTE19
, ORG_CONT.ATTRIBUTE20
, ORG_CONT.ATTRIBUTE21
, ORG_CONT.ATTRIBUTE22
, ORG_CONT.ATTRIBUTE23
, ORG_CONT.ATTRIBUTE24
, ORG_CONT.LAST_UPDATE_DATE
, ORG_CONT.LAST_UPDATED_BY
, ORG_CONT.CREATION_DATE
, ORG_CONT.CREATED_BY
, ORG_CONT.LAST_UPDATE_LOGIN
, ORG_CONT.OBJECT_VERSION_NUMBER
, ORG_CONT.CREATED_BY_MODULE
, ORG_CONT.APPLICATION_ID
, PARTY_REL.OBJECT_VERSION_NUMBER
, PARTY_REL.CREATED_BY_MODULE
, PARTY_REL.APPLICATION_ID
, HP.OBJECT_VERSION_NUMBER
, HP.CREATED_BY_MODULE
, HP.APPLICATION_ID
, ORG_CONT.CONTACT_KEY
, ORG_CONT.MAILING_ADDRESS_ID
, ORG_CONT.MAIL_STOP
, ORG_CONT.MANAGED_BY
, ORG_CONT.MATCH_GROUP_ID
, ORG_CONT.NATIVE_LANGUAGE
, ORG_CONT.STATUS
, ORG_CONT.OTHER_LANGUAGE_1
, ORG_CONT.OTHER_LANGUAGE_2
FROM HZ_RELATIONSHIPS PARTY_REL
, HZ_ORG_CONTACTS ORG_CONT
, HZ_PARTIES HP
, AR_LOOKUPS LKUP1
, AR_LOOKUPS LKUP2
, AR_LOOKUPS LKUP3
WHERE ORG_CONT.PARTY_RELATIONSHIP_ID = PARTY_REL.RELATIONSHIP_ID
AND ( PARTY_REL.START_DATE IS NULL OR (PARTY_REL.START_DATE IS NOT NULL
AND SYSDATE BETWEEN PARTY_REL.START_DATE
AND NVL(PARTY_REL.END_DATE
, SYSDATE)))
AND ORG_CONT.DEPARTMENT_CODE = LKUP1.LOOKUP_CODE(+)
AND LKUP1.LOOKUP_TYPE(+) = 'DEPARTMENT_TYPE'
AND ORG_CONT.JOB_TITLE_CODE = LKUP2.LOOKUP_CODE(+)
AND LKUP2.LOOKUP_TYPE(+) = 'RESPONSIBILITY'
AND HP.PARTY_ID = PARTY_REL.PARTY_ID
AND HP.CATEGORY_CODE = LKUP3.LOOKUP_CODE(+)
AND LKUP3.LOOKUP_TYPE(+) = 'CUSTOMER_CATEGORY'
AND PARTY_REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND PARTY_REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
AND PARTY_REL.DIRECTIONAL_FLAG = 'F'

Columns