VIEW: APPS.AS_SALES_LEADS_V

Source

View Text - Preformatted

SELECT LEAD.ROWID ROW_ID, LEAD.SALES_LEAD_ID, LEAD.LAST_UPDATE_DATE, LEAD.LAST_UPDATED_BY, LEAD.CREATION_DATE, LEAD.CREATED_BY, LEAD.LAST_UPDATE_LOGIN, LEAD.REQUEST_ID, LEAD.PROGRAM_APPLICATION_ID, LEAD.PROGRAM_ID, LEAD.PROGRAM_UPDATE_DATE, LEAD.LEAD_NUMBER, LEAD.STATUS_CODE, AS_LOOKUPS1.MEANING LEAD_STATUS, LEAD.CUSTOMER_ID, CUST.PARTY_NAME, CUST.PARTY_NUMBER PARTY_NUMBER, CUST.PARTY_TYPE PARTY_TYPE, LEAD.ADDRESS_ID, LEAD.ORIG_SYSTEM_REFERENCE, LEAD.ASSIGN_TO_PERSON_ID, LEAD.ASSIGN_TO_SALESFORCE_ID, RES.SOURCE_NAME ASSIGNED_NAME, LEAD.CHANNEL_CODE, ASO_CHANNEL.SALES_CHANNEL, LEAD.BUDGET_AMOUNT, LEAD.BUDGET_STATUS_CODE, AS_LOOKUPS2.MEANING BUDGET_STATUS, LEAD.CURRENCY_CODE, LEAD.DECISION_TIMEFRAME_CODE, AS_LOOKUPS3.MEANING TIME_FRAME, LEAD.DESCRIPTION, LEAD.LEAD_RANK_CODE, RANK.MEANING RANK, LEAD.SOURCE_PROMOTION_ID, CAMP.NAME SOURCE_PROMOTION_NAME, CAMP.SOURCE_CODE SOURCE_PROMOTION_CODE, LEAD.INITIATING_CONTACT_ID, LEAD.CLOSE_REASON, LEAD.PARENT_PROJECT, 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_DATE, LEAD.ACCEPT_FLAG, LEAD.VEHICLE_RESPONSE_CODE, LEAD.TOTAL_SCORE, LEAD.SCORECARD_ID, LEAD.KEEP_FLAG, LEAD.URGENT_FLAG, LEAD.IMPORT_FLAG, LEAD.REJECT_REASON_CODE, LEAD.LEAD_RANK_ID, LEAD.DELETED_FLAG, LEAD.ASSIGN_SALES_GROUP_ID, LEAD.OFFER_ID, OFR.NAME OFFER_NAME, OFR.SOURCE_CODE OFFER_CODE, LEAD.CONTACT_ROLE_CODE, LEAD.INCUMBENT_PARTNER_PARTY_ID, LEAD.INCUMBENT_PARTNER_RESOURCE_ID, LEAD.PRM_EXEC_SPONSOR_FLAG, LEAD.PRM_PRJ_LEAD_IN_PLACE_FLAG, LEAD.PRM_SALES_LEAD_TYPE, LEAD.PRM_IND_CLASSIFICATION_CODE, LEAD.QUALIFIED_FLAG, LEAD.ORIG_SYSTEM_CODE, LEAD.PRM_ASSIGNMENT_TYPE, LEAD.AUTO_ASSIGNMENT_TYPE, LEAD.PRIMARY_CONTACT_PARTY_ID, LEAD.PRIMARY_CNT_PERSON_PARTY_ID, LEAD.PRIMARY_CONTACT_PHONE_ID, LEAD.TRUNC_CREATION_DATE, AS_LOOKUPS4.MEANING, CONTACT.PARTY_NAME, PHONE.PHONE_COUNTRY_CODE ||DECODE(PHONE.PHONE_AREA_CODE, NULL, '', ' '||PHONE.PHONE_AREA_CODE) || DECODE(PHONE.PHONE_NUMBER, NULL, '', ' '||PHONE.PHONE_NUMBER) || DECODE(PHONE.PHONE_EXTENSION, NULL, '', ' ext. '||PHONE.PHONE_EXTENSION), STATUS.ENABLED_FLAG, AS_LOOKUPS5.MEANING, AS_LOOKUPS5.ENABLED_FLAG, AS_LOOKUPS5.DESCRIPTION, LEAD.LEAD_DATE, LEAD.COUNTRY, LEAD.SOURCE_SYSTEM FROM AS_SALES_LEADS LEAD, HZ_PARTIES CUST, AMS_P_SOURCE_CODES_V CAMP, AMS_P_SOURCE_CODES_V OFR, AS_STATUSES_TL AS_LOOKUPS1, AS_SALES_LEAD_RANKS_TL RANK, AS_LOOKUPS AS_LOOKUPS2, AS_LOOKUPS AS_LOOKUPS3, AS_LOOKUPS AS_LOOKUPS4, HZ_PARTIES CONTACT, HZ_CONTACT_POINTS PHONE, ASO_I_SALES_CHANNELS_V ASO_CHANNEL, JTF_RS_RESOURCE_EXTNS RES, AS_STATUSES_B STATUS, AS_LOOKUPS AS_LOOKUPS5 WHERE LEAD.CUSTOMER_ID = CUST.PARTY_ID AND LEAD.STATUS_CODE = AS_LOOKUPS1.STATUS_CODE AND AS_LOOKUPS1.LANGUAGE = USERENV('LANG') AND LEAD.LEAD_RANK_ID = RANK.RANK_ID(+) AND RANK.LANGUAGE(+) = USERENV('LANG') AND LEAD.BUDGET_STATUS_CODE = AS_LOOKUPS2.LOOKUP_CODE(+) AND AS_LOOKUPS2.LOOKUP_TYPE(+) = 'BUDGET_STATUS' AND LEAD.DECISION_TIMEFRAME_CODE = AS_LOOKUPS3.LOOKUP_CODE(+) AND AS_LOOKUPS3.LOOKUP_TYPE(+) = 'DECISION_TIMEFRAME' AND LEAD.REJECT_REASON_CODE = AS_LOOKUPS4.LOOKUP_CODE(+) AND AS_LOOKUPS4.LOOKUP_TYPE(+) = 'REJECT_REASON_CODE' AND LEAD.CHANNEL_CODE = ASO_CHANNEL.SALES_CHANNEL_CODE(+) AND LEAD.SOURCE_PROMOTION_ID = CAMP.SOURCE_CODE_ID(+) AND LEAD.OFFER_ID = OFR.SOURCE_CODE_ID(+) AND LEAD.PRIMARY_CNT_PERSON_PARTY_ID = CONTACT.PARTY_ID(+) AND LEAD.PRIMARY_CONTACT_PHONE_ID = PHONE.CONTACT_POINT_ID(+) AND RES.RESOURCE_ID(+) = LEAD.ASSIGN_TO_SALESFORCE_ID AND LEAD.STATUS_CODE = STATUS.STATUS_CODE AND LEAD.VEHICLE_RESPONSE_CODE = AS_LOOKUPS5.LOOKUP_CODE(+) AND AS_LOOKUPS5.LOOKUP_TYPE(+) = 'VEHICLE_RESPONSE_CODE'

View Text - HTML Formatted

SELECT LEAD.ROWID ROW_ID
, LEAD.SALES_LEAD_ID
, LEAD.LAST_UPDATE_DATE
, LEAD.LAST_UPDATED_BY
, LEAD.CREATION_DATE
, LEAD.CREATED_BY
, LEAD.LAST_UPDATE_LOGIN
, LEAD.REQUEST_ID
, LEAD.PROGRAM_APPLICATION_ID
, LEAD.PROGRAM_ID
, LEAD.PROGRAM_UPDATE_DATE
, LEAD.LEAD_NUMBER
, LEAD.STATUS_CODE
, AS_LOOKUPS1.MEANING LEAD_STATUS
, LEAD.CUSTOMER_ID
, CUST.PARTY_NAME
, CUST.PARTY_NUMBER PARTY_NUMBER
, CUST.PARTY_TYPE PARTY_TYPE
, LEAD.ADDRESS_ID
, LEAD.ORIG_SYSTEM_REFERENCE
, LEAD.ASSIGN_TO_PERSON_ID
, LEAD.ASSIGN_TO_SALESFORCE_ID
, RES.SOURCE_NAME ASSIGNED_NAME
, LEAD.CHANNEL_CODE
, ASO_CHANNEL.SALES_CHANNEL
, LEAD.BUDGET_AMOUNT
, LEAD.BUDGET_STATUS_CODE
, AS_LOOKUPS2.MEANING BUDGET_STATUS
, LEAD.CURRENCY_CODE
, LEAD.DECISION_TIMEFRAME_CODE
, AS_LOOKUPS3.MEANING TIME_FRAME
, LEAD.DESCRIPTION
, LEAD.LEAD_RANK_CODE
, RANK.MEANING RANK
, LEAD.SOURCE_PROMOTION_ID
, CAMP.NAME SOURCE_PROMOTION_NAME
, CAMP.SOURCE_CODE SOURCE_PROMOTION_CODE
, LEAD.INITIATING_CONTACT_ID
, LEAD.CLOSE_REASON
, LEAD.PARENT_PROJECT
, 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_DATE
, LEAD.ACCEPT_FLAG
, LEAD.VEHICLE_RESPONSE_CODE
, LEAD.TOTAL_SCORE
, LEAD.SCORECARD_ID
, LEAD.KEEP_FLAG
, LEAD.URGENT_FLAG
, LEAD.IMPORT_FLAG
, LEAD.REJECT_REASON_CODE
, LEAD.LEAD_RANK_ID
, LEAD.DELETED_FLAG
, LEAD.ASSIGN_SALES_GROUP_ID
, LEAD.OFFER_ID
, OFR.NAME OFFER_NAME
, OFR.SOURCE_CODE OFFER_CODE
, LEAD.CONTACT_ROLE_CODE
, LEAD.INCUMBENT_PARTNER_PARTY_ID
, LEAD.INCUMBENT_PARTNER_RESOURCE_ID
, LEAD.PRM_EXEC_SPONSOR_FLAG
, LEAD.PRM_PRJ_LEAD_IN_PLACE_FLAG
, LEAD.PRM_SALES_LEAD_TYPE
, LEAD.PRM_IND_CLASSIFICATION_CODE
, LEAD.QUALIFIED_FLAG
, LEAD.ORIG_SYSTEM_CODE
, LEAD.PRM_ASSIGNMENT_TYPE
, LEAD.AUTO_ASSIGNMENT_TYPE
, LEAD.PRIMARY_CONTACT_PARTY_ID
, LEAD.PRIMARY_CNT_PERSON_PARTY_ID
, LEAD.PRIMARY_CONTACT_PHONE_ID
, LEAD.TRUNC_CREATION_DATE
, AS_LOOKUPS4.MEANING
, CONTACT.PARTY_NAME
, PHONE.PHONE_COUNTRY_CODE ||DECODE(PHONE.PHONE_AREA_CODE
, NULL
, ''
, ' '||PHONE.PHONE_AREA_CODE) || DECODE(PHONE.PHONE_NUMBER
, NULL
, ''
, ' '||PHONE.PHONE_NUMBER) || DECODE(PHONE.PHONE_EXTENSION
, NULL
, ''
, ' EXT. '||PHONE.PHONE_EXTENSION)
, STATUS.ENABLED_FLAG
, AS_LOOKUPS5.MEANING
, AS_LOOKUPS5.ENABLED_FLAG
, AS_LOOKUPS5.DESCRIPTION
, LEAD.LEAD_DATE
, LEAD.COUNTRY
, LEAD.SOURCE_SYSTEM
FROM AS_SALES_LEADS LEAD
, HZ_PARTIES CUST
, AMS_P_SOURCE_CODES_V CAMP
, AMS_P_SOURCE_CODES_V OFR
, AS_STATUSES_TL AS_LOOKUPS1
, AS_SALES_LEAD_RANKS_TL RANK
, AS_LOOKUPS AS_LOOKUPS2
, AS_LOOKUPS AS_LOOKUPS3
, AS_LOOKUPS AS_LOOKUPS4
, HZ_PARTIES CONTACT
, HZ_CONTACT_POINTS PHONE
, ASO_I_SALES_CHANNELS_V ASO_CHANNEL
, JTF_RS_RESOURCE_EXTNS RES
, AS_STATUSES_B STATUS
, AS_LOOKUPS AS_LOOKUPS5
WHERE LEAD.CUSTOMER_ID = CUST.PARTY_ID
AND LEAD.STATUS_CODE = AS_LOOKUPS1.STATUS_CODE
AND AS_LOOKUPS1.LANGUAGE = USERENV('LANG')
AND LEAD.LEAD_RANK_ID = RANK.RANK_ID(+)
AND RANK.LANGUAGE(+) = USERENV('LANG')
AND LEAD.BUDGET_STATUS_CODE = AS_LOOKUPS2.LOOKUP_CODE(+)
AND AS_LOOKUPS2.LOOKUP_TYPE(+) = 'BUDGET_STATUS'
AND LEAD.DECISION_TIMEFRAME_CODE = AS_LOOKUPS3.LOOKUP_CODE(+)
AND AS_LOOKUPS3.LOOKUP_TYPE(+) = 'DECISION_TIMEFRAME'
AND LEAD.REJECT_REASON_CODE = AS_LOOKUPS4.LOOKUP_CODE(+)
AND AS_LOOKUPS4.LOOKUP_TYPE(+) = 'REJECT_REASON_CODE'
AND LEAD.CHANNEL_CODE = ASO_CHANNEL.SALES_CHANNEL_CODE(+)
AND LEAD.SOURCE_PROMOTION_ID = CAMP.SOURCE_CODE_ID(+)
AND LEAD.OFFER_ID = OFR.SOURCE_CODE_ID(+)
AND LEAD.PRIMARY_CNT_PERSON_PARTY_ID = CONTACT.PARTY_ID(+)
AND LEAD.PRIMARY_CONTACT_PHONE_ID = PHONE.CONTACT_POINT_ID(+)
AND RES.RESOURCE_ID(+) = LEAD.ASSIGN_TO_SALESFORCE_ID
AND LEAD.STATUS_CODE = STATUS.STATUS_CODE
AND LEAD.VEHICLE_RESPONSE_CODE = AS_LOOKUPS5.LOOKUP_CODE(+)
AND AS_LOOKUPS5.LOOKUP_TYPE(+) = 'VEHICLE_RESPONSE_CODE'