MATERIALIZED VIEW: APPS.ENI_DBI_TOB_MV

Source

SELECT /* 12.0: bug#4526784 */
CUSTOMER_ID,
        RESOURCE_ID,
        SALES_GRP_ID,
        ORDER_NUMBER,
        LINE_NUMBER,
        ITEM_CATEGORY_ID,
        ITEM_ID,
        BOOKED_DATE,
        SCHEDULE_DATE,
        BOOKED_AMT_G,
        BOOKED_AMT_G1,
        RANK_PROD_ITEM,
                    RANK () OVER( ORDER BY BOOKED_AMT_G DESC ) RANK_ALL
FROM (SELECT
                          f.customer_id                     CUSTOMER_ID,
        sc.resource_id                    RESOURCE_ID,
        sc.sales_grp_id                   SALES_GRP_ID,
        f.order_number                    ORDER_NUMBER,
        f.line_number                     LINE_NUMBER,
        nvl(item.vbh_category_id,-1)      ITEM_CATEGORY_ID,
        nvl(item.master_id,item.id)       ITEM_ID,
        f.time_booked_date_id             BOOKED_DATE,
        f.time_schedule_date_id           SCHEDULE_DATE,
        f.booked_amt_g                    BOOKED_AMT_G,
        f.booked_amt_g1                   BOOKED_AMT_G1,
        RANK() OVER (PARTITION BY (to_char(item.vbh_category_id)||to_char(item.inventory_item_id))
                            ORDER BY (f.BOOKED_AMT_G) DESC) RANK_PROD_ITEM
      FROM
        ISC.ISC_BOOK_SUM2_F     f,
        ISC.ISC_SALES_CREDITS_F sc,
        ENI.ENI_OLTP_ITEM_STAR  item
      WHERE
        f.line_id = sc.line_id
        AND f.BOOKED_AMT_G IS NOT NULL
        AND f.inventory_item_id = item.inventory_item_id
        AND f.item_inv_org_id = item.organization_id
        AND f.order_source_id <> 10
        AND f.order_source_id <> 27
        AND f.ordered_quantity <> 0
        AND f.fulfilled_qty_inv = 0
        AND f.open_flag = 'Y'
        AND f.item_type_code <> 'SERVICE'
        AND f.line_category_code <> 'RETURN'
	AND f.charge_periodicity_code is NULL)
	WHERE RANK_PROD_ITEM <= 25