/ Oracle EBS

EBS R12 取得客戶的phone, email, URL資料

資料來源：這裡

聯絡方式(Contact point)可以被新增於客戶主檔的三個層級：客戶層、地址層、聯絡人.

客戶聯絡人(contacts)的 phone, email, URL：

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';

客戶層級的 Customer’s phone, email and URL:

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';

地址層級的 Phone, Email and URL：

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;