[Home] [Help]
| Product: | AS - Sales Foundation |
|---|---|
| Description: | Displays the base information of a lead. |
| Implementation/DBA Data: | Not implemented in this database |
SELECT LD.ROWID
, LD.LEAD_ID
, LD.LAST_UPDATE_DATE
, LD.LAST_UPDATED_BY
, LD.CREATION_DATE
, LD.CREATED_BY
, LD.LAST_UPDATE_LOGIN
, LD.LEAD_NUMBER
, LD.STATUS
, LD.CUSTOMER_ID
, LD.ADDRESS_ID
, LD.SALES_STAGE_ID
, LD.CHANNEL_CODE
, LD.TOTAL_AMOUNT
, LD.CURRENCY_CODE
, LD.DECISION_DATE
, LD.WIN_PROBABILITY
, LD.DESCRIPTION
, LD.ATTRIBUTE_CATEGORY
, LD.ATTRIBUTE1
, LD.ATTRIBUTE2
, LD.ATTRIBUTE3
, LD.ATTRIBUTE4
, LD.ATTRIBUTE5
, LD.ATTRIBUTE6
, LD.ATTRIBUTE7
, LD.ATTRIBUTE8
, LD.ATTRIBUTE9
, LD.ATTRIBUTE10
, LD.ATTRIBUTE11
, LD.ATTRIBUTE12
, LD.ATTRIBUTE13
, LD.ATTRIBUTE14
, LD.ATTRIBUTE15
, CU.CUSTOMER_NAME
, CU.CUSTOMER_NUMBER
, AD.CITY
, SO.MEANING CHANNEL
, LU.MEANING STATUS
, SS.NAME SALES_STAGE
, LAB.LEAD_ADJUSTMENT_ID
, LAB.ADJUSTED_BY_PERSON_ID
, LAB.TOTAL_AMOUNT
, LAB.WIN_PROBABILITY
, LAB.STATUS_CODE
, LAB.DECISION_DATE
, LA1.LEAD_ADJUSTMENT_ID
, LA1.ADJUSTED_BY_PERSON_ID
, PF.FIRST_NAME || ' ' || PF.LAST_NAME
, LA1.TOTAL_AMOUNT
, LA1.WIN_PROBABILITY
, LA1.STATUS_CODE
, LA1.DECISION_DATE
, LA1.ADJUSTED_FOR_SALESGROUP_ID
, LA2.LEAD_ADJUSTMENT_ID
, LA2.ADJUSTED_BY_PERSON_ID
, LA2.TOTAL_AMOUNT
, LA2.WIN_PROBABILITY
, LA2.STATUS_CODE
, LA2.DECISION_DATE
, LA2.REVIEWED_FLAG
, LA2.ADJUSTED_FOR_SALESGROUP_ID
FROM RA_ADDRESSES AD
, RA_CUSTOMERS CU
, SO_LOOKUPS SO
, AS_LOOKUPS LU
, AS_SALES_STAGES_VL SS
, AS_LEAD_ADJS LAB
, AS_LEAD_ADJS LA1
, PER_PEOPLE_X PF
, AS_LEAD_ADJS LA2
, AS_OPPORTUNITIES LD
WHERE LD.ADDRESS_ID = AD.ADDRESS_ID
AND LD.CUSTOMER_ID = CU.CUSTOMER_ID
AND LD.CHANNEL_CODE = SO.LOOKUP_CODE(+)
AND SO.LOOKUP_TYPE(+) = 'SALES_CHANNEL'
AND LD.STATUS = LU.LOOKUP_CODE(+)
AND LU.LOOKUP_TYPE(+) = 'LEAD_STATUS'
AND LD.SALES_STAGE_ID = SS.SALES_STAGE_ID(+)
AND LAB.ORIGINAL_LEAD_ID = LD.OPPORTUNITY_ID
AND LAB.ORIGINAL_LEAD_FLAG = 'Y'
AND LA1.ORIGINAL_LEAD_ID = LD.OPPORTUNITY_ID
AND LA1.ADJUSTED_BY_PERSON_ID = PF.PERSON_ID
AND (LA1.ORIGINAL_LEAD_FLAG = 'Y' OR LA1.LEAD_ADJUSTMENT_ID = (SELECT MAX(SUBLA1.LEAD_ADJUSTMENT_ID)
FROM AS_LEAD_ADJS SUBLA1
WHERE SUBLA1.ORIGINAL_LEAD_ID = LA1.ORIGINAL_LEAD_ID
AND SUBLA1.ADJUSTED_BY_PERSON_ID = LA1.ADJUSTED_BY_PERSON_ID))
AND LA2.ORIGINAL_LEAD_ID = LD.OPPORTUNITY_ID
AND (LA2.ORIGINAL_LEAD_FLAG = 'Y' OR LA2.LEAD_ADJUSTMENT_ID = (SELECT MAX(SUBLA2.LEAD_ADJUSTMENT_ID)
FROM AS_LEAD_ADJS SUBLA2
WHERE SUBLA2.ORIGINAL_LEAD_ID = LA2.ORIGINAL_LEAD_ID
AND SUBLA2.ADJUSTED_BY_PERSON_ID = LA2.ADJUSTED_BY_PERSON_ID))