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.ISC_FS_003_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
, grouping_id( e.vbh_category_id
             , nvl(e.master_id,e.id)
             , a.customer_id ) grp_id
, a.act_bac_district_id parent_district_id
, a.act_bac_assignee_type record_type
, a.act_bac_assignee_id district_id
, to_char(a.act_bac_assignee_id) || '.' || a.act_bac_district_id district_id_c
, a.task_type_id
, a.customer_id
, nvl(e.master_id,e.id) product_id
, e.vbh_category_id
, sum(nvl(a.first_opened,0)) first_opened
, sum(nvl(a.reopened,0)) reopened
, sum(nvl(a.closed,0)) closed
/* the following columns prefixed with mv_ exist solely to enable
   MV to be fast refreshable */
, 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
, grouping_id( c.ent_year_id
             , c.ent_qtr_id
             , c.ent_period_id
             , c.week_id
             , c.report_date_julian
             , a.act_bac_district_id
             , a.act_bac_assignee_type
             , a.act_bac_assignee_id
             , to_char(a.act_bac_assignee_id) || '.' || a.act_bac_district_id
             , a.task_type_id
             , a.customer_id
             , nvl(e.master_id,e.id)
             , e.vbh_category_id
             )                 mv_grp_id
, count(*)                     mv_count
, count(nvl(a.first_opened,0)) mv_first_opened
, count(nvl(a.reopened,0))     mv_reopened
, count(nvl(a.closed,0))       mv_closed
/* end of fast refreshable needed columns */
from
  ISC.ISC_FS_TASK_ACTIVITY_F a
, ENI.ENI_OLTP_ITEM_STAR e
, FII.FII_TIME_DAY c
where
    a.task_type_rule = 'DISPATCH'
and a.source_object_type_code = 'SR'
and a.deleted_flag = 'N'
and a.inventory_item_id = e.inventory_item_id
and a.inv_organization_id = e.organization_id
and a.activity_date = c.report_date
group by
  c.ent_year_id
, rollup(c.ent_qtr_id, c.ent_period_id, c.week_id, c.report_date_julian)
, a.act_bac_district_id
, a.act_bac_assignee_type
, a.act_bac_assignee_id
, to_char(a.act_bac_assignee_id) || '.' || a.act_bac_district_id
, a.task_type_id
, rollup( e.vbh_category_id
        , nvl(e.master_id,e.id)
        , a.customer_id
        )