客户账户层联系人信息：联系人、电话、手机和 Email S代码,OracleEBS代码库,德仔网

代码语言

代码分类

【OracleEBS】客户账户层联系人信息：联系人、电话、手机和 Email S

作者:Dezai.CN / 发布于2012/12/5/ 975

- 客户账户层联系人信息：联系人、电话、手机和 Email SQL
SELECT hr.party_id party_id,
       hcar.cust_account_id cust_account_id,
       hcar.cust_acct_site_id cust_acct_site_id,
       hp.person_last_name || ' ' || hp.person_middle_name || ' ' ||
       hp.person_first_name contact_person,
       hcpp.phone_area_code phone_area_code,
       hcpp.phone_number phone_number,
       hcpp.phone_extension phone_extension,
       hcpm.phone_area_code mobile_phone_area_code,
       hcpm.phone_number mobile_phone_number,
       hcpe.email_address email_address
  FROM hz_relationships      hr,
       hz_cust_account_roles hcar,
       hz_org_contacts       hoc,
       hz_contact_points     hcpp,
       hz_contact_points     hcpm,
       hz_contact_points     hcpe,
       hz_parties            hp,
       hz_cust_accounts      hca
  WHERE hr.object_id = hp.party_id
   AND hr.party_id = hcar.party_id
   AND hr.relationship_id = hoc.party_relationship_id(+)
   AND hcpp.owner_table_id(+) = hr.party_id
   AND hcpm.owner_table_id(+) = hr.party_id
   AND hcpe.owner_table_id(+) = hr.party_id
   AND hr.object_type = 'PERSON'
   AND hr.relationship_code(+) = 'CONTACT'
   AND hcpp.owner_table_name(+) = 'HZ_PARTIES'
   AND hcpm.owner_table_name(+) = 'HZ_PARTIES'
   AND hcpe.owner_table_name(+) = 'HZ_PARTIES'
   AND hcpp.contact_point_type(+) = 'PHONE'
   AND hcpp.phone_line_type(+) = 'GEN'
   AND hcpm.contact_point_type(+) = 'PHONE'
   AND hcpm.phone_line_type(+) = 'MOBILE'
   AND hcpe.contact_point_type(+) = 'EMAIL'
   AND hcpe.phone_line_type IS NULL
   AND hr.subject_id = hca.party_id
   AND hcar.cust_acct_site_id IS NULL
  ORDER BY hr.creation_date;

试试其它关键字

同语言下

可能有用的

Dezai.CN贡献的其它代码(4037)