VIEW: APPS.OKE_CUSTOMER_CONTACTS_V

Source

View Text - Preformatted

SELECT CAR.CUST_ACCOUNT_ROLE_ID , '#' , CA.CUST_ACCOUNT_ID , '#' , SUBSTRB(P.PERSON_LAST_NAME , 1 , 50) || ', ' || SUBSTRB(P.PERSON_FIRST_NAME , 1 , 40) , SUBSTR(OC.JOB_TITLE , 1 , 40) , CA.ORG_ID , CAR.CUST_ACCOUNT_ID , CAR.ROLE_TYPE , CAR.CURRENT_ROLE_STATE_EFFECTIVE , CAR.CUST_ACCT_SITE_ID , CAR.BEGIN_DATE , CAR.END_DATE , DECODE( SIGN( TRUNC(SYSDATE) - TRUNC( NVL(CAR.BEGIN_DATE , SYSDATE) ) ) , -1 , 'I' , DECODE( SIGN( TRUNC(SYSDATE) - TRUNC( NVL(CAR.END_DATE , SYSDATE) ) ) , 1 , 'I' , 'A' ) ) , P2.EMAIL_ADDRESS FROM HZ_PARTIES P2 , HZ_CUST_ACCOUNTS_ALL CA , HZ_CUST_ACCOUNT_ROLES CAR , HZ_PARTIES P , HZ_RELATIONSHIPS PR , HZ_ORG_CONTACTS OC WHERE CAR.ROLE_TYPE = 'CONTACT' AND PR.PARTY_ID = CAR.PARTY_ID AND PR.CONTENT_SOURCE_TYPE = 'USER_ENTERED' AND OC.PARTY_RELATIONSHIP_ID = PR.RELATIONSHIP_ID AND P.PARTY_ID = PR.SUBJECT_ID AND CA.CUST_ACCOUNT_ID = CAR.CUST_ACCOUNT_ID AND PR.PARTY_ID = P2.PARTY_ID

View Text - HTML Formatted

SELECT CAR.CUST_ACCOUNT_ROLE_ID
, '#'
, CA.CUST_ACCOUNT_ID
, '#'
, SUBSTRB(P.PERSON_LAST_NAME
, 1
, 50) || '
, ' || SUBSTRB(P.PERSON_FIRST_NAME
, 1
, 40)
, SUBSTR(OC.JOB_TITLE
, 1
, 40)
, CA.ORG_ID
, CAR.CUST_ACCOUNT_ID
, CAR.ROLE_TYPE
, CAR.CURRENT_ROLE_STATE_EFFECTIVE
, CAR.CUST_ACCT_SITE_ID
, CAR.BEGIN_DATE
, CAR.END_DATE
, DECODE( SIGN( TRUNC(SYSDATE) - TRUNC( NVL(CAR.BEGIN_DATE
, SYSDATE) ) )
, -1
, 'I'
, DECODE( SIGN( TRUNC(SYSDATE) - TRUNC( NVL(CAR.END_DATE
, SYSDATE) ) )
, 1
, 'I'
, 'A' ) )
, P2.EMAIL_ADDRESS
FROM HZ_PARTIES P2
, HZ_CUST_ACCOUNTS_ALL CA
, HZ_CUST_ACCOUNT_ROLES CAR
, HZ_PARTIES P
, HZ_RELATIONSHIPS PR
, HZ_ORG_CONTACTS OC
WHERE CAR.ROLE_TYPE = 'CONTACT'
AND PR.PARTY_ID = CAR.PARTY_ID
AND PR.CONTENT_SOURCE_TYPE = 'USER_ENTERED'
AND OC.PARTY_RELATIONSHIP_ID = PR.RELATIONSHIP_ID
AND P.PARTY_ID = PR.SUBJECT_ID
AND CA.CUST_ACCOUNT_ID = CAR.CUST_ACCOUNT_ID
AND PR.PARTY_ID = P2.PARTY_ID