MATERIALIZED VIEW: APPS.AS_FORECAST_MV

Source

SELECT /*+ use_HASH(SGD) use_hash(SCD) use_hash(AFS)  use_hash(ASP) use_hash(R) */ count(1) cnt, SGD.PARENT_GROUP_ID PARENT_SALES_GROUP_ID, R.PERIOD_NAME CONVERSION_PERIOD_NAME, SCD.CREDIT_TYPE_ID, R.PERIOD_SET_NAME CONVERSION_PERIOD_SET_NAME, AFS.FORECAST_CATEGORY_ID, SUM(R.CONVERSION_STATUS_FLAG) CONVERSION_STATUS_FLAG, SUM(SCD.SALES_CREDIT_AMOUNT*R.CONVERSION_RATE) TOTAL_AMOUNT, SUM(DECODE(nvl(WIN_LOSS_INDICATOR,'L'),'W',SCD.SALES_CREDIT_AMOUNT* R.CONVERSION_RATE, SCD.WEIGHTED_AMOUNT* R.CONVERSION_RATE)) WEIGHTED_AMOUNT, SUM(SCD.WON_AMOUNT* R.CONVERSION_RATE) WON_AMOUNT FROM AS_PERIOD_DAYS ASP, JTF_RS_GROUPS_DENORM SGD, AS_SALES_CREDITS_DENORM SCD, (SELECT FST.forecast_category_id, FST.START_DATE_ACTIVE,
                            FST.END_DATE_ACTIVE, EPD.child_id product_category_id
                     FROM ENI_PROD_DENORM_HRCHY_V EPD, AS_FST_SALES_CATEGORIES FST
                     WHERE EPD.PARENT_ID = FST.PRODUCT_CATEGORY_ID) AFS, AS_PERIOD_RATES R, FND_PROFILE_OPTION_VALUES POV, FND_PROFILE_OPTIONS PO WHERE PO.PROFILE_OPTION_NAME = 'AS_PREFERRED_CURRENCY' AND POV.LEVEL_ID = 10001 AND POV.LEVEL_VALUE = 0 AND PO.PROFILE_OPTION_ID = POV.PROFILE_OPTION_ID AND PO.APPLICATION_ID = POV.APPLICATION_ID AND R.PERIOD_SET_NAME = ASP.PERIOD_SET_NAME AND R.PERIOD_NAME  = ASP.PERIOD_NAME AND R.FROM_CURRENCY= SCD.CURRENCY_CODE AND R.TO_CURRENCY= POV.PROFILE_OPTION_VALUE AND SCD.FORECAST_DATE  = ASP.PERIOD_DAY AND SCD.FORECAST_DATE >= ASP.START_DATE AND SCD.FORECAST_DATE >= SGD.start_date_active AND SCD.FORECAST_DATE >= AFS.START_DATE_ACTIVE AND SCD.FORECAST_DATE <= ASP.END_DATE AND SCD.FORECAST_DATE <=  NVL(SGD.end_date_active,ASP.END_DATE) AND SCD.FORECAST_DATE <=  NVL(AFS.END_DATE_ACTIVE,ASP.END_DATE) AND SGD.GROUP_ID     = SCD.SALES_GROUP_ID AND AFS.PRODUCT_CATEGORY_ID  = SCD.PRODUCT_CATEGORY_ID AND SCD.OPP_DELETED_FLAG  = 'N'AND SCD.FORECAST_ROLLUP_FLAG  = 'Y'group by SGD.PARENT_GROUP_ID, SCD.CREDIT_TYPE_ID, R.PERIOD_SET_NAME, R.PERIOD_NAME, AFS.FORECAST_CATEGORY_ID