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