VIEW: APPS.AST_LS_SALES_LEADS_CONTACT_V

Source

View Text - Preformatted

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 , LDC.LEAD_CONTACT_ID LEAD_CONTACT_ID,AL6.MEANING CONTACT_ROLE , LDC.CONTACT_ROLE_CODE CONTACT_ROLE_CODE, 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 AL6 , AS_LOOKUPS AL4, AS_LOOKUPS CLOSE , OE_LOOKUPS AL5 , 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 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.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 LDC.CONTACT_ROLE_CODE = AL6.LOOKUP_CODE(+) AND AL6.LOOKUP_TYPE(+)= 'LEAD_CONTACT_ROLE' AND LDH.BUDGET_STATUS_CODE = AL4.LOOKUP_CODE(+) AND AL4.LOOKUP_TYPE(+) = 'BUDGET_STATUS' AND REL.SUBJECT_ID = PERSON_CONTACT.PARTY_ID AND REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES' AND REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'

View Text - HTML Formatted

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
, LDC.LEAD_CONTACT_ID LEAD_CONTACT_ID
, AL6.MEANING CONTACT_ROLE
, LDC.CONTACT_ROLE_CODE CONTACT_ROLE_CODE
, 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 AL6
, AS_LOOKUPS AL4
, AS_LOOKUPS CLOSE
, OE_LOOKUPS AL5
, 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 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.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 LDC.CONTACT_ROLE_CODE = AL6.LOOKUP_CODE(+)
AND AL6.LOOKUP_TYPE(+)= 'LEAD_CONTACT_ROLE'
AND LDH.BUDGET_STATUS_CODE = AL4.LOOKUP_CODE(+)
AND AL4.LOOKUP_TYPE(+) = 'BUDGET_STATUS'
AND REL.SUBJECT_ID = PERSON_CONTACT.PARTY_ID
AND REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'