Customer contact points query

select account_number "Account Number"

     , obj.party_name "Customer Name"

     , sub.party_name "Contact Name"

     , hcp.contact_point_type || ': ' ||

       DECODE(hcp.contact_point_type, 'EMAIL', hcp.email_address

                                    , 'PHONE', hcp.phone_area_code || ' ' || hcp.phone_number

                                    , 'WEB'  , hcp.url

                                    , 'Unknow contact Point Type ' || hcp.contact_point_type

             ) "How to Contact"

  from apps.hz_cust_accounts  hca

     , apps.hz_parties        obj

     , apps.hz_relationships  rel

     , apps.hz_contact_points hcp

     , apps.hz_parties        sub

 where hca.party_id           = rel.object_id

   and hca.party_id           = obj.party_id

   and rel.subject_id         = sub.party_id

   and rel.relationship_type  = 'CONTACT'

   and rel.directional_flag   = 'F'

   and rel.party_id           = hcp.owner_table_id

   and hcp.owner_table_name   = 'HZ_PARTIES'

;

select account_number "Account Number"

     , hp.party_name  "Customer Name"

     , hcp.contact_point_type || ': ' ||

       DECODE(hcp.contact_point_type, 'EMAIL', hcp.email_address

                                    , 'PHONE', hcp.phone_area_code || ' ' || hcp.phone_number

                                    , 'WEB'  , hcp.url

                                    , 'Unknow contact Point Type ' || hcp.contact_point_type

             ) "How to Contact"

  from apps.hz_cust_accounts hca

     , apps.hz_parties       hp

     , apps.hz_contact_points hcp

 where hca.party_id          = hp.party_id

   and hp.party_id          = hcp.owner_table_id

   and hcp.owner_table_name = 'HZ_PARTIES'

;

select account_number "Account Number"

     , hp.party_name  "Customer Name"

     , loc.address1 ||

       decode(loc.address2, null, null, ', ' || loc.address2) ||

       decode(loc.address3, null, null, ', ' || loc.address3) ||

       decode(loc.address4, null, null, ', ' || loc.address4) ||

       ' ' || loc.city || ', ' || loc.state || ' ' || loc.postal_code "Address"

     , hcp.contact_point_type || ': ' ||

       DECODE(hcp.contact_point_type, 'EMAIL', hcp.email_address

                                    , 'PHONE', hcp.phone_area_code || ' ' || hcp.phone_number

                                    , 'WEB'  , hcp.url

                                    , 'Unknow contact Point Type ' || hcp.contact_point_type

             ) "How to Contact"

  from apps.hz_cust_accounts hca

     , apps.hz_parties       hp

     , apps.hz_cust_acct_sites_all cas

     , apps.hz_contact_points      hcp

     , apps.hz_party_sites         hps

     , apps.hz_locations           loc

 where hca.cust_account_id   = cas.cust_account_id

   and hca.party_id          = hp.party_id

   and hcp.owner_table_id    = cas.party_site_id

   and hcp.owner_table_name  = 'HZ_PARTY_SITES'

   and cas.party_site_id     = hps.party_site_id

   and hps.location_id       = loc.location_id

;