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_DR_COSTS_01_MV

Source


select
  /* 12.0: bug#4526784 */
  rof.repair_organization_id repair_organization_id
, nvl(items.vbh_category_id,-1) product_category_id
, rof.item_org_id item_org_id
, rof.customer_id customer_id
, rof.repair_type_id repair_type_id
, grouping_id( rof.repair_organization_id
             , nvl(items.vbh_category_id,-1)
             , rof.item_org_id
             , rof.customer_id
             , rof.repair_type_id
             , ent_qtr_id
             , ent_period_id
             , week_id
             , report_date_julian
             ) grp_id
, 31 - grouping_id( rof.item_org_id
                  , rof.customer_id
                  , nvl(items.vbh_category_id,-1)
                  , rof.repair_organization_id
                  , rof.repair_type_id
                  ) aggregation_flag
, decode( grouping_id ( ent_qtr_id
                      , ent_period_id
                      , week_id
                      , report_date_julian
                      )
        , 0, report_date_julian
        , 1, week_id
        , 3, ent_period_id
        , 7, ent_qtr_id
        ) time_id
, decode( grouping_id( ent_qtr_id
                     , ent_period_id
                     , week_id
                     , report_date_julian
                     )
        , 0, 1
        , 1, 16
        , 3, 32
        , 7, 64
        ) period_type_id
, cal.ent_qtr_id qtr_id
, cal.ent_period_id period_id
, cal.week_id week_id
, cal.report_date_julian day_id
, sum( costs.material_cost_g ) material_cost_g
, sum( costs.material_cost_sg ) material_cost_sg
, sum( costs.labor_cost_g ) labor_cost_g
, sum( costs.labor_cost_sg ) labor_cost_sg
, sum( costs.expense_cost_g ) expense_cost_g
, sum( costs.expense_cost_sg ) expense_cost_sg
, count( costs.material_cost_g ) mv_material_cost_g_cnt
, count( costs.material_cost_sg ) mv_material_cost_sg_cnt
, count( costs.labor_cost_g ) mv_labor_cost_g_cnt
, count( costs.labor_cost_sg ) mv_labor_cost_sg_cnt
, count( costs.expense_cost_g ) mv_expense_cost_g_cnt
, count( costs.expense_cost_sg ) mv_expense_cost_sg_cnt
, count(*) mv_cnt
from
  ISC.ISC_DR_REPAIR_ORDERS_F rof
, ISC.ISC_DR_COSTS_F costs
, ENI.ENI_OLTP_ITEM_STAR items
, FII.FII_TIME_DAY cal
where
    rof.item_org_id = items.id
and rof.repair_line_id = costs.repair_line_id
and rof.dbi_date_closed = cal.report_date
group by
  rof.repair_organization_id
, rof.repair_type_id
, cal.ent_qtr_id
, rollup( rof.customer_id )
, rollup( nvl(items.vbh_category_id,-1)
        , rof.item_org_id
        )
, rollup( cal.ent_period_id
        , cal.week_id
        , cal.report_date_julian
        )