MATERIALIZED VIEW: APPS.ENI_DBI_TOO_MV

Source

SELECT /* 12.0: bug#4526784 */
          CUSTOMER_ID,
          OPTY_ID,
          SALES_CREDIT_AMT ,
          SALES_CREDIT_AMT_S ,
          WIN_PROBABILITY,
          CLOSE_DATE,
          PRODUCT_CATEGORY_ID,
          ITEM_ID,
          SALESREP_ID,
          SALES_GROUP_ID,
          RANK_PROD_ITEM,
          RANK() OVER (ORDER BY (SALES_CREDIT_AMT) DESC) RANK_ALL
        FROM
        (
          SELECT
            CUSTOMER_ID,
            OPTY_ID,
            SALES_CREDIT_AMT,
            SALES_CREDIT_AMT_S,
            WIN_PROBABILITY,
            CLOSE_DATE,
            PRODUCT_CATEGORY_ID,
            ITEM_ID,
            SALESREP_ID,
            SALES_GROUP_ID,
            RANK() OVER (PARTITION BY (PRODUCT_CATEGORY_ID||ITEM_ID)
                                  ORDER BY (SALES_CREDIT_AMT) DESC ) RANK_PROD_ITEM
          FROM
          (
            SELECT
              CUSTOMER_ID,
              OPTY_ID,
              SUM(SALES_CREDIT_AMT) SALES_CREDIT_AMT ,
              SUM(SALES_CREDIT_AMT_S) SALES_CREDIT_AMT_S ,
              WIN_PROBABILITY,
              TO_DATE(OPTY_CLOSE_TIME_ID,'J') CLOSE_DATE,
              DECODE(ITEM.ID, '-1--99',PRODUCT_CATEGORY_ID, ITEM.VBH_CATEGORY_ID) PRODUCT_CATEGORY_ID,
              NVL(ITEM.MASTER_ID, ITEM.ID) ITEM_ID,
              OWNER_SALESREP_ID SALESREP_ID,
              OWNER_SALES_GROUP_ID SALES_GROUP_ID
            FROM
              BIL.BIL_BI_OPDTL_F FACT,
              ENI_OLTP_ITEM_STAR ITEM
            WHERE
              SALES_CREDIT_AMT IS NOT NULL
              AND OPEN_STATUS_FLAG='Y'
              AND FORECAST_ROLLUP_FLAG = 'Y'
              AND ITEM.INVENTORY_ITEM_ID=FACT.ITEM_ID
              AND ITEM.ORGANIZATION_ID= FACT.ITEM_ORGANIZATION_ID
            GROUP BY
              CUSTOMER_ID,
              OPTY_ID,
              OPTY_CLOSE_TIME_ID,
              WIN_PROBABILITY,
              NVL(ITEM.MASTER_ID, ITEM.ID),
              DECODE(ITEM.ID, '-1--99',PRODUCT_CATEGORY_ID, ITEM.VBH_CATEGORY_ID),
              FACT.OWNER_SALES_GROUP_ID,
              FACT.OWNER_SALESREP_ID
          )
        )
        WHERE
          RANK_PROD_ITEM <= 25