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
,sales_resp.sales_resp_name
,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-03'
) 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.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-03'
)
--===========
--DIT状态的含意
select ast.status_code,ast.meaning from AS_STATUSES_TL ast where ast.language='ZHS'
and ast.status_code in(
'ACCOMPALISHMENT'
,'ACTIVE_1'
,'ACTIVE_2'
,'CANCELLATION'
,'FAILURE'
,'SUCCESS')
--===========
--客户信息
select * from 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-03-31 如果有CRM又有ERP编码的客户，需均取有效的
--case when hca.status='A' then 1 else 0 end
/*zhenfeng 2012-03-31 如果有CRM又有ERP编码的客户，需均取有效的
*/
case when hp.PARTY_NUMBER in( 131244,140535,1678,202426,2398,2521,2531,63331,92954,244758,250916 ) --一个CRM编码有多个重复的ERP编码，针对重复的只取有效的
then
case when hca.status='A' then 1 else 0 end
else 1 end
end
