View: BIC_CONTACTS_DETAIL_V

View Text

SELECT DISTINCT NULL CUST_ACCOUNT_ID
, NULL ORG_ID
, A.PARTY_ID OBJECT_PARTY_ID
, C.PARTY_ID
, C.PARTY_NAME CONTACT_NAME
, CON.DEPARTMENT
, CON.TITLE
, CON.JOB_TITLE
, CON.RANK
, L.ADDRESS
, P.CONTACT_TYPE
, P.CONTACT_ID
, P.PRIMARY_FLAG
FROM HZ_PARTIES A
, HZ_PARTIES C
, HZ_PARTY_RELATIONSHIPS R
, HZ_ORG_CONTACTS CON
, HZ_PARTY_SITES ST
, BIC_ADDRESS_V L
, BIC_CONTACT_POINTS_V P
WHERE A.PARTY_ID = R.OBJECT_ID
AND R.SUBJECT_ID = C.PARTY_ID
AND R.PARTY_RELATIONSHIP_TYPE = 'CONTACT_OF'
AND R.PARTY_RELATIONSHIP_ID = CON.PARTY_RELATIONSHIP_ID(+)
AND C.PARTY_ID = ST.PARTY_ID(+)
AND P.PRIMARY_FLAG(+) = 'Y'
AND NVL(ST.START_DATE_ACTIVE(+)
, SYSDATE) <= SYSDATE
AND NVL(ST.END_DATE_ACTIVE(+)
, SYSDATE) >= SYSDATE
AND ST.LOCATION_ID = L.LOCATION_ID (+)
AND P.OWNER_TABLE_NAME(+) = 'HZ_PARTIES'
AND P.OWNER_TABLE_ID(+) = C.PARTY_ID WITH READ ONLY

Columns