(--客户需排除 贸易商、内部客户、代理商、佣金客户、竟争对手、供应商
SELECT hp.party_id, hca.cust_account_id customer_id
, hp.PARTY_NUMBER CRM_CUST_NUMBER ,hp.PARTY_NAME customer_name , hca.ACCOUNT_NUMBER customer_number
from HZ_PARTIES hp, --CRM客户
HZ_CUST_ACCOUNTS hca, --ERP客户
(--取CLIENT_SORT08贸易商编码、CLIENT_SORT09内部客户、CLIENT_SORT06代理商、CLIENT_SORT07佣金客户
SELECT ASSIGN.OWNER_TABLE_ID PARTY_ID
FROM HZ_CODE_ASSIGNMENTS ASSIGN
, AS_HZ_CLASS_CODE_DENORM CODE
WHERE ASSIGN.CLASS_CODE = CODE.CLASS_CODE AND CODE.CLASS_CODE = CODE.ANCESTOR_CODE
AND CODE.LANGUAGE = userenv('LANG') AND ASSIGN.CLASS_CATEGORY = CODE.CLASS_CATEGORY
AND ASSIGN.OWNER_TABLE_NAME = 'HZ_PARTIES'
AND ASSIGN.CLASS_CATEGORY = 'SECOM_CLIENT_SORT' --判断是否为贸易商大类
AND ASSIGN.STATUS = 'A' and ASSIGN.End_Date_Active is null --类别为有效而且有效结束日期为空的
AND ASSIGN.CLASS_CODE in ('CLIENT_SORT08','CLIENT_SORT09','CLIENT_SORT06','CLIENT_SORT07') --取CLIENT_SORT08贸易商编码、CLIENT_SORT09内部客户、CLIENT_SORT06代理商、CLIENT_SORT07佣金客户
group by ASSIGN.OWNER_TABLE_ID
) sst
WHERE hp.PARTY_ID= hca.PARTY_ID(+)
AND hp.party_id=sst.PARTY_ID(+)
AND sst.PARTY_ID IS NULL --需排除 贸易商、内部客户、代理商、佣金客户
AND hp.CATEGORY_CODE in('PROSPECT','CUSTOMER') --只取PROSPECT 潜在客户 CUSTOMER 客户 ；排除 VENDOR 供应商 COMPETITOR OR 竟争对手
) rc
