OM:客户区域&地址&联系人&电话之sql

这几天看了trm文档，把table之前的关系搞明白了

1. 客户，区域，地址

SELECT CUST.ACCOUNT_NUMBER 客户编码,
       HP.PARTY_NAME 客户名称,
       RT.SEGMENT1 区域,
       RT.SEGMENT2 小区域,     
       --HPS.IDENTIFYING_ADDRESS_FLAG,
       HL.ADDRESS1  地址
  FROM HZ_CUST_ACCOUNTS       CUST,
       HZ_PARTIES             HP,      
       HZ_CUST_ACCT_SITES_ALL CUST_ACCT,
       HZ_PARTY_SITES         HPS ,
       HZ_LOCATIONS HL ,
       HZ_CUST_SITE_USES_ALL  SU,             
       RA_TERRITORIES         RT
 WHERE CUST.PARTY_ID = HP.PARTY_ID
   AND HP.PARTY_TYPE(+) = 'ORGANIZATION'
   AND CUST_ACCT.CUST_ACCOUNT_ID(+) = CUST.CUST_ACCOUNT_ID
   AND HPS.PARTY_SITE_ID(+) = CUST_ACCT.PARTY_SITE_ID
   AND HPS.IDENTIFYING_ADDRESS_FLAG(+) = 'Y'  --标识地址标识 
   AND HL.LOCATION_ID(+) = HPS.LOCATION_ID 
   AND CUST_ACCT.CUST_ACCT_SITE_ID = SU.CUST_ACCT_SITE_ID(+)
   AND SU.SITE_USE_CODE(+) = 'SHIP_TO' --发货地点
   AND SU.Primary_Flag(+) = 'Y'   
   AND SU.TERRITORY_ID = RT.TERRITORY_ID (+) 
   --AND HCA.ACCOUNT_NUMBER='5698'

2. 根据site_use_id获取客户联系人

select decode(acct_role.cust_account_role_id,
        null,
        null,
        substrb(party.person_last_name, 1, 50) || ', ' ||
        substrb(party.person_first_name, 1, 40)) contact_name
       -- into p_customer_contact
from  hz_cust_site_uses_all   su,     
      hz_cust_account_roles   acct_role,
      hz_parties              party,
      hz_relationships        rel
 where 1=1
   and su.contact_id = acct_role.cust_account_role_id(+)
   and acct_role.party_id = rel.party_id(+)
   and rel.subject_table_name(+) = 'HZ_PARTIES'
   and rel.object_table_name(+) = 'HZ_PARTIES'
   and rel.directional_flag(+) = 'F'
   and acct_role.role_type(+) = 'CONTACT'
   and rel.subject_id = party.party_id(+)
   and su.site_use_id=p_site_use_id; --orders.ship_to_org;

 

3.--根据site_use_id获取客户telephone
    
  select phone.phone_country_code || '-' || phone.phone_area_code || '-' ||phone.phone_number
      --   into p_customer_telphone
  from  hz_cust_site_uses_all  site,
         hz_cust_acct_sites_all acct_site,
         hz_party_sites         party_site,
         hz_contact_points phone
  where 1=1
    and acct_site.party_site_id = party_site.party_site_id
    and acct_site.cust_acct_site_id = site.cust_acct_site_id
    and site.site_use_code = 'SHIP_TO'
    and site.status = 'A'
    and acct_site.status = 'A'
    and phone.owner_table_name(+) = 'HZ_PARTY_SITES'
    and phone.owner_table_id(+) = party_site.party_site_id
    and phone.status(+)='A'
    and phone.phone_line_type(+)='GEN'
    AND phone.PRIMARY_FLAG(+) = 'Y'
    and site.site_use_id=p_site_use_id; --ship_to_org;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/41594/viewspace-674117/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/41594/viewspace-674117/