View: AST_LS_ORGANIZATIONS_V

View Text

SELECT CONT_POINT.CONTACT_POINT_ID
, ORG_CONT.ORG_CONTACT_ID
, PARTY.PARTY_ID
, PARTY.PARTY_NAME
, PARTY.PARTY_NUMBER
, PARTY.PARTY_TYPE
, PARTY.COMPETITOR_FLAG
, PARTY.CUSTOMER_KEY
, PARTY.CATEGORY_CODE
, PARTY.STATUS
, PARTY.SIC_CODE
, PARTY.TAX_REFERENCE
, PARTY.CURR_FY_POTENTIAL_REVENUE CURRENT_FY_REVENUE
, PARTY.FISCAL_YEAREND_MONTH
, PARTY_SITE.PARTY_SITE_ID
, CONT_POINT.LAST_UPDATE_DATE
, CONT_POINT.LAST_UPDATED_BY
, CONT_POINT.CREATION_DATE
, CONT_POINT.CREATED_BY
, CONT_POINT.TELEPHONE_TYPE
, CONT_POINT.PHONE_LINE_TYPE
, CONT_POINT.LAST_UPDATE_LOGIN
, CONT_POINT.PHONE_AREA_CODE
, CONT_POINT.PHONE_NUMBER
, CONT_POINT.PHONE_EXTENSION
, CONT_POINT.PHONE_COUNTRY_CODE||' '||CONT_POINT.PHONE_AREA_CODE||'-'|| CONT_POINT.PHONE_NUMBER||' '||CONT_POINT.PHONE_EXTENSION FULL_PHONE_NUMBER
, CONT_POINT.CONTACT_POINT_TYPE
, CONT_POINT.EMAIL_ADDRESS
, CONT_POINT.PRIMARY_FLAG
, CONT_POINT.ORIG_SYSTEM_REFERENCE
, CONT_POINT.REQUEST_ID
, CONT_POINT.PROGRAM_APPLICATION_ID
, CONT_POINT.PROGRAM_ID
, CONT_POINT.PROGRAM_UPDATE_DATE
, LOC.ADDRESS_KEY
, LOC.LOCATION_ID
, LOC.ADDRESS1
, LOC.ADDRESS2
, LOC.ADDRESS3
, LOC.ADDRESS4
, LOC.CITY
, LOC.STATE
, LOC.PROVINCE
, LOC.COUNTY
, LOC.POSTAL_CODE
, LOC.COUNTRY
FROM HZ_PARTIES PARTY
, HZ_PARTY_SITES PARTY_SITE
, HZ_ORG_CONTACTS ORG_CONT
, HZ_CONTACT_POINTS CONT_POINT
, HZ_LOCATIONS LOC
WHERE PARTY.PARTY_TYPE = 'ORGANIZATION'
AND PARTY.PARTY_ID = PARTY_SITE.PARTY_ID
AND PARTY_SITE.PARTY_SITE_ID = ORG_CONT.PARTY_SITE_ID(+)
AND PARTY_SITE.IDENTIFYING_ADDRESS_FLAG='Y'
AND LOC.LOCATION_ID (+) = PARTY_SITE.LOCATION_ID
AND CONT_POINT.OWNER_TABLE_NAME(+) = 'HZ_PARTIES'
AND CONT_POINT.OWNER_TABLE_ID(+) = PARTY.PARTY_ID
AND CONT_POINT.PRIMARY_FLAG = 'Y'

Columns