MATERIALIZED VIEW: APPS.IBE_BI_TOP_PROD_MV

Source

SELECT
      (CASE WHEN GROUPING (TIME.report_date_julian) = 0 THEN TIME.report_date_julian
      WHEN GROUPING (TIME.week_id)=0 THEN TIME.week_id
      WHEN GROUPING (TIME.ent_period_id)=0 THEN TIME.ent_period_id
      WHEN GROUPING (TIME.ent_qtr_id)=0 THEN TIME.ent_qtr_id
      Else TIME.ent_year_id End) Time_Id,
      (CASE WHEN GROUPING (TIME.report_date_julian) = 0 THEN 1
      WHEN GROUPING (TIME.week_id)=0 THEN 16
      WHEN GROUPING (TIME.ent_period_id)=0 THEN 32
      WHEN GROUPING (TIME.ent_qtr_id)=0 THEN 64 Else 128 END) Period_Type_id,
      MINISITE_ID,
      CATEGORY_ID,
      ITEM_ID,
      RESOURCE_FLAG,
      CURRENCY_CD_F,
      COUNT(decode(UMARKER,'ORDERS',NUM_OF_ORD_CART,NULL)) TOT_ORD_COUNT,
      COUNT(decode(UMARKER, 'CARTS',NUM_OF_ORD_CART,NULL)) TOT_CART_COUNT,
      COUNT (DECODE (no_orders, 0, NULL, no_orders)) con_ord,      --  Change for bug 5373132
      SUM(decode(UMARKER,'ORDERS',NUM_OF_LINES,0)) NUM_OF_ORD_LINES,
      SUM(decode(UMARKER,'CARTS',NUM_OF_LINES,0)) NUM_OF_CART_LINES,
      SUM(decode(UMARKER,'ORDERS',BOOKED_AMT_F,0)) BOOKED_AMOUNT_F,
      SUM(decode(UMARKER,'ORDERS',BOOKED_AMT_G,0)) BOOKED_AMOUNT_G,
      SUM(decode(UMARKER,'ORDERS',BOOKED_AMT_G1,0)) BOOKED_AMOUNT_G1,
      SUM(decode(UMARKER,'CARTS',BOOKED_AMT_F,0)) CARTS_AMT_F,
      SUM(decode(UMARKER,'CARTS',BOOKED_AMT_G,0)) CARTS_AMT_G,
      SUM(decode(UMARKER,'CARTS',BOOKED_AMT_G1,0)) CARTS_AMT_G1,
      SUM(decode(UMARKER,'ORDERS',BOOKED_LIST_AMT_F,0)) BOOKED_LIST_AMT_F,
      SUM(decode(UMARKER,'ORDERS',BOOKED_LIST_AMT_G,0)) BOOKED_LIST_AMT_G,
      SUM(decode(UMARKER,'ORDERS',BOOKED_LIST_AMT_G1,0)) BOOKED_LIST_AMT_G1,
      SUM(decode(UMARKER,'ORDERS',DISCOUNT_AMT_F,0)) DISCOUNT_AMOUNT_F,
      SUM(decode(UMARKER,'ORDERS',DISCOUNT_AMT_G,0)) DISCOUNT_AMOUNT_G,
      SUM(decode(UMARKER,'ORDERS',DISCOUNT_AMT_G1,0)) DISCOUNT_AMOUNT_G1,
      TIME.ENT_YEAR_ID,
      TIME.ent_qtr_id,
      TIME.ent_period_id,
      TIME.week_id,
      TIME.report_date_julian,
      COUNT(decode(UMARKER,'ORDERS',NUM_OF_ORD_CART,NULL)) D_TOT_ORD_COUNT_CNT,
      COUNT(decode(UMARKER, 'CARTS',NUM_OF_ORD_CART,NULL)) D_TOT_CART_COUNT_CNT,
      COUNT(NO_ORDERS) D_CON_ORD_CNT,
      COUNT(decode(UMARKER,'ORDERS',BOOKED_AMT_F,0)) D_BOOKED_AMOUNT_F_CNT,
      COUNT(decode(UMARKER,'ORDERS',BOOKED_AMT_G,0)) D_BOOKED_AMOUNT_G_CNT,
      COUNT(decode(UMARKER,'ORDERS',BOOKED_AMT_G1,0)) D_BOOKED_AMOUNT_G1_CNT,
      COUNT(decode(UMARKER,'CARTS',BOOKED_AMT_F,0)) D_CARTS_AMT_F_CNT,
      COUNT(decode(UMARKER,'CARTS',BOOKED_AMT_G,0)) D_CARTS_AMT_G_CNT,
      COUNT(decode(UMARKER,'CARTS',BOOKED_AMT_G1,0)) D_CARTS_AMT_G1_CNT,
      COUNT(decode(UMARKER,'ORDERS',NUM_OF_LINES,0)) D_NUM_OF_ORD_LINES_CNT,
      COUNT(decode(UMARKER,'CARTS',NUM_OF_LINES,0)) D_NUM_OF_CART_LINES_CNT,
      COUNT(decode(UMARKER,'ORDERS',BOOKED_LIST_AMT_F,0)) D_BOOKED_LIST_AMT_F_CNT,
      COUNT(decode(UMARKER,'ORDERS',BOOKED_LIST_AMT_G,0)) D_BOOKED_LIST_AMT_G_CNT,
      COUNT(decode(UMARKER,'ORDERS',BOOKED_LIST_AMT_G1,0)) D_BOOKED_LIST_AMT_G1_CNT,
      COUNT(decode(UMARKER,'ORDERS',DISCOUNT_AMT_F,0)) D_DISCOUNT_AMOUNT_F_CNT,
      COUNT(decode(UMARKER,'ORDERS',DISCOUNT_AMT_G,0)) D_DISCOUNT_AMOUNT_G_CNT,
      COUNT(decode(UMARKER,'ORDERS',DISCOUNT_AMT_G1,0)) D_DISCOUNT_AMOUNT_G1_CNT,
      GROUPING_ID (MINISITE_ID,
                   ITEM_ID,
                   CATEGORY_ID,
                   RESOURCE_FLAG,
                   CURRENCY_CD_F,
                   TIME.ent_year_id,
                   TIME.ent_qtr_id,
                   TIME.ent_period_id,
                   TIME.week_id,
                   TIME.report_date_julian) D_GID,
      COUNT(*) D_total_count_time_mv
    FROM
      APPS.IBE_BI_ITEM_MV FACT,
      FII.FII_TIME_DAY TIME
    WHERE
      FACT.BOOKED_DATE_ID = Time.report_date
    GROUP BY
      MINISITE_ID,
      ITEM_ID,
      CATEGORY_ID,
      RESOURCE_FLAG,
      CURRENCY_CD_F,
      TIME.ent_year_id,
      ROLLUP(TIME.ent_qtr_id,TIME.ent_period_id,TIME.week_id,TIME.report_date_julian)