VIEW: APPS.AS_FRCST_REP_OPP_LINES_V

Source

View Text - Preformatted

SELECT SCD.opp_description ,SCD.lead_number opp_number ,NVL(MTL.description, MCT.description) product_name ,MCT1.description product_category_name ,SCD.customer_name ,STT.meaning status ,STG.name sales_stage ,SCD.win_probability ,SCD.forecast_date ,NVL(SCD.opp_worst_forecast_amount * R.conversion_rate,0) opp_worst_forecast_amount ,NVL(SCD.opp_forecast_amount * R.conversion_rate,0) opp_forecast_amount ,NVL(SCD.opp_best_forecast_amount * R.conversion_rate,0) opp_best_forecast_amount ,SCD.sales_credit_amount * R.conversion_rate pipeline_amount ,DECODE(SCD.win_loss_indicator, 'W', SCD.sales_credit_amount, SCD.weighted_amount)* R.conversion_rate weighted_amount ,SCD.won_amount * R.conversion_rate won_amount ,AFS.forecast_category_id ,SCD.credit_type_id ,R.to_currency ,R.period_name ,R.period_set_name ,SCD.sales_group_id ,SCD.salesforce_id FROM as_fst_sales_categories AFS ,eni_prod_denorm_hrchy_v EPD ,as_sales_credits_denorm SCD ,as_period_rates R ,mtl_categories_tl MCT ,mtl_system_items_tl MTL ,mtl_categories_tl MCT1 ,as_sales_stages_all_tl STG ,as_statuses_tl STT WHERE TRUNC(sysdate) BETWEEN AFS.start_date_active AND NVL(AFS.end_date_active,TRUNC(sysdate)) AND AFS.product_category_id = EPD.parent_id AND EPD.child_id = SCD.product_category_id AND SCD.forecast_rollup_flag = 'Y' AND R.from_currency = SCD.currency_code AND SCD.product_category_id = MCT.category_id AND SCD.item_id = MTL.inventory_item_id(+) AND SCD.organization_id = MTL.organization_id(+) AND MCT.language = USERENV('LANG') AND MTL.language(+) = USERENV('LANG') AND AFS.product_category_id = MCT1.category_id AND MCT1.language = USERENV('LANG') AND STG.language(+) = USERENV('LANG') AND SCD.sales_stage_id = STG.sales_stage_id(+) AND STT.language = USERENV('LANG') AND SCD.status_code = STT.status_code

View Text - HTML Formatted

SELECT SCD.OPP_DESCRIPTION
, SCD.LEAD_NUMBER OPP_NUMBER
, NVL(MTL.DESCRIPTION
, MCT.DESCRIPTION) PRODUCT_NAME
, MCT1.DESCRIPTION PRODUCT_CATEGORY_NAME
, SCD.CUSTOMER_NAME
, STT.MEANING STATUS
, STG.NAME SALES_STAGE
, SCD.WIN_PROBABILITY
, SCD.FORECAST_DATE
, NVL(SCD.OPP_WORST_FORECAST_AMOUNT * R.CONVERSION_RATE
, 0) OPP_WORST_FORECAST_AMOUNT
, NVL(SCD.OPP_FORECAST_AMOUNT * R.CONVERSION_RATE
, 0) OPP_FORECAST_AMOUNT
, NVL(SCD.OPP_BEST_FORECAST_AMOUNT * R.CONVERSION_RATE
, 0) OPP_BEST_FORECAST_AMOUNT
, SCD.SALES_CREDIT_AMOUNT * R.CONVERSION_RATE PIPELINE_AMOUNT
, DECODE(SCD.WIN_LOSS_INDICATOR
, 'W'
, SCD.SALES_CREDIT_AMOUNT
, SCD.WEIGHTED_AMOUNT)* R.CONVERSION_RATE WEIGHTED_AMOUNT
, SCD.WON_AMOUNT * R.CONVERSION_RATE WON_AMOUNT
, AFS.FORECAST_CATEGORY_ID
, SCD.CREDIT_TYPE_ID
, R.TO_CURRENCY
, R.PERIOD_NAME
, R.PERIOD_SET_NAME
, SCD.SALES_GROUP_ID
, SCD.SALESFORCE_ID
FROM AS_FST_SALES_CATEGORIES AFS
, ENI_PROD_DENORM_HRCHY_V EPD
, AS_SALES_CREDITS_DENORM SCD
, AS_PERIOD_RATES R
, MTL_CATEGORIES_TL MCT
, MTL_SYSTEM_ITEMS_TL MTL
, MTL_CATEGORIES_TL MCT1
, AS_SALES_STAGES_ALL_TL STG
, AS_STATUSES_TL STT
WHERE TRUNC(SYSDATE) BETWEEN AFS.START_DATE_ACTIVE
AND NVL(AFS.END_DATE_ACTIVE
, TRUNC(SYSDATE))
AND AFS.PRODUCT_CATEGORY_ID = EPD.PARENT_ID
AND EPD.CHILD_ID = SCD.PRODUCT_CATEGORY_ID
AND SCD.FORECAST_ROLLUP_FLAG = 'Y'
AND R.FROM_CURRENCY = SCD.CURRENCY_CODE
AND SCD.PRODUCT_CATEGORY_ID = MCT.CATEGORY_ID
AND SCD.ITEM_ID = MTL.INVENTORY_ITEM_ID(+)
AND SCD.ORGANIZATION_ID = MTL.ORGANIZATION_ID(+)
AND MCT.LANGUAGE = USERENV('LANG')
AND MTL.LANGUAGE(+) = USERENV('LANG')
AND AFS.PRODUCT_CATEGORY_ID = MCT1.CATEGORY_ID
AND MCT1.LANGUAGE = USERENV('LANG')
AND STG.LANGUAGE(+) = USERENV('LANG')
AND SCD.SALES_STAGE_ID = STG.SALES_STAGE_ID(+)
AND STT.LANGUAGE = USERENV('LANG')
AND SCD.STATUS_CODE = STT.STATUS_CODE