View: CSC_GS_CUSTOMERS_ORG_ADV_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_ADV_V

View Text

SELECT HZP_PTY.PARTY_ID PTY_PARTY_ID
, HZP_PTY.PARTY_NAME PTY_PARTY_NAME
, HZP_PTY.PARTY_NUMBER PTY_PARTY_NUMBER
, HZP_PTY.PARTY_TYPE PTY_PARTY_TYPE
, HZCA_PTY.CUST_ACCOUNT_ID PTY_CUST_ACCOUNT_ID
, HZCA_PTY.ACCOUNT_NUMBER PTY_ACCOUNT_NUMBER
, HZCA_PTY.ACCOUNT_NAME PTY_ACCOUNT_NAME
, HZCA_PTY.LAST_UPDATE_DATE PTY_CUST_ID_LAST_UPDATE_DATE
, HZP_PTY.GROUP_TYPE PTY_GROUP_TYPE
, HZP_PTY.PERSON_PRE_NAME_ADJUNCT PTY_TITLE
, HZP_PTY.PERSON_FIRST_NAME PTY_FIRST_NAME
, HZP_PTY.PERSON_MIDDLE_NAME PTY_MIDDLE_NAME
, HZP_PTY.PERSON_LAST_NAME PTY_LAST_NAME
, HZP_PTY.PERSON_FIRST_NAME || ' ' || HZP_PTY.PERSON_LAST_NAME PTY_FULL_NAME
, HZP_PTY.LAST_UPDATE_DATE PTY_LAST_UPDATE_DATE
, CONT_PTY.CONTACT_POINT_ID PTY_CONTACT_POINT_ID
, CONT_PTY.EMAIL_ID PTY_EMAIL_ID
, CONT_PTY.PHONE_COUNTRY_CODE PTY_PHONE_COUNTRY_CODE
, CONT_PTY.PHONE_AREA_CODE PTY_PHONE_AREA_CODE
, CONT_PTY.PHONE_NUMBER PTY_PHONE_NUMBER
, CONT_PTY.PHONE_AREA_CODE || CONT_PTY.PHONE_NUMBER PTY_AREA_CODE_PHONE_NUMBER
, CONT_PTY.PHONE_EXTENSION PTY_PHONE_EXTENSION
, CONT_PTY.PHONE_LINE_TYPE PTY_PHONE_TYPE
, CONT_PTY.LAST_UPDATE_DATE PTY_CONT_LAST_UPDATE_DATE
, CONT_PTY.EMAIL_LAST_UPDATE_DATE PTY_EMAIL_LAST_UPDATE_DATE
, DECODE(HZP_PTY.ADDRESS1
, NULL
, NULL
, HZP_PTY.ADDRESS1 || DECODE(HZP_PTY.ADDRESS2
, NULL
, NULL
, ';' || HZP_PTY.ADDRESS2 || DECODE(HZP_PTY.ADDRESS3
, NULL
, NULL
, ';' || HZP_PTY.ADDRESS3 || DECODE(HZP_PTY.ADDRESS4
, NULL
, NULL
, ';' || HZP_PTY.ADDRESS4 )))) PTY_PARTIAL_ADDRESS
, HZP_PTY.ADDRESS1 PTY_ADDRESS1
, HZP_PTY.ADDRESS2 PTY_ADDRESS2
, HZP_PTY.ADDRESS3 PTY_ADDRESS3
, HZP_PTY.ADDRESS4 PTY_ADDRESS4
, HZP_PTY.CITY PTY_CITY
, HZP_PTY.STATE PTY_STATE
, HZP_PTY.POSTAL_CODE PTY_POSTAL_CODE
, HZP_PTY.PROVINCE PTY_PROVINCE
, HZP_PTY.COUNTRY PTY_COUNTRY
, DECODE(HZP_PTY.ADDRESS1
, NULL
, NULL
, HZP_PTY.ADDRESS1 || DECODE(HZP_PTY.ADDRESS2
, NULL
, NULL
, ';' || HZP_PTY.ADDRESS2 || DECODE(HZP_PTY.ADDRESS3
, NULL
, NULL
, ';' || HZP_PTY.ADDRESS3 || DECODE(HZP_PTY.ADDRESS4
, NULL
, NULL
, ';' || HZP_PTY.ADDRESS4 )))) || DECODE(HZP_PTY.CITY
, NULL
, NULL
, ' - ' || HZP_PTY.CITY ) || DECODE(HZP_PTY.STATE
, NULL
, NULL
, ' - ' || HZP_PTY.STATE ) || DECODE(HZP_PTY.POSTAL_CODE
, NULL
, NULL
, ' - ' || HZP_PTY.POSTAL_CODE) || DECODE(HZP_PTY.COUNTRY
, NULL
, NULL
, ' ' || HZP_PTY.COUNTRY) PTY_FULL_ADDRESS
, CONT_PTY.EMAIL_ADDRESS PTY_EMAIL_ADDRESS
, HZP_SUB.PARTY_ID SUB_PARTY_ID
, HZP_SUB.PARTY_NAME SUB_PARTY_NAME
, HZP_SUB.PARTY_NUMBER SUB_PARTY_NUMBER
, HZP_SUB.PARTY_TYPE SUB_PARTY_TYPE
, HZCA_SUB.CUST_ACCOUNT_ID SUB_CUST_ACCOUNT_ID
, HZCA_SUB.ACCOUNT_NUMBER SUB_ACCOUNT_NUMBER
, HZCA_SUB.ACCOUNT_NAME SUB_ACCOUNT_NAME
, HZCA_SUB.LAST_UPDATE_DATE SUB_CUST_ID_LAST_UPDATE_DATE
, HZP_SUB.GROUP_TYPE SUB_GROUP_TYPE
, HZP_SUB.PERSON_PRE_NAME_ADJUNCT SUB_TITLE
, HZP_SUB.PERSON_FIRST_NAME SUB_FIRST_NAME
, HZP_SUB.PERSON_MIDDLE_NAME SUB_MIDDLE_NAME
, HZP_SUB.PERSON_LAST_NAME SUB_LAST_NAME
, HZP_SUB.PERSON_FIRST_NAME || ' ' || HZP_SUB.PERSON_LAST_NAME SUB_FULL_NAME
, HZP_SUB.LAST_UPDATE_DATE SUB_LAST_UPDATE_DATE
, CONT_SUB.CONTACT_POINT_ID SUB_CONTACT_POINT_ID
, CONT_SUB.EMAIL_ID SUB_EMAIL_ID
, CONT_SUB.PHONE_COUNTRY_CODE SUB_PHONE_COUNTRY_CODE
, CONT_SUB.PHONE_AREA_CODE SUB_PHONE_AREA_CODE
, DECODE(CONT_SUB.PHONE_AREA_CODE
, NULL
, NULL
, CONT_SUB.PHONE_AREA_CODE ||'-') || CONT_SUB.PHONE_NUMBER SUB_AREA_CODE_PHONE_NUMBER
, CONT_SUB.PHONE_NUMBER SUB_PHONE_NUMBER
, CONT_SUB.PHONE_EXTENSION SUB_PHONE_EXTENSION
, CONT_SUB.PHONE_LINE_TYPE SUB_PHONE_TYPE
, CONT_SUB.LAST_UPDATE_DATE SUB_CONT_LAST_UPDATE_DATE
, CONT_SUB.EMAIL_LAST_UPDATE_DATE SUB_EMAIL_LAST_UPDATE_DATE
, DECODE(HZP_SUB.ADDRESS1
, NULL
, NULL
, HZP_SUB.ADDRESS1 || DECODE(HZP_SUB.ADDRESS2
, NULL
, NULL
, ';' || HZP_SUB.ADDRESS2 || DECODE(HZP_SUB.ADDRESS3
, NULL
, NULL
, ';' || HZP_SUB.ADDRESS3 || DECODE(HZP_SUB.ADDRESS4
, NULL
, NULL
, ';' || HZP_SUB.ADDRESS4 )))) SUB_PARTIAL_ADDRESS
, HZP_SUB.ADDRESS1 SUB_ADDRESS1
, HZP_SUB.ADDRESS2 SUB_ADDRESS2
, HZP_SUB.ADDRESS3 SUB_ADDRESS3
, HZP_SUB.ADDRESS4 SUB_ADDRESS4
, HZP_SUB.CITY SUB_CITY
, HZP_SUB.STATE SUB_STATE
, HZP_SUB.POSTAL_CODE SUB_POSTAL_CODE
, HZP_SUB.PROVINCE SUB_PROVINCE
, HZP_SUB.COUNTRY SUB_COUNTRY
, DECODE(HZP_SUB.ADDRESS1
, NULL
, NULL
, HZP_SUB.ADDRESS1 || DECODE(HZP_SUB.ADDRESS2
, NULL
, NULL
, ';' || HZP_SUB.ADDRESS2 || DECODE(HZP_SUB.ADDRESS3
, NULL
, NULL
, ';' || HZP_SUB.ADDRESS3 || DECODE(HZP_SUB.ADDRESS4
, NULL
, NULL
, ';' || HZP_SUB.ADDRESS4 )))) || DECODE(HZP_SUB.CITY
, NULL
, NULL
, ' - ' || HZP_SUB.CITY ) || DECODE(HZP_SUB.STATE
, NULL
, NULL
, ' - ' || HZP_SUB.STATE ) || DECODE(HZP_SUB.POSTAL_CODE
, NULL
, NULL
, ' - ' || HZP_SUB.POSTAL_CODE ) || DECODE(HZP_SUB.COUNTRY
, NULL
, NULL
, ' ' || HZP_SUB.COUNTRY ) SUB_FULL_ADDRESS
, CONT_SUB.EMAIL_ADDRESS SUB_EMAIL_ADDRESS
, HZP_SUB.STATUS SUB_STATUS
, 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_OBJ.CONTACT_POINT_ID OBJ_CONTACT_POINT_ID
, CONT_OBJ.EMAIL_ID OBJ_EMAIL_ID
, CONT_OBJ.PHONE_COUNTRY_CODE OBJ_PHONE_COUNTRY_CODE
, CONT_OBJ.PHONE_AREA_CODE OBJ_PHONE_AREA_CODE
, CONT_OBJ.PHONE_NUMBER OBJ_PHONE_NUMBER
, DECODE(CONT_OBJ.PHONE_AREA_CODE
, NULL
, NULL
, CONT_OBJ.PHONE_AREA_CODE ||'-') || CONT_OBJ.PHONE_NUMBER OBJ_AREA_CODE_PHONE_NUMBER
, CONT_OBJ.PHONE_EXTENSION OBJ_PHONE_EXTENSION
, CONT_OBJ.PHONE_LINE_TYPE OBJ_PHONE_TYPE
, CONT_OBJ.LAST_UPDATE_DATE OBJ_CONT_LAST_UPDATE_DATE
, CONT_OBJ.EMAIL_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_OBJ.EMAIL_ADDRESS OBJ_EMAIL_ADDRESS
, HZPR.PARTY_RELATIONSHIP_TYPE RELATION
, ARL.MEANING RELATION_MEANING
, HZP_OBJ.STATUS OBJ_STATUS
, HZP_OBJ.ATTRIBUTE_CATEGORY OBJ_ATTRIBUTE_CATEGORY
, HZP_OBJ.ATTRIBUTE1 OBJ_ATTRIBUTE1
, HZP_OBJ.ATTRIBUTE2 OBJ_ATTRIBUTE2
, HZP_OBJ.ATTRIBUTE3 OBJ_ATTRIBUTE3
, HZP_OBJ.ATTRIBUTE4 OBJ_ATTRIBUTE4
, HZP_OBJ.ATTRIBUTE5 OBJ_ATTRIBUTE5
, HZP_OBJ.ATTRIBUTE6 OBJ_ATTRIBUTE6
, HZP_OBJ.ATTRIBUTE7 OBJ_ATTRIBUTE7
, HZP_OBJ.ATTRIBUTE8 OBJ_ATTRIBUTE8
, HZP_OBJ.ATTRIBUTE9 OBJ_ATTRIBUTE9
, HZP_OBJ.ATTRIBUTE10 OBJ_ATTRIBUTE10
, HZP_OBJ.ATTRIBUTE11 OBJ_ATTRIBUTE11
, HZP_OBJ.ATTRIBUTE12 OBJ_ATTRIBUTE12
, HZP_OBJ.ATTRIBUTE13 OBJ_ATTRIBUTE13
, HZP_OBJ.ATTRIBUTE14 OBJ_ATTRIBUTE14
, HZP_OBJ.ATTRIBUTE15 OBJ_ATTRIBUTE15
, HZP_OBJ.ATTRIBUTE16 OBJ_ATTRIBUTE16
, HZP_OBJ.ATTRIBUTE17 OBJ_ATTRIBUTE17
, HZP_OBJ.ATTRIBUTE18 OBJ_ATTRIBUTE18
, HZP_OBJ.ATTRIBUTE19 OBJ_ATTRIBUTE19
, HZP_OBJ.ATTRIBUTE20 OBJ_ATTRIBUTE20
FROM HZ_PARTIES HZP_PTY
, HZ_PARTIES HZP_SUB
, HZ_PARTIES HZP_OBJ
, HZ_PARTY_RELATIONSHIPS HZPR
, HZ_CUST_ACCOUNTS HZCA_PTY
, CSC_GS_CONTACTS_V CONT_PTY
, HZ_CUST_ACCOUNTS HZCA_SUB
, CSC_GS_CONTACTS_V CONT_SUB
, HZ_CUST_ACCOUNTS HZCA_OBJ
, CSC_GS_CONTACTS_V CONT_OBJ
, AR_LOOKUPS ARL
WHERE HZP_PTY.PARTY_ID = HZPR.PARTY_ID
AND HZP_PTY.PARTY_ID = HZCA_PTY.PARTY_ID (+)
AND HZP_PTY.PARTY_ID = CONT_PTY.PARTY_ID (+)
AND HZP_SUB.PARTY_ID = HZPR.SUBJECT_ID
AND HZP_SUB.PARTY_ID = HZCA_SUB.PARTY_ID (+)
AND HZP_SUB.PARTY_ID = CONT_SUB.PARTY_ID (+)
AND HZP_OBJ.PARTY_ID = HZPR.OBJECT_ID
AND HZP_OBJ.PARTY_ID = HZCA_OBJ.PARTY_ID (+)
AND HZP_OBJ.PARTY_ID = CONT_OBJ.PARTY_ID (+)
AND HZP_OBJ.PARTY_TYPE = 'ORGANIZATION'
AND ARL.LOOKUP_TYPE = 'PARTY_RELATIONS_TYPE'
AND HZPR.PARTY_RELATIONSHIP_TYPE = ARL.LOOKUP_CODE UNION SELECT HZP.PARTY_ID PTY_PARTY_ID
, HZP.PARTY_NAME PTY_PARTY_NAME
, HZP.PARTY_NUMBER PTY_PARTY_NUMBER
, HZP.PARTY_TYPE PTY_PARTY_TYPE
, HZCA.CUST_ACCOUNT_ID PTY_CUST_ACCOUNT_ID
, HZCA.ACCOUNT_NUMBER PTY_ACCOUNT_NUMBER
, HZCA.ACCOUNT_NAME PTY_ACCOUNT_NAME
, HZCA.LAST_UPDATE_DATE PTY_CUST_ID_LAST_UPDATE_DATE
, HZP.GROUP_TYPE PTY_GROUP_TYPE
, HZP.PERSON_PRE_NAME_ADJUNCT PTY_TITLE
, HZP.PERSON_FIRST_NAME PTY_FIRST_NAME
, HZP.PERSON_MIDDLE_NAME PTY_MIDDLE_NAME
, HZP.PERSON_LAST_NAME PTY_LAST_NAME
, HZP.PERSON_FIRST_NAME || ' ' || HZP.PERSON_LAST_NAME PTY_FULL_NAME
, HZP.LAST_UPDATE_DATE PTY_LAST_UPDATE_DATE
, CONT.CONTACT_POINT_ID PTY_CONTACT_POINT_ID
, CONT.EMAIL_ID PTY_EMAIL_ID
, CONT.PHONE_COUNTRY_CODE PTY_PHONE_COUNTRY_CODE
, CONT.PHONE_AREA_CODE PTY_PHONE_AREA_CODE
, CONT.PHONE_NUMBER PTY_PHONE_NUMBER
, CONT.PHONE_AREA_CODE || CONT.PHONE_NUMBER PTY_AREA_CODE_PHONE_NUMBER
, CONT.PHONE_EXTENSION PTY_PHONE_EXTENSION
, CONT.PHONE_LINE_TYPE PTY_PHONE_TYPE
, CONT.LAST_UPDATE_DATE PTY_CONT_LAST_UPDATE_DATE
, CONT.EMAIL_LAST_UPDATE_DATE PTY_EMAIL_LAST_UPDATE_DATE
, DECODE(HZP.ADDRESS1
, NULL
, NULL
, HZP.ADDRESS1 || DECODE(HZP.ADDRESS2
, NULL
, NULL
, ';' || HZP.ADDRESS2 || DECODE(HZP.ADDRESS3
, NULL
, NULL
, ';' || HZP.ADDRESS3 || DECODE(HZP.ADDRESS4
, NULL
, NULL
, '; ' || HZP.ADDRESS4 )))) PTY_PARTIAL_ADDRESS
, HZP.ADDRESS1 PTY_ADDRESS1
, HZP.ADDRESS2 PTY_ADDRESS2
, HZP.ADDRESS3 PTY_ADDRESS3
, HZP.ADDRESS4 PTY_ADDRESS4
, HZP.CITY PTY_CITY
, HZP.STATE PTY_STATE
, HZP.POSTAL_CODE PTY_POSTAL_CODE
, HZP.PROVINCE PTY_PROVINCE
, HZP.COUNTRY PTY_COUNTRY
, DECODE(HZP.ADDRESS1
, NULL
, NULL
, HZP.ADDRESS1 || DECODE(HZP.ADDRESS2
, NULL
, NULL
, ';' || HZP.ADDRESS2 || DECODE(HZP.ADDRESS3
, NULL
, NULL
, ';' || HZP.ADDRESS3 || DECODE(HZP.ADDRESS4
, NULL
, NULL
, ';' || HZP.ADDRESS4 )))) || DECODE(HZP.CITY
, NULL
, NULL
, ' - ' || HZP.CITY) || DECODE(HZP.STATE
, NULL
, NULL
, ' - ' || HZP.STATE) || DECODE(HZP.POSTAL_CODE
, NULL
, NULL
, ' - ' || HZP.POSTAL_CODE) || DECODE(HZP.COUNTRY
, NULL
, NULL
, ' ' || HZP.COUNTRY ) PTY_FULL_ADDRESS
, CONT.EMAIL_ADDRESS PTY_EMAIL_ADDRESS
, HZP.PARTY_ID SUB_PARTY_ID
, HZP.PARTY_NAME SUB_PARTY_NAME
, HZP.PARTY_NUMBER SUB_PARTY_NUMBER
, HZP.PARTY_TYPE SUB_PARTY_TYPE
, HZCA.CUST_ACCOUNT_ID SUB_CUST_ACCOUNT_ID
, HZCA.ACCOUNT_NUMBER SUB_ACCOUNT_NUMBER
, HZCA.ACCOUNT_NAME SUB_ACCOUNT_NAME
, HZCA.LAST_UPDATE_DATE SUB_CUST_ID_LAST_UPDATE_DATE
, HZP.GROUP_TYPE SUB_GROUP_TYPE
, HZP.PERSON_PRE_NAME_ADJUNCT SUB_TITLE
, HZP.PERSON_FIRST_NAME SUB_FIRST_NAME
, HZP.PERSON_MIDDLE_NAME SUB_MIDDLE_NAME
, HZP.PERSON_LAST_NAME SUB_LAST_NAME
, HZP.PERSON_FIRST_NAME || ' ' || HZP.PERSON_LAST_NAME SUB_FULL_NAME
, HZP.LAST_UPDATE_DATE SUB_LAST_UPDATE_DATE
, CONT.CONTACT_POINT_ID SUB_CONTACT_POINT_ID
, CONT.EMAIL_ID SUB_EMAIL_ID
, CONT.PHONE_COUNTRY_CODE SUB_PHONE_COUNTRY_CODE
, CONT.PHONE_AREA_CODE SUB_PHONE_AREA_CODE
, CONT.PHONE_NUMBER SUB_PHONE_NUMBER
, DECODE(CONT.PHONE_AREA_CODE
, NULL
, NULL
, CONT.PHONE_AREA_CODE ||'-') || CONT.PHONE_NUMBER SUB_AREA_CODE_PHONE_NUMBER
, CONT.PHONE_EXTENSION SUB_PHONE_EXTENSION
, CONT.PHONE_LINE_TYPE SUB_PHONE_TYPE
, CONT.LAST_UPDATE_DATE SUB_CONT_LAST_UPDATE_DATE
, CONT.EMAIL_LAST_UPDATE_DATE SUB_EMAIL_LAST_UPDATE_DATE
, DECODE(HZP.ADDRESS1
, NULL
, NULL
, HZP.ADDRESS1 || DECODE(HZP.ADDRESS2
, NULL
, NULL
, '; ' || HZP.ADDRESS2 || DECODE(HZP.ADDRESS3
, NULL
, NULL
, ';' || HZP.ADDRESS3 || DECODE(HZP.ADDRESS4
, NULL
, NULL
, ';' || HZP.ADDRESS4 )))) SUB_PARTIAL_ADDRESS
, HZP.ADDRESS1 SUB_ADDRESS1
, HZP.ADDRESS2 SUB_ADDRESS2
, HZP.ADDRESS3 SUB_ADDRESS3
, HZP.ADDRESS4 SUB_ADDRESS4
, HZP.CITY SUB_CITY
, HZP.STATE SUB_STATE
, HZP.POSTAL_CODE SUB_POSTAL_CODE
, HZP.PROVINCE SUB_PROVINCE
, HZP.COUNTRY SUB_COUNTRY
, DECODE(HZP.ADDRESS1
, NULL
, NULL
, HZP.ADDRESS1 || DECODE(HZP.ADDRESS2
, NULL
, NULL
, ';' || HZP.ADDRESS2 || DECODE(HZP.ADDRESS3
, NULL
, NULL
, ';' || HZP.ADDRESS3 || DECODE(HZP.ADDRESS4
, NULL
, NULL
, ';' || HZP.ADDRESS4 )))) || DECODE(HZP.CITY
, NULL
, NULL
, ' - ' || HZP.CITY) || DECODE(HZP.STATE
, NULL
, NULL
, ' - ' || HZP.STATE) || DECODE(HZP.POSTAL_CODE
, NULL
, NULL
, ' - ' || HZP.POSTAL_CODE) || DECODE(HZP.COUNTRY
, NULL
, NULL
, ' ' || HZP.COUNTRY ) SUB_FULL_ADDRESS
, CONT.EMAIL_ADDRESS SUB_EMAIL_ADDRESS
, HZP.STATUS SUB_STATUS
, HZP.PARTY_ID OBJ_PARTY_ID
, HZP.PARTY_NAME OBJ_PARTY_NAME
, HZP.PARTY_NUMBER OBJ_PARTY_NUMBER
, HZP.PARTY_TYPE OBJ_PARTY_TYPE
, HZCA.CUST_ACCOUNT_ID OBJ_CUST_ACCOUNT_ID
, HZCA.ACCOUNT_NUMBER OBJ_ACCOUNT_NUMBER
, HZCA.ACCOUNT_NAME OBJ_ACCOUNT_NAME
, HZCA.LAST_UPDATE_DATE OBJ_CUST_ID_LAST_UPDATE_DATE
, HZP.GROUP_TYPE OBJ_GROUP_TYPE
, HZP.PERSON_PRE_NAME_ADJUNCT OBJ_TITLE
, HZP.PERSON_FIRST_NAME OBJ_FIRST_NAME
, HZP.PERSON_MIDDLE_NAME OBJ_MIDDLE_NAME
, HZP.PERSON_LAST_NAME OBJ_LAST_NAME
, HZP.PERSON_FIRST_NAME || ' ' || HZP.PERSON_LAST_NAME OBJ_FULL_NAME
, HZP.LAST_UPDATE_DATE OBJ_LAST_UPDATE_DATE
, CONT.CONTACT_POINT_ID OBJ_CONTACT_POINT_ID
, CONT.EMAIL_ID OBJ_EMAIL_ID
, CONT.PHONE_COUNTRY_CODE OBJ_PHONE_COUNTRY_CODE
, CONT.PHONE_AREA_CODE OBJ_PHONE_AREA_CODE
, CONT.PHONE_NUMBER OBJ_PHONE_NUMBER
, DECODE(CONT.PHONE_AREA_CODE
, NULL
, NULL
, CONT.PHONE_AREA_CODE ||'-') || CONT.PHONE_NUMBER OBJ_AREA_CODE_PHONE_NUMBER
, CONT.PHONE_EXTENSION OBJ_PHONE_EXTENSION
, CONT.PHONE_LINE_TYPE OBJ_PHONE_TYPE
, CONT.LAST_UPDATE_DATE OBJ_CONT_LAST_UPDATE_DATE
, CONT.EMAIL_LAST_UPDATE_DATE OBJ_EMAIL_LAST_UPDATE_DATE
, DECODE(HZP.ADDRESS1
, NULL
, NULL
, HZP.ADDRESS1 || DECODE(HZP.ADDRESS2
, NULL
, NULL
, ';' || HZP.ADDRESS2 || DECODE(HZP.ADDRESS3
, NULL
, NULL
, ';' || HZP.ADDRESS3 || DECODE(HZP.ADDRESS4
, NULL
, NULL
, ';'|| HZP.ADDRESS4 )))) OBJ_PARTIAL_ADDRESS
, HZP.ADDRESS1 OBJ_ADDRESS1
, HZP.ADDRESS2 OBJ_ADDRESS2
, HZP.ADDRESS3 OBJ_ADDRESS3
, HZP.ADDRESS4 OBJ_ADDRESS4
, HZP.CITY OBJ_CITY
, HZP.STATE OBJ_STATE
, HZP.POSTAL_CODE OBJ_POSTAL_CODE
, HZP.PROVINCE OBJ_PROVINCE
, HZP.COUNTRY OBJ_COUNTRY
, DECODE(HZP.ADDRESS1
, NULL
, NULL
, HZP.ADDRESS1 || DECODE(HZP.ADDRESS2
, NULL
, NULL
, ';' || HZP.ADDRESS2 || DECODE(HZP.ADDRESS3
, NULL
, NULL
, ';' || HZP.ADDRESS3 || DECODE(HZP.ADDRESS4
, NULL
, NULL
, ';' || HZP.ADDRESS4 )))) || DECODE(HZP.CITY
, NULL
, NULL
, ' - ' || HZP.CITY) || DECODE(HZP.STATE
, NULL
, NULL
, ' - ' || HZP.STATE) || DECODE(HZP.POSTAL_CODE
, NULL
, NULL
, ' - ' || HZP.POSTAL_CODE) || DECODE(HZP.COUNTRY
, NULL
, NULL
, ' ' || HZP.COUNTRY ) OBJ_FULL_ADDRESS
, CONT.EMAIL_ADDRESS OBJ_EMAIL_ADDRESS
, 'SELF' RELATION
, NULL RELATION_MEANING
, HZP.STATUS OBJ_STATUS
, HZP.ATTRIBUTE_CATEGORY OBJ_ATTRIBUTE_CATEGORY
, HZP.ATTRIBUTE1 OBJ_ATTRIBUTE1
, HZP.ATTRIBUTE2 OBJ_ATTRIBUTE2
, HZP.ATTRIBUTE3 OBJ_ATTRIBUTE3
, HZP.ATTRIBUTE4 OBJ_ATTRIBUTE4
, HZP.ATTRIBUTE5 OBJ_ATTRIBUTE5
, HZP.ATTRIBUTE6 OBJ_ATTRIBUTE6
, HZP.ATTRIBUTE7 OBJ_ATTRIBUTE7
, HZP.ATTRIBUTE8 OBJ_ATTRIBUTE8
, HZP.ATTRIBUTE9 OBJ_ATTRIBUTE9
, HZP.ATTRIBUTE10 OBJ_ATTRIBUTE10
, HZP.ATTRIBUTE11 OBJ_ATTRIBUTE11
, HZP.ATTRIBUTE12 OBJ_ATTRIBUTE12
, HZP.ATTRIBUTE13 OBJ_ATTRIBUTE13
, HZP.ATTRIBUTE14 OBJ_ATTRIBUTE14
, HZP.ATTRIBUTE15 OBJ_ATTRIBUTE15
, HZP.ATTRIBUTE16 OBJ_ATTRIBUTE16
, HZP.ATTRIBUTE17 OBJ_ATTRIBUTE17
, HZP.ATTRIBUTE18 OBJ_ATTRIBUTE18
, HZP.ATTRIBUTE19 OBJ_ATTRIBUTE19
, HZP.ATTRIBUTE20 OBJ_ATTRIBUTE20
FROM HZ_PARTIES HZP
, HZ_CUST_ACCOUNTS HZCA
, CSC_GS_CONTACTS_V CONT
WHERE HZP.PARTY_ID = HZCA.PARTY_ID (+)
AND HZP.PARTY_ID = CONT.PARTY_ID (+)
AND HZP.PARTY_TYPE = 'ORGANIZATION'

Columns