select hp.status, hp.party_id, hca.cust_account_id, hp.PARTY_NUMBER CRM_CUST_NUMBER, hca.ACCOUNT_NUMBER ERP_CUST_NUMBER
,hp.PARTY_NAME ORGNAME
,hp.CATEGORY_CODE
,cust_class.sc_sort
,cust_class.sc_industry
,cust_address.PROVINCE
,hp.curr_fy_potential_revenue annual_rec --年收入
,hp.employees_total --员工总数
from HZ_PARTIES hp, --CRM客户
HZ_CUST_ACCOUNTS hca, --ERP客户
(--取贸易商的数据
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='CLIENT_SORT08' --贸易商编码
group by ASSIGN.OWNER_TABLE_ID
) sst,
(--取得客户的类别和行业信息
select secom_client_v.PARTY_ID
,max( case when secom_client_v.CLASS_CATEGORY='SECOM_CLIENT_SORT' then secom_client_v.Concat_Class_Code_Meaning end) sc_sort
,max(case when secom_client_v.CLASS_CATEGORY='SECOM_CLIENT_INDUSTRY' then secom_client_v.Concat_Class_Code_Meaning end) sc_industry
from
(
select SECOM_CLIENT.PARTY_ID, SECOM_CLIENT.CLASS_CATEGORY
,ltrim( max(sys_connect_by_path(SECOM_CLIENT.Concat_Class_Code_Meaning , ',') ),',') Concat_Class_Code_Meaning
from
(
SELECT ASSIGN.OWNER_TABLE_ID PARTY_ID, ASSIGN.CLASS_CATEGORY,CODE.Concat_Class_Code_Meaning --需要连起来全名的
, row_number() over(PARTITION BY ASSIGN.OWNER_TABLE_ID, ASSIGN.CLASS_CATEGORY
ORDER BY ASSIGN.OWNER_TABLE_ID, ASSIGN.CLASS_CATEGORY ) rn
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 in( 'SECOM_CLIENT_SORT','SECOM_CLIENT_INDUSTRY') --判断是否为贸易商大类
AND ASSIGN.STATUS = 'A' and ASSIGN.End_Date_Active is null --类别为有效而且有效结束日期为空的
) SECOM_CLIENT
START WITH rn = 1
CONNECT BY PRIOR rn = rn - 1
AND SECOM_CLIENT.PARTY_ID = PRIOR SECOM_CLIENT.PARTY_ID
AND SECOM_CLIENT.CLASS_CATEGORY = PRIOR SECOM_CLIENT.CLASS_CATEGORY
group by SECOM_CLIENT.PARTY_ID,SECOM_CLIENT.CLASS_CATEGORY
) secom_client_v
group by secom_client_v.PARTY_ID
) cust_class,
( --地址信用
SELECT SITE.PARTY_ID, max(LOC.PROVINCE) PROVINCE
--,SITE_USE.SITE_USE_TYPE
FROM HZ_PARTY_SITES SITE
,HZ_PARTY_SITE_USES SITE_USE
,HZ_LOCATIONS LOC
,HZ_CONTACT_POINTS PHONE
WHERE (nvl(SITE.STATUS,'A') = 'A')
AND SITE.STATUS IN ('A','I') AND SITE.LOCATION_ID = LOC.LOCATION_ID
AND SITE.PARTY_SITE_ID = SITE_USE.PARTY_SITE_ID(+)
AND NVL(SITE_USE.STATUS(+),'A') = 'A'
AND NVL(SITE_USE.END_DATE(+), SYSDATE) >= SYSDATE AND SITE.PARTY_SITE_ID = PHONE.OWNER_TABLE_ID(+)
AND PHONE.OWNER_TABLE_NAME(+) = 'HZ_PARTY_SITES' AND PHONE.CONTACT_POINT_TYPE(+) = 'PHONE'
AND PHONE.PRIMARY_FLAG(+) = 'Y' AND DECODE(PHONE.STATUS(+),'A','1','I','1') = '1'
and SITE.IDENTIFYING_ADDRESS_FLAG='Y' --取主要地址的省份
group by SITE.PARTY_ID
) cust_address
where hp.PARTY_ID= hca.PARTY_ID(+)
and hp.party_id=sst.PARTY_ID(+)
and hp.party_id=cust_class.PARTY_ID(+)
and hp.party_id=cust_address.PARTY_ID(+)
AND hp.STATUS IN ('A')
AND hp.PARTY_TYPE = 'ORGANIZATION'
and sst.PARTY_ID is null --关联后为空则为非贸易商 (PROSPECT 潜在客户 CUSTOMER 客户)
and hp.CATEGORY_CODE in('PROSPECT','CUSTOMER') --PROSPECT 潜在客户 CUSTOMER 客户 VENDOR 供应商 COMPETITOR OR 竟争对手
and 1=case when hca.account_number is null then 1
else
case when hp.PARTY_NUMBER in( 131244,140535,1678,202426,2398,2521,2531,63331,92954 ) --一个CRM编码有多个重复的ERP编码，针对重复的只取有效的
then
case when hca.status='A' then 1 else 0 end
else 1 end
end
and hp.PARTY_NAME like '%西安新邮通信设备有限公司%'
