View: AMS_ORG_CONTACT_DETAILS_V

Product:	AMS - Marketing
Description:	This view lists the details of organization contacts
Implementation/DBA Data:	APPS.AMS_ORG_CONTACT_DETAILS_V

View Text

SELECT C.ORG_CONTACT_ID CONTACT_ID
, O.PARTY_ID ORGANIZATION_ID
, R.PARTY_ID
, C.CONTACT_NUMBER
, P.PARTY_NUMBER PARTY_CONTACT_NUMBER
, P.PARTY_NAME PARTY_CONTACT_NAME
, PR.PARTY_TYPE
, P.VALIDATED_FLAG
, C.PARTY_SITE_ID
, P.STATUS
, R.RELATIONSHIP_TYPE
, R.RELATIONSHIP_CODE
, PR.EMAIL_ADDRESS
, C.DECISION_MAKER_FLAG
, C.REFERENCE_USE_FLAG
, C.RANK
, P.CUSTOMER_KEY
, O.SIC_CODE
, O.SIC_CODE_TYPE
, DECODE(PR.PERSON_TITLE
, NULL
, PR.PERSON_TITLE
, AL.MEANING) TITLE
, C.JOB_TITLE
, C.JOB_TITLE_CODE
, C.DEPARTMENT
, C.DEPARTMENT_CODE
, C.MANAGED_BY
, C.MAILING_ADDRESS_ID
, C.MAIL_STOP
, O.URL
, P.SALUTATION
, P.PERSON_PRE_NAME_ADJUNCT
, P.PERSON_FIRST_NAME
, P.PERSON_MIDDLE_NAME
, P.PERSON_LAST_NAME
, P.PERSON_NAME_SUFFIX
, P.PERSON_TITLE
, P.PERSON_ACADEMIC_TITLE
, P.PERSON_PREVIOUS_LAST_NAME
, P.KNOWN_AS
, P.PERSON_IDEN_TYPE
, P.PERSON_IDENTIFIER
, PR.ADDRESS1
, PR.ADDRESS2
, PR.ADDRESS3
, PR.ADDRESS4
, PR.CITY
, PR.STATE
, PR.POSTAL_CODE
, PR.PROVINCE
, PR.COUNTY
, PR.COUNTRY
, ' '
, O.DUNS_NUMBER
, O.HQ_BRANCH_IND
, O.TAX_NAME
, O.TAX_REFERENCE
, P.GROUP_TYPE
, C.MATCH_GROUP_ID
, C.NATIVE_LANGUAGE
, C.OTHER_LANGUAGE_1
, C.OTHER_LANGUAGE_2
, O.JGZZ_FISCAL_CODE
, R.RELATIONSHIP_ID
, P.ORIG_SYSTEM_REFERENCE
, P.CREATION_DATE
, P.CREATED_BY
, P.CREATED_BY CREATED_BY_NAME
, P.LAST_UPDATE_DATE
, P.LAST_UPDATED_BY
, P.LAST_UPDATED_BY LAST_UPDATED_BY_NAME
, P.LAST_UPDATE_LOGIN
, P.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN_NAME
, P.REQUEST_ID
, P.PROGRAM_APPLICATION_ID
, P.PROGRAM_ID
, P.PROGRAM_UPDATE_DATE
, P.WH_UPDATE_DATE
, P.ATTRIBUTE_CATEGORY
, P.ATTRIBUTE1
, P.ATTRIBUTE2
, P.ATTRIBUTE3
, P.ATTRIBUTE4
, P.ATTRIBUTE5
, P.ATTRIBUTE6
, P.ATTRIBUTE7
, P.ATTRIBUTE8
, P.ATTRIBUTE9
, P.ATTRIBUTE10
, P.ATTRIBUTE11
, P.ATTRIBUTE12
, P.ATTRIBUTE13
, P.ATTRIBUTE14
, P.ATTRIBUTE15
, P.ATTRIBUTE16
, P.ATTRIBUTE17
, P.ATTRIBUTE18
, P.ATTRIBUTE19
, P.ATTRIBUTE20
, P.ATTRIBUTE21
, P.ATTRIBUTE22
, P.ATTRIBUTE23
, P.ATTRIBUTE24
, P.GLOBAL_ATTRIBUTE_CATEGORY
, P.GLOBAL_ATTRIBUTE1
, P.GLOBAL_ATTRIBUTE2
, P.GLOBAL_ATTRIBUTE4
, P.GLOBAL_ATTRIBUTE3
, P.GLOBAL_ATTRIBUTE5
, P.GLOBAL_ATTRIBUTE6
, P.GLOBAL_ATTRIBUTE7
, P.GLOBAL_ATTRIBUTE8
, P.GLOBAL_ATTRIBUTE9
, P.GLOBAL_ATTRIBUTE10
, P.GLOBAL_ATTRIBUTE11
, P.GLOBAL_ATTRIBUTE12
, P.GLOBAL_ATTRIBUTE13
, P.GLOBAL_ATTRIBUTE14
, P.GLOBAL_ATTRIBUTE15
, P.GLOBAL_ATTRIBUTE16
, P.GLOBAL_ATTRIBUTE17
, P.GLOBAL_ATTRIBUTE18
, P.GLOBAL_ATTRIBUTE19
, P.GLOBAL_ATTRIBUTE20
, HPP.GENDER
, HCP.PHONE_COUNTRY_CODE
, HCP.PHONE_AREA_CODE
, HCP.PHONE_NUMBER
, HCP.PHONE_EXTENSION
, HOCR.ROLE_TYPE
, P.PARTY_ID PERSON_PARTY_ID
, O.PARTY_NAME ORGANIZATION_PARTY_NAME
FROM HZ_RELATIONSHIPS R
, HZ_PARTIES PR
, HZ_PARTIES P
, HZ_ORG_CONTACTS C
, HZ_PARTIES O
, HZ_PERSON_PROFILES HPP
, HZ_CONTACT_POINTS HCP
, HZ_ORG_CONTACT_ROLES HOCR
, AR_LOOKUPS AL
WHERE R.OBJECT_ID = O.PARTY_ID
AND R.SUBJECT_ID = P.PARTY_ID
AND C.PARTY_RELATIONSHIP_ID = R.RELATIONSHIP_ID
AND AL.LOOKUP_TYPE= 'CONTACT_TITLE'
AND AL.LOOKUP_CODE= NVL(PR.PERSON_TITLE
, 'MR.')
AND R.DIRECTIONAL_FLAG = 'F'
AND R.SUBJECT_TYPE = 'PERSON'
AND R.OBJECT_TYPE = 'ORGANIZATION'
AND P.PARTY_TYPE = 'PERSON'
AND R.RELATIONSHIP_CODE IN ('CONTACT'
, 'CONTACT_OF'
, 'EMPLOYEE_OF')
AND R.ACTUAL_CONTENT_SOURCE = 'USER_ENTERED'
AND P.STATUS = 'A'
AND R.STATUS = 'A'
AND O.STATUS = 'A'
AND PR.PARTY_ID = R.PARTY_ID
AND HPP.PARTY_ID = P.PARTY_ID
AND HPP.EFFECTIVE_END_DATE IS NULL
AND HPP.ACTUAL_CONTENT_SOURCE = 'SST'
AND HCP.CONTACT_POINT_TYPE(+) = 'PHONE'
AND HCP.OWNER_TABLE_NAME(+) = 'HZ_PARTIES'
AND HCP.OWNER_TABLE_ID (+) = PR.PARTY_ID
AND HCP.STATUS (+) = 'A'
AND HCP.PRIMARY_FLAG (+) = 'Y'
AND HOCR.ORG_CONTACT_ID(+) = C.ORG_CONTACT_ID
AND HOCR.PRIMARY_FLAG (+) = 'Y'

Columns