View: AMS_LT_ORG_CONTACT_DETAILS_V

Product:	AMS - Marketing
Description:	Data Source view to get information about b2b contacts
Implementation/DBA Data:	APPS.AMS_LT_ORG_CONTACT_DETAILS_V

View Text

SELECT O.PARTY_ID ORGANIZATION_ID
, R.PARTY_ID
, P.PARTY_NUMBER PARTY_CONTACT_NUMBER
, P.PARTY_NAME PARTY_CONTACT_NAME
, P.PARTY_TYPE
, R.RELATIONSHIP_TYPE
, R.RELATIONSHIP_CODE
, PR.EMAIL_ADDRESS
, P.CUSTOMER_KEY
, P.SIC_CODE
, P.SIC_CODE_TYPE
, P.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
, R.RELATIONSHIP_ID
, PR.ADDRESS1
, PR.ADDRESS2
, PR.ADDRESS3
, PR.ADDRESS4
, PR.COUNTY
, PR.COUNTRY
, PR.CITY
, PR.POSTAL_CODE
, PR.STATE
, PR.PROVINCE
, O.DUNS_NUMBER_C DUNS_NUMBER
, O.REFERENCE_USE_FLAG
, O.TAX_REFERENCE
, O.ORIG_SYSTEM_REFERENCE
, O.JGZZ_FISCAL_CODE
FROM HZ_RELATIONSHIPS R
, HZ_PARTIES PR
, HZ_PARTIES P
, HZ_PARTIES O
WHERE R.OBJECT_ID = O.PARTY_ID
AND R.SUBJECT_ID = P.PARTY_ID
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 P.STATUS = 'A'
AND R.STATUS = 'A'
AND O.STATUS = 'A'
AND PR.PARTY_ID = R.PARTY_ID

Columns