MATERIALIZED VIEW: APPS.IBW_VISIT_CUST_TIME_MV

Source

select                           /*  Fix for bug # 4751315 */
        site_id                               site_id
      , customer_id                           customer_id
      , transaction_date                      transaction_date
      , currency_cd_f                         currency_cd_f
      , sum(visits)                           visits
      , sum(repeat_visits)                    repeat_visits
      , sum(visit_duration)                   visit_duration
      , sum(page_views)                       page_views
      , sum(daily_uniq_visitors)              daily_uniq_visitors
      , sum(new_web_customers)                new_web_customers
      , sum(new_web_customers_all)            new_web_customers_all
      , sum(orders_site_visits)               orders_site_visits
      , sum(web_registrations)                web_registrations
      , sum(carts)                            carts
      , sum(orders)                           orders
      , sum(repeat_web_orders)                repeat_web_orders
      , sum(anonymous_carts)                  anonymous_carts
      , sum(total_order_inquiries)            total_order_inquiries
      , sum(total_payment_inquiries)          total_payment_inquiries
      , sum(total_invoice_inquiries)          total_invoice_inquiries
      , sum(a_leads)                          a_leads
      , sum(booked_amt_f)                     booked_amt_f
      , sum(booked_amt_g)                     booked_amt_g
      , sum(booked_amt_g1)                    booked_amt_g1
      , sum(booked_web_orders)                booked_web_orders
      , sum(assisted_web_orders)              assisted_web_orders
      , sum(total_booked_amt_f)               total_booked_amt_f
      , sum(total_booked_amt_g)               total_booked_amt_g
      , sum(total_booked_amt_g1)              total_booked_amt_g1
      , sum(total_booked_orders)              total_booked_orders
      , sum(opt_outs)                         opt_outs
      , (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
             (
                  visit_cust.site_id
                , visit_cust.customer_id
                , transaction_date
                , visit_cust.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(visits)                    d_visits
                , count(repeat_visits)             d_repeat_visits
                , count(visit_duration)            d_visit_duration
                , count(page_views)                d_page_views
                , count(daily_uniq_visitors)       d_daily_uniq_visitors
                , count(new_web_customers)         d_new_web_customers
                , count(new_web_customers_all)     d_new_web_customers_all
                , count(orders_site_visits)        d_orders_site_visits
                , count(web_registrations)         d_web_registrations
                , count(carts)                     d_carts
                , count(orders)                    d_orders
                , count(repeat_web_orders)         d_repeat_web_orders
                , count(anonymous_carts)           d_anonymous_carts
                , count(total_order_inquiries)     d_total_order_inquiries
                , count(total_payment_inquiries)   d_total_payment_inquiries
                , count(total_invoice_inquiries)   d_total_invoice_inquiries
                , count(a_leads)                   d_a_leads
                , 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(booked_web_orders)         d_booked_web_orders
                , count(assisted_web_orders)       d_assisted_web_orders
                , count(total_booked_amt_f)        d_total_booked_amt_f
                , count(total_booked_amt_g)        d_total_booked_amt_g
                , count(total_booked_amt_g1)       d_total_booked_amt_g1
                , count(total_booked_orders)       d_total_booked_orders
                , count(opt_outs)                  d_opt_outs
                , count(*)                         d_total_cnt
       from
         ibw_visit_cust_sum_mv visit_cust,
         FII.FII_TIME_DAY        time
       where
         transaction_date   = time.report_date
      group by
         visit_cust.site_id
       , visit_cust.customer_id
       , transaction_date
       , visit_cust.currency_cd_f
       , GROUPING SETS(time.ent_year_id,time.ent_qtr_id,time.ent_period_id,time.week_id,time.report_date_julian)