VIEW: APPS.AR_CONTACTS_V

Source

View Text - Preformatted

SELECT ACCT_ROLE.ROWID ROW_ID , ACCT_ROLE.CUST_ACCOUNT_ROLE_ID CONTACT_ID , ACCT_ROLE.CUST_ACCOUNT_ID CUSTOMER_ID , ACCT_ROLE.CUST_ACCT_SITE_ID ADDRESS_ID , ACCT_ROLE.LAST_UPDATE_DATE LAST_UPDATE_DATE , ACCT_ROLE.OBJECT_VERSION_NUMBER OBJECT_VERSION , ACCT_ROLE.LAST_UPDATED_BY LAST_UPDATED_BY , ACCT_ROLE.CREATION_DATE CREATION_DATE , ACCT_ROLE.CREATED_BY CREATED_BY , ACCT_ROLE.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN , PARTY.PERSON_PRE_NAME_ADJUNCT TITLE , ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('CONTACT_TITLE',PARTY.PERSON_PRE_NAME_ADJUNCT) TITLE_MEANING , SUBSTRB(PARTY.PERSON_FIRST_NAME,1,40) FIRST_NAME , SUBSTRB(PARTY.PERSON_LAST_NAME,1,50) LAST_NAME , NULL FIRST_NAME_ALT , NULL LAST_NAME_ALT , ACCT_ROLE.STATUS , ORG_CONT.JOB_TITLE JOB_TITLE , ORG_CONT.JOB_TITLE_CODE JOB_TITLE_CODE , nvl(ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('RESPONSIBILITY',ORG_CONT.JOB_TITLE_CODE) , ORG_CONT.JOB_TITLE) , ORG_CONT.MAIL_STOP MAIL_STOP , ACCT_ROLE.ORIG_SYSTEM_REFERENCE ORIG_SYSTEM_REFERENCE , PARTY.CUSTOMER_KEY CONTACT_KEY , ACCT_ROLE.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY , ACCT_ROLE.ATTRIBUTE1 ATTRIBUTE1 , ACCT_ROLE.ATTRIBUTE2 ATTRIBUTE2 , ACCT_ROLE.ATTRIBUTE3 ATTRIBUTE3 , ACCT_ROLE.ATTRIBUTE4 ATTRIBUTE4 , ACCT_ROLE.ATTRIBUTE5 ATTRIBUTE5 , ACCT_ROLE.ATTRIBUTE6 ATTRIBUTE6 , ACCT_ROLE.ATTRIBUTE7 ATTRIBUTE7 , ACCT_ROLE.ATTRIBUTE8 ATTRIBUTE8 , ACCT_ROLE.ATTRIBUTE9 ATTRIBUTE9 , ACCT_ROLE.ATTRIBUTE10 ATTRIBUTE10 , ACCT_ROLE.ATTRIBUTE11 ATTRIBUTE11 , ACCT_ROLE.ATTRIBUTE12 ATTRIBUTE12 , ACCT_ROLE.ATTRIBUTE13 ATTRIBUTE13 , ACCT_ROLE.ATTRIBUTE14 ATTRIBUTE14 , ACCT_ROLE.ATTRIBUTE15 ATTRIBUTE15 , ACCT_ROLE.ATTRIBUTE16 ATTRIBUTE16 , ACCT_ROLE.ATTRIBUTE17 ATTRIBUTE17 , ACCT_ROLE.ATTRIBUTE18 ATTRIBUTE18 , ACCT_ROLE.ATTRIBUTE19 ATTRIBUTE19 , ACCT_ROLE.ATTRIBUTE20 ATTRIBUTE20 , ACCT_ROLE.ATTRIBUTE21 ATTRIBUTE21 , ACCT_ROLE.ATTRIBUTE22 ATTRIBUTE22 , ACCT_ROLE.ATTRIBUTE23 ATTRIBUTE23 , ACCT_ROLE.ATTRIBUTE24 ATTRIBUTE24 , ACCT_ROLE.ATTRIBUTE25 ATTRIBUTE25 , REL_PARTY.EMAIL_ADDRESS , PARTY.PARTY_ID , ORG_CONT.ORG_CONTACT_ID , CONT_POINT.CONTACT_POINT_ID , ORG_CONT.CONTACT_NUMBER , NULL , PER_LANG.LAST_UPDATE_DATE , PER_LANG.OBJECT_VERSION_NUMBER , CONT_POINT.LAST_UPDATE_DATE , CONT_POINT.OBJECT_VERSION_NUMBER , PARTY.LAST_UPDATE_DATE , PARTY.OBJECT_VERSION_NUMBER , REL.LAST_UPDATE_DATE , ORG_CONT.LAST_UPDATE_DATE , ORG_CONT.OBJECT_VERSION_NUMBER , REL.RELATIONSHIP_ID , REL_PARTY.LAST_UPDATE_DATE , REL_PARTY.OBJECT_VERSION_NUMBER , REL_PARTY.PARTY_ID , REL.OBJECT_VERSION_NUMBER FROM HZ_CONTACT_POINTS CONT_POINT, HZ_CUST_ACCOUNT_ROLES ACCT_ROLE, HZ_PARTIES PARTY, HZ_PARTIES REL_PARTY, HZ_RELATIONSHIPS REL, HZ_ORG_CONTACTS ORG_CONT , HZ_CUST_ACCOUNTS ROLE_ACCT, HZ_PERSON_LANGUAGE PER_LANG WHERE ACCT_ROLE.PARTY_ID = REL.PARTY_ID AND ACCT_ROLE.ROLE_TYPE = 'CONTACT' AND ORG_CONT.PARTY_RELATIONSHIP_ID = REL.RELATIONSHIP_ID AND REL.SUBJECT_ID = PARTY.PARTY_ID AND REL_PARTY.PARTY_ID = REL.PARTY_ID AND CONT_POINT.OWNER_TABLE_ID(+) = REL_PARTY.PARTY_ID AND CONT_POINT.CONTACT_POINT_TYPE(+) = 'EMAIL' AND CONT_POINT.PRIMARY_FLAG(+) = 'Y' AND ACCT_ROLE.CUST_ACCOUNT_ID = ROLE_ACCT.CUST_ACCOUNT_ID AND ROLE_ACCT.PARTY_ID = REL.OBJECT_ID AND PARTY.PARTY_ID = PER_LANG.PARTY_ID(+) AND PER_LANG.NATIVE_LANGUAGE(+) = 'Y'

View Text - HTML Formatted

SELECT ACCT_ROLE.ROWID ROW_ID
, ACCT_ROLE.CUST_ACCOUNT_ROLE_ID CONTACT_ID
, ACCT_ROLE.CUST_ACCOUNT_ID CUSTOMER_ID
, ACCT_ROLE.CUST_ACCT_SITE_ID ADDRESS_ID
, ACCT_ROLE.LAST_UPDATE_DATE LAST_UPDATE_DATE
, ACCT_ROLE.OBJECT_VERSION_NUMBER OBJECT_VERSION
, ACCT_ROLE.LAST_UPDATED_BY LAST_UPDATED_BY
, ACCT_ROLE.CREATION_DATE CREATION_DATE
, ACCT_ROLE.CREATED_BY CREATED_BY
, ACCT_ROLE.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, PARTY.PERSON_PRE_NAME_ADJUNCT TITLE
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('CONTACT_TITLE'
, PARTY.PERSON_PRE_NAME_ADJUNCT) TITLE_MEANING
, SUBSTRB(PARTY.PERSON_FIRST_NAME
, 1
, 40) FIRST_NAME
, SUBSTRB(PARTY.PERSON_LAST_NAME
, 1
, 50) LAST_NAME
, NULL FIRST_NAME_ALT
, NULL LAST_NAME_ALT
, ACCT_ROLE.STATUS
, ORG_CONT.JOB_TITLE JOB_TITLE
, ORG_CONT.JOB_TITLE_CODE JOB_TITLE_CODE
, NVL(ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('RESPONSIBILITY'
, ORG_CONT.JOB_TITLE_CODE)
, ORG_CONT.JOB_TITLE)
, ORG_CONT.MAIL_STOP MAIL_STOP
, ACCT_ROLE.ORIG_SYSTEM_REFERENCE ORIG_SYSTEM_REFERENCE
, PARTY.CUSTOMER_KEY CONTACT_KEY
, ACCT_ROLE.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY
, ACCT_ROLE.ATTRIBUTE1 ATTRIBUTE1
, ACCT_ROLE.ATTRIBUTE2 ATTRIBUTE2
, ACCT_ROLE.ATTRIBUTE3 ATTRIBUTE3
, ACCT_ROLE.ATTRIBUTE4 ATTRIBUTE4
, ACCT_ROLE.ATTRIBUTE5 ATTRIBUTE5
, ACCT_ROLE.ATTRIBUTE6 ATTRIBUTE6
, ACCT_ROLE.ATTRIBUTE7 ATTRIBUTE7
, ACCT_ROLE.ATTRIBUTE8 ATTRIBUTE8
, ACCT_ROLE.ATTRIBUTE9 ATTRIBUTE9
, ACCT_ROLE.ATTRIBUTE10 ATTRIBUTE10
, ACCT_ROLE.ATTRIBUTE11 ATTRIBUTE11
, ACCT_ROLE.ATTRIBUTE12 ATTRIBUTE12
, ACCT_ROLE.ATTRIBUTE13 ATTRIBUTE13
, ACCT_ROLE.ATTRIBUTE14 ATTRIBUTE14
, ACCT_ROLE.ATTRIBUTE15 ATTRIBUTE15
, ACCT_ROLE.ATTRIBUTE16 ATTRIBUTE16
, ACCT_ROLE.ATTRIBUTE17 ATTRIBUTE17
, ACCT_ROLE.ATTRIBUTE18 ATTRIBUTE18
, ACCT_ROLE.ATTRIBUTE19 ATTRIBUTE19
, ACCT_ROLE.ATTRIBUTE20 ATTRIBUTE20
, ACCT_ROLE.ATTRIBUTE21 ATTRIBUTE21
, ACCT_ROLE.ATTRIBUTE22 ATTRIBUTE22
, ACCT_ROLE.ATTRIBUTE23 ATTRIBUTE23
, ACCT_ROLE.ATTRIBUTE24 ATTRIBUTE24
, ACCT_ROLE.ATTRIBUTE25 ATTRIBUTE25
, REL_PARTY.EMAIL_ADDRESS
, PARTY.PARTY_ID
, ORG_CONT.ORG_CONTACT_ID
, CONT_POINT.CONTACT_POINT_ID
, ORG_CONT.CONTACT_NUMBER
, NULL
, PER_LANG.LAST_UPDATE_DATE
, PER_LANG.OBJECT_VERSION_NUMBER
, CONT_POINT.LAST_UPDATE_DATE
, CONT_POINT.OBJECT_VERSION_NUMBER
, PARTY.LAST_UPDATE_DATE
, PARTY.OBJECT_VERSION_NUMBER
, REL.LAST_UPDATE_DATE
, ORG_CONT.LAST_UPDATE_DATE
, ORG_CONT.OBJECT_VERSION_NUMBER
, REL.RELATIONSHIP_ID
, REL_PARTY.LAST_UPDATE_DATE
, REL_PARTY.OBJECT_VERSION_NUMBER
, REL_PARTY.PARTY_ID
, REL.OBJECT_VERSION_NUMBER
FROM HZ_CONTACT_POINTS CONT_POINT
, HZ_CUST_ACCOUNT_ROLES ACCT_ROLE
, HZ_PARTIES PARTY
, HZ_PARTIES REL_PARTY
, HZ_RELATIONSHIPS REL
, HZ_ORG_CONTACTS ORG_CONT
, HZ_CUST_ACCOUNTS ROLE_ACCT
, HZ_PERSON_LANGUAGE PER_LANG
WHERE ACCT_ROLE.PARTY_ID = REL.PARTY_ID
AND ACCT_ROLE.ROLE_TYPE = 'CONTACT'
AND ORG_CONT.PARTY_RELATIONSHIP_ID = REL.RELATIONSHIP_ID
AND REL.SUBJECT_ID = PARTY.PARTY_ID
AND REL_PARTY.PARTY_ID = REL.PARTY_ID
AND CONT_POINT.OWNER_TABLE_ID(+) = REL_PARTY.PARTY_ID
AND CONT_POINT.CONTACT_POINT_TYPE(+) = 'EMAIL'
AND CONT_POINT.PRIMARY_FLAG(+) = 'Y'
AND ACCT_ROLE.CUST_ACCOUNT_ID = ROLE_ACCT.CUST_ACCOUNT_ID
AND ROLE_ACCT.PARTY_ID = REL.OBJECT_ID
AND PARTY.PARTY_ID = PER_LANG.PARTY_ID(+)
AND PER_LANG.NATIVE_LANGUAGE(+) = 'Y'