MATERIALIZED VIEW: APPS.IBW_CMPANALYSIS_TIME_MV

Source

SELECT                                  /*  Fix for bug # 4751315 */
        camp.site_id                                site_id
        , camp.currency_cd_f                        currency_cd_f
        , camp.campaign_source_code_id              campaign_source_code_id
        , camp.transaction_date                     transaction_date
        , sum(visits)                               visits
        , sum(visit_duration)                       visit_duration
        , sum(page_views)                           page_views
        , sum(web_registrations)                    web_registrations
        , sum(daily_uniq_visitors)                  daily_uniq_visitors
        , sum(a_leads)                              a_leads
        , sum(carts)                                carts
        , sum(orders)                               orders
        , sum(booked_amt_f)                         booked_amt_f
        , sum(booked_amt_g)                         booked_amt_g
        , sum(booked_amt_g1)                        booked_amt_g1
        , count(visits)                             d_visits
        , count(visit_duration)                     d_visit_duration
        , count(page_views)                         d_page_views
        , count(web_registrations)                  d_web_registrations
        , count(daily_uniq_visitors)                d_daily_uniq_visitors
        , count(a_leads)                            d_a_leads
        , count(carts)                              d_carts
        , count(orders)                             d_orders
        , 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(*)                                  d_total_cnt
        , (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
             (
                  camp.site_id
                , camp.currency_cd_f
                , camp.campaign_source_code_id
                , camp.transaction_date
                , time.ent_year_id
                , time.ent_qtr_id
                , time.ent_period_id
                , time.week_id
                , time.report_date_julian
             )                                      d_gid
      FROM
        ibw_cmpanalysis_sum_mv  camp,
        FII.FII_TIME_DAY          time
      WHERE
        camp.transaction_date = time.report_date
      GROUP BY
         camp.site_id
         , camp.currency_cd_f
         , camp.campaign_source_code_id
         , camp.transaction_date
         , GROUPING SETS(time.ent_year_id,time.ent_qtr_id,time.ent_period_id,time.week_id,time.report_date_julian)