VIEW: APPS.CSC_GS_CUSTOMERS_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.telephone_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.telephone_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_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.telephone_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 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 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.telephone_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.telephone_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.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.telephone_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 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 (+) /* for Bug#1376777 and NOT EXISTS ( select 1 from hz_relationships hpr where hzp.party_id IN (hpr.party_id, hpr.subject_id, hpr.object_id ) ) */

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.TELEPHONE_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.TELEPHONE_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_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.TELEPHONE_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
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 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.TELEPHONE_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.TELEPHONE_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.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.TELEPHONE_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
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 (+) /* FOR BUG#1376777
AND NOT EXISTS ( SELECT 1
FROM HZ_RELATIONSHIPS HPR
WHERE HZP.PARTY_ID IN (HPR.PARTY_ID
, HPR.SUBJECT_ID
, HPR.OBJECT_ID ) ) */