[Home] [Help]
| Product: | AMS - Marketing |
|---|---|
| Description: | This view is used by Discoverer Business Area:List Generation Folders.It retrieves all Contacts on the system along with their addresses and primary and secondary interests. |
| Implementation/DBA Data: | Not implemented in this database |
SELECT C.ORG_CONTACT_ID CONTACT_ID
, 'AMS_CONTACT_LO' CON_TYPE
, C.PARTY_SITE_ID ADDRESS_ID
, O.PARTY_ID CUSTOMER_ID
, P.PARTY_ID PARTY_ID
, P.PERSON_LAST_NAME LAST_NAME
, P.PERSON_FIRST_NAME FIRST_NAME
, EML.EMAIL_ADDRESS
, C.RANK
, P.PERSON_NAME_SUFFIX SUFFIX
, P.SALUTATION
, P.PERSON_PRE_NAME_ADJUNCT TITLE
, C.DEPARTMENT_CODE
, C.JOB_TITLE_CODE
, P.LAST_UPDATED_BY CON_LAST_UPDATED_BY
, P.LAST_UPDATE_DATE CON_LAST_UPDATED_DATE
, FND_USER_AP_PKG.GET_USER_NAME(P.LAST_UPDATED_BY) CON_LAST_UPDATED_NAME
, NVL(C.DECISION_MAKER_FLAG
, 'N') CONTACT_DECISION_MAKER
, NVL(C.REFERENCE_USE_FLAG
, 'N') CONTACT_REFERENCE
, DECODE(C.STATUS
, 'A'
, 'ACTIVE'
, 'INACTIVE') CONTACT_ACTIVE
, CIT.INTEREST_TYPE CONTACT_INTEREST_TYPE
, CIT.INTEREST_TYPE||'/'||CICP.CODE CONTACT_PRIMARY_INT_CODE
, CIT.INTEREST_TYPE||'/'||CICP.CODE||'/'||CICS.CODE CONTACT_SECONDARY_INT_CODE
FROM HZ_PARTIES P
, HZ_PARTIES O
, HZ_PARTY_RELATIONSHIPS R
, HZ_ORG_CONTACTS C
, AS_INTERESTS_ALL CIA
, AS_INTEREST_TYPES_VL CIT
, AS_INTEREST_CODES_VL CICP
, AS_INTEREST_CODES_VL CICS
, AMS_P_CONTACT_EMAIL_V EML
WHERE P.PARTY_TYPE = 'PERSON'
AND R.PARTY_RELATIONSHIP_TYPE IN ('CONTACT'
, 'CONTACT_OF')
AND P.PARTY_ID = R.SUBJECT_ID
AND O.PARTY_ID = R.OBJECT_ID
AND R.PARTY_RELATIONSHIP_ID = C.PARTY_RELATIONSHIP_ID
AND CIA.ADDRESS_ID(+) = C.PARTY_SITE_ID
AND CIA.CONTACT_ID(+) = C.ORG_CONTACT_ID
AND CIA.INTEREST_TYPE_ID = CIT.INTEREST_TYPE_ID(+)
AND CIT.CONTACT_INTEREST_FLAG(+) = 'Y'
AND CIA.PRIMARY_INTEREST_CODE_ID = CICP.INTEREST_CODE_ID(+)
AND CIA.SECONDARY_INTEREST_CODE_ID = CICS.INTEREST_CODE_ID(+)
AND C.ORG_CONTACT_ID=EML.CONTACT_ID(+)