VIEW: APPS.CSC_GS_CUSTOMERS_ORG_ADV_V

Source

View Text - Preformatted

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.relationship_code 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_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.relationship_code = arl.lookup_code and hzpr.subject_table_name = 'HZ_PARTIES' and hzpr.object_table_name = 'HZ_PARTIES' and hzpr.directional_flag = 'F' 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'

View Text - HTML Formatted

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.RELATIONSHIP_CODE 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_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.RELATIONSHIP_CODE = ARL.LOOKUP_CODE
AND HZPR.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND HZPR.OBJECT_TABLE_NAME = 'HZ_PARTIES'
AND HZPR.DIRECTIONAL_FLAG = 'F' 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'