Product: | ONT - Order Management |
---|---|
Description: | This view is used for validation of contact idnetification. |
Implementation/DBA Data: | APPS.OE_RA_CONTACTS_V |
SELECT ACCT_ROLE.CUST_ACCOUNT_ROLE_ID
, ACCT_ROLE.LAST_UPDATE_DATE
, ACCT_ROLE.LAST_UPDATED_BY
, ACCT_ROLE.CREATION_DATE
, ACCT_ROLE.CREATED_BY
, ACCT_ROLE.CUST_ACCOUNT_ID
, ACCT_ROLE.STATUS
, ACCT_ROLE.ORIG_SYSTEM_REFERENCE
, SUBSTRB( PARTY.PERSON_LAST_NAME
, 1
, 50) LAST_NAME
, ORG_CONT.LAST_UPDATE_LOGIN
, PARTY.PERSON_PRE_NAME_ADJUNCT
, SUBSTRB(PARTY.PERSON_FIRST_NAME
, 1
, 40) FIRST_NAME
, ORG_CONT.JOB_TITLE
, ORG_CONT.MAIL_STOP
, ACCT_ROLE.CUST_ACCT_SITE_ID /* ADDRESS_ID */
, ACCT_ROLE.ATTRIBUTE_CATEGORY
, ACCT_ROLE.ATTRIBUTE1
, ACCT_ROLE.ATTRIBUTE2
, ACCT_ROLE.ATTRIBUTE3
, ACCT_ROLE.ATTRIBUTE4
, ACCT_ROLE.ATTRIBUTE5
, ACCT_ROLE.ATTRIBUTE6
, ACCT_ROLE.ATTRIBUTE7
, ACCT_ROLE.ATTRIBUTE8
, ACCT_ROLE.ATTRIBUTE9
, ACCT_ROLE.ATTRIBUTE10
, ACCT_ROLE.REQUEST_ID
, ACCT_ROLE.PROGRAM_APPLICATION_ID
, ACCT_ROLE.PROGRAM_ID
, ACCT_ROLE.PROGRAM_UPDATE_DATE
, PARTY.CUSTOMER_KEY
, NULL /*CONTACT_PERSONAL_INFORMATION*/
, ORG_CONT.DECISION_MAKER_FLAG
, ORG_CONT.JOB_TITLE_CODE
, ORG_CONT.MANAGED_BY
, PARTY.LANGUAGE_NAME NATIVE_LANGUAGE
, ORG_CONT.REFERENCE_USE_FLAG
, ORG_CONT.CONTACT_NUMBER
, ACCT_ROLE.ATTRIBUTE11
, ACCT_ROLE.ATTRIBUTE25
, NULL /*OTHER_LANGUAGE_1*/
, NULL /*OTHER_LANGUAGE_2*/
, ORG_CONT.RANK
, ACCT_ROLE.PRIMARY_FLAG
, ACCT_ROLE.ATTRIBUTE12
, ACCT_ROLE.ATTRIBUTE13
, ACCT_ROLE.ATTRIBUTE14
, ACCT_ROLE.ATTRIBUTE15
, ACCT_ROLE.ATTRIBUTE16
, ACCT_ROLE.ATTRIBUTE17
, ACCT_ROLE.ATTRIBUTE18
, ACCT_ROLE.ATTRIBUTE19
, ACCT_ROLE.ATTRIBUTE20
, ACCT_ROLE.ATTRIBUTE21
, ACCT_ROLE.ATTRIBUTE22
, ACCT_ROLE.ATTRIBUTE23
, ACCT_ROLE.ATTRIBUTE24
, PARTY.DO_NOT_MAIL_FLAG
, PARTY.PERSON_NAME_SUFFIX SUFFIX
, REL_PARTY.EMAIL_ADDRESS
, ORG_CONT.MAILING_ADDRESS_ID
, ORG_CONT.MATCH_GROUP_ID
, NULL /*SEX_CODE*/
, NULL /*SALUTATION*/
, ORG_CONT.DEPARTMENT_CODE
, ORG_CONT.DEPARTMENT
FROM HZ_CUST_ACCOUNT_ROLES ACCT_ROLE
, HZ_PARTIES PARTY
, HZ_RELATIONSHIPS REL
, HZ_ORG_CONTACTS ORG_CONT
, HZ_PARTIES REL_PARTY
, HZ_CUST_ACCOUNTS ACCT
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_TABLE_NAME = 'HZ_PARTIES'
AND REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
AND REL.SUBJECT_ID = PARTY.PARTY_ID
AND REL.PARTY_ID = REL_PARTY.PARTY_ID
AND REL.OBJECT_ID = ACCT.PARTY_ID
AND ACCT.CUST_ACCOUNT_ID = ACCT_ROLE.CUST_ACCOUNT_ID