MATERIALIZED VIEW: APPS.ASO_BI_QOT_SG_MV

Source

SELECT /* 12.0: bug#4526784 */  'G' umarker,
        PRNTMV.Parent_group_id  Parent_Resource_grp_id,
        RSG.Parent_group_id Resource_grp_id,
        NULL Resource_id,
        'Y' Resource_grp_flag,
        SUM(FACT.newqot_amnt) Newqot_amnt,
        SUM(FACT.sec_newqot_amnt) sec_Newqot_amnt,
        SUM(FACT.newqot_number) Newqot_number,
        SUM(FACT.openqot_amnt) Openqot_amnt,
        SUM(FACT.sec_openqot_amnt) sec_Openqot_amnt,
        SUM(
        FACT.openqot_number *
        DECODE(Row_Type, 'OPNNGN', -1, 1)
        ) Openqot_number,
        SUM(FACT.convqot_amnt) Convqot_amnt,
        SUM(FACT.sec_convqot_amnt) sec_Convqot_amnt,
        SUM(FACT.convqot_number) Convqot_number,
        SUM(FACT.Conv_days) Conv_days,
        COUNT(FACT.newqot_amnt) Newqot_amnt_cnt,
        COUNT(FACT.sec_newqot_amnt) sec_Newqot_amnt_cnt,
        COUNT(FACT.newqot_number) Newqot_number_cnt,
        COUNT(FACT.openqot_amnt) Openqot_amnt_cnt,
        COUNT(FACT.sec_openqot_amnt) sec_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.sec_convqot_amnt) sec_Convqot_amnt_cnt,
        COUNT(FACT.convqot_number) Convqot_number_cnt,
        COUNT(FACT.Conv_days) Conv_days_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, PRNTMV.Parent_group_id,
                    FACT.Ent_Year_id, FACT.Ent_qtr_id,
                    FACT.Ent_period_id, FACT.Week_id,
                    FACT.Day_id) Grp_id
FROM    ASO_BI_QOT_L1_MV FACT,
        JTF.JTF_RS_GROUPS_DENORM RSG,
        ASO_BI_RSG_PRNT_MV PRNTMV
WHERE   FACT.Resource_grp_id = RSG.Group_id
AND     RSG.Latest_Relationship_Flag = 'Y'
AND     PRNTMV.Group_id = RSG.Parent_group_id
GROUP BY  RSG.Parent_group_id, PRNTMV.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  Parent_Resource_grp_id,
        FACT.Resource_grp_id  Resource_grp_id,
        FACT.Resource_id,
        'N' Resource_grp_flag,
        SUM(FACT.newqot_amnt) Newqot_amnt,
        SUM(FACT.sec_newqot_amnt) sec_Newqot_amnt,
        SUM(FACT.newqot_number) Newqot_number,
        SUM(FACT.openqot_amnt) Openqot_amnt,
        SUM(FACT.sec_openqot_amnt) sec_Openqot_amnt,
        SUM(
        FACT.openqot_number *
        DECODE(Row_Type, 'OPNNGN', -1, 1)
        ) Openqot_number,
        SUM(FACT.convqot_amnt) Convqot_amnt,
        SUM(FACT.sec_convqot_amnt) sec_Convqot_amnt,
        SUM(FACT.convqot_number) Convqot_number,
        SUM(FACT.Conv_days) Conv_days,
        COUNT(FACT.newqot_amnt) Newqot_amnt_cnt,
        COUNT(FACT.sec_newqot_amnt) sec_Newqot_amnt_cnt,
        COUNT(FACT.newqot_number) Newqot_number_cnt,
        COUNT(FACT.openqot_amnt) Openqot_amnt_cnt,
        COUNT(FACT.sec_openqot_amnt) sec_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.sec_convqot_amnt) sec_Convqot_amnt_cnt,
        COUNT(FACT.convqot_number) Convqot_number_cnt,
        COUNT(FACT.Conv_days) Conv_days_cnt,
        COUNT(*) All_cnt1,
        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.Ent_Year_id, FACT.Ent_qtr_id,
                    FACT.Ent_period_id, FACT.Week_id,
                    FACT.Day_id) Grp_id
FROM    ASO_BI_QOT_L1_MV FACT
GROUP BY  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
                        )