eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.OKS_RA_HCONTACTS_V

Source

View Text - Preformatted


SELECT ACCT_ROLE.ROWID , ACCT_ROLE.CUST_ACCOUNT_ROLE_ID /* CONTACT_ID */ , ACCT_ROLE.LAST_UPDATE_DATE , ACCT_ROLE.LAST_UPDATED_BY , ACCT_ROLE.CREATION_DATE , ACCT_ROLE.CREATED_BY , ACCT_ROLE.CUST_ACCOUNT_ID /* CUSTOMER_ID */ , ACCT_ROLE.STATUS , ACCT_ROLE.ORIG_SYSTEM_REFERENCE /* ORIG_SYSTEM_REFERENCE */ , substrb(PARTY.PERSON_LAST_NAME,1,50) , ORG_CONT.LAST_UPDATE_LOGIN , PARTY.PERSON_PRE_NAME_ADJUNCT , substrb(PARTY.PERSON_FIRST_NAME,1,40) , ORG_CONT.JOB_TITLE , 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 /*The key is taken from hz_parties table as person key is stored there - bug 1199439,1354647*/ , PARTY.CUSTOMER_KEY /* ORG_CONT.CONTACT_KEY */ , NULL /* CONTACT_PERSONAL_INFORMATION */ , ORG_CONT.DECISION_MAKER_FLAG , ORG_CONT.JOB_TITLE_CODE , 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 /* PRIMARY_ROLE */ , 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.PERSON_NAME_SUFFIX /* SUFFIX */ , REL_PARTY.EMAIL_ADDRESS /* contact's email is relationship party's email - 1276469 */ , NULL /* SEX_CODE */ , NULL /* SALUTATION */ , ORG_CONT.DEPARTMENT_CODE , ORG_CONT.DEPARTMENT , NULL /* LAST_NAME_ALT */ , NULL /* FIRST_NAME_ALT */ , PARTY.PARTY_ID , ORG_CONT.ORG_CONTACT_ID , PARTY.LAST_UPDATE_DATE , REL.LAST_UPDATE_DATE , ORG_CONT.LAST_UPDATE_DATE , REL.RELATIONSHIP_ID 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 ROLE_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_ID = PARTY.PARTY_ID AND REL.PARTY_ID = REL_PARTY.PARTY_ID AND REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES' AND REL.OBJECT_TABLE_NAME = 'HZ_PARTIES' AND ACCT_ROLE.CUST_ACCOUNT_ID = ROLE_ACCT.CUST_ACCOUNT_ID AND ROLE_ACCT.PARTY_ID = REL.OBJECT_ID /* AND REL.DIRECTIONAL_FLAG = 'F' */

View Text - HTML Formatted

SELECT ACCT_ROLE.ROWID
, ACCT_ROLE.CUST_ACCOUNT_ROLE_ID /* CONTACT_ID */
, ACCT_ROLE.LAST_UPDATE_DATE
, ACCT_ROLE.LAST_UPDATED_BY
, ACCT_ROLE.CREATION_DATE
, ACCT_ROLE.CREATED_BY
, ACCT_ROLE.CUST_ACCOUNT_ID /* CUSTOMER_ID */
, ACCT_ROLE.STATUS
, ACCT_ROLE.ORIG_SYSTEM_REFERENCE /* ORIG_SYSTEM_REFERENCE */
, SUBSTRB(PARTY.PERSON_LAST_NAME
, 1
, 50)
, ORG_CONT.LAST_UPDATE_LOGIN
, PARTY.PERSON_PRE_NAME_ADJUNCT
, SUBSTRB(PARTY.PERSON_FIRST_NAME
, 1
, 40)
, ORG_CONT.JOB_TITLE
, 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 /*THE KEY IS TAKEN
FROM HZ_PARTIES TABLE AS PERSON KEY IS STORED THERE - BUG 1199439
, 1354647*/
, PARTY.CUSTOMER_KEY /* ORG_CONT.CONTACT_KEY */
, NULL /* CONTACT_PERSONAL_INFORMATION */
, ORG_CONT.DECISION_MAKER_FLAG
, ORG_CONT.JOB_TITLE_CODE
, 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 /* PRIMARY_ROLE */
, 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.PERSON_NAME_SUFFIX /* SUFFIX */
, REL_PARTY.EMAIL_ADDRESS /* CONTACT'S EMAIL IS RELATIONSHIP PARTY'S EMAIL - 1276469 */
, NULL /* SEX_CODE */
, NULL /* SALUTATION */
, ORG_CONT.DEPARTMENT_CODE
, ORG_CONT.DEPARTMENT
, NULL /* LAST_NAME_ALT */
, NULL /* FIRST_NAME_ALT */
, PARTY.PARTY_ID
, ORG_CONT.ORG_CONTACT_ID
, PARTY.LAST_UPDATE_DATE
, REL.LAST_UPDATE_DATE
, ORG_CONT.LAST_UPDATE_DATE
, REL.RELATIONSHIP_ID
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 ROLE_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_ID = PARTY.PARTY_ID
AND REL.PARTY_ID = REL_PARTY.PARTY_ID
AND REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
AND ACCT_ROLE.CUST_ACCOUNT_ID = ROLE_ACCT.CUST_ACCOUNT_ID
AND ROLE_ACCT.PARTY_ID = REL.OBJECT_ID /*
AND REL.DIRECTIONAL_FLAG = 'F' */