MATERIALIZED VIEW: APPS.IBW_KPI_METRICS_TIME_MV

Source

SELECT                                    /*  Fix for bug # 4751315 */
              site_id                           site_id
            , transaction_date                  transaction_date
            , currency_cd_f                     currency_cd_f
            , sum(visits)                       visits
            , sum(web_registrations)            web_registrations
            , sum(carts)                        carts
            , sum(anonymous_carts)              anonymous_carts
            , sum(booked_amt_f)                 booked_amt_f
            , sum(booked_amt_g)                 booked_amt_g
            , sum(booked_amt_g1)                booked_amt_g1
            , sum(orders_site_visits)           orders_site_visits  -- for browse to buy
            , sum(booked_web_orders)            booked_web_orders   -- used also for numerator of Percent Web Orders
            , sum(new_web_customers)            new_web_customers
            , sum(repeat_web_orders)  	        repeat_web_orders
            , sum(total_booked_orders)          total_booked_orders -- denominator for Percent Web Orders
            , (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
            ,  time.ent_year_id                 ent_year_id
            ,  time.ent_qtr_id                  ent_qtr_id
            ,  time.ent_period_id               ent_period_id
            ,  time.week_id                     week_id
            ,  time.report_date_julian          report_date_julian
            ,  grouping_id
                  (
                      site_id
                    , currency_cd_f
                    , transaction_date
                    , time.ent_year_id
                    , time.ent_qtr_id
                    , time.ent_period_id
                    , time.week_id
                    , time.report_date_julian
                  )                             d_gid
            , count(visits)                     d_visits
            , count(web_registrations)          d_web_registrations
            , count(carts)                      d_carts
            , count(anonymous_carts)            d_anonymous_carts
            , count(booked_amt_f)               d_booked_amt_f
            , count(booked_amt_g)               d_booked_amt_g
            , count(booked_amt_g1)              d_booked_amt_g1
            , count(orders_site_visits)         d_orders_site_visits
            , count(booked_web_orders)          d_booked_web_orders
            , count(new_web_customers)          d_new_web_customers
            , count(repeat_web_orders)  	      d_repeat_web_orders
            , count(total_booked_orders)        d_total_booked_orders
            , count(*)                          d_cnt
        FROM  ibw_visit_cust_sum_mv
            , FII.FII_TIME_DAY     time
       WHERE  transaction_date  = time.report_date
       GROUP BY
              site_id
            , currency_cd_f
            , transaction_date
            , GROUPING SETS(time.ent_year_id,time.ent_qtr_id,time.ent_period_id,time.week_id,time.report_date_julian)