--加CRM ERP的状态
SELECT
cust.CRM_CUST_NUMBER
,cust.ERP_CUST_NUMBER
,cust.ORGNAME
,cust.org_name_en
,cust.PROVINCE
,cust.city
,L.LEAD_ID
,L.DESCRIPTION oppDescription
,l.attribute1 project
,dit_mtl_v.item_code
,cust.sc_industry --客户行业
,cust.sc_sort --客户类型
,TO_NUMBER(ssdv.price)*TO_NUMBER(ssdv.num)*12 Potential --年潜能
,ssdv.stagename
,ssdv.status
,ssdv.num --数量
,ssdv.price --单价
,ssdv.lifecycle --生命周期
,ssdv.num*ssdv.price*ssdv.lifecycle AMOUNT --业务规模
,L.Creation_Date --创建日期
,L.LAST_UPDATE_DATE rupdatedDate --最后修改日期
,L.Attribute5 send_date --送样日期
,L.DECISION_DATE decisionDate --DIT关闭日期
,L.Attribute6 --量产日期
,dit_mtl_v.SEGMENT1
,dit_mtl_v.SEGMENT2
,jrs.source_last_name --sales_resp.sales_resp_name jrs.person_party_id为空，再关联员工表关联不出来 如 136430
,seh.belong_department
,l.attribute2 project_type
,ssdv.status_name
,jrs.person_party_id
,sales_resp.sales_resp_id
,L.CURRENCY_CODE currency
FROM AS_LEADS_ALL L,
HZ_PARTIES PARTY,
AS_PERIOD_RATES R,
AS_PERIOD_DAYS D ,
JTF_RS_RESOURCE_EXTNS jrs,
(--创建人姓名
select per.party_id,per.person_id sales_resp_id,per.last_name sales_resp_name,per.full_name,fu.user_id
from
(--查询有效的用户
select per.party_id, per.person_id,per.last_name,per.full_name
from PER_ALL_PEOPLE_F per
where sysdate between per.effective_start_date and per.effective_end_date
) per,
(--取有效的登陆用户
select person_party_id,user_id,user_name from FND_USER
where end_date is null and 1= case when user_name='SIE_CS' then 0 else 1 end --修改用户不取 SIE 因为对应同一个管理员会有person_party_id记录重复
) fu
where per.party_id=fu.person_party_id(+)
) sales_resp,
(--DIT阶段名称
select sales_stage_id,name stagename
from as_sales_stages_all_tl ass
where ass.language = userenv('lang')
) assv ,
secom_employe_hr_dept seh,
(--关联DIT对应的物料
select LEAD_ID,item_code,SEGMENT1,SEGMENT2 from
( select ao.LEAD_LINE_ID,ao.LEAD_ID,ao.INVENTORY_ITEM_ID
,mtl.item_code ,mtl.SEGMENT1,mtl.SEGMENT2,ao.creation_date
, row_number() over(PARTITION BY ao.LEAD_ID ORDER BY ao.LEAD_ID ) rn
from AS_LEAD_LINES_ALL ao
,(select mc.INVENTORY_ITEM_ID ,ms.SEGMENT1 item_code ,mc.SEGMENT1,mc.SEGMENT2
from MTL_ITEM_CATEGORIES_V mc ,
mtl_system_items_b ms,
( --关联有效的厂牌、产品线与之对应的业务类型
select scv.ITEM_CATEGORY_SEGMENT1,scv.ITEM_CATEGORY_SEGMENT2,scv.DEPARTMENT
from SECOM_COMPARISON_V scv where status_Code='VALID'
)scv
where mc.INVENTORY_ITEM_ID=ms.inventory_item_id(+)
and mc.ORGANIZATION_ID=ms.organization_id(+)
and mc.SEGMENT1=scv.ITEM_CATEGORY_SEGMENT1(+)
and mc.SEGMENT2=scv.ITEM_CATEGORY_SEGMENT2(+)
and ms.organization_id=84
and mc.CATEGORY_SET_ID=1
--and scv.DEPARTMENT is not null --业务类型实际不会为空 (以致部分物料不能显示)
) mtl
where ao.INVENTORY_ITEM_ID=mtl.INVENTORY_ITEM_ID(+)
) dit_mtl
where rn=1
)dit_mtl_v,
(--关联旧的DIT信息
select ssd.leadid ,ssd.price,ssd.num,ssd.lifecycle, ssd.status, ast.meaning status_name ,ssd.stagename
from secom.secom_cv_dit_data ssd
,(
select ast.status_code,ast.meaning from AS_STATUSES_TL ast where ast.language='ZHS'
) ast
where ssd.status=ast.status_code(+) and ssd.period='2012-05'
) ssdv,
--关联客户信息
secom.secom_tmp_dit_customer cust
WHERE ( L.CUSTOMER_ID = PARTY.PARTY_ID AND L.CURRENCY_CODE = R.FROM_CURRENCY
AND R.TO_CURRENCY = 'USD' AND R.PERIOD_NAME = D.PERIOD_NAME AND L.DECISION_DATE = D.PERIOD_DAY
and L.OWNER_SALESFORCE_ID=jrs.RESOURCE_ID(+)
and jrs.source_id=sales_resp.sales_resp_id(+) --2012-06-06 zhenfeng 直接改成通过员工iD关联 原 and jrs.person_party_id=sales_resp.party_id(+)
and sales_resp.sales_resp_id=seh.person_id(+)
and l.lead_id=dit_mtl_v.LEAD_ID(+)
and l.lead_id=ssdv.leadid(+)
and L.CUSTOMER_ID=cust.party_id(+)
and L.SALES_STAGE_ID=assv.sales_stage_id
AND D.PERIOD_TYPE = 'Month' AND R.CONVERSION_TYPE = 'Corporate' AND R.PERIOD_SET_NAME = '世强会计日历'
AND D.PERIOD_SET_NAME = '世强会计日历' )
and l.lead_id in (
select ssd.leadid from secom.secom_cv_dit_data ssd where period='2012-05'
)
--===========
--客户信息
truncate table secom.secom_tmp_dit_customer
drop table secom.secom_tmp_dit_customer
create table secom.secom_tmp_dit_customer
as
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.ORGANIZATION_NAME_PHONETIC org_name_en,
hp.CATEGORY_CODE,
cust_class.sc_sort,
cust_class.sc_industry,
cust_address.PROVINCE,
cust_address.city,
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 in
('CLIENT_SORT08', 'CLIENT_SORT09') --取贸易商编码、内部客户
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 PARTY_ID, PROVINCE, City
from (SELECT SITE.PARTY_ID,
LOC.PROVINCE,
LOC.City,
row_number() over(PARTITION BY SITE.PARTY_ID ORDER BY SITE.PARTY_ID) rn
--,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' --取主要地址的省份
) hpad
where hpad.rn = 1) 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
--zhenfeng 2012-04-31 如果有CRM又有ERP编码的客户，需均取有效的
--case when hca.status='A' then 1 else 0 end
/*zhenfeng 2012-04-31 如果有CRM又有ERP编码的客户，需均取有效的
*/
case when hp.PARTY_NUMBER in (131244, 140535, 1678, 202426, 2398, 2521, 2531, 63331, 92954, 244758, 250916, 2984,253259) --一个CRM编码有多个重复的ERP编码，针对重复的只取有效的
then case when hca.status = 'A' then 1 else 0 end else 1 end end
