VIEW: APPS.ASF_FRCST_SUM_REPRT_V

Source

View Text - Preformatted

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

View Text - HTML Formatted

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