eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

MATERIALIZED VIEW: APPS.BIV_ACT_SUM_MV

Source


select /* 12.0: bug#4526784 */
  decode(grouping_id( c.ent_year_id
                    , c.ent_qtr_id
                    , c.ent_period_id
                    , c.week_id
                    , c.report_date_julian ), 0, c.report_date_julian
                                            , 1, c.week_id
                                            , 3, c.ent_period_id
                                            , 7, c.ent_qtr_id
                                            , 15, c.ent_year_id) time_id
, decode(grouping_id( c.ent_year_id
                    , c.ent_qtr_id
                    , c.ent_period_id
                    , c.week_id
                    , c.report_date_julian ), 0, 1
                                            , 1, 16
                                            , 3, 32
                                            , 7, 64
                                            , 15, 128) period_type_id
, decode( grouping_id( nvl(pc.master_id,pc.id)
                     , f.customer_id
                     , f.owner_group_id
                     , f.sr_creation_channel ) , 14, 5 -- Channel
                                               , 13, 3 -- Assignment Group
                                               , 11, 2 -- Customer
                                               , 7, 1 -- Prod
                                               , 0 ) grp_id
, f.incident_type_id
, f.incident_severity_id
, pc.vbh_category_id
, nvl(pc.master_id,pc.id) product_id
, f.customer_id
, f.owner_group_id
, f.sr_creation_channel
, sum(f.first_opened_count) first_opened_count
, sum(f.reopened_count) reopened_count
, sum(f.closed_count) closed_count
/* the following columns prefixed with mv_ exist solely to enable
   MV to be fast refreshable */
, grouping_id( f.incident_type_id
             , nvl(pc.master_id,pc.id)
             , pc.vbh_category_id
             , f.incident_severity_id
             , f.customer_id
             , f.owner_group_id
             , f.sr_creation_channel
             , c.ent_year_id
             , c.ent_qtr_id
             , c.ent_period_id
             , c.week_id
             , c.report_date_julian )  mv_grouping_id
, c.report_date_julian                 mv_day_id
, c.week_id                            mv_week_id
, c.ent_period_id                      mv_period_id
, c.ent_qtr_id                         mv_ent_qtr_id
, c.ent_year_id                        mv_ent_year_id
, count(f.first_opened_count) mv_first_opened_count
, count(f.reopened_count) mv_reopened_count
, count(f.closed_count) mv_closed_count
, count(*) mv_count
/* end of fast refreshable needed columns */
from
  BIV.BIV_DBI_ACTIVITY_SUM_F f
, FII.FII_TIME_DAY c
, ENI.ENI_OLTP_ITEM_STAR pc
where
    f.activity_date = c.report_date
and f.inventory_item_id = pc.inventory_item_id
and f.inv_organization_id = pc.organization_id
group by
  grouping sets ( (c.ent_year_id, c.ent_qtr_id, c.ent_period_id, c.week_id, c.report_date_julian)
                , (c.ent_year_id, c.ent_qtr_id, c.ent_period_id, c.week_id)
                , (c.ent_year_id, c.ent_qtr_id, c.ent_period_id)
                , (c.ent_year_id, c.ent_qtr_id)
                , (c.ent_year_id) )
, f.incident_type_id
, f.incident_severity_id
, pc.vbh_category_id
, grouping sets ( (nvl(pc.master_id,pc.id))
                , (f.customer_id)
                , (f.owner_group_id)
                , (f.sr_creation_channel)
                , (nvl(pc.master_id,pc.id),f.customer_id,f.owner_group_id,f.sr_creation_channel)
                )