Customer Contact Details

select hr.relationship_id,

       hr.subject_id,

       hr.subject_type,      

       hr.subject_table_name,

       hr.object_id,

       hr.object_type,      

       hr.object_table_name,

       hr.party_id rel_party_id,

       hr.relationship_code,      

       hr.comments rel_comments,

       hr.start_date,

       hr.end_date,      

       hr.status rel_status,

       hr.relationship_type,      

       hr.created_by_module rel_created_by_module,

       hr.percentage_ownership,      

       hr.content_source_type rel_content_source_type,      

       hr.actual_content_source rel_actual_content_source,      

       rel_hp.party_number rel_party_number,

       hoc.org_contact_id,      

       hoc.party_relationship_id,

       hoc.comments org_cont_comments,      

       hoc.contact_number,

       hoc.department_code,

       hoc.department,

       hoc.title,      

       hoc.job_title,

       hoc.mail_stop,      

       hoc.decision_maker_flag,      

       hoc.job_title_code,

       hoc.reference_use_flag,

       hoc.RANK,      

       hcar.cust_account_role_id,

       hcar.party_id acct_role_party_id,      

       hcar.cust_account_id acct_role_cust_account_id,      

       hcar.cust_acct_site_id acct_role_cust_acct_site_id,      

       hcar.primary_flag acct_role_primary_flag,

       hcar.role_type,      

       hcar.source_code acct_role_source_code,

       hcar.status acct_role_status,      

       hcar.created_by_module acct_role_created_by_module,

       hp.party_id,      

       hp.party_number,

       hp.party_name,

       hp.party_type,

       hp.validated_flag,      

       hp.salutation par_salutation,

       hp.status par_status,      

       hpp.person_profile_id,

       hpp.party_id per_party_id,

       hpp.person_name,      

       hpp.person_pre_name_adjunct,

       hpp.person_first_name,      

       hpp.person_middle_name,

       hpp.person_last_name,

       hpp.person_name_suffix,      

       hpp.person_title,

       hpp.person_academic_title,      

       hpp.person_previous_last_name,

       hpp.person_initials,      

       hpp.known_as per_known_as,

       hpp.person_name_phonetic,      

       hpp.person_first_name_phonetic,

       hpp.person_last_name_phonetic,      

       hpp.tax_reference per_tax_reference,      

       hpp.jgzz_fiscal_code per_jgzz_fiscal_code,

       hpp.person_iden_type,      

       hpp.person_identifier,

       hpp.date_of_birth,

       hpp.place_of_birth,      

       hpp.date_of_death,

       hpp.gender,

       hpp.declared_ethnicity,      

       hpp.marital_status,

       hpp.marital_status_effective_date,      

       hpp.personal_income,

       hpp.head_of_household_flag,

       hpp.household_income,      

       hpp.household_size,

       hpp.rent_own_ind per_rent_own_ind,      

       hpp.last_known_gps,

       hpp.known_as2 per_known_as2,      

       hpp.known_as3 per_known_as3,

       hpp.known_as4 per_known_as4,      

       hpp.known_as5 per_known_as5,

       hpp.middle_name_phonetic,      

       hpp.created_by_module per_created_by_module,      

       hpp.actual_content_source per_actual_content_source,      

       hpp.internal_flag internal_flag

  FROM hz_parties hp,      

       hz_parties rel_hp,     

       hz_person_profiles hpp,      

       hz_relationships hr,      

       hz_org_contacts hoc,      

       hz_cust_account_roles hcar

 WHERE hoc.party_relationship_id = hr.relationship_id

   AND hr.subject_id             = hp.party_id

   AND rel_hp.party_id           = hr.party_id

   AND hp.party_id               = hpp.party_id(+)  

   AND hpp.content_source_type(+) = user_entered

   AND hpp.effective_end_date IS NULL  

   AND rel_hp.party_id           = hcar.party_id(+)  

   AND hoc.party_relationship_id = hr.relationship_id

   AND hr.subject_table_name     = 'HZ_PARTIES'  

   AND hr.subject_type           = 'PERSON'  

   AND hr.relationship_code      = 'CONTACT_OF'  

   AND hcar.cust_account_id      = vl_cust_account_id

   AND hcar.cust_acct_site_id    = vl_acct_site_id