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_MAINT_005_MV

Source


select
  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( f.organization_id
             , wo.asset_group_id
             , nvl(wo.instance_id,-1)
             , nvl(wo.activity_id,-1) ) grp_id
, f.organization_id
, wo.asset_group_id asset_group_id
, nvl(wo.instance_id,-1) instance_id
, nvl(wo.activity_id,-1) activity_id
, f.department_id department_id
, f.maint_cost_category
, sum(decode(f.estimated_flag,'Y',f.actual_mat_cost_b,0)) e_actual_mat_cost_b
, sum(decode(f.estimated_flag,'Y',f.actual_lab_cost_b,0)) e_actual_lab_cost_b
, sum(decode(f.estimated_flag,'Y',f.actual_eqp_cost_b,0)) e_actual_eqp_cost_b
, sum(decode(f.estimated_flag,'N',f.actual_mat_cost_b,0)) ne_actual_mat_cost_b
, sum(decode(f.estimated_flag,'N',f.actual_lab_cost_b,0)) ne_actual_lab_cost_b
, sum(decode(f.estimated_flag,'N',f.actual_eqp_cost_b,0)) ne_actual_eqp_cost_b
, sum(f.estimated_mat_cost_b) estimated_mat_cost_b
, sum(f.estimated_lab_cost_b) estimated_lab_cost_b
, sum(f.estimated_eqp_cost_b) estimated_eqp_cost_b
/* primary global currency */
, sum(decode(f.estimated_flag,'Y',f.actual_mat_cost_b * f.conversion_rate1,0)) e_actual_mat_cost_g
, sum(decode(f.estimated_flag,'Y',f.actual_lab_cost_b * f.conversion_rate1,0)) e_actual_lab_cost_g
, sum(decode(f.estimated_flag,'Y',f.actual_eqp_cost_b * f.conversion_rate1,0)) e_actual_eqp_cost_g
, sum(decode(f.estimated_flag,'N',f.actual_mat_cost_b * f.conversion_rate1,0)) ne_actual_mat_cost_g
, sum(decode(f.estimated_flag,'N',f.actual_lab_cost_b * f.conversion_rate1,0)) ne_actual_lab_cost_g
, sum(decode(f.estimated_flag,'N',f.actual_eqp_cost_b * f.conversion_rate1,0)) ne_actual_eqp_cost_g
, sum(f.estimated_mat_cost_b * f.conversion_rate1) estimated_mat_cost_g
, sum(f.estimated_lab_cost_b * f.conversion_rate1) estimated_lab_cost_g
, sum(f.estimated_eqp_cost_b * f.conversion_rate1) estimated_eqp_cost_g
/* secondary global currency */
, sum(decode(f.estimated_flag,'Y',f.actual_mat_cost_b * f.conversion_rate2,0)) e_actual_mat_cost_sg
, sum(decode(f.estimated_flag,'Y',f.actual_lab_cost_b * f.conversion_rate2,0)) e_actual_lab_cost_sg
, sum(decode(f.estimated_flag,'Y',f.actual_eqp_cost_b * f.conversion_rate2,0)) e_actual_eqp_cost_sg
, sum(decode(f.estimated_flag,'N',f.actual_mat_cost_b * f.conversion_rate2,0)) ne_actual_mat_cost_sg
, sum(decode(f.estimated_flag,'N',f.actual_lab_cost_b * f.conversion_rate2,0)) ne_actual_lab_cost_sg
, sum(decode(f.estimated_flag,'N',f.actual_eqp_cost_b * f.conversion_rate2,0)) ne_actual_eqp_cost_sg
, sum(f.estimated_mat_cost_b * f.conversion_rate2) estimated_mat_cost_sg
, sum(f.estimated_lab_cost_b * f.conversion_rate2) estimated_lab_cost_sg
, sum(f.estimated_eqp_cost_b * f.conversion_rate2) estimated_eqp_cost_sg
/* the following columns prefixed with mv_ exist solely to enable
   MV to be fast refreshable */
, grouping_id( f.organization_id
             , wo.asset_group_id
             , nvl(wo.instance_id,-1)
             , nvl(wo.activity_id,-1)
             , f.department_id
             , f.maint_cost_category
             , 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(decode(f.estimated_flag,'Y',f.actual_mat_cost_b,0)) mv_e_actual_mat_cost_b
, count(decode(f.estimated_flag,'Y',f.actual_lab_cost_b,0)) mv_e_actual_lab_cost_b
, count(decode(f.estimated_flag,'Y',f.actual_eqp_cost_b,0)) mv_e_actual_eqp_cost_b
, count(decode(f.estimated_flag,'N',f.actual_mat_cost_b,0)) mv_ne_actual_mat_cost_b
, count(decode(f.estimated_flag,'N',f.actual_lab_cost_b,0)) mv_ne_actual_lab_cost_b
, count(decode(f.estimated_flag,'N',f.actual_eqp_cost_b,0)) mv_ne_actual_eqp_cost_b
, count(f.estimated_mat_cost_b) mv_estimated_mat_cost_b
, count(f.estimated_lab_cost_b) mv_estimated_lab_cost_b
, count(f.estimated_eqp_cost_b) mv_estimated_eqp_cost_b
, count(decode(f.estimated_flag,'Y',f.actual_mat_cost_b * f.conversion_rate1,0)) mv_e_actual_mat_cost_g
, count(decode(f.estimated_flag,'Y',f.actual_lab_cost_b * f.conversion_rate1,0)) mv_e_actual_lab_cost_g
, count(decode(f.estimated_flag,'Y',f.actual_eqp_cost_b * f.conversion_rate1,0)) mv_e_actual_eqp_cost_g
, count(decode(f.estimated_flag,'N',f.actual_mat_cost_b * f.conversion_rate1,0)) mv_ne_actual_mat_cost_g
, count(decode(f.estimated_flag,'N',f.actual_lab_cost_b * f.conversion_rate1,0)) mv_ne_actual_lab_cost_g
, count(decode(f.estimated_flag,'N',f.actual_eqp_cost_b * f.conversion_rate1,0)) mv_ne_actual_eqp_cost_g
, count(f.estimated_mat_cost_b * f.conversion_rate1) mv_estimated_mat_cost_g
, count(f.estimated_lab_cost_b * f.conversion_rate1) mv_estimated_lab_cost_g
, count(f.estimated_eqp_cost_b * f.conversion_rate1) mv_estimated_eqp_cost_g
, count(decode(f.estimated_flag,'Y',f.actual_mat_cost_b * f.conversion_rate2,0)) mv_e_actual_mat_cost_sg
, count(decode(f.estimated_flag,'Y',f.actual_lab_cost_b * f.conversion_rate2,0)) mv_e_actual_lab_cost_sg
, count(decode(f.estimated_flag,'Y',f.actual_eqp_cost_b * f.conversion_rate2,0)) mv_e_actual_eqp_cost_sg
, count(decode(f.estimated_flag,'N',f.actual_mat_cost_b * f.conversion_rate2,0)) mv_ne_actual_mat_cost_sg
, count(decode(f.estimated_flag,'N',f.actual_lab_cost_b * f.conversion_rate2,0)) mv_ne_actual_lab_cost_sg
, count(decode(f.estimated_flag,'N',f.actual_eqp_cost_b * f.conversion_rate2,0)) mv_ne_actual_eqp_cost_sg
, count(f.estimated_mat_cost_b * f.conversion_rate2) mv_estimated_mat_cost_sg
, count(f.estimated_lab_cost_b * f.conversion_rate2) mv_estimated_lab_cost_sg
, count(f.estimated_eqp_cost_b * f.conversion_rate2) mv_estimated_eqp_cost_sg
, count(*) mv_count
/* end of fast refreshable needed columns */
from
  ISC.ISC_MAINT_WO_CST_SUM_F f
, ISC.ISC_MAINT_WORK_ORDERS_F wo
, FII.FII_TIME_DAY c
where
    f.completion_date = c.report_date
and f.work_order_id = wo.work_order_id
and f.organization_id = wo.organization_id
group by
  c.ent_year_id
, rollup ( c.ent_qtr_id
         , c.ent_period_id
         , c.week_id
         , c.report_date_julian )
, f.organization_id
, rollup( wo.asset_group_id
        , nvl(wo.instance_id,-1) )
, rollup( nvl(wo.activity_id,-1) )
, f.department_id
, f.maint_cost_category