VIEW: APPS.CSC_GS_CUSTOMERS_GRP_V

Source

View Text - Preformatted

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 = 'GROUP' 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'

View Text - HTML Formatted

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 = 'GROUP'
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'