SELECT ala.LEAD_ID,
ala.DESCRIPTION,
ala.CUSTOMER_ID,
ala.ADDRESS_ID,
ala.ATTRIBUTE1,
ala.ATTRIBUTE10,
ala.ATTRIBUTE4,
ala.STATUS,
ala.CHANNEL_CODE,
ala.ATTRIBUTE5,
ala.CLOSE_REASON,
ala.ATTRIBUTE6,
to_char(ala.DECISION_DATE, 'yyyy-mm-dd hh24:mi:ss') DECISION_DATE,
ala.WIN_PROBABILITY,
ala.ATTRIBUTE8,
ala.ATTRIBUTE9,
ala.ATTRIBUTE7,
ala.TOTAL_AMOUNT,
ala.ATTRIBUTE12,
ala.ATTRIBUTE13,
ala.ATTRIBUTE14,
ass.name sales_stages,
oas.meaning,
jt.source_id,
fu.user_name sales_name,
aaa.INTERNAL_UPDATE_ACCESS,
aaa.sales_group_id,
(select line.inventory_item_id from secom.oa_as_lead_lines_all line
where line.lead_id = ala.lead_id
and rownum = 1) inventory_item_id,
ala.ATTRIBUTE8*ala.ATTRIBUTE9*ala.ATTRIBUTE7 AMOUNT,
to_number(ala.ATTRIBUTE8) MONTH_QUANTITY
FROM secom.oa_as_accesses_all_all aaa,
secom.oa_as_leads_all ala,
secom.oa_jtf_rs_resource_extns jt,
SECOM.OA_AS_STATUSES_TL OAS,
SECOM.OA_AS_SALES_STAGES_ALL_TL ass,
secom.oa_fnd_user fu
WHERE aaa.delete_flag(+) IS NULL
AND aaa.lead_id(+) = ala.lead_id
AND aaa.person_id = jt.source_id(+)
AND jt.category(+) = 'EMPLOYEE'
AND ass.language = 'US'
and ass.sales_stage_id = ala.sales_stage_id
and ala.status = oas.status_code
and oas.language = 'ZHS'
and fu.employee_id(+) = jt.source_id
and upper(ala.status)<>'CANCELLATION'
and ala.CUSTOMER_ID= 118335
and oas.meaning<>'失败'
and oas.meaning<>'取消'
and SECOM_SALES_SECURITY.ACCESS_CRM_LEAD_OA(ala.lead_id,'andy_pan') = 'Y'
