Product: | ASF - Sales Online |
---|---|
Description: | |
Implementation/DBA Data: | APPS.ASF_FRCST_SUM_REPRT_V |
SELECT V.LEAD_ID
, V.OPP_DESCRIPTION
, V.SALES_GROUP_NAME
, V.SALES_GROUP_ID
, V.SALES_REP_NAME
, V.SALESFORCE_ID
, V.CUSTOMER_NAME
, V.CUSTOMER_ID
, V.LEAD_NUMBER
, V.DECISION_DATE
, V.SALES_STAGE
, V.SALES_STAGE_ID
, V.STATUS
, V.STATUS_CODE
, V.CHANNEL_CODE
, V.WIN_PROBABILITY
, V.INTEREST_TYPE
, V.INTEREST_TYPE_ID
, V.PRIMARY_INTEREST_CODE
, V.PRIMARY_INTEREST_CODE_ID
, V.SECONDARY_INTEREST_CODE
, V.SECONDARY_INTEREST_CODE_ID
, V.LAST_UPDATE_DATE
, V.LAST_UPDATED_BY
, V.CREATION_DATE
, V.CREATED_BY
, V.CUSTOMER_CATEGORY
, V.CUSTOMER_CATEGORY_CODE
, V.SALES_CREDIT_ID
, V.ADDRESS_ID
, V.OPP_OPEN_STATUS_FLAG
, V.OPP_DELETED_FLAG
, V.PARTY_TYPE
, MC.CONVERSION_TYPE
, MC.TO_CURRENCY_CODE
, MC.CONVERSION_PERIOD_SET_NAME
, MC.CONVERSION_PERIOD_NAME
, MC.CONVERSION_PERIOD_TYPE
, MC.CONVERSION_DATE
, MC.FROM_CURRENCY_CODE
, MC.TOTAL_AMOUNT
, SUM(NVL(MC.SALES_CREDIT_AMOUNT
, 0))
, SUM(NVL(MC.WON_AMOUNT
, 0))
, SUM(NVL(MC.WEIGHTED_AMOUNT
, 0))
, MAX(MC.CONVERSION_STATUS_FLAG)
, V.CREDIT_TYPE_ID
, AOWL.OPP_WORKSHEET_LINE_ID
, AOWL.FORECAST_AMOUNT
, AOWL.WORST_FORECAST_AMOUNT
, AOWL.BEST_FORECAST_AMOUNT
FROM AS_SALES_CREDITS_DENORM V
, AS_MC_SALES_CREDITS_DEN MC
, AS_OPP_WORKSHEET_LINES AOWL
WHERE V.SALES_CREDIT_ID = MC.SALES_CREDIT_ID
AND MC.TYPE = 'PERIOD'
AND V.LEAD_ID = AOWL.LEAD_ID(+)
AND V.EMPLOYEE_PERSON_ID = AOWL.EMPLOYEE_PERSON_ID(+)
AND (AOWL.END_DATE_ACTIVE IS NULL OR AOWL.END_DATE_ACTIVE < SYSDATE) GROUP BY V.LEAD_ID
, V.OPP_DESCRIPTION
, V.SALES_GROUP_NAME
, V.SALES_GROUP_ID
, V.SALES_REP_NAME
, V.SALESFORCE_ID
, V.CUSTOMER_NAME
, V.CUSTOMER_ID
, V.LEAD_NUMBER
, V.DECISION_DATE
, V.SALES_STAGE
, V.SALES_STAGE_ID
, V.STATUS
, V.STATUS_CODE
, V.CHANNEL_CODE
, V.WIN_PROBABILITY
, V.INTEREST_TYPE
, V.INTEREST_TYPE_ID
, V.PRIMARY_INTEREST_CODE
, V.PRIMARY_INTEREST_CODE_ID
, V.SECONDARY_INTEREST_CODE
, V.SECONDARY_INTEREST_CODE_ID
, V.LAST_UPDATE_DATE
, V.LAST_UPDATED_BY
, V.CREATION_DATE
, V.CREATED_BY
, V.CUSTOMER_CATEGORY
, V.CUSTOMER_CATEGORY_CODE
, V.SALES_CREDIT_ID
, V.ADDRESS_ID
, V.OPP_OPEN_STATUS_FLAG
, V.OPP_DELETED_FLAG
, V.PARTY_TYPE
, MC.CONVERSION_TYPE
, MC.TO_CURRENCY_CODE
, MC.CONVERSION_PERIOD_SET_NAME
, MC.CONVERSION_PERIOD_NAME
, MC.CONVERSION_PERIOD_TYPE
, MC.CONVERSION_DATE
, MC.FROM_CURRENCY_CODE
, MC.TOTAL_AMOUNT
, V.CREDIT_TYPE_ID
, AOWL.OPP_WORKSHEET_LINE_ID
, AOWL.FORECAST_AMOUNT
, AOWL.WORST_FORECAST_AMOUNT
, AOWL.BEST_FORECAST_AMOUNT