VIEW: APPS.AST_LS_SALES_LEADS_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, OFFER.SOURCE_CODE OFFER_CODE , 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, AMS_P_SOURCE_CODES_V OFFER 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.OFFER_ID = OFFER.SOURCE_CODE_ID(+) 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' AND REL.DIRECTIONAL_FLAG(+) = 'F'

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
, OFFER.SOURCE_CODE OFFER_CODE
, 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
, AMS_P_SOURCE_CODES_V OFFER
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.OFFER_ID = OFFER.SOURCE_CODE_ID(+)
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'
AND REL.DIRECTIONAL_FLAG(+) = 'F'