[Home] [Help]
View: OKE_CUST_CONTACTS_V
View Text
SELECT CAR.CUST_ACCOUNT_ROLE_ID
, '#'
, PR.OBJECT_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_PARTY_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.PARTY_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
Columns
Name |
ID1 |
ID2 |
PARTY_ID |
PARTY_ID2 |
NAME |
DESCRIPTION |
ORG_ID |
CUST_ACCT_ID |
ROLE_TYPE |
CURRENT_ROLE_STATE_EFFECTIVE |
CUST_ACCT_SITE_ID |
START_DATE_ACTIVE |
END_DATE_ACTIVE |
STATUS |
EMAIL_ADDRESS |