VIEW: APPS.AST_OPPORTUNITIES_MV

Source

View Text - Preformatted

SELECT opp_mb.LEAD_ID, opp_mb.LAST_UPDATE_DATE, opp_mb.LAST_UPDATED_BY, opp_mb.CREATION_DATE, opp_mb.CREATED_BY, opp_mb.LAST_UPDATE_LOGIN, opp_mb.REQUEST_ID, opp_mb.PROGRAM_APPLICATION_ID, opp_mb.PROGRAM_ID, opp_mb.PROGRAM_UPDATE_DATE, opp_mb.LEAD_NUMBER, opp_mb.ORG_ID, opp_mb.STATUS status_code, opp_mb.CUSTOMER_ID, opp_mb.ADDRESS_ID, opp_mb.AUTO_ASSIGNMENT_TYPE, opp_mb.PRM_ASSIGNMENT_TYPE, opp_mb.DECISION_TIMEFRAME_CODE, opp_mb.VEHICLE_RESPONSE_CODE, ASLKP.MEANING response_channel, opp_mb.BUDGET_STATUS_CODE, opp_mb.PRM_IND_CLASSIFICATION_CODE, opp_mb.PRM_LEAD_TYPE, opp_mb.CLOSE_COMPETITOR_CODE, opp_mb.DELETED_FLAG, opp_mb.CUSTOMER_BUDGET, opp_mb.ORIGINAL_LEAD_ID, opp_mb.INCUMBENT_PARTNER_RESOURCE_ID, opp_mb.INCUMBENT_PARTNER_PARTY_ID, opp_mb.FOLLOWUP_DATE, opp_mb.PRM_EXEC_SPONSOR_FLAG, opp_mb.PRM_PRJ_LEAD_IN_PLACE_FLAG, opp_mb.METHODOLOGY_CODE, opp_mb.OFFER_ID, opp_mb.LEAD_SOURCE_CODE, opp_mb.ORIG_SYSTEM_REFERENCE, opp_mb.SALES_STAGE_ID, opp_mb.CHANNEL_CODE, opp_mb.TOTAL_AMOUNT, opp_mb.TOTAL_REVENUE_OPP_FORECAST_AMT, opp_mb.CURRENCY_CODE, opp_mb.DECISION_DATE, opp_mb.WIN_PROBABILITY, opp_mb.CLOSE_REASON, opp_mb.CLOSE_COMPETITOR, opp_mb.CLOSE_COMPETITOR_ID, opp_mb.CLOSE_COMMENT, opp_mb.DESCRIPTION, opp_mb.RANK rank_code, opp_mb.END_USER_CUSTOMER_NAME, opp_mb.SOURCE_PROMOTION_ID, opp_mb.END_USER_CUSTOMER_ID, opp_mb.END_USER_ADDRESS_ID, opp_mb.ATTRIBUTE_CATEGORY, opp_mb.ATTRIBUTE1, opp_mb.ATTRIBUTE2, opp_mb.ATTRIBUTE3, opp_mb.ATTRIBUTE4, opp_mb.ATTRIBUTE5, opp_mb.ATTRIBUTE6, opp_mb.ATTRIBUTE7, opp_mb.ATTRIBUTE8, opp_mb.ATTRIBUTE9, opp_mb.ATTRIBUTE10, opp_mb.ATTRIBUTE11, opp_mb.ATTRIBUTE12, opp_mb.ATTRIBUTE13, opp_mb.ATTRIBUTE14, opp_mb.ATTRIBUTE15, opp_mb.PARENT_PROJECT, opp_mb.NO_OPP_ALLOWED_FLAG, opp_mb.DELETE_ALLOWED_FLAG, opp_mb.PRICE_LIST_ID, ASOCHANNELS.MEANING channel, STGTL.NAME sales_stage, ASSTATUSES.MEANING status, opp_mb.FREEZE_FLAG freeze_flag, opp_mb.SALES_METHODOLOGY_ID, opp_mb.language_code lang FROM (SELECT OPP.LEAD_ID, OPP.LAST_UPDATE_DATE, OPP.LAST_UPDATED_BY, OPP.CREATION_DATE, OPP.CREATED_BY, OPP.LAST_UPDATE_LOGIN, OPP.REQUEST_ID, OPP.PROGRAM_APPLICATION_ID, OPP.PROGRAM_ID, OPP.PROGRAM_UPDATE_DATE, OPP.LEAD_NUMBER, OPP.ORG_ID, OPP.STATUS, OPP.CUSTOMER_ID, OPP.ADDRESS_ID, OPP.AUTO_ASSIGNMENT_TYPE, OPP.PRM_ASSIGNMENT_TYPE, OPP.DECISION_TIMEFRAME_CODE, OPP.VEHICLE_RESPONSE_CODE, OPP.BUDGET_STATUS_CODE, OPP.PRM_IND_CLASSIFICATION_CODE, OPP.PRM_LEAD_TYPE, OPP.CLOSE_COMPETITOR_CODE, OPP.DELETED_FLAG, OPP.CUSTOMER_BUDGET, OPP.ORIGINAL_LEAD_ID, OPP.INCUMBENT_PARTNER_RESOURCE_ID, OPP.INCUMBENT_PARTNER_PARTY_ID, OPP.FOLLOWUP_DATE, OPP.PRM_EXEC_SPONSOR_FLAG, OPP.PRM_PRJ_LEAD_IN_PLACE_FLAG, OPP.METHODOLOGY_CODE, OPP.OFFER_ID, OPP.LEAD_SOURCE_CODE, OPP.ORIG_SYSTEM_REFERENCE, OPP.SALES_STAGE_ID, OPP.CHANNEL_CODE, OPP.TOTAL_AMOUNT, OPP.TOTAL_REVENUE_OPP_FORECAST_AMT, OPP.CURRENCY_CODE, OPP.DECISION_DATE, OPP.WIN_PROBABILITY, OPP.CLOSE_REASON, OPP.CLOSE_COMPETITOR, OPP.CLOSE_COMPETITOR_ID, OPP.CLOSE_COMMENT, OPP.DESCRIPTION, OPP.RANK, OPP.END_USER_CUSTOMER_NAME, OPP.SOURCE_PROMOTION_ID, OPP.END_USER_CUSTOMER_ID, OPP.END_USER_ADDRESS_ID, OPP.ATTRIBUTE_CATEGORY, OPP.ATTRIBUTE1, OPP.ATTRIBUTE2, OPP.ATTRIBUTE3, OPP.ATTRIBUTE4, OPP.ATTRIBUTE5, OPP.ATTRIBUTE6, OPP.ATTRIBUTE7, OPP.ATTRIBUTE8, OPP.ATTRIBUTE9, OPP.ATTRIBUTE10, OPP.ATTRIBUTE11, OPP.ATTRIBUTE12, OPP.ATTRIBUTE13, OPP.ATTRIBUTE14, OPP.ATTRIBUTE15, OPP.PARENT_PROJECT, OPP.NO_OPP_ALLOWED_FLAG, OPP.DELETE_ALLOWED_FLAG, OPP.PRICE_LIST_ID, OPP.FREEZE_FLAG freeze_flag, OPP.SALES_METHODOLOGY_ID, fndlang.language_code FROM AS_LEADS_ALL OPP, fnd_languages fndlang WHERE fndlang.installed_flag in ('I', 'B') AND OPP.LAST_UPDATE_DATE >=SYSDATE-NVL((select profile_option_value from fnd_profile_option_values where level_id = 10001 and (profile_option_id, application_id) =(select profile_option_id, application_id from fnd_profile_options where profile_option_name = 'AST_MV_NUM_OF_MONTH_DATA')),3)*30 ) OPP_mb, FND_LOOKUP_VALUES ASOCHANNELS,AS_SALES_STAGES_ALL_TL STGTL,AS_STATUSES_TL ASSTATUSES,FND_LOOKUP_VALUES ASLKP WHERE OPP_mb.CHANNEL_CODE = ASOCHANNELS.LOOKUP_CODE(+) AND ASOCHANNELS.LOOKUP_TYPE(+) = 'SALES_CHANNEL' AND ASOCHANNELS.LANGUAGE(+) = opp_mb.language_code AND ASOCHANNELS.VIEW_APPLICATION_ID(+) = 660 AND nvl(ASOCHANNELS.SECURITY_GROUP_ID, 0) = nvl((select nvl(max(lt.security_group_id), 0) from fnd_lookup_types lt where lt.view_application_id = 660 and lt.lookup_type = 'SALES_CHANNEL' and lt.security_group_id in (0, to_number(decode(substrb(userenv('CLIENT_INFO'),55,1),' ', '0',null, '0',substrb(userenv('CLIENT_INFO'),55,10))))), -99) AND OPP_mb.VEHICLE_RESPONSE_CODE = ASLKP.LOOKUP_CODE(+) AND ASLKP.LOOKUP_TYPE(+) ='VEHICLE_RESPONSE_CODE' AND ASLKP.LANGUAGE(+) = opp_mb.language_code AND ASLKP.VIEW_APPLICATION_ID(+) = 279 AND nvl(ASLKP.SECURITY_GROUP_ID, 0) = nvl((select nvl(max(lt.security_group_id), 0) from fnd_lookup_types lt where lt.view_application_id = 279 and lt.lookup_type = 'VEHICLE_RESPONSE_CODE' and lt.security_group_id in (0, to_number(decode(substrb(userenv('CLIENT_INFO'),55,1),' ', '0',null, '0',substrb(userenv('CLIENT_INFO'),55,10))))), -99) AND OPP_mb.SALES_STAGE_ID = STGTL.SALES_STAGE_ID(+) AND STGTL.LANGUAGE(+) = opp_mb.language_code AND OPP_mb.STATUS = ASSTATUSES.STATUS_CODE AND ASSTATUSES.LANGUAGE = opp_mb.language_code

View Text - HTML Formatted

SELECT OPP_MB.LEAD_ID
, OPP_MB.LAST_UPDATE_DATE
, OPP_MB.LAST_UPDATED_BY
, OPP_MB.CREATION_DATE
, OPP_MB.CREATED_BY
, OPP_MB.LAST_UPDATE_LOGIN
, OPP_MB.REQUEST_ID
, OPP_MB.PROGRAM_APPLICATION_ID
, OPP_MB.PROGRAM_ID
, OPP_MB.PROGRAM_UPDATE_DATE
, OPP_MB.LEAD_NUMBER
, OPP_MB.ORG_ID
, OPP_MB.STATUS STATUS_CODE
, OPP_MB.CUSTOMER_ID
, OPP_MB.ADDRESS_ID
, OPP_MB.AUTO_ASSIGNMENT_TYPE
, OPP_MB.PRM_ASSIGNMENT_TYPE
, OPP_MB.DECISION_TIMEFRAME_CODE
, OPP_MB.VEHICLE_RESPONSE_CODE
, ASLKP.MEANING RESPONSE_CHANNEL
, OPP_MB.BUDGET_STATUS_CODE
, OPP_MB.PRM_IND_CLASSIFICATION_CODE
, OPP_MB.PRM_LEAD_TYPE
, OPP_MB.CLOSE_COMPETITOR_CODE
, OPP_MB.DELETED_FLAG
, OPP_MB.CUSTOMER_BUDGET
, OPP_MB.ORIGINAL_LEAD_ID
, OPP_MB.INCUMBENT_PARTNER_RESOURCE_ID
, OPP_MB.INCUMBENT_PARTNER_PARTY_ID
, OPP_MB.FOLLOWUP_DATE
, OPP_MB.PRM_EXEC_SPONSOR_FLAG
, OPP_MB.PRM_PRJ_LEAD_IN_PLACE_FLAG
, OPP_MB.METHODOLOGY_CODE
, OPP_MB.OFFER_ID
, OPP_MB.LEAD_SOURCE_CODE
, OPP_MB.ORIG_SYSTEM_REFERENCE
, OPP_MB.SALES_STAGE_ID
, OPP_MB.CHANNEL_CODE
, OPP_MB.TOTAL_AMOUNT
, OPP_MB.TOTAL_REVENUE_OPP_FORECAST_AMT
, OPP_MB.CURRENCY_CODE
, OPP_MB.DECISION_DATE
, OPP_MB.WIN_PROBABILITY
, OPP_MB.CLOSE_REASON
, OPP_MB.CLOSE_COMPETITOR
, OPP_MB.CLOSE_COMPETITOR_ID
, OPP_MB.CLOSE_COMMENT
, OPP_MB.DESCRIPTION
, OPP_MB.RANK RANK_CODE
, OPP_MB.END_USER_CUSTOMER_NAME
, OPP_MB.SOURCE_PROMOTION_ID
, OPP_MB.END_USER_CUSTOMER_ID
, OPP_MB.END_USER_ADDRESS_ID
, OPP_MB.ATTRIBUTE_CATEGORY
, OPP_MB.ATTRIBUTE1
, OPP_MB.ATTRIBUTE2
, OPP_MB.ATTRIBUTE3
, OPP_MB.ATTRIBUTE4
, OPP_MB.ATTRIBUTE5
, OPP_MB.ATTRIBUTE6
, OPP_MB.ATTRIBUTE7
, OPP_MB.ATTRIBUTE8
, OPP_MB.ATTRIBUTE9
, OPP_MB.ATTRIBUTE10
, OPP_MB.ATTRIBUTE11
, OPP_MB.ATTRIBUTE12
, OPP_MB.ATTRIBUTE13
, OPP_MB.ATTRIBUTE14
, OPP_MB.ATTRIBUTE15
, OPP_MB.PARENT_PROJECT
, OPP_MB.NO_OPP_ALLOWED_FLAG
, OPP_MB.DELETE_ALLOWED_FLAG
, OPP_MB.PRICE_LIST_ID
, ASOCHANNELS.MEANING CHANNEL
, STGTL.NAME SALES_STAGE
, ASSTATUSES.MEANING STATUS
, OPP_MB.FREEZE_FLAG FREEZE_FLAG
, OPP_MB.SALES_METHODOLOGY_ID
, OPP_MB.LANGUAGE_CODE LANG
FROM (SELECT OPP.LEAD_ID
, OPP.LAST_UPDATE_DATE
, OPP.LAST_UPDATED_BY
, OPP.CREATION_DATE
, OPP.CREATED_BY
, OPP.LAST_UPDATE_LOGIN
, OPP.REQUEST_ID
, OPP.PROGRAM_APPLICATION_ID
, OPP.PROGRAM_ID
, OPP.PROGRAM_UPDATE_DATE
, OPP.LEAD_NUMBER
, OPP.ORG_ID
, OPP.STATUS
, OPP.CUSTOMER_ID
, OPP.ADDRESS_ID
, OPP.AUTO_ASSIGNMENT_TYPE
, OPP.PRM_ASSIGNMENT_TYPE
, OPP.DECISION_TIMEFRAME_CODE
, OPP.VEHICLE_RESPONSE_CODE
, OPP.BUDGET_STATUS_CODE
, OPP.PRM_IND_CLASSIFICATION_CODE
, OPP.PRM_LEAD_TYPE
, OPP.CLOSE_COMPETITOR_CODE
, OPP.DELETED_FLAG
, OPP.CUSTOMER_BUDGET
, OPP.ORIGINAL_LEAD_ID
, OPP.INCUMBENT_PARTNER_RESOURCE_ID
, OPP.INCUMBENT_PARTNER_PARTY_ID
, OPP.FOLLOWUP_DATE
, OPP.PRM_EXEC_SPONSOR_FLAG
, OPP.PRM_PRJ_LEAD_IN_PLACE_FLAG
, OPP.METHODOLOGY_CODE
, OPP.OFFER_ID
, OPP.LEAD_SOURCE_CODE
, OPP.ORIG_SYSTEM_REFERENCE
, OPP.SALES_STAGE_ID
, OPP.CHANNEL_CODE
, OPP.TOTAL_AMOUNT
, OPP.TOTAL_REVENUE_OPP_FORECAST_AMT
, OPP.CURRENCY_CODE
, OPP.DECISION_DATE
, OPP.WIN_PROBABILITY
, OPP.CLOSE_REASON
, OPP.CLOSE_COMPETITOR
, OPP.CLOSE_COMPETITOR_ID
, OPP.CLOSE_COMMENT
, OPP.DESCRIPTION
, OPP.RANK
, OPP.END_USER_CUSTOMER_NAME
, OPP.SOURCE_PROMOTION_ID
, OPP.END_USER_CUSTOMER_ID
, OPP.END_USER_ADDRESS_ID
, OPP.ATTRIBUTE_CATEGORY
, OPP.ATTRIBUTE1
, OPP.ATTRIBUTE2
, OPP.ATTRIBUTE3
, OPP.ATTRIBUTE4
, OPP.ATTRIBUTE5
, OPP.ATTRIBUTE6
, OPP.ATTRIBUTE7
, OPP.ATTRIBUTE8
, OPP.ATTRIBUTE9
, OPP.ATTRIBUTE10
, OPP.ATTRIBUTE11
, OPP.ATTRIBUTE12
, OPP.ATTRIBUTE13
, OPP.ATTRIBUTE14
, OPP.ATTRIBUTE15
, OPP.PARENT_PROJECT
, OPP.NO_OPP_ALLOWED_FLAG
, OPP.DELETE_ALLOWED_FLAG
, OPP.PRICE_LIST_ID
, OPP.FREEZE_FLAG FREEZE_FLAG
, OPP.SALES_METHODOLOGY_ID
, FNDLANG.LANGUAGE_CODE
FROM AS_LEADS_ALL OPP
, FND_LANGUAGES FNDLANG
WHERE FNDLANG.INSTALLED_FLAG IN ('I'
, 'B')
AND OPP.LAST_UPDATE_DATE >=SYSDATE-NVL((SELECT PROFILE_OPTION_VALUE
FROM FND_PROFILE_OPTION_VALUES
WHERE LEVEL_ID = 10001
AND (PROFILE_OPTION_ID
, APPLICATION_ID) =(SELECT PROFILE_OPTION_ID
, APPLICATION_ID
FROM FND_PROFILE_OPTIONS
WHERE PROFILE_OPTION_NAME = 'AST_MV_NUM_OF_MONTH_DATA'))
, 3)*30 ) OPP_MB
, FND_LOOKUP_VALUES ASOCHANNELS
, AS_SALES_STAGES_ALL_TL STGTL
, AS_STATUSES_TL ASSTATUSES
, FND_LOOKUP_VALUES ASLKP
WHERE OPP_MB.CHANNEL_CODE = ASOCHANNELS.LOOKUP_CODE(+)
AND ASOCHANNELS.LOOKUP_TYPE(+) = 'SALES_CHANNEL'
AND ASOCHANNELS.LANGUAGE(+) = OPP_MB.LANGUAGE_CODE
AND ASOCHANNELS.VIEW_APPLICATION_ID(+) = 660
AND NVL(ASOCHANNELS.SECURITY_GROUP_ID
, 0) = NVL((SELECT NVL(MAX(LT.SECURITY_GROUP_ID)
, 0)
FROM FND_LOOKUP_TYPES LT
WHERE LT.VIEW_APPLICATION_ID = 660
AND LT.LOOKUP_TYPE = 'SALES_CHANNEL'
AND LT.SECURITY_GROUP_ID IN (0
, TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO')
, 55
, 1)
, ' '
, '0'
, NULL
, '0'
, SUBSTRB(USERENV('CLIENT_INFO')
, 55
, 10)))))
, -99)
AND OPP_MB.VEHICLE_RESPONSE_CODE = ASLKP.LOOKUP_CODE(+)
AND ASLKP.LOOKUP_TYPE(+) ='VEHICLE_RESPONSE_CODE'
AND ASLKP.LANGUAGE(+) = OPP_MB.LANGUAGE_CODE
AND ASLKP.VIEW_APPLICATION_ID(+) = 279
AND NVL(ASLKP.SECURITY_GROUP_ID
, 0) = NVL((SELECT NVL(MAX(LT.SECURITY_GROUP_ID)
, 0)
FROM FND_LOOKUP_TYPES LT
WHERE LT.VIEW_APPLICATION_ID = 279
AND LT.LOOKUP_TYPE = 'VEHICLE_RESPONSE_CODE'
AND LT.SECURITY_GROUP_ID IN (0
, TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO')
, 55
, 1)
, ' '
, '0'
, NULL
, '0'
, SUBSTRB(USERENV('CLIENT_INFO')
, 55
, 10)))))
, -99)
AND OPP_MB.SALES_STAGE_ID = STGTL.SALES_STAGE_ID(+)
AND STGTL.LANGUAGE(+) = OPP_MB.LANGUAGE_CODE
AND OPP_MB.STATUS = ASSTATUSES.STATUS_CODE
AND ASSTATUSES.LANGUAGE = OPP_MB.LANGUAGE_CODE