View: CSC_GS_CUSTOMERS_ORG_V

Product:	CSC - Customer Care
Description:	View used to perform search operation on customer information in the contact center form.
Implementation/DBA Data:	APPS.CSC_GS_CUSTOMERS_ORG_V

View Text

SELECT HZP_OBJ.PARTY_ID OBJ_PARTY_ID
, HZP_OBJ.PARTY_NAME OBJ_PARTY_NAME
, HZP_OBJ.PARTY_NUMBER OBJ_PARTY_NUMBER
, HZP_OBJ.PARTY_TYPE OBJ_PARTY_TYPE
, HZCA_OBJ.CUST_ACCOUNT_ID OBJ_CUST_ACCOUNT_ID
, HZCA_OBJ.ACCOUNT_NUMBER OBJ_ACCOUNT_NUMBER
, HZCA_OBJ.ACCOUNT_NAME OBJ_ACCOUNT_NAME
, HZCA_OBJ.LAST_UPDATE_DATE OBJ_CUST_ID_LAST_UPDATE_DATE
, HZP_OBJ.GROUP_TYPE OBJ_GROUP_TYPE
, HZP_OBJ.PERSON_PRE_NAME_ADJUNCT OBJ_TITLE
, HZP_OBJ.PERSON_FIRST_NAME OBJ_FIRST_NAME
, HZP_OBJ.PERSON_MIDDLE_NAME OBJ_MIDDLE_NAME
, HZP_OBJ.PERSON_LAST_NAME OBJ_LAST_NAME
, HZP_OBJ.PERSON_FIRST_NAME || ' ' || HZP_OBJ.PERSON_LAST_NAME OBJ_FULL_NAME
, HZP_OBJ.LAST_UPDATE_DATE OBJ_LAST_UPDATE_DATE
, CONT_PH.CONTACT_POINT_ID OBJ_CONTACT_POINT_ID
, CONT_EM.CONTACT_POINT_ID OBJ_EMAIL_ID
, CONT_PH.PHONE_COUNTRY_CODE OBJ_PHONE_COUNTRY_CODE
, CONT_PH.PHONE_AREA_CODE OBJ_PHONE_AREA_CODE
, CONT_PH.PHONE_NUMBER OBJ_PHONE_NUMBER
, CONT_PH.PHONE_AREA_CODE||CONT_PH.PHONE_NUMBER OBJ_AREA_CODE_PHONE_NUMBER
, CONT_PH.PHONE_EXTENSION OBJ_PHONE_EXTENSION
, CONT_PH.PHONE_LINE_TYPE OBJ_PHONE_TYPE
, CONT_PH.LAST_UPDATE_DATE OBJ_CONT_LAST_UPDATE_DATE
, CONT_EM.LAST_UPDATE_DATE OBJ_EMAIL_LAST_UPDATE_DATE
, DECODE(HZP_OBJ.ADDRESS1
, NULL
, NULL
, HZP_OBJ.ADDRESS1 || DECODE(HZP_OBJ.ADDRESS2
, NULL
, NULL
, ';' || HZP_OBJ.ADDRESS2 || DECODE(HZP_OBJ.ADDRESS3
, NULL
, NULL
, ';' || HZP_OBJ.ADDRESS3 || DECODE(HZP_OBJ.ADDRESS4
, NULL
, NULL
, ';' || HZP_OBJ.ADDRESS4 )))) OBJ_PARTIAL_ADDRESS
, HZP_OBJ.ADDRESS1 OBJ_ADDRESS1
, HZP_OBJ.ADDRESS2 OBJ_ADDRESS2
, HZP_OBJ.ADDRESS3 OBJ_ADDRESS3
, HZP_OBJ.ADDRESS4 OBJ_ADDRESS4
, HZP_OBJ.CITY OBJ_CITY
, HZP_OBJ.STATE OBJ_STATE
, HZP_OBJ.POSTAL_CODE OBJ_POSTAL_CODE
, HZP_OBJ.PROVINCE OBJ_PROVINCE
, HZP_OBJ.COUNTRY OBJ_COUNTRY
, DECODE(HZP_OBJ.ADDRESS1
, NULL
, NULL
, HZP_OBJ.ADDRESS1 || DECODE(HZP_OBJ.ADDRESS2
, NULL
, NULL
, ';' || HZP_OBJ.ADDRESS2 || DECODE(HZP_OBJ.ADDRESS3
, NULL
, NULL
, ';' || HZP_OBJ.ADDRESS3 || DECODE(HZP_OBJ.ADDRESS4
, NULL
, NULL
, ';' || HZP_OBJ.ADDRESS4 )))) || DECODE(HZP_OBJ.CITY
, NULL
, NULL
, ' - ' || HZP_OBJ.CITY ) || DECODE(HZP_OBJ.STATE
, NULL
, NULL
, ' - ' || HZP_OBJ.STATE ) || DECODE(HZP_OBJ.POSTAL_CODE
, NULL
, NULL
, ' - ' || HZP_OBJ.POSTAL_CODE ) || DECODE(HZP_OBJ.COUNTRY
, NULL
, NULL
, ' ' || HZP_OBJ.COUNTRY ) OBJ_FULL_ADDRESS
, CONT_EM.EMAIL_ADDRESS OBJ_EMAIL_ADDRESS
, 'SELF'
, HZP_OBJ.STATUS
, HZP_OBJ.ATTRIBUTE_CATEGORY
, HZP_OBJ.ATTRIBUTE1
, HZP_OBJ.ATTRIBUTE2
, HZP_OBJ.ATTRIBUTE3
, HZP_OBJ.ATTRIBUTE4
, HZP_OBJ.ATTRIBUTE5
, HZP_OBJ.ATTRIBUTE6
, HZP_OBJ.ATTRIBUTE7
, HZP_OBJ.ATTRIBUTE8
, HZP_OBJ.ATTRIBUTE9
, HZP_OBJ.ATTRIBUTE10
, HZP_OBJ.ATTRIBUTE11
, HZP_OBJ.ATTRIBUTE12
, HZP_OBJ.ATTRIBUTE13
, HZP_OBJ.ATTRIBUTE14
, HZP_OBJ.ATTRIBUTE15
, HZP_OBJ.ATTRIBUTE16
, HZP_OBJ.ATTRIBUTE17
, HZP_OBJ.ATTRIBUTE18
, HZP_OBJ.ATTRIBUTE19
, HZP_OBJ.ATTRIBUTE20
FROM HZ_PARTIES HZP_OBJ
, HZ_CUST_ACCOUNTS HZCA_OBJ
, HZ_CONTACT_POINTS CONT_PH
, HZ_CONTACT_POINTS CONT_EM
WHERE HZP_OBJ.PARTY_ID = HZCA_OBJ.PARTY_ID (+)
AND HZP_OBJ.PARTY_TYPE = 'ORGANIZATION'
AND CONT_EM.OWNER_TABLE_NAME (+) = 'HZ_PARTIES'
AND CONT_EM.OWNER_TABLE_ID (+) = HZP_OBJ.PARTY_ID
AND CONT_EM.CONTACT_POINT_TYPE (+)= 'EMAIL'
AND CONT_PH.OWNER_TABLE_NAME (+) = 'HZ_PARTIES'
AND CONT_PH.OWNER_TABLE_ID (+) = HZP_OBJ.PARTY_ID
AND CONT_PH.CONTACT_POINT_TYPE (+)= 'PHONE'

Columns