select lp.*
from(
SELECT l.attribute1 project,l.attribute2 ptype ,litem.item
, PARTY.PARTY_NAME customerName, L.TOTAL_AMOUNT totalAmount, L.WIN_PROBABILITY winProb
, L.STATUS status, L.LEAD_ID leadID, L.SALES_METHODOLOGY_ID methodologyCode
, 'DUMMY' updateableFlag, L.DECISION_DATE decisionDate
, L.CURRENCY_CODE currency, PARTY.PARTY_TYPE customerType
, L.SALES_STAGE_ID salesStageID
, assv.stagename
, L.LAST_UPDATE_DATE rupdatedDate, L.DESCRIPTION oppDescription
,L.Org_Id
,L.Attribute7 price
,L.Attribute8 num
,L.Attribute9 lifecycle
,L.Total_Amount
,L.Creation_Date
,jrs.person_party_id
,sales_resp.sales_resp_id
,sales_resp.sales_resp_name
,Party.Party_Id
,Party.Party_Number
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
,(
select a.LEAD_ID ,max(CONCATENATED_SEGMENTS) item from
(
SELECT CONCATENATED_SEGMENTS , QUANTITY , UNIT_OF_MEASURE_TL , SOURCE_PROMOTION_ID , CAMPAIGN_CODE , TOTAL_AMOUNT OFFER_CODE , LEAD_ID , OFFER_ID , ITEM_DESCRIPTION
FROM AS_OPPORTUNITY_LINES_V
) a group by a.LEAD_ID
) litem
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 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=litem.LEAD_ID
) lp
order by lp.project
