MATERIALIZED VIEW: APPS.ASO_BI_QOT_DISC_MV

Source

SELECT /* 12.0: bug#4526784 */  'G' Umarker,
        Low,
        Parent_group_id Resource_grp_id,
        NULL Resource_id,
        'Y' Resource_grp_flag,
        SUM(New_qot_val) new_val,
        SUM(sec_New_qot_val) sec_new_val,
        SUM(Opn_qot_val) opn_val,
        SUM(sec_Opn_qot_val) sec_opn_val,
        SUM(Conv_qot_val) conv_val,
        SUM(sec_Conv_qot_val) sec_conv_val,
        COUNT(New_qot_val) new_val_cnt,
        COUNT(sec_New_qot_val) sec_new_val_cnt,
        COUNT(Opn_qot_val) opn_val_cnt,
        COUNT(sec_Opn_qot_val) sec_opn_val_cnt,
        COUNT(Conv_qot_val) conv_val_cnt,
        COUNT(sec_Conv_qot_val) sec_conv_val_cnt,
        SUM(New_qot_cnt) new_cnt,
        SUM(Opn_qot_cnt * DECODE(Row_Type, 'OPNNGN', -1, 1)) opn_cnt,
        SUM(Conv_qot_cnt) conv_cnt,
        COUNT(New_qot_cnt) new_cnt_cnt,
        COUNT(Opn_qot_cnt * DECODE(Row_Type, 'OPNNGN', -1, 1)) opn_cnt_cnt,
        COUNT(Conv_qot_cnt) conv_cnt_cnt,
        COUNT(*) All_count,
        Ent_year_id,
        Ent_qtr_id,
        Ent_period_id,
        Week_id,
        Day_id,
        DECODE(
          GROUPING_ID(Ent_year_id,Ent_qtr_id,Ent_period_id,Week_id,Day_id),
          15,Ent_year_id,23,Ent_qtr_id,27,Ent_period_id, 29, Week_id,30,Day_id)
          Time_id,
        DECODE(
          GROUPING_ID(Ent_year_id,Ent_qtr_id,Ent_period_id,Week_id,Day_id),
          15,128,23,64,27,32,29,16,30,1)
          Period_type_id,
        GROUPING_ID(Low, DENORM.Parent_group_id,
                    Ent_year_id,
                    Ent_qtr_id,Ent_period_id,
                    Week_id,Day_id) grp_id
FROM
        ASO_BI_QOT_DISCB_MV FACT,
        JTF.JTF_RS_GROUPS_DENORM DENORM,
        JTF.JTF_RS_GROUP_USAGES USG
WHERE
        FACT.Resource_grp_id = DENORM.Group_id
        AND DENORM.Parent_group_id = USG.Group_id
        AND USG.Usage = 'SALES'
        AND DENORM.Latest_Relationship_Flag = 'Y'
GROUP BY
        Low,DENORM.Parent_group_id,
        Grouping Sets(Ent_year_id,Ent_qtr_id,Ent_period_id,Week_id,Day_id)
UNION ALL
SELECT  'R' Umarker,
        Low,
        FACT.Resource_Grp_Id  Resource_grp_id,
        Resource_id,
        'N' Resource_grp_flag,
        SUM(New_qot_val) new_val,
        SUM(sec_New_qot_val) sec_new_val,
        SUM(Opn_qot_val) opn_val,
        SUM(sec_Opn_qot_val) sec_opn_val,
        SUM(Conv_qot_val) conv_val,
        SUM(sec_Conv_qot_val) sec_conv_val,
        COUNT(New_qot_val) new_val_cnt,
        COUNT(sec_New_qot_val) sec_new_val_cnt,
        COUNT(Opn_qot_val) opn_val_cnt,
        COUNT(sec_Opn_qot_val) sec_opn_val_cnt,
        COUNT(Conv_qot_val) conv_val_cnt,
        COUNT(sec_Conv_qot_val) sec_conv_val_cnt,
        SUM(New_qot_cnt) new_cnt,
        SUM(Opn_qot_cnt * DECODE(Row_Type, 'OPNNGN', -1, 1)) opn_cnt,
        SUM(Conv_qot_cnt) conv_cnt,
        COUNT(New_qot_cnt) new_cnt_cnt,
        COUNT(Opn_qot_cnt * DECODE(Row_Type, 'OPNNGN', -1, 1)) opn_cnt_cnt,
        COUNT(Conv_qot_cnt) conv_cnt_cnt,
        COUNT(*) All_count1,
        Ent_year_id,
        Ent_qtr_id,
        Ent_period_id,
        Week_id,
        Day_id,
        DECODE(
          GROUPING_ID(Ent_year_id,Ent_qtr_id,Ent_period_id,Week_id,Day_id),
          15,Ent_year_id,23,Ent_qtr_id,27,Ent_period_id, 29, Week_id,30,Day_id)
          Time_id,
        DECODE(
          GROUPING_ID(Ent_year_id,Ent_qtr_id,Ent_period_id,Week_id,Day_id),
          15,128,23,64,27,32,29,16,30,1)
          Period_type_id,
        GROUPING_ID(Low, FACT.Resource_grp_id,
                    Resource_id, Ent_year_id,
                    Ent_qtr_id,Ent_period_id,
                    Week_id,Day_id) grp_id
FROM
        ASO_BI_QOT_DISCB_MV FACT
GROUP BY
        Low,FACT.Resource_grp_id,Resource_id,
        Grouping Sets(Ent_year_id,Ent_qtr_id,Ent_period_id,Week_id,Day_id)