View: AST_LS_SALES_LEADS_V

Product:	AST - TeleSales
Description:
Implementation/DBA Data:	APPS.AST_LS_SALES_LEADS_V

View Text

SELECT LDH.SALES_LEAD_ID SALES_LEAD_ID
, LDH.LEAD_NUMBER LEAD_NUMBER
, LDH.CUSTOMER_ID CUSTOMER_ID
, LDH.ADDRESS_ID ADDRESS_ID
, LDH.STATUS_CODE STATUS_CODE
, LDH.PARENT_PROJECT PARENT_PROJECT
, LDH.DESCRIPTION LEAD_NAME
, LDH.DESCRIPTION DESCRIPTION
, LDH.DECISION_TIMEFRAME_CODE DECISION_TIMEFRAME_CODE
, LDH.LEAD_RANK_CODE LEAD_RANK_CODE
, LDH.CHANNEL_CODE CHANNEL_CODE
, LDH.CREATION_DATE CREATION_DATE
, LDH.CREATED_BY CREATED_BY
, CLOSE.MEANING CLOSE_REASON
, LDH.ASSIGN_TO_PERSON_ID ASSIGN_TO_PERSON_ID
, LDH.BUDGET_AMOUNT BUDGET_AMOUNT
, LDH.BUDGET_STATUS_CODE BUDGET_STATUS_CODE
, AL4.MEANING BUDGET_STATUS
, LDH.CURRENCY_CODE CURRENCY_CODE
, LDH.KEEP_FLAG KEEP_FLAG
, LDH.QUALIFIED_FLAG QUALIFIED_FLAG
, LDH.URGENT_FLAG URGENT_FLAG
, LDH.ACCEPT_FLAG ACCEPT_FLAG
, LDH.VEHICLE_RESPONSE_CODE VEHICLE_RESPONSE_CODE
, LDH.DELETED_FLAG DELETED_FLAG
, LDH.SCORECARD_ID IMPORT_INTERFACE_ID
, LDH.ASSIGN_SALES_GROUP_ID ASSIGN_SALES_GROUP_ID
, LDH.OFFER_ID OFFER_ID
, LDH.SOURCE_PROMOTION_ID SOURCE_PROMOTION_ID
, CMP.SOURCE_CODE SOURCE_PROMOTION_CODE
, AL1.MEANING STATUS
, AL3.MEANING DECISION_TIMEFRAME
, AL5.MEANING SALES_CHANNEL
, CUS.PARTY_NAME CUSTOMER_NAME
, CUS.PARTY_TYPE PARTY_TYPE
, AR1.MEANING PARTY_NAME
, LDC.LEAD_CONTACT_ID LEAD_CONTACT_ID
, LDC.CONTACT_ROLE_CODE CONTACT_ROLE
, LDC.CONTACT_ID CONTACT_ID
, PERSON_CONTACT.PERSON_FIRST_NAME CONTACT_FIRST_NAME
, PERSON_CONTACT.PERSON_LAST_NAME CONTACT_LAST_NAME
, PERSON_CONTACT.PERSON_FIRST_NAME||' '||PERSON_CONTACT.PERSON_LAST_NAME CONTACT_FULL_NAME
, PER.SOURCE_FIRST_NAME ASSIGNED_TO_FIRST_NAME
, PER.SOURCE_LAST_NAME ASSIGNED_TO_LAST_NAME
, PER.SOURCE_NAME ASSIGNED_NAME
, RNK.MEANING LEAD_RANK
, LDH.LAST_UPDATE_DATE UPDATE_DATE
, LDH.ATTRIBUTE1 ATTRIBUTE1
, LDH.ATTRIBUTE2 ATTRIBUTE2
, LDH.ATTRIBUTE3 ATTRIBUTE3
, LDH.ATTRIBUTE4 ATTRIBUTE4
, LDH.ATTRIBUTE5 ATTRIBUTE5
, LDH.ATTRIBUTE6 ATTRIBUTE6
, LDH.ATTRIBUTE7 ATTRIBUTE7
, LDH.ATTRIBUTE8 ATTRIBUTE8
, LDH.ATTRIBUTE9 ATTRIBUTE9
, LDH.ATTRIBUTE10 ATTRIBUTE10
, LDH.ATTRIBUTE11 ATTRIBUTE11
, LDH.ATTRIBUTE12 ATTRIBUTE12
, LDH.ATTRIBUTE13 ATTRIBUTE13
, LDH.ATTRIBUTE14 ATTRIBUTE14
, LDH.ATTRIBUTE15 ATTRIBUTE15
FROM AS_SALES_LEADS LDH
, AS_SALES_LEAD_CONTACTS LDC
, HZ_PARTIES CUS
, HZ_PARTIES PERSON_CONTACT
, AS_SALES_LEAD_RANKS_TL RNK
, AS_STATUSES_TL AL1
, AS_LOOKUPS AL3
, AS_LOOKUPS CLOSE
, OE_LOOKUPS AL5
, AS_LOOKUPS AL4
, AR_LOOKUPS AR1
, JTF_RS_RESOURCE_EXTNS PER
, AMS_SOURCE_CODES CMP
, HZ_RELATIONSHIPS REL
WHERE LDH.SALES_LEAD_ID + 0 > 0
AND LDH.CUSTOMER_ID = CUS.PARTY_ID
AND AR1.LOOKUP_TYPE(+) = 'PARTY_TYPE'
AND CUS.PARTY_TYPE = AR1.LOOKUP_CODE(+)
AND LDH.ASSIGN_TO_PERSON_ID = PER.SOURCE_ID(+)
AND LDH.ASSIGN_TO_SALESFORCE_ID = PER.RESOURCE_ID(+)
AND LDH.LEAD_RANK_ID = RNK.RANK_ID(+)
AND RNK.LANGUAGE(+) = USERENV('LANG')
AND LDH.STATUS_CODE = AL1.STATUS_CODE(+)
AND LDH.SOURCE_PROMOTION_ID = CMP.SOURCE_CODE_ID(+)
AND AL1.LANGUAGE(+) = USERENV('LANG')
AND LDH.DECISION_TIMEFRAME_CODE = AL3.LOOKUP_CODE(+)
AND AL3.LOOKUP_TYPE(+) = 'DECISION_TIMEFRAME'
AND LDH.BUDGET_STATUS_CODE = AL4.LOOKUP_CODE(+)
AND AL4.LOOKUP_TYPE(+) = 'BUDGET_STATUS'
AND LDH.CLOSE_REASON = CLOSE.LOOKUP_CODE(+)
AND CLOSE.LOOKUP_TYPE(+) = 'CLOSE_REASON'
AND LDH.CHANNEL_CODE = AL5.LOOKUP_CODE(+)
AND AL5.LOOKUP_TYPE(+) = 'SALES_CHANNEL'
AND LDH.SALES_LEAD_ID = LDC.SALES_LEAD_ID(+)
AND LDC.PRIMARY_CONTACT_FLAG(+) = 'Y'
AND LDC.CONTACT_PARTY_ID = REL.PARTY_ID(+)
AND REL.SUBJECT_ID = PERSON_CONTACT.PARTY_ID(+)
AND PER.CATEGORY='EMPLOYEE'
AND REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'

Columns