View: AST_LS_CONT_NAME_V

View Text

SELECT O.PARTY_NAME
, P.PARTY_ID PARTY_CONTACT_ID
, NULL PARTY_CONTACT_NAME
, 'PARTY_RELATIONSHIP' PARTY_TYPE
, O.PARTY_NUMBER
, O.PARTY_ID
, O.COMPETITOR_FLAG
, O.CUSTOMER_KEY
, O.STATUS
, O.SIC_CODE
, O.FISCAL_YEAREND_MONTH
, O.CURR_FY_POTENTIAL_REVENUE CURRENT_FY_REVENUE
, O.TAX_REFERENCE
, P.PARTY_ID PERSON_ID
, P.PARTY_NAME PERSON_NAME
, P.PERSON_FIRST_NAME
, P.PERSON_LAST_NAME
, P.PERSON_FIRST_NAME || ' ' || P.PERSON_LAST_NAME FULL_NAME
, R.EMAIL_ADDRESS
, R.ADDRESS1
, R.ADDRESS2
, R.ADDRESS3
, R.ADDRESS4
, R.CITY
, R.STATE
, R.PROVINCE
, R.COUNTY
, R.POSTAL_CODE
, R.COUNTRY
, CNT.CONTACT_NUMBER
, CNT.CONTACT_KEY
, CNT.ORG_CONTACT_ID
, CNT.JOB_TITLE
, CNT.DEPARTMENT_CODE
, CNT.DEPARTMENT
, CPT.CONTACT_POINT_ID
, CPT.CONTACT_POINT_TYPE
, CPT.TELEPHONE_TYPE
, CPT.PHONE_LINE_TYPE
, CPT.PHONE_COUNTRY_CODE
, CPT.PHONE_AREA_CODE
, CPT.PHONE_NUMBER
, CPT.PHONE_EXTENSION
, NVL(CPT.PHONE_COUNTRY_CODE
, ' ')||'-'|| NVL(CPT.PHONE_AREA_CODE
, ' ')||'-'|| CPT.PHONE_NUMBER||' '||CPT.PHONE_EXTENSION FULL_PHONE_NUMBER
, REL.PARTY_RELATIONSHIP_TYPE
, REL.PARTY_RELATIONSHIP_ID
FROM HZ_PARTY_RELATIONSHIPS REL
, HZ_PARTIES P
, HZ_PARTIES O
, HZ_PARTIES R
, HZ_CONTACT_POINTS CPT
, HZ_ORG_CONTACTS CNT
WHERE P.PARTY_TYPE = 'PERSON'
AND P.PARTY_ID = REL.SUBJECT_ID
AND REL.STATUS = 'A'
AND REL.OBJECT_ID = O.PARTY_ID
AND REL.PARTY_RELATIONSHIP_ID = CNT.PARTY_RELATIONSHIP_ID
AND R.PARTY_ID = REL.PARTY_ID
AND CPT.OWNER_TABLE_NAME(+) = 'HZ_PARTIES'
AND CPT.OWNER_TABLE_ID(+) = REL.PARTY_ID
AND CPT.PRIMARY_FLAG(+) = 'Y'
AND CPT.CONTACT_POINT_TYPE(+) = 'PHONE'
AND CPT.STATUS(+) = 'A'

Columns