SELECT hp.party_name "客户名称", --客户名称
hca.account_number "客户编号", --客户编号
hca.cust_account_id "客户ID", --客户cust_acc_id
decode(hca.customer_type,
'I',
'内部客户',
'R',
'外部客户',
hca.customer_type) "客户类型",
hca.customer_class_code "客户分类CODE",
flv.meaning "客户分类",
hca.price_list_id "价目表ID",
cux_om_converged_pub.get_price_list_name(p_list_header_id => hca.price_list_id) "价目表",
hps.attribute1 "停靠站",
hps.attribute2 "路线",
hps.party_site_id "客户PARTY_SITE_ID", --客户party_site_id
hcsu.site_use_code "客户地点用途",
decode(hl.country, 'CN', '中国', hl.country) "客户国家", --国家
hl.state "客户省份", --省
hl.city "客户城市", --市
hl.address1 "客户地址1", --地址行1
hl.address2 "客户地址2", --地址行2
amt.overall_credit_limit "信用限额",
hou.name "业务实体",
----
hp2.party_name "联系人名称",
decode(hl3.country, 'CN', '中国', hl3.country) "联系人国家", --国家
hl3.state "联系人省份", --省
hl3.city "联系人城市", --市
hl3.address1 "联系人地址1", --地址行1
hl3.address2 "联系人地址2", --地址行2
hp3.primary_phone_number 联系人电话
/* COUNT(1)*/
FROM --客户
hz_party_sites hps, --客户 party_sites
hz_parties hp, --客户party
hz_locations hl, --客户 locations
hz_cust_accounts hca,
hz_cust_site_uses_all hcsu,
hz_cust_acct_sites_all hcas,
hr_operating_units hou,
----客户联系人，联系方式
hz_cust_account_roles hcar,
hz_parties hp2, --客户联系人 party
hz_parties hp3, --客户联系方式 party
hz_relationships hr,
hz_locations hl3, --客户联系方式 locations
hz_party_sites hps3, --客户联系方式 party_sites
hz_org_contacts hoc, --仅用于显示职称
--客户配置文件
hz_cust_profile_amts amt,
hz_customer_profiles pro,
---
fnd_lookup_values_vl flv
WHERE --客户信息关联
hps.party_id = hp.party_id
AND hps.location_id = hl.location_id
AND hca.party_id = hps.party_id
AND hcsu.cust_acct_site_id = hcas.cust_acct_site_id
AND hcas.party_site_id = hps.party_site_id
AND hcsu.org_id = hou.organization_id
--客户联系人，联系方式关联
AND hca.cust_account_id = hcar.cust_account_id
AND hcas.cust_acct_site_id = hcar.cust_acct_site_id
AND hcar.role_type = 'CONTACT'
AND hcar.party_id = hr.party_id
AND hp3.party_id = hr.party_id
AND hr.subject_id = hp2.party_id
AND hr.directional_flag = 'F'
AND hp2.party_type = 'PERSON'
AND hoc.party_relationship_id = hr.relationship_id
AND hps3.party_id = hp3.party_id
AND hps3.location_id = hl3.location_id
--客户配置文件 关联
AND amt.cust_account_profile_id = pro.cust_account_profile_id
AND pro.cust_account_id = hca.cust_account_id
---
AND flv.lookup_type = 'CUSTOMER CLASS'
AND trunc(SYSDATE) BETWEEN nvl(flv.start_date_active, trunc(SYSDATE) - 1) AND
nvl(flv.end_date_active, SYSDATE)
AND flv.enabled_flag = 'Y'
AND flv.lookup_code = hca.customer_class_code;
/* SELECT * FROM HZ_CUST_ACCOUNT_ROLES
*/