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

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

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-1038433/，如需转载，请注明出处，否则将追究法律责任。

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