oracle销售联系人,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;