View: AS_SALES_LEADS_LISTS_V

View Text

SELECT LEAD.SALES_LEAD_ID
, LEAD.LAST_UPDATE_DATE
, LEAD.LAST_UPDATED_BY
, LEAD.CREATION_DATE
, LEAD.CREATED_BY
, PER1.FIRST_NAME CREATED_BY_FIRST_NAME
, PER1.LAST_NAME CREATED_BY_LAST_NAME
, PER1.LAST_NAME||DECODE(USER1.EMPLOYEE_ID
, NULL
, NULL
, '
, ') ||PER1.FIRST_NAME CREATED_BY_FULL_NAME
, LEAD.LAST_UPDATE_LOGIN
, LEAD.REQUEST_ID
, LEAD.PROGRAM_APPLICATION_ID
, LEAD.PROGRAM_ID
, LEAD.PROGRAM_UPDATE_DATE
, LEAD.LEAD_NUMBER
, LEAD.STATUS_CODE
, DECODE(LEAD.STATUS_CODE
, NULL
, NULL
, GET_MEANING ('SALES_LEAD_STATUS'
, LEAD.STATUS_CODE)) STATUS
, LEAD.SOURCE_PROMOTION_ID
, LEAD.INITIATING_CONTACT_ID
, LEAD.ORIG_SYSTEM_REFERENCE
, LEAD.CONTACT_ROLE_CODE
, DECODE(LEAD.CONTACT_ROLE_CODE
, NULL
, NULL
, GET_MEANING ('LEAD_CONTACT_ROLE'
, LEAD.CONTACT_ROLE_CODE)) CONTACT_ROLE
, LEAD.CHANNEL_CODE
, LOOK4.MEANING CHANNEL
, LEAD.BUDGET_AMOUNT
, LEAD.BUDGET_STATUS_CODE
, DECODE(LEAD.BUDGET_STATUS_CODE
, NULL
, NULL
, GET_MEANING ('BUDGET_STATUS'
, LEAD.BUDGET_STATUS_CODE)) BUDGET_STATUS
, LEAD.CURRENCY_CODE
, LEAD.DECISION_TIMEFRAME_CODE
, DECODE(DECISION_TIMEFRAME_CODE
, NULL
, NULL
, GET_MEANING ('DECISION_TIMEFRAME'
, DECISION_TIMEFRAME_CODE)) DECISION_TIMEFRAME
, LEAD.CLOSE_REASON
, LEAD.PARENT_PROJECT
, LEAD.DESCRIPTION
, LEAD.LEAD_RANK_CODE
, DECODE(LEAD.LEAD_RANK_CODE
, NULL
, NULL
, GET_MEANING ('SALES_LEAD_RANK'
, LEAD.LEAD_RANK_CODE)) RANK
, LEAD.ATTRIBUTE_CATEGORY
, LEAD.ATTRIBUTE1
, LEAD.ATTRIBUTE2
, LEAD.ATTRIBUTE3
, LEAD.ATTRIBUTE4
, LEAD.ATTRIBUTE5
, LEAD.ATTRIBUTE6
, LEAD.ATTRIBUTE7
, LEAD.ATTRIBUTE8
, LEAD.ATTRIBUTE9
, LEAD.ATTRIBUTE10
, LEAD.ATTRIBUTE11
, LEAD.ATTRIBUTE12
, LEAD.ATTRIBUTE13
, LEAD.ATTRIBUTE14
, LEAD.ATTRIBUTE15
, LEAD.ASSIGN_TO_PERSON_ID
, PER2.LAST_NAME||DECODE(LEAD.ASSIGN_TO_PERSON_ID
, NULL
, NULL
, '
, ')|| PER2.FIRST_NAME ASSIGN_TO_FULL_NAME
, LEAD.ASSIGN_DATE
, LEAD.ACCEPT_FLAG
, LEAD.VEHICLE_RESPONSE_CODE
, --DECODE(LEAD.VEHICLE_RESPONSE_CODE
, NULL
, NULL
, GET_MEANING --('VEHICLE_RESPONSE_CODE'
, LEAD.VEHICLE_RESPONSE_CODE))
, LEAD.TOTAL_SCORE
, LEAD.PROMOTION_TYPE
, LEAD.SCORECARD_ID
, LEAD.KEEP_FLAG
, LEAD.URGENT_FLAG
, LEAD.IMPORT_FLAG
, --PROM.NAME SOURCE_PROMOTION_NAME
, -- PROM.CODE PROMOTION_CODE
, CUST.CUSTOMER_NAME
, CUST.CUSTOMER_NUMBER
, LEAD.ADDRESS_ID SITE_ID
, NULL ORG_ID
, ADDR.ADDRESS1
, ADDR.ADDRESS2
, ADDR.ADDRESS3
, ADDR.ADDRESS4
, ADDR.CITY
, ADDR.STATE
, NULL COUNTY
, ADDR.COUNTRY
, NULL PROVINCE
, NULL POSTAL_CODE
, CONT.LAST_NAME CONTACT_LAST_NAME
, CONT.FIRST_NAME CONTACT_FIRST_NAME
, CONT.LAST_NAME||DECODE(LEAD.INITIATING_CONTACT_ID
, NULL
, NULL
, '
, ')|| CONT.FIRST_NAME CONTACT_FULL_NAME
, PHON.AREA_CODE
, PHON.PHONE_NUMBER
, PHON.EXTENSION -- LOOK6.MEANING PHONE_TYPE
FROM SO_LOOKUPS LOOK4
, -- AR_LOOKUPS LOOK6
, -- AS_PROMOTIONS PROM
, PER_PEOPLE_F PER1
, PER_PEOPLE_F PER2
, -- AS_SALES_LEAD_CONTACTS ASCONT
, RA_PHONES PHON
, RA_CONTACTS CONT
, RA_ADDRESSES ADDR
, RA_CUSTOMERS CUST
, FND_USER USER1
, AS_SALES_LEADS LEAD
WHERE --
AND LOOK4.LOOKUP_TYPE(+) = 'SALES_CHANNEL'
AND LEAD.CHANNEL_CODE = LOOK4.LOOKUP_CODE(+) --
AND LEAD.SOURCE_PROMOTION_ID = PROM.PROMOTION_ID
AND LEAD.CREATED_BY = USER1.USER_ID
AND USER1.EMPLOYEE_ID(+) = PER1.PERSON_ID
AND LEAD.ASSIGN_TO_PERSON_ID = PER2.PERSON_ID(+)
AND LEAD.ADDRESS_ID =ADDR.ADDRESS_ID
AND LEAD.CUSTOMER_ID = CUST.CUSTOMER_ID
AND LEAD.INITIATING_CONTACT_ID = CONT.CONTACT_ID --
AND LEAD.INITIATING_CONTACT_ID=ASCONT.CONTACT_ID --AND ASCONT.PHONE_ID =PHON.PHONE_ID
AND LEAD.INITIATING_CONTACT_ID =PHON.CONTACT_ID --
AND PHON.PRIMARY_FLAG='Y' --
AND LOOK6.LOOKUP_TYPE(+) = 'COMMUNICATION_TYPE' --
AND PHON.PHONE_TYPE = LOOK6.LOOKUP_CODE(+)

Columns