MATERIALIZED VIEW: APPS.BIL_BI_RS_GRP_MV

Source

select /* 12.0: bug#4526784 */
               'groups' umarker,
               b.parent_group_id parent_parent_group_id,
               a.parent_group_id parent_group_id,
               a.group_id sales_group_id,
               count(*) xcnt
             from
               JTF.JTF_RS_GROUPS_DENORM a,
               JTF.JTF_RS_GROUPS_DENORM b,
               JTF.JTF_RS_GROUP_USAGES c,
               JTF.JTF_RS_GROUP_USAGES d,
               JTF.JTF_RS_GROUP_USAGES e
             where
               a.parent_group_id = b.group_id
               and a.latest_relationship_flag='Y' and b.latest_relationship_flag='Y'
               and b.immediate_parent_flag='Y'
               and a.group_id=c.group_id and c.usage='SALES'
               and a.parent_group_id=d.group_id and d.usage='SALES'
               and b.parent_group_id=e.group_id and e.usage='SALES'
             group by
               b.parent_group_id,
               a.parent_group_id,
               a.group_id
           UNION ALL
             select
               'top_groups',
               null parent_parent_group_id,
               a.sales_group_id parent_group_id,
               b.group_id sales_group_id,
               COUNT(*)
             FROM
               APPS.BIL_BI_GRP_CNT_MV a,
               JTF.JTF_RS_GROUPS_DENORM b,
               JTF.JTF_RS_GROUP_USAGES c
             WHERE
               gcount = 1
               and a.sales_group_id = b.parent_group_id
               and b.group_id= c.group_id and c.usage='SALES'
               and b.latest_relationship_flag='Y'
             group by
               a.sales_group_id,
               b.group_id