MATERIALIZED VIEW: APPS.ISC_DR_CHARGES_02_MV

Source

select
  /* 12.0: bug#4526784 */
  mv1.repair_organization_id repair_organization_id
, nvl(edh.parent_id, -1) product_category_id
, mv1.customer_id customer_id
, mv1.repair_type_id repair_type_id
, grouping_id( mv1.customer_id
             , nvl(edh.parent_id, -1)
             , mv1.repair_organization_id
             , mv1.repair_type_id
             , mv1.time_id
             , mv1.period_type_id
             ) grp_id
, 15 - grouping_id( mv1.customer_id
                  , nvl(edh.parent_id, -1)
                  , mv1.repair_organization_id
                  , mv1.repair_type_id
                  ) aggregation_flag
, mv1.time_id time_id
, mv1.period_type_id period_type_id
, sum( mv1.material_charges_g ) material_charges_g
, sum( mv1.material_charges_sg ) material_charges_sg
, sum( mv1.labor_charges_g ) labor_charges_g
, sum( mv1.labor_charges_sg ) labor_charges_sg
, sum( mv1.expense_charges_g ) expense_charges_g
, sum( mv1.expense_charges_sg ) expense_charges_sg
, count( mv1.material_charges_g )  mv_material_charges_g_cnt
, count( mv1.material_charges_sg ) mv_material_charges_sg_cnt
, count( mv1.labor_charges_g ) mv_labor_charges_g_cnt
, count( mv1.labor_charges_sg ) mv_labor_charges_sg_cnt
, count( mv1.expense_charges_g ) mv_expense_charges_g_cnt
, count( mv1.expense_charges_sg ) mv_expense_charges_sg_cnt
, count(*) mv_cnt
from
  isc_dr_charges_01_mv mv1
, ENI.ENI_DENORM_HIERARCHIES edh
, INV.MTL_DEFAULT_CATEGORY_SETS mdcs
where
    mv1.product_category_id = edh.child_id
and edh.object_type = 'CATEGORY_SET'
and edh.object_id = mdcs.category_set_id
and edh.dbi_flag = 'Y'
and mdcs.functional_area_id = 11
and edh.top_node_flag = 'Y'
and mv1.aggregation_flag  = 15
group by
  mv1.time_id
, mv1.period_type_id
, nvl(edh.parent_id,-1)
, rollup( mv1.repair_organization_id
        , mv1.customer_id
        )
, rollup( mv1.repair_type_id )