MATERIALIZED VIEW: APPS.ASO_BI_QOT_CUSTB_MV

Source

SELECT  'G' Umarker,
        RSG.Parent_group_id Resource_grp_id,
        NULL Resource_id,
        FACT.Party_id,
        SUM(FACT.newqot_amnt) Newqot_amnt,
        SUM(FACT.newqot_number) Newqot_number,
        SUM(FACT.openqot_amnt) Openqot_amnt,
        SUM(FACT.openqot_number * DECODE(Row_Type, 'OPNNGN', -1, 1)) Openqot_number,
        SUM(FACT.convqot_amnt) Convqot_amnt,
        SUM(FACT.convqot_number) Convqot_number,
        COUNT(FACT.newqot_amnt) Newqot_amnt_cnt,
        COUNT(FACT.newqot_number) Newqot_number_cnt,
        COUNT(FACT.openqot_amnt) Openqot_amnt_cnt,
        COUNT(FACT.openqot_number * DECODE(Row_Type, 'OPNNGN', -1, 1)) Openqot_number_cnt ,
        COUNT(FACT.convqot_amnt) Convqot_amnt_cnt,
        COUNT(FACT.convqot_number) Convqot_number_cnt,
        COUNT(*) All_cnt,
        FACT.Ent_Year_id,
        FACT.Ent_qtr_id,
        FACT.Ent_period_id,
        FACT.Week_id,
        FACT.Day_id,
        DECODE(GROUPING_ID(
                  FACT.Ent_Year_id, FACT.Ent_qtr_id,
                  FACT.Ent_period_id, FACT.Week_id,
                  FACT.Day_id),
                15, 128,
                23, 64,
                27, 32,
                29, 16,
                30, 1) Period_type_id,
        DECODE(GROUPING_ID(
                  FACT.Ent_Year_id, FACT.Ent_qtr_id,
                  FACT.Ent_period_id, FACT.Week_id,
                  FACT.Day_id),
                15, FACT.Ent_year_id,
                23, FACT.Ent_qtr_id,
                27, FACT.Ent_period_id,
                29, FACT.Week_id,
                30, FACT.Day_id) TIME_id,
        GROUPING_ID(RSG.Parent_group_id,
                    FACT.Party_id,
                    FACT.Ent_Year_id, FACT.Ent_qtr_id,
                    FACT.Ent_period_id, FACT.Week_id,
                    FACT.Day_id) Grp_id
FROM    APPS.ASO_BI_QOT_L1_MV FACT,
        JTF.JTF_RS_GROUPS_DENORM RSG,
        JTF.JTF_RS_GROUP_USAGES USG
WHERE   FACT.Resource_grp_id = RSG.Group_id
AND     RSG.Latest_Relationship_Flag = 'Y'
AND     RSG.Parent_group_id = USG.Group_id
AND     USG.Usage = 'SALES'
AND     FACT.Party_id IS NOT NULL
GROUP BY  FACT.Party_id, RSG.Parent_group_id,
GROUPING SETS(FACT.Ent_Year_id, FACT.Ent_qtr_id,
                        FACT.Ent_period_id, FACT.Week_id, FACT.Day_id)
UNION ALL
SELECT  'R' Umarker,
        FACT.Resource_grp_id Resource_grp_id,
        FACT.Resource_id,
        FACT.Party_id,
        SUM(FACT.newqot_amnt) Newqot_amnt,
        SUM(FACT.newqot_number) Newqot_number,
        SUM(FACT.openqot_amnt) Openqot_amnt,
        SUM(FACT.openqot_number * DECODE(Row_Type, 'OPNNGN', -1, 1)) Openqot_number,
        SUM(FACT.convqot_amnt) Convqot_amnt,
        SUM(FACT.convqot_number) Convqot_number,
        COUNT(FACT.newqot_amnt) Newqot_amnt_cnt,
        COUNT(FACT.newqot_number) Newqot_number_cnt,
        COUNT(FACT.openqot_amnt) Openqot_amnt_cnt,
        COUNT(FACT.openqot_number * DECODE(Row_Type, 'OPNNGN', -1, 1)) Openqot_number_cnt,
        COUNT(FACT.convqot_amnt) Convqot_amnt_cnt,
        COUNT(FACT.convqot_number) Convqot_number_cnt,
        COUNT(*) All_cnt,
        FACT.Ent_Year_id,
        FACT.Ent_qtr_id,
        FACT.Ent_period_id,
        FACT.Week_id,
        FACT.Day_id,
        DECODE(GROUPING_ID(
                  FACT.Ent_Year_id, FACT.Ent_qtr_id,
                  FACT.Ent_period_id, FACT.Week_id,
                  FACT.Day_id),
                15, 128,
                23, 64,
                27, 32,
                29, 16,
                30, 1) Period_type_id,
        DECODE(GROUPING_ID(
                  FACT.Ent_Year_id, FACT.Ent_qtr_id,
                  FACT.Ent_period_id, FACT.Week_id,
                  FACT.Day_id),
                15, FACT.Ent_year_id,
                23, FACT.Ent_qtr_id,
                27, FACT.Ent_period_id,
                29, FACT.Week_id,
                30, FACT.Day_id) TIME_id,
        GROUPING_ID(FACT.Resource_grp_id, FACT.Resource_id,
                    FACT.Party_id,
                    FACT.Ent_Year_id, FACT.Ent_qtr_id,
                    FACT.Ent_period_id, FACT.Week_id,
                    FACT.Day_id) Grp_id
FROM    APPS.ASO_BI_QOT_L1_MV FACT
WHERE   FACT.Party_id IS NOT NULL
GROUP BY  FACT.Party_id, FACT.Resource_grp_id, FACT.Resource_id,
          GROUPING SETS(FACT.Ent_Year_id, FACT.Ent_qtr_id,
                        FACT.Ent_period_id, FACT.Week_id, FACT.Day_id)