MATERIALIZED VIEW: APPS.OPI_PGM_CAT_MV

Source

Select /* 12.0: bug#4526784 */ mv.organization_id,
           parent_id item_category_id,
           grouping_id (mv.organization_id,
                        cat.parent_id,
                        time_id,
                        period_type_id) grouping_id,
           mv.time_id,
           mv.period_type_id,
           sum (mv.fulfilled_val_g) fulfilled_val_g,
           count (mv.fulfilled_val_g) fulfilled_val_g_cnt,
           sum (mv.fulfilled_val_b) fulfilled_val_b,
           count (mv.fulfilled_val_b) fulfilled_val_b_cnt,
           sum (mv.cogs_val_g) cogs_val_g,
           count (mv.cogs_val_g) cogs_val_g_cnt,
           sum (mv.cogs_val_b) cogs_val_b,
           count (mv.cogs_val_b) cogs_val_b_cnt,
           sum (mv.fulfilled_val_sg) fulfilled_val_sg,
           count (mv.fulfilled_val_sg) fulfilled_val_sg_cnt,
           sum (mv.cogs_val_sg) cogs_val_sg,
           count (mv.cogs_val_sg) cogs_val_sg_cnt,
           count(*) cnt
      FROM  opi_pgm_sum_mv mv,
            ENI.ENI_DENORM_HIERARCHIES cat,
            INV.MTL_DEFAULT_CATEGORY_SETS mtl
      WHERE mv.item_category_id = cat.child_id
        AND cat.object_type = 'CATEGORY_SET'
        AND cat.object_id = mtl.category_set_id
        AND cat.dbi_flag = 'Y'
        AND mtl.functional_area_id = 11
        AND cat.top_node_flag = 'Y'
        AND customer_item_cat_flag = 5
      GROUP BY
            mv.organization_id,
            cat.parent_id,
            time_id,
            period_type_id